From 677d0ee49b04a0a158bcbaae51a677f5cfaf77cb Mon Sep 17 00:00:00 2001
From: zhizhijie <zhizhijie@users.noreply.gitee.com>
Date: 星期六, 19 九月 2026 12:18:42 +0800
Subject: [PATCH] fix(货运): 明细查询去掉ROUND改为全精度导出 + 小数位约定说明 + 两查询等价性验证
---
traffic-audit-server/src/main/java/com/trafficaudit/dataimport/service/DataImportService.java | 591 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
1 files changed, 566 insertions(+), 25 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 e34a275..049d707 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
@@ -56,6 +56,14 @@
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
+import org.apache.poi.xwpf.usermodel.XWPFDocument;
+import org.apache.poi.xwpf.usermodel.XWPFParagraph;
+import org.apache.poi.xwpf.usermodel.XWPFTable;
+import org.apache.poi.xwpf.usermodel.XWPFTableRow;
+import org.w3c.dom.Document;
+import org.w3c.dom.Element;
+import org.w3c.dom.Node;
+import org.w3c.dom.NodeList;
import cn.hutool.core.io.IoUtil;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@@ -81,6 +89,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;
@@ -314,6 +323,9 @@
entity.setRegionCode(gs(row, colMap, "鎵�灞炲湴鍖�"));
entity.setEnterpriseCode(gs(row, colMap, "浼佷笟浠ｇ爜"));
entity.setEnterpriseName(name.trim());
+ // 鏂扮増妯℃澘鍦ㄣ�屼紒涓氬悕绉般�嶅悗鏂板浜嗕袱鍒楋紙缁熶竴绀句細淇＄敤浠ｇ爜/濉姤鍗曚綅锛夛紝鎸夎〃澶村彇鍊硷紝鑰佹ā鏉挎棤璇ュ垪鏃朵负 null
+ entity.setUnifiedCreditCode(gs(row, colMap, "缁熶竴绀句細淇＄敤浠ｇ爜"));
+ entity.setReportUnit(gs(row, colMap, "濉姤鍗曚綅"));
entity.setVehicleTotal(gi(row, colMap, "瀹㈣繍杞﹁締_鎬昏"));
entity.setSeatTotal(gi(row, colMap, "鏍稿畾杞藉浣峗鎬昏"));
entity.setVehicleSchedule(gi(row, colMap, "杞﹁締鏁癬瀹㈣繍鐝溅锛堝惈瀹氱嚎鏃呮父瀹㈣繍瀹㈣溅锛�"));
@@ -1050,11 +1062,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);
@@ -1119,27 +1142,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 鏈� */
@@ -1345,6 +1490,7 @@
CLEAR_TYPE_TO_OP.put("cityTaxiAuth", "CITY_TAXI_AUTH");
CLEAR_TYPE_TO_OP.put("wycTotal", "WYC_TOTAL");
CLEAR_TYPE_TO_OP.put("wycOrder", "WYC_ORDER");
+ CLEAR_TYPE_TO_OP.put("wycOrderReport", "WYC_ORDER");
CLEAR_TYPE_TO_AUDIT.put("h2032", new String[]{"H2032"});
CLEAR_TYPE_TO_AUDIT.put("transportAuth", new String[]{"H2032"});
@@ -1386,7 +1532,8 @@
CLEAR_TYPE_TO_BATCH.put("cityTaxi", new String[]{"CITY_TAXI"});
CLEAR_TYPE_TO_BATCH.put("cityTaxiAuth", new String[]{"CITY_TAXI_AUTH"});
CLEAR_TYPE_TO_BATCH.put("wycTotal", new String[]{"WYC_TOTAL"});
- CLEAR_TYPE_TO_BATCH.put("wycOrder", new String[]{"WYC_ORDER"});
+ CLEAR_TYPE_TO_BATCH.put("wycOrder", new String[]{"WYC_ORDER", "WYC_ORDER_REPORT"});
+ CLEAR_TYPE_TO_BATCH.put("wycOrderReport", new String[]{"WYC_ORDER_REPORT", "WYC_ORDER"});
}
/**
@@ -2318,10 +2465,10 @@
}
if (name == null || name.trim().isEmpty()) continue;
String nt = name.trim().replaceAll("[\r\n]+", "");
- if (nt.contains("鍚堣") || nt.contains("鎬昏")) continue;
+ String ntFlat = nt.replaceAll("\\s+", "");
+ if (ntFlat.contains("鍚堣") || ntFlat.contains("鎬昏") || ntFlat.contains("灏忚")) continue;
if (nt.matches("^[涓�浜屼笁鍥涗簲鍏竷鍏節鍗乚+銆�.*")) continue;
if (nt.matches("^锛圼涓�浜屼笁鍥涗簲鍏竷鍏節鍗乚+锛�.*")) continue;
- if (nt.replace(" ", "").matches(".*灏廫\s*璁�.*")) continue;
if (nt.startsWith("濉姤") || nt.startsWith("鍗曚綅璐熻矗浜�") || nt.matches("^\\d+銆�.*")) continue;
if (hasSeq) {
if (seq == null || seq.trim().isEmpty()) {
@@ -2383,7 +2530,8 @@
}
if (!hasName) continue;
Map<Integer, String> colTexts = new HashMap<>();
- for (int rr = r; rr <= Math.min(r + 3, sh.getLastRowNum()); rr++) {
+ int scanEnd = investHeaderScanEnd(sh, r, row);
+ for (int rr = r; rr <= scanEnd; rr++) {
Row rr2 = sh.getRow(rr);
if (rr2 == null) continue;
for (int c = 0; c <= rr2.getLastCellNum(); c++) {
@@ -2407,7 +2555,7 @@
else if (v.contains("鑷勾鍒濈疮璁�")) col.putIfAbsent("yearcum", c);
else if (v.contains("鏈湀瀹屾垚")) col.putIfAbsent("monthdone", c);
else if (v.contains("寤鸿闃舵")) col.putIfAbsent("stage", c);
- else if (v.contains("褰㈣薄杩涘害")) col.putIfAbsent("desc", c);
+ else if (v.contains("褰㈣薄杩涘害") || v.contains("寤鸿杩涘睍")) col.putIfAbsent("desc", c);
else if (v.contains("宸ュ彲") && !v.contains("椤圭洰鍚嶇О")) col.putIfAbsent("gk", c);
else if (v.contains("鍒濊") && !v.contains("椤圭洰鍚嶇О")) col.putIfAbsent("cs", c);
else if (v.contains("寤虹瓚闈㈢Н") && !v.contains("鏂板")) col.putIfAbsent("area", c);
@@ -2427,6 +2575,12 @@
int md = col.get("yearcum") + 1;
if (md <= row.getLastCellNum()) col.put("monthdone", md);
}
+ // desc 缂哄け鏃舵寜 stage+1 鍏滃簳锛氱渷鏍囥�岄」鐩缓璁鹃樁娈点�佽繘搴︽儏鍐点�嶆槸 L/M 鍚堝苟琛ㄥご锛�
+ // 瀛濇劅/鎭╂柦/鏋楀尯/闅忓窞绛夊競宸炲彧鍦� L 鍐欍�屽缓璁鹃樁娈点�嶃�丮 鐩存帴鍐欒繘搴︽枃瀛楄�屼笉鍐欐爣棰�
+ if (!col.containsKey("desc") && col.containsKey("stage") && col.get("stage") >= 0) {
+ int dc = col.get("stage") + 1;
+ if (dc <= row.getLastCellNum()) col.put("desc", dc);
+ }
for (String k : new String[]{"builder", "nature", "start", "end", "total", "startcum",
"yearplan", "yearcum", "monthdone", "stage", "desc", "gk", "cs", "area"}) {
col.putIfAbsent(k, -1);
@@ -2435,6 +2589,46 @@
return col;
}
return null;
+ }
+
+ /** 琛ㄥご鏂囨湰鎵弿涓嬬晫锛氶粯璁よ〃澶磋 +3锛涜嫢銆岄」鐩悕绉般�嶅垪棣栦釜鏁版嵁琛屾洿闈犱笅锛屽垯鎵╁睍鍒版暟鎹鍓嶄竴琛岋紙涓婇檺 +12锛夈��
+ * 鑳屾櫙锛氬捀瀹�/鍗佸牥绛夊競宸炴妸銆屽缓璁鹃樁娈�/褰㈣薄杩涘害銆嶆爣绛惧啓鍦ㄨ〃澶翠笅绗� 4~8 琛岋紝鍥哄畾 +3 浼氭紡鍒楋紙褰㈣薄杩涘害璇讳笉鍒帮級銆� */
+ private int investHeaderScanEnd(Sheet sh, int headerRow, Row headerRowObj) {
+ int hardEnd = Math.min(headerRow + 12, sh.getLastRowNum());
+ int nameCol = -1;
+ if (headerRowObj != null) {
+ for (int c = 0; c <= headerRowObj.getLastCellNum(); c++) {
+ String v = cellStr(headerRowObj, c);
+ if (v != null && v.contains("椤圭洰鍚嶇О")) { nameCol = c; break; }
+ }
+ }
+ int firstData = findFirstInvestDataRow(sh, headerRow, nameCol);
+ int scanEnd = (firstData > headerRow)
+ ? Math.min(hardEnd, firstData - 1)
+ : Math.min(headerRow + 3, sh.getLastRowNum());
+ if (scanEnd < headerRow) scanEnd = headerRow;
+ return scanEnd;
+ }
+
+ /** 銆岄」鐩悕绉般�嶅垪棣栦釜鏁版嵁琛屼笅鏍囷紙璺宠繃琛ㄥご缁涓庣┖琛岋級锛涙壘涓嶅埌杩斿洖 -1 */
+ private int findFirstInvestDataRow(Sheet sh, int headerRow, int nameCol) {
+ if (nameCol < 0) return -1;
+ for (int r = headerRow + 1; r <= sh.getLastRowNum(); r++) {
+ Row row = sh.getRow(r);
+ if (row == null) continue;
+ String name = cellStr(row, nameCol);
+ if (name == null || name.trim().isEmpty()) continue;
+ String nt = name.trim().replaceAll("[\r\n]+", "");
+ if (nt.contains("椤圭洰鍚嶇О")) continue;
+ String flat = nt.replaceAll("\\s+", "");
+ if (flat.contains("鍚堣") || flat.contains("鎬昏") || flat.contains("灏忚")) continue;
+ if (nt.matches("^[涓�浜屼笁鍥涗簲鍏竷鍏節鍗乚+銆�.*")) continue;
+ if (nt.matches("^锛圼涓�浜屼笁鍥涗簲鍏竷鍏節鍗乚+锛�.*")) continue;
+ if (nt.startsWith("濉姤") || nt.startsWith("鍗曚綅璐熻矗浜�") || nt.matches("^\\d+銆�.*")) continue;
+ if (investmentCity(nt) != null) continue;
+ return r;
+ }
+ return -1;
}
/** 缁熻 sheet 鍐呴」鐩暟鎹鏁帮紙鐢ㄤ簬澶� sheet 鍘嗗彶鏂囦欢閫夊綋鏈� sheet锛� */
@@ -2449,9 +2643,9 @@
String name = cellStr(row, nameCol);
if (name == null || name.trim().isEmpty()) continue;
String nt = name.trim().replaceAll("[\r\n]+", "");
- if (nt.contains("鍚堣") || nt.contains("鎬昏")) continue;
+ String ntFlat = nt.replaceAll("\\s+", "");
+ if (ntFlat.contains("鍚堣") || ntFlat.contains("鎬昏") || ntFlat.contains("灏忚")) continue;
if (nt.matches("^[涓�浜屼笁鍥涗簲鍏竷鍏節鍗乚+銆�.*")) continue;
- if (nt.replace(" ", "").matches(".*灏廫\s*璁�.*")) continue;
if (nt.startsWith("濉姤") || nt.startsWith("鍗曚綅璐熻矗浜�") || nt.matches("^\\d+銆�.*")) continue;
if (!hasSeq) {
if (nt.length() > 4) count++;
@@ -3144,6 +3338,352 @@
return ok;
}
+ // ========== 缃戠害杞﹁鍗曡繍琛岀洃娴嬪垎鏋愭姤鍛婏紙docx锛夊鍏� ==========
+
+ /** 缃戠害杞﹁鍗曟姤鍛婂鍏ョ粨鏋滐紙鎶ヨ〃鏈�/璁㈠崟鍚堣/瑕嗙洊甯傚窞鏁�/缂哄け甯傚窞锛� */
+ public static class WycOrderReportResult {
+ private final String period;
+ private final double orderSum;
+ private final int cityCount;
+ private final List<String> missingCities;
+
+ public WycOrderReportResult(String period, double orderSum, int cityCount, List<String> missingCities) {
+ this.period = period;
+ this.orderSum = orderSum;
+ this.cityCount = cityCount;
+ this.missingCities = missingCities;
+ }
+
+ public String getPeriod() { return period; }
+ public double getOrderSum() { return orderSum; }
+ public int getCityCount() { return cityCount; }
+ public List<String> getMissingCities() { return missingCities; }
+ }
+
+ /**
+ * 銆婄綉绾﹁溅琛屼笟YYYY骞碝鏈堣繍琛岀洃娴嬫儏鍐靛垎鏋愭姤鍛娿�嬶紙docx锛夊鍏ワ細
+ * 瑙ｆ瀽鎶ュ憡闄勪欢涓�屽湴甯傚悕绉� | 鈥� | 璁㈠崟鏁版�绘暟銆嶈〃鐨� 17 甯傚窞璁㈠崟鏁帮紝鍐欏叆 wyc_order_monthly 鐨�
+ * F 璁㈠崟鍚堣涓� G-W 17 甯傚窞璁㈠崟鍒楋紱鍏ㄧ渷 pin锛圔-E锛夋部鐢ㄨ鏈熷凡鏈夎锛岃嫢鏃犺鏈熻鍒欎粠
+ * wyc_total_monthly 鍚屾姤琛ㄦ湡澶嶅埗銆傛姤琛ㄦ湡浼樺厛鍙栨姤鍛婃爣棰樺勾鏈堬紝鍙栦笉鍒版椂鐢ㄩ〉闈㈡墍閫夋姤琛ㄦ湡銆�
+ */
+ public synchronized WycOrderReportResult importWycOrderReport(MultipartFile file, String period) throws Exception {
+ Map<String, Double> orders = new LinkedHashMap<>();
+ double[] sumHolder = new double[]{0.0};
+ String docPeriod;
+ byte[] bytes;
+ try (java.io.InputStream in = file.getInputStream()) {
+ bytes = IoUtil.readBytes(in);
+ }
+ boolean isZip = bytes.length > 3 && (bytes[0] & 0xFF) == 0x50 && (bytes[1] & 0xFF) == 0x4B;
+ boolean isWordMl = !isZip && looksLikeWordMl(bytes);
+ try {
+ if (isZip && !isWordPackage(bytes)) {
+ throw new RuntimeException("杩欎笉鏄� Word 鏂囨。锛堝帇缂╁寘閲屾病鏈� word/document.xml锛夛紝"
+ + "澶氬崐鏄� Excel 鎴栧叾瀹冩牸寮忋�傝涓婁紶缃戠害杞﹁鍗曞垎鏋愭姤鍛婄殑 .docx锛堟垨 Word 2003 XML锛�");
+ }
+ if (isZip) {
+ try (XWPFDocument doc = new XWPFDocument(new java.io.ByteArrayInputStream(bytes))) {
+ docPeriod = periodOfParagraphs(paragraphsOfDocx(doc));
+ parseWycOrderReportTables(doc, orders, sumHolder);
+ }
+ } else if (isWordMl) {
+ // 缃戠害杞︾洃绠″钩鍙颁笅鍙戠殑鎶ュ憡甯歌 WPS/Word 瀛樻垚銆學ord 2003 XML锛堝钩閾� XML锛夈�嶅悗鍐嶆敼鍚� .docx锛�
+ // 杩欑鏂囦欢涓嶆槸 zip 鍖咃紝XWPFDocument 鎵撲笉寮�锛堟姤 "File is not a zip file"锛夛紝闇�鎸� XML 鐩存帴瑙ｆ瀽銆�
+ String xml = new String(bytes, java.nio.charset.StandardCharsets.UTF_8);
+ Document dom = parseXmlSafely(xml);
+ docPeriod = periodOfParagraphs(paragraphsOfDocxXml(dom));
+ parseWycOrderReportTablesXml(dom, orders, sumHolder);
+ } else if (isOle2(bytes)) {
+ throw new RuntimeException("杩欐槸 Word 97-2003 鑰佹牸寮忥紙.doc锛夛紝璇风敤 WPS/Word 鎵撳紑鍚庛�屽彟瀛樹负銆峝ocx 鏍煎紡鍐嶅鍏�");
+ } else {
+ throw new RuntimeException("鏃犳硶璇嗗埆鐨勬枃浠舵牸寮忥細鏃笉鏄� docx锛坺ip 鍖咃級锛屼篃涓嶆槸 Word 2003 XML 鎴� .doc");
+ }
+ } catch (RuntimeException e) {
+ throw e;
+ } catch (Exception e) {
+ throw new RuntimeException("鏃犳硶璇诲彇 Word 鏂囨。锛岃涓婁紶 .docx 鏍煎紡鐨勩�婄綉绾﹁溅琛屼笟YYYY骞碝鏈堣繍琛岀洃娴嬫儏鍐靛垎鏋愭姤鍛娿�嬶紱"
+ + "鑻ヤ笂浼犵殑鍏跺疄鏄� Excel/PDF锛岃鏀圭敤瀵瑰簲妯℃澘瀵煎叆銆傦紙鍘熷閿欒锛�" + e.getMessage() + "锛�");
+ }
+ if (orders.isEmpty()) {
+ throw new RuntimeException("鏈В鏋愬埌浠讳綍甯傚窞璁㈠崟鏁帮紝璇风‘璁や笂浼犵殑鏄�婄綉绾﹁溅琛屼笟YYYY骞碝鏈堣繍琛岀洃娴嬫儏鍐靛垎鏋愭姤鍛娿��");
+ }
+ String rp = null;
+ if (docPeriod != null && docPeriod.matches("\\d{4}-\\d{2}")) rp = docPeriod;
+ else if (period != null && period.matches("\\d{4}-\\d{2}")) rp = period;
+ if (rp == null) {
+ throw new RuntimeException("鏃犳硶纭畾鎶ヨ〃鏈燂細鎶ュ憡鏍囬鏈惈銆孻YYY骞碝鏈堛�嶏紝涓旈〉闈㈡湭閫夋嫨鎶ヨ〃鏈�");
+ }
+
+ List<String> missing = new ArrayList<>();
+ double citySum = 0.0;
+ for (String city : RegionUtil.cityList()) {
+ Double v = orders.get(city);
+ if (v == null) missing.add(city);
+ else citySum += v;
+ }
+ double sum = sumHolder[0] > 0 ? sumHolder[0] : citySum;
+
+ WycOrderMonthly it = wycOrderMapper.selectOne(new LambdaQueryWrapper<WycOrderMonthly>()
+ .eq(WycOrderMonthly::getReportPeriod, rp).last("LIMIT 1"));
+ if (it == null) it = new WycOrderMonthly();
+ it.setReportPeriod(rp);
+ it.setOrderSum(sum > 0 ? sum : null);
+ it.setOrderWuhan(orderOf(orders, "姝︽眽甯�"));
+ it.setOrderHuangshi(orderOf(orders, "榛勭煶甯�"));
+ it.setOrderShiyan(orderOf(orders, "鍗佸牥甯�"));
+ it.setOrderYichang(orderOf(orders, "瀹滄槍甯�"));
+ it.setOrderXiangyang(orderOf(orders, "瑗勯槼甯�"));
+ it.setOrderEzhou(orderOf(orders, "閯傚窞甯�"));
+ it.setOrderJingmen(orderOf(orders, "鑽嗛棬甯�"));
+ it.setOrderXiaogan(orderOf(orders, "瀛濇劅甯�"));
+ it.setOrderJingzhou(orderOf(orders, "鑽嗗窞甯�"));
+ it.setOrderHuanggang(orderOf(orders, "榛勫唸甯�"));
+ it.setOrderXianning(orderOf(orders, "鍜稿畞甯�"));
+ it.setOrderSuizhou(orderOf(orders, "闅忓窞甯�"));
+ it.setOrderEnshi(orderOf(orders, "鎭╂柦宸�"));
+ it.setOrderXiantao(orderOf(orders, "浠欐甯�"));
+ it.setOrderQianjiang(orderOf(orders, "娼滄睙甯�"));
+ it.setOrderTianmen(orderOf(orders, "澶╅棬甯�"));
+ it.setOrderShennong(orderOf(orders, "绁炲啘鏋舵灄鍖�"));
+
+ if (it.getPinK() == null || it.getPinCK() == null || it.getPinZ() == null || it.getPinCZ() == null) {
+ WycTotalMonthly tot = wycTotalMapper.selectOne(new LambdaQueryWrapper<WycTotalMonthly>()
+ .eq(WycTotalMonthly::getReportPeriod, rp).last("LIMIT 1"));
+ if (tot != null) {
+ if (it.getPinK() == null) it.setPinK(tot.getPinK());
+ if (it.getPinCK() == null) it.setPinCK(tot.getPinCK());
+ if (it.getPinZ() == null) it.setPinZ(tot.getPinZ());
+ if (it.getPinCZ() == null) it.setPinCZ(tot.getPinCZ());
+ }
+ }
+
+ wycOrderMapper.delete(new LambdaQueryWrapper<WycOrderMonthly>().eq(WycOrderMonthly::getReportPeriod, rp));
+ wycOrderMapper.insert(it);
+ recordBatch(file.getOriginalFilename(), "WYC_ORDER_REPORT", rp, 1, 1, 0, null);
+ log.info("WycOrderReport imported: period={} sum={} cities={} missing={}", rp, sum, orders.size(), missing);
+ return new WycOrderReportResult(rp, sum, orders.size(), missing);
+ }
+
+ /** 鎶ュ憡鏍囬涓殑鎶ヨ〃鏈燂細缃戠害杞﹁涓�2026骞�7鏈� -> 2026-07锛涘彇涓嶅埌杩斿洖 null */
+ private String periodOfParagraphs(List<String> paragraphs) {
+ java.util.regex.Pattern titlePat = java.util.regex.Pattern.compile("缃戠害杞﹁涓歕\s*(\\d{4})\\s*骞碶\s*(\\d{1,2})\\s*鏈�");
+ java.util.regex.Pattern anyPat = java.util.regex.Pattern.compile("(\\d{4})\\s*骞碶\s*(\\d{1,2})\\s*鏈�");
+ String fallback = null;
+ int scanned = 0;
+ for (String text : paragraphs) {
+ if (text == null) continue;
+ String flat = text.replaceAll("\\s+", "");
+ if (flat.isEmpty()) continue;
+ java.util.regex.Matcher m = titlePat.matcher(flat);
+ if (m.find()) return ymd(m.group(1), m.group(2));
+ if (fallback == null && scanned < 10) {
+ java.util.regex.Matcher m2 = anyPat.matcher(flat);
+ if (m2.find()) fallback = ymd(m2.group(1), m2.group(2));
+ }
+ scanned++;
+ }
+ return fallback;
+ }
+
+ /** docx 娈佃惤鏂囨湰锛堜繚鎸佸師濮嬮『搴忥級 */
+ private List<String> paragraphsOfDocx(XWPFDocument doc) {
+ List<String> out = new ArrayList<>();
+ for (XWPFParagraph para : doc.getParagraphs()) out.add(para.getText());
+ return out;
+ }
+
+ /** Word 2003 XML锛圵ordML锛夋钀芥枃鏈細<w:p> 涓嬫墍鏈� <w:t> 鎷兼帴鍚庡帇鎺夌┖鐧� */
+ private List<String> paragraphsOfDocxXml(Document dom) {
+ List<String> out = new ArrayList<>();
+ for (Element p : elementsByLocalName(dom, "p")) out.add(textOfXmlElement(p));
+ return out;
+ }
+
+ /** 鏄惁涓� Word 2003 XML锛圵ordML 骞抽摵 XML锛夆�斺�旇繖绉嶆枃浠跺父琚敼鍚嶆垚 .docx */
+ private boolean looksLikeWordMl(byte[] bytes) {
+ int n = Math.min(bytes.length, 4096);
+ String head = new String(bytes, 0, n, java.nio.charset.StandardCharsets.UTF_8);
+ return head.contains("wordml") && head.contains("<w:wordDocument");
+ }
+
+ /** zip 鍖呭唴鏄惁鍚� word/document.xml锛堢敤浜庡尯鍒� docx 涓� xlsx/鍏跺畠 OPC 鍖咃級 */
+ private boolean isWordPackage(byte[] bytes) {
+ try (java.util.zip.ZipInputStream zin =
+ new java.util.zip.ZipInputStream(new java.io.ByteArrayInputStream(bytes))) {
+ java.util.zip.ZipEntry e;
+ while ((e = zin.getNextEntry()) != null) {
+ String n = e.getName();
+ if ("word/document.xml".equals(n)) return true;
+ }
+ } catch (Exception ignore) {
+ return false;
+ }
+ return false;
+ }
+
+ /** 鏄惁涓� OLE2 澶嶅悎鏂囨。锛圵ord 97-2003 .doc锛� */
+ private boolean isOle2(byte[] b) {
+ return b.length > 8 && (b[0] & 0xFF) == 0xD0 && (b[1] & 0xFF) == 0xCF
+ && (b[2] & 0xFF) == 0x11 && (b[3] & 0xFF) == 0xE0;
+ }
+
+ /** 瀹夊叏瑙ｆ瀽 XML锛氱鐢ㄥ閮ㄥ疄浣�/DTD锛堟姤鍛婃潵鑷閮ㄥ钩鍙帮紝闃� XXE锛� */
+ private Document parseXmlSafely(String xml) throws Exception {
+ javax.xml.parsers.DocumentBuilderFactory f = javax.xml.parsers.DocumentBuilderFactory.newInstance();
+ f.setNamespaceAware(true);
+ try { f.setFeature("http://apache.org/xml/features/nonvalidating/load-external-dtd", false); } catch (Exception ignore) { }
+ try { f.setFeature("http://xml.org/sax/features/external-general-entities", false); } catch (Exception ignore) { }
+ try { f.setFeature("http://xml.org/sax/features/external-parameter-entities", false); } catch (Exception ignore) { }
+ javax.xml.parsers.DocumentBuilder b = f.newDocumentBuilder();
+ b.setEntityResolver(new org.xml.sax.EntityResolver() {
+ @Override
+ public org.xml.sax.InputSource resolveEntity(String publicId, String systemId) {
+ return new org.xml.sax.InputSource(new java.io.StringReader(""));
+ }
+ });
+ return b.parse(new org.xml.sax.InputSource(new java.io.StringReader(xml)));
+ }
+
+ /** 鎸� localName 鍙栧叏閮ㄥ悗浠ｅ厓绱狅紙鍛藉悕绌洪棿鏃犲叧锛屽吋瀹� WordML 涓� docx 鐨� w: 鍓嶇紑锛� */
+ private List<Element> elementsByLocalName(Document dom, String localName) {
+ List<Element> out = new ArrayList<>();
+ NodeList list = dom.getElementsByTagNameNS("*", localName);
+ for (int i = 0; i < list.getLength(); i++) out.add((Element) list.item(i));
+ return out;
+ }
+
+ /** 鍙彇鐩存帴瀛愬厓绱狅紙閬垮厤宓屽琛ㄦ牸/宓屽灞炴�у厓绱犺绠楄繘鏉ワ級 */
+ private List<Element> childElementsByLocalName(Element parent, String localName) {
+ List<Element> out = new ArrayList<>();
+ NodeList kids = parent.getChildNodes();
+ for (int i = 0; i < kids.getLength(); i++) {
+ Node n = kids.item(i);
+ if (n.getNodeType() == Node.ELEMENT_NODE && localName.equals(n.getLocalName())) out.add((Element) n);
+ }
+ return out;
+ }
+
+ /** 鍏冪礌鍐呮墍鏈� <w:t> 鏂囨湰鎷兼帴骞跺帇鎺夌┖鐧� */
+ private String textOfXmlElement(Element el) {
+ StringBuilder sb = new StringBuilder();
+ NodeList ts = el.getElementsByTagNameNS("*", "t");
+ for (int i = 0; i < ts.getLength(); i++) sb.append(ts.item(i).getTextContent());
+ return flat(sb.toString());
+ }
+
+ private String ymd(String y, String mo) {
+ try {
+ int month = Integer.parseInt(mo);
+ if (month < 1 || month > 12) return null;
+ return String.format("%04d-%02d", Integer.parseInt(y), month);
+ } catch (NumberFormatException e) {
+ return null;
+ }
+ }
+
+ /** 瑙ｆ瀽鎶ュ憡涓殑銆屽湴甯傚悕绉� | 鈥� | 璁㈠崟鏁版�绘暟銆嶈〃锛宱rders=甯傚窞璁㈠崟锛宻umHolder[0]=鍚堣琛岃鍗曟暟 */
+ private void parseWycOrderReportTables(XWPFDocument doc, Map<String, Double> orders, double[] sumHolder) {
+ List<List<List<String>>> tables = new ArrayList<>();
+ for (XWPFTable table : doc.getTables()) {
+ List<List<String>> rows = new ArrayList<>();
+ for (XWPFTableRow row : table.getRows()) {
+ List<String> cells = new ArrayList<>();
+ for (org.apache.poi.xwpf.usermodel.XWPFTableCell c : row.getTableCells()) cells.add(flat(c.getText()));
+ rows.add(cells);
+ }
+ tables.add(rows);
+ }
+ matchWycOrderTable(tables, orders, sumHolder);
+ }
+
+ /** Word 2003 XML锛圵ordML锛夊垎鏀細<w:tbl>/<w:tr>/<w:tc> 鎶芥垚鏂囨湰琛ㄥ悗璧板悓涓�濂楀尮閰嶉�昏緫 */
+ private void parseWycOrderReportTablesXml(Document dom, Map<String, Double> orders, double[] sumHolder) {
+ List<List<List<String>>> tables = new ArrayList<>();
+ for (Element tbl : elementsByLocalName(dom, "tbl")) {
+ List<List<String>> rows = new ArrayList<>();
+ for (Element tr : childElementsByLocalName(tbl, "tr")) {
+ List<String> cells = new ArrayList<>();
+ for (Element tc : childElementsByLocalName(tr, "tc")) cells.add(textOfXmlElement(tc));
+ rows.add(cells);
+ }
+ tables.add(rows);
+ }
+ matchWycOrderTable(tables, orders, sumHolder);
+ }
+
+ /**
+ * 鍦ㄣ�屾枃鏈寲琛ㄦ牸銆嶉噷鎵鹃檮浠�1锛堣〃澶村惈銆岃鍗曟暟鎬绘暟銆�/銆岃鍗曟暟銆嶏級骞跺彇 17 甯傚窞璁㈠崟鏁颁笌鍚堣銆�
+ * 鍏ュ弬 tables = 琛� -> 琛� -> 鍗曞厓鏍兼枃鏈紙宸插帇绌虹櫧锛夛紝涓庢潵婧愭牸寮忔棤鍏炽��
+ */
+ private void matchWycOrderTable(List<List<List<String>>> tables, Map<String, Double> orders, double[] sumHolder) {
+ for (List<List<String>> rows : tables) {
+ if (rows.size() < 3) continue;
+ int headerRow = -1;
+ int orderCol = -1;
+ for (int r = 0; r < Math.min(rows.size(), 3); r++) {
+ List<String> cells = rows.get(r);
+ for (int c = 0; c < cells.size(); c++) {
+ String norm = cells.get(c);
+ if (norm.contains("璁㈠崟鏁版�绘暟") || "璁㈠崟鏁�".equals(norm)) {
+ headerRow = r;
+ orderCol = c;
+ break;
+ }
+ }
+ if (headerRow >= 0) break;
+ }
+ if (headerRow < 0 || orderCol < 0) continue;
+
+ Map<String, Double> found = new LinkedHashMap<>();
+ double citySum = 0.0;
+ boolean hasTotal = false;
+ for (int r = headerRow + 1; r < rows.size(); r++) {
+ List<String> cells = rows.get(r);
+ if (cells.isEmpty() || orderCol >= cells.size()) continue;
+ String name = cells.get(0);
+ if (name.isEmpty()) continue;
+ Double val = parsePlainNumber(cells.get(orderCol));
+ if (val == null) continue;
+ String city = RegionUtil.normalizeCityName(name);
+ if (city != null && RegionUtil.cityList().contains(city)) {
+ found.put(city, val);
+ citySum += val;
+ } else if (name.contains("鍚堣") || name.contains("鎬昏") || name.contains("鍏ㄧ渷") || name.contains("婀栧寳")) {
+ hasTotal = true;
+ sumHolder[0] = val;
+ }
+ }
+ if (!found.isEmpty()) {
+ orders.clear();
+ orders.putAll(found);
+ if (!hasTotal) sumHolder[0] = citySum;
+ return;
+ }
+ }
+ }
+
+ private String flat(String s) {
+ return s == null ? "" : s.replaceAll("\\s+", "").trim();
+ }
+
+ /** 銆�11,183,267 鍗曘�嶈繖绫绘枃鏈彇鏁板�� */
+ private Double parsePlainNumber(String s) {
+ if (s == null) return null;
+ String t = s.replaceAll("[,\\s]", "").trim();
+ if (t.isEmpty()) return null;
+ java.util.regex.Matcher m = java.util.regex.Pattern.compile("-?\\d+(?:\\.\\d+)?").matcher(t);
+ if (!m.find()) return null;
+ try {
+ return Double.parseDouble(m.group());
+ } catch (NumberFormatException e) {
+ return null;
+ }
+ }
+
+ private Double orderOf(Map<String, Double> orders, String city) {
+ Double v = orders.get(city);
+ return (v == null || v <= 0) ? null : v;
+ }
private String periodOfCell(Cell c) {
if (c == null) return null;
if (c.getCellType() == CellType.NUMERIC) {
@@ -3513,6 +4053,7 @@
case "scaleSplit": return new ImportResult(importScaleSplit(file, period), 0, new ArrayList<>());
case "wycTotal": return new ImportResult(importWycTotal(file, period), 0, new ArrayList<>());
case "wycOrder": return new ImportResult(importWycOrder(file, period), 0, new ArrayList<>());
+ case "wycOrderReport": return new ImportResult(importWycOrderReport(file, period).getCityCount(), 0, new ArrayList<>());
default: throw new RuntimeException("涓嶆敮鎸佺殑瀵煎叆绫诲瀷锛�" + type);
}
}
--
Gitblit v1.9.1