<?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.districtReport.RdPendingBuildingProjectMapper">
    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="cn.com.countrygarden.bi.main.model.districtReport.RdPendingBuildingProject">
                                    <id column="id" property="id" />
                                                <result column="district_code" property="districtCode" />
                                                <result column="project_code" property="projectCode" />
                                                <result column="project_name" property="projectName" />
                                                <result column="building_type" property="buildingType" />
                                                <result column="manager_area" property="managerArea" />
                                                <result column="contract_close_building_date" property="contractCloseBuildingDate" />
                                                <result column="temporary_close_building_date" property="temporaryCloseBuildingDate" />
                                                <result column="manager_households_count" property="managerHouseholdsCount" />
                                                <result column="hardcover_households_count" property="hardcoverHouseholdsCount" />
                                                <result column="hardcover_households_area" property="hardcoverHouseholdsArea" />
                                                <result column="blank_households_count" property="blankHouseholdsCount" />
                                                <result column="blank_households_area" property="blankHouseholdsArea" />
                                                <result column="property_price" property="propertyPrice" />
                                                <result column="property_area" property="propertyArea" />
                                                <result column="server_center_area" property="serverCenterArea" />
                                                <result column="ENABLE" property="enable" />
                                                <result column="create_time" property="createTime" />
                                                <result column="create_by" property="createBy" />
                                                <result column="create_name" property="createName" />
                                                <result column="update_time" property="updateTime" />
                                                <result column="update_by" property="updateBy" />
                                                <result column="update_name" property="updateName" />
                        </resultMap>
   <select id="queryPageList" resultType="cn.com.countrygarden.bi.main.dto.districtReport.RdPendingBuildingProjectDTO">
            SELECT DISTINCT rp.id as id,rd.region_name as regionName,rp.district_code as districtCode,rd.district_name as districtName,rp.project_code as projectCode,rp.project_name as projectName,rp.building_type as buildingType,rp.manager_area as managerArea,
            rp.contract_close_building_date as contractCloseBuildingDateString,rp.temporary_close_building_date as temporaryCloseBuildingDateString,
            rp.manager_households_count as managerHouseholdsCount,rp.hardcover_households_count as hardcoverHouseholdsCount,rp.hardcover_households_area as hardcoverHouseholdsArea,rp.blank_households_count as blankHouseholdsCount,
            rp.blank_households_area as blankHouseholdsArea,rp.property_price as propertyPrice,rp.property_area as propertyArea,rp.server_center_area as serverCenterArea,
            rp.create_time as createTime,rp.create_name as createName,rp.create_by as createBy
            FROM rd_pending_building_project rp
            LEFT JOIN rd_district_info rd ON rp.district_code=rd.district_code
            LEFT JOIN rd_district_project_mp rm ON rm.district_code=rd.district_code
            LEFT JOIN rd_district_principal rdp ON rd.district_code=rdp.district_code
            <where>
                <if test="organCodeString!=null and organCodeString.size()!=0">
                    and rd.region_code in
                    <foreach collection="organCodeString" item="organCodeString" index="index" open="(" close=")" separator=",">
                        #{organCodeString}
                    </foreach>
                </if>
                <if test="commIdString!=null and commIdString.size()!=0">
                    and rp.project_name in
                    <foreach collection="commIdString" item="commIdString" index="index" open="(" close=")" separator=",">
                        #{commIdString}
                    </foreach>
                </if>
                <if test="principaString!=null and principaString.size()!=0">
                    and rdp.principal_code in
                    <foreach collection="principaString" item="principaString" index="index" open="(" close=")" separator=",">
                        #{principaString}
                    </foreach>
                    AND rdp.ENABLE=1
                </if>
                <if test="rdPendingBuildingProjectDTO.createBy!=null and rdPendingBuildingProjectDTO.createBy!='' ">
                    and rp.create_by=#{rdPendingBuildingProjectDTO.createBy}
                </if>
            <if test="rdPendingBuildingProjectDTO.regionCode!=null and rdPendingBuildingProjectDTO.regionCode!=''">
                and rd.region_code=#{rdPendingBuildingProjectDTO.regionCode}
            </if>
            <if test="rdPendingBuildingProjectDTO.districtCode!=null and rdPendingBuildingProjectDTO.districtCode!=''">
               and rp.district_code=#{rdPendingBuildingProjectDTO.districtCode}
            </if>
            <if test="rdPendingBuildingProjectDTO.projectName!=null and rdPendingBuildingProjectDTO.projectName!=''">
               and rp.project_name like concat('%',#{rdPendingBuildingProjectDTO.projectName},'%')
            </if>
                <if test="rdPendingBuildingProjectDTO.contractCloseBuildingDate1!=null and rdPendingBuildingProjectDTO.contractCloseBuildingDate1!='' and rdPendingBuildingProjectDTO.contractCloseBuildingDate2!=null and rdPendingBuildingProjectDTO.contractCloseBuildingDate2!=''">
                    and rp.contract_close_building_date BETWEEN #{rdPendingBuildingProjectDTO.contractCloseBuildingDate1} and #{rdPendingBuildingProjectDTO.contractCloseBuildingDate2}
                </if>
                <if test="rdPendingBuildingProjectDTO.temporaryCloseBuildingDate1!=null and rdPendingBuildingProjectDTO.temporaryCloseBuildingDate1!='' and rdPendingBuildingProjectDTO.temporaryCloseBuildingDate2!=null and rdPendingBuildingProjectDTO.temporaryCloseBuildingDate2!=''">
                    and rp.temporary_close_building_date BETWEEN #{rdPendingBuildingProjectDTO.temporaryCloseBuildingDate1} and #{rdPendingBuildingProjectDTO.temporaryCloseBuildingDate2}
                </if>
                and rp.ENABLE=1
            </where>
       ORDER BY rp.project_code,rp.district_code,rp.create_time
   </select>

    <select id="queryById" resultType="cn.com.countrygarden.bi.main.dto.districtReport.RdPendingBuildingProjectDTO">
             SELECT rp.id as id,rd.region_name as regionName,rp.district_code as districtCode,rd.district_name as districtName,rp.project_code as projectCode,rp.project_name as projectName,rp.building_type as buildingType,rp.manager_area as managerArea,
            rp.contract_close_building_date as contractCloseBuildingDate,rp.temporary_close_building_date as temporaryCloseBuildingDate,
            rp.manager_households_count as managerHouseholdsCount,rp.hardcover_households_count as hardcoverHouseholdsCount,rp.hardcover_households_area as hardcoverHouseholdsArea,rp.blank_households_count as blankHouseholdsCount,
            rp.blank_households_area as blankHouseholdsArea,rp.property_price as propertyPrice,rp.property_area as propertyArea,rp.server_center_area as serverCenterArea,
            rp.create_time as createTime,rp.create_by as createBy
            FROM rd_pending_building_project rp
            LEFT JOIN rd_district_info rd ON rp.district_code=rd.district_code
            <where>
                <if test="id!=null">
                     and rp.id=#{id}
                </if>
            and rp.ENABLE=1
          </where>
    </select>
    <select id="buildingProjectAddone" resultType="cn.com.countrygarden.bi.main.dto.districtReport.RdPendingBuildingProjectDTO">
        SELECT rp.id as id,rd.region_code as regionCode,rd.region_name as regionName,rp.district_code as districtCode,rd.district_name as districtName,rp.project_code as projectCode,rp.project_name as projectName,rp.building_type as buildingType,rp.manager_area as managerArea,
        rp.contract_close_building_date as contractCloseBuildingDate,rp.temporary_close_building_date as temporaryCloseBuildingDate,
        rp.manager_households_count as managerHouseholdsCount,rp.hardcover_households_count as hardcoverHouseholdsCount,rp.hardcover_households_area as hardcoverHouseholdsArea,rp.blank_households_count as blankHouseholdsCount,
        rp.blank_households_area as blankHouseholdsArea,rp.property_price as propertyPrice,rp.property_area as propertyArea,rp.server_center_area as serverCenterArea,
        rp.create_time as createTime,rp.create_by as createBy
        FROM rd_pending_building_project rp
        LEFT JOIN rd_district_info rd ON rp.district_code=rd.district_code
        <where>
            <if test="regionCode!=null and regionCode!=''">
                and rd.region_code=#{regionCode}
            </if>
            <if test="districtCode!=null and districtCode!=''">
                and rp.district_code=#{districtCode}
            </if>
            <if test="projectName!=null and projectName!=''">
                and rp.project_name=#{projectName}
            </if>
            <if test="createdBy!=null and createdBy!=''">
                and rp.create_by=#{createdBy}
            </if>
            and rp.ENABLE=1
        </where>
    </select>
    <select id="districtProjectName" resultType="int">
        SELECT COUNT(DISTINCT project_name) FROM rd_pending_building_project
        <where>
            <if test="projectName!=null and projectName!=''">
                and project_name=#{projectName}
            </if>
            and ENABLE=1
        </where>
    </select>
    <update id="edit">
            UPDATE rd_pending_building_project
            <set>
                <if test="districtCode!=null and districtCode!=''">district_code = #{districtCode},</if>
                <if test="projectCode!=null and projectCode!=''">project_code = #{projectCode},</if>
                <if test="projectName!=null and projectName!=''">project_name = #{projectName},</if>
                <if test="buildingType!=null and buildingType!=''">building_type = #{buildingType},</if>
                <if test="managerArea!=null">manager_area = #{managerArea},</if>
                <if test="contractCloseBuildingDate!=null">contract_close_building_date = #{contractCloseBuildingDate},</if>
                <if test="temporaryCloseBuildingDate!=null">temporary_close_building_date = #{temporaryCloseBuildingDate},</if>
                <if test="managerHouseholdsCount!=null">manager_households_count = #{managerHouseholdsCount},</if>
                <if test="hardcoverHouseholdsCount!=null">hardcover_households_count = #{hardcoverHouseholdsCount},</if>
                <if test="hardcoverHouseholdsArea!=null">hardcover_households_area = #{hardcoverHouseholdsArea},</if>
                <if test="blankHouseholdsCount!=null">blank_households_count = #{blankHouseholdsCount},</if>
                <if test="blankHouseholdsArea!=null">blank_households_area = #{blankHouseholdsArea},</if>
                <if test="propertyPrice!=null">property_price = #{propertyPrice},</if>
                <if test="propertyArea!=null">property_area = #{propertyArea},</if>
                <if test="serverCenterArea!=null">server_center_area = #{serverCenterArea},</if>
                <if test="updateTime!=null">update_time = #{updateTime},</if>
                <if test="updateBy!=null and updateBy!=''">update_by = #{updateBy},</if>
            </set>
	         WHERE
	              id = #{id};
    </update>

    <update id="deleteUpdate">
              UPDATE rd_pending_building_project set ENABLE=0,update_time=NOW(),update_by=#{updateBy},update_name=#{updateName}
              WHERE id = #{id};
    </update>

    <update id="delpingliangtag">
        UPDATE rd_pending_building_project set ENABLE=0,update_time=NOW(),update_by=#{updateBy},update_name=#{updateName}
        <where>
            <if test="rids!=null and rids.size()!=0">
                id in
                <foreach collection="rids" item="rids" index="index" open="(" close=")" separator=",">
                    #{rids}
                </foreach>

            </if>
        </where>
    </update>

    <select id="evaluationrdUncollectedBuildingProjects" resultType="cn.com.countrygarden.bi.main.dto.districtReport.RdPendingBuildingProjectVO">
        SELECT DISTINCT rp.id as id,rd.region_name as regionName,rp.district_code as districtCode,rd.district_name as districtName,rp.project_code as projectCode,rp.project_name as projectName,rp.building_type as buildingType,rp.manager_area as managerArea,
        rp.contract_close_building_date as contractCloseBuildingDate,rp.temporary_close_building_date as temporaryCloseBuildingDate,
        rp.manager_households_count as managerHouseholdsCount,rp.hardcover_households_count as hardcoverHouseholdsCount,rp.hardcover_households_area as hardcoverHouseholdsArea,rp.blank_households_count as blankHouseholdsCount,
        rp.blank_households_area as blankHouseholdsArea,rp.property_price as propertyPrice,rp.property_area as propertyArea,rp.server_center_area as serverCenterArea,rp.create_time
        FROM rd_pending_building_project rp
        LEFT JOIN rd_district_info rd ON rp.district_code=rd.district_code
        LEFT JOIN rd_district_principal rdp ON rd.district_code=rdp.district_code
        <where>
            <if test="organCodeString!=null and organCodeString.size()!=0">
                and rd.region_code in
                <foreach collection="organCodeString" item="organCodeString" index="index" open="(" close=")" separator=",">
                    #{organCodeString}
                </foreach>
            </if>
            <if test="commIdString!=null and commIdString.size()!=0">
                and rp.project_code in
                <foreach collection="commIdString" item="commIdString" index="index" open="(" close=")" separator=",">
                    #{commIdString}
                </foreach>
            </if>
            <if test="principaString!=null and principaString.size()!=0">
                and rdp.principal_code in
                <foreach collection="principaString" item="principaString" index="index" open="(" close=")" separator=",">
                    #{principaString}
                </foreach>
                AND rdp.ENABLE=1
            </if>
            <if test="rdPendingBuildingProjectDTO.createBy!=null and rdPendingBuildingProjectDTO.createBy!='' ">
                and rp.create_by=#{rdPendingBuildingProjectDTO.createBy}
            </if>
            <if test="rdPendingBuildingProjectDTO.regionCode!=null and rdPendingBuildingProjectDTO.regionCode!=''">
                and rd.region_code=#{rdPendingBuildingProjectDTO.regionCode}
            </if>
            <if test="rdPendingBuildingProjectDTO.projectName!=null and rdPendingBuildingProjectDTO.projectName!=''">
                and rp.project_name like concat('%',#{rdPendingBuildingProjectDTO.projectName},'%')
            </if>
            <if test="rdPendingBuildingProjectDTO.districtCode!=null and rdPendingBuildingProjectDTO.districtCode!=''">
                and rp.district_code=#{rdPendingBuildingProjectDTO.districtCode}
            </if>
            <if test="rdPendingBuildingProjectDTO.projectCode!=null and rdPendingBuildingProjectDTO.projectCode!=''">
                and rp.project_code=#{rdPendingBuildingProjectDTO.projectCode}
            </if>
            <if test="rdPendingBuildingProjectDTO.contractCloseBuildingDate1!=null and rdPendingBuildingProjectDTO.contractCloseBuildingDate1!='' and rdPendingBuildingProjectDTO.contractCloseBuildingDate2!=null and rdPendingBuildingProjectDTO.contractCloseBuildingDate2!=''">
                and rp.contract_close_building_date BETWEEN #{rdPendingBuildingProjectDTO.contractCloseBuildingDate1} and #{rdPendingBuildingProjectDTO.contractCloseBuildingDate2}
            </if>
            <if test="rdPendingBuildingProjectDTO.temporaryCloseBuildingDate1!=null and rdPendingBuildingProjectDTO.temporaryCloseBuildingDate1!='' and rdPendingBuildingProjectDTO.temporaryCloseBuildingDate2!=null and rdPendingBuildingProjectDTO.temporaryCloseBuildingDate2!=''">
                and rp.temporary_close_building_date BETWEEN #{rdPendingBuildingProjectDTO.temporaryCloseBuildingDate1} and #{rdPendingBuildingProjectDTO.temporaryCloseBuildingDate2}
            </if>
            and rp.ENABLE=1
        </where>
        ORDER BY rp.project_code,rp.district_code,rp.create_time
    </select>

    <select id="BuildingProjectOne" resultType="cn.com.countrygarden.bi.main.dto.districtReport.RdPendingBuildingProjectDTO">
         SELECT rp.id as id,rd.region_name as regionName,rp.district_code as districtCode,rd.district_name as districtName,rp.project_code as projectCode,rp.project_name as projectName,rp.building_type as buildingType,rp.manager_area as managerArea,
        rp.contract_close_building_date as contractCloseBuildingDate,rp.temporary_close_building_date as temporaryCloseBuildingDate,
        rp.manager_households_count as managerHouseholdsCount,rp.hardcover_households_count as hardcoverHouseholdsCount,rp.hardcover_households_area as hardcoverHouseholdsArea,rp.blank_households_count as blankHouseholdsCount,
        rp.blank_households_area as blankHouseholdsArea,rp.property_price as propertyPrice,rp.property_area as propertyArea,rp.server_center_area as serverCenterArea,
        rp.create_time as createTime,rp.create_by as createBy
        FROM rd_pending_building_project rp
        LEFT JOIN rd_district_info rd ON rp.district_code=rd.district_code
        <where>
            <if test="districtCode!=null and districtCode!=''">
                and rp.district_code=#{districtCode}
            </if>
            <if test="projectName!=null and projectName!=''">
                and rp.project_name=#{projectName}
            </if>
            and rp.ENABLE=1
        </where>

    </select>

</mapper>