fj-dc/.svn/pristine/ff/ff40d29294a3faf86ed55a3a4f93bc8c88b39f4d.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.BisInspPblmDao">

    <resultMap type="cn.com.goldenwater.dcproj.model.BisInspPblm" id="bisInspPblmResultMap">
        <result property="pblmsTypeId" column="PBLMS_TYPE_ID"/>
        <result property="regid" column="REGID"/>
        <result property="commonFileIds" column="COMMON_FILE_IDS"/>
        <result property="fileNo" column="FILE_NO"/>
        <result property="fileNoNumber" column="FILE_NO_NUMBER"/>
        <result property="pblmsId" column="PBLMS_ID"/>
        <result property="pblmId" column="PBLM_ID"/>
        <result property="quote" column="QUOTE"/>
        <result property="waterUserId" column="WATER_USER_ID"/>
        <result property="objId" column="OBJ_ID"/>
        <result property="objType" column="OBJ_TYPE"/>
        <result property="inspGroupId" column="INSP_GROUP_ID"/>
        <result property="pguid" column="PGUID"/>
        <result property="inspPblmType" column="INSP_PBLM_TYPE"/>
        <result property="inspPblmCode" column="INSP_PBLM_CODE"/>
        <result property="inspPblmName" column="INSP_PBLM_NAME"/>
        <result property="inspPblmDesc" column="INSP_PBLM_DESC"/>
        <result property="inspAddDesc" column="INSP_ADD_DESC"/>
        <result property="pblmLong" column="PBLM_LONG"/>
        <result property="pblmLat" column="PBLM_LAT"/>
        <result property="ifCasePblm" column="IF_CASE_PBLM"/>
        <result property="inspPblmOrgName" column="INSP_PBLM_ORG_NAME"/>
        <result property="pblmPersName" column="PBLM_PERS_NAME"/>
        <result property="inspPblmCate" column="INSP_PBLM_CATE"/>
        <result property="pblmStat" column="PBLM_STAT"/>
        <result property="reviOpin" column="REVI_OPIN"/>
        <result property="reviConc" column="REVI_CONC"/>
        <result property="reviOrgGuid" column="REVI_ORG_GUID"/>
        <result property="dataStat" column="DATA_STAT"/>
        <result property="collTime" column="COLL_TIME"/>
        <result property="villType" column="VILL_TYPE"/>
        <result property="recPers" column="REC_PERS"/>
        <result property="note" column="NOTE"/>
        <result property="persName" column="PERS_NAME"/>
        <result property="inspPblmsName" column="insp_pblms_name"/>
        <result property="checkPoint" column="check_point"/>
        <result property="nm" column="nm"/>
        <result property="objNm" column="obj_nm"/>
        <result property="cwsName" column="cws_name"/>
        <result property="cwsCode" column="CWS_CODE"/>
        <result property="villageCode" column="VILLAGE_CODE"/>
        <result property="pblmDesc" column="pblm_desc"/>
        <result property="state" column="state"/>
        <result property="code" column="CODE"/>
        <result property="adFullName" column="AD_FULL_NAME"/>
        <result property="adCode" column="AD_CODE"/>
        <result property="orgNm" column="ORG_NM"/>
        <result property="orgId" column="ORG_ID"/>
        <result property="rsName" column="rs_name"/>
        <result property="hasVedio" column="HAS_VEDIO"/>
        <result property="problemType" column="PROBLEM_TYPE"/>
        <result property="nature" column="NATURE"/>
        <result property="reviOrg" column="revi_org"/>
        <result property="qualityTypeId" column="QUALITY_TYPE_ID"/>
        <result property="defectTypeId" column="DEFECT_TYPE_ID"/>
        <result property="fdbkId" column="FDBK_ID"/>
        <result property="unitNm" column="unit_nm"/>
        <result property="itemId" column="ITEM_ID"/>
        <result property="snNum" column="sn_num"/>
        <result property="attach" column="attach"/>
        <result property="sheet" column="sheet"/>
        <result property="engScal" column="eng_scal"/>
        <result property="wagaScal" column="waga_Scal"/>
        <result property="gateType" column="gate_type"/>
        <result property="sttp" column="sttp"/>
        <result property="rePtype" column="re_ptype"/>
        <result property="pnm" column="pnm"/>
        <result property="oid" column="oid"/>
        <result property="uptm" column="UP_TM"/>
        <result property="pblmNameBz" column="PBLM_NAME_BZ"/>
        <result property="checkPointBz" column="CHECK_POINT_BZ"/>
        <result property="checkPoint" column="CHECK_POINT"/>
        <result property="snNumBz" column="SN_NUM_BZ"/>
        <result property="pblmDescBz" column="PBLM_DESC_BZ"/>
        <result property="pblmReason" column="PBLM_REASON"/>
        <result property="pblmSggtn" column="PBLM_SGGTN"/>
        <result property="gongdiBzId" column="GONGDI_BZ_ID"/>
        <result property="objCode" column="OBJ_CODE"/>
        <result property="objName" column="OBJ_NAME"/>
        <result property="objAdCode" column="OBJ_AD_CODE"/>
        <result property="attachBz" column="attach_bz"/>
        <result property="wtdstNote" column="WTDST_NOTE"/>
        <result property="score" column="SCORE"/>
        <result property="provincial" column="PROVINCIAL"/>
        <result property="belongOrgId" column="BELONG_ORG_ID"/>
        <result property="belongAdCode" column="BELONG_AD_CODE"/>
    </resultMap>
    <!-- 这是问题删除表字段 -->
    <sql id="table_columns_pblmDelLog">
        PBLM_ID,PBLM_NAME_BZ,CHECK_POINT_BZ,SN_NUM_BZ,PBLM_DESC_BZ,
        OBJ_ID,
        OBJ_TYPE,
        INSP_GROUP_ID,
        PGUID,
        INSP_PBLM_TYPE,
        INSP_PBLM_CODE,
        INSP_PBLM_NAME,
        INSP_PBLM_DESC,
        INSP_ADD_DESC,
        PBLM_LONG,
        PBLM_LAT,
        IF_CASE_PBLM,
        INSP_PBLM_ORG_NAME,
        PBLM_PERS_NAME,
        INSP_PBLM_CATE,
        PBLM_STAT,
        REVI_OPIN,
        REVI_CONC,
        REVI_ORG_GUID,
        DATA_STAT,
        COLL_TIME,
        REC_PERS,
        NOTE,
        REGID,
        COMMON_FILE_IDS,
        FILE_NO,
        FILE_NO_NUMBER,
        PBLMS_ID,
        PBLMS_TYPE_ID,
        VILL_TYPE,
        CWS_CODE,
        VILLAGE_CODE,
        PBLM_DEL_ID,
        PBLM_DEL_PERS,
        PBLM_DEL_PERSNM,
        PBLM_DEL_TM,
        PBLM_DEL_NOTE,QUOTE,
        PROVINCIAL,
        BELONG_ORG_ID,
        BELONG_AD_CODE
    </sql>
    <sql id="entity_properties_pblmDelLog">
        #{pblmId},#{pblmNameBz},#{checkPointBz},#{snNumBz},#{pblmDescBz},
        #{objId},
        #{objType},
        #{inspGroupId},
        #{pguid},
        #{inspPblmType},
        #{inspPblmCode},
        #{inspPblmName},
        #{inspPblmDesc},
        #{inspAddDesc},
        #{pblmLong},
        #{pblmLat},
        #{ifCasePblm},
        #{inspPblmOrgName},
        #{pblmPersName},
        #{inspPblmCate},
        #{pblmStat},
        #{reviOpin},
        #{reviConc},
        #{reviOrgGuid},
        #{dataStat},
        #{collTime},
        #{recPers},
        #{note},
        #{regid},
        #{commonFileIds},
        #{fileNo},
        #{fileNoNumber},
        #{pblmsId},
        #{pblmsTypeId},
        #{villType},
        #{cwsCode},
        #{villageCode},
        #{pblmDelId},
        #{pblmDelPers},
        #{pblmDelPersnm},
        TO_DATE(#{pblmDelTm},'YYYY-MM-DD HH24:MI:SS'),
        #{pblmDelNote},#{quote},#{provincial},#{belongOrgId},#{belongAdCode}
    </sql>

    <!-- 这是问题表字段 -->
    <sql id="table_columns">
        attach_Bz,
        CWS_CODE,HAS_VEDIO,PROBLEM_TYPE,QUALITY_TYPE_ID,DEFECT_TYPE_ID,FDBK_ID,ITEM_ID,WATER_USER_ID,GONGDI_BZ_ID,
        VILLAGE_CODE,
        VILL_TYPE,
        PBLMS_TYPE_ID,
        REGID,
        COMMON_FILE_IDS,
        FILE_NO,
        FILE_NO_NUMBER,
        PBLMS_ID,
        PBLM_ID,
        OBJ_ID,
        OBJ_TYPE,
        INSP_GROUP_ID,
        PGUID,
        INSP_PBLM_TYPE,
        INSP_PBLM_CODE,
        INSP_PBLM_NAME,
        INSP_PBLM_DESC,
        INSP_ADD_DESC,
        PBLM_LONG,
        PBLM_LAT,
        IF_CASE_PBLM,
        INSP_PBLM_ORG_NAME,
        PBLM_PERS_NAME,
        <if test="inspPblmCate != null and inspPblmCate != ''">INSP_PBLM_CATE,</if>
        <if test="relBasis != null and relBasis != ''">REL_BASIS,</if>
        <if test="damRegCode != null and damRegCode != ''">DAM_REG_CODE,</if>
        PBLM_STAT,
        REVI_OPIN,
        REVI_CONC,
        REVI_ORG_GUID,
        DATA_STAT,
        COLL_TIME,
        REC_PERS,
        NOTE,QUOTE,
        UP_TM,PBLM_NAME_BZ,CHECK_POINT_BZ,SN_NUM_BZ,PBLM_DESC_BZ,NEW_VILL_NAME,
        PBLM_REASON,
        PBLM_SGGTN,WTDST_NOTE,
        OBJ_CODE,OBJ_NAME,OBJ_AD_CODE,SCORE,
        PROVINCIAL,BELONG_ORG_ID, BELONG_AD_CODE
    </sql>
    <sql id="entity_properties">
        #{attachBz},
        #{cwsCode},#{hasVedio},#{problemType},#{qualityTypeId},#{defectTypeId},#{fdbkId},#{itemId},#{waterUserId},#{gongdiBzId},
        #{villageCode},
        #{villType},
        #{pblmsTypeId},
        #{regid},
        #{commonFileIds},
        #{fileNo},
        #{fileNoNumber},
        #{pblmsId},
        #{pblmId},
        #{objId},
        #{objType},
        #{inspGroupId},
        #{pguid},
        #{inspPblmType},
        #{inspPblmCode},
        #{inspPblmName},
        #{inspPblmDesc},
        #{inspAddDesc},
        #{pblmLong},
        #{pblmLat},
        #{ifCasePblm},
        #{inspPblmOrgName},
        #{pblmPersName},
        <if test="inspPblmCate != null and inspPblmCate != ''">#{inspPblmCate},</if>
        <if test="relBasis != null and relBasis != ''">#{relBasis},</if>
        <if test="damRegCode != null and damRegCode != ''">#{damRegCode},</if>
        #{pblmStat},
        #{reviOpin},
        #{reviConc},
        #{reviOrgGuid},
        #{dataStat},
        #{collTime},
        #{recPers},
        #{note},#{quote},
        #{uptm},#{pblmNameBz},#{checkPointBz},#{snNumBz},#{pblmDescBz},#{newVillName},
        #{pblmReason},
        #{pblmSggtn}, #{wtdstNote},
        #{objCode}, #{objName}, #{objAdCode},
        #{score},
        #{provincial},
        #{belongOrgId},
        #{belongAdCode}
    </sql>

    <sql id="page_where">
        <trim prefix="where" prefixOverrides="and | or ">
            <if test="waterUserId != null and waterUserId != ''">and WATER_USER_ID = #{waterUserId}</if>
            <if test="cwsCode != null and cwsCode != ''">and CWS_CODE = #{cwsCode}</if>
            <if test="newVillName != null and newVillName != ''">and NEW_VILL_NAME = #{newVillName}</if>
            <if test="pblmNameBz != null and pblmNameBz != ''">and PBLM_NAME_BZ = #{pblmNameBz}</if>
            <if test="snNumBz != null and snNumBz != ''">and SN_NUM_BZ = #{snNumBz}</if>
            <if test="attachBz != null and attachBz != ''">and attach_Bz = #{attachBz}</if>
            <if test="gongdiBzId != null and gongdiBzId != ''">and gongdi_Bz_Id = #{gongdiBzId}</if>
            <if test="checkPointBz != null and checkPointBz != ''">and CHECK_POINT_BZ = #{checkPointBz}</if>
            <if test="pblmDescBz != null and pblmDescBz != ''">and PBLM_DESC_BZ = #{pblmDescBz}</if>
            <if test="regid != null and regid != ''">and REGID = #{regid}</if>
            <if test="quote != null and quote != ''">and QUOTE = #{quote}</if>
            <if test="hasVedio != null and hasVedio != ''">and HAS_VEDIO = #{hasVedio}</if>
            <if test="villType != null and villType != ''">and VILL_TYPE = #{villType}</if>
            <if test="commonFileIds != null and commonFileIds != ''">and COMMON_FILE_IDS = #{commonFileIds}</if>
            <if test="fileNo != null and fileNo != ''">and FILE_NO = #{fileNo}</if>
            <if test="fileNoNumber != null and fileNoNumber != ''">and FILE_NO_NUMBER = #{fileNoNumber}</if>
            <if test="pblmsId != null and pblmsId != ''">and PBLMS_ID = #{pblmsId}</if>
            <if test="pblmId != null and pblmId != ''">and PBLM_ID = #{pblmId}</if>
            <if test="problemType != null and problemType != ''">and problem_Type = #{problemType}</if>
            <if test="objId != null and objId != ''">and OBJ_ID = #{objId}</if>
            <if test="objType != null and objType != ''">and OBJ_TYPE = #{objType}</if>
            <if test="inspGroupId != null and inspGroupId != ''">and INSP_GROUP_ID = #{inspGroupId}</if>
            <if test="pguid != null and pguid != ''">and PGUID = #{pguid}</if>
            <if test="inspPblmType != null and inspPblmType != ''">and INSP_PBLM_TYPE = #{inspPblmType}</if>
            <if test="inspPblmCode != null and inspPblmCode != ''">and INSP_PBLM_CODE = #{inspPblmCode}</if>
            <if test="inspPblmName != null and inspPblmName != ''">and INSP_PBLM_NAME = #{inspPblmName}</if>
            <if test="inspPblmDesc != null and inspPblmDesc != ''">and INSP_PBLM_DESC like '%${inspPblmDesc}%'</if>
            <if test="inspAddDesc != null and inspAddDesc != ''">and INSP_ADD_DESC = #{inspAddDesc}</if>
            <if test="pblmLong != null and pblmLong != ''">and PBLM_LONG = #{pblmLong}</if>
            <if test="pblmLat != null and pblmLat != ''">and PBLM_LAT = #{pblmLat}</if>
            <if test="ifCasePblm != null and ifCasePblm != ''">and IF_CASE_PBLM = #{ifCasePblm}</if>
            <if test="inspPblmOrgName != null and inspPblmOrgName != ''">and INSP_PBLM_ORG_NAME = #{inspPblmOrgName}
            </if>
            <if test="pblmPersName != null and pblmPersName != ''">and PBLM_PERS_NAME = #{pblmPersName}</if>
            <if test="inspPblmCate != null and inspPblmCate != ''">and INSP_PBLM_CATE = #{inspPblmCate}</if>
            <if test="pblmStat != null and pblmStat != ''">and PBLM_STAT = #{pblmStat}</if>
            <if test="reviOpin != null and reviOpin != ''">and REVI_OPIN = #{reviOpin}</if>
            <if test="reviConc != null and reviConc != ''">and REVI_CONC = #{reviConc}</if>
            <if test="reviOrgGuid != null and reviOrgGuid != ''">and REVI_ORG_GUID = #{reviOrgGuid}</if>
            <if test="dataStat != null and dataStat != ''">and DATA_STAT = #{dataStat}</if>
            <if test="collTime != null">and COLL_TIME = #{collTime}</if>
            <if test="recPers != null and recPers != ''">and REC_PERS = #{recPers}</if>
            <if test="note != null and note != ''">and NOTE = #{note}</if>
            <if test="year != null and year != ''">and TO_CHAR(COLL_TIME,'YYYY') = #{year}</if>
            <if test="startTime != null and startTime != ''">and COLL_TIME &gt; TO_DATE(#{startTime},'YYYY-MM-DD
                HH24:MI:SS')
            </if>
            <if test="endTime != null and endTime != ''">and COLL_TIME &lt; TO_DATE(#{endTime},'YYYY-MM-DD
                HH24:MI:SS')
            </if>
            <if test="findTime != null and findTime != ''">and to_char(coll_time,'yyyy-MM-dd')=#{findTime}</if>
            <if test="monthTime != null and monthTime != ''">and to_char(coll_time,'yyyy-MM')=#{monthTime}</if>
            <if test="objCode != null and objCode != ''">and OBJ_CODE = #{objCode}</if>
            <if test="objName != null and objName != ''">and OBJ_NAME = #{objName}</if>
            <if test="objAdCode != null and objAdCode != ''">and OBJ_AD_CODE = #{objAdCode}</if>
            <if test="wtdstNote != null and wtdstNote != ''">and WTDST_NOTE = #{wtdstNote}</if>
            <if test="provincial != null and provincial != ''">and PROVINCIAL = #{provincial}</if>
            <if test="belongOrgId != null and belongOrgId != ''">and BELONG_ORG_ID = #{belongOrgId}</if>
            <if test="belongAdCode != null and belongAdCode != ''">and BELONG_AD_CODE LIKE CONCAT(#{belongAdCode},'%') </if>
        </trim>
    </sql>

    <select id="getListByMap" resultMap="bisInspPblmResultMap" parameterType="java.util.Map">
        select m.* from BIS_INSP_PBLM m where m.obj_type=#{objType} and m.regid=#{regId}
    </select>

    <select id="get" resultMap="bisInspPblmResultMap" parameterType="String">
        select
            S.*, S.PBLM_NAME_BZ AS INSP_PBLMS_NAME,
            S.CHECK_POINT_BZ AS CHECK_POINT, S.PBLM_DESC_BZ AS PBLM_DESC,
            J.NM, B.PERS_NAME, P.ORG_ID, CASE WHEN R.STATE IS NULL THEN '0' ELSE R.STATE END AS STATE
        FROM BIS_INSP_PBLM S
        LEFT JOIN BIS_INSP_ALL_OBJ J ON S.OBJ_ID=J.OBJ_ID
        LEFT JOIN BIS_INSP_RSVR_RGSTR R ON R.OBJ_ID=J.OBJ_ID
        LEFT JOIN BIS_INSP_ALL_RLATION_PERS B ON S.REC_PERS = B.GUID
        LEFT JOIN OBJ_INSP_PBLMS P ON P.GUID=S.PBLMS_TYPE_ID
        where s.PBLM_ID = #{id}
    </select>

    <select id="getBy" resultMap="bisInspPblmResultMap">
        select
        <include refid="table_columns"/>
        from bis_insp_pblm
        <include refid="page_where"/>
    </select>

    <select id="getCommonSingle" resultMap="bisInspPblmResultMap" parameterType="String">
        select w.*,s.insp_pblms_name,s.check_point,s.pblm_desc from (
        select e.OBJ_NM as nm,m.*,s.pers_name from view_reg_base e join bis_insp_pblm m
        on e.OBJ_ID=m.obj_id
        left join bis_insp_all_rlation_pers s
        on m.rec_pers=s.guid
        where 1=1
        <if test="pType !=null and pType !=''">
            and m.obj_type=#{pType}
        </if>
        and m.pblm_id=#{pblmId}
        ) w left join obj_insp_pblms s on w.pblms_type_id=s.guid
    </select>

    <select id="pblmRectPage" parameterType="cn.com.goldenwater.dcproj.param.BisInspVo"
            resultType="cn.com.goldenwater.dcproj.param.BisInspVo">
        select e.RGSTR_NM as name,e.ad_code,p.rec_pers as pers_id,m.rect_id,m.id,p.*,to_char(p.coll_time,'yyyy-MM-dd')
        as insp_pblm_tm
        from VIEW_REG_BASE e,bis_insp_pblm p,bis_insp_rect_pblm m
        LEFT JOIN BIS_INSP_ALL_OBJ J ON e.obj_id = J.OBJ_ID
        LEFT JOIN BIS_INSP_ALL L ON L.ID = J.ID
        LEFT JOIN BIS_INSP_ALL_RLATION_PERS R ON P.REC_PERS = R.GUID
        where e.obj_id=p.obj_id and p.pblm_id=m.pblm_id and m.data_stat='0'
        <include refid="pblmPageSql"/>
    </select>

    <sql id="pblmPageSql">
        <if test="inspPblmCate != null and inspPblmCate != ''">and p.INSP_PBLM_CATE = #{inspPblmCate}</if>
        <if test="ifCasePblm != null and ifCasePblm != ''">and p.IF_CASE_PBLM = #{ifCasePblm}</if>
        <if test="dataStat != null and dataStat != ''">and p.DATA_STAT = #{dataStat}</if>
        <if test="inspPblmName != null and inspPblmName != ''">
            and p.INSP_PBLM_NAME like '%${inspPblmName}%'
        </if>
        <if test="name != null and name != ''">
            and p.obj_name like '%${name}%'
        </if>
        <if test="objName != null and objName != ''">
            and p.obj_name like '%${objName}%'
        </if>
        <if test="adCode != null and adCode != ''">
            and p.obj_ad_code like '${adCode}%'
        </if>
        <if test="objAdCode != null and objAdCode != ''">
            and p.obj_ad_code like '${objAdCode}%'
        </if>
        <!-- 小水电站 通用督查 基础对象code是uuid没有遵循行政区域的编码规则 -->
        <if test="province != null and province != ''">
            and (p.obj_code like '${province}%' or p.belong_ad_code like '${province}%' )
        </if>
        <if test="regid != null and regid != ''">and p.regid = #{regid}</if>
        <if test="objType != null and objType != ''">and p.obj_type = #{objType}</if>
        <if test="startTime != null and startTime != ''">and p.COLL_TIME &gt;= TO_DATE(#{startTime},'YYYY-MM-DD')</if>
        <if test="inspPblmTm != null and inspPblmTm != ''">and p.COLL_TIME=TO_DATE(#{inspPblmTm},'YYYY-MM-DD')</if>
        <if test="endTime != null and endTime != ''">and p.COLL_TIME &lt; TO_DATE(#{endTime},'YYYY-MM-DD')+1</if>
        <if test="pblmStat != null and pblmStat != ''">and p.PBLM_Stat = #{pblmStat}</if>
        <if test="hasVedio != null and hasVedio != ''">and p.HAS_VEDIO = #{hasVedio}</if>
        <if test="objId != null and objId != ''">and p.OBJ_ID =#{objId}</if>
        <if test="provincial != null and provincial != ''">and p.PROVINCIAL =#{provincial}</if>
        <if test='belongOrgId != null and belongOrgId != "" '>and p.BELONG_ORG_ID =#{belongOrgId}</if>
        <if test='belongAdCode != null and belongAdCode != "" '>and p.BELONG_AD_CODE like '${belongAdCode}%'</if>
        <if test="year != null and year != ''">and to_char(p.coll_time,'yyyy')= #{year}</if>
        <!--isJoinRec  是否加入到整改    0-未加过     1-加入过-->
        <if test="isJoinRec != null and isJoinRec != ''">
            <if test="isJoinRec =='0'.toString()">
                and p.pblm_id not in (select pblm_id from BIS_INSP_PBLM_PLIST WHERE pblm_id IS NOT NULL)
            </if>
            <if test="isJoinRec =='1'.toString()">
                and p.pblm_id in (select pblm_id from BIS_INSP_PBLM_PLIST WHERE pblm_id IS NOT NULL)
            </if>
        </if>
        <!--isJoinRec  是否加入到整改    0-未加过     1-加入过-->
        <if test="orgNm != null and orgNm != ''">and L.PNM like '%${orgNm}%'</if>
        <if test="persId != null and persId != ''">and P.REC_PERS = #{persId} </if>
        <if test="persName != null and persName != ''">and R.PERS_NAME like '%${persName}%'</if>
        <if test="leadDep !=null and leadDep != ''">and L.lead_dep LIKE CONCAT('%', CONCAT(#{leadDep}, '%') ) </if>
        order by P.coll_time desc
    </sql>

    <select id="pblmPage" parameterType="cn.com.goldenwater.dcproj.param.BisInspVo"
            resultType="cn.com.goldenwater.dcproj.param.BisInspVo">
        SELECT P.OBJ_NAME AS NAME,P.OBJ_AD_CODE AD_CODE,P.REC_PERS AS PERS_ID,P.*,TO_CHAR(P.COLL_TIME,'YYYY-MM-DD') AS INSP_PBLM_TM,B.AD_FULL_NAME
        FROM BIS_INSP_PBLM P
        JOIN BIS_INSP_ALL_OBJ J ON P.OBJ_ID = J.OBJ_ID
        LEFT JOIN BIS_INSP_ALL L ON L.ID = J.ID
        LEFT JOIN ATT_AD_BASE B ON P.OBJ_AD_CODE = B.AD_CODE
        LEFT JOIN BIS_INSP_ALL_RLATION_PERS R ON P.REC_PERS = R.GUID
        WHERE 1 = 1
        <include refid="pblmPageSql"/>
    </select>

    <select id="getOnePblm" resultType="cn.com.goldenwater.dcproj.model.KeyRegPblm" parameterType="java.lang.String">
        select CWS_CODE,HAS_VEDIO,PROBLEM_TYPE,QUALITY_TYPE_ID,DEFECT_TYPE_ID,gongdi_Bz_Id,
        VILLAGE_CODE,
        VILL_TYPE,
        PBLMS_TYPE_ID,
        REGID,
        COMMON_FILE_IDS,
        FILE_NO,
        FILE_NO_NUMBER,
        PBLMS_ID,m.quote,
        m.PBLM_ID, m.OBJ_ID, OBJ_TYPE, INSP_GROUP_ID,
        PGUID, INSP_PBLM_TYPE, INSP_PBLM_CODE, INSP_PBLM_NAME, INSP_PBLM_DESC, INSP_ADD_DESC, PBLM_LONG, PBLM_LAT,
        IF_CASE_PBLM, INSP_PBLM_ORG_NAME,
        PBLM_PERS_NAME, INSP_PBLM_CATE, PBLM_STAT, REVI_OPIN, REVI_CONC, REVI_ORG_GUID,
        m.DATA_STAT, m.COLL_TIME, m.REC_PERS, m.PROVINCIAL, m.BELONG_ORG_ID, m.BELONG_AD_CODE
        ,(select s.attach||'-'||s.sheet from obj_key_pblms s where s.id=QUALITY_TYPE_ID) as attach_qua,
        (select s.sn from obj_key_pblms s where s.id=QUALITY_TYPE_ID) as attach_qua_sn,
        (select s.attach_name from obj_key_pblms s where s.id=QUALITY_TYPE_ID) as attach_qua_name,
        (select s.attach||'-'||s.sheet from obj_key_pblms s where s.id=DEFECT_TYPE_ID) as attach_def,
        (select s.sn from obj_key_pblms s where s.id=DEFECT_TYPE_ID) as attach_def_sn,
        (select s.attach_name from obj_key_pblms s where s.id=DEFECT_TYPE_ID) as attach_def_name,
        (select s.attach||'-'||s.sheet from obj_key_pblms s where s.id=PBLMS_TYPE_ID) as attach_wg,
        (select s.sn from obj_key_pblms s where s.id=PBLMS_TYPE_ID) as attach_wg_sn,
        (select s.attach_name from obj_key_pblms s where s.id=PBLMS_TYPE_ID) as attach_wg_name,
        (select s.attach||'-'||s.sheet from obj_key_pblms s where s.id=PBLMS_ID) as attach_hetong,
        (select s.sn from obj_key_pblms s where s.id=PBLMS_ID) as attach_hetong_sn,
        (select s.sn from obj_key_pblms s where s.id=gongdi_Bz_Id) as attach_gongdi_sn,
        (select s.attach_name from obj_key_pblms s where s.id=gongdi_Bz_Id) as attach_gongdi_name,
        (select s.attach||'-'||s.sheet from obj_key_pblms s where s.id=gongdi_Bz_Id) as attach_gongdi,
        (select s.attach_name from obj_key_pblms s where s.id=PBLMS_ID) as attach_hetong_name,
        k.rect_meas,k.guid,u.nm as unit_Nm,
        k.rect_conc,k.note as rectNote,k.org_guid,k.coll_time as rect_time,e.revi_org,u.nm as rect_name
        <if test="pType =='7'.toString()">
            ,case when mo.lev='1' then mo.nm else '' end as first_Name,case when mo.lev='2' then mo.nm else '' end as
            second_Name,case when mo.lev='3' then mo.nm else '' end as third_Name
        </if>
        ,bi.nm
        from bis_insp_pblm m
        left join BIS_INSP_KEY_REG_SEC_UNIT u on m.regid=u.id
        left join (
        select revi_org , org_type from obj_key_pblm_rel l where l.org_type is not null group by revi_org,org_type order
        by org_type
        ) e on e.org_type=u.nature
        left join bis_insp_rect_fdbk k on m.pblm_id=k.pblm_id and k.guid=m.FDBK_ID
        <if test="pType =='7'.toString()">
            left join ATT_MAMPU_INFO mo on m.regid = mo.id
        </if>
        <if test="pType=='4'.toString()">
            left join bis_insp_key_register_section mo on u.sec_id = mo.id
        </if>
        left join BIS_INSP_KEY_REGISTER bi on mo.reg_Id=bi.id
        where m.PBLM_ID=#{pblmId}
    </select>

    <select id="findAll" resultMap="bisInspPblmResultMap">
        select
        <include refid="table_columns"/>
        from bis_insp_pblm
    </select>
    <select id="findList" resultMap="bisInspPblmResultMap">
        select
        attach_Bz,
        CWS_CODE,HAS_VEDIO,PROBLEM_TYPE,QUALITY_TYPE_ID,DEFECT_TYPE_ID,FDBK_ID,ITEM_ID,WATER_USER_ID,GONGDI_BZ_ID,
        VILLAGE_CODE,
        VILL_TYPE,
        PBLMS_TYPE_ID,
        REGID,
        COMMON_FILE_IDS,
        FILE_NO,
        FILE_NO_NUMBER,
        PBLMS_ID,
        PBLM_ID,
        OBJ_ID,
        OBJ_TYPE,
        INSP_GROUP_ID,
        PGUID,
        INSP_PBLM_TYPE,
        INSP_PBLM_CODE,
        INSP_PBLM_NAME,
        INSP_PBLM_DESC,
        INSP_ADD_DESC,
        PBLM_LONG,
        PBLM_LAT,
        IF_CASE_PBLM,
        INSP_PBLM_ORG_NAME,
        PBLM_PERS_NAME,
        INSP_PBLM_CATE,
        REL_BASIS,
        PBLM_STAT,
        REVI_OPIN,
        REVI_CONC,
        REVI_ORG_GUID,
        DATA_STAT,
        COLL_TIME,
        REC_PERS,
        NOTE,QUOTE,
        UP_TM,PBLM_NAME_BZ,CHECK_POINT_BZ,SN_NUM_BZ,PBLM_DESC_BZ,NEW_VILL_NAME,
        PBLM_REASON,
        PBLM_SGGTN,WTDST_NOTE,
        OBJ_CODE,OBJ_NAME,OBJ_AD_CODE,PROVINCIAL,BELONG_ORG_ID,BELONG_AD_CODE
        from bis_insp_pblm
        <include refid="page_where"/>
    </select>

    <sql id="page_search">
        <trim prefix="where" prefixOverrides="and | or ">
            <if test="persName != null and persName != ''">and PERS_NAME like '%${persName}%'</if>
            <if test="inspPblmCate != null and inspPblmCate != ''">and INSP_PBLM_CATE = #{inspPblmCate}</if>
            <if test="ifCasePblm != null and ifCasePblm != ''">and IF_CASE_PBLM = #{ifCasePblm}</if>
            <if test="dataStat != null and dataStat != ''">and DATA_STAT = #{dataStat}</if>
            <if test="inspPblmName != null and inspPblmName != ''">and INSP_PBLM_NAME like '%${inspPblmName}%'</if>
            <if test="pblmDescBz != null and pblmDescBz != ''">and PBLM_DESC_BZ like '%${pblmDescBz}%'</if>
            <if test="inspPblmsName != null and inspPblmsName != ''">and INSP_PBLMS_NAME like '%${inspPblmsName}%'</if>
            <if test="newVillName != null and newVillName != ''">and NEW_VILL_NAME = #{newVillName}</if>
            <if test="regid != null and regid != ''">and regid = #{regid}</if>
            <if test="rgstrId != null and rgstrId != ''">and regid = #{rgstrId}</if>
            <if test="objType != null and objType != ''">and obj_type = #{objType}</if>
            <if test="waterUserId != null and waterUserId != ''">and WATER_USER_ID = #{waterUserId}</if>
            <choose>
                <when test="villType == '34' ">
                    AND CWS_CODE IS NULL
                </when>
                <when test="villType == '33' ">
                    AND WATER_USER_ID IS NULL
                </when>
                <when test="villType != null and villType != '' ">and VILL_TYPE in (${villType})</when>
            </choose>
            <if test="villageCode != null and villageCode != ''">and village_Code = #{villageCode}</if>
            <if test="cwsCode != null and cwsCode != ''">and cws_Code = #{cwsCode}</if>

            <if test="code != null and code != ''">and CODE = #{code}</if>
            <if test="codes != null and codes != ''">and CODE in (${codes})</if>
            <if test="hasVedio != null and hasVedio != ''">and HAS_VEDIO = #{hasVedio}</if>
            <if test="recPers != null and recPers != ''">and REC_PERS = #{recPers}</if>
            <if test="objId != null and objId != ''">and OBJ_ID =#{objId}</if>
            <if test="objIdes != null and objIdes != ''">and OBJ_ID in (${objIdes})</if>
            <if test="nm != null and nm != ''">and nm like '%${nm}%'</if>
            <if test="startTime != null and startTime != ''">and COLL_TIME &gt;= TO_DATE(#{startTime},'YYYY-MM-DD')</if>
            <if test="findTime != null and findTime != ''">and COLL_TIME=TO_DATE(#{findTime},'YYYY-MM-DD')</if>
            <if test="endTime != null and endTime != ''">and COLL_TIME &lt; TO_DATE(#{endTime},'YYYY-MM-DD')+1</if>
            <if test="pblmStat != null and pblmStat != ''">and PBLM_Stat = #{pblmStat}</if>
            <if test="srcDesc != null and srcDesc != ''">and srcDesc like '%${srcDesc}%'</if>
            <if test="oides != null and oides != ''">and oid in (${oides})</if>
            <if test="adCode != null and adCode != ''">and CODE like '${adCode}%'</if>
            <if test="engScal != null and engScal != ''">and eng_Scal =#{engScal}</if>
            <if test="wagaScal != null and wagaScal != ''">and waga_Scal =#{wagaScal}</if>
            <if test="gateType != null and gateType != ''">and gate_Type =#{gateType}</if>
            <if test="sttp != null and sttp != ''">and sttp =#{sttp}</if>
            <if test="rePtype != null and rePtype != ''">and re_Ptype =#{rePtype}</if>
            <if test="pnm != null and pnm != ''">and pnm =#{pnm}</if>

            <!--and COLL_TIME &gt;= TO_DATE('2020-01-01','YYYY-MM-DD')-->

        </trim>

    </sql>
    <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="listOfPage" resultMap="bisInspPblmResultMap"
            parameterType="cn.com.goldenwater.dcproj.param.BisInspPblmParam">
        SELECT * FROM (
        SELECT
        P.*, O.NM, O.OBJ_AD_CODE AS AD_CODE,
        AAB.AD_NAME, AAB.AD_FULL_NAME, RP.PERS_NAME, A.PNM, T.NAME AS OBJ_TYPE_NAME
        FROM BIS_INSP_PBLM P
        JOIN BIS_INSP_ALL_OBJ O on P.OBJ_ID = O.OBJ_ID
        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 P.OBJ_TYPE = T.PTYPE
        <!--        JOIN (-->
        <!--        <foreach collection="rgsterTable" index="index" item="item">-->
        <!--            <if test="index != 0">-->
        <!--                UNION ALL-->
        <!--            </if>-->
        <!--            <choose>-->
        <!--                <when test="item == 'BIS_INSP_GRW' ">-->
        <!--                    SELECT OBJ_ID, INSP_STATE AS "STATE" FROM BIS_INSP_GRW WHERE INSP_STATE = '2'-->
        <!--                </when>-->
        <!--                <otherwise>-->
        <!--                    SELECT OBJ_ID, STATE FROM ${item} WHERE STATE = '2'-->
        <!--                </otherwise>-->
        <!--            </choose>-->
        <!--        </foreach>-->
        <!--        ) R ON R.OBJ_ID = o.OBJ_ID-->
        LEFT JOIN ATT_AD_BASE AAB on O.OBJ_AD_CODE = AAB.AD_CODE
        LEFT JOIN BIS_INSP_ALL_RLATION_PERS RP on P.REC_PERS = RP.GUID
        LEFT JOIN BIS_INSP_ALL A on A.ID = O.ID
        <if test='null != pblmStat and pblmStat != "" and "5" == pblmStat'>
            LEFT JOIN BIS_INSP_PBLM_PLIST Pl ON PL.PBLM_ID = P.PBLM_ID and PL.DATA_STAT='0'
        </if>
        <where>
            <if test="orgId != null and orgId != '' ">O.ID LIKE '___${orgId}%'</if>
            <if test='null != pblmStat and pblmStat != "" and "5" == pblmStat'>
                and PL.PBLM_OUT = '1'
            </if>
        </where>
        ) A
        <where>
            <if test="depm != null and depm.length > 0 ">
                AND OBJ_TYPE IN (SELECT TO_NUMBER(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="persName != null and persName != ''">and PERS_NAME like '%${persName}%'</if>
            <if test="inspPblmCate != null and inspPblmCate != ''">and INSP_PBLM_CATE = #{inspPblmCate}</if>
            <if test="ifCasePblm != null and ifCasePblm != ''">and IF_CASE_PBLM = #{ifCasePblm}</if>
            <if test="inspPblmName != null and inspPblmName != ''">and INSP_PBLM_NAME like '%${inspPblmName}%'</if>
            <if test="objType != null and objType != ''">and obj_type = #{objType}</if>
            <if test="objTypeName != null and objTypeName != ''">and OBJ_TYPE_NAME LIKE '%${objTypeName}%'</if>
            <if test="hasVedio != null and hasVedio != ''">and HAS_VEDIO = #{hasVedio}</if>
            <if test="objId != null and objId != ''">and OBJ_ID =#{objId}</if>
            <if test="nm != null and nm != ''">and nm like '%${nm}%'</if>
            <if test="startTime != null and startTime != ''">and COLL_TIME &gt;= TO_DATE(#{startTime},'YYYY-MM-DD')</if>
            <if test="endTime != null and endTime != ''">and COLL_TIME &lt; TO_DATE(#{endTime},'YYYY-MM-DD') + 1</if>
            <if test="sttm != null and sttm != '' and entm != null and entm != '' ">
                AND COLL_TIME &gt;= TO_DATE(#{sttm}, 'YYYY-MM-DD')
                AND COLL_TIME &lt; TO_DATE(#{entm}, 'YYYY-MM-DD') + 1
            </if>
            <if test="adCode != null and adCode != ''">and AD_CODE like '${adCode}%'</if>
            <!-- 福建省 福州市排除 平潭县 把平潭县 归属到 平潭综合实验区-->
            <if test='adCode != null and adCode == "3501" '>AND SUBSTR(AD_CODE, 0, 6) !='350128'</if>
            <if test='adCode != null and adCode == "3510" '>AND SUBSTR(AD_CODE, 0, 6) ='350128'</if>
            <if test="pnm != null and pnm != ''">and pnm like '%${pnm}%'</if>
            <if test="orgNm != null and orgNm != ''">and pnm like '%${orgNm}%'</if>
            <if test="adFullName != null and adFullName != ''">and REPLACE(AD_FULL_NAME, '-', '') LIKE
                '%${adFullName}%'
            </if>
        </where>
    </select>

    <select id="list" resultMap="bisInspPblmResultMap"
            parameterType="cn.com.goldenwater.dcproj.param.BisInspPblmParam">
        SELECT
        *
        FROM BIS_INSP_PBLM
        <where>
            <if test="inspPblmCate != null and inspPblmCate != ''">and INSP_PBLM_CATE = #{inspPblmCate}</if>
            <if test="ifCasePblm != null and ifCasePblm != ''">and IF_CASE_PBLM = #{ifCasePblm}</if>
            <if test="inspPblmName != null and inspPblmName != ''">and INSP_PBLM_NAME like '%${inspPblmName}%'</if>
            <if test="objType != null and objType != ''">and obj_type = #{objType}</if>
            <if test="objTypeName != null and objTypeName != ''">and OBJ_TYPE_NAME LIKE '%${objTypeName}%'</if>
            <if test="hasVedio != null and hasVedio != ''">and HAS_VEDIO = #{hasVedio}</if>
            <if test="objId != null and objId != ''">and OBJ_ID =#{objId}</if>
            <if test="startTime != null and startTime != ''">and COLL_TIME &gt;= TO_DATE(#{startTime},'YYYY-MM-DD')</if>
            <if test="endTime != null and endTime != ''">and COLL_TIME &lt; TO_DATE(#{endTime},'YYYY-MM-DD') + 1</if>
            <if test="adCode != null and adCode != ''">and AD_CODE like '${adCode}%'</if>
            <if test="pnm != null and pnm != ''">and pnm like '%${pnm}%'</if>
            <if test="orgNm != null and orgNm != ''">and pnm like '%${orgNm}%'</if>
            <if test="regid != null and regid != ''">and REGID like '%${regid}%'</if>
        </where>
    </select>

    <select id="findPlbmList" resultMap="bisInspPblmResultMap"
            parameterType="cn.com.goldenwater.dcproj.param.PlbmParam">
        select * from (
        select w.* from (
        select
        CASE WHEN P.VILLAGE_CODE IS NOT NULL THEN AD.AD_NAME
        WHEN P.OBJ_TYPE='3' THEN H.AD_NM
        WHEN P.CWS_CODE IS NOT NULL THEN E.CWS_NAME
        ELSE J.NM END AS srcDesc,
        e.cws_name,j.code,j.nm,j.id as oid,ad.ad_code,
        R.PERS_NAME, P.*,
        NVL(P.INSP_PBLM_NAME,P.PBLM_NAME_BZ) AS INSP_PBLMS_NAME, P.PBLM_DESC_BZ AS PBLM_DESC,P.CHECK_POINT_BZ AS CHECK_POINT,
        S.ATTACH,S.SN_NUM,S.SHEET,
        <if test='villType == "8" or villType=="9" '>u.nm as unit_nm,</if>
        <if test='villType=="0" or objType=="1"  or objType=="36"'>g.eng_scal,</if>
        <if test='villType=="7" or objType=="6"'>wa.eng_scal as waga_Scal,wa.gate_type,</if>
        <if test='villType=="5" or objType=="5"'>gr.sttp,</if>
        <if test='villType=="8" or villType=="9"'>BA.ptype as re_ptype,</if>
        (select  l.pnm from bis_insp_all l where l.id=substr(al.id,0,6)) as pnm,
        al.sttm, al.entm
        from BIS_INSP_PBLM p
        join (select * from bis_insp_all_obj o
        <where>
            <include refid="choseSql"/>
        </where>
        ) J ON P.OBJ_ID=J.OBJ_ID
        LEFT JOIN BIS_INSP_ALL_RLATION_PERS R ON P.REC_PERS=R.GUID
        LEFT JOIN OBJ_INSP_PBLMS S ON P.PBLMS_TYPE_ID=S.GUID
        LEFT JOIN ATT_CWS_BASE E ON J.CODE=E.CWS_CODE
        LEFT JOIN ATT_AD_X_BASE AD ON P.VILLAGE_CODE = AD.AD_CODE
        LEFT JOIN BIS_INSP_WTDST_TRACK H ON P.REGID = H.TRACK_ID
        <if test='villType=="8"'>left join BIS_INSP_KEY_REG_SEC_UNIT u on p.regid=u.id</if>
        <if test='villType=="9"'>left join att_mampu_info u on p.regid=u.id</if>
        <if test='villType=="0" or objType=="1"  or objType=="36"'>left join bis_insp_rsvr_rgstr g on
            j.obj_id=g.obj_id
        </if>
        <if test='villType=="7" or objType=="6"'>left join att_waga_rgstr wa on j.obj_id=wa.obj_id</if>
        <if test='villType=="5" or objType=="5"'>left join BIS_INSP_GRW gr on j.obj_id=gr.obj_id</if>
        <if test='villType=="8" or villType=="9"'>left join bis_insp_key_register BA on j.obj_id=BA.obj_id</if>
        <choose>
            <when test='objType=="17" '>left join BIS_INSP_OPRD_CSTR_PROJ_RGSTR BA on j.obj_id=BA.obj_id</when>
            <when test='objType=="18" '>left join ATT_JSKEJIAN_FLKDIS_RGSTR BA on j.obj_id=BA.obj_id</when>
            <when test='objType=="19" '>left join ATT_JSKEJIAN_WATER_RGSTR BA on j.obj_id=BA.obj_id</when>
            <when test='objType == "20"'>LEFT JOIN ATT_JSKEJIAN_ECOFRD_RGSTR BA on j.obj_id = BA.obj_id</when>
            <when test='objType == "22"'>LEFT JOIN BIS_INSP_RSML_RGSTR BA on j.obj_id = BA.obj_id</when>
            <when test='objType=="28"'>left join bis_insp_IRR_RGSTR BA on j.obj_id = BA.obj_id</when>
            <when test='objType=="62"'>left join BIS_INSP_CDEP_RGSTR BA on j.obj_id = BA.obj_id</when>
            <when test='objType=="67"'>left join BIS_INSP_BID_RGSTR BA on j.obj_id = BA.obj_id</when>
            <when test='objType=="68"'>left join BIS_INSP_RLRW_RGSTR BA on j.obj_id = BA.obj_id</when>
            <when test='objType=="69"'>left join BIS_INSP_DRDMP_RGSTR BA on j.obj_id = BA.obj_id</when>
            <when test='objType=="70"'>left join BIS_INSP_GROW_RGSTR BA on j.obj_id = BA.obj_id</when>
            <when test='objType=="73"'>left join BIS_INSP_HYST_RGSTR BA on j.obj_id = BA.obj_id</when>
            <when test='objType=="98"'>left join BIS_INSP_FUND_RGSTR BA on j.obj_id = BA.obj_id</when>
            <when test='objType == "99"'>LEFT JOIN BIS_INSP_KEYSAFQH_REGISTER BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='objType == "100"'>LEFT JOIN BIS_INSP_KEYCHKQH_REGISTER BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='objType == "101"'>LEFT JOIN BIS_INSP_SVWTQH BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='objType == "104"'>LEFT JOIN BIS_INSP_STSTN BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='objType == "105"'>LEFT JOIN BIS_INSP_QYMTEN BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='objType == "106"'>LEFT JOIN BIS_INSP_RSVRYN_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='objType == "108"'>LEFT JOIN BIS_INSP_XJVILL BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='objType == "110"'>LEFT JOIN BIS_INSP_FJWTENF BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='objType == "112"'>LEFT JOIN BIS_INSP_WIUJS_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='objType == "113"'>LEFT JOIN BIS_INSP_SWHSJS BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='objType == "115"'>LEFT JOIN BIS_INSP_RSVR_SD BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='objType == "116"'>LEFT JOIN BIS_INSP_HYSTP BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='objType == "119"'>LEFT JOIN BIS_INSP_FJQA BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='objType == "120"'>LEFT JOIN BIS_INSP_JSPWIU BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='objType == "126"'>LEFT JOIN BIS_INSP_JSPWIU2024 BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <otherwise>LEFT JOIN BIS_INSP_GNRL_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID</otherwise>
        </choose>
        left join bis_insp_all al on al.id=j.id
        ) w
        where 1=1
        <if test="tabType =='2'.toString()">
            and w.entm &lt;to_date(#{nowTime},'yyyy-MM-dd')+1
        </if>
        <if test="tabType =='1'.toString()">
            and w.entm &gt;=to_date(#{nowTime},'yyyy-MM-dd') and w.sttm &lt; to_date(#{nowTime},'yyyy-MM-dd')+1
        </if>
        <if test="timeType =='1'.toString()">
            and w.COLL_TIME >=to_date(#{nowTime},'yyyy-MM-dd')
        </if>
        <if test="timeType =='2'.toString()">
            and w.COLL_TIME &gt;=to_date(#{nowMonth},'yyyy-MM')
        </if>
        <if test="timeType =='3'.toString()">
            and w.COLL_TIME &gt;=to_date(#{nowYear},'yyyy')
        </if>
        <if test="inspPblmName !=null and inspPblmName !=''">
            and w.insp_pblm_name like '%${inspPblmName}%'
        </if>
        and REGEXP_LIKE(W.OID,'^('|| ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{presId}
        <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"/>
        ) else (select 'non' from dual) end) ||')')
        ) a
        <include refid="page_search"/>
        order by
        <if test="orderBy !=null and orderBy !=''">
            ${orderBy},
        </if>
        PBLM_ID
    </select>

    <!--精确获取每一个'+'后的数据-->
    <select id="findAddPlusPblm" resultMap="bisInspPblmResultMap">
        select j.nm,j.code,m.*,s.insp_pblms_name,s.check_point,r.pers_name from BIS_INSP_RSVR_PBLM p
        join bis_insp_pblm m on p.pblm_id=m.pblm_id and m.rec_pers=#{persId}
        left join BIS_INSP_ALL_OBJ j on m.obj_id=j.obj_id
        left join OBJ_INSP_PBLMS s on m.pblms_type_id=s.guid
        left join BIS_INSP_ALL_RLATION_PERS r on m.rec_pers=r.guid

    </select>

    <!--获取问题与文件关系-->
    <select id="findPblmBindFiles" resultType="cn.com.goldenwater.dcproj.model.PblmDoc"
            parameterType="cn.com.goldenwater.dcproj.param.DocType">
        select
        pblm_id,
        LISTAGG(f.file_path, ',') WITHIN GROUP(ORDER BY pblm_id) as filePathes,
        LISTAGG(f.file_ext, ',') WITHIN GROUP(ORDER BY pblm_id) as fileExtes
        from (
        select w.* from (
        select j.code,j.nm,j.id as oid,j.ptype,p.* from (
        select * from BIS_INSP_PBLM where 1=1
        <if test="hasVedio != null and hasVedio != ''">and HAS_VEDIO = #{hasVedio}</if>
        <if test="startTime != null and startTime != ''">and COLL_TIME &gt;= TO_DATE(#{startTime},'YYYY-MM-DD')</if>
        <if test="findTime != null and findTime != ''">and COLL_TIME=TO_DATE(#{findTime},'YYYY-MM-DD')</if>
        <if test="endTime != null and endTime != ''">and COLL_TIME &lt;= TO_DATE(#{endTime},'YYYY-MM-DD')+1</if>
        <if test="inspPblmCate != null and inspPblmCate != ''">and INSP_PBLM_CATE = #{inspPblmCate}</if>
        <if test="ifCasePblm != null and ifCasePblm != ''">and IF_CASE_PBLM = #{ifCasePblm}</if>
        <if test="pType != null and pType != ''">and obj_type = #{pType}</if>
        <if test="villType != null and villType != ''">and VILL_TYPE = #{villType}</if>
        <if test="problemType != null and problemType != ''">and problem_Type = #{problemType}</if>
        <if test="pblmIds != null and pblmIds != ''">and pblm_id in (${pblmIds})</if>
        ) p
        join (
        select * from BIS_INSP_ALL_OBJ o where o.ptype=#{pType}
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        ) w where
        REGEXP_LIKE(
        W.OID,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )
        else (select 'non' from dual) end)
        ||')')
        ) a ,gw_com_file f where a.pblm_id=f.biz_id group by pblm_id

    </select>
    <!--172工程，该方法需要修改对应的标准库-->
    <select id="findKeyRegPlbmList" resultType="cn.com.goldenwater.dcproj.model.KeyRegPblm"
            parameterType="cn.com.goldenwater.dcproj.param.KeyRegPatam">
        select * from (
        select w.* from (
        select j.code,j.nm,j.id as oid,j.ptype,r.PERS_NAME,p.*,
        u.nature,e.revi_org,l.pnm as org_nm,l.entm,l.sttm,j.id as org_id,u.sec_id,
        (select s.attach||'-'||s.sheet from obj_key_pblms s where s.id=QUALITY_TYPE_ID) as attach_qua,
        (select s.sn from obj_key_pblms s where s.id=QUALITY_TYPE_ID) as attach_qua_sn,
        (select s.attach_name from obj_key_pblms s where s.id=QUALITY_TYPE_ID) as attach_qua_name,
        (select s.attach||'-'||s.sheet from obj_key_pblms s where s.id=DEFECT_TYPE_ID) as attach_def,
        (select s.sn from obj_key_pblms s where s.id=DEFECT_TYPE_ID) as attach_def_sn,
        (select s.attach_name from obj_key_pblms s where s.id=DEFECT_TYPE_ID) as attach_def_name,
        (select s.attach||'-'||s.sheet from obj_key_pblms s where s.id=PBLMS_TYPE_ID) as attach_wg,
        (select s.sn from obj_key_pblms s where s.id=PBLMS_TYPE_ID) as attach_wg_sn,
        (select s.attach_name from obj_key_pblms s where s.id=PBLMS_TYPE_ID) as attach_wg_name,
        (select s.attach||'-'||s.sheet from obj_key_pblms s where s.id=PBLMS_ID) as attach_hetong,
        (select s.sn from obj_key_pblms s where s.id=PBLMS_ID) as attach_hetong_sn,
        (select s.attach_name from obj_key_pblms s where s.id=PBLMS_ID) as attach_hetong_name,
        (select s.attach||'-'||s.sheet from obj_key_pblms s where s.id=gongdi_bz_id) as attach_Gongdi,
        (select s.sn from obj_key_pblms s where s.id=gongdi_bz_id) as attach_Gongdi_sn,
        (select s.attach_name from obj_key_pblms s where s.id=gongdi_bz_id) as attach_Gongdi_name
        ,u.nm as unit_nm,
        k.rect_meas,k.guid,(case when k.data_stat is null then '0' else k.data_stat end) as check_stat,
        k.rect_conc,k.note as rectNote,k.org_guid,k.coll_time as rect_time,case when mo.lev='1' then mo.nm else '' end
        as first_Name,case when mo.lev='2' then mo.nm else '' end as second_Name,case when mo.lev='3' then mo.nm else ''
        end as third_Name,
        s.nm as sec_name
        from (select * from BIS_INSP_PBLM pb where pb.obj_type=#{pType}
        <if test="inspPblmCate != null and inspPblmCate != ''">and pb.INSP_PBLM_CATE = #{inspPblmCate}</if>
        <if test="ifCasePblm != null and ifCasePblm != ''">and pb.IF_CASE_PBLM = #{ifCasePblm}</if>
        <if test="hasVedio != null and hasVedio != ''">and pb.HAS_VEDIO = #{hasVedio}</if>
        <if test="inspPblmName != null and inspPblmName != ''">and pb.INSP_PBLM_NAME like '%${inspPblmName}%'</if>
        <if test="reviConc =='0'.toString()">
            <if test="startTime != null and startTime != ''">
                AND pb.COLL_TIME &gt;= TO_DATE(#{startTime},'YYYY-MM-DD')
            </if>
            <if test="findTime != null and findTime != ''">and pb.COLL_TIME=TO_DATE(#{findTime},'YYYY-MM-DD')</if>
            <if test="endTime != null and endTime != ''">
                AND pb.COLL_TIME &lt;= TO_DATE(#{endTime},'YYYY-MM-DD')+1
            </if>
        </if>

        ) p
        join (select * from BIS_INSP_ALL_OBJ o where o.pType=#{pType}
        <if test="objId != null and objId != ''">and OBJ_ID = #{objId}</if>
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        left join bis_insp_rect_fdbk k on p.pblm_id=k.pblm_id and k.guid=p.FDBK_ID
        <choose>
            <when test='pType == "99"'>
                left join BIS_INSP_KEYSAFQH_REGISTER_UNI u on p.regid=u.id
            </when>
            <when test='pType == "100"'>
                left join BIS_INSP_KEYCHKQH_UNIT u on p.regid=u.id
            </when>
            <otherwise>
                left join BIS_INSP_KEY_REG_SEC_UNIT u on p.regid=u.id
            </otherwise>
        </choose>

        left join (
        select revi_org , org_type from obj_key_pblm_rel l where l.org_type is not null group by revi_org,org_type order
        by org_type
        ) e on e.org_type=u.nature
        left join BIS_INSP_ALL l on l.id=j.id
        left join ATT_MAMPU_INFO mo on p.regid = mo.id
        <choose>
            <when test='pType == "99"'>
                left join BIS_INSP_KEYSAFQH_REGISTER_SEC s on u.sec_id = s.id
            </when>
            <when test='pType == "100"'>
                left join BIS_INSP_KEYCHKQH_SECTION s on u.sec_id = s.id
            </when>
            <otherwise>
                left join BIS_INSP_KEY_REGISTER_SECTION s on u.sec_id = s.id
            </otherwise>
        </choose>
        ) w where w.ptype=#{pType}
        <if test="tabType =='2'.toString()">
            and w.entm &lt;to_date(#{nowTime},'yyyy-MM-dd')+1
        </if>
        <if test="tabType =='1'.toString()">
            and w.entm &gt;=to_date(#{nowTime},'yyyy-MM-dd')
        </if>
        and
        REGEXP_LIKE(
        W.OID,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )
        else (select 'non' from dual) end)
        ||')')
        ) a where 1=1
        <if test="nm != null and nm != ''">and nm like '%${nm}%'</if>
        <if test="orgNm != null and orgNm != ''">and org_nm like '%${orgNm}%'</if>
        <if test="problemType != null and problemType != ''">and problem_Type = #{problemType}</if>
        <if test="persName != null and persName != ''">and PERS_NAME like '%${persName}%'</if>
        <if test="reviConc != null and reviConc != ''">and REVI_CONC = #{reviConc}</if>
        <if test="regId != null and regId != ''">and regId= #{regId}</if>
        <if test="villType != null and villType != ''">and vill_type = #{villType}</if>
        <if test="codes != null and codes != ''">and CODE in (${codes})</if>
        <if test="recPers !=null and recPers !=''">and rec_pers=#{recPers}</if>
        <if test="rectConc != null and rectConc != ''">and RECT_CONC in (${rectConc})</if>
        <if test="orgIds != null and orgIds != ''">and org_id in (${orgIds})</if>
        <if test="inspPblmType != null and inspPblmType != ''">and INSP_PBLM_TYPE = #{inspPblmType}</if>
        <if test="secId != null and secId != ''">and SEC_ID = #{secId}</if>
        <if test="state != null and state != ''">and STATE = #{state}</if>
        <if test="reviConc !='0'.toString()">
            <if test="startTime != null and startTime != ''">
                AND RECT_TIME &gt;= TO_DATE(#{startTime},'YYYY-MM-DD')
            </if>
            <if test="findTime != null and findTime != ''">and RECT_TIME=TO_DATE(#{findTime},'YYYY-MM-DD')</if>
            <if test="endTime != null and endTime != ''">
                AND RECT_TIME &lt; TO_DATE(#{endTime},'YYYY-MM-DD')+1
            </if>
        </if>
        <choose>
            <when test="orderBy !=null and orderBy !=''">
                ${orderBy}
            </when>
            <otherwise>
                order by coll_time desc
            </otherwise>
        </choose>

    </select>

    <select id="findJswaPlbmList" parameterType="cn.com.goldenwater.dcproj.param.PrdParam"
            resultMap="bisInspPblmResultMap">
        select * from (
        select w.* from (
        select ba.name ,j.code,j.nm,j.id as
        oid,j.ptype,r.PERS_NAME,l.sttm,l.entm,p.*,b.ad_full_name,
        b.ad_code,l.pnm as org_nm,j.id as org_id
        from (select * from BIS_INSP_PBLM pb where pb.obj_type=19
        <if test="inspPblmCate != null and inspPblmCate != ''">and pb.INSP_PBLM_CATE = #{inspPblmCate}</if>
        <if test="ifCasePblm != null and ifCasePblm != ''">and pb.IF_CASE_PBLM = #{ifCasePblm}</if>
        <if test="hasVedio != null and hasVedio != ''">and pb.HAS_VEDIO = #{hasVedio}</if>
        <if test="startTime != null and startTime != ''">
            AND pb.COLL_TIME &gt;= TO_DATE(#{startTime},'YYYY-MM-DD')
        </if>
        <if test="findTime != null and findTime != ''">and pb.COLL_TIME=TO_DATE(#{findTime},'YYYY-MM-DD')</if>
        <if test="endTime != null and endTime != ''">
            AND pb.COLL_TIME &lt; TO_DATE(#{endTime},'YYYY-MM-DD')+1
        </if>
        <if test="inspPblmName != null and inspPblmName != ''">and pb.INSP_PBLM_NAME like '%${inspPblmName}%'</if>
        ) p
        left join (select * from BIS_INSP_ALL_OBJ o where o.pType=19
        <if test="objId != null and objId != ''">and o.OBJ_ID = #{objId}</if>
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        left join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        left join ATT_JSKEJIAN_WATER_BASE ba on ba.id=j.code
        left join att_ad_x_base b on ba.ad_code=b.ad_code
        left join BIS_INSP_ALL l on l.id=j.id
        ) w where w.pType=19
        <if test="tabType =='2'.toString()">
            and w.entm &lt;to_date(#{nowTime},'yyyy-MM-dd')+1
        </if>
        <if test="tabType =='1'.toString()">
            and w.entm &gt;=to_date(#{nowTime},'yyyy-MM-dd')
        </if>
        and
        REGEXP_LIKE(
        W.OID,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )
        else (select 'non' from dual) end)
        ||')')
        ) a where 1=1
        <if test="orgNm != null and orgNm != ''">and org_nm like '%${orgNm}%'</if>
        <if test="name != null and name != ''">and name like '%${name}%'</if>
        <if test="adFullName != null and adFullName != ''">and ad_full_name like '%${adFullName}%'</if>
        <if test="persName != null and persName != ''">and PERS_NAME like '%${persName}%'</if>
        <if test="inspGroupId != null and inspGroupId != ''">and insp_Group_Id = #{inspGroupId}</if>
        <if test="codes != null and codes != ''">and CODE in (${codes})</if>
        <if test="adCodes != null and adCodes != ''">and AD_CODE in (${adCodes})</if>
        <if test="orgIds != null and orgIds != ''">and org_id in (${orgIds})</if>

        <if test="inspPblmType != null and inspPblmType != ''">and INSP_PBLM_TYPE = #{inspPblmType}</if>
        order by ad_code asc,coll_time desc
    </select>

    <select id="findJsflPlbmList" parameterType="cn.com.goldenwater.dcproj.param.PrdParam"
            resultMap="bisInspPblmResultMap">
        select * from (
        select w.* from (
        select ba.name ,j.code,j.nm,j.id as
        oid,j.ptype,r.PERS_NAME,p.*,b.ad_full_name,
        b.ad_code,l.pnm as org_nm,l.sttm,l.entm,j.id as org_id
        from (select * from BIS_INSP_PBLM pb where pb.obj_type=18
        <if test="inspPblmCate != null and inspPblmCate != ''">and pb.INSP_PBLM_CATE = #{inspPblmCate}</if>
        <if test="ifCasePblm != null and ifCasePblm != ''">and pb.IF_CASE_PBLM = #{ifCasePblm}</if>
        <if test="hasVedio != null and hasVedio != ''">and pb.HAS_VEDIO = #{hasVedio}</if>
        <if test="startTime != null and startTime != ''">
            AND pb.COLL_TIME &gt;= TO_DATE(#{startTime},'YYYY-MM-DD')
        </if>
        <if test="findTime != null and findTime != ''">and pb.COLL_TIME=TO_DATE(#{findTime},'YYYY-MM-DD')</if>
        <if test="endTime != null and endTime != ''">
            AND pb.COLL_TIME &lt; TO_DATE(#{endTime},'YYYY-MM-DD')+1
        </if>
        <if test="inspPblmName != null and inspPblmName != ''">and pb.INSP_PBLM_NAME like '%${inspPblmName}%'</if>
        ) p
        left join (select * from BIS_INSP_ALL_OBJ o where o.pType=18
        <if test="objId != null and objId != ''">and o.OBJ_ID = #{objId}</if>
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        left join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        left join ATT_JSKEJIAN_FLKDIS_BASE ba on ba.id=j.code
        left join att_ad_x_base b on ba.ad_code=b.ad_code
        left join BIS_INSP_ALL l on l.id=j.id
        ) w where w.pType=18
        <if test="tabType =='2'.toString()">
            and w.entm &lt;to_date(#{nowTime},'yyyy-MM-dd')+1
        </if>
        <if test="tabType =='1'.toString()">
            and w.entm &gt;=to_date(#{nowTime},'yyyy-MM-dd')
        </if>
        and
        REGEXP_LIKE(
        W.OID,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )
        else (select 'non' from dual) end)
        ||')')
        ) a where 1=1
        <if test="orgNm != null and orgNm != ''">and org_nm like '%${orgNm}%'</if>
        <if test="name != null and name != ''">and name like '%${name}%'</if>
        <if test="adFullName != null and adFullName != ''">and ad_full_name like '%${adFullName}%'</if>
        <if test="persName != null and persName != ''">and PERS_NAME like '%${persName}%'</if>
        <if test="inspGroupId != null and inspGroupId != ''">and insp_Group_Id = #{inspGroupId}</if>
        <if test="codes != null and codes != ''">and CODE in (${codes})</if>
        <if test="adCodes != null and adCodes != ''">and AD_CODE in (${adCodes})</if>
        <if test="orgIds != null and orgIds != ''">and org_id in (${orgIds})</if>

        <if test="inspPblmType != null and inspPblmType != ''">and INSP_PBLM_TYPE = #{inspPblmType}</if>
        order by ad_code asc,coll_time desc
    </select>

    <select id="findPrdPlbmList" parameterType="cn.com.goldenwater.dcproj.param.PrdParam"
            resultMap="bisInspPblmResultMap">
        select * from (
        select w.* from (
        select ba.name ,j.code,j.nm,l.sttm,l.entm,j.id as
        oid,j.ptype,r.PERS_NAME,p.*,s.insp_pblms_name,s.check_point,s.attach,s.sheet,s.sn_num,b.ad_full_name,
        b.ad_code,l.pnm as org_nm,j.id as org_id
        from (select * from BIS_INSP_PBLM pb where pb.obj_type=17
        <if test="inspPblmCate != null and inspPblmCate != ''">and pb.INSP_PBLM_CATE = #{inspPblmCate}</if>
        <if test="ifCasePblm != null and ifCasePblm != ''">and pb.IF_CASE_PBLM = #{ifCasePblm}</if>
        <if test="hasVedio != null and hasVedio != ''">and pb.HAS_VEDIO = #{hasVedio}</if>
        <if test="startTime != null and startTime != ''">
            AND pb.COLL_TIME &gt;= TO_DATE(#{startTime},'YYYY-MM-DD')
        </if>
        <if test="findTime != null and findTime != ''">and pb.COLL_TIME=TO_DATE(#{findTime},'YYYY-MM-DD')</if>
        <if test="endTime != null and endTime != ''">
            AND pb.COLL_TIME &lt; TO_DATE(#{endTime},'YYYY-MM-DD')+1
        </if>
        <if test="inspPblmName != null and inspPblmName != ''">and pb.INSP_PBLM_NAME like '%${inspPblmName}%'</if>
        ) p
        left join (select * from BIS_INSP_ALL_OBJ o where o.pType=17
        <if test="objId != null and objId != ''">and o.OBJ_ID = #{objId}</if>
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        left join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        left join OBJ_INSP_PBLMS s on p.pblms_type_id=s.guid
        left join ATT_PRD_CSTR_PROJ_BASE ba on ba.id=j.code
        left join att_ad_x_base b on ba.ad_code=b.ad_code
        left join BIS_INSP_ALL l on l.id=j.id
        ) w where w.pType=17
        <if test="tabType =='2'.toString()">
            and w.entm &lt;to_date(#{nowTime},'yyyy-MM-dd')+1
        </if>
        <if test="tabType =='1'.toString()">
            and w.entm &gt;=to_date(#{nowTime},'yyyy-MM-dd')
        </if>
        and
        REGEXP_LIKE(
        W.OID,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )
        else (select 'non' from dual) end)
        ||')')
        ) a where 1=1
        <if test="orgNm != null and orgNm != ''">and org_nm like '%${orgNm}%'</if>
        <if test="name != null and name != ''">and name like '%${name}%'</if>
        <if test="adFullName != null and adFullName != ''">and ad_full_name like '%${adFullName}%'</if>
        <if test="persName != null and persName != ''">and PERS_NAME like '%${persName}%'</if>
        <if test="inspGroupId != null and inspGroupId != ''">and insp_Group_Id = #{inspGroupId}</if>
        <if test="codes != null and codes != ''">and CODE in (${codes})</if>
        <if test="adCodes != null and adCodes != ''">and AD_CODE in (${adCodes})</if>
        <if test="orgIds != null and orgIds != ''">and org_id in (${orgIds})</if>

        <if test="inspPblmType != null and inspPblmType != ''">and INSP_PBLM_TYPE = #{inspPblmType}</if>
        order by ad_code asc,coll_time desc
    </select>

    <select id="findEcofrdPlbmList" parameterType="cn.com.goldenwater.dcproj.param.PrdParam"
            resultMap="bisInspPblmResultMap">
        select * from (
        select w.* from (
        select ba.name ,j.code,j.nm,l.sttm,l.entm,j.id as
        oid,j.ptype,r.PERS_NAME,p.*,s.insp_pblms_name,s.check_point,s.attach,s.sheet,s.sn_num,b.ad_full_name,
        b.ad_code,l.pnm as org_nm,j.id as org_id
        from (select * from BIS_INSP_PBLM pb where pb.obj_type=20
        <if test="inspPblmCate != null and inspPblmCate != ''">and pb.INSP_PBLM_CATE = #{inspPblmCate}</if>
        <if test="ifCasePblm != null and ifCasePblm != ''">and pb.IF_CASE_PBLM = #{ifCasePblm}</if>
        <if test="hasVedio != null and hasVedio != ''">and pb.HAS_VEDIO = #{hasVedio}</if>
        <if test="startTime != null and startTime != ''">
            AND pb.COLL_TIME &gt;= TO_DATE(#{startTime},'YYYY-MM-DD')
        </if>
        <if test="findTime != null and findTime != ''">and pb.COLL_TIME=TO_DATE(#{findTime},'YYYY-MM-DD')</if>
        <if test="endTime != null and endTime != ''">
            AND pb.COLL_TIME &lt; TO_DATE(#{endTime},'YYYY-MM-DD')+1
        </if>
        <if test="inspPblmName != null and inspPblmName != ''">and pb.INSP_PBLM_NAME like '%${inspPblmName}%'</if>
        ) p
        left join (select * from BIS_INSP_ALL_OBJ o where o.pType=20
        <if test="objId != null and objId != ''">and o.OBJ_ID = #{objId}</if>
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        left join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        left join OBJ_INSP_PBLMS s on p.pblms_type_id=s.guid
        left join ATT_PRD_CSTR_PROJ_BASE ba on ba.id=j.code
        left join att_ad_x_base b on ba.ad_code=b.ad_code
        left join BIS_INSP_ALL l on l.id=j.id
        ) w where w.pType=20
        <if test="tabType =='2'.toString()">
            and w.entm &lt;to_date(#{nowTime},'yyyy-MM-dd')+1
        </if>
        <if test="tabType =='1'.toString()">
            and w.entm &gt;=to_date(#{nowTime},'yyyy-MM-dd')
        </if>
        and
        REGEXP_LIKE(
        W.OID,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )
        else (select 'non' from dual) end)
        ||')')
        ) a where 1=1
        <if test="orgNm != null and orgNm != ''">and org_nm like '%${orgNm}%'</if>
        <if test="name != null and name != ''">and name like '%${name}%'</if>
        <if test="adFullName != null and adFullName != ''">and ad_full_name like '%${adFullName}%'</if>
        <if test="persName != null and persName != ''">and PERS_NAME like '%${persName}%'</if>
        <if test="inspGroupId != null and inspGroupId != ''">and insp_Group_Id = #{inspGroupId}</if>
        <if test="codes != null and codes != ''">and CODE in (${codes})</if>
        <if test="adCodes != null and adCodes != ''">and AD_CODE in (${adCodes})</if>
        <if test="orgIds != null and orgIds != ''">and org_id in (${orgIds})</if>

        <if test="inspPblmType != null and inspPblmType != ''">and INSP_PBLM_TYPE = #{inspPblmType}</if>
        order by ad_code asc,coll_time desc
    </select>

    <!--水闸问题列表-->
    <select id="findWagaPlbmList" resultMap="bisInspPblmResultMap"
            parameterType="cn.com.goldenwater.dcproj.param.WagaParam">
        select * from (
        select w.* from (
        select ba.gate_name ,l.sttm,l.entm,j.code,j.nm,j.id as
        oid,j.ptype,r.PERS_NAME,p.*,s.insp_pblms_name,s.check_point,s.attach,s.sheet,s.sn_num,b.ad_full_name,
        b.ad_code,l.pnm as org_nm,j.id as org_id
        from (select * from BIS_INSP_PBLM pb where pb.obj_type=6
        <if test="inspPblmCate != null and inspPblmCate != ''">and pb.INSP_PBLM_CATE = #{inspPblmCate}</if>
        <if test="ifCasePblm != null and ifCasePblm != ''">and pb.IF_CASE_PBLM = #{ifCasePblm}</if>
        <if test="hasVedio != null and hasVedio != ''">and pb.HAS_VEDIO = #{hasVedio}</if>
        <if test="startTime != null and startTime != ''">
            AND pb.COLL_TIME &gt;= TO_DATE(#{startTime},'YYYY-MM-DD')
        </if>
        <if test="findTime != null and findTime != ''">and pb.COLL_TIME=TO_DATE(#{findTime},'YYYY-MM-DD')</if>
        <if test="endTime != null and endTime != ''">
            AND pb.COLL_TIME &lt; TO_DATE(#{endTime},'YYYY-MM-DD')+1
        </if>
        <if test="inspPblmName != null and inspPblmName != ''">and pb.INSP_PBLM_NAME like '%${inspPblmName}%'</if>
        ) p
        left join (select * from BIS_INSP_ALL_OBJ o where o.pType=6
        <if test="objId != null and objId != ''">and o.OBJ_ID = #{objId}</if>
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        left join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        left join OBJ_INSP_PBLMS s on p.pblms_type_id=s.guid
        left join att_waga_base ba on ba.obj_code=j.code
        left join att_ad_x_base b on ba.ad_code=b.ad_code
        left join BIS_INSP_ALL l on l.id=j.id
        ) w where w.pType=6
        <if test="tabType =='2'.toString()">
            and w.entm &lt;to_date(#{nowTime},'yyyy-MM-dd')+1
        </if>
        <if test="tabType =='1'.toString()">
            and w.entm &gt;=to_date(#{nowTime},'yyyy-MM-dd')
        </if>
        and
        REGEXP_LIKE(
        W.OID,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )
        else (select 'non' from dual) end)
        ||')')
        ) a where 1=1
        <if test="nm != null and nm != ''">and nm like '%${nm}%'</if>
        <if test="orgNm != null and orgNm != ''">and org_nm like '%${orgNm}%'</if>
        <if test="gateName != null and gateName != ''">and gate_name like '%${gateName}%'</if>
        <if test="adFullName != null and adFullName != ''">and ad_full_name like '%${adFullName}%'</if>
        <if test="persName != null and persName != ''">and PERS_NAME like '%${persName}%'</if>
        <if test="inspGroupId != null and inspGroupId != ''">and insp_Group_Id = #{inspGroupId}</if>
        <if test="codes != null and codes != ''">and CODE in (${codes})</if>
        <if test="recPers !=null and recPers !=''">
            and rec_pers=#{recPers}
        </if>
        <if test="adCodes != null and adCodes != ''">and AD_CODE in (${adCodes})</if>
        <if test="orgIds != null and orgIds != ''">and org_id in (${orgIds})</if>
        <if test="state != null and state != ''">and STATE = #{state}</if>
        <if test="inspPblmType != null and inspPblmType != ''">and INSP_PBLM_TYPE = #{inspPblmType}</if>
        order by ad_code asc,coll_time desc
    </select>
    <select id="findReverPlbmList" resultMap="bisInspPblmResultMap"
            parameterType="cn.com.goldenwater.dcproj.param.ReverParam">
        select * from (
        select w.* from (
        select
        J.NM RS_NAME, R.PERS_NAME, B.AD_FULL_NAME,B.AD_CODE,
        J.CODE,J.NM,J.ID AS OID, J.ID AS ORG_ID, J.PTYPE,
        P.*, P.INSP_PBLM_NAME AS INSP_PBLMS_NAME,P.PBLM_DESC_BZ AS PBLM_DESC, P.CHECK_POINT_BZ AS CHECK_POINT,
        S.ATTACH,S.SHEET,S.SN_NUM,S.OLD_ATTACH,S.OLD_SN,
        L.PNM AS ORG_NM, L.STTM,L.ENTM
        from (select * from BIS_INSP_PBLM pb where pb.obj_type=#{objType}
        <if test="inspPblmCate != null and inspPblmCate != ''">and pb.INSP_PBLM_CATE = #{inspPblmCate}</if>
        <if test="ifCasePblm != null and ifCasePblm != ''">and pb.IF_CASE_PBLM = #{ifCasePblm}</if>
        <if test="hasVedio != null and hasVedio != ''">and pb.HAS_VEDIO = #{hasVedio}</if>
        <if test="startTime != null and startTime != ''">AND pb.COLL_TIME &gt;= TO_DATE(#{startTime},'YYYY-MM-DD')</if>
        <if test="findTime != null and findTime != ''">and pb.COLL_TIME=TO_DATE(#{findTime},'YYYY-MM-DD')</if>
        <if test="endTime != null and endTime != ''">AND pb.COLL_TIME &lt; TO_DATE(#{endTime},'YYYY-MM-DD')+1</if>
        <if test="inspPblmName != null and inspPblmName != ''">and pb.INSP_PBLM_NAME like '%${inspPblmName}%'</if>
        ) p
        join (select * from BIS_INSP_ALL_OBJ o where o.pType=#{objType}
        <if test="objId != null and objId != ''">and o.OBJ_ID = #{objId}</if>
        <include refid="choseSql"/>
        ) J ON P.OBJ_ID=J.OBJ_ID
        LEFT JOIN BIS_INSP_ALL_RLATION_PERS R ON P.REC_PERS=R.GUID
        LEFT JOIN OBJ_INSP_PBLMS S ON P.PBLMS_TYPE_ID=S.GUID
        LEFT JOIN ATT_AD_X_BASE B ON J.OBJ_AD_CODE=B.AD_CODE
        LEFT JOIN BIS_INSP_ALL L ON L.ID=J.ID
        ) W
        WHERE W.PTYPE = #{objType}
        <if test="tabType =='2'.toString()">
            and w.entm &lt;to_date(#{nowTime},'yyyy-MM-dd')+1
        </if>
        <if test="tabType =='1'.toString()">and w.entm &gt;=to_date(#{nowTime},'yyyy-MM-dd')</if>
        and
        REGEXP_LIKE(
        W.OID,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )
        else (select 'non' from dual) end)
        ||')')
        ) a
        <where>
            <if test="nm != null and nm != ''">and nm like '%${nm}%'</if>
            <if test="orgNm != null and orgNm != ''">and org_nm like '%${orgNm}%'</if>
            <if test="rsName != null and rsName != ''">and rs_name like '%${rsName}%'</if>
            <if test="adFullName != null and adFullName != ''">and ad_full_name like '%${adFullName}%'</if>
            <if test="persName != null and persName != ''">and PERS_NAME like '%${persName}%'</if>
            <if test="codes != null and codes != ''">and CODE in (${codes})</if>
            <if test="recPers !=null and recPers !=''">and rec_pers=#{recPers}</if>
            <if test="adCodes != null and adCodes != ''">and AD_CODE in (${adCodes})</if>
            <if test="orgIds != null and orgIds != ''">and org_id in (${orgIds})</if>
            <if test="state != null and state != ''">and STATE = #{state}</if>
            <if test="inspPblmType != null and inspPblmType != ''">and INSP_PBLM_TYPE = #{inspPblmType}</if>
        </where>
        order by ad_code asc, CODE asc , coll_time desc
    </select>

    <select id="findVillType" resultType="java.lang.String">
        select distinct m.insp_pblm_name from BIS_INSP_PBLM m,bis_insp_all_obj o where m.obj_id=o.obj_id and
        m.obj_type=2
        <include refid="choseSql"/>
    </select>

    <select id="findVillPlbmList" resultMap="bisInspPblmResultMap"
            parameterType="cn.com.goldenwater.dcproj.param.VillParam">
        select * from (
        select w.* from (
        select j.code,j.nm,j.id as
        oid,j.ptype,r.PERS_NAME,p.*,s.insp_pblms_name,s.check_point,s.attach,s.sheet,s.sn_num,b.ad_full_name,b.ad_code,l.pnm
        as org_nm,j.id as org_id
        from (select * from BIS_INSP_PBLM pb where pb.obj_type=2
        <if test="inspPblmCate != null and inspPblmCate != ''">and pb.INSP_PBLM_CATE = #{inspPblmCate}</if>
        <if test="ifCasePblm != null and ifCasePblm != ''">and pb.IF_CASE_PBLM = #{ifCasePblm}</if>
        <if test="hasVedio != null and hasVedio != ''">and pb.HAS_VEDIO = #{hasVedio}</if>
        <if test="startTime != null and startTime != ''">
            AND pb.COLL_TIME &gt;= TO_DATE(#{startTime},'YYYY-MM-DD')
        </if>
        <if test="findTime != null and findTime != ''">and pb.COLL_TIME=TO_DATE(#{findTime},'YYYY-MM-DD')</if>
        <if test="endTime != null and endTime != ''">
            AND pb.COLL_TIME &lt;TO_DATE(#{endTime},'YYYY-MM-DD')+1
        </if>
        <if test="inspPblmName != null and inspPblmName != ''">and pb.INSP_PBLM_NAME like '%${inspPblmName}%'</if>
        <if test="villType != null and villType != '' and villType != '0'.toString()">and pb.vill_Type =#{villType}</if>
        <if test="villageCode != null and villageCode != ''">and pb.village_Code =#{villageCode}</if>
        <if test="cwsCode != null and cwsCode != ''">and pb.cws_code =#{cwsCode}</if>
        <if test="engSurId != null and engSurId != ''">and pb.REGID =#{engSurId}</if>
        ) p
        join (select * from BIS_INSP_ALL_OBJ o where o.pType=2
        <if test="objId != null and objId != ''">and OBJ_ID = #{objId}</if>
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        left join OBJ_INSP_PBLMS s on p.pblms_type_id=s.guid
        left join BIS_INSP_VILL_RGSTR ba on ba.obj_id=j.obj_id
        left join att_ad_x_base b on j.code=b.ad_code
        left join BIS_INSP_ALL l on l.id=j.id
        ) w where w.ptype=2 and
        REGEXP_LIKE(
        W.OID,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )
        else (select 'non' from dual) end)
        ||')')
        ) a where 1=1
        <if test="nm != null and nm != ''">and nm like '%${nm}%'</if>
        <if test="orgNm != null and orgNm != ''">and org_nm like '%${orgNm}%'</if>
        <if test="adFullName != null and adFullName != ''">and ad_full_name like '%${adFullName}%'</if>
        <if test="persName != null and persName != ''">and PERS_NAME like '%${persName}%'</if>
        <if test="codes != null and codes != ''">and CODE in (${codes})</if>

        <if test="recPers !=null and recPers !=''">
            and rec_pers=#{recPers}
        </if>
        <if test="adCodes != null and adCodes != ''">and AD_CODE in (${adCodes})</if>
        <if test="orgIds != null and orgIds != ''">and org_id in (${orgIds})</if>

        <if test="inspPblmType != null and inspPblmType != ''">and INSP_PBLM_TYPE = #{inspPblmType}</if>

        order by ad_code asc,coll_time desc
    </select>

    <!--地下水-->
    <select id="findWaterPlbmList" resultMap="bisInspPblmResultMap"
            parameterType="cn.com.goldenwater.dcproj.param.UnderWaterParam">
        select * from (
        select w.* from (
        select j.code,j.nm,j.id as oid,l.sttm,l.entm,j.ptype,r.PERS_NAME,p.*,e.ad_full_name as
        ad_full_name,s.insp_pblms_name,s.check_point,s.attach,s.sheet,s.sn_num,b.stcd as ad_code,l.pnm as org_nm,j.id as
        org_id
        from (select * from BIS_INSP_PBLM pb where pb.obj_type=5
        <if test="inspPblmCate != null and inspPblmCate != ''">and pb.INSP_PBLM_CATE = #{inspPblmCate}</if>
        <if test="ifCasePblm != null and ifCasePblm != ''">and pb.IF_CASE_PBLM = #{ifCasePblm}</if>
        <if test="hasVedio != null and hasVedio != ''">and pb.HAS_VEDIO = #{hasVedio}</if>
        <if test="startTime != null and startTime != ''">
            AND pb.COLL_TIME &gt;= TO_DATE(#{startTime},'YYYY-MM-DD')
        </if>
        <if test="findTime != null and findTime != ''">and pb.COLL_TIME=TO_DATE(#{findTime},'YYYY-MM-DD')</if>
        <if test="endTime != null and endTime != ''">
            AND pb.COLL_TIME &lt;= TO_DATE(#{endTime},'YYYY-MM-DD')+1
        </if>
        <if test="inspPblmName != null and inspPblmName != ''">and pb.INSP_PBLM_NAME like '%${inspPblmName}%'</if>

        ) p
        join (select * from BIS_INSP_ALL_OBJ o where o.pType=5
        <if test="objId != null and objId != ''">and OBJ_ID = #{objId}</if>
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        left join OBJ_INSP_PBLMS s on p.pblms_type_id=s.guid
        left join BIS_INSP_GRW t on t.obj_id=p.obj_id
        left join att_grw_base b on t.stcd=b.stcd and j.code=b.stcd
        left join att_ad_x_base e on b.adcd=e.ad_code
        left join BIS_INSP_ALL l on l.id=j.id
        ) w where w.ptype='5'
        <if test="tabType =='2'.toString()">
            and w.entm &lt;to_date(#{nowTime},'yyyy-MM-dd')+1
        </if>
        <if test="tabType =='1'.toString()">
            and w.entm &gt;=to_date(#{nowTime},'yyyy-MM-dd')
        </if>
        and
        REGEXP_LIKE(
        W.OID,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )
        else (select 'non' from dual) end)
        ||')')
        ) a where 1=1
        <if test="persName != null and persName != ''">and PERS_NAME like '%${persName}%'</if>
        <if test="orgNm != null and orgNm != ''">and org_nm like '%${orgNm}%'</if>
        <if test="codes != null and codes != ''">and CODE in (${codes})</if>

        <if test="adCodes != null and adCodes != ''">and AD_CODE in (${adCodes})</if>
        <if test="orgIds != null and orgIds != ''">and org_id in (${orgIds})</if>

        <if test="recPers !=null and recPers !=''">
            and rec_pers=#{recPers}
        </if>
        <if test="nm != null and nm != ''">and nm like '%${nm}%'</if>
        <if test="adFullName != null and adFullName != ''">and ad_full_name like '%${adFullName}%'</if>

        order by ad_code asc,coll_time desc
    </select>


    <select id="findTrackPlbmList" resultMap="bisInspPblmResultMap"
            parameterType="cn.com.goldenwater.dcproj.param.TrackParam">
        select * from (
        select w.* from (
        select j.code,j.nm,j.id as oid,j.ptype,l.sttm,l.entm,r.PERS_NAME,p.*,
        b.ad_full_name,s.insp_pblms_name,s.pblm_desc,s.check_point,s.attach,s.sheet,s.sn_num,b.ad_code,l.pnm
        as org_nm,j.id as org_id
        from (select * from BIS_INSP_PBLM pb where pb.obj_type = 3
        <if test="inspPblmCate != null and inspPblmCate != ''">and pb.INSP_PBLM_CATE = #{inspPblmCate}</if>
        <if test="ifCasePblm != null and ifCasePblm != ''">and pb.IF_CASE_PBLM = #{ifCasePblm}</if>
        <if test="hasVedio != null and hasVedio != ''">and pb.HAS_VEDIO = #{hasVedio}</if>
        <if test="startTime != null and startTime != ''">AND pb.COLL_TIME &gt;= TO_DATE(#{startTime},'YYYY-MM-DD')</if>
        <if test="findTime != null and findTime != ''">and pb.COLL_TIME=TO_DATE(#{findTime},'YYYY-MM-DD')</if>
        <if test="endTime != null and endTime != ''">AND pb.COLL_TIME &lt; TO_DATE(#{endTime},'YYYY-MM-DD')+1</if>
        <if test="inspPblmName != null and inspPblmName != ''">and pb.INSP_PBLM_NAME like '%${inspPblmName}%'</if>
        ) p
        join (select * from BIS_INSP_ALL_OBJ o where o.pType=3
        <if test="objId != null and objId != ''">and OBJ_ID = #{objId}</if>
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        left join OBJ_INSP_PBLMS s on p.pblms_type_id=s.guid
        left join BIS_INSP_WTDST t on t.wtdst_id = j.code
        left join att_ad_x_base b on t.loc_ad=b.ad_code
        left join BIS_INSP_ALL l on l.id=j.id
        ) w where w.ptype=3
        <if test="tabType =='2'.toString()">
            and w.entm &lt;to_date(#{nowTime},'yyyy-MM-dd')+1
        </if>
        <if test="tabType =='1'.toString()">
            and w.entm &gt;=to_date(#{nowTime},'yyyy-MM-dd')
        </if>
        and

        REGEXP_LIKE(
        W.OID,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )
        else (select 'non' from dual) end)
        ||')')
        ) a where 1=1
        <if test="persName != null and persName != ''">and PERS_NAME like '%${persName}%'</if>
        <if test="orgNm != null and orgNm != ''">and org_nm like '%${orgNm}%'</if>
        <if test="codes != null and codes != ''">and CODE in (${codes})</if>
        <if test="adCodes != null and adCodes != ''">and AD_CODE in (${adCodes})</if>
        <if test="orgIds != null and orgIds != ''">and org_id in (${orgIds})</if>

        <if test="recPers !=null and recPers !=''">
            and rec_pers=#{recPers}
        </if>
        <if test="inspPblmType != null and inspPblmType != ''">and INSP_PBLM_TYPE = #{inspPblmType}</if>
        <if test="nm != null and nm != ''">and nm like '%${nm}%'</if>
        <if test="adFullName != null and adFullName != ''">and ad_full_name like '%${adFullName}%'</if>

        order by ad_code asc,coll_time desc
    </select>
    <!---->
    <select id="findKeyRegPblmPage" resultType="cn.com.goldenwater.dcproj.dto.PblmRetCount"
            parameterType="cn.com.goldenwater.dcproj.param.CountParam">
        select e.nm as rs_name ,dd.code from ATT_EMPWTPRJ_BASE e,(
        select distinct w.code from (
        select j.id as oid,j.ptype,j.code,p.insp_pblm_cate,r.org_id from BIS_INSP_PBLM p
        join (select * from bis_insp_all_obj o where o.ptype=#{pType}
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        ) w where w.ptype=#{pType}
        <if test="persId != null and persId != ''">
            and
            REGEXP_LIKE(
            W.OID,'^('||
            ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
            <include refid="orgIdSql"/>
            )>0 then
            (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
            id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
            <include refid="orgIdSql"/>
            )
            else (select 'non' from dual) end)
            ||')')
        </if>
        ) dd where e.id=dd.code
    </select>

    <!--分页查询小水闸-->
    <select id="findWagaPblmPage" resultType="cn.com.goldenwater.dcproj.dto.PblmRetCount"
            parameterType="cn.com.goldenwater.dcproj.param.CountParam">
        select e.gate_name as rs_name ,dd.code,e.eng_scal from att_waga_base e,(
        select distinct w.code from (
        select j.id as oid,j.ptype,j.code,p.insp_pblm_cate,j.org_id from bis_insp_pblm p
        join (
        select * from bis_insp_all_obj o where o.pType=#{pType}
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        ) w where w.ptype=#{pType}
        and
        REGEXP_LIKE(
        W.OID,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )
        else (select 'non' from dual) end)
        ||')') ) dd where e.obj_code=dd.code


    </select>

    <!--分页查询小水库-->
    <select id="findReverPblmPage" resultType="cn.com.goldenwater.dcproj.dto.PblmRetCount"
            parameterType="cn.com.goldenwater.dcproj.param.CountParam">
        select e.rs_name,dd.code,(case to_char(e.eng_scal) when '1' then '大(1)型' when '2' then '大(2)型' when '3' then
        '中型' when '4' then '小(1)型' when '5' then '小(2)型' end ) as eng_scal from att_rs_base e,(
        select distinct w.code from (
        select j.id as oid,j.ptype,j.code,p.insp_pblm_cate,j.org_id from
        bis_insp_pblm p
        join (select * from bis_insp_all_obj o where o.ptype=#{pType}
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        ) w where w.ptype=#{pType}
        and
        REGEXP_LIKE(
        W.OID,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )
        else (select 'non' from dual) end)
        ||')') ) dd where e.rs_code=dd.code
    </select>

    <!--问题总计-->
    <select id="totalCount" resultType="cn.com.goldenwater.dcproj.dto.CateTotal" parameterType="java.lang.String">
        select count(code) as total,(case insp_pblm_cate when '0' then '一般' when '1' then '较重' when '2' then '严重' when
        '3' then '特别严重' end) as cate from (
        select j.id as oid,j.ptype,j.code,p.insp_pblm_cate,j.ORG_ID from BIS_INSP_PBLM p
        join (select * from bis_insp_all_obj o where o.ptype=#{pType}
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        ) w where w.ptype=#{pType}
        <if test="persId !=null and persId !=''">
            and
            REGEXP_LIKE(
            W.OID,'^('||
            ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
            <include refid="orgIdSql"/>
            )>0 then
            (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
            id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
            <include refid="orgIdSql"/>
            )
            else (select 'non' from dual) end)
            ||')')
        </if>
        and insp_pblm_cate is not null group by insp_pblm_cate order by insp_pblm_cate
    </select>
    <select id="countKeyReg" resultType="cn.com.goldenwater.dcproj.dto.PblmCount" parameterType="java.lang.String">
        select dd.*,b.nm as rs_name from ATT_EMPWTPRJ_BASE b , (
        select a.code,count(a.code) as sub,insp_pblm_cate,count(insp_pblm_cate) as total from (
        select w.* from (
        select j.id as oid,j.ptype,j.code,p.insp_pblm_cate,j.org_id from bis_insp_pblm p
        join (select * from bis_insp_all_obj o where o.pType=#{pType}
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        ) w where w.ptype=#{pType} and w.code in(${codes})
        <if test="persId !=null and persId !=''">
            and
            REGEXP_LIKE(
            W.OID,'^('||
            ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
            <include refid="orgIdSql"/>
            )>0 then
            (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
            id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
            <include refid="orgIdSql"/>
            )
            else (select 'non' from dual) end)
            ||')')
        </if>

        ) a where a.insp_pblm_cate is not null group by a.code,a.insp_pblm_cate order by a.code ) dd where b.id=dd.code
    </select>
    <!--小水库水闸问题统计-->
    <select id="countWaga" resultType="cn.com.goldenwater.dcproj.dto.PblmCount" parameterType="java.lang.String">
        select dd.*,b.gate_name as rs_name,b.eng_scal from att_waga_base b , (
        select a.code,count(a.code) as sub,insp_pblm_cate,count(insp_pblm_cate) as total from (
        select w.* from (
        select j.id as oid,j.ptype,j.code,p.insp_pblm_cate,p.BELONG_ORG_ID from BIS_INSP_PBLM p
        join (select * from bis_insp_all_obj o where o.pType=6
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        ) w where w.ptype=6 and w.code in(${codes})
        and
        REGEXP_LIKE(
        W.OID,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )
        else (select 'non' from dual) end)
        ||')')
        ) a where a.insp_pblm_cate is not null group by a.code,a.insp_pblm_cate order by a.code ) dd where
        b.obj_code=dd.code

    </select>
    <!--小水库问题统计-->
    <select id="countRever" resultType="cn.com.goldenwater.dcproj.dto.PblmCount" parameterType="java.lang.String">
        select dd.*,b.rs_name,b.eng_scal from att_rs_base b,(
        select a.code,count(a.code) as sub,insp_pblm_cate,count(insp_pblm_cate) as total from (
        select w.* from (
        select j.id as oid,j.ptype,j.code,p.insp_pblm_cate,j.ORG_ID from BIS_INSP_PBLM p
        join (select * from bis_insp_all_obj o where o.ptype=#{pType}
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        ) w where w.ptype=#{pType} and w.code in(${codes})
        and
        REGEXP_LIKE(
        W.OID,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )
        else (select 'non' from dual) end)
        ||')')
        ) a where a.insp_pblm_cate is not null group by a.code,a.insp_pblm_cate order by a.code ) dd where
        b.rs_code=dd.code
    </select>

    <!--人饮统计问题分页-->
    <select id="findVillPblmPage" resultType="cn.com.goldenwater.dcproj.dto.VillCount"
            parameterType="cn.com.goldenwater.dcproj.param.CountParam">

        select dd.*,b.ad_full_name from att_ad_x_base b ,(
        select code,count(pblm_id) as total from (
        select j.code,j.nm,j.id as oid,j.ptype,p.vill_type,b.ad_full_name,p.pblm_id,j.org_id from BIS_INSP_PBLM p
        join (select * from bis_insp_all_obj o where o.ptype=2
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        left join att_ad_x_base b on j.code=b.ad_code
        ) w where w.ptype=2
        and
        REGEXP_LIKE(
        W.OID,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )
        else (select 'non' from dual) end)
        ||')') and vill_type is not null group by w.code ) dd where b.ad_code=dd.code order by dd.code
    </select>

    <select id="countVill" resultType="cn.com.goldenwater.dcproj.dto.VillTypeCount" parameterType="java.lang.String">
        select count(code) as total,code,insp_Pblm_Name from (
        select j.code,j.nm,j.id as oid,j.ptype,p.vill_type,j.org_id,(case p.vill_type when '1' then '行政村' when '2' then
        '水源地' when '3' then '饮水工程' else '责任落实' end) insp_Pblm_Name
        from BIS_INSP_PBLM p
        join (select * from bis_insp_all_obj o where o.ptype=2
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        left join att_ad_x_base b on j.code=b.ad_code
        ) w where w.ptype=2 and w.code in (${codes})
        and
        REGEXP_LIKE(
        W.OID,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )
        else (select 'non' from dual) end)
        ||')') and w.vill_type is not null group by code,insp_Pblm_Name

    </select>

    <!--人饮问题统计-->
    <select id="totalCountVill" parameterType="java.lang.String"
            resultType="cn.com.goldenwater.dcproj.dto.VillTypeCount">
        select count(code) as total,insp_Pblm_Name from (
        select j.code,j.nm,j.id as oid,j.ptype,p.vill_type,j.org_id,(case p.vill_type when '1' then '行政村' when '2' then
        '水源地' when '3' then '饮水工程' else '责任落实' end) insp_Pblm_Name
        from BIS_INSP_PBLM p
        join (select * from bis_insp_all_obj o where o.ptype=2
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        left join att_ad_x_base b on j.code=b.ad_code
        ) w where w.ptype=2
        and
        REGEXP_LIKE(
        W.OID,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )
        else (select 'non' from dual) end)
        ||')') and w.vill_type is not null group by insp_Pblm_Name

    </select>

    <!--获取用户可查询问题范围-->
    <select id="findPblmByPersId" parameterType="java.lang.String" resultType="cn.com.goldenwater.dcproj.dto.PblmRole">
        select distinct name, id,length,case when perType is null then '0' else perType end perType from (
        SELECT length(B.ID) as length,substr(B.id,0,3) as id,e.name as name,o.permission as perType
        FROM (
        select * from BIS_INSP_ALL_RLATION r where
        <choose>
            <when test="province !=null and province !=''">
                r.ad_code =#{province}
            </when>
            <otherwise>
                r.ad_code is null
            </otherwise>
        </choose>
        ) B join BIS_INSP_ALL_RLATION_PERS o on b.persid=o.guid
        join att_insp_type e on substr(B.id,0,3)=e.code
        WHERE B.PERSID =#{persId} ) w order by w.id asc
    </select>
    <!--根据督查组查询统计问题-->
    <select id="getAreaOrgIdsNew" resultType="java.lang.String" parameterType="java.lang.String">
        SELECT LISTAGG(A.id, ',') WITHIN GROUP(ORDER BY A.id) as id
        FROM BIS_INSP_ALL A
        join BIS_INSP_ALL_RLATION B ON A.ID = B.ID
        join Bis_insp_all_obj o on A.id=o.id
        WHERE B.PERSID =#{persId} AND A.ID LIKE #{areaId}
        <include refid="choseSql"/>
    </select>

    <!--获取数据-->
    <select id="findKeyRegBYAreaId" parameterType="java.lang.String"
            resultType="cn.com.goldenwater.dcproj.model.KeyRegCount">
        select count(insp_pblm_cate) as total,insp_pblm_cate,ad_full_name from (
        select j.id as oid,j.ptype,j.code,p.insp_pblm_cate,
        (select e.ad_full_name from att_ad_x_base e where e.ad_code=concat(substr(se.ad_code,0,2),'0000000000')) as
        ad_full_name from BIS_INSP_PBLM p
        join (select * from BIS_INSP_ALL_OBJ o where o.ptype=#{pType}
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        left join att_empwtprj_base ba on j.code=ba.id
        left join att_ad_x_base se on ba.ad_code=se.ad_code
        ) w where w.ptype=#{pType} group by insp_pblm_cate,ad_full_name
    </select>
    <!--根据督查组查人饮问题县统计-->
    <select id="findVillPblmBYAreaId" parameterType="java.lang.String"
            resultType="cn.com.goldenwater.dcproj.dto.VillCount">
        select count(code) as countyNum,ad_full_name from (
        select distinct code,ad_full_name from (
        select j.code,j.nm,j.id as oid,j.ptype,p.vill_type,
        (select e.ad_full_name from att_ad_x_base e where e.ad_code=concat(substr(b.ad_code,0,2),'0000000000')) as
        ad_full_name,p.pblm_id from BIS_INSP_PBLM p
        join (select * from BIS_INSP_ALL_OBJ o where o.ptype=2
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        left join att_ad_x_base b on j.code=b.ad_code
        ) w where w.ptype=2
        <if test="areaId !=null and areaId !='' ">
            and w.oid in (${areaId})
        </if>
        and vill_type is not null ) dd group by ad_full_name
    </select>
    <select id="countVillPblmByArea" parameterType="java.lang.String"
            resultType="cn.com.goldenwater.dcproj.dto.VillTypeCount">
        select count(ad_full_name) as total,ad_full_name,insp_Pblm_Name from (
        select j.nm,j.id as oid,j.ptype,p.vill_type,substr(b.ad_full_name,0,INSTR(b.ad_full_name, '-', 1, 1)-1) as
        ad_full_name,
        (case p.vill_type when '1' then '行政村' when '2' then '水源地' when '3' then '饮水工程' else '责任落实' end) insp_Pblm_Name
        from BIS_INSP_PBLM p
        join (select * from BIS_INSP_ALL_OBJ o where o.ptype=2
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        left join att_ad_x_base b on j.code=b.ad_code
        ) w where w.ptype=2
        <if test="areaId !=null and areaId !='' ">
            and w.oid in (${areaId})
        </if>
        and w.vill_type is not null group by ad_full_name,insp_Pblm_Name

    </select>

    <!--根据督查组查询水闸问题-->
    <select id="findWagaPblmBYAreaId" parameterType="java.lang.String"
            resultType="cn.com.goldenwater.dcproj.model.WagaTotal">
        select count(obj_code) as sub,ad_full_name as nm from (
        select w.obj_code,ad_full_name from (
        select t.obj_code,j.code,j.nm,j.id as oid,j.ptype,p.vill_type,p.insp_pblm_name,
        (select e.ad_full_name from att_ad_x_base e where e.ad_code=concat(substr(b.ad_code,0,2),'0000000000')) as
        ad_full_name,p.pblm_id from BIS_INSP_PBLM p
        join (select * from BIS_INSP_ALL_OBJ o where o.ptype=6
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        left join att_waga_base t on t.obj_code=j.code
        left join att_ad_x_base b on t.ad_code=b.ad_code
        ) w where w.ptype=6
        <if test="areaId !=null and areaId !='' ">
            and w.oid in (${areaId})
        </if>
        and vill_type is not null and insp_pblm_name is not null ) dd where ad_full_name is not null group by
        ad_full_name

    </select>

    <!--水闸问题根据类型统计-->
    <select id="countWagaPblmByType" parameterType="java.lang.String"
            resultType="cn.com.goldenwater.dcproj.model.WagaType">
        select count(ad_full_name) as total,ad_full_name as nm,eng_scal from (
        select w.ad_full_name,w.eng_scal from (
        select t.obj_code,t.eng_scal,j.code,j.nm,j.id as oid,j.ptype,p.vill_type,p.insp_pblm_name,
        (select e.ad_full_name from att_ad_x_base e where e.ad_code=concat(substr(b.ad_code,0,2),'0000000000')) as
        ad_full_name,
        p.pblm_id from BIS_INSP_PBLM p
        join (select * from BIS_INSP_ALL_OBJ o where o.ptype=6
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        left join att_waga_base t on t.obj_code=j.code
        left join att_ad_x_base b on t.ad_code=b.ad_code
        ) w where w.ptype=6
        <if test="areaId !=null and areaId !='' ">
            and w.oid in (${areaId})
        </if>
        and vill_type is not null and insp_pblm_name is not null ) dd group by ad_full_name,eng_scal
    </select>

    <select id="countWagaPblmByCate" parameterType="java.lang.String"
            resultType="cn.com.goldenwater.dcproj.model.WagaType">
        select count(ad_full_name) as total,ad_full_name as nm,insp_pblm_cate as cate from (
        select w.ad_full_name,w.insp_pblm_cate from (
        select t.obj_code,t.eng_scal,j.code,j.nm,j.id as
        oid,j.ptype,p.vill_type,p.insp_pblm_cate,p.insp_pblm_name,p.pblm_id ,
        (select e.ad_full_name from att_ad_x_base e where e.ad_code=concat(substr(b.ad_code,0,2),'0000000000')) as
        ad_full_name
        from BIS_INSP_PBLM p
        join (select * from BIS_INSP_ALL_OBJ o where o.ptype=6
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        left join att_waga_base t on t.obj_code=j.code
        left join att_ad_x_base b on t.ad_code=b.ad_code
        ) w where w.ptype=6
        <if test="areaId !=null and areaId !='' ">
            and w.oid in (${areaId})
        </if>
        and vill_type is not null and insp_pblm_name is not null ) dd group by ad_full_name,insp_pblm_cate


    </select>
    <!--根据督查组查询水毁问题-->
    <select id="findTrackPblmBYAreaId" parameterType="java.lang.String"
            resultType="cn.com.goldenwater.dcproj.dto.TrackCount">
        select count(wtdst_id) as project_Nm,ad_full_name as nm from (
        select distinct w.wtdst_id,ad_full_name from (
        select t.wtdst_id,j.code,j.nm,j.id as oid,j.ptype,p.vill_type,p.insp_pblm_name,
        (select e.ad_full_name from att_ad_x_base e where e.ad_code=concat(substr(b.ad_code,0,2),'0000000000')) as
        ad_full_name,p.pblm_id from BIS_INSP_PBLM p
        join (select * from BIS_INSP_ALL_OBJ o where o.ptype=3
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        left join bis_insp_wtdst t on t.wtdst_id=j.code
        left join att_ad_x_base b on t.loc_ad=b.ad_code
        ) w where w.ptype=3
        <if test="areaId !=null and areaId !='' ">
            and w.oid in (${areaId})
        </if>
        and vill_type is not null and insp_pblm_name is not null ) dd where ad_full_name is not null group by
        ad_full_name
    </select>
    <select id="countTrackPblmByArea" parameterType="java.lang.String"
            resultType="cn.com.goldenwater.dcproj.dto.TrackTypeCount">
        select insp_pblm_name,ad_full_name as nm,count(insp_pblm_name) as total from (
        select t.wtdst_id,j.code,j.nm,j.id as oid,j.ptype,p.vill_type,p.insp_pblm_name,
        (select e.ad_full_name from att_ad_x_base e where e.ad_code=concat(substr(b.ad_code,0,2),'0000000000')) as
        ad_full_name,p.pblm_id from BIS_INSP_PBLM p
        join (select * from BIS_INSP_ALL_OBJ o where o.ptype=3
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id

        left join bis_insp_wtdst t on t.wtdst_id=j.code
        left join att_ad_x_base b on t.loc_ad=b.ad_code
        ) w where w.ptype=3
        <if test="areaId !=null and areaId !='' ">
            and w.oid in (${areaId})
        </if>
        and vill_type is not null and insp_pblm_name is not null group by ad_full_name,insp_pblm_name

    </select>
    <!--水毁工程和问题总计查询-->
    <select id="findTrackPblmPage" resultType="cn.com.goldenwater.dcproj.dto.TrackCount"
            parameterType="cn.com.goldenwater.dcproj.param.CountParam">
        select dd.*,k.nm from BIS_INSP_ALL_OBJ k,(
        select obj_id,count(insp_pblm_cate) as total from (
        select w.* from (
        select j.nm,j.id as oid,j.obj_id,j.ptype,p.insp_pblm_name,p.insp_pblm_cate from BIS_INSP_PBLM p
        join (select * from BIS_INSP_ALL_OBJ o where o.ptype=3
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        ) w where w.ptype=3 and
        REGEXP_LIKE(
        W.OID,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )
        else (select 'non' from dual) end)
        ||')') and w.insp_pblm_name is not null and w.insp_pblm_cate is not null
        ) a group by obj_id ) dd where k.obj_id=dd.obj_id order by total

    </select>
    <!--水毁根据督查问题类型统计-->
    <select id="countTrack" parameterType="java.lang.String" resultType="cn.com.goldenwater.dcproj.dto.TrackTypeCount">
        select obj_id,count(insp_pblm_cate) as total,insp_pblm_name from (
        select w.* from (
        select j.nm,j.id as oid,j.obj_id,j.ptype,p.insp_pblm_name,p.insp_pblm_cate from BIS_INSP_PBLM p
        join (select * from BIS_INSP_ALL_OBJ o where o.ptype=3
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        ) w where w.ptype=3 and obj_id in (${objIds}) and
        REGEXP_LIKE(
        W.OID,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )
        else (select 'non' from dual) end)
        ||')') and w.insp_pblm_name is not null and w.insp_pblm_cate is not null
        ) a group by obj_id,insp_pblm_name
    </select>
    <!--水毁问题分类合计-->
    <select id="totalCountTrack" parameterType="java.lang.String"
            resultType="cn.com.goldenwater.dcproj.dto.TrackTypeCount">
        select insp_pblm_name,count(insp_pblm_cate) as total from (
        select j.nm,j.id as oid,j.obj_id,j.ptype,p.insp_pblm_name,p.insp_pblm_cate from BIS_INSP_PBLM p
        join (select * from BIS_INSP_ALL_OBJ o where o.ptype=3
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        ) w where w.ptype=3 and
        REGEXP_LIKE(
        W.OID,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )
        else (select 'non' from dual) end)
        ||')') and w.insp_pblm_name is not null and w.insp_pblm_cate is not null group by insp_pblm_name
    </select>
    <select id="countByType" resultType="cn.com.goldenwater.dcproj.dto.CountType" parameterType="java.util.Map">
        select count(w.pblm_id) as count,(case obj_type when '1' then '小水库' when '3' then '水毁' when '5' then '地下水' end )
        as name from (
        select j.code,j.nm,j.id as oid,r.PERS_NAME,p.* from BIS_INSP_PBLM p
        join (select * from BIS_INSP_ALL_OBJ o where 1=1
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        ) w
        where 1=1
        <trim prefix="and" prefixOverrides="and | or ">
            <if test="adCode != null and adCode != '' and adCode != 'null'">and w.CODE like '${adCode}%'</if>
        </trim>
        and REGEXP_LIKE(
        W.oid,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{presId}
        <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"/>
        )
        else (select 'non' from dual) end)
        ||')') and obj_type in (1,3,5) group by obj_type
        union all
        select count(w.pblm_id) as count,'行政村' as name from (
        select j.code,j.nm,j.id as oid,r.PERS_NAME,p.* from BIS_INSP_PBLM p
        join BIS_INSP_ALL_OBJ j on p.obj_id=j.obj_id
        join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        ) w
        where 1=1
        <trim prefix="and" prefixOverrides="and | or ">
            <if test="adCode != null and adCode != '' and adCode != 'null'">and w.CODE like '${adCode}%'</if>
        </trim>
        and REGEXP_LIKE(
        W.oid,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{presId}
        <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"/>
        )
        else (select 'non' from dual) end)
        ||')') and obj_type=2 and w.village_code is not null
        union all
        select count(w.pblm_id) as count,'饮水工程' as name from (
        select j.code,j.nm,j.id as oid,r.PERS_NAME,p.* from BIS_INSP_PBLM p
        join BIS_INSP_ALL_OBJ j on p.obj_id=j.obj_id
        join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        ) w
        where 1=1
        <trim prefix="and" prefixOverrides="and | or ">
            <if test="adCode != null and adCode != '' and adCode != 'null'">and w.CODE like '${adCode}%'</if>
        </trim>
        and REGEXP_LIKE(
        W.oid,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{presId}
        <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"/>
        )
        else (select 'non' from dual) end)
        ||')') and obj_type=2 and w.cws_code is not null
        union all
        select count(w.pblm_id) as count,'水源地' as name from (
        select j.code,j.nm,j.id as oid,r.PERS_NAME,p.* from BIS_INSP_PBLM p
        join BIS_INSP_ALL_OBJ j on p.obj_id=j.obj_id
        join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        ) w
        where 1=1
        <trim prefix="and" prefixOverrides="and | or ">
            <if test="adCode != null and adCode != '' and adCode != 'null'">and w.CODE like '${adCode}%'</if>
        </trim>
        and REGEXP_LIKE(
        W.oid,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{presId}
        <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"/>
        )
        else (select 'non' from dual) end)
        ||')') and obj_type=2 and w.cws_code is null and w.village_code is null


    </select>

    <select id="selectCount" resultType="int">
        select count(m.PBLM_ID) from bis_insp_pblm m,bis_insp_all_obj j
        where m.obj_id=j.obj_id
        <if test="startTime != null and startTime != ''">and m.COLL_TIME &gt; TO_DATE(#{startTime},'YYYY-MM-DD
            HH24:MI:SS')
        </if>
        <if test="endTime != null and endTime != ''">and m.COLL_TIME &lt; TO_DATE(#{endTime},'YYYY-MM-DD HH24:MI:SS')
        </if>
        <if test="findTime != null and findTime != ''">and to_char(m.coll_time,'yyyy-MM-dd')=#{findTime}</if>
        <if test="monthTime != null and monthTime != ''">and to_char(m.coll_time,'yyyy-MM')=#{monthTime}</if>
        <if test="regid != null and regid != ''">and M.REGID = #{regid}</if>
        <if test="objId != null and objId != ''">and M.OBJ_ID = #{objId}</if>
        <if test="orgId != null and orgId != ''">and J.ORG_ID = #{orgId}</if>
        <choose>
            <when test="province != null and province != ''">
                and j.ad_code = '${province}'
            </when>
            <otherwise>
                and j.ad_code is null
            </otherwise>
        </choose>
    </select>


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

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

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

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

    <update id="deletePblmByTypeId" parameterType="cn.com.goldenwater.dcproj.model.BisInspPblm">
        delete from bis_insp_pblm
        where obj_type = #{objType}
        and PBLMS_TYPE_ID in (${pblmsTypeId})
        <if test="objType == '1'">
            and obj_id in (select obj_id from bis_insp_rsvr_rgstr where rgstr_id = #{rgstrId})
        </if>
    </update>

    <update id="updatePblmStatus" parameterType="cn.com.goldenwater.dcproj.model.BisInspPblm">
        update bis_insp_pblm set pblmStat=#{pblmStat} where regid=#{regid}

    </update>

    <update id="update" parameterType="cn.com.goldenwater.dcproj.model.BisInspPblm">
        update bis_insp_pblm
        <trim prefix="set" suffixOverrides=",">
            <if test="newVillName != null and newVillName != ''">NEW_VILL_NAME = #{newVillName},</if>
            <if test="pblmNameBz != null and pblmNameBz != ''">PBLM_NAME_BZ = #{pblmNameBz},</if>
            <if test="snNumBz != null and snNumBz != ''">SN_NUM_BZ = #{snNumBz},</if>
            <if test="checkPointBz != null and checkPointBz != ''">CHECK_POINT_BZ = #{checkPointBz},</if>
            <if test="pblmDescBz != null and pblmDescBz != ''">PBLM_DESC_BZ = #{pblmDescBz},</if>
            <if test="regid != null and regid != ''">REGID = #{regid},</if>
            <if test="attachBz != null and attachBz != ''">attach_Bz = #{attachBz},</if>
            <if test="quote != null and quote != ''">QUOTE = #{quote},</if>
            <if test="commonFileIds != null and commonFileIds != ''">COMMON_FILE_IDS = #{commonFileIds},</if>
            <if test="fileNo != null and fileNo != ''">FILE_NO = #{fileNo},</if>
            <if test="fileNoNumber != null and fileNoNumber != ''">FILE_NO_NUMBER = #{fileNoNumber},</if>
            <if test="pblmsId != null and pblmsId != '0'.toString()">PBLMS_ID = #{pblmsId},</if>
            <if test="pblmsTypeId != null and pblmsTypeId != '0'.toString()">PBLMS_TYPE_ID = #{pblmsTypeId},</if>
            <if test="pblmsId == '0'.toString()">PBLMS_ID ='',</if>
            <if test="pblmsTypeId == '0'.toString()">PBLMS_TYPE_ID ='',</if>
            <if test="objId != null and objId != ''">OBJ_ID = #{objId},</if>
            <if test="objType != null and objType != ''">OBJ_TYPE = #{objType},</if>
            <if test="waterUserId != null and waterUserId != ''">water_User_Id = #{waterUserId},</if>
            <if test="inspGroupId != null and inspGroupId != ''">INSP_GROUP_ID = #{inspGroupId},</if>
            <if test="pguid != null and pguid != ''">PGUID = #{pguid},</if>
            <if test="inspPblmType != null and inspPblmType != ''">INSP_PBLM_TYPE = #{inspPblmType},</if>
            <if test="inspPblmCode != null and inspPblmCode != ''">INSP_PBLM_CODE = #{inspPblmCode},</if>
            <if test="inspPblmName != null and inspPblmName != ''">INSP_PBLM_NAME = #{inspPblmName},</if>
            <if test="inspPblmDesc != null and inspPblmDesc != ''">INSP_PBLM_DESC = #{inspPblmDesc},</if>
            <if test="inspAddDesc != null and inspAddDesc != ''">INSP_ADD_DESC = #{inspAddDesc},</if>
            <if test="pblmLong != null and pblmLong != ''">PBLM_LONG = #{pblmLong},</if>
            <if test="pblmLat != null and pblmLat != ''">PBLM_LAT = #{pblmLat},</if>
            <if test="ifCasePblm != null and ifCasePblm != ''">IF_CASE_PBLM = #{ifCasePblm},</if>
            <if test="inspPblmOrgName != null and inspPblmOrgName != ''">INSP_PBLM_ORG_NAME = #{inspPblmOrgName},</if>
            <if test="pblmPersName != null and pblmPersName != ''">PBLM_PERS_NAME = #{pblmPersName},</if>
            <if test="inspPblmCate != null and inspPblmCate != ''">INSP_PBLM_CATE = #{inspPblmCate},</if>
            <if test="relBasis != null and relBasis != ''">REL_BASIS = #{relBasis},</if>
            <if test="pblmStat != null and pblmStat != ''">PBLM_STAT = #{pblmStat},</if>
            <if test="reviOpin != null and reviOpin != ''">REVI_OPIN = #{reviOpin},</if>
            <if test="reviConc != null and reviConc != ''">REVI_CONC = #{reviConc},</if>
            <if test="villageCode != null and villageCode != ''">village_Code = #{villageCode},</if>
            <if test="cwsCode != null and cwsCode != ''">cws_Code = #{cwsCode},</if>
            <if test="reviOrgGuid != null and reviOrgGuid != ''">REVI_ORG_GUID = #{reviOrgGuid},</if>
            <if test="dataStat != null and dataStat != ''">DATA_STAT = #{dataStat},</if>
            <if test="collTime != null">COLL_TIME = #{collTime},</if>
            <if test="recPers != null and recPers != ''">REC_PERS = #{recPers},</if>
            <if test="note != null and note != ''">NOTE = #{note},</if>
            <if test="hasVedio != null and hasVedio != ''">HAS_VEDIO = #{hasVedio},</if>
            <if test="problemType != null and problemType != ''">problem_Type = #{problemType},</if>
            <if test="villType != null and villType != ''">VILL_TYPE = #{villType},</if>
            <if test="defectTypeId != null and defectTypeId != '0'.toString()">DEFECT_TYPE_ID = #{defectTypeId},</if>
            <if test="qualityTypeId != null and qualityTypeId != '0'.toString()">QUALITY_TYPE_ID = #{qualityTypeId},
            </if>
            <if test="defectTypeId == '0'.toString()">DEFECT_TYPE_ID ='',</if>
            <if test="qualityTypeId == '0'.toString()">QUALITY_TYPE_ID ='',</if>
            <if test="gongdiBzId != null and gongdiBzId != '0'.toString()">gongdi_Bz_Id = #{gongdiBzId},</if>
            <if test="gongdiBzId == '0'.toString()">gongdi_Bz_Id ='',</if>
            <if test="fdbkId != null and fdbkId != ''">FDBK_ID = #{fdbkId},</if>
            <if test="itemId != null and itemId != ''">item_Id = #{itemId},</if>
            <if test="uptm != null">UP_TM = #{uptm},</if>
            <if test="pblmReason != null and pblmReason != ''">PBLM_REASON = #{pblmReason},</if>
            <if test="pblmSggtn != null and pblmSggtn != ''">PBLM_SGGTN = #{pblmSggtn},</if>
            <if test="objCode != null and objCode != ''">OBJ_CODE = #{objCode},</if>
            <if test="objName != null and objName != ''">OBJ_NAME = #{objName},</if>
            <if test="objAdCode != null and objAdCode != ''">OBJ_AD_CODE = #{objAdCode},</if>
            <if test="wtdstNote != null and wtdstNote != ''">WTDST_NOTE = #{wtdstNote},</if>
            <if test="state != null and state != ''">STATE = #{state},</if>
            <if test="score != null and score != ''">SCORE = #{score},</if>
            <if test="damRegCode != null and damRegCode != ''">DAM_REG_CODE = #{damRegCode},</if>
        </trim>
        <where>PBLM_ID = #{pblmId}</where>
    </update>

    <update id="updateBy" parameterType="cn.com.goldenwater.dcproj.model.BisInspPblm">
        update bis_insp_pblm
        <trim prefix="set" suffixOverrides=",">
            <if test="newVillName != null and newVillName != ''">NEW_VILL_NAME = #{newVillName},</if>
            <if test="pblmNameBz != null and pblmNameBz != ''">PBLM_NAME_BZ = #{pblmNameBz},</if>
            <if test="snNumBz != null and snNumBz != ''">SN_NUM_BZ = #{snNumBz},</if>
            <if test="checkPointBz != null and checkPointBz != ''">CHECK_POINT_BZ = #{checkPointBz},</if>
            <if test="pblmDescBz != null and pblmDescBz != ''">PBLM_DESC_BZ = #{pblmDescBz},</if>
            <if test="regid != null and regid != ''">REGID = #{regid},</if>
            <if test="attachBz != null and attachBz != ''">attach_Bz = #{attachBz},</if>
            <if test="commonFileIds != null and commonFileIds != ''">COMMON_FILE_IDS = #{commonFileIds},</if>
            <if test="fileNo != null and fileNo != ''">FILE_NO = #{fileNo},</if>
            <if test="fileNoNumber != null and fileNoNumber != ''">FILE_NO_NUMBER = #{fileNoNumber},</if>
            <if test="pblmsId != null and pblmsId != ''">PBLMS_ID = #{pblmsId},</if>
            <if test="pblmId != null and pblmId != ''">PBLM_ID = #{pblmId},</if>
            <if test="waterUserId != null and waterUserId != ''">water_User_Id = #{waterUserId},</if>
            <if test="objId != null and objId != ''">OBJ_ID = #{objId},</if>
            <if test="objType != null and objType != ''">OBJ_TYPE = #{objType},</if>
            <if test="inspGroupId != null and inspGroupId != ''">INSP_GROUP_ID = #{inspGroupId},</if>
            <if test="pguid != null and pguid != ''">PGUID = #{pguid},</if>
            <if test="itemId != null and itemId != ''">item_Id = #{itemId},</if>
            <if test="inspPblmType != null and inspPblmType != ''">INSP_PBLM_TYPE = #{inspPblmType},</if>
            <if test="inspPblmCode != null and inspPblmCode != ''">INSP_PBLM_CODE = #{inspPblmCode},</if>
            <if test="inspPblmName != null and inspPblmName != ''">INSP_PBLM_NAME = #{inspPblmName},</if>
            <if test="inspPblmDesc != null and inspPblmDesc != ''">INSP_PBLM_DESC = #{inspPblmDesc},</if>
            <if test="inspAddDesc != null and inspAddDesc != ''">INSP_ADD_DESC = #{inspAddDesc},</if>
            <if test="pblmLong != null and pblmLong != ''">PBLM_LONG = #{pblmLong},</if>
            <if test="pblmLat != null and pblmLat != ''">PBLM_LAT = #{pblmLat},</if>
            <if test="ifCasePblm != null and ifCasePblm != ''">IF_CASE_PBLM = #{ifCasePblm},</if>
            <if test="inspPblmOrgName != null and inspPblmOrgName != ''">INSP_PBLM_ORG_NAME = #{inspPblmOrgName},</if>
            <if test="pblmPersName != null and pblmPersName != ''">PBLM_PERS_NAME = #{pblmPersName},</if>
            <if test="inspPblmCate != null and inspPblmCate != ''">INSP_PBLM_CATE = #{inspPblmCate},</if>
            <if test="pblmStat != null and pblmStat != ''">PBLM_STAT = #{pblmStat},</if>
            <if test="reviOpin != null and reviOpin != ''">REVI_OPIN = #{reviOpin},</if>
            <if test="reviConc != null and reviConc != ''">REVI_CONC = #{reviConc},</if>
            <if test="reviOrgGuid != null and reviOrgGuid != ''">REVI_ORG_GUID = #{reviOrgGuid},</if>
            <if test="dataStat != null and dataStat != ''">DATA_STAT = #{dataStat},</if>
            <if test="fdbkId != null and fdbkId != ''">FDBK_ID = #{fdbkId},</if>
            <if test="collTime != null">COLL_TIME = #{collTime},</if>
            <if test="villageCode != null and villageCode != ''">village_Code = #{villageCode},</if>
            <if test="problemType != null and problemType != ''">problem_Type = #{problemType},</if>
            <if test="cwsCode != null and cwsCode != ''">cws_Code = #{cwsCode},</if>
            <if test="recPers != null and recPers != ''">REC_PERS = #{recPers},</if>
            <if test="note != null and note != ''">NOTE = #{note},</if>
            <if test="villType != null and villType != ''">VILL_TYPE = #{villType},</if>
            <if test="hasVedio != null and hasVedio != ''">HAS_VEDIO = #{hasVedio},</if>
            <if test="defectTypeId != null and defectTypeId != ''">DEFECT_TYPE_ID = #{defectTypeId},</if>
            <if test="qualityTypeId != null and qualityTypeId != ''">QUALITY_TYPE_ID = #{qualityTypeId},</if>
            <if test="gongdiBzId != null and gongdiBzId != '0'.toString()">gongdi_Bz_Id = #{gongdiBzId},</if>
            <if test="gongdiBzId == '0'.toString()">gongdi_Bz_Id ='',</if>
            <if test="uptm != null">UP_TM = #{uptm},</if>
            <if test="pblmReason != null and pblmReason != ''">PBLM_REASON = #{pblmReason},</if>
            <if test="pblmSggtn != null and pblmSggtn != ''">PBLM_SGGTN = #{pblmSggtn},</if>
            <if test="objCode != null and objCode != ''">OBJ_CODE = #{objCode},</if>
            <if test="objName != null and objName != ''">OBJ_NAME = #{objName},</if>
            <if test="objAdCode != null and objAdCode != ''">OBJ_AD_CODE = #{objAdCode},</if>
            <if test="wtdstNote != null and wtdstNote != ''">WTDST_NOTE = #{wtdstNote},</if>
        </trim>
        <include refid="page_where"/>
    </update>


    <!-- 其他自定义SQL -->
    <select id="getBisInspPblmById" resultType="cn.com.goldenwater.dcproj.model.BisInspPblmDelLog">
        select t.pblm_id,t.PBLM_NAME_BZ,t.CHECK_POINT_BZ,t.SN_NUM_BZ,t.PBLM_DESC_BZ,
        t.obj_id,
        t.obj_type,
        t.quote,
        t.insp_group_id,
        t.pguid,
        t.insp_pblm_type,
        t.insp_pblm_code,
        t.insp_pblm_name,
        t.insp_pblm_desc,
        t.insp_add_desc,
        t.pblm_long,
        t.pblm_lat,
        t.if_case_pblm,
        t.insp_pblm_org_name,
        t.pblm_pers_name,
        t.insp_pblm_cate,
        t.pblm_stat,
        t.revi_opin,
        t.revi_conc,
        t.revi_org_guid,
        t.data_stat,
        to_char(t.coll_time,'YYYY-MM-DD HH24:MI:SS') coll_time,
        t.rec_pers,
        t.note,
        t.regid,
        t.common_file_ids,
        t.file_no,
        t.file_no_number,
        t.pblms_id,
        t.pblms_type_id,
        t.vill_type,
        t.cws_code,
        t.WTDST_NOTE,
        t.village_code from BIS_INSP_PBLM t WHERE t.pblm_id = #{pblmId}
    </select>

    <insert id="insertPblmDelLog" parameterType="cn.com.goldenwater.dcproj.model.BisInspPblmDelLog">
        insert into BIS_INSP_PBLM_DEL_LOG(
        <include refid="table_columns_pblmDelLog"/>
        )
        values (
        <include refid="entity_properties_pblmDelLog"/>
        )
    </insert>
    <sql id="choseSql">
        <choose>
            <when test="province != null and province !=''">
                and o.ad_code = '${province}'
            </when>

        </choose>
    </sql>
    <select id="countPblmIncByDay" parameterType="cn.com.goldenwater.dcproj.param.IndexPblmParam"
            resultType="cn.com.goldenwater.dcproj.dto.CountTime">
        select LTRIM(to_char(TO_DATE(f.dat,'yyyy-mm-dd'),'dd'),'0') as name,f.value from (
        select c.dat,nvl(d.value,0) as value from (
        select to_char(to_date(#{seltime},'yyyy-mm-dd')+level-1,'yyyy-mm-dd') as dat
        from dual
        connect by level &lt;=to_date(#{ettm},'yyyy-mm-dd')-to_date(#{seltime},'yyyy-mm-dd')+1
        ) c left join (
        select to_char(w.COLL_TIME,'yyyy-MM-dd') AS NAME,count(1)as value from (
        <trim>
            <include refid="lyzSql"/>
        </trim>
        ) w where 1=1
        <if test="sttm != null and sttm != ''">and w.coll_time&gt;= TO_DATE(#{sttm},'YYYY-MM-DD')</if>
        <if test="ettm != null and ettm != ''">and to_char(w.coll_time,'yyyy-MM-dd') &lt;= #{ettm}</if>
        <if test="orgIds != null and orgIds != ''">
            and substr(w.id,4,3) in (${orgIds})
        </if>
        <if test="adCode != null and adCode != ''">and substr(w.id,4,3) in (${orgIds})and w.AD_CODE like
            CONCAT(#{adCode}, '%')
        </if>
        <if test="type != null and type != ''">and w.obj_type in (${type})</if>
        group by to_char(w.COLL_TIME,'yyyy-MM-dd')
        ) d on c.dat = d.name
        order by c.dat
        ) f
    </select>

    <select id="countPblmTotalByDay" parameterType="cn.com.goldenwater.dcproj.param.IndexPblmParam"
            resultType="cn.com.goldenwater.dcproj.dto.CountTime">
        select LTRIM(to_char(TO_DATE(f.dat,'yyyy-mm-dd'),'dd'),'0') as name,f.value from (
        select x.dat,sum(x.value) over(order by x.dat) as value from (
        select c.dat,nvl(d.value,0) as value from (
        select to_char(to_date(#{sttm},'yyyy-mm-dd')+level-1,'yyyy-mm-dd') as dat
        from dual
        connect by level &lt;=to_date(#{ettm},'yyyy-mm-dd')-to_date(#{sttm},'yyyy-mm-dd')+1
        ) c left join (
        select to_char(w.COLL_TIME,'yyyy-MM-dd') AS NAME,count(1)as value from (

        <trim>
            <include refid="lyzSql"/>
        </trim>
        ) w where 1=1
        <if test="sttm != null and sttm != ''">and w.coll_time&gt;= TO_DATE(#{sttm},'YYYY-MM-DD')</if>
        <if test="ettm != null and ettm != ''">and to_char(w.coll_time,'yyyy-MM-dd') &lt;= #{ettm}</if>
        <if test="orgIds != null and orgIds != ''">
            and substr(w.id,4,3) in (${orgIds})
        </if>
        <if test="adCode != null and adCode != ''">and substr(w.id,4,3) in (${orgIds})and w.AD_CODE like
            CONCAT(#{adCode}, '%')
        </if>
        <if test="type != null and type != ''">and w.obj_type in (${type})</if>
        group by to_char(w.COLL_TIME,'yyyy-MM-dd')
        ) d on c.dat = d.name
        order by c.dat
        )x
        ) f
    </select>

    <select id="countPblmIncByWeek" parameterType="cn.com.goldenwater.dcproj.param.IndexPblmParam"
            resultType="cn.com.goldenwater.dcproj.dto.CountTime">
        select to_char(w.COLL_TIME,'iw') AS NAME,count(1)as value from (

        <trim>
            <include refid="lyzSql"/>
        </trim>
        ) w where 1=1
        <if test="sttm != null and sttm != ''">and w.coll_time&gt;= TO_DATE(#{sttm},'YYYY-MM-DD')</if>
        <if test="stime != null and stime != ''">and w.coll_time&lt;= TO_DATE(#{stime},'YYYY-MM-DD')</if>
        <if test="orgIds != null and orgIds != ''">
            and substr(w.id,4,3) in (${orgIds})
        </if>
        <if test="adCode != null and adCode != ''">and w.AD_CODE like CONCAT(#{adCode}, '%')</if>
        <if test="type != null and type != ''">and w.obj_type in (${type})</if>
        group by to_char(w.COLL_TIME,'iw')
        order by name
    </select>

    <select id="countPblmTotalByWeek" parameterType="cn.com.goldenwater.dcproj.param.IndexPblmParam"
            resultType="cn.com.goldenwater.dcproj.dto.CountTime">
        select c.name,sum(c.value) over(order by c.name) as value from (
        select to_char(w.COLL_TIME,'w') AS NAME,count(1)as value from (

        <trim>
            <include refid="lyzSql"/>
        </trim>
        ) w where 1=1
        <if test="stime != null and stime != ''">and to_char(w.coll_time,'yyyy-MM') =
            to_char(TO_DATE(#{stime},'yyyy-MM-dd'),'yyyy-MM')
        </if>
        <if test="orgIds != null and orgIds != ''">
            and substr(w.id,4,3) in (${orgIds})
        </if>
        <if test="adCode != null and adCode != ''">and w.AD_CODE like CONCAT(#{adCode}, '%')</if>
        <if test="type != null and type != ''">and w.obj_type in (${type})</if>
        group by to_char(w.COLL_TIME,'w')
        order by name
        ) c

    </select>

    <select id="countPblmIncByMonth" parameterType="cn.com.goldenwater.dcproj.param.IndexPblmParam"
            resultType="cn.com.goldenwater.dcproj.dto.CountTime">
        select LTRIM(to_char(to_date(f.name,'yyyy-MM'),'MM'),'0') as name, f.value as value from (
        select to_char(to_date(m.monthlist,'yyyy-MM'),'yyyy-MM') as name,nvl(d.value,0) as value from (
        SELECT TO_CHAR(ADD_MONTHS(to_date(to_char(to_date(#{seltime}, 'yyyy-MM-dd'),'yyyy-MM'),'yyyy-MM'), ROWNUM - 1),
        'yyyy-MM') as monthlist
        FROM DUAL
        CONNECT BY ROWNUM &lt;=
        months_between(to_date(to_char(to_date(#{ettm}, 'yyyy-MM-dd'),'yyyy-MM'), 'yyyy-MM'),
        to_date(to_char(to_date(#{seltime}, 'yyyy-MM-dd'),'yyyy-MM'),'yyyy-MM')) + 1
        ) m left join (
        select to_char(COLL_TIME,'yyyy-MM') as name,count(*) as value from (

        <trim>
            <include refid="lyzSql"/>
        </trim>
        ) w where 1=1
        <if test="sttm != null and sttm != ''">and w.coll_time &gt;= TO_DATE(#{sttm},'YYYY-MM-DD')</if>
        <if test="ettm != null and ettm != ''">and to_char(w.coll_time,'yyyy-MM') &lt;=
            to_char(TO_DATE(#{ettm},'yyyy-MM-dd'),'yyyy-MM')
        </if>
        <if test="orgIds != null and orgIds != ''">
            and substr(w.id,4,3) in (${orgIds})
        </if>
        <if test="adCode != null and adCode != ''">and w.AD_CODE like CONCAT(#{adCode}, '%')</if>
        <if test="type != null and type != ''">and w.obj_type in (${type})</if>
        group by to_char(COLL_TIME,'yyyy-MM')
        ) d on m.monthlist = d.name
        order by m.monthlist
        ) f
    </select>

    <select id="countPblmTotalByMonth" parameterType="cn.com.goldenwater.dcproj.param.IndexPblmParam"
            resultType="cn.com.goldenwater.dcproj.dto.CountTime">
        select LTRIM(to_char(to_date(f.name,'yyyy-MM'),'MM'),'0') as name, f.value as value from (
        select c.name,sum(c.value) over(order by c.name) as value from (
        select to_char(to_date(m.monthlist,'yyyy-MM'),'yyyy-MM') as name,nvl(d.value,0) as value from (
        SELECT TO_CHAR(ADD_MONTHS(to_date(to_char(to_date(#{sttm}, 'yyyy-MM-dd'),'yyyy-MM'),'yyyy-MM'), ROWNUM - 1),
        'yyyy-MM') as monthlist
        FROM DUAL
        CONNECT BY ROWNUM &lt;=
        months_between(to_date(to_char(to_date(#{stime}, 'yyyy-MM-dd'),'yyyy-MM'), 'yyyy-MM'),
        to_date(to_char(to_date(#{sttm}, 'yyyy-MM-dd'),'yyyy-MM'),'yyyy-MM')) + 1
        ) m left join (
        select to_char(COLL_TIME,'yyyy-MM') as name,count(*) as value from (

        <trim>
            <include refid="lyzSql"/>
        </trim>
        ) w where 1=1
        <if test="sttm != null and sttm != ''">and w.coll_time&lt;= TO_DATE(#{stime},'YYYY-MM-DD')</if>
        <if test="stime != null and stime != ''">and to_char(w.coll_time,'yyyy-MM') &gt;=
            to_char(TO_DATE(#{sttm},'yyyy-MM-dd'),'yyyy-MM')
        </if>
        <if test="orgIds != null and orgIds != ''">
            and substr(w.id,4,3) in (${orgIds})
        </if>
        <if test="adCode != null and adCode != ''">and w.AD_CODE like CONCAT(#{adCode}, '%')</if>
        <if test="type != null and type != ''">and w.obj_type in (${type})</if>
        group by to_char(COLL_TIME,'yyyy-MM')
        ) d on m.monthlist = d.name
        ) c ) f
    </select>

    <select id="countPblmByTime" parameterType="cn.com.goldenwater.dcproj.param.IndexPblmParam" resultType="Integer">
        select count(*) from (
        <trim>
            <include refid="lyzSql"/>
        </trim>

        ) w where 1=1 and w.id is not null
        <if test="sttm != null and sttm != ''">and w.coll_time&gt;= TO_DATE(#{sttm},'YYYY-MM-DD')</if>
        <if test="ettm != null and ettm != ''">and w.coll_time &lt; TO_DATE(#{ettm},'YYYY-MM-DD')+1</if>
        <if test="orgIds != null and orgIds != ''">
            and substr(w.id,4,3) in (${orgIds})
        </if>
        <if test="adCode != null and adCode != ''">and w.AD_CODE like CONCAT(#{adCode}, '%')</if>
        <if test="type != null and type != ''">and w.obj_type in (${type})</if>
    </select>

    <sql id="provinceSql">
        <choose>
            <when test="province !=null and province !=''">
                and o.ad_code =#{province}
            </when>
            <otherwise>
                and o.ad_code is null
            </otherwise>
        </choose>
    </sql>

    <sql id="lyzSql">
        select OB.ADM_DIV AS AD_CODE,t.coll_time AS COLL_TIME,o.ID AS ID,t.obj_type from BIS_INSP_PBLM t
        left join bis_insp_all_obj o
        on t.obj_id = o.obj_id
        left join ATT_RS_BASE OB ON O.CODE = OB.RS_CODE where t.obj_type = 1
        <if test="quote !=null and quote !=''">
            and t.quote=#{quote}
        </if>
        <include refid="provinceSql"/>
        union all
        select o.code AS AD_CODE,t.coll_time AS COLL_TIME,o.ID AS ID,t.obj_type from BIS_INSP_PBLM t
        left join bis_insp_all_obj o
        on t.obj_id = o.obj_id where t.obj_type = 2
        <if test="quote !=null and quote !=''">
            and t.quote=#{quote}
        </if>
        <include refid="provinceSql"/>
        union all
        select OB.LOC_AD AS AD_CODE,t.coll_time AS COLL_TIME,o.ID AS ID,t.obj_type
        from BIS_INSP_PBLM t
        left join bis_insp_all_obj o
        on t.obj_id = o.obj_id
        left join BIS_INSP_WTDST OB ON O.CODE = OB.WTDST_ID where t.obj_type = 3
        <if test="quote !=null and quote !=''">
            and t.quote=#{quote}
        </if>
        <include refid="provinceSql"/>
        union all
        select OB.AD_CODE AS AD_CODE ,t.coll_time AS COLL_TIME,o.ID AS ID ,t.obj_type
        from BIS_INSP_PBLM t
        left join bis_insp_all_obj o
        on t.obj_id = o.obj_id
        left join ATT_EMPWTPRJ_BASE OB ON O.CODE = OB.ID where t.obj_type = 4
        <if test="quote !=null and quote !=''">
            and t.quote=#{quote}
        </if>
        <include refid="provinceSql"/>
        union all
        select OB.ADCD AS AD_CODE,t.coll_time AS COLL_TIME,o.ID AS ID,t.obj_type
        from BIS_INSP_PBLM t
        left join bis_insp_all_obj o
        on t.obj_id = o.obj_id
        left join ATT_GRW_BASE OB ON O.CODE = OB.STCD where t.obj_type = 5
        <if test="quote !=null and quote !=''">
            and t.quote=#{quote}
        </if>
        <include refid="provinceSql"/>
        union all
        select OB.AD_CODE AS AD_CODE,t.coll_time AS COLL_TIME,o.ID AS ID,t.obj_type
        from BIS_INSP_PBLM t
        left join bis_insp_all_obj o
        on t.obj_id = o.obj_id
        left join ATT_WAGA_BASE OB ON O.CODE = OB.OBJ_CODE where t.obj_type = 6
        <if test="quote !=null and quote !=''">
            and t.quote=#{quote}
        </if>
        <include refid="provinceSql"/>
        union all
        select OB.AD_CODE AS AD_CODE,t.coll_time AS COLL_TIME,o.ID AS ID,t.obj_type
        from BIS_INSP_PBLM t
        left join bis_insp_all_obj o
        on t.obj_id = o.obj_id
        left join ATT_EMPWTPRJ_BASE OB ON O.CODE = OB.ID where t.obj_type = 7
        <if test="quote !=null and quote !=''">
            and t.quote=#{quote}
        </if>
        <include refid="provinceSql"/>
        union all
        select OB.AD_CODE AS AD_CODE,t.intm AS COLL_TIME,o.ID AS ID,t.obj_type
        from tac_pblm_info t
        left join bis_insp_all_obj o
        on t.obj_id = o.obj_id
        left join tac_att_pawp_base OB ON O.CODE = OB.ID where t.obj_type = 8
        union all
        select OB.AD_CODE AS AD_CODE,t.coll_time AS COLL_TIME,o.ID AS ID,t.obj_type
        from BIS_INSP_PBLM t
        left join bis_insp_all_obj o
        on t.obj_id = o.obj_id
        left join att_fsc_base OB ON O.CODE = OB.ID where t.obj_type = 9
        <if test="quote !=null and quote !=''">
            and t.quote=#{quote}
        </if>
        <include refid="provinceSql"/>
        union all
        select OB.AD_CODE AS AD_CODE,t.coll_time AS COLL_TIME,o.ID AS ID,t.obj_type
        from BIS_INSP_PBLM t
        left join bis_insp_all_obj o
        on t.obj_id = o.obj_id
        left join att_other_base OB ON O.CODE = OB.ID where t.obj_type = 10
        <if test="quote !=null and quote !=''">
            and t.quote=#{quote}
        </if>
        <include refid="provinceSql"/>
        union all
        select OB.ADM_DIV AS AD_CODE,t.coll_time AS COLL_TIME,o.ID AS ID,t.obj_type
        from BIS_INSP_PBLM t
        left join bis_insp_all_obj o
        on t.obj_id = o.obj_id
        left join ATT_RS_BASE OB ON o.CODE = OB.RS_CODE where t.obj_type = 22
        <if test="quote !=null and quote !=''">
            and t.quote=#{quote}
        </if>
        <include refid="provinceSql"/>
    </sql>

    <select id="findSdPblmList" parameterType="cn.com.goldenwater.dcproj.param.SdPblmParam"
            resultMap="bisInspPblmResultMap">
        select w.* from (
        select ba.sd_name as rs_name,l.entm,l.sttm,j.code,j.nm,j.id as
        oid,j.ptype,r.PERS_NAME,p.*,s.insp_pblms_name,s.check_point,s.attach,s.sheet,s.sn_num,b.ad_full_name,b.ad_code,l.pnm
        as org_nm,j.id as org_id from (
        select * from BIS_INSP_PBLM pb where pb.obj_type=#{pType}
        <if test="ifCasePblm != null and ifCasePblm != ''">and pb.IF_CASE_PBLM = #{ifCasePblm}</if>
        <if test="hasVedio != null and hasVedio != ''">and pb.HAS_VEDIO = #{hasVedio}</if>
        <if test="startTime != null and startTime != ''">
            AND pb.COLL_TIME &gt;= TO_DATE(#{startTime},'YYYY-MM-DD')
        </if>
        <if test="quote !=null and quote !=''">
            and pb.quote=#{quote}
        </if>
        <if test="findTime != null and findTime != ''">and pb.COLL_TIME=TO_DATE(#{findTime},'YYYY-MM-DD')</if>
        <if test="reviConc != null and reviConc != ''">and pb.revi_conc = #{reviConc}</if>
        <if test="inspPblmCate != null and inspPblmCate != ''">and pb.INSP_PBLM_CATE = #{inspPblmCate}</if>
        <if test="endTime != null and endTime != ''">
            AND pb.COLL_TIME &lt; TO_DATE(#{endTime},'YYYY-MM-DD')+1
        </if>
        <if test="inspPblmName != null and inspPblmName != ''">and pb.INSP_PBLM_NAME like '%${inspPblmName}%'</if>
        <if test="objId != null and objId != ''">and pb.obj_Id = #{objId}</if>
        ) p left join (select * from bis_insp_all_obj bj where bj.ptype = #{pType}) j on p.obj_id = j.obj_id
        join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        left join bis_insp_rect_fdbk k on p.pblm_id=k.pblm_id and k.guid=p.FDBK_ID
        left join BIS_INSP_SD_RGSTR ba on ba.obj_id=j.obj_id
        left join att_ad_x_base b on ba.ad_code=b.ad_code
        left join OBJ_INSP_PBLMS s on p.pblms_type_id=s.guid
        left join BIS_INSP_ALL l on l.id=j.id
        ) w where w.ptype=#{pType}
        <if test="tabType =='2'.toString()">
            and w.entm &lt;to_date(#{nowTime},'yyyy-MM-dd')+1
        </if>
        <if test="tabType =='1'.toString()">
            and w.entm &gt;=to_date(#{nowTime},'yyyy-MM-dd')
        </if>
        and
        REGEXP_LIKE(
        W.OID,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )
        else (select 'non' from dual) end)
        ||')')
        <if test="nm != null and nm != ''">and w.nm like '%${nm}%'</if>
        <if test="orgNm != null and orgNm != ''">and w.org_nm like '%${orgNm}%'</if>
        <if test="sdName != null and sdName != ''">and w.rs_name like '%${sdName}%'</if>
        <if test="adFullName != null and adFullName != ''">and w.ad_full_name like '%${adFullName}%'</if>
        <if test="persName != null and persName != ''">and w.PERS_NAME like '%${persName}%'</if>
        <if test="codes != null and codes != ''">and w.CODE in (${codes})</if>
        <if test="recPers !=null and recPers !=''">
            and w.rec_pers=#{recPers}
        </if>
        <if test="adCodes != null and adCodes != ''">and w.AD_CODE in (${adCodes})</if>
        <if test="orgIds != null and orgIds != ''">and w.org_id in (${orgIds})</if>

        <if test="inspPblmType != null and inspPblmType != ''">and w.INSP_PBLM_TYPE = #{inspPblmType}</if>
        order by w.ad_code asc,w.coll_time desc
    </select>

    <select id="findSdPblmPage" resultType="cn.com.goldenwater.dcproj.dto.PblmRetCount"
            parameterType="cn.com.goldenwater.dcproj.param.CountParam">
        select e.sd_name as rsName,dd.code,e.SD_TYPE as eng_scal from att_sd_base e,(
        select distinct w.code from (
        select j.id as oid,j.ptype,j.code,p.insp_pblm_cate from BIS_INSP_PBLM p
        join (select * from BIS_INSP_ALL_OBJ o where 1=1
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        ) w where w.ptype=#{pType} and
        REGEXP_LIKE(
        W.OID,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )
        else (select 'non' from dual) end)
        ||')') ) dd where e.sd_code=dd.code
    </select>

    <select id="findSwhsPblmPage" resultType="cn.com.goldenwater.dcproj.dto.PblmRetCount"
            parameterType="cn.com.goldenwater.dcproj.param.CountParam">
        select e.swhs_name as rsName,dd.code,e.WAIN_WASO_TYPE as eng_scal from att_swhs_base e,(
        select distinct w.code from (
        select j.id as oid,j.ptype,j.code,p.insp_pblm_cate from BIS_INSP_PBLM p
        join (select * from BIS_INSP_ALL_OBJ o where o.ptype=#{pType}
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        ) w where w.ptype=#{pType} and
        REGEXP_LIKE(
        W.OID,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )
        else (select 'non' from dual) end)
        ||')') ) dd where e.swhs_code=dd.code
    </select>

    <select id="findJswaPblmPage" resultType="cn.com.goldenwater.dcproj.dto.PblmRetCount"
            parameterType="cn.com.goldenwater.dcproj.param.CountParam">
        select e.name as rsName,dd.code,'' as eng_scal from ATT_JSKEJIAN_WATER_BASE e,(
        select distinct w.code from (
        select j.id as oid,j.ptype,j.code,p.insp_pblm_cate from BIS_INSP_PBLM p
        join (select * from BIS_INSP_ALL_OBJ o where o.ptype=#{pType}
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        ) w where w.ptype=#{pType} and
        REGEXP_LIKE(
        W.OID,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )
        else (select 'non' from dual) end)
        ||')') ) dd where e.id=dd.code
    </select>

    <select id="findJsflPblmPage" resultType="cn.com.goldenwater.dcproj.dto.PblmRetCount"
            parameterType="cn.com.goldenwater.dcproj.param.CountParam">
        select e.name as rsName,dd.code,'' as eng_scal from ATT_JSKEJIAN_FLKDIS_BASE e,(
        select distinct w.code from (
        select j.id as oid,j.ptype,j.code,p.insp_pblm_cate from BIS_INSP_PBLM p
        join (select * from BIS_INSP_ALL_OBJ o where o.ptype=#{pType}
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        ) w where w.ptype=#{pType} and
        REGEXP_LIKE(
        W.OID,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )
        else (select 'non' from dual) end)
        ||')') ) dd where e.id=dd.code
    </select>

    <select id="findPrdPblmPage" resultType="cn.com.goldenwater.dcproj.dto.PblmRetCount"
            parameterType="cn.com.goldenwater.dcproj.param.CountParam">
        select e.name as rsName,dd.code,'' as eng_scal from ATT_PRD_CSTR_PROJ_BASE e,(
        select distinct w.code from (
        select j.id as oid,j.ptype,j.code,p.insp_pblm_cate from BIS_INSP_PBLM p
        join (select * from BIS_INSP_ALL_OBJ o where o.ptype=#{pType}
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        ) w where w.ptype=#{pType} and
        REGEXP_LIKE(
        W.OID,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )
        else (select 'non' from dual) end)
        ||')') ) dd where e.id=dd.code
    </select>

    <select id="findWiuPblmPage" resultType="cn.com.goldenwater.dcproj.dto.PblmRetCount"
            parameterType="cn.com.goldenwater.dcproj.param.CountParam">
        select e.wiu_name as rsName,dd.code,'' as eng_scal from att_wiu_base e,(
        select distinct w.code from (
        select j.id as oid,j.ptype,j.code,p.insp_pblm_cate from BIS_INSP_PBLM p
        join (select * from BIS_INSP_ALL_OBJ o where o.ptype=#{pType}
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        ) w where w.ptype=#{pType} and
        REGEXP_LIKE(
        W.OID,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
        id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        )
        else (select 'non' from dual) end)
        ||')') ) dd where e.id=dd.code
    </select>

    <select id="countSd" resultType="cn.com.goldenwater.dcproj.dto.PblmCount" parameterType="java.lang.String">
        select dd.*,b.SD_NAME as rs_name from ATT_SD_BASE b , (
        select a.code,count(a.code) as sub,insp_pblm_cate,count(insp_pblm_cate) as total from (
        select w.* from (
        select j.id as oid,j.ptype,j.code,p.insp_pblm_cate from BIS_INSP_PBLM p
        join (select * from BIS_INSP_ALL_OBJ o where o.ptype=#{pType}
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        ) w where w.ptype=#{pType} and w.code in(${codes})
        <if test="persId !=null and persId !=''">
            and
            REGEXP_LIKE(
            W.OID,'^('||
            ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
            <include refid="orgIdSql"/>
            )>0 then
            (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
            id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
            <include refid="orgIdSql"/>
            )
            else (select 'non' from dual) end)
            ||')')
        </if>
        ) a where a.insp_pblm_cate is not null group by a.code,a.insp_pblm_cate order by a.code ) dd where
        b.SD_CODE=dd.code
    </select>

    <select id="findPblmList" resultType="cn.com.goldenwater.dcproj.model.BisInspPblm"
            parameterType="cn.com.goldenwater.dcproj.param.PblmParam">
        SELECT DISTINCT W.* FROM (
        SELECT
        J.ID AS OID, J.ID AS ORG_ID,J.CODE, J.NM, J.NM AS RS_NAME, J.PTYPE,
        R.PERS_NAME,
        P.*, NVL(P.INSP_PBLM_NAME,P.PBLM_NAME_BZ) AS INSP_PBLMS_NAME,P.CHECK_POINT_BZ AS CHECK_POINT,
        P.PBLM_DESC_BZ AS PBLM_DESC,
        S.ATTACH,S.SHEET,S.SN_NUM,
        B.AD_FULL_NAME AS LOC,B.AD_FULL_NAME,B.AD_CODE,
        <if test='pType == "78"'>BAU.SEC_ID, BAU.ID AS UNIT_ID, BAU.NM AS UNIT_NM ,</if>
        L.PNM AS ORG_NM
        from (
        select * from BIS_INSP_PBLM pb
        <where>
            <if test="ifCasePblm != null and ifCasePblm != ''">and pb.IF_CASE_PBLM = #{ifCasePblm}</if>
            <if test="hasVedio != null and hasVedio != ''">and pb.HAS_VEDIO = #{hasVedio}</if>
            <if test="startTime != null and startTime != ''">AND pb.COLL_TIME &gt;= TO_DATE(#{startTime},'YYYY-MM-DD')</if>
            <if test="quote !=null and quote !=''">and pb.quote=#{quote}</if>
            <if test="findTime != null and findTime != ''">and pb.COLL_TIME=TO_DATE(#{findTime},'YYYY-MM-DD')</if>
            <if test="reviConc != null and reviConc != ''">and pb.revi_conc = #{reviConc}</if>
            <if test="inspPblmCate != null and inspPblmCate != ''">and pb.INSP_PBLM_CATE = #{inspPblmCate}</if>
            <if test="endTime != null and endTime != ''">AND pb.COLL_TIME &lt; TO_DATE(#{endTime},'YYYY-MM-DD')+1</if>
            <if test="inspPblmName != null and inspPblmName != ''">and pb.INSP_PBLM_NAME like '%${inspPblmName}%'</if>
            <if test="objId != null and objId != ''">and pb.OBJ_ID = #{objId}</if>
            <if test="regid != null and regid != ''">and pb.regid = #{regid}</if>
            <if test="cwsCode != null and cwsCode != ''">and pb.CWS_CODE = #{cwsCode}</if>
        </where>
        ) p
        JOIN (SELECT * FROM BIS_INSP_ALL_OBJ O WHERE O.PTYPE=#{pType}<include refid="choseSql"/>) J ON P.OBJ_ID =
        J.OBJ_ID
        LEFT JOIN BIS_INSP_ALL_RLATION_PERS R ON P.REC_PERS = R.GUID
        <choose>
            <when test='pType == "3"'>
                left join BIS_INSP_WTDST_TRACK ba on ba.obj_id=j.obj_id
            </when>
            <when test='pType == "4"'>
                left join BIS_INSP_KEY_REGISTER ba on ba.obj_id=j.obj_id
            </when>
            <when test='pType == "7"'>
                left join BIS_INSP_KEY_REGISTER ba on ba.obj_id=j.obj_id
            </when>
            <when test='pType == "11"'>
                left join BIS_INSP_SD_RGSTR ba on ba.obj_id=j.obj_id
            </when>
            <when test='pType == "12"'>
                left join bis_insp_wiu_rgstr ba on ba.obj_id=j.obj_id
            </when>
            <when test='pType == "13"'>
                left join bis_insp_swhs_rgstr ba on ba.obj_id=j.obj_id
            </when>
            <when test='pType == "14"'>
                LEFT JOIN BIS_INSP_WINT_RGSTR ba on ba.obj_id = j.obj_id
            </when>
            <when test='pType == "15"'>
                LEFT JOIN BIS_INSP_SVWT_AREA_RGSTR ba on ba.obj_id = j.obj_id
            </when>
            <when test='pType == "16"'>
                LEFT JOIN BIS_INSP_SVWT_WUNT_RGSTR ba on ba.obj_id = j.obj_id
            </when>
            <when test='pType == "17"'>
                LEFT JOIN BIS_INSP_OPRD_CSTR_PROJ_RGSTR ba on ba.obj_id = j.obj_id
            </when>
            <when test='pType == "18"'>
                LEFT JOIN ATT_JSKEJIAN_FLKDIS_RGSTR ba on ba.obj_id = j.obj_id
            </when>
            <when test='pType == "19"'>
                LEFT JOIN ATT_JSKEJIAN_WATER_RGSTR ba on ba.obj_id = j.obj_id
            </when>
            <when test='pType == "20"'>
                LEFT JOIN ATT_JSKEJIAN_ECOFRD_RGSTR ba on ba.obj_id = j.obj_id
            </when>
            <when test='pType == "22"'>
                LEFT JOIN BIS_INSP_RSML_RGSTR ba on ba.obj_id = j.obj_id
            </when>
            <when test='pType == "24"'>
                LEFT JOIN bis_insp_stnd_rgstr ba on ba.obj_id = j.obj_id
            </when>
            <when test='pType == "41"'>
                LEFT JOIN bis_zhejiang_vill_rgstr ba on ba.obj_id = j.obj_id
            </when>
            <when test='pType == "42"'>
                LEFT JOIN BIS_ZHEJIANG_JG_RGSTR ba on ba.obj_id = j.obj_id
            </when>
            <when test='pType == "26"'>
                LEFT JOIN BIS_INSP_RSFCO_RGSTR ba on ba.obj_id = j.obj_id
            </when>
            <when test='pType == "25"'>
                LEFT JOIN BIS_NEW_VILL_RGSTR ba on ba.obj_id = j.obj_id
            </when>
            <when test='pType == "27"'>
                LEFT JOIN BIS_INSP_MFDP_RGSTR ba on ba.obj_id = j.obj_id
            </when>
            <when test='pType == "28"'>
                LEFT JOIN BIS_INSP_IRR_RGSTR ba on ba.obj_id = j.obj_id
            </when>
            <when test='pType == "29"'>
                LEFT JOIN BIS_INSP_WAGA_SAP_RGSTR ba on ba.obj_id = j.obj_id
            </when>
            <when test='pType == "30"'>
                LEFT JOIN (select t.*,'' as ad_code from BIS_INSP_EFP_RGSTR t) ba on ba.obj_id = j.obj_id
            </when>
            <when test='pType == "31"'>
                LEFT JOIN BIS_INSP_WINTU_RGSTR ba on ba.obj_id = j.obj_id
            </when>
            <when test='pType == "32"'>
                LEFT JOIN BIS_INSP_WRM_RGSTR ba on ba.obj_id = j.obj_id
            </when>
            <when test='pType == "33"'>
                LEFT JOIN BIS_INSP_WTUNT_RGSTR ba on ba.obj_id = j.obj_id
            </when>
            <when test='pType == "34"'>
                LEFT JOIN BIS_INSP_rssfdr_RGSTR ba on ba.obj_id = j.obj_id
            </when>
            <when test='pType == "35"'>
                LEFT JOIN BIS_INSP_samrmp_RGSTR ba on ba.obj_id = j.obj_id
            </when>
            <when test='pType == "37"'>
                LEFT JOIN BIS_RDWSP_RGSTR ba on ba.obj_id = j.obj_id
            </when>
            <when test='pType == "53"'>
                LEFT JOIN BIS_INSP_WRWS_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID
            </when>
            <when test='pType == "54"'>
                LEFT JOIN BIS_INSP_WTCRR_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID
            </when>
            <when test='pType == "55"'>
                LEFT JOIN BIS_INSP_WTGT_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID
            </when>
            <when test='pType == "56"'>
                LEFT JOIN BIS_INSP_WRWX_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID
            </when>
            <when test='pType == "57"'>
                LEFT JOIN BIS_INSP_WTGTH_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID
            </when>
            <when test='pType == "62"'>
                LEFT JOIN BIS_INSP_CDEP_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID
            </when>
            <when test='pType == "67"'>
                LEFT JOIN BIS_INSP_BID_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID
            </when>
            <when test='pType == "68"'>
                LEFT JOIN BIS_INSP_RLRW_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID
            </when>
            <when test='pType == "69"'>
                LEFT JOIN BIS_INSP_DRDMP_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID
            </when>
            <when test='pType == "70"'>
                LEFT JOIN BIS_INSP_GROW_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID
            </when>
            <when test='pType == "71"'>
                LEFT JOIN BIS_INSP_VILLQH_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID
            </when>
            <when test='pType == "72"'>LEFT JOIN BIS_INSP_WIUQH_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "73"'>LEFT JOIN BIS_INSP_HYST_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "74"'>LEFT JOIN BIS_INSP_SAFETY_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "75"'>LEFT JOIN BIS_INSP_YNDRDMP_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "76"'>LEFT JOIN BIS_INSP_DPGM_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "77"'>LEFT JOIN BIS_INSP_RVGM_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "78"'>
                LEFT JOIN BIS_INSP_WTPRJ_REGISTER BA ON BA.OBJ_ID = J.OBJ_ID
                LEFT JOIN BIS_INSP_WTPRJ_UNIT BAU ON P.regid = BAU.ID
            </when>
            <when test='pType == "79"'>LEFT JOIN BIS_INSP_VILLGD BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "80"'>LEFT JOIN BIS_INSP_RSLKBK_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "81"'>LEFT JOIN BIS_INSP_WAGAJS_BDG BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "82"'>LEFT JOIN BIS_INSP_WTGOV_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "83"'>LEFT JOIN BIS_INSP_CHMCLS_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "84"'>LEFT JOIN BIS_INSP_RSSFGD_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "89"'>LEFT JOIN BIS_INSP_UNWT_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "91"'>LEFT JOIN BIS_INSP_PRDQH_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "93"'>LEFT JOIN BIS_INSP_FLOODQH BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "94"'>LEFT JOIN BIS_INSP_MFDPQH_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "95"'>LEFT JOIN BIS_INSP_DSTFLDQH BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "96"'>LEFT JOIN BIS_INSP_RSFCOQH_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "97"'>LEFT JOIN BIS_INSP_VILL2021_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "98"'>LEFT JOIN BIS_INSP_FUND_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "99"'>LEFT JOIN BIS_INSP_KEYSAFQH_REGISTER BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "100"'>LEFT JOIN BIS_INSP_KEYCHKQH_REGISTER BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "101"'>LEFT JOIN BIS_INSP_SVWTQH BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "104"'>LEFT JOIN BIS_INSP_STSTN BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "105"'>LEFT JOIN BIS_INSP_QYMTEN BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "106"'>LEFT JOIN BIS_INSP_RSVRYN_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "108"'>LEFT JOIN BIS_INSP_XJVILL BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "110"'>LEFT JOIN BIS_INSP_FJWTENF BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "112"'>LEFT JOIN BIS_INSP_WIUJS_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "113"'>LEFT JOIN BIS_INSP_SWHSJS BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "115"'>LEFT JOIN BIS_INSP_RSVR_SD BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "116"'>LEFT JOIN BIS_INSP_HYSTP BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "117"'>LEFT JOIN BIS_INSP_RDWPGD_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "119"'>LEFT JOIN BIS_INSP_FJQA BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "120"'>LEFT JOIN BIS_INSP_JSPWIU BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <when test='pType == "126"'>LEFT JOIN BIS_INSP_JSPWIU2024 BA ON BA.OBJ_ID = J.OBJ_ID</when>
            <otherwise>LEFT JOIN BIS_INSP_GNRL_RGSTR BA ON BA.OBJ_ID = J.OBJ_ID</otherwise>
        </choose>
        LEFT JOIN ATT_AD_X_BASE B ON BA.AD_CODE = B.AD_CODE
        LEFT JOIN OBJ_INSP_PBLMS S ON P.PBLMS_TYPE_ID = S.GUID
        LEFT JOIN BIS_INSP_ALL L ON L.ID = J.ID
        where 1=1
        <if test="tabType =='2'.toString()">
            AND L.ENTM &lt; TO_DATE(#{nowTime},'yyyy-MM-dd')+1
        </if>
        <if test="tabType =='1'.toString()">
            AND L.ENTM &gt;= TO_DATE(#{nowTime},'yyyy-MM-dd')
        </if>
        ) w
        WHERE REGEXP_LIKE(
        W.OID,'^('||
        ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID = #{persId}
        <include refid="orgIdSql"/>
        <choose>
            <when test="pType.length() == 1">
                and id like concat('00','${pType}%')
            </when>
            <when test="pType.length() == 2">
                and id like concat('0','${pType}%')
            </when>
            <otherwise>
                and id like '${pType}%'
            </otherwise>
        </choose>
        )>0 then
        (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
        <include refid="orgIdSql"/>
        <choose>
            <when test="pType.length() == 1">
                and id like concat('00','${pType}%')
            </when>
            <when test="pType.length() == 2">
                and id like concat('0','${pType}%')
            </when>
            <otherwise>
                and id like '${pType}%'
            </otherwise>
        </choose>
        )
        else (select 'non' from dual) end) ||')')
        <if test="nm != null and nm != ''">and w.nm like '%${nm}%'</if>
        <if test="newVillName != null and newVillName != ''">and w.NEW_VILL_NAME like '%${newVillName}%'</if>
        <if test="orgNm != null and orgNm != ''">and w.org_nm like '%${orgNm}%'</if>
        <if test="adFullName != null and adFullName != ''">and w.ad_full_name like '%${adFullName}%'</if>
        <if test="persName != null and persName != ''">and w.PERS_NAME like '%${persName}%'</if>
        <if test="codes != null and codes != ''">and w.CODE in (${codes})</if>
        <if test="recPers !=null and recPers !=''">and w.rec_pers=#{recPers}</if>
        <if test="adCodes != null and adCodes != ''">and w.AD_CODE in (${adCodes})</if>
        <!-- 福建省 综合展示 lxf -->
        <if test="adCode != null and adCode != ''">and w.AD_CODE like '${adCode}%'</if>
        <if test="orgIds != null and orgIds != ''">and w.org_id in (${orgIds})</if>
        <if test="inspPblmType != null and inspPblmType != ''">and w.INSP_PBLM_TYPE = #{inspPblmType}</if>
        <if test="bplmsTypeId != null and bplmsTypeId != ''">and w.PBLMS_TYPE_ID = #{bplmsTypeId}</if>
        <if test="secId != null and secId != ''">and w.SEC_ID = #{secId}</if>
        <if test="inspPblmsName != null and inspPblmsName != ''">and w.INSP_PBLMS_NAME Like '%${inspPblmsName}%'</if>
        <if test="checkPoint != null and checkPoint != ''">and w.CHECK_POINT_BZ Like '%${checkPoint}%'</if>
        <if test="state != null and state != ''">and w.STATE = #{state}</if>
        order by w.ad_code asc, w.code asc, w.coll_time desc
    </select>

    <select id="countSwhs" resultType="cn.com.goldenwater.dcproj.dto.PblmCount" parameterType="java.lang.String">
        select dd.*,b.SWHS_NAME as rs_name from ATT_SWHS_BASE b , (
        select a.code,count(a.code) as sub,insp_pblm_cate,count(insp_pblm_cate) as total from (
        select w.* from (
        select j.id as oid,j.ptype,j.code,p.insp_pblm_cate from BIS_INSP_PBLM p
        join (select * from BIS_INSP_ALL_OBJ o where o.ptype=#{pType}
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        ) w where w.ptype=#{pType} and w.code in(${codes})
        <if test="persId !=null and persId !=''">
            and
            REGEXP_LIKE(
            W.OID,'^('||
            ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
            <include refid="orgIdSql"/>
            )>0 then
            (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
            id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
            <include refid="orgIdSql"/>
            )
            else (select 'non' from dual) end)
            ||')')
        </if>
        ) a where a.insp_pblm_cate is not null group by a.code,a.insp_pblm_cate order by a.code ) dd where b.ID=dd.code
    </select>

    <select id="countJsfl" resultType="cn.com.goldenwater.dcproj.dto.PblmCount" parameterType="java.lang.String">
        select dd.*,b.name as rs_name from ATT_JSKEJIAN_FLKDIS_BASE b , (
        select a.code,count(a.code) as sub,insp_pblm_cate,count(insp_pblm_cate) as total from (
        select w.* from (
        select j.id as oid,j.ptype,j.code,p.insp_pblm_cate from BIS_INSP_PBLM p
        join (select * from BIS_INSP_ALL_OBJ o where o.ptype=#{pType}
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        ) w where w.ptype=#{pType} and w.code in(${codes})
        <if test="persId !=null and persId !=''">
            and
            REGEXP_LIKE(
            W.OID,'^('||
            ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
            <include refid="orgIdSql"/>
            )>0 then
            (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
            id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
            <include refid="orgIdSql"/>
            )
            else (select 'non' from dual) end)
            ||')')
        </if>
        ) a where a.insp_pblm_cate is not null group by a.code,a.insp_pblm_cate order by a.code ) dd where b.ID=dd.code
    </select>

    <select id="countJswa" resultType="cn.com.goldenwater.dcproj.dto.PblmCount" parameterType="java.lang.String">
        select dd.*,b.name as rs_name from ATT_JSKEJIAN_WATER_BASE b , (
        select a.code,count(a.code) as sub,insp_pblm_cate,count(insp_pblm_cate) as total from (
        select w.* from (
        select j.id as oid,j.ptype,j.code,p.insp_pblm_cate from BIS_INSP_PBLM p
        join (select * from BIS_INSP_ALL_OBJ o where o.ptype=#{pType}
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        ) w where w.ptype=#{pType} and w.code in(${codes})
        <if test="persId !=null and persId !=''">
            and
            REGEXP_LIKE(
            W.OID,'^('||
            ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
            <include refid="orgIdSql"/>
            )>0 then
            (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
            id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
            <include refid="orgIdSql"/>
            )
            else (select 'non' from dual) end)
            ||')')
        </if>
        ) a where a.insp_pblm_cate is not null group by a.code,a.insp_pblm_cate order by a.code ) dd where b.ID=dd.code
    </select>

    <select id="countPrd" resultType="cn.com.goldenwater.dcproj.dto.PblmCount" parameterType="java.lang.String">
        select dd.*,b.name as rs_name from ATT_PRD_CSTR_PROJ_BASE b , (
        select a.code,count(a.code) as sub,insp_pblm_cate,count(insp_pblm_cate) as total from (
        select w.* from (
        select j.id as oid,j.ptype,j.code,p.insp_pblm_cate from BIS_INSP_PBLM p
        join (select * from BIS_INSP_ALL_OBJ o where o.ptype=#{pType}
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        ) w where w.ptype=#{pType} and w.code in(${codes})
        <if test="persId !=null and persId !=''">
            and
            REGEXP_LIKE(
            W.OID,'^('||
            ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
            <include refid="orgIdSql"/>
            )>0 then
            (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
            id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
            <include refid="orgIdSql"/>
            )
            else (select 'non' from dual) end)
            ||')')
        </if>
        ) a where a.insp_pblm_cate is not null group by a.code,a.insp_pblm_cate order by a.code ) dd where b.ID=dd.code
    </select>

    <select id="countWiu" resultType="cn.com.goldenwater.dcproj.dto.PblmCount" parameterType="java.lang.String">
        select dd.*,b.WIU_NAME as rs_name from ATT_WIU_BASE b , (
        select a.code,count(a.code) as sub,insp_pblm_cate,count(insp_pblm_cate) as total from (
        select w.* from (
        select j.id as oid,j.ptype,j.code,p.insp_pblm_cate from BIS_INSP_PBLM p
        join (select * from BIS_INSP_ALL_OBJ o where o.ptype=#{pType}
        <include refid="choseSql"/>
        ) j on p.obj_id=j.obj_id
        join BIS_INSP_ALL_RLATION_PERS r on p.rec_pers=r.guid
        ) w where w.ptype=#{pType} and w.code in(${codes})
        <if test="persId !=null and persId !=''">
            and
            REGEXP_LIKE(
            W.OID,'^('||
            ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
            <include refid="orgIdSql"/>
            )>0 then
            (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
            id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId}
            <include refid="orgIdSql"/>
            )
            else (select 'non' from dual) end)
            ||')')
        </if>
        ) a where a.insp_pblm_cate is not null group by a.code,a.insp_pblm_cate order by a.code ) dd where b.ID=dd.code
    </select>

    <select id="getObjPblmList" parameterType="cn.com.goldenwater.dcproj.param.BisInspPblmParam"
            resultType="cn.com.goldenwater.dcproj.model.BisInspPblm">
        select
        p.*,s.check_point,s.pblm_desc
        from bis_insp_pblm p
        left join OBJ_INSP_PBLMS s on p.pblms_type_id=s.guid
        where obj_type = #{objType}
        <!-- and PBLMS_TYPE_ID in (${pblmsTypeId}) -->
        <if test="objId != null and objId != ''">
            and obj_id = #{objId}
        </if>
        <choose>
            <when test="orgId !=null and orgId !=''">
                AND ORG_ID=#{orgId}
            </when>
            <otherwise>
                AND ORG_ID is null
            </otherwise>
        </choose>
    </select>

    <select id="getPblmList" resultMap="bisInspPblmResultMap"
            parameterType="cn.com.goldenwater.dcproj.model.BisInspPblm">
        select t.*,x.ad_full_name,r.rsvr_nm as rsName,r.RGSTR_ID,p.check_point,p.pblm_desc from BIS_INSP_PBLM t left
        join obj_insp_pblms p on t.pblms_type_id = p.guid
        <if test='objType == "1"'>
            left join Bis_Insp_Rsvr_Rgstr
        </if>
        r on t.regid = r.rgstr_id
        left join att_ad_x_base x on r.ad_code = x.ad_code
        where p.guid is not null and t.obj_type = #{objType}
        and t.regid in (${rgstrId})
    </select>

    <select id="getPblmListByRegId" parameterType="cn.com.goldenwater.dcproj.param.BisInspPblmParam"
            resultType="cn.com.goldenwater.dcproj.model.BisInspPblm">
        select t.*,x.ad_full_name,r.rsvr_nm as rsName,r.RGSTR_ID,p.check_point,p.pblm_desc from BIS_INSP_PBLM t left
        join obj_insp_pblms p on t.pblms_type_id = p.guid
        <if test='objType == "1"'>
            left join Bis_Insp_Rsvr_Rgstr
        </if>
        r on t.regid = r.rgstr_id
        left join att_ad_x_base x on r.ad_code = x.ad_code
        where t.obj_type = #{objType}
        ${regid}
    </select>

    <update id="updatePblmStateByObjId" parameterType="cn.com.goldenwater.dcproj.param.BisInspPblmParam">
        UPDATE BIS_INSP_PBLM t
        set t.state = #{state}
        where t.obj_id = #{objId}
        <if test="objType != null and objType != ''">
            and t.obj_type = #{objType}
        </if>
    </update>
    <update id="updatePblmState" parameterType="cn.com.goldenwater.dcproj.param.BisInspPblmParam">
        UPDATE BIS_INSP_PBLM t set t.state = #{state} where t.obj_id = #{objId} and t.obj_type = #{objType}
    </update>
    <select id="getPblmListByTm" resultType="cn.com.goldenwater.dcproj.dto.BisInspTaskTotal">
        select count(a.pblm_id)pblmSize from bis_insp_pblm A JOIN BIS_INSP_ALL_OBJ B ON A.OBJ_ID=B.OBJ_ID where
        a.obj_type=#{objType} and a.coll_time&gt;=#{stTm} and a.coll_time&lt;#{enTm} and substr(b.code,1,6)=#{adCode}
    </select>

    <select id="getPblmPath" resultType="String">
        select FILE_PATH
        from GW_COM_FILE WHERE BIZ_ID = #{pblmId}
    </select>

    <!-- 批量导入口径查询 -->
    <select id="getPblmListFromImport" parameterType="cn.com.goldenwater.dcproj.param.PblmParam" resultMap="bisInspPblmResultMap">
        SELECT
            t2.ad_full_name,
            t1.PBLM_ID,
            t1.obj_code,
            t1.obj_name,
            t1.check_point_bz,
            t1.INSP_PBLM_TYPE,
            t1.insp_pblm_name,
            t1.INSP_PBLM_DESC,
            t1.PBLM_DESC_BZ,
            t1.INSP_PBLM_CATE,
            t1.IF_CASE_PBLM,
            t3.pers_name,
            t1.coll_time,
            t1.has_vedio
        FROM bis_insp_pblm t1
        LEFT JOIN ATT_AD_X_BASE t2 on t1.obj_ad_code = t2.ad_code
        LEFT JOIN BIS_INSP_ALL_RLATION_PERS t3 ON t1.rec_pers = t3.guid
        LEFT JOIN bis_insp_all_obj t4 ON t1.obj_id = t4.obj_id
        WHERE t1.obj_type = '999'
        <if test="province != null and province != ''">
            <choose>
                <when test="province !=null and province !=''">
                    and t1.obj_ad_code like '${province}%'
                </when>
                <otherwise>
                    and t1.obj_ad_code is null
                </otherwise>
            </choose>
        </if>
        <if test="adFullName != null and adFullName != '' ">
            AND t2.ad_full_name like '%${adFullName}%'
        </if>
        <if test="objName != null and objName != '' ">
            AND t1.obj_name like '%${objName}%'
        </if>
        <if test="inspPblmName != null and inspPblmName != '' ">
            AND t1.insp_pblm_name like '%${inspPblmName}%'
        </if>
        <if test="recPers != null and recPers != '' ">
            AND t1.rec_pers like '%{recPers}%'
        </if>
        <if test="persName != null and persName != '' ">
            AND t3.pers_name like '%${persName}%'
        </if>
        <if test="startTime != null and startTime != '' ">
            AND t1.coll_time &gt; TO_DATE(#{startTime},'YYYY-MM-DD')
        </if>
        <if test="endTime != null and endTime != '' ">
            AND t1.coll_time &lt; TO_DATE(#{endTime},'YYYY-MM-DD')
        </if>
    </select>

    <!-- 批量导入 -->
    <insert id="insertByList" parameterType="java.util.ArrayList">
        INSERT ALL
        <foreach collection="list" item="item">
            INTO bis_insp_pblm
            (PBLM_ID, OBJ_ID,OBJ_TYPE, OBJ_AD_CODE, INSP_PBLM_NAME, PBLM_NAME_BZ, INSP_PBLM_DESC,
            IF_CASE_PBLM, DATA_STAT, COLL_TIME, REC_PERS, obj_name, CHECK_POINT_BZ,
            PBLM_DESC_BZ, INSP_PBLM_CATE, has_vedio, state )
            VALUES
            (
            #{item.pblmId},#{item.objId},#{item.objType},#{item.objAdCode},#{item.inspPblmName},#{item.pblmNameBz},#{item.inspPblmDesc},
            #{item.ifCasePblm}, 0, #{item.collTime},#{item.recPers},#{item.objName},#{item.checkPointBz},
            #{item.pblmDescBz}, #{item.inspPblmCate},#{item.hasVedio},#{item.state}
            )
        </foreach>
        select * from DUAL
    </insert>

    <delete id="batchDelete" parameterType="java.util.ArrayList">
        delete from bis_insp_pblm where PBLM_ID in
        <foreach collection="list" item="item" open="(" close=")" separator=",">
            #{item.pblmId}
        </foreach>
    </delete>

    <select id="indexTopList" resultMap="bisInspPblmResultMap"
            parameterType="cn.com.goldenwater.dcproj.param.BisInspPblmParam">
        SELECT * FROM  (SELECT
        P.*, O.NM, O.OBJ_AD_CODE AS AD_CODE,
        AAB.AD_NAME, AAB.AD_FULL_NAME,  A.PNM, T.NAME AS OBJ_TYPE_NAME
        FROM BIS_INSP_PBLM P
        JOIN BIS_INSP_ALL_OBJ O on P.OBJ_ID = O.OBJ_ID
        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 P.OBJ_TYPE = T.PTYPE
        LEFT JOIN ATT_AD_BASE AAB on O.OBJ_AD_CODE = AAB.AD_CODE
        LEFT JOIN BIS_INSP_ALL A on A.ID = O.ID
        <where>
            <if test="orgId != null and orgId != '' ">O.ID LIKE '___${orgId}%'</if>
            <if test="objType != null and objType != ''">and P.OBJ_TYPE = #{objType}</if>
            <if test="inspPblmCate != null and inspPblmCate != ''">and P.INSP_PBLM_CATE = #{inspPblmCate}</if>
            <if test="sttm != null and sttm != '' and entm != null and entm != '' ">
                AND P.COLL_TIME &gt;= TO_DATE(#{sttm}, 'YYYY-MM-DD')
                AND P.COLL_TIME &lt; TO_DATE(#{entm}, 'YYYY-MM-DD') + 1
            </if>
            <if test="adCode != null and adCode != ''">and O.OBJ_AD_CODE like '${adCode}%'</if>
            <!-- 福建省 福州市排除 平潭县 把平潭县 归属到 平潭综合实验区-->
            <if test='adCode != null and adCode == "3501" '>AND SUBSTR(O.OBJ_AD_CODE, 0, 6) !='350128'</if>
            <if test='adCode != null and adCode == "3510" '>AND SUBSTR(O.OBJ_AD_CODE, 0, 6) ='350128'</if>
        </where>
        ORDER BY P.COLL_TIME desc
        ) w where  ROWNUM &lt;= ${pageSize}



    </select>

</mapper>