/*
 *    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)
 */
package com.yifu.cloud.plus.v1.yifu.social.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.style.row.SimpleRowHeightStyleStrategy;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.google.common.collect.Lists;
import com.yifu.cloud.plus.v1.yifu.archives.util.IdCardUtil;
import com.yifu.cloud.plus.v1.yifu.common.core.constant.CommonConstants;
import com.yifu.cloud.plus.v1.yifu.common.core.util.Common;
import com.yifu.cloud.plus.v1.yifu.common.core.util.DateUtil;
import com.yifu.cloud.plus.v1.yifu.common.core.util.OSSUtil;
import com.yifu.cloud.plus.v1.yifu.common.core.util.R;
import com.yifu.cloud.plus.v1.yifu.common.dapr.util.ArchivesDaprUtil;
import com.yifu.cloud.plus.v1.yifu.social.config.*;
import com.yifu.cloud.plus.v1.yifu.social.constants.DispatchConstants;
import com.yifu.cloud.plus.v1.yifu.social.entity.*;
import com.yifu.cloud.plus.v1.yifu.social.mapper.*;
import com.yifu.cloud.plus.v1.yifu.social.service.*;
import com.yifu.cloud.plus.v1.yifu.social.util.ExcelMergeImage;
import com.yifu.cloud.plus.v1.yifu.social.util.ExcelToImage;
import com.yifu.cloud.plus.v1.yifu.social.util.RandomFontStyleUtil;
import com.yifu.cloud.plus.v1.yifu.social.util.WordToImageUtil;
import com.yifu.cloud.plus.v1.yifu.social.vo.*;
import lombok.AllArgsConstructor;
import lombok.extern.log4j.Log4j2;
import org.apache.commons.io.IOUtils;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.ReflectionUtils;
import org.springframework.web.client.RestTemplate;

import java.io.*;
import java.lang.reflect.Field;
import java.net.URL;
import java.time.LocalDateTime;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 税友-推送
 *
 * @author hgw
 * @date 2024-12-6 15:43:23
 */
@AllArgsConstructor
@Log4j2
@Service
public class TSocialFriendPushServiceImpl extends ServiceImpl<TSocialFriendMapper, TSocialInfo> implements TSocialFriendPushService {

	private final RestTemplate restTemplate;
	private final SocialFriendConfig socialFriendConfig;
	private final TSocialInfoService tSocialInfoService;
	private final TSocialDeadlineInfoService tSocialDeadlineInfoService;
	private final TSocialSoldierLogService tSocialSoldierLogService;
	private final TSocialFriendBackLogService tSocialFriendBackLogService;
	private final TSocialFreindSetService tSocialFreindSetService;
	private final TSocialFriendReduceSetService tSocialFriendReduceSetService;
	private final TSocialInfoMapper socialMapper;

	// 自动生成解除劳动合同证明书使用的，查询合同最早的起始时间
	private final ArchivesDaprUtil archivesDaprUtil;

	private final AtomicInteger atomicPushFriend = new AtomicInteger(0);
	@Autowired
	private ExcelMergeImage excelMergeImage;
	@Autowired
	private ExcelToImage excelToImage;
	@Autowired
	private WordToImageUtil wordToImageUtil;
	@Autowired
	private OSSUtil ossUtil;

	@Autowired
	private SysHouseHoldInfoMapper houseHoldInfoMapper;

	@Autowired
	private TAttaInfoMapper attaInfoMapper;

	@Autowired
	private TDispatchInfoMapper dispatchInfoMapper;

	private final static String AND_STR = "&";

	/**
	 * @param isAuto 1是，0否。是否自动推送的，自动要查户配置。手动可推送养工失。
	 *               手动的都推送（大部分都是推送，芜湖要先推送医保办理成功后才推送社保）
	 * @param dispatchIdList 审核后推送的派单ID
	 * @param pushDispatchIdList 手动推送的派单ID（这里不限制单个推送类型的）
	 * @Description: 推送到税友
	 * @Author: hgw
	 * @Date: 2024-12-12 10:03:59
	 * @return: com.yifu.cloud.plus.v1.yifu.common.core.util.R<java.lang.String>
	 **/
	@Override
	public R<String> pushFriend(List<String> dispatchIdList, List<String> pushDispatchIdList, Integer isAuto, String userId) {
		if (atomicPushFriend.incrementAndGet() <= 1) {
			try {
				TSocialFreindSet set = tSocialFreindSetService.getById("1");
				if (set != null && Common.isNotNull(set.getType()) && 1 == set.getType()) {

					// 通用的模板+查到的户的模板map
					Map<String, URL> templateSrcMap = new HashMap<>();
					// 通用的花名册模板
					// 2025-11-4 17:45:38 1.7.17-税友临时分支，此处代码暂时注释掉，因为需求（陈红）说暂时不要通用花名册模板，后面有可能还是要通用花名册模板
					// List<TAttaInfo> srcList = attaInfoMapper.getTemplateSrcById();
					URL url;
					/*if (srcList != null && !srcList.isEmpty()) {
						for (TAttaInfo src : srcList) {
							url = ossUtil.getObjectUrl(null, src.getAttaSrc());
							templateSrcMap.put("template_" + src.getId(), url);
						}
					}*/
					// 离职原因Map，将离职原因翻译为税友的字典
					Map<String, TSocialFriendReduceSet> reduceYgsMap = tSocialFriendReduceSetService.getFriendReduceMap(CommonConstants.ONE_STRING);
					Map<String, TSocialFriendReduceSet> reduceYsdMap = tSocialFriendReduceSetService.getFriendReduceMap(CommonConstants.TWO_STRING);
					Map<String, TSocialFriendReduceSet> reduceChiZhouMap = tSocialFriendReduceSetService.getFriendReduceMap(CommonConstants.THREE_STRING);
					Map<String, Integer> huiZhouMap = this.getSpecialHuNameMap();

					// 获取时间限制配置(获取 当月启用 的配置)
					Map<String, TSocialDeadlineInfo> socialSetMap = tSocialDeadlineInfoService.getSocialSetByAreaId();
					// 所有的章
					List<SysHouseHoldInfo> zhangList = houseHoldInfoMapper.getAttaSrcByAutoDoc();
					Map<String, URL> zhangMap = new HashMap<>();
					if (zhangList != null && !zhangList.isEmpty()) {
						for (SysHouseHoldInfo zhang : zhangList) {
							url = ossUtil.getObjectUrl(null, zhang.getType());
							zhangMap.put(zhang.getName(), url);
						}
					}
					// 离职证明模板
					String templateSrc = attaInfoMapper.getLeaveDocTemplate();
					URL templateUrl = null;
					if (Common.isNotNull(templateSrc)) {
						templateUrl = ossUtil.getObjectUrl(null, templateSrc);
					}

					// 社保增员、社保减员、医保增员、医保减员
					// 养工失新增列表
					List<SociaFriendYgsAddVo> ygsAddlist = baseMapper.getSocialSoldierYgsAddVoList(dispatchIdList, pushDispatchIdList, isAuto, userId);
					if (ygsAddlist != null && !ygsAddlist.isEmpty()) {
						this.doPushSocialAndLogYgs(socialSetMap, ygsAddlist, 1, reduceYgsMap, zhangMap, templateUrl, templateSrcMap, reduceChiZhouMap, huiZhouMap);
					}

					// 养工失减少列表
					List<SociaFriendYgsAddVo> ygsReducelist = baseMapper.getSocialSoldierYgsReduceVoList(dispatchIdList, pushDispatchIdList, isAuto, userId);
					if (ygsReducelist != null && !ygsReducelist.isEmpty()) {
						this.doPushSocialAndLogYgs(socialSetMap, ygsReducelist, 2, reduceYgsMap, zhangMap, templateUrl, templateSrcMap, reduceChiZhouMap, huiZhouMap);
					}

					// 医生大新增列表
					List<SociaFriendYgsAddVo> ysdAddlist = baseMapper.getSocialSoldierYsdAddVoList(dispatchIdList,pushDispatchIdList, isAuto, userId);
					if (ysdAddlist != null && !ysdAddlist.isEmpty()) {
						this.doPushSocialAndLogYsd(socialSetMap, ysdAddlist, 3, reduceYsdMap, zhangMap, templateUrl, templateSrcMap, reduceChiZhouMap, huiZhouMap);
					}
					// 医生大减少列表
					List<SociaFriendYgsAddVo> ysdReducelist = baseMapper.getSocialSoldierYsdReduceVoList(dispatchIdList, pushDispatchIdList, isAuto, userId);
					if (ysdReducelist != null && !ysdReducelist.isEmpty()) {
						this.doPushSocialAndLogYsd(socialSetMap, ysdReducelist, 4, reduceYsdMap, zhangMap, templateUrl, templateSrcMap, reduceChiZhouMap, huiZhouMap);
					}
					if ((ygsAddlist == null || ygsAddlist.isEmpty())
							&& (ygsReducelist == null || ygsReducelist.isEmpty())
							&& (ysdAddlist == null || ysdAddlist.isEmpty())
							&& (ysdReducelist == null || ysdReducelist.isEmpty())
					) {
						return R.failed("无数据，推送结束！");
					}
				}
			} finally {
				atomicPushFriend.decrementAndGet();
			}
			return R.ok();
		} else {
			atomicPushFriend.decrementAndGet();
			return R.failed("距离上一次推送任务时间间隔太短，请10分钟后再试！！！");
		}
	}

	// 2026-03-26 hgw
	// 不可以并发，会使花名册转图片的字体不正确
	// 后续优化，考虑升级(spire.xls)的版本
	// 此方法是：删除临时文件
	@Override
	public void doClearFriend() {
		try {
			String tempDirPath = System.getProperty("java.io.tmpdir");
			File tempDir = new File(tempDirPath);
			if (tempDir.exists() && tempDir.isDirectory()) {
				File[] spireTempFiles = tempDir.listFiles((dir, name) ->
						name.startsWith("+~JF") && name.endsWith(".tmp")
				);
				if (spireTempFiles != null && spireTempFiles.length > 0) {
					// 删30分钟前的数据
					long thirtyMinutesAgo = System.currentTimeMillis() - 30L * 60 * 1000;
					for (File file : spireTempFiles) {
						if (file.isFile() && file.canWrite() && file.lastModified() < thirtyMinutesAgo) {
							file.delete();
						}
					}
				}
			}
		} catch (Exception e) {
			log.error("清理 Spire.XLS 临时文件失败", e);
		}
	}

	// 获取特殊的户名称，当前给 徽州区 使用，徽州区需要走不同的分支
	private Map<String, Integer> getSpecialHuNameMap() {
		Map<String, Integer> map = new HashMap<>();
		List<String> list = baseMapper.getSpecialHuName();
		if (list != null && !list.isEmpty()) {
			for (String s : list) {
				map.put(s, CommonConstants.ONE_INT);
			}
		}
		return map;
	}

