<?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.ManagerHousekeeperCacheMapper">
    <!--<cache eviction="LRU"-->
           <!--type="org.apache.ibatis.cache.impl.PerpetualCache"-->
           <!--flushInterval="120000"-->
           <!--size="1024"-->
           <!--readOnly="true"/>-->
    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="cn.com.countrygarden.bi.main.model.manager.map.ManagerHousekeeperCache">
                                    <id column="id" property="id" />
                                                <result column="region_name" property="regionName" />
                                                <result column="region_code" property="regionCode" />
                                                <result column="project_name" property="projectName" />
                                                <result column="pk_org" property="pkOrg" />
                                                <result column="development_subject" property="developmentSubject" />
                                                <result column="name" property="name" />
                                                <result column="sex" property="sex" />
                                                <result column="birthdate" property="birthdate" />
                                                <result column="age_structure" property="ageStructure" />
                                                <result column="houses" property="houses" />
                                                <result column="villas" property="villas" />
                                                <result column="shops" property="shops" />
                                                <result column="garden_district" property="gardenDistrict" />
                                                <result column="joinwork_date" property="joinworkDate" />
                                                <result column="jurisdictionFor_month" property="jurisdictionforMonth" />
                                                <result column="job_lv" property="jobLv" />
                                                <result column="housekeeper_phone" property="housekeeperPhone" />
                                                <result column="phone_Isfixed" property="phoneIsfixed" />
                                                <result column="service_center_Hotline" property="serviceCenterHotline" />
                                                <result column="hotline_connection_time" property="hotlineConnectionTime" />
                                                <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>
    <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_cache` c
            <where>
                <if test="params.organCode!=null and params.organCode!=''">
                   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>
            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 COUNT(DISTINCT c.region_name,c.pk_org,c.name,c.pk_psndoc)
        FROM biapp.manager_housekeeper_cache c
        <where>
            <if test="params.organCode!=null and params.organCode!=''">
                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>

    <select id="countPageListByUser" resultType="java.lang.Integer">
        SELECT COUNT(DISTINCT c.region_name,c.pk_org,c.name,c.pk_psndoc)
        FROM biapp.manager_housekeeper_cache c
        <where>
            c.pk_org IN
            <foreach collection="commsName" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
            <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>
        </where>
    </select>

    <select id="selectByKeeper"
            resultType="cn.com.countrygarden.bi.main.dto.manager.ManagerKeeperDTO">
                SELECT DISTINCT c.name,c.region_code organCode,c.region_name organName,c.comm_code commCode,c.comm_name commName,c.region_name organName,c.comm_code commCode,c.comm_name commName,
                (select `code` from performance_app.bd_psndoc WHERE pk_psndoc=c.pk_psndoc LIMIT 1) userId,
                (SELECT ROUND(fc.collectionrate*100,2) FROM biapp.`fenghuang_collectionrate` fc WHERE fc.user_id=userId
                    AND collectionrate_time=date_format(DATE_ADD(NOW(),INTERVAL -1 MONTH),'%Y-%m') LIMIT 1 ) collectionrate
                FROM `manager_housekeeper_cache` c
			    WHERE c.region_code=#{organCode} and  c.comm_code=#{commId}
    </select>

    <select id="countHousekeeperByOrgan"
            resultType="java.lang.Integer">
        <![CDATA[
            SELECT count(DISTINCT region_name,pk_org,name,pk_psndoc)
            FROM manager_housekeeper_cache
            WHERE region_name like CONCAT(#{organName}) AND comm_name IS NOT NULL AND comm_name <> ''
        ]]>
    </select>
    <select  id="findALLPhoneNum" resultType="cn.com.countrygarden.bi.main.dto.HouseKeeperNameTreeDTO">
        SELECT housekeeper_phone name
        FROM manager_housekeeper_cache
        <where>
            <if test="phone != null and phone != ''">
                AND housekeeper_phone LIKE concat('%', #{phone}, '%')
            </if>
            <if test="regionName != null and regionName != ''">
                AND region_name like concat(#{regionName}, '%')
            </if>
        </where>
    </select>
    <update id="updateCommByProjectName" >
        UPDATE biapp.manager_housekeeper_cache
        SET comm_name = #{commName},
            comm_code = #{commCode},
            development_subject = #{developmentsubject}
        WHERE	pk_org = #{projectName}
    </update>
    <update id="updateCommByCommName" >
        UPDATE biapp.manager_housekeeper_cache
        SET comm_name = ''
        WHERE	comm_name = #{commName}
    </update>

    <select id="selectByQueryByUser"
            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.age_structure ageStructure,
        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_cache` c
        <where>
            c.region_name IN
            <foreach collection="regionName" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
            <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="params.jobLv!=null and params.jobLv!=''">
                and c.job_lv like CONCAT('%',#{params.jobLv},'%')
            </if>
        </where>
        ORDER BY c.comm_name ASC,c.name ASC
        <if test="offset!=null and size!=null">
            limit #{offset},#{size}
        </if>
    </select>
</mapper>