fj-dc/.svn/pristine/2a/2a1ab7242aee6cb283bb38fdaeaf3dc6bde231c2.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.BisInspMtprggdDao">

	<resultMap type="cn.com.goldenwater.dcproj.model.BisInspMtprggd" id="bisInspMtprggdResultMap">
		<result property="id" column="ID"/>
		<result property="unitName" column="UNIT_NAME"/>
		<result property="year" column="YEAR"/>
		<result property="mnth" column="MNTH"/>
		<result property="idx" column="IDX"/>
		<result property="pname" column="PNAME"/>
		<result property="ptype" column="PTYPE"/>
		<result property="tm" column="TM"/>
		<result property="prjSize" column="PRJ_SIZE"/>
		<result property="depSize" column="DEP_SIZE"/>
		<result property="chkSize" column="CHK_SIZE"/>
		<result property="leadSize" column="LEAD_SIZE"/>
		<result property="chkDaySize" column="CHK_DAY_SIZE"/>
		<result property="feedBackSize" column="FEED_BACK_SIZE"/>
		<result property="pblmSize" column="PBLM_SIZE"/>
		<result property="mendSize" column="MEND_SIZE"/>
		<result property="mendCmpSize" column="MEND_CMP_SIZE"/>
		<result property="mendCmpTatSize" column="MEND_CMP_TAT_SIZE"/>
		<result property="mendCmpRatio" column="MEND_CMP_RATIO"/>
		<result property="accuntDep" column="ACCUNT_DEP"/>
		<result property="accuntText" column="ACCUNT_TEXT"/>
		<result property="acuntTm" column="ACUNT_TM"/>
		<result property="accuntNote" column="ACCUNT_NOTE"/>
		<result property="persId" column="PERS_ID"/>
		<result property="intm" column="INTM"/>
		<result property="uptm" column="UPTM"/>
		<result property="dataStat" column="DATA_STAT"/>
		<result property="adCode" column="AD_CODE"/>
		<result property="chkYearId" column="CHK_YEAR_ID"/>
		<result property="em" column="EM"/>
	</resultMap>
	
	<sql id="table_columns">
		ID,
		CHK_YEAR_ID,
		UNIT_NAME,
		YEAR,
		MNTH,
		IDX,
		PNAME,
		PTYPE,
		TM,
		PRJ_SIZE,
		DEP_SIZE,
		CHK_SIZE,
		LEAD_SIZE,
		CHK_DAY_SIZE,
		FEED_BACK_SIZE,
		PBLM_SIZE,
		MEND_SIZE,
		MEND_CMP_SIZE,
		MEND_CMP_TAT_SIZE,
		MEND_CMP_RATIO,
		ACCUNT_DEP,
		ACCUNT_TEXT,
		ACUNT_TM,
		ACCUNT_NOTE,
		PERS_ID,
		INTM,
		UPTM,
		DATA_STAT,
		AD_CODE,
		EM
    </sql>
	<sql id="entity_properties">
		#{id},
		#{chkYearId},
		#{unitName},
		#{year},
		#{mnth},
		#{idx},
		#{pname},
		#{ptype},
		#{tm},
		#{prjSize},
		#{depSize},
		#{chkSize},
		#{leadSize},
		#{chkDaySize},
		#{feedBackSize},
		#{pblmSize},
		#{mendSize},
		#{mendCmpSize},
		#{mendCmpTatSize},
		#{mendCmpRatio},
		#{accuntDep},
		#{accuntText},
		#{acuntTm},
		#{accuntNote},
		#{persId},
		#{intm},
		#{uptm},
		#{dataStat},
		#{adCode},
		#{em}
	</sql>

    <!-- 使用like用法：columnName like concat('%',#columnName#,'%') -->
    <sql id="page_where">
        <trim prefix="where" prefixOverrides="and | or ">
            <if test="unitName != null and unitName != ''">and UNIT_NAME = #{unitName}</if>
            <if test="year != null and year != ''">and YEAR = #{year}</if>
            <if test="mnth != null and mnth != ''">and MNTH = #{mnth}</if>
            <if test="idx != null and idx != ''">and IDX = #{idx}</if>
            <if test="pname != null and pname != ''">and PNAME = #{pname}</if>
            <if test="ptype != null and ptype != ''">and PTYPE = #{ptype}</if>
            <if test="tm != null">TM = #{tm}</if>
			<if test="em != null">EM = #{em}</if>
            <if test="prjSize != null and prjSize != ''">and PRJ_SIZE = #{prjSize}</if>
            <if test="depSize != null and depSize != ''">and DEP_SIZE = #{depSize}</if>
            <if test="chkSize != null and chkSize != ''">and CHK_SIZE = #{chkSize}</if>
            <if test="leadSize != null and leadSize != ''">and LEAD_SIZE = #{leadSize}</if>
            <if test="chkDaySize != null and chkDaySize != ''">and CHK_DAY_SIZE = #{chkDaySize}</if>
            <if test="feedBackSize != null and feedBackSize != ''">and FEED_BACK_SIZE = #{feedBackSize}</if>
            <if test="pblmSize != null and pblmSize != ''">and PBLM_SIZE = #{pblmSize}</if>
            <if test="mendSize != null and mendSize != ''">and MEND_SIZE = #{mendSize}</if>
            <if test="mendCmpSize != null and mendCmpSize != ''">and MEND_CMP_SIZE = #{mendCmpSize}</if>
            <if test="mendCmpTatSize != null and mendCmpTatSize != ''">and MEND_CMP_TAT_SIZE = #{mendCmpTatSize}</if>
            <if test="mendCmpRatio != null and mendCmpRatio != ''">and MEND_CMP_RATIO = #{mendCmpRatio}</if>
            <if test="accuntDep != null and accuntDep != ''">and ACCUNT_DEP = #{accuntDep}</if>
            <if test="accuntText != null and accuntText != ''">and ACCUNT_TEXT = #{accuntText}</if>
            <if test="acuntTm != null">and ACUNT_TM = #{acuntTm}</if>
            <if test="accuntNote != null and accuntNote != ''">and ACCUNT_NOTE = #{accuntNote}</if>
            <if test="persId != null and persId != ''">and PERS_ID = #{persId}</if>
            <if test="intm != null">and INTM = #{intm}</if>
            <if test="uptm != null">and UPTM = #{uptm}</if>
			<if test="adCode != null and adCode != ''">and AD_CODE LIKE '${adCode}%'</if>
			and DATA_STAT = '0'
			and CHK_YEAR_ID = #{chkYearId}
        </trim>
    </sql>


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

    <select id="getBy" resultMap="bisInspMtprggdResultMap">
        select <include refid="table_columns" /> from BIS_INSP_MTPRGGD <include refid="page_where" />
    </select>

    <select id="findAll" resultMap="bisInspMtprggdResultMap">
        select <include refid="table_columns" /> from BIS_INSP_MTPRGGD
    </select>

    <select id="findList" resultMap="bisInspMtprggdResultMap">
        select <include refid="table_columns" /> from BIS_INSP_MTPRGGD <include refid="page_where" />
    </select>

	<select id="findListByMonth" resultMap="bisInspMtprggdResultMap">
		select <include refid="table_columns" /> from BIS_INSP_MTPRGGD where 1=1
		<if test="adCode != null and adCode != ''">and AD_CODE LIKE '${adCode}%'</if>
		<if test="sttm != null and sttm != ''">and CONCAT(CONCAT(year,'-'),(case when mnth>9 then  cast(mnth as char) else concat('0', cast(mnth  as char) ) end)) &gt;= #{sttm}</if>
		<if test="entm != null and entm != ''">and CONCAT(CONCAT(year,'-'),(case when mnth>9 then  cast(mnth as char) else concat('0', cast(mnth  as char) ) end)) &lt;= #{entm}</if>
		and DATA_STAT = '0'
	</select>

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

    <select id="getMonthRefrom" resultType="java.util.Map" parameterType="java.util.Map">
		SELECT * FROM (SELECT
		BA. ID ID,
		BA.PNM groupName,
		(SELECT CODE FROM ATT_INSP_TYPE WHERE substr(BA.ID,0,3)= CODE) OBJTYPE,
		(SELECT NAME FROM ATT_INSP_TYPE WHERE substr(BA.ID,0,3)= CODE) OBJNAME,
		to_char(BA.STTM,'yyyy-mm-dd')  sttm,
		to_char(BA.ENTM,'yyyy-mm-dd')  entm,
		BIA1.PNM BATCH,
		BIA1.CHK_FORM chkForm,
		BIA1.LEAD_DEP leadDep,
		BIA1.PROVINCE province,
		(
		SELECT
		listagg (ad_name, ',') WITHIN GROUP (ORDER BY ID) AS chkArea
		FROM
		BIS_INSP_SEL_AREA
		WHERE
		ID = BA. ID
		GROUP BY
		ID
		) chkArea,
		(
		SELECT
		COUNT (1)
		FROM
		BIS_INSP_ALL_OBJ
		WHERE
		ID = BA. ID
		) COUNT,
		(SELECT
		listagg (pers_name, ',') WITHIN GROUP (ORDER BY ID) AS persName
		FROM (SELECT
		id,pers_name
		FROM
		BIS_INSP_ALL_RLATION b
		LEFT JOIN BIS_INSP_ALL_RLATION_PERS A ON A .guid = b.PERSID
		WHERE
		b.pertype = '1' AND length(b.id)=12) where ID = BA. ID
		GROUP BY
		ID) persName,
		(SELECT
		listagg (mobilenumb, ',') WITHIN GROUP (ORDER BY ID) AS mobilenumb
		FROM (SELECT
		id,mobilenumb
		FROM
		BIS_INSP_ALL_RLATION b
		LEFT JOIN BIS_INSP_ALL_RLATION_PERS A ON A .guid = b.PERSID
		WHERE
		b.pertype = '1' AND length(b.id)=12) where ID = BA. ID
		GROUP BY
		ID) mobilenumb
		FROM
		(
		SELECT
		BIA. ID,
		BIA.PID,
		BIA.PNM,
		BIA.STTM,
		BIA.ENTM
		FROM
		(
		SELECT
		ID
		FROM
		BIS_INSP_ALL
		where length(ID)=12
		GROUP BY
		ID
		) NG
		LEFT JOIN BIS_INSP_ALL BIA ON NG. ID = BIA. ID
		) BA
		LEFT JOIN BIS_INSP_ALL BIA1 ON BA.PID = BIA1. ID
		WHERE
		BA. ID IS NOT NULL
		ORDER BY
		BA. ID) WHERE 1=1
		<if test="sttm!=null and sttm!='' and entm!=null and entm!=''">
			and substr(STTM,0,7) &gt;= #{sttm}
			and substr(ENTM,0,7) &lt;= #{entm}
		</if>
		<if test="objTypes != null and objTypes != ''">
		     and OBJTYPE in
			<foreach collection="objTypes.split(',')" item="item" index="index" open="(" separator="," close=")" >
                #{item}
			</foreach>
		</if>
		<if test="groupName!=null and groupName!=''">
			and GROUPNAME LIKE '%${groupName}%'
		</if>
		<if test="chkArea!=null and chkArea!=''">
			and CHKAREA LIKE '%${chkArea}%'
		</if>
		<if test="adCode!=null and adCode!=''">
			and PROVINCE LIKE '${adCode}%'
		</if>
		order by STTM DESC
	</select>
	<select id="getObjNames" resultType="java.util.Map" parameterType="java.lang.String">
        select ID id,NM nm FROM BIS_INSP_ALL_OBJ WHERE ID = #{id}
	</select>

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

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

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

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

	<update id="update" parameterType="cn.com.goldenwater.dcproj.model.BisInspMtprggd">
		update BIS_INSP_MTPRGGD 
		<trim prefix="set" suffixOverrides=",">
			<if test="unitName != null and unitName != ''">UNIT_NAME = #{unitName},</if>
			<if test="year != null and year != ''">YEAR = #{year},</if>
			<if test="mnth != null and mnth != ''">MNTH = #{mnth},</if>
			<if test="idx != null and idx != ''">IDX = #{idx},</if>
			<if test="pname != null and pname != ''">PNAME = #{pname},</if>
			<if test="ptype != null and ptype != ''">PTYPE = #{ptype},</if>
			<if test="tm != null">TM = #{tm},</if>
			<if test="em != null">EM = #{em},</if>
			<if test="prjSize != null and prjSize != ''">PRJ_SIZE = #{prjSize},</if>
			<if test="depSize != null and depSize != ''">DEP_SIZE = #{depSize},</if>
			<if test="chkSize != null and chkSize != ''">CHK_SIZE = #{chkSize},</if>
			<if test="leadSize != null and leadSize != ''">LEAD_SIZE = #{leadSize},</if>
			<if test="chkDaySize != null and chkDaySize != ''">CHK_DAY_SIZE = #{chkDaySize},</if>
			<if test="feedBackSize != null and feedBackSize != ''">FEED_BACK_SIZE = #{feedBackSize},</if>
			<if test="pblmSize != null and pblmSize != ''">PBLM_SIZE = #{pblmSize},</if>
			<if test="mendSize != null and mendSize != ''">MEND_SIZE = #{mendSize},</if>
			<if test="mendCmpSize != null and mendCmpSize != ''">MEND_CMP_SIZE = #{mendCmpSize},</if>
			<if test="mendCmpTatSize != null and mendCmpTatSize != ''">MEND_CMP_TAT_SIZE = #{mendCmpTatSize},</if>
			<if test="mendCmpRatio != null and mendCmpRatio != ''">MEND_CMP_RATIO = #{mendCmpRatio},</if>
			<if test="accuntDep != null and accuntDep != ''">ACCUNT_DEP = #{accuntDep},</if>
			<if test="accuntText != null and accuntText != ''">ACCUNT_TEXT = #{accuntText},</if>
			<if test="acuntTm != null">ACUNT_TM = #{acuntTm},</if>
			<if test="accuntNote != null and accuntNote != ''">ACCUNT_NOTE = #{accuntNote},</if>
			<if test="persId != null and persId != ''">PERS_ID = #{persId},</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>
		</trim>
		<where>ID = #{id}</where>
	</update>

    <update id="updateBy" parameterType="cn.com.goldenwater.dcproj.model.BisInspMtprggd">
        update BIS_INSP_MTPRGGD
        <trim prefix="set" suffixOverrides=",">
            <if test="unitName != null and unitName != ''">UNIT_NAME = #{unitName},</if>
            <if test="year != null and year != ''">YEAR = #{year},</if>
            <if test="mnth != null and mnth != ''">MNTH = #{mnth},</if>
            <if test="idx != null and idx != ''">IDX = #{idx},</if>
            <if test="pname != null and pname != ''">PNAME = #{pname},</if>
            <if test="ptype != null and ptype != ''">PTYPE = #{ptype},</if>
            <if test="tm != null">TM = #{tm},</if>
			<if test="em != null">EM = #{em},</if>
            <if test="prjSize != null and prjSize != ''">PRJ_SIZE = #{prjSize},</if>
            <if test="depSize != null and depSize != ''">DEP_SIZE = #{depSize},</if>
            <if test="chkSize != null and chkSize != ''">CHK_SIZE = #{chkSize},</if>
            <if test="leadSize != null and leadSize != ''">LEAD_SIZE = #{leadSize},</if>
            <if test="chkDaySize != null and chkDaySize != ''">CHK_DAY_SIZE = #{chkDaySize},</if>
            <if test="feedBackSize != null and feedBackSize != ''">FEED_BACK_SIZE = #{feedBackSize},</if>
            <if test="pblmSize != null and pblmSize != ''">PBLM_SIZE = #{pblmSize},</if>
            <if test="mendSize != null and mendSize != ''">MEND_SIZE = #{mendSize},</if>
            <if test="mendCmpSize != null and mendCmpSize != ''">MEND_CMP_SIZE = #{mendCmpSize},</if>
            <if test="mendCmpTatSize != null and mendCmpTatSize != ''">MEND_CMP_TAT_SIZE = #{mendCmpTatSize},</if>
            <if test="mendCmpRatio != null and mendCmpRatio != ''">MEND_CMP_RATIO = #{mendCmpRatio},</if>
            <if test="accuntDep != null and accuntDep != ''">ACCUNT_DEP = #{accuntDep},</if>
            <if test="accuntText != null and accuntText != ''">ACCUNT_TEXT = #{accuntText},</if>
            <if test="acuntTm != null">ACUNT_TM = #{acuntTm},</if>
            <if test="accuntNote != null and accuntNote != ''">ACCUNT_NOTE = #{accuntNote},</if>
            <if test="persId != null and persId != ''">PERS_ID = #{persId},</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>
        </trim>
        <include refid="page_where" />
    </update>


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


</mapper>