feat(投资): 市州月报统一填报模板 + 县(市、区)列解析 + 表头行判定收紧

zhizhijie

2026-09-20 a08c70db67e49bf6cd125df04ebaf68c26f0d8dd

 traffic-audit-server/src/main/java/com/trafficaudit/reportexport/service/ReportExportService.java

@@ -226,6 +226,36 @@
                    }
                    break;
                }
                case "investPassengerDetail":
                case "investLogisticsDetail": {
                    boolean paxDetail = "investPassengerDetail".equals(t);
                    String catName = paxDetail ? "客运站场" : "物流园区";
                    String catLabel = paxDetail ? "客运站场投资月报" : "物流园区投资月报";
                    List<InvestmentProject> catProjects = investProjectMapper.selectList(
                        new LambdaQueryWrapper<InvestmentProject>().eq(InvestmentProject::getCategory, catName));
                    final List<Long> catIds = new ArrayList<>();
                    for (InvestmentProject cp : catProjects) catIds.add(cp.getId());
                    if (catIds.isEmpty()) {
                        rows = 0;
                        notes.add("缺数据：" + catLabel);
                    } else {
                        rows = addReadyCount(() -> investMonthlyMapper.selectCount(
                            periodQw(period, mode, InvestmentMonthly::getReportPeriod)
                                .in(InvestmentMonthly::getProjectId, catIds)), catLabel, notes);
                    }
                    String detTpl = paxDetail ? "模板_X月客运站投资明细表.xlsx" : "模板_X月物流站场投资明细.xls";
                    try {
                        if (resolveAnyTemplate(investTemplateDir, detTpl) == null) {
                            rows = 0;
                            notes.add("docs/投资/模板 下缺少《" + detTpl + "》");
                        }
                    } catch (Exception ex) {
                        rows = 0;
                        notes.add("母版定位失败：" + ex.getMessage());
                    }
                    auditTypes.add("INVEST");
                    break;
                }
                default:
                    rows = 0;
                    notes.add("未知报表类型：" + t);
