<?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.yifu.archives.mapper.TEmployeeInfoMapper">

	<resultMap id="tEmployeeInfoMap" type="com.yifu.cloud.plus.v1.yifu.archives.entity.TEmployeeInfo">
		<id property="id" column="ID"/>
		<result property="empCode" column="EMP_CODE"/>
		<result property="empNatrue" column="EMP_NATRUE"/>
		<result property="empName" column="EMP_NAME"/>
		<result property="empIdcard" column="EMP_IDCARD"/>
		<result property="validityStart" column="VALIDITY_START"/>
		<result property="validityEnd" column="VALIDITY_END"/>
		<result property="empSex" column="EMP_SEX"/>
		<result property="empBirthday" column="EMP_BIRTHDAY"/>
		<result property="empAge" column="EMP_AGE"/>
		<result property="empMarriStatus" column="EMP_MARRI_STATUS"/>
		<result property="empNational" column="EMP_NATIONAL"/>
		<result property="politicalStatus" column="POLITICAL_STATUS"/>
		<result property="empEmail" column="EMP_EMAIL"/>
		<result property="empPhone" column="EMP_PHONE"/>
		<result property="idProvince" column="ID_PROVINCE"/>
		<result property="idCity" column="ID_CITY"/>
		<result property="idTown" column="ID_TOWN"/>
		<result property="empRegisType" column="EMP_REGIS_TYPE"/>
		<result property="fileProvince" column="FILE_PROVINCE"/>
		<result property="fileCity" column="FILE_CITY"/>
		<result property="fileTown" column="FILE_TOWN"/>
		<result property="isCollege" column="IS_COLLEGE"/>
		<result property="hignEducation" column="HIGN_EDUCATION"/>
		<result property="school" column="SCHOOL"/>
		<result property="major" column="MAJOR"/>
		<result property="admissionDate" column="ADMISSION_DATE"/>
		<result property="gradutionDate" column="GRADUTION_DATE"/>
		<result property="remark" column="REMARK"/>
		<result property="fileSource" column="FILE_SOURCE"/>
		<result property="status" column="STATUS"/>
		<result property="fileStatus" column="FILE_STATUS"/>
		<result property="projectNum" column="PROJECT_NUM"/>
		<result property="contractStatus" column="CONTRACT_STATUS"/>
		<result property="insuranceStatus" column="INSURANCE_STATUS"/>
		<result property="socialStatus" column="SOCIAL_STATUS"/>
		<result property="fundStatus" column="FUND_STATUS"/>
		<result property="salaryStatus" column="SALARY_STATUS"/>
		<result property="deleteFlag" column="DELETE_FLAG"/>
		<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="leaveUser" column="LEAVE_USER"/>
		<result property="leaveUserName" column="LEAVE_USER_NAME"/>
		<result property="leaveTime" column="LEAVE_TIME"/>
		<result property="leaveReason" column="LEAVE_REASON"/>
		<result property="leaveRemark" column="LEAVE_REMARK"/>
		<result property="contactAddress" column="CONTACT_ADDRESS"/>

		<result property="firstWorkFlag" column="FIRST_WORK_FLAG"/>
		<result property="haveQualification" column="HAVE_QUALIFICATION"/>

		<result property="bankName" column="BANK_NAME"/>
		<result property="bankSubName" column="BANK_SUB_NAME"/>
		<result property="bankNo" column="BANK_NO"/>
		<result property="invoiceTitle" column="INVOICE_TITLE"/>
		<result property="bankProvince" column="BANK_PROVINCE"/>
		<result property="bankCity" column="BANK_CITY"/>
		<result property="taxMonth" column="TAX_MONTH"/>
		<result property="contactProvince" column="CONTACT_PROVINCE"/>
		<result property="contactCity" column="CONTACT_CITY"/>
		<result property="contactTown" column="CONTACT_TOWN"/>

	</resultMap>

	<resultMap id="tEmployeeExportMap" type="com.yifu.cloud.plus.v1.yifu.archives.vo.EmployeeExportVO">
		<id property="id" column="ID"/>
		<result property="empCode" column="EMP_CODE"/>
		<result property="empNatrue" column="EMP_NATRUE"/>
		<result property="empName" column="EMP_NAME"/>
		<result property="empIdcard" column="EMP_IDCARD"/>
		<result property="empAge" column="EMP_AGE"/>
		<result property="validityStart" column="VALIDITY_START"/>
		<result property="validityEnd" column="VALIDITY_END"/>
		<result property="empMarriStatus" column="EMP_MARRI_STATUS"/>
		<result property="empNational" column="EMP_NATIONAL"/>
		<result property="politicalStatus" column="POLITICAL_STATUS"/>
		<result property="empPhone" column="EMP_PHONE"/>
		<result property="empEmail" column="EMP_EMAIL"/>
		<result property="idProvince" column="ID_PROVINCE"/>
		<result property="idCity" column="ID_CITY"/>
		<result property="idTown" column="ID_TOWN"/>
		<result property="empRegisType" column="EMP_REGIS_TYPE"/>
		<result property="fileProvince" column="FILE_PROVINCE"/>
		<result property="fileCity" column="FILE_CITY"/>
		<result property="fileTown" column="FILE_TOWN"/>
		<result property="isCollege" column="IS_COLLEGE"/>
		<result property="hignEducation" column="HIGN_EDUCATION"/>
		<result property="school" column="SCHOOL"/>
		<result property="major" column="MAJOR"/>
		<result property="admissionDate" column="ADMISSION_DATE"/>
		<result property="gradutionDate" column="GRADUTION_DATE"/>
		<result property="remark" column="REMARK"/>
		<result property="fileSource" column="FILE_SOURCE"/>
		<result property="isProjects" column="IS_PROJECTS"/>
		<result property="projectNum" column="PROJECT_NUM"/>
		<result property="deptName" column="DEPT_NAME"/>
		<result property="deptNo" column="DEPT_NO"/>
		<result property="contractStatus" column="CONTRACT_STATUS"/>
		<result property="insuranceStatus" column="INSURANCE_STATUS"/>
		<result property="socialStatus" column="SOCIAL_STATUS"/>
		<result property="fundStatus" column="FUND_STATUS"/>
		<result property="salaryStatus" column="SALARY_STATUS"/>
		<result property="createName" column="CREATE_NAME"/>
		<result property="createTime" column="CREATE_TIME"/>
		<result property="contactProvince" column="CONTACT_PROVINCE"/>
		<result property="contactCity" column="CONTACT_CITY"/>
		<result property="contactTown" column="CONTACT_TOWN"/>
		<result property="contactAddress" column="CONTACT_ADDRESS"/>

		<result property="unitName" column="UNIT_NAME"/>
		<result property="unitNo" column="UNIT_NO"/>
		<result property="leaveUserName" column="LEAVE_USER_NAME"/>
		<result property="leaveTime" column="LEAVE_TIME"/>
		<result property="leaveReason" column="LEAVE_REASON"/>
		<result property="leaveRemark" column="LEAVE_REMARK"/>
		<result property="status" column="STATUS"/>
		<result property="firstWorkFlag" column="FIRST_WORK_FLAG"/>
		<result property="haveQualification" column="HAVE_QUALIFICATION"/>
	</resultMap>

	<resultMap id="tEmployeeLeaveExportMap" type="com.yifu.cloud.plus.v1.yifu.archives.vo.EmployeeLeaveExportVO">
		<result property="empCode" column="EMP_CODE"/>
		<result property="empNatrue" column="EMP_NATRUE"/>
		<result property="empName" column="EMP_NAME"/>
		<result property="empIdcard" column="EMP_IDCARD"/>
		<result property="empPhone" column="EMP_PHONE"/>
		<result property="fileProvince" column="FILE_PROVINCE"/>
		<result property="fileCity" column="FILE_CITY"/>
		<result property="fileTown" column="FILE_TOWN"/>
		<result property="deptName" column="DEPT_NAME"/>
		<result property="deptNo" column="DEPT_NO"/>
		<result property="unitName" column="UNIT_NAME"/>
		<result property="leaveUserName" column="LEAVE_USER_NAME"/>
		<result property="leaveTime" column="LEAVE_TIME"/>
	</resultMap>
	<resultMap id="dispatchEmpVoMap" type="com.yifu.cloud.plus.v1.yifu.archives.vo.DispatchEmpVo">
		<id property="id" column="ID"/>
		<result property="empCode" column="EMP_CODE"/>
		<result property="empNatrue" column="EMP_NATRUE"/>
		<result property="empName" column="EMP_NAME"/>
		<result property="empIdcard" column="EMP_IDCARD"/>
		<result property="empNational" column="EMP_NATIONAL"/>
		<result property="empPhone" column="EMP_PHONE"/>
		<result property="idProvince" column="ID_PROVINCE"/>
		<result property="idCity" column="ID_CITY"/>
		<result property="idTown" column="ID_TOWN"/>
		<result property="empRegisType" column="EMP_REGIS_TYPE"/>
		<result property="fileProvince" column="FILE_PROVINCE"/>
		<result property="fileCity" column="FILE_CITY"/>
		<result property="fileTown" column="FILE_TOWN"/>
		<result property="hignEducation" column="HIGN_EDUCATION"/>
		<result property="fileStatus" column="FILE_STATUS"/>
		<result property="projectStatus" column="PROJECT_STATUS"/>


		<result property="contractName" column="CONTRACT_NAME"/>
		<result property="contractType" column="CONTRACT_TYPE"/>
		<result property="contractStart" column="CONTRACT_START"/>
		<result property="contractEnd" column="CONTRACT_END"/>
		<result property="contractTerm" column="CONTRACT_TERM"/>
		<result property="contractSubName" column="CONTRACT_SUB_NAME"/>
		<result property="contractId" column="CONTRACT_ID"/>

		<result property="unitName" column="UNIT_NAME"/>
		<result property="unitNo" column="UNIT_NO"/>
		<result property="deptId" column="DEPT_ID"/>
		<result property="deptName" column="DEPT_NAME"/>
		<result property="deptNo" column="DEPT_NO"/>
		<result property="post" column="POST"/>
		<result property="empNo" column="EMP_NO"/>
		<result property="tryPeriod" column="TRY_PERIOD"/>
		<result property="school" column="school"/>

	</resultMap>
	<sql id="baseParam">
		a.ID,
		a.EMP_CODE,
		a.EMP_NATRUE,
		a.EMP_NAME,
		a.EMP_IDCARD,
		a.VALIDITY_START,
		a.VALIDITY_END,
		a.EMP_SEX,
		a.EMP_BIRTHDAY,
		a.EMP_AGE,
		a.EMP_MARRI_STATUS,
		a.EMP_NATIONAL,
		a.POLITICAL_STATUS,
		a.EMP_EMAIL,
		a.EMP_PHONE,
		a.ID_PROVINCE,
		a.ID_CITY,
		a.ID_TOWN,
		a.EMP_REGIS_TYPE,
		a.FILE_PROVINCE,
		a.FILE_CITY,
		a.FILE_TOWN,
		a.IS_COLLEGE,
		a.HIGN_EDUCATION,
		a.SCHOOL,
		a.MAJOR,
		a.ADMISSION_DATE,
		a.GRADUTION_DATE,
		a.REMARK,
		a.FILE_SOURCE,
		a.STATUS,
		a.FILE_STATUS,
		a.PROJECT_NUM,
		a.CONTRACT_STATUS,
		a.INSURANCE_STATUS,
		a.SOCIAL_STATUS,
		a.FUND_STATUS,
		a.SALARY_STATUS,
		a.DELETE_FLAG,
		a.CREATE_BY,
		a.CREATE_NAME,
		a.CREATE_TIME,
		a.UPDATE_BY,
		a.UPDATE_TIME,
		a.LEAVE_USER,
		a.LEAVE_USER_NAME,
		a.LEAVE_TIME,
		a.LEAVE_REASON,
		a.LEAVE_REMARK,
		a.CONTACT_ADDRESS,
		a.FIRST_WORK_FLAG,
		a.HAVE_QUALIFICATION,

		a.BANK_NAME,
		a.BANK_SUB_NAME,
		a.BANK_NO,
		a.INVOICE_TITLE,
		a.BANK_PROVINCE,
		a.BANK_CITY,
		a.TAX_MONTH,
		a.CONTACT_PROVINCE,
		a.CONTACT_CITY,
		a.CONTACT_TOWN
	</sql>

	<sql id="Base_Export_List">
		a.EMP_CODE,
		a.EMP_NATRUE,
		a.EMP_NAME,
		a.EMP_IDCARD,
		a.VALIDITY_START,
		a.VALIDITY_END,
		a.EMP_MARRI_STATUS,
		a.EMP_NATIONAL,
		a.POLITICAL_STATUS,
		a.EMP_PHONE,
		a.EMP_EMAIL,
		a.ID_PROVINCE,
		a.ID_CITY,
		a.ID_TOWN,
		a.EMP_REGIS_TYPE,
		a.FILE_PROVINCE,
		a.FILE_CITY,
		a.FILE_TOWN,
		a.IS_COLLEGE,
		a.HIGN_EDUCATION,
		a.SCHOOL,
		a.MAJOR,
		a.ADMISSION_DATE,
		a.GRADUTION_DATE,
		a.REMARK,
		a.FILE_SOURCE,
		a.PROJECT_NUM,
		a.CONTRACT_STATUS,
		a.INSURANCE_STATUS,
		a.SOCIAL_STATUS,
		a.FUND_STATUS,
		a.SALARY_STATUS,
		a.CREATE_NAME,
		a.CREATE_TIME,
		a.STATUS,
		a.CONTACT_ADDRESS
	</sql>

	<update id="updateEducationOfEmp">
		update t_employee_info
		<trim prefix="set" suffixOverrides=",">
			<if test="education.isCollege != null">is_college=#{education.isCollege},</if>
			<if test="education.highIdentification != null">hign_education=#{education.highIdentification},</if>
			<if test="education.school != null">school=#{education.school},</if>
			<if test="education.major != null">major=#{education.major},</if>
			<if test="education.entryDate != null">admission_date=#{education.entryDate},</if>
			<if test="education.gradutionDate != null">gradution_date=#{education.gradutionDate},</if>
		</trim>
		WHERE id=#{education.empId}
	</update>

	<sql id="employeeInfo_where">
		<if test="tEmployeeInfo != null">
			<if test="tEmployeeInfo.idList != null">
				AND a.id in
				<foreach item="idStr" index="index" collection="tEmployeeInfo.idList" open="(" separator="," close=")">
					#{idStr}
				</foreach>
			</if>
			<if test="tEmployeeInfo.idList == null">
				<if test="tEmployeeInfo.id != null and tEmployeeInfo.id.trim() != ''">
					AND a.ID = #{tEmployeeInfo.id}
				</if>
				<if test="tEmployeeInfo.empCode != null and tEmployeeInfo.empCode.trim() != ''">
					AND a.EMP_CODE = #{tEmployeeInfo.empCode}
				</if>
				<if test="tEmployeeInfo.empNatrue != null and tEmployeeInfo.empNatrue.trim() != ''">
					AND a.EMP_NATRUE = #{tEmployeeInfo.empNatrue}
				</if>
				<if test="tEmployeeInfo.empName != null and tEmployeeInfo.empName.trim() != ''">
					AND a.EMP_NAME like concat('%',#{tEmployeeInfo.empName},'%')
				</if>
				<if test="tEmployeeInfo.empIdcard != null and tEmployeeInfo.empIdcard.trim() != ''">
					AND a.EMP_IDCARD like concat('%',#{tEmployeeInfo.empIdcard},'%')
				</if>
				<if test="tEmployeeInfo.nameOrIdcard != null and tEmployeeInfo.nameOrIdcard.trim() != ''">
					AND (
					a.EMP_NAME  like concat(#{tEmployeeInfo.nameOrIdcard},'%')
					or
					a.EMP_IDCARD  like concat(#{tEmployeeInfo.nameOrIdcard},'%')
					)
				</if>
				<if test="tEmployeeInfo.validityStart != null">
					AND a.VALIDITY_START = #{tEmployeeInfo.validityStart}
				</if>
				<if test="tEmployeeInfo.validityEnd != null">
					AND a.VALIDITY_END = #{tEmployeeInfo.validityEnd}
				</if>
				<if test="tEmployeeInfo.empSex != null and tEmployeeInfo.empSex.trim() != ''">
					AND a.EMP_SEX = #{tEmployeeInfo.empSex}
				</if>
				<if test="tEmployeeInfo.empBirthday != null">
					AND a.EMP_BIRTHDAY = #{tEmployeeInfo.empBirthday}
				</if>
				<if test="tEmployeeInfo.empAgeStart >0">
					AND a.EMP_AGE >= #{tEmployeeInfo.empAgeStart}
				</if>
				<if test="tEmployeeInfo.empAgeEnd >0">
					AND a.EMP_AGE <![CDATA[ <= ]]> #{tEmployeeInfo.empAgeEnd}
				</if>
				<if test="tEmployeeInfo.empMarriStatus != null and tEmployeeInfo.empMarriStatus.trim() != ''">
					AND a.EMP_MARRI_STATUS = #{tEmployeeInfo.empMarriStatus}
				</if>
				<if test="tEmployeeInfo.empNational != null and tEmployeeInfo.empNational.trim() != ''">
					AND a.EMP_NATIONAL = #{tEmployeeInfo.empNational}
				</if>
				<if test="tEmployeeInfo.politicalStatus != null and tEmployeeInfo.politicalStatus.trim() != ''">
					AND a.POLITICAL_STATUS = #{tEmployeeInfo.politicalStatus}
				</if>
				<if test="tEmployeeInfo.empEmail != null and tEmployeeInfo.empEmail.trim() != ''">
					AND a.EMP_EMAIL = #{tEmployeeInfo.empEmail}
				</if>
				<if test="tEmployeeInfo.empPhone != null and tEmployeeInfo.empPhone.trim() != ''">
					AND a.EMP_PHONE = #{tEmployeeInfo.empPhone}
				</if>
				<if test="tEmployeeInfo.idProvince != null">
					AND a.ID_PROVINCE = #{tEmployeeInfo.idProvince}
				</if>
				<if test="tEmployeeInfo.idCity != null">
					AND a.ID_CITY = #{tEmployeeInfo.idCity}
				</if>
				<if test="tEmployeeInfo.idTown != null">
					AND a.ID_TOWN = #{tEmployeeInfo.idTown}
				</if>
				<if test="tEmployeeInfo.empRegisType != null and tEmployeeInfo.empRegisType.trim() != ''">
					AND a.EMP_REGIS_TYPE = #{tEmployeeInfo.empRegisType}
				</if>
				<if test="tEmployeeInfo.fileProvince != null">
					AND a.FILE_PROVINCE = #{tEmployeeInfo.fileProvince}
				</if>
				<if test="tEmployeeInfo.fileCity != null">
					AND a.FILE_CITY = #{tEmployeeInfo.fileCity}
				</if>
				<if test="tEmployeeInfo.fileTown != null">
					AND a.FILE_TOWN = #{tEmployeeInfo.fileTown}
				</if>
				<if test="tEmployeeInfo.isCollege != null">
					AND a.IS_COLLEGE = #{tEmployeeInfo.isCollege}
				</if>
				<if test="tEmployeeInfo.hignEducation != null and tEmployeeInfo.hignEducation.trim() != ''">
					AND a.HIGN_EDUCATION = #{tEmployeeInfo.hignEducation}
				</if>
				<if test="tEmployeeInfo.school != null and tEmployeeInfo.school.trim() != ''">
					AND a.SCHOOL = #{tEmployeeInfo.school}
				</if>
				<if test="tEmployeeInfo.major != null and tEmployeeInfo.major.trim() != ''">
					AND a.MAJOR = #{tEmployeeInfo.major}
				</if>
				<if test="tEmployeeInfo.admissionDate != null">
					AND a.ADMISSION_DATE = #{tEmployeeInfo.admissionDate}
				</if>
				<if test="tEmployeeInfo.gradutionDate != null">
					AND a.GRADUTION_DATE = #{tEmployeeInfo.gradutionDate}
				</if>
				<if test="tEmployeeInfo.remark != null and tEmployeeInfo.remark.trim() != ''">
					AND a.REMARK = #{tEmployeeInfo.remark}
				</if>
				<if test="tEmployeeInfo.fileSource != null and tEmployeeInfo.fileSource.trim() != ''">
					AND a.FILE_SOURCE = #{tEmployeeInfo.fileSource}
				</if>
				<if test="tEmployeeInfo.status != null">
					AND a.STATUS = #{tEmployeeInfo.status}
				</if>
				<if test="tEmployeeInfo.fileStatus != null">
					AND a.FILE_STATUS = #{tEmployeeInfo.fileStatus}
				</if>
				<if test="tEmployeeInfo.projectNum != null">
					<if test="tEmployeeInfo.projectNum == 1">
						AND (a.PROJECT_NUM is null or a.PROJECT_NUM <![CDATA[ <= ]]> 1)
					</if>
					<if test="tEmployeeInfo.projectNum == 2">
						AND a.PROJECT_NUM > 1
					</if>
				</if>
				<if test="tEmployeeInfo.contractStatus != null">
					AND a.CONTRACT_STATUS = #{tEmployeeInfo.contractStatus}
				</if>
				<if test="tEmployeeInfo.insuranceStatus != null">
					AND a.INSURANCE_STATUS = #{tEmployeeInfo.insuranceStatus}
				</if>
				<if test="tEmployeeInfo.socialStatus != null">
					AND a.SOCIAL_STATUS = #{tEmployeeInfo.socialStatus}
				</if>
				<if test="tEmployeeInfo.fundStatus != null">
					AND a.FUND_STATUS = #(${tEmployeeInfo.fundStatus}
				</if>
				<if test="tEmployeeInfo.salaryStatus != null">
					AND a.SALARY_STATUS = #{tEmployeeInfo.salaryStatus}
				</if>
				<if test="tEmployeeInfo.deleteFlag != null and tEmployeeInfo.deleteFlag.trim() != ''">
					AND a.DELETE_FLAG = #{tEmployeeInfo.deleteFlag}
				</if>
				<if test="tEmployeeInfo.createBy != null and tEmployeeInfo.createBy.trim() != ''">
					AND a.CREATE_BY = #{tEmployeeInfo.createBy}
				</if>
				<if test="tEmployeeInfo.createName != null and tEmployeeInfo.createName.trim() != ''">
					AND a.CREATE_NAME = #{tEmployeeInfo.createName}
				</if>
				<if test="tEmployeeInfo.createTime != null">
					AND a.CREATE_TIME = #{tEmployeeInfo.createTime}
				</if>
				<if test="tEmployeeInfo.updateBy != null and tEmployeeInfo.updateBy.trim() != ''">
					AND a.UPDATE_BY = #{tEmployeeInfo.updateBy}
				</if>
				<if test="tEmployeeInfo.updateTime != null">
					AND a.UPDATE_TIME = #{tEmployeeInfo.updateTime}
				</if>
				<if test="tEmployeeInfo.leaveUser != null and tEmployeeInfo.leaveUser.trim() != ''">
					AND a.LEAVE_USER = #{tEmployeeInfo.leaveUser}
				</if>
				<if test="tEmployeeInfo.leaveUserName != null and tEmployeeInfo.leaveUserName.trim() != ''">
					AND a.LEAVE_USER_NAME = #{tEmployeeInfo.leaveUserName}
				</if>
				<if test="tEmployeeInfo.leaveTime != null">
					AND a.LEAVE_TIME = #{tEmployeeInfo.leaveTime}
				</if>
				<if test="tEmployeeInfo.leaveTimeStart != null">
					AND a.LEAVE_TIME >= #{tEmployeeInfo.leaveTimeStart}
				</if>
				<if test="tEmployeeInfo.leaveTimeEnd != null">
					AND a.LEAVE_TIME <![CDATA[ <= ]]> #{tEmployeeInfo.leaveTimeEnd}
				</if>
				<if test="tEmployeeInfo.leaveReason != null and tEmployeeInfo.leaveReason.trim() != ''">
					AND a.LEAVE_REASON = #{tEmployeeInfo.leaveReason}
				</if>
				<if test="tEmployeeInfo.leaveRemark != null and tEmployeeInfo.leaveRemark.trim() != ''">
					AND a.LEAVE_REMARK = #{tEmployeeInfo.leaveRemark}
				</if>
				<if test="tEmployeeInfo.contactAddress != null and tEmployeeInfo.contactAddress.trim() != ''">
					AND a.CONTACT_ADDRESS = #{tEmployeeInfo.contactAddress}
				</if>

				<if test="tEmployeeInfo.empNatureArray != null and tEmployeeInfo.empNatureArray.length > 0">
					AND a.EMP_NATRUE in
					<foreach item="idStr" index="index" collection="tEmployeeInfo.empNatureArray" open="(" separator=","
							 close=")">
						#{idStr}
					</foreach>
				</if>
				<if test="tEmployeeInfo.contractStatusArray != null and tEmployeeInfo.contractStatusArray.length > 0">
					AND a.CONTRACT_STATUS in
					<foreach item="idStr" index="index" collection="tEmployeeInfo.contractStatusArray" open="("
							 separator="," close=")">
						#{idStr}
					</foreach>
				</if>
				<if test="tEmployeeInfo.insuranceStatusArray != null and tEmployeeInfo.insuranceStatusArray.length > 0">
					AND a.INSURANCE_STATUS in
					<foreach item="idStr" index="index" collection="tEmployeeInfo.insuranceStatusArray" open="("
							 separator="," close=")">
						#{idStr}
					</foreach>
				</if>
				<if test="tEmployeeInfo.socialStatusArray != null and tEmployeeInfo.socialStatusArray.length > 0">
					AND a.SOCIAL_STATUS in
					<foreach item="idStr" index="index" collection="tEmployeeInfo.socialStatusArray" open="("
							 separator="," close=")">
						#{idStr}
					</foreach>
				</if>
				<if test="tEmployeeInfo.fundStatusArray != null and tEmployeeInfo.fundStatusArray.length > 0">
					AND a.FUND_STATUS in
					<foreach item="idStr" index="index" collection="tEmployeeInfo.fundStatusArray" open="("
							 separator="," close=")">
						#{idStr}
					</foreach>
				</if>
				<if test="tEmployeeInfo.createTimeStart != null">
					AND a.CREATE_TIME <![CDATA[ >= ]]> #{tEmployeeInfo.createTimeStart}
				</if>
				<if test="tEmployeeInfo.createTimeEnd != null">
					AND a.CREATE_TIME <![CDATA[ <= ]]> #{tEmployeeInfo.createTimeEnd}
				</if>
			</if>
		</if>
	</sql>

	<!-- hgw 分页查询 -->
	<select id="getPage" resultMap="tEmployeeInfoMap">
		select
		<include refid="baseParam"/>
		from t_employee_info a
		<!--<if test="tEmployeeInfo.authSql != null and tEmployeeInfo.authSql.contains('b.DEPT_ID') ">
			left join t_employee_project b on a.id=b.EMP_ID and b.DELETE_FLAG = '0'
		</if>-->
		where a.DELETE_FLAG = '0'
		<include refid="employeeInfo_where"/>

		<if test="tEmployeeInfo.authSql != null and tEmployeeInfo.authSql.trim() != ''">
			and
			a.id in (
				select  b.emp_id from  t_employee_project  b
				where
			b.DELETE_FLAG = '0'
			${tEmployeeInfo.authSql}
			)
		</if>
		order by a.CREATE_TIME desc
	</select>
	<!-- fxj 2024-08-23 身份证/姓名 下拉选择 查询 在职和离职数据 -->
	<select id="getPageAsso" resultMap="tEmployeeInfoMap">
		select
		a.EMP_NAME,
		a.EMP_IDCARD
		from t_employee_info a
		where 1=1 and a.delete_flag = '0'
		<if test="tEmployeeInfo != null">
			<if test="tEmployeeInfo.empName != null and tEmployeeInfo.empName.trim() != ''">
				AND a.EMP_NAME like concat('%',#{tEmployeeInfo.empName},'%')
			</if>
			<if test="tEmployeeInfo.empIdcard != null and tEmployeeInfo.empIdcard.trim() != ''">
				AND a.EMP_IDCARD like concat('%',#{tEmployeeInfo.empIdcard},'%')
			</if>
		</if>
		<if test="tEmployeeInfo.nameOrIdcard != null and tEmployeeInfo.nameOrIdcard.trim() != ''">
			AND (
			a.EMP_NAME  like concat(#{tEmployeeInfo.nameOrIdcard},'%')
			or
			a.EMP_IDCARD  like concat(#{tEmployeeInfo.nameOrIdcard},'%')
			)
		</if>
		order by a.CREATE_TIME desc
	</select>

	<!-- hgw 分页查询 -->
	<select id="getLeavePage" resultMap="tEmployeeExportMap">
		SELECT
		c.ID,
		c.EMP_CODE,
		c.EMP_NATRUE,
		c.EMP_NAME,
		c.EMP_IDCARD,
		c.EMP_PHONE,
		c.FILE_PROVINCE,
		c.FILE_CITY,
		c.FILE_TOWN,
		c.LEAVE_TIME,
		c.LEAVE_USER_NAME,
		c.CREATE_TIME,
		b.DEPT_ID,
		b.EMP_ID,
		b.UNIT_NAME,
		b.DEPT_NAME,
		b.DEPT_NO
		FROM
		(
		SELECT
		a.ID,
		a.EMP_CODE,
		a.EMP_NATRUE,
		a.EMP_NAME,
		a.EMP_IDCARD,
		a.EMP_PHONE,
		a.FILE_PROVINCE,
		a.FILE_CITY,
		a.FILE_TOWN,
		a.LEAVE_TIME,
		a.LEAVE_USER_NAME,
		a.CREATE_TIME
		FROM
		t_employee_info a
		WHERE
		a.DELETE_FLAG = '0'
		<include refid="employeeInfo_where"/>
		<if test="null != page and null != size">
			limit #{page},#{size}
		</if>
		) c
		LEFT JOIN (
		SELECT
		f.DEPT_ID,
		f.EMP_ID,
		GROUP_CONCAT(DISTINCT f.UNIT_NAME) UNIT_NAME,
		GROUP_CONCAT(DISTINCT f.DEPT_NAME) DEPT_NAME,
		GROUP_CONCAT(DISTINCT f.DEPT_NO) DEPT_NO
		FROM
		t_employee_project f
		WHERE
		f.DELETE_FLAG = '0'
		GROUP BY f.EMP_ID
		) b ON c.id = b.EMP_ID
		where 1 = 1
		<if test="tEmployeeInfo.unitName != null and tEmployeeInfo.unitName.trim() != ''">
			AND b.UNIT_NAME like concat('%',#{tEmployeeInfo.unitName},'%')
		</if>
		<if test="tEmployeeInfo.deptName != null and tEmployeeInfo.deptName.trim() != ''">
			AND b.DEPT_NAME like concat('%',#{tEmployeeInfo.deptName},'%')
		</if>
		<if test="tEmployeeInfo.deptNo != null and tEmployeeInfo.deptNo.trim() != ''">
			AND b.DEPT_NO like concat('%',#{tEmployeeInfo.deptNo},'%')
		</if>
		<if test="tEmployeeInfo.authSql != null and tEmployeeInfo.authSql.trim() != ''">
			${tEmployeeInfo.authSql}
		</if>
		ORDER BY c.CREATE_TIME DESC
	</select>

	<!-- hgw 分页查询 -->
	<select id="getLeaveIPage" resultMap="tEmployeeExportMap">
		SELECT
		c.ID,
		c.EMP_CODE,
		c.EMP_NATRUE,
		c.EMP_NAME,
		c.EMP_IDCARD,
		c.EMP_PHONE,
		c.FILE_PROVINCE,
		c.FILE_CITY,
		c.FILE_TOWN,
		c.LEAVE_TIME,
		c.LEAVE_USER_NAME,
		c.CREATE_TIME,
		b.DEPT_ID,
		b.EMP_ID,
		b.UNIT_NAME,
		b.DEPT_NAME,
		b.DEPT_NO
		FROM
		(
		SELECT
		a.ID,
		a.EMP_CODE,
		a.EMP_NATRUE,
		a.EMP_NAME,
		a.EMP_IDCARD,
		a.EMP_PHONE,
		a.FILE_PROVINCE,
		a.FILE_CITY,
		a.FILE_TOWN,
		a.LEAVE_TIME,
		a.LEAVE_USER_NAME,
		a.CREATE_TIME
		FROM
		t_employee_info a
		WHERE
		a.DELETE_FLAG = '0'
		<include refid="employeeInfo_where"/>
		) c
		LEFT JOIN (
		SELECT
		f.DEPT_ID,
		f.EMP_ID,
		GROUP_CONCAT(DISTINCT f.UNIT_NAME) UNIT_NAME,
		GROUP_CONCAT(DISTINCT f.DEPT_NAME) DEPT_NAME,
		GROUP_CONCAT(DISTINCT f.DEPT_NO) DEPT_NO
		FROM
		t_employee_project f
		WHERE
		f.DELETE_FLAG = '0'
		GROUP BY f.EMP_ID
		) b ON c.id = b.EMP_ID
		where 1 = 1
		<if test="tEmployeeInfo.unitName != null and tEmployeeInfo.unitName.trim() != ''">
			AND b.UNIT_NAME like concat('%',#{tEmployeeInfo.unitName},'%')
		</if>
		<if test="tEmployeeInfo.deptName != null and tEmployeeInfo.deptName.trim() != ''">
			AND b.DEPT_NAME like concat('%',#{tEmployeeInfo.deptName},'%')
		</if>
		<if test="tEmployeeInfo.deptNo != null and tEmployeeInfo.deptNo.trim() != ''">
			AND b.DEPT_NO like concat('%',#{tEmployeeInfo.deptNo},'%')
		</if>
		<if test="tEmployeeInfo.authSql != null and tEmployeeInfo.authSql.trim() != ''">
			${tEmployeeInfo.authSql}
		</if>
		ORDER BY c.CREATE_TIME DESC
	</select>

	<select id="getLeavePageCount" resultType="java.lang.Long">
		SELECT
		count(1)
		FROM t_employee_info a
		where 1=1
		and a.DELETE_FLAG = '0'
		<include refid="employeeInfo_where"/>
	</select>

	<!-- hgw list查询 -->
	<select id="getList" resultMap="tEmployeeInfoMap">
		select
		<include refid="baseParam"/>
		from t_employee_info a
		where a.DELETE_FLAG = '0'
		<include refid="employeeInfo_where"/>
		order by a.CREATE_TIME desc
	</select>

	<!-- hgw 根据身份证查询 -->
	<select id="getListByIdCard" resultMap="tEmployeeInfoMap">
		select
		<include refid="baseParam"/>
		from t_employee_info a
		where
		a.DELETE_FLAG = '0' and a.EMP_IDCARD in
		<foreach item="item" index="index" collection="idCardList" open="(" separator="," close=")">
			#{item}
		</foreach>
	</select>

	<!-- hgw 根据身份证查询 -->
	<select id="getListByDeptId" resultMap="tEmployeeInfoMap">
		select
			<include refid="baseParam"/>
		from t_employee_info a
		left join t_employee_project b on a.EMP_IDCARD = b.EMP_IDCARD
		where a.DELETE_FLAG = '0' and b.DEPT_ID = #{deptId}
		GROUP BY a.EMP_IDCARD
	</select>
	<select id="getListByDeptIdByLimit" resultMap="tEmployeeInfoMap">
		select
			<include refid="baseParam"/>
		from t_employee_info a
		left join t_employee_project b on a.EMP_IDCARD = b.EMP_IDCARD
		where a.DELETE_FLAG = '0' and b.DEPT_ID = #{deptId}
		limit #{limit1},#{limit2}
	</select>

	<select id="getListByIdCardList" resultMap="tEmployeeInfoMap">
		select
		<include refid="baseParam"/>
		from t_employee_info a
		where
		a.DELETE_FLAG = '0' and a.EMP_IDCARD in
		<foreach item="item" index="index" collection="idCardList" open="(" separator="," close=")">
			#{item}
		</foreach>
	</select>

	<!-- hgw 根据手机号查询 -->
	<select id="getListByPhone" resultMap="tEmployeeInfoMap">
		select
		<include refid="baseParam"/>
		from t_employee_info a
		where
		a.DELETE_FLAG = '0' and a.EMP_PHONE in
		<foreach item="item" index="index" collection="phoneList" open="(" separator="," close=")">
			#{item}
		</foreach>
	</select>

	<update id="updateExcelEmployeeInfo" parameterType="java.util.List">
		update t_employee_info
		<trim prefix="set" suffixOverrides=",">
			<trim prefix="EMP_NATRUE =case" suffix="end,">
				<foreach collection="list" item="i" index="index">
					<if test="i.empNatrue!=null">
						when ID=#{i.id} then #{i.empNatrue}
					</if>
				</foreach>
			</trim>
			<trim prefix="EMP_NAME =case" suffix="end,">
				<foreach collection="list" item="i" index="index">
					<if test="i.empName!=null">
						when ID=#{i.id} then #{i.empName}
					</if>
				</foreach>
			</trim>
			<trim prefix="EMP_IDCARD =case" suffix="end,">
				<foreach collection="list" item="i" index="index">
					<if test="i.empIdcard!=null">
						when ID=#{i.id} then #{i.empIdcard}
					</if>
				</foreach>
			</trim>
			<trim prefix="FILE_STATUS =case" suffix="end,">
				<foreach collection="list" item="i" index="index">
					when ID=#{i.id} then 0
				</foreach>
			</trim>
			<trim prefix="PROJECT_NUM =case" suffix="end,">
				<foreach collection="list" item="i" index="index">
					when ID=#{i.id} then #{i.projectNum}
				</foreach>
			</trim>
			<trim prefix="UPDATE_TIME =case" suffix="end,">
				<foreach collection="list" item="i" index="index">
					when ID=#{i.id} then now()
				</foreach>
			</trim>
			<trim prefix="UPDATE_BY =case" suffix="end,">
				<foreach collection="list" item="i" index="index">
					<if test="i.updateBy!=null">
						when ID=#{i.id} then #{i.updateBy}
					</if>
				</foreach>
			</trim>
		</trim>
		where
		<foreach collection="list" separator="or" item="i" index="index">
			ID=#{i.id}
		</foreach>
	</update>

	<!--tEmployeeInfo人员档案导出查询-->
	<select id="getTEmployeeExportList" resultMap="tEmployeeExportMap">
		select
		<include refid="Base_Export_List"/>,
		p.IS_PROJECTS ,
		p.DEPT_NAME,
		p.DEPT_NO,
		P.PROJECT_NUM
		from t_employee_info a
		LEFT JOIN view_project p on a.EMP_IDCARD=p.EMP_IDCARD
		<!--left join (
		select
			b.EMP_ID,
			GROUP_CONCAT(DISTINCT b.DEPT_NAME) DEPT_NAME,
			GROUP_CONCAT(DISTINCT b.DEPT_NO) DEPT_NO
		    from t_employee_project b where
		    1=1 and b.DELETE_FLAG = '0'
		<if test="tEmployeeInfo.authSql != null and tEmployeeInfo.authSql.trim() != ''">
			${tEmployeeInfo.authSql}
		</if>
		) b on a.id = b.EMP_ID-->
		<where>
			a.DELETE_FLAG = '0'
			<include refid="employeeInfo_where"/>

			<if test="tEmployeeInfo.authSql != null and tEmployeeInfo.authSql.trim() != ''">
				and
				a.id in (
				select  b.emp_id from  t_employee_project  b
				where
				b.DELETE_FLAG = '0'
				${tEmployeeInfo.authSql}
				)
			</if>
		</where>
		order by a.CREATE_TIME desc
	</select>

	<!--tEmployeeInfo离职档案导出查询-->
	<select id="getTEmployeeLeaveExportList" resultMap="tEmployeeExportMap">
		select * from (
		SELECT
		<include refid="Base_Export_List"/>
		,a.LEAVE_TIME
		,a.LEAVE_USER
		,a.LEAVE_USER_NAME
		,a.LEAVE_REASON,
		p.IS_PROJECTS ,
		p.DEPT_NAME,
		p.DEPT_NO,p.UNIT_NAME
		from t_employee_info a
		LEFT JOIN view_project_leave p on a.EMP_IDCARD=p.EMP_IDCARD
		<!--left join (
			select
			b.EMP_ID,
			GROUP_CONCAT(DISTINCT b.DEPT_NAME) DEPT_NAME,
			GROUP_CONCAT(DISTINCT b.DEPT_NO) DEPT_NO,
			GROUP_CONCAT(DISTINCT b.unit_name) UNIT_NAME,
			GROUP_CONCAT(DISTINCT b.unit_no) UNIT_NO
			from t_employee_project b where
			1=1 and b.DELETE_FLAG = '0'
			<if test="tEmployeeInfo.authSql != null and tEmployeeInfo.authSql.trim() != ''">
				${tEmployeeInfo.authSql}
			</if>
		) b on a.id = b.EMP_ID-->
		<where>
			a.DELETE_FLAG = '0'
			<include refid="employeeInfo_where"/>
			<if test="tEmployeeInfo.authSql != null and tEmployeeInfo.authSql.trim() != ''">
				and
				a.id in (
				select  b.emp_id from  t_employee_project  b
				where
				b.DELETE_FLAG = '0'
				${tEmployeeInfo.authSql}
				)
			</if>
		</where>
		order by a.CREATE_TIME desc
		) b
		where 1=1
		<if test="tEmployeeInfo.unitName != null and tEmployeeInfo.unitName.trim() != ''">
			AND b.UNIT_NAME like concat('%',#{tEmployeeInfo.unitName},'%')
		</if>
		<if test="tEmployeeInfo.deptName != null and tEmployeeInfo.deptName.trim() != ''">
			AND b.DEPT_NAME like concat('%',#{tEmployeeInfo.deptName},'%')
		</if>
		<if test="tEmployeeInfo.deptNo != null and tEmployeeInfo.deptNo.trim() != ''">
			AND b.DEPT_NO like concat('%',#{tEmployeeInfo.deptNo},'%')
		</if>
	</select>

	<!-- 获取当日最大的编码 -->
	<select id="getMaxEmpCode" resultType="java.lang.String">
		SELECT ifnull(max(right (e.EMP_CODE, 5)), 0) EMP_CODE
		FROM t_employee_info e
		where e.DELETE_FLAG = '0'
		  and e.CREATE_TIME > DATE_FORMAT(now(), '%Y-%m-%d')
	</select>

	<select id="getDispatchEmpVo" resultMap="dispatchEmpVoMap">
		select
		a.ID,a.EMP_CODE,a.EMP_NAME,
		a.EMP_IDCARD,a.EMP_NATIONAL,a.EMP_PHONE,a.ID_PROVINCE,a.ID_CITY,
		a.ID_TOWN,a.EMP_REGIS_TYPE,a.FILE_PROVINCE,a.FILE_CITY,a.FILE_TOWN,a.HIGN_EDUCATION,a.FILE_STATUS,a.CONTACT_ADDRESS,

		b.CONTRACT_NAME,b.CONTRACT_TYPE,
		b.CONTRACT_START,b.CONTRACT_END,b.CONTRACT_TERM,b.CONTRACT_SUB_NAME,b.CONTRACT_ID,

		c.UNIT_NAME,
		c.UNIT_NO,c.DEPT_ID,c.DEPT_NAME,c.DEPT_NO,c.POST,c.EMP_NO,c.PROJECT_STATUS,c.EMP_NATRUE,ifnull(c.TRY_PERIOD,b.TRY_PERIOD) TRY_PERIOD
		,a.SCHOOL
		from (
		select e.ID,e.EMP_CODE,e.EMP_NAME,e.EMP_IDCARD,e.EMP_NATIONAL,e.EMP_PHONE,e.ID_PROVINCE,e.ID_CITY,
		e.ID_TOWN,e.EMP_REGIS_TYPE,e.FILE_PROVINCE,e.FILE_CITY,e.FILE_TOWN,e.HIGN_EDUCATION,e.FILE_STATUS,e.CONTACT_ADDRESS
		,e.SCHOOL
		from t_employee_info e
		where 1=1 and e.DELETE_FLAG = '0'
		<if test="checkVo != null">
			<if test="checkVo.idCards != null">
				AND e.EMP_IDCARD in
				<foreach item="idStr" index="index" collection="checkVo.idCards" open="(" separator="," close=")">
					#{idStr}
				</foreach>
			</if>
		</if>
		) a
		left join (
		select p.UNIT_NAME,p.UNIT_NO,p.DEPT_ID,p.DEPT_NAME,p.DEPT_NO,p.POST,p.EMP_ID,p.EMP_NO,P.PROJECT_STATUS,p.EMP_NATRUE,p.TRY_PERIOD
		from t_employee_project p
		where 1=1 and p.DELETE_FLAG = '0'
		<if test="checkVo != null">
			<if test="checkVo.codes != null">
				AND p.DEPT_NO in
				<foreach item="code" index="index" collection="checkVo.codes" open="(" separator="," close=")">
					#{code}
				</foreach>
			</if>
		</if>
		) c on a.ID=c.EMP_ID
		left join
		(
		<!-- 取值未删除，审核通过，使用中，未作废的合同数据，按项目分组，有数据即可不要求最新 -->
		SELECT
		x.SETTLE_DOMAIN,
		x.CONTRACT_NAME,
		x.CONTRACT_TYPE,
		x.CONTRACT_START,
		x.CONTRACT_END,
		x.CONTRACT_TERM,
		x.CONTRACT_SUB_NAME,
		x.ID as "CONTRACT_ID",
		x.EMP_IDCARD,x.TRY_PERIOD
		FROM
		t_employee_contract_info x
		WHERE
		x.DELETE_FLAG = '0'
		AND (
		( x.AUDIT_STATUS = '2' AND x.IN_USE = '0' )
		OR
		( x.AUDIT_STATUS = '1' AND x.IN_USE = '1' )
		)
		<if test="checkVo != null">
			<if test="checkVo.idCards != null">
				AND x.EMP_IDCARD in
				<foreach item="idStr" index="index" collection="checkVo.idCards" open="(" separator="," close=")">
					#{idStr}
				</foreach>
			</if>
		</if>
		GROUP BY
		x.SETTLE_DOMAIN,x.EMP_IDCARD
		) b on c.DEPT_ID= b.SETTLE_DOMAIN and b.EMP_IDCARD=a.EMP_IDCARD

	</select>


	<!--tEmployeeInfo人员档案导出查询改造 fxj 2022-11-02-->
	<select id="noPageDiy" resultMap="tEmployeeExportMap">
		select
		a.EMP_CODE,
		a.EMP_NATRUE,
		a.EMP_NAME,
		a.EMP_IDCARD,
		a.EMP_AGE,
		a.VALIDITY_START,
		a.VALIDITY_END,
		a.EMP_MARRI_STATUS,
		a.EMP_NATIONAL,
		a.POLITICAL_STATUS,
		a.EMP_PHONE,
		a.EMP_EMAIL,
		a1.AREA_NAME as "ID_PROVINCE",
		a2.AREA_NAME as "ID_CITY",
		a3.AREA_NAME as "ID_TOWN",
		a.EMP_REGIS_TYPE,
		a4.AREA_NAME as "FILE_PROVINCE",
		a5.AREA_NAME as "FILE_CITY",
		a6.AREA_NAME as "FILE_TOWN",
		CASE WHEN a.IS_COLLEGE=0 THEN "否"
		WHEN a.IS_COLLEGE =1 THEN "是"
		ELSE a.IS_COLLEGE END as "IS_COLLEGE",
		a.HIGN_EDUCATION,
		a.SCHOOL,
		a.MAJOR,
		a.ADMISSION_DATE,
		a.GRADUTION_DATE,
		a.REMARK,
		a.FILE_SOURCE,
		/*a.PROJECT_NUM,*/
		a.CONTRACT_STATUS,
		a.INSURANCE_STATUS,
		a.SOCIAL_STATUS,
		a.FUND_STATUS,
		CASE WHEN a.SALARY_STATUS=0 THEN "无"
		WHEN a.SALARY_STATUS =1 THEN "是"
		WHEN a.SALARY_STATUS =2 THEN "否"
		ELSE a.SALARY_STATUS END as "SALARY_STATUS",#  0=无,1=是,2=否
		a.CREATE_NAME,
		a.CREATE_TIME,
		CASE WHEN a.`STATUS`=0 THEN "草稿"
		WHEN a.`STATUS` =1 THEN "已审核"
		ELSE a.`STATUS` END as  "STATUS",  # 0=草稿,1=已审核
		a7.AREA_NAME as "CONTACT_PROVINCE",
		a8.AREA_NAME as "CONTACT_CITY",
		a9.AREA_NAME as "CONTACT_TOWN",
		a.CONTACT_ADDRESS,
		p.IS_PROJECTS ,
		p.DEPT_NAME,
		p.DEPT_NO,
		P.PROJECT_NUM
		from t_employee_info a
		LEFT JOIN sys_area a1 on a1.id=a.ID_PROVINCE
		LEFT JOIN sys_area a2 on a2.id=a.ID_CITY
		LEFT JOIN sys_area a3 on a3.id=a.ID_TOWN
		LEFT JOIN sys_area a4 on a4.id=a.FILE_PROVINCE
		LEFT JOIN sys_area a5 on a5.id=a.FILE_CITY
		LEFT JOIN sys_area a6 on a6.id=a.FILE_TOWN
		LEFT JOIN sys_area a7 on a7.id=a.CONTACT_PROVINCE
		LEFT JOIN sys_area a8 on a8.id=a.CONTACT_CITY
		LEFT JOIN sys_area a9 on a9.id=a.CONTACT_TOWN
		LEFT JOIN view_project p on a.EMP_IDCARD=p.EMP_IDCARD
		<where>
			a.DELETE_FLAG = '0'
			<include refid="employeeInfo_where_export"/>
		</where>
		order by a.CREATE_TIME desc
		<if test="tEmployeeInfo != null">
			<if test="tEmployeeInfo.limitStart != null">
				limit #{tEmployeeInfo.limitStart},#{tEmployeeInfo.limitEnd}
			</if>
		</if>
	</select>

	<!--tEmployeeInfo人员档案导出查询count fxj 2022-11-02-->
	<select id="noPageCountDiy" resultType="java.lang.Integer">
		select
		count(1)
		from t_employee_info a
		<where>
			a.DELETE_FLAG = '0'
			<include refid="employeeInfo_where_export"/>
		</where>
	</select>
	<sql id="employeeInfo_where_export">
		<include refid="employeeInfo_where"/>

		<if test="tEmployeeInfo.authSql != null and tEmployeeInfo.authSql.trim() != ''">
			and
			a.id in (
			select  b.emp_id from  t_employee_project  b
			where
			b.DELETE_FLAG = '0'
			${tEmployeeInfo.authSql}
			)
		</if>
	</sql>

	<!--tEmployeeInfo离职档案导出查询-->
	<select id="noPageLeaveDiy" resultMap="tEmployeeLeaveExportMap">
		SELECT
		a.EMP_CODE,
		CASE WHEN a.EMP_NATRUE='0' THEN "外包"
		WHEN a.EMP_NATRUE='1'  THEN "派遣"
		WHEN a.EMP_NATRUE='2'  THEN "代理"
		WHEN a.EMP_NATRUE='3'  THEN "内部员工"
		ELSE "" END as "EMP_NATRUE",
		a.EMP_NAME,
		a.EMP_IDCARD,
		a.EMP_PHONE,
		a.EMP_EMAIL,
		a1.area_name as "FILE_PROVINCE",
		a2.area_name as "FILE_CITY",
		a3.area_name as "FILE_TOWN",
		a.LEAVE_TIME,
		a.LEAVE_USER_NAME,
		p.DEPT_NAME,
		p.DEPT_NO,
		p.UNIT_NAME
		from t_employee_info a
		LEFT JOIN view_project_leave p on a.EMP_IDCARD=p.EMP_IDCARD
		LEFT JOIN sys_area a1 on a1.id=a.FILE_PROVINCE
		LEFT JOIN sys_area a2 on a2.id=a.FILE_CITY
		LEFT JOIN sys_area a3 on a3.id=a.FILE_TOWN
		<where>
			a.DELETE_FLAG = '0'
			<include refid="employeeInfo_where"/>
			<if test="tEmployeeInfo.authSql != null and tEmployeeInfo.authSql.trim() != ''">
				and
				a.id in (
				select  b.emp_id from  t_employee_project  b
				where
				b.DELETE_FLAG = '0'
				${tEmployeeInfo.authSql}
				)
			</if>
			<if test="tEmployeeInfo.unitName != null and tEmployeeInfo.unitName.trim() != ''">
				AND p.UNIT_NAME like concat('%',#{tEmployeeInfo.unitName},'%')
			</if>
			<if test="tEmployeeInfo.deptName != null and tEmployeeInfo.deptName.trim() != ''">
				AND p.DEPT_NAME like concat('%',#{tEmployeeInfo.deptName},'%')
			</if>
			<if test="tEmployeeInfo.deptNo != null and tEmployeeInfo.deptNo.trim() != ''">
				AND p.DEPT_NO like concat('%',#{tEmployeeInfo.deptNo},'%')
			</if>
		</where>
		order by a.CREATE_TIME desc
		<if test="tEmployeeInfo != null">
			<if test="tEmployeeInfo.limitStart != null">
				limit #{tEmployeeInfo.limitStart},#{tEmployeeInfo.limitEnd}
			</if>
		</if>

	</select>

	<!--tEmployeeInfo离职档案导出查询-->
	<select id="noPageCountLeaveDiy" resultType="java.lang.Integer">
		select count(1)
		from t_employee_info a
		LEFT JOIN view_project_leave p on a.EMP_IDCARD=p.EMP_IDCARD
		<where>
			a.DELETE_FLAG = '0'
			<include refid="employeeInfo_where"/>
			<if test="tEmployeeInfo.authSql != null and tEmployeeInfo.authSql.trim() != ''">
				and
				a.id in (
				select  b.emp_id from  t_employee_project  b
				where
				b.DELETE_FLAG = '0'
				${tEmployeeInfo.authSql}
				)
			</if>
			<if test="tEmployeeInfo.unitName != null and tEmployeeInfo.unitName.trim() != ''">
				AND p.UNIT_NAME like concat('%',#{tEmployeeInfo.unitName},'%')
			</if>
			<if test="tEmployeeInfo.deptName != null and tEmployeeInfo.deptName.trim() != ''">
				AND p.DEPT_NAME like concat('%',#{tEmployeeInfo.deptName},'%')
			</if>
			<if test="tEmployeeInfo.deptNo != null and tEmployeeInfo.deptNo.trim() != ''">
				AND p.DEPT_NO like concat('%',#{tEmployeeInfo.deptNo},'%')
			</if>
		</where>
		order by a.CREATE_TIME desc

	</select>

	<update id="updateSocialInfoById" >
		update t_employee_info a
		<trim prefix="set" suffixOverrides=",">
			<if test="tEmployeeInfo.id != null">a.id=a.id,</if>
			<if test="tEmployeeInfo.socialStatus != null">SOCIAL_STATUS=#{tEmployeeInfo.socialStatus},</if>
			<if test="tEmployeeInfo.fundStatus != null">FUND_STATUS=#{tEmployeeInfo.fundStatus},</if>
			<if test="tEmployeeInfo.hignEducation != null">HIGN_EDUCATION=#{tEmployeeInfo.hignEducation},</if>
			<if test="tEmployeeInfo.school != null">SCHOOL=#{tEmployeeInfo.school},</if>
			<if test="tEmployeeInfo.major != null">MAJOR=#{tEmployeeInfo.major},</if>
			<if test="tEmployeeInfo.gradutionDate != null">GRADUTION_DATE=#{tEmployeeInfo.gradutionDate},</if>
			<if test="tEmployeeInfo.contactAddress != null">CONTACT_ADDRESS=#{tEmployeeInfo.contactAddress},</if>
			<if test="tEmployeeInfo.isCollege != null">IS_COLLEGE=#{tEmployeeInfo.isCollege},</if>
			<if test="tEmployeeInfo.fileProvince != null">a.FILE_PROVINCE=#{tEmployeeInfo.fileProvince},</if>
			<if test="tEmployeeInfo.fileCity != null">a.FILE_CITY=#{tEmployeeInfo.fileCity},</if>
			<if test="tEmployeeInfo.fileCity != null">
				<if test="tEmployeeInfo.fileTown != null">a.FILE_TOWN=#{tEmployeeInfo.fileTown},</if>
				<if test="tEmployeeInfo.fileTown == null">a.FILE_TOWN=null,</if>
			</if>
			<if test="tEmployeeInfo.empRegisType != null">a.EMP_REGIS_TYPE=#{tEmployeeInfo.empRegisType},</if>
			<if test="tEmployeeInfo.empPhone != null  and tEmployeeInfo.empPhone != ''">a.EMP_PHONE=#{tEmployeeInfo.empPhone},</if>
		</trim>
		WHERE a.id=#{tEmployeeInfo.id}
	</update>

	<!-- 更新档案商险状态 -->
	<update id="updateEmployeeInfoInsuranceStatus" >
		update t_employee_info a set a.INSURANCE_STATUS = #{emp.status} where a.EMP_IDCARD = #{emp.idCard}
	</update>

	<!-- 更新档案薪资状态 -->
	<update id="updateEmployeeInfoSalaryStatus" >
		update t_employee_info a set a.SALARY_STATUS = #{emp.status} where a.EMP_IDCARD = #{emp.idCard}
	</update>

	<!-- 每月1号更新档案薪资状态为否薪资 -->
	<update id="everyMonthUpdateEmployeeInfoSalaryStatus" >
		update t_employee_info e set e.SALARY_STATUS = 2
		where e.SALARY_STATUS = 1
			and NOT EXISTS (
				select 1 from view_salary_account_3month a where a.emp_idcard = e.EMP_IDCARD
			)
	</update>

	<!-- 每天更新人员档案、项目档案的过期合同为2不在用 -->
	<update id="everyDayUpdateEmployeeInfoContractStatus" >
		update t_employee_info e
		left join t_employee_contract_info c on e.EMP_IDCARD=c.EMP_IDCARD and c.IN_USE='0'
		set e.CONTRACT_STATUS=2
		where e.CONTRACT_STATUS != 2 and c.CONTRACT_END <![CDATA[ < ]]> DATE_FORMAT(curdate(),"%Y-%m-%d")
		and not EXISTS (
			select 1 from t_employee_contract_info ec where ec.EMP_IDCARD=c.EMP_IDCARD and ec.IN_USE='0'
			and (ec.CONTRACT_END is null or ec.CONTRACT_END >= DATE_FORMAT(curdate(),"%Y-%m-%d"))
		)
	</update>
	<update id="updateContractStatus" >
		update t_employee_info a
		set CONTRACT_STATUS = #{status}
		WHERE a.id=#{empId}
	</update>

	<!-- hgw 根据身份证查询-基础信息 -->
	<select id="getHcmArchiveBasicVo" resultType="com.yifu.cloud.plus.v1.yifu.archives.vo.HcmArchiveBasicVo">
		select
			a.EMP_NAME empName
			 ,a.EMP_IDCARD empIdCard
			 ,a.EMP_EMAIL email
			 ,a.EMP_EMAIL email
			 ,a.EMP_BIRTHDAY bornDate
			 ,if(a.EMP_SEX='2','女',if(a.EMP_SEX='1','男','-')) sex
			 ,education.label education
			 ,registype.label residentType
			 ,married.label maritalStatus
			 ,nation.label nation
			 ,concat(ifnull(p.AREA_NAME,''),ifnull(c.AREA_NAME,''),ifnull(t.AREA_NAME,'')) nativePlace
			 ,politicalStatus.label political
			 ,a.contact_Address contactAddress
		from t_employee_info a
				 left join sys_area p on p.id=a.ID_PROVINCE
				 left join sys_area c on c.id=a.ID_CITY
				 left join sys_area t on t.id=a.ID_TOWN
				 left join view_sys_dict_item nation on nation.value=a.EMP_NATIONAL and nation.type='emp_national'
				 left join view_sys_dict_item education on education.value=a.HIGN_EDUCATION and education.type='education'
				 left join view_sys_dict_item registype on registype.value=a.EMP_REGIS_TYPE and registype.type='emp_registype'
				 left join view_sys_dict_item married on married.value=a.EMP_MARRI_STATUS and married.type='emp_married'
				 left join view_sys_dict_item politicalStatus on politicalStatus.value=a.POLITICAL_STATUS and politicalStatus.type='emp_political'
		where a.DELETE_FLAG = '0' and a.EMP_IDCARD = #{idCard} limit 1
	</select>
	<select id="getHcmArchiveJobVo" resultType="com.yifu.cloud.plus.v1.yifu.archives.vo.HcmArchiveJobVo">
		select
			a.WORK_UNIT companyName
			 ,a.WORK_DEPART deptName
			 ,a.WORK_JOB jobName
			 ,a.START_DATE joinDate
			 ,a.END_DATE leaveDate
			 ,a.REMARK remark
		from t_emp_work_recording a
		where a.EMP_IDCARD = #{idCard}
	</select>
	<select id="getHcmArchiveEducationVo" resultType="com.yifu.cloud.plus.v1.yifu.archives.vo.HcmArchiveEducationVo">
		select
			a.SCHOOL schoolName
			 ,a.MAJOR major
			 ,education.label education
			 ,a.ENTRY_DATE joinDate
			 ,a.GRADUTION_DATE leaveDate
			 ,educationType.label isFullTime
			 ,'' annex
		from t_emp_education a
				 left join view_sys_dict_item education on education.value=a.EDUCATION_NAME and education.type='education'
				 left join view_sys_dict_item educationType on educationType.value=a.type and educationType.type='education_type'
		where a.EMP_IDCARD = #{idCard}
	</select>
	<select id="getHcmArchiveCertificateVo" resultType="com.yifu.cloud.plus.v1.yifu.archives.vo.HcmArchiveCertificateVo">
		select
			qualification.label certificateName
			 ,a.CERTIFICATION_NO certificateNo
			 ,a.ASSESSMENT_UNIT certificateOrg
			 ,'' certificateDate
			 ,'' certificateExpireDate
			 ,'' annex
		from t_emp_professional_qualification a
				 left join view_sys_dict_item qualification on qualification.value=a.QUALIFICATION_TYPE and qualification.type='qualification_type'
		where a.EMP_IDCARD = #{idCard}
	</select>
	<select id="getHcmArchiveFamilyVo" resultType="com.yifu.cloud.plus.v1.yifu.archives.vo.HcmArchiveFamilyVo">
		select
			relation.label relationName
			 ,a.FAMILY_NAME trueName
			 ,a.BIRTHDAY bornTime
			 ,'' political
			 ,a.WORK_UNIT company
			 ,'' jobName
		from t_emp_family a
				 left join view_sys_dict_item relation on relation.value=a.RELATIONSHIP_SELF and relation.type='family_relation'
		where a.EMP_IDCARD = #{idCard}
	</select>
	<select id="getHcmContractVo" resultType="com.yifu.cloud.plus.v1.yifu.archives.vo.HcmContractVo">
		select
			a.CONTRACT_NAME contractType
			 ,contractType.label termType
			 ,'' contractDate
			 ,a.CONTRACT_PARTY contractCompany
			 ,a.CONTRACT_NO contractCode
			 ,a.CONTRACT_START startDate
			 ,a.CONTRACT_END endDate
			 ,if(a.IN_USE='0','可用','不可用') contractStatus
			 ,'' annexArray
			 ,'' contractOwner
		from t_employee_contract_info a
				 left join view_sys_dict_item contractType on contractType.value=a.CONTRACT_TYPE and contractType.type='employee_contract_type'
		where a.EMP_IDCARD = #{idCard}
	</select>

	<!-- hgw 根据身份证查询 -->
	<select id="getHcmEmpInfoList" resultMap="tEmployeeInfoMap">
		select
			<include refid="baseParam"/>
		from t_employee_info a
		where a.DELETE_FLAG = '0' and a.EMP_IDCARD in
		<foreach item="item" index="index" collection="idCardList" open="(" separator="," close=")">
			#{item}
		</foreach>
	</select>


	<!-- 员工年龄+1 定时任务方法  专用 -->
	<update id="updateEmployeeAgeTask">
		update t_employee_info  set  EMP_AGE = YEAR(CURRENT_DATE())-YEAR(STR_TO_DATE(SUBSTRING(EMP_IDCARD,7,4),'%Y')) where EMP_IDCARD is not null  and DELETE_FLAG='0' and SUBSTRING(EMP_IDCARD,7,1) in ('1','2')
	</update>

	<select id="getEmpOnlineMap" resultType="java.lang.String">
		SELECT EMP_IDCARD
		FROM t_employee_info
		WHERE EMP_IDCARD IN
		<foreach item="idCard" index="index" collection="empIdCardList"
				 open="(" separator="," close=")">
			#{idCard}
		</foreach>
		and FILE_STATUS = '1'  <!-- 已减档 -->
		and DELETE_FLAG ='0'   <!-- 未删除 -->
	</select>


	<select id="getHcmArchiveBasicList" resultType="com.yifu.cloud.plus.v1.yifu.archives.vo.HcmArchiveBasicVo">
		select
			 a.EMP_NAME empName
			 ,a.EMP_IDCARD empIdCard
			 ,a.EMP_PHONE empPhone
			 ,a.EMP_EMAIL email
			 ,a.EMP_BIRTHDAY bornDate
			 ,if(a.EMP_SEX='2','女',if(a.EMP_SEX='1','男','-')) sex
			 ,a.HIGN_EDUCATION educationValue
			 ,a.EMP_REGIS_TYPE residentTypeValue
			 ,a.EMP_MARRI_STATUS maritalStatusValue
			 ,a.EMP_NATIONAL nationValue
			 ,a.POLITICAL_STATUS politicalValue
			 ,a.ID_PROVINCE idProvince
			 ,a.ID_CITY idCity
			 ,a.ID_TOWN idTown
			 ,a.CONTACT_PROVINCE contactProvince
			 ,a.CONTACT_CITY contactCity
			 ,a.CONTACT_TOWN contactTown
			 ,a.contact_Address contactAddress
			 ,a.BANK_NO bankNo
			 ,a.BANK_NAME bankName
		from t_employee_info a
		where a.DELETE_FLAG = '0' and a.EMP_IDCARD in
		<foreach item="item" index="index" collection="idCardSet" open="(" separator="," close=")">
			#{item}
		</foreach>
	</select>

	<select id="getSysAreaList" resultType="com.yifu.cloud.plus.v1.yifu.archives.vo.SysAreaVo">
		select a.id id,a.AREA_NAME name from sys_area a
		where a.id in
		<foreach item="item" index="index" collection="areaIdSet" open="(" separator="," close=")">
			#{item}
		</foreach>
	</select>

	<select id="getHcmArchiveFamilyList" resultType="com.yifu.cloud.plus.v1.yifu.archives.vo.HcmArchiveFamilyVo">
		select
			a.RELATIONSHIP_SELF relation
			,a.EMP_IDCARD empIdCard
			 ,a.FAMILY_NAME trueName
			 ,a.BIRTHDAY bornTime
			 ,'' political
			 ,a.WORK_UNIT company
			 ,'' jobName
		from t_emp_family a
		where a.DELETE_FLAG = '0' and a.EMP_IDCARD in
		<foreach item="item" index="index" collection="idCardSet" open="(" separator="," close=")">
			#{item}
		</foreach>
		order by a.CREATE_TIME desc
	</select>

	<select id="getHcmArchiveEducationList" resultType="com.yifu.cloud.plus.v1.yifu.archives.vo.HcmArchiveEducationVo">
		select
			a.SCHOOL schoolName
			,a.EMP_IDCARD empIdCard
			 ,a.MAJOR major
			 ,EDUCATION_NAME educationValue
			 ,a.ENTRY_DATE joinDate
			 ,a.GRADUTION_DATE leaveDate
			 ,a.type educationTypeValue
			 ,'' annex
		from t_emp_education a
		where a.DELETE_FLAG = '0' and a.EMP_IDCARD in
		<foreach item="item" index="index" collection="idCardSet" open="(" separator="," close=")">
			#{item}
		</foreach>
		order by a.CREATE_TIME desc
	</select>

	<select id="getHcmArchiveJobList" resultType="com.yifu.cloud.plus.v1.yifu.archives.vo.HcmArchiveJobVo">
		select
			a.WORK_UNIT companyName
			,a.EMP_IDCARD empIdCard
			 ,a.WORK_DEPART deptName
			 ,a.WORK_JOB jobName
			 ,a.START_DATE joinDate
			 ,a.END_DATE leaveDate
			 ,a.REMARK remark
		from t_emp_work_recording a
		where a.DELETE_FLAG = '0' and a.EMP_IDCARD in
		<foreach item="item" index="index" collection="idCardSet" open="(" separator="," close=")">
			#{item}
		</foreach>
		order by a.CREATE_TIME desc
	</select>

	<select id="getHcmArchiveBadRecordList" resultType="com.yifu.cloud.plus.v1.yifu.archives.vo.HcmArchiveBadRecordVo">
		select
		a.EMP_IDCARD empIdCard
		,a.HAPPEN_TIME happenDate
		,a.REMARK remark
		from t_emp_bad_record a
		where a.EMP_IDCARD in
		<foreach item="item" index="index" collection="idCardSet" open="(" separator="," close=")">
			#{item}
		</foreach>
		order by a.CREATE_TIME desc
	</select>

	<select id="getHcmArchiveCertificateList" resultType="com.yifu.cloud.plus.v1.yifu.archives.vo.HcmArchiveCertificateVo">
		select
			a.QUALIFICATION_TYPE certificateType
		    ,a.EMP_IDCARD empIdCard
			 ,a.CERTIFICATION_NO certificateNo
			 ,a.ASSESSMENT_UNIT certificateOrg
			 ,'' certificateDate
			 ,'' certificateExpireDate
			 ,'' annex
		from t_emp_professional_qualification a
		where a.DELETE_FLAG = '0' and a.EMP_IDCARD in
		<foreach item="item" index="index" collection="idCardSet" open="(" separator="," close=")">
			#{item}
		</foreach>
		order by a.CREATE_TIME desc
	</select>

	<!-- 在档的信息 -->
	<select id="getEmpInfoByIdCard" resultType="java.lang.String">
		select
			a.id
		from t_employee_info a
		where a.DELETE_FLAG = '0' and a.file_Status = 0 and a.EMP_IDCARD = #{empIdCard}
		limit 1
	</select>

</mapper>
