package com.infoloop.tianting.service.impl;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.date.LocalDateTimeUtil;
import com.fasterxml.jackson.core.type.TypeReference;
import com.infoloop.tianting.SingleDishSkuByIdsRpcResponse;
import com.infoloop.tianting.clientcustomerorderservice.OnlinePayEnum;
import com.infoloop.tianting.clientcustomerorderservice.OrderOperationRecord;
import com.infoloop.tianting.clientcustomerorderservice.OrderOperationType;
import com.infoloop.tianting.clientcustomerorderservice.OrderStatusEnum;
import com.infoloop.tianting.clientcustomerorderservice.PayStatusEnum;
import com.infoloop.tianting.clientcustomerorderservice.ServeStatusEnum;
import com.infoloop.tianting.clientcustomerorderservice.SingleClientCustomerOrderDetailRpcResponse;
import com.infoloop.tianting.clientcustomerorderservice.SingleClientCustomerOrderRpcResponse;
import com.infoloop.tianting.clientcustomerservice.SingleClientCustomerRpcResponse;
import com.infoloop.tianting.clientinventoryservice.SingleClientRpcResponse;
import com.infoloop.tianting.context.LoginContextHolder;
import com.infoloop.tianting.enums.SettlementDimensionEnum;
import com.infoloop.tianting.exception.ClientEndExceptions.BusinessException;
import com.infoloop.tianting.exception.ErrorCodeEnum;
import com.infoloop.tianting.menuservice.SingleMenuDetailRpcResponse;
import com.infoloop.tianting.menuservice.SingleMenuRpcResponse;
import com.infoloop.tianting.model.bo.OrderDetailBO;
import com.infoloop.tianting.model.dto.OrderDbDTO.QueryOrderByConditionDto;
import com.infoloop.tianting.model.dto.OrderDbDTO.QueryOrderDto;
import com.infoloop.tianting.model.dto.SettlementDTO;
import com.infoloop.tianting.model.dto.SettlementDTO.SeparateMealDataDto;
import com.infoloop.tianting.model.dto.SettlementDTO.SeparateMealDetail;
import com.infoloop.tianting.model.dto.SettlementDTO.SettlementDataByMealDto;
import com.infoloop.tianting.model.dto.SettlementDTO.SettlementDataDto;
import com.infoloop.tianting.model.dto.SettlementDTO.SettlementMenuMealDto;
import com.infoloop.tianting.model.dto.SettlementDTO.SettlementMenuSkuDto;
import com.infoloop.tianting.model.dto.SettlementDTO.SettlementRequestDto;
import com.infoloop.tianting.model.enums.desc.BaseProtoDescEnum;
import com.infoloop.tianting.model.enums.desc.OnlinePayDescEnum;
import com.infoloop.tianting.service.SettlementService;
import com.infoloop.tianting.service.client.ClientCustomerServiceRpcClient;
import com.infoloop.tianting.service.client.ClientInventoryServiceRpcClient;
import com.infoloop.tianting.service.client.ClientResourceTagServiceRpcClient;
import com.infoloop.tianting.service.client.MealServiceRpcClient;
import com.infoloop.tianting.service.client.MenuServiceRpcClient;
import com.infoloop.tianting.service.client.OrderServiceRpcClient;
import com.infoloop.tianting.service.client.SkuServiceRpcClient;
import com.infoloop.tianting.setmealscheduleservice.SingleMealRpcResponse;
import com.infoloop.tianting.utils.DateUtil;
import com.infoloop.tianting.utils.JsonUtil;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.temporal.ChronoUnit;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.function.Function;
import java.util.stream.Collectors;
import java.util.stream.Stream;

@Slf4j
@Service
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
public class SettlementServiceImpl implements SettlementService {
    private final OrderServiceRpcClient orderServiceRpcClient;
    private final MenuServiceRpcClient menuServiceRpcClient;
    private final SkuServiceRpcClient skuServiceRpcClient;
    private final ClientInventoryServiceRpcClient clientInventoryServiceRpcClient;
    private final MealServiceRpcClient mealServiceRpcClient;
    private final ClientCustomerServiceRpcClient clientCustomerServiceRpcClient;
    private final ClientResourceTagServiceRpcClient clientResourceTagServiceRpcClient;

    public static ByteArrayOutputStream writeToExcelBySku(SettlementDataDto settlementDataDto)
            throws IOException {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("订单数据");

        final var classifyOrders = settlementDataDto.getClassifyOrders();

        int rowNum = 0;
        Row sheetTitleRow = sheet.createRow(rowNum++);
        Cell sheetTitleCell = sheetTitleRow.createCell(0);
        sheetTitleCell.setCellValue(settlementDataDto.getClient());
        Row dateRangeRow = sheet.createRow(rowNum++);
        Cell dateRangeCell = dateRangeRow.createCell(0);
        final var dateRange = settlementDataDto.getStartTime() + "至" + settlementDataDto.getEndTime();
        dateRangeCell.setCellValue(dateRange);
        Row titleRow = sheet.createRow(rowNum++);
        Cell dateTitleCell = titleRow.createCell(0);
        dateTitleCell.setCellValue("点餐日期");
        Cell menuTitleCell = titleRow.createCell(1);
        menuTitleCell.setCellValue("餐单名称");
        Cell skuCodeTitleCell = titleRow.createCell(2);
        skuCodeTitleCell.setCellValue("菜品编号");

        Cell skuNameTitleCell = titleRow.createCell(3);
        skuNameTitleCell.setCellValue("菜品名称");
        Cell skuPriceTitleCell = titleRow.createCell(4);
        skuPriceTitleCell.setCellValue("单价");
        Cell skuCountTitleCell = titleRow.createCell(5);
        skuCountTitleCell.setCellValue("数量");
        Cell skuTotalPriceTitleCell = titleRow.createCell(6);
        skuTotalPriceTitleCell.setCellValue("合计金额");

        for (Map.Entry<String, Map<String, List<SettlementMenuSkuDto>>> outerEntry : classifyOrders.entrySet()) {
            String orderDate = outerEntry.getKey();
            Map<String, List<SettlementMenuSkuDto>> innerMap = outerEntry.getValue();
            int curDateTotalRow = 0;
            for (Map.Entry<String, List<SettlementMenuSkuDto>> innerEntry : innerMap.entrySet()) {
                curDateTotalRow += innerEntry.getValue().size();
            }

            // 写入日期列并合并单元格
            int startRowForDate = rowNum;
            for (int i = 0; i < curDateTotalRow; i++) {
                Row dateRow = sheet.createRow(rowNum++);
                Cell dateCell = dateRow.createCell(0);
                dateCell.setCellValue(orderDate);
            }
            int endRowForDate = rowNum - 1;
            if (endRowForDate > startRowForDate) {
                CellRangeAddress dateRegion = new CellRangeAddress(startRowForDate, endRowForDate, 0, 0);
                sheet.addMergedRegion(dateRegion);
            }

            rowNum = startRowForDate;
            for (Map.Entry<String, List<SettlementMenuSkuDto>> innerEntry : innerMap.entrySet()) {
                String menuName = innerEntry.getKey();
                List<SettlementMenuSkuDto> skuList = innerEntry.getValue();

                // 写入菜单ID列并合并单元格
                int startRowForMenu = rowNum;
                for (int i = 0; i < skuList.size(); i++) {
                    Row menuRow = sheet.getRow(rowNum++);
                    Cell menuCell = menuRow.createCell(1);
                    menuCell.setCellValue(menuName);
                }
                int endRowForMenu = rowNum - 1;
                if (endRowForMenu > startRowForMenu) {
                    CellRangeAddress menuRegion = new CellRangeAddress(startRowForMenu, endRowForMenu, 1, 1);
                    sheet.addMergedRegion(menuRegion);
                }

                // 写入SKU列
                rowNum = startRowForMenu;
                for (SettlementMenuSkuDto skuDto : skuList) {
                    Row skuRow = sheet.getRow(rowNum++);
                    Cell skuCodeCell = skuRow.createCell(2);
                    skuCodeCell.setCellValue(skuDto.getSkuCode());
                    Cell showNameCell = skuRow.createCell(3);
                    showNameCell.setCellValue(skuDto.getShowName());
                    Cell priceCell = skuRow.createCell(4);
                    priceCell.setCellValue((double) skuDto.getPrice() / 100);
                    Cell countCell = skuRow.createCell(5);
                    countCell.setCellValue(skuDto.getCount());
                    Cell totalPriceCell = skuRow.createCell(6);
                    totalPriceCell.setCellValue((double) skuDto.getTotalPrice() / 100);
                }
            }
        }

        // 自动调整列宽
        for (int i = 0; i < 7; i++) {
            sheet.autoSizeColumn(i);
        }

        // 将工作簿内容写入字节数组输出流
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.close();

        return outputStream;
    }

