<?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.TEmpContractAlertMapper">

  <resultMap id="tEmpContractAlertMap" type="com.yifu.cloud.plus.v1.yifu.archives.entity.TEmpContractAlert">
	  <id property="id" column="ID"/>
	  <result property="empName" column="EMP_NAME"/>
	  <result property="empId" column="EMP_ID"/>
	  <result property="project" column="PROJECT"/>
	  <result property="projectNo" column="PROJECT_NO"/>
	  <result property="empIdcard" column="EMP_IDCARD"/>
	  <result property="empCode" column="EMP_CODE"/>
	  <result property="contractStart" column="CONTRACT_START"/>
	  <result property="contractEnd" column="CONTRACT_END"/>
	  <result property="fileProvince" column="FILE_PROVINCE"/>
	  <result property="fileCity" column="FILE_CITY"/>
	  <result property="fileTown" column="FILE_TOWN"/>
	  <result property="empType" column="EMP_TYPE"/>
	  <result property="contractType" column="CONTRACT_TYPE"/>
	  <result property="contractTerm" column="CONTRACT_TERM"/>
	  <result property="daysDue" column="DAYS_DUE"/>
	  <result property="dueFlag" column="DUE_FLAG"/>
	  <result property="daysOver" column="DAYS_OVER"/>
	  <result property="firstAlertTime" column="FIRST_ALERT_TIME"/>
	  <result property="alertPerson" column="ALERT_PERSON"/>
	  <result property="handleStatus" column="HANDLE_STATUS"/>
	  <result property="createBy" column="CREATE_BY"/>
	  <result property="updateBy" column="UPDATE_BY"/>
	  <result property="createName" column="CREATE_NAME"/>
	  <result property="createTime" column="CREATE_TIME"/>
	  <result property="updateTime" column="UPDATE_TIME"/>
	  <result property="isFeedback" column="IS_FEEDBACK"/>
	  <result property="feedbackReason" column="FEEDBACK_REASON"/>
	  <result property="feedbackDetail" column="FEEDBACK_DATAIL"/>
	  <result property="contractId" column="CONTRACT_ID"/>
	  <result property="applyNo" column="APPLY_NO"/>
	  <result property="workingHours" column="WORKING_HOURS"/>
	  <result property="post" column="POST"/>
	  <!--  合同自动化新增字段
	  <result property="expectedCollectionTime" column="EXPECTED_COLLECTION_TIME"/>
	  <result property="signType" column="SIGN_TYPE"/>
	  <result property="processStatus" column="PROCESS_STATUS"/>-->
	  <result property="csName" column="CS_NAME"/>
	  <result property="csLoginName" column="CS_LOGIN_NAME"/>
	  <result property="autoFlag" column="AUTO_FLAG"/>
  </resultMap>
	<resultMap id="tEmpContractAlertAutoMap" type="com.yifu.cloud.plus.v1.yifu.archives.vo.ContractAlertAutoVo">
		<id property="id" column="ID"/>
		<result property="empName" column="EMP_NAME"/>
		<result property="empId" column="EMP_ID"/>
		<result property="project" column="PROJECT"/>
		<result property="projectNo" column="PROJECT_NO"/>
		<result property="empIdcard" column="EMP_IDCARD"/>
		<result property="empCode" column="EMP_CODE"/>
		<result property="contractStart" column="CONTRACT_START"/>
		<result property="contractEnd" column="CONTRACT_END"/>
		<result property="fileProvince" column="FILE_PROVINCE"/>
		<result property="fileCity" column="FILE_CITY"/>
		<result property="fileTown" column="FILE_TOWN"/>
		<result property="empType" column="EMP_TYPE"/>
		<result property="contractType" column="CONTRACT_TYPE"/>
		<result property="contractTerm" column="CONTRACT_TERM"/>
		<result property="daysDue" column="DAYS_DUE"/>
		<result property="dueFlag" column="DUE_FLAG"/>
		<result property="daysOver" column="DAYS_OVER"/>
		<result property="firstAlertTime" column="FIRST_ALERT_TIME"/>
		<result property="alertPerson" column="ALERT_PERSON"/>
		<result property="handleStatus" column="HANDLE_STATUS"/>
		<result property="createBy" column="CREATE_BY"/>
		<result property="updateBy" column="UPDATE_BY"/>
		<result property="createName" column="CREATE_NAME"/>
		<result property="createTime" column="CREATE_TIME"/>
		<result property="updateTime" column="UPDATE_TIME"/>
		<result property="isFeedback" column="IS_FEEDBACK"/>
		<result property="feedbackReason" column="FEEDBACK_REASON"/>
		<result property="feedbackDetail" column="FEEDBACK_DATAIL"/>
		<result property="contractId" column="CONTRACT_ID"/>
		<result property="applyNo" column="APPLY_NO"/>
		<result property="workingHours" column="WORKING_HOURS"/>
		<result property="post" column="POST"/>
		<!--  合同自动化新增字段  -->
		<result property="expectedCollectionTime" column="EXPECTED_COLLECTION_TIME"/>
		<result property="signType" column="SIGN_TYPE"/>
		<result property="processStatus" column="PROCESS_STATUS"/>
		<result property="csName" column="CS_NAME"/>
		<result property="csLoginName" column="CS_LOGIN_NAME"/>
		<result property="autoFlag" column="AUTO_FLAG"/>
		<result property="errorInfo" column="ERROR_INFO"/>
		<result property="errorTime" column="ERROR_TIME"/>
		<result property="preId" column="preId"/>
	</resultMap>
    <update id="truncate">
		TRUNCATE t_emp_contract_alert
	</update>

