From 37f52097847f9025589c79034be2454432c55433 Mon Sep 17 00:00:00 2001
From: xyc <jc_xiong@hotmail.com>
Date: 星期五, 02 十月 2026 10:12:27 +0800
Subject: [PATCH] docs(交接): 记录关机前开发服务已停止
---
traffic-audit-server/src/main/java/com/trafficaudit/holiday/service/HolidayWorkbookService.java | 880 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 880 insertions(+), 0 deletions(-)
diff --git a/traffic-audit-server/src/main/java/com/trafficaudit/holiday/service/HolidayWorkbookService.java b/traffic-audit-server/src/main/java/com/trafficaudit/holiday/service/HolidayWorkbookService.java
new file mode 100644
index 0000000..3fc5d2e
--- /dev/null
+++ b/traffic-audit-server/src/main/java/com/trafficaudit/holiday/service/HolidayWorkbookService.java
@@ -0,0 +1,880 @@
+package com.trafficaudit.holiday.service;
+
+import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
+import com.trafficaudit.holiday.HolidayConstants;
+import com.trafficaudit.holiday.HolidayNumberUtils;
+import com.trafficaudit.holiday.entity.HolidayCalendar;
+import com.trafficaudit.holiday.entity.HolidayPassengerCoefficient;
+import com.trafficaudit.holiday.entity.HolidayPassengerFlow;
+import com.trafficaudit.holiday.entity.HolidayVehicleFlow;
+import com.trafficaudit.holiday.mapper.HolidayPassengerCoefficientMapper;
+import com.trafficaudit.holiday.mapper.HolidayPassengerFlowMapper;
+import com.trafficaudit.holiday.mapper.HolidayVehicleFlowMapper;
+import lombok.Data;
+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.FormulaEvaluator;
+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.apache.poi.xssf.usermodel.XSSFWorkbook;
+import org.springframework.beans.factory.annotation.Value;
+import org.springframework.stereotype.Service;
+import org.springframework.transaction.annotation.Transactional;
+import org.springframework.web.multipart.MultipartFile;
+
+import javax.annotation.Resource;
+import java.io.ByteArrayOutputStream;
+import java.io.File;
+import java.io.FileInputStream;
+import java.io.InputStream;
+import java.math.BigDecimal;
+import java.time.LocalDate;
+import java.time.LocalDateTime;
+import java.time.format.DateTimeParseException;
+import java.util.ArrayList;
+import java.util.LinkedHashMap;
+import java.util.List;
+import java.util.Map;
+
+@Slf4j
+@Service
+public class HolidayWorkbookService {
+
+ private static final Map<String, String> PASSENGER_SHEET_MODE = new LinkedHashMap<>();
+ private static final int FIRST_DATA_ROW_INDEX = 2;
+ private static final int[] REFERENCE_YEARS = {2024, 2025, 2026};
+
+ static {
+ PASSENGER_SHEET_MODE.put("閾佽矾", HolidayConstants.MODE_RAIL);
+ PASSENGER_SHEET_MODE.put("姘戣埅", HolidayConstants.MODE_CIVIL_AVIATION);
+ PASSENGER_SHEET_MODE.put("閬撹矾", HolidayConstants.MODE_ROAD);
+ PASSENGER_SHEET_MODE.put("姘磋矾", HolidayConstants.MODE_WATER);
+ }
+
+ @Resource
+ private HolidayConfigService configService;
+ @Resource
+ private HolidayPassengerFlowMapper passengerFlowMapper;
+ @Resource
+ private HolidayVehicleFlowMapper vehicleFlowMapper;
+ @Resource
+ private HolidayPassengerCoefficientMapper coefficientMapper;
+
+ @Value("${holiday.reference-path:docs/涓撻」缁熻/閲嶈鑺傚亣鏃�/2026骞村崄涓�鏁版嵁.xlsx}")
+ private String referencePath;
+
+ public File resolveReferenceFile() {
+ List<File> candidates = new ArrayList<>();
+ File configured = new File(referencePath);
+ candidates.add(configured);
+ if (!configured.isAbsolute()) {
+ candidates.add(new File(System.getProperty("user.dir"), referencePath));
+ candidates.add(new File(System.getProperty("user.dir"), "../" + referencePath));
+ }
+ for (File candidate : candidates) {
+ try {
+ if (candidate.exists() && candidate.isFile()) {
+ return candidate.getCanonicalFile();
+ }
+ } catch (Exception ignored) {
+ // Keep checking the remaining candidate paths.
+ }
+ }
+ return configured;
+ }
+
+ public String referenceFileName() {
+ return resolveReferenceFile().getName();
+ }
+
+ @Transactional
+ public ImportSummary importWorkbook(MultipartFile file, int year, String holidayType, boolean allowHistoricalOverwrite) {
+ if (file == null || file.isEmpty()) {
+ throw new IllegalArgumentException("璇烽�夋嫨瑕佸鍏ョ殑 Excel 鏂囦欢");
+ }
+ List<HolidayCalendar> holidayDays = configService.findHolidayDays(year, holidayType);
+ if (holidayDays.isEmpty()) {
+ throw new IllegalArgumentException("鎵�閫夊勾浠藉拰鑺傚亣鏃ュ皻鏈厤缃棩鏈燂紝璇峰厛鍦ㄧ郴缁熼厤缃腑缁存姢鑺傚亣鏃�");
+ }
+ Map<Integer, LocalDate> dateByDay = new LinkedHashMap<>();
+ for (HolidayCalendar day : holidayDays) {
+ dateByDay.put(day.getDayIndex(), day.getHolidayDate());
+ }
+
+ if (!allowHistoricalOverwrite) {
+ ImportSummary historicalSummary = detectHistoricalChanges(file, year, dateByDay);
+ if (historicalSummary.isConfirmationRequired()) {
+ return historicalSummary;
+ }
+ }
+
+ ImportSummary summary = new ImportSummary();
+ try (InputStream in = file.getInputStream(); Workbook workbook = WorkbookFactory.create(in)) {
+ FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
+ importPassengerSheets(workbook, evaluator, year, dateByDay, summary);
+ importVehicleSheets(workbook, evaluator, year, dateByDay, summary);
+ } catch (Exception e) {
+ throw new IllegalArgumentException("宸ヤ綔绨胯В鏋愬け璐�: " + e.getMessage(), e);
+ }
+ return summary;
+ }
+
+ private ImportSummary detectHistoricalChanges(MultipartFile file, int year, Map<Integer, LocalDate> dateByDay) {
+ ImportSummary summary = new ImportSummary();
+ try (InputStream in = file.getInputStream(); Workbook workbook = WorkbookFactory.create(in)) {
+ FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
+ int latestDay = findLatestIncomingDay(workbook, evaluator, year, dateByDay);
+ if (latestDay <= 1) {
+ return summary;
+ }
+ Map<LocalDate, Map<String, BigDecimal>> existingPassenger = loadPassengerByDate(year);
+ Map<LocalDate, Map<String, BigDecimal>> existingVehicle = loadVehicleByDate(year);
+ detectPassengerHistory(workbook, evaluator, year, dateByDay, latestDay, existingPassenger, summary);
+ detectVehicleHistory(workbook, evaluator, year, dateByDay, latestDay, existingVehicle, summary);
+ if (!summary.getHistoricalChanges().isEmpty()) {
+ summary.setConfirmationRequired(true);
+ }
+ } catch (Exception e) {
+ throw new IllegalArgumentException("宸ヤ綔绨胯В鏋愬け璐�: " + e.getMessage(), e);
+ }
+ return summary;
+ }
+
+ private int findLatestIncomingDay(Workbook workbook, FormulaEvaluator evaluator, int year, Map<Integer, LocalDate> dateByDay) {
+ int latest = 0;
+ for (Integer dayIndex : dateByDay.keySet()) {
+ if (hasPassengerValue(workbook, evaluator, year, dayIndex)
+ || hasVehicleValue(workbook, evaluator, year, dayIndex)) {
+ latest = Math.max(latest, dayIndex);
+ }
+ }
+ return latest;
+ }
+
+ private boolean hasPassengerValue(Workbook workbook, FormulaEvaluator evaluator, int year, int dayIndex) {
+ for (Map.Entry<String, String> entry : PASSENGER_SHEET_MODE.entrySet()) {
+ Sheet sheet = workbook.getSheet(entry.getKey());
+ if (sheet == null) continue;
+ Integer yearColumn = findYearColumn(sheet, evaluator, year, 2, 7);
+ if (yearColumn == null) continue;
+ Row row = sheet.getRow(FIRST_DATA_ROW_INDEX + dayIndex - 1);
+ if (row == null) continue;
+ BigDecimal value = numberValue(row.getCell(yearColumn), evaluator);
+ if (value != null) return true;
+ }
+ return false;
+ }
+
+ private boolean hasVehicleValue(Workbook workbook, FormulaEvaluator evaluator, int year, int dayIndex) {
+ Sheet expressway = workbook.getSheet("楂橀�熷叕璺�");
+ if (expressway != null) {
+ Integer yearColumn = findYearColumn(expressway, evaluator, year, 2, 7);
+ if (yearColumn != null) {
+ Row row = expressway.getRow(FIRST_DATA_ROW_INDEX + dayIndex - 1);
+ if (row != null && numberValue(row.getCell(yearColumn), evaluator) != null) return true;
+ }
+ }
+ Sheet nationalProvincial = workbook.getSheet("鏅�氬浗鐪侀亾");
+ if (nationalProvincial != null) {
+ Integer yearColumn = findYearColumn(nationalProvincial, evaluator, year, 3, 8);
+ if (yearColumn != null) {
+ Row row = nationalProvincial.getRow(FIRST_DATA_ROW_INDEX + dayIndex - 1);
+ if (row != null && numberValue(row.getCell(yearColumn), evaluator) != null) return true;
+ }
+ }
+ return false;
+ }
+
+ private void detectPassengerHistory(Workbook workbook, FormulaEvaluator evaluator, int year,
+ Map<Integer, LocalDate> dateByDay, int latestDay,
+ Map<LocalDate, Map<String, BigDecimal>> existing,
+ ImportSummary summary) {
+ for (Map.Entry<String, String> entry : PASSENGER_SHEET_MODE.entrySet()) {
+ Sheet sheet = workbook.getSheet(entry.getKey());
+ if (sheet == null) continue;
+ Integer yearColumn = findYearColumn(sheet, evaluator, year, 2, 7);
+ if (yearColumn == null) continue;
+ for (Map.Entry<Integer, LocalDate> day : dateByDay.entrySet()) {
+ if (day.getKey() >= latestDay) continue;
+ Row row = sheet.getRow(FIRST_DATA_ROW_INDEX + day.getKey() - 1);
+ if (row == null) continue;
+ BigDecimal incoming = numberValue(row.getCell(yearColumn), evaluator);
+ if (incoming == null) continue;
+ Map<String, BigDecimal> existingDay = existing.get(day.getValue());
+ BigDecimal oldValue = existingDay == null ? null : existingDay.get(entry.getValue());
+ if (oldValue != null && !sameValue(oldValue, incoming)) {
+ summary.addHistoricalChange("绗�" + day.getKey() + "鏃� " + day.getValue() + " "
+ + entry.getKey() + "锛氬師鍊� " + formatChangeValue(oldValue) + "锛屽鍏ュ�� " + formatChangeValue(incoming));
+ }
+ }
+ }
+ }
+
+ private void detectVehicleHistory(Workbook workbook, FormulaEvaluator evaluator, int year,
+ Map<Integer, LocalDate> dateByDay, int latestDay,
+ Map<LocalDate, Map<String, BigDecimal>> existing,
+ ImportSummary summary) {
+ detectVehicleSheetHistory(workbook, evaluator, year, dateByDay, latestDay, existing, summary,
+ "楂橀�熷叕璺�", HolidayConstants.ROAD_EXPRESSWAY, 2, 7);
+ detectVehicleSheetHistory(workbook, evaluator, year, dateByDay, latestDay, existing, summary,
+ "鏅�氬浗鐪侀亾", HolidayConstants.ROAD_NATIONAL_PROVINCIAL, 3, 8);
+ detectComparisonBaseHistory(workbook, evaluator, year, dateByDay, latestDay, existing, summary);
+ }
+
+ private void detectVehicleSheetHistory(Workbook workbook, FormulaEvaluator evaluator, int year,
+ Map<Integer, LocalDate> dateByDay, int latestDay,
+ Map<LocalDate, Map<String, BigDecimal>> existing,
+ ImportSummary summary, String sheetName, String roadCategory,
+ int startColumn, int endColumn) {
+ Sheet sheet = workbook.getSheet(sheetName);
+ if (sheet == null) return;
+ Integer yearColumn = findYearColumn(sheet, evaluator, year, startColumn, endColumn);
+ if (yearColumn == null) return;
+ for (Map.Entry<Integer, LocalDate> day : dateByDay.entrySet()) {
+ if (day.getKey() >= latestDay) continue;
+ Row row = sheet.getRow(FIRST_DATA_ROW_INDEX + day.getKey() - 1);
+ if (row == null) continue;
+ BigDecimal incoming = numberValue(row.getCell(yearColumn), evaluator);
+ if (incoming == null) continue;
+ Map<String, BigDecimal> existingDay = existing.get(day.getValue());
+ BigDecimal oldValue = existingDay == null ? null : existingDay.get(roadCategory);
+ if (oldValue != null && !sameValue(oldValue, incoming)) {
+ summary.addHistoricalChange("绗�" + day.getKey() + "鏃� " + day.getValue() + " "
+ + sheetName + "锛氬師鍊� " + formatChangeValue(oldValue) + "锛屽鍏ュ�� " + formatChangeValue(incoming));
+ }
+ }
+ }
+
+ private void detectComparisonBaseHistory(Workbook workbook, FormulaEvaluator evaluator, int year,
+ Map<Integer, LocalDate> dateByDay, int latestDay,
+ Map<LocalDate, Map<String, BigDecimal>> existingVehicle,
+ ImportSummary summary) {
+ if (!HolidayConstants.supportsComparisonBase(year)) return;
+ Sheet sheet = workbook.getSheet("鏅�氬浗鐪侀亾");
+ if (sheet == null) return;
+ Map<LocalDate, BigDecimal> existingBase = loadComparisonBaseByDate(year);
+ for (Map.Entry<Integer, LocalDate> day : dateByDay.entrySet()) {
+ if (day.getKey() >= latestDay) continue;
+ Map<String, BigDecimal> existingDay = existingVehicle.get(day.getValue());
+ if (existingDay == null || existingDay.get(HolidayConstants.ROAD_NATIONAL_PROVINCIAL) == null) continue;
+ Row row = sheet.getRow(FIRST_DATA_ROW_INDEX + day.getKey() - 1);
+ if (row == null) continue;
+ BigDecimal incoming;
+ try {
+ incoming = numberValue(row.getCell(2), evaluator);
+ } catch (Exception e) {
+ summary.addError("鏅�氬浗鐪侀亾 绗�" + (row.getRowNum() + 1) + "琛岀幆姣斿熀鏈燂細" + e.getMessage());
+ continue;
+ }
+ BigDecimal oldValue = existingBase.get(day.getValue());
+ if (!sameNullableValue(oldValue, incoming)) {
+ summary.addHistoricalChange("绗�" + day.getKey() + "鏃� " + day.getValue() + " 鏅�氬浗鐪侀亾鐜瘮鍩烘湡锛氬師鍊� "
+ + nullableChangeValue(oldValue) + "锛屽鍏ュ�� " + nullableChangeValue(incoming));
+ }
+ }
+ }
+
+ Map<LocalDate, Map<String, BigDecimal>> result = new LinkedHashMap<>();
+ private Map<LocalDate, Map<String, BigDecimal>> loadPassengerByDate(int year) {
+ List<HolidayPassengerFlow> rows = passengerFlowMapper.selectList(
+ new LambdaQueryWrapper<HolidayPassengerFlow>().eq(HolidayPassengerFlow::getYear, year));
+ for (HolidayPassengerFlow row : rows) {
+ result.computeIfAbsent(row.getFlowDate(), key -> new LinkedHashMap<>())
+ .put(row.getTransportMode(), row.getPassengerCount());
+ }
+ return result;
+ }
+
+ private Map<LocalDate, Map<String, BigDecimal>> loadVehicleByDate(int year) {
+ Map<LocalDate, Map<String, BigDecimal>> result = new LinkedHashMap<>();
+ List<HolidayVehicleFlow> rows = vehicleFlowMapper.selectList(
+ new LambdaQueryWrapper<HolidayVehicleFlow>().eq(HolidayVehicleFlow::getYear, year));
+ for (HolidayVehicleFlow row : rows) {
+ result.computeIfAbsent(row.getFlowDate(), key -> new LinkedHashMap<>())
+ .put(row.getRoadCategory(), row.getVehicleCount());
+ }
+ return result;
+ }
+
+ private Map<LocalDate, BigDecimal> loadComparisonBaseByDate(int year) {
+ Map<LocalDate, BigDecimal> result = new LinkedHashMap<>();
+ List<HolidayVehicleFlow> rows = vehicleFlowMapper.selectList(
+ new LambdaQueryWrapper<HolidayVehicleFlow>()
+ .eq(HolidayVehicleFlow::getYear, year)
+ .eq(HolidayVehicleFlow::getRoadCategory, HolidayConstants.ROAD_NATIONAL_PROVINCIAL));
+ for (HolidayVehicleFlow row : rows) result.put(row.getFlowDate(), row.getComparisonBase());
+ return result;
+ }
+
+ private boolean sameValue(BigDecimal left, BigDecimal right) {
+ return HolidayNumberUtils.sameValue(left, right);
+ }
+
+ private boolean sameNullableValue(BigDecimal left, BigDecimal right) {
+ if (left == null || right == null) return left == right;
+ return sameValue(left, right);
+ }
+
+ private String nullableChangeValue(BigDecimal value) {
+ return value == null ? "绌�" : formatChangeValue(value);
+ }
+
+ private String formatChangeValue(BigDecimal value) {
+ return HolidayNumberUtils.changeText(value);
+ }
+
+ @Transactional
+ public void seedReferenceDataIfNeeded() {
+ File reference = resolveReferenceFile();
+ if (!reference.exists() || !reference.isFile()) {
+ log.warn("Holiday reference workbook not found: {}", reference.getAbsolutePath());
+ return;
+ }
+ try (InputStream in = new FileInputStream(reference); Workbook workbook = WorkbookFactory.create(in)) {
+ FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
+ seedNationalDayCalendar();
+ for (int year : REFERENCE_YEARS) {
+ if (passengerFlowMapper.selectCount(new LambdaQueryWrapper<HolidayPassengerFlow>()
+ .eq(HolidayPassengerFlow::getYear, year)) == 0) {
+ seedPassengerYear(workbook, evaluator, year);
+ }
+ if (vehicleFlowMapper.selectCount(new LambdaQueryWrapper<HolidayVehicleFlow>()
+ .eq(HolidayVehicleFlow::getYear, year)) == 0) {
+ seedVehicleYear(workbook, evaluator, year);
+ }
+ if (coefficientMapper.selectCount(new LambdaQueryWrapper<HolidayPassengerCoefficient>()
+ .eq(HolidayPassengerCoefficient::getYear, year)) == 0) {
+ seedCoefficientYear(workbook, evaluator, year);
+ }
+ }
+ } catch (Exception e) {
+ log.error("Failed to seed holiday reference data", e);
+ }
+ }
+
+ public byte[] exportWorkbook(int year, String holidayType) {
+ File reference = resolveReferenceFile();
+ if (!reference.exists() || !reference.isFile()) {
+ throw new IllegalArgumentException("鑺傚亣鏃ユā鏉挎枃浠朵笉瀛樺湪: " + reference.getAbsolutePath());
+ }
+ try (InputStream in = new FileInputStream(reference); XSSFWorkbook workbook = new XSSFWorkbook(in)) {
+ FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
+ fillPassengerSheets(workbook, evaluator, year, holidayType);
+ fillVehicleSheets(workbook, evaluator, year, holidayType);
+ fillCoefficients(workbook, evaluator, year, holidayType);
+ workbook.setForceFormulaRecalculation(true);
+ try {
+ evaluator.evaluateAll();
+ } catch (Exception e) {
+ log.warn("Workbook formula recalculation did not complete; Excel will recalculate on open", e);
+ }
+ ByteArrayOutputStream out = new ByteArrayOutputStream();
+ workbook.write(out);
+ return out.toByteArray();
+ } catch (Exception e) {
+ throw new IllegalArgumentException("鑺傚亣鏃ユ暟鎹鍑哄け璐�: " + e.getMessage(), e);
+ }
+ }
+
+ private void importPassengerSheets(Workbook workbook, FormulaEvaluator evaluator, int year,
+ Map<Integer, LocalDate> dateByDay, ImportSummary summary) {
+ for (Map.Entry<String, String> entry : PASSENGER_SHEET_MODE.entrySet()) {
+ Sheet sheet = workbook.getSheet(entry.getKey());
+ if (sheet == null) {
+ summary.addError(entry.getKey() + "锛氭壘涓嶅埌宸ヤ綔琛�");
+ continue;
+ }
+ Integer yearColumn = findYearColumn(sheet, evaluator, year, 2, 7);
+ if (yearColumn == null) {
+ summary.addError(entry.getKey() + "锛氭壘涓嶅埌 " + year + " 骞存暟鎹垪");
+ continue;
+ }
+ for (Map.Entry<Integer, LocalDate> day : dateByDay.entrySet()) {
+ Row row = sheet.getRow(FIRST_DATA_ROW_INDEX + day.getKey() - 1);
+ if (row == null) {
+ summary.addError(entry.getKey() + " 绗�" + day.getKey() + "鏃ワ細宸ヤ綔琛ㄧ己灏戣琛�");
+ continue;
+ }
+ Integer rowDay = parseDayIndex(row.getCell(0), evaluator);
+ if (rowDay == null || !rowDay.equals(day.getKey())) {
+ summary.addError(entry.getKey() + " 绗�" + (row.getRowNum() + 1) + "琛岋細绗嚑鏃ヤ笌鑺傚亣鏃ラ厤缃笉涓�鑷�");
+ continue;
+ }
+ BigDecimal value;
+ try {
+ value = numberValue(row.getCell(yearColumn), evaluator);
+ } catch (Exception e) {
+ summary.addError(entry.getKey() + " 绗�" + (row.getRowNum() + 1) + "琛岋細" + e.getMessage());
+ continue;
+ }
+ if (value == null) {
+ summary.incrementSkipped();
+ continue;
+ }
+ upsertPassenger(year, day.getValue(), entry.getValue(), value);
+ summary.incrementPassenger();
+ }
+ }
+ }
+
+ private void importVehicleSheets(Workbook workbook, FormulaEvaluator evaluator, int year,
+ Map<Integer, LocalDate> dateByDay, ImportSummary summary) {
+ importVehicleSheet(workbook, evaluator, year, dateByDay, summary,
+ "楂橀�熷叕璺�", HolidayConstants.ROAD_EXPRESSWAY, HolidayConstants.VEHICLE_SMALL_PASSENGER, 2, 7, null);
+ importVehicleSheet(workbook, evaluator, year, dateByDay, summary,
+ "鏅�氬浗鐪侀亾", HolidayConstants.ROAD_NATIONAL_PROVINCIAL, HolidayConstants.VEHICLE_MEDIUM_SMALL_PASSENGER, 3, 8,
+ HolidayConstants.supportsComparisonBase(year) ? 2 : null);
+ }
+
+ private void importVehicleSheet(Workbook workbook, FormulaEvaluator evaluator, int year,
+ Map<Integer, LocalDate> dateByDay, ImportSummary summary,
+ String sheetName, String roadCategory, String vehicleType,
+ int startColumn, int endColumn, Integer comparisonBaseColumn) {
+ Sheet sheet = workbook.getSheet(sheetName);
+ if (sheet == null) {
+ summary.addError(sheetName + "锛氭壘涓嶅埌宸ヤ綔琛�");
+ return;
+ }
+ Integer yearColumn = findYearColumn(sheet, evaluator, year, startColumn, endColumn);
+ if (yearColumn == null) {
+ summary.addError(sheetName + "锛氭壘涓嶅埌 " + year + " 骞存暟鎹垪");
+ return;
+ }
+ for (Map.Entry<Integer, LocalDate> day : dateByDay.entrySet()) {
+ Row row = sheet.getRow(FIRST_DATA_ROW_INDEX + day.getKey() - 1);
+ if (row == null) {
+ summary.addError(sheetName + " 绗�" + day.getKey() + "鏃ワ細宸ヤ綔琛ㄧ己灏戣琛�");
+ continue;
+ }
+ Integer rowDay = parseDayIndex(row.getCell(0), evaluator);
+ if (rowDay == null || !rowDay.equals(day.getKey())) {
+ summary.addError(sheetName + " 绗�" + (row.getRowNum() + 1) + "琛岋細绗嚑鏃ヤ笌鑺傚亣鏃ラ厤缃笉涓�鑷�");
+ continue;
+ }
+ BigDecimal value;
+ try {
+ value = numberValue(row.getCell(yearColumn), evaluator);
+ } catch (Exception e) {
+ summary.addError(sheetName + " 绗�" + (row.getRowNum() + 1) + "琛岋細" + e.getMessage());
+ continue;
+ }
+ if (value == null) {
+ summary.incrementSkipped();
+ continue;
+ }
+ BigDecimal comparisonBase = null;
+ if (comparisonBaseColumn != null) {
+ try {
+ comparisonBase = numberValue(row.getCell(comparisonBaseColumn), evaluator);
+ } catch (Exception e) {
+ summary.addError(sheetName + " 绗�" + (row.getRowNum() + 1) + "琛岀幆姣斿熀鏈燂細" + e.getMessage());
+ continue;
+ }
+ }
+ upsertVehicle(year, day.getValue(), roadCategory, vehicleType, value, comparisonBase);
+ summary.incrementVehicle();
+ }
+ }
+
+ private void fillPassengerSheets(XSSFWorkbook workbook, FormulaEvaluator evaluator, int selectedYear, String holidayType) {
+ for (Map.Entry<String, String> entry : PASSENGER_SHEET_MODE.entrySet()) {
+ Sheet sheet = workbook.getSheet(entry.getKey());
+ if (sheet == null) continue;
+ for (int year : REFERENCE_YEARS) {
+ Integer yearColumn = findYearColumn(sheet, evaluator, year, 2, 7);
+ if (yearColumn == null) continue;
+ Map<Integer, LocalDate> dayDates = dayDates(year, holidayType);
+ Map<LocalDate, BigDecimal> values = loadPassengerValues(year, entry.getValue());
+ for (Map.Entry<Integer, LocalDate> day : dayDates.entrySet()) {
+ if (!values.containsKey(day.getValue())) continue;
+ setNumeric(sheet, FIRST_DATA_ROW_INDEX + day.getKey() - 1, yearColumn, values.get(day.getValue()));
+ }
+ }
+ }
+ }
+
+ private void fillVehicleSheets(XSSFWorkbook workbook, FormulaEvaluator evaluator, int selectedYear, String holidayType) {
+ fillVehicleSheet(workbook, evaluator, "楂橀�熷叕璺�", HolidayConstants.ROAD_EXPRESSWAY, 2, 7, holidayType, null);
+ fillVehicleSheet(workbook, evaluator, "鏅�氬浗鐪侀亾", HolidayConstants.ROAD_NATIONAL_PROVINCIAL, 3, 8, holidayType,
+ HolidayConstants.supportsComparisonBase(selectedYear) ? 2 : null);
+ }
+
+ private void fillVehicleSheet(XSSFWorkbook workbook, FormulaEvaluator evaluator, String sheetName,
+ String roadCategory, int startColumn, int endColumn, String holidayType,
+ Integer comparisonBaseColumn) {
+ Sheet sheet = workbook.getSheet(sheetName);
+ if (sheet == null) return;
+ for (int year : REFERENCE_YEARS) {
+ Integer yearColumn = findYearColumn(sheet, evaluator, year, startColumn, endColumn);
+ if (yearColumn == null) continue;
+ Map<Integer, LocalDate> dayDates = dayDates(year, holidayType);
+ Map<LocalDate, BigDecimal> values = loadVehicleValues(year, roadCategory);
+ for (Map.Entry<Integer, LocalDate> day : dayDates.entrySet()) {
+ if (!values.containsKey(day.getValue())) continue;
+ setNumeric(sheet, FIRST_DATA_ROW_INDEX + day.getKey() - 1, yearColumn, values.get(day.getValue()));
+ }
+ if (comparisonBaseColumn != null && HolidayConstants.supportsComparisonBase(year)) {
+ Map<LocalDate, BigDecimal> bases = loadComparisonBaseValues(year);
+ for (Map.Entry<Integer, LocalDate> day : dayDates.entrySet()) {
+ if (!bases.containsKey(day.getValue())) continue;
+ setNumeric(sheet, FIRST_DATA_ROW_INDEX + day.getKey() - 1, comparisonBaseColumn, bases.get(day.getValue()));
+ }
+ for (Map.Entry<Integer, LocalDate> day : dayDates.entrySet()) {
+ if (!bases.containsKey(day.getValue())) continue;
+ int rowIndex = FIRST_DATA_ROW_INDEX + day.getKey() - 1;
+ int excelRow = rowIndex + 1;
+ setFormula(sheet, rowIndex, 4, "=(D" + excelRow + "-C" + excelRow + ")/C" + excelRow);
+ }
+ }
+ }
+ }
+
+ private void fillCoefficients(XSSFWorkbook workbook, FormulaEvaluator evaluator, int selectedYear, String holidayType) {
+ fillCoefficientSheet(workbook, evaluator, "楂橀�熷叕璺�", 8, 10, holidayType);
+ fillCoefficientSheet(workbook, evaluator, "鏅�氬浗鐪侀亾", 9, 11, holidayType);
+ }
+
+ private void fillCoefficientSheet(XSSFWorkbook workbook, FormulaEvaluator evaluator, String sheetName,
+ int startColumn, int endColumn, String holidayType) {
+ Sheet sheet = workbook.getSheet(sheetName);
+ if (sheet == null) return;
+ for (int year : REFERENCE_YEARS) {
+ Integer yearColumn = findYearColumn(sheet, evaluator, year, startColumn, endColumn);
+ if (yearColumn == null) continue;
+ BigDecimal coefficient = configService.coefficientOf(year, HolidayConstants.CATEGORY_HOLIDAY);
+ if (coefficient == null) continue;
+ Map<Integer, LocalDate> dayDates = dayDates(year, holidayType);
+ for (Integer dayIndex : dayDates.keySet()) {
+ setNumeric(sheet, FIRST_DATA_ROW_INDEX + dayIndex - 1, yearColumn, coefficient);
+ }
+ }
+ }
+
+ private void seedNationalDayCalendar() {
+ // Calendar is admin-managed after the first bootstrap. Re-adding missing day indexes on
+ // every start would resurrect days the admin deleted, so only seed a fresh table.
+ if (!configService.listHolidays(null, null).isEmpty()) {
+ return;
+ }
+ int[] lengths = {7, 8, 8};
+ for (int i = 0; i < REFERENCE_YEARS.length; i++) {
+ int year = REFERENCE_YEARS[i];
+ List<HolidayCalendar> existing = configService.findHolidayDays(year, HolidayConstants.TYPE_NATIONAL_DAY);
+ Map<Integer, HolidayCalendar> byDay = new LinkedHashMap<>();
+ for (HolidayCalendar row : existing) {
+ byDay.put(row.getDayIndex(), row);
+ }
+ for (int day = 1; day <= lengths[i]; day++) {
+ if (byDay.containsKey(day)) continue;
+ HolidayCalendar calendar = new HolidayCalendar();
+ calendar.setYear(year);
+ calendar.setHolidayType(HolidayConstants.TYPE_NATIONAL_DAY);
+ calendar.setHolidayDate(LocalDate.of(year, 10, day));
+ calendar.setDayIndex(day);
+ calendar.setCreatedAt(LocalDateTime.now());
+ calendar.setUpdatedAt(LocalDateTime.now());
+ configService.saveHoliday(toMap(calendar));
+ }
+ }
+ }
+
+ private void seedPassengerYear(Workbook workbook, FormulaEvaluator evaluator, int year) {
+ Map<Integer, LocalDate> dayDates = dayDates(year, HolidayConstants.TYPE_NATIONAL_DAY);
+ if (dayDates.isEmpty()) return;
+ for (Map.Entry<String, String> entry : PASSENGER_SHEET_MODE.entrySet()) {
+ Sheet sheet = workbook.getSheet(entry.getKey());
+ if (sheet == null) continue;
+ Integer yearColumn = findYearColumn(sheet, evaluator, year, 2, 7);
+ if (yearColumn == null) continue;
+ for (Map.Entry<Integer, LocalDate> day : dayDates.entrySet()) {
+ Row row = sheet.getRow(FIRST_DATA_ROW_INDEX + day.getKey() - 1);
+ if (row == null) continue;
+ BigDecimal value = numberValue(row.getCell(yearColumn), evaluator);
+ if (value != null) {
+ upsertPassenger(year, day.getValue(), entry.getValue(), value);
+ }
+ }
+ }
+ }
+
+ private void seedVehicleYear(Workbook workbook, FormulaEvaluator evaluator, int year) {
+ Map<Integer, LocalDate> dayDates = dayDates(year, HolidayConstants.TYPE_NATIONAL_DAY);
+ if (dayDates.isEmpty()) return;
+ seedVehicleSheet(workbook, evaluator, year, dayDates, "楂橀�熷叕璺�",
+ HolidayConstants.ROAD_EXPRESSWAY, HolidayConstants.VEHICLE_SMALL_PASSENGER, 2, 7);
+ seedVehicleSheet(workbook, evaluator, year, dayDates, "鏅�氬浗鐪侀亾",
+ HolidayConstants.ROAD_NATIONAL_PROVINCIAL, HolidayConstants.VEHICLE_MEDIUM_SMALL_PASSENGER, 3, 8);
+ }
+
+ private void seedVehicleSheet(Workbook workbook, FormulaEvaluator evaluator, int year,
+ Map<Integer, LocalDate> dayDates, String sheetName,
+ String roadCategory, String vehicleType, int startColumn, int endColumn) {
+ Sheet sheet = workbook.getSheet(sheetName);
+ if (sheet == null) return;
+ Integer yearColumn = findYearColumn(sheet, evaluator, year, startColumn, endColumn);
+ if (yearColumn == null) return;
+ for (Map.Entry<Integer, LocalDate> day : dayDates.entrySet()) {
+ Row row = sheet.getRow(FIRST_DATA_ROW_INDEX + day.getKey() - 1);
+ if (row == null) continue;
+ BigDecimal value = numberValue(row.getCell(yearColumn), evaluator);
+ if (value != null) {
+ upsertVehicle(year, day.getValue(), roadCategory, vehicleType, value, null);
+ }
+ }
+ }
+
+ private void seedCoefficientYear(Workbook workbook, FormulaEvaluator evaluator, int year) {
+ List<BigDecimal> values = new ArrayList<>();
+ collectCoefficientValues(workbook, evaluator, "楂橀�熷叕璺�", year, 8, 10, values);
+ collectCoefficientValues(workbook, evaluator, "鏅�氬浗鐪侀亾", year, 9, 11, values);
+ if (values.isEmpty()) return;
+ BigDecimal sum = BigDecimal.ZERO;
+ for (BigDecimal value : values) {
+ sum = HolidayNumberUtils.normalize(sum.add(value));
+ }
+ BigDecimal coefficient = HolidayNumberUtils.divide(sum, BigDecimal.valueOf(values.size()));
+ upsertCoefficient(year, HolidayConstants.CATEGORY_HOLIDAY, coefficient);
+ upsertCoefficient(year, HolidayConstants.CATEGORY_NON_HOLIDAY, coefficient);
+ }
+
+ private void collectCoefficientValues(Workbook workbook, FormulaEvaluator evaluator, String sheetName,
+ int year, int startColumn, int endColumn, List<BigDecimal> values) {
+ Sheet sheet = workbook.getSheet(sheetName);
+ if (sheet == null) return;
+ Integer yearColumn = findYearColumn(sheet, evaluator, year, startColumn, endColumn);
+ if (yearColumn == null) return;
+ for (int rowIndex = FIRST_DATA_ROW_INDEX; rowIndex < FIRST_DATA_ROW_INDEX + 10; rowIndex++) {
+ Row row = sheet.getRow(rowIndex);
+ if (row == null) continue;
+ try {
+ BigDecimal value = numberValue(row.getCell(yearColumn), evaluator);
+ if (value != null && value.compareTo(BigDecimal.ZERO) > 0) values.add(value);
+ } catch (Exception ignored) {
+ // Skip cells that are not numeric coefficients.
+ }
+ }
+ }
+
+ private Map<Integer, LocalDate> dayDates(int year, String holidayType) {
+ Map<Integer, LocalDate> result = new LinkedHashMap<>();
+ for (HolidayCalendar row : configService.findHolidayDays(year, holidayType)) {
+ result.put(row.getDayIndex(), row.getHolidayDate());
+ }
+ return result;
+ }
+
+ private Map<LocalDate, BigDecimal> loadPassengerValues(int year, String transportMode) {
+ Map<LocalDate, BigDecimal> result = new LinkedHashMap<>();
+ List<HolidayPassengerFlow> rows = passengerFlowMapper.selectList(
+ new LambdaQueryWrapper<HolidayPassengerFlow>()
+ .eq(HolidayPassengerFlow::getYear, year)
+ .eq(HolidayPassengerFlow::getTransportMode, transportMode));
+ for (HolidayPassengerFlow row : rows) result.put(row.getFlowDate(), row.getPassengerCount());
+ return result;
+ }
+
+ private Map<LocalDate, BigDecimal> loadVehicleValues(int year, String roadCategory) {
+ Map<LocalDate, BigDecimal> result = new LinkedHashMap<>();
+ List<HolidayVehicleFlow> rows = vehicleFlowMapper.selectList(
+ new LambdaQueryWrapper<HolidayVehicleFlow>()
+ .eq(HolidayVehicleFlow::getYear, year)
+ .eq(HolidayVehicleFlow::getRoadCategory, roadCategory));
+ for (HolidayVehicleFlow row : rows) result.put(row.getFlowDate(), row.getVehicleCount());
+ return result;
+ }
+
+ private Map<LocalDate, BigDecimal> loadComparisonBaseValues(int year) {
+ Map<LocalDate, BigDecimal> result = new LinkedHashMap<>();
+ List<HolidayVehicleFlow> rows = vehicleFlowMapper.selectList(
+ new LambdaQueryWrapper<HolidayVehicleFlow>()
+ .eq(HolidayVehicleFlow::getYear, year)
+ .eq(HolidayVehicleFlow::getRoadCategory, HolidayConstants.ROAD_NATIONAL_PROVINCIAL));
+ for (HolidayVehicleFlow row : rows) {
+ if (row.getComparisonBase() != null) result.put(row.getFlowDate(), row.getComparisonBase());
+ }
+ return result;
+ }
+
+ private void upsertPassenger(int year, LocalDate date, String mode, BigDecimal value) {
+ HolidayPassengerFlow existing = passengerFlowMapper.selectOne(new LambdaQueryWrapper<HolidayPassengerFlow>()
+ .eq(HolidayPassengerFlow::getYear, year)
+ .eq(HolidayPassengerFlow::getFlowDate, date)
+ .eq(HolidayPassengerFlow::getTransportMode, mode)
+ .last("limit 1"));
+ if (existing == null) {
+ existing = new HolidayPassengerFlow();
+ existing.setYear(year);
+ existing.setFlowDate(date);
+ existing.setTransportMode(mode);
+ existing.setPassengerCount(value);
+ existing.setCreatedAt(LocalDateTime.now());
+ existing.setUpdatedAt(LocalDateTime.now());
+ passengerFlowMapper.insert(existing);
+ } else {
+ existing.setPassengerCount(value);
+ existing.setUpdatedAt(LocalDateTime.now());
+ passengerFlowMapper.updateById(existing);
+ }
+ }
+
+ private void upsertVehicle(int year, LocalDate date, String roadCategory, String vehicleType,
+ BigDecimal value, BigDecimal comparisonBase) {
+ HolidayVehicleFlow existing = vehicleFlowMapper.selectOne(new LambdaQueryWrapper<HolidayVehicleFlow>()
+ .eq(HolidayVehicleFlow::getYear, year)
+ .eq(HolidayVehicleFlow::getFlowDate, date)
+ .eq(HolidayVehicleFlow::getRoadCategory, roadCategory)
+ .eq(HolidayVehicleFlow::getVehicleType, vehicleType)
+ .last("limit 1"));
+ if (existing == null) {
+ existing = new HolidayVehicleFlow();
+ existing.setYear(year);
+ existing.setFlowDate(date);
+ existing.setRoadCategory(roadCategory);
+ existing.setVehicleType(vehicleType);
+ existing.setVehicleCount(value);
+ existing.setComparisonBase(comparisonBase);
+ existing.setCreatedAt(LocalDateTime.now());
+ existing.setUpdatedAt(LocalDateTime.now());
+ vehicleFlowMapper.insert(existing);
+ } else {
+ existing.setVehicleCount(value);
+ existing.setComparisonBase(comparisonBase);
+ existing.setUpdatedAt(LocalDateTime.now());
+ vehicleFlowMapper.updateById(existing);
+ }
+ }
+
+ private void upsertCoefficient(int year, String dateCategory, BigDecimal value) {
+ HolidayPassengerCoefficient existing = coefficientMapper.selectOne(
+ new LambdaQueryWrapper<HolidayPassengerCoefficient>()
+ .eq(HolidayPassengerCoefficient::getYear, year)
+ .eq(HolidayPassengerCoefficient::getDateCategory, dateCategory)
+ .last("limit 1"));
+ if (existing == null) {
+ existing = new HolidayPassengerCoefficient();
+ existing.setYear(year);
+ existing.setDateCategory(dateCategory);
+ existing.setCoefficient(value);
+ existing.setCreatedAt(LocalDateTime.now());
+ existing.setUpdatedAt(LocalDateTime.now());
+ coefficientMapper.insert(existing);
+ } else {
+ existing.setCoefficient(value);
+ existing.setUpdatedAt(LocalDateTime.now());
+ coefficientMapper.updateById(existing);
+ }
+ }
+
+ private Integer findYearColumn(Sheet sheet, FormulaEvaluator evaluator, int year, int startColumn, int endColumn) {
+ Row header = sheet.getRow(1);
+ if (header == null) return null;
+ String wanted = year + "骞�";
+ DataFormatter formatter = new DataFormatter();
+ for (int column = startColumn; column <= endColumn; column++) {
+ Cell cell = header.getCell(column);
+ if (cell == null) continue;
+ String text = formatter.formatCellValue(cell, evaluator).replace("\n", "").trim();
+ if (wanted.equals(text) || text.startsWith(wanted + "绱")) return column;
+ }
+ return null;
+ }
+
+ private Integer parseDayIndex(Cell cell, FormulaEvaluator evaluator) {
+ if (cell == null) return null;
+ DataFormatter formatter = new DataFormatter();
+ String text = formatter.formatCellValue(cell, evaluator).trim();
+ java.util.regex.Matcher matcher = java.util.regex.Pattern.compile("绗琝\s*(\\d+)\\s*鏃�").matcher(text);
+ return matcher.find() ? Integer.valueOf(matcher.group(1)) : null;
+ }
+
+ private BigDecimal numberValue(Cell cell, FormulaEvaluator evaluator) {
+ if (cell == null) return null;
+ CellType type = cell.getCellType();
+ if (type == CellType.FORMULA) {
+ type = evaluator.evaluateFormulaCell(cell);
+ }
+ if (type == CellType.BLANK) return null;
+ if (type == CellType.NUMERIC) return HolidayNumberUtils.excelValue(cell.getNumericCellValue());
+ if (type == CellType.STRING) {
+ String text = cell.getStringCellValue().trim().replace(",", "");
+ if (text.isEmpty() || "-".equals(text) || "鈥�".equals(text)) return null;
+ try {
+ return HolidayNumberUtils.decimalValue(text);
+ } catch (NumberFormatException e) {
+ throw new IllegalArgumentException("鍗曞厓鏍间笉鏄湁鏁堟暟瀛�: " + text);
+ }
+ }
+ if (type == CellType.BOOLEAN) throw new IllegalArgumentException("鍗曞厓鏍间笉鑳芥槸甯冨皵鍊�");
+ throw new IllegalArgumentException("鍗曞厓鏍间笉鏄湁鏁堟暟瀛�");
+ }
+
+ private void setNumeric(Sheet sheet, int rowIndex, int columnIndex, BigDecimal value) {
+ if (value == null) return;
+ Row row = sheet.getRow(rowIndex);
+ if (row == null) row = sheet.createRow(rowIndex);
+ Cell cell = row.getCell(columnIndex);
+ if (cell == null) cell = row.createCell(columnIndex);
+ cell.setCellValue(value.doubleValue());
+ }
+
+ private void setFormula(Sheet sheet, int rowIndex, int columnIndex, String formula) {
+ Row row = sheet.getRow(rowIndex);
+ if (row == null) row = sheet.createRow(rowIndex);
+ Cell cell = row.getCell(columnIndex);
+ if (cell == null) cell = row.createCell(columnIndex);
+ cell.setCellFormula(formula);
+ }
+
+ private Map<String, Object> toMap(HolidayCalendar calendar) {
+ Map<String, Object> params = new LinkedHashMap<>();
+ params.put("year", calendar.getYear());
+ params.put("holidayType", calendar.getHolidayType());
+ params.put("holidayDate", calendar.getHolidayDate().toString());
+ params.put("dayIndex", calendar.getDayIndex());
+ return params;
+ }
+
+ @Data
+ public static class ImportSummary {
+ private int passengerRows;
+ private int vehicleRows;
+ private int skippedRows;
+ private boolean confirmationRequired;
+ private final List<String> errors = new ArrayList<>();
+ private final List<String> historicalChanges = new ArrayList<>();
+
+ public void incrementPassenger() {
+ passengerRows++;
+ }
+
+ public void incrementVehicle() {
+ vehicleRows++;
+ }
+
+ public void incrementSkipped() {
+ skippedRows++;
+ }
+
+ public void addError(String message) {
+ errors.add(message);
+ }
+
+ public void addHistoricalChange(String message) {
+ if (historicalChanges.size() < 200) {
+ historicalChanges.add(message);
+ }
+ }
+
+ public int getSuccessRows() {
+ return passengerRows + vehicleRows;
+ }
+
+ public int getFailRows() {
+ return errors.size();
+ }
+ }
+}
--
Gitblit v1.9.1