# WMRevenueManualImportReservationStatisticsHandleService.java

    package cloud.wconnect.rateshop.service.weeklymonthlyrevenue.reservationstatistics;

    import cloud.wconnect.commonlib.cons.*;
    import cloud.wconnect.commonlib.exception.BadRequestException;
    import cloud.wconnect.commonlib.exception.InternalServerErrorException;
    import cloud.wconnect.commonlib.exception.ParseReportException;
    import cloud.wconnect.commonlib.pojo.dto.PropertyDTO;
    import cloud.wconnect.commonlib.util.*;
    import cloud.wconnect.rateshop.cons.S3BucketFunctionCons;
    import cloud.wconnect.rateshop.cons.weeklymonthlyrevenue.MonthlyRevenueReportCategoryEnum;
    import cloud.wconnect.rateshop.pojo.bo.weeklymonthlyrevenue.WMRevenueReservationStatisticsBO;
    import cloud.wconnect.rateshop.pojo.dto.weeklymonthlyrevenue.WeeklyMonthlyRevenueImportReportDTO;
    import cloud.wconnect.rateshop.service.AmazonS3Service;
    import cloud.wconnect.rateshop.service.weeklymonthlyrevenue.IWMRevenueManualImportReportHandleService;
    import cloud.wconnect.rateshop.service.weeklymonthlyrevenue.IWeeklyMonthlyRevenueImportLogService;
    import cloud.wconnect.rateshop.service.weeklymonthlyrevenue.IWeeklyMonthlyRevenueReportService;
    import com.baomidou.dynamic.datasource.annotation.DS;
    import com.google.common.collect.Lists;
    import jakarta.annotation.Nullable;
    import jakarta.annotation.Resource;
    import lombok.extern.slf4j.Slf4j;
    import org.apache.commons.lang3.StringUtils;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.ss.usermodel.WorkbookFactory;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.stereotype.Component;
    import org.springframework.web.multipart.MultipartFile;

    import java.io.IOException;
    import java.io.InputStream;
    import java.math.BigDecimal;
    import java.text.MessageFormat;
    import java.time.LocalDate;
    import java.time.format.DateTimeFormatter;
    import java.time.format.DateTimeParseException;
    import java.util.List;
    import java.util.Locale;
    import java.util.Objects;

    /**
     * 手动导入Reservation Statistics报告处理类
     *
     * @author lichen
     * @date 2025/11/4
     */
    @Slf4j
    @Component
    @DS("rate_shop")
    public class WMRevenueManualImportReservationStatisticsHandleService implements IWMRevenueManualImportReportHandleService {

        @Resource
        private IWeeklyMonthlyRevenueReportService reportService;
        @Resource
        private AmazonS3Service amazonS3Service;
        @Autowired
        private I18nMessageUtils i18nMessageUtils;
        @Autowired
        private IWeeklyMonthlyRevenueImportLogService importLogService;
        @Value("${aws-s3.third-party.key.weekly-monthly-revenue.reservation-statistics}")
        private String s3FilePath;
        @Value("${file-max-size.import-report.weekly-monthly-revenue}")
        private long fileMaxSize;

        /**
         * 根据来源和分类判断是否支持此报告
         *
         * @param reportType 报告分类
         * @return boolean
         * @author lichen
         * @date 2025/11/4
         */
        @Override
        public boolean supportsByReportType(int reportType) {
            return reportType == MonthlyRevenueReportCategoryEnum.RESERVATION_STATISTICS.getCategory();
        }

        /**
         * 校验文件内容是否合法
         *
         * @param propertyDTO 资产
         * @param source      来源
         * @param file        文件
         * @author lichen
         * @date 2025/11/4
         */
        @Override
        public void checkFile(PropertyDTO propertyDTO, String source, MultipartFile file) {
            if (SystemPlatformEnum.OPERA.getDesc().equals(source)) {
                checkFileForOpera(file);
            } else if (SystemPlatformEnum.OTHER.getDesc().equals(source)) {
                checkFileForOther(file);
            } else {
                checkFileForShiji(file);
            }
        }

        /**
         * 校验日期参数，包括：versionDate，startDate，endDate
         *
         * @param propertyDTO 资产
         * @param reportDTO   报告
         * @author lichen
         * @date 2025/11/4
         */
        @Override
        public void checkDate(PropertyDTO propertyDTO, String source, WeeklyMonthlyRevenueImportReportDTO reportDTO) {
            IWMRevenueManualImportReportHandleService.checkDateForHistory(propertyDTO, reportDTO, i18nMessageUtils);
        }

        /**
         * 处理数据
         *
         * @param propertyDTO 资产
         * @param file        文件
         * @param dateRange   报告数据日期范围(yyyyMMdd)
         * @param category    0:全部 1:历史 2:未来
         * @param versionDate 版本日期（yyyyMMdd）
         * @param operatorId  操作人 id
         * @param source      来源
         * @param importLogId 导入日志id
         * @author lichen
         * @date 2025/11/4
         */
        @Override
        public void handleData(PropertyDTO propertyDTO, MultipartFile file, List<Long> dateRange,
                               int category, @Nullable Long versionDate, int operatorId,
                               String source, @Nullable Long importLogId) {
            // 获取报告 S3 Key
            String s3Key = propertyDTO.getTenantSymbol() + "/" + propertyDTO.getAbbr() + "/" + s3FilePath
                    + UploadUtils.getRandomFileName(file.getOriginalFilename());

            boolean importResult = true;
            try (InputStream in = file.getInputStream()) {
                // 上传报告到 S3，用于导入记录下载报告
                amazonS3Service.uploadFile(S3BucketFunctionCons.WEEKLY_MONTHLY_REVENUE_IMPORT_RECORD, file, s3Key);

                // 解析报告
                List<WMRevenueReservationStatisticsBO> boList = reportService.parseReservationStatisticsReport(propertyDTO, in, source, dateRange);
                // 处理报告
                reportService.handleReservationStatistics(propertyDTO, boList, operatorId);
            } catch (Exception e) {
                importResult = false;
                throw new ParseReportException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(),
                        "Handle import Weekly Monthly Revenue Reservation Statistics report error, source=" + source, e);
            } finally {
                if (importResult) {
                    if (Objects.nonNull(importLogId)) {
                        //解决异常
                        importLogService.manualSolveSuccess(importLogId, operatorId, s3Key);
                    } else {
                        //新增导入记录
                        importLogService.save(propertyDTO, category, versionDate,
                                dateRange, s3Key, ReportImportResultCons.IMPORT_RESULT_SUCCESS,
                                MonthlyRevenueReportCategoryEnum.RESERVATION_STATISTICS.getCategory(), operatorId);
                    }
                }
            }

        }

        /**
         * 校验文件内容
         *
         * @param file 文件
         * @author lichen
         * @date 2025/11/1
         */
        private void checkFileForOpera(MultipartFile file) {
            // 限制文件大小
            FileCheckUtils.checkFileSize(file, fileMaxSize);
            // 校验文件格式
            UploadUtils.checkFileFormat(file, Lists.newArrayList("xml"));
            // 校验文件内容
            try (InputStream inputStream = file.getInputStream()) {
                FileCheckUtils.checkReservationStatisticsFileForRevenue(inputStream);
            } catch (BadRequestException e) {
                throw e;
            } catch (Exception e) {
                throw new InternalServerErrorException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(),
                        "Check Weekly Monthly Revenue Reservation statistics Opera report error", e);
            }
        }

        /**
         * 校验文件内容
         *
         * @param file 文件
         * @author lichen
         * @date 2025/11/1
         */
        private void checkFileForOther(MultipartFile file) {
            // 限制文件大小
            FileCheckUtils.checkFileSize(file, fileMaxSize);
            // 校验文件格式
            UploadUtils.checkFileFormat(file, Lists.newArrayList("xls", "xlsx"));

            try (InputStream in = file.getInputStream();
                 Workbook workbook = WorkbookFactory.create(in)) {
                Sheet sheet = workbook.getSheetAt(0);
                // 获取最大行数
                int lastRowNum = sheet.getLastRowNum();
                // 若只包含标题，直接返回
                if (lastRowNum < 1) {
                    return;
                }

                // 提取标题
                List<String> titleList = Lists.newArrayList();
                Row titleRow = sheet.getRow(0);
                for (int i = 0; i <= 6; i++) {
                    titleList.add(CellUtils.convertCellStr(titleRow.getCell(i)));
                }

                for (int i = 1; i <= lastRowNum; i++) {
                    Row row = sheet.getRow(i);

                    // 校验 Date
                    String dateStr = CellUtils.convertCellStr(row.getCell(0));
                    if (StringUtils.isBlank(dateStr)) {
                        throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY,
                                i18nMessageUtils.getErrorParamMapNotNeedTranslate(titleList.get(0)),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The Date cannot be blank");
                    } else {
                        try {
                            DateTimeFormatter formatter = DateTimeFormatter.ofPattern(BaseConstant.DATE_FORMAT_YYYY_MM_DD, Locale.ENGLISH);
                            LocalDate.parse(dateStr, formatter);
                        } catch (DateTimeParseException e) {
                            throw new BadRequestException(CommonErrorI18nConstant.FORMAT_INCORRECT,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(titleList.get(0), BaseConstant.DATE_FORMAT_YYYY_MM_DD),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The Date format is incorrect, Date = " + dateStr);
                        }
                    }
                    // Transient Sold
                    String transientSoldStr = CellUtils.convertCellStr(row.getCell(1));
                    if (StringUtils.isNotBlank(transientSoldStr)) {
                        checkFileIntegerParam(titleList.get(1), 1000000, transientSoldStr);
                    }
                    // Transient Revenue
                    String transientRevenueStr = CellUtils.convertCellStr(row.getCell(2));
                    if (StringUtils.isNotBlank(transientRevenueStr)) {
                        checkFileBigDecimalParam(titleList.get(2), 10, transientRevenueStr);
                    }
                    // Group Sold
                    String groupSoldStr = CellUtils.convertCellStr(row.getCell(3));
                    if (StringUtils.isNotBlank(groupSoldStr)) {
                        checkFileIntegerParam(titleList.get(3), 1000000, groupSoldStr);
                    }
                    // Group Revenue
                    String groupRevenueStr = CellUtils.convertCellStr(row.getCell(4));
                    if (StringUtils.isNotBlank(groupRevenueStr)) {
                        checkFileBigDecimalParam(titleList.get(4), 10, groupRevenueStr);
                    }
                    // Contract Sold
                    String contractSoldStr = CellUtils.convertCellStr(row.getCell(5));
                    if (StringUtils.isNotBlank(contractSoldStr)) {
                        checkFileIntegerParam(titleList.get(5), 1000000, contractSoldStr);
                    }
                    // Contract Revenue
                    String contractRevenueStr = CellUtils.convertCellStr(row.getCell(6));
                    if (StringUtils.isNotBlank(contractRevenueStr)) {
                        checkFileBigDecimalParam(titleList.get(6), 10, contractRevenueStr);
                    }
                }
            } catch (IOException e) {
                throw new InternalServerErrorException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(),
                        "Handle check Weekly Monthly Revenue Reservation Statistics Other report error.", e);
            }
        }

        /**
         * Integer 字段校验
         *
         * @param title 标题
         * @param max   最大值
         * @param param 参数
         * @author zc
         * @date 2024/8/22
         */
        private void checkFileIntegerParam(String title, int max, String param) {
            try {
                int value = Integer.parseInt(param);
                if (value < 0) {
                    throw new BadRequestException(CommonErrorI18nConstant.CANNOT_LESS_THAN_ZERO,
                            i18nMessageUtils.getErrorParamMapNotNeedTranslate(title),
                            BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                            MessageFormat.format("The {0} cannot be less than 0, {0} = {1}", title, param));
                }
                if (value > max) {
                    throw new BadRequestException(CommonErrorI18nConstant.NUMBER_OVERLENGTH,
                            i18nMessageUtils.getErrorParamMapNotNeedTranslate(title, String.valueOf(max).length() - 1, 0),
                            BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                            MessageFormat.format("The {0} cannot be greater than {1}, {0} = {2}", title, max, param));
                }
            } catch (NumberFormatException e) {
                throw new BadRequestException(CommonErrorI18nConstant.MUST_INTEGER,
                        i18nMessageUtils.getErrorParamMapNotNeedTranslate(title),
                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                        MessageFormat.format("The {0} must integer, {0} = {1}", title, param));
            }
        }

        /**
         * BigDecimal 字段校验
         *
         * @param title        标题
         * @param integerLimit 整数位限制
         * @param param        参数
         * @author zc
         * @date 2024/8/22
         */
        private void checkFileBigDecimalParam(String title, int integerLimit, String param) {
            BigDecimal value;
            try {
                value = new BigDecimal(param);
            } catch (NumberFormatException e) {
                throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                        i18nMessageUtils.getErrorParamMapNotNeedTranslate(title),
                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                        MessageFormat.format("The {0} format is incorrect, {0} = {1}", title, param));
            }
            // 校验整数位是否超过限制
            int intLength = value.abs().toBigInteger().toString().length();
            if (intLength > integerLimit) {
                throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                        i18nMessageUtils.getErrorParamMapNotNeedTranslate(title, integerLimit),
                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                        MessageFormat.format("The integer of {0} exceeds the limit, {0} = {1}", title, param));
            }
        }

        /**
         * 校验文件内容是否合法
         *
         * @param file 文件
         * @author lichen
         * @date 2025/11/4
         */
        private void checkFileForShiji(MultipartFile file) {
            // 限制文件大小
            FileCheckUtils.checkFileSize(file, fileMaxSize);
            // 校验文件格式
            UploadUtils.checkFileFormat(file, Lists.newArrayList("csv"));
            // 校验文件内容
            try (InputStream inputStream = file.getInputStream()) {
                ShijiFileCheckUtils.checkReservationStatistics(inputStream);
            } catch (BadRequestException e) {
                throw e;
            } catch (Exception e) {
                throw new InternalServerErrorException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(),
                        "Check Weekly Monthly Revenue Reservation statistics Shiji report error", e);
            }
        }

    }