<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper
		namespace="cn.com.countrygarden.bi.main.dao.personnel.PersonnelMapper">
	<!-- 通用查询映射结果 -->
	<resultMap id="BaseResultMap"
			   type="cn.com.countrygarden.bi.main.model.personnel.Personnel">
		<id column="id" property="id" />
		<result column="user_id" property="userId" />
		<result column="name" property="name" />
		<result column="created_date" property="createdDate" />
		<result column="created_by" property="createdBy" />
		<result column="updated_date" property="updatedDate" />
		<result column="updated_by" property="updatedBy" />
	</resultMap>
	<select id="perone" resultType="cn.com.countrygarden.bi.main.model.personnel.Personnel">
		select p.id as id,p.user_id as userId,p.name as name from personnel p
		where p.user_id=#{userId}
	</select>

	<select id="peroneid" resultType="cn.com.countrygarden.bi.main.model.personnel.Personnel">
		select p.id as id,p.user_id as userId,p.name as name from personnel p
		where p.id=#{id}
	</select>


	<select id="selectInfomationByUid"
			resultType="cn.com.countrygarden.bi.main.dto.LabelVo">
		SELECT DISTINCT b.BIRTHDATE,b.EDU education,b.JOINWORKDATE
		joinworkDate,b.AGE age,b.SEX sex,b.MOBILE mobile,
		l.name,l.user_id
		userId,t.identity
		FROM personnel_tag t
		left join personnel l on t.user_id=l.user_id
		LEFT JOIN performance_app.bd_psndoc b ON
		b. CODE = l.user_id
		<where>
			<if test="name != null and name != ''">
				and l.name LIKE concat('%',#{name},'%')
			</if>
			<if test="userId != null and userId != ''">
				and l.user_id LIKE concat('%',#{userId},'%')
			</if>
		</where>
	</select>
	<select id="selectByUid"
			resultType="cn.com.countrygarden.bi.main.dto.LabelVo">
		SELECT DISTINCT
		b.BIRTHDATE,b.EDU,b.JOINWORKDATE,b.AGE,b.SEX,b.MOBILE,b.GLBDEF17,b.TITLETECHPOST,b.EDU,b.SEX,b.OFFICEPHONE,b.AGE,l.name,l.user_id
		userId
		FROM performance_app.bd_psndoc b
		LEFT JOIN biapp.personnel l ON
		b. CODE = l.user_id
		WHERE b.code = #{uid}
	</select>

	<!-- <select id="selectByUids"
			resultType="cn.com.countrygarden.bi.main.dto.LabelVo"
			parameterType="String">
		SELECT DISTINCT
		b.BIRTHDATE,b.EDU,b.JOINWORKDATE,b.AGE,b.SEX,b.MOBILE,b.GLBDEF17,b.TITLETECHPOST,b.EDU,b.SEX,b.OFFICEPHONE,b.AGE,l.name,l.user_id
		userId
		FROM performance_app.bd_psndoc b
		LEFT JOIN biapp.personnel l ON
		b. CODE = l.user_id
		WHERE b.code in
		<foreach collection="uids" item="uids" index="index" open="("
				 close=")" separator=",">

			#{uids}

		</foreach>
	</select> -->
	<select id="selectByUids2"
			resultType="cn.com.countrygarden.bi.main.dto.personnel.PersonnelAppDTO"
			parameterType="String">
		SELECT DISTINCT
		l.id personnelId,l.name,l.user_id userId,
		b.GLBDEF17
		entryTime,b.EDU education,
		b.AGE age,b.SEX sex,b.MOBILE mobile,
		a.evaluation assessmentEvaluate
		FROM performance_app.bd_psndoc b
		LEFT JOIN
		biapp.personnel l ON b. CODE = l.user_id
		LEFT JOIN personnel_assessment a ON a.personnel_Id=l.id
		where l.user_id in
		<foreach collection="uids" item="uids" index="index" open="("
				 close=")" separator=",">
			#{uids}
		</foreach>
	</select>
	<select id="selectByPersonInfoByCodeOrName"
			resultType="cn.com.countrygarden.bi.main.dto.PersonInfoVO">
		SELECT DISTINCT
		d.BID,
		p.user_id CODE,
		p.name NAME,
		w.pk_dept workdept,
		p.id personnelId
		FROM
		personnel_tag t
		left join personnel p on t.user_id=p.user_id
		left join performance_app.bd_psndoc d on p.user_id = d.CODE
		left join performance_app.hi_psnjob w on d.PK_PSNDOC = w.PK_PSNDOC
		left join (select distinct Four_tissue,pk_org from performance_app.bd_type) t on t.pk_org=d.PK_ORG 
		where w.poststat = 'Y'
		
		 <if test="userquyu!=null">
		     and t.Four_tissue like concat('%',#{userquyu},'%') 
		 </if>
		<if test="search != null and search != ''">
			and ( p.name like concat('%', #{search}, '%') or p.user_id = #{search} )
		</if>
		
			<if test="userIds!=null">
		and  p.user_id in
		<foreach collection="userIds" item="userIds" index="index" open="("
				 close=")" separator=",">
			#{userIds}
		</foreach>
		</if>
		
	</select>
	
	
	<select id="findPersonnelAdvisorDTO"
			resultType="cn.com.countrygarden.bi.main.dto.personnel.PersonnelAdvisorDTO">
		SELECT DISTINCT
		pe.personnel_id,
		pe.id id,
		pe.personnel_type_id advisorType,
		pe.advisor_user_name advisorUserName,
		pe.mentorpost
		mentorpost,
		pe.advisor_user_id AS advisorUserId,
		pe.positivevaluation AS
		positivevaluation,
		pe.fixedpostevaluation AS fixedpostevaluation,
		pe.filesame AS filesame,
		pe.files AS files,
		pe.created_by AS createdBy,
		pe.created_date AS createdDate
		FROM
		personnel_advisor pe
		LEFT JOIN
		personnel_advisor_type t on t.id=pe.personnel_type_id
		LEFT JOIN
		personnel p on p.user_id=pe.advisor_user_id
		where
		t.advisor_type=#{Type} and pe.personnel_id=#{personnelId}
	</select>
	<select id="find270Info" parameterType="int"  resultType="Double">
		SELECT
		AVG(score)
		FROM
		review_score
		WHERE
		judge_id IN (
		SELECT DISTINCT
		j.id
		FROM
		review_judge j
		LEFT JOIN review_investigated i ON i.id = j.investigated_id
		WHERE
		i.job_number =#{uid}
		)
		and problem_id in
		<foreach collection="problemIds" item="problemIds" index="index" open="("
				 close=")" separator=",">
			#{problemIds}
		</foreach>
	</select>
	<select id="findProblemIds" resultType="int" parameterType="int">
    select DISTINCT p.id from review_questionnaire_problem p
	left join review_questionnaire q on p.questionnaire_id=q.id
	where q.investigationmanagement_id=3
	 and p.questionnaire_problem_classification_id=#{classId}
	</select>
	<select id="findPersonnelCampusevaluation" parameterType="Integer" resultType="cn.com.countrygarden.bi.main.dto.personnel.CampusevaluationDTO">
	select matchinglevel campusevaluationMatchinglevel, created_date campusevaluationCreatedDate from personnel_campusevaluation
	where personnel_id=#{personnelId}
	
	</select>
	
		<select id="selectnull" resultType="cn.com.countrygarden.bi.main.model.personnel.Personnel">
	       select pe.id as id,pe.user_id as userId,pe.name as name from personnel pe where pe.user_id='' or pe.name='' or pe.user_id is null or pe.name is null 
	
	</select>
</mapper>