<?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.ksh.CkKshBasicMapper">
    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="cn.com.countrygarden.bi.main.model.ck.ksh.CkKshBasic">
        <id column="id" property="id"/>
        <result column="org_code" property="orgCode"/>
        <result column="org_name" property="orgName"/>
        <result column="comm_name" property="commName"/>
        <result column="comm_code" property="commCode"/>
        <result column="month" property="month"/>
        <result column="year" property="year"/>
        <result column="status" property="status"/>
        <result column="create_by" property="createBy"/>
        <result column="create_time" property="createTime"/>
        <result column="update_by" property="updateBy"/>
        <result column="update_time" property="updateTime"/>
        <result column="is_display" property="isDisplay"/>
        <result column="contractProperty" property="contractProperty"/>
        <result column="is_fillin" property="isFillin"/>
        <result column="is_locking" property="isLocking"/>
        <result column="is_unlocking" property="isUnlocking"/>
    </resultMap>

    <select id="selectBasic" resultMap="BaseResultMap" useCache="false">

        SELECT id,org_code,org_name,comm_code,comm_name,`month`,`year`,`status` FROM ck_ksh_basic
        where `status` = 1 AND org_code = #{ckCleanerBasic.orgCode} AND comm_code = #{ckCleanerBasic.commCode} AND `month` = #{ckCleanerBasic.month} AND `year` = #{ckCleanerBasic.year}
        <if test="ckCleanerBasic.id!=null">
            <![CDATA[
            and id <> #{ckCleanerBasic.id}
             ]]>
        </if>

    </select>

    <select id="statisPr" resultType="cn.com.countrygarden.bi.main.model.ck.ksh.CkKshBasic">
         SELECT DISTINCT cb.id AS id,cb.org_code as orgCode,cb.org_name AS orgName,cb.comm_code as commCode,cb.comm_name AS commName,cb.month AS `month`,cb.year AS `year` FROM ck_ksh_basic cb
         WHERE (cb.status=0 OR cb.status=1) and cb.month=#{ckCleanerBasic.month} and cb.year=#{ckCleanerBasic.year}
         and cb.org_code=#{ckCleanerBasic.orgCode} and cb.comm_code=#{ckCleanerBasic.commCode}
    </select>
    <select id="inheritBasicInfo" resultMap="BaseResultMap">
        SELECT id,org_code , org_name , comm_name,comm_code,month , year , org_name FROM `ck_ksh_basic`
        <where>
            status=1
            <if test="query.orgCode !=null and query.orgCode !=''">
                and org_code =#{query.orgCode}
            </if>
            <if test="query.year !=null and query.year !=''">
                and year =#{query.year}
            </if>
            <if test="query.month !=null and query.month !=''">
                and month =#{query.month}
            </if>
            <if test="query.id !=null ">
                and id  <![CDATA[<>]]> #{query.id}
            </if>
            <choose>
                <when test=" query.commCode !=null and query.commCode !='' and query.commCode=='empty'">
                    and (ISNULL(comm_code) or comm_code="")
                </when>
                <otherwise>
                    and comm_code=#{query.commCode}
                </otherwise>
            </choose>

        </where>
    </select>
    <select id="statisProjDetadNot" resultType="cn.com.countrygarden.bi.main.dto.ck.CkCleanerTableVo2">
        SELECT DISTINCT cb.id AS id,cb.org_code as orgCode,cb.org_name AS orgName,cb.comm_code as commCode,cb.comm_name AS commName,cb.month AS `month`,cb.year AS `year`,
        cb.create_time,cb.contractProperty as contractProperty,cb.actualEnterTime as actualEnterTime
        FROM ck_ksh_basic cb
        WHERE cb.status=1 and cb.is_submit=0 and cb.month=#{month} and cb.year=#{year}
        <if test="orgCode!=null and orgCode!=''">
            and cb.org_code=#{orgCode}
        </if>
        <if test="ckRegionRole==true">
            <choose>
                <when test="regionList!=null and regionList.size()>0 ">
                    <foreach collection="regionList" item="item" open="and cb.org_code in (" close=")"
                             separator=",">
                        #{item.organCode}
                    </foreach>
                </when>
            </choose>

        </if>
        <if test="nianyue!=null and nianyue!=''">
            <![CDATA[
            AND cb.contractProperty <= #{nianyue}
            ]]>
        </if>
        ORDER BY cb.create_time DESC
    </select>

    <select id="statisProjDetadNotCount" resultType="int">
        SELECT count(DISTINCT cb.id)
         FROM ck_ksh_basic cb
        WHERE cb.status=1 and cb.is_submit=0 and cb.month=#{month} and cb.year=#{year}
        <if test="orgCode!=null and orgCode!=''">
            and cb.org_code=#{orgCode}
        </if>
        <if test="nianyue!=null and nianyue!=''">
            <![CDATA[
            AND cb.contractProperty <= #{nianyue}
            ]]>
        </if>
        <if test="ckRegionRole==true">
            <choose>
                <when test="regionList!=null and regionList.size()>0 ">
                    <foreach collection="regionList" item="item" open="and cb.org_code in (" close=")"
                             separator=",">
                        #{item.organCode}
                    </foreach>
                </when>
            </choose>

        </if>

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

        </if>

    </select>

    <update id="updateByPrimaryLocking">
             UPDATE  `biapp`.`ck_ksh_basic`
             <set>
                 <if test="isLocking!=null">
                     `is_locking` = #{isLocking},
                 </if>
                 <if test="isUnlocking!=null">
                     is_unlocking=#{isUnlocking},
                 </if>
             </set>

    </update>

    <update id="updateByPrimaryKisDisplay" >
         UPDATE ck_ksh_basic SET is_display = #{isDisplay} WHERE id = #{id}
    </update>

    <select id="selectadd" resultMap="BaseResultMap">
      SELECT `id`,`org_code`,`org_name`,`comm_code`,`comm_name`,`month`,`year`,
