/*
 *    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.salary.service.impl;

import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.monitorjbl.xlsx.StreamingReader;
import com.yifu.cloud.plus.v1.check.entity.TCheckIdCard;
import com.yifu.cloud.plus.v1.yifu.common.core.constant.CommonConstants;
import com.yifu.cloud.plus.v1.yifu.common.core.constant.ResultConstants;
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.R;
import com.yifu.cloud.plus.v1.yifu.salary.entity.TSalaryStandardOriginal;
import com.yifu.cloud.plus.v1.yifu.salary.entity.TSalaryStandardOriginalDetail;
import com.yifu.cloud.plus.v1.yifu.salary.mapper.SysConfigLimitMapper;
import com.yifu.cloud.plus.v1.yifu.salary.mapper.TSalaryStandardOriginalMapper;
import com.yifu.cloud.plus.v1.yifu.salary.service.TSalaryStandardOriginalDetailService;
import com.yifu.cloud.plus.v1.yifu.salary.service.TSalaryStandardOriginalService;
import com.yifu.cloud.plus.v1.yifu.salary.util.SalaryConstants;
import com.yifu.cloud.plus.v1.yifu.salary.vo.*;
import lombok.AllArgsConstructor;
import lombok.extern.log4j.Log4j2;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 薪资原表识别
 *
 * @author hgw
 * @date 2024-4-2 17:20:46
 */
