From 0cfd6e093e08566b4199836a54d827067580007e Mon Sep 17 00:00:00 2001
From: zhizhijie <zhizhijie@users.noreply.gitee.com>
Date: 星期三, 16 九月 2026 11:58:23 +0800
Subject: [PATCH] feat(import): 货运量周转量支持多年度工作簿（21-26年）一次性入库；fix(report): 汇总大表预览删除中口径之后的明细表
---
traffic-audit-server/src/main/java/com/trafficaudit/dataimport/service/DataImportService.java | 170 ++++++++++++++++++++++++++++++++++++++++++++++++++------
1 files changed, 152 insertions(+), 18 deletions(-)
diff --git a/traffic-audit-server/src/main/java/com/trafficaudit/dataimport/service/DataImportService.java b/traffic-audit-server/src/main/java/com/trafficaudit/dataimport/service/DataImportService.java
index 4c372e7..9b5d0d1 100644
--- a/traffic-audit-server/src/main/java/com/trafficaudit/dataimport/service/DataImportService.java
+++ b/traffic-audit-server/src/main/java/com/trafficaudit/dataimport/service/DataImportService.java
@@ -85,6 +85,7 @@
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.LinkedHashSet;
+import java.util.TreeMap;
import java.util.stream.Collectors;
import java.text.SimpleDateFormat;
@@ -1054,11 +1055,22 @@
// ========== 璐ц繍閲忓懆杞噺锛堟ā鏉縚璐ц繍閲忓懆杞噺.xlsx锛屾瘡鏈堝鍏ワ級 ==========
public int importFreightTurnover(MultipartFile file, String period) throws Exception {
+ try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
+ // 澶氬勾搴﹀伐浣滅翱锛堜竴骞翠竴涓� Sheet锛氭暣骞� 1-12 鏈堬紝濡� 21-26 骞村畾绋挎枃浠讹級鈫� 鍏ㄩ儴骞翠唤鍏ュ簱
+ List<String> yearSheets = detectYearSheets(wb);
+ if (!yearSheets.isEmpty()) {
+ return importFreightTurnoverByYear(wb, file, yearSheets);
+ }
+ return importFreightTurnoverByMonth(wb, file, period);
+ }
+ }
+
+ /** 姣忔湀瀵煎叆锛堟ā鏉縚璐ц繍閲忓懆杞噺.xlsx锛夛細宸﹀崐浠婂勾 1-N 鏈堬紝鍙冲崐鍘诲勾 1-12 鏈� */
+ private int importFreightTurnoverByMonth(Workbook wb, MultipartFile file, String period) throws Exception {
List<FreightTurnoverImport> list = new ArrayList<>();
- int failRows = 0;
// 鎶ヨ〃鏈� N 鏈堬細宸﹀崐涓轰粖骞� 1-N 鏈堬紙鍒楁暟涓嶅浐瀹氾級锛屽彸鍗婂浐瀹氫负鍘诲勾 1-12 鏈堬紙绠楀悓姣旂敤锛�
int monthCount = Integer.parseInt(period.split("-")[1]);
- try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
+ {
Sheet sheet = wb.getSheetAt(0);
Row header = sheet.getRow(0);
String h0 = header == null ? null : getString(header, 0);
@@ -1123,27 +1135,149 @@
}
list.add(entity);
}
- } catch (Exception e) {
- throw e;
}
+ Map<String, List<FreightTurnoverImport>> byPeriod = new LinkedHashMap<>();
+ byPeriod.put(period, list);
+ return persistFreightTurnover(byPeriod, file.getOriginalFilename());
+ }
- // 骞傜瓑锛氬悓鎶ヨ〃鏈熷厛鍒犻櫎
- freightTurnoverMapper.delete(new LambdaQueryWrapper<FreightTurnoverImport>()
- .eq(FreightTurnoverImport::getReportPeriod, period));
+ /** 璇嗗埆銆屽骞村害宸ヤ綔绨裤�嶏細姣忎釜 Sheet 涓�骞达紙Sheet 鍚� 2021骞�/2022骞粹�︼級锛屼笖琛ㄥご涓恒�岃揣杩愰噺/1鏈堚��12鏈堛�嶏紱鍚﹀垯杩斿洖绌哄垪琛� */
+ private List<String> detectYearSheets(Workbook wb) {
+ List<String> names = new ArrayList<>();
+ if (wb.getNumberOfSheets() < 2) return names;
+ for (int i = 0; i < wb.getNumberOfSheets(); i++) {
+ Sheet sheet = wb.getSheetAt(i);
+ String nm = sheet.getSheetName() == null ? "" : sheet.getSheetName().trim();
+ if (!nm.matches("\\d{4}\\s*骞�?")) { names.clear(); return names; }
+ Row header = sheet.getRow(0);
+ String h0 = header == null ? null : getString(header, 0);
+ String h1 = header == null ? null : getString(header, 1);
+ if (h0 == null || !h0.contains("璐ц繍閲�") || h1 == null || !h1.contains("鏈�")) {
+ names.clear();
+ return names;
+ }
+ names.add(nm);
+ }
+ return names;
+ }
- int success = 0;
- for (FreightTurnoverImport entity : list) {
- try {
- freightTurnoverMapper.insert(entity);
- success++;
- } catch (Exception e) {
- log.error("freight turnover insert error: {}", entity.getRegionName(), e);
- failRows++;
+ /** 澶氬勾搴﹀伐浣滅翱瀵煎叆锛氶�愬勾涓�鏉℃姤琛ㄦ湡锛堟姤琛ㄦ湡=璇ュ勾鏈�鍚庢湁鏁版嵁鐨勬湀浠斤級锛屼笂骞村悓鏈熷彇涓婁竴骞村害 Sheet */
+ private int importFreightTurnoverByYear(Workbook wb, MultipartFile file, List<String> yearSheets) {
+ Map<Integer, String> sheetByYear = new TreeMap<>();
+ for (String sheetName : yearSheets) {
+ sheetByYear.put(Integer.parseInt(sheetName.replace("骞�", "").trim()), sheetName);
+ }
+ Map<Integer, Map<String, FreightTurnoverImport>> byYear = new TreeMap<>();
+ Map<Integer, Integer> maxMonthByYear = new HashMap<>();
+ for (Map.Entry<Integer, String> entry : sheetByYear.entrySet()) {
+ Map<String, FreightTurnoverImport> parsed = new LinkedHashMap<>();
+ int maxMonth = parseWholeYearSheet(wb.getSheet(entry.getValue()), parsed);
+ if (parsed.isEmpty()) continue;
+ byYear.put(entry.getKey(), parsed);
+ maxMonthByYear.put(entry.getKey(), Math.max(1, Math.min(12, maxMonth)));
+ }
+ Map<String, List<FreightTurnoverImport>> byPeriod = new LinkedHashMap<>();
+ for (Map.Entry<Integer, Map<String, FreightTurnoverImport>> entry : byYear.entrySet()) {
+ int year = entry.getKey();
+ String yearPeriod = year + "-" + String.format("%02d", maxMonthByYear.get(year));
+ Map<String, FreightTurnoverImport> lastYear = byYear.get(year - 1);
+ List<FreightTurnoverImport> rows = new ArrayList<>();
+ for (Map.Entry<String, FreightTurnoverImport> regionEntry : entry.getValue().entrySet()) {
+ FreightTurnoverImport entity = regionEntry.getValue();
+ entity.setReportPeriod(yearPeriod);
+ // 涓婂勾鍚屾湡 = 涓婁竴骞村害 Sheet锛堝骞村害鏂囦欢娌℃湁鍙冲崐銆屽幓骞�1-12鏈堛�嶅尯鍧楋級
+ FreightTurnoverImport last = lastYear == null ? null : lastYear.get(regionEntry.getKey());
+ if (last != null) {
+ for (int m = 1; m <= 12; m++) {
+ setLastFreight(entity, m, getFreight(last, m));
+ setLastTurnover(entity, m, getTurnover(last, m));
+ }
+ }
+ rows.add(entity);
+ }
+ byPeriod.put(yearPeriod, rows);
+ }
+ return persistFreightTurnover(byPeriod, file.getOriginalFilename());
+ }
+
+ /** 瑙ｆ瀽鏁村勾 Sheet锛�1-12 鏈堝垪锛夛細璐ц繍閲忓潡 + 鍛ㄨ浆閲忓潡锛涜繑鍥炴渶鍚庝竴涓湁璐ц繍閲忔暟鎹殑鏈堜唤 */
+ private int parseWholeYearSheet(Sheet sheet, Map<String, FreightTurnoverImport> out) {
+ Map<String, FreightTurnoverImport> freightMap = new LinkedHashMap<>();
+ Map<String, FreightTurnoverImport> turnoverMap = new LinkedHashMap<>();
+ Set<String> seen = new HashSet<>();
+ int maxMonth = 0;
+ for (int r = 1; r <= sheet.getLastRowNum(); r++) {
+ Row row = sheet.getRow(r);
+ if (row == null) continue;
+ String name = getString(row, 0);
+ if (name == null || name.trim().isEmpty()) continue;
+ String region = RegionUtil.normalizeCityName(name);
+ boolean isCity = RegionUtil.cityList().contains(region);
+ if (!isCity && !"婀栧寳鐪�".equals(region)) continue; // 琛ㄥご/璇存槑琛�
+ if (seen.contains(region)) {
+ // 璇ュ悕绉板凡鍑虹幇杩� -> 鍛ㄨ浆閲忓尯鍧�
+ FreightTurnoverImport entity = turnoverMap.get(region);
+ if (entity == null) {
+ entity = new FreightTurnoverImport();
+ entity.setRegionName(name.trim());
+ turnoverMap.put(region, entity);
+ }
+ for (int m = 1; m <= 12; m++) {
+ setTurnover(entity, m, getDecimalOrNull(row, m));
+ }
+ } else {
+ seen.add(region);
+ FreightTurnoverImport entity = freightMap.get(region);
+ if (entity == null) {
+ entity = new FreightTurnoverImport();
+ entity.setRegionName(name.trim());
+ freightMap.put(region, entity);
+ }
+ for (int m = 1; m <= 12; m++) {
+ Double v = getDecimalOrNull(row, m);
+ setFreight(entity, m, v);
+ if (v != null && m > maxMonth) maxMonth = m;
+ }
}
}
- recordBatch(file.getOriginalFilename(), "FREIGHT_TURNOVER", period, list.size(), success, failRows, null);
- log.info("Freight turnover imported: {} rows for {}", success, period);
- return success;
+ // 鍚堝苟锛氫竴涓競宸炰竴琛岋紙璐ц繍閲� + 鍛ㄨ浆閲忥級
+ for (Map.Entry<String, FreightTurnoverImport> entry : freightMap.entrySet()) {
+ FreightTurnoverImport entity = entry.getValue();
+ FreightTurnoverImport turnover = turnoverMap.get(entry.getKey());
+ if (turnover != null) {
+ for (int m = 1; m <= 12; m++) {
+ setTurnover(entity, m, getTurnover(turnover, m));
+ }
+ }
+ out.put(entry.getKey(), entity);
+ }
+ return maxMonth;
+ }
+
+ /** 钀藉簱锛氭寜鎶ヨ〃鏈熷厛鍒犲悗鎻掞紙骞傜瓑锛夛紝姣忎釜鎶ヨ〃鏈熶竴鏉″鍏ユ壒娆¤褰曪紝杩斿洖鎴愬姛鏉℃暟 */
+ private int persistFreightTurnover(Map<String, List<FreightTurnoverImport>> byPeriod, String fileName) {
+ int total = 0;
+ for (Map.Entry<String, List<FreightTurnoverImport>> entry : byPeriod.entrySet()) {
+ String p = entry.getKey();
+ List<FreightTurnoverImport> rows = entry.getValue();
+ freightTurnoverMapper.delete(new LambdaQueryWrapper<FreightTurnoverImport>()
+ .eq(FreightTurnoverImport::getReportPeriod, p));
+ int failRows = 0;
+ int success = 0;
+ for (FreightTurnoverImport entity : rows) {
+ try {
+ freightTurnoverMapper.insert(entity);
+ success++;
+ } catch (Exception e) {
+ log.error("freight turnover insert error: {}", entity.getRegionName(), e);
+ failRows++;
+ }
+ }
+ recordBatch(fileName, "FREIGHT_TURNOVER", p, rows.size(), success, failRows, null);
+ log.info("Freight turnover imported: {} rows for {}", success, p);
+ total += success;
+ }
+ return total;
}
/** 瑙ｆ瀽璐ц繍閲忓尯鍧楄锛氬乏鍗� B 璧� N 鍒椾负浠婂勾 1-N 鏈堬紝鍙冲崐涓哄幓骞� 1-12 鏈� */
--
Gitblit v1.9.1