<?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.RdUncollectedBuildingProjectsMapper">
    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="cn.com.countrygarden.bi.main.model.districtReport.RdUncollectedBuildingProjects">
                                    <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="marketing_level" property="marketingLevel" />
                                                <result column="property_level" property="propertyLevel" />
                                                <result column="open_date" property="openDate" />
                                                <result column="opening_date" property="openingDate" />
                                                <result column="project_responsibler" property="projectResponsibler" />
                                                <result column="contact" property="contact" />
                                                <result column="case_count" property="caseCount" />
                                                <result column="goods_sold" property="goodsSold" />
                                                <result column="goods_remaining" property="goodsRemaining" />
                                                <result column="management_type" property="managementType" />
                                                <result column="manager_area" property="managerArea" />
                                                <result column="day_passenger_count" property="dayPassengerCount" />
                                                <result column="villa_count" property="villaCount" />
                                                <result column="house_count" property="houseCount" />
                                                <result column="senior_count" property="seniorCount" />
                                                <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.RdUncollectedBuildingProjectsDTO">
                  SELECT DISTINCT ru.id as id,ru.district_code as districtCode,rd.district_name as districtName,ru.project_code as projectCode,ru.project_name as projectName,ru.marketing_level as marketingLevel,
                  ru.property_level as propertyLevel,ru.open_date as openDateString,
                   ru.opening_date as openingDateString,ru.project_responsibler as projectResponsibler,ru.contact as contact,ru.case_count as caseCount,
                   ru.goods_sold as goodsSold,ru.goods_remaining as goodsRemaining,ru.management_type as managementType,
                   ru.manager_area as managerArea,ru.day_passenger_count as dayPassengerCount,ru.villa_count as villaCount,ru.house_count as houseCount,
                   ru.senior_count as seniorCount,ru.create_name as createName,ru.create_time as createTime,ru.create_by as createBy
                   FROM rd_uncollected_building_projects ru
                   LEFT JOIN rd_district_info rd ON ru.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 ru.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="rdUncollectedBuildingProjectsDTO.districtCode!=null and rdUncollectedBuildingProjectsDTO.districtCode!=''">
                            and ru.district_code=#{rdUncollectedBuildingProjectsDTO.districtCode}
                        </if>
                       <if test="rdUncollectedBuildingProjectsDTO.projectCode!=null and rdUncollectedBuildingProjectsDTO.projectCode!=''">
                           and ru.project_code=#{rdUncollectedBuildingProjectsDTO.projectCode}
                       </if>
                       <if test="rdUncollectedBuildingProjectsDTO.openDateString1!=null and rdUncollectedBuildingProjectsDTO.openDateString1!='' and rdUncollectedBuildingProjectsDTO.openDateString2!=null and rdUncollectedBuildingProjectsDTO.openDateString2!=''">
                            and ru.open_date BETWEEN #{rdUncollectedBuildingProjectsDTO.openDateString1} and #{rdUncollectedBuildingProjectsDTO.openDateString2}
                       </if>
                       <if test="rdUncollectedBuildingProjectsDTO.openingDateString1!=null and rdUncollectedBuildingProjectsDTO.openingDateString1!='' and rdUncollectedBuildingProjectsDTO.openingDateString2!=null and rdUncollectedBuildingProjectsDTO.openingDateString2!=''">
                           and ru.opening_date BETWEEN #{rdUncollectedBuildingProjectsDTO.openingDateString1} and #{rdUncollectedBuildingProjectsDTO.openingDateString2}
                       </if>
                       <if test="rdUncollectedBuildingProjectsDTO.createBy!=null and rdUncollectedBuildingProjectsDTO.createBy!=''">
                           and ru.create_by=#{rdUncollectedBuildingProjectsDTO.createBy}
                       </if>
                        and ru.ENABLE=1
                   </where>
         ORDER BY ru.project_code,ru.district_code,ru.create_time
     </select>

    <select id="queryById" resultType="cn.com.countrygarden.bi.main.dto.districtReport.RdUncollectedBuildingProjectsDTO">
             SELECT ru.id as id,ru.district_code as districtCode,rd.district_name as districtName,ru.project_code as projectCode,ru.project_name as projectName,ru.marketing_level as marketingLevel,
                  ru.property_level as propertyLevel,ru.open_date as openDate,
                   ru.opening_date as openingDate,ru.project_responsibler as projectResponsibler,ru.contact as contact,ru.case_count as caseCount,
                   ru.goods_sold as goodsSold,ru.goods_remaining as goodsRemaining,ru.management_type as managementType,
                   ru.manager_area as managerArea,ru.day_passenger_count as dayPassengerCount,ru.villa_count as villaCount,ru.house_count as houseCount,
                   ru.senior_count as seniorCount,ru.create_time as createTime,ru.create_by as createBy
                   FROM rd_uncollected_building_projects ru
                   LEFT JOIN rd_district_info rd ON ru.district_code=rd.district_code
        <where>
        <if test="id!=null">
            ru.id=#{id}
        </if>
            and ru.ENABLE=1
        </where>
    </select>

    <select id="buildingProjectsAddOne" resultType="cn.com.countrygarden.bi.main.dto.districtReport.RdUncollectedBuildingProjectsDTO">
        SELECT ru.id as id,ru.district_code as districtCode,rd.district_name as districtName,ru.project_code as projectCode,ru.project_name as projectName,ru.marketing_level as marketingLevel,
        ru.property_level as propertyLevel,ru.open_date as openDate,
        ru.opening_date as openingDate,ru.project_responsibler as projectResponsibler,ru.contact as contact,ru.case_count as caseCount,
        ru.goods_sold as goodsSold,ru.goods_remaining as goodsRemaining,ru.management_type as managementType,
        ru.manager_area as managerArea,ru.day_passenger_count as dayPassengerCount,ru.villa_count as villaCount,ru.house_count as houseCount,
        ru.senior_count as seniorCount,ru.create_time as createTime,ru.create_by as createBy
        FROM rd_uncollected_building_projects ru
        LEFT JOIN rd_district_info rd ON ru.district_code=rd.district_code
        <where>
            <if test="districtCode!=null and districtCode!=''">
                 and ru.district_code=#{districtCode}
            </if>
            <if test="projectCode!=null and projectCode!=''">
                and ru.project_code=#{projectCode}
            </if>
            <if test="createdBy!=null and createdBy!=''">
                and ru.create_by=#{createdBy}
            </if>
            and ru.ENABLE=1
        </where>
    </select>

    <update id="updateByPrimary">
          UPDATE rd_uncollected_building_projects
          <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="marketingLevel!=null and marketingLevel!=''">marketing_level = #{marketingLevel},</if>
              <if test="propertyLevel!=null and propertyLevel!=''">property_level = #{propertyLevel},</if>
              <if test="openDate!=null">open_date = #{openDate},</if>
              <if test="openingDate!=null">opening_date = #{openingDate},</if>
              <if test="projectResponsibler!=null and projectResponsibler!=''">project_responsibler = #{projectResponsibler},</if>
              <if test="contact!=null and contact!=''">contact = #{contact},</if>
              <if test="caseCount!=null">case_count = #{caseCount},</if>
              <if test="goodsSold!=null">goods_sold = #{goodsSold},</if>
              <if test="goodsRemaining!=null">goods_remaining = #{goodsRemaining},</if>
              <if test="managementType!=null and managementType!=''">management_type = #{managementType},</if>
              <if test="managerArea!=null">manager_area = #{managerArea},</if>
              <if test="dayPassengerCount!=null">day_passenger_count = #{dayPassengerCount},</if>
              <if test="villaCount!=null">villa_count = #{villaCount},</if>
              <if test="houseCount!=null">house_count = #{houseCount},</if>
              <if test="seniorCount!=null">senior_count = #{seniorCount},</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_uncollected_building_projects set ENABLE=0,update_time=NOW(),update_by=#{updateBy},update_name=#{updateName}
          WHERE id=#{id};
    </update>

    <update id="delpingliangtag">
        UPDATE rd_uncollected_building_projects 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.RdUncollectedBuildingProjectsVO">
               SELECT DISTINCT ru.id as id,ru.district_code as districtCode,rd.district_name as districtName,ru.project_code as projectCode,ru.project_name as projectName,ru.marketing_level as marketingLevel,
                  ru.property_level as propertyLevel,ru.open_date as openDate,
                   ru.opening_date as openingDate,ru.project_responsibler as projectResponsibler,ru.contact as contact,ru.case_count as caseCount,
                   ru.goods_sold as goodsSold,ru.goods_remaining as goodsRemaining,ru.management_type as managementType,
                   ru.manager_area as managerArea,ru.day_passenger_count as dayPassengerCount,ru.villa_count as villaCount,ru.house_count as houseCount,
                   ru.senior_count as seniorCount,ru.create_time as createTime,ru.create_by as createBy
                   FROM rd_uncollected_building_projects ru
                   LEFT JOIN rd_district_info rd ON ru.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 ru.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="rdUncollectedBuildingProjectsDTO.districtCode!=null and rdUncollectedBuildingProjectsDTO.districtCode!=''">
                        and ru.district_code=#{rdUncollectedBuildingProjectsDTO.districtCode}
                    </if>
                    <if test="rdUncollectedBuildingProjectsDTO.projectCode!=null and rdUncollectedBuildingProjectsDTO.projectCode!=''">
                        and ru.project_code=#{rdUncollectedBuildingProjectsDTO.projectCode}
                    </if>
                    <if test="rdUncollectedBuildingProjectsDTO.openDateString1!=null and rdUncollectedBuildingProjectsDTO.openDateString1!='' and rdUncollectedBuildingProjectsDTO.openDateString2!=null and rdUncollectedBuildingProjectsDTO.openDateString2!=''">
                        and ru.open_date BETWEEN #{rdUncollectedBuildingProjectsDTO.openDateString1} and #{rdUncollectedBuildingProjectsDTO.openDateString2}
                    </if>
                    <if test="rdUncollectedBuildingProjectsDTO.openingDateString1!=null and rdUncollectedBuildingProjectsDTO.openingDateString1!='' and rdUncollectedBuildingProjectsDTO.openingDateString2!=null and rdUncollectedBuildingProjectsDTO.openingDateString2!=''">
                        and ru.opening_date BETWEEN #{rdUncollectedBuildingProjectsDTO.openingDateString1} and #{rdUncollectedBuildingProjectsDTO.openingDateString2}
                    </if>
                    and ru.ENABLE=1
            </where>
        ORDER BY ru.project_code,ru.district_code,ru.create_time
    </select>

    <select id="BuildingProjectsOne" resultType="cn.com.countrygarden.bi.main.dto.districtReport.RdUncollectedBuildingProjectsDTO">
                  SELECT ru.id as id,ru.district_code as districtCode,rd.district_name as districtName,ru.project_code as projectCode,ru.project_name as projectName,ru.marketing_level as marketingLevel,
                  ru.property_level as propertyLevel,ru.open_date as openDate,
                   ru.opening_date as openingDate,ru.project_responsibler as projectResponsibler,ru.contact as contact,ru.case_count as caseCount,
                   ru.goods_sold as goodsSold,ru.goods_remaining as goodsRemaining,ru.management_type as managementType,
                   ru.manager_area as managerArea,ru.day_passenger_count as dayPassengerCount,ru.villa_count as villaCount,ru.house_count as houseCount,
                   ru.senior_count as seniorCount,ru.create_time as createTime,ru.create_by as createBy
                   FROM rd_uncollected_building_projects ru
                   LEFT JOIN rd_district_info rd ON ru.district_code=rd.district_code
                   <where>
                       <if test="districtName!=null and districtName!=''">
                           and rd.district_name=#{districtName}
                       </if>
                       <if test="projectName!=null and projectName!=''">
                           and ru.project_name=#{projectName}
                       </if>
                       and ru.ENABLE=1
                   </where>
    </select>

    <select id="dUncollectedBuildingProjectsAddone" resultType="int">
        SELECT COUNT(DISTINCT ru.project_code) FROM rd_uncollected_building_projects ru
        LEFT JOIN rd_district_info d ON d.district_code=ru.district_code
        <where>
            <if test="projectCode!=null and projectCode!=''">
                and ru.project_code=#{projectCode}
            </if>

            and ru.ENABLE=1
        </where>
    </select>
</mapper>