<?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.DbrwjzcxMapper">
<select id="getDbrwjzcxList" resultType="HashMap" parameterType="com.zjrs.zwnw.dbgl.dto.DbrwglDbrwQueryDTO">
    select * from (select distinct f_dbgl_getYjdByGzqdlsh(b.rs_dbgl_gzqd_lsh,a.RS_DBGL_DBRW_LSH) as "yjd",
    e.rwzt as "wcbz",
    c.rs_dbgl_dbxm_gzqd_lsh as "rsdbgldbxmgzqdlsh",
    a.rs_dbgl_dbxm_lsh as "rsdbgldbxmlsh",
    c.rs_dbgl_gzqd_lsh as "rsdbglgzqdlsh",
    e.rs_dbgl_dbrw_lsh as "rsdbgldbrwlsh",
    e.dbbh as "dbbh",
    f_common_getSSgUserOrg(d.ORGID) "qtbm" ,
    <if test="dto.rsdbgldbxmlsh != null and dto.rsdbgldbxmlsh != ''">
        f_common_getSSgUserOrg(d.ORGID) "xbbm",
    </if>
    (select (SELECT q.USER_NAME FROM S_SG_USER q WHERE q.id = t3.FGLD)
    from V_S_SG_ORG t1, v_rs_dbgl_dbxm_zrbm t2, V_S_SG_ORG_EX t3
    where t1.id = t3.id
    and t2.ORGID = t3.id
    and t2.LX = '1'
    and t2.RS_DBGL_DBXM_LSH = a.RS_DBGL_DBXM_LSH) "fgld",
    (SELECT count(1) from V_RS_DBGL_DBXM_YX   a1,
    V_RS_DBGL_GZQD_YX   b1,
    V_RS_DBGL_DBXM_GZQD c1,
    v_rs_dbgl_dbxm_zrbm d1,
    V_RS_DBGL_DBRW_YX e1
    where a1.ISLEAF = '1'
    and a1.RS_DBGL_DBXM_LSH = c1.RS_DBGL_DBXM_LSH
    and b1.RS_DBGL_GZQD_LSH = c1.RS_DBGL_GZQD_LSH
    and a1.RS_DBGL_DBXM_LSH = d1.RS_DBGL_DBXM_LSH
    and b1.ZRBM=d1.ORGID
    AND a1.RS_DBGL_DBRW_LSH = e1.RS_DBGL_DBRW_LSH
    and a1.RS_DBGL_DBXM_LSH = a.RS_DBGL_DBXM_LSH
    AND e1.RS_DBGL_DBRW_LSH = e.RS_DBGL_DBRW_LSH
    AND e1.TS_YXBZ = '1'
    AND a1.TS_YXBZ = '1' AND b1.TS_YXBZ = '1' AND  d1.LX = '2'
    ) "existXbbm",
    nvl(c.gzqd,b.gzqd) as "gzqd",
    '' as "gzjz",
    '' as "czwt",
    '' as "xgbgz",
    '' as "wcjd",
    '' as "sdpj",
    '' as "zlpj",
    '' as "shsj",
    d.lx as "lx",
    a.xmbh as "xmbh",
    a.xmmc as "xmmc",
    f_common_getSSgUserOrg(d.ORGID) as "orgname",
    f_dbgl_getPjByCode(d.SDPJ,d.ZLPJ) as "dbpj",
    f_dbgl_getPjByCode(b.SDPJ,b.ZLPJ) as "zp",
    d.rs_dbgl_dbxm_zrbm_lsh as "rsdbgldbxmzrbmlsh"
   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,
        V_RS_DBGL_DBRW_YX   e
  where a.RS_DBGL_DBRW_LSH = e.RS_DBGL_DBRW_LSH
    <if test="dto.rwzt != null and dto.rwzt != ''">
        and e.rwzt = #{dto.rwzt}
    </if>
    <if test="dto.bae001 != null and dto.bae001 != ''">
        and a.bae001 = #{dto.bae001}
    </if>
    <if test="dto.bae003Start != null and dto.bae003Start != ''">
        and e.bae003 <![CDATA[ >= ]]> #{dto.bae003Start}||' 00:00:00'
    </if>
    <if test="dto.bae003End != null and dto.bae003End != ''">
        and e.bae003 <![CDATA[ <= ]]> #{dto.bae003End}||' 23:59:59'
    </if>
    <if test="dto.lx != null and dto.lx != ''">
        and d.lx = #{ dto.lx }
    </if>
    <if test="dto.rsdbgldbxmlsh != null and dto.rsdbgldbxmlsh != ''">
        AND a.RS_DBGL_DBXM_LSH = #{ dto.rsdbgldbxmlsh }
    </if>
    )
    ORDER BY "dbbh" DESC,"xmbh","shsj" DESC
