fj-dc/.svn/pristine/e4/e4e6b6b03345fd5d0151bca61a40b98d894bf1e8.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.BisInspStatDao">

    <select id="getDcGroupInfo" resultType="cn.com.goldenwater.dcproj.dto.BisInspStatGroupInfoDto">
       select  a.*,b.id depId,b.name depNm,(select count(*) from bis_insp_all_rlation t where t.id=a.id )persCoun
       from bis_insp_all A LEFT JOIN bis_insp_all_tran B ON SUBSTR(A.ID,4,3)=B.ID where 1=1
         AND A.INTM &gt;= TO_DATE(#{stTm},'YYYY-MM-DD') AND A.INTM &lt; TO_DATE(#{enTm},'YYYY-MM-DD') + 1
        and b.id  in ('027') order by b.id,a.id
    </select>

    <select id="getRsvrInfoList" resultType="cn.com.goldenwater.dcproj.dto.BisInspStatRsvrInfoDto">
      select * from VIEW_RSVR_LIST where groupId in (${id})
    </select>
    <select id="getGroupInfo" resultType="cn.com.goldenwater.dcproj.dto.BisInspStatGroupInfoDto">
       select  a.*,b.id depId,b.name depNm,(select count(*) from bis_insp_all_rlation t where t.id=a.id )persCoun
       from bis_insp_all A LEFT JOIN bis_insp_all_tran B ON SUBSTR(A.ID,4,3)=B.ID where 1=1
        and length(a.id) = 12
        and to_char(a.sttm,'yyyy') = #{year} and to_char(a.sttm,'MM') = #{month}
        and a.id like '${type}%'
       <if test="persId != null and persId != ''">
           and
           REGEXP_LIKE(
           A.Id,'^('||
           ( case when(SELECT count(id) from BIS_INSP_ALL_RLATION WHERE PERSID =#{persId} and id like '${type}%')>0 then
           (SELECT LISTAGG(id, '|') WITHIN GROUP(ORDER BY PERSID) as
           id FROM BIS_INSP_ALL_RLATION WHERE PERSID =#{persId} and id like '${type}%')
           else (select 'non' from dual) end)
           ||')')
       </if>
    </select>
    <select id="getBsGroupInfo" resultType="cn.com.goldenwater.dcproj.dto.BisInspStatGroupInfoDto">
       select  a.*,b.id depId,b.name depNm,(select count(*) from bis_insp_all_rlation t where t.id=a.id )persCoun
       from bis_insp_all A LEFT JOIN bis_insp_all_tran B ON SUBSTR(A.ID,4,3)=B.ID where 1=1
         AND A.INTM &gt;= TO_DATE(#{stTm},'YYYY-MM-DD') AND A.INTM &lt; TO_DATE(#{enTm},'YYYY-MM-DD') + 1
        and b.id  in ('020','021','022','023','024','025','026') order by b.id,a.id
    </select>
    <select id="getDcRegsitInfo" resultType="cn.com.goldenwater.dcproj.dto.BisInspStatGroupInfoDto">
       select  a.*,b.id depId,b.name depNm,(select count(*) from bis_insp_all_rlation t where t.id=a.id )persCoun
       from bis_insp_all A LEFT JOIN bis_insp_all_tran B ON SUBSTR(A.ID,4,3)=B.ID where 1=1
         AND A.INTM &gt;= TO_DATE(#{stTm},'YYYY-MM-DD') AND A.INTM &lt; TO_DATE(#{enTm},'YYYY-MM-DD') + 1
        and b.id  in ('020','021','022','023','024','025','026') order by b.id,a.id
    </select>

    <select id="getBigPblmEmpwtNm" resultType="cn.com.goldenwater.dcproj.model.KeyRegPblm">
        SELECT
        CWS_CODE,
        HAS_VEDIO,
        PROBLEM_TYPE,
        QUALITY_TYPE_ID,
        DEFECT_TYPE_ID,
        VILLAGE_CODE,
        VILL_TYPE,
        PBLMS_TYPE_ID,
        REGID,
        COMMON_FILE_IDS,
        FILE_NO,
        FILE_NO_NUMBER,
        PBLMS_ID,
        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,
        m.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,
        ( 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,
        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,
        u.NATURE,
        bi.nm,
        s.nm AS sec_name
        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
        LEFT JOIN bis_insp_key_register_section mo ON u.sec_id = mo.id
        LEFT JOIN BIS_INSP_KEY_REGISTER bi ON mo.reg_Id = bi.id
        LEFT JOIN BIS_INSP_KEY_REGISTER_SECTION s ON u.sec_id = s.id
        WHERE
        m.IF_CASE_PBLM = '1'
        and m.COLL_TIME &gt;= TO_DATE(#{stTm},'yyyy-mm-dd')
        and m.COLL_TIME &lt;  TO_DATE(#{enTm},'yyyy-mm-dd') + 1
        and bi.nm is not null
    </select>


    <!--水库-->
    <!--获取总共督查了多少水库-->
    <select id="getReverCount" resultType="java.lang.Integer">
        select COUNT(1) AS count from BIS_INSP_ALL_OBJ A join BIS_INSP_RSVR_RGSTR B on A.Obj_Id = B.OBJ_ID
        <if test="engScal != null and engScal !='' ">
            where b.eng_scal = #{engScal}
        </if>
    </select>
    <!--获取总共发现了多少问题-->
    <select id="getReverPblmCount" resultType="java.lang.Integer">
        select count(1) from (select * from BIS_INSP_PBLM pb where pb.obj_type=1) p
        join (select * from BIS_INSP_ALL_OBJ bj where bj.pType=1) j on p.obj_id=j.obj_id
        left join BIS_INSP_RSVR_RGSTR b on j.obj_id = b.obj_id
        <if test="engScal != null and engScal !='' ">
            where b.eng_scal = #{engScal}
        </if>
    </select>
    <!--获取总共多少人-->
    <select id="getPersonCount" resultType="java.lang.Integer">
        select count(1) from bis_insp_all_rlation t
        <if test="id != null and id !='' ">
            where t.id in (${id})
        </if>
    </select>

    <!--水闸-->
    <!--获取总共督查了多少座水闸-->
    <select id="getWagaCount" resultType="java.lang.Integer">
        select COUNT(1) AS count from BIS_INSP_ALL_OBJ A join Att_Waga_Rgstr B on A.Obj_Id = B.OBJ_ID
    </select>
    <!--获取总共发现了多少问题-->
    <select id="getWagaPblmCount" resultType="java.lang.Integer">
        select count(1) from (select * from BIS_INSP_PBLM pb where pb.obj_type=6) p
        join (select * from BIS_INSP_ALL_OBJ bj where bj.pType=6) j on p.obj_id=j.obj_id
        left join Att_Waga_Rgstr b on j.obj_id = b.obj_id
    </select>

    <!--水毁-->
    <!--获取总共督查了多少座水毁-->
    <select id="getTrackCount" resultType="java.lang.Integer">
        select count(1) from BIS_INSP_WTDST A join BIS_INSP_ALL_OBJ b on A.WTDST_ID = B.CODE
    </select>
    <!--获取总共发现了多少问题-->
    <select id="getTrackPblmCount" resultType="java.lang.Integer">
        select count(1) from (select * from BIS_INSP_PBLM pb where pb.obj_type=3) p
        join (select * from BIS_INSP_ALL_OBJ bj where bj.pType=3) j on p.obj_id=j.obj_id
        left join BIS_INSP_WTDST b on j.code = B.WTDST_ID
    </select>
</mapper>