fj-dc/.svn/pristine/fd/fdda2d5bb2985dc9abaa6d5af7f582023dc6d9c5.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.BisInspStndRgstrDao">

	<resultMap type="cn.com.goldenwater.dcproj.model.BisInspStndRgstr" id="bisInspStndRgstrResultMap">
		<result property="id" column="ID"/>
		<result property="objId" column="OBJ_ID"/>
		<result property="rsCode" column="RS_CODE"/>
		<result property="rsvrNm" column="RSVR_NM"/>
		<result property="adCode" column="AD_CODE"/>
		<result property="resCreateTime" column="RES_CREATE_TIME"/>
		<result property="engScal" column="ENG_SCAL"/>
		<result property="totCap" column="TOT_CAP"/>
		<result property="hystSite" column="HYST_SITE"/>
		<result property="hasDamRegCode" column="HAS_DAM_REG_CODE"/>
		<result property="damRegCode" column="DAM_REG_CODE"/>
		<result property="damType" column="DAM_TYPE"/>
		<result property="resCreateTimeNote" column="RES_CREATE_TIME_NOTE"/>
		<result property="engScalNote" column="ENG_SCAL_NOTE"/>
		<result property="groupId" column="GROUP_ID"/>
		<result property="groupLeader" column="GROUP_LEADER"/>
		<result property="groupLeaderTel" column="GROUP_LEADER_TEL"/>
		<result property="recPersId" column="REC_PERS_ID"/>
		<result property="recPers" column="REC_PERS"/>
		<result property="recPersTel" column="REC_PERS_TEL"/>
		<result property="state" column="STATE"/>
		<result property="dtyminState" column="DTYMIN_STATE"/>
		<result property="mgamtState" column="MGAMT_STATE"/>
		<result property="plrdState" column="PLRD_STATE"/>
		<result property="persId" column="PERS_ID"/>
		<result property="note" column="NOTE"/>
		<result property="intm" column="INTM"/>
		<result property="uptm" column="UPTM"/>
		<result property="dataStat" column="DATA_STAT"/>
		<result property="jxDuty" column="JX_DUTY"/>
		<result property="jxDutyPhone" column="JX_DUTY_PHONE"/>
		<result property="jxRsvrType" column="JX_RSVR_TYPE"/>
		<result property="jxRsvrStndLev" column="JX_RSVR_STND_LEV"/>
		<result property="rsAdmName" column="RS_ADM_NAME"/>
		<result property="rsAdmCode" column="RS_ADM_CODE"/>
		<result property="adNm" column="AD_NM"/>
		<result property="damSizeHig" column="DAM_SIZE_HIG"/>
		<result property="centerX" column="CENTER_X"/>
		<result property="centerY" column="CENTER_Y"/>
		<result property="centerXGd" column="CENTER_X_GD"/>
		<result property="centerYGd" column="CENTER_Y_GD"/>
	</resultMap>
	
	<sql id="table_columns">
		ID,
		OBJ_ID,
		RS_CODE,
		RSVR_NM,
		AD_CODE,
		RES_CREATE_TIME,
		ENG_SCAL,
		TOT_CAP,
		HYST_SITE,
		HAS_DAM_REG_CODE,
		DAM_REG_CODE,
		DAM_TYPE,
		RES_CREATE_TIME_NOTE,
		ENG_SCAL_NOTE,
		GROUP_ID,
		GROUP_LEADER,
		GROUP_LEADER_TEL,
		REC_PERS_ID,
		REC_PERS,
		REC_PERS_TEL,
		STATE,
		DTYMIN_STATE,
		MGAMT_STATE,
		PLRD_STATE,
		PERS_ID,
		NOTE,
		INTM,
		UPTM,
		DATA_STAT,
		JX_DUTY,
		JX_DUTY_PHONE,
		JX_RSVR_TYPE,
		JX_RSVR_STND_LEV,
		RS_ADM_NAME,
		RS_ADM_CODE,
		AD_NM,
		DAM_SIZE_HIG,
		CENTER_X,
		CENTER_Y,
		CENTER_X_GD,
		CENTER_Y_GD
    </sql>
	<sql id="entity_properties">
		#{id},
		#{objId},
		#{rsCode},
		#{rsvrNm},
		#{adCode},
		#{resCreateTime},
		#{engScal},
		#{totCap},
		#{hystSite},
		#{hasDamRegCode},
		#{damRegCode},
		#{damType},
		#{resCreateTimeNote},
		#{engScalNote},
		#{groupId},
		#{groupLeader},
		#{groupLeaderTel},
		#{recPersId},
		#{recPers},
		#{recPersTel},
		#{state},
		#{dtyminState},
		#{mgamtState},
		#{plrdState},
		#{persId},
		#{note},
		#{intm},
		#{uptm},
		#{dataStat},
		#{jxDuty},
		#{jxDutyPhone},
		#{jxRsvrType},
		#{jxRsvrStndLev},
		#{rsAdmName},
		#{rsAdmCode},
		#{adNm},
		#{damSizeHig},
		#{centerX},
		#{centerY},
		#{centerXGd},
		#{centerYGd}
	</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="rsCode != null and rsCode != ''">and RS_CODE = #{rsCode}</if>
            <if test="rsvrNm != null and rsvrNm != ''">and RSVR_NM = #{rsvrNm}</if>
            <if test="adCode != null and adCode != ''">and AD_CODE = #{adCode}</if>
            <if test="resCreateTime != null">and RES_CREATE_TIME = #{resCreateTime}</if>
            <if test="engScal != null and engScal != ''">and ENG_SCAL = #{engScal}</if>
            <if test="totCap != null and totCap != ''">and TOT_CAP = #{totCap}</if>
            <if test="hystSite != null and hystSite != ''">and HYST_SITE = #{hystSite}</if>
            <if test="hasDamRegCode != null and hasDamRegCode != ''">and HAS_DAM_REG_CODE = #{hasDamRegCode}</if>
            <if test="damRegCode != null and damRegCode != ''">and DAM_REG_CODE = #{damRegCode}</if>
            <if test="damType != null and damType != ''">and DAM_TYPE = #{damType}</if>
            <if test="resCreateTimeNote != null and resCreateTimeNote != ''">and RES_CREATE_TIME_NOTE = #{resCreateTimeNote}</if>
            <if test="engScalNote != null and engScalNote != ''">and ENG_SCAL_NOTE = #{engScalNote}</if>
            <if test="groupId != null and groupId != ''">and GROUP_ID = #{groupId}</if>
            <if test="groupLeader != null and groupLeader != ''">and GROUP_LEADER = #{groupLeader}</if>
            <if test="groupLeaderTel != null and groupLeaderTel != ''">and GROUP_LEADER_TEL = #{groupLeaderTel}</if>
            <if test="recPersId != null and recPersId != ''">and REC_PERS_ID = #{recPersId}</if>
            <if test="recPers != null and recPers != ''">and REC_PERS = #{recPers}</if>
            <if test="recPersTel != null and recPersTel != ''">and REC_PERS_TEL = #{recPersTel}</if>
            <if test="state != null and state != ''">and STATE = #{state}</if>
            <if test="dtyminState != null and dtyminState != ''">and DTYMIN_STATE = #{dtyminState}</if>
            <if test="mgamtState != null and mgamtState != ''">and MGAMT_STATE = #{mgamtState}</if>
            <if test="plrdState != null and plrdState != ''">and PLRD_STATE = #{plrdState}</if>
            <if test="persId != null and persId != ''">and PERS_ID = #{persId}</if>
            <if test="note != null and note != ''">and NOTE = #{note}</if>
            <if test="intm != null">and INTM = #{intm}</if>
            <if test="uptm != null">and UPTM = #{uptm}</if>
            <if test="dataStat != null and dataStat != ''">and DATA_STAT = #{dataStat}</if>
			<if test="jxDuty != null and jxDuty != ''">and JX_DUTY= #{jxDuty}</if>
			<if test="jxDutyPhone != null and jxDutyPhone != ''">and JX_DUTY_PHONE = #{jxDutyPhone}</if>
			<if test="jxRsvrType != null and jxRsvrType != ''">and JX_RSVR_TYPE = #{jxRsvrType}</if>
			<if test="jxRsvrStndLev != null and jxRsvrStndLev != ''">and JX_RSVR_STND_LEV = #{jxRsvrStndLev}</if>
			<if test="rsAdmName != null and rsAdmName != ''">and RS_ADM_NAME = #{rsAdmName}</if>
			<if test="rsAdmCode != null and rsAdmCode != ''">and RS_ADM_CODE = #{rsAdmCode}</if>
			<if test="adNm != null and adNm != ''">and AD_NM = #{adNm}</if>
            and DATA_STAT='0'
        </trim>
    </sql>


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

    <select id="getBy" resultMap="bisInspStndRgstrResultMap">
        select <include refid="table_columns" /> from BIS_INSP_STND_RGSTR <include refid="page_where" />
    </select>

    <select id="findAll" resultMap="bisInspStndRgstrResultMap">
        select <include refid="table_columns" /> from BIS_INSP_STND_RGSTR
    </select>

    <select id="findList" resultMap="bisInspStndRgstrResultMap">
        select <include refid="table_columns" /> from BIS_INSP_STND_RGSTR <include refid="page_where" />
    </select>

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


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

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

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

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

	<update id="update" parameterType="cn.com.goldenwater.dcproj.model.BisInspStndRgstr">
		update BIS_INSP_STND_RGSTR 
		<trim prefix="set" suffixOverrides=",">
			<if test="objId != null and objId != ''">OBJ_ID = #{objId},</if>
			<if test="rsCode != null and rsCode != ''">RS_CODE = #{rsCode},</if>
			<if test="rsvrNm != null and rsvrNm != ''">RSVR_NM = #{rsvrNm},</if>
			<if test="adCode != null and adCode != ''">AD_CODE = #{adCode},</if>
			<if test="resCreateTime != null">RES_CREATE_TIME = #{resCreateTime},</if>
			<if test="engScal != null and engScal != ''">ENG_SCAL = #{engScal},</if>
			<if test="totCap != null and totCap != ''">TOT_CAP = #{totCap},</if>
			<if test="hystSite != null and hystSite != ''">HYST_SITE = #{hystSite},</if>
			<if test="hasDamRegCode != null and hasDamRegCode != ''">HAS_DAM_REG_CODE = #{hasDamRegCode},</if>
			<if test="damRegCode != null and damRegCode != ''">DAM_REG_CODE = #{damRegCode},</if>
			<if test="damType != null and damType != ''">DAM_TYPE = #{damType},</if>
			<if test="resCreateTimeNote != null and resCreateTimeNote != ''">RES_CREATE_TIME_NOTE = #{resCreateTimeNote},</if>
			<if test="engScalNote != null and engScalNote != ''">ENG_SCAL_NOTE = #{engScalNote},</if>
			<if test="groupId != null and groupId != ''">GROUP_ID = #{groupId},</if>
			<if test="groupLeader != null and groupLeader != ''">GROUP_LEADER = #{groupLeader},</if>
			<if test="groupLeaderTel != null and groupLeaderTel != ''">GROUP_LEADER_TEL = #{groupLeaderTel},</if>
			<if test="recPersId != null and recPersId != ''">REC_PERS_ID = #{recPersId},</if>
			<if test="recPers != null and recPers != ''">REC_PERS = #{recPers},</if>
			<if test="recPersTel != null and recPersTel != ''">REC_PERS_TEL = #{recPersTel},</if>
			<if test="state != null and state != ''">STATE = #{state},</if>
			<if test="dtyminState != null and dtyminState != ''">DTYMIN_STATE = #{dtyminState},</if>
			<if test="mgamtState != null and mgamtState != ''">MGAMT_STATE = #{mgamtState},</if>
			<if test="plrdState != null and plrdState != ''">PLRD_STATE = #{plrdState},</if>
			<if test="persId != null and persId != ''">PERS_ID = #{persId},</if>
			<if test="note != null and note != ''">NOTE = #{note},</if>
			<if test="intm != null">INTM = #{intm},</if>
			<if test="uptm != null">UPTM = #{uptm},</if>
			<if test="dataStat != null and dataStat != ''">DATA_STAT = #{dataStat},</if>
			<if test="jxDuty!= null and jxDuty != ''">JX_DUTY = #{jxDuty},</if>
			<if test="jxDutyPhone != null and jxDutyPhone != ''">JX_DUTY_PHONE = #{jxDutyPhone},</if>
			<if test="jxRsvrType != null and jxRsvrType != ''">JX_RSVR_TYPE = #{jxRsvrType},</if>
			<if test="jxRsvrStndLev != null and jxRsvrStndLev != ''">JX_RSVR_STND_LEV = #{jxRsvrStndLev},</if>
			<if test="rsAdmName != null and rsAdmName != ''">RS_ADM_NAME = #{rsAdmName},</if>
			<if test="rsAdmCode != null and rsAdmCode != ''">RS_ADM_CODE = #{rsAdmCode},</if>
			<if test="adNm != null and adNm != ''">AD_NM = #{adNm},</if>
			<if test="damSizeHig != null and damSizeHig != ''">DAM_SIZE_HIG = #{damSizeHig},</if>
			<if test="centerX != null and centerX != ''">CENTER_X = #{centerX},</if>
			<if test="centerY != null and centerY != ''">CENTER_Y = #{centerY},</if>
			<if test="centerXGd != null and centerXGd != ''">CENTER_X_GD = #{centerXGd},</if>
			<if test="centerYGd != null and centerYGd != ''">CENTER_Y_GD = #{centerYGd},</if>
		</trim>
		<where>ID = #{id}</where>
	</update>

    <update id="updateBy" parameterType="cn.com.goldenwater.dcproj.model.BisInspStndRgstr">
        update BIS_INSP_STND_RGSTR
        <trim prefix="set" suffixOverrides=",">
            <if test="objId != null and objId != ''">OBJ_ID = #{objId},</if>
            <if test="rsCode != null and rsCode != ''">RS_CODE = #{rsCode},</if>
            <if test="rsvrNm != null and rsvrNm != ''">RSVR_NM = #{rsvrNm},</if>
            <if test="adCode != null and adCode != ''">AD_CODE = #{adCode},</if>
            <if test="resCreateTime != null">RES_CREATE_TIME = #{resCreateTime},</if>
            <if test="engScal != null and engScal != ''">ENG_SCAL = #{engScal},</if>
            <if test="totCap != null and totCap != ''">TOT_CAP = #{totCap},</if>
            <if test="hystSite != null and hystSite != ''">HYST_SITE = #{hystSite},</if>
            <if test="hasDamRegCode != null and hasDamRegCode != ''">HAS_DAM_REG_CODE = #{hasDamRegCode},</if>
            <if test="damRegCode != null and damRegCode != ''">DAM_REG_CODE = #{damRegCode},</if>
            <if test="damType != null and damType != ''">DAM_TYPE = #{damType},</if>
            <if test="resCreateTimeNote != null and resCreateTimeNote != ''">RES_CREATE_TIME_NOTE = #{resCreateTimeNote},</if>
            <if test="engScalNote != null and engScalNote != ''">ENG_SCAL_NOTE = #{engScalNote},</if>
            <if test="groupId != null and groupId != ''">GROUP_ID = #{groupId},</if>
            <if test="groupLeader != null and groupLeader != ''">GROUP_LEADER = #{groupLeader},</if>
            <if test="groupLeaderTel != null and groupLeaderTel != ''">GROUP_LEADER_TEL = #{groupLeaderTel},</if>
            <if test="recPersId != null and recPersId != ''">REC_PERS_ID = #{recPersId},</if>
            <if test="recPers != null and recPers != ''">REC_PERS = #{recPers},</if>
            <if test="recPersTel != null and recPersTel != ''">REC_PERS_TEL = #{recPersTel},</if>
            <if test="state != null and state != ''">STATE = #{state},</if>
            <if test="dtyminState != null and dtyminState != ''">DTYMIN_STATE = #{dtyminState},</if>
            <if test="mgamtState != null and mgamtState != ''">MGAMT_STATE = #{mgamtState},</if>
            <if test="plrdState != null and plrdState != ''">PLRD_STATE = #{plrdState},</if>
            <if test="persId != null and persId != ''">PERS_ID = #{persId},</if>
            <if test="note != null and note != ''">NOTE = #{note},</if>
            <if test="intm != null">INTM = #{intm},</if>
            <if test="uptm != null">UPTM = #{uptm},</if>
            <if test="dataStat != null and dataStat != ''">DATA_STAT = #{dataStat},</if>
			<if test="jxDuty!= null and jxDuty != ''">JX_DUTY = #{jxDuty},</if>
			<if test="jxDutyPhone != null and jxDutyPhone != ''">JX_DUTY_PHONE = #{jxDutyPhone},</if>
			<if test="jxRsvrType != null and jxRsvrType != ''">JX_RSVR_TYPE = #{jxRsvrType},</if>
			<if test="jxRsvrStndLev != null and jxRsvrStndLev != ''">JX_RSVR_STND_LEV = #{jxRsvrStndLev},</if>
			<if test="rsAdmName != null and rsAdmName != ''">RS_ADM_NAME = #{rsAdmName},</if>
			<if test="rsAdmCode != null and rsAdmCode != ''">RS_ADM_CODE = #{rsAdmCode},</if>
			<if test="adNm != null and adNm != ''">AD_NM = #{adNm},</if>
			<if test="damSizeHig != null and damSizeHig != ''">DAM_SIZE_HIG = #{damSizeHig},</if>
			<if test="centerX != null and centerX != ''">CENTER_X = #{centerX},</if>
			<if test="centerY != null and centerY != ''">CENTER_Y = #{centerY},</if>
			<if test="centerXGd != null and centerXGd != ''">CENTER_X_GD = #{centerXGd},</if>
			<if test="centerYGd != null and centerYGd != ''">CENTER_Y_GD = #{centerYGd},</if>
        </trim>
        <include refid="page_where" />
    </update>


	<!-- 其他自定义SQL -->
	<select id="findStndRgstrPage" resultType="cn.com.goldenwater.dcproj.dto.BisInspRgstrDto"
			parameterType="cn.com.goldenwater.dcproj.param.TypeParam">
		select * from (
		SELECT A.code,
		(case when b.rsvr_nm is null then A.nm else b.rsvr_nm end) nm,
		A.id groupId,
		A.ID,
		A.pType,
		A.OBJ_INTM,
		A.OBJ_ID,
		c.pnm groupName,
		A.Is_Set_Obj,
		(CASE WHEN B.STATE IS NULL THEN '0' ELSE B.STATE END) AS wtdstState,
		(case when b.eng_scal is null then to_char(e.eng_scal) else b.eng_scal end) eng_scal,
		b.state,
		(case when b.hyst_site is null then to_char(e.location) else b.hyst_site end) as hyst_site,
		(case when b.hyst_site is null then  to_char(e.location) else b.hyst_site end) location,
		b.id rgstrId,
		b.rsvr_nm rsvrNm,
		b.ad_code adCode,
		b.res_create_time resCreateTime,
		b.tot_cap totCap,
		b.dam_reg_code damRegCode,
		b.dam_type damType,
		b.has_dam_reg_code hasDamRegCode,
		b.res_create_time_note resCreateTimeNote,
		b.eng_scal_note engScalNote,
		b.group_leader groupLeader,
		b.group_leader_tel groupLeaderTel,
		b.rec_pers_id recPersId,
		b.rec_pers recPers,
		b.rec_pers_tel recPersTel,
		b.uptm,
		b.intm,
		b.note,
		b.dtymin_state,
		b.mgamt_state,
		b.plrd_state
		FROM BIS_INSP_ALL_OBJ A
		LEFT JOIN BIS_INSP_STND_RGSTR B
		ON A.Obj_Id = B.OBJ_ID
		left join BIS_INSP_ALL C on A.ID=C.ID
		left join ATT_RS_BASE e
		on A.code = e.rs_code
		where 1=1 and
		<if test="tabType =='2'.toString()">
			and C.entm &lt;to_date(#{nowTime},'yyyy-MM-dd')+1
		</if>
		<if test="tabType =='1'.toString()">
			and C.entm &gt;=to_date(#{nowTime},'yyyy-MM-dd')
		</if>
		REGEXP_LIKE(
		A.Id,'^('||
		( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{presId} and id like '024%'
		<include refid="orgIdSql"/>
		)>0 then
		(SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
		id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{presId}
		<include refid="orgIdSql"/> and id like '024%'
		)
		else (select 'non' from dual) end)
		||')')
		) a where a.PTYPE=#{pType}
		<if test="plnaId !=null and plnaId !=''">
			and ID like '${plnaId}%'
		</if>
		<if test="engScal !=null and engScal !=''">
			and eng_scal =#{engScal}
		</if>
        <if test="state !=null and state !=''">
            and wtdstState =#{state}
        </if>
		<if test="adName !=null and adName  !=''">
			and location like '${adName}%'
		</if>

		<if test="code !=null and code !=''">
			and code like '${code}%'
		</if>
		<if test="rsName !=null and rsName !=''">
			and rsvrNm like '%${rsName}%'
		</if>
		<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>

		order by wtdstState asc
		<if test="orderBy != null and orderBy != ''">
			,${orderBy}
		</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="getListStndByInspGroupIdObjType" resultType="cn.com.goldenwater.dcproj.model.BisInspStndRgstr" parameterType="cn.com.goldenwater.dcproj.param.PagePersObjParam">
		select t.*,C.id groupId,C.PNM groupName,B.obj_id,r.hyst_site loc
		from att_ad_base t
		left join BIS_INSP_ALL_OBJ B
		on B.CODE = t.ad_code
		LEFT JOIN BIS_INSP_ALL C ON B.ID = C.ID
		and B.ptype = #{objType}
		left join BIS_INSP_STND_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>