<?xml version="1.0" encoding="UTF-8"?>

<!--
  ~
  ~      Copyright (c) 2018-2025, lengleng All rights reserved.
  ~
  ~  Redistribution and use in source and binary forms, with or without
  ~  modification, are permitted provided that the following conditions are met:
  ~
  ~ Redistributions of source code must retain the above copyright notice,
  ~  this list of conditions and the following disclaimer.
  ~  Redistributions in binary form must reproduce the above copyright
  ~  notice, this list of conditions and the following disclaimer in the
  ~  documentation and/or other materials provided with the distribution.
  ~  Neither the name of the yifu4cloud.com developer nor the names of its
  ~  contributors may be used to endorse or promote products derived from
  ~  this software without specific prior written permission.
  ~  Author: lengleng (wangiegie@gmail.com)
  ~
  -->

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.yifu.cloud.plus.v1.yifu.social.mapper.TDispatchInfoMapper">

	<resultMap id="tDispatchInfoMap" type="com.yifu.cloud.plus.v1.yifu.social.entity.TDispatchInfo">
		<id property="id" column="ID"/>
		<result property="empId" column="EMP_ID"/>
		<result property="empName" column="EMP_NAME"/>
		<result property="empNo" column="EMP_NO"/>
		<result property="empIdcard" column="EMP_IDCARD"/>
		<result property="empType" column="EMP_TYPE"/>
		<result property="empMobile" column="EMP_MOBILE"/>
		<result property="empNational" column="EMP_NATIONAL"/>
		<result property="status" column="STATUS"/>
		<result property="type" column="TYPE"/>
		<result property="socialId" column="SOCIAL_ID"/>
		<result property="fundId" column="FUND_ID"/>
		<result property="typeSub" column="TYPE_SUB"/>
		<result property="processInstId" column="PROCESS_INST_ID"/>
		<result property="settleDomain" column="SETTLE_DOMAIN"/>
		<result property="deleteFlag" column="DELETE_FLAG"/>
		<result property="socialReduceDate" column="SOCIAL_REDUCE_DATE"/>
		<result property="fundReduceDate" column="FUND_REDUCE_DATE"/>
		<result property="leaveDate" column="LEAVE_DATE"/>
		<result property="reduceReason" column="REDUCE_REASON"/>
		<result property="reduceRemark" column="REDUCE_REMARK"/>
		<result property="belongUnit" column="BELONG_UNIT"/>
		<result property="currentOperator" column="CURRENT_OPERATOR"/>
		<result property="dispatchIdBak" column="DISPATCH_ID_BAK"/>
		<result property="contractStart" column="CONTRACT_START"/>
		<result property="contractEnd" column="CONTRACT_END"/>
		<result property="contractTerm" column="CONTRACT_TERM"/>
		<result property="empRegisType" column="EMP_REGIS_TYPE"/>
		<result property="fileAddr" column="FILE_ADDR"/>
		<result property="post" column="POST"/>
		<result property="trustRemark" column="TRUST_REMARK"/>
		<result property="fileProvince" column="FILE_PROVINCE"/>
		<result property="fileCity" column="FILE_CITY"/>
		<result property="fileTown" column="FILE_TOWN"/>
		<result property="contractType" column="CONTRACT_TYPE"/>
		<result property="socialHandleStatus" column="SOCIAL_HANDLE_STATUS"/>
		<result property="fundHandleStatus" column="FUND_HANDLE_STATUS"/>
		<result property="handleRemark" column="HANDLE_REMARK"/>
		<result property="auditRemark" column="AUDIT_REMARK"/>
		<result property="fundHandleRemark" column="FUND_HANDLE_REMARK"/>
		<result property="socialHandleRemark" column="SOCIAL_HANDLE_REMARK"/>
		<result property="socialProvince" column="SOCIAL_PROVINCE"/>
		<result property="socialCity" column="SOCIAL_CITY"/>
		<result property="socialTown" column="SOCIAL_TOWN"/>
		<result property="fundProvince" column="FUND_PROVINCE"/>
		<result property="fundCity" column="FUND_CITY"/>
		<result property="fundTown" column="FUND_TOWN"/>
		<result property="auditUser" column="AUDIT_USER"/>
		<result property="auditUserName" column="AUDIT_USER_NAME"/>
		<result property="auditTime" column="AUDIT_TIME"/>
		<result property="workingHours" column="WORKING_HOURS"/>
		<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="organName" column="ORGAN_NAME"/>
		<result property="dispatchItem" column="DISPATCH_ITEM"/>
		<result property="settleDomainName" column="SETTLE_DOMAIN_NAME"/>
		<result property="settleDomainCode" column="SETTLE_DOMAIN_CODE"/>
		<result property="belongUnitName" column="BELONG_UNIT_NAME"/>
		<result property="dispatchCode" column="DISPATCH_CODE"/>
		<result property="againDispatchCode" column="AGAIN_DISPATCH_CODE"/>
		<result property="contractSubName" column="CONTRACT_SUB_NAME"/>
		<result property="contractId" column="CONTRACT_ID"/>
		<result property="educationName" column="EDUCATION_NAME"/>

		<result property="tryPeriod" column="TRY_PERIOD"/>
		<result property="idCardProvince" column="ID_CARD_PROVINCE"/>
		<result property="idCardCity" column="ID_CARD_CITY"/>
		<result property="idCardTown" column="ID_CARD_TOWN"/>
		<result property="idCardAddress" column="ID_CARD_ADDRESS"/>
		<result property="applyNo" column="APPLY_NO"/>
		<result property="createTime" column="CREATE_TIME"/>
		<result property="orderId" column="ORDER_ID"/>

		<result property="schoolName" column="SCHOOL_NAME"/>
		<result property="major" column="MAJOR"/>
		<result property="firstPayTime" column="FIRST_PAY_TIME"/>
		<result property="graduationTime" column="GRADUATION_TIME"/>
		<result property="autoFlag" column="AUTO_FLAG"/>


	</resultMap>
	<resultMap id="dispatchPageMap" type="com.yifu.cloud.plus.v1.yifu.social.vo.TDispatchInfoPageVo" extends="tDispatchInfoMap">
		<result property="providentHouseholdName" column="PROVIDENT_HOUSEHOLD_NAME"/>
		<result property="socialHouseholdName" column="SOCIAL_HOUSEHOLD_NAME"/>
		<result property="exportFundFlag" column="EXPORT_FUND_FLAG"/>
		<result property="exportSocialFlag" column="EXPORT_SOCIAL_FLAG"/>
		<result property="ygsHandleStatus" column="YGS_HANDLE_STATUS"/>
		<result property="ysdHandleStatus" column="YSD_HANDLE_STATUS"/>

	</resultMap>
	<!-- 派单导出vo -->
	<resultMap id="exportMap" type="com.yifu.cloud.plus.v1.yifu.social.vo.TDispatchInfoExportVo">
		<result property="empName" column="EMP_NAME"/>
		<result property="empIdcard" column="EMP_IDCARD"/>
		<result property="empType" column="EMP_TYPE"/>
		<result property="belongUnitName" column="BELONG_UNIT_NAME"/>
		<result property="settleDomainName" column="SETTLE_DOMAIN_NAME"/>
		<result property="type" column="TYPE"/>
		<result property="socialHouseholdName" column="SOCIAL_HOUSEHOLD_NAME"/>
		<result property="providentHouseholdName" column="PROVIDENT_HOUSEHOLD_NAME"/>
		<result property="socialHandleStatus" column="SOCIAL_HANDLE_STATUS"/>
		<result property="pensionHandle" column="PENSION_HANDLE"/>
		<result property="medicalHandle" column="MEDICAL_HANDLE"/>
		<result property="unemployHandle" column="UNEMPLOY_HANDLE"/>
		<result property="workInjuryHandle" column="WORK_INJURY_HANDLE"/>
		<result property="birthHandle" column="BIRTH_HANDLE"/>
		<result property="fundReduceDate" column="FUND_REDUCE_DATE"/>
		<result property="bigailmentHandle" column="BIGAILMENT_HANDLE"/>
		<result property="fundHandleStatus" column="FUND_HANDLE_STATUS"/>
		<result property="createName" column="CREATE_NAME"/>
		<result property="organName" column="ORGAN_NAME"/>
		<result property="createTime" column="CREATE_TIME"/>
		<result property="contractType" column="CONTRACT_TYPE"/>
		<result property="contractSubName" column="CONTRACT_SUB_NAME"/>
		<result property="contractTerm" column="CONTRACT_TERM"/>
		<result property="contractStart" column="CONTRACT_START"/>
		<result property="contractEnd" column="CONTRACT_END"/>
		<result property="auditUserName" column="AUDIT_USER_NAME"/>
		<result property="auditRemark" column="AUDIT_REMARK"/>
		<result property="trustRemark" column="TRUST_REMARK"/>
		<result property="leaveDate" column="LEAVE_DATE"/>
		<result property="reduceReason" column="REDUCE_REASON"/>
		<result property="status" column="STATUS"/>
		<result property="auditUser" column="AUDIT_USER"/>
		<result property="auditTime" column="AUDIT_TIME"/>
		<result property="fundHandleRemark" column="FUND_HANDLE_REMARK"/>
		<result property="socialHandleRemark" column="SOCIAL_HANDLE_REMARK"/>

		<result property="providentStart" column="PROVIDENT_START"/>
		<result property="fundAuditStatus" column="FUND_AUDIT_STATUS"/>
		<result property="fundAuditUser" column="FUND_AUDIT_USER"/>
		<result property="fundHandleUser" column="FUND_HANDLE_USER"/>
		<result property="fundHandleTime" column="FUND_HANDLE_TIME"/>

		<result property="auditStatus" column="AUDIT_STATUS"/>
		<result property="socialStartDate" column="SOCIAL_START_DATE"/>
		<result property="socialReduceDate" column="SOCIAL_REDUCE_DATE"/>
		<result property="recordBase" column="RECORD_BASE"/>
		<result property="handleUser" column="HANDLE_USER"/>
		<result property="handleTime" column="HANDLE_TIME"/>
		<result property="unitPensionCardinal" column="UNIT_PENSION_CARDINAL"/>
		<result property="unitMedicalCardinal" column="UNIT_MEDICAL_CARDINAL"/>
		<result property="unitUnemploymentCardinal" column="UNIT_UNEMPLOYMENT_CARDINAL"/>
		<result property="unitBigailmentCardinal" column="UNIT_BIGAILMENT_CARDINAL"/>
		<result property="unitWorkInjuryCardinal" column="UNIT_WORK_INJURY_CARDINAL"/>
		<result property="unitBirthCardinal" column="UNIT_BIRTH_CARDINAL"/>
		<result property="empMobile" column="EMP_MOBILE"/>

		<result property="educationName" column="EDUCATION_NAME"/>
		<result property="schoolName" column="SCHOOL_NAME"/>
		<result property="major" column="MAJOR"/>
		<result property="firstPayTime" column="FIRST_PAY_TIME"/>
		<result property="graduationTime" column="GRADUATION_TIME"/>

		<result property="autoFlag" column="AUTO_FLAG"/>

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

	<resultMap id="fundSupplementaryMap" type="com.yifu.cloud.plus.v1.yifu.social.vo.FundSupplementaryExportVo">
		<result property="providentStart" column="PROVIDENT_START"/>
		<result property="unitProvidengCardinal" column="UNIT_PROVIDENG_CARDINAL"/>
		<result property="personalProvidentCardinal" column="PERSONAL_PROVIDENT_CARDINAL"/>
		<result property="unitProvidentPer" column="UNIT_PROVIDENT_PER"/>
		<result property="personalProvidentPer" column="PERSONAL_PROVIDENT_PER"/>
		<result property="unitProvidentFee" column="UNIT_PROVIDENT_FEE"/>
		<result property="personalProvidentFee" column="PERSONAL_PROVIDENT_FEE"/>
		<result property="empName" column="EMP_NAME"/>
	</resultMap>

	<resultMap id="FundExport" type="com.yifu.cloud.plus.v1.yifu.social.vo.TDispatchFundPersionExportVo">
		<result property="num" column="NUM"/>
		<result property="personalAccount" column="PERSON_ACCOUNT"/>
		<result property="empIdcard" column="EMP_IDCARD"/>
		<result property="empName" column="EMP_NAME"/>
		<result property="depositBase" column="DEPOSIT_BASE"/>
		<result property="monthlyIncrease" column="MOTHLY_INCREASE"/>
		<result property="monthlyDecrease" column="MOTHLY_DECREASE"/>
		<result property="causeChange" column="CAUSE_CHANGE"/>
	</resultMap>

	<resultMap id="CompanyProVo" type="com.yifu.cloud.plus.v1.yifu.social.vo.CompanyProVo">
		<result property="companyPro" column="COMPANY_PRO"/>

	</resultMap>

	<resultMap id="HouseNameConfigVo" type="com.yifu.cloud.plus.v1.yifu.social.vo.HouseNameConfigVo">
		<result property="customerNo" column="CUSTOMER_NO"/>
		<result property="fundBank" column="FUND_BANK"/>
		<result property="companyPro" column="COMPANY_PRO"/>

	</resultMap>
	<resultMap id="FundSupplementaryExport" type="com.yifu.cloud.plus.v1.yifu.social.vo.TDispatchFundSupplementaryExportVo">
		<result property="num" column="NUM"/>
		<result property="personalAccount" column="PERSON_ACCOUNT"/>
		<result property="empName" column="EMP_NAME"/>
		<result property="depositBase" column="DEPOSIT_BASE"/>
		<result property="supplementaryAmount" column="SUPPLEMENTART_AMOUNT"/>
		<result property="originate" column="ORIGINATE"/>
		<result property="end" column="END"/>
	</resultMap>

	<resultMap id="FundSupplementary" type="com.yifu.cloud.plus.v1.yifu.social.vo.TDispatchFundSumVo">
		<result property="supplementaryAmountSum" column="SUPPLEMENTART_AMOUNT_SUM"/>
		<result property="number" column="NUMBER"/>
	</resultMap>

	<resultMap id="FundSumExport" type="com.yifu.cloud.plus.v1.yifu.social.vo.TDispatchFundSumExportVo">
		<result property="numOne" column="NUM_ONE"/>
		<result property="numTwo" column="NUM_TWO"/>
		<result property="monenyOne" column="MONEY_ONE"/>
		<result property="monenyTwo" column="MONEY_TWO"/>
	</resultMap>
	<resultMap id="socialDisExportMap" type="com.yifu.cloud.plus.v1.yifu.social.vo.TDispatchSocialExportVo">
		<result property="num" column="NUM"/>
		<result property="empIdcard" column="EMP_IDCARD"/>
		<result property="idcardType" column="IDCARD_TYPE"/>
		<result property="empName" column="EMP_NAME"/>
		<result property="medicalCardinal" column="MEDICAL_CARDINAL"/>
		<result property="empMobile" column="EMP_MOBILE"/>
		<result property="educationName" column="EDUCATION_NAME"/>
		<result property="disMonth" column="DIS_MONTH"/>
		<result property="changeTypeOne" column="CHANGE_TYPE_ONE"/>
		<result property="changeTypeTwo" column="CHANGE_TYPE_TWO"/>
		<result property="changeTypeThree" column="CHANGE_TYPE_THREE"/>
		<result property="changeTypeFour" column="CHANGE_TYPE_FOUR"/>
		<result property="remark" column="REMARK"/>
	</resultMap>

	<resultMap id="socialPersionDisExportMap" type="com.yifu.cloud.plus.v1.yifu.social.vo.TDispatchSocialPersionExportVo">
		<result property="num" column="num"/>
		<result property="empIdcard" column="EMP_IDCARD"/>
		<result property="empNo" column="EMP_NO"/>
		<result property="empName" column="EMP_NAME"/>
		<result property="sex" column="sex"/>
		<result property="empNational" column="EMP_NATIONAL"/>
		<result property="workDate" column="WORK_DATE"/>
		<result property="type" column="type"/>
		<result property="signleType" column="SIGNLE_TYPE"/>
		<result property="insuranceTypeOne" column="INSURANCE_TYPE_ONE"/>
		<result property="insuranceTypeTwo" column="INSURANCE_TYPE_TWO"/>
		<result property="insuranceTypeThree" column="INSURANCE_TYPE_THREE"/>
		<result property="insuranceTypeFour" column="INSURANCE_TYPE_FOUR"/>
		<result property="insuranceTypeFive" column="INSURANCE_TYPE_FIVE"/>
		<result property="persionCardinal" column="PERSION_CARDINAL"/>
		<result property="dateOne" column="DATE_ONE"/>
		<result property="contactAddress" column="CONTRACT_ADRESS"/>
		<result property="remark" column="REMARK"/>
	</resultMap>

	<resultMap id="socialPersionReduceDisExportMap" type="com.yifu.cloud.plus.v1.yifu.social.vo.TDispatchSocialPersionReduceExportVo">
		<result property="num" column="num"/>
		<result property="empIdcard" column="EMP_IDCARD"/>
		<result property="empNo" column="EMP_NO"/>
		<result property="empName" column="EMP_NAME"/>
		<result property="workDate" column="WORK_DATE"/>
		<result property="typeA" column="typeA"/>
		<result property="typeB" column="typeB"/>
		<result property="typeC" column="typeC"/>
		<result property="typeD" column="typeD"/>
		<result property="typeE" column="typeE"/>
		<result property="typeW" column="typeW"/>
		<result property="typeF" column="typeF"/>
		<result property="typeG" column="typeG"/>
		<result property="typeH" column="typeH"/>
		<result property="typeI" column="typeI"/>
		<result property="typeJ" column="typeJ"/>
		<result property="typeK" column="typeK"/>
		<result property="typeL" column="typeL"/>
		<result property="typeM" column="typeM"/>
		<result property="typeN" column="typeN"/>
		<result property="typeO" column="typeO"/>
		<result property="typeP" column="typeP"/>
		<result property="typeQ" column="typeQ"/>
		<result property="typeZ" column="typeZ"/>
		<result property="remark" column="REMARK"/>
	</resultMap>

	<!-- 派单审核导出vo -->
	<resultMap id="exportAuditMap" type="com.yifu.cloud.plus.v1.yifu.social.vo.TDispatchAuditExportVo">
		<result property="empName" column="EMP_NAME"/>
		<result property="empIdcard" column="EMP_IDCARD"/>
		<result property="belongUnitName" column="BELONG_UNIT_NAME"/>
		<result property="settleDomainName" column="SETTLE_DOMAIN_NAME"/>
		<result property="empMobile" column="EMP_MOBILE"/>
		<result property="idCardAddress" column="ID_CARD_ADDRESS"/>

		<result property="type" column="TYPE"/>
		<result property="socialHouseholdName" column="SOCIAL_HOUSEHOLD_NAME"/>
		<result property="providentHouseholdName" column="PROVIDENT_HOUSEHOLD_NAME"/>
		<result property="createName" column="CREATE_NAME"/>
		<result property="createTime" column="CREATE_TIME"/>
		<result property="contractStart" column="CONTRACT_START"/>
		<result property="contractEnd" column="CONTRACT_END"/>
		<result property="trustRemark" column="TRUST_REMARK"/>
		<result property="contractName" column="CONTRACT_NAME"/>
		<result property="workingHours" column="WORKING_HOURS"/>
		<result property="socialReduceDate" column="SOCIAL_REDUCE_DATE"/>
		<result property="fundReduceDate" column="FUND_REDUCE_DATE"/>
		<result property="leaveDate" column="LEAVE_DATE"/>
		<result property="reduceReason" column="REDUCE_REASON"/>

		<result property="paymentType" column="PAYMENT_TYPE"/>
		<result property="recordBase" column="RECORD_BASE"/>
		<result property="unitPensionCardinal" column="UNIT_PENSION_CARDINAL"/>
		<result property="unitMedicalCardinal" column="UNIT_MEDICAL_CARDINAL"/>
		<result property="unitUnemploymentCardinal" column="UNIT_UNEMPLOYMENT_CARDINAL"/>
		<result property="unitWorkInjuryCardinal" column="UNIT_WORK_INJURY_CARDINAL"/>
		<result property="unitBirthCardinal" column="UNIT_BIRTH_CARDINAL"/>
		<result property="personalPensionCardinal" column="PERSONAL_PENSION_CARDINAL"/>
		<result property="personalMedicalCardinal" column="PERSONAL_MEDICAL_CARDINAL"/>
		<result property="personalUnemploymentCardinal" column="PERSONAL_UNEMPLOYMENT_CARDINAL"/>
		<result property="personalBigailmentMoney" column="PERSONAL_BIGAILMENT_MONEY"/>
		<result property="unitBigailmentMoney" column="UNIT_BIGAILMENT_MONEY"/>
		<result property="socialStartDate" column="SOCIAL_START_DATE"/>

		<result property="providentStart" column="PROVIDENT_START"/>
		<result property="unitProvidengCardinal" column="UNIT_PROVIDENG_CARDINAL"/>
		<result property="personalProvidentCardinal" column="PERSONAL_PROVIDENT_CARDINAL"/>
		<result property="unitProvidentPer" column="UNIT_PROVIDENT_PER"/>
		<result property="personalProvidentPer" column="PERSONAL_PROVIDENT_PER"/>

		<result property="educationName" column="EDUCATION_NAME"/>
		<result property="schoolName" column="SCHOOL_NAME"/>
		<result property="major" column="MAJOR"/>
		<result property="firstPayTime" column="FIRST_PAY_TIME"/>
		<result property="graduationTime" column="GRADUATION_TIME"/>
	</resultMap>

	<sql id="Base_Column_List">
		a.ID,
		a.EMP_ID,
		a.EMP_NAME,
		a.EMP_NO,
		a.EMP_IDCARD,
		a.EMP_TYPE,
		a.EMP_MOBILE,
		a.EMP_NATIONAL,
		a.STATUS,
		a.TYPE,
		a.SOCIAL_ID,
		a.FUND_ID,
		a.TYPE_SUB,
		a.PROCESS_INST_ID,
		a.SETTLE_DOMAIN,
		a.DELETE_FLAG,
		a.SOCIAL_REDUCE_DATE,
		a.FUND_REDUCE_DATE,
		a.LEAVE_DATE,
		a.REDUCE_REASON,
		a.REDUCE_REMARK,
		a.BELONG_UNIT,
		a.CURRENT_OPERATOR,
		a.DISPATCH_ID_BAK,
		a.CONTRACT_START,
		a.CONTRACT_END,
		a.CONTRACT_TERM,
		a.EMP_REGIS_TYPE,
		a.ENJOIN_DATE,
		a.FILE_ADDR,
		a.POST,
		a.TRUST_REMARK,
		a.FILE_PROVINCE,
		a.FILE_CITY,
		a.FILE_TOWN,
		a.CONTRACT_TYPE,
		a.SOCIAL_HANDLE_STATUS,
		a.FUND_HANDLE_STATUS,
		a.HANDLE_REMARK,
		a.AUDIT_REMARK,
		a.FUND_HANDLE_REMARK,
		a.SOCIAL_HANDLE_REMARK,
		a.SOCIAL_PROVINCE,
		a.SOCIAL_CITY,
		a.SOCIAL_TOWN,
		a.FUND_PROVINCE,
		a.FUND_CITY,
		a.FUND_TOWN,
		a.AUDIT_USER,
		a.AUDIT_USER_NAME,
		a.AUDIT_TIME,
		a.WORKING_HOURS,
		a.CREATE_BY,
		a.UPDATE_BY,
		a.CREATE_NAME,
		a.CREATE_TIME,
		a.UPDATE_TIME,
		a.ORGAN_NAME,
		a.DISPATCH_ITEM,
		a.SETTLE_DOMAIN_NAME,
		a.BELONG_UNIT_NAME,
		a.DISPATCH_CODE,
		a.AGAIN_DISPATCH_CODE,
		a.SETTLE_DOMAIN_CODE,
		a.CONTRACT_SUB_NAME,
		a.CONTRACT_ID,
		a.EDUCATION_NAME,
		a.TRY_PERIOD,
		a.ID_CARD_PROVINCE,
		a.ID_CARD_CITY,
		a.ID_CARD_TOWN,
		a.ID_CARD_ADDRESS,
		a.APPLY_NO,
		a.ORDER_ID,
		a.SCHOOL_NAME,
		a.MAJOR,
		a.FIRST_PAY_TIME,
		a.GRADUATION_TIME,a.AUTO_FLAG
	</sql>
	<sql id="tDispatchInfo_where">
		<if test="tDispatchInfo != null">
			<if test="tDispatchInfo.dispatchItems != null and tDispatchInfo.dispatchItems.size >0">
				AND
				<foreach item="item" index="index" collection="tDispatchInfo.dispatchItems" open="(" separator="or" close=")">
					INSTR(a.DISPATCH_ITEM,#{item})
				</foreach>
			</if>
			<if test="tDispatchInfo.idList != null and tDispatchInfo.idList.size >0">
				AND a.ID in
				<foreach item="idStr" index="index" collection="tDispatchInfo.idList" open="(" separator="," close=")">
					#{idStr}
				</foreach>
			</if>
			<if test="tDispatchInfo.empTypeList != null and tDispatchInfo.empTypeList.length>0">
				AND a.EMP_TYPE IN
				<foreach collection="tDispatchInfo.empTypeList" item="item" index="index" open="(" separator="," close=")">
					#{item}
				</foreach>
			</if>
			<if test="tDispatchInfo.autoFlag != null and tDispatchInfo.autoFlag.trim() != ''">
				AND a.AUTO_FLAG = #{tDispatchInfo.autoFlag}
			</if>
			<if test="tDispatchInfo.id != null and tDispatchInfo.id.trim() != ''">
				AND a.ID = #{tDispatchInfo.id}
			</if>
			<if test="tDispatchInfo.orderId != null and tDispatchInfo.orderId.trim() != ''">
				AND a.ORDER_ID = #{tDispatchInfo.orderId}
			</if>
			<if test="tDispatchInfo.empId != null and tDispatchInfo.empId.trim() != ''">
				AND a.EMP_ID = #{tDispatchInfo.empId}
			</if>
			<if test="tDispatchInfo.empName != null and tDispatchInfo.empName.trim() != ''">
				AND a.EMP_NAME like concat('%',#{tDispatchInfo.empName},'%')
			</if>
			<if test="tDispatchInfo.empNo != null and tDispatchInfo.empNo.trim() != ''">
				AND a.EMP_NO = #{tDispatchInfo.empNo}
			</if>
			<if test="tDispatchInfo.empIdcard != null and tDispatchInfo.empIdcard.trim() != ''">
				AND a.EMP_IDCARD like concat('%',#{tDispatchInfo.empIdcard},'%')
			</if>
			<if test="tDispatchInfo.empType != null and tDispatchInfo.empType.trim() != ''">
				AND a.EMP_TYPE in #{tDispatchInfo.empType}
			</if>
			<if test="tDispatchInfo.empMobile != null and tDispatchInfo.empMobile.trim() != ''">
				AND a.EMP_MOBILE = #{tDispatchInfo.empMobile}
			</if>
			<if test="tDispatchInfo.empNational != null and tDispatchInfo.empNational.trim() != ''">
				AND a.EMP_NATIONAL = #{tDispatchInfo.empNational}
			</if>
			<if test="tDispatchInfo.status != null and tDispatchInfo.status.trim() != ''">
				AND a.STATUS = #{tDispatchInfo.status}
			</if>
			<if test="tDispatchInfo.type != null and tDispatchInfo.type.trim() != ''">
				AND a.TYPE = #{tDispatchInfo.type}
			</if>
			<if test="tDispatchInfo.socialId != null and tDispatchInfo.socialId.trim() != ''">
				AND a.SOCIAL_ID = #{tDispatchInfo.socialId}
			</if>
			<if test="tDispatchInfo.fundId != null and tDispatchInfo.fundId.trim() != ''">
				AND a.FUND_ID = #{tDispatchInfo.fundId}
			</if>
			<if test="tDispatchInfo.typeSub != null and tDispatchInfo.typeSub.trim() != ''">
				AND a.TYPE_SUB = #{tDispatchInfo.typeSub}
			</if>
			<if test="tDispatchInfo.processInstId != null and tDispatchInfo.processInstId.trim() != ''">
				AND a.PROCESS_INST_ID = #{tDispatchInfo.processInstId}
			</if>
			<if test="tDispatchInfo.settleDomain != null and tDispatchInfo.settleDomain.trim() != ''">
				AND a.SETTLE_DOMAIN = #{tDispatchInfo.settleDomain}
			</if>
			<if test="tDispatchInfo.settleDomainName != null and tDispatchInfo.settleDomainName.trim() != ''">
				AND a.SETTLE_DOMAIN_NAME like CONCAT(#{tDispatchInfo.settleDomainName}, '%')
			</if>
			<if test="tDispatchInfo.settleDomainCode != null and tDispatchInfo.settleDomainCode.trim() != ''">
				AND a.SETTLE_DOMAIN_CODE = #{tDispatchInfo.settleDomainCode}
			</if>

			<if test="tDispatchInfo.deleteFlag != null and tDispatchInfo.deleteFlag.trim() != ''">
				AND a.DELETE_FLAG = #{tDispatchInfo.deleteFlag}
			</if>
			<if test="tDispatchInfo.socialReduceDate != null">
				AND a.SOCIAL_REDUCE_DATE = #{tDispatchInfo.socialReduceDate}
			</if>
			<if test="tDispatchInfo.fundReduceDate != null">
				AND a.FUND_REDUCE_DATE = #{tDispatchInfo.fundReduceDate}
			</if>
			<if test="tDispatchInfo.leaveDate != null">
				AND a.LEAVE_DATE = #{tDispatchInfo.leaveDate}
			</if>
			<if test="tDispatchInfo.reduceReason != null and tDispatchInfo.reduceReason.trim() != ''">
				AND a.REDUCE_REASON = #{tDispatchInfo.reduceReason}
			</if>
			<if test="tDispatchInfo.reduceRemark != null and tDispatchInfo.reduceRemark.trim() != ''">
				AND a.REDUCE_REMARK = #{tDispatchInfo.reduceRemark}
			</if>
			<if test="tDispatchInfo.belongUnit != null and tDispatchInfo.belongUnit.trim() != ''">
				AND a.BELONG_UNIT = #{tDispatchInfo.belongUnit}
			</if>
			<if test="tDispatchInfo.currentOperator != null and tDispatchInfo.currentOperator.trim() != ''">
				AND a.CURRENT_OPERATOR = #{tDispatchInfo.currentOperator}
			</if>
			<if test="tDispatchInfo.dispatchIdBak != null and tDispatchInfo.dispatchIdBak.trim() != ''">
				AND a.DISPATCH_ID_BAK = #{tDispatchInfo.dispatchIdBak}
			</if>
			<if test="tDispatchInfo.contractStart != null">
				AND a.CONTRACT_START = #{tDispatchInfo.contractStart}
			</if>
			<if test="tDispatchInfo.contractEnd != null">
				AND a.CONTRACT_END = #{tDispatchInfo.contractEnd}
			</if>
			<if test="tDispatchInfo.contractTerm != null">
				AND a.CONTRACT_TERM = #{tDispatchInfo.contractTerm}
			</if>
			<if test="tDispatchInfo.empRegisType != null and tDispatchInfo.empRegisType.trim() != ''">
				AND a.EMP_REGIS_TYPE = #{tDispatchInfo.empRegisType}
			</if>
			<if test="tDispatchInfo.fileAddr != null and tDispatchInfo.fileAddr.trim() != ''">
				AND a.FILE_ADDR = #{tDispatchInfo.fileAddr}
			</if>
			<if test="tDispatchInfo.post != null and tDispatchInfo.post.trim() != ''">
				AND a.POST = #{tDispatchInfo.post}
			</if>
			<if test="tDispatchInfo.trustRemark != null and tDispatchInfo.trustRemark.trim() != ''">
				AND a.TRUST_REMARK = #{tDispatchInfo.trustRemark}
			</if>
			<if test="tDispatchInfo.fileProvince != null">
				AND a.FILE_PROVINCE = #{tDispatchInfo.fileProvince}
			</if>
			<if test="tDispatchInfo.fileCity != null">
				AND a.FILE_CITY = #{tDispatchInfo.fileCity}
			</if>
			<if test="tDispatchInfo.fileTown != null">
				AND a.FILE_TOWN = #{tDispatchInfo.fileTown}
			</if>
			<if test="tDispatchInfo.contractType != null and tDispatchInfo.contractType.trim() != ''">
				AND a.CONTRACT_TYPE = #{tDispatchInfo.contractType}
			</if>
			<if test="tDispatchInfo.socialHandleStatus != null and tDispatchInfo.socialHandleStatus.trim() != ''">
				AND a.SOCIAL_HANDLE_STATUS = #{tDispatchInfo.socialHandleStatus}
			</if>
			<if test="tDispatchInfo.fundHandleStatus != null and tDispatchInfo.fundHandleStatus.trim() != ''">
				AND a.FUND_HANDLE_STATUS = #{tDispatchInfo.fundHandleStatus}
			</if>
			<if test="tDispatchInfo.handleRemark != null and tDispatchInfo.handleRemark.trim() != ''">
				AND a.HANDLE_REMARK = #{tDispatchInfo.handleRemark}
			</if>
			<if test="tDispatchInfo.auditRemark != null and tDispatchInfo.auditRemark.trim() != ''">
				AND a.AUDIT_REMARK = #{tDispatchInfo.auditRemark}
			</if>
			<if test="tDispatchInfo.fundHandleRemark != null and tDispatchInfo.fundHandleRemark.trim() != ''">
				AND a.FUND_HANDLE_REMARK = #{tDispatchInfo.fundHandleRemark}
			</if>
			<if test="tDispatchInfo.socialHandleRemark != null and tDispatchInfo.socialHandleRemark.trim() != ''">
				AND a.SOCIAL_HANDLE_REMARK = #{tDispatchInfo.socialHandleRemark}
			</if>
			<if test="tDispatchInfo.socialProvince != null and tDispatchInfo.socialProvince.trim() != ''">
				AND a.SOCIAL_PROVINCE = #{tDispatchInfo.socialProvince}
			</if>
			<if test="tDispatchInfo.socialCity != null and tDispatchInfo.socialCity.trim() != ''">
				AND a.SOCIAL_CITY = #{tDispatchInfo.socialCity}
			</if>
			<if test="tDispatchInfo.socialTown != null and tDispatchInfo.socialTown.trim() != ''">
				AND a.SOCIAL_TOWN = #{tDispatchInfo.socialTown}
			</if>
			<if test="tDispatchInfo.fundProvince != null and tDispatchInfo.fundProvince.trim() != ''">
				AND a.FUND_PROVINCE = #{tDispatchInfo.fundProvince}
			</if>
			<if test="tDispatchInfo.fundCity != null and tDispatchInfo.fundCity.trim() != ''">
				AND a.FUND_CITY = #{tDispatchInfo.fundCity}
			</if>
			<if test="tDispatchInfo.fundTown != null and tDispatchInfo.fundTown.trim() != ''">
				AND a.FUND_TOWN = #{tDispatchInfo.fundTown}
			</if>
			<if test="tDispatchInfo.auditUser != null and tDispatchInfo.auditUser.trim() != ''">
				AND a.AUDIT_USER = #{tDispatchInfo.auditUser}
			</if>
			<if test="tDispatchInfo.auditUserName != null and tDispatchInfo.auditUserName.trim() != ''">
				AND a.AUDIT_USER_NAME like concat('%',#{tDispatchInfo.auditUserName},'%')
			</if>
			<if test="tDispatchInfo.auditTime != null">
				AND a.AUDIT_TIME = #{tDispatchInfo.auditTime}
			</if>
			<if test="tDispatchInfo.auditTimeStart != null">
				AND a.AUDIT_TIME <![CDATA[>=]]> #{tDispatchInfo.auditTimeStart}
			</if>
			<if test="tDispatchInfo.auditTimeEnd != null">
				AND a.AUDIT_TIME <![CDATA[<=]]> #{tDispatchInfo.auditTimeEnd}
			</if>

			<if test="tDispatchInfo.workingHours != null and tDispatchInfo.workingHours.trim() != ''">
				AND a.WORKING_HOURS = #{tDispatchInfo.workingHours}
			</if>
			<if test="tDispatchInfo.updateBy != null and tDispatchInfo.updateBy.trim() != ''">
				AND a.UPDATE_BY = #{tDispatchInfo.updateBy}
			</if>
			<if test="tDispatchInfo.createName != null and tDispatchInfo.createName.trim() != ''">
				AND a.CREATE_NAME = #{tDispatchInfo.createName}
			</if>
			<if test="tDispatchInfo.updateTime != null">
				AND a.UPDATE_TIME = #{tDispatchInfo.updateTime}
			</if>
			<if test="tDispatchInfo.organName != null and tDispatchInfo.organName.trim() != ''">
				AND a.ORGAN_NAME = #{tDispatchInfo.organName}
			</if>
			<if test="tDispatchInfo.dispatchItem != null and tDispatchInfo.dispatchItem.trim() != ''">
				AND a.DISPATCH_ITEM = #{tDispatchInfo.dispatchItem}
			</if>
			<if test="tDispatchInfo.belongUnitName != null and tDispatchInfo.belongUnitName.trim() != ''">
				AND a.BELONG_UNIT_NAME = #{tDispatchInfo.belongUnitName}
			</if>

			<if test="tDispatchInfo.socialHouseholdName != null and tDispatchInfo.socialHouseholdName.trim() != ''">
				AND a.SOCIAL_HOUSEHOLD_NAME like CONCAT(CONCAT(#{tDispatchInfo.socialHouseholdName}), '%')
			</if>
			<if test="tDispatchInfo.providentHouseholdName != null and tDispatchInfo.providentHouseholdName.trim() != ''">
				AND a.PROVIDENT_HOUSEHOLD_NAME like CONCAT(CONCAT(#{tDispatchInfo.providentHouseholdName}), '%')
			</if>
			<if test="tDispatchInfo.createTimeStart != null">
				AND a.CREATE_TIME <![CDATA[ >= ]]> #{tDispatchInfo.createTimeStart}
			</if>
			<if test="tDispatchInfo.createTimeEnd != null">
				AND a.CREATE_TIME <![CDATA[ <= ]]> #{tDispatchInfo.createTimeEnd}
			</if>
			<if test="tDispatchInfo.authSql != null and tDispatchInfo.authSql.trim() != ''">
				${tDispatchInfo.authSql}
			</if>
			<if test="tDispatchInfo.noCreateUser != null and tDispatchInfo.noCreateUser.trim() != ''">
				AND a.CREATE_BY != #{tDispatchInfo.noCreateUser}
			</if>
		</if>
	</sql>

	<sql id="tDispatchInfoAudit_where">
		<if test="tDispatchInfo != null">
			<if test="tDispatchInfo.dispatchItems != null and tDispatchInfo.dispatchItems.size >0">
				AND
				<foreach item="item" index="index" collection="tDispatchInfo.dispatchItems" open="(" separator="or" close=")">
					INSTR(a.DISPATCH_ITEM,#{item})
				</foreach>
			</if>
			<if test="tDispatchInfo.empTypeList != null and tDispatchInfo.empTypeList.length>0">
				AND a.EMP_TYPE IN
				<foreach collection="tDispatchInfo.empTypeList" item="item" index="index" open="(" separator="," close=")">
					#{item}
				</foreach>

			</if>
			<if test="tDispatchInfo.idList != null and tDispatchInfo.idList.size >0">
				AND a.ID in
				<foreach item="idStr" index="index" collection="tDispatchInfo.idList" open="(" separator="," close=")">
					#{idStr}
				</foreach>
			</if>
			<if test="tDispatchInfo.type != null and tDispatchInfo.type.trim() != ''">
				AND a.TYPE = #{tDispatchInfo.type}
			</if>
			<if test="tDispatchInfo.empName != null and tDispatchInfo.empName.trim() != ''">
				AND a.EMP_NAME like concat('%',#{tDispatchInfo.empName},'%')
			</if>
			<if test="tDispatchInfo.empIdcard != null and tDispatchInfo.empIdcard.trim() != ''">
				AND a.EMP_IDCARD like concat('%',#{tDispatchInfo.empIdcard},'%')
			</if>
			<if test="tDispatchInfo.settleDomainName != null and tDispatchInfo.settleDomainName.trim() != ''">
				AND a.SETTLE_DOMAIN_NAME like CONCAT(#{tDispatchInfo.settleDomainName}, '%')
			</if>
			<if test="tDispatchInfo.settleDomainCode != null and tDispatchInfo.settleDomainCode.trim() != ''">
				AND a.SETTLE_DOMAIN_CODE = #{tDispatchInfo.settleDomainCode}
			</if>
			<if test="tDispatchInfo.socialProvince != null and tDispatchInfo.socialProvince.trim() != ''">
				AND a.SOCIAL_PROVINCE = #{tDispatchInfo.socialProvince}
			</if>
			<if test="tDispatchInfo.socialCity != null and tDispatchInfo.socialCity.trim() != ''">
				AND a.SOCIAL_CITY = #{tDispatchInfo.socialCity}
			</if>
			<if test="tDispatchInfo.socialTown != null and tDispatchInfo.socialTown.trim() != ''">
				AND a.SOCIAL_TOWN = #{tDispatchInfo.socialTown}
			</if>
			<if test="tDispatchInfo.fundProvince != null and tDispatchInfo.fundProvince.trim() != ''">
				AND a.FUND_PROVINCE = #{tDispatchInfo.fundProvince}
			</if>
			<if test="tDispatchInfo.fundCity != null and tDispatchInfo.fundCity.trim() != ''">
				AND a.FUND_CITY = #{tDispatchInfo.fundCity}
			</if>
			<if test="tDispatchInfo.fundTown != null and tDispatchInfo.fundTown.trim() != ''">
				AND a.FUND_TOWN = #{tDispatchInfo.fundTown}
			</if>
			<if test="tDispatchInfo.createName != null and tDispatchInfo.createName.trim() != ''">
				AND a.CREATE_NAME = #{tDispatchInfo.createName}
			</if>
			<if test="tDispatchInfo.socialHouseholdName != null and tDispatchInfo.socialHouseholdName.trim() != ''">
				AND a.SOCIAL_HOUSEHOLD_NAME like CONCAT(#{tDispatchInfo.socialHouseholdName}, '%')
			</if>
			<if test="tDispatchInfo.providentHouseholdName != null and tDispatchInfo.providentHouseholdName.trim() != ''">
				AND a.PROVIDENT_HOUSEHOLD_NAME like CONCAT(#{tDispatchInfo.providentHouseholdName}, '%')
			</if>
			<if test="tDispatchInfo.createTimeStart != null">
				AND a.CREATE_TIME <![CDATA[ >= ]]> #{tDispatchInfo.createTimeStart}
			</if>
			<if test="tDispatchInfo.createTimeEnd != null">
				AND a.CREATE_TIME <![CDATA[ <= ]]> #{tDispatchInfo.createTimeEnd}
			</if>
			<if test="tDispatchInfo.authSql != null and tDispatchInfo.authSql.trim() != ''">
				${tDispatchInfo.authSql}
			</if>
			<if test="tDispatchInfo.noCreateUser != null and tDispatchInfo.noCreateUser.trim() != ''">
				AND a.CREATE_BY != #{tDispatchInfo.noCreateUser}
			</if>
		</if>
	</sql>

	<!--tDispatchInfo简单分页查询-->
	<select id="getTDispatchInfoPage" resultMap="dispatchPageMap">
		SELECT
		<include refid="Base_Column_List"/>,
		a.SOCIAL_HOUSEHOLD_NAME,
		a.PROVIDENT_HOUSEHOLD_NAME
		FROM t_dispatch_info a
		<where>
			1=1
			<include refid="tDispatchInfo_where"/>
		</where>
		order by a.APPLY_NO desc
	</select>

	<!--tDispatchInfo简单分页查询-->
	<select id="getTDispatchInfoPageAudit" resultMap="dispatchPageMap">
		SELECT
		<include refid="Base_Column_List"/>,
		a.SOCIAL_HOUSEHOLD_NAME,
		a.PROVIDENT_HOUSEHOLD_NAME
		FROM t_dispatch_info a
		<where>
			1=1
			<include refid="tDispatchInfo_where"/>
		</where>

		order by a.APPLY_NO desc
	</select>

	<!-- 派单导出 -->
	<select id="exportDispatch" resultMap="exportMap">
		select
		a.EMP_NAME,
		a.EMP_IDCARD,
		a.EMP_TYPE,
		a.BELONG_UNIT_NAME,
		a.SETTLE_DOMAIN_NAME,
		case when a.TYPE = 0 then "派增"
		when a.TYPE = 1 then "派减"
		else null  end  as  "TYPE" ,
		a.SOCIAL_HOUSEHOLD_NAME,
		case when a.SOCIAL_HANDLE_STATUS = 0 then "待办理"
		when a.SOCIAL_HANDLE_STATUS = 1 then "办理成功"
		when a.SOCIAL_HANDLE_STATUS = 2 then "办理失败"
		when a.SOCIAL_HANDLE_STATUS = 3 then "部分办理失败"
		when a.SOCIAL_HANDLE_STATUS = 4 then "办理中"
		else null  end as "SOCIAL_HANDLE_STATUS",
		IF(a.TYPE='0',
		case when s.PENSION_HANDLE = 0 then "派增待办理"
		when s.PENSION_HANDLE = 1 then "派增办理成功"
		when s.PENSION_HANDLE = 2 then "派增办理失败"
		when s.PENSION_HANDLE = 3 then "已派减"
		when s.PENSION_HANDLE = 4 then "派减失败"
		when s.PENSION_HANDLE = 5 then "派减待办理"
		else null  end,
		IF(a.TYPE='1',
		case when if(ifnull(h.id,0) != '0',h.PENSION_HANDLE,s.PENSION_HANDLE) = 0 then "派增待办理"
		when if(ifnull(h.id,0) != '0',h.PENSION_HANDLE,s.PENSION_HANDLE) = 1 then "派增办理成功"
		when if(ifnull(h.id,0) != '0',h.PENSION_HANDLE,s.PENSION_HANDLE) = 2 then "派增办理失败"
		when if(ifnull(h.id,0) != '0',h.PENSION_HANDLE,s.PENSION_HANDLE) = 3 then "已派减"
		when if(ifnull(h.id,0) != '0',h.PENSION_HANDLE,s.PENSION_HANDLE) = 4 then "派减失败"
		when if(ifnull(h.id,0) != '0',h.PENSION_HANDLE,s.PENSION_HANDLE) = 5 then "派减待办理"
		else null  end,""
		))
		as PENSION_HANDLE,
		IF(a.TYPE='0',
		case when s.MEDICAL_HANDLE = 0 then "派增待办理"
		when s.MEDICAL_HANDLE = 1 then "派增办理成功"
		when s.MEDICAL_HANDLE = 2 then "派增办理失败"
		when s.MEDICAL_HANDLE = 3 then "已派减"
		when s.MEDICAL_HANDLE = 4 then "派减失败"
		when s.MEDICAL_HANDLE = 5 then "派减待办理"
		else null  end,
		IF(a.TYPE='1',
		case when if(ifnull(h.id,0) != '0',h.MEDICAL_HANDLE,s.MEDICAL_HANDLE) = 0 then "派增待办理"
		when if(ifnull(h.id,0) != '0',h.MEDICAL_HANDLE,s.MEDICAL_HANDLE) = 1 then "派增办理成功"
		when if(ifnull(h.id,0) != '0',h.MEDICAL_HANDLE,s.MEDICAL_HANDLE) = 2 then "派增办理失败"
		when if(ifnull(h.id,0) != '0',h.MEDICAL_HANDLE,s.MEDICAL_HANDLE) = 3 then "已派减"
		when if(ifnull(h.id,0) != '0',h.MEDICAL_HANDLE,s.MEDICAL_HANDLE) = 4 then "派减失败"
		when if(ifnull(h.id,0) != '0',h.MEDICAL_HANDLE,s.MEDICAL_HANDLE) = 5 then "派减待办理"
		else null  end,""
		))
		as MEDICAL_HANDLE,
		IF(a.TYPE='0',
		case when s.UNEMPLOY_HANDLE = 0 then "派增待办理"
		when s.UNEMPLOY_HANDLE = 1 then "派增办理成功"
		when s.UNEMPLOY_HANDLE = 2 then "派增办理失败"
		when s.UNEMPLOY_HANDLE = 3 then "已派减"
		when s.UNEMPLOY_HANDLE = 4 then "派减失败"
		when s.UNEMPLOY_HANDLE = 5 then "派减待办理"
		else null  end,
		IF(a.TYPE='1',
		case when if(ifnull(h.id,0) != '0',h.UNEMPLOY_HANDLE,s.UNEMPLOY_HANDLE) = 0 then "派增待办理"
		when if(ifnull(h.id,0) != '0',h.UNEMPLOY_HANDLE,s.UNEMPLOY_HANDLE) = 1 then "派增办理成功"
		when if(ifnull(h.id,0) != '0',h.UNEMPLOY_HANDLE,s.UNEMPLOY_HANDLE) = 2 then "派增办理失败"
		when if(ifnull(h.id,0) != '0',h.UNEMPLOY_HANDLE,s.UNEMPLOY_HANDLE) = 3 then "已派减"
		when if(ifnull(h.id,0) != '0',h.UNEMPLOY_HANDLE,s.UNEMPLOY_HANDLE) = 4 then "派减失败"
		when if(ifnull(h.id,0) != '0',h.UNEMPLOY_HANDLE,s.UNEMPLOY_HANDLE) = 5 then "派减待办理"
		else null  end,""
		))
		as UNEMPLOY_HANDLE,
		IF(a.TYPE='0',
		case when s.WORK_INJURY_HANDLE = 0 then "派增待办理"
		when s.WORK_INJURY_HANDLE = 1 then "派增办理成功"
		when s.WORK_INJURY_HANDLE = 2 then "派增办理失败"
		when s.WORK_INJURY_HANDLE = 3 then "已派减"
		when s.WORK_INJURY_HANDLE = 4 then "派减失败"
		when s.WORK_INJURY_HANDLE = 5 then "派减待办理"
		else null  end,
		IF(a.TYPE='1',
		case when if(ifnull(h.id,0) != '0',h.WORK_INJURY_HANDLE,s.WORK_INJURY_HANDLE) = 0 then "派增待办理"
		when if(ifnull(h.id,0) != '0',h.WORK_INJURY_HANDLE,s.WORK_INJURY_HANDLE) = 1 then "派增办理成功"
		when if(ifnull(h.id,0) != '0',h.WORK_INJURY_HANDLE,s.WORK_INJURY_HANDLE) = 2 then "派增办理失败"
		when if(ifnull(h.id,0) != '0',h.WORK_INJURY_HANDLE,s.WORK_INJURY_HANDLE) = 3 then "已派减"
		when if(ifnull(h.id,0) != '0',h.WORK_INJURY_HANDLE,s.WORK_INJURY_HANDLE) = 4 then "派减失败"
		when if(ifnull(h.id,0) != '0',h.WORK_INJURY_HANDLE,s.WORK_INJURY_HANDLE) = 5 then "派减待办理"
		else null  end,""
		))
		as WORK_INJURY_HANDLE,
		IF(a.TYPE='0',
		case when s.BIRTH_HANDLE = 0 then "派增待办理"
		when s.BIRTH_HANDLE = 1 then "派增办理成功"
		when s.BIRTH_HANDLE = 2 then "派增办理失败"
		when s.BIRTH_HANDLE = 3 then "已派减"
		when s.BIRTH_HANDLE = 4 then "派减失败"
		when s.BIRTH_HANDLE = 5 then "派减待办理"
		else null  end,
		IF(a.TYPE='1',
		case when if(ifnull(h.id,0) != '0',h.BIRTH_HANDLE,s.BIRTH_HANDLE) = 0 then "派增待办理"
		when if(ifnull(h.id,0) != '0',h.BIRTH_HANDLE,s.BIRTH_HANDLE) = 1 then "派增办理成功"
		when if(ifnull(h.id,0) != '0',h.BIRTH_HANDLE,s.BIRTH_HANDLE) = 2 then "派增办理失败"
		when if(ifnull(h.id,0) != '0',h.BIRTH_HANDLE,s.BIRTH_HANDLE) = 3 then "已派减"
		when if(ifnull(h.id,0) != '0',h.BIRTH_HANDLE,s.BIRTH_HANDLE) = 4 then "派减失败"
		when if(ifnull(h.id,0) != '0',h.BIRTH_HANDLE,s.BIRTH_HANDLE) = 5 then "派减待办理"
		else null  end,""
		))
		as BIRTH_HANDLE,
		IF(a.TYPE='0',
		case when s.BIGAILMENT_HANDLE = 0 then "派增待办理"
		when s.BIGAILMENT_HANDLE = 1 then "派增办理成功"
		when s.BIGAILMENT_HANDLE = 2 then "派增办理失败"
		when s.BIGAILMENT_HANDLE = 3 then "已派减"
		when s.BIGAILMENT_HANDLE = 4 then "派减失败"
		when s.BIGAILMENT_HANDLE = 5 then "派减待办理"
		else null  end,
		IF(a.TYPE='1',
		case when if(ifnull(h.id,0) != '0',h.BIGAILMENT_HANDLE,s.BIGAILMENT_HANDLE) = 0 then "派增待办理"
		when if(ifnull(h.id,0) != '0',h.BIGAILMENT_HANDLE,s.BIGAILMENT_HANDLE) = 1 then "派增办理成功"
		when if(ifnull(h.id,0) != '0',h.BIGAILMENT_HANDLE,s.BIGAILMENT_HANDLE) = 2 then "派增办理失败"
		when if(ifnull(h.id,0) != '0',h.BIGAILMENT_HANDLE,s.BIGAILMENT_HANDLE) = 3 then "已派减"
		when if(ifnull(h.id,0) != '0',h.BIGAILMENT_HANDLE,s.BIGAILMENT_HANDLE) = 4 then "派减失败"
		when if(ifnull(h.id,0) != '0',h.BIGAILMENT_HANDLE,s.BIGAILMENT_HANDLE) = 5 then "派减待办理"
		else null  end,""
		))
		as BIGAILMENT_HANDLE,
		case when a.FUND_HANDLE_STATUS = 0 then "派增待办理"
		when a.FUND_HANDLE_STATUS = 1 then "派增办理成功"
		when a.FUND_HANDLE_STATUS = 2 then "派增办理失败"
		when a.FUND_HANDLE_STATUS = 3 then "已派减"
		else null  end  as FUND_HANDLE_STATUS,
		a.CREATE_NAME,
		a.ORGAN_NAME,
		a.CREATE_TIME,
		a.CONTRACT_NAME AS CONTRACT_TYPE,
		a.CONTRACT_SUB_NAME,
		a.CONTRACT_TERM,
		a.CONTRACT_START,
		a.CONTRACT_END,
		a.AUDIT_REMARK,
		a.TRUST_REMARK,
		a.LEAVE_DATE,
		a.REDUCE_REASON,
		case when a.STATUS = 0 then "未提交"
		when a.STATUS = 1 then "待审核"
		when a.STATUS = 2 then "审核通过"
		when a.STATUS = 3 then "审核不通过"
		when a.STATUS = 4 then "已办结"
		when a.STATUS = 5 then "待SSC审核"
		else null  end as STATUS,
		a.AUDIT_USER_NAME as AUDIT_USER,
		a.AUDIT_TIME,
		a.FUND_HANDLE_REMARK,
		a.SOCIAL_HANDLE_REMARK,
		a.FUND_REDUCE_DATE,
		c.PROVIDENT_START,
		if(a.FUND_ID = NULL,'',
		(case when a.STATUS = 0 then "未提交"
		when a.STATUS = 1 then "待审核"
		when a.STATUS = 2 then "审核通过"
		when a.STATUS = 3 then "审核不通过"
		when a.STATUS = 4 then "已办结"
		when a.STATUS = 5 then "待SSC审核"
		else null  end )
		) as FUND_AUDIT_STATUS,
		c.AUDIT_USER_NAME as FUND_AUDIT_USER,
		c.HANDLE_USER_NAME as FUND_HANDLE_USER,
		c.HANDLE_TIME as FUND_HANDLE_TIME,
		s.AUDIT_USER_NAME as AUDIT_USER_NAME,
		if(a.SOCIAL_ID =NULL,'',
		(case when a.STATUS = 0 then "未提交"
		when a.STATUS = 1 then "待审核"
		when a.STATUS = 2 then "审核通过"
		when a.STATUS = 3 then "审核不通过"
		when a.STATUS = 4 then "已办结"
		when a.STATUS = 5 then "待SSC审核"
		else null  end )
		) as AUDIT_STATUS,
		s.SOCIAL_START_DATE,
		a.SOCIAL_REDUCE_DATE,
		s.RECORD_BASE,
		s.HANDLE_USER_NAME as HANDLE_USER,
		s.HANDLE_TIME,
		if(ifnull(h.id,0) != '0',h.UNIT_PENSION_CARDINAL,s.UNIT_PENSION_CARDINAL) UNIT_PENSION_CARDINAL,
		if(ifnull(h.id,0) != '0',h.UNIT_MEDICAL_CARDINAL,s.UNIT_MEDICAL_CARDINAL) UNIT_MEDICAL_CARDINAL,
		if(ifnull(h.id,0) != '0',h.UNIT_UNEMPLOYMENT_CARDINAL,s.UNIT_UNEMPLOYMENT_CARDINAL) UNIT_UNEMPLOYMENT_CARDINAL,
		if(ifnull(h.id,0) != '0',h.UNIT_BIGAILMENT_CARDINAL,s.UNIT_BIGAILMENT_CARDINAL) UNIT_BIGAILMENT_CARDINAL,
		if(ifnull(h.id,0) != '0',h.UNIT_WORK_INJURY_CARDINAL,s.UNIT_WORK_INJURY_CARDINAL) UNIT_WORK_INJURY_CARDINAL,
		if(ifnull(h.id,0) != '0',h.UNIT_BIRTH_CARDINAL,s.UNIT_BIRTH_CARDINAL) UNIT_BIRTH_CARDINAL,
		c.PROVIDENT_HOUSEHOLD_NAME,
		c.UNIT_PROVIDENG_CARDINAL,
		c.UNIT_PROVIDENT_PER,
		c.PERSONAL_PROVIDENT_PER,
		a.EMP_MOBILE,
		a.EDUCATION_NAME,
		a.SCHOOL_NAME,
		a.MAJOR,
		a.FIRST_PAY_TIME,
		a.GRADUATION_TIME,
		case when a.AUTO_FLAG = 0 then "是"
		when a.AUTO_FLAG = 1 then "否"
		else "无"  end  as  "AUTO_FLAG"

		from t_dispatch_info a
		left join t_dispatch_social_fund_info h on a.id = h.REDUCE_SOCIAL_FUND_ID
		left join t_social_info s on s.id=a.SOCIAL_ID
		left join t_provident_fund c on c.id=a.FUND_ID
		<where>
			a.DELETE_FLAG = '0'
			<include refid="tDispatchInfo_where"/>
		</where>
		order by a.APPLY_NO desc
		<if test="tDispatchInfo.limitStart != null">
			limit #{tDispatchInfo.limitStart},#{tDispatchInfo.limitEnd}
		</if>
	</select>

	<!-- 派单审核导出 -->
	<select id="exportAuditDispatch" resultMap="exportAuditMap">
		select
		a.EMP_NAME,
		a.EMP_IDCARD,
		a.EMP_MOBILE,
		a.EMP_TYPE,
		a.ID_CARD_ADDRESS,
		a.BELONG_UNIT_NAME,
		a.SETTLE_DOMAIN_NAME,
		a.TYPE,
		a.SOCIAL_HOUSEHOLD_NAME,
		a.PROVIDENT_HOUSEHOLD_NAME,
		a.CREATE_NAME,
		a.CREATE_TIME,
		a.CONTRACT_START,
		a.CONTRACT_END,
		a.TRUST_REMARK,
		a.CONTRACT_NAME,
		a.WORKING_HOURS,
		a.SOCIAL_REDUCE_DATE,
		a.FUND_REDUCE_DATE,
		a.LEAVE_DATE,
		a.REDUCE_REASON,
		a.CONTACT_ADDRESS,
		b.SOCIAL_START_DATE,
		b.PAYMENT_TYPE,
		b.RECORD_BASE,
		if(ifnull(h.id,0) != '0',h.UNIT_PENSION_CARDINAL,b.UNIT_PENSION_CARDINAL) UNIT_PENSION_CARDINAL,
		if(ifnull(h.id,0) != '0',h.UNIT_MEDICAL_CARDINAL,b.UNIT_MEDICAL_CARDINAL) UNIT_MEDICAL_CARDINAL,
		if(ifnull(h.id,0) != '0',h.UNIT_UNEMPLOYMENT_CARDINAL,b.UNIT_UNEMPLOYMENT_CARDINAL) UNIT_UNEMPLOYMENT_CARDINAL,
		if(ifnull(h.id,0) != '0',h.UNIT_WORK_INJURY_CARDINAL,b.UNIT_WORK_INJURY_CARDINAL) UNIT_WORK_INJURY_CARDINAL,
		if(ifnull(h.id,0) != '0',h.UNIT_BIRTH_CARDINAL,b.UNIT_BIRTH_CARDINAL) UNIT_BIRTH_CARDINAL,
		if(ifnull(h.id,0) != '0',h.PERSONAL_PENSION_CARDINAL,b.PERSONAL_PENSION_CARDINAL) PERSONAL_PENSION_CARDINAL,
		if(ifnull(h.id,0) != '0',h.PERSONAL_MEDICAL_CARDINAL,b.PERSONAL_MEDICAL_CARDINAL) PERSONAL_MEDICAL_CARDINAL,
		if(ifnull(h.id,0) != '0',h.PERSONAL_UNEMPLOYMENT_CARDINAL,b.PERSONAL_UNEMPLOYMENT_CARDINAL) PERSONAL_UNEMPLOYMENT_CARDINAL,
		if(ifnull(h.id,0) != '0',h.PERSONAL_BIGAILMENT_MONEY,b.PERSONAL_BIGAILMENT_MONEY) PERSONAL_BIGAILMENT_MONEY,
		if(ifnull(h.id,0) != '0',h.UNIT_BIGAILMENT_MONEY,b.UNIT_BIGAILMENT_MONEY) UNIT_BIGAILMENT_MONEY,
		c.UNIT_PROVIDENG_CARDINAL,
		c.PROVIDENT_START,
		c.PERSONAL_PROVIDENT_CARDINAL,
		c.UNIT_PROVIDENT_PER,
		c.PERSONAL_PROVIDENT_PER,
		a.EDUCATION_NAME,
		a.SCHOOL_NAME,
		a.MAJOR,
		a.FIRST_PAY_TIME,
		a.GRADUATION_TIME
		from t_dispatch_info a
		left join t_dispatch_social_fund_info h on a.id = h.REDUCE_SOCIAL_FUND_ID
		left join t_social_info b on a.SOCIAL_ID = b.ID
		left join t_provident_fund c on a.FUND_ID = C.ID
		<where>
			a.DELETE_FLAG = '0'
			<if test="tDispatchInfo != null">
				<if test="tDispatchInfo.status != null and tDispatchInfo.status.trim() != ''">
					AND a.STATUS = #{tDispatchInfo.status}
				</if>
				<if test="tDispatchInfo.status == null or tDispatchInfo.status.trim() == ''">
					AND a.STATUS = '1'
				</if>
			</if>
			<include refid="tDispatchInfoAudit_where"/>
		</where>
		order by a.APPLY_NO desc
		<if test="tDispatchInfo.limitStart != null">
			limit #{tDispatchInfo.limitStart},#{tDispatchInfo.limitEnd}
		</if>
	</select>

	<select id="selectExportCount" resultType="java.lang.Long">
		SELECT
		count(1)
		from t_dispatch_info a
		<where>
			a.DELETE_FLAG = '0'
			<if test="tDispatchInfo != null">
				<if test="tDispatchInfo.status != null and tDispatchInfo.status.trim() != ''">
					AND a.STATUS = #{tDispatchInfo.status}
				</if>
				<if test="tDispatchInfo.status == null or tDispatchInfo.status.trim() == ''">
					AND a.STATUS = '1'
				</if>
			</if>
			<include refid="tDispatchInfoAudit_where"/>
		</where>
	</select>

	<resultMap id="socialSoldierExportMap" type="com.yifu.cloud.plus.v1.yifu.social.vo.SocialSoldierExportVo">
		<result property="empName" column="EMP_NAME"/>
		<result property="empIdcard" column="EMP_IDCARD"/>
		<result property="empType" column="EMP_TYPE"/>
		<result property="empMobile" column="EMP_MOBILE"/>
		<result property="idCardAddress" column="ID_CARD_ADDRESS"/>
		<result property="empNational" column="EMP_NATIONAL"/>
		<result property="type" column="TYPE"/>
		<result property="settleDomain" column="SETTLE_DOMAIN_NAME"/>
		<result property="reduceReason" column="REDUCE_REASON"/>
		<result property="belongUnit" column="BELONG_UNIT_NAME"/>
		<result property="contractStart" column="CONTRACT_START"/>
		<result property="contractEnd" column="CONTRACT_END"/>
		<result property="contractTerm" column="CONTRACT_TERM"/>
		<result property="empRegisType" column="EMP_REGIS_TYPE"/>
		<result property="post" column="POST"/>
		<result property="socialHandleStatus" column="SOCIAL_HANDLE_STATUS"/>
		<result property="socialProvince" column="SOCIAL_PROVINCE"/>
		<result property="socialCity" column="SOCIAL_CITY"/>
		<result property="socialTown" column="SOCIAL_TOWN"/>
		<result property="workingHours" column="WORKING_HOURS"/>
		<result property="educationName" column="EDUCATION_NAME"/>
		<result property="createUserName" column="CREATE_NAME"/>

		<result property="socialHouseholdName" column="SOCIAL_HOUSEHOLD_NAME"/>
		<result property="socialProvince" column="SOCIAL_PROVINCE"/>
		<result property="socialCity" column="SOCIAL_CITY"/>
		<result property="socialTown" column="SOCIAL_TOWN"/>
		<result property="unitPensionCardinal" column="UNIT_PENSION_CARDINAL"/>
		<result property="unitMedicalCardinal" column="UNIT_MEDICAL_CARDINAL"/>
		<result property="unitUnemploymentCardinal" column="UNIT_UNEMPLOYMENT_CARDINAL"/>
		<result property="unitWorkInjuryCardinal" column="UNIT_WORK_INJURY_CARDINAL"/>
		<result property="unitBirthCardinal" column="UNIT_BIRTH_CARDINAL"/>
		<result property="personalBigailmentMoney" column="PERSONAL_BIGAILMENT_MONEY"/>
		<result property="unitBigailmentMoney" column="UNIT_BIGAILMENT_MONEY"/>
		<result property="unitBigailmentCardinal" column="UNIT_BIGAILMENT_CARDINAL"/>
		<result property="socailTrustRemark" column="TRUST_REMARK"/>
		<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="exportSocialFlag" column="EXPORT_SOCIAL_FLAG"/>
		<result property="schoolName" column="SCHOOL_NAME"/>
		<result property="major" column="MAJOR"/>
		<result property="firstPayTime" column="FIRST_PAY_TIME"/>
		<result property="graduationTime" column="GRADUATION_TIME"/>

		<result property="leaveDate" column="LEAVE_DATE"/>
		<result property="ygsHandleStatus" column="ygs_Handle_Status"/>
		<result property="ysdHandleStatus" column="ysd_Handle_Status"/>
		<result property="ygsRemark" column="YGS_REMARK"/>
		<result property="ysdRemark" column="YSD_REMARK"/>

	</resultMap>

	<resultMap id="socialHandleExportMap" type="com.yifu.cloud.plus.v1.yifu.social.vo.SocialHandleExportVo">
		<result property="empName" column="EMP_NAME"/>
		<result property="empIdcard" column="EMP_IDCARD"/>
		<result property="empType" column="EMP_TYPE"/>
		<result property="empMobile" column="EMP_MOBILE"/>
		<result property="idCardAddress" column="ID_CARD_ADDRESS"/>
		<result property="empNational" column="EMP_NATIONAL"/>
		<result property="type" column="TYPE"/>
		<result property="settleDomain" column="SETTLE_DOMAIN_NAME"/>
		<result property="reduceReason" column="REDUCE_REASON"/>
		<result property="belongUnit" column="BELONG_UNIT_NAME"/>
		<result property="contractStart" column="CONTRACT_START"/>
		<result property="contractEnd" column="CONTRACT_END"/>
		<result property="contractTerm" column="CONTRACT_TERM"/>
		<result property="empRegisType" column="EMP_REGIS_TYPE"/>
		<result property="post" column="POST"/>
		<result property="socialHandleStatus" column="SOCIAL_HANDLE_STATUS"/>
		<result property="socialProvince" column="SOCIAL_PROVINCE"/>
		<result property="socialCity" column="SOCIAL_CITY"/>
		<result property="socialTown" column="SOCIAL_TOWN"/>
		<result property="workingHours" column="WORKING_HOURS"/>
		<result property="educationName" column="EDUCATION_NAME"/>
		<result property="createUserName" column="CREATE_NAME"/>

		<result property="socialHouseholdName" column="SOCIAL_HOUSEHOLD_NAME"/>
		<result property="socialProvince" column="SOCIAL_PROVINCE"/>
		<result property="socialCity" column="SOCIAL_CITY"/>
		<result property="socialTown" column="SOCIAL_TOWN"/>
		<result property="unitPensionCardinal" column="UNIT_PENSION_CARDINAL"/>
		<result property="unitMedicalCardinal" column="UNIT_MEDICAL_CARDINAL"/>
		<result property="unitUnemploymentCardinal" column="UNIT_UNEMPLOYMENT_CARDINAL"/>
		<result property="unitWorkInjuryCardinal" column="UNIT_WORK_INJURY_CARDINAL"/>
		<result property="unitBirthCardinal" column="UNIT_BIRTH_CARDINAL"/>
		<result property="personalBigailmentMoney" column="PERSONAL_BIGAILMENT_MONEY"/>
		<result property="unitBigailmentMoney" column="UNIT_BIGAILMENT_MONEY"/>
		<result property="unitBigailmentCardinal" column="UNIT_BIGAILMENT_CARDINAL"/>
		<result property="socailTrustRemark" column="TRUST_REMARK"/>
		<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="exportSocialFlag" column="EXPORT_SOCIAL_FLAG"/>
		<result property="schoolName" column="SCHOOL_NAME"/>
		<result property="major" column="MAJOR"/>
		<result property="firstPayTime" column="FIRST_PAY_TIME"/>
		<result property="graduationTime" column="GRADUATION_TIME"/>
		<result property="leaveDate" column="LEAVE_DATE"/>
		<result property="socialReduceDate" column="SOCIAL_REDUCE_DATE"/>
	</resultMap>

	<resultMap id="fundHandleMap" type="com.yifu.cloud.plus.v1.yifu.social.vo.FundHandleExportVo">
		<result property="type" column="TYPE"/>
		<result property="empName" column="EMP_NAME"/>
		<result property="empIdcard" column="EMP_IDCARD"/>
		<result property="belongUnit" column="BELONG_UNIT_NAME"/>
		<result property="settleDomain" column="SETTLE_DOMAIN_NAME"/>
		<result property="empMobile" column="EMP_MOBILE"/>
		<result property="idCardAddress" column="ID_CARD_ADDRESS"/>
		<result property="createUserName" column="CREATE_NAME"/>
		<result property="fundReduceDate" column="FUND_REDUCE_DATE"/>
		<result property="providentHouseholdName" column="PROVIDENT_HOUSEHOLD_NAME"/>
		<result property="providentStart" column="PROVIDENT_START"/>
		<result property="unitProvidengCardinal" column="UNIT_PROVIDENG_CARDINAL"/>
		<result property="personalProvidentCardinal" column="PERSONAL_PROVIDENT_CARDINAL"/>
		<result property="unitProvidentPer" column="UNIT_PROVIDENT_PER"/>
		<result property="personalProvidentPer" column="PERSONAL_PROVIDENT_PER"/>
		<result property="unitProvidentFee" column="UNIT_PROVIDENT_FEE"/>
		<result property="personalProvidentFee" column="PERSONAL_PROVIDENT_FEE"/>
		<result property="fundProvince" column="FUND_PROVINCE"/>
		<result property="fundCity" column="FUND_CITY"/>
		<result property="fundTown" column="FUND_TOWN"/>
		<result property="belongUnit" column="BELONG_UNIT_NAME"/>
		<result property="fundTrustRemark" column="TRUST_REMARK"/>
		<result property="leaveDate" column="LEAVE_DATE"/>
		<result property="reduceReason" column="REDUCE_REASON"/>
		<result property="exportFundFlag" column="EXPORT_FUND_FLAG"/>
	</resultMap>

	<!--tDispatchInfo 社保花名册数据查询-->
	<select id="getSocialRecordRosterList" resultMap="socialHandleExportMap">
		SELECT
		a.TYPE,
		a.SOCIAL_HANDLE_STATUS,
		/*IF(a.TYPE='0',s.PENSION_HANDLE,IF(s.PENSION_HANDLE='2',"--",a.SOCIAL_HANDLE_STATUS)) as "PENSION_HANDLE",
		IF(a.TYPE='0',s.MEDICAL_HANDLE,IF(s.MEDICAL_HANDLE='2',"--",a.SOCIAL_HANDLE_STATUS)) as "MEDICAL_HANDLE",
		IF(a.TYPE='0',s.UNEMPLOY_HANDLE,IF(s.UNEMPLOY_HANDLE='2',"--",a.SOCIAL_HANDLE_STATUS)) as "UNEMPLOY_HANDLE",
		IF(a.TYPE='0',s.WORK_INJURY_HANDLE,IF(s.WORK_INJURY_HANDLE='2',"--",a.SOCIAL_HANDLE_STATUS)) as "WORK_INJURY_HANDLE",
		IF(a.TYPE='0',s.BIRTH_HANDLE,IF(s.BIRTH_HANDLE='2',"--",a.SOCIAL_HANDLE_STATUS)) as "BIRTH_HANDLE",
		IF(a.TYPE='0',s.BIGAILMENT_HANDLE,IF(s.BIGAILMENT_HANDLE='2',"--",a.SOCIAL_HANDLE_STATUS)) as "BIGAILMENT_HANDLE",*/
		if(ifnull(h.id,0) != '0',h.PENSION_HANDLE,s.PENSION_HANDLE) PENSION_HANDLE,
		if(ifnull(h.id,0) != '0',h.MEDICAL_HANDLE,s.MEDICAL_HANDLE) MEDICAL_HANDLE,
		if(ifnull(h.id,0) != '0',h.UNEMPLOY_HANDLE,s.UNEMPLOY_HANDLE) UNEMPLOY_HANDLE,
		if(ifnull(h.id,0) != '0',h.WORK_INJURY_HANDLE,s.WORK_INJURY_HANDLE) WORK_INJURY_HANDLE,
		if(ifnull(h.id,0) != '0',h.BIRTH_HANDLE,s.BIRTH_HANDLE) BIRTH_HANDLE,
		if(ifnull(h.id,0) != '0',h.BIGAILMENT_HANDLE,s.BIGAILMENT_HANDLE) BIGAILMENT_HANDLE,
		a.EMP_NAME,
		a.EMP_IDCARD,
		a.EDUCATION_NAME,
		a.BELONG_UNIT_NAME,
		a.SETTLE_DOMAIN_NAME,
		a.EMP_TYPE,
		a.POST,
		a.EMP_MOBILE,
		a.ID_CARD_ADDRESS,
		a.EMP_REGIS_TYPE,
		a.EMP_NATIONAL,
		a.WORKING_HOURS,
		a.CONTRACT_TERM,
		a.CONTRACT_START,
		a.CONTRACT_END,
		a.CREATE_NAME,
		a.CONTACT_ADDRESS,
		s.RECORD_BASE,
		s.PAYMENT_TYPE,
		a.SOCIAL_HOUSEHOLD_NAME,
		if(ifnull(h.id,0) != '0',h.SOCIAL_PROVINCE,s.SOCIAL_PROVINCE) SOCIAL_PROVINCE,
		if(ifnull(h.id,0) != '0',h.SOCIAL_CITY,s.SOCIAL_CITY) SOCIAL_CITY,
		if(ifnull(h.id,0) != '0',h.SOCIAL_TOWN,s.SOCIAL_TOWN) SOCIAL_TOWN,
		if(ifnull(h.id,0) != '0',h.PENSION_START,s.PENSION_START) PENSION_START,
		if(ifnull(h.id,0) != '0',h.UNIT_PENSION_CARDINAL,s.UNIT_PENSION_CARDINAL) UNIT_PENSION_CARDINAL,
		if(ifnull(h.id,0) != '0',h.MEDICAL_START,s.MEDICAL_START) MEDICAL_START,
		if(ifnull(h.id,0) != '0',h.UNIT_MEDICAL_CARDINAL,s.UNIT_MEDICAL_CARDINAL) UNIT_MEDICAL_CARDINAL,
		if(ifnull(h.id,0) != '0',h.UNEMPLOY_START,s.UNEMPLOY_START) UNEMPLOY_START,
		if(ifnull(h.id,0) != '0',h.UNIT_UNEMPLOYMENT_CARDINAL,s.UNIT_UNEMPLOYMENT_CARDINAL) UNIT_UNEMPLOYMENT_CARDINAL,
		if(ifnull(h.id,0) != '0',h.WORK_INJURY_START,s.WORK_INJURY_START) WORK_INJURY_START,
		if(ifnull(h.id,0) != '0',h.UNIT_WORK_INJURY_CARDINAL,s.UNIT_WORK_INJURY_CARDINAL) UNIT_WORK_INJURY_CARDINAL,
		if(ifnull(h.id,0) != '0',h.BIRTH_START,s.BIRTH_START) BIRTH_START,
		if(ifnull(h.id,0) != '0',h.UNIT_BIRTH_CARDINAL,s.UNIT_BIRTH_CARDINAL) UNIT_BIRTH_CARDINAL,
		if(ifnull(h.id,0) != '0',h.BIGAILMENT_START,s.BIGAILMENT_START) BIGAILMENT_START,
		if(ifnull(h.id,0) != '0',h.UNIT_BIGAILMENT_CARDINAL,s.UNIT_BIGAILMENT_CARDINAL) UNIT_BIGAILMENT_CARDINAL,
		if(ifnull(h.id,0) != '0',h.UNIT_BIGAILMENT_MONEY,s.UNIT_BIGAILMENT_MONEY) UNIT_BIGAILMENT_MONEY,
		if(ifnull(h.id,0) != '0',h.PERSONAL_BIGAILMENT_MONEY,s.PERSONAL_BIGAILMENT_MONEY) PERSONAL_BIGAILMENT_MONEY,
		a.TRUST_REMARK,
		a.REDUCE_REASON,
		'1'  as EXPORT_SOCIAL_FLAG,
		a.SCHOOL_NAME,
		a.MAJOR,
		a.FIRST_PAY_TIME,
		a.GRADUATION_TIME,
		IF(a.LEAVE_DATE is not null,DATE_FORMAT(a.LEAVE_DATE,"%Y-%m-%d"),"") as "LEAVE_DATE",
		IF(a.SOCIAL_REDUCE_DATE is not null,DATE_FORMAT(a.SOCIAL_REDUCE_DATE,"%Y-%m-%d"),"") as "SOCIAL_REDUCE_DATE"
		FROM t_dispatch_info a
		left join t_social_info s on a.SOCIAL_ID = s.id
		left join t_dispatch_social_fund_info h on a.id = h.REDUCE_SOCIAL_FUND_ID
		<include refid="where_getSocialRecordRoster"/>
	</select>

	<!--tDispatchInfo 社保士兵数据查询-->
	<select id="getSocialSoldierList" resultMap="socialSoldierExportMap">
		SELECT
		a.TYPE,
		a.SOCIAL_HANDLE_STATUS,
		if(ifnull(h.id,0) != '0',h.PENSION_HANDLE,s.PENSION_HANDLE) PENSION_HANDLE,
		if(ifnull(h.id,0) != '0',h.MEDICAL_HANDLE,s.MEDICAL_HANDLE) MEDICAL_HANDLE,
		if(ifnull(h.id,0) != '0',h.UNEMPLOY_HANDLE,s.UNEMPLOY_HANDLE) UNEMPLOY_HANDLE,
		if(ifnull(h.id,0) != '0',h.WORK_INJURY_HANDLE,s.WORK_INJURY_HANDLE) WORK_INJURY_HANDLE,
		if(ifnull(h.id,0) != '0',h.BIRTH_HANDLE,s.BIRTH_HANDLE) BIRTH_HANDLE,
		if(ifnull(h.id,0) != '0',h.BIGAILMENT_HANDLE,s.BIGAILMENT_HANDLE) BIGAILMENT_HANDLE,
		a.EMP_NAME,
		a.EMP_IDCARD,
		a.EDUCATION_NAME,
		a.BELONG_UNIT_NAME,
		a.SETTLE_DOMAIN_NAME,
		a.EMP_TYPE,
		a.POST,
		a.EMP_MOBILE,
		a.ID_CARD_ADDRESS,
		a.EMP_REGIS_TYPE,
		a.EMP_NATIONAL,
		a.WORKING_HOURS,
		a.CONTRACT_TERM,
		a.CONTRACT_START,
		a.CONTRACT_END,
		a.CREATE_NAME,
		a.CONTACT_ADDRESS,
		s.RECORD_BASE,
		s.PAYMENT_TYPE,
		a.SOCIAL_HOUSEHOLD_NAME,
		if(ifnull(h.id,0) != '0',h.SOCIAL_PROVINCE,s.SOCIAL_PROVINCE) SOCIAL_PROVINCE,
		if(ifnull(h.id,0) != '0',h.SOCIAL_CITY,s.SOCIAL_CITY) SOCIAL_CITY,
		if(ifnull(h.id,0) != '0',h.SOCIAL_TOWN,s.SOCIAL_TOWN) SOCIAL_TOWN,
		if(ifnull(h.id,0) != '0',h.PENSION_START,s.PENSION_START) PENSION_START,
		if(ifnull(h.id,0) != '0',h.UNIT_PENSION_CARDINAL,s.UNIT_PENSION_CARDINAL) UNIT_PENSION_CARDINAL,
		if(ifnull(h.id,0) != '0',h.MEDICAL_START,s.MEDICAL_START) MEDICAL_START,
		if(ifnull(h.id,0) != '0',h.UNIT_MEDICAL_CARDINAL,s.UNIT_MEDICAL_CARDINAL) UNIT_MEDICAL_CARDINAL,
		if(ifnull(h.id,0) != '0',h.UNEMPLOY_START,s.UNEMPLOY_START) UNEMPLOY_START,
		if(ifnull(h.id,0) != '0',h.UNIT_UNEMPLOYMENT_CARDINAL,s.UNIT_UNEMPLOYMENT_CARDINAL) UNIT_UNEMPLOYMENT_CARDINAL,
		if(ifnull(h.id,0) != '0',h.WORK_INJURY_START,s.WORK_INJURY_START) WORK_INJURY_START,
		if(ifnull(h.id,0) != '0',h.UNIT_WORK_INJURY_CARDINAL,s.UNIT_WORK_INJURY_CARDINAL) UNIT_WORK_INJURY_CARDINAL,
		if(ifnull(h.id,0) != '0',h.BIRTH_START,s.BIRTH_START) BIRTH_START,
		if(ifnull(h.id,0) != '0',h.UNIT_BIRTH_CARDINAL,s.UNIT_BIRTH_CARDINAL) UNIT_BIRTH_CARDINAL,
		if(ifnull(h.id,0) != '0',h.BIGAILMENT_START,s.BIGAILMENT_START) BIGAILMENT_START,
		if(ifnull(h.id,0) != '0',h.UNIT_BIGAILMENT_CARDINAL,s.UNIT_BIGAILMENT_CARDINAL) UNIT_BIGAILMENT_CARDINAL,
		if(ifnull(h.id,0) != '0',h.UNIT_BIGAILMENT_MONEY,s.UNIT_BIGAILMENT_MONEY) UNIT_BIGAILMENT_MONEY,
		if(ifnull(h.id,0) != '0',h.PERSONAL_BIGAILMENT_MONEY,s.PERSONAL_BIGAILMENT_MONEY) PERSONAL_BIGAILMENT_MONEY,
		a.TRUST_REMARK,
		a.REDUCE_REASON,
		'1'  as EXPORT_SOCIAL_FLAG,
		a.SCHOOL_NAME,
		a.MAJOR,
		a.FIRST_PAY_TIME,
		a.GRADUATION_TIME,
		IF(a.LEAVE_DATE is not null,DATE_FORMAT(a.LEAVE_DATE,"%Y-%m-%d"),"") as "LEAVE_DATE"
		,s.ygs_Handle_Status
		,s.ysd_Handle_Status
		,s.YGS_REMARK
		,s.YSD_REMARK
		FROM t_dispatch_info a
		left join t_social_info s on a.SOCIAL_ID = s.id
		left join t_dispatch_social_fund_info h on a.id = h.REDUCE_SOCIAL_FUND_ID
		<include refid="where_getSocialSoldier"/>
		group by s.id
		order by a.AUDIT_TIME desc
	</select>

	<update id="updateSocialRecords">
		update
		t_dispatch_info a
		left join t_social_info s on a.SOCIAL_ID = s.id
		left join t_dispatch_social_fund_info h on a.id = h.REDUCE_SOCIAL_FUND_ID
		set a.EXPORT_SOCIAL_FLAG = 1
		<include refid="where_getSocialRecordRoster"/>
	</update>

	<!--tDispatchInfo 社保花名册数据查询count-->
	<select id="getSocialRecordRosterListCount" resultType="java.lang.Integer">
		SELECT
		count(1) from
		(
		SELECT
		a.id
		FROM t_dispatch_info a
		left join t_social_info s on a.SOCIAL_ID = s.id
		left join t_dispatch_social_fund_info h on a.id = h.REDUCE_SOCIAL_FUND_ID
		<include refid="where_getSocialRecordRoster"/>) a
	</select>

	<!-- 导出社保士兵count -->
	<select id="getSocialSoldierCount" resultType="java.lang.Integer">
		SELECT
		count(1)
		from (
		SELECT
		1
		FROM t_dispatch_info a
		left join t_social_info s on a.SOCIAL_ID = s.id
		left join t_dispatch_social_fund_info h on a.id = h.REDUCE_SOCIAL_FUND_ID
		<include refid="where_getSocialSoldier"/>
		group by s.id
		) a
	</select>

	<resultMap id="socialSoldierPageMap" type="com.yifu.cloud.plus.v1.yifu.social.vo.TSocialSoldierPageVo" extends="tDispatchInfoMap">
		<result property="socialHouseholdName" column="SOCIAL_HOUSEHOLD_NAME"/>
		<result property="ygsHandleStatus" column="YGS_HANDLE_STATUS"/>
		<result property="ysdHandleStatus" column="YSD_HANDLE_STATUS"/>
		<result property="ygsRemark" column="YGS_REMARK"/>
		<result property="ysdRemark" column="YSD_REMARK"/>
		<result property="ygsRemarkOld" column="YGS_REMARK_OLD"/>
		<result property="ysdRemarkOld" column="YSD_REMARK_OLD"/>
	</resultMap>

	<!-- hgw 2024-5-10 10:42:33 社保士兵列表查询 -->
	<select id="getTSocialSoldierHandlePage" resultMap="socialSoldierPageMap">
		SELECT
		SUBSTRING_INDEX(GROUP_CONCAT(a.ID ORDER BY a.CREATE_TIME desc),',',1) ID,
		a.EMP_ID,
		a.EMP_NAME,
		a.EMP_NO,
		a.EMP_IDCARD,
		a.EMP_TYPE,
		a.EMP_MOBILE,
		a.EMP_NATIONAL,
		a.STATUS,
		SUBSTRING_INDEX(GROUP_CONCAT(a.TYPE ORDER BY a.CREATE_TIME desc),',',1) TYPE,
		SUBSTRING_INDEX(GROUP_CONCAT(a.SOCIAL_ID ORDER BY a.CREATE_TIME desc),',',1) SOCIAL_ID,
		a.FUND_ID,
		a.TYPE_SUB,
		a.PROCESS_INST_ID,
		a.SETTLE_DOMAIN,
		a.DELETE_FLAG,
		a.SOCIAL_REDUCE_DATE,
		a.FUND_REDUCE_DATE,
		a.LEAVE_DATE,
		a.REDUCE_REASON,
		a.REDUCE_REMARK,
		a.BELONG_UNIT,
		a.CURRENT_OPERATOR,
		a.DISPATCH_ID_BAK,
		a.CONTRACT_START,
		a.CONTRACT_END,
		a.CONTRACT_TERM,
		a.EMP_REGIS_TYPE,
		a.ENJOIN_DATE,
		a.FILE_ADDR,
		a.POST,
		a.TRUST_REMARK,
		a.FILE_PROVINCE,
		a.FILE_CITY,
		a.FILE_TOWN,
		a.CONTRACT_TYPE,
		SUBSTRING_INDEX(GROUP_CONCAT(a.SOCIAL_HANDLE_STATUS ORDER BY a.CREATE_TIME desc),',',1) SOCIAL_HANDLE_STATUS,
		a.FUND_HANDLE_STATUS,
		a.HANDLE_REMARK,
		a.AUDIT_REMARK,
		a.FUND_HANDLE_REMARK,
		a.SOCIAL_HANDLE_REMARK,
		a.SOCIAL_PROVINCE,
		a.SOCIAL_CITY,
		a.SOCIAL_TOWN,
		a.FUND_PROVINCE,
		a.FUND_CITY,
		a.FUND_TOWN,
		a.AUDIT_USER,
		a.AUDIT_USER_NAME,
		a.AUDIT_TIME,
		a.WORKING_HOURS,
		a.CREATE_BY,
		a.UPDATE_BY,
		a.CREATE_NAME,
		a.CREATE_TIME,
		a.UPDATE_TIME,
		a.ORGAN_NAME,
		a.DISPATCH_ITEM,
		a.SETTLE_DOMAIN_NAME,
		a.BELONG_UNIT_NAME,
		a.DISPATCH_CODE,
		a.AGAIN_DISPATCH_CODE,
		a.SETTLE_DOMAIN_CODE,
		a.CONTRACT_SUB_NAME,
		a.CONTRACT_ID,
		a.EDUCATION_NAME,
		a.TRY_PERIOD,
		a.ID_CARD_PROVINCE,
		a.ID_CARD_CITY,
		a.ID_CARD_TOWN,
		a.ID_CARD_ADDRESS,
		SUBSTRING_INDEX(GROUP_CONCAT(a.APPLY_NO ORDER BY a.CREATE_TIME desc),',',1) APPLY_NO,
		a.ORDER_ID,
		a.SCHOOL_NAME,
		a.MAJOR,
		a.FIRST_PAY_TIME,
		a.GRADUATION_TIME,a.AUTO_FLAG
		,a.SOCIAL_HOUSEHOLD_NAME,s.YGS_HANDLE_STATUS,s.YSD_HANDLE_STATUS
		,s.YGS_REMARK,s.YSD_REMARK,s.YGS_REMARK_OLD,s.YSD_REMARK_OLD
		FROM t_dispatch_info a
		left join t_social_info s on a.SOCIAL_ID = s.id
		left join t_dispatch_social_fund_info h on a.id = h.REDUCE_SOCIAL_FUND_ID
		<include refid="where_getSocialSoldier"/>
		group by s.id
		order by a.AUDIT_TIME desc
	</select>

	<!--tDispatchInfo 社保办理数据查询-->
	<select id="getTDispatchSocialHandlePage" resultMap="dispatchPageMap">
		SELECT
		<include refid="Base_Column_List"/>,
		a.SOCIAL_HOUSEHOLD_NAME,a.EXPORT_SOCIAL_FLAG,s.YGS_HANDLE_STATUS,s.YSD_HANDLE_STATUS
		FROM t_dispatch_info a
		left join t_social_info s on a.SOCIAL_ID = s.id
		left join t_dispatch_social_fund_info h on a.id = h.REDUCE_SOCIAL_FUND_ID
		<include refid="where_getSocialRecordRoster"/>
		and s.DELETE_FLAG = 0
	</select>

	<!--tDispatchInfo 社保办理数据查询-->
	<select id="getTDispatchSocialHandleNoPage" resultMap="dispatchPageMap">
		SELECT
		<include refid="Base_Column_List"/>,
		a.SOCIAL_HOUSEHOLD_NAME,a.EXPORT_SOCIAL_FLAG
		FROM t_dispatch_info a
		left join t_social_info s on a.SOCIAL_ID = s.id
		<include refid="where_getSocialRecordNoPage"/>
	</select>

	<!--tDispatchInfo 公积金办理数据查询-->
	<select id="getTDispatchFundHandlePage" resultMap="dispatchPageMap">
		SELECT
		<include refid="Base_Column_List"/>,
		a.PROVIDENT_HOUSEHOLD_NAME,a.EXPORT_FUND_FLAG
		FROM t_dispatch_info a
		left join t_provident_fund f on a.FUND_ID = f.id
		<where>
			<include refid="where_getFundRecord"/>
			group by a.id
			order by a.APPLY_NO desc
		</where>
	</select>

	<!--tDispatchInfo 公积金办理数据查询 2023-05-08-->
	<select id="getTDispatchFundHandleNoPage" resultMap="dispatchPageMap">
		SELECT
		<include refid="Base_Column_List"/>,
		a.PROVIDENT_HOUSEHOLD_NAME
		FROM t_dispatch_info a
		left join t_provident_fund f on a.FUND_ID = f.id
		<where>
			<include refid="where_getFundRecord"/>
			<if test="idCards != null and idCards.size > 0">
				AND a.emp_idcard in
				<foreach item="items" index="index" collection="idCards" open="(" separator="," close=")">
					#{items}
				</foreach>
			</if>
			group by a.id
			order by a.APPLY_NO desc
		</where>

	</select>

	<!--tDispatchInfo 公积金变更清册cout-->
	<select id="getFundRecordCount" resultType="java.lang.Integer">
		SELECT
		count(1) from
		(SELECT
		a.id
		FROM t_dispatch_info a
		left join t_provident_fund f on a.FUND_ID = f.id
		<where>
			<include refid="where_getFundRecord"/>
			group by a.id
			order by a.APPLY_NO desc
		</where>) a
	</select>

	<select id="getFCount" resultType="java.lang.Integer">
		SELECT count(1) from (
		SELECT 1
		FROM t_dispatch_info a
		<where>
			<include refid="where_getFundDisRecord"/>
		</where>
		group by a.EMP_IDCARD ,a.TYPE) h
	</select>

	<select id="getSocialDisRecordCount" resultType="java.lang.Integer">
		SELECT count(1) from (
		SELECT 1
		FROM t_dispatch_info a
		left join t_social_info s on s.id=a.SOCIAL_ID
		<where>
			<include refid="where_getSocialDisRecord"/>
		</where>
		group by a.EMP_IDCARD) h
	</select>

	<!--tDispatchInfo 社保医疗花名册派单数据-->
	<select id="getSocialDisRecord" resultMap="socialDisExportMap">
		SELECT
		(@i :=@i+1) as NUM,
		h.EMP_NAME,
		h.IDCARD_TYPE,
		h.EMP_IDCARD,
		h.DIS_MONTH,
		h.MEDICAL_CARDINAL,
		h.CHANGE_TYPE_ONE,
		h.CHANGE_TYPE_TWO,
		h.CHANGE_TYPE_THREE,
		h.CHANGE_TYPE_FOUR,
		h.EMP_MOBILE,
		h.EDUCATION_NAME,
		h.REMARK
		from (
		select
		a.EMP_NAME,
		'身份证' as IDCARD_TYPE,
		a.EMP_IDCARD,
		replace(DATE_FORMAT(a.CREATE_TIME,"%Y-%m"),'-','') as DIS_MONTH,
		s.UNIT_MEDICAL_CARDINAL as MEDICAL_CARDINAL,
		'' as CHANGE_TYPE_ONE,
		'√' as CHANGE_TYPE_TWO,
		'' as CHANGE_TYPE_THREE,
		'' as CHANGE_TYPE_FOUR,
		a.EMP_MOBILE,
		a.EDUCATION_NAME,
		'' as REMARK
		FROM t_dispatch_info a
		left join t_social_info s on s.id=a.SOCIAL_ID
		<where>
			<include refid="where_getSocialDisRecord"/>
		</where>
		group by a.EMP_IDCARD) h,(select @i:=0) as itable
	</select>

	<!--tDispatchInfo 社保医疗花名册派单数据-->
	<select id="getSocialReduceDisRecord" resultMap="socialDisExportMap">
		SELECT
		(@i :=@i+1) as NUM,
		h.EMP_NAME,
		h.IDCARD_TYPE,
		h.EMP_IDCARD,
		h.DIS_MONTH,
		h.MEDICAL_CARDINAL,
		h.CHANGE_TYPE_ONE,
		h.CHANGE_TYPE_TWO,
		h.CHANGE_TYPE_THREE,
		h.CHANGE_TYPE_FOUR,
		h.EMP_MOBILE,
		h.EDUCATION_NAME,
		h.REMARK
		from (
		select
		a.EMP_NAME,
		'身份证' as IDCARD_TYPE,
		a.EMP_IDCARD,
		replace(DATE_FORMAT(a.CREATE_TIME,"%Y-%m"),'-','') as DIS_MONTH,
		s.UNIT_MEDICAL_CARDINAL as MEDICAL_CARDINAL,
		'' as CHANGE_TYPE_ONE,
		'' as CHANGE_TYPE_TWO,
		'√' as CHANGE_TYPE_THREE,
		'' as CHANGE_TYPE_FOUR,
		'' as EMP_MOBILE,
		'' as EDUCATION_NAME,
		'' as REMARK
		FROM t_dispatch_info a
		left join t_social_info s on s.id=a.SOCIAL_ID
		<where>
			<include refid="where_getSocialDisRecord"/>
		</where>
		group by a.EMP_IDCARD) h,(select @i:=0) as itable
	</select>

	<!--tDispatchInfo 社保养老花名册派单数据-->
	<select id="getSocialPersionDisCount" resultType="java.lang.Integer">
		SELECT count(1) from (
		select 1
		FROM t_dispatch_info a
		<where>
			<include refid="where_getSocialPersionDisRecord"/>
		</where>
		group by a.EMP_IDCARD) h
	</select>

	<!--tDispatchInfo 社保养老花名册派单数据-->
	<select id="getSocialPersionDisRecord" resultMap="socialPersionDisExportMap">
		SELECT
		(@i :=@i+1) as num,
		h.EMP_NAME,
		h.EMP_NO,
		h.EMP_IDCARD,
		h.sex,
		h.EMP_NATIONAL,
		h.WORK_DATE,
		h.type,
		h.SIGNLE_TYPE,
		h.INSURANCE_TYPE_ONE,
		h.INSURANCE_TYPE_TWO,
		h.INSURANCE_TYPE_THREE,
		h.INSURANCE_TYPE_FOUR,
		h.INSURANCE_TYPE_FIVE,
		h.PERSION_CARDINAL,
		h.MEDICAL_CARDINAL,
		h.DATE_ONE,
		h.CONTRACT_ADRESS,
		h.REMARK
		FROM (
		SELECT
		a.EMP_NAME,
		'' as EMP_NO,
		a.EMP_IDCARD,
		CASE (SUBSTR(a.EMP_IDCARD,17,1)%2)
		WHEN 1 THEN '男'
		WHEN 0 THEN '女'
		else '男'
		END sex,
		case when a.EMP_NATIONAL = 1 then "汉族"
		when a.EMP_NATIONAL = 2 then "傣族"
		when a.EMP_NATIONAL = 3 then "回族"
		when a.EMP_NATIONAL = 4 then "未知"
		when a.EMP_NATIONAL = 5 then "满族"
		when a.EMP_NATIONAL = 6 then "壮族"
		when a.EMP_NATIONAL = 7 then "黎族"
		when a.EMP_NATIONAL = 8 then "蒙古族"
		when a.EMP_NATIONAL = 9 then "白族"
		when a.EMP_NATIONAL = 10 then "仫佬族"
		when a.EMP_NATIONAL = 11 then "土家族"
		when a.EMP_NATIONAL = 12 then "畬族"
		when a.EMP_NATIONAL = 13 then "苗族"
		else "汉族" end as EMP_NATIONAL,
		concat(ifnull(DATE_FORMAT(a.CONTRACT_START,"%Y%m%d"),''),'-',ifnull(DATE_FORMAT(a.CONTRACT_END,"%Y%m%d"),'')) as WORK_DATE,
		'非农业户口（城镇）' as type,
		'企业职工' as SIGNLE_TYPE,
		'√' as INSURANCE_TYPE_ONE,
		'√' as INSURANCE_TYPE_TWO,
		'' as INSURANCE_TYPE_THREE,
		'√' as INSURANCE_TYPE_FOUR,
		'' as INSURANCE_TYPE_FIVE,
		s.RECORD_BASE as PERSION_CARDINAL,
		s.UNIT_MEDICAL_CARDINAL as MEDICAL_CARDINAL,
		concat(ifnull(DATE_FORMAT(a.CONTRACT_START,"%Y%m"),''),'-',ifnull(DATE_FORMAT(a.CONTRACT_END,"%Y%m"),'')) as DATE_ONE,
		'安徽省合肥市' as CONTRACT_ADRESS,
		a.EMP_MOBILE as REMARK
		FROM t_dispatch_info a
		left join t_social_info s on s.id=a.SOCIAL_ID
		<where>
			<include refid="where_getSocialPersionDisRecord"/>
		</where>
		group by a.EMP_IDCARD)h,(select @i:=0) as itable
	</select>

	<!--tDispatchInfo 社保养老花名册派单数据-->
	<select id="getSocialPersionReduceDisCount" resultType="java.lang.Integer">
		SELECT count(1) from (
		SELECT 1
		FROM t_dispatch_info a
		<where>
			<include refid="where_getSocialPersionDisRecord"/>
		</where>
		group by a.EMP_IDCARD) h
	</select>

	<!--tDispatchInfo 社保养老花名册派单数据-->
	<select id="getSocialPersionReduceDisRecord" resultMap="socialPersionReduceDisExportMap">
		SELECT
		(@i :=@i+1) as num,
		h.EMP_NO,
		h.EMP_NAME,
		h.EMP_IDCARD,
		h.WORK_DATE,
		h.typeA,
		h.typeB,
		h.typeC,
		h.typeD,
		h.typeE,
		h.typeW,
		h.typeF,
		h.typeG,
		h.typeH,
		h.typeZ,
		h.typeJ,
		h.typeK,
		h.typeL,
		h.typeM,
		h.typeN,
		h.typeO,
		h.typeP,
		h.typeQ,
		h.REMARK
		FROM (
		SELECT
		'' as EMP_NO,
		a.EMP_NAME,
		a.EMP_IDCARD,
		DATE_FORMAT(a.CREATE_TIME,"%Y%m") as WORK_DATE,
		'√' as typeA,
		'' as typeB,
		'' as typeC,
		'' as typeD,
		'' as typeE,
		'' as typeW,
		'' as typeF,
		'' as typeG,
		'' as typeH,
		'' as typeZ,
		'' as typeJ,
		'' as typeK,
		'' as typeL,
		'' as typeM,
		'' as typeN,
		'' as typeO,
		'' as typeP,
		'' as typeQ,
		a.EMP_MOBILE as REMARK
		FROM t_dispatch_info a
		left join t_social_info s on s.id=a.SOCIAL_ID
		<where>
			<include refid="where_getSocialPersionDisRecord"/>
		</where>
		group by a.EMP_IDCARD) h,(select @i:=0) as itable
	</select>

	<!--tDispatchInfo 公积金变更清册查询-->
	<select id="getFundRecordList" resultMap="fundHandleMap">
		SELECT
		a.TYPE,
		a.EMP_NAME,
		a.EMP_IDCARD,
		a.EMP_TYPE,
		a.BELONG_UNIT_NAME,
		a.SETTLE_DOMAIN_NAME,
		a.EMP_MOBILE,
		a.ID_CARD_ADDRESS,
		a.CREATE_NAME,
		a.PROVIDENT_HOUSEHOLD_NAME,
		a.CONTACT_ADDRESS,
		f.PROVIDENT_START,
		f.UNIT_PROVIDENG_CARDINAL,
		a.FUND_REDUCE_DATE,
		f.PERSONAL_PROVIDENT_CARDINAL,
		f.UNIT_PROVIDENT_PER,
		f.PERSONAL_PROVIDENT_PER,
		f.UNIT_PROVIDENT_FEE,
		f.PERSONAL_PROVIDENT_FEE,
		f.FUND_PROVINCE,
		f.FUND_CITY,
		f.FUND_TOWN,
		a.TRUST_REMARK,
		a.LEAVE_DATE,
		a.REDUCE_REASON,
		'1' as EXPORT_FUND_FLAG
		FROM t_dispatch_info a
		left join t_provident_fund f on a.FUND_ID = f.id
		<where>
			<include refid="where_getFundRecord"/>
			group by a.id
			order by a.APPLY_NO desc
		</where>
	</select>
	<update id="updateFundRecords">
		update  t_dispatch_info a
		left join t_provident_fund f on a.FUND_ID = f.id
		set a.EXPORT_FUND_FLAG = 1
		<where>
			<include refid="where_getFundRecord"/>
		</where>
	</update>
	<!--tDispatchInfo 公积金补交清册-->
	<select id="getFundSupplementaryRecords" resultMap="fundSupplementaryMap">
		SELECT
		f.PROVIDENT_START,
		f.UNIT_PROVIDENG_CARDINAL,
		f.PERSONAL_PROVIDENT_CARDINAL,
		f.UNIT_PROVIDENT_PER,
		f.PERSONAL_PROVIDENT_PER,
		f.UNIT_PROVIDENT_FEE,
		f.PERSONAL_PROVIDENT_FEE,
		f.EMP_NAME
		FROM t_dispatch_info a
		left join t_provident_fund f on a.FUND_ID = f.id
		<where>
			<include refid="where_getFundRecord"/>
			group by a.id
			order by a.APPLY_NO desc
		</where>
	</select>

	<!-- 社保办理查询 -->
	<sql id="where_getSocialRecordRoster">
		<where>
			s.DELETE_FLAG = 0
			/** 社保办理状态：0待办理/1已办理2办理失败3已派减4 办理中 5 部分办理失败 派单社保办理状态 0 未办理 1 全部办理成功(原-已办理) 2 全部办理失败（原-办理失败）  3 部分办理成功 4 办理中  **/
			/*社保代办理 1.派单审核通过 2.派增(type=0)：待办理(social_handle_status='0')  or 派减(type=1)：已办理完成或部分办理失败 且已派减*/
			AND a.social_id is not null
			AND a.STATUS = "2"
			and (( a.type = '0' AND (s.HANDLE_STATUS = "0" or s.HANDLE_STATUS = "4") ) or ( a.type ='1'  AND (if(ifnull(h.id,0) != '0',h.HANDLE_STATUS,s.HANDLE_STATUS) in ('1','5','6','3','7') or (s.HANDLE_STATUS in ('1','5','6','3','7') and s.REDUCE_CAN = '1'))  ))
			and a.SOCIAL_HANDLE_STATUS in ('0','4')
			and (a.auto_flag='1' or s.YGS_HANDLE_STATUS ='5' or s.YSD_HANDLE_STATUS = '5' )

			<!--<if test="tDispatchInfo.status != null and tDispatchInfo.status.trim() != ''">
				/*待办理组合判断*/
				<if test="'2'.toString() == tDispatchInfo.status.trim() and tDispatchInfo.typeSub != null ">
					/*社保代办理*/
					<if test=" '0'.toString()==tDispatchInfo.typeSub.trim() ">
						<if test="tDispatchInfo.socialHandleStatus != null and tDispatchInfo.socialHandleStatus != ''">

						</if>
						<if test="tDispatchInfo.socialHandleStatus == null or tDispatchInfo.socialHandleStatus == ''">
							and (( a.type = '0' AND b.HANDLE_STATUS in ('0','4')) or ( a.type ='1'  AND b.HANDLE_STATUS in ('1','5')  and b.REDUCE_CAN = '1'))
							and a.SOCIAL_HANDLE_STATUS in ('0','4')
						</if>
					</if>
					/* 公积金办理*/
					<if test=" '1'.toString()==tDispatchInfo.typeSub.trim() ">
						and (( a.type = '0' AND c.HANDLE_STATUS = '0') or ( a.type ='1'  AND c.HANDLE_STATUS = '1' and c.REDUCE_CAN = '1'))
						and a.FUND_HANDLE_STATUS='0'
					</if>
				</if>
			</if>-->
			<include refid="where_getSocialRecordRoster_base"/>
		</where>
	</sql>

	<!-- 社保士兵列表查询 -->
	<sql id="where_getSocialSoldier">
		<where>
			a.DELETE_FLAG = '0'
			AND a.social_id is not null
			and a.AUTO_FLAG = '0'
			<include refid="where_getSocialRecordRoster_base"/>

			<if test="tDispatchInfo != null">
				<if test="tDispatchInfo.ygsHandleStatus != null and tDispatchInfo.ygsHandleStatus.trim() != ''">
					AND s.YGS_HANDLE_STATUS = #{tDispatchInfo.ygsHandleStatus}
				</if>
				<if test="tDispatchInfo.ysdHandleStatus != null and tDispatchInfo.ysdHandleStatus.trim() != ''">
					AND s.YSD_HANDLE_STATUS = #{tDispatchInfo.ysdHandleStatus}
				</if>
				<if test="tDispatchInfo.ygsRemark != null and tDispatchInfo.ygsRemark.trim() != ''">
					AND s.YGS_REMARK like concat('%',#{tDispatchInfo.ygsRemark},'%')
				</if>
				<if test="tDispatchInfo.ysdRemark != null and tDispatchInfo.ysdRemark.trim() != ''">
					AND s.YSD_REMARK like concat('%',#{tDispatchInfo.ysdRemark},'%')
				</if>
			</if>
		</where>
	</sql>

	<sql id="where_getSocialRecordRoster_base">
		<if test="idsStr != null and idsStr.size > 0">
			AND a.ID in
			<foreach item="items" index="index" collection="idsStr" open="(" separator="," close=")">
				#{items}
			</foreach>
		</if>
		<if test="tDispatchInfo != null">
			<if test="tDispatchInfo.empTypeList != null  and tDispatchInfo.empTypeList.length>0">
				AND a.EMP_TYPE IN
				<foreach collection="tDispatchInfo.empTypeList" item="item" index="index" open="(" separator="," close=")">
					#{item}
				</foreach>
			</if>

			<if test="tDispatchInfo.dispatchItems != null and tDispatchInfo.dispatchItems.size >0">
				AND
				<foreach item="item" index="index" collection="tDispatchInfo.dispatchItems" open="(" separator="or" close=")">
					INSTR(a.DISPATCH_ITEM,#{item})
				</foreach>
			</if>
			<if test="tDispatchInfo.type != null and tDispatchInfo.type.trim() != ''">
				AND a.TYPE = #{tDispatchInfo.type}
			</if>
			<if test="tDispatchInfo.socialProvince != null and tDispatchInfo.socialProvince!= null">
				AND  if(ifnull(h.id,0) != '0',h.social_province,s.social_province) = #{tDispatchInfo.socialProvince}
				<if test="tDispatchInfo.socialCity != null and tDispatchInfo.socialCity!= null">
					AND  if(ifnull(h.id,0) != '0',h.social_city,s.social_city) = #{tDispatchInfo.socialCity}
				</if>
				<if test="tDispatchInfo.socialTown != null and tDispatchInfo.socialTown!= null">
					AND  if(ifnull(h.id,0) != '0',h.social_town,s.social_town) = #{tDispatchInfo.socialTown}
				</if>
			</if>
			<if test="tDispatchInfo.empName != null and tDispatchInfo.empName.trim() != ''">
				AND a.EMP_NAME like concat('%',#{tDispatchInfo.empName},'%')
			</if>
			<if test="tDispatchInfo.empIdcard != null and tDispatchInfo.empIdcard.trim() != ''">
				AND a.EMP_IDCARD like concat('%',#{tDispatchInfo.empIdcard},'%')
			</if>
			<if test="tDispatchInfo.status != null and tDispatchInfo.status.trim() != ''">
				AND a.STATUS = #{tDispatchInfo.status}
			</if>

			<if test="tDispatchInfo.settleDomain != null and tDispatchInfo.settleDomain.trim() != ''">
				AND a.SETTLE_DOMAIN = #{tDispatchInfo.settleDomain}
			</if>

			<if test="tDispatchInfo.settleDomainCode != null and tDispatchInfo.settleDomainCode.trim() != ''">
				AND a.SETTLE_DOMAIN_CODE = #{tDispatchInfo.settleDomainCode}
			</if>

			<if test="tDispatchInfo.socialHandleStatus != null and tDispatchInfo.socialHandleStatus.trim() != ''">
				AND a.SOCIAL_HANDLE_STATUS = #{tDispatchInfo.socialHandleStatus}
			</if>

			<if test="tDispatchInfo.createName != null and tDispatchInfo.createName.trim() != ''">
				AND a.CREATE_NAME = #{tDispatchInfo.createName}
			</if>

			<if test="tDispatchInfo.settleDomainName != null and tDispatchInfo.settleDomainName.trim() != ''">
				AND a.SETTLE_DOMAIN_NAME like CONCAT(CONCAT(#{tDispatchInfo.settleDomainName}), '%')
			</if>
			<if test="tDispatchInfo.belongUnitName != null and tDispatchInfo.belongUnitName.trim() != ''">
				AND a.BELONG_UNIT_NAME = #{tDispatchInfo.belongUnitName}
			</if>
			<if test="tDispatchInfo.createTimeEnd != null">
				AND a.CREATE_TIME <![CDATA[<=]]> #{tDispatchInfo.createTimeEnd}
			</if>
			<if test="tDispatchInfo.createTimeStart != null">
				AND a.CREATE_TIME <![CDATA[>=]]> #{tDispatchInfo.createTimeStart}
			</if>
			<if test="tDispatchInfo.auditTimeStart != null">
				AND a.AUDIT_TIME <![CDATA[>=]]> #{tDispatchInfo.auditTimeStart}
			</if>
			<if test="tDispatchInfo.auditTimeEnd != null">
				AND a.AUDIT_TIME <![CDATA[<=]]> #{tDispatchInfo.auditTimeEnd}
			</if>
			<if test="tDispatchInfo.auditUserName != null and tDispatchInfo.auditUserName.trim() != ''">
				AND a.AUDIT_USER_NAME like concat('%',#{tDispatchInfo.auditUserName},'%')
			</if>
			<if test="tDispatchInfo.socialHouseholdName != null and tDispatchInfo.socialHouseholdName.trim() != ''">
				AND a.SOCIAL_HOUSEHOLD_NAME  like CONCAT(CONCAT(#{tDispatchInfo.socialHouseholdName}), '%')
			</if>
			<if test="tDispatchInfo.authSql != null and tDispatchInfo.authSql.trim() != ''">
				${tDispatchInfo.authSql}
			</if>
			<if test="tDispatchInfo.exportSocialFlag != null and tDispatchInfo.exportSocialFlag.trim() != ''">
				AND a.EXPORT_SOCIAL_FLAG = ${tDispatchInfo.exportSocialFlag}
			</if>
		</if>
	</sql>


	<sql id="where_getSocialRecordNoPage">
		<where>
			s.DELETE_FLAG = 0
			<if test="idsStr != null and idsStr.size > 0">
				AND a.ID in
				<foreach item="items" index="index" collection="idsStr" open="(" separator="," close=")">
					#{items}
				</foreach>
			</if>
			/** 社保办理状态：0待办理/1已办理2办理失败3已派减4 办理中 5 部分办理失败 派单社保办理状态 0 未办理 1 全部办理成功(原-已办理) 2 全部办理失败（原-办理失败）  3 部分办理成功 4 办理中  **/
			/*社保代办理 1.派单审核通过 2.派增(type=0)：待办理(social_handle_status='0')  or 派减(type=1)：已办理完成或部分办理失败 且已派减*/
			AND a.social_id is not null
			AND a.STATUS = "2"
			and (( a.type = '0' AND (s.HANDLE_STATUS = "0" or s.HANDLE_STATUS = "4") ) or ( a.type ='1'  AND s.HANDLE_STATUS in ('1','5','6','3','7')  and s.REDUCE_CAN = '1'))
			and a.SOCIAL_HANDLE_STATUS in ('0','4')
			<if test="tDispatchInfo.empTypeList != null  and tDispatchInfo.empTypeList.length>0 ">
				AND a.EMP_TYPE IN
				<foreach collection="tDispatchInfo.empTypeList" item="item" index="index" open="(" separator="," close=")">
					#{item}
				</foreach>
			</if>
			<if test="tDispatchInfo != null">
				<if test="tDispatchInfo.dispatchItems != null and tDispatchInfo.dispatchItems.size >0">
					AND
					<foreach item="item" index="index" collection="tDispatchInfo.dispatchItems" open="(" separator="or" close=")">
						INSTR(a.DISPATCH_ITEM,#{item})
					</foreach>
				</if>
				<if test="tDispatchInfo.type != null and tDispatchInfo.type.trim() != ''">
					AND a.TYPE = #{tDispatchInfo.type}
				</if>
				<if test="tDispatchInfo.socialProvince != null and tDispatchInfo.socialProvince!= null">
					AND  s.social_province = #{tDispatchInfo.socialProvince}
					<if test="tDispatchInfo.socialCity != null and tDispatchInfo.socialCity!= null">
						AND  s.social_city = #{tDispatchInfo.socialCity}
					</if>
					<if test="tDispatchInfo.socialTown != null and tDispatchInfo.socialTown!= null">
						AND  s.social_town = #{tDispatchInfo.socialTown}
					</if>
				</if>
				<if test="tDispatchInfo.empName != null and tDispatchInfo.empName.trim() != ''">
					AND a.EMP_NAME like concat('%',#{tDispatchInfo.empName},'%')
				</if>
				<if test="tDispatchInfo.empIdcard != null and tDispatchInfo.empIdcard.trim() != ''">
					AND a.EMP_IDCARD like concat('%',#{tDispatchInfo.empIdcard},'%')
				</if>
				<if test="tDispatchInfo.status != null and tDispatchInfo.status.trim() != ''">
					AND a.STATUS = #{tDispatchInfo.status}
				</if>

				<if test="tDispatchInfo.settleDomain != null and tDispatchInfo.settleDomain.trim() != ''">
					AND a.SETTLE_DOMAIN = #{tDispatchInfo.settleDomain}
				</if>

				<if test="tDispatchInfo.settleDomainCode != null and tDispatchInfo.settleDomainCode.trim() != ''">
					AND a.SETTLE_DOMAIN_CODE = #{tDispatchInfo.settleDomainCode}
				</if>

				<if test="tDispatchInfo.socialHandleStatus != null and tDispatchInfo.socialHandleStatus.trim() != ''">
					AND a.SOCIAL_HANDLE_STATUS = #{tDispatchInfo.socialHandleStatus}
				</if>

				<if test="tDispatchInfo.createName != null and tDispatchInfo.createName.trim() != ''">
					AND a.CREATE_NAME = #{tDispatchInfo.createName}
				</if>

				<if test="tDispatchInfo.settleDomainName != null and tDispatchInfo.settleDomainName.trim() != ''">
					AND a.SETTLE_DOMAIN_NAME like CONCAT(CONCAT(#{tDispatchInfo.settleDomainName}), '%')
				</if>
				<if test="tDispatchInfo.belongUnitName != null and tDispatchInfo.belongUnitName.trim() != ''">
					AND a.BELONG_UNIT_NAME = #{tDispatchInfo.belongUnitName}
				</if>
				<if test="tDispatchInfo.createTimeEnd != null">
					AND a.CREATE_TIME <![CDATA[<=]]> #{tDispatchInfo.createTimeEnd}
				</if>
				<if test="tDispatchInfo.createTimeStart != null">
					AND a.CREATE_TIME <![CDATA[>=]]> #{tDispatchInfo.createTimeStart}
				</if>
				<if test="tDispatchInfo.auditTimeStart != null">
					AND a.AUDIT_TIME <![CDATA[>=]]> #{tDispatchInfo.auditTimeStart}
				</if>
				<if test="tDispatchInfo.auditTimeEnd != null">
					AND a.AUDIT_TIME <![CDATA[<=]]> #{tDispatchInfo.auditTimeEnd}
				</if>
				<if test="tDispatchInfo.auditUserName != null and tDispatchInfo.auditUserName.trim() != ''">
					AND a.AUDIT_USER_NAME like concat('%',#{tDispatchInfo.auditUserName},'%')
				</if>
				<if test="tDispatchInfo.socialHouseholdName != null and tDispatchInfo.socialHouseholdName.trim() != ''">
					AND a.SOCIAL_HOUSEHOLD_NAME  like CONCAT(CONCAT(#{tDispatchInfo.socialHouseholdName}), '%')
				</if>
				<if test="tDispatchInfo.authSql != null and tDispatchInfo.authSql.trim() != ''">
					${tDispatchInfo.authSql}
				</if>
				<if test="tDispatchInfo.exportSocialFlag != null and tDispatchInfo.exportSocialFlag.trim() != ''">
					AND a.EXPORT_SOCIAL_FLAG = ${tDispatchInfo.exportSocialFlag}
				</if>
			</if>
			<if test="idCards != null and idCards.size > 0">
				AND a.emp_idcard in
				<foreach item="items" index="index" collection="idCards" open="(" separator="," close=")">
					#{items}
				</foreach>
			</if>
			group by a.id
			order by a.APPLY_NO desc
		</where>
	</sql>

	<sql id="where_getFundRecord">
		a.DELETE_FLAG = 0
		<if test="idsStr != null and idsStr.size > 0">
			AND a.ID in
			<foreach item="items" index="index" collection="idsStr" open="(" separator="," close=")">
				#{items}
			</foreach>
		</if>
		/** 社保办理状态：0待办理/1已办理2办理失败3已派减4 办理中 5 部分办理失败 派单社保办理状态 0 未办理 1 全部办理成功(原-已办理) 2 全部办理失败（原-办理失败）  3 部分办理成功 4 办理中  **/
		/*社保代办理 1.派单审核通过 2.派增(type=0)：待办理(social_handle_status='0')  or 派减(type=1)：已办理完成或部分办理失败 且已派减*/
		AND a.fund_id is not null
		AND a.STATUS = "2"
		and (( a.type = '0' AND f.HANDLE_STATUS = '0') or ( a.type ='1'  AND f.HANDLE_STATUS = '1' and f.REDUCE_CAN = '1'))
		and a.FUND_HANDLE_STATUS='0'
		<!--<if test="tDispatchInfo.status != null and tDispatchInfo.status.trim() != ''">
			/*待办理组合判断*/
			<if test="'2'.toString() == tDispatchInfo.status.trim() and tDispatchInfo.typeSub != null ">
				/*社保代办理*/
				<if test=" '0'.toString()==tDispatchInfo.typeSub.trim() ">
					<if test="tDispatchInfo.socialHandleStatus != null and tDispatchInfo.socialHandleStatus != ''">

					</if>
					<if test="tDispatchInfo.socialHandleStatus == null or tDispatchInfo.socialHandleStatus == ''">
						and (( a.type = '0' AND b.HANDLE_STATUS in ('0','4')) or ( a.type ='1'  AND b.HANDLE_STATUS in ('1','5')  and b.REDUCE_CAN = '1'))
						and a.SOCIAL_HANDLE_STATUS in ('0','4')
					</if>
				</if>
				/* 公积金办理*/
				<if test=" '1'.toString()==tDispatchInfo.typeSub.trim() ">
					and (( a.type = '0' AND c.HANDLE_STATUS = '0') or ( a.type ='1'  AND c.HANDLE_STATUS = '1' and c.REDUCE_CAN = '1'))
					and a.FUND_HANDLE_STATUS='0'
				</if>
			</if>
		</if>-->


		<if test="tDispatchInfo != null">
			<if test="tDispatchInfo.dispatchItems != null and tDispatchInfo.dispatchItems.size >0">
				AND
				<foreach item="item" index="index" collection="tDispatchInfo.dispatchItems" open="(" separator="or" close=")">
					INSTR(a.DISPATCH_ITEM,#{item})
				</foreach>
			</if>
			<if test="tDispatchInfo.empTypeList != null  and tDispatchInfo.empTypeList.length>0">
				AND a.EMP_TYPE IN
				<foreach collection="tDispatchInfo.empTypeList" item="item" index="index" open="(" separator="," close=")">
					#{item}
				</foreach>
			</if>
			<if test="tDispatchInfo.fundProvince != null and tDispatchInfo.fundProvince.trim() != ''">
				AND a.FUND_PROVINCE = #{tDispatchInfo.fundProvince}
			</if>
			<if test="tDispatchInfo.type != null and tDispatchInfo.type.trim() != ''">
				AND a.TYPE = #{tDispatchInfo.type}
			</if>
			<if test="tDispatchInfo.fundCity != null and tDispatchInfo.fundCity.trim() != ''">
				AND a.FUND_CITY = #{tDispatchInfo.fundCity}
			</if>
			<if test="tDispatchInfo.fundTown != null and tDispatchInfo.fundTown.trim() != ''">
				AND a.FUND_TOWN = #{tDispatchInfo.fundTown}
			</if>
			<if test="tDispatchInfo.empName != null and tDispatchInfo.empName.trim() != ''">
				AND a.EMP_NAME like concat('%',#{tDispatchInfo.empName},'%')
			</if>
			<if test="tDispatchInfo.empIdcard != null and tDispatchInfo.empIdcard.trim() != ''">
				AND a.EMP_IDCARD like concat('%',#{tDispatchInfo.empIdcard},'%')
			</if>
			<if test="tDispatchInfo.status != null and tDispatchInfo.status.trim() != ''">
				AND a.STATUS = #{tDispatchInfo.status}
			</if>

			<if test="tDispatchInfo.settleDomain != null and tDispatchInfo.settleDomain.trim() != ''">
				AND a.SETTLE_DOMAIN = #{tDispatchInfo.settleDomain}
			</if>

			<if test="tDispatchInfo.settleDomainCode != null and tDispatchInfo.settleDomainCode.trim() != ''">
				AND a.SETTLE_DOMAIN_CODE = #{tDispatchInfo.settleDomainCode}
			</if>

			<if test="tDispatchInfo.socialHandleStatus != null and tDispatchInfo.socialHandleStatus.trim() != ''">
				AND a.SOCIAL_HANDLE_STATUS = #{tDispatchInfo.socialHandleStatus}
			</if>

			<if test="tDispatchInfo.createName != null and tDispatchInfo.createName.trim() != ''">
				AND a.CREATE_NAME = #{tDispatchInfo.createName}
			</if>

			<if test="tDispatchInfo.settleDomainName != null and tDispatchInfo.settleDomainName.trim() != ''">
				AND a.SETTLE_DOMAIN_NAME like CONCAT(CONCAT(#{tDispatchInfo.settleDomainName}), '%')
			</if>
			<if test="tDispatchInfo.belongUnitName != null and tDispatchInfo.belongUnitName.trim() != ''">
				AND a.BELONG_UNIT_NAME = #{tDispatchInfo.belongUnitName}
			</if>
			<if test="tDispatchInfo.createTimeEnd != null">
				AND a.CREATE_TIME <![CDATA[<=]]> #{tDispatchInfo.createTimeEnd}
			</if>
			<if test="tDispatchInfo.createTimeStart != null">
				AND a.CREATE_TIME <![CDATA[>=]]> #{tDispatchInfo.createTimeStart}
			</if>
			<if test="tDispatchInfo.auditTimeStart != null">
				AND a.AUDIT_TIME <![CDATA[>=]]> #{tDispatchInfo.auditTimeStart}
			</if>
			<if test="tDispatchInfo.auditTimeEnd != null">
				AND a.AUDIT_TIME <![CDATA[<=]]> #{tDispatchInfo.auditTimeEnd}
			</if>
			<if test="tDispatchInfo.auditUserName != null and tDispatchInfo.auditUserName.trim() != ''">
				AND a.AUDIT_USER_NAME like concat('%',#{tDispatchInfo.auditUserName},'%')
			</if>
			<if test="tDispatchInfo.providentHouseholdName != null and tDispatchInfo.providentHouseholdName.trim() != ''">
				AND a.PROVIDENT_HOUSEHOLD_NAME like CONCAT(CONCAT(#{tDispatchInfo.providentHouseholdName}), '%')
			</if>
			<if test="tDispatchInfo.authSql != null and tDispatchInfo.authSql.trim() != ''">
				${tDispatchInfo.authSql}
			</if>
			<if test="tDispatchInfo.exportFundFlag != null and tDispatchInfo.exportFundFlag.trim() != ''">
				AND a.EXPORT_FUND_FLAG = ${tDispatchInfo.exportFundFlag}
			</if>
		</if>
	</sql>

	<sql id="where_getSocialDisRecord">
		a.DELETE_FLAG = 0
		AND a.social_id is not null

		<if test="tDispatchInfo.socialId != null and tDispatchInfo.socialId.trim() != ''">
			AND a.SOCIAL_ID = #{tDispatchInfo.socialId}
		</if>
		<if test="tDispatchInfo.syFlag != null and tDispatchInfo.syFlag.trim() == '1'">
			AND a.STATUS = '2'
		</if>
		<!-- dispatch-status 未提交1待审核2审核通过3审核不通过4办理完成5待SSC审核 -->
		<if test='tDispatchInfo.auditStatus != null and tDispatchInfo.auditStatus.trim() == "0" '>
			AND a.STATUS in ('2','4')
		</if>
		<if test='tDispatchInfo.auditStatus != null and tDispatchInfo.auditStatus.trim() == "1" '>
			AND a.STATUS in ('1','5')
		</if>
		AND a.DISPATCH_ITEM like concat('%','医疗','%')
		<if test="tDispatchInfo != null">
			<if test="tDispatchInfo.socialHouseholdName != null and tDispatchInfo.socialHouseholdName.trim() != ''">
				AND a.SOCIAL_HOUSEHOLD_NAME = #{tDispatchInfo.socialHouseholdName}
			</if>
			<if test="tDispatchInfo.type != null and tDispatchInfo.type.trim() != ''">
				AND a.TYPE = #{tDispatchInfo.type}
			</if>
			<if test="tDispatchInfo.disMonth != null and tDispatchInfo.disMonth.trim() != ''">
				AND replace(DATE_FORMAT(a.CREATE_TIME,"%Y-%m"),'-','') = #{tDispatchInfo.disMonth}
			</if>
			<if test="tDispatchInfo.socialHandleStatus != null and tDispatchInfo.socialHandleStatus.trim() != '' and tDispatchInfo.socialHandleStatus != 1">
				AND a.SOCIAL_HANDLE_STATUS = #{tDispatchInfo.socialHandleStatus}
			</if>
			<if test="tDispatchInfo.socialHandleStatus != null and tDispatchInfo.socialHandleStatus.trim() != '' and tDispatchInfo.socialHandleStatus == 1">
				AND a.SOCIAL_HANDLE_STATUS in ('1','3')
			</if>
			<if test="tDispatchInfo.authSql != null and tDispatchInfo.authSql.trim() != ''">
				${tDispatchInfo.authSql}
			</if>
		</if>
	</sql>

	<sql id="where_getFundDisRecord">
		a.DELETE_FLAG = 0
		AND a.fund_id is not null
		AND a.DISPATCH_ITEM like concat('%','公积金','%')
		<if test="tDispatchInfo != null">
			<!-- dispatch-status 未提交1待审核2审核通过3审核不通过4办理完成5待SSC审核 -->
			<if test='tDispatchInfo.auditStatus != null and tDispatchInfo.auditStatus.trim() == "0" '>
				AND a.STATUS in ('2','4')
			</if>
			<if test='tDispatchInfo.auditStatus != null and tDispatchInfo.auditStatus.trim() == "1" '>
				AND a.STATUS in ('1','5')
			</if>
			<if test="tDispatchInfo.providentHouseholdName != null and tDispatchInfo.providentHouseholdName.trim() != ''">
				AND a.PROVIDENT_HOUSEHOLD_NAME = #{tDispatchInfo.providentHouseholdName}
			</if>
			<if test="tDispatchInfo.type != null and tDispatchInfo.type.trim() != ''">
				AND a.TYPE = #{tDispatchInfo.type}
			</if>
			<if test="tDispatchInfo.disMonth != null and tDispatchInfo.disMonth.trim() != ''">
				AND replace(DATE_FORMAT(a.CREATE_TIME,"%Y-%m"),'-','') = #{tDispatchInfo.disMonth}
			</if>
			<if test="tDispatchInfo.fundHandleStatus != null and tDispatchInfo.fundHandleStatus.trim() != ''">
				AND a.FUND_HANDLE_STATUS = #{tDispatchInfo.fundHandleStatus}
			</if>
			<if test="tDispatchInfo.authSql != null and tDispatchInfo.authSql.trim() != ''">
				${tDispatchInfo.authSql}
			</if>
		</if>

	</sql>
	<sql id="where_getFund">
		a.DELETE_FLAG = 0
		AND a.fund_id is not null
		AND ((PERIOD_DIFF(#{tDispatchInfo.disMonth},REPLACE ( DATE_FORMAT( s.PROVIDENT_START, "%Y-%m" ), '-', '' )))+1) >0
		AND a.DISPATCH_ITEM like concat('%','公积金','%')
		AND a.Type=0
		<if test="tDispatchInfo != null">
			<!-- dispatch-status 未提交1待审核2审核通过3审核不通过4办理完成5待SSC审核 -->
			<if test='tDispatchInfo.auditStatus != null and tDispatchInfo.auditStatus.trim() == "0" '>
				AND a.STATUS in ('2','4')
			</if>
			<if test='tDispatchInfo.auditStatus != null and tDispatchInfo.auditStatus.trim() == "1" '>
				AND a.STATUS in ('1','5')
			</if>
			<if test="tDispatchInfo.providentHouseholdName != null and tDispatchInfo.providentHouseholdName.trim() != ''">
				AND a.PROVIDENT_HOUSEHOLD_NAME = #{tDispatchInfo.providentHouseholdName}
			</if>
			<if test="tDispatchInfo.type != null and tDispatchInfo.type.trim() != ''">
				AND a.TYPE = #{tDispatchInfo.type}
			</if>
			<if test="tDispatchInfo.disMonth != null and tDispatchInfo.disMonth.trim() != ''">
				AND replace(DATE_FORMAT(a.CREATE_TIME,"%Y-%m"),'-','') = #{tDispatchInfo.disMonth}
			</if>
			<if test="tDispatchInfo.fundHandleStatus != null and tDispatchInfo.fundHandleStatus.trim() != ''">
				AND a.FUND_HANDLE_STATUS = #{tDispatchInfo.fundHandleStatus}
			</if>
			<if test="tDispatchInfo.authSql != null and tDispatchInfo.authSql.trim() != ''">
				${tDispatchInfo.authSql}
			</if>
		</if>

	</sql>

	<sql id="where_getSocialPersionDisRecord">
		a.DELETE_FLAG = 0
		AND a.social_id is not null
		<if test="tDispatchInfo.socialId != null and tDispatchInfo.socialId.trim() != ''">
			AND a.SOCIAL_ID = #{tDispatchInfo.socialId}
		</if>
		<if test="tDispatchInfo.syFlag != null and tDispatchInfo.syFlag.trim() == '1'">
			AND a.STATUS = '2'
		</if>

		<!-- dispatch-status 未提交1待审核2审核通过3审核不通过4办理完成5待SSC审核 -->
		<if test='tDispatchInfo.auditStatus != null and tDispatchInfo.auditStatus.trim() == "0"'>
			AND a.STATUS in ('2','4')
		</if>
		<if test='tDispatchInfo.auditStatus != null and tDispatchInfo.auditStatus.trim() == "1"'>
			AND a.STATUS in ('1','5')
		</if>

		AND (a.DISPATCH_ITEM like concat('%','工伤','%') or a.DISPATCH_ITEM like concat('%','养老','%') or a.DISPATCH_ITEM like concat('%','失业','%'))
		<if test="tDispatchInfo != null">
			<if test="tDispatchInfo.socialHouseholdName != null and tDispatchInfo.socialHouseholdName.trim() != ''">
				AND a.SOCIAL_HOUSEHOLD_NAME = #{tDispatchInfo.socialHouseholdName}
			</if>
			<if test="tDispatchInfo.type != null and tDispatchInfo.type.trim() != ''">
				AND a.TYPE = #{tDispatchInfo.type}
			</if>
			<if test="tDispatchInfo.disMonth != null and tDispatchInfo.disMonth.trim() != ''">
				AND replace(DATE_FORMAT(a.CREATE_TIME,"%Y-%m"),'-','') = #{tDispatchInfo.disMonth}
			</if>
			<if test="tDispatchInfo.socialHandleStatus != null and tDispatchInfo.socialHandleStatus.trim() != '' and tDispatchInfo.socialHandleStatus != 1">
				AND a.SOCIAL_HANDLE_STATUS = #{tDispatchInfo.socialHandleStatus}
			</if>
			<if test="tDispatchInfo.socialHandleStatus != null and tDispatchInfo.socialHandleStatus.trim() != '' and tDispatchInfo.socialHandleStatus == 1">
				AND a.SOCIAL_HANDLE_STATUS in ('1','3')
			</if>
			<if test="tDispatchInfo.authSql != null and tDispatchInfo.authSql.trim() != ''">
				${tDispatchInfo.authSql}
			</if>
		</if>
	</sql>


	<!-- 获取当日最大的编码 -->
	<select id="getMaxDispatchCode" resultType="java.lang.String">
		SELECT ifnull(max(right(e.APPLY_NO,4)),0) APPLY_NO FROM t_dispatch_info e where e.DELETE_FLAG = 0 and e.CREATE_TIME>DATE_FORMAT(now(),'%Y-%m-%d')
	</select>

	<update id="updateExportSocialFlagById">
		update  t_dispatch_info a set a.EXPORT_SOCIAL_FLAG = #{status} WHERE a.id = #{id}
	</update>
	<update id="updateExportFundFlagById">
		update  t_dispatch_info a set a.EXPORT_FUND_FLAG = #{status} WHERE a.id = #{id}
	</update>

	<!--tDispatchInfo 社保医疗花名册派单数据-->
	<select id="getFundPersionExportVo" resultMap="FundExport">
		SELECT
		(@i :=@i+1) as NUM,
		a.EMP_NAME,
		a.EMP_IDCARD,
		s.UNIT_PROVIDENG_CARDINAL AS DEPOSIT_BASE ,
		IF(a.TYPE=0 ,s.UNIT_PROVIDENT_FEE*2,NULL) as  MOTHLY_INCREASE,
		IF(a.TYPE=1 ,s.UNIT_PROVIDENT_FEE*2,NULL) as  MOTHLY_DECREASE,
		case when a.TYPE=0 then "新增"
		else "减少" end as CAUSE_CHANGE
		FROM t_dispatch_info a
		left join t_provident_fund s on s.id=a.FUND_ID
		<where>
			<include refid="where_getFundDisRecord"/>
		</where>
		ORDER BY
		a.TYPE

	</select>

	<!--tDispatchInfo 社保医疗花名册派单数据-->
	<select id="getFundSupplementarynExportVo" resultMap="FundSupplementaryExport">

		SELECT
		( @i := @i + 1 ) AS NUM,
		a.EMP_NAME,
		a.EMP_IDCARD,
		s.UNIT_PROVIDENG_CARDINAL AS DEPOSIT_BASE,
		REPLACE ( DATE_FORMAT( s.PROVIDENT_START, "%Y-%m" ), '-', '' ) AS ORIGINATE,
		#{tDispatchInfo.disMonth} AS END,
		ROUND(((PERIOD_DIFF(#{tDispatchInfo.disMonth},REPLACE ( DATE_FORMAT( s.PROVIDENT_START, "%Y-%m" ), '-', '' )))+1)*s.UNIT_PROVIDENT_FEE*2 ) AS SUPPLEMENTART_AMOUNT
		FROM
		t_dispatch_info a
		LEFT JOIN t_provident_fund s ON s.id = a.FUND_ID,(
		SELECT
		@i := 0
		) AS itable
		<where>
			<include refid="where_getFund"/>
		</where>
	</select>
	<select id="getFundSumExportVo" resultType="com.yifu.cloud.plus.v1.yifu.social.vo.TDispatchFundSumExportVo">
		SELECT
		sum(h.NUM_ONE) as NUM_ONE,
		sum(h.NUM_TWO) as NUM_TWO,
		sum(h.MONENY_ONE) AS MONENY_ONE ,
		sum(h.MONENY_TWO)  AS MONENY_TWO
		FROM(
		SELECT
		IF(a.TYPE=1, 1,0) as NUM_ONE ,
		IF(a.TYPE=0, 1,0) as NUM_TWO,
		SUM(IF(a.TYPE=0 ,s.UNIT_PROVIDENT_FEE*2,NULL)) AS MONENY_ONE ,
		SUM(IF(a.TYPE=1 ,s.UNIT_PROVIDENT_FEE*2,NULL)) AS MONENY_TWO
		FROM t_dispatch_info a
		left join t_provident_fund s on s.id=a.FUND_ID
		<where>
			<include refid="where_getFundDisRecord"/>
		</where>
		GROUP BY
		a.EMP_IDCARD,
		a.TYPE
		ORDER BY
		a.TYPE
		)h, (select @i:=0) as itable
	</select>
	<select id="getFundSumExportVoOne" resultMap="FundSupplementary">
		SELECT
		count(a.EMP_IDCARD) as NUMBER,
		SUM(ROUND(((PERIOD_DIFF(#{tDispatchInfo.disMonth},REPLACE ( DATE_FORMAT( s.PROVIDENT_START, "%Y-%m" ), '-', '' )))+1)*s.UNIT_PROVIDENT_FEE*2 ))  AS SUPPLEMENTART_AMOUNT_SUM
		FROM t_dispatch_info a
		left join t_provident_fund s on s.id = a.FUND_ID
		<where>
			<include refid="where_getFund"/>
		</where>
	</select>

	<select id="getFundPersionExportVoOne" resultMap="CompanyProVo">
		SELECT
			F.COMPANY_PRO AS COMPANY_PRO
		FROM `sys_house_hold_info` A
				 LEFT JOIN sys_area B ON A.PROVINCE = B.ID
				 LEFT JOIN sys_area C ON A.CITY = C.ID
				 LEFT JOIN sys_area D ON A.TOWN = D.ID
				 LEFT JOIN sys_base_set_info E ON A.ID = E.DEPART_ID
				 LEFT JOIN sys_pay_proportion F ON E.ID = F.SYS_BASE_SET_ID
		WHERE A.TYPE = '1' AND A.`NAME` IN  (#{houseName})
		GROUP BY A.`NAME`
	</select>


	<select id="getFundSumExportVoTwo" resultMap="HouseNameConfigVo">
		SELECT
			A.CUSTOMER_NO,
			A.FUND_BANK,
			F.COMPANY_PRO AS COMPANY_PRO
		FROM
			`sys_house_hold_info` A
				LEFT JOIN sys_area B ON A.PROVINCE = B.ID
				LEFT JOIN sys_area C ON A.CITY = C.ID
				LEFT JOIN sys_area D ON A.TOWN = D.ID
				LEFT JOIN sys_base_set_info E ON A.ID = E.DEPART_ID
				LEFT JOIN sys_pay_proportion F ON E.ID = F.SYS_BASE_SET_ID
		WHERE
			A.TYPE = '1'
		  AND A.`NAME` IN (#{houseName})
		  AND E.`STATUS` = '0'
		  AND
				(PERIOD_DIFF(#{tDispatchInfo.disMonth},REPLACE ( DATE_FORMAT( E.APPLY_START_DATE , "%Y-%m" ), '-', '' )))>0
		  and
			((PERIOD_DIFF(#{tDispatchInfo.disMonth},REPLACE ( DATE_FORMAT( E.APPLY_END_DATE , "%Y-%m" ), '-', '' ))) <![CDATA[ < ]]>0
				OR ( APPLY_END_DATE IS NULL OR APPLY_END_DATE = '' ))
		GROUP BY A.`NAME`
	</select>
	<resultMap id="autoFlagMap" type="com.yifu.cloud.plus.v1.yifu.social.vo.AutoFlagVo">
		<result property="dispatchId" column="dispatchId"/>
		<result property="autoFlag" column="autoFlag"/>
	</resultMap>
	<select id="getAutoFlag" resultMap="autoFlagMap">
		select a.id as "dispatchId",
		b.AUTO_STATUS as 'autoFlag'
		FROM  t_dispatch_info a
		LEFT JOIN sys_house_hold_info b  on a.SOCIAL_HOUSEHOLD_NAME=b.`NAME` and b.TYPE='0'
		where a.SOCIAL_HOUSEHOLD_NAME is not null
		and a.id in
		<foreach item="item" index="index" collection="idsList" open="(" separator="," close=")">
			#{item}
		</foreach>
	</select>
</mapper>
