<?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.manager.map.ManagerHousekeeperFollowMapper">
    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="cn.com.countrygarden.bi.main.model.manager.map.ManagerHousekeeperFollow">
     	<id column="id" property="id" />
        <result column="bid" property="bid" />
        <result column="region" property="region" />
        <result column="user_code" property="userCode" />
        <result column="created_date" property="createdDate" />
        <result column="created_by" property="createdBy" />
        <result column="updated_date" property="updatedDate" />
        <result column="updated_by" property="updatedBy" />
	</resultMap>

    <!-- 通用查询映射结果 -->
    <resultMap id="HouseMapperResultMap" type="cn.com.countrygarden.bi.main.dto.HouseKeeperDTO">
        <id column="BID" property="BID" />
        <result column="ADDR" property="ADDR" />
        <result column="BIRTHDATE" property="BIRTHDATE" />
        <result column="CODE" property="CODE" />
        <result column="CREATIONTIME" property="CREATIONTIME" />
        <result column="CREATOR" property="CREATOR" />
        <result column="DATAORIGINFLAG" property="DATAORIGINFLAG" />
        <result column="EMAIL" property="EMAIL" />
        <result column="ENABLESTATE" property="ENABLESTATE" />
        <result column="HOMEPHONE" property="HOMEPHONE" />
        <result column="IDTYPE" property="IDTYPE" />
        <result column="ISSHOPASSIST" property="ISSHOPASSIST" />
        <result column="JOINWORKDATE" property="JOINWORKDATE" />
        <result column="MNECODE" property="MNECODE" />
        <result column="MOBILE" property="MOBILE" />
        <result column="MODIFIEDTIME" property="MODIFIEDTIME" />
        <result column="MODIFIER" property="MODIFIER" />
        <result column="NAME" property="NAME" />
        <result column="NAME2" property="NAME2" />
        <result column="NAME3" property="NAME3" />
        <result column="NAME4" property="NAME4" />
        <result column="NAME5" property="NAME5" />
        <result column="NAME6" property="NAME6" />
        <result column="OFFICEPHONE" property="OFFICEPHONE" />
        <result column="PK_GROUP" property="PK_GROUP" />
        <result column="PK_ORG" property="PK_ORG" />
        <result column="PK_PSNDOC" property="PK_PSNDOC" />
        <result column="SEX" property="SEX" />
        <result column="TS" property="TS" />
        <result column="USEDNAME" property="USEDNAME" />
        <result column="BLOODTYPE" property="BLOODTYPE" />
        <result column="CENSUSADDR" property="CENSUSADDR" />
        <result column="CHARACTERRPR" property="CHARACTERRPR" />
        <result column="COUNTRY" property="COUNTRY" />
        <result column="DIE_DATE" property="DIE_DATE" />
        <result column="DIE_REMARK" property="DIE_REMARK" />
        <result column="EDU" property="EDU" />
        <result column="FAX" property="FAX" />
        <result column="FILEADDRESS" property="FILEADDRESS" />
        <result column="HEALTH" property="HEALTH" />
        <result column="ISHISKEYPSN" property="ISHISKEYPSN" />
        <result column="JOINPOLITYDATE" property="JOINPOLITYDATE" />
        <result column="MARITAL" property="MARITAL" />
        <result column="MARRIAGEDATE" property="MARRIAGEDATE" />
        <result column="NATIONALITY" property="NATIONALITY" />
        <result column="NATIVEPLACE" property="NATIVEPLACE" />
        <result column="PENELAUTH" property="PENELAUTH" />
        <result column="PERMANRESIDE" property="PERMANRESIDE" />
        <result column="PK_DEGREE" property="PK_DEGREE" />
        <result column="PK_HRORG" property="PK_HRORG" />
        <result column="POLITY" property="POLITY" />
        <result column="POSTALCODE" property="POSTALCODE" />
        <result column="PROF" property="PROF" />
        <result column="RETIREDATE" property="RETIREDATE" />
        <result column="SECRET_EMAIL" property="SECRET_EMAIL" />
        <result column="SHORTNAME" property="SHORTNAME" />
        <result column="TITLETECHPOST" property="TITLETECHPOST" />
        <result column="FIRSTNAME" property="FIRSTNAME" />
        <result column="LASTNAME" property="LASTNAME" />
        <result column="ISCADRE" property="ISCADRE" />
        <result column="ISHISLEADER" property="ISHISLEADER" />
        <result column="AGE" property="AGE" />
        <result column="WORKAGE" property="WORKAGE" />
        <result column="GLBDEF21" property="GLBDEF21" />
        <result column="GLBDEF1" property="GLBDEF1" />
        <result column="GLBDEF2" property="GLBDEF2" />
        <result column="GLBDEF3" property="GLBDEF3" />
        <result column="GLBDEF4" property="GLBDEF4" />
        <result column="GLBDEF5" property="GLBDEF5" />
        <result column="GLBDEF6" property="GLBDEF6" />
        <result column="GLBDEF7" property="GLBDEF7" />
        <result column="GLBDEF8" property="GLBDEF8" />
        <result column="GLBDEF9" property="GLBDEF9" />
        <result column="GLBDEF10" property="GLBDEF10" />
        <result column="GLBDEF11" property="GLBDEF11" />
        <result column="GLBDEF12" property="GLBDEF12" />
        <result column="GLBDEF13" property="GLBDEF13" />
        <result column="GLBDEF14" property="GLBDEF14" />
        <result column="GLBDEF15" property="GLBDEF15" />
        <result column="GLBDEF17" property="GLBDEF17" />
        <result column="GLBDEF18" property="GLBDEF18" />
        <result column="GLBDEF19" property="GLBDEF19" />
        <result column="GLBDEF20" property="GLBDEF20" />
        <result column="GLBDEF22" property="GLBDEF22" />
        <result column="GLBDEF23" property="GLBDEF23" />
        <result column="GLBDEF16" property="GLBDEF16" />
        <result column="GLBDEF24" property="GLBDEF24" />
        <result column="GLBDEF25" property="GLBDEF25" />
        <result column="GLBDEF26" property="GLBDEF26" />
        <result column="GLBDEF27" property="GLBDEF27" />
        <result column="GLBDEF28" property="GLBDEF28" />
        <result column="GLBDEF29" property="GLBDEF29" />
        <result column="GLBDEF30" property="GLBDEF30" />
        <result column="RN" property="RN" />
        <result column="DR" property="DR" />
        <result column="region" property="region" />
        <result column="followFlag" property="followFlag" />
        <result column="projectName" property="projectName" />
        <result column="gardenDistrict" property="gardenDistrict" />
        <result column="hotlineConnectionTime" property="hotlineConnectionTime" />
        <result column="housekeeperPhone" property="housekeeperPhone" />
        <result column="jurisdictionMonth" property="jurisdictionMonth" />
        <result column="phoneIsfixed" property="phoneIsfixed" />
        <result column="serviceCenterHotline" property="serviceCenterHotline" />
        <result column="remark" property="remark" />
        <result column="housekeeperId" property="housekeeperId" />
        <result column="houses" property="houses" />
        <result column="villas" property="villas" />
        <result column="shops" property="shops" />
        <result column="jobLv" property="jobLv" />
        <result column="CommName" property="commName" />
        <result column="organName" property="organName" />
        <result column="developmentsubject" property="developmentSubject" />
        <result column="userCode" property="userCode" />
        <result column="hjid" property="hjid" />
    </resultMap>

    <select id="selectHouseKeeperFllow" resultMap="HouseMapperResultMap">
			SELECT  f.region organName,r.property_project_name projectName,f.hjid hjid,r.project_name commName
				,doc.ADDR,doc.BIRTHDATE,doc.`CODE`,doc.CREATIONTIME,doc.CREATOR,doc.DATAORIGINFLAG,doc.EMAIL,doc.ENABLESTATE,doc.HOMEPHONE,doc.ID,doc.IDTYPE,doc.ISSHOPASSIST,doc.JOINWORKDATE,doc.MNECODE,doc.MOBILE,doc.MODIFIEDTIME,doc.MODIFIER,doc.`NAME`,doc.NAME2,doc.NAME3,doc.NAME4,doc.NAME5,doc.NAME6,doc.OFFICEPHONE,r.property_project_name PK_GROUP,doc.PK_ORG,doc.PK_PSNDOC,doc.SEX,doc.TS,doc.USEDNAME,doc.BLOODTYPE,doc.CENSUSADDR,doc.CHARACTERRPR,doc.COUNTRY,doc.DIE_DATE,doc.DIE_REMARK,doc.EDU,doc.FAX,doc.FILEADDRESS,doc.HEALTH,doc.ISHISKEYPSN,doc.JOINPOLITYDATE,doc.MARITAL,doc.MARRIAGEDATE,doc.NATIONALITY,doc.NATIVEPLACE,doc.PENELAUTH,doc.PERMANRESIDE,doc.PK_DEGREE,doc.POLITY,doc.POSTALCODE,doc.PROF,doc.RETIREDATE,doc.SECRET_EMAIL,doc.SHORTNAME,doc.TITLETECHPOST,doc.FIRSTNAME,doc.LASTNAME,doc.ISCADRE,doc.ISHISLEADER,doc.AGE,doc.WORKAGE,doc.GLBDEF21,doc.GLBDEF1,doc.GLBDEF2,doc.GLBDEF3,doc.GLBDEF4,doc.GLBDEF5,doc.GLBDEF6,doc.GLBDEF7,doc.GLBDEF8,doc.GLBDEF9,doc.GLBDEF10,doc.GLBDEF11,doc.GLBDEF12,doc.GLBDEF13,doc.GLBDEF14,doc.GLBDEF15,doc.GLBDEF17,doc.GLBDEF18,doc.GLBDEF19,doc.GLBDEF20,doc.GLBDEF22,doc.GLBDEF23,doc.GLBDEF16,doc.GLBDEF24,doc.GLBDEF25,doc.GLBDEF26,doc.GLBDEF27,doc.GLBDEF28,doc.GLBDEF29,doc.GLBDEF30,doc.RN,doc.DR,doc.BID
				,fhkc.id housekeeperId,fhkc.garden_district gardenDistrict,fhkc.hotline_connection_time hotlineConnectionTime,fhkc.housekeeper_phone housekeeperPhone,fhkc.jurisdiction_month jurisdictionMonth,fhkc.phone_isfixed phoneIsfixed,fhkc.service_center_hotline serviceCenterHotline,fhkc.remark remark
                ,f.post jobLv
			FROM biapp.manager_housekeeper_follow f 
			LEFT JOIN performance_app.bd_psndoc doc ON f.bid=doc.BID
			LEFT JOIN biapp.wcx_region_project_rel r on r.property_project_name=doc.PK_ORG
			LEFT JOIN (SELECT id,garden_district,hotline_connection_time,housekeeper_phone,jurisdiction_month,phone_isfixed,service_center_hotline,remark,user_code,bid FROM biapp.fenghuang_house_keeper_communication) fhkc ON fhkc.bid=doc.BID
			<where>
                f.user_code=#{userId}
                <if test="organCodes != null and organCodes.size()>0">
                    and f.region in
                    <foreach item="item" index="index" collection="organCodes" open="(" separator="," close=")">
		            	#{item}
		        	</foreach>
                </if>
                <if test="commNames!=null and commNames.size()>0">
                    and r.property_project_name in
                    <foreach item="item" index="index" collection="commNames" open="(" separator="," close=")">
		            	#{item}
		        	</foreach>
                </if>
                <if test="userCode!=null and userCode!=''">
	        		AND doc.CODE = #{userCode}
		        </if>
		        <if test="phoneNum!=null and phoneNum!=''">
		        	AND doc.MOBILE = #{phoneNum}
		        </if>
		        <if test="gardenDistricts!=null and gardenDistricts!=''">
		        	AND fhkc.garden_district = #{gardenDistricts}
		        </if>
           </where>
           ORDER BY r.project_name ASC
    </select>
	<select id="selectHouseKeeperFollowName" resultType="cn.com.countrygarden.bi.main.dto.HouseKeeperNameTreeDTO">
        SELECT DISTINCT doc.`CODE` code,doc.`NAME` name
		FROM biapp.manager_housekeeper_follow f
		LEFT JOIN performance_app.bd_psndoc doc  ON f.bid=doc.BID
		WHERE f.user_code=#{userCode}
	</select>
    <select id="selectOrganCode" resultType="java.lang.String">
    	SELECT DISTINCT f.region organName
		FROM biapp.manager_housekeeper_follow f
		WHERE f.user_code=#{userCode}
    </select>
    <select id="findPhoneNumTreeByBid" resultType="cn.com.countrygarden.bi.main.dto.HouseKeeperNameTreeDTO">
    	SELECT DISTINCT doc.MOBILE code,doc.MOBILE name
		FROM performance_app.bd_psndoc doc
		WHERE doc.CODE=#{userCode} 
    </select>
    <select id="selectFollowGardenDistrictTree" resultType="cn.com.countrygarden.bi.main.dto.HouseKeeperNameTreeDTO">
		SELECT DISTINCT IFNULL(fhkc.garden_district,'') code,IFNULL(fhkc.garden_district,'')  name
		FROM biapp.manager_housekeeper_follow f
		LEFT JOIN performance_app.bd_psndoc doc ON doc.BID = f.bid
		LEFT JOIN fenghuang_house_keeper_communication fhkc ON doc.CODE = fhkc.user_code
		WHERE f.user_code=#{userCode}
	</select>
	<select id="selectHouseKeeperFllowById" resultType="cn.com.countrygarden.bi.main.dto.HouseKeeperDTO">
		SELECT bid,region,user_code,hjid
		FROM biapp.manager_housekeeper_follow 
		WHERE bid=#{bid} AND hjid=#{hjid} AND user_code=#{userCode}
	</select>
    <select id="selectByQuery" resultType="cn.com.countrygarden.bi.main.dto.HouseKeeperDTO">
        SELECT DISTINCT c.bid BID,
        c.comm_name commName,
        c.region_name region,
        c.region_code regionCode,
        c.project_name projectName,
        c.pk_org,
        c.development_subject developmentSubject,
        c.name NAME,
        c.sex,
        c.birthdate,
        c.gxhs gxhs,
        c.garden_district gardenDistrict,
        c.joinwork_date joinworkDate,
        c.jurisdictionFor_month jurisdictionForMonth,
        c.job_lv jobLv,
        c.housekeeper_phone housekeeperPhone,
        c.phone_Isfixed phoneIsfixed,
        c.service_center_Hotline serviceCenterHotline,
        c.hotline_connection_time hotlineConnectionTime,
        c.jurisdiction_month jurisdictionMonth,
        c.pk_psndoc ,
        c.organ_name region,
        c.comm_name commName,
        c.hjid,
        c.organ_name organName
        FROM `manager_housekeeper_follow` f
        LEFT JOIN manager_housekeeper_cache c ON c.bid = f.bid
        <where>
            f.user_code = #{params.userCode}
            <if test="params.organCode!=null and params.organCode!=''">
                and c.region_name like CONCAT(#{params.organCode},'%')
            </if>
            <if test="params.commCode!=null and params.commCode!=''">
                and c.pk_org like CONCAT(#{params.commCode},'%')
            </if>
            <if test="params.userName!=null and params.userName!=''">
                and c.name like CONCAT('%',#{params.userName},'%')
            </if>
            <if test="params.phoneNum!=null and params.phoneNum!=''">
                and  c.housekeeper_phone like CONCAT('%',#{params.phoneNum},'%')
            </if>
            <if test="regions!=null and regions.size()>0">
                AND c.region_name in
                <foreach collection="regions" item="item" index="index" separator="," open="(" close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        ORDER BY c.comm_name ASC,c.project_name ASC,c.name ASC
        <if test="offset!=null and size!=null">
            limit #{offset},#{size}
        </if>
    </select>
    <select id="countPageList" resultType="java.lang.Integer">
        SELECT DISTINCT COUNT(DISTINCT c.region_name,c.pk_org,c.name,c.pk_psndoc)
        FROM `manager_housekeeper_follow` f
        LEFT JOIN manager_housekeeper_cache c ON c.bid = f.bid
        <where>
            f.user_code = #{params.userCode}
            <if test="params.organCode!=null and params.organCode!=''">
                and c.region_name like CONCAT(#{params.organCode},'%')
            </if>
            <if test="params.commCode!=null and params.commCode!=''">
                and c.pk_org like CONCAT(#{params.commCode},'%')
            </if>
            <if test="params.developmentSubjects!=null and params.developmentSubjects!=''">
                and c.development_subject=#{params.developmentSubjects}
            </if>
            <if test="params.userName!=null and params.userName!=''">
                and c.name like CONCAT('%',#{params.userName},'%')
            </if>
            <if test="params.phoneNum!=null and params.phoneNum!=''">
                and  c.housekeeper_phone like CONCAT('%',#{params.phoneNum},'%')
            </if>
            <if test="params.gardenDistricts!=null and params.gardenDistricts!=''">
                and c.garden_district=#{params.gardenDistricts}
            </if>
            <if test="regions!=null and regions.size()>0">
                AND c.region_name in
                <foreach collection="regions" item="item" index="index" separator="," open="(" close=")">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>
</mapper>