data-daq/service-daq/src/main/java/com/simuwang/daq/service/EmailTemplateService.java

package com.simuwang.daq.service;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.exceptions.ExceptionUtil;
import cn.hutool.core.lang.Pair;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.StrUtil;
import com.simuwang.base.common.conts.ApplicationRuleFileConst;
import com.simuwang.base.common.conts.EmailDataDirectionConst;
import com.simuwang.base.common.conts.EmailFieldConst;
import com.simuwang.base.common.util.DateUtils;
import com.simuwang.base.common.util.ExcelUtil;
import com.simuwang.base.common.util.NavDataUtil;
import com.simuwang.base.common.util.StringUtil;
import com.simuwang.base.mapper.daq.EmailTemplateApplicationRuleMapper;
import com.simuwang.base.mapper.daq.EmailTemplateDataRuleMapper;
import com.simuwang.base.mapper.daq.EmailTemplateMappingMapper;
import com.simuwang.base.pojo.dto.EmailFileContentDTO;
import com.simuwang.base.pojo.dos.EmailTemplateApplicationRuleDO;
import com.simuwang.base.pojo.dos.EmailTemplateDataRuleDO;
import com.simuwang.base.pojo.dos.EmailTemplateInfoDO;
import com.simuwang.base.pojo.dto.EmailContentInfoDTO;
import com.simuwang.base.pojo.dto.EmailFundNavDTO;
import com.simuwang.base.pojo.dto.TemplateDataRuleDTO;
import com.simuwang.base.pojo.dto.TemplateDetailDTO;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import java.io.File;
import java.math.BigDecimal;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @author mozuwen
 * @date 2024-09-23
 * @description 净值模板解析
 */
@Service
public class EmailTemplateService {

    private static final Logger log = LoggerFactory.getLogger(EmailTemplateService.class);

    private final EmailTemplateMappingMapper emailTemplateMappingMapper;
    private final EmailTemplateApplicationRuleMapper emailTemplateApplicationRuleMapper;
    private final EmailTemplateDataRuleMapper emailTemplateDataRuleMapper;
    private final static Map<Integer, String> FILE_TYPE_MAP = new HashMap<>();

    static {
        // 1-备案编码，2-基金名称，3-净值日期，4-单位净值，5-累计净值，6-资产份额，7-资产净值
        FILE_TYPE_MAP.put(1, EmailFieldConst.REGISTER_NUMBER);
        FILE_TYPE_MAP.put(2, EmailFieldConst.FUND_NAME);
        FILE_TYPE_MAP.put(3, EmailFieldConst.PRICE_DATE);
        FILE_TYPE_MAP.put(4, EmailFieldConst.NAV);
        FILE_TYPE_MAP.put(5, EmailFieldConst.CUMULATIVE_NAV_WITHDRAWAL);
        FILE_TYPE_MAP.put(6, EmailFieldConst.ASSET_SHARE);
        FILE_TYPE_MAP.put(7, EmailFieldConst.ASSET_NET);
    }

    public EmailTemplateService(EmailTemplateMappingMapper emailTemplateMappingMapper, EmailTemplateApplicationRuleMapper emailTemplateApplicationRuleMapper,
                                EmailTemplateDataRuleMapper emailTemplateDataRuleMapper) {
        this.emailTemplateMappingMapper = emailTemplateMappingMapper;
        this.emailTemplateApplicationRuleMapper = emailTemplateApplicationRuleMapper;
        this.emailTemplateDataRuleMapper = emailTemplateDataRuleMapper;
    }

