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

  <resultMap id="tSocialInfoMap" type="com.yifu.cloud.plus.v1.yifu.social.entity.TSocialInfo">
    <id property="id" column="ID"/>
    <result property="socialHousehold" column="SOCIAL_HOUSEHOLD"/>
	  <result property="socialHouseholdName" column="SOCIAL_HOUSEHOLD_NAME"/>
    <result property="socialStartDate" column="SOCIAL_START_DATE"/>
    <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="personalPensionCardinal" column="PERSONAL_PENSION_CARDINAL"/>
    <result property="personalMedicalCardinal" column="PERSONAL_MEDICAL_CARDINAL"/>
    <result property="personalBigailmentCardinal" column="PERSONAL_BIGAILMENT_CARDINAL"/>
    <result property="personalUnemploymentCardinal" column="PERSONAL_UNEMPLOYMENT_CARDINAL"/>
    <result property="empId" column="EMP_ID"/>
    <result property="empNo" column="EMP_NO"/>
    <result property="empName" column="EMP_NAME"/>
    <result property="empIdcard" column="EMP_IDCARD"/>
    <result property="unitPensionPer" column="UNIT_PENSION_PER"/>
    <result property="unitMedicalPer" column="UNIT_MEDICAL_PER"/>
    <result property="unitUnemploymentPer" column="UNIT_UNEMPLOYMENT_PER"/>
    <result property="unitWorkUnjuryPer" column="UNIT_WORK_UNJURY_PER"/>
    <result property="unitBirthPer" column="UNIT_BIRTH_PER"/>
    <result property="personalPensionPer" column="PERSONAL_PENSION_PER"/>
    <result property="personalMedicalPer" column="PERSONAL_MEDICAL_PER"/>
    <result property="personalUnemploymentPer" column="PERSONAL_UNEMPLOYMENT_PER"/>
    <result property="auditStatus" column="AUDIT_STATUS"/>
    <result property="handleStatus" column="HANDLE_STATUS"/>
    <result property="draftStatus" column="DRAFT_STATUS"/>
    <result property="cardinalId" column="CARDINAL_ID"/>
    <result property="oldId" column="OLD_ID"/>
    <result property="settleDomain" column="SETTLE_DOMAIN"/>
    <result property="settleDomainCode" column="SETTLE_DOMAIN_CODE"/>
    <result property="socialProvince" column="SOCIAL_PROVINCE"/>
    <result property="socialCity" column="SOCIAL_CITY"/>
    <result property="socialTown" column="SOCIAL_TOWN"/>
    <result property="remark" column="REMARK"/>
    <result property="deleteFlag" column="DELETE_FLAG"/>
    <result property="unitPersionMoney" column="UNIT_PERSION_MONEY"/>
    <result property="unitMedicalMoney" column="UNIT_MEDICAL_MONEY"/>
    <result property="unitUnemploymentMoney" column="UNIT_UNEMPLOYMENT_MONEY"/>
    <result property="unitInjuryMoney" column="UNIT_INJURY_MONEY"/>
    <result property="unitBirthMoney" column="UNIT_BIRTH_MONEY"/>
    <result property="unitBigailmentMoney" column="UNIT_BIGAILMENT_MONEY"/>
    <result property="personalPersionMoney" column="PERSONAL_PERSION_MONEY"/>
    <result property="personalMedicalMoney" column="PERSONAL_MEDICAL_MONEY"/>
    <result property="personalUnemploymentMoney" column="PERSONAL_UNEMPLOYMENT_MONEY"/>
    <result property="personalBigailmentMoney" column="PERSONAL_BIGAILMENT_MONEY"/>
    <result property="valueType" column="VALUE_TYPE"/>
    <result property="personalBigailmentPer" column="PERSONAL_BIGAILMENT_PER"/>
    <result property="unitBigailmentPer" column="UNIT_BIGAILMENT_PER"/>
    <result property="isIllness" column="IS_ILLNESS"/>
    <result property="collectMoth" column="COLLECT_MOTH"/>
    <result property="collectType" column="COLLECT_TYPE"/>
    <result property="processInstId" column="PROCESS_INST_ID"/>
    <result property="auditUser" column="AUDIT_USER"/>
	  <result property="auditUserName" column="AUDIT_USER_NAME"/>
    <result property="auditTime" column="AUDIT_TIME"/>
    <result property="handleUser" column="HANDLE_USER"/>
	  <result property="handleUserName" column="HANDLE_USER_NAME"/>
    <result property="handleTime" column="HANDLE_TIME"/>
    <result property="belongUnit" column="BELONG_UNIT"/>
    <result property="socialReduceDate" column="SOCIAL_REDUCE_DATE"/>
    <result property="canOverpay" column="CAN_OVERPAY"/>
    <result property="overpayNumber" column="OVERPAY_NUMBER"/>
    <result property="haveThisMonth" column="HAVE_THIS_MONTH"/>
    <result property="insurancePension" column="INSURANCE_PENSION"/>
    <result property="insuranceMedical" column="INSURANCE_MEDICAL"/>
    <result property="insuranceUnemployment" column="INSURANCE_UNEMPLOYMENT"/>
    <result property="insuranceInjury" column="INSURANCE_INJURY"/>
    <result property="insuranceBirth" column="INSURANCE_BIRTH"/>
    <result property="insuranceBigailment" column="INSURANCE_BIGAILMENT"/>
    <result property="insuranceIsLatestCardinality" column="INSURANCE_IS_LATEST_CARDINALITY"/>
    <result property="reduceHandleUser" column="REDUCE_HANDLE_USER"/>
    <result property="reduceHandleTime" column="REDUCE_HANDLE_TIME"/>
    <result property="isChargePersonal" column="IS_CHARGE_PERSONAL"/>
    <result property="trustRemark" column="TRUST_REMARK"/>
    <result property="reduceCan" column="REDUCE_CAN"/>
    <result property="inauguralTeam" column="INAUGURAL_TEAM"/>
    <result property="telecomNumber" column="TELECOM_NUMBER"/>
    <result property="paymentType" column="PAYMENT_TYPE"/>
    <result property="recordBase" column="RECORD_BASE"/>
    <result property="socialType" column="SOCIAL_TYPE"/>
    <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="pensionReduceStart" column="PENSION_REDUCE_START"/>
	<result property="medicalReduceStart" column="MEDICAL_REDUCE_START"/>
	<result property="unemployReduceStart" column="UNEMPLOY_REDUCE_START"/>
	<result property="workInjuryReduceStart" column="WORK_INJURY_REDUCE_START"/>
	<result property="birthReduceStart" column="BIRTH_REDUCE_START"/>
	<result property="bigailmentReduceStart" column="BIGAILMENT_REDUCE_START"/>
    <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="bigailmentHandle" column="BIGAILMENT_HANDLE"/>
    <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="payPolicy" column="PAY_POLICY"/>
	<result property="lowerLimit" column="LOWER_LIMIT"/>
	<result property="upperLimit" column="UPPER_LIMIT"/>
	<result property="unitSocialSum" column="UNIT_SOCIAL_SUM"/>
	<result property="personalSocialSum" column="PERSONAL_SOCIAL_SUM"/>

	  <result property="firstPayTime" column="FIRST_PAY_TIME"/>

	  <!-- 社保士兵新增字段： -->
	  <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"/>
	  <result property="ygsAddId" column="YGS_ADD_ID"/>
	  <result property="ysdAddId" column="YSD_ADD_ID"/>
	  <result property="dispatchId" column="DISPATCH_ID"/>
	  <result property="dispatchType" column="DISPATCH_TYPE"/>
	  <result property="ygsOverDueFlag" column="YGS_OVER_DUE_FLAG"/>
	  <result property="ysdOverDueFlag" column="YSD_OVER_DUE_FLAG"/>

	  <!-- 税友新增字段-->
	  <result property="ygsRequestId" column="YGS_REQUEST_ID"/>
	  <result property="ysdRequestId" column="YSD_REQUEST_ID"/>
	  <result property="ysdSocialStartDateNext" column="YSD_SOCIAL_START_DATE_NEXT"/>
	  <result property="sfqztb" column="SFQZTB"/>

	  <result property="ygsHandleStatusOld" column="YGS_HANDLE_STATUS_OLD"/>
	  <result property="ysdHandleStatusOld" column="YSD_HANDLE_STATUS_OLD"/>
  </resultMap>
    <sql id="Base_Column_List">
                a.ID,
                a.SOCIAL_HOUSEHOLD,
				a.SOCIAL_HOUSEHOLD_NAME,
                a.SOCIAL_START_DATE,
                a.UNIT_PENSION_CARDINAL,
                a.UNIT_MEDICAL_CARDINAL,
                a.UNIT_UNEMPLOYMENT_CARDINAL,
                a.UNIT_BIGAILMENT_CARDINAL,
                a.UNIT_WORK_INJURY_CARDINAL,
                a.UNIT_BIRTH_CARDINAL,
                a.PERSONAL_PENSION_CARDINAL,
                a.PERSONAL_MEDICAL_CARDINAL,
                a.PERSONAL_BIGAILMENT_CARDINAL,
                a.PERSONAL_UNEMPLOYMENT_CARDINAL,
                a.PERSONAL_WORK_INJURY_CARDINAL,
                a.EMP_ID,
                a.EMP_NO,
                a.EMP_NAME,
                a.EMP_IDCARD,
                a.UNIT_PENSION_PER,
                a.UNIT_MEDICAL_PER,
                a.UNIT_UNEMPLOYMENT_PER,
                a.UNIT_WORK_UNJURY_PER,
                a.UNIT_BIRTH_PER,
                a.PERSONAL_PENSION_PER,
                a.PERSONAL_MEDICAL_PER,
                a.PERSONAL_UNEMPLOYMENT_PER,
                a.AUDIT_STATUS,
                a.HANDLE_STATUS,
                a.DRAFT_STATUS,
                a.CARDINAL_IID,
                a.OLD_ID,
                a.SETTLE_DOMAIN,
                a.SOCIAL_PROVINCE,
                a.SOCIAL_CITY,
                a.SOCIAL_TOWN,
                a.REMARK,
                a.DELETE_FLAG,
                a.UNIT_PERSION_MONEY,
                a.UNIT_MEDICAL_MONEY,
                a.UNIT_UNEMPLOYMENT_MONEY,
                a.UNIT_INJURY_MONEY,
                a.UNIT_BIRTH_MONEY,
                a.UNIT_BIGAILMENT_MONEY,
                a.PERSONAL_PERSION_MONEY,
                a.PERSONAL_MEDICAL_MONEY,
                a.PERSONAL_UNEMPLOYMENT_MONEY,
                a.PERSONAL_BIGAILMENT_MONEY,
                a.VALUE_TYPE,
                a.PERSONAL_BIGAILMENT_PER,
                a.UNIT_BIGAILMENT_PER,
                a.IS_ILLNESS,
                a.COLLECT_MOTH,
                a.COLLECT_TYPE,
                a.PROCESS_INST_ID,
                a.AUDIT_USER,
                a.AUDIT_TIME,
                a.HANDLE_USER,
                a.HANDLE_TIME,
                a.BELONG_UNIT,
                a.SOCIAL_REDUCE_DATE,
                a.CAN_OVERPAY,
                a.OVERPAY_NUMBER,
                a.HAVE_THIS_MONTH,
                a.INSURANCE_PENSION,
                a.INSURANCE_MEDICAL,
                a.INSURANCE_UNEMPLOYMENT,
                a.INSURANCE_INJURY,
                a.INSURANCE_BIRTH,
                a.INSURANCE_BIGAILMENT,
                a.INSURANCE_IS_LATEST_CARDINALITY,
                a.REDUCE_HANDLE_USER,
                a.REDUCE_HANDLE_TIME,
                a.IS_CHARGE_PERSONAL,
                a.TRUST_REMARK,
                a.REDUCE_CAN,
                a.INAUGURAL_TEAM,
                a.TELECOM_NUMBER,
                a.PAYMENT_TYPE,
                a.RECORD_BASE,
                a.SOCIAL_TYPE,
                a.BIGAILMENT_VARY_FLAG,
                a.PENSION_START,
                a.MEDICAL_START,
                a.UNEMPLOY_START,
                a.WORK_INJURY_START,
                a.BIRTH_START,
                a.BIGAILMENT_START,
                a.PENSION_HANDLE,
                a.MEDICAL_HANDLE,
                a.UNEMPLOY_HANDLE,
                a.WORK_INJURY_HANDLE,
                a.BIRTH_HANDLE,
                a.BIGAILMENT_HANDLE,
                a.CREATE_BY,
                a.UPDATE_BY,
                a.CREATE_NAME,
                a.UPDATE_TIME,
				a.PAY_POLICY,
				a.LOWER_LIMIT,
				a.UPPER_LIMIT,a.UNIT_SOCIAL_SUM,a.PERSONAL_SOCIAL_SUM,
				a.FIRST_PAY_TIME,

				a.YGS_HANDLE_STATUS,
				a.YSD_HANDLE_STATUS,
				a.YGS_REMARK,
				a.YSD_REMARK,
				a.YGS_REMARK_OLD,
				a.YSD_REMARK_OLD,
				a.YGS_ADD_ID,
				a.YSD_ADD_ID,
				a.YGS_REQUEST_ID,
				a.YSD_REQUEST_ID,
				a.YSD_SOCIAL_START_DATE_NEXT,
				a.YGS_HANDLE_STATUS_OLD,
				a.YSD_HANDLE_STATUS_OLD,
				a.SFQZTB

    </sql>
    <sql id="tSocialInfo_where">
        <if test="tSocialInfo != null">
                    <if test="tSocialInfo.id != null and tSocialInfo.id.trim() != ''">
                        AND a.ID = #{tSocialInfo.id}
                    </if>
                    <if test="tSocialInfo.socialHousehold != null and tSocialInfo.socialHousehold.trim() != ''">
                        AND a.SOCIAL_HOUSEHOLD = #{tSocialInfo.socialHousehold}
                    </if>
                    <if test="tSocialInfo.socialStartDate != null">
                        AND a.SOCIAL_START_DATE = #{tSocialInfo.socialStartDate}
                    </if>
                    <if test="tSocialInfo.unitPensionCardinal != null">
                        AND a.UNIT_PENSION_CARDINAL = #{tSocialInfo.unitPensionCardinal}
                    </if>
                    <if test="tSocialInfo.unitMedicalCardinal != null">
                        AND a.UNIT_MEDICAL_CARDINAL = #{tSocialInfo.unitMedicalCardinal}
                    </if>
                    <if test="tSocialInfo.unitUnemploymentCardinal != null">
                        AND a.UNIT_UNEMPLOYMENT_CARDINAL = #{tSocialInfo.unitUnemploymentCardinal}
                    </if>
                    <if test="tSocialInfo.unitBigailmentCardinal != null">
                        AND a.UNIT_BIGAILMENT_CARDINAL = #{tSocialInfo.unitBigailmentCardinal}
                    </if>
                    <if test="tSocialInfo.unitWorkInjuryCardinal != null">
                        AND a.UNIT_WORK_INJURY_CARDINAL = #{tSocialInfo.unitWorkInjuryCardinal}
                    </if>
                    <if test="tSocialInfo.unitBirthCardinal != null">
                        AND a.UNIT_BIRTH_CARDINAL = #{tSocialInfo.unitBirthCardinal}
                    </if>
                    <if test="tSocialInfo.personalPensionCardinal != null">
                        AND a.PERSONAL_PENSION_CARDINAL = #{tSocialInfo.personalPensionCardinal}
                    </if>
                    <if test="tSocialInfo.personalMedicalCardinal != null">
                        AND a.PERSONAL_MEDICAL_CARDINAL = #{tSocialInfo.personalMedicalCardinal}
                    </if>
                    <if test="tSocialInfo.personalBigailmentCardinal != null">
                        AND a.PERSONAL_BIGAILMENT_CARDINAL = #{tSocialInfo.personalBigailmentCardinal}
                    </if>
                    <if test="tSocialInfo.personalUnemploymentCardinal != null">
                        AND a.PERSONAL_UNEMPLOYMENT_CARDINAL = #{tSocialInfo.personalUnemploymentCardinal}
                    </if>
                    <if test="tSocialInfo.personalWorkInjuryCardinal != null">
                        AND a.PERSONAL_WORK_INJURY_CARDINAL = #{tSocialInfo.personalWorkInjuryCardinal}
                    </if>
                    <if test="tSocialInfo.empId != null and tSocialInfo.empId.trim() != ''">
                        AND a.EMP_ID = #{tSocialInfo.empId}
                    </if>
                    <if test="tSocialInfo.empNo != null and tSocialInfo.empNo.trim() != ''">
                        AND a.EMP_NO = #{tSocialInfo.empNo}
                    </if>
                    <if test="tSocialInfo.empName != null and tSocialInfo.empName.trim() != ''">
                        AND a.EMP_NAME = #{tSocialInfo.empName}
                    </if>
                    <if test="tSocialInfo.empIdcard != null and tSocialInfo.empIdcard.trim() != ''">
                        AND a.EMP_IDCARD = #{tSocialInfo.empIdcard}
                    </if>
                    <if test="tSocialInfo.unitPensionPer != null">
                        AND a.UNIT_PENSION_PER = #{tSocialInfo.unitPensionPer}
                    </if>
                    <if test="tSocialInfo.unitMedicalPer != null">
                        AND a.UNIT_MEDICAL_PER = #{tSocialInfo.unitMedicalPer}
                    </if>
                    <if test="tSocialInfo.unitUnemploymentPer != null">
                        AND a.UNIT_UNEMPLOYMENT_PER = #{tSocialInfo.unitUnemploymentPer}
                    </if>
                    <if test="tSocialInfo.unitWorkUnjuryPer != null">
                        AND a.UNIT_WORK_UNJURY_PER = #{tSocialInfo.unitWorkUnjuryPer}
                    </if>
                    <if test="tSocialInfo.unitBirthPer != null">
                        AND a.UNIT_BIRTH_PER = #{tSocialInfo.unitBirthPer}
                    </if>
                    <if test="tSocialInfo.personalPensionPer != null">
                        AND a.PERSONAL_PENSION_PER = #{tSocialInfo.personalPensionPer}
                    </if>
                    <if test="tSocialInfo.personalMedicalPer != null">
                        AND a.PERSONAL_MEDICAL_PER = #{tSocialInfo.personalMedicalPer}
                    </if>
                    <if test="tSocialInfo.personalUnemploymentPer != null">
                        AND a.PERSONAL_UNEMPLOYMENT_PER = #{tSocialInfo.personalUnemploymentPer}
                    </if>
                    <if test="tSocialInfo.auditStatus != null and tSocialInfo.auditStatus.trim() != ''">
                        AND a.AUDIT_STATUS = #{tSocialInfo.auditStatus}
                    </if>
                    <if test="tSocialInfo.handleStatus != null and tSocialInfo.handleStatus.trim() != ''">
                        AND a.HANDLE_STATUS = #{tSocialInfo.handleStatus}
                    </if>
                    <if test="tSocialInfo.draftStatus != null and tSocialInfo.draftStatus.trim() != ''">
                        AND a.DRAFT_STATUS = #{tSocialInfo.draftStatus}
                    </if>
                    <if test="tSocialInfo.cardinalId != null and tSocialInfo.cardinalId.trim() != ''">
                        AND a.CARDINAL_IID = #{tSocialInfo.cardinalId}
                    </if>
                    <if test="tSocialInfo.oldId != null">
                        AND a.OLD_ID = #{tSocialInfo.oldId}
                    </if>
                    <if test="tSocialInfo.settleDomain != null and tSocialInfo.settleDomain.trim() != ''">
                        AND a.SETTLE_DOMAIN = #{tSocialInfo.settleDomain}
                    </if>
                    <if test="tSocialInfo.socialProvince != null and tSocialInfo.socialProvince.trim() != ''">
                        AND a.SOCIAL_PROVINCE = #{tSocialInfo.socialProvince}
                    </if>
                    <if test="tSocialInfo.socialCity != null and tSocialInfo.socialCity.trim() != ''">
                        AND a.SOCIAL_CITY = #{tSocialInfo.socialCity}
                    </if>
                    <if test="tSocialInfo.socialTown != null and tSocialInfo.socialTown.trim() != ''">
                        AND a.SOCIAL_TOWN = #{tSocialInfo.socialTown}
                    </if>
                    <if test="tSocialInfo.remark != null and tSocialInfo.remark.trim() != ''">
                        AND a.REMARK = #{tSocialInfo.remark}
                    </if>
                    <if test="tSocialInfo.deleteFlag != null and tSocialInfo.deleteFlag.trim() != ''">
                        AND a.DELETE_FLAG = #{tSocialInfo.deleteFlag}
                    </if>
                    <if test="tSocialInfo.unitPersionMoney != null">
                        AND a.UNIT_PERSION_MONEY = #{tSocialInfo.unitPersionMoney}
                    </if>
                    <if test="tSocialInfo.unitMedicalMoney != null">
                        AND a.UNIT_MEDICAL_MONEY = #{tSocialInfo.unitMedicalMoney}
                    </if>
                    <if test="tSocialInfo.unitUnemploymentMoney != null">
                        AND a.UNIT_UNEMPLOYMENT_MONEY = #{tSocialInfo.unitUnemploymentMoney}
                    </if>
                    <if test="tSocialInfo.unitInjuryMoney != null">
                        AND a.UNIT_INJURY_MONEY = #{tSocialInfo.unitInjuryMoney}
                    </if>
                    <if test="tSocialInfo.unitBirthMoney != null">
                        AND a.UNIT_BIRTH_MONEY = #{tSocialInfo.unitBirthMoney}
                    </if>
                    <if test="tSocialInfo.unitBigailmentMoney != null">
                        AND a.UNIT_BIGAILMENT_MONEY = #{tSocialInfo.unitBigailmentMoney}
                    </if>
                    <if test="tSocialInfo.personalPersionMoney != null">
                        AND a.PERSONAL_PERSION_MONEY = #{tSocialInfo.personalPersionMoney}
                    </if>
                    <if test="tSocialInfo.personalMedicalMoney != null">
                        AND a.PERSONAL_MEDICAL_MONEY = #{tSocialInfo.personalMedicalMoney}
                    </if>
                    <if test="tSocialInfo.personalUnemploymentMoney != null">
                        AND a.PERSONAL_UNEMPLOYMENT_MONEY = #{tSocialInfo.personalUnemploymentMoney}
                    </if>
                    <if test="tSocialInfo.personalBigailmentMoney != null">
                        AND a.PERSONAL_BIGAILMENT_MONEY = #{tSocialInfo.personalBigailmentMoney}
                    </if>
                    <if test="tSocialInfo.valueType != null and tSocialInfo.valueType.trim() != ''">
                        AND a.VALUE_TYPE = #{tSocialInfo.valueType}
                    </if>
                    <if test="tSocialInfo.personalBigailmentPer != null">
                        AND a.PERSONAL_BIGAILMENT_PER = #{tSocialInfo.personalBigailmentPer}
                    </if>
                    <if test="tSocialInfo.unitBigailmentPer != null">
                        AND a.UNIT_BIGAILMENT_PER = #{tSocialInfo.unitBigailmentPer}
                    </if>
                    <if test="tSocialInfo.isIllness != null and tSocialInfo.isIllness.trim() != ''">
                        AND a.IS_ILLNESS = #{tSocialInfo.isIllness}
                    </if>
                    <if test="tSocialInfo.collectMoth != null">
                        AND a.COLLECT_MOTH = #{tSocialInfo.collectMoth}
                    </if>
                    <if test="tSocialInfo.collectType != null and tSocialInfo.collectType.trim() != ''">
                        AND a.COLLECT_TYPE = #{tSocialInfo.collectType}
                    </if>
                    <if test="tSocialInfo.processInstId != null and tSocialInfo.processInstId.trim() != ''">
                        AND a.PROCESS_INST_ID = #{tSocialInfo.processInstId}
                    </if>
                    <if test="tSocialInfo.auditUser != null and tSocialInfo.auditUser.trim() != ''">
                        AND a.AUDIT_USER = #{tSocialInfo.auditUser}
                    </if>
                    <if test="tSocialInfo.auditTime != null">
                        AND a.AUDIT_TIME = #{tSocialInfo.auditTime}
                    </if>
                    <if test="tSocialInfo.handleUser != null and tSocialInfo.handleUser.trim() != ''">
                        AND a.HANDLE_USER = #{tSocialInfo.handleUser}
                    </if>
                    <if test="tSocialInfo.handleTime != null">
                        AND a.HANDLE_TIME = #{tSocialInfo.handleTime}
                    </if>
                    <if test="tSocialInfo.belongUnit != null and tSocialInfo.belongUnit.trim() != ''">
                        AND a.BELONG_UNIT = #{tSocialInfo.belongUnit}
                    </if>
                    <if test="tSocialInfo.socialReduceDate != null">
                        AND a.SOCIAL_REDUCE_DATE = #{tSocialInfo.socialReduceDate}
                    </if>
                    <if test="tSocialInfo.canOverpay != null and tSocialInfo.canOverpay.trim() != ''">
                        AND a.CAN_OVERPAY = #{tSocialInfo.canOverpay}
                    </if>
                    <if test="tSocialInfo.overpayNumber != null">
                        AND a.OVERPAY_NUMBER = #{tSocialInfo.overpayNumber}
                    </if>
                    <if test="tSocialInfo.haveThisMonth != null and tSocialInfo.haveThisMonth.trim() != ''">
                        AND a.HAVE_THIS_MONTH = #{tSocialInfo.haveThisMonth}
                    </if>
                    <if test="tSocialInfo.insurancePension != null and tSocialInfo.insurancePension.trim() != ''">
                        AND a.INSURANCE_PENSION = #{tSocialInfo.insurancePension}
                    </if>
                    <if test="tSocialInfo.insuranceMedical != null and tSocialInfo.insuranceMedical.trim() != ''">
                        AND a.INSURANCE_MEDICAL = #{tSocialInfo.insuranceMedical}
                    </if>
                    <if test="tSocialInfo.insuranceUnemployment != null and tSocialInfo.insuranceUnemployment.trim() != ''">
                        AND a.INSURANCE_UNEMPLOYMENT = #{tSocialInfo.insuranceUnemployment}
                    </if>
                    <if test="tSocialInfo.insuranceInjury != null and tSocialInfo.insuranceInjury.trim() != ''">
                        AND a.INSURANCE_INJURY = #{tSocialInfo.insuranceInjury}
                    </if>
                    <if test="tSocialInfo.insuranceBirth != null and tSocialInfo.insuranceBirth.trim() != ''">
                        AND a.INSURANCE_BIRTH = #{tSocialInfo.insuranceBirth}
                    </if>
                    <if test="tSocialInfo.insuranceBigailment != null and tSocialInfo.insuranceBigailment.trim() != ''">
                        AND a.INSURANCE_BIGAILMENT = #{tSocialInfo.insuranceBigailment}
                    </if>
                    <if test="tSocialInfo.insuranceIsLatestCardinality != null and tSocialInfo.insuranceIsLatestCardinality.trim() != ''">
                        AND a.INSURANCE_IS_LATEST_CARDINALITY = #{tSocialInfo.insuranceIsLatestCardinality}
                    </if>
                    <if test="tSocialInfo.reduceHandleUser != null and tSocialInfo.reduceHandleUser.trim() != ''">
                        AND a.REDUCE_HANDLE_USER = #{tSocialInfo.reduceHandleUser}
                    </if>
                    <if test="tSocialInfo.reduceHandleTime != null">
                        AND a.REDUCE_HANDLE_TIME = #{tSocialInfo.reduceHandleTime}
                    </if>
                    <if test="tSocialInfo.isChargePersonal != null and tSocialInfo.isChargePersonal.trim() != ''">
                        AND a.IS_CHARGE_PERSONAL = #{tSocialInfo.isChargePersonal}
                    </if>
                    <if test="tSocialInfo.trustRemark != null and tSocialInfo.trustRemark.trim() != ''">
                        AND a.TRUST_REMARK = #{tSocialInfo.trustRemark}
                    </if>
                    <if test="tSocialInfo.reduceCan != null and tSocialInfo.reduceCan.trim() != ''">
                        AND a.REDUCE_CAN = #{tSocialInfo.reduceCan}
                    </if>
                    <if test="tSocialInfo.inauguralTeam != null and tSocialInfo.inauguralTeam.trim() != ''">
                        AND a.INAUGURAL_TEAM = #{tSocialInfo.inauguralTeam}
                    </if>
                    <if test="tSocialInfo.telecomNumber != null and tSocialInfo.telecomNumber.trim() != ''">
                        AND a.TELECOM_NUMBER = #{tSocialInfo.telecomNumber}
                    </if>
                    <if test="tSocialInfo.paymentType != null and tSocialInfo.paymentType.trim() != ''">
                        AND a.PAYMENT_TYPE = #{tSocialInfo.paymentType}
                    </if>
                    <if test="tSocialInfo.recordBase != null and tSocialInfo.recordBase.trim() != ''">
                        AND a.RECORD_BASE = #{tSocialInfo.recordBase}
                    </if>
                    <if test="tSocialInfo.socialType != null">
                        AND a.SOCIAL_TYPE = #{tSocialInfo.socialType}
                    </if>
                    <if test="tSocialInfo.bigailmentVaryFlag != null and tSocialInfo.bigailmentVaryFlag.trim() != ''">
                        AND a.BIGAILMENT_VARY_FLAG = #{tSocialInfo.bigailmentVaryFlag}
                    </if>
                    <if test="tSocialInfo.pensionStart != null">
                        AND a.PENSION_START = #{tSocialInfo.pensionStart}
                    </if>
                    <if test="tSocialInfo.medicalStart != null">
                        AND a.MEDICAL_START = #{tSocialInfo.medicalStart}
                    </if>
                    <if test="tSocialInfo.unemployStart != null">
                        AND a.UNEMPLOY_START = #{tSocialInfo.unemployStart}
                    </if>
                    <if test="tSocialInfo.workInjuryStart != null">
                        AND a.WORK_INJURY_START = #{tSocialInfo.workInjuryStart}
                    </if>
                    <if test="tSocialInfo.birthStart != null">
                        AND a.BIRTH_START = #{tSocialInfo.birthStart}
                    </if>
                    <if test="tSocialInfo.bigailmentStart != null">
                        AND a.BIGAILMENT_START = #{tSocialInfo.bigailmentStart}
                    </if>
                    <if test="tSocialInfo.pensionHandle != null and tSocialInfo.pensionHandle.trim() != ''">
                        AND a.PENSION_HANDLE = #{tSocialInfo.pensionHandle}
                    </if>
                    <if test="tSocialInfo.medicalHandle != null and tSocialInfo.medicalHandle.trim() != ''">
                        AND a.MEDICAL_HANDLE = #{tSocialInfo.medicalHandle}
                    </if>
                    <if test="tSocialInfo.unemployHandle != null and tSocialInfo.unemployHandle.trim() != ''">
                        AND a.UNEMPLOY_HANDLE = #{tSocialInfo.unemployHandle}
                    </if>
                    <if test="tSocialInfo.workInjuryHandle != null and tSocialInfo.workInjuryHandle.trim() != ''">
                        AND a.WORK_INJURY_HANDLE = #{tSocialInfo.workInjuryHandle}
                    </if>
                    <if test="tSocialInfo.birthHandle != null and tSocialInfo.birthHandle.trim() != ''">
                        AND a.BIRTH_HANDLE = #{tSocialInfo.birthHandle}
                    </if>
                    <if test="tSocialInfo.bigailmentHandle != null and tSocialInfo.bigailmentHandle.trim() != ''">
                        AND a.BIGAILMENT_HANDLE = #{tSocialInfo.bigailmentHandle}
                    </if>
                    <if test="tSocialInfo.createBy != null and tSocialInfo.createBy.trim() != ''">
                        AND a.CREATE_BY = #{tSocialInfo.createBy}
                    </if>
                    <if test="tSocialInfo.updateBy != null and tSocialInfo.updateBy.trim() != ''">
                        AND a.UPDATE_BY = #{tSocialInfo.updateBy}
                    </if>
                    <if test="tSocialInfo.createName != null and tSocialInfo.createName.trim() != ''">
                        AND a.CREATE_NAME = #{tSocialInfo.createName}
                    </if>
                    <if test="tSocialInfo.updateTime != null">
                        AND a.UPDATE_TIME = #{tSocialInfo.updateTime}
                    </if>


					<if test="tSocialInfo.ygsHandleStatus != null and tSocialInfo.ygsHandleStatus.trim() != ''">
						AND a.YGS_HANDLE_STATUS = #{tSocialInfo.ygsHandleStatus}
					</if>
					<if test="tSocialInfo.ysdHandleStatus != null and tSocialInfo.ysdHandleStatus.trim() != ''">
						AND a.YSD_HANDLE_STATUS = #{tSocialInfo.ysdHandleStatus}
					</if>
					<if test="tSocialInfo.ygsRemark != null and tSocialInfo.ygsRemark.trim() != ''">
						AND a.YGS_REMARK like concat('%',#{tSocialInfo.ygsRemark},'%')
					</if>
					<if test="tSocialInfo.ysdRemark != null and tSocialInfo.ysdRemark.trim() != ''">
						AND a.YSD_REMARK like concat('%',#{tSocialInfo.ysdRemark},'%')
					</if>
		</if>
    </sql>
    <!--tSocialInfo简单分页查询-->
    <select id="getTSocialInfoPage" resultMap="tSocialInfoMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM t_social_info a
        <where>
            1=1
            <include refid="tSocialInfo_where"/>
        </where>
    </select>

    <!-- 获取所有需要自动办理的养工失 -->
    <select id="getSocialSoldierYgsAll" resultMap="tSocialInfoMap">
        SELECT
			a.ID,
			a.SOCIAL_PROVINCE,
			a.EMP_NAME,
			a.SOCIAL_HOUSEHOLD_NAME,
			a.EMP_IDCARD,
			a.YGS_ADD_ID,
			a.HANDLE_STATUS,
			a.YGS_HANDLE_STATUS,
			SUBSTRING_INDEX(GROUP_CONCAT(d.id ORDER BY d.CREATE_TIME desc),',',1) as DISPATCH_ID,
			SUBSTRING_INDEX(GROUP_CONCAT(d.type ORDER BY d.CREATE_TIME desc),',',1) as DISPATCH_TYPE,
if((sd.id is not null and NOW() <![CDATA[ <= ]]> sd.SOCIAL_END_DATE) or ( sd.id is null and DATE_FORMAT(d.AUDIT_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m')),0 ,1) YGS_OVER_DUE_FLAG
        FROM t_social_info a
        left join t_dispatch_info d on d.SOCIAL_ID = a.id
		left join t_social_deadline_info sd on sd.MONTH = DATE_FORMAT(d.AUDIT_TIME,'%Y%m') and sd.STATUS = '0'
			and sd.PROVINCE=a.SOCIAL_PROVINCE and sd.CITY=a.SOCIAL_CITY and ( (a.SOCIAL_TOWN is null and sd.TOWN is null) or (sd.TOWN=a.SOCIAL_TOWN))
		where a.YGS_ADD_ID is not null and a.YGS_ADD_ID != '' and a.YGS_ADD_ID != '-' and a.YGS_REQUEST_ID is null and d.AUTO_FLAG='0'
		  and (DATE_FORMAT(d.AUDIT_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m') or DATE_FORMAT(d.AUDIT_TIME,'%Y%m') = DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 MONTH),'%Y%m'))
		  and a.YGS_HANDLE_STATUS in ('1','2','3')
		group by a.id
	</select>

	<!-- 获取所有需要自动办理的医生大 -->
    <select id="getSocialSoldierYsdAll" resultMap="tSocialInfoMap">
		SELECT
			a.ID,
			a.SOCIAL_PROVINCE,
			a.EMP_NAME,
			a.SOCIAL_HOUSEHOLD_NAME,
			a.EMP_IDCARD,
			a.YSD_ADD_ID,
			a.HANDLE_STATUS,
			a.YSD_HANDLE_STATUS,
			SUBSTRING_INDEX(GROUP_CONCAT(d.id ORDER BY d.CREATE_TIME desc),',',1) as DISPATCH_ID,
			SUBSTRING_INDEX(GROUP_CONCAT(d.type ORDER BY d.CREATE_TIME desc),',',1) as DISPATCH_TYPE,
		if((sd.id is not null and NOW() <![CDATA[ <= ]]> sd.MEDICAL_END_DATE) or ( sd.id is null and DATE_FORMAT(d.AUDIT_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m')),0 ,1) YSD_OVER_DUE_FLAG
		FROM t_social_info a
				 left join t_dispatch_info d on d.SOCIAL_ID = a.id
				 left join t_social_deadline_info sd on sd.MONTH = DATE_FORMAT(d.AUDIT_TIME,'%Y%m') and sd.STATUS = '0'
			and sd.PROVINCE=a.SOCIAL_PROVINCE and sd.CITY=a.SOCIAL_CITY and ( (a.SOCIAL_TOWN is null and sd.TOWN is null) or (sd.TOWN=a.SOCIAL_TOWN))
		where a.YSD_ADD_ID is not null and a.YSD_ADD_ID != '' and a.YSD_REQUEST_ID is null and d.AUTO_FLAG='0'
		  and (DATE_FORMAT(d.AUDIT_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m') or DATE_FORMAT(d.AUDIT_TIME,'%Y%m') = DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 MONTH),'%Y%m'))
		  and a.YSD_HANDLE_STATUS in ('1','2','3')
		group by a.id
    </select>

	<!-- 获取所有需要自动办理的养工失 -->
    <select id="getSocialFriendYgsAll" resultMap="tSocialInfoMap">
        SELECT
			a.ID,
			a.EMP_NAME,
			a.SOCIAL_HOUSEHOLD,
			a.SOCIAL_HOUSEHOLD_NAME,
			a.EMP_IDCARD,
			a.YGS_REQUEST_ID,
			a.HANDLE_STATUS,
			a.YGS_HANDLE_STATUS,
			SUBSTRING_INDEX(GROUP_CONCAT(d.id ORDER BY d.CREATE_TIME desc),',',1) as DISPATCH_ID,
			SUBSTRING_INDEX(GROUP_CONCAT(d.type ORDER BY d.CREATE_TIME desc),',',1) as DISPATCH_TYPE,
if((sd.id is not null and NOW() <![CDATA[ <= ]]> sd.SOCIAL_END_DATE) or ( sd.id is null and DATE_FORMAT(d.AUDIT_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m')),0 ,1) YGS_OVER_DUE_FLAG
        FROM t_social_info a
        left join t_dispatch_info d on d.SOCIAL_ID = a.id
		left join t_social_deadline_info sd on sd.MONTH = DATE_FORMAT(d.AUDIT_TIME,'%Y%m') and sd.STATUS = '0'
			and sd.PROVINCE=a.SOCIAL_PROVINCE and sd.CITY=a.SOCIAL_CITY and ( (a.SOCIAL_TOWN is null and sd.TOWN is null) or (sd.TOWN=a.SOCIAL_TOWN))
		where a.YGS_REQUEST_ID is not null and a.YGS_REQUEST_ID != '' and a.YGS_REQUEST_ID != '-' and d.AUTO_FLAG='0'
		  and (DATE_FORMAT(d.AUDIT_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m') or DATE_FORMAT(d.AUDIT_TIME,'%Y%m') = DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 MONTH),'%Y%m'))
		  and a.YGS_HANDLE_STATUS in ('1','2')
		group by a.id
		order by a.YGS_REQUEST_ID asc
	</select>

	<!-- 获取所有需要自动办理的医生大 -->
    <select id="getSocialFriendYsdAll" resultMap="tSocialInfoMap">
		SELECT
			a.ID,
			a.EMP_NAME,
			a.SOCIAL_HOUSEHOLD,
			a.SOCIAL_HOUSEHOLD_NAME,
			a.EMP_IDCARD,
			a.YSD_REQUEST_ID,
			a.HANDLE_STATUS,
			a.YSD_HANDLE_STATUS,
			a.SOCIAL_START_DATE,
			a.YSD_SOCIAL_START_DATE_NEXT,
			SUBSTRING_INDEX(GROUP_CONCAT(d.id ORDER BY d.CREATE_TIME desc),',',1) as DISPATCH_ID,
			SUBSTRING_INDEX(GROUP_CONCAT(d.type ORDER BY d.CREATE_TIME desc),',',1) as DISPATCH_TYPE,
			if((sd.id is not null and NOW() <![CDATA[ <= ]]> sd.MEDICAL_END_DATE) or ( sd.id is null and DATE_FORMAT(d.AUDIT_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m')),0 ,1) YSD_OVER_DUE_FLAG
		FROM t_social_info a
				 left join t_dispatch_info d on d.SOCIAL_ID = a.id
				 left join t_social_deadline_info sd on sd.MONTH = DATE_FORMAT(d.AUDIT_TIME,'%Y%m') and sd.STATUS = '0'
			and sd.PROVINCE=a.SOCIAL_PROVINCE and sd.CITY=a.SOCIAL_CITY and ( (a.SOCIAL_TOWN is null and sd.TOWN is null) or (sd.TOWN=a.SOCIAL_TOWN))
		where a.YSD_REQUEST_ID is not null and a.YSD_REQUEST_ID != '' and a.YSD_REQUEST_ID != '-' and d.AUTO_FLAG='0'
		  and (DATE_FORMAT(d.AUDIT_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m') or DATE_FORMAT(d.AUDIT_TIME,'%Y%m') = DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 MONTH),'%Y%m'))
		  and a.YSD_HANDLE_STATUS in ('1','2')
		group by a.id
		order by a.YSD_REQUEST_ID asc
    </select>

    <!-- 获取所有需要社保局审核的养工失 -->
    <select id="getSocialSoldierYgsByAudit" resultMap="tSocialInfoMap">
        SELECT
			a.ID,
			a.EMP_NAME,
			a.SOCIAL_HOUSEHOLD_NAME,
			a.EMP_IDCARD,
			a.YGS_ADD_ID,
			a.HANDLE_STATUS,
			a.YGS_HANDLE_STATUS,
			SUBSTRING_INDEX(GROUP_CONCAT(d.id ORDER BY d.CREATE_TIME desc),',',1) as DISPATCH_ID,
			SUBSTRING_INDEX(GROUP_CONCAT(d.type ORDER BY d.CREATE_TIME desc),',',1) as DISPATCH_TYPE
        FROM t_social_info a
        left join t_dispatch_info d on d.SOCIAL_ID = a.id
		where a.YGS_ADD_ID is not null and a.YGS_ADD_ID != '' and a.YGS_ADD_ID != '-' and d.AUTO_FLAG='0'
		  and (DATE_FORMAT(d.AUDIT_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m') or DATE_FORMAT(d.AUDIT_TIME,'%Y%m') = DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 MONTH),'%Y%m'))
		  and a.YGS_HANDLE_STATUS = '7'
		group by a.id
	</select>

	<!-- 获取所有需要社保局审核的医生大 -->
    <select id="getSocialSoldierYsdByAudit" resultMap="tSocialInfoMap">
		SELECT
			a.ID,
			a.EMP_NAME,
			a.SOCIAL_HOUSEHOLD_NAME,
			a.EMP_IDCARD,
			a.YSD_ADD_ID,
			a.HANDLE_STATUS,
			a.YSD_HANDLE_STATUS,
			SUBSTRING_INDEX(GROUP_CONCAT(d.id ORDER BY d.CREATE_TIME desc),',',1) as DISPATCH_ID,
			SUBSTRING_INDEX(GROUP_CONCAT(d.type ORDER BY d.CREATE_TIME desc),',',1) as DISPATCH_TYPE
		FROM t_social_info a
				 left join t_dispatch_info d on d.SOCIAL_ID = a.id
		where a.YSD_ADD_ID is not null and a.YSD_ADD_ID != '' and a.YSD_ADD_ID != '-' and d.AUTO_FLAG='0'
		  and (DATE_FORMAT(d.AUDIT_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m') or DATE_FORMAT(d.AUDIT_TIME,'%Y%m') = DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 MONTH),'%Y%m'))
		  and a.YSD_HANDLE_STATUS = '7'
		group by a.id
    </select>

	<!-- 根据任务ID，获取需要自动办理的社保 -->
    <select id="getSocialSoldierByAddId" resultMap="tSocialInfoMap">
        SELECT
			a.ID,
			a.EMP_NAME,
			a.SOCIAL_HOUSEHOLD_NAME,
			a.EMP_IDCARD,
			a.YGS_ADD_ID,
			a.YSD_ADD_ID,
			a.HANDLE_STATUS,
			a.YGS_HANDLE_STATUS,
			a.YSD_HANDLE_STATUS,
			d.id as DISPATCH_ID
        FROM t_social_info a
		left join t_dispatch_info d on d.SOCIAL_ID = a.id
        where a.YGS_ADD_ID = #{addId} or a.YSD_ADD_ID = #{addId}
    </select>

	<!-- 获取所有需要自动办理的养工失:跨约继续办理更新为中止办理 -->
	<select id="getSocialFriendYgsAllTask" resultMap="tSocialInfoMap">
		SELECT
			a.ID,
			a.EMP_NAME,
			a.SOCIAL_HOUSEHOLD_NAME,
			a.EMP_IDCARD,
			a.YGS_REQUEST_ID,
			a.HANDLE_STATUS,
			a.YGS_HANDLE_STATUS,
			SUBSTRING_INDEX(GROUP_CONCAT(d.id ORDER BY d.CREATE_TIME desc),',',1) as DISPATCH_ID,
			SUBSTRING_INDEX(GROUP_CONCAT(d.type ORDER BY d.CREATE_TIME desc),',',1) as DISPATCH_TYPE
		FROM t_social_info a
				 left join t_dispatch_info d on d.SOCIAL_ID = a.id
		where a.YGS_REQUEST_ID is not null and a.YGS_REQUEST_ID != '' and a.YGS_REQUEST_ID != '-' and d.AUTO_FLAG='0'
		  and DATE_FORMAT(d.AUDIT_TIME,'%Y%m') = DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 MONTH),'%Y%m')
		  and a.YGS_HANDLE_STATUS ='3'
		group by a.id
		order by a.YGS_REQUEST_ID asc
	</select>

	<!-- 获取所有需要自动办理的医生大:跨约继续办理更新为中止办理 -->
	<select id="getSocialFriendYsdAllTask" resultMap="tSocialInfoMap">
		SELECT
			a.ID,
			a.EMP_NAME,
			a.SOCIAL_HOUSEHOLD_NAME,
			a.EMP_IDCARD,
			a.YSD_REQUEST_ID,
			a.HANDLE_STATUS,
			a.YSD_HANDLE_STATUS,
			a.SOCIAL_START_DATE,
			a.YSD_SOCIAL_START_DATE_NEXT,
			SUBSTRING_INDEX(GROUP_CONCAT(d.id ORDER BY d.CREATE_TIME desc),',',1) as DISPATCH_ID,
			SUBSTRING_INDEX(GROUP_CONCAT(d.type ORDER BY d.CREATE_TIME desc),',',1) as DISPATCH_TYPE
		FROM t_social_info a
				 left join t_dispatch_info d on d.SOCIAL_ID = a.id
		where
		  d.AUTO_FLAG='0'
		  and DATE_FORMAT(d.AUDIT_TIME,'%Y%m') = DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 MONTH),'%Y%m')
		  and (a.YSD_HANDLE_STATUS = '3' or a.YGS_HANDLE_STATUS ='3')
		group by a.id
		order by a.YSD_REQUEST_ID asc
	</select>

</mapper>
