# BudgetDatabaseExportServiceImpl.java

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

    import cloud.wconnect.commonlib.component.BeanMapper;
    import cloud.wconnect.commonlib.config.jxlscommand.StyleCommand;
    import cloud.wconnect.commonlib.cons.BaseConstant;
    import cloud.wconnect.commonlib.cons.BaseErrorCodeEnum;
    import cloud.wconnect.commonlib.cons.CommonFieldI18nConstant;
    import cloud.wconnect.commonlib.exception.BadRequestException;
    import cloud.wconnect.commonlib.pojo.dto.PropertyDTO;
    import cloud.wconnect.commonlib.pojo.dto.internationalization.InternationalizationNumberDTO;
    import cloud.wconnect.commonlib.util.I18nMessageUtils;
    import cloud.wconnect.commonlib.util.InternationalizationUtils;
    import cloud.wconnect.commonlib.util.YearMonthUtils;
    import cloud.wconnect.rateshop.cons.budgetdatabase.*;
    import cloud.wconnect.rateshop.entity.budgetdatabase.*;
    import cloud.wconnect.rateshop.pojo.bo.budgetdatabase.*;
    import cloud.wconnect.rateshop.pojo.dto.budgetdatabase.*;
    import cloud.wconnect.rateshop.service.budgetdatabase.*;
    import com.baomidou.dynamic.datasource.annotation.DS;
    import com.google.common.collect.Lists;
    import jakarta.servlet.http.HttpServletResponse;
    import org.apache.commons.collections.CollectionUtils;
    import org.apache.commons.lang3.StringUtils;
    import org.jxls.builder.xls.XlsCommentAreaBuilder;
    import org.jxls.transform.poi.JxlsPoiTemplateFillerBuilder;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.context.annotation.Lazy;
    import org.springframework.context.i18n.LocaleContextHolder;
    import org.springframework.core.io.ClassPathResource;
    import org.springframework.stereotype.Service;

    import java.io.IOException;
    import java.io.InputStream;
    import java.nio.charset.StandardCharsets;
    import java.time.YearMonth;
    import java.time.format.TextStyle;
    import java.util.*;
    import java.util.stream.Collectors;

    /**
     *
     * @author: yyt
     * 2025-09-11
     */
    @Service
    @DS("rate_shop")
    public class BudgetDatabaseExportServiceImpl implements IBudgetDatabaseExportService {

        @Autowired
        private IBudgetDatabaseTotalService budgetDatabaseTotalService;
        @Lazy
        @Autowired
        private IBudgetDatabaseConfigRoomTabService budgetDatabaseConfigRoomTabService;
        @Lazy
        @Autowired
        private IBudgetDatabaseConfigRoomTabItemService budgetDatabaseConfigRoomTabItemService;
        @Autowired
        private IBudgetDatabaseConfigFbTabService budgetDatabaseConfigFbTabService;
        @Autowired
        private IBudgetDatabaseConfigOtherTabService budgetDatabaseConfigOtherTabService;
        @Lazy
        @Autowired
        private IBudgetDatabaseRoomService budgetDatabaseRoomService;
        @Lazy
        @Autowired
        private IBudgetDatabaseFbRevenueService budgetDatabaseFbRevenueService;
        @Autowired
        private IBudgetDatabaseFbCoversService budgetDatabaseFbCoversService;
        @Lazy
        @Autowired
        private IBudgetDatabaseOtherRevenueService budgetDatabaseOtherRevenueService;
        @Autowired
        private IBudgetDatabaseCommentRoomService budgetDatabaseCommentRoomService;
        @Autowired
        private I18nMessageUtils i18nMessageUtils;
        @Autowired
        private BeanMapper beanMapper;

        /**
         * 导出
         *
         * @param propertyDTO 资产
         * @param year        年
         * @param response    响应信息
         * @author yyt
         * @date 2025/9/11
         **/
        @Override
        public void export(PropertyDTO propertyDTO, int year, HttpServletResponse response) {
            Map<String, Object> data = new HashMap<>();
            data.put("budgetYear", year);
            data.put("actualYear", year - 1);
            // Total
            data.put("total", packageTotal(propertyDTO, year));
            // room
            data.put("rooms", packageRoom(propertyDTO, year));
            // F&B
            data.put("fbs", packageFB(propertyDTO, year));
            // Other
            data.put("others", packageOther(propertyDTO, year));
            // 每月 Market Segmentation
            data.put("monthSegmentations", packageMonthSegmentation(propertyDTO, year));
            // 设置标题
            packageTitle(data, year);

            String templateName = String.format("/tmpl-xls/budgetdatabase/budget-database_tmpl_%s.xlsx", LocaleContextHolder.getLocale());

            try (InputStream inputStream = new ClassPathResource(templateName).getInputStream()) {
                String fileName = String.format("%s_budget-database_%d.xlsx", propertyDTO.getAbbr(), year);
                fileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
                response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                response.addHeader("Access-Control-Expose-Headers", "Content-Disposition");
                response.setHeader("Content-Disposition", String.join("", "attachment; filename=", fileName));

                XlsCommentAreaBuilder xlsCommentAreaBuilder = new XlsCommentAreaBuilder();
                xlsCommentAreaBuilder.addCommandMapping(StyleCommand.NAME, StyleCommand.class);

                JxlsPoiTemplateFillerBuilder.newInstance()
                        .withAreaBuilder(xlsCommentAreaBuilder)
                        .withTemplate(inputStream)
                        .buildAndFill(data, response::getOutputStream);
            } catch (IOException e) {
                throw new BadRequestException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(), "Export file failed; exception:" + e);
            }
        }

        /**
         * 设置标题
         *
         * @param data 数据
         * @param year 年
         * @author yyt
         * @date 2025/9/13
         **/
        private void packageTitle(Map<String, Object> data,  int year) {
            Locale locale = LocaleContextHolder.getLocale();
            String budgetYearStr = year + "";
            String actualYearStr = (year - 1) + "";

            List<String> monthOperationInfoTitles = Lists.newArrayList(i18nMessageUtils.message(BudgetDatabaseTitleEnum.BUDGET_ROOMS.getDesc(), locale, budgetYearStr),
                    i18nMessageUtils.message(BudgetDatabaseTitleEnum.ACTUAL_ROOMS.getDesc(), locale, actualYearStr),
                    i18nMessageUtils.message(BudgetDatabaseTitleEnum.YOY_CHG.getDesc(), locale),
                    i18nMessageUtils.message(BudgetDatabaseTitleEnum.BUDGET_ADR.getDesc(), locale, budgetYearStr),
                    i18nMessageUtils.message(BudgetDatabaseTitleEnum.ACTUAL_ADR.getDesc(), locale, actualYearStr),
                    i18nMessageUtils.message(BudgetDatabaseTitleEnum.YOY_CHG.getDesc(), locale),
                    i18nMessageUtils.message(BudgetDatabaseTitleEnum.BUDGET_REVENUE.getDesc(), locale, budgetYearStr),
                    i18nMessageUtils.message(BudgetDatabaseTitleEnum.ACTUAL_REVENUE.getDesc(), locale, actualYearStr),
                    i18nMessageUtils.message(BudgetDatabaseTitleEnum.YOY_CHG.getDesc(), locale),
                    i18nMessageUtils.message(BudgetDatabaseTitleEnum.BUDGET_OCC.getDesc(), locale, budgetYearStr),
                    i18nMessageUtils.message(BudgetDatabaseTitleEnum.ACTUAL_OCC.getDesc(), locale, actualYearStr),
                    i18nMessageUtils.message(BudgetDatabaseTitleEnum.OCC_CHANGE.getDesc(), locale),
                    i18nMessageUtils.message(BudgetDatabaseTitleEnum.BUDGET_REVPAR.getDesc(), locale, budgetYearStr),
                    i18nMessageUtils.message(BudgetDatabaseTitleEnum.ACTUAL_REVPAR.getDesc(), locale, actualYearStr),
                    i18nMessageUtils.message(BudgetDatabaseTitleEnum.YOY_CHG.getDesc(), locale),
                    i18nMessageUtils.message(BudgetDatabaseTitleEnum.BUDGET_RGI.getDesc(), locale, budgetYearStr),
                    i18nMessageUtils.message(BudgetDatabaseTitleEnum.ACTUAL_RGI.getDesc(), locale, actualYearStr),
                    i18nMessageUtils.message(BudgetDatabaseTitleEnum.YOY_CHG.getDesc(), locale),
                    i18nMessageUtils.message(BudgetDatabaseTitleEnum.PROJECTED_STR_REVPAR_INDEX.getDesc(), locale, actualYearStr),
                    i18nMessageUtils.message(BudgetDatabaseTitleEnum.HOTEL_REVPAR.getDesc(), locale, actualYearStr),
                    i18nMessageUtils.message(BudgetDatabaseTitleEnum.CS_REVPAR.getDesc(), locale, actualYearStr));
            data.put("monthOperationInfoTitles", monthOperationInfoTitles);
        }

        /**
         * 封装 Total
         *
         * @param propertyDTO 资产
         * @param year        年
         * @return Total
         * @author yyt
         * @date 2025/9/11
         **/
        private List<TotalDetailBO> packageTotal(PropertyDTO propertyDTO, int year) {
            InternationalizationNumberDTO number = propertyDTO.getInternationalization().getNumber();
            String calculationRule = number.getCalculationRule();
            String currency = number.getCurrency();
            Locale locale = LocaleContextHolder.getLocale();

            TotalDTO totalDTO = budgetDatabaseTotalService.listTotal(propertyDTO.getId(), year);
            List<TotalDetailBO> totalDetailBOs = Lists.newArrayListWithExpectedSize(4);
            List<TotalDetailDTO> totalInfos = totalDTO.getTotalInfos();
            for (TotalDetailDTO totalInfo : totalInfos) {
                TotalDetailBO totalDetailBO = new TotalDetailBO();
                totalDetailBO.setTitle(i18nMessageUtils.message(BudgetDatabaseTitleEnum.getDesc(totalInfo.getTitle()), locale));
                totalDetailBO.setBudgetYearBO(packageBudgetYearForRevenue(beanMapper.map(totalInfo, BudgetYearDTO.class), calculationRule, currency));
                if (totalInfo.getTitle().equals(CommonFieldI18nConstant.TOTAL)) {
                    totalDetailBO.setBold(true);
                }
                totalDetailBOs.add(totalDetailBO);
            }
            return totalDetailBOs;
        }

        /**
         * 封装 budgetYear
         *
         * @param budgetYearDTO   budgetYearDTO
         * @param calculationRule 计算规则
         * @param currency        币种
         * @return BudgetYearBO
         * @author yyt
         * @date 2025/9/11
         **/
        private BudgetYearBO packageBudgetYearForRevenue(BudgetYearDTO budgetYearDTO, String calculationRule, String currency) {

            BudgetYearBO budgetYearBO = new BudgetYearBO();
            budgetYearBO.setJanuary(Objects.isNull(budgetYearDTO.getJanuary()) ? null : InternationalizationUtils.formatAmountViewValue(budgetYearDTO.getJanuary(), calculationRule, currency));
            budgetYearBO.setFebruary(Objects.isNull(budgetYearDTO.getFebruary()) ? null :
                    InternationalizationUtils.formatAmountViewValue(budgetYearDTO.getFebruary(), calculationRule, currency));
            budgetYearBO.setMarch(Objects.isNull(budgetYearDTO.getMarch()) ? null :
                    InternationalizationUtils.formatAmountViewValue(budgetYearDTO.getMarch(), calculationRule, currency));
            budgetYearBO.setApril(Objects.isNull(budgetYearDTO.getApril()) ? null :
                    InternationalizationUtils.formatAmountViewValue(budgetYearDTO.getApril(), calculationRule, currency));
            budgetYearBO.setMay(Objects.isNull(budgetYearDTO.getMay()) ? null :
                    InternationalizationUtils.formatAmountViewValue(budgetYearDTO.getMay(), calculationRule, currency));
            budgetYearBO.setJune(Objects.isNull(budgetYearDTO.getJune()) ? null :
                    InternationalizationUtils.formatAmountViewValue(budgetYearDTO.getJune(), calculationRule, currency));
            budgetYearBO.setJuly(Objects.isNull(budgetYearDTO.getJuly()) ? null :
                    InternationalizationUtils.formatAmountViewValue(budgetYearDTO.getJuly(), calculationRule, currency));
            budgetYearBO.setAugust(Objects.isNull(budgetYearDTO.getAugust()) ? null :
                    InternationalizationUtils.formatAmountViewValue(budgetYearDTO.getAugust(), calculationRule, currency));
            budgetYearBO.setSeptember(Objects.isNull(budgetYearDTO.getSeptember()) ? null :
                    InternationalizationUtils.formatAmountViewValue(budgetYearDTO.getSeptember(), calculationRule, currency));
            budgetYearBO.setOctober(Objects.isNull(budgetYearDTO.getOctober()) ? null :
                    InternationalizationUtils.formatAmountViewValue(budgetYearDTO.getOctober(), calculationRule, currency));
            budgetYearBO.setNovember(Objects.isNull(budgetYearDTO.getNovember()) ? null :
                    InternationalizationUtils.formatAmountViewValue(budgetYearDTO.getNovember(), calculationRule, currency));
            budgetYearBO.setDecember(Objects.isNull(budgetYearDTO.getDecember()) ? null :
                    InternationalizationUtils.formatAmountViewValue(budgetYearDTO.getDecember(), calculationRule, currency));
            budgetYearBO.setBudgetTotal(Objects.isNull(budgetYearDTO.getBudgetTotal()) ? null :
                    InternationalizationUtils.formatAmountViewValue(budgetYearDTO.getBudgetTotal(), calculationRule, currency));
            budgetYearBO.setActualTotal(Objects.isNull(budgetYearDTO.getActualTotal()) ? null :
                    InternationalizationUtils.formatAmountViewValue(budgetYearDTO.getActualTotal(), calculationRule, currency));
            budgetYearBO.setYoyChg(Objects.isNull(budgetYearDTO.getYoyChg()) ? null :
                    InternationalizationUtils.formatAmountViewValue(budgetYearDTO.getYoyChg(), calculationRule, null) + "%");
            return budgetYearBO;
        }

        /**
         * 封装 budgetYear
         *
         * @param budgetYearDTO   budgetYearDTO
         * @param calculationRule 计算规则
         * @return BudgetYearBO
         * @author yyt
         * @date 2025/9/11
         **/
        private BudgetYearBO packageBudgetYearForRoom(BudgetYearRoomsDTO budgetYearDTO, String calculationRule) {
            BudgetYearBO budgetYearBO = new BudgetYearBO();
            budgetYearBO.setJanuary(Objects.isNull(budgetYearDTO.getJanuary()) ? null :
                    InternationalizationUtils.formatAmountViewValue(budgetYearDTO.getJanuary(), calculationRule, null));
            budgetYearBO.setFebruary(Objects.isNull(budgetYearDTO.getFebruary()) ? null :
                    InternationalizationUtils.formatAmountViewValue(budgetYearDTO.getFebruary(), calculationRule, null));
            budgetYearBO.setMarch(Objects.isNull(budgetYearDTO.getMarch()) ? null :
                    InternationalizationUtils.formatAmountViewValue(budgetYearDTO.getMarch(), calculationRule, null));
            budgetYearBO.setApril(Objects.isNull(budgetYearDTO.getApril()) ? null :
                    InternationalizationUtils.formatAmountViewValue(budgetYearDTO.getApril(), calculationRule, null));
            budgetYearBO.setMay(Objects.isNull(budgetYearDTO.getMay()) ? null :
                    InternationalizationUtils.formatAmountViewValue(budgetYearDTO.getMay(), calculationRule, null));
            budgetYearBO.setJune(Objects.isNull(budgetYearDTO.getJune()) ? null :
                    InternationalizationUtils.formatAmountViewValue(budgetYearDTO.getJune(), calculationRule, null));
            budgetYearBO.setJuly(Objects.isNull(budgetYearDTO.getJuly()) ? null :
                    InternationalizationUtils.formatAmountViewValue(budgetYearDTO.getJuly(), calculationRule, null));
            budgetYearBO.setAugust(Objects.isNull(budgetYearDTO.getAugust()) ? null :
                    InternationalizationUtils.formatAmountViewValue(budgetYearDTO.getAugust(), calculationRule, null));
            budgetYearBO.setSeptember(Objects.isNull(budgetYearDTO.getSeptember()) ? null :
                    InternationalizationUtils.formatAmountViewValue(budgetYearDTO.getSeptember(), calculationRule, null));
            budgetYearBO.setOctober(Objects.isNull(budgetYearDTO.getOctober()) ? null :
                    InternationalizationUtils.formatAmountViewValue(budgetYearDTO.getOctober(), calculationRule, null));
            budgetYearBO.setNovember(Objects.isNull(budgetYearDTO.getNovember()) ? null :
                    InternationalizationUtils.formatAmountViewValue(budgetYearDTO.getNovember(), calculationRule, null));
            budgetYearBO.setDecember(Objects.isNull(budgetYearDTO.getDecember()) ? null :
                    InternationalizationUtils.formatAmountViewValue(budgetYearDTO.getDecember(), calculationRule, null));
            budgetYearBO.setBudgetTotal(Objects.isNull(budgetYearDTO.getBudgetTotal()) ? null :
                    InternationalizationUtils.formatAmountViewValue(budgetYearDTO.getBudgetTotal(), calculationRule, null));
            budgetYearBO.setActualTotal(Objects.isNull(budgetYearDTO.getActualTotal()) ? null :
                    InternationalizationUtils.formatAmountViewValue(budgetYearDTO.getActualTotal(), calculationRule, null));
            budgetYearBO.setYoyChg(Objects.isNull(budgetYearDTO.getYoyChg()) ? null :
                    InternationalizationUtils.formatAmountViewValue(budgetYearDTO.getYoyChg(), calculationRule, null) + "%");
            return budgetYearBO;
        }

        /**
         * 封装 Room
         *
         * @param propertyDTO 资产
         * @param year        年
         * @return Room
         * @author yyt
         * @date 2025/9/11
         **/
        private List<RoomBO> packageRoom(PropertyDTO propertyDTO, int year) {
            Integer propertyId = propertyDTO.getId();
            Locale locale = LocaleContextHolder.getLocale();
            InternationalizationNumberDTO number = propertyDTO.getInternationalization().getNumber();
            String calculationRule = number.getCalculationRule();
            String currency = number.getCurrency();

            // 查询所有 tab
            List<BudgetDatabaseConfigRoomTab> tabs = budgetDatabaseConfigRoomTabService.list(propertyId, year);
            if (CollectionUtils.isEmpty(tabs)) return Collections.emptyList();

            List<RoomBO> bos = Lists.newArrayListWithExpectedSize(tabs.size());
            for (BudgetDatabaseConfigRoomTab tab : tabs) {
                List<RoomOperationInfoDTO> infoDTOS = budgetDatabaseRoomService.listRoom(tab, false);

                MarketSegmentationBO marketSegmentationBO = packageMarketSegmentationBO(infoDTOS, calculationRule, currency, locale);
                RoomBO roomBO = beanMapper.map(marketSegmentationBO, RoomBO.class);

                MonthStatisticsDTO monthStatisticsDTO = budgetDatabaseRoomService.listMonthStatistics(tab, false);
                List<MonthOperationInfoDTO> monthStatistics = monthStatisticsDTO.getMonthStatistics();
                List<MonthOperationInfoBO> monthOperationInfoBOs = Lists.newArrayListWithExpectedSize(monthStatistics.size());
                for (MonthOperationInfoDTO item : monthStatistics) {
                    MonthOperationInfoBO monthOperationInfoBO = packageMonthOperationInfoBO(item, calculationRule, currency);
                    String month;
                    if (item.getYearMonth() == -1) {
                        month = i18nMessageUtils.message(BudgetDatabaseTitleEnum.TOTAL_AVERAGE.getDesc(), locale);
                        monthOperationInfoBO.setBold(true);
                    } else {
                        month = YearMonthUtils.getYearMonth(item.getYearMonth().toString()).getMonth().getDisplayName(TextStyle.FULL, locale);
                    }
                    monthOperationInfoBO.setMonth(month);
                    monthOperationInfoBOs.add(monthOperationInfoBO);
                }
                roomBO.setMonthOperationInfoBOs(monthOperationInfoBOs);

                // total
                RoomTotalDTO roomTotalDTO = budgetDatabaseTotalService.listRoomTotal(tab);
                List<RoomTotalDetailDTO> items = roomTotalDTO.getItems();
                List<RoomTotalDetailBO> roomTotalDetailBOS = Lists.newArrayListWithExpectedSize(items.size());
                for (RoomTotalDetailDTO item : items) {
                    RoomTotalDetailBO roomTotalDetailBO = new RoomTotalDetailBO();
                    String name;
                    if (item.getId() == BaseConstant.ZERO) {
                        name = i18nMessageUtils.message(BudgetDatabaseTitleCons.TOTAL_REVENUE, locale);
                        roomTotalDetailBO.setBold(true);
                    } else if(item.getId() == -1) {
                        name = i18nMessageUtils.message(BudgetDatabaseTitleCons.ROOM_REVENUE, locale);
                        roomTotalDetailBO.setBold(true);
                    } else {
                        name = item.getName();
                    }
                    roomTotalDetailBO.setName(name);
                    roomTotalDetailBO.setBudgetYearBO(packageBudgetYearForRevenue(beanMapper.map(item, BudgetYearDTO.class), calculationRule, currency));
                    roomTotalDetailBOS.add(roomTotalDetailBO);
                }
                roomBO.setTotals(roomTotalDetailBOS);

                roomBO.setName(formatSheetName(tab.getName()));
                bos.add(roomBO);
            }

            return bos;
        }

        /**
         * 格式化 sheet 名称
         *
         * @param name sheet 名称
         * @return sheet 名称
         * @author yyt
         * @date 2025/9/12
         **/
        private String formatSheetName(String name) {
            if (StringUtils.isEmpty(name)) return "";
            return name.length() > 31 ? name.substring(0, 31) : name;
        }

        /**
         * 格式化 sheet 名称
         *
         * @param name sheet 名称
         * @return sheet 名称
         * @author yyt
         * @date 2025/9/12
         **/
        private String formatMonthSheetName(String name) {
            if (StringUtils.isEmpty(name)) return "";
            return name.length() > 20 ? name.substring(0, 20) : name;
        }

        /**
         * 封装 MonthOperationInfoBO
         *
         * @param monthOperationInfoDTO 月信息
         * @param calculationRule       计算规则
         * @param currency              币种
         * @return MonthOperationInfoBO
         * @author yyt
         * @date 2025/9/12
         **/
        private MonthOperationInfoBO packageMonthOperationInfoBO(MonthOperationInfoDTO monthOperationInfoDTO, String calculationRule, String currency) {
            MonthOperationInfoBO monthOperationInfoBO = new MonthOperationInfoBO();
            monthOperationInfoBO.setOperationInfoBO(packageOperationInfo(beanMapper.map(monthOperationInfoDTO, OperationInfoDTO.class), calculationRule, currency));
            monthOperationInfoBO.setBudgetRgi(Objects.isNull(monthOperationInfoDTO.getBudgetRgi()) ? null :
                    InternationalizationUtils.formatAmountViewValue(monthOperationInfoDTO.getBudgetRgi(), calculationRule, null) + "%");
            monthOperationInfoBO.setActualRgi(Objects.isNull(monthOperationInfoDTO.getActualRgi()) ? null :
                    InternationalizationUtils.formatAmountViewValue(monthOperationInfoDTO.getActualRgi(), calculationRule, null) + "%");
            monthOperationInfoBO.setRgiYoyChg(Objects.isNull(monthOperationInfoDTO.getRgiYoyChg()) ? null :
                    InternationalizationUtils.formatAmountViewValue(monthOperationInfoDTO.getRgiYoyChg(), calculationRule, null) + "%");
            monthOperationInfoBO.setStrRevpar(Objects.isNull(monthOperationInfoDTO.getStrRevpar()) ? null :
                    InternationalizationUtils.formatAmountViewValue(monthOperationInfoDTO.getStrRevpar(), calculationRule, null));
            monthOperationInfoBO.setHotelRevpar(Objects.isNull(monthOperationInfoDTO.getHotelRevpar()) ? null :
                    InternationalizationUtils.formatAmountViewValue(monthOperationInfoDTO.getHotelRevpar(), calculationRule, currency));
            monthOperationInfoBO.setCsRevpar(Objects.isNull(monthOperationInfoDTO.getCsRevpar()) ? null :
                    InternationalizationUtils.formatAmountViewValue(monthOperationInfoDTO.getCsRevpar(), calculationRule, null));
            monthOperationInfoBO.setContent(Objects.isNull(monthOperationInfoDTO.getComment()) ? null : monthOperationInfoDTO.getComment().getContent());
            return monthOperationInfoBO;
        }

        /**
         * 封装 RoomOperationInfoDTO
         *
         * @param infoDTO RoomOperationInfoDTO
         * @param calculationRule         计算规则
         * @param currency                币种
         * @param locale                  多语言
         * @return RoomOperationInfoDTO
         * @author yyt
         * @date 2025/9/11
         **/
        private RoomOperationInfoBO packageSegmentOperationInfo(RoomOperationInfoDTO infoDTO,
                                                                String calculationRule, String currency, Locale locale) {
            RoomOperationInfoBO iInfoBO = new RoomOperationInfoBO();
            String name = infoDTO.getName();
            if (infoDTO.getRoomId() <= BaseConstant.ZERO) {
                name = i18nMessageUtils.message(RoomTypeEnum.getI18nDesc(name), locale);
                iInfoBO.setBold(true);
            }
            iInfoBO.setName(name);
            iInfoBO.setOperationInfoBO(packageOperationInfo(beanMapper.map(infoDTO, OperationInfoDTO.class), calculationRule, currency));
            iInfoBO.setContent(Objects.isNull(infoDTO.getComment()) ?  null : infoDTO.getComment().getContent());
            return iInfoBO;
        }

        /**
         * 封装 Operation Info
         *
         * @param operationInfoDTO operationInfoDTO
         * @param calculationRule 计算规则
         * @param currency        币种
         * @return OperationInfoBO
         * @author yyt
         * @date 2025/9/11
         **/
        private OperationInfoBO packageOperationInfo(OperationInfoDTO operationInfoDTO, String calculationRule, String currency) {
            OperationInfoBO operationInfoBO = new OperationInfoBO();
            operationInfoBO.setBudgetRooms(Objects.isNull(operationInfoDTO.getBudgetRooms()) ? null :
                    InternationalizationUtils.formatAmountViewValue(operationInfoDTO.getBudgetRooms(), null, null));
            operationInfoBO.setActualRooms(Objects.isNull(operationInfoDTO.getActualRooms()) ? null :
                    InternationalizationUtils.formatAmountViewValue(operationInfoDTO.getActualRooms(), null, null));
            operationInfoBO.setRoomsYoyChg(Objects.isNull(operationInfoDTO.getRoomsYoyChg()) ? null :
                    InternationalizationUtils.formatAmountViewValue(operationInfoDTO.getRoomsYoyChg(), calculationRule, null) + "%");
            operationInfoBO.setBudgetAdr(Objects.isNull(operationInfoDTO.getBudgetAdr()) ? null :
                    InternationalizationUtils.formatAmountViewValue(operationInfoDTO.getBudgetAdr(), calculationRule, currency));
            operationInfoBO.setActualAdr(Objects.isNull(operationInfoDTO.getActualAdr()) ? null :
                    InternationalizationUtils.formatAmountViewValue(operationInfoDTO.getActualAdr(), calculationRule, currency));
            operationInfoBO.setAdrYoyChg(Objects.isNull(operationInfoDTO.getAdrYoyChg()) ? null :
                    InternationalizationUtils.formatAmountViewValue(operationInfoDTO.getAdrYoyChg(), calculationRule, null) + "%");
            operationInfoBO.setBudgetRevenue(Objects.isNull(operationInfoDTO.getBudgetRevenue()) ? null :
                    InternationalizationUtils.formatAmountViewValue(operationInfoDTO.getBudgetRevenue(), calculationRule, currency));
            operationInfoBO.setActualRevenue(Objects.isNull(operationInfoDTO.getActualRevenue()) ? null :
                    InternationalizationUtils.formatAmountViewValue(operationInfoDTO.getActualRevenue(), calculationRule, currency));
            operationInfoBO.setRevenueYoyChg(Objects.isNull(operationInfoDTO.getRevenueYoyChg()) ? null :
                    InternationalizationUtils.formatAmountViewValue(operationInfoDTO.getRevenueYoyChg(), calculationRule, null) + "%");
            operationInfoBO.setRevenueTotal(Objects.isNull(operationInfoDTO.getRevenueTotal()) ? null :
                    InternationalizationUtils.formatAmountViewValue(operationInfoDTO.getRevenueTotal(), calculationRule, null) + "%");
            operationInfoBO.setBudgetOcc(Objects.isNull(operationInfoDTO.getBudgetOcc()) ? null :
                    InternationalizationUtils.formatAmountViewValue(operationInfoDTO.getBudgetOcc(), calculationRule, null) + "%");
            operationInfoBO.setActualOcc(Objects.isNull(operationInfoDTO.getActualOcc()) ? null :
                    InternationalizationUtils.formatAmountViewValue(operationInfoDTO.getActualOcc(), calculationRule, null) + "%");
            operationInfoBO.setOccChange(Objects.isNull(operationInfoDTO.getOccChange()) ? null :
                    InternationalizationUtils.formatAmountViewValue(operationInfoDTO.getOccChange(), calculationRule, null) + "%");
            operationInfoBO.setBudgetRevpar(Objects.isNull(operationInfoDTO.getBudgetRevpar()) ? null :
                    InternationalizationUtils.formatAmountViewValue(operationInfoDTO.getBudgetRevpar(), calculationRule, currency));
            operationInfoBO.setActualRevpar(Objects.isNull(operationInfoDTO.getActualRevpar()) ? null :
                    InternationalizationUtils.formatAmountViewValue(operationInfoDTO.getActualRevpar(), calculationRule, currency));
            operationInfoBO.setRevparYoyChg(Objects.isNull(operationInfoDTO.getRevparYoyChg()) ? null :
                    InternationalizationUtils.formatAmountViewValue(operationInfoDTO.getRevparYoyChg(), calculationRule, null) + "%");
            return operationInfoBO;
        }

        /**
         * 封装 FB
         *
         * @param propertyDTO  资产
         * @param year         年
         * @return FB
         * @author yyt
         * @date 2025/9/11
         **/
        private List<FBBO> packageFB(PropertyDTO propertyDTO, int year) {
            Integer propertyId = propertyDTO.getId();
            Locale locale = LocaleContextHolder.getLocale();
            InternationalizationNumberDTO number = propertyDTO.getInternationalization().getNumber();
            String calculationRule = number.getCalculationRule();
            String currency = number.getCurrency();

            // 查询所有 tab
            List<BudgetDatabaseConfigFbTab> tabs = budgetDatabaseConfigFbTabService.list(propertyId, year);
            if (CollectionUtils.isEmpty(tabs)) return Collections.emptyList();

            List<FBBO> bos = Lists.newArrayListWithExpectedSize(tabs.size());
            for (BudgetDatabaseConfigFbTab tab : tabs) {
                FBBO fbBO = new FBBO();
                FbRevenueDTO fbRevenueDTO = budgetDatabaseFbRevenueService.listFbRevenue(tab, false);
                List<FbRevenueDetailDTO> fbRevenueDetailDTOS = fbRevenueDTO.getItems();
                List<FbDetailBO> revenues = Lists.newArrayListWithExpectedSize(fbRevenueDetailDTOS.size());
                for (FbRevenueDetailDTO item : fbRevenueDetailDTOS) {
                    revenues.add(packageFbDetail(item, calculationRule, currency, locale, true));
                }
                fbBO.setRevenues(revenues);

                FbCoversDTO fbCoversDTO = budgetDatabaseFbCoversService.listFbCoversDTO(tab);
                List<FbRevenueDetailDTO> fbCoverDetailDTOS = fbCoversDTO.getItems();
                List<FbDetailBO> covers = Lists.newArrayListWithExpectedSize(fbCoverDetailDTOS.size());
                for (FbRevenueDetailDTO item : fbCoverDetailDTOS) {
                    covers.add(packageFbDetail(item, calculationRule, currency, locale, false));
                }
                fbBO.setCovers(covers);
                fbBO.setName(formatSheetName(tab.getName()));
                bos.add(fbBO);
            }
            return bos;
        }

        /**
         * 封装 FB Detail
         *
         * @param item         FB Detail
         * @param calculationRule 计算规则
         * @param currency     币种
         * @param locale       语言
         * @param dataIsAmount 数据是否是金额
         * @return FB Detail
         * @author yyt
         * @date 2025/9/11
         **/
        private FbDetailBO packageFbDetail(FbRevenueDetailDTO item, String calculationRule, String currency, Locale locale, boolean dataIsAmount) {
            FbDetailBO fbDetailBO = new FbDetailBO();
            String name;
            if(item.getId() <= BaseConstant.ZERO){
                name = i18nMessageUtils.message(FbTypeEnum.getI18nDesc(item.getName()), locale);
                fbDetailBO.setBold(true);
            } else {
                name = item.getName();
            }
            fbDetailBO.setName(name);
            fbDetailBO.setBudgetYearBO(dataIsAmount ? packageBudgetYearForRevenue(beanMapper.map(item, BudgetYearDTO.class), calculationRule, currency)
                    : packageBudgetYearForRoom(beanMapper.map(item, BudgetYearRoomsDTO.class), calculationRule));
            fbDetailBO.setContent(Objects.isNull(item.getComment()) ? null : item.getComment().getContent());
            return fbDetailBO;
        }

        /**
         * 封装 Other
         *
         * @param propertyDTO 资产
         * @param year        年
         * @return Other
         * @author yyt
         * @date 2025/9/11
         **/
        private List<OtherBO> packageOther(PropertyDTO propertyDTO, int year) {
            Integer propertyId = propertyDTO.getId();
            Locale locale = LocaleContextHolder.getLocale();
            InternationalizationNumberDTO number = propertyDTO.getInternationalization().getNumber();
            String calculationRule = number.getCalculationRule();
            String currency = number.getCurrency();

            // 查询所有 tab
            List<BudgetDatabaseConfigOtherTab> tabs = budgetDatabaseConfigOtherTabService.list(propertyId, year);
            if (CollectionUtils.isEmpty(tabs)) return Collections.emptyList();

            List<OtherBO> bos = Lists.newArrayListWithExpectedSize(tabs.size());
            for (BudgetDatabaseConfigOtherTab tab : tabs) {
                OtherDTO otherDTO = budgetDatabaseOtherRevenueService.listOther(tab, false);
                List<OtherDetailDTO> items = otherDTO.getItems();
                List<OtherDetailBO> otherDetailBOS = Lists.newArrayListWithExpectedSize(items.size());
                for (OtherDetailDTO item : items) {
                    OtherDetailBO otherDetailBO = new OtherDetailBO();
                    String name;
                    if (item.getId() <= BaseConstant.ZERO) {
                        name = i18nMessageUtils.message(BudgetDatabaseTitleEnum.TOTAL.getDesc(), locale);
                        otherDetailBO.setBold(true);
                    } else {
                        name = item.getName();
                    }
                    otherDetailBO.setName(name);
                    otherDetailBO.setBudgetYearBO(packageBudgetYearForRevenue(beanMapper.map(item, BudgetYearDTO.class), calculationRule, currency));
                    otherDetailBO.setContent(Objects.isNull(item.getComment()) ? null : item.getComment().getContent());
                    otherDetailBOS.add(otherDetailBO);
                }

                OtherBO otherBO = new OtherBO();
                otherBO.setName(formatSheetName(tab.getName()));
                otherBO.setOtherDetails(otherDetailBOS);
                bos.add(otherBO);
            }

            return bos;
        }

        /**
         * 封装 Month Segmentation
         *
         * @param propertyDTO 资产
         * @param year        年
         * @return Month Segmentation
         * @author yyt
         * @date 2025/9/11
         **/
        private List<MarketSegmentationBO> packageMonthSegmentation(PropertyDTO propertyDTO, int year) {
            Integer propertyId = propertyDTO.getId();
            Locale locale = LocaleContextHolder.getLocale();
            InternationalizationNumberDTO number = propertyDTO.getInternationalization().getNumber();
            String calculationRule = number.getCalculationRule();
            String currency = number.getCurrency();

            // 查询所有 tab
            List<BudgetDatabaseConfigRoomTab> tabs = budgetDatabaseConfigRoomTabService.list(propertyId, year);
            if (CollectionUtils.isEmpty(tabs)) return Collections.emptyList();

            List<MarketSegmentationBO> bos = Lists.newArrayListWithExpectedSize(tabs.size() * 12);

            Map<Integer, List<BudgetDatabaseConfigRoomTabItem>> roomIdsByTabId =
                    budgetDatabaseConfigRoomTabItemService.listByTabIds(tabs.stream().map(BudgetDatabaseConfigRoomTab::getId).toList(), RoomClassificationEnum.ROOM.getCode())
                            .stream().collect(Collectors.groupingBy(BudgetDatabaseConfigRoomTabItem::getRoomTabId));

            for (BudgetDatabaseConfigRoomTab tab : tabs) {
                List<BudgetDatabaseConfigRoomTabItem> rooms = roomIdsByTabId.getOrDefault(tab.getId(), Collections.emptyList());
                if (CollectionUtils.isEmpty(rooms)) {
                    continue;
                }

                // 查询 revenue
                List<Integer> roomIds = rooms.stream().map(BudgetDatabaseConfigRoomTabItem::getId).toList();
                Map<Integer, Map<Integer, BudgetDatabaseRoom>> revenueByYearMonth = budgetDatabaseRoomService.listByItemIds(roomIds)
                        .stream().collect(Collectors.groupingBy(BudgetDatabaseRoom::getYearMonth,
                                Collectors.toMap(BudgetDatabaseRoom::getRoomId, v -> v)));

                // comment
                List<BudgetDatabaseCommentRoom> comments = budgetDatabaseCommentRoomService.list(propertyId, tab.getId(), RoomCommentAreaCons.MARKET_SEGMENTATION);
                Map<Integer, Map<Integer, BudgetDatabaseCommentRoom>> commentByArea = comments.stream().collect(Collectors.groupingBy(BudgetDatabaseCommentRoom::getArea,
                        Collectors.toMap(BudgetDatabaseCommentRoom::getRow, v -> v)));

                String tabName = tab.getName();

                for (int i = 1; i <= 12; i++) {
                    YearMonth yearMonthObj = YearMonth.of(year, i);
                    int yearMonth = Integer.parseInt(YearMonthUtils.DATE_TIME_FORMATTER.format(yearMonthObj));
                    List<RoomOperationInfoDTO> infoDTOS = budgetDatabaseRoomService.packageRoomOperationInfoDTO(propertyId, year, i, rooms,
                            revenueByYearMonth.getOrDefault(yearMonth, Collections.emptyMap()),
                            commentByArea.getOrDefault(i, Collections.emptyMap()), Collections.emptyMap());

                    MarketSegmentationBO marketSegmentationBO = packageMarketSegmentationBO(infoDTOS, calculationRule, currency, locale);
                    marketSegmentationBO.setMonth(yearMonthObj.getMonth().getDisplayName(TextStyle.FULL, locale));
                    marketSegmentationBO.setName(formatMonthSheetName(tabName) + "-" + marketSegmentationBO.getMonth());
                    bos.add(marketSegmentationBO);
                }
            }

            return bos;
        }

        /**
         * 封装 Month Segmentation
         *
         * @param infoDTOS        roomOperationInfoDTOS
         * @param calculationRule 计算规则
         * @param currency        币种
         * @param locale          多语言
         * @return MarketSegmentationBO
         * @author yyt
         * @date 2025/9/12
         **/
        private MarketSegmentationBO packageMarketSegmentationBO(List<RoomOperationInfoDTO> infoDTOS, String calculationRule, String currency, Locale locale) {
            int size = infoDTOS.size();
            List<RoomOperationInfoBO> rooms = Lists.newArrayListWithExpectedSize(size);
            for (RoomOperationInfoDTO item : infoDTOS) {
                rooms.add(packageSegmentOperationInfo(item, calculationRule, currency, locale));
            }
            MarketSegmentationBO marketSegmentationBO = new MarketSegmentationBO();
            marketSegmentationBO.setRooms(rooms);
            return marketSegmentationBO;
        }

    }