@@ -381,7 +411,9 @@
                coverage.addAll(investCover);
                // 投资为当月报表（无 1..N 累计口径），只提示当月缺失
                if (!investCover.contains(month)) missing.add(month);
                investPreview(period, mode, summary, problems);
                if ("investPassengerDetail".equals(t)) investPreview(period, mode, "客运站场", summary, problems);
                else if ("investLogisticsDetail".equals(t)) investPreview(period, mode, "物流园区", summary, problems);
                else investPreview(period, mode, summary, problems);
            } else if ("energySummary".equals(t)) {
                Long ec = energyMapper.selectCount(periodQw(period, mode, EnergyVehicleQuarterly::getReportPeriod));
                if (ec != null && ec > 0) coverage.add(month);
@@ -412,7 +444,8 @@
    }

    private boolean isInvestType(String t) {
        return "investPlan".equals(t) || "investFiveYearLogistics".equals(t) || "investBillion".equals(t) || "investCounty".equals(t);
        return "investPlan".equals(t) || "investFiveYearLogistics".equals(t) || "investBillion".equals(t) || "investCounty".equals(t)
            || "investPassengerDetail".equals(t) || "investLogisticsDetail".equals(t);
    }

    private boolean isEnergyType(String t) {
@@ -668,9 +701,23 @@
        if (allZero) problems.add("全省累计值全为 0（公交/出租月报可能未导入）");
    }

    /** 投资摘要：项目数 + 本月完成/自年初/自开始累计 */
    /** 投资摘要：项目数 + 本月完成/自年初/自开始累计（全投资月报合计，不分类别） */
    private void investPreview(String period, String mode, List<Map<String, Object>> summary, List<String> problems) {
        List<InvestmentMonthly> list = investMonthlyMapper.selectList(periodQw(period, mode, InvestmentMonthly::getReportPeriod));
        investPreview(period, mode, null, summary, problems);
    }

    /** 投资摘要（category 非空时只统计该类别，如客运站场/物流园区投资明细表） */
    private void investPreview(String period, String mode, String category, List<Map<String, Object>> summary, List<String> problems) {
        LambdaQueryWrapper<InvestmentMonthly> qw = periodQw(period, mode, InvestmentMonthly::getReportPeriod);
        if (category != null) {
            List<InvestmentProject> ps = investProjectMapper.selectList(
                new LambdaQueryWrapper<InvestmentProject>().eq(InvestmentProject::getCategory, category));
            List<Long> ids = new ArrayList<>();
            for (InvestmentProject p : ps) ids.add(p.getId());
            if (ids.isEmpty()) { problems.add("缺数据：库内没有「" + category + "」项目主档"); return; }
            qw.in(InvestmentMonthly::getProjectId, ids);
        }
        List<InvestmentMonthly> list = investMonthlyMapper.selectList(qw);
        Set<Long> proj = new HashSet<>();
        double monthDone = 0.0, yearCum = 0.0, startCum = 0.0;
        for (InvestmentMonthly m : list) {
@@ -3460,7 +3507,7 @@
     * 保留全部页签/版式/公式/2025 年缓存值，仅把表头年份动态化为目标年；数据填充按 P2 起逐步接入。
     */
    public byte[] exportSummaryWorkbook(String period, String mode) throws Exception {
        return exportSummaryWorkbook(period, mode, null);
        return exportSummaryWorkbook(period, mode, null, null);
    }

    /** 生成_道路运输量汇总表.xlsx（P0-P1 骨架，支持 toMonth 裁剪）：
@@ -3468,6 +3515,13 @@
     *  toMonth 非空且小于 M 时，把 8 个台账/合成页裁到 toMonth 月（toMonth+1..M 的当月值/同比列清空、保留表头，
     *  累计与累计同比公式改为按 toMonth 月重算），用于“先生成 1-6 月、1-7 月扩列”验证。 */
    public byte[] exportSummaryWorkbook(String period, String mode, Integer toMonth) throws Exception {
        return exportSummaryWorkbook(period, mode, toMonth, null);
    }

    /** 同上，额外通过 progress 回调报告进度（异步导出任务用：步骤百分比 + 步骤文案） */
    public byte[] exportSummaryWorkbook(String period, String mode, Integer toMonth,
                                        java.util.function.BiConsumer<Integer, String> progress) throws Exception {
        reportProgress(progress, 2, "校验报表期与母版");
        int year = Integer.parseInt(period.contains("-") ? period.split("-")[0] : period);
        int month = period.contains("-") ? monthOf(period, mode) : 12;
        File template = resolveSummaryMother(period, mode);
@@ -3475,9 +3529,11 @@
            throw new RuntimeException("未找到汇总表母版《" + year + "年" + month + "月道路运输量汇总表.xlsx》（docs/生成汇总大表）");
        }
        double savedZipRatio = ZipSecureFile.getMinInflateRatio();
        reportProgress(progress, 6, "读取母版工作簿");
        ZipSecureFile.setMinInflateRatio(0.0001); // 新版母版 styles.xml 由 openpyxl 高压缩（0.0099<0.01）触发 POI Zip-bomb 误报，读取后还原
        try (InputStream in = new FileInputStream(template);
             XSSFWorkbook wb = new XSSFWorkbook(in)) {
            reportProgress(progress, 12, "还原同期月度块");
            File donor = resolveSummaryDonor(year, month);
            if (donor != null) {
                try (InputStream din = new FileInputStream(donor); XSSFWorkbook dwb = new XSSFWorkbook(din)) {
@@ -3496,26 +3552,42 @@
            int effMonth = (toMonth != null && toMonth > 0 && toMonth < month) ? toMonth : month;
            List<String> dbProblems = new ArrayList<>();
            String effPeriod = String.format("%04d-%02d", year, effMonth);
            reportProgress(progress, 22, "回填当月数据");
            int dbFilled = summaryWorkbookFiller.fillMonthlyLedger(wb, effPeriod, year, effMonth, dbProblems);
            int yoyFilled = summaryWorkbookFiller.ensureMonthYoyFormulas(wb, year, effMonth);
            int monthFormulaFilled = summaryWorkbookFiller.ensureMonthFormulas(wb, year, effMonth);
            // 累计同比分母修正：母版把「去年各月」与「去年1-M月累计」辅助列同时计入（重复计/漏当月）
            int cumYoyFixed = summaryWorkbookFiller.repairCumulativeYoyFormulas(wb, year, effMonth);
            reportProgress(progress, 45, "刷新排名页与跨页合成页");
            int rankRefreshed = refreshSummaryRankSheets(wb, effPeriod, "month");
            int cumulativeRefreshed = refreshSummaryCumulativeSheets(wb, year, effMonth); // 四张跨页合成页随报表期刷新
            int widthApplied = applySummaryReferenceLayout(wb);
            log.info("汇总工作簿数据驱动回填完成：写入当月值格数={}，补写当月同比公式格数={}，补写当月合计公式格数={}，"
                    + "刷新排名页数值格数={}，刷新四页（城市汇总/城市分市州/中口径分析/道路运输周转量）数值格数={}，"
                    + "修正累计同比分母格数={}，刷新排名页数值格数={}，刷新四页（城市汇总/城市分市州/中口径分析/道路运输周转量）数值格数={}，"
                    + "按《生成_道路运输量汇总表》对齐列宽列数={}，缺数提示={}",
                    dbFilled, yoyFilled, monthFormulaFilled, rankRefreshed, cumulativeRefreshed, widthApplied, dbProblems);
                    dbFilled, yoyFilled, monthFormulaFilled, cumYoyFixed, rankRefreshed, cumulativeRefreshed, widthApplied, dbProblems);
            applyTwoDecimalFormat(wb); // 数值显示两位小数（保留全精度）；% / 日期等既有样式不改变
            int inlineFixed = normalizeStaleInlineStrings(wb); // 清掉「非 inlineStr 却残留 <is>」的非法格，否则 Excel 拒绝打开
            if (inlineFixed > 0) log.warn("汇总工作簿清掉残留 inlineStr 非法格 {} 个（母版由 openpyxl 写出时的典型问题）", inlineFixed);
            reportProgress(progress, 70, "重算整本公式（约 4 万格）");
            int[] recalc = recalcWorkbookFormulas(wb); // 公式重算并写回缓存值：全省行(Σ市州)/累计列/排名 SUM 才能被预览与二次处理读到
            log.info("汇总工作簿公式重算完成：成功 {} 格，失败 {} 格（失败格保留母版旧缓存值，打开时按 fullCalcOnLoad 重算）", recalc[0], recalc[1]);
            if (!wb.getCTWorkbook().isSetCalcPr()) wb.getCTWorkbook().addNewCalcPr();
            wb.getCTWorkbook().getCalcPr().setFullCalcOnLoad(true); // 还原/改写公式后打开即重算
            reportProgress(progress, 95, "写出文件");
            return toBytes(wb);
        } finally {
            ZipSecureFile.setMinInflateRatio(savedZipRatio);
        }
    }

    /** 导出进度回调（异步导出任务用）：回调自身异常不影响导出 */
    private static void reportProgress(java.util.function.BiConsumer<Integer, String> progress, int percent, String step) {
        if (progress == null) return;
        try {
            progress.accept(percent, step);
        } catch (Exception ignore) {
            // 进度回调失败不影响导出
        }
    }

@@ -5381,6 +5453,7 @@
                hssfText(row, 0, "合计");
                InvestAgg prov = new InvestAgg();
                for (InvestmentMonthly m : monthlies) prov.add(m, pmap.get(m.getProjectId()));
                addProjectsWithoutMonthly(prov, projects, mByProject, null, null);
                writeInvestAggHssf(row, prov);
                continue;
            }
@@ -5393,6 +5466,7 @@
                    InvestmentProject p = pmap.get(m.getProjectId());
                    if (p != null && blockCategory.equals(p.getCategory())) agg.add(m, p);
                }
                addProjectsWithoutMonthly(agg, projects, mByProject, blockCategory, null);
                writeInvestAggHssf(row, agg);
                seq = 0;
                continue;
