SPB Git

Repository Public

export-generic.ts 15.3 KB 186 lines typescript

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
Files main
Menu

Theme