fj-dc/.svn/pristine/df/dfbf7f73bc1bae207c39d6e0d4e107da6a5364dc.svn-base

<?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.goldenwater.dcproj.dao.AttAdBaseDao">

    <resultMap type="cn.com.goldenwater.dcproj.model.AttAdBase" id="attAdBaseResultMap">
        <result property="guid" column="GUID"/>
        <result property="adCode" column="AD_CODE"/>
        <result property="adName" column="AD_NAME"/>
        <result property="adType" column="AD_TYPE"/>
        <result property="lowLeftLong" column="LOW_LEFT_LONG"/>
        <result property="lowLeftLat" column="LOW_LEFT_LAT"/>
        <result property="upRightLong" column="UP_RIGHT_LONG"/>
        <result property="upRightLat" column="UP_RIGHT_LAT"/>
        <result property="adGrad" column="AD_GRAD"/>
        <result property="adFullName" column="AD_FULL_NAME"/>
        <result property="upAdGuid" column="UP_AD_GUID"/>
        <result property="note" column="NOTE"/>
        <result property="collTime" column="COLL_TIME"/>
        <result property="updTime" column="UPD_TIME"/>
        <result property="recPers" column="REC_PERS"/>
        <result property="stat" column="STAT"/>
    </resultMap>

    <sql id="table_columns">
        GUID,
        AD_CODE,
        AD_NAME,
        <!--AD_TYPE,-->
        LOW_LEFT_LONG,
        LOW_LEFT_LAT,
        UP_RIGHT_LONG,
        UP_RIGHT_LAT,
        AD_GRAD,
        <!--UP_AD_GUID,-->
        NOTE,
        <!--COLL_TIME,
        UPD_TIME,
        REC_PERS,-->
        STAT
    </sql>
    <sql id="entity_properties">
        #{guid},
        #{adCode},
        #{adName},
        <!--#{adType},-->
        #{lowLeftLong},
        #{lowLeftLat},
        #{upRightLong},
        #{upRightLat},
        #{adGrad},
        <!--#{upAdGuid},-->
        #{note},
        <!--#{collTime},
        #{updTime},
        #{recPers},-->
        #{stat}
    </sql>

    <!-- 使用like用法：columnName like concat('%',#columnName#,'%') -->
    <sql id="page_where">
        <trim prefix="where" prefixOverrides="and | or ">
            <if test="adCode != null and adCode != ''">and AD_CODE LIKE '${adCode}%'</if>
            <if test="adName != null and adName != ''">and AD_NAME = #{adName}</if>
            <if test="adType != null and adType != ''">and AD_TYPE = #{adType}</if>
            <if test="lowLeftLong != null and lowLeftLong != ''">and LOW_LEFT_LONG = #{lowLeftLong}</if>
            <if test="lowLeftLat != null and lowLeftLat != ''">and LOW_LEFT_LAT = #{lowLeftLat}</if>
            <if test="upRightLong != null and upRightLong != ''">and UP_RIGHT_LONG = #{upRightLong}</if>
            <if test="upRightLat != null and upRightLat != ''">and UP_RIGHT_LAT = #{upRightLat}</if>
            <if test="adGrad != null and adGrad != ''">and AD_GRAD = #{adGrad}</if>
            <if test="upAdGuid != null and upAdGuid != ''">and UP_AD_GUID = #{upAdGuid}</if>
            <if test="note != null and note != ''">and NOTE = #{note}</if>
            <if test="collTime != null">and COLL_TIME = #{collTime}</if>
            <if test="updTime != null">and UPD_TIME = #{updTime}</if>
            <if test="stat != null and stat != ''">and STAT = #{stat}</if>
        </trim>
    </sql>


    <select id="get" resultMap="attAdBaseResultMap" parameterType="String">
        select * from att_ad_base where ad_code = #{id}
    </select>

    <select id="getBy" resultMap="attAdBaseResultMap">
        select
        <include refid="table_columns"/>
        from att_ad_base
        <include refid="page_where"/>
    </select>

    <select id="findAll" resultMap="attAdBaseResultMap">
        select
        <include refid="table_columns"/>
        from att_ad_base
    </select>

    <select id="findList" resultMap="attAdBaseResultMap">
        select
        GUID,
        AD_CODE,
        AD_NAME,
        LOW_LEFT_LONG,
        LOW_LEFT_LAT,
        UP_RIGHT_LONG,
        UP_RIGHT_LAT,
        AD_GRAD,
        AD_FULL_NAME,
        NOTE,
        STAT
        from att_ad_base
        <include refid="page_where"/>
        ORDER BY AD_CODE ASC
    </select>

    <select id="selectCount" resultType="int">
        select count(GUID) from att_ad_base
        <include refid="page_where"/>
    </select>


    <insert id="insert" parameterType="cn.com.goldenwater.dcproj.model.AttAdBase">
        insert into att_ad_base(
        <include refid="table_columns"/>
        )
        values (
        <include refid="entity_properties"/>
        )
    </insert>

    <delete id="delete" parameterType="java.lang.String">
        delete from att_ad_base where GUID = #{id}
    </delete>

    <delete id="deleteBy" parameterType="cn.com.goldenwater.dcproj.model.AttAdBase">
        delete from att_ad_base
        <include refid="page_where"/>
    </delete>

    <update id="deleteInFlag" parameterType="java.lang.String">
        update att_ad_base set flag_valid = 0 where GUID = #{id}
    </update>

    <update id="update" parameterType="cn.com.goldenwater.dcproj.model.AttAdBase">
        update att_ad_base
        <trim prefix="set" suffixOverrides=",">
            <if test="adCode != null and adCode != ''">AD_CODE = #{adCode},</if>
            <if test="adName != null and adName != ''">AD_NAME = #{adName},</if>
            <if test="adType != null and adType != ''">AD_TYPE = #{adType},</if>
            <if test="lowLeftLong != null and lowLeftLong != ''">LOW_LEFT_LONG = #{lowLeftLong},</if>
            <if test="lowLeftLat != null and lowLeftLat != ''">LOW_LEFT_LAT = #{lowLeftLat},</if>
            <if test="upRightLong != null and upRightLong != ''">UP_RIGHT_LONG = #{upRightLong},</if>
            <if test="upRightLat != null and upRightLat != ''">UP_RIGHT_LAT = #{upRightLat},</if>
            <if test="adGrad != null and adGrad != ''">AD_GRAD = #{adGrad},</if>
            <if test="upAdGuid != null and upAdGuid != ''">UP_AD_GUID = #{upAdGuid},</if>
            <if test="note != null and note != ''">NOTE = #{note},</if>
            <if test="collTime != null">COLL_TIME = #{collTime},</if>
            <if test="updTime != null">UPD_TIME = #{updTime},</if>
            <if test="recPers != null and recPers != ''">REC_PERS = #{recPers},</if>
            <if test="stat != null and stat != ''">STAT = #{stat},</if>
        </trim>
        <where>GUID = #{guid}</where>
    </update>

    <update id="updateBy" parameterType="cn.com.goldenwater.dcproj.model.AttAdBase">
        update att_ad_base
        <trim prefix="set" suffixOverrides=",">
            <if test="adCode != null and adCode != ''">AD_CODE = #{adCode},</if>
            <if test="adName != null and adName != ''">AD_NAME = #{adName},</if>
            <if test="adType != null and adType != ''">AD_TYPE = #{adType},</if>
            <if test="lowLeftLong != null and lowLeftLong != ''">LOW_LEFT_LONG = #{lowLeftLong},</if>
            <if test="lowLeftLat != null and lowLeftLat != ''">LOW_LEFT_LAT = #{lowLeftLat},</if>
            <if test="upRightLong != null and upRightLong != ''">UP_RIGHT_LONG = #{upRightLong},</if>
            <if test="upRightLat != null and upRightLat != ''">UP_RIGHT_LAT = #{upRightLat},</if>
            <if test="adGrad != null and adGrad != ''">AD_GRAD = #{adGrad},</if>
            <if test="upAdGuid != null and upAdGuid != ''">UP_AD_GUID = #{upAdGuid},</if>
            <if test="note != null and note != ''">NOTE = #{note},</if>
            <if test="collTime != null">COLL_TIME = #{collTime},</if>
            <if test="updTime != null">UPD_TIME = #{updTime},</if>
            <if test="recPers != null and recPers != ''">REC_PERS = #{recPers},</if>
            <if test="stat != null and stat != ''">STAT = #{stat},</if>
        </trim>
        <include refid="page_where"/>
    </update>


    <!-- 其他自定义SQL -->

    <select id="getByAdcode" resultMap="attAdBaseResultMap" parameterType="String">
        select
       *
        from att_ad_base where AD_CODE = #{code}
    </select>
    <select id="getAllChildByCode" resultMap="attAdBaseResultMap" parameterType="String">
        select * from att_ad_base where AD_CODE  like '${code}%'
    </select>

    <!--查询督查范围内的对下那个-->
    <select id="getListByInspGroupIdObjType" parameterType="cn.com.goldenwater.dcproj.param.PagePersObjParam"
            resultType="cn.com.goldenwater.dcproj.model.AttAdBase">
        select t.*,C.id,C.PNM,o.obj_id
        from ATT_AD_X_BASE t
        left join BIS_INSP_ALL_OBJ o
        on o.CODE = t.Ad_Code LEFT JOIN BIS_INSP_ALL C ON o.ID = C.ID
        and o.ptype =#{objType}
        <where>
            <if test='isAll == "0"'>and o.ID = #{inspGroupId}</if>
            <if test='isAll == "1"'>and o.ID LIKE '${inspGroupId}%'</if>
            <if test="adCode != null and adCode != ''">and t.AD_CODE LIKE '${adCode}%'</if>
            <choose>
                <when test="province !=null and province !=''">
                    and o.ad_code like '${province}%'
                </when>
                <otherwise>
                    and o.ad_code is null
                </otherwise>
            </choose>
            <if test="adName != null and adName != ''">and t.AD_NAME LIKE '%${adName}%'</if>
            <if test="wtdstNm != null and wtdstNm != ''">and t.AD_NAME LIKE '%${wtdstNm}%'</if>
        </where>
    </select>

    <!--查询不在范围内的对象-->
    <select id="getObjListNotInspGroupId" resultType="cn.com.goldenwater.dcproj.model.AttAdBase">
        SELECT * FROM ATT_AD_BASE WHERE AD_CODE NOT in (
        select CODE from BIS_INSP_ALL_OBJ where id like concat(#{inspGroupId},'%'))
        <if test="adName != null and adName != ''">and AD_NAME LIKE '%${adName}%'</if>
        <if test="adCode != null and adCode != ''">and AD_CODE LIKE '${adCode}%'</if>
        <if test="province !=null and province !=''">
            and ad_code like '${province}%'
        </if>
        order by ad_code asc
    </select>
    <select id="getObjListByGroupIdAndTypeNew" resultType="cn.com.goldenwater.dcproj.model.AttAdBase">
        select * from BIS_INSP_ALL_OBJ where id like concat('','%')
    </select>

    <sql id="orgIdSql">
        <choose>
            <when test="orgId !=null and orgId !=''">

                and ORG_ID=#{orgId}
            </when>
            <otherwise>
                and ORG_ID is null
            </otherwise>
        </choose>
    </sql>

    <select id="getObjByPersId" parameterType="cn.com.goldenwater.dcproj.param.PersObjParam"
            resultType="cn.com.goldenwater.dcproj.dto.SecsurveyVlgDto">
        SELECT A.VILL_ID,
        A.ENG_ID,
        A.VILLAGE_CODE,
        A.IS_POVERTY,
        A.IS_FLUEXC,
        A.IS_MTAREA,
        A.IS_CENWT_SUPPLY,
        A.WATERSUPPLY_PER,
        A.VISIT_DATE,
        A.REC_PERS_ID,
        A.REC_PERS2,
        A.REC_PERS_TEL,
        A.CREATE_TIME,
        A.UPDATE_TIME,
        A.STATUS,
        A.AD_NM,C.OBJ_ID,
        A.WAS_POVERTY,ad.LGTD lgtdPc,ad.LTTD lttdPc,ad.AD_LONG centerXGd,ad.AD_LAT centerYGd
        FROM BIS_INSP_SECSURVEY_VLG A
        LEFT JOIN BIS_INSP_VILL_RGSTR B ON A.ENG_ID = B.ENG_ID
        LEFT JOIN BIS_INSP_ALL_OBJ C ON B.OBJ_ID = C.OBJ_ID
        LEFT JOIN ATT_AD_X_BASE ad on A.VILLAGE_CODE = ad.AD_CODE
        WHERE C.PTYPE = #{objType}
        <choose>
            <when test="province !=null and province !=''">
                and A.VILLAGE_CODE like '${province}%'
            </when>
            <otherwise>
                and C.AD_CODE is null
            </otherwise>
        </choose>
        AND
        REGEXP_LIKE(
        C.Id,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID = #{persGuid}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID = #{persGuid}
        <include refid="orgIdSql"/>
        )
        else (select 'non' from dual) end)
        ||')')

        <if test="adName != null and adName != ''">and A.AD_NM LIKE '%${adName}%'</if>
        <if test="adCode != null and adCode != ''">and A.VILLAGE_CODE LIKE '${adCode}%'</if>
        <if test="maxLgtd != null and maxLgtd != '' and minLgtd != null and minLgtd != ''">and ad.LGTD BETWEEN
            #{minLgtd} AND #{maxLgtd}
        </if>
        <if test="maxLttd != null and maxLttd != '' and minLttd != null and minLttd != ''">and ad.LTTD BETWEEN
            #{minLttd} AND #{maxLttd}
        </if>
        <if test="maxcenterXGd != null and maxcenterXGd != '' and mincenterXGd != null and mincenterXGd != ''">and
            ad.AD_LONG BETWEEN #{mincenterXGd} AND #{maxcenterXGd}
        </if>
        <if test="maxcenterYGd != null and maxcenterYGd != '' and mincenterYGd != null and mincenterYGd != ''">and
            ad.AD_LAT BETWEEN #{mincenterYGd} AND #{maxcenterYGd}
        </if>
    </select>

    <select id="getPspByPersId" parameterType="cn.com.goldenwater.dcproj.param.PersObjParam"
            resultType="cn.com.goldenwater.dcproj.dto.ProSourceProtectDto">
        SELECT A.ENG_SUR_ID,
        A.ENG_ID,
        A.WATER_SOURCE_NM,
        A.VILLAGE_NM,
        A.CWS_CODE,
        A.PLAN_INS_AMOUNT,
        A.SOURCE_TYPE,
        A.IS_PROTECT_AREA,
        A.WATER_QUALITY,
        A.IS_PROTECT_MARK,
        A.IS_POLL_SOURCE,
        A.IS_SPARE,
        A.VISIT_DATE,
        A.REC_PERS_ID,
        A.REC_PERS2,
        A.REC_PERS_TEL,
        A.CREATE_TIME,
        A.UPDATE_TIME,
        A.STATUS,
        A.WTSR_CD,
        A.PRO_PLBM,
        A.MK_PLBM,
        A.POLL_PLBM,
        A.SP_PLBM,
        A.LGTD,
        A.LTTD,
        A.LGTD centerXGd,
        A.LTTD centerYGd,
        A.LGTDPC lgtdPc,
        A.LTTDPC lttdPc,C.OBJ_ID,
        A.POLL_NOTE FROM BIS_INSP_PRO_SOURCE_PROTECT A LEFT JOIN BIS_INSP_VILL_RGSTR B ON A.ENG_ID = B.ENG_ID
        LEFT JOIN BIS_INSP_ALL_OBJ C ON B.OBJ_ID = C.OBJ_ID
        WHERE 1 = 1 AND C.PTYPE = #{objType} AND B.ENG_ID IS NOT NULL
        <choose>
            <when test="province !=null and province !=''">
                and C.AD_CODE=#{province}
            </when>
            <otherwise>
                and C.AD_CODE is null
            </otherwise>
        </choose>
        AND
        REGEXP_LIKE(
        C.Id,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID = #{persGuid}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID = #{persGuid}
        <include refid="orgIdSql"/>
        )
        else (select 'non' from dual) end)
        ||')')
        <if test="province != null and province != ''">and C.CODE LIKE '${province}%'</if>
        <if test="adName != null and adName != ''">and A.WATER_SOURCE_NM LIKE '%${adName}%'</if>
        <if test="adCode != null and adCode != ''">and C.CODE LIKE '${adCode}%'</if>
        <if test="maxLgtd != null and maxLgtd != '' and minLgtd != null and minLgtd != ''">and A.LGTDPC BETWEEN
            #{minLgtd} AND #{maxLgtd}
        </if>
        <if test="maxLttd != null and maxLttd != '' and minLttd != null and minLttd != ''">and A.LTTDPC BETWEEN
            #{minLttd} AND #{maxLttd}
        </if>
        <if test="maxcenterXGd != null and maxcenterXGd != '' and mincenterXGd != null and mincenterXGd != ''">and
            A.LGTD BETWEEN #{mincenterXGd} AND #{maxcenterXGd}
        </if>
        <if test="maxcenterYGd != null and maxcenterYGd != '' and mincenterYGd != null and mincenterYGd != ''">and
            A.LTTD BETWEEN #{mincenterYGd} AND #{maxcenterYGd}
        </if>
    </select>

    <select id="getCwsByPersId" parameterType="cn.com.goldenwater.dcproj.param.PersObjParam"
            resultType="cn.com.goldenwater.dcproj.dto.AttCwsBaseDto">
        SELECT A.GUID,
        A.CWS_CODE,
        A.CWS_NAME,
        A.CWS_LONG,
        A.CWS_LAT,
        A.CWS_LOC,
        A.ENG_TYPE,
        A.WASU_TYPE,
        A.WASU_RANG,
        A.DES_WASU_SCAL,
        A.DES_WASU_POP,
        A.ENG_STAT,
        A.START_DATE,
        A.COMP_DATE,
        A.NOTE,
        A.EFF_DATE,
        A.EXPR_DATE,
        A.ADDVCD,
        A.CENTER_X_GD,
        A.CENTER_Y_GD,
        A.INTM,
        A.EDTR_PESR,
        A.UPTM,C.OBJ_ID,
        A.CHK_STATE FROM ATT_CWS_BASE A
        LEFT JOIN BIS_INSP_ALL_OBJ C ON A.ADDVCD = C.CODE
        WHERE 1 = 1 AND C.PTYPE = #{objType}
        <choose>
            <when test="province !=null and province !=''">
                and A.ADDVCD like '${province}%'
            </when>
            <otherwise>
                and C.AD_CODE is null
            </otherwise>
        </choose>
        <!-- AND
         REGEXP_LIKE(
         C.Id,'^('||
         ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID = #{persGuid}
         <include refid="orgIdSql"/>
         )>0 then
         (SELECT  LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
         id FROM BIS_INSP_ALL_RLATION WHERE PERSID = #{persGuid}
         <include refid="orgIdSql"/>
         )
         else  (select 'non' from dual)  end)
         ||')')-->

        <if test="adName != null and adName != ''">and A.CWS_NAME LIKE '%${adName}%'</if>
        <if test="adCode != null and adCode != ''">and A.ADDVCD LIKE '${adCode}%'</if>
        <if test="maxLgtd != null and maxLgtd != '' and minLgtd != null and minLgtd != ''">and A.CWS_LONG BETWEEN
            #{minLgtd} AND #{maxLgtd}
        </if>
        <if test="maxLttd != null and maxLttd != '' and minLttd != null and minLttd != ''">and A.CWS_LAT BETWEEN
            #{minLttd} AND #{maxLttd}
        </if>
        <if test="maxcenterXGd != null and maxcenterXGd != '' and mincenterXGd != null and mincenterXGd != ''">and
            A.CENTER_X_GD BETWEEN #{mincenterXGd} AND #{maxcenterXGd}
        </if>
        <if test="maxcenterYGd != null and maxcenterYGd != '' and mincenterYGd != null and mincenterYGd != ''">and
            A.CENTER_Y_GD BETWEEN #{mincenterYGd} AND #{maxcenterYGd}
        </if>
        <if test="desWasuScal != null and desWasuScal != ''">and A.DES_WASU_SCAL &gt;= #{desWasuScal}</if>
    </select>

    <select id="getAdData" parameterType="String" resultType="cn.com.goldenwater.dcproj.model.AttAdBase">
        select guid,
        ad_code,
        ad_name,
        low_left_long,
        low_left_lat,
        up_right_long,
        up_right_lat,
        up_ad_name,
        ad_grad,
        ad_abbr_name,
        ad_stat,
        ad_area,
        ad_full_name,
        note,
        eff_date,
        expr_date,
        ad_stat_long,
        ad_stat_lat,
        domain_code,
        ad_sign,
        modifier_people,
        upd_date,
        is_report,
        ad_fcode,lgtd,lttd from att_ad_base WHERE AD_FCODE = #{adCode} ORDER BY AD_CODE
    </select>

    <select id="fingAdUp" parameterType="String" resultType="cn.com.goldenwater.dcproj.dto.AdUpDto">
        SELECT * FROM ATT_AD_BASE WHERE AD_CODE = #{adCode}
    </select>


    <select id="getAdBaseByPersId" parameterType="cn.com.goldenwater.dcproj.param.PersObjParam"
            resultType="cn.com.goldenwater.dcproj.model.AttAdBase">
        SELECT * FROM BIS_INSP_ALL_OBJ A LEFT JOIN ATT_AD_BASE B ON A.CODE = B.AD_CODE
        WHERE A.PTYPE = '2'
        <choose>
            <when test="province !=null and province !=''">
                and A.AD_CODE=#{province}
            </when>
            <otherwise>
                and A.AD_CODE is null
            </otherwise>
        </choose>
        AND
        REGEXP_LIKE(
        A.Id,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID = #{persGuid}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID = #{persGuid}
        <include refid="orgIdSql"/>
        )
        else (select 'non' from dual) end)
        ||')')
        <if test="adName != null and adName != ''">and B.AD_NAME LIKE '%${adName}%'</if>
        <if test="adCode != null and adCode != ''">and A.CODE LIKE '${adCode}%'</if>
        <if test="maxLgtd != null and maxLgtd != '' and minLgtd != null and minLgtd != ''">and B.LGTD BETWEEN #{minLgtd}
            AND #{maxLgtd}
        </if>
        <if test="maxLttd != null and maxLttd != '' and minLttd != null and minLttd != ''">and B.LTTD BETWEEN #{minLttd}
            AND #{maxLttd}
        </if>
        <if test="maxcenterXGd != null and maxcenterXGd != '' and mincenterXGd != null and mincenterXGd != ''">and
            B.AD_LONG BETWEEN #{mincenterXGd} AND #{maxcenterXGd}
        </if>
        <if test="maxcenterYGd != null and maxcenterYGd != '' and mincenterYGd != null and mincenterYGd != ''">and
            B.AD_LAT BETWEEN #{mincenterYGd} AND #{maxcenterYGd}
        </if>
    </select>
    <select id="getAllChildren" resultType="cn.com.goldenwater.dcproj.dto.AdUpDto">
        select * from ATT_AD_BASE where AD_CODE LIKE '${adCode}%' and AD_GRAD &lt;= 4
    </select>

    <!-- 根据行政区划 代码 获取 全名 -->
    <select id="getNameByAdCode" resultType="String" >
        select
        AD_FULL_NAME
        from att_ad_base where AD_CODE = #{code}
    </select>
</mapper>