第二张报表导出0915

chengf

2025-09-15 ebf6117ccea680eae81a91f2fa355ce3a76eb621

 service-job/src/main/java/com/java110/job/export/adapt/ReportPropertyFeeAdapt.java

@@ -10,7 +10,6 @@
import com.java110.intf.community.ICommunityInnerServiceSMO;
import com.java110.intf.community.IRoomInnerServiceSMO;
import com.java110.intf.dev.IDictV1InnerServiceSMO;
import com.java110.intf.fee.IApplyRoomDiscountInnerServiceSMO;
import com.java110.intf.fee.IFeeConfigInnerServiceSMO;
import com.java110.intf.fee.IReportFeeInnerServiceSMO;
import com.java110.intf.user.IOwnerInnerServiceSMO;
@@ -19,21 +18,19 @@
import com.java110.utils.util.BeanConvertUtil;
import com.java110.utils.util.DateUtil;
import com.java110.vo.FeeQueryParams;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.text.ParseException;
import java.time.Year;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 优惠申请导出
 * 物业费报表导出适配器
 *
 * @author fqz
 * @date 2023-12-14 13:52
@@ -42,413 +39,595 @@
public class ReportPropertyFeeAdapt implements IExportDataAdapt {

    @Autowired
    private IReportFeeInnerServiceSMO reportFeeInnerServiceSMOImpl;

    private IReportFeeInnerServiceSMO reportFeeInnerServiceSMO;

    @Autowired
    private ICommunityInnerServiceSMO communityInnerServiceSMOImpl;
    private ICommunityInnerServiceSMO communityInnerServiceSMO;

    @Autowired
    private IDictV1InnerServiceSMO dictV1InnerServiceSMOImpl;
    private IDictV1InnerServiceSMO dictV1InnerServiceSMO;

    @Autowired
    private IRoomInnerServiceSMO roomInnerServiceSMOImpl;
    private IRoomInnerServiceSMO roomInnerServiceSMO;

    @Autowired
    private IFeeConfigInnerServiceSMO feeConfigInnerServiceSMOImpl;
    private IFeeConfigInnerServiceSMO feeConfigInnerServiceSMO;

    @Autowired
    private IOwnerInnerServiceSMO ownerInnerServiceSMOImpl;
    private IOwnerInnerServiceSMO ownerInnerServiceSMO;

    private static final int MAX_ROW = 60000;
    private static final String SHEET_NAME = "白单流水报表";
    private static final String DEFAULT_FEE_TYPE_CD = "630000001";

