<?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.VRsRlzyZwglMapper">

    <select id="getZwglList" parameterType="com.zjrs.zwnw.rlzysc.dto.ZwQueryDTO" resultType="HashMap">
        <!-- isyy：该招聘会下是否有预约，如有预约，则在取消发布操作弹出确认框：该招聘会已有预约，是否确认取消发布-->
        select a.hcmc as "hcmc", c.rs_rlzy_zwgl_lsh as "rsrlzyzwgllsh",
        a.rs_rlzy_zphgl_lsh as "rsrlzyzphgllsh",
        a.rs_rlzy_hcgl_lsh as "rsrlzyhcgllsh",
        b.zphmc as "zphmc",
        c.jj as "jj",
        b.bae001 as "bae001",
        c.zwmc  as "zwmc",
        b.jbcs as "jbcs",
        b.jbrqc || '~' || b.jbrqz as "jbrq",
        c.fbzt as "fbzt",
        '' as "zphzt"
        from v_rs_rlzy_hcgl   a,
        v_rs_rlzy_zphgl  b,
        v_rs_rlzy_zwgl   c
        where a.rs_rlzy_zphgl_lsh = b.rs_rlzy_zphgl_lsh
        and a.rs_rlzy_hcgl_lsh = c.rs_rlzy_hcgl_lsh
        and a.ts_yxbz = '1'
        and b.ts_yxbz = '1'
        and c.ts_yxbz = '1'
        <if test="dto.zwmc != null and dto.zwmc != ''">
            and c.zwmc like '%${ dto.zwmc }%'
        </if>
        <if test="dto.hcmc != null and dto.hcmc != ''">
            and a.hcmc like '%${ dto.hcmc }%'
        </if>
        <if test="dto.zphmc != null and dto.zphmc != ''">
            and b.zphmc like '%${ dto.zphmc }%'
        </if>
        order by c.bae005 DESC
    </select>

    <select id="getZwsyqkckList" parameterType="com.zjrs.zwnw.rlzysc.dto.ZwQueryDTO" resultType="HashMap">
        <!-- 查询已开发给外网预约的数据-->
        select a.hcbh as "hcbh",
        b.zphmc as "zphmc",
        c.zwmc  as "zwmc",
        b.bae001 as "ssdq",
        a.hcmc as "hcmc",
        b.zphbh as "zphbh",
        b.jbcs as "jbcs",
        b.jbrqc || '~' || b.jbrqz as "jbrq",
        a.zzws as "zzws",
        c.rs_rlzy_zwgl_lsh as "rsrlzyzwgllsh"
        from v_rs_rlzy_hcgl   a,
        v_rs_rlzy_zphgl  b,v_rs_rlzy_zwgl c
        where a.rs_rlzy_zphgl_lsh = b.rs_rlzy_zphgl_lsh
        and a.rs_rlzy_hcgl_lsh = c.rs_rlzy_hcgl_lsh
        <if test="dto.hcbh != null and dto.hcbh != ''">
            and a.hcbh = #{dto.hcbh}
        </if>
        order by a.bae005 DESC
    </select>

    <select id="getHcToZphXx" parameterType="String" resultType="Map">
        <!-- 根据会场管理流水号获取招聘会信息-->
        select a.zphbh as "zphbh",
        a.zphmc as "zphmc",
        a.jbrqc as "jbrqc",
        a.jbrqz as "jbrqz",
        a.kssj as "kssj",
        a.jssj as "jssj",
        a.jbrqz as "jbrqz",
        a.jbcs as "jbcs",
        a.cs as "cs",
        b.hcbh as "hcbh",
        b.hcmc as "hcmc",
        a.bae001 as "ssdq"
        from v_rs_rlzy_zphgl a, v_rs_rlzy_hcgl b
        where b.rs_rlzy_zphgl_lsh = a.rs_rlzy_zphgl_lsh
        and b.rs_rlzy_hcgl_lsh = #{hcgllsh}
        <!-- 新增的时候，招聘会和会场都必须是未发布-->
    </select>

    <select id="getZwglXx" parameterType="String" resultType="Map">
        select a.rs_rlzy_zwgl_lsh as "rsrlzyzwgllsh",
        a.rs_rlzy_hcgl_lsh as "rsrlzyhcgllsh",
        a.zwmc as "zwmc",
        a.dd as "dd",
        a.kssj as "kssj",
        a.jssj as "jssj",
        a.jj as "jj",
        a.hs as "hs",
        a.ls as "ls",
        b.hcbh as "hcbh",
        b.hcmc as "hcmc",
        c.zphbh as "zphbh",
        c.zphmc as "zphmc",
        c.bae001 as "ssdq",
        (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= #{userid} and rownum=1)) as "bae001",        (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 "bae006",
        b.rs_rlzy_zphgl_lsh as "rsrlzyzphgllsh",
        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 a.rs_rlzy_zwgl_lsh = #{zwgllsh}
        <!-- 修改的时候，展位和会场都必须是未发布-->
    </select>

    <select id="getZwToZphHcXx" parameterType="String" resultType="Map">
        <!-- 根据展位管理流水号获取招聘会信息,查询已发布的数据-->
        select c.zt as "zt",
        c.zphmc as "zphmc",
        c.jbrqc as "jbrqc",
        c.jbrqz as "jbrqz",
        c.kssj as "kssj",
        c.jssj as "jssj",
        c.bae001 as "ssdq"
        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
        and a.rs_rlzy_zwgl_lsh = #{zwgllsh}
    </select>

    <!-- 批量发布展位-->
    <update id="updateZwFb" useGeneratedKeys="false">
        begin
        <foreach collection="list" index="index" item="map">
            update v_rs_rlzy_zwgl
            <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_zwgl_lsh = #{map.rsrlzyzwgllsh};
        </foreach>
        end;
    </update>


    <!-- 批量取消发布展位-->
    <update id="updateZwQxFb" useGeneratedKeys="false">
        begin
        <foreach collection="list" index="index" item="map">
            update v_rs_rlzy_zwgl
            <set>
                <trim suffixOverrides=",">
                    fbzt = '0',
                    <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_zwgl_lsh = #{map.rsrlzyzwgllsh};
        </foreach>
        end;
    </update>

    <select id="getZwsyqkckXxDetails" parameterType="com.zjrs.zwnw.rlzysc.dto.ZwQueryListDTO"  resultType="HashMap">
        select
            d.gsmc as "gsmc",
            zwmc as "zwmc",
            zprs as "zprs"
        from v_rs_rlzy_zwwzyyzwxx a,v_rs_rlzy_zwwzyysh b,
            v_rs_rlzy_zwxx c, v_rs_rlzy_qyxx d, rs_rlzy_zwwzgl e
        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
            and c.rs_rlzy_qyxx_lsh = d.rs_rlzy_qyxx_lsh
            and e.rs_rlzy_zwwzgl_lsh = b.rs_zwwz_lsh
            <if test="dto.rsrlzyzwgllsh!=null and dto.rsrlzyzwgllsh!=''">
                and a.rs_rlzy_zwgl_lsh = #{dto.rsrlzyzwgllsh}
            </if>
            <if test="dto.rsrlzyzwwzgllsh!=null and dto.rsrlzyzwwzgllsh!=''">
            and e.rs_rlzy_zwwzgl_lsh = #{dto.rsrlzyzwwzgllsh}
            </if>
        group by d.gsmc, c.zwmc, c.zprs
    </select>
</mapper>
