fix(holiday): 拥挤度标签恢复两位小数

xyc

昨天 2f3c2d2851f7693357feb9e62a56a64670755c6a

 traffic-audit-server/src/main/java/com/trafficaudit/holiday/service/HolidayWorkbookService.java

@@ -46,6 +46,7 @@

    private static final Map<String, String> PASSENGER_SHEET_MODE = new LinkedHashMap<>();
    private static final int FIRST_DATA_ROW_INDEX = 2;
    private static final int DAY_ROW_SCAN_LIMIT = 30;
    private static final int[] REFERENCE_YEARS = {2024, 2025, 2026};

    static {
@@ -115,7 +116,7 @@
        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);
            importPassengerSheets(workbook, evaluator, year, holidayType, dateByDay, summary);
            importVehicleSheets(workbook, evaluator, year, dateByDay, summary);
        } catch (Exception e) {
            throw new IllegalArgumentException("工作簿解析失败: " + e.getMessage(), e);
@@ -161,7 +162,7 @@
            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);
            Row row = findDayRow(sheet, evaluator, dayIndex);
            if (row == null) continue;
            BigDecimal value = numberValue(row.getCell(yearColumn), evaluator);
            if (value != null) return true;
@@ -174,7 +175,7 @@
        if (expressway != null) {
            Integer yearColumn = findYearColumn(expressway, evaluator, year, 2, 7);
            if (yearColumn != null) {
                Row row = expressway.getRow(FIRST_DATA_ROW_INDEX + dayIndex - 1);
                Row row = findDayRow(expressway, evaluator, dayIndex);
                if (row != null && numberValue(row.getCell(yearColumn), evaluator) != null) return true;
            }
        }
@@ -182,7 +183,7 @@
        if (nationalProvincial != null) {
            Integer yearColumn = findYearColumn(nationalProvincial, evaluator, year, 3, 8);
            if (yearColumn != null) {
                Row row = nationalProvincial.getRow(FIRST_DATA_ROW_INDEX + dayIndex - 1);
                Row row = findDayRow(nationalProvincial, evaluator, dayIndex);
                if (row != null && numberValue(row.getCell(yearColumn), evaluator) != null) return true;
            }
        }
@@ -200,7 +201,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);
                Row row = findDayRow(sheet, evaluator, day.getKey());
                if (row == null) continue;
                BigDecimal incoming = numberValue(row.getCell(yearColumn), evaluator);
                if (incoming == null) continue;
@@ -236,7 +237,7 @@
        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);
            Row row = findDayRow(sheet, evaluator, day.getKey());
            if (row == null) continue;
            BigDecimal incoming = numberValue(row.getCell(yearColumn), evaluator);
            if (incoming == null) continue;
