<?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.yifu.cloud.plus.v1.csp.mapper.EmployeeRegistrationMapper">

  <resultMap id="employeeRegistrationMap" type="com.yifu.cloud.plus.v1.csp.entity.EmployeeRegistration">
    <id property="id" column="id"/>
    <result property="employeeName" column="employee_name"/>
    <result property="empIdcard" column="emp_idcard"/>
    <result property="empPhone" column="emp_phone"/>
    <result property="position" column="position"/>
    <result property="feedbackType" column="feedback_type"/>
    <result property="joinLeaveDate" column="join_leave_date"/>
    <result property="processStatus" column="process_status"/>
    <result property="registorUsername" column="registor_username"/>
    <result property="registorDeptname" column="registor_deptname"/>
    <result property="registorDeptid" column="registor_deptid"/>
    <result property="registorPhone" column="registor_phone"/>
    <result property="empDeptname" column="emp_deptname"/>
    <result property="empDeptid" column="emp_deptid"/>
    <result property="createBy" column="create_by"/>
    <result property="createName" column="create_name"/>
    <result property="createTime" column="create_time"/>
    <result property="updateBy" column="update_by"/>
    <result property="updateTime" column="update_time"/>
    <result property="deptName" column="dept_name"/>
    <result property="deptNo" column="dept_no"/>
	  <result property="deptId" column="dept_id"/>
	  <result property="customerPhone" column="customer_phone"/>
	  <result property="customerUsername" column="customer_username"/>
	  <result property="customerUserLoginname" column="customer_user_loginname"/>
	  <result property="dataSource" column="data_source"/>
	  <result property="customerUsernameNew" column="customer_username_new"/>
	  <result property="customerUserLoginNameNew" column="customer_user_login_name_new"/>
	  <result property="leaveStatus" column="leave_status"/>
  </resultMap>

	<resultMap id="employeeRegistrationExportMap" type="com.yifu.cloud.plus.v1.csp.vo.EmployeeRegistrationExportVo">
		<result property="employeeName" column="employee_name"/>
		<result property="empIdcard" column="emp_idcard"/>
		<result property="empPhone" column="emp_phone"/>
		<result property="position" column="position"/>
		<result property="feedbackType" column="feedback_type"/>
		<result property="joinLeaveDate" column="join_leave_date"/>
		<result property="processStatus" column="process_status"/>
		<result property="registorUsername" column="registor_username"/>
		<result property="empDeptname" column="emp_deptname"/>
		<result property="createTime" column="create_time"/>
	</resultMap>

	<resultMap id="employeeRegistrationHrExportMap" type="com.yifu.cloud.plus.v1.csp.vo.EmployeeRegistrationHrExportVo">
		<result property="employeeName" column="employee_name"/>
		<result property="empIdcard" column="emp_idcard"/>
		<result property="empPhone" column="emp_phone"/>
		<result property="position" column="position"/>
		<result property="feedbackType" column="feedback_type"/>
		<result property="joinLeaveDate" column="join_leave_date"/>
		<result property="processStatus" column="process_status"/>
		<result property="registorUsername" column="registor_username"/>
		<result property="empDeptname" column="emp_deptname"/>
		<result property="createTime" column="create_time"/>
		<result property="deptName" column="dept_name"/>
		<result property="deptNo" column="dept_no"/>
	</resultMap>

	<resultMap id="employeeRegistrationHrPreExportMap" type="com.yifu.cloud.plus.v1.csp.vo.EmployeeRegistrationHrPreExportVo">
		<result property="employeeName" column="employee_name"/>
		<result property="empIdcard" column="emp_idcard"/>
		<result property="empPhone" column="emp_phone"/>
		<result property="position" column="position"/>
		<result property="joinLeaveDate" column="join_leave_date"/>
		<result property="processStatus" column="process_status"/>
		<result property="registorUsername" column="registor_username"/>
		<result property="createTime" column="create_time"/>
		<result property="deptName" column="dept_name"/>
		<result property="deptNo" column="dept_no"/>
		<result property="dataSource" column="data_source"/>
		<result property="customerUsernameNew" column="customer_username_new"/>
	</resultMap>

	<resultMap id="employeeRegistrationHrAutoExportMap" type="com.yifu.cloud.plus.v1.csp.vo.EmployeeRegistrationHrAutoExportVo">
		<result property="employeeName" column="employee_name"/>
		<result property="empIdcard" column="emp_idcard"/>
		<result property="empPhone" column="emp_phone"/>
		<result property="position" column="position"/>
		<result property="feedbackType" column="feedback_type"/>
		<result property="joinLeaveDate" column="join_leave_date"/>
		<result property="processStatus" column="process_status"/>
		<result property="registorUsername" column="registor_username"/>
		<result property="customerUsername" column="customer_username"/>
		<result property="createTime" column="create_time"/>
		<result property="deptName" column="dept_name"/>
		<result property="deptNo" column="dept_no"/>
		<result property="dataSource" column="data_source"/>
	</resultMap>
    <sql id="Base_Column_List">
                a.id,
                a.employee_name,
                a.emp_idcard,
                a.emp_phone,
                a.position,
                a.feedback_type,
                a.join_leave_date,
                a.process_status,
                a.registor_username,
                a.registor_deptname,
                a.registor_deptid,
                a.registor_phone,
                a.emp_deptname,
                a.emp_deptid,
                a.create_by,
                a.create_name,
                a.create_time,
                a.update_by,
                a.update_time,
		        a.dept_name,
		        a.dept_id,
                a.dept_no,
		        a.customer_username,
		        a.customer_phone,
				a.customer_user_loginname,
		        a.data_source,
				a.customer_username_new,a.customer_user_login_name_new
    </sql>
    <sql id="employeeRegistration_where">
        <if test="employeeRegistration != null">
                    <if test="employeeRegistration.id != null and employeeRegistration.id.trim() != ''">
                        AND a.id = #{employeeRegistration.id}
                    </if>
                    <if test="employeeRegistration.employeeName != null and employeeRegistration.employeeName.trim() != ''">
                        AND a.employee_name like CONCAT('%',#{employeeRegistration.employeeName},'%')
                    </if>
                    <if test="employeeRegistration.empIdcard != null and employeeRegistration.empIdcard.trim() != ''">
                        AND a.emp_idcard like CONCAT('%',#{employeeRegistration.empIdcard},'%')
                    </if>
					<if test="employeeRegistration.idcardList != null and employeeRegistration.idcardList.size() > 0 ">
						AND a.emp_idcard in
						<foreach item="item" index="index" collection="employeeRegistration.idcardList" open="(" separator="," close=")">
							'${item}'
						</foreach>
					</if>
                    <if test="employeeRegistration.empPhone != null and employeeRegistration.empPhone.trim() != ''">
                        AND a.emp_phone like CONCAT('%',#{employeeRegistration.empPhone},'%')
                    </if>
					<if test="employeeRegistration.postList != null and employeeRegistration.postList.size() > 0 ">
						AND a.position in
						<foreach item="item" index="index" collection="employeeRegistration.postList" open="(" separator="," close=")">
							'${item}'
						</foreach>
					</if>
					<if test="employeeRegistration.position != null and employeeRegistration.position.trim() != ''">
						AND a.position like CONCAT('%',#{employeeRegistration.position},'%')
					</if>
					<if test="employeeRegistration.typeList != null and employeeRegistration.typeList.size() > 0 ">
						AND a.feedback_type in
						<foreach item="item" index="index" collection="employeeRegistration.typeList" open="(" separator="," close=")">
							'${item}'
						</foreach>
					</if>
					<if test="employeeRegistration.createTimeStart != null">
						AND a.CREATE_TIME <![CDATA[ >= ]]> #{employeeRegistration.createTimeStart}
					</if>
					<if test="employeeRegistration.createTimeEnd != null">
						AND a.CREATE_TIME <![CDATA[ <= ]]> #{employeeRegistration.createTimeEnd}
					</if>
					<if test="employeeRegistration.joinTimeStart != null">
						AND a.join_leave_date <![CDATA[ >= ]]> #{employeeRegistration.joinTimeStart}
					</if>
					<if test="employeeRegistration.joinTimeEnd != null">
						AND a.join_leave_date <![CDATA[ <= ]]> #{employeeRegistration.joinTimeEnd}
					</if>
                    <if test="employeeRegistration.joinLeaveDate != null">
                        AND a.join_leave_date = #{employeeRegistration.joinLeaveDate}
                    </if>
                    <if test="employeeRegistration.statusList != null and employeeRegistration.statusList.size() > 0 ">
                        AND a.process_status in
						<foreach item="item" index="index" collection="employeeRegistration.statusList" open="(" separator="," close=")">
							'${item}'
						</foreach>
                    </if>
                    <if test="employeeRegistration.registorUsername != null and employeeRegistration.registorUsername.trim() != ''">
                        AND a.registor_username = #{employeeRegistration.registorUsername}
                    </if>
                    <if test="employeeRegistration.deptName != null and employeeRegistration.deptName.trim() != ''">
                        AND a.dept_name like CONCAT('%',#{employeeRegistration.deptName},'%')
                    </if>
                    <if test="employeeRegistration.registorDeptid != null and employeeRegistration.registorDeptid.trim() != ''">
                        AND a.registor_deptid = #{employeeRegistration.registorDeptid}
                    </if>
                    <if test="employeeRegistration.registorPhone != null and employeeRegistration.registorPhone.trim() != ''">
                        AND a.registor_phone = #{employeeRegistration.registorPhone}
                    </if>
                    <if test="employeeRegistration.empDeptname != null and employeeRegistration.empDeptname.trim() != ''">
                        AND a.emp_deptname = #{employeeRegistration.empDeptname}
                    </if>
                    <if test="employeeRegistration.empDeptid != null and employeeRegistration.empDeptid.trim() != ''">
                        AND a.emp_deptid = #{employeeRegistration.empDeptid}
                    </if>
                    <if test="employeeRegistration.createBy != null and employeeRegistration.createBy.trim() != ''">
                        AND a.create_by = #{employeeRegistration.createBy}
                    </if>
                    <if test="employeeRegistration.createName != null and employeeRegistration.createName.trim() != ''">
                        AND a.create_name = #{employeeRegistration.createName}
                    </if>
					<if test="employeeRegistration.customerUserLoginname != null and employeeRegistration.customerUserLoginname.trim() != ''">
						AND a.customer_user_loginname = #{employeeRegistration.customerUserLoginname}
					</if>
					<if test="employeeRegistration.deptNo != null and employeeRegistration.deptNo.trim() != ''">
						AND a.dept_no = #{employeeRegistration.deptNo}
					</if>
					<if test="employeeRegistration.dataSource != null and employeeRegistration.dataSource.trim() != ''">
						AND a.data_source = #{employeeRegistration.dataSource}
					</if>
					<if test="employeeRegistration.customerUsernameNew != null and employeeRegistration.customerUsernameNew.trim() != ''">
						AND a.customer_username_new like CONCAT('%',#{employeeRegistration.customerUsernameNew},'%')
					</if>
					<if test="employeeRegistration.departIdSet != null and employeeRegistration.departIdSet.size > 0">
						AND a.emp_deptid in
						<foreach item="idStr" index="index" collection="employeeRegistration.departIdSet" open="(" separator=","
								 close=")">
							#{idStr}
						</foreach>
					</if>
					<if test="employeeRegistration.deptNoList != null and employeeRegistration.deptNoList.size > 0">
						AND a.dept_no in
						<foreach item="idStr" index="index" collection="employeeRegistration.deptNoList" open="(" separator=","
								 close=")">
							#{idStr}
						</foreach>
					</if>
					<if test="employeeRegistration.authSql != null and employeeRegistration.authSql.trim() != ''">
						${employeeRegistration.authSql}
					</if>
        </if>
    </sql>
    <!--employeeRegistration简单分页查询-->
    <select id="getEmployeeRegistrationPage" resultMap="employeeRegistrationMap">
        SELECT
        <include refid="Base_Column_List"/>,b.leave_status
        FROM employee_registration a
		left join employee_registration_leave b on a.id = b.id
        <where>
            1=1
            <include refid="employeeRegistration_where"/>
        </where>
		order by a.create_time desc
    </select>

	<!--employeeRegistration简单分页查询-->
	<select id="getHrConfirmIdcardList" resultMap="employeeRegistrationMap">
		SELECT
		<include refid="Base_Column_List"/>
		FROM employee_registration a
		<where>
			1=1
			<include refid="employeeRegistration_where"/>
		</where>
		limit 0,2000
	</select>

	<!--employeeRegistration简单分页查询-->
	<select id="getEmployeeRegistrationList" resultMap="employeeRegistrationMap">
		SELECT
		<include refid="Base_Column_List"/>
		FROM employee_registration a
		<where>
			1=1
			<include refid="employeeRegistration_where"/>
		</where>
		order by a.create_time desc
	</select>

	<select id="selectExportCount" resultType="java.lang.Long">
		SELECT
		count(1)
		FROM employee_registration a
		<where>
			1=1
			<include refid="employeeRegistration_where"/>
		</where>
	</select>

	<select id="selectExportList" resultMap="employeeRegistrationExportMap">
		SELECT
		a.employee_name,
		a.emp_idcard,
		a.emp_phone,
		a.position,
		if(a.feedback_type = '1','入职','离职') feedback_type,
		a.join_leave_date,
		case a.process_status when '0' then '已接收' when '1' then '待处理' when '2' then '已处理' when '3' then '拒绝入职' when '5' then '已确认离职' else '-' end process_status,
		a.registor_username,
		a.emp_deptname,
		a.create_time
		FROM employee_registration a
		<where>
			1=1
			<include refid="employeeRegistration_where"/>
		</where>
		order by a.create_time desc
		<if test="employeeRegistration.limitStart != null">
			limit #{employeeRegistration.limitStart},#{employeeRegistration.limitEnd}
		</if>
	</select>

	<select id="selectHrExportList" resultMap="employeeRegistrationHrExportMap">
		SELECT
		a.dept_name,
		a.dept_no,
		a.employee_name,
		a.emp_idcard,
		a.emp_phone,
		a.position,
		if(a.feedback_type = '1','入职','离职') feedback_type,
		a.join_leave_date,
		case a.process_status when '0' then '已接收' when '1' then '待处理' when '2' then '已处理' when '3' then '拒绝入职' when '5' then '已确认离职' else '-' end process_status,
		a.registor_username,
		a.emp_deptname,
		a.create_time
		FROM employee_registration a
		<where>
			1=1
			<include refid="employeeRegistration_where"/>
		</where>
		order by a.create_time desc
		<if test="employeeRegistration.limitStart != null">
			limit #{employeeRegistration.limitStart},#{employeeRegistration.limitEnd}
		</if>
	</select>

	<select id="selectHrPreExportList" resultMap="employeeRegistrationHrPreExportMap">
		SELECT
		a.dept_name,
		a.dept_no,
		a.employee_name,
		a.emp_idcard,
		a.emp_phone,
		a.position,
		a.join_leave_date,
		'待处理' process_status,
		a.registor_username,
		a.create_time,
		a.customer_username_new,
		if(a.data_source = '1','客户端','客服端') data_source
		FROM employee_registration a
		<where>
			1=1
			<include refid="employeeRegistration_where"/>
		</where>
		order by a.create_time desc
		<if test="employeeRegistration.limitStart != null">
			limit #{employeeRegistration.limitStart},#{employeeRegistration.limitEnd}
		</if>
	</select>

	<!-- 获取待处理的数据 -->
	<select id="getZeroRegistrationByIdCard" resultType="com.yifu.cloud.plus.v1.yifu.archives.entity.TEmployeeProjectBelongDept">
		SELECT
		    a.id,
			a.emp_deptname empDeptname,
			a.emp_deptid empDeptid,
			l.CREATE_TIME logCreateTime
		FROM employee_registration a
		left join t_operation_log l on l.MAIN_ID=a.id and l.TITLE = '确认接收'
		where a.emp_idcard = #{searchCspVo.empIdCard} and a.process_status= '0'
		<if test="searchCspVo.deptId != null and searchCspVo.deptId.trim() != ''">
			AND a.dept_id = #{searchCspVo.deptId}
		</if>
		<if test="searchCspVo.feedbackType != null and searchCspVo.feedbackType.trim() != ''">
			AND a.feedback_type = #{searchCspVo.feedbackType}
		</if>
		GROUP BY a.id
		order by a.create_time desc
	</select>

	<select id="selectHrAutoExportList" resultMap="employeeRegistrationHrAutoExportMap">
		SELECT
		a.dept_name,
		a.dept_no,
		a.employee_name,
		a.emp_idcard,
		a.emp_phone,
		a.position,
		if(a.feedback_type = '1','入职','离职') feedback_type,
		a.join_leave_date,
		case a.process_status when '0' then '已接收' when '1' then '待处理' when '2' then '已处理' when '3' then '拒绝入职' when '5' then '已确认离职' else '-' end process_status,
		a.registor_username,
		a.create_time,
		a.customer_username,
		if(a.data_source = '1','客户端','客服端') data_source
		FROM employee_registration a
		<where>
			1=1
			<include refid="employeeRegistration_where"/>
		</where>
		order by a.create_time desc
		<if test="employeeRegistration.limitStart != null">
			limit #{employeeRegistration.limitStart},#{employeeRegistration.limitEnd}
		</if>
	</select>

	<!-- 将数据处理为已处理 -->
	<update id="updateTwoRegistrationByIdCard">
		update employee_registration a
		set a.process_status= '2'
		where a.emp_idcard = #{searchCspVo.empIdCard} and a.process_status= '0'
		<if test="searchCspVo.deptId != null and searchCspVo.deptId.trim() != ''">
			AND a.dept_id = #{searchCspVo.deptId}
		</if>
		<if test="searchCspVo.feedbackType != null and searchCspVo.feedbackType.trim() != ''">
			AND a.feedback_type = #{searchCspVo.feedbackType}
		</if>
	</update>

	<select id="selectRegistrationListBySure" resultMap="employeeRegistrationMap">
		SELECT
			<include refid="Base_Column_List"/>
		FROM employee_registration a
			left join employee_registration_leave b on a.id=b.id
		where b.leave_status = '0'
		<if test="idList != null">
			AND a.id in
			<foreach item="idStr" index="index" collection="idList" open="(" separator="," close=")">
				#{idStr}
			</foreach>
		</if>
	</select>


	<!--查询是否有处理中的离职信息，如果有，不允许再次登记入离职-->
	<select id="getEmployeeRegistrationLeavingCount" resultType="java.lang.Integer">
		SELECT
			count(1)
		FROM employee_registration a
		join employee_registration_leave b on a.id = b.id
		where a.emp_idcard = #{idCard} AND a.dept_no = #{deptNo} and b.leave_status != '3'
	</select>

</mapper>
