fj-dc/.svn/pristine/9e/9e8e05fc0d0b6a421cfe14b5a2f39ffeac7673de.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.BisInspObjDao">

    <resultMap type="cn.com.goldenwater.dcproj.model.BisInspObj" id="bisInspObjResultMap">
        <result property="objId" column="OBJ_ID"/>
        <result property="objType" column="OBJ_TYPE"/>
        <result property="inspGroupId" column="INSP_GROUP_ID"/>
        <result property="plnaId" column="PLNA_ID"/>
        <result property="objName" column="OBJ_NAME"/>
        <result property="guid" column="GUID"/>
    </resultMap>


    <sql id="table_columns">
        OBJ_ID,
        OBJ_TYPE,
        INSP_GROUP_ID,
        PLNA_ID,
        GUID
    </sql>
    <sql id="entity_properties">
        #{objId},
        #{objType},
        #{inspGroupId},
        #{plnaId},
        #{guid}
    </sql>
    <sql id="table_columnsNew">
        OBJ_ID,
        CODE,
        ID,
        NM,
        ptype,
        lgtd,
        lttd
    </sql>
    <sql id="entity_propertiesNew">
        #{objId},
        #{objType},
        #{inspGroupId},
        #{plnaId},
        #{guid}
    </sql>
    <!-- 使用like用法：columnName like concat('%',#columnName#,'%') -->
    <sql id="page_where">
        <trim prefix="where" prefixOverrides="and | or ">
            <if test="objType != null and objType != ''">and OBJ_TYPE = #{objType}</if>
            <if test="inspGroupId != null and inspGroupId != ''">and INSP_GROUP_ID = #{inspGroupId}</if>
            <if test="guid != null and guid != ''">and GUID = #{guid}</if>
            <if test="plnaId != null and plnaId != ''">and PLNA_ID = #{plnaId}</if>
        </trim>
    </sql>


    <select id="get" resultMap="bisInspObjResultMap" parameterType="String">
        select
        <include refid="table_columns"/>
        from bis_insp_obj where OBJ_ID = #{id}
    </select>

    <select id="getBy" resultMap="bisInspObjResultMap">
        select
        <include refid="table_columns"/>
        from bis_insp_obj
        <include refid="page_where"/>
    </select>

    <select id="findAll" resultMap="bisInspObjResultMap">
        select
        <include refid="table_columns"/>
        from bis_insp_obj
    </select>

    <select id="findList" resultMap="bisInspObjResultMap">
        select
        <include refid="table_columns"/>
        from bis_insp_obj
        <include refid="page_where"/>
    </select>

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


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

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

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

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

    <update id="update" parameterType="cn.com.goldenwater.dcproj.model.BisInspObj">
        update bis_insp_obj
        <trim prefix="set" suffixOverrides=",">
            <if test="objType != null and objType != ''">OBJ_TYPE = #{objType},</if>
            <if test="inspGroupId != null and inspGroupId != ''">INSP_GROUP_ID = #{inspGroupId},</if>
            <if test="guid != null and guid != ''">GUID = #{guid},</if>
            <if test="plnaId != null and plnaId != ''">PLNA_ID = #{plnaId},</if>
        </trim>
        <where>OBJ_ID = #{objId}</where>
    </update>

    <update id="updateBy" parameterType="cn.com.goldenwater.dcproj.model.BisInspObj">
        update bis_insp_obj
        <trim prefix="set" suffixOverrides=",">
            <if test="objType != null and objType != ''">OBJ_TYPE = #{objType},</if>
            <if test="inspGroupId != null and inspGroupId != ''">INSP_GROUP_ID = #{inspGroupId},</if>
            <if test="guid != null and guid != ''">GUID = #{guid},</if>
            <if test="plnaId != null and plnaId != ''">PLNA_ID = #{plnaId},</if>
        </trim>
        <include refid="page_where"/>
    </update>


    <!-- 其他自定义SQL -->
    <delete id="deleteInspObj">
        DELETE FROM BIS_INSP_OBJ WHERE OBJ_ID = #{objId} AND OBJ_TYPE = #{objType} AND INSP_GROUP_ID = #{inspGroupId}
    </delete>


    <select id="findListByGroupIdAndPlanId" resultMap="bisInspObjResultMap">
        select t1.obj_id,t1.obj_type,
               CASE WHEN t1.obj_type='1' THEN t4.rsvr_nm
                    WHEN t1.OBJ_TYPE='2' THEN t5.regs_nm
                    WHEN t1.OBJ_TYPE='3' THEN t2.obj_nm
                    WHEN t1.OBJ_TYPE='4' THEN t3.group_leader
                   END AS OBJ_NAME
        from BIS_INSP_OBJ t1
                 left join BIS_INSP_WTDST_TRACK t2 on t1.OBJ_ID= t2.obj_id and t1.obj_type=3
                 left join BIS_INSP_KEY_REGISTER t3 on t1.OBJ_ID= t3.obj_id and t1.obj_type=4
                 left join BIS_INSP_RSVR_RGSTR t4 on t1.OBJ_ID= t4.obj_id and t1.obj_type=1
                 left join BIS_INSP_VILL_RGSTR t5 on t1.OBJ_ID= t5.obj_id and t1.obj_type=2
        where t1.insp_group_id= #{inspGroupId} and t1.plna_id= #{plnaId}
    </select>

    <select id="getPersByOrg" resultType="String">
        SELECT GUID FROM BIS_INSP_ALL_RLATION_PERS WHERE ORG_ID = #{orgId}
    </select>

    <delete id="deleteAll" parameterType="String">
        DELETE FROM BIS_INSP_ALL_OBJ WHERE ID LIKE '${id}%'
    </delete>

    <!--根据节点获取obj_id-->
    <select id="getObjIdList" resultType="cn.com.goldenwater.dcproj.dto.VillRgstrEngIdDto">
        SELECT OBJ_ID FROM BIS_INSP_ALL_OBJ WHERE ID LIKE '${id}%'
    </select>

    <!--根据obj_id删除问题表-->
    <delete id="deleteAllPblm">
        DELETE FROM BIS_INSP_PBLM WHERE OBJ_ID = #{s}
    </delete>

    <select id="getBisInspSelAreaList" resultType="cn.com.goldenwater.dcproj.model.BisInspSelArea">
        SELECT * FROM BIS_INSP_SEL_AREA WHERE ID = #{inspGroupId}
    </select>

    <sql id="choseSql">
        <choose>
            <when test="province !=null and province !=''">
                and B.AD_CODE LIKE CONCAT(#{province}, '%')
            </when>
            <otherwise>
                and B.AD_CODE is null
            </otherwise>
        </choose>
    </sql>

    <select id="getKeySwhsObjByPersId" resultType="cn.com.goldenwater.dcproj.dto.BisInspObjDto"
            parameterType="cn.com.goldenwater.dcproj.param.PersObjParam">
        SELECT A.ID AS CODE,A.SWHS_CODE AS OBJCODE,A.SWHS_NAME AS NM,
        A.WAIN_WASO_TYPE AS TYPE,
        A.SWHS_LONG AS CENTERX,
        A.SWHS_LAT AS CENTERY,
        A.SWHS_LONG_GD AS LGTD,
        A.SWHS_LAT_GD AS LTTD,
        A.SWHS_LOC AS LOCATION,
        A.NOTE,
        A.AD_CODE,
        B.OBJ_ID,
        R.STATE,
        R.ID AS RGSTRID
        FROM ATT_SWHS_BASE A LEFT JOIN BIS_INSP_ALL_OBJ B ON A.ID = B.CODE
        LEFT JOIN BIS_INSP_SWHS_RGSTR R ON B.OBJ_ID = R.OBJ_ID
        WHERE 1 = 1 AND B.PTYPE = #{objType}
        <include refid="choseSql"/>
        AND
        REGEXP_LIKE(
        B.Id,concat('^(',
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID = #{persGuid}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT group_concat(id separator '|')  as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID = #{persGuid}
        <include refid="orgIdSql"/>
        GROUP BY PERSID
        )
        else (select 'non' from dual) end)
        ,')'))
        <if test="adCode != null and adCode != ''">and A.AD_CODE LIKE '${adCode}%'</if>
        <if test="maxLgtd != null and maxLgtd != '' and minLgtd != null and minLgtd != ''">and A.SWHS_LONG BETWEEN
            #{minLgtd} AND #{maxLgtd}
        </if>
        <if test="maxLttd != null and maxLttd != '' and minLttd != null and minLttd != ''">and A.SWHS_LAT BETWEEN
            #{minLttd} AND #{maxLttd}
        </if>
        <if test="maxcenterXGd != null and maxcenterXGd != '' and mincenterXGd != null and mincenterXGd != ''">and
            A.SWHS_LONG_GD BETWEEN #{mincenterXGd} AND #{maxcenterXGd}
        </if>
        <if test="maxcenterYGd != null and maxcenterYGd != '' and mincenterYGd != null and mincenterYGd != ''">and
            A.SWHS_LAT_GD BETWEEN #{mincenterYGd} AND #{maxcenterYGd}
        </if>
    </select>

    <select id="getWiuObjByPersId" resultType="cn.com.goldenwater.dcproj.dto.BisInspObjDto"
            parameterType="cn.com.goldenwater.dcproj.param.PersObjParam">
        SELECT A.ID AS CODE,A.WIU_CODE AS OBJCODE,A.WIU_NAME AS NM,A.WINT_CODE as wintCode,
        A.WIU_LONG AS CENTERX,
        A.WIU_LAT AS CENTERY,
        A.GD_X AS LGTD,
        A.GD_Y AS LTTD,
        R.WIU_LOC AS LOCATION,
        R.AD_CODE,
        B.OBJ_ID,
        R.STATE,
        R.ID AS RGSTRID
        FROM ATT_WIU_BASE A LEFT JOIN BIS_INSP_ALL_OBJ B ON A.ID = B.CODE
        LEFT JOIN BIS_INSP_WIU_RGSTR R ON B.OBJ_ID = R.OBJ_ID
        WHERE 1 = 1 AND B.PTYPE = #{objType}
        <include refid="choseSql"/>
        AND
        REGEXP_LIKE(
        B.Id,concat('^(',
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID = #{persGuid}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT group_concat(id separator '|') as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID = #{persGuid}
        <include refid="orgIdSql"/>
        GROUP BY PERSID
        )
        else (select 'non' from dual) end)
        ,')'))
        <if test="adCode != null and adCode != ''">and R.AD_CODE LIKE '${adCode}%'</if>
        <if test="maxLgtd != null and maxLgtd != '' and minLgtd != null and minLgtd != ''">and A.WIU_LONG BETWEEN
            #{minLgtd} AND #{maxLgtd}
        </if>
        <if test="maxLttd != null and maxLttd != '' and minLttd != null and minLttd != ''">and A.WIU_LAT BETWEEN
            #{minLttd} AND #{maxLttd}
        </if>
        <if test="maxcenterXGd != null and maxcenterXGd != '' and mincenterXGd != null and mincenterXGd != ''">and
            A.GD_X BETWEEN #{mincenterXGd} AND #{maxcenterXGd}
        </if>
        <if test="maxcenterYGd != null and maxcenterYGd != '' and mincenterYGd != null and mincenterYGd != ''">and
            A.GD_Y BETWEEN #{mincenterYGd} AND #{maxcenterYGd}
        </if>
    </select>
    <select id="getWintObjByPersId" resultType="cn.com.goldenwater.dcproj.dto.BisInspObjDto"
            parameterType="cn.com.goldenwater.dcproj.param.PersObjParam">
        SELECT A.ID AS CODE,A.WINT_CODE AS OBJCODE,A.WINT_NAME AS NM,
        A.WAIN_LONG AS CENTERX,
        A.WAIN_LAT AS CENTERY,
        A.GD_X AS LGTD,
        A.GD_Y AS LTTD,
        A.WINT_POS AS LOCATION,
        A.AD_CODE,
        A.WAIN_TYPE as type
        FROM ATT_WINT_BASE_B A
        WHERE 1 = 1
        <if test="rsName != null and rsName != ''">and A.WINT_NAME like '%${rsName}%'</if>
        <if test="adCode != null and adCode != ''">and A.AD_CODE LIKE '${adCode}%'</if>
        <if test="maxLgtd != null and maxLgtd != '' and minLgtd != null and minLgtd != ''">and A.WAIN_LONG BETWEEN
            #{minLgtd} AND #{maxLgtd}
        </if>
        <if test="maxLttd != null and maxLttd != '' and minLttd != null and minLttd != ''">and A.WAIN_LAT BETWEEN
            #{minLttd} AND #{maxLttd}
        </if>
        <if test="maxcenterXGd != null and maxcenterXGd != '' and mincenterXGd != null and mincenterXGd != ''">and
            A.GD_X BETWEEN #{mincenterXGd} AND #{maxcenterXGd}
        </if>
        <if test="maxcenterYGd != null and maxcenterYGd != '' and mincenterYGd != null and mincenterYGd != ''">and
            A.GD_Y BETWEEN #{mincenterYGd} AND #{maxcenterYGd}
        </if>
    </select>
    <select id="getJSWAObjByPersId" resultType="cn.com.goldenwater.dcproj.dto.BisInspObjDto"
            parameterType="cn.com.goldenwater.dcproj.param.PersObjParam">
        SELECT A.id as code,A.name AS NM,
        A.CENTER_X AS CENTERX,
        A.CENTER_Y AS CENTERY,
        A.GD_X AS LGTD,
        A.GD_Y AS LTTD,
        R.LOCATION ,
        R.AD_CODE,
        B.OBJ_ID,
        R.STATE,
        R.ID AS RGSTRID
        FROM ATT_JSKEJIAN_WATER_BASE A LEFT JOIN BIS_INSP_ALL_OBJ B ON A.id = B.CODE
        LEFT JOIN ATT_JSKEJIAN_WATER_RGSTR R ON B.OBJ_ID = R.OBJ_ID
        WHERE 1 = 1 AND B.PTYPE = #{objType}
        <include refid="choseSql"/>
        AND
        REGEXP_LIKE(
        B.Id,concat('^(',
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID = #{persGuid}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT group_concat(id separator '|')  as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID = #{persGuid}
        <include refid="orgIdSql"/>
        GROUP BY PERSID
        )
        else (select 'non' from dual) end)
        ,')'))
        <if test="adCode != null and adCode != ''">and R.AD_CODE LIKE '${adCode}%'</if>
        <if test="maxLgtd != null and maxLgtd != '' and minLgtd != null and minLgtd != ''">and A.CENTER_X BETWEEN
            #{minLgtd} AND #{maxLgtd}
        </if>
        <if test="maxLttd != null and maxLttd != '' and minLttd != null and minLttd != ''">and A.CENTER_Y BETWEEN
            #{minLttd} AND #{maxLttd}
        </if>
        <if test="maxcenterXGd != null and maxcenterXGd != '' and mincenterXGd != null and mincenterXGd != ''">and
            A.GD_X BETWEEN #{mincenterXGd} AND #{maxcenterXGd}
        </if>
        <if test="maxcenterYGd != null and maxcenterYGd != '' and mincenterYGd != null and mincenterYGd != ''">and
            A.GD_Y BETWEEN #{mincenterYGd} AND #{maxcenterYGd}
        </if>
    </select>

    <select id="getJSFLObjByPersId" resultType="cn.com.goldenwater.dcproj.dto.BisInspObjDto"
            parameterType="cn.com.goldenwater.dcproj.param.PersObjParam">
        SELECT A.id as code,A.name AS NM,
        A.CENTER_X AS CENTERX,
        A.CENTER_Y AS CENTERY,
        A.GD_X AS LGTD,
        A.GD_Y AS LTTD,
        R.LOCATION ,
        R.AD_CODE,
        B.OBJ_ID,
        R.STATE,
        R.ID AS RGSTRID
        FROM ATT_JSKEJIAN_FLKDIS_BASE A LEFT JOIN BIS_INSP_ALL_OBJ B ON A.id = B.CODE
        LEFT JOIN ATT_JSKEJIAN_FLKDIS_RGSTR R ON B.OBJ_ID = R.OBJ_ID
        WHERE 1 = 1 AND B.PTYPE = #{objType}
        <include refid="choseSql"/>
        AND
        REGEXP_LIKE(
        B.Id,concat('^(',
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID = #{persGuid}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT group_concat(id separator '|')  as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID = #{persGuid}
        <include refid="orgIdSql"/>
        GROUP BY PERSID
        )
        else (select 'non' from dual) end)
        ,')'))
        <if test="adCode != null and adCode != ''">and R.AD_CODE LIKE '${adCode}%'</if>
        <if test="maxLgtd != null and maxLgtd != '' and minLgtd != null and minLgtd != ''">and A.CENTER_X BETWEEN
            #{minLgtd} AND #{maxLgtd}
        </if>
        <if test="maxLttd != null and maxLttd != '' and minLttd != null and minLttd != ''">and A.CENTER_Y BETWEEN
            #{minLttd} AND #{maxLttd}
        </if>
        <if test="maxcenterXGd != null and maxcenterXGd != '' and mincenterXGd != null and mincenterXGd != ''">and
            A.GD_X BETWEEN #{mincenterXGd} AND #{maxcenterXGd}
        </if>
        <if test="maxcenterYGd != null and maxcenterYGd != '' and mincenterYGd != null and mincenterYGd != ''">and
            A.GD_Y BETWEEN #{mincenterYGd} AND #{maxcenterYGd}
        </if>
    </select>

    <select id="getPrdObjByPersId" resultType="cn.com.goldenwater.dcproj.dto.BisInspObjDto"
            parameterType="cn.com.goldenwater.dcproj.param.PersObjParam">
        SELECT A.id as code,A.code AS OBJCODE,A.name AS NM,
        A.CENTER_X AS CENTERX,
        A.CENTER_Y AS CENTERY,
        A.GD_X AS LGTD,
        A.GD_Y AS LTTD,
        R.LOCATION ,
        R.AD_CODE,
        B.OBJ_ID,
        R.STATE,
        R.ID AS RGSTRID
        FROM ATT_PRD_CSTR_PROJ_BASE A LEFT JOIN BIS_INSP_ALL_OBJ B ON A.id = B.CODE
        LEFT JOIN BIS_INSP_OPRD_CSTR_PROJ_RGSTR R ON B.OBJ_ID = R.OBJ_ID
        WHERE 1 = 1 AND B.PTYPE = #{objType}
        <include refid="choseSql"/>
        AND
        REGEXP_LIKE(
        B.Id,concat('^(',
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID = #{persGuid}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT group_concat(id separator '|') as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID = #{persGuid}
        <include refid="orgIdSql"/>
        GROUP BY PERSID
        )
        else (select 'non' from dual) end)
        ')'))
        <if test="adCode != null and adCode != ''">and R.AD_CODE LIKE '${adCode}%'</if>
        <if test="maxLgtd != null and maxLgtd != '' and minLgtd != null and minLgtd != ''">and A.CENTER_X BETWEEN
            #{minLgtd} AND #{maxLgtd}
        </if>
        <if test="maxLttd != null and maxLttd != '' and minLttd != null and minLttd != ''">and A.CENTER_Y BETWEEN
            #{minLttd} AND #{maxLttd}
        </if>
        <if test="maxcenterXGd != null and maxcenterXGd != '' and mincenterXGd != null and mincenterXGd != ''">and
            A.GD_X BETWEEN #{mincenterXGd} AND #{maxcenterXGd}
        </if>
        <if test="maxcenterYGd != null and maxcenterYGd != '' and mincenterYGd != null and mincenterYGd != ''">and
            A.GD_Y BETWEEN #{mincenterYGd} AND #{maxcenterYGd}
        </if>
    </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="getSdObjByPersId" resultType="cn.com.goldenwater.dcproj.dto.BisInspObjDto"
            parameterType="cn.com.goldenwater.dcproj.param.PersObjParam">
        SELECT A.SD_CODE AS CODE,A.SD_CODE AS OBJCODE,A.SD_NAME AS NM,
        A.CENTER_X AS CENTERX,
        A.CENTER_Y AS CENTERY,
        A.GD_X AS LGTD,
        A.GD_Y AS LTTD,
        R.SD_LOC AS LOCATION,
        R.AD_CODE,
        B.OBJ_ID,
        R.STATE,
        R.ID AS RGSTRID
        FROM ATT_SD_BASE A LEFT JOIN BIS_INSP_ALL_OBJ B ON A.SD_CODE = B.CODE
        LEFT JOIN BIS_INSP_SD_RGSTR R ON B.OBJ_ID = R.OBJ_ID
        WHERE 1 = 1 AND B.PTYPE = #{objType}
        <include refid="choseSql"/>
        AND
        REGEXP_LIKE(
        B.Id,concat('^(',
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID = #{persGuid}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT group_concat(id separator '|') as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID = #{persGuid}
        <include refid="orgIdSql"/>
        GROUP BY PERSID
        )
        else (select 'non' from dual) end)
        ,')'))
        <if test="adCode != null and adCode != ''">and R.AD_CODE LIKE '${adCode}%'</if>
        <if test="maxLgtd != null and maxLgtd != '' and minLgtd != null and minLgtd != ''">and A.CENTER_X BETWEEN
            #{minLgtd} AND #{maxLgtd}
        </if>
        <if test="maxLttd != null and maxLttd != '' and minLttd != null and minLttd != ''">and A.CENTER_Y BETWEEN
            #{minLttd} AND #{maxLttd}
        </if>
        <if test="maxcenterXGd != null and maxcenterXGd != '' and mincenterXGd != null and mincenterXGd != ''">and
            A.GD_X BETWEEN #{mincenterXGd} AND #{maxcenterXGd}
        </if>
        <if test="maxcenterYGd != null and maxcenterYGd != '' and mincenterYGd != null and mincenterYGd != ''">and
            A.GD_Y BETWEEN #{mincenterYGd} AND #{maxcenterYGd}
        </if>
    </select>

    <select id="getSvwtObjByPersId" resultType="cn.com.goldenwater.dcproj.dto.BisInspObjDto"
            parameterType="cn.com.goldenwater.dcproj.param.PersObjParam">
        SELECT A.AD_CODE AS CODE,A.AD_CODE AS OBJCODE,A.AD_Name AS NM,
        case when r.pc_x is null then A.LGTDPC else r.pc_x end AS CENTERX,
        case when r.pc_y is null then a.lttdpc else r.pc_y end AS CENTERY,
        case when r.gd_x is null then A.LGTD else r.gd_x end AS LGTD,
        case when r.gd_y is null then a.LtTD else r.gd_y end as LTTD,
        case when R.loc is null then a.AD_FULL_NAME else r.loc end AS LOCATION,
        R.AD_CODE,
        B.OBJ_ID,
        R.STATE,
        R.ID AS RGSTRID
        FROM ATT_AD_X_BASE A LEFT JOIN BIS_INSP_ALL_OBJ B ON A.AD_CODe = B.CODE
        LEFT JOIN BIS_INSP_SVWT_AREA_RGSTR R ON B.OBJ_ID = R.OBJ_ID
        WHERE 1 = 1 AND B.PTYPE = #{objType}
        <include refid="choseSql"/>
        AND
        REGEXP_LIKE(
        B.Id,concat('^(',
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID = #{persGuid}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT group_concat(id separator '|') as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID = #{persGuid}
        <include refid="orgIdSql"/>
        GROUP BY PERSID
        )
        else (select 'non' from dual) end)
        ,')'))
        <if test="adCode != null and adCode != ''">and R.AD_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>
        <if test="state != null and state != ''">
            and r.state = #{state}
        </if>
    </select>
    <select id="getWuntObjByPersId" resultType="cn.com.goldenwater.dcproj.dto.BisInspObjDto"
            parameterType="cn.com.goldenwater.dcproj.param.PersObjParam">
        SELECT A.id AS CODE,A.UT_CODE AS OBJCODE,A.UT_NAME AS NM,
        case when r.pc_x is null then A.pc_x else r.pc_x end AS CENTERX,
        case when r.pc_y is null then a.pc_y else r.pc_y end AS CENTERY,
        case when r.gd_x is null then A.gd_x else r.gd_x end AS LGTD,
        case when r.gd_y is null then a.gd_y else r.gd_y end as LTTD,
        case when R.UT_ADDR is null then a.UT_ADDR else r.UT_ADDR end AS LOCATION,
        R.AD_CODE,
        B.OBJ_ID,
        R.STATE,
        R.ID AS RGSTRID,
        R.UT_PPT as type
        FROM ATT_WUNT_BASE A LEFT JOIN BIS_INSP_ALL_OBJ B ON A.ID = B.CODE
        LEFT JOIN BIS_INSP_SVWT_WUNT_RGSTR R ON B.OBJ_ID = R.OBJ_ID
        WHERE 1 = 1 AND B.PTYPE = #{objType}
        <include refid="choseSql"/>
        AND
        REGEXP_LIKE(
        B.Id,concat('^(',
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID = #{persGuid}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT group_concat(id separator '|') as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID = #{persGuid}
        <include refid="orgIdSql"/>
        GROUP BY PERSID
        )
        else (select 'non' from dual) end)
        ,')'))
        <if test="adCode != null and adCode != ''">and R.AD_CODE LIKE '${adCode}%'</if>
        <if test="rsName != null and rsName != ''">and a.UT_NAME like '%${rsName}%'</if>
        <if test="maxLgtd != null and maxLgtd != '' and minLgtd != null and minLgtd != ''">and A.pc_x BETWEEN
            #{minLgtd} AND #{maxLgtd}
        </if>
        <if test="maxLttd != null and maxLttd != '' and minLttd != null and minLttd != ''">and A.pc_y BETWEEN
            #{minLttd} AND #{maxLttd}
        </if>
        <if test="maxcenterXGd != null and maxcenterXGd != '' and mincenterXGd != null and mincenterXGd != ''">and
            A.gd_x BETWEEN #{mincenterXGd} AND #{maxcenterXGd}
        </if>
        <if test="maxcenterYGd != null and maxcenterYGd != '' and mincenterYGd != null and mincenterYGd != ''">and
            A.gd_y BETWEEN #{mincenterYGd} AND #{maxcenterYGd}
        </if>
        <if test="state != null and state != ''">
            and r.state = #{state}
        </if>
    </select>


    <select id="getIrrObjByPersId" resultType="cn.com.goldenwater.dcproj.dto.BisInspObjDto"
            parameterType="cn.com.goldenwater.dcproj.param.PersObjParam">
        SELECT A.id as code,A.Irr_Name AS NM,
        A.CENTER_X AS CENTERX,
        A.CENTER_Y AS CENTERY,
        A.GD_X AS LGTD,
        A.GD_Y AS LTTD,
        R.Ad_Full_Name as LOCATION ,
        R.AD_CODE,
        B.OBJ_ID,
        R.STATE,
        R.ID AS RGSTRID
        FROM att_irr_base A LEFT JOIN BIS_INSP_ALL_OBJ B ON A.id = B.CODE
        LEFT JOIN bis_insp_irr_rgstr R ON B.OBJ_ID = R.OBJ_ID
        WHERE 1 = 1 and B.PTYPE = #{objType}
        <include refid="choseSql"/>
        AND
        REGEXP_LIKE(
        B.Id,concat('^(',
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID = #{persGuid}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT group_concat(id separator '|') as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID = #{persGuid}
        <include refid="orgIdSql"/>
        GROUP BY PERSID
        )
        else (select 'non' from dual) end)
        ,')'))
        <if test="adCode != null and adCode != ''">and R.AD_CODE LIKE '${adCode}%'</if>
        <if test="maxLgtd != null and maxLgtd != '' and minLgtd != null and minLgtd != ''">and A.CENTER_X BETWEEN
            #{minLgtd} AND #{maxLgtd}
        </if>
        <if test="maxLttd != null and maxLttd != '' and minLttd != null and minLttd != ''">and A.CENTER_Y BETWEEN
            #{minLttd} AND #{maxLttd}
        </if>
        <if test="maxcenterXGd != null and maxcenterXGd != '' and mincenterXGd != null and mincenterXGd != ''">and
            A.GD_X BETWEEN #{mincenterXGd} AND #{maxcenterXGd}
        </if>
        <if test="maxcenterYGd != null and maxcenterYGd != '' and mincenterYGd != null and mincenterYGd != ''">and
            A.GD_Y BETWEEN #{mincenterYGd} AND #{maxcenterYGd}
        </if>
    </select>

    <select id="getSapObjByPersId" resultType="cn.com.goldenwater.dcproj.dto.AttWagaSapBaseDto"
            parameterType="cn.com.goldenwater.dcproj.param.PersObjParam">
        SELECT
        A.id as code,
        A.Sap_Name AS NM,
        A.ID,
        A.SAP_NAME,
        A.BAS_CODE,
        A.DISK_NAME,
        A.AD_CODE,
        A.AD_NAME,
        A.START_LOC,
        A.END_LOC,
        A.CENTER_X,
        A.CENTER_Y,
        A.GD_X,
        A.GD_Y,
        A.RIVER_BANK,
        A.LAKE,
        A.COAST,
        A.DIKE_GRAD,
        A.SAP_LEN,
        A.SAP_TYPE,
        A.CHK_STATE,
        A.MAMPU,
        A.MAMPU_ATTN,
        A.MAMPU_ATTN_TEL,
        A.COMP_UNIT_ATTN,
        A.COMP_UNIT_ATTN_TEL,
        A.GVMT_FLD_WHO,
        A.GVMT_FLD_WHO_TEL,
        A.GD_X AS LGTD,
        A.GD_Y AS LTTD,
        R.START_LOC as location,
        B.OBJ_ID,
        R.STATE,
        R.ID AS RGSTR_ID
        FROM att_waga_sap_base A
        LEFT JOIN BIS_INSP_ALL_OBJ B ON A.id = B.CODE
        LEFT JOIN bis_insp_waga_sap_rgstr R ON B.OBJ_ID = R.OBJ_ID
        WHERE B.PTYPE = #{objType}
        <include refid="choseSql"/>
        AND REGEXP_LIKE(
        B.Id,concat('^(',
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID = #{persGuid}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT group_concat(id separator '|') as id FROM BIS_INSP_ALL_RLATION WHERE PERSID = #{persGuid}
        <include refid="orgIdSql"/>
        GROUP BY PERSID
        )
        else (select 'non' from dual) end)
        ,')'))
        <if test="adCode != null and adCode != ''">and R.AD_CODE LIKE '${adCode}%'</if>
        <if test="maxLgtd != null and maxLgtd != '' and minLgtd != null and minLgtd != ''">
            and A.CENTER_X BETWEEN #{minLgtd} AND #{maxLgtd}
        </if>
        <if test="maxLttd != null and maxLttd != '' and minLttd != null and minLttd != ''">
            and A.CENTER_Y BETWEEN #{minLttd} AND #{maxLttd}
        </if>
        <if test="maxcenterXGd != null and maxcenterXGd != '' and mincenterXGd != null and mincenterXGd != ''">
            and A.GD_X BETWEEN #{mincenterXGd} AND #{maxcenterXGd}
        </if>
        <if test="maxcenterYGd != null and maxcenterYGd != '' and mincenterYGd != null and mincenterYGd != ''">
            and A.GD_Y BETWEEN #{mincenterYGd} AND #{maxcenterYGd}
        </if>
    </select>
    <select id="getSapBaseList" resultType="cn.com.goldenwater.dcproj.dto.BisInspObjDto"
            parameterType="cn.com.goldenwater.dcproj.param.PersObjParam">
        SELECT B.ID AS CODE, B.ID AS OBJCODE, B.SAP_NAME AS NM,
        B.CENTER_X, B.CENTER_Y, B.GD_X, B.GD_Y,
        B.GD_X AS LGTD, B.GD_Y AS LTTD,
        B.ad_CODE,
        B.SAP_TYPE engScal,
        B.START_LOC location,
        IFNULL(A.state, 0) as state
        FROM ATT_WAGA_SAP_BASE B
        LEFT JOIN (
        SELECT a.ID, '1' as state
        FROM ATT_WAGA_SAP_BASE a
        WHERE Exists (select code FROM BIS_INSP_ALL_OBJ o where o.code = a.id and o.ptype = '29')
        ) A ON A.ID = B.ID
        <where>
            <include refid="choseSql"/>
            <if test="rsName != null and rsName != ''">
                AND B.SAP_NAME like concat('%',concat(#{rsName},'%'))
            </if>
            <if test="adCode != null and adCode != ''">and B.AD_CODE LIKE '${adCode}%'</if>
            <if test="maxLgtd != null and maxLgtd != '' and minLgtd != null and minLgtd != ''">and B.CENTER_X BETWEEN
                #{minLgtd} AND #{maxLgtd}
            </if>
            <if test="maxLttd != null and maxLttd != '' and minLttd != null and minLttd != ''">and B.CENTER_Y BETWEEN
                #{minLttd} AND #{maxLttd}
            </if>
            <if test="maxcenterXGd != null and maxcenterXGd != '' and mincenterXGd != null and mincenterXGd != ''">and
                B.GD_X BETWEEN #{mincenterXGd} AND #{maxcenterXGd}
            </if>
            <if test="maxcenterYGd != null and maxcenterYGd != '' and mincenterYGd != null and mincenterYGd != ''">and
                B.GD_Y BETWEEN #{mincenterYGd} AND #{maxcenterYGd}
            </if>
            <if test="engScal != null and engScal != ''">
                AND B.SAP_TYPE = #{engScal}
            </if>
        </where>
    </select>

</mapper>