<?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.ManagerMapIndexMapper">
      <resultMap id="BaseResultMap" type="cn.com.countrygarden.bi.main.model.manager.map.FengHuangManagerData">
	    <id column="id" jdbcType="INTEGER" property="id" />
	    <result column="organ_code" jdbcType="VARCHAR" property="organCode" />
	    <result column="organ_name" jdbcType="VARCHAR" property="organName" />
	    <result column="comm_id" jdbcType="VARCHAR" property="commId" />
	    <result column="comm_name" jdbcType="VARCHAR" property="commName" />
	    <result column="manager_name" jdbcType="VARCHAR" property="managerName" />
	    <result column="phone_percentage" jdbcType="DOUBLE" property="phonePercentage" />
	    <result column="wechat_percentage" jdbcType="DOUBLE" property="wechatPercentage" />
	    <result column="wechat_cover_percentage" jdbcType="DOUBLE" property="wechatCoverPercentage" />
	    <result column="recognition_percentage" jdbcType="DOUBLE" property="recognitionpercentage" />
	    <result column="satisfaction" jdbcType="DOUBLE" property="satisfaction" />
	    <result column="received_quick_events" jdbcType="BIGINT" property="receivedQuickEvents" />
	    <result column="received_repair_events" jdbcType="BIGINT" property="receivedRepairEvents" />
	    <result column="received_complaint_events" jdbcType="BIGINT" property="receivedComplaintEvents" />
	    <result column="solved_quick_events" jdbcType="BIGINT" property="solvedQuickEvents" />
	    <result column="solved_repair_events" jdbcType="BIGINT" property="solvedRepairEvents" />
	    <result column="solved_complaint_events" jdbcType="BIGINT" property="solvedComplaintEvents" />
	    <result column="request_received" jdbcType="BIGINT" property="requestReceived" />
	    <result column="request_solved" jdbcType="BIGINT" property="requestSolved" />
	    <result column="wechat_praise" jdbcType="BIGINT" property="wechatPraise" />
	    <result column="letter_praise" jdbcType="BIGINT" property="letterPraise" />
	    <result column="banner_num" jdbcType="BIGINT" property="bannerNum" />
	    <result column="other_praise" jdbcType="BIGINT" property="otherPraise" />
	    <result column="wechat_subscription" jdbcType="BIGINT" property="wechatSubscription" />
	    <result column="status" jdbcType="VARCHAR" property="status" />
	    <result column="begin_date" jdbcType="DATE" property="beginDate" />
	    <result column="end_date" jdbcType="DATE" property="endDate" />
	    <result column="created_date" jdbcType="TIMESTAMP" property="createdDate" />
	    <result column="created_by" jdbcType="VARCHAR" property="createdBy" />
	    <result column="updated_date" jdbcType="TIMESTAMP" property="updatedDate" />
	    <result column="updated_by" jdbcType="VARCHAR" property="updatedBy" />
	    <result column="customres" jdbcType="INTEGER" property="customres" />
	    <result column="wechat_customers" jdbcType="INTEGER" property="wechatCustomers" />
	    <result column="added_wechat" jdbcType="INTEGER" property="addedWechat" />
	  </resultMap>
    
    <select id="getManagerMapData" resultType="cn.com.countrygarden.bi.main.dto.ManagerMapDataDTO">
        SELECT f.id,f.organ_name organName,f.organ_code organCode
        FROM fenghuang_manager_data f 
        <where>
            <if test="organName!=null and organName!=''">
                and f.organ_name like '%${organName}%'
            </if>
            <if test="commName!=null and commName!=''">
                and f.comm_name like '%${commName}%'
            </if>
        </where>
        GROUP BY f.organ_code
    </select>
    
	<select id="findByOrgancode" resultType="cn.com.countrygarden.bi.main.model.manager.map.FengHuangManagerData">
        SELECT c.id , c.organ_code organCode , c.organ_name organName , 
        	c.comm_id commId , c.comm_name commName
        FROM fenghuang_manager_data c
        WHERE c.organ_code = #{organCode}
        GROUP BY c.comm_id
    </select>
    
    <select id="countCommInfo" resultType="java.lang.Integer">
        SELECT count(comm_id)
        FROM (SELECT comm_id FROM fenghuang_manager_data  WHERE organ_code=#{organCode} GROUP BY comm_id ) a
    </select>

    <!--<select id="countHouseKeeper" resultType="cn.com.countrygarden.bi.main.dto.HouseKepperDTO">-->
        <!--SELECT COUNT(1) total from (-->
			<!--SELECT-->
				 <!--1-->
			<!--FROM-->
				<!--performance_app.bd_psndoc doc-->
			<!--LEFT JOIN performance_app.hi_psnjob job ON job.PK_PSNDOC = doc.`PK_PSNDOC`-->
			<!--WHERE-->
				<!--job.PK_POST IN (-->
					<!--'未定岗',-->
					<!--'见习管家',-->
					<!--'凤凰管家（副主管）',-->
					<!--'凤凰管家（主管）',-->
					<!--'凤凰管家（副经理）',-->
					<!--'凤凰管家（经理）'-->
				<!--)-->
			<!--AND job.ENDFLAG = 'N'-->
			<!--AND job.LASTFLAG = 'Y'-->
			<!--AND job.ISMAINJOB = 'Y'-->
			<!--AND job.INDOCFLAG = 'Y'-->
			<!--GROUP BY doc.PK_PSNDOC-->
		  <!--)tc-->
	<!--</select>-->
	<select id="countHouseKeeper" resultType="cn.com.countrygarden.bi.main.dto.HouseKepperDTO">
        SELECT
			IFNULL(SUM(mmc.houserkeeper_count), 0) total
		FROM
			(SELECT DISTINCT organ_name ,houserkeeper_count
			FROM
				manager_map_cache) mmc
	</select>
    <select id="countHireHouseKeeper" resultType="java.lang.Integer">
        SELECT COUNT(1) hireCount from (
			SELECT
				1
			FROM
				performance_app.bd_psndoc doc
			LEFT JOIN (
				SELECT
					PK_POST,
					CLERKCODE
				FROM
					performance_app.hi_psnjob WHERE ENDFLAG = 'N'
			AND LASTFLAG = 'Y'
			AND ISMAINJOB = 'Y'
			AND INDOCFLAG = 'Y'
			) job ON job.CLERKCODE = doc.`CODE`
			WHERE
				DATE_FORMAT(doc.GLBDEF17, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')
			AND job.PK_POST IN (
				'未定岗',
				'见习管家',
				'凤凰管家（副主管）',
				'凤凰管家（主管）',
				'凤凰管家（副经理）',
				'凤凰管家（经理）'
			)
			GROUP BY doc.PK_PSNDOC
		)tc
    </select>
    <select id="countQuitHouseKeeper" resultType="java.lang.Integer">
        SELECT COUNT(1) quitCount from (
			SELECT
				1
			FROM
				performance_app.bd_psndoc doc
			LEFT JOIN (
				SELECT
						PK_POST,
						CLERKCODE,
						PK_PSNDOC
					FROM
						performance_app.hi_psnjob WHERE ENDFLAG = 'N' AND TRNSEVENT = 4
				AND LASTFLAG = 'Y'
				AND ISMAINJOB = 'Y'
				AND INDOCFLAG = 'Y'
			) job ON job.PK_PSNDOC = doc.`PK_PSNDOC`
			WHERE
				DATE_FORMAT(GLBDEF17, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')
				and job.PK_POST IN (
					'未定岗',
					'见习管家',
					'凤凰管家（副主管）',
					'凤凰管家（主管）',
					'凤凰管家（副经理）',
					'凤凰管家（经理）'
				)
			GROUP BY doc.PK_PSNDOC

		)tc
    </select>
</mapper>