	/**
	 * @Description: 推送社保和记录日志
	 * @Author: hgw
	 * @Date: 2024/9/2 17:21
	 * @return: void
	 **/
	private R<String> doPushSocialAndLogYgs(Map<String, TSocialDeadlineInfo> socialSetMap
			, List<SociaFriendYgsAddVo> ygsAddlist, int type, Map<String, TSocialFriendReduceSet> reduceMap
			, Map<String, URL> zhangMap, URL templateUrl, Map<String, URL> templateSrcMap
			, Map<String, TSocialFriendReduceSet> reduceChiZhouMap, Map<String, Integer> huiZhouMap) {
		int canPushType;
		TSocialInfo socialInfo;
		String typeStr = "提交";
		List<TSocialSoldierLog> logList = new ArrayList<>();
		TSocialSoldierLog socialLog;
		String remark = null;
		List<TSocialInfo> renGongList = new ArrayList<>();
		String requestId;
		String thisTime = DateUtil.getThisTime();
		List<TSocialFriendBackLog> backLogList = new ArrayList<>();
		R<String> urlR;
		List<SociaFriendYgsAddVo> listVo = new ArrayList<>();
		// 花名册需要的list
		List<SociaFriendYgsAddVo> huaListVo = new ArrayList<>();
		List<TSocialSoldierLog> logTemp = new ArrayList<>();
		List<TSocialInfo> socialInfoTemp = new ArrayList<>();
		List<TSocialInfo> socialInfoUpdate = new ArrayList<>();
		String ygsHandleStatus;
		String dispatchItem = "社保";
		// 同一个户才一批次推送，因为账号密码不一样 ，不同户，则先推送一批
		boolean nextFlag = false;
		SociaFriendYgsAddVo vo;
		String bizNo;
		int i=1;
		TSocialFriendReduceSet reduceSet;
		// 是否为单个推送（派单审核时定性的）
		boolean isSingle;
		// 是否为生成解除劳动合同书 且是 派减
		boolean isAutoLeaveDoc;
		// 社保是否自动生成花名册
		boolean rosterAutoFlagYgs;
		// 社保仅补缴生成材料 0否1是
		boolean dataFlagYgs;
		int nowMonth = Integer.parseInt(DateUtil.getThisMonth());
		// 如果是徽州区的社保，要单个推送，且走新分支
		boolean isHuiZhou;
		for (int index = 0 ; index < ygsAddlist.size(); index++) {
			vo = ygsAddlist.get(index);
			// 派减离职原因转化：
			if (type == 2 && Common.isNotNull(vo.getTbyy())) {
				reduceSet = reduceChiZhouMap.get(vo.getTbyy());
				if (reduceSet != null) {
					vo.setChiZhouTbyy(reduceSet.getTbyy());
					vo.setChiZhouZzyy(reduceSet.getZzyy());
				}
				reduceSet = reduceMap.get(vo.getSocialHouseholdName() + CommonConstants.DOWN_LINE_STRING + vo.getTbyy());
				if (reduceSet == null) {
					reduceSet = reduceMap.get(vo.getTbyy());
				}
				if (reduceSet != null) {
					vo.setTbyy(reduceSet.getTbyy());
					vo.setZzyy(reduceSet.getZzyy());
					vo.setBajcejyy(reduceSet.getBajcejyy());
				} else {
					remark = "超出离职原因的配置，转人工！配置为："+vo.getTbyy();
					socialInfo = new TSocialInfo();
					socialInfo.setId(vo.getSocialId());
					socialInfo.setYgsHandleStatus(CommonConstants.FIVE_STRING);
					renGongList.add(socialInfo);
					socialLog = tSocialSoldierLogService.getFriendLog(vo, typeStr, dispatchItem, remark);
					if (socialLog != null) {
						logList.add(socialLog);
					}
					continue;
				}
			}
			// 0：未到时间不动; 1：推送; 2：转人工；3预减员
			canPushType = this.getCanPushType(socialSetMap, vo);
			if (1 == canPushType || 3 == canPushType) {
				try {

					socialInfo = new TSocialInfo();
					socialInfo.setId(vo.getSocialId());
					listVo.add(vo);

					// 仅补缴生成材料
					dataFlagYgs = Common.isNotNull(vo.getDataFlagYgs())
							&& CommonConstants.ONE_STRING.equals(vo.getDataFlagYgs())
							&& Common.isNotNull(vo.getSocialStartMonth());
					if (dataFlagYgs) {
						// 仅补缴生成材料
						try {
							if (nowMonth >= Integer.parseInt(vo.getSocialStartMonth())) {
								huaListVo.add(vo);
							}
						} catch (Exception e) {
							huaListVo.add(vo);
						}
					} else {
						huaListVo.add(vo);
					}

					socialInfoTemp.add(socialInfo);
					socialLog = tSocialSoldierLogService.getFriendLog(vo, typeStr, dispatchItem, remark);
					if (socialLog != null) {
						logTemp.add(socialLog);
					}
					if (Common.isNotNull(vo.getSocialHouseholdName())
							&& index+1 < ygsAddlist.size()
							&& !vo.getSocialHouseholdName().equals(ygsAddlist.get(index+1).getSocialHouseholdName())) {
						nextFlag = true;
					}
					isSingle = Common.isNotNull(vo.getIsSingle()) && CommonConstants.ONE_STRING.equals(vo.getIsSingle());
					isAutoLeaveDoc = templateUrl != null && type == 2 && Common.isNotNull(vo.getIsAutoLeaveDoc())
							&& CommonConstants.ONE_STRING.equals(vo.getIsAutoLeaveDoc());
					isHuiZhou = huiZhouMap.get(vo.getSocialHouseholdName()) != null;
					if (isHuiZhou || isAutoLeaveDoc || isSingle || nextFlag || i % 10 == 0 || index >= ygsAddlist.size()-1) {

						rosterAutoFlagYgs = Common.isEmpty(vo.getRosterAutoFlagYgs()) || CommonConstants.ONE_STRING.equals(vo.getRosterAutoFlagYgs());
						if (rosterAutoFlagYgs && !huaListVo.isEmpty()) {

							urlR = doExportRosterNew(vo, type, huaListVo, zhangMap, templateSrcMap);

							// 房工接口，获取 企业职工基本医疗保险参保登记表 url
							//urlR = doExportRoster(vo.getSocialId(), String.valueOf(type), vo.getUnitCreditCode(), listVo)
							if (urlR != null && urlR.getCode() == 200 && Common.isNotNull(urlR.getData())) {
								vo.setQyzgshbxzyhmc(urlR.getData());
							}
						}

						if (isSingle && Common.isNotNull(vo.getDispatchId())) {
							// 获取单个附件并推送税友，返回 imgList 到Vo里
							this.setImgList(vo);
						}
						// 派减且是要自动生成离职证明：
						if (isAutoLeaveDoc) {
							this.doSetAutoDoc(zhangMap, templateUrl, vo, CommonConstants.EIGHT_INT);
						}

						i=0;
						nextFlag = false;
						// 针对部分传参bizNo却无requestId返回的情况，需要改进代码：

						bizNo = vo.getSocialId() + type + thisTime;
						requestId = socialFriendConfig.pushFriendByInfo(restTemplate, vo, listVo, type, bizNo, backLogList
								, tSocialFriendBackLogService, isHuiZhou);
						ygsHandleStatus = CommonConstants.TWO_STRING;
						remark = "已推送提交任务！";
						if (Common.isEmpty(requestId)) {
							requestId = "推送税友未返回，请联系管理员bizNo="+bizNo;
							ygsHandleStatus = CommonConstants.FIVE_STRING;
							remark = "推送税友未返回，转人工处理！请联系管理员bizNo="+bizNo;
						}
						for (TSocialInfo s : socialInfoTemp) {
							s.setYgsRequestId(requestId);
							s.setYgsHandleStatus(ygsHandleStatus);
							socialInfoUpdate.add(s);
						}
						for (TSocialSoldierLog sLog : logTemp) {
							sLog.setRemark("【税友】" + remark);
							logList.add(sLog);
						}
						// 重置
						listVo = new ArrayList<>();
						huaListVo = new ArrayList<>();
						socialInfoTemp = new ArrayList<>();
						logTemp = new ArrayList<>();
					}
					i++;
				} catch (Exception e) {
					remark = "提交社保士兵异常！";
				}
			} else {
				if (0 == canPushType) {
					remark = "未到时间，不推送！";
				} else {
					remark = "无时间配置或超出办理截止时间，转人工！";
					socialInfo = new TSocialInfo();
					socialInfo.setId(vo.getSocialId());
					socialInfo.setYgsHandleStatus(CommonConstants.FIVE_STRING);
					renGongList.add(socialInfo);
				}
				socialLog = tSocialSoldierLogService.getFriendLog(vo, typeStr, dispatchItem, remark);
				if (socialLog != null) {
					logList.add(socialLog);
				}
			}
		}
		if (!socialInfoUpdate.isEmpty()) {
			tSocialInfoService.updateBatchById(socialInfoUpdate);
		}
		if (!backLogList.isEmpty()) {
			tSocialFriendBackLogService.saveBatch(backLogList);
		}
		if (!renGongList.isEmpty()) {
			tSocialInfoService.updateBatchById(renGongList);
		}
		if (!logList.isEmpty()) {
			tSocialSoldierLogService.saveBatch(logList);
		}
		// 临时return
		return R.ok();
	}

	/**
	 * @Description: 自动生成离职证明书
	 * @Author: hgw
	 * @Date: 2025/5/27 11:10
	 * @return: void
	 **/
	private void doSetAutoDoc(Map<String, URL> zhangMap, URL templateUrl, SociaFriendYgsAddVo vo, Integer type) {
		// 自动生成离职证明书
		// String src = "1735185038602皖信.png"
		URL url = zhangMap.get(vo.getSocialHouseholdName());
		if (url != null) {
			try (InputStream imageStream = url.openStream()) {
				// 准备替换参数
				Map<String, String> replacements = new HashMap<>();
				replacements.put("empName", vo.getEmpName());
				replacements.put("empSex", IdCardUtil.getSexName(vo.getEmpIdcard()));
				replacements.put("empAge", String.valueOf(IdCardUtil.IdNOToAge(vo.getEmpIdcard())));
				replacements.put("idCard", vo.getEmpIdcard());
				R<String> contractRes = archivesDaprUtil.getMinDateByCardAndDeptId(vo.getEmpIdcard(), vo.getDeptId());
				if (contractRes != null && Common.isNotNull(contractRes.getData())){
					replacements.put("startDate", contractRes.getData());
				}
				replacements.put("leaveReason", vo.getTbyy());
				replacements.put("leaveDate", vo.getLeaveDate());
				replacements.put("nowDate", vo.getDispatchDate());

				// 执行替换 + 插入印章
				// 转换成图片 完成后清理流
				TSocialFriendBackLog backLog = new TSocialFriendBackLog();
				backLog.setSocialId(vo.getEmpIdcard());
				backLog.setType(type);
				backLog.setCreateTime(LocalDateTime.now());
				String friendUrl = wordToImageUtil.replaceTextInWord(templateUrl.openStream()
						, replacements, imageStream, restTemplate, socialFriendConfig, backLog);
				// System.out.println("看看URL111："+friendUrl)
				tSocialFriendBackLogService.save(backLog);
				vo.setAutoLeaveDoc(friendUrl);
			} catch (Exception e) {
				log.info("未获取到公章");
			}
		}
	}

