<?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.bsdb.entity.DbsxMapper">

    <select id="getSeq" resultType="String">
        select seq_dbsx_lsh.nextval from dual
    </select>

    <select id="queryDbglWorkspaceDbList"
            parameterType="com.zjrs.zwnw.bsdb.dto.DbglPageQueryDTO"
            resultType="com.zjrs.zwnw.bsdb.dto.DbglDTO">
        select
        rs_bsdb_dbsx_lsh as "rsbsdbdbsxlsh",
        ywlsh,
        sllsh,
        rs_bsdb_sxlx as "rsbsdbsxlx",
        rs_bsdb_sxmc as "rsbsdbsxmc",
        rs_bsdb_sxjjzt as "rsbsdbsxjjzt",
        f_common_getSSgUserOrg(rs_bsdb_fzdw) as "rsbsdbfzdw",
        f_common_getSSgUser(fzr) as "fzr",
        f_common_getSSgUser(dbr) as "dbr",
        rs_bsdb_fbzt as "rsbsdbfbzt",
        rs_bsdb_jdzt as "rsbsdbjdzt",
        rs_bsdb_fkzt as "rsbsdbfkzt",
        bae003
        from
        (
        <!-- 新创建 未发布 创建人 -->
        SELECT A.* FROM RS_BSDB_DBSX A WHERE A.RS_BSDB_FBZT = '0' AND A.BAE002 = #{dto.bae002}        
        ) t
        where t.ts_yxbz = '1' and  (t.rs_bsdb_jdzt != '05' or t.rs_bsdb_jdzt is null)
        <if test="dto.rsbsdbdbsxlsh != null and dto.rsbsdbdbsxlsh != ''">
            and t.rs_bsdb_dbsx_lsh = #{ dto.rsbsdbdbsxlsh }
        </if>
        <if test="dto.bae003z != null and dto.bae003z != ''">
            and substr(t.bae003, 0, 10) <![CDATA[<=]]> #{ dto.bae003z }
        </if>
        order by t.bae005 desc
    </select>

    <select id="queryDbglWorkspaceYbList"
            parameterType="com.zjrs.zwnw.bsdb.dto.DbglPageQueryDTO"
            resultType="com.zjrs.zwnw.bsdb.dto.DbglDTO">
        select
        rs_bsdb_dbsx_lsh as "rsbsdbdbsxlsh",
        ywlsh,
        sllsh,
        rs_bsdb_sxlx as "rsbsdbsxlx",
        rs_bsdb_sxmc as "rsbsdbsxmc",
        rs_bsdb_sxjjzt as "rsbsdbsxjjzt",
        f_common_getSSgUserOrg(rs_bsdb_fzdw) as "rsbsdbfzdw",
        f_common_getSSgUser(fzr) as "fzr",
        f_common_getSSgUser(dbr) as "dbr",
        rs_bsdb_fbzt as "rsbsdbfbzt",
        rs_bsdb_jdzt as "rsbsdbjdzt",
        rs_bsdb_fkzt as "rsbsdbfkzt",
        rs_bsdb_pjsj as "rsbsdbpjsj",
        bae003
        from
        (SELECT A.* FROM RS_BSDB_DBSX A WHERE (bae002 = #{dto.bae002} or fzr = #{dto.bae002} or dbr = #{dto.bae002})) t
        where t.ts_yxbz = '1'
        <if test="dto.rsbsdbdbsxlsh != null and dto.rsbsdbdbsxlsh != ''">
            and t.rs_bsdb_dbsx_lsh = #{ dto.rsbsdbdbsxlsh }
        </if>
        <if test="dto.bae003c != null and dto.bae003c != ''">
            and substr(t.bae003, 0, 10) <![CDATA[>=]]> #{ dto.bae003c }
        </if>
        <if test="dto.bae003z != null and dto.bae003z != ''">
            and substr(t.bae003, 0, 10) <![CDATA[<=]]> #{ dto.bae003z }
        </if>
        order by t.bae005 desc
    </select>

    <select id="queryDbglTimeoutList"
            parameterType="com.zjrs.zwnw.bsdb.dto.VRsSlxxRemaindatePageQueryDTO"
            resultType="com.zjrs.zwnw.bsdb.dto.VRsSlxxRemaindateDTO">
        select  slbh, sxmc, syts, zbks, ssbmmc, bae003, sgorgid from v_rs_slxx_remaindate a
        where a.slbh not in (select b.sllsh from rs_bsdb_dbsx b where a.slbh = b.sllsh)
        <if test="dto.slbh != null and dto.slbh != ''">
            and a.slbh like '%'||#{ dto.slbh }||'%'
        </if>
        order by to_number(syts) asc
    </select>

    <select id="queryDbglList"
            parameterType="com.zjrs.zwnw.bsdb.dto.DbglPageQueryDTO"
            resultType="com.zjrs.zwnw.bsdb.dto.DbglDTO">
        select
            rs_bsdb_dbsx_lsh as "rsbsdbdbsxlsh",
            ywlsh,
            sllsh,
            rs_bsdb_sxlx as "rsbsdbsxlx",
            rs_bsdb_sxmc as "rsbsdbsxmc",
            rs_bsdb_sxjjzt as "rsbsdbsxjjzt",
            f_common_getSSgUserOrg(rs_bsdb_fzdw) as "rsbsdbfzdw",
            f_common_getSSgUser(fzr) as "fzr",
            f_common_getSSgUser(dbr) as "dbr",
            rs_bsdb_fbzt as "rsbsdbfbzt",
            rs_bsdb_jdzt as "rsbsdbjdzt",
            rs_bsdb_fkzt as "rsbsdbfkzt",
            rs_bsdb_pjzt as "rsbsdbpjzt",
            rs_bsdb_pjsj as "rsbsdbpjsj",
            bae003
        from rs_bsdb_dbsx a
        where a.ts_yxbz = '1'
        <if test="dto.rsbsdbdbsxlsh != null and dto.rsbsdbdbsxlsh != ''">
            and a.rs_bsdb_dbsx_lsh = #{ dto.rsbsdbdbsxlsh }
        </if>
        <if test="dto.bae003c != null and dto.bae003c != ''">
            and substr(a.bae003, 0, 10) <![CDATA[>=]]> #{ dto.bae003c }
        </if>
        <if test="dto.bae003z != null and dto.bae003z != ''">
            and substr(a.bae003, 0, 10) <![CDATA[<=]]> #{ dto.bae003z }
        </if>
        order by a.bae005 desc
    </select>

    <insert id="insertDbglSms" parameterType="com.zjrs.zwnw.bsdb.dto.DbglSmsDTO">
        insert into sendsms(smsindex,phonenumber,smscontent,smstime,status)
        values(SEQ_SENDSMS.nextval, #{dto.phonenumber}, #{dto.smscontent}, sysdate,'0')
    </insert>


    <select id="queryReportformsList"
            parameterType="com.zjrs.zwnw.bsdb.dto.DbglPageQueryDTO"
            resultType="com.zjrs.zwnw.bsdb.dto.DbglDTO">
        select
            t.rs_bsdb_dbsx_lsh as "rsbsdbdbsxlsh",
            t.rs_bsdb_dbgz_lsh as "rsbsdbdbgzlsh",
            t.ywlsh,
            t.sllsh,
            t.rs_bsdb_sxlx as "rsbsdbsxlx",
            t.rs_bsdb_sxmc as "rsbsdbsxmc",
            t.rs_bsdb_sxjjzt as "rsbsdbsxjjzt",
            f_common_getSSgUserOrg(t.rs_bsdb_fzdw)  as "rsbsdbfzdw",
            f_common_getSSgUser(t.fzr) as "fzr",
            f_common_getSSgUser(t.dbr) as "dbr",
            t.rs_bsdb_fbzt as "rsbsdbfbzt",
            t.rs_bsdb_jdzt as "rsbsdbjdzt",
            t.bae003,
            t.blsx,
            t.yellow_bz as "yellowbz",
            t.red_bz as "redbz",
            t.rs_bsdb_pfsj as "rsbsdbpfsj",
            t.cqsj as "cqsj",
            '1' cqzt,
            t.rs_bsdb_pjzt as "rsbsdbpjzt"
        from (
        select
            a.rs_bsdb_dbsx_lsh, b.rs_bsdb_dbgz_lsh, ywlsh, sllsh,
            rs_bsdb_sxlx, rs_bsdb_sxmc, rs_bsdb_sxjjzt, rs_bsdb_fzdw,
            fzr, dbr, rs_bsdb_fbzt, rs_bsdb_jdzt, a.bae003, b.blsx,
            b.yellow_bz, b.red_bz, a.rs_bsdb_pfsj,a.bae005,
            f_common_getTimeoutDay(a.rs_bsdb_pfsj) cqsj, a.rs_bsdb_pjzt
        from rs_bsdb_dbsx a, rs_bsdb_dbgz b
        where
        a.rs_bsdb_dbsx_lsh = b.rs_bsdb_dbsx_lsh
        and a.ts_yxbz = '1' and a.rs_bsdb_fbzt = '1' and b.ts_yxbz = '1'
        and a.rs_bsdb_pfzt = '1'
        ) t
        <where>
            <trim prefixOverrides="and">
                <if test="dto.rsbsdbdbsxlsh != null and dto.rsbsdbdbsxlsh != ''">
                    and t.rs_bsdb_dbsx_lsh = #{ dto.rsbsdbdbsxlsh }
                </if>
                <if test="dto.bae003z != null and dto.bae003z != ''">
                    and substr(t.bae003, 0, 10) <![CDATA[<=]]> #{ dto.bae003z }
                </if>
            </trim>
        </where>
        order by t.bae005 desc
    </select>

    <select id="queryStatisForDbsxhj"
            parameterType="com.zjrs.zwnw.bsdb.dto.DbglReportFormsQueryDTO"
            resultType="HashMap">
        select
            cnt as "cnt",
            nvl(finish_cnt, '0') as "finish_cnt",
            nvl(round((finish_cnt/cnt)*100,2), '0')||'%' as "pct_cnt" from
        (select
            count(a.rs_bsdb_dbsx_lsh) cnt,
            sum(case when rs_bsdb_jdzt = '05' then 1 else 0 end) finish_cnt
        from rs_bsdb_dbsx a
        where a.rs_bsdb_fbzt = '1'
        <if test="dto.bae003c != null and dto.bae003c != ''">
            and substr(a.bae003, 0, 10) <![CDATA[>=]]> #{ dto.bae003c }
        </if>
        )
    </select>

    <select id="queryStatisForDbsxfltj"
            parameterType="com.zjrs.zwnw.bsdb.dto.DbglReportFormsQueryDTO"
            resultType="HashMap">
        select
            (select AAA103 from v_ggfw_dict c where c.aaa102 = a.rs_bsdb_sxlx AND AAA100 = 'RS_BSDB_SXLX') as "type",
            count(a.rs_bsdb_sxlx) as "value"
        from rs_bsdb_dbsx a
        where a.rs_bsdb_fbzt = '1'
        <if test="dto.bae003c != null and dto.bae003c != ''">
            and substr(a.bae003, 0, 10) <![CDATA[>=]]> #{ dto.bae003c }
        </if>
        group by a.rs_bsdb_sxlx
        order by a.rs_bsdb_sxlx asc
    </select>

    <select id="queryStatisForDbsxzttj"
            parameterType="com.zjrs.zwnw.bsdb.dto.DbglReportFormsQueryDTO"
            resultType="HashMap">
        select
        (select AAA103 from v_ggfw_dict c where c.aaa102 = nvl(a.rs_bsdb_jdzt, '01') AND AAA100 = 'RS_BSDB_JDZT') as "type",
        count(nvl(a.rs_bsdb_jdzt, '01')) as "value"
        from rs_bsdb_dbsx a
        where
        a.ts_yxbz = '1'
        <if test="dto.bae003c != null and dto.bae003c != ''">
            and substr(a.bae003, 0, 10) <![CDATA[>=]]> #{ dto.bae003c }
        </if>
        group by a.rs_bsdb_jdzt
        order by a.rs_bsdb_jdzt asc
    </select>

    <select id="queryStatisForYwsxfltj"
            parameterType="com.zjrs.zwnw.bsdb.dto.DbglReportFormsQueryDTO"
            resultType="HashMap">
        select
            nvl(type, '其他') as "type",
            value as "value"
        from (
        select
        (select aaa103 from v_ggfw_dict b where b.aaa102 = a.ywzt AND AAA100 = 'RS_SX_CONFIG_YWZT') type,
        count(1) value
        from v_rs_slxx_remaindate a
        <where>
            <trim prefixOverrides="and">
                <if test="dto.bae003c != null and dto.bae003c != ''">
                    and substr(a.bae003, 0, 10) <![CDATA[>=]]> #{ dto.bae003c }
                </if>
            </trim>
        </where>
        group by ywzt order by ywzt desc) t
    </select>

    <select id="queryStatisForFzdwsxtj"
            parameterType="com.zjrs.zwnw.bsdb.dto.DbglReportFormsQueryDTO"
            resultType="HashMap">
        select
        c.org_name as "type",
        count(a.rs_bsdb_fzdw) as "value"
        from rs_bsdb_dbsx a, rs_bsdb_dbgz b, s_sg_org c
        where
        a.rs_bsdb_dbsx_lsh = b.rs_bsdb_dbsx_lsh
        and c.id = a.rs_bsdb_fzdw and c.is_valid = '1'
        and a.ts_yxbz = '1' and b.ts_yxbz = '1'
        <if test="dto.bae003c != null and dto.bae003c != ''">
            and substr(a.bae003, 0, 10) <![CDATA[>=]]> #{ dto.bae003c }
        </if>
        and c.area_id = #{ dto.xzqh }
        group by c.org_name, a.rs_bsdb_fzdw
        order by a.rs_bsdb_fzdw asc
    </select>

    <select id="queryBsdbSysOrgForList" parameterType="Map" resultType="HashMap">
        select id as "code", org_name as "name" from s_sg_org c
        where c.is_valid = '1' and parent_id is not null
        <if test="params.aaa102 != null and params.aaa102 != ''">
            and c.area_id = #{params.aaa102}
        </if>
    </select>

    <select id="queryGgfwDictForList" parameterType="Map" resultType="HashMap">
        select aaa102 as "code", aaa103 as "name" from v_ggfw_dict c
        where aaa100 = #{params.aaa100}
        <if test="params.aaa102 != null and params.aaa102 != ''">
            and aaa102 like '%'||#{params.aaa102}||'%'
        </if>
    </select>

    <select id="querySSgOrgList" parameterType="Map" resultType="com.zjrs.zwnw.bsdb.dto.TreeNodeDTO">
        select org_name as "title", id as "value", id as "key", parent_id as "pid"
        from s_sg_org where is_valid = '1' and order_number is not null
        <if test="params.area_id != null and params.area_id != ''">
            and area_id like '%'||#{params.area_id}||'%'
        </if>
        <choose>
            <when test="params.pid != null and params.pid != ''">
                and parent_id is not null
            </when>
            <otherwise>
                and parent_id is null
            </otherwise>
        </choose>
    </select>

    <select id="querySSgUserList" resultType="com.zjrs.zwnw.bsdb.dto.TreeNodeDTO">
        select user_name as "title", a.id as "value", a.id as "key", org_id as "pid"
        from s_sg_user a, s_sg_user_org b where a.id = b.user_id 
    </select>

    <select id="querySSgUserOrgId" parameterType="String" resultType="String">
        select org_id from s_sg_user a, s_sg_user_org b where a.id = b.user_id 
        and a.id = #{id}
    </select>

    <select id="querySSgUser" parameterType="String" resultType="com.zjrs.zwnw.bsdb.dto.SmsUserDTO">
        select user_name as "username", a.id as "id", phone_number as "phonenumber"
        from s_sg_user a where a.id = #{id}
    </select>

    <select id="queryUserOrg" parameterType="Map" resultType="com.zjrs.zwnw.bsdb.dto.DictDTO">
        select org_name as "title", id as "value", id as "key", parent_id as "pid"
        from s_sg_org where order_number is not null
    </select>

    <select id="queryUserAreaId" parameterType="String" resultType="String">
        select area_id as "area_id" from s_sg_org a where a.id = #{id}
    </select>

    <select id="getCurrentTaskCqzt" parameterType="String" resultType="String">
        select (case when to_number(t.cqsj) > to_number(t.blsx) then '1' else '0' end) cqzt
        from (
        select
            f_common_getTimeoutDay(a.rs_bsdb_pfsj) cqsj, b.blsx
        from rs_bsdb_dbsx a, rs_bsdb_dbgz b
        where
        a.rs_bsdb_dbsx_lsh = b.rs_bsdb_dbsx_lsh
        and a.rs_bsdb_dbsx_lsh = #{id}
        ) t
    </select>

</mapper>
