<?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.ggfw.rlzysc.entity.ZphYdMapper">
    <select id="getZphYdList" parameterType="com.zjrs.ggfw.rlzysc.dto.dwzp.ZphYdQueryDTO" resultType="HashMap">
        <!-- 显示已发布且未开始的招聘会-->
        select
        ydzt              as "ydzt",
        zphbh             as "zphbh",
        zphmc             as "zphmc",
        jbrqc||' '||kssj||' ~ '||jbrqz||' ' ||jssj            as "cxsj",
        jbrqc             as "jbrqc",
        jbcs              as "jbcs",
        cs                as "cs",
        zzws              as "zzws",
        zwmc              as "zwmc",
        hcmc              as "hcmc",
        rs_rlzy_zwgl_lsh  as "rsrlzyzwgllsh",
        rs_rlzy_zphgl_lsh as "rsrlzyzphgllsh",
        rs_rlzy_hcgl_lsh  as "rsrlzyhcgllsh",
        zphlx             as "zphlx",
        '' as "zphzt"
        from (select f_rlzy_getYdzt(c.rs_rlzy_zwgl_lsh,#{dto.rsrlzyqyxxlsh}) ydzt,
        b.zphbh,
        b.zphmc,
        b.zphzt,
        b.jbrqc,
        b.jbrqz,
        b.kssj,
        b.jssj,
        b.jbcs,
        b.cs,
        a.hcmc,
        a.zzws,
        c.zwmc,
        c.rs_rlzy_zwgl_lsh,
        b.rs_rlzy_zphgl_lsh,
        a.rs_rlzy_hcgl_lsh,
        b.zphlx
        from v_rs_rlzy_hcgl a, v_rs_rlzy_zphgl b, v_rs_rlzy_zwgl c
        where a.ts_yxbz = '1'
        and b.ts_yxbz = '1'
        and c.ts_yxbz = '1'
        and a.rs_rlzy_zphgl_lsh = b.rs_rlzy_zphgl_lsh
        and a.rs_rlzy_hcgl_lsh = c.rs_rlzy_hcgl_lsh
        and a.fbzt = '1'
        and b.fbzt = '1'
        and c.fbzt = '1'
        and b.zphjblx = '01'
        )
        where 1 = 1
        <if test="dto.rsrlzyzphgllsh != null and dto.rsrlzyzphgllsh != ''">
            and rs_rlzy_zphgl_lsh = #{dto.rsrlzyzphgllsh}
        </if>
        order by jbrqc desc
    </select>


    <select id="getZphBmList" parameterType="com.zjrs.ggfw.rlzysc.dto.dwzp.ZphYdQueryDTO" resultType="HashMap">
        <!-- 显示已发布且未开始的招聘会-->
        select
        bmzt              as "bmzt",
        zphbh             as "zphbh",
        zphmc             as "zphmc",
        jbrqc||' '||kssj||' ~ '||jbrqz||' ' ||jssj            as "cxsj",
        jbrqc             as "jbrqc",
        jbcs              as "jbcs",
        cs                as "cs",
        rs_rlzy_zphgl_lsh as "rsrlzyzphgllsh",
        zphlx             as "zphlx",
        '' as "zphzt"
        from ( select
        '1' bmzt,
        b.zphbh,
        b.zphmc,
        b.zphzt,
        b.jbrqc,
        b.jbrqz,
        b.kssj,
        b.jssj,
        b.jbcs,
        b.cs,
        b.rs_rlzy_zphgl_lsh,
        b.zphlx
        from v_rs_rlzy_zphgl b
        where b.ts_yxbz = '1'
        and b.fbzt = '1'
        and b.zphjblx = '02'
        )
        where 1 = 1
        <if test="dto.rsrlzyzphgllsh != null and dto.rsrlzyzphgllsh != ''">
            and rs_rlzy_zphgl_lsh = #{dto.rsrlzyzphgllsh}
        </if>
        <if test="dto.bmzt != null and dto.bmzt != ''">
            and bmzt = #{ dto.bmzt }
        </if>
        order by jbrqc desc
    </select>


    <select id="getZwToZphHcXx" parameterType="String" resultType="Map">
        <!-- 根据展位管理流水号获取招聘会信息,查询已发布的数据-->
        select c.zt as "zt",<!--主题-->
        c.zphjs as "zphjs",
        c.zphmc as "zphmc",
        c.jbrqc as "jbrqc",
        c.jbrqz as "jbrqz",
        c.kssj as "kssj",
        c.jssj as "jssj",
        c.jbdw as "jbdw",
        c.cbdw as "cbdw",
        b.rs_rlzy_hcgl_lsh as "rsrlzyhcgllsh",
        c.rs_rlzy_zphgl_lsh as "rsrlzyzphgllsh",
        a.rs_rlzy_zwgl_lsh as "rsrlzyzwgllsh",
        a.sfzczxzw as "sfzczxzw"
        from v_rs_rlzy_zwgl a,v_rs_rlzy_hcgl b,v_rs_rlzy_zphgl c
        where a.rs_rlzy_hcgl_lsh = b.rs_rlzy_hcgl_lsh
        and b.rs_rlzy_zphgl_lsh = c.rs_rlzy_zphgl_lsh
    </select>

    <select id="getZwList" parameterType="HashMap" resultType="HashMap">
        select rs_rlzy_zwwzgl_lsh as "rsrlzyzwwzgllsh",
        hsh as "hsh",
        lsh as "lsh",
        zwzt as "zwzt",
        '1' as "isxz",
        '0' as "istrxz",
        '' as "zwwzmc"
        from v_rs_rlzy_zwwzgl a where rs_rlzy_zwgl_lsh = #{map.rsrlzyzwgllsh}
        and hsh = #{map.hsh} order by to_number(lsh)
    </select>

    <select id="getDixtinctHshList" parameterType="String" resultType="String">
        select distinct hsh from v_rs_rlzy_zwwzgl where rs_rlzy_zwgl_lsh = #{rsrlzyzwgllsh} order by to_number(hsh)
    </select>

    <select id="getzyxxList" parameterType="String" resultType="HashMap">
        <!-- 根据企业查询预约展位对应的招聘信息-->
        select c.zpgwbh as "zpgwbh",
        c.zwmc as "zwmc",
        c.xz as "xz",
        c.zprs as "zprs",
        c.rs_rlzy_zwxx_lsh as "rsrlzyzwxxlsh",
        c.whcd as "whcd",
        c.gzjy as "gzjy",
        c.lxdz as "lxdz",
        '1'    as "gxzt"
        from v_rs_rlzy_zwxx       c,
        v_rs_rlzy_qyxx       b
        where b.rs_yhlsh = #{userid}
        and c.rs_rlzy_qyxx_lsh = b.rs_rlzy_qyxx_lsh
        and c.zpfs in ('0','1')
        and c.fbzt = '1'
    </select>

    <select id="getWlzphzyxxList" resultType="java.util.Map">
        <!-- 根据企业查询预约展位对应的招聘信息-->
        select c.zpgwbh as "zpgwbh",
        c.zwmc as "zwmc",
        c.xz as "xz",
        c.zprs as "zprs",
        c.rs_rlzy_zwxx_lsh as "rsrlzyzwxxlsh",
        c.whcd as "whcd",
        c.gzjy as "gzjy",
        c.lxdz as "lxdz"
        from v_rs_rlzy_zwxx       c,
        v_rs_rlzy_qyxx       b
        where b.rs_yhlsh = #{userid}
        and c.rs_rlzy_qyxx_lsh = b.rs_rlzy_qyxx_lsh
        and c.fbzt = '1'
    </select>
    
    <insert id="batchinsertzwsh" useGeneratedKeys="false">
        begin
        <foreach collection="list" index="index" item="zwshpo">
            insert into v_rs_rlzy_zwwzyysh
            (rs_rlzy_zwwzyysh_lsh, rs_rlzy_zphgl_lsh, rs_rlzy_hcgl_lsh,
            rs_rlzy_qyxx_lsh, rs_zwwz_lsh, rs_rlzy_zwgl_lsh, yyr, yyrq, lxfs, lxr, yypc, sfyy, sftjnwsh,
            ts_cjr,ts_cjsj)
            values(
            #{zwshpo.rsrlzyzwwzyyshlsh},
            #{zwshpo.rsrlzyzphgllsh},
            #{zwshpo.rsrlzyhcgllsh},
            #{zwshpo.rsrlzyqyxxlsh},
            #{zwshpo.rszwwzlsh},
            #{zwshpo.rsrlzyzwgllsh},
            #{zwshpo.yyr},
            #{zwshpo.yyrq},
            #{zwshpo.lxfs},
            #{zwshpo.lxr},
            #{zwshpo.yypc},
            #{zwshpo.sfyy},
            #{zwshpo.sftjnwsh},
            #{zwshpo.tscjr},
            #{zwshpo.tscjsj}
            );
        </foreach>
        end;
    </insert>

    <select id="getZphzt" parameterType="String" resultType="String">
        select (case when (zphzt = '0' and to_date(jbrqc||' '||kssj,'YYYY-MM-DD HH24:MI:SS') > sysdate) then '1'
        when (zphzt = '0' and ((to_date(jbrqz||' '||jssj,'YYYY-MM-DD HH24:MI:SS') > sysdate) and (sysdate >to_date(jbrqc||' '||kssj,'YYYY-MM-DD HH24:MI:SS')))) then '2'
        when (zphzt = '1' or sysdate > to_date(jbrqz||' '||jssj,'YYYY-MM-DD HH24:MI:SS')) then '3'
        else '' end) as "zphzt" from v_rs_rlzy_zphgl where rs_rlzy_zphgl_lsh = #{rsrlzyzphgllsh}
    </select>

    <select id="getZphksrq" parameterType="String" resultType="Map">
        select a.jbrqc as "jbrqc", a.zphmc as "zphmc", a.bae002 as "bae002" from rs_rlzy_zphgl a, rs_rlzy_zwgl b, rs_rlzy_hcgl c
          where a.rs_rlzy_zphgl_lsh = c.rs_rlzy_zphgl_lsh and b.rs_rlzy_hcgl_lsh = c.rs_rlzy_hcgl_lsh
          and b.rs_Rlzy_zwgl_lsh = #{zjlsh}
    </select>

</mapper>
