<?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.insurances.mapper.insurances.TInsurancePreRenewDetailMapper">

  <resultMap id="tInsurancePreRenewDetailMap" type="com.yifu.cloud.plus.v1.yifu.insurances.entity.TInsurancePreRenewDetail">
    <id property="id" column="ID"/>
    <result property="insurancesId" column="INSURANCES_ID"/>
    <result property="insurancesIdNew" column="INSURANCES_ID_NEW"/>
    <result property="deptName" column="DEPT_NAME"/>
    <result property="deptNo" column="DEPT_NO"/>
    <result property="empName" column="EMP_NAME"/>
    <result property="empIdcardNo" column="EMP_IDCARD_NO"/>
    <result property="post" column="POST"/>
    <result property="buyTypeOld" column="BUY_TYPE_OLD"/>
    <result property="insuranceProvinceNameOld" column="INSURANCE_PROVINCE_NAME_OLD"/>
    <result property="insuranceCityNameOld" column="INSURANCE_CITY_NAME_OLD"/>
    <result property="insuranceCompanyNameOld" column="INSURANCE_COMPANY_NAME_OLD"/>
    <result property="insuranceTypeNameOld" column="INSURANCE_TYPE_NAME_OLD"/>
    <result property="policyEffectOld" column="POLICY_EFFECT_OLD"/>
    <result property="policyStartOld" column="POLICY_START_OLD"/>
    <result property="policyEndOld" column="POLICY_END_OLD"/>
    <result property="buyType" column="BUY_TYPE"/>
    <result property="configId" column="CONFIG_ID"/>
    <result property="configName" column="CONFIG_NAME"/>
    <result property="insuranceCompanyName" column="INSURANCE_COMPANY_NAME"/>
    <result property="insuranceTypeName" column="INSURANCE_TYPE_NAME"/>
    <result property="insuranceTypeId" column="INSURANCE_TYPE_ID"/>
    <result property="buyStandardOld" column="BUY_STANDARD_OLD"/>
    <result property="buyStandard" column="BUY_STANDARD"/>
    <result property="insuranceProvinceName" column="INSURANCE_PROVINCE_NAME"/>
    <result property="insuranceCityName" column="INSURANCE_CITY_NAME"/>
    <result property="policyStart" column="POLICY_START"/>
    <result property="policyEnd" column="POLICY_END"/>
    <result property="policyEffect" column="POLICY_EFFECT"/>
    <result property="expectedCollectionTime" column="expected_collection_time"/>
    <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="unitName" column="UNIT_NAME"/>
    <result property="unitNo" column="UNIT_NO"/>
    <result property="isAddress" column="IS_ADDRESS"/>
    <result property="errorInfo" column="error_info"/>
    <result property="errorTime" column="error_time"/>
    <result property="policyNo" column="policy_no"/>
    <result property="replaceEmployeeName" column="replace_employee_name"/>
    <result property="replaceEmpIdcard" column="replace_emp_idcard"/>
    <result property="replaceDeptNo" column="replace_dept_no"/>
    <result property="processStatus" column="process_status"/>
    <result property="confirmDate" column="confirm_date"/>
    <result property="isLeave" column="is_leave"/>
    <result property="customerUsername" column="customer_username"/>
    <result property="customerUserLoginname" column="customer_user_loginname"/>
  </resultMap>
    <sql id="Base_Column_List">
                a.ID,
                a.INSURANCES_ID,
                a.INSURANCES_ID_NEW,
                a.DEPT_NAME,
                a.DEPT_NO,
                a.EMP_NAME,
                a.EMP_IDCARD_NO,
                a.POST,
                a.BUY_TYPE_OLD,
                a.INSURANCE_PROVINCE_NAME_OLD,
                a.INSURANCE_CITY_NAME_OLD,
                a.INSURANCE_COMPANY_NAME_OLD,
                a.INSURANCE_TYPE_NAME_OLD,
                a.POLICY_EFFECT_OLD,
                a.POLICY_START_OLD,
                a.POLICY_END_OLD,
                a.BUY_TYPE,
                a.CONFIG_ID,
                a.CONFIG_NAME,
                a.INSURANCE_COMPANY_NAME,
                a.INSURANCE_TYPE_NAME,
                a.INSURANCE_TYPE_ID,
                a.BUY_STANDARD_OLD,
                a.BUY_STANDARD,
                a.INSURANCE_PROVINCE_NAME,
                a.INSURANCE_CITY_NAME,
                a.POLICY_START,
                a.POLICY_END,
                a.POLICY_EFFECT,
                a.expected_collection_time,
                a.CREATE_BY,
                a.CREATE_NAME,
                a.CREATE_TIME,
                a.UPDATE_BY,
                a.UPDATE_TIME,
                a.UNIT_NAME,
                a.UNIT_NO,
                a.IS_ADDRESS,
                a.error_info,
                a.error_time,
                a.policy_no,
                a.replace_employee_name,
                a.replace_emp_idcard,
                a.replace_dept_no,
                a.process_status,
                a.confirm_date,
                a.customer_username,
                a.customer_user_loginname,
                a.is_leave
    </sql>
    <sql id="tInsurancePreRenewDetail_where">
        <if test="tInsurancePreRenewDetail != null">
                    <if test="tInsurancePreRenewDetail.id != null and tInsurancePreRenewDetail.id.trim() != ''">
                        AND a.ID = #{tInsurancePreRenewDetail.id}
                    </if>
                    <if test="tInsurancePreRenewDetail.insurancesId != null and tInsurancePreRenewDetail.insurancesId.trim() != ''">
                        AND a.INSURANCES_ID = #{tInsurancePreRenewDetail.insurancesId}
                    </if>
                    <if test="tInsurancePreRenewDetail.deptName != null and tInsurancePreRenewDetail.deptName.trim() != ''">
                        AND a.DEPT_NAME = #{tInsurancePreRenewDetail.deptName}
                    </if>
                    <if test="tInsurancePreRenewDetail.deptNo != null and tInsurancePreRenewDetail.deptNo.trim() != ''">
                        AND a.DEPT_NO = #{tInsurancePreRenewDetail.deptNo}
                    </if>
                    <if test="tInsurancePreRenewDetail.empName != null and tInsurancePreRenewDetail.empName.trim() != ''">
                        AND a.EMP_NAME = #{tInsurancePreRenewDetail.empName}
                    </if>
                    <if test="tInsurancePreRenewDetail.empIdcardNo != null and tInsurancePreRenewDetail.empIdcardNo.trim() != ''">
                        AND a.EMP_IDCARD_NO = #{tInsurancePreRenewDetail.empIdcardNo}
                    </if>
                    <if test="tInsurancePreRenewDetail.post != null and tInsurancePreRenewDetail.post.trim() != ''">
                        AND a.POST = #{tInsurancePreRenewDetail.post}
                    </if>
                    <if test="tInsurancePreRenewDetail.buyTypeOld != null">
                        AND a.BUY_TYPE_OLD = #{tInsurancePreRenewDetail.buyTypeOld}
                    </if>
                    <if test="tInsurancePreRenewDetail.insuranceProvinceNameOld != null and tInsurancePreRenewDetail.insuranceProvinceNameOld.trim() != ''">
                        AND a.INSURANCE_PROVINCE_NAME_OLD = #{tInsurancePreRenewDetail.insuranceProvinceNameOld}
                    </if>
                    <if test="tInsurancePreRenewDetail.insuranceCityNameOld != null and tInsurancePreRenewDetail.insuranceCityNameOld.trim() != ''">
                        AND a.INSURANCE_CITY_NAME_OLD = #{tInsurancePreRenewDetail.insuranceCityNameOld}
                    </if>
                    <if test="tInsurancePreRenewDetail.insuranceCompanyNameOld != null and tInsurancePreRenewDetail.insuranceCompanyNameOld.trim() != ''">
                        AND a.INSURANCE_COMPANY_NAME_OLD = #{tInsurancePreRenewDetail.insuranceCompanyNameOld}
                    </if>
                    <if test="tInsurancePreRenewDetail.insuranceTypeNameOld != null and tInsurancePreRenewDetail.insuranceTypeNameOld.trim() != ''">
                        AND a.INSURANCE_TYPE_NAME_OLD = #{tInsurancePreRenewDetail.insuranceTypeNameOld}
                    </if>
                    <if test="tInsurancePreRenewDetail.policyEffectOld != null">
                        AND a.POLICY_EFFECT_OLD = #{tInsurancePreRenewDetail.policyEffectOld}
                    </if>
                    <if test="tInsurancePreRenewDetail.policyStartOld != null">
                        AND a.POLICY_START_OLD = #{tInsurancePreRenewDetail.policyStartOld}
                    </if>
                    <if test="tInsurancePreRenewDetail.policyEndOld != null">
                        AND a.POLICY_END_OLD = #{tInsurancePreRenewDetail.policyEndOld}
                    </if>
                    <if test="tInsurancePreRenewDetail.buyType != null">
                        AND a.BUY_TYPE = #{tInsurancePreRenewDetail.buyType}
                    </if>
                    <if test="tInsurancePreRenewDetail.configId != null and tInsurancePreRenewDetail.configId.trim() != ''">
                        AND a.CONFIG_ID = #{tInsurancePreRenewDetail.configId}
                    </if>
                    <if test="tInsurancePreRenewDetail.configName != null and tInsurancePreRenewDetail.configName.trim() != ''">
                        AND a.CONFIG_NAME = #{tInsurancePreRenewDetail.configName}
                    </if>
                    <if test="tInsurancePreRenewDetail.insuranceCompanyName != null and tInsurancePreRenewDetail.insuranceCompanyName.trim() != ''">
                        AND a.INSURANCE_COMPANY_NAME = #{tInsurancePreRenewDetail.insuranceCompanyName}
                    </if>
                    <if test="tInsurancePreRenewDetail.insuranceTypeName != null and tInsurancePreRenewDetail.insuranceTypeName.trim() != ''">
                        AND a.INSURANCE_TYPE_NAME = #{tInsurancePreRenewDetail.insuranceTypeName}
                    </if>
                    <if test="tInsurancePreRenewDetail.buyStandard != null and tInsurancePreRenewDetail.buyStandard.trim() != ''">
                        AND a.BUY_STANDARD = #{tInsurancePreRenewDetail.buyStandard}
                    </if>
                    <if test="tInsurancePreRenewDetail.insuranceProvinceName != null and tInsurancePreRenewDetail.insuranceProvinceName.trim() != ''">
                        AND a.INSURANCE_PROVINCE_NAME = #{tInsurancePreRenewDetail.insuranceProvinceName}
                    </if>
                    <if test="tInsurancePreRenewDetail.insuranceCityName != null and tInsurancePreRenewDetail.insuranceCityName.trim() != ''">
                        AND a.INSURANCE_CITY_NAME = #{tInsurancePreRenewDetail.insuranceCityName}
                    </if>
                    <if test="tInsurancePreRenewDetail.policyStart != null">
                        AND a.POLICY_START = #{tInsurancePreRenewDetail.policyStart}
                    </if>
                    <if test="tInsurancePreRenewDetail.policyEnd != null">
                        AND a.POLICY_END = #{tInsurancePreRenewDetail.policyEnd}
                    </if>
                    <if test="tInsurancePreRenewDetail.policyEffect != null">
                        AND a.POLICY_EFFECT = #{tInsurancePreRenewDetail.policyEffect}
                    </if>
                    <if test="tInsurancePreRenewDetail.expectedCollectionTime != null">
                        AND a.expected_collection_time = #{tInsurancePreRenewDetail.expectedCollectionTime}
                    </if>
                    <if test="tInsurancePreRenewDetail.createBy != null and tInsurancePreRenewDetail.createBy.trim() != ''">
                        AND a.CREATE_BY = #{tInsurancePreRenewDetail.createBy}
                    </if>
                    <if test="tInsurancePreRenewDetail.createName != null and tInsurancePreRenewDetail.createName.trim() != ''">
                        AND a.CREATE_NAME = #{tInsurancePreRenewDetail.createName}
                    </if>
                    <if test="tInsurancePreRenewDetail.createTime != null">
                        AND a.CREATE_TIME = #{tInsurancePreRenewDetail.createTime}
                    </if>
                    <if test="tInsurancePreRenewDetail.updateBy != null and tInsurancePreRenewDetail.updateBy.trim() != ''">
                        AND a.UPDATE_BY = #{tInsurancePreRenewDetail.updateBy}
                    </if>
                    <if test="tInsurancePreRenewDetail.updateTime != null">
                        AND a.UPDATE_TIME = #{tInsurancePreRenewDetail.updateTime}
                    </if>
                    <if test="tInsurancePreRenewDetail.unitName != null and tInsurancePreRenewDetail.unitName.trim() != ''">
                        AND a.UNIT_NAME = #{tInsurancePreRenewDetail.unitName}
                    </if>
                    <if test="tInsurancePreRenewDetail.unitNo != null and tInsurancePreRenewDetail.unitNo.trim() != ''">
                        AND a.UNIT_NO = #{tInsurancePreRenewDetail.unitNo}
                    </if>
                    <if test="tInsurancePreRenewDetail.isAddress != null and tInsurancePreRenewDetail.isAddress.trim() != ''">
                        AND a.IS_ADDRESS = #{tInsurancePreRenewDetail.isAddress}
                    </if>
                    <if test="tInsurancePreRenewDetail.errorInfo != null and tInsurancePreRenewDetail.errorInfo.trim() != ''">
                        AND a.error_info = #{tInsurancePreRenewDetail.errorInfo}
                    </if>
                    <if test="tInsurancePreRenewDetail.errorTime != null">
                        AND a.error_time = #{tInsurancePreRenewDetail.errorTime}
                    </if>
                    <if test="tInsurancePreRenewDetail.policyNo != null and tInsurancePreRenewDetail.policyNo.trim() != ''">
                        AND a.policy_no = #{tInsurancePreRenewDetail.policyNo}
                    </if>
                    <if test="tInsurancePreRenewDetail.replaceEmployeeName != null and tInsurancePreRenewDetail.replaceEmployeeName.trim() != ''">
                        AND a.replace_employee_name = #{tInsurancePreRenewDetail.replaceEmployeeName}
                    </if>
                    <if test="tInsurancePreRenewDetail.replaceEmpIdcard != null and tInsurancePreRenewDetail.replaceEmpIdcard.trim() != ''">
                        AND a.replace_emp_idcard = #{tInsurancePreRenewDetail.replaceEmpIdcard}
                    </if>
                    <if test="tInsurancePreRenewDetail.replaceDeptNo != null and tInsurancePreRenewDetail.replaceDeptNo.trim() != ''">
                        AND a.replace_dept_no = #{tInsurancePreRenewDetail.replaceDeptNo}
                    </if>
                    <if test="tInsurancePreRenewDetail.processStatus != null and tInsurancePreRenewDetail.processStatus.trim() != ''">
                        AND a.process_status = #{tInsurancePreRenewDetail.processStatus}
                    </if>
        </if>
    </sql>
    <!--tInsurancePreRenewDetail简单分页查询-->
    <select id="getTInsurancePreRenewDetailPage" resultMap="tInsurancePreRenewDetailMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM t_insurance_pre_renew_detail a
        <where>
            1=1
            <include refid="tInsurancePreRenewDetail_where"/>
        </where>
    </select>

    <!-- 查找符合条件的需要派单的数据 -->
    <select id="selectPreRenewDetailListByDispatch" resultMap="tInsurancePreRenewDetailMap">
        SELECT
        	<include refid="Base_Column_List"/>
        FROM t_insurance_pre_renew_detail a
		JOIN t_insurance_alert b ON b.INSURANCES_PRE_RENEW_DETAIL_ID = a.ID
        <where>
			b.EXPIRE_IGNORE_FLAG = '1' and b.DELETE_FLAG=0 and a.is_leave = '0' and b.IS_OVERDUE = 0
			AND a.process_status in ('1','2','5')
			<if test="idList != null and idList.size() > 0">
				and a.id in
				<foreach collection="idList" item="param" index="index" open="(" close=")" separator=",">
					#{param}
				</foreach>
			</if>
			<if test="idList == null or idList.size() == 0">
        		and a.id = '-1'
			</if>
        </where>
		order by b.POLICY_END asc,b.id asc
    </select>

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

	<!-- 获取所有需要派单的数据 -->
	<select id="getAllNeedDispatchData" resultType="java.lang.String">
		SELECT
			a.id
		FROM t_insurance_pre_renew_detail a
		JOIN t_insurance_alert b ON b.INSURANCES_PRE_RENEW_DETAIL_ID = a.ID
		where b.EXPIRE_IGNORE_FLAG = '1' and b.DELETE_FLAG=0 and a.is_leave = '0' and b.IS_OVERDUE = 0
		  <!-- '0','1','2','5' 改为 '0','1','2' （待确认、待派单、派单失败） -->
		  AND a.process_status in ('0','1','2')
		  and DATE_FORMAT(a.expected_collection_time,"%Y-%m-%d") <![CDATA[ <= ]]> DATE_FORMAT(CURDATE(),"%Y-%m-%d")
	</select>

	<!-- 获取所有待确认的数据 -->
	<select id="getAllUnconfimData" resultType="com.yifu.cloud.plus.v1.yifu.archives.vo.IdNameNoVo">
		SELECT
			count(1) as id, a.customer_user_loginname as name
		FROM t_insurance_pre_renew_detail a
		JOIN t_insurance_alert b ON b.INSURANCES_PRE_RENEW_DETAIL_ID = a.ID
		where b.EXPIRE_IGNORE_FLAG = '1' and b.DELETE_FLAG=0 and a.is_leave = '0' and b.IS_OVERDUE = 0
			AND a.process_status = '0' and DATE_FORMAT(a.confirm_Date,"%Y-%m-%d") <![CDATA[ <= ]]> CURDATE()

		group by a.customer_user_loginname
	</select>

	<update id="updateRenewBatchById">
		update t_insurance_pre_renew_detail a
		set a.process_status = '2'
		where a.process_status in ('0','1') and a.id in
		<foreach item="idStr" index="index" collection="idList" open="(" separator="," close=")">
			#{idStr}
		</foreach>
	</update>

	<update id="updateInsurancePreRenewToIsLeave">
		<if test="empIdCard != null and empIdCard.trim() != '' and deptNo != null and deptNo.trim() != ''">
			update t_insurance_pre_renew_detail a
			set a.is_leave = '1'
			where a.EMP_IDCARD_NO = #{empIdCard} and a.DEPT_NO = #{deptNo}
		</if>
	</update>

</mapper>
