<?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.rlzysc.entity.VRsRlzyZphglMapper">
    <sql id="zphfbList_where">
        <if test="dto.fbzt != null and dto.fbzt != ''">
            and v.fbzt = #{dto.fbzt}
        </if>
        <if test="dto.zphjblx != null and dto.zphjblx != ''">
            and v.zphjblx = #{dto.zphjblx}
        </if>
    </sql>
    <select id="getZphfbList" parameterType="com.zjrs.zwnw.rlzysc.dto.ZphQueryDTO" resultType="HashMap">
        <!--
        1.招聘会管理获取列表：无论是否发布，只要有效都查询出来
        2.新增会场或展位时需要勾选的会场列表：只有未发布未删除的数据可以被查询出来（传入发布状态参数）
        havezw:该招聘会下是否拥有展位，0为否，1为是，没有展位前端应该提示不能发布，删除
        isyy：该招聘会下是否有预约，如有预约，则在取消发布操作弹出确认框：该招聘会已有预约，是否确认取消发布
        zphzt字段：1为手动结束，0为未设置手动结束
        -->
        select v.zphbh as "zphbh",
        v.zphmc as "zphmc",
        v.jbcs as "jbcs",
        v.zphjs as "zphjs",
        v.jbrqc||' '||v.kssj || '~' || v.jbrqz||' '||v.jssj as "jbrq",
        v.jbrqc as "jbrqc",
        v.jbrqz as "jbrqz",
        v.uzphzt as "zphzt",
        v.fbzt as "fbzt",
        v.zphjblx as "zphjblx",
        v.bmzt as "bmzt",
        v.bae001 as "ssdq",
        v.zdzt as "zdzt",
