<?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.CkCleanerExpandMapper">
    <!-- 通用查询映射结果 -->
    <resultMap id="listDataMap" type="cn.com.countrygarden.bi.main.dto.ck.CkCleanerTableVo">
        <id column="id" property="basicId"/>
        <result column="org_code" property="orgCode"/>
        <result column="org_name" property="orgName"/>
        <result column="comm_code" property="commCode"/>
        <result column="comm_name" property="commName"/>
        <result column="month" property="month"/>
        <result column="year" property="year"/>
        <result column="status" property="status"/>
        <collection property="children" fetchType="eager"
                    ofType="cn.com.countrygarden.bi.main.dto.ck.CkCleanerFieldConfigVo"
                    select="cleanerFieldConfigVoList" column="{basicId=id,configStatus=status}">
        </collection>
    </resultMap>
    <select id="selectListData" resultMap="listDataMap">
        SELECT distinct
        ccb.id ,ccb.org_code ,ccb.org_name ,ccb.comm_code ,ccb.comm_name,ccb.month ,ccb.year,ccb.status
        ,ccb.create_time
        FROM
        `ck_cleaner_basic` ccb
        LEFT JOIN ck_cleaner_expand cce on cce.basic_id=ccb.id
        <where>
            ccb.status=1
            <if test="null!=query">
                <if test="null!=query.basicId">
                    and ccb.id=#{query.basicId}
                </if>
                <if test="null!=query.searchMap and query.searchMap.size()>0">
                    <foreach collection="query.searchMap.keys" item="key" index="index">
                        and (cce.field_code=#{key} and cce.value=#{query.searchMap[${key}]} and cce.`status` <![CDATA[<>]]>2 )
                    </foreach>
                </if>
                <if test="null!=query.orgCode and ''!=query.orgCode">
                    and ccb.org_code=#{query.orgCode}
                </if>
                <if test="null!=query.commCode and ''!=query.commCode">
                    and ccb.comm_code=#{query.commCode}
                </if>
                <if test="null!=query.orgName and ''!=query.orgName">
                    and ccb.org_name LIKE CONCAT("%",#{query.orgName},"%")
                </if>
                <if test="null!=query.year and ''!=query.year">
                    and ccb.year=#{query.year}
                </if>
                <if test="null!=query.month and ''!=query.month">
                    and ccb.month=#{query.month}
                </if>

                <if test="query.ckRegionRole==true">
                    <choose>
                        <when test="query.regionList!=null and query.regionList.size()>0 ">
                            <foreach collection="query.regionList" item="item" open="and ccb.org_code in (" close=")"
                                     separator=",">
                                #{item.organCode}
                            </foreach>
                        </when>
                    </choose>

                </if>

                <if test="query.ckProjectRole==true">
                    <choose>
                        <when test="query.projectList!=null and query.projectList.size()>0 ">
                            <foreach collection="query.projectList" item="item" open="and ccb.comm_code in (" close=")"
                                     separator=",">
                                #{item.commId}
                            </foreach>
                        </when>
                    </choose>

                </if>
            </if>
        </where>
        ORDER BY ccb.create_time DESC
    </select>
    <select id="cleanerFieldConfigVoList" resultType="cn.com.countrygarden.bi.main.dto.ck.CkCleanerFieldConfigVo">
        SELECT distinct
        ccf.id id,
        ccf.field_code fieldCode,
        ccf.field_name fieldName,
        cce.VALUE value,
        ccf.parent_id parentId ,
        ccf.path path,
        ccf.type type,
        ccf.field_length fieldLength,
        ccf.show_width showWidth,
        ccf.query_flag queryFlag,
        ccf.required_flag requiredFlag,
        ccf.inherit_flag inheritFlag,
        ccf.value_type valueType,
        ccf.soft soft,
        cce.id expandId,
        cce.basic_id basicId,
        ccf.status status,
        ccf.max max,
        ccf.min min
        FROM
        ck_cleaner_expand cce
        LEFT JOIN `ck_cleaner_field_config` ccf ON ccf.field_code = cce.field_code

        <where>
            cce.status=1
            <choose>

                <when test="configStatus!=null">
                    and  ccf.status  = #{configStatus}
                </when>
                <otherwise>
                    and   ccf.status <![CDATA[<>]]> 2
                </otherwise>
            </choose>
            and cce.basic_id=#{basicId}
        </where>
    </select>

    <select id="tatisticsProject" resultType="int">
        SELECT  COUNT(DISTINCT cb.id) FROM ck_cleaner_basic cb
        LEFT JOIN ck_cleaner_expand ce ON ce.basic_id=cb.id
        WHERE cb.status=1 AND ce.status=1 AND ce.value IS NOT NULL AND ce.value <![CDATA[<>]]> ''
        AND cb.month=#{month} and cb.year=#{year}
        <if test="orgCode!=null and orgCode!=''">
            and cb.org_code=#{orgCode}
        </if>
    </select>

    <select id="statisProjDetad" resultType="cn.com.countrygarden.bi.main.dto.ck.CkCleanerTableVo">
        SELECT  DISTINCT cb.id as id,cb.org_name as orgName,cb.comm_name as commName,cb.year as `year`,
        cb.month as `month`,cb.create_name as createName,cb.create_time FROM ck_cleaner_basic cb
        LEFT JOIN ck_cleaner_expand ce ON ce.basic_id=cb.id
        WHERE cb.status=1 AND ce.status=1 AND ce.value IS NOT NULL AND ce.value <![CDATA[<>]]> ''
        AND cb.month=#{month} and cb.year=#{year}
        <if test="orgCode!=null and orgCode!=''">
            and cb.org_code=#{orgCode}
        </if>
        ORDER BY cb.create_time DESC
    </select>

    <select id="ckCleanerCont" resultType="int">
        SELECT COUNT(id) FROM ck_cleaner_basic
        WHERE (`status`=0 OR `status`=2) and `month`=#{month} and `year`=#{year}
        <if test="orgCode!=null and orgCode!=''">
            and org_code=#{orgCode}
        </if>
    </select>

    <select id="selectRegionByBaseIds" resultType="cn.com.countrygarden.bi.main.model.ck.CkFiledValueVo">
        select b.org_code orgCode,b.org_name orgName,ex.field_code fieldCode,SUM(ex.`value`) `value` from ck_cleaner_expand ex LEFT JOIN ck_cleaner_basic b on b.id=ex.basic_id
        WHERE ex.basic_id in
        <foreach collection="basicIds" index="index" item="basicId"
                 open="(" separator="," close=")">
            #{basicId}
        </foreach>
        GROUP BY b.org_code,b.org_name,ex.field_code
    </select>
    <select id="selectListDataNoChild" resultType="cn.com.countrygarden.bi.main.dto.ck.CkCleanerTableVo">
        SELECT distinct
        ccb.id ,ccb.org_code ,ccb.org_name ,ccb.comm_code ,ccb.comm_name,ccb.month ,ccb.year,ccb.status
        FROM
        `ck_cleaner_basic` ccb
        LEFT JOIN ck_cleaner_expand cce on cce.basic_id=ccb.id
        <where>
            ccb.status=1
            <if test="null!=query">
                <if test="null!=query.basicId">
                    and ccb.id=#{query.basicId}
                </if>
                <if test="null!=query.searchMap and query.searchMap.size()>0">
                    <foreach collection="query.searchMap.keys" item="key" index="index">
                        and (cce.field_code=#{key} and cce.value=#{query.searchMap[${key}]} and cce.`status` <![CDATA[<>]]>2 )
                    </foreach>
                </if>
                <if test="null!=query.orgCode and ''!=query.orgCode">
                    and ccb.org_code LIKE CONCAT("%",#{query.orgCode},"%")
                </if>
                <if test="null!=query.commCode and ''!=query.commCode">
                    and ccb.comm_code LIKE CONCAT("%",#{query.commCode},"%")
                </if>
                <if test="null!=query.orgName and ''!=query.orgName">
                    and ccb.org_name LIKE CONCAT("%",#{query.orgName},"%")
                </if>
                <if test="null!=query.year and ''!=query.year">
                    and ccb.year=#{query.year}
                </if>
                <if test="null!=query.month and ''!=query.month">
                    and ccb.month=#{query.month}
                </if>

                <if test="query.ckRegionRole==true">
                    <choose>
                        <when test="query.regionList!=null and query.regionList.size()>0 ">
                            <foreach collection="query.regionList" item="item" open="and ccb.org_code in (" close=")"
                                     separator=",">
                                #{item.organCode}
                            </foreach>
                        </when>
                        <otherwise>
                            and 1=2
                        </otherwise>
                    </choose>

                </if>

                <if test="query.ckProjectRole==true">
                    <choose>
                        <when test="query.projectList!=null and query.projectList.size()>0 ">
                            <foreach collection="query.projectList" item="item" open="and ccb.comm_code in (" close=")"
                                     separator=",">
                                #{item.commId}
                            </foreach>
                        </when>
                        <otherwise>
                            and 1=2
                        </otherwise>
                    </choose>

                </if>
            </if>
        </where>
        ORDER BY ccb.`year` desc,ccb.`month` DESC
    </select>

    <select id="allBasic" resultType="cn.com.countrygarden.bi.main.dto.ck.CkCleanerTableVo">
        <![CDATA[
         SELECT DISTINCT b.id AS basicId,b.org_code AS orgCode,b.org_name AS orgName,b.comm_code AS commCode,
         b.comm_name AS commName,b.month AS `month`,b.year AS `year`,
        (SELECT data_config  FROM ck_cleaner_externaldisplay WHERE basic_id=b.id LIMIT 1) AS dataConfig
        ,b.update_time as updateTime
        FROM ck_cleaner_basic b
        WHERE
         (SELECT data_config  FROM ck_cleaner_externaldisplay WHERE basic_id=b.id LIMIT 1) IS NOT NULL
        AND (SELECT data_config  FROM ck_cleaner_externaldisplay WHERE basic_id=b.id LIMIT 1) <> '[]'
         ]]>
        and b.month=#{month} AND b.year=#{year}
        <if test="updateDate!=null and updateDate!=''">
            AND b.update_time >= #{updateDate}
        </if>
        order by b.update_time desc
    </select>
</mapper>