1// UQO Éval — Métré : exports CSV et XLSX (exceljs, MIT) d'une révision immuable. SERVEUR seulement.2import ExcelJS from "exceljs";3import type { EstimateVersion, Measurement, Project } from "./types";45const csvCell = (v: unknown): string => { const s = v == null ? "" : String(v); return /[",\n;]/.test(s) ? `"${s.replace(/"/g, '""')}"` : s; };67export function versionToCsv(v: EstimateVersion, lang: "fr" | "en"): string {8 const fr = lang === "fr";9 const head = fr ? ["division", "description", "assemblage", "mesure_id", "quantite_metre", "facteur", "quantite", "unite", "base", "unitaire_materiau", "unitaire_mo", "unitaire_equipement", "unitaire_autres", "unitaire_direct", "materiau", "mo", "equipement", "autres", "direct", "localise", "nature_prix", "source", "date_observation", "confiance", "part_observee", "exclue", "avertissements"]10 : ["division", "description", "assembly", "measurement_id", "takeoff_qty", "factor", "quantity", "unit", "basis", "unit_material", "unit_labour", "unit_equipment", "unit_other", "unit_direct", "material", "labour", "equipment", "other", "direct", "located", "price_kind", "source", "observed_at", "confidence", "observed_share", "excluded", "warnings"];11 const rows = v.result.lines.map((l) => [l.wbs, l.description, l.assemblyCode, l.measurementId, l.takeoffQuantity, l.adjustmentFactor, l.quantity, l.unit, l.basis, l.unitMaterial, l.unitLabour, l.unitEquipment, l.unitOther, l.unitDirect, l.material, l.labour, l.equipment, l.other, l.direct, l.located, l.provenance.kind, l.provenance.source, l.provenance.observedAt, l.provenance.confidence, l.provenance.observedShare, l.excluded ? 1 : 0, l.warnings.join(" | ")]);12 const T = v.result.totals;13 const meta = [["# UQO Éval — Métré", `${fr ? "révision" : "revision"} ${v.number} — ${v.label}`, v.createdAt, `sha256:${v.contentHash}`], ["# catalogue", v.result.catalogueVersion, "# formules", v.result.formulaVersion], ["# total", T.total, "# direct_localise", T.located, "# avant_taxes", T.subtotalBeforeTax]];14 return [...meta, head, ...rows].map((r) => r.map(csvCell).join(",")).join("\n") + "\n";15}1617export async function versionToXlsx(p: Project, v: EstimateVersion, measurements: Measurement[], lang: "fr" | "en"): Promise<Buffer> {18 const fr = lang === "fr";19 const wb = new ExcelJS.Workbook();20 wb.creator = "UQO Éval — Métré"; wb.created = new Date();21 const r = v.result;22 const s1 = wb.addWorksheet(fr ? "Résumé" : "Summary");23 s1.columns = [{ width: 48 }, { width: 22 }];24 s1.addRows([[fr ? "Projet" : "Project", p.name], [fr ? "Adresse" : "Address", [p.address, p.municipality].filter(Boolean).join(", ")], [fr ? "Révision" : "Revision", `${v.number} — ${v.label}`], [fr ? "Figée le" : "Frozen on", v.createdAt], ["SHA-256", v.contentHash], [fr ? "Catalogue" : "Catalogue", r.catalogueVersion], [fr ? "Formules" : "Formulas", r.formulaVersion], [fr ? "Prix au" : "Prices as of", r.params.priceDate], [fr ? "Région" : "Region", `${r.location.code} (M ${r.location.materialFactor} · MO ${r.location.labourFactor} · É ${r.location.equipmentFactor})`], []]);25 const T = r.totals;26 const tot: [string, number][] = [[fr ? "Direct" : "Direct", T.direct], [fr ? "Ajustement régional" : "Regional adjustment", T.locationAdjustment], [fr ? "Direct localisé" : "Located direct", T.located], [`${fr ? "Conditions générales" : "General conditions"} ${r.params.generalConditionsPct} %`, T.generalConditions], [`${fr ? "Frais généraux" : "Overhead"} ${r.params.overheadPct} %`, T.overhead], [`Profit ${r.params.profitPct} %`, T.profit], [`${fr ? "Contingence" : "Contingency"} ${r.params.contingencyPct} %`, T.contingency], [fr ? "Actualisation" : "Escalation", T.escalation], [fr ? "Sous-total avant taxes" : "Subtotal before tax", T.subtotalBeforeTax], ["TPS/GST", T.gst], ["TVQ/QST", T.qst], ["TOTAL", T.total]];27 for (const [k, val] of tot) { const row = s1.addRow([k, val]); row.getCell(2).numFmt = '#,##0.00 "$"'; }28 s1.addRow([]); s1.addRow([fr ? "Ordre d'application" : "Application order"]); for (const o of r.applicationOrder) s1.addRow([o]);29 s1.getRow(1).font = { bold: true };3031 const s2 = wb.addWorksheet(fr ? "Lignes" : "Lines");32 s2.columns = (fr ? ["Division", "Description", "Assemblage", "Qté métré", "Facteur", "Quantité", "Unité", "Base", "Unit. matériau", "Unit. MO", "Unit. équip.", "Unit. autres", "Unit. direct", "Matériau", "MO", "Équipement", "Autres", "Direct", "Localisé", "Nature", "Source", "Observé le", "Confiance", "Part observée", "Exclue", "Avertissements"]33 : ["Division", "Description", "Assembly", "Takeoff qty", "Factor", "Quantity", "Unit", "Basis", "Unit material", "Unit labour", "Unit equip.", "Unit other", "Unit direct", "Material", "Labour", "Equipment", "Other", "Direct", "Located", "Kind", "Source", "Observed", "Confidence", "Observed share", "Excluded", "Warnings"]).map((h, i) => ({ header: h, key: `c${i}`, width: i === 1 ? 44 : i === 20 ? 36 : 14 }));34 for (const l of r.lines) s2.addRow([l.wbs, l.description, l.assemblyCode, l.takeoffQuantity, l.adjustmentFactor, l.quantity, l.unit, l.basis, l.unitMaterial, l.unitLabour, l.unitEquipment, l.unitOther, l.unitDirect, l.material, l.labour, l.equipment, l.other, l.direct, l.located, l.provenance.kind, l.provenance.source, l.provenance.observedAt, l.provenance.confidence, l.provenance.observedShare, l.excluded ? 1 : 0, l.warnings.join(" | ")]);35 s2.getRow(1).font = { bold: true };36 for (const c of [9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19]) s2.getColumn(c).numFmt = '#,##0.00';3738 const s3 = wb.addWorksheet(fr ? "Métré" : "Takeoff");39 s3.columns = (fr ? ["Id", "Mesure", "Formule", "Entrées", "Valeur", "Unité", "Cal. révision", "Périmée", "Confiance", "Vérifiée le", "Calculée le", "Lignage"] : ["Id", "Measurement", "Formula", "Inputs", "Value", "Unit", "Cal. revision", "Stale", "Confidence", "Verified at", "Computed at", "Lineage"]).map((h, i) => ({ header: h, key: `m${i}`, width: i === 3 ? 48 : 16 }));40 for (const m of measurements) s3.addRow([m.id, m.name, m.formulaText, JSON.stringify(m.inputs), m.value, m.unit, m.calibrationRevision, m.stale ? 1 : 0, m.confidence, m.verifiedAt, m.computedAt, m.lineage.join(" ; ")]);41 s3.getRow(1).font = { bold: true };4243 const s4 = wb.addWorksheet(fr ? "Divisions" : "Divisions");44 s4.columns = [{ header: "WBS", key: "w", width: 30 }, { header: fr ? "Direct" : "Direct", key: "d", width: 16 }, { header: fr ? "Localisé" : "Located", key: "l", width: 16 }, { header: "%", key: "p", width: 8 }];45 for (const w of r.byWbs) s4.addRow([w.wbs, w.direct, w.located, w.sharePct]);46 s4.getRow(1).font = { bold: true };47 const out = await wb.xlsx.writeBuffer();48 return Buffer.from(out as ArrayBuffer);49}50