fix: 投资系统基准导入兼容查询结果表头（行号/单位/时期/计划总投资）

zhizhijie

9 小时以前 91d7eee872986f3aaa6e04edf843dd3a0526ba8f

 traffic-audit-server/src/main/java/com/trafficaudit/dataimport/service/DataImportService.java

@@ -168,6 +168,7 @@
        Map<String, Integer> colMap;
        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
            Sheet sheet = wb.getSheetAt(0);
            requireHeaderCols(sheet, "道路货物运输月度生产情况", "企业名称", "货运量_总计");
            colMap = buildHeaderMap(sheet.getRow(0));
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
@@ -284,6 +285,7 @@
        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
            // sheet0: 公路旅客6月报
            Sheet sheet = wb.getSheetAt(0);
            requireHeaderCols(sheet, "公路旅客运输月度生产情况", "企业名称", "客运量_总计");
            Map<String, Integer> colMap = buildHeaderMap(sheet.getRow(0));
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
@@ -476,6 +478,7 @@
        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
            Sheet sheet = wb.getSheetAt(0);
            Map<String, Integer> colMap = buildHeaderMap(sheet.getRow(0));
            requireHeaderCols(sheet, "旅客运政车辆数", "企业名称", "车辆数", "载客位数");
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                if (row == null) continue;
@@ -530,6 +533,7 @@
        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
            // sheet0: 第二季度货车能耗明细（每行左右各5组车辆，每组固定18列）
            Sheet sheet = wb.getSheetAt(0);
            requireHeaderKeyword(sheet, "道路货运车辆能源消耗情况", "企业名称", "车牌号");
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                if (row == null) continue;
@@ -648,6 +652,7 @@
        List<EnergyAuthVehicle> authList = new ArrayList<>();
        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
            Sheet sheet = wb.getSheetAt(0);
            requireHeaderKeyword(sheet, "能耗车辆运政信息", "车牌号");
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                if (row == null) continue;
@@ -716,6 +721,7 @@
        int failRows = 0;
        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
            Sheet sheet = wb.getSheetAt(0);
            requireHeaderKeyword(sheet, "运政车辆", "企业名称");
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                if (row == null) continue;
@@ -763,6 +769,7 @@
        int failRows = 0;
        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
            Sheet sheet = wb.getSheetAt(0);
            requireHeaderKeyword(sheet, "轨迹里程", "企业名称");
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                if (row == null) continue;
@@ -806,6 +813,14 @@
        List<ScaleSplitTransport> list = new ArrayList<>();
        int failRows = 0;
        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
            boolean hasPeriodSheet = false;
            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                String sn = wb.getSheetAt(i).getSheetName();
                if (sn != null && (sn.contains("当月") || sn.contains("累计"))) { hasPeriodSheet = true; break; }
            }
            if (!hasPeriodSheet) {
                throw new RuntimeException("所选数据类型【规上规下拆分】与文件内容不符：未找到含 当月/累计 的 sheet，请确认是否选错了数据类型");
            }
            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                Sheet sheet = wb.getSheetAt(i);
                String sheetName = sheet.getSheetName();
@@ -896,6 +911,18 @@
        int monthCount = Integer.parseInt(period.split("-")[1]);
        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
            Sheet sheet = wb.getSheetAt(0);
            boolean cityRowFound = false;
            for (int r = 1; r <= Math.min(sheet.getLastRowNum(), 30); r++) {
                Row rr = sheet.getRow(r);
                if (rr == null) continue;
                String nm = getString(rr, 0);
                if (nm == null || nm.trim().isEmpty()) continue;
                String region = RegionUtil.normalizeCityName(nm);
                if (RegionUtil.cityList().contains(region) || "湖北省".equals(region)) { cityRowFound = true; break; }
            }
            if (!cityRowFound) {
                throw new RuntimeException("所选数据类型【货运量周转量】与文件内容不符：未识别到市州行（首列应为市州名称），请确认是否选错了数据类型");
            }
            Map<String, FreightTurnoverImport> freightMap = new HashMap<>();
            Map<String, FreightTurnoverImport> turnoverMap = new HashMap<>();
            Set<String> seen = new HashSet<>();
