<?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.DbrwMapper">

    <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",
            a.sctbjzrq as "sctbjzrq",
            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.ts_yxbz = '1'
        <if test="dto.rwzt != null and dto.rwzt != ''">
            and a.rwzt = #{dto.rwzt}
        </if>
        <if test="dto.rwly != null and dto.rwly != ''">
            and a.rwly LIKE '%'||#{dto.rwly}||'%'
        </if>
        <if test="dto.rwlyfl != null and dto.rwlyfl != ''">
            and a.rwlyfl = #{dto.rwlyfl}
        </if>
        <if test="dto.pfzt != null and dto.pfzt != ''">
            and a.pfzt = #{dto.pfzt}
        </if>
        <if test="dto.sdpj != null and dto.sdpj != ''">
            and a.sdpj = #{dto.sdpj}
        </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>

        <if test="dto.jssjStart != null and dto.jssjStart != ''">
            and a.jssj <![CDATA[ >= ]]> #{dto.jssjStart}||' 00:00:00'
        </if>

        <if test="dto.jssjEnd != null and dto.jssjEnd != ''">
            and a.jssj <![CDATA[ <= ]]> #{dto.jssjEnd}||' 23:59:59'
        </if>
        order by a.DBBH desc

    </select>

    <select id="getXmxxList" resultType="HashMap" parameterType="com.zjrs.zwnw.dbgl.dto.DbrwglXmxxQueryDTO">
        select a.rs_dbgl_dbxm_lsh as "rsdbgldbxmlsh",
            a.rs_dbgl_dbrw_lsh as "rsdbgldbrwlsh",
            a.xmbh as "xmbh",
            a.xmmc as "xmmc",
            '' as "fgld",
            '' as "qtbm",
            '' as "xbbm",
            a.isleaf as "isleaf",
            '' as "qtbmszqx"
        from v_Rs_Dbgl_Dbxm_Yx a
        where a.ts_yxbz = '1'
            and a.rs_dbgl_dbrw_lsh = #{dto.rsdbgldbrwlsh}
            order by a.xmbh
    </select>

    <select id="getGzqdffList" resultType="HashMap" parameterType="com.zjrs.zwnw.dbgl.dto.DbrwglDbrwclQueryDTO">
        select b.rs_dbgl_dbxm_lsh as "rsdbgldbxmlsh",
            b.rs_dbgl_dbrw_lsh as "rsdbgldbrwlsh",
            a.dbbh as "dbbh",
            a.rwly as "rwly",
            a.rwlyfl as "rwlyfl",
            b.xmmc as "xmmc",
            '' as "qtbm",
            a.tbzq as "tbzq",
            a.sctbjzrq as "sctbjzrq",
            '' as "fpzt",
            a.bae003 as "bae003",
            f_common_getSSgUserOrg(a.bae001) as "bae001",
            a.pfsj as "pfsj"
        from V_RS_DBGL_DBRW_YX a, V_RS_DBGL_DBXM_YX b
        where a.rs_dbgl_dbrw_lsh = b.RS_DBGL_DBRW_LSH
        <if test="dto.rwly != null and dto.rwly != ''">
            and a.rwly LIKE '%'||#{dto.rwly}||'%'
        </if>
        <if test="dto.rwlyfl != null and dto.rwlyfl != ''">
            and a.rwlyfl = #{dto.rwlyfl}
        </if>
        <if test="dto.xmmc != null and dto.xmmc != ''">
            and b.xmmc LIKE '%'||#{dto.xmmc}||'%'
        </if>
        <if test="dto.bae001 != null and dto.bae001 != ''">
            and a.bae001 = #{dto.bae001}
        </if>
        <if test="dto.pfsjStart != null and dto.pfsjStart != ''">
            and a.pfsj <![CDATA[ >= ]]> #{dto.pfsjStart}||' 00:00:00'
        </if>
        <if test="dto.pfsjEnd != null and dto.pfsjEnd != ''">
            and a.pfsj <![CDATA[ <= ]]> #{dto.pfsjEnd}||' 23:59:59'
        </if>

        order by a.pfsj desc
    </select>

    <select id="getDbrwListByXmid" resultType="HashMap" parameterType="com.zjrs.zwnw.dbgl.dto.DbrwglCommQueryDTO">
        select a.rs_dbgl_dbrw_lsh as "rsdbgldbrwlsh",
        a.dbbh             as "dbbh",
        a.rwlyfl           as "rwlyfl",
        a.rwly             as "rwly"
        from V_RS_DBGL_DBRW_YX   a,
        V_RS_DBGL_DBXM_YX   b
        where a.RS_DBGL_DBRW_LSH = b.RS_DBGL_DBRW_LSH
        and b.ISLEAF = '1'
        and b.RS_DBGL_DBXM_LSH = #{dto.ids}
    </select>
    <select id="getGzqdListByXmid" resultType="HashMap" parameterType="com.zjrs.zwnw.dbgl.dto.DbrwglCommQueryDTO">
        select c.rs_dbgl_gzqd_lsh      as "rsdbglgzqdlsh",
        d.rs_dbgl_dbxm_gzqd_lsh as "rsdbgldbxmgzqdlsh",
        c.gzqd                  as "gzqd",
        c.zrr                   as "zrr"
        from V_RS_DBGL_DBXM_YX   b,
        V_RS_DBGL_GZQD_YX   c,
        V_RS_DBGL_DBXM_GZQD d
        where b.RS_DBGL_DBXM_LSH = d.RS_DBGL_DBXM_LSH
        and c.RS_DBGL_GZQD_LSH = d.RS_DBGL_GZQD_LSH
        and b.RS_DBGL_DBXM_LSH = #{dto.ids}
    </select>
    <select id="getGzjzshList" resultType="HashMap" parameterType="com.zjrs.zwnw.dbgl.dto.DbrwglDbrwclGzjzshQueryDTO">
        select a.rs_dbgl_gzqd_lsh as "rsdbglgzqdlsh",
        a.gzqd as "gzqd",
        a.gzjz as "gzjz",
        a.czwt as "czwt",
        a.xgbgz as "xgbgz",
        a.sdpj as "sdpj",
        a.zlpj as "zlpj",
        a.zrr as "zrr",
        a.zrbm as "zrbm",
        a.shzt as "shzt",
        a.shr as "shr",
        a.shjg as "shjg",
        a.shsj as "shsj",
        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",
        f_dbgl_getTbzqByGzqdlsh(a.rs_dbgl_gzqd_lsh) as "tbzq",
        f_dbgl_getBctbrqByGzqdlsh(a.rs_dbgl_gzqd_lsh) as "jzrq",
        f_dbgl_getSysxByGzqdlsh(a.rs_dbgl_gzqd_lsh) as "sysx",
        f_dbgl_getYjdByGzqdlsh(a.rs_dbgl_gzqd_lsh,'') as "yjd"
        from V_RS_DBGL_GZQD_YX a
        where exists (select 1
                from V_RS_DBGL_DBRW_YX   b,
                V_RS_DBGL_DBXM_YX   c,
                V_RS_DBGL_DBXM_GZQD d,
                v_rs_dbgl_dbxm_zrbm e,
                V_S_SG_ORG_EX       f
                where b.RS_DBGL_DBRW_LSH = c.RS_DBGL_DBRW_LSH
                and c.RS_DBGL_DBXM_LSH = d.RS_DBGL_DBXM_LSH
                and a.RS_DBGL_GZQD_LSH = d.RS_DBGL_GZQD_LSH
                and c.RS_DBGL_DBXM_LSH = e.RS_DBGL_DBXM_LSH
                and e.ORGID = f.id
                and f.JGFZR =  #{dto.userid}
                and a.ZRBM= e.ORGID)
        <if test="dto.gzqd != null and dto.gzqd != ''">
            and a.gzqd LIKE '%'||#{dto.gzqd}||'%'
        </if>
        <if test="dto.zrr != null and dto.zrr != ''">
            and a.zrr = #{dto.zrr}
        </if>
        <if test="dto.shzt != null and dto.shzt != ''">
            and a.shzt = #{dto.shzt}
        </if>

        order by f_dbgl_getSysxByGzqdlsh(a.rs_dbgl_gzqd_lsh),a.bae005
    </select>
    <select id="getDbrwtbList" resultType="HashMap" parameterType="com.zjrs.zwnw.dbgl.dto.DbrwglDbrwtbQueryDTO">
        select a.rs_dbgl_gzqd_lsh as "rsdbglgzqdlsh",
        a.gzqd as "gzqd",
        a.gzjz as "gzjz",
        a.czwt as "czwt",
        a.xgbgz as "xgbgz",
        a.sdpj as "sdpj",
        a.zlpj as "zlpj",
        a.zrr as "zrr",
        a.zrbm as "zrbm",
        a.shzt as "shzt",
        a.shr as "shr",
        a.shjg as "shjg",
        a.shsj as "shsj",
        f_dbgl_getYjdByGzqdlsh(a.rs_dbgl_gzqd_lsh,'') as "yjd",
        f_dbgl_getWcbzByGzqdlsh(a.rs_dbgl_gzqd_lsh) as "wcbz",
        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",
        f_dbgl_getTbzqByGzqdlsh(a.rs_dbgl_gzqd_lsh) as "tbzq",
        f_dbgl_getBctbrqByGzqdlsh(a.rs_dbgl_gzqd_lsh) as "jzrq",
        f_dbgl_getSysxByGzqdlsh(a.rs_dbgl_gzqd_lsh) as "sysx"
        from V_RS_DBGL_GZQD_YX a
        WHERE a.zrr = #{dto.userid}
        <if test="dto.gzqd != null and dto.gzqd != ''">
            and a.gzqd LIKE '%'||#{dto.gzqd}||'%'
        </if>
        <if test="dto.zrr != null and dto.zrr != ''">
            and a.zrr = #{dto.zrr}
        </if>
        <if test="dto.shzt != null and dto.shzt != ''">
            and a.shzt = #{dto.shzt}
        </if>
        order by to_number(f_dbgl_getSysxByGzqdlsh(a.rs_dbgl_gzqd_lsh)),a.bae005
    </select>

    <select id="getCqcxList" resultType="com.zjrs.zwnw.dbgl.dto.DbrwglCqcxListDTO"
            parameterType="com.zjrs.zwnw.dbgl.dto.DbrwglCqcxQueryDTO">
        SELECT t.*,(f_common_getSSgUserOrg(t.bae001)) bae001Name,
            (F_DICT_GET_AAA103('RS_DBGL_SSBM',t.ssbmlx)) ssbmlxName,(f_common_getSSgUserOrg(t.ssbm)) ssbmName,
            (F_DICT_GET_AAA103('RS_DBGL_CQLX',t.cqlx)) cqlxName
          FROM (select A.*, '01' CQLX
                  from V_RS_DBGL_CQCX A) T
        WHERE 1=1
            <if test="dto.rwly != null and dto.rwly != ''">
                and rwly LIKE '%'||#{dto.rwly}||'%'
            </if>
            <if test="dto.xmmc != null and dto.xmmc != ''">
                and xmmc LIKE '%'||#{dto.xmmc}||'%'
            </if>
            <if test="dto.bae001 != null and dto.bae001 != ''">
                and bae001 = #{dto.bae001}
            </if>
         ORDER BY T.DBBH DESC, T.XMBH ASC, T.TBJZRQ DESC
    </select>

</mapper>
