<?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.microSatisfy.MicroSatisfyMapper">
    <!-- 批量插入调研数据 -->
    <insert id="addSatisfyList" parameterType="java.util.List" >
        INSERT INTO `micro_satisfy_info` (
        `uuid`,
        `bussinessId`,
        `questionNaire`,
        `pid`,
        `commId`,
        `commName`,
        `organId`,
        `organName`,
        `roomName`,
        `custName`,
        `receiverName`,
        `mobile`,
        `finishTime`,
        `status`,
        `n1`,
        `n2`,
        `n31`,
        `n32`,
        `n33`,
        `n34`,
        `n35`,
        `n36`,
        `n37`,
        `n38`,
        `n39`,
        `n310`,
        `n311`,
        `n312`,
        `n313`,
        `n314`,
        `n315`,
        `n316`,
        `n317`,
        `n318`,
        `n319`,
        `n320`,
        `n321`,
        `n322`,
        `n398`,
        `otherUnsatisfy`,
        `n5`,
        `n6`,
        `n7`,
        `f1`,
        `f2`,
        `f3`,
        `voice`,
        isAssessment,
        ifLocking,
        `createBy`,
        `createTime`,
        `updateBy`,
        `updateTime`
        )
        VALUES
        <foreach collection="list" item="item" index="index" separator="," >
            (
            UUID(),
            #{item.bussinessId},
            #{item.questionNaire},
            #{item.pid},
            #{item.commId},
            #{item.commName},
            #{item.organId},
            #{item.organName},
            #{item.roomName},
            #{item.custName},
            #{item.receiverName},
            #{item.mobile},
            #{item.finishTime},
            #{item.status},
            #{item.n1},
            #{item.n2},
            #{item.n31},
            #{item.n32},
            #{item.n33},
            #{item.n34},
            #{item.n35},
            #{item.n36},
            #{item.n37},
            #{item.n38},
            #{item.n39},
            #{item.n310},
            #{item.n311},
            #{item.n312},
            #{item.n313},
            #{item.n314},
            #{item.n315},
            #{item.n316},
            #{item.n317},
            #{item.n318},
            #{item.n319},
            #{item.n320},
            #{item.n321},
            #{item.n322},
            #{item.n398},
            #{item.otherUnsatisfy},
            #{item.n5},
            #{item.n6},
            #{item.n7},
            #{item.f1},
            #{item.f2},
            #{item.f3},
            #{item.voice},
            '0',
            '0',
            #{item.createBy},
            NOW(),
            #{item.updateBy},
            NOW()
            )
        </foreach>
    </insert>
    <!-- 查询码表关键字 -->
    <select id="queryAnswerCode" resultType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyAnswerCodeModel">
        SELECT answerId,answerKey FROM micro_answer_code WHERE isDelete = 1
    </select>
    <!-- 导入原始数据 -->
    <insert id="addSatisfyListOrgin"  parameterType="java.util.List">
        INSERT INTO `micro_satisfy_questionnaire` (
        `bussinessId`,
        `questionNaire`,
        `finishTime`,
        `organId`,
        `commId`,
        `organName`,
        `commName`,
        `status`,
        `roomName`,
        `custName`,
        `receiverName`,
        `mobile`,
        problem,
        `answer`,
        identifierId,
        type,
        createDate
        )
        VALUES
        <foreach collection="list" item="item" index="index" separator="," >
            (
            #{item.bussinessId},
            #{item.paperName},
            #{item.finishTime},
            #{item.organId},
            #{item.commId},
            #{item.organName},
            #{item.commName},
            #{item.status},
            #{item.roomName},
            #{item.custName},
            #{item.receiveName},
            #{item.mobile},
            #{item.question},
            #{item.answer},
            #{item.identifierId},
            #{item.identifying},
            NOW()
            )
        </foreach>
    </insert>
    
       <update id="deleteAnswerCode">
        DELETE FROM micro_answer_code
    </update>
    
        <insert id="addAnswerCode"  parameterType="java.util.List">
        INSERT INTO `micro_answer_code` (
        `uuid`,
        `answerId`,
        `answerKey`,
        `contentDescribe`,
        `answerContent`,
        `isDelete`,
        `rule`,
        `createdBy`,
        `createdTime`
        )
        VALUES
        <foreach collection="list" item="item" index="index" separator="," >
            (
            UUID(),
            #{item.answerId},
            #{item.answerKey},
            #{item.contentDescribe},
            #{item.answerContent},
            #{item.isDelete},
            #{item.rule},
            #{item.createdBy},
            NOW()
            )
        </foreach>
    </insert>
    
    
    
           <update id="deleteRegionalObjectives">
        DELETE FROM micro_satisfy_organ_point
    </update>
    
        <insert id="addRegionalObjectives"  parameterType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyPointModel">
        INSERT INTO `micro_satisfy_organ_point` (
        `organId`,
        `organName`,
        `crossPoint`,
        `basePoint`,
        `middePoint`,
        `highPoint`,
        `isDelete`,
        `createdBy`,
        `createdTime`
        )
        VALUES
           (
            #{organId},
            #{organName},
            #{crossPoint},
            #{basePoint},
            #{middePoint},
            #{highPoint},
            #{isDelete},
            #{createdBy},
            NOW()
            )
  
    </insert>
    
    <update id="updateRegionalObjectives" parameterType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyPointModel">
    UPDATE `micro_satisfy_organ_point`
SET 
 `organName` = #{organName},
 `crossPoint` = #{crossPoint},
 `basePoint` = #{basePoint},
 `middePoint` = #{middePoint},
 `highPoint` = #{highPoint},
 `isDelete` = #{isDelete},
 `createdBy` = #{createdBy},
 `createdTime` = NOW()