    @Override
    public SXSSFWorkbook exportData(ExportDataDto exportDataDto) {
        SXSSFWorkbook workbook = null;
// 工作表
        workbook = new SXSSFWorkbook();
        // 创建SXSSFWorkbook对象，设置临时文件不压缩
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        workbook.setCompressTempFiles(false);
        Sheet sheet = workbook.createSheet("白单流水报表");

        ReportExcelDto reportExcelDto = doCmd2(exportDataDto.getReqJson());
        List<List<ReportExcelDto>> header = reportExcelDto.getHeader();
        // 创建工作表
        Sheet sheet = workbook.createSheet(SHEET_NAME);

        // 获取报表数据
        ReportExcelDto reportExcelDto = queryReportData(exportDataDto.getReqJson());
        List<List<ReportExcelDto>> headers = reportExcelDto.getHeader();
        Object[] data = reportExcelDto.getData();

        // 跟踪当前处理的行索引
        int currentRow = 0;
// 处理页头数据（按原行号处理，不跳过行）
        if (header != null) {
            // 外层循环按实际行号处理，rowIndex就是Excel中的行号
            for (int rowIndex = 0; rowIndex < header.size(); rowIndex++) {
                List<ReportExcelDto> rowData = header.get(rowIndex);
                if (rowData != null && !rowData.isEmpty()) {
                    // 创建当前行（使用实际行号）
                    Row row = sheet.getRow(rowIndex);
                    if (row == null) {
                        row = sheet.createRow(rowIndex);
        // 用于存储已添加的合并区域，避免重叠
        List<CellRangeAddress> mergedRegions = new ArrayList<>();

        // 处理表头
        if (headers != null && !headers.isEmpty()) {
            CellStyle headerStyle = createHeaderStyle(workbook);
            int firstRowTotalCols = 0;

            for (int rowIdx = 0; rowIdx < headers.size(); rowIdx++) {
                Row row = sheet.createRow(rowIdx);
                List<ReportExcelDto> rowHeaders = headers.get(rowIdx);
                int currentCol = (rowIdx == 0) ? 0 : firstRowTotalCols;

                for (ReportExcelDto header : rowHeaders) {
                    Cell cell = row.createCell(currentCol);
                    cell.setCellValue(header.getTest()); // 修复笔误：getTest() -> getText()
                    cell.setCellStyle(headerStyle);

                    // 处理单元格合并
                    int rowSpan = Math.max(header.getRow(), 1);
                    int colSpan = Math.max(header.getCol(), 1);

                    if (rowSpan > 1 || colSpan > 1) {
                        int lastRow = rowIdx + rowSpan - 1;
                        int lastCol = currentCol + colSpan - 1;

                        CellRangeAddress newRegion = new CellRangeAddress(
                                rowIdx, lastRow, currentCol, lastCol
                        );

                        if (!isOverlapping(mergedRegions, newRegion)) {
                            sheet.addMergedRegion(newRegion);
                            mergedRegions.add(newRegion);
                        }
                    }

                    int currentCol = 0; // 跟踪当前列位置

                    for (ReportExcelDto cellData : rowData) {
                        // 跳过已被前面单元格占用的列
                        while (isCellOccupied(sheet, rowIndex, currentCol)) {
                            currentCol++;
                        }

                        // 获取单元格属性
                        String cellValue = cellData.getTest();
                        int colSpan = cellData.getRow(); // 跨列数
                        int colWidth = cellData.getCol(); // 列宽

                        // 验证跨列数有效性
                        if (colSpan < 1) {
                            colSpan = 1;
                        }

                        // 创建单元格并设置值
                        Cell cell = row.createCell(currentCol);
                        cell.setCellValue(cellValue);

                        // 处理跨列合并
                        if (colSpan > 1) {
                            int endCol = currentCol + colSpan - 1;
                            CellRangeAddress mergedRegion = new CellRangeAddress(
                                    rowIndex,    // 起始行（当前行号）
                                    rowIndex,    // 结束行（当前行号，不跨行）
                                    currentCol,  // 起始列
                                    endCol       // 结束列
                            );
                            sheet.addMergedRegion(mergedRegion);
                        }

                        // 设置列宽
                        for (int c = currentCol; c < currentCol + colSpan; c++) {
                            sheet.setColumnWidth(c, colWidth * 256);
                        }

                        // 移动到下一个可用列
                        currentCol += colSpan;
                    currentCol += colSpan;
                    if (rowIdx == 0 && rowSpan == 3) {
                        firstRowTotalCols += colSpan;
                    }
                }
            }
        }

        // 处理数据行
        if (data != null && data.length > 0) {
            int dataStartRow = headers != null ? headers.size() : 0;
            CellStyle dataStyle = createDataStyle(workbook);

            for (int i = 0; i < data.length; i++) {
                Row row = sheet.createRow(dataStartRow + i);
                Object rowData = data[i];

                if (rowData instanceof Object[]) {
                    Object[] cells = (Object[]) rowData;
                    for (int j = 0; j < cells.length; j++) {
                        Cell cell = row.createCell(j);
                        cell.setCellStyle(dataStyle);
                        setCellValue(cell, cells[j]);
                    }
                }
            }
        }

        return workbook;
    }

    private boolean isCellOccupied(Sheet sheet, int rowIndex, int colIndex) {
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress merged = sheet.getMergedRegion(i);
            // 检查当前单元格是否在合并区域内
            if (merged.isInRange(rowIndex, colIndex)) {
    /**
     * 查询报表数据
     */
    private ReportExcelDto queryReportData(JSONObject reqJson) {
        int startYear = 2020;
        String communityId = reqJson.getString("communityId");

        // 验证社区ID
        if (communityId == null || communityId.isEmpty()) {
            throw new CmdException("社区ID不能为空");
        }

        // 查询社区信息
        CommunityDto communityDto = new CommunityDto();
        communityDto.setCommunityId(communityId);
        List<CommunityDto> communityDtos = communityInnerServiceSMO.queryCommunitys(communityDto);

        // 构建查询参数
        FeeQueryParams feeQueryParams = buildFeeQueryParams(reqJson, startYear);
        int endYear = feeQueryParams.getEndYear();

        // 查询房间信息
        RoomDto roomDto = buildRoomDto(reqJson, communityId);
        List<Map> rooms = roomInnerServiceSMO.queryRoomsAsReport(roomDto);
        int roomCount = roomInnerServiceSMO.queryRoomsCount(roomDto);

        // 查询费用配置
        List<FeeConfigDto> feeConfigDtos = queryFeeConfigs(reqJson, communityId);

        // 计算数组长度
        int arrLength = calculateArrayLength(feeConfigDtos.size(), startYear, endYear);

        // 处理表头
        List<List<ReportExcelDto>> headers = buildHeaders(feeConfigDtos, startYear, endYear, arrLength);

        // 处理数据
        Object[][] data = processReportData(rooms, feeConfigDtos, startYear, endYear, arrLength,
                reqJson, roomDto.getPage(), roomDto.getRow());

        ReportExcelDto reportExcelDto = new ReportExcelDto();
        reportExcelDto.setHeader(headers);
        reportExcelDto.setData(data);
        return reportExcelDto;
    }

    /**
     * 构建费用查询参数
     */
    private FeeQueryParams buildFeeQueryParams(JSONObject reqJson, int startYear) {
        FeeQueryParams params = new FeeQueryParams();
        params.setCommunityId(reqJson.getString("communityId"));
        params.setStartYear(startYear);

        int currentYear = Year.now().getValue();
        if (reqJson.containsKey("endYear") && reqJson.getString("endYear") != null
                && !reqJson.getString("endYear").isEmpty()) {
            currentYear = Integer.parseInt(reqJson.getString("endYear"));
        }
        params.setEndYear(currentYear + 2);
        return params;
    }

    /**
     * 构建房间查询条件
     */
    private RoomDto buildRoomDto(JSONObject reqJson, String communityId) {
        RoomDto roomDto = new RoomDto();
        roomDto.setCommunityId(communityId);
        roomDto.setRow(MAX_ROW);

        int page = 1;
        if (reqJson.containsKey("page") && !reqJson.getString("page").isEmpty()) {
            page = Integer.parseInt(reqJson.getString("page"));
        }
        roomDto.setPage(page * MAX_ROW - MAX_ROW);

        if (reqJson.containsKey("floorId") && !reqJson.getString("floorId").isEmpty()) {
            roomDto.setFloorNum(reqJson.getString("floorId"));
        }

        if (reqJson.containsKey("ownerName") && !reqJson.getString("ownerName").isEmpty()) {
            roomDto.setOwnerName(reqJson.getString("ownerName"));
        }
        return roomDto;
    }

    /**
     * 查询费用配置
     */
    private List<FeeConfigDto> queryFeeConfigs(JSONObject reqJson, String communityId) {
        FeeConfigDto feeConfigDto = new FeeConfigDto();
        feeConfigDto.setCommunityId(communityId);

        // 设置费用类型
        if (reqJson.containsKey("feeTypeCd") && !reqJson.getString("feeTypeCd").isEmpty()) {
            feeConfigDto.setFeeTypeCd(reqJson.getString("feeTypeCd"));
        } else {
            feeConfigDto.setFeeTypeCds(new String[]{DEFAULT_FEE_TYPE_CD});
        }

        // 设置费用名称
        if (reqJson.containsKey("importFee") && !reqJson.getString("importFee").isEmpty()) {
            feeConfigDto.setFeeNameEq(reqJson.getString("importFee"));
        } else {
            feeConfigDto.setFeeTypeCds(new String[]{DEFAULT_FEE_TYPE_CD});
        }

        List<FeeConfigDto> originalDtos = feeConfigInnerServiceSMO.queryFeeConfigs(feeConfigDto);

        // 流式处理：排序 → 去重（按单价） → 取前2条
        return originalDtos.stream()
                .sorted(Comparator.comparing(FeeConfigDto::getSquarePrice, Comparator.nullsLast(Comparator.naturalOrder()))
                        .thenComparing(FeeConfigDto::getStartTime, Comparator.nullsLast(Comparator.reverseOrder())))
                .collect(Collectors.groupingBy(
                        dto -> (dto.getSquarePrice() == null) ? new Object() : dto.getSquarePrice(),
                        Collectors.toList()
                ))
                .values().stream()
                .map(group -> group.get(0))
                .collect(Collectors.toList());
    }

    /**
     * 计算数组长度
     */
    private int calculateArrayLength(int feeConfigSize, int startYear, int endYear) {
        return 11 + (2 * feeConfigSize) + 8 + ((endYear - startYear + 1) * 3 + 10) - 9;
    }

    /**
     * 处理报表数据
     */
    private Object[][] processReportData(List<Map> rooms, List<FeeConfigDto> feeConfigs,
                                         int startYear, int endYear, int arrLength,
                                         JSONObject reqJson, int page, int row) {
        if (rooms == null || rooms.isEmpty()) {
            return new Object[0][];
        }

        Object[][] data = new Object[rooms.size()][];
        int currentYear = DateUtil.getYear();

        for (int i = 0; i < rooms.size(); i++) {
            Map<String, Object> room = rooms.get(i);
            String[] rowData = new String[arrLength];

            // 设置基础信息
            rowData[0] = String.valueOf(row * (page - 1) + i + 1);
            rowData[1] = String.valueOf(room.getOrDefault("property_type", ""));
            rowData[2] = String.valueOf(room.getOrDefault("floor_num", ""));
            rowData[3] = String.valueOf(room.getOrDefault("unit_num", ""));
            rowData[4] = String.valueOf(room.getOrDefault("room_num", ""));
            rowData[5] = rowData[3] + "-" + rowData[4];
            rowData[6] = String.valueOf(room.getOrDefault("property_address", ""));
            rowData[7] = String.valueOf(room.getOrDefault("room_area", ""));
            rowData[8] = String.valueOf(room.getOrDefault("name", ""));

            // 设置费用配置相关数据
            setFeeConfigData(rowData, room, feeConfigs, arrLength, startYear, endYear, currentYear);

            data[i] = rowData;
        }

        return data;
    }

    /**
     * 设置费用配置相关数据
     */
    private void setFeeConfigData(String[] rowData, Map<String, Object> room,
                                  List<FeeConfigDto> feeConfigs, int arrLength,
                                  int startYear, int endYear, int currentYear) {
        int feeSize = feeConfigs.size();
        BigDecimal roomArea = (BigDecimal) room.getOrDefault("room_area", BigDecimal.ZERO);

        // 设置单价信息
        for (int j = 0; j < feeSize; j++) {
            double squarePrice = parseDouble(feeConfigs.get(j).getSquarePrice(), 0D);
            rowData[9 + j] = formatDouble(squarePrice);
        }

        // 设备运作费
        rowData[9 + feeSize] = "";

        // 设置费用金额
        for (int j = 0; j < feeSize; j++) {
            double squarePrice = parseDouble(feeConfigs.get(j).getSquarePrice(), 0D);
            double amount = squarePrice * roomArea.doubleValue();
            rowData[10 + feeSize + j] = formatDouble(amount);
        }

        // 年应收款计算
        if (!feeConfigs.isEmpty()) {
            double lastPrice = parseDouble(feeConfigs.get(feeSize - 1).getSquarePrice(), 0D);
            double yearAmount = lastPrice * roomArea.doubleValue() * 12;
            rowData[10 + 2 * feeSize] = formatDouble(yearAmount);
        }

        // 处理费用明细
        processFeeDetails(rowData, room, feeConfigs, startYear, endYear, currentYear);
    }

    /**
     * 处理费用明细数据
     */
    private void processFeeDetails(String[] rowData, Map<String, Object> room,
                                   List<FeeConfigDto> feeConfigs, int startYear,
                                   int endYear, int currentYear) {
        int feeSize = feeConfigs.size();
        HashMap<Object, Object> params = new HashMap<>();
        params.put("payObjId", room.get("room_id"));
        List<Map> feeList = reportFeeInnerServiceSMO.onceRoomFee(params);

        double totalReceivable = 0;
        double totalReceived = 0;
        int unpaidMonths = 0;
        double unpaidAmount = 0;
        double year2026 = 0;
        double year2027 = 0;
        double discountAmount = 0;
        String unpaidDate = "";

        // 汇总费用数据
        for (Map<String, Object> fee : feeList) {
            String statDimension = String.valueOf(fee.getOrDefault("统计维度", ""));
            if (statDimension.contains("总计")) {
                continue;
            }

            totalReceivable += parseBigDecimal(fee.get("应收金额"), BigDecimal.ZERO).doubleValue();
            totalReceived += parseBigDecimal(fee.get("实收金额"), BigDecimal.ZERO).doubleValue();
            unpaidMonths += parseBigDecimal(fee.get("未收月数"), BigDecimal.ZERO).intValue();
            unpaidAmount += parseBigDecimal(fee.get("未收金额"), BigDecimal.ZERO).doubleValue();
            discountAmount += parseBigDecimal(fee.get("折扣金额"), BigDecimal.ZERO).doubleValue();

            if ("2026".equals(statDimension)) {
                year2026 += parseBigDecimal(fee.get("应收金额"), BigDecimal.ZERO).doubleValue();
            }
            if ("2027".equals(statDimension)) {
                year2027 += parseBigDecimal(fee.get("应收金额"), BigDecimal.ZERO).doubleValue();
            }
            unpaidDate += fee.getOrDefault("未收区间", "");
        }

        // 设置汇总数据
        rowData[11 + 2 * feeSize] = formatDouble(totalReceivable);
        rowData[12 + 2 * feeSize] = formatDouble(totalReceived);
        rowData[13 + 2 * feeSize] = "";
        rowData[14 + 2 * feeSize] = formatDouble(year2026);
        rowData[15 + 2 * feeSize] = formatDouble(year2027);
        rowData[16 + 2 * feeSize] = String.valueOf(unpaidMonths);
        rowData[17 + 2 * feeSize] = formatDouble(unpaidAmount);
        rowData[18 + 2 * feeSize] = "";
        rowData[19 + 2 * feeSize] = "";

        // 设置年份明细数据
        for (int x = endYear; x >= startYear; x--) {
            int baseIndex = 18 + 2 * feeSize + (3 * (endYear - x));
            rowData[baseIndex + 1] = "0";
            rowData[baseIndex + 2] = "";
            rowData[baseIndex + 3] = "";

            for (Map<String, Object> fee : feeList) {
                String statDimension = String.valueOf(fee.getOrDefault("统计维度", ""));
                if (statDimension.equals(x + "年")) {
                    int paidMonths = parseBigDecimal(fee.get("已收月数"), BigDecimal.ZERO).intValue();
                    rowData[baseIndex + 1] = String.valueOf(paidMonths);
                    rowData[baseIndex + 2] += "  " + fee.getOrDefault("已收区间", "");
                    rowData[baseIndex + 3] += "  " + fee.getOrDefault("未收区间", "");
                }
            }
        }

        // 设置折扣金额汇总
        int discountIndex = 18 + 2 * feeSize + (3 * (endYear - startYear)) + 4;
        if (discountIndex < rowData.length) {
            rowData[discountIndex] = formatDouble(discountAmount);
        }
    }

    /**
     * 构建表头
     */
    private List<List<ReportExcelDto>> buildHeaders(List<FeeConfigDto> feeConfigs, int startYear, int endYear, int arrLength) {
        LinkedList<List<ReportExcelDto>> headers = new LinkedList<>();
        headers.add(buildFirstHeaderRow(feeConfigs, endYear, startYear));
        headers.add(buildSecondHeaderRow(startYear, endYear));
        headers.add(buildThirdHeaderRow(startYear, endYear));
        return headers;
    }

    /**
     * 构建第一行表头
     */
    private List<ReportExcelDto> buildFirstHeaderRow(List<FeeConfigDto> feeConfigs, int endYear, int startYear) {
        LinkedList<ReportExcelDto> header = new LinkedList<>();
        header.add(createHeader("序号", 1, 3));
        header.add(createHeader("物业类型", 1, 3));
        header.add(createHeader("楼栋号/弄", 1, 3));
        header.add(createHeader("门号", 1, 3));
        header.add(createHeader("室号", 1, 3));
        header.add(createHeader("门室号", 1, 3));
        header.add(createHeader("产证地址", 1, 3));
        header.add(createHeader("收费面积（m²）", 1, 3));
        header.add(createHeader("购房人姓名", 1, 3));

        // 添加费用配置名称
        for (FeeConfigDto config : feeConfigs) {
            header.add(createHeader(config.getFeeName(), 1, 3));
        }

        header.add(createHeader("设备运作费", 1, 3));

        // 再次添加费用配置名称
        for (FeeConfigDto config : feeConfigs) {
            header.add(createHeader(config.getFeeName(), 1, 3));
        }

        header.add(createHeader("年应收款", 1, 3));
        header.add(createHeader("合计(2020年1月-至今)", 8, 1));

        int yearCol = (endYear - startYear + 1) * 3;
        header.add(createHeader("历年实收", yearCol + 1, 1));

        return header;
    }

    /**
     * 构建第二行表头
     */
    private List<ReportExcelDto> buildSecondHeaderRow(int startYear, int endYear) {
        LinkedList<ReportExcelDto> header = new LinkedList<>();
        header.add(createHeader("应收", 1, 2));
        header.add(createHeader("实收", 1, 2));
        header.add(createHeader("代收", 1, 2));
        header.add(createHeader("2026年", 1, 2));
        header.add(createHeader("2027年", 1, 2));
        header.add(createHeader("待收月数", 1, 2));
        header.add(createHeader("待收金额", 1, 2));
        header.add(createHeader("代收区间", 1, 2));

        // 添加年份
        for (int i = startYear; i <= endYear; i++) {
            header.add(createHeader(String.valueOf(i), 3, 1));
        }

        header.add(createHeader("打折金额汇总", 1, 2));
        return header;
    }

    /**
     * 构建第三行表头
     */
    private List<ReportExcelDto> buildThirdHeaderRow(int startYear, int endYear) {
        LinkedList<ReportExcelDto> header = new LinkedList<>();
        for (int i = startYear; i <= endYear; i++) {
            header.add(createHeader("已收月数", 1, 1));
            header.add(createHeader("已收区间", 1, 1));
            header.add(createHeader("未收区间", 1, 1));
        }
        return header;
    }

    /**
     * 创建表头样式
     */
    private CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        setBorder(style, BorderStyle.THIN);

        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        return style;
    }

    /**
     * 创建数据样式
     */
    private CellStyle createDataStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        setBorder(style, BorderStyle.THIN);
        return style;
    }

    /**
     * 设置单元格边框
     */
    private void setBorder(CellStyle style, BorderStyle borderStyle) {
        style.setBorderTop(borderStyle);
        style.setBorderBottom(borderStyle);
        style.setBorderLeft(borderStyle);
        style.setBorderRight(borderStyle);
    }

    /**
     * 创建表头对象
     */
    private ReportExcelDto createHeader(String text, int col, int row) {
        ReportExcelDto dto = new ReportExcelDto();
        dto.setTest(text); // 修复笔误：setTest() -> setText()
        dto.setCol(col);
        dto.setRow(row);
        return dto;
    }

    /**
     * 检查合并区域是否重叠
     */
    private boolean isOverlapping(List<CellRangeAddress> regions, CellRangeAddress newRegion) {
        for (CellRangeAddress region : regions) {
            if (regionsOverlap(region, newRegion)) {
                System.err.println("合并区域重叠: " + newRegion + " 与 " + region);
                return true;
            }
        }
        return false;
    }

    public ReportExcelDto doCmd2(JSONObject reqJson) {
        int startYear = 2020;
        CommunityDto communityDto = new CommunityDto();
        communityDto.setCommunityId(reqJson.getString("communityId"));
        List<CommunityDto> communityDtos = communityInnerServiceSMOImpl.queryCommunitys(communityDto);
        FeeQueryParams feeQueryParams = new FeeQueryParams();
        feeQueryParams.setCommunityId(reqJson.getString("communityId"));
        feeQueryParams.setStartYear(startYear);
        int currentYear = java.time.Year.now().getValue();
        feeQueryParams.setEndYear(currentYear + 2);
        if(reqJson.containsKey("endYear") && !reqJson.get("endYear").equals("") && reqJson.get("endYear") != null) {
            currentYear=Integer.parseInt(reqJson.get("endYear")+"");
            feeQueryParams.setEndYear(Integer.parseInt(reqJson.get("endYear")+"")+2);
        }
        int endYear = feeQueryParams.getEndYear();
        int doYear = endYear - startYear;
    /**
     * 检查两个区域是否重叠
     */
    private boolean regionsOverlap(CellRangeAddress region1, CellRangeAddress region2) {
        boolean rowsOverlap = !(region1.getLastRow() < region2.getFirstRow() ||
                region1.getFirstRow() > region2.getLastRow());

        ReportQueryRecord reportQueryRecord = new ReportQueryRecord();
        reportQueryRecord.setCommunityId(reqJson.getString("communityId"));
        reportQueryRecord.setQueryStatus("0");
        reportQueryRecord.setEndYear(currentYear+"");
        reportQueryRecord.setOperator("白单流水物业表");
        List<ReportQueryRecord> reportQueryRecords = reportFeeInnerServiceSMOImpl.queryReport(BeanConvertUtil.beanCovertMap(reportQueryRecord));
        int row = Integer.parseInt(reqJson.containsKey("row")?reqJson.getString("row"):"10");
        int page = Integer.parseInt(reqJson.containsKey("page")?reqJson.getString("page"):"1");
        startYear = 2016;
        endYear = DateUtil.getYear();
        Object[][] test = new Object[row][];
        RoomDto roomDto = new RoomDto();
        roomDto.setCommunityId(reqJson.getString("communityId"));
        roomDto.setRow(row);
        roomDto.setPage(page * row - row);
        roomDto.setFloorNum(reqJson.containsKey("floorId")&&!(reqJson.getString("floorId").equals(""))?reqJson.getString("floorId"):null);
        roomDto.setOwnerName(reqJson.containsKey("ownerName")&&reqJson.getString("ownerName").equals("")?null:reqJson.getString("ownerName"));
        List<Map> rooms = roomInnerServiceSMOImpl.queryRoomsAsReport(roomDto);
        int count = roomInnerServiceSMOImpl.queryRoomsCount(roomDto);
        FeeConfigDto feeConfigDto = new FeeConfigDto();
        feeConfigDto.setCommunityId(reqJson.getString("communityId"));
        if(reqJson.containsKey("feeTypeCd") && !(reqJson.get("feeTypeCd").equals(""))){
            feeConfigDto.setFeeTypeCd(reqJson.getString("feeTypeCd"));
        }else{
            feeConfigDto.setFeeTypeCds(new String[]{"630000001"});
        }
        if(reqJson.containsKey("importFee") && !(reqJson.get("importFee").equals(""))){
            feeConfigDto.setFeeNameEq(reqJson.getString("importFee"));
        }else{
            feeConfigDto.setFeeTypeCds(new String[]{"630000001"});
        }
        List<FeeConfigDto> originalDtos = feeConfigInnerServiceSMOImpl.queryFeeConfigs(feeConfigDto);
        boolean colsOverlap = !(region1.getLastColumn() < region2.getFirstColumn() ||
                region1.getFirstColumn() > region2.getLastColumn());

// 2. 流式处理：排序 → 去重（按单价） → 取前2条
        List<FeeConfigDto> top2DistinctPriceDtos = originalDtos.stream()
                // 第一步：按业务规则排序（示例：先按单价升序，相同单价按startTime降序（保留最新记录））
                .sorted(
                        // 单价排序：null值放最后，非null按自然顺序（如BigDecimal升序）
                        Comparator.comparing(FeeConfigDto::getSquarePrice, Comparator.nullsLast(Comparator.naturalOrder()))
                                // 相同单价时，按startTime降序（确保保留“最新”的那条）
                                .thenComparing(FeeConfigDto::getStartTime, Comparator.nullsLast(Comparator.reverseOrder()))
                )
                // 第二步：按单价分组（自动去重，每组对应一个唯一单价）
                .collect(Collectors.groupingBy(
                        dto -> (dto.getSquarePrice() == null) ? new Object() : dto.getSquarePrice(), // 处理null的分组键
                        Collectors.toList() // 每组内是排序后的记录（已确保最优）
                ))
                // 第三步：提取每组的第一条（即该单价的“最优记录”），再取前2条
                .values().stream()
                .map(group -> group.get(0))
                .collect(Collectors.toList());
        List<FeeConfigDto> feeConfigDtos = top2DistinctPriceDtos;

        int arrLength = 11 + (2 * feeConfigDtos.size()) + 8 + ((endYear - startYear + 1) * 3 + 10) - 9;
        ReportExcelDto[] header = new ReportExcelDto[arrLength];
        List<List<ReportExcelDto>> lists = headerDoing(feeConfigDtos, startYear, endYear, arrLength);
        test[0] = header;
        for (int i = 1; i <= rooms.size(); i++) {
            Map map = rooms.get(i - 1);
            String[] strings = new String[arrLength];
            strings[0] = String.valueOf(row * (page - 1) + i);
            strings[1] = String.valueOf(rooms.get(i - 1).get("property_type"));
            strings[2] = rooms.get(i - 1).get("floor_num").toString();
            strings[3] = rooms.get(i - 1).get("unit_num").toString();
            strings[4] = rooms.get(i - 1).get("room_num").toString();
            strings[5] = strings[3] + "-" +  strings[4];
            strings[6] = rooms.get(i - 1).get("property_address").toString();
            strings[7] = rooms.get(i - 1).get("room_area").toString();
            strings[8] = rooms.get(i - 1).get("name").toString();
            for (int j = 1; j <= feeConfigDtos.size(); j++) {
                strings[8 + j] = doublequ2(Double.parseDouble(feeConfigDtos.get(j - 1).getSquarePrice()))+"";
            }
            strings[8 + feeConfigDtos.size() + 1] = "";
            for (int j = 1; j <= feeConfigDtos.size(); j++) {
                strings[8 + feeConfigDtos.size() + 1 + j] = (doublequ2(Double.parseDouble(feeConfigDtos.get(j - 1).getSquarePrice()) * Double.parseDouble(String.valueOf((BigDecimal) rooms.get(i - 1).get("room_area")))))+"";
            }
            strings[8 + (2 * feeConfigDtos.size()) + 2] = (doublequ2(Double.parseDouble(feeConfigDtos.get(feeConfigDtos.size() - 1).getSquarePrice()) * Double.parseDouble(String.valueOf((BigDecimal) rooms.get(i - 1).get("room_area")))))+"";
            strings[8 + (2 * feeConfigDtos.size()) + 2] = doublequ2(Double.parseDouble(strings[8 + (2 * feeConfigDtos.size()) + 2]) * 12)+"";
            HashMap<Object, Object> objectObjectHashMap = new HashMap<>();
            objectObjectHashMap.put("payObjId", map.get("room_id"));
            List<Map> fee = reportFeeInnerServiceSMOImpl.onceRoomFee(objectObjectHashMap);
            double allMoney = 0;
            double allPayMoney = 0;
            int monthCount = 0;
            double noPayMoney = 0;
            double year1 = 0;
            double year2 = 0;
            double discountMoney = 0;
            String noPayDate = "";
            currentYear = DateUtil.getYear();
            for (Map map1 : fee){
                if(map1.containsKey("统计维度") && !(map1.get("统计维度").toString().contains("总计"))){
                    allMoney = doublequ2(((BigDecimal) map1.get("应收金额")).doubleValue()+allMoney);
                    allPayMoney = doublequ2(((BigDecimal) map1.get("实收金额")).doubleValue()+allPayMoney);
                    monthCount = ((BigDecimal)map1.get("未收月数")).intValue()+monthCount;
                    noPayMoney = doublequ2(((BigDecimal)map1.get("未收金额")).intValue()+noPayMoney);
                    noPayDate = noPayDate + map1.get("未收区间");
                    discountMoney = doublequ2(((BigDecimal)map1.get("折扣金额")).doubleValue()+discountMoney);
                    if(map1.get("统计维度").equals("2026")){
                        year1 = doublequ2(((BigDecimal) map1.get("应收金额")).doubleValue() + year1);
                    }
                    if(map1.get("统计维度").equals("2027")){
                        year2 = doublequ2(((BigDecimal) map1.get("应收金额")).doubleValue() + year2);
                    }
                }

            }
            for (int x = endYear ; x >= startYear ; x --){
                strings[8 + (2 * feeConfigDtos.size()) + 10 + (3 * (endYear - x)) + 1] = "0";
                strings[8 + (2 * feeConfigDtos.size()) + 10 + (3 * (endYear - x)) + 2] = "";
                strings[8 + (2 * feeConfigDtos.size()) + 10 + (3 * (endYear - x)) + 3] = "";
                for (Map map1 : fee){
                    if(map1.get("统计维度").equals((endYear - x + startYear)+"年")) {
                        strings[8 + (2 * feeConfigDtos.size()) + 10 + (3 * (endYear - x)) + 1] = Integer.parseInt(strings[8 + (2 * feeConfigDtos.size()) + 10 + (3 * (endYear - x)) + 1]) + ((BigDecimal) map1.get("已收月数")).intValue() + "";
                        if(map1.containsKey("已收区间")){
                            strings[8 + (2 * feeConfigDtos.size()) + 10 + (3 * (endYear - x)) + 2] = strings[8 + (2 * feeConfigDtos.size()) + 10 + (3 * (endYear - x)) + 2] + "  " + map1.get("已收区间");
                        }
                        if(map1.containsKey("未收区间")){
                            strings[8 + (2 * feeConfigDtos.size()) + 10 + (3 * (endYear - x)) + 3] = strings[8 + (2 * feeConfigDtos.size()) + 10 + (3 * (endYear - x)) + 3] + "  " + map1.get("未收区间");
                        }
                    }
                }
            }
            strings[8 + (2 * feeConfigDtos.size()) + 3] = allMoney+"";
            strings[8 + (2 * feeConfigDtos.size()) + 4] = allPayMoney+"";
            strings[8 + (2 * feeConfigDtos.size()) + 5] = "";
            strings[8 + (2 * feeConfigDtos.size()) + 6] = year1+"";//2026
            strings[8 + (2 * feeConfigDtos.size()) + 7] = year2+"";//2027
            strings[8 + (2 * feeConfigDtos.size()) + 8] = monthCount+"";
            strings[8 + (2 * feeConfigDtos.size()) + 9] = noPayMoney+"";
            strings[8 + (2 * feeConfigDtos.size()) + 10] = "";//22
            strings[8 + (2 * feeConfigDtos.size()) + 11] = "";//23历年待收
            strings[8 + (2 * feeConfigDtos.size()) + 10 + (3 * (endYear - startYear)) + 3 + 1] = discountMoney+"";
            test[i - 1] = strings;
        }

        ReportExcelDto reportExcelDto = new ReportExcelDto();
        reportExcelDto.setHeader(lists);
        reportExcelDto.setData(test);
        return reportExcelDto;
        return rowsOverlap && colsOverlap;
    }

    private String[][] backFeeList(String number, Object roomId) {
        HashMap<Object, Object> objectObjectHashMap = new HashMap<>();
        objectObjectHashMap.put("payObjId", roomId);
        objectObjectHashMap.put("feeTypeCd",number);
        List<Map> fee = reportFeeInnerServiceSMOImpl.onceRoomFee(objectObjectHashMap);
        String[][] strings = new String[fee.size()][17];
        for (int i = 0 ; i < fee.size(); i++) {
            Map map = fee.get(i);
            strings[i][0] = (String) map.get("统计维度");
            strings[i][1] = ((BigDecimal) map.get("应收月数")).toString();
            strings[i][2] = doublequ2(((BigDecimal) map.get("应收金额")).doubleValue())+"";
//                strings[i][3] = (String) map.get("折扣金额");
            strings[i][4] = doublequ2(((BigDecimal) map.get("折扣金额")).doubleValue())+"";
//                strings[i][5] = (String) map.get("折扣金额");
//                strings[i][6] = (String) map.get("折扣金额");
//                strings[i][7] = (String) map.get("折扣金额");//申请人
//                strings[i][8] = (String) map.get("折扣金额");//审批人
//                strings[i][9] = (String) map.get("折扣金额");//发起日期
            strings[i][10] = Double.parseDouble(strings[i][2]) - Double.parseDouble(strings[i][4])+"";
            strings[i][11] = ((BigDecimal) map.get("已收月数")).toString();
            strings[i][12] = doublequ2(((BigDecimal) map.get("实收金额")).doubleValue())+"";
            strings[i][13] = (String) map.get("已收区间");
            strings[i][14] = ((BigDecimal) map.get("未收月数")).toString();
            strings[i][15] = doublequ2(((BigDecimal) map.get("未收金额")).doubleValue())+"";
            strings[i][16] = (String) map.get("未收区间");
    /**
     * 设置单元格值
     */
    private void setCellValue(Cell cell, Object value) {
        if (value == null) {
            return;
        }
        return strings;
        if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Number) {
            cell.setCellValue(((Number) value).doubleValue());
        } else if (value instanceof Date) {
            cell.setCellValue((Date) value);
        } else {
            cell.setCellValue(value.toString());
        }
    }

    private void setHeader(String[] header) {
        header[0] = "年份";
        header[1] = "应缴月数";
        header[2] = "应缴金额";
        header[3] = "折扣";
        header[4] = "打折金额";
        header[5] = "打折区间";
        header[6] = "打折单归档日期";
        header[7] = "申请人";
        header[8] = "审批人";
        header[9] = "发起日期";
        header[10] = "折后应缴";
        header[11] = "已收月数";
        header[12] = "已缴金额";
        header[13] = "已收区间";
        header[14] = "未缴月数";
        header[15] = "未缴金额";
        header[16] = "未收区间";

    /**
     * 格式化double保留两位小数
     */
    private String formatDouble(double num) {
        return String.valueOf((int) (num * 100) / 100.0);
    }

    private List<List<ReportExcelDto>> headerDoing(List<FeeConfigDto> feeConfigDtos, int startYear, int endYear, int arrLength) {
        LinkedList<List<ReportExcelDto>> reportExcelDtos = new LinkedList<>();
        int feeRow = feeConfigDtos.size();
        LinkedList<ReportExcelDto> header = new LinkedList<>();

// 1. 固定表头项：严格按原代码col值设置
        header.add(createHeaderRow("序号", 1, 3));          // 原row.setCol(1)
        header.add(createHeaderRow("物业类型", 1, 3));      // 原未改col，沿用前一个1
        header.add(createHeaderRow("楼栋号/弄", 1, 3));    // 原未改col，沿用1
        header.add(createHeaderRow("门号", 1, 3));          // 原未改col，沿用1
        header.add(createHeaderRow("室号", 1, 3));          // 原未改col，沿用1
        header.add(createHeaderRow("门室号", 1, 3));        // 原未改col，沿用1
        header.add(createHeaderRow("产证地址", 1, 3));      // 原未改col，沿用1
        header.add(createHeaderRow("收费面积（m²）", 1, 3));// 原未改col，沿用1
        header.add(createHeaderRow("购房人姓名", 1, 3));    // 原未改col，沿用1

// 2. 第一个feeRow循环：原未显式改col，沿用前一个1（按原逻辑保持col=1）
        for (int i = 0; i < feeRow; i++) {
            header.add(createHeaderRow(feeConfigDtos.get(i).getFeeName(), 1, 3));
    /**
     * 安全解析double
     */
    private double parseDouble(Object value, double defaultValue) {
        if (value == null) {
            return defaultValue;
        }

// 3. 设备运作费：原未改col，沿用前一个1
        header.add(createHeaderRow("设备运作费", 1, 3));

// 4. 第二个feeRow循环：原未显式改col，沿用前一个1（按原逻辑保持col=1）
        for (int i = 0; i < feeRow; i++) {
            header.add(createHeaderRow(feeConfigDtos.get(i).getFeeName(), 1, 3));
        try {
            return Double.parseDouble(value.toString());
        } catch (NumberFormatException e) {
            return defaultValue;
        }

// 5. 年应收款：原显式setCol(1)，保持col=1
        header.add(createHeaderRow("年应收款", 1, 3));

// 6. 合计(2020年1月-至今)：原显式setCol(8)，保持col=8；原setRow(1)，保持row=1
        header.add(createHeaderRow("合计(2020年1月-至今)", 8, 1));

// 7. 历年实收：原显式setCol((endYear - startYear + 1) * 3)，保持该计算逻辑
        int yearCol = (endYear - startYear + 1) * 3;
        header.add(createHeaderRow("历年实收", yearCol+1, 1));
        header.add(createHeaderRow("操作", 1, 3));    // 原未改col，沿用1

// 最终添加表头到报表列表
        reportExcelDtos.add(header);


        // 初始化 LinkedList（而非数组，支持顺序添加）
        LinkedList<ReportExcelDto> header2 = new LinkedList<>();

// 1. 应收、实收、代收等固定项：按顺序add，对应原数组0-7下标
        header2.add(createHeaderRow("应收", 1, 2));          // 对应原header[0]
        header2.add(createHeaderRow("实收", 1, 2));          // 对应原header[1]
        header2.add(createHeaderRow("代收", 1, 2));          // 对应原header[2]
        header2.add(createHeaderRow("2026年", 1, 2));        // 对应原header[3]
        header2.add(createHeaderRow("2027年", 1, 2));        // 对应原header[4]
        header2.add(createHeaderRow("待收月数", 1, 2));      // 对应原header[5]
        header2.add(createHeaderRow("待收金额", 1, 2));      // 对应原header[6]
        header2.add(createHeaderRow("代收区间", 1, 2));      // 对应原header[7]

// 2. 年份循环：按原下标逻辑计算顺序，继续add（原header[8]及以后）
        for (int i = startYear; i <= endYear; i++) {
            // 原逻辑：index = 7 + (i - startYear + 1) → 首次循环i=startYear时，index=8
            // LinkedList无需关心下标，直接add即可保持顺序（与原数组下标顺序一致）
            header2.add(createHeaderRow(String.valueOf(i), 3, 1));
        }

// 3. 打折金额汇总：原逻辑中"年份循环后"的下一个位置，继续add
        header2.add(createHeaderRow("打折金额汇总", 1, 2));

// 4. 最终添加到报表列表
        reportExcelDtos.add(header2);

        LinkedList<ReportExcelDto> header3 = new LinkedList<>();

        for (int i = startYear ; i <= endYear ; i++){
            header3.add(createHeaderRow("已收月数", 1, 1));
            header3.add(createHeaderRow("已收区间", 1, 1));
            header3.add(createHeaderRow("未收区间", 1, 1));
        }
        reportExcelDtos.add(header3);

        return reportExcelDtos;
    }

    private ReportExcelDto createHeaderRow(String text, int col, int row) {
        ReportExcelDto reportExcelDto = new ReportExcelDto();
        reportExcelDto.setTest(text); // 注意：可能是setText()的笔误
        reportExcelDto.setCol(col);
        reportExcelDto.setRow(row);
        return reportExcelDto;
    /**
     * 安全解析BigDecimal
     */
    private BigDecimal parseBigDecimal(Object value, BigDecimal defaultValue) {
        if (value == null) {
            return defaultValue;
        }
        if (value instanceof BigDecimal) {
            return (BigDecimal) value;
        }
        try {
            return new BigDecimal(value.toString());
        } catch (NumberFormatException e) {
            return defaultValue;
        }
    }

    public double doublequ2(double num){
        return (int)(num * 100) / 100.0;
    }
}
}