@@ -1152,6 +1179,33 @@
        }
    }

    /** 类型-表头校验：按必需列名（解析器实际依赖的列），缺失即抛"类型与文件不符" */
    private void requireHeaderCols(Sheet sheet, String typeLabel, String... cols) {
        Map<String, Integer> colMap = buildHeaderMap(sheet.getRow(0));
        List<String> missing = new ArrayList<>();
        for (String c : cols) {
            if (!colMap.containsKey(c)) missing.add(c);
        }
        if (!missing.isEmpty()) {
            throw new RuntimeException("所选数据类型【" + typeLabel + "】与文件内容不符：表头缺少必需列【" + String.join("】【", missing) + "】，请确认是否选错了数据类型");
        }
    }

    /** 类型-表头校验：扫描表头行，命中任一关键字即通过 */
    private void requireHeaderKeyword(Sheet sheet, String typeLabel, String... keywords) {
        Row header = sheet == null ? null : sheet.getRow(0);
        if (header != null) {
            for (Cell cell : header) {
                String v = FORMATTER.formatCellValue(cell);
                if (v == null || v.isEmpty()) continue;
                for (String k : keywords) {
                    if (v.contains(k)) return;
                }
            }
        }
        throw new RuntimeException("所选数据类型【" + typeLabel + "】与文件内容不符：表头未包含【" + String.join("】【", keywords) + "】等列，请确认是否选错了数据类型");
    }

    /** 按表头名称建立列号映射，兼容新旧模板列位差异 */
    private Map<String, Integer> buildHeaderMap(Row header) {
        Map<String, Integer> colMap = new HashMap<>();
@@ -1398,7 +1452,7 @@
                }
            }
            if (headerRow < 0) {
                throw new RuntimeException("未找到表头行（需包含 序号/项目名称 列）");
                throw new RuntimeException("所选数据类型【" + (passenger ? "投资月报（客运站场）" : "投资月报（物流园区）") + "】与文件内容不符：未找到表头行（需包含 序号/项目名称 列），请确认是否选错了数据类型");
            }
            for (int r = headerRow + 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
@@ -1553,11 +1607,19 @@
            }
            fileResults.add(fr);
        }
        // 幂等清理：同类别同报表期月度数据 + INVEST 审核结果（项目主档跨月保留，按名 upsert）
        cleanInvestMonthly(category, period);
        // 幂等清理：按本次文件涉及市州清理月度数据 + INVEST 审核结果（项目主档跨月保留，按名 upsert）
        cleanInvestMonthlyByCity(category, period, rows);
        Map<String, Set<String>> knownCities = loadInvestKnownCities(category);
        Map<String, String> fileFirstMismatch = new LinkedHashMap<>();
        int success = 0;
        List<String> failDetails = new ArrayList<>();
        for (InvestCityRow item : rows) {
            String mismatch = investCityMismatchReason(item, knownCities);
            if (mismatch != null) {
                failDetails.add(mismatch);
                fileFirstMismatch.putIfAbsent(item.fileName, mismatch);
                continue;
            }
            try {
                saveInvestProjectRow(item.project, item.monthly);
                success++;
@@ -1569,6 +1631,11 @@
                failDetails.add(item.fileName + " 第 " + item.excelRow + " 行（" + item.project.getProjectName() + "）：" + reason);
                log.error("Invest city insert error: file {}, row {}", item.fileName, item.excelRow, e);
            }
        }
        // 把首个市州错位提示挂到对应文件结果上，便于页面弹窗直接看到
        for (Map<String, Object> fr2 : fileResults) {
            String msg = fileFirstMismatch.get(fr2.get("fileName"));
            if (msg != null && fr2.get("error") == null) fr2.put("error", msg);
        }
        String errorDetail = String.join("\n", failDetails);
        if (errorDetail.length() > 4000) errorDetail = errorDetail.substring(0, 4000) + "\n……";