@@ -5421,6 +5495,7 @@
                    if (p != null && city.equals(p.getCity())
                        && (blockCategory == null || blockCategory.equals(p.getCategory()))) agg.add(m, p);
                }
                addProjectsWithoutMonthly(agg, projects, mByProject, blockCategory, city);
                writeInvestAggHssf(row, agg);
                continue;
            }
@@ -5468,6 +5543,24 @@
        if (agg.yearPlan > 0) hssfNum(row, 23, agg.yearCum / agg.yearPlan);
    }

    /**
     * 补计「本期没有月度记录、但项目主档有总投资」的项目（只计总投资这一列）。
     * 场景：某市州本月未报送单表（如武汉 8 月物流），主档里项目仍在，若不补计，该市州小计/合计会整块为空。
     *
     * @param category 块类别（客运站场/物流园区）；null 表示不限
     * @param city     市州全称（如 武汉市）；null 表示不限
     */
    private void addProjectsWithoutMonthly(InvestAgg agg, List<InvestmentProject> projects,
                                           Map<Long, InvestmentMonthly> mByProject,
                                           String category, String city) {
        for (InvestmentProject p : projects) {
            if (p == null || mByProject.containsKey(p.getId())) continue;
            if (category != null && !category.equals(p.getCategory())) continue;
            if (city != null && !city.equals(p.getCity())) continue;
            if (p.getTotalInvestment() != null) agg.total += p.getTotalInvestment();
        }
    }



    // ==================== 投资模块：客运站投资明细表 / 物流站场投资明细（模板底稿原位替换） ====================