<?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.DbrwglCommMapper">
    <select id="getOrgs" resultType="com.zjrs.zwnw.dbgl.dto.DictDTO" parameterType="com.zjrs.zwnw.dbgl.dto.DictGetDTO">
        select ID as code, ORG_NAME as detail
        from s_sg_org
        where PARENT_ID is not null 
        <if test="dto.areaid != null and dto.areaid != ''">
            and AREA_ID = #{ dto.areaid }
        </if>
        <if test="dto.orgid != null and dto.orgid != ''">
            and ID = #{ dto.orgid }
        </if>
        order by ORG_NAME
    </select>

    <select id="getJgfzrOrgs" resultType="com.zjrs.zwnw.dbgl.dto.DictDTO" parameterType="com.zjrs.zwnw.dbgl.dto.DictGetDTO">
        SELECT A.ID as code, B.ORG_NAME as detail FROM S_SG_ORG_EX A,S_SG_ORG B
        WHERE A.ID = B.ID AND B.PARENT_ID is not null and B.IS_VALID='1'
        AND A.JGFZR = #{ dto.userid }
    </select>

    <select id="getUsers" resultType="com.zjrs.zwnw.dbgl.dto.DictDTO" parameterType="com.zjrs.zwnw.dbgl.dto.DictGetDTO">
        select a.id as code,a.user_name as detail
        from s_sg_user a, s_sg_user_org b, s_sg_org c
        where a.id = b.user_id
        and b.org_id = c.id
        <if test="dto.orgid != null and dto.orgid != ''">
            and c.id = #{ dto.orgid }
        </if>
        order by user_name
    </select>

    <select id="getQtbmFgldByXmid" resultType="HashMap" parameterType="com.zjrs.zwnw.dbgl.dto.DbrwglCommDTO">
        select c.FGLD,
        ((select user_name from v_rs_sy_org_area where user_id = c.FGLD)) FGLDNAME
        from v_rs_dbgl_dbxm_zrbm b, V_S_SG_ORG_EX c
        where b.ORGID = c.id
        and b.RS_DBGL_DBXM_LSH = #{dto.ids}
    </select>
    <select id="getXbbmFgldByXmid" resultType="HashMap" parameterType="com.zjrs.zwnw.dbgl.dto.DbrwglCommDTO">
        select distinct c.FGLD,
        ((select user_name from v_rs_sy_org_area where user_id = c.FGLD)) fgldname
        from v_rs_dbgl_dbxm_zrbm b, V_S_SG_ORG_EX c
        where b.ORGID = c.id
        and b.RS_DBGL_DBXM_LSH = #{dto.ids}
    </select>

    <select id="getQtbmFgldByRwid" resultType="HashMap" parameterType="com.zjrs.zwnw.dbgl.dto.DbrwglCommDTO">
        select distinct c.FGLD,
        ((select user_name from v_rs_sy_org_area where user_id = c.FGLD)) FGLDNAME
        from v_RS_DBGL_DBXM a, v_rs_dbgl_dbxm_zrbm b, V_S_SG_ORG_EX c
        where a.RS_DBGL_DBXM_LSH = b.RS_DBGL_DBXM_LSH
        and b.ORGID = c.id
        and a.RS_DBGL_DBRW_LSH = #{dto.ids}
    </select>

    <select id="getXbbmFgldByRwid" resultType="HashMap" parameterType="com.zjrs.zwnw.dbgl.dto.DbrwglCommDTO">
        select distinct c.FGLD,
            ((select user_name
            from v_rs_sy_org_area
            where user_id = c.FGLD)) fgldname
        from v_RS_DBGL_DBXM a, v_rs_dbgl_dbxm_zrbm b, V_S_SG_ORG_EX c
        where  a.RS_DBGL_DBXM_LSH = b.RS_DBGL_DBXM_LSH
            and b.ORGID = c.id
            and a.RS_DBGL_DBRW_LSH = #{dto.ids}
    </select>


    <select id="getGzqdjzqkList" resultType="HashMap" parameterType="com.zjrs.zwnw.dbgl.dto.DbrwglCommQueryDTO">
        select t.*, f_dbgl_getPjByCode(t."sdpj",t."zlpj") as "zp", f_dbgl_getPjByCode(t."dbsdpj",t."dbzlpj") as "dbpj" from (
            select distinct f_dbgl_getYjdByGzqdlsh(b.rs_dbgl_gzqd_lsh,a.RS_DBGL_DBRW_LSH) as "yjd",
            (select rwzt from V_RS_DBGL_DBRW_YX t where t.RS_DBGL_DBRW_LSH = a.RS_DBGL_DBRW_LSH) as "wcbz",
                c.rs_dbgl_dbxm_gzqd_lsh as "rsdbgldbxmgzqdlsh",
                c.rs_dbgl_dbxm_lsh as "rsdbgldbxmlsh",
                c.rs_dbgl_gzqd_lsh as "rsdbglgzqdlsh",
                d.rs_dbgl_dbxm_zrbm_lsh as "rsdbgldbxmzrbmlsh",
                nvl(c.gzqd,b.gzqd) as "gzqd",
                '' as "gzjz",
                '' as "czwt",
                '' as "xgbgz",
                '' as "wcjd",
                '' as "sdpj",
                '' as "zlpj",
                b.zrr as "zrr",
                d.ORGID as "zrbm",
                '' as "shsj",
                d.lx as "lx",
                a.xmbh as "xmbh",
                a.xmmc as "xmmc",
                f_common_getSSgUserOrg(d.ORGID) as "orgname",
                d.sdpj as "dbsdpj",
                d.zlpj as "dbzlpj"
              from V_RS_DBGL_DBXM_YX a
              left join v_rs_dbgl_dbxm_zrbm d
                on a.RS_DBGL_DBXM_LSH = d.RS_DBGL_DBXM_LSH
              left join V_RS_DBGL_DBXM_GZQD c
                on a.RS_DBGL_DBXM_LSH = c.RS_DBGL_DBXM_LSH
                and c.zrbm = d.ORGID
              left join V_RS_DBGL_GZQD_YX b
                on b.ZRBM = d.ORGID
               and b.RS_DBGL_GZQD_LSH = c.RS_DBGL_GZQD_LSH
            where a.RS_DBGL_DBRW_LSH = #{ dto.ids }
            <if test="dto.ssbm != null and dto.ssbm != ''">
                and d.lx = #{ dto.ssbm }
            </if>
            <if test="dto.rsdbgldbxmlsh != null and dto.rsdbgldbxmlsh != ''">
                and a.RS_DBGL_DBXM_LSH = #{ dto.rsdbgldbxmlsh }
            </if>

        ) t order by "xmbh","lx","orgname"
    </select>
    <select id="getShlsListByQdid" resultType="HashMap" parameterType="com.zjrs.zwnw.dbgl.dto.DbrwglCommQueryDTO">
        select distinct
            a.gzqd                      as "gzqd",
            a.gzjz                      as "gzjz",
            a.czwt                      as "czwt",
            a.xgbgz                     as "xgbgz",
            decode(a.wcjd,null,'',a.wcjd||'%')  as "wcjd",
            a.sdpj                      as "sdpj",
            a.zlpj                      as "zlpj",
            a.zrr                       as "zrr",
            a.zrbm                      as "zrbm",
            a.shr                       as "shr",
            a.shjg                      as "shjg",
            a.shsj                      as "shsj"
        from V_RS_DBGL_DBXM_GZQD_LOG a,
             V_RS_DBGL_DBXM_GZQD b
        where a.RS_DBGL_DBXM_GZQD_LSH = b.RS_DBGL_DBXM_GZQD_LSH
            and b.RS_DBGL_GZQD_LSH = #{ dto.ids }
            order by a.shsj desc
    </select>
    <select id="getPsjlLstByQdid" resultType="HashMap" parameterType="com.zjrs.zwnw.dbgl.dto.DbrwglCommQueryDTO">
        select a.rs_dbgl_psxx_lsh      as "rsdbglpsxxlsh",
            a.rs_dbgl_dbxm_gzqd_lsh as "rsdbgldbxmgzqdlsh",
            a.psyj                  as "psyj",
            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"
        from V_RS_DBGL_PSXX_YX a, V_RS_DBGL_DBXM_GZQD b
        where a.RS_DBGL_DBXM_GZQD_LSH = b.RS_DBGL_DBXM_GZQD_LSH
            and b.RS_DBGL_GZQD_LSH = #{ dto.ids }
            order by a.bae003 desc
    </select>
    <select id="getDbtxList" resultType="HashMap" parameterType="com.zjrs.zwnw.dbgl.dto.DbrwglDbrwclGzjzshQueryDTO">
        select * from (
            select a.rs_dbgl_gzqd_lsh as "rsdbglgzqdlsh",
                a.gzqd as "gzqd",
                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",
                'sh'  as "lx"
            from V_RS_DBGL_GZQD_YX a
            where a.SHZT = '02'
        )order by to_number("sysx")

    </select>

    <select id="getDbfpList" resultType="HashMap" parameterType="com.zjrs.zwnw.dbgl.dto.DbrwglDbrwclGzjzshQueryDTO">
        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",
        f_common_getSSgUserOrg((select orgid
        from v_rs_dbgl_dbxm_zrbm c
        where c.RS_DBGL_DBXM_LSH =
        b.RS_DBGL_DBXM_LSH
        and c.LX = '1')) as "qtbm",
        a.tbzq as "tbzq",
        a.sctbjzrq as "sctbjzrq"
        from V_RS_DBGL_DBRW_YX a, V_RS_DBGL_DBXM_YX b
        where a.rs_dbgl_dbrw_lsh = b.RS_DBGL_DBRW_LSH
    </select>

    <select id="getDbtbList" resultType="HashMap" parameterType="com.zjrs.zwnw.dbgl.dto.DbrwglDbrwclGzjzshQueryDTO">
        select * from (
        select a.rs_dbgl_gzqd_lsh as "rsdbglgzqdlsh",
        a.gzqd as "gzqd",
        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",
        'tb' as "lx"
        from V_RS_DBGL_GZQD_YX a
        where a.zrr = #{dto.userid}
        and exists (select 1
        from V_RS_DBGL_DBRW_YX   b,
        V_RS_DBGL_DBXM_YX   c,
        V_RS_DBGL_DBXM_GZQD d
        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
        )
        )order by to_number("sysx")

    </select>

    <select id="getDbshList" resultType="HashMap" parameterType="com.zjrs.zwnw.dbgl.dto.DbrwglDbrwclGzjzshQueryDTO">
        select * from (
        select a.rs_dbgl_gzqd_lsh as "rsdbglgzqdlsh",
        a.gzqd as "gzqd",
        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",
        'sh'  as "lx"
        from V_RS_DBGL_GZQD_YX a
        where a.SHZT = '0'
        and 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
        )
        )order by to_number("sysx")

    </select>
</mapper>
