package com.yifu.cloud.plus.v1.yifu.common.core.util;

import com.yifu.cloud.plus.v1.yifu.common.core.constant.CacheConstants;
import com.yifu.cloud.plus.v1.yifu.common.core.constant.CommonConstants;
import com.yifu.cloud.plus.v1.yifu.common.core.constant.ExcelAttribute;
import com.yifu.cloud.plus.v1.yifu.common.core.constant.ValidityConstants;
import com.yifu.cloud.plus.v1.yifu.common.core.vo.ExcelColumnVo;
import com.yifu.cloud.plus.v1.yifu.common.core.vo.ExcelSheetVo;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import com.monitorjbl.xlsx.StreamingReader;

import java.io.IOException;
import java.io.InputStream;
import java.io.Serializable;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.time.LocalDateTime;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @Author hgw
 * @Date 2024-10-17 14:37:50
 * @Description 自己写的识别excel内容
 * @Version 1.0
 */
@Data
@Slf4j
public class DiyExcelImportUtil implements Serializable {

	/**
	 * @Description: 识别Excel，单行表头。
	 * @Author: hgw
	 * @Date: 2024/10/18 14:40
	 * @return: com.yifu.cloud.plus.v1.yifu.common.core.util.R<com.yifu.cloud.plus.v1.yifu.common.core.vo.ExcelSheetVo>
	 **/
	public static R<ExcelSheetVo> readXlsx(InputStream inputStream, List<String> specialIdCardList
			, boolean titleFlag, String sheetName, int maxRow) 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 = StreamingReader.builder().rowCacheSize(1000)    // 缓存到内存中的行数(默认是10)
					.bufferSize(10240)     // 读取资源时，缓存到内存的字节大小(默认是1024)
					.open(inputStream);
			// 获取sheet数量
			int sheetNum = sheets.getNumberOfSheets();
			// 每个单元格内容
			String cellValue;
			// 内容行
			int dataRow;
			// 身份证所在列
			int idCardColumn = -1;
			// 空白列起始号
			int nullColumn;
			// 上一个空白行
			int preNullColumn ;
			// 空白列数（一旦数量达到 maxNullColumNum，将停止循环列，并删除title list与content）
			int nullColumnNum;
			// 极限空白列，超过阈值，将不继续循环列
			int maxNullColumNum = 60;
			int rowLastCellNum;
			String idCardValue;
			// 超过极限空白列maxNullColumNum，只循环表头 true:未超越
			boolean nullFlag = true;
			// 极限空白表头列，超过阈值，将不继续循环列
			int maxTitleNullColumNum = 1;
			// 极限空白身份证，超过将不循环行
			int maxIdCardNullRow = 2;
			int idCardNullNum;
			Cell cell;
			// 循环每个sheet
			Sheet sheetAt;
			for (int i = 0; i < sheetNum; i++) {
				sheetAt = sheets.getSheetAt(i);
				if ((Common.isEmpty(sheetName) && i == 0) || (Common.isNotNull(sheetAt.getSheetName()) && Common.isNotNull(sheetName) && sheetName.equals(sheetAt.getSheetName()))) {
					if (sheetAt.getLastRowNum() > maxRow) {
						return R.failed("未避免等待较长时间，请单表不要上传超过" + maxRow + "行的数据！");
					}
					contentList = new ArrayList<>();
					// 塞sheet名称
					sheetVo.setSheetName(sheetAt.getSheetName());
					titleList = new ArrayList<>();
					// 初始化每个sheet的数据行
					dataRow = 1;
					// 循环当前sheet的每行内容
					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) {
							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())
								}
								columnVo.setLineNum(cellNum);
								if (Common.isNotNull(cellValue)) {
									cellValue = cellValue.trim().replaceAll("\\s+", "");
									titleMap.put(String.valueOf(cellNum), cellValue);
									if (cellValue.contains("身份证号")) {
										idCardColumn = cellNum;
									}
								}
								columnVo.setContent(cellValue);
								if (nullFlag) {
									contentMap.put(String.valueOf(cellNum), cellValue);
									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;
										columnList.add(columnVo);
									}
									if (nullColumnNum >= maxNullColumNum) {
										for (int num = maxNullColumNum - 1; num >= 0; 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 (rowNum > 0) {
								if (idCardColumn != -1) {
									if (Common.isEmpty(idCardValue)) {
										idCardNullNum++;
									} else {
										idCardNullNum = 0;
										contentList.add(contentMap);
									}
									/*if (Common.isNotNull(idCardValue) && (regIdCard(idCardValue)
											// 特殊身份证允许识别
											|| regSpecialIdCard(idCardValue, specialIdCardList) || regIdCardError(idCardValue))) {
										contentList.add(contentMap);
									}*/
								}
							} else {
								titleList = new ArrayList<>(columnList);
							}
						}
						rowNum++;
						if (titleFlag) {
							break;
						}
						if (idCardNullNum >= maxIdCardNullRow) {
							break;
						}
					}
					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);
	}

	/**
	 * @Description: 智能识别数据行，但是有缺陷：身份证格式不对，会找不到数据行。
	 * @Author: hgw
	 * @Date: 2024/10/18 14:39
	 * @return: com.yifu.cloud.plus.v1.yifu.common.core.util.R<com.yifu.cloud.plus.v1.yifu.common.core.vo.ExcelSheetVo>
	 **/
	public static R<ExcelSheetVo> readXlsxIntelligence(InputStream inputStream, List<String> specialIdCardList
			, boolean titleFlag, String sheetName, int maxRow) 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 = StreamingReader.builder().rowCacheSize(1000)    // 缓存到内存中的行数(默认是10)
					.bufferSize(10240)     // 读取资源时，缓存到内存的字节大小(默认是1024)
					.open(inputStream);
			// 获取sheet数量
			int sheetNum = sheets.getNumberOfSheets();
			// 每个单元格内容
			String cellValue;
			// 内容行
			int dataRow;
			// 身份证所在列
			int idCardColumn = -1;
			// 空白列起始号
			int nullColumn = -1;
			// 上一个空白行
			int preNullColumn = -1;
			// 空白列数（一旦数量达到 maxNullColumNum，将停止循环列，并删除title list与content）
			int nullColumnNum = 0;
			// 极限空白列，超过阈值，将不继续循环列
			int maxNullColumNum = 60;
			int rowLastCellNum = -1;
			String idCardValue = null;
			// 数据行是否被确认，true否
			boolean dataRowFlag;
			// 超过极限空白列maxNullColumNum，只循环表头 true:未超越
			boolean nullFlag = true;
			// 极限空白表头列，超过阈值，将不继续循环列
			int maxTitleNullColumNum = 1;
			// 极限空白身份证，超过将不循环行
			int maxIdCardNullRow = 1;
			int idCardNullNum = 0;
			Cell cell;
			// 循环每个sheet
			Sheet sheetAt;
			for (int i = 0; i < sheetNum; i++) {
				sheetAt = sheets.getSheetAt(i);
				if ((Common.isEmpty(sheetName) && i == 0) || (Common.isNotNull(sheetAt.getSheetName()) && Common.isNotNull(sheetName) && sheetName.equals(sheetAt.getSheetName()))) {
					if (sheetAt.getLastRowNum() > maxRow) {
						return R.failed("未避免等待较长时间，请单表不要上传超过" + maxRow + "行的数据！");
					}
					contentList = new ArrayList<>();
					// 塞sheet名称
					sheetVo.setSheetName(sheetAt.getSheetName());
					titleList = new ArrayList<>();
					// 初始化每个sheet的数据行
					dataRow = 0;
					dataRowFlag = true;
					// 循环当前sheet的每行内容
					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) {
							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())
								}
								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) {
						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: 判断身份证格式：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: 判断特殊身份证
	 * 格式正确返回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: 判断类似身份证的数据，进识别行，提示客服该行错误：14位数字
	 * 格式正确返回true
	 * @Author: hgw
	 * @Date: 2022/5/13 11:53
	 * @return: boolean
	 **/
	private static boolean regIdCardError(String content) {
		return content.matches("^.*\\d{14}.*$");
	}

	/**
	 * @Description: 判断字段格式
	 * @Author: hgw
	 * @Date: 2024/10/14 11:38
	 * @return: java.lang.String
	 **/
	public static String validateUtil(String c, ExcelAttribute attr, StringBuilder errorFormat, Map<String, String> dictMap
			, Map<String, String> parentAreaMap, String shuName, boolean onlyList, Map<String, Date> startDateMap) {
		String tempStr = null;
		Date dateValue = null;
		//非空校验
		if (Common.isNotNull(c)) {
			// 仅校验附属信息
			if (onlyList) {
				if (attr.isMark()) {
					if (attr.isDate()) {
						String format = "yyyyMMdd";
						if (Common.isNotNull(attr.dateFormat())) {
							format = attr.dateFormat();
						}
						dateValue = DateUtil.stringDateFormat(c.trim(), format);
						if (null == dateValue) {
							errorFormat.append(attr.name()).append("；");
						} else {
							startDateMap.put(shuName, dateValue);
							tempStr = c.trim();
						}
					}
					//手机号码校验
					if (attr.isPhone() && getMatchRes(c.trim(), attr, ValidityConstants.MOBILE_PATTERN)) {
						errorFormat.append(attr.name()).append("；");
					}
					//最大长度校验
					if (attr.maxLength() > 0 && c.length() > attr.maxLength()) {
						errorFormat.append(attr.name()).append("_超出最大长度；");
					}
					// 字典
					if (attr.isDataId() && null != dictMap) {
						tempStr = null;
						if (Common.isNotNull(attr.readConverterExp())) {
							String[] convertSource = attr.readConverterExp().split(",");
							String[] itemArray;
							for (String item : convertSource) {
								itemArray = item.split("=");
								if (itemArray.length>1 && itemArray[1].equals(c)) {
									tempStr = itemArray[0];
									break;
								}
							}
						} else {
							tempStr = getLableIdByValue(attr, dictMap, c);
						}
						if (Common.isEmpty(tempStr)) {
							errorFormat.append(attr.name()).append("_未找到字典值；");
						}
					}
					// 时间大小比较
					if (Common.isNotNull(attr.pattern())) {
						if (!("validityEnd".equals(shuName) && "长期".equals(c)) && dateValue != null) {
							Date startDate = startDateMap.get(attr.pattern());
							if (startDate != null) {
								if (!dateValue.after(startDate)) {
									errorFormat.append(attr.name()).append("_小于起始日期；");
								}
							} else {
								errorFormat.append(attr.name()).append("_未找到起始日期，请将起始日期放在左边列；");
							}
						}
					}
				}
			} else {
				//日期格式校验
				if (attr.isDate()) {
					if (!("validityEnd".equals(shuName) && "长期".equals(c))) {
						String format = "yyyyMMdd";
						if (Common.isNotNull(attr.dateFormat())) {
							format = attr.dateFormat();
						}
						if ("taxMonth".equals(shuName) && c.length() != 6) {
							errorFormat.append(attr.name()).append("；");
						} else {
							dateValue = DateUtil.stringDateFormat(c.trim(), format);
							if (null == dateValue) {
								errorFormat.append(attr.name()).append("；");
							} else {
								startDateMap.put(shuName, dateValue);
								tempStr = c.trim();
							}
						}
					}
				}
				//手机号码校验
				if (attr.isPhone() && getMatchRes(c.trim(), attr, ValidityConstants.MOBILE_PATTERN)) {
					errorFormat.append(attr.name()).append("；");
				}
				//身份证校验
				if (attr.isIdCard() && getMatchRes(c.trim(), attr, ValidityConstants.IDCARD_PATTERN)) {
					errorFormat.append(attr.name()).append("；");
				}
				//最大长度校验
				if (attr.maxLength() > 0 && c.length() > attr.maxLength()) {
					errorFormat.append(attr.name()).append("_超出最大长度；");
				}
				// 区域
				if (attr.isArea()) {
					//区域字段处理
					if (Common.isEmpty(attr.parentField())) {
						tempStr = dictMap.get(c + "_0");
						if (Common.isEmpty(tempStr)) {
							tempStr = String.valueOf(RedisUtil.redis.opsForValue().get(CacheConstants.AREA_VALUE + c));
							if (Common.isEmpty(tempStr)) {
								errorFormat.append(attr.name()).append("不正确或上级异常；");
							} else {
								parentAreaMap.put(shuName, c);
								dictMap.put(c + "_0", tempStr);
							}
						} else {
							parentAreaMap.put(shuName, c);
						}
					} else {
						tempStr = dictMap.get(c.trim() + "_" + parentAreaMap.get(attr.parentField()));
						if (Common.isEmpty(tempStr)) {
							tempStr = String.valueOf(RedisUtil.redis.opsForValue().get(CacheConstants.AREA_VALUE + c + "_" + parentAreaMap.get(attr.parentField())));
							if (Common.isEmpty(tempStr)) {
								errorFormat.append(attr.name()).append("不正确或上级异常；");
							} else {
								parentAreaMap.put(shuName, c);
								dictMap.put(c.trim() + "_" + parentAreaMap.get(attr.parentField()), tempStr);
							}
						} else {
							parentAreaMap.put(shuName, c);
						}
					}
				}

				// 字典
				if (attr.isDataId() && null != dictMap) {
					tempStr = null;
					if (Common.isNotNull(attr.readConverterExp())) {
						String[] convertSource = attr.readConverterExp().split(",");
						String[] itemArray;
						for (String item : convertSource) {
							itemArray = item.split("=");
							if (itemArray.length>1 && itemArray[1].equals(c)) {
								tempStr = itemArray[0];
								break;
							}
						}
					} else {
						tempStr = getLableIdByValue(attr, dictMap, c);
					}
					if (Common.isEmpty(tempStr)) {
						errorFormat.append(attr.name()).append("_未找到字典值；");
					}
				}
				// 时间大小比较
				if (Common.isNotNull(attr.pattern())) {
					if (!("validityEnd".equals(shuName) && "长期".equals(c)) && dateValue != null) {
						Date startDate = startDateMap.get(attr.pattern());
						if (startDate != null) {
							if (!dateValue.after(startDate)) {
								errorFormat.append(attr.name()).append("_小于起始日期；");
							}
						} else {
							errorFormat.append(attr.name()).append("_未找到起始日期，请将起始日期放在左边列；");
						}
					}
				}
			}

		}
		return tempStr;
	}

	// 为空的检查一下起始时间有没有值，有值，截止日期不可为空
	public static void validateUtilEmpty(String c, ExcelAttribute attr, StringBuilder errorFormat, Map<String, Date> startDateMap) {
		//非空校验
		if (Common.isEmpty(c) && Common.isNotNull(attr.pattern())) {
			Date startDate = startDateMap.get(attr.pattern());
			if (startDate != null) {
				errorFormat.append(attr.name()).append("_起始日期有值则截止日期必填；");
			}
		}
	}

	private static boolean getMatchRes(String c, ExcelAttribute attr, String pattern) {
		return !c.matches(Common.isEmpty(attr.pattern()) ? pattern : attr.pattern());
	}

	private static String getLableIdByValue(ExcelAttribute attr, Map<String, String> dictMap, String value) {
		String tempValue = null;
		if (null != dictMap && dictMap.size() > 0 && Common.isNotNull(value)) {
			if (Common.isNotNull(attr.dataType())) {
				tempValue =  dictMap.get(attr.dataType() + CommonConstants.DOWN_LINE_STRING + value);
				if (Common.isEmpty(tempValue)){
					tempValue  = dictMap.get(attr.dataType() + CommonConstants.DOWN_LINE_STRING +"id"+CommonConstants.DOWN_LINE_STRING +  value);
				}
			} else {
				tempValue = dictMap.get(value);
			}
		}
		// 默认字典获取不到 去 缓存获取
		if (Common.isEmpty(tempValue) && Common.isNotNull(attr.dataType())) {
			Map<String,String> dicObj = (Map<String, String>) RedisUtil.redis.opsForValue()
					.get(CacheConstants.DICT_DETAILS
							+ CommonConstants.COLON_STRING
							+ attr.dataType());
			if (dicObj != null) {
				for (Map.Entry<String, String> entry : dicObj.entrySet()) {
					if (Common.isNotNull(entry.getValue()) && entry.getValue().equals(value.trim())) {
						tempValue = entry.getKey();
						dictMap.put(attr.dataType() + CommonConstants.DOWN_LINE_STRING + value, tempValue);
						break;
					}
				}
			}
		}
		return tempValue;
	}

	// 塞翻译后的值
	public static <T> void setFieldsValue(Field field, String cellValueStr, Class<?> fieldType, T entity) throws IllegalAccessException {
		if (String.class == fieldType) {
			setStringField(field, cellValueStr, entity);
		} else if (Date.class == fieldType && Common.isNotNull(cellValueStr.trim())) {
			setDateField(field, cellValueStr, entity);
		} else if (LocalDateTime.class == fieldType && Common.isNotNull(cellValueStr.trim())) {
			// 1.9.7: 加时间格式转化
			setDateTimeField(field, cellValueStr, entity);
		} else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {
			setIntegerField(field, cellValueStr, entity);
		} else {
			field.set(entity, cellValueStr);
		}
	}

	// 塞导入的值
	public static <T> void setFields(Field field, String cellValueStr, Class<?> fieldType, T entity) throws IllegalAccessException {
		if (String.class == fieldType) {
			field.set(entity, cellValueStr);
		} else if (Date.class == fieldType && Common.isNotNull(cellValueStr.trim())) {
			setDateField(field, cellValueStr, entity);
		} else if (LocalDateTime.class == fieldType && Common.isNotNull(cellValueStr.trim())) {
			// 1.9.7: 加时间格式转化
			setDateTimeField(field, cellValueStr, entity);
		} else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {
			setIntegerField(field, cellValueStr, entity);
		} else {
			field.set(entity, cellValueStr);
		}
	}

	/**
	 * @Description: 判断固定字符串，塞值
	 * @Author: hgw
	 * @Date: 2021/1/11 11:47
	 * @return: void
	 **/
	public static <T> void setStringField(Field field, String cellValueStr, T entity) throws IllegalAccessException {
		if ("是".equals(cellValueStr)) {
			field.set(entity, "0");
		} else if ("否".equals(cellValueStr)) {
			field.set(entity, "1");
		} else {
			field.set(entity, cellValueStr);
		}
	}

	public static <T> void setDateField(Field field, String cellValueStr, T entity) throws IllegalAccessException {
		String patten = DateUtil.ISO_DATE_FORMAT;
		if (cellValueStr.contains("-")) {
			patten = DateUtil.ISO_EXPANDED_DATE_FORMAT;
		} else if (cellValueStr.contains("/")) {
			patten = DateUtil.DATETIME_PATTERN_XIEGANG;
		}
		field.set(entity, DateUtil.stringDateFormat(cellValueStr.trim(), patten));
	}

	public static <T> void setDateTimeField(Field field, String cellValueStr, T entity) throws IllegalAccessException {
		String patten = DateUtil.ISO_DATE_FORMAT;
		if (cellValueStr.contains("-")) {
			patten = DateUtil.ISO_EXPANDED_DATE_FORMAT;
		} else if (cellValueStr.contains("/")) {
			patten = DateUtil.DATETIME_PATTERN_XIEGANG;
		}
		String pattenTime = patten+" 00:00:00";
		field.set(entity, LocalDateTimeUtils.formatTime(cellValueStr.trim(), pattenTime));
	}

	public static <T> void setIntegerField(Field field, String cellValueStr, T entity) throws IllegalAccessException {
		if ("否".equals(cellValueStr)) {
			field.set(entity, 0);
		} else if ("是".equals(cellValueStr)) {
			field.set(entity, 1);
		} else {
			field.set(entity, Integer.parseInt(cellValueStr));
		}
	}

}