@@ -261,7 +262,7 @@
            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);
            Row row = findDayRow(sheet, evaluator, day.getKey());
            if (row == null) continue;
            BigDecimal incoming;
            try {
@@ -278,8 +279,8 @@
        }
    }

        Map<LocalDate, Map<String, BigDecimal>> result = new LinkedHashMap<>();
    private Map<LocalDate, Map<String, BigDecimal>> loadPassengerByDate(int year) {
        Map<LocalDate, Map<String, BigDecimal>> result = new LinkedHashMap<>();
        List<HolidayPassengerFlow> rows = passengerFlowMapper.selectList(
                new LambdaQueryWrapper<HolidayPassengerFlow>().eq(HolidayPassengerFlow::getYear, year));
        for (HolidayPassengerFlow row : rows) {
@@ -337,6 +338,7 @@
        try (InputStream in = new FileInputStream(reference); Workbook workbook = WorkbookFactory.create(in)) {
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            seedNationalDayCalendar();
            ensurePreviousDayCalendarIfMissing();
            for (int year : REFERENCE_YEARS) {
                if (passengerFlowMapper.selectCount(new LambdaQueryWrapper<HolidayPassengerFlow>()
                        .eq(HolidayPassengerFlow::getYear, year)) == 0) {
@@ -351,6 +353,8 @@
                    seedCoefficientYear(workbook, evaluator, year);
                }
            }
            seedPreviousDayPassengerIfMissing(workbook, evaluator);
            seedRoad2019IfMissing(workbook, evaluator);
        } catch (Exception e) {
            log.error("Failed to seed holiday reference data", e);
        }
@@ -381,7 +385,8 @@
    }

    private void importPassengerSheets(Workbook workbook, FormulaEvaluator evaluator, int year,
                                       Map<Integer, LocalDate> dateByDay, ImportSummary summary) {
                                       String holidayType, 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) {
@@ -394,7 +399,7 @@
                continue;
            }
            for (Map.Entry<Integer, LocalDate> day : dateByDay.entrySet()) {
                Row row = sheet.getRow(FIRST_DATA_ROW_INDEX + day.getKey() - 1);
                Row row = findDayRow(sheet, evaluator, day.getKey());
                if (row == null) {
                    summary.addError(entry.getKey() + " 第" + day.getKey() + "日：工作表缺少该行");
                    continue;
@@ -418,6 +423,39 @@
                upsertPassenger(year, day.getValue(), entry.getValue(), value);
                summary.incrementPassenger();
            }
        }
        importPreviousDayPassengerRows(workbook, evaluator, year, holidayType, dateByDay, summary);
    }

    private void importPreviousDayPassengerRows(Workbook workbook, FormulaEvaluator evaluator, int year,
                                                 String holidayType, Map<Integer, LocalDate> dateByDay,
                                                 ImportSummary summary) {
        LocalDate previousDate = previousDayDate(year, holidayType, dateByDay);
        if (previousDate == null) return;
        for (Map.Entry<String, String> entry : PASSENGER_SHEET_MODE.entrySet()) {
            if (!HolidayConstants.MODE_RAIL.equals(entry.getValue())
                    && !HolidayConstants.MODE_CIVIL_AVIATION.equals(entry.getValue())) {
                continue;
            }
            Sheet sheet = workbook.getSheet(entry.getKey());
            if (sheet == null) continue;
            Integer yearColumn = findYearColumn(sheet, evaluator, year, 2, 7);
            if (yearColumn == null) continue;
            Row row = findPreviousDayRow(sheet, evaluator);
            if (row == null) continue;
            BigDecimal value;
            try {
                value = numberValue(row.getCell(yearColumn), evaluator);
            } catch (Exception e) {
                summary.addError(entry.getKey() + " 前一日行：" + e.getMessage());
                continue;
            }
            if (value == null) {
                summary.incrementSkipped();
                continue;
            }
            upsertPassenger(year, previousDate, entry.getValue(), value);
            summary.incrementPassenger();
        }
    }

@@ -445,7 +483,7 @@
            return;
        }
        for (Map.Entry<Integer, LocalDate> day : dateByDay.entrySet()) {
            Row row = sheet.getRow(FIRST_DATA_ROW_INDEX + day.getKey() - 1);
            Row row = findDayRow(sheet, evaluator, day.getKey());
            if (row == null) {
                summary.addError(sheetName + " 第" + day.getKey() + "日：工作表缺少该行");
                continue;
@@ -491,7 +529,17 @@
                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()));
                    Row row = findDayRow(sheet, evaluator, day.getKey());
                    if (row == null) continue;
                    setNumeric(sheet, row.getRowNum(), yearColumn, values.get(day.getValue()));
                }
                LocalDate previousDate = previousDayDate(year, holidayType, dayDates);
                Row previousRow = findPreviousDayRow(sheet, evaluator);
                if (previousDate != null && previousRow != null) {
                    BigDecimal previousValue = values.get(previousDate);
                    if (previousValue != null) {
                        setNumeric(sheet, previousRow.getRowNum(), yearColumn, previousValue);
                    }
                }
            }
        }
