# RevenueBudgetOtbServiceImpl.java

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

    import cloud.wconnect.commonlib.component.BeanMapper;
    import cloud.wconnect.commonlib.cons.BaseConstant;
    import cloud.wconnect.commonlib.pojo.dto.PropertyDTO;
    import cloud.wconnect.commonlib.pojo.dto.internationalization.InternationalizationDTO;
    import cloud.wconnect.commonlib.pojo.dto.internationalization.InternationalizationNumberDTO;
    import cloud.wconnect.commonlib.redis.PropertyI18nRedisService;
    import cloud.wconnect.commonlib.util.*;
    import cloud.wconnect.rateshop.cons.revenue.BudgetFileTitleCons;
    import cloud.wconnect.rateshop.entity.revenue.RevenueBudgetOtb;
    import cloud.wconnect.rateshop.entity.revenue.RevenueBudgetSegment;
    import cloud.wconnect.rateshop.entity.revenue.RevenueOtbRevenueLastYear;
    import cloud.wconnect.rateshop.entity.revenue.RevenueSegment;
    import cloud.wconnect.rateshop.mapper.revenue.RevenueBudgetOtbMapper;
    import cloud.wconnect.rateshop.mapper.revenue.RevenueOtbRevenueLastYearMapper;
    import cloud.wconnect.rateshop.pojo.dto.revenue.BudgetDTO;
    import cloud.wconnect.rateshop.service.revenue.IRevenueBudgetOtbService;
    import cloud.wconnect.rateshop.service.revenue.IRevenueBudgetSegmentService;
    import cloud.wconnect.rateshop.service.revenue.IRevenueSegmentService;
    import cloud.wconnect.rateshop.service.revenue.IRevenueSendEventService;
    import com.baomidou.dynamic.datasource.annotation.DS;
    import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
    import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
    import com.google.common.collect.Lists;
    import com.google.common.collect.Maps;
    import org.apache.commons.collections.CollectionUtils;
    import org.apache.commons.lang3.StringUtils;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.context.i18n.LocaleContextHolder;
    import org.springframework.stereotype.Service;
    import org.springframework.transaction.annotation.Transactional;

    import java.math.BigDecimal;
    import java.math.RoundingMode;
    import java.time.ZonedDateTime;
    import java.util.*;
    import java.util.concurrent.Executor;
    import java.util.stream.Collectors;

    /**
     * <p>
     *  服务实现类
     * </p>
     *
     * @author yyt
     * @since 2023-10-26
     */
    @Service
    @DS("rate_shop")
    public class RevenueBudgetOtbServiceImpl extends ServiceImpl<RevenueBudgetOtbMapper, RevenueBudgetOtb> implements IRevenueBudgetOtbService {

        @Autowired
        private BeanMapper beanMapper;
        @Autowired
        private Executor executor;
        @Autowired
        private IRevenueSendEventService revenueSendEventService;
        @Autowired
        private IRevenueBudgetSegmentService revenueBudgetSegmentService;
        @Autowired
        private IRevenueSegmentService revenueSegmentService;
        @Autowired
        private PropertyI18nRedisService propertyI18nRedisService;
        @Autowired
        private I18nMessageUtils i18nMessageUtils;
        @Autowired
        private RevenueOtbRevenueLastYearMapper revenueOtbRevenueLastYearMapper;

        /**
         * 获取 budget
         *
         * @param propertyDTO 资产
         * @param date        日期
         * @return budget
         * @author yyt
         * @date 2023/10/27
         */
        @Override
        public BudgetDTO getBudgetDTO(PropertyDTO propertyDTO, long date) {
            ZonedDateTime targetDate = DateTimeUtils.toZonedDateTime(date, propertyDTO.getTimezone());
            RevenueBudgetOtb budget = getOne(new QueryWrapper<RevenueBudgetOtb>().lambda()
                    .eq(RevenueBudgetOtb::getPropertyId, propertyDTO.getId())
                    .eq(RevenueBudgetOtb::getTheYear, targetDate.getYear())
                    .eq(RevenueBudgetOtb::getTheMonth, targetDate.getMonthValue()));
            return Objects.isNull(budget) ? new BudgetDTO() : beanMapper.map(budget, BudgetDTO.class);
        }

        /**
         * 保存 budget
         *
         * @param propertyDTO 资产
         * @param targetDate 目标日期
         * @param budget     budget
         * @param userId     操作人
         * @author yyt
         * @date 2023/10/27
         */
        @Override
        @Transactional(rollbackFor = Exception.class)
        public void save(PropertyDTO propertyDTO, Long targetDate, BudgetDTO budget, int userId) {
            RevenueBudgetOtb updateBudget = beanMapper.map(budget, RevenueBudgetOtb.class);
            ZonedDateTime date = DateTimeUtils.toZonedDateTime(targetDate, propertyDTO.getTimezone());
            long now = DateTimeUtils.nowTimestamp();
            updateBudget.setPropertyId(propertyDTO.getId()).setTheYear(date.getYear()).setTheMonth(date.getMonthValue())
                    .setCreatedBy(userId).setCreatedTime(now).setUpdatedBy(userId).setUpdatedTime(now);

            InternationalizationNumberDTO number = propertyI18nRedisService.get(propertyDTO.getId()).getInternationalization().getNumber();
            int calculatePrecision = BaseConstant.DATA_PRECISION;
            RoundingMode roundingMode = InternationalizationUtils.getRoundingMode(number.getCalculationRule());

            if (Objects.nonNull(updateBudget.getOccPercent()) && Objects.nonNull(updateBudget.getAdr())) {
                // revpar = occ * adr
                updateBudget.setRevPar(updateBudget.getOccPercent().multiply(updateBudget.getAdr())
                        .divide(BigDecimal.valueOf(100L), calculatePrecision, roundingMode));
            } else {
                updateBudget.setRevPar(null);
            }
            baseMapper.saveOrUpdate(updateBudget);

            // budget 改动发送事件
            TransactionUtils.afterCommitAsyncExecute(executor, () ->
                    revenueSendEventService.sendBudgetEvent(propertyDTO, Lists.newArrayList(updateBudget)));
        }

        /**
         * 导入 budget 文件
         *
         * @param propertyDTO 资产
         * @param year       选择年
         * @param workbook   文件
         * @param userId     操作人
         * @author yyt
         * @date 2023/10/28
         */
        @Override
        @Transactional(rollbackFor = Exception.class)
        public void importBudgetFile(PropertyDTO propertyDTO, int year, Workbook workbook, int userId) {
            Sheet sheet = workbook.getSheetAt(0);
            // 获取资产 segment
            Integer propertyId = propertyDTO.getId();
            List<RevenueSegment> segments = revenueSegmentService.listActiveSegment(propertyId);
            Map<String, Integer> segmentIdBySegment = segments.stream()
                    .collect(Collectors.toMap(RevenueSegment::getSegment, RevenueSegment::getId));

            Locale locale = LocaleContextHolder.getLocale();

            // 取第一个单元格中的标题值
            int startRowIdx = 0;
            Row monthRow = sheet.getRow(startRowIdx);
            Cell colCell = monthRow.getCell(0);
            String colValue = CellUtils.convertCellStr(colCell);
            if (StringUtils.equals(i18nMessageUtils.message(BudgetFileTitleCons.MONTH, locale), colValue)) {
                // 下移一行
                startRowIdx++;

                Row segmentRow = sheet.getRow(startRowIdx);
                if (segmentRow != null) {
                    colCell = segmentRow.getCell(0);
                    colValue = CellUtils.convertCellStr(colCell);
                    if (StringUtils.equals(i18nMessageUtils.message(BudgetFileTitleCons.SEGMENT, locale), colValue)) {
                        // 下移一行
                        startRowIdx++;
                        List<RevenueBudgetSegment> budgetSegments = Lists.newArrayList();
                        startRowIdx = packageBudgetSegmentList(propertyId, year, sheet, startRowIdx, segmentIdBySegment,
                                budgetSegments, userId);
                        if (!budgetSegments.isEmpty()) {
                            revenueBudgetSegmentService.saveOrUpdateBatchRewrite(budgetSegments);
                        }
                    }
                }

                Row otbRevenueRow = sheet.getRow(startRowIdx);
                if (otbRevenueRow != null) {
                    colCell = otbRevenueRow.getCell(0);
                    colValue = CellUtils.convertCellStr(colCell);
                    if (StringUtils.equals(i18nMessageUtils.message(BudgetFileTitleCons.OTB_REVENUE, locale), colValue)) {
                        // 往下移一行，开始解析 OTB Budget Revenue 数据
                        startRowIdx++;
                        // 生成Map: key->yearAndMonth(example: 202301), value -> OTB Revenue Budget
                        Map<Integer, RevenueBudgetOtb> budgetOtbByYearMonth = packageBudgetOtbByYearAndMonth(propertyId, year, sheet, startRowIdx);
                        List<RevenueBudgetOtb> budgetOtbs = convert2BudgetOtbList(propertyId, year, budgetOtbByYearMonth, userId);
                        if (!budgetOtbs.isEmpty()) {
                            budgetOtbs.sort(Comparator.comparing(RevenueBudgetOtb::getTheYear).thenComparing(RevenueBudgetOtb::getTheMonth));
                            baseMapper.saveOrUpdateBatch(budgetOtbs);

                            // budget 变动，发送事件
                            TransactionUtils.afterCommitAsyncExecute(executor, () -> revenueSendEventService.sendBudgetEvent(propertyDTO, budgetOtbs));
                        }

                        // 开始解析 last year
                        Map<Integer, RevenueOtbRevenueLastYear> lastYearOtbByYearMonth = packageLastYearOtbByYearAndMonth(propertyId, year - 1, sheet, startRowIdx);
                        List<RevenueOtbRevenueLastYear> lastYearOtbs = convert2LastYearOtbList(propertyId, year - 1, lastYearOtbByYearMonth, userId);
                        if(CollectionUtils.isNotEmpty(lastYearOtbs)) {
                            lastYearOtbs.sort(Comparator.comparing(RevenueOtbRevenueLastYear::getTheYear)
                                    .thenComparing(RevenueOtbRevenueLastYear::getTheMonth));
                            revenueOtbRevenueLastYearMapper.saveOrUpdateBatch(lastYearOtbs);
                        }
                    }
                }
            }
        }

        /**
         * 封装 Budget Segment 列表
         *
         * @param propertyId         资产 id
         * @param year               指定年份
         * @param sheet              Excel表内容
         * @param startRowIndex      起始行索引
         * @param segmentIdBySegment Map: key->Segment名称 value->Segment ID
         * @param budgetSegments     封装的 Budget Segment 可变列表
         * @param userId             操作人
         * @return 遍历后的行索引
         * @author yyt
         * @date 2023/10/28
         */
        private int packageBudgetSegmentList(Integer propertyId, int year, Sheet sheet, int startRowIndex,
                                             Map<String, Integer> segmentIdBySegment, List<RevenueBudgetSegment> budgetSegments,
                                             int userId) {
            long now = DateTimeUtils.nowTimestamp();
            InternationalizationNumberDTO number = propertyI18nRedisService.get(propertyId).getInternationalization().getNumber();
            int dataPrecision = BaseConstant.DATA_PRECISION;
            RoundingMode roundingMode = InternationalizationUtils.getRoundingMode(number.getCalculationRule());
            Locale locale = LocaleContextHolder.getLocale();

            while (true) {
                Row segmentRow = sheet.getRow(startRowIndex);
                if (segmentRow == null) {
                    break;
                }

                // 取第一个单元格中的标题值
                Cell colCell = segmentRow.getCell(0);
                String colValue = CellUtils.convertCellStr(colCell);
                if (StringUtils.equals(i18nMessageUtils.message(BudgetFileTitleCons.OTB_REVENUE, locale), colValue)
                        || StringUtils.isBlank(colValue)) {
                    break;
                }

                Integer segmentId = segmentIdBySegment.get(colValue);
                if (Objects.nonNull(segmentId)) {
                    // 从第2列开始，循环获取每个月的 Segment Revenue
                    int maxColIdx = 12;
                    for (int colIdx = 1; colIdx <= maxColIdx; colIdx++) {
                        Cell revenueCell = segmentRow.getCell(colIdx);
                        String revenueStr = CellUtils.convertCellStr(revenueCell);
                        if (StringUtils.isNotBlank(revenueStr)) {
                            BigDecimal revenue = new BigDecimal(revenueStr);
                            budgetSegments.add(new RevenueBudgetSegment().setPropertyId(propertyId).setTheYear(year)
                                    .setTheMonth(colIdx).setSegmentId(segmentId).setBudgetRevenue(revenue.setScale(dataPrecision, roundingMode))
                                    .setCreatedBy(userId).setCreatedTime(now).setUpdatedBy(userId).setUpdatedTime(now));
                        }
                    }
                }

                startRowIndex++;
            }
            return startRowIndex;
        }

        /**
         * 封装 budget otb year month
         *
         * @param propertyId    资产 id
         * @param year          指定年份
         * @param sheet         Excel表内容
         * @param startRowIndex 起始行索引
         * @return budget otb year month
         * @author yyt
         * @date 2023/10/30
         */
        private Map<Integer, RevenueBudgetOtb> packageBudgetOtbByYearAndMonth(Integer propertyId, int year, Sheet sheet,
                                                                              int startRowIndex) {
            InternationalizationDTO internationalization = propertyI18nRedisService.get(propertyId).getInternationalization();
            InternationalizationNumberDTO number = internationalization.getNumber();
            int dataPrecision = BaseConstant.DATA_PRECISION;
            String calculationRule = number.getCalculationRule();
            RoundingMode roundingMode = InternationalizationUtils.getRoundingMode(calculationRule);
            Map<Integer, RevenueBudgetOtb> budgetOtbByYearMonth = Maps.newHashMap();
            BigDecimal oneHundred = BigDecimal.valueOf(100L);
            Locale locale = LocaleContextHolder.getLocale();
            // 12个月
            int maxColIdx = 12;

            while (true) {
                Row otbRevenueRow = sheet.getRow(startRowIndex);
                if (otbRevenueRow == null) {
                    break;
                }
                Cell colCell = otbRevenueRow.getCell(0);
                String colValue = CellUtils.convertCellStr(colCell);

                if (StringUtils.isBlank(colValue)) {
                    break;
                } else if (StringUtils.equals(i18nMessageUtils.message(BudgetFileTitleCons.ROOM_REVENUE, locale), colValue)) {
                    // 从第2列开始，循环获取每个月的 Room Revenue 信息
                    for (int colIdx = 1; colIdx <= maxColIdx; colIdx++) {
                        Cell roomRevenueCell = otbRevenueRow.getCell(colIdx);
                        String roomRevenueStr = CellUtils.convertCellStr(roomRevenueCell);
                        if (StringUtils.isNotBlank(roomRevenueStr)) {
                            int yearAndMonth = year * 100 + colIdx;
                            RevenueBudgetOtb budgetOtb = budgetOtbByYearMonth.getOrDefault(yearAndMonth, new RevenueBudgetOtb());
                            budgetOtb.setRoomRevenue(new BigDecimal(roomRevenueStr).setScale(dataPrecision, roundingMode));
                            budgetOtbByYearMonth.put(yearAndMonth, budgetOtb);
                        }
                    }
                } else if (StringUtils.equals(i18nMessageUtils.message(BudgetFileTitleCons.OCCUPANCY, locale), colValue)) {
                    // 从第2列开始，循环获取每个月的 Occupancy 信息
                    for (int colIdx = 1; colIdx <= maxColIdx; colIdx++) {
                        Cell occCell = otbRevenueRow.getCell(colIdx);
                        String occStr = CellUtils.convertCellStr(occCell);
                        if (StringUtils.isNotBlank(occStr)) {
                            int yearAndMonth = year * 100 + colIdx;
                            RevenueBudgetOtb budgetOtb = budgetOtbByYearMonth.getOrDefault(yearAndMonth, new RevenueBudgetOtb());
                            budgetOtb.setOccPercent(new BigDecimal(occStr).multiply(oneHundred).setScale(dataPrecision, roundingMode));
                            budgetOtbByYearMonth.put(yearAndMonth, budgetOtb);
                        }
                    }
                } else if (StringUtils.equals(i18nMessageUtils.message(BudgetFileTitleCons.ADR, locale), colValue)) {
                    // 从第2列开始，循环获取每个月的 ADR 信息
                    for (int colIdx = 1; colIdx <= maxColIdx; colIdx++) {
                        Cell adrCell = otbRevenueRow.getCell(colIdx);
                        String adrStr = CellUtils.convertCellStr(adrCell);
                        if (StringUtils.isNotBlank(adrStr)) {
                            int yearAndMonth = year * 100 + colIdx;
                            RevenueBudgetOtb budgetOtb = budgetOtbByYearMonth.getOrDefault(yearAndMonth, new RevenueBudgetOtb());
                            budgetOtb.setAdr(new BigDecimal(adrStr).setScale(dataPrecision, roundingMode));
                            budgetOtbByYearMonth.put(yearAndMonth, budgetOtb);
                        }
                    }
                } else if (StringUtils.equals(i18nMessageUtils.message(BudgetFileTitleCons.BANQUET_REVENUE, locale), colValue)) {
                    // 从第2列开始，循环获取每个月的 Banquet Revenue 信息
                    for (int colIdx = 1; colIdx <= maxColIdx; colIdx++) {
                        Cell banquetRevenueCell = otbRevenueRow.getCell(colIdx);
                        String banquetRevenueStr = CellUtils.convertCellStr(banquetRevenueCell);
                        if (StringUtils.isNotBlank(banquetRevenueStr)) {
                            int yearAndMonth = year * 100 + colIdx;
                            RevenueBudgetOtb budgetOtb = budgetOtbByYearMonth.getOrDefault(yearAndMonth, new RevenueBudgetOtb());
                            budgetOtb.setBanquetRevenue(new BigDecimal(banquetRevenueStr).setScale(dataPrecision, roundingMode));
                            budgetOtbByYearMonth.put(yearAndMonth, budgetOtb);
                        }
                    }
                } else if (StringUtils.equals(i18nMessageUtils.message(BudgetFileTitleCons.FB_OTB_REVENUE, locale), colValue)) {
                    // 从第2列开始，循环获取每个月的 F&B OTB Revenue 信息
                    for (int colIdx = 1; colIdx <= maxColIdx; colIdx++) {
                        Cell fbOtbRevenueCell = otbRevenueRow.getCell(colIdx);
                        String fbOtbRevenueStr = CellUtils.convertCellStr(fbOtbRevenueCell);
                        if (StringUtils.isNotBlank(fbOtbRevenueStr)) {
                            int yearAndMonth = year * 100 + colIdx;
                            RevenueBudgetOtb budgetOtb = budgetOtbByYearMonth.getOrDefault(yearAndMonth, new RevenueBudgetOtb());
                            budgetOtb.setFbOtbRevenue(new BigDecimal(fbOtbRevenueStr).setScale(dataPrecision, roundingMode));
                            budgetOtbByYearMonth.put(yearAndMonth, budgetOtb);
                        }
                    }
                }

                startRowIndex++;
            }

            return budgetOtbByYearMonth;
        }

        /**
         * 转换 budget otb
         *
         * @param propertyId           资产 id
         * @param year                 指定年份
         * @param budgetOtbByYearMonth budget otb year month
         * @param userId               操作人
         * @return budget otb
         * @author yyt
         * @date 2023/10/30
         */
        private List<RevenueBudgetOtb> convert2BudgetOtbList(Integer propertyId, int year, Map<Integer, RevenueBudgetOtb> budgetOtbByYearMonth,
                                                             int userId) {
            if (budgetOtbByYearMonth.isEmpty()) {
                return Collections.emptyList();
            }

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

            List<RevenueBudgetOtb> budgetOtbs = Lists.newArrayListWithExpectedSize(budgetOtbByYearMonth.size());
            long now = DateTimeUtils.nowTimestamp();
            budgetOtbByYearMonth.forEach((yearAndMonth, budgetOtb) -> {
                int month = yearAndMonth - (year * 100);
                budgetOtb.setPropertyId(propertyId).setTheYear(year).setTheMonth(month).setCreatedBy(userId)
                        .setCreatedTime(now).setUpdatedBy(userId).setUpdatedTime(now);

                // revpar = occ * adr
                BigDecimal occPercent = budgetOtb.getOccPercent();
                BigDecimal adr = budgetOtb.getAdr();
                if (Objects.nonNull(occPercent) && Objects.nonNull(adr)) {
                    BigDecimal otbRevpar = occPercent.multiply(adr).divide(BigDecimal.valueOf(100L), dataPrecision, roundingMode);
                    budgetOtb.setRevPar(otbRevpar);
                } else {
                    budgetOtb.setRevPar(null);
                }

                budgetOtbs.add(budgetOtb);
            });

            return budgetOtbs;
        }

        /**
         * 封装 last year otb year month
         *
         * @param propertyId    资产 id
         * @param year          指定年份
         * @param sheet         Excel表内容
         * @param startRowIndex 起始行索引
         * @return last year otb year month
         * @author yyt
         * @date 2024/4/9
         */
        private Map<Integer, RevenueOtbRevenueLastYear> packageLastYearOtbByYearAndMonth(Integer propertyId, int year, Sheet sheet, int startRowIndex) {
            InternationalizationDTO internationalization = propertyI18nRedisService.get(propertyId).getInternationalization();
            InternationalizationNumberDTO number = internationalization.getNumber();
            int dataPrecision = BaseConstant.DATA_PRECISION;
            String calculationRule = number.getCalculationRule();
            RoundingMode roundingMode = InternationalizationUtils.getRoundingMode(calculationRule);
            Map<Integer, RevenueOtbRevenueLastYear> lastYearOtbByYearMonth = Maps.newHashMap();
            BigDecimal oneHundred = BigDecimal.valueOf(100L);
            Locale locale = LocaleContextHolder.getLocale();
            // 12个月
            int maxColIdx = 12;

            while (true) {
                Row otbRevenueRow = sheet.getRow(startRowIndex);
                if (otbRevenueRow == null) {
                    break;
                }
                Cell colCell = otbRevenueRow.getCell(0);
                String colValue = CellUtils.convertCellStr(colCell);

                if (StringUtils.isBlank(colValue)) {
                    break;
                } else if (StringUtils.equals(i18nMessageUtils.message(BudgetFileTitleCons.LAST_YEAR_ACTUAL_REVENUE, locale), colValue)) {
                    // 从第2列开始，循环获取每个月的 Room Revenue 信息
                    for (int colIdx = 1; colIdx <= maxColIdx; colIdx++) {
                        Cell roomRevenueCell = otbRevenueRow.getCell(colIdx);
                        String roomRevenueStr = CellUtils.convertCellStr(roomRevenueCell);
                        if (StringUtils.isNotBlank(roomRevenueStr)) {
                            int yearAndMonth = year * 100 + colIdx;
                            RevenueOtbRevenueLastYear lastYearOtb = lastYearOtbByYearMonth.getOrDefault(yearAndMonth, new RevenueOtbRevenueLastYear());
                            lastYearOtb.setOtbRevenue(new BigDecimal(roomRevenueStr).setScale(dataPrecision, roundingMode));
                            lastYearOtbByYearMonth.put(yearAndMonth, lastYearOtb);
                        }
                    }
                } else if (StringUtils.equals(i18nMessageUtils.message(BudgetFileTitleCons.LAST_YEAR_OCC, locale), colValue)) {
                    // 从第2列开始，循环获取每个月的 Occupancy 信息
                    for (int colIdx = 1; colIdx <= maxColIdx; colIdx++) {
                        Cell occCell = otbRevenueRow.getCell(colIdx);
                        String occStr = CellUtils.convertCellStr(occCell);
                        if (StringUtils.isNotBlank(occStr)) {
                            int yearAndMonth = year * 100 + colIdx;
                            RevenueOtbRevenueLastYear lastYearOtb = lastYearOtbByYearMonth.getOrDefault(yearAndMonth, new RevenueOtbRevenueLastYear());
                            lastYearOtb.setOtbOccPercent(new BigDecimal(occStr).multiply(oneHundred).setScale(dataPrecision, roundingMode));
                            lastYearOtbByYearMonth.put(yearAndMonth, lastYearOtb);
                        }
                    }
                } else if (StringUtils.equals(i18nMessageUtils.message(BudgetFileTitleCons.LAST_YEAR_ADR, locale), colValue)) {
                    // 从第2列开始，循环获取每个月的 ADR 信息
                    for (int colIdx = 1; colIdx <= maxColIdx; colIdx++) {
                        Cell adrCell = otbRevenueRow.getCell(colIdx);
                        String adrStr = CellUtils.convertCellStr(adrCell);
                        if (StringUtils.isNotBlank(adrStr)) {
                            int yearAndMonth = year * 100 + colIdx;
                            RevenueOtbRevenueLastYear lastYearOtb = lastYearOtbByYearMonth.getOrDefault(yearAndMonth, new RevenueOtbRevenueLastYear());
                            lastYearOtb.setOtbAdr(new BigDecimal(adrStr).setScale(dataPrecision, roundingMode));
                            lastYearOtbByYearMonth.put(yearAndMonth, lastYearOtb);
                        }
                    }
                } else if (StringUtils.equals(i18nMessageUtils.message(BudgetFileTitleCons.LAST_YEAR_BANQUET_REVENUE, locale), colValue)) {
                    // 从第2列开始，循环获取每个月的 Banquet Revenue 信息
                    for (int colIdx = 1; colIdx <= maxColIdx; colIdx++) {
                        Cell banquetRevenueCell = otbRevenueRow.getCell(colIdx);
                        String banquetRevenueStr = CellUtils.convertCellStr(banquetRevenueCell);
                        if (StringUtils.isNotBlank(banquetRevenueStr)) {
                            int yearAndMonth = year * 100 + colIdx;
                            RevenueOtbRevenueLastYear lastYearOtb = lastYearOtbByYearMonth.getOrDefault(yearAndMonth, new RevenueOtbRevenueLastYear());
                            lastYearOtb.setBanquetRevenue(new BigDecimal(banquetRevenueStr).setScale(dataPrecision, roundingMode));
                            lastYearOtbByYearMonth.put(yearAndMonth, lastYearOtb);
                        }
                    }
                } else if (StringUtils.equals(i18nMessageUtils.message(BudgetFileTitleCons.LAST_YEAR_FB_OTB_REVENUE, locale), colValue)) {
                    // 从第2列开始，循环获取每个月的 F&B OTB Revenue 信息
                    for (int colIdx = 1; colIdx <= maxColIdx; colIdx++) {
                        Cell fbOtbRevenueCell = otbRevenueRow.getCell(colIdx);
                        String fbOtbRevenueStr = CellUtils.convertCellStr(fbOtbRevenueCell);
                        if (StringUtils.isNotBlank(fbOtbRevenueStr)) {
                            int yearAndMonth = year * 100 + colIdx;
                            RevenueOtbRevenueLastYear lastYearOtb = lastYearOtbByYearMonth.getOrDefault(yearAndMonth, new RevenueOtbRevenueLastYear());
                            lastYearOtb.setFbOtbRevenue(new BigDecimal(fbOtbRevenueStr).setScale(dataPrecision, roundingMode));
                            lastYearOtbByYearMonth.put(yearAndMonth, lastYearOtb);
                        }
                    }
                }

                startRowIndex++;
            }

            return lastYearOtbByYearMonth;
        }

        /**
         * 转换 budget otb
         *
         * @param propertyId             资产 id
         * @param year                   指定年份
         * @param lastYearOtbByYearMonth last year otb year month
         * @param userId                 操作人
         * @return budget otb
         * @author yyt
         * @date 2024/4/9
         */
        private List<RevenueOtbRevenueLastYear> convert2LastYearOtbList(Integer propertyId, int year,
                                                                        Map<Integer, RevenueOtbRevenueLastYear> lastYearOtbByYearMonth, int userId) {
            if (lastYearOtbByYearMonth.isEmpty()) {
                return Collections.emptyList();
            }

            InternationalizationNumberDTO number = propertyI18nRedisService.get(propertyId).getInternationalization().getNumber();

            List<RevenueOtbRevenueLastYear> lastYearOtbs = Lists.newArrayListWithExpectedSize(lastYearOtbByYearMonth.size());
            long now = DateTimeUtils.nowTimestamp();
            lastYearOtbByYearMonth.forEach((yearAndMonth, lastYearOtb) -> {
                int month = yearAndMonth - (year * 100);
                lastYearOtb.setPropertyId(propertyId).setTheYear(year).setTheMonth(month).setCreatedBy(userId)
                        .setCreatedTime(now).setUpdatedBy(userId).setUpdatedTime(now);

                // 计算 revpar
                lastYearOtb.calculateRevpar(number);

                lastYearOtbs.add(lastYearOtb);
            });

            return lastYearOtbs;
        }

        /**
         * 查询 budget
         *
         * @param propertyId 资产 id
         * @param year       年
         * @param month      月
         * @return budget
         * @author yyt
         * @date 2024/6/14
         **/
        @Override
        public RevenueBudgetOtb getBudget(Integer propertyId, int year, int month) {
            return getOne(new QueryWrapper<RevenueBudgetOtb>().lambda().eq(RevenueBudgetOtb::getPropertyId, propertyId)
                    .eq(RevenueBudgetOtb::getTheYear, year).eq(RevenueBudgetOtb::getTheMonth, month));
        }

    }