<?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.sharppathevaluation.PersonnelSharppathevaluationMapper">
	<!-- 通用查询映射结果 -->
	<resultMap id="BaseResultMap"
		type="cn.com.countrygarden.bi.main.model.sharppathevaluation.PersonnelSharppathevaluation">
		<id column="id" property="id" />
		<result column="personnel_id" property="personnelId" />
		<result column="personneltag_id" property="personneltagId" />
		<result column="compositeIndex" property="compositeindex" />
		<result column="inferentialcapability"
			property="inferentialcapability" />
		<result column="managementpersonality"
			property="managementpersonality" />
		<result column="managerialskills" property="managerialskills" />
		<result column="starrecommendation"
			property="starrecommendation" />
		<result column="overallmerit" property="overallmerit" />
		<result column="cominformunderstanding"
			property="cominformunderstanding" />
		<result column="criticalevaluation"
			property="criticalevaluation" />
		<result column="strategicreasoning"
			property="strategicreasoning" />
		<result column="conceptualreasoning"
			property="conceptualreasoning" />
		<result column="generaldescription"
			property="generaldescription" />
		<result column="cs_answer_time" property="csAnswerTime" />
		<result column="wishsuccess" property="wishsuccess" />
		<result column="powermotive" property="powermotive" />
		<result column="affiliationmotive" property="affiliationmotive" />
		<result column="vigour" property="vigour" />
		<result column="innovation" property="innovation" />
		<result column="insight" property="insight" />
		<result column="makedecision" property="makedecision" />
		<result column="reason" property="reason" />
		<result column="optimistic" property="optimistic" />
		<result column="compressionresistance"
			property="compressionresistance" />
		<result column="emotionalstability"
			property="emotionalstability" />
		<result column="adaptability" property="adaptability" />
		<result column="socialconfidence" property="socialconfidence" />
		<result column="influencing" property="influencing" />
		<result column="empathy" property="empathy" />
		<result column="support" property="support" />
		<result column="responsibility" property="responsibility" />
		<result column="careful" property="careful" />
		<result column="logic" property="logic" />
		<result column="psychokinesis" property="psychokinesis" />
		<result column="man_totalpoints" property="manTotalpoints" />
		<result column="man_generaldescription"
			property="manGeneraldescription" />
		<result column="man_answer_time" property="manAnswerTime" />
		<result column="planningmanagement"
			property="planningmanagement" />
		<result column="organizationalmanant"
			property="organizationalmanant" />
		<result column="leadershipmanagement"
			property="leadershipmanagement" />
		<result column="controlmanagement" property="controlmanagement" />
		<result column="strategicunderstanding"
			property="strategicunderstanding" />
		<result column="goalsetting" property="goalsetting" />
		<result column="planningarrangements"
			property="planningarrangements" />
		<result column="timemanagement" property="timemanagement" />
		<result column="tasking" property="tasking" />
		<result column="authorization" property="authorization" />
		<result column="teammanagement" property="teammanagement" />
		<result column="judgementdecision" property="judgementdecision" />
		<result column="Incentivedrive" property="incentivedrive" />
		<result column="trainsubordinates" property="trainsubordinates" />
		<result column="communication" property="communication" />
		<result column="relationshipmanagement"
			property="relationshipmanagement" />
		<result column="monitoringfeedback"
			property="monitoringfeedback" />
		<result column="strainregulation" property="strainregulation" />
		<result column="performancemanaent"
			property="performancemanaent" />
		<result column="msk_generaldescriptionc"
			property="mskGeneraldescriptionc" />
		<result column="msk_answer_time" property="mskAnswerTime" />
		<result column="technicalfunction" property="technicalfunction" />
		<result column="managementype" property="managementype" />
		<result column="creativeentreprenship"
			property="creativeentreprenship" />
		<result column="safetytability" property="safetytability" />
		<result column="challengetype" property="challengetype" />
		<result column="servicetype" property="servicetype" />
		<result column="livingform" property="livingform" />
		<result column="lndependent_type" property="lndependentType" />
		<result column="occupationalanchor"
			property="occupationalanchor" />
		<result column="occ_answer_time" property="occAnswerTime" />
		<result column="relationaldimension"
			property="relationaldimension" />
		<result column="taskdimension" property="taskdimension" />
		<result column="managementstyletype"
			property="managementstyletype" />
		<result column="mst_answer_time" property="mstAnswerTime" />
		<result column="lovecommitment" property="lovecommitment" />
		<result column="Idealcommitment" property="idealcommitment" />
		<result column="normcommitment" property="normcommitment" />
		<result column="opportunitycommitment"
			property="opportunitycommitment" />
		<result column="org_totalpoints" property="orgTotalpoints" />
		<result column="org_answer_time" property="orgAnswerTime" />
		<result column="files" property="files" />
		<result column="addition_time" property="additionTime" />
		<result column="created_date" property="createdDate" />
		<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 pg.identity in
			<foreach collection="rolesMap.identitys" item="identitys"
				index="index" open="(" close=")" separator=",">
				#{identitys}
			</foreach>
		</if>

	</sql>
	<select id="PersonnelSharppathevaluationAll"
		parameterType="Integer"
		resultType="cn.com.countrygarden.bi.main.dto.sharppathevaluation.PersonnelSharppathevaluationDTO">
		select p.name personnelName,
		p.user_id
		userId,t.identity
		personnelTagIdentity,
		<include refid="PersonnelAndSharppathevaluationBaseResultMap" />
		FROM
		personnel_sharppathevaluation s
		LEFT JOIN personnel p ON
		personnel_id = p.id
		LEFT JOIN personnel_tag t on t.id=s.personneltag_id
		where s.flag=0 and p.id=#{personnelId} and t.id=#{personneltagId}
	</select>
	<sql id="PersonnelAndSharppathevaluationBaseResultMap">
		s.id id,
		s.personnel_id personnelId,
		s.personneltag_id
		personneltagId,
		s.compositeIndex compositeindex,
		s.inferentialcapability
		inferentialcapability,
		s.managementpersonality
		managementpersonality,
		s.managerialskills managerialskills,
		s.starrecommendation
		starrecommendation,
		s.overallmerit overallmerit,
		s.cominformunderstanding cominformunderstanding,
		s.criticalevaluation
		criticalevaluation,
		s.strategicreasoning strategicreasoning,
		s.conceptualreasoning conceptualreasoning,
		s.generaldescription
		generaldescription,
		s.cs_answer_time csAnswerTime,
		s.wishsuccess
		wishsuccess,
		s.powermotive powermotive,
		s.affiliationmotive
		affiliationmotive,
		s.vigour vigour,
		s.innovation innovation,
		s.insight
		insight,
		s.makedecision makedecision,
		s.reason reason,
		s.optimistic
		optimistic,
		s.compressionresistance compressionresistance,
		s.emotionalstability emotionalstability,
		s.adaptability adaptability,
		s.socialconfidence socialconfidence,
		s.influencing influencing,
		s.empathy empathy,
		s.support support,
		s.responsibility responsibility,
		s.careful careful,
		s.logic logic,
		s.psychokinesis psychokinesis,
		s.man_totalpoints manTotalpoints,
		s.man_generaldescription
		manGeneraldescription,
		s.man_answer_time manAnswerTime,
		s.planningmanagement planningmanagement,
		s.organizationalmanant
		organizationalmanant,
		s.leadershipmanagement leadershipmanagement,
		s.controlmanagement controlmanagement,
		s.strategicunderstanding
		strategicunderstanding,
		s.goalsetting goalsetting,
		s.planningarrangements planningarrangements,
		s.timemanagement
		timemanagement,
		s.tasking tasking,
		s.authorization authorization,
		s.teammanagement teammanagement,
		s.judgementdecision judgementdecision,
		s.Incentivedrive incentivedrive,
		s.trainsubordinates trainsubordinates,
		s.communication communication,
		s.relationshipmanagement
		relationshipmanagement,
		s.monitoringfeedback monitoringfeedback,
		s.strainregulation strainregulation,
		s.performancemanaent
		performancemanaent,
		s.msk_generaldescriptionc mskGeneraldescriptionc,
		s.msk_answer_time mskAnswerTime,
		s.technicalfunction technicalfunction,
		s.managementype managementype,
		s.creativeentreprenship
		creativeentreprenship,
		s.safetytability safetytability,
		s.challengetype
		challengetype,
		s.servicetype servicetype,
		s.livingform livingform,
		s.lndependent_type lndependentType,
		s.occupationalanchor
		occupationalanchor,
		s.occ_answer_time occAnswerTime,
		s.relationaldimension relationaldimension,
		s.taskdimension
		taskdimension,
		s.managementstyletype managementstyletype,
		s.mst_answer_time mstAnswerTime,
		s.lovecommitment lovecommitment,
		s.Idealcommitment idealcommitment,
		s.normcommitment normcommitment,
		s.opportunitycommitment opportunitycommitment,
		s.org_totalpoints
		orgTotalpoints,
		s.org_answer_time orgAnswerTime,
		s.files files,
		s.addition_time additionTime,
		s.created_date createdDate,
		s.created_by
		createdBy,
		s.updated_date
		updatedDate,
		s.updated_by updatedBy
	</sql>
	<select id="selectPersonnelInformation"
		resultType="cn.com.countrygarden.bi.main.dto.sharppathevaluation.SearchFormDTO">
		select distinct id id,user_id userId,name searchName from
		personnel
		WHERE 1=1
		<if test="search != null and search != ''">
			and ( name like concat('%', #{search}, '%') or user_id =
			#{search}
			)
		</if>

	</select>
	<select id="selectSharppathevaluationAll" parameterType="String"
		resultType="cn.com.countrygarden.bi.main.dto.sharppathevaluation.PersonnelSharppathevaluationDTO">
		select p.name personnelName,t.Four_tissue regionName,
		p.user_id
		userId,t.identity
		personnelTagIdentity,
		<include refid="PersonnelAndSharppathevaluationBaseResultMap" />
		FROM
		personnel_sharppathevaluation s
		LEFT JOIN personnel p ON
		personnel_id = p.id
		LEFT JOIN personnel_tag t on t.id=s.personneltag_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="searchName!=null and searchName!=''">

			and p.name like concat('%', #{searchName}, '%')
		</if>
		<if test="personnelId!=null">

			and p.id =#{personnelId}
		</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 s.id desc
	</select>
	<select id="selectSharppathevaluationInfoMapRole"
		resultType="cn.com.countrygarden.bi.main.dto.sharppathevaluation.PersonnelSharppathevaluationDTO">
		select p.name personnelName,case when (t.Four_tissue is null) then
		doc.PK_ORG else t.Four_tissue end regionName,
		p.user_id
		userId,pg.identity
		personnelTagIdentity,
		<include refid="PersonnelAndSharppathevaluationBaseResultMap" />
		FROM
		biapp.personnel_sharppathevaluation s
		LEFT JOIN biapp.personnel p
		ON
		personnel_id = p.id
		LEFT JOIN biapp.personnel_tag pg ON pg.id =
		s.personneltag_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 s.id desc
	</select>
	<select id="selectSharppathevaluationInfo"
		resultType="cn.com.countrygarden.bi.main.dto.sharppathevaluation.PersonnelSharppathevaluationDTO">
		select p.name personnelName,case when (t.Four_tissue is null) then
		doc.PK_ORG else t.Four_tissue end regionName,
		p.user_id
		userId,t.identity
		personnelTagIdentity,
		<include refid="PersonnelAndSharppathevaluationBaseResultMap" />
		FROM
		personnel_sharppathevaluation s
		LEFT JOIN personnel p ON
		personnel_id = p.id
		LEFT JOIN personnel_tag t on t.id=s.personneltag_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="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 =#{personInfoVO.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 s.id desc
	</select>
	<select id="importPersonnelSharppathevaluationData2"
		resultType="cn.com.countrygarden.bi.main.dto.sharppathevaluation.PersonnelSharppathevaluationDTO">

		SELECT
		p. NAME personnelName,
		CASE
		WHEN (ty.Four_tissue IS NULL) THEN
		doc.PK_ORG
		ELSE
		ty.Four_tissue
		END regionName,
		p.user_id userId,
		pg.identity personnelTagIdentity,
		<include refid="PersonnelAndSharppathevaluationBaseResultMap" />
		FROM
		personnel_sharppathevaluation s
		LEFT JOIN personnel p ON
		s.personnel_id = p.id
		LEFT JOIN personnel_tag pg ON pg.id =
		s.personneltag_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
		) ty ON ty.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 s.flag=-1
			</if>
			
			and s.flag=0
		</where>
		ORDER BY s.id desc
	</select>
	<select id="importPersonnelSharppathevaluationData1"
		resultType="cn.com.countrygarden.bi.main.dto.sharppathevaluation.PersonnelSharppathevaluationDTO">
		<!-- select p.name personnelName,CASE WHEN (ty.Four_tissue IS NULL) THEN 
			doc.PK_ORG ELSE ty.Four_tissue END regionName, p.user_id userId,t.identity 
			personnelTagIdentity, <include refid="PersonnelAndSharppathevaluationBaseResultMap" 
			/> FROM personnel_sharppathevaluation s LEFT JOIN personnel p ON personnel_id 
			= p.id LEFT JOIN personnel p ON personnel_id = p.id LEFT JOIN personnel_tag 
			t ON t.id = s.personneltag_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 ) ty ON ty.pk_org = doc.PK_ORG WHERE s.flag = 0 -->
		SELECT
		p. NAME personnelName,
		CASE
		WHEN (ty.Four_tissue IS NULL) THEN
		doc.PK_ORG
		ELSE
		ty.Four_tissue
		END regionName,
		p.user_id userId,
		t.identity personnelTagIdentity,
		<include refid="PersonnelAndSharppathevaluationBaseResultMap" />
		FROM
		personnel_sharppathevaluation s
		LEFT JOIN personnel p ON
		personnel_id = p.id
		LEFT JOIN personnel_tag t ON t.id =
		s.personneltag_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
		) ty ON ty.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>
		<if test="userIds!=null">
			and p.user_id in
			<foreach collection="userIds" item="userIds" index="index"
				open="(" close=")" separator=",">
				#{userIds}
			</foreach>

		</if>

		ORDER BY s.id desc
	</select>
	<select id="importPersonnelSharppathevaluationData"
		resultType="cn.com.countrygarden.bi.main.dto.sharppathevaluation.PersonnelSharppathevaluationDTO">
		select p.name personnelName,CASE WHEN (t.Four_tissue IS NULL) THEN
		doc.PK_ORG ELSE ty.Four_tissue END regionName,
		p.user_id
		userId,t.identity
		personnelTagIdentity,
		<include refid="PersonnelAndSharppathevaluationBaseResultMap" />
		FROM
		personnel_sharppathevaluation s
		LEFT JOIN personnel p ON
		personnel_id = p.id
		LEFT JOIN personnel_tag t on t.id=s.personneltag_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)
		ty ON ty.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 t.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 t.identity in
					<foreach collection="rolesMap.identitys" item="identitys"
						index="index" open="(" close=")" separator=",">
						#{identitys}
					</foreach>
				</if>
			</if>
		</if>


		ORDER BY s.id desc
	</select>
	<select id="findCountHiPsnjob" resultType="int"
		parameterType="String">
		select count(HJID) from performance_app.hi_psnjob where
		CLERKCODE=#{userId}
	</select>
	<select id="selectSharppathevaluationNewest"
		resultType="cn.com.countrygarden.bi.main.dto.sharppathevaluation.PersonnelSharppathevaluationDTO">
		select s.personnel_id personnelId,max(s.addition_time) additionTime
		from
		personnel_sharppathevaluation s
		LEFT JOIN personnel p ON
		s.personnel_id = p.id
		LEFT JOIN personnel_tag pg ON pg.id =
		s.personneltag_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
		) ty ON ty.pk_org = doc.PK_ORG
		<where>
		<include refid="sqlcoue" />
			<if test="DTO!=null">
				and personnel_id=#{DTO.personnelId}
			</if>
			<!-- rolesMap.role!=null没有相应的角色 -->
			<if test=" rolesMap.role!=null">
				and flag=-1
			</if>

			
			and flag=0
		</where>
		group by s.personnel_id
	</select>
	<select id="findPersonnelSharppathevaluationDTO"
		resultType="cn.com.countrygarden.bi.main.dto.sharppathevaluation.PersonnelSharppathevaluationDTO">
		select p.name personnelName,
		p.user_id
		userId,t.identity
		personnelTagIdentity,
		<include refid="PersonnelAndSharppathevaluationBaseResultMap" />
		FROM
		personnel_sharppathevaluation s
		LEFT JOIN personnel p ON
		personnel_id = p.id
		LEFT JOIN personnel_tag t on t.id=s.personneltag_id
		where s.flag=0


		<if test="DTO!=null">

			and s.personnel_id =#{DTO.personnelId}
			and
			s.addition_time=#{DTO.additionTime}

		</if>
	</select>
	
	
	<update id="delpiliang">
	   update biapp.personnel_sharppathevaluation set flag=1
	   <where>
	       <if test="shid!=null">
	          and id in 
	            <foreach collection="shid" item="shid" index="index" open="(" close=")" separator=",">
    
                #{shid}
           </foreach>
	       </if>
	     
	   </where>
	   
	</update>
</mapper>