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

  <resultMap id="tEmployeeInsurancePreMap" type="com.yifu.cloud.plus.v1.yifu.insurances.entity.TEmployeeInsurancePre">
    <id property="id" column="id"/>
    <result property="employeeName" column="employee_name"/>
    <result property="empIdcard" column="emp_idcard"/>
    <result property="empPhone" column="emp_phone"/>
    <result property="joinLeaveDate" column="join_leave_date"/>
    <result property="processStatus" column="process_status"/>
    <result property="customerUsername" column="customer_username"/>
    <result property="deptName" column="dept_name"/>
    <result property="deptNo" column="dept_no"/>
    <result property="deptId" column="dept_id"/>
    <result property="expectedCollectionTime" column="expected_collection_time"/>
    <result property="customerUserLoginname" column="customer_user_loginname"/>
    <result property="insuranceCompanyName" column="insurance_company_name"/>
    <result property="insuranceTypeName" column="insurance_type_name"/>
    <result property="insuranceTypeId" column="insurance_type_id"/>
    <result property="buyStandard" column="buy_standard"/>
    <result property="buyType" column="buy_type"/>
    <result property="policyStart" column="policy_start"/>
    <result property="policyEnd" column="policy_end"/>
    <result property="policyEffect" column="policy_effect"/>
    <result property="policyNo" column="policy_no"/>
    <result property="isAddress" column="is_address"/>
    <result property="position" column="position"/>
    <result property="dataSource" column="data_source"/>
    <result property="fileProvince" column="file_province"/>
    <result property="fileCity" column="file_city"/>
    <result property="provinceCode" column="province_code"/>
    <result property="cityCode" column="city_code"/>
    <result property="registerId" column="register_id"/>
    <result property="replaceEmployeeName" column="replace_employee_name"/>
    <result property="replaceEmpIdcard" column="replace_emp_idcard"/>
    <result property="replaceDeptNo" column="replace_dept_no"/>
    <result property="configName" column="config_name"/>
    <result property="configId" column="config_id"/>
    <result property="insurancesId" column="insurances_id"/>
    <result property="createBy" column="create_by"/>
    <result property="createName" column="create_name"/>
    <result property="createTime" column="create_time"/>
    <result property="updateBy" column="update_by"/>
    <result property="updateTime" column="update_time"/>
    <result property="registTime" column="regist_time"/>
    <result property="registUser" column="regist_user"/>
    <result property="errorInfo" column="error_info"/>
    <result property="errorTime" column="error_time"/>
  </resultMap>

	<resultMap id="tEmployeeInsurancePreExportMap" type="com.yifu.cloud.plus.v1.yifu.insurances.vo.TEmployeeInsurancePreExportVo">
		<result property="employeeName" column="employee_name"/>
		<result property="empIdcard" column="emp_idcard"/>
		<result property="joinLeaveDate" column="join_leave_date"/>
		<result property="processStatus" column="process_status"/>
		<result property="customerUsername" column="customer_username"/>
		<result property="policyStartEnd" column="policy_start_end"/>
		<result property="deptName" column="dept_name"/>
		<result property="deptNo" column="dept_no"/>
		<result property="expectedCollectionTime" column="expected_collection_time"/>
		<result property="insuranceCompanyName" column="insurance_company_name"/>
		<result property="insuranceTypeName" column="insurance_type_name"/>
		<result property="buyStandard" column="buy_standard"/>
		<result property="buyType" column="buy_type"/>
		<result property="isAddress" column="is_address"/>
	</resultMap>
    <sql id="Base_Column_List">
                a.id,
                a.employee_name,
                a.emp_idcard,
                a.emp_phone,
                a.join_leave_date,
                a.process_status,
                a.customer_username,
                a.dept_name,
                a.dept_no,
                a.dept_id,
                a.expected_collection_time,
                a.customer_user_loginname,
                a.insurance_company_name,
                a.insurance_type_name,
                a.insurance_type_id,
                a.buy_standard,
                a.buy_type,
                a.policy_start,
                a.policy_end,
                a.is_address,
                a.position,
                a.data_source,
                a.file_province,
                a.file_city,
                a.province_code,
                a.city_code,
                a.register_id,
                a.replace_employee_name,
                a.replace_emp_idcard,
                a.replace_dept_no,
                a.create_by,
                a.create_name,
                a.create_time,
                a.update_by,
                a.update_time,a.policy_no,a.config_name,a.policy_effect,a.insurances_id,a.regist_time,a.regist_user,a.config_id,a.error_info,a.error_time
    </sql>
    <sql id="tEmployeeInsurancePre_where">
        <if test="tEmployeeInsurancePre != null">
					<if test="tEmployeeInsurancePre.isRefuse != null and tEmployeeInsurancePre.isRefuse.trim() != ''">
						AND a.IS_REFUSE = #{tEmployeeInsurancePre.isRefuse}
					</if>
					<if test="tEmployeeInsurancePre.isLeave != null and tEmployeeInsurancePre.isLeave.trim() != ''">
						AND a.IS_LEAVE = #{tEmployeeInsurancePre.isLeave}
					</if>
                    <if test="tEmployeeInsurancePre.id != null and tEmployeeInsurancePre.id.trim() != ''">
                        AND a.id = #{tEmployeeInsurancePre.id}
                    </if>
					<if test="tEmployeeInsurancePre.idList != null and tEmployeeInsurancePre.idList.size() > 0">
						and a.id in
						<foreach collection="tEmployeeInsurancePre.idList" item="param" index="index" open="(" close=")"
								 separator=",">
							#{param}
						</foreach>
					</if>
                    <if test="tEmployeeInsurancePre.employeeName != null and tEmployeeInsurancePre.employeeName.trim() != ''">
						AND a.employee_name like CONCAT('%',#{tEmployeeInsurancePre.employeeName},'%')
                    </if>
                    <if test="tEmployeeInsurancePre.empIdcard != null and tEmployeeInsurancePre.empIdcard.trim() != ''">
						AND a.emp_idcard like CONCAT('%',#{tEmployeeInsurancePre.empIdcard},'%')
                    </if>
					<if test="tEmployeeInsurancePre.registUser != null and tEmployeeInsurancePre.registUser.trim() != ''">
						AND a.regist_user like CONCAT('%',#{tEmployeeInsurancePre.registUser},'%')
					</if>
                    <if test="tEmployeeInsurancePre.empPhone != null and tEmployeeInsurancePre.empPhone.trim() != ''">
						AND a.emp_phone like CONCAT('%',#{tEmployeeInsurancePre.empPhone},'%')
                    </if>
					<if test="tEmployeeInsurancePre.joinTimeStart != null">
						AND a.join_leave_date <![CDATA[ >= ]]> #{tEmployeeInsurancePre.joinTimeStart}
					</if>
					<if test="tEmployeeInsurancePre.joinTimeEnd != null">
						AND a.join_leave_date <![CDATA[ <= ]]> #{tEmployeeInsurancePre.joinTimeEnd}
					</if>
                    <if test="tEmployeeInsurancePre.processStatus != null and tEmployeeInsurancePre.processStatus.trim() != ''">
                        AND a.process_status = #{tEmployeeInsurancePre.processStatus}
                    </if>
					<if test="tEmployeeInsurancePre.statusList != null and tEmployeeInsurancePre.statusList.size() > 0">
						AND a.process_status in
						<foreach item="item" index="index" collection="tEmployeeInsurancePre.statusList" open="(" separator="," close=")">
							'${item}'
						</foreach>
					</if>
                    <if test="tEmployeeInsurancePre.customerUsername != null and tEmployeeInsurancePre.customerUsername.trim() != ''">
                        AND a.customer_username like CONCAT('%',#{tEmployeeInsurancePre.customerUsername},'%')
                    </if>
                    <if test="tEmployeeInsurancePre.deptName != null and tEmployeeInsurancePre.deptName.trim() != ''">
						AND a.dept_name like CONCAT('%',#{tEmployeeInsurancePre.deptName},'%')
                    </if>
                    <if test="tEmployeeInsurancePre.deptNo != null and tEmployeeInsurancePre.deptNo.trim() != ''">
                        AND a.dept_no = #{tEmployeeInsurancePre.deptNo}
                    </if>
                    <if test="tEmployeeInsurancePre.deptId != null and tEmployeeInsurancePre.deptId.trim() != ''">
                        AND a.dept_id = #{tEmployeeInsurancePre.deptId}
                    </if>
					<if test="tEmployeeInsurancePre.registTimeStart != null">
						AND a.regist_time <![CDATA[ >= ]]> #{tEmployeeInsurancePre.registTimeStart}
					</if>
					<if test="tEmployeeInsurancePre.expectedCollectionTime != null">
						AND DATE_FORMAT(a.expected_collection_time,'%Y-%m-%d') = CURDATE()
					</if>
					<if test="tEmployeeInsurancePre.registTimeEnd != null">
						and a.regist_time <![CDATA[ <= ]]> #{tEmployeeInsurancePre.registTimeEnd}
					</if>
                    <if test="tEmployeeInsurancePre.insuranceCompanyName != null and tEmployeeInsurancePre.insuranceCompanyName.trim() != ''">
                        AND a.insurance_company_name = #{tEmployeeInsurancePre.insuranceCompanyName}
                    </if>
                    <if test="tEmployeeInsurancePre.insuranceTypeName != null and tEmployeeInsurancePre.insuranceTypeName.trim() != ''">
                        AND a.insurance_type_name = #{tEmployeeInsurancePre.insuranceTypeName}
                    </if>
                    <if test="tEmployeeInsurancePre.insuranceTypeId != null">
                        AND a.insurance_type_id = #{tEmployeeInsurancePre.insuranceTypeId}
                    </if>
                    <if test="tEmployeeInsurancePre.buyStandard != null and tEmployeeInsurancePre.buyStandard.trim() != ''">
                        AND a.buy_standard = #{tEmployeeInsurancePre.buyStandard}
                    </if>
                    <if test="tEmployeeInsurancePre.buyType != null">
                        AND a.buy_type = #{tEmployeeInsurancePre.buyType}
                    </if>
                    <if test="tEmployeeInsurancePre.policyStart != null">
                        AND a.policy_start = #{tEmployeeInsurancePre.policyStart}
                    </if>
                    <if test="tEmployeeInsurancePre.policyEnd != null">
                        AND a.policy_end = #{tEmployeeInsurancePre.policyEnd}
                    </if>
                    <if test="tEmployeeInsurancePre.isAddress != null and tEmployeeInsurancePre.isAddress.trim() != ''">
                        AND a.is_address = #{tEmployeeInsurancePre.isAddress}
                    </if>
                    <if test="tEmployeeInsurancePre.position != null and tEmployeeInsurancePre.position.trim() != ''">
						AND a.position like CONCAT('%',#{tEmployeeInsurancePre.position},'%')
                    </if>
                    <if test="tEmployeeInsurancePre.dataSource != null and tEmployeeInsurancePre.dataSource.trim() != ''">
                        AND a.data_source = #{tEmployeeInsurancePre.dataSource}
                    </if>
                    <if test="tEmployeeInsurancePre.fileProvince != null and tEmployeeInsurancePre.fileProvince.trim() != ''">
                        AND a.file_province = #{tEmployeeInsurancePre.fileProvince}
                    </if>
                    <if test="tEmployeeInsurancePre.fileCity != null and tEmployeeInsurancePre.fileCity.trim() != ''">
                        AND a.file_city = #{tEmployeeInsurancePre.fileCity}
                    </if>
                    <if test="tEmployeeInsurancePre.provinceCode != null and tEmployeeInsurancePre.provinceCode.trim() != ''">
                        AND a.province_code = #{tEmployeeInsurancePre.provinceCode}
                    </if>
                    <if test="tEmployeeInsurancePre.cityCode != null and tEmployeeInsurancePre.cityCode.trim() != ''">
                        AND a.city_code = #{tEmployeeInsurancePre.cityCode}
                    </if>
                    <if test="tEmployeeInsurancePre.registerId != null and tEmployeeInsurancePre.registerId.trim() != ''">
                        AND a.register_id = #{tEmployeeInsurancePre.registerId}
                    </if>
                    <if test="tEmployeeInsurancePre.createBy != null and tEmployeeInsurancePre.createBy.trim() != ''">
                        AND a.create_by = #{tEmployeeInsurancePre.createBy}
                    </if>
					<if test="tEmployeeInsurancePre.authSql != null and tEmployeeInsurancePre.authSql.trim() != ''">
						${tEmployeeInsurancePre.authSql}
					</if>
					<if test="tEmployeeInsurancePre.deptNoList != null and tEmployeeInsurancePre.deptNoList.size > 0">
						AND a.dept_no in
						<foreach item="idStr" index="index" collection="tEmployeeInsurancePre.deptNoList" open="(" separator=","
								 close=")">
							#{idStr}
						</foreach>
					</if>
        </if>
    </sql>
    <!--tEmployeeInsurancePre简单分页查询-->
    <select id="getTEmployeeInsurancePrePage" resultMap="tEmployeeInsurancePreMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM t_employee_insurance_pre a
        <where>
            1=1
            <include refid="tEmployeeInsurancePre_where"/>
        </where>
		order by a.create_time desc
    </select>

	<!--tEmployeeInsurancePre不分页查询-->
	<select id="getTEmployeeInsurancePreNoPage" resultMap="tEmployeeInsurancePreMap">
		SELECT
		<include refid="Base_Column_List"/>
		FROM t_employee_insurance_pre a
		<where>
			1=1 and a.IS_REFUSE ='1' and a.IS_LEAVE ='1'
			<include refid="tEmployeeInsurancePre_where"/>
		</where>
		order by a.create_time desc
	</select>

    <!--查询list，用来对比，记录日志-->
    <select id="getListByEmpPreId" resultMap="tEmployeeInsurancePreMap">
        SELECT
        	<include refid="Base_Column_List"/>
        FROM t_employee_insurance_pre a
        where a.register_id = #{empPreId}
    </select>
    <!--先删后加-->
    <delete id="deleteByEmpPreId">
        delete FROM t_employee_insurance_pre where register_id = #{empPreId} and process_status in ('0','1','2','5')
    </delete>

	<select id="selectExportCount" resultType="java.lang.Long">
		SELECT
		count(1)
		FROM t_employee_insurance_pre a
		<where>
			1=1
			<include refid="tEmployeeInsurancePre_where"/>
		</where>
	</select>

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

	<select id="selectExportList" resultMap="tEmployeeInsurancePreExportMap">
		SELECT
		a.dept_name,
		a.dept_no,
		a.employee_name,
		a.emp_idcard,
		a.join_leave_date,
		CASE WHEN a.process_status ='0' THEN "待确认"
		WHEN a.process_status='1' THEN "待派单"
		WHEN a.process_status ='2' THEN "派单失败"
		WHEN a.process_status ='3' THEN "待投保"
		WHEN a.process_status ='4' THEN "投保中"
		WHEN a.process_status ='5' THEN "投保退回"
		WHEN a.process_status ='6' THEN "已完成"
		WHEN a.process_status ='7' THEN "待缴费"
		ELSE a.process_status END as process_status,
		a.customer_username,
		concat(DATE_FORMAT(a.policy_start,'%Y-%m-%d'),'-',DATE_FORMAT(a.policy_end,'%Y-%m-%d')) as policy_start_end,
		a.expected_collection_time,
		a.insurance_company_name,
		a.insurance_type_name,
		a.buy_standard,
		CASE WHEN a.buy_type ='1' THEN "新增"
		WHEN a.buy_type='3' THEN "批增"
		WHEN a.buy_type ='4' THEN "替换"
		ELSE a.buy_type END as buy_type,
		if(a.is_address = '0','是',if(a.is_address = '1','否','')) as is_address,
		CASE WHEN a.process_status ='2' THEN error_info
		ELSE "" END as error_info
		FROM t_employee_insurance_pre a
		<where>
			1=1 and a.IS_REFUSE ='1' and a.IS_LEAVE ='1'
			<include refid="tEmployeeInsurancePre_where"/>
		</where>
		order by a.create_time desc
		<if test="tEmployeeInsurancePre.limitStart != null">
			limit #{tEmployeeInsurancePre.limitStart},#{tEmployeeInsurancePre.limitEnd}
		</if>
	</select>

	<select id="getAllUnconfimData" resultMap="tEmployeeInsurancePreMap">
		SELECT
		<include refid="Base_Column_List"/>
		FROM t_employee_insurance_pre a
		where a.process_status = '0' and a.IS_REFUSE ='1' and a.IS_LEAVE ='1'  and  DATE_FORMAT(a.expected_collection_time,"%Y-%m-%d") = CURDATE()
		group by a.customer_user_loginname
	</select>

	<select id="getAllUnDisData" resultType="java.lang.String">
		SELECT
		id
		FROM t_employee_insurance_pre a
		<!--  可发起状态有 0 1 2 5 改为 0 1 2 (待确认、待派单、派单失败)   -->
		where a.process_status in ('0','1','2') and a.IS_REFUSE ='1' and a.IS_LEAVE ='1'   and DATE_FORMAT(a.expected_collection_time,"%Y-%m-%d") <![CDATA[ <= ]]> CURDATE()
	</select>
</mapper>
