<?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.ck.satisfaction.SatisfiedDissatisfiedRegionMapper">
    <resultMap id="BaseResultMap" type="cn.com.countrygarden.bi.main.model.ck.satisfaction.SatisfiedDissatisfiedRegion">
        <id column="id" property="id"/>
        <result column="org_code" property="orgCode"/>
        <result column="org_name" property="orgName"/>
        <result column="seVpapr" property="seVpapr"/>
        <result column="seLlegal" property="seLlegal"/>
        <result column="sePet" property="sePet"/>
        <result column="seNoise" property="seNoise"/>
        <result column="seNonresid" property="seNonresid"/>
        <result column="seOtheroise" property="seOtheroise"/>
        <result column="seCardchecking" property="seCardchecking"/>
        <result column="seLaxcarking" property="seLaxcarking"/>
        <result column="seFireman" property="seFireman"/>
        <result column="seLlechaing" property="seLlechaing"/>
        <result column="seSense" property="seSense"/>
        <result column="sePostwork" property="sePostwork"/>
        <result column="sePooratitude" property="sePooratitude"/>
        <result column="seConsciousness" property="seConsciousness"/>
        <result column="cuStafhad" property="cuStafhad"/>
        <result column="cuFrontdesk" property="cuFrontdesk"/>
        <result column="cuHousekeeper" property="cuHousekeeper"/>
        <result column="cuConhouse" property="cuConhouse"/>
        <result column="cuProblemfollow" property="cuProblemfollow"/>
        <result column="cuPoorachatide" property="cuPoorachatide"/>
        <result column="cuFrequently" property="cuFrequently"/>
        <result column="cuFrbujidesk" property="cuFrbujidesk"/>
        <result column="grGreenwaste" property="grGreenwaste"/>
        <result column="grMaintenance" property="grMaintenance"/>
        <result column="grDowntrees" property="grDowntrees"/>
        <result column="grPlantruning" property="grPlantruning"/>
        <result column="grWitheeds" property="grWitheeds"/>
        <result column="grLoess" property="grLoess"/>
        <result column="grSeedlingloss" property="grSeedlingloss"/>
        <result column="grFewplant" property="grFewplant"/>
        <result column="grUnreasonable" property="grUnreasonable"/>
        <result column="grServiceimage" property="grServiceimage"/>
        <result column="clUnrplagacans" property="clUnrplagacans"/>
        <result column="clRemovaon" property="clRemovaon"/>
        <result column="clOutdoorpublic" property="clOutdoorpublic"/>
        <result column="clBuilinterior" property="clBuilinterior"/>
        <result column="clGarcleang" property="clGarcleang"/>
        <result column="clWaterscape" property="clWaterscape"/>
        <result column="clFpelimination" property="clFpelimination"/>
        <result column="clCleanupaccu" property="clCleanupaccu"/>
        <result column="clServiceimage" property="clServiceimage"/>
        <result column="enPuicliting" property="enPuicliting"/>
        <result column="enAmusement" property="enAmusement"/>
        <result column="enElevator" property="enElevator"/>
        <result column="enWaterupply" property="enWaterupply"/>
        <result column="enFireighting" property="enFireighting"/>
        <result column="enMonitoring" property="enMonitoring"/>
        <result column="enAccessconol" property="enAccessconol"/>
        <result column="enRoadgate" property="enRoadgate"/>
        <result column="enPublicarea" property="enPublicarea"/>
        <result column="enDrainage" property="enDrainage"/>
        <result column="enEquipment" property="enEquipment"/>
        <result column="enImageatude" property="enImageatude"/>
        <result column="enHomemainte" property="enHomemainte"/>
        <result column="yearMonth" property="yearMonth"/>
        <result column="status" property="status"/>
        <result column="create_by" property="createBy"/>
        <result column="create_name" property="createName"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
        <result column="update_name" property="updateName"/>
        <result column="update_by" property="updateBy"/>
        <result column="is_editor" property="isEditor"/>
    </resultMap>


    <select id="selectListDataToble" resultType="long">
        SELECT count(id) FROM `satisfied_dissatisfied_region`
        WHERE `status`=1
        <if test="yearNian!=null and yearNian!=''">
            AND yearMonth LIKE CONCAT("%",#{yearNian},"%")
        </if>
        <if test="yueList!=null and yueList.size()!=0">
            AND yearMonth in
            <foreach collection="yueList" item="yueList" index="index" open="(" close=")" separator=",">
                #{yueList}
            </foreach>
        </if>

        <if test="orgCodeList!=null and orgCodeList.size()!=0">
            AND org_code in
            <foreach collection="orgCodeList" item="orgCodeList" index="index" open="(" close=")" separator=",">
                #{orgCodeList}
            </foreach>
        </if>
    </select>

    <select id="queryPageList" resultMap="BaseResultMap">
        SELECT
        `id`,
        `org_code`,
        `org_name`,
        `seVpapr`,
        `seLlegal`,
        `sePet`,
        `seNoise`,
        `seNonresid`,
        `seOtheroise`,
        `seCardchecking`,
        `seLaxcarking`,
        `seFireman`,
        `seLlechaing`,
        `seSense`,
        `sePostwork`,
        `sePooratitude`,
        `seConsciousness`,
        `cuStafhad`,
        `cuFrontdesk`,
        `cuHousekeeper`,
        `cuConhouse`,
        `cuProblemfollow`,
        `cuPoorachatide`,
        `cuFrequently`,
        `cuFrbujidesk`,
        `grGreenwaste`,
        `grMaintenance`,
        `grDowntrees`,
        `grPlantruning`,
        `grWitheeds`,
        `grPlantdeath`,
        `grLoess`,
        `grSeedlingloss`,
        `grFewplant`,
        `grUnreasonable`,
        `grServiceimage`,
        `clUnrplagacans`,
        `clRemovaon`,
        `clOutdoorpublic`,
        `clBuilinterior`,
        `clGarcleang`,
        `clWaterscape`,
        `clFpelimination`,
        `clCleanupaccu`,
        `clServiceimage`,
        `enPuicliting`,
        `enAmusement`,
        `enElevator`,
        `enWaterupply`,
        `enFireighting`,
        `enMonitoring`,
        `enAccessconol`,
        `enRoadgate`,
        `enPublicarea`,
        `enDrainage`,
        `enEquipment`,
        `enImageatude`,
        `enHomemainte`,
        `yearMonth`
         FROM `satisfied_dissatisfied_region`
        WHERE `status`=1
        <if test="yearNian!=null and yearNian!=''">
            AND yearMonth LIKE CONCAT("%",#{yearNian},"%")
        </if>
        <if test="yueList!=null and yueList.size()!=0">
            AND yearMonth in
            <foreach collection="yueList" item="yueList" index="index" open="(" close=")" separator=",">
                #{yueList}
            </foreach>
        </if>

        <if test="orgCodeList!=null and orgCodeList.size()!=0">
            AND org_code in
            <foreach collection="orgCodeList" item="orgCodeList" index="index" open="(" close=")" separator=",">
                #{orgCodeList}
            </foreach>
        </if>
        ORDER BY yearMonth DESC
    </select>

    <select id="getrevenue" resultMap="BaseResultMap">
        SELECT
        id,
        `org_code`,
        `org_name`,
        `seVpapr`,
        `seLlegal`,
        `sePet`,
        `seNoise`,
        `seNonresid`,
        `seOtheroise`,
        `seCardchecking`,
        `seLaxcarking`,
        `seFireman`,
        `seLlechaing`,
        `seSense`,
        `sePostwork`,
        `sePooratitude`,
        `seConsciousness`,
        `cuStafhad`,
        `cuFrontdesk`,
        `cuHousekeeper`,
        `cuConhouse`,
        `cuProblemfollow`,
        `cuPoorachatide`,
        `cuFrequently`,
        `cuFrbujidesk`,
        `grGreenwaste`,
        `grMaintenance`,
        `grDowntrees`,
        `grPlantruning`,
        `grWitheeds`,
        `grPlantdeath`,
        `grLoess`,
        `grSeedlingloss`,
        `grFewplant`,
        `grUnreasonable`,
        `grServiceimage`,
        `clUnrplagacans`,
        `clRemovaon`,
        `clOutdoorpublic`,
        `clBuilinterior`,
        `clGarcleang`,
        `clWaterscape`,
        `clFpelimination`,
        `clCleanupaccu`,
        `clServiceimage`,
        `enPuicliting`,
        `enAmusement`,
        `enElevator`,
        `enWaterupply`,
        `enFireighting`,
        `enMonitoring`,
        `enAccessconol`,
        `enRoadgate`,
        `enPublicarea`,
        `enDrainage`,
        `enEquipment`,
        `enImageatude`,
        `enHomemainte`,
        `yearMonth`,`status`,is_editor
        FROM `satisfied_dissatisfied_region`
        <where>
        <if test="startTime!=null and startTime!=''and endTime!=null and endTime!=''">
            and update_time between #{startTime} and #{endTime}
        </if>
        </where>
        order by create_time desc
    </select>


    <select id="listBiao" resultType="cn.com.countrygarden.bi.main.model.ck.satisfaction.SatisfiedDissatisfiedRegion">
        SELECT COLUMN_NAME `key`,column_comment `value` FROM information_schema.COLUMNS
        WHERE table_name = 'satisfied_dissatisfied_region';
    </select>

    <insert id="insertPL">
        INSERT INTO satisfied_dissatisfied_region(
        `org_code`,
        `org_name`,
        `seVpapr`,
        `seLlegal`,
        `sePet`,
        `seNoise`,
        `seNonresid`,
        `seOtheroise`,
        `seCardchecking`,
        `seLaxcarking`,
        `seFireman`,
        `seLlechaing`,
        `seSense`,
        `sePostwork`,
        `sePooratitude`,
        `seConsciousness`,
        `cuStafhad`,
        `cuFrontdesk`,
        `cuHousekeeper`,
        `cuConhouse`,
        `cuProblemfollow`,
        `cuPoorachatide`,
        `cuFrequently`,
        `cuFrbujidesk`,
        `grGreenwaste`,
        `grMaintenance`,
        `grDowntrees`,
        `grPlantruning`,
        `grWitheeds`,
        `grPlantdeath`,
        `grLoess`,
        `grSeedlingloss`,
        `grFewplant`,
        `grUnreasonable`,
        `grServiceimage`,
        `clUnrplagacans`,
        `clRemovaon`,
        `clOutdoorpublic`,
        `clBuilinterior`,
        `clGarcleang`,
        `clWaterscape`,
        `clFpelimination`,
        `clCleanupaccu`,
        `clServiceimage`,
        `enPuicliting`,
        `enAmusement`,
        `enElevator`,
        `enWaterupply`,
        `enFireighting`,
        `enMonitoring`,
        `enAccessconol`,
        `enRoadgate`,
        `enPublicarea`,
        `enDrainage`,
        `enEquipment`,
        `enImageatude`,
        `enHomemainte`,
        `yearMonth`,
        `status`,
        `create_by`,
        `create_time`,
        `update_time`,is_editor)
        VALUES
        <foreach collection="orListIn" item="orListIn" separator=",">
            (#{orListIn.orgCode},#{orListIn.orgName},#{orListIn.seVpapr},#{orListIn.seLlegal},#{orListIn.sePet},#{orListIn.seNoise},
            #{orListIn.seNonresid},#{orListIn.seOtheroise},#{orListIn.seCardchecking},#{orListIn.seLaxcarking},#{orListIn.seFireman}
            ,#{orListIn.seLlechaing},#{orListIn.seSense},#{orListIn.sePostwork},#{orListIn.sePooratitude},
            #{orListIn.seConsciousness},#{orListIn.cuStafhad},#{orListIn.cuFrontdesk},#{orListIn.cuHousekeeper},
            #{orListIn.cuConhouse},#{orListIn.cuProblemfollow},#{orListIn.cuPoorachatide},#{orListIn.cuFrequently},#{orListIn.cuFrbujidesk}
            ,#{orListIn.grGreenwaste},#{orListIn.grMaintenance},#{orListIn.grDowntrees},#{orListIn.grPlantruning},
            #{orListIn.grWitheeds},#{orListIn.grPlantdeath},#{orListIn.grLoess},#{orListIn.grSeedlingloss},
            #{orListIn.grFewplant},#{orListIn.grUnreasonable},#{orListIn.grServiceimage},#{orListIn.clUnrplagacans},#{orListIn.clRemovaon}
            ,#{orListIn.clOutdoorpublic},#{orListIn.clBuilinterior},#{orListIn.clGarcleang},#{orListIn.clWaterscape},
            #{orListIn.clFpelimination},#{orListIn.clCleanupaccu},#{orListIn.clServiceimage},#{orListIn.enPuicliting},
            #{orListIn.enAmusement},#{orListIn.enElevator},#{orListIn.enWaterupply},#{orListIn.enFireighting},#{orListIn.enMonitoring}
            ,#{orListIn.enAccessconol},#{orListIn.enRoadgate},#{orListIn.enPublicarea},#{orListIn.enDrainage},
            #{orListIn.enEquipment},#{orListIn.enImageatude},#{orListIn.enHomemainte},
            #{orListIn.yearMonth},#{orListIn.status},#{orListIn.createBy},NOW(),NOW(),#{orListIn.isEditor})
        </foreach>
    </insert>


    <select id="niancha" resultType="string">
         SELECT DISTINCT LEFT(yearMonth,4) FROM `satisfied_dissatisfied_region`
    </select>

    <update id="updateDelete">
        UPDATE `satisfied_dissatisfied_region` SET `status` = '0'
    </update>

</mapper>