    public static ByteArrayOutputStream writeToExcelByMeal(SettlementDataByMealDto settlementDataDto)
            throws IOException {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("订单数据");

        final var classifyOrders = settlementDataDto.getClassifyOrders();

        int rowNum = 0;
        Row sheetTitleRow = sheet.createRow(rowNum++);
        Cell sheetTitleCell = sheetTitleRow.createCell(0);
        sheetTitleCell.setCellValue(settlementDataDto.getClient());
        Row dateRangeRow = sheet.createRow(rowNum++);
        Cell dateRangeCell = dateRangeRow.createCell(0);
        final var startDateStr = DateUtil.formatDate(settlementDataDto.getStartTime(), DateUtil.YYYY_MM_DD);
        final var endDateStr = DateUtil.formatDate(settlementDataDto.getEndTime(), DateUtil.YYYY_MM_DD);
        final var dateRange = startDateStr + "至" + endDateStr;
        dateRangeCell.setCellValue(dateRange);
        Row titleRow = sheet.createRow(rowNum++);
        int columnNum = 0;
        Cell menuTitleCell = titleRow.createCell(columnNum++);
        menuTitleCell.setCellValue("餐单名称");
        Cell mealTitleCell = titleRow.createCell(columnNum++);
        mealTitleCell.setCellValue("餐别");

        final var dates = getDatesBetween(settlementDataDto.getStartTime(),
                settlementDataDto.getEndTime());
        for (String date : dates) {
            Cell dateTitleCell = titleRow.createCell(columnNum++);
            dateTitleCell.setCellValue(date);
        }
        Cell priceTitleCell = titleRow.createCell(columnNum);
        priceTitleCell.setCellValue("总金额");
        for (Map.Entry<String, Map<String, Map<String, List<SettlementMenuMealDto>>>> outerEntry : classifyOrders.entrySet()) {
            String menuNme = outerEntry.getKey();
            Map<String, Map<String, List<SettlementMenuMealDto>>> innerMap = outerEntry.getValue();
            int curMenuTotalRow = innerMap.size();
            // 写入menu列并合并单元格
            int startRowForMenu = rowNum;
            for (int i = 0; i < curMenuTotalRow; i++) {
                Row dateRow = sheet.createRow(rowNum++);
                Cell menuCell = dateRow.createCell(0);
                menuCell.setCellValue(menuNme);
            }
            int endRowForDate = rowNum - 1;
            if (endRowForDate > startRowForMenu) {
                CellRangeAddress dateRegion = new CellRangeAddress(startRowForMenu, endRowForDate, 0, 0);
                sheet.addMergedRegion(dateRegion);
            }

            rowNum = startRowForMenu;
            for (Map.Entry<String, Map<String, List<SettlementMenuMealDto>>> innerEntry : innerMap.entrySet()) {
                String mealName = innerEntry.getKey();
                Map<String, List<SettlementMenuMealDto>> skuMap = innerEntry.getValue();

                // 写入meal列并合并单元格
                Row mealRow = sheet.getRow(rowNum++);
                Cell mealCell = mealRow.createCell(1);
                mealCell.setCellValue(mealName);

                columnNum = 2;
                int totalPrice = 0;
                for (String date : dates) {
                    List<SettlementMenuMealDto> toWriteDateSkuList = skuMap.get(date);
                    if (toWriteDateSkuList == null) {
                        toWriteDateSkuList = new ArrayList<>();
                    }
                    int price = 0;
                    if (!toWriteDateSkuList.isEmpty()) {
                        for (SettlementMenuMealDto settlementMenuMealDto : toWriteDateSkuList) {
                            price += settlementMenuMealDto.getPrice() * settlementMenuMealDto.getCount();
                        }
                    }
                    totalPrice += price;
                    Cell priceCell = mealRow.createCell(columnNum++);
                    priceCell.setCellValue((double) price / 100);
                }
                Cell totalPriceCell = mealRow.createCell(columnNum);
                totalPriceCell.setCellValue((double) totalPrice / 100);
            }
        }

        // 自动调整列宽
        final var columnCnt = dates.size() + 2;
        for (int i = 0; i < columnCnt; i++) {
            sheet.autoSizeColumn(i);
        }

        // 将工作簿内容写入字节数组输出流
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.close();

        return outputStream;
    }

    public static List<String> getDatesBetween(LocalDateTime startTime, LocalDateTime endTime) {
        List<String> dateList = new ArrayList<>();
        LocalDate startDate = startTime.toLocalDate();
        LocalDate endDate = endTime.toLocalDate();

        long daysBetween = ChronoUnit.DAYS.between(startDate, endDate) + 1;
        for (int i = 0; i < daysBetween; i++) {
            LocalDate currentDate = startDate.plusDays(i);
            dateList.add(DateUtil.formatDate(currentDate, DateUtil.YYYY_MM_DD));
        }
        return dateList;
    }

    /**
     * 按订单维度生成Excel
     */
    private static ByteArrayOutputStream writeToExcelByOrder(SettlementDTO.SettlementDataByOrderDto settlementDataDto)
            throws IOException {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("客人结算");

        // 创建表头样式（黑色边框）
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);
        headerStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        headerStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        headerStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        headerStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