    public List<EmailFundNavDTO> parseUsingTemplate(EmailContentInfoDTO emailContentInfoDTO) {
        // 考虑文件为PDF，html,zip等情况 -> 将正文html和pdf文件转成Excel
        List<EmailFileContentDTO> emailFileContentDTOList = getRealFilePath(emailContentInfoDTO.getFilePath(), emailContentInfoDTO.getEmailContent());
        if (CollUtil.isEmpty(emailFileContentDTOList)) {
            return CollUtil.newArrayList();
        }
        List<EmailFundNavDTO> emailFundNavDTOList = CollUtil.newArrayList();
        for (EmailFileContentDTO emailFileContentDTO : emailFileContentDTOList) {
            String filePath = emailFileContentDTO.getFilePath();
            String textContent = emailFileContentDTO.getContent();
            // 获取模板信息
            List<TemplateDetailDTO> templateDetailDTOList = getTemplateDetail(emailContentInfoDTO, textContent, filePath);
            if (CollUtil.isEmpty(templateDetailDTOList)) {
                log.info("未匹配模板 -> 参数:{}", emailFileContentDTO);
                return CollUtil.newArrayList();
            }
            // 按照模板分别进行解析
            for (TemplateDetailDTO templateDetailDTO : templateDetailDTOList) {
                try {
                    List<EmailFundNavDTO> fundNavDTOList = extraFundNav(filePath, textContent, templateDetailDTO);
                    //因为异常数据也要保留，不能直接过滤，所以这里需要注释掉
//                    fundNavDTOList = fundNavDTOList.stream().filter(NavDataUtil::navDataFormatCheck).toList();
                    if (CollUtil.isEmpty(fundNavDTOList)) {
                        log.info("模板配置解析不到数据 -> 模板id:{}", templateDetailDTO.getTemplateId());
                        continue;
                    }
                    fundNavDTOList.forEach(e -> e.setTemplateId(templateDetailDTO.getTemplateId()));
                    emailFundNavDTOList.addAll(fundNavDTOList);
                    log.info("模板配置解析成功 -> 模板id:{},数据:{}", templateDetailDTO.getTemplateId(), fundNavDTOList);
                } catch (Exception e) {
                    log.error("净值模板解析报错 -> 模板id:{},文件:{},堆栈信息:{}", templateDetailDTO.getTemplateId(), filePath, ExceptionUtil.stacktraceToString(e));
                }
            }
        }
        // 过滤掉相同的数据(考虑到多个模板解析到相同数据的情况)
        return emailFundNavDTOList.stream().distinct().toList();
    }

    /**
     * 根据模板信息提取净值数据
     *
     * @param filePath          文件路径(excel类型)
     * @param textContent       邮件正文内容
     * @param templateDetailDTO 模板配置信息
     * @return 净值数据
     */
    private List<EmailFundNavDTO> extraFundNav(String filePath, String textContent, TemplateDetailDTO templateDetailDTO) {
        List<TemplateDataRuleDTO> dataRuleDetailList = templateDetailDTO.getDataRuleDetailList();
        Map<String, String> fieldValueMap = MapUtil.newHashMap(8);
        List<Map<String, String>> fieldValueMapList = CollUtil.newArrayList();
        boolean hasParsedTableData = false;
        Integer direction = templateDetailDTO.getDirection();

        for (TemplateDataRuleDTO templateDataRuleDTO : dataRuleDetailList) {
            Integer fieldName = templateDataRuleDTO.getFieldName();
            Integer position = templateDataRuleDTO.getPosition() == null ? 2 : templateDataRuleDTO.getPosition();
            // 提取位置-正文
            if (position == 1) {
                String valueByPattern = getValueByPattern(textContent, templateDataRuleDTO.getFieldRule());
                fieldValueMap.put(FILE_TYPE_MAP.get(fieldName), valueByPattern);
            }
            // 提取位置-正文表格或附件表格(表格数据提取一次后不再提取)
            if (position == 2 && !hasParsedTableData) {
                hasParsedTableData = true;
                List<TemplateDataRuleDTO> dataRuleDTOS = dataRuleDetailList.stream().filter(e -> e.getPosition() == null || e.getPosition() == 2).toList();
                fieldValueMapList = parsedTableData(filePath, direction, dataRuleDTOS);
            }
        }
        return buildEmailFundNavDTO(fieldValueMap, fieldValueMapList);
    }

