<?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="MdePositionCvRelMapper">
    <resultMap type="com.wechat.model.dbEntity.MdePositionCvRel" id="MdePositionCvRel">
        <id column="pos_id" property="posId"/>
        <result column="candidate" property="candidate"/>
        <result column="cv_title" property="cvTitle"/>
        <result column="cv_url" property="cvUrl"/>
        <result column="cv_status" property="cvStatus"/>
        <result column="deliver_date" property="deliverDate"/>
        <result column="matching_degree" property="matchingDegree"/>
        <result column="version_no" property="versionNo"/>
        <result column="remarks" property="remarks"/>
        <result column="del_flag" property="delFlag"/>
        <result column="create_by" property="createBy"/>
        <result column="create_date" property="createDate"/>
        <result column="update_by" property="updateBy"/>
        <result column="update_date" property="updateDate"/>
        <result column="job_name" property="jobName"/>
        <result column="name" property="name"/>
        <result column="foreign_name" property="foreignName"/>
        <result column="publisher" property="publisher"/>
    </resultMap>

    <resultMap type="com.wechat.model.dto.UserInfoResp" id="userInfoRespMap">
        <result column="INTV_ID" property="intvId"/>
        <result column="USER_NAME" property="phoneNo"/>
        <result column="HEAD_PORTRAIT" property="headImage"/>
        <result column="NAME" property="name"/>
        <result column="EMAIL" property="email"/>
        <result column="USER_CODE" property="userCode"/>
        <result column="birthday" property="birthday"/>
        <result column="POS_ID" property="jobId"/>
        <result column="CV_STATUS" property="status"/>
        <result column="CV_URL" property="cvUrl"/>
        <result column="JOB_NAME" property="jobName"/>
        <result column="FIRST_WORK" property="firstWork"/>
        <result column="DELIVER_DATE" property="createDate"/>
        <result column="foreign_name" property="foreignName"/>
        <result column="identity_flag" property="identityFlag"/>
        <collection column="USER_CODE" property="workExperience"
                    ofType="com.wechat.model.responseDto.WorkExperienceResp">
            <result column="positionTitle" property="positionTitle"/>
            <result column="coName" property="coName"/>
            <result column="weEnterDate" property="enterDate"/>
            <result column="weQuitDate" property="quitDate"/>
            <result column="showFlag" property="showFlag"/>
        </collection>
        <collection column="USER_CODE" property="educationExperience"
                    ofType="com.wechat.model.responseDto.EducationExperienceResp">
            <result column="schName" property="schName"/>
            <result column="speciality" property="speciality"/>
            <result column="eeEnterDate" property="enterDate"/>
            <result column="eeQuitDate" property="quitDate"/>
            <result column="educationName" property="educationName"/>
        </collection>
        <collection column="intvId" property="interviewInfo" ofType="com.wechat.model.dto.InterviewListDto">
            <result column="entContacts" property="entContacts"/>
            <result column="entContactInfo" property="entContactInfo"/>
            <result column="type" property="type"/>
            <result column="addrProvince" property="addrProvince"/>
            <result column="addrCity" property="addrCity"/>
            <result column="addrArea" property="addrArea"/>
            <result column="addDetail" property="addDetail"/>
            <result column="intvTime" property="intvTime"/>
            <result column="roomId" property="roomId"/>
            <result column="intvStatus" property="status"/>
            <result column="inviteDate" property="inviteDate"/>
            <result column="inviteUpdateDate" property="updateDate"/>
            <result column="remarks" property="remarks"/>
            <collection column="intvTimeId" property="interviewTime" ofType="com.wechat.model.dto.InterviewTimeListDto">
                <result column="intvTimeId" property="intvTimeId"/>
                <result column="intvTimeFrom" property="intvTimeFrom"/>
                <result column="intvTimeTo" property="intvTimeTo"/>
                <result column="confirmedBy" property="confirmedBy"/>
            </collection>
        </collection>
    </resultMap>


    <resultMap type="com.wechat.model.dto.PositionCvHisDto" id="postCVDetailRespMap">
        <result column="intvId" property="intvId"/>
        <result column="posId" property="posId"/>
        <result column="publisher" property="publisher"/>
        <result column="deliverDate" property="deliverDate"/>
        <result column="cvStatus" property="cvStatus"/>
        <result column="cvTitle" property="cvTitle"/>
        <result column="cvUrl" property="cvUrl"/>
        <result column="jobName" property="jobName"/>
        <result column="payFrom" property="payFrom"/>
        <result column="payTo" property="payTo"/>
        <result column="name" property="name"/>
        <result column="foreignName" property="foreignName"/>
        <result column="headPortrait" property="headPortrait"/>
        <result column="addProvinceName" property="addProvinceName"/>
        <result column="addCityName" property="addCityName"/>
        <result column="workExperience" property="workExperience"/>
        <result column="education" property="education"/>
        <result column="closedFlag" property="closedFlag"/>
        <collection column="intvId" property="interviewInfo" ofType="com.wechat.model.dto.InterviewListDto">
            <result column="entContacts" property="entContacts"/>
            <result column="entContactInfo" property="entContactInfo"/>
            <result column="type" property="type"/>
            <result column="roomId" property="roomId"/>
            <result column="addrProvince" property="addrProvince"/>
            <result column="addrCity" property="addrCity"/>
            <result column="addrArea" property="addrArea"/>
            <result column="addDetail" property="addDetail"/>
            <result column="intvTime" property="intvTime"/>
            <result column="intvStatus" property="status"/>
            <result column="inviteDate" property="inviteDate"/>
            <result column="inviteUpdateDate" property="updateDate"/>
            <result column="remarks" property="remarks"/>
            <collection column="intvTimeId" property="interviewTime" ofType="com.wechat.model.dto.InterviewTimeListDto">
                <result column="intvTimeId" property="intvTimeId"/>
                <result column="intvTimeFrom" property="intvTimeFrom"/>
                <result column="intvTimeTo" property="intvTimeTo"/>
                <result column="confirmedBy" property="confirmedBy"/>
            </collection>
        </collection>
    </resultMap>

    <sql id="mdePositionCvRelColumns">
        a.pos_id AS "posId"
		,a.candidate AS "candidate"
		,a.cv_title AS "cvTitle"
		,a.cv_url AS "cvUrl"
		,a.cv_status AS "cvStatus"
		,a.deliver_date AS "deliverDate"
		,a.matching_degree AS "matchingDegree"
		,a.version_no AS "versionNo"
		,a.remarks AS "remarks"
		,a.del_flag AS "delFlag"
		,a.create_by AS "createBy"
		,a.create_date AS "createDate"
		,a.update_by AS "updateBy"
		,a.update_date AS "updateDate"
		,a.publisher as publisher
    </sql>

    <sql id="mdePositionCvRelJoins">
    </sql>

    <select id="get" resultMap="MdePositionCvRel">
        SELECT
        <include refid="mdePositionCvRelColumns"/>
        FROM mde_position_cv_rel a
        <include refid="mdePositionCvRelJoins"/>
        WHERE a.pos_id = #{posId}
    </select>
    <!-- 是否投递check -->
    <select id="hasPosted" resultMap="MdePositionCvRel">
        SELECT
        <include refid="mdePositionCvRelColumns"/>
        FROM mde_position_cv_rel a
        <include refid="mdePositionCvRelJoins"/>
        WHERE a.pos_id = #{jobId}
        and a.candidate =#{userCode}
        and a.DELIVER_DATE &gt;#{deliverDate}
        and a.del_flag=0 limit 1
    </select>

    <select id="findList" resultMap="MdePositionCvRel">
        SELECT
        <include refid="mdePositionCvRelColumns"/>
        FROM mde_position_cv_rel a
        <include refid="mdePositionCvRelJoins"/>
        <where>
            a.del_flag = #{DEL_FLAG_NORMAL}
        </where>
        <choose>
            <when test="page !=null and page.orderBy != null and page.orderBy != ''">
                ORDER BY #{page.orderBy}
            </when>
            <otherwise>
                ORDER BY a.update_date DESC
            </otherwise>
        </choose>
    </select>

    <insert id="insertUserCvRel">
        INSERT INTO mde_position_cv_rel(pos_id, candidate, PUBLISHER, cv_title, cv_url, cv_status, deliver_date,
                                        matching_degree, version_no, remarks, del_flag,
                                        create_by, create_date, update_by, update_date)
        values (#{posId}, #{candidate}, #{publisher}, #{cvTitle}, #{cvUrl}, #{cvStatus}, #{deliverDate},
                #{matchingDegree}, #{versionNo}, #{remarks}, #{delFlag},
                #{createBy}, #{createDate}, #{updateBy}, #{updateDate})
    </insert>

    <update id="update">
        UPDATE mde_position_cv_rel SET
        <if test="cvTitle!=null">
            cv_title = #{cvTitle},
        </if>
        <if test="cvUrl!=null">
            cv_url = #{cvUrl},
        </if>
        <if test="cvStatus!=null">
            cv_status = #{cvStatus},
        </if>
        <if test="deliverDate!=null">
            deliver_date = #{deliverDate},
        </if>
        <if test="matchingDegree!=null">
            matching_degree = #{matchingDegree},
        </if>
        version_no = version_no +1,
        <if test="remarks!=null">
            remarks = #{remarks},
        </if>
        <if test="updateBy!=null">
            update_by = #{updateBy},
        </if>
        <if test="updateDate!=null">
            update_date = #{updateDate}
        </if>
        WHERE pos_id = #{posId} and candidate = #{candidate} and version_no = #{versionNo}
    </update>

    <update id="updatePositionCvRel">
        UPDATE mde_position_cv_rel
        SET cv_title     = #{cvTitle},
            cv_url       = #{cvUrl},
            cv_status    = #{cvStatus},
            deliver_date = #{deliverDate},
            version_no   = version_no + 1,
            create_date=#{createDate},
            create_by    = #{createBy},
            update_by    = #{updateBy},
            update_date  = #{updateDate}
        WHERE pos_id = #{posId}
          and candidate = #{candidate}
          and del_flag = 0
    </update>

    <update id="delete">
        UPDATE mde_position_cv_rel
        SET version_no = version_no + 1,
            del_flag   = #{DEL_FLAG_DELETE}
        WHERE pos_id = #{posId}
          and candidate = #{candidate}
          and version_no = #{versionNo}
    </update>
    <!-- 简历投递详情 -->
    <select id="getPostCVDetailInfo" resultMap="postCVDetailRespMap">
        SELECT
        a.INTV_ID as intvId,
        a.pos_id as posId,
        a.PUBLISHER as publisher,
        a.DELIVER_DATE as deliverDate,
        (case a.cv_status
        WHEN '6' THEN '1'
        else a.cv_status end ) as cvStatus,
        a.cv_title as cvTitle,
        a.cv_url as cvUrl,
        b.job_name as jobName,
        b.PAY_FROM as payFrom,
        b.PAY_TO as payTo,
        c.NAME as name,
        c.foreign_name as foreignName,
        c.HEAD_PORTRAIT as headPortrait,
        s1.NAME as addProvinceName,
        s2.NAME as addCityName,
        b.work_experience as workExperience,
        b.education as education,
        b.DEL_FLAG as closedFlag
        ,intv.ENT_CONTACTS as entContacts
        ,intv.ENT_CONTACT_INFO as entContactInfo
        ,intv.TYPE as type
        ,intv.ROOM_ID as roomId
        ,intv.ADDR_PROVINCE as addrProvince
        ,intv.ADDR_CITY as addrCity
        ,intv.ADDR_AREA as addrArea
        ,intv.ADD_DETAIL as addDetail
        ,intv.INTV_TIME as intvTime
        ,intv.STATUS as intvStatus
        ,intv.CREATE_DATE as inviteDate
        ,intv.UPDATE_DATE as inviteUpdateDate
        ,intv.REMARKS as remarks
        ,time.INTV_TIME_ID as intvTimeId
        ,time.INTV_TIME_FROM as intvTimeFrom
        ,time.INTV_TIME_TO as intvTimeTo
        ,time.CONFIRMED_BY as confirmedBy
        FROM
        mde_position_cv_rel a
        LEFT JOIN mde_job_advertised b
        ON a.pos_id = b.JOB_ID
<!--        AND b.del_flag = 0-->
        LEFT JOIN mde_interview intv
        ON a.INTV_ID = intv.INTV_ID
        LEFT JOIN mde_intv_time time
        ON intv.INTV_ID = time.INTV_ID
        AND time.STATUS = '0'
        AND time.DEL_FLAG = '0'
        LEFT JOIN mde_user c
        ON b.PUBLISHER = c.USER_CODE
        AND c.del_flag = 0
        INNER JOIN sys_area s1
        ON b.ADD_PROVINCE = s1.`CODE`
        INNER JOIN sys_area s2
        ON b.ADD_CITY = s2.`CODE`
        WHERE
        a.CANDIDATE = #{userCode}
        and a.cv_status in
        <foreach collection="cvStatus" item="item" separator="," open="(" close=")">
            #{item}
        </foreach>
        AND a.del_flag = 0
        ORDER BY
        <!-- 状态[1-待求职者确认；2-待企业确认；3-等待面试；4-已拒绝；5-面试结束；6-储备] -->
        (case intv.STATUS
        when 1 then 1
        when 3 then 2
        when 2 then 3
        else 9 end ) asc,
        intv.UPDATE_DATE desc,
        time.INTV_TIME_FROM asc,
        time.INTV_TIME_TO asc,
        a.create_date desc,
        a.CV_STATUS
    </select>

    <!-- (已不用)投递该公司所有用户简历信息 -->
    <select id="getUserCvStateListInfo" resultMap="userInfoRespMap">
        SELECT
        j.JOB_NAME,
        u.USER_NAME,
        u.foreign_name,
        u.identity_flag ,
        u.birthday ,
        u.USER_CODE ,
        u.HEAD_PORTRAIT ,
        u.FIRST_WORK ,
        u.EMAIL,
        u.`NAME` ,
        p.INTV_ID,
        p.POS_ID,
        p.CV_STATUS,
        p.CV_URL,
        p.DELIVER_DATE,
        <if test='status == "3" or status == "" or status == null'>
            <!-- 邀请面试状态  -->
            intv.ENT_CONTACTS as entContacts,
            intv.ENT_CONTACT_INFO as entContactInfo,
            intv.TYPE as type,
            intv.ADDR_PROVINCE as addrProvince,
            intv.ADDR_CITY as addrCity,
            intv.ADDR_AREA as addrArea,
            intv.ADD_DETAIL as addDetail,
            intv.INTV_TIME as intvTime,
            intv.ROOM_ID as roomId,
            intv.STATUS as intvStatus,
            intv.CREATE_DATE as inviteDate,
            intv.UPDATE_DATE as inviteUpdateDate,
            intv.REMARKS as remarks,
            time.INTV_TIME_ID as intvTimeId,
            time.INTV_TIME_FROM as intvTimeFrom,
            time.INTV_TIME_TO as intvTimeTo,
            time.CONFIRMED_BY as confirmedBy,
        </if>
        we.co_name AS "coName",
        we.position_title AS "positionTitle",
        we.enter_date AS "weEnterDate",
        we.quit_date AS "weQuitDate",
        we.show_flag AS "showFlag"
        <choose>
            <when test="lang == 'en'.toString()">
                ,s.en as educationName
            </when>
            <otherwise>
                ,s.label as educationName
            </otherwise>
        </choose>
        FROM
        mde_position_cv_rel p
        INNER JOIN mde_job_advertised j
        on j.JOB_ID = p.POS_ID
        INNER JOIN mde_user u
        on u.USER_CODE = p.CANDIDATE
        <if test='status == "3" or status == "" or status == null'>
            <!-- 邀请面试状态  -->
            LEFT JOIN mde_interview intv
            ON intv.INTV_ID = p.INTV_ID
            AND intv.DEL_FLAG = '0'
            LEFT JOIN mde_intv_time time
            ON intv.INTV_ID = time.INTV_ID
            AND time.STATUS = '0'
            AND time.DEL_FLAG = '0'
            <if test='model == "1"'>
                AND time.sort = 1
            </if>
        </if>
        left join mde_work_experience we
        on u.USER_CODE = we.USER_CODE
        AND we.SHOW_FLAG = '0'
        AND we.DEL_FLAG = 0
        LEFT JOIN (
        SELECT
        min( edu.EDUCATION ) AS EDUCATION,
        edu.USER_CODE
        FROM
        MDE_EDUCATION_EXPERIENCE edu
        INNER JOIN mde_position_cv_rel p ON edu.USER_CODE = p.CANDIDATE
        AND edu.DEL_FLAG = 0
        where p.PUBLISHER = #{publisher}
        GROUP BY
        edu.USER_CODE
        ) edumin ON edumin.USER_CODE = p.CANDIDATE

        left JOIN sys_dict s
        on edumin.EDUCATION = s.`value`
        AND s.type = 'education'
        WHERE
        p.PUBLISHER = #{publisher}
        <if test="status != null and status != ''">
            AND p.CV_STATUS =#{status}
        </if>
        <if test="jobId != null and jobId != ''">
            AND j.JOB_ID =#{jobId}
        </if>
        AND p.DEL_FLAG = 0
        AND u.DEL_FLAG = 0
        AND j.DEL_FLAG = 0
        GROUP BY
        p.CANDIDATE
        ,p.POS_ID
        <if test='status == "3" or status == "" or status == null'>
            ,time.INTV_TIME_ID
        </if>
        order by
        <if test='status == "3"'>
            <!-- 状态[1-待求职者确认；2-待企业确认；3-等待面试；4-已拒绝；5-面试结束] -->
            (case intv.STATUS
            when 2 then 1
            when 3 then 2
            when 1 then 3
            else 9 end ) asc,
            intv.UPDATE_DATE desc,
        </if>
        p.DELIVER_DATE DESC,
        p.UPDATE_DATE DESC
    </select>

    <!-- 查询过滤已收简历 -->
    <select id="filterUserCvCount"  resultType="java.lang.Integer">
        SELECT
        count(1)
        FROM (
            SELECT
            count(1)
                FROM
                mde_position_cv_rel p
                INNER JOIN mde_job_advertised j
                on j.JOB_ID = p.POS_ID
                INNER JOIN mde_user u
                on u.USER_CODE = p.CANDIDATE
                <if test='status == "3" or status == "" or status == null'>
                    <!-- 邀请面试状态  -->
                    LEFT JOIN mde_interview intv
                    ON intv.INTV_ID = p.INTV_ID
                    AND intv.DEL_FLAG = '0'
                    LEFT JOIN mde_intv_time time
                    ON intv.INTV_ID = time.INTV_ID
                    AND time.STATUS = '0'
                    AND time.DEL_FLAG = '0'
                    <if test='model == "1"'>
                        AND time.sort = 1
                    </if>
                </if>
                left join mde_work_experience we
                on u.USER_CODE = we.USER_CODE
                AND we.SHOW_FLAG = '0'
                AND we.DEL_FLAG = 0
                LEFT JOIN (
                SELECT
                min( edu.EDUCATION ) AS EDUCATION,
                edu.USER_CODE
                FROM
                MDE_EDUCATION_EXPERIENCE edu
                INNER JOIN mde_position_cv_rel p ON edu.USER_CODE = p.CANDIDATE
                AND edu.DEL_FLAG = 0
                where p.PUBLISHER = #{publisher}
                GROUP BY
                edu.USER_CODE
                ) edumin ON edumin.USER_CODE = p.CANDIDATE
                left JOIN sys_dict s
                on edumin.EDUCATION = s.`value`
                AND s.type = 'education'
                WHERE
                p.PUBLISHER = #{publisher}
                <if test="status != null and status != ''">
                    AND p.CV_STATUS =#{status}
                </if>
                <if test="positionIdList != null">
                    AND j.POSITION_ID IN
                    <foreach collection="positionIdList" item="id" separator="," open="(" close=")">
                        #{id}
                    </foreach>
                </if>
                AND p.DEL_FLAG = 0
                AND u.DEL_FLAG = 0
                AND j.DEL_FLAG = 0
                GROUP BY
                p.CANDIDATE
                ,p.POS_ID
                <if test='status == "3" or status == "" or status == null'>
                    ,time.INTV_TIME_ID
                </if>
        ) as count
    </select>

    <!-- 查询过滤已收简历 -->
    <select id="filterUserCv" resultMap="userInfoRespMap">
        SELECT
        j.JOB_NAME,
        u.USER_NAME,
        u.foreign_name,
        u.identity_flag ,
        u.birthday ,
        u.USER_CODE ,
        u.HEAD_PORTRAIT ,
        u.FIRST_WORK ,
        u.EMAIL,
        u.`NAME` ,
        p.INTV_ID,
        p.POS_ID,
        p.CV_STATUS,
        p.CV_URL,
        p.DELIVER_DATE,
        <if test='status == "3" or status == "" or status == null'>
            <!-- 邀请面试状态  -->
            intv.ENT_CONTACTS as entContacts,
            intv.ENT_CONTACT_INFO as entContactInfo,
            intv.TYPE as type,
            intv.ADDR_PROVINCE as addrProvince,
            intv.ADDR_CITY as addrCity,
            intv.ADDR_AREA as addrArea,
            intv.ADD_DETAIL as addDetail,
            intv.INTV_TIME as intvTime,
            intv.ROOM_ID as roomId,
            intv.STATUS as intvStatus,
            intv.CREATE_DATE as inviteDate,
            intv.UPDATE_DATE as inviteUpdateDate,
            intv.REMARKS as remarks,
            time.INTV_TIME_ID as intvTimeId,
            time.INTV_TIME_FROM as intvTimeFrom,
            time.INTV_TIME_TO as intvTimeTo,
            time.CONFIRMED_BY as confirmedBy,
        </if>
        we.co_name AS "coName",
        we.position_title AS "positionTitle",
        we.enter_date AS "weEnterDate",
        we.quit_date AS "weQuitDate",
        we.show_flag AS "showFlag"
        <choose>
            <when test="lang == 'en'.toString()">
                ,s.en as educationName
            </when>
            <otherwise>
                ,s.label as educationName
            </otherwise>
        </choose>
        FROM
        mde_position_cv_rel p
        INNER JOIN mde_job_advertised j
        on j.JOB_ID = p.POS_ID
        INNER JOIN mde_user u
        on u.USER_CODE = p.CANDIDATE
        <if test='status == "3" or status == "" or status == null'>
            <!-- 邀请面试状态  -->
            LEFT JOIN mde_interview intv
            ON intv.INTV_ID = p.INTV_ID
            AND intv.DEL_FLAG = '0'
            LEFT JOIN mde_intv_time time
            ON intv.INTV_ID = time.INTV_ID
            AND time.STATUS = '0'
            AND time.DEL_FLAG = '0'
            <if test='model == "1"'>
                AND time.sort = 1
            </if>
        </if>
        left join mde_work_experience we
        on u.USER_CODE = we.USER_CODE
        AND we.SHOW_FLAG = '0'
        AND we.DEL_FLAG = 0
        LEFT JOIN (
        SELECT
        min( edu.EDUCATION ) AS EDUCATION,
        edu.USER_CODE
        FROM
        MDE_EDUCATION_EXPERIENCE edu
        INNER JOIN mde_position_cv_rel p ON edu.USER_CODE = p.CANDIDATE
        AND edu.DEL_FLAG = 0
        where p.PUBLISHER = #{publisher}
        GROUP BY
        edu.USER_CODE
        ) edumin ON edumin.USER_CODE = p.CANDIDATE
        left JOIN sys_dict s
        on edumin.EDUCATION = s.`value`
        AND s.type = 'education'
        WHERE
        p.PUBLISHER = #{publisher}
        <if test="status != null and status != ''">
            AND p.CV_STATUS =#{status}
        </if>
        <if test="positionIdList != null">
            AND j.POSITION_ID IN
            <foreach collection="positionIdList" item="id" separator="," open="(" close=")">
                  #{id}
            </foreach>
        </if>
        AND p.DEL_FLAG = 0
        AND u.DEL_FLAG = 0
        AND j.DEL_FLAG = 0
        GROUP BY
        p.CANDIDATE
        ,p.POS_ID
        <if test='status == "3" or status == "" or status == null'>
            ,time.INTV_TIME_ID
        </if>
        order by
        j.JOB_STATUS asc,
        <if test='status == "3"'>
            <!-- 状态[1-待求职者确认；2-待企业确认；3-等待面试；4-已拒绝；5-面试结束] -->
            (case intv.STATUS
            when 2 then 1
            when 3 then 2
            when 1 then 3
            else 9 end ) asc,
            intv.UPDATE_DATE desc,
        </if>
        p.DELIVER_DATE DESC,
        p.UPDATE_DATE DESC
    </select>

    <!-- 已发简历关键词搜索统计 -->
    <select id="searchUserCvByKeyWordCount" resultType="java.lang.Integer">
        SELECT
        count(1)
        FROM (
            SELECT
            count(1)
            FROM
            mde_position_cv_rel p
            INNER JOIN mde_job_advertised j
            on j.JOB_ID = p.POS_ID
            INNER JOIN mde_user u
            on u.USER_CODE = p.CANDIDATE
            <!-- 邀请面试状态  -->    LEFT JOIN mde_interview intv
            ON intv.INTV_ID =
            p.INTV_ID
            AND
            intv.DEL_FLAG = '0'
            LEFT JOIN mde_intv_time time
            ON intv.INTV_ID = time.INTV_ID
            AND time.STATUS = '0'
            AND time.DEL_FLAG = '0'
            <if test='model == "1"'>
                    AND time.sort = 1
                </if>

                left join mde_work_experience we
                on u.USER_CODE = we.USER_CODE
                AND we.SHOW_FLAG = '0'
                AND we.DEL_FLAG = 0
                LEFT JOIN (
                SELECT
                min( edu.EDUCATION ) AS EDUCATION,
                edu.USER_CODE
                FROM
                MDE_EDUCATION_EXPERIENCE edu
                INNER JOIN mde_position_cv_rel p ON edu.USER_CODE = p.CANDIDATE
                AND edu.DEL_FLAG = 0
                where p.PUBLISHER = #{publisher}
                GROUP BY
                edu.USER_CODE
                ) edumin ON edumin.USER_CODE = p.CANDIDATE

                left JOIN sys_dict s
                on edumin.EDUCATION = s.`value`
                AND s.type = 'education'
                WHERE
                p.PUBLISHER = #{publisher}
                AND p.DEL_FLAG = 0
                AND u.DEL_FLAG = 0
                AND j.DEL_FLAG = 0
                <if test="keyWord !=null">
                    AND
                    (
                    MATCH(j.JOB_NAME) AGAINST(#{keyWord} IN BOOLEAN MODE)
                    or
                    MATCH(u.NAME,u.FOREIGN_NAME,u.USER_NAME) AGAINST(#{keyWord} IN BOOLEAN MODE)
                    or
                    s.label like concat('%',#{keyWord},'%')
                    )
                </if>
                GROUP BY
                p.CANDIDATE,
                p.POS_ID,
                time.INTV_TIME_ID
        ) as count
    </select>

    <!-- 已发简历关键词搜索 -->
    <select id="searchUserCvByKeyWord" resultMap="userInfoRespMap">
        SELECT
        j.JOB_NAME,
        u.USER_NAME,
        u.foreign_name,
        u.identity_flag ,
        u.birthday ,
        u.USER_CODE ,
        u.HEAD_PORTRAIT ,
        u.FIRST_WORK ,
        u.EMAIL,
        u.`NAME` ,
        p.INTV_ID,
        p.POS_ID,
        p.CV_STATUS,
        p.CV_URL,
        p.DELIVER_DATE,

        <!-- 邀请面试状态  -->
        intv.ENT_CONTACTS as entContacts,
        intv.ENT_CONTACT_INFO as entContactInfo,
        intv.TYPE as type,
        intv.ADDR_PROVINCE as addrProvince,
        intv.ADDR_CITY as addrCity,
        intv.ADDR_AREA as addrArea,
        intv.ADD_DETAIL as addDetail,
        intv.INTV_TIME as intvTime,
        intv.ROOM_ID as roomId,
        intv.STATUS as intvStatus,
        intv.CREATE_DATE as inviteDate,
        intv.UPDATE_DATE as inviteUpdateDate,
        intv.REMARKS as remarks,
        time.INTV_TIME_ID as intvTimeId,
        time.INTV_TIME_FROM as intvTimeFrom,
        time.INTV_TIME_TO as intvTimeTo,
        time.CONFIRMED_BY as confirmedBy,

        we.co_name AS "coName",
        we.position_title AS "positionTitle",
        we.enter_date AS "weEnterDate",
        we.quit_date AS "weQuitDate",
        we.show_flag AS "showFlag"
        <choose>
            <when test="lang == 'en'.toString()">
                ,s.en as educationName
            </when>
            <otherwise>
                ,s.label as educationName
            </otherwise>
        </choose>
        FROM
        mde_position_cv_rel p
        INNER JOIN mde_job_advertised j
        on j.JOB_ID = p.POS_ID
        INNER JOIN mde_user u
        on u.USER_CODE = p.CANDIDATE

        <!-- 邀请面试状态  -->
        LEFT JOIN mde_interview intv
        ON intv.INTV_ID = p.INTV_ID
        AND intv.DEL_FLAG = '0'
        LEFT JOIN mde_intv_time time
        ON intv.INTV_ID = time.INTV_ID
        AND time.STATUS = '0'
        AND time.DEL_FLAG = '0'
        <if test='model == "1"'>
            AND time.sort = 1
        </if>

        left join mde_work_experience we
        on u.USER_CODE = we.USER_CODE
        AND we.SHOW_FLAG = '0'
        AND we.DEL_FLAG = 0
        LEFT JOIN (
        SELECT
        min( edu.EDUCATION ) AS EDUCATION,
        edu.USER_CODE
        FROM
        MDE_EDUCATION_EXPERIENCE edu
        INNER JOIN mde_position_cv_rel p ON edu.USER_CODE = p.CANDIDATE
        AND edu.DEL_FLAG = 0
        where p.PUBLISHER = #{publisher}
        GROUP BY
        edu.USER_CODE
        ) edumin ON edumin.USER_CODE = p.CANDIDATE

        left JOIN sys_dict s
        on edumin.EDUCATION = s.`value`
        AND s.type = 'education'
        WHERE
        p.PUBLISHER = #{publisher}
        AND p.DEL_FLAG = 0
        AND u.DEL_FLAG = 0
        AND j.DEL_FLAG = 0
        <if test="keyWord !=null">
            AND
            (
                MATCH(j.JOB_NAME) AGAINST(#{keyWord} IN BOOLEAN MODE)
                or
                MATCH(u.NAME,u.FOREIGN_NAME,u.USER_NAME) AGAINST(#{keyWord} IN BOOLEAN MODE)
                or
                s.label like concat('%',#{keyWord},'%')
            )
        </if>
        GROUP BY
        p.CANDIDATE,
        p.POS_ID,
        time.INTV_TIME_ID
        order by
        p.DELIVER_DATE DESC,
        p.UPDATE_DATE DESC
    </select>
    <!-- 投递该公司所有用户简历数量  -->
    <!-- 	<select id="getUserCvStateListInfoCount" resultType="java.lang.Integer">
                SELECT
                    count(1)
                FROM
                    mde_position_cv_rel p
                    INNER JOIN mde_job_advertised j
                            on j.JOB_ID = p.POS_ID
                    INNER JOIN mde_user u
                            on u.USER_CODE = p.CANDIDATE
                WHERE
                        j.PUBLISHER = #{userCode}
                    <if test="status != null and status != ''">
                    AND p.CV_STATUS =#{status}
                    </if>
                    <if test="jobId != null and jobId != ''">
                    AND j.JOB_ID =#{jobId}
                    </if>
                    AND p.DEL_FLAG = 0
                    AND u.DEL_FLAG = 0
                    AND j.DEL_FLAG = 0
        </select> -->

    <!-- 更新简历状态 -->
    <update id="updateUserCvStatus" parameterType="java.util.Map">
        update mde_position_cv_rel
        set CV_STATUS   = #{status},
            REMARKS     =ifnull(#{remark}, ''),
            UPDATE_DATE = NOW(),
            version_no  = version_no + 1
        where DEL_FLAG = 0
          and POS_ID = #{jobId}
          and CANDIDATE = #{userCode}
    </update>


    <!-- 更新简历状态 -->
    <update id="updateCvAlready" parameterType="java.util.Map">
        update mde_position_cv_rel
        set CV_STATUS   = '1',
            UPDATE_DATE = NOW(),
            version_no  = version_no + 1
        where DEL_FLAG = 0
          and POS_ID = #{jobId}
          and CANDIDATE = #{userCode}
          and CV_STATUS = '0'
    </update>

    <!-- 更新全部简历状态 -->
    <update id="updateCvsAlready" parameterType="java.util.Map">
        UPDATE mde_position_cv_rel rel,
            mde_job_advertised jad
        SET rel.CV_STATUS   = '1',
            rel.UPDATE_DATE = NOW(),
            rel.version_no  = rel.version_no + 1
        WHERE rel.DEL_FLAG = 0
          AND jad.JOB_ID = rel.POS_ID
          AND jad.PUBLISHER = #{userCode}
          AND rel.CV_STATUS = '0'
    </update>

    <select id="findPositionCvRelByJobIdAndUserCode" parameterType="java.util.Map" resultMap="MdePositionCvRel">
        SELECT
        <include refid="mdePositionCvRelColumns"/>
        FROM
        mde_position_cv_rel a
        WHERE
        a.DEL_FLAG = 0
        AND a.POS_ID = #{jobId}
        AND a.CANDIDATE = #{userCode}
        ORDER BY a.DELIVER_DATE DESC
    </select>
    <select id="getInfoByIdAndUserCode" resultMap="MdePositionCvRel">
        SELECT
        <include refid="mdePositionCvRelColumns"/>
        FROM
        mde_position_cv_rel a
        WHERE
        a.DEL_FLAG = 0
        AND a.POS_ID = #{posId}
        AND a.CANDIDATE = #{candidate}
        limit 1
    </select>
    <delete id="deleteCv">
        delete
        from mde_position_cv_rel
        WHERE POS_ID = #{posId}
          and CANDIDATE = #{candidate}
    </delete>

    <select id="countCvsByDateStaus" resultType="java.lang.Integer">
        SELECT
        count( rel.CANDIDATE ) as num
        FROM
        mde_position_cv_rel rel
        WHERE
        rel.PUBLISHER = #{userCode}
        <if test="cvStatus != null and cvStatus != '' ">
            AND rel.CV_STATUS = #{cvStatus}
        </if>
        <![CDATA[
   			AND rel.DELIVER_DATE >= #{from} 
			AND rel.DELIVER_DATE <= #{to}
		]]>
        <if test="jobId != null and jobId != 0 ">
            AND rel.POS_ID = #{jobId}
        </if>
    </select>

    <select id="countEducationByDate" resultType="com.wechat.model.responseDto.LVResp">
        SELECT
        d.label as label,
        count( education ) AS value
        FROM
        (
        SELECT
        rel.CANDIDATE,
        rel.POS_ID,
        IFNULL( MIN( edu.EDUCATION ), 10 ) AS education
        FROM
        mde_position_cv_rel rel
        LEFT JOIN mde_education_experience edu ON rel.CANDIDATE = edu.user_code
        WHERE
        rel.PUBLISHER = #{userCode}
        <![CDATA[
	   			AND rel.DELIVER_DATE >= #{from} 
				AND rel.DELIVER_DATE <= #{to}
				]]>
        <if test="jobId != null and jobId != 0 ">
            AND rel.POS_ID = #{jobId}
        </if>
        GROUP BY
        rel.CANDIDATE,
        rel.POS_ID
        ) info
        INNER JOIN sys_dict d ON info.education = d.VALUE
        WHERE
        d.type = 'education'
        GROUP BY
        info.education
        ORDER BY
        education+0
    </select>

    <select id="getReportsFormByDate" resultType="com.wechat.model.dto.JobRepFormDtlDto">
        SELECT jobpv.jobName,
               jobpv.pv,
               count(rel.CANDIDATE)            as cvNums,
               IFNULL(download.downloadNum, 0) AS downloadNum
        FROM (SELECT job.JOB_NAME     AS jobName,
                     a.JOB_ID         AS jobId,
                     SUM(a.PAGE_VIEW) AS pv
              FROM mde_job_pv_uv a
                       INNER JOIN mde_job_advertised job ON job.JOB_ID = a.JOB_ID
              WHERE
			<![CDATA[
				job.PUBLISHER = #{userCode}
                AND a.WRIT_DATE >= #{from}
                AND a.WRIT_DATE <= #{to}
			]]> 
			GROUP BY a.JOB_ID) jobpv
                 LEFT JOIN
             mde_position_cv_rel rel
             ON jobpv.jobId = rel.POS_ID
                 LEFT JOIN
             (SELECT down.POS_ID           as posId,
                     count(down.CANDIDATE) as downloadNum
              FROM mde_position_cv_rel down
              WHERE
				 <![CDATA[
				  down.DELIVER_DATE >= #{from}
                AND down.DELIVER_DATE <= #{to}
			  ]]> 
			  AND down.PUBLISHER = #{userCode}
                AND down.CV_STATUS = '2'
              GROUP BY down.POS_ID) as download
             ON jobpv.jobId = download.posId
        WHERE
		  <![CDATA[
			  rel.DELIVER_DATE >= #{from}
          AND rel.DELIVER_DATE <= #{to}
		  ]]> 
		GROUP BY jobpv.jobId
        ORDER BY jobpv.pv desc
    </select>
</mapper>