<?xml version="1.0" encoding="UTF-8"?>

<!--
  ~
  ~      Copyright (c) 2018-2025, lengleng All rights reserved.
  ~
  ~  Redistribution and use in source and binary forms, with or without
  ~  modification, are permitted provided that the following conditions are met:
  ~
  ~ Redistributions of source code must retain the above copyright notice,
  ~  this list of conditions and the following disclaimer.
  ~  Redistributions in binary form must reproduce the above copyright
  ~  notice, this list of conditions and the following disclaimer in the
  ~  documentation and/or other materials provided with the distribution.
  ~  Neither the name of the yifu4cloud.com developer nor the names of its
  ~  contributors may be used to endorse or promote products derived from
  ~  this software without specific prior written permission.
  ~  Author: lengleng (wangiegie@gmail.com)
  ~
  -->

<!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.EmployeeRegistrationLeaveMapper">

	<!-- 主要使用 -->
  <resultMap id="tableAndExportMap" type="com.yifu.cloud.plus.v1.csp.vo.EmployeeRegistrationLeaveTableVo">
    <id property="id" column="id"/>
    <result property="leaveStatus" column="leave_status"/>
    <result property="doStatus" column="do_status"/>
    <result property="doStatusContract" column="do_status_contract"/>
    <result property="doStatusSocial" column="do_status_social"/>
    <result property="doStatusFund" column="do_status_fund"/>
    <result property="doStatusInsurance" column="do_status_insurance"/>
    <result property="doStatusProject" column="do_status_project"/>
    <result property="doStatusEmp" column="do_status_emp"/>
	<result property="confirmUser" column="confirm_user"/>
	<result property="confirmTime" column="confirm_time"/>
	<result property="linkPort" column="link_port"/>
	<result property="doType" column="do_type"/>
	  <result property="empProjectId" column="emp_project_id"/>
	  <result property="empId" column="emp_id"/>
	  <result property="contractId" column="contract_id"/>
	  <result property="insuranceId" column="insurance_id"/>
	  <result property="fundId" column="fund_id"/>
	  <result property="socialId" column="social_id"/>
	  <result property="failSocialUser" column="fail_social_user"/>
	  <result property="failFundUser" column="fail_fund_user"/>
	  <result property="failInsuranceUser" column="fail_insurance_user"/>
	  <result property="failContractUser" column="fail_contract_user"/>
	  <result property="failEmpUser" column="fail_emp_user"/>
	  <result property="failEmpProjectUser" column="fail_emp_project_user"/>
	  <result property="failSocialRemark" column="fail_social_remark"/>
	  <result property="failFundRemark" column="fail_fund_remark"/>
	  <result property="failInsuranceRemark" column="fail_insurance_remark"/>
	  <result property="failContractRemark" column="fail_contract_remark"/>
	  <result property="failEmpRemark" column="fail_emp_remark"/>
	  <result property="failEmpProjectRemark" column="fail_emp_project_remark"/>
	  <result property="failSocialTime" column="fail_social_time"/>
	  <result property="failFundTime" column="fail_fund_time"/>
	  <result property="failInsuranceTime" column="fail_insurance_time"/>
	  <result property="failContractTime" column="fail_contract_time"/>
	  <result property="failEmpTime" column="fail_emp_time"/>
	  <result property="failEmpProjectTime" column="fail_emp_project_time"/>

	  <result property="employeeName" column="employee_name"/>
	  <result property="empIdcard" column="emp_idcard"/>
	  <result property="joinLeaveDate" column="join_leave_date"/>
	  <result property="deptName" column="dept_name"/>
	  <result property="deptNo" column="dept_no"/>
	  <result property="customerUsernameNew" column="customer_username_new"/>
  </resultMap>

	<!-- 新表全量字段-确认、派单时使用 -->
	<resultMap id="employeeRegistrationLeaveMap" type="com.yifu.cloud.plus.v1.csp.entity.EmployeeRegistrationLeave">
    <id property="id" column="id"/>
    <result property="leaveStatus" column="leave_status"/>
    <result property="doStatus" column="do_status"/>
    <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="doStatusContract" column="do_status_contract"/>
    <result property="doStatusSocial" column="do_status_social"/>
    <result property="doStatusFund" column="do_status_fund"/>
    <result property="doStatusInsurance" column="do_status_insurance"/>
    <result property="doStatusProject" column="do_status_project"/>
    <result property="doStatusEmp" column="do_status_emp"/>
    <result property="doType" column="do_type"/>
    <result property="socialId" column="social_id"/>
    <result property="socialReduceDate" column="social_reduce_date"/>
    <result property="socialTrustRemark" column="social_trust_remark"/>
    <result property="socialExpectedTime" column="social_expected_time"/>
    <result property="fundId" column="fund_id"/>
    <result property="fundReduceDate" column="fund_reduce_date"/>
    <result property="fundTrustRemark" column="fund_trust_remark"/>
    <result property="fundExpectedTime" column="fund_expected_time"/>
    <result property="insuranceId" column="insurance_id"/>
    <result property="insuranceExpectedTime" column="insurance_expected_time"/>
    <result property="insuranceExpectedTimeNext" column="insurance_expected_time_next"/>
    <result property="contractId" column="contract_id"/>
    <result property="contractExpectedTime" column="contract_expected_time"/>
    <result property="empId" column="emp_id"/>
    <result property="empProjectId" column="emp_project_id"/>
    <result property="confirmUser" column="confirm_user"/>
    <result property="confirmTime" column="confirm_time"/>
    <result property="linkPort" column="link_port"/>
    <result property="isMultipleDept" column="is_multiple_dept"/>
		<result property="socialDispatchIdOld" column="social_dispatch_id_old"/>
		<result property="fundDispatchIdOld" column="fund_dispatch_id_old"/>
		<result property="insuranceIdOld" column="insurance_id_old"/>
		<result property="contractIdOld" column="contract_id_old"/>
		<result property="empIdOld" column="emp_id_old"/>
		<result property="empProjectIdOld" column="emp_project_id_old"/>
		<result property="socialStatusOld" column="social_status_old"/>
		<result property="fundStatusOld" column="fund_status_old"/>
		<result property="insuranceStatusOld" column="insurance_status_old"/>
		<result property="contractStatusOld" column="contract_status_old"/>
		<result property="empStatusOld" column="emp_status_old"/>
		<result property="empProjectStatusOld" column="emp_project_status_old"/>
		<result property="failSocialUser" column="fail_social_user"/>
		<result property="failFundUser" column="fail_fund_user"/>
		<result property="failInsuranceUser" column="fail_insurance_user"/>
		<result property="failContractUser" column="fail_contract_user"/>
		<result property="failEmpUser" column="fail_emp_user"/>
		<result property="failEmpProjectUser" column="fail_emp_project_user"/>
		<result property="failSocialRemark" column="fail_social_remark"/>
		<result property="failFundRemark" column="fail_fund_remark"/>
		<result property="failInsuranceRemark" column="fail_insurance_remark"/>
		<result property="failContractRemark" column="fail_contract_remark"/>
		<result property="failEmpRemark" column="fail_emp_remark"/>
		<result property="failEmpProjectRemark" column="fail_emp_project_remark"/>
		<result property="failSocialTime" column="fail_social_time"/>
		<result property="failFundTime" column="fail_fund_time"/>
		<result property="failInsuranceTime" column="fail_insurance_time"/>
		<result property="failContractTime" column="fail_contract_time"/>
		<result property="failEmpTime" column="fail_emp_time"/>
		<result property="failEmpProjectTime" column="fail_emp_project_time"/>

		<result property="closeRemarkSocial" column="close_remark_social"/>
		<result property="closeRemarkFund" column="close_remark_fund"/>
		<result property="closeRemarkInsurance" column="close_remark_insurance"/>
		<result property="closeRemarkContract" column="close_remark_contract"/>
		<result property="closeRemarkEmpProject" column="close_remark_emp_project"/>
		<result property="closeRemarkEmp" column="close_remark_emp"/>

		<result property="employeeName" column="employee_name"/>
		<result property="empIdcard" column="emp_idcard"/>
		<result property="deptName" column="dept_name"/>
		<result property="deptNo" column="dept_no"/>
		<result property="joinLeaveDate" column="join_leave_date"/>
		<result property="leaveReasonName" column="leave_reason_name"/>
		<result property="leaveReasonId" column="leave_reason_id"/>
		<result property="customerUsernameNew" column="customer_username_new"/>
  </resultMap>
	<!-- 全量字段 -->
    <sql id="Base_Column_List">
                a.id,
                a.leave_status,
                a.do_status,
                a.create_by,
                a.create_name,
                a.create_time,
                a.update_by,
                a.update_time,
                a.do_status_contract,
                a.do_status_social,
                a.do_status_fund,
                a.do_status_insurance,
                a.do_status_project,
                a.do_status_emp,
                a.do_type,
                a.social_id,
                a.social_reduce_date,
                a.social_trust_remark,
                a.social_expected_time,
                a.fund_id,
                a.fund_reduce_date,
                a.fund_trust_remark,
                a.fund_expected_time,
                a.insurance_id,
                a.insurance_expected_time,
                a.insurance_expected_time_next,
                a.contract_id,
                a.contract_expected_time,
                a.emp_id,
                a.emp_project_id,
                a.confirm_user,
                a.confirm_time,
                a.link_port,
                a.is_multiple_dept,
                a.social_dispatch_id_old,
                a.fund_dispatch_id_old,
                a.insurance_id_old,
                a.contract_id_old,
                a.emp_id_old,
                a.emp_project_id_old,
                a.social_status_old,
                a.fund_status_old,
                a.insurance_status_old,
                a.contract_status_old,
                a.emp_status_old,
                a.emp_project_status_old,
				a.fail_social_user,
                a.fail_fund_user,
                a.fail_insurance_user,
                a.fail_contract_user,
                a.fail_emp_user,
                a.fail_emp_project_user,
                a.fail_social_remark,
                a.fail_fund_remark,
                a.fail_insurance_remark,
                a.fail_contract_remark,
                a.fail_emp_remark,
                a.fail_emp_project_remark,
                a.fail_social_time,
                a.fail_fund_time,
                a.fail_insurance_time,
                a.fail_contract_time,
                a.fail_emp_time,
                a.fail_emp_project_time,
                a.close_remark_social,
                a.close_remark_fund,
                a.close_remark_insurance,
                a.close_remark_contract,
                a.close_remark_emp,
                a.close_remark_emp_project,
                a.leave_reason_name,
                a.leave_reason_id
    </sql>


    <sql id="employeeRegistrationLeave_where">
        <if test="employeeRegistrationLeave != null">
                    <if test="employeeRegistrationLeave.id != null and employeeRegistrationLeave.id.trim() != ''">
                        AND a.id = #{employeeRegistrationLeave.id}
                    </if>
			<if test="employeeRegistrationLeave.idList != null">
				AND a.id in
				<foreach item="idStr" index="index" collection="employeeRegistrationLeave.idList" open="(" separator="," close=")">
					#{idStr}
				</foreach>
			</if>
			<if test="employeeRegistrationLeave.deptNoList != null and employeeRegistrationLeave.deptNoList.size > 0">
				AND b.DEPT_NO in
				<foreach item="idStr" index="index" collection="employeeRegistrationLeave.deptNoList" open="(" separator=","
						 close=")">
					#{idStr}
				</foreach>
			</if>
			<if test="employeeRegistrationLeave.customerUsernameNew != null and employeeRegistrationLeave.customerUsernameNew.trim() != ''">
				AND b.customer_username_new like CONCAT('%',#{employeeRegistrationLeave.customerUsernameNew},'%')
			</if>
			<if test="employeeRegistrationLeave.employeeName != null and employeeRegistrationLeave.employeeName.trim() != ''">
				AND b.employee_name like CONCAT('%',#{employeeRegistrationLeave.employeeName},'%')
			</if>
			<if test="employeeRegistrationLeave.empIdcard != null and employeeRegistrationLeave.empIdcard.trim() != ''">
				AND b.emp_idcard like CONCAT('%',#{employeeRegistrationLeave.empIdcard},'%')
			</if>
			<if test="employeeRegistrationLeave.idcardList != null and employeeRegistrationLeave.idcardList.size() > 0 ">
				AND b.emp_idcard in
				<foreach item="item" index="index" collection="employeeRegistrationLeave.idcardList" open="(" separator="," close=")">
					'${item}'
				</foreach>
			</if>
			<if test="employeeRegistrationLeave.deptName != null and employeeRegistrationLeave.deptName.trim() != ''">
				AND b.dept_name like CONCAT('%',#{employeeRegistrationLeave.deptName},'%')
			</if>
			<if test="employeeRegistrationLeave.joinTimeStart != null">
				AND b.join_leave_date <![CDATA[ >= ]]> #{employeeRegistrationLeave.joinTimeStart}
			</if>
			<if test="employeeRegistrationLeave.joinTimeEnd != null">
				AND b.join_leave_date <![CDATA[ <= ]]> #{employeeRegistrationLeave.joinTimeEnd}
			</if>
			<if test="employeeRegistrationLeave.deptNo != null and employeeRegistrationLeave.deptNo.trim() != ''">
				AND b.dept_no = #{employeeRegistrationLeave.deptNo}
			</if>
			
                    <if test="employeeRegistrationLeave.leaveStatus != null and employeeRegistrationLeave.leaveStatus.trim() != ''">
                        AND a.leave_status = #{employeeRegistrationLeave.leaveStatus}
                    </if>
			<if test="employeeRegistrationLeave.processStatusArray != null and employeeRegistrationLeave.processStatusArray.length > 0">
				AND a.leave_status in
				<foreach item="statusStr" index="index" collection="employeeRegistrationLeave.processStatusArray" open="("
						 separator="," close=")">
					#{statusStr}
				</foreach>
			</if>
                    <if test="employeeRegistrationLeave.doStatus != null and employeeRegistrationLeave.doStatus.trim() != ''">
                        AND a.do_status = #{employeeRegistrationLeave.doStatus}
                    </if>
                    <if test="employeeRegistrationLeave.createBy != null and employeeRegistrationLeave.createBy.trim() != ''">
                        AND a.create_by = #{employeeRegistrationLeave.createBy}
                    </if>
                    <if test="employeeRegistrationLeave.createName != null and employeeRegistrationLeave.createName.trim() != ''">
                        AND a.create_name = #{employeeRegistrationLeave.createName}
                    </if>
                    <if test="employeeRegistrationLeave.createTime != null">
                        AND a.create_time = #{employeeRegistrationLeave.createTime}
                    </if>
                    <if test="employeeRegistrationLeave.updateBy != null and employeeRegistrationLeave.updateBy.trim() != ''">
                        AND a.update_by = #{employeeRegistrationLeave.updateBy}
                    </if>
                    <if test="employeeRegistrationLeave.updateTime != null">
                        AND a.update_time = #{employeeRegistrationLeave.updateTime}
                    </if>
                    <if test="employeeRegistrationLeave.doStatusContract != null and employeeRegistrationLeave.doStatusContract.trim() != ''">
                        AND a.do_status_contract = #{employeeRegistrationLeave.doStatusContract}
                    </if>
                    <if test="employeeRegistrationLeave.doStatusSocial != null and employeeRegistrationLeave.doStatusSocial.trim() != ''">
                        AND a.do_status_social = #{employeeRegistrationLeave.doStatusSocial}
                    </if>
                    <if test="employeeRegistrationLeave.doStatusFund != null and employeeRegistrationLeave.doStatusFund.trim() != ''">
                        AND a.do_status_fund = #{employeeRegistrationLeave.doStatusFund}
                    </if>
                    <if test="employeeRegistrationLeave.doStatusInsurance != null and employeeRegistrationLeave.doStatusInsurance.trim() != ''">
                        AND a.do_status_insurance = #{employeeRegistrationLeave.doStatusInsurance}
                    </if>
                    <if test="employeeRegistrationLeave.doStatusProject != null and employeeRegistrationLeave.doStatusProject.trim() != ''">
                        AND a.do_status_project = #{employeeRegistrationLeave.doStatusProject}
                    </if>
                    <if test="employeeRegistrationLeave.doStatusEmp != null and employeeRegistrationLeave.doStatusEmp.trim() != ''">
                        AND a.do_status_emp = #{employeeRegistrationLeave.doStatusEmp}
                    </if>
                    <if test="employeeRegistrationLeave.doType != null and employeeRegistrationLeave.doType.trim() != ''">
                        AND a.do_type = #{employeeRegistrationLeave.doType}
                    </if>
                    <if test="employeeRegistrationLeave.socialId != null and employeeRegistrationLeave.socialId.trim() != ''">
                        AND a.social_id = #{employeeRegistrationLeave.socialId}
                    </if>
                    <if test="employeeRegistrationLeave.socialReduceDate != null and employeeRegistrationLeave.socialReduceDate.trim() != ''">
                        AND a.social_reduce_date = #{employeeRegistrationLeave.socialReduceDate}
                    </if>
                    <if test="employeeRegistrationLeave.socialTrustRemark != null and employeeRegistrationLeave.socialTrustRemark.trim() != ''">
                        AND a.social_trust_remark = #{employeeRegistrationLeave.socialTrustRemark}
                    </if>
                    <if test="employeeRegistrationLeave.socialExpectedTime != null">
                        AND a.social_expected_time = #{employeeRegistrationLeave.socialExpectedTime}
                    </if>
                    <if test="employeeRegistrationLeave.fundId != null and employeeRegistrationLeave.fundId.trim() != ''">
                        AND a.fund_id = #{employeeRegistrationLeave.fundId}
                    </if>
                    <if test="employeeRegistrationLeave.fundReduceDate != null and employeeRegistrationLeave.fundReduceDate.trim() != ''">
                        AND a.fund_reduce_date = #{employeeRegistrationLeave.fundReduceDate}
                    </if>
                    <if test="employeeRegistrationLeave.fundTrustRemark != null and employeeRegistrationLeave.fundTrustRemark.trim() != ''">
                        AND a.fund_trust_remark = #{employeeRegistrationLeave.fundTrustRemark}
                    </if>
                    <if test="employeeRegistrationLeave.fundExpectedTime != null">
                        AND a.fund_expected_time = #{employeeRegistrationLeave.fundExpectedTime}
                    </if>
                    <if test="employeeRegistrationLeave.insuranceId != null and employeeRegistrationLeave.insuranceId.trim() != ''">
                        AND a.insurance_id = #{employeeRegistrationLeave.insuranceId}
                    </if>
                    <if test="employeeRegistrationLeave.insuranceExpectedTime != null">
                        AND a.insurance_expected_time = #{employeeRegistrationLeave.insuranceExpectedTime}
                    </if>
                    <if test="employeeRegistrationLeave.insuranceExpectedTimeNext != null">
                        AND a.insurance_expected_time_next = #{employeeRegistrationLeave.insuranceExpectedTimeNext}
                    </if>
                    <if test="employeeRegistrationLeave.contractId != null and employeeRegistrationLeave.contractId.trim() != ''">
                        AND a.contract_id = #{employeeRegistrationLeave.contractId}
                    </if>
                    <if test="employeeRegistrationLeave.contractExpectedTime != null">
                        AND a.contract_expected_time = #{employeeRegistrationLeave.contractExpectedTime}
                    </if>
                    <if test="employeeRegistrationLeave.empId != null and employeeRegistrationLeave.empId.trim() != ''">
                        AND a.emp_id = #{employeeRegistrationLeave.empId}
                    </if>
                    <if test="employeeRegistrationLeave.empProjectId != null and employeeRegistrationLeave.empProjectId.trim() != ''">
                        AND a.emp_project_id = #{employeeRegistrationLeave.empProjectId}
                    </if>

			<if test="employeeRegistrationLeave.authSql != null and employeeRegistrationLeave.authSql.trim() != ''">
				${employeeRegistrationLeave.authSql}
			</if>

        </if>
    </sql>

	<!-- 离职待办列表 -->
	<sql id="Base_Table_List">
		a.id,a.do_type,a.emp_project_id,a.emp_id,a.contract_id,a.insurance_id,a.fund_id,a.social_id,
		a.fail_social_user,
		a.fail_fund_user,
		a.fail_insurance_user,
		a.fail_contract_user,
		a.fail_emp_user,
		a.fail_emp_project_user,
		a.fail_social_remark,
		a.fail_fund_remark,
		a.fail_insurance_remark,
		a.fail_contract_remark,
		a.fail_emp_remark,
		a.fail_emp_project_remark,
		a.fail_social_time,
		a.fail_fund_time,
		a.fail_insurance_time,
		a.fail_contract_time,
		a.fail_emp_time,
		a.fail_emp_project_time,
		<include refid="Base_Export_List"></include>
	</sql>

	<sql id="Base_Export_List">
		case a.leave_status when '0' then '待确认' when '1' then '待处理' when '2' then '处理中' when '3' then '已处理' else '--' end leave_status,
		case a.do_status when '0' then '--' when '1' then '未执行' when '2' then '执行中' when '3' then '部分成功' when '4' then '全部失败' when '5' then '全部成功' when '6' then '无需处理' else '--' end do_status,
		case a.do_status_contract when '0' then '--' when '1' then '无需处理' when '2' then '待发起' when '3' then '发起失败'
when '4' then '待审核' when '8' then '审核通过' when '10' then '审核不通过' else '--' end do_status_contract,
		case a.do_status_social when '0' then '--' when '1' then '无需处理' when '2' then '待派单' when '3' then '发起失败'
when '4' then '待审核' when '5' then '待办理' when '6' then '部分办理失败' when '7' then '办理中' when '8' then '办理成功'
when '9' then '办理失败' when '10' then '审核不通过' else '--' end do_status_social,
		case a.do_status_fund when '0' then '--' when '1' then '无需处理' when '2' then '待派单' when '3' then '发起失败'
when '4' then '待审核' when '5' then '待办理' when '8' then '办理成功' when '9' then '办理失败' when '10' then '审核不通过' else '--' end do_status_fund,
		case a.do_status_insurance when '0' then '--' when '1' then '无需处理' when '2' then '待派单' when '3' then '发起失败'
when '4' then '待减员' when '5' then '待办理' when '6' then '减员中' when '8' then '减员成功' when '9' then '减员退回' else '--' end do_status_insurance,
		case a.do_status_project when '0' then '--' when '1' then '无需处理' when '2' then '待减项' when '8' then '减项成功' when '9' then '减项失败' else '--' end do_status_project,
		case a.do_status_emp when '0' then '--' when '1' then '无需处理' when '2' then '待减档' when '8' then '减档成功' when '9' then '减档失败' else '--' end do_status_emp,
		b.employee_name,
		b.emp_idcard,
		DATE_FORMAT(b.join_leave_date,'%Y-%m-%d') join_leave_date,
		b.dept_name,
		b.dept_no,
		b.customer_username_new
	</sql>

	<!-- 离职确认列表 -->
	<sql id="Base_Confirm_Table_List">
		a.id,a.do_type,a.emp_project_id,a.emp_id,a.contract_id,a.insurance_id,a.fund_id,a.social_id,
		a.fail_social_user,
		a.fail_fund_user,
		a.fail_insurance_user,
		a.fail_contract_user,
		a.fail_emp_user,
		a.fail_emp_project_user,
		a.fail_social_remark,
		a.fail_fund_remark,
		a.fail_insurance_remark,
		a.fail_contract_remark,
		a.fail_emp_remark,
		a.fail_emp_project_remark,
		a.fail_social_time,
		a.fail_fund_time,
		a.fail_insurance_time,
		a.fail_contract_time,
		a.fail_emp_time,
		a.fail_emp_project_time,
		<include refid="Base_Confirm_Export_List"></include>
	</sql>

	<sql id="Base_Confirm_Export_List">
		a.confirm_user,
		DATE_FORMAT(a.confirm_time,'%Y-%m-%d %H:%i:%s') confirm_time,
		a.link_port,
		<include refid="Base_Export_List"></include>
	</sql>

	<!-- 离职待办列表-->
	<select id="getEmployeeRegistrationLeaveTablePage" resultMap="tableAndExportMap">
		SELECT
			<include refid="Base_Table_List"/>
		FROM employee_registration_leave a
		left join employee_registration b on a.id=b.id
		<where>
			1=1
			<include refid="employeeRegistrationLeave_where"/>
		</where>
		ORDER BY b.create_time desc,b.id asc
	</select>

	<!-- 离职待办列表2000条-->
	<select id="getEmployeeRegistrationLeaveTablePage2000" resultMap="tableAndExportMap">
		SELECT
			<include refid="Base_Table_List"/>
		FROM employee_registration_leave a
		left join employee_registration b on a.id=b.id
		<where>
			1=1
			<include refid="employeeRegistrationLeave_where"/>
			<if test="employeeRegistrationLeave != null">
				<if test="employeeRegistrationLeave.processStatusArray != null and employeeRegistrationLeave.processStatusArray.length > 0">
					AND a.leave_status in
					<foreach item="statusStr" index="index" collection="employeeRegistrationLeave.processStatusArray" open="("
							 separator="," close=")">
						#{statusStr}
					</foreach>
				</if>
				<if test='employeeRegistrationLeave.isDispatch != null and employeeRegistrationLeave.isDispatch.trim() != ""
					and employeeRegistrationLeave.isDispatch.trim() == "1" '>
					and (
						(a.do_status_social is not null and a.do_status_social in ('2','3','6','9','10')) or
						(a.do_status_fund is not null and a.do_status_fund in ('2','3','9','10')) or
						(a.do_status_insurance is not null and a.do_status_insurance in ('2','3','9')) or
						(a.do_status_contract is not null and a.do_status_contract in ('2','3','10')) or
						(a.do_status_project is not null and a.do_status_project in ('2','9')) or
						(a.do_status_emp is not null and a.do_status_emp in ('2','9'))
					)
				</if>
			</if>
		</where>
		ORDER BY b.create_time desc,b.id asc
		limit 2000
	</select>
    <!--离职确认列表-->
    <select id="getEmployeeRegistrationLeaveConfirmTablePage" resultMap="tableAndExportMap">
        SELECT
        	<include refid="Base_Confirm_Table_List"/>
        FROM employee_registration_leave a
		left join employee_registration b on a.id=b.id
        <where>
			a.confirm_user is not null
			<include refid="employeeRegistrationLeave_where"/>
        </where>
		ORDER BY b.create_time desc,b.id asc
    </select>

	<!-- 离职待办导出-->
	<select id="getEmployeeRegistrationLeaveTableList" resultMap="tableAndExportMap">
		SELECT
			<include refid="Base_Export_List"/>
		FROM employee_registration_leave a
		left join employee_registration b on a.id=b.id
		<where>
			1=1
			<include refid="employeeRegistrationLeave_where"/>
		</where>
		ORDER BY b.create_time desc,b.id asc
		<if test="employeeRegistrationLeave != null">
			<if test="employeeRegistrationLeave.limitStart != null">
				limit #{employeeRegistrationLeave.limitStart},#{employeeRegistrationLeave.limitEnd}
			</if>
		</if>
	</select>
    <!--离职确认导出-->
    <select id="getEmployeeRegistrationLeaveConfirmTableList" resultMap="tableAndExportMap">
        SELECT
        	<include refid="Base_Confirm_Export_List"/>
        FROM employee_registration_leave a
		left join employee_registration b on a.id=b.id
        <where>
			a.confirm_user is not null
			<include refid="employeeRegistrationLeave_where"/>
        </where>
		ORDER BY b.create_time desc,b.id asc
		<if test="employeeRegistrationLeave != null">
			<if test="employeeRegistrationLeave.limitStart != null">
				limit #{employeeRegistrationLeave.limitStart},#{employeeRegistrationLeave.limitEnd}
			</if>
		</if>
    </select>

	<!-- 离职待办导出条数-->
	<select id="getEmployeeRegistrationLeaveTableCount" resultType="java.lang.Integer">
		SELECT
			count(1)
		FROM employee_registration_leave a
		left join employee_registration b on a.id=b.id
		<where>
			1=1
			<include refid="employeeRegistrationLeave_where"/>
		</where>
	</select>
    <!--离职确认导出条数-->
    <select id="getEmployeeRegistrationLeaveConfirmTableCount" resultType="java.lang.Integer">
        SELECT
        	count(1)
        FROM employee_registration_leave a
		left join employee_registration b on a.id=b.id
        <where>
			a.confirm_user is not null
			<include refid="employeeRegistrationLeave_where"/>
        </where>
    </select>

	<select id="selectLeaveListBySure" resultMap="employeeRegistrationLeaveMap" >
		SELECT
		<include refid="Base_Column_List"/>
		FROM employee_registration_leave a
		where a.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>

	<update id="doBatchSure">
		<if test="idList != null and idList.size() > 0">
			update employee_registration_leave  a
			set a.leave_status = '1'
			where
			a.id in
			<foreach collection="idList" item="param" index="index" open="(" close=")" separator=",">
				#{param}
			</foreach>
		</if>
	</update>

	<!-- 查找符合条件的需要派单的数据 -->
	<select id="selectLeaveListByDispatch" resultMap="employeeRegistrationLeaveMap">
		SELECT
			b.employee_name,
			b.emp_idcard,
			b.dept_name,
			b.dept_no,
			DATE_FORMAT(b.join_leave_date,'%Y-%m-%d') join_leave_date,
		       b.customer_username_new,
			<include refid="Base_Column_List"/>
		FROM employee_registration_leave a
		left join employee_registration b on a.id=b.id
		<where>
			a.leave_status in ('1','2')
			<if test="idList != null and idList.size() > 0">
				and a.id in
				<foreach collection="idList" item="param" index="index" open="(" close=")" separator=",">
					#{param}
				</foreach>
			</if>
		</where>
		ORDER BY b.customer_username_new asc,b.id asc
	</select>

	<!-- 查询需要办理的数据 -->
	<select id="selectLeaveListByHandleAndType" resultMap="employeeRegistrationLeaveMap">
		SELECT
		<include refid="Base_Column_List"/>
		FROM employee_registration_leave a
		<where> 1=1
			<if test="idList != null and idList.size() > 0">
				<if test="doType != null and doType.trim() != ''">
					 <if test='doType == "1" '>
						 and a.do_status_social not in ('0','1') and a.social_id in
					 </if>
					<if test='doType == "2" '>
						and a.do_status_fund not in ('0','1') and a.fund_id in
					 </if>
					<if test='doType == "4" '>
						and a.do_status_contract not in ('0','1') and a.contract_id in
					 </if>
					<if test='doType == "5" '>
						and a.do_status_project not in ('0','1') and a.emp_project_id in
					 </if>
					<if test='doType == "6" '>
						and a.do_status_emp not in ('0','1') and a.emp_id in
					 </if>
					<foreach collection="idList" item="param" index="index" open="(" close=")" separator=",">
						#{param}
					</foreach>
				</if>
			</if>
		</where>
	</select>

	<!-- 查询需要办理的商险数据 -->
	<select id="selectLeaveListByHandleAndInsuranceType" resultMap="employeeRegistrationLeaveMap">
		<if test="idList != null and idList.size() > 0">
			SELECT
				<include refid="Base_Column_List"/>
			FROM employee_registration_leave a
			JOIN employee_registration_leave_insurance i on a.id=i.leave_id
			where a.do_status_insurance not in ('0','1') and a.insurance_id is not null and i.insurance_id in
			<foreach collection="idList" item="param" index="index" open="(" close=")" separator=",">
				#{param}
			</foreach>
		</if>
	</select>

</mapper>