        // 创建数据行样式（浅灰色边框）
        CellStyle dataStyle = workbook.createCellStyle();
        dataStyle.setBorderTop(BorderStyle.THIN);
        dataStyle.setBorderBottom(BorderStyle.THIN);
        dataStyle.setBorderLeft(BorderStyle.THIN);
        dataStyle.setBorderRight(BorderStyle.THIN);
        dataStyle.setTopBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        dataStyle.setBottomBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        dataStyle.setLeftBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        dataStyle.setRightBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());

        int rowNum = 0;

        // 创建表头
        Row titleRow = sheet.createRow(rowNum++);
        int columnNum = 0;

        Cell cell0 = titleRow.createCell(columnNum++);
        cell0.setCellValue("用餐时间");
        cell0.setCellStyle(headerStyle);

        Cell cell1 = titleRow.createCell(columnNum++);
        cell1.setCellValue("订单号");
        cell1.setCellStyle(headerStyle);

        Cell cell2 = titleRow.createCell(columnNum++);
        cell2.setCellValue("餐别");
        cell2.setCellStyle(headerStyle);

        Cell cell3 = titleRow.createCell(columnNum++);
        cell3.setCellValue("房间号");
        cell3.setCellStyle(headerStyle);

        Cell cell4 = titleRow.createCell(columnNum++);
        cell4.setCellValue("客户姓名");
        cell4.setCellStyle(headerStyle);

        Cell cell5 = titleRow.createCell(columnNum++);
        cell5.setCellValue("客户腕带号");
        cell5.setCellStyle(headerStyle);

        Cell cell6 = titleRow.createCell(columnNum++);
        cell6.setCellValue("忌口信息");
        cell6.setCellStyle(headerStyle);

        Cell cell7 = titleRow.createCell(columnNum++);
        cell7.setCellValue("医嘱信息");
        cell7.setCellStyle(headerStyle);

        Cell cell8 = titleRow.createCell(columnNum++);
        cell8.setCellValue("餐单名称");
        cell8.setCellStyle(headerStyle);

        Cell cell9 = titleRow.createCell(columnNum++);
        cell9.setCellValue("菜品名称");
        cell9.setCellStyle(headerStyle);

        Cell cell10 = titleRow.createCell(columnNum++);
        cell10.setCellValue("菜品份数");
        cell10.setCellStyle(headerStyle);

        Cell cell11 = titleRow.createCell(columnNum++);
        cell11.setCellValue("菜品单价");
        cell11.setCellStyle(headerStyle);

        Cell cell12 = titleRow.createCell(columnNum++);
        cell12.setCellValue("菜品金额");
        cell12.setCellStyle(headerStyle);

        Cell cell13 = titleRow.createCell(columnNum++);
        cell13.setCellValue("菜品状态");
        cell13.setCellStyle(headerStyle);

        Cell cell14 = titleRow.createCell(columnNum++);
        cell14.setCellValue("交易码");
        cell14.setCellStyle(headerStyle);

        Cell cell15 = titleRow.createCell(columnNum++);
        cell15.setCellValue("备注");
        cell15.setCellStyle(headerStyle);

        Cell cell16 = titleRow.createCell(columnNum++);
        cell16.setCellValue("支付方式");
        cell16.setCellStyle(headerStyle);

        // 填充数据
        final var classifyOrders = settlementDataDto.getClassifyOrders();
        if (classifyOrders != null) {
            for (SettlementDTO.SettlementOrderDetail orderDetail : classifyOrders) {
                Row dataRow = sheet.createRow(rowNum++);
                columnNum = 0;

                Cell dataCell0 = dataRow.createCell(columnNum++);
                dataCell0.setCellValue(orderDetail.getMealTime() != null ? orderDetail.getMealTime() : "");
                dataCell0.setCellStyle(dataStyle);

                Cell dataCell1 = dataRow.createCell(columnNum++);
                dataCell1.setCellValue(orderDetail.getCode() != null ? orderDetail.getCode() : "");
                dataCell1.setCellStyle(dataStyle);

                Cell dataCell2 = dataRow.createCell(columnNum++);
                dataCell2.setCellValue(orderDetail.getMealName() != null ? orderDetail.getMealName() : "");
                dataCell2.setCellStyle(dataStyle);

                Cell dataCell3 = dataRow.createCell(columnNum++);
                dataCell3.setCellValue(orderDetail.getRoomNo() != null ? orderDetail.getRoomNo() : "");
                dataCell3.setCellStyle(dataStyle);

                Cell dataCell4 = dataRow.createCell(columnNum++);
                dataCell4.setCellValue(orderDetail.getCustomerName() != null ? orderDetail.getCustomerName() : "");
                dataCell4.setCellStyle(dataStyle);

                Cell dataCell5 = dataRow.createCell(columnNum++);
                dataCell5.setCellValue(orderDetail.getHISCustomerNo() != null ? orderDetail.getHISCustomerNo() : "");
                dataCell5.setCellStyle(dataStyle);

                // 忌口列表转字符串
                String avoidsStr = orderDetail.getAvoids() != null ? String.join(", ", orderDetail.getAvoids()) : "";
                Cell dataCell6 = dataRow.createCell(columnNum++);
                dataCell6.setCellValue(avoidsStr);
                dataCell6.setCellStyle(dataStyle);

                // 医嘱列表转字符串
                String doctorsStr = orderDetail.getDoctors() != null ? String.join(", ", orderDetail.getDoctors()) : "";
                Cell dataCell7 = dataRow.createCell(columnNum++);
                dataCell7.setCellValue(doctorsStr);
                dataCell7.setCellStyle(dataStyle);

                Cell dataCell8 = dataRow.createCell(columnNum++);
                dataCell8.setCellValue(orderDetail.getMenuName() != null ? orderDetail.getMenuName() : "");
                dataCell8.setCellStyle(dataStyle);

                Cell dataCell9 = dataRow.createCell(columnNum++);
                dataCell9.setCellValue(orderDetail.getSkuName() != null ? orderDetail.getSkuName() : "");
                dataCell9.setCellStyle(dataStyle);

                Cell dataCell10 = dataRow.createCell(columnNum++);
                dataCell10.setCellValue(orderDetail.getCount() != null ? orderDetail.getCount() : 0);
                dataCell10.setCellStyle(dataStyle);

                // 价格
                Cell dataCell11 = dataRow.createCell(columnNum++);
                if (orderDetail.getPrice() != null) {
                    dataCell11.setCellValue(orderDetail.getPrice().doubleValue());
                } else {
                    dataCell11.setCellValue(0.0);
                }
                dataCell11.setCellStyle(dataStyle);

                // 总价
                Cell dataCell12 = dataRow.createCell(columnNum++);
                if (orderDetail.getTotalPrice() != null) {
                    dataCell12.setCellValue(orderDetail.getTotalPrice().doubleValue());
                } else {
                    dataCell12.setCellValue(0.0);
                }
                dataCell12.setCellStyle(dataStyle);

                // 状态
                Cell dataCell13 = dataRow.createCell(columnNum++);
                dataCell13.setCellValue(orderDetail.getOperationType());
                dataCell13.setCellStyle(dataStyle);

                Cell dataCell14 = dataRow.createCell(columnNum++);
                dataCell14.setCellValue(orderDetail.getTransactionId() != null ? orderDetail.getTransactionId() : "");
                dataCell14.setCellStyle(dataStyle);

                // 合并备注和忌口备注，用分号分割，备注在前
                String remark = orderDetail.getRemark() != null ? orderDetail.getRemark() : "";
                String avoidRemark = orderDetail.getAvoidRemark() != null ? orderDetail.getAvoidRemark() : "";
                String combinedRemark = "";
                if (!remark.isEmpty() && !avoidRemark.isEmpty()) {
                    combinedRemark = remark + "；" + avoidRemark;
                } else if (!remark.isEmpty()) {
                    combinedRemark = remark;
                } else if (!avoidRemark.isEmpty()) {
                    combinedRemark = avoidRemark;
                }
                Cell dataCell15 = dataRow.createCell(columnNum++);
                dataCell15.setCellValue(combinedRemark);
                dataCell15.setCellStyle(dataStyle);

                Cell dataCell16 = dataRow.createCell(columnNum++);
                dataCell16.setCellValue(orderDetail.getOnlinePay() != null ? orderDetail.getOnlinePay() : "");
                dataCell16.setCellStyle(dataStyle);
            }
        }

        // 自动调整列宽
        for (int i = 0; i < 17; i++) {
            sheet.autoSizeColumn(i);
        }

        // 将工作簿内容写入字节数组输出流
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.close();

        return outputStream;
    }

    /**
     * 生成分餐表Excel
     */
    private static ByteArrayOutputStream writeToExcelBySeparateMeal(SeparateMealDataDto separateMealDataDto)
            throws IOException {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("分餐表");

        // 创建标题样式（合并单元格标题）
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setBorderTop(BorderStyle.THIN);
        titleStyle.setBorderBottom(BorderStyle.THIN);
        titleStyle.setBorderLeft(BorderStyle.THIN);
        titleStyle.setBorderRight(BorderStyle.THIN);
        titleStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        titleStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        titleStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        titleStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        Font titleFont = workbook.createFont();
        titleFont.setBold(true);
        titleFont.setFontHeightInPoints((short) 16);
        titleStyle.setFont(titleFont);

        // 创建表头样式（黑色边框）
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);
        headerStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        headerStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        headerStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        headerStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

        // 创建数据行样式（浅灰色边框）
        CellStyle dataStyle = workbook.createCellStyle();
        dataStyle.setBorderTop(BorderStyle.THIN);
        dataStyle.setBorderBottom(BorderStyle.THIN);
        dataStyle.setBorderLeft(BorderStyle.THIN);
        dataStyle.setBorderRight(BorderStyle.THIN);
        dataStyle.setTopBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        dataStyle.setBottomBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        dataStyle.setLeftBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        dataStyle.setRightBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());

        int rowNum = 0;

        // 第一行：创建合并单元格标题
        Row mainTitleRow = sheet.createRow(rowNum++);
        Cell mainTitleCell = mainTitleRow.createCell(0);
        mainTitleCell.setCellValue("住院客户分餐表");
        mainTitleCell.setCellStyle(titleStyle);

        // 合并第一行的8列
        CellRangeAddress titleRegion = new CellRangeAddress(0, 0, 0, 8);
        sheet.addMergedRegion(titleRegion);

        // 为合并区域设置边框
        RegionUtil.setBorderTop(BorderStyle.THIN, titleRegion, sheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, titleRegion, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, titleRegion, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, titleRegion, sheet);
        RegionUtil.setTopBorderColor(IndexedColors.BLACK.getIndex(), titleRegion, sheet);
        RegionUtil.setBottomBorderColor(IndexedColors.BLACK.getIndex(), titleRegion, sheet);
        RegionUtil.setLeftBorderColor(IndexedColors.BLACK.getIndex(), titleRegion, sheet);
        RegionUtil.setRightBorderColor(IndexedColors.BLACK.getIndex(), titleRegion, sheet);

        // 第二行：创建表头
        Row titleRow = sheet.createRow(rowNum++);
        int columnNum = 0;

        Cell cell0 = titleRow.createCell(columnNum++);
        cell0.setCellValue("餐别");
        cell0.setCellStyle(headerStyle);

        Cell cell1 = titleRow.createCell(columnNum++);
        cell1.setCellValue("房间号");
        cell1.setCellStyle(headerStyle);

        Cell cell2 = titleRow.createCell(columnNum++);
        cell2.setCellValue("客户姓名");
        cell2.setCellStyle(headerStyle);

        Cell cell3 = titleRow.createCell(columnNum++);
        cell3.setCellValue("医嘱信息");
        cell3.setCellStyle(headerStyle);

        Cell cell4 = titleRow.createCell(columnNum++);
        cell4.setCellValue("餐单名称");
        cell4.setCellStyle(headerStyle);

        Cell cell5 = titleRow.createCell(columnNum++);
        cell5.setCellValue("菜品名称");
        cell5.setCellStyle(headerStyle);

        Cell cell6 = titleRow.createCell(columnNum++);
        cell6.setCellValue("菜品数量");
        cell6.setCellStyle(headerStyle);

        Cell cell7 = titleRow.createCell(columnNum++);
        cell7.setCellValue("忌口信息");
        cell7.setCellStyle(headerStyle);

        Cell cell8 = titleRow.createCell(columnNum++);
        cell8.setCellValue("备注");
        cell8.setCellStyle(headerStyle);

        // 填充数据
        final var mealDetails = separateMealDataDto.getMealDetails();
        if (mealDetails != null) {
            for (SeparateMealDetail mealDetail : mealDetails) {
                Row dataRow = sheet.createRow(rowNum++);
                columnNum = 0;

                Cell dataCell0 = dataRow.createCell(columnNum++);
                dataCell0.setCellValue(mealDetail.getMealName() != null ? mealDetail.getMealName() : "");
                dataCell0.setCellStyle(dataStyle);

                Cell dataCell1 = dataRow.createCell(columnNum++);
                dataCell1.setCellValue(mealDetail.getRoomNo() != null ? mealDetail.getRoomNo() : "");
                dataCell1.setCellStyle(dataStyle);

                Cell dataCell2 = dataRow.createCell(columnNum++);
                dataCell2.setCellValue(mealDetail.getCustomerName() != null ? mealDetail.getCustomerName() : "");
                dataCell2.setCellStyle(dataStyle);

                Cell dataCell3 = dataRow.createCell(columnNum++);
                dataCell3.setCellValue(mealDetail.getDoctors() != null ? mealDetail.getDoctors() : "");
                dataCell3.setCellStyle(dataStyle);

                Cell dataCell4 = dataRow.createCell(columnNum++);
                dataCell4.setCellValue(mealDetail.getMenuName() != null ? mealDetail.getMenuName() : "");
                dataCell4.setCellStyle(dataStyle);

                Cell dataCell5 = dataRow.createCell(columnNum++);
                dataCell5.setCellValue(mealDetail.getSkuName() != null ? mealDetail.getSkuName() : "");
                dataCell5.setCellStyle(dataStyle);

                Cell dataCell6 = dataRow.createCell(columnNum++);
                dataCell6.setCellValue(mealDetail.getCount() != null ? mealDetail.getCount() : 0);
                dataCell6.setCellStyle(dataStyle);

                Cell dataCell7 = dataRow.createCell(columnNum++);
                dataCell7.setCellValue(mealDetail.getAvoids() != null ? mealDetail.getAvoids() : "");
                dataCell7.setCellStyle(dataStyle);

                Cell dataCell8 = dataRow.createCell(columnNum++);
                dataCell8.setCellValue(mealDetail.getRemark() != null ? mealDetail.getRemark() : "");
                dataCell8.setCellStyle(dataStyle);
            }
        }

        // 自动调整列宽
        for (int i = 0; i < 9; i++) {
            sheet.autoSizeColumn(i);
        }

        // 将工作簿内容写入字节数组输出流
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.close();

        return outputStream;
    }

    /**
     * 创建空的分餐表Excel（无数据）
     */
    private static ByteArrayOutputStream createEmptyExcelBySeparateMeal() {
        try {
            SeparateMealDataDto emptyData = SeparateMealDataDto.builder()
                    .mealDetails(new ArrayList<>())
                    .build();
            return writeToExcelBySeparateMeal(emptyData);
        } catch (IOException e) {
            log.error("创建空分餐表Excel失败", e);
            throw BusinessException.build(ErrorCodeEnum.GENERATE_EXCEL_ERROR);
        }
    }

    @Override
    public ByteArrayOutputStream generateOrderSettlementExcel(SettlementRequestDto settlementRequest) {
        if (settlementRequest.getDimension().equals(SettlementDimensionEnum.BY_SKU)) {
            return generateOrderSettlementExcelBySku(settlementRequest);
        } else if (settlementRequest.getDimension().equals(SettlementDimensionEnum.BY_MEAL)) {
            return generateOrderSettlementExcelByMeal(settlementRequest);
        } else if (settlementRequest.getDimension().equals(SettlementDimensionEnum.BY_ORDER)) {
            return generateOrderSettlementExcelByOrder(settlementRequest);
        }
        return null;
    }

    private ByteArrayOutputStream generateOrderSettlementExcelByOrder(SettlementRequestDto settlementRequest) {
        // 参数校验
        if (settlementRequest == null || settlementRequest.getTimeStart() == null || settlementRequest.getTimeEnd() == null) {
            throw BusinessException.build(ErrorCodeEnum.GENERATE_EXCEL_ERROR);
        }

        final var loginInfo = LoginContextHolder.getLoginInfo();
        final var userResources = clientResourceTagServiceRpcClient.getUserResources(loginInfo.getEnterpriseId(), loginInfo.getClientId(), loginInfo.getId());

        // 计算日期范围
        final var startDate = settlementRequest.getTimeStart().plusHours(8).toLocalDate().atStartOfDay();
        final var endDate = settlementRequest.getTimeEnd()
                .plusHours(8)
                .toLocalDate()
                .atTime(23, 59, 59, 999_000_000);

        // 获取企业ID
        final var operatorLoginInfo = LoginContextHolder.getLoginInfo();
        final int enterpriseId = LoginContextHolder.hasLogin() ? operatorLoginInfo.getEnterpriseId() : settlementRequest.getEnterpriseId();

        final var stallIds = new ArrayList<Integer>();
        if (CollectionUtil.isEmpty(settlementRequest.getStallIds())) {
            stallIds.addAll(userResources.getStallIdsList());
        } else {
            stallIds.addAll(settlementRequest.getStallIds());
        }

        // 构建查询条件
        final var queryOrderDto = QueryOrderByConditionDto.builder()
                .enterpriseId(enterpriseId)
                .shouldFilterMealTimeStart(true)
                .mealTimeStart(startDate)
                .shouldFilterMealTimeEnd(true)
                .mealTimeEnd(endDate)
                .shouldFilterStallIds(true)
                .stallIds(stallIds)
                .build();

        // 查询订单列表
        final var orderList = orderServiceRpcClient.getOrdersByCondition(queryOrderDto);
        if (orderList == null || orderList.isEmpty()) {
            log.warn("未查询到订单数据，enterpriseId: {}, startDate: {}, endDate: {}", enterpriseId, startDate, endDate);
            return createEmptyExcelByOrder();
        }

        // 筛选有效订单ID
        List<Integer> orderIds = orderList.stream()
                .filter(Objects::nonNull)
                .filter(o -> {
                    // 在线支付订单需要支付成功
                    if (o.getOnlinePay() == OnlinePayEnum.ONLINE) {
                        return o.getPayStatus() == PayStatusEnum.SUCCEED;
                    } else {
                        // 非在线支付订单需要未取消
                        return o.getStatus() != OrderStatusEnum.ORDER_CANCELED;
                    }
                })
                .map(SingleClientCustomerOrderRpcResponse::getId)
                .distinct()
                .collect(Collectors.toList());

        if (orderIds.isEmpty()) {
            return createEmptyExcelByOrder();
        }

        // 构建订单映射
        final var orderMap = orderList.stream().filter(o -> o.getId() != 0)
                .collect(Collectors.toMap(SingleClientCustomerOrderRpcResponse::getId, o -> o, (o1, o2) -> o1));

        // 查询订单详情
        final var orderDetailList = orderServiceRpcClient.getOrderDetailsByOrderIds(orderIds);
        if (orderDetailList == null || orderDetailList.isEmpty()) {
            log.warn("未查询到订单详情数据，orderIds: {}", orderIds);
            return createEmptyExcelByOrder();
        }
        // 订单详情按订单ID分组
        final var orderDetailGroupByOrderId = orderDetailList.stream().filter(Objects::nonNull).collect(Collectors.groupingBy(SingleClientCustomerOrderDetailRpcResponse::getOrderId));

        final var orderOperationRecordsByOrderIds = orderServiceRpcClient.getOrderOperationRecordsByOrderIds(LoginContextHolder.getEnterpriseId(), orderIds).getResponsesList();

        // 订单操作记录按订单ID分组
        final var orderOperationRecordByOrderId = orderOperationRecordsByOrderIds.stream().filter(Objects::nonNull).collect(Collectors.groupingBy(OrderOperationRecord::getOrderId));

        List<Integer> skuIds = Stream.concat(
                        orderOperationRecordsByOrderIds.stream()
                                .filter(e -> StringUtils.isNoneBlank(e.getOriginalOrderDetailJson()))
                                .flatMap(e -> JsonUtil.readJsonAs(e.getOriginalOrderDetailJson(), new TypeReference<List<OrderDetailBO>>() {}).stream())
                                .map(OrderDetailBO::getSkuId),
                        orderDetailList.stream()
                                .filter(Objects::nonNull)
                                .map(SingleClientCustomerOrderDetailRpcResponse::getSkuId)
                )
                .distinct()
                .collect(Collectors.toList());

        final var skuResponse = skuServiceRpcClient.getDishSkusByIds(skuIds);
        final List<SingleDishSkuByIdsRpcResponse> skus = skuResponse.getResponseList();
        final var skuMap = skus.stream().collect(Collectors.toMap(SingleDishSkuByIdsRpcResponse::getId, s -> s, (s1, s2) -> s1));

        // 获取餐别数据
        final var mealResponse = mealServiceRpcClient.getAllMeals(enterpriseId);
        final List<SingleMealRpcResponse> meals = mealResponse != null ? mealResponse.getResponsesList() : new ArrayList<>();
        final var mealMap = meals.stream().collect(Collectors.toMap(SingleMealRpcResponse::getId, m -> m, (m1, m2) -> m1));

        // 获取菜单数据
        List<Integer> menuIds = orderList.stream().map(SingleClientCustomerOrderRpcResponse::getMenuId).distinct().collect(Collectors.toList());
        final var menuResponse = menuServiceRpcClient.getMenusByIds(enterpriseId, menuIds);
        final List<SingleMenuRpcResponse> menuList = menuResponse != null ? menuResponse.getResponsesList() : new ArrayList<>();
        final var menuMap = menuList.stream().collect(Collectors.toMap(SingleMenuRpcResponse::getId, m -> m, (m1, m2) -> m1));

        // 获取菜单详情数据
        final var menuDetailResponse = menuServiceRpcClient.queryMenuDetailsByMenuIds(enterpriseId, menuIds);
        final List<SingleMenuDetailRpcResponse> menuDetailList =  menuDetailResponse.getResponsesList();
        final var menuDetailMap = menuDetailList.stream().collect(Collectors.toMap(SingleMenuDetailRpcResponse::getId, m -> m, (m1, m2) -> m1));

        // 获取客户数据
        List<Integer> customerIds = orderList.stream().map(SingleClientCustomerOrderRpcResponse::getCustomerId).distinct().collect(Collectors.toList());
        final var customerResponse = clientCustomerServiceRpcClient.getClientCustomersByIds(enterpriseId, customerIds);
        final List<SingleClientCustomerRpcResponse> customers = customerResponse.getResponsesList();
        final var customerMap = customers.stream().collect(Collectors.toMap(SingleClientCustomerRpcResponse::getId, c -> c, (c1, c2) -> c1));

        // 构建结算订单明细
        final var classifyOrders = new ArrayList<SettlementDTO.SettlementOrderDetail>();
        for (Integer orderId : orderIds) {
            final var orderDetails = orderDetailGroupByOrderId.getOrDefault(orderId, Collections.emptyList());
            final var order = orderMap.get(orderId);
            if (order == null) {
                continue;
            }
            // 获取菜单信息
            final var menu = menuMap.get(order.getMenuId());
            final String menuName = menu != null ? menu.getName() : "";

            // 获取客户信息
            final var customer = customerMap.get(order.getCustomerId());
            final String customerName = customer != null ? customer.getName() : "";
            final String hisCustomerNo = customer != null ? customer.getHISCustomerNo() : "";

            final var orderOperationRecords = orderOperationRecordByOrderId.get(orderId);

            Map<Integer, OrderDetailBO> addOriginalDetailMap = orderOperationRecords.stream()
                    .filter(e -> e.getType() == OrderOperationType.ADD_DISHES)
                    .flatMap(e -> JsonUtil.readJsonAs(e.getOriginalOrderDetailJson(), new TypeReference<List<OrderDetailBO>>() {
                    }).stream())
                    .collect(Collectors.toMap(OrderDetailBO::getId, Function.identity(), (a, b) -> a));

            Map<Integer, OrderDetailBO> replaceOriginalDetailMap = orderOperationRecords.stream()
                    .filter(e -> e.getType() == OrderOperationType.REPLACE_DISHES)
                    .flatMap(e -> JsonUtil.readJsonAs(e.getOriginalOrderDetailJson(), new TypeReference<List<OrderDetailBO>>() {
                    }).stream())
                    .collect(Collectors.toMap(OrderDetailBO::getId, Function.identity(), (a, b) -> a));

            for (SingleClientCustomerOrderDetailRpcResponse orderDetail : orderDetails) {
                // 1. 先处理加餐
                if (addOriginalDetailMap.containsKey(orderDetail.getId())
                        && addOriginalDetailMap.get(orderDetail.getId()).getCount() != orderDetail.getCount()) {
                    final var orderDetailBO = addOriginalDetailMap.get(orderDetail.getId());
                    SettlementDTO.SettlementOrderDetail beforeChange = buildSettlementOrderDetail(
                            order, orderDetail.getMenuDetailId(), orderDetail.getSkuId(), orderDetailBO.getCount(), orderDetail.getPrice(), "加餐", menuName, customerName, hisCustomerNo, menuDetailMap, mealMap, skuMap);
                    classifyOrders.add(beforeChange);
                    SettlementDTO.SettlementOrderDetail afterChange = buildSettlementOrderDetail(
                            order, orderDetail.getMenuDetailId(), orderDetail.getSkuId(), orderDetail.getCount(), orderDetail.getPrice(), "加餐", menuName, customerName, hisCustomerNo, menuDetailMap, mealMap, skuMap);
                    classifyOrders.add(afterChange);
                }

                // 2. 再处理换菜
                if (replaceOriginalDetailMap.containsKey(orderDetail.getId())
                        && !Objects.equals(replaceOriginalDetailMap.get(orderDetail.getId()).getSkuId(), orderDetail.getSkuId())) {
                    final var orderDetailBO = replaceOriginalDetailMap.get(orderDetail.getId());
                    SettlementDTO.SettlementOrderDetail beforeChange = buildSettlementOrderDetail(
                            order, orderDetail.getMenuDetailId(), orderDetailBO.getSkuId(), orderDetailBO.getCount(), orderDetailBO.getPrice(), "换菜前", menuName, customerName, hisCustomerNo, menuDetailMap, mealMap, skuMap);
                    classifyOrders.add(beforeChange);
                    SettlementDTO.SettlementOrderDetail afterChange = buildSettlementOrderDetail(
                            order, orderDetail.getMenuDetailId(), orderDetail.getSkuId(), orderDetail.getCount(), orderDetail.getPrice(), "换菜后", menuName, customerName, hisCustomerNo, menuDetailMap, mealMap, skuMap);
                    classifyOrders.add(afterChange);
                }

                // 3. 判断已退款
                if (orderDetail.getServeStatus() == ServeStatusEnum.SERVE_REJECT) {
                    SettlementDTO.SettlementOrderDetail refundRecord1 = buildSettlementOrderDetail(
                            order, orderDetail.getMenuDetailId(), orderDetail.getSkuId(), -orderDetail.getCount(), orderDetail.getPrice(), "已退款", menuName, customerName, hisCustomerNo, menuDetailMap, mealMap, skuMap);
                    classifyOrders.add(refundRecord1);
                    SettlementDTO.SettlementOrderDetail refundRecord2 = buildSettlementOrderDetail(
                            order, orderDetail.getMenuDetailId(), orderDetail.getSkuId(), orderDetail.getCount(), orderDetail.getPrice(), "已退款", menuName, customerName, hisCustomerNo, menuDetailMap, mealMap, skuMap);
                    classifyOrders.add(refundRecord2);
                }

                // 4. 如果既不是加餐，也不是换菜，也不是退款，才记录“订餐”
                boolean isNormal = !(addOriginalDetailMap.containsKey(orderDetail.getId())
                        && addOriginalDetailMap.get(orderDetail.getId()).getCount() != orderDetail.getCount())
                        && !(replaceOriginalDetailMap.containsKey(orderDetail.getId())
                        && !Objects.equals(replaceOriginalDetailMap.get(orderDetail.getId()).getSkuId(), orderDetail.getSkuId()))
                        && orderDetail.getServeStatus() != ServeStatusEnum.SERVE_REJECT;

                if (isNormal) {
                    SettlementDTO.SettlementOrderDetail normalRecord = buildSettlementOrderDetail(
                            order, orderDetail.getMenuDetailId(), orderDetail.getSkuId(), orderDetail.getCount(), orderDetail.getPrice(), "订餐", menuName, customerName, hisCustomerNo, menuDetailMap, mealMap, skuMap);
                    classifyOrders.add(normalRecord);
                }
            }

        }

        // 构建结算数据
        final var settlementDataByOrderDto = SettlementDTO.SettlementDataByOrderDto.builder()
                .classifyOrders(classifyOrders)
                .build();

        // 生成Excel
        try {
            return writeToExcelByOrder(settlementDataByOrderDto);
        } catch (IOException e) {
            log.error("生成按订单结算Excel失败", e);
            throw BusinessException.build(ErrorCodeEnum.GENERATE_EXCEL_ERROR);
        }
    }

    private SettlementDTO.SettlementOrderDetail buildSettlementOrderDetail(SingleClientCustomerOrderRpcResponse order,
                                                                           int menuDetailId,
                                                                           int skuId,
                                                                           int count,
                                                                           Integer price,
                                                                           String operationType,
                                                                           String menuName,
                                                                           String customerName,
                                                                           String hisCustomerNo,
                                                                           Map<Integer, SingleMenuDetailRpcResponse> menuDetailMap,
                                                                           Map<Integer, SingleMealRpcResponse> mealMap,
                                                                           Map<Integer, SingleDishSkuByIdsRpcResponse> skuMap) {
        String mealName = getMealNameSafely(menuDetailId, menuDetailMap, mealMap);
        String skuName = getSkuNameSafely(skuId, skuMap);
        String roomNo = getRoomNoSafely(order);
        List<String> avoids = getAvoidsSafely(order);
        List<String> doctors = getDoctorsSafely(order);
        String onlinePayDesc = getOnlinePayDescSafely(order);
        return SettlementDTO.SettlementOrderDetail.builder()
                .mealTime(order.getMealTime() != 0L ? LocalDateTimeUtil.of(order.getMealTime()).format(DateUtil.YYYY_MM_DD_LEFT_SYMBOL) : "")
                .code(order.getOrderCode())
                .mealName(mealName)
                .roomNo(roomNo)
                .customerName(customerName)
                .HISCustomerNo(hisCustomerNo)
                .avoids(avoids)
                .doctors(doctors)
                .menuName(menuName)
                .skuName(skuName)
                .count(count)
                .price(calculatePrice(price))
                .totalPrice(calculateTotalPrice(price, count))
                .remark(order.getRemark())
                .avoidRemark(order.getAvoidRemark())
                .onlinePay(onlinePayDesc)
                .operationType(operationType)
                .transactionId(order.getTransactionId())
                .build();
    }

    /**
     * 安全获取餐别名称
     */
    private String getMealNameSafely(int menuDetailId,
                                     Map<Integer, SingleMenuDetailRpcResponse> menuDetailMap,
                                     Map<Integer, SingleMealRpcResponse> mealMap) {
        if (menuDetailId == 0) {
            return "";
        }

        final var menuDetail = menuDetailMap.get(menuDetailId);
        if (menuDetail == null || menuDetail.getMealId() == 0) {
            return "";
        }

        final var meal = mealMap.get(menuDetail.getMealId());
        return meal != null ? meal.getName() : "";
    }

    /**
     * 安全获取SKU名称
     */
    private String getSkuNameSafely(int skuId,
                                    Map<Integer, SingleDishSkuByIdsRpcResponse> skuMap) {
        if (skuId == 0) {
            return "";
        }
        final var sku = skuMap.get(skuId);
        return sku != null ? sku.getName() : "";
    }

    /**
     * 安全获取房间号
     */
    private String getRoomNoSafely(SingleClientCustomerOrderRpcResponse order) {
        if (order == null) {
            return "";
        }

        String syncRoomNo = order.getSyncRoomNo();
        if (!syncRoomNo.isEmpty()) {
            return syncRoomNo;
        }

        return order.getRoomNo();
    }

    /**
     * 安全获取忌口列表
     */
    private List<String> getAvoidsSafely(SingleClientCustomerOrderRpcResponse order) {
        if (order == null) {
            return Collections.emptyList();
        }

        return order.getCustomerNoticeJson().getAvoidsList();
    }

    /**
     * 安全获取医嘱列表
     */
    private List<String> getDoctorsSafely(SingleClientCustomerOrderRpcResponse order) {
        if (order == null) {
            return Collections.emptyList();
        }

        return order.getCustomerNoticeJson().getDoctorsList();
    }

    /**
     * 安全获取在线支付描述
     */
    private String getOnlinePayDescSafely(SingleClientCustomerOrderRpcResponse order) {
        if (order == null) {
            return "";
        }

        try {
            String desc = BaseProtoDescEnum.getDescByProtoEnum(order.getOnlinePay(), OnlinePayDescEnum.class);
            return desc != null ? desc : "";
        } catch (Exception e) {
            log.warn("获取在线支付描述失败，orderId: {}", order.getId(), e);
            return "";
        }
    }

    /**
     * 计算单价
     */
    private BigDecimal calculatePrice(Integer price) {
        if (price == null) {
            return BigDecimal.ZERO;
        }
        return new BigDecimal(price).divide(new BigDecimal(100), 2, RoundingMode.HALF_UP);
    }

    /**
     * 计算总价
     */
    private BigDecimal calculateTotalPrice(Integer price, Integer count) {
        if (price == null || count == null) {
            return BigDecimal.ZERO;
        }
        return new BigDecimal(price)
                .multiply(new BigDecimal(count))
                .divide(new BigDecimal(100), 2, RoundingMode.HALF_UP);
    }

    /**
     * 创建空的订单Excel
     */
    private ByteArrayOutputStream createEmptyExcelByOrder() {
        try {
            final var emptyData = SettlementDTO.SettlementDataByOrderDto.builder()
                    .classifyOrders(Collections.emptyList())
                    .build();
            return writeToExcelByOrder(emptyData);
        } catch (IOException e) {
            log.error("创建空Excel失败", e);
            throw BusinessException.build(ErrorCodeEnum.GENERATE_EXCEL_ERROR);
        }
    }

    public ByteArrayOutputStream generateOrderSettlementExcelBySku(SettlementRequestDto settlementRequest) {
        final var startDate = settlementRequest.getTimeStart().plusHours(8).toLocalDate().atStartOfDay();
        final var endDate = settlementRequest.getTimeEnd().plusHours(8).toLocalDate().plusDays(1).atStartOfDay();
        final var operatorLoginInfo = LoginContextHolder.getLoginInfo();
        final int enterpriseId = LoginContextHolder.hasLogin() ? operatorLoginInfo.getEnterpriseId() : settlementRequest.getEnterpriseId();
        final var queryOrderDto = QueryOrderByConditionDto.builder()
                .enterpriseId(enterpriseId)
                .shouldFilterCreatedAtTimeStart(true)
                .createdAtTimeStart(startDate)
                .shouldFilterCreatedAtTimeEnd(true)
                .createdAtTimeEnd(endDate)
                .shouldFilterStallIds(!settlementRequest.getStallIds().isEmpty())
                .stallIds(settlementRequest.getStallIds())
                .build();
        final var orderList = orderServiceRpcClient.getOrdersByCondition(queryOrderDto);
        List<Integer> orderIds = orderList.stream()
                .filter(o -> !o.getStatus().equals(OrderStatusEnum.ORDER_CANCELED))
                .map(SingleClientCustomerOrderRpcResponse::getId).distinct()
                .collect(Collectors.toList());
        final var orderDetailList = orderServiceRpcClient.getOrderDetailsByOrderIds(orderIds);
        List<Integer> skuIds = orderDetailList.stream()
                .filter(d -> !d.getServeStatus().equals(ServeStatusEnum.SERVE_REJECT))
                .map(SingleClientCustomerOrderDetailRpcResponse::getSkuId)
                .distinct()
                .collect(Collectors.toList());
        final var skus = skuServiceRpcClient.getDishSkusByIds(skuIds).getResponseList();

        List<Integer> menuIds = orderList.stream().map(
                        SingleClientCustomerOrderRpcResponse::getMenuId).distinct()
                .collect(Collectors.toList());
        final var menuList = menuServiceRpcClient
                .getMenusByIds(enterpriseId, menuIds)
                .getResponsesList();
        final var menuDetailList = menuServiceRpcClient.queryMenuDetailsByMenuIds(
                enterpriseId, menuIds).getResponsesList();

        Map<String, Map<String, List<SettlementMenuSkuDto>>> classifyOrders = new HashMap<>();

        //找出所有需要的日期
        final var dates = orderList.stream()
                .sorted(Comparator.comparingLong(SingleClientCustomerOrderRpcResponse::getCreatedAt))
                .map(o -> DateUtil.formatDateOfUtc(o.getCreatedAt(), DateUtil.YYYY_MM_DD_UTC))
                .distinct()
                .collect(Collectors.toList());
        for (String date : dates) {

            if (!classifyOrders.containsKey(date)) {
                classifyOrders.put(date, new HashMap<>());
            }
            final var menusMap = classifyOrders.get(date);

            // 找出当天所有的订单
            final var curOrderList = orderList.stream()
                    .filter(o -> Objects.equals(DateUtil.formatDateOfUtc(o.getCreatedAt(), DateUtil.YYYY_MM_DD_UTC), date))
                    .collect(Collectors.toList());
            final var curOrderIds = curOrderList.stream()
                    .map(SingleClientCustomerOrderRpcResponse::getId)
                    .collect(Collectors.toList());

            // 找出当天已点的所有餐单
            final var curMenuIds = curOrderList.stream()
                    .map(SingleClientCustomerOrderRpcResponse::getMenuId)
                    .distinct()
                    .collect(Collectors.toList());
            for (Integer curMenuId : curMenuIds) {
                final var curMenu = menuList.stream().filter(m -> m.getId() == curMenuId).findFirst();
                if (curMenu.isEmpty()) {
                    continue;
                }
                final var curMenuName = curMenu.get().getName();
                if (!menusMap.containsKey(curMenuName)) {
                    menusMap.put(curMenuName, new ArrayList<>());
                }
                final var menuSkuList = new ArrayList<SettlementMenuSkuDto>();
                // 找出当天餐单所有的菜品
                final var curMenuDetails = menuDetailList.stream()
                        .filter(md -> md.getMenuId() == curMenuId)
                        .collect(Collectors.toList());

                //找出当天所有已点的菜品明细
                final var curOrderDetails = orderDetailList.stream()
                        .filter(d -> curOrderIds.contains(d.getOrderId()) && !d.getServeStatus().equals(ServeStatusEnum.SERVE_REJECT))
                        .collect(Collectors.toList());

                for (SingleMenuDetailRpcResponse m : curMenuDetails) {
                    // 找出该餐单下该sku所点菜品的明细
                    final var curSkuOrderDetails = curOrderDetails.stream()
                            .filter(rd -> rd.getMenuDetailId() == m.getId())
                            .collect(Collectors.toList());
                    final var countList = curSkuOrderDetails.stream()
                            .map(SingleClientCustomerOrderDetailRpcResponse::getCount)
                            .collect(Collectors.toList());
                    int countTotal = countList.stream().mapToInt(Integer::intValue).sum();
                    if (countTotal <= 0) {
                        continue;
                    }
                    // 找出该sku的code
                    final var curSku = skus.stream().filter(s -> s.getId() == m.getSkuId()).findFirst();
                    String skuCode = "";
                    if (curSku.isPresent()) {
                        skuCode = curSku.get().getCode();
                        final var existSku = menuSkuList.stream().filter(s -> s.getSkuCode().equals(curSku.get().getCode())).findFirst();
                        if (existSku.isPresent()) {
                            final var originalSku = existSku.get();
                            final var count = existSku.get().getCount();
                            final var newCount = count + countTotal;
                            originalSku.setCount(newCount);
                            originalSku.setTotalPrice(m.getPrice() * newCount);
                            continue;
                        }
                    }

                    final var settlementMenuSku = SettlementMenuSkuDto.builder()
                            .skuCode(skuCode)
                            .count(countTotal)
                            .showName(m.getShowName())
                            .price(m.getPrice())
                            .totalPrice(m.getPrice() * countTotal)
                            .build();
                    menuSkuList.add(settlementMenuSku);

                }
                menusMap.get(curMenuName).addAll(menuSkuList);

            }
        }
        final var stalls = clientInventoryServiceRpcClient.getStallsByIds(enterpriseId, settlementRequest.getStallIds()).getClientsList();
        final var stallNames = stalls.stream()
                .filter(s -> settlementRequest.getStallIds().contains(s.getId()))
                .map(SingleClientRpcResponse::getName).collect(Collectors.toList());
        String joinedString = String.join(" ", stallNames);
        final var settlementDataDto = SettlementDataDto.builder()
                .classifyOrders(classifyOrders)
                .client(joinedString)
                .startTime(DateUtil.formatDate(settlementRequest.getTimeStart().plusHours(8), DateUtil.YYYY_MM_DD))
                .endTime(DateUtil.formatDate(settlementRequest.getTimeEnd().plusHours(8), DateUtil.YYYY_MM_DD))
                .build();

        try {
            return writeToExcelBySku(settlementDataDto);
        } catch (IOException e) {
            throw BusinessException.build(ErrorCodeEnum.GENERATE_EXCEL_ERROR);
        }

    }

    public ByteArrayOutputStream generateOrderSettlementExcelByMeal(SettlementRequestDto settlementRequest) {
        final var startDate = settlementRequest.getTimeStart().plusHours(8).toLocalDate().atStartOfDay();
        final var endDate = settlementRequest.getTimeEnd().plusHours(8).toLocalDate().plusDays(1).atStartOfDay();
        final var operatorLoginInfo = LoginContextHolder.getLoginInfo();
        final int enterpriseId = LoginContextHolder.hasLogin() ? operatorLoginInfo.getEnterpriseId() : settlementRequest.getEnterpriseId();
        final var queryOrderDto = QueryOrderByConditionDto.builder()
                .enterpriseId(enterpriseId)
                .shouldFilterCreatedAtTimeStart(true)
                .createdAtTimeStart(startDate)
                .shouldFilterCreatedAtTimeEnd(true)
                .createdAtTimeEnd(endDate)
                .shouldFilterStallIds(!settlementRequest.getStallIds().isEmpty())
                .stallIds(settlementRequest.getStallIds())
                .build();
        final var orderList = orderServiceRpcClient.getOrdersByCondition(queryOrderDto);
        List<Integer> orderIds = orderList.stream()
                .filter(o -> !o.getStatus().equals(OrderStatusEnum.ORDER_CANCELED))
                .map(SingleClientCustomerOrderRpcResponse::getId).distinct()
                .collect(Collectors.toList());
        final var orderDetailList = orderServiceRpcClient.getOrderDetailsByOrderIds(orderIds);
        List<Integer> menuIds = orderList.stream().map(
                        SingleClientCustomerOrderRpcResponse::getMenuId).distinct()
                .collect(Collectors.toList());
        final var menuList = menuServiceRpcClient
                .getMenusByIds(enterpriseId, menuIds)
                .getResponsesList();
        //获取到所有的餐别
        final var allMeals = mealServiceRpcClient.getAllMeals(enterpriseId).getResponsesList();

        Map<String, Map<String, Map<String, List<SettlementMenuMealDto>>>> classifyOrders = new HashMap<>();
        for (SingleMenuRpcResponse menu : menuList) {
            final var menuName = menu.getName();
            if (!classifyOrders.containsKey(menuName)) {
                classifyOrders.put(menuName, new HashMap<>());
            }
            final var menusMap = classifyOrders.get(menuName);
            //这个餐单下所有已点的orders
            final var curMenuOrders = orderList.stream()
                    .filter(o -> o.getMenuId() == menu.getId())
                    .collect(Collectors.toList());
            final var curMenuOrderIds = curMenuOrders.stream()
                    .map(SingleClientCustomerOrderRpcResponse::getId)
                    .collect(Collectors.toList());
            //这个餐单下所有已点的order details
            final var curMenuOrderDetails = orderDetailList.stream()
                    .filter(od -> curMenuOrderIds.contains(od.getOrderId()))
                    .collect(Collectors.toList());
            //这个餐单下所有已点的餐别
            final var curMenuMealIds = curMenuOrderDetails.stream()
                    .map(SingleClientCustomerOrderDetailRpcResponse::getMealId)
                    .distinct()
                    .collect(Collectors.toList());
            // 汇总每个餐别的情况
            for (Integer curMealId : curMenuMealIds) {
                String mealName = "无餐别";
                if (curMealId != 0) {
                    final var curMeal = allMeals.stream().filter(m -> m.getId() == curMealId).findFirst();
                    if (curMeal.isEmpty()) {
                        continue;
                    } else {
                        mealName = curMeal.get().getName();
                    }
                }
                if (!menusMap.containsKey(mealName)) {
                    menusMap.put(mealName, new HashMap<>());
                }
                final var mealsMap = menusMap.get(mealName);
                //当前餐别已点的order details
                final var curMealOrderDetails = curMenuOrderDetails.stream()
                        .filter(od -> od.getMealId() == curMealId)
                        .collect(Collectors.toList());
                //找出所有需要的日期
                final var dates = curMealOrderDetails.stream()
                        .sorted(Comparator.comparingLong(SingleClientCustomerOrderDetailRpcResponse::getCreatedAt))
                        .map(o -> DateUtil.formatDateOfUtc(o.getCreatedAt(), DateUtil.YYYY_MM_DD_UTC))
                        .distinct()
                        .collect(Collectors.toList());
                for (String date : dates) {

                    if (!mealsMap.containsKey(date)) {
                        mealsMap.put(date, new ArrayList<>());
                    }
                    final var curMealDateSkuList = mealsMap.get(date);
                    //找出当天所有的order details
                    final var curMealDateOrderDetails = curMealOrderDetails.stream()
                            .filter(o -> Objects.equals(DateUtil.formatDateOfUtc(o.getCreatedAt(), DateUtil.YYYY_MM_DD_UTC), date))
                            .collect(Collectors.toList());
                    for (SingleClientCustomerOrderDetailRpcResponse detail : curMealDateOrderDetails) {
                        if (detail.getServeStatus().equals(ServeStatusEnum.SERVE_REJECT)) {
                            continue;
                        }
                        final var settlementOrderDetail = SettlementMenuMealDto.builder()
                                .skuName(detail.getShowName())
                                .count(detail.getCount())
                                .price(detail.getPrice())
                                .build();
                        curMealDateSkuList.add(settlementOrderDetail);
                    }
                }
            }

        }
        final var client = clientInventoryServiceRpcClient.getClientById(enterpriseId,
                settlementRequest.getClientId()).getClient();
        final var settlementDataByMealDto = SettlementDataByMealDto.builder()
                .classifyOrders(classifyOrders)
                .client(client.getName())
                .startTime(settlementRequest.getTimeStart().plusHours(8))
                .endTime(settlementRequest.getTimeEnd().plusHours(8))
                .build();
        try {
            return writeToExcelByMeal(settlementDataByMealDto);
        } catch (IOException e) {
            throw BusinessException.build(ErrorCodeEnum.GENERATE_EXCEL_ERROR);
        }

    }

    @Override
    public ByteArrayOutputStream exportSeparateMealsExcel(QueryOrderDto queryOrderDto) {
        // 参数校验
        if (queryOrderDto == null) {
            throw BusinessException.build(ErrorCodeEnum.GENERATE_EXCEL_ERROR);
        }

        final int enterpriseId = queryOrderDto.getEnterpriseId();

        // 查询订单列表
        final var queryClientCustomerOrdersByConditionRpcResponse = orderServiceRpcClient.queryClientCustomerOrdersByCondition(queryOrderDto);


        final var orderList = queryClientCustomerOrdersByConditionRpcResponse.getResponsesList();
        if (orderList.isEmpty()) {
            log.warn("未查询到订单数据，enterpriseId: {}", enterpriseId);
            return createEmptyExcelBySeparateMeal();
        }

        // 获取订单IDs
        List<Integer> orderIds = orderList.stream()
                .map(SingleClientCustomerOrderRpcResponse::getId)
                .collect(Collectors.toList());

        // 构建订单映射
        final var orderMap = orderList.stream()
                .filter(Objects::nonNull)
                .filter(o -> o.getId() != 0)
                .collect(Collectors.toMap(SingleClientCustomerOrderRpcResponse::getId, o -> o, (o1, o2) -> o1));

        // 查询订单详情
        final var orderDetailList = orderServiceRpcClient.getOrderDetailsByOrderIds(orderIds);
        if (orderDetailList == null || orderDetailList.isEmpty()) {
            log.warn("未查询到订单详情数据，orderIds: {}", orderIds);
            return createEmptyExcelBySeparateMeal();
        }

        // 订单详情按订单ID分组
        final var orderDetailGroupByOrderId = orderDetailList.stream()
                .filter(Objects::nonNull)
                .filter(d -> !d.getServeStatus().equals(ServeStatusEnum.SERVE_REJECT))
                .collect(Collectors.groupingBy(SingleClientCustomerOrderDetailRpcResponse::getOrderId));

        // 获取SKU数据
        List<Integer> skuIds = orderDetailList.stream()
                .filter(Objects::nonNull)
                .filter(d -> !d.getServeStatus().equals(ServeStatusEnum.SERVE_REJECT))
                .map(SingleClientCustomerOrderDetailRpcResponse::getSkuId)
                .distinct()
                .collect(Collectors.toList());

        final var skuResponse = skuServiceRpcClient.getDishSkusByIds(skuIds);
        final List<SingleDishSkuByIdsRpcResponse> skus = skuResponse != null ? skuResponse.getResponseList() : new ArrayList<>();
        final var skuMap = skus.stream()
                .collect(Collectors.toMap(SingleDishSkuByIdsRpcResponse::getId, s -> s, (s1, s2) -> s1));

        // 获取餐别数据
        final var mealResponse = mealServiceRpcClient.getAllMeals(enterpriseId);
        final List<SingleMealRpcResponse> meals = mealResponse != null ? mealResponse.getResponsesList() : new ArrayList<>();
        final var mealMap = meals.stream()
                .filter(Objects::nonNull)
                .filter(m -> m.getId() != 0)
                .collect(Collectors.toMap(SingleMealRpcResponse::getId, m -> m, (m1, m2) -> m1));

        // 获取菜单数据
        List<Integer> menuIds = orderList.stream()
                .map(SingleClientCustomerOrderRpcResponse::getMenuId)
                .distinct()
                .collect(Collectors.toList());

        final var menuResponse = menuServiceRpcClient.getMenusByIds(enterpriseId, menuIds);
        final List<SingleMenuRpcResponse> menuList = menuResponse != null ? menuResponse.getResponsesList() : new ArrayList<>();
        final var menuMap = menuList.stream()
                .collect(Collectors.toMap(SingleMenuRpcResponse::getId, m -> m, (m1, m2) -> m1));

        // 获取菜单详情数据
        final var menuDetailResponse = menuServiceRpcClient.queryMenuDetailsByMenuIds(enterpriseId, menuIds);
        final List<SingleMenuDetailRpcResponse> menuDetailList = menuDetailResponse != null ? menuDetailResponse.getResponsesList() : new ArrayList<>();
        final var menuDetailMap = menuDetailList.stream()
                .collect(Collectors.toMap(SingleMenuDetailRpcResponse::getId, m -> m, (m1, m2) -> m1));

        // 获取客户数据
        List<Integer> customerIds = orderList.stream()
                .map(SingleClientCustomerOrderRpcResponse::getCustomerId)
                .filter(id -> id != 0)
                .distinct()
                .collect(Collectors.toList());

        final var customerResponse = clientCustomerServiceRpcClient.getClientCustomersByIds(enterpriseId, customerIds);
        final List<SingleClientCustomerRpcResponse> customers = customerResponse != null ? customerResponse.getResponsesList() : new ArrayList<>();
        final var customerMap = customers.stream()
                .collect(Collectors.toMap(SingleClientCustomerRpcResponse::getId, c -> c, (c1, c2) -> c1));

        // 构建分餐表明细
        final var mealDetails = new ArrayList<SeparateMealDetail>();
        for (Integer orderId : orderIds) {
            final var orderDetails = orderDetailGroupByOrderId.getOrDefault(orderId, Collections.emptyList());
            final var order = orderMap.get(orderId);

            // 订单为空则跳过
            if (order == null) {
                log.warn("订单不存在，orderId: {}", orderId);
                continue;
            }

            // 获取菜单信息
            final var menu = menuMap.get(order.getMenuId());
            final String menuName = menu != null ? menu.getName() : "";

            // 获取客户信息
            final var customer = customerMap.get(order.getCustomerId());
            final String customerName = customer != null ? customer.getName() : "";

            // 安全获取房间号
            String roomNo = getRoomNoSafely(order);

            // 处理每个订单详情
            for (SingleClientCustomerOrderDetailRpcResponse orderDetail : orderDetails) {
                if (orderDetail == null) {
                    continue;
                }

                // 安全获取餐别名称
                String mealName = getMealNameSafely(orderDetail.getMenuDetailId(), menuDetailMap, mealMap);

                // 安全获取SKU名称
                String skuName = getSkuNameSafely(orderDetail.getSkuId(), skuMap);

                // 安全获取忌口信息（列表转字符串）
                List<String> avoidsList = getAvoidsSafely(order);
                String avoidsStr = String.join(", ", avoidsList);

                // 安全获取医生信息（列表转字符串）
                List<String> doctorsList = getDoctorsSafely(order);
                String doctorsStr = String.join(", ", doctorsList);

                String remark = order.getRemark();
                String avoidRemark = order.getAvoidRemark();
                String combinedRemark = "";
                if (!remark.isEmpty() && !avoidRemark.isEmpty()) {
                    combinedRemark = remark + "；" + avoidRemark;
                } else if (!remark.isEmpty()) {
                    combinedRemark = remark;
                } else if (!avoidRemark.isEmpty()) {
                    combinedRemark = avoidRemark;
                }

                // 构建分餐表明细
                SeparateMealDetail mealDetail = SeparateMealDetail.builder()
                        .orderId(orderId)
                        .mealName(mealName)
                        .roomNo(roomNo)
                        .customerName(customerName)
                        .menuName(menuName)
                        .skuName(skuName)
                        .count(orderDetail.getCount() != 0 ? orderDetail.getCount() : 0)
                        .avoids(avoidsStr)
                        .doctors(doctorsStr)
                        .remark(combinedRemark)
                        .build();
                mealDetails.add(mealDetail);
            }
        }

        // 构建分餐表数据
        final var separateMealDataDto = SeparateMealDataDto.builder()
                .mealDetails(mealDetails)
                .build();

        // 生成Excel
        try {
            return writeToExcelBySeparateMeal(separateMealDataDto);
        } catch (IOException e) {
            log.error("生成分餐表Excel失败", e);
            throw BusinessException.build(ErrorCodeEnum.GENERATE_EXCEL_ERROR);
        }
    }

}