<?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="com.zjrs.zwnw.psam.entity.RsPsamFfxxbMapper">

    <select id="getList" parameterType="com.zjrs.zwnw.psam.dto.PsamKglDTO" resultType="map">
        SELECT b.rs_psam_rkxxb_lsh "rsPsamRkxxbLsh",b.psambh "psambh",b.klx "klx",b.kzt "kzt",
               NVL(e.jgmc,(SELECT aa.aab004 FROM ab01 aa WHERE aa.ztid=c.ztid AND ROWNUM=1)) "dwmc",
               NVL(e.jgdz,(SELECT aa.AAE006 FROM ab01 aa WHERE aa.ztid=c.ztid AND ROWNUM=1)) "dwdz",
               c.jglx "jglx",(SELECT t.user_name FROM s_sg_user t WHERE t.id=b.zhxgr) "zhxgr"
        FROM RS_PSAM_RKXXB b,RS_PSAM_YWXX_SQB c,RS_PSAM_FFXXB e
        WHERE b.rs_psam_ffxxb_lsh=e.rs_psam_ffxxb_lsh(+)
        AND b.rs_psam_ywxx_sqb_lsh=c.rs_psam_ywxx_sqb_lsh(+)
        <if test="dto.psambh != null and dto.psambh != ''">
            and b.psambh = #{ dto.psambh }
        </if>
        <if test="dto.kzt != null and dto.kzt != ''">
            and b.kzt = #{ dto.kzt }
        </if>
        ORDER BY b.kzt,b.psambh
    </select>
    <select id="kdwwhGetList" parameterType="com.zjrs.zwnw.psam.dto.PsamKglDTO" resultType="map">
        SELECT b.rs_psam_rkxxb_lsh "rsPsamRkxxbLsh",b.psambh "psambh",b.klx "klx",b.kzt "kzt",
        aa.aab004 "dwmc",aa.AAE006 "dwdz",c.jglx "jglx",
        (SELECT t.user_name FROM s_sg_user t WHERE t.id=b.zhxgr) "zhxgr"
        FROM RS_PSAM_RKXXB b,RS_PSAM_YWXX_SQB c,ab01 aa
        WHERE b.rs_psam_ywxx_sqb_lsh=c.rs_psam_ywxx_sqb_lsh 
        <if test="dto.dwmc != null and dto.dwmc != ''">
            AND aa.aab004 like '%${ dto.dwmc }%'
        </if>
        <if test="dto.psambh != null and dto.psambh != ''">
            and b.psambh = #{ dto.psambh }
        </if>
        ORDER BY b.kzt,b.psambh
    </select>
    <select id="kdwwhGetInfo" parameterType="com.zjrs.zwnw.psam.dto.PsamKglDTO" resultType="map">
        SELECT c.rs_psam_ywxx_sqb_lsh "rsPsamYwxxSqbLsh",b.psambh "psambh",b.klx "klx",b.kzt "kzt",
               aa.aab004 "dwmc",aa.AAE006 "dwdz",c.jglx "jglx",
               aa.AAB998 "corpCode",c.bgdh "bgdh",aa.AAB078 "xzqh",
               nvl((SELECT t.user_name FROM s_sg_user t WHERE t.id=aa.bae002),aa.bae002) "cjr",aa.bae003 "cjsj",
               (SELECT t.user_name FROM s_sg_user t WHERE t.id=aa.bae004) "zhxgr",aa.bae005 "zhxgsj"
        FROM RS_PSAM_RKXXB b,RS_PSAM_YWXX_SQB c,ab01 aa
        WHERE b.rs_psam_ywxx_sqb_lsh=c.rs_psam_ywxx_sqb_lsh AND aa.ztid=c.ztid
          and b.rs_psam_rkxxb_lsh=#{ dto.rsPsamRkxxbLsh }
    </select>
    <select id="getKzttjList" parameterType="com.zjrs.zwnw.psam.dto.PsamKglDTO" resultType="com.zjrs.zwnw.psam.dto.PsamTjDTO">
        SELECT detail "klx",(SELECT COUNT(1) FROM rs_psam_rkxxb aa WHERE aa.kzt='1' AND aa.klx=a.code) "zy"
             ,(SELECT COUNT(1) FROM rs_psam_rkxxb aa WHERE aa.kzt='2' AND aa.klx=a.code) "sh"
             ,(SELECT COUNT(1) FROM rs_psam_rkxxb aa WHERE aa.kzt='3' AND aa.klx=a.code) "ds"
             ,(SELECT COUNT(1) FROM rs_psam_rkxxb aa WHERE aa.kzt='4' AND aa.klx=a.code) "kx"
             ,(SELECT COUNT(1) FROM rs_psam_rkxxb aa WHERE aa.kzt='5' AND aa.klx=a.code) "zx"
             ,(SELECT COUNT(1) FROM rs_psam_rkxxb aa WHERE aa.kzt='6' AND aa.klx=a.code) "dj"
        FROM s_sg_dict a where KIND_CODE='RS_PSAM_KLX'
        ORDER BY CODE
    </select>
    <select id="getShsltjList" parameterType="com.zjrs.zwnw.psam.dto.PsamKglDTO" resultType="com.zjrs.zwnw.psam.dto.PsamTjDTO">
        SELECT shlx "shlx",(SELECT t."AREA_NAME" FROM V_S_SG_AREA t WHERE t."AREA_CODE"=b.AAB078) "aab078",COUNT(1) "cnt"
        FROM RS_PSAM_YWXX_SQB a,ab01 b,rs_psam_rkxxb aa
        WHERE a.ztid=b.ztid AND aa.rs_psam_ywxx_sqb_lsh=a.rs_psam_ywxx_sqb_lsh
        <if test="dto.shlx != null and dto.shlx != ''">
            and shlx = #{ dto.shlx }
        </if>
        <if test="dto.xzqh != null and dto.xzqh != ''">
            and aab078 = #{ dto.xzqh }
        </if>
        GROUP BY shlx,b.aab078 ORDER BY shlx
    </select>
    <select id="exportShsltjList" parameterType="com.zjrs.zwnw.psam.dto.PsamKglDTO" resultType="com.zjrs.zwnw.psam.dto.PsamTjDTO">
        select rownum rn,aa.* from (SELECT (SELECT detail FROM s_sg_dict t WHERE t.kind_code='RS_PSAM_SHLX' AND
        CODE=shlx) shlx,
        (SELECT t."AREA_NAME" FROM V_S_SG_AREA t WHERE t."AREA_CODE"=b.AAB078) "aab078",COUNT(1) cnt
        FROM RS_PSAM_YWXX_SQB a,ab01 b,rs_psam_rkxxb aa
        WHERE a.ztid=b.ztid AND aa.rs_psam_ywxx_sqb_lsh=a.rs_psam_ywxx_sqb_lsh
        <if test="dto.shlx != null and dto.shlx != ''">
            and shlx = #{ dto.shlx }
        </if>
        <if test="dto.xzqh != null and dto.xzqh != ''">
            and aab078 = #{ dto.xzqh }
        </if>
        GROUP BY shlx,b.aab078 ORDER BY shlx) aa
    </select>
    <select id="getJgtjList" parameterType="com.zjrs.zwnw.psam.dto.PsamKglDTO" resultType="com.zjrs.zwnw.psam.dto.PsamTjDTO">
        SELECT a.rs_psam_azjgxx_lsh "rsPsamAzjgxxLsh",(SELECT jgmc FROM rs_psam_azjgxx az WHERE
        az.rs_psam_azjgxx_lsh=a.rs_psam_azjgxx_lsh) jgmc,
        COUNT(1) cnt,(SELECT t."AREA_NAME" FROM V_S_SG_AREA t WHERE t."AREA_CODE"=AAB078) aab078 FROM
        rs_psam_azjgxx a,rs_psam_ywxx_sqb b,rs_psam_rkxxb e,ab01 k
        WHERE a.rs_psam_azjgxx_lsh=b.rs_psam_azjgxx_lsh
        AND e.rs_psam_ywxx_sqb_lsh=b.rs_psam_ywxx_sqb_lsh AND k.ztid=b.ztid
        <if test="dto.dwmc != null and dto.dwmc != ''">
            and a.jgmc like '%${ dto.dwmc }%'
        </if>
        <if test="dto.xzqh != null and dto.xzqh != ''">
            and aab078 = #{ dto.xzqh }
        </if>
        GROUP BY a.rs_psam_azjgxx_lsh,k.aab078 ORDER BY jgmc
    </select>
    <select id="exportJgtjList" parameterType="com.zjrs.zwnw.psam.dto.PsamKglDTO" resultType="com.zjrs.zwnw.psam.dto.PsamTjDTO">
        select rownum rn,aa.* from (SELECT (SELECT jgmc FROM rs_psam_azjgxx az WHERE
        az.rs_psam_azjgxx_lsh=a.rs_psam_azjgxx_lsh) jgmc,
        COUNT(1) cnt,(SELECT t."AREA_NAME" FROM V_S_SG_AREA t WHERE t."AREA_CODE"=AAB078) aab078 FROM
        rs_psam_azjgxx a,rs_psam_ywxx_sqb b,rs_psam_rkxxb e,ab01 k
        WHERE a.rs_psam_azjgxx_lsh=b.rs_psam_azjgxx_lsh
        AND e.rs_psam_ywxx_sqb_lsh=b.rs_psam_ywxx_sqb_lsh AND k.ztid=b.ztid
        <if test="dto.dwmc != null and dto.dwmc != ''">
            and a.jgmc like '%${ dto.dwmc }%'
        </if>
        <if test="dto.xzqh != null and dto.xzqh != ''">
            and aab078 = #{ dto.xzqh }
        </if>
        GROUP BY a.rs_psam_azjgxx_lsh,k.aab078 ORDER BY jgmc)aa
    </select>
    <select id="getShtjList" parameterType="com.zjrs.zwnw.psam.dto.PsamKglDTO" resultType="com.zjrs.zwnw.psam.dto.PsamTjDTO">
        SELECT a.ztid,aab004 jgmc,AAB998 corpCode,a.jglx,COUNT(1) cnt,(SELECT t."AREA_NAME" FROM V_S_SG_AREA t WHERE
        t."AREA_CODE"=AAB078) aab078
        FROM rs_psam_ywxx_sqb a,ab01 b,rs_psam_rkxxb c
        WHERE a.ztid=b.ztid AND a.rs_psam_ywxx_sqb_lsh=c.rs_psam_ywxx_sqb_lsh
        <if test="dto.dwmc != null and dto.dwmc != ''">
            and b.aab004 like '%${ dto.dwmc }%'
        </if>
        <if test="dto.xzqh != null and dto.xzqh != ''">
            and aab078 = #{ dto.xzqh }
        </if>
        <if test="dto.corpCode != null and dto.corpCode != ''">
            and AAB998 = #{ dto.corpCode }
        </if>
        GROUP BY aab004,AAB998,a.jglx,AAB078,a.ztid ORDER BY aab004
    </select>
    <select id="exportShtjList" parameterType="com.zjrs.zwnw.psam.dto.PsamKglDTO" resultType="com.zjrs.zwnw.psam.dto.PsamTjDTO">
        select rownum rn,aa.* from (SELECT aab004 jgmc,AAB998 corpCode,
        (SELECT detail FROM s_sg_dict t WHERE t.kind_code='RS_PSAM_JGLX' AND CODE=a.jglx) jglx,COUNT(1) cnt,
                                           (SELECT t."AREA_NAME" FROM V_S_SG_AREA t WHERE t."AREA_CODE"=AAB078) aab078
        FROM rs_psam_ywxx_sqb a,ab01 b,rs_psam_rkxxb c
        WHERE a.ztid=b.ztid AND a.rs_psam_ywxx_sqb_lsh=c.rs_psam_ywxx_sqb_lsh
        <if test="dto.dwmc != null and dto.dwmc != ''">
            and b.aab004 like '%${ dto.dwmc }%'
        </if>
        <if test="dto.xzqh != null and dto.xzqh != ''">
            and aab078 = #{ dto.xzqh }
        </if>
        GROUP BY aab004,AAB998,a.jglx,AAB078 ORDER BY aab004)aa
    </select>
    <select id="getShtjDetailList" parameterType="com.zjrs.zwnw.psam.dto.PsamKglDTO" resultType="map">
        SELECT AAB998 "corpCode",aab004 "jgmc",AAE006 "dwdz",b.psambh "psambh",aab078 "aab078",b.klx "klx",b.kzt
            "kzt",b.cjsj "rksj",c.bjsj "cksj",
               '' "lxr",
               '' "bgdh"
        FROM ab01 a,rs_psam_rkxxb b,rs_psam_ywxx_sqb c
        WHERE a.ztid=c.ztid AND b.rs_psam_ywxx_sqb_lsh=c.rs_psam_ywxx_sqb_lsh
            and a.ztid = #{ dto.ztid } ORDER BY b.cjsj desc
    </select>
    <select id="getJgtjDetailList" parameterType="com.zjrs.zwnw.psam.dto.PsamKglDTO" resultType="map">
        SELECT e.psambh "psambh",e.klx "klx",e.kzt "kzt"
        FROM rs_psam_azjgxx a,rs_psam_ywxx_sqb b,rs_psam_rkxxb e
        WHERE a.rs_psam_azjgxx_lsh=b.rs_psam_azjgxx_lsh
          AND e.rs_psam_ywxx_sqb_lsh=b.rs_psam_ywxx_sqb_lsh
          AND a.rs_psam_azjgxx_lsh=#{ dto.rsPsamAzjgxxLsh }
        <if test="dto.psambh != null and dto.psambh != ''">
            and e.psambh = #{ dto.psambh }
        </if>
        order by e.psambh
    </select>
</mapper>
