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

	<resultMap id="BaseResultMap" type="com.yifu.cloud.plus.v1.yifu.insurances.entity.TInsuranceAlert">
		<id property="id" column="ID" jdbcType="VARCHAR"/>
		<result property="empName" column="EMP_NAME" jdbcType="VARCHAR"/>
		<result property="empIdcardNo" column="EMP_IDCARD_NO" jdbcType="VARCHAR"/>
		<result property="deptNo" column="DEPT_NO" jdbcType="VARCHAR"/>
		<result property="post" column="POST" jdbcType="VARCHAR"/>
		<result property="insuranceCompanyName" column="INSURANCE_COMPANY_NAME" jdbcType="VARCHAR"/>
		<result property="insuranceTypeName" column="INSURANCE_TYPE_NAME" jdbcType="VARCHAR"/>
		<result property="buyStandard" column="BUY_STANDARD" jdbcType="VARCHAR"/>
		<result property="medicalQuota" column="MEDICAL_QUOTA" jdbcType="VARCHAR"/>
		<result property="dieDisableQuota" column="DIE_DISABLE_QUOTA" jdbcType="VARCHAR"/>
		<result property="policyNo" column="POLICY_NO" jdbcType="VARCHAR"/>
		<result property="policyStart" column="POLICY_START" jdbcType="DATE"/>
		<result property="policyEnd" column="POLICY_END" jdbcType="DATE"/>
		<result property="policyEffect" column="POLICY_EFFECT" jdbcType="DATE"/>
		<result property="settleMonth" column="SETTLE_MONTH" jdbcType="VARCHAR"/>
		<result property="invoiceNo" column="INVOICE_NO" jdbcType="VARCHAR"/>
		<result property="insuranceProvinceName" column="INSURANCE_PROVINCE_NAME" jdbcType="VARCHAR"/>
		<result property="insuranceCityName" column="INSURANCE_CITY_NAME" jdbcType="VARCHAR"/>
		<result property="insuranceHandleProvinceName" column="INSURANCE_HANDLE_PROVINCE_NAME" jdbcType="VARCHAR"/>
		<result property="insuranceHandleCityName" column="INSURANCE_HANDLE_CITY_NAME" jdbcType="VARCHAR"/>
		<result property="settleType" column="SETTLE_TYPE" jdbcType="TINYINT"/>
		<result property="actualPremium" column="ACTUAL_PREMIUM" jdbcType="DECIMAL"/>
		<result property="estimatePremium" column="ESTIMATE_PREMIUM" jdbcType="DECIMAL"/>
		<result property="isEffect" column="IS_EFFECT" jdbcType="TINYINT"/>
		<result property="isOverdue" column="IS_OVERDUE" jdbcType="TINYINT"/>
		<result property="remark" column="REMARK" jdbcType="VARCHAR"/>
		<result property="createBy" column="CREATE_BY" jdbcType="VARCHAR"/>
		<result property="createName" column="CREATE_NAME" jdbcType="VARCHAR"/>
		<result property="createTime" column="CREATE_TIME" jdbcType="TIMESTAMP"/>
		<result property="updateBy" column="UPDATE_BY" jdbcType="VARCHAR"/>
		<result property="updateTime" column="UPDATE_TIME" jdbcType="TIMESTAMP"/>
		<result property="deleteFlag" column="DELETE_FLAG" jdbcType="TINYINT"/>
		<result property="createUserDeptName" column="CREATE_USER_DEPT_NAME" jdbcType="VARCHAR"/>
		<result property="deptName" column="DEPT_NAME" jdbcType="VARCHAR"/>
		<result property="unitName" column="UNIT_NAME" jdbcType="VARCHAR"/>
		<result property="unitNo" column="UNIT_NO" jdbcType="VARCHAR"/>
		<result property="expireIgnoreFlag" column="EXPIRE_IGNORE_FLAG" jdbcType="VARCHAR"/>
		<result property="expireRemark" column="EXPIRE_REMARK" jdbcType="VARCHAR"/>
		<result property="alerter" column="ALERTER" jdbcType="VARCHAR"/>
		<result property="alertId" column="ALERT_ID" jdbcType="VARCHAR"/>
		<result property="haveWorkDay" column="HAVE_WORK_DAY" jdbcType="VARCHAR"/>
		<result property="processStatus" column="process_status" jdbcType="VARCHAR"/>
		<result property="errorInfo" column="error_info" jdbcType="VARCHAR"/>
		<result property="errorTime" column="error_time" jdbcType="DATE"/>
		<result property="insurancesPreRenewDetailId" column="INSURANCES_PRE_RENEW_DETAIL_ID" jdbcType="VARCHAR"/>
		<result property="buyType" column="BUY_TYPE" jdbcType="TINYINT"/>
		<result property="configId" column="config_id" jdbcType="VARCHAR"/>
		<result property="configName" column="config_name" jdbcType="VARCHAR"/>
		<result property="isAddress" column="IS_ADDRESS" jdbcType="CHAR"/>
		<result property="expectedCollectionTime" column="expected_collection_time" jdbcType="TIMESTAMP"/>
		<result property="insurancesIdNew" column="INSURANCES_ID_NEW" jdbcType="VARCHAR"/>
		<result property="policyStartNew" column="POLICY_START_NEW" jdbcType="DATE"/>
		<result property="policyEndNew" column="POLICY_END_NEW" jdbcType="DATE"/>
		<result property="policyEffectNew" column="POLICY_EFFECT_NEW" jdbcType="DATE"/>
		<result property="customerUsername" column="CUSTOMER_USERNAME" jdbcType="VARCHAR"/>
	</resultMap>

	<!-- 商险续签待办专用 -->
	<sql id="Alert_Column_List">
		a.ID,
		a.EMP_NAME,
		a.EMP_IDCARD_NO,
		a.DEPT_NO,
		a.POST,
		a.INSURANCE_COMPANY_NAME,
		a.INSURANCE_TYPE_NAME,
		a.INSURANCE_CITY_NAME,
		a.INSURANCE_HANDLE_CITY_NAME,
		a.INSURANCE_HANDLE_PROVINCE_NAME,
		a.INSURANCE_PROVINCE_NAME,
		a.BUY_STANDARD,
		a.UNIT_NAME,
		a.UNIT_NO,
		a.SETTLE_TYPE,
		a.POLICY_EFFECT,
		a.EXPIRE_REMARK,
		a.EXPIRE_IGNORE_FLAG,
		a.SETTLE_MONTH,
		a.POLICY_START,
		a.POLICY_END,
		a.ACTUAL_PREMIUM,
		a.ESTIMATE_PREMIUM,
		a.INVOICE_NO,
		a.MEDICAL_QUOTA,
		a.DIE_DISABLE_QUOTA,
		a.DEPT_NAME,
		a.POLICY_NO,
		a.IS_OVERDUE,
		a.REMARK,
		a.CREATE_BY,
		a.DELETE_FLAG,
		a.CREATE_NAME,
		a.CREATE_TIME,
		a.UPDATE_BY,
		a.UPDATE_TIME,
		a.CREATE_USER_DEPT_NAME,
		a.IS_EFFECT,
		a.ALERTER,
		a.ALERT_ID,
		a.HAVE_WORK_DAY,
		a.INSURANCES_PRE_RENEW_DETAIL_ID,
		b.BUY_TYPE,
		b.process_status,
		b.error_info,
		b.error_time
		,DATE_FORMAT(b.expected_collection_time,'%Y-%m-%d %H:%i') expected_collection_time
        ,b.INSURANCES_ID_NEW
        ,b.CUSTOMER_USERNAME
	</sql>
	<sql id="tInsuranceAlert_where">
		<if test="tInsuranceAlert != null">
			<if test="tInsuranceAlert.id != null and tInsuranceAlert.id.trim() != ''">
				AND a.ID = #{tInsuranceAlert.id}
			</if>
			<if test="tInsuranceAlert.idList != null">
				AND a.id in
				<foreach item="idStr" index="index" collection="tInsuranceAlert.idList" open="(" separator="," close=")">
					#{idStr}
				</foreach>
			</if>
			<if test="tInsuranceAlert.empName != null and tInsuranceAlert.empName.trim() != ''">
				AND a.EMP_NAME like concat('%', #{tInsuranceAlert.empName}, '%')
			</if>
			<if test="tInsuranceAlert.empIdcardNo != null and tInsuranceAlert.empIdcardNo.trim() != ''">
				AND a.EMP_IDCARD_NO like concat('%', #{tInsuranceAlert.empIdcardNo}, '%')
			</if>
			<if test="tInsuranceAlert.deptNo != null and tInsuranceAlert.deptNo.trim() != ''">
				AND a.DEPT_NO like concat('%', #{tInsuranceAlert.deptNo}, '%')
			</if>
			<if test="tInsuranceAlert.post != null and tInsuranceAlert.post.trim() != ''">
				AND a.POST = #{tInsuranceAlert.post}
			</if>
			<if test="tInsuranceAlert.insuranceCompanyName != null and tInsuranceAlert.insuranceCompanyName.trim() != ''">
				AND a.INSURANCE_COMPANY_NAME like concat('%', #{tInsuranceAlert.insuranceCompanyName}, '%')
			</if>
			<if test="tInsuranceAlert.insuranceTypeName != null and tInsuranceAlert.insuranceTypeName.trim() != ''">
				AND a.INSURANCE_TYPE_NAME like concat('%', #{tInsuranceAlert.insuranceTypeName}, '%')
			</if>
			<if test="tInsuranceAlert.insuranceCityName != null and tInsuranceAlert.insuranceCityName.trim() != ''">
				AND a.INSURANCE_CITY_NAME like concat('%', #{tInsuranceAlert.insuranceCityName}, '%')
			</if>
			<if test="tInsuranceAlert.insuranceHandleCityName != null and tInsuranceAlert.insuranceHandleCityName.trim() != ''">
				AND a.INSURANCE_HANDLE_CITY_NAME like concat('%', #{tInsuranceAlert.insuranceHandleCityName}, '%')
			</if>
			<if test="tInsuranceAlert.insuranceHandleProvinceName != null and tInsuranceAlert.insuranceHandleProvinceName.trim() != ''">
				AND a.INSURANCE_HANDLE_PROVINCE_NAME = #{tInsuranceAlert.insuranceHandleProvinceName}
			</if>
			<if test="tInsuranceAlert.insuranceProvinceName != null and tInsuranceAlert.insuranceProvinceName.trim() != ''">
				AND a.INSURANCE_PROVINCE_NAME = #{tInsuranceAlert.insuranceProvinceName}
			</if>
			<if test="tInsuranceAlert.buyStandard != null and tInsuranceAlert.buyStandard.trim() != ''">
				AND a.BUY_STANDARD = #{tInsuranceAlert.buyStandard}
			</if>
			<if test="tInsuranceAlert.unitName != null and tInsuranceAlert.unitName.trim() != ''">
				AND a.UNIT_NAME = #{tInsuranceAlert.unitName}
			</if>
			<if test="tInsuranceAlert.unitNo != null and tInsuranceAlert.unitNo.trim() != ''">
				AND a.UNIT_NO = #{tInsuranceAlert.unitNo}
			</if>
			<if test="tInsuranceAlert.settleType != null">
				AND a.SETTLE_TYPE = #{tInsuranceAlert.settleType}
			</if>
			<if test="tInsuranceAlert.policyEffect != null">
				AND a.POLICY_EFFECT = #{tInsuranceAlert.policyEffect}
			</if>
			<if test="tInsuranceAlert.expireRemark != null and tInsuranceAlert.expireRemark.trim() != ''">
				AND a.EXPIRE_REMARK = #{tInsuranceAlert.expireRemark}
			</if>
			<if test="tInsuranceAlert.expireIgnoreFlag != null and tInsuranceAlert.expireIgnoreFlag.trim() != ''">
				AND a.EXPIRE_IGNORE_FLAG = #{tInsuranceAlert.expireIgnoreFlag}
			</if>
			<if test="tInsuranceAlert.settleMonth != null and tInsuranceAlert.settleMonth.trim() != ''">
				AND a.SETTLE_MONTH = #{tInsuranceAlert.settleMonth}
			</if>
			<if test="tInsuranceAlert.policyStart != null">
				AND a.POLICY_START = #{tInsuranceAlert.policyStart}
			</if>
			<if test="tInsuranceAlert.policyEnd != null">
				AND a.POLICY_END = #{tInsuranceAlert.policyEnd}
			</if>
			<if test="tInsuranceAlert.policyEndStart != null">
				AND a.POLICY_END >= #{tInsuranceAlert.policyEndStart}
			</if>
			<if test="tInsuranceAlert.policyEndEnd != null">
				AND a.POLICY_END <![CDATA[<=]]> #{tInsuranceAlert.policyEndEnd}
			</if>
			<if test="tInsuranceAlert.actualPremium != null">
				AND a.ACTUAL_PREMIUM = #{tInsuranceAlert.actualPremium}
			</if>
			<if test="tInsuranceAlert.estimatePremium != null">
				AND a.ESTIMATE_PREMIUM = #{tInsuranceAlert.estimatePremium}
			</if>
			<if test="tInsuranceAlert.invoiceNo != null and tInsuranceAlert.invoiceNo.trim() != ''">
				AND a.INVOICE_NO = #{tInsuranceAlert.invoiceNo}
			</if>
			<if test="tInsuranceAlert.medicalQuota != null and tInsuranceAlert.medicalQuota.trim() != ''">
				AND a.MEDICAL_QUOTA = #{tInsuranceAlert.medicalQuota}
			</if>
			<if test="tInsuranceAlert.dieDisableQuota != null and tInsuranceAlert.dieDisableQuota.trim() != ''">
				AND a.DIE_DISABLE_QUOTA = #{tInsuranceAlert.dieDisableQuota}
			</if>
			<if test="tInsuranceAlert.deptName != null and tInsuranceAlert.deptName.trim() != ''">
				AND a.DEPT_NAME like concat('%', #{tInsuranceAlert.deptName}, '%')
			</if>
			<if test="tInsuranceAlert.policyNo != null and tInsuranceAlert.policyNo.trim() != ''">
				AND a.POLICY_NO = #{tInsuranceAlert.policyNo}
			</if>
			<if test="tInsuranceAlert.isOverdue != null">
				AND a.IS_OVERDUE = #{tInsuranceAlert.isOverdue}
			</if>
			<if test="tInsuranceAlert.remark != null and tInsuranceAlert.remark.trim() != ''">
				AND a.REMARK = #{tInsuranceAlert.remark}
			</if>
			<if test="tInsuranceAlert.createBy != null and tInsuranceAlert.createBy.trim() != ''">
				AND a.CREATE_BY = #{tInsuranceAlert.createBy}
			</if>
			<if test="tInsuranceAlert.deleteFlag != null">
				AND a.DELETE_FLAG = #{tInsuranceAlert.deleteFlag}
			</if>
			<if test="tInsuranceAlert.createName != null and tInsuranceAlert.createName.trim() != ''">
				AND a.CREATE_NAME = #{tInsuranceAlert.createName}
			</if>
			<if test="tInsuranceAlert.createTime != null">
				AND a.CREATE_TIME = #{tInsuranceAlert.createTime}
			</if>
			<if test="tInsuranceAlert.updateBy != null and tInsuranceAlert.updateBy.trim() != ''">
				AND a.UPDATE_BY = #{tInsuranceAlert.updateBy}
			</if>
			<if test="tInsuranceAlert.updateTime != null">
				AND a.UPDATE_TIME = #{tInsuranceAlert.updateTime}
			</if>
			<if test="tInsuranceAlert.createUserDeptName != null and tInsuranceAlert.createUserDeptName.trim() != ''">
				AND a.CREATE_USER_DEPT_NAME = #{tInsuranceAlert.createUserDeptName}
			</if>
			<if test="tInsuranceAlert.isEffect != null">
				AND a.IS_EFFECT = #{tInsuranceAlert.isEffect}
			</if>
			<if test="tInsuranceAlert.alerter != null and tInsuranceAlert.alerter.trim() != ''">
				AND a.ALERTER = #{tInsuranceAlert.alerter}
			</if>
			<if test="tInsuranceAlert.alertId != null and tInsuranceAlert.alertId.trim() != ''">
				AND a.ALERT_ID = #{tInsuranceAlert.alertId}
			</if>
			<if test="tInsuranceAlert.haveWorkDay != null and tInsuranceAlert.haveWorkDay.trim() != ''">
				AND a.HAVE_WORK_DAY = #{tInsuranceAlert.haveWorkDay}
			</if>
			<if test="tInsuranceAlert.customerUsername != null and tInsuranceAlert.customerUsername.trim() != ''">
				AND b.CUSTOMER_USERNAME like CONCAT('%',#{tInsuranceAlert.customerUsername},'%')
			</if>
			<if test="tInsuranceAlert.deptNoList != null and tInsuranceAlert.deptNoList.size > 0">
				AND a.DEPT_NO in
				<foreach item="idStr" index="index" collection="tInsuranceAlert.deptNoList" open="(" separator=","
						 close=")">
					#{idStr}
				</foreach>
			</if>
		</if>
	</sql>
	<!--tInsuranceAlert简单分页查询-->
	<select id="getTInsuranceAlertPage" resultMap="BaseResultMap">
		SELECT
			<include refid="Alert_Column_List"/>
		FROM t_insurance_alert a
		JOIN t_insurance_pre_renew_detail b ON a.INSURANCES_PRE_RENEW_DETAIL_ID = b.ID
		<where>
			a.EXPIRE_IGNORE_FLAG = '1' and a.DELETE_FLAG=0 and b.is_leave = '0' and a.IS_OVERDUE = 0
			<include refid="tInsuranceAlert_where"/>
			<if test="tInsuranceAlert != null">
				<if test="tInsuranceAlert.isBefore != null and tInsuranceAlert.isBefore.trim() != ''">
					<if test='tInsuranceAlert.isBefore.trim() == "1"'>
						AND DATE_FORMAT(now(),'%Y-%m-%d') >= DATE_FORMAT(b.expected_collection_time,'%Y-%m-%d')
					</if>
				</if>

				<if test="tInsuranceAlert.processStatusArraySearch != null and tInsuranceAlert.processStatusArraySearch.length > 0">
					AND b.PROCESS_STATUS in
					<foreach item="statusStr" index="index" collection="tInsuranceAlert.processStatusArraySearch" open="("
							 separator="," close=")">
						#{statusStr}
					</foreach>
				</if>
				<if test="tInsuranceAlert.processStatusArray != null and tInsuranceAlert.processStatusArray.length > 0">
					AND b.PROCESS_STATUS in
					<foreach item="statusStr" index="index" collection="tInsuranceAlert.processStatusArray" open="("
							 separator="," close=")">
						#{statusStr}
					</foreach>
				</if>
			</if>
		</where>
		order by a.POLICY_END asc,a.id asc
	</select>

	<!--tInsuranceAlert简单分页查询-->
	<select id="getTInsuranceAlertList2000" resultMap="BaseResultMap">
		SELECT
			a.ID,
			a.EMP_NAME,
			a.EMP_IDCARD_NO,
			a.DEPT_NO,
			a.POST,
			b.INSURANCE_COMPANY_NAME,
			b.INSURANCE_TYPE_NAME,
			b.INSURANCE_CITY_NAME,
			a.INSURANCE_HANDLE_CITY_NAME,
			a.INSURANCE_HANDLE_PROVINCE_NAME,
			b.INSURANCE_PROVINCE_NAME,
			b.BUY_STANDARD,
			a.UNIT_NAME,
			a.UNIT_NO,
			a.SETTLE_TYPE,
			a.POLICY_EFFECT,
			a.EXPIRE_REMARK,
			a.EXPIRE_IGNORE_FLAG,
			a.SETTLE_MONTH,
			a.POLICY_START,
			a.POLICY_END,
			a.ACTUAL_PREMIUM,
			a.ESTIMATE_PREMIUM,
			a.INVOICE_NO,
			a.MEDICAL_QUOTA,
			a.DIE_DISABLE_QUOTA,
			a.DEPT_NAME,
			a.POLICY_NO,
			a.IS_OVERDUE,
			a.REMARK,
			a.CREATE_BY,
			a.DELETE_FLAG,
			a.CREATE_NAME,
			a.CREATE_TIME,
			a.UPDATE_BY,
			a.UPDATE_TIME,
			a.CREATE_USER_DEPT_NAME,
			a.IS_EFFECT,
			a.ALERTER,
			a.ALERT_ID,
			a.HAVE_WORK_DAY,
			a.INSURANCES_PRE_RENEW_DETAIL_ID,
			b.BUY_TYPE,
			b.process_status,
			b.error_info,
			b.error_time
			,DATE_FORMAT(b.expected_collection_time,'%Y-%m-%d %H:%i') expected_collection_time
			,b.INSURANCES_ID_NEW,
			b.POLICY_EFFECT POLICY_EFFECT_NEW,
			b.POLICY_START POLICY_START_NEW,
			b.POLICY_END POLICY_END_NEW
		FROM t_insurance_alert a
		JOIN t_insurance_pre_renew_detail b ON a.INSURANCES_PRE_RENEW_DETAIL_ID = b.ID
		<where>
			a.EXPIRE_IGNORE_FLAG = '1' and a.DELETE_FLAG=0 and b.is_leave = '0' and a.IS_OVERDUE = 0
			<include refid="tInsuranceAlert_where"/>
			<if test="tInsuranceAlert != null">
				<if test="tInsuranceAlert.isBefore != null and tInsuranceAlert.isBefore.trim() != ''">
					<if test='tInsuranceAlert.isBefore.trim() == "1"'>
						AND DATE_FORMAT(now(),'%Y-%m-%d') >= DATE_FORMAT(b.expected_collection_time,'%Y-%m-%d')
					</if>
				</if>
				<if test="tInsuranceAlert.processStatusArraySearch != null and tInsuranceAlert.processStatusArraySearch.length > 0">
					AND b.PROCESS_STATUS in
					<foreach item="statusStr" index="index" collection="tInsuranceAlert.processStatusArraySearch" open="("
							 separator="," close=")">
						#{statusStr}
					</foreach>
				</if>
				<if test="tInsuranceAlert.processStatusArray != null and tInsuranceAlert.processStatusArray.length > 0">
					AND b.PROCESS_STATUS in
					<foreach item="statusStr" index="index" collection="tInsuranceAlert.processStatusArray" open="("
							 separator="," close=")">
						#{statusStr}
					</foreach>
				</if>
			</if>
		</where>
		order by a.POLICY_END asc,a.id asc
		limit 2000
	</select>

	<!-- 导出 -->
	<select id="getTInsuranceAlertExportCount" resultType="java.lang.Integer">
		SELECT
			count(1)
		FROM t_insurance_alert a
		JOIN t_insurance_pre_renew_detail b ON a.INSURANCES_PRE_RENEW_DETAIL_ID = b.ID
		<where>
			a.EXPIRE_IGNORE_FLAG = '1' and a.DELETE_FLAG=0 and b.is_leave = '0' and a.IS_OVERDUE = 0
			<include refid="tInsuranceAlert_where"/>
			<if test="tInsuranceAlert != null">
				<if test="tInsuranceAlert.isBefore != null and tInsuranceAlert.isBefore.trim() != ''">
					<if test='tInsuranceAlert.isBefore.trim() == "1"'>
						AND DATE_FORMAT(now(),'%Y-%m-%d') >= DATE_FORMAT(b.expected_collection_time,'%Y-%m-%d')
					</if>
				</if>

				<if test="tInsuranceAlert.processStatusArraySearch != null and tInsuranceAlert.processStatusArraySearch.length > 0">
					AND b.PROCESS_STATUS in
					<foreach item="statusStr" index="index" collection="tInsuranceAlert.processStatusArraySearch" open="("
							 separator="," close=")">
						#{statusStr}
					</foreach>
				</if>
				<if test="tInsuranceAlert.processStatusArray != null and tInsuranceAlert.processStatusArray.length > 0">
					AND b.PROCESS_STATUS in
					<foreach item="statusStr" index="index" collection="tInsuranceAlert.processStatusArray" open="("
							 separator="," close=")">
						#{statusStr}
					</foreach>
				</if>
			</if>
		</where>
	</select>

	<!-- 导出 -->
	<select id="getTInsuranceAlertExport" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.TInsuranceAlertExportVO">
		SELECT
			a.DEPT_NAME,a.DEPT_NO,a.EMP_NAME,a.EMP_IDCARD_NO,a.INSURANCE_CITY_NAME,a.INSURANCE_HANDLE_CITY_NAME
			,a.INSURANCE_COMPANY_NAME,a.INSURANCE_TYPE_NAME,DATE_FORMAT(a.POLICY_EFFECT,'%Y-%m-%d') POLICY_EFFECT
			,concat(DATE_FORMAT(a.POLICY_START,'%Y-%m-%d'),'-',DATE_FORMAT(a.POLICY_END,'%Y-%m-%d')) POLICY_START
			,'临期' isOverdue,a.HAVE_WORK_DAY,'否' EXPIRE_IGNORE_FLAG,a.CREATE_NAME,a.ALERTER
			,DATE_FORMAT(b.expected_collection_time,'%Y-%m-%d %H:%i') expected_collection_time
			,case b.process_status  when '0' then '待确认' when '1' then '待派单' when '2' then '派单失败'
			when '3' then '待投保' when '4' then '投保中' when '5' then '投保退回' when '6' then '已投保'  when '7' then '待缴费'  else '-' end process_status
		    ,b.CUSTOMER_USERNAME
		FROM t_insurance_alert a
		JOIN t_insurance_pre_renew_detail b ON a.INSURANCES_PRE_RENEW_DETAIL_ID = b.ID
		<where>
			a.EXPIRE_IGNORE_FLAG = '1' and a.DELETE_FLAG=0 and b.is_leave = '0' and a.IS_OVERDUE = 0
			<include refid="tInsuranceAlert_where"/>
			<if test="tInsuranceAlert != null">
				<if test="tInsuranceAlert.isBefore != null and tInsuranceAlert.isBefore.trim() != ''">
					<if test='tInsuranceAlert.isBefore.trim() == "1"'>
						AND DATE_FORMAT(now(),'%Y-%m-%d') >= DATE_FORMAT(b.expected_collection_time,'%Y-%m-%d')
					</if>
				</if>

				<if test="tInsuranceAlert.processStatusArraySearch != null and tInsuranceAlert.processStatusArraySearch.length > 0">
					AND b.PROCESS_STATUS in
					<foreach item="statusStr" index="index" collection="tInsuranceAlert.processStatusArraySearch" open="("
							 separator="," close=")">
						#{statusStr}
					</foreach>
				</if>
				<if test="tInsuranceAlert.processStatusArray != null and tInsuranceAlert.processStatusArray.length > 0">
					AND b.PROCESS_STATUS in
					<foreach item="statusStr" index="index" collection="tInsuranceAlert.processStatusArray" open="("
							 separator="," close=")">
						#{statusStr}
					</foreach>
				</if>
			</if>
		</where>
		order by a.POLICY_END asc,a.id asc
		<if test="tInsuranceAlert != null">
			<if test="tInsuranceAlert.limitStart != null">
				limit #{tInsuranceAlert.limitStart},#{tInsuranceAlert.limitEnd}
			</if>
		</if>
	</select>

	<select id="selectInsuranceAlert"  resultMap="BaseResultMap">
		SELECT
			a.ID,
			a.EMP_NAME,
			a.EMP_IDCARD_NO,
			a.DEPT_NO,
			a.DEPT_NAME,
			a.POST,
			a.INSURANCE_COMPANY_NAME,
			a.INSURANCE_TYPE_NAME,
			a.INSURANCE_CITY_NAME,
			a.INSURANCE_HANDLE_CITY_NAME,
			a.INSURANCE_HANDLE_PROVINCE_NAME,
			a.INSURANCE_PROVINCE_NAME,
			a.BUY_STANDARD,
			a.UNIT_NAME,
			a.UNIT_NO,
			a.SETTLE_TYPE,
			a.POLICY_EFFECT,
			a.EXPIRE_REMARK,
			a.EXPIRE_IGNORE_FLAG,
			a.SETTLE_MONTH,
			a.POLICY_START,
			a.POLICY_END,
			a.ACTUAL_PREMIUM,
			a.ESTIMATE_PREMIUM,
			a.INVOICE_NO,
			a.MEDICAL_QUOTA,
			a.DIE_DISABLE_QUOTA,
			a.DEPT_ID,
			a.POLICY_NO,
			IF( a.POLICY_END >= curdate(), 0, 1 ) IS_OVERDUE,
			a.REMARK,
			a.CREATE_BY,
			a.DELETE_FLAG,
			a.CREATE_NAME,
			a.CREATE_TIME,
			a.UPDATE_BY,
			a.UPDATE_TIME,
			a.IS_EFFECT,
			a.CREATE_USER_DEPT_NAME
			,d.ID INSURANCES_PRE_RENEW_DETAIL_ID
			,a.BUY_TYPE
			,ep.config_id
			,ep.config_name
			,a.IS_ADRESS
		FROM
			t_insurance_detail a
				INNER JOIN (
				SELECT
					h.endTime,
					max( l.POLICY_START ) POLICY_START,
					l.EMP_IDCARD_NO,
					l.INSURANCE_COMPANY_NAME,
					l.INSURANCE_TYPE_NAME
				FROM
					(
						SELECT
							b.EMP_IDCARD_NO,
							max( b.POLICY_END ) endTime
						FROM
							t_insurance_detail b
						WHERE
							b.POLICY_START BETWEEN date_sub( date_add( curdate(), INTERVAL 1 DAY ), INTERVAL 1 YEAR )
								AND curdate()
						  AND b.IS_EFFECT = 0
						  AND b.DELETE_FLAG = '0'
						  AND NOT EXISTS (
								SELECT
									1
								FROM
									t_insurance_detail ea
								WHERE
									ea.POLICY_START >= date_sub( date_add( curdate(), INTERVAL 1 DAY ), INTERVAL 1 YEAR )
								  AND ea.IS_EFFECT = 0
								  AND ea.DELETE_FLAG = '0'
								  AND ea.POLICY_END >= date_add( curdate(), INTERVAL 1 MONTH )
								  AND ea.EMP_IDCARD_NO = b.EMP_IDCARD_NO
								  AND ea.INSURANCE_COMPANY_NAME = b.INSURANCE_COMPANY_NAME
								  AND ea.INSURANCE_TYPE_NAME = b.INSURANCE_TYPE_NAME
							)
						GROUP BY
							b.EMP_IDCARD_NO,
							b.INSURANCE_COMPANY_NAME,
							b.INSURANCE_TYPE_NAME
					) h
						INNER JOIN t_insurance_detail l ON h.EMP_IDCARD_NO = l.EMP_IDCARD_NO
						AND l.DELETE_FLAG = '0'
						AND h.endTime = l.POLICY_END
						AND l.POLICY_START BETWEEN date_sub( date_add( curdate(), INTERVAL 1 DAY ), INTERVAL 1 YEAR )
															   AND curdate()
				WHERE
					l.EXPIRE_IGNORE_FLAG = '1'
				GROUP BY
					l.EMP_IDCARD_NO,
					l.INSURANCE_COMPANY_NAME,
					l.INSURANCE_TYPE_NAME,
					l.POLICY_END
			) ll ON ll.EMP_IDCARD_NO = a.EMP_IDCARD_NO
				AND ll.POLICY_START = a.POLICY_START
				AND ll.endTime = a.POLICY_END
				AND ll.INSURANCE_COMPANY_NAME = a.INSURANCE_COMPANY_NAME
				AND ll.INSURANCE_TYPE_NAME = a.INSURANCE_TYPE_NAME
		LEFT JOIN t_insurance_pre_renew_detail d on d.INSURANCES_ID = a.id
		LEFT JOIN t_employee_insurance_pre ep on ep.INSURANCES_ID = a.id
		where a.DELETE_FLAG = '0'
		  AND a.EXPIRE_IGNORE_FLAG = '1'
		  AND a.IS_EFFECT = 0
	</select>
	<resultMap id="PushWxResultMap" type="com.yifu.cloud.plus.v1.yifu.insurances.vo.InsuranceAlertWx">
		<result property="projectName" column="DEPT_NAME" jdbcType="VARCHAR"/>
		<result property="deptNo" column="DEPT_NO" jdbcType="VARCHAR"/>
		<result property="lq" column="lq" jdbcType="VARCHAR"/>
		<result property="gq" column="gq" jdbcType="VARCHAR"/>
	</resultMap>
	<select id="getInsuranceAlertToWx"  resultMap="PushWxResultMap">
		select  a.DEPT_NAME,a.DEPT_NO,
				SUM(case when a.IS_OVERDUE='0' THEN 1 ELSE 0 END) as 'lq',
				SUM(CASE WHEN a.IS_OVERDUE='1' THEN 1 ELSE 0 END) as 'gq'
		from  t_insurance_alert  a where a.EXPIRE_IGNORE_FLAG = '1'  and a.DELETE_FLAG='0' GROUP BY a.DEPT_NO
	</select>

	<select id="selectInsuranceAlertIgnore"  resultMap="BaseResultMap">
		SELECT
			a.ID,
			a.EMP_NAME,
			a.EMP_IDCARD_NO
		FROM
			t_insurance_alert a
		where a.DELETE_FLAG = '0' and a.EXPIRE_IGNORE_FLAG = '0'
	</select>


	<select id="selectInsuranceAlertList"  resultMap="BaseResultMap">
		SELECT
			a.ID,
			b.process_status
		FROM t_insurance_alert a
		JOIN t_insurance_pre_renew_detail b ON a.INSURANCES_PRE_RENEW_DETAIL_ID = b.ID
		where a.EXPIRE_IGNORE_FLAG = '1' and a.DELETE_FLAG=0 and b.is_leave = '0' and a.IS_OVERDUE = 0
		<if test="idList != null">
			AND a.id in
			<foreach item="idStr" index="index" collection="idList" open="(" separator="," close=")">
				#{idStr}
			</foreach>
		</if>
	</select>

	<select id="selectInsuranceAlertListBySure"  resultMap="BaseResultMap">
		SELECT
			b.ID,
			b.process_status
		FROM t_insurance_alert a
		JOIN t_insurance_pre_renew_detail b ON a.INSURANCES_PRE_RENEW_DETAIL_ID = b.ID
		where a.EXPIRE_IGNORE_FLAG = '1' and a.DELETE_FLAG=0 and b.is_leave = '0' and a.IS_OVERDUE = 0
		<if test="idList != null">
			AND b.id in
			<foreach item="idStr" index="index" collection="idList" open="(" separator="," close=")">
				#{idStr}
			</foreach>
		</if>
	</select>

	<select id="selectInsuranceAlertOne"  resultMap="BaseResultMap">
		SELECT
			a.ID,
			b.process_status
		FROM t_insurance_alert a
		JOIN t_insurance_pre_renew_detail b ON a.INSURANCES_PRE_RENEW_DETAIL_ID = b.ID
		where a.EXPIRE_IGNORE_FLAG = '1' and a.DELETE_FLAG=0 and b.is_leave = '0' and a.IS_OVERDUE = 0
			AND b.id = #{id}
		limit 1
	</select>
</mapper>