<update id="changeFeedBackAll"  parameterType="com.yifu.cloud.plus.v1.yifu.archives.vo.ChangeFeedBackAllVo">
	update  t_emp_contract_alert a
	set a.IS_FEEDBACK=#{changeFeedBackAllVo.isFeedback},a.FEEDBACK_REASON=#{changeFeedBackAllVo.feedbackReason},a.FEEDBACK_DETAIL=#{changeFeedBackAllVo.feedbackDetail},
	a.FEEDBACK_PERSON=#{changeFeedBackAllVo.feedbackPerson},a.FEEDBACK_TIME=#{changeFeedBackAllVo.feedbackTime}
	where a.APPLY_NO in
	<foreach item="idStr" index="index" collection="changeFeedBackAllVo.ids" open="(" separator="," close=")">
		#{idStr}
	</foreach>
</update>

	<select id="getAlertInfoList" resultMap="tEmpContractAlertMap">
		select
			a.ID,
		    a.EMP_NAME,
			a.PROJECT_NO,
			a.APPLY_NO,
			a.EMP_IDCARD,
		    a.WORKING_HOURS,
		    a.post
		from t_emp_contract_alert a
		where a.EMP_IDCARD in
		<foreach item="idStr" index="index" collection="idCardList" open="(" separator="," close=")">
			#{idStr}
		</foreach>
	</select>
	<sql id="Base_Column_List">
		a.ID,
		a.EMP_NAME,
		a.EMP_ID,
		a.DEPT_ID,
		a.PROJECT,
		a.PROJECT_NO,
		a.EMP_IDCARD,
		a.EMP_CODE,
		a.CONTRACT_START,
		a.CONTRACT_END,
		a.FILE_PROVINCE,
		a.FILE_CITY,
		a.FILE_TOWN,
		a.EMP_TYPE,
		a.CONTRACT_TYPE,
		a.CONTRACT_TERM,
		a.DAYS_DUE,
		a.DUE_FLAG,
		a.DAYS_OVER,
		a.FIRST_ALERT_TIME,
		a.ALERT_PERSON,
		a.CREATE_BY,
		a.UPDATE_BY,
		a.CREATE_NAME,
		a.CREATE_TIME,
		a.UPDATE_TIME,
		a.HANDLE_STATUS,
		a.CONTRACT_ID,
		a.AUDIT_TIME,
		a.UNIT_ID,
		a.UNIT_NAME,
		a.UNIT_NO,
		a.EMP_PHONE,
		a.IS_FEEDBACK,
		a.FEEDBACK_PERSON,
		a.FEEDBACK_TIME,
		a.FEEDBACK_REASON,
		a.FEEDBACK_DETAIL,
		a.APPLY_NO,
		a.POST,
		a.WORKING_HOURS,
		a.CS_NAME,
		a.CS_LOGIN_NAME,
		a.AUTO_FLAG
	</sql>
	<sql id="tEmpContractAlert_where">
		<if test="tEmpContractAlert != null">
			<if test="tEmpContractAlert.id != null and tEmpContractAlert.id.trim() != ''">
				AND a.ID = #{tEmpContractAlert.id}
			</if>
			<if test="tEmpContractAlert.empName != null and tEmpContractAlert.empName.trim() != ''">
				AND a.EMP_NAME  like concat('%', #{tEmpContractAlert.empName}, '%')
			</if>
			<if test="tEmpContractAlert.empId != null and tEmpContractAlert.empId.trim() != ''">
				AND a.EMP_ID = #{tEmpContractAlert.empId}
			</if>
			<if test="tEmpContractAlert.deptId != null and tEmpContractAlert.deptId.trim() != ''">
				AND a.DEPT_ID = #{tEmpContractAlert.deptId}
			</if>
			<if test="tEmpContractAlert.project != null and tEmpContractAlert.project.trim() != ''">
				AND a.PROJECT like concat('%', #{tEmpContractAlert.project}, '%')
			</if>
			<if test="tEmpContractAlert.deptNoList != null and tEmpContractAlert.deptNoList.size > 0">
				AND a.PROJECT in
				<foreach item="idStr" index="index" collection="tEmpContractAlert.deptNoList" open="(" separator="," close=")">
					#{idStr}
				</foreach>
			</if>
			<if test="tEmpContractAlert.idList != null and tEmpContractAlert.idList.size > 0">
				AND a.id in
				<foreach item="id" index="index" collection="tEmpContractAlert.idList" open="(" separator="," close=")">
					#{id}
				</foreach>
			</if>
			<if test="tEmpContractAlert.projectNo != null and tEmpContractAlert.projectNo.trim() != ''">
				AND a.PROJECT_NO = #{tEmpContractAlert.projectNo}
			</if>
			<if test="tEmpContractAlert.empIdcard != null and tEmpContractAlert.empIdcard.trim() != ''">
				AND a.EMP_IDCARD = #{tEmpContractAlert.empIdcard}
			</if>
			<if test="tEmpContractAlert.empCode != null and tEmpContractAlert.empCode.trim() != ''">
				AND a.EMP_CODE = #{tEmpContractAlert.empCode}
			</if>
			<if test="tEmpContractAlert.contractStart != null">
				AND a.CONTRACT_START = #{tEmpContractAlert.contractStart}
			</if>
			<if test="tEmpContractAlert.contractEnd != null">
				AND a.CONTRACT_END = #{tEmpContractAlert.contractEnd}
			</if>
			<if test="tEmpContractAlert.fileProvince != null and tEmpContractAlert.fileProvince.trim() != ''">
				AND a.FILE_PROVINCE = #{tEmpContractAlert.fileProvince}
			</if>
			<if test="tEmpContractAlert.fileCity != null and tEmpContractAlert.fileCity.trim() != ''">
				AND a.FILE_CITY = #{tEmpContractAlert.fileCity}
			</if>
			<if test="tEmpContractAlert.fileTown != null and tEmpContractAlert.fileTown.trim() != ''">
				AND a.FILE_TOWN = #{tEmpContractAlert.fileTown}
			</if>
			<if test="tEmpContractAlert.empType != null and tEmpContractAlert.empType.trim() != ''">
				AND a.EMP_TYPE = #{tEmpContractAlert.empType}
			</if>
			<if test="tEmpContractAlert.contractType != null and tEmpContractAlert.contractType.trim() != ''">
				AND a.CONTRACT_TYPE = #{tEmpContractAlert.contractType}
			</if>
			<if test="tEmpContractAlert.contractTerm != null and tEmpContractAlert.contractTerm.trim() != ''">
				AND a.CONTRACT_TERM = #{tEmpContractAlert.contractTerm}
			</if>
			<if test="tEmpContractAlert.daysDue != null">
				AND a.DAYS_DUE = #{tEmpContractAlert.daysDue}
			</if>
			<if test="tEmpContractAlert.dueFlag != null and tEmpContractAlert.dueFlag.trim() != ''">
				AND a.DUE_FLAG = #{tEmpContractAlert.dueFlag}
			</if>
			<if test="tEmpContractAlert.daysOver != null">
				AND a.DAYS_OVER = #{tEmpContractAlert.daysOver}
			</if>
			<if test="tEmpContractAlert.firstAlertTime != null">
				AND a.FIRST_ALERT_TIME = #{tEmpContractAlert.firstAlertTime}
			</if>
			<if test="tEmpContractAlert.alertPerson != null and tEmpContractAlert.alertPerson.trim() != ''">
				AND a.ALERT_PERSON = #{tEmpContractAlert.alertPerson}
			</if>
			<if test="tEmpContractAlert.createBy != null and tEmpContractAlert.createBy.trim() != ''">
				AND a.CREATE_BY = #{tEmpContractAlert.createBy}
			</if>
			<if test="tEmpContractAlert.updateBy != null and tEmpContractAlert.updateBy.trim() != ''">
				AND a.UPDATE_BY = #{tEmpContractAlert.updateBy}
			</if>
			<if test="tEmpContractAlert.createName != null and tEmpContractAlert.createName.trim() != ''">
				AND a.CREATE_NAME = #{tEmpContractAlert.createName}
			</if>
			<if test="tEmpContractAlert.createTime != null">
				AND a.CREATE_TIME = #{tEmpContractAlert.createTime}
			</if>
			<if test="tEmpContractAlert.updateTime != null">
				AND a.UPDATE_TIME = #{tEmpContractAlert.updateTime}
			</if>
			<if test="tEmpContractAlert.handleStatus != null and tEmpContractAlert.handleStatus.trim() != ''">
				AND a.HANDLE_STATUS = #{tEmpContractAlert.handleStatus}
			</if>
			<if test="tEmpContractAlert.contractId != null and tEmpContractAlert.contractId.trim() != ''">
				AND a.CONTRACT_ID = #{tEmpContractAlert.contractId}
			</if>
			<if test="tEmpContractAlert.auditTime != null">
				AND a.AUDIT_TIME = #{tEmpContractAlert.auditTime}
			</if>
			<if test="tEmpContractAlert.unitId != null and tEmpContractAlert.unitId.trim() != ''">
				AND a.UNIT_ID = #{tEmpContractAlert.unitId}
			</if>
			<if test="tEmpContractAlert.unitName != null and tEmpContractAlert.unitName.trim() != ''">
				AND a.UNIT_NAME = #{tEmpContractAlert.unitName}
			</if>
			<if test="tEmpContractAlert.unitNo != null and tEmpContractAlert.unitNo.trim() != ''">
				AND a.UNIT_NO = #{tEmpContractAlert.unitNo}
			</if>
			<if test="tEmpContractAlert.empPhone != null and tEmpContractAlert.empPhone.trim() != ''">
				AND a.EMP_PHONE = #{tEmpContractAlert.empPhone}
			</if>
			<if test="tEmpContractAlert.isFeedback != null">
				AND a.IS_FEEDBACK = #{tEmpContractAlert.isFeedback}
			</if>
			<if test="tEmpContractAlert.feedbackPerson != null and tEmpContractAlert.feedbackPerson.trim() != ''">
				AND a.FEEDBACK_PERSON = #{tEmpContractAlert.feedbackPerson}
			</if>
			<if test="tEmpContractAlert.feedbackTime != null">
				AND a.FEEDBACK_TIME = #{tEmpContractAlert.feedbackTime}
			</if>
			<if test="tEmpContractAlert.feedbackReason != null and tEmpContractAlert.feedbackReason.trim() != ''">
				AND a.FEEDBACK_REASON = #{tEmpContractAlert.feedbackReason}
			</if>
			<if test="tEmpContractAlert.feedbackDetail != null and tEmpContractAlert.feedbackDetail.trim() != ''">
				AND a.FEEDBACK_DETAIL = #{tEmpContractAlert.feedbackDetail}
			</if>
			<if test="tEmpContractAlert.applyNo != null and tEmpContractAlert.applyNo.trim() != ''">
				AND a.APPLY_NO = #{tEmpContractAlert.applyNo}
			</if>
			<if test="tEmpContractAlert.post != null and tEmpContractAlert.post.trim() != ''">
				AND a.POST = #{tEmpContractAlert.post}
			</if>
			<if test="tEmpContractAlert.workingHours != null and tEmpContractAlert.workingHours.trim() != ''">
				AND a.WORKING_HOURS = #{tEmpContractAlert.workingHours}
			</if>
			<if test="tEmpContractAlert.expectedCollectionTime != null">
				AND b.EXPECTED_COLLECTION_TIME = #{tEmpContractAlert.expectedCollectionTime}
			</if>
			<if test="tEmpContractAlert.signType != null">
				AND b.SIGN_TYPE = #{tEmpContractAlert.signType}
			</if>
			<if test="tEmpContractAlert.processStatus != null and tEmpContractAlert.processStatus.trim() != ''">
				AND IFNULL(b.PROCESS_STATUS,'0') = #{tEmpContractAlert.processStatus}
			</if>

			<if test="tEmpContractAlert.processStatusList != null and tEmpContractAlert.processStatusList.size>0">
				AND IFNULL(b.PROCESS_STATUS,'0') IN
				<foreach collection="tEmpContractAlert.processStatusList" item="item" index="index" open="(" separator="," close=")">
					#{item}
				</foreach>
			</if>
			<if test="tEmpContractAlert.csName != null and tEmpContractAlert.csName.trim() != ''">
				AND a.CS_NAME like CONCAT('%',#{tEmpContractAlert.csName},'%')
			</if>
			<if test="tEmpContractAlert.expectedCollectionTimeStart != null">
				AND b.EXPECTED_COLLECTION_TIME <![CDATA[ >= ]]> #{tEmpContractAlert.expectedCollectionTimeStart}
			</if>
			<if test="tEmpContractAlert.expectedCollectionTimeEnd != null">
				AND b.EXPECTED_COLLECTION_TIME <![CDATA[ <= ]]> #{tEmpContractAlert.expectedCollectionTimeEnd}
			</if>
			<if test="tEmpContractAlert.contractEndStart != null">
				AND a.CONTRACT_END <![CDATA[ >= ]]> #{tEmpContractAlert.contractEndStart}
			</if>
			<if test="tEmpContractAlert.contractEndEnd != null">
				AND a.CONTRACT_END <![CDATA[ <= ]]> #{tEmpContractAlert.contractEndEnd}
			</if>
			<if test="tEmpContractAlert.csLoginName != null and tEmpContractAlert.csLoginName.trim() != ''">
				AND a.CS_LOGIN_NAME = #{tEmpContractAlert.csLoginName}
			</if>
			<if test="tEmpContractAlert.autoFlag != null and tEmpContractAlert.autoFlag.trim() != ''">
				AND a.AUTO_FLAG = #{tEmpContractAlert.autoFlag}
			</if>
		</if>
	</sql>
	<!--tEmpContractAlert简单分页查询-->
	<select id="getTEmpContractAlertAutoPage" resultMap="tEmpContractAlertAutoMap">
		SELECT
		<include refid="Base_Column_List"/>,
		b.EXPECTED_COLLECTION_TIME,
		b.SIGN_TYPE,
		IFNULL(b.PROCESS_STATUS,'0') PROCESS_STATUS,
		b.ERROR_INFO,
		b.ERROR_TIME,
		IFNULL(b.id,null) as 'preId'
		FROM t_emp_contract_alert a
		LEFT JOIN t_employee_contract_pre_new b on a.CONTRACT_ID = b.contract_id
		<where>
			1=1
			<include refid="tEmpContractAlert_where"/>
		</where>
	</select>
	<!--tEmpContractAlert简单分页查询-->
	<select id="getTEmpContractAlertAutoCount" resultType="java.lang.Integer">
		SELECT
		count(1)
		FROM t_emp_contract_alert a
		LEFT JOIN t_employee_contract_pre_new b on a.CONTRACT_ID = b.contract_id
		<where>
			1=1
			<include refid="tEmpContractAlert_where"/>
		</where>
	</select>

	<!--tEmpContractAlert简单分页查询-->
	<select id="listExport" resultType="com.yifu.cloud.plus.v1.yifu.archives.vo.TEmpContractAlertExportVo">
		SELECT
		<include refid="Base_Column_List"/>,
		b.EXPECTED_COLLECTION_TIME,
		b.SIGN_TYPE,
		IFNULL(b.PROCESS_STATUS,'0') PROCESS_STATUS
		FROM t_emp_contract_alert a
		LEFT JOIN t_employee_contract_pre_new b on a.CONTRACT_ID = b.contract_id
		<where>
			1=1
			<include refid="tEmpContractAlert_where"/>
			<if test="tEmpContractAlert != null">
				<if test="tEmpContractAlert.limitStart != null">
					limit #{tEmpContractAlert.limitStart},#{tEmpContractAlert.limitEnd}
				</if>
			</if>
		</where>
	</select>

	<update id="updateProcessStatus">
		UPDATE t_emp_contract_alert a
			JOIN t_employee_contract_pre_new b ON a.CONTRACT_ID = b.contract_id
			SET a.HANDLE_STATUS =
				CASE
				WHEN b.process_status = '9' THEN '2'
				WHEN b.process_status IN ('6', '8', '2', '4') THEN '1'
				ELSE a.HANDLE_STATUS  -- 保持不变
		END
		WHERE a.AUTO_FLAG = '0'
		AND b.process_status IN ('9', '6', '8', '2', '4')  -- 只处理这些状态
	</update>

	<!--tEmpContractAlert简单分页查询-->
	<select id="getAutoRenewForConfirm" resultType="com.yifu.cloud.plus.v1.yifu.archives.vo.ContractAlertConfirmVo">
		SELECT
		a.id,
		a.EMP_NAME,
		a.EMP_IDCARD,
		a.EMP_TYPE,
		a.CONTRACT_TYPE,
		a.CONTRACT_START,
		a.CONTRACT_END,
		a.FILE_PROVINCE,
		a.FILE_CITY,
		a.FILE_TOWN,
		c.EMP_PHONE,
		a.contract_id
		FROM t_emp_contract_alert a
		LEFT JOIN t_employee_contract_pre_new b on a.CONTRACT_ID = b.contract_id
		LEFT JOIN t_employee_info c on a.EMP_ID = c.id
		<where>
			1=1
			<include refid="tEmpContractAlert_where"/>
		</where>
		limit 2000
	</select>

	<!--tEmpContractAlert简单分页查询-->
	<select id="getPushWxConfrimInfos" resultMap="tEmpContractAlertAutoMap">
		SELECT a.CONTRACT_END, a.CS_LOGIN_NAME
		FROM t_emp_contract_alert a
		WHERE a.AUTO_FLAG = '0'
		  AND a.CS_LOGIN_NAME IS NOT NULL
		  AND a.CONTRACT_END BETWEEN CURDATE() - INTERVAL 1 MONTH - INTERVAL 15 DAY
		  AND CURDATE() + INTERVAL 1 MONTH + INTERVAL 15 DAY
		  AND NOT EXISTS (
			SELECT 1
			FROM t_employee_contract_pre_new b
			WHERE b.contract_id = a.CONTRACT_ID
			)
	</select>
</mapper>