`status`,`create_time`,`is_display`,`contractProperty`,`is_fillin`,`is_locking`,`is_unlocking`,`is_submit`
  FROM ck_ksh_basic  WHERE id NOT IN (SELECT DISTINCT cb.id FROM ck_ksh_basic cb
        LEFT JOIN ck_ksh_expand ce ON ce.basic_id=cb.id
        WHERE cb.status=1 AND ce.status=1 AND cb.is_submit=1
        AND cb.month=#{month} AND cb.year=2021) AND `month`=#{month} AND `year`=2021 AND `status`=1
    </select>

    <select id="statisProjDetQuyuWahu" resultType="cn.com.countrygarden.bi.main.dto.ck.CkCleanerTableVo3">
          SELECT DISTINCT ksal.orgCode AS orgCode, ksal.orgName AS orgName,ksal.month AS `month`,ksal.year AS `year`,SUM(ksal.yilu) AS yilu,SUM(ksal.weilu) AS weilu FROM(
 SELECT cb.`org_code` AS orgCode,cb.`org_name` AS orgName,COUNT(DISTINCT cb.id) AS yilu ,0 AS weilu,cb.month AS `month`,cb.year AS `year` FROM ck_ksh_basic cb
        LEFT JOIN ck_ksh_expand ce ON ce.basic_id=cb.id
        WHERE cb.status=1 AND ce.status=1 AND cb.is_submit=1
        AND cb.month=#{month} AND cb.year=#{year}
        <if test="orgCode!=null and orgCode!=''">
            and cb.org_code=#{orgCode}
        </if>
        <if test="ckRegionRole==true">
            <choose>
                <when test="regionList!=null and regionList.size()>0 ">
                    <foreach collection="regionList" item="item" open="and cb.org_code in (" close=")"
                             separator=",">
                        #{item.organCode}
                    </foreach>
                </when>
            </choose>

        </if>
        GROUP BY cb.`org_code`,cb.`org_name`
     UNION ALL
         SELECT ck.`org_code` AS orgCode,ck.`org_name` AS orgName, 0 AS yilu ,COUNT(DISTINCT ck.id) AS weilu,ck.month AS `month`,ck.year AS `year`
         FROM ck_ksh_basic ck
        WHERE ck.status=1 AND ck.is_submit=0 AND ck.month=#{month} AND ck.year=#{year}
        <if test="orgCode!=null and orgCode!=''">
            and ck.org_code=#{orgCode}
        </if>
        <if test="ckRegionRole==true">
            <choose>
                <when test="regionList!=null and regionList.size()>0 ">
                    <foreach collection="regionList" item="item" open="and ck.org_code in (" close=")"
                             separator=",">
                        #{item.organCode}
                    </foreach>
                </when>
            </choose>

        </if>
       <if test="nianyue!=null and nianyue!=''">
            <![CDATA[
            AND ck.contractProperty <= #{nianyue}
            ]]>
        </if>
         GROUP BY ck.`org_code`,ck.`org_name`

)ksal  GROUP BY ksal.orgCode, ksal.orgName,ksal.month,ksal.year
    </select>
</mapper>