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

    <resultMap id="BaseResultMap" type="com.yifu.cloud.plus.v1.yifu.insurances.entity.TInsuranceDetail">
            <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="insuranceTypeId" column="INSURANCE_TYPE_ID" jdbcType="BIGINT"/>
            <result property="buyStandard" column="BUY_STANDARD" jdbcType="VARCHAR"/>
            <result property="rate" column="RATE" jdbcType="DECIMAL"/>
            <result property="billingType" column="BILLING_TYPE" jdbcType="TINYINT"/>
            <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="batchDate" column="BATCH_DATE" jdbcType="TIMESTAMP"/>
			<result property="settleMonth" column="SETTLE_MONTH" jdbcType="VARCHAR"/>
            <result property="invoiceNo" column="INVOICE_NO" jdbcType="VARCHAR"/>
            <result property="buyType" column="BUY_TYPE" jdbcType="TINYINT"/>
            <result property="insuranceProvince" column="INSURANCE_PROVINCE" jdbcType="TINYINT"/>
            <result property="insuranceProvinceName" column="INSURANCE_PROVINCE_NAME" jdbcType="VARCHAR"/>
            <result property="insuranceCity" column="INSURANCE_CITY" jdbcType="TINYINT"/>
            <result property="insuranceCityName" column="INSURANCE_CITY_NAME" jdbcType="VARCHAR"/>
            <result property="insuranceHandleProvince" column="INSURANCE_HANDLE_PROVINCE" jdbcType="TINYINT"/>
            <result property="insuranceHandleProvinceName" column="INSURANCE_HANDLE_PROVINCE_NAME" jdbcType="VARCHAR"/>
            <result property="insuranceHandleCity" column="INSURANCE_HANDLE_CITY" jdbcType="TINYINT"/>
            <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="signFlag" column="SIGN_FLAG" jdbcType="TINYINT"/>
            <result property="buyHandleStatus" column="BUY_HANDLE_STATUS" jdbcType="TINYINT"/>
            <result property="defaultSettleId" column="DEFAULT_SETTLE_ID" jdbcType="VARCHAR"/>
            <result property="reduceHandleStatus" column="REDUCE_HANDLE_STATUS" jdbcType="TINYINT"/>
            <result property="isUse" column="IS_USE" jdbcType="TINYINT"/>
            <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="handledBy" column="HANDLED_BY" jdbcType="VARCHAR"/>
			<result property="handledTime" column="HANDLED_TIME" jdbcType="TIMESTAMP"/>
			<result property="orderNo" column="ORDER_NO" jdbcType="VARCHAR"/>
			<result property="createUserDeptId" column="CREATE_USER_DEPT_ID" jdbcType="VARCHAR"/>
			<result property="createUserDeptName" column="CREATE_USER_DEPT_NAME" jdbcType="VARCHAR"/>
			<result property="deptId" column="DEPT_ID" jdbcType="VARCHAR"/>
			<result property="bpoFlag" column="BPO_FLAG" jdbcType="VARCHAR"/>
			<result property="deptName" column="DEPT_NAME" jdbcType="VARCHAR"/>
			<result property="unitId" column="UNIT_ID" jdbcType="VARCHAR"/>
			<result property="unitName" column="UNIT_NAME" jdbcType="VARCHAR"/>
			<result property="unitNo" column="UNIT_NO" jdbcType="VARCHAR"/>
			<result property="incomeSettleFlag" column="INCOME_SETTLE_FLAG"/>
			<result property="incomeCollectFlag" column="INCOME_COLLECT_FLAG"/>
			<result property="paySettleFlag" column="PAY_COLLECT_FLAG"/>
			<result property="payCollectFlag" column="PAY_SETTLE_FLAG"/>
			<result property="sincomeSettleFlag" column="SINCOME_SETTLE_FLAG"/>
			<result property="sincomeCollectFlag" column="SINCOME_COLLECT_FLAG"/>
			<result property="spaySettleFlag" column="SPAY_COLLECT_FLAG"/>
			<result property="spayCollectFlag" column="SPAY_SETTLE_FLAG"/>
			<result property="isAdress" column="IS_ADRESS" jdbcType="VARCHAR"/>

		<result property="paymentStatus" column="PAYMENT_STATUS" jdbcType="VARCHAR"/>
		<result property="paymentTime" column="PAYMENT_TIME" jdbcType="VARCHAR"/>
		<result property="isJfcd" column="IS_JFCD" jdbcType="VARCHAR"/>
		<result property="purchaseCycle" column="PURCHASE_CYCLE" jdbcType="VARCHAR"/>
		<result property="preHandleTime" column="PRE_HANDLE_TIME" jdbcType="VARCHAR"/>


	</resultMap>
	<resultMap id="UpdateMap" type="com.yifu.cloud.plus.v1.yifu.insurances.vo.InsuranceUpdateVO">
		<id property="id" column="ID" jdbcType="VARCHAR"/>
		<result property="invoiceNo" column="INVOICE_NO" jdbcType="VARCHAR"/>
		<result property="policyNo" column="POLICY_NO" jdbcType="VARCHAR"/>
	</resultMap>
    <sql id="Base_Column_List">
        ID,EMP_NAME,
        EMP_IDCARD_NO,DEPT_NO,POST,INSURANCE_COMPANY_NAME,INSURANCE_TYPE_NAME,
        INSURANCE_TYPE_ID,BUY_STANDARD,RATE,BILLING_TYPE,MEDICAL_QUOTA,DIE_DISABLE_QUOTA,POLICY_NO,
        POLICY_START,POLICY_END,POLICY_EFFECT,
        BATCH_DATE,SETTLE_MONTH,INVOICE_NO,
        BUY_TYPE,INSURANCE_PROVINCE,INSURANCE_PROVINCE_NAME,INSURANCE_CITY,INSURANCE_CITY_NAME,
        INSURANCE_HANDLE_PROVINCE,INSURANCE_HANDLE_PROVINCE_NAME,INSURANCE_HANDLE_CITY,INSURANCE_HANDLE_CITY_NAME,SETTLE_TYPE,
        ACTUAL_PREMIUM,ESTIMATE_PREMIUM,SIGN_FLAG,
        BUY_HANDLE_STATUS,DEFAULT_SETTLE_ID,REDUCE_HANDLE_STATUS,
        IS_USE,IS_EFFECT,IS_OVERDUE,REMARK,
        CREATE_BY,CREATE_NAME,CREATE_TIME,BPO_FLAG,
        UPDATE_BY,UPDATE_TIME,DELETE_FLAG,DEFAULT_SETTLE_ID,HANDLED_BY,HANDLED_TIME,ORDER_NO,CREATE_USER_DEPT_ID,CREATE_USER_DEPT_NAME,
		DEPT_NAME,UNIT_ID,UNIT_NAME,UNIT_NO,IS_ADRESS,PAYMENT_STATUS,PAYMENT_TIME,IS_JFCD,PURCHASE_CYCLE,PRE_HANDLE_TIME


	</sql>
	<!--投保分页查询-->
	<select id="getInsuranceListPage" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.InsuranceListVO">
		select a.id as id,
		a.EMP_NAME as empName,
		a.EMP_IDCARD_NO as empIdcardNo,
		a.BUY_TYPE as buyType,
		a.POST as post,
		a.DEPT_NO as deptNo,
		a.POLICY_START as policyStart,
		a.POLICY_END as policyEnd,
		a.INSURANCE_COMPANY_NAME as insuranceCompanyName,
		a.INSURANCE_TYPE_NAME as insuranceTypeName,
	    a.BUY_STANDARD as buyStandard,
		a.CREATE_BY as createBy,
		a.CREATE_NAME as createName,
		a.BUY_HANDLE_STATUS as buyHandleStatus,
		a.SETTLE_TYPE as settleType,
		a.CREATE_USER_DEPT_NAME as createUserDeptName,
		a.SETTLE_MONTH as settleMonth,
		a.DEPT_NAME as projectName,
		a.REDUCE_HANDLE_STATUS as reduceHandleStatus,
		a.CREATE_TIME as createTime,
		a.UNIT_NAME as unitName,
		a.UNIT_NO as unitNo,
		a.IS_EFFECT as isEffect,
		a.IS_ADRESS as isAdress,
		a.PAYMENT_STATUS as paymentStatus,
		a.PAYMENT_TIME as paymentTime,
		a.IS_JFCD as isJfcd,
		a.PURCHASE_CYCLE  as purchaseCycle,
		a.BILLING_TYPE as billingType,
		a.PRE_HANDLE_TIME as preHandleTime,
		a.ESTIMATE_PREMIUM as estimatePremium
		from t_insurance_detail a
		where a.DELETE_FLAG = 0
		<if test="param.isEffect != null">
			and a.IS_EFFECT = #{param.isEffect}
		</if>
		<if test="param.id != null">
			and a.id = #{param.id}
		</if>
		<if test="param.unitName != null and param.unitName.trim() != ''">
			and a.UNIT_NAME like concat(replace(replace(#{param.unitName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.unitNo != null and param.unitNo.trim() != ''">
			and a.UNIT_NO like concat(replace(replace(#{param.unitNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.deptNo != null and param.deptNo.trim() != ''">
			and a.DEPT_NO = #{param.deptNo}
		</if>
		<if test="param.orderNo != null and param.orderNo.trim() != ''">
			and a.ORDER_NO = #{param.orderNo}
		</if>
		<if test="param.buyHandleStatus != null">
			and a.BUY_HANDLE_STATUS = #{param.buyHandleStatus}
		</if>
		<if test="param.empName != null and param.empName.trim() != ''">
			and a.EMP_NAME like concat('%',replace(replace(#{param.empName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.empIdcardNo != null and param.empIdcardNo.trim() != ''">
			and a.EMP_IDCARD_NO like concat('%',replace(replace(#{param.empIdcardNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.insuranceCompanyName != null and param.insuranceCompanyName.trim() != ''">
			and a.INSURANCE_COMPANY_NAME = #{param.insuranceCompanyName}
		</if>
		<if test="param.insuranceTypeName != null and param.insuranceTypeName.trim() != ''">
			and a.INSURANCE_TYPE_NAME like concat('%',replace(replace(#{param.insuranceTypeName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.buyHandleStatusList != null and param.buyHandleStatusList.size > 0">
			and a.BUY_HANDLE_STATUS in
			<foreach collection="param.buyHandleStatusList" index="index" item="item" open="(" separator="," close=")">
				 #{item}
			</foreach>
		</if>
		<if test="param.buyType != null">
			and a.BUY_TYPE = #{param.buyType}
		</if>
		<if test="param.createName != null and param.createName.trim() != ''">
			and a.CREATE_NAME like concat('%',#{param.createName},'%')
		</if>
		<if test="param.startDate != null and param.startDate.trim() != '' and param.endDate != null and param.endDate.trim() != ''">
			and a.CREATE_TIME <![CDATA[ >= ]]>  concat(#{param.startDate}, ' 00:00:00') and a.CREATE_TIME <![CDATA[ <= ]]> concat(#{param.endDate}, ' 23:59:59')
		</if>
		<if test="param.reduceHandleStatus != null">
			and a.REDUCE_HANDLE_STATUS = #{param.reduceHandleStatus}
		</if>
		<if test="param.isAdress != null and param.isAdress.trim() != ''">
			and a.IS_ADRESS = #{param.isAdress}
		</if>
		<if test="param.paymentStatus != null and param.paymentStatus.trim() != ''">
			and a.PAYMENT_STATUS = #{param.paymentStatus}
		</if>
		<if test="param.paymentTimeStart != null and param.paymentTimeStart.trim() != '' ">
			and a.PAYMENT_TIME <![CDATA[ >= ]]>  #{param.paymentTimeStart}
		</if>
		<if test="param.paymentTimeEnd != null and param.paymentTimeEnd.trim() != ''">
			and a.PAYMENT_TIME <![CDATA[ <= ]]> #{param.paymentTimeEnd}
		</if>
		<if test="param.isJfcd != null and param.isJfcd.trim() != ''">
			and a.IS_JFCD = #{param.isJfcd}
		</if>
		<if test="param.estimatePremiumStart != null">
			and a.ESTIMATE_PREMIUM <![CDATA[ >= ]]>  #{param.estimatePremiumStart}
		</if>
		<if test="param.estimatePremiumEnd != null">
			and a.ESTIMATE_PREMIUM <![CDATA[ <= ]]> #{param.estimatePremiumEnd}
		</if>
		<if test="param.authSql != null and param.authSql.trim() != ''">
			${param.authSql}
		</if>
		ORDER BY a.BUY_HANDLE_STATUS,a.CREATE_TIME DESC
	</select>
	<!--投保不分页查询-->
	<select id="getInsuranceList" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.InsuranceListVO">
		select a.id as id,
		a.EMP_NAME as empName,
		a.EMP_IDCARD_NO as empIdcardNo,
		a.BUY_TYPE as buyType,
		a.POST as post,
		a.DEPT_NO as deptNo,
		a.POLICY_START as policyStart,
		a.POLICY_END as policyEnd,
		a.INSURANCE_COMPANY_NAME as insuranceCompanyName,
		a.INSURANCE_TYPE_NAME as insuranceTypeName,
		a.BUY_STANDARD as buyStandard,
		a.CREATE_BY as createBy,
		a.CREATE_NAME as createName,
		a.BUY_HANDLE_STATUS as buyHandleStatus,
		a.SETTLE_TYPE as settleType,
		a.SETTLE_MONTH as settleMonth,
		a.DEPT_NAME as projectName,
		a.REDUCE_HANDLE_STATUS as reduceHandleStatus,
		if(a.BUY_HANDLE_STATUS=4,a.BACK_REMARK,'') as backRemark,
		a.CREATE_TIME as createTime,
		a.IS_EFFECT as isEffect,
		a.IS_ADRESS as isAdress,
		a.PAYMENT_STATUS as paymentStatus,
		a.PAYMENT_TIME as paymentTime,
		a.IS_JFCD as isJfcd,
		<!-- a.PURCHASE_CYCLE as purchaseCycle, -->
		a.PURCHASE_CYCLE as purchaseCycle,
		a.PRE_HANDLE_TIME as preHandleTime,
		a.ESTIMATE_PREMIUM as estimatePremium,
		a.BILLING_TYPE as billingType
		from t_insurance_detail a
		where a.DELETE_FLAG = 0
		<if test="param.isEffect != null">
			and a.IS_EFFECT = #{param.isEffect}
		</if>
		<if test="param.id != null">
			and a.id = #{param.id}
		</if>
		<if test="param.deptNo != null and param.deptNo.trim() != ''">
			and a.DEPT_NO = #{param.deptNo}
		</if>
		<if test="param.buyHandleStatus != null">
			and a.BUY_HANDLE_STATUS = #{param.buyHandleStatus}
		</if>
		<if test="param.empName != null and param.empName.trim() != ''">
			and a.EMP_NAME like concat('%',replace(replace(#{param.empName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.empIdcardNo != null and param.empIdcardNo.trim() != ''">
			and a.EMP_IDCARD_NO like concat('%',replace(replace(#{param.empIdcardNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.insuranceCompanyName != null and param.insuranceCompanyName.trim() != ''">
			and a.INSURANCE_COMPANY_NAME = #{param.insuranceCompanyName}
		</if>
		<if test="param.insuranceTypeName != null and param.insuranceTypeName.trim() != ''">
			and a.INSURANCE_TYPE_NAME like concat('%',replace(replace(#{param.insuranceTypeName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.buyHandleStatusList != null and param.buyHandleStatusList.size > 0">
			and a.BUY_HANDLE_STATUS in
			<foreach collection="param.buyHandleStatusList" index="index" item="item" open="(" separator="," close=")">
				#{item}
			</foreach>
		</if>
		<if test="param.buyType != null">
			and a.BUY_TYPE = #{param.buyType}
		</if>
		<if test="param.createName != null and param.createName.trim() != ''">
			and a.CREATE_NAME like concat('%',replace(replace(#{param.createName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.startDate != null and param.startDate.trim() != '' and param.endDate != null and param.endDate.trim() != ''">
			and a.CREATE_TIME <![CDATA[ >= ]]>  concat(#{param.startDate}, ' 00:00:00') and a.CREATE_TIME <![CDATA[ <= ]]> concat(#{param.endDate}, ' 23:59:59')
		</if>
		<if test="param.reduceHandleStatus != null">
			and a.REDUCE_HANDLE_STATUS = #{param.reduceHandleStatus}
		</if>
		<if test="param.isAdress != null and param.isAdress.trim() != ''">
			and a.IS_ADRESS = #{param.isAdress}
		</if>
		<if test="param.paymentStatus != null and param.paymentStatus.trim() != ''">
			and a.PAYMENT_STATUS = #{param.paymentStatus}
		</if>
		<if test="param.paymentTimeStart != null and param.paymentTimeStart.trim() != '' ">
			and a.PAYMENT_TIME <![CDATA[ >= ]]>  #{param.paymentTimeStart}
		</if>
		<if test="param.paymentTimeEnd != null and param.paymentTimeEnd.trim() != ''">
			and a.PAYMENT_TIME <![CDATA[ <= ]]> #{param.paymentTimeEnd}
		</if>
		<if test="param.isJfcd != null and param.isJfcd.trim() != ''">
			and a.IS_JFCD = #{param.isJfcd}
		</if>
		<if test="param.estimatePremiumStart != null">
			and a.ESTIMATE_PREMIUM <![CDATA[ >= ]]>  #{param.estimatePremiumStart}
		</if>
		<if test="param.estimatePremiumEnd != null">
			and a.ESTIMATE_PREMIUM <![CDATA[ <= ]]> #{param.estimatePremiumEnd}
		</if>
		<if test="param.authSql != null and param.authSql.trim() != ''">
			${param.authSql}
		</if>
		ORDER BY a.BUY_HANDLE_STATUS,a.CREATE_TIME DESC
	</select>
	<!--投保办理分页查询-->
	<select id="getInsuranceHandleListPage" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.InsuranceListVO">
		select a.id as id,
		a.EMP_NAME as empName,
		a.EMP_IDCARD_NO as empIdcardNo,
		a.BUY_TYPE as buyType,
		a.POST as post,
		a.DEPT_NO as deptNo,
		a.POLICY_START as policyStart,
		a.POLICY_END as policyEnd,
		a.INSURANCE_COMPANY_NAME as insuranceCompanyName,
		a.INSURANCE_TYPE_NAME as insuranceTypeName,
		a.BUY_STANDARD as buyStandard,
	    a.CREATE_TIME as createTime,
		a.CREATE_NAME as createName,
		a.BUY_HANDLE_STATUS as buyHandleStatus,
		a.SETTLE_TYPE as settleType,
		a.SETTLE_MONTH as settleMonth,
		a.DEPT_NAME as projectName,
		a.IS_ADRESS as isAdress,a.PURCHASE_CYCLE as purchaseCycle
		from t_insurance_detail a
		where a.DELETE_FLAG = 0
		<if test="param.deptNo != null and param.deptNo.trim() != ''">
			and a.DEPT_NO = #{param.deptNo}
		</if>
		<if test="param.buyHandleStatus != null">
			and a.BUY_HANDLE_STATUS = #{param.buyHandleStatus}
		</if>
		<if test="param.empName != null and param.empName.trim() != ''">
			and a.EMP_NAME like concat('%',replace(replace(#{param.empName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.empIdcardNo != null and param.empIdcardNo.trim() != ''">
			and a.EMP_IDCARD_NO like concat('%',replace(replace(#{param.empIdcardNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.insuranceCompanyName != null and param.insuranceCompanyName.trim() != ''">
			and a.INSURANCE_COMPANY_NAME = #{param.insuranceCompanyName}
		</if>
		<if test="param.insuranceTypeName != null and param.insuranceTypeName.trim() != ''">
			and a.INSURANCE_TYPE_NAME like concat('%',replace(replace(#{param.insuranceTypeName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.buyHandleStatusList != null and param.buyHandleStatusList.size > 0">
			and a.BUY_HANDLE_STATUS in
			<foreach collection="param.buyHandleStatusList" index="index" item="item" open="(" separator="," close=")">
				#{item}
			</foreach>
		</if>
		<if test="param.updateBy != null and param.updateBy.trim() != ''">
			and a.HANDLED_BY = #{param.updateBy}
		</if>
		<if test="param.buyType != null">
			and a.BUY_TYPE = #{param.buyType}
		</if>
		<if test="param.createName != null and param.createName.trim() != ''">
			and a.CREATE_NAME like concat('%',replace(replace(#{param.createName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.startDate != null and param.startDate.trim() != '' and param.endDate != null and param.endDate.trim() != ''">
			and a.CREATE_TIME <![CDATA[ >= ]]>  concat(#{param.startDate}, ' 00:00:00') and a.CREATE_TIME <![CDATA[ <= ]]> concat(#{param.endDate}, ' 23:59:59')
		</if>
		<if test="param.isAdress != null and param.isAdress.trim() != ''">
			and a.IS_ADRESS = #{param.isAdress}
		</if>
		<if test="param.authSql != null and param.authSql.trim() != ''">
			${param.authSql}
		</if>
		<if test="param.buyStandardMin != null">
			AND a.BUY_STANDARD <![CDATA[ >= ]]> #{param.buyStandardMin}
		</if>
		<if test="param.buyStandardMax != null">
			AND a.BUY_STANDARD <![CDATA[ <= ]]> #{param.buyStandardMax}
		</if>
		ORDER BY a.BUY_HANDLE_STATUS,a.CREATE_TIME DESC
	</select>
	<!--通过id查询商险信息-->
	<select id="getInsuranceDetailById" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.InsuranceDetailVO">
		select
		a.id as id,
		a.EMP_NAME as empName,
		a.EMP_IDCARD_NO as empIdcardNo,
		a.BUY_TYPE as buyType,
		a.DEPT_NO as deptNo,
		a.DEPT_NAME as projectName,
		a.POST as post,
		a.POLICY_START as policyStart,
		a.POLICY_END as policyEnd,
		a.POLICY_EFFECT as policyEffect,
		a.POLICY_NO as policyNo,
		a.INSURANCE_TYPE_NAME as insuranceTypeName,
		a.INSURANCE_TYPE_ID as insuranceTypeId,
		a.BUY_STANDARD as buyStandard,
		a.MEDICAL_QUOTA as medicalQuota,
		a.DIE_DISABLE_QUOTA as dieDisableQuota,
		a.INSURANCE_COMPANY_NAME as insuranceCompanyName,
		a.SETTLE_TYPE as premiumType,
		a.ESTIMATE_PREMIUM as estimatePremium,
		a.ACTUAL_PREMIUM as actualPremium,
		a.INVOICE_NO as invoiceNo,
		a.SETTLE_TYPE as settleType,
		a.SETTLE_MONTH as settleMonth,
		a.BUY_HANDLE_STATUS as buyHandleStatus,
		a.REDUCE_HANDLE_STATUS as reduceHandleStatus,
		a.IS_USE as isUse,
		a.IS_EFFECT as isEffect,
		a.IS_OVERDUE as isOverdue,
		a.INSURANCE_PROVINCE_NAME as insuranceProvinceName,
		a.INSURANCE_PROVINCE as insuranceProvince,
		a.INSURANCE_CITY_NAME as insuranceCityName,
		a.INSURANCE_CITY as insuranceCity,
		a.INSURANCE_HANDLE_PROVINCE_NAME as insuranceHandleProvinceName,
		a.INSURANCE_HANDLE_PROVINCE as insuranceHandleProvince,
		a.INSURANCE_HANDLE_CITY_NAME as insuranceHandleCityName,
		a.INSURANCE_HANDLE_CITY as insuranceHandleCity,
		a.CREATE_NAME as createName,
		a.CREATE_TIME as createTime,
		a.REMARK as remark,
		a.BILLING_TYPE as billingType,
		a.DEFAULT_SETTLE_ID as defaultSettleId,
		a.RATE as  rate,
	    a.REFUND_ID as  refundId,
		a.EXPIRE_REMARK,
		a.EXPIRE_IGNORE_FLAG,
		a.IS_ADRESS as isAdress,a.PAYMENT_STATUS,a.PAYMENT_TIME,a.IS_JFCD,
		a.PURCHASE_CYCLE,a.PRE_HANDLE_TIME
		from t_insurance_detail a
		left join t_insurance_settle tis on a.DEFAULT_SETTLE_ID = tis.ID
		where a.ID = #{id}
	</select>
	<!--导出办理勾选查询结果-->
	<select id="getInsuranceExportListBySelect" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.InsuranceExportListVO">
		select a.id as id,
		a.DEPT_NO as deptNo,
		a.DEPT_NAME as projectName,
		a.EMP_NAME as empName,
		a.EMP_IDCARD_NO as empIdcardNo,
		a.BUY_TYPE as buyType,
		a.BUY_HANDLE_STATUS as buyHandleStatus,
		a.POST as post,
		a.INSURANCE_PROVINCE_NAME as insuranceProvinceName,
		a.INSURANCE_CITY_NAME as insuranceCityName,
		a.INSURANCE_HANDLE_PROVINCE_NAME as insuranceHandleProvinceName,
		a.INSURANCE_HANDLE_CITY_NAME as insuranceHandleCityName,
		a.POLICY_START as policyStart,
		a.POLICY_END as policyEnd,
		a.POLICY_EFFECT as policyEffect,
		a.INSURANCE_COMPANY_NAME as insuranceCompanyName,
		a.INSURANCE_TYPE_NAME as insuranceTypeName,
		a.BUY_STANDARD as buyStandard,
		a.MEDICAL_QUOTA as medicalQuota,
		a.DIE_DISABLE_QUOTA as dieDisableQuota,
		a.ACTUAL_PREMIUM as actualPremium,
		a.SETTLE_MONTH as settleMonth,
		a.SETTLE_TYPE as settleType,
		a.CREATE_NAME as createName,
		a.POLICY_NO as policyNo,
		a.REMARK as remark,
		a.IS_ADRESS as isAdress,a.DEFAULT_SETTLE_ID as defaultSettleId
		from t_insurance_detail a
		where a.DELETE_FLAG = 0 and a.id in
		<foreach collection="idList" index="index" item="item" open="(" separator="," close=")">
			#{item}
		</foreach>
		ORDER BY a.CREATE_TIME DESC
	</select>
	<!--出办理非勾选查询结果-->
	<select id="getInsuranceExportList" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.InsuranceExportListVO">
		select a.id as id,
			a.DEPT_NO as deptNo,
			a.DEPT_NAME as projectName,
			a.EMP_NAME as empName,
			a.EMP_IDCARD_NO as empIdcardNo,
			a.BUY_TYPE as buyType,
			a.POST as post,
			a.INSURANCE_PROVINCE_NAME as insuranceProvinceName,
			a.INSURANCE_CITY_NAME as insuranceCityName,
			a.INSURANCE_HANDLE_PROVINCE_NAME as insuranceHandleProvinceName,
			a.INSURANCE_HANDLE_CITY_NAME as insuranceHandleCityName,
			a.POLICY_START as policyStart,
			a.POLICY_END as policyEnd,
			a.POLICY_EFFECT as policyEffect,
			a.INSURANCE_COMPANY_NAME as insuranceCompanyName,
			a.INSURANCE_TYPE_NAME as insuranceTypeName,
			a.BUY_STANDARD as buyStandard,
			a.MEDICAL_QUOTA as medicalQuota,
			a.DIE_DISABLE_QUOTA as dieDisableQuota,
			a.ACTUAL_PREMIUM as actualPremium,
			a.SETTLE_MONTH as settleMonth,
			a.SETTLE_TYPE as settleType,
			a.CREATE_NAME as createName,
			a.POLICY_NO as policyNo,
			a.REMARK as remark,
			a.IS_ADRESS as isAdress,a.DEFAULT_SETTLE_ID as defaultSettleId
		from t_insurance_detail a
		where a.DELETE_FLAG = 0 and a.BUY_HANDLE_STATUS = #{param.buyHandleStatus}
		<if test="param.empName != null and param.empName.trim() != ''">
			and a.EMP_NAME like concat('%',replace(replace(#{param.empName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.empIdcardNo != null and param.empIdcardNo.trim() != ''">
			and a.EMP_IDCARD_NO like concat('%',replace(replace(#{param.empIdcardNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.deptNo != null and param.deptNo.trim() != ''">
			and a.DEPT_NO like concat('%',replace(replace(#{param.deptNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.insuranceCompanyName != null and param.insuranceCompanyName.trim() != ''">
			and a.INSURANCE_COMPANY_NAME = #{param.insuranceCompanyName}
		</if>
		<if test="param.buyType != null">
			and a.BUY_TYPE = #{param.buyType}
		</if>
		<if test="param.createName != null and param.createName.trim() != ''">
			and a.CREATE_NAME like concat('%',replace(replace(#{param.createName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.startDate != null and param.startDate.trim() != '' and param.endDate != null and param.endDate.trim() != ''">
			and a.CREATE_TIME <![CDATA[ >= ]]>  concat(#{param.startDate}, ' 00:00:00') and a.CREATE_TIME <![CDATA[ <= ]]> concat(#{param.endDate}, ' 23:59:59')
		</if>
		<if test="param.updateBy != null and param.updateBy.trim() != ''">
			and a.HANDLED_BY = #{param.updateBy}
		</if>
		<if test="param.isAdress != null and param.isAdress.trim() != ''">
			and a.IS_ADRESS = #{param.isAdress}
		</if>
		<if test="param.authSql != null and param.authSql.trim() != ''">
			${param.authSql}
		</if>
		<if test="param.buyStandardMin != null">
			AND a.BUY_STANDARD <![CDATA[ >= ]]> #{param.buyStandardMin}
		</if>
		<if test="param.buyStandardMax != null">
			AND a.BUY_STANDARD <![CDATA[ <= ]]> #{param.buyStandardMax}
		</if>
		ORDER BY a.CREATE_TIME DESC
	</select>
	<!--导出办理勾选查询结果-->
	<select id="getInsuranceExportOneListBySelect" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.InsuranceExportListOneVO">
		select a.id as id,
		a.DEPT_NO as deptNo,
		a.DEPT_NAME as projectName,
		a.EMP_NAME as empName,
		a.EMP_IDCARD_NO as empIdcardNo,
		a.BUY_TYPE as buyType,
		a.BUY_HANDLE_STATUS as buyHandleStatus,
		a.POST as post,
		a.INSURANCE_PROVINCE_NAME as insuranceProvinceName,
		a.INSURANCE_CITY_NAME as insuranceCityName,
		a.POLICY_START as policyStart,
		a.POLICY_END as policyEnd,
		a.POLICY_EFFECT as policyEffect,
		a.INSURANCE_COMPANY_NAME as insuranceCompanyName,
		a.INSURANCE_TYPE_NAME as insuranceTypeName,
		a.BUY_STANDARD as buyStandard,
		a.MEDICAL_QUOTA as medicalQuota,
		a.DIE_DISABLE_QUOTA as dieDisableQuota,
		a.ACTUAL_PREMIUM as actualPremium,
		a.SETTLE_MONTH as settleMonth,
		a.SETTLE_TYPE as settleType,
		a.CREATE_NAME as createName,
		a.POLICY_NO as policyNo,
		a.REMARK as remark,a.UNIT_NAME as unitName,a.DEFAULT_SETTLE_ID as defaultSettleId,a.PURCHASE_CYCLE as purchaseCycle
		from t_insurance_detail a
		where a.DELETE_FLAG = 0 and a.id in
		<foreach collection="idList" index="index" item="item" open="(" separator="," close=")">
			#{item}
		</foreach>
		ORDER BY a.CREATE_TIME DESC
	</select>
	<!--出办理非勾选查询结果-->
	<select id="getInsuranceExportOnetList" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.InsuranceExportListOneVO">
		select a.id as id,
		a.DEPT_NO as deptNo,
		a.DEPT_NAME as projectName,
		a.EMP_NAME as empName,
		a.EMP_IDCARD_NO as empIdcardNo,
		a.BUY_TYPE as buyType,
		a.POST as post,
		a.INSURANCE_PROVINCE_NAME as insuranceProvinceName,
		a.INSURANCE_CITY_NAME as insuranceCityName,
		a.POLICY_START as policyStart,
		a.POLICY_END as policyEnd,
		a.POLICY_EFFECT as policyEffect,
		a.INSURANCE_COMPANY_NAME as insuranceCompanyName,
		a.INSURANCE_TYPE_NAME as insuranceTypeName,
		a.BUY_STANDARD as buyStandard,
		a.MEDICAL_QUOTA as medicalQuota,
		a.DIE_DISABLE_QUOTA as dieDisableQuota,
		a.ACTUAL_PREMIUM as actualPremium,
		a.SETTLE_MONTH as settleMonth,
		a.SETTLE_TYPE as settleType,
		a.CREATE_NAME as createName,
		a.POLICY_NO as policyNo,
		a.REMARK as remark,a.UNIT_NAME as unitName,a.DEFAULT_SETTLE_ID as defaultSettleId
		from t_insurance_detail a
		where a.DELETE_FLAG = 0 and a.BUY_HANDLE_STATUS = #{param.buyHandleStatus}
		<if test="param.empName != null and param.empName.trim() != ''">
			and a.EMP_NAME like concat('%',replace(replace(#{param.empName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.empIdcardNo != null and param.empIdcardNo.trim() != ''">
			and a.EMP_IDCARD_NO like concat('%',replace(replace(#{param.empIdcardNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.deptNo != null and param.deptNo.trim() != ''">
			and a.DEPT_NO like concat('%',replace(replace(#{param.deptNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.insuranceCompanyName != null and param.insuranceCompanyName.trim() != ''">
			and a.INSURANCE_COMPANY_NAME = #{param.insuranceCompanyName}
		</if>
		<if test="param.buyType != null">
			and a.BUY_TYPE = #{param.buyType}
		</if>
		<if test="param.createName != null and param.createName.trim() != ''">
			and a.CREATE_NAME like concat('%',replace(replace(#{param.createName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.startDate != null and param.startDate.trim() != '' and param.endDate != null and param.endDate.trim() != ''">
			and a.CREATE_TIME <![CDATA[ >= ]]>  concat(#{param.startDate}, ' 00:00:00') and a.CREATE_TIME <![CDATA[ <= ]]> concat(#{param.endDate}, ' 23:59:59')
		</if>
		<if test="param.updateBy != null and param.updateBy.trim() != ''">
			and a.HANDLED_BY = #{param.updateBy}
		</if>
		<if test="param.isAdress != null and param.isAdress.trim() != ''">
			and a.IS_ADRESS = #{param.isAdress}
		</if>
		<if test="param.authSql != null and param.authSql.trim() != ''">
			${param.authSql}
		</if>
		<if test="param.buyStandardMin != null">
			AND a.BUY_STANDARD <![CDATA[ >= ]]> #{param.buyStandardMin}
		</if>
		<if test="param.buyStandardMax != null">
			AND a.BUY_STANDARD <![CDATA[ <= ]]> #{param.buyStandardMax}
		</if>
		ORDER BY a.CREATE_TIME DESC
	</select>
	<!--批量更新保单号-->
	<update id="updatePolicyNoBatch">
		<foreach collection="details" index="index" item="item" separator=";">
				update t_insurance_detail set POLICY_NO = #{item.policyNo} where ID = #{item.id}
		</foreach>
	</update>
	<!--批量更新发票号-->
	<update id="updateInvoiceNoBatch">
		<foreach collection="details" index="index" item="item" separator=";">
			update t_insurance_detail set INVOICE_NO = #{item.invoiceNo} where ID = #{item.id}
		</foreach>
	</update>

	<!-- 派单人员系统已有参保明细查询-->
	<select id="getOnProcessInsuredList" resultType="com.yifu.cloud.plus.v1.yifu.insurances.entity.TInsurancePreDetail">
		select
		a.id as id,
		a.EMP_NAME as empName,
		a.EMP_IDCARD_NO as empIdcardNo,
		a.BUY_TYPE as buyType,
		a.DEPT_NO as deptNo,
		a.DEPT_NAME as deptName,
		a.POST as post,
		a.POLICY_START as policyStart,
		a.POLICY_END as policyEnd,
		a.POLICY_EFFECT as policyEffect,
		a.INSURANCE_COMPANY_NAME as insuranceCompanyName,
		a.INSURANCE_TYPE_NAME as insuranceTypeName,
		a.BUY_STANDARD as buyStandard,
		a.BUY_HANDLE_STATUS as buyHandleStatus,
		a.CREATE_TIME as createTime,
		a.CREATE_NAME as createName,
		a.UNIT_NAME as unitName,
		a.UNIT_NO as unitNo,
		a.IS_ADRESS as isAdress,
		a.INSURANCE_PROVINCE_NAME as insuranceProvinceName,
		a.INSURANCE_CITY_NAME as insuranceCityName
		from
		t_insurance_detail a
		where
		a.DELETE_FLAG = 0
		  and (a.BUY_HANDLE_STATUS in (1,2,6) or (a.BUY_HANDLE_STATUS = 3 and a.IS_EFFECT = 0 and a.IS_OVERDUE = 0))
		  AND EMP_IDCARD_NO = #{empIdCard}
	</select>

	<!-- 派单人员系统已有非自动化的参保明细查询-->
	<select id="getUnAutoProcessInsuredList" resultType="com.yifu.cloud.plus.v1.yifu.insurances.entity.TInsurancePreDetail">
		select
			a.id as id,
			a.EMP_NAME as empName,
			a.EMP_IDCARD_NO as empIdcardNo,
			a.BUY_TYPE as buyType,
			a.DEPT_NO as deptNo,
			a.DEPT_NAME as deptName,
			a.POST as post,
			a.POLICY_START as policyStart,
			a.POLICY_END as policyEnd,
			a.POLICY_EFFECT as policyEffect,
			a.INSURANCE_COMPANY_NAME as insuranceCompanyName,
			a.INSURANCE_TYPE_NAME as insuranceTypeName,
			a.BUY_STANDARD as buyStandard,
			a.BUY_HANDLE_STATUS as buyHandleStatus,
			a.CREATE_TIME as createTime,
			a.CREATE_NAME as createName,
			a.UNIT_NAME as unitName,
			a.UNIT_NO as unitNo,
			a.IS_ADRESS as isAdress,
			a.INSURANCE_PROVINCE_NAME as insuranceProvinceName,
			a.INSURANCE_CITY_NAME as insuranceCityName
		from
			t_insurance_detail a
			left join t_employee_insurance_pre b on a.id = b.insurances_id
		where
			a.DELETE_FLAG = 0
		  and (a.BUY_HANDLE_STATUS in (1,2,6) or (a.BUY_HANDLE_STATUS = 3 and a.IS_EFFECT = 0 and a.IS_OVERDUE = 0))
		  AND EMP_IDCARD_NO = #{empIdCard} and b.id is null
		group by a.id
	</select>

	<!-- ***********************减员办理******************************** -->
	<!-- 已投保列表分页查询-->
	<select id="getInsuredListPage" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.InsuredListVo">
		select
			a.id as id,
			a.REDUCE_HANDLE_STATUS as reduceHandleStatus,
			a.EMP_NAME as empName,
			a.EMP_IDCARD_NO as empIdcardNo,
			a.BUY_TYPE as buyType,
			a.DEPT_NO as deptNo,
			a.DEPT_NAME as projectName,
			a.POST as post,
			a.POLICY_START as policyStart,
			a.POLICY_END as policyEnd,
			a.POLICY_EFFECT as policyEffect,
			a.IS_USE as isUse,
			a.IS_EFFECT as isEffect,
			a.IS_OVERDUE as isOverdue,
			a.INSURANCE_COMPANY_NAME as insuranceCompanyName,
			a.INSURANCE_TYPE_NAME as insuranceTypeName,
			a.CREATE_USER_DEPT_NAME as createUserDeptName,
			a.BUY_STANDARD as buyStandard,
			a.CREATE_TIME as createTime,
			a.CREATE_NAME as createName,
			a.UNIT_NAME as unitName,
			a.UNIT_NO as unitNo,
			a.IS_ADRESS as isAdress,
			a.REPLACE_TAG as replaceTag,
		    a.REPLACE_REMARK as replaceRemark
		from
		     t_insurance_detail a
		where
			a.DELETE_FLAG = 0
		and
			a.BUY_HANDLE_STATUS = 3
		<if test="param.isEffect != null">
			and a.IS_EFFECT = #{param.isEffect}
		</if>
		<if test="param.unitName != null and param.unitName.trim() != ''">
			and a.UNIT_NAME like concat(replace(replace(#{param.unitName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.unitNo != null and param.unitNo.trim() != ''">
			and a.UNIT_NO like concat(replace(replace(#{param.unitNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.createName != null and param.createName.trim() != ''">
			and a.CREATE_NAME like concat('%',replace(replace(#{param.createName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.buyType != null">
			and a.BUY_TYPE = #{param.buyType}
		</if>
		<if test="param.reduceHandleStatus != null">
			and a.REDUCE_HANDLE_STATUS = #{param.reduceHandleStatus}
		</if>
		<if test="param.empName != null and param.empName.trim() != ''">
			and a.EMP_NAME like concat('%',replace(replace(#{param.empName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.empIdcardNo != null and param.empIdcardNo.trim() != ''">
			and a.EMP_IDCARD_NO like concat('%',replace(replace(#{param.empIdcardNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.deptNo != null and param.deptNo.trim() != ''">
			and a.DEPT_NO = #{param.deptNo}
		</if>
		<if test="param.insuranceCompanyName != null and param.insuranceCompanyName.trim() != ''">
			and a.INSURANCE_COMPANY_NAME = #{param.insuranceCompanyName}
		</if>
		<if test="param.policyNo != null and param.policyNo.trim() != ''">
			and a.POLICY_NO like concat('%',replace(replace(#{param.policyNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.policyJingQueStart != null and param.policyJingQueStart.trim() != ''">
			AND  DATE_FORMAT(a.POLICY_START,"%Y-%m-%d")  = #{param.policyJingQueStart}
		</if>
		<if test="param.policyJingQueEnd != null and param.policyJingQueEnd.trim() != ''">
			AND   DATE_FORMAT(a.POLICY_END,"%Y-%m-%d") = #{param.policyJingQueEnd}
		</if>

		<if test="param.policyStart != null and param.policyStart.trim() != '' and param.policyEnd != null and param.policyEnd.trim() != ''">
			and (
			(a.POLICY_START <![CDATA[ <= ]]>  concat(#{param.policyStart}, ' 00:00:00') and a.POLICY_END <![CDATA[ >= ]]> concat(#{param.policyEnd}, ' 23:59:59')) OR
			(a.POLICY_START <![CDATA[ >= ]]>  concat(#{param.policyStart}, ' 00:00:00') and a.POLICY_START <![CDATA[ <= ]]> concat(#{param.policyEnd}, ' 23:59:59')) OR
			(a.POLICY_END <![CDATA[ >= ]]>  concat(#{param.policyStart}, ' 00:00:00') and a.POLICY_END <![CDATA[ <= ]]> concat(#{param.policyEnd}, ' 23:59:59'))
				)
		</if>
		<if test="param.invoiceNo != null and param.invoiceNo.trim() != ''">
			and a.INVOICE_NO like concat('%',replace(replace(#{param.invoiceNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.isUse != null">
			and a.IS_USE = #{param.isUse}
		</if>
		<if test="param.isOverdue != null">
			and a.IS_OVERDUE = #{param.isOverdue}
		</if>
		<if test="param.createStartTime != null and param.createStartTime.trim() != ''">
			AND a.CREATE_TIME  <![CDATA[ >= ]]>  concat(#{param.createStartTime}, ' 00:00:00')
		</if>
		<if test="param.createEndTime != null and param.createEndTime.trim() != ''">
			AND a.CREATE_TIME  <![CDATA[ <= ]]> concat(#{param.createEndTime}, ' 23:59:59')
		</if>
		<if test="param.isAdress != null and param.isAdress.trim() != ''">
			and a.IS_ADRESS = #{param.isAdress}
		</if>
		<if test="param.replaceTag != null and param.replaceTag.trim() != ''">
			and a.REPLACE_TAG = #{param.replaceTag}
		</if>
		<if test="param.replaceRemark != null and param.replaceRemark.trim() != ''">
			and a.REPLACE_REMARK like concat('%',replace(replace(#{param.replaceRemark},'_','\\_'),'%','\\%'),'%')
		</if>
		<if test="param.authSql != null and param.authSql.trim() != ''">
			${param.authSql}
		</if>
		ORDER BY a.CREATE_TIME DESC ,a.ID ASC
	</select>
	<!-- 已投保列表不分页查询-->
	<select id="getInsuredList" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.InsuredListVo">
		select
			a.id as id,
			a.EMP_NAME as empName,
			a.EMP_IDCARD_NO as empIdcardNo,
			a.BUY_TYPE as buyType,
			a.DEPT_NO as deptNo,
			a.DEPT_NAME as projectName,
			a.POST as post,
			a.POLICY_START as policyStart,
			a.POLICY_END as policyEnd,
			a.INSURANCE_COMPANY_NAME as insuranceCompanyName,
			a.INSURANCE_TYPE_NAME as insuranceTypeName,
			a.BUY_STANDARD as buyStandard,
			a.MEDICAL_QUOTA as medicalQuota,
			a.DIE_DISABLE_QUOTA as dieDisableQuota,
			a.ESTIMATE_PREMIUM as estimatePremium,
			a.ACTUAL_PREMIUM as actualPremium,
			a.POLICY_NO as policyNo,
			a.INVOICE_NO as invoiceNo,
			a.POLICY_EFFECT as policyEffect,
			a.INSURANCE_PROVINCE_NAME as insuranceProvinceName,
			CONCAT(a.INSURANCE_PROVINCE_NAME,'/',a.INSURANCE_CITY_NAME) as insuranceCityName,
			a.INSURANCE_HANDLE_PROVINCE_NAME as insuranceHandleProvinceName,
			CONCAT(a.INSURANCE_HANDLE_PROVINCE_NAME,'/',a.INSURANCE_HANDLE_CITY_NAME) as insuranceHandleCityName,
			a.IS_USE as isUse,
			a.IS_EFFECT as isEffect,
			a.IS_OVERDUE as isOverdue,
			a.CREATE_TIME as createTime,
			a.CREATE_NAME as createName,
			a.UNIT_NAME as unitName,
			a.UNIT_NO as unitNo,
			if(a.IS_ADRESS = '0','是',if(ifnull(a.IS_ADRESS,3) = 3,'','否')) as isAdress,a.remark,
			a.REPLACE_TAG as replaceTag,
			a.REPLACE_REMARK as replaceRemark
		from
		    t_insurance_detail a
		where
		    a.DELETE_FLAG = 0
		and
			a.BUY_HANDLE_STATUS = 3
		<if test="param.isEffect != null">
			and a.IS_EFFECT = #{param.isEffect}
		</if>
		<if test="param.unitName != null and param.unitName.trim() != ''">
			and a.UNIT_NAME like concat(replace(replace(#{param.unitName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.unitNo != null and param.unitNo.trim() != ''">
			and a.UNIT_NO like concat(replace(replace(#{param.unitNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.createName != null and param.createName.trim() != ''">
			and a.CREATE_NAME like concat('%',replace(replace(#{param.createName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.buyType != null">
			and a.BUY_TYPE = #{param.buyType}
		</if>
		<if test="param.reduceHandleStatus != null">
			and a.REDUCE_HANDLE_STATUS = #{param.reduceHandleStatus}
		</if>
		<if test="param.empName != null and param.empName.trim() != ''">
			and a.EMP_NAME like concat('%',replace(replace(#{param.empName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.empIdcardNo != null and param.empIdcardNo.trim() != ''">
			and a.EMP_IDCARD_NO like concat('%',replace(replace(#{param.empIdcardNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.deptNo != null and param.deptNo.trim() != ''">
			and a.DEPT_NO = #{param.deptNo}
		</if>
		<if test="param.insuranceCompanyName != null and param.insuranceCompanyName.trim() != ''">
			and a.INSURANCE_COMPANY_NAME = #{param.insuranceCompanyName}
		</if>
		<if test="param.policyNo != null and param.policyNo.trim() != ''">
			and a.POLICY_NO like concat('%',replace(replace(#{param.policyNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.policyStart != null and param.policyStart.trim() != '' and (param.policyEnd == null or param.policyEnd.trim() == '')">
			AND a.POLICY_END   <![CDATA[ >= ]]>  concat(#{param.policyStart}, ' 00:00:00')
		</if>
		<if test="param.policyEnd != null and param.policyEnd.trim() != '' and (param.policyStart == null or param.policyStart.trim() == '')">
			AND a.POLICY_START <![CDATA[ <= ]]> concat(#{param.policyEnd}, ' 23:59:59')
		</if>
		<if test="param.policyJingQueStart != null and param.policyJingQueStart.trim() != ''">
			AND  DATE_FORMAT(a.POLICY_START,"%Y-%m-%d")  = #{param.policyJingQueStart}
		</if>
		<if test="param.policyJingQueEnd != null and param.policyJingQueEnd.trim() != ''">
			AND   DATE_FORMAT(a.POLICY_END,"%Y-%m-%d") = #{param.policyJingQueEnd}
		</if>
		<if test="param.policyStart != null and param.policyStart.trim() != '' and param.policyEnd != null and param.policyEnd.trim() != ''">
			and (
			(a.POLICY_START <![CDATA[ <= ]]>  concat(#{param.policyStart}, ' 00:00:00') and a.POLICY_END <![CDATA[ >= ]]> concat(#{param.policyEnd}, ' 23:59:59')) OR
			(a.POLICY_START <![CDATA[ >= ]]>  concat(#{param.policyStart}, ' 00:00:00') and a.POLICY_START <![CDATA[ <= ]]> concat(#{param.policyEnd}, ' 23:59:59')) OR
			(a.POLICY_END <![CDATA[ >= ]]>  concat(#{param.policyStart}, ' 00:00:00') and a.POLICY_END <![CDATA[ <= ]]> concat(#{param.policyEnd}, ' 23:59:59'))
			)
		</if>
		<if test="param.createStartTime != null and param.createStartTime.trim() != ''">
			AND a.CREATE_TIME  <![CDATA[ >= ]]>  concat(#{param.createStartTime}, ' 00:00:00')
		</if>
		<if test="param.createEndTime != null and param.createEndTime.trim() != ''">
			AND a.CREATE_TIME  <![CDATA[ <= ]]> concat(#{param.createEndTime}, ' 23:59:59')
		</if>
		<if test="param.invoiceNo != null and param.invoiceNo.trim() != ''">
			and a.INVOICE_NO like concat('%',replace(replace(#{param.invoiceNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.isUse != null">
			and a.IS_USE = #{param.isUse}
		</if>
		<if test="param.isOverdue != null ">
			and a.IS_OVERDUE = #{param.isOverdue}
		</if>
		<if test="param.isAdress != null and param.isAdress.trim() != ''">
			and a.IS_ADRESS = #{param.isAdress}
		</if>
		<if test="param.replaceTag != null and param.replaceTag.trim() != ''">
			and a.REPLACE_TAG = #{param.replaceTag}
		</if>
		<if test="param.replaceRemark != null and param.replaceRemark.trim() != ''">
			and a.REPLACE_REMARK like concat('%',replace(replace(#{param.replaceRemark},'_','\\_'),'%','\\%'),'%')
		</if>
		<if test="param.authSql != null and param.authSql.trim() != ''">
			${param.authSql}
		</if>
		ORDER BY a.CREATE_TIME DESC
		<if test="param != null">
			<if test="param.limitStart != null">
				limit #{param.limitStart},#{param.limitEnd}
			</if>
		</if>
	</select>
	<select id="getInsuredListCount" resultType="java.lang.Long">
		select
		count(1)
		from
		t_insurance_detail a
		where
		a.DELETE_FLAG = 0
		and
		a.BUY_HANDLE_STATUS = 3
		<if test="param.isEffect != null">
			and a.IS_EFFECT = #{param.isEffect}
		</if>
		<if test="param.createName != null and param.createName.trim() != ''">
			and a.CREATE_NAME like concat('%',replace(replace(#{param.createName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.buyType != null">
			and a.BUY_TYPE = #{param.buyType}
		</if>
		<if test="param.reduceHandleStatus != null">
			and a.REDUCE_HANDLE_STATUS = #{param.reduceHandleStatus}
		</if>
		<if test="param.empName != null and param.empName.trim() != ''">
			and a.EMP_NAME like concat('%',replace(replace(#{param.empName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.empIdcardNo != null and param.empIdcardNo.trim() != ''">
			and a.EMP_IDCARD_NO like concat('%',replace(replace(#{param.empIdcardNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.deptNo != null and param.deptNo.trim() != ''">
			and a.DEPT_NO = #{param.deptNo}
		</if>
		<if test="param.insuranceCompanyName != null and param.insuranceCompanyName.trim() != ''">
			and a.INSURANCE_COMPANY_NAME = #{param.insuranceCompanyName}
		</if>
		<if test="param.policyNo != null and param.policyNo.trim() != ''">
			and a.POLICY_NO like concat('%',replace(replace(#{param.policyNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.policyStart != null and param.policyStart.trim() != '' and (param.policyEnd == null or param.policyEnd.trim() == '')">
			AND a.POLICY_END   <![CDATA[ >= ]]>  concat(#{param.policyStart}, ' 00:00:00')
		</if>
		<if test="param.policyEnd != null and param.policyEnd.trim() != '' and (param.policyStart == null or param.policyStart.trim() == '')">
			AND a.POLICY_START <![CDATA[ <= ]]> concat(#{param.policyEnd}, ' 23:59:59')
		</if>
		<if test="param.policyJingQueStart != null and param.policyJingQueStart.trim() != ''">
			AND  DATE_FORMAT(a.POLICY_START,"%Y-%m-%d")  = #{param.policyJingQueStart}
		</if>
		<if test="param.policyJingQueEnd != null and param.policyJingQueEnd.trim() != ''">
			AND   DATE_FORMAT(a.POLICY_END,"%Y-%m-%d") = #{param.policyJingQueEnd}
		</if>
		<if test="param.policyStart != null and param.policyStart.trim() != '' and param.policyEnd != null and param.policyEnd.trim() != ''">
			and (
			(a.POLICY_START <![CDATA[ <= ]]>  concat(#{param.policyStart}, ' 00:00:00') and a.POLICY_END <![CDATA[ >= ]]> concat(#{param.policyEnd}, ' 23:59:59')) OR
			(a.POLICY_START <![CDATA[ >= ]]>  concat(#{param.policyStart}, ' 00:00:00') and a.POLICY_START <![CDATA[ <= ]]> concat(#{param.policyEnd}, ' 23:59:59')) OR
			(a.POLICY_END <![CDATA[ >= ]]>  concat(#{param.policyStart}, ' 00:00:00') and a.POLICY_END <![CDATA[ <= ]]> concat(#{param.policyEnd}, ' 23:59:59'))
			)
		</if>
		<if test="param.createStartTime != null and param.createStartTime.trim() != ''">
			AND a.CREATE_TIME  <![CDATA[ >= ]]>  concat(#{param.createStartTime}, ' 00:00:00')
		</if>
		<if test="param.createEndTime != null and param.createEndTime.trim() != ''">
			AND a.CREATE_TIME  <![CDATA[ <= ]]> concat(#{param.createEndTime}, ' 23:59:59')
		</if>
		<if test="param.invoiceNo != null and param.invoiceNo.trim() != ''">
			and a.INVOICE_NO like concat('%',replace(replace(#{param.invoiceNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.isUse != null">
			and a.IS_USE = #{param.isUse}
		</if>
		<if test="param.isOverdue != null ">
			and a.IS_OVERDUE = #{param.isOverdue}
		</if>
		<if test="param.isAdress != null and param.isAdress.trim() != ''">
			and a.IS_ADRESS = #{param.isAdress}
		</if>
		<if test="param.replaceTag != null and param.replaceTag.trim() != ''">
			and a.REPLACE_TAG = #{param.replaceTag}
		</if>
		<if test="param.replaceRemark != null and param.replaceRemark.trim() != ''">
			and a.REPLACE_REMARK like concat('%',replace(replace(#{param.replaceRemark},'_','\\_'),'%','\\%'),'%')
		</if>
		<if test="param.authSql != null and param.authSql.trim() != ''">
			${param.authSql}
		</if>
		ORDER BY a.CREATE_TIME DESC
	</select>
	<!-- 已减员列表分页查询-->
	<select id="getInsuranceRefundPageList" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.InsuranceRefundListVo">
		select
		    a.id as id,
			a.EMP_NAME as empName,
			a.EMP_IDCARD_NO as empIdcardNo,
			a.POST as post,
			a.DEPT_NO as deptNo,
			a.DEPT_NAME as projectName,
			a.INSURANCE_COMPANY_NAME as insuranceCompanyName,
		    a.INSURANCE_TYPE_NAME as insuranceTypeName,
		    a.BUY_STANDARD as buyStandard,
			a.POLICY_START as policyStart,
			a.POLICY_END as policyEnd,
			a.POLICY_NO as policyNo,
			a.CREATE_NAME as createName,
			a.BUY_TYPE as buyType,
			refund.CREATE_USER_DEPT_NAME as createUserDeptName,
			refund.CREATE_TIME as refundCreateTime,
			a.CREATE_TIME as createTime,
		    a.IS_EFFECT as isEffect,
			a.IS_ADRESS as isAdress
		from
			t_insurance_detail a,
			t_insurance_refund refund
		where
		    a.DELETE_FLAG = 0
		and
			a.REFUND_ID = refund.ID
		and
			a.REDUCE_HANDLE_STATUS = 4
		<if test="param.isEffect != null">
			and a.IS_EFFECT = #{param.isEffect}
		</if>
		<if test="param.createName != null and param.createName.trim() != ''">
			and a.CREATE_NAME like concat('%',replace(replace(#{param.createName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.empName != null and param.empName.trim() != ''">
			and a.EMP_NAME like concat('%',replace(replace(#{param.empName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.empIdcardNo != null and param.empIdcardNo.trim() != ''">
			and a.EMP_IDCARD_NO like concat('%',replace(replace(#{param.empIdcardNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.deptNo != null and param.deptNo.trim() != ''">
			and a.DEPT_NO = #{param.deptNo}
		</if>
		<if test="param.insuranceCompanyName != null and param.insuranceCompanyName.trim() != ''">
			and a.INSURANCE_COMPANY_NAME = #{param.insuranceCompanyName}
		</if>
		<if test="param.policyNo != null and param.policyNo.trim() != ''">
			and a.POLICY_NO like concat('%',replace(replace(#{param.policyNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.policyStart != null and param.policyStart.trim() != '' and ( param.policyEnd == null or param.policyEnd.trim() == '' )">
			AND a.POLICY_END   <![CDATA[ >= ]]>  concat(#{param.policyStart}, ' 00:00:00')
		</if>
		<if test="param.policyEnd != null and param.policyEnd.trim() != '' and (param.policyStart == null or param.policyStart.trim() == '')">
			AND a.POLICY_START <![CDATA[ <= ]]> concat(#{param.policyEnd}, ' 23:59:59')
		</if>
		<if test="param.policyJingQueStart != null and param.policyJingQueStart.trim() != ''">
			AND  DATE_FORMAT(a.POLICY_START,"%Y-%m-%d")  = #{param.policyJingQueStart}
		</if>
		<if test="param.policyJingQueEnd != null and param.policyJingQueEnd.trim() != ''">
			AND   DATE_FORMAT(a.POLICY_END,"%Y-%m-%d") = #{param.policyJingQueEnd}
		</if>
		<if test="param.policyStart != null and param.policyStart.trim() != '' and param.policyEnd != null and param.policyEnd.trim() != ''">
			and (
			(a.POLICY_START <![CDATA[ <= ]]>  concat(#{param.policyStart}, ' 00:00:00') and a.POLICY_END <![CDATA[ >= ]]> concat(#{param.policyEnd}, ' 23:59:59')) OR
			(a.POLICY_START <![CDATA[ >= ]]>  concat(#{param.policyStart}, ' 00:00:00') and a.POLICY_START <![CDATA[ <= ]]> concat(#{param.policyEnd}, ' 23:59:59')) OR
			(a.POLICY_END <![CDATA[ >= ]]>  concat(#{param.policyStart}, ' 00:00:00') and a.POLICY_END <![CDATA[ <= ]]> concat(#{param.policyEnd}, ' 23:59:59'))
			)
		</if>
		<if test="param.startDate != null and param.startDate.trim() != ''">
			AND a.CREATE_TIME  <![CDATA[ >= ]]>  concat(#{param.startDate}, ' 00:00:00')
		</if>
		<if test="param.endDate != null and param.endDate.trim() != ''">
			AND a.CREATE_TIME  <![CDATA[ <= ]]> concat(#{param.endDate}, ' 23:59:59')
		</if>
		<if test="param.isAdress != null and param.isAdress.trim() != ''">
			and a.IS_ADRESS = #{param.isAdress}
		</if>
		<if test="param.authSql != null and param.authSql.trim() != ''">
			${param.authSql}
		</if>
		ORDER BY refund.CREATE_TIME DESC,a.ID ASC
	</select>
	<!-- 已减员列表不分页查询-->
	<select id="getInsuranceRefundList" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.InsuranceRefundListVo">
		select
			a.id as id,
			a.DEPT_NO as deptNo,
			a.DEPT_NAME as projectName,
			a.EMP_NAME as empName,
			a.EMP_IDCARD_NO as empIdcardNo,
			a.BUY_TYPE as buyType,
			a.POST as post,
			a.INSURANCE_PROVINCE_NAME as insuranceProvinceName,
			a.INSURANCE_CITY_NAME as insuranceCityName,
			a.INSURANCE_HANDLE_PROVINCE_NAME as insuranceHandleProvinceName,
			a.INSURANCE_HANDLE_CITY_NAME as insuranceHandleCityName,
			a.POLICY_START as policyStart,
			a.POLICY_END as policyEnd,
		    a.POLICY_NO as policyNo,
		    a.ACTUAL_PREMIUM as actualPremium,
			a.INSURANCE_COMPANY_NAME as insuranceCompanyName,
			a.INSURANCE_TYPE_NAME as insuranceTypeName,
			a.BUY_STANDARD as buyStandard,
			a.MEDICAL_QUOTA as medicalQuota,
			a.DIE_DISABLE_QUOTA as dieDisableQuota,
			a.CREATE_NAME as createName,
			refund.CREATE_TIME as refundCreateTime,
			a.IS_EFFECT as isEffect,
			a.IS_ADRESS as isAdress,refund.remark as remark
		from
			t_insurance_detail a,
			t_insurance_refund refund
		where
			a.REFUND_ID = refund.ID
		and
			a.DELETE_FLAG = 0
		and
			a.REDUCE_HANDLE_STATUS = 4
		<if test="param.isEffect != null">
			and a.IS_EFFECT = #{param.isEffect}
		</if>
		<if test="param.createName != null and param.createName.trim() != ''">
			and a.CREATE_NAME like concat('%',replace(replace(#{param.createName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.empName != null and param.empName.trim() != ''">
			and a.EMP_NAME like concat('%',replace(replace(#{param.empName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.empIdcardNo != null and param.empIdcardNo.trim() != ''">
			and a.EMP_IDCARD_NO like concat('%',replace(replace(#{param.empIdcardNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.deptNo != null and param.deptNo.trim() != ''">
			and a.DEPT_NO = #{param.deptNo}
		</if>
		<if test="param.insuranceCompanyName != null and param.insuranceCompanyName.trim() != ''">
			and a.INSURANCE_COMPANY_NAME = #{param.insuranceCompanyName}
		</if>
		<if test="param.policyNo != null and param.policyNo.trim() != ''">
			and a.POLICY_NO like concat('%',replace(replace(#{param.policyNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.policyStart != null and param.policyStart.trim() != '' and (param.policyEnd == null or param.policyEnd.trim() == '')">
			AND a.POLICY_END   <![CDATA[ >= ]]>  concat(#{param.policyStart}, ' 00:00:00')
		</if>
		<if test="param.policyEnd != null and param.policyEnd.trim() != '' and (param.policyStart == null or param.policyStart.trim() == '')">
			AND a.POLICY_START <![CDATA[ <= ]]> concat(#{param.policyEnd}, ' 23:59:59')
		</if>
		<if test="param.policyJingQueStart != null and param.policyJingQueStart.trim() != ''">
			AND  DATE_FORMAT(a.POLICY_START,"%Y-%m-%d")  = #{param.policyJingQueStart}
		</if>
		<if test="param.policyJingQueEnd != null and param.policyJingQueEnd.trim() != ''">
			AND   DATE_FORMAT(a.POLICY_END,"%Y-%m-%d") = #{param.policyJingQueEnd}
		</if>
		<if test="param.policyStart != null and param.policyStart.trim() != '' and param.policyEnd != null and param.policyEnd.trim() != ''">
			and (
			(a.POLICY_START <![CDATA[ <= ]]>  concat(#{param.policyStart}, ' 00:00:00') and a.POLICY_END <![CDATA[ >= ]]> concat(#{param.policyEnd}, ' 23:59:59')) OR
			(a.POLICY_START <![CDATA[ >= ]]>  concat(#{param.policyStart}, ' 00:00:00') and a.POLICY_START <![CDATA[ <= ]]> concat(#{param.policyEnd}, ' 23:59:59')) OR
			(a.POLICY_END <![CDATA[ >= ]]>  concat(#{param.policyStart}, ' 00:00:00') and a.POLICY_END <![CDATA[ <= ]]> concat(#{param.policyEnd}, ' 23:59:59'))
			)
		</if>
		<if test="param.startDate != null and param.startDate.trim() != ''">
			AND a.CREATE_TIME  <![CDATA[ >= ]]>  concat(#{param.startDate}, ' 00:00:00')
		</if>
		<if test="param.endDate != null and param.endDate.trim() != ''">
			AND a.CREATE_TIME  <![CDATA[ <= ]]> concat(#{param.endDate}, ' 23:59:59')
		</if>
		<if test="param.isAdress != null and param.isAdress.trim() != ''">
			and a.IS_ADRESS = #{param.isAdress}
		</if>
		<if test="param.authSql != null and param.authSql.trim() != ''">
			${param.authSql}
		</if>
		ORDER BY refund.CREATE_TIME DESC
	</select>
	<!-- 减员办理列表分页查询-->
	<select id="getInsuranceRefundHandlingPageList" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.RefundExportListVo">
		select
		DISTINCT
		a.id as id,
		a.EMP_NAME as empName,
		a.EMP_IDCARD_NO as empIdcardNo,
		a.BUY_TYPE as buyType,
		a.DEPT_NO as deptNo,
		a.DEPT_NAME as projectName,
		a.POST as post,
		a.INSURANCE_PROVINCE_NAME as insuranceProvinceName,
		a.INSURANCE_CITY_NAME as insuranceCityName,
		a.INSURANCE_HANDLE_PROVINCE_NAME as insuranceHandleProvinceName,
		a.INSURANCE_HANDLE_CITY_NAME as insuranceHandleCityName,
		a.POLICY_START as policyStart,
		a.POLICY_END as policyEnd,
		a.INSURANCE_COMPANY_NAME as insuranceCompanyName,
		a.INSURANCE_TYPE_NAME as insuranceTypeName,
		a.BUY_STANDARD as buyStandard,
		a.MEDICAL_QUOTA as medicalQuota,
		a.DIE_DISABLE_QUOTA as dieDisableQuota,
		refund.CREATE_TIME as createTime,
		refund.CREATE_NAME as createName,
		a.REDUCE_HANDLE_STATUS as reduceHandleStatus,
		a.IS_ADRESS as isAdress
		from
			t_insurance_detail a,
			t_insurance_refund refund
		where
			a.REFUND_ID = refund.ID
		<if test="param.empName != null and param.empName.trim() != ''">
			and a.EMP_NAME like concat('%',replace(replace(#{param.empName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.empIdcardNo != null and param.empIdcardNo.trim() != ''">
			and a.EMP_IDCARD_NO like concat('%',replace(replace(#{param.empIdcardNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.deptNo != null and param.deptNo.trim() != ''">
			and a.DEPT_NO = #{param.deptNo}
		</if>
		<if test="param.reduceHandleStatus != null">
			and a.REDUCE_HANDLE_STATUS = #{param.reduceHandleStatus}
		</if>
		<if test="param.insuranceTypeName != null and param.insuranceTypeName.trim() != ''">
			and a.INSURANCE_TYPE_NAME like concat('%',replace(replace(#{param.insuranceTypeName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.insuranceCompanyName != null and param.insuranceCompanyName.trim() != ''">
			and a.INSURANCE_COMPANY_NAME = #{param.insuranceCompanyName}
		</if>
		<if test="param.updateBy != null and param.updateBy.trim() != ''">
			and  refund.UPDATE_BY = #{param.updateBy}
		</if>
		<if test="param.createName != null and param.createName.trim() != ''">
			and refund.CREATE_NAME like concat('%',replace(replace(#{param.createName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.startDate != null and param.startDate.trim() != ''">
			AND refund.CREATE_TIME   <![CDATA[ >= ]]>  concat(#{param.startDate}, ' 00:00:00')
		</if>
		<if test="param.endDate != null and param.endDate.trim() != ''">
			AND refund.CREATE_TIME <![CDATA[ <= ]]> concat(#{param.endDate}, ' 23:59:59')
		</if>
		<if test="param.buyStandardMin != null and param.buyStandardMin.trim() != ''">
			AND a.BUY_STANDARD   <![CDATA[ >= ]]>  #{param.buyStandardMin}
		</if>
		<if test="param.buyStandardMax != null and param.buyStandardMax.trim() != ''">
			AND a.BUY_STANDARD <![CDATA[ <= ]]> #{param.buyStandardMax}
		</if>
		<if test="param.isAdress != null and param.isAdress.trim() != ''">
			and a.IS_ADRESS = #{param.isAdress}
		</if>
		<if test="param.authSql != null and param.authSql.trim() != ''">
			${param.authSql}
		</if>
		ORDER BY refund.CREATE_TIME DESC,a.ID ASC
	</select>
	<!-- 查询保单列表-->
	<select id="selectDetailListByIds"
			resultType="com.yifu.cloud.plus.v1.yifu.insurances.entity.TInsuranceDetail">
		select
			<include refid="Base_Column_List"></include>
		from
				t_insurance_detail
		where
			ID in
			<foreach collection="param.idList" index="index" item="item" open="(" separator="," close=")">
				#{item}
			</foreach>
	</select>

	<!-- 查询保单减员退费信息-->
	<select id="selectRefundDetailId" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.InsuranceRefundVo">
		select
			refund.ID as id,
			refund.INS_DETAIL_ID as insDetailId,
			refund.REFUND_MONEY as refundMoney,
		    a.INSURANCE_HANDLE_PROVINCE as insuranceHandleProvince,
		    a.INSURANCE_HANDLE_CITY as insuranceHandleCity
		from
			t_insurance_detail a,
			t_insurance_refund refund
		where
	  		a.DELETE_FLAG = 0
			and a.REFUND_ID = refund.ID
			and a.BUY_HANDLE_STATUS = 5
			and a.REDUCE_HANDLE_STATUS = 4
			and a.EMP_NAME = #{param.empName}
			and a.EMP_IDCARD_NO = #{param.empIdcardNo}
			and a.INSURANCE_COMPANY_NAME = #{param.insuranceCompanyName}
			and a.INSURANCE_TYPE_NAME = #{param.insuranceTypeName}
			and a.POLICY_START = #{param.policyStart}
			and a.POLICY_END = #{param.policyEnd}
			and a.BUY_STANDARD = #{param.buyStandard}
		order by
			a.CREATE_TIME DESC
		limit 1;
	</select>
	<!-- 根据ID导出减员办理列表-->
	<select id="getRefundExportListBySelect" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.RefundExportListVo">
		select
			a.id as id,
			a.DEPT_NO as deptNo,
			a.DEPT_NAME as projectName,
			a.EMP_NAME as empName,
			a.EMP_IDCARD_NO as empIdcardNo,
			a.BUY_TYPE as buyType,
			a.POST as post,
			a.INSURANCE_PROVINCE_NAME as insuranceProvinceName,
			a.INSURANCE_CITY_NAME as insuranceCityName,
			a.INSURANCE_HANDLE_PROVINCE_NAME as insuranceHandleProvinceName,
			a.INSURANCE_HANDLE_CITY_NAME as insuranceHandleCityName,
			a.POLICY_START as policyStart,
			a.POLICY_END as policyEnd,
			a.INSURANCE_COMPANY_NAME as insuranceCompanyName,
			a.INSURANCE_TYPE_NAME as insuranceTypeName,
			a.BUY_STANDARD as buyStandard,
			a.MEDICAL_QUOTA as medicalQuota,
			a.DIE_DISABLE_QUOTA as dieDisableQuota,
			a.SETTLE_MONTH as settleMonth,
			a.SETTLE_TYPE as settleType,
			a.POLICY_NO as policyNo,
			refund.CREATE_NAME as createName,
			a.REDUCE_HANDLE_STATUS as reduceHandleStatus,
			refund.REMARK as remark,a.UNIT_NAME as unitName
		from
		    t_insurance_detail a,
			t_insurance_refund refund
		where
			a.REFUND_ID = refund.ID AND
		    a.id in
		<foreach collection="idList" index="index" item="item" open="(" separator="," close=")">
			#{item}
		</foreach>
		ORDER BY a.CREATE_TIME DESC
	</select>
	<!-- 根据查询参数导出减员办理列表-->
	<select id="getRefundExportList" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.RefundExportListVo">
		select
		    a.id as id,
			a.DEPT_NO as deptNo,
			a.DEPT_NAME as projectName,
			a.EMP_NAME as empName,
			a.EMP_IDCARD_NO as empIdcardNo,
			a.BUY_TYPE as buyType,
			a.POST as post,
			a.INSURANCE_PROVINCE_NAME as insuranceProvinceName,
			a.INSURANCE_CITY_NAME as insuranceCityName,
			a.INSURANCE_HANDLE_PROVINCE_NAME as insuranceHandleProvinceName,
			a.INSURANCE_HANDLE_CITY_NAME as insuranceHandleCityName,
			a.POLICY_START as policyStart,
			a.POLICY_END as policyEnd,
			a.INSURANCE_COMPANY_NAME as insuranceCompanyName,
			a.INSURANCE_TYPE_NAME as insuranceTypeName,
			a.BUY_STANDARD as buyStandard,
			a.MEDICAL_QUOTA as medicalQuota,
			a.DIE_DISABLE_QUOTA as dieDisableQuota,
			a.SETTLE_MONTH as settleMonth,
			a.SETTLE_TYPE as settleType,
			refund.CREATE_NAME as createName,
			a.POLICY_NO as policyNo,
			refund.REMARK as remark,a.UNIT_NAME as unitName
		from
			t_insurance_detail a,
			t_insurance_refund refund
		where
			a.REFUND_ID = refund.ID and
		    a.DELETE_FLAG = 0 and a.REDUCE_HANDLE_STATUS = #{param.reduceHandleStatus}
		<if test="param.empName != null and param.empName.trim() != ''">
			and a.EMP_NAME like concat('%',replace(replace(#{param.empName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.empIdcardNo != null and param.empIdcardNo.trim() != ''">
			and a.EMP_IDCARD_NO like concat('%',replace(replace(#{param.empIdcardNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.deptNo != null and param.deptNo.trim() != ''">
			and a.DEPT_NO = #{param.deptNo}
		</if>
		<if test="param.insuranceTypeName != null and param.insuranceTypeName.trim() != ''">
			and a.INSURANCE_TYPE_NAME like concat('%',replace(replace(#{param.insuranceTypeName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.insuranceCompanyName != null and param.insuranceCompanyName.trim() != ''">
			and a.INSURANCE_COMPANY_NAME = #{param.insuranceCompanyName}
		</if>
		<if test="param.buyStandardMin != null and param.buyStandardMin.trim() != ''">
			AND a.BUY_STANDARD   <![CDATA[ >= ]]>  #{param.buyStandardMin}
		</if>
		<if test="param.buyStandardMax != null and param.buyStandardMax.trim() != ''">
			AND a.BUY_STANDARD <![CDATA[ <= ]]> #{param.buyStandardMax}
		</if>
		<if test="param.updateBy != null and param.updateBy.trim() != ''">
			and  refund.UPDATE_BY = #{param.updateBy}
		</if>
		<if test="param.isAdress != null and param.isAdress.trim() != ''">
			and a.IS_ADRESS = #{param.isAdress}
		</if>
		<if test="param.authSql != null and param.authSql.trim() != ''">
			${param.authSql}
		</if>
		ORDER BY a.CREATE_TIME DESC
	</select>
	<!-- 减员办理-->
	<update id="updateInsuranceRefund">
		update
			t_insurance_detail a
		set
			UPDATE_BY = #{param.updateBy},
			REDUCE_HANDLE_STATUS = #{param.refundType},
			UPDATE_TIME = #{param.refundCreateTime}
		where
		      a.ID in
		<foreach collection="param.idList" index="index" item="item" open="(" separator="," close=")">
			#{item}
		</foreach>
	</update>
	<!--根据身份证查询商险信息-->
	<select id="getInsuranceListByIdCard" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.InsuranceListByIdCardVo">
		SELECT
		<include refid="Base_Column_List"/>
		FROM t_insurance_detail
		<where>
			1=1
			AND EMP_IDCARD_NO = #{idCard}
			and DEPT_NO = #{deptNo}
		</where>
		ORDER BY CREATE_TIME desc
	</select>
	<!--查询所有在保，未出险，未过期，未失效，未减员的保单信息-->
    <select id="selectListByEmpIdcardNo" resultType="com.yifu.cloud.plus.v1.yifu.insurances.entity.TInsuranceDetail">
		SELECT
			<include refid="Base_Column_List"/>
		FROM
		  t_insurance_detail
		<where>
		    EMP_IDCARD_NO = #{empIdcardNo}
			AND IS_EFFECT = 0
			AND IS_USE =0
			and IS_OVERDUE = 0
			and DELETE_FLAG = 0
			and BUY_HANDLE_STATUS =3
			and (REDUCE_HANDLE_STATUS is null or REDUCE_HANDLE_STATUS = 3)
		</where>
		ORDER BY CREATE_TIME desc
	</select>
	<!--商险订单列表查询-->
	<select id="getOrderInsuredListPage" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.InsuredOrderListVo">
		(
		select
			a.id as id,
			a.id as onlyId,
			a.BUY_TYPE as buyType,
			a.CREATE_BY as createBy,
			a.EMP_NAME as empName,
			a.EMP_IDCARD_NO as empIdcardNo,
			a.INSURANCE_COMPANY_NAME as insuranceCompanyName,
			a.INSURANCE_TYPE_NAME as insuranceTypeName,
			a.BUY_STANDARD as buyStandard,
			a.MEDICAL_QUOTA as medicalQuota,
			a.DIE_DISABLE_QUOTA as dieDisableQuota,
			a.CREATE_NAME as createName,
			a.CREATE_USER_DEPT_NAME as createUserDeptName,
			a.CREATE_TIME as createTime,
			a.BUY_HANDLE_STATUS as buyHandleStatus,
			a.REDUCE_HANDLE_STATUS as reduceHandleStatus
		from
			t_insurance_detail a
		where
			a.DELETE_FLAG = 0
		and
			a.ORDER_NO = #{param.orderNo}
		<if test="param.empName != null and param.empName.trim() != ''">
			and a.EMP_NAME like concat('%',replace(replace(#{param.empName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.empIdcardNo != null and param.empIdcardNo.trim() != ''">
			and a.EMP_IDCARD_NO like concat('%',replace(replace(#{param.empIdcardNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.startDate != null ">
			AND a.CREATE_TIME  <![CDATA[ >= ]]>  concat(#{param.startDate}, ' 00:00:00')
		</if>
		<if test="param.endDate != null ">
			AND a.CREATE_TIME  <![CDATA[ <= ]]> concat(#{param.endDate}, ' 23:59:59')
		</if>
		<if test="param.authSql != null and param.authSql.trim() != ''">
			${param.authSql}
		</if>
		<if test="param.deptNoList != null">
			and a.DEPT_NO in
			<foreach collection="param.deptNoList" index="index" item="item" open="(" separator="," close=")">
				#{item}
			</foreach>
		</if>)
		union all(
		select
			a.id as id,
			refund.id as onlyId,
			refund.BUY_TYPE as buyType,
			refund.CREATE_BY as createBy,
			a.EMP_NAME as empName,
			a.EMP_IDCARD_NO as empIdcardNo,
			a.INSURANCE_COMPANY_NAME as insuranceCompanyName,
			a.INSURANCE_TYPE_NAME as insuranceTypeName,
			a.BUY_STANDARD as buyStandard,
			a.MEDICAL_QUOTA as medicalQuota,
			a.DIE_DISABLE_QUOTA as dieDisableQuota,
			refund.CREATE_NAME as createName,
			refund.CREATE_USER_DEPT_NAME as createUserDeptName,
			refund.CREATE_TIME as createTime,
			a.BUY_HANDLE_STATUS as buyHandleStatus,
			refund.REDUCE_HANDLE_STATUS as reduceHandleStatus
			from
				t_insurance_detail a,
				t_insurance_refund refund
			where
				a.DELETE_FLAG = 0
			and
			    a.ID = refund.INS_DETAIL_ID
			and
				refund.ORDER_NO = #{param.orderNo}
			<if test="param.empName != null and param.empName.trim() != ''">
				and a.EMP_NAME like concat('%',replace(replace(#{param.empName},'_','\_'),'%','\%'),'%')
			</if>
			<if test="param.empIdcardNo != null and param.empIdcardNo.trim() != ''">
				and a.EMP_IDCARD_NO like concat('%',replace(replace(#{param.empIdcardNo},'_','\_'),'%','\%'),'%')
			</if>
			<if test="param.startDate != null">
				AND refund.CREATE_TIME  <![CDATA[ >= ]]>  concat(#{param.startDate}, ' 00:00:00')
			</if>
			<if test="param.endDate != null ">
				AND refund.CREATE_TIME  <![CDATA[ <= ]]> concat(#{param.endDate}, ' 23:59:59')
			</if>
			<if test="param.authSql != null and param.authSql.trim() != ''">
				${param.authSql}
			</if>
			<if test="param.deptNoList != null">
				and a.DEPT_NO in
				<foreach collection="param.deptNoList" index="index" item="item" open="(" separator="," close=")">
					#{item}
				</foreach>
			</if>
		)
		order by createTime desc
	</select>

	<!-- ekp费用划转-商险-->
	<update id="ekpUpdateInsuranceDept">
		update t_insurance_detail p
		set
		p.DEPT_ID = #{dept.id}
		,p.DEPT_NAME = #{dept.departName}
		,p.DEPT_NO = #{dept.departNo}
		,p.UNIT_ID = #{dept.customerId}
		,p.UNIT_NAME = #{dept.customerName}
		,p.UNIT_NO = #{dept.customerCode}
		where p.id in
		<foreach item="item" index="index" collection="idSet" open="(" separator="," close=")">
			#{item}
		</foreach>
	</update>

	<!--查询所有商险，如果idList 不为空，则不含被替换的本身。 用来改变档案状态-->
	<select id="selectByIdCardList" resultMap="BaseResultMap">
		SELECT
			<include refid="Base_Column_List"/>
		FROM
		t_insurance_detail
		where
			DELETE_FLAG = 0
		<if test="idCardList != null and idCardList.size > 0">
			AND EMP_IDCARD_NO in
			<foreach item="item" index="index" collection="idCardList" open="(" separator="," close=")">
				#{item}
			</foreach>
		</if>
			<if test="idList != null and idList.size > 0">
				AND id not in
				<foreach item="item" index="index" collection="idList" open="(" separator="," close=")">
					#{item}
				</foreach>
			</if>
	</select>

	<!--查询所有在途商险，判断是否买了商险：【1待投保 2投保中】 或 【3已投保 且 有效】-->
	<select id="selectInUseByIdCardList" resultMap="BaseResultMap">
		SELECT
		ID,
		EMP_NAME,
		EMP_IDCARD_NO
		FROM t_insurance_detail d
		where DELETE_FLAG = 0
		and (d.BUY_HANDLE_STATUS in (1,2,6) or (d.BUY_HANDLE_STATUS = 3 and d.IS_EFFECT = 0
			and d.POLICY_EFFECT <![CDATA[ <= ]]> DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 1 DAY),'%Y-%m-%d') and d.POLICY_END >= DATE_FORMAT(NOW(),'%Y-%m-%d')
		    ))

		<if test="idCardList != null and idCardList.size > 0">
			AND EMP_IDCARD_NO in
			<foreach item="item" index="index" collection="idCardList" open="(" separator="," close=")">
				#{item}
			</foreach>
		</if>
		<if test="idCardList == null">
			and 1=2
		</if>
	</select>

	<select id="selectByIds" resultMap="UpdateMap">
		SELECT
		a.ID,
		a.INVOICE_NO,
		a.POLICY_NO
		FROM
		t_insurance_detail a
		where
		a.ID in
		<foreach item="item" index="index" collection="ids" open="(" separator="," close=")">
			#{item}
		</foreach>
	</select>


	<!--	退保退费分页查询-->
	<select id="getInsuranceRefundMoneyPageList" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.RefundListVo">
		SELECT
			a.ID AS id,
			a.DEPT_NAME AS projectName,
			a.EMP_NAME AS empName,
			a.EMP_IDCARD_NO AS empIdcardNo,
			a.BUY_TYPE AS buyType,
			a.POLICY_START AS policyStart,
			a.POLICY_END AS policyEnd,
			a.INSURANCE_COMPANY_NAME AS insuranceCompanyName,
			a.INSURANCE_TYPE_NAME AS insuranceTypeName,
			a.BUY_STANDARD AS buyStandard,
			refund.REFUND_MONEY AS refundMoney,
			IF
				( a.BUY_TYPE = 4, `replace`.empName, a.EMP_NAME ) AS returnEmpName,
			IF
				( a.BUY_TYPE = 4, `replace`.empIdcardNo, a.EMP_IDCARD_NO ) AS returnEmpIdcardNo,
			IF
				( a.BUY_TYPE = 4, `replace`.projectName, a.DEPT_NAME ) AS returnProjectName,
			a.IS_ADRESS as isAdress
		FROM
			t_insurance_detail a
				LEFT JOIN t_insurance_refund refund ON a.REFUND_ID = refund.ID
				LEFT JOIN (
				SELECT
					`replace`.FROM_INSURANCE_DETAIL_ID AS fromDetailId,
					`replace`.TO_INSURANCE_DETAIL_ID AS toDetailId,
					`replace`.ORIGIN_INSURANCE_DETAIL_ID AS originDetailId,
					detail.DEPT_NAME AS projectName,
					detail.EMP_NAME AS empName,
					detail.EMP_IDCARD_NO AS empIdcardNo
				FROM
					t_insurance_replace `replace`
						LEFT JOIN t_insurance_detail detail ON `replace`.ORIGIN_INSURANCE_DETAIL_ID = detail.ID
			) `replace` ON a.ID = `replace`.toDetailId
				AND a.BUY_TYPE = 4
		WHERE
			a.DELETE_FLAG = 0
		  AND a.REDUCE_HANDLE_STATUS = 4
		  and refund.REFUND_MONEY IS NOT NULL
		<if test="param.empName != null and param.empName.trim() != ''">
			and a.EMP_NAME like concat('%',replace(replace(#{param.empName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.empIdcardNo != null and param.empIdcardNo.trim() != ''">
			and a.EMP_IDCARD_NO like concat('%',replace(replace(#{param.empIdcardNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.deptNo != null and param.deptNo.trim() != ''">
			and a.DEPT_NO = #{param.deptNo}
		</if>
		<if test="param.reduceHandleStatus != null">
			and a.REDUCE_HANDLE_STATUS = #{param.reduceHandleStatus}
		</if>
		<if test="param.insuranceTypeName != null and param.insuranceTypeName.trim() != ''">
			and a.INSURANCE_TYPE_NAME like concat('%',replace(replace(#{param.insuranceTypeName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.insuranceCompanyName != null and param.insuranceCompanyName.trim() != ''">
			and a.INSURANCE_COMPANY_NAME = #{param.insuranceCompanyName}
		</if>
		<if test="param.updateBy != null and param.updateBy.trim() != ''">
			and  refund.UPDATE_BY = #{param.updateBy}
		</if>
		<if test="param.createName != null and param.createName.trim() != ''">
			and refund.CREATE_NAME like concat('%',replace(replace(#{param.createName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.startDate != null and param.startDate.trim() != ''">
			AND refund.CREATE_TIME   <![CDATA[ >= ]]>  concat(#{param.startDate}, ' 00:00:00')
		</if>
		<if test="param.endDate != null and param.endDate.trim() != ''">
			AND refund.CREATE_TIME <![CDATA[ <= ]]> concat(#{param.endDate}, ' 23:59:59')
		</if>
		<if test="param.buyStandardMin != null and param.buyStandardMin.trim() != ''">
			AND a.BUY_STANDARD   <![CDATA[ >= ]]>  #{param.buyStandardMin}
		</if>
		<if test="param.buyStandardMax != null and param.buyStandardMax.trim() != ''">
			AND a.BUY_STANDARD <![CDATA[ <= ]]> #{param.buyStandardMax}
		</if>
		<if test="param.isAdress != null and param.isAdress.trim() != ''">
			and a.IS_ADRESS = #{param.isAdress}
		</if>
		<if test="param.authSql != null and param.authSql.trim() != ''">
			${param.authSql}
		</if>
		ORDER BY refund.CREATE_TIME DESC,a.ID ASC
	</select>

	<select id="getInsuranceRefundMoneyList" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.RefundListVo">
		SELECT
		a.ID AS id,
		a.DEPT_NAME AS projectName,
		a.EMP_NAME AS empName,
		a.EMP_IDCARD_NO AS empIdcardNo,
		a.BUY_TYPE AS buyType,
		a.POLICY_START AS policyStart,
		a.POLICY_END AS policyEnd,
		a.INSURANCE_COMPANY_NAME AS insuranceCompanyName,
		a.INSURANCE_TYPE_NAME AS insuranceTypeName,
		a.BUY_STANDARD AS buyStandard,
		refund.REFUND_MONEY AS refundMoney,
		IF
		( a.BUY_TYPE = 4, `replace`.empName, a.EMP_NAME ) AS returnEmpName,
		IF
		( a.BUY_TYPE = 4, `replace`.empIdcardNo, a.EMP_IDCARD_NO ) AS returnEmpIdcardNo,
		IF
		( a.BUY_TYPE = 4, `replace`.projectName, a.DEPT_NAME ) AS returnProjectName,
		a.IS_ADRESS as isAdress
		FROM
		t_insurance_detail a
		LEFT JOIN t_insurance_refund refund ON a.REFUND_ID = refund.ID
		LEFT JOIN (
		SELECT
		`replace`.FROM_INSURANCE_DETAIL_ID AS fromDetailId,
		`replace`.TO_INSURANCE_DETAIL_ID AS toDetailId,
		`replace`.ORIGIN_INSURANCE_DETAIL_ID AS originDetailId,
		detail.DEPT_NAME AS projectName,
		detail.EMP_NAME AS empName,
		detail.EMP_IDCARD_NO AS empIdcardNo
		FROM
		t_insurance_replace `replace`
		LEFT JOIN t_insurance_detail detail ON `replace`.ORIGIN_INSURANCE_DETAIL_ID = detail.ID
		) `replace` ON a.ID = `replace`.toDetailId
		AND a.BUY_TYPE = 4
		WHERE
		a.DELETE_FLAG = 0
		AND a.REDUCE_HANDLE_STATUS = 4
		and refund.REFUND_MONEY IS NOT NULL
		<if test="param.empName != null and param.empName.trim() != ''">
			and a.EMP_NAME like concat('%',replace(replace(#{param.empName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.empIdcardNo != null and param.empIdcardNo.trim() != ''">
			and a.EMP_IDCARD_NO like concat('%',replace(replace(#{param.empIdcardNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.deptNo != null and param.deptNo.trim() != ''">
			and a.DEPT_NO = #{param.deptNo}
		</if>
		<if test="param.reduceHandleStatus != null">
			and a.REDUCE_HANDLE_STATUS = #{param.reduceHandleStatus}
		</if>
		<if test="param.insuranceTypeName != null and param.insuranceTypeName.trim() != ''">
			and a.INSURANCE_TYPE_NAME like concat('%',replace(replace(#{param.insuranceTypeName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.insuranceCompanyName != null and param.insuranceCompanyName.trim() != ''">
			and a.INSURANCE_COMPANY_NAME = #{param.insuranceCompanyName}
		</if>
		<if test="param.updateBy != null and param.updateBy.trim() != ''">
			and  refund.UPDATE_BY = #{param.updateBy}
		</if>
		<if test="param.createName != null and param.createName.trim() != ''">
			and refund.CREATE_NAME like concat('%',replace(replace(#{param.createName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.startDate != null and param.startDate.trim() != ''">
			AND refund.CREATE_TIME   <![CDATA[ >= ]]>  concat(#{param.startDate}, ' 00:00:00')
		</if>
		<if test="param.endDate != null and param.endDate.trim() != ''">
			AND refund.CREATE_TIME <![CDATA[ <= ]]> concat(#{param.endDate}, ' 23:59:59')
		</if>
		<if test="param.buyStandardMin != null and param.buyStandardMin.trim() != ''">
			AND a.BUY_STANDARD   <![CDATA[ >= ]]>  #{param.buyStandardMin}
		</if>
		<if test="param.buyStandardMax != null and param.buyStandardMax.trim() != ''">
			AND a.BUY_STANDARD <![CDATA[ <= ]]> #{param.buyStandardMax}
		</if>
		<if test="param.isAdress != null and param.isAdress.trim() != ''">
			and a.IS_ADRESS = #{param.isAdress}
		</if>
		<if test="param.authSql != null and param.authSql.trim() != ''">
			${param.authSql}
		</if>
		ORDER BY refund.CREATE_TIME DESC,a.ID ASC
	</select>

	<sql id="selectParamsVo">
		t.ID id,
        t.EMP_ID empId,
        t.EMP_NO empNo,
        t.EMP_NAME empName,
        t.EMP_IDCARD_NO empIdcardNo,
        t.INSURANCE_COMPANY insuranceCompany,
        t.INSURANCE_TYPE insuranceType,
        t.POLICY_START policyStart,
        t.POLICY_END policyEnd,
        t.BUY_PAY buyPay,
        t.IS_AT_SALARY isAtSalary,
        t.REMARK,
        t.CREATE_USER createUser,
        t.CREATE_TIME createTime,
        t.STATUS,
        t.POLICY_NO policyNo,
        t.MEDICAL_MONEY medicalMoney,
        t.DEATH_DISABILITY_MONEY deathDisabilityMoney,
        t.SETTLEMENT_DEPART settlementDepart,
        t.SETTLEMENT_ORGAN settlementOrgan,
        t.REPLACE_DATE replaceDate,
        t.REPLACE_STATUS replaceStatus,
        t.BUSINESS_INSURANCE_ADDR businessInsuranceAddr,
        t.SETTLE_TYPE settleType,
        t.BUY_STANDARD buyStandard,
        t.BATCH_DATE batchDate,
        t.IS_NOTICE isNotice,
        t.SETTLE_MONTH settleMonth,
        t.INVOICE_NO invoiceNo,
        t.BUY_TYPE buyType,
        t.LAST_UPDATE_USER lastUpdateUser,
        t.LAST_UPDATE_TIME lastUpdateTime,
        t.OLD_ID oldId,
        t.SETTLEMENT_FORM_ID settlementFormId,
        t.IS_SUBMIT,
        t.CUSTOMER_NAME customerName,
        t.CUSTOMER_ID customerId,
        t.SETTLEMENT_ORGAN_NAME settlementOrganName,
        t.SETTLEMENT_ORGAN_NO settlementOrganNo,
        t.SETTLE_FORM_TYPE settleFormType,
        t.POLICY_EFFECT policyEffect,
        t.SETTLE_FORM_ID settleFormId,
        p.NAME insuranceTypeName,
        tic.COMPANY_NAME insuranceCompanyName,
        t.BUSINESS_INSURANCE_PROV_ID businessInsuranceProvId,
        t.BUSINESS_INSURANCE_ADDR_ID businessInsuranceAddrId,
        t.BUSINESS_INSURANCE_PROV businessInsuranceProv,t.VALID_FLAG  validFlag,
        t.FORECAST_BUY_PAY,
        t.SETTLE_BUY_PAY_TYPE
        ,case  when t.POLICY_END &lt; date(now()) then '1' else 0  end  'expireFlag',
        t.post,
        t.BUY_MONTH,
        t.REFUND_RECORD_ID,
        t.REFUND_MONEY,
        t.REFUND_CREATE_TIME,
        t.REFUND_CREATE_USER,
        t.USER_DEPT_NAME
	</sql>
	<sql id="expire_sql_param">
		select
			a.id,
			a.EMP_NAME,
			a.EMP_IDCARD_NO,
			a.DEPT_NAME,
			a.DEPT_NO,
			a.INSURANCE_COMPANY_NAME,
			a.INSURANCE_TYPE_NAME,
			a.POLICY_START,
			a.POLICY_END,
			a.BUY_STANDARD,
			a.ACTUAL_PREMIUM,
			a.CREATE_NAME,
			a.CREATE_TIME,
			a.BUY_HANDLE_STATUS,
			a.POLICY_NO,
			a.MEDICAL_QUOTA,
			a.DIE_DISABLE_QUOTA,
			a.SETTLE_TYPE,
			a.SETTLE_MONTH,
			a.INVOICE_NO,
			r2.REPLACE_TIME,
			a.POLICY_EFFECT,
			a.UNIT_NAME as "customerName",
			a.UNIT_NO,
			a.REMARK,
			a.INSURANCE_PROVINCE_NAME,
			a.INSURANCE_CITY_NAME,
			a.IS_EFFECT,
			a.POST,
			r.fromEmpName,
			r.toEmpName,
			r.REPLACE_TIME as "toReplaceTime",
			a.IS_OVERDUE,
			a.ESTIMATE_PREMIUM,
			rf.REFUND_MONEY,
			rf.CREATE_TIME as "refundCreateTime",
			rf.CREATE_NAME as "refundCreateUser",
			a.CREATE_USER_DEPT_NAME,
			a.EXPIRE_REMARK,
			a.EXPIRE_IGNORE_FLAG
	</sql>
	<sql id="expire_export_sql_param">
		select
			a.id,
			a.EMP_NAME,
			a.EMP_IDCARD_NO,
			a.DEPT_NAME,
			a.DEPT_NO,
			a.INSURANCE_COMPANY_NAME,
			a.INSURANCE_TYPE_NAME,
			a.POLICY_START,
			a.POLICY_END,
			a.BUY_STANDARD,
			a.ACTUAL_PREMIUM,
			a.CREATE_NAME,
			a.CREATE_TIME,
			"已投保" as "BUY_HANDLE_STATUS",
			a.POLICY_NO,
			a.MEDICAL_QUOTA,
			a.DIE_DISABLE_QUOTA,
			case when a.SETTLE_TYPE = 0 then "与薪资结算"
				 when a.SETTLE_TYPE = 1 then "单独结算"
				 else null  end  as  "SETTLE_TYPE" ,
			a.SETTLE_MONTH,
			a.INVOICE_NO,
			r2.REPLACE_TIME,
			a.POLICY_EFFECT,
			a.UNIT_NAME as "customerName",
			a.UNIT_NO,
			a.REMARK,
			a.INSURANCE_CITY_NAME,
			case when a.IS_EFFECT = 0 then "有效"
				 when a.IS_EFFECT = 1 then "无效"
				 else null  end  as  "IS_EFFECT" ,
			a.POST,
			r.fromEmpName,
			r.toEmpName,
			r.REPLACE_TIME as "toReplaceTime",
			case when a.IS_OVERDUE = 0 then "临期"
				 when a.IS_OVERDUE = 1 then "过期"
				 else null  end  as  "IS_OVERDUE" ,
			a.ESTIMATE_PREMIUM,
			rf.REFUND_MONEY,
			rf.CREATE_TIME as "refundCreateTime",
			rf.CREATE_NAME as "refundCreateUser",
			a.CREATE_USER_DEPT_NAME,
			a.EXPIRE_REMARK,
			case when a.EXPIRE_IGNORE_FLAG = 0 then "是"
				 when a.EXPIRE_IGNORE_FLAG = 1 then "否"
				 else null  end  as  "EXPIRE_IGNORE_FLAG"
	</sql>
	<sql id="expire_sql_where">
		<where>
			<if test="param != null">
				<if test="param.empName != null and param.empName.trim() != ''">
					AND a.EMP_NAME like CONCAT('%' , #{param.empName},'%')
				</if>
				<if test="param.empIdcardNo != null and param.empIdcardNo.trim() != ''">
					AND a.EMP_IDCARD_NO like CONCAT('%' ,#{param.empIdcardNo},'%')
				</if>
				<if test="param.insuranceCityName != null and param.insuranceCityName.trim() != ''">
					AND a.INSURANCE_CITY_NAME  like CONCAT(#{param.insuranceCityName},'%')
				</if>
				<if test="param.insuranceHandleCityName != null and param.insuranceHandleCityName.trim() != ''">
					AND a.INSURANCE_HANDLE_CITY_NAME  like CONCAT(#{param.insuranceHandleCityName},'%')
				</if>
				<if test="param.createName != null and param.createName.trim() != ''">
					AND a.CREATE_NAME = #{param.createName}
				</if>
				<if test="param.expireIgnoreFlag != null and param.expireIgnoreFlag.trim() != ''">
					AND a.EXPIRE_IGNORE_FLAG = #{param.expireIgnoreFlag}
				</if>
				<if test="param.isOverdue != null and param.isOverdue.trim() != ''">
					AND a.IS_OVERDUE = #{param.isOverdue}
				</if>
				<if test="param.alerter != null and param.alerter.trim() != ''">
					and a.ALERTER = #{param.alerter}
				</if>
				<if test="param.alertId != null and param.alertId.trim() != ''">
					and a.ALERT_ID = #{param.alertId}
				</if>
				<if test="param.idList != null and param.idList.size() > 0">
					and a.id in
					<foreach collection="param.idList" item="param" index="index" open="(" close=")"
							 separator=",">
						#{param}
					</foreach>
				</if>
				<if test="param.settleDomainIds != null and param.settleDomainIds.size() > 0">
					and a.DEPT_NO in
					<foreach collection="param.settleDomainIds" item="param" index="index" open="(" close=")"
							 separator=",">
						#{param}
					</foreach>
				</if>
			</if>

		</where>
	</sql>
	<select id="getInsuranceExpireWarnVoPageHistory" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.TBusinessInsuranceVo">
		<include refid="expire_sql_param"/>,a.INSURANCE_HANDLE_CITY_NAME
		from
		(select  * from
			(select * from t_insurance_detail a
			where
				<!--   info.AUDIT_STATUS='0'  and t.STATUS = 5  状态 0.待提交 1.待办理  2.已提交保险公司  3.已办理  4.办理失败 5：待结算（已输入保单号）-->
				<!--一个月内的，没有再续买商险的数据-->
				a.BUY_HANDLE_STATUS ='3'
				and a.EXPIRE_IGNORE_FLAG='1'
				and (
				date_add(curdate() - day(curdate()) + 1, interval 3 month) >
				a.POLICY_END
				)
				<if test="param != null">
					<if test="param.insuranceProvinces == null and param.insuranceCitys == null and domains == null">
						and a.CREATE_BY = #{param.createBy}
					</if>
					and (1 = 2
					<if test="param.insuranceProvinces != null">
						<if test="param.insuranceProvinces.size() > 0">
							or a.INSURANCE_HANDLE_PROVINCE in
							<foreach collection="param.insuranceProvinces" item="param" index="index" open="(" close=")"
									 separator=",">
								#{param}
							</foreach>

						</if>
					</if>
					<if test="param.insuranceCitys != null">
						<if test="param.insuranceCitys.size() > 0">
							or a.INSURANCE_HANDLE_CITY in
							<foreach collection="param.insuranceCitys" item="param" index="index" open="(" close=")"
									 separator=",">
								#{param}
							</foreach>
						</if>
					</if>
					<!--数据权限判断-->
					or a.CREATE_BY = #{param.createBy}
					<if test="domains != null">
						<if test="domains.size() > 0">
							or a.DEPT_ID in
							<foreach collection="domains" item="param" index="index" open="(" close=")" separator=",">
								#{param}
							</foreach>
						</if>
					</if>
					)
				</if>
			) a
		<include refid="expire_sql_where"/>
		)   a
		LEFT JOIN view_insurance_replace r on a.id=r.TO_INSURANCE_DETAIL_ID
		LEFT JOIN view_insurance_replace r2 on a.id=r2.FROM_INSURANCE_DETAIL_ID
		LEFT JOIN t_insurance_refund rf on rf.INS_DETAIL_ID=a.id
		<include refid="expire_sql_where"/>
		order by a.CREATE_TIME desc
	</select>

	<select id="getInsuranceExpireWarnVoPage" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.TBusinessInsuranceVo">
		<include refid="expire_sql_param"/>
		,a.INSURANCE_HANDLE_CITY_NAME
		from
		 t_insurance_alert a
		LEFT JOIN view_insurance_replace r on a.id=r.TO_INSURANCE_DETAIL_ID
		LEFT JOIN view_insurance_replace r2 on a.id=r2.FROM_INSURANCE_DETAIL_ID
		LEFT JOIN t_insurance_refund rf on rf.INS_DETAIL_ID=a.id
		<include refid="expire_sql_where"/>
		order by a.POLICY_END asc
	</select>

	<select id="pageDiyHistory" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.TBusinessInsuranceVo">
		<include refid="expire_sql_param"/>,a.INSURANCE_HANDLE_CITY_NAME
		from
		(select  * from (select * from t_insurance_detail a
		where
		<!--   info.AUDIT_STATUS='0'  and t.STATUS = 5  状态 0.待提交 1.待办理  2.已提交保险公司  3.已办理  4.办理失败 5：待结算（已输入保单号）-->
		<!--一个月内的，没有再续买商险的数据-->
		a.BUY_HANDLE_STATUS ='3'
		and a.EXPIRE_IGNORE_FLAG='1'
		and (
		date_add(curdate() - day(curdate()) + 1, interval 3 month) >
		a.POLICY_END
		)
		<if test="param != null">
			<if test="param.insuranceProvinces == null and param.insuranceCitys == null and domains == null">
				and a.CREATE_BY = #{param.createBy}
			</if>
			and (1 = 2
			<if test="param.insuranceProvinces != null">
				<if test="param.insuranceProvinces.size() > 0">
					or a.INSURANCE_HANDLE_PROVINCE in
					<foreach collection="param.insuranceProvinces" item="param" index="index" open="(" close=")"
							 separator=",">
						#{param}
					</foreach>

				</if>
			</if>
			<if test="param.insuranceCitys != null">
				<if test="param.insuranceCitys.size() > 0">
					or a.INSURANCE_HANDLE_CITY in
					<foreach collection="param.insuranceCitys" item="param" index="index" open="(" close=")"
							 separator=",">
						#{param}
					</foreach>
				</if>
			</if>
			<!--数据权限判断-->
			or a.CREATE_BY = #{param.createBy}
			<if test="domains != null">
				<if test="domains.size() > 0">
					or a.DEPT_ID in
					<foreach collection="domains" item="param" index="index" open="(" close=")" separator=",">
						#{param}
					</foreach>
				</if>
			</if>
			)
		</if>
		)  a
		<include refid="expire_sql_where"/>
		order by a.CREATE_TIME desc
		<if test="param != null and param.limitStart != null">
			limit #{param.limitStart},#{param.limitEnd}
		</if>
		) a
		LEFT JOIN view_insurance_replace r on a.id=r.TO_INSURANCE_DETAIL_ID
		LEFT JOIN view_insurance_replace r2 on a.id=r2.FROM_INSURANCE_DETAIL_ID
		LEFT JOIN t_insurance_refund rf on rf.INS_DETAIL_ID=a.id
	</select>

	<select id="pageDiy" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.TBusinessInsuranceVo">
		<include refid="expire_sql_param"/>
		,a.INSURANCE_HANDLE_CITY_NAME,a.ALERTER
		from
		t_insurance_alert a
		LEFT JOIN view_insurance_replace r on a.id=r.TO_INSURANCE_DETAIL_ID
		LEFT JOIN view_insurance_replace r2 on a.id=r2.FROM_INSURANCE_DETAIL_ID
		LEFT JOIN t_insurance_refund rf on rf.INS_DETAIL_ID=a.id
		<include refid="expire_sql_where"/>
		group by a.id
		order by a.POLICY_END asc
		<if test="param != null and param.limitStart != null">
			limit #{param.limitStart},#{param.limitEnd}
		</if>
	</select>

	<select id="noPageDiy" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.InsuranceWarnExportVo">
		<include refid="expire_export_sql_param"/>
		,a.INSURANCE_HANDLE_CITY_NAME
		from
		t_insurance_alert a
		LEFT JOIN view_insurance_replace r on a.id=r.TO_INSURANCE_DETAIL_ID
		LEFT JOIN view_insurance_replace r2 on a.id=r2.FROM_INSURANCE_DETAIL_ID
		LEFT JOIN t_insurance_refund rf on rf.INS_DETAIL_ID=a.id
		<include refid="expire_sql_where"/>
		group by a.id
		order by a.POLICY_END asc
		<if test="param != null and param.limitStart != null">
			limit #{param.limitStart},#{param.limitEnd}
		</if>
	</select>
	<select id="noPageCountDiy" resultType="java.lang.Integer">
		select
		count(1)
		from
		t_insurance_alert a
		<include refid="expire_sql_where"/>
	</select>

	<update id="batchexpireIgnore">
		<if test="idList != null and idList.size() > 0">
			update t_insurance_detail  a
			set a.EXPIRE_IGNORE_FLAG = '0',a.EXPIRE_REMARK=#{remark}
			where
			a.id in
			<foreach collection="idList" item="param" index="index" open="(" close=")" separator=",">
				#{param}
			</foreach>
		</if>
	</update>

	<update id="batchexpireAlertIgnore">
		<if test="idList != null and idList.size() > 0">
			update t_insurance_alert  a
			set a.EXPIRE_IGNORE_FLAG = '0',a.EXPIRE_REMARK=#{remark}
			where
			a.id in
			<foreach collection="idList" item="param" index="index" open="(" close=")" separator=",">
				#{param}
			</foreach>
		</if>
	</update>

	<update id="updateExpireAlert">
		update t_insurance_alert
		<trim prefix="set" suffixOverrides=",">
			<if test="expireFlag != null">EXPIRE_IGNORE_FLAG=#{expireFlag},</if>
			<if test="remark != null">EXPIRE_REMARK=#{remark},</if>
		</trim>
		where id = #{id}
	</update>

	<select id="selectPushList" resultType="com.yifu.cloud.plus.v1.yifu.insurances.entity.TInsuranceDetail">
		SELECT
			*
		FROM
			t_insurance_detail
		WHERE ERROR_STATUS = '1'
	</select>

	<!--商险自动化-被替换人的分页接口-->
	<select id="replacePageToAutoSelect" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.InsuredListVo">
		select
		a.id as id,
		a.EMP_NAME as empName,
		a.EMP_IDCARD_NO as empIdcardNo,
		a.DEPT_NO as deptNo,
		a.DEPT_NAME as projectName,
		a.POLICY_START as policyStart,
		a.POLICY_END as policyEnd,
		a.INSURANCE_COMPANY_NAME as insuranceCompanyName,
		a.INSURANCE_TYPE_NAME as insuranceTypeName,
		a.INSURANCE_TYPE_ID as insuranceTypeId,
		a.BUY_STANDARD as buyStandard,
		a.INSURANCE_PROVINCE as insuranceProvince,
		a.INSURANCE_PROVINCE_NAME as insuranceProvinceName,
		a.INSURANCE_CITY as insuranceCity,
		a.INSURANCE_CITY_NAME as insuranceCityName,
		a.REPLACE_TAG as replaceTag,
		a.REPLACE_REMARK as replaceRemark
		from t_insurance_detail a
		where a.DELETE_FLAG = 0 and a.BUY_HANDLE_STATUS = 3 and IS_USE = 0 and IS_EFFECT = 0 and IS_OVERDUE = 0
		<if test="param.empNameOrCard != null and param.empNameOrCard.trim() != ''">
			and (
			    a.EMP_NAME like concat('%',replace(replace(#{param.empNameOrCard},'_','\_'),'%','\%'),'%')
				or a.EMP_IDCARD_NO like concat('%',replace(replace(#{param.empNameOrCard},'_','\_'),'%','\%'),'%')
			)
		</if>
		<if test="param.empName != null and param.empName.trim() != ''">
			and a.EMP_NAME like concat('%',replace(replace(#{param.empName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.empIdcardNo != null and param.empIdcardNo.trim() != ''">
			and a.EMP_IDCARD_NO like concat('%',replace(replace(#{param.empIdcardNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.insuranceCompanyName != null and param.insuranceCompanyName.trim() != ''">
			and a.INSURANCE_COMPANY_NAME like concat('%',replace(replace(#{param.insuranceCompanyName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.insuranceTypeName != null and param.insuranceTypeName.trim() != ''">
			and a.INSURANCE_TYPE_NAME like concat('%',replace(replace(#{param.insuranceTypeName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.buyStandardMin != null and param.buyStandardMin.trim() != ''">
			AND a.BUY_STANDARD+0 <![CDATA[ >= ]]> #{param.buyStandardMin}+0
		</if>
		<if test="param.buyStandardMax != null and param.buyStandardMax.trim() != ''">
			AND a.BUY_STANDARD+0 <![CDATA[ <= ]]> #{param.buyStandardMax}+0
		</if>
		<if test="param.replaceTag != null and param.replaceTag.trim() != ''">
			and a.REPLACE_TAG = #{param.replaceTag}
		</if>
		<if test="param.replaceRemark != null and param.replaceRemark.trim() != ''">
			and a.REPLACE_REMARK like concat('%',replace(replace(#{param.replaceRemark},'_','\\_'),'%','\\%'),'%')
		</if>
		<if test="param.authSql != null and param.authSql.trim() != ''">
			${param.authSql}
		</if>
		ORDER BY a.CREATE_TIME DESC ,a.ID ASC
	</select>

	<!--商险自动化-针对批增的查询保单号的分页接口-->
	<select id="policyPageToAutoSelect" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.InsuredListVo">
		select
		a.id as id,
		a.POLICY_NO as policyNo,
		a.POLICY_START as policyStart,
		a.POLICY_END as policyEnd,
		a.INSURANCE_COMPANY_NAME as insuranceCompanyName,
		a.INSURANCE_TYPE_NAME as insuranceTypeName,
		a.INSURANCE_TYPE_ID as insuranceTypeId,
		a.BUY_STANDARD as buyStandard,
		a.INSURANCE_PROVINCE as insuranceProvince,
		a.INSURANCE_PROVINCE_NAME as insuranceProvinceName,
		a.INSURANCE_CITY as insuranceCity,
		a.INSURANCE_CITY_NAME as insuranceCityName
		from t_insurance_detail a
		where a.DELETE_FLAG = 0 and a.BUY_HANDLE_STATUS = 3 and a.POLICY_NO is not null and a.POLICY_NO != ""
		<if test="param.policyNo != null and param.policyNo.trim() != ''">
			and a.POLICY_NO like concat('%',replace(replace(#{param.policyNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.insuranceCompanyName != null and param.insuranceCompanyName.trim() != ''">
			and a.INSURANCE_COMPANY_NAME like concat('%',replace(replace(#{param.insuranceCompanyName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.insuranceTypeName != null and param.insuranceTypeName.trim() != ''">
			and a.INSURANCE_TYPE_NAME like concat('%',replace(replace(#{param.insuranceTypeName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.buyStandardMin != null and param.buyStandardMin.trim() != ''">
			AND a.BUY_STANDARD+0 <![CDATA[ >= ]]> #{param.buyStandardMin}+0
		</if>
		<if test="param.buyStandardMax != null and param.buyStandardMax.trim() != ''">
			AND a.BUY_STANDARD+0 <![CDATA[ <= ]]> #{param.buyStandardMax}+0
		</if>
		<if test="param.policyEnd != null and param.policyEnd.trim() != ''">
			AND DATE(a.POLICY_END) <![CDATA[ >= ]]> #{param.policyEnd}
		</if>
		<if test="param.authSql != null and param.authSql.trim() != ''">
			${param.authSql}
		</if>
		group by a.POLICY_NO,a.INSURANCE_COMPANY_NAME,a.INSURANCE_TYPE_NAME,a.POLICY_START,a.POLICY_END,a.BUY_STANDARD
		ORDER BY a.CREATE_TIME DESC ,a.ID ASC
	</select>

	<select id="getInsuranceDetailList" resultType="java.lang.String">
		SELECT
			a.id
		FROM t_insurance_detail a
		where a.EMP_IDCARD_NO = #{empIdCard}
		  and a.DEPT_NO = #{deptNo}
		  and a.BUY_HANDLE_STATUS = 3 and a.IS_EFFECT = 0 and a.IS_OVERDUE = 0
	</select>

	<select id="getInsuranceDetailListByClose" resultType="java.lang.String">
		select
			a.id
		from
			t_insurance_detail a
		where a.DELETE_FLAG = 0 and (a.REDUCE_HANDLE_STATUS is null or a.REDUCE_HANDLE_STATUS != 4)
			and a.EMP_IDCARD_NO = #{empIdCard}
		 	 and a.DEPT_NO = #{deptNo}

	</select>

	<!-- 导出商险明细项目变更日志 -->
	<select id="getInsuranceChangeLogExportList" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.InsuranceChangeLogExportVo">
		select
			a.EMP_NAME as empName,
			a.EMP_IDCARD_NO as empIdcardNo,
			a.INSURANCE_COMPANY_NAME as insuranceCompanyName,
			a.INSURANCE_TYPE_NAME as insuranceTypeName,
			a.POLICY_START as policyStart,
			a.POLICY_END as policyEnd,
			a.BUY_STANDARD as buyStandard,
			b.CREATE_NAME as createName,
			b.CREATE_TIME as createTime,
			CONCAT(
			'项目编码：旧值为：', IFNULL(JSON_UNQUOTE(JSON_EXTRACT(b.OLD_INFO, '$.deptNo')), ''), '，新值为：', IFNULL(JSON_UNQUOTE(JSON_EXTRACT(b.NEW_INFO, '$.deptNo')), ''),
			'\n项目名称：旧值为：', IFNULL(JSON_UNQUOTE(JSON_EXTRACT(b.OLD_INFO, '$.deptName')), ''), '，新值为：', IFNULL(JSON_UNQUOTE(JSON_EXTRACT(b.NEW_INFO, '$.deptName')), '')
			) as operateContent,
			b.REASON as reason
		from t_insurance_detail a
		left join t_insurances_change_log b on a.ID = b.PRE_ID
		where a.DELETE_FLAG = 0 and a.BUY_HANDLE_STATUS = 3
		<if test="param.isEffect != null">
			and a.IS_EFFECT = #{param.isEffect}
		</if>
		<if test="param.unitName != null and param.unitName.trim() != ''">
			and a.UNIT_NAME like concat(replace(replace(#{param.unitName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.unitNo != null and param.unitNo.trim() != ''">
			and a.UNIT_NO like concat(replace(replace(#{param.unitNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.createName != null and param.createName.trim() != ''">
			and a.CREATE_NAME like concat('%',replace(replace(#{param.createName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.buyType != null">
			and a.BUY_TYPE = #{param.buyType}
		</if>
		<if test="param.reduceHandleStatus != null">
			and a.REDUCE_HANDLE_STATUS = #{param.reduceHandleStatus}
		</if>
		<if test="param.empName != null and param.empName.trim() != ''">
			and a.EMP_NAME like concat('%',replace(replace(#{param.empName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.empIdcardNo != null and param.empIdcardNo.trim() != ''">
			and a.EMP_IDCARD_NO like concat('%',replace(replace(#{param.empIdcardNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.deptNo != null and param.deptNo.trim() != ''">
			and a.DEPT_NO = #{param.deptNo}
		</if>
		<if test="param.insuranceCompanyName != null and param.insuranceCompanyName.trim() != ''">
			and a.INSURANCE_COMPANY_NAME = #{param.insuranceCompanyName}
		</if>
		<if test="param.policyNo != null and param.policyNo.trim() != ''">
			and a.POLICY_NO like concat('%',replace(replace(#{param.policyNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.policyJingQueStart != null and param.policyJingQueStart.trim() != ''">
			AND DATE_FORMAT(a.POLICY_START,"%Y-%m-%d") = #{param.policyJingQueStart}
		</if>
		<if test="param.policyJingQueEnd != null and param.policyJingQueEnd.trim() != ''">
			AND DATE_FORMAT(a.POLICY_END,"%Y-%m-%d") = #{param.policyJingQueEnd}
		</if>
		<if test="param.policyStart != null and param.policyStart.trim() != '' and param.policyEnd != null and param.policyEnd.trim() != ''">
			and (
				(a.POLICY_START <![CDATA[ >= ]]> #{param.policyStart} and a.POLICY_START <![CDATA[ <= ]]> #{param.policyEnd})
				or (a.POLICY_END <![CDATA[ >= ]]> #{param.policyStart} and a.POLICY_END <![CDATA[ <= ]]> #{param.policyEnd})
				or (a.POLICY_START <![CDATA[ <= ]]> #{param.policyStart} and a.POLICY_END <![CDATA[ >= ]]> #{param.policyEnd})
			)
		</if>
		<if test="param.createStartTime != null and param.createStartTime.trim() != ''">
			and a.CREATE_TIME <![CDATA[ >= ]]> #{param.createStartTime}
		</if>
		<if test="param.createEndTime != null and param.createEndTime.trim() != ''">
			and a.CREATE_TIME <![CDATA[ <= ]]> #{param.createEndTime}
		</if>
		<if test="param.buyHandleStatus != null">
			and a.BUY_HANDLE_STATUS = #{param.buyHandleStatus}
		</if>
		<if test="param.isUse != null">
			and a.IS_USE = #{param.isUse}
		</if>
		<if test="param.isOverdue != null">
			and a.IS_OVERDUE = #{param.isOverdue}
		</if>
		<if test="param.invoiceNo != null and param.invoiceNo.trim() != ''">
			and a.INVOICE_NO like concat('%',replace(replace(#{param.invoiceNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.insuranceCompanyId != null and param.insuranceCompanyId.trim() != ''">
			and a.INSURANCE_COMPANY_ID = #{param.insuranceCompanyId}
		</if>
		<if test="param.deptNoList != null and param.deptNoList.size > 0">
			and a.DEPT_NO in
			<foreach collection="param.deptNoList" item="item" open="(" separator="," close=")">
				#{item}
			</foreach>
		</if>
		<if test="param.isAdress != null and param.isAdress.trim() != ''">
			and a.IS_ADRESS = #{param.isAdress}
		</if>
		<if test="param.insuranceTypeName != null and param.insuranceTypeName.trim() != ''">
			and a.INSURANCE_TYPE_NAME like concat('%',replace(replace(#{param.insuranceTypeName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.buyStandardMin != null and param.buyStandardMin.trim() != ''">
			AND a.BUY_STANDARD+0 <![CDATA[ >= ]]> #{param.buyStandardMin}+0
		</if>
		<if test="param.buyStandardMax != null and param.buyStandardMax.trim() != ''">
			AND a.BUY_STANDARD+0 <![CDATA[ <= ]]> #{param.buyStandardMax}+0
		</if>
		<if test="param.authSql != null and param.authSql.trim() != ''">
			${param.authSql}
		</if>
		and b.PRE_ID is not null
		order by b.CREATE_TIME desc
		<if test="param.limitStart != null and param.limitEnd != null">
			limit #{param.limitStart}, #{param.limitEnd}
		</if>
	</select>

	<!-- 导出商险明细项目变更日志统计 -->
	<select id="getInsuranceChangeLogExportCount" resultType="long">
		select
			count(*)
		from t_insurance_detail a
		left join t_insurances_change_log b on a.ID = b.PRE_ID
		where a.DELETE_FLAG = 0 and a.BUY_HANDLE_STATUS = 3
		<if test="param.isEffect != null">
			and a.IS_EFFECT = #{param.isEffect}
		</if>
		<if test="param.unitName != null and param.unitName.trim() != ''">
			and a.UNIT_NAME like concat(replace(replace(#{param.unitName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.unitNo != null and param.unitNo.trim() != ''">
			and a.UNIT_NO like concat(replace(replace(#{param.unitNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.createName != null and param.createName.trim() != ''">
			and a.CREATE_NAME like concat('%',replace(replace(#{param.createName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.buyType != null">
			and a.BUY_TYPE = #{param.buyType}
		</if>
		<if test="param.reduceHandleStatus != null">
			and a.REDUCE_HANDLE_STATUS = #{param.reduceHandleStatus}
		</if>
		<if test="param.empName != null and param.empName.trim() != ''">
			and a.EMP_NAME like concat('%',replace(replace(#{param.empName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.empIdcardNo != null and param.empIdcardNo.trim() != ''">
			and a.EMP_IDCARD_NO like concat('%',replace(replace(#{param.empIdcardNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.deptNo != null and param.deptNo.trim() != ''">
			and a.DEPT_NO = #{param.deptNo}
		</if>
		<if test="param.insuranceCompanyName != null and param.insuranceCompanyName.trim() != ''">
			and a.INSURANCE_COMPANY_NAME = #{param.insuranceCompanyName}
		</if>
		<if test="param.policyNo != null and param.policyNo.trim() != ''">
			and a.POLICY_NO like concat('%',replace(replace(#{param.policyNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.policyJingQueStart != null and param.policyJingQueStart.trim() != ''">
			AND DATE_FORMAT(a.POLICY_START,"%Y-%m-%d") = #{param.policyJingQueStart}
		</if>
		<if test="param.policyJingQueEnd != null and param.policyJingQueEnd.trim() != ''">
			AND DATE_FORMAT(a.POLICY_END,"%Y-%m-%d") = #{param.policyJingQueEnd}
		</if>
		<if test="param.policyStart != null and param.policyStart.trim() != '' and param.policyEnd != null and param.policyEnd.trim() != ''">
			and (
				(a.POLICY_START <![CDATA[ >= ]]> #{param.policyStart} and a.POLICY_START <![CDATA[ <= ]]> #{param.policyEnd})
				or (a.POLICY_END <![CDATA[ >= ]]> #{param.policyStart} and a.POLICY_END <![CDATA[ <= ]]> #{param.policyEnd})
				or (a.POLICY_START <![CDATA[ <= ]]> #{param.policyStart} and a.POLICY_END <![CDATA[ >= ]]> #{param.policyEnd})
			)
		</if>
		<if test="param.createStartTime != null and param.createStartTime.trim() != ''">
			and a.CREATE_TIME <![CDATA[ >= ]]> #{param.createStartTime}
		</if>
		<if test="param.createEndTime != null and param.createEndTime.trim() != ''">
			and a.CREATE_TIME <![CDATA[ <= ]]> #{param.createEndTime}
		</if>
		<if test="param.buyHandleStatus != null">
			and a.BUY_HANDLE_STATUS = #{param.buyHandleStatus}
		</if>
		<if test="param.isUse != null">
			and a.IS_USE = #{param.isUse}
		</if>
		<if test="param.isOverdue != null">
			and a.IS_OVERDUE = #{param.isOverdue}
		</if>
		<if test="param.invoiceNo != null and param.invoiceNo.trim() != ''">
			and a.INVOICE_NO like concat('%',replace(replace(#{param.invoiceNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.insuranceCompanyId != null and param.insuranceCompanyId.trim() != ''">
			and a.INSURANCE_COMPANY_ID = #{param.insuranceCompanyId}
		</if>
		<if test="param.deptNoList != null and param.deptNoList.size > 0">
			and a.DEPT_NO in
			<foreach collection="param.deptNoList" item="item" open="(" separator="," close=")">
				#{item}
			</foreach>
		</if>
		<if test="param.isAdress != null and param.isAdress.trim() != ''">
			and a.IS_ADRESS = #{param.isAdress}
		</if>
		<if test="param.insuranceTypeName != null and param.insuranceTypeName.trim() != ''">
			and a.INSURANCE_TYPE_NAME like concat('%',replace(replace(#{param.insuranceTypeName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.buyStandardMin != null and param.buyStandardMin.trim() != ''">
			AND a.BUY_STANDARD+0 <![CDATA[ >= ]]> #{param.buyStandardMin}+0
		</if>
		<if test="param.buyStandardMax != null and param.buyStandardMax.trim() != ''">
			AND a.BUY_STANDARD+0 <![CDATA[ <= ]]> #{param.buyStandardMax}+0
		</if>
		<if test="param.authSql != null and param.authSql.trim() != ''">
			${param.authSql}
		</if>
		and b.PRE_ID is not null
	</select>
	<select id="getInsuranceListPagePaymentMoney" resultType="java.lang.Double">
		select
		ROUND(SUM(ifnull(a.ESTIMATE_PREMIUM,0.00)),2) as paymentMoney
		from t_insurance_detail a
		where a.DELETE_FLAG = 0
		<if test="param.isEffect != null">
			and a.IS_EFFECT = #{param.isEffect}
		</if>
		<if test="param.id != null">
			and a.id = #{param.id}
		</if>
		<if test="param.unitName != null and param.unitName.trim() != ''">
			and a.UNIT_NAME like concat(replace(replace(#{param.unitName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.unitNo != null and param.unitNo.trim() != ''">
			and a.UNIT_NO like concat(replace(replace(#{param.unitNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.deptNo != null and param.deptNo.trim() != ''">
			and a.DEPT_NO = #{param.deptNo}
		</if>
		<if test="param.orderNo != null and param.orderNo.trim() != ''">
			and a.ORDER_NO = #{param.orderNo}
		</if>
		<if test="param.buyHandleStatus != null">
			and a.BUY_HANDLE_STATUS = #{param.buyHandleStatus}
		</if>
		<if test="param.empName != null and param.empName.trim() != ''">
			and a.EMP_NAME like concat('%',replace(replace(#{param.empName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.empIdcardNo != null and param.empIdcardNo.trim() != ''">
			and a.EMP_IDCARD_NO like concat('%',replace(replace(#{param.empIdcardNo},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.insuranceCompanyName != null and param.insuranceCompanyName.trim() != ''">
			and a.INSURANCE_COMPANY_NAME = #{param.insuranceCompanyName}
		</if>
		<if test="param.insuranceTypeName != null and param.insuranceTypeName.trim() != ''">
			and a.INSURANCE_TYPE_NAME like concat('%',replace(replace(#{param.insuranceTypeName},'_','\_'),'%','\%'),'%')
		</if>
		<if test="param.buyHandleStatusList != null and param.buyHandleStatusList.size > 0">
			and a.BUY_HANDLE_STATUS in
			<foreach collection="param.buyHandleStatusList" index="index" item="item" open="(" separator="," close=")">
				#{item}
			</foreach>
		</if>
		<if test="param.buyType != null">
			and a.BUY_TYPE = #{param.buyType}
		</if>
		<if test="param.createName != null and param.createName.trim() != ''">
			and a.CREATE_NAME like concat('%',#{param.createName},'%')
		</if>
		<if test="param.startDate != null and param.startDate.trim() != '' and param.endDate != null and param.endDate.trim() != ''">
			and a.CREATE_TIME <![CDATA[ >= ]]>  concat(#{param.startDate}, ' 00:00:00') and a.CREATE_TIME <![CDATA[ <= ]]> concat(#{param.endDate}, ' 23:59:59')
		</if>
		<if test="param.reduceHandleStatus != null">
			and a.REDUCE_HANDLE_STATUS = #{param.reduceHandleStatus}
		</if>
		<if test="param.isAdress != null and param.isAdress.trim() != ''">
			and a.IS_ADRESS = #{param.isAdress}
		</if>
		<if test="param.paymentStatus != null and param.paymentStatus.trim() != ''">
			and a.PAYMENT_STATUS = #{param.paymentStatus}
		</if>
		<if test="param.paymentTimeStart != null and param.paymentTimeStart.trim() != '' ">
			and a.PAYMENT_TIME <![CDATA[ >= ]]>  #{param.paymentTimeStart}
		</if>
		<if test="param.paymentTimeEnd != null and param.paymentTimeEnd.trim() != ''">
			and a.PAYMENT_TIME <![CDATA[ <= ]]> #{param.paymentTimeEnd}
		</if>
		<if test="param.isJfcd != null and param.isJfcd.trim() != ''">
			and a.IS_JFCD = #{param.isJfcd}
		</if>
		<if test="param.estimatePremiumStart != null">
			and a.ESTIMATE_PREMIUM <![CDATA[ >= ]]>  #{param.estimatePremiumStart}
		</if>
		<if test="param.estimatePremiumEnd != null">
			and a.ESTIMATE_PREMIUM <![CDATA[ <= ]]> #{param.estimatePremiumEnd}
		</if>
		<if test="param.authSql != null and param.authSql.trim() != ''">
			${param.authSql}
		</if>
	</select>

	<select id="selectDeptNoListByIdCard" resultType="java.lang.String">
		select
			DISTINCT a.DEPT_NO
		from
			t_insurance_detail a
		where a.DEPT_NO is not null and a.DELETE_FLAG = 0 and a.BUY_HANDLE_STATUS = 5 and a.REDUCE_HANDLE_STATUS = 4
		  and a.EMP_IDCARD_NO in
		<foreach collection="idCardList" item="item" open="(" separator="," close=")">
			#{item}
		</foreach>
	</select>
</mapper>