@@ -1587,6 +1654,119 @@
        result.put("fiveYearTemplateProjects", fiveYearNames.size());
        result.put("fiveYearMatched", fiveYearMatched);
        log.info("Invest city {} {} imported: {} rows (fiveYear matched {}/{})",
            category, period, success, fiveYearMatched, fiveYearNames.size());
        return result;
    }

    /**
     * 多文件批量导入：前端一次选择多个 Excel 上传。
     * 投资类型（investment/investmentLogistics）按市州单表解析（文件名/填报单位识别市州，十五五自动标记）；
     * 其他类型逐个走现有解析逻辑（importDirFile）。
     */
    public Map<String, Object> importBatchFiles(String type, String period, List<MultipartFile> files) throws Exception {
        if (files == null || files.isEmpty()) {
            throw new RuntimeException("未接收到任何文件");
        }
        if ("investment".equals(type) || "investmentLogistics".equals(type)) {
            String category = "investment".equals(type) ? "客运站场" : "物流园区";
            return importInvestCityFiles(period, category, files);
        }
        List<Map<String, Object>> fileResults = new ArrayList<>();
        int totalSuccess = 0, totalFail = 0;
        for (MultipartFile f : files) {
            Map<String, Object> fr = new LinkedHashMap<>();
            fr.put("fileName", f.getOriginalFilename());
            try {
                ImportResult r = importDirFile(type, f, period);
                fr.put("success", r.getSuccess());
                fr.put("fail", r.getFail());
                if (r.getFail() > 0 && r.getFailDetails() != null && !r.getFailDetails().isEmpty()) {
                    fr.put("error", r.getFailDetails().get(0));
                }
                totalSuccess += r.getSuccess();
                totalFail += r.getFail();
            } catch (Exception e) {
                fr.put("success", 0);
                fr.put("fail", 0);
                fr.put("error", e.getMessage() == null ? e.toString() : e.getMessage());
                totalFail++;
            }
            fileResults.add(fr);
        }
        Map<String, Object> result = new LinkedHashMap<>();
        result.put("type", type);
        result.put("total", files.size());
        result.put("success", totalSuccess);
        result.put("fail", totalFail);
        result.put("files", fileResults);
        return result;
    }

    /** 投资多文件批量：逐文件按市州单表解析，统一幂等清理落库，批次汇总 */
    public Map<String, Object> importInvestCityFiles(String period, String category, List<MultipartFile> files) throws Exception {
        boolean passenger = "客运站场".equals(category);
        Set<String> fiveYearNames = loadFiveYearProjectNames();
        List<InvestCityRow> rows = new ArrayList<>();
        List<Map<String, Object>> fileResults = new ArrayList<>();
        for (MultipartFile f : files) {
            Map<String, Object> fr = new LinkedHashMap<>();
            fr.put("fileName", f.getOriginalFilename());
            try (Workbook wb = WorkbookFactory.create(f.getInputStream())) {
                parseInvestCityWorkbook(wb, f.getOriginalFilename(), category, period, fiveYearNames, rows, fr);
            } catch (Exception e) {
                fr.put("success", 0);
                fr.put("error", e.getMessage() == null ? e.toString() : e.getMessage());
                log.error("invest city file parse error: {}", f.getOriginalFilename(), e);
            }
            fileResults.add(fr);
        }
        String catDir = passenger ? "客运（客运站）" : "物流（物流园区）";
        cleanInvestMonthlyByCity(category, period, rows);
        Map<String, Set<String>> knownCities = loadInvestKnownCities(category);
        Map<String, String> fileFirstMismatch = new LinkedHashMap<>();
        int success = 0;
        List<String> failDetails = new ArrayList<>();
        for (InvestCityRow item : rows) {
            String mismatch = investCityMismatchReason(item, knownCities);
            if (mismatch != null) {
                failDetails.add(mismatch);
                fileFirstMismatch.putIfAbsent(item.fileName, mismatch);
                continue;
            }
            try {
                saveInvestProjectRow(item.project, item.monthly);
                success++;
            } catch (Exception e) {
                Throwable cause = e;
                while (cause.getCause() != null) cause = cause.getCause();
                String reason = cause.getMessage();
                if (reason == null || reason.trim().isEmpty()) reason = e.getMessage();
                failDetails.add(item.fileName + " 第 " + item.excelRow + " 行（" + item.project.getProjectName() + "）：" + reason);
                log.error("Invest city insert error: file {}, row {}", item.fileName, item.excelRow, e);
            }
        }
        // 把首个市州错位提示挂到对应文件结果上，便于页面弹窗直接看到
        for (Map<String, Object> fr2 : fileResults) {
            String msg = fileFirstMismatch.get(fr2.get("fileName"));
            if (msg != null && fr2.get("error") == null) fr2.put("error", msg);
        }
        String errorDetail = String.join("\n", failDetails);
        if (errorDetail.length() > 4000) errorDetail = errorDetail.substring(0, 4000) + "\n……";
        recordBatch("多文件批量导入（" + catDir + "/" + period + "）", "INVEST_" + category + "_CITY",
            period, rows.size(), success, failDetails.size(), errorDetail);
        int fiveYearMatched = 0;
        for (InvestCityRow item : rows) {
            if (Integer.valueOf(1).equals(item.project.getIsFiveYear())) fiveYearMatched++;
        }
        Map<String, Object> result = new LinkedHashMap<>();
        result.put("total", rows.size());
        result.put("success", success);
        result.put("fail", failDetails.size());
        result.put("failDetails", failDetails);
        result.put("files", fileResults);
        result.put("fiveYearTemplateProjects", fiveYearNames.size());
        result.put("fiveYearMatched", fiveYearMatched);
        log.info("Invest city multi-file {} {} imported: {} rows (fiveYear matched {}/{})",
            category, period, success, fiveYearMatched, fiveYearNames.size());
        return result;
    }