	/**
	 * @param vo
	 * @Description: 获取单个附件并推送税友，返回 imgList 到Vo里
	 * @Author: hgw
	 * @Date: 2025/4/28 17:05
	 * @return: void
	 **/
	private void setImgList(SociaFriendYgsAddVo vo) {
		URL url;
		InputStream inStream = null;
		List<String> imgListYgs = new ArrayList<>();
		List<String> imgListYsd = new ArrayList<>();
		List<TAttaInfo> attaInfoList = attaInfoMapper.getTAttaInfoListByDoMainId(vo.getDispatchId());
		if (attaInfoList != null && !attaInfoList.isEmpty()) {
			String base64String;
			TSocialFriendBackLog backLog;
			String imgUrl;
			for (TAttaInfo attaInfo : attaInfoList) {
				if (Common.isNotNull(attaInfo.getAttaSrc())) {
					// 获取附件url
					url = ossUtil.getObjectUrl(null, attaInfo.getAttaSrc());
					if (url != null) {
						try {
							inStream = url.openStream();
							// inStream转化为base64String
							base64String = Base64.getEncoder().encodeToString(IOUtils.toByteArray(inStream));
							backLog = new TSocialFriendBackLog();
							backLog.setSocialId(vo.getSocialId());
							backLog.setType(16);
							imgUrl = socialFriendConfig.uploadPng(restTemplate, attaInfo.getAttaName(), base64String, backLog);
							tSocialFriendBackLogService.save(backLog);
							if (Common.isNotNull(imgUrl)){
								if (Common.isEmpty(attaInfo.getRelationType()) || CommonConstants.TWO_STRING.equals(attaInfo.getRelationType())) {
									imgListYgs.add(imgUrl);
								} else {
									imgListYsd.add(imgUrl);
								}
							}
						} catch (Exception e){
							log.info("税友单个图片上传失败！");
						} finally {
							if (null != inStream){
								try {
									inStream.close();
								} catch (IOException e) {
									e.printStackTrace();
								}
							}
						}
					}
				}
			}
			vo.setImgListYgs(imgListYgs);
			vo.setImgListYsd(imgListYsd);
		}
	}

	/**
	 * @Description: 推送医保和记录日志
	 * @Author: hgw
	 * @Date: 2024/9/2 17:21
	 * @return: void
	 **/
	private void doPushSocialAndLogYsd(Map<String, TSocialDeadlineInfo> socialSetMap
			, List<SociaFriendYgsAddVo> ysdAddlist, int type, Map<String, TSocialFriendReduceSet> reduceMap
			, Map<String, URL> zhangMap, URL templateUrl, Map<String, URL> templateSrcMap
			, Map<String, TSocialFriendReduceSet> reduceChiZhouMap, Map<String, Integer> huiZhouMap) {
		int canPushType;
		TSocialInfo socialInfo;
		String typeStr = "提交";
		List<TSocialSoldierLog> logList = new ArrayList<>();
		TSocialSoldierLog socialLog;
		List<TSocialInfo> renGongList = new ArrayList<>();
		String remark = null;
		String requestId;
		String thisTime = DateUtil.getThisTime();
		List<TSocialFriendBackLog> backLogList = new ArrayList<>();
		R<String> urlR;

		List<SociaFriendYgsAddVo> listVo = new ArrayList<>();
		// 花名册需要的list
		List<SociaFriendYgsAddVo> huaListVo = new ArrayList<>();
		List<TSocialSoldierLog> logTemp = new ArrayList<>();
		List<TSocialInfo> socialInfoTemp = new ArrayList<>();
		List<TSocialInfo> socialInfoUpdate = new ArrayList<>();
		String ysdHandleStatus;
		String dispatchItem = "医保";
		// 同一个户才一批次推送，因为账号密码不一样 ，不同户，则先推送一批
		boolean nextFlag = false;
		SociaFriendYgsAddVo vo;
		String bizNo;
		int i=1;
		TSocialFriendReduceSet reduceSet;
		// 是否为生成解除劳动合同书 且是 派减
		boolean isAutoLeaveDoc;
		// 是否为单个推送（派单审核时定性的）
		boolean isSingle;
		// 社保是否自动生成花名册
		boolean rosterAutoFlagYsd;
		// 社保仅补缴生成材料 0否1是
		boolean dataFlagYsd;
		int nowMonth = Integer.parseInt(DateUtil.getThisMonth());
		// 如果是徽州区的医保，可以批量推送，且走新分支
		boolean isHuiZhou;
		for (int index = 0 ; index < ysdAddlist.size(); index++) {
			vo = ysdAddlist.get(index);

			// 0：未到时间不动; 1：推送; 2：转人工
			// 1.7.15去掉死亡拦截，税友说能办理
			/*if ("16".equals(vo.getTbyy())) {
				remark = "劳动者死亡，转人工！";
				socialInfo = new TSocialInfo();
				socialInfo.setId(vo.getSocialId());
				socialInfo.setYsdHandleStatus(CommonConstants.FIVE_STRING);
				renGongList.add(socialInfo);
				socialLog = tSocialSoldierLogService.getFriendLog(vo, typeStr, dispatchItem, remark);
				if (socialLog != null) {
					logList.add(socialLog);
				}
			} else {*/
				// 派减离职原因转化：
			if (type == 4 && Common.isNotNull(vo.getTbyy())) {
				reduceSet = reduceChiZhouMap.get(vo.getTbyy());
				if (reduceSet != null) {
					vo.setChiZhouTbyy(reduceSet.getTbyy());
					vo.setChiZhouZzyy(reduceSet.getZzyy());
				}
				reduceSet = reduceMap.get(vo.getSocialHouseholdName() + CommonConstants.DOWN_LINE_STRING + vo.getTbyy());
				if (reduceSet == null) {
					reduceSet = reduceMap.get(vo.getTbyy());
				}
				if (reduceSet != null) {
					vo.setTbyy(reduceSet.getTbyy());
					vo.setZzyy(reduceSet.getZzyy());
					vo.setBajcejyy(reduceSet.getBajcejyy());
				} else {
					remark = "超出医保离职原因的配置，转人工！配置为："+vo.getTbyy();
					socialInfo = new TSocialInfo();
					socialInfo.setId(vo.getSocialId());
					socialInfo.setYsdHandleStatus(CommonConstants.FIVE_STRING);
					renGongList.add(socialInfo);
					socialLog = tSocialSoldierLogService.getFriendLog(vo, typeStr, dispatchItem, remark);
					if (socialLog != null) {
						logList.add(socialLog);
					}
					continue;
				}
			}
			canPushType = this.getCanPushTypeYsd(socialSetMap, vo);
			if (1 == canPushType) {
				try {
					socialInfo = new TSocialInfo();
					socialInfo.setId(vo.getSocialId());
					listVo.add(vo);
					// 仅补缴生成材料
					dataFlagYsd = Common.isNotNull(vo.getDataFlagYsd())
							&& CommonConstants.ONE_STRING.equals(vo.getDataFlagYsd())
							&& Common.isNotNull(vo.getSocialStartMonth());
					if (dataFlagYsd) {
						// 仅补缴生成材料
						try {
							if (nowMonth >= Integer.parseInt(vo.getSocialStartMonth())) {
								huaListVo.add(vo);
							}
						} catch (Exception e) {
							huaListVo.add(vo);
						}
					} else {
						huaListVo.add(vo);
					}
					socialInfo = new TSocialInfo();
					socialInfo.setId(vo.getSocialId());
					socialInfoTemp.add(socialInfo);
					socialLog = tSocialSoldierLogService.getFriendLog(vo, typeStr, dispatchItem, remark);
					if (socialLog != null) {
						logTemp.add(socialLog);
					}
					if (Common.isNotNull(vo.getSocialHouseholdName())
							&& index+1 < ysdAddlist.size()
							&& !vo.getSocialHouseholdName().equals(ysdAddlist.get(index+1).getSocialHouseholdName())) {
						nextFlag = true;
					}
					isSingle = Common.isNotNull(vo.getIsSingleYsd()) && CommonConstants.ONE_STRING.equals(vo.getIsSingleYsd());

					isAutoLeaveDoc = templateUrl != null && type == 4 && Common.isNotNull(vo.getIsAutoLeaveDoc())
							&& CommonConstants.ONE_STRING.equals(vo.getIsAutoLeaveDoc());
					// 徽州区的医保可以批量
					isHuiZhou = huiZhouMap.get(vo.getSocialHouseholdName()) != null;
					if (isAutoLeaveDoc || isSingle || nextFlag || i % 10 == 0 || index >= ysdAddlist.size()-1) {

						rosterAutoFlagYsd = Common.isEmpty(vo.getRosterAutoFlagYsd()) || CommonConstants.ONE_STRING.equals(vo.getRosterAutoFlagYsd());
						if (rosterAutoFlagYsd && !huaListVo.isEmpty()) {

							urlR = doExportRosterNew(vo, type, huaListVo, zhangMap, templateSrcMap);

							// 房工接口，获取 企业职工基本医疗保险参保登记表 url
							//urlR = doExportRoster(vo.getSocialId(), String.valueOf(type), vo.getUnitCreditCode(), listVo)
							if (urlR != null && urlR.getCode() == 200 && Common.isNotNull(urlR.getData())) {
								vo.setQyzgjbylbxcbdjb(urlR.getData());
							}
						}
						// 派减且是要自动生成离职证明：
						if (isAutoLeaveDoc) {
							this.doSetAutoDoc(zhangMap, templateUrl, vo, CommonConstants.NINE_INTEGER);
						}
						if (isSingle && Common.isNotNull(vo.getDispatchId())) {
							// 获取单个附件并推送税友，返回 imgList 到Vo里
							this.setImgList(vo);
						}
						i=0;
						nextFlag = false;
						// 针对部分无requestId返回的情况，优化代码
						bizNo = vo.getSocialId() + type + thisTime;
						requestId = socialFriendConfig.pushFriendByInfo(restTemplate, vo, listVo, type, bizNo, backLogList
								, tSocialFriendBackLogService, isHuiZhou);
						ysdHandleStatus = CommonConstants.TWO_STRING;
						remark = "已推送提交任务！";
						if (Common.isEmpty(requestId)) {
							requestId = "推送税友未返回，请联系管理员bizNo="+bizNo;
							ysdHandleStatus = CommonConstants.FIVE_STRING;
							remark = "推送税友未返回，转人工处理！请联系管理员bizNo="+bizNo;
						}
						for (TSocialInfo s : socialInfoTemp) {
							s.setYsdRequestId(requestId);
							s.setYsdHandleStatus(ysdHandleStatus);
							socialInfoUpdate.add(s);
						}
						for (TSocialSoldierLog sLog : logTemp) {
							sLog.setRemark("【税友】" + remark);
							logList.add(sLog);
						}
						// 重置
						listVo = new ArrayList<>();
						huaListVo = new ArrayList<>();
						socialInfoTemp = new ArrayList<>();
						logTemp = new ArrayList<>();
					}
					i++;
				} catch (Exception e) {
					remark = "提交社保士兵异常！";
				}
			} else {
				if (0 == canPushType) {
					remark = "未到时间，不推送！";
				} else {
					remark = "无时间配置或超出办理截止时间，转人工！";
					socialInfo = new TSocialInfo();
					socialInfo.setId(vo.getSocialId());
					socialInfo.setYsdHandleStatus(CommonConstants.FIVE_STRING);
					renGongList.add(socialInfo);
				}
				socialLog = tSocialSoldierLogService.getFriendLog(vo, typeStr, dispatchItem, remark);
				if (socialLog != null) {
					logList.add(socialLog);
				}
			}
			//}
		}
		if (!backLogList.isEmpty()) {
			tSocialFriendBackLogService.saveBatch(backLogList);
		}
		if (!socialInfoUpdate.isEmpty()) {
			tSocialInfoService.updateBatchById(socialInfoUpdate);
		}
		if (!renGongList.isEmpty()) {
			tSocialInfoService.updateBatchById(renGongList);
		}
		if (!logList.isEmpty()) {
			tSocialSoldierLogService.saveBatch(logList);
		}
	}

