<?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.lz.modules.app.dao.StaffOccupationDao">

    <!-- 可根据自己的需求，是否要使用 -->
    <resultMap type="com.lz.modules.app.entity.StaffOccupationEntity" id="staffOccupationMap">
        <result property="id" column="id"/>
        <result property="isDelete" column="is_delete"/>
        <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>
        <result property="staffId" column="staff_id"/>
        <result property="staffNo" column="staff_no"/>
        <result property="employeeNo" column="employee_no"/>
        <result property="position" column="position"/>
        <result property="entryTime" column="entry_time"/>
        <result property="companySeniority" column="company_seniority"/>
        <result property="officialTime" column="official_time"/>
        <result property="staffStatus" column="staff_status"/>
        <result property="staffType" column="staff_type"/>
        <result property="resignationTime" column="resignation_time"/>
        <result property="resignationReasons" column="resignation_reasons"/>
        <result property="signingCompany" column="signing_company"/>
        <result property="contractEndTime" column="contract_end_time"/>
        <result property="jobBeginTime" column="job_begin_time"/>
        <result property="jobSeniority" column="job_seniority"/>
        <result property="workExperience" column="work_experience"/>
        <result property="resumeUrl" column="resume_url"/>
    </resultMap>

    <select id="getStaffOccupationByStaffId" resultType="com.lz.modules.app.entity.StaffOccupationEntity">
        select * from lz_staff_occupation where is_delete=0 and staff_id = #{staffId}
    </select>

    <select id="getOccupationByStaffId" resultType="com.lz.modules.app.dto.StaffOccupationInfoDto">
    select employee_no staff_no,position,DATE_FORMAT(entry_time,'%Y-%m-%d') entry_time,staff_status,DATE_FORMAT(resignation_time,'%Y-%m-%d') resignation_time,resignation_reasons,
    concat(floor(TIMESTAMPDIFF(day,entry_time,DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))/360),'年',TIMESTAMPDIFF(day,entry_time,DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S')) mod 360 div 30,'个月',TIMESTAMPDIFF(day,entry_time,DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S')) mod 360 mod 30,'天') company_seniority,
    signing_company,DATE_FORMAT(job_begin_time,'%Y-%m-%d') job_begin_time,work_experience,resume_url
    from lz_staff_occupation where is_delete=0 and staff_id=#{staffId}
    </select>

    <update id="updateStatusByStaffId">
        update lz_staff_occupation set staff_status=#{status},resignation_time=null where is_delete=0 and staff_id = #{staffId}
    </update>

    <update id="updateAllOccupation">
        update lz_staff_occupation set staff_status=1,resignation_time=now() where is_delete=0 and staff_status=0
    </update>

    <insert id="addStaffOccupationBatch">
        INSERT INTO lz_staff_occupation(staff_id,employee_no,staff_type,staff_status)
        VALUES
        <foreach collection="staffOccupations" item="staffOccupation" separator=",">
            (#{staffOccupation.staffId},#{staffOccupation.employeeNo},#{staffOccupation.staffType},#{staffOccupation.staffStatus})
        </foreach>
    </insert>

    <update id="updateStatusByStaff">
        update lz_staff_occupation set staff_status=#{departmentStaffBo.status},resignation_time=null, position=#{departmentStaffBo.position}, staff_no=#{departmentStaffBo.employeeNo} where is_delete=0 and staff_id = #{staffId}
    </update>

    <select id="removeDimissionStaffByStaffIds" resultType="String">
       SELECT staff_id from lz_staff_occupation where staff_status = 0 and
       staff_id in
        <foreach collection="staffIds" item="staff_id" open="(" close=")"
                 separator=",">
            #{staff_id}
        </foreach>
    </select>

</mapper>
