447 lines
25 KiB
JavaScript
447 lines
25 KiB
JavaScript
import fs from "node:fs/promises";
|
||
import path from "node:path";
|
||
import { SpreadsheetFile, Workbook } from "@oai/artifact-tool";
|
||
|
||
const evidencePath = "/Users/lingniu/project/ai-coding/lingniu-vehicle-ingest/tmp/mileage-appeal-20260810/evidence.json";
|
||
const outputDir = "/Users/lingniu/project/ai-coding/lingniu-vehicle-ingest/outputs/019fe9b9-eb60-7d22-8c0a-c70851a48083";
|
||
const outputPath = path.join(outputDir, "15辆车里程核验申诉材料_GB32960日里程及原始报文.xlsx");
|
||
const previewDir = path.join(outputDir, "preview");
|
||
const data = JSON.parse(await fs.readFile(evidencePath, "utf8"));
|
||
|
||
const workbook = Workbook.create();
|
||
const summary = workbook.worksheets.add("申诉总览");
|
||
const notes = workbook.worksheets.add("口径与证据说明");
|
||
const vehicleSheets = new Map();
|
||
for (const vehicle of data.vehicles) {
|
||
const sheetName = `${String(vehicle.seq).padStart(2, "0")}-${vehicle.plate}`;
|
||
vehicleSheets.set(vehicle.vin, { sheetName, sheet: workbook.worksheets.add(sheetName) });
|
||
}
|
||
|
||
const palette = {
|
||
navy: "#1F4E78",
|
||
blue: "#D9EAF7",
|
||
blue2: "#EAF3F8",
|
||
yellow: "#FFF2CC",
|
||
amber: "#FCE4D6",
|
||
mileageHeader: "#C65911",
|
||
mileageLight: "#FFF2CC",
|
||
mileageStrong: "#FFD966",
|
||
green: "#E2F0D9",
|
||
gray: "#E7E6E6",
|
||
dark: "#1F2937",
|
||
muted: "#5B6573",
|
||
border: "#B7C9D6",
|
||
white: "#FFFFFF",
|
||
};
|
||
|
||
const thinBorders = { preset: "all", style: "thin", color: palette.border };
|
||
const titleFormat = {
|
||
fill: palette.navy,
|
||
font: { bold: true, color: palette.white, fontSize: 16, typeface: "Microsoft YaHei" },
|
||
horizontalAlignment: "left",
|
||
verticalAlignment: "center",
|
||
};
|
||
const sectionFormat = {
|
||
fill: palette.blue,
|
||
font: { bold: true, color: palette.dark, fontSize: 11, typeface: "Microsoft YaHei" },
|
||
horizontalAlignment: "left",
|
||
verticalAlignment: "center",
|
||
borders: thinBorders,
|
||
};
|
||
const headerFormat = {
|
||
fill: palette.navy,
|
||
font: { bold: true, color: palette.white, fontSize: 9, typeface: "Microsoft YaHei" },
|
||
horizontalAlignment: "center",
|
||
verticalAlignment: "center",
|
||
wrapText: true,
|
||
borders: thinBorders,
|
||
};
|
||
const labelFormat = {
|
||
fill: palette.blue2,
|
||
font: { bold: true, color: palette.dark, fontSize: 9, typeface: "Microsoft YaHei" },
|
||
horizontalAlignment: "left",
|
||
verticalAlignment: "center",
|
||
wrapText: true,
|
||
borders: thinBorders,
|
||
};
|
||
const valueFormat = {
|
||
font: { color: palette.dark, fontSize: 9, typeface: "Microsoft YaHei" },
|
||
verticalAlignment: "center",
|
||
wrapText: true,
|
||
borders: thinBorders,
|
||
};
|
||
|
||
function asDate(day) {
|
||
return new Date(`${day}T00:00:00Z`);
|
||
}
|
||
|
||
function addDays(day, count) {
|
||
const date = asDate(day);
|
||
date.setUTCDate(date.getUTCDate() + count);
|
||
return date.toISOString().slice(0, 10);
|
||
}
|
||
|
||
function calendarDays(start, end) {
|
||
const days = [];
|
||
for (let day = start; day <= end; day = addDays(day, 1)) days.push(day);
|
||
return days;
|
||
}
|
||
|
||
function absDelta(a, b) {
|
||
if (a == null || b == null || Number.isNaN(Number(a)) || Number.isNaN(Number(b))) return null;
|
||
return Math.abs(Number(a) - Number(b));
|
||
}
|
||
|
||
function rawRange(vehicle) {
|
||
const rows = vehicle.rawEvidence.filter((row) => row.status === "OK");
|
||
if (rows.length === 0) return "无";
|
||
return `${rows[0].day} 至 ${rows.at(-1).day}`;
|
||
}
|
||
|
||
function evidenceLevel(metric, raw) {
|
||
if (raw?.status === "OK") return "A-原始32960报文";
|
||
if (metric?.source_code === "legacy_lingniu_prod") return "C-历史日统计迁移（无RAW）";
|
||
if (metric) return "B-中台32960日统计（无RAW）";
|
||
return "D-无中台日记录";
|
||
}
|
||
|
||
function rowNote(metric, raw) {
|
||
const notes = [];
|
||
if (metric?.source_code === "legacy_lingniu_prod") notes.push("历史生产库 ln_vehicle_day_mileage 迁移记录，不是原始帧");
|
||
if (raw?.status === "OK" && !metric) notes.push("有原始报文，但中台日统计无记录");
|
||
if (raw?.status === "OK" && metric && absDelta(metric.latest_total_mileage_km, raw.parsedTotalMileageKm) > 0.1) {
|
||
notes.push("日统计日末值与本日最后可得RAW读数不一致，详见数据源/时间；可能涉及人工源选举或跨日延迟");
|
||
}
|
||
if (raw?.status !== "OK" && metric && metric.source_code !== "legacy_lingniu_prod") notes.push("有中台日统计，但当日未检出含仪表总里程的RAW帧");
|
||
if (!metric && raw?.status !== "OK") notes.push("当日无中台日记录，且现行RAW历史不可用或未检出");
|
||
return notes.join("；");
|
||
}
|
||
|
||
function setStandardSheet(sheet) {
|
||
sheet.showGridLines = false;
|
||
sheet.freezePanes.freezeRows(13);
|
||
sheet.freezePanes.freezeColumns(1);
|
||
}
|
||
|
||
for (const vehicle of data.vehicles) {
|
||
const { sheetName, sheet } = vehicleSheets.get(vehicle.vin);
|
||
setStandardSheet(sheet);
|
||
sheet.getRange("A1:P1").merge();
|
||
sheet.getRange("A1").values = [[`${vehicle.plate} 里程核验申诉明细`]];
|
||
sheet.getRange("A1:P1").format = titleFormat;
|
||
sheet.getRange("A1:P1").format.rowHeight = 30;
|
||
|
||
sheet.getRange("A2:P2").merge();
|
||
sheet.getRange("A2").values = [["核验口径：GB/T 32960 车辆仪表累计总里程；原始报文列保存完整HEX，点击单元格可在公式栏复制。相邻自然日均有RAW读数时才计算RAW日差。"]];
|
||
sheet.getRange("A2:P2").format = { fill: palette.yellow, font: { color: palette.dark, fontSize: 9, typeface: "Microsoft YaHei" }, wrapText: true, verticalAlignment: "center", borders: thinBorders };
|
||
sheet.getRange("A2:P2").format.rowHeight = 30;
|
||
|
||
sheet.getRange("A4:P6").format = valueFormat;
|
||
sheet.getRange("A4").values = [["车牌"]];
|
||
sheet.getRange("B4:C4").merge(); sheet.getRange("B4").values = [[vehicle.plate]];
|
||
sheet.getRange("D4").values = [["VIN"]];
|
||
sheet.getRange("E4:H4").merge(); sheet.getRange("E4").values = [[vehicle.vin]];
|
||
sheet.getRange("I4").values = [["车型"]];
|
||
sheet.getRange("J4:K4").merge(); sheet.getRange("J4").values = [[vehicle.model]];
|
||
sheet.getRange("L4").values = [["协议"]];
|
||
sheet.getRange("M4:P4").merge(); sheet.getRange("M4").values = [["GB32960（仪表累计总里程）"]];
|
||
|
||
sheet.getRange("A5").values = [["核验区间"]];
|
||
sheet.getRange("B5:C5").merge(); sheet.getRange("B5").values = [["2025-11-28 至 2026-07-31"]];
|
||
sheet.getRange("D5").values = [["原TBOX合计(km)"]];
|
||
sheet.getRange("E5:F5").merge(); sheet.getRange("E5").values = [[vehicle.sourceTboxKm]];
|
||
sheet.getRange("G5").values = [["第三方平台合计(km)"]];
|
||
sheet.getRange("H5:I5").merge(); sheet.getRange("H5").values = [[vehicle.platformKm]];
|
||
sheet.getRange("J5").values = [["原表损耗(km)"]];
|
||
sheet.getRange("K5:L5").merge(); sheet.getRange("K5").values = [[vehicle.lossKm]];
|
||
sheet.getRange("M5").values = [["原申诉里程(km)"]];
|
||
sheet.getRange("N5:P5").merge(); sheet.getRange("N5").values = [[vehicle.appealKm]];
|
||
sheet.getRange("E5:F5").format.numberFormat = "#,##0.0";
|
||
sheet.getRange("H5:I5").format.numberFormat = "#,##0.0";
|
||
sheet.getRange("K5:L5").format.numberFormat = "#,##0.0";
|
||
sheet.getRange("N5:P5").format.numberFormat = "#,##0.0";
|
||
sheet.getRange("K5:L5").format.fill = palette.yellow;
|
||
|
||
sheet.getRange("A6").values = [["异常标记"]];
|
||
sheet.getRange("B6:D6").merge(); sheet.getRange("B6").values = [[vehicle.anomaly]];
|
||
sheet.getRange("E6").values = [["原排查结论"]];
|
||
sheet.getRange("F6:P6").merge(); sheet.getRange("F6").values = [[vehicle.note]];
|
||
for (const address of ["A4", "A5", "A6", "D4", "D5", "E6", "G5", "I4", "J5", "L4", "M5"]) sheet.getRange(address).format = labelFormat;
|
||
|
||
sheet.getRange("A8:P8").format = valueFormat;
|
||
sheet.getRange("A8").values = [["原车辆统计合计(km)"]];
|
||
sheet.getRange("B8:C8").merge(); sheet.getRange("B8").values = [[vehicle.sourceTboxKm]];
|
||
sheet.getRange("D8").values = [["车辆数据中台统计合计(km)"]];
|
||
sheet.getRange("E8:F8").merge(); sheet.getRange("E8").formulas = [["=SUM(B14:B259)"]];
|
||
sheet.getRange("G8").values = [["中台日统计有值天数"]];
|
||
sheet.getRange("H8:I8").merge(); sheet.getRange("H8").formulas = [["=COUNT(B14:B259)"]];
|
||
sheet.getRange("J8").values = [["RAW仪表证据天数"]];
|
||
sheet.getRange("K8:L8").merge(); sheet.getRange("K8").formulas = [["=COUNT(F14:F259)"]];
|
||
sheet.getRange("M8").values = [["RAW证据日期范围"]];
|
||
sheet.getRange("N8:P8").merge(); sheet.getRange("N8").values = [[rawRange(vehicle)]];
|
||
for (const address of ["A8", "D8", "G8", "J8", "M8"]) sheet.getRange(address).format = labelFormat;
|
||
sheet.getRange("B8:C8").format.numberFormat = "#,##0.0";
|
||
sheet.getRange("E8:F8").format.numberFormat = "#,##0.0";
|
||
sheet.getRange("B8:C8").format.fill = palette.blue2;
|
||
sheet.getRange("E8:F8").format.fill = palette.mileageStrong;
|
||
sheet.getRange("E8:F8").format.font = { bold: true, color: "#7F6000", fontSize: 10, typeface: "Microsoft YaHei" };
|
||
sheet.getRange("A8:P8").format.rowHeight = 38;
|
||
|
||
sheet.getRange("A9").values = [["证据限制"]];
|
||
sheet.getRange("B9:P9").merge();
|
||
sheet.getRange("B9").values = [["现行TDengine RAW历史自2026-07-01起可查询；此前仅有历史生产库日统计迁移记录。材料已按A/B/C/D分级，未将统计记录伪称为原始报文。"]];
|
||
sheet.getRange("A9").format = labelFormat;
|
||
sheet.getRange("B9:P9").format = { ...valueFormat, fill: palette.amber };
|
||
sheet.getRange("A9:P9").format.rowHeight = 30;
|
||
|
||
sheet.getRange("A11:P11").merge();
|
||
sheet.getRange("A11").values = [["每日核验明细（完整日历）"]];
|
||
sheet.getRange("A11:P11").format = sectionFormat;
|
||
|
||
sheet.getRange("A12:D12").merge();
|
||
sheet.getRange("A12").values = [["车辆数据中台区间里程总计"]];
|
||
sheet.getRange("E12:F12").merge();
|
||
sheet.getRange("E12").formulas = [["=SUM(B14:B259)"]];
|
||
sheet.getRange("G12:P12").merge();
|
||
sheet.getRange("G12").values = [["计算范围：B14:B259（下方“中台当日里程(km)”列）"]];
|
||
sheet.getRange("A12:D12").format = {
|
||
...headerFormat,
|
||
fill: palette.mileageHeader,
|
||
horizontalAlignment: "left",
|
||
};
|
||
sheet.getRange("E12:F12").format = {
|
||
...valueFormat,
|
||
fill: palette.mileageStrong,
|
||
font: { bold: true, color: "#7F6000", fontSize: 11, typeface: "Microsoft YaHei" },
|
||
numberFormat: "#,##0.0",
|
||
horizontalAlignment: "right",
|
||
};
|
||
sheet.getRange("G12:P12").format = {
|
||
...valueFormat,
|
||
fill: palette.mileageLight,
|
||
font: { color: palette.textMuted, fontSize: 9, typeface: "Microsoft YaHei" },
|
||
};
|
||
sheet.getRange("A12:P12").format.rowHeight = 30;
|
||
|
||
const headers = ["日期", "中台当日里程(km)", "中台日末仪表总里程(km)", "日统计数据源", "日统计来源端点", "RAW解析仪表总里程(km)", "RAW相邻日差(km)", "日统计-RAW差(km)", "证据等级", "报文事件时间", "中台接收时间", "RAW来源端点", "Event ID", "Frame ID", "原始报文大小(bytes)", "原始GB32960报文HEX（完整）"];
|
||
sheet.getRange("A13:P13").values = [headers];
|
||
sheet.getRange("A13:P13").format = headerFormat;
|
||
sheet.getRange("A13:P13").format.rowHeight = 42;
|
||
|
||
const metricByDay = new Map(vehicle.dailyMetrics.map((row) => [row.stat_date, row]));
|
||
const rawByDay = new Map(vehicle.rawEvidence.map((row) => [row.day, row]));
|
||
const days = calendarDays(data.period.start, data.period.end);
|
||
const rows = days.map((day) => {
|
||
const metric = metricByDay.get(day);
|
||
const raw = rawByDay.get(day);
|
||
return [
|
||
asDate(day),
|
||
metric?.daily_mileage_km ?? null,
|
||
metric?.latest_total_mileage_km ?? null,
|
||
metric ? `${metric.platform_name || ""} / ${metric.source_code || ""}` : "",
|
||
metric?.latest_source_endpoint ?? "",
|
||
raw?.status === "OK" ? raw.parsedTotalMileageKm : null,
|
||
null,
|
||
null,
|
||
evidenceLevel(metric, raw),
|
||
raw?.status === "OK" ? raw.eventTime : "",
|
||
raw?.status === "OK" ? raw.receivedAt : "",
|
||
raw?.status === "OK" ? raw.sourceEndpoint : "",
|
||
raw?.status === "OK" ? raw.eventId : "",
|
||
raw?.status === "OK" ? raw.frameId : "",
|
||
raw?.status === "OK" ? raw.rawSizeBytes : null,
|
||
raw?.status === "OK" ? raw.rawHex : "",
|
||
];
|
||
});
|
||
sheet.getRange("A14:P259").values = rows;
|
||
sheet.getRange("A14:A259").format.numberFormat = "yyyy-mm-dd";
|
||
sheet.getRange("B14:C259").format.numberFormat = "#,##0.0";
|
||
sheet.getRange("F14:H259").format.numberFormat = "#,##0.0";
|
||
sheet.getRange("O14:O259").format.numberFormat = "#,##0";
|
||
sheet.getRange("A14:P259").format = {
|
||
font: { color: palette.dark, fontSize: 8, typeface: "Microsoft YaHei" },
|
||
verticalAlignment: "center",
|
||
borders: { insideHorizontal: { style: "thin", color: "#D9E2E8" }, bottom: { style: "thin", color: palette.border } },
|
||
};
|
||
sheet.getRange("A14:A259").format.horizontalAlignment = "center";
|
||
sheet.getRange("B14:C259").format.horizontalAlignment = "right";
|
||
sheet.getRange("F14:H259").format.horizontalAlignment = "right";
|
||
sheet.getRange("I14:O259").format.wrapText = true;
|
||
sheet.getRange("P14:P259").format = { font: { color: palette.muted, fontSize: 7, typeface: "Consolas" }, wrapText: true, verticalAlignment: "top" };
|
||
sheet.getRange("A14:P259").format.rowHeight = 34;
|
||
sheet.getRange("G14").values = [[null]];
|
||
for (let row = 15; row <= 259; row += 1) {
|
||
sheet.getRange(`G${row}`).formulas = [[`=IF(OR(F${row}="",F${row - 1}=""),"",F${row}-F${row - 1})`]];
|
||
}
|
||
for (let row = 14; row <= 259; row += 1) {
|
||
sheet.getRange(`H${row}`).formulas = [[`=IF(OR(C${row}="",F${row}=""),"",C${row}-F${row})`]];
|
||
}
|
||
|
||
sheet.getRange("I14:I259").conditionalFormats.add("containsText", { text: "A-", format: { fill: palette.green, font: { color: "#375623", bold: true } } });
|
||
sheet.getRange("I14:I259").conditionalFormats.add("containsText", { text: "C-", format: { fill: palette.yellow, font: { color: "#7F6000" } } });
|
||
sheet.getRange("I14:I259").conditionalFormats.add("containsText", { text: "D-", format: { fill: palette.gray, font: { color: palette.muted } } });
|
||
sheet.getRange("H14:H259").conditionalFormats.addCustom("=ABS($H14)>0.1", { fill: palette.amber, font: { color: "#9C0006", bold: true } });
|
||
|
||
const table = sheet.tables.add("A13:P259", true, `Vehicle${String(vehicle.seq).padStart(2, "0")}Evidence`);
|
||
table.style = "TableStyleMedium2";
|
||
table.showBandedColumns = false;
|
||
table.showFilterButton = true;
|
||
sheet.getRange("B13").format = { ...headerFormat, fill: palette.mileageHeader };
|
||
sheet.getRange("B14:B259").format.fill = palette.mileageLight;
|
||
sheet.getRange("B14:B259").conditionalFormats.add("cellIs", {
|
||
operator: "greaterThan",
|
||
formula: 0,
|
||
format: { fill: palette.mileageStrong, font: { bold: true, color: "#7F6000" } },
|
||
});
|
||
|
||
const widths = [12, 17, 20, 24, 26, 20, 17, 18, 25, 20, 20, 22, 34, 34, 14, 72];
|
||
for (let col = 0; col < widths.length; col += 1) sheet.getRangeByIndexes(0, col, 259, 1).format.columnWidth = widths[col];
|
||
}
|
||
|
||
summary.showGridLines = false;
|
||
summary.freezePanes.freezeRows(4);
|
||
summary.getRange("A1:P1").merge();
|
||
summary.getRange("A1").values = [["15辆车里程核验申诉总览"]];
|
||
summary.getRange("A1:P1").format = titleFormat;
|
||
summary.getRange("A1:P1").format.rowHeight = 32;
|
||
summary.getRange("A2:P2").merge();
|
||
summary.getRange("A2").values = [["结论：车辆数据中台GB32960日里程复核合计与原异常表TBOX区间里程逐车一致；第三方平台合计显著偏低。RAW原始帧按现有可查历史提供，早期记录已明确标注证据限制。"]];
|
||
summary.getRange("A2:P2").format = { fill: palette.green, font: { bold: true, color: "#375623", fontSize: 10, typeface: "Microsoft YaHei" }, wrapText: true, verticalAlignment: "center", borders: thinBorders };
|
||
summary.getRange("A2:P2").format.rowHeight = 34;
|
||
|
||
summary.getRange("A3:B3").merge(); summary.getRange("A3").values = [["全部15辆车·原车辆统计(km)"]];
|
||
summary.getRange("C3").formulas = [["=F20"]];
|
||
summary.getRange("D3:E3").merge(); summary.getRange("D3").values = [["车辆数据中台统计(km)"]];
|
||
summary.getRange("F3").formulas = [["=G20"]];
|
||
summary.getRange("G3:H3").merge(); summary.getRange("G3").values = [["第三方平台统计(km)"]];
|
||
summary.getRange("I3").formulas = [["=H20"]];
|
||
summary.getRange("J3:K3").merge(); summary.getRange("J3").values = [["平台漏计差异(km)"]];
|
||
summary.getRange("L3").formulas = [["=I20"]];
|
||
summary.getRange("M3:N3").merge(); summary.getRange("M3").values = [["RAW仪表证据(天)"]];
|
||
summary.getRange("O3:P3").merge(); summary.getRange("O3").formulas = [["=M20"]];
|
||
for (const address of ["A3:B3", "D3:E3", "G3:H3", "J3:K3", "M3:N3"]) summary.getRange(address).format = labelFormat;
|
||
for (const address of ["C3", "F3", "I3", "L3", "O3:P3"]) summary.getRange(address).format = { ...valueFormat, fill: palette.mileageLight, font: { bold: true, color: "#7F6000", fontSize: 10, typeface: "Microsoft YaHei" }, horizontalAlignment: "right" };
|
||
summary.getRange("C3:L3").format.numberFormat = "#,##0.0";
|
||
summary.getRange("O3:P3").format.numberFormat = "#,##0";
|
||
summary.getRange("A3:P3").format.rowHeight = 34;
|
||
|
||
const summaryHeaders = ["序号", "车型", "车牌", "VIN", "核验区间", "原TBOX合计(km)", "中台复核合计(km)", "第三方平台合计(km)", "损耗(km)", "损耗率", "申诉里程(km)", "日统计天数", "RAW证据天数", "RAW证据范围", "核验结论", "原排查结论"];
|
||
summary.getRange("A4:P4").values = [summaryHeaders];
|
||
summary.getRange("A4:P4").format = headerFormat;
|
||
summary.getRange("A4:P4").format.rowHeight = 40;
|
||
const summaryRows = data.vehicles.map((vehicle) => [
|
||
vehicle.seq, vehicle.model, vehicle.plate, vehicle.vin, "2025-11-28 至 2026-07-31", vehicle.sourceTboxKm, null, vehicle.platformKm, null, null, vehicle.appealKm, null, null, rawRange(vehicle), null, vehicle.note,
|
||
]);
|
||
summary.getRange("A5:P19").values = summaryRows;
|
||
for (let index = 0; index < data.vehicles.length; index += 1) {
|
||
const row = 5 + index;
|
||
const { sheetName } = vehicleSheets.get(data.vehicles[index].vin);
|
||
summary.getRange(`G${row}`).formulas = [[`='${sheetName}'!E8`]];
|
||
summary.getRange(`I${row}`).formulas = [[`=F${row}-H${row}`]];
|
||
summary.getRange(`J${row}`).formulas = [[`=IF(F${row}=0,"",I${row}/F${row})`]];
|
||
summary.getRange(`L${row}`).formulas = [[`='${sheetName}'!H8`]];
|
||
summary.getRange(`M${row}`).formulas = [[`='${sheetName}'!K8`]];
|
||
summary.getRange(`O${row}`).formulas = [[`=IF(ABS(G${row}-F${row})<0.1,"中台复核合计与原TBOX一致","需复核中台合计")`]];
|
||
}
|
||
summary.getRange("A20:E20").merge(); summary.getRange("A20").values = [["合计"]];
|
||
summary.getRange("F20").formulas = [["=SUM(F5:F19)"]];
|
||
summary.getRange("G20").formulas = [["=SUM(G5:G19)"]];
|
||
summary.getRange("H20").formulas = [["=SUM(H5:H19)"]];
|
||
summary.getRange("I20").formulas = [["=SUM(I5:I19)"]];
|
||
summary.getRange("J20").formulas = [["=IF(F20=0,\"\",I20/F20)"]];
|
||
summary.getRange("K20").formulas = [["=SUM(K5:K19)"]];
|
||
summary.getRange("L20").formulas = [["=SUM(L5:L19)"]];
|
||
summary.getRange("M20").formulas = [["=SUM(M5:M19)"]];
|
||
summary.getRange("N20:P20").merge(); summary.getRange("N20").values = [["15车合计；RAW证据为现行历史可查询范围"]];
|
||
summary.getRange("A20:P20").format = { fill: palette.blue, font: { bold: true, color: palette.dark, fontSize: 9, typeface: "Microsoft YaHei" }, borders: thinBorders, verticalAlignment: "center", wrapText: true };
|
||
summary.getRange("A5:P19").format = { font: { color: palette.dark, fontSize: 9, typeface: "Microsoft YaHei" }, verticalAlignment: "center", wrapText: true, borders: { insideHorizontal: { style: "thin", color: "#D9E2E8" }, bottom: { style: "thin", color: palette.border } } };
|
||
summary.getRange("F5:I20").format.numberFormat = "#,##0.0";
|
||
summary.getRange("J5:J20").format.numberFormat = "0.00%";
|
||
summary.getRange("K5:K20").format.numberFormat = "#,##0.0";
|
||
summary.getRange("L5:M20").format.numberFormat = "#,##0";
|
||
summary.getRange("I5:I19").format.fill = palette.yellow;
|
||
summary.getRange("O5:O19").conditionalFormats.add("containsText", { text: "一致", format: { fill: palette.green, font: { color: "#375623", bold: true } } });
|
||
const summaryTable = summary.tables.add("A4:P19", true, "AppealSummary");
|
||
summaryTable.style = "TableStyleMedium2";
|
||
summaryTable.showFilterButton = true;
|
||
const summaryWidths = [8, 9, 14, 22, 22, 16, 18, 18, 15, 12, 16, 13, 14, 24, 26, 54];
|
||
for (let col = 0; col < summaryWidths.length; col += 1) summary.getRangeByIndexes(0, col, 20, 1).format.columnWidth = summaryWidths[col];
|
||
summary.getRange("A5:P19").format.rowHeight = 42;
|
||
|
||
notes.showGridLines = false;
|
||
notes.getRange("A1:H1").merge();
|
||
notes.getRange("A1").values = [["核验口径与证据说明"]];
|
||
notes.getRange("A1:H1").format = titleFormat;
|
||
notes.getRange("A1:H1").format.rowHeight = 32;
|
||
notes.getRange("A3:H3").merge(); notes.getRange("A3").values = [["1. 核验口径"]]; notes.getRange("A3:H3").format = sectionFormat;
|
||
notes.getRange("A4:B8").values = [
|
||
["协议", "GB/T 32960"],
|
||
["仪表总里程字段", "gb32960.vehicle.total_mileage_km"],
|
||
["仪表总里程含义", "车辆仪表盘累计总里程，单位 km；不同协议累计值不可拼接。"],
|
||
["中台当日里程", "生产 vehicle_daily_mileage 的 GB32960 日统计值；本材料逐车求和后与原异常表TBOX合计核对。"],
|
||
["RAW相邻日差", "仅相邻两个自然日都存在RAW仪表读数时计算；缺任一日则留空，不把跨日缺口跳变塞进单日。"],
|
||
];
|
||
notes.getRange("A4:A8").format = labelFormat;
|
||
notes.getRange("B4:H8").merge(true);
|
||
notes.getRange("B4:H8").format = valueFormat;
|
||
notes.getRange("A10:H10").merge(); notes.getRange("A10").values = [["2. 证据分级"]]; notes.getRange("A10:H10").format = sectionFormat;
|
||
notes.getRange("A11:B14").values = [
|
||
["A-原始32960报文", "中台TDengine保存的完整GB32960十六进制报文，且解析结果明确包含仪表总里程字段。"],
|
||
["B-中台32960日统计", "中台日统计存在，但当日未检出含仪表总里程的RAW帧。"],
|
||
["C-历史日统计迁移", "来自历史生产库 lingniu_prod.ln_vehicle_day_mileage 的迁移记录，不是原始帧。"],
|
||
["D-无中台日记录", "当日中台日统计无记录，且现行RAW历史不可用或未检出。"],
|
||
];
|
||
notes.getRange("A11:A14").format = labelFormat;
|
||
notes.getRange("B11:H14").merge(true);
|
||
notes.getRange("B11:H14").format = valueFormat;
|
||
notes.getRange("A11:H11").format.fill = palette.green;
|
||
notes.getRange("A13:H13").format.fill = palette.yellow;
|
||
notes.getRange("A14:H14").format.fill = palette.gray;
|
||
|
||
notes.getRange("A16:H16").merge(); notes.getRange("A16").values = [["3. 数据来源与限制"]]; notes.getRange("A16:H16").format = sectionFormat;
|
||
notes.getRange("A17:B21").values = [
|
||
["原异常表", "/Users/lingniu/Documents/里程异常统计表_20251128-20260731-回复.xlsx"],
|
||
["中台日统计API", "http://115.29.187.205:20200/api/stats/daily-metrics"],
|
||
["中台RAW历史API", "http://115.29.187.205:20200/api/history/raw-frames"],
|
||
["RAW可查范围", "本次提取范围2026-07-01至2026-07-31；15车共取得334个含仪表总里程字段的完整原始帧。"],
|
||
["重要限制", "2026-07-01之前现行TDengine没有可查询RAW历史；只能提供历史生产库日统计迁移记录。对外申诉时应如实说明，并向原TSP/车厂补调更早期原始报文归档。"],
|
||
];
|
||
notes.getRange("A17:A21").format = labelFormat;
|
||
notes.getRange("B17:H21").merge(true);
|
||
notes.getRange("B17:H21").format = valueFormat;
|
||
notes.getRange("A21:H21").format.fill = palette.amber;
|
||
|
||
notes.getRange("A23:H23").merge(); notes.getRange("A23").values = [["4. 使用建议"]]; notes.getRange("A23:H23").format = sectionFormat;
|
||
notes.getRange("A24:H27").values = [
|
||
["① 先提交“申诉总览”，证明15车中台GB32960日里程合计与原TBOX合计一致。", null, null, null, null, null, null, null],
|
||
["② 每车附对应Sheet，重点引用A类RAW证据、事件时间、接收时间和完整HEX。", null, null, null, null, null, null, null],
|
||
["③ 对第三方缺失日，用完整日历中的D级空缺、前后仪表累计变化和原排查结论说明漏传。", null, null, null, null, null, null, null],
|
||
["④ 申诉若要求全区间逐日原始报文，需由原TSP/车厂补调2026-07-01之前归档；本材料未虚构缺失证据。", null, null, null, null, null, null, null],
|
||
];
|
||
for (let row = 24; row <= 27; row += 1) notes.getRange(`A${row}:H${row}`).merge();
|
||
notes.getRange("A24:H27").format = { ...valueFormat, fill: palette.blue2 };
|
||
notes.getRange("A4:H27").format.rowHeight = 30;
|
||
notes.getRange("A1:A27").format.columnWidth = 22;
|
||
for (let col = 1; col < 8; col += 1) notes.getRangeByIndexes(0, col, 27, 1).format.columnWidth = 18;
|
||
|
||
await fs.mkdir(outputDir, { recursive: true });
|
||
await fs.mkdir(previewDir, { recursive: true });
|
||
|
||
const inspection = {};
|
||
inspection.summary = (await workbook.inspect({ kind: "table", range: "申诉总览!A1:P20", include: "values,formulas", tableMaxRows: 24, tableMaxCols: 16, maxChars: 22000 })).ndjson;
|
||
inspection.formulaErrors = (await workbook.inspect({ kind: "match", searchTerm: "#REF!|#DIV/0!|#VALUE!|#NAME\\?|#N/A", options: { useRegex: true, maxResults: 300 }, summary: "final formula error scan" })).ndjson;
|
||
await fs.writeFile(path.join(outputDir, "verification.ndjson"), `${inspection.summary}\n${inspection.formulaErrors}\n`, "utf8");
|
||
|
||
const previewSheets = ["申诉总览", "口径与证据说明", ...data.vehicles.map((vehicle) => vehicleSheets.get(vehicle.vin).sheetName)];
|
||
for (let index = 0; index < previewSheets.length; index += 1) {
|
||
const sheetName = previewSheets[index];
|
||
const range = sheetName === "申诉总览" ? "A1:P20" : sheetName === "口径与证据说明" ? "A1:H27" : "A1:P24";
|
||
const preview = await workbook.render({ sheetName, range, scale: 1, format: "png" });
|
||
const safeName = sheetName.replaceAll(/[\\/:*?\[\]]/g, "_");
|
||
await fs.writeFile(path.join(previewDir, `${String(index + 1).padStart(2, "0")}-${safeName}.png`), new Uint8Array(await preview.arrayBuffer()));
|
||
}
|
||
|
||
const output = await SpreadsheetFile.exportXlsx(workbook);
|
||
await output.save(outputPath);
|
||
console.log(JSON.stringify({ outputPath, sheets: previewSheets.length, previewDir }, null, 2));
|