	/**
	 * @param socialSetMap 时间限制配置Map
	 * @param vo           社保或医保
	 * @Description:
	 * @Author: hgw
	 * @Date: 2024/9/2 16:00
	 * @return: int
	 **/
	private int getCanPushType(Map<String, TSocialDeadlineInfo> socialSetMap, SociaFriendYgsAddVo vo) {
		// 0：未到时间不动; 1：推送; 2：转人工；3预减员
		int canPushType = 0;
		if (socialSetMap != null) {
			TSocialDeadlineInfo socialSet;
			StringBuilder addressKey;
			Date nowTime = new Date();
			if (Common.isNotNull(vo.getSocialProvince())) {
				addressKey = new StringBuilder(vo.getSocialProvince()).append(CommonConstants.DOWN_LINE_STRING);
				if (Common.isEmpty(vo.getSocialCity())) {
					addressKey.append(CommonConstants.CENTER_SPLIT_LINE_STRING);
				} else {
					addressKey.append(vo.getSocialCity());
				}
				addressKey.append(CommonConstants.DOWN_LINE_STRING);
				if (Common.isEmpty(vo.getSocialTown())) {
					addressKey.append(CommonConstants.CENTER_SPLIT_LINE_STRING);
				} else {
					addressKey.append(vo.getSocialTown());
				}
				socialSet = socialSetMap.get(addressKey.toString());

				if (socialSet != null) {
					Date start = socialSet.getSocialStartDate();
					Date end = socialSet.getSocialEndDate();
					if ("派减".equals(vo.getType())) {
						start = socialSet.getSocialStartDateReduce();
						end = socialSet.getSocialEndDateReduce();
					}
					if (Common.isNotNull(start) && Common.isNotNull(end)) {
						if (nowTime.before(start)) {
							canPushType = 0;
						} else if (nowTime.after(end)) {
							start = socialSet.getSocialPreReduceStartDate();
							end = socialSet.getSocialPreReduceEndDate();
							// 预减员
							if (start != null && end != null && start.before(nowTime) && nowTime.before(end)) {
								canPushType = 3;
							} else {
								canPushType = 2;
							}
						} else {
							canPushType = 1;
						}
					} else {
						// 无配置或配置时间为空
						canPushType = 2;
					}
				} else {
					// 无配置或配置时间为空
					canPushType = 2;
				}
			}
		} else {
			// 2无时间限制的转人工
			canPushType = 2;
		}
		return canPushType;
	}

	private int getCanPushTypeYsd(Map<String, TSocialDeadlineInfo> socialSetMap, SociaFriendYgsAddVo vo) {
		// 0：未到时间不动; 1：推送; 2：转人工
		int canPushType = 0;
		if (socialSetMap != null) {
			TSocialDeadlineInfo socialSet;
			StringBuilder addressKey;
			Date nowTime = new Date();
			if (Common.isNotNull(vo.getSocialProvince())) {
				addressKey = new StringBuilder(vo.getSocialProvince()).append(CommonConstants.DOWN_LINE_STRING);
				if (Common.isEmpty(vo.getSocialCity())) {
					addressKey.append(CommonConstants.CENTER_SPLIT_LINE_STRING);
				} else {
					addressKey.append(vo.getSocialCity());
				}
				addressKey.append(CommonConstants.DOWN_LINE_STRING);
				if (Common.isEmpty(vo.getSocialTown())) {
					addressKey.append(CommonConstants.CENTER_SPLIT_LINE_STRING);
				} else {
					addressKey.append(vo.getSocialTown());
				}
				socialSet = socialSetMap.get(addressKey.toString());

				if (socialSet != null) {
					Date start = socialSet.getMedicalStartDate();
					Date end = socialSet.getMedicalEndDate();
					if ("派减".equals(vo.getType())) {
						start = socialSet.getMedicalStartDateReduce();
						end = socialSet.getMedicalEndDateReduce();
					}
					if (Common.isNotNull(start) && Common.isNotNull(end)) {
						if (nowTime.before(start)) {
							canPushType = 0;
						} else if (nowTime.after(end)) {
							canPushType = 2;
						} else {
							canPushType = 1;
						}
					} else {
						// 无配置或配置时间为空
						canPushType = 2;
					}
				} else {
					// 无配置或配置时间为空
					canPushType = 2;
				}
			}
		} else {
			// 2无时间限制的转人工
			canPushType = 2;
		}
		return canPushType;
	}

	/**
	 * @return
	 * @Author fxj
	 * @Description 查询当月已经审核通过的未办理完成的社保或医保 增减信息
	 * @Date 15:58 2024/12/10
	 * @Param socialId：社保明细ID type：  1 社保增  2社保减  3 医保增 4 医保减 unitCreditCode：单位统一信用代码
	 **/
	private R<String> doExportRosterNew(SociaFriendYgsAddVo vo, int type, List<SociaFriendYgsAddVo> listVo
			,Map<String, URL> zhangMap, Map<String, URL> templateSrcMap) {

		if (vo == null || Common.isEmpty(listVo)) {
			return R.failed(CommonConstants.PARAM_IS_NOT_ERROR);
		}
		//社保增减
		return doExportRosterSocialNew(vo, type, listVo, zhangMap, templateSrcMap);
	}

	/**
	 * @return
	 * @Author fxj
	 * @Description 查询当月已经审核通过的未办理完成的社保或医保 增减信息
	 * @Date 15:58 2024/12/10
	 * @Param socialId：社保明细ID type：  1 社保增  2社保减  3 医保增 4 医保减 unitCreditCode：单位统一信用代码
	 **/
	@Override
	public R<String> doExportRoster(String socialId, String type, String unitCreditCode, List<SociaFriendYgsAddVo> listVo) {
		SocialHandleSearchVo searchVo = new SocialHandleSearchVo();
		if (Common.isEmpty(socialId) || Common.isEmpty(type) || Common.isEmpty(unitCreditCode)) {
			return R.failed(CommonConstants.PARAM_IS_NOT_ERROR);
		}
		TSocialInfo socialInfo = socialMapper.selectById(socialId);
		if (Common.isEmpty(socialInfo)) {
			return R.failed(CommonConstants.PARAM_IS_NOT_ERROR + "：未获取到社保明细信息");
		}
		//查询户名称对应的户配置 以获取公章信息
		SysHouseHoldInfo holdInfo = houseHoldInfoMapper.selectOne(Wrappers.<SysHouseHoldInfo>query().lambda()
				.eq(SysHouseHoldInfo::getName, socialInfo.getSocialHouseholdName())
				.eq(SysHouseHoldInfo::getType, 0)
				.eq(SysHouseHoldInfo::getDelFlag, 0)
				.last(CommonConstants.LAST_ONE_SQL));
		if (Common.isEmpty(holdInfo)) {
			return R.failed(CommonConstants.RESULT_DATA_FAIL + ":获取户配置信息失败");
		}
		TAttaInfo att = attaInfoMapper.selectOne(Wrappers.<TAttaInfo>query().lambda()
				.eq(TAttaInfo::getDomainId, holdInfo.getId()).last(CommonConstants.LAST_ONE_SQL));
		if (Common.isEmpty(att)) {
			return R.failed(CommonConstants.RESULT_DATA_FAIL + ":获取电子印章信息失败");
		}
		searchVo.setSocialHouseholdName(socialInfo.getSocialHouseholdName());
		searchVo.setDisMonth(DateUtil.getThisMonth());
		searchVo.setSyFlag(CommonConstants.ONE_STRING);
		//税友特殊处理标识

		List<String> socialIdList = new ArrayList<>();
		for (SociaFriendYgsAddVo vo : listVo) {
			socialIdList.add(vo.getSocialId());
		}
		searchVo.setSocialIdList(socialIdList);
		String fileName = DispatchConstants.SOCIAL_MEDICAL_EXPORT + searchVo.getSocialHouseholdName() + "_" + socialId + CommonConstants.XLS;
		String projectRoot = System.getProperty(CommonConstants.USER_DIR);
		String filePath = projectRoot + CommonConstants.DOUBLE_LINE + fileName;
		//社保增减
		if (CommonConstants.ONE_STRING.equals(type) || CommonConstants.TWO_STRING.equals(type)) {
			return doExportRosterSocial(searchVo, type, fileName, projectRoot, filePath, att.getAttaSrc());
		}
		//医保增减
		if (CommonConstants.THREE_STRING.equals(type) || CommonConstants.FOUR_STRING.equals(type)) {
			return doExportRosterMedical(searchVo, type, fileName, projectRoot, filePath, att.getAttaSrc());
		}
		return null;
	}