<!--        (select decode(count(a.rs_rlzy_zphgl_lsh),0,'0','1')-->
<!--        from v_rs_rlzy_zphgl a,v_rs_rlzy_hcgl b,v_rs_rlzy_zwgl c-->
<!--        where a.rs_rlzy_zphgl_lsh = b.rs_rlzy_zphgl_lsh-->
<!--        and b.rs_rlzy_hcgl_lsh = c.rs_rlzy_hcgl_lsh-->
<!--        and a.ts_yxbz = '1' and b.ts_yxbz = '1' and c.ts_yxbz = '1'-->
<!--        and a.rs_rlzy_zphgl_lsh = v.rs_rlzy_zphgl_lsh) as "havezw",-->
<!--        (select decode(count(a.rs_rlzy_zphgl_lsh),0,'0','1')-->
<!--        from v_rs_rlzy_zwwzyysh a-->
<!--        where a.rs_rlzy_zphgl_lsh = v.rs_rlzy_zphgl_lsh and a.shjg = '1' ) as "isyy",-->
        rs_rlzy_zphgl_lsh as "rsrlzyzphgllsh"
        from (select g.*,
        (case when (g.zphzt = '0' and to_date(g.jbrqc||' '||g.kssj,'YYYY-MM-DD HH24:MI:SS') > sysdate) then '1'
        when (g.zphzt = '0' and ((to_date(g.jbrqz||' '||g.jssj,'YYYY-MM-DD HH24:MI:SS') > sysdate) and (sysdate >to_date(g.jbrqc||' '||g.kssj,'YYYY-MM-DD HH24:MI:SS')))) then '2'
        when (g.zphzt = '1' or sysdate > to_date(g.jbrqz||' '||g.jssj,'YYYY-MM-DD HH24:MI:SS')) then '3'
        else '' end) uzphzt
        from v_rs_rlzy_zphgl g ) v where 1=1
        <include refid="zphfbList_where" />
        order by v.zphbh desc
    </select>

    <select id="getZphXx" parameterType="com.zjrs.zwnw.rlzysc.dto.ZjQueryDTO" resultType="HashMap">
        select a.zphbh as "zphbh",a.zphmc as "zphmc",a.zphlx as "zphlx",a.bae001 as "ssdq",
        a.jbrqc as "jbrqc",a.jbrqz as "jbrqz",a.zphjblx as "zphjblx",a.rs_tycc_lsh as "rstycclsh",
        a.kssj as "kssj",a.jssj as "jssj",a.jbcs as "jbcs",a.cs as "cs",a.lxzj as "lxzj",
        a.lxr as "lxr",a.lxdh as "lxdh",a.dzyx as "dzyx",a.cz as "cz",a.jbdw as "jbdw",a.cbdw as "cbdw",
        a.chdws as "chdws",a.zws as "zws",a.zt as "zt",a.zphjs as "zphjs",
        a.bz as "bz", a.rs_rlzy_zphgl_lsh as "rsrlzyzphgllsh",
        (select user_name from v_rs_sy_org_area where user_id = a.bae002) as "bae002",
        a.bae003 as "bae003",
        (select ORG_NAME from s_sg_org where ID=(select ORG_ID from s_sg_user_org where user_id= a.bae002 and rownum=1)) as "bae006",
        (select user_name from v_rs_sy_org_area where user_id = a.bae004) as "bae004",
        a.bae005 as "bae005",
        (select max(org_name) from v_rs_sy_org_area where org_id = a.bae006) as "bae001",
        a.bmzt as "bmzt",
        0 zpgws,
        (select count(distinct a.rs_rlzy_qyxx_lsh) from v_rs_rlzy_zwwzyyzwxx a,v_rs_rlzy_zwxx b where
        a.rs_rlzy_zphgl_lsh = #{dto.zjlsh} and a.rs_rlzy_zwxx_lsh = b.rs_rlzy_zwxx_lsh ) bmcjqys,
        (SELECT sum(t2.zprs) FROM V_RS_RLZY_ZWWZYYZWXX t1, V_RS_RLZY_ZWXX t2 WHERE t1.RS_RLZY_ZWXX_LSH = t2.RS_RLZY_ZWXX_LSH
        AND t1.RS_RLZY_ZPHGL_LSH = a.rs_rlzy_zphgl_lsh and t2.ts_yxbz = '1') zprs,
        a.xbdw as "xbdw"
        from v_rs_rlzy_zphgl a where a.rs_rlzy_zphgl_lsh = #{dto.zjlsh} and a.ts_yxbz = '1'
    </select>

    <select id="getZphWscs" parameterType="String" resultType="HashMap">
        select count(distinct d.RS_RLZY_JLGL_LSH) as "wscs"
        from v_rs_rlzy_zphgl      a,
        v_rs_rlzy_zwwzyyzwxx b,
        v_RS_RLZY_ZWXX         c,
        v_rs_rlzy_jlgl         d
        where a.zphjblx = '02'
        and a.RS_RLZY_ZPHGL_LSH = b.RS_RLZY_ZPHGL_LSH
        and c.rs_rlzy_qyxx_lsh = b.RS_RLZY_QYXX_LSH
        and d.rs_rlzy_zwxx_lsh = c.rs_rlzy_zwxx_lsh
        and b.rs_rlzy_zwxx_lsh = c.rs_rlzy_zwxx_lsh
        and b.RS_RLZY_ZPHGL_LSH = #{rsrlzyzphgllsh}
    </select>

    <select id="getZphbh"  parameterType="String" resultType="HashMap">
        select (case when max(zphbh)='' then '' else max(substr(zphbh,9)) end)  as "fw"
        from v_rs_rlzy_zphgl where substr(zphbh,0,8)=#{date}
    </select>

    <!-- 用于判断招聘会，会场，展位的关系-->
    <select id="getZphHcZwCnt"  parameterType="Map" resultType="HashMap">
        select a.rs_rlzy_zphgl_lsh as "rsrlzyzphgllsh",
        b.rs_rlzy_hcgl_lsh as "rsrlzyhcgllsh",
        c.rs_rlzy_zwgl_lsh as "rsrlzyzwgllsh"
        from v_rs_rlzy_zphgl a,v_rs_rlzy_hcgl b,v_rs_rlzy_zwgl c
        where a.rs_rlzy_zphgl_lsh = b.rs_rlzy_zphgl_lsh
        and b.rs_rlzy_hcgl_lsh = c.rs_rlzy_hcgl_lsh
        
        <if test="hclsh!=null and hclsh!=''">
            and b.rs_rlzy_hcgl_lsh = #{hclsh}
        </if>
        <if test="zwlsh!=null and zwlsh!=''">
            and c.rs_rlzy_zwgl_lsh = #{zwlsh}
        </if>
    </select>

    <!-- 用于获取参加网络招聘会单位信息-->
    <select id="getWlZphQyXx"  parameterType="com.zjrs.zwnw.rlzysc.dto.ZphQueryDTO" resultType="HashMap">
        select a.rs_rlzy_qyxx_lsh, b.AAB004 gsmc,b.aab998,b.sjhm,count(a.rs_rlzy_zwxx_lsh) zpgws from v_rs_rlzy_zwwzyyzwxx a,rs_rlzy_qyxx b,rs_rlzy_zwxx c where
        rs_rlzy_zphgl_lsh = #{dto.zphlsh} and a.rs_rlzy_qyxx_lsh = b.rs_rlzy_qyxx_lsh
        and a.rs_rlzy_zwxx_lsh = c.rs_rlzy_zwxx_lsh
        group by a.rs_rlzy_qyxx_lsh,b.AAB004,b.aab998,b.sjhm
    </select>

    <!-- 用于获取参加网络招聘会职位信息-->
    <select id="getWlZphZwXx"  parameterType="com.zjrs.zwnw.rlzysc.dto.ZphQueryDTO" resultType="HashMap">
        select c.rs_rlzy_zwxx_lsh, b.gsmc,b.aab998,b.sjhm,c.zwmc,c.xz,c.zprs,c.whcd,c.gzjy,c.lxdz,c.lxdh
        from v_rs_rlzy_zwwzyyzwxx a,rs_rlzy_qyxx b,rs_rlzy_zwxx c where
        rs_rlzy_zphgl_lsh = #{dto.zphlsh} and a.rs_rlzy_qyxx_lsh = b.rs_rlzy_qyxx_lsh
        and a.rs_rlzy_zwxx_lsh = c.rs_rlzy_zwxx_lsh
    </select>

    <!-- 用于导出网络招聘会职位信息-->
    <select id="exportWlzphZwXx"  parameterType="com.zjrs.zwnw.rlzysc.dto.ZjDTO" resultType="com.zjrs.zwnw.rlzysc.dto.ExportZphZwXxResDTO">
        select b.gsmc as "gsmc",b.aab998 as "aab998",
        c.zwmc as "zwmc",(select aaa103 from v_ggfw_dict where aaa100 = 'RS_RLZY_XZFW' AND AAA102 = c.xz) as "xz",
        c.zprs as "zprs",
        (select aaa103 from v_ggfw_dict where aaa100 = 'JBXX_AAC011' AND AAA102 = c.whcd) as "whcd",
        (select aaa103 from v_ggfw_dict where aaa100 = 'RS_RLZY_GZJY' AND AAA102 = c.gzjy) as "gzjy",
        c.lxdz as "lxdz",c.lxdh as "lxdh"
        from v_rs_rlzy_zwwzyyzwxx a,rs_rlzy_qyxx b,rs_rlzy_zwxx c where
        rs_rlzy_zphgl_lsh = #{dto.zjlsh} and a.rs_rlzy_qyxx_lsh = b.rs_rlzy_qyxx_lsh
        and a.rs_rlzy_zwxx_lsh = c.rs_rlzy_zwxx_lsh
    </select>

    <!-- 批量发布招聘会-->
    <update id="updateZphFb" useGeneratedKeys="false">
        begin
        <foreach collection="list" index="index" item="map">
            update v_rs_rlzy_zphgl
            <set>
                <trim suffixOverrides=",">
                    fbzt = '1',
                    <if test="map.bae004!=null and map.bae004!=''">
                        bae004=#{map.bae004},
                    </if>
                    <if test="map.bae005!=null and map.bae005!=''">
                        bae005=#{map.bae005},
                    </if>
                    <if test="map.bae006!=null and map.bae006!=''">
                        bae006=#{map.bae006},
                    </if>
                </trim>
            </set>
            where rs_rlzy_zphgl_lsh = #{map.rsrlzyzphgllsh};
        </foreach>
        end;
    </update>


    <!-- 批量取消发布招聘会-->
    <update id="updateZphQxFb" useGeneratedKeys="false">
        begin
        <foreach collection="list" index="index" item="map">
            update v_rs_rlzy_zphgl
            <set>
                <trim suffixOverrides=",">
                    fbzt = '1',
                    <if test="map.bae004!=null and map.bae004!=''">
                        bae004=#{map.bae004},
                    </if>
                    <if test="map.bae005!=null and map.bae005!=''">
                        bae005=#{map.bae005},
                    </if>
                    <if test="map.bae006!=null and map.bae006!=''">
                        bae006=#{map.bae006},
                    </if>
                </trim>
            </set>
            where rs_rlzy_zphgl_lsh = #{map.rsrlzyzphgllsh};
        </foreach>
        end;
    </update>

    <select id="queryExportDataList"  parameterType="list" resultType="com.zjrs.zwnw.rlzysc.dto.ZphglExportResultDTO">
        SELECT rownum as "rownum",q.* FROM (select t.* from (
        select distinct
        c.gsmc as "gsmc",
        c.GSJS as "gsjs",
        (select aaa103 from aaa3 where aaa102 = c.aab022) as "sshy",
        z.zwmc as "zwmc",
        z.zprs as "zprs",
        (select aaa103 from v_ggfw_dict where AAA100 like'JBXX_AAC011' and AAA102= z.whcd) as "whcd",
        z.gzdd as "gzdd",
        c.hrlxfs as "hrlxfs",
        c.sjhm as "sjhm",
        c.hrmc as "hrmc",
        z.lxdz  as "lxdz",
        (select detail from s_sg_dict where KIND_CODE = 'RS_RLZY_XZFW' AND CODE = xz)||' '
        ||f_rs_rlzy_get_gsfl(c.gsfl) as "xzfl",
        (SELECT zwwzmc FROM v_rs_rlzy_zwwzgl j WHERE j.RS_RLZY_ZWWZGL_LSH = a.rs_zwwz_lsh) "zwwzmc",
        z.gzms  as "gzms"
        from v_rs_rlzy_zwwzyysh a,
        (select max(rs_rlzy_zwwzyysh_lsh) id
        from v_rs_rlzy_zwwzyysh
        group by yypc) b,
        v_rs_rlzy_qyxx c,
        v_rs_rlzy_zphgl d,
        v_rs_rlzy_zwwzyyzwxx q,
        v_rs_rlzy_zwxx z
        WHERE
        q.rs_rlzy_zphgl_lsh = a.rs_rlzy_zphgl_lsh
        AND q.rs_rlzy_qyxx_lsh = a.rs_rlzy_qyxx_lsh
        AND q.rs_rlzy_hcgl_lsh = a.rs_rlzy_hcgl_lsh
        AND q.rs_rlzy_zwgl_lsh = a.rs_rlzy_zwgl_lsh
        AND q.rs_rlzy_zwxx_lsh = z.rs_rlzy_zwxx_lsh
        AND a.rs_rlzy_zwwzyysh_lsh = b.id
        and a.RS_RLZY_ZPHGL_LSH = d.RS_RLZY_ZPHGL_LSH
        and c.rs_rlzy_qyxx_lsh(+) = a.rs_rlzy_qyxx_lsh
        <if test="ywlsh!=null and ywlsh!='' ">
            and a.rs_rlzy_zphgl_lsh = #{ywlsh}
        </if>
        ) t order BY substr(t."zwwzmc",1,1),to_number(substr(t."zwwzmc",2))) q
    </select>

    <select id="queryQychtjList" parameterType="com.zjrs.zwnw.rlzysc.dto.QychtjDTO" resultType="HashMap">
            select t.rs_rlzy_qyxx_lsh as "rsrlzyqyxxlsh", t.gsmc as "dwmc", t.aab998 as "tyshxydm", f_rs_rlzy_get_dwzph_count(t.rs_rlzy_qyxx_lsh) as "cnt",f_jbxxgl_getAllAreaNameLine(aab078) as "xjzdxzqh" from
            (select c.rs_rlzy_qyxx_lsh, c.gsmc, c.aab998,
            c.aab078
            from v_rs_rlzy_zphgl a,
            (select b.rs_rlzy_zphgl_lsh , b.rs_rlzy_qyxx_lsh from v_rs_rlzy_zwwzyysh b where b."SHJG" = '1'
            group by b.rs_rlzy_zphgl_lsh , b.rs_rlzy_qyxx_lsh ) b, v_rs_rlzy_qyxx c
            where a.rs_rlzy_zphgl_lsh = b.rs_rlzy_zphgl_lsh
            and b.rs_rlzy_qyxx_lsh = c.rs_rlzy_qyxx_lsh
            and a.rs_rlzy_zphgl_lsh = #{dto.zphlsh}
            <if test="dto.dwmc!=null and dto.dwmc!='' ">
                and c.gsmc like '%'||#{dto.dwmc}||'%'
            </if>
            group by c.rs_rlzy_qyxx_lsh, c.gsmc, c.aab998,c.aab078
            union all
            select t1.rs_rlzy_qyxx_lsh, t1.gsmc, t1.aab998,
            aab078 from (
            select c.rs_rlzy_qyxx_lsh, c.gsmc, c.aab998, b.rs_rlzy_zphgl_lsh,c.aab078
            from v_rs_rlzy_zwwzyyzwxx a, v_rs_rlzy_zphgl b, v_rs_rlzy_qyxx c
            where a.rs_rlzy_zphgl_lsh = b.rs_rlzy_zphgl_lsh
            and a.rs_rlzy_qyxx_lsh = c.rs_rlzy_qyxx_lsh
            and b.rs_rlzy_zphgl_lsh = #{dto.zphlsh}
            <if test="dto.dwmc!=null and dto.dwmc!='' ">
                and c.gsmc like '%'||#{dto.dwmc}||'%'
            </if>
            <if test="dto.tyshxydm!=null and dto.tyshxydm!='' ">
                and c.aab998 like '%'||#{dto.tyshxydm}||'%'
            </if>
            group by c.rs_rlzy_qyxx_lsh, c.gsmc, c.aab998, b.rs_rlzy_zphgl_lsh,c.aab078
            ) t1 group by t1.rs_rlzy_qyxx_lsh, t1.gsmc, t1.aab998,t1.aab078
            ) t
            group by t.rs_rlzy_qyxx_lsh, t.gsmc, t.aab998,t.aab078
            order by t.gsmc asc
    </select>

    <select id="queryExportQychtj" parameterType="com.zjrs.zwnw.rlzysc.dto.QychtjDTO" resultType="com.zjrs.zwnw.rlzysc.dto.QychtjExportResultDTO">
        select rownum as "rownum", t.gsmc as "dwmc", t.aab998 as "tyshxydm",t.hrmc as "hrmc",t.zphbh as "zphbh", t.zphmc  as "zphmc", t.jbrqc || ' ' || t.kssj || ' 至 ' || t.jbrqz || ' ' || t.jssj AS "jbrq",
        '' as "zphlx", t.zwmc  as "zwmc" , t.xz  as "xz" ,
        t.zprs  as "zprs" , t.lxdz  as "lxdz" , t.lxdh  as "lxdh" , t.gzms  as "gzms", t.gsjs,t.sshy   from (
        select  c.gsmc, c.aab998,c.hrmc, a.zphbh, a.zphmc, a.jbrqc, a.kssj, a.jbrqz, a.jssj ,a.zphjblx,d.zwmc,
        (select AAA103
        from v_ggfw_dict
        where AAA100 = 'RS_RLZY_XZFW'
        and AAA102 = d.XZ) xz,
        d.zprs,
        d.lxdz,
        d.lxdh,
        d.gzms,c.gsjs,(select aaa103 from aaa3 where aaa102 = c.aab022) sshy
        from v_rs_rlzy_zphgl a,
        (select b.rs_rlzy_zphgl_lsh , b.rs_rlzy_qyxx_lsh from v_rs_rlzy_zwwzyysh b where b."SHJG" = '1'
        group by b.rs_rlzy_zphgl_lsh , b.rs_rlzy_qyxx_lsh ) b,
        v_rs_rlzy_qyxx c,v_RS_RLZY_ZWXX d
        where a.rs_rlzy_zphgl_lsh = b.rs_rlzy_zphgl_lsh
        and b.rs_rlzy_qyxx_lsh = c.rs_rlzy_qyxx_lsh
        and a.rs_rlzy_zphgl_lsh = #{dto.zphlsh}
        and c.rs_rlzy_qyxx_lsh = d.rs_rlzy_qyxx_lsh
        <if test="dto.dwmc!=null and dto.dwmc!='' ">
            and c.gsmc like '%'||#{dto.dwmc}||'%'
        </if>
        <if test="dto.tyshxydm!=null and dto.tyshxydm!='' ">
            and c.aab998 like '%'||#{dto.tyshxydm}||'%'
        </if>
        ) t order by t.gsmc asc,t.jbrqc desc

    </select>

    <select id="queryQychtjDetailsList" parameterType="com.zjrs.zwnw.rlzysc.dto.QychtjDTO" resultType="HashMap">
        select t.bae001 as "ssdq",t.gsmc as "dwmc", t.aab998 as "tyshxydm",t.zphbh as "zphbh", t.zphmc  as "zphmc", t.jbrqc || ' ' || t.kssj || ' 至 ' || t.jbrqz || ' ' || t.jssj AS "jbrq",
        '' as "zphlx" from
        (select  a.bae001,c.gsmc, c.aab998, a.zphbh, a.zphmc, a.jbrqc, a.kssj, a.jbrqz, a.jssj ,a.zphjblx
        from v_rs_rlzy_zphgl a,
        (select b.rs_rlzy_zphgl_lsh , b.rs_rlzy_qyxx_lsh from v_rs_rlzy_zwwzyysh b where b."SHJG" = '1'
        group by b.rs_rlzy_zphgl_lsh , b.rs_rlzy_qyxx_lsh ) b,
        v_rs_rlzy_qyxx c
        where a.rs_rlzy_zphgl_lsh = b.rs_rlzy_zphgl_lsh
        and b.rs_rlzy_qyxx_lsh = c.rs_rlzy_qyxx_lsh
        and c.rs_rlzy_qyxx_lsh = #{dto.rsrlzyqyxxlsh}
        <if test="dto.dwmc!=null and dto.dwmc!='' ">
            and c.gsmc like '%'||#{dto.dwmc}||'%'
        </if>
        <if test="dto.tyshxydm!=null and dto.tyshxydm!='' ">
            and c.aab998 like '%'||#{dto.tyshxydm}||'%'
        </if>
        <if test="dto.tjnyc!=null and dto.tjnyc!='' ">
            and  to_date(substr(jbrqc, 0, 7), 'yyyy-mm') <![CDATA[>=]]> to_date(#{dto.tjnyc}, 'yyyy-mm')
        </if>
        <if test="dto.tjnyz!=null and dto.tjnyz!='' ">
            and  to_date(substr(jbrqc, 0, 7), 'yyyy-mm') <![CDATA[<=]]> to_date(#{dto.tjnyz}, 'yyyy-mm')
        </if>
        ) t
        order by t.jbrqc desc
    </select>


    <select id="queryExportZwtj" parameterType="com.zjrs.zwnw.rlzysc.dto.QychtjDTO" resultType="com.zjrs.zwnw.rlzysc.dto.ZwtjExportResultDTO">
        SELECT (SELECT RS_RLZY_ZYXX_MC FROM
        RS_RLZY_ZYXX T1 WHERE T1.RS_RLZY_ZYXX_BM = E.PARENT_RS_RLZY_ZYXX_LSH)
        as "parentzwmc", E.RS_RLZY_ZYXX_MC as "zwmc", SUM(D.ZPRS) as "num" FROM RS_RLZY_ZWWZYYZWXX A, RS_RLZY_QYXX B, RS_RLZY_ZPHGL C, RS_RLZY_ZWXX D, RS_RLZY_ZYXX E
        WHERE A.RS_RLZY_QYXX_LSH = B.RS_RLZY_QYXX_LSH AND A.RS_RLZY_ZPHGL_LSH = C.RS_RLZY_ZPHGL_LSH AND A.RS_RLZY_ZWXX_LSH = D.RS_RLZY_ZWXX_LSH
        AND D.ZPGZ = E.RS_RLZY_ZYXX_BM
        AND C.RS_RLZY_ZPHGL_LSH  = #{dto.zphlsh}
        AND B.aab078 like '%4408%'
        GROUP BY E.PARENT_RS_RLZY_ZYXX_LSH, E.RS_RLZY_ZYXX_MC
    </select>

    <select id="exportZwtjRylb" parameterType="com.zjrs.zwnw.rlzysc.dto.QychtjDTO" resultType="com.zjrs.zwnw.rlzysc.dto.ZwtjExportResultDTO">

        select (select aaa103 from  v_ggfw_dict where aaa100 = 'RS_RLZY_RYLB' and aaa102= G.TS_RYLB) as "zwmc", COUNT(distinct d.RS_RLZY_JLGL_LSH) AS "num"
        from v_rs_rlzy_zphgl      a,
        v_rs_rlzy_zwwzyyzwxx b,
        v_RS_RLZY_ZWXX         c,
        v_rs_rlzy_jlgl         d, RS_RLZY_JLXX G
        where a.zphjblx = '02'
        and a.RS_RLZY_ZPHGL_LSH = b.RS_RLZY_ZPHGL_LSH AND d.RS_RLZY_JLXX_LSH = G.RS_RLZY_JLXX_LSH
        and c.rs_rlzy_qyxx_lsh = b.RS_RLZY_QYXX_LSH
        and d.rs_rlzy_zwxx_lsh = c.rs_rlzy_zwxx_lsh
        and b.RS_RLZY_ZPHGL_LSH =  #{dto.zphlsh}
        GROUP BY G.TS_RYLB
    </select>


    <select id="getZphWscsList" parameterType="String" resultType="com.zjrs.zwnw.rlzysc.dto.WscsmxListDTO">
        select distinct (select xm from v_rs_rlzy_jlxx jl where jl.RS_RLZY_JLXX_LSH=d.RS_RLZY_JLXX_LSH and rownum=1)  as "xm",
        a.ZPHMC as "zphmc",
        d.TS_CJSJ as "tdsj",
        c.ZWMC as"zwmc",
        (select AAB004 from v_RS_RLZY_QYXX where RS_RLZY_QYXX_LSH=c.RS_RLZY_QYXX_LSH and rownum=1)   as"dwmc"
        from v_rs_rlzy_zphgl      a,
        v_rs_rlzy_zwwzyyzwxx b,
        v_RS_RLZY_ZWXX         c,
        v_rs_rlzy_jlgl         d
        where a.zphjblx = '02'
        and a.RS_RLZY_ZPHGL_LSH = b.RS_RLZY_ZPHGL_LSH
        and c.rs_rlzy_qyxx_lsh = b.RS_RLZY_QYXX_LSH
        and b.rs_rlzy_zwxx_lsh = c.rs_rlzy_zwxx_lsh
        and d.rs_rlzy_zwxx_lsh = c.rs_rlzy_zwxx_lsh
        and b.RS_RLZY_ZPHGL_LSH = #{rsrlzyzphgllsh}
        and d.TS_CJSJ BETWEEN a.JBRQC||' 00:00:00' AND a.JBRQZ|| ' 23:59:59'
        order by d.TS_CJSJ desc
    </select>

    <select id="exportAllWscsmx" parameterType="com.zjrs.zwnw.rlzysc.dto.ZphQueryDTO" resultType="com.zjrs.zwnw.rlzysc.dto.WscsmxListDTO">
        select distinct (select xm from v_rs_rlzy_jlxx jl where jl.RS_RLZY_JLXX_LSH=d.RS_RLZY_JLXX_LSH and rownum=1)  as "xm",
        a.rs_rlzy_zphgl_lsh,
        a.ZPHMC as "zphmc",
        d.TS_CJSJ as "tdsj",
        c.ZWMC as"zwmc",
        (select AAB004 from v_RS_RLZY_QYXX where RS_RLZY_QYXX_LSH=c.RS_RLZY_QYXX_LSH and rownum=1)   as"dwmc"
        from v_rs_rlzy_zphgl      a,
        v_rs_rlzy_zwwzyyzwxx b,
        v_RS_RLZY_ZWXX         c,
        v_rs_rlzy_jlgl         d
        where 
        a.RS_RLZY_ZPHGL_LSH = b.RS_RLZY_ZPHGL_LSH
        and c.rs_rlzy_qyxx_lsh = b.RS_RLZY_QYXX_LSH
        and b.RS_RLZY_ZWXX_LSH = c.RS_RLZY_ZWXX_LSH
        and d.rs_rlzy_zwxx_lsh = c.rs_rlzy_zwxx_lsh
        <if test="dto.zphjblx != null and dto.zphjblx != ''">
            and a.zphjblx = #{dto.zphjblx}
        </if>
        <if test="dto.zphmc != null and dto.zphmc != ''">
            and a.zphmc like '%${ dto.zphmc }%'
        </if>
        <if test="dto.ssdq != null and dto.ssdq != ''">
            and a.bae001 = #{dto.ssdq}
        </if>
        and d.TS_CJSJ BETWEEN a.JBRQC||' 00:00:00' AND a.JBRQZ|| ' 23:59:59'
        order by a.rs_rlzy_zphgl_lsh,d.TS_CJSJ desc
    </select>

    <select id="exportBmcjqysDc" parameterType="com.zjrs.zwnw.rlzysc.dto.ZphQueryDTO" resultType="com.zjrs.zwnw.rlzysc.dto.ExportBmcjQysDTO">
        SELECT f_rs_rlzy_get_dwzph_count(c.RS_RLZY_QYXX_LSH) "cnt", c.RS_RLZY_QYXX_LSH,c.AAB004 "qymc",f_jbxxgl_getAllAreaNameLine(c.aab078) "zcddxzqh" FROM v_rs_rlzy_zwwzyyzwxx a,V_RS_RLZY_ZPHGL b,V_RS_RLZY_QYXX c
        WHERE a.RS_RLZY_ZPHGL_LSH  = b.RS_RLZY_ZPHGL_LSH AND a.RS_RLZY_QYXX_LSH = c.RS_RLZY_QYXX_LSH
        <if test="dto.zphjblx != null and dto.zphjblx != ''">
            and b.zphjblx = #{dto.zphjblx}
        </if>
        GROUP BY c.RS_RLZY_QYXX_LSH,c.AAB004,c.AAB078
    </select>

    <select id="exportQychtjRylbtj" parameterType="com.zjrs.zwnw.rlzysc.dto.ZphQueryDTO" resultType="com.zjrs.zwnw.rlzysc.dto.ZwtjExportResultDTO">
        select (select aaa103 from  v_ggfw_dict where aaa100 = 'RS_RLZY_RYLB' and aaa102= G.TS_RYLB) as "zwmc", COUNT(distinct d.RS_RLZY_JLGL_LSH) AS "num"
        from v_rs_rlzy_zphgl      a,
        v_rs_rlzy_zwwzyyzwxx b,
        v_RS_RLZY_ZWXX         c,
        v_rs_rlzy_jlgl         d, RS_RLZY_JLXX G
        where a.zphjblx = '02'
        and a.RS_RLZY_ZPHGL_LSH = b.RS_RLZY_ZPHGL_LSH AND d.RS_RLZY_JLXX_LSH = G.RS_RLZY_JLXX_LSH
        and c.rs_rlzy_zwxx_lsh = b.RS_RLZY_ZWXX_LSH
        and d.rs_rlzy_zwxx_lsh = c.rs_rlzy_zwxx_lsh
        <if test="dto.fbzt != null and dto.fbzt != ''">
            and a.fbzt = #{dto.fbzt}
        </if>
        GROUP BY G.TS_RYLB
    </select>

    <select id="exportQychtjZwtj" parameterType="com.zjrs.zwnw.rlzysc.dto.ZphQueryDTO" resultType="com.zjrs.zwnw.rlzysc.dto.ZwtjExportResultDTO">
        select (select t.rs_rlzy_zyxx_mc
        from RS_RLZY_ZYXX t
        where t.RS_RLZY_ZYXX_BM = gzdl) AS "parentzwmc",
        (select t.rs_rlzy_zyxx_mc
        from RS_RLZY_ZYXX t
        where t.RS_RLZY_ZYXX_BM = gzzl) "zwmc",
        sum(gwsl) AS "gwnum",
        sum(zprs) AS "num",
        sum(wscs) "wscsnum"
        from (select substr(a.ZPGZ, 1, 8) || '000' gzdl,
        a.ZPGZ gzzl,
        a.rs_rlzy_zwxx_lsh,
        1 gwsl,
        a.ZPRS zprs,
        (select count(1)
        from rs_rlzy_jlgl t
        where t.rs_rlzy_zwxx_lsh = a.rs_rlzy_zwxx_lsh
        and c.JBRQC || c.KSSJ &lt;= t.ts_cjsj
        and c.JBRQZ || c.JSSJ &gt;= t.ts_cjsj) wscs
        from rs_rlzy_zphgl c, rs_rlzy_zwwzyyzwxx b, RS_RLZY_ZWXX a
        where c.zphjblx = '02'
        and c.RS_RLZY_ZPHGL_LSH = b.RS_RLZY_ZPHGL_LSH
        and a.rs_rlzy_zwxx_lsh = b.RS_RLZY_ZWXX_LSH
        and a.zpfs in ('0', '2')
        <if test="dto.fbzt != null and dto.fbzt != ''">
            and c.fbzt = #{dto.fbzt}
        </if>
        )
        GROUP BY gzdl, gzzl ORDER BY gzdl
    </select>

    <select id="exportQychtjHzBmqy" parameterType="com.zjrs.zwnw.rlzysc.dto.QychtjExportHzBmqyDTO" resultType="HashMap">
        select v.zphbh as "zphbh",
        v.zphmc as "zphmc",
        v.jbrqc || ' ' || v.kssj || '~' || v.jbrqz || ' ' || v.jssj as "jbrq",
        f_dict_get_aaa103('RS_RLZY_ZPHJBLX', v.zphjblx) as "zphjblx",
        f_dict_get_aaa103('JBXX_ZJ_XZQH', v.bae001) as "ssdq",
        (select count(distinct a.rs_rlzy_qyxx_lsh)
        from v_rs_rlzy_zwwzyyzwxx a, v_rs_rlzy_zwxx b
        where a.rs_rlzy_zphgl_lsh = v.RS_RLZY_ZPHGL_LSH
        and a.rs_rlzy_zwxx_lsh = b.rs_rlzy_zwxx_lsh) as "bmcjqys",
        (select count(1) from v_rs_rlzy_zphfw_log a where a.RS_RLZY_ZPHGL_LSH=v.RS_RLZY_ZPHGL_LSH) as "lll"
        from v_rs_rlzy_zphgl v
        where 1 = 1
        <include refid="zphfbList_where" />
        order by v.zphbh desc
    </select>
</mapper>
