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

package com.simuwang.daq.service;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.lang.Pair;
import cn.hutool.core.util.StrUtil;
import com.simuwang.base.common.conts.ApplicationRuleFileConst;
import com.simuwang.base.common.conts.EmailFieldConst;
import com.simuwang.base.common.util.ExcelUtil;
import com.simuwang.base.mapper.EmailTemplateApplicationRuleMapper;
import com.simuwang.base.mapper.EmailTemplateDataRuleMapper;
import com.simuwang.base.mapper.EmailTemplateMappingMapper;
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.TemplateDetailDTO;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.stereotype.Service;

import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Service
public class EmailTemplateService {

    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<TemplateDetailDTO> getTemplateDetail(EmailContentInfoDTO emailContentInfoDTO, String filePath) {
        List<TemplateDetailDTO> templateDetailDTOList = CollUtil.newArrayList();
        String senderEmail = emailContentInfoDTO.getSenderEmail();
        if (StrUtil.isBlank(senderEmail) || StrUtil.isBlank(filePath)) {
            return templateDetailDTOList;
        }
        // 查询邮箱配置的模板Id
        List<EmailTemplateInfoDO> emailTemplateInfoDOList = emailTemplateMappingMapper.queryByEmail(senderEmail);
        List<Integer> templateIdList = emailTemplateInfoDOList.stream().map(EmailTemplateInfoDO::getId).collect(Collectors.toList());
        if (CollUtil.isEmpty(templateIdList)) {
            return templateDetailDTOList;
        }
        Map<Integer, Integer> templateIdDirectionMap = emailTemplateInfoDOList.stream().collect(Collectors.toMap(EmailTemplateInfoDO::getId, EmailTemplateInfoDO::getDirection));
        // 查询模版适用性规则 -> 判断邮件是否满足模板适用性规则
        List<EmailTemplateApplicationRuleDO> templateApplicationRuleDOList = emailTemplateApplicationRuleMapper.queryByTemplateId(templateIdList);
        if (CollUtil.isEmpty(templateApplicationRuleDOList)) {
            return templateDetailDTOList;
        }
        Map<Integer, List<EmailTemplateApplicationRuleDO>> templateIdApplicationRuleMap = templateApplicationRuleDOList.stream().collect(Collectors.groupingBy(EmailTemplateApplicationRuleDO::getTemplateId));
        // 查询模版数据规则
        List<EmailTemplateDataRuleDO> templateDataRuleDOList = emailTemplateDataRuleMapper.queryByTemplateId(templateIdList);
        Map<Integer, List<EmailTemplateDataRuleDO>> templateIdDataRuleMap = templateDataRuleDOList.stream().collect(Collectors.groupingBy(EmailTemplateDataRuleDO::getTemplateId));

        for (Map.Entry<Integer, List<EmailTemplateApplicationRuleDO>> templateIdApplicationRule : templateIdApplicationRuleMap.entrySet()) {
            Integer templateId = templateIdApplicationRule.getKey();
            List<EmailTemplateApplicationRuleDO> applicationRuleDOList = templateIdApplicationRule.getValue();
            // 判断是否满足模板适用性规则
            boolean isMatchTemplate = isMatchTemplate(emailContentInfoDTO.getEmailTitle(), emailContentInfoDTO.getFileName(), filePath, applicationRuleDOList);
            if (!isMatchTemplate) {
                continue;
            }
            TemplateDetailDTO templateDetailDTO = new TemplateDetailDTO();
            templateDetailDTO.setTemplateId(templateId);
            // 模板的数据行方向：默认为行
            Integer directionOrDefault = templateIdDirectionMap.getOrDefault(templateId, 1);
            templateDetailDTO.setDirection(directionOrDefault);
            Map<String, Pair<Integer, Integer>> fieldPositionMap = getFieldPosition(templateIdDataRuleMap.get(templateId));
            templateDetailDTO.setFieldPositionMap(fieldPositionMap);
            templateDetailDTOList.add(templateDetailDTO);
        }
        return templateDetailDTOList;
    }

    private Map<String, Pair<Integer, Integer>> getFieldPosition(List<EmailTemplateDataRuleDO> emailTemplateDataRuleDOList) {
        Map<String, Pair<Integer, Integer>> fieldPositionMap = new HashMap<>();
        for (EmailTemplateDataRuleDO templateDataRuleDO : emailTemplateDataRuleDOList) {
            Integer fieldName = templateDataRuleDO.getFieldName();
            String fieldValue = FILE_TYPE_MAP.get(fieldName);
            int column = columnLetterToIndex(templateDataRuleDO.getColumn());
            Pair<Integer, Integer> pair = new Pair<>(templateDataRuleDO.getRow() - 1, column);
            fieldPositionMap.put(fieldValue, pair);
        }
        return fieldPositionMap;
    }

    private boolean isMatchTemplate(String emailTitle, String fileName, String filePath, List<EmailTemplateApplicationRuleDO> applicationRuleDOList) {
        int notMatchCount = 0;
        for (EmailTemplateApplicationRuleDO applicationRuleDO : applicationRuleDOList) {
            if (!isMathApplicationRule(applicationRuleDO, emailTitle, fileName, filePath)) {
                notMatchCount++;
            }
        }
        return notMatchCount == 0;
    }

    private boolean isMathApplicationRule(EmailTemplateApplicationRuleDO applicationRuleDO, 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)) {
            if (StrUtil.isNotBlank(containKeyword)) {
                isMatch = Arrays.stream(containKeyword.split(",")).allMatch(emailTitle::contains);
            }
            if (StrUtil.isNotBlank(notContainKeyword)) {
                isMatch = Arrays.stream(notContainKeyword.split(",")).noneMatch(emailTitle::contains) && isMatch;
            }
        }
        if (ApplicationRuleFileConst.EMAIL_FILE_NAME_FILE.equals(type)) {
            if (StrUtil.isNotBlank(containKeyword)) {
                isMatch = Arrays.stream(containKeyword.split(",")).allMatch(fileName::contains);
            }
            if (StrUtil.isNotBlank(notContainKeyword)) {
                isMatch = Arrays.stream(notContainKeyword.split(",")).noneMatch(fileName::contains) && isMatch;
            }
        }
        if (ApplicationRuleFileConst.EMAIL_EXCEL_CONTENT_FILE.equals(type)) {
            if (StrUtil.isNotBlank(fileName) && ExcelUtil.isExcel(fileName)) {
                if (applicationRuleDO.getRow() == null || StrUtil.isBlank(applicationRuleDO.getColumn())) {
                    return false;
                }
                Sheet sheet = ExcelUtil.getFirstSheet(filePath);
                if (sheet == null) {
                    return false;
                }
                Row row = sheet.getRow(applicationRuleDO.getRow());
                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;
                }
                if (StrUtil.isNotBlank(containKeyword)) {
                    isMatch = Arrays.stream(containKeyword.split(",")).allMatch(cellValue::contains);
                }
                if (StrUtil.isNotBlank(notContainKeyword)) {
                    isMatch = Arrays.stream(notContainKeyword.split(",")).noneMatch(cellValue::contains) && isMatch;
                }
            }
        }
        return isMatch;
    }

    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;
    }

}