@@ -1627,6 +1807,7 @@
        }
        String city = cityFromFileName(fileName);
        if (city == null) city = cityFromSheetHeader(best);
        fr.put("city", city == null ? "" : city);
        String currentCity = city;
        Map<String, Integer> col = bestCol;
        int nameCol = col.get("name");
@@ -1637,6 +1818,15 @@
            Row row = best.getRow(r);
            if (row == null) continue;
            String name = cellStr(row, nameCol);
            String seq = hasSeq ? cellStr(row, seqCol) : null;
            // 市州小节行：部分汇总表把市州名写在序号列（如十堰汇总表 A 列写"武汉"），需同样识别
            if (seq != null && !seq.trim().isEmpty()) {
                String seqCity = investmentCity(seq.trim());
                if (seqCity != null) {
                    currentCity = seqCity;
                    continue;
                }
            }
            if (name == null || name.trim().isEmpty()) continue;
            String nt = name.trim().replaceAll("[\r\n]+", "");
            if (nt.contains("合计") || nt.contains("总计")) continue;
@@ -1645,7 +1835,6 @@
            if (nt.replace(" ", "").matches(".*小\\s*计.*")) continue;
            if (nt.startsWith("填报") || nt.startsWith("单位负责人") || nt.matches("^\\d+、.*")) continue;
            if (hasSeq) {
                String seq = cellStr(row, seqCol);
                if (seq == null || seq.trim().isEmpty()) {
                    // 市州小节行（如 "武汉" "天 门"），更新当前市州
                    String c = investmentCity(nt);
@@ -1884,7 +2073,7 @@
        return null;
    }

    /** 同类别同报表期月度数据 + INVEST 审核结果清理（幂等） */
    /** 同类别同报表期月度数据 + INVEST 审核结果清理（幂等，全省汇总大表全量清理） */
    private void cleanInvestMonthly(String category, String period) {
        List<InvestmentProject> sameCat = investProjectMapper.selectList(
            new LambdaQueryWrapper<InvestmentProject>().eq(InvestmentProject::getCategory, category));
@@ -1895,15 +2084,60 @@
                .eq(InvestmentMonthly::getReportPeriod, period)
                .in(InvestmentMonthly::getProjectId, catProjectIds));
        }
        deleteInvestAuditResults(period, null);
    }

    /** 按市州清理：只删除本次文件涉及市州（含未识别市州的 null 项目）的月度数据 + INVEST 审核结果 */
    private void cleanInvestMonthlyByCity(String category, String period, List<InvestCityRow> rows) {
        Set<String> citySet = new HashSet<>();
        boolean hasNullCity = false;
        for (InvestCityRow item : rows) {
            String c = item.project.getCity();
            if (c == null || c.trim().isEmpty()) hasNullCity = true;
            else citySet.add(c.trim());
        }
        if (citySet.isEmpty() && !hasNullCity) return;
        boolean cleanNullCity = hasNullCity;
        LambdaQueryWrapper<InvestmentProject> pw = new LambdaQueryWrapper<InvestmentProject>()
            .eq(InvestmentProject::getCategory, category);
        pw.and(w -> {
            if (!citySet.isEmpty()) w.in(InvestmentProject::getCity, citySet);
            if (cleanNullCity) {
                if (!citySet.isEmpty()) w.or();
                w.isNull(InvestmentProject::getCity);
            }
        });
        List<InvestmentProject> cityProjects = investProjectMapper.selectList(pw);
        java.util.Set<Long> catProjectIds = new HashSet<>();
        for (InvestmentProject pc : cityProjects) catProjectIds.add(pc.getId());
        if (catProjectIds.isEmpty()) return;
        List<InvestmentMonthly> oldMonthlies = investMonthlyMapper.selectList(
            new LambdaQueryWrapper<InvestmentMonthly>()
                .eq(InvestmentMonthly::getReportPeriod, period)
                .in(InvestmentMonthly::getProjectId, catProjectIds));
        java.util.Set<Long> oldMonthlyIds = new HashSet<>();
        for (InvestmentMonthly m : oldMonthlies) oldMonthlyIds.add(m.getId());
        investMonthlyMapper.delete(new LambdaQueryWrapper<InvestmentMonthly>()
            .eq(InvestmentMonthly::getReportPeriod, period)
            .in(InvestmentMonthly::getProjectId, catProjectIds));
        deleteInvestAuditResults(period, oldMonthlyIds);
    }

    /** 清理 INVEST 审核结果（monthlyIds 为 null 表示该期间全量清理） */
    private void deleteInvestAuditResults(String period, java.util.Set<Long> monthlyIds) {
        List<Long> investRuleIds = new ArrayList<>();
        for (com.trafficaudit.rulemanage.entity.AuditRule rule : ruleMapper.selectList(null)) {
            if ("INVEST".equals(rule.getReportType())) investRuleIds.add(rule.getId());
        }
        if (!investRuleIds.isEmpty()) {
            auditResultMapper.delete(new LambdaQueryWrapper<AuditResult>()
                .eq(AuditResult::getReportPeriod, period)
                .in(AuditResult::getRuleId, investRuleIds));
        if (investRuleIds.isEmpty()) return;
        LambdaQueryWrapper<AuditResult> w = new LambdaQueryWrapper<AuditResult>()
            .eq(AuditResult::getReportPeriod, period)
            .in(AuditResult::getRuleId, investRuleIds);
        if (monthlyIds != null) {
            if (monthlyIds.isEmpty()) return;
            w.in(AuditResult::getReportId, monthlyIds);
        }
        auditResultMapper.delete(w);
    }

    /** 项目主档 upsert（按 项目名+类别）+ 月度写入 */
@@ -1922,6 +2156,32 @@
            monthly.setProjectId(exist.getId());
        }
        investMonthlyMapper.insert(monthly);
    }

    /** 载入某类别「规范化项目名 -> 已入库市州集合」，用于市州一致性校验（名称在多个市州重复时放弃校验） */
    private Map<String, Set<String>> loadInvestKnownCities(String category) {
        Map<String, Set<String>> map = new HashMap<>();
        List<InvestmentProject> list = investProjectMapper.selectList(
            new LambdaQueryWrapper<InvestmentProject>().eq(InvestmentProject::getCategory, category));
        for (InvestmentProject p : list) {
            if (p.getCity() == null || p.getCity().trim().isEmpty()) continue;
            map.computeIfAbsent(normalizeProjectName(p.getProjectName()), k -> new HashSet<>())
               .add(p.getCity().trim());
        }
        return map;
    }

    /** 市州一致性校验：库中唯一归属市州与本次解析不一致时返回错误说明，否则返回 null */
    private String investCityMismatchReason(InvestCityRow item, Map<String, Set<String>> knownCities) {
        String parsed = item.project.getCity();
        if (parsed == null || parsed.trim().isEmpty()) return null;
        Set<String> known = knownCities.get(normalizeProjectName(item.project.getProjectName()));
        if (known == null || known.size() != 1) return null;
        String knownCity = known.iterator().next();
        if (knownCity.equals(parsed.trim())) return null;
        return item.fileName + " 第 " + item.excelRow + " 行（" + item.project.getProjectName()
            + "）：市州一致性校验未通过——库中该项目归属「" + knownCity + "」，本次文件解析为「" + parsed.trim()
            + "」，疑似市州错位，已跳过导入，请核对源文件";
    }

    private String cellStr(Row row, int idx) {
@@ -1959,6 +2219,7 @@
        int failRows = 0;
        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
            Sheet sheet = wb.getSheetAt(0);
            requireHeaderKeyword(sheet, "投资系统导出", "项目名称", "计划总投资", "单位");
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                if (row == null) continue;
@@ -2198,6 +2459,7 @@
        List<CityTaxiAuth> list = new ArrayList<>();
        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
            Sheet sheet = wb.getSheetAt(0);
            requireHeaderKeyword(sheet, "出租车运政车辆", "车牌");
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                if (row == null) continue;
@@ -2369,10 +2631,13 @@
        result.put("parent", cur.getParent() == null ? "" : cur.getParent());
        List<Map<String, Object>> dirs = new ArrayList<>();
        File[] subs = cur.listFiles(File::isDirectory);
        int dirTotal = 0;
        if (subs != null) {
            Arrays.sort(subs, Comparator.comparing(File::getName));
            for (File d : subs) {
                if (d.isHidden()) continue;
                dirTotal++;
                if (dirs.size() >= 300) continue;
                Map<String, Object> m = new LinkedHashMap<>();
                m.put("name", d.getName());
                m.put("path", d.getAbsolutePath());
@@ -2380,17 +2645,21 @@
            }
        }
        result.put("dirs", dirs);
        result.put("dirsTruncated", dirTotal > dirs.size());
        List<String> excelFiles = new ArrayList<>();
        File[] files = cur.listFiles((d, n) -> {
            String lower = n.toLowerCase();
            return lower.endsWith(".xlsx") || lower.endsWith(".xls");
        });
        int excelTotal = files == null ? 0 : files.length;
        if (files != null) {
            Arrays.sort(files, Comparator.comparing(File::getName));
            for (File f : files) excelFiles.add(f.getName());
            int take = Math.min(files.length, 300);
            for (int i = 0; i < take; i++) excelFiles.add(files[i].getName());
        }
        result.put("excelCount", excelFiles.size());
        result.put("excelCount", excelTotal);
        result.put("files", excelFiles);
        result.put("filesTruncated", excelTotal > excelFiles.size());
        return result;
    }