    /**
     * 根据数据提取规则提取数据
     *
     * @param filePath     文件路径(excel类型)
     * @param direction    数据方向:1-行,2-列,3-其他(无规律)
     * @param dataRuleDTOS 数据提取规则
     * @return 数据字段-字段值映射关系
     */
    private List<Map<String, String>> parsedTableData(String filePath, Integer direction, List<TemplateDataRuleDTO> dataRuleDTOS) {
        if (StrUtil.isBlank(filePath) || CollUtil.isEmpty(dataRuleDTOS)) {
            return CollUtil.newArrayList();
        }
        Sheet sheet = ExcelUtil.getFirstSheet(filePath);
        if (sheet == null) {
            return CollUtil.newArrayList();
        }

        Map<String, Pair<Integer, Integer>> fieldPositionMap = MapUtil.newHashMap();
        Map<String, String> fieldPatternMap = MapUtil.newHashMap(8);
        Map<String, BigDecimal> fieldUnitConvertMap = MapUtil.newHashMap(8);
        Map<String, String> fieldReplaceMap = MapUtil.newHashMap(8);
        for (TemplateDataRuleDTO dataRuleDTO : dataRuleDTOS) {
            if (dataRuleDTO.getRow() == null) {
                continue;
            }
            Integer row = dataRuleDTO.getRow() - 1;
            int column = columnLetterToIndex(dataRuleDTO.getColumn());
            Pair<Integer, Integer> pair = new Pair<>(row, column);
            fieldPositionMap.put(FILE_TYPE_MAP.get(dataRuleDTO.getFieldName()), pair);
            if (StrUtil.isNotBlank(dataRuleDTO.getFieldRule())) {
                fieldPatternMap.put(FILE_TYPE_MAP.get(dataRuleDTO.getFieldName()), dataRuleDTO.getFieldRule());
            }
            if (dataRuleDTO.getUnitConvert() != null) {
                fieldUnitConvertMap.put(FILE_TYPE_MAP.get(dataRuleDTO.getFieldName()), dataRuleDTO.getUnitConvert());
            }
            if (StringUtil.isNotEmpty(dataRuleDTO.getReplaceText())) {
                fieldReplaceMap.put(FILE_TYPE_MAP.get(dataRuleDTO.getFieldName()), dataRuleDTO.getReplaceText());
            }
        }

        List<Map<String, String>> fieldValueMapList = CollUtil.newArrayList();
        if (direction.equals(EmailDataDirectionConst.ROW_DIRECTION_TYPE)) {
            int startRow = fieldPositionMap.values().stream().map(Pair::getKey).min(Integer::compareTo).orElse(0);
            for (int i = startRow + 1; i <= sheet.getLastRowNum(); i++) {
                Map<String, String> fieldValueMap = MapUtil.newHashMap(8);
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                for (Map.Entry<String, Pair<Integer, Integer>> fieldEntry : fieldPositionMap.entrySet()) {
                    String fieldName = fieldEntry.getKey();
                    int columnIndex = fieldEntry.getValue().getValue();
                    Cell cell = row.getCell(columnIndex);
                    String value = ExcelUtil.getCellValue(cell);
                    String fieldRule = fieldPatternMap.get(fieldName);
                    String replaceText = fieldReplaceMap.get(fieldName);
                    boolean isDateFormat = fieldName.equals(EmailFieldConst.PRICE_DATE) && StringUtil.isNumeric(value) && StrUtil.isNotBlank(value) &&  StringUtil.compare2NumericValue(value);
                    if (isDateFormat) {
                        value = ExcelUtil.convertExcelDateToString(value);
                    }
                    value=replaceText(value,replaceText);
                    String cellValue = getValueByPattern(value, fieldRule);
                    BigDecimal unitConvert = fieldUnitConvertMap.get(fieldName);
                    cellValue = getValueAfterUnitConvert(cellValue, unitConvert);
                    fieldValueMap.put(fieldName, cellValue);
                }
                fieldValueMapList.add(fieldValueMap);
            }
        }
        if (direction.equals(EmailDataDirectionConst.COLUMN_DIRECTION_TYPE)) {
            int startColumn = fieldPositionMap.values().stream().map(Pair::getValue).min(Integer::compareTo).orElse(0);
            for (int columnNum = startColumn + 1; columnNum < EmailDataDirectionConst.MAX_ROW_COLUMN; columnNum++) {
                Map<String, String> fieldValueMap = MapUtil.newHashMap();
                for (Map.Entry<String, Pair<Integer, Integer>> fieldEntry : fieldPositionMap.entrySet()) {
                    String fieldName = fieldEntry.getKey();
                    Integer rowIndex = fieldEntry.getValue().getKey();
                    Row row = sheet.getRow(rowIndex);
                    if (row == null) {
                        continue;
                    }
                    Integer columnIndex = fieldEntry.getValue().getValue();
                    Cell cell = row.getCell(columnIndex + 1);
                    String fieldRule = fieldPatternMap.get(fieldName);
                    String cellValue = getValueByPattern(ExcelUtil.getCellValue(cell), fieldRule);

                    BigDecimal unitConvert = fieldUnitConvertMap.get(fieldName);
                    cellValue = getValueAfterUnitConvert(cellValue, unitConvert);
                    fieldValueMap.put(fieldName, cellValue);
                }
                fieldValueMapList.add(fieldValueMap);
            }
        }
        // 表头和数据在同一个单元格
        if (direction.equals(EmailDataDirectionConst.OTHER_DIRECTION_TYPE)) {
            Map<String, String> fieldValueMap = MapUtil.newHashMap();
            for (Map.Entry<String, Pair<Integer, Integer>> fieldEntry : fieldPositionMap.entrySet()) {
                String fieldName = fieldEntry.getKey();

                Integer rowIndex = fieldEntry.getValue().getKey();
                Integer columnIndex = fieldEntry.getValue().getValue();
                Row row = sheet.getRow(rowIndex);
                if (row == null) {
                    continue;
                }
                Cell cell = row.getCell(columnIndex);
                String fieldRule = fieldPatternMap.get(fieldName);
                String cellValue = getValueByPattern(ExcelUtil.getCellValue(cell), fieldRule);

                BigDecimal unitConvert = fieldUnitConvertMap.get(fieldName);
                cellValue = getValueAfterUnitConvert(cellValue, unitConvert);
                fieldValueMap.put(fieldName, cellValue);
            }
            fieldValueMapList.add(fieldValueMap);
        }
        return fieldValueMapList;
    }

