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

  <resultMap id="tEmployeeProjectMap" type="com.yifu.cloud.plus.v1.yifu.archives.entity.TEmployeeProject">
    <id property="id" column="ID"/>
    <result property="empId" column="EMP_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="bankName" column="BANK_NAME"/>
    <result property="bankSubName" column="BANK_SUB_NAME"/>
    <result property="bankNo" column="BANK_NO"/>
    <result property="leaveUser" column="LEAVE_USER"/>
    <result property="leaveTime" column="LEAVE_TIME"/>
    <result property="leaveReason" column="LEAVE_REASON"/>
    <result property="leaveRemark" column="LEAVE_REMARK"/>
    <result property="isLeaveEmployee" column="IS_LEAVE_EMPLOYEE"/>
    <result property="empNo" column="EMP_NO"/>
    <result property="unitId" column="UNIT_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="businessPrimaryType" column="BUSINESS_PRIMARY_TYPE"/>
    <result property="businessSecondType" column="BUSINESS_SECOND_TYPE"/>
    <result property="businessThirdType" column="BUSINESS_THIRD_TYPE"/>
    <result property="contractType" column="CONTRACT_TYPE"/>
    <result property="workingHours" column="WORKING_HOURS"/>
    <result property="post" column="POST"/>
    <result property="enjoinDate" column="ENJOIN_DATE"/>
    <result property="tryPeriod" column="TRY_PERIOD"/>
    <result property="projectSource" column="PROJECT_SOURCE"/>
    <result property="taxMonth" column="TAX_MONTH"/>
    <result property="status" column="STATUS"/>
    <result property="projectStatus" column="PROJECT_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="remark" column="REMARK"/>
    <result property="fileProvince" column="FILE_PROVINCE"/>
    <result property="fileCity" column="FILE_CITY"/>
    <result property="fileTown" column="FILE_TOWN"/>
    <result property="socialProvince" column="SOCIAL_PROVINCE"/>
    <result property="socialCity" column="SOCIAL_CITY"/>
    <result property="socialTown" column="SOCIAL_TOWN"/>
    <result property="empPhone" column="EMP_PHONE"/>
    <result property="salaryStatus" column="SALARY_STATUS"/>
    <result property="insuranceStatus" column="INSURANCE_STATUS"/>
    <result property="socialStatus" column="SOCIAL_STATUS"/>
    <result property="fundStatus" column="FUND_STATUS"/>
    <result property="contractStatus" column="CONTRACT_STATUS"/>
	<result property="empLabel" column="EMP_LABEL"/>
	<result property="isComplete" column="IS_COMPLETE"/>
  </resultMap>

	<resultMap id="tEmployeeProjectExportMap" type="com.yifu.cloud.plus.v1.yifu.archives.vo.EmployeeProjectExportVO">
		<result property="empCode" column="EMP_CODE"/>
		<result property="projectStatus" column="PROJECT_STATUS"/>
		<result property="proEmpNatrue" column="PROEMP_NATRUE"/>
		<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="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="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="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="proStatus" column="PROSTATUS"/>
		<result property="bankName" column="BANK_NAME"/>
		<result property="bankSubName" column="BANK_SUB_NAME"/>
		<result property="bankNo" column="BANK_NO"/>
		<result property="empNo" column="EMP_NO"/>
		<result property="unitName" column="UNIT_NAME"/>
		<result property="unitNo" column="UNIT_NO"/>
		<result property="deptName" column="DEPT_NAME"/>
		<result property="deptNo" column="DEPT_NO"/>
		<result property="businessPrimaryType" column="BUSINESS_PRIMARY_TYPE"/>
		<result property="businessSecondType" column="BUSINESS_SECOND_TYPE"/>
		<result property="businessThirdType" column="BUSINESS_THIRD_TYPE"/>
		<result property="contractType" column="CONTRACT_TYPE"/>
		<result property="workingHours" column="WORKING_HOURS"/>
		<result property="post" column="POST"/>
		<result property="enjoinDate" column="ENJOIN_DATE"/>
		<result property="tryPeriod" column="TRY_PERIOD"/>
		<result property="projectSource" column="PROJECT_SOURCE"/>
		<result property="empNatrue" column="EMP_NATRUE"/>
		<result property="proCreateName" column="PROCREATE_NAME"/>
		<result property="proCreateTime" column="PROCREATE_TIME"/>
		<result property="fileProvince" column="FILE_PROVINCE"/>
		<result property="fileCity" column="FILE_CITY"/>
		<result property="fileTown" column="FILE_TOWN"/>
		<result property="empPhone" column="EMP_PHONE"/>
		<result property="salaryStatus" column="SALARY_STATUS"/>
		<result property="insuranceStatus" column="INSURANCE_STATUS"/>
		<result property="socialStatus" column="SOCIAL_STATUS"/>
		<result property="fundStatus" column="FUND_STATUS"/>
		<result property="createName" column="CREATE_NAME"/>
		<result property="createTime" column="CREATE_TIME"/>
		<result property="contractStatus" column="CONTRACT_STATUS"/>
		<result property="empLabel" column="EMP_LABEL"/>
		<result property="contractParty" column="CONTRACT_PARTY"/>
		<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="isComplete" column="IS_COMPLETE"/>
	</resultMap>

	<sql id="Base_Column_List">
				  a.ID,
                  a.EMP_ID,
                  a.EMP_CODE,
                  a.EMP_NATRUE,
                  a.EMP_NAME,
                  a.EMP_IDCARD,
                  a.BANK_NAME,
                  a.BANK_SUB_NAME,
                  a.BANK_NO,
                  a.LEAVE_USER,
                  a.LEAVE_TIME,
                  a.LEAVE_REASON,
                  a.LEAVE_REMARK,
                  a.EMP_NO,
                  a.UNIT_ID,
                  a.UNIT_NAME,
                  a.UNIT_NO,
                  a.DEPT_ID,
                  a.DEPT_NAME,
                  a.DEPT_NO,
                  a.BUSINESS_PRIMARY_TYPE,
                  a.BUSINESS_SECOND_TYPE,
                  a.BUSINESS_THIRD_TYPE,
                  a.CONTRACT_TYPE,
                  a.WORKING_HOURS,
                  a.POST,
                  a.ENJOIN_DATE,
                  a.TRY_PERIOD,
                  a.PROJECT_SOURCE,
                  a.TAX_MONTH,
                  a.STATUS,
                  a.PROJECT_STATUS,
                  a.DELETE_FLAG,
                  a.CREATE_BY,
                  a.CREATE_NAME,
                  a.CREATE_TIME,
                  a.UPDATE_BY,
                  a.UPDATE_TIME,
                  a.REMARK,
                  a.CONTRACT_STATUS,
				  b.EMP_PHONE,
				  a.INSURANCE_STATUS,
				  a.SOCIAL_STATUS,
				  a.FUND_STATUS,
				  b.FILE_PROVINCE,
				  b.FILE_CITY,
				  b.FILE_TOWN,
				  a.SOCIAL_PROVINCE,
				  a.SOCIAL_CITY,
				  a.SOCIAL_TOWN,
				  a.SALARY_STATUS,
				  a.EMP_LABEL,
				  a.IS_COMPLETE
	</sql>

	<sql id="Base_Export_List">
                  a.EMP_CODE,
                  a.EMP_NATRUE,
                  a.EMP_NAME,
                  a.EMP_IDCARD,
                  a.BANK_NAME,
                  a.BANK_SUB_NAME,
                  a.BANK_NO,
                  a.EMP_NO,
                  a.UNIT_NAME,
                  a.UNIT_NO,
                  a.DEPT_NAME,
                  a.DEPT_NO,
                  a.BUSINESS_PRIMARY_TYPE,
                  a.BUSINESS_SECOND_TYPE,
                  a.BUSINESS_THIRD_TYPE,
                  a.CONTRACT_TYPE,
                  a.WORKING_HOURS,
                  a.POST,
                  a.ENJOIN_DATE,
                  a.TRY_PERIOD,
                  a.PROJECT_SOURCE,
                  a.CONTRACT_STATUS,
                  if(b.STATUS=0,'草稿','已审核') AS PROSTATUS,
                  b.CREATE_NAME AS PROCREATE_NAME,
                  b.CREATE_TIME AS PROCREATE_TIME,
				  b.EMP_PHONE,
				  a.INSURANCE_STATUS,
				  a.SOCIAL_STATUS,
				  a.FUND_STATUS,
				  a4.AREA_NAME FILE_PROVINCE,
				  a5.AREA_NAME FILE_CITY,
				  a6.AREA_NAME FILE_TOWN,
				  a7.AREA_NAME SOCIAL_PROVINCE,
				  a8.AREA_NAME SOCIAL_CITY,
				  a9.AREA_NAME SOCIAL_TOWN,
				  a.SALARY_STATUS,
				  b.VALIDITY_START,
				  b.VALIDITY_END,
				  b.EMP_MARRI_STATUS,
				  b.EMP_NATIONAL,
				  b.POLITICAL_STATUS,
				  a1.AREA_NAME ID_PROVINCE,
				  a2.AREA_NAME ID_CITY,
				  a3.AREA_NAME ID_TOWN,
				  b.EMP_REGIS_TYPE,
				  CASE WHEN b.IS_COLLEGE=0 THEN "否"
				  WHEN b.IS_COLLEGE =1 THEN "是"
				  ELSE b.IS_COLLEGE
				  END as "IS_COLLEGE",
				  b.HIGN_EDUCATION,
				  b.SCHOOL,
				  b.MAJOR,
				  b.ADMISSION_DATE,
				  b.GRADUTION_DATE,
				  b.REMARK,
				case b.FILE_SOURCE
				when 1 then '派单'
				when 2 then '薪酬'
				when 3 then '商险'
				when 4 then '档案新建'
				when 5 then '批量导入'
				when 6 then '电子签'
				when 7 then '预入职'
				else '-' end FILE_SOURCE,
				  if(a.STATUS='0','草稿',IF(a.PROJECT_STATUS=1,'已减项','已审核')) PROJECT_STATUS,
				  a.CREATE_NAME,
				  a.CREATE_TIME,
				  b.EMP_EMAIL,
				  b.EMP_NATRUE AS PROEMP_NATRUE,
				  a.EMP_LABEL,
                  t.CONTRACT_PARTY,
				  if(ifnull(a.IS_COMPLETE,'1')='0','是','否') IS_COMPLETE

	</sql>

	<sql id="tEmployeeProject_where">
		<if test="tEmployeeProject != null">
			<if test="tEmployeeProject.id != null and tEmployeeProject.id.trim() != ''">
				AND a.ID = #{tEmployeeProject.id}
			</if>
			<if test="tEmployeeProject.empId != null and tEmployeeProject.empId.trim() != ''">
				AND a.EMP_ID = #{tEmployeeProject.empId}
			</if>
			<if test="tEmployeeProject.empCode != null and tEmployeeProject.empCode.trim() != ''">
				AND a.EMP_CODE = #{tEmployeeProject.empCode}
			</if>
			<if test="tEmployeeProject.empNatrue != null and tEmployeeProject.empNatrue.trim() != ''">
				AND a.EMP_NATRUE in (${tEmployeeProject.empNatrue})
			</if>
			<if test="tEmployeeProject.empName != null and tEmployeeProject.empName.trim() != ''">
				AND a.EMP_NAME like concat('%',#{tEmployeeProject.empName},'%')
			</if>
			<if test="tEmployeeProject.empIdcard != null and tEmployeeProject.empIdcard.trim() != ''">
				AND a.EMP_IDCARD like concat('%',#{tEmployeeProject.empIdcard},'%')
			</if>
			<if test="tEmployeeProject.bankName != null and tEmployeeProject.bankName.trim() != ''">
				AND a.BANK_NAME = #{tEmployeeProject.bankName}
			</if>
			<if test="tEmployeeProject.bankSubName != null and tEmployeeProject.bankSubName.trim() != ''">
				AND a.BANK_SUB_NAME = #{tEmployeeProject.bankSubName}
			</if>
			<if test="tEmployeeProject.bankNo != null and tEmployeeProject.bankNo.trim() != ''">
				AND a.BANK_NO = #{tEmployeeProject.bankNo}
			</if>
			<if test="tEmployeeProject.leaveUser != null and tEmployeeProject.leaveUser.trim() != ''">
				AND a.LEAVE_USER = #{tEmployeeProject.leaveUser}
			</if>
			<if test="tEmployeeProject.leaveTime != null">
				AND a.LEAVE_TIME = #{tEmployeeProject.leaveTime}
			</if>
			<if test="tEmployeeProject.leaveReason != null and tEmployeeProject.leaveReason.trim() != ''">
				AND a.LEAVE_REASON = #{tEmployeeProject.leaveReason}
			</if>
			<if test="tEmployeeProject.leaveRemark != null and tEmployeeProject.leaveRemark.trim() != ''">
				AND a.LEAVE_REMARK = #{tEmployeeProject.leaveRemark}
			</if>
			<if test="tEmployeeProject.empNo != null and tEmployeeProject.empNo.trim() != ''">
				AND a.EMP_NO = #{tEmployeeProject.empNo}
			</if>
			<if test="tEmployeeProject.unitId != null and tEmployeeProject.unitId.trim() != ''">
				AND a.UNIT_ID = #{tEmployeeProject.unitId}
			</if>
			<if test="tEmployeeProject.unitName != null and tEmployeeProject.unitName.trim() != ''">
				AND a.UNIT_NAME like concat('%',#{tEmployeeProject.unitName},'%')
			</if>
			<if test="tEmployeeProject.unitNo != null and tEmployeeProject.unitNo.trim() != ''">
				AND a.UNIT_NO = #{tEmployeeProject.unitNo}
			</if>
			<if test="tEmployeeProject.deptId != null and tEmployeeProject.deptId.trim() != ''">
				AND a.DEPT_ID = #{tEmployeeProject.deptId}
			</if>
			<if test="tEmployeeProject.deptName != null and tEmployeeProject.deptName.trim() != ''">
				AND a.DEPT_NAME like concat('%',#{tEmployeeProject.deptName},'%')
			</if>
			<if test="tEmployeeProject.deptNo != null and tEmployeeProject.deptNo.trim() != ''">
				AND a.DEPT_NO = #{tEmployeeProject.deptNo}
			</if>
			<if test="tEmployeeProject.businessPrimaryType != null and tEmployeeProject.businessPrimaryType.trim() != ''">
				AND a.BUSINESS_PRIMARY_TYPE = #{tEmployeeProject.businessPrimaryType}
			</if>
			<if test="tEmployeeProject.businessSecondType != null and tEmployeeProject.businessSecondType.trim() != ''">
				AND a.BUSINESS_SECOND_TYPE = #{tEmployeeProject.businessSecondType}
			</if>
			<if test="tEmployeeProject.businessThirdType != null and tEmployeeProject.businessThirdType.trim() != ''">
				AND a.BUSINESS_THIRD_TYPE like CONCAT('%',#{tEmployeeProject.businessThirdType},'%')
			</if>
			<if test="tEmployeeProject.contractType != null and tEmployeeProject.contractType.trim() != ''">
				AND a.CONTRACT_TYPE = #{tEmployeeProject.contractType}
			</if>
			<if test="tEmployeeProject.workingHours != null and tEmployeeProject.workingHours.trim() != ''">
				AND a.WORKING_HOURS = #{tEmployeeProject.workingHours}
			</if>
			<if test="tEmployeeProject.post != null and tEmployeeProject.post.trim() != ''">
				AND a.POST = #{tEmployeeProject.post}
			</if>
			<if test="tEmployeeProject.enjoinDate != null">
				AND a.ENJOIN_DATE = #{tEmployeeProject.enjoinDate}
			</if>
			<if test="tEmployeeProject.tryPeriod != null and tEmployeeProject.tryPeriod.trim() != ''">
				AND a.TRY_PERIOD = #{tEmployeeProject.tryPeriod}
			</if>
			<if test="tEmployeeProject.projectSource != null and tEmployeeProject.projectSource.trim() != ''">
				AND a.PROJECT_SOURCE = #{tEmployeeProject.projectSource}
			</if>
			<if test="tEmployeeProject.taxMonth != null and tEmployeeProject.taxMonth.trim() != ''">
				AND a.TAX_MONTH = #{tEmployeeProject.taxMonth}
			</if>
			<if test="tEmployeeProject.status != null">
				AND a.STATUS = #{tEmployeeProject.status}
			</if>
			<if test="tEmployeeProject.projectStatus != null">
				AND a.PROJECT_STATUS = #{tEmployeeProject.projectStatus}
			</if>
			<if test="tEmployeeProject.createBy != null and tEmployeeProject.createBy.trim() != ''">
				AND a.CREATE_BY = #{tEmployeeProject.createBy}
			</if>
			<if test="tEmployeeProject.createName != null and tEmployeeProject.createName.trim() != ''">
				AND a.CREATE_NAME = #{tEmployeeProject.createName}
			</if>
			<if test="tEmployeeProject.createTime != null">
				AND a.CREATE_TIME = #{tEmployeeProject.createTime}
			</if>
			<if test="tEmployeeProject.updateBy != null and tEmployeeProject.updateBy.trim() != ''">
				AND a.UPDATE_BY = #{tEmployeeProject.updateBy}
			</if>
			<if test="tEmployeeProject.updateTime != null">
				AND a.UPDATE_TIME = #{tEmployeeProject.updateTime}
			</if>
			<if test="tEmployeeProject.remark != null and tEmployeeProject.remark.trim() != ''">
				AND a.REMARK = #{tEmployeeProject.remark}
			</if>
			<if test="tEmployeeProject.contractStatus != null">
				AND a.CONTRACT_STATUS = #{tEmployeeProject.contractStatus}
			</if>
			<if test="tEmployeeProject.empPhone != null and tEmployeeProject.empPhone.trim() != ''">
				AND b.EMP_PHONE = #{tEmployeeProject.empPhone}
			</if>
			<if test="tEmployeeProject.insuranceStatus != null">
				AND a.INSURANCE_STATUS = #{tEmployeeProject.insuranceStatus}
			</if>
			<if test="tEmployeeProject.socialStatus != null">
			    AND a.SOCIAL_STATUS = #{tEmployeeProject.socialStatus}
		    </if>
			<if test="tEmployeeProject.fundStatus != null">
				AND a.FUND_STATUS = #{tEmployeeProject.fundStatus}
		    </if>
			<if test="tEmployeeProject.salaryStatus != null">
				AND a.SALARY_STATUS = #{tEmployeeProject.salaryStatus}
			</if>
			<if test="tEmployeeProject.fileProvince != null">
				AND b.FILE_PROVINCE = #{tEmployeeProject.fileProvince}
			</if>
			<if test="tEmployeeProject.fileCity != null">
				AND b.FILE_CITY = #{tEmployeeProject.fileCity}
			</if>
			<if test="tEmployeeProject.fileTown != null">
				AND b.FILE_TOWN = #{tEmployeeProject.fileTown}
			</if>
			<if test="tEmployeeProject.socialProvince != null">
				AND a.SOCIAL_PROVINCE = #{tEmployeeProject.socialProvince}
			</if>
			<if test="tEmployeeProject.socialCity != null">
				AND a.SOCIAL_CITY = #{tEmployeeProject.socialCity}
			</if>
			<if test="tEmployeeProject.socialTown != null">
				AND a.SOCIAL_TOWN = #{tEmployeeProject.socialTown}
			</if>
			<if test="tEmployeeProject.empNatureArray != null and tEmployeeProject.empNatureArray.length > 0">
				AND a.EMP_NATRUE in
				<foreach item="idStr" index="index" collection="tEmployeeProject.empNatureArray" open="(" separator=","
						 close=")">
					#{idStr}
				</foreach>
			</if>
			<if test="tEmployeeProject.contractStatusArray != null and tEmployeeProject.contractStatusArray.length > 0">
				AND a.CONTRACT_STATUS in
				<foreach item="idStr" index="index" collection="tEmployeeProject.contractStatusArray" open="("
						 separator="," close=")">
					#{idStr}
				</foreach>
			</if>
			<if test="tEmployeeProject.insuranceStatusArray != null and tEmployeeProject.insuranceStatusArray.length > 0">
				AND a.INSURANCE_STATUS in
				<foreach item="idStr" index="index" collection="tEmployeeProject.insuranceStatusArray" open="("
						 separator="," close=")">
					#{idStr}
				</foreach>
			</if>
			<if test="tEmployeeProject.socialStatusArray != null and tEmployeeProject.socialStatusArray.length > 0">
				AND a.SOCIAL_STATUS in
				<foreach item="idStr" index="index" collection="tEmployeeProject.socialStatusArray" open="("
						 separator="," close=")">
					#{idStr}
				</foreach>
			</if>
			<if test="tEmployeeProject.fundStatusArray != null and tEmployeeProject.fundStatusArray.length > 0">
				AND a.FUND_STATUS in
				<foreach item="idStr" index="index" collection="tEmployeeProject.fundStatusArray" open="("
						 separator="," close=")">
					#{idStr}
				</foreach>
			</if>
			<if test="tEmployeeProject.empLabel != null">
				AND a.EMP_LABEL = #{tEmployeeProject.empLabel}
			</if>

			<if test="tEmployeeProject.createTimeStart != null">
				AND a.CREATE_TIME <![CDATA[ >= ]]> #{tEmployeeProject.createTimeStart}
			</if>
			<if test="tEmployeeProject.createTimeEnd != null">
				AND a.CREATE_TIME <![CDATA[ <= ]]> #{tEmployeeProject.createTimeEnd}
			</if>
			<if test="tEmployeeProject.isComplete != null and tEmployeeProject.isComplete.trim() != ''">
				AND a.IS_COMPLETE = #{tEmployeeProject.isComplete}
			</if>
		</if>
	</sql>

	<sql id="exportTEmployeeProject_where">
		<if test="tEmployeeProject != null">
			<if test="tEmployeeProject.empCode != null and tEmployeeProject.empCode.trim() != ''">
				AND a.EMP_CODE = #{tEmployeeProject.empCode}
			</if>
			<if test="tEmployeeProject.empNatrue != null and tEmployeeProject.empNatrue.trim() != ''">
				AND a.EMP_NATRUE in (${tEmployeeProject.empNatrue})
			</if>
			<if test="tEmployeeProject.empName != null and tEmployeeProject.empName.trim() != ''">
				AND a.EMP_NAME like concat('%',#{tEmployeeProject.empName},'%')
			</if>
			<if test="tEmployeeProject.empIdcard != null and tEmployeeProject.empIdcard.trim() != ''">
				AND a.EMP_IDCARD like concat('%',#{tEmployeeProject.empIdcard},'%')
			</if>
			<if test="tEmployeeProject.projectStatusReplace != null and tEmployeeProject.projectStatusReplace.trim() != ''">
				AND a.PROJECT_STATUS = #{tEmployeeProject.projectStatusReplace}
			</if>
			<if test="tEmployeeProject.bankName != null and tEmployeeProject.bankName.trim() != ''">
				AND a.BANK_NAME = #{tEmployeeProject.bankName}
			</if>
			<if test="tEmployeeProject.bankSubName != null and tEmployeeProject.bankSubName.trim() != ''">
				AND a.BANK_SUB_NAME = #{tEmployeeProject.bankSubName}
			</if>
			<if test="tEmployeeProject.bankNo != null and tEmployeeProject.bankNo.trim() != ''">
				AND a.BANK_NO = #{tEmployeeProject.bankNo}
			</if>
			<if test="tEmployeeProject.empNo != null and tEmployeeProject.empNo.trim() != ''">
				AND a.EMP_NO = #{tEmployeeProject.empNo}
			</if>
			<if test="tEmployeeProject.unitName != null and tEmployeeProject.unitName.trim() != ''">
				AND a.UNIT_NAME like concat('%',#{tEmployeeProject.unitName},'%')
			</if>
			<if test="tEmployeeProject.unitNo != null and tEmployeeProject.unitNo.trim() != ''">
				AND a.UNIT_NO = #{tEmployeeProject.unitNo}
			</if>
			<if test="tEmployeeProject.deptName != null and tEmployeeProject.deptName.trim() != ''">
				AND a.DEPT_NAME like concat('%',#{tEmployeeProject.deptName},'%')
			</if>
			<if test="tEmployeeProject.deptNo != null and tEmployeeProject.deptNo.trim() != ''">
				AND a.DEPT_NO = #{tEmployeeProject.deptNo}
			</if>
			<if test="tEmployeeProject.businessPrimaryType != null and tEmployeeProject.businessPrimaryType.trim() != ''">
				AND a.BUSINESS_PRIMARY_TYPE = #{tEmployeeProject.businessPrimaryType}
			</if>
			<if test="tEmployeeProject.businessSecondType != null and tEmployeeProject.businessSecondType.trim() != ''">
				AND a.BUSINESS_SECOND_TYPE = #{tEmployeeProject.businessSecondType}
			</if>
			<if test="tEmployeeProject.businessThirdType != null and tEmployeeProject.businessThirdType.trim() != ''">
				AND a.BUSINESS_THIRD_TYPE = #{tEmployeeProject.businessThirdType}
			</if>
			<if test="tEmployeeProject.contractType != null and tEmployeeProject.contractType.trim() != ''">
				AND a.CONTRACT_TYPE = #{tEmployeeProject.contractType}
			</if>
			<if test="tEmployeeProject.workingHours != null and tEmployeeProject.workingHours.trim() != ''">
				AND a.WORKING_HOURS = #{tEmployeeProject.workingHours}
			</if>
			<if test="tEmployeeProject.post != null and tEmployeeProject.post.trim() != ''">
				AND a.POST = #{tEmployeeProject.post}
			</if>
			<if test="tEmployeeProject.enjoinDate != null">
				AND a.ENJOIN_DATE = #{tEmployeeProject.enjoinDate}
			</if>
			<if test="tEmployeeProject.tryPeriod != null and tEmployeeProject.tryPeriod.trim() != ''">
				AND a.TRY_PERIOD = #{tEmployeeProject.tryPeriod}
			</if>
			<if test="tEmployeeProject.projectSource != null and tEmployeeProject.projectSource.trim() != ''">
				AND a.PROJECT_SOURCE = #{tEmployeeProject.projectSource}
			</if>
			<if test="tEmployeeProject.proStatus != null">
				AND a.STATUS = #{tEmployeeProject.proStatus}
			</if>
			<if test="tEmployeeProject.createName != null and tEmployeeProject.createName.trim() != ''">
				AND a.CREATE_NAME = #{tEmployeeProject.createName}
			</if>
			<if test="tEmployeeProject.createTime != null">
				AND a.CREATE_TIME = #{tEmployeeProject.createTime}
			</if>
			<if test="tEmployeeProject.remark != null and tEmployeeProject.remark.trim() != ''">
				AND a.REMARK = #{tEmployeeProject.remark}
			</if>
			<if test="tEmployeeProject.contractStatus != null">
				AND a.CONTRACT_STATUS = #{tEmployeeProject.contractStatus}
			</if>
			<if test="tEmployeeProject.empPhone != null and tEmployeeProject.empPhone.trim() != ''">
				AND b.EMP_PHONE = #{tEmployeeProject.empPhone}
			</if>
			<if test="tEmployeeProject.insuranceStatus != null">
				AND a.INSURANCE_STATUS = #{tEmployeeProject.insuranceStatus}
			</if>
			<if test="tEmployeeProject.socialStatus != null">
				AND a.SOCIAL_STATUS = #{tEmployeeProject.socialStatus}
			</if>
			<if test="tEmployeeProject.fundStatus != null">
				AND a.FUND_STATUS = #{tEmployeeProject.fundStatus}
			</if>
			<if test="tEmployeeProject.salaryStatus != null">
				AND a.SALARY_STATUS = #{tEmployeeProject.salaryStatus}
			</if>
			<if test="tEmployeeProject.fileProvince != null">
				AND b.FILE_PROVINCE = #{tEmployeeProject.fileProvince}
			</if>
			<if test="tEmployeeProject.fileCity != null">
				AND b.FILE_CITY = #{tEmployeeProject.fileCity}
			</if>
			<if test="tEmployeeProject.fileTown != null">
				AND b.FILE_TOWN = #{tEmployeeProject.fileTown}
			</if>
			<if test="tEmployeeProject.socialProvince != null">
				AND a.SOCIAL_PROVINCE = #{tEmployeeProject.socialProvince}
			</if>
			<if test="tEmployeeProject.socialCity != null">
				AND a.SOCIAL_CITY = #{tEmployeeProject.socialCity}
			</if>
			<if test="tEmployeeProject.socialTown != null">
				AND a.SOCIAL_TOWN = #{tEmployeeProject.socialTown}
			</if>
			<if test="tEmployeeProject.idList != null">
				AND a.id in
				<foreach item="idStr" index="index" collection="tEmployeeProject.idList" open="(" separator="," close=")">
					#{idStr}
				</foreach>
			</if>
			<if test="tEmployeeProject.empNatureArray != null and tEmployeeProject.empNatureArray.length > 0">
				AND a.EMP_NATRUE in
				<foreach item="idStr" index="index" collection="tEmployeeProject.empNatureArray" open="(" separator=","
						 close=")">
					#{idStr}
				</foreach>
			</if>
			<if test="tEmployeeProject.contractStatusArray != null and tEmployeeProject.contractStatusArray.length > 0">
				AND a.CONTRACT_STATUS in
				<foreach item="idStr" index="index" collection="tEmployeeProject.contractStatusArray" open="("
						 separator="," close=")">
					#{idStr}
				</foreach>
			</if>
			<if test="tEmployeeProject.insuranceStatusArray != null and tEmployeeProject.insuranceStatusArray.length > 0">
				AND a.INSURANCE_STATUS in
				<foreach item="idStr" index="index" collection="tEmployeeProject.insuranceStatusArray" open="("
						 separator="," close=")">
					#{idStr}
				</foreach>
			</if>
			<if test="tEmployeeProject.socialStatusArray != null and tEmployeeProject.socialStatusArray.length > 0">
				AND a.SOCIAL_STATUS in
				<foreach item="idStr" index="index" collection="tEmployeeProject.socialStatusArray" open="("
						 separator="," close=")">
					#{idStr}
				</foreach>
			</if>
			<if test="tEmployeeProject.fundStatusArray != null and tEmployeeProject.fundStatusArray.length > 0">
				AND a.FUND_STATUS in
				<foreach item="idStr" index="index" collection="tEmployeeProject.fundStatusArray" open="("
						 separator="," close=")">
					#{idStr}
				</foreach>
			</if>
			<if test="tEmployeeProject.empLabel != null">
				AND a.EMP_LABEL = #{tEmployeeProject.empLabel}
			</if>
			<if test="tEmployeeProject.createTimeStart != null">
				AND a.CREATE_TIME <![CDATA[ >= ]]> #{tEmployeeProject.createTimeStart}
			</if>
			<if test="tEmployeeProject.createTimeEnd != null">
				AND a.CREATE_TIME <![CDATA[ <= ]]> #{tEmployeeProject.createTimeEnd}
			</if>

			<if test="tEmployeeProject.isComplete != null and tEmployeeProject.isComplete.trim() != ''">
				AND a.IS_COMPLETE = #{tEmployeeProject.isComplete}
			</if>
		</if>
	</sql>

	<update id="updateExcelEmpProject" parameterType="java.util.List">
		update t_employee_project
		<trim prefix="set" suffixOverrides=",">
			<trim prefix="EMP_LABEL =case" suffix="end,">
				<foreach collection="list" item="i" index="index">
					<if test="i.empLabel!=null">
						when ID=#{i.id} then #{i.empLabel}
					</if>
				</foreach>
			</trim>
			<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_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="PROJECT_STATUS =case" suffix="end,">
				<foreach collection="list" item="i" index="index">
					when ID=#{i.id} then 0
				</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 prefix="DEPT_NO =case" suffix="end,">
				<foreach collection="list" item="i" index="index">
					<if test="i.deptNo!=null">
						when ID=#{i.id} then #{i.deptNo}
					</if>
				</foreach>
			</trim>
			<trim prefix="CONTRACT_TYPE =case" suffix="end,">
				<foreach collection="list" item="i" index="index">
					<if test="i.contractType!=null">
						when ID=#{i.id} then #{i.contractType}
					</if>
				</foreach>
			</trim>
			<trim prefix="WORKING_HOURS =case" suffix="end,">
				<foreach collection="list" item="i" index="index">
					<if test="i.workingHours!=null">
						when ID=#{i.id} then #{i.workingHours}
					</if>
				</foreach>
			</trim>
			<trim prefix="POST =case" suffix="end,">
				<foreach collection="list" item="i" index="index">
					<if test="i.post!=null">
						when ID=#{i.id} then #{i.post}
					</if>
				</foreach>
			</trim>
			<trim prefix="TRY_PERIOD =case" suffix="end,">
				<foreach collection="list" item="i" index="index">
					<if test="i.tryPeriod!=null">
						when ID=#{i.id} then #{i.tryPeriod}
					</if>
				</foreach>
			</trim>
			<trim prefix="ENJOIN_DATE =case" suffix="end,">
				<foreach collection="list" item="i" index="index">
					<if test="i.enjoinDate!=null">
						when ID=#{i.id} then #{i.enjoinDate}
					</if>
				</foreach>
			</trim>
			<trim prefix="BANK_NAME =case" suffix="end,">
				<foreach collection="list" item="i" index="index">
					<if test="i.bankName!=null">
						when ID=#{i.id} then #{i.bankName}
					</if>
				</foreach>
			</trim>
			<trim prefix="BANK_NO =case" suffix="end,">
				<foreach collection="list" item="i" index="index">
					<if test="i.bankNo!=null">
						when ID=#{i.id} then #{i.bankNo}
					</if>
				</foreach>
			</trim>
			<trim prefix="BANK_SUB_NAME =case" suffix="end,">
				<foreach collection="list" item="i" index="index">
					<if test="i.bankSubName!=null">
						when ID=#{i.id} then #{i.bankSubName}
					</if>
				</foreach>
			</trim>
		</trim>
		where
		<foreach collection="list" separator="or" item="i" index="index" >
			ID=#{i.id}
		</foreach>
	</update>
	<insert id="insertExcelEmpProject" parameterType="java.util.List">
		insert into t_employee_project
		(ID,EMP_ID,EMP_NATRUE,EMP_NAME,EMP_IDCARD,STATUS,PROJECT_STATUS,EMP_CODE,DEPT_NO,CONTRACT_TYPE,POST,
		DEPT_ID,DEPT_NAME,UNIT_ID,UNIT_NAME,UNIT_NO,EMP_NO,TRY_PERIOD,ENJOIN_DATE,BANK_NAME,BANK_NO,BANK_SUB_NAME,
		BUSINESS_PRIMARY_TYPE,BUSINESS_SECOND_TYPE,BUSINESS_THIRD_TYPE,WORKING_HOURS,PROJECT_SOURCE,DELETE_FLAG,
		CREATE_BY,CREATE_NAME,CREATE_TIME,INSURANCE_STATUS,SOCIAL_STATUS,FUND_STATUS,EMP_LABEL)
		VALUES
		<foreach collection="list" item="item" index="index" separator=",">
			(
			#{item.id,jdbcType=VARCHAR},
			#{item.empId,jdbcType=VARCHAR},
			#{item.empNatrue,jdbcType=VARCHAR},
			#{item.empName,jdbcType=VARCHAR},
			#{item.empIdcard,jdbcType=VARCHAR},
			#{item.status,jdbcType=VARCHAR},
			0,
			#{item.empCode,jdbcType=VARCHAR},
			#{item.deptNo,jdbcType=VARCHAR},
			#{item.contractType,jdbcType=VARCHAR},
			#{item.post,jdbcType=VARCHAR},
			#{item.deptId,jdbcType=VARCHAR},
			#{item.deptName,jdbcType=VARCHAR},
			#{item.unitId,jdbcType=VARCHAR},
			#{item.unitName,jdbcType=VARCHAR},
			#{item.unitNo,jdbcType=VARCHAR},
			#{item.empNo,jdbcType=VARCHAR},
			#{item.tryPeriod,jdbcType=VARCHAR},
			#{item.enjoinDate,jdbcType=DATE},
			#{item.bankName,jdbcType=VARCHAR},
			#{item.bankNo,jdbcType=VARCHAR},
			#{item.bankSubName,jdbcType=VARCHAR},
			#{item.businessPrimaryType,jdbcType=VARCHAR},
			#{item.businessSecondType,jdbcType=VARCHAR},
			#{item.businessThirdType,jdbcType=VARCHAR},
			#{item.workingHours,jdbcType=VARCHAR},
			#{item.projectSource,jdbcType=VARCHAR},
			#{item.deleteFlag,jdbcType=VARCHAR},
			#{item.createBy,jdbcType=VARCHAR},
			#{item.createName,jdbcType=VARCHAR},
			now(),
			0,
			0,
			0,
			#{item.empLabel,jdbcType=VARCHAR}
			)
		</foreach>
	</insert>
	<update id="updateReduceEmpProject" parameterType="java.util.List">
		update t_employee_project
		<trim prefix="set" suffixOverrides=",">
			<trim prefix="EMP_LABEL =case" suffix="end,">
				<foreach collection="list" item="i" index="index">
					when ID=#{i.id} then #{i.empLabel}
				</foreach>
			</trim>
			<trim prefix="LEAVE_TIME =case" suffix="end,">
				<foreach collection="list" item="i" index="index">
					when ID=#{i.id} then now()
				</foreach>
			</trim>
			<trim prefix="LEAVE_REASON =case" suffix="end,">
				<foreach collection="list" item="i" index="index">
					<if test="i.leaveReason!=null">
						when ID=#{i.id} then #{i.leaveReason}
					</if>
				</foreach>
			</trim>
			<trim prefix="LEAVE_USER =case" suffix="end,">
				<foreach collection="list" item="i" index="index">
					<if test="i.leaveUser!=null">
						when ID=#{i.id} then #{i.leaveUser}
					</if>
				</foreach>
			</trim>
			<trim prefix="LEAVE_REMARK =case" suffix="end,">
				<foreach collection="list" item="i" index="index">
					<if test="i.leaveRemark!=null">
						when ID=#{i.id} then #{i.leaveRemark}
					</if>
				</foreach>
			</trim>
			<trim prefix="PROJECT_STATUS =case" suffix="end,">
				<foreach collection="list" item="i" index="index">
					when ID=#{i.id} then 1
				</foreach>
			</trim>
		</trim>
		where
		<foreach collection="list" separator="or" item="i" index="index" >
			ID=#{i.id}
		</foreach>
	</update>
	<!--查询部门编码和最大员工数-->
	<select id="findEmployeeMaxOnlyNoByDepId" resultType="java.lang.String">
		select MAX(EMP_NO) from t_employee_project where
			id not like '%abc' and
			EMP_NO like concat(#{deptNo},'%')
	</select>

	<update id="updateEmpIdById" parameterType="java.util.List">
		update t_employee_project
		<trim prefix="set" suffixOverrides=",">
		<trim prefix="EMP_ID =case" suffix="end,">
			<foreach collection="list" item="i" index="index">
				<if test="i.id!=null">
					when EMP_IDCARD=#{i.empIdcard} then #{i.id}
				</if>
			</foreach>
		</trim>
		</trim>
		where
		<foreach collection="list" separator="or" item="i" index="index" >
			EMP_IDCARD=#{i.empIdcard}
		</foreach>
	</update>

	<!--tEmployeeInfo简单分页查询-->
	<select id="getTEmployeeProjectInfoPage" resultMap="tEmployeeProjectMap">
	SELECT
		<include refid="Base_Column_List"/>
		FROM t_employee_project a
		left join t_employee_info b on a.EMP_ID = b.id
		<where>
			a.DELETE_FLAG = '0'
			<include refid="tEmployeeProject_where"/>
			<if test="tEmployeeProject.authSql != null and tEmployeeProject.authSql.trim() != ''">
				${tEmployeeProject.authSql}
			</if>
		</where>
		order by a.CREATE_TIME desc
	</select>

	<!--tEmployeeInfo简单分页查询-->
	<select id="getProjectByDetpNo" resultMap="tEmployeeProjectMap">
		SELECT
		a.ID,
		a.EMP_NAME,
		a.EMP_IDCARD,
		b.EMP_PHONE
		FROM t_employee_project a left join t_employee_info b on a.EMP_ID = b.id
		where
		a.DELETE_FLAG = '0' and a.DEPT_NO = #{tEmployeeProject.deptNo}
		and a.PROJECT_STATUS = 0
		<if test="tEmployeeProject.empIdcard != null and tEmployeeProject.empIdcard.trim() != ''">
			AND (a.EMP_IDCARD like concat('%',#{tEmployeeProject.empIdcard},'%') or a.EMP_NAME like concat('%',#{tEmployeeProject.empIdcard}))
		</if>
			order by a.CREATE_TIME desc
	</select>

	<!--tEmployeeInfo导出查询改造 fxj 2022-11-03-->
	<select id="noPageDiy" resultMap="tEmployeeProjectExportMap">
		SELECT
		<include refid="Base_Export_List"/>,
		a10.AREA_NAME as "CONTACT_PROVINCE",
		a11.AREA_NAME as "CONTACT_CITY",
		a12.AREA_NAME as "CONTACT_TOWN",
		b.contact_address
		FROM t_employee_project a
		left join t_employee_info b on a.EMP_ID = b.id
		LEFT JOIN (
		SELECT
		t1.EMP_ID ,t1.CONTRACT_PARTY,t1.IN_USE,t1.DEPT_NO
		FROM
		t_employee_contract_info t1
<!--		INNER JOIN ( SELECT EMP_ID, MAX( CREATE_TIME ) CREATE_TIME FROM t_employee_contract_info GROUP BY EMP_ID ) t2 ON t2.EMP_ID = t1.EMP_ID-->
<!--		AND t2.CREATE_TIME = t1.CREATE_TIME-->
		WHERE t1.IN_USE='0'
		) AS t ON t.EMP_ID = a.EMP_ID AND a.DEPT_NO =t.DEPT_NO
		LEFT JOIN sys_area a1 on a1.id=b.ID_PROVINCE
		LEFT JOIN sys_area a2 on a2.id=b.ID_CITY
		LEFT JOIN sys_area a3 on a3.id=b.ID_TOWN
		LEFT JOIN sys_area a4 on a4.id=b.FILE_PROVINCE
		LEFT JOIN sys_area a5 on a5.id=b.FILE_CITY
		LEFT JOIN sys_area a6 on a6.id=b.FILE_TOWN
		LEFT JOIN sys_area a7 on a7.id=a.SOCIAL_PROVINCE
		LEFT JOIN sys_area a8 on a8.id=a.SOCIAL_CITY
		LEFT JOIN sys_area a9 on a9.id=a.SOCIAL_TOWN
		LEFT JOIN sys_area a10 on a10.id=b.CONTACT_PROVINCE
		LEFT JOIN sys_area a11 on a11.id=b.CONTACT_CITY
		LEFT JOIN sys_area a12 on a12.id=b.CONTACT_TOWN
		<where>
			a.DELETE_FLAG = '0'
			<include refid="exportTEmployeeProject_where"/>
			<if test="tEmployeeProject.authSql != null and tEmployeeProject.authSql.trim() != ''">
				${tEmployeeProject.authSql}
			</if>

		</where>

		order by a.CREATE_TIME desc
		<if test="tEmployeeProject != null">
			<if test="tEmployeeProject.limitStart != null">
				limit #{tEmployeeProject.limitStart},#{tEmployeeProject.limitEnd}
			</if>
		</if>
	</select>
	<!--tEmployeeInfo导出查询count改造 fxj 2022-11-03-->
	<select id="noPageCountDiy" resultType="java.lang.Integer">
		SELECT
		count(1)
		FROM t_employee_project a
		left join t_employee_info b on a.EMP_ID = b.id
		LEFT JOIN (
		SELECT
		t1.EMP_ID ,t1.CONTRACT_PARTY,t1.IN_USE,t1.DEPT_NO
		FROM
		t_employee_contract_info t1
		INNER JOIN ( SELECT EMP_ID, MAX( CREATE_TIME ) CREATE_TIME FROM t_employee_contract_info GROUP BY EMP_ID ) t2 ON t2.EMP_ID = t1.EMP_ID
		AND t2.CREATE_TIME = t1.CREATE_TIME
		WHERE
		t1.IN_USE='0'
		) AS t ON t.EMP_ID = a.EMP_ID AND a.DEPT_NO=t.DEPT_NO
		<where>
			a.DELETE_FLAG = '0'
			<include refid="exportTEmployeeProject_where"/>
			<if test="tEmployeeProject.authSql != null and tEmployeeProject.authSql.trim() != ''">
				${tEmployeeProject.authSql}
			</if>

		</where>
	</select>

	<!-- 更新项目档案商险状态 -->
	<update id="updateEmployeeInsuranceStatus" >
		update t_employee_project a set a.INSURANCE_STATUS = #{emp.status} where a.EMP_IDCARD = #{emp.idCard} and a.DEPT_NO = #{emp.deptNo}
	</update>

	<!-- 更新项目档案薪资状态 -->
	<update id="updateEmployeeSalaryStatus" >
		update t_employee_project a set a.SALARY_STATUS = #{emp.status} where a.EMP_IDCARD = #{emp.idCard} and a.DEPT_NO = #{emp.deptNo}
	</update>

	<!-- 每月1号更新项目档案薪资状态 -->
	<update id="everyMonthUpdateEmployeeSalaryStatus" >
		update t_employee_project 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 and a.dept_id=e.DEPT_ID
			)
	</update>

	<!-- 每天更新人员档案、项目档案的过期合同为2不在用 -->
	<update id="everyDayUpdateEmployeContractStatus" >
		update t_employee_project e
		join t_employee_contract_info c on e.EMP_IDCARD=c.EMP_IDCARD and e.DEPT_NO=c.DEPT_NO 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")
	</update>

	<resultMap id="businessEmployeeExtendListVO" type="com.yifu.cloud.plus.v1.yifu.archives.vo.BusinessEmployeeExtendListVO">
	</resultMap>
	<select id="getTEmployeeBusinessPage" resultMap="businessEmployeeExtendListVO">
		SELECT
		a.ID,
		a.EMP_ID as empId,
		b.ID as extendId,
		a.EMP_NAME,
		a.EMP_IDCARD,
		if(b.BUSINESS_ENJOIN_DATE is not null, b.BUSINESS_ENJOIN_DATE, a.ENJOIN_DATE) as BUSINESS_ENJOIN_DATE,
		a.DEPT_ID SETTLE_DOMAIN,
		b.DEPART_NAME,
		b.DEPART_ID,
		if(b.BUSINESS_POST is not null, b.BUSINESS_POST, a.POST) as BUSINESS_POST,
		b.BUSINESS_TELECOM_NUMBER,
		b.EMPLOYEE_TAGS,
		if(b.BUSINESS_WORKING_STATUS is not null, b.BUSINESS_WORKING_STATUS, a.PROJECT_STATUS) as BUSINESS_WORKING_STATUS,
		a.PROJECT_STATUS WORK_FLAG,
		a.STATUS WORKING_STATUS_SUB,
		b.BUSINESS_LEAVE_DATE,
		b.DOCUMENT_TYPE,
		c.DEPART_NAME as SETTLE_DOMAIN_NAME
		FROM t_employee_project a
		left join t_business_employee_extend_info b on a.EMP_IDCARD = b.EMP_IDCARD
		left join t_settle_domain c on a.DEPT_ID = c.ID
		where a.DELETE_FLAG = '0' and c.DELETE_FLAG = '0' and a.PROJECT_STATUS = '0'
		<include refid="BusinessQuery"/>
		order by a.ENJOIN_DATE DESC
	</select>

	<sql id="BusinessQuery">
		<if test="query.empIdcard != null and query.empIdcard.trim() != ''">
			AND a.EMP_IDCARD like CONCAT('%', #{query.empIdcard},'%')
		</if>
		<if test="query.empName != null and query.empName.trim() != ''">
			AND a.EMP_NAME like CONCAT('%',#{query.empName},'%')
		</if>
		<if test="query.settleDomainId != null and query.settleDomainId.trim() != ''">
			AND a.DEPT_ID = #{query.settleDomainId}
		</if>
		<if test="query.businessPost != null and query.businessPost != ''">
			AND b.BUSINESS_POST like CONCAT('%',#{query.businessPost},'%')
		</if>
		<if test="query.departName != null and query.departName.trim() != ''">
			AND b.DEPART_NAME like CONCAT(#{query.departName},'%')
		</if>
		<if test="query.departId != null and query.departId != 1">
			AND (b.DEPART_ID = #{query.departId} or b.DEPART_CODE like concat('%-', #{query.departId},'-%'))
		</if>
		<if test="query.businessTelecomNumber != null and query.businessTelecomNumber.trim() != ''">
			AND b.BUSINESS_TELECOM_NUMBER like CONCAT('%',#{query.businessTelecomNumber} ,'%')
		</if>
		<!-- businessWorkingStatus: 0 在职  1 离职  -->
		<if test="query.businessWorkingStatus != null and query.businessWorkingStatus.trim() != ''">
			<!-- 0查询在职 -->
			<if test="query.businessWorkingStatus == '0'.toString()">
				AND (b.BUSINESS_WORKING_STATUS is null or b.BUSINESS_WORKING_STATUS = '0')
			</if>
			<!-- 1查询离职 -->
			<if test="query.businessWorkingStatus == '1'.toString()">
				AND b.BUSINESS_WORKING_STATUS = '1'
			</if>
		</if>
		<if test="query.employeeTagName != null and query.employeeTagName.trim() != ''">
			AND b.EMPLOYEE_TAGS like CONCAT('%',#{query.employeeTagName},'%')
		</if>
		<!-- 是否持证 0否 1是 -->
		<if test="query.documentStatus != null and query.documentStatus.trim() != '' and query.documentStatus == '0'.toString()">
			AND if(b.DOCUMENT_TYPE = '', b.DOCUMENT_TYPE = '', b.DOCUMENT_TYPE is null)
		</if>
		<if test="query.documentStatus != null and query.documentStatus.trim() != '' and query.documentStatus == '1'.toString()">
			AND b.DOCUMENT_TYPE is NOT NULL AND b.DOCUMENT_TYPE <![CDATA[<>]]> ''
		</if>
		<if test="query.settleDomainIds != null  and query.settleDomainIds.size >0">
			and a.DEPT_ID in
			<foreach item="settleDomainId" index="index" collection="query.settleDomainIds" open="(" separator="," close=")">
				#{settleDomainId}
			</foreach>
		</if>
	</sql>

	<resultMap id="businessEmployeeExtentExcelVO" type="com.yifu.cloud.plus.v1.yifu.archives.vo.BusinessEmployeeExtentExcelVO">
	</resultMap>
	<select id="getTEmployeeBusinessList" resultMap="businessEmployeeExtentExcelVO">
		SELECT
			a.EMP_NAME,
			a.EMP_IDCARD,
			b.BUSINESS_TELECOM_NUMBER,
			b.DEPART_NAME,
			b.DEPART_ID,
			a.DEPT_ID SETTLE_DOMAIN,
			b.BUSINESS_POST,
			b.FIRST_DEGREE_AND_MAJOR,
			b.FIRST_DEGREE_GRADUATE_SCHOOL,
			b.HIGHEST_DEGREE_AND_MAJOR,
			b.HIGHEST_DEGREE_GRADUATE_SCHOOL,
			b.CONTACT_INFO,
			b.ARCHIVES_ADDR,
			if(b.BUSINESS_ENJOIN_DATE is not null, b.BUSINESS_ENJOIN_DATE, a.ENJOIN_DATE) as BUSINESS_ENJOIN_DATE,
			b.EMPLOYEE_TAGS,
			if(b.BUSINESS_WORKING_STATUS is not null, b.BUSINESS_WORKING_STATUS, a.PROJECT_STATUS) as BUSINESS_WORKING_STATUS,
			a.PROJECT_STATUS WORK_FLAG,
			a.STATUS WORKING_STATUS_SUB,
			if(b.BUSINESS_LEAVE_DATE is not null, b.BUSINESS_LEAVE_DATE, a.LEAVE_TIME) as BUSINESS_LEAVE_DATE,
			b.DOCUMENT_TYPE,
			c.DEPART_NAME as SETTLE_DOMAIN_NAME
		FROM t_employee_project a
		left join t_business_employee_extend_info b on a.EMP_IDCARD = b.EMP_IDCARD
		left join t_settle_domain c on a.DEPT_ID = c.ID
		where a.DELETE_FLAG = '0' and c.DELETE_FLAG = '0' and a.PROJECT_STATUS = '0'
		    <include refid="BusinessQuery"/>
		order by a.ENJOIN_DATE DESC
	</select>
	<update id="updateSocialAndFundStatusById" >
		update t_employee_project a
		<trim prefix="set" suffixOverrides=",">
			<if test="project.id != null">a.id=a.id,</if>
			<if test="project.socialStatus != null">SOCIAL_STATUS=#{project.socialStatus},</if>
			<if test="project.fundStatus != null">FUND_STATUS=#{project.fundStatus},</if>
		</trim>
		WHERE a.id=#{project.id}
	</update>

	<update id="updateChangeById" >
		update t_employee_project a
		<trim prefix="set" suffixOverrides=",">
			<if test="project.socialStatus != null">SOCIAL_STATUS=#{project.socialStatus},</if>
			<if test="project.fundStatus != null">FUND_STATUS=#{project.fundStatus},</if>
			<if test="project.socialProvince == null">SOCIAL_PROVINCE= null,</if>
			<if test="project.socialCity == null">SOCIAL_CITY=null,</if>
			<if test="project.socialTown == null">SOCIAL_TOWN=null,</if>
			<if test="project.contractStatus != null">CONTRACT_STATUS=#{project.contractStatus},</if>
			<if test="project.contractType == null">CONTRACT_TYPE=null,</if>
		</trim>
		WHERE a.id=#{project.id}
	</update>
	<update id="updateContractStatus" >
		update t_employee_project a
		set CONTRACT_STATUS = #{status}
		WHERE a.EMP_ID=#{empId} and a.DEPT_ID=#{deptId}
	</update>
	<update id="updatePostByEmpDeptNo" >
		update t_employee_project a
		set
		    <if test="post != null and post !=''">
				a.POST = #{post}
			<if test="tryPeriod != null and tryPeriod !=''">
				,
			</if>
			</if>
			<if test="tryPeriod != null and tryPeriod !=''">
				a.TRY_PERIOD = #{tryPeriod}
			</if>
		WHERE a.DEPT_NO=#{deptNo}  and a.EMP_IDCARD=#{empIdcard} and a.DELETE_FLAG ='0'
	</update>

	<!-- 批量刷新项目档案的是否完整状态 -->
	<update id="batchUpdateEmpProjectCompleteStatus">
		update t_employee_project a set a.IS_COMPLETE = #{isComplete} where a.id in
		<foreach item="item" index="index" collection="idList" open="(" separator="," close=")">
			#{item}
		</foreach>
	</update>

	<!-- 批量刷新项目档案的是否完整状态 -->
	<update id="batchUpdateEmpProjectCompleteStatusByType">
		update
		    t_employee_project a LEFT JOIN t_employee_info e on a.emp_id=e.id
		    set a.IS_COMPLETE = '0'
		where a.CONTRACT_TYPE = #{contractType}
		and a.IS_COMPLETE = '1' and a.DELETE_FLAG ='0' and a.EMP_NATRUE != '2' and a.PROJECT_STATUS = 0
		and e.EMP_PHONE is not null
		and a.CONTRACT_TYPE != ''
		and e.EMP_SEX is not null
		and e.EMP_BIRTHDAY is not null
		<if test="deptNo != null and deptNo.trim() != ''">
			and a.DEPT_NO = #{deptNo}
		</if>
	</update>

	<!-- 员工类型为代理的，默认完整 -->
	<update id="updateTrueCompleteByAgent">
		update t_employee_project a set a.IS_COMPLETE = '0' where a.EMP_NATRUE = '2' and a.IS_COMPLETE = '1'
	</update>

	<!-- 基础信息完整，有可能完整的 -->
	<select id="getNeedJudgeCompleteType" resultType="java.lang.String" >
		select
			a.CONTRACT_TYPE
		from t_employee_project a
		LEFT JOIN t_employee_info e on a.emp_id=e.id
		where a.IS_COMPLETE = '1' and a.DELETE_FLAG ='0' and a.EMP_NATRUE != '2' and a.PROJECT_STATUS = 0
		and e.EMP_PHONE is not null
		and a.CONTRACT_TYPE != ''
		and e.EMP_SEX is not null
		and e.EMP_BIRTHDAY is not null
		<if test="deptNo != null and deptNo.trim() != ''">
			and a.DEPT_NO = #{deptNo}
		</if>
		group by a.CONTRACT_TYPE
	</select>

	<!-- 基础信息完整，有可能完整的 -->
	<select id="getNeedNextJudgeCompleteByColumnAndEmpProjectId" resultType="java.lang.String" >
		select
			a.id
		from t_employee_project a
		LEFT JOIN t_employee_info e on a.emp_id=e.id
		<if test="tableInfo != null and tableInfo.trim() != ''">
			LEFT JOIN ${tableInfo}
		</if>
		where a.IS_COMPLETE = '1' and a.DELETE_FLAG ='0' and a.EMP_NATRUE != '2' and a.PROJECT_STATUS = 0
		and e.EMP_PHONE is not null
		and a.CONTRACT_TYPE != ''
		and e.EMP_SEX is not null
		and e.EMP_BIRTHDAY is not null
		<if test="type != null and type.trim() != ''">
			and a.CONTRACT_TYPE = #{type}
		</if>
		<if test="deptNo != null and deptNo.trim() != ''">
			and a.DEPT_NO = #{deptNo}
		</if>
		<if test="columnInfo != null and columnInfo.trim() != ''">
			and (${columnInfo}) = 1
		</if>
		<if test="empProjectIdList != null and empProjectIdList.size > 0">
			AND a.ID in
			<foreach item="items" index="index" collection="empProjectIdList" open="(" separator="," close=")">
				#{items}
			</foreach>
		</if>
		group by a.id
	</select>
</mapper>
