<?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.performance.bdpsndocMapper">
   <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> 
		
     </sql>
    <insert id="insertOrUpdate" parameterType="java.util.List" >
            INSERT INTO bd_psndoc 
            (ADDR,BIRTHDATE,CODE,CREATIONTIME,CREATOR,DATAORIGINFLAG,EMAIL,ENABLESTATE,HOMEPHONE,ID,IDTYPE,ISSHOPASSIST,JOINWORKDATE,MNECODE,MOBILE,MODIFIEDTIME,MODIFIER,NAME,NAME2,NAME3,NAME4,NAME5,NAME6,OFFICEPHONE,PK_GROUP,PK_ORG,PK_PSNDOC,SEX,TS,USEDNAME,BLOODTYPE,CENSUSADDR,CHARACTERRPR,COUNTRY,DIE_DATE,DIE_REMARK,EDU,FAX,FILEADDRESS,HEALTH,ISHISKEYPSN,JOINPOLITYDATE,MARITAL,MARRIAGEDATE,NATIONALITY,NATIVEPLACE,PENELAUTH,PERMANRESIDE,PHOTO,PK_DEGREE,PK_HRORG,POLITY,POSTALCODE,PREVIEWPHOTO,PROF,RETIREDATE,SECRET_EMAIL,SHORTNAME,TITLETECHPOST,FIRSTNAME,LASTNAME,ISCADRE,ISHISLEADER,AGE,WORKAGE,GLBDEF21,GLBDEF1,GLBDEF2,GLBDEF3,GLBDEF4,GLBDEF5,GLBDEF6,GLBDEF7,GLBDEF8,GLBDEF9,GLBDEF10,GLBDEF11,GLBDEF12,GLBDEF13,GLBDEF14,GLBDEF15,GLBDEF17,GLBDEF18,GLBDEF19,GLBDEF20,GLBDEF22,GLBDEF23,GLBDEF16,GLBDEF24,GLBDEF25,GLBDEF26,GLBDEF27,GLBDEF28,GLBDEF29,GLBDEF30,RN,DR)
            VALUES  
            <foreach collection="bd" item="bdlist"  separator=","> 
            (#{bdlist.ADDR},#{bdlist.BIRTHDATE},#{bdlist.CODE},#{bdlist.CREATIONTIME},#{bdlist.CREATOR},#{bdlist.DATAORIGINFLAG},#{bdlist.EMAIL},#{bdlist.ENABLESTATE},#{bdlist.HOMEPHONE},#{bdlist.ID},#{bdlist.IDTYPE},#{bdlist.ISSHOPASSIST},#{bdlist.JOINWORKDATE},#{bdlist.MNECODE},#{bdlist.MOBILE},#{bdlist.MODIFIEDTIME},#{bdlist.MODIFIER},#{bdlist.NAME},#{bdlist.NAME2},#{bdlist.NAME3},#{bdlist.NAME4},#{bdlist.NAME5},#{bdlist.NAME6},#{bdlist.OFFICEPHONE},#{bdlist.PK_GROUP},#{bdlist.PK_ORG},#{bdlist.PK_PSNDOC},#{bdlist.SEX},#{bdlist.TS},#{bdlist.USEDNAME},#{bdlist.BLOODTYPE},#{bdlist.CENSUSADDR},#{bdlist.CHARACTERRPR},#{bdlist.COUNTRY},#{bdlist.DIE_DATE},#{bdlist.DIE_REMARK},#{bdlist.EDU},#{bdlist.FAX},#{bdlist.FILEADDRESS},#{bdlist.HEALTH},#{bdlist.ISHISKEYPSN},#{bdlist.JOINPOLITYDATE},#{bdlist.MARITAL},#{bdlist.MARRIAGEDATE},#{bdlist.NATIONALITY},#{bdlist.NATIVEPLACE},#{bdlist.PENELAUTH},#{bdlist.PERMANRESIDE},#{bdlist.PHOTO},#{bdlist.PK_DEGREE},#{bdlist.PK_HRORG},#{bdlist.POLITY},#{bdlist.POSTALCODE},#{bdlist.PREVIEWPHOTO},#{bdlist.PROF},#{bdlist.RETIREDATE},#{bdlist.SECRET_EMAIL},#{bdlist.SHORTNAME},#{bdlist.TITLETECHPOST},#{bdlist.FIRSTNAME},#{bdlist.LASTNAME},#{bdlist.ISCADRE},#{bdlist.ISHISLEADER},#{bdlist.AGE},#{bdlist.WORKAGE},#{bdlist.GLBDEF21},#{bdlist.GLBDEF1},#{bdlist.GLBDEF2},#{bdlist.GLBDEF3},#{bdlist.GLBDEF4},#{bdlist.GLBDEF5},#{bdlist.GLBDEF6},#{bdlist.GLBDEF7},#{bdlist.GLBDEF8},#{bdlist.GLBDEF9},#{bdlist.GLBDEF10},#{bdlist.GLBDEF11},#{bdlist.GLBDEF12},#{bdlist.GLBDEF13},#{bdlist.GLBDEF14},#{bdlist.GLBDEF15},#{bdlist.GLBDEF17},#{bdlist.GLBDEF18},#{bdlist.GLBDEF19},#{bdlist.GLBDEF20},#{bdlist.GLBDEF22},#{bdlist.GLBDEF23},#{bdlist.GLBDEF16},#{bdlist.GLBDEF24},#{bdlist.GLBDEF25},#{bdlist.GLBDEF26},#{bdlist.GLBDEF27},#{bdlist.GLBDEF28},#{bdlist.GLBDEF29},#{bdlist.GLBDEF30},#{bdlist.RN},#{bdlist.DR})      
            
            </foreach>                      
    </insert>
    <select id="selectByPersonInfoByCodeOrName" resultType="cn.com.countrygarden.bi.main.dto.PersonInfoVO">
         select distinct d.BID,d.`CODE`,d.`NAME`,w.pk_dept WORKDEPT,
        case when (t.Four_tissue is null) then d.PK_ORG else t.Four_tissue end regionName 
        from performance_app.bd_psndoc d 
        left join  performance_app.hi_psnjob w  on d.PK_PSNDOC = w.PK_PSNDOC
        left join (select distinct Four_tissue,pk_org from performance_app.bd_type) t on t.pk_org=d.PK_ORG 
        <where> 
         
		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 d.PK_ORG  like concat('%',#{userquyu},'%'))
		   </foreach>)  			
		</if> 
         <if test="search != null and search != ''">
            and ( d.`NAME` like concat('%', #{search}, '%') or d.`CODE`  =  #{search} )
        </if>
          <if test="userIds!=null">
		and  d.`CODE` in
		<foreach collection="userIds" item="userIds" index="index" open="("
				 close=")" separator=",">
			#{userIds}
		</foreach>
		</if>
		</where>
         limit 0,100 
    </select>
    <select id="findALLNameData" resultType="cn.com.countrygarden.bi.main.dto.HouseKeeperNameTreeDTO">
        SELECT DISTINCT doc.`CODE` code,doc.`NAME` name
		FROM performance_app.bd_psndoc doc
		LEFT JOIN  performance_app.hi_psnjob job ON job.CLERKCODE=doc.`CODE`
		WHERE job.PK_POST IN ('未定岗','见习管家','凤凰管家（副主管）','凤凰管家（主管）','凤凰管家（副经理）','凤凰管家（经理）')
        <if test="name != null and name != ''">
		AND doc.`NAME` LIKE concat('%', #{name}, '%')
        </if>
        <if test="regionName != null and regionName != ''">
          AND doc.PK_ORG in
          (
            SELECT
            rp.project_name
            FROM
            (
                SELECT   max(Four_tissue)  region_name, pk_org project_name
                FROM performance_app.bd_type
                where Four_tissue like concat(#{regionName}, '%')
                GROUP BY pk_org
            ) rp
         )
        </if>
    	ORDER BY doc.`CODE` ASC
    </select>
    <select id="findALLPhoneNum" resultType="cn.com.countrygarden.bi.main.dto.HouseKeeperNameTreeDTO">
        SELECT DISTINCT doc.MOBILE code,doc.MOBILE name
		FROM performance_app.bd_psndoc doc
		LEFT JOIN  performance_app.hi_psnjob job ON job.CLERKCODE=doc.`CODE`
		WHERE job.PK_POST IN ('未定岗','见习管家','凤凰管家（副主管）','凤凰管家（主管）','凤凰管家（副经理）','凤凰管家（经理）')
        <if test="phone != null and phone != ''">
		AND doc.MOBILE LIKE concat('%', #{phone}, '%')
        </if>
        <if test="regionName != null and regionName != ''">
          AND doc.PK_ORG in
          (
            SELECT
            rp.project_name
            FROM
            (
                SELECT   max(Four_tissue)  region_name, pk_org project_name
                FROM performance_app.bd_type
                where Four_tissue like concat(#{regionName}, '%')
                GROUP BY pk_org
            ) rp
         )
        </if>
    	ORDER BY doc.MOBILE ASC
    </select>
    <select id="findPhoneNumByUserCode" resultType="java.lang.String">
    	SELECT DISTINCT doc.MOBILE mobile
		FROM performance_app.bd_psndoc doc
		WHERE doc.CODE=#{userCode} 
    </select>
    <select id="findPhoneNumTreeByUserCode" resultType="java.lang.String">
    	SELECT DISTINCT doc.MOBILE
		FROM performance_app.bd_psndoc doc
		WHERE doc.CODE=#{userCode} 
    </select>

     <select id="bdpsndoc" parameterType="java.lang.String" resultType="cn.com.countrygarden.bi.main.dto.bdpsndocDTO">
           
       <!-- select dbp.BID as bid,dbp.CREATOR as creator,dbp.ID as id from performance_app.bd_psndoc dbp
       where dbp.ID=#{bdid} -->

       select dbp.ID as id,dbp.CREATOR as creator from performance_app.bd_psndoc dbp
       where dbp.ID=#{bdid}
     
     </select>

    <select id="findByPersonInfoByCodeOrName" parameterType="String" resultType="cn.com.countrygarden.bi.main.model.personnel.Personnel">
	 select  DISTINCT d.code userId,d.name name from performance_app.bd_psndoc d LEFT JOIN  performance_app.hi_psnjob w  ON d.PK_PSNDOC = w.PK_PSNDOC
        WHERE w.poststat='Y'
        and d.code=#{userId} and d.name=#{name}	
	</select>
	
		<select id="findErpUserCodes" resultType="String">
		
		
		select distinct doc.code UserCode from
		 performance_app.bd_psndoc doc 
	     left join (select distinct Four_tissue,pk_org from
		performance_app.bd_type) t on t.pk_org=doc.PK_ORG
        where 1=1
        
        <if test="userCode!=null">
             and doc.`CODE`=#{userCode}
        </if>
        
        <if test="userquyu!=null">
        and (t.Four_tissue like concat('%',#{userquyu},'%') or doc.PK_ORG  like concat('%',#{userquyu},'%'))
        
        </if>
      

	</select>
	
</mapper>