fj-dc/.svn/pristine/42/42c3fb0dac0b904ea65f6970c19d3c57f42dfe8c.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.BisInspWagaSapRgstrDao">

    <resultMap type="cn.com.goldenwater.dcproj.model.BisInspWagaSapRgstr" id="bisInspWagaSapRgstrResultMap">
        <result property="id" column="ID"/>
        <result property="objId" column="OBJ_ID"/>
        <result property="sapId" column="SAP_ID"/>
        <result property="sapName" column="SAP_NAME"/>
        <result property="basCode" column="BAS_CODE"/>
        <result property="diskName" column="DISK_NAME"/>
        <result property="adCode" column="AD_CODE"/>
        <result property="adName" column="AD_NAME"/>
        <result property="startLoc" column="START_LOC"/>
        <result property="endLoc" column="END_LOC"/>
        <result property="centerX" column="CENTER_X"/>
        <result property="centerY" column="CENTER_Y"/>
        <result property="gdX" column="GD_X"/>
        <result property="gdY" column="GD_Y"/>
        <result property="riverBank" column="RIVER_BANK"/>
        <result property="lake" column="LAKE"/>
        <result property="coast" column="COAST"/>
        <result property="dikeGrad" column="DIKE_GRAD"/>
        <result property="sapLen" column="SAP_LEN"/>
        <result property="sapType" column="SAP_TYPE"/>
        <result property="chkState" column="CHK_STATE"/>
        <result property="mampu" column="MAMPU"/>
        <result property="mampuAttn" column="MAMPU_ATTN"/>
        <result property="mampuAttnTel" column="MAMPU_ATTN_TEL"/>
        <result property="compUnitAttn" column="COMP_UNIT_ATTN"/>
        <result property="compUnitAttnTel" column="COMP_UNIT_ATTN_TEL"/>
        <result property="gvmtFldWho" column="GVMT_FLD_WHO"/>
        <result property="gvmtFldWhoTel" column="GVMT_FLD_WHO_TEL"/>
        <result property="note" column="NOTE"/>
        <result property="state" column="STATE"/>
        <result property="mrsCiStat" column="MRS_CI_STAT"/>
        <result property="safeStat" column="SAFE_STAT"/>
        <result property="dmamStat" column="DMAM_STAT"/>
        <result property="proenStat" column="PROEN_STAT"/>
        <result property="baseStat" column="BASE_STAT"/>
        <result property="persId" column="PERS_ID"/>
        <result property="groupId" column="GROUP_ID"/>
        <result property="intm" column="INTM"/>
        <result property="uptm" column="UPTM"/>
    </resultMap>

    <sql id="table_columns">
    ID,
    OBJ_ID,
    SAP_ID,
    SAP_NAME,
    BAS_CODE,
    DISK_NAME,
    AD_CODE,
    AD_NAME,
    START_LOC,
    END_LOC,
    CENTER_X,
    CENTER_Y,
    GD_X,
    GD_Y,
    RIVER_BANK,
    LAKE,
    COAST,
    DIKE_GRAD,
    SAP_LEN,
    SAP_TYPE,
    CHK_STATE,
    MAMPU,
    MAMPU_ATTN,
    MAMPU_ATTN_TEL,
    COMP_UNIT_ATTN,
    COMP_UNIT_ATTN_TEL,
    GVMT_FLD_WHO,
    GVMT_FLD_WHO_TEL,
    NOTE,
    STATE,
    MRS_CI_STAT,
    SAFE_STAT,
    DMAM_STAT,
    PROEN_STAT,
        BASE_STAT,
    PERS_ID,
    GROUP_ID,
    INTM,
    UPTM
    </sql>
    <sql id="entity_properties">
    #{id},
    #{objId},
    #{sapId},
    #{sapName},
    #{basCode},
    #{diskName},
    #{adCode},
    #{adName},
    #{startLoc},
    #{endLoc},
    #{centerX},
    #{centerY},
    #{gdX},
    #{gdY},
    #{riverBank},
    #{lake},
    #{coast},
    #{dikeGrad},
    #{sapLen},
    #{sapType},
    #{chkState},
    #{mampu},
    #{mampuAttn},
    #{mampuAttnTel},
    #{compUnitAttn},
    #{compUnitAttnTel},
    #{gvmtFldWho},
    #{gvmtFldWhoTel},
    #{note},
    #{state},
    #{mrsCiStat},
    #{safeStat},
    #{dmamStat},
    #{proenStat},
       #{baseStat},
    #{persId},
    #{groupId},
    #{intm},
    #{uptm}
    </sql>

    <!-- 使用like用法：columnName like concat('%',#columnName#,'%') -->
    <sql id="page_where">
        <trim prefix="where" prefixOverrides="and | or ">
            <if test="objId != null and objId != ''">and OBJ_ID = #{objId}</if>
            <if test="sapId != null and sapId != ''">and SAP_ID = #{sapId}</if>
            <if test="sapName != null and sapName != ''">and SAP_NAME = #{sapName}</if>
            <if test="basCode != null and basCode != ''">and BAS_CODE = #{basCode}</if>
            <if test="diskName != null and diskName != ''">and DISK_NAME = #{diskName}</if>
            <if test="adCode != null and adCode != ''">and AD_CODE = #{adCode}</if>
            <if test="adName != null and adName != ''">and AD_NAME = #{adName}</if>
            <if test="startLoc != null and startLoc != ''">and START_LOC = #{startLoc}</if>
            <if test="endLoc != null and endLoc != ''">and END_LOC = #{endLoc}</if>
            <if test="centerX != null and centerX != ''">and CENTER_X = #{centerX}</if>
            <if test="centerY != null and centerY != ''">and CENTER_Y = #{centerY}</if>
            <if test="gdX != null and gdX != ''">and GD_X = #{gdX}</if>
            <if test="gdY != null and gdY != ''">and GD_Y = #{gdY}</if>
            <if test="riverBank != null and riverBank != ''">and RIVER_BANK = #{riverBank}</if>
            <if test="lake != null and lake != ''">and LAKE = #{lake}</if>
            <if test="coast != null and coast != ''">and COAST = #{coast}</if>
            <if test="dikeGrad != null and dikeGrad != ''">and DIKE_GRAD = #{dikeGrad}</if>
            <if test="sapLen != null and sapLen != ''">and SAP_LEN = #{sapLen}</if>
            <if test="sapType != null and sapType != ''">and SAP_TYPE = #{sapType}</if>
            <if test="chkState != null and chkState != ''">and CHK_STATE = #{chkState}</if>
            <if test="mampu != null and mampu != ''">and MAMPU = #{mampu}</if>
            <if test="mampuAttn != null and mampuAttn != ''">and MAMPU_ATTN = #{mampuAttn}</if>
            <if test="mampuAttnTel != null and mampuAttnTel != ''">and MAMPU_ATTN_TEL = #{mampuAttnTel}</if>
            <if test="compUnitAttn != null and compUnitAttn != ''">and COMP_UNIT_ATTN = #{compUnitAttn}</if>
            <if test="compUnitAttnTel != null and compUnitAttnTel != ''">and COMP_UNIT_ATTN_TEL = #{compUnitAttnTel}
            </if>
            <if test="gvmtFldWho != null and gvmtFldWho != ''">and GVMT_FLD_WHO = #{gvmtFldWho}</if>
            <if test="gvmtFldWhoTel != null and gvmtFldWhoTel != ''">and GVMT_FLD_WHO_TEL = #{gvmtFldWhoTel}</if>
            <if test="note != null and note != ''">and NOTE = #{note}</if>
            <if test="state != null and state != ''">and STATE = #{state}</if>
            <if test="mrsCiStat != null and mrsCiStat != ''">and MRS_CI_STAT = #{mrsCiStat}</if>
            <if test="safeStat != null and safeStat != ''">and SAFE_STAT = #{safeStat}</if>
            <if test="dmamStat != null and dmamStat != ''">and DMAM_STAT = #{dmamStat}</if>
            <if test="proenStat != null and proenStat != ''">and PROEN_STAT = #{proenStat}</if>
            <if test="baseStat != null and baseStat != ''">and BASE_STAT = #{baseStat}</if>

            <if test="persId != null and persId != ''">and PERS_ID = #{persId}</if>
            <if test="groupId != null and groupId != ''">and GROUP_ID = #{groupId}</if>
            <if test="intm != null">and INTM = #{intm}</if>
            <if test="uptm != null">and UPTM = #{uptm}</if>
        </trim>
    </sql>


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

    <select id="getBy" resultMap="bisInspWagaSapRgstrResultMap">
        select
        <include refid="table_columns"/>
        from BIS_INSP_WAGA_SAP_RGSTR
        <include refid="page_where"/>
    </select>

    <select id="findAll" resultMap="bisInspWagaSapRgstrResultMap">
        select
        <include refid="table_columns"/>
        from BIS_INSP_WAGA_SAP_RGSTR
    </select>

    <select id="findList" resultMap="bisInspWagaSapRgstrResultMap">
        select
        <include refid="table_columns"/>
        from BIS_INSP_WAGA_SAP_RGSTR
        <include refid="page_where"/>
    </select>

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

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

    <delete id="delete" parameterType="java.lang.String">
        update  BIS_INSP_WAGA_SAP_RGSTR set DATA_STAT='9' where ID = #{id}
    </delete>

    <delete id="deleteBy" parameterType="cn.com.goldenwater.dcproj.model.BisInspWagaSapRgstr">
        update BIS_INSP_WAGA_SAP_RGSTR set DATA_STAT='9'
        <include refid="page_where"/>
    </delete>

    <update id="deleteInFlag" parameterType="java.lang.String">
        update BIS_INSP_WAGA_SAP_RGSTR set DATA_STAT = '9' where ID = #{id}
    </update>

    <update id="update" parameterType="cn.com.goldenwater.dcproj.model.BisInspWagaSapRgstr">
        update BIS_INSP_WAGA_SAP_RGSTR
        <trim prefix="set" suffixOverrides=",">
            <if test="objId != null and objId != ''">OBJ_ID = #{objId},</if>
            <if test="sapId != null and sapId != ''">SAP_ID = #{sapId},</if>
            <if test="sapName != null and sapName != ''">SAP_NAME = #{sapName},</if>
            <if test="basCode != null and basCode != ''">BAS_CODE = #{basCode},</if>
            <if test="diskName != null and diskName != ''">DISK_NAME = #{diskName},</if>
            <if test="adCode != null and adCode != ''">AD_CODE = #{adCode},</if>
            <if test="adName != null and adName != ''">AD_NAME = #{adName},</if>
            <if test="startLoc != null and startLoc != ''">START_LOC = #{startLoc},</if>
            <if test="endLoc != null and endLoc != ''">END_LOC = #{endLoc},</if>
            <if test="centerX != null and centerX != ''">CENTER_X = #{centerX},</if>
            <if test="centerY != null and centerY != ''">CENTER_Y = #{centerY},</if>
            <if test="gdX != null and gdX != ''">GD_X = #{gdX},</if>
            <if test="gdY != null and gdY != ''">GD_Y = #{gdY},</if>
            <if test="riverBank != null and riverBank != ''">RIVER_BANK = #{riverBank},</if>
            <if test="lake != null and lake != ''">LAKE = #{lake},</if>
            <if test="coast != null and coast != ''">COAST = #{coast},</if>
            <if test="dikeGrad != null and dikeGrad != ''">DIKE_GRAD = #{dikeGrad},</if>
            <if test="sapLen != null and sapLen != ''">SAP_LEN = #{sapLen},</if>
            <if test="sapType != null and sapType != ''">SAP_TYPE = #{sapType},</if>
            <if test="chkState != null and chkState != ''">CHK_STATE = #{chkState},</if>
            <if test="mampu != null and mampu != ''">MAMPU = #{mampu},</if>
            <if test="mampuAttn != null and mampuAttn != ''">MAMPU_ATTN = #{mampuAttn},</if>
            <if test="mampuAttnTel != null and mampuAttnTel != ''">MAMPU_ATTN_TEL = #{mampuAttnTel},</if>
            <if test="compUnitAttn != null and compUnitAttn != ''">COMP_UNIT_ATTN = #{compUnitAttn},</if>
            <if test="compUnitAttnTel != null and compUnitAttnTel != ''">COMP_UNIT_ATTN_TEL = #{compUnitAttnTel},</if>
            <if test="gvmtFldWho != null and gvmtFldWho != ''">GVMT_FLD_WHO = #{gvmtFldWho},</if>
            <if test="gvmtFldWhoTel != null and gvmtFldWhoTel != ''">GVMT_FLD_WHO_TEL = #{gvmtFldWhoTel},</if>
            <if test="note != null and note != ''">NOTE = #{note},</if>
            <if test="state != null and state != ''">STATE = #{state},</if>
            <if test="mrsCiStat != null and mrsCiStat != ''">MRS_CI_STAT = #{mrsCiStat},</if>
            <if test="safeStat != null and safeStat != ''">SAFE_STAT = #{safeStat},</if>
            <if test="dmamStat != null and dmamStat != ''">DMAM_STAT = #{dmamStat},</if>
            <if test="proenStat != null and proenStat != ''">PROEN_STAT = #{proenStat},</if>
            <if test="baseStat != null and baseStat != ''">BASE_STAT = #{baseStat},</if>
            <if test="persId != null and persId != ''">PERS_ID = #{persId},</if>
            <if test="groupId != null and groupId != ''">GROUP_ID = #{groupId},</if>
            <if test="intm != null">INTM = #{intm},</if>
            <if test="uptm != null">UPTM = #{uptm},</if>
        </trim>
        <where>ID = #{id}</where>
    </update>

    <update id="updateBy" parameterType="cn.com.goldenwater.dcproj.model.BisInspWagaSapRgstr">
        update BIS_INSP_WAGA_SAP_RGSTR
        <trim prefix="set" suffixOverrides=",">
            <if test="objId != null and objId != ''">OBJ_ID = #{objId},</if>
            <if test="sapId != null and sapId != ''">SAP_ID = #{sapId},</if>
            <if test="sapName != null and sapName != ''">SAP_NAME = #{sapName},</if>
            <if test="basCode != null and basCode != ''">BAS_CODE = #{basCode},</if>
            <if test="diskName != null and diskName != ''">DISK_NAME = #{diskName},</if>
            <if test="adCode != null and adCode != ''">AD_CODE = #{adCode},</if>
            <if test="adName != null and adName != ''">AD_NAME = #{adName},</if>
            <if test="startLoc != null and startLoc != ''">START_LOC = #{startLoc},</if>
            <if test="endLoc != null and endLoc != ''">END_LOC = #{endLoc},</if>
            <if test="centerX != null and centerX != ''">CENTER_X = #{centerX},</if>
            <if test="centerY != null and centerY != ''">CENTER_Y = #{centerY},</if>
            <if test="gdX != null and gdX != ''">GD_X = #{gdX},</if>
            <if test="gdY != null and gdY != ''">GD_Y = #{gdY},</if>
            <if test="riverBank != null and riverBank != ''">RIVER_BANK = #{riverBank},</if>
            <if test="lake != null and lake != ''">LAKE = #{lake},</if>
            <if test="coast != null and coast != ''">COAST = #{coast},</if>
            <if test="dikeGrad != null and dikeGrad != ''">DIKE_GRAD = #{dikeGrad},</if>
            <if test="sapLen != null and sapLen != ''">SAP_LEN = #{sapLen},</if>
            <if test="sapType != null and sapType != ''">SAP_TYPE = #{sapType},</if>
            <if test="chkState != null and chkState != ''">CHK_STATE = #{chkState},</if>
            <if test="mampu != null and mampu != ''">MAMPU = #{mampu},</if>
            <if test="mampuAttn != null and mampuAttn != ''">MAMPU_ATTN = #{mampuAttn},</if>
            <if test="mampuAttnTel != null and mampuAttnTel != ''">MAMPU_ATTN_TEL = #{mampuAttnTel},</if>
            <if test="compUnitAttn != null and compUnitAttn != ''">COMP_UNIT_ATTN = #{compUnitAttn},</if>
            <if test="compUnitAttnTel != null and compUnitAttnTel != ''">COMP_UNIT_ATTN_TEL = #{compUnitAttnTel},</if>
            <if test="gvmtFldWho != null and gvmtFldWho != ''">GVMT_FLD_WHO = #{gvmtFldWho},</if>
            <if test="gvmtFldWhoTel != null and gvmtFldWhoTel != ''">GVMT_FLD_WHO_TEL = #{gvmtFldWhoTel},</if>
            <if test="note != null and note != ''">NOTE = #{note},</if>
            <if test="state != null and state != ''">STATE = #{state},</if>
            <if test="mrsCiStat != null and mrsCiStat != ''">MRS_CI_STAT = #{mrsCiStat},</if>
            <if test="safeStat != null and safeStat != ''">SAFE_STAT = #{safeStat},</if>
            <if test="dmamStat != null and dmamStat != ''">DMAM_STAT = #{dmamStat},</if>
            <if test="proenStat != null and proenStat != ''">PROEN_STAT = #{proenStat},</if>
            <if test="baseStat != null and baseStat != ''">BASE_STAT = #{baseStat},</if>
            <if test="persId != null and persId != ''">PERS_ID = #{persId},</if>
            <if test="groupId != null and groupId != ''">GROUP_ID = #{groupId},</if>
            <if test="intm != null">INTM = #{intm},</if>
            <if test="uptm != null">UPTM = #{uptm},</if>
        </trim>
        <include refid="page_where"/>
    </update>


    <!-- 其他自定义SQL -->
    <select id="getWagaSapRegstrInfo" resultType="cn.com.goldenwater.dcproj.dto.BisInspRgstrDto">
        SELECT
        *
        FROM
        (
        SELECT
        a.OBJ_ID AS objId,
        #{pType} AS ptype,
        ( CASE WHEN a.STATE IS NULL THEN '0' ELSE a.STATE END ) AS state,
        ( CASE WHEN a.AD_NAME IS NULL THEN to_char( e.AD_FULL_NAME ) ELSE a.AD_NAME END ) location,
        A.CENTER_X, A.CENTER_Y, A.GD_X, A.GD_Y,
        A.GD_X AS LGTD, A.GD_Y AS LTTD,
        a.INTM AS intm,
        a.UPTM AS uptm,
        b.id AS nodeId,
        a.id AS rgstrId,
        a.SAP_NAME AS nm,
        insp.pnm AS groupName,
        a.MRS_CI_STAT AS mrsCiStat,
        a.SAFE_STAT AS safeStat,
        a.DMAM_STAT AS dmamStat,
        a.PROEN_STAT AS proenStat,
        a.BASE_STAT AS baseStat,
        a.BASE_STAT AS baseInfoState,
        a.SAP_ID AS sapId,
        a.PERS_ID AS persId
        FROM
        BIS_INSP_WAGA_SAP_RGSTR A
        LEFT JOIN BIS_INSP_ALL_OBJ b ON a.OBJ_ID = b.OBJ_ID
        LEFT JOIN ATT_AD_X_BASE e ON A.AD_CODE = e.ad_code
        LEFT JOIN bis_insp_all insp ON b.id = insp.id
        WHERE
        1=1
        <if test="tabType =='2'.toString()">
            and insp.entm &lt;to_date(#{nowTime},'yyyy-MM-dd')+1
        </if>
        <if test="tabType =='1'.toString()">
            and insp.entm &gt;=to_date(#{nowTime},'yyyy-MM-dd')
        </if>
        AND B.Id in (
        select distinct id from (
        SELECT P.id FROM BIS_INSP_ALL P START WITH P.PID IN (SELECT id FROM BIS_INSP_ALL_RLATION A
        WHERE A.PERSID = #{presId} AND type= '29' and length(id) in (3,6,9)
        <include refid="choseSql"/>
        )
        CONNECT BY P.PID = PRIOR P.ID
        union all
        SELECT P.id FROM BIS_INSP_ALL P where p.id in (SELECT id FROM BIS_INSP_ALL_RLATION A
        WHERE A.PERSID =#{presId} AND type= '29' and length(id)=12
        <include refid="choseSql"/>
        )
        )
        )
        ) a
        WHERE
        1 =1
        <if test="sttm !=null and sttm !='' and entm !=null and entm !=''">
            and intm&gt;=to_date(#{sttm},'yyyy-mm-dd') and intm &lt;=to_date(#{entm},'yyyy-mm-dd')
        </if>
        <if test="state !=null and state !=''">
            and state in (${state})
        </if>
        <if test="adName !=null and adName !=''">
            and location like '%${adName}%'
        </if>
        <if test="groupId !=null and groupId !=''">
            and nodeId like '${groupId}%'
        </if>
        <if test="plnaId !=null and plnaId !=''">
            and nodeId like '${plnaId}%'
        </if>
        <if test="rsName !=null and rsName !=''">
            and nm like '${rsName}%'
        </if>

    </select>

    <select id="getWagaSapRgstrInfoByObjId" resultType="java.lang.String">
        select id from BIS_INSP_WAGA_SAP_RGSTR where OBJ_ID = #{objId}
    </select>
    <delete id="deleteById">
        delete from BIS_INSP_WAGA_SAP_RGSTR where id = #{id}
    </delete>

    <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="getObjListNotInspGroupId" parameterType="String"
            resultType="cn.com.goldenwater.dcproj.model.AttWagaSapBase">
        select t.*
        from att_irr_base t
        where t.id not in (
        select CODE from BIS_INSP_ALL_OBJ B where id like concat(#{inspGroupId},'%') and B.obj_type=#{objType}
        <choose>
            <when test="province !=null and province !=''">
                and B.AD_CODE =#{province}
            </when>
            <otherwise>
                and B.AD_CODE is null
            </otherwise>
        </choose>
        )
        <if test="adName != null and adName != ''">and t.nm LIKE '%${adName}%'</if>
        <if test="adCode != null and adCode != ''">and t.AD_CODE LIKE '${adCode}%'</if>

    </select>

    <select id="getListByInspGroupIdObjType" resultType="cn.com.goldenwater.dcproj.model.AttWagaSapBase"
            parameterType="cn.com.goldenwater.dcproj.param.PagePersObjParam">
        select t.*,C.id groupId,C.PNM groupName,B.obj_id
        from att_waga_sap_base t
        left join BIS_INSP_ALL_OBJ B
        on B.CODE = t.id
        LEFT JOIN BIS_INSP_ALL C ON B.ID = C.ID
        and B.ptype ='29'
        left join bis_insp_waga_sap_rgstr r on b.obj_id=r.obj_id
        where 1 = 1

        <if test='isAll == "0"'>and B.ID = #{inspGroupId}</if>
        <if test='isAll == "1"'>and B.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 B.AD_CODE LIKE '${province}%'
            </when>
            <otherwise>
                and B.AD_CODE is null
            </otherwise>
        </choose>
    </select>
</mapper>