    private String replaceText(String value, String replaceText) {
        if(StringUtil.isEmpty(replaceText) || StringUtil.isEmpty(value)){
            return value;
        }
        for(int i=0;i<replaceText.length();i++){
            value = value.replaceAll(String.valueOf(replaceText.charAt(i)),"");
        }
        return value;
    }

    /**
     * 封装解析的数据为净值数据
     *
     * @param textFieldValueMap      从正文表格中解析到的数据
     * @param excelFieldValueMapList 从正文文本中解析到的数据
     * @return 净值数据
     */
    private List<EmailFundNavDTO> buildEmailFundNavDTO(Map<String, String> textFieldValueMap, List<Map<String, String>> excelFieldValueMapList) {
        if (MapUtil.isEmpty(textFieldValueMap) && CollUtil.isEmpty(excelFieldValueMapList)) {
            return CollUtil.newArrayList();
        }
        List<EmailFundNavDTO> fundNavDTOList = CollUtil.newArrayList();
        if (CollUtil.isNotEmpty(excelFieldValueMapList)) {
            for (Map<String, String> excelFieldValueMap : excelFieldValueMapList) {
                fundNavDTOList.add(buildEmailFundNavDTO(excelFieldValueMap, textFieldValueMap));
            }
        }
        if (MapUtil.isNotEmpty(textFieldValueMap) && CollUtil.isEmpty(excelFieldValueMapList)) {
            fundNavDTOList.add(buildEmailFundNavDTO(null, textFieldValueMap));
        }
        return fundNavDTOList;
    }