@@ -515,22 +563,42 @@
            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()));
                Row row = findDayRow(sheet, evaluator, day.getKey());
                if (row == null) continue;
                setNumeric(sheet, row.getRowNum(), yearColumn, values.get(day.getValue()));
            }
            if (comparisonBaseColumn != null && HolidayConstants.supportsComparisonBase(year)) {
                // Column C (comparison base) is written as an operator backup value only. The ring
                // ratio in column E always compares the current day with the previous day row of
                // the same year, so the base never changes the calculated percentage.
                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()));
                    Row row = findDayRow(sheet, evaluator, day.getKey());
                    if (row == null) continue;
                    setNumeric(sheet, row.getRowNum(), comparisonBaseColumn, bases.get(day.getValue()));
                }
                Integer previousRowIndex = null;
                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);
                    Row row = findDayRow(sheet, evaluator, day.getKey());
                    if (row == null) {
                        previousRowIndex = null;
                        continue;
                    }
                    if (previousRowIndex != null) {
                        setFormula(sheet, row.getRowNum(), 4,
                                ringRatioFormula(row.getRowNum() + 1, previousRowIndex + 1));
                    }
                    previousRowIndex = row.getRowNum();
                }
            }
        }
    }

    // Ring ratio = (current day value - previous day value) / previous day value, always taken
    // from the active year column (D). This intentionally ignores the comparison base column.
    static String ringRatioFormula(int excelRow, int previousExcelRow) {
        return "=(D" + excelRow + "-D" + previousExcelRow + ")/D" + previousExcelRow;
    }

    private void fillCoefficients(XSSFWorkbook workbook, FormulaEvaluator evaluator, int selectedYear, String holidayType) {
@@ -560,9 +628,10 @@
        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];
        int[] calendarYears = {2019, 2024, 2025, 2026};
        int[] lengths = {8, 7, 8, 8};
        for (int i = 0; i < calendarYears.length; i++) {
            int year = calendarYears[i];
            List<HolidayCalendar> existing = configService.findHolidayDays(year, HolidayConstants.TYPE_NATIONAL_DAY);
            Map<Integer, HolidayCalendar> byDay = new LinkedHashMap<>();
            for (HolidayCalendar row : existing) {
@@ -579,6 +648,87 @@
                calendar.setUpdatedAt(LocalDateTime.now());
                configService.saveHoliday(toMap(calendar));
            }
            if (year == 2019) {
                continue;
            }
            HolidayCalendar previousDay = new HolidayCalendar();
            previousDay.setYear(year);
            previousDay.setHolidayType(HolidayConstants.TYPE_NATIONAL_DAY);
            previousDay.setHolidayDate(LocalDate.of(year, 10, 1).minusDays(1));
            previousDay.setDayIndex(0);
            previousDay.setCreatedAt(LocalDateTime.now());
            previousDay.setUpdatedAt(LocalDateTime.now());
            configService.saveHoliday(toMap(previousDay));
        }
    }

    private void ensurePreviousDayCalendarIfMissing() {
        for (int year : REFERENCE_YEARS) {
            if (configService.findPreviousDay(year, HolidayConstants.TYPE_NATIONAL_DAY) != null) {
                continue;
            }
            LocalDate firstHolidayDate = firstDate(dayDates(year, HolidayConstants.TYPE_NATIONAL_DAY));
            if (firstHolidayDate == null) {
                continue;
            }
            HolidayCalendar previousDay = new HolidayCalendar();
            previousDay.setYear(year);
            previousDay.setHolidayType(HolidayConstants.TYPE_NATIONAL_DAY);
            previousDay.setHolidayDate(firstHolidayDate.minusDays(1));
            previousDay.setDayIndex(0);
            previousDay.setCreatedAt(LocalDateTime.now());
            previousDay.setUpdatedAt(LocalDateTime.now());
            configService.saveHoliday(toMap(previousDay));
        }
    }

    private void seedPreviousDayPassengerIfMissing(Workbook workbook, FormulaEvaluator evaluator) {
        for (int year : REFERENCE_YEARS) {
            Map<Integer, LocalDate> dayDates = dayDates(year, HolidayConstants.TYPE_NATIONAL_DAY);
            LocalDate previousDate = previousDayDate(year, HolidayConstants.TYPE_NATIONAL_DAY, dayDates);
            if (previousDate == null) continue;
            for (Map.Entry<String, String> entry : PASSENGER_SHEET_MODE.entrySet()) {
                if (!HolidayConstants.MODE_RAIL.equals(entry.getValue())
                        && !HolidayConstants.MODE_CIVIL_AVIATION.equals(entry.getValue())) {
                    continue;
                }
                if (passengerFlowMapper.selectCount(new LambdaQueryWrapper<HolidayPassengerFlow>()
                        .eq(HolidayPassengerFlow::getYear, year)
                        .eq(HolidayPassengerFlow::getFlowDate, previousDate)
                        .eq(HolidayPassengerFlow::getTransportMode, entry.getValue())) > 0) {
                    continue;
                }
                Sheet sheet = workbook.getSheet(entry.getKey());
                if (sheet == null) continue;
                Integer yearColumn = findYearColumn(sheet, evaluator, year, 2, 7);
                if (yearColumn == null) continue;
                Row row = findPreviousDayRow(sheet, evaluator);
                if (row == null) continue;
                BigDecimal value = numberValue(row.getCell(yearColumn), evaluator);
                if (value != null) upsertPassenger(year, previousDate, entry.getValue(), value);
            }
        }
    }

    private void seedRoad2019IfMissing(Workbook workbook, FormulaEvaluator evaluator) {
        if (passengerFlowMapper.selectCount(new LambdaQueryWrapper<HolidayPassengerFlow>()
                .eq(HolidayPassengerFlow::getYear, 2019)
                .eq(HolidayPassengerFlow::getTransportMode, HolidayConstants.MODE_ROAD)) > 0) {
            return;
        }
        Sheet sheet = workbook.getSheet("道路");
        if (sheet == null) return;
        Integer yearColumn = findYearColumn(sheet, evaluator, 2019, 2, 20);
        if (yearColumn == null) return;
        for (int rowIndex = 0; rowIndex <= Math.min(sheet.getLastRowNum(), DAY_ROW_SCAN_LIMIT); rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row == null) continue;
            Integer dayIndex = parseDayIndex(row.getCell(0), evaluator);
            if (dayIndex == null) continue;
            BigDecimal value = numberValue(row.getCell(yearColumn), evaluator);
            if (value != null) {
                upsertPassenger(2019, LocalDate.of(2019, 10, dayIndex), HolidayConstants.MODE_ROAD, value);
            }
        }
    }

