# ParseCaseCommonService.java

    package cloud.wconnect.finance.service.chargeback.importcase;

    import cloud.wconnect.commonlib.cons.BaseConstant;
    import cloud.wconnect.commonlib.cons.BaseErrorCodeEnum;
    import cloud.wconnect.commonlib.cons.CommonErrorI18nConstant;
    import cloud.wconnect.commonlib.exception.BadRequestException;
    import cloud.wconnect.commonlib.exception.ParseReportException;
    import cloud.wconnect.commonlib.pojo.dto.PropertyDTO;
    import cloud.wconnect.commonlib.util.CellUtils;
    import cloud.wconnect.commonlib.util.I18nMessageUtils;
    import cloud.wconnect.finance.entity.chargeback.CbCaseBank;
    import cloud.wconnect.finance.pojo.bo.chargeback.CaseListBO;
    import cloud.wconnect.finance.service.chargeback.ICbCaseBankService;
    import com.baomidou.dynamic.datasource.annotation.DS;
    import com.google.common.collect.Lists;
    import org.apache.commons.collections.CollectionUtils;
    import org.apache.commons.lang3.StringUtils;
    import org.apache.poi.ss.usermodel.*;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;

    import jakarta.annotation.Resource;
    import java.io.IOException;
    import java.io.InputStream;
    import java.math.BigDecimal;
    import java.time.LocalDate;
    import java.time.format.DateTimeFormatter;
    import java.util.*;
    import java.util.stream.Collectors;

    /**
     * @author: yyt
     * 2024-08-09
     */
    @Service
    @DS("finance")
    public class ParseCaseCommonService implements IParseCaseCommonService {

        @Resource
        private ICbCaseBankService cbCaseBankService;
        @Autowired
        private I18nMessageUtils i18nMessageUtils;

        /**
         * 导入Case时的最大数量限制
         */
        private static final int IMPORT_MAX_COUNT = 50;

        /**
         * 检查文件
         *
         * @param propertyDTO 资产
         * @param in          文件
         * @author yyt
         * @date 2024/8/9
         **/
        public void checkFile(PropertyDTO propertyDTO, InputStream in) throws IOException {
            Map<String, Integer> bankIdByName = cbCaseBankService.list(propertyDTO.getId()).stream()
                    .collect(Collectors.toMap(CbCaseBank::getName, CbCaseBank::getId));
            try(Workbook workbook = WorkbookFactory.create(in)){
                Row titleRow = workbook.getSheetAt(0).getRow(0);
                if (Objects.isNull(titleRow)) {
                    throw new BadRequestException(CommonErrorI18nConstant.IMPORT_REPORT_NOT_REQUIRED,
                            BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                            "The imported report is not the one required for the current functionality");
                }

                String caseNumberTitle = CellUtils.convertCellStr(titleRow.getCell(0));
                String issueDateTitle = CellUtils.convertCellStr(titleRow.getCell(1));
                String dueDateTitle = CellUtils.convertCellStr(titleRow.getCell(2));
                String cardNumberTitle = CellUtils.convertCellStr(titleRow.getCell(3));
                String cardTypeTitle = CellUtils.convertCellStr(titleRow.getCell(4));
                String transactionDateTitle = CellUtils.convertCellStr(titleRow.getCell(5));
                String reasonTitle = CellUtils.convertCellStr(titleRow.getCell(6));
                String transactionAmountTitle = CellUtils.convertCellStr(titleRow.getCell(7));
                String disputeAmountTitle = CellUtils.convertCellStr(titleRow.getCell(8));
                DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(BaseConstant.DATE_FORMAT_YYYY_MM_DD);

                int caseCount = 0;

                int numberOfSheets = workbook.getNumberOfSheets();
                for (int i = 0; i < numberOfSheets; i++) {
                    Sheet sheet = workbook.getSheetAt(i);
                    //  sheet名称格式：BA（银行平台）
                    String sheetName = sheet.getSheetName();
                    Integer vendor = bankIdByName.get(sheetName);
                    if (vendor == null) {
                        throw new BadRequestException(CommonErrorI18nConstant.DATA_NOT_EXIST,
                                i18nMessageUtils.getErrorParamMapNotNeedTranslate(sheetName),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "the vendor is not found;vendor:" + sheetName);
                    }

                    for (int j = 1; j <= sheet.getLastRowNum(); j++) {
                        Row row = sheet.getRow(j);
                        if (Objects.isNull(row)) {
                            continue;
                        }

                        Cell caseNumberCell = row.getCell(0);
                        if (Objects.isNull(caseNumberCell) || StringUtils.isBlank(caseNumberCell.toString())) {
                            throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(caseNumberTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The case number cannot be empty");
                        } else {
                            // 最多 30 个字
                            if (CellUtils.convertCellStr(caseNumberCell).length() > 30) {
                                throw new BadRequestException(CommonErrorI18nConstant.OVERLENGTH,
                                        i18nMessageUtils.getErrorParamMapNotNeedTranslate(caseNumberTitle, 30),
                                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                        "The case number cannot be more than 30 characters");
                            }
                        }

                        Cell issueDateCell = row.getCell(1);
                        if (Objects.isNull(issueDateCell) || StringUtils.isBlank(issueDateCell.toString())) {
                            throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(issueDateTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The issue date cannot be empty");
                        } else {
                            try {
                                LocalDate.parse(CellUtils.convertCellStr(issueDateCell, BaseConstant.DATE_FORMAT_YYYY_MM_DD), dateTimeFormatter);
                            } catch (Exception e) {
                                throw new BadRequestException(CommonErrorI18nConstant.FORMAT_INCORRECT,
                                        i18nMessageUtils.getErrorParamMapNotNeedTranslate(issueDateTitle, BaseConstant.DATE_FORMAT_YYYY_MM_DD),
                                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                        "The issue date format error");
                            }
                        }

                        Cell dueDateCell = row.getCell(2);
                        if (Objects.isNull(dueDateCell) || StringUtils.isBlank(dueDateCell.toString())) {
                            throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(dueDateTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The due date cannot be empty");
                        } else {
                            try {
                                LocalDate.parse(CellUtils.convertCellStr(dueDateCell, BaseConstant.DATE_FORMAT_YYYY_MM_DD), dateTimeFormatter);
                            } catch (Exception e) {
                                throw new BadRequestException(CommonErrorI18nConstant.FORMAT_INCORRECT,
                                        i18nMessageUtils.getErrorParamMapNotNeedTranslate(dueDateTitle, BaseConstant.DATE_FORMAT_YYYY_MM_DD),
                                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                        "The due date format error");
                            }
                        }

                        Cell cardNumberCell = row.getCell(3);
                        if (Objects.isNull(cardNumberCell) || StringUtils.isBlank(cardNumberCell.toString())) {
                            throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(cardNumberTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The card number cannot be empty");
                        } else {
                            if (CellUtils.convertCellStr(cardNumberCell).length() > 30) {
                                throw new BadRequestException(CommonErrorI18nConstant.OVERLENGTH,
                                        i18nMessageUtils.getErrorParamMapNotNeedTranslate(cardNumberTitle, 30),
                                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                        "The card number cannot be more than 30 characters");
                            }
                        }

                        Cell cardTypeCell = row.getCell(4);
                        if (Objects.isNull(cardTypeCell) || StringUtils.isBlank(cardTypeCell.toString())) {
                            throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(cardTypeTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The card type cannot be empty");
                        } else {
                            if (CellUtils.convertCellStr(cardTypeCell).length() > 20) {
                                throw new BadRequestException(CommonErrorI18nConstant.OVERLENGTH,
                                        i18nMessageUtils.getErrorParamMapNotNeedTranslate(cardTypeTitle, 20),
                                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                        "The card type cannot be more than 20 characters");
                            }
                        }

                        Cell transactionDateCell = row.getCell(5);
                        if (Objects.isNull(transactionDateCell) || StringUtils.isBlank(transactionDateCell.toString())) {
                            throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(transactionDateTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The transaction date cannot be empty");
                        } else {
                            try {
                                LocalDate.parse(CellUtils.convertCellStr(transactionDateCell, BaseConstant.DATE_FORMAT_YYYY_MM_DD), dateTimeFormatter);
                            } catch (Exception e) {
                                throw new BadRequestException(CommonErrorI18nConstant.FORMAT_INCORRECT,
                                        i18nMessageUtils.getErrorParamMapNotNeedTranslate(transactionDateCell, BaseConstant.DATE_FORMAT_YYYY_MM_DD),
                                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                        "The transaction date format error");
                            }
                        }

                        Cell reasonCell = row.getCell(6);
                        if (Objects.isNull(reasonCell) || StringUtils.isBlank(reasonCell.toString())) {
                            throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(reasonTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The reason cannot be empty");
                        } else {
                            if (CellUtils.convertCellStr(reasonCell).length() > 500) {
                                throw new BadRequestException(CommonErrorI18nConstant.OVERLENGTH,
                                        i18nMessageUtils.getErrorParamMapNotNeedTranslate(reasonTitle, 500),
                                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                        "The reason cannot be more than 500 characters");
                            }
                        }

                        Cell transactionAmountCell = row.getCell(7);
                        if (Objects.isNull(transactionAmountCell) || StringUtils.isBlank(transactionAmountCell.toString())) {
                            throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(transactionAmountTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The transaction amount cannot be empty");
                        } else {
                            BigDecimal transactionAmount;
                            try {
                                transactionAmount = new BigDecimal(CellUtils.convertCellStr(transactionAmountCell));
                            } catch (Exception e) {
                                throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                        i18nMessageUtils.getErrorParamMapNotNeedTranslate(transactionAmountTitle),
                                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                        "The transaction amount must be a number");
                            }
                            // 整数 8 位
                            if (transactionAmount.toBigInteger().toString().length() > 8) {
                                throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                        i18nMessageUtils.getErrorParamMapNotNeedTranslate(transactionAmountTitle, 8),
                                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                        "The transaction amount exceed the limit");
                            }
                        }

                        Cell disputeAmountCell = row.getCell(8);
                        if (Objects.isNull(disputeAmountCell) || StringUtils.isBlank(disputeAmountCell.toString())) {
                            throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(disputeAmountTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The dispute amount cannot be empty");
                        } else {
                            BigDecimal disputeAmount;
                            try {
                                disputeAmount = new BigDecimal(CellUtils.convertCellStr(disputeAmountCell));
                            } catch (Exception e) {
                                throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                        i18nMessageUtils.getErrorParamMapNotNeedTranslate(disputeAmountTitle),
                                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                        "The dispute amount must be a number");
                            }
                            // 整数 8 位
                            if (disputeAmount.toBigInteger().toString().length() > 8) {
                                throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                        i18nMessageUtils.getErrorParamMapNotNeedTranslate(disputeAmountTitle, 8),
                                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                        "The dispute amount exceed the limit");
                            }
                        }
                        caseCount++;
                    }
                }

                // 验证 Case 数量
                if (caseCount > IMPORT_MAX_COUNT) {
                    throw new BadRequestException(CommonErrorI18nConstant.HANDLE_DATA_LIMIT,
                            i18nMessageUtils.getErrorParamMapNotNeedTranslate(IMPORT_MAX_COUNT),
                            BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                            "the case count can not more than 50.");
                }
            }catch (Exception e){
                throw new ParseReportException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(), "check file failed.", e);
            }
        }

        /**
         * 解析文件
         *
         * @param propertyDTO 资产
         * @param in          文件
         * @return case
         * @author yyt
         * @date 2024/8/9
         **/
        @Override
        public List<CaseListBO> parseReport(PropertyDTO propertyDTO, InputStream in) throws IOException {
            Map<String, Integer> bankIdByName = cbCaseBankService.list(propertyDTO.getId()).stream()
                    .collect(Collectors.toMap(CbCaseBank::getName, CbCaseBank::getId));

            List<CaseListBO> cases = Lists.newArrayList();
            try(Workbook workbook = WorkbookFactory.create(in)) {
                // 解析excel文件
                int numberOfSheets = workbook.getNumberOfSheets();
                for (int i = 0; i < numberOfSheets; i++) {
                    Sheet sheet = workbook.getSheetAt(i);
                    //  sheet名称格式：BA
                    String sheetName = sheet.getSheetName();
                    Integer vendor = bankIdByName.get(sheetName);
                    for (int num = 1; num <= sheet.getLastRowNum(); num++) {
                        Row row = sheet.getRow(num);
                        if (row == null) {
                            continue;
                        }

                        CaseListBO cs = new CaseListBO();
                        cs.setCaseNumber(CellUtils.convertCellStr(row.getCell(0)));
                        cs.setVendor(vendor);
                        cs.setCardType(CellUtils.convertCellStr(row.getCell(4)));
                        // 设置开始日期
                        cs.setIssueDate(StringUtils.replace(CellUtils.convertCellStr(row.getCell(1), BaseConstant.DATE_FORMAT_YYYY_MM_DD), "-", "/"));
                        // 设置结束日期
                        cs.setDueDate(StringUtils.replace(CellUtils.convertCellStr(row.getCell(2), BaseConstant.DATE_FORMAT_YYYY_MM_DD), "-", "/"));
                        // 设置卡号
                        cs.setCardNumber(CellUtils.convertCellStr(row.getCell(3)));
                        // 设置交易日期
                        cs.setTrxDate(StringUtils.replace(CellUtils.convertCellStr(row.getCell(5), BaseConstant.DATE_FORMAT_YYYY_MM_DD), "-", "/"));
                        // 设置交易金额
                        cs.setTrxAmount(new BigDecimal(CellUtils.convertCellStr(row.getCell(7))));
                        // 设置纠纷金额
                        cs.setDisputeAmount(new BigDecimal(CellUtils.convertCellStr((row.getCell(8)))));
                        cs.setReason(CellUtils.convertCellStr(row.getCell(6)));
                        cs.setAttachments("");
                        cases.add(cs);
                    }
                }

                if (CollectionUtils.isEmpty(cases)) {
                    return Collections.emptyList();
                }

                // 去重 case number、issue Date
                return cases.stream().collect(Collectors.collectingAndThen(Collectors.toCollection(() ->
                        new TreeSet<>(Comparator.comparing(cs -> cs.getCaseNumber() + ":" + cs.getIssueDate()))), ArrayList::new));
            }catch (Exception e){
                throw new ParseReportException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(), "parseReport failed.", e);
            }
        }

    }