	/**
	 * @return
	 * @Author fxj
	 * @Description 医保增加花名册
	 * @Date 16:29 2024/12/10
	 * @Param
	 **/
	public R<String> doExportRosterMedical(SocialHandleSearchVo searchVo,
										   String type,
										   String fileName,
										   String projectRoot,
										   String filePath,
										   String src) {

		//获取要导出的列表
		List<TDispatchSocialExportVo> list = new ArrayList<>();
		File createFile = null;
		ExcelWriter excelWriter = null;
		try {
			// 这里 需要指定写用哪个class去写，然后写到第一个sheet，然后文件流会自动关闭
			createFile = new File(filePath);
			excelWriter = EasyExcel.write(createFile).build();
			WriteSheet writeSheet;
			WriteTable writeTable0;
			WriteTable writeTable1;
			WriteTable writeTable2;
			WriteTable writeTable3;
			WriteTable writeTable4;
			TDispatchSocialExportVo vo;

			List<Integer> columnIndexes = Arrays.asList(0, 12);
			CustomCellStyleStrategy customCellStyleStrategy = new CustomCellStyleStrategy(columnIndexes);
			CustomCellStyleOneStrategy customCellStyleStrategyOne = new CustomCellStyleOneStrategy(columnIndexes);
			CustomCellStyleTwoStrategy customCellStyleStrategyTwo = new CustomCellStyleTwoStrategy(columnIndexes);
			CustomCellStyleThreeStrategy customCellStyleStrategyThree = new CustomCellStyleThreeStrategy(columnIndexes);

			writeTable0 = EasyExcel.writerTable(0).
					head(headOne()).
					registerWriteHandler(customCellStyleStrategy).
					registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 40, (short) 20)).
					build();
			writeTable1 = EasyExcel.writerTable(1).
					head(headTwo(searchVo.getSocialHouseholdName(), searchVo.getUnitCreditCode())).
					registerWriteHandler(customCellStyleStrategyOne).
					registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 25, (short) 20)).
					build();
			writeTable2 = EasyExcel.writerTable(2).
					head(headThree()).
					registerWriteHandler(customCellStyleStrategyThree).
					registerWriteHandler(new CustomCellWriteWidthConfig()).
					registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 25, (short) 20))
					.build();
			writeTable3 = EasyExcel.writerTable(3).
					head(headFour()).
					registerWriteHandler(customCellStyleStrategyTwo).
					registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 50, (short) 20))
					.build();
			writeTable4 = EasyExcel.writerTable(4).
					head(headFive()).
					registerWriteHandler(customCellStyleStrategyTwo).
					registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 25, (short) 20))
					.build();
			//派增的数据--医保增员
			if (CommonConstants.THREE_STRING.equals(type)) {
				searchVo.setType(CommonConstants.ZERO_STRING);
				long count = dispatchInfoMapper.getSocialDisRecordCount(searchVo);
				if (count > CommonConstants.ZERO_INT) {
					list = dispatchInfoMapper.getSocialDisRecord(searchVo);
					if (Common.isNotNull(list)) {
						//判断list的大小是否大于10
						if (list.size() < 10) {
							int size = 10 - list.size();
							int num = list.size() + 1;
							for (int i = 1; i <= size; i++) {
								vo = new TDispatchSocialExportVo();
								vo.setNum(num);
								num++;
								list.add(vo);
							}
						}
						writeSheet = EasyExcel.writerSheet("增员").build();
						// 第一次写入会创建头
						excelWriter.write(new ArrayList<>(), writeSheet, writeTable0);
						excelWriter.write(new ArrayList<>(), writeSheet, writeTable1);
						// 第二次写如也会创建头，然后在第一次的后面写入数据
						excelWriter.write(list, writeSheet, writeTable2);
						excelWriter.write(new ArrayList<>(), writeSheet, writeTable3);
						excelWriter.write(new ArrayList<>(), writeSheet, writeTable4);
					}
				} else {
					writeSheet = EasyExcel.writerSheet("增员").build();
					excelWriter.write(new ArrayList<>(), writeSheet, writeTable0);
					excelWriter.write(new ArrayList<>(), writeSheet, writeTable1);
					// 第二次写如也会创建头，然后在第一次的后面写入数据
					int num = 1;
					for (int i = 1; i <= 10; i++) {
						vo = new TDispatchSocialExportVo();
						vo.setNum(num);
						num++;
						list.add(vo);
					}
					excelWriter.write(list, writeSheet, writeTable2);
					excelWriter.write(new ArrayList<>(), writeSheet, writeTable3);
					excelWriter.write(new ArrayList<>(), writeSheet, writeTable4);
				}
			} else if (CommonConstants.FOUR_STRING.equals(type)) {
				//派减的数据--医保减员
				searchVo.setType(CommonConstants.ONE_STRING);
				long count1 = dispatchInfoMapper.getSocialDisRecordCount(searchVo);
				if (count1 > CommonConstants.ZERO_INT) {
					list = dispatchInfoMapper.getSocialReduceDisRecord(searchVo);
					if (Common.isNotNull(list)) {
						//判断list的大小是否大于10
						if (list.size() < 10) {
							int size = 10 - list.size();
							int num = list.size() + 1;
							for (int i = 1; i <= size; i++) {
								vo = new TDispatchSocialExportVo();
								vo.setNum(num);
								num++;
								list.add(vo);
							}
						}
						writeSheet = EasyExcel.writerSheet("减员").build();
						// 第一次写入会创建头
						excelWriter.write(new ArrayList<>(), writeSheet, writeTable0);
						excelWriter.write(new ArrayList<>(), writeSheet, writeTable1);
						// 第二次写如也会创建头，然后在第一次的后面写入数据
						excelWriter.write(list, writeSheet, writeTable2);
						excelWriter.write(new ArrayList<>(), writeSheet, writeTable3);
						excelWriter.write(new ArrayList<>(), writeSheet, writeTable4);
					}
				} else {
					writeSheet = EasyExcel.writerSheet("减员").build();
					excelWriter.write(new ArrayList<>(), writeSheet, writeTable0);
					excelWriter.write(new ArrayList<>(), writeSheet, writeTable1);
					// 第二次写如也会创建头，然后在第一次的后面写入数据
					int num = 1;
					list = new ArrayList<>();
					for (int i = 1; i <= 10; i++) {
						vo = new TDispatchSocialExportVo();
						vo.setNum(num);
						num++;
						list.add(vo);
					}
					excelWriter.write(list, writeSheet, writeTable2);
					excelWriter.write(new ArrayList<>(), writeSheet, writeTable3);
					excelWriter.write(new ArrayList<>(), writeSheet, writeTable4);
				}
			}
			if (excelWriter != null) {
				excelWriter.finish();
			}
			File outFile = null;
			File inputFile = null;
			File tempFile = null;
			FileInputStream inputStream = null;
			FileOutputStream fileOUt = null;
			InputStream inStream = null;
			FileInputStream inputStreamOut = null;
			try {

				//读取文件 生成电子章
				inputFile = new File(filePath);
				inputStream = new FileInputStream(inputFile);
				String tempPath = projectRoot + "//temp_" + fileName;
				tempFile = new File(tempPath);
				fileOUt = new FileOutputStream(tempFile);
				URL url = ossUtil.getObjectUrl(null, src);
				if (Common.isEmpty(url)) {
					return R.failed(CommonConstants.RESULT_DATA_FAIL + ":电子印章OSS获取URL异常");
				}
				inStream = url.openStream();
				if (Common.isEmpty(inStream)) {
					return R.failed(CommonConstants.RESULT_DATA_FAIL + ":电子印章OSS获取流异常");
				}
				// 添加电子章 完成后关闭所有流
				//excelMergeImage.addPictureToExcel(inputStream, inStream, fileOUt, 0, type);
				outFile = new File(tempPath);
				inputStreamOut = new FileInputStream(outFile);
				// 转换成图片 完成后清理流
				TSocialFriendBackLog backLog = new TSocialFriendBackLog();
				backLog.setType(CommonConstants.SEVEN_INT);
				backLog.setSocialId(searchVo.getSocialId());
				R<String> ulrR = excelToImage.excelToImg(searchVo.getSocialId(), ossUtil, tSocialFriendBackLogService
						, inputStream, socialFriendConfig, restTemplate, backLog, inStream);
				tSocialFriendBackLogService.save(backLog);
				return ulrR;
			} catch (Exception e) {
				return R.failed("税友附件生成异常：" + e.getMessage());
			} finally {
				// 清理文件
				finallyClose(inputFile, tempFile, outFile, inputStream, fileOUt, inStream, inputStreamOut);
			}
		} catch (Exception e) {
			if (excelWriter != null) {
				excelWriter.finish();
			}
			log.error("税友附件生成异常", e);
			return R.failed("税友附件生成异常：" + e.getMessage());
		} finally {
			if (null != createFile && createFile.exists()) {
				createFile.delete();
			}
			if (null != list) {
				list.clear();
			}
		}
	}

	private void finallyClose(File inputFile,
							  File tempFile,
							  File outFile,
							  FileInputStream inputStream,
							  FileOutputStream fileOUt,
							  InputStream inStream,
							  FileInputStream inputStreamOut) throws IOException {
		if (inputFile != null && inputFile.exists()) {
			inputFile.delete();
		}
		if (tempFile != null && tempFile.exists()) {
			tempFile.delete();
		}
		if (outFile != null && outFile.exists()) {
			outFile.delete();
		}
		if (null != inputStream) {
			inputStream.close();
		}
		if (null != fileOUt) {
			fileOUt.close();
		}
		if (null != inStream) {
			inStream.close();
		}
		if (null != inputStreamOut) {
			inputStreamOut.close();
		}
	}
	@Override
	public R<String> test(){
		SociaFriendYgsAddVo  vo = new SociaFriendYgsAddVo();
		vo.setSocialHouseholdName("11111111");
		vo.setSocialId("22222222");
		vo.setEmpName("房新江");
		List<SociaFriendYgsAddVo> listVo = new ArrayList<>();
		listVo.add(vo);
		vo = new SociaFriendYgsAddVo();
		vo.setSocialHouseholdName("11111111");
		vo.setSocialId("22222222");
		vo.setEmpName("张叁");
		listVo.add(vo);
		vo = new SociaFriendYgsAddVo();
		vo.setSocialHouseholdName("11111111");
		vo.setSocialId("22222222");
		vo.setEmpName("张叁");
		listVo.add(vo);
		vo = new SociaFriendYgsAddVo();
		vo.setSocialHouseholdName("11111111");
		vo.setSocialId("22222222");
		vo.setEmpName("张叁");
		listVo.add(vo);
		vo = new SociaFriendYgsAddVo();
		vo.setSocialHouseholdName("11111111");
		vo.setSocialId("22222222");
		vo.setEmpName("张叁");
		listVo.add(vo);
		vo = new SociaFriendYgsAddVo();
		vo.setSocialHouseholdName("11111111");
		vo.setSocialId("22222222");
		vo.setEmpName("张叁");
		listVo.add(vo);
		String huId ="1911679205825486849";
		String fileName = DispatchConstants.SOCIAL_MEDICAL_EXPORT + vo.getSocialHouseholdName() + "_" + vo.getSocialId() + CommonConstants.XLS;
		String projectRoot = System.getProperty(CommonConstants.USER_DIR);
		String filePath = projectRoot + CommonConstants.DOUBLE_LINE + fileName;
		//获取要导出的列表
		List<TDispatchSocialPersionExportVo> list = new ArrayList<>();
		ExcelWriter excelWriter = null;
		File createFile = null;
		try {
			// 通用的模板+查到的户的模板map
			Map<String, URL> templateSrcMap = new HashMap<>();
			// 通用的花名册模板
			List<TAttaInfo> srcList = attaInfoMapper.getTemplateSrcById();
			URL url;
			if (srcList != null && !srcList.isEmpty()) {
				for (TAttaInfo src : srcList) {
					url = ossUtil.getObjectUrl(null, src.getAttaSrc());
					templateSrcMap.put("template_" + src.getId(), url);
				}
			}
			int type = CommonConstants.TWO_INT;
			String templateId = "template_11";
			String relationType = "11";
			if (type == CommonConstants.TWO_INT) {
				templateId = "template_12";
				relationType = "12";
			} else if (type == CommonConstants.THREE_INT) {
				templateId = "template_13";
				relationType = "13";
			} else if (type == CommonConstants.FOUR_INT) {
				templateId = "template_14";
				relationType = "14";
			}
			String huKey = type + CommonConstants.DOWN_LINE_STRING + huId;
			URL templateUrl = templateSrcMap.get(huKey);
			if (Common.isEmpty(templateUrl)) {
				String templateSrc = attaInfoMapper.getTemplateSrcByIdAndType(huId, relationType);
				if (Common.isNotNull(templateSrc)) {
					templateUrl = ossUtil.getObjectUrl(null, templateSrc);
				} else {
					templateUrl = templateSrcMap.get(templateId);
					templateSrcMap.put(huKey, templateUrl);
				}
			}
			if (Common.isEmpty(templateUrl)) {
				return R.failed(CommonConstants.RESULT_DATA_FAIL + ":花名册模板OSS获取URL异常");
			}
			InputStream templateInStream = templateUrl.openStream();
			if (Common.isEmpty(templateInStream)) {
				return R.failed(CommonConstants.RESULT_DATA_FAIL + ":花名册模板OSS获取流异常");
			}
			try (Workbook workbook = WorkbookFactory.create(templateInStream)) {

				Sheet sheet = workbook.getSheetAt(0);

				// 1. 填充主表数据
				fillMainData(sheet, vo);

				// 2. 填充明细表数据
				fillDetailData(sheet, listVo);

				// 保存文件
				try (FileOutputStream fos = new FileOutputStream(filePath)) {
					workbook.write(fos);
				}

			} catch (Exception e) {
				throw new RuntimeException("填充Excel模板失败", e);
			}
		} catch (Exception e) {
			throw new RuntimeException("填充Excel模板失败", e);
		}
		return R.ok("成功");
	}
	/**
	 * @Author hgw
	 * @Description 社保增减花名册
	 * @Date 2025-9-3 16:20:32
	 **/
	public R<String> doExportRosterSocialNew(SociaFriendYgsAddVo vo, int type, List<SociaFriendYgsAddVo> listVo
			, Map<String, URL> zhangMap, Map<String, URL> templateSrcMap) {
		String huId = vo.getHuId();
		URL url = zhangMap.get(vo.getSocialHouseholdName());
		if (Common.isEmpty(url)) {
			TAttaInfo att = attaInfoMapper.selectOne(Wrappers.<TAttaInfo>query().lambda()
					.eq(TAttaInfo::getDomainId, huId).last(CommonConstants.LAST_ONE_SQL));
			if (Common.isEmpty(att)) {
				return  R.failed("获取电子印章信息失败");
			}
			url = ossUtil.getObjectUrl(null, att.getAttaSrc());
			zhangMap.put(vo.getSocialHouseholdName(), url);
		}

		String fileName = DispatchConstants.SOCIAL_MEDICAL_EXPORT + vo.getSocialHouseholdName() + "_" + vo.getSocialId() + CommonConstants.XLS;
		String projectRoot = System.getProperty(CommonConstants.USER_DIR);
		String filePath = projectRoot + CommonConstants.DOUBLE_LINE + fileName;
		//获取要导出的列表
		List<TDispatchSocialPersionExportVo> list = new ArrayList<>();
		//ExcelWriter excelWriter = null;
		File createFile = null;
		try {


			String templateId = "template_11";
			String relationType = "11";
			if (type == CommonConstants.TWO_INT) {
				templateId = "template_12";
				relationType = "12";
			} else if (type == CommonConstants.THREE_INT) {
				templateId = "template_13";
				relationType = "13";
			} else if (type == CommonConstants.FOUR_INT) {
				templateId = "template_14";
				relationType = "14";
			}
			String huKey = type + CommonConstants.DOWN_LINE_STRING + huId;
			URL templateUrl = templateSrcMap.get(huKey);
			if (Common.isEmpty(templateUrl)) {
				String templateSrc = attaInfoMapper.getTemplateSrcByIdAndType(huId, relationType);
				if (Common.isNotNull(templateSrc)) {
					templateUrl = ossUtil.getObjectUrl(null, templateSrc);
				} else {
					templateUrl = templateSrcMap.get(templateId);
					templateSrcMap.put(huKey, templateUrl);
				}
			}
			if (Common.isEmpty(templateUrl)) {
				return R.failed(CommonConstants.RESULT_DATA_FAIL + ":花名册模板OSS获取URL异常");
			}
			InputStream templateInStream = templateUrl.openStream();
			if (Common.isEmpty(templateInStream)) {
				return R.failed(CommonConstants.RESULT_DATA_FAIL + ":花名册模板OSS获取流异常");
			}
			try (Workbook workbook = WorkbookFactory.create(templateInStream)) {

				Sheet sheet = workbook.getSheetAt(0);

				// 1. 填充主表数据
				fillMainData(sheet, vo);

				// 2. 填充明细表数据
				fillDetailData(sheet, listVo);

				// 保存文件
				try (FileOutputStream fos = new FileOutputStream(filePath)) {
					workbook.write(fos);
				}

			} catch (Exception e) {
				throw new RuntimeException("填充Excel模板失败", e);
			}

			FileInputStream inputStream = null;
			InputStream inStream = null;
			try {
				//读取文件 生成电子章
				createFile = new File(filePath);
				inputStream = new FileInputStream(createFile);

				if (Common.isEmpty(url)) {
					return R.failed(CommonConstants.RESULT_DATA_FAIL + ":电子印章OSS获取URL异常");
				}
				inStream = url.openStream();
				if (Common.isEmpty(inStream)) {
					return R.failed(CommonConstants.RESULT_DATA_FAIL + ":电子印章OSS获取流异常");
				}

				// 转换成图片 完成后清理流
				TSocialFriendBackLog backLog = new TSocialFriendBackLog();
				backLog.setSocialId(vo.getSocialId());
				backLog.setType(CommonConstants.SIX_INT);
				backLog.setCreateTime(LocalDateTime.now());

				R<String> ulrR = excelToImage.excelToImg(vo.getSocialId(), ossUtil, tSocialFriendBackLogService
						, inputStream, socialFriendConfig, restTemplate, backLog, inStream);
				tSocialFriendBackLogService.save(backLog);
				return ulrR;
			} catch (Exception e) {
				return R.failed("税友附件生成异常：" + e.getMessage());
			} finally {
				// 清理文件
				finallyClose(null, null, null, inputStream, null, inStream, null);
			}
		} catch (Exception e) {
			/*if (excelWriter != null) {
				excelWriter.finish();
			}*/
			log.error("税友附件生成异常", e);
			return R.failed("税友附件生成异常：" + e.getMessage());
		} finally {
			finallyClear(list, createFile);
		}
	}



	/**
	 * 填充主表数据
	 */
	private static void fillMainData(Sheet sheet, SociaFriendYgsAddVo vo) {
		String cellValue;
		String key;
		String newValue;
		String[] qianMingArr;
		for (Row row : sheet) {
			for (Cell cell : row) {
				if (cell.getCellType() == CellType.STRING) {
					cellValue = cell.getStringCellValue();
					if (cellValue.startsWith("${") && cellValue.endsWith("}")) {
						// 正常的主表的单元格的占位，类似"${empName}"
						key = cellValue.substring(2, cellValue.length() - 1);
						if (key.contains(CommonConstants.QIAN_MING)) {
							qianMingArr = key.split("&");
							if (qianMingArr.length>1) {
								key = qianMingArr[1];
							} else {
								key = "empName";
							}
							newValue = getFieldValue(vo, key);
							setQianMingStyle(cell, newValue);
						} else {
							newValue = getFieldValue(vo, key);
							cell.setCellValue(newValue);
						}
					} else if (containsPlaceholder(cellValue)) {
						// 非正常的主表的单元格的占位，类似"经办人：${empName} 单位名称${unitName}"
						newValue = replaceMixedPlaceholders(cellValue, vo);
						cell.setCellValue(newValue);
					}
				}
			}
		}
	}

	public static String getFieldValue(Object obj, String fieldName) {
		try {
			if (obj != null) {
				Field field = ReflectionUtils.findField(obj.getClass(), fieldName);
				if (field != null) {
					field.setAccessible(true);
					Object value = ReflectionUtils.getField(field, obj);
					if (Common.isNotNull(value)) {
						return String.valueOf(value);
					}
				}
			}
		} catch (Exception e) {
			return "";
		}
		return "";
	}

	/**
	 * @Description: 签名样式
	 * @Author: hgw
	 * @Date: 2025/9/2 9:51
	 * @return: void
	 **/
	private static void setQianMingStyle(Cell cell, String value) {
		if (cell != null && Common.isNotNull(value)) {
			// 加签名样式  随机的【潦草】字体样式设置
			//cell.setCellValue(value);
			RandomFontStyleUtil.setRandomSignatureStyle(cell, value);
		}
	}

	/**
	 * 检查字符串是否包含占位符
	 */
	private static boolean containsPlaceholder(String text) {
		return text != null && text.contains("${");
	}

	/**
	 * 替换混合文本中的占位符
	 */
	private static String replaceMixedPlaceholders(String cellValue, SociaFriendYgsAddVo vo) {
		if (cellValue == null) {
			return cellValue;
		}
		// 使用正则表达式匹配 ${xxx} 格式的占位符
		Pattern pattern = Pattern.compile("\\$\\{([^}]+)\\}");
		Matcher matcher = pattern.matcher(cellValue);
		StringBuffer result = new StringBuffer();
		String key;
		String oldKey;
		String newValue;
		String[] qianMingArr;
		while (matcher.find()) {
			key = matcher.group(1); // 获取占位符名称（去掉${}）
			if (Common.isNotNull(key)) {
				oldKey = key;
				if (key.contains(CommonConstants.QIAN_MING)) {
					qianMingArr = key.split("&");
					if (qianMingArr.length>1) {
						key = qianMingArr[1];
					} else {
						key = "empName";
					}
				}
				newValue = getFieldValue(vo, key);
				if (Common.isEmpty(newValue)) {
					// 如果找不到，保留原占位符
					newValue = "${" + oldKey + "}";
				}
				matcher.appendReplacement(result, newValue.replace("\\", "\\\\").replace("$", "\\$"));
			}
		}
		matcher.appendTail(result);
		return result.toString();
	}

	/**
	 * 填充明细表数据
	 */
	private static void fillDetailData(Sheet sheet, List<SociaFriendYgsAddVo> listVo) {
		if (listVo == null || listVo.isEmpty()) return;

		// 查找明细区域开始和结束位置
		int startRow = -1;
		int endRow = -1;
		Map<String, Integer> columnMapping = new java.util.HashMap<>();
		Row row;
		Cell cell;
		String value;
		String key;
		// 查找表头行和模板行
		for (int i = 0; i <= sheet.getLastRowNum(); i++) {
			row = sheet.getRow(i);
			if (row == null) continue;

			for (int j = 0; j < row.getLastCellNum(); j++) {
				cell = row.getCell(j);
				if (cell != null && cell.getCellType() == CellType.STRING) {
					value = cell.getStringCellValue();
					if (Common.isNotNull(value)) {
						// 查找表头
						if (value.startsWith("#{") && !value.equals("#{end}")) {
							key = value.substring(2, value.length() - 1);
							columnMapping.put(key+CommonConstants.DUNHAO_STRING+j, j);
							if (startRow == -1) {
								startRow = i;
							}
						}
						// 查找结束标记
						else if (value.equals("#{end}")) {
							endRow = i;
							cell.setCellValue("");
							break;
						}
					}
				}
			}
			if (endRow != -1) break;
		}

		if (startRow == -1 || endRow == -1) return;

		// 插入数据行
		SociaFriendYgsAddVo vo;
		Row newRow;
		int columnIndex;
		String newValue;
		String[] qianMingArr;
		for (int i = 0; i < listVo.size(); i++) {
			vo = listVo.get(i);
			newRow = sheet.getRow(startRow + i);

			// 填充数据
			for (Map.Entry<String, Integer> entry : columnMapping.entrySet()) {
				key = entry.getKey();
				if (Common.isNotNull(key)) {
					qianMingArr = key.split(CommonConstants.DUNHAO_STRING);
					key = qianMingArr[0];
					columnIndex = entry.getValue();
					cell = newRow.getCell(columnIndex);
					if (cell == null) {
						cell = newRow.createCell(columnIndex);
					}
					if (key.contains(CommonConstants.QIAN_MING)) {
						// 签名样式
						qianMingArr = key.split(AND_STR);
						if (qianMingArr.length>1) {
							key = qianMingArr[1];
						} else {
							key = "empName";
						}
						newValue = getFieldValue(vo, key);
						setQianMingStyle(cell, newValue);
					} else if (key.contains(CommonConstants.GU_DING)) {
						// 固定文字
						qianMingArr = key.split(AND_STR);
						if (qianMingArr.length>1) {
							key = qianMingArr[1];
							cell.setCellValue(key);
						}
					} else if (key.contains(AND_STR)) {
						qianMingArr = key.split(AND_STR);
						newValue = "";
						for (String s : qianMingArr) {
							newValue += getFieldValue(vo, s);
						}
						cell.setCellValue(newValue);
					} else {
						newValue = getFieldValue(vo, key);
						cell.setCellValue(newValue);
					}
				}
			}
		}
	}

	/**
	 * @return
	 * @Author fxj
	 * @Description 社保增减花名册
	 * @Date 16:28 2024/12/10
	 * @Param
	 **/
	public R<String> doExportRosterSocial(SocialHandleSearchVo searchVo,
										  String type,
										  String fileName,
										  String projectRoot,
										  String filePath,
										  String src) {
		//获取要导出的列表
		List<TDispatchSocialPersionExportVo> list = new ArrayList<>();
		ExcelWriter excelWriter = null;
		File createFile = null;
		try {
			// 这里 需要指定写用哪个class去写，然后写到第一个sheet，然后文件流会自动关闭
			createFile = new File(filePath);
			excelWriter = EasyExcel.write(createFile).build();
			WriteSheet writeSheet;
			WriteTable writeTable0;
			WriteTable writeTable1;
			WriteTable writeTable2;
			WriteTable writeTable3;
			WriteTable writeTable4;
			TDispatchSocialPersionExportVo vo;
			TDispatchSocialPersionReduceExportVo reduceVo;
			if (CommonConstants.ONE_STRING.equals(type)) {
				//派增的数据--社保
				searchVo.setType(CommonConstants.ZERO_STRING);
				long count = dispatchInfoMapper.getSocialPersionDisCount(searchVo);
				List<Integer> columnIndexes = Arrays.asList(0, 17);
				CustomCellStyleStrategy customCellStyleStrategy = new CustomCellStyleStrategy(columnIndexes);
				CustomCellStyleOneStrategy customCellStyleStrategyOne = new CustomCellStyleOneStrategy(columnIndexes);
				CustomCellStyleTwoStrategy customCellStyleStrategyTwo = new CustomCellStyleTwoStrategy(columnIndexes);
				CustomCellStyleThreeStrategy customCellStyleStrategyThree = new CustomCellStyleThreeStrategy(columnIndexes);

				writeTable0 = EasyExcel.writerTable(0).
						head(persionHeadOne()).
						registerWriteHandler(customCellStyleStrategy).
						registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 40, (short) 20)).
						build();
				writeTable1 = EasyExcel.writerTable(1).
						head(persionHeadTwo(searchVo.getSocialHouseholdName(), searchVo.getCustomerNo())).
						registerWriteHandler(customCellStyleStrategyOne).
						registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 20, (short) 20)).
						build();
				writeTable2 = EasyExcel.writerTable(2).
						head(persionHead()).
						registerWriteHandler(customCellStyleStrategyThree).
						registerWriteHandler(new CustomCellWriteWidthOneConfig()).
						registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 30, (short) 20))
						.build();
				writeTable3 = EasyExcel.writerTable(3).
						head(persionHeadThree()).
						registerWriteHandler(customCellStyleStrategyTwo).
						registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 150, (short) 20))
						.build();
				writeTable4 = EasyExcel.writerTable(4).
						head(persionHead1()).
						registerWriteHandler(customCellStyleStrategyTwo).
						registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 20, (short) 20))
						.build();
				if (count > CommonConstants.ZERO_INT) {
					list = dispatchInfoMapper.getSocialPersionDisRecord(searchVo);
					if (Common.isNotNull(list)) {
						//判断list的大小是否大于10
						if (list.size() < 10) {
							int size = 10 - list.size();
							int num = list.size() + 1;
							for (int i = 1; i <= size; i++) {
								vo = new TDispatchSocialPersionExportVo();
								vo.setNum(num);
								num++;
								list.add(vo);
							}
						}
						writeSheet = EasyExcel.writerSheet("增员").build();
						// 第一次写入会创建头
						// 第二次写如也会创建头，然后在第一次的后面写入数据
						excelWriter.write(new ArrayList<>(), writeSheet, writeTable0);
						excelWriter.write(new ArrayList<>(), writeSheet, writeTable1);
						excelWriter.write(list, writeSheet, writeTable2);
						excelWriter.write(new ArrayList<>(), writeSheet, writeTable3);
						excelWriter.write(new ArrayList<>(), writeSheet, writeTable4);
					}
				} else {
					writeSheet = EasyExcel.writerSheet("增员").build();
					excelWriter.write(new ArrayList<>(), writeSheet, writeTable0);
					excelWriter.write(new ArrayList<>(), writeSheet, writeTable1);
					int num = 1;
					for (int i = 1; i <= 10; i++) {
						vo = new TDispatchSocialPersionExportVo();
						vo.setNum(num);
						num++;
						list.add(vo);
					}
					excelWriter.write(list, writeSheet, writeTable2);
					excelWriter.write(new ArrayList<>(), writeSheet, writeTable3);
					excelWriter.write(new ArrayList<>(), writeSheet, writeTable4);
				}
			} else if (CommonConstants.TWO_STRING.equals(type)) {
				//派减的数据--社保
				searchVo.setType(CommonConstants.ONE_STRING);
				long count1 = dispatchInfoMapper.getSocialPersionReduceDisCount(searchVo);
				//获取要导出的列表
				List<TDispatchSocialPersionReduceExportVo> listReduce = new ArrayList<>();

				List<Integer> columnIndex = Arrays.asList(0, 24);
				CustomCellStyleStrategy customCellStyleStrategy1 = new CustomCellStyleStrategy(columnIndex);
				CustomCellStyleOneStrategy customCellStyleStrategy2 = new CustomCellStyleOneStrategy(columnIndex);
				CustomCellStyleTwoStrategy customCellStyleStrategy3 = new CustomCellStyleTwoStrategy(columnIndex);
				CustomCellStyleThreeStrategy customCellStyleStrategy4 = new CustomCellStyleThreeStrategy(columnIndex);

				writeTable0 = EasyExcel.writerTable(0).
						head(persionReduceHeadOne()).
						registerWriteHandler(customCellStyleStrategy1).
						registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 40, (short) 20)).
						build();
				writeTable1 = EasyExcel.writerTable(1).
						head(persionReduceHeadTwo(searchVo.getSocialHouseholdName(), searchVo.getCustomerNo())).
						registerWriteHandler(customCellStyleStrategy2).
						registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 20, (short) 20)).
						build();
				writeTable2 = EasyExcel.writerTable(2).
						head(persionReduceHead()).
						registerWriteHandler(customCellStyleStrategy4).
						registerWriteHandler(new CustomCellWriteWidthTwoConfig()).
						registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 50, (short) 20))
						.build();
				writeTable3 = EasyExcel.writerTable(3).
						head(persionReduceHead1()).
						registerWriteHandler(customCellStyleStrategy3).
						registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 40, (short) 20))
						.build();
				if (count1 > CommonConstants.ZERO_INT) {
					listReduce = dispatchInfoMapper.getSocialPersionReduceDisRecord(searchVo);
					if (Common.isNotNull(listReduce)) {
						//判断list的大小是否大于10
						if (listReduce.size() < 10) {
							int size = 10 - listReduce.size();
							int num = listReduce.size() + 1;
							for (int i = 1; i <= size; i++) {
								reduceVo = new TDispatchSocialPersionReduceExportVo();
								reduceVo.setNum(num);
								num++;
								listReduce.add(reduceVo);
							}
						}
						writeSheet = EasyExcel.writerSheet("减员").build();
						// 第一次写入会创建头
						// 第二次写如也会创建头，然后在第一次的后面写入数据
						excelWriter.write(new ArrayList<>(), writeSheet, writeTable0);
						excelWriter.write(new ArrayList<>(), writeSheet, writeTable1);
						excelWriter.write(listReduce, writeSheet, writeTable2);
						excelWriter.write(new ArrayList<>(), writeSheet, writeTable3);
					}
				} else {
					writeSheet = EasyExcel.writerSheet("减员").build();
					excelWriter.write(new ArrayList<>(), writeSheet, writeTable0);
					excelWriter.write(new ArrayList<>(), writeSheet, writeTable1);
					int num = 1;
					for (int i = 1; i <= 10; i++) {
						reduceVo = new TDispatchSocialPersionReduceExportVo();
						reduceVo.setNum(num);
						num++;
						listReduce.add(reduceVo);
					}
					excelWriter.write(listReduce, writeSheet, writeTable2);
					excelWriter.write(new ArrayList<>(), writeSheet, writeTable3);
				}
				if (Common.isNotNull(listReduce)) {
					listReduce.clear();
				}
			}
			if (excelWriter != null) {
				excelWriter.finish();
			}
			File inputFile = null;
			File tempFile = null;
			File outFile = null;
			FileInputStream inputStream = null;
			FileOutputStream fileOUt = null;
			InputStream inStream = null;
			FileInputStream inputStreamOut = null;
			try {
				//读取文件 生成电子章
				inputFile = new File(filePath);
				inputStream = new FileInputStream(inputFile);
				String tempPath = projectRoot + "//temp_" + fileName;
				tempFile = new File(tempPath);
				fileOUt = new FileOutputStream(tempFile);
				URL url = ossUtil.getObjectUrl(null, src);
				if (Common.isEmpty(url)) {
					return R.failed(CommonConstants.RESULT_DATA_FAIL + ":电子印章OSS获取URL异常");
				}
				inStream = url.openStream();
				if (Common.isEmpty(inStream)) {
					return R.failed(CommonConstants.RESULT_DATA_FAIL + ":电子印章OSS获取流异常");
				}
				// 添加电子章 完成后关闭所有流
				// ——在excel生成图片后加章，在excelToImg方法里 完成后关闭所有流
				//excelMergeImage.addPictureToExcel(inputStream, inStream, fileOUt, 0, type);
				outFile = new File(tempPath);
				inputStreamOut = new FileInputStream(outFile);
				// 转换成图片 完成后清理流
				TSocialFriendBackLog backLog = new TSocialFriendBackLog();
				backLog.setSocialId(searchVo.getSocialId());
				backLog.setType(CommonConstants.SIX_INT);
				backLog.setCreateTime(LocalDateTime.now());
				//R<String> ulrR = excelToImage.excelToImg(searchVo.getSocialId(), ossUtil, tSocialFriendBackLogService
				//		, inputStreamOut, socialFriendConfig, restTemplate, backLog, inStream);
				R<String> ulrR = excelToImage.excelToImg(searchVo.getSocialId(), ossUtil, tSocialFriendBackLogService
						, inputStream, socialFriendConfig, restTemplate, backLog, inStream);
				tSocialFriendBackLogService.save(backLog);
				return ulrR;
			} catch (Exception e) {
				return R.failed("税友附件生成异常：" + e.getMessage());
			} finally {
				// 清理文件
				finallyClose(inputFile, tempFile, outFile, inputStream, fileOUt, inStream, inputStreamOut);
			}
		} catch (Exception e) {
			if (excelWriter != null) {
				excelWriter.finish();
			}
			log.error("税友附件生成异常", e);
			return R.failed("税友附件生成异常：" + e.getMessage());
		} finally {
			finallyClear(list, createFile);
		}
	}

	private void finallyClear(List<TDispatchSocialPersionExportVo> list, File createFile) {
		if (null != createFile && createFile.exists()) {
			createFile.delete();
		}
		if (null != list) {
			list.clear();
		}
	}

	/**
	 * @return
	 * @Author huyc
	 * @Description 导出社保派单花名册
	 * @Date 09:05 2023/7/19
	 */
	public List<List<String>> headOne() {
		List<List<String>> list = new ArrayList<>();
		//表头数据
		String a = "合肥市基本医疗保险参保登记表";
		//第一列-第十三列
		for (int i = 1; i <= 13; i++) {
			list.add(Lists.newArrayList(a));
		}
		return list;
	}

	/**
	 * @return
	 * @Author huyc
	 * @Description 导出社保派单花名册
	 * @Date 09:05 2023/7/19
	 */
	public List<List<String>> headTwo(String houseName, String cusNo) {
		List<List<String>> list = new ArrayList<>();
		//表头数据
		String b = "单位名称(盖章): " + houseName + "   单位统一信用代码:" + (null == cusNo ? " " : cusNo);
		String c = "人员类型： □√单位职工      □灵活就业人员";
		for (int i = 1; i <= 13; i++) {
			list.add(Lists.newArrayList(b, c));
		}
		return list;
	}

	/**
	 * @return
	 * @Author huyc
	 * @Description 导出社保派单花名册
	 * @Date 09:05 2023/7/19
	 */
	public List<List<String>> headThree() {
		List<List<String>> list = new ArrayList<>();
		//表头数据
		String d = "变更类别";
		//第一列
		list.add(Lists.newArrayList("序号", "序号"));
		//第二列
		list.add(Lists.newArrayList("姓名", "姓名"));
		//第三列
		list.add(Lists.newArrayList("身份证件类型", "身份证件类型"));
		//第四列
		list.add(Lists.newArrayList("身份证件号码", "身份证件号码"));
		//第五列
		list.add(Lists.newArrayList("参报/停保时间", "参报/停保时间"));
		//第六列
		list.add(Lists.newArrayList("申报工资（元）", "申报工资（元）"));
		//变更类别
		List<String> orderSpeaces = Lists.newArrayList("新增", "续保", "中断", "终止");
		orderSpeaces.forEach(e -> {
			list.add(Lists.newArrayList(d, e));
		});
		//第十一列
		list.add(Lists.newArrayList("手机号码", "手机号码"));
		//第十二列
		list.add(Lists.newArrayList("学历", "学历"));
		//第十三列
		list.add(Lists.newArrayList("备注", "备注"));
		return list;
	}

	/**
	 * @return
	 * @Author huyc
	 * @Description 导出社保派单花名册
	 * @Date 09:05 2023/7/19
	 */
	public List<List<String>> headFour() {
		List<List<String>> list = new ArrayList<>();
		//表头数据
		String a = "备注： 1.身份证类型填写：身份证、护照、港澳台通行证等； \n" +
				"       2.增加人员勾选新增或续保，减少人员勾选中断或终止，按相应类型进行勾选；\n" +
				"       3.联系电话务必填写准确的手机号码；";
		//第一列-第十三列
		for (int i = 1; i <= 13; i++) {
			list.add(Lists.newArrayList(a));
		}
		return list;
	}

	/**
	 * @return
	 * @Author huyc
	 * @Description 导出社保派单花名册
	 * @Date 09:05 2023/7/19
	 */
	public List<List<String>> headFive() {
		List<List<String>> list = new ArrayList<>();
		String b = "填报人：                 联系电话：                  经办人（盖章）：            " +
				DateUtil.getYear(DateUtil.getCurrentDateTime()) + " 年" + " " +
				DateUtil.getMonth(DateUtil.getCurrentDateTime()) + " 月" + " " +
				DateUtil.getDay(DateUtil.getCurrentDateTime()) + " 日";
		//第一列-第十三列
		for (int i = 1; i <= 13; i++) {
			list.add(Lists.newArrayList(b));
		}
		return list;
	}

	/**
	 * @return
	 * @Author huyc
	 * @Description 导出社保派单花名册
	 * @Date 09:05 2023/7/19
	 */
	public List<List<String>> persionHeadOne() {
		List<List<String>> list = new ArrayList<>();
		//表头数据
		String a = "职 工 社 会 保 险 增 加 花 名 册";
		//第一列-第十八列
		for (int i = 1; i <= 18; i++) {
			list.add(Lists.newArrayList(a));
		}
		return list;
	}

	/**
	 * @return
	 * @Author huyc
	 * @Description 导出社保派单花名册
	 * @Date 09:05 2023/7/19
	 */
	public List<List<String>> persionHeadTwo(String houseName, String cusNo) {
		List<List<String>> list = new ArrayList<>();
		//表头数据
		String a = "单位名称(签章): " + houseName + "                        单位编码:" + (null == cusNo ? " " : cusNo);
		//第一列-第十八列
		for (int i = 1; i <= 18; i++) {
			list.add(Lists.newArrayList(a));
		}
		return list;
	}

	/**
	 * @return
	 * @Author huyc
	 * @Description 导出社保派单花名册
	 * @Date 09:05 2023/7/21
	 */
	public List<List<String>> persionHead() {
		List<List<String>> list = new ArrayList<>();
		//表头数据
		String c = "参加保险险种";
		//第一列
		list.add(Lists.newArrayList("序号", "序号"));
		//第二列
		list.add(Lists.newArrayList("姓名", "姓名"));
		//第三列
		list.add(Lists.newArrayList("职工编码", "职工编码"));
		//第四列
		list.add(Lists.newArrayList("社会保障号码   （身份证号码）", "社会保障号码   （身份证号码）"));
		//第五列
		list.add(Lists.newArrayList("性别", "性别"));
		//第六列
		list.add(Lists.newArrayList("民族", "民族"));
		//第七列
		list.add(Lists.newArrayList("参加工作时间", "参加工作时间"));
		//第八列
		list.add(Lists.newArrayList("户口性质", "户口性质"));
		//第九列
		list.add(Lists.newArrayList("个人身份", "个人身份"));
		//参加保险险种
		List<String> orderSpeaces = Lists.newArrayList("养老", "失业", "医疗", "工伤", "生育");
		orderSpeaces.forEach(e -> {
			list.add(Lists.newArrayList(c, e));
		});
		//第十五列
		list.add(Lists.newArrayList("月工资收入(元)", "月工资收入(元)"));
		//第十六列
		list.add(Lists.newArrayList("补缴保险属期", "补缴保险属期"));
		//第十七列
		list.add(Lists.newArrayList("户籍所在地", "户籍所在地"));
		//第十八列
		list.add(Lists.newArrayList("备注", "备注"));
		return list;
	}

	/**
	 * @return
	 * @Author huyc
	 * @Description 导出社保派单花名册
	 * @Date 11:12 2023/7/21
	 */
	public List<List<String>> persionHeadThree() {
		List<List<String>> list = new ArrayList<>();
		//表头数据
		String a = "1、“户口性质”栏按下列类别填写：非农业户口（城镇）、本地非农业户口（本地城镇）、外地非农业户口（外地城镇）、农业户口（农村）、 \n" +
				"          本地农业户口（本地农村）、外地农业户口（外地农村）、港澳台、外籍 \n" +
				"2、“个人身份”栏按下列类别填写：“企业职工”、“国家公务员”、“事业单位在编人员”、“编外人员”、“现役军人”、“协保人员”、“公益性岗位”“农民工医保” \n" +
				"3、“参加保险险种”栏可对应选项打“√” \n" +
				"4、新增人员（含断保再次缴费人员），个人月工资收入按起薪工资额申报。\n" +
				"5、补缴时段超过3个月的，缴费基数为历年安徽省社平工资，同时加征利息(含参保当月的缴费基数)。\n" +
				"6、如属军队退役人员或劳模人员在备注栏中填写，“退役军人类别”栏按下列类别填写：军队转业干部、计划分配的军队转业干部、自主择业的军队转业干部、退伍复员军人、转业复员士官、退伍 \n" +
				"义务兵（含伤病残义务兵）、复员干部，“劳模级别”栏按下列类别填写：国家级、省（自治区、直辖市）级、部（委）级、地（市、厅、局）级、其他 \n" +
				"7、本表需填制一式两份，经办机构审核盖章后方可有效，一份单位自存，一份报市社会保险经办机构。";
		//第一列-第十八列
		for (int i = 1; i <= 18; i++) {
			list.add(Lists.newArrayList(a));
		}
		return list;
	}

	/**
	 * @return
	 * @Author huyc
	 * @Description 导出社保派单花名册
	 * @Date 11:12 2023/7/21
	 */
	public List<List<String>> persionHead1() {
		List<List<String>> list = new ArrayList<>();
		//表头数据
		String b = "填表人：                   单位联系电话：                    社保经办机构审核人：                  " + "填报日期:  " + DateUtil.getYear(DateUtil.getCurrentDateTime()) + " 年" + " " + DateUtil.getMonth(DateUtil.getCurrentDateTime()) + " 月" + " " + DateUtil.getDay(DateUtil.getCurrentDateTime()) + " 日";
		//第一列-第十八列
		for (int i = 1; i <= 18; i++) {
			list.add(Lists.newArrayList(b));
		}
		return list;
	}

	/**
	 * @return
	 * @Author huyc
	 * @Description 导出社保派单花名册
	 * @Date 09:05 2023/7/19
	 */
	public List<List<String>> persionReduceHeadOne() {
		List<List<String>> list = new ArrayList<>();
		//表头数据
		String a = "社 会 保 险 缴 费 单 位 人 员 减 少 花 名 册";
		//第一列-第二十五列
		for (int i = 1; i <= 25; i++) {
			list.add(Lists.newArrayList(a));
		}
		return list;
	}

	/**
	 * @return
	 * @Author huyc
	 * @Description 导出社保派单花名册
	 * @Date 09:05 2023/7/19
	 */
	public List<List<String>> persionReduceHeadTwo(String houseName, String cusNo) {
		List<List<String>> list = new ArrayList<>();
		//表头数据
		String a = "单位名称(签章): " + houseName + "                   单位编码:" + (null == cusNo ? " " : cusNo);
		//第一列-第二十五列cusNo
		for (int i = 1; i <= 25; i++) {
			list.add(Lists.newArrayList(a));
		}
		return list;
	}

	/**
	 * @return
	 * @Author huyc
	 * @Description 导出社保派单花名册
	 * @Date 09:05 2023/7/21
	 */
	public List<List<String>> persionReduceHead() {
		List<List<String>> list = new ArrayList<>();
		//表头数据
		String c = "减少原因";
		//第一列
		list.add(Lists.newArrayList("序号", "序号"));
		//第二列
		list.add(Lists.newArrayList("职工编码", "职工编码"));
		//第三列
		list.add(Lists.newArrayList("姓名", "姓名"));
		//第四列
		list.add(Lists.newArrayList("社会保障号码   （身份证号码）", "社会保障号码   （身份证号码）"));
		//第五列
		list.add(Lists.newArrayList("停止缴费时间", "停止缴费时间"));
		//减少原因
		List<String> orderSpeaces = Lists.newArrayList("辞职", "辞退", "参军", "上学", "劳改", "劳教", "除名", "人员失踪", "停薪留职", "合同期满", "解除合同", "统筹内调出", "调出统筹范围", "退休", "死亡", "出国定居", "港澳台定居", "退保", "其他");
		orderSpeaces.forEach(e -> {
			list.add(Lists.newArrayList(c, e));
		});
		list.add(Lists.newArrayList("备注", "备注"));
		return list;
	}

	/**
	 * @return
	 * @Author huyc
	 * @Description 导出社保派单花名册
	 * @Date 11:12 2023/7/21
	 */
	public List<List<String>> persionReduceHead1() {
		List<List<String>> list = new ArrayList<>();
		//表头数据
		String a = "说明1、“减少原因”栏可对应选项打“√”； \n" +
				"2、本表需填制一式二份，经办机构审核盖章后方可有效，一份单位自存，一份报市社会保险经办机构。";
		String b = "填表人：              单位联系电话：                社保经办机构审核人：             " + "填报日期:  " + DateUtil.getYear(DateUtil.getCurrentDateTime()) + " 年" + " " + DateUtil.getMonth(DateUtil.getCurrentDateTime()) + " 月" + " " + DateUtil.getDay(DateUtil.getCurrentDateTime()) + " 日";
		//第一列-第十八列
		for (int i = 1; i <= 25; i++) {
			list.add(Lists.newArrayList(a, b));
		}
		return list;
	}

	/**
	 * @return
	 * @Author wzb
	 * @Description 导出公积金派单花名册
	 * @Date 09:05 2023/7/19
	 */
	public List<List<String>> headSecond(String houseName, SocialHandleSearchVo searchVo) {
		List<List<String>> list = new ArrayList<>();
		//表头数据
		String a = null;
		if (houseName != null) {
			HouseNameConfigVo vo = dispatchInfoMapper.getFundSumExportVoTwo(houseName, searchVo);
			if (vo == null) {
				a = "单位名称(签章): " + houseName + "                       单位公积金账号:" + "" + "                    单边缴纳比例:" + "";
			} else {

				if (vo.getCustomerNo() == null) {
					vo.setCustomerNo("");
				}
				if (vo.getCompanyPro() == null) {
					vo.setCompanyPro("");
				}
				a = "单位名称(签章): " + houseName + "         单位公积金账号:" + vo.getCustomerNo() + "      单边缴纳比例:" + vo.getCompanyPro() + "%";
			}
			//第一列-第十八列
			for (int i = 1; i <= 8; i++) {
				list.add(Lists.newArrayList(a));
			}
		}
		return list;
	}


}
