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

    <select id="getGrqzxxshList" parameterType="com.zjrs.zwnw.rlzysc.dto.GrqzQueryDTO" resultType="HashMap">
        <!-- 提交审核且尚未审核的简历数据的才显示到该列表  0未提交审核，1提交审核-->
        select a.rs_rlzy_jlxx_lsh as "rsrlzyjlxxlsh",
        a.jlmc as "jlmc",
        a.xjzdxzqh as "xjzdxzqh",
        f_jbxxgl_getAllAreaNameLine(a.xjzdxzqh) as "xjzdxzqhname",
        a.aac003 as "xm",
        a.aac058 as "zjlx",
        a.aac147 as "sfzhm",
        a.sjhm as "sjhm",
        c.qwzw as "qwzw",
        c.qwxz as "qwxz"
        from v_rs_rlzy_jlxx a, v_rs_rlzy_qzyx_dyb b, v_rs_rlzy_qzyx c,v_tyrz_user_grjbxx d
        where a.rs_rlzy_jlxx_lsh = b.rs_rlzy_jlxx_lsh(+)
        and b.rs_rlzy_qzyx_lsh = c.rs_rlzy_qzyx_lsh(+)
        and a.rs_yhlsh = d.useridcode(+)        
        <if test="dto.zjlx != null and dto.zjlx != ''">
            and d.aac058 = #{dto.zjlx}
        </if>
        order by a.ts_xgsj DESC
    </select>

    <select id="getGrqzxxshXxAndJbxx" parameterType="com.zjrs.zwnw.rlzysc.dto.ZjQueryDTO" resultType="HashMap">
        select a.rs_rlzy_jlxx_lsh  as "rsrlzyjlxxlsh" ,
        a.jlmc as "jlmc",
        d.ztid as "ztid",
        d.aac003 as "xm",
        d.aac004 as "tsxb",
        d.aac058 as "zjlx",
        d.aac147 as "sfzhm",
        d.aac006 as "csrq",
        a.nl as "nl",
        d.aac005  as "tsmz",
        d.aac009  as "tshj",
        f_jbxxgl_getAllAreaName(d.aac300) as "hkszd",
        a.ts_hyzk  as "tshyzk",
        a.ts_zzmm  as "tszzmm",
        a.ts_byfl  as "tsbyfl",
        a.ts_xl  as "tsxl",
        a.ts_qzlx  as "tsqzlx",
        a.cjgzsj as "cjgzsj",
        a.sjhm as "sjhm",
        a.yx as "yx",
        a.ahtc as "ahtc",
        a.xjzd as "xjzd",
        a.jlmc as "jlmc",
        a.tx_tycc_lsh as "txtycclsh",
        a.sp_tycc_lsh as "sptycclsh",
        a.rs_yhlsh as "rsyhlsh",
        c.mqzk as "mqzk",
        c.qwxz as "qwxz",
        c.gzdd as "gzdd",
        c.gzxz as "gzxz",
        c.qwhy as "qwhy",
        c.qwzw as "qwzw",
        c.gznx as "gznx",
        a.gkcd as "gkcd",
        c.wyyz as "wyyz",
        c.wysp as "wysp",
        c.zwjs as "zwjs",
        a.ts_qzlx as "qzzlx",
        a.shjg as "shjg",
        a.ts_rylb as "tsrylb",
        a.zyxx as "zyxx"
        from v_rs_rlzy_jlxx a, v_rs_rlzy_qzyx_dyb b, v_rs_rlzy_qzyx c,v_tyrz_user_grjbxx d
        where a.rs_rlzy_jlxx_lsh = b.rs_rlzy_jlxx_lsh(+)
        and b.rs_rlzy_qzyx_lsh = c.rs_rlzy_qzyx_lsh(+)
        and a.rs_yhlsh = d.useridcode(+)
        and a.rs_rlzy_jlxx_lsh = #{dto.zjlsh}
    </select>

    <select id="getGrqzxxshXx" parameterType="com.zjrs.zwnw.rlzysc.dto.ZjQueryDTO" resultType="HashMap">
        select a.rs_rlzy_jlxx_lsh  as "rsrlzyjlxxlsh" ,
        a.jlmc as "jlmc",
        a.aac003 as "xm",
        a.aac004 as "tsxb",
        a.aac058 as "zjlx",
        a.aac147 as "sfzhm",
        a.aac006 as "csrq",
        a.nl as "nl",
        a.aac005  as "tsmz",
        a.aac009  as "tshj",
        f_jbxxgl_getAllAreaName(a.aac300) as "hkszd",
        a.ts_hyzk  as "tshyzk",
        a.ts_zzmm  as "tszzmm",
        a.ts_byfl  as "tsbyfl",
        a.ts_xl  as "tsxl",
        a.ts_qzlx  as "tsqzlx",
        a.cjgzsj as "cjgzsj",
        a.sjhm as "sjhm",
        a.yx as "yx",
        a.ahtc as "ahtc",
        a.xjzd as "xjzd",
        a.jlmc as "jlmc",
        a.tx_tycc_lsh as "txtycclsh",
        a.sp_tycc_lsh as "sptycclsh",
        c.mqzk as "mqzk",
        c.qwxz as "qwxz",
        c.gzdd as "gzdd",
        c.gzxz as "gzxz",
        c.qwhy as "qwhy",
        c.qwzw as "qwzw",
        a.gznx as "gznx",
        a.gkcd as "gkcd",
        c.wyyz as "wyyz",
        c.wysp as "wysp",
        c.zwjs as "zwjs",
        a.ts_qzlx as "qzzlx",
        a.shjg as "shjg",
        a.ts_rylb as "tsrylb",
        a.zyxx as "zyxx"
        from v_rs_rlzy_jlxx a, v_rs_rlzy_qzyx_dyb b, v_rs_rlzy_qzyx c,v_tyrz_user_grjbxx d
        where a.rs_rlzy_jlxx_lsh = b.rs_rlzy_jlxx_lsh(+)
        and b.rs_rlzy_qzyx_lsh = c.rs_rlzy_qzyx_lsh(+)
        and a.rs_yhlsh = d.useridcode(+)
        and a.rs_rlzy_jlxx_lsh = #{dto.zjlsh}
    </select>

    <select id="getGrqzxxglList" parameterType="com.zjrs.zwnw.rlzysc.dto.GrqzQueryDTO" resultType="HashMap">
        <!-- 列表数据用来冻结或注销，只能操作审核通过的数据(审核不通过的数据重新提交审核，需要将上次审核结果清空)-->
        select a.rs_rlzy_jlxx_lsh as "rsrlzyjlxxlsh",
        a.jlmc as "jlmc",
        a.ts_xl as "xl",
        a.xjzdxzqh as "xjzdxzqh",
        f_jbxxgl_getAllAreaName(a.xjzdxzqh) as "xjzdxzqhname",
        a.sjhm as "sjhm",
        c.qwzw as "qwzw",
        c.qwxz as "qwxz",
        d.aac003 as "xm",
        d.aac147 as "sfzhm",
        d.aac058 as "zjlx",
        a.rs_rg_jbxx_jlzt  as "zt",
        (select user_name from s_sg_user d where a.jlshr = d.id) as "jlshr",
        a.shsj as "shsj",
        (case when a.fbzt = '1' then a.ts_xgsj else '' end) as "fbsj"
        from v_rs_rlzy_jlxx a, v_rs_rlzy_qzyx_dyb b, v_rs_rlzy_qzyx c,v_tyrz_user_grjbxx d
        where a.rs_rlzy_jlxx_lsh = b.rs_rlzy_jlxx_lsh(+)
        and b.rs_rlzy_qzyx_lsh = c.rs_rlzy_qzyx_lsh(+)
        and a.rs_yhlsh = d.useridcode(+)
        <if test="dto.zjlx != null and dto.zjlx != ''">
            and d.aac058 = #{dto.zjlx}
        </if>
        <if test="dto.sfzhm != null and dto.sfzhm != ''">
            and d.aac147 = #{dto.sfzhm}
        </if>
        <if test="dto.qwzw != null and dto.qwzw != ''">
            and c.qwzw = #{dto.qwzw}
        </if>
        order by a.ts_xgsj DESC
    </select>

    <select id="getExportGrqzxxglList" parameterType="com.zjrs.zwnw.rlzysc.dto.GrqzQueryDTO" resultType="com.zjrs.zwnw.rlzysc.dto.GrqzxxglExportDTO">
        <!-- 列表数据用来冻结或注销，只能操作审核通过的数据(审核不通过的数据重新提交审核，需要将上次审核结果清空)-->
        select rownum as "rownum", t.* from (
        select
        a.rs_rlzy_jlxx_lsh as "rsrlzyjlxxlsh",
        a.jlmc as "jlmc",
        (select aaa103 from v_ggfw_dict where AAA100 = 'JBXX_AAC011' and AAA102 = a.ts_xl) as "xl",
        a.xjzdxzqh as "xjzdxzqh",
        f_jbxxgl_getAllAreaName(a.xjzdxzqh) as "xjzdxzqhname",
        a.sjhm as "sjhm",
        (select aaa103 from v_ggfw_dict where AAA100 = 'RS_RLZY_ZYXX' and AAA102 = c.qwzw) as "qwzw",
        (select aaa103 from v_ggfw_dict where AAA100 = 'RS_RLZY_XZFW' and AAA102 = c.qwxz) as "qwxz",
        d.aac003 as "xm",
        d.aac147 as "sfzhm",
        d.aac058 as "zjlx",
        (select aaa103 from v_ggfw_dict where AAA100 = 'RS_RLZY_HYZT' and AAA102 = a.rs_rg_jbxx_jlzt)  as "zt",
        (select user_name from s_sg_user d where a.jlshr = d.id) as "jlshr",
        a.shsj as "shsj",
        (case when a.fbzt = '1' then a.ts_xgsj else '' end) as "fbsj"
        from v_rs_rlzy_jlxx a, v_rs_rlzy_qzyx_dyb b, v_rs_rlzy_qzyx c,v_tyrz_user_grjbxx d
        where a.rs_rlzy_jlxx_lsh = b.rs_rlzy_jlxx_lsh(+)
        and b.rs_rlzy_qzyx_lsh = c.rs_rlzy_qzyx_lsh(+)
        and a.rs_yhlsh = d.useridcode(+)
        <if test="dto.zjlx != null and dto.zjlx != ''">
            and d.aac058 = #{dto.zjlx}
        </if>
        order by a.ts_xgsj DESC) t
    </select>

    <select id="getQzxxQzppList" parameterType="com.zjrs.zwnw.rlzysc.dto.GrqzQueryDTO" resultType="HashMap">
        <!-- 求职推荐，只针对审核通过，且发布简历的数据-->
        select a.rs_rlzy_jlxx_lsh  as "rsrlzyjlxxlsh",
        d.aac003 as "xm",
        d.aac147 as "sfzhm",
        a.jlmc as "jlmc",
        a.sjhm as "sjhm",
        c.qwzw as "qwzw",
        c.qwxz as "qwxz",
        a.rs_rg_jbxx_jlzt  as "zt",
        a.ts_xl as "xl"
        from v_rs_rlzy_jlxx a, v_rs_rlzy_qzyx_dyb b, v_rs_rlzy_qzyx c,v_tyrz_user_grjbxx d
        where a.rs_rlzy_jlxx_lsh = b.rs_rlzy_jlxx_lsh(+)
        and b.rs_rlzy_qzyx_lsh = c.rs_rlzy_qzyx_lsh(+)
        and a.rs_yhlsh = d.useridcode(+)
        order by a.ts_xgsj DESC
    </select>

    <select id="queryJbxx" parameterType="map" resultType="com.zjrs.zwnw.jbxxgl.dto.GrxxDTO">
        select
        c.aac003 as "aac003",
        c.aac006 as "aac006",
        c.aac058 as "aac058",
        c.aac147 as "aac147",
        c.aac004 as "aac004",
        c.aac005 as "aac005",
        c.aac009 as "aac009",
        c.aac300 as "aac300"
        from v_rs_rlzy_jlxx c
        where c.rs_rlzy_jlxx_lsh = #{map.rsrlzyjlxxlsh}
    </select>

</mapper>
