Repository Public
1// UQO Éval — exports génériques (CSV, XLSX, PDF) de tout résultat généré par la plateforme, toujours avec la mention UQO Éval.2/**3 * Un résultat = objet JSON quelconque. Règles de sérialisation :4 * - scalaires de premier niveau → feuille « Résumé » (clé, valeur) ;5 * - tableaux d'objets → une feuille par clé (colonnes = union des clés ; sous-objets aplatis « a.b ») ;6 * - objets imbriqués → explorés récursivement (préfixe de clé), profondeur ≤ 3 ;7 * - libellés bilingues {fr,en} → langue demandée.8 * Chaque fichier porte l'en-tête « UQO Éval — laboratoire pédagogique d'évaluation immobilière », le titre,9 * la date de génération, l'URL source et la mention pédagogique IMM1003 · IMM1033.10 */11import ExcelJS from "exceljs";12import PDFDocument from "pdfkit";13import path from "path";1415export const UQO_MENTION_FR = "UQO Éval — laboratoire pédagogique d'évaluation immobilière · cours IMM1003 et IMM1033, Simon-Pierre Boucher (UQO) · estimation statistique à titre indicatif, ni évaluation certifiée (OEAQ) ni soumission";16export const UQO_MENTION_EN = "UQO Éval — real-estate valuation teaching laboratory · courses IMM1003 and IMM1033, Simon-Pierre Boucher (UQO) · indicative statistical estimate, neither a certified appraisal (OEAQ) nor a bid";1718export interface ExportMeta { title: string; subtitle?: string | null; source: string; lang: "fr" | "en"; generated?: string }19export interface Sheet { name: string; columns: string[]; rows: (string | number | null)[][] }2021const isTxt = (v: unknown): v is { fr: string; en: string } => !!v && typeof v === "object" && typeof (v as { fr?: unknown }).fr === "string" && typeof (v as { en?: unknown }).en === "string";22const scalar = (v: unknown, lang: "fr" | "en"): string | number | null => v == null ? null : typeof v === "number" ? (Number.isFinite(v) ? Math.round(v * 1e6) / 1e6 : null) : typeof v === "boolean" ? (v ? 1 : 0) : typeof v === "string" ? v : isTxt(v) ? v[lang] : Array.isArray(v) ? (v.every((x) => typeof x !== "object") ? v.join(" | ") : `[${v.length}]`) : JSON.stringify(v).slice(0, 500);2324function flatten(o: Record<string, unknown>, lang: "fr" | "en", prefix = "", depth = 0): Record<string, string | number | null> {25 const out: Record<string, string | number | null> = {};26 for (const [k, v] of Object.entries(o)) {27 const key = prefix ? `${prefix}.${k}` : k;28 if (v && typeof v === "object" && !Array.isArray(v) && !isTxt(v) && depth < 2) Object.assign(out, flatten(v as Record<string, unknown>, lang, key, depth + 1));29 else out[key] = scalar(v, lang);30 }31 return out;32}3334/** Transforme un résultat en feuilles (Résumé + une feuille par tableau d'objets). */35export function toSheets(result: unknown, lang: "fr" | "en", opts: { maxRows?: number; skip?: string[] } = {}): Sheet[] {36 const maxRows = opts.maxRows ?? 50000; const skip = new Set(opts.skip ?? []);37 const summary: (string | number | null)[][] = []; const sheets: Sheet[] = [];38 const walk = (o: Record<string, unknown>, prefix: string, depth: number) => {39 for (const [k, v] of Object.entries(o)) {40 const key = prefix ? `${prefix}.${k}` : k;41 if (skip.has(key) || skip.has(k)) continue;42 if (Array.isArray(v)) {43 if (!v.length) continue;44 if (v.every((x) => x == null || typeof x !== "object")) { summary.push([key, v.slice(0, 200).map((x) => scalar(x, lang)).join(" | ")]); continue; }45 // tableaux imbriqués dans les lignes (ex. séries → points) : hissés en feuilles propres « clé.étiquette »46 const items = v.slice(0, maxRows).map((x, i) => {47 if (!x || typeof x !== "object") return { value: scalar(x, lang) } as Record<string, unknown>;48 const row: Record<string, unknown> = { ...(x as Record<string, unknown>) };49 for (const [fk, fv] of Object.entries(row)) {50 if (Array.isArray(fv) && fv.length && fv.every((y) => y && typeof y === "object")) {51 const lab = scalar(row.key ?? row.id ?? row.label ?? row.name ?? row.code ?? i, lang);52 const sub = (fv as Record<string, unknown>[]).map((y) => flatten(y, lang));53 const cols = [...new Set(sub.flatMap((r) => Object.keys(r)))];54 sheets.push({ name: `${k}.${String(lab)}`.slice(0, 31), columns: cols, rows: sub.map((r) => cols.map((c) => r[c] ?? null)) });55 row[fk] = `[${fv.length} → ${k}.${String(lab)}]`;56 }57 }58 return row;59 });60 const rows = items.map((x) => flatten(x, lang));61 const cols = [...new Set(rows.flatMap((r) => Object.keys(r)))];62 sheets.push({ name: key.slice(0, 31), columns: cols, rows: rows.map((r) => cols.map((c) => r[c] ?? null)) });63 } else if (v && typeof v === "object" && !isTxt(v)) { if (depth < 3) walk(v as Record<string, unknown>, key, depth + 1); else summary.push([key, scalar(v, lang)]); }64 else summary.push([key, scalar(v, lang)]);65 }66 };67 if (result && typeof result === "object" && !Array.isArray(result)) walk(result as Record<string, unknown>, "", 0);68 else if (Array.isArray(result)) walk({ data: result }, "", 0);69 else summary.push(["value", scalar(result, lang)]);70 return [{ name: lang === "fr" ? "Résumé" : "Summary", columns: [lang === "fr" ? "clé" : "key", lang === "fr" ? "valeur" : "value"], rows: summary }, ...sheets];71}7273const csvCell = (v: unknown): string => { const s = v == null ? "" : String(v); return /[",\n;]/.test(s) ? `"${s.replace(/"/g, '""')}"` : s; };7475export function sheetsToCsv(sheets: Sheet[], meta: ExportMeta): string {76 const gen = meta.generated ?? new Date().toISOString();77 const head = [`# ${meta.lang === "fr" ? UQO_MENTION_FR : UQO_MENTION_EN}`, `# ${meta.title}${meta.subtitle ? ` — ${meta.subtitle}` : ""}`, `# ${meta.lang === "fr" ? "généré le" : "generated on"} ${gen} · ${meta.source}`, ""];78 const parts: string[] = [...head];79 for (const s of sheets) { parts.push(`# ${s.name}`); parts.push(s.columns.map(csvCell).join(",")); for (const r of s.rows) parts.push(r.map(csvCell).join(",")); parts.push(""); }80 return "" + parts.join("\n");81}8283export async function sheetsToXlsx(sheets: Sheet[], meta: ExportMeta): Promise<Buffer> {84 const wb = new ExcelJS.Workbook(); wb.creator = "UQO Éval"; wb.created = new Date();85 const gen = meta.generated ?? new Date().toISOString();86 const cover = wb.addWorksheet("UQO Éval");87 cover.columns = [{ width: 28 }, { width: 90 }];88 cover.addRows([["UQO Éval", meta.lang === "fr" ? "Laboratoire pédagogique d'évaluation immobilière — Université du Québec en Outaouais" : "Real-estate valuation teaching laboratory — Université du Québec en Outaouais"], [meta.lang === "fr" ? "Titre" : "Title", meta.title], [meta.lang === "fr" ? "Sous-titre" : "Subtitle", meta.subtitle ?? ""], [meta.lang === "fr" ? "Généré le" : "Generated on", gen], ["Source", meta.source], [meta.lang === "fr" ? "Mention" : "Notice", meta.lang === "fr" ? UQO_MENTION_FR : UQO_MENTION_EN], [meta.lang === "fr" ? "Feuilles" : "Sheets", sheets.map((s) => s.name).join(", ")]]);89 cover.getCell("A1").font = { bold: true, size: 16, color: { argb: "FF006085" } }; cover.getColumn(1).font = { bold: true };90 const used = new Set<string>(["UQO Éval"]);91 for (const s of sheets) {92 let name = s.name.replace(/[\\/*?:[\]]/g, "_").slice(0, 28) || "feuille"; let i = 1; while (used.has(name)) name = `${name.slice(0, 25)}_${i++}`; used.add(name);93 const ws = wb.addWorksheet(name);94 ws.columns = s.columns.map((c) => ({ header: c, key: c, width: Math.min(40, Math.max(10, c.length + 2)) }));95 for (const r of s.rows) ws.addRow(r);96 ws.getRow(1).font = { bold: true }; ws.views = [{ state: "frozen", ySplit: 1 }];97 }98 return Buffer.from((await wb.xlsx.writeBuffer()) as ArrayBuffer);99}100101/* ------------------------------------------------------------------ PDF */102103/**104 * JetBrains Mono : les ligatures (« // », « .. », « -- », « -> »…) font planter fontkit (build CJS chargé par Next) —105 * « Offset is outside the bounds of the DataView ». On désactive liga/calt/dlig sur tout texte du document.106 */107export function disableLigatures(doc: PDFKit.PDFDocument): void {108 const NOLIG = { liga: false, calt: false, dlig: false };109 const d = doc as unknown as Record<"text" | "widthOfString" | "heightOfString", (...a: unknown[]) => unknown>;110 for (const m of ["text", "widthOfString", "heightOfString"] as const) {111 const orig = d[m].bind(doc) as (...a: unknown[]) => unknown;112 d[m] = (...a: unknown[]) => {113 const last = a[a.length - 1];114 if (last && typeof last === "object") a[a.length - 1] = { features: NOLIG, ...(last as object) };115 else a.push({ features: NOLIG });116 return orig(...a);117 };118 }119}120121const INK = "#16181b", INK2 = "#4b5258", INK3 = "#8b928c", PAPER = "#f4f5f3", SURFACE2 = "#f9fafa", BLUE = "#005F85", TURQ = "#2BCCAE", LINE = "#dedcd4";122const W = 612, H = 792, M = 44, BOTTOM = H - 62;123const F = (f: string) => path.join(process.cwd(), "assets", "fonts", f);124const LOGO = path.join(process.cwd(), "assets", "uqo", "UQO_blanc.png");125126export async function sheetsToPdf(sheets: Sheet[], meta: ExportMeta, opts: { maxRowsPerSheet?: number; maxCols?: number } = {}): Promise<Buffer> {127 const doc = new PDFDocument({ size: "LETTER", margin: 0, autoFirstPage: false, bufferPages: true, info: { Title: meta.title, Author: "UQO Éval" } });128 doc.registerFont("Inter", F("Inter-Regular.ttf")); doc.registerFont("SG-Bold", F("SpaceGrotesk-Bold.ttf")); doc.registerFont("JB-Reg", F("JetBrainsMono-Regular.ttf")); doc.registerFont("JB-Bold", F("JetBrainsMono-Bold.ttf"));129 disableLigatures(doc);130 const chunks: Buffer[] = []; doc.on("data", (c: Buffer) => chunks.push(c));131 const done = new Promise<Buffer>((res) => doc.on("end", () => res(Buffer.concat(chunks))));132 const fr = meta.lang === "fr"; const gen = meta.generated ?? new Date().toISOString();133 let y = 34;134 const newPage = () => { doc.addPage({ size: "LETTER", margin: 0 }); doc.rect(0, 0, W, H).fill(PAPER); doc.rect(0, 0, W, 8).fill(INK); doc.rect(0, 8, W, 2.5).fill(TURQ); y = 34; };135 const ensure = (h: number) => { if (y + h > BOTTOM) newPage(); };136 // couverture137 doc.addPage({ size: "LETTER", margin: 0 }); doc.rect(0, 0, W, H).fill(PAPER); doc.rect(0, 0, W, 190).fill(INK); doc.rect(0, 190, W, 3).fill(TURQ);138 doc.image(LOGO, M, 30, { height: 26 }); doc.font("SG-Bold").fontSize(22).fillColor(TURQ).text("Éval", M + 26 * (2123 / 853) + 8, 34, { lineBreak: false });139 doc.font("JB-Bold").fontSize(7.5).fillColor(TURQ).text((fr ? "LABORATOIRE PÉDAGOGIQUE D'ÉVALUATION IMMOBILIÈRE — UQO" : "REAL-ESTATE VALUATION TEACHING LABORATORY — UQO"), M, 78, { characterSpacing: 1.6 });140 doc.font("SG-Bold").fontSize(22).fillColor("#ffffff").text(meta.title, M, 94, { width: W - 2 * M });141 if (meta.subtitle) doc.font("Inter").fontSize(9.5).fillColor("#c9d1d4").text(meta.subtitle, M, doc.y + 4, { width: W - 2 * M });142 doc.font("JB-Reg").fontSize(7).fillColor("#9aa5aa").text(`${fr ? "généré le" : "generated on"} ${gen} · ${meta.source}`, M, 170, { width: W - 2 * M });143 y = 210;144 const maxRows = opts.maxRowsPerSheet ?? 80, maxCols = opts.maxCols ?? 8;145 for (const s of sheets) {146 ensure(50);147 doc.rect(M, y + 3.5, 20, 2).fill(BLUE); doc.font("JB-Bold").fontSize(8).fillColor(BLUE).text(s.name.toUpperCase(), M + 26, y, { characterSpacing: 1.4, lineBreak: false }); y += 18;148 const cols = s.columns.slice(0, maxCols); const extra = s.columns.length - cols.length;149 const rows = s.rows.slice(0, maxRows);150 const isSummary = cols.length === 2 && (s.columns[0] === "clé" || s.columns[0] === "key");151 const ws = isSummary ? [200, W - 2 * M - 200] : cols.map(() => (W - 2 * M) / cols.length);152 const rowH = 13;153 const header = () => { ensure(rowH + 2); doc.rect(M, y, W - 2 * M, rowH).fill(INK); let x = M; cols.forEach((c, i) => { doc.font("JB-Bold").fontSize(6).fillColor(PAPER).text(String(c).toUpperCase().slice(0, 40), x + 4, y + 3.8, { width: ws[i] - 8, lineBreak: false, height: rowH, ellipsis: true }); x += ws[i]; }); y += rowH; };154 header();155 rows.forEach((r, ri) => {156 if (y + rowH > BOTTOM) { newPage(); header(); }157 if (ri % 2) doc.rect(M, y, W - 2 * M, rowH).fill(SURFACE2);158 let x = M; cols.forEach((_, i) => { const v = r[i]; const txt = v == null ? "" : typeof v === "number" ? v.toLocaleString(fr ? "fr-CA" : "en-CA", { maximumFractionDigits: 4 }) : String(v); doc.font(typeof v === "number" ? "JB-Reg" : "Inter").fontSize(6.8).fillColor(INK).text(txt, x + 4, y + 3.4, { width: ws[i] - 8, lineBreak: false, height: rowH, ellipsis: true, align: typeof v === "number" ? "right" : "left" }); x += ws[i]; });159 doc.moveTo(M, y + rowH).lineTo(W - M, y + rowH).lineWidth(0.4).stroke(LINE); y += rowH;160 });161 if (s.rows.length > rows.length || extra > 0) { ensure(14); doc.font("Inter").fontSize(7).fillColor(INK2).text(`${s.rows.length > rows.length ? `${s.rows.length - rows.length} ${fr ? "lignes supplémentaires" : "more rows"}` : ""}${extra > 0 ? ` · ${extra} ${fr ? "colonnes supplémentaires" : "more columns"}` : ""} — ${fr ? "voir l'export XLSX/CSV" : "see XLSX/CSV export"}`, M, y + 2); y += 14; }162 y += 10;163 }164 const range = doc.bufferedPageRange();165 for (let i = range.start; i < range.start + range.count; i++) {166 doc.switchToPage(i);167 doc.rect(M, H - 46, W - 2 * M, 1.2).fill(INK);168 doc.font("JB-Reg").fontSize(5.9).fillColor(INK3).text((fr ? "UQO-EVAL.APP · PLATEFORME PÉDAGOGIQUE — ESTIMATION STATISTIQUE INDICATIVE, NI ÉVALUATION CERTIFIÉE (OEAQ) NI SOUMISSION" : "UQO-EVAL.APP · TEACHING PLATFORM — INDICATIVE STATISTICAL ESTIMATE, NEITHER A CERTIFIED APPRAISAL (OEAQ) NOR A BID"), M, H - 40, { width: W - 2 * M - 60, lineBreak: false, height: 8, ellipsis: true, characterSpacing: 0.2 });169 doc.font("JB-Reg").fontSize(5.9).fillColor(INK3).text("COURS IMM1003 · IMM1033 — SIMON-PIERRE BOUCHER, UQO", M, H - 30, { width: W - 2 * M - 60, lineBreak: false, height: 8, characterSpacing: 0.4 });170 doc.font("JB-Bold").fontSize(7.5).fillColor(INK).text(`${i - range.start + 1} / ${range.count}`, W - M - 40, H - 39, { width: 40, align: "right" });171 }172 doc.end();173 return done;174}175176/** Point d'entrée : format → corps + MIME + extension. */177export async function exportResult(result: unknown, format: "csv" | "xlsx" | "pdf" | "json", meta: ExportMeta, opts: { maxRows?: number; skip?: string[] } = {}): Promise<{ body: Buffer; mime: string; ext: string }> {178 if (format === "json") return { body: Buffer.from(JSON.stringify({ _uqo_eval: { title: meta.title, subtitle: meta.subtitle ?? null, generated: meta.generated ?? new Date().toISOString(), source: meta.source, mention: meta.lang === "fr" ? UQO_MENTION_FR : UQO_MENTION_EN }, ...(result && typeof result === "object" && !Array.isArray(result) ? (result as object) : { data: result }) }, null, 1)), mime: "application/json; charset=utf-8", ext: "json" };179 const sheets = toSheets(result, meta.lang, opts);180 if (format === "csv") return { body: Buffer.from(sheetsToCsv(sheets, meta)), mime: "text/csv; charset=utf-8", ext: "csv" };181 if (format === "xlsx") return { body: await sheetsToXlsx(sheets, meta), mime: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", ext: "xlsx" };182 return { body: await sheetsToPdf(sheets, meta), mime: "application/pdf", ext: "pdf" };183}184185export const slug = (s: string) => s.toLowerCase().normalize("NFD").replace(/[̀-ͯ]/g, "").replace(/[^a-z0-9]+/g, "-").replace(/^-+|-+$/g, "").slice(0, 60) || "export";186