WHERE
	`organId` = #{organId}
    
    </update>
    
      <select id="querySatisfyQuestionnaire" resultType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyOrginModel">
	   select
			bussinessId,
			questionNaire paperName,
			finishTime,
			organId,
			organName,
			commId,
			commName,
			status,
			roomName roomName,
			custName custName,
			receiverName receiveName,
			mobile mobile,
			problem question,
			answer from micro_satisfy_questionnaire where identifierId=#{identifierId} ORDER BY createDate
    </select>
    
    <delete id="deleteSatisfyQuestionnaire">
    	delete from micro_satisfy_questionnaire where identifierId=#{identifierId}
    	    <if test="null != identifying and '' != identifying">
                        AND  type= #{identifying}
              </if>
    </delete>
    
      <select id="querySatisfyQuestionnaireByType"  resultType="java.util.HashMap">
		SELECT
				type type,
				COUNT(id) count
			FROM
				micro_satisfy_questionnaire
			WHERE
				identifierId =#{identifierId}
			GROUP BY
				type
    </select>
    
    <!-- 按批次查询导入数据 -->
    <!-- 	<select id="queryImportData" parameterType="String" resultType="java.util.HashMap">
            SELECT TEMPA.*,TEMPB.`status` FROM
            (SELECT
              any_value(A.`pid`) `pid`,
              COUNT(A.`uuid`) AS num,
              any_value(A.`organId`) `organId`,
              any_value(A.`commId`) `commId`,
              any_value(A.`commName`) `commName`,
              any_value(A.`organName`) `organName`,
               any_value(A.`quality`) `quality`,
              any_value(DATE_FORMAT(A.finishTime, "%Y-%m-%d")) `timeStr`
            FROM
              `micro_satisfy_info` A
            <where>
                A.`status` = 1
                <if test="null != pid and '' != pid">
                    AND A.pid = #{pid}
                </if>
                <if test="null == pid or '' == pid">
                    <if test="null != timeStr and '' != timeStr">
                        AND DATE_FORMAT(A.finishTime, "%Y-%m") = DATE_FORMAT(#{timeStr}, "%Y-%m")
                    </if>
                    <if test="null == timeStr or '' == timeStr">
                        AND DATE_FORMAT(A.finishTime, "%Y-%m") = DATE_FORMAT(NOW(), "%Y-%m")
                    </if>
                </if>
            </where>
            GROUP BY A.pid,A.organId,A.commId,A.quality
            ORDER BY timeStr DESC) AS TEMPA LEFT JOIN micro_satisfy_region_detail AS TEMPB
            ON TEMPA.organId = TEMPB.organId
            AND TEMPA.commId = TEMPB.commId
            AND DATE_FORMAT(TEMPB.month, "%Y-%m") = TEMPA.timeStr
        </select> -->



    <select id="queryImportData" parameterType="String" resultType="java.util.HashMap">
        SELECT
        any_value(A.`pid`) `pid`,
        (select COUNT(`uuid`) from `micro_satisfy_info` where
        commId = any_value(A.commId)
        <if test="null != timeStr and '' != timeStr">
            AND DATE_FORMAT(finishTime, "%Y-%m") = DATE_FORMAT(#{timeStr}, "%Y-%m")
        </if>
        <if test="null == timeStr or '' == timeStr">
            AND DATE_FORMAT(finishTime, "%Y-%m") = DATE_FORMAT(NOW(), "%Y-%m")
        </if>

        group by commId
        ) num,
        any_value(A.`isAssessment`) `isAssessment`,
        any_value(A.`organId`) `organId`,
        any_value(A.`commId`) `commId`,
        any_value(A.`commName`) `commName`,
        any_value(A.`organName`) `organName`,
        any_value(A.`quality`) `quality`,
        any_value(A.`ifLocking`) ifLocking,
        any_value(DATE_FORMAT(A.finishTime, "%Y-%m-%d")) `timeStr`,
        any_value(DATE_FORMAT(A.updateTime, "%Y-%m-%d %H:%i:%s")) sortingTime
        FROM
        `micro_satisfy_info` A
        where
        A.`status` = 1
        <if test="null != timeStr and '' != timeStr">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") = DATE_FORMAT(#{timeStr}, "%Y-%m")
        </if>
        <if test="null == timeStr or '' == timeStr">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") = DATE_FORMAT(NOW(), "%Y-%m")
        </if>


        GROUP BY A.commId
        ORDER BY any_value(A.updateTime) DESC
    </select>

    <!-- 查询批次号 -->
    <select id="queryPid" resultType="java.util.HashMap">
        SELECT
        pid,
        pid AS pidName
        FROM
        `micro_satisfy_info`
        WHERE `status` = 1
        GROUP BY pid
    </select>

    <!--
        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(end_date,"%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        </if>
        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(end_date,"%Y-%m") &lt;= DATE_FORMAT(#{endDate},"%Y-%m")
        </if> -->

    <!-- 查询当月管家满意度 -->
    <select id="queryManagerSatisfy" resultType = "java.util.HashMap">
        SELECT
        any_value(m.organ_code) organCode,
        any_value(m.organ_name) organName,
        any_value(m.comm_id) commId,
        any_value(m.comm_name) commName,
        any_value(m.manager_name) managerName,
        ROUND(AVG(m.recognition_percentage),2) recognitionPercentage,
        ROUND(AVG(m.satisfaction),2) satisfaction
        FROM
        fenghuang_manager_data m,
        fenghuang_comm c
        WHERE m.comm_id = c.erp_comm_id
        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(m.end_date,"%Y-%m") = DATE_FORMAT(#{endDate},"%Y-%m")
        </if>


        <if test="null != organId and '' != organId">
            AND m.organ_code= #{organId}
        </if>


        GROUP BY m.comm_id
        ORDER BY satisfaction DESC
    </select>
    <!-- 查询当月管家满意度排名 -->
    <select id="queryManagerSatisfyRank" resultType = "java.util.HashMap">
        SELECT * FROM (SELECT
        TEMP.*,
        CASE
        WHEN @y = TEMP.satisfaction
        THEN @x
        WHEN @y := TEMP.satisfaction
        THEN @x := @x + 1
        WHEN @y = 0
        THEN @x := @x + 1
        END AS rank
        FROM
        (SELECT
        any_value(m.organ_code) organCode,
        any_value(m.organ_name) organName,
        m.comm_id commId,
        any_value(m.comm_name) commName,
        m.manager_name managerName,
        ROUND(AVG(m.recognition_percentage), 2) recognitionPercentage,
        ROUND(AVG(m.satisfaction), 2) satisfaction
        FROM
        fenghuang_manager_data m,
        fenghuang_comm c
        WHERE m.comm_id = c.erp_comm_id
        AND m.organ_code = #{organId}
        AND m.comm_id = #{commId}
        <if test="null != timeStr and '' != timeStr">
            AND DATE_FORMAT(end_date,"%Y-%m") = DATE_FORMAT(#{timeStr}, "%Y-%m")
        </if>
        <if test="null == timeStr or '' == timeStr">
            AND DATE_FORMAT(end_date,"%Y-%m") = DATE_FORMAT(now(),"%Y-%m")
        </if>
        GROUP BY m.comm_id,m.manager_name
        ORDER BY end_date DESC) AS TEMP,
        (SELECT @x := 0,@y := NULL) AS TEMPRANK
        ORDER BY TEMP.satisfaction  DESC) AS TEMPFINAL
    </select>
    <!-- 查询管家满意度折线图 -->
    <select id="queryManagerScore" resultType = "java.util.HashMap">

        SELECT
        MAX(CASE TEMP.timeStr WHEN '01' THEN TEMP.score ELSE 0 END) `january`,
        MAX(CASE TEMP.timeStr WHEN '02' THEN TEMP.score ELSE 0 END) `february`,
        MAX(CASE TEMP.timeStr WHEN '03' THEN TEMP.score ELSE 0 END) `march`,
        MAX(CASE TEMP.timeStr WHEN '04' THEN TEMP.score ELSE 0 END) `april`,
        MAX(CASE TEMP.timeStr WHEN '05' THEN TEMP.score ELSE 0 END) `may`,
        MAX(CASE TEMP.timeStr WHEN '06' THEN TEMP.score ELSE 0 END) `june`,
        MAX(CASE TEMP.timeStr WHEN '07' THEN TEMP.score ELSE 0 END) `july`,
        MAX(CASE TEMP.timeStr WHEN '08' THEN TEMP.score ELSE 0 END) `august`,
        MAX(CASE TEMP.timeStr WHEN '09' THEN TEMP.score ELSE 0 END) `september`,
        MAX(CASE TEMP.timeStr WHEN '10' THEN TEMP.score ELSE 0 END) `october`,
        MAX(CASE TEMP.timeStr WHEN '11' THEN TEMP.score ELSE 0 END) `november`,
        MAX(CASE TEMP.timeStr WHEN '12' THEN TEMP.score ELSE 0 END) `december`
        FROM
        (SELECT
        any_value(DATE_FORMAT(end_date,"%m")) timeStr,
        ROUND(AVG(IFNULL(m.`satisfaction`,0)),2) score
        FROM
        fenghuang_manager_data m,
        fenghuang_comm c
        WHERE m.comm_id = c.erp_comm_id

        AND m.`comm_id` = #{commId}

        <if test="null != timeStr and '' != timeStr">
            AND DATE_FORMAT(end_date,"%Y") = DATE_FORMAT(#{timeStr}, "%Y")
        </if>
        <if test="null == timeStr or '' == timeStr">
            AND DATE_FORMAT(end_date,"%Y") = DATE_FORMAT(now(),"%Y")
        </if>
        GROUP BY DATE_FORMAT(m.end_date, "%Y-%m"),m.`comm_id`,m.`manager_name`) AS TEMP
    </select>
    <!-- 	<select id="queryManagerScore" resultType = "java.util.HashMap">
        SELECT
            any_value(DATE_FORMAT(end_date,"%m")) timeStr,
            ROUND(AVG(IFNULL(m.`satisfaction`,0)),2) score
            FROM
              fenghuang_manager_data m,
              fenghuang_comm c
            WHERE m.comm_id = c.erp_comm_id

            AND m.`comm_id` = #{commId}

            <if test="null != beginDate and '' != beginDate">
                AND DATE_FORMAT(end_date,"%Y-%m") &gt;= DATE_FORMAT(#{beginDate},"%Y-%m")
            </if>
            <if test="null == endDate or '' == endDate">
                AND DATE_FORMAT(end_date,"%Y-%m") &lt;= DATE_FORMAT(#{endDate},"%Y-%m")
            </if>
            GROUP BY m.`comm_id`
        </select> -->
    <!-- 查询区域排名 -->
    <!-- 	<select id="queryOrganRank" resultType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyRankModel">
            SELECT
              `pid`,
              `organId`,
              `organName`,
              `rankCurMon` AS `organRank`,
              `commNum`,
               `satisfactionRate`,
              `satisfy`,
              `bottleScore`,
              `monthUp`,
              `rankLastMon`,
              IFNULL(`rankLastMonStatus`,0) `rankLastMonStatus`,
              `rankCurMon`,
              IFNULL(`rankCurMonStatus`,0) `rankCurMonStatus`,
              ABS(`rankCurMon` - IFNULL(`rankLastMon`,0)) AS `rankChange`,
              IFNULL(`rankCurMonStatus`,0) AS `rankChangeStatus`,
              DATE_FORMAT(`month`,'%Y-%m-%d') timeStr
            FROM
              `micro_organ_rank`
            WHERE isDelete = 0
            <if test="null != timeStr and '' != timeStr">
                AND DATE_FORMAT(month,"%Y-%m") = DATE_FORMAT(#{timeStr}, "%Y-%m")
            </if>
            <if test="null == timeStr or '' == timeStr">
                AND DATE_FORMAT(month,"%Y-%m") = DATE_FORMAT(now(),"%Y-%m")
            </if>
            ORDER BY organRank ASC
        </select> -->
    <!-- 查询项目排名 -->
    <!-- 	<select id="queryCommRank" resultType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyRankModel">
            SELECT
              `pid`,
              `organId`,
              `organName`,
              `commId`,
               `satisfactionRate`,
              `commName`,
              `rankCurMon` AS `commRank`,
              `satisfy`,
              `bottleScore`,
              `monthUp`,
              `rankLastMon`,
              IFNULL(`rankLastMonStatus`,0) `rankLastMonStatus`,
              `rankCurMon`,
              IFNULL(`rankCurMonStatus`,0) `rankCurMonStatus`,
              ABS(`rankCurMon` - IFNULL(`rankLastMon`,0)) AS `rankChange`,
              IFNULL(`rankCurMonStatus`,0) AS `rankChangeStatus`,
              DATE_FORMAT(`month`,'%Y-%m-%d') timeStr
            FROM
              `micro_comm_rank`
            WHERE isDelete = 0
            <if test="null != timeStr and '' != timeStr">
                AND DATE_FORMAT(month,"%Y-%m") = DATE_FORMAT(#{timeStr}, "%Y-%m")
            </if>
            <if test="null == timeStr or '' == timeStr">
                AND DATE_FORMAT(month,"%Y-%m") = DATE_FORMAT(now(),"%Y-%m")
            </if>
            ORDER BY commRank ASC
        </select> -->
    <!-- 查询区域项目满意度基本信息 -->
    <select id="queryOrganSatisfyDetail" parameterType="String" resultType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyOrganDetailModel">
        select
        any_value(A.commId) commId,
        any_value(A.commName) commName,
        any_value(A.organId) organId,
        any_value(A.organName) organName,
        count(A.bussinessId) userNum,

        (select count(bussinessId) from `micro_satisfy_info`
        where DATE_FORMAT(finishTime, "%Y-%m") = DATE_FORMAT(any_value(A.finishTime), "%Y-%m")) effectiveNum,

        sum(case A.n1 when 3 then 1 else 0 end) permanentNum,
        sum(CASE A.n2 WHEN 1 THEN 1 ELSE 0 END) satisfyScore1,
        SUM(CASE A.n2 WHEN 2 THEN 1 ELSE 0 END) satisfyScore2,
        SUM(CASE A.n2 WHEN 3 THEN 1 ELSE 0 END) satisfyScore3,
        SUM(CASE A.n2 WHEN 4 THEN 1 ELSE 0 END) satisfyScore4,
        SUM(CASE A.n2 WHEN 5 THEN 1 ELSE 0 END) satisfyScore5,
        sum(case when A.f1 > 2 then 1 else 0 end) safeNum,
        SUM(CASE WHEN A.f2 > 2 THEN 1 ELSE 0 END) environmentNum,
        SUM(CASE WHEN A.f2 > 2 THEN 1 ELSE 0 END) serviceNum,
        SUM(CASE WHEN A.n2 > 3 THEN 1 ELSE 0 END) highUserNum,
        sum(case when A.n2 > 3 THEN 0 ELSE 1 END) unSatisfyNum,
        SUM(CASE A.n33 WHEN 0 THEN 0 ELSE 1 END) unFloor,
        SUM(CASE A.n35 WHEN 0 THEN 0 ELSE 1 END) unEstablish,
        SUM(CASE A.n36 WHEN 0 THEN 0 ELSE 1 END) unGreen,
        SUM(CASE A.n313 WHEN 0 THEN 0 ELSE 1 END) unEnvironment,
        SUM(CASE A.n315 WHEN 0 THEN 0 ELSE 1 END) unWaterClean,
        SUM(CASE A.n316 WHEN 0 THEN 0 ELSE 1 END) unRoomQuanlity,
        SUM(CASE A.n318 WHEN 0 THEN 0 ELSE 1 END) unCharge,
        SUM(CASE A.n319 WHEN 0 THEN 0 ELSE 1 END) unStatus,
        SUM(CASE A.n321 WHEN 0 THEN 0 ELSE 1 END) unElectric,
        SUM(CASE A.n398 WHEN 0 THEN 0 ELSE 1 END) unOther
        from
        `micro_satisfy_info` A
        where A.`status` = 1
        <if test="null != pid and '' != pid">
            AND A.pid = #{pid}
        </if>
        <if test="null != timeStr and '' != timeStr">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") = DATE_FORMAT(#{timeStr}, "%Y-%m")
        </if>
        <if test="null == timeStr or '' == timeStr">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") = DATE_FORMAT(NOW(), "%Y-%m")
        </if>
        group by A.commId, A.organId
        order by A.organId
    </select>
    <!-- 新增区域项目详情 -->
    <insert id="insertOrganCommDetail" parameterType="java.util.List">
        INSERT INTO `micro_satisfy_region_detail` (
        `id`,
        `pid`,
        `commId`,
        `organId`,
        `commName`,
        `organName`,
        `userNum`,
        `permanentNum`,
        `permanent`,
        `satisfyScore1`,
        `satisfyScore2`,
        `satisfyScore3`,
        `satisfyScore4`,
        `satisfyScore5`,
        `safeNum`,
        `environmentNum`,
        `serviceNum`,
        `highUserNum`,
        `topTwo`,
        `topThree`,
        `unSatisfyNum`,
        `unSafeManage`,
        `unPublicMaintenance`,
        `unEnvironment`,
        `unGreen`,
        `unEstablish`,
        `unFloor`,
        `unGuard`,
        `unGate`,
        `unWaterClean`,
        `unStatus`,
        `unOther`,
        `unCharge`,
        `unElectric`,
        `unRoomQuanlity`,
        `managerAwarenessRate`,
        `managerSatisfactionRate`,
        `month`,
        `createDate`,
        `createdBy`,
        `isDelete`,
        `status`
        )
        VALUES
        <foreach collection="list" item="item" index="index" separator="," >
            (
            UUID(),
            #{item.pid},
            #{item.commId},
            #{item.organId},
            #{item.commName},
            #{item.organName},
            #{item.userNum},
            #{item.permanentNum},
            #{item.permanent},
            #{item.satisfyScore1},
            #{item.satisfyScore2},
            #{item.satisfyScore3},
            #{item.satisfyScore4},
            #{item.satisfyScore5},
            #{item.safeNum},
            #{item.environmentNum},
            #{item.serviceNum},
            #{item.highUserNum},
            #{item.topTwo},
            #{item.topThree},
            #{item.unSatisfyNum},
            #{item.unSafeManage},
            #{item.unPublicMaintenance},
            #{item.unEnvironment},
            #{item.unGreen},
            #{item.unEstablish},
            #{item.unFloor},
            #{item.unGuard},
            #{item.unGate},
            #{item.unWaterClean},
            #{item.unStatus},
            #{item.unOther},
            #{item.unCharge},
            #{item.unElectric},
            #{item.unRoomQuanlity},
            #{item.managerAwarenessRate},
            #{item.managerSatisfactionRate},
            #{item.month},
            NOW(),
            #{item.createdBy},
            0,
            0
            )
        </foreach>
    </insert>
    <!-- 豁免并锁定 -->
    <!-- <update id="updateOragnCommDetail">
        UPDATE
          `micro_satisfy_region_detail` A
        SET
          A.`status` = 1,
          A.`createdBy` = #{userCode}
        <where>
            <foreach collection="list" index="index" item="item" separator="OR" open="(" close=")">
                A.`organId` = #{item.organId}
                AND A.`commId` = #{item.commId}
                AND A.`pid` = #{item.pid}
                AND DATE_FORMAT(A.`month`,"%Y-%m") = DATE_FORMAT(#{item.timeStr}, "%Y-%m")
            </foreach>
        </where>
    </update> -->

    <!-- 豁免并锁定 -->
    <update id="updateOragnCommDetail">
        UPDATE
        `micro_satisfy_info` A
        SET
        A.`ifLocking` = '1',
        A.`updateBy` = #{userCode}
        <where>
            <foreach collection="list" index="index" item="item" separator="OR" open="(" close=")">
                A.`organId` = #{item.organId}
                AND A.`commId` = #{item.commId}
                <!-- AND A.`pid` = #{item.pid} -->
                AND DATE_FORMAT(A.`finishTime`,"%Y-%m") = DATE_FORMAT(#{item.timeStr}, "%Y-%m")
            </foreach>
        </where>
    </update>
    <!-- 不考核 -->
    <update id="updateIsAssessmentComm">
        UPDATE
        `micro_satisfy_info` A
        SET
        A.`isAssessment` = '1',
        A.`updateBy` = #{userCode}
        <where>
            <foreach collection="list" index="index" item="item" separator="OR" open="(" close=")">
                A.`organId` = #{item.organId}
                AND A.`commId` = #{item.commId}
                <!-- AND A.`pid` = #{item.pid} -->
                AND DATE_FORMAT(A.`finishTime`,"%Y-%m") = DATE_FORMAT(#{item.timeStr}, "%Y-%m")
            </foreach>
        </where>
    </update>

    <!-- 查询区域项目详情列表by OrgaCode -->
    <select id="queryOrganSatisfyDetailByOrganId" resultType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyOrganDetailModel">
        select
        any_value(A.commId) commId,
        sum(any_value(A.commId)) commNum,
        any_value(A.commName) commName,
        any_value(A.organId) organId,
        any_value(A.organName) organName,
        any_value(A.permanent) permanent,
        sum(any_value(A.userNum)) userNum,
        sum(any_value(A.safeNum)) safeNum,
        sum(any_value(A.environmentNum)) environmentNum,
        sum(any_value(A.serviceNum)) serviceNum,
        sum(any_value(A.highUserNum)) highUserNum,
        any_value(A.`status`) status,
        sum(any_value(TEMP.recognitionPercentage)) managerAwarenessRate,
        sum(any_value(TEMP.satisfaction)) managerSatisfactionRate,
        any_value(A.topTwo) topTwo,
        any_value(A.topThree) topThree
        from
        `micro_satisfy_region_detail` A
        LEFT JOIN
        (SELECT
        any_value(m.organ_code) organCode,
        any_value(m.organ_name) organName,
        any_value(m.comm_id) commId,
        any_value(m.comm_name) commName,
        any_value(m.manager_name) managerName,
        ROUND(AVG(m.recognition_percentage), 2) recognitionPercentage,
        ROUND(AVG(m.satisfaction), 2) satisfaction
        FROM
        fenghuang_manager_data m,
        fenghuang_comm c
        WHERE m.comm_id = c.erp_comm_id
        <if test="null != organId and '' != organId">
            AND m.organ_code = #{organId}
        </if>
        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(m.end_date, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        </if>
        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(m.end_date, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        </if>
        <if test="null == beginDate or '' == beginDate">
            AND DATE_FORMAT(m.end_date, "%Y") = DATE_FORMAT(NOW(), "%Y")
        </if>
        GROUP BY m.comm_id) AS TEMP ON A.commId = TEMP.commId
        WHERE A.`isDelete` = 0
        <if test="null != organId and '' != organId">
            AND A.organId = #{organId}
        </if>
        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(A.month, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        </if>
        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(A.month, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        </if>
        <if test="null == beginDate or '' == beginDate">
            AND DATE_FORMAT(A.month, "%Y") = DATE_FORMAT(NOW(), "%Y")
        </if>
        group by A.commId
    </select>
    <!-- 查询区域排名-数据导入 -->
    <select id="queryOrganRankImport" resultType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyRankModel">
        SELECT
        TEMP.*,
        CASE
        WHEN @y = TEMP.satisfyOrign THEN @x
        WHEN @y := TEMP.satisfyOrign THEN @x :=@x + 1
        WHEN @y = 0 THEN @x :=@x + 1
        END
        AS rankCurMon FROM
        (SELECT
        any_value(A.pid) pid,
        any_value(A.organId) organId,
        any_value(A.organName) organName,
        COUNT(DISTINCT (A.commId)) commNum,
        ROUND(AVG(IFNULL(A.topTwo,0)+IFNULL(A.topThree,0)),2) satisfyOrign
        FROM
        `micro_satisfy_region_detail` AS A
        WHERE A.`status` = 0
        AND A.isDelete = 0
        <if test="null != timeStr and ''!= timeStr">
            AND DATE_FORMAT(A.`month`,"%Y-%m") = DATE_FORMAT(#{timeStr},"%Y-%m")
        </if>
        <if test="null == timeStr or ''== timeStr">
            AND DATE_FORMAT(A.`month`,"%Y-%m") = DATE_FORMAT(NOW(),"%Y-%m")
        </if>
        GROUP BY A.pid,A.organId
        ORDER BY satisfyOrign DESC) AS TEMP,
        (SELECT @x:=0,@y:= NULL) AS TEMPRANK
    </select>
    <!-- 查询项目排名-数据导入 -->
    <select id="queryCommRankImport" resultType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyRankModel">
        SELECT
        TEMP.*,
        CASE
        WHEN @y = TEMP.satisfyOrign THEN @x
        WHEN @y := TEMP.satisfyOrign THEN @x :=@x + 1
        WHEN @y = 0 THEN @x :=@x + 1
        END
        AS rankCurMon FROM
        (SELECT
        any_value(A.pid) pid,
        any_value(A.commId) commId,
        any_value(A.commName) commName,
        any_value(A.organId) organId,
        any_value(A.organName) organName,
        any_value(ROUND(CASE WHEN A.topTwo IS NULL THEN A.topThree ELSE A.topTwo END,2)) satisfyOrign
        FROM
        `micro_satisfy_region_detail` AS A
        WHERE A.`status` = 0
        AND A.isDelete = 0
        <if test="null != timeStr and ''!= timeStr">
            AND DATE_FORMAT(A.`month`,"%Y-%m") = DATE_FORMAT(#{timeStr},"%Y-%m")
        </if>
        <if test="null == timeStr or ''== timeStr">
            AND DATE_FORMAT(A.`month`,"%Y-%m") = DATE_FORMAT(NOW(),"%Y-%m")
        </if>
        GROUP BY A.pid,A.commId
        ORDER BY satisfyOrign DESC) AS TEMP,
        (SELECT @x:=0,@y:= NULL) AS TEMPRANK
    </select>
    <!-- 查询区域目标 -->
    <select id="queryOrganPointByOrganCode" parameterType="String" resultType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyPointModel">
        SELECT
        `organId`,
        `organName`,
        `crossPoint`,
        `basePoint`,
        `middePoint`,
        `highPoint`
        FROM
        `micro_satisfy_organ_point`
        WHERE organId = #{organId}
        AND isDelete = 0
    </select>
    <!-- 查询项目目标 -->
    <select id="queryCommPointByCommCode" parameterType="String" resultType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyPointModel">
        SELECT
        `commId`,
        `commName`,
        `crossPoint`,
        `basePoint`,
        `middePoint`,
        `highPoint`
        FROM
        `micro_satisfy_comm_point`
        WHERE commId = #{commId}
        AND isDelete = 0
    </select>
    
    
            <insert id="addCommObjectives"  parameterType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyCommModel">
        
INSERT INTO `micro_satisfy_comm_point` (
	`commId`,
	`commName`,
	`crossPoint`,
	`basePoint`,
	`middePoint`,
	`highPoint`,
	`isDelete`,
	`createdBy`,
	`createdTime`
)
VALUES
	(
		#{commId},
		#{commName},
		#{crossPoint},
		#{basePoint},
		#{middePoint},
		#{highPoint},
		#{isDelete},
		#{createdBy},
		NOW()
	)
    </insert>
    
    <update id="updateCommObjectives" parameterType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyCommModel">
    UPDATE `micro_satisfy_comm_point`
SET 
 `commName` = #{commName},
 `crossPoint` = #{crossPoint},
 `basePoint` = #{basePoint},
 `middePoint` = #{middePoint},
 `highPoint` = #{highPoint},
 `isDelete` = #{isDelete},
 `createdBy` = #{createdBy},
 `createdTime` = NOW()
WHERE
	`commId` = #{commId}
    
    </update>
    
    
    <!-- 查询区域上次排名信息 -->
    <select id="queryOrganRankModelById" parameterType="String" resultType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyRankModel">
        SELECT
        `organId`,
        `organName`,
        `satisfy`,
        `bottleScore`,
        `commNum`,
        `rankLastMon`,
        IFNULL(`rankLastMonStatus`,0) `rankLastMonStatus`,
        `rankCurMon`,
        IFNULL(`rankCurMonStatus`,0) `rankCurMonStatus`,
        `monthUp`
        FROM
        `micro_organ_rank`
        WHERE isDelete = 0
        AND organId = #{organId}
        AND DATE_FORMAT(`month`,'%Y-%m') &lt;= DATE_FORMAT(#{timeStr},'%Y-%m')
        ORDER BY createDate DESC
        LIMIT 1
    </select>
    <!-- 查询上次项目排名信息 -->
    <select id="queryCommRankModelById" parameterType="String" resultType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyRankModel">
        SELECT
        `organId`,
        `organName`,
        `satisfy`,
        `bottleScore`,
        `rankLastMon`,
        IFNULL(`rankLastMonStatus`,0) `rankLastMonStatus`,
        `rankCurMon`,
        IFNULL(`rankCurMonStatus`,0) `rankCurMonStatus`,
        `monthUp`
        FROM
        `micro_comm_rank`
        WHERE isDelete = 0
        AND organId = #{organId}
        AND commId = #{commId}
        AND DATE_FORMAT(`month`,'%Y-%m') &lt;= DATE_FORMAT(#{timeStr},'%Y-%m')
        ORDER BY createDate DESC
        LIMIT 1
    </select>
    <!-- 新增区域排名信息 -->
    <insert id="addOrganRank">
        INSERT INTO `micro_organ_rank` (
        `ID`,
        `pid`,
        `organId`,
        `organName`,
        `satisfy`,
        `bottleScore`,
        `commNum`,
        `rankLastMon`,
        `rankLastMonStatus`,
        `rankCurMon`,
        `rankCurMonStatus`,
        `monthUp`,
        `satisfactionRate`,
        `isDelete`,
        `month`,
        `createDate`,
        `createdBy`
        )
        VALUES
        <foreach collection="list" item="item" index="index" separator="," >
            (
            UUID(),
            #{item.pid},
            #{item.organId},
            #{item.organName},
            #{item.satisfy},
            #{item.bottleScore},
            #{item.commNum},
            #{item.rankLastMon},
            #{item.rankLastMonStatus},
            #{item.rankCurMon},
            #{item.rankCurMonStatus},
            #{item.monthUp},
            #{item.satisfyOrign},
            0,
            #{item.month},
            NOW(),
            #{item.createdBy}
            )
        </foreach>
    </insert>
    <!-- 新增项目排名信息 -->
    <insert id="addCommRank">
        INSERT INTO `micro_comm_rank` (
        `ID`,
        `pid`,
        `organId`,
        `organName`,
        `commId`,
        `commName`,
        `satisfy`,
        `bottleScore`,
        `rankLastMon`,
        `rankLastMonStatus`,
        `rankCurMon`,
        `rankCurMonStatus`,
        `monthUp`,
        `satisfactionRate`,
        `isDelete`,
        `month`,
        `createDate`,
        `createdBy`
        )
        VALUES
        <foreach collection="list" item="item" index="index" separator="," >
            (
            UUID(),
            #{item.pid},
            #{item.organId},
            #{item.organName},
            #{item.commId},
            #{item.commName},
            #{item.satisfy},
            #{item.bottleScore},
            #{item.rankLastMon},
            #{item.rankLastMonStatus},
            #{item.rankCurMon},
            #{item.rankCurMonStatus},
            #{item.monthUp},
            #{item.satisfyOrign},
            0,
            #{item.month},
            NOW(),
            #{item.createdBy}
            )
        </foreach>
    </insert>
    <!-- 清空区域项目信息基础表 -->
    <update id="clearOrganDetailByTimeStr">
        UPDATE
        `micro_satisfy_region_detail`
        SET isDelete = 1
        WHERE DATE_FORMAT(`month`,'%Y-%m') = DATE_FORMAT(#{timeStr},'%Y-%m')
        AND isDelete = 0
    </update>
    <!-- 清空区域排名表 -->
    <update id="clearOrganRankByTimeStr">
        UPDATE
        `micro_organ_rank`
        SET isDelete = 1
        WHERE DATE_FORMAT(`month`,'%Y-%m') = DATE_FORMAT(#{timeStr},'%Y-%m')
        AND isDelete = 0
    </update>
    <!-- 清空项目排名表 -->
    <update id="clearCommRankByTimeStr">
        UPDATE
        `micro_comm_rank`
        SET isDelete = 1
        WHERE DATE_FORMAT(`month`,'%Y-%m') = DATE_FORMAT(#{timeStr},'%Y-%m')
        AND isDelete = 0
    </update>
    <!-- 覆盖当月已导入当月数据 -->
    <update id="deleteImport">
        DELETE FROM micro_satisfy_info
        WHERE
        DATE_FORMAT(finishTime,'%Y-%m')=DATE_FORMAT(#{timeStr},'%Y-%m')
    </update>
    <!-- 查询当月导入数据统计信息 -->
    <!--   ROUND(AVG(IFNULL(A.topTwo,0)+IFNULL(A.topThree,0)),2) score, -->
    <select id="queryDataCount" resultType="java.util.HashMap">
        select
        count(A.bussinessId) userNum,
        COUNT(DISTINCT (A.organId)) organNum,
        COUNT(DISTINCT(A.commId)) commNum,
        MAX(DATE_FORMAT(A.`finishTime`,'%Y-%m-%d')) timeStr
        from
        `micro_satisfy_info` A
        where A.`status` = 1
        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        </if>
        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        </if>


        <if test="null != organId and '' != organId">
            AND A.organId = #{organId}
        </if>


        GROUP BY DATE_FORMAT(A.`finishTime`,'%Y')


    </select>
    <!-- 查询当年全国得分走势图 -->
    <!-- <select id="queryCountryScore" resultType="java.util.HashMap">
        SELECT
        MAX(CASE TEMP.timeStr WHEN '01' THEN TEMP.score ELSE 0 END) `january`,
        MAX(CASE TEMP.timeStr WHEN '02' THEN TEMP.score ELSE 0 END) `february`,
        MAX(CASE TEMP.timeStr WHEN '03' THEN TEMP.score ELSE 0 END) `march`,
        MAX(CASE TEMP.timeStr WHEN '04' THEN TEMP.score ELSE 0 END) `april`,
        MAX(CASE TEMP.timeStr WHEN '05' THEN TEMP.score ELSE 0 END) `may`,
        MAX(CASE TEMP.timeStr WHEN '06' THEN TEMP.score ELSE 0 END) `june`,
        MAX(CASE TEMP.timeStr WHEN '07' THEN TEMP.score ELSE 0 END) `july`,
        MAX(CASE TEMP.timeStr WHEN '08' THEN TEMP.score ELSE 0 END) `august`,
        MAX(CASE TEMP.timeStr WHEN '09' THEN TEMP.score ELSE 0 END) `september`,
        MAX(CASE TEMP.timeStr WHEN '10' THEN TEMP.score ELSE 0 END) `october`,
        MAX(CASE TEMP.timeStr WHEN '11' THEN TEMP.score ELSE 0 END) `november`,
        MAX(CASE TEMP.timeStr WHEN '12' THEN TEMP.score ELSE 0 END) `december`
        FROM
        (
        SELECT
        any_value(DATE_FORMAT(A.`month`,'%m')) timeStr,
        ROUND(AVG(IFNULL(A.topTwo,0)+IFNULL(A.topThree,0)),2) score
        FROM `micro_satisfy_region_detail` A
        WHERE A.`status` = 0
        AND A.`isDelete` = 0
        <if test="null != timeStr and '' != timeStr">
            AND DATE_FORMAT(A.`month`,'%Y') = DATE_FORMAT(#{timeStr},'%Y')
        </if>
        <if test="null == timeStr or '' == timeStr">
            AND DATE_FORMAT(A.`month`,'%Y') = DATE_FORMAT(NOW(),'%Y')
        </if>
        GROUP BY DATE_FORMAT(A.`month`,'%Y-%m')
        ORDER BY timeStr ASC
        ) AS TEMP
    </select> -->
    <select id="queryCountryScore" resultType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyOrganDetailModel">
        select
        count(A.bussinessId) userNum,
        any_value(A.organId) organId,
        count(DISTINCT (A.commId)) commNum,
        any_value(DATE_FORMAT(A.finishTime, "%Y-%m")) month,
        sum(case when A.f1 > 2 then 1 else 0 end) safeNum,
        SUM(CASE WHEN A.f2 > 2 THEN 1 ELSE 0 END) environmentNum,
        SUM(CASE WHEN A.f3 > 2 THEN 1 ELSE 0 END) serviceNum,
        SUM(CASE WHEN A.n2 > 3 THEN 1 ELSE 0 END) highUserNum
        from
        `micro_satisfy_info` A
        where A.`status` = 1
        and  A.ifLocking= '0'
        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        </if>
        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        </if>

        <if test="null != Identification and '' != Identification">
            AND A.quality=#{Identification}
        </if>


        <if test="null != organId and '' != organId">
            AND A.organId = #{organId}
        </if>


        GROUP BY DATE_FORMAT(A.finishTime, "%Y-%m")
    </select>

    <!-- 查询区域导入数据统计信息 -->
    <select id="queryOrganCount" resultType="java.util.HashMap">
        SELECT
        COUNT(DISTINCT (A.organId)) organNum,
        COUNT(DISTINCT(A.commId)) commNum,
        (SELECT ROUND(AVG(B.satisfy),2) FROM `micro_organ_rank` AS B
        WHERE B.organId = #{organId}
        AND DATE_FORMAT(B.`month`,'%Y-%m') = DATE_FORMAT(#{timeStr},'%Y-%m')
        GROUP BY B.organId) score,
        (SELECT C.organName FROM `micro_organ_rank` AS C
        WHERE C.organId = #{organId}
        AND DATE_FORMAT(C.`month`,'%Y-%m') = DATE_FORMAT(#{timeStr},'%Y-%m')
        LIMIT 1) organName,
        any_value(DATE_FORMAT(A.`month`,'%Y-%m-%d')) timeStr,
        any_value(A.pid) pid
        FROM `micro_satisfy_region_detail` A
        WHERE A.`status` = 0
        AND A.`isDelete` = 0
        <if test="null != pid and '' != pid">
            AND A.pid = #{pid}
        </if>
        <if test="null != timeStr and '' != timeStr">
            AND DATE_FORMAT(A.`month`,'%Y-%m') = DATE_FORMAT(#{timeStr},'%Y-%m')
            AND DATE_FORMAT(A.`month`,'%Y') = DATE_FORMAT(#{timeStr},'%Y')
        </if>
        <if test="null == timeStr or '' == timeStr">
            AND DATE_FORMAT(A.`month`,'%Y') = DATE_FORMAT(NOW(),'%Y')
        </if>
        GROUP BY DATE_FORMAT(A.`month`,'%Y-%m')
        ORDER BY timeStr DESC LIMIT 1
    </select>
    <!-- 查询区域导入数据统计信息 -->
    <select id="queryCommCount" resultType="java.util.HashMap">
        SELECT
        any_value(A.organId) organId,
        any_value(A.commId) commId,
        any_value(DATE_FORMAT(A.`month`,'%Y-%m-%d')) timeStr,
        any_value(A.pid) pid,
        any_value(A.commName) commName,

        ROUND(AVG(IFNULL(A.topTwo,0)+IFNULL(A.topThree,0)),2) satisfyOrign

        FROM  `micro_satisfy_region_detail` A
        WHERE A.`status` = 0
        AND A.`isDelete` = 0
        <if test="null != commId and '' != commId">
            AND A.commId = #{commId}
        </if>
        <if test="null != organId and '' != organId">
            AND A.organId = #{organId}
        </if>
        <if test="null != timeStr and '' != timeStr">
            AND DATE_FORMAT(A.`month`,'%Y-%m') = DATE_FORMAT(#{timeStr},'%Y-%m')
            AND DATE_FORMAT(A.`month`,'%Y') = DATE_FORMAT(#{timeStr},'%Y')
        </if>
        <if test="null == timeStr or '' == timeStr">
            AND DATE_FORMAT(A.`month`,'%Y') = DATE_FORMAT(NOW(),'%Y')
        </if>
        GROUP BY DATE_FORMAT(A.`month`,'%Y-%m')
        ORDER BY timeStr DESC LIMIT 1
    </select>


    <!-- 查询区域走势图 -->
    <select id="queryOrganScore" resultType="java.util.HashMap">
        SELECT
        MAX(CASE TEMP.timeStr WHEN '01' THEN TEMP.score ELSE 0 END) `january`,
        MAX(CASE TEMP.timeStr WHEN '02' THEN TEMP.score ELSE 0 END) `february`,
        MAX(CASE TEMP.timeStr WHEN '03' THEN TEMP.score ELSE 0 END) `march`,
        MAX(CASE TEMP.timeStr WHEN '04' THEN TEMP.score ELSE 0 END) `april`,
        MAX(CASE TEMP.timeStr WHEN '05' THEN TEMP.score ELSE 0 END) `may`,
        MAX(CASE TEMP.timeStr WHEN '06' THEN TEMP.score ELSE 0 END) `june`,
        MAX(CASE TEMP.timeStr WHEN '07' THEN TEMP.score ELSE 0 END) `july`,
        MAX(CASE TEMP.timeStr WHEN '08' THEN TEMP.score ELSE 0 END) `august`,
        MAX(CASE TEMP.timeStr WHEN '09' THEN TEMP.score ELSE 0 END) `september`,
        MAX(CASE TEMP.timeStr WHEN '10' THEN TEMP.score ELSE 0 END) `october`,
        MAX(CASE TEMP.timeStr WHEN '11' THEN TEMP.score ELSE 0 END) `november`,
        MAX(CASE TEMP.timeStr WHEN '12' THEN TEMP.score ELSE 0 END) `december`
        FROM
        (SELECT
        any_value(DATE_FORMAT(A.`month`,'%m')) timeStr,
        ROUND(AVG(A.satisfy),2) score
        FROM `micro_organ_rank` A
        WHERE A.`isDelete` = 0
        AND A.`organId` = #{organId}
        <if test="null != pid and '' != pid">
            AND A.pid = #{pid}
        </if>
        <if test="null != timeStr and '' != timeStr">
            AND DATE_FORMAT(A.`month`,'%Y') = DATE_FORMAT(#{timeStr},'%Y')
        </if>
        <if test="null == timeStr or '' == timeStr">
            AND DATE_FORMAT(A.`month`,'%Y') = DATE_FORMAT(NOW(),'%Y')
        </if>
        GROUP BY DATE_FORMAT(A.`month`,'%Y-%m')
        ORDER BY timeStr ASC) AS TEMP
    </select>
    <!-- 查询项目走势图 -->
    <!-- 	<select id="queryCommScore" resultType="java.util.HashMap">
            SELECT
            MAX(CASE TEMP.timeStr WHEN '01' THEN TEMP.score ELSE 0 END) `january`,
            MAX(CASE TEMP.timeStr WHEN '02' THEN TEMP.score ELSE 0 END) `february`,
            MAX(CASE TEMP.timeStr WHEN '03' THEN TEMP.score ELSE 0 END) `march`,
            MAX(CASE TEMP.timeStr WHEN '04' THEN TEMP.score ELSE 0 END) `april`,
            MAX(CASE TEMP.timeStr WHEN '05' THEN TEMP.score ELSE 0 END) `may`,
            MAX(CASE TEMP.timeStr WHEN '06' THEN TEMP.score ELSE 0 END) `june`,
            MAX(CASE TEMP.timeStr WHEN '07' THEN TEMP.score ELSE 0 END) `july`,
            MAX(CASE TEMP.timeStr WHEN '08' THEN TEMP.score ELSE 0 END) `august`,
            MAX(CASE TEMP.timeStr WHEN '09' THEN TEMP.score ELSE 0 END) `september`,
            MAX(CASE TEMP.timeStr WHEN '10' THEN TEMP.score ELSE 0 END) `october`,
            MAX(CASE TEMP.timeStr WHEN '11' THEN TEMP.score ELSE 0 END) `november`,
            MAX(CASE TEMP.timeStr WHEN '12' THEN TEMP.score ELSE 0 END) `december`
            FROM
            (SELECT
            any_value(DATE_FORMAT(A.`month`,'%m')) timeStr,
            ROUND(AVG(A.satisfy),2) score
            FROM `micro_comm_rank` A
            WHERE A.`isDelete` = 0
            AND A.`organId` = #{organId}
            AND A.`commId` = #{commId}
            <if test="null != pid and '' != pid">
                AND A.pid = #{pid}
            </if>
            <if test="null != timeStr and '' != timeStr">
                AND DATE_FORMAT(A.`month`,'%Y') = DATE_FORMAT(#{timeStr},'%Y')
            </if>
            <if test="null == timeStr or '' == timeStr">
                AND DATE_FORMAT(A.`month`,'%Y') = DATE_FORMAT(NOW(),'%Y')
            </if>
            GROUP BY DATE_FORMAT(A.`month`,'%Y-%m')
            ORDER BY timeStr ASC) AS TEMP
        </select> -->



    <select id="queryCommScore" resultType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyOrganDetailModel">
        select
        count(A.bussinessId) userNum,
        any_value(A.quality) quality,
       any_value(A.ifLocking) ifLocking,
        any_value(DATE_FORMAT(A.finishTime, "%Y-%m")) month,
        sum(case when A.f1 > 2 then 1 else 0 end) safeNum,
        SUM(CASE WHEN A.f2 > 2 THEN 1 ELSE 0 END) environmentNum,
        SUM(CASE WHEN A.f3 > 2 THEN 1 ELSE 0 END) serviceNum,
        SUM(CASE WHEN A.n2 > 3 THEN 1 ELSE 0 END) highUserNum
        from
        `micro_satisfy_info` A
        where A.`status` = 1
        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        </if>
        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        </if>
        <if test="null != commId and '' != commId">
            AND A.commId = #{commId}
        </if>
        <if test="null != organId and '' != organId">
            AND A.organId = #{organId}
        </if>

        GROUP BY DATE_FORMAT(A.finishTime, "%Y-%m")
    </select>


    <!-- 查询项目详情by OrgaCode commId-->
    <select id="queryCommDetail" resultType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyOrganDetailModel">
        select
        any_value(A.commId) commId,
        any_value(A.commName) commName,
        any_value(A.organId) organId,
        any_value(A.organName) organName,
        any_value(A.userNum) userNum,
        any_value(A.permanent) permanent,
        any_value(A.permanentNum) permanentNum,
        ROUND(AVG(IFNULL(A.topTwo,0)),2) topTwo,
        ROUND(AVG(IFNULL(A.topThree,0)),2) topThree,
        any_value(A.satisfyScore1) satisfyScore1,
        any_value(A.satisfyScore2) satisfyScore2,
        any_value(A.satisfyScore3) satisfyScore3,
        any_value(A.satisfyScore4) satisfyScore4,
        any_value(A.satisfyScore5) satisfyScore5,
        any_value(A.unSatisfyNum) unSatisfyNum,
        any_value(A.unSafeManage) unSafeManage,
        any_value(A.unPublicMaintenance) unPublicMaintenance,
        any_value(A.unGuard) unGuard,
        any_value(A.unGate) unGate,
        any_value(A.unFloor) unFloor,
        any_value(A.unEstablish) unEstablish,
        any_value(A.unGreen) unGreen,
        any_value(A.unEnvironment) unEnvironment,
        any_value(A.unWaterClean) unWaterClean,
        any_value(A.unRoomQuanlity) unRoomQuanlity,
        any_value(A.unCharge) unCharge,
        any_value(A.unStatus) unStatus,
        any_value(A.unElectric) unElectric,
        any_value(A.unOther) unOther,
        any_value(A.`status`) `status`,
        any_value(TEMP.recognitionPercentage)  managerAwarenessRate,
        any_value(TEMP.satisfaction)  managerSatisfactionRate
        from
        `micro_satisfy_region_detail` A
        LEFT JOIN
        (SELECT
        any_value(m.organ_code) organCode,
        any_value(m.organ_name) organName,
        any_value(m.comm_id) commId,
        any_value(m.comm_name) commName,
        any_value(m.manager_name) managerName,
        ROUND(AVG(m.recognition_percentage), 2) recognitionPercentage,
        ROUND(AVG(m.satisfaction), 2) satisfaction
        FROM
        fenghuang_manager_data m,
        fenghuang_comm c
        WHERE m.comm_id = c.erp_comm_id
        <if test="null != timeStr and '' != timeStr">
            AND DATE_FORMAT(m.end_date, "%Y-%m") = DATE_FORMAT(#{timeStr}, "%Y-%m")
        </if>
        <if test="null == timeStr or '' == timeStr">
            AND DATE_FORMAT(m.end_date, "%Y-%m") = DATE_FORMAT(NOW(), "%Y-%m")
        </if>
        GROUP BY m.comm_id) AS TEMP ON A.commId = TEMP.commId
        WHERE A.`isDelete` = 0
        <if test="null != pid and '' != pid">
            AND A.pid = #{pid}
        </if>
        <if test="null != timeStr and '' != timeStr">
            AND DATE_FORMAT(A.month, "%Y-%m") = DATE_FORMAT(#{timeStr}, "%Y-%m")
        </if>
        <if test="null == timeStr or '' == timeStr">
            AND DATE_FORMAT(A.month, "%Y-%m") = DATE_FORMAT(NOW(), "%Y-%m")
        </if>
        AND A.organId = #{organId}
        AND A.commId = #{commId}
        GROUP BY A.commId
    </select>
    <!-- 查询数据转换基础数据 -->
    <select id="queryDataImportBase" resultType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyModel">
        SELECT DISTINCT
        B.`bussinessId`,
        B.`paperName` questionNaire,
        B.`finishTime`,
        B.`organId`,
        B.`status`,
        B.`roomName`,
        B.`custName`,
        B.`mobile`,
        B.`receiveName`,
        B.`commId`,
        B.`organName`,
        B.`commName`
        FROM
        `micro_satisfy_orgin_info` B
    </select>
    <!-- 根据问题ID,工单ID查询答案信息 -->
    <select id="queryBaseAnswer" resultType="java.lang.String">
        SELECT
        CASE
        WHEN answer IS NULL
        THEN answerContent
        ELSE answer
        END
        FROM
        `micro_satisfy_orgin_info`
        WHERE bussinessId = #{bussinessId}
        AND answerId = #{answerId}
    </select>
    <!-- 转换成功后清理基础数据 -->
    <update id="clearBaseData">
        TRUNCATE TABLE `micro_satisfy_orgin_info`
    </update>

    <update id="updateQuality" >
        UPDATE micro_satisfy_info SET quality=#{quality} WHERE commId=#{commId} AND organId=#{organId} AND DATE_FORMAT(finishTime, "%Y-%m") = DATE_FORMAT(str_to_date(#{cruentDate},"%Y-%m-%d"), "%Y-%m")
    </update>

    <!-- AND DATE_FORMAT(finishTime, "%Y-%m") = DATE_FORMAT(str_to_date(#{cruentDate},"%Y-%m-%d"), "%Y-%m") -->

    <select id="selectVoice" resultType="java.util.HashMap">
        SELECT
        A.voice voice,
        count(A.voice) voiceCount
        FROM
        micro_satisfy_info A
        WHERE
        A.status = 1

        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        </if>
        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        </if>


        <if test="null != commId and '' != commId">
            AND A.commId = #{commId}
        </if>
        <if test="null != organId and '' != organId">
            AND A.organId = #{organId}
        </if>
        GROUP BY A.voice
    </select>

    <select id="scoreDistribution" resultType="java.util.HashMap">
        SELECT
        CONCAT(msi.n2,'分') score,
        count(msi.uuid) count
        FROM
        micro_satisfy_info msi
        WHERE
        msi.commId = #{commId} AND msi.status = 1
        GROUP BY msi.n2
    </select>

    <select id="countTake" resultType="java.lang.Integer">
        SELECT
        COUNT(msi.uuid)
        FROM
        micro_satisfy_info msi
        WHERE
        msi.commId = #{commId} AND msi.status = 1
    </select>
    <select id="countNotTake" resultType="java.lang.Integer">
        SELECT
        COUNT(msi.uuid)
        FROM
        micro_satisfy_info msi
        WHERE
        msi.commId = #{commId}
        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(msi.finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        </if>
        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(msi.finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        </if>
        AND  msi.status != 1
    </select>
    <select id="queryQuality" resultType="java.lang.Integer">
        SELECT
        msi.quality
        FROM
        micro_satisfy_info msi
        WHERE
        msi.commId = #{commId}
        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(msi.finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        </if>
        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(msi.finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        </if>
        limit 1
    </select>
    <select id="countExempt" resultType="java.lang.Integer">
        SELECT
        COUNT(msi.id)
        FROM
        micro_satisfy_region_detail msi
        WHERE
        msi.commId = #{commId}
        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(msi.`month`, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        </if>
        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(msi.`month`, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        </if>
        AND msi.status = 1
    </select>

    <!-- 查询全国排名 -->
    <select id="queryNationalRankings" resultType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyRankModel">
        SELECT
        TEMP.*,
        CASE
        WHEN @y = TEMP.satisfyOrign THEN @x
        WHEN @y := TEMP.satisfyOrign THEN @x :=@x + 1
        WHEN @y = 0 THEN @x :=@x + 1
        END
        AS rankCurMon FROM
        (SELECT
        any_value(A.pid) pid,
        any_value(A.organId) organId,
        any_value(A.organName) organName,
        any_value(A.commId) commId,
        ROUND(AVG(IFNULL(A.topTwo,0)+IFNULL(A.topThree,0)),2) satisfyOrign
        FROM
        `micro_satisfy_region_detail` AS A
        WHERE A.`status` = 0
        AND A.isDelete = 0
        <if test="null != timeStr and ''!= timeStr">
            AND DATE_FORMAT(A.`month`,"%Y-%m") = DATE_FORMAT(#{timeStr},"%Y-%m")
        </if>
        <if test="null == timeStr or ''== timeStr">
            AND DATE_FORMAT(A.`month`,"%Y-%m") = DATE_FORMAT(NOW(),"%Y-%m")
        </if>
        GROUP BY A.commId
        ORDER BY satisfyOrign DESC) AS TEMP,
        (SELECT @x:=0,@y:= NULL) AS TEMPRANK
    </select>

    <sql id="getSatisfaction">
        SELECT
        any_value(A.organId) organId,
        count(DISTINCT (A.commId)) commNum,
        any_value(A.organName) organName,
        sum(any_value(A.safeNum)) safeNum,
        sum(any_value(A.environmentNum)) environmentNum,
        sum(any_value(A.serviceNum)) serviceNum,
        sum(any_value(A.highUserNum)) highUserNum,
        sum(any_value(A.userNum)) userNum
        FROM `micro_satisfy_region_detail` A
        WHERE
    </sql>

    <!-- 查询当年全国季度得分走势图 -->
    <!-- 	<select id="queryNationalQuarterScore" resultType="java.util.HashMap">

             <include refid="getSatisfaction"></include>

         A.`status` = 0
            AND A.`isDelete` = 0
            <if test="null != parameter and '' != parameter">
            AND	(topThree is not null and topThree!='')
            </if>
            <if test="null == parameter or '' == parameter">
            AND	(A.topThree is null or A.topThree='')
            </if>

            <if test="null != beginDate and '' != beginDate">
                AND DATE_FORMAT(A.`month`,'%Y-%m') &gt;= DATE_FORMAT(#{beginDate},'%Y-%m')
            </if>
            <if test="null != endDate and '' != endDate">
                AND DATE_FORMAT(A.`month`,'%Y-%m') &lt;= DATE_FORMAT(#{endDate},'%Y-%m')
            </if>

            <if test="null != group and '' != group">
            GROUP BY A.`organId`
            </if>
            <if test="null == group or '' == group">
            GROUP BY DATE_FORMAT(A.`month`,'%Y')
            </if>


        </select> -->


    <!-- 季度 满意度折线图 -->
    <select id="queryCountryQuarterScore" resultType="java.util.HashMap">
        select
        count(DISTINCT (A.commId)) commNum,
        count(A.bussinessId) userNum,
        sum(case when A.f1 > 2 then 1 else 0 end) safeNum,
        SUM(CASE WHEN A.f2 > 2 THEN 1 ELSE 0 END) environmentNum,
        SUM(CASE WHEN A.f3 > 2 THEN 1 ELSE 0 END) serviceNum,
        SUM(CASE WHEN A.n2 > 3 THEN 1 ELSE 0 END) highUserNum
        from
        `micro_satisfy_info` A
        where A.`status` = 1
        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        </if>
        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        </if>
        <if test="null != Identification and '' != Identification">
            AND A.quality=#{Identification}
        </if>

        <if test="null != organId and '' != organId">
            AND A.organId = #{organId}
        </if>

        GROUP BY DATE_FORMAT(A.`finishTime`,'%Y')
    </select>


    <!-- 查询全年全国的-得分走势图 -->
    <!-- 	<select id="queryCountryNationwideScore" resultType="java.util.HashMap">

        <include refid="getSatisfaction"></include>

             A.`status` = 0
            AND A.`isDelete` = 0
            <if test="null != parameter and '' != parameter">
            AND	(topThree is not null and topThree!='')
            </if>
            <if test="null == parameter or '' == parameter">
            AND	(A.topThree is null or A.topThree='')
            </if>

            <if test="null != timeStr and '' != timeStr">
                AND DATE_FORMAT(A.`month`,'%Y') = DATE_FORMAT(#{timeStr},'%Y')
            </if>
            GROUP BY DATE_FORMAT(A.`month`,'%Y')
        </select> -->


    <!-- 查询当年项目季度得分走势图 -->
    <!-- 	<select id="queryProjectScore" resultType="java.util.HashMap">
            SELECT
            sum(any_value(A.safeNum)) safeNum,
            sum(any_value(A.environmentNum)) environmentNum,
            sum(any_value(A.serviceNum)) serviceNum,
            sum(any_value(A.highUserNum)) highUserNum,
            sum(any_value(A.userNum)) userNum,
            any_value(A.topTwo)  topTwo,
            any_value(A.topThree)  topThree
            FROM `micro_satisfy_region_detail` A
            WHERE

         A.`status` = 0
            AND A.`isDelete` = 0
            <if test="null != commId and '' != commId">
                AND A.commId = #{commId}
            </if>
           <if test="null != organId and '' != organId">
                AND A.organId = #{organId}
            </if>

            <if test="null != beginDate and '' != beginDate">
                AND DATE_FORMAT(A.`month`,'%Y-%m') &gt;= DATE_FORMAT(#{beginDate},'%Y-%m')
            </if>
            <if test="null != endDate and '' != endDate">
                AND DATE_FORMAT(A.`month`,'%Y-%m') &lt;= DATE_FORMAT(#{endDate},'%Y-%m')
            </if>
            GROUP BY DATE_FORMAT(A.`month`,'%Y')

        </select> -->

    <!-- 查询项目全国排名所需要的参数 -->
    <!-- 	<select id="queryProjectParameter" resultType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyOrganDetailModel">
            SELECT

            any_value(A.commName) commName,
            any_value(A.commId) commId,
            any_value(A.organId) organId,
            sum(any_value(A.safeNum)) safeNum,
            sum(any_value(A.environmentNum)) environmentNum,
            sum(any_value(A.serviceNum)) serviceNum,
            sum(any_value(A.highUserNum)) highUserNum,
            sum(any_value(A.userNum)) userNum,
            any_value(A.topTwo)  topTwo,
            any_value(A.topThree)  topThree
            FROM `micro_satisfy_region_detail` A
            WHERE

             A.`status` = 0
            AND A.`isDelete` = 0
            <if test="null != beginDate and '' != beginDate">
                AND DATE_FORMAT(A.`month`,'%Y-%m') &gt;= DATE_FORMAT(#{beginDate},'%Y-%m')
            </if>
            <if test="null != endDate and '' != endDate">
                AND DATE_FORMAT(A.`month`,'%Y-%m') &lt;= DATE_FORMAT(#{endDate},'%Y-%m')
            </if>
            GROUP BY A.`commId`

        </select> -->


    <!-- 查询季度区域排名所需要的参数 -->
    <!-- <select id="queryRegionalRankingParameter" resultType="java.util.HashMap">

         <include refid="getSatisfaction"></include>

        A.`status` = 0
        AND A.`isDelete` = 0
        <if test="null != parameter and '' != parameter">
        AND	(topThree is not null and topThree!='')
        </if>
        <if test="null == parameter or '' == parameter">
        AND	(A.topThree is null or A.topThree='')
        </if>

        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(A.`month`,'%Y-%m') &gt;= DATE_FORMAT(#{beginDate},'%Y-%m')
        </if>
        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(A.`month`,'%Y-%m') &lt;= DATE_FORMAT(#{endDate},'%Y-%m')
        </if>
        GROUP BY A.`organId`
    </select> -->

    <!-- 季度区域查询 公用,
           -->
    <select id="queryPublic" resultType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyOrganDetailModel">
        select
        any_value(A.commId) commId,
        any_value(A.commName) commName,
        any_value(A.organId) organId,
        any_value(A.organName) organName,
        (select count(DISTINCT (commId)) from `micro_satisfy_info` where
        DATE_FORMAT(finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        AND DATE_FORMAT(finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        AND organId = any_value(A.organId)
        group by organId
        ) commNum,
        count(A.bussinessId) userNum,
        sum(case when A.f1 > 2 then 1 else 0 end) safeNum,
        SUM(CASE WHEN A.f2 > 2 THEN 1 ELSE 0 END) environmentNum,
        SUM(CASE WHEN A.f3 > 2 THEN 1 ELSE 0 END) serviceNum,
        SUM(CASE WHEN A.n2 > 3 THEN 1 ELSE 0 END) highUserNum
        from
        `micro_satisfy_info` A
        where A.`status` = 1
        and  A.ifLocking= '0'
        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        </if>
        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        </if>
        <if test="null != Identification and '' != Identification">
            AND A.quality=#{Identification}
        </if>

        <if test="null != organId and '' != organId">
            AND A.organId = #{organId}
        </if>


        group by A.organId
        order by A.organId
    </select>


    <!-- 季度项目查询 公用,
       -->
    <select id="queryPublicProject" resultType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyOrganDetailModel">
        select
        max(DATE_FORMAT(any_value(A.finishTime),"%Y-%m-%d")) createDate,
        any_value(A.quality) quality,
        any_value(A.commId) commId,
        any_value(A.ifLocking)  ifLocking,
        any_value(A.commName) commName,
        any_value(A.organId) organId,
        any_value(A.organName) organName,
        count(A.bussinessId) userNum,
        sum(case when A.f1 > 2 then 1 else 0 end) safeNum,
        SUM(CASE WHEN A.f2 > 2 THEN 1 ELSE 0 END) environmentNum,
        SUM(CASE WHEN A.f3 > 2 THEN 1 ELSE 0 END) serviceNum,
        SUM(CASE WHEN A.n2 > 3 THEN 1 ELSE 0 END) highUserNum
        from
        `micro_satisfy_info` A
        where A.`status` = 1
        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        </if>
        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        </if>
        <if test="null != Identification and '' != Identification">
            AND A.quality=#{Identification}
        </if>


        <if test="null != organId and '' != organId">
            AND A.organId = #{organId}
        </if>

        <if test="innIds != null and innIds.size > 0">
            and A.commId in
            <foreach collection="innIds" index="index" open="(" close=")" separator="," item="item">
                #{item.commId}
            </foreach>
        </if>

        group by A.commId
    </select>

    <select id="queryProjectScore" resultType="java.util.HashMap">

        select
        any_value(A.quality) quality,
        any_value(A.ifLocking) ifLocking,
        count(A.bussinessId) userNum,
        sum(case when A.f1 > 2 then 1 else 0 end) safeNum,
        SUM(CASE WHEN A.f2 > 2 THEN 1 ELSE 0 END) environmentNum,
        SUM(CASE WHEN A.f3 > 2 THEN 1 ELSE 0 END) serviceNum,
        SUM(CASE WHEN A.n2 > 3 THEN 1 ELSE 0 END) highUserNum
        from
        `micro_satisfy_info` A
        where A.`status` = 1
        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        </if>
        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        </if>


        <if test="null != commId and '' != commId">
            AND A.commId = #{commId}
        </if>
        <if test="null != organId and '' != organId">
            AND A.organId = #{organId}
        </if>

        GROUP BY DATE_FORMAT(A.`finishTime`,'%Y')


    </select>

    <!-- 查询项目的季度 得分和不满意总结  -->
    <select id="queryProjectSummary" resultType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyOrganDetailModel">
        select
        any_value(A.commId) commId,
        any_value(A.commName) commName,
        any_value(A.organId) organId,
        any_value(A.organName) organName,
        any_value(A.quality) quality,
        any_value(A.ifLocking) ifLocking,
        count(A.bussinessId) userNum,
        (select count(bussinessId) from `micro_satisfy_info` where
        DATE_FORMAT(finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        AND DATE_FORMAT(finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        AND commId = #{commId}
        AND organId = #{organId}
        ) permanentNum,
        sum(CASE A.n2 WHEN 1 THEN 1 ELSE 0 END) satisfyScore1,
        SUM(CASE A.n2 WHEN 2 THEN 1 ELSE 0 END) satisfyScore2,
        SUM(CASE A.n2 WHEN 3 THEN 1 ELSE 0 END) satisfyScore3,
        SUM(CASE A.n2 WHEN 4 THEN 1 ELSE 0 END) satisfyScore4,
        SUM(CASE A.n2 WHEN 5 THEN 1 ELSE 0 END) satisfyScore5,
        sum(case when A.f1 > 2 then 1 else 0 end) safeNum,
        SUM(CASE WHEN A.f2 > 2 THEN 1 ELSE 0 END) environmentNum,
        SUM(CASE WHEN A.f3 > 2 THEN 1 ELSE 0 END) serviceNum,
        SUM(CASE WHEN A.n2 > 3 THEN 1 ELSE 0 END) highUserNum,

        SUM(CASE A.n31 WHEN 0 THEN 0 ELSE 1 END) unSpoil,
        SUM(CASE A.n32 WHEN 0 THEN 0 ELSE 1 END) unFrequently,

        SUM(CASE A.n33 WHEN 0 THEN 0 ELSE 1 END) unFloor,

        SUM(CASE A.n34 WHEN 0 THEN 0 ELSE 1 END) unRepair,

        SUM(CASE A.n35 WHEN 0 THEN 0 ELSE 1 END) unEstablish,
        SUM(CASE A.n36 WHEN 0 THEN 0 ELSE 1 END) unGreen,

        SUM(CASE A.n37 WHEN 0 THEN 0 ELSE 1 END) unGreenPlanting,
        SUM(CASE A.n38 WHEN 0 THEN 0 ELSE 1 END) unParking,
        SUM(CASE A.n39 WHEN 0 THEN 0 ELSE 1 END) unNoise,
        SUM(CASE A.n310 WHEN 0 THEN 0 ELSE 1 END) unDoorPost,
        SUM(CASE A.n311 WHEN 0 THEN 0 ELSE 1 END) unpeccancy,
        SUM(CASE A.n312 WHEN 0 THEN 0 ELSE 1 END) unGarbage,

        SUM(CASE A.n313 WHEN 0 THEN 0 ELSE 1 END) unEnvironment,

        SUM(CASE A.n314 WHEN 0 THEN 0 ELSE 1 END) unFourHazards,

        SUM(CASE A.n315 WHEN 0 THEN 0 ELSE 1 END) unWaterClean,
        SUM(CASE A.n316 WHEN 0 THEN 0 ELSE 1 END) unRoomQuanlity,

        SUM(CASE A.n317 WHEN 0 THEN 0 ELSE 1 END) unCommitment,

        SUM(CASE A.n318 WHEN 0 THEN 0 ELSE 1 END) unCharge,
        SUM(CASE A.n319 WHEN 0 THEN 0 ELSE 1 END) unStatus,

        SUM(CASE A.n320 WHEN 0 THEN 0 ELSE 1 END)  unMatching,

        SUM(CASE A.n321 WHEN 0 THEN 0 ELSE 1 END) unElectric,

        SUM(CASE A.n322 WHEN 0 THEN 0 ELSE 1 END) unDissatisfaction,

        SUM(CASE A.n398 WHEN 0 THEN 0 ELSE 1 END) unOther
        from
        `micro_satisfy_info` A
        where A.`status` = 1
        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        </if>
        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        </if>


        <if test="null != commId and '' != commId">
            AND A.commId = #{commId}
        </if>
        <if test="null != organId and '' != organId">
            AND A.organId = #{organId}
        </if>
        group by A.commId
    </select>

    <!-- 区域分析 -->
    <select id="queryRegionalAnalysis" resultType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyOrganDetailModel">

        select
        any_value(A.commId) commId,
        any_value(A.commName) commName,
        any_value(A.organId) organId,
        any_value(A.organName) organName,
        any_value(A.quality) quality,
        count(A.bussinessId) userNum,
        (select count(*) from `micro_satisfy_info` where
        DATE_FORMAT(finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        AND DATE_FORMAT(finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        AND commId = any_value(A.commId)
        and `status` !=1
        group by DATE_FORMAT(finishTime, "%Y")
        ) notAssess,

        (select count(*) from `micro_satisfy_info` where
        DATE_FORMAT(finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        AND DATE_FORMAT(finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        AND commId = any_value(A.commId)
        group by DATE_FORMAT(finishTime, "%Y")
        ) effectiveNum,

        any_value(A.ifLocking) exempt,

        sum(case when A.f1 > 2 then 1 else 0 end) safeNum,
        SUM(CASE WHEN A.f2 > 2 THEN 1 ELSE 0 END) environmentNum,
        SUM(CASE WHEN A.f3 > 2 THEN 1 ELSE 0 END) serviceNum,
        SUM(CASE WHEN A.n2 > 3 THEN 1 ELSE 0 END) highUserNum,
        ROUND(avg(A.f1+A.f2+A.f3)) evaluationScore
        from
        `micro_satisfy_info` A
        where A.`status` = 1

        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        </if>
        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        </if>

        <if test="null != organId and '' != organId">
            AND A.organId = #{organId}
        </if>


        <if test="innIds != null and innIds.size > 0">
            and A.commId in
            <foreach collection="innIds" index="index" open="(" close=")" separator="," item="item">
                #{item.commId}
            </foreach>
        </if>

        group by A.commId
    </select>
    <!--and	A.ifLocking= '0'



            (select count(*) from `micro_satisfy_info` where
           DATE_FORMAT(finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
          AND DATE_FORMAT(finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        AND commId = any_value(A.commId)
        and ifLocking ='1'
          group by DATE_FORMAT(finishTime, "%Y")
          ) exempt,



     -->

    <!-- 查询项目的季度 得分和不满意总结  -->
    <!--
        <select id="queryQuestionnaire" resultType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyOrganDetailModel">
    select
      any_value(A.commId) commId,
      any_value(A.commName) commName,
      any_value(A.organId) organId,
      any_value(A.organName) organName,
      any_value(A.quality) quality,
      sum(case when A.f1 > 2 then 1 else 0 end) safeNum,
      SUM(CASE WHEN A.f2 > 2 THEN 1 ELSE 0 END) environmentNum,
      SUM(CASE WHEN A.f3 > 2 THEN 1 ELSE 0 END) serviceNum,

      SUM(CASE WHEN A.n1 > 2 THEN 1 ELSE 0 END) permanentNum,
      SUM(CASE WHEN A.n2 > 2 THEN 1 ELSE 0 END) highUserNum,

      SUM(CASE A.n5 WHEN 1 THEN 1 ELSE 0 END) n5,
      SUM(CASE A.n6 WHEN 1 THEN 1 ELSE 0 END) n6,
      SUM(CASE WHEN A.n7 > 2 THEN 1 ELSE 0 END) n7,

      SUM(CASE A.n31 WHEN 0 THEN 1 ELSE 0 END) unSpoil,
      SUM(CASE A.n32 WHEN 0 THEN 1 ELSE 0 END) unFrequently,

      SUM(CASE A.n33 WHEN 0 THEN 1 ELSE 0 END) unFloor,

      SUM(CASE A.n34 WHEN 0 THEN 1 ELSE 0 END) unRepair,

      SUM(CASE A.n35 WHEN 0 THEN 1 ELSE 0 END) unEstablish,
      SUM(CASE A.n36 WHEN 0 THEN 1 ELSE 0 END) unGreen,

      SUM(CASE A.n37 WHEN 0 THEN 1 ELSE 0 END) unGreenPlanting,
      SUM(CASE A.n38 WHEN 0 THEN 1 ELSE 0 END) unParking,
       SUM(CASE A.n39 WHEN 0 THEN 1 ELSE 0 END) unNoise,
      SUM(CASE A.n310 WHEN 0 THEN 1 ELSE 0 END) unDoorPost,
       SUM(CASE A.n311 WHEN 0 THEN 1 ELSE 0 END) unpeccancy,
      SUM(CASE A.n312 WHEN 0 THEN 1 ELSE 0 END) unGarbage,

      SUM(CASE A.n313 WHEN 0 THEN 1 ELSE 0 END) unEnvironment,

      SUM(CASE A.n314 WHEN 0 THEN 1 ELSE 0 END) unFourHazards,

      SUM(CASE A.n315 WHEN 0 THEN 1 ELSE 0 END) unWaterClean,
      SUM(CASE A.n316 WHEN 0 THEN 1 ELSE 0 END) unRoomQuanlity,

      SUM(CASE A.n317 WHEN 0 THEN 1 ELSE 0 END) unCommitment,

      SUM(CASE A.n318 WHEN 0 THEN 1 ELSE 0 END) unCharge,
      SUM(CASE A.n319 WHEN 0 THEN 1 ELSE 0 END) unStatus,

      SUM(CASE A.n320 WHEN 0 THEN 1 ELSE 0 END)  unMatching,

      SUM(CASE A.n321 WHEN 0 THEN 1 ELSE 0 END) unElectric,

      SUM(CASE A.n322 WHEN 0 THEN 1 ELSE 0 END) unDissatisfaction,

      SUM(CASE A.n398 WHEN 0 THEN 1 ELSE 0 END) unOther
    from
      `micro_satisfy_info` A
      where A.`status` = 1
        <if test="null != beginDate and '' != beginDate">
        AND DATE_FORMAT(A.finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
    </if>
    <if test="null != endDate and '' != endDate">
        AND DATE_FORMAT(A.finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
    </if>

   <if test="null != organId and '' != organId">
        AND A.organId = #{organId}
    </if>
    group by A.commId
</select>

   -->



    <!-- 		  SUM(A.n31) unSpoil,
       SUM(A.n32) unFrequently,

       SUM(A.n33) unFloor,

       SUM(A.n34) unRepair,

       SUM(A.n35) unEstablish,
       SUM(A.n36) unGreen,

       SUM(A.n37) unGreenPlanting,
       SUM(A.n38) unParking,
        SUM(A.n39) unNoise,
       SUM(A.n310) unDoorPost,
        SUM(A.n311) unpeccancy,
       SUM(A.n312) unGarbage,

       SUM(A.n313) unEnvironment,

       SUM(A.n314) unFourHazards,

       SUM(A.n315) unWaterClean,
       SUM(A.n316) unRoomQuanlity,

       SUM(A.n317) unCommitment,

       SUM(A.n318) unCharge,
       SUM(A.n319) unStatus,

       SUM(A.n320)  unMatching,

       SUM(A.n321) unElectric,

       SUM(A.n322) unDissatisfaction,


       SUM(A.n5) n5,
       SUM(A.n6) n6,

             SUM(A.n1) permanentNum,


       SUM(A.n398) unOther -->
    <select id="queryQuestionnaire" resultType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyOrganDetailModel">
        select
        any_value(A.commId) commId,
        any_value(A.commName) commName,
        any_value(A.organId) organId,
        any_value(A.organName) organName,
        any_value(A.quality) quality,
        any_value(A.f1) safeNum,
        any_value(A.f2) environmentNum,
        any_value(A.f3) serviceNum,


        any_value(A.n2) highUserNum,

        any_value(A.n7) n7


        from
        `micro_satisfy_info` A
        where A.`status` = 1
        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        </if>
        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        </if>

        <if test="null != organId and '' != organId">
            AND A.organId = #{organId}
        </if>

        <if test="null != commId and '' != commId">
            AND A.commId = #{commId}
        </if>

    </select>
    
    
        <sql id="getPublicQueryRegionalRanking">
         select
        any_value(A.organName) organName,
        count(A.bussinessId) userNum,
        any_value(A.organId) organId,

        (select count(*) from `micro_satisfy_info` where
        DATE_FORMAT(finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        AND DATE_FORMAT(finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        AND commId = any_value(A.commId)
        group by DATE_FORMAT(finishTime, "%Y")
        ) effectiveNum,


        (select count(DISTINCT (commId)) from `micro_satisfy_info` where
        DATE_FORMAT(finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        AND DATE_FORMAT(finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        AND organId = any_value(A.organId)

        group by organId
        ) commNum,

        count(DISTINCT (A.organId)) organNum,

        sum(case when A.f1 > 2 then 1 else 0 end) safeNum,
        SUM(CASE WHEN A.f2 > 2 THEN 1 ELSE 0 END) environmentNum,
        SUM(CASE WHEN A.f3 > 2 THEN 1 ELSE 0 END) serviceNum,
        SUM(CASE WHEN A.n2 > 3 THEN 1 ELSE 0 END) highUserNum
        from
        `micro_satisfy_info` A
    </sql>
    
    
      <select id="getQueryRegionalRankingTwo" resultType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyOrganDetailModel">

     <include refid="getPublicQueryRegionalRanking"></include>
     
        where A.`status` = 1
        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        </if>
        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        </if>

        <if test="null != Identification and '' != Identification">
            AND A.quality=#{Identification}
        </if>


        <if test="null != organId and '' != organId">
            AND A.organId = #{organId}
        </if>

        <if test="innIds != null and innIds.size > 0">
            and A.commId in
            <foreach collection="innIds" index="index" open="(" close=")" separator="," item="item">
                #{item.commId}
            </foreach>
        </if>

        GROUP BY A.`organId`
    </select>
    

    <!--group by A.commId  -->

    <select id="getQueryRegionalRanking" resultType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyOrganDetailModel">

     <include refid="getPublicQueryRegionalRanking"></include>
     
        where A.`status` = 1
        and  A.ifLocking= '0'
        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        </if>
        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        </if>

        <if test="null != Identification and '' != Identification">
            AND A.quality=#{Identification}
        </if>


        <if test="null != organId and '' != organId">
            AND A.organId = #{organId}
        </if>

        <if test="innIds != null and innIds.size > 0">
            and A.commId in
            <foreach collection="innIds" index="index" open="(" close=")" separator="," item="item">
                #{item.commId}
            </foreach>
        </if>

        GROUP BY A.`organId`
    </select>
    <!-- 	  count(DISTINCT (A.commId)) commNum,




            <if test="innIds != null and innIds.size > 0">
             and A.commId in
              <foreach collection="innIds" index="index" open="(" close=")" separator="," item="item">
                 #{item.commId}
             </foreach>
        </if>
        -->

    <select id="getProjectSatisfaction" resultType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyOrganDetailModel">
        select
        any_value(A.organId) organId,
        any_value(A.organName) organName,
        count(DISTINCT (A.commId)) commNum,
        count(A.bussinessId) userNum,
        sum(case when A.f1 > 2 then 1 else 0 end) safeNum,
        SUM(CASE WHEN A.f2 > 2 THEN 1 ELSE 0 END) environmentNum,
        SUM(CASE WHEN A.f3 > 2 THEN 1 ELSE 0 END) serviceNum,
        SUM(CASE WHEN A.n2 > 3 THEN 1 ELSE 0 END) highUserNum
        from
        `micro_satisfy_info` A
        where A.`status` = 1
        and A.ifLocking='0'
        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        </if>
        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        </if>
        <if test="null != Identification and '' != Identification">
            AND A.quality=#{Identification}
        </if>
        <if test="null != organId and '' != organId">
            AND A.organId = #{organId}
        </if>

        <if test="innIds != null and innIds.size > 0">
            and A.commId in
            <foreach collection="innIds" index="index" open="(" close=")" separator="," item="item">
                #{item.commId}
            </foreach>
        </if>

        group by A.organId
    </select>


    <select id="getProjectSatisfactionParameter" resultType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyOrganDetailModel">
        select
        any_value(A.quality) quality,
        any_value(A.organId) organId,
        any_value(A.organName) organName,
        any_value(A.commId) commId,
        any_value(A.commName) commName,
        count(DISTINCT (A.commId)) commNum,
        count(A.bussinessId) userNum,
        sum(case when A.f1 > 2 then 1 else 0 end) safeNum,
        SUM(CASE WHEN A.f2 > 2 THEN 1 ELSE 0 END) environmentNum,
        SUM(CASE WHEN A.f3 > 2 THEN 1 ELSE 0 END) serviceNum,
        SUM(CASE WHEN A.n2 > 3 THEN 1 ELSE 0 END) highUserNum
        from
        `micro_satisfy_info` A
        where A.`status` = 1
        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        </if>
        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        </if>
        <if test="null != commId and '' != commId">
            AND A.commId = #{commId}
        </if>
        group by A.commId HAVING COUNT(A.commId)&gt;=30
    </select>


    <select id="getHousekeeperSatisfaction" resultType="java.util.HashMap">
        SELECT
        any_value(m.organ_code) organCode,
        any_value(m.organ_name) organName,
        any_value(m.comm_id) commId,
        any_value(m.comm_name) commName,
        any_value(m.manager_name) managerName,
        ROUND(AVG(m.recognition_percentage), 2) recognitionPercentage,
        ROUND(AVG(m.satisfaction), 2) satisfaction
        FROM
        fenghuang_manager_data m,
        fenghuang_comm c
        WHERE m.comm_id = c.erp_comm_id
        <if test="null != commId and '' != commId">
            AND m.comm_id = #{commId}
        </if>
        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(m.end_date, "%Y-%m") = DATE_FORMAT(#{beginDate}, "%Y-%m")
        </if>

        GROUP BY m.comm_id

    </select>



    <select id="getGetNumber" resultType="java.lang.String">

        select
        A.organId
        from
        `micro_satisfy_info` A
        where A.`status` = 1

        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        </if>
        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        </if>

        <if test="null != Identification and '' != Identification">
            and  A.ifLocking= '0'
        </if>

        GROUP BY A.`organId`
    </select>


    <select id="getGetNumberComm" resultType="java.lang.String">
        select
        A.commId

        from
        `micro_satisfy_info` A
        where A.`status` = 1
        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        </if>
        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        </if>

        group by A.commId
    </select>




    <select id="sumByDate" resultMap="BaseResultMap" >
        <![CDATA[
      SELECT
      COUNT(DISTINCT m.organ_code) AS organ_name,
        COUNT(DISTINCT m.comm_id) AS comm_name,
        COUNT(m.manager_name) AS manager_name,
        SUM(m.recognition_percentage) as recognition_percentage,
        SUM(m.satisfaction) as satisfaction

      FROM fenghuang_manager_data m
      INNER JOIN fenghuang_comm c ON m.comm_id = c.erp_comm_id
      WHERE  DATE_FORMAT(m.begin_date, "%Y-%m") = DATE_FORMAT(#{beginDate}, "%Y-%m") AND  DATE_FORMAT(m.end_date, "%Y-%m") = DATE_FORMAT(#{endDate}, "%Y-%m")  AND status<>'unfill'
    ]]>



        <if test="null != organId and '' != organId">
            AND m.organ_code = #{organId}
        </if>
        <if test="commId != null and commId != ''">
            AND m.comm_id = #{commId}
        </if>

        <![CDATA[
      GROUP BY begin_date,end_date
    ]]>
    </select>



    <select id="sumYeryByDate" resultMap="BaseResultMap" >
        <![CDATA[
      SELECT
      COUNT(DISTINCT m.organ_code) AS organ_name,
        COUNT(DISTINCT m.comm_id) AS comm_name,
        COUNT(m.manager_name) AS manager_name,
        SUM(m.recognition_percentage) as recognition_percentage,
        SUM(m.satisfaction) as satisfaction,
        any_value(DATE_FORMAT(m.begin_date,"%Y-%m")) timeStr

      FROM fenghuang_manager_data m
      INNER JOIN fenghuang_comm c ON m.comm_id = c.erp_comm_id
      WHERE  DATE_FORMAT(m.begin_date, "%Y") = DATE_FORMAT(#{beginDate}, "%Y") AND  DATE_FORMAT(m.end_date, "%Y") = DATE_FORMAT(#{endDate}, "%Y")  AND m.status<>'unfill'
    ]]>



        <if test="null != organId and '' != organId">
            AND m.organ_code = #{organId}
        </if>
        <if test="commId != null and commId != ''">
            AND m.comm_id = #{commId}
        </if>

        <![CDATA[
      GROUP BY DATE_FORMAT(m.begin_date, "%Y-%m")
    ]]>
    </select>



    <select id="getManagerSatisfy" resultType="java.util.HashMap">
        select
        any_value(A.organId) organId,
        any_value(A.organName) organName,
        any_value(A.commId) commId,
        any_value(A.commName) commName
        from
        `micro_satisfy_info` A
        where A.`status` = 1
        <if test="null != timeStr and '' != timeStr">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") = DATE_FORMAT(#{timeStr}, "%Y-%m")
        </if>
        <!-- 		<if test="null != endDate and '' != endDate">
                    AND DATE_FORMAT(A.finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
                </if> -->

        <if test="null != organId and '' != organId">
            AND A.organId = #{organId}
        </if>

        group by A.commId
    </select>


    <resultMap id="BaseResultMap" type="cn.com.countrygarden.bi.main.model.dup.ManagerData">


        <result column="organ_name" jdbcType="VARCHAR" property="organName" />

        <result column="comm_name" jdbcType="VARCHAR" property="commName" />
        <result column="manager_name" jdbcType="VARCHAR" property="managerName" />

        <result column="recognition_percentage" jdbcType="DOUBLE" property="recognitionPercentage" />
        <result column="satisfaction" jdbcType="DOUBLE" property="satisfaction" />

        <result column="timeStr" jdbcType="VARCHAR" property="createdBy" />

    </resultMap>



    <select id="getEffectiveProject" resultType="java.util.HashMap">
        select
        COUNT(f.commId) sampleNumber,
        any_value(f.commId) commId
        FROM
        micro_satisfy_info f
        where f.`status` = 1
        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(f.finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        </if>

        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(f.finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        </if>

        <if test="null != organId and '' != organId">
            AND f.organId = #{organId}
        </if>
        <if test="commId != null and commId != ''">
            AND f.commId = #{commId}
        </if>

        group by f.commId HAVING COUNT(f.commId)&gt;=30
    </select>
    
        <select id="getOrganEffectiveProject" resultType="java.util.HashMap">
        select
        any_value(f.commId) commId
        FROM
        micro_satisfy_info f
        where f.`status` = 1
        and f.isAssessment=0
        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(f.finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        </if>

        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(f.finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        </if>

        <if test="null != organId and '' != organId">
            AND f.organId = #{organId}
        </if>
        <if test="commId != null and commId != ''">
            AND f.commId = #{commId}
        </if>

        group by f.commId HAVING COUNT(f.commId)&gt;=30
    </select>
    
         <select id="getOrganIfLocking" resultType="java.util.HashMap">
        select
        any_value(f.commId) commId
        FROM
        micro_satisfy_info f
        where f.`status` = 1
        and f.ifLocking=1
        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(f.finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        </if>

        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(f.finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        </if>

        <if test="null != organId and '' != organId">
            AND f.organId = #{organId}
        </if>
        <if test="commId != null and commId != ''">
            AND f.commId = #{commId}
        </if>

        group by f.commId
    </select>


    <select id="getMicroOrganRank" resultType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyOrganDetailModel">

        select
        any_value(A.organName) organName,
        count(A.bussinessId) userNum,
        any_value(A.organId) organId,

        (select count(*) from `micro_satisfy_info` where
        DATE_FORMAT(finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        AND DATE_FORMAT(finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        AND commId = any_value(A.commId)
        group by DATE_FORMAT(finishTime, "%Y")
        ) effectiveNum,

        count(DISTINCT (A.commId)) commNum,

        count(DISTINCT (A.organId)) organNum,

        sum(case when A.f1 > 2 then 1 else 0 end) safeNum,
        SUM(CASE WHEN A.f2 > 2 THEN 1 ELSE 0 END) environmentNum,
        SUM(CASE WHEN A.f3 > 2 THEN 1 ELSE 0 END) serviceNum,
        SUM(CASE WHEN A.n2 > 3 THEN 1 ELSE 0 END) highUserNum
        from
        `micro_satisfy_info` A
        where A.`status` = 1

        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        </if>
        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        </if>

        <if test="null != Identification and '' != Identification">
            AND A.quality=#{Identification}
        </if>


        <if test="innIds != null and innIds.size > 0">
            and A.organId in
            <foreach collection="innIds" index="index" open="(" close=")" separator="," item="item">
                #{item.organId}
            </foreach>

            and A.commId in
            <foreach collection="innIds" index="index" open="(" close=")" separator="," item="item">
                #{item.commId}
            </foreach>
        </if>

        GROUP BY A.`organId`
    </select>

<!-- 查询区域 得分和不满意总结  -->
    <select id="queryProjectRegion" resultType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyOrganDetailModel">
        select
        any_value(A.organId) organId,
        any_value(A.organName) organName,
        count(A.bussinessId) userNum,
        (select count(bussinessId) from `micro_satisfy_info` where
        DATE_FORMAT(finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        AND DATE_FORMAT(finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        AND organId = #{organId}
        ) permanentNum,
        sum(CASE A.n2 WHEN 1 THEN 1 ELSE 0 END) satisfyScore1,
        SUM(CASE A.n2 WHEN 2 THEN 1 ELSE 0 END) satisfyScore2,
        SUM(CASE A.n2 WHEN 3 THEN 1 ELSE 0 END) satisfyScore3,
        SUM(CASE A.n2 WHEN 4 THEN 1 ELSE 0 END) satisfyScore4,
        SUM(CASE A.n2 WHEN 5 THEN 1 ELSE 0 END) satisfyScore5,

        SUM(CASE A.n31 WHEN 0 THEN 0 ELSE 1 END) unSpoil,
        SUM(CASE A.n32 WHEN 0 THEN 0 ELSE 1 END) unFrequently,

        SUM(CASE A.n33 WHEN 0 THEN 0 ELSE 1 END) unFloor,

        SUM(CASE A.n34 WHEN 0 THEN 0 ELSE 1 END) unRepair,

        SUM(CASE A.n35 WHEN 0 THEN 0 ELSE 1 END) unEstablish,
        SUM(CASE A.n36 WHEN 0 THEN 0 ELSE 1 END) unGreen,

        SUM(CASE A.n37 WHEN 0 THEN 0 ELSE 1 END) unGreenPlanting,
        SUM(CASE A.n38 WHEN 0 THEN 0 ELSE 1 END) unParking,
        SUM(CASE A.n39 WHEN 0 THEN 0 ELSE 1 END) unNoise,
        SUM(CASE A.n310 WHEN 0 THEN 0 ELSE 1 END) unDoorPost,
        SUM(CASE A.n311 WHEN 0 THEN 0 ELSE 1 END) unpeccancy,
        SUM(CASE A.n312 WHEN 0 THEN 0 ELSE 1 END) unGarbage,

        SUM(CASE A.n313 WHEN 0 THEN 0 ELSE 1 END) unEnvironment,

        SUM(CASE A.n314 WHEN 0 THEN 0 ELSE 1 END) unFourHazards,

        SUM(CASE A.n315 WHEN 0 THEN 0 ELSE 1 END) unWaterClean,
        SUM(CASE A.n316 WHEN 0 THEN 0 ELSE 1 END) unRoomQuanlity,

        SUM(CASE A.n317 WHEN 0 THEN 0 ELSE 1 END) unCommitment,

        SUM(CASE A.n318 WHEN 0 THEN 0 ELSE 1 END) unCharge,
        SUM(CASE A.n319 WHEN 0 THEN 0 ELSE 1 END) unStatus,

        SUM(CASE A.n320 WHEN 0 THEN 0 ELSE 1 END)  unMatching,

        SUM(CASE A.n321 WHEN 0 THEN 0 ELSE 1 END) unElectric,

        SUM(CASE A.n322 WHEN 0 THEN 0 ELSE 1 END) unDissatisfaction,

        SUM(CASE A.n398 WHEN 0 THEN 0 ELSE 1 END) unOther
        from
        `micro_satisfy_info` A
        where A.`status` = 1
        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        </if>
        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        </if>

        <if test="null != organId and '' != organId">
            AND A.organId = #{organId}
        </if>
        
        <if test="innIds != null and innIds.size > 0">
            and A.commId in
            <foreach collection="innIds" index="index" open="(" close=")" separator="," item="item">
                #{item.commId}
            </foreach>
        </if>
        
        group by A.organId
    </select>
    
    
    
    	<!-- 项目分析 -->
    <select id="querySatisfyDetailByCommId" resultType="cn.com.countrygarden.bi.main.model.microSatisfy.MicroSatisfyOrganDetailModel">

        select
        any_value(A.commId) commId,
        any_value(A.commName) commName,
        any_value(A.organId) organId,
        any_value(A.organName) organName,
        any_value(A.quality) quality,
        any_value(A.ifLocking) ifLocking,
        count(A.bussinessId) userNum,
        (select count(*) from `micro_satisfy_info` where
        DATE_FORMAT(finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        AND DATE_FORMAT(finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        AND commId = any_value(A.commId)
        and `status` !=1
        group by DATE_FORMAT(finishTime, "%Y")
        ) notAssess,

        (select count(*) from `micro_satisfy_info` where
        DATE_FORMAT(finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        AND DATE_FORMAT(finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        AND commId = any_value(A.commId)
        group by DATE_FORMAT(finishTime, "%Y")
        ) effectiveNum,

        any_value(A.ifLocking) exempt,

        sum(case when A.f1 > 2 then 1 else 0 end) safeNum,
        SUM(CASE WHEN A.f2 > 2 THEN 1 ELSE 0 END) environmentNum,
        SUM(CASE WHEN A.f3 > 2 THEN 1 ELSE 0 END) serviceNum,
        SUM(CASE WHEN A.n2 > 3 THEN 1 ELSE 0 END) highUserNum,
        ROUND(avg(A.f1+A.f2+A.f3)) evaluationScore
        from
        `micro_satisfy_info` A
        where A.`status` = 1

        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        </if>
        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        </if>

        <if test="null != organId and '' != organId">
            AND A.organId = #{organId}
        </if>

	 <if test="null != commId and '' != commId">
            AND A.commId = #{commId}
        </if>
 
        group by A.commId
    </select>
    
    <select id="selectVoiceRegion" resultType="java.util.HashMap">
        SELECT
        A.voice voice,
        count(A.voice) voiceCount
        FROM
        micro_satisfy_info A
        WHERE
        A.status = 1
		and  A.ifLocking= '0'
        <if test="null != beginDate and '' != beginDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &gt;= DATE_FORMAT(#{beginDate}, "%Y-%m")
        </if>
        <if test="null != endDate and '' != endDate">
            AND DATE_FORMAT(A.finishTime, "%Y-%m") &lt;= DATE_FORMAT(#{endDate}, "%Y-%m")
        </if>
        <if test="null != organId and '' != organId">
            AND A.organId = #{organId}
        </if>
                <if test="innIds != null and innIds.size > 0">
            and A.commId in
            <foreach collection="innIds" index="index" open="(" close=")" separator="," item="item">
                #{item.commId}
            </foreach>
        </if>
        
        GROUP BY A.voice
    </select>

    
    

    <!-- 	<update id="deleteMicroOrganRank">
        DELETE FROM micro_organ_rank
        WHERE
        DATE_FORMAT(month,'%Y-%m')=DATE_FORMAT(#{timeStr},'%Y-%m')
    </update>

        <update id="deleteMicroCommRank">
        DELETE FROM micro_comm_rank
        WHERE
        DATE_FORMAT(month,'%Y-%m')=DATE_FORMAT(#{timeStr},'%Y-%m')
    </update>

        <update id="deleteMicroSatisfyRegionDetail">
        DELETE FROM micro_satisfy_region_detail
        WHERE
        DATE_FORMAT(month,'%Y-%m')=DATE_FORMAT(#{timeStr},'%Y-%m')
    </update> -->

</mapper>