</select>

    <select id="exportDbrwjzcxList" resultType="com.zjrs.zwnw.dbgl.dto.DbrwglExportrwcxDTO" parameterType="com.zjrs.zwnw.dbgl.dto.DbrwglDbrwQueryDTO">
        select * from (select distinct
        c.rs_dbgl_dbxm_gzqd_lsh as "rsdbgldbxmgzqdlsh",
        c.rs_dbgl_dbxm_lsh as "rsdbgldbxmlsh",
        c.rs_dbgl_gzqd_lsh as "rsdbglgzqdlsh",
        e.rs_dbgl_dbrw_lsh as "rsdbgldbrwlsh",
        e.dbbh as "dbbh",
        e.rwly as "rwly",
        '' "rwlyfl",
        '' "qtbm",
        '' as "xbbm",
        '' "fgld",
        nvl(c.gzqd,b.gzqd) as "gzqd",
        '' as "gzjz",
        '' as "czwt",
        '' as "xybgz",
        '' as "wcjd",
        '' as "sdpj",
        '' as "zlpj",
        '' as "tbsj",
        '' as "tbbm",
        d.lx as "lx",
        a.xmbh as "xmbh",
        a.xmmc as "xmmc",
        f_common_getSSgUserOrg(d.ORGID) as "orgname"  ,
        F_DICT_GET_AAA103('RS_DBGL_DBRW_SDPJ',d.sdpj) as "dbsdpj",
        F_DICT_GET_AAA103('RS_DBGL_DBRW_ZLPJ',d.zlpj) as "dbzlpj",
        d.lx
       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,
            V_RS_DBGL_DBRW_YX   e
      where a.ISLEAF = '1'
        AND a.RS_DBGL_DBRW_LSH = e.RS_DBGL_DBRW_LSH

        <if test="dto.rwzt != null and dto.rwzt != ''">
            and e.rwzt = #{dto.rwzt}
        </if>
        <if test="dto.rwly != null and dto.rwly != ''">
            and e.rwly LIKE '%'||#{dto.rwly}||'%'
        </if>
        <if test="dto.rwlyfl != null and dto.rwlyfl != ''">
            and e.rwlyfl = #{dto.rwlyfl}
        </if>
        <if test="dto.sdpj != null and dto.sdpj != ''">
            and c.sdpj = #{dto.sdpj}
        </if>
        <if test="dto.zlpj != null and dto.zlpj != ''">
            and c.zlpj = #{dto.zlpj}
        </if>
        <if test="dto.bae001 != null and dto.bae001 != ''">
            and a.bae001 = #{dto.bae001}
        </if>
        <if test="dto.bae003Start != null and dto.bae003Start != ''">
            and e.bae003 <![CDATA[ >= ]]> #{dto.bae003Start}||' 00:00:00'
        </if>
        <if test="dto.bae003End != null and dto.bae003End != ''">
            and e.bae003 <![CDATA[ <= ]]> #{dto.bae003End}||' 23:59:59'
        </if>
        <if test="dto.lx != null and dto.lx != ''">
            and d.lx = #{ dto.lx }
        </if>
        )
       ORDER BY "dbbh","xmbh",lx,"qtbm" nulls last,"xbbm" DESC
    </select>


    <select id="getDictMap" resultType="map" parameterType="com.zjrs.zwnw.dbgl.dto.DbrwglDbrwQueryDTO">
        select
        <trim suffixOverrides=",">
        1 "demo",
        <if test="dto.fgld != null and dto.fgld != ''">
        (select user_name from s_sg_user where id = #{dto.fgld}) "fgld",
        </if>
        <if test="dto.qtbm != null and dto.qtbm != ''">
        (select org_name from s_sg_org where id = #{dto.qtbm}) "qtbm",
        </if>
        <if test="dto.xbbm != null and dto.xbbm != ''">
        (select org_name from s_sg_org where id = #{dto.xbbm}) "xbbm",
        </if>
        <if test="dto.bae001 != null and dto.bae001 != ''">
        (select org_name from s_sg_org where id = #{dto.bae001}) "bae001",
        </if>
        <if test="dto.rwzt != null and dto.rwzt != ''">
        '' "rwzt",
        </if>
        <if test="dto.rwlyfl != null and dto.rwlyfl != ''">
        '' "rwlyfl",
        </if>
        <if test="dto.sdpj != null and dto.sdpj != ''">
        '' "sdpj",
        </if>
        <if test="dto.zlpj != null and dto.zlpj != ''">
        '' "zlpj",
        </if>
        </trim>
        from dual
    </select>


    <select id="getNeedSendSMSList" resultType="map">
        SELECT * FROM (select distinct f_dbgl_getYjdByGzqdlsh(b.rs_dbgl_gzqd_lsh,'') as "yjd",
        c.rs_dbgl_dbxm_lsh as "rsdbgldbxmlsh",
        e.rs_dbgl_dbrw_lsh as "rsdbgldbrwlsh",
        e.RWLY "rwmc",
        g.USER_NAME "username",
        g.PHONE_NUMBER "phonenumber"
        from V_RS_DBGL_DBXM_YX   a,
        V_RS_DBGL_GZQD_YX   b,
        V_RS_DBGL_DBXM_GZQD c,
        v_rs_dbgl_dbxm_zrbm d,
        V_RS_DBGL_DBRW_YX e,
        S_SG_ORG_EX f,
        S_SG_USER g
        where a.ISLEAF = '1'
        and a.RS_DBGL_DBXM_LSH = c.RS_DBGL_DBXM_LSH
        and b.RS_DBGL_GZQD_LSH = c.RS_DBGL_GZQD_LSH
        and a.RS_DBGL_DBXM_LSH = d.RS_DBGL_DBXM_LSH
        and b.ZRBM=d.ORGID
        AND a.RS_DBGL_DBRW_LSH = e.RS_DBGL_DBRW_LSH
        AND f.ID = d.ORGID
        )
        where "yjd" = '2'
        GROUP BY  "rsdbgldbxmlsh","rsdbgldbrwlsh","yjd","username","phonenumber","rwmc"
    </select>
</mapper>