    /**
     * 获取模板配置信息
     *
     * @param emailContentInfoDTO 采集到的邮件原始信息
     * @param textContent         邮件正文内容
     * @param filePath            文件路径(excel类型)
     * @return 模板配置信息列表
     */
    public List<TemplateDetailDTO> getTemplateDetail(EmailContentInfoDTO emailContentInfoDTO, String textContent, String filePath) {
        List<TemplateDetailDTO> templateDetailDTOList = CollUtil.newArrayList();
        String senderEmail = emailContentInfoDTO.getSenderEmail();
        if (StrUtil.isBlank(senderEmail)) {
            return templateDetailDTOList;
        }
        // 1-excel类型，2-正文类型
        int type = StrUtil.isNotBlank(emailContentInfoDTO.getFilePath()) && ExcelUtil.isHTML(emailContentInfoDTO.getFilePath()) ? 2 : 1;
        // 查询邮箱配置的模板Id
        List<EmailTemplateInfoDO> emailTemplateInfoDOList = emailTemplateMappingMapper.queryByEmail(senderEmail, type);
        List<Integer> templateIdList = emailTemplateInfoDOList.stream().map(EmailTemplateInfoDO::getId).distinct().collect(Collectors.toList());
        if (CollUtil.isEmpty(templateIdList)) {
            return templateDetailDTOList;
        }
        Map<Integer, EmailTemplateInfoDO> templateIdDirectionMap = emailTemplateInfoDOList.stream()
                .collect(Collectors.toMap(EmailTemplateInfoDO::getId, v -> v, (oldValue, newValue) -> newValue));
        // 查询模版适用性规则 -> 判断邮件是否满足模板适用性规则(模板的适应性规则允许为空 -> 这种情况下满足模板的适用条件)
        List<EmailTemplateApplicationRuleDO> templateApplicationRuleDOList = emailTemplateApplicationRuleMapper.queryByTemplateId(templateIdList);
        Map<Integer, List<EmailTemplateApplicationRuleDO>> templateIdApplicationRuleMap = MapUtil.newHashMap();
        if (CollUtil.isNotEmpty(templateApplicationRuleDOList)) {
            templateIdApplicationRuleMap = templateApplicationRuleDOList.stream().collect(Collectors.groupingBy(EmailTemplateApplicationRuleDO::getTemplateId));
        }
        // 查询模版数据规则
        List<EmailTemplateDataRuleDO> templateDataRuleDOList = emailTemplateDataRuleMapper.queryByTemplateId(templateIdList);
        if (CollUtil.isEmpty(templateDataRuleDOList)) {
            return templateDetailDTOList;
        }
        // 过滤掉(表格行和表格列)或者(提取规则)都为空的数据
        Map<Integer, List<EmailTemplateDataRuleDO>> templateIdDataRuleMap = templateDataRuleDOList.stream()
                .filter(e -> (e.getRow() != null && StrUtil.isNotBlank(e.getColumn())) || StrUtil.isNotBlank(e.getFieldRule()))
                .collect(Collectors.groupingBy(EmailTemplateDataRuleDO::getTemplateId));

        for (Integer templateId : templateIdList) {
            List<EmailTemplateApplicationRuleDO> applicationRuleDOList = templateIdApplicationRuleMap.get(templateId);
            // 判断是否满足模板适用性规则
            boolean isMatchTemplate = isMatchTemplate(textContent, emailContentInfoDTO.getEmailTitle(), emailContentInfoDTO.getFileName(), filePath, applicationRuleDOList);
            if (!isMatchTemplate) {
                continue;
            }
            TemplateDetailDTO templateDetailDTO = new TemplateDetailDTO();
            templateDetailDTO.setTemplateId(templateId);
            EmailTemplateInfoDO emailTemplateInfoDO = templateIdDirectionMap.get(templateId);
            templateDetailDTO.setDirection(emailTemplateInfoDO.getDirection());
            templateDetailDTO.setType(emailTemplateInfoDO.getType());
            templateDetailDTO.setStartIndex(emailTemplateInfoDO.getStartIndex());
            templateDetailDTO.setEndIndex(emailTemplateInfoDO.getEndIndex());

            List<EmailTemplateDataRuleDO> dataRuleDOList = templateIdDataRuleMap.get(templateId);
            List<TemplateDataRuleDTO> dataRuleDetailList = dataRuleDOList.stream().map(e -> BeanUtil.copyProperties(e, TemplateDataRuleDTO.class)).toList();
            templateDetailDTO.setDataRuleDetailList(dataRuleDetailList);
            templateDetailDTOList.add(templateDetailDTO);
        }
        return templateDetailDTOList;
    }

