# DbPmsHfParseServiceByOther.java

    package cloud.wconnect.workperformance.service.database.historyforecast;

    import cloud.wconnect.commonlib.cons.BaseConstant;
    import cloud.wconnect.commonlib.cons.BaseErrorCodeEnum;
    import cloud.wconnect.commonlib.cons.CommonErrorI18nConstant;
    import cloud.wconnect.commonlib.cons.SystemPlatformEnum;
    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.workperformance.cons.S3BucketFunctionCons;
    import cloud.wconnect.workperformance.entity.database.DbReportImportLog;
    import cloud.wconnect.workperformance.pojo.bo.database.historyforecast.DbPmsHfBO;
    import cloud.wconnect.workperformance.service.AmazonS3Service;
    import cloud.wconnect.workperformance.service.database.IDbPmsHfForecastService;
    import cloud.wconnect.workperformance.service.database.IDbPmsHfHistoryService;
    import cloud.wconnect.workperformance.service.database.IDbReportImportLogService;
    import com.baomidou.dynamic.datasource.annotation.DS;
    import com.google.common.collect.Lists;
    import jakarta.annotation.Resource;
    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.Value;
    import org.springframework.stereotype.Component;
    import org.springframework.transaction.annotation.Transactional;
    import org.springframework.web.multipart.MultipartFile;

    import java.io.IOException;
    import java.io.InputStream;
    import java.math.BigDecimal;
    import java.math.RoundingMode;
    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;

    /**
     * 解析 Other History and Forecast/Service 报告
     *
     * @author zc
     * @date 2026/1/5
     */
    @Component
    @DS("database")
    public class DbPmsHfParseServiceByOther implements IDbPmsHfParseService {
        @Resource
        private IDbPmsHfHistoryService hfHistoryService;
        @Resource
        private IDbPmsHfForecastService hfForecastService;
        @Resource
        private IDbReportImportLogService importLogService;
        @Resource
        private I18nMessageUtils i18nMessageUtils;
        @Resource
        private AmazonS3Service amazonS3Service;

        @Value("${aws-s3.third-party.key.database.history-and-forecast}")
        private String s3FilePath;
        @Value("${file-max-size.import-report.database}")
        private long fileMaxSize;

        /**
         * 支持的报告来源
         *
         * @param source 报告来源
         * @return boolean
         * @author zc
         * @date 2026/1/5
         */
        @Override
        public boolean supports(String source) {
            return SystemPlatformEnum.OTHER.getDesc().equals(source);
        }

        /**
         * 校验文件内容是否合法
         *
         * @param propertyDTO 资产
         * @param file        文件
         * @author zc
         * @date 2026/1/5
         */
        @Override
        public void checkFile(PropertyDTO propertyDTO, 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 <= 22; 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);
                        }
                    }

                    // Inventory rooms
                    String inventoryRoomsStr = CellUtils.convertCellStr(row.getCell(1));
                    if (StringUtils.isNotBlank(inventoryRoomsStr)) {
                        checkFileIntegerParam(titleList.get(1), 10000, inventoryRoomsStr);
                    }
                    // Sell Inventory rooms
                    String sellInventoryRoomsStr = CellUtils.convertCellStr(row.getCell(2));
                    if (StringUtils.isNotBlank(sellInventoryRoomsStr)) {
                        checkFileIntegerParam(titleList.get(2), 10000, sellInventoryRoomsStr);
                    }
                    // Revenue
                    String revenueStr = CellUtils.convertCellStr(row.getCell(3));
                    if (StringUtils.isNotBlank(revenueStr)) {
                        checkFileBigDecimalParam(titleList.get(3), 10, revenueStr);
                    }
                    // Deduct individual
                    String deductIndividualStr = CellUtils.convertCellStr(row.getCell(4));
                    if (StringUtils.isNotBlank(deductIndividualStr)) {
                        checkFileIntegerParam(titleList.get(4), 10000, deductIndividualStr);
                    }
                    // Deduct individual revenue
                    String deductIndividualRevenueStr = CellUtils.convertCellStr(row.getCell(5));
                    if (StringUtils.isNotBlank(deductIndividualRevenueStr)) {
                        checkFileBigDecimalParam(titleList.get(5), 10, deductIndividualRevenueStr);
                    }
                    // Non deduct individual
                    String nonDeductIndividualStr = CellUtils.convertCellStr(row.getCell(6));
                    if (StringUtils.isNotBlank(nonDeductIndividualStr)) {
                        checkFileIntegerParam(titleList.get(6), 10000, nonDeductIndividualStr);
                    }
                    // Non deduct individual revenue
                    String nonDeductIndividualRevenueStr = CellUtils.convertCellStr(row.getCell(7));
                    if (StringUtils.isNotBlank(nonDeductIndividualRevenueStr)) {
                        checkFileBigDecimalParam(titleList.get(7), 10, nonDeductIndividualRevenueStr);
                    }
                    // Deduct group
                    String deductGroupStr = CellUtils.convertCellStr(row.getCell(8));
                    if (StringUtils.isNotBlank(deductGroupStr)) {
                        checkFileIntegerParam(titleList.get(8), 10000, deductGroupStr);
                    }
                    // Deduct group revenue
                    String deductGroupRevenueStr = CellUtils.convertCellStr(row.getCell(9));
                    if (StringUtils.isNotBlank(deductGroupRevenueStr)) {
                        checkFileBigDecimalParam(titleList.get(9), 10, deductGroupRevenueStr);
                    }
                    // Non deduct group
                    String nonDeductGroupStr = CellUtils.convertCellStr(row.getCell(10));
                    if (StringUtils.isNotBlank(nonDeductGroupStr)) {
                        checkFileIntegerParam(titleList.get(10), 10000, nonDeductGroupStr);
                    }
                    // Non deduct group revenue
                    String nonDeductGroupRevenueStr = CellUtils.convertCellStr(row.getCell(11));
                    if (StringUtils.isNotBlank(nonDeductGroupRevenueStr)) {
                        checkFileBigDecimalParam(titleList.get(11), 10, nonDeductGroupRevenueStr);
                    }
                    // Number of persons
                    String numberOfPersonsStr = CellUtils.convertCellStr(row.getCell(12));
                    if (StringUtils.isNotBlank(numberOfPersonsStr)) {
                        checkFileIntegerParam(titleList.get(12), 10000, numberOfPersonsStr);
                    }
                    // Arrival rooms
                    String arrivalRoomsStr = CellUtils.convertCellStr(row.getCell(13));
                    if (StringUtils.isNotBlank(arrivalRoomsStr)) {
                        checkFileIntegerParam(titleList.get(13), 10000, arrivalRoomsStr);
                    }
                    // Departure rooms
                    String departureRoomsStr = CellUtils.convertCellStr(row.getCell(14));
                    if (StringUtils.isNotBlank(departureRoomsStr)) {
                        checkFileIntegerParam(titleList.get(14), 10000, departureRoomsStr);
                    }
                    // Complimentary rooms
                    String compRoomsStr = CellUtils.convertCellStr(row.getCell(15));
                    if (StringUtils.isNotBlank(compRoomsStr)) {
                        checkFileIntegerParam(titleList.get(15), 10000, compRoomsStr);
                    }
                    // House Use rooms
                    String houseUseRoomsStr = CellUtils.convertCellStr(row.getCell(16));
                    if (StringUtils.isNotBlank(houseUseRoomsStr)) {
                        checkFileIntegerParam(titleList.get(16), 10000, houseUseRoomsStr);
                    }
                    // Day Use rooms
                    String dayUseRoomsStr = CellUtils.convertCellStr(row.getCell(17));
                    if (StringUtils.isNotBlank(dayUseRoomsStr)) {
                        checkFileIntegerParam(titleList.get(17), 10000, dayUseRoomsStr);
                    }
                    // No show rooms
                    String noShowRoomsStr = CellUtils.convertCellStr(row.getCell(18));
                    if (StringUtils.isNotBlank(noShowRoomsStr)) {
                        checkFileIntegerParam(titleList.get(18), 10000, noShowRoomsStr);
                    }
                    // Out of order rooms
                    String outOfOrderRoomsStr = CellUtils.convertCellStr(row.getCell(19));
                    if (StringUtils.isNotBlank(outOfOrderRoomsStr)) {
                        checkFileIntegerParam(titleList.get(19), 10000, outOfOrderRoomsStr);
                    }
                    // Occ rooms
                    String occRoomsStr = CellUtils.convertCellStr(row.getCell(20));
                    if (StringUtils.isNotBlank(occRoomsStr)) {
                        checkFileIntegerParam(titleList.get(20), 10000, occRoomsStr);
                    }
                    // Average room rate
                    String avgRoomRateStr = CellUtils.convertCellStr(row.getCell(21));
                    if (StringUtils.isNotBlank(avgRoomRateStr)) {
                        checkFileBigDecimalParam(titleList.get(21), 10, avgRoomRateStr);
                    }
                    // Occupancy
                    String occupancyStr = CellUtils.convertCellStr(row.getCell(22));
                    if (StringUtils.isNotBlank(occupancyStr)) {
                        checkFileBigDecimalParam(titleList.get(22), 10, occupancyStr);
                    }
                }
            } catch (IOException e) {
                throw new InternalServerErrorException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(),
                        "Handle check Database History and forecast Other report error.", e);
            }
        }

        /**
         * Integer 字段校验
         *
         * @param title 标题
         * @param max   最大值
         * @param param 参数
         * @author zc
         * @date 2026/1/5
         */
        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 2026/1/5
         */
        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 propertyDTO 资产
         * @param file        文件
         * @param importLog   日志
         * @param operatorId  操作人 id
         * @author zc
         * @date 2026/1/5
         */
        @Override
        @Transactional(rollbackFor = Exception.class)
        public void handleData(PropertyDTO propertyDTO, MultipartFile file, DbReportImportLog importLog, int operatorId) {
            // 处理是否成功
            boolean handleResult = true;
            // 获取报告 S3 Key
            String s3Key = propertyDTO.getTenantSymbol() + "/" + propertyDTO.getAbbr() + "/" + s3FilePath
                    + UploadUtils.getRandomFileName(file.getOriginalFilename());

            try (InputStream in = file.getInputStream()) {
                // 上传报告到 S3，用于导入记录下载报告
                amazonS3Service.uploadFile(S3BucketFunctionCons.DATABASE_IMPORT_RECORD, file, s3Key);
                // 解析报告
                List<DbPmsHfBO> boList = parse(propertyDTO, in, List.of(importLog.getStartDate(), importLog.getEndDate()));
                // 保存数据
                hfHistoryService.handle(propertyDTO, boList, importLog.getBusinessDate(), operatorId);
                hfForecastService.handle(propertyDTO, boList, importLog.getBusinessDate(), operatorId);
            } catch (Exception e) {
                handleResult = false;
                throw new InternalServerErrorException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(),
                        "Handle import Database History and Forecast Other report error.", e);
            } finally {
                importLogService.update(importLog.getId(), operatorId, s3Key, handleResult);
            }
        }

        /**
         * 解析报告
         *
         * @param propertyDTO 资产
         * @param in          报告文件流
         * @param dateRange   数据日期范围（yyyyMMdd）
         * @return List<DbPmsHfBO>
         * @author zc
         * @date 2026/1/5
         */
        @Override
        public List<DbPmsHfBO> parse(PropertyDTO propertyDTO, InputStream in, List<Long> dateRange) throws ParseReportException {
            try (Workbook workbook = WorkbookFactory.create(in)) {
                List<DbPmsHfBO> boList = Lists.newArrayList();

                long startDate = dateRange.get(0);
                long endDate = dateRange.get(1);

                RoundingMode roundingMode = InternationalizationUtils.getRoundingMode(propertyDTO
                        .getInternationalization().getNumber().getCalculationRule());

                Sheet sheet = workbook.getSheetAt(0);
                // 获取最大行数
                int lastRowNum = sheet.getLastRowNum();
                for (int i = 1; i <= lastRowNum; i++) {
                    Row row = sheet.getRow(i);

                    // Date
                    String dateStr = CellUtils.convertCellStr(row.getCell(0));
                    long date = Long.parseLong(DateTimeUtils.localDateToStr(DateTimeUtils.strToLocalDate(dateStr,
                            BaseConstant.DATE_FORMAT_YYYY_MM_DD), BaseConstant.DATE_FORMAT_YYYYMMDD));
                    // 判断数据日期是否在开始日期 和 结束日期之间
                    if (date < startDate || date > endDate) {
                        continue;
                    }

                    boList.add(convert2Bo(row, roundingMode));
                }

                return boList;
            } catch (IOException e) {
                throw new InternalServerErrorException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(),
                        "Handle parse Database History and Forecast Other report error.", e);
            }
        }

        /**
         * 转换为 BO
         *
         * @param row          行数据
         * @param roundingMode 舍入模式
         * @return PmsHfBO
         * @author zc
         * @date 2026/1/5
         */
        private DbPmsHfBO convert2Bo(Row row, RoundingMode roundingMode) {
            DbPmsHfBO bo = new DbPmsHfBO();

            int scale = BaseConstant.DATA_PRECISION;
            String dateStr = CellUtils.convertCellStr(row.getCell(0));
            bo.setDStr(dateStr);

            // Inventory rooms
            String inventoryRoomsStr = CellUtils.convertCellStr(row.getCell(1));
            bo.setIr(StringUtils.isBlank(inventoryRoomsStr) ? null : Integer.parseInt(inventoryRoomsStr));
            // Sell Inventory rooms
            String sellInventoryRoomsStr = CellUtils.convertCellStr(row.getCell(2));
            bo.setSir(StringUtils.isBlank(sellInventoryRoomsStr) ? null : Integer.parseInt(sellInventoryRoomsStr));
            // Revenue
            String revenueStr = CellUtils.convertCellStr(row.getCell(3));
            bo.setRr(StringUtils.isEmpty(revenueStr) ? null : new BigDecimal(revenueStr)
                    .setScale(scale, roundingMode));
            // Deduct individual
            String deductIndividualStr = CellUtils.convertCellStr(row.getCell(4));
            bo.setDis(StringUtils.isBlank(deductIndividualStr) ? null : Integer.parseInt(deductIndividualStr));
            // Deduct individual revenue
            String deductIndividualRevenueStr = CellUtils.convertCellStr(row.getCell(5));
            bo.setDir(StringUtils.isEmpty(deductIndividualRevenueStr) ? null :
                    new BigDecimal(deductIndividualRevenueStr).setScale(scale, roundingMode));
            // Non deduct individual
            String nonDeductIndividualStr = CellUtils.convertCellStr(row.getCell(6));
            bo.setNdis(StringUtils.isBlank(nonDeductIndividualStr) ? null : Integer.parseInt(nonDeductIndividualStr));
            // Non deduct individual revenue
            String nonDeductIndividualRevenueStr = CellUtils.convertCellStr(row.getCell(7));
            bo.setNdir(StringUtils.isEmpty(nonDeductIndividualRevenueStr) ? null :
                    new BigDecimal(nonDeductIndividualRevenueStr).setScale(scale, roundingMode));
            // Deduct group
            String deductGroupStr = CellUtils.convertCellStr(row.getCell(8));
            bo.setDgs(StringUtils.isBlank(deductGroupStr) ? null : Integer.parseInt(deductGroupStr));
            // Deduct group revenue
            String deductGroupRevenueStr = CellUtils.convertCellStr(row.getCell(9));
            bo.setDgr(StringUtils.isEmpty(deductGroupRevenueStr) ? null :
                    new BigDecimal(deductGroupRevenueStr).setScale(scale, roundingMode));
            // Non deduct group
            String nonDeductGroupStr = CellUtils.convertCellStr(row.getCell(10));
            bo.setNdgs(StringUtils.isBlank(nonDeductGroupStr) ? null : Integer.parseInt(nonDeductGroupStr));
            // Non deduct group revenue
            String nonDeductGroupRevenueStr = CellUtils.convertCellStr(row.getCell(11));
            bo.setNdgr(StringUtils.isEmpty(nonDeductGroupRevenueStr) ? null :
                    new BigDecimal(nonDeductGroupRevenueStr).setScale(scale, roundingMode));
            // Number of persons
            String numberOfPersonsStr = CellUtils.convertCellStr(row.getCell(12));
            bo.setGu(StringUtils.isBlank(numberOfPersonsStr) ? null : Integer.parseInt(numberOfPersonsStr));
            // Arrival rooms
            String arrivalRoomsStr = CellUtils.convertCellStr(row.getCell(13));
            bo.setArr(StringUtils.isBlank(arrivalRoomsStr) ? null : Integer.parseInt(arrivalRoomsStr));
            // Departure rooms
            String departureRoomsStr = CellUtils.convertCellStr(row.getCell(14));
            bo.setDep(StringUtils.isBlank(departureRoomsStr) ? null : Integer.parseInt(departureRoomsStr));
            // Complimentary rooms
            String compRoomsStr = CellUtils.convertCellStr(row.getCell(15));
            bo.setCom(StringUtils.isBlank(compRoomsStr) ? null : Integer.parseInt(compRoomsStr));
            // House Use rooms
            String houseUseRoomsStr = CellUtils.convertCellStr(row.getCell(16));
            bo.setHu(StringUtils.isBlank(houseUseRoomsStr) ? null : Integer.parseInt(houseUseRoomsStr));
            // Day Use rooms
            String dayUseRoomsStr = CellUtils.convertCellStr(row.getCell(17));
            bo.setDu(StringUtils.isBlank(dayUseRoomsStr) ? null : Integer.parseInt(dayUseRoomsStr));
            // No show rooms
            String noShowRoomsStr = CellUtils.convertCellStr(row.getCell(18));
            bo.setNs(StringUtils.isBlank(noShowRoomsStr) ? null : Integer.parseInt(noShowRoomsStr));
            // Out of order rooms
            String outOfOrderRoomsStr = CellUtils.convertCellStr(row.getCell(19));
            bo.setOoo(StringUtils.isBlank(outOfOrderRoomsStr) ? null : Integer.parseInt(outOfOrderRoomsStr));
            // Occ rooms
            String occRoomsStr = CellUtils.convertCellStr(row.getCell(20));
            bo.setRs(StringUtils.isBlank(occRoomsStr) ? null : Integer.parseInt(occRoomsStr));
            // Average room rate
            String avgRoomRateStr = CellUtils.convertCellStr(row.getCell(21));
            bo.setAdr(StringUtils.isEmpty(avgRoomRateStr) ? null : new BigDecimal(avgRoomRateStr)
                    .setScale(scale, roundingMode));
            // Occupancy
            String occupancyStr = CellUtils.convertCellStr(row.getCell(22));
            bo.setOcc(StringUtils.isBlank(occupancyStr) ? null : new BigDecimal(occupancyStr)
                    .setScale(scale, roundingMode));

            return bo;
        }
    }