<?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.PersonnelCampusevaluationMapper">
    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="cn.com.countrygarden.bi.main.model.personnel.PersonnelCampusevaluation">
                                    <id column="id" property="id" />
                                                <result column="personnel_id" property="personnelId" />
                                                <result column="personneltag_id" property="personneltagId" />                                              
                                                <result column="matchinglevel" property="matchinglevel" />                                             
                                                <result column="filesame" property="filesame" />
                                                <result column="files" property="files" />
                                                <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>
     
     <sql id="sqlcoue">      
          <if test="userquyu!=null">
		    
		        <foreach collection="userquyu" item="userquyu"
						index="index" separator="or">
						
						 (t.Four_tissue like concat('%',#{userquyu},'%') or doc.PK_ORG  like concat('%',#{userquyu},'%'))
					</foreach> 
						
		</if> 
		<!-- 
		 <if test="rolesMap.identitys!=null">
					<foreach collection="rolesMap.identitys" item="identitys"
						index="index">
						or pg.identity like concat('#{identitys}','%')
					</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="percamlist" resultType="cn.com.countrygarden.bi.main.dto.PersonnelCampusevaluationDTO">
          select pc.id as id,p.user_id as userId,p.name as personnelName,pg.id as personneltagId,pg.identity as identity,pc.matchinglevel as matchinglevel,
           pc.created_name as createdName,pc.created_date as createdDate,pc.files as files,pc.filesame as filesame,
           case when (t.Four_tissue is null) then doc.PK_ORG else t.Four_tissue end regionName from 
          biapp.personnel_campusevaluation pc
          left join biapp.personnel p on pc.personnel_id=p.id
          left join biapp.personnel_tag pg on p.user_id=pg.user_id and pc.personneltag_id=pg.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="percam.userId!=null and percam.userId!=''">
               and p.user_id like concat('%',#{percam.userId},'%')
          </if>
          
          <if test="percam.personnelName!=null and percam.personnelName!=''">
               and p.name like concat('%',#{percam.personnelName},'%')
          </if>
          
          <if test="percam.matchinglevel!=null and percam.matchinglevel!=''">
               and pc.matchinglevel like concat('%',#{percam.matchinglevel},'%')
          </if>
          
          <if test="percam.identity!=null and percam.identity!=''">
               and pg.identity like concat('%',#{percam.identity},'%')
                              
          </if>
          
          <if test="percam.regionName!=null and percam.regionName!=''">			
			and (t.Four_tissue like concat('%',#{percam.regionName},'%') or doc.PK_ORG like concat('%',#{percam.regionName},'%'))
		</if>
		
         
          <!-- rolesMap.role!=null没有相应的角色 -->
		<if test="rolesMap.role!=null">
			and pc.flag=-1
		</if>
		
		and pc.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> 
          order by pc.created_date desc
                             
     
     </select>
     
      <select id="percamlione" resultType="cn.com.countrygarden.bi.main.dto.PersonnelCampusevaluationDTO" parameterType="java.lang.String">
          select pc.id as id,p.user_id as userId,p.name as personnelName,pc.matchinglevel as matchinglevel,
          pc.created_name as createdName,pc.created_date as createdDate,pc.files as files,pc.filesame as filesame 
          from personnel_campusevaluation pc,personnel p,personnel_tag pag
          WHERE pc.personnel_id=p.id and pag.id=pc.personneltag_id and pc.flag=0 and p.user_id =#{userId} and pag.identity=#{identity}         
       </select>
     
     <select id="perone" resultType="cn.com.countrygarden.bi.main.dto.PersonnelCampusevaluationDTO" parameterType="java.lang.Integer">
                
           select pc.id as id,p.user_id as userId,p.name as personnelName,pg.id as personneltagId,pg.identity as identity,pc.matchinglevel as matchinglevel,
           pc.created_name as createdName,pc.created_date as createdDate,pc.files as files,pc.filesame as filesame,
           case when (t.Four_tissue is null) then doc.PK_ORG else t.Four_tissue end regionName from 
          biapp.personnel_campusevaluation pc
          left join biapp.personnel p on pc.personnel_id=p.id
          left join biapp.personnel_tag pg on p.user_id=pg.user_id and pc.personneltag_id=pg.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 pc.flag=0  
          
          <if test="id!=null">
              and pc.id=#{id} 
           </if>
                    
     </select>
     
      <select id="addone" resultType="cn.com.countrygarden.bi.main.dto.PersonnelCampusevaluationDTO" parameterType="java.lang.String">
                
          select pc.id as id,p.user_id as userId,pc.personnel_id as personnelId,p.name as personnelName,pc.matchinglevel as matchinglevel,
          pc.created_name as createdName,pc.created_date as createdDate,pc.filesame as filesame,pc.files as files,pc.flag as flag,
          pc.updated_by as updatedBy,pc.updated_date as updatedDate,pg.id as personneltagId,pg.identity as identity from personnel_campusevaluation pc,personnel p,personnel_tag pg
          where pc.personnel_id=p.id and pg.user_id=p.user_id and pg.id=pc.personneltag_id and pc.flag=0
          
          <if test="userId!=null">
              and p.user_id=#{userId} 
           </if>
           
           <if test="personneltagId!=null">
               and pg.id=#{personneltagId}
           </if>
                    
     </select>
     
     <select id="peroneperoncam" resultType="cn.com.countrygarden.bi.main.dto.PersonnelCampusevaluationDTO">
                
          select pc.id as id,p.user_id as userId,pc.personnel_id as personnelId,p.name as personnelName,pc.matchinglevel as matchinglevel,
          pc.created_name as createdName,pc.created_date as createdDate,pc.filesame as filesame,pc.files as files,pc.flag as flag,
          pc.updated_by as updatedBy,pc.updated_date as updatedDate from personnel_campusevaluation pc,personnel p
          where pc.personnel_id=p.id 
          
          <if test="userId!=null and userId!=''">
              and p.user_id=#{userId} 
           </if>
           
          <!--  <if test="personneltagId!=null and personneltagId!=''">
                and pg.id=#{personneltagId}
           </if> -->
           
         
     </select>
     
     <update id="edit" parameterType="cn.com.countrygarden.bi.main.model.personnel.PersonnelCampusevaluation">
            update personnel_campusevaluation
            <set>
               <if test="personneltagId!=null and personneltagId!=''">personneltag_id = #{personneltagId},</if>
               <if test="personnelId!=null and personnelId!=''">personnel_id = #{personnelId},</if>
               <if test="matchinglevel!=null and matchinglevel!=''">matchinglevel = #{matchinglevel},</if>
               <if test="filesame!=null and filesame!=''">filesame = #{filesame},</if>
               <if test="files!=null and files!=''">files = #{files},</if>
               <if test="updatedBy!=null and updatedBy!=''">updated_by = #{updatedBy},</if>
               <if test="updatedDate!=null">updated_date = #{updatedDate},</if>
            </set>       		
             where id = #{id};
     </update>
     <update id="editpeca" parameterType="cn.com.countrygarden.bi.main.dto.PersonnelCampusDTO">
            update personnel_campusevaluation
            <set>
               
               <if test="matchinglevel!=null and matchinglevel!=''">matchinglevel = #{matchinglevel},</if>
               <if test="filesame!=null and filesame!=''">filesame = #{filesame},</if>
               <if test="files!=null and files!=''">files = #{files},</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="exxlspersonnelcampus" resultType="cn.com.countrygarden.bi.main.dto.PersonnelCampusevaluationDTO">
             select pc.id as id,p.user_id as userId,p.name as personnelName,pg.id as personneltagId,pg.identity as identity,pc.matchinglevel as matchinglevel,
           pc.created_name as createdName,pc.created_date as createdDate,pc.files as files,pc.filesame as filesame,
           case when (t.Four_tissue is null) then doc.PK_ORG else t.Four_tissue end regionName from 
          biapp.personnel_campusevaluation pc
          left join biapp.personnel p on pc.personnel_id=p.id
          left join biapp.personnel_tag pg on p.user_id=pg.user_id and pc.personneltag_id=pg.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="percam.userId!=null and percam.userId!=''">
               and p.user_id like concat('%',#{percam.userId},'%')
          </if>
          
          <if test="percam.personnelName!=null and percam.personnelName!=''">
               and p.name like concat('%',#{percam.personnelName},'%')
          </if>
          
          <if test="percam.matchinglevel!=null and percam.matchinglevel!=''">
               and pc.matchinglevel like concat('%',#{percam.matchinglevel},'%')
          </if>
          
          <if test="percam.identity!=null and percam.identity!=''">
               and pg.identity like concat('%',#{percam.identity},'%')
          </if>
          <if test="percam.regionName!=null and percam.regionName!=''">
			and (t.Four_tissue like concat('%',#{percam.regionName},'%') or doc.PK_ORG like concat('%',#{percam.regionName},'%'))
		</if>
          
           <!-- rolesMap.role!=null没有相应的角色 -->
		<if test=" rolesMap.role!=null">
			and pc.flag=-1
		</if>
		
		
		
		and pc.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> 
          
     </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>
    
    <select id="selectByPersonInfoByCodeOrName" resultType="cn.com.countrygarden.bi.main.dto.PersonInfoVO">
        <!-- select distinct d.BID,d.`CODE`,d.`NAME`,w.pk_dept WORKDEPT,p.id personnelId from biapp.personnel p,biapp.personnel_tag tag,performance_app.bd_psndoc d,performance_app.hi_psnjob w  
          where d.PK_PSNDOC = w.PK_PSNDOC and p.user_id=d.`CODE` and w.endflag='N' and w.lastflag='Y' and w.ismainjob='Y' and tag.user_id=p.user_id -->
          select distinct doc.BID,doc.`CODE`,doc.`NAME`,w.pk_dept WORKDEPT,p.id personnelId 
          from biapp.personnel p
          left join biapp.personnel_tag pg on pg.user_id=p.user_id
          left join performance_app.bd_psndoc doc on p.user_id=doc.`CODE`
          left join performance_app.hi_psnjob w on doc.PK_PSNDOC = w.PK_PSNDOC
          left join (select distinct Four_tissue,pk_org from performance_app.bd_type) t on t.pk_org=doc.PK_ORG 
          <where>  
          
            and w.endflag='N' and w.lastflag='Y' and w.ismainjob='Y' 
           <if test="userquyu!=null">
		    and
		        (<foreach collection="userquyu" item="userquyu"
						index="index" separator="or">
						
						 (t.Four_tissue like concat('%',#{userquyu},'%') or doc.PK_ORG  like concat('%',#{userquyu},'%'))
					</foreach>) 
						
		</if> 
        <if test="search != null and search != ''">
            and ( doc.`NAME` like concat('%', #{search}, '%') or doc.`CODE`  =  #{search} )
        </if>
         
        <!-- rolesMap.role!=null没有相应的角色 -->
		<if test=" rolesMap.role!=null">
			and p.id=-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 d.code = #{rolesMap.gj_Area}
				 <if test="rolesMap.identitys!=null">
					or tag.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 tag.identity in
					<foreach collection="rolesMap.identitys" item="identitys"
						index="index" open="(" close=")" separator=",">
						#{identitys}
					</foreach>
				</if>
			</if> 
			
		</if>
       </if> -->
       </where>
        limit 0,20
    </select>  
    
    <select id="personneltag" resultType="cn.com.countrygarden.bi.main.dto.PersonnelTgDTO" parameterType="java.lang.String">
          select pg.id as personneltagId, pg.user_id as userId,pg.identity as identity,p.id as personnelId from personnel_tag pg,personnel p
           where pg.user_id=p.user_id 
           <if test="userId!='' and userId!=null">
                and pg.user_id=#{userId}
           </if>
           <if test="identity!='' and identity!=null">
                and pg.identity=#{identity}
           </if>
         
    
    </select>
    
    <update id="delupdate" parameterType="java.lang.Integer">
          update personnel_campusevaluation set flag=1
           where id=#{id};
    </update>
    
    <update id="delupdatepiliang">
          update personnel_campusevaluation 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>
    
    


</mapper>