<?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.dbgl.entity.DbrwcxMapper">
    <select id="getDbrwList" resultType="HashMap" parameterType="com.zjrs.zwnw.dbgl.dto.DbrwglDbrwQueryDTO">
        select a.rs_dbgl_dbrw_lsh as "rsdbgldbrwlsh",
        a.dbbh as "dbbh",
        a.rwlyfl as "rwlyfl",
        a.rwly as "rwly",
        a.wh as "wh",
        a.fwdw as "fwdw",
        a.fwsj as "fwsj",
        a.tbzq as "tbzq",
        '' as "bctbjzrq",
        '' as "sysx",
        a.rwjsrq as "rwjsrq",
        a.yellowyj as "yellowyj",
        a.redyj as "redyj",
        a.pfzt as "pfzt",
        a.pfsj as "pfsj",
        a.pfr as "pfr",
        a.jsr as "jsr",
        a.rwzt as "rwzt",
        a.sdpj as "sdpj",
        a.zlpj as "zlpj",
        a.pjms as "pjms",
        a.pjr as "pjr",
        a.pjjg as "pjjg",
        a.pjsj as "pjsj",
        a.jssj as "jssj",
        a.ts_yxbz as "tsyxbz",
        a.bae001 as "bae001",
        a.bae002 as "bae002",
        a.bae003 as "bae003",
        a.bae004 as "bae004",
        a.bae005 as "bae005",
        a.bae006 as "bae006",
        '' as "fgld",
        '' as "qtbm",
        '' as "xbbm"
        from v_rs_dbgl_dbrw a
        where a.pfzt = '1'
        <if test="dto.rwzt != null and dto.rwzt != ''">
            and a.rwzt = #{dto.rwzt}
        </if>
        <if test="dto.qtbm != null and dto.qtbm != ''">
            and exists (select 1
            from V_RS_DBGL_DBXM_YX b,v_rs_dbgl_dbxm_zrbm c
            where b.RS_DBGL_DBXM_LSH = c.RS_DBGL_DBXM_LSH
            and b.RS_DBGL_DBRW_LSH = a.RS_DBGL_DBRW_LSH
            and c.lx='1'
            and c.orgid =#{dto.qtbm})
        </if>
        <if test="dto.bae001 != null and dto.bae001 != ''">
            and a.bae001 = #{dto.bae001}
        </if>
        <if test="dto.bae003Start != null and dto.bae003Start != ''">
            and a.bae003 <![CDATA[ >= ]]> #{dto.bae003Start}||' 00:00:00'
        </if>
        <if test="dto.bae003End != null and dto.bae003End != ''">
            and a.bae003 <![CDATA[ <= ]]> #{dto.bae003End}||' 23:59:59'
        </if>
        order by a.dbbh desc
    </select>

    <select id="exportDbrwcxList" resultType="com.zjrs.zwnw.dbgl.dto.DbrwglExportrwcxDTO" parameterType="com.zjrs.zwnw.dbgl.dto.DbrwglDbrwQueryDTO">
        select * from (select (select xmmc
        from V_RS_DBGL_DBXM_YX b
        where b.RS_DBGL_DBRW_LSH = a.RS_DBGL_DBRW_LSH
        and b.XMBH = substr(a.XMBH, 1, 2)) level1,
        '' level2,
        '' level3,
        '' level4,
        '' level5,
        b.dbbh as "dbbh",
        b.rwly as "rwly",
        b.wh as "wh",
        b.fwdw as "fwdw",
        b.fwsj as "fwsj",
        b.rwjsrq as "rwjsrq",
        a.xmbh as "xmbh",
        '' as "fgld",
        nvl(c.gzqd,d.gzqd) as "gzqd",
        '' as "gzjz",
        '' as "czwt",
        '' as "xgbgz",
        '' as "wcjd",
        '' as "sctbjzrq",
        b.RS_DBGL_DBRW_LSH as "rsdbgldbrwlsh",(select max(shsj) from V_RS_DBGL_DBXM_GZQD where RS_DBGL_GZQD_LSH=d.RS_DBGL_GZQD_LSH ) "tbsj",
        '' as "tbbm",
        (SELECT detail FROM s_sg_dict t WHERE t.kind_code='RS_DBGL_DBRW_RWLYFL' AND CODE=b.rwlyfl) "rwlyfl",
        (SELECT detail FROM s_sg_dict t WHERE t.kind_code='RS_DBGL_DBRW_TBZQ' AND CODE=b.tbzq) "tbzq",
        (SELECT detail FROM s_sg_dict t WHERE t.kind_code='RS_DBGL_DBRW_SDPJ' AND CODE=b.sdpj) "sdpj",
        (SELECT detail FROM s_sg_dict t WHERE t.kind_code='RS_DBGL_DBRW_ZLPJ' AND CODE=b.zlpj) "zlpj",
        (SELECT detail FROM s_sg_dict t WHERE t.kind_code='RS_DBGL_DBRW_SDPJ' AND CODE= (select sdpj from V_RS_DBGL_DBXM_GZQD where SHSJ = (select max(SHSJ) from V_RS_DBGL_DBXM_GZQD where RS_DBGL_GZQD_LSH=d.RS_DBGL_GZQD_LSH )and rownum=1)) "sdzp",
        (SELECT detail FROM s_sg_dict t WHERE t.kind_code='RS_DBGL_DBRW_ZLPJ' AND CODE= (select zlpj from V_RS_DBGL_DBXM_GZQD where SHSJ = (select max(SHSJ) from V_RS_DBGL_DBXM_GZQD where RS_DBGL_GZQD_LSH=d.RS_DBGL_GZQD_LSH )and rownum=1)) "zlzp",
        '' as "qtbm",
        '' as "xbbm",
        a.RS_DBGL_DBXM_LSH as "rsdbgldbxmlsh",
        F_DICT_GET_AAA103('RS_DBGL_DBRW_SDPJ',e.sdpj) as "dbsdpj",
        F_DICT_GET_AAA103('RS_DBGL_DBRW_ZLPJ',e.zlpj) as "dbzlpj",
        e.lx
        from  V_RS_DBGL_DBRW_YX b
            LEFT join V_RS_DBGL_DBXM_YX a
              ON a.RS_DBGL_DBRW_LSH = b.RS_DBGL_DBRW_LSH
             and a.isleaf = '1'
            left join v_rs_dbgl_dbxm_zrbm e
              on a.RS_DBGL_DBXM_LSH = e.RS_DBGL_DBXM_LSH
            LEFT JOIN RS_DBGL_DBXM_GZQD c
              on a.RS_DBGL_DBXM_LSH = c.RS_DBGL_DBXM_LSH
             and c.zrbm = e.ORGID
            LEFT JOIN RS_DBGL_GZQD d
              ON d.RS_DBGL_GZQD_LSH = c.RS_DBGL_GZQD_LSH
             and d.ZRBM = e.ORGID
        where b.pfzt = '1'
        <if test="dto.rwzt != null and dto.rwzt != ''">
            and b.rwzt = #{dto.rwzt}
        </if>
        <if test="dto.bae001 != null and dto.bae001 != ''">
            and b.bae001 = #{dto.bae001}
        </if>
        <if test="dto.bae003Start != null and dto.bae003Start != ''">
            and b.bae003 <![CDATA[ >= ]]> #{dto.bae003Start}||' 00:00:00'
        </if>
        <if test="dto.bae003End != null and dto.bae003End != ''">
            and b.bae003 <![CDATA[ <= ]]> #{dto.bae003End}||' 23:59:59'
        </if>
        )
        order by "dbbh","xmbh",lx,"qtbm" nulls last,"xbbm"
    </select>
</mapper>