@@ -591,11 +741,22 @@
            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);
                Row row = findDayRow(sheet, evaluator, day.getKey());
                if (row == null) continue;
                BigDecimal value = numberValue(row.getCell(yearColumn), evaluator);
                if (value != null) {
                    upsertPassenger(year, day.getValue(), entry.getValue(), value);
                }
            }
            if (HolidayConstants.MODE_RAIL.equals(entry.getValue())
                    || HolidayConstants.MODE_CIVIL_AVIATION.equals(entry.getValue())) {
                Row previousRow = findPreviousDayRow(sheet, evaluator);
                LocalDate previousDate = previousDayDate(year, HolidayConstants.TYPE_NATIONAL_DAY, dayDates);
                if (previousRow != null && previousDate != null) {
                    BigDecimal value = numberValue(previousRow.getCell(yearColumn), evaluator);
                    if (value != null) {
                        upsertPassenger(year, previousDate, entry.getValue(), value);
                    }
                }
            }
        }
@@ -618,7 +779,7 @@
        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);
            Row row = findDayRow(sheet, evaluator, day.getKey());
            if (row == null) continue;
            BigDecimal value = numberValue(row.getCell(yearColumn), evaluator);
            if (value != null) {
@@ -657,6 +818,46 @@
                // Skip cells that are not numeric coefficients.
            }
        }
    }

    private LocalDate previousDayDate(int year, String holidayType, Map<Integer, LocalDate> dateByDay) {
        HolidayCalendar configured = configService.findPreviousDay(year, holidayType);
        if (configured != null && configured.getHolidayDate() != null) {
            return configured.getHolidayDate();
        }
        LocalDate firstDate = firstDate(dateByDay);
        return firstDate == null ? null : firstDate.minusDays(1);
    }

    private LocalDate firstDate(Map<Integer, LocalDate> dateByDay) {
        LocalDate result = null;
        for (LocalDate date : dateByDay.values()) {
            if (date != null && (result == null || date.isBefore(result))) result = date;
        }
        return result;
    }

    private Row findDayRow(Sheet sheet, FormulaEvaluator evaluator, int dayIndex) {
        int lastRow = Math.min(sheet.getLastRowNum(), DAY_ROW_SCAN_LIMIT);
        for (int rowIndex = 0; rowIndex <= lastRow; rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row == null) continue;
            Integer rowDay = parseDayIndex(row.getCell(0), evaluator);
            if (rowDay != null && rowDay.intValue() == dayIndex) return row;
        }
        return null;
    }

    private Row findPreviousDayRow(Sheet sheet, FormulaEvaluator evaluator) {
        int lastRow = Math.min(sheet.getLastRowNum(), DAY_ROW_SCAN_LIMIT);
        DataFormatter formatter = new DataFormatter();
        for (int rowIndex = 0; rowIndex <= lastRow; rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row == null || row.getCell(0) == null) continue;
            String text = formatter.formatCellValue(row.getCell(0), evaluator).trim();
            if (text.contains("前一日")) return row;
        }
        return null;
    }

    private Map<Integer, LocalDate> dayDates(int year, String holidayType) {
@@ -826,7 +1027,9 @@
        if (row == null) row = sheet.createRow(rowIndex);
        Cell cell = row.getCell(columnIndex);
        if (cell == null) cell = row.createCell(columnIndex);
        cell.setCellFormula(formula);
        // POI's setCellFormula rejects a leading '='; the helper accepts either form.
        String normalized = formula.startsWith("=") ? formula.substring(1) : formula;
        cell.setCellFormula(normalized);
    }

    private Map<String, Object> toMap(HolidayCalendar calendar) {