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();
|
}
|
}
|
}
|
