# ParseRevenueExcelServiceImpl.java

    package cloud.wconnect.rateshop.service.revenue.report;

    import cloud.wconnect.commonlib.cons.BaseConstant;
    import cloud.wconnect.commonlib.cons.BaseErrorCodeEnum;
    import cloud.wconnect.commonlib.cons.CommonErrorI18nConstant;
    import cloud.wconnect.commonlib.cons.CommonFieldI18nConstant;
    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.pojo.dto.RoomsConfigDTO;
    import cloud.wconnect.commonlib.pojo.dto.internationalization.InternationalizationNumberDTO;
    import cloud.wconnect.commonlib.service.RoomsConfigInternalService;
    import cloud.wconnect.commonlib.util.CellUtils;
    import cloud.wconnect.commonlib.util.DateTimeUtils;
    import cloud.wconnect.commonlib.util.I18nMessageUtils;
    import cloud.wconnect.commonlib.util.InternationalizationUtils;
    import cloud.wconnect.rateshop.cons.revenue.RevenueCons;
    import cloud.wconnect.rateshop.entity.revenue.RevenueAdrConfig;
    import cloud.wconnect.rateshop.entity.revenue.RevenueSegment;
    import cloud.wconnect.rateshop.pojo.bo.revenue.*;
    import cloud.wconnect.rateshop.pojo.dto.revenue.CustomersDTO;
    import cloud.wconnect.rateshop.service.revenue.IRevenueAdrConfigService;
    import cloud.wconnect.rateshop.service.revenue.IRevenueSegmentService;
    import com.baomidou.dynamic.datasource.annotation.DS;
    import com.google.common.collect.Lists;
    import com.google.common.collect.Maps;
    import org.apache.commons.collections.MapUtils;
    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 org.springframework.web.multipart.MultipartFile;

    import jakarta.annotation.Resource;
    import java.io.IOException;
    import java.io.InputStream;
    import java.math.BigDecimal;
    import java.math.RoundingMode;
    import java.time.LocalDate;
    import java.time.ZoneId;
    import java.time.ZonedDateTime;
    import java.time.format.DateTimeFormatter;
    import java.util.Collections;
    import java.util.List;
    import java.util.Map;
    import java.util.Objects;
    import java.util.stream.Collectors;

    /**
     * @author: yyt
     * 2024-08-15
     */
    @Service
    @DS("rate_shop")
    public class ParseRevenueExcelServiceImpl implements IParseRevenueExcelService {

        @Resource
        private RoomsConfigInternalService roomsConfigInternalService;
        @Resource
        private IRevenueAdrConfigService revenueAdrConfigService;
        @Resource
        private IRevenueSegmentService revenueSegmentService;
        @Autowired
        private I18nMessageUtils i18nMessageUtils;

        /**
         * 检查 HistoryAndForecast 文件
         *
         * @param propertyDTO 资产
         * @param file        文件
         * @author yyt
         * @date 2024/8/15
         **/
        @Override
        public void checkHistoryAndForecast(PropertyDTO propertyDTO, MultipartFile file) {
            try (InputStream in = file.getInputStream();
                 Workbook workbook = WorkbookFactory.create(in)) {
                Sheet sheet = workbook.getSheetAt(0);
                Row revenueRow = sheet.getRow(0);
                if (Objects.isNull(revenueRow)) {
                    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 revenueTitle = CellUtils.convertCellStr(revenueRow.getCell(0));
                Cell revenueCell = revenueRow.getCell(1);
                if (Objects.nonNull(revenueCell) && StringUtils.isNotBlank(revenueCell.toString())) {
                    BigDecimal revenue;
                    try {
                        revenue = new BigDecimal(CellUtils.convertCellStr(revenueCell));
                    } catch (Exception e) {
                        throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                i18nMessageUtils.getErrorParamMapNotNeedTranslate(revenueTitle),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The revenue must be a number");
                    }
                    // 整数 10 位
                    if (revenue.toBigInteger().toString().length() > 10) {
                        throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                i18nMessageUtils.getErrorParamMapNotNeedTranslate(revenueTitle, 10),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The revenue exceed the limit");
                    }
                }

                Row rateRow = sheet.getRow(1);
                String rateTitle = CellUtils.convertCellStr(rateRow.getCell(0));
                Cell rateCell = rateRow.getCell(1);
                if (Objects.nonNull(rateCell) && StringUtils.isNotBlank(rateCell.toString())) {
                    BigDecimal rate;
                    try {
                        rate = new BigDecimal(CellUtils.convertCellStr(rateCell));
                    } catch (Exception e) {
                        throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                i18nMessageUtils.getErrorParamMapNotNeedTranslate(rateTitle),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The rate must be a number");
                    }
                    // 整数 10 位
                    if (rate.toBigInteger().toString().length() > 10) {
                        throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                i18nMessageUtils.getErrorParamMapNotNeedTranslate(rateTitle, 10),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The rate exceed the limit");
                    }
                }

                Row roomsRow = sheet.getRow(2);
                String roomsTitle = CellUtils.convertCellStr(roomsRow.getCell(0));
                Cell roomsCell = roomsRow.getCell(1);
                if (Objects.nonNull(roomsCell) && StringUtils.isNotBlank(roomsCell.toString())) {
                    BigDecimal rooms;
                    try {
                        rooms = new BigDecimal(CellUtils.convertCellStr(roomsCell));
                    } catch (Exception e) {
                        throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                i18nMessageUtils.getErrorParamMapNotNeedTranslate(roomsTitle),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The rooms must be a number");
                    }
                    // 整数 8 位
                    if (rooms.toBigInteger().toString().length() > 8) {
                        throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                i18nMessageUtils.getErrorParamMapNotNeedTranslate(roomsTitle, 8),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The rooms exceed the limit");
                    }
                }

                Row occRow = sheet.getRow(3);
                String occTitle = CellUtils.convertCellStr(occRow.getCell(0));
                Cell occCell = occRow.getCell(1);
                if (Objects.nonNull(occCell) && StringUtils.isNotBlank(occCell.toString())) {
                    BigDecimal occ;
                    try {
                        occ = new BigDecimal(CellUtils.convertCellStr(occCell));
                    } catch (Exception e) {
                        throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                i18nMessageUtils.getErrorParamMapNotNeedTranslate(occTitle),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The occ must be a number");
                    }
                    // 整数 6 位
                    if (occ.toBigInteger().toString().length() > 6) {
                        throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                i18nMessageUtils.getErrorParamMapNotNeedTranslate(occTitle, 6),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The occ exceed the limit");
                    }
                }

                Row listTitleRow = sheet.getRow(4);
                String dateTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(listTitleRow, "A"));
                String listRoomsTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(listTitleRow, "B"));
                String listOccTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(listTitleRow, "C"));
                String listRateTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(listTitleRow, "D"));
                String oooRoomsTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(listTitleRow, "E"));
                String transientDeductRoomsTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(listTitleRow, "F"));
                String transientNonDeductRoomsTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(listTitleRow, "G"));
                String groupDeductRoomsTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(listTitleRow, "H"));
                String groupNonDeductRoomsTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(listTitleRow, "I"));
                DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(BaseConstant.DATE_FORMAT_YYYY_MM_DD);

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

                    Cell dateCell = CellUtils.getCellByColumnLetter(row, "A");
                    if (Objects.isNull(dateCell) || StringUtils.isBlank(dateCell.toString())) {
                        throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY,
                                i18nMessageUtils.getErrorParamMapNotNeedTranslate(dateTitle),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The date cannot be empty");
                    } else {
                        // 格式必须 yyyy-MM-dd
                        try {
                            LocalDate.parse(CellUtils.convertCellStr(dateCell, BaseConstant.DATE_FORMAT_YYYY_MM_DD), dateTimeFormatter);
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.FORMAT_INCORRECT,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(dateTitle, BaseConstant.DATE_FORMAT_YYYY_MM_DD),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The date format error");
                        }
                    }

                    roomsCell = CellUtils.getCellByColumnLetter(row, "B");
                    if (Objects.nonNull(roomsCell) && StringUtils.isNotBlank(roomsCell.toString())) {
                        BigDecimal rooms;
                        try {
                            rooms = new BigDecimal(CellUtils.convertCellStr(roomsCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(listRoomsTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The rooms must be a number");
                        }
                        if (rooms.toBigInteger().toString().length() > 6) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(listRoomsTitle, 6),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The rooms exceed the limit");
                        }
                    }

                    occCell = CellUtils.getCellByColumnLetter(row, "C");
                    if (Objects.nonNull(occCell) && StringUtils.isNotBlank(occCell.toString())) {
                        BigDecimal occ;
                        try {
                            occ = new BigDecimal(CellUtils.convertCellStr(occCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(listOccTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The occ must be a number");
                        }
                        if (occ.toBigInteger().toString().length() > 6) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(listOccTitle, 6),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The occ exceed the limit");
                        }
                    }

                    rateCell = CellUtils.getCellByColumnLetter(row, "D");
                    if (Objects.nonNull(rateCell) && StringUtils.isNotBlank(rateCell.toString())) {
                        BigDecimal rate;
                        try {
                            rate = new BigDecimal(CellUtils.convertCellStr(rateCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(listRateTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The rate must be a number");
                        }
                        if (rate.toBigInteger().toString().length() > 6) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(listRateTitle, 6),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The rate exceed the limit");
                        }
                    }

                    Cell oooRoomsCell = CellUtils.getCellByColumnLetter(row, "E");
                    if (Objects.nonNull(oooRoomsCell) && StringUtils.isNotBlank(oooRoomsCell.toString())) {
                        BigDecimal oooRooms;
                        try {
                            oooRooms = new BigDecimal(CellUtils.convertCellStr(oooRoomsCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(oooRoomsTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The ooo rooms must be a number");
                        }
                        if (oooRooms.toBigInteger().toString().length() > 6) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(oooRoomsTitle, 6),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The ooo rooms exceed the limit");
                        }
                    }

                    Cell transientDeductRoomsCell = CellUtils.getCellByColumnLetter(row, "F");
                    if (Objects.nonNull(transientDeductRoomsCell) && StringUtils.isNotBlank(transientDeductRoomsCell.toString())) {
                        BigDecimal transientDeductRooms;
                        try {
                            transientDeductRooms = new BigDecimal(CellUtils.convertCellStr(transientDeductRoomsCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(transientDeductRoomsTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The transient deduct rooms must be a number");
                        }
                        if (transientDeductRooms.toBigInteger().toString().length() > 6) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(transientDeductRoomsTitle, 6),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The transient deduct rooms exceed the limit");
                        }
                    }

                    Cell transientNonDeductRoomsCell = CellUtils.getCellByColumnLetter(row, "G");
                    if (Objects.nonNull(transientNonDeductRoomsCell) && StringUtils.isNotBlank(transientNonDeductRoomsCell.toString())) {
                        BigDecimal transientNonDeductRooms;
                        try {
                            transientNonDeductRooms = new BigDecimal(CellUtils.convertCellStr(transientNonDeductRoomsCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(transientNonDeductRoomsTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The transient non deduct rooms must be a number");
                        }
                        if (transientNonDeductRooms.toBigInteger().toString().length() > 6) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(transientNonDeductRoomsTitle, 6),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The transient non deduct rooms exceed the limit");
                        }
                    }

                    Cell groupDeductRoomsCell = CellUtils.getCellByColumnLetter(row, "H");
                    if (Objects.nonNull(groupDeductRoomsCell) && StringUtils.isNotBlank(groupDeductRoomsCell.toString())) {
                        BigDecimal groupDeductRooms;
                        try {
                            groupDeductRooms = new BigDecimal(CellUtils.convertCellStr(groupDeductRoomsCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(groupDeductRoomsTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The group deduct rooms must be a number");
                        }
                        if (groupDeductRooms.toBigInteger().toString().length() > 6) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(groupDeductRoomsTitle, 6),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The group deduct rooms exceed the limit");
                        }
                    }

                    Cell groupNonDeductRoomsCell = CellUtils.getCellByColumnLetter(row, "I");
                    if (Objects.nonNull(groupNonDeductRoomsCell) && StringUtils.isNotBlank(groupNonDeductRoomsCell.toString())) {
                        BigDecimal groupNonDeductRooms;
                        try {
                            groupNonDeductRooms = new BigDecimal(CellUtils.convertCellStr(groupNonDeductRoomsCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(groupNonDeductRoomsTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The group non deduct rooms must be a number");
                        }
                        if (groupNonDeductRooms.toBigInteger().toString().length() > 6) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(groupNonDeductRoomsTitle, 6),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The group non deduct rooms exceed the limit");
                        }
                    }
                }
            } catch (BadRequestException e) {
                throw e;
            } catch (Exception e) {
                throw new ParseReportException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(), "parsing file failed.", e);
            }
        }

        /**
         * 检查 HistoryAndForecast 文件
         *
         * @param propertyDTO 资产
         * @param file        文件
         * @author yyt
         * @date 2025/7/7
         **/
        @Override
        public void checkHistoryAndForecastForShiJi(PropertyDTO propertyDTO, MultipartFile file) {
            try (InputStream in = file.getInputStream();
                 Workbook workbook = WorkbookFactory.create(in)) {
                Sheet sheet = workbook.getSheetAt(0);
                Row revenueRow = sheet.getRow(0);
                if (Objects.isNull(revenueRow)) {
                    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 revenueTitle = CellUtils.convertCellStr(revenueRow.getCell(0));
                Cell revenueCell = revenueRow.getCell(1);
                if (Objects.nonNull(revenueCell) && StringUtils.isNotBlank(revenueCell.toString())) {
                    BigDecimal revenue;
                    try {
                        revenue = new BigDecimal(CellUtils.convertCellStr(revenueCell));
                    } catch (Exception e) {
                        throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                i18nMessageUtils.getErrorParamMapNotNeedTranslate(revenueTitle),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The revenue must be a number");
                    }
                    // 整数 10 位
                    if (revenue.toBigInteger().toString().length() > 10) {
                        throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                i18nMessageUtils.getErrorParamMapNotNeedTranslate(revenueTitle, 10),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The revenue exceed the limit");
                    }
                }

                Row rateRow = sheet.getRow(1);
                String rateTitle = CellUtils.convertCellStr(rateRow.getCell(0));
                Cell rateCell = rateRow.getCell(1);
                if (Objects.nonNull(rateCell) && StringUtils.isNotBlank(rateCell.toString())) {
                    BigDecimal rate;
                    try {
                        rate = new BigDecimal(CellUtils.convertCellStr(rateCell));
                    } catch (Exception e) {
                        throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                i18nMessageUtils.getErrorParamMapNotNeedTranslate(rateTitle),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The rate must be a number");
                    }
                    // 整数 10 位
                    if (rate.toBigInteger().toString().length() > 10) {
                        throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                i18nMessageUtils.getErrorParamMapNotNeedTranslate(rateTitle, 10),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The rate exceed the limit");
                    }
                }

                Row roomsRow = sheet.getRow(2);
                String roomsTitle = CellUtils.convertCellStr(roomsRow.getCell(0));
                Cell roomsCell = roomsRow.getCell(1);
                if (Objects.nonNull(roomsCell) && StringUtils.isNotBlank(roomsCell.toString())) {
                    BigDecimal rooms;
                    try {
                        rooms = new BigDecimal(CellUtils.convertCellStr(roomsCell));
                    } catch (Exception e) {
                        throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                i18nMessageUtils.getErrorParamMapNotNeedTranslate(roomsTitle),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The rooms must be a number");
                    }
                    // 整数 8 位
                    if (rooms.toBigInteger().toString().length() > 8) {
                        throw new BadRequestException(CommonErrorI18nConstant.NUMBER_OVERLENGTH,
                                i18nMessageUtils.getErrorParamMapNotNeedTranslate(roomsTitle, 8, 0),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The rooms exceed the limit");
                    }
                }

                Row occRow = sheet.getRow(3);
                String occTitle = CellUtils.convertCellStr(occRow.getCell(0));
                Cell occCell = occRow.getCell(1);
                if (Objects.nonNull(occCell) && StringUtils.isNotBlank(occCell.toString())) {
                    BigDecimal occ;
                    try {
                        occ = new BigDecimal(CellUtils.convertCellStr(occCell));
                    } catch (Exception e) {
                        throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                i18nMessageUtils.getErrorParamMapNotNeedTranslate(occTitle),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The occ must be a number");
                    }
                    // 整数 6 位
                    if (occ.toBigInteger().toString().length() > 6) {
                        throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                i18nMessageUtils.getErrorParamMapNotNeedTranslate(occTitle, 6),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The occ exceed the limit");
                    }
                }

                Row listTitleRow = sheet.getRow(4);
                String dateTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(listTitleRow, "A"));
                String listRoomsTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(listTitleRow, "B"));
                String listOccTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(listTitleRow, "C"));
                String listRateTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(listTitleRow, "D"));
                String oooRoomsTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(listTitleRow, "E"));
                String transientDeductRoomsTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(listTitleRow, "F"));
                String transientDeductRevenueTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(listTitleRow, "G"));
                String groupDeductRoomsTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(listTitleRow, "H"));
                String groupDeductRevenueTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(listTitleRow, "I"));
                DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(BaseConstant.DATE_FORMAT_YYYY_MM_DD);

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

                    Cell dateCell = CellUtils.getCellByColumnLetter(row, "A");
                    if (Objects.isNull(dateCell) || StringUtils.isBlank(dateCell.toString())) {
                        throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY,
                                i18nMessageUtils.getErrorParamMapNotNeedTranslate(dateTitle),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The date cannot be empty");
                    } else {
                        // 格式必须 yyyy-MM-dd
                        try {
                            LocalDate.parse(CellUtils.convertCellStr(dateCell, BaseConstant.DATE_FORMAT_YYYY_MM_DD), dateTimeFormatter);
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.FORMAT_INCORRECT,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(dateTitle, BaseConstant.DATE_FORMAT_YYYY_MM_DD),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The date format error");
                        }
                    }

                    roomsCell = CellUtils.getCellByColumnLetter(row, "B");
                    if (Objects.nonNull(roomsCell) && StringUtils.isNotBlank(roomsCell.toString())) {
                        BigDecimal rooms;
                        try {
                            rooms = new BigDecimal(CellUtils.convertCellStr(roomsCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(listRoomsTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The rooms must be a number");
                        }
                        if (rooms.toBigInteger().toString().length() > 6) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(listRoomsTitle, 6, 0),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The rooms exceed the limit");
                        }
                    }

                    occCell = CellUtils.getCellByColumnLetter(row, "C");
                    if (Objects.nonNull(occCell) && StringUtils.isNotBlank(occCell.toString())) {
                        BigDecimal occ;
                        try {
                            occ = new BigDecimal(CellUtils.convertCellStr(occCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(listOccTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The occ must be a number");
                        }
                        if (occ.toBigInteger().toString().length() > 6) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(listOccTitle, 6),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The occ exceed the limit");
                        }
                    }

                    rateCell = CellUtils.getCellByColumnLetter(row, "D");
                    if (Objects.nonNull(rateCell) && StringUtils.isNotBlank(rateCell.toString())) {
                        BigDecimal rate;
                        try {
                            rate = new BigDecimal(CellUtils.convertCellStr(rateCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(listRateTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The rate must be a number");
                        }
                        if (rate.toBigInteger().toString().length() > 6) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(listRateTitle, 6),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The rate exceed the limit");
                        }
                    }

                    Cell oooRoomsCell = CellUtils.getCellByColumnLetter(row, "E");
                    if (Objects.nonNull(oooRoomsCell) && StringUtils.isNotBlank(oooRoomsCell.toString())) {
                        BigDecimal oooRooms;
                        try {
                            oooRooms = new BigDecimal(CellUtils.convertCellStr(oooRoomsCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(oooRoomsTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The ooo rooms must be a number");
                        }
                        if (oooRooms.toBigInteger().toString().length() > 6) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(oooRoomsTitle, 6, 0),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The ooo rooms exceed the limit");
                        }
                    }

                    Cell transientDeductRoomsCell = CellUtils.getCellByColumnLetter(row, "F");
                    if (Objects.nonNull(transientDeductRoomsCell) && StringUtils.isNotBlank(transientDeductRoomsCell.toString())) {
                        BigDecimal transientDeductRooms;
                        try {
                            transientDeductRooms = new BigDecimal(CellUtils.convertCellStr(transientDeductRoomsCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(transientDeductRoomsTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The transient deduct rooms must be a number");
                        }
                        if (transientDeductRooms.toBigInteger().toString().length() > 6) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(transientDeductRoomsTitle, 6, 0),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The transient deduct rooms exceed the limit");
                        }
                    }

                    Cell transientDeductRevenueCell = CellUtils.getCellByColumnLetter(row, "G");
                    if (Objects.nonNull(transientDeductRevenueCell) && StringUtils.isNotBlank(transientDeductRevenueCell.toString())) {
                        BigDecimal transientDeductRevenue;
                        try {
                            transientDeductRevenue = new BigDecimal(CellUtils.convertCellStr(transientDeductRevenueCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(transientDeductRevenueTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The transient deduct revenue rooms must be a number");
                        }
                        if (transientDeductRevenue.toBigInteger().toString().length() > 6) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(transientDeductRevenueTitle, 6),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The transient deduct revenue rooms exceed the limit");
                        }
                    }

                    Cell groupDeductRoomsCell = CellUtils.getCellByColumnLetter(row, "H");
                    if (Objects.nonNull(groupDeductRoomsCell) && StringUtils.isNotBlank(groupDeductRoomsCell.toString())) {
                        BigDecimal groupDeductRooms;
                        try {
                            groupDeductRooms = new BigDecimal(CellUtils.convertCellStr(groupDeductRoomsCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(groupDeductRoomsTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The group deduct rooms must be a number");
                        }
                        if (groupDeductRooms.toBigInteger().toString().length() > 6) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(groupDeductRoomsTitle, 6, 0),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The group deduct rooms exceed the limit");
                        }
                    }

                    Cell groupDeductRevenueCell = CellUtils.getCellByColumnLetter(row, "I");
                    if (Objects.nonNull(groupDeductRevenueCell) && StringUtils.isNotBlank(groupDeductRevenueCell.toString())) {
                        BigDecimal groupDeductRevenue;
                        try {
                            groupDeductRevenue = new BigDecimal(CellUtils.convertCellStr(groupDeductRevenueCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(groupDeductRevenueTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The group deduct revenue rooms must be a number");
                        }
                        if (groupDeductRevenue.toBigInteger().toString().length() > 6) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(groupDeductRevenueTitle, 6),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The group deduct revenue rooms exceed the limit");
                        }
                    }
                }
            } catch (BadRequestException e) {
                throw e;
            } catch (Exception e) {
                throw new ParseReportException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(), "parsing file failed.", e);
            }
        }

        /**
         * 解析 HistoryAndForecast 报告
         *
         * @param propertyDTO 资产
         * @param inputStream 文件流
         * @param startDate   开始日期
         * @param endDate     结束日期
         * @return Otb And TransientGroup
         * @author yyt
         * @date 2024/8/15
         **/
        @Override
        public OtbAndTransientGroupBO parseHistoryAndForecast(PropertyDTO propertyDTO, InputStream inputStream, Long startDate, Long endDate) throws IOException {
            InternationalizationNumberDTO number = propertyDTO.getInternationalization().getNumber();
            RoundingMode roundingMode = InternationalizationUtils.getRoundingMode(number.getCalculationRule());
            int dataPrecision = BaseConstant.DATA_PRECISION;

            Integer propertyId = propertyDTO.getId();
            RoomsConfigDTO roomsConfig = roomsConfigInternalService.getRoomsConfig(propertyId);
            Integer rooms = roomsConfig.getRooms();

            RevenueAdrConfig revenueAdrConfig = revenueAdrConfigService.get(propertyId);
            boolean checkTransientDeduct = Objects.nonNull(revenueAdrConfig) && revenueAdrConfig.getCheckTransientDeduct();
            boolean checkTransientNonDeduct = Objects.nonNull(revenueAdrConfig) && revenueAdrConfig.getCheckTransientNonDeduct();
            boolean checkGroupDeduct = Objects.nonNull(revenueAdrConfig) && revenueAdrConfig.getCheckGroupDeduct();
            boolean checkGroupNonDeduct = Objects.nonNull(revenueAdrConfig) && revenueAdrConfig.getCheckGroupNonDeduct();

            // 最多1个月的数据
            List<TransientGroupBO> transientGroups = Lists.newArrayListWithExpectedSize(31);
            ZoneId zoneId = ZoneId.of(propertyDTO.getTimezone());
            DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(BaseConstant.DATE_FORMAT_YYYY_MM_DD);
            BigDecimal oneHundred = BigDecimal.valueOf(100L);

            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            for (int i = 5; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (Objects.isNull(row)) {
                    continue;
                }

                Cell dateCell = CellUtils.getCellByColumnLetter(row, "A");
                String dateStr = CellUtils.convertCellStr(dateCell, BaseConstant.DATE_FORMAT_YYYY_MM_DD);
                ZonedDateTime targetDateZoneDateTime = LocalDate.parse(dateStr, dateTimeFormatter).atStartOfDay(zoneId);
                long targetDate = targetDateZoneDateTime.toEpochSecond();
                if (targetDate < startDate || targetDate > endDate) {
                    continue;
                }

                TransientGroupBO transientGroup = new TransientGroupBO();
                transientGroup.setTargetDate(dateStr);

                // 如果配置了房间数，则根据 occ 计算规则判断：1.使用配置的房间数计算 occ，2.直接使用文件中的 occ
                if (roomsConfig.getOccRule() == RevenueCons.USE_ROOMS_TO_CALCULATE) {
                    String totalOccRoomsStr = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "B"));
                    if (StringUtils.isNotEmpty(totalOccRoomsStr)) {
                        //  Occ. % = total occ rooms / 总房间数
                        BigDecimal occPercent = new BigDecimal(totalOccRoomsStr).divide(BigDecimal.valueOf(rooms),
                                dataPrecision, roundingMode).multiply(oneHundred);
                        transientGroup.setOccPercent(occPercent);
                    }
                } else {
                    String occPercentStr = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "C"));
                    if (StringUtils.isNotEmpty(occPercentStr)) {
                        transientGroup.setOccPercent(new BigDecimal(occPercentStr).setScale(dataPrecision, roundingMode));
                    }
                }

                String adrStr = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "D"));
                if (StringUtils.isNotEmpty(adrStr)) {
                    transientGroup.setAdr(new BigDecimal(adrStr).setScale(dataPrecision, roundingMode));
                }

                // 如果勾选了 Transient deduct，则累加交订金的 Transient 房间数
                String transientRoomsStr = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "F"));
                if (checkTransientDeduct && StringUtils.isNotEmpty(transientRoomsStr)) {
                    transientGroup.setTransientRoomNum(Integer.valueOf(transientRoomsStr));
                }

                // 如果勾选了 Transient non deduct，则累加未交订金的 Transient 房间数
                String nonDeductRoomsStr = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "G"));
                if (checkTransientNonDeduct && StringUtils.isNotEmpty(nonDeductRoomsStr)) {
                    Integer transientRoomNum = transientGroup.getTransientRoomNum();
                    Integer nonDeductRooms = Integer.valueOf(nonDeductRoomsStr);
                    if (transientRoomNum == null) {
                        transientGroup.setTransientRoomNum(nonDeductRooms);
                    } else {
                        transientGroup.setTransientRoomNum(transientRoomNum + nonDeductRooms);
                    }
                }

                // 如果勾选了 Group deduct，则累加交订金的 Group 房间数
                String groupRoomsStr = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "H"));
                if (checkGroupDeduct && StringUtils.isNotEmpty(groupRoomsStr)) {
                    transientGroup.setGroupRoomNum(Integer.valueOf(groupRoomsStr));
                }

                // 如果勾选了 Group non deduct，则累加未交订金的 Group 房间数
                String grpNonDeductRoomsStr = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "I"));
                if (checkGroupNonDeduct && StringUtils.isNotEmpty(grpNonDeductRoomsStr)) {
                    Integer groupRoomNum = transientGroup.getGroupRoomNum();
                    Integer grpNonDeductRooms = Integer.valueOf(grpNonDeductRoomsStr);
                    if (groupRoomNum == null) {
                        transientGroup.setGroupRoomNum(grpNonDeductRooms);
                    } else {
                        transientGroup.setGroupRoomNum(groupRoomNum + grpNonDeductRooms);
                    }
                }

                // 计算 Rooms Available = 酒店房间数 - Total Occ. - OOO Rooms
                Integer roomsAvailable = rooms;
                if (Objects.nonNull(roomsAvailable)) {
                    String totalOcc = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "B"));
                    String oooRooms = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "E"));
                    if (StringUtils.isNotEmpty(totalOcc)) {
                        roomsAvailable -= Integer.parseInt(totalOcc);
                    }
                    if (StringUtils.isNotEmpty(oooRooms)) {
                        roomsAvailable -= Integer.parseInt(oooRooms);
                    }
                }
                transientGroup.setRoomsAvailable(roomsAvailable);

                transientGroups.add(transientGroup);
            }

            OtbRevenueBO otbRevenue = new OtbRevenueBO();
            Row revenueRow = sheet.getRow(0);
            if (Objects.nonNull(revenueRow)) {
                Cell revenueCell = revenueRow.getCell(1);
                if (Objects.nonNull(revenueCell) && StringUtils.isNotBlank(revenueCell.toString())) {
                    otbRevenue.setOtbRevenue(new BigDecimal(CellUtils.convertCellStr(revenueCell)));
                }
            }

            Row rateRow = sheet.getRow(1);
            if (Objects.nonNull(rateRow)) {
                Cell rateCell = rateRow.getCell(1);
                if (Objects.nonNull(rateCell) && StringUtils.isNotBlank(rateCell.toString())) {
                    otbRevenue.setOtbAdr(new BigDecimal(CellUtils.convertCellStr(rateCell)));
                }
            }

            // 如果配置了房间数，则根据 occ 计算规则判断：1.使用配置的房间数计算 occ，2.直接使用文件中的 occ
            if (roomsConfig.getOccRule() == RevenueCons.USE_ROOMS_TO_CALCULATE) {
                Row roomsRow = sheet.getRow(2);
                if (Objects.nonNull(roomsRow)) {
                    Cell roomsCell = roomsRow.getCell(1);
                    if (Objects.nonNull(roomsCell) && StringUtils.isNotBlank(roomsCell.toString())) {
                        //  Occ. % = Total Occ / (房间数 * 总天数)
                        int totalDays = transientGroups.size();
                        if (totalDays > BaseConstant.ZERO) {
                            BigDecimal occPercent = new BigDecimal(CellUtils.convertCellStr(roomsCell))
                                    .divide(BigDecimal.valueOf((long) roomsConfig.getRooms() * totalDays), dataPrecision, roundingMode)
                                    .multiply(BigDecimal.valueOf(100L));
                            otbRevenue.setOtbOccPercent(occPercent);
                        }
                    }
                }
            } else {
                Row occRow = sheet.getRow(3);
                if (Objects.nonNull(occRow)) {
                    Cell occCell = occRow.getCell(1);
                    if (Objects.nonNull(occCell) && StringUtils.isNotBlank(occCell.toString())) {
                        otbRevenue.setOtbOccPercent(new BigDecimal(CellUtils.convertCellStr(occCell)).setScale(dataPrecision, roundingMode));
                    }
                }
            }

            workbook.close();

            return new OtbAndTransientGroupBO(otbRevenue, transientGroups);
        }

        /**
         * 解析 HistoryAndForecast 报告
         *
         * @param propertyDTO 资产
         * @param inputStream 文件流
         * @param startDate   开始日期
         * @param endDate     结束日期
         * @return Otb And TransientGroup
         * @author yyt
         * @date 2024/8/15
         **/
        @Override
        public OtbAndTransientGroupBO parseHistoryAndForecastForShiJi(PropertyDTO propertyDTO, InputStream inputStream, Long startDate, Long endDate) throws IOException {
            InternationalizationNumberDTO number = propertyDTO.getInternationalization().getNumber();
            RoundingMode roundingMode = InternationalizationUtils.getRoundingMode(number.getCalculationRule());
            int dataPrecision = BaseConstant.DATA_PRECISION;

            Integer propertyId = propertyDTO.getId();
            RoomsConfigDTO roomsConfig = roomsConfigInternalService.getRoomsConfig(propertyId);
            Integer rooms = roomsConfig.getRooms();

            RevenueAdrConfig revenueAdrConfig = revenueAdrConfigService.get(propertyId);
            boolean checkTransientDeduct = Objects.nonNull(revenueAdrConfig) && revenueAdrConfig.getCheckTransientDeduct();
            boolean checkGroupDeduct = Objects.nonNull(revenueAdrConfig) && revenueAdrConfig.getCheckGroupDeduct();

            // 最多1个月的数据
            List<TransientGroupBO> transientGroups = Lists.newArrayListWithExpectedSize(31);
            ZoneId zoneId = ZoneId.of(propertyDTO.getTimezone());
            DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(BaseConstant.DATE_FORMAT_YYYY_MM_DD);
            BigDecimal oneHundred = BigDecimal.valueOf(100L);

            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            for (int i = 5; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (Objects.isNull(row)) {
                    continue;
                }

                Cell dateCell = CellUtils.getCellByColumnLetter(row, "A");
                String dateStr = CellUtils.convertCellStr(dateCell, BaseConstant.DATE_FORMAT_YYYY_MM_DD);
                ZonedDateTime targetDateZoneDateTime = LocalDate.parse(dateStr, dateTimeFormatter).atStartOfDay(zoneId);
                long targetDate = targetDateZoneDateTime.toEpochSecond();
                if (targetDate < startDate || targetDate > endDate) {
                    continue;
                }

                TransientGroupBO transientGroup = new TransientGroupBO();
                transientGroup.setTargetDate(dateStr);

                // 如果配置了房间数，则根据 occ 计算规则判断：1.使用配置的房间数计算 occ，2.直接使用文件中的 occ
                if (roomsConfig.getOccRule() == RevenueCons.USE_ROOMS_TO_CALCULATE) {
                    String totalOccRoomsStr = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "B"));
                    if (StringUtils.isNotEmpty(totalOccRoomsStr)) {
                        //  Occ. % = total occ rooms / 总房间数
                        BigDecimal occPercent = new BigDecimal(totalOccRoomsStr).divide(BigDecimal.valueOf(rooms), dataPrecision, roundingMode).multiply(oneHundred);
                        transientGroup.setOccPercent(occPercent);
                    }
                } else {
                    String occPercentStr = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "C"));
                    if (StringUtils.isNotEmpty(occPercentStr)) {
                        transientGroup.setOccPercent(new BigDecimal(occPercentStr).setScale(dataPrecision, roundingMode));
                    }
                }

                String adrStr = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "D"));
                if (StringUtils.isNotEmpty(adrStr)) {
                    transientGroup.setAdr(new BigDecimal(adrStr).setScale(dataPrecision, roundingMode));
                }

                // 如果勾选了 Transient deduct，则累加交订金的 Transient 房间数
                String transientRoomsStr = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "F"));
                if (checkTransientDeduct && StringUtils.isNotEmpty(transientRoomsStr)) {
                    transientGroup.setTransientRoomNum(Integer.valueOf(transientRoomsStr));
                }
                // 如果勾选了 Transient deduct，则计算交订金的 Transient 房间平均房价
                BigDecimal transientRoomRevenue = null;
                String deductRevenueStr = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "G"));
                if (checkTransientDeduct && StringUtils.isNotEmpty(deductRevenueStr)) {
                    transientRoomRevenue = new BigDecimal(deductRevenueStr);
                }
                Integer transientRoomNum = transientGroup.getTransientRoomNum();
                if (Objects.nonNull(transientRoomRevenue) && Objects.nonNull(transientRoomNum) && transientRoomNum != BaseConstant.ZERO) {
                    BigDecimal transientAdr = transientRoomRevenue.divide(BigDecimal.valueOf(transientRoomNum), BaseConstant.DATA_PRECISION, roundingMode);
                    transientGroup.setTransientAdr(transientAdr);
                }

                // 如果勾选了 Group deduct，则累加交订金的 Group 房间数
                String groupRoomsStr = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "H"));
                if (checkGroupDeduct && StringUtils.isNotEmpty(groupRoomsStr)) {
                    transientGroup.setGroupRoomNum(Integer.valueOf(groupRoomsStr));
                }
                // 如果勾选了 Group deduct，则计算交订金的 Group 房间平均房价
                BigDecimal groupRoomRevenue = null;
                String grpDeductRevenueStr = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "I"));
                if (checkGroupDeduct && StringUtils.isNotEmpty(grpDeductRevenueStr)) {
                    groupRoomRevenue = new BigDecimal(grpDeductRevenueStr);
                }
                Integer groupRoomNum = transientGroup.getGroupRoomNum();
                if (Objects.nonNull(groupRoomRevenue) && Objects.nonNull(groupRoomNum) && groupRoomNum != BaseConstant.ZERO) {
                    BigDecimal groupAdr = groupRoomRevenue.divide(BigDecimal.valueOf(groupRoomNum),BaseConstant.DATA_PRECISION, roundingMode);
                    transientGroup.setGroupAdr(groupAdr);
                }

                // 计算 Rooms Available = 酒店房间数 - Total Occ. - OOO Rooms
                Integer roomsAvailable = rooms;
                if (Objects.nonNull(roomsAvailable)) {
                    String totalOcc = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "B"));
                    String oooRooms = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "E"));
                    if (StringUtils.isNotEmpty(totalOcc)) {
                        roomsAvailable -= Integer.parseInt(totalOcc);
                    }
                    if (StringUtils.isNotEmpty(oooRooms)) {
                        roomsAvailable -= Integer.parseInt(oooRooms);
                    }
                }
                transientGroup.setRoomsAvailable(roomsAvailable);

                transientGroups.add(transientGroup);
            }

            OtbRevenueBO otbRevenue = new OtbRevenueBO();
            Row revenueRow = sheet.getRow(0);
            if (Objects.nonNull(revenueRow)) {
                Cell revenueCell = revenueRow.getCell(1);
                if (Objects.nonNull(revenueCell) && StringUtils.isNotBlank(revenueCell.toString())) {
                    otbRevenue.setOtbRevenue(new BigDecimal(CellUtils.convertCellStr(revenueCell)));
                }
            }

            Row rateRow = sheet.getRow(1);
            if (Objects.nonNull(rateRow)) {
                Cell rateCell = rateRow.getCell(1);
                if (Objects.nonNull(rateCell) && StringUtils.isNotBlank(rateCell.toString())) {
                    otbRevenue.setOtbAdr(new BigDecimal(CellUtils.convertCellStr(rateCell)));
                }
            }

            // 如果配置了房间数，则根据 occ 计算规则判断：1.使用配置的房间数计算 occ，2.直接使用文件中的 occ
            if (roomsConfig.getOccRule() == RevenueCons.USE_ROOMS_TO_CALCULATE) {
                Row roomsRow = sheet.getRow(2);
                if (Objects.nonNull(roomsRow)) {
                    Cell roomsCell = roomsRow.getCell(1);
                    if (Objects.nonNull(roomsCell) && StringUtils.isNotBlank(roomsCell.toString())) {
                        //  Occ. % = Total Occ / (房间数 * 总天数)
                        int totalDays = transientGroups.size();
                        if (totalDays > BaseConstant.ZERO) {
                            BigDecimal occPercent = new BigDecimal(CellUtils.convertCellStr(roomsCell))
                                    .divide(BigDecimal.valueOf((long) roomsConfig.getRooms() * totalDays), dataPrecision, roundingMode)
                                    .multiply(BigDecimal.valueOf(100L));
                            otbRevenue.setOtbOccPercent(occPercent);
                        }
                    }
                }
            } else {
                Row occRow = sheet.getRow(3);
                if (Objects.nonNull(occRow)) {
                    Cell occCell = occRow.getCell(1);
                    if (Objects.nonNull(occCell) && StringUtils.isNotBlank(occCell.toString())) {
                        otbRevenue.setOtbOccPercent(new BigDecimal(CellUtils.convertCellStr(occCell)).setScale(dataPrecision, roundingMode));
                    }
                }
            }

            workbook.close();

            return new OtbAndTransientGroupBO(otbRevenue, transientGroups);
        }

        /**
         * 检查History and Forecast from thirty days ago to yesterday文件
         *
         * @param propertyDTO 资产
         * @param file        文件
         * @author yyt
         * @date 2024/8/15
         **/
        @Override
        public void checkHistoryAndForecastFromThirtyDaysAgoToYesterday(PropertyDTO propertyDTO, MultipartFile file) {
            try (InputStream in = file.getInputStream();
                 Workbook workbook = WorkbookFactory.create(in)) {
                Sheet sheet = workbook.getSheetAt(0);
                Row titleRow = sheet.getRow(0);
                String dateTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(titleRow, "A"));
                String occTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(titleRow, "B"));
                String rateTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(titleRow, "C"));
                String transientDeductRoomsTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(titleRow, "D"));
                String groupDeductRoomsTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(titleRow, "E"));
                DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(BaseConstant.DATE_FORMAT_YYYY_MM_DD);

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

                    Cell dateCell = CellUtils.getCellByColumnLetter(row, "A");
                    if (Objects.isNull(dateCell) || StringUtils.isBlank(dateCell.toString())) {
                        throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY,
                                i18nMessageUtils.getErrorParamMapNotNeedTranslate(dateTitle),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The date cannot be empty");
                    } else {
                        // 格式必须 yyyy-MM-dd
                        try {
                            LocalDate.parse(CellUtils.convertCellStr(dateCell, BaseConstant.DATE_FORMAT_YYYY_MM_DD), dateTimeFormatter);
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.FORMAT_INCORRECT,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(dateTitle, BaseConstant.DATE_FORMAT_YYYY_MM_DD),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The date format error");
                        }
                    }

                    Cell occCell = CellUtils.getCellByColumnLetter(row, "B");
                    if (Objects.nonNull(occCell) && StringUtils.isNotBlank(occCell.toString())) {
                        BigDecimal occ;
                        try {
                            occ = new BigDecimal(CellUtils.convertCellStr(occCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(occTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The occ must be a number");
                        }
                        if (occ.toBigInteger().toString().length() > 6) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(occTitle, 6),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The occ exceed the limit");
                        }
                    }

                    Cell rateCell = CellUtils.getCellByColumnLetter(row, "C");
                    if (Objects.nonNull(rateCell) && StringUtils.isNotBlank(rateCell.toString())) {
                        BigDecimal rate;
                        try {
                            rate = new BigDecimal(CellUtils.convertCellStr(rateCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(rateTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The rate must be a number");
                        }
                        if (rate.toBigInteger().toString().length() > 6) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(rateTitle, 6),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The rate exceed the limit");
                        }
                    }

                    Cell transientDeductRoomsCell = CellUtils.getCellByColumnLetter(row, "D");
                    if (Objects.nonNull(transientDeductRoomsCell) && StringUtils.isNotBlank(transientDeductRoomsCell.toString())) {
                        BigDecimal transientDeductRooms;
                        try {
                            transientDeductRooms = new BigDecimal(CellUtils.convertCellStr(transientDeductRoomsCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(transientDeductRoomsTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The transient deduct rooms must be a number");
                        }
                        if (transientDeductRooms.toBigInteger().toString().length() > 6) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(transientDeductRoomsTitle, 6),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The transient deduct rooms exceed the limit");
                        }
                    }

                    Cell groupDeductRoomsCell = CellUtils.getCellByColumnLetter(row, "E");
                    if (Objects.nonNull(groupDeductRoomsCell) && StringUtils.isNotBlank(groupDeductRoomsCell.toString())) {
                        BigDecimal groupDeductRooms;
                        try {
                            groupDeductRooms = new BigDecimal(CellUtils.convertCellStr(groupDeductRoomsCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(groupDeductRoomsTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The group deduct rooms must be a number");
                        }
                        if (groupDeductRooms.toBigInteger().toString().length() > 6) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(groupDeductRoomsTitle, 6),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The group deduct rooms exceed the limit");
                        }
                    }
                }
            } catch (BadRequestException e) {
                throw e;
            } catch (Exception e) {
                throw new ParseReportException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(), "parsing file failed.", e);
            }
        }

        /**
         * 解析History and Forecast from thirty days ago to yesterday报告
         *
         * @param propertyDTO 资产
         * @param inputStream 文件流
         * @param startDate   开始日期
         * @param endDate     结束日期
         * @return java.util.List<cloud.wconnect.rateshop.pojo.bo.revenue.TransientGroupBO>
         * @author lichen
         * @date 2025/8/5
         */
        @Override
        public List<HistoryAndForecastBO> parseHistoryAndForecastFromThirtyDaysAgoToYesterday(PropertyDTO propertyDTO, InputStream inputStream,
                                                                      Long startDate, Long endDate) throws IOException {
            // 最多31天数据
            List<HistoryAndForecastBO> historyAndForecastBOS = Lists.newArrayListWithExpectedSize(31);
            ZoneId zoneId = ZoneId.of(propertyDTO.getTimezone());
            DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(BaseConstant.DATE_FORMAT_YYYY_MM_DD);
            InternationalizationNumberDTO number = propertyDTO.getInternationalization().getNumber();
            int dataPrecision = BaseConstant.DATA_PRECISION;
            RoundingMode roundingMode = InternationalizationUtils.getRoundingMode(number.getCalculationRule());

            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (Objects.isNull(row)) {
                    continue;
                }

                Cell dateCell = CellUtils.getCellByColumnLetter(row, "A");
                String dateStr = CellUtils.convertCellStr(dateCell, BaseConstant.DATE_FORMAT_YYYY_MM_DD);
                long targetDate = LocalDate.parse(dateStr, dateTimeFormatter).atStartOfDay(zoneId).toEpochSecond();
                if (targetDate < startDate || targetDate > endDate) {
                    continue;
                }

                HistoryAndForecastBO historyAndForecastBO = new HistoryAndForecastBO();
                historyAndForecastBO.setTargetDate(dateStr);

                Cell occCell = CellUtils.getCellByColumnLetter(row, "B");
                if (Objects.nonNull(occCell) && StringUtils.isNotBlank(occCell.toString())) {
                    historyAndForecastBO.setOccPercent(new BigDecimal(CellUtils.convertCellStr(occCell)).setScale(dataPrecision, roundingMode));
                }

                Cell rateCell = CellUtils.getCellByColumnLetter(row, "C");
                if (Objects.nonNull(rateCell) && StringUtils.isNotBlank(rateCell.toString())) {
                    historyAndForecastBO.setAdr(new BigDecimal(CellUtils.convertCellStr(rateCell)).setScale(dataPrecision, roundingMode));
                }

                Cell transientDeductRoomsCell = CellUtils.getCellByColumnLetter(row, "D");
                if (Objects.nonNull(transientDeductRoomsCell) && StringUtils.isNotBlank(transientDeductRoomsCell.toString())) {
                    historyAndForecastBO.setTransientRoomNum(Integer.valueOf(CellUtils.convertCellStr(transientDeductRoomsCell)));
                }

                Cell groupDeductRoomsCell = CellUtils.getCellByColumnLetter(row, "E");
                if (Objects.nonNull(groupDeductRoomsCell) && StringUtils.isNotBlank(groupDeductRoomsCell.toString())) {
                    historyAndForecastBO.setGroupRoomNum(Integer.valueOf(CellUtils.convertCellStr(groupDeductRoomsCell)));
                }

                historyAndForecastBOS.add(historyAndForecastBO);
            }

            workbook.close();

            return historyAndForecastBOS;
        }

        /**
         * 检查 HistoryAndForecastBusinessBlock 文件
         *
         * @param propertyDTO 资产
         * @param file        文件
         * @author yyt
         * @date 2024/8/15
         **/
        @Override
        public void checkHistoryAndForecastBusinessBlock(PropertyDTO propertyDTO, MultipartFile file) {
            try (InputStream in = file.getInputStream();
                 Workbook workbook = WorkbookFactory.create(in)) {
                Sheet sheet = workbook.getSheetAt(0);
                Row titleRow = sheet.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 dateTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(titleRow, "A"));
                String transientDeductRoomsTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(titleRow, "B"));
                String transientDeductRevenueTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(titleRow, "C"));
                String transientNonDeductRoomsTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(titleRow, "D"));
                String transientNonDeductRevenueTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(titleRow, "E"));
                String groupDeductRoomsTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(titleRow, "F"));
                String groupDeductRevenueTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(titleRow, "G"));
                String groupNonDeductRoomsTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(titleRow, "H"));
                String groupNonDeductRevenueTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(titleRow, "I"));
                DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(BaseConstant.DATE_FORMAT_YYYY_MM_DD);

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

                    Cell dateCell = CellUtils.getCellByColumnLetter(row, "A");
                    if (Objects.isNull(dateCell) || StringUtils.isBlank(dateCell.toString())) {
                        throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY,
                                i18nMessageUtils.getErrorParamMapNotNeedTranslate(dateTitle),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The date cannot be empty");
                    } else {
                        // 格式必须 yyyy-MM-dd
                        try {
                            LocalDate.parse(CellUtils.convertCellStr(dateCell, BaseConstant.DATE_FORMAT_YYYY_MM_DD), dateTimeFormatter);
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.FORMAT_INCORRECT,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(dateTitle, BaseConstant.DATE_FORMAT_YYYY_MM_DD),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The date format error");
                        }
                    }

                    Cell transientDeductRoomsCell = CellUtils.getCellByColumnLetter(row, "B");
                    if (Objects.nonNull(transientDeductRoomsCell) && StringUtils.isNotBlank(transientDeductRoomsCell.toString())) {
                        BigDecimal transientDeductRooms;
                        try {
                            transientDeductRooms = new BigDecimal(CellUtils.convertCellStr(transientDeductRoomsCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(transientDeductRoomsTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The transient deduct rooms must be a number");
                        }
                        if (transientDeductRooms.toBigInteger().toString().length() > 6) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(transientDeductRoomsTitle, 6),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The transient deduct rooms exceed the limit");
                        }
                    }

                    Cell transientDeductRevenueCell = CellUtils.getCellByColumnLetter(row, "C");
                    if (Objects.nonNull(transientDeductRevenueCell) && StringUtils.isNotBlank(transientDeductRevenueCell.toString())) {
                        BigDecimal transientDeductRevenue;
                        try {
                            transientDeductRevenue = new BigDecimal(CellUtils.convertCellStr(transientDeductRevenueCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(transientDeductRevenueTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The transient deduct revenue must be a number");
                        }
                        if (transientDeductRevenue.toBigInteger().toString().length() > 6) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(transientDeductRevenueTitle, 6),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The transient deduct revenue exceed the limit");
                        }
                    }

                    Cell transientNonDeductRoomsCell = CellUtils.getCellByColumnLetter(row, "D");
                    if (Objects.nonNull(transientNonDeductRoomsCell) && StringUtils.isNotBlank(transientNonDeductRoomsCell.toString())) {
                        BigDecimal transientNonDeductRooms;
                        try {
                            transientNonDeductRooms = new BigDecimal(CellUtils.convertCellStr(transientNonDeductRoomsCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(transientNonDeductRoomsTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The transient non deduct rooms must be a number");
                        }
                        if (transientNonDeductRooms.toBigInteger().toString().length() > 6) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(transientNonDeductRoomsTitle, 6),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The transient non deduct rooms exceed the limit");
                        }
                    }

                    Cell transientNonDeductRevenueCell = CellUtils.getCellByColumnLetter(row, "E");
                    if (Objects.nonNull(transientNonDeductRevenueCell) && StringUtils.isNotBlank(transientNonDeductRevenueCell.toString())) {
                        BigDecimal transientNonDeductRevenue;
                        try {
                            transientNonDeductRevenue = new BigDecimal(CellUtils.convertCellStr(transientNonDeductRevenueCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(transientNonDeductRevenueTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The transient non deduct revenue must be a number");
                        }
                        if (transientNonDeductRevenue.toBigInteger().toString().length() > 6) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(transientNonDeductRevenueTitle, 6),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The transient non deduct revenue exceed the limit");
                        }
                    }

                    Cell groupDeductRoomsCell = CellUtils.getCellByColumnLetter(row, "F");
                    if (Objects.nonNull(groupDeductRoomsCell) && StringUtils.isNotBlank(groupDeductRoomsCell.toString())) {
                        BigDecimal groupDeductRooms;
                        try {
                            groupDeductRooms = new BigDecimal(CellUtils.convertCellStr(groupDeductRoomsCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(groupDeductRoomsTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The group deduct rooms must be a number");
                        }
                        if (groupDeductRooms.toBigInteger().toString().length() > 6) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(groupDeductRoomsTitle, 6),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The group deduct rooms exceed the limit");
                        }
                    }

                    Cell groupDeductRevenueCell = CellUtils.getCellByColumnLetter(row, "G");
                    if (Objects.nonNull(groupDeductRevenueCell) && StringUtils.isNotBlank(groupDeductRevenueCell.toString())) {
                        BigDecimal groupDeductRevenue;
                        try {
                            groupDeductRevenue = new BigDecimal(CellUtils.convertCellStr(groupDeductRevenueCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(groupDeductRevenueTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The group deduct revenue must be a number");
                        }
                        if (groupDeductRevenue.toBigInteger().toString().length() > 6) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(groupDeductRevenueTitle, 6),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The group deduct revenue exceed the limit");
                        }
                    }

                    Cell groupNonDeductRoomsCell = CellUtils.getCellByColumnLetter(row, "H");
                    if (Objects.nonNull(groupNonDeductRoomsCell) && StringUtils.isNotBlank(groupNonDeductRoomsCell.toString())) {
                        BigDecimal groupNonDeductRooms;
                        try {
                            groupNonDeductRooms = new BigDecimal(CellUtils.convertCellStr(groupNonDeductRoomsCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(groupNonDeductRoomsTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The group non deduct rooms must be a number");
                        }
                        if (groupNonDeductRooms.toBigInteger().toString().length() > 6) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(groupNonDeductRoomsTitle, 6),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The group non deduct rooms exceed the limit");
                        }
                    }

                    Cell groupNonDeductRevenueCell = CellUtils.getCellByColumnLetter(row, "I");
                    if (Objects.nonNull(groupNonDeductRevenueCell) && StringUtils.isNotBlank(groupNonDeductRevenueCell.toString())) {
                        BigDecimal groupNonDeductRevenue;
                        try {
                            groupNonDeductRevenue = new BigDecimal(CellUtils.convertCellStr(groupNonDeductRevenueCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(groupNonDeductRevenueTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The group non deduct revenue must be a number");
                        }
                        if (groupNonDeductRevenue.toBigInteger().toString().length() > 6) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(groupNonDeductRevenueTitle, 6),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The group non deduct revenue exceed the limit");
                        }
                    }
                }

            } catch (BadRequestException e) {
                throw e;
            } catch (Exception e) {
                throw new ParseReportException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(), "parsing file failed.", e);
            }
        }

        /**
         * 解析 HistoryAndForecastBusinessBlock 报告
         *
         * @param propertyDTO 资产
         * @param inputStream 文件流
         * @param startDate   开始日期
         * @param endDate     结束日期
         * @return TransientGroupAdrBO
         * @author yyt
         * @date 2024/8/15
         **/
        @Override
        public List<TransientGroupAdrBO> parseHistoryAndForecastBusinessBlock(PropertyDTO propertyDTO, InputStream inputStream,
                                                                             Long startDate, Long endDate) throws IOException {
            // 获取 adr 配置
            RevenueAdrConfig revenueAdrConfig = revenueAdrConfigService.get(propertyDTO.getId());
            boolean checkTransientDeduct = Objects.nonNull(revenueAdrConfig) && revenueAdrConfig.getCheckTransientDeduct();
            boolean checkTransientNonDeduct = Objects.nonNull(revenueAdrConfig) && revenueAdrConfig.getCheckTransientNonDeduct();
            boolean checkGroupDeduct = Objects.nonNull(revenueAdrConfig) && revenueAdrConfig.getCheckGroupDeduct();
            boolean checkGroupNonDeduct = Objects.nonNull(revenueAdrConfig) && revenueAdrConfig.getCheckGroupNonDeduct();

            InternationalizationNumberDTO number = propertyDTO.getInternationalization().getNumber();
            int dataPrecision = BaseConstant.DATA_PRECISION;
            RoundingMode roundingMode = InternationalizationUtils.getRoundingMode(number.getCalculationRule());

            ZoneId zoneId = ZoneId.of(propertyDTO.getTimezone());
            DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(BaseConstant.DATE_FORMAT_YYYY_MM_DD);
            List<TransientGroupAdrBO> transientGroupList = Lists.newArrayListWithExpectedSize(14);

            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (Objects.isNull(row)) {
                    continue;
                }

                Cell dateCell = CellUtils.getCellByColumnLetter(row, "A");
                String dateStr = CellUtils.convertCellStr(dateCell, BaseConstant.DATE_FORMAT_YYYY_MM_DD);
                long targetDate = DateTimeUtils.toTimestamp(LocalDate.parse(dateStr, dateTimeFormatter)
                        .atStartOfDay(zoneId));
                if (targetDate < startDate || targetDate > endDate) {
                    continue;
                }

                TransientGroupAdrBO transientGroup = new TransientGroupAdrBO();
                transientGroup.setTargetDate(dateStr);

                // 计算并设置 Transient ADR
                String deductRoomsStr = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "B"));
                String deductRoomRevStr = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "C"));
                String nonDeductRoomsStr = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "D"));
                String nonDeductRoomRevStr = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "E"));

                Integer transientRoomNum = null;
                BigDecimal transientRoomRevenue = null;
                if (checkTransientDeduct && !deductRoomsStr.isEmpty()) {
                    transientRoomNum = Integer.parseInt(deductRoomsStr);
                }
                if (checkTransientDeduct && !deductRoomRevStr.isEmpty()) {
                    transientRoomRevenue = new BigDecimal(deductRoomRevStr);
                }

                if (checkTransientNonDeduct && !nonDeductRoomsStr.isEmpty()) {
                    int nonDeductRooms = Integer.parseInt(nonDeductRoomsStr);
                    transientRoomNum = Objects.isNull(transientRoomNum) ? nonDeductRooms : transientRoomNum + nonDeductRooms;
                }
                if (checkTransientNonDeduct && !nonDeductRoomRevStr.isEmpty()) {
                    BigDecimal nonDeductRoomRev = new BigDecimal(nonDeductRoomRevStr);
                    transientRoomRevenue = Objects.isNull(transientRoomRevenue) ? nonDeductRoomRev : transientRoomRevenue.add(nonDeductRoomRev);
                }

                if (Objects.nonNull(transientRoomRevenue) && Objects.nonNull(transientRoomNum) && transientRoomNum != BaseConstant.ZERO) {
                    BigDecimal transientAdr = transientRoomRevenue.divide(BigDecimal.valueOf(transientRoomNum), dataPrecision, roundingMode);
                    transientGroup.setTransientAdr(transientAdr);
                }

                // 计算并设置 Group ADR
                String groupDeductRoomsStr = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "F"));
                String groupDeductRoomRevStr = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "G"));
                String groupNonDeductRoomsStr = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "H"));
                String groupNonDeductRoomRevStr = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "I"));

                Integer groupRoomNum = null;
                BigDecimal groupRoomRevenue = null;
                if (checkGroupDeduct && !groupDeductRoomsStr.isEmpty()) {
                    groupRoomNum = Integer.parseInt(groupDeductRoomsStr);
                }
                if (checkGroupDeduct && !groupDeductRoomRevStr.isEmpty()) {
                    groupRoomRevenue = new BigDecimal(groupDeductRoomRevStr);
                }

                if (checkGroupNonDeduct && !groupNonDeductRoomsStr.isEmpty()) {
                    int groupNonDeductRooms = Integer.parseInt(groupNonDeductRoomsStr);
                    groupRoomNum = Objects.isNull(groupRoomNum) ? groupNonDeductRooms : groupRoomNum + groupNonDeductRooms;
                }
                if (checkGroupNonDeduct && !groupNonDeductRoomRevStr.isEmpty()) {
                    BigDecimal groupNonDeductRoomRev = new BigDecimal(groupNonDeductRoomRevStr);
                    groupRoomRevenue = Objects.isNull(groupRoomRevenue) ? groupNonDeductRoomRev : groupRoomRevenue.add(groupNonDeductRoomRev);
                }

                if (Objects.nonNull(groupRoomRevenue) && Objects.nonNull(groupRoomNum) && groupRoomNum != BaseConstant.ZERO) {
                    BigDecimal groupAdr = groupRoomRevenue.divide(BigDecimal.valueOf(groupRoomNum), dataPrecision, roundingMode);
                    transientGroup.setGroupAdr(groupAdr);
                }

                transientGroupList.add(transientGroup);
            }

            workbook.close();

            return transientGroupList;
        }

        /**
         * 检查 Segment 文件
         *
         * @param propertyDTO 资产
         * @param file        文件
         * @author yyt
         * @date 2024/8/15
         **/
        @Override
        public void checkSegmentFile(PropertyDTO propertyDTO, MultipartFile file) {
            // 获取当前资产配置的 segment
            Map<String, Integer> segmentIdByName = revenueSegmentService.listActiveSegment(propertyDTO.getId())
                    .stream().collect(Collectors.toMap(RevenueSegment::getSegment, RevenueSegment::getId));
            if (segmentIdByName.isEmpty()) {
                throw new BadRequestException(CommonErrorI18nConstant.NOT_FOUND,
                        i18nMessageUtils.getErrorParamMapNeedTranslate(CommonFieldI18nConstant.SEGMENT),
                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                        "Please configure segment first");
            }

            DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(BaseConstant.DATE_FORMAT_YYYY_MM_DD);

            try (InputStream in = file.getInputStream();
                 Workbook workbook = WorkbookFactory.create(in)) {
                Sheet sheet = workbook.getSheetAt(0);
                Row titleRow = sheet.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");
                }

                for (int i = 1; i < titleRow.getLastCellNum(); i++) {
                    Cell cell = titleRow.getCell(i);
                    String name = CellUtils.convertCellStr(cell);
                    if (StringUtils.isEmpty(name)) {
                        throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY,
                                i18nMessageUtils.getErrorParamMapNeedTranslate(CommonFieldI18nConstant.SEGMENT),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The segment must not be empty");
                    }

                    Integer segmentId = segmentIdByName.get(name);
                    if (Objects.isNull(segmentId)) {
                        throw new BadRequestException(CommonErrorI18nConstant.DATA_NOT_EXIST,
                                i18nMessageUtils.getErrorParamMapNotNeedTranslate(name),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The segment is not found;segment:" + name);
                    }
                    // 连续跨 2 列
                    i++;
                }

                Row roomsAndRevenueRow = sheet.getRow(1);
                String roomsTitle = CellUtils.convertCellStr(roomsAndRevenueRow.getCell(1));
                String revenueTitle = CellUtils.convertCellStr(roomsAndRevenueRow.getCell(2));

                for (int i = 2; i <= sheet.getLastRowNum(); i++) {
                    Row row = sheet.getRow(i);
                    if (Objects.isNull(row)) continue;

                    Cell dateCell = row.getCell(0);
                    if (Objects.isNull(dateCell) || StringUtils.isBlank(dateCell.toString())) {
                        throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY,
                                i18nMessageUtils.getErrorParamMapNeedTranslate(CommonFieldI18nConstant.DATE),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The date must not be null");
                    } else {
                        try {
                            LocalDate.parse(CellUtils.convertCellStr(dateCell, BaseConstant.DATE_FORMAT_YYYY_MM_DD), dateTimeFormatter);
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.FORMAT_INCORRECT,
                                    i18nMessageUtils.getErrorParamMapNeedTranslate(CommonFieldI18nConstant.DATE, BaseConstant.DATE_FORMAT_YYYY_MM_DD),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The date format error");
                        }
                    }

                    for (int j = 1; j < row.getLastCellNum(); j++) {
                        Cell roomsCell = row.getCell(j);
                        if (Objects.nonNull(roomsCell) && StringUtils.isNotBlank(roomsCell.toString())) {
                            BigDecimal rooms;
                            try {
                                rooms = new BigDecimal(CellUtils.convertCellStr(roomsCell));
                            } catch (Exception e) {
                                throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                        i18nMessageUtils.getErrorParamMapNotNeedTranslate(roomsTitle),
                                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                        "The rooms must be a number");
                            }
                            if (rooms.toBigInteger().toString().length() > 6) {
                                throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                        i18nMessageUtils.getErrorParamMapNotNeedTranslate(roomsTitle, 6),
                                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                        "The rooms exceed the limit");
                            }
                        }

                        Cell revenueCell = row.getCell(j + 1);
                        if (Objects.nonNull(revenueCell) && StringUtils.isNotBlank(revenueCell.toString())) {
                            BigDecimal revenue;
                            try {
                                revenue = new BigDecimal(CellUtils.convertCellStr(revenueCell));
                            } catch (Exception e) {
                                throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                        i18nMessageUtils.getErrorParamMapNotNeedTranslate(revenueTitle),
                                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                        "The revenue must be a number");
                            }
                            if (revenue.toBigInteger().toString().length() > 10) {
                                throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                        i18nMessageUtils.getErrorParamMapNotNeedTranslate(revenueTitle, 10),
                                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                        "The revenue exceed the limit");
                            }
                        }
                        j++;
                    }
                }

            } catch (BadRequestException e) {
                throw e;
            } catch (Exception e) {
                throw new InternalServerErrorException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(), "parse file failed", e);
            }
        }

        /**
         * 检查 CustomersRevenue 文件
         *
         * @param propertyDTO 资产
         * @param file        文件
         * @param sheetNo     sheet no
         * @author yyt
         * @date 2024/8/15
         **/
        @Override
        public void checkCustomersRevenueFile(PropertyDTO propertyDTO, MultipartFile file, int sheetNo) {
            try (InputStream in = file.getInputStream();
                 Workbook workbook = WorkbookFactory.create(in)) {
                Sheet sheet = workbook.getSheetAt(sheetNo);
                Row titleRow = sheet.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");
                }

                DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(BaseConstant.DATE_FORMAT_YYYY_MM_DD);
                String transientRevenueTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(titleRow, "B"));
                String transientRoomsTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(titleRow, "C"));
                String groupRevenueTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(titleRow, "D"));
                String groupRoomsTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(titleRow, "E"));
                String contractRevenueTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(titleRow, "F"));
                String contractRoomsTitle = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(titleRow, "G"));

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

                    Cell dateCell = row.getCell(0);
                    if (Objects.isNull(dateCell) || StringUtils.isBlank(dateCell.toString())) {
                        throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY,
                                i18nMessageUtils.getErrorParamMapNeedTranslate(CommonFieldI18nConstant.DATE),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The date must not be null");
                    } else {
                        try {
                            LocalDate.parse(CellUtils.convertCellStr(dateCell, BaseConstant.DATE_FORMAT_YYYY_MM_DD), dateTimeFormatter);
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.FORMAT_INCORRECT,
                                    i18nMessageUtils.getErrorParamMapNeedTranslate(CommonFieldI18nConstant.DATE, BaseConstant.DATE_FORMAT_YYYY_MM_DD),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The date format error");
                        }
                    }

                    Cell transientRevenueCell = CellUtils.getCellByColumnLetter(row, "B");
                    if (Objects.nonNull(transientRevenueCell) && StringUtils.isNotBlank(transientRevenueCell.toString())) {
                        BigDecimal transientRevenue;
                        try {
                            transientRevenue = new BigDecimal(CellUtils.convertCellStr(transientRevenueCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(transientRevenueTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The transient revenue must be a number");
                        }
                        if (transientRevenue.toBigInteger().toString().length() > 10) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(transientRevenueTitle, 10),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The transient revenue exceed the limit");
                        }
                    }

                    Cell transientRoomsCell = CellUtils.getCellByColumnLetter(row, "C");
                    if (Objects.nonNull(transientRoomsCell) && StringUtils.isNotBlank(transientRoomsCell.toString())) {
                        BigDecimal transientRooms;
                        try {
                            transientRooms = new BigDecimal(CellUtils.convertCellStr(transientRoomsCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(transientRoomsTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The transient rooms must be a number");
                        }
                        if (transientRooms.toBigInteger().toString().length() > 6) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(transientRoomsTitle, 6),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The transient rooms exceed the limit");
                        }
                    }

                    Cell groupRevenueCell = CellUtils.getCellByColumnLetter(row, "D");
                    if (Objects.nonNull(groupRevenueCell) && StringUtils.isNotBlank(groupRevenueCell.toString())) {
                        BigDecimal groupRevenue;
                        try {
                            groupRevenue = new BigDecimal(CellUtils.convertCellStr(groupRevenueCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(groupRevenueTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The group revenue must be a number");
                        }
                        if (groupRevenue.toBigInteger().toString().length() > 10) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(groupRevenueTitle, 10),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The group revenue exceed the limit");
                        }
                    }

                    Cell groupRoomsCell = CellUtils.getCellByColumnLetter(row, "E");
                    if (Objects.nonNull(groupRoomsCell) && StringUtils.isNotBlank(groupRoomsCell.toString())) {
                        BigDecimal groupRooms;
                        try {
                            groupRooms = new BigDecimal(CellUtils.convertCellStr(groupRoomsCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(groupRoomsTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The group rooms must be a number");
                        }
                        if (groupRooms.toBigInteger().toString().length() > 6) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(groupRoomsTitle, 6),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The group rooms exceed the limit");
                        }
                    }

                    Cell contractRevenueCell = CellUtils.getCellByColumnLetter(row, "F");
                    if (Objects.nonNull(contractRevenueCell) && StringUtils.isNotBlank(contractRevenueCell.toString())) {
                        BigDecimal contractRevenue;
                        try {
                            contractRevenue = new BigDecimal(CellUtils.convertCellStr(contractRevenueCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(contractRevenueTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The contract revenue must be a number");
                        }
                        if (contractRevenue.toBigInteger().toString().length() > 10) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(contractRevenueTitle, 10),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The contract revenue exceed the limit");
                        }
                    }

                    Cell contractRoomsCell = CellUtils.getCellByColumnLetter(row, "G");
                    if (Objects.nonNull(contractRoomsCell) && StringUtils.isNotBlank(contractRoomsCell.toString())) {
                        BigDecimal contractRooms;
                        try {
                            contractRooms = new BigDecimal(CellUtils.convertCellStr(contractRoomsCell));
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(contractRoomsTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The contract rooms must be a number");
                        }
                        if (contractRooms.toBigInteger().toString().length() > 6) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(contractRoomsTitle, 6),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The contract rooms exceed the limit");
                        }
                    }
                }

            } catch (BadRequestException e) {
                throw e;
            } catch (Exception e) {
                throw new InternalServerErrorException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(), "parse file failed", e);
            }
        }

        /**
         * 解析 Segment 报告
         *
         * @param propertyDTO 资产
         * @param inputStream 文件流
         * @param startDate   开始日期
         * @param endDate     结束日期
         * @return RoomsAndRevenueBO
         * @author yyt
         * @date 2024/8/15
         **/
        @Override
        public List<RoomsAndRevenueBO> parseSegmentFile(PropertyDTO propertyDTO, InputStream inputStream, Long startDate, Long endDate) throws IOException {
            Integer propertyId = propertyDTO.getId();
            List<RevenueSegment> segments = revenueSegmentService.listActiveSegment(propertyId);

            // 解析文件数据
            Map<String, Integer> segmentIdByName = segments.stream().collect(Collectors.toMap(RevenueSegment::getSegment,
                    RevenueSegment::getId));
            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            Row titelRow = sheet.getRow(0);
            Map<Integer, Integer> indexBySegmentId = Maps.newLinkedHashMapWithExpectedSize(titelRow.getLastCellNum());
            for (int i = 1; i < titelRow.getLastCellNum(); i++) {
                Cell cell = titelRow.getCell(i);
                String name = cell.getStringCellValue();
                Integer segmentId = segmentIdByName.get(name);
                indexBySegmentId.put(segmentId, i);
                // 连续跨 2 列
                i++;
            }

            String timezone = propertyDTO.getTimezone();
            ZoneId zoneId = ZoneId.of(timezone);
            DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(BaseConstant.DATE_FORMAT_YYYY_MM_DD);
            int size = segments.size();
            Map<Integer, BigDecimal> revenueBySegmentId = Maps.newHashMapWithExpectedSize(size);
            Map<Integer, Integer> roomsBySegmentId = Maps.newHashMapWithExpectedSize(size);
            for (int i = 2; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (Objects.isNull(row)) continue;

                String date = CellUtils.convertCellStr(row.getCell(0), BaseConstant.DATE_FORMAT_YYYY_MM_DD);
                long targetDate = LocalDate.parse(date, dateTimeFormatter).atStartOfDay(zoneId).toEpochSecond();
                if (targetDate < startDate || targetDate > endDate) {
                    continue;
                }

                indexBySegmentId.forEach((segmentId, index) -> {
                    String roomsStr = CellUtils.convertCellStr(row.getCell(index));
                    int rooms = StringUtils.isEmpty(roomsStr) ? BaseConstant.ZERO : Integer.parseInt(roomsStr);
                    roomsBySegmentId.put(segmentId, roomsBySegmentId.getOrDefault(segmentId, 0) + rooms);

                    String revenue = CellUtils.convertCellStr(row.getCell(index + 1));
                    revenueBySegmentId.put(segmentId, revenueBySegmentId.getOrDefault(segmentId, BigDecimal.ZERO)
                            .add(StringUtils.isEmpty(revenue) ? BigDecimal.ZERO : new BigDecimal(revenue)));
                });
            }

            workbook.close();

            if (MapUtils.isEmpty(revenueBySegmentId) && MapUtils.isEmpty(roomsBySegmentId)) {
                return Collections.emptyList();
            }

            return segments.stream().map(item -> {
                Integer id = item.getId();
                return new RoomsAndRevenueBO(id, roomsBySegmentId.get(id), revenueBySegmentId.get(id));
            }).collect(Collectors.toList());
        }

        /**
         * 解析 CustomersRevenue 报告
         *
         * @param propertyDTO 资产
         * @param inputStream 文件流
         * @param sheetNo     sheet no
         * @param startDate   开始日期
         * @param endDate     结束日期
         * @return CustomersDTO
         * @author yyt
         * @date 2024/8/15
         **/
        @Override
        public CustomersDTO parseCustomersRevenueFile(PropertyDTO propertyDTO, InputStream inputStream, int sheetNo,
                                                      Long startDate, Long endDate) throws IOException {
            BigDecimal transientRevenue = null;
            Integer transientRooms = null;
            BigDecimal groupRevenue = null;
            Integer groupRooms = null;
            BigDecimal contractRevenue = null;
            Integer contractRooms = null;

            String timezone = propertyDTO.getTimezone();
            ZoneId zoneId = ZoneId.of(timezone);
            DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(BaseConstant.DATE_FORMAT_YYYY_MM_DD);
            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheetAt(sheetNo);
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (Objects.isNull(row)) continue;

                String date = CellUtils.convertCellStr(row.getCell(0), BaseConstant.DATE_FORMAT_YYYY_MM_DD);
                long targetDate = LocalDate.parse(date, dateTimeFormatter).atStartOfDay(zoneId).toEpochSecond();
                if (targetDate < startDate || targetDate > endDate) {
                    continue;
                }

                String transientRevenueStr = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "B"));
                if (StringUtils.isNotEmpty(transientRevenueStr)) {
                    if (Objects.isNull(transientRevenue)) {
                        transientRevenue = BigDecimal.ZERO;
                    }
                    transientRevenue = transientRevenue.add(new BigDecimal(transientRevenueStr));
                }

                String transientRoomsStr = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "C"));
                if (StringUtils.isNotEmpty(transientRoomsStr)) {
                    if (Objects.isNull(transientRooms)) {
                        transientRooms = BaseConstant.ZERO;
                    }
                    transientRooms += Integer.parseInt(transientRoomsStr);
                }

                String groupRevenueStr = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "D"));
                if (StringUtils.isNotEmpty(groupRevenueStr)) {
                    if (Objects.isNull(groupRevenue)) {
                        groupRevenue = BigDecimal.ZERO;
                    }
                    groupRevenue = groupRevenue.add(new BigDecimal(groupRevenueStr));
                }

                String groupRoomsStr = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "E"));
                if (StringUtils.isNotEmpty(groupRoomsStr)) {
                    if (Objects.isNull(groupRooms)) {
                        groupRooms = BaseConstant.ZERO;
                    }
                    groupRooms += Integer.parseInt(groupRoomsStr);
                }

                String contractRevenueStr = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "F"));
                if (StringUtils.isNotEmpty(contractRevenueStr)) {
                    if (Objects.isNull(contractRevenue)) {
                        contractRevenue = BigDecimal.ZERO;
                    }
                    contractRevenue = contractRevenue.add(new BigDecimal(contractRevenueStr));
                }

                String contractRoomsStr = CellUtils.convertCellStr(CellUtils.getCellByColumnLetter(row, "G"));
                if (StringUtils.isNotEmpty(contractRoomsStr)) {
                    if (Objects.isNull(contractRooms)) {
                        contractRooms = BaseConstant.ZERO;
                    }
                    contractRooms += Integer.parseInt(contractRoomsStr);
                }
            }

            workbook.close();

            return new CustomersDTO(transientRevenue, transientRooms, groupRevenue, groupRooms, contractRevenue, contractRooms);
        }

    }