<?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.zhongzheng.modules.recruit.mapper.RecruitInterviewMapper">

    <resultMap type="com.zhongzheng.modules.recruit.domain.RecruitInterview" id="RecruitInterviewResult">
        <result property="interviewId" column="interview_id"/>
        <result property="year" column="year"/>
        <result property="month" column="month"/>
        <result property="day" column="day"/>
        <result property="timingStart" column="timing_start"/>
        <result property="timingEnd" column="timing_end"/>
        <result property="needsId" column="needs_id"/>
        <result property="resumeJson" column="resume_json"/>
        <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>
        <result property="status" column="status"/>
        <result property="tenantId" column="tenant_id"/>
        <result property="userId" column="user_id"/>
        <result property="attachUrl" column="attach_url"/>
        <result property="refuse" column="refuse"/>
        <result property="resumeTime" column="resume_time"/>
        <result property="linkMan" column="link_man"/>
        <result property="linkTel" column="link_tel"/>
        <result property="companyName" column="company_name"/>
        <result property="emall" column="emall"/>
        <result property="startYear" column="start_year"/>
        <result property="userBirth" column="user_birth"/>
        <result property="eduLevel" column="edu_level"/>
        <result property="completeness" column="completeness"/>
    </resultMap>

    <resultMap type="com.zhongzheng.modules.recruit.vo.RecruitInterviewVo" id="RecruitInterviewVoResult">
        <result property="interviewId" column="interview_id"/>
        <result property="year" column="year"/>
        <result property="month" column="month"/>
        <result property="day" column="day"/>
        <result property="timingStart" column="timing_start"/>
        <result property="timingEnd" column="timing_end"/>
        <result property="incomeStart" column="income_start"/>
        <result property="incomeEnd" column="income_end"/>
        <result property="incomeType" column="income_type"/>
        <result property="needsId" column="needs_id"/>
        <result property="resumeJson" column="resume_json"/>
        <result property="status" column="status"/>
        <result property="tenantId" column="tenant_id"/>
        <result property="userId" column="user_id"/>
        <result property="attachUrl" column="attach_url"/>
        <result property="refuse" column="refuse"/>
        <result property="resumeTime" column="resume_time"/>
        <result property="linkMan" column="link_man"/>
        <result property="linkTel" column="link_tel"/>
        <result property="companyName" column="company_name"/>
        <result property="emall" column="emall"/>
        <result property="startYear" column="start_year"/>
        <result property="userBirth" column="user_birth"/>
        <result property="eduLevel" column="edu_level"/>
        <result property="completeness" column="completeness"/>
        <result property="sex" column="sex"/>
        <result property="district" column="district"/>
        <result property="city" column="city"/>
        <result property="userEMall" column="userEMall"/>
        <result property="companyDistrict" column="company_district"/>
        <result property="companyCity" column="company_city"/>
        <result property="companyProvince" column="company_province"/>
        <result property="companyAddress" column="company_address"/>
        <result property="companyId" column="company_id"/>
    </resultMap>

    <resultMap type="com.zhongzheng.modules.recruit.vo.RecruitInterviewExcel" id="RecruitInterviewExcelResult">
        <result property="realName" column="realname"/>
        <result property="job" column="job"/>
        <result property="date" column="date"/>
        <result property="status" column="status"/>
        <result property="phone" column="telphone"/>
        <result property="timing" column="timing"/>
    </resultMap>

    <resultMap type="com.zhongzheng.modules.recruit.vo.RecruitInterviewUserVo" id="RecruitInterviewUserVoResult">
        <result property="resumeJson" column="resume_json"/>
        <association property="userVo" javaType="com.zhongzheng.modules.user.vo.UserVo" resultMap="UserVoResult"/>
    </resultMap>

    <resultMap type="com.zhongzheng.modules.user.vo.UserVo" id="UserVoResult">
        <result property="userId" column="user_id"/>
        <result property="nickname" column="nickname"/>
        <result property="realname" column="realname"/>
        <result property="sex" column="sex"/>
        <result property="idCard" column="id_card"/>
        <result property="telphone" column="telphone"/>
        <result property="userLevel" column="user_level"/>
        <result property="userBirth" column="user_birth"/>
        <result property="eduLevel" column="edu_level"/>
        <result property="schoolId" column="school_id"/>
        <result property="major" column="major"/>
        <result property="entranceTime" column="entrance_time"/>
        <result property="openId" column="open_id"/>
        <result property="idCardImg1" column="id_card_img1"/>
        <result property="idCardImg2" column="id_card_img2"/>
        <result property="country" column="country"/>
        <result property="province" column="province"/>
        <result property="city" column="city"/>
        <result property="integral" column="integral"/>
        <result property="createTime" column="create_time"/>
        <result property="status" column="status"/>
        <result property="certified" column="certified"/>
        <result property="certifiedTime" column="certified_time"/>
        <result property="district" column="district"/>
        <result property="marry" column="marry"/>
        <result property="houseProvince" column="house_province"/>
        <result property="houseCity" column="house_city"/>
        <result property="houseDistrict" column="house_district"/>
        <result property="oneInchPhotos" column="one_inch_photos"/>
        <result property="politic" column="politic"/>
        <result property="email" column="email"/>
        <result property="avatar" column="avatar"/>
        <result property="courseNum" column="course_num"/>
        <result property="studyTime" column="study_time"/>
    </resultMap>

    <select id="selectByDay" parameterType="map" resultMap="RecruitInterviewVoResult">
        select r.*,u.realname,n.job from recruit_interview r
            LEFT JOIN `user` u on r.user_id = u.user_id
            LEFT JOIN recruit_needs n on n.id = r.needs_id
        where 1=1
        <if test="year!=null">
                and r.year=#{year}
            </if>
            <if test="month!=null">
                and r.month=#{month}
            </if>
            <if test="day!=null">
                and r.day=#{day}
            </if>
        <if test="timingStart !=null">
            and r.timing_start=#{timingStart}
        </if>
        <if test="timingEnd !=null">
            and r.timing_End=#{timingEnd}
        </if>
        <if test="status != null and status.size()!=0 ">
            AND r.status in
            <foreach collection="status" item="item" index="index" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="selectQueryExcel" parameterType="com.zhongzheng.modules.recruit.bo.RecruitInterviewQueryBo" resultMap="RecruitInterviewExcelResult">
        SELECT
            CONCAT( r.`month`, '月', r.`day`, '日' ) AS date,
	n.job,
	u.realname,
	u.telphone,
	CONCAT( r.timing_start, ':00-', r.timing_end, ':00' ) AS timing,
	(
	CASE
			r.`status`
			WHEN 0 THEN
			'已投递'
			WHEN 1 THEN
			'已发邀请'
			WHEN 2 THEN
			'已确认'
			WHEN 3 THEN
			'已谢绝'
			WHEN 4 THEN
			'初试通过'
			WHEN 5 THEN
			'初试不通过'
			WHEN 6 THEN
			'复试通过'
			WHEN 8 THEN
			'已发offer'
			WHEN 9 THEN
			'爽约'
			WHEN 10 THEN
			'淘汰'
			WHEN 11 THEN
			'放弃'
			WHEN 12 THEN
			'转正'
		END
		) AS `status`
        FROM
            recruit_interview r
            LEFT JOIN `user` u ON u.user_id = r.user_id
            LEFT JOIN recruit_needs n ON n.id = r.needs_id
        WHERE
            1 =1
        <if test="statusIds != null and statusIds.size()!=0 ">
            AND r.status in
            <foreach collection="statusIds" item="item" index="index" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="year!=null">
            and r.year=#{year}
        </if>
        <if test="month!=null">
            and r.month=#{month}
        </if>
        <if test="day!=null">
            and r.day=#{day}
        </if>
    </select>

    <select id="selectByListAll" parameterType="com.zhongzheng.modules.recruit.bo.RecruitInterviewQueryBo" resultMap="RecruitInterviewVoResult">
        SELECT
        u.realname,
        n.job,
        u.sex,
        u.email as userEMall,
        u.city,
        u.district,
        r.year,
        r.interview_id,
        r.month,
        r.day,
        r.timing_start,
        r.timing_end,
        r.needs_id,
        r.resume_json,
        r.create_time,
        r.update_time,
        r.status,
        r.tenant_id,
        r.user_id,
        r.attach_url,
        r.refuse,
        r.resume_time,
        r.emall,
        r.start_year,
        r.user_birth,
        r.edu_level,
        r.completeness,
        c.company_name,
        n.link_man,
        n.link_tel,
        n.city as company_city,
        n.district as company_district,
        n.province as company_province,
        n.address as company_address,
        n.company_id,
        n.income_type,
        n.income_start,
        n.income_end
        FROM
            recruit_interview r
                LEFT JOIN recruit_needs n ON r.needs_id = n.id
                LEFT JOIN USER u ON u.user_id = r.user_id
                LEFT JOIN v_tenant_company c ON c.company_id = n.company_id
        where 1=1
        <if test="job !=null">
          and n.job like concat('%',#{job},'%')
        </if>
        <if test="workStart !=null and workEnd !=null and workEnd != '' and workStart!=''">
            and (SELECT DATE_FORMAT(NOW(), '%Y') as `year`)-r.start_year BETWEEN #{workStart} and #{workEnd}
        </if>
        <if test="ageStart !=null and ageEnd !=null and ageEnd != '' and ageStart!=''">
            and TIMESTAMPDIFF(YEAR,r.user_birth,CURDATE())  BETWEEN #{ageStart} and #{ageEnd}
        </if>
        <if test="eduLevel !=null and eduLevel!=''">
            and r.edu_level = #{eduLevel}
        </if>
        <if test="completeness !=null and completeness!=''">
            and r.completeness > #{completeness}
        </if>
        <if test="district !=null and district!=''">
            and u.district = #{district}
        </if>
        <if test="province !=null and completeness!=''">
            and u.province = #{province}
        </if>
        <if test="city !=null and city!=''">
            and u.city = #{city}
        </if>
        <if test="houseProvince !=null and houseProvince!=''">
            and u.house_province = #{houseProvince}
        </if>
        <if test="houseCity !=null and houseCity!=''">
            and u.house_city = #{houseCity}
        </if>
        <if test="sex !=null and sex != ''">
            and u.sex = #{sex}
        </if>
        <if test="marry !=null and marry !=''">
            and u.marry = #{marry}
        </if>
        <if test="politic !=null and politic !=''">
            and u.politic = #{politic}
        </if>
        <if test="userId !=null and userId !=''">
            and r.user_id = #{userId}
        </if>
        <if test="status !=null">
            and r.status = #{status}
        </if>
        <if test="statusIds != null and statusIds.size()!=0 ">
            AND r.status in
            <foreach collection="statusIds" item="item" index="index" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        ORDER BY r.create_time desc
    </select>

    <select id="interviewUserId" parameterType="map" resultMap="RecruitInterviewUserVoResult">
        SELECT
            *
        FROM
            recruit_interview r
                LEFT JOIN `user` u ON u.user_id = r.user_id
        WHERE
            r.interview_id =#{interviewUserId}
    </select>

    <select id="selectSysUserName" parameterType="map" resultType="String">
        SELECT
            user_name
        FROM
            sys_user
        WHERE
            user_id =#{sysUserId}
    </select>

    <select id="selectUserName" parameterType="map" resultType="String">
        SELECT
            realname
        FROM
            user
        WHERE
            user_id =#{userId}
    </select>

    <select id="queryListAppAll" parameterType="com.zhongzheng.modules.recruit.bo.RecruitInterviewQueryBo" resultMap="RecruitInterviewVoResult">
        SELECT
        u.realname,
        n.job,
        n.income_type,
        n.income_start,
        n.income_end,
        u.sex,
        u.email as userEMall,
        u.city,
        u.district,
        r.*,
        n.city as company_city,
        n.district as company_district,
        n.province as company_province,
        n.address as company_address
        FROM
        recruit_interview r
        LEFT JOIN recruit_needs n ON r.needs_id = n.id
        LEFT JOIN USER u ON u.user_id = r.user_id
        LEFT JOIN v_tenant_company c ON c.company_id = n.company_id
        where 1=1  and r.status != 0
        <if test="job !=null">
            and n.job = #{job}
        </if>
        <if test="workStart !=null and workEnd !=null and workEnd != '' and workStart!=''">
            and (SELECT DATE_FORMAT(NOW(), '%Y') as `year`)-r.start_year BETWEEN #{workStart} and #{workEnd}
        </if>
        <if test="ageStart !=null and ageEnd !=null and ageEnd != '' and ageStart!=''">
            and TIMESTAMPDIFF(YEAR,r.user_birth,CURDATE())  BETWEEN #{ageStart} and #{ageEnd}
        </if>
        <if test="eduLevel !=null and eduLevel!=''">
            and r.edu_level = #{eduLevel}
        </if>
        <if test="completeness !=null and completeness!=''">
            and r.completeness > #{completeness}
        </if>
        <if test="district !=null and district!=''">
            and u.district = #{district}
        </if>
        <if test="province !=null and completeness!=''">
            and u.province = #{province}
        </if>
        <if test="city !=null and city!=''">
            and u.city = #{city}
        </if>
        <if test="houseProvince !=null and houseProvince!=''">
            and u.house_province = #{houseProvince}
        </if>
        <if test="houseCity !=null and houseCity!=''">
            and u.house_city = #{houseCity}
        </if>
        <if test="sex !=null and sex != ''">
            and u.sex = #{sex}
        </if>
        <if test="marry !=null and marry !=''">
            and u.marry = #{marry}
        </if>
        <if test="politic !=null and politic !=''">
            and u.politic = #{politic}
        </if>
        <if test="userId !=null and userId !=''">
            and r.user_id = #{userId}
        </if>
        ORDER BY r.create_time desc
    </select>
</mapper>