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

    <select id="getDwzpxxshList" parameterType="com.zjrs.zwnw.rlzysc.dto.ZpxxQueryDTO" resultType="HashMap">
        <!-- 只显示已提交审核尚未审核尚未发布且有效的数据
        企业信息审核通过的数据才显示-->
        select c.aab004 as "gsmc",
        c.aab078 as "aab078",
        f_jbxxgl_getAllAreaNameLine(c.aab078) as "zcddxzqhname",
        c.aab013 as "frxm",
        b.lxdh as "lxdh",
        b.zwmc as "zwmc",
        b.zprs as "zprs",
        b.rs_rlzy_zwxx_lsh as "rsrlzyzwxxlsh"
        from v_rs_rlzy_qyxx a, v_rs_rlzy_zwxx b,v_tyrz_user_dwjbxx c
        where a.rs_rlzy_qyxx_lsh(+) = b.rs_rlzy_qyxx_lsh
        and a.rs_yhlsh = c.useridcode(+)
        <if test="dto.zwmc != null and dto.zwmc != ''">
            and b.zwmc = #{dto.zwmc}
        </if>
        <if test="dto.tyshxydm != null and dto.tyshxydm != ''">
            and c.aab998 = #{dto.tyshxydm}
        </if>
        order by b.ts_xgsj DESC
    </select>

    <select id="getDwzpxxshXx" parameterType="com.zjrs.zwnw.rlzysc.dto.ZjQueryDTO" resultType="HashMap">
        select c.aab004 as "gsmc",
        c.aab998 as "tyshxydm",
        c.bze001 as "zcdd",
        f_jbxxgl_getAllAreaName(c.aab078) as "zcddxzqhdm",
        c.aab019 as "dwlx",
        c.aab020 as "jjlx",
        c.aab021 as "lsgx",
        c.aab022 as "sshy",
        c.aae006 as "dwdz",
        b.ts_yxbz as "tsyxbz",
        a.sjhm as "sjhm",
        b.dwdz as "dwdz",
        c.aae004 as "lxr",
        b.zpfs as "zpfs",
        b.sfydzpry as "sfydzpry",
        b.zpgz as "zpgz",
        b.gzxz as "gzxz",
        b.xz as "xz",
        b.gzms as "gzms",
        b.whcd as "whcd",
        b.gzjy as "gzjy",
        b.zprs as "zprs",
        b.xz as "xz",
        b.rs_nan as "rsnan",
        b.rs_nv as "rsnv",
        b.nl as "nl",
        b.sl as "sl",
        b.hkyq as "hkyq",
        b.sg_nan as "sgnan",
        b.sg_nv as "sgnv",
        b.syq as "syq",
        b.syqgz as "syqgz",
        b.ygxs as "ygxs",
        b.gzhj as "gzhj",
        b.zsdx as "zsdx",
        b.zph as "zph",
        b.lxr as "dwzplxr",
        b.lxdh as "lxdh",
        b.zwmc as "zwmc",
        b.dzyx as "dzyx",
        b.lxdz as "lxdz",
        b.gzdd as "gzdd",
        b.fldy as "fldy",
        b.qtfldy as "qtfldy",
        b.xzqhname as "xzqhname"
        from v_rs_rlzy_qyxx a, v_rs_rlzy_zwxx b,v_tyrz_user_dwjbxx c
        where a.rs_rlzy_qyxx_lsh(+) = b.rs_rlzy_qyxx_lsh
        and a.rs_yhlsh = c.useridcode(+)
        and b.rs_rlzy_zwxx_lsh = #{dto.zjlsh}