    /**
     * 判断邮件是否满足模板的适用性规则
     *
     * @param emailContent          邮件正文内容（已转成纯文本,以换行符分割每行内容）
     * @param emailTitle            邮件主题
     * @param fileName              附件名称
     * @param filePath              文件路径（邮件正文html转成）
     * @param applicationRuleDOList 模板配置的适用性规则列表（为空时 -> 返回true）
     * @return true or false
     */
    private boolean isMatchTemplate(String emailContent, String emailTitle, String fileName,
                                    String filePath, List<EmailTemplateApplicationRuleDO> applicationRuleDOList) {
        if (CollUtil.isEmpty(applicationRuleDOList)) {
            return true;
        }
        return applicationRuleDOList.stream()
                .allMatch(e -> isMathApplicationRule(e, emailContent, emailTitle, fileName, filePath));
    }

    /**
     * 判断邮件是否满足模板的某条适用性规则
     *
     * @param applicationRuleDO 适用性规则
     * @param emailContent      邮件正文内容（可为空）
     * @param emailTitle        邮件主题
     * @param fileName          附件名称
     * @param filePath          文件路径（邮件正文html转成）
     * @return true or false
     */
    private boolean isMathApplicationRule(EmailTemplateApplicationRuleDO applicationRuleDO, String emailContent, String emailTitle, String fileName, String filePath) {
        boolean isMatch = true;
        Integer type = applicationRuleDO.getType();
        String containKeyword = applicationRuleDO.getContainKeyword();
        String notContainKeyword = applicationRuleDO.getNotContainKeyword();
        if (ApplicationRuleFileConst.EMAIL_TITLE_FILE.equals(type)) {
            isMatch = iskeywordMatch(emailTitle, containKeyword, notContainKeyword);
        } else if (ApplicationRuleFileConst.EMAIL_CONTENT_FILE.equals(type)) {
            isMatch = iskeywordMatch(emailContent, containKeyword, notContainKeyword);
        } else if (ApplicationRuleFileConst.EMAIL_FILE_NAME_FILE.equals(type)) {
            isMatch = iskeywordMatch(fileName, containKeyword, notContainKeyword);
        } else if (ApplicationRuleFileConst.EMAIL_EXCEL_CONTENT_FILE.equals(type)) {
            if (StrUtil.isNotBlank(fileName) && ExcelUtil.isExcel(fileName)) {
                Integer rowIndex = applicationRuleDO.getRow();
                if (rowIndex == null || StrUtil.isBlank(applicationRuleDO.getColumn())) {
                    return false;
                }
                Sheet sheet = ExcelUtil.getFirstSheet(filePath);
                if (sheet == null) {
                    return false;
                }
                rowIndex = rowIndex - 1;
                Row row = sheet.getRow(rowIndex);
                if (row == null) {
                    return false;
                }
                int column = columnLetterToIndex(applicationRuleDO.getColumn());
                Cell cell = row.getCell(column);
                if (cell == null) {
                    return false;
                }
                String cellValue = ExcelUtil.getCellValue(cell);
                if (StrUtil.isBlank(cellValue)) {
                    return false;
                }
                isMatch = iskeywordMatch(cellValue, containKeyword, notContainKeyword);
            }
        }
        return isMatch;
    }

    /**
     * 判断字符串是否包含指定的关键词列表，并且不包含指定的排除关键词列表。
     *
     * @param emailTitle        要检查的字符串
     * @param containKeyword    逗号分隔的必须包含的关键词列表
     * @param notContainKeyword 逗号分隔的必须不包含的关键词列表
     * @return true or false
     */
    public boolean iskeywordMatch(String emailTitle, String containKeyword, String notContainKeyword) {
        boolean isMatch = true;
        if (StrUtil.isNotBlank(containKeyword)) {
            isMatch &= Arrays.stream(containKeyword.split(","))
                    .allMatch(emailTitle::contains);
        }
        if (StrUtil.isNotBlank(notContainKeyword)) {
            isMatch &= Arrays.stream(notContainKeyword.split(","))
                    .noneMatch(emailTitle::contains);
        }
        return isMatch;
    }

