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

	<resultMap id="sysBaseSetInfoMap" type="com.yifu.cloud.plus.v1.yifu.social.entity.SysBaseSetInfo">
		<id property="id" column="ID"/>
		<result property="province" column="PROVINCE"/>
		<result property="city" column="CITY"/>
		<result property="town" column="TOWN"/>
		<result property="applyStartDate" column="APPLY_START_DATE"/>
		<result property="applyEndDate" column="APPLY_END_DATE"/>
		<result property="upperLimit" column="UPPER_LIMIT"/>
		<result property="lowerLimit" column="LOWER_LIMIT"/>
		<result property="payPolicy" column="PAY_POLICY"/>
		<result property="baseType" column="BASE_TYPE"/>
		<result property="unitPersionPro" column="UNIT_PERSION_PRO"/>
		<result property="unitMedicalPro" column="UNIT_MEDICAL_PRO"/>
		<result property="unitUnemploymentPro" column="UNIT_UNEMPLOYMENT_PRO"/>
		<result property="unitInjuryPro" column="UNIT_INJURY_PRO"/>
		<result property="unitBirthPro" column="UNIT_BIRTH_PRO"/>
		<result property="unitProSum" column="UNIT_PRO_SUM"/>
		<result property="personalPersionPro" column="PERSONAL_PERSION_PRO"/>
		<result property="personalMedicalPro" column="PERSONAL_MEDICAL_PRO"/>
		<result property="personalUnemploymentPro" column="PERSONAL_UNEMPLOYMENT_PRO"/>
		<result property="personalProSum" column="PERSONAL_PRO_SUM"/>
		<result property="isIllness" column="IS_ILLNESS"/>
		<result property="isChargePersonal" column="IS_CHARGE_PERSONAL"/>
		<result property="chargeCompany" column="CHARGE_COMPANY"/>
		<result property="chargePersonal" column="CHARGE_PERSONAL"/>
		<result property="collectType" column="COLLECT_TYPE"/>
		<result property="payCompany" column="PAY_COMPANY"/>
		<result property="payCompanyPro" column="PAY_COMPANY_PRO"/>
		<result property="payPersonal" column="PAY_PERSONAL"/>
		<result property="payPersonalPro" column="PAY_PERSONAL_PRO"/>
		<result property="collectMoth" column="COLLECT_MOTH"/>
		<result property="fundPayLowerLimit" column="FUND_PAY_LOWER_LIMIT"/>
		<result property="fundPayUpperLimit" column="FUND_PAY_UPPER_LIMIT"/>
		<result property="fundPayPoint" column="FUND_PAY_POINT"/>
		<result property="fundPayType" column="FUND_PAY_TYPE"/>
		<result property="housingFundCompamyPro" column="HOUSING_FUND_COMPAMY_PRO"/>
		<result property="housingFundPersonalPro" column="HOUSING_FUND_PERSONAL_PRO"/>
		<result property="departId" column="DEPART_ID"/>
		<result property="status" column="STATUS"/>
		<result property="valueType" column="VALUE_TYPE"/>
		<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="deleteFlag" column="DELETE_FLAG"/>
		<result property="departName" column="DEPART_NAME"/>
		<result property="doMonth" column="DO_MONTH"/>
		<result property="createBy" column="CREATE_BY"/>
		<result property="updateBy" column="UPDATE_BY"/>
		<result property="createName" column="CREATE_NAME"/>
		<result property="createTime" column="CREATE_TIME"/>
		<result property="updateTime" column="UPDATE_TIME"/>
		<result property="upPersion" column="UP_PERSION"/>
		<result property="upMedical" column="UP_MEDICAL"/>
		<result property="upUnemployment" column="UP_UNEMPLOYMENT"/>
		<result property="upInjury" column="UP_INJURY"/>
		<result property="upBirth" column="UP_BIRTH"/>
		<result property="upBig" column="UP_BIG"/>
		<result property="lowerPersion" column="LOWER_PERSION"/>
		<result property="lowerMedical" column="LOWER_MEDICAL"/>
		<result property="lowerUnemployment" column="LOWER_UNEMPLOYMENT"/>
		<result property="lowerInjury" column="LOWER_INJURY"/>
		<result property="lowerBirth" column="LOWER_BIRTH"/>
		<result property="lowerBig" column="LOWER_BIG"/>
		<result property="isSameBase" column="IS_SAME_BASE"/>
		<collection property="fundProList" ofType="com.yifu.cloud.plus.v1.yifu.social.entity.SysPayProportion"
					select="com.yifu.cloud.plus.v1.yifu.social.mapper.SysPayProportionMapper.selectByPrimaryId" column="id">
		</collection>
	</resultMap>
	<sql id="Base_Column_List">
		a.ID,
		a.PROVINCE,
		a.CITY,
		a.TOWN,
		a.APPLY_START_DATE,
		a.APPLY_END_DATE,
		a.UPPER_LIMIT,
		a.LOWER_LIMIT,
		a.PAY_POLICY,
		a.BASE_TYPE,
		a.UNIT_PERSION_PRO,
		a.UNIT_MEDICAL_PRO,
		a.UNIT_UNEMPLOYMENT_PRO,
		a.UNIT_INJURY_PRO,
		a.UNIT_BIRTH_PRO,
		a.UNIT_PRO_SUM,
		a.PERSONAL_PERSION_PRO,
		a.PERSONAL_MEDICAL_PRO,
		a.PERSONAL_UNEMPLOYMENT_PRO,
		a.PERSONAL_PRO_SUM,
		a.IS_ILLNESS,
		a.IS_CHARGE_PERSONAL,
		a.CHARGE_COMPANY,
		a.CHARGE_PERSONAL,
		a.COLLECT_TYPE,
		a.PAY_COMPANY,
		a.PAY_COMPANY_PRO,
		a.PAY_PERSONAL,
		a.PAY_PERSONAL_PRO,
		a.COLLECT_MOTH,
		a.FUND_PAY_LOWER_LIMIT,
		a.FUND_PAY_UPPER_LIMIT,
		a.FUND_PAY_POINT,
		a.FUND_PAY_TYPE,
		a.HOUSING_FUND_COMPAMY_PRO,
		a.HOUSING_FUND_PERSONAL_PRO,
		a.DEPART_ID,
		a.STATUS,
		a.VALUE_TYPE,
		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.DELETE_FLAG,
		a.DEPART_NAME,
		a.DO_MONTH,
		a.CREATE_BY,
		a.UPDATE_BY,
		a.CREATE_NAME,
		a.CREATE_TIME,
		a.UPDATE_TIME,
		UP_PERSION,
		UP_MEDICAL,
		UP_UNEMPLOYMENT,
		UP_INJURY,
		UP_BIRTH,
		UP_BIG,
		LOWER_PERSION,
		LOWER_MEDICAL,
		LOWER_UNEMPLOYMENT,
		LOWER_INJURY,
		LOWER_BIRTH,
		IS_SAME_BASE,
		LOWER_BIG
	</sql>
	<sql id="sysBaseSetInfo_where">
		<if test="sysBaseSetInfo != null">
			<if test="sysBaseSetInfo.id != null and sysBaseSetInfo.id.trim() != ''">
				AND a.ID = #{sysBaseSetInfo.id}
			</if>
			<if test="sysBaseSetInfo.province != null">
				AND a.PROVINCE = #{sysBaseSetInfo.province}
			</if>
			<if test="sysBaseSetInfo.city != null">
				<if test="sysBaseSetInfo.city != -1">
					AND a.CITY = #{sysBaseSetInfo.city}
				</if>
				<if test="sysBaseSetInfo.city == -1">
					AND a.CITY is null
				</if>
			</if>
			<if test="sysBaseSetInfo.town != null">
				<if test="sysBaseSetInfo.town != -1">
					AND a.TOWN = #{sysBaseSetInfo.town}
				</if>
				<if test="sysBaseSetInfo.town == -1">
					AND a.TOWN is null
				</if>
			</if>
			<if test="sysBaseSetInfo.applyStartDate != null">
				AND a.APPLY_START_DATE <![CDATA[ <= ]]> #{sysBaseSetInfo.applyStartDate}
				AND (a.APPLY_END_DATE is null or a.APPLY_END_DATE <![CDATA[ >= ]]> #{sysBaseSetInfo.applyStartDate})
			</if>
			<if test="sysBaseSetInfo.applyEndDate != null">
				AND a.APPLY_END_DATE = #{sysBaseSetInfo.applyEndDate}
			</if>
			<if test="sysBaseSetInfo.upperLimit != null">
				AND a.UPPER_LIMIT = #{sysBaseSetInfo.upperLimit}
			</if>
			<if test="sysBaseSetInfo.lowerLimit != null">
				AND a.LOWER_LIMIT = #{sysBaseSetInfo.lowerLimit}
			</if>
			<if test="sysBaseSetInfo.payPolicy != null and sysBaseSetInfo.payPolicy.trim() != ''">
				AND a.PAY_POLICY = #{sysBaseSetInfo.payPolicy}
			</if>
			<if test="sysBaseSetInfo.baseType != null and sysBaseSetInfo.baseType.trim() != ''">
				AND a.BASE_TYPE = #{sysBaseSetInfo.baseType}
			</if>
			<if test="sysBaseSetInfo.unitPersionPro != null">
				AND a.UNIT_PERSION_PRO = #{sysBaseSetInfo.unitPersionPro}
			</if>
			<if test="sysBaseSetInfo.unitMedicalPro != null">
				AND a.UNIT_MEDICAL_PRO = #{sysBaseSetInfo.unitMedicalPro}
			</if>
			<if test="sysBaseSetInfo.unitUnemploymentPro != null">
				AND a.UNIT_UNEMPLOYMENT_PRO = #{sysBaseSetInfo.unitUnemploymentPro}
			</if>
			<if test="sysBaseSetInfo.unitInjuryPro != null">
				AND a.UNIT_INJURY_PRO = #{sysBaseSetInfo.unitInjuryPro}
			</if>
			<if test="sysBaseSetInfo.unitBirthPro != null">
				AND a.UNIT_BIRTH_PRO = #{sysBaseSetInfo.unitBirthPro}
			</if>
			<if test="sysBaseSetInfo.unitProSum != null">
				AND a.UNIT_PRO_SUM = #{sysBaseSetInfo.unitProSum}
			</if>
			<if test="sysBaseSetInfo.personalPersionPro != null">
				AND a.PERSONAL_PERSION_PRO = #{sysBaseSetInfo.personalPersionPro}
			</if>
			<if test="sysBaseSetInfo.personalMedicalPro != null">
				AND a.PERSONAL_MEDICAL_PRO = #{sysBaseSetInfo.personalMedicalPro}
			</if>
			<if test="sysBaseSetInfo.personalUnemploymentPro != null">
				AND a.PERSONAL_UNEMPLOYMENT_PRO = #{sysBaseSetInfo.personalUnemploymentPro}
			</if>
			<if test="sysBaseSetInfo.personalProSum != null">
				AND a.PERSONAL_PRO_SUM = #{sysBaseSetInfo.personalProSum}
			</if>
			<if test="sysBaseSetInfo.isIllness != null and sysBaseSetInfo.isIllness.trim() != ''">
				AND a.IS_ILLNESS = #{sysBaseSetInfo.isIllness}
			</if>
			<if test="sysBaseSetInfo.isChargePersonal != null and sysBaseSetInfo.isChargePersonal.trim() != ''">
				AND a.IS_CHARGE_PERSONAL = #{sysBaseSetInfo.isChargePersonal}
			</if>
			<if test="sysBaseSetInfo.chargeCompany != null">
				AND a.CHARGE_COMPANY = #{sysBaseSetInfo.chargeCompany}
			</if>
			<if test="sysBaseSetInfo.chargePersonal != null">
				AND a.CHARGE_PERSONAL = #{sysBaseSetInfo.chargePersonal}
			</if>
			<if test="sysBaseSetInfo.collectType != null and sysBaseSetInfo.collectType.trim() != ''">
				AND a.COLLECT_TYPE = #{sysBaseSetInfo.collectType}
			</if>
			<if test="sysBaseSetInfo.payCompany != null">
				AND a.PAY_COMPANY = #{sysBaseSetInfo.payCompany}
			</if>
			<if test="sysBaseSetInfo.payCompanyPro != null">
				AND a.PAY_COMPANY_PRO = #{sysBaseSetInfo.payCompanyPro}
			</if>
			<if test="sysBaseSetInfo.payPersonal != null">
				AND a.PAY_PERSONAL = #{sysBaseSetInfo.payPersonal}
			</if>
			<if test="sysBaseSetInfo.payPersonalPro != null">
				AND a.PAY_PERSONAL_PRO = #{sysBaseSetInfo.payPersonalPro}
			</if>
			<if test="sysBaseSetInfo.collectMoth != null">
				AND a.COLLECT_MOTH = #{sysBaseSetInfo.collectMoth}
			</if>
			<if test="sysBaseSetInfo.fundPayLowerLimit != null">
				AND a.FUND_PAY_LOWER_LIMIT = #{sysBaseSetInfo.fundPayLowerLimit}
			</if>
			<if test="sysBaseSetInfo.fundPayUpperLimit != null">
				AND a.FUND_PAY_UPPER_LIMIT = #{sysBaseSetInfo.fundPayUpperLimit}
			</if>
			<if test="sysBaseSetInfo.fundPayPoint != null">
				AND a.FUND_PAY_POINT = #{sysBaseSetInfo.fundPayPoint}
			</if>
			<if test="sysBaseSetInfo.fundPayType != null">
				AND a.FUND_PAY_TYPE = #{sysBaseSetInfo.fundPayType}
			</if>
			<if test="sysBaseSetInfo.housingFundCompamyPro != null">
				AND a.HOUSING_FUND_COMPAMY_PRO = #{sysBaseSetInfo.housingFundCompamyPro}
			</if>
			<if test="sysBaseSetInfo.housingFundPersonalPro != null">
				AND a.HOUSING_FUND_PERSONAL_PRO = #{sysBaseSetInfo.housingFundPersonalPro}
			</if>
			<if test="sysBaseSetInfo.departId != null and sysBaseSetInfo.departId.trim() != ''">
				AND a.DEPART_ID = #{sysBaseSetInfo.departId}
			</if>
			<if test="sysBaseSetInfo.status != null">
				AND a.STATUS = #{sysBaseSetInfo.status}
			</if>
			<if test="sysBaseSetInfo.valueType != null and sysBaseSetInfo.valueType.trim() != ''">
				AND a.VALUE_TYPE = #{sysBaseSetInfo.valueType}
			</if>
			<if test="sysBaseSetInfo.canOverpay != null and sysBaseSetInfo.canOverpay.trim() != ''">
				AND a.CAN_OVERPAY = #{sysBaseSetInfo.canOverpay}
			</if>
			<if test="sysBaseSetInfo.overpayNumber != null">
				AND a.OVERPAY_NUMBER = #{sysBaseSetInfo.overpayNumber}
			</if>
			<if test="sysBaseSetInfo.haveThisMonth != null and sysBaseSetInfo.haveThisMonth.trim() != ''">
				AND a.HAVE_THIS_MONTH = #{sysBaseSetInfo.haveThisMonth}
			</if>
			<if test="sysBaseSetInfo.insurancePension != null and sysBaseSetInfo.insurancePension.trim() != ''">
				AND a.INSURANCE_PENSION = #{sysBaseSetInfo.insurancePension}
			</if>
			<if test="sysBaseSetInfo.insuranceMedical != null and sysBaseSetInfo.insuranceMedical.trim() != ''">
				AND a.INSURANCE_MEDICAL = #{sysBaseSetInfo.insuranceMedical}
			</if>
			<if test="sysBaseSetInfo.insuranceUnemployment != null and sysBaseSetInfo.insuranceUnemployment.trim() != ''">
				AND a.INSURANCE_UNEMPLOYMENT = #{sysBaseSetInfo.insuranceUnemployment}
			</if>
			<if test="sysBaseSetInfo.insuranceInjury != null and sysBaseSetInfo.insuranceInjury.trim() != ''">
				AND a.INSURANCE_INJURY = #{sysBaseSetInfo.insuranceInjury}
			</if>
			<if test="sysBaseSetInfo.insuranceBirth != null and sysBaseSetInfo.insuranceBirth.trim() != ''">
				AND a.INSURANCE_BIRTH = #{sysBaseSetInfo.insuranceBirth}
			</if>
			<if test="sysBaseSetInfo.insuranceBigailment != null and sysBaseSetInfo.insuranceBigailment.trim() != ''">
				AND a.INSURANCE_BIGAILMENT = #{sysBaseSetInfo.insuranceBigailment}
			</if>
			<if test="sysBaseSetInfo.insuranceIsLatestCardinality != null and sysBaseSetInfo.insuranceIsLatestCardinality.trim() != ''">
				AND a.INSURANCE_IS_LATEST_CARDINALITY = #{sysBaseSetInfo.insuranceIsLatestCardinality}
			</if>
			<if test="sysBaseSetInfo.deleteFlag != null and sysBaseSetInfo.deleteFlag.trim() != ''">
				AND a.DELETE_FLAG = #{sysBaseSetInfo.deleteFlag}
			</if>
			<if test="sysBaseSetInfo.departName != null and sysBaseSetInfo.departName.trim() != ''">
				AND a.DEPART_NAME like concat('%',#{sysBaseSetInfo.departName},'%')
			</if>
			<if test="sysBaseSetInfo.applyDate != null and sysBaseSetInfo.applyDate.trim() != ''">
				AND (
					a.APPLY_START_DATE <![CDATA[ <= ]]> #{sysBaseSetInfo.applyDate}
					and (a.APPLY_END_DATE is null or a.APPLY_END_DATE >= #{sysBaseSetInfo.applyDate})
				)
			</if>
			<if test="sysBaseSetInfo.createBy != null and sysBaseSetInfo.createBy.trim() != ''">
				AND a.CREATE_BY = #{sysBaseSetInfo.createBy}
			</if>
			<if test="sysBaseSetInfo.updateBy != null and sysBaseSetInfo.updateBy.trim() != ''">
				AND a.UPDATE_BY = #{sysBaseSetInfo.updateBy}
			</if>
			<if test="sysBaseSetInfo.createName != null and sysBaseSetInfo.createName.trim() != ''">
				AND a.CREATE_NAME = #{sysBaseSetInfo.createName}
			</if>
			<if test="sysBaseSetInfo.createTime != null">
				AND a.CREATE_TIME = #{sysBaseSetInfo.createTime}
			</if>
			<if test="sysBaseSetInfo.updateTime != null">
				AND a.UPDATE_TIME = #{sysBaseSetInfo.updateTime}
			</if>
		</if>
	</sql>
	<!--sysBaseSetInfo简单分页查询-->
	<select id="getSysBaseSetInfoPage" resultMap="sysBaseSetInfoMap">
		SELECT
			<include refid="Base_Column_List"/>
		FROM sys_base_set_info a
		<where>
			1=1
			<include refid="sysBaseSetInfo_where"/>
		</where>
		order by
		FIELD(a.DEPART_NAME, '安徽皖信人力资源管理有限公司-市直', '安徽皖信人力资源管理有限公司-省直', '安徽皖信人力资源管理有限公司') DESC,
		a.CREATE_TIME desc
	</select>
	<!--sysBaseSetInfo简单分页查询-->
	<select id="getSysBaseSetInfoPageAsso" resultMap="sysBaseSetInfoMap">
		SELECT
		<include refid="Base_Column_List"/>
		FROM sys_base_set_info a
		<where>
			1=1
			<include refid="sysBaseSetInfo_where"/>
			<if test="sysBaseSetInfo != null">
				<if test="sysBaseSetInfo.departName != null and sysBaseSetInfo.departName.trim() != ''">
					AND a.DEPART_NAME = #{sysBaseSetInfo.departName}
				</if>
			</if>
		</where>
		order by
		FIELD(a.DEPART_NAME, '安徽皖信人力资源管理有限公司-市直', '安徽皖信人力资源管理有限公司-省直', '安徽皖信人力资源管理有限公司') DESC,
		a.CREATE_TIME desc
	</select>
	<!--sysBaseSetInfo简单查询-->
	<select id="getAllByEntity" resultMap="sysBaseSetInfoMap">
		SELECT
			<include refid="Base_Column_List"/>
		FROM sys_base_set_info a
		<where>
			1=1
			<include refid="sysBaseSetInfo_where"/>
		</where>
	</select>

	<!--获取对应户名称的公积金基数信息-->
	<select id="getFundBaseSet" resultMap="sysBaseSetInfoMap">
		SELECT
			<include refid="Base_Column_List"/>
		FROM sys_base_set_info a
		<where>
			1=1
			and
			(
			(a.APPLY_START_DATE  <![CDATA[ <= ]]> now() and a.APPLY_END_DATE >= now())
			or (a.APPLY_START_DATE  <![CDATA[ <= ]]> now() and a.APPLY_END_DATE is null)
			)
			<if test="fundHolds != null and fundHolds.size > 0">
				and a.DEPART_NAME in
				<foreach collection="fundHolds" item="item" open="(" close=")" separator="," >
					#{item}
				</foreach>
			</if>

			and a.DELETE_FLAG = '0'
			and a.status ='0'
			and a.BASE_TYPE = '1'
		</where>
	</select>

	<!--sysBaseSetInfo不分页查询-->
	<select id="getSysBaseSetInfoNoPage" resultMap="sysBaseSetInfoMap">
		SELECT
			<include refid="Base_Column_List"/>
		FROM sys_base_set_info a
		<where>
			1=1
			<include refid="sysBaseSetInfo_where"/>
		</where>
		ORDER BY a.apply_start_date desc
	</select>

	<!-- 获取条数-->
	<select id="getCountByEdit" resultType="java.lang.Integer">
		SELECT
			count(1)
		FROM sys_base_set_info a
		<where>
			a.STATUS = 0 and a.ID != #{sysBaseSetInfo.id}
			<if test="sysBaseSetInfo.baseType != null and sysBaseSetInfo.baseType.trim() != ''">
				AND a.BASE_TYPE = #{sysBaseSetInfo.baseType}
			</if>
			<if test="sysBaseSetInfo.departId != null and sysBaseSetInfo.departId.trim() != ''">
				AND a.DEPART_ID = #{sysBaseSetInfo.departId}
			</if>
			<if test="sysBaseSetInfo.province != null">
				AND a.PROVINCE = #{sysBaseSetInfo.province}
			</if>
			<if test="sysBaseSetInfo.city != null">
				AND a.CITY = #{sysBaseSetInfo.city}
			</if>
			<if test="sysBaseSetInfo.city == null">
				AND a.CITY is null
			</if>
			<if test="sysBaseSetInfo.town != null">
				AND a.TOWN = #{sysBaseSetInfo.town}
			</if>
			<if test="sysBaseSetInfo.town == null">
				AND a.TOWN is null
			</if>
			<if test="sysBaseSetInfo.applyEndDate == null">
				AND a.APPLY_END_DATE <![CDATA[ >= ]]> #{sysBaseSetInfo.applyStartDate}
			</if>
			<if test="sysBaseSetInfo.applyEndDate != null">
				AND (
				(apply_end_date is not null and (
				(
				apply_start_date <![CDATA[ <= ]]> #{sysBaseSetInfo.applyEndDate}
				and apply_end_date >= #{sysBaseSetInfo.applyEndDate}
				) or (
				apply_start_date <![CDATA[ <= ]]> #{sysBaseSetInfo.applyStartDate}
				and apply_end_date >= #{sysBaseSetInfo.applyStartDate}
				) or (
				apply_start_date >= #{sysBaseSetInfo.applyStartDate}
				and apply_end_date <![CDATA[ <= ]]> #{sysBaseSetInfo.applyEndDate}
				)
				)) or (apply_end_date is null and apply_start_date <![CDATA[ <= ]]> #{sysBaseSetInfo.applyEndDate})
				)
			</if>
		</where>
		limit 1
	</select>

	<select id="noPageSocialDiy" resultType="com.yifu.cloud.plus.v1.yifu.social.vo.SysBaseSetSocialExportVo">
		select
		a.DEPART_NAME departName,
		CONCAT_WS('-',
		CASE WHEN a1.id is NOT NULL THEN a1.AREA_NAME ELSE NULL END,
		CASE WHEN a2.id is NOT NULL THEN a2.AREA_NAME ELSE NULL END,
		CASE WHEN a3.id is NOT NULL THEN a3.AREA_NAME ELSE NULL END
		) as  province,
		a.STATUS 'status',
		CONCAT(a.APPLY_START_DATE,'~',IF(a.APPLY_END_DATE is NULL,"永久",a.APPLY_END_DATE)) applyStartDate,
		a.APPLY_START_DATE applyStartDate,
		a.DO_MONTH doMonth,
		a.IS_SAME_BASE isSameBase,
		a.LOWER_PERSION lowerPersion,
		a.UP_PERSION upPersion,
		a.LOWER_INJURY lowerInjury,
		a.UP_INJURY upInjury,
		a.LOWER_MEDICAL lowerMedical,
		a.UP_MEDICAL upMedical,
		a.LOWER_BIRTH lowerBirth,
		a.UP_BIRTH upBirth,
		a.LOWER_UNEMPLOYMENT lowerUnemployment,
		a.UP_UNEMPLOYMENT upUnemployment,
		IF(a.IS_ILLNESS='0',IF(a.VALUE_TYPE='1',a.LOWER_BIG,""),"") lowerBig,
		IF(a.IS_ILLNESS='0',IF(a.VALUE_TYPE='1',a.UP_BIG,""),"") upBig,
		a.IS_ILLNESS isIllness,
		a.COLLECT_TYPE collectType,
		a.IS_CHARGE_PERSONAL isChargePersonal,
		a.COLLECT_MOTH collectMoth,
		a.VALUE_TYPE valueType,
		IF(a.IS_ILLNESS='0',IF(a.VALUE_TYPE='0',a.PAY_COMPANY,""),"") chargeCompany,
		IF(a.IS_ILLNESS='0',IF(a.VALUE_TYPE='0',a.PAY_PERSONAL,""),"")  chargePersonal,
		a.UNIT_MEDICAL_PRO unitMedicalPro,
		a.PERSONAL_MEDICAL_PRO personalMedicalPro,
		a.UNIT_BIRTH_PRO unitBirthPro,
		a.UNIT_PERSION_PRO unitPersionPro,
		a.PERSONAL_PERSION_PRO personalPersionPro,
		a.UNIT_INJURY_PRO unitInjuryPro,
		a.UNIT_UNEMPLOYMENT_PRO unitUnemploymentPro,
		a.PERSONAL_UNEMPLOYMENT_PRO personalUnemploymentPro,
		IF(a.IS_ILLNESS='0',IF(a.VALUE_TYPE='1',a.PAY_COMPANY_PRO,""),"") payCompanyPro,
		IF(a.IS_ILLNESS='0',IF(a.VALUE_TYPE='1',a.PAY_PERSONAL_PRO,""),"") payPersonalPro,
		a.CAN_OVERPAY canOverpay,
		a.UNIT_PRO_SUM unitProSum,
		a.PERSONAL_PRO_SUM personalProSum,
		CONCAT_WS(',',
		CASE WHEN a.INSURANCE_PENSION=0 THEN '养老' ELSE NULL END,
		CASE WHEN a.INSURANCE_MEDICAL=0 THEN '医疗' ELSE NULL END,
		CASE WHEN a.INSURANCE_INJURY=0 THEN '工伤' ELSE NULL END,
		CASE WHEN a.INSURANCE_UNEMPLOYMENT=0 THEN '失业' ELSE NULL END,
		CASE WHEN a.INSURANCE_BIRTH=0 THEN '生育' ELSE NULL END,
		CASE WHEN a.INSURANCE_BIGAILMENT=0 THEN '大病' ELSE NULL END
		) as  insurancePension,
		a.OVERPAY_NUMBER overpayNumber,
		a.HAVE_THIS_MONTH haveThisMonth,
		a.INSURANCE_IS_LATEST_CARDINALITY insuranceIsLatestCardinality,
		a.PAY_POLICY payPolicy
		FROM sys_base_set_info a
		LEFT JOIN sys_area a1 on a1.id=a.PROVINCE
		LEFT JOIN sys_area a2 on a2.id=a.CITY
		LEFT JOIN sys_area a3 on a3.id=a.TOWN
		<where>
			a.DELETE_FLAG='0'
			and a.BASE_TYPE ='0'
			<include refid="sysBaseSetInfo_where"/>
		</where>
		order by a.CREATE_TIME desc
	</select>

	<select id="noPageFundDiy" resultType="com.yifu.cloud.plus.v1.yifu.social.vo.SysBaseSetFundExportVo">
		select
		a.DEPART_NAME departName,
		a.FUND_PAY_TYPE fundPayType,
		CONCAT_WS('-',
		CASE WHEN a1.id is NOT NULL THEN a1.AREA_NAME ELSE NULL END,
		CASE WHEN a2.id is NOT NULL THEN a2.AREA_NAME ELSE NULL END,
		CASE WHEN a3.id is NOT NULL THEN a3.AREA_NAME ELSE NULL END
		) as provinceStr,
		a.STATUS 'status',
		CONCAT(a.APPLY_START_DATE,'~',IF(a.APPLY_END_DATE is NULL,"永久",a.APPLY_END_DATE)) applyStartDate,
		a.DO_MONTH doMonth,
		a.LOWER_LIMIT lowerLimit,
		a.UPPER_LIMIT upperLimit,
		a.FUND_PAY_POINT fundPayPoint,
		fp.pro housingFundPro,
		a.CAN_OVERPAY canOverpay,
		a.OVERPAY_NUMBER overpayNumber,
		a.HAVE_THIS_MONTH haveThisMonth,
		a.INSURANCE_IS_LATEST_CARDINALITY insuranceIsLatestCardinality,
		a.PAY_POLICY payPolicy
		FROM sys_base_set_info a
		LEFT JOIN sys_area a1 on a1.id=a.PROVINCE
		LEFT JOIN sys_area a2 on a2.id=a.CITY
		LEFT JOIN sys_area a3 on a3.id=a.TOWN
		LEFT JOIN
		(select GROUP_CONCAT(CONCAT(
		CASE
		WHEN p.COMPANY_PRO = TRUNCATE(p.COMPANY_PRO, 0) THEN FORMAT(p.COMPANY_PRO, 0)
		ELSE FORMAT(p.COMPANY_PRO, 2)
		END,
		',',
		CASE
		WHEN p.PERSONAL_PRO = TRUNCATE(p.PERSONAL_PRO, 0) THEN FORMAT(p.PERSONAL_PRO, 0)
		ELSE FORMAT(p.PERSONAL_PRO, 2)
		END) SEPARATOR ';') as pro,p.SYS_BASE_SET_ID from sys_pay_proportion p GROUP BY p.SYS_BASE_SET_ID)
		fp on fp.SYS_BASE_SET_ID=a.ID

		<where>
			a.DELETE_FLAG='0'
			and a.BASE_TYPE ='1'
			<include refid="sysBaseSetInfo_where"/>
		</where>
		order by a.CREATE_TIME desc
	</select>
</mapper>
