<?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.social.mapper.TDispatchInfoPreMapper">

  <resultMap id="tDispatchInfoPreMap" type="com.yifu.cloud.plus.v1.yifu.social.entity.TDispatchInfoPre">
    <id property="id" column="ID"/>
    <result property="empName" column="EMP_NAME"/>
    <result property="empIdcard" column="EMP_IDCARD"/>
    <result property="processStatus" column="PROCESS_STATUS"/>
    <result property="deptId" column="DEPT_ID"/>
    <result property="socialProvince" column="SOCIAL_PROVINCE"/>
    <result property="socialProvinceId" column="SOCIAL_PROVINCE_ID"/>
    <result property="socialCity" column="SOCIAL_CITY"/>
    <result property="socialCityId" column="SOCIAL_CITY_ID"/>
    <result property="socialTown" column="SOCIAL_TOWN"/>
    <result property="socialTownId" column="SOCIAL_TOWN_ID"/>
    <result property="fundProvince" column="FUND_PROVINCE"/>
    <result property="fundCity" column="FUND_CITY"/>
    <result property="fundTown" column="FUND_TOWN"/>
    <result property="dispatchItem" column="DISPATCH_ITEM"/>
    <result property="deptName" column="DEPT_NAME"/>
    <result property="deptNo" column="DEPT_NO"/>
    <result property="providentHouseholdName" column="PROVIDENT_HOUSEHOLD_NAME"/>
    <result property="socialHouseholdName" column="SOCIAL_HOUSEHOLD_NAME"/>
    <result property="typeSub" column="TYPE_SUB"/>
    <result property="recordBase" column="RECORD_BASE"/>
    <result property="recordBaseType" column="RECORD_BASE_TYPE"/>
    <result property="joinLeaveDate" column="JOIN_LEAVE_DATE"/>
    <result property="customerUsername" column="CUSTOMER_USERNAME"/>
    <result property="customerUserLoginName" column="CUSTOMER_USER_LOGIN_NAME"/>
    <result property="expectedCollectionTime" column="EXPECTED_COLLECTION_TIME"/>
    <result property="expectedConfirmTime" column="EXPECTED_CONFIRM_TIME"/>
    <result property="isAutoDis" column="IS_AUTO_DIS"/>
    <result property="isCreateDate" column="IS_CREATE_DATE"/>
    <result property="configName" column="CONFIG_NAME"/>
    <result property="configId" column="CONFIG_ID"/>
    <result property="receiveType" column="RECEIVE_TYPE"/>
    <result property="registerId" column="REGISTER_ID"/>
	  <result property="upperLimit" column="UPPER_LIMIT"/>
	  <result property="lowerLimit" column="LOWER_LIMIT"/>
    <result property="pensionStart" column="PENSION_START"/>
    <result property="medicalStart" column="MEDICAL_START"/>
    <result property="unemployStart" column="UNEMPLOY_START"/>
    <result property="workInjuryStart" column="WORK_INJURY_START"/>
    <result property="birthStart" column="BIRTH_START"/>
    <result property="bigailmentStart" column="BIGAILMENT_START"/>
    <result property="socialStartDate" column="SOCIAL_START_DATE"/>
    <result property="trustRemark" column="TRUST_REMARK"/>
    <result property="pensionCardinal" column="PENSION_CARDINAL"/>
    <result property="medicalCardinal" column="MEDICAL_CARDINAL"/>
    <result property="unemploymentCardinal" column="UNEMPLOYMENT_CARDINAL"/>
    <result property="bigailmentCardinal" column="BIGAILMENT_CARDINAL"/>
    <result property="workInjuryCardinal" column="WORK_INJURY_CARDINAL"/>
    <result property="birthCardinal" column="BIRTH_CARDINAL"/>
    <result property="paymentType" column="PAYMENT_TYPE"/>
    <result property="contractStatus" column="CONTRACT_STATUS"/>
    <result property="contractId" column="CONTRACT_ID"/>
    <result property="dispatcherId" column="DISPATCHER_ID"/>
    <result property="socialFundId" column="SOCIAL_FUND_ID"/>
    <result property="createBy" column="CREATE_BY"/>
    <result property="updateBy" column="UPDATE_BY"/>
    <result property="createName" column="CREATE_NAME"/>
    <result property="updateTime" column="UPDATE_TIME"/>
    <result property="createTime" column="CREATE_TIME"/>
    <result property="deleteFlag" column="DELETE_FLAG"/>
	  <result property="errorInfo" column="error_info"/>
	  <result property="errorBackInfo" column="error_back_info"/>
	  <result property="errorTime" column="error_time"/>
	  <result property="position" column="position"/>
	  <result property="phone" column="phone"/>
	  <result property="upPersion" column="UP_PERSION"/>
	  <result property="upMedical" column="UP_MEDICAL"/>
	  <result property="upUnemployment" column="UP_UNEMPLOYMENT"/>
	  <result property="upInjury" column="UP_INJURY"/>
	  <result property="upBirth" column="UP_BIRTH"/>
	  <result property="upBig" column="UP_BIG"/>
	  <result property="lowerPersion" column="LOWER_PERSION"/>
	  <result property="lowerMedical" column="LOWER_MEDICAL"/>
	  <result property="lowerUnemployment" column="LOWER_UNEMPLOYMENT"/>
	  <result property="lowerInjury" column="LOWER_INJURY"/>
	  <result property="lowerBirth" column="LOWER_BIRTH"/>
	  <result property="lowerBig" column="LOWER_BIG"/>

	  <result property="providentCardinal" column="PROVIDENT_CARDINAL"/>
	  <result property="unitProvidentPer" column="UNIT_PROVIDENT_PER"/>
	  <result property="personalProvidentPer" column="PERSONAL_PROVIDENT_PER"/>

	  <result property="configHouseId" column="CONFIG_HOUSE_ID"/>

	  <result property="fundStartDate" column="FUND_START_DATE"/>


  </resultMap>

	<resultMap id="tDispatchInfoPreExportMap" type="com.yifu.cloud.plus.v1.yifu.social.vo.TDispatchInfoPreExportVo">
		<result property="empName" column="emp_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="deptName" column="dept_name"/>
		<result property="deptNo" column="dept_no"/>
		<result property="expectedCollectionTime" column="expected_collection_time"/>
		<result property="socialHouseholdName" column="SOCIAL_HOUSEHOLD_NAME"/>
		<result property="recordBase" column="RECORD_BASE"/>
		<result property="contractStatus" column="CONTRACT_STATUS"/>

		<result property="pensionStart" column="PENSION_START"/>
		<result property="medicalStart" column="MEDICAL_START"/>
		<result property="unemployStart" column="UNEMPLOY_START"/>
		<result property="workInjuryStart" column="WORK_INJURY_START"/>
		<result property="birthStart" column="BIRTH_START"/>
		<result property="bigailmentStart" column="BIGAILMENT_START"/>


	</resultMap>

	<resultMap id="tDispatchInfoPreFundExportMap" type="com.yifu.cloud.plus.v1.yifu.social.vo.TDispatchInfoPreFundExportVo">
		<result property="empName" column="emp_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="deptName" column="dept_name"/>
		<result property="deptNo" column="dept_no"/>
		<result property="expectedCollectionTime" column="expected_collection_time"/>
		<result property="fundHouseholdName" column="PROVIDENT_HOUSEHOLD_NAME"/>
		<result property="providentCardinal" column="PROVIDENT_CARDINAL"/>
		<result property="unitProvidentPer" column="UNIT_PROVIDENT_PER"/>
		<result property="personalProvidentPer" column="PERSONAL_PROVIDENT_PER"/>
		<result property="contractStatus" column="CONTRACT_STATUS"/>
		<result property="fundStartDate" column="FUND_START_DATE"/>

	</resultMap>
    <sql id="Base_Column_List">
                a.ID,
                a.EMP_NAME,
                a.EMP_IDCARD,
                a.PROCESS_STATUS,
                a.DEPT_ID,
                a.SOCIAL_PROVINCE,
                a.SOCIAL_CITY,
                a.SOCIAL_TOWN,
                a.FUND_PROVINCE,
                a.FUND_CITY,
                a.FUND_TOWN,
                a.DISPATCH_ITEM,
                a.DEPT_NAME,
                a.DEPT_NO,
                a.PROVIDENT_HOUSEHOLD_NAME,
                a.SOCIAL_HOUSEHOLD_NAME,
                a.TYPE_SUB,
                a.RECEIVE_TYPE,
                a.RECORD_BASE,
                a.RECORD_BASE_TYPE,
                a.JOIN_LEAVE_DATE,
                a.CUSTOMER_USERNAME,
                a.CUSTOMER_USER_LOGIN_NAME,
                a.EXPECTED_COLLECTION_TIME,
                a.EXPECTED_CONFIRM_TIME,
                a.IS_AUTO_DIS,
                a.IS_CREATE_DATE,
                a.CONFIG_NAME,
                a.CONFIG_ID,
                a.REGISTER_ID,
                a.PENSION_START,
                a.MEDICAL_START,
                a.UNEMPLOY_START,
                a.SOCIAL_START_DATE,
                a.WORK_INJURY_START,
                a.BIRTH_START,
                a.BIGAILMENT_START,
                a.TRUST_REMARK,
                a.PENSION_CARDINAL,
                a.MEDICAL_CARDINAL,
                a.UNEMPLOYMENT_CARDINAL,
                a.BIGAILMENT_CARDINAL,
                a.WORK_INJURY_CARDINAL,
                a.BIRTH_CARDINAL,
                a.PAYMENT_TYPE,
                a.CONTRACT_ID,
                a.CREATE_BY,
                a.UPDATE_BY,
                a.CREATE_NAME,
                a.UPDATE_TIME,
                a.CREATE_TIME,
                a.DELETE_FLAG,error_info,error_time,a.DISPATCHER_ID,a.error_back_info,a.position,SOCIAL_FUND_ID,a.phone,
				a.SOCIAL_PROVINCE_ID,a.SOCIAL_CITY_ID,a.SOCIAL_TOWN_ID
				,a.UP_PERSION,
				a.UP_MEDICAL,
				a.UP_UNEMPLOYMENT,
				a.UP_INJURY,
				a.UP_BIRTH,
				a.UP_BIG,
				a.LOWER_PERSION,
				a.LOWER_MEDICAL,
				a.LOWER_UNEMPLOYMENT,
				a.LOWER_INJURY,
				a.LOWER_BIRTH,
				a.LOWER_BIG,
				a.PROVIDENT_CARDINAL,
				a.UNIT_PROVIDENT_PER,
				a.PERSONAL_PROVIDENT_PER,
				a.CONFIG_HOUSE_ID
    </sql>
	<sql id="tDispatchInfoPre_other_where">
		<if test="tDispatchInfoPre != null">
			<if test="tDispatchInfoPre.isRefuse != null and tDispatchInfoPre.isRefuse.trim() != ''">
				AND a.IS_REFUSE = #{tDispatchInfoPre.isRefuse}
			</if>
			<if test="tDispatchInfoPre.id != null and tDispatchInfoPre.id.trim() != ''">
				AND a.ID = #{tDispatchInfoPre.id}
			</if>
			<if test="tDispatchInfoPre.idList != null and tDispatchInfoPre.idList.size() > 0">
				and a.id in
				<foreach collection="tDispatchInfoPre.idList" item="param" index="index" open="(" close=")"
						 separator=",">
					#{param}
				</foreach>
			</if>
			<if test="tDispatchInfoPre.statusList != null and tDispatchInfoPre.statusList.size() > 0">
				AND a.process_status in
				<foreach item="item" index="index" collection="tDispatchInfoPre.statusList" open="(" separator="," close=")">
					'${item}'
				</foreach>
			</if>
			<if test="tDispatchInfoPre.empName != null and tDispatchInfoPre.empName.trim() != ''">
				AND a.EMP_NAME like CONCAT('%',#{tDispatchInfoPre.empName},'%')
			</if>
			<if test="tDispatchInfoPre.empIdcard != null and tDispatchInfoPre.empIdcard.trim() != ''">
				AND a.EMP_IDCARD = #{tDispatchInfoPre.empIdcard}
			</if>
			<if test="tDispatchInfoPre.processStatus != null and tDispatchInfoPre.processStatus.trim() != ''">
				AND a.PROCESS_STATUS = #{tDispatchInfoPre.processStatus}
			</if>

			<if test="tDispatchInfoPre.deptName != null and tDispatchInfoPre.deptName.trim() != ''">
				AND a.DEPT_NAME like CONCAT('%',#{tDispatchInfoPre.deptName},'%')
			</if>
			<if test="tDispatchInfoPre.deptNo != null and tDispatchInfoPre.deptNo.trim() != ''">
				AND a.DEPT_NO = #{tDispatchInfoPre.deptNo}
			</if>
			<if test="tDispatchInfoPre.providentHouseholdName != null and tDispatchInfoPre.providentHouseholdName.trim() != ''">
				AND a.PROVIDENT_HOUSEHOLD_NAME = #{tDispatchInfoPre.providentHouseholdName}
			</if>
			<if test="tDispatchInfoPre.socialHouseholdName != null and tDispatchInfoPre.socialHouseholdName.trim() != ''">
				AND a.SOCIAL_HOUSEHOLD_NAME = #{tDispatchInfoPre.socialHouseholdName}
			</if>
			<if test="tDispatchInfoPre.typeSub != null and tDispatchInfoPre.typeSub.trim() != ''">
				AND a.TYPE_SUB = #{tDispatchInfoPre.typeSub}
			</if>
			<if test="tDispatchInfoPre.customerUsername != null and tDispatchInfoPre.customerUsername.trim() != ''">
				AND a.CUSTOMER_USERNAME = #{tDispatchInfoPre.customerUsername}
			</if>
			<if test="tDispatchInfoPre.customerUserLoginName != null and tDispatchInfoPre.customerUserLoginName.trim() != ''">
				AND a.CUSTOMER_USER_LOGIN_NAME = #{tDispatchInfoPre.customerUsername.customerUserLoginName}
			</if>
			<if test="tDispatchInfoPre.expectedCollectionTime != null">
				AND DATE_FORMAT(a.EXPECTED_COLLECTION_TIME,'%Y-%m-%d') = CURDATE()
			</if>
			<if test="tDispatchInfoPre.dispatcherTimeStart != null">
				AND DATE_FORMAT(a.EXPECTED_COLLECTION_TIME,'%Y-%m-%d') <![CDATA[ >= ]]> #{tDispatchInfoPre.dispatcherTimeStart}
			</if>
			<if test="tDispatchInfoPre.dispatcherTimeEnd != null">
				and DATE_FORMAT(a.EXPECTED_COLLECTION_TIME,'%Y-%m-%d') <![CDATA[ <= ]]> #{tDispatchInfoPre.dispatcherTimeEnd}
			</if>
			<if test="tDispatchInfoPre.expectedConfirmTime != null">
				AND a.EXPECTED_CONFIRM_TIME = #{tDispatchInfoPre.expectedConfirmTime}
			</if>
			<if test="tDispatchInfoPre.isAutoDis != null and tDispatchInfoPre.isAutoDis.trim() != ''">
				AND a.IS_AUTO_DIS = #{tDispatchInfoPre.isAutoDis}
			</if>
			<if test="tDispatchInfoPre.isCreateDate != null and tDispatchInfoPre.isCreateDate.trim() != ''">
				AND a.IS_CREATE_DATE = #{tDispatchInfoPre.isCreateDate}
			</if>
			<if test="tDispatchInfoPre.contractStatus != null and tDispatchInfoPre.contractStatus.trim() != ''">
				AND a.CONTRACT_STATUS = #{tDispatchInfoPre.contractStatus}
			</if>
			<if test="tDispatchInfoPre.failType != null and tDispatchInfoPre.failType.trim() != ''">
				AND a.error_info is not null
			</if>
			<if test="tDispatchInfoPre.autoSendFlag != null and tDispatchInfoPre.autoSendFlag.trim() != ''">
				AND a.EXPECTED_CONFIRM_TIME is not null and DATE_FORMAT(a.EXPECTED_CONFIRM_TIME,'%Y-%m-%d') <![CDATA[ <= ]]> CURDATE()
			</if>
			<if test="tDispatchInfoPre.timeSendFlag != null and tDispatchInfoPre.timeSendFlag.trim() != ''">
				AND (a.EXPECTED_COLLECTION_TIME is null or
				DATE_FORMAT(a.EXPECTED_COLLECTION_TIME,'%Y-%m-%d') <![CDATA[ <= ]]> CURDATE() )
			</if>
			<if test="tDispatchInfoPre.authSql != null and tDispatchInfoPre.authSql.trim() != ''">
				${tDispatchInfoPre.authSql}
			</if>
			<if test="tDispatchInfoPre.deptNoList != null and tDispatchInfoPre.deptNoList.size > 0">
				AND a.dept_no in
				<foreach item="idStr" index="index" collection="tDispatchInfoPre.deptNoList" open="(" separator=","
						 close=")">
					#{idStr}
				</foreach>
			</if>
			<!-- 公积金起缴日期查询 -->
			<if test="tDispatchInfoPre.fundTimeStart != null">
				AND DATE_FORMAT(a.FUND_START_DATE,'%Y-%m-%d') <![CDATA[ >= ]]> #{tDispatchInfoPre.fundTimeStart}
			</if>
			<if test="tDispatchInfoPre.fundTimeEnd != null">
				AND DATE_FORMAT(a.FUND_START_DATE,'%Y-%m-%d') <![CDATA[ <= ]]> #{tDispatchInfoPre.fundTimeEnd}
			</if>
			<!-- 社保五险起缴日期查询，有一个符合就出现 -->
			<if test="tDispatchInfoPre.socialTimeStart != null and tDispatchInfoPre.socialTimeEnd != null">
				AND (
				(a.PENSION_START is not null and     DATE_FORMAT(a.PENSION_START,'%Y-%m-%d') <![CDATA[ >= ]]> #{tDispatchInfoPre.socialTimeStart}
				and  DATE_FORMAT(a.PENSION_START,'%Y-%m-%d') <![CDATA[ <= ]]> #{tDispatchInfoPre.socialTimeEnd}
				) or
				(a.MEDICAL_START is not null and     DATE_FORMAT(a.MEDICAL_START,'%Y-%m-%d') <![CDATA[ >= ]]> #{tDispatchInfoPre.socialTimeStart}
				and     DATE_FORMAT(a.MEDICAL_START,'%Y-%m-%d') <![CDATA[ <= ]]> #{tDispatchInfoPre.socialTimeEnd}
				) or
				(a.WORK_INJURY_START is not null and DATE_FORMAT(a.WORK_INJURY_START,'%Y-%m-%d') <![CDATA[ >= ]]> #{tDispatchInfoPre.socialTimeStart}
				and DATE_FORMAT(a.WORK_INJURY_START,'%Y-%m-%d') <![CDATA[ <= ]]> #{tDispatchInfoPre.socialTimeEnd}
				) or
				(a.UNEMPLOY_START is not null  and   DATE_FORMAT(a.UNEMPLOY_START,'%Y-%m-%d') <![CDATA[ >= ]]> #{tDispatchInfoPre.socialTimeStart}
				and   DATE_FORMAT(a.UNEMPLOY_START,'%Y-%m-%d') <![CDATA[ <= ]]> #{tDispatchInfoPre.socialTimeEnd}
				) or
				(a.BIRTH_START is not null  and      DATE_FORMAT(a.BIRTH_START,'%Y-%m-%d') <![CDATA[ >= ]]> #{tDispatchInfoPre.socialTimeStart}
				and      DATE_FORMAT(a.BIRTH_START,'%Y-%m-%d') <![CDATA[ <= ]]> #{tDispatchInfoPre.socialTimeEnd}
				) or
				(a.BIGAILMENT_START is not null and  DATE_FORMAT(a.BIGAILMENT_START,'%Y-%m-%d') <![CDATA[ >= ]]> #{tDispatchInfoPre.socialTimeStart}
				and  DATE_FORMAT(a.BIGAILMENT_START,'%Y-%m-%d') <![CDATA[ <= ]]> #{tDispatchInfoPre.socialTimeEnd}
				)
				)
			</if>

		</if>
	</sql>

    <sql id="tDispatchInfoPre_where">
        <if test="tDispatchInfoPre != null">
					<if test="tDispatchInfoPre.isRefuse != null and tDispatchInfoPre.isRefuse.trim() != ''">
						AND a.IS_REFUSE = #{tDispatchInfoPre.isRefuse}
					</if>
                    <if test="tDispatchInfoPre.id != null and tDispatchInfoPre.id.trim() != ''">
                        AND a.ID = #{tDispatchInfoPre.id}
                    </if>
					<if test="tDispatchInfoPre.idList != null and tDispatchInfoPre.idList.size() > 0">
						and a.id in
						<foreach collection="tDispatchInfoPre.idList" item="param" index="index" open="(" close=")"
								 separator=",">
							#{param}
						</foreach>
					</if>
					<if test="tDispatchInfoPre.statusList != null and tDispatchInfoPre.statusList.size() > 0">
						AND a.process_status in
						<foreach item="item" index="index" collection="tDispatchInfoPre.statusList" open="(" separator="," close=")">
							'${item}'
						</foreach>
					</if>
                    <if test="tDispatchInfoPre.empName != null and tDispatchInfoPre.empName.trim() != ''">
                        AND a.EMP_NAME like CONCAT('%',#{tDispatchInfoPre.empName},'%')
                    </if>
                    <if test="tDispatchInfoPre.empIdcard != null and tDispatchInfoPre.empIdcard.trim() != ''">
                        AND a.EMP_IDCARD = #{tDispatchInfoPre.empIdcard}
                    </if>
                    <if test="tDispatchInfoPre.processStatus != null and tDispatchInfoPre.processStatus.trim() != ''">
                        AND a.PROCESS_STATUS = #{tDispatchInfoPre.processStatus}
                    </if>

                    <if test="tDispatchInfoPre.deptName != null and tDispatchInfoPre.deptName.trim() != ''">
                        AND a.DEPT_NAME like CONCAT('%',#{tDispatchInfoPre.deptName},'%')
                    </if>
                    <if test="tDispatchInfoPre.deptNo != null and tDispatchInfoPre.deptNo.trim() != ''">
                        AND a.DEPT_NO = #{tDispatchInfoPre.deptNo}
                    </if>
                    <if test="tDispatchInfoPre.providentHouseholdName != null and tDispatchInfoPre.providentHouseholdName.trim() != ''">
                        AND a.PROVIDENT_HOUSEHOLD_NAME = #{tDispatchInfoPre.providentHouseholdName}
                    </if>
                    <if test="tDispatchInfoPre.socialHouseholdName != null and tDispatchInfoPre.socialHouseholdName.trim() != ''">
                        AND a.SOCIAL_HOUSEHOLD_NAME = #{tDispatchInfoPre.socialHouseholdName}
                    </if>
                    <if test="tDispatchInfoPre.typeSub != null and tDispatchInfoPre.typeSub.trim() != ''">
                        AND a.TYPE_SUB = #{tDispatchInfoPre.typeSub}
                    </if>
                    <if test="tDispatchInfoPre.customerUsername != null and tDispatchInfoPre.customerUsername.trim() != ''">
                        AND a.CUSTOMER_USERNAME like CONCAT('%',#{tDispatchInfoPre.customerUsername},'%')
                    </if>
					<if test="tDispatchInfoPre.customerUserLoginName != null and tDispatchInfoPre.customerUserLoginName.trim() != ''">
						AND a.CUSTOMER_USER_LOGIN_NAME = #{tDispatchInfoPre.customerUsername.customerUserLoginName}
					</if>
                    <if test="tDispatchInfoPre.expectedCollectionTime != null">
                        AND DATE_FORMAT(a.EXPECTED_COLLECTION_TIME,'%Y-%m-%d') = CURDATE()
                    </if>
					<if test="tDispatchInfoPre.dispatcherTimeStart != null">
						AND DATE_FORMAT(a.EXPECTED_COLLECTION_TIME,'%Y-%m-%d') <![CDATA[ >= ]]> #{tDispatchInfoPre.dispatcherTimeStart}
					</if>
					<if test="tDispatchInfoPre.dispatcherTimeEnd != null">
						and DATE_FORMAT(a.EXPECTED_COLLECTION_TIME,'%Y-%m-%d') <![CDATA[ <= ]]> #{tDispatchInfoPre.dispatcherTimeEnd}
					</if>
                    <if test="tDispatchInfoPre.expectedConfirmTime != null">
                        AND a.EXPECTED_CONFIRM_TIME = #{tDispatchInfoPre.expectedConfirmTime}
                    </if>
                    <if test="tDispatchInfoPre.isAutoDis != null and tDispatchInfoPre.isAutoDis.trim() != ''">
                        AND a.IS_AUTO_DIS = #{tDispatchInfoPre.isAutoDis}
                    </if>
                    <if test="tDispatchInfoPre.isCreateDate != null and tDispatchInfoPre.isCreateDate.trim() != ''">
                        AND a.IS_CREATE_DATE = #{tDispatchInfoPre.isCreateDate}
                    </if>
                    <if test="tDispatchInfoPre.contractStatus != null and tDispatchInfoPre.contractStatus.trim() != ''">
                        AND if(b.EMP_IDCARD is not null,'0','1') = #{tDispatchInfoPre.contractStatus}
                    </if>
					<if test="tDispatchInfoPre.failType != null and tDispatchInfoPre.failType.trim() != ''">
						AND a.error_info is not null
					</if>
					<if test="tDispatchInfoPre.authSql != null and tDispatchInfoPre.authSql.trim() != ''">
						${tDispatchInfoPre.authSql}
					</if>
					<if test="tDispatchInfoPre.deptNoList != null and tDispatchInfoPre.deptNoList.size > 0">
						AND a.dept_no in
						<foreach item="idStr" index="index" collection="tDispatchInfoPre.deptNoList" open="(" separator=","
								 close=")">
							#{idStr}
						</foreach>
					</if>
			<!-- 公积金起缴日期查询 -->
			<if test="tDispatchInfoPre.fundTimeStart != null">
				AND DATE_FORMAT(a.FUND_START_DATE,'%Y-%m-%d') <![CDATA[ >= ]]> #{tDispatchInfoPre.fundTimeStart}
			</if>
			<if test="tDispatchInfoPre.fundTimeEnd != null">
				AND DATE_FORMAT(a.FUND_START_DATE,'%Y-%m-%d') <![CDATA[ <= ]]> #{tDispatchInfoPre.fundTimeEnd}
			</if>
			<!-- 社保五险起缴日期查询，有一个符合就出现 -->
			<if test="tDispatchInfoPre.socialTimeStart != null and tDispatchInfoPre.socialTimeEnd != null">
				AND (
				(a.PENSION_START is not null and     DATE_FORMAT(a.PENSION_START,'%Y-%m-%d') <![CDATA[ >= ]]> #{tDispatchInfoPre.socialTimeStart}
				and  DATE_FORMAT(a.PENSION_START,'%Y-%m-%d') <![CDATA[ <= ]]> #{tDispatchInfoPre.socialTimeEnd}
				) or
				(a.MEDICAL_START is not null and     DATE_FORMAT(a.MEDICAL_START,'%Y-%m-%d') <![CDATA[ >= ]]> #{tDispatchInfoPre.socialTimeStart}
				and     DATE_FORMAT(a.MEDICAL_START,'%Y-%m-%d') <![CDATA[ <= ]]> #{tDispatchInfoPre.socialTimeEnd}
				) or
				(a.WORK_INJURY_START is not null and DATE_FORMAT(a.WORK_INJURY_START,'%Y-%m-%d') <![CDATA[ >= ]]> #{tDispatchInfoPre.socialTimeStart}
				and DATE_FORMAT(a.WORK_INJURY_START,'%Y-%m-%d') <![CDATA[ <= ]]> #{tDispatchInfoPre.socialTimeEnd}
				) or
				(a.UNEMPLOY_START is not null  and   DATE_FORMAT(a.UNEMPLOY_START,'%Y-%m-%d') <![CDATA[ >= ]]> #{tDispatchInfoPre.socialTimeStart}
				and   DATE_FORMAT(a.UNEMPLOY_START,'%Y-%m-%d') <![CDATA[ <= ]]> #{tDispatchInfoPre.socialTimeEnd}
				) or
				(a.BIRTH_START is not null  and      DATE_FORMAT(a.BIRTH_START,'%Y-%m-%d') <![CDATA[ >= ]]> #{tDispatchInfoPre.socialTimeStart}
				and      DATE_FORMAT(a.BIRTH_START,'%Y-%m-%d') <![CDATA[ <= ]]> #{tDispatchInfoPre.socialTimeEnd}
				) or
				(a.BIGAILMENT_START is not null and  DATE_FORMAT(a.BIGAILMENT_START,'%Y-%m-%d') <![CDATA[ >= ]]> #{tDispatchInfoPre.socialTimeStart}
				and  DATE_FORMAT(a.BIGAILMENT_START,'%Y-%m-%d') <![CDATA[ <= ]]> #{tDispatchInfoPre.socialTimeEnd}
				)
				)
			</if>
        </if>
    </sql>
    <!--tDispatchInfoPre简单分页查询-->
    <select id="getTDispatchInfoPrePage" resultMap="tDispatchInfoPreMap">
        SELECT
        <include refid="Base_Column_List"/>
		,if(b.EMP_IDCARD is not null,'0','1') as CONTRACT_STATUS,a.FUND_START_DATE
        FROM t_dispatch_info_pre a
		<!--huych:跨库建档案的合同视图，因为需要查询合同的最新状态，合同表只用到身份证和项目编码两个字段，跨库查效率较高，如果后期档案改库名需要调整这块代码-->
		left join view_contract b on a.EMP_IDCARD = b.EMP_IDCARD and a.DEPT_NO = b.DEPT_NO
        <where>
			a.DELETE_FLAG = '0'
            <include refid="tDispatchInfoPre_where"/>
        </where>
		group by a.id
		order by a.create_time desc
    </select>

	<select id="selectExportCount" resultType="java.lang.Long">
		select count(a.id) from (
		SELECT
		a.id
		FROM t_dispatch_info_pre a
		left join view_contract b on a.EMP_IDCARD = b.EMP_IDCARD and a.DEPT_NO = b.DEPT_NO
		<where>
			1=1  and a.type_sub = '0'
			<include refid="tDispatchInfoPre_where"/>
		</where>
		group by a.id
		) a
	</select>
	<select id="selectFundExportCount" resultType="java.lang.Long">
		select count(a.id) from (
		SELECT
		a.id
		FROM t_dispatch_info_pre a
		left join view_contract b on a.EMP_IDCARD = b.EMP_IDCARD and a.DEPT_NO = b.DEPT_NO
		<where>
			1=1 and a.type_sub = '1'
			and a.DELETE_FLAG = '0'
			<include refid="tDispatchInfoPre_where"/>
		</where>
		group by a.id) a
	</select>

	<select id="selectSocialCount" resultType="java.lang.Long">
		SELECT
		count(a.id)

		FROM t_dispatch_info_pre a
		<where>
			1=1  and a.type_sub = '0' and a.DELETE_FLAG = '0'
			<include refid="tDispatchInfoPre_where"/>
		</where>
	</select>

	<select id="selectFundCount" resultType="java.lang.Long">
		SELECT
		count(a.id)
		FROM t_dispatch_info_pre a
		<where>
			1=1 and a.type_sub = '1'
			and a.DELETE_FLAG = '0'
			<include refid="tDispatchInfoPre_other_where"/>
		</where>
	</select>

	<select id="selectExportList" resultMap="tDispatchInfoPreExportMap">
		SELECT
		a.dept_name,
		a.dept_no,
		a.emp_name,
		a.emp_idcard,
		a.SOCIAL_HOUSEHOLD_NAME,
		a.RECORD_BASE,
		DATE_FORMAT(a.join_leave_date,'%Y-%m-%d') as join_leave_date,
		if(b.EMP_IDCARD is not null,'已签署','未签署') as CONTRACT_STATUS,
		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 "办理成功"
		WHEN a.process_status ='8' THEN "办理失败"
		WHEN a.process_status ='9' THEN "部分办理失败"
		ELSE a.process_status END as process_status,
		a.expected_collection_time,
		a.customer_username,
		a.PENSION_START,
		a.MEDICAL_START,
		a.WORK_INJURY_START,
		a.UNEMPLOY_START,
		a.BIRTH_START,
		a.BIGAILMENT_START
		FROM t_dispatch_info_pre a
		left join view_contract b on a.EMP_IDCARD = b.EMP_IDCARD and a.DEPT_NO = b.DEPT_NO
		<where>
			a.DELETE_FLAG = '0' and a.type_sub = '0'
			<include refid="tDispatchInfoPre_where"/>
		</where>
		group by a.id
		order by a.create_time desc
		<if test="tDispatchInfoPre.limitStart != null">
			limit #{tDispatchInfoPre.limitStart},#{tDispatchInfoPre.limitEnd}
		</if>
	</select>
	<select id="selectFundExportList" resultMap="tDispatchInfoPreFundExportMap">
		SELECT
		a.dept_name,
		a.dept_no,
		a.emp_name,
		a.emp_idcard,
		a.PROVIDENT_HOUSEHOLD_NAME,
		a.PROVIDENT_CARDINAL,
		a.UNIT_PROVIDENT_PER,
		a.PERSONAL_PROVIDENT_PER,
		DATE_FORMAT(a.join_leave_date,'%Y-%m-%d') as join_leave_date,
		if(b.EMP_IDCARD is not null,'已签署','未签署') as CONTRACT_STATUS,
		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 "办理成功"
		WHEN a.process_status ='8' THEN "办理失败"
		WHEN a.process_status ='9' THEN "部分办理失败"
		ELSE a.process_status END as process_status,
		a.expected_collection_time,
		a.customer_username,
		a.FUND_START_DATE
		FROM t_dispatch_info_pre a
		left join view_contract b on a.EMP_IDCARD = b.EMP_IDCARD and a.DEPT_NO = b.DEPT_NO
		<where>
			a.DELETE_FLAG = '0' and  a.type_sub = '1'
			<include refid="tDispatchInfoPre_where"/>
		</where>
		group by a.id
		order by a.create_time desc
		<if test="tDispatchInfoPre.limitStart != null">
			limit #{tDispatchInfoPre.limitStart},#{tDispatchInfoPre.limitEnd}
		</if>
	</select>
	<!--tDispatchInfoPre c端不分页查询-->
	<select id="geNoPageList" resultMap="tDispatchInfoPreMap">
		SELECT
		<include refid="Base_Column_List"/>
		,if(b.EMP_IDCARD is not null,'0','1') as CONTRACT_STATUS,a.FUND_START_DATE
		FROM t_dispatch_info_pre a
		left join view_contract b on a.EMP_IDCARD = b.EMP_IDCARD and a.DEPT_NO = b.DEPT_NO
		<where>
			a.DELETE_FLAG = '0' and a.IS_REFUSE ='1'
			<include refid="tDispatchInfoPre_other_where"/>
		</where>
		GROUP BY a.id
		order by a.create_time desc
		limit 0,2000
	</select>

	<!--tDispatchInfoPre c端不分页查询-->
	<select id="geNoPageListNew" resultMap="tDispatchInfoPreMap">
		SELECT
		<include refid="Base_Column_List"/>
		,a.FUND_START_DATE
		FROM t_dispatch_info_pre a
		<where>
			a.DELETE_FLAG = '0' and a.IS_REFUSE ='1'
			<include refid="tDispatchInfoPre_other_where"/>
		</where>
		order by a.create_time desc
		limit 0,2000
	</select>

	<!--tDispatchInfoPre社保待派单数据查询-->
	<select id="getDisPatcherSocialList" resultType="com.yifu.cloud.plus.v1.yifu.social.vo.TDispatchImportVo">
		SELECT
		(@i :=@i+1) as rowIndex,
		a.EMP_NAME empName,
		a.EMP_IDCARD empIdcard,
		a.dept_no settleDomainCode,
		a.RECORD_BASE recordBase,
		a.SOCIAL_HOUSEHOLD_NAME socialHousehold,
		if(a.IS_CREATE_DATE = '0',a.SOCIAL_START_DATE,a.PENSION_START) as pensionStart,
		if(a.IS_CREATE_DATE = '0',a.SOCIAL_START_DATE,a.MEDICAL_START) as medicalStart,
		if(a.IS_CREATE_DATE = '0',a.SOCIAL_START_DATE,a.WORK_INJURY_START) as workInjuryStart,
		if(a.IS_CREATE_DATE = '0',a.SOCIAL_START_DATE,a.UNEMPLOY_START) as unemployStart,
		if(a.IS_CREATE_DATE = '0',a.SOCIAL_START_DATE,a.BIRTH_START) as birthStart,
		if(a.IS_CREATE_DATE = '0',a.SOCIAL_START_DATE,a.BIGAILMENT_START) as bigailmentStart,
		a.PAYMENT_TYPE as paymentType,
		if(a.PAYMENT_TYPE = '1',PENSION_CARDINAL,null) as pensionCardinal,
		if(a.PAYMENT_TYPE = '1',MEDICAL_CARDINAL,null) as medicalCardinal,
		if(a.PAYMENT_TYPE = '1',UNEMPLOYMENT_CARDINAL,null) as unemploymentCardinal,
		if(a.PAYMENT_TYPE = '1',WORK_INJURY_CARDINAL,null) as workInjuryCardinal,
		if(a.PAYMENT_TYPE = '1',BIRTH_CARDINAL,null) as birthCardinal,
		if(a.PAYMENT_TYPE = '1',BIGAILMENT_CARDINAL,null) as bigailmentCardinal,
		a.id preId,
		a.TRUST_REMARK trustRemark,
		a.CUSTOMER_USER_LOGIN_NAME as preLoginName,
		if(#{type} = '1',concat('自动化手动-',a.customer_username),concat('自动化自动-',a.customer_username)) preName
		FROM t_dispatch_info_pre a,(select @i:=0) as itable
		<where>
			1=1 and a.IS_REFUSE='1'
			AND a.id in
			<foreach item="idStr" index="index" collection="idList" open="(" separator="," close=")">
				#{idStr}
			</foreach>
			AND a.process_status in
			<foreach item="status" index="index" collection="statusList" open="(" separator="," close=")">
				#{status}
			</foreach>
		</where>
	</select>
	<!--tDispatchInfoPre公积金待派单数据查询-->
	<select id="getDisPatcherFundList" resultType="com.yifu.cloud.plus.v1.yifu.social.vo.TDispatchImportVo">
		SELECT
		(@i :=@i+1) as rowIndex,
		a.EMP_NAME empName,
		a.EMP_IDCARD empIdcard,
		a.dept_no settleDomainCode,
		a.RECORD_BASE recordBase,
		a.PROVIDENT_HOUSEHOLD_NAME providentHousehold,
		CASE WHEN a.PAYMENT_TYPE ='0' THEN c.LOWER_LIMIT
		WHEN a.PAYMENT_TYPE ='2' THEN c.UPPER_LIMIT
		ELSE a.PROVIDENT_CARDINAL END as 'providentCardinal',
		a.FUND_START_DATE providentStart,
		a.FUND_PER_FLAG perFlag,
		a.UNIT_PROVIDENT_PER providentPer,
		a.PERSONAL_PROVIDENT_PER personalProvidentPer,
		a.id preId,
		a.TRUST_REMARK trustRemark,
		a.CUSTOMER_USER_LOGIN_NAME as preLoginName,
		if(#{type} = '1',concat('自动化手动-',a.customer_username),concat('自动化自动-',a.customer_username)) preName
		FROM t_dispatch_info_pre a
		LEFT JOIN (select t.DEPART_NAME,t.LOWER_LIMIT,t.UPPER_LIMIT from sys_base_set_info t where t.BASE_TYPE = '1' and t.STATUS = 0 and t.DELETE_FLAG = '0' and
		DATE_FORMAT(t.APPLY_START_DATE,'%Y%m%d') <![CDATA[ <= ]]> DATE_FORMAT(CURDATE(),'%Y%m%d') and DATE_FORMAT(CURDATE(),'%Y%m%d') <![CDATA[ <= ]]> IFNULL(t.APPLY_END_DATE,DATE_FORMAT(CURDATE(),'%Y%m%d'))
		) c on c.DEPART_NAME = a.PROVIDENT_HOUSEHOLD_NAME
		,(select @i:=0) as itable
		<where>
			1=1 and a.IS_REFUSE='1'
			AND a.id in
			<foreach item="idStr" index="index" collection="idList" open="(" separator="," close=")">
				#{idStr}
			</foreach>
			AND a.process_status in
			<foreach item="status" index="index" collection="statusList" open="(" separator="," close=")">
				#{status}
			</foreach>
		</where>
		group by a.id
	</select>
	<select id="getAllUnDisSocialData" resultMap="tDispatchInfoPreMap">
		SELECT
		<include refid="Base_Column_List"/>,a.FUND_START_DATE
		FROM t_dispatch_info_pre a
		where
			a.expected_collection_time is not null and type_sub = '0'
		  and DATE_FORMAT(a.expected_collection_time,"%Y-%m-%d") <![CDATA[ <= ]]> CURDATE()
		  and a.process_status = '0' and a.IS_REFUSE='1'
		group by a.customer_user_login_name
	</select>

	<select id="getAllUnDisFundData" resultMap="tDispatchInfoPreMap">
		SELECT
		<include refid="Base_Column_List"/>,a.FUND_START_DATE
		FROM t_dispatch_info_pre a
		where
		a.expected_collection_time is not null  and type_sub = '1'
		and DATE_FORMAT(a.expected_collection_time,"%Y-%m-%d") <![CDATA[ <= ]]> CURDATE()
		and a.process_status = '0' and a.IS_REFUSE='1'
		group by a.customer_user_login_name
	</select>

	<select id="getAllUnDisDataIdList" resultType="java.lang.String">
		SELECT
			id
		FROM t_dispatch_info_pre a
		where
			a.expected_collection_time is not null
		  and DATE_FORMAT(a.expected_collection_time,"%Y-%m-%d") <![CDATA[ <= ]]> CURDATE()
		  and a.process_status in ('1','2','4','8','9')
		  and a.type_sub = '0' and a.IS_REFUSE='1'
	</select>

	<select id="getAllUnDisDataIdFundList" resultType="java.lang.String">
		SELECT
			id
		FROM t_dispatch_info_pre a
		where
			a.expected_collection_time is not null
		  and DATE_FORMAT(a.expected_collection_time,"%Y-%m-%d") <![CDATA[ <= ]]> CURDATE()
		  and a.process_status in ('1','2','4','8','9')
		  and a.type_sub = '1' and a.IS_REFUSE='1'
	</select>
</mapper>