<!--        and a.qyxx_shzt = '1'-->
    </select>

    <select id="getDwzpxxglList" parameterType="com.zjrs.zwnw.rlzysc.dto.ZpxxQueryDTO" resultType="HashMap">
        <!-- 只显示审核通过且有效的数据
        企业信息审核通过-->
        select c.aab004 as "gsmc",
        c.aae006 as "gssz",
        a.hrmc as "hrmc",
        a.hrlxfs as "hrlxfs",
        a.lxzj as "lxzj",
        a.dzyx as "dzyx",
        b.zwmc as "zwmc",
        b.zprs as "zprs",
        b.whcd as "whcd",
        (select count(*) from v_rs_rlzy_zwxxfw_log d where d.rs_rlzy_zwxx_lsh = b.rs_rlzy_zwxx_lsh) as "recordnum",
        (select aaa103 from  v_ggfw_dict where aaa100 = 'JBXX_AAC011' and aaa102= b.whcd) as "whcdname",
        b.gzms as "gzms",
        b.xz as "xz",
        (select aaa103 from  v_ggfw_dict where aaa100 = 'RS_RLZY_XZFW' and aaa102= b.xz) as "xzname",
        b.rs_rlzy_zwxx_lsh as "rsrlzyzwxxlsh",
        b.zwzt as "zwzt",
        b.shsj as "shsj",
        b.fbsj as "fbsj",
        (select user_name from s_sg_user d where b.zwxxshr = d.id) as "zwxxshr",
        b.shjg as "shjg",
        b.shyj as "shyj"
        from v_rs_rlzy_qyxx a, v_rs_rlzy_zwxx b,v_tyrz_user_dwjbxx c
        where a.rs_rlzy_qyxx_lsh(+) = b.rs_rlzy_qyxx_lsh
        and a.rs_yhlsh = c.useridcode(+)
        order by b.SHSJ DESC nulls last,b.RS_RLZY_ZWXX_LSH
    </select>

    <select id="getDwzpxxglExportList" parameterType="com.zjrs.zwnw.rlzysc.dto.ZpxxQueryDTO" resultType="com.zjrs.zwnw.rlzysc.dto.DwzpxxglDTO" >
        select ROWNUM rn,v.* from (select
        c.aab004,
        c.aae006,
        c.aab998,
        a.hrmc,
        a.hrlxfs,
        a.lxzj,
        a.dzyx,
        b.zwmc,
        b.zprs,
        b.whcd,
        b.ts_xgsj,
        (select aaa103 from v_ggfw_dict where aaa100 = 'JBXX_AAC011' and aaa102= b.whcd) as whcdname,
        b.gzms,
        b.xz,
        (select aaa103 from v_ggfw_dict where aaa100 = 'RS_RLZY_XZFW' and aaa102= b.xz) as xzname,
        b.rs_rlzy_zwxx_lsh as rsrlzyzwxxlsh,
        (select aaa103 from v_ggfw_dict where aaa100 = 'RS_RLZY_HYZT' and aaa102= b.zwzt) as zwzt,
        b.shsj,
        b.fbsj,
        (SELECT COUNT(1) FROM RS_RLZY_ZWXXFW_LOG d WHERE
        b.RS_RLZY_ZWXX_LSH = d.RS_RLZY_ZWXX_LSH) lll
        from v_rs_rlzy_qyxx a, v_rs_rlzy_zwxx b,v_tyrz_user_dwjbxx c
        where a.rs_rlzy_qyxx_lsh(+) = b.rs_rlzy_qyxx_lsh
        and a.rs_yhlsh = c.useridcode(+)
        order by a.GSMC,b.FBSJ DESC)  v
    </select>

    <select id="getZpxxGwppList" parameterType="com.zjrs.zwnw.rlzysc.dto.ZpxxQueryDTO" resultType="HashMap">
        <!-- 求职推荐，只针对审核通过，且发布职位的数据-->
        select c.aab004 as "gsmc",
        c.aab013 as "frxm",
        b.lxdh as "lxdh",
        b.zwmc as "zwmc",
        b.zprs as "zprs",
        b.rs_rlzy_zwxx_lsh as "rsrlzyzwxxlsh",
        b.gzdd as "gzdd",
        b.whcd as "xl",
        b.xz as "xz"
        from v_rs_rlzy_qyxx a, v_rs_rlzy_zwxx b,v_tyrz_user_dwjbxx c
        where a.rs_rlzy_qyxx_lsh(+) = b.rs_rlzy_qyxx_lsh
        and a.rs_yhlsh = c.useridcode(+)
        <if test="dto.xz != null and dto.xz != ''">
            and b.xz = #{dto.xz}
        </if>
        order by b.ts_xgsj DESC
    </select>

    <select id="getZpxxYyshList" parameterType="com.zjrs.zwnw.rlzysc.dto.ZpxxQueryDTO" resultType="HashMap">
        select c.aab004 as "gsmc",
        (select count(rs_zwwz_lsh)
        from v_rs_rlzy_zwwzyysh
        where rs_rlzy_zwwzyysh_lsh = a.rs_rlzy_zwwzyysh_lsh) as "zws",
        a.RS_RLZY_QYXX_LSH as "RSRLZYQYXXLSH",
        c.aae004 as "lxr",
        c.aae006 as "lxdz",
        b.czhm as "czhm"
        from v_rs_rlzy_zwwzyysh a, v_rs_rlzy_qyxx b, v_tyrz_user_dwjbxx c
        where 
         a.RS_RLZY_QYXX_LSH = b."RS_RLZY_QYXX_LSH"
        and b.rs_yhlsh = c.useridcode(+)
    </select>
</mapper>
