<?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.PersonnelAssessmentMapper">
    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="cn.com.countrygarden.bi.main.model.personnel.PersonnelAssessment">
                                    <id column="id" property="id" />
                                                 <result column="personnel_type_id" property="personnelTypeId" />
                                                <result column="personnel_Id" property="personnelId" />                                              
                                                <result column="evaluation" property="evaluation" />
                                                <result column="flag" property="flag" />
                                                <result column="addition_time" property="additionTime" />
                                                <result column="created_date" property="createdDate" />
                                                <result column="created_by" property="createdBy" />
                                                <result column="created_name" property="createdName" />
                                                <result column="updated_date" property="updatedDate" />
                                                <result column="updated_by" property="updatedBy" />
                        </resultMap>
                        
                        <!-- 自定义查询映射结果，再根据id查询对应的List<option> -->
	<resultMap id="ProblemVoMap"
		type="cn.com.countrygarden.bi.main.dto.ReviewQuestionnaireProblemVo">
		<id column="id" property="id" />
		<result column="questionnaire_problem_classification_id"
			property="classification" />
		<result column="question_type_id" property="type" />
		<result column="name" property="name" />
		
		<result column="title_description" property="title" />
		<result column="score" property="score" />

		<result column="answer_description"
			property="answerDescription" />
		<collection property="option"
			ofType="cn.com.countrygarden.bi.main.dto.ReviewQuestionnaireProblemOptionVo"
			select="optionVoList" column="id">
		</collection>
	</resultMap>
	
	
		<resultMap id="BaseResultjuMap"
		type="cn.com.countrygarden.bi.main.model.review.ReviewJudge">
		<id column="id" property="id" />
		<result column="investigated_id" property="investigatedId" />
		<result column="grade_id" property="gradeId" />
		<result column="NAME" property="name" />
		<result column="tel" property="tel" />
		<result column="is_partake" property="isPartake" />
		<result column="flag" property="flag" />
		<result column="create_by" property="createBy" />
		<result column="create_time" property="createTime" />
		<result column="update_by" property="updateBy" />
		<result column="update_time" property="updateTime" />
	</resultMap>
	<sql id="sqlcoue">      
          <if test="userquyu!=null and userquyu.size()!=0">
          and
          (<foreach collection="userquyu" item="userquyu"
						index="index" open="(" close=")" separator="or">
						  (t.Four_tissue like concat('%',#{userquyu},'%') or doc.PK_ORG  like concat('%',#{userquyu},'%'))
						</foreach>)
          
		   
		        <!--  <if test="rolesMap.identitys!=null">
		                   or pg.identity in
					<foreach collection="rolesMap.identitys" item="identitys"
						index="index" open="(" close=")" separator=",">
						#{identitys}
					</foreach>
		          </if> -->
						
		</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="optionVoList"
		resultType="cn.com.countrygarden.bi.main.dto.ReviewQuestionnaireProblemOptionVo">
		select id optionId, questionnaire_problem_id problemId,name name,option_score optionScore
		from review_questionnaire_problem_option where
		questionnaire_problem_id=#{id}
	</select>
	
	<select id="peradsssone" resultType="cn.com.countrygarden.bi.main.model.personnel.PersonnelAssessment" parameterType="java.lang.String">
	       select pe.personnel_Id as personnelId from personnel_assessment pe,personnel p
           where pe.personnel_Id=p.id 
           <if test="userId!=null and userId!=''">
                   and p.user_id=#{userId}
           </if>
           
         
	 
	</select>
                        
       <select id="scoreproblem"
		resultType="cn.com.countrygarden.bi.main.dto.ReviewScoreProblemDTO"
		parameterType="cn.com.countrygarden.bi.main.dto.ReviewScoreProblemDTO">
		
		select rqp.title_description as titledescription,
        rs.score as score,rj.id ,rj.grade_id as rgname,rqp.id as questionnaireId,rqp.questionnaire_problem_classification_id as described,
        rj.`name`, rs.answer as answer,rs.create_time as createtime
       from review_judge rj
      left join review_questionnaire_problem rqp on rj.questionnaire_id=rqp.questionnaire_id
      left join review_score rs on rs.problem_id=rqp.id and rs.judge_id=rj.id and rs.judge_id=rj.id
       left join review_questionnaire rq on rq.id=rj.questionnaire_id
      where rq.flag=1 and rqp.flag=1 and rs.flag=1 and rj.investigated_id = #{investigatedid} and rj.grade_id=#{rgname} and rq.investigationmanagement_id=3 and rq.id=#{questionnaireId}
       order by rs.create_time desc
		

      </select>
      
      <update id="updateByPrimary" parameterType="cn.com.countrygarden.bi.main.model.personnel.PersonnelAssessment">
               update personnel_assessment 
	             <set>
	                 <if test="evaluation!=null and evaluation!=''">evaluation=#{evaluation}, </if>
	                  <if test="updatedBy!=null and updatedBy!=''">updated_by=#{updatedBy},</if>
	                  <if test="updatedDate!=null">updated_date=#{updatedDate}</if>
	             
	             </set>
	              where id=#{id};
	
       
      </update>
         
      <select id="reviewjudge" resultType="cn.com.countrygarden.bi.main.model.review.ReviewJudge" parameterType="cn.com.countrygarden.bi.main.dto.ReviewScoreProblemDTO">
              select rj.id as id,rj.name as `name` from review_investigated ri,review_judge rj,review_score rs,review_questionnaire rq
              where ri.id=rj.investigated_id and rs.judge_id=rj.id 
               and rj.questionnaire_id=rq.id and rq.investigationmanagement_id=3 and ri.id=#{investigatedid} and rj.grade_id=#{rgname} and rq.id=#{questionnaireId}
              group by rj.name,rj.id
         
       </select>
       
       <select id="questionnaireProblems" resultType="cn.com.countrygarden.bi.main.dto.ReviewQuestionnaireProblemDTO" parameterType="cn.com.countrygarden.bi.main.dto.ReviewScoreProblemDTO">
                 select rqp.id as id,rqp.questionnaire_id as questionnaireId,rqp.questionnaire_problem_classification_id as questionnaireProblemClassificationId,rqpc.name as `name`,
              rqp.question_type_id as questionTypeId,rqp.score as score,rqp.title_description as titleDescription,rqp.answer_description as answerDescription,rqp.flag as flag
              from review_questionnaire_problem rqp,review_questionnaire_problem_classification rqpc,review_questionnaire rq
              where rqp.questionnaire_problem_classification_id=rqpc.id and rq.id=rqp.questionnaire_id and rq.flag=1 and rqp.flag=1 
              and rqp.questionnaire_id=#{questionnaireId} and rq.investigationmanagement_id=#{investigationmanagementId}  
       
       </select>
       
       <select id="ReviewQuestionnairelistVo"
		resultType="cn.com.countrygarden.bi.main.dto.ReviewQuestionnairelistVo">
	select q.id id,q.name name ,q.flag flag ,(select count(i.id) from
		review_investigated i where
		i.questionnaire_id=q.id) total,(select count(distinct(j.investigated_id)) from review_judge j,review_investigated i
		where j.questionnaire_id=q.id and j.is_submission=0 and i.id=j.investigated_id  and i.flag=0) underWay from
		review_questionnaire q
		where q.flag=1 
		<if test="name!=null and name!=''">
			and q.name like concat('%',#{name},'%')
		</if>
		<if test="investigationmanagementId!=null">
			and q.investigationmanagement_id =#{investigationmanagementId}
		</if>
		<if test="id!=null">
			and q.id =#{id}
		</if>
		<if test="hasRole==false">
			and q.create_by=#{userCode}
		</if>
		order by q.create_time desc
	</select>
	
	<select id="findProblemClassificationIds" parameterType="int"
		resultType="int">
		select distinct(p.questionnaire_problem_classification_id) from
		review_questionnaire_problem
		p,review_questionnaire_problem_classification c where p.flag=1
		and p.questionnaire_problem_classification_id=c.id and c.flag !=2 and
		p.questionnaire_id=#{questionnaireId}

	</select>
	
	
		<select id="selectProblemClassificationIds" parameterType="int" resultType="int">
	
	select distinct(questionnaire_problem_classification_id) from `review_questionnaire_problem` where flag=1 and questionnaire_id !=#{questionnaireId}  and questionnaire_problem_classification_id in
	
	<foreach collection="classificationIds" index="index" item="classificationIds"
			open="(" separator="," close=")">
			#{classificationIds}
		</foreach>	
	</select>
	
	<select id="selectQuestionnaireProblemTypeVo"
		resultType="cn.com.countrygarden.bi.main.dto.ReviewQuestionnaireProblemTypeVo" parameterType="int">
		select t.id,t.name label from `review_questionnaire_problem_type` t where t.id in
		(select i.review_questionnaire_problem_type_id from  investigationmanagement_ref_problem_type i where i.investigationmanagement_id=#{investigationmanagementId})

	</select>
	
	<select id="queryQuestionnaireProblemVo" resultType="cn.com.countrygarden.bi.main.dto.ReviewQuestionnaireProblemVo" parameterType="int">


			   select rqp.id as id,rqpc.name as `name`,rqp.title_description as title,rqp.questionnaire_problem_classification_id as classification,rqp.question_type_id as TYPE,rqpt.orderxu as orderxu,rqp.score as score
                 FROM review_questionnaire_problem rqp
                 left join review_questionnaire rq on rq.id=rqp.questionnaire_id
                 left join review_questionnaire_problem_type rqpt on rqpt.id=rqp.question_type_id
                 left join review_questionnaire_problem_classification rqpc on rqpc.id=rqp.questionnaire_problem_classification_id
                 where rq.investigationmanagement_id=3 and rq.id=#{questionnaireId} and rqp.question_type_id<![CDATA[<>]]>2
                  order by classification 
	</select>
	
	<update id="delupdate" parameterType="java.lang.Integer">
	     update personnel_assessment set flag=1 where id=#{id};
	</update>
	
	<select id="queryQuestionnaireProblemClassificationVo"
		resultType="cn.com.countrygarden.bi.main.dto.ReviewQuestionnaireProblemClassificationVo" parameterType="int">
		 select rqpc.id as id,rqpc.name as label,rqpc.flag flag from
		review_questionnaire_problem_classification rqpc
         where rqpc.investigationmanagement_id=#{investigationmanagementId}
	</select>
	
		<select id="findClassificationFlagTwo" parameterType="int"
		resultType="int">
		select id from review_questionnaire_problem_classification where flag=2
	</select>
	
		<delete id="deleteNotClassificationIds" parameterType="int">
		delete from `review_questionnaire_problem_classification` where flag=1
		and id not in

		<foreach collection="classificationId" index="index"
			item="classificationId" open="(" separator="," close=")">
			#{classificationId}
		</foreach>

	</delete>
	
	 <select id="selectrqclassiid" resultType="java.lang.Integer">
            select id from review_questionnaire_problem_classification where `name`='无' and investigationmanagement_id=3	   
	   </select>
	   
	   <update id="updateProblemFlag" parameterType="int">
		update review_questionnaire_problem set flag=0 where
		questionnaire_id=#{questionnaireId}
	</update>
	
   <select id="queryQuestionnaireProblemdanVo" resultMap="ProblemVoMap" parameterType="int">
             select id,questionnaire_problem_classification_id,
		question_type_id , title_description ,score ,answer_description
		from review_questionnaire_problem where
		questionnaire_id=#{questionnaireId}
   </select>
   
    <select id="queryQuestionnaireProblemwendaVo" resultType="cn.com.countrygarden.bi.main.dto.ReviewQuestionnaireProblemVo" parameterType="int">
	      select rqp.id as id,rqp.questionnaire_problem_classification_id as classification,rqpc.name as `name`,
        rqp.question_type_id as `type`,rqp.title_description as title,rqp.score as score,rqp.answer_description as answerDescription
        from review_questionnaire_problem_classification rqpc,review_questionnaire_problem rqp,review_questionnaire rq 
        where rq.id=rqp.questionnaire_id and rqpc.id=rqp.questionnaire_problem_classification_id and
        rqp.questionnaire_id=#{questionnaireId} and rq.investigationmanagement_id=3 and rqp.question_type_id<![CDATA[<>]]>1 and rqp.question_type_id<![CDATA[<>]]>3
        order by classification

     </select>
     
     <select id="selectInvestigationVoList"
		resultType="cn.com.countrygarden.bi.main.dto.ReviewInvestigationVo"
		parameterType="cn.com.countrygarden.bi.main.dto.ReviewInvestigationVo">
SELECT DISTINCT i.id AS investigatedid,i.questionnaire_id AS questionnaireid,
		CASE WHEN (t.Four_tissue IS NULL) THEN doc.PK_ORG ELSE t.Four_tissue END regionName,
		i.name AS NAME, i.flag AS flag
		,i.create_time AS launchtime , q.name AS
		questionnairename,q.investigationmanagement_id AS investigationmanagementId,
		(SELECT COUNT(j.id)
		FROM biapp.review_judge j WHERE
		j.investigated_id=i.id) AS
		total,(SELECT COUNT(
		j.is_submission) FROM
		biapp.review_judge j WHERE
		j.investigated_id=i.id AND
		j.is_submission=0) AS
		underWay,
		(SELECT COUNT(g.id) FROM biapp.review_grade
		g,biapp.review_judge j WHERE
		g.name='上级' AND
		g.id=j.grade_id AND
		j.is_submission=0 AND
		j.investigated_id=i.id) AS
		superiorUnderWay,
		(SELECT
		COUNT(g.id) FROM
		biapp.review_grade g,biapp.review_judge j WHERE g.name='上级' AND
		g.id=j.grade_id AND
		j.investigated_id=i.id) AS superiortotal,
		(SELECT
		COUNT(g.id) FROM
		biapp.review_grade g,biapp.review_judge j WHERE g.name='同级' AND
		g.id=j.grade_id AND
		j.is_submission=0 AND j.investigated_id=i.id) AS 
		equativeunderway,
		(SELECT
		COUNT(g.id) FROM biapp.review_grade g,biapp.review_judge
		j WHERE g.name='同级' AND
		g.id=j.grade_id AND j.investigated_id=i.id) AS
		equativetotal,
		(SELECT
		COUNT(g.id) FROM biapp.review_grade g,biapp.review_judge j
		WHERE g.name='下级' AND
		g.id=j.grade_id AND j.is_submission=0 AND
		j.investigated_id=i.id) AS
		subordinateunderway,
		(SELECT COUNT(g.id) FROM
		biapp.review_grade
		g,biapp.review_judge j WHERE g.name='下级' AND
		g.id=j.grade_id AND
		j.investigated_id=i.id) AS subordinatetotal

		FROM biapp.review_investigated i
		LEFT JOIN biapp.review_questionnaire q ON i.questionnaire_id=q.id
        LEFT JOIN biapp.personnel p ON p.user_id=i.job_number
        LEFT JOIN biapp.personnel_tag pg ON pg.user_id=p.user_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="reviewinvesti.questionName!=null and reviewinvesti.questionName!=''">
			and q.name=#{reviewinvesti.questionName}
		</if>
		
		<if test="reviewinvesti.name!=null and reviewinvesti.name!=''">
			and i.name like concat('%',#{reviewinvesti.name},'%')
		</if>
		<if test="reviewinvesti.investigationmanagementId!=null">
			and q.investigationmanagement_id =#{reviewinvesti.investigationmanagementId}
		</if>
		<if test="reviewinvesti.questionnaireName!=null and reviewinvesti.questionnaireName!=''">
			and q.name like concat('%',#{reviewinvesti.questionnaireName},'%')
		</if>
		
		  <!-- rolesMap.role!=null没有相应的角色 -->
		<if test=" rolesMap.role!=null">
			and q.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 i.job_number = #{rolesMap.gj_Area}
				 <if test="rolesMap.identitys!=null">
					or pg.identity in
					<foreach collection="rolesMap.identitys" item="identitys"
						index="index" open="(" close=")" separator=",">
						#{identitys}
					</foreach>
			</if>

			 <if test="rolesMap.gj_Area==null">
				<if test="rolesMap.identitys!=null">
					and pg.identity in
					<foreach collection="rolesMap.identitys" item="identitys"
						index="index" open="(" close=")" separator=",">
						#{identitys}
					</foreach>
				</if>
			</if> 
			
		</if>
		
		</if> -->
		
		<!--  <if test="reviewinvesti.haseole==false">
		and q.create_by=#{reviewinvesti.getusercode}
       </if>  -->
       </where> 
		order by i.create_time desc
	</select>
	
   <select id="selectJudgeTel" parameterType="int"
		resultMap="BaseResultjuMap">
		select id,questionnaire_id,tel,name from review_judge where is_submission=0
		and investigated_id=#{investigatedId} and
		questionnaire_id=#{questionnaireId}
	</select>
	
	<select id="selectInvestigatedIsRepeatVo" resultType="cn.com.countrygarden.bi.main.dto.reviewInvestigatedIsRepeatVo" parameterType="int">
	select i.flag flag,i.is_repeat isrepeat,j.is_submission issubmission from  review_investigated i, review_judge j 
	where i.id=j.investigated_id and i.id=#{investigatedId} and j.id=#{reviewJudgeId}	
	</select>
	
	<select id="selectAnswerBeforeVo" parameterType="int"
		resultType="cn.com.countrygarden.bi.main.dto.ReviewAnswerBeforeVo">
		select i.id investigatedId,i.questionnaire_id questionnaireId,i.name
		investigatedName,
		(select q.name from review_questionnaire q where q.id=i.questionnaire_id)
		questionnaireName,
		(select q.described from review_questionnaire q where
		q.id=i.questionnaire_id) questionnaireDescribed
		from review_investigated i where i.id=#{investigatedId} and i.questionnaire_id=#{questionnaireId}

	</select>
	
	<select id="queryQuestionnaireProblemVoasse" resultType="cn.com.countrygarden.bi.main.dto.ReviewQuestionnaireProblemVo" parameterType="int">

			   select rqp.id as id,rqpc.name as `name`,rqp.title_description as title,rqp.questionnaire_problem_classification_id as classification,rqp.question_type_id as TYPE,rqpt.orderxu as orderxu,rqp.score as score
                 from review_questionnaire_problem rqp
                 left join review_questionnaire rq on rq.id=rqp.questionnaire_id
                 left join review_questionnaire_problem_type rqpt on rqpt.id=rqp.question_type_id
                 left join review_questionnaire_problem_classification rqpc on rqpc.id=rqp.questionnaire_problem_classification_id
                 where rq.investigationmanagement_id=3 and rq.id=#{questionnaireId} and rqp.question_type_id<![CDATA[<>]]>2
                  order by classification 
	</select>
	
     <select id="option" resultType="cn.com.countrygarden.bi.main.dto.ReviewQuestionnaireProblemOptionVo">
            select rqpo.id as optionId,rqpo.questionnaire_problem_id as problemId,rqpo.name as name,rqpo.option_score as optionScore
            from review_questionnaire_problem_option rqpo
            where rqpo.questionnaire_problem_id=#{problemid}  

     </select>
     
     <select id="queryQuestionnaireProblemwendaVoasse" resultType="cn.com.countrygarden.bi.main.dto.ReviewQuestionnaireProblemVo" parameterType="int">
	      select rqp.id as id,rqp.questionnaire_problem_classification_id as classification,rqpc.name as `name`,
        rqp.question_type_id as `type`,rqp.title_description as title,rqp.score as score,rqp.answer_description as answerDescription
        from review_questionnaire_problem_classification rqpc,review_questionnaire_problem rqp,review_questionnaire rq 
        where rq.id=rqp.questionnaire_id and rqpc.id=rqp.questionnaire_problem_classification_id and
        rqp.questionnaire_id=#{questionnaireId} and rq.investigationmanagement_id=3 and rqp.question_type_id<![CDATA[<>]]>1 and rqp.question_type_id<![CDATA[<>]]>3
        order by classification

     </select>
     
     <update id="updataflag" parameterType="java.lang.Integer">
		update review_score rs,review_judge rj set rs.flag=0
		where rs.judge_id=rj.id and rj.questionnaire_id=#{questionnaireId} and
		rj.investigated_id=#{id} and rs.judge_id=#{judgeId};

	</update>
	
	<select id="revinveads" resultType="cn.com.countrygarden.bi.main.model.review.ReviewInvestigated" parameterType="cn.com.countrygarden.bi.main.dto.ReviewScoreProblemDTO">
	        select distinct ri.id as id from review_score rs,review_judge rj,review_investigated ri
            where rs.judge_id=rj.id and rj.investigated_id=ri.id
            <if test="userId!=null and userId!=''">
                and ri.job_number like concat('%',#{userId},'%')
            </if>
            <if test="name!=null and name!=''">
             and ri.NAME like concat('%',#{name},'%')
            </if>
            
           <if test="investigatedId!=null">
              and ri.id=#{investigatedId}
           </if>
	</select>
	
		<select id="revinveaAssessment" resultType="cn.com.countrygarden.bi.main.model.review.ReviewInvestigated">
	        select distinct ri.id as id from review_score rs,review_judge rj,review_investigated ri
            where rs.judge_id=rj.id and rj.investigated_id=ri.id
           
              <if test="perseluserid!=null">
		and ri.job_number in
		<foreach collection="perseluserid" item="perseluserid" index="index" open="("
				 close=")" separator=",">
			#{perseluserid}
		</foreach>
		</if>
           
	</select>
	
	<select id="pernnelAsseslidt" resultType="cn.com.countrygarden.bi.main.dto.PersonnelAssessmentDTO">
    
     
         select distinct pas.id as id,pe.user_id as userId,pe.name as `name`,pas.evaluation as evaluation,rq.id as questionnaireId,
          ri.id as investigatedId,pas.created_by as createdBy,ri.create_time as createdDate,pas.created_name as createdName,
          case when (t.Four_tissue is null) then doc.PK_ORG else t.Four_tissue end regionName 
          from personnel_assessment pas
           left join personnel pe on pas.personnel_Id=pe.id
           left join personnel_tag pg on pg.user_id=pe.user_id
           left join review_investigated ri on pas.id=ri.personnelassessment_id and ri.job_number=pe.user_id
           left join review_judge rj on rj.investigated_id=ri.id
           left join review_score rs on rs.judge_id=rj.id 
           left join review_questionnaire_problem rqp on rs.problem_id=rqp.id
           left join review_questionnaire rq on rq.id=rqp.questionnaire_id
           left join review_questionnaire_problem_classification rqc on rqp.questionnaire_problem_classification_id=rqc.id
           left join bd_psndoc doc on doc.`CODE`=pe.user_id
           left join (select distinct Four_tissue,pk_org from bd_type) t on t.pk_org=doc.PK_ORG
            <where>
            and rq.flag=1 and rs.flag=1 and rqp.flag=1 and rq.investigationmanagement_id=3 
            <include refid="sqlcoue"/> 
            
          
          <if test="personnelAssessmentDTO.userId!=null and personnelAssessmentDTO.userId!=''">
            and pe.user_id like concat('%',#{personnelAssessmentDTO.userId},'%')
       </if>
       <if test="personnelAssessmentDTO.name!=null and personnelAssessmentDTO.name!=''">
            and pe.name like concat('%',#{personnelAssessmentDTO.name},'%')
       </if>
       <if test="personnelAssessmentDTO.investigatedId!=null">
           and ri.id=#{personnelAssessmentDTO.investigatedId}
       </if>
       <if test="personnelAssessmentDTO.regionName!=null and personnelAssessmentDTO.regionName!=''">			
			and (t.Four_tissue like concat('%',#{personnelAssessmentDTO.regionName},'%') or doc.PK_ORG like concat('%',#{personnelAssessmentDTO.regionName},'%'))
		</if>
		
		
       
        <!-- rolesMap.role!=null没有相应的角色 -->
		<if test=" rolesMap.role!=null">
			and pas.flag=-1
		</if>
		
		and pas.flag=0 
		<!-- 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 pg.identity in
					<foreach collection="rolesMap.identitys" item="identitys"
						index="index" open="(" close=")" separator=",">
						#{identitys}
					</foreach>
			</if>

			 <if test="rolesMap.gj_Area==null">
				<if test="rolesMap.identitys!=null">
					and pg.identity in
					<foreach collection="rolesMap.identitys" item="identitys"
						index="index" open="(" close=")" separator=",">
						#{identitys}
					</foreach>
				</if>
			</if> 
			
		</if>
		
		</if> -->
		</where> 
	   group by pe.name,pas.id,pe.user_id,pas.evaluation,rq.id,rqc.id,ri.id,pas.created_by,ri.create_time,pas.created_name,regionName
       
       order by ri.create_time desc
 
	</select>
	
	<select id="pernnelAssessment" resultType="cn.com.countrygarden.bi.main.dto.PersonnelAssessmentDTO">
    
         <!-- select distinct pas.id as id,pe.user_id as userId,pe.name as `name`,pas.evaluation as evaluation,rq.id as questionnaireId,ri.id as investigatedId,pas.created_by as createdBy,ri.create_time AS createdDate,pas.created_name as createdName 
          from personnel_assessment pas,personnel pe,review_questionnaire rq,review_questionnaire_problem rqp,review_questionnaire_problem_classification rqc,review_score rs,review_judge rj,review_investigated ri 
           where pas.personnel_Id=pe.id and rq.id=rqp.questionnaire_id and pas.id=ri.personnelassessment_id and rqp.questionnaire_problem_classification_id=rqc.id and rq.flag=1 and rs.flag=1 and rqp.flag=1 
          and rs.problem_id=rqp.id and rj.investigated_id=ri.id and rs.judge_id=rj.id and ri.job_number=pe.user_id and rq.investigationmanagement_id=3 and pas.flag=0 -->
         select distinct pas.id as id,pe.user_id as userId,pe.name as `name`,pas.evaluation as evaluation,rq.id as questionnaireId,
          ri.id as investigatedId,pas.created_by as createdBy,ri.create_time as createdDate,pas.created_name as createdName,
          case when (t.Four_tissue is null) then doc.PK_ORG else t.Four_tissue end regionName 
          from personnel_assessment pas
           left join personnel pe on pas.personnel_Id=pe.id
           left join personnel_tag pg on pg.user_id=pe.user_id
           left join review_investigated ri on pas.id=ri.personnelassessment_id and ri.job_number=pe.user_id
           left join review_judge rj on rj.investigated_id=ri.id
           left join review_score rs on rs.judge_id=rj.id 
           left join review_questionnaire_problem rqp on rs.problem_id=rqp.id
           left join review_questionnaire rq on rq.id=rqp.questionnaire_id
           left join review_questionnaire_problem_classification rqc on rqp.questionnaire_problem_classification_id=rqc.id
           left join bd_psndoc doc on doc.`CODE`=pe.user_id
           left join (select distinct Four_tissue,pk_org from bd_type) t on t.pk_org=doc.PK_ORG
            <where> 
            and rq.flag=1 and rs.flag=1 and rqp.flag=1 and rq.investigationmanagement_id=3 
            <include refid="sqlcoue"/> 
           
            <if test="personnelAssessmentDTO.userId!=null and personnelAssessmentDTO.userId!=''">
            and pe.user_id like concat('%',#{personnelAssessmentDTO.userId},'%')
       </if>
       <if test="personnelAssessmentDTO.name!=null and personnelAssessmentDTO.name!=''">
            and pe.name like concat('%',#{personnelAssessmentDTO.name},'%')
       </if>
       <if test="personnelAssessmentDTO.investigatedId!=null">
           and ri.id=#{personnelAssessmentDTO.investigatedId}
       </if>
       <if test="personnelAssessmentDTO.regionName!=null and personnelAssessmentDTO.regionName!=''">			
			and (t.Four_tissue like concat('%',#{personnelAssessmentDTO.regionName},'%') or doc.PK_ORG like concat('%',#{personnelAssessmentDTO.regionName},'%'))
		</if>
         <if test="perseluserid!=null">
		and pe.user_id in
		<foreach collection="perseluserid" item="perseluserid" index="index" open="("
				 close=")" separator=",">
			#{perseluserid}
		</foreach>
		</if>
		<if test=" rolesMap.role!=null">
			and pas.flag=-1
		</if>
		<!-- 
		 <if test="userquyu!=null">
		   and (t.Four_tissue like concat('%',#{userquyu},'%') or doc.PK_ORG  like concat('%',#{userquyu},'%'))
		     <foreach collection="userquyu" item="userquyu"
						index="index" open="(" close=")" separator=",">
					 #{userquyu}
					</foreach>
		</if> 
		
		 <if test="rolesMap.identitys!=null">
					and tag.identity in
					<foreach collection="rolesMap.identitys" item="identitys"
						index="index" open="(" close=")" separator=",">
						#{identitys}
					</foreach>
			</if>    -->
       and pas.flag=0 
       <!--  <if test="userIds!=null">
		and  pe.user_id in
		<foreach collection="userIds" item="userIds" index="index" open="("
				 close=")" separator=",">
			#{userIds}
		</foreach>
		</if> -->
       </where>
       group by pe.name,pas.id,pe.user_id,pas.evaluation,rq.id,rqc.id,ri.id,pas.created_by,ri.create_time,pas.created_name,regionName
       
       order by ri.create_time desc
       
        
	</select>
	
	<select id="exxlspersonneall" resultType="cn.com.countrygarden.bi.main.dto.PersonnelAssessmentDTO">
<!-- 	   select ri.job_number as userId,max(pa.created_date) as createdDate from
		review_investigated ri,personnel_assessment pa where ri.flag=0 and ri.personnelassessment_id=pa.id
		group by ri.job_number  --> 
		
		select distinct ri.job_number as userId,MAX(pa.created_date) as createdDate,
        case when (t.Four_tissue is null) then doc.PK_ORG else t.Four_tissue end regionName
        from personnel_assessment pa
        left join personnel pe on pe.id=pa.personnel_id  
        left join review_investigated ri on ri.personnelassessment_id=pa.id
        left join bd_psndoc doc on doc.CODE=pe.user_id and pe.user_id=ri.job_number
        left join (select distinct Four_tissue,pk_org from bd_type) t on t.pk_org=doc.PK_ORG 
		<where>
		<include refid="sqlcoue"/> 
		<if test=" rolesMap.role!=null">
			and pas.flag=-1
		</if>
		
		and pa.flag=0
		</where> 
		group by ri.job_number,regionName
	</select>
	
	<select id="perslassntscore" resultType="cn.com.countrygarden.bi.main.dto.PersonnelAssessmentDTO" parameterType="java.lang.Integer">
	        select pas.id as id,pe.user_id as userId,pe.name as name,rqc.name as rqcname,rqc.id as rqcid,ri.id as investigatedId,sum(rs.score)/(select count(distinct rs.judge_id)
            from review_questionnaire rq,review_questionnaire_problem rqp,review_judge rj,review_score rs,review_investigated ri
            where rq.id=rqp.questionnaire_id and rj.investigated_id=ri.id and rs.judge_id=rj.id and rq.investigationmanagement_id=3 and ri.id=#{investigatedId}) as pngjun,rq.id as questionnaireId,pas.created_date as createdDate
            from personnel_assessment pas,personnel pe,review_questionnaire rq,
            review_questionnaire_problem rqp,review_questionnaire_problem_classification rqc,review_score rs,review_judge rj,review_investigated ri
            where pas.personnel_Id=pe.id and rq.id=rqp.questionnaire_id and rqp.questionnaire_problem_classification_id=rqc.id 
            and rs.problem_id=rqp.id and rj.investigated_id=ri.id and rs.judge_id=rj.id and rs.flag=1 and rqp.flag=1 and rq.flag=1 and
           ri.job_number=pe.user_id and rq.investigationmanagement_id=3 and ri.id=#{investigatedId}

            group by pe.name,rqc.name,pas.id,pe.user_id,rqc.id,rq.id,ri.id order by pas.created_date desc
	
	</select>
	
	<select id="scprezong" resultType="cn.com.countrygarden.bi.main.dto.PersonnelAssessmentDTO">
	          select pa.id as id,ri.name as name,sum(rs.score) as score
              from review_score rs,review_investigated ri,review_judge rj,personnel_assessment pa
              where ri.id=rj.investigated_id and rs.judge_id=rj.id and ri.personnelassessment_id=pa.id 
              
               <if test="investigatedId!=null">
                    and ri.id=#{investigatedId}
                </if>
              
              group by id,name      
	
	</select>
	
	<select id="reviewreProblemClaions" resultType="cn.com.countrygarden.bi.main.model.review.ReviewQuestionnaireProblemClassification">
	           select rqc.name as name from review_questionnaire rq,review_questionnaire_problem rqp,
               review_questionnaire_problem_classification rqc,review_score rs
               where rq.id=rqp.questionnaire_id and rqp.questionnaire_problem_classification_id=rqc.id 
               and rs.problem_id=rqp.id and rq.investigationmanagement_id=3
               group by rqc.name
	</select>
	
		<select id="scoredtos"
		resultType="cn.com.countrygarden.bi.main.dto.ReviewScoreDTO"
		parameterType="java.lang.Integer">

		select rg.name as gradename,sum(rs.score) as summ,sum(rs.score)/(select count(1) from review_judge rjj where
		 rjj.grade_id=#{gradeid} and rjj.investigated_id=#{investigatedid}) as
		avgs,pqpc.name as described from review_grade rg,review_judge
		rj,review_score rs,
		review_questionnaire_problem rqp,review_questionnaire_problem_classification pqpc
		where rs.judge_id=rj.id and rg.id=rj.grade_id and rqp.id=rs.problem_id and
		rqp.questionnaire_problem_classification_id= pqpc.id and
		rqp.question_type_id <![CDATA[<>]]>
		2
		and rj.investigated_id=#{investigatedid} and rs.flag=1 and rqp.questionnaire_id=#{questionnaireId}
		group by rg.name,pqpc.name

	</select>
	
	<select id="scoredtosrj"
		resultType="cn.com.countrygarden.bi.main.dto.ReviewScoreDTO"
		parameterType="java.lang.Integer">
		
		select rj.id as rjid,rj.name as rjname,rg.name as gradename,rs.score as score,pqpc.name as described from review_grade rg,review_judge
		rj,review_score rs,
		review_questionnaire_problem rqp,review_questionnaire_problem_classification pqpc
		where rs.judge_id=rj.id and rg.id=rj.grade_id and rqp.id=rs.problem_id and
		rqp.questionnaire_problem_classification_id= pqpc.id and
		rqp.question_type_id <![CDATA[<>]]>2
		and rj.investigated_id=#{investigatedid} and rs.flag=1 and rqp.questionnaire_id=#{questionnaireId}
		group by rg.name,rj.name,rj.id,pqpc.name,rj.id,rs.score,pqpc.name
		
		
	</select>
	
		<select id="scoredtosrjname" resultType="cn.com.countrygarden.bi.main.dto.ReviewScoreDTO"
		parameterType="java.lang.Integer">
		
		select rj.id as rjid,rj.name as rjname,rg.name as gradename,pqpc.name as described  from review_grade rg,review_judge
		rj,review_score rs,
		review_questionnaire_problem rqp,review_questionnaire_problem_classification pqpc
		where rs.judge_id=rj.id and rg.id=rj.grade_id and rqp.id=rs.problem_id and
		rqp.questionnaire_problem_classification_id= pqpc.id and
		rqp.question_type_id <![CDATA[<>]]>2
		AND rj.investigated_id=#{investigatedid} AND rs.flag=1 
		group by rg.name,rj.name,rj.id,pqpc.name
		
	</select>
	
		<select id="evaluationproblem"
		resultType="cn.com.countrygarden.bi.main.dto.ReviewScoreProblemDTO"
		parameterType="java.lang.Integer">
		
		select rj.name as name,rqp.title_description as titledescription,pqpc.id as
		described,rs.score as score,rs.answer as answer,rg.id as rgname,rs.create_time as createtime from review_grade
		rg,review_judge rj,review_score rs,
		review_questionnaire_problem rqp,review_questionnaire_problem_classification pqpc
		where rs.judge_id=rj.id and rg.id=rj.grade_id and rqp.id=rs.problem_id and
		rqp.questionnaire_problem_classification_id= pqpc.id and rqp.question_type_id=2
		and rj.investigated_id=#{investigatedid} and rqp.questionnaire_id=#{questionnaireId}
		
		order by rs.create_time desc
	</select>
	
		<update id="updateedit"
		parameterType="cn.com.countrygarden.bi.main.model.review.ReviewEvaluate">
		update review_evaluate set described = #{described},update_by=#{updateBy},
		update_time=#{updateTime} 
		WHERE investigated_id = #{investigatedId} AND questionnaire_id = #{questionnaireId}

	</update>
	
	<select id="reviewjudgegrade" parameterType="int" resultType="cn.com.countrygarden.bi.main.dto.ReviewExcel.ReviewjudgeGradeDTO">
               select rg.id as rgid,rg.name as rgname,rj.id as rjid,rj.name as rjname,rs.create_time as createTime from review_score rs,review_judge rj,review_grade rg 
               where rs.judge_id=rj.id and rj.grade_id=rg.id and rj.investigated_id=#{investigatedid} and rj.questionnaire_id=#{questionnaireId}			   
               group by rjname,rgid,rjid,createTime
			    order by rgid

	</select>
	
		 <select id="reviewrinqu" parameterType="java.lang.Integer" resultType="cn.com.countrygarden.bi.main.dto.ReviewExcel.ReviewRinquDTO">
              select ri.id as riid,ri.name as riname,rq.id as rqid,rq.name as rqname from review_investigated ri,review_questionnaire rq
               where rq.id=ri.questionnaire_id and ri.id=#{investigatedid} and rq.id=#{questionnaireId}
	 </select>
	 
	 		<select id="questionoblemsex" resultType="cn.com.countrygarden.bi.main.dto.ReviewQuestionnaireProblemDTO" parameterType="java.lang.Integer">
              select rqp.id as id,rqp.questionnaire_id as questionnaireId,rqp.questionnaire_problem_classification_id as questionnaireProblemClassificationId,rqpc.name as `name`,
              rqp.question_type_id as questionTypeId,rqp.score as score,rqp.title_description as titleDescription,rqp.answer_description as answerDescription,rqp.flag as flag
              from review_questionnaire_problem rqp,review_questionnaire_problem_classification rqpc,review_questionnaire rq
              where rqp.questionnaire_problem_classification_id=rqpc.id and rq.id=rqp.questionnaire_id and rqp.question_type_id<![CDATA[<>]]>2
              and rqp.questionnaire_id=#{questionnaireId} and rq.investigationmanagement_id=#{investigationmanagementId}
               
			   order by questionnaireProblemClassificationId
	   </select>
	   
	   	 <select id="scoreproblemexcl" parameterType="java.lang.Integer" resultType="cn.com.countrygarden.bi.main.dto.ReviewScoreProblemDTO">

        select rqp.title_description as titledescription,
        rs.score as score,rj.id ,rj.grade_id as rgname,rqp.id as questionnaireId,rqp.questionnaire_problem_classification_id as described,
        rj.`name`, rs.answer as answer,rs.create_time as createtime
       from review_judge rj
      left join review_questionnaire_problem rqp on rj.questionnaire_id=rqp.questionnaire_id
      left join review_score rs on rs.problem_id=rqp.id and rs.judge_id=rj.id and rs.judge_id=rj.id 
      where rj.investigated_id=#{investigatedid} and rj.questionnaire_id=#{questionnaireid} and rqp.question_type_id<![CDATA[<>]]>2
      order by described

	 </select>
	 
	 	   <select id="questionoblemswenex" resultType="cn.com.countrygarden.bi.main.dto.ReviewQuestionnaireProblemDTO" parameterType="java.lang.Integer">
              select rqp.id as id,rqp.questionnaire_id as questionnaireId,rqp.questionnaire_problem_classification_id as questionnaireProblemClassificationId,rqpc.name as `name`,
              rqp.question_type_id as questionTypeId,rqp.score as score,rqp.title_description as titleDescription,rqp.answer_description as answerDescription,rqp.flag as flag
              from review_questionnaire_problem rqp,review_questionnaire_problem_classification rqpc,review_questionnaire rq
              where rqp.questionnaire_problem_classification_id=rqpc.id and rq.id=rqp.questionnaire_id and rqp.question_type_id<![CDATA[<>]]>1 and rqp.question_type_id<![CDATA[<>]]>3
              and rqp.questionnaire_id=#{questionnaireId} and rq.investigationmanagement_id=#{investigationmanagementId}
               
	   </select>
	   
	   <select id="scoreproblemwenexcl" parameterType="java.lang.Integer" resultType="cn.com.countrygarden.bi.main.dto.ReviewScoreProblemDTO">

        select rqp.title_description as titledescription,
        rs.score as score,rj.id ,rj.grade_id as rgname,rqp.id as questionnaireId,rqp.questionnaire_problem_classification_id as described,
        rj.`name`, rs.answer as answer,rs.create_time as createtime
       from review_judge rj
      left join review_questionnaire_problem rqp on rj.questionnaire_id=rqp.questionnaire_id
      left join review_score rs on rs.problem_id=rqp.id and rs.judge_id=rj.id and rs.judge_id=rj.id 
      where rj.investigated_id=#{investigatedid} and rj.questionnaire_id=#{questionnaireid} and rqp.question_type_id<![CDATA[<>]]>1 and rqp.question_type_id<![CDATA[<>]]>3
      order by described

	 </select>
	 
	    <select id="getlistlishi" parameterType="java.lang.Integer" resultType="cn.com.countrygarden.bi.main.dto.ReviewScoreProblemDTO">

        select rs.score as score,rs.answer as answer,rqp.title_description as titleDescription,rqpc.name as describedname,rj.name as `name`,rs.create_time as createTime from review_score rs
       left join review_questionnaire_problem rqp on rs.problem_id=rqp.id
      left join review_questionnaire_problem_classification rqpc on rqp.questionnaire_problem_classification_id=rqpc.id
      left join review_judge rj on rj.id=rs.judge_id
      where rj.investigated_id=#{investigatedid}
      group by rj.name,rqp.title_description,rqpc.name,rs.score,rs.answer,rs.create_time
      order by rs.create_time desc

	 </select>
       
       <select id="scoreraid"
		resultType="java.lang.Integer"
		parameterType="cn.com.countrygarden.bi.main.dto.ReviewScoreProblemDTO">
		select count(1) from review_judge rj,review_investigated ri where rj.investigated_id=ri.id and
        ri.id=#{investigatedid} and rj.grade_id=#{rgname}

	</select>
	
	<select id="reviewevaluate" resultType="java.lang.String" parameterType="java.lang.Integer">
	      select re.described from review_evaluate re,review_investigated ri
        where re.investigated_id=ri.id and ri.questionnaire_id=re.questionnaire_id 
       and re.investigated_id=#{investigatedid} and re.questionnaire_id=#{questionnaireid}
	</select>
	
	 <select id="personInfoCodeOrId" resultType="cn.com.countrygarden.bi.main.dto.PersonInfoVO" parameterType="java.lang.String">
            select p.id as id,p.user_id as code,p.name as name from personnel p
            
            <where>
                <if test="search!=null and search!=''">
                    p.user_id=#{search} or p.name like concat('%', #{search},'%')
                </if>
            </where>
            limit 0,20 
    
    </select>
    
    <delete id="deleteass" parameterType="cn.com.countrygarden.bi.main.model.personnel.PersonnelAssessment">
           delete from personnel_assessment where
	        personnel_Id=#{personnelId};
              
    </delete>
    
    <select id="selectdelpas" resultType="cn.com.countrygarden.bi.main.dto.PersonnelAssessmentDTO">
             select pas.id as id,ri.job_number as userId from review_investigated ri,personnel_assessment pas
             where ri.personnelassessment_id=pas.id and ri.flag=0 and pas.id=#{id}
    
    </select>
    
    <update id="delupdatepiliang">
       update personnel_assessment set flag=1
       <where>
           <if test="id!=null">
           id in
             <foreach collection="id" item="id" index="index" open="(" close=")" separator=",">
    
                #{id}
           </foreach>
           
           </if>
       
       </where>
    </update>
    
    <select id="selectrerediao" resultType="cn.com.countrygarden.bi.main.dto.ReviewInvestigationVo">
            select ri.id as investigatedId,rq.name as questionnaireName from review_investigated ri,review_questionnaire rq
            <where>
            and rq.id=ri.questionnaire_id and ri.flag=0 and ri.questionnaire_id=#{id}
           <!--  <if test="id!=null">
            
                   ri.questionnaire_id in 
                   <foreach collection="id" item="id" index="index" open="(" close=")" separator=",">
    
                #{id}
           </foreach>
           </if> -->
            </where>
           
    
    </select>
       
</mapper>