<?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.writtenExamination.PersonnelSelectionscoreMapper">
	<!-- 通用查询映射结果 -->
	<resultMap id="BaseResultMap"
		type="cn.com.countrygarden.bi.main.model.writtenExamination.PersonnelSelectionscore">
		<id column="id" property="id" />
		<result column="personnel_id" property="personnelId" />
		<result column="personnel_tag_id" property="personneltagId" />
		<result column="personnel_selection_type_id"
			property="personnelSelectionTypeId" />
		<result column="selectiondescription"
			property="selectiondescription" />
		<result column="score" property="score" />
		<result column="appendix_name" property="appendixName" />
		<result column="appendix" property="appendix" />
		<result column="selection_time" property="selectionTime" />
		<result column="created_date" property="createdDate" />
		<result column="created_name" property="createdName" />
		<result column="created_by" property="createdBy" />
		<result column="updated_date" property="updatedDate" />
		<result column="updated_by" property="updatedBy" />
		<result column="flag" property="flag" />
	</resultMap>
	<sql id="sqlcoue">
		<if test="rolesMap.userquyu!=null">
		<foreach collection="rolesMap.userquyu" item="rolesMap.userquyu"
						index="index" separator="or">
			 (t.Four_tissue like
			concat('%',#{rolesMap.userquyu},'%') or doc.PK_ORG like
			concat('%',#{rolesMap.userquyu},'%'))
			</foreach>
		</if>
		

		<if test="rolesMap.identitys!=null and rolesMap.userquyu==null">
			and pg.identity in
			<foreach collection="rolesMap.identitys" item="identitys"
				index="index" open="(" close=")" separator=",">
				#{identitys}
			</foreach>
		</if>

	</sql>

	<select id="findPersonnelWrittenexaminationVOAll"
		resultType="cn.com.countrygarden.bi.main.dto.PersonnelWrittenexaminationVO">
		select p.name name,p.user_id code, s.personnel_id
		personnelId,ta.identity personneltagIdentity,
		s.selection_time
		selectionTime,s.selectiondescription
		selectiondescription,t.select_type selectType,
		s.score score,s.appendix
		appendix,s.appendix_name appendixName from
		personnel_selectionscore s
		LEFT JOIN personnel p on p.id=
		s.personnel_id
		LEFT JOIN
		personnel_selection_type t on
		t.id=s.personnel_selection_type_id
		LEFT
		JOIN personnel_tag ta on
		ta.id=s.personnel_tag_id
		where s.flag=0
		<if test="selectType!=null">
			and t.select_type=#{selectType}
		</if>
		<if test="name!=null and name!=''">
			and p.name like concat('%',#{name},'%')
		</if>
		<if test="code!=null and code!=''">
			and p.user_id like concat('%',#{code},'%')
		</if>
		ORDER BY s.personnel_id,p.user_id desc
	</select>
	<select id="findPersonnelWrittenexaminationVO"
		resultType="cn.com.countrygarden.bi.main.dto.PersonnelWrittenexaminationVO">
		select distinct p.name name,p.user_id code,s.personnel_id
		personnelId,t.select_type selectType
		from personnel_selectionscore s
		left join
		personnel p on p.id= s.personnel_id
		left join
		personnel_selection_type
		t on t.id=s.personnel_selection_type_id
		where
		s.flag=0
		<if test="selectType!=null">
			and t.select_type!=#{selectType}
		</if>
		<if test="name!=null and name!=''">
			and p.name like concat('%',#{name},'%')
		</if>
		<if test="code!=null and code!=''">
			and p.user_id like concat('%',#{code},'%')
		</if>
		order by s.personnel_id,p.user_id desc
	</select>
	<select id="findExistWrittenexamination"
		resultType="cn.com.countrygarden.bi.main.dto.PersonnelTgDTO"
		parameterType="String">
		SELECT DISTINCT t.Four_tissue regionName,
		p.id personnelId,
		p.user_id
		userId,
		p.NAME NAME,
		t.identity identity,
		t.id personneltagId ,(
		SELECT
		MAX(id)
		FROM
		personnel_selectionscore
		WHERE
		s.personnel_id = personnel_id
		AND s.personnel_tag_id = personnel_tag_id
		) Id
		FROM
		personnel_selectionscore s
		LEFT JOIN personnel p ON s.personnel_id =
		p.id
		LEFT JOIN personnel_tag t ON t.id = s.personnel_tag_id
		LEFT join
		performance_app.bd_psndoc doc on doc.`CODE`=p.user_id
		LEFT JOIN
		performance_app.bd_type t on t.pk_org=doc.PK_ORG
		where s.flag=0


		<if test="userId!=null and userId!=''">
			and p.user_id like concat('%',#{userId},'%')
		</if>
		<if test="name!=null and name!=''">
			and p.name like concat('%',#{name},'%')
		</if>
		<if test="regionName!=null and regionName!=''">
			and t.Four_tissue =#{regionName}
		</if>
		<if test="userIds!=null">
			and p.user_id in
			<foreach collection="userIds" item="userIds" index="index"
				open="(" close=")" separator=",">
				#{userIds}
			</foreach>

		</if>
		ORDER BY id DESC
	</select>
	<select id="findExistWrittenexaminationAll"
		resultType="cn.com.countrygarden.bi.main.dto.PersonnelTgDTO">
		SELECT DISTINCT
		p.id personnelId,
		p.user_id
		userId,
		CASE WHEN
		(t.Four_tissue IS NULL) THEN doc.PK_ORG ELSE t.Four_tissue END
		regionName,

		p.NAME NAME,
		pg.identity identity,
		pg.id personneltagId ,(
		SELECT
		MAX(id)
		FROM
		personnel_selectionscore
		WHERE
		s.personnel_id =
		personnel_id
		AND
		s.personnel_tag_id = personnel_tag_id
		) Id
		FROM
		personnel_selectionscore
		s
		LEFT JOIN personnel p ON s.personnel_id =
		p.id
		LEFT JOIN personnel_tag pg
		ON pg.id = s.personnel_tag_id
		LEFT join
		performance_app.bd_psndoc doc
		on
		doc.`CODE`=p.user_id
		LEFT JOIN (SELECT
		DISTINCT Four_tissue,pk_org
		FROM
		performance_app.bd_type) t on
		t.pk_org=doc.PK_ORG
		<where>
              <include refid="sqlcoue" />
			<if test="personInfoVO.code!=null and personInfoVO.code!=''">
				and p.user_id =#{personInfoVO.code}
			</if>
			<if test="personInfoVO.name!=null and personInfoVO.name!=''">
				and p.name like concat('%',#{personInfoVO.name},'%')
			</if>
			<if
				test="personInfoVO.regionName!=null and personInfoVO.regionName!=''">
				AND (t.Four_tissue LIKE
				concat('%',#{personInfoVO.regionName},'%') or
				doc.PK_ORG like
				concat('%',#{personInfoVO.regionName},'%'))
			</if>
			<!-- rolesMap.role!=null没有相应的角色 -->
			<if test=" rolesMap.role!=null">
				and s.flag=-1
			</if>
			
			and s.flag=0
		</where>
		ORDER BY id DESC
	</select>
	<select id="findSelectionscoreAll" resultMap="BaseResultMap">
		select
		w.personnel_id,w.selectiondescription,w.score,w.selection_time,w.created_name,t.select_type
		from personnel_selectionscore w
		left join personnel_selection_type t on
		t.id=w.personnel_selection_type_id
		where w.flag=0 and
		t.select_type=#{selectType}
		and
		w.personnel_id=#{personnelId}

	</select>
	<!-- <select id="findSelectionscoreAll" resultMap="BaseResultMap"> select 
		w.personnel_id,w.selectiondescription,w.score,w.selection_time,w.created_name,t.select_type 
		from personnel_selectionscore w left join personnel_selection_type t on t.id=w.personnel_selection_type_id 
		where w.flag=0 and t.select_type=#{selectType} and w.personnel_id=#{personnelId} 
		</select> -->
	<select id="findPersonnelWrittenexaminationVOexportExcelAll1"
		resultType="cn.com.countrygarden.bi.main.dto.PersonnelWrittenexaminationVO">
		select p.name name,p.user_id code, s.personnel_id
		personnelId,pg.identity personneltagIdentity,CASE WHEN (t.Four_tissue
		IS NULL) THEN doc.PK_ORG ELSE t.Four_tissue END regionName,
		s.selection_time
		selectionTime,s.selectiondescription
		selectiondescription,t.id
		personnelSelectionTypeId,
		s.score
		score,s.appendix
		appendix,s.appendix_name appendixName from
		personnel_selectionscore s
		LEFT JOIN personnel p on p.id=
		s.personnel_id
		LEFT JOIN
		personnel_selection_type t on
		t.id=s.personnel_selection_type_id
		LEFT
		JOIN personnel_tag pg on
		pg.id=s.personnel_tag_id
		LEFT join performance_app.bd_psndoc doc on
		doc.`CODE`=p.user_id
		LEFT JOIN (SELECT DISTINCT Four_tissue,pk_org FROM
		performance_app.bd_type) t ON t.pk_org=doc.PK_ORG

		<where>
		<include refid="sqlcoue" />
			<if test="queryCriteriaDTO.personnelId!=null">
				and s.personnel_id in
				<foreach collection="queryCriteriaDTO.personnelId"
					index="index" item="queryCriteriaDTO.personnelId" open="("
					close=")" separator=",">
					#{queryCriteriaDTO.personnelId}
				</foreach>
			</if>
			<if test=" rolesMap.role!=null">
				and flag=-1
			</if>
			
			and s.flag=0
		</where>




		ORDER BY s.personnel_id,p.user_id desc



	</select>
	<select id="findPersonnelWrittenexaminationVOexportExcelAll"
		resultType="cn.com.countrygarden.bi.main.dto.PersonnelWrittenexaminationVO">
		select p.name name,p.user_id code, s.personnel_id
		personnelId,ta.identity personneltagIdentity,CASE WHEN (t.Four_tissue
		IS NULL) THEN doc.PK_ORG ELSE t.Four_tissue END regionName,
		s.selection_time
		selectionTime,s.selectiondescription
		selectiondescription,t.id
		personnelSelectionTypeId,
		s.score
		score,s.appendix
		appendix,s.appendix_name appendixName from
		personnel_selectionscore s
		LEFT JOIN personnel p on p.id=
		s.personnel_id
		LEFT JOIN
		personnel_selection_type t on
		t.id=s.personnel_selection_type_id
		LEFT
		JOIN personnel_tag ta on
		ta.id=s.personnel_tag_id
		LEFT join performance_app.bd_psndoc doc on
		doc.`CODE`=p.user_id
		LEFT JOIN (SELECT DISTINCT Four_tissue,pk_org FROM
		performance_app.bd_type) t ON t.pk_org=doc.PK_ORG
		where s.flag=0
		<if test="queryCriteriaDTO.personnelId!=null">
			and s.personnel_id in
			<foreach collection="queryCriteriaDTO.personnelId"
				index="index" item="queryCriteriaDTO.personnelId" open="(" close=")"
				separator=",">
				#{queryCriteriaDTO.personnelId}
			</foreach>
		</if>
		<!-- rolesMap.role!=null没有相应的角色 -->
		<if test=" rolesMap.role!=null">
			and s.flag=-1
		</if>
		<!-- rolesMap.gj_admin！=null管理员，有所有权限 -->
		<if test="rolesMap.gj_admin==null and rolesMap.role==null">
			<!-- rolesMap.gj_Area！=null区域管理员，只能查看登录账号区域的下的数据 -->
			<if test="rolesMap.gj_Area!=null">
				and doc.code = #{rolesMap.gj_Area}
				<if test="rolesMap.identitys!=null">
					or ta.identity in
					<foreach collection="rolesMap.identitys" item="identitys"
						index="index" open="(" close=")" separator=",">
						#{identitys}
					</foreach>
				</if>
			</if>

			<if test="rolesMap.gj_Area==null">
				<if test="rolesMap.identitys!=null">
					and ta.identity in
					<foreach collection="rolesMap.identitys" item="identitys"
						index="index" open="(" close=")" separator=",">
						#{identitys}
					</foreach>
				</if>
			</if>
		</if>

		ORDER BY s.personnel_id,p.user_id desc



	</select>

	<select id="selectpersonnelWrittenexaminationNewest"
		resultType="cn.com.countrygarden.bi.main.model.writtenExamination.PersonnelSelectionscore">
		select s.personnel_id personnelId,max(s.selection_time)
		selectionTime,s.personnel_selection_type_id personnelSelectionTypeId

		from
		personnel_selectionscore s
		LEFT JOIN personnel p on p.id=
		s.personnel_id
		LEFT JOIN
		personnel_selection_type t on
		t.id=s.personnel_selection_type_id
		LEFT
		JOIN personnel_tag pg on
		pg.id=s.personnel_tag_id
		LEFT join performance_app.bd_psndoc doc on
		doc.`CODE`=p.user_id
		LEFT JOIN (SELECT DISTINCT Four_tissue,pk_org FROM
		performance_app.bd_type) t ON t.pk_org=doc.PK_ORG
		<where>
		<include refid="sqlcoue" />
			<!-- rolesMap.role!=null没有相应的角色 -->
			<if test=" rolesMap.role!=null">
				and flag=-1
			</if>
			
			and flag=0
		</where>
		group by
		s.personnel_id,s.personnel_selection_type_id
	</select>
	<delete id="deletePersonnelSelectionscore"
		parameterType="java.util.List">

		delete from personnel_selectionscore where flag=0

		and personnel_id in
		<foreach collection="list" index="index" item="tagList"
			open="(" close=")" separator=",">
			#{tagList.personnelId}

		</foreach>
		and personnel_tag_id in
		<foreach collection="list" index="index" item="tagList"
			open="(" close=")" separator=",">

			#{tagList.personneltagId}

		</foreach>
		and personnel_selection_type_id in
		<foreach collection="list" index="index" item="tagList"
			open="(" close=")" separator=",">

			#{tagList.personnelSelectionTypeId}
		</foreach>

	</delete>
	
	<update id="delpiliang">
	      update biapp.personnel_selectionscore set flag=1
	      <where>
	          <if test="perslId!=null">
	                and personnel_id in 
	                <foreach collection="perslId" item="perslId" index="index" open="(" close=")" separator=",">   
                     #{perslId}
                     </foreach>
	          </if>
	          <if test="perstagId!=null">
	              and personnel_tag_id in
	              <foreach collection="perstagId" item="perstagId" index="index" open="(" close=")" separator=",">   
                     #{perstagId}
                     </foreach>
	          
	          </if>
	          <if test="perselecTypeId!=null">
	               and personnel_selection_type_id in
	                <foreach collection="perselecTypeId" item="perselecTypeId" index="index" open="(" close=")" separator=",">   
                     #{perselecTypeId}
                     </foreach>
	          </if>
	      
	      </where>
	
	</update>
</mapper>