<?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.SatisfiedRegionMapper">
    <resultMap id="BaseResultMap" type="cn.com.countrygarden.bi.main.model.ck.satisfaction.SatisfiedRegion">
        <id column="id" property="id"/>
        <result column="org_code" property="orgCode"/>
        <result column="org_name" property="orgName"/>
        <result column="monthResults" property="monthResults"/>
        <result column="annualResults" property="annualResults"/>
        <result column="security_results" property="securityResults"/>
        <result column="cleaning_results" property="cleaningResults"/>
        <result column="green_results" property="greenResults"/>
        <result column="engineering_results" property="engineeringResults"/>
        <result column="customer_results" property="customerResults"/>
        <result column="security" property="security"/>
        <result column="cleaning" property="cleaning"/>
        <result column="green" property="green"/>
        <result column="engineering" property="engineering"/>
        <result column="customer" property="customer"/>
        <result column="comprehensive" property="comprehensive"/>
        <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_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`,`monthResults`,`annualResults`, `security_results`,
        `cleaning_results`,`green_results`,`engineering_results`,
        `customer_results`,`security`,`cleaning`,`green`,
        `engineering`,`customer`,`comprehensive`,`yearMonth` FROM `satisfied_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`,`monthResults`,`annualResults`, `security_results`,
        `cleaning_results`,`green_results`,`engineering_results`,
        `customer_results`,`security`,`cleaning`,`green`,
        `engineering`,`customer`,`comprehensive`,`yearMonth`,`status`,is_editor  FROM `satisfied_region`
        <where>
        <if test="startTime!=null and startTime!=''and endTime!=null and endTime!=''">
            and update_time between #{startTime} and #{endTime}
        </if>
        </where>
    </select>

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

    <insert id="insertPL">
        INSERT INTO satisfied_region(
        `org_code`,
        `org_name`,
        `monthResults`,
        `annualResults`,
        `security`,
        `cleaning`,
        `green`,
        `engineering`,
        `customer`,
        `comprehensive`,
        `yearMonth`,
        `status`,
        `create_by`,
        `create_time`,update_time,is_editor)
        VALUES
        <foreach collection="orListIn" item="orListIn" separator=",">
            (#{orListIn.orgCode},#{orListIn.orgName},#{orListIn.monthResults},#{orListIn.annualResults},
            #{orListIn.security},#{orListIn.cleaning},#{orListIn.green},#{orListIn.engineering},#{orListIn.customer}
            ,#{orListIn.comprehensive},#{orListIn.yearMonth},#{orListIn.status},#{orListIn.createBy},NOW(),NOW(),#{orListIn.isEditor})
        </foreach>
    </insert>

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

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