fj-dc/.svn/pristine/cd/cd7f991d35aa6a2ce07be02c507f0aefd2952fbc.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.BisInspAllDao">

    <resultMap type="cn.com.goldenwater.dcproj.model.BisInspAll" id="bisInspAllResultMap">
        <result property="id" column="ID"/>
        <result property="pid" column="PID"/>
        <result property="pnm" column="PNM"/>
        <result property="sttm" column="STTM"/>
        <result property="entm" column="ENTM"/>
        <result property="inspYear" column="INSP_YEAR"/>
        <result property="inspMnth" column="INSP_MNTH"/>
        <result property="inspTask" column="INSP_TASK"/>
        <result property="impData" column="IMP_DATA"/>
        <result property="province" column="PROVINCE"/>
        <result property="chkType" column="CHK_TYPE"/>
        <result property="leadDep" column="LEAD_DEP"/>
        <result property="chkForm" column="CHK_FORM"/>
        <result property="yearTaskId" column="YEAR_TASK_ID"/>
        <result property="isPlan" column="IS_PLAN"/>
        <result property="quantity" column="QUANTITY"/>
        <result property="planDpId" column="PLAN_DP_ID"/>
        <result property="note" column="NOTE"/>

    </resultMap>

    <select id="listInspType" resultType="cn.com.goldenwater.dcproj.model.InspType">
        select code,name,ptype,in_tm,up_tm from att_insp_type order BY code
    </select>

    <sql id="table_columns">
		ID,
		PID,
		PNM,
		STTM,
		ENTM,INSP_YEAR,INSP_MNTH,INSP_TASK,IMP_DATA,PROVINCE,CHK_TYPE,
		LEAD_DEP,
		CHK_FORM,
		YEAR_TASK_ID,
        IS_PLAN,
        ORG_ID,
		QUANTITY,
        PLAN_DP_ID,
        NOTE
    </sql>
    <sql id="entity_properties">
		#{id},
		#{pid},
		#{pnm},
		#{sttm},
		#{entm},#{inspYear},#{inspMnth},#{inspTask},#{impData},#{province},
		#{chkType},
		#{leadDep},
		#{chkForm},
		#{yearTaskId},
        #{isPlan},
        #{orgId},
        #{quantity},
        #{planDpId},
        #{note}
	</sql>

    <!-- 使用like用法：columnName like concat('%',#columnName#,'%') -->
    <sql id="page_where">
        <trim prefix="where" prefixOverrides="and | or ">
            <if test="id != null and id != ''">and ID LIKE concat(#{id},'%')</if>
            <if test="pid != null and pid != ''">and PID = #{pid}</if>
            <if test="pnm != null and pnm != ''">and PNM like concat('%',concat(#{pnm},'%'))</if>
            <if test="sttm != null">and STTM = #{sttm}</if>
            <if test="entm != null">and ENTM = #{entm}</if>
            <if test="inspMnth != null and inspMnth != ''">and INSP_MNTH = #{inspMnth}</if>
            <if test="inspYear != null and inspYear != ''">and INSP_YEAR = #{inspYear}</if>
            <if test="chkType != null and chkType != ''">and CHK_TYPE = #{chkType}</if>
            <if test="leadDep != null and leadDep != ''">and LEAD_DEP = #{leadDep}</if>
            <if test="chkForm != null and chkForm != ''">and CHK_FORM = #{chkForm}</if>
            <if test="yearTaskId != null and yearTaskId != ''">and YEAR_TASK_ID = #{yearTaskId}</if>
            <if test="isPlan != null and isPlan != ''">and IS_PLAN = #{isPlan}</if>
            <if test="quantity != null">and QUANTITY = #{quantity}</if>
            <if test="planDpId != null and planDpId != ''">and PLAN_DP_ID = #{planDpId}</if>
            <if test="note != null and note != ''">and NOTE = #{note}</if>
        </trim>
    </sql>

    <select id="getOne" resultMap="bisInspAllResultMap">
        select * from BIS_INSP_ALL a where a.id in (select ID from bis_insp_all_obj j where j.nm like '%${nm}%')
        <if test="id != null and id != ''">and ID LIKE concat(#{id},'%')</if>
        <if test="pid != null and pid != ''">and PID = #{pid}</if>
        <if test="pnm != null and pnm != ''">and PNM like concat('%',concat(#{pnm},'%'))</if>
        <if test="sttm != null">and STTM = #{sttm}</if>
        <if test="entm != null">and ENTM = #{entm}</if>
        <if test="inspMnth != null and inspMnth != ''">and INSP_MNTH = #{inspMnth}</if>
        <if test="inspYear != null and inspYear != ''">and INSP_YEAR = #{inspYear}</if>
        <if test="chkType != null and chkType != ''">and CHK_TYPE = #{chkType}</if>
        <if test="leadDep != null and leadDep != ''">and LEAD_DEP = #{leadDep}</if>
        <if test="chkForm != null and chkForm != ''">and CHK_FORM = #{chkForm}</if>
        <if test="yearTaskId != null and yearTaskId != ''">and YEAR_TASK_ID = #{yearTaskId}</if>
        <if test="chkType != null and chkType != ''">and CHK_TYPE = #{chkType}</if>
        <if test="leadDep != null and leadDep != ''">and LEAD_DEP = #{leadDep}</if>
        <if test="chkForm != null and chkForm != ''">and CHK_FORM = #{chkForm}</if>
        <if test="yearTaskId != null and yearTaskId != ''">and YEAR_TASK_ID = #{yearTaskId}</if>
        <if test="isPlan != null and isPlan != ''">and IS_PLAN = #{isPlan}</if>
        <if test="quantity != null">and QUANTITY = #{quantity}</if>
        <if test="planDpId != null and planDpId != ''">and PLAN_DP_ID = #{planDpId}</if>
        <if test="note != null and note != ''">and NOTE = #{note}</if>
    </select>

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

    <select id="getBy" resultMap="bisInspAllResultMap">
        select
        <include refid="table_columns"/>
        from BIS_INSP_ALL
        <include refid="page_where"/>
    </select>

    <select id="findAll" resultMap="bisInspAllResultMap">
        select
        <include refid="table_columns"/>
        from BIS_INSP_ALL
    </select>

    <select id="findAreas" resultMap="bisInspAllResultMap">
        select bas_code as id,bas_name as pnm from ATT_BAS_BASE order by bas_code

    </select>

    <select id="findList" resultMap="bisInspAllResultMap">
        select
        <include refid="table_columns"/>
        from BIS_INSP_ALL
        <include refid="page_where"/>
    </select>

    <sql id="selSql">
        <choose>
            <when test="province !=null and province !=''">
                and t.ad_code=#{province}
            </when>
            <otherwise>
                and t.ad_code is null
            </otherwise>
        </choose>
    </sql>
    <select id="findNodeByPersId" resultMap="bisInspAllResultMap">
        select DISTINCT t2.ID,t2.PID,t2.PNM,t2.STTM,t2.ENTM
        from (
        select * from bis_insp_all o where 1=1
        <choose>
            <when test="province !=null and province !=''">
                and o.province=#{province}
            </when>
            <otherwise>
                and o.province is null
            </otherwise>
        </choose>
        ) t2
        start with t2.id in
        (select t.id
        from bis_insp_all_rlation t
        where t.persid =#{persId}
        <include refid="selSql"/>
        )
        connect by t2.pid = prior t2.id
    </select>

    <select id="findExistList" parameterType="cn.com.goldenwater.dcproj.param.BisInspAllParam"
            resultType="cn.com.goldenwater.dcproj.model.BisInspAllRlation">
        select distinct id,substr(o.id,4) as org_id from bis_insp_all_rlation o where o.id like '___${orgId}%' and
        persid=#{persId}
        <choose>
            <when test="province !=null and province !=''">
                and o.ad_code=#{province}
            </when>
            <otherwise>
                and o.ad_code is null
            </otherwise>
        </choose>

    </select>

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

    <select id="selectMax" resultType="String">
        select Max(ID) as id from BIS_INSP_ALL
        <include refid="page_where"/>
    </select>
    <insert id="insert" parameterType="cn.com.goldenwater.dcproj.model.BisInspAll">
        insert into BIS_INSP_ALL(
        <include refid="table_columns"/>
        )
        values (
        <include refid="entity_properties"/>
        )
    </insert>

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

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

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

    <update id="update" parameterType="cn.com.goldenwater.dcproj.model.BisInspAll">
        update BIS_INSP_ALL
        <trim prefix="set" suffixOverrides=",">
            <if test="pid != null and pid != ''">PID = #{pid},</if>
            <if test="pnm != null and pnm != ''">PNM = #{pnm},</if>
            <if test="sttm != null">STTM = #{sttm},</if>
            <if test="entm != null">ENTM = #{entm},</if>
            <if test="inspMnth != null">INSP_MNTH = #{inspMnth},</if>
            <if test="inspYear != null">INSP_YEAR = #{inspYear},</if>
            <if test="inspTask != null">INSP_TASK = #{inspTask},</if>
            <if test="impData != null">IMP_DATA = #{impData},</if>
            <if test="chkType != null and chkType != ''">CHK_TYPE = #{chkType},</if>
            <if test="leadDep != null and leadDep != ''">LEAD_DEP = #{leadDep},</if>
            <if test="chkForm != null and chkForm != ''">CHK_FORM = #{chkForm},</if>
            <if test="yearTaskId != null and yearTaskId != ''">YEAR_TASK_ID = #{yearTaskId},</if>
            <if test="isPlan != null and isPlan != ''">IS_PLAN = #{isPlan},</if>
            <if test="quantity != null">QUANTITY = #{quantity},</if>
            <if test="note != null and note != ''">NOTE = #{note},</if>
        </trim>
        <where>ID = #{id}</where>
    </update>

    <update id="updateBy" parameterType="cn.com.goldenwater.dcproj.model.BisInspAll">
        update BIS_INSP_ALL
        <trim prefix="set" suffixOverrides=",">
            <if test="pid != null and pid != ''">PID = #{pid},</if>
            <if test="pnm != null and pnm != ''">PNM = #{pnm},</if>
            <if test="sttm != null">STTM = #{sttm},</if>
            <if test="entm != null">ENTM = #{entm},</if>
            <if test="orgId != null and orgId != ''">ORG_ID = #{orgId},</if>
            <if test="inspMnth != null">INSP_MNTH = #{inspMnth},</if>
            <if test="inspYear != null">INSP_YEAR = #{inspYear},</if>
            <if test="inspTask != null">INSP_TASK = #{inspTask},</if>
            <if test="impData != null">IMP_DATA = #{impData},</if>
            <if test="chkType != null and chkType != ''">CHK_TYPE = #{chkType},</if>
            <if test="leadDep != null and leadDep != ''">LEAD_DEP = #{leadDep},</if>
            <if test="chkForm != null and chkForm != ''">CHK_FORM = #{chkForm},</if>
            <if test="yearTaskId != null and yearTaskId != ''">YEAR_TASK_ID = #{yearTaskId},</if>
            <if test="isPlan != null and isPlan != ''">IS_PLAN = #{isPlan},</if>
            <if test="quantity != null">QUANTITY = #{quantity},</if>
            <if test="note != null and note != ''">NOTE = #{note},</if>
        </trim>
        <include refid="page_where"/>
    </update>


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

    <!--查询子id最大的对象 参数 001-->
    <select id="getMaxOne" resultMap="bisInspAllResultMap">
        select Max(id) as id from BIS_INSP_ALL t where t.pid = #{pid}
    </select>

    <select id="getAllNode" resultType="cn.com.goldenwater.dcproj.model.BisInspAll">
        select DISTINCT w.* from (
        select a.*,t.PERSID,t.AD_CODE from bis_insp_all a ,bis_insp_all_rlation t
        where SUBSTR(a.id,1,6)=t.ID
        <include refid="selSql"/>
        and t.persid=#{userid} and length(t.ID)=6
        <if test="orgType !=null and orgType != ''">
            and a.ID LIKE concat(#{orgType},'%')
        </if>
        UNION ALL
        select a.*,t.PERSID,t.AD_CODE from bis_insp_all a ,bis_insp_all_rlation t
        where SUBSTR(a.id,1,9)=t.ID
        <include refid="selSql"/>
        and t.persid=#{userid} and LENGTH(t.id)=9
        <if test="orgType !=null and orgType != ''">
            and a.ID LIKE concat(#{orgType},'%')
        </if>
        UNION ALL
        select a.*,t.PERSID,t.AD_CODE from bis_insp_all a ,bis_insp_all_rlation t
        where a.id=t.ID
        <include refid="selSql"/>
        and t.persid=#{userid} and LENGTH(t.id)=12
        <if test="orgType !=null and orgType != ''">
            and a.ID LIKE concat(#{orgType},'%')
        </if>
        ) w where 1=1
        <if test="orgType !=null and orgType != ''">
            and ID LIKE concat(#{orgType},'%')
        </if>
        <if test="tabType =='2'.toString()">
            and entm &lt;to_date(#{nowTime},'yyyy-MM-dd')+1
        </if>
        <if test="tabType =='1'.toString()">
            and entm &gt;=to_date(#{nowTime},'yyyy-MM-dd')
        </if>
        <if test="year !=null and year != ''">
            and INSP_YEAR=#{year}
        </if>
        <if test="curYear !=null and curYear != ''">
            and ((TO_CHAR(STTM,'YYYY') = #{curYear}) OR (TO_CHAR(ENTM,'YYYY') = #{curYear}))
        </if>
        <if test="yearTaskId !=null and yearTaskId != ''">
            and (YEAR_TASK_ID=#{yearTaskId} or length(id)=6)
        </if>
        <if test="leadDep !=null and leadDep != ''">
            and lead_dep LIKE CONCAT('%', CONCAT(#{leadDep}, '%') )
        </if>
        order by id
    </select>

    <select id="BisInspAllObjById" resultType="cn.com.goldenwater.dcproj.model.BisInspAllNode">
        select RGSTR_ID as id,ID as pid,OBJ_NM AS pnm,OBJ_ID,AD_CODE,code from
        VIEW_REG_BASE t
        where
        1 = 1
        <include refid="choseSql"/>
        <if test="id !=null and id != ''">
            and ID = #{id}
        </if>
        order by OBJ_NM
    </select>

    <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="getAllNodeByLevel" resultType="cn.com.goldenwater.dcproj.model.BisInspAll">
        select id,pid,pnm,sttm,entm from
        BIS_INSP_ALL t where
        REGEXP_LIKE(
        Id,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="choseSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="choseSql"/>
        )
        else (select 'non' from dual) end)
        ||')')
        <if test="objType !=null and objType != ''">
            and ID LIKE concat(#{objType},'%')
        </if>
        <if test="level !=null and level != ''">
            and length(ID) >= #{level}
        </if>
        order by ID
    </select>

    <select id="getUserByOrg" resultType="java.util.HashMap">
        SELECT
        a.ID,
        a.PID,
        a.PNM,
        d.PNM AS pidnm,
        t.PERTYPE,
        c.GUID,
        c.ORG_ID,
        c.PERS_NAME,
        c.BORN_DATE,
        c.PLST,
        c.SCHOOL,
        c.SPECIALTY,
        c.TELNUMB,
        c.FAXNUMB,
        c.ADM_DUTY,
        c.ORG_NM,
        c.MOBILENUMB,
        c.IDNM,
        c.DPNM,
        c.DPPOST,
        c.EMAIL,
        c.REMARK,
        c.IMGURL
        FROM
        (
        SELECT
        ID,
        PID,
        PNM,
        STTM,
        ENTM
        FROM
        BIS_INSP_ALL a START WITH a.id = #{objType} connect BY prior a.ID = a.PID
        ) a
        LEFT JOIN BIS_INSP_ALL_RLATION t ON a.ID = t.id
        LEFT JOIN BIS_INSP_ALL_RLATION_PERS c ON t.PERSID = c.GUID
        LEFT JOIN BIS_INSP_ALL d ON a.PID = d.ID
        WHERE length(a.ID) = 12
        <include refid="selSql"/>
        ORDER BY
        a.ID,b.PERTYPE desc
    </select>
    <sql id="objSql">
        <choose>
            <when test="province !=null and province !=''">
                and obj.ad_code=#{province}
            </when>
            <otherwise>
                and obj.ad_code is null
            </otherwise>
        </choose>
    </sql>
    <!--导出模块获取流域树 水库1，人饮2，水毁3 三棵树-->
    <select id="getNodeByPidAndCode" resultType="java.util.LinkedHashMap">
        <choose>
            <when test="pid.length() >= 12">
                select distinct '' "id", obj.nm "pnm",
                obj.code "code", obj.nm "nm",obj.ptype "pType",obj.obj_id "objId",rgstr.eng_id "engId"
                from BIS_INSP_ALL_OBJ obj left join BIS_INSP_VILL_RGSTR rgstr
                on obj.obj_id = rgstr.obj_id
                where
                obj.id = #{pid}
                <!--instr((select LISTAGG(code, ',') WITHIN GROUP(ORDER BY id) from BIS_INSP_ALL_OBJ where id = #{pid}),obj.code) > 0-->
                <if test="type !=null and type == '2'.toString()">
                    and rgstr.eng_id is not null
                </if>
                <include refid="objSql"/>
                <if test="type !=null and type == '1'.toString()">
                    and rgstr.eng_id = 'non'
                </if>
                <if test="type !=null and type == '3'.toString()">
                    and rgstr.eng_id = 'non'
                </if>
            </when>
            <otherwise>
                SELECT distinct
                ia.id "id", ia.pnm "pnm"
                FROM BIS_INSP_ALL ia
                LEFT JOIN BIS_INSP_ALL_OBJ obj ON ia.id = obj.id
                WHERE ia.pid LIKE #{pid}||'%'
                AND length(ia.id) = length(#{pid})+3
                <include refid="objSql"/>
                <if test="pid !=null and pid.length() >= 9 and code !=null and code !=''">
                    <choose>
                        <when test="type !=null and type=='3'.toString()">
                            AND instr(#{code},substr((select loc_ad from BIS_INSP_WTDST where wtdst_id = obj.code),0,2))
                            > 0
                        </when>
                        <otherwise>
                            AND instr(#{code},substr(code,0,2)) > 0
                        </otherwise>
                    </choose>
                </if>
                ORDER BY ia.id
            </otherwise>
        </choose>
    </select>

    <select id="getAllNodeById" resultType="cn.com.goldenwater.dcproj.model.BisInspAll">
        select id,pid,pnm,sttm,entm from BIS_INSP_ALL where id in (${id})
    </select>

    <select id="countSysMsg" parameterType="String" resultType="cn.com.goldenwater.dcproj.dto.DataDto">
        select sum(ducha_type) as ducha_type,sum(ducha_group) as ducha_group,sum(ducha_pers) as ducha_pers,
        sum(ducha_obj) as ducha_obj,sum(ducha_pblm) as ducha_pblm from (
        select count(distinct substr(t.id,0,3)) as ducha_type,0 as ducha_group,0 as ducha_pers,0 as ducha_obj,0 as ducha_pblm
        from bis_insp_all t where length(t.id)=12 and t.sttm &lt;= to_date(#{nowTIme},'yyyy-MM-dd')
        and t.entm &gt;= to_date(#{nowTIme},'yyyy-MM-dd') and t.id like '___${orgId}%'
        union all
        select 0 as ducha_type,count(t.id) as ducha_group,0 as ducha_pers,0 as ducha_obj,0 as ducha_pblm
        from bis_insp_all t where length(t.id)=12 and t.sttm &lt;= to_date(#{nowTIme},'yyyy-MM-dd')
        and t.entm &gt;= to_date(#{nowTIme},'yyyy-MM-dd') and t.id like '___${orgId}%'
        union all
        select 0 as ducha_type,0 as ducha_group,count(distinct r.persid ) as ducha_pers,0 as ducha_obj,0 as ducha_pblm
        from bis_insp_all t,bis_insp_all_rlation r where  length(t.id)=12 and t.id=r.id
        and t.sttm &lt;= to_date(#{nowTIme},'yyyy-MM-dd')
        and t.entm &gt;= to_date(#{nowTIme},'yyyy-MM-dd') and t.id like '___${orgId}%'
        union all
        select 0 as ducha_type,0 as ducha_group,0 as ducha_pers,count(r.code ) as ducha_obj,0 as ducha_pblm
        from bis_insp_all t,bis_insp_all_obj r where  t.id=r.id and t.sttm &lt;= to_date(#{nowTIme},'yyyy-MM-dd')
        and t.entm &gt;= to_date(#{nowTIme},'yyyy-MM-dd') and t.id like '___${orgId}%'
        union all
        select 0 as ducha_type,0 as ducha_group,0 as ducha_pers,0 as ducha_obj,count(m.pblm_id ) as ducha_pblm
        from bis_insp_all t,bis_insp_all_obj r,bis_insp_pblm m where t.id=r.id and r.obj_id=m.obj_id
        and t.sttm &lt;= to_date(#{nowTIme},'yyyy-MM-dd')
        and t.entm &gt;= to_date(#{nowTIme},'yyyy-MM-dd') and t.id like '___${orgId}%'
        ) w
    </select>

    <select id="countUserLine" resultType="cn.com.goldenwater.dcproj.dto.UserLineDto">
        select sum(pers_total) as pers_total,sum(active_user) as active_user,org_id from (
        select count(g.pers_id) as pers_total,0 as active_user,g.org_id from bis_insp_all_rlation_pers
        t,bis_insp_pers_org g
        where t.guid=g.pers_id
        <choose>
            <when test="orgId !=null and orgId !=''">
                and g.org_id=#{orgId}
            </when>
            <otherwise>
                and g.org_id is null
            </otherwise>
        </choose>

        group by g.org_id
        union all
        select 0 as pers_total,count(distinct s.guid) as active_user,o.org_id from bis_insp_all_rlation_pers
        s,bis_insp_pers_org o,bis_insp_log g
        where s.guid =g.user_id and s.guid=o.pers_id
        <choose>
            <when test="orgId !=null and orgId !=''">
                and o.org_id=#{orgId}
            </when>
            <otherwise>
                and o.org_id is null
            </otherwise>
        </choose>
        group by o.org_id

        ) w group by org_id
    </select>

    <select id="countUserLine2" resultType="cn.com.goldenwater.dcproj.dto.UserLineDto">
        select sum(pers_total) as pers_total,sum(active_user) as active_user,org_id from (
        select count(g.pers_id) as pers_total,0 as active_user,g.org_id from bis_insp_all_rlation_pers
        t,bis_insp_pers_org g
        where t.guid=g.pers_id
        <if test="orgId !=null and orgId !=''">
            and g.org_id=#{orgId}
        </if>
        group by g.org_id
        union all
        select 0 as pers_total,count(distinct s.guid) as active_user,o.org_id from bis_insp_all_rlation_pers
        s,bis_insp_pers_org o,bis_insp_log g
        where s.guid =g.user_id and s.guid=o.pers_id
        <if test="orgId !=null and orgId !=''">
            and o.org_id=#{orgId}
        </if>
        group by o.org_id
        ) w group by org_id
    </select>

    <select id="findOrgMsg" resultType="cn.com.goldenwater.dcproj.dto.OrgDto">
        select t.org_id,t.org_nm
        from bis_insp_org t
        where t.org_pid='1'
    </select>

    <select id="listByName" resultType="cn.com.goldenwater.dcproj.model.BisInspAll">
        SELECT
            rownum id, O.*
        FROM (
        SElECT
             '000' PID, PNM, TO_CHAR(LISTAGG(ID, ',') WITHIN GROUP(ORDER BY PNM)) AS "code"
        FROM BIS_INSP_ALL
        WHERE
            PID IN (${pTypes})
            AND LENGTH(ID) = #{length}
        GROUP BY PNM
        ORDER BY TO_CHAR(LISTAGG(ID, ',') WITHIN GROUP(ORDER BY PNM))
        ) o
    </select>

    <select id="listByMap" resultType="cn.com.goldenwater.dcproj.dto.BisInspAllDto">
        SELECT distinct C.*, T.NAME AS OBJ_TYPE_NAME, CT.PNM PPNM
        FROM BIS_INSP_ALL C
        LEFT JOIN BIS_INSP_ALL CT ON CT.ID = C.PID
        JOIN (select * FROM ATT_INSP_TYPE where code IN (select INSP_TYPE FROM REL_ORG_INSP_TYPE
        <where>
            <if test="orgId != null and orgId != '' ">
                AND ORG_ID = #{orgId}
            </if>
        </where>
        )) T ON SUBSTR(C.ID, 0, 3) = T.CODE
        LEFT JOIN BIS_INSP_ALL_OBJ O ON O.ID = C.ID
        <where>
            length(C.ID) = 12
            <if test="depm != null and depm.size() > 0 ">
                AND SUBSTR(C.ID, 0, 3) IN (SELECT PTYPE FROM ATT_INSP_TYPE_DUTY where DUTY_DEP IN
                <foreach item="item" index="index" collection="depm" open="(" separator="," close=")">
                    '${item}'
                </foreach>
                )
            </if>
            <if test="adCode != null and adCode != ''">and O.OBJ_AD_CODE like concat(#{adCode}, '%')</if>
            <if test="length != null and length != ''">and length(C.ID) = #{length}</if>
            <if test="id != null and id != ''">and C.ID like '%${id}%'</if>
            <if test="pnm != null and pnm != ''">and C.pnm like '%${pnm}%'</if>
            <if test="objTypeName != null and objTypeName != ''">and T.NAME like '%${objTypeName}%'</if>
            <if test="ppnm != null and ppnm != ''">and CT.PNM like '%${ppnm}%'</if>
            <if test="orgId != null and orgId != '' ">AND C.ID LIKE '___${orgId}%'</if>
            <if test="objType != null and objType != ''">and TO_NUMBER(SUBSTR(C.ID, 0, 3)) = #{objType}</if>
            <if test="sttm != null and sttm != '' and entm != null and entm != '' ">
                AND C.STTM &gt;= TO_DATE(#{sttm}, 'YYYY-MM-DD')
                AND C.STTM &lt; TO_DATE(#{entm}, 'YYYY-MM-DD') + 1
            </if>
        </where>
        order by C.ID
    </select>

    <select id="listByMap2" resultType="cn.com.goldenwater.dcproj.dto.BisInspAllDto">
        SELECT distinct C.*
        FROM BIS_INSP_ALL C
        <where>
            <if test="length != null and length != ''">and length(C.ID) = #{length}</if>
            <if test="id != null and id != ''">and C.ID like '%${id}%'</if>
            <if test="pnm != null and pnm != ''">and C.pnm like '%${pnm}%'</if>
            <if test="year != null and year != ''">and TO_CHAR(C.STTM, 'YYYY') = #{year}</if>
            <if test="sttm != null and sttm != '' and entm != null and entm != '' ">
                AND TO_DATE(TO_CHAR(C.STTM, 'YYYY-MM-DD'), 'YYYY-MM-DD') &gt;= TO_DATE(#{sttm}, 'YYYY-MM-DD')
                AND TO_DATE(TO_CHAR(C.ENTM, 'YYYY-MM-DD'), 'YYYY-MM-DD') &lt; TO_DATE(#{entm}, 'YYYY-MM-DD') + 1
            </if>
        </where>
        order by C.ID
    </select>

    <select id="getYearById" resultType="java.lang.String">
        SELECT DISTINCT
            year
        FROM
            (
            SELECT
                DISTINCT TO_CHAR(STTM, 'yyyy') year
            FROM BIS_INSP_ALL
            where ID LIKE '${id}%'
            order by STTM
        ) WHERE year IS NOT NULL
    </select>
    <select id="getPersDcWork" resultType="cn.com.goldenwater.dcproj.model.BisInspAll">
        select A.*,b.pertype from bis_insp_all A  JOIN bis_insp_all_rlation B ON A.ID=B.ID WHERE b.persid=#{persId} AND LENGTH(B.ID)=12
    </select>

    <!--首页 四川  批次数汇总  督查批次统计 批次 类型 组数 督查人员数、区域数、对象数 -->
    <select id="getInspBatchNum"  resultType="java.util.Map">
        select AB.ID , AB.PNM, AB.INSP_TYPE, T.NAME,
               NVL(COUNT(DISTINCT CASE WHEN AG.ID is not null THEN AG.ID END  ),0) as GROUP_NUM ,
               NVL(COUNT(DISTINCT CASE WHEN AR.PERSID is not null THEN AR.PERSID END  ),0) as PERS_NUM ,
               NVL(COUNT(DISTINCT CASE WHEN SA.AREA_ID is not null THEN SA.AREA_ID END  ),0) as AREA_NUM  ,
               NVL(COUNT(DISTINCT CASE WHEN AO.OBJ_ID is not null THEN AO.OBJ_ID END  ),0) as OBJ_NUM
        from BIS_INSP_ALL AB
         JOIN (select CODE,NAME, PTYPE FROM ATT_INSP_TYPE where code IN (select INSP_TYPE FROM REL_ORG_INSP_TYPE where org_id = #{orgId} )) T ON substr(AB.ID,1,3) = T.CODE
         LEFT JOIN  BIS_INSP_ALL AG on AG.PID = AB.ID
         LEFT JOIN  BIS_INSP_ALL_RLATION AR ON AR.ID = AG.ID
         LEFT JOIN  BIS_INSP_SEL_AREA SA ON SA.ID =  AG.ID
         LEFT JOIN  BIS_INSP_ALL_OBJ AO ON AO.ID = AG.ID
        <where>
            LENGTH(AB.ID) = 9
            <if test=' null != orgId and "" !=orgId' >
                AND AB.ID like '___${orgId}%'
            </if>
            <if test=' null != adCode and "" !=adCode' >
                AND AB.PROVINCE like '${adCode}%'
            </if>
            <if test="sttm != null and sttm != '' and entm != null and entm != '' ">
                AND TO_DATE(TO_CHAR(AB.STTM, 'YYYY-MM-DD'), 'YYYY-MM-DD') &gt;= TO_DATE(#{sttm}, 'YYYY-MM-DD')
                AND TO_DATE(TO_CHAR(AB.INTM, 'YYYY-MM-DD'), 'YYYY-MM-DD') &lt; TO_DATE(#{entm}, 'YYYY-MM-DD') + 1
            </if>
            <if test=' null != pnm and "" !=pnm' >
                AND AB.PNM like '%${pnm}%'
            </if>
            <if test=' null != objType and "" !=objType' >
                AND AB.ID like '${objType}%'
            </if>
            <if test=' null != objTypeName and "" !=objTypeName' >
                AND T.NAME like '%${objTypeName}%'
            </if>
        </where>
        group by AB.ID , AB.PNM, AB.INSP_TYPE, T.NAME
    </select>

</mapper>