From f329f504040f3608478c687a99aa36a3cf5720b7 Mon Sep 17 00:00:00 2001
From: xyc <jc_xiong@hotmail.com>
Date: 星期六, 03 十月 2026 23:45:04 +0800
Subject: [PATCH] feat(observation): 新增观测站分日调查数据导入与明细查看
---
traffic-audit-server/src/main/java/com/trafficaudit/observation/service/ObservationStationImportService.java | 350 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 350 insertions(+), 0 deletions(-)
diff --git a/traffic-audit-server/src/main/java/com/trafficaudit/observation/service/ObservationStationImportService.java b/traffic-audit-server/src/main/java/com/trafficaudit/observation/service/ObservationStationImportService.java
new file mode 100644
index 0000000..643a784
--- /dev/null
+++ b/traffic-audit-server/src/main/java/com/trafficaudit/observation/service/ObservationStationImportService.java
@@ -0,0 +1,350 @@
+package com.trafficaudit.observation.service;
+
+import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
+import com.trafficaudit.observation.dto.ObservationDateSummary;
+import com.trafficaudit.observation.entity.ObservationStationFlow;
+import com.trafficaudit.observation.mapper.ObservationStationFlowMapper;
+import lombok.extern.slf4j.Slf4j;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.CellType;
+import org.apache.poi.ss.usermodel.DataFormatter;
+import org.apache.poi.ss.usermodel.DateUtil;
+import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.usermodel.Workbook;
+import org.apache.poi.ss.usermodel.WorkbookFactory;
+import org.springframework.stereotype.Service;
+import org.springframework.transaction.annotation.Transactional;
+import org.springframework.web.multipart.MultipartFile;
+
+import javax.annotation.Resource;
+import java.io.InputStream;
+import java.math.BigDecimal;
+import java.time.LocalDate;
+import java.time.format.DateTimeFormatter;
+import java.util.ArrayList;
+import java.util.LinkedHashMap;
+import java.util.LinkedHashSet;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+
+/**
+ * Imports the "observation station daily survey" workbook (.xls/.xlsx).
+ * Header names are matched by text, so column order may change between releases.
+ * Re-importing the same date replaces that date's rows.
+ */
+@Slf4j
+@Service
+public class ObservationStationImportService {
+
+ private static final String H_DATE = "瑙傛祴鏃ユ湡";
+ private static final String H_CODE = "瑙傛祴绔欑紪鍙�";
+ private static final String H_NAME = "瑙傛祴绔欏悕绉�";
+ private static final String H_MILEAGE = "瑙傛祴閲岀▼";
+ private static final String H_CITY = "鍦板競鍚嶇О";
+ private static final String H_COUNTY = "鍖哄幙鍚嶇О";
+ private static final String H_DIRECTION = "琛岄┒鏂瑰悜";
+ private static final String H_SMALL_MEDIUM_PASSENGER = "涓皬瀹㈡祦閲�";
+ private static final String H_LARGE_PASSENGER = "澶у杞︽祦閲�";
+ private static final String H_SMALL_TRUCK = "灏忚揣杞︽祦閲�";
+ private static final String H_MEDIUM_TRUCK = "涓揣杞︽祦閲�";
+ private static final String H_LARGE_TRUCK = "澶ц揣杞︽祦閲�";
+ private static final String H_EXTRA_LARGE_TRUCK = "鐗瑰ぇ璐ф祦閲�";
+ private static final String H_CONTAINER = "闆嗚绠辨祦閲�";
+ private static final String H_MOTORCYCLE = "鎽╂墭杞︽祦閲�";
+ private static final String H_TRACTOR = "鎷栨媺鏈烘祦閲�";
+ private static final String H_PASSENGER_TOTAL = "瀹㈣溅娴侀噺";
+ private static final String H_TRUCK_TOTAL = "璐ц溅娴侀噺";
+ private static final String H_CAR_TOTAL = "姹借溅娴侀噺";
+ private static final String H_MOTOR_TOTAL = "鏈哄姩杞︽祦閲�";
+ private static final String H_PASSENGER_EQUIVALENT = "瀹㈣溅褰撻噺";
+ private static final String H_TRUCK_EQUIVALENT = "璐ц溅褰撻噺";
+ private static final String H_CAR_EQUIVALENT = "姹借溅褰撻噺";
+ private static final String H_MOTOR_EQUIVALENT = "鏈哄姩杞﹀綋閲�";
+ private static final String H_SPEED = "鏈哄姩杞﹂�熷害";
+ private static final String H_CONGESTION = "鎷ユ尋搴�";
+
+ private static final int HEADER_SCAN_ROWS = 10;
+ private static final int MAX_REPORTED_ERRORS = 50;
+
+ private static final DateTimeFormatter[] DATE_FORMATS = {
+ DateTimeFormatter.ofPattern("yyyy-M-d"),
+ DateTimeFormatter.ofPattern("yyyy/M/d"),
+ DateTimeFormatter.ofPattern("yyyy.M.d"),
+ DateTimeFormatter.ofPattern("yyyyMMdd")
+ };
+
+ @Resource
+ private ObservationStationFlowMapper stationFlowMapper;
+
+ @Transactional
+ public Map<String, Object> importWorkbook(MultipartFile file) {
+ if (file == null || file.isEmpty()) {
+ throw new IllegalArgumentException("璇烽�夋嫨瑕佸鍏ョ殑 Excel 鏂囦欢");
+ }
+ String sourceFile = file.getOriginalFilename();
+ List<ObservationStationFlow> rows = new ArrayList<>();
+ Set<LocalDate> dates = new LinkedHashSet<>();
+ Set<String> stationCodes = new LinkedHashSet<>();
+ List<String> errors = new ArrayList<>();
+ int skipped = 0;
+
+ DataFormatter formatter = new DataFormatter();
+ try (InputStream in = file.getInputStream(); Workbook workbook = WorkbookFactory.create(in)) {
+ Sheet sheet = workbook.getNumberOfSheets() > 0 ? workbook.getSheetAt(0) : null;
+ if (sheet == null) {
+ throw new IllegalArgumentException("宸ヤ綔绨夸腑娌℃湁鍙鍙栫殑宸ヤ綔琛�");
+ }
+ int headerRowIndex = findHeaderRow(sheet, formatter);
+ if (headerRowIndex < 0) {
+ throw new IllegalArgumentException("鏈壘鍒拌〃澶达紝璇风‘璁ゆ枃浠跺寘鍚玕"瑙傛祴鏃ユ湡\"鍜孿"瑙傛祴绔欑紪鍙穃"涓ゅ垪");
+ }
+ Map<String, Integer> headerIndex = buildHeaderIndex(sheet.getRow(headerRowIndex), formatter);
+
+ for (int r = headerRowIndex + 1; r <= sheet.getLastRowNum(); r++) {
+ Row row = sheet.getRow(r);
+ if (row == null) {
+ skipped++;
+ continue;
+ }
+ String code = stringCell(row, headerIndex, H_CODE, formatter);
+ LocalDate obsDate = dateCell(row, headerIndex, H_DATE, formatter);
+ if (isBlank(code) && obsDate == null) {
+ skipped++;
+ continue;
+ }
+ int rowNo = r + 1;
+ if (obsDate == null) {
+ addError(errors, "绗�" + rowNo + "琛岋細瑙傛祴鏃ユ湡鏃犳硶璇嗗埆");
+ continue;
+ }
+ if (isBlank(code)) {
+ addError(errors, "绗�" + rowNo + "琛岋細瑙傛祴绔欑紪鍙蜂负绌�");
+ continue;
+ }
+ ObservationStationFlow entity = new ObservationStationFlow();
+ entity.setObsDate(obsDate);
+ entity.setStationCode(code);
+ entity.setStationName(stringCell(row, headerIndex, H_NAME, formatter));
+ entity.setMileage(decimalCell(row, headerIndex, H_MILEAGE, formatter));
+ entity.setCityName(stringCell(row, headerIndex, H_CITY, formatter));
+ entity.setCountyName(stringCell(row, headerIndex, H_COUNTY, formatter));
+ String direction = stringCell(row, headerIndex, H_DIRECTION, formatter);
+ entity.setDirection(direction == null ? "" : direction);
+ entity.setSmallMediumPassenger(decimalCell(row, headerIndex, H_SMALL_MEDIUM_PASSENGER, formatter));
+ entity.setLargePassenger(decimalCell(row, headerIndex, H_LARGE_PASSENGER, formatter));
+ entity.setSmallTruck(decimalCell(row, headerIndex, H_SMALL_TRUCK, formatter));
+ entity.setMediumTruck(decimalCell(row, headerIndex, H_MEDIUM_TRUCK, formatter));
+ entity.setLargeTruck(decimalCell(row, headerIndex, H_LARGE_TRUCK, formatter));
+ entity.setExtraLargeTruck(decimalCell(row, headerIndex, H_EXTRA_LARGE_TRUCK, formatter));
+ entity.setContainerTruck(decimalCell(row, headerIndex, H_CONTAINER, formatter));
+ entity.setMotorcycle(decimalCell(row, headerIndex, H_MOTORCYCLE, formatter));
+ entity.setTractor(decimalCell(row, headerIndex, H_TRACTOR, formatter));
+ entity.setPassengerTotal(decimalCell(row, headerIndex, H_PASSENGER_TOTAL, formatter));
+ entity.setTruckTotal(decimalCell(row, headerIndex, H_TRUCK_TOTAL, formatter));
+ entity.setCarTotal(decimalCell(row, headerIndex, H_CAR_TOTAL, formatter));
+ entity.setMotorTotal(decimalCell(row, headerIndex, H_MOTOR_TOTAL, formatter));
+ entity.setPassengerEquivalent(decimalCell(row, headerIndex, H_PASSENGER_EQUIVALENT, formatter));
+ entity.setTruckEquivalent(decimalCell(row, headerIndex, H_TRUCK_EQUIVALENT, formatter));
+ entity.setCarEquivalent(decimalCell(row, headerIndex, H_CAR_EQUIVALENT, formatter));
+ entity.setMotorEquivalent(decimalCell(row, headerIndex, H_MOTOR_EQUIVALENT, formatter));
+ entity.setVehicleSpeed(decimalCell(row, headerIndex, H_SPEED, formatter));
+ entity.setCongestionDegree(decimalCell(row, headerIndex, H_CONGESTION, formatter));
+ entity.setSourceFile(sourceFile);
+ rows.add(entity);
+ dates.add(obsDate);
+ stationCodes.add(code);
+ }
+ } catch (IllegalArgumentException e) {
+ throw e;
+ } catch (Exception e) {
+ throw new IllegalArgumentException("宸ヤ綔绨胯В鏋愬け璐�: " + e.getMessage(), e);
+ }
+
+ if (!rows.isEmpty()) {
+ stationFlowMapper.delete(new LambdaQueryWrapper<ObservationStationFlow>()
+ .in(ObservationStationFlow::getObsDate, dates));
+ for (ObservationStationFlow row : rows) {
+ stationFlowMapper.insert(row);
+ }
+ }
+
+ List<String> dateTexts = new ArrayList<>();
+ for (LocalDate date : dates) {
+ dateTexts.add(date.toString());
+ }
+ BigDecimal smallMediumTotal = BigDecimal.ZERO;
+ for (ObservationStationFlow row : rows) {
+ if (row.getSmallMediumPassenger() != null) {
+ smallMediumTotal = smallMediumTotal.add(row.getSmallMediumPassenger());
+ }
+ }
+
+ Map<String, Object> summary = new LinkedHashMap<>();
+ summary.put("totalRows", rows.size());
+ summary.put("stationCount", stationCodes.size());
+ summary.put("dates", dateTexts);
+ summary.put("skippedRows", skipped);
+ summary.put("smallMediumPassengerTotal", smallMediumTotal);
+ summary.put("errors", errors);
+ log.info("Observation station import done: file={} rows={} dates={} skipped={} failed={}",
+ sourceFile, rows.size(), dateTexts, skipped, errors.size());
+ return summary;
+ }
+
+ public List<ObservationDateSummary> listDateSummaries() {
+ return stationFlowMapper.selectDateSummary();
+ }
+
+ public List<ObservationStationFlow> listRows(LocalDate date, int limit) {
+ int effectiveLimit = limit <= 0 ? 2000 : Math.min(limit, 5000);
+ return stationFlowMapper.selectList(new LambdaQueryWrapper<ObservationStationFlow>()
+ .eq(date != null, ObservationStationFlow::getObsDate, date)
+ .orderByAsc(ObservationStationFlow::getId)
+ .last("LIMIT " + effectiveLimit));
+ }
+
+ public int deleteDate(LocalDate date) {
+ if (date == null) {
+ throw new IllegalArgumentException("璇烽�夋嫨瑕佸垹闄ょ殑瑙傛祴鏃ユ湡");
+ }
+ return stationFlowMapper.delete(new LambdaQueryWrapper<ObservationStationFlow>()
+ .eq(ObservationStationFlow::getObsDate, date));
+ }
+
+ private void addError(List<String> errors, String message) {
+ if (errors.size() < MAX_REPORTED_ERRORS) {
+ errors.add(message);
+ }
+ }
+
+ private int findHeaderRow(Sheet sheet, DataFormatter formatter) {
+ int lastRow = Math.min(sheet.getLastRowNum(), HEADER_SCAN_ROWS);
+ for (int r = 0; r <= lastRow; r++) {
+ Row row = sheet.getRow(r);
+ if (row == null) {
+ continue;
+ }
+ Map<String, Integer> index = buildHeaderIndex(row, formatter);
+ if (index.containsKey(normalize(H_DATE)) && index.containsKey(normalize(H_CODE))) {
+ return r;
+ }
+ }
+ return -1;
+ }
+
+ private Map<String, Integer> buildHeaderIndex(Row row, DataFormatter formatter) {
+ Map<String, Integer> index = new LinkedHashMap<>();
+ if (row == null) {
+ return index;
+ }
+ for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
+ Cell cell = row.getCell(c);
+ String header = normalize(formatter.formatCellValue(cell));
+ if (!header.isEmpty() && !index.containsKey(header)) {
+ index.put(header, c);
+ }
+ }
+ return index;
+ }
+
+ private String normalize(String value) {
+ if (value == null) {
+ return "";
+ }
+ StringBuilder builder = new StringBuilder(value.length());
+ for (int i = 0; i < value.length(); i++) {
+ char ch = value.charAt(i);
+ if (ch == 0xFEFF || Character.isWhitespace(ch)) {
+ continue;
+ }
+ builder.append(ch);
+ }
+ return builder.toString();
+ }
+
+ private String stringCell(Row row, Map<String, Integer> headerIndex, String header, DataFormatter formatter) {
+ Integer column = headerIndex.get(normalize(header));
+ if (column == null) {
+ return null;
+ }
+ Cell cell = row.getCell(column);
+ if (cell == null) {
+ return null;
+ }
+ String value = formatter.formatCellValue(cell).trim();
+ return value.isEmpty() ? null : value;
+ }
+
+ private LocalDate dateCell(Row row, Map<String, Integer> headerIndex, String header, DataFormatter formatter) {
+ Integer column = headerIndex.get(normalize(header));
+ if (column == null) {
+ return null;
+ }
+ return readDate(row.getCell(column), formatter);
+ }
+
+ private BigDecimal decimalCell(Row row, Map<String, Integer> headerIndex, String header, DataFormatter formatter) {
+ Integer column = headerIndex.get(normalize(header));
+ if (column == null) {
+ return null;
+ }
+ return readDecimal(row.getCell(column), formatter);
+ }
+
+ private LocalDate readDate(Cell cell, DataFormatter formatter) {
+ if (cell == null) {
+ return null;
+ }
+ CellType type = cell.getCellType();
+ if (type == CellType.FORMULA) {
+ type = cell.getCachedFormulaResultType();
+ }
+ if (type == CellType.NUMERIC && DateUtil.isCellDateFormatted(cell)) {
+ return cell.getLocalDateTimeCellValue().toLocalDate();
+ }
+ String text = formatter.formatCellValue(cell).trim();
+ if (text.isEmpty()) {
+ return null;
+ }
+ text = text.replace("骞�", "-").replace("鏈�", "-").replace("鏃�", "").trim();
+ for (DateTimeFormatter format : DATE_FORMATS) {
+ try {
+ return LocalDate.parse(text, format);
+ } catch (Exception ignored) {
+ // Try the next supported pattern.
+ }
+ }
+ return null;
+ }
+
+ private BigDecimal readDecimal(Cell cell, DataFormatter formatter) {
+ if (cell == null) {
+ return null;
+ }
+ CellType type = cell.getCellType();
+ if (type == CellType.FORMULA) {
+ type = cell.getCachedFormulaResultType();
+ }
+ if (type == CellType.NUMERIC) {
+ return BigDecimal.valueOf(cell.getNumericCellValue());
+ }
+ if (type == CellType.BLANK) {
+ return null;
+ }
+ String text = formatter.formatCellValue(cell).trim().replace(",", "").replace(" ", "");
+ if (text.isEmpty() || "-".equals(text) || "鈥�".equals(text) || "/".equals(text)) {
+ return null;
+ }
+ try {
+ return new BigDecimal(text);
+ } catch (NumberFormatException e) {
+ return null;
+ }
+ }
+
+ private boolean isBlank(String value) {
+ return value == null || value.trim().isEmpty();
+ }
+}
--
Gitblit v1.9.1