    /**
     * 进行单位转换
     *
     * @param cellValue   待进行单位转换的值
     * @param unitConvert 转换单位
     * @return 单位转换后的值
     */
    private String getValueAfterUnitConvert(String cellValue, BigDecimal unitConvert) {
        if (unitConvert == null) {
            return cellValue;
        }
        try {
            BigDecimal bigDecimal = new BigDecimal(cellValue);
            return String.valueOf(bigDecimal.multiply(unitConvert));
        } catch (Exception e) {
            return cellValue;
        }
    }

    /**
     * 根据正则表达式提取内容
     *
     * @param text      文本
     * @param fieldRule 正则表达式
     * @return 内容
     */
    private String getValueByPattern(String text, String fieldRule) {
        if (StrUtil.isBlank(text) || StrUtil.isBlank(fieldRule)) {
            return text;
        }
        Pattern pattern = Pattern.compile(fieldRule);
        Matcher matcher = pattern.matcher(text);
        try {
            while (matcher.find()) {
                return matcher.group(1);
            }
        } catch (Exception e) {
            return null;
        }
        return null;
    }

    private EmailFundNavDTO buildEmailFundNavDTO(Map<String, String> excelFieldValueMap, Map<String, String> textFieldValueMap) {
        EmailFundNavDTO fundNavDTO = new EmailFundNavDTO();
        String registerNumber = MapUtil.isNotEmpty(excelFieldValueMap) && StrUtil.isNotBlank(excelFieldValueMap.get(EmailFieldConst.REGISTER_NUMBER))
                ? excelFieldValueMap.get(EmailFieldConst.REGISTER_NUMBER) : MapUtil.isNotEmpty(textFieldValueMap) ? textFieldValueMap.get(EmailFieldConst.REGISTER_NUMBER) : null;
        String fundName = MapUtil.isNotEmpty(excelFieldValueMap) && StrUtil.isNotBlank(excelFieldValueMap.get(EmailFieldConst.FUND_NAME))
                ? excelFieldValueMap.get(EmailFieldConst.FUND_NAME) : MapUtil.isNotEmpty(textFieldValueMap) ? textFieldValueMap.get(EmailFieldConst.FUND_NAME) : null;
        String priceDate = MapUtil.isNotEmpty(excelFieldValueMap) && StrUtil.isNotBlank(excelFieldValueMap.get(EmailFieldConst.PRICE_DATE))
                ? excelFieldValueMap.get(EmailFieldConst.PRICE_DATE) : MapUtil.isNotEmpty(textFieldValueMap) ? textFieldValueMap.get(EmailFieldConst.PRICE_DATE) : null;
        //错误的日期格式也要保留下来，后续展示需要，这里处理的话会变为null,需要注释掉
        //        priceDate = DateUtils.stringToDate(priceDate);
        String nav = MapUtil.isNotEmpty(excelFieldValueMap) && StrUtil.isNotBlank(excelFieldValueMap.get(EmailFieldConst.NAV))
                ? excelFieldValueMap.get(EmailFieldConst.NAV) : MapUtil.isNotEmpty(textFieldValueMap) ? textFieldValueMap.get(EmailFieldConst.NAV) : null;
        String cumulativeNavWithdrawal = MapUtil.isNotEmpty(excelFieldValueMap) && StrUtil.isNotBlank(excelFieldValueMap.get(EmailFieldConst.CUMULATIVE_NAV_WITHDRAWAL))
                ? excelFieldValueMap.get(EmailFieldConst.CUMULATIVE_NAV_WITHDRAWAL) : MapUtil.isNotEmpty(textFieldValueMap) ? textFieldValueMap.get(EmailFieldConst.CUMULATIVE_NAV_WITHDRAWAL) : null;
        String assetShare = MapUtil.isNotEmpty(excelFieldValueMap) && StrUtil.isNotBlank(excelFieldValueMap.get(EmailFieldConst.ASSET_SHARE))
                ? excelFieldValueMap.get(EmailFieldConst.ASSET_SHARE) : MapUtil.isNotEmpty(textFieldValueMap) ? textFieldValueMap.get(EmailFieldConst.ASSET_SHARE) : null;
        String assetNet = MapUtil.isNotEmpty(excelFieldValueMap) && StrUtil.isNotBlank(excelFieldValueMap.get(EmailFieldConst.ASSET_NET))
                ? excelFieldValueMap.get(EmailFieldConst.ASSET_NET) : MapUtil.isNotEmpty(textFieldValueMap) ? textFieldValueMap.get(EmailFieldConst.ASSET_NET) : null;

        fundNavDTO.setRegisterNumber(registerNumber);
        fundNavDTO.setFundName(fundName);
        fundNavDTO.setPriceDate(priceDate);
        fundNavDTO.setNav(nav);
        fundNavDTO.setCumulativeNavWithdrawal(cumulativeNavWithdrawal);
        fundNavDTO.setAssetShare(assetShare);
        fundNavDTO.setAssetNet(assetNet);
        return fundNavDTO;
    }

    public List<EmailFileContentDTO> getRealFilePath(String filePath, String content) {
        List<EmailFileContentDTO> emailFileContentDTOList = CollUtil.newArrayList();
        if (StrUtil.isBlank(filePath)) {
            return emailFileContentDTOList;
        }
        if (ExcelUtil.isExcel(filePath)) {
            emailFileContentDTOList.add(new EmailFileContentDTO(filePath, null));
        } else if (ExcelUtil.isHTML(filePath)) {
            String excelFilePath = filePath.replace(".html", ".xlsx");
            excelFilePath = ExcelUtil.contentConvertToExcel(content, excelFilePath);
            emailFileContentDTOList.add(new EmailFileContentDTO(excelFilePath, getTextFromHtml(content)));
        } else if (ExcelUtil.isPdf(filePath)) {
            String excelFilePath = filePath.replace(".pdf", ".xlsx").replace(".PDF", ".xlsx");
            excelFilePath = ExcelUtil.pdfConvertToExcel(filePath, excelFilePath);
            emailFileContentDTOList.add(new EmailFileContentDTO(excelFilePath, null));
        } else if (ExcelUtil.isZip(filePath)) {
            String destPath = filePath.replaceAll(".zip", "").replaceAll(".ZIP", "");
            List<String> dir = ExcelUtil.extractCompressedFiles(filePath, destPath);
            for (String zipFilePath : dir) {
                File file = new File(zipFilePath);
                if (file.isDirectory()) {
                    for (String navFilePath : Objects.requireNonNull(file.list())) {
                        Optional.ofNullable(getRealFilePath(navFilePath, null)).ifPresent(emailFileContentDTOList::addAll);
                    }
                } else {
                    Optional.ofNullable(getRealFilePath(zipFilePath, null)).ifPresent(emailFileContentDTOList::addAll);
                }
            }
        }
        return emailFileContentDTOList;
    }

    /**
     * 获取html所有文本内容
     *
     * @param htmlContent 内容(html格式)
     * @return 文本内容
     */
    private String getTextFromHtml(String htmlContent) {
        if (StrUtil.isBlank(htmlContent)) {
            return null;
        }
        Document doc = Jsoup.parse(htmlContent);
        if (doc == null) {
            return htmlContent;
        }
        Elements allElements = doc.getAllElements();
        String textContent = allElements.stream().map(Element::ownText).collect(Collectors.joining("\n"));

        return textContent.trim();
    }

    /**
     * 将数据列转成数字型索引
     *
     * @param columnName 数据列名：如A,B,....
     * @return 数据列数字型索引
     */
    public static int columnLetterToIndex(String columnName) {
        int columnIndex = 0;
        int columnLength = columnName.length();
        // Excel列的基础是26
        int base = 26;
        for (int i = 0; i < columnLength; i++) {
            char letter = columnName.charAt(i);
            // 计算字母的位置（A=0, B=1, ..., Z=25）
            int position = letter - 'A';
            columnIndex += (int) (position * Math.pow(base, columnLength - i - 1));
        }
        return columnIndex;
    }
}