<?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.RdDistrictInfoMapper">
    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="cn.com.countrygarden.bi.main.model.districtReport.RdDistrictInfo">
                                    <id column="id" property="id" />
                                                <result column="region_code" property="regionCode" />
                                                <result column="region_name" property="regionName" />
                                                <result column="district_code" property="districtCode" />
                                                <result column="district_name" property="districtName" />
                                                <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" />
                                                <result column="TYPE" property="type" />
       </resultMap>
    <select id="queryPageList" resultType="cn.com.countrygarden.bi.main.dto.districtReport.RdDistrictInfoDTO">
        SELECT DISTINCT ri.id AS id,ri.region_code AS regionCode,ri.region_name AS regionName,ri.district_code AS districtCode,
        ri.district_name AS districtName,ri.ENABLE AS `enable`,ri.create_time AS createTime, ri.create_by AS createBy,
        ri.create_name AS createName,ri.update_time AS updateTime, ri.update_by AS updateBy,
        ri.TYPE AS `type` FROM rd_district_info ri
        LEFT JOIN rd_district_principal rdp ON ri.district_code=rdp.district_code
            <where>
                <if test="organCodeString!=null and organCodeString.size()!=0">
                    and ri.region_code in
                    <foreach collection="organCodeString" item="organCodeString" index="index" open="(" close=")" separator=",">
                        #{organCodeString}
                    </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="rdDistrictInfoDTO.regionCode!=null and rdDistrictInfoDTO.regionCode!=''">
              and ri.region_code=#{rdDistrictInfoDTO.regionCode}
            </if>
            <if test="rdDistrictInfoDTO.districtName!=null and rdDistrictInfoDTO.districtName!=''">
                and ri.district_name like concat('%',#{rdDistrictInfoDTO.districtName},'%')
            </if>
            and ri.ENABLE=1
            </where>
        ORDER BY ri.district_code,ri.create_time
    </select>

    <select id="queryPrincipalList" resultType="cn.com.countrygarden.bi.main.dto.districtReport.RdDistrictInfoDTO">
          SELECT DISTINCT ri.id AS id,ri.region_code AS regionCode,ri.region_name AS regionName,ri.district_code AS districtCode,
        ri.district_name AS districtName,ri.ENABLE AS `enable`,ri.create_time AS createTime, ri.create_by AS createBy,
        ri.create_name AS createName,ri.update_time AS updateTime, ri.update_by AS updateBy,
        ri.TYPE AS `type`,rdp.principal_name AS principalName FROM rd_district_info ri
        LEFT JOIN rd_district_principal rdp ON ri.district_code=rdp.district_code
        <where>
            <if test="organCodeString!=null and organCodeString.size()!=0">
            and ri.region_code in
            <foreach collection="organCodeString" item="organCodeString" index="index" open="(" close=")" separator=",">
                #{organCodeString}
            </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>
            </if>
            <if test="rdDistrictInfoDTO.regionCode!=null and rdDistrictInfoDTO.regionCode!=''">
                and ri.region_code=#{rdDistrictInfoDTO.regionCode}
            </if>
            <if test="rdDistrictInfoDTO.districtName!=null and rdDistrictInfoDTO.districtName!=''">
                and ri.district_name like concat('%',#{rdDistrictInfoDTO.districtName},'%')
            </if>
            <if test="rdDistrictInfoDTO.principalName!=null and rdDistrictInfoDTO.principalName!=''">
                 and rdp.principal_name like concat('%',#{rdDistrictInfoDTO.principalName},'%')
            </if>
            and ri.ENABLE=1 AND rdp.ENABLE=1
        </where>
        ORDER BY ri.district_code,ri.create_time
    </select>

    <select id="queryById" resultType="cn.com.countrygarden.bi.main.dto.districtReport.RdDistrictInfoDTO">
              SELECT d.id AS id,d.region_code AS regionCode,d.region_name AS regionName,d.district_code AS districtCode,
            d.district_name AS districtName,d.ENABLE AS `enable`,d.create_time AS createTime, d.create_by AS createBy,
            d.update_time AS updateTime,d.update_by AS updateBy,
            d.TYPE AS `type`,p.project_code AS commid,p.project_name AS commName FROM rd_district_info d
            LEFT JOIN rd_district_project_mp p ON d.district_code=p.district_code
            <where>
                   <if test="id!=null">
                       and d.id=#{id}
                   </if>
                and d.ENABLE=1
            </where>
    </select>

    <select id="addonePrincipale" resultType="cn.com.countrygarden.bi.main.dto.districtReport.RdDistrictPrincipalDTO">
        SELECT DISTINCT rdp.id AS id,rdp.region_code AS regionCode,rdp.region_name AS regionName,rdp.district_code AS districtCode,
        rdp.principal_code AS principalCode,rdp.principal_name AS principalName, rdp.authorization_time AS authorizationTime,
        rdp.authorization_name AS authorizationName,rdp.create_time AS createTime,rdp.create_by AS createBy,rdp.create_name AS createName,
        ri.district_name AS districtName,rdp.principal_login as principalLogin FROM rd_district_principal rdp
        LEFT JOIN rd_district_info ri ON ri.district_code=rdp.district_code
        <where>
            <if test="id!=null">
                and ri.id=#{id}
            </if>
            and ri.ENABLE=1 and rdp.ENABLE=1
        </where>
    </select>

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

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

    <update id="update">
          UPDATE rd_district_info
          <set>
              <if test="regionCode!=null and regionCode!=''">region_code = #{regionCode},</if>
              <if test="regionName!=null and regionName!=''">region_name = #{regionName},</if>
              <if test="districtCode!=null and districtCode!=''">district_code = #{districtCode},</if>
              <if test="districtCode!=null and districtCode!=''">district_code = #{districtCode},</if>
              <if test="districtName!=null and districtName!=''">district_name = #{districtName},</if>
              <if test="districtName!=null">ENABLE = #{enable},</if>
              <if test="updateTime!=null">update_time = #{updateTime},</if>
              <if test="type!=null">TYPE = #{type},</if>
              <if test="updateBy!=null and updateBy!=''">update_by = #{updateBy},</if>
          </set>
	         WHERE id = #{id};
    </update>

    <select id="countList" resultType="cn.com.countrygarden.bi.main.model.districtReport.RdDistrictInfo">
          select id AS id,region_code AS regionCode,region_name AS regionName,district_code AS districtCode,
        district_name AS districtName from rd_district_info
          <where>
              <if test="regionName!=null and regionName!=''">
                  and region_name=#{regionName}
              </if>
              <if test="districtName!=null and districtName!=''">
                  and district_name=#{districtName}
              </if>
              and ENABLE=1
          </where>
    </select>

    <select id="rdDistrictInfoBown" resultType="cn.com.countrygarden.bi.main.model.districtReport.RdDistrictInfo">
            SELECT DISTINCT rd.region_code as regionCode,rd.district_code AS districtCode,rd.district_name AS districtName FROM rd_district_info rd
            LEFT JOIN rd_district_principal rdp ON rd.district_code=rdp.district_code
            <where>
                <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="regionCode!=null and regionCode!=''">
                    and rd.region_code=#{regionCode}
                </if>
                and rd.ENABLE=1
            </where>
    </select>

    <select id="rdDistrictInfoBownName" resultType="cn.com.countrygarden.bi.main.model.districtReport.RdDistrictInfo">
        SELECT DISTINCT rd.district_code AS districtCode,rd.district_name AS districtName FROM rd_district_info rd
        LEFT JOIN rd_district_principal rdp ON rd.district_code=rdp.district_code
        <where>
            <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="regionName!=null and regionName!=''">
                and rd.region_name=#{regionName}
            </if>
            <if test="districtName!=null and districtName!=''">
                and rd.district_name=#{districtName}
            </if>
            and rd.ENABLE=1
        </where>

    </select>

    <select id="rdDistrictProjectDTO" resultType="cn.com.countrygarden.bi.main.dto.districtReport.RdDistrictInfoDTO">
        SELECT DISTINCT d.region_code as regionCode,d.district_code AS districtCode,d.district_name AS districtName,p.project_code as commid,p.project_name as commName
         FROM rd_district_info d
        LEFT JOIN rd_district_project_mp p ON d.district_code=p.district_code
        LEFT JOIN rd_district_principal rdp ON d.district_code=rdp.district_code
        <where>
            <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="districtCode!=null and districtCode!=''">
                and d.district_code=#{districtCode}
            </if>
            and d.ENABLE=1
        </where>

    </select>

    <select id="RdDistrictInfoDelCheck" resultType="cn.com.countrygarden.bi.main.dto.districtReport.RdDistrictInfoDelDTO">
             SELECT DISTINCT rds.district_code AS rdsDiCode,rp.district_code AS rpDiCode,rn.district_code AS rnDiCode
              ,ru.district_code AS ruDiCode,rc.district_code AS rcDiCode FROM rd_district_info rd
             LEFT JOIN rd_district_settled rds ON  rds.district_code=rd.district_code AND rds.ENABLE=1
            LEFT JOIN rd_pending_building_project rp ON  rp.district_code=rd.district_code AND rp.ENABLE=1
             LEFT JOIN rd_new_project rn ON  rn.district_code=rd.district_code AND rn.ENABLE=1
            LEFT JOIN rd_uncollected_building_projects ru ON  ru.district_code=rd.district_code AND ru.ENABLE=1
             LEFT JOIN rd_closed_building_project rc ON  rc.district_code=rd.district_code AND rc.ENABLE=1
             WHERE rd.district_code=#{districtCode}
    </select>

    <select id="prCommId" resultType="cn.com.countrygarden.bi.main.dto.districtReport.RdDistrictInfoDelDTO">
                SELECT COUNT(*) AS num FROM rd_district_project_mp rm
                LEFT JOIN rd_pending_building_project rp ON rm.project_code=rp.project_code AND rp.enable=1
                LEFT JOIN rd_new_project rn ON rn.project_code=rm.project_code AND rn.enable=1
                 LEFT JOIN rd_uncollected_building_projects ru ON ru.project_code=rm.project_code AND ru.enable=1
                 LEFT JOIN rd_closed_building_project rc ON rc.project_code=rm.project_code AND rc.enable=1
                 WHERE rc.project_name=#{commName} or rp.project_name=#{commName} or rn.project_name=#{commName} or ru.project_name=#{commName}
    </select>

    <select id="rdDistrictInfoAddOne" resultType="cn.com.countrygarden.bi.main.dto.districtReport.RdDistrictInfoDTO">
                SELECT d.id AS id,d.region_code AS regionCode,d.region_name AS regionName,d.district_code AS districtCode,
            d.district_name AS districtName,d.ENABLE AS `enable`,d.create_time AS createTime, d.create_by AS createBy,
            d.update_time AS updateTime,d.update_by AS updateBy,
            d.TYPE AS `type`,p.project_code AS commid,p.project_name AS commName FROM rd_district_info d
            LEFT JOIN rd_district_project_mp p ON d.district_code=p.district_code
            <where>
                <if test="regionCode!=null and regionCode!=''">
                    and d.region_code=#{regionCode}
                </if>
                <if test="districtName!=null and districtName!=''">
                    and d.district_name=#{districtName}
                </if>
                and d.ENABLE=1
            </where>
    </select>
    <select id="selectuserRoles" resultType="cn.com.countrygarden.bi.main.model.sys.UserRoles">
    SELECT id as id,role_code as roleCode,user_code as userCode,created_date as createdDate
    ,created_by as createdBy,updated_date as updatedDate,updated_by as updatedBy
    FROM user_roles
    WHERE user_code=#{userCode}
    ORDER BY id
    </select>

    <select id="districtNameMp" resultType="cn.com.countrygarden.bi.main.model.districtReport.RdDistrictInfo">
       SELECT district_code as districtCode,district_name as districtName FROM rd_district_info
       <where>
              <if test="districtName!=null and districtName!=''">
                  and district_name=#{districtName}
              </if>
           and ENABLE=1
       </where>
    </select>

    <select id="countProjectMp" resultType="java.lang.Integer">
        SELECT COUNT(id) FROM rd_district_project_mp
        WHERE district_code=#{districtCode} AND project_name=#{projectName} AND ENABLE=1
    </select>
</mapper>