<?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.zj.entity.GccrcMapper">

    <select id="exportGccrcList" parameterType="com.zjrs.zwnw.zj.dto.GccrcQueryDTO" resultType="com.zjrs.zwnw.zj.dto.ExportGccrcListDTO">
        select ROWNUM rn,ab.* from (SELECT (SELECT aaa103 FROM v_ggfw_dict a WHERE a.aaa100='RS_ZJ_GCCRC_RCKLX' AND
        a.aaa102=t1.rcklx AND ROWNUM=1) rcklx,
        t1.rckhm,t1.yxrq,(SELECT aaa103 FROM v_ggfw_dict a WHERE a.aaa100='RS_ZJ_GCCRC_RCKZT' AND a.aaa102=t1.rckzt AND ROWNUM=1) rckzt,
        (SELECT aaa103 FROM v_ggfw_dict a WHERE a.aaa100='RS_ZJ_GCCRC_QFZT' AND a.aaa102=t1.qfzt AND ROWNUM=1) qfzt,
        (SELECT aaa103 FROM v_ggfw_dict a WHERE a.aaa100='JBXX_AAC161' AND a.aaa102=t2.AAC161 AND ROWNUM=1) gj,
        (SELECT aaa103 FROM v_ggfw_dict a WHERE a.aaa100='JBXX_AAC058' AND a.aaa102=t2.AAC058 AND ROWNUM=1) zjlx,
        t2.AAC147 zjhm,t2.AAC003 xm,
        (SELECT aaa103 FROM v_ggfw_dict a WHERE a.aaa100='JBXX_AAC004' AND a.aaa102=t2.AAC004 AND ROWNUM=1) xb,
        t2.AAC006 asny,
        (SELECT aaa103 FROM v_ggfw_dict a WHERE a.aaa100='JBXX_AAC024' AND a.aaa102=t2.AAC024 AND ROWNUM=1) zzmm,
        f_jbxxgl_getAllAreaName(t2.AAC300) hkszd,
        (SELECT aaa103 FROM v_ggfw_dict a WHERE a.aaa100='RS_ZJ_GCCRC_LYFS' AND a.aaa102=t1.lyfs AND ROWNUM=1) lyfs,
        t1.dzjjycysj,
        (SELECT aaa103 FROM v_ggfw_dict a WHERE a.aaa100='RS_ZJ_GCCRC_CYYYKQK' AND a.aaa102=t1.cyyykqk AND ROWNUM=1) cyyykqk,
        (SELECT aaa103 FROM v_ggfw_dict a WHERE a.aaa100='RS_ZJ_GCCRC_XL' AND a.aaa102=t1.xl AND ROWNUM=1) xl,
        (SELECT aaa103 FROM v_ggfw_dict a WHERE a.aaa100='RS_ZJ_GCCRC_BYXXLX' AND a.aaa102=t1.qrzzgxlbyxxlx AND ROWNUM=1) qrzzgxlbyxxlx,
        t1.QRZZGXLBYXXMC,t1.QRZZGXLZYDL,t1.QRZZGXLZYXL,
        (SELECT aaa103 FROM v_ggfw_dict a WHERE a.aaa100='RS_ZJ_GCCRC_ZCDJ' AND a.aaa102=t1.ZCDJ AND ROWNUM=1) ZCDJ,
        (SELECT aaa103 FROM v_ggfw_dict a WHERE a.aaa100='RS_ZJ_GCCRC_ZCXL' AND a.aaa102=t1.ZCXL AND ROWNUM=1) ZCXL,
        ZCZYMC,
        (SELECT aaa103 FROM v_ggfw_dict a WHERE a.aaa100='RS_ZJ_GCCRC_JNDJ' AND a.aaa102=t1.JNDJ AND ROWNUM=1) JNDJ,
        t1.jnzy,t1.dwmc,
        (SELECT aaa103 FROM v_ggfw_dict a WHERE a.aaa100='JBXX_AAB022' AND a.aaa102=t1.dwsshy AND ROWNUM=1) dwsshy,
        (SELECT aaa103 FROM v_ggfw_dict a WHERE a.aaa100='RS_ZJ_GCCRC_DWXZ' AND a.aaa102=t1.dwxz AND ROWNUM=1) dwxz,
        (SELECT aaa103 FROM v_ggfw_dict a WHERE a.aaa100='RS_ZJ_GCCRC_DWGX' AND a.aaa102=t1.dwgx AND ROWNUM=1) dwgx,
        (SELECT area_name FROM v_s_sg_area a WHERE a.area_code=t1.GWSZXSQ AND ROWNUM=1) GWSZXSQ,
        (SELECT aaa103 FROM v_ggfw_dict a WHERE a.aaa100='JBXX_AAB022' AND a.aaa102=t1.CSGZHY AND ROWNUM=1) CSGZHY,
        (SELECT aaa103 FROM v_ggfw_dict a WHERE a.aaa100='RS_ZJ_GCCRC_GZGWXZ' AND a.aaa102=t1.GZGWXZ AND ROWNUM=1) GZGWXZ,
        (SELECT aaa103 FROM v_ggfw_dict a WHERE a.aaa100='RS_ZJ_GCCRC_LDHTQX' AND a.aaa102=t1.LDHTQX AND ROWNUM=1) LDHTQX,
               LDHTJZRQ,GJJZDRCXMMC,SJJZDRCXMMC,SHIJJZDRCXMMC,
        (SELECT aaa103 FROM v_ggfw_dict a WHERE a.aaa100='RS_ZJ_GCCRC_SHZLQK' AND a.aaa102=t1.SHZLQK AND ROWNUM=1) SHZLQK,
        SHZLMC,GJJJXMC,SJJXMC,SHIJJXMC,
        (SELECT aaa103 FROM v_ggfw_dict a WHERE a.aaa100='RS_ZJ_GCCRC_RDTJ' AND a.aaa102=t1.RDTJ AND ROWNUM=1) rdtj,rdzyyj,
        (SELECT aaa103 FROM v_ggfw_dict a WHERE a.aaa100='RS_ZJ_GCCRC_SQLX_JG' AND a.aaa102=t1.SQLX AND ROWNUM=1)
        sqlx,bz,t1.qfsj
        FROM RS_ZJ_GCCRC t1,ac01 t2 where t1.ztid=t2.ztid
        <if test="dto.gj != null and dto.gj != ''">
            and t2.AAC161 = #{ dto.gj }
        </if>
        ORDER BY t1.qfzt DESC,t1.rckhm DESC)ab
    </select>
    <select id="getGccrcListPage" parameterType="com.zjrs.zwnw.zj.dto.GccrcQueryDTO" resultType="com.zjrs.zwnw.zj.dto.GccrcListDTO">
        SELECT t1.rs_zj_gccrc_lsh rsZjGccrcLsh,t2.AAC161 gj,t2.AAC058 zjlx,t2.AAC147 zjhm,t2.AAC003 xm,t1.xl,t1.csgzhy,t1.gzgwxz,t1.zcxl,t1.zczymc,t1.dwxz,t1.dwgx,
        t1.rckhm,t1.rcklx,t1.rckzt,t1.qfzt,t1.QFSJ,t1.SQLX FROM RS_ZJ_GCCRC t1,ac01 t2 where t1.ztid=t2.ztid
        <if test="dto.gj != null and dto.gj != ''">
            and t2.AAC161 = #{ dto.gj }
        </if>
        <if test="dto.zjlx != null and dto.zjlx != ''">
            and t2.aac058 = #{ dto.zjlx }
        </if>
        ORDER BY t1.qfzt DESC,t1.rckhm DESC
    </select>
    <select id="getBglsList" parameterType="com.zjrs.zwnw.zj.dto.GccrcQueryByIdPageDTO" resultType="com.zjrs.zwnw.zj.po.GccrcLsPO">
        SELECT t.rs_zj_gccrc_ls_lsh rsZjGccrcLsLsh,t.rs_zj_gccrc_lsh rsZjGccrcLsh,
               nvl((select user_name from v_rs_sy_org_area where user_id =t.bgr),t.bgr) bgr,
               (select max(org_name) from v_rs_sy_org_area where org_id = t.bgrjg) bgrjg,bgsj,bgnr
        FROM rs_zj_gccrc_ls t WHERE t.rs_zj_gccrc_lsh=#{ dto.rsZjGccrcLsh }
        ORDER BY bgsj desc
    </select>
    <select id="getGccrcInfo" parameterType="com.zjrs.zwnw.zj.dto.GccrcQueryByIdDTO"
            resultType="com.zjrs.zwnw.zj.dto.GccrcInfoDTO">
        SELECT t1.rs_zj_gccrc_lsh rsZjGccrcLsh,t1.ztid,lyfs,dzjjycysj,cyyykqk,xl,qrzzgxlbyxxlx,
               qrzzgxlbyxxmc,qrzzgxlzydl,qrzzgxlzyxl,zcdj,zcxl,zczymc,jndj,jnzy,dwmc,dwsshy,dwxz,dwgx,
               gwszxsq,csgzhy,gzgwxz,ldhtqx,ldhtjzrq,gjjzdrcxmmc,sjjzdrcxmmc,shijjzdrcxmmc,shzlqk,
               shzlmc,gjjjxmc,sjjxmc,shijjxmc,rcklx,rckhm,tyccid,rckzt,qfzt,yxrq,t2.AAC161 gj,
               t2.AAC058 zjlx,t2.AAC147 zjhm,t2.AAC003 xm,t2.AAC004 xb,t2.AAC006 csny,t2.AAC067 sjhm,
               t2.AAC024 zzmm,t2.AAC300 hkszd,t1.SQLX,t1.bz,t1.RDTJ,t1.RDZYYJ,t1.GSQS,t1.YDWMC,t1.YDWSZD,t1.LDHTQSRQ
        FROM RS_ZJ_GCCRC t1,ac01 t2 where t1.ztid=t2.ztid and t1.rs_zj_gccrc_lsh=#{ dto.rsZjGccrcLsh }
    </select>
    <select id="getCntByZtid" parameterType="com.zjrs.zwnw.zj.dto.GccrcSaveDTO"
            resultType="java.lang.Long">
        SELECT COUNT(1) FROM RS_ZJ_GCCRC_SQ a ,rs_wb_bj b
        WHERE a.rs_wb_bj_lsh=b.rs_wb_bj_lsh AND sqzt IN(1,2) AND b.ztid=#{ dto.ztid }
    </select>
</mapper>
