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

    <resultMap type="cn.com.goldenwater.dcproj.model.BisInspOtherPblm" id="bisInspOtherPblmResultMap">
        <result property="name" column="NAME"/>
        <result property="type" column="TYPE"/>
        <result property="admOrg" column="ADM_ORG"/>
        <result property="basName" column="BAS_NAME"/>
        <result property="province" column="PROVINCE"/>
        <result property="city" column="CITY"/>
        <result property="country" column="COUNTRY"/>
        <result property="groupName" column="GROUP_NAME"/>
        <result property="persId" column="PERS_ID"/>
        <result property="persName" column="PERS_NAME"/>
        <result property="groupId" column="GROUP_ID"/>
        <result property="inTm" column="IN_TM"/>
        <result property="upTm" column="UP_TM"/>
        <result property="id" column="ID"/>
        <result property="rgstrId" column="RGSTR_ID"/>
        <result property="aUnit" column="A_UNIT"/>
        <result property="pblmNum" column="PBLM_NUM"/>
        <result property="pblmIsTrue" column="PBLM_IS_TRUE"/>
        <result property="rectConc" column="RECT_CONC"/>
        <result property="rectMeas" column="RECT_MEAS"/>
        <result property="fdTm" column="FD_TM"/>
        <result property="rectLead" column="RECT_LEAD"/>
        <result property="rectMemb" column="RECT_MEMB"/>
        <result property="rectTm" column="RECT_TM"/>
        <result property="note" column="NOTE"/>
        <result property="dataStat" column="DATA_STAT"/>
    </resultMap>

    <sql id="table_columns">
        PERS_ID,
        GROUP_ID,
        IN_TM,
        UP_TM,
        ID,
        RGSTR_ID,
        A_UNIT,
        PBLM_NUM,
        PBLM_IS_TRUE,
        RECT_CONC,
        RECT_MEAS,
        FD_TM,
        RECT_LEAD,
        RECT_MEMB,
        RECT_TM,
        NOTE,
        DATA_STAT
    </sql>
    <sql id="entity_properties">
        #{persId},
        #{groupId},
        #{inTm},
        #{upTm},
        #{id},
        #{rgstrId},
        #{aUnit},
        #{pblmNum},
        #{pblmIsTrue},
        #{rectConc},
        #{rectMeas},
        #{fdTm},
        #{rectLead},
        #{rectMemb},
        #{rectTm},
        #{note},
        #{dataStat}
    </sql>

    <sql id="entity_properties_item">
        #{item.persId},
        #{item.groupId},
        #{item.inTm},
        #{item.upTm},
        #{item.id},
        #{item.rgstrId},
        #{item.aUnit},
        #{item.pblmNum},
        #{item.pblmIsTrue},
        #{item.rectConc},
        #{item.rectMeas},
        #{item.fdTm},
        #{item.rectLead},
        #{item.rectMemb},
        #{item.rectTm},
        #{item.note},
        #{item.dataStat}


    </sql>

    <!-- 使用like用法：columnName like concat('%',#columnName#,'%') -->
    <sql id="page_where">
        <trim prefix="where" prefixOverrides="and | or ">
            <if test="groupId != null and groupId != ''">and GROUP_ID = #{groupId}</if>
            <if test="inTm != null">and IN_TM = #{inTm}</if>
            <if test="upTm != null">and UP_TM = #{upTm}</if>
            <if test="id != null and id != ''">and ID = #{id}</if>
            <if test="rgstrId != null and rgstrId != ''">and RGSTR_ID = #{rgstrId}</if>
            <if test="aUnit != null and aUnit != ''">and A_UNIT = #{aUnit}</if>
            <if test="pblmNum != null and pblmNum != ''">and PBLM_NUM = #{pblmNum}</if>
            <if test="pblmIsTrue != null and pblmIsTrue != ''">and PBLM_IS_TRUE = #{pblmIsTrue}</if>
            <if test="rectConc != null and rectConc != ''">and RECT_CONC = #{rectConc}</if>
            <if test="rectMeas != null and rectMeas != ''">and RECT_MEAS = #{rectMeas}</if>
            <if test="fdTm != null">and FD_TM = #{fdTm}</if>
            <if test="rectLead != null and rectLead != ''">and RECT_LEAD = #{rectLead}</if>
            <if test="rectMemb != null and rectMemb != ''">and RECT_MEMB = #{rectMemb}</if>
            <if test="rectTm != null">and RECT_TM = #{rectTm}</if>
            <if test="note != null and note != ''">and NOTE = #{note}</if>
            <if test="dataStat != null and dataStat != ''">and DATA_STAT = #{dataStat}</if>
        </trim>
    </sql>


    <select id="get" resultMap="bisInspOtherPblmResultMap" parameterType="String">
        select
        <include refid="table_columns"/>
        from BIS_INSP_OTHER_PBLM where ID = #{id}
    </select>

    <select id="getBy" resultMap="bisInspOtherPblmResultMap">
        select
        <include refid="table_columns"/>
        from BIS_INSP_OTHER_PBLM
        <include refid="page_where"/>
    </select>

    <select id="findAll" resultMap="bisInspOtherPblmResultMap">
        select
        <include refid="table_columns"/>
        from BIS_INSP_OTHER_PBLM
    </select>

    <select id="findList" resultMap="bisInspOtherPblmResultMap">
        select
        <include refid="table_columns"/>
        from BIS_INSP_OTHER_PBLM
        <include refid="page_where"/>
    </select>

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


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


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

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

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

    <update id="update" parameterType="cn.com.goldenwater.dcproj.model.BisInspOtherPblm">
        update BIS_INSP_OTHER_PBLM
        <trim prefix="set" suffixOverrides=",">
            <if test="groupId != null and groupId != ''">GROUP_ID = #{groupId},</if>
            <if test="inTm != null">IN_TM = #{inTm},</if>
            <if test="upTm != null">UP_TM = #{upTm},</if>
            <if test="id != null and id != ''">ID = #{id},</if>
            <if test="rgstrId != null and rgstrId != ''">RGSTR_ID = #{rgstrId},</if>
            <if test="aUnit != null and aUnit != ''">A_UNIT = #{aUnit},</if>
            <if test="pblmNum != null and pblmNum != ''">PBLM_NUM = #{pblmNum},</if>
            <if test="pblmIsTrue != null and pblmIsTrue != ''">PBLM_IS_TRUE = #{pblmIsTrue},</if>
            <if test="rectConc != null and rectConc != ''">RECT_CONC = #{rectConc},</if>
            <if test="rectMeas != null and rectMeas != ''">RECT_MEAS = #{rectMeas},</if>
            <if test="fdTm != null">FD_TM = #{fdTm},</if>
            <if test="rectLead != null and rectLead != ''">RECT_LEAD = #{rectLead},</if>
            <if test="rectMemb != null and rectMemb != ''">RECT_MEMB = #{rectMemb},</if>
            <if test="rectTm != null">RECT_TM = #{rectTm},</if>
            <if test="note != null and note != ''">NOTE = #{note},</if>
            <if test="dataStat != null and dataStat != ''">DATA_STAT = #{dataStat},</if>
        </trim>
        <where>ID = #{id}</where>
    </update>

    <update id="updateBy" parameterType="cn.com.goldenwater.dcproj.model.BisInspOtherPblm">
        update BIS_INSP_OTHER_PBLM
        <trim prefix="set" suffixOverrides=",">
            <if test="groupId != null and groupId != ''">GROUP_ID = #{groupId},</if>
            <if test="inTm != null">IN_TM = #{inTm},</if>
            <if test="upTm != null">UP_TM = #{upTm},</if>
            <if test="id != null and id != ''">ID = #{id},</if>
            <if test="rgstrId != null and rgstrId != ''">RGSTR_ID = #{rgstrId},</if>
            <if test="aUnit != null and aUnit != ''">A_UNIT = #{aUnit},</if>
            <if test="pblmNum != null and pblmNum != ''">PBLM_NUM = #{pblmNum},</if>
            <if test="pblmIsTrue != null and pblmIsTrue != ''">PBLM_IS_TRUE = #{pblmIsTrue},</if>
            <if test="rectConc != null and rectConc != ''">RECT_CONC = #{rectConc},</if>
            <if test="rectMeas != null and rectMeas != ''">RECT_MEAS = #{rectMeas},</if>
            <if test="fdTm != null">FD_TM = #{fdTm},</if>
            <if test="rectLead != null and rectLead != ''">RECT_LEAD = #{rectLead},</if>
            <if test="rectMemb != null and rectMemb != ''">RECT_MEMB = #{rectMemb},</if>
            <if test="rectTm != null">RECT_TM = #{rectTm},</if>
            <if test="note != null and note != ''">NOTE = #{note},</if>
            <if test="dataStat != null and dataStat != ''">DATA_STAT = #{dataStat},</if>
        </trim>
        <include refid="page_where"/>
    </update>


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

    <insert id="insertList" parameterType="java.util.List" useGeneratedKeys="false">
        insert into BIS_INSP_OTHER_PBLM(
        <include refid="table_columns"/>
        )
        select t.* from (
        <foreach collection="list" item="item" index="index" separator="union all">
            select
            <include refid="entity_properties_item"/>
            from dual
        </foreach>
        ) t
    </insert>

    <sql id="choseSql">
        <choose>
            <when test="province !=null and province !=''">
                and ad_code like '${province}%'
            </when>
            <otherwise>
                and ad_code is null
            </otherwise>
        </choose>
    </sql>

    <select id="findPblmList" parameterType="cn.com.goldenwater.dcproj.param.FileParam"
            resultType="cn.com.goldenwater.dcproj.dto.BisInspOtherPblmDto">
        select x.id as groupObjId,x.name,x.ad_full_name,x.riverName,x.type,x.adm_org org,p.* from (
        select * from (
        select A.*,b.id as rgstrId,CASE WHEN B.STATE IS NULL THEN '0' ELSE B.STATE END AS fscState,(case when b.name is
        null then to_char(c.name) else b.name end) as name,b.type,b.IN_TM,b.UP_TM,
        c.id as baseId,b.adm_org,
        (case when b.location is null then c.location else b.location end) as location,ia.pnm groupName,ria.pnm
        riverName,ad.ad_full_name from BIS_INSP_ALL_OBJ A
        join BIS_INSP_Other_RGSTR B on A.Obj_Id = B.Obj_Id left join
        att_other_base c on a.code = c.id
        left join ATT_AD_X_BASE ad on b.ad_code = ad.ad_code LEFT JOIN BIS_INSP_ALL ia on A.ID = ia.id
        left join BIS_INSP_ALL ria on substr(A.ID,0,6) = ria.id
        where 1 = 1
        <if test="adName != null and adName != ''">and ad.AD_FULL_NAME LIKE '%${adName}%'</if>
        <if test="groupId != null and groupId != ''">and A.id = #{groupId}</if>
        <if test="sttm != null and sttm != '' and entm != null and entm != ''">
            AND B.IN_TM &gt;= TO_DATE(#{sttm},'YYYY-MM-DD') AND B.UP_TM &lt; TO_DATE(#{entm},'YYYY-MM-DD')+1
        </if>
        and
        REGEXP_LIKE(
        A.Id,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}   AND ID LIKE '010%'
        <include refid="choseSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|')  as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}  AND ID LIKE '010%'
        <include refid="choseSql"/> GROUP BY PERSID
        )
        else (select 'non' from dual) end)
        ||')')
        ) where 1 = 1
        <if test="state != null and state != ''">
            and fscState in (${state})
        </if>
        <if test="name != null and name != ''">
            and name like '%${name}%'
        </if>
        ) x left join BIS_INSP_Other_PBLM p on x.rgstrId = p.rgstr_id
    </select>

    <select id="findPageList" resultMap="bisInspOtherPblmResultMap">
        SELECT B.NAME,
        B.TYPE,
        B.ADM_ORG,
        H.BAS_NAME,
        G.AD_NAME AS PROVINCE,
        F.AD_NAME AS CITY,
        E.AD_NAME AS COUNTRY,
        D.ID AS GROUP_ID,
        D.PNM AS GROUP_NAME,
        J.PERS_NAME,
        A.PERS_ID,
        A.IN_TM,
        A.UP_TM,
        A.ID,
        A.RGSTR_ID,
        A.A_UNIT,
        A.PBLM_NUM,
        A.PBLM_IS_TRUE,
        A.RECT_CONC,
        A.RECT_MEAS,
        A.FD_TM,
        A.RECT_LEAD,
        A.RECT_MEMB,
        A.RECT_TM,
        A.NOTE,
        A.DATA_STAT
        FROM BIS_INSP_OTHER_PBLM A
        LEFT JOIN BIS_INSP_OTHER_RGSTR B
        ON A.RGSTR_ID = B.ID
        LEFT JOIN BIS_INSP_ALL_OBJ C
        ON B.OBJ_ID = C.OBJ_ID
        LEFT JOIN BIS_INSP_ALL D
        ON C.ID = D.ID
        LEFT JOIN ATT_AD_X_BASE E
        ON B.AD_CODE = E.AD_CODE
        AND E.AD_GRAD = '4'
        LEFT JOIN ATT_AD_X_BASE F
        ON CONCAT(SUBSTR(B.AD_CODE, 0, 4), '00000000') = F.AD_CODE
        AND F.AD_GRAD = '3'
        LEFT JOIN ATT_AD_X_BASE G
        ON CONCAT(SUBSTR(B.AD_CODE, 0, 2), '0000000000') = G.AD_CODE
        AND G.AD_GRAD = '2'
        LEFT JOIN ATT_BAS_BASE H
        ON B.ADM_ORG = H.BAS_CODE
        LEFT JOIN BIS_INSP_ALL_RLATION_PERS J
        ON A.PERS_ID = J.GUID
        <trim prefix="where" prefixOverrides="and | or ">
            1=1
            <if test="inTm != null">and A.IN_TM = #{inTm}</if>
            <if test="upTm != null">and A.UP_TM = #{upTm}</if>
            <if test="id != null and id != ''">and A.ID = #{id}</if>
            <if test="rgstrId != null and rgstrId != ''">and A.RGSTR_ID = #{rgstrId}</if>
            <if test="aUnit != null and aUnit != ''">and A.A_UNIT LIKE CONCAT('%',CONCAT(#{aUnit},'%')) </if>
            <if test="pblmNum != null and pblmNum != ''">and A.PBLM_NUM = #{pblmNum}</if>
            <if test="pblmIsTrue != null and pblmIsTrue != ''">and A.PBLM_IS_TRUE = #{pblmIsTrue}</if>
            <if test="rectConc != null and rectConc != ''">and A.RECT_CONC LIKE CONCAT('%',CONCAT(#{rectConc},'%'))  </if>
            <if test="rectMeas != null and rectMeas != ''">and A.RECT_MEAS LIKE CONCAT('%',CONCAT(#{rectMeas},'%'))  </if>
            <if test="fdTm != null">and A.FD_TM = #{fdTm}</if>
            <if test="rectLead != null and rectLead != ''">and A.RECT_LEAD LIKE CONCAT('%',CONCAT(#{rectLead},'%'))  </if>
            <if test="rectMemb != null and rectMemb != ''">and A.RECT_MEMB LIKE CONCAT('%',CONCAT(#{rectMemb},'%'))  </if>
            <if test="rectTm != null">and A.RECT_TM = #{rectTm}</if>
            <if test="note != null and note != ''">and A.NOTE = #{note}</if>
            <if test="dataStat != null and dataStat != ''">and A.DATA_STAT = #{dataStat}</if>
            <if test="groupId != null and groupId != ''">and D.ID = #{groupId}</if>
            <if test="orgIds != null and orgIds != ''">and D.ID IN
                <foreach item="item" index="index" collection="orgIds.split(',')" open="(" separator="," close=")">
                    '${item}'
                </foreach>
            </if>
            <if test="adCode != null and adCode != ''">and B.AD_CODE LIKE CONCAT(#{adCode},'%')</if>
            <if test="adName != null and adName != ''">and E.AD_FULL_NAME LIKE CONCAT('%',CONCAT(#{adName},'%'))</if>
            <if test="startTime != null">
                <if test='tmType =="1" or tmType =="" or tmType ==null'>
                    and A.FD_TM &gt;= #{startTime}
                </if>
                <if test='tmType =="2"'>
                    and A.RECT_TM &gt;= #{startTime}
                </if>
            </if>
            <if test="endTime != null">
                <if test='tmType =="1" or tmType =="" or tmType ==null'>
                    and A.FD_TM &lt;= #{endTime}
                </if>
                <if test='tmType =="2"'>
                    and A.RECT_TM &lt;= #{endTime}
                </if>
            </if>
            <if test="persId != null and persId != ''">and
                REGEXP_LIKE(
                D.Id,'^('||
                ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
                AND ID LIKE '010%'
                <include refid="choseSql"/>
                )>0 then
                (SELECT LISTAGG(id, '|')  as
                id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
                AND ID LIKE '010%'
                <include refid="choseSql"/> GROUP BY PERSID
                )
                else (select 'non' from dual) end)
                ||')')
            </if>
            <if test="persName != null and persName != ''">
                AND J.PERS_NAME LIKE CONCAT('%',CONCAT(#{persName},'%'))
            </if>
            <if test="name != null and name != ''">and B.NAME LIKE CONCAT('%',CONCAT(#{name},'%'))</if>
            <if test="groupName != null and groupName != ''">and D.PNM LIKE CONCAT('%',CONCAT(#{groupName},'%'))</if>
        </trim>
    </select>
</mapper>