<?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.YdzwshMapper">
    <select id="getZphList" parameterType="com.zjrs.zwnw.rlzysc.dto.YdZphQueryDTO" resultType="HashMap">
        <!-- 只查询已发布的招聘会-->
        select a.zphmc as "zphmc",
        a.jbrqc || '~' || a.jbrqz as "jbsj",
        a.jbcs as "jbcs",
        a.rs_rlzy_zphgl_lsh as "rsrlzyzphgllsh",
        '' as "zphzt",
        f_jbxxgl_getAllAreaNameLine(a.bae001) "bae001"
        from v_rs_rlzy_zphgl a where a.ts_yxbz = '1'
        <if test="dto.zphbh != null and dto.zphbh != ''">
            and a.zphbh = #{dto.zphbh}
        </if>
        order by a.bae005 DESC
    </select>

    <select id="getYdzwshList" parameterType="com.zjrs.zwnw.rlzysc.dto.YdQueryDTO" resultType="HashMap">
        <!-- 只有申请了预约且尚未取消预约的数据可以显示，外网可以将审核通过的展位取消预约，需要将审核结果置为0，取消预约的数据不能重复提交预约
        需要重新申请预约-->
        select * from (select a.yypc as "yypc",
        a.yyr as "yyr",
        a.shyj as "shyj",
        a.shsj as "shsj",
        a.shjg as "shjg",
        (select user_name
        from v_rs_sy_org_area
        where user_id = a.zwyy_shr) as bae002,
        (select max(org_name)
        from v_rs_sy_org_area
        where org_id = a.shorg) as bae001,
        a.lxfs as "a.lxfs",
        c.sjhm as "sjhm",
        c.gsmc as "gsmc",
        (select listagg(bb.zwwzmc, ',') within group(order by bb.zwwzmc) "zwmc"
        from v_rs_rlzy_zwwzyysh aa, v_rs_rlzy_zwwzgl bb
        where aa.rs_zwwz_lsh = bb.rs_rlzy_zwwzgl_lsh
        and aa.yypc = a.yypc) as "ydzw"
        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
        where 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
        and a.rs_rlzy_zphgl_lsh = #{dto.rsrlzyzphgllsh}
        <if test="dto.shjg != null and dto.shjg != ''">
            and a.shjg = #{dto.shjg}
        </if>
        order by a.bae005 desc) ORDER BY  "ydzw"
    </select>

    <select id="getYdxx" parameterType="String" resultType="HashMap">
        select b.gsmc as "gsmc",
        a.yyr as "yyr",
        a.lxfs as "lxfs",
        a.lxr as "lxr",
        a.shyj as "shyj"
        from v_rs_rlzy_zwwzyysh a, v_rs_rlzy_qyxx b
        where a.rs_rlzy_qyxx_lsh = b.rs_rlzy_qyxx_lsh
        and a.rs_rlzy_zwwzyysh_lsh in
        (select max(rs_rlzy_zwwzyysh_lsh) id
        from v_rs_rlzy_zwwzyysh
        where yypc = #{yypc})
    </select>

    <select id="getzwdzconcat" parameterType="String" resultType="String">
        <!-- 获取同批次预定的展位位置名称-->
        select wm_concat(b.zwwzmc) as "zwwzmc"
        from v_rs_rlzy_zwwzyysh a, v_rs_rlzy_zwwzgl b
        where a.rs_zwwz_lsh = b.rs_rlzy_zwwzgl_lsh
        and a.yypc = #{yypc}
    </select>

    <select id="getzyxxList" parameterType="com.zjrs.zwnw.rlzysc.dto.ZjQueryListDTO" resultType="HashMap">
        <!-- 根据预约批次查询预约展位对应的招聘信息-->
        select distinct zpgwbh as "zpgwbh",
        zwmc   as "zwmc",
        xz     as "xz",
        zprs   as "zprs",
        whcd   as "whcd",
        gzjy   as "gzjy",
        lxdz   as "lxdz"
        from v_rs_rlzy_zwwzyyzwxx a,
        (select max(rs_rlzy_qyxx_lsh) rs_rlzy_qyxx_lsh,max(rs_rlzy_zphgl_lsh) rs_rlzy_zphgl_lsh,
        max(rs_rlzy_hcgl_lsh) rs_rlzy_hcgl_lsh, max(rs_rlzy_zwgl_lsh) rs_rlzy_zwgl_lsh
        from v_rs_rlzy_zwwzyysh b
        where yypc = #{dto.zjlsh}) b,v_rs_rlzy_zwxx c
        where a.rs_rlzy_zphgl_lsh = b.rs_rlzy_zphgl_lsh
        and a.rs_rlzy_hcgl_lsh = b.rs_rlzy_hcgl_lsh
        and a.rs_rlzy_qyxx_lsh = b.rs_rlzy_qyxx_lsh
        and a.rs_rlzy_zwgl_lsh = b.rs_rlzy_zwgl_lsh
        and a.rs_rlzy_zwxx_lsh = c.rs_rlzy_zwxx_lsh
    </select>
</mapper>
