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

    <select id="getHcglList" parameterType="com.zjrs.zwnw.rlzysc.dto.HcQueryDTO" resultType="HashMap">
        <!--
        1.会场管理获取列表：无论是否发布，只要有效都查询出来
        2.新增展位时需要勾选的会场列表：只有未发布未删除的数据可以被查询出来（传入发布状态参数）
        havezw:该招聘会下是否拥有展位，0为否，1为是，没有展位前端应该提示不能发布，删除
        isyy：该招聘会下是否有预约，如有预约，则在取消发布操作弹出确认框：该招聘会已有预约，是否确认取消发布
        -->
        select v.hcbh as "hcbh",
        v.hcmc as "hcmc",
        v.hczmj as "hczmj",
        v.zzws as "zzws",
        v.zjbz as "zjbz",
        v.bae003 as "bae003",
        v.zphbh as "zphbh",
        v.rs_rlzy_hcgl_lsh as "rsrlzyhcgllsh",
        v.bae001 as "bae001",
        v.bae002 as "bae002",
        v.hcfbzt as "hcfbzt",
        v.zphfbzt as "zphfbzt",
        v.zphzt as "zphzt"
        from(
        select a.hcbh,
        a.hcmc,
        a.hczmj,
        a.zzws,
        a.zjbz,
        a.bae003,
        b.zphbh,
        b.zphmc,
        b.bae001,
        a.rs_rlzy_hcgl_lsh,
        a.fbzt as hcfbzt,
        b.fbzt as zphfbzt,
        b.jbrqc,
        b.kssj,
        b.jbrqz,
        b.jssj,
        '' as zphzt,
        (select user_name from v_rs_sy_org_area where user_id = a.bae002) as bae002,
        a.bae005 as bae005
        from v_rs_rlzy_hcgl a, v_rs_rlzy_zphgl b
        where a.rs_rlzy_zphgl_lsh = b.rs_rlzy_zphgl_lsh
        and a.ts_yxbz = '1' and b.ts_yxbz = '1') v where 1=1
        <if test="dto.fbzt != null and dto.fbzt != ''">
            and v.hcfbzt = #{dto.fbzt} and v.zphfbzt = #{dto.fbzt}
        </if>
        order by v.bae005 DESC
    </select>

    <select id="getHcXx" parameterType="com.zjrs.zwnw.rlzysc.dto.ZjQueryDTO" resultType="HashMap">
        select
        b.rs_rlzy_zphgl_lsh as "rsrlzyzphgllsh",b.zphmc as "zphmc",b.bae001 as "ssdq",
        a.hcbh as "hcbh",a.hcmc as "hcmc",a.zzws as "zzws",a.hczt as "hczt",a.lxr as "lxr",
        a.lxdh as "lxdh",
<!--        a.zjbz as "zjbz",-->
        a.rs_rlzy_hcgl_lsh as "rsrlzyhcgllsh",
        a.hcxz as "hcxz",a.zwghms as "zwghms",a.hczmj as "hczmj",a.zwzmj as "zwzmj",
        a.hcdz as "hcdz",a.bz as "bz",
        (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"
        from v_rs_rlzy_hcgl a,v_rs_rlzy_zphgl b
        where a.rs_rlzy_hcgl_lsh = #{dto.zjlsh} 
        and a.rs_rlzy_zphgl_lsh = b.rs_rlzy_zphgl_lsh
    </select>

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

    <!-- 批量发布会场-->
    <update id="updateHcFb" useGeneratedKeys="false">
        begin
        <foreach collection="list" index="index" item="map">
            update v_rs_rlzy_hcgl
            <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_hcgl_lsh = #{map.rsrlzyhcgllsh};
        </foreach>
        end;
    </update>

    <!-- 批量取消发布会场-->
    <update id="updateHcQxFb" useGeneratedKeys="false">
        begin
        <foreach collection="list" index="index" item="map">
            update v_rs_rlzy_hcgl
            <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_hcgl_lsh = #{map.rsrlzyhcgllsh};
        </foreach>
        end;
    </update>


</mapper>