@Log4j2
@Service
@AllArgsConstructor
public class TSalaryStandardOriginalServiceImpl extends ServiceImpl<TSalaryStandardOriginalMapper, TSalaryStandardOriginal>
		implements TSalaryStandardOriginalService {

	private final TSalaryStandardOriginalDetailService tSalaryStandardOriginalDetailService;

	private final AtomicInteger importAtomicInteger = new AtomicInteger(0);

	private final SysConfigLimitMapper configLimitMapper;

	/**
	 * @Description: 分页查询
	 * @Author: hgw
	 * @Date: 2024/4/2 17:19
	 * @return:
	 **/
	@Override
	public IPage<TSalaryStandardOriginal> getTSalaryStandardOriginalPage(Page<TSalaryStandardOriginal> page, TSalaryStandardOriginal tSalaryStandardOriginal) {
		return baseMapper.getTSalaryStandardOriginalPage(page, tSalaryStandardOriginal);
	}

	/**
	 * @Description: 导出
	 * @Author: hgw
	 * @Date: 2023/1/18 10:31
	 * @return: java.util.List<com.yifu.cloud.plus.v1.yifu.salary.vo.TSalaryStandardIssueExportVo>
	 **/
	private List<TSalaryOriginalExportVo> exportList(TSalaryOriginalSearchVo searchVo) {
		List<String> idList = Common.getList(searchVo.getIds());
		return baseMapper.getTSalaryOriginalExportVoList(searchVo, idList);
	}

	private int exportListCount(TSalaryOriginalSearchVo searchVo) {
		List<String> idList = Common.getList(searchVo.getIds());
		return baseMapper.exportListCount(searchVo, idList);
	}

	/**
	 * @Description: 分页查询
	 * @Author: hgw
	 * @Date: 2024/4/2 17:19
	 * @return:
	 **/
	@Override
	public List<TSalaryStandardOriginal> getTSalaryStandardOriginalList(TSalaryStandardOriginal tSalaryStandardOriginal) {
		return baseMapper.getTSalaryStandardOriginalList(tSalaryStandardOriginal);
	}

	@Override
	public List<String> getSpecialIdCardList() {
		List<String> specialIdCardList = baseMapper.getSpecialIdCardList();
		return specialIdCardList;
	}

	/**
	 * @param file
	 * @Description: 核心解析
	 * @Author: hgw
	 * @Date: 2024/4/8 17:05
	 * @return: com.yifu.cloud.plus.v1.yifu.common.core.util.R<com.yifu.cloud.plus.v1.yifu.salary.vo.ExcelVo>
	 **/
	@Override
	public R<ExcelSheetVo> getExcelHeader(MultipartFile file, String sheetName) {
		// 特殊身份证允许识别为数据行
		List<String> specialIdCardList = baseMapper.getSpecialIdCardList();
		try {
			//对前端传递的文件进行校验
			if (file == null || file.isEmpty()) {
				return R.failed("文件为空，重新上传");
			}
			//对前端传递的文件进行校验
			if (Common.isEmpty(sheetName)) {
				return R.failed("sheet名为空，请选择！");
			}
			//获取文件名称 判断文件是否为 Execl
			String filename = file.getOriginalFilename();
			if (filename == null || Common.isEmpty(filename)) {
				return R.failed("文件名称为空，请重新上传！");
			}
			if (!(filename.endsWith(".xls") || filename.endsWith(".xlsx"))) {
				return R.failed("文件名称格式有误，请重新上传！" + filename);
			}
			R<ExcelSheetVo> voR;
			ExcelSheetVo vo = null;
			InputStream inputStream = file.getInputStream();
			//根据文件格式 对应不同的api解析
			if (filename.endsWith(".xlsx")) {
				voR = readXlsx(inputStream, specialIdCardList, true, sheetName, -1, null);
			} else {
				voR = readXls(inputStream, specialIdCardList, true, sheetName, -1, null);
			}
			if (voR != null && voR.getCode() == CommonConstants.SUCCESS) {
				vo = voR.getData();
			} else {
				return R.failed(voR == null ? "识别表格异常！" : voR.getMsg());
			}
			//数据保存
			if (Common.isNotNull(vo.getErrorInfo())) {
				return R.failed(vo.getErrorInfo());
			}
			return R.ok(vo);
		} catch (IOException e) {
			e.printStackTrace();
			log.error("解析表头失败！" + e.getMessage());
		}
		return R.failed("解析表头失败");
	}

	/**
	 * @param maxColumn         表格表头长度 + 10 （有可能含有未配置的新员工之类的系统表头，放宽10列）
	 * @param inputStream
	 * @param specialIdCardList
	 * @param titleFlag         true:返回title;false:返回content
	 * @Description: 解析xls
	 * @Author: hgw
	 * @Date: 2024/4/8 17:02
	 * @return: com.yifu.cloud.plus.v1.yifu.salary.vo.ExcelVo
	 **/
	private R<ExcelSheetVo> readXls(InputStream inputStream, List<String> specialIdCardList, boolean titleFlag
			, String sheetName, Integer idCardNum, Integer maxColumn) throws IOException {
		HSSFWorkbook sheets = null;
		ExcelSheetVo sheetVo = new ExcelSheetVo();
		try {
			List<ExcelColumnVo> titleList;
			List<ExcelColumnVo> columnList;
			ExcelColumnVo columnVo;
			// 内容list
			List<Map<String, String>> contentList;
			// 每一行的内容map
			Map<String, String> contentMap;
			// 存储每一列有值的表头
			Map<String, String> titleMap = new HashMap<>();
			sheets = new HSSFWorkbook(inputStream);
			// sheet
			HSSFSheet sheetAt;
			HSSFRow row;
			// 获取sheet数量
			int sheetNum = sheets.getNumberOfSheets();
			// 每个单元格内容
			String cellValue;
			// 内容行
			int dataRow;
			// 身份证所在列
			int idCardColumn = -1;
			if (Common.isNotNull(idCardNum)) {
				idCardColumn = idCardNum;
			}
			// 空白列起始号
			int nullColumn = -1;
			// 上一个空白行
			int preNullColumn = -1;
			// 空白列数（一旦数量达到 maxNullColumNum，将停止循环列，并删除title list与content）
			int nullColumnNum = 0;
			// 极限空白列，超过阈值，将不继续循环列
			int maxNullColumNum = 30;
			int rowLastCellNum = -1;
			if (Common.isNotNull(maxColumn)) {
				maxNullColumNum = maxColumn;
				rowLastCellNum = maxColumn;
			}
			String idCardValue = null;
			// 数据行是否被确认，true否
			boolean dataRowFlag;
			// 超过极限空白列maxNullColumNum，只循环表头 true:未超越
			boolean nullFlag = true;
			// 极限空白表头列，超过阈值，将不继续循环列
			int maxTitleNullColumNum = 50;
			HSSFCell cell;
			// 极限空白身份证，超过将不循环行
			int maxIdCardNullRow = 50;
			int idCardNullNum = 0;
			// 循环每个sheet
			for (int i = 0; i < sheetNum; i++) {
				sheetAt = sheets.getSheetAt(i);
				if (Common.isNotNull(sheetAt.getSheetName()) && Common.isNotNull(sheetName)
						&& sheetName.equals(sheetAt.getSheetName())) {
					contentList = new ArrayList<>();
					// 塞sheet名称
					sheetVo.setSheetName(sheetAt.getSheetName());
					titleList = new ArrayList<>();
					// 初始化每个sheet的数据行
					dataRow = 0;
					dataRowFlag = true;
					idCardNullNum = 0;
					for (int rowNum = 0; rowNum <= sheetAt.getLastRowNum(); rowNum++) {

						nullColumn = -1;
						preNullColumn = -1;
						nullColumnNum = 0;
						nullFlag = true;

						idCardValue = null;
						contentMap = new HashMap<>();
						columnList = new ArrayList<>();
						row = sheetAt.getRow(rowNum);
						if (row != null) {
							// 循环当前sheet的每行内容
							if (Common.isNotNull(maxColumn)) {
								rowLastCellNum = maxColumn;
							} else {
								rowLastCellNum = row.getLastCellNum();
							}
							for (int cellNum = 0; cellNum < rowLastCellNum; cellNum++) {
								columnVo = new ExcelColumnVo();
								try {
									cell = row.getCell(cellNum);
									if (cell == null) {
										cellValue = "";
									} else {
										if (cell.getCellType() == CellType.FORMULA) {
											cell.setCellType(CellType.STRING);
											cellValue = cell.getStringCellValue();
										} else if (CellType.STRING == cell.getCellType()) {
											cellValue = row.getCell(cellNum).getStringCellValue();
										} else if (CellType.NUMERIC == cell.getCellType()) {
											try {
												cellValue = String.valueOf(row.getCell(cellNum).getNumericCellValue());
												if (cellValue.contains("E")) {
													cellValue = String.valueOf(new DecimalFormat("#").format(row.getCell(cellNum).getNumericCellValue()));
												}
											} catch (NumberFormatException e) {
												cell.setCellType(CellType.STRING);
												cellValue = String.valueOf(row.getCell(cellNum).getStringCellValue());
											}
										} else if (HSSFDateUtil.isCellDateFormatted(cell)) {
											cellValue = String.valueOf(row.getCell(cellNum).getDateCellValue());
										} else if (CellType.BOOLEAN == cell.getCellType()) {
											cellValue = String.valueOf(row.getCell(cellNum).getBooleanCellValue());
										} else {
											cellValue = row.getCell(cellNum).getStringCellValue();
										}
									}
								} catch (Exception e) {
									cellValue = "";
									//sheetVo.setErrorInfo("请注意单元格格式，第" + (i + 1) + "个sheet，第" + (rowNum + 1) + "行第" + (cellNum + 1) + "列。" + e.getMessage())
									//return sheetVo
								}
								columnVo.setLineNum(cellNum);
								if (Common.isNotNull(cellValue)) {
									cellValue = cellValue.trim().replaceAll("\\s+", "");
									titleMap.put(String.valueOf(cellNum), cellValue);
								}
								columnVo.setContent(cellValue);
								if (dataRowFlag) {
									if (idCardColumn != -1) {
										if (idCardColumn == cellNum && Common.isNotNull(cellValue) &&
												(((cellValue.length() == 15 || cellValue.length() == 18) && regIdCard(cellValue))
														// 特殊身份证允许识别
														|| regSpecialIdCard(cellValue, specialIdCardList))) {
											dataRow = rowNum;
											dataRowFlag = false;
										}
									} else {
										if (Common.isNotNull(cellValue) &&
												(((cellValue.length() == 15 || cellValue.length() == 18) && regIdCard(cellValue))
														// 特殊身份证允许识别
														|| regSpecialIdCard(cellValue, specialIdCardList))) {
											dataRow = rowNum;
											idCardColumn = cellNum;
											dataRowFlag = false;
										}
									}
								}
								if (nullFlag) {
									contentMap.put(String.valueOf(cellNum), cellValue);
									if (dataRowFlag) {
										if (Common.isEmpty(cellValue)) {
											if (titleMap.get(String.valueOf(cellNum)) != null) {
												columnVo.setContent(titleMap.get(String.valueOf(cellNum)));
											} else {
												if (preNullColumn == -1 || preNullColumn + 1 == cellNum) {
													if (nullColumn == -1) {
														nullColumn = cellNum;
													}
													preNullColumn = cellNum;
													nullColumnNum++;
												}
												columnVo.setContent("空白列" + cellNum);
											}
										} else {
											nullColumn = -1;
											preNullColumn = -1;
											nullColumnNum = 0;
										}
										columnList.add(columnVo);
									} else {
										if (Common.isEmpty(cellValue)) {
											if (preNullColumn == -1 || preNullColumn + 1 == cellNum) {
												if (nullColumn == -1) {
													nullColumn = cellNum;
												}
												preNullColumn = cellNum;
												nullColumnNum++;
											}
										} else {
											nullColumn = -1;
											preNullColumn = -1;
											nullColumnNum = 0;
										}
									}
									if (nullColumnNum >= maxNullColumNum) {
										for (int num = maxNullColumNum - 1; num >= 0; num--) {
											if (dataRowFlag) {
												columnList.remove(nullColumn + num);
											}
											contentMap.remove(String.valueOf(nullColumn + num));
										}
										nullFlag = false;
									}
									if (idCardColumn != -1 && cellNum == idCardColumn) {
										idCardValue = cellValue;
									}
								} else {
									if (Common.isEmpty(cellValue)) {
										if (preNullColumn == -1 || preNullColumn + 1 == cellNum) {
											if (nullColumn == -1) {
												nullColumn = cellNum;
											}
											preNullColumn = cellNum;
											nullColumnNum++;
										}
									} else {
										nullColumn = -1;
										preNullColumn = -1;
										nullColumnNum = 0;
									}
									// 表头Map超过50，跳出当前行的列循环
									if (nullColumnNum >= maxTitleNullColumNum) {
										break;
									}
								}
							}
							if (!dataRowFlag) {
								if (idCardColumn != -1) {
									if (Common.isEmpty(idCardValue)) {
										idCardNullNum++;
									}
									if (Common.isNotNull(idCardValue) && (
											regIdCard(idCardValue)
											// 特殊身份证允许识别
											|| regSpecialIdCard(idCardValue, specialIdCardList)
											|| regIdCardError(idCardValue) )) {
										contentList.add(contentMap);
									}
								}
							} else {
								titleList = new ArrayList<>(columnList);
							}
						}
						if (rowNum >= 50 && dataRowFlag) {
							return R.failed("请检查模板与配置是否一致！未找到身份证所在的数据行，请检查表数据！");
						}
						if (titleFlag && !dataRowFlag) {
							break;
						}
						if (idCardNullNum >= maxIdCardNullRow) {
							break;
						}
					}
					if (dataRowFlag) {
						if (idCardNum != null && idCardNum != -1) {
							return R.failed("您配置的身份证列第【"+(idCardNum+1)+"】列未找到身份证，请确认表头配置或表格数据！！");
						}
						return R.failed("未找到身份证所在的数据行，请检查表数据！！");
					}
					sheetVo.setDataNum(dataRow);
					sheetVo.setTitleList(titleList);
					sheetVo.setContentList(contentList);
					return R.ok(sheetVo);
				}
			}
		} catch (IOException e) {
			e.printStackTrace();
			if (sheets != null) {
				sheets.close();
			}
		} finally {
			if (sheets != null) {
				sheets.close();
			}
		}
		return R.ok(sheetVo);
	}

	/**
	 * @param maxColumn         表格表头长度 + 10 （有可能含有未配置的新员工之类的系统表头，放宽10列）
	 * @param inputStream
	 * @param specialIdCardList
	 * @param titleFlag         true:返回title;false:返回content
	 * @Description: 解析xlsx
	 * @Author: hgw
	 * @Date: 2024/4/8 17:02
	 * @return: com.yifu.cloud.plus.v1.yifu.salary.vo.ExcelVo
	 **/
	private R<ExcelSheetVo> readXlsx(InputStream inputStream, List<String> specialIdCardList, boolean titleFlag
			, String sheetName, Integer idCardNum, Integer maxColumn) throws IOException {
		ExcelSheetVo sheetVo = new ExcelSheetVo();
		Workbook sheets = null;
		try {
			List<ExcelColumnVo> titleList;
			List<ExcelColumnVo> columnList;
			ExcelColumnVo columnVo;
			// 内容list
			List<Map<String, String>> contentList;
			// 每一行的内容map
			Map<String, String> contentMap;
			// 存储每一列有值的表头
			Map<String, String> titleMap = new HashMap<>();
			//sheets = new SXSSFWorkbook(inputStream);

			sheets = StreamingReader.builder()
					.rowCacheSize(1000)    // 缓存到内存中的行数(默认是10)
					.bufferSize(10240)     // 读取资源时，缓存到内存的字节大小(默认是1024)
					.open(inputStream);
			// sheet
			//XSSFSheet sheetAt;
			//XSSFRow row;
			// 获取sheet数量
			int sheetNum = sheets.getNumberOfSheets();
			// 每个单元格内容
			String cellValue;
			// 内容行
			int dataRow;
			// 身份证所在列
			int idCardColumn = -1;
			if (Common.isNotNull(idCardNum)) {
				idCardColumn = idCardNum;
			}
			// 空白列起始号
			int nullColumn = -1;
			// 上一个空白行
			int preNullColumn = -1;
			// 空白列数（一旦数量达到 maxNullColumNum，将停止循环列，并删除title list与content）
			int nullColumnNum = 0;
			// 极限空白列，超过阈值，将不继续循环列
			int maxNullColumNum = 30;
			int rowLastCellNum = -1;
			if (Common.isNotNull(maxColumn)) {
				maxNullColumNum = maxColumn;
			}
			String idCardValue = null;
			// 数据行是否被确认，true否
			boolean dataRowFlag;
			// 超过极限空白列maxNullColumNum，只循环表头 true:未超越
			boolean nullFlag = true;
			// 极限空白表头列，超过阈值，将不继续循环列
			int maxTitleNullColumNum = 50;
			// 极限空白身份证，超过将不循环行
			int maxIdCardNullRow = 50;
			int idCardNullNum = 0;
			Cell cell;
			// 循环每个sheet
			Sheet sheetAt;
			for (int i = 0; i < sheetNum; i++) {
				//sheetAt = sheets.getSheetAt(i);
				sheetAt = sheets.getSheetAt(i);
				if (Common.isNotNull(sheetAt.getSheetName()) && Common.isNotNull(sheetName)
						&& sheetName.equals(sheetAt.getSheetName())) {
					contentList = new ArrayList<>();
					// 塞sheet名称
					sheetVo.setSheetName(sheetAt.getSheetName());
					titleList = new ArrayList<>();
					// 初始化每个sheet的数据行
					dataRow = 0;
					dataRowFlag = true;
					// 循环当前sheet的每行内容
					//for (int rowNum = 0; rowNum <= sheetAt.getLastRowNum(); rowNum++) {

					idCardNullNum = 0;

					int rowNum = 0;
					for (Row row : sheetAt) {

						nullColumn = -1;
						preNullColumn = -1;
						nullColumnNum = 0;
						nullFlag = true;

						idCardValue = null;
						contentMap = new HashMap<>();
						columnList = new ArrayList<>();
						if (row != null) {
							if (Common.isNotNull(maxColumn)) {
								rowLastCellNum = maxColumn;
							} else {
								rowLastCellNum = row.getLastCellNum();
							}
							for (int cellNum = 0; cellNum < rowLastCellNum; cellNum++) {
								columnVo = new ExcelColumnVo();
								try {
									cell = row.getCell(cellNum);
									if (cell == null) {
										cellValue = "";
									} else {
										if (cell.getCellType() == CellType.FORMULA) {
											cellValue = String.valueOf(cell.getStringCellValue());
										} else if (CellType.STRING == cell.getCellType()) {
											cellValue = row.getCell(cellNum).getStringCellValue();
										} else if (CellType.NUMERIC == cell.getCellType()) {
											try {
												cellValue = String.valueOf(row.getCell(cellNum).getNumericCellValue());
												if (cellValue.contains("E")) {
													cellValue = String.valueOf(new DecimalFormat("#").format(row.getCell(cellNum).getNumericCellValue()));
												}
											} catch (NumberFormatException e) {
												cell.setCellType(CellType.STRING);
												cellValue = String.valueOf(row.getCell(cellNum).getStringCellValue());
											}
										} else if (HSSFDateUtil.isCellDateFormatted(cell)) {
											cellValue = String.valueOf(row.getCell(cellNum).getDateCellValue());
										} else if (CellType.BOOLEAN == cell.getCellType()) {
											cellValue = String.valueOf(row.getCell(cellNum).getBooleanCellValue());
										} else {
											cellValue = row.getCell(cellNum).getStringCellValue();
										}
									}
								} catch (Exception e) {
									cellValue = "";
									//sheetVo.setErrorInfo("请注意单元格格式，第" + (i + 1) + "个sheet，第" + (rowNum + 1) + "行第" + (cellNum + 1) + "列。" + e.getMessage())
									//return sheetVo
								}
								columnVo.setLineNum(cellNum);
								if (Common.isNotNull(cellValue)) {
									cellValue = cellValue.trim().replaceAll("\\s+", "");
									titleMap.put(String.valueOf(cellNum), cellValue);
								}
								columnVo.setContent(cellValue);
								if (dataRowFlag) {
									if (idCardColumn != -1) {
										if (idCardColumn == cellNum && Common.isNotNull(cellValue) &&
												(((cellValue.length() == 15 || cellValue.length() == 18) && regIdCard(cellValue))
														// 特殊身份证允许识别
														|| regSpecialIdCard(cellValue, specialIdCardList))) {
											dataRow = rowNum;
											dataRowFlag = false;
										}
									} else {
										if (Common.isNotNull(cellValue) &&
												(((cellValue.length() == 15 || cellValue.length() == 18) && regIdCard(cellValue))
														// 特殊身份证允许识别
														|| regSpecialIdCard(cellValue, specialIdCardList))) {
											dataRow = rowNum;
											idCardColumn = cellNum;
											dataRowFlag = false;
										}
									}
								}
								if (nullFlag) {
									contentMap.put(String.valueOf(cellNum), cellValue);
									if (dataRowFlag) {
										if (Common.isEmpty(cellValue)) {
											if (titleMap.get(String.valueOf(cellNum)) != null) {
												columnVo.setContent(titleMap.get(String.valueOf(cellNum)));
											} else {
												if (preNullColumn == -1 || preNullColumn + 1 == cellNum) {
													if (nullColumn == -1) {
														nullColumn = cellNum;
													}
													preNullColumn = cellNum;
													nullColumnNum++;
												}
												columnVo.setContent("空白列" + cellNum);
											}
										} else {
											nullColumn = -1;
											preNullColumn = -1;
											nullColumnNum = 0;
										}
										columnList.add(columnVo);
									} else {
										if (Common.isEmpty(cellValue)) {
											if (preNullColumn == -1 || preNullColumn + 1 == cellNum) {
												if (nullColumn == -1) {
													nullColumn = cellNum;
												}
												preNullColumn = cellNum;
												nullColumnNum++;
											}
										} else {
											nullColumn = -1;
											preNullColumn = -1;
											nullColumnNum = 0;
										}
									}
									if (nullColumnNum >= maxNullColumNum) {
										for (int num = maxNullColumNum - 1; num >= 0; num--) {
											if (dataRowFlag) {
												columnList.remove(nullColumn + num);
											}
											contentMap.remove(String.valueOf(nullColumn + num));
										}
										nullFlag = false;
									}
									if (idCardColumn != -1 && cellNum == idCardColumn) {
										idCardValue = cellValue;
									}
								} else {
									if (Common.isEmpty(cellValue)) {
										if (preNullColumn == -1 || preNullColumn + 1 == cellNum) {
											if (nullColumn == -1) {
												nullColumn = cellNum;
											}
											preNullColumn = cellNum;
											nullColumnNum++;
										}
									} else {
										nullColumn = -1;
										preNullColumn = -1;
										nullColumnNum = 0;
									}
									// 表头Map超过50，跳出当前行的列循环
									if (nullColumnNum >= maxTitleNullColumNum) {
										break;
									}
								}
							}
							if (!dataRowFlag) {
								if (idCardColumn != -1) {
									if (Common.isEmpty(idCardValue)) {
										idCardNullNum++;
									}
									if (Common.isNotNull(idCardValue) && (
											regIdCard(idCardValue)
													// 特殊身份证允许识别
													|| regSpecialIdCard(idCardValue, specialIdCardList)
													|| regIdCardError(idCardValue) )) {
										contentList.add(contentMap);
									}
								}
							} else {
								titleList = new ArrayList<>(columnList);
							}
						}
						if (rowNum >= 50 && dataRowFlag) {
							return R.failed("未找到身份证所在的数据行，请检查表数据！");
						}
						rowNum++;
						if (titleFlag && !dataRowFlag) {
							break;
						}
						if (idCardNullNum >= maxIdCardNullRow) {
							break;
						}
					}
					if (dataRowFlag) {
						if (idCardNum != null && idCardNum != -1) {
							return R.failed("您配置的身份证列第【"+(idCardNum+1)+"】列未找到身份证，请确认表头配置或表格数据！！");
						}
						return R.failed("未找到身份证所在的数据行，请检查表数据！！");
					}
					sheetVo.setDataNum(dataRow);
					sheetVo.setTitleList(titleList);
					sheetVo.setContentList(contentList);
					return R.ok(sheetVo);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			if (sheets != null) {
				sheets.close();
			}
		} finally {
			if (sheets != null) {
				sheets.close();
			}
		}
		return R.ok(sheetVo);
	}

	/**
	 * @param content
	 * @Description: 判断特殊身份证
	 * 格式正确返回true
	 * @Author: hgw
	 * @Date: 2024-4-8 15:33:04
	 * @return: boolean
	 **/
	private static boolean regSpecialIdCard(String content, List<String> specialIdCardList) {
		if (specialIdCardList != null && !specialIdCardList.isEmpty()) {
			for (int i = 0; i < specialIdCardList.size(); i++) {
				if (specialIdCardList.get(i).equals(content)) {
					return true;
				}
			}
		}
		return false;
	}

	/**
	 * @param content
	 * @Description: 判断身份证格式：18位数字，17位数字+X，15位数字，14位数字+X
	 * 格式正确返回true
	 * @Author: hgw
	 * @Date: 2022/5/13 11:53
	 * @return: boolean
	 **/
	private static boolean regIdCard(String content) {
		boolean flag = false;
		Pattern p = Pattern.compile("\\d{18}|\\d{17}X|\\d{15}|\\d{14}X");
		Matcher m = p.matcher(content);
		if (m.matches()) {
			flag = true;
		}
		return flag;
	}

	/**
	 * @param content
	 * @Description: 判断类似身份证的数据，进识别行，提示客服该行错误：14位数字
	 * 格式正确返回true
	 * @Author: hgw
	 * @Date: 2022/5/13 11:53
	 * @return: boolean
	 **/
	private static boolean regIdCardError(String content) {
		return content.matches("^.*\\d{14}.*$");
	}

	/**
	 * @param excelVo
	 * @Description: 新版原表识别导入
	 * @Author: hgw
	 * @Date: 2024/4/8 18:22
	 * @return: com.yifu.cloud.plus.v1.yifu.common.core.util.R<java.util.List < com.yifu.cloud.plus.v1.yifu.common.core.util.ErrorMessage>>
	 **/
	@Override
	public R<SalaryUploadParamVo> salaryUploadByOriginal(MultipartFile file, SalaryUploadOriginalParamVo excelVo) {
		Integer maxLimit = configLimitMapper.getSysConfigLimitByKey("IMPORT_SALARY_ONE_LIMIT");
		if (Common.isEmpty(maxLimit)) {
			maxLimit = 10;
		}
		if (importAtomicInteger.incrementAndGet() <= maxLimit){
			try {
				return this.getExcelContent(file, excelVo);
			} catch (Exception e){
				log.error("薪资原表识别异常：",e);
				return R.failed("薪资原表识别异常，请检查表数据");
			} finally {
				importAtomicInteger.decrementAndGet();
			}
		} else {
			// 前面做了+1（atomicInteger.incrementAndGet()）这里也要-1
			importAtomicInteger.decrementAndGet();
			log.error("超出阈值："+ ResultConstants.IMPORT_DOWN_LOAD_DATA);
			return R.failed("薪资原表识别，" + ResultConstants.IMPORT_DOWN_LOAD_DATA);
		}
	}

	/**
	 * @param excelVo
	 * @Description: 核心解析-内容
	 * @Author: hgw
	 * @Date: 2024-4-8 18:20:22
	 * @return: com.yifu.cloud.plus.v1.yifu.common.core.util.R<com.yifu.cloud.plus.v1.yifu.salary.vo.ExcelVo>
	 **/
	private R<SalaryUploadParamVo> getExcelContent(MultipartFile file, SalaryUploadOriginalParamVo excelVo) {
		try {
			String sheetName = excelVo.getSheetName();
			// 配置
			String originalId = excelVo.getOriginalId();

			// 特殊身份证允许识别为数据行
			List<String> specialIdCardList = baseMapper.getSpecialIdCardList();
			//对前端传递的文件进行校验
			if (Common.isEmpty(originalId)) {
				return R.failed("识别配置为空，请选择识别配置！");
			}
			TSalaryStandardOriginal original = this.getById(originalId);
			if (original == null || Common.isEmpty(original.getId())) {
				return R.failed("请检查表头配置！");
			}
			List<TSalaryStandardOriginalDetail> detailList = tSalaryStandardOriginalDetailService.getTSalaryStandardOriginalDetailByOrgId(originalId);
			Map<String, TSalaryStandardOriginalDetail> detailMap = new HashMap<>();
			Integer idCardNum = -1;
			if (detailList == null || detailList.isEmpty() || detailList.size() < 3) {
				return R.failed("请检查表头配置！！");
			}
			// 忽略配置的表头，额外的表头
			Map<String, Integer> ignoreMap = new HashMap<>();
			ignoreMap.put("是否新员工（默认否）", 1);
			ignoreMap.put("开户行总行", 1);
			ignoreMap.put("开户行省", 1);
			ignoreMap.put("开户行市", 1);
			ignoreMap.put("开户行支行", 1);
			ignoreMap.put("银行卡号", 1);
			ignoreMap.put("手机号码", 1);
			ignoreMap.put("计税月份", 1);
			ignoreMap.put("工资发放时间（立即发、暂停发）", 1);

			Integer maxColumn = detailList.size() + 10;

			// 是否固定列，true是，原逻辑，false否，忽略没配置的表头
			boolean isFixedColumn = true;
			if (Common.isNotNull(original.getIsFixedColumn()) && CommonConstants.ZERO_INT == original.getIsFixedColumn()) {
				isFixedColumn = false;
				maxColumn = null;
			}
			// 不固定表头配置
			Map<String, TSalaryStandardOriginalDetail> notFixedTitleMap = new HashMap<>();
			// 用来检测 不固定表头配置的表头，有没有都在
			Map<String, Integer> notFixedKeyMap = new HashMap<>();
			// 判断是否已重复存在
			Map<String, Integer> curMap = new HashMap<>();

			for (TSalaryStandardOriginalDetail detail : detailList) {
				if (Common.isEmpty(detail.getModelName())
						&& (ignoreMap.get(detail.getExcelName()) != null)) {
					detail.setModelName(detail.getExcelName());
				} else if (SalaryConstants.ID_NUMBER.equals(detail.getModelName())) {
					idCardNum = detail.getColumnNo();
					if (!isFixedColumn) {
						idCardNum = null;
					}
				}
				if (isFixedColumn) {
					detailMap.put(String.valueOf(detail.getColumnNo()), detail);
				} else {
					if (Common.isNotNull(detail.getModelName()) || ignoreMap.get(detail.getExcelName()) != null) {
						if (Common.isNotNull(detail.getExcelKey())) {
							notFixedTitleMap.put(detail.getExcelKey(), detail);
							notFixedKeyMap.put(detail.getExcelKey(), CommonConstants.ONE_INT);
						} else {
							notFixedTitleMap.put(detail.getExcelName(), detail);
							notFixedKeyMap.put(detail.getExcelName(), CommonConstants.ONE_INT);
						}
					}
				}
			}
			//对前端传递的文件进行校验
			if (Common.isEmpty(sheetName)) {
				return R.failed("sheet名为空，请选择！");
			}
			//对前端传递的文件进行校验
			if (file == null || file.isEmpty()) {
				return R.failed("文件为空，重新上传");
			}
			//获取文件名称 判断文件是否为 Execl
			String filename = file.getOriginalFilename();
			if (filename == null || Common.isEmpty(filename)) {
				return R.failed("文件名称为空，请重新上传！");
			}
			if (!(filename.endsWith(".xls") || filename.endsWith(".xlsx"))) {
				return R.failed("文件名称格式有误，请重新上传！" + filename);
			}
			String jsonString = null;
			InputStream inputStream = file.getInputStream();
			R<ExcelSheetVo> voR;
			ExcelSheetVo vo;
			//根据文件格式 对应不同的api解析
			if (filename.endsWith(".xlsx")) {
				voR = readXlsx(inputStream, specialIdCardList, false, sheetName, idCardNum, maxColumn);
			} else {
				voR = readXls(inputStream, specialIdCardList, false, sheetName, idCardNum, maxColumn);
			}
			if (voR != null && voR.getCode() == CommonConstants.SUCCESS) {
				vo = voR.getData();
			} else {
				return R.failed(voR == null ? "识别表格异常！" : voR.getMsg());
			}

			// 验证表头：
			if (vo.getTitleList() != null && !vo.getTitleList().isEmpty()) {
				ExcelColumnVo title;
				TSalaryStandardOriginalDetail detail;
				// 标题行的内容
				String titleStr;
				TSalaryStandardOriginalDetail detailSet;
				// 匹配的表头关键字
				String key;
				// 匹配规则
				String rule;
				boolean isFixError;
				boolean isError;
				boolean isOther;
				// 重复存在的表头值
				Integer curValue;
				StringBuilder repetitiveKey = new StringBuilder();
				for (int i = 0; i < vo.getTitleList().size(); i++) {
					rule = "等于";
					title = vo.getTitleList().get(i);
					titleStr = title.getContent();
					isOther = ignoreMap.get(titleStr) == null;
					if (Common.isNotNull(titleStr)) {
						// 固定表头
						if (isFixedColumn) {
							detailSet = detailMap.get(String.valueOf(title.getLineNum()));
							if (detailSet == null) {
								if (ignoreMap.get(titleStr) == null && title.getLineNum() < detailList.size()) {

									return R.failed("未找到对应配置的表头，请核实导入的原表信息后，重新尝试！");
									//return R.failed("表头与导入的不一致,第" + title.getLineNum() + "列表头【" + title.getContent() + "】未找到配置，请先配置表头！");
								} else if (ignoreMap.get(titleStr) != null) {
									detail = new TSalaryStandardOriginalDetail();
									detail.setModelName(titleStr);
									detailMap.put(String.valueOf(title.getLineNum()), detail);
								}
							} else {
								if (Common.isNotNull(detailSet.getExcelKey())) {
									key = detailSet.getExcelKey();
								} else {
									key = detailSet.getExcelName();
								}
								if (Common.isNotNull(detailSet.getExcelRule())) {
									rule = detailSet.getExcelRule();
								}
								if (Common.isEmpty(rule)) {
									rule = "等于";
								}
								isError = this.judgeRule(titleStr, key, rule);
								if (isError && isOther) {
									return R.failed("未找到对应配置的表头，请核实导入的原表信息后，重新尝试！！");
								}
								//return R.failed("表头与导入的不一致,第" + (i + 1) + "列应为【" + detailMap.get(String.valueOf(title.getLineNum())).getExcelName() + "】，实际为【" + title.getContent() + "】")
							}
						} else {
							// 循环匹配不固定表头
							for (Map.Entry<String, TSalaryStandardOriginalDetail> entry : notFixedTitleMap.entrySet()) {
								key = entry.getKey();
								rule = entry.getValue().getExcelRule();
								if (Common.isEmpty(rule)) {
									rule = "等于";
								}
								isFixError = this.judgeRule(titleStr, key, rule);
								// 表示匹配到了
								if (!isFixError) {
									curValue = curMap.get(key);
									if (Common.isNotNull(curValue)) {
										repetitiveKey.append(key).append(";");
									} else {
										notFixedKeyMap.remove(key);
										curMap.put(key, 1);
									}
									// 存储适配map,以做识别内容
									detailMap.put(String.valueOf(title.getLineNum()), entry.getValue());
									// 这里加上截断，就有可能关注不到重复的配置过的表头了：
									// break
								}
							}
							detailSet = detailMap.get(String.valueOf(title.getLineNum()));
							if (detailSet == null && !isOther) {
								detail = new TSalaryStandardOriginalDetail();
								detail.setModelName(titleStr);
								detailMap.put(String.valueOf(title.getLineNum()), detail);
							}
						}
					} else if (isFixedColumn && detailMap.get(String.valueOf(title.getLineNum())) != null && Common.isNotNull(detailMap.get(String.valueOf(title.getLineNum())).getExcelName())) {
						return R.failed("未找到对应配置的表头，请核实导入的原表信息后，重新尝试！！！");
						//return R.failed("表头与导入的不一致,第" + (i + 1) + "列应为【" + detailMap.get(String.valueOf(title.getLineNum())).getExcelName() + "】，实际为空！")
					}
				}
				if (repetitiveKey.length() > 0) {
					return R.failed("检测到关键字重复：" + repetitiveKey + " 请检查调整后再次提交");
				}
				if (notFixedKeyMap.size() > 0) {
					StringBuilder titles = new StringBuilder();
					for (String keyStr : notFixedKeyMap.keySet()) {
						titles.append(keyStr).append(";");
					}
					return R.failed("配置的表头未找到：" + titles);
				}
			} else {
				return R.failed("表格没有表头，请检查表格数据！");
			}

			if (vo.getContentList() != null && !vo.getContentList().isEmpty()) {
				// 智能识别表头
				TSalaryStandardOriginalDetail detail;
				List<Map<String, String>> contentList = new ArrayList<>();
				Map<String, String> newRowMap;
				String value;
				String oldValue;
				int i = 0;
				List<TCheckIdCard> checkList = new ArrayList<>();
				TCheckIdCard check;
				// StringBuilder errMsg = new StringBuilder()
				for (Map<String, String> rowMap : vo.getContentList()) {
					i++;
					newRowMap = new HashMap<>();
					check = new TCheckIdCard();
					for (Map.Entry<String, String> column : rowMap.entrySet()) {
						detail = detailMap.get(column.getKey());
						if (detail != null && Common.isNotNull(detail.getModelName())) {
							value = column.getValue();
							if (Common.isNotNull(value)) {
								try {
									if ("REDUCE".equals(detail.getFlag()) || "ADD".equals(detail.getFlag()) || "MAIN".equals(detail.getFlag())) {
										if ("REDUCE".equals(detail.getFlag())) {
											if (value.contains(",")) {
												value = value.replace(",","");
											}
											value = String.valueOf(new BigDecimal(value).negate());
										}
										oldValue = newRowMap.get(detail.getModelName());
										if (Common.isNotNull(oldValue)) {
											if (oldValue.contains(",")) {
												oldValue = oldValue.replace(",","");
											}
											value = String.valueOf(new BigDecimal(oldValue).add(new BigDecimal(value))
													.setScale(SalaryConstants.PLACES, BigDecimal.ROUND_HALF_UP));
										}
									}
								} catch (NumberFormatException e) {
									return R.failed("数据行第 " + i + " 行，第 " + (Integer.parseInt(column.getKey()) + 1) + " 列 不是金额，不可以进行加减计算！");
								}
								newRowMap.put(detail.getModelName(), value);
								if (SalaryConstants.ID_NUMBER.equals(detail.getModelName())) {
									check.setIdCard(value);
								}
								if (SalaryConstants.EMP_NAME2.equals(detail.getModelName())) {
									check.setName(value);
								}
							}
						}
					}
					if (Common.isEmpty(check.getIdCard())) {
						return R.failed("数据行第 " + i + " 行，无身份证号！");
					}
					if (Common.isEmpty(check.getName())) {
						return R.failed("数据行第 " + i + " 行，无员工姓名！");
					}
					checkList.add(check);
					contentList.add(newRowMap);
				}
				// 最后转化为jsonString
				jsonString = JSON.toJSON(contentList).toString();
				SalaryUploadParamVo returnVo = new SalaryUploadParamVo();
				returnVo.setExcelType(CommonConstants.ZERO_STRING);
				returnVo.setDataNum(vo.getDataNum());
				returnVo.setCheckList(checkList);
				returnVo.setJsonString(jsonString);
				returnVo.setSalaryType(excelVo.getSalaryType());
				returnVo.setConfigId(excelVo.getConfigId());
				returnVo.setIsIssue(excelVo.getIsIssue());
				returnVo.setOrderId(excelVo.getOrderId());
				returnVo.setResList(excelVo.getResList());
				returnVo.setSettleDepart(excelVo.getSettleDepart());
				returnVo.setSetName(excelVo.getSetName());
				returnVo.setOriginalSetName(excelVo.getOriginalSetName());
				returnVo.setNewEmpUpdateFlag(excelVo.getNewEmpUpdateFlag());
				returnVo.setIsFixedColumn(excelVo.getIsFixedColumn());
				//数据保存
				return R.ok(returnVo);
			}

			return R.failed("无法识别数据！请联系管理员");
		} catch (IOException e) {
			e.printStackTrace();
		}
		return R.failed("无法识别数据！请联系管理员！");
	}

	/**
	 * @param titleStr Excel表头
	 * @param key 关键字
	 * @param rule 规则，汉字：等于、包含
	 * @Description: 判断关键字是否正确
	 * @Author: hgw
	 * @Date: 2024/9/13 17:35
	 * @return: boolean
	 **/
	private boolean judgeRule(String titleStr, String key, String rule) {
		boolean isFixError;
		switch (rule) {
			case "等于" : isFixError = !titleStr.equals(key); break;
			case "包含" : isFixError = !titleStr.contains(key); break;
			default: isFixError = !titleStr.equals(key); break;
		}
		return isFixError;
	}

	/**
	 * @Description: 薪资原表配置导出
	 * @Author: hgw
	 * @Date: 2024/5/13 17:47
	 * @return: void
	 **/
	@Override
	public void exportOriginal(HttpServletResponse response, TSalaryOriginalSearchVo searchVo) {
		String fileName = "薪资原表配置导出" + DateUtil.getThisTime() + ".xlsx";
		//获取要导出的列表
		List<TSalaryOriginalExportVo> list = new ArrayList<>();
		long count = exportListCount(searchVo);
		ServletOutputStream out = null;
		try {
			out = response.getOutputStream();
			response.setContentType(CommonConstants.MULTIPART_FORM_DATA);
			response.setCharacterEncoding("utf-8");
			response.setHeader(CommonConstants.CONTENT_DISPOSITION, CommonConstants.ATTACHMENT_FILENAME + URLEncoder.encode(fileName, CommonConstants.UTF8));
			// 这里 需要指定写用哪个class去写，然后写到第一个sheet，然后文件流会自动关闭
			ExcelWriter excelWriter = EasyExcelFactory.write(out, TSalaryOriginalExportVo.class).build();
			int index = 0;
			if (count > CommonConstants.ZERO_INT) {
				for (int i = 0; i <= count; i = i + CommonConstants.EXCEL_EXPORT_LIMIT) {
					// 获取实际记录
					searchVo.setLimitStart(i);
					searchVo.setLimitEnd(CommonConstants.EXCEL_EXPORT_LIMIT);
					list = exportList(searchVo);
					if (Common.isNotNull(list)) {
						WriteSheet writeSheet = EasyExcelFactory.writerSheet("原表配置" + index).build();
						excelWriter.write(list, writeSheet);
						index++;
						list.clear();
					}
				}
			} else {
				WriteSheet writeSheet = EasyExcelFactory.writerSheet("原表配置" + index).build();
				excelWriter.write(list, writeSheet);
			}
			if (Common.isNotNull(list)) {
				list.clear();
			}
			out.flush();
			excelWriter.finish();
		} catch (Exception e) {
			log.error("执行异常", e);
		} finally {
			try {
				if (null != out) {
					out.close();
				}
			} catch (IOException e) {
				log.error("执行异常", e);
			}
		}
	}

}
