1// UQO Éval — Métré : import manuel d'un barème publié (DTNI-11A/11B de Montréal, bordereaux MTMD, grilles) au format CSV. Module PUR.2/**3 * L'administrateur télécharge légalement le document (PDF/XLSX) depuis le site4 * de l'éditeur, le transcrit/convertit en CSV et l'importe. Chaque ligne devient5 * une observation de classe « published_schedule » (ou « awarded_unit_bid » si6 * le document est un bordereau de soumission retenue) avec sa provenance.7 *8 * Colonnes reconnues (en-têtes FR/EN, insensibles à la casse/accents) :9 * code | item | description* | unite/unit* | prix/price/montant* | categorie | section10 * inclut_materiau | inclut_main_doeuvre | inclut_equipement | taxes (avant|apres)11 * date_effet / effective_date | region | note12 */13import { normHeader, parseCsv, parseNumber } from "./csv";1415export const SCHEDULE_PARSER_VERSION = "schedule-csv-1.0";1617export interface ScheduleRow {18 externalId: string;19 code: string | null;20 description: string;21 unit: string;22 amount: number;23 category: string | null;24 includesMaterial: boolean | null;25 includesLabour: boolean | null;26 includesEquipment: boolean | null;27 taxBasis: "before_tax" | "after_tax" | "unknown";28 effectiveDate: string | null;29 region: string | null;30 note: string | null;31}3233const idx = (h: string[], ...keys: string[]) => h.findIndex((x) => keys.includes(x));34const bool = (s: string | undefined): boolean | null => { const t = (s ?? "").trim().toLowerCase(); if (!t) return null; return ["1", "oui", "yes", "true", "o", "y", "x"].includes(t) ? true : ["0", "non", "no", "false", "n"].includes(t) ? false : null; };3536export function parseScheduleCsv(text: string): { rows: ScheduleRow[]; skipped: { line: number; reason: string }[]; header: string[] } {37 const { header, rows } = parseCsv(text, { maxRows: 100000 });38 const h = header.map(normHeader);39 const iCode = idx(h, "code", "item", "no", "numero", "article");40 const iDesc = idx(h, "description", "designation", "libelle", "item_description", "ouvrage");41 const iUnit = idx(h, "unite", "unit", "um", "u_m");42 const iAmt = idx(h, "prix", "price", "montant", "prix_unitaire", "unit_price", "taux", "rate", "amount");43 const iCat = idx(h, "categorie", "category", "section", "division", "famille");44 const iMat = idx(h, "inclut_materiau", "materiau", "material", "includes_material");45 const iLab = idx(h, "inclut_main_doeuvre", "main_doeuvre", "labour", "labor", "includes_labour");46 const iEq = idx(h, "inclut_equipement", "equipement", "equipment", "includes_equipment");47 const iTax = idx(h, "taxes", "tax", "tax_basis", "base_taxes");48 const iDate = idx(h, "date_effet", "effective_date", "date", "en_vigueur");49 const iReg = idx(h, "region", "ville", "city", "territoire");50 const iNote = idx(h, "note", "notes", "remarque", "commentaire");51 if (iDesc < 0 || iUnit < 0 || iAmt < 0) throw new Error("Colonnes requises : description, unité, prix (en-têtes trouvés : " + header.join(", ") + ")");52 const out: ScheduleRow[] = [];53 const skipped: { line: number; reason: string }[] = [];54 rows.forEach((r, i) => {55 const line = i + 2;56 const desc = (r[iDesc] ?? "").trim();57 const unit = (r[iUnit] ?? "").trim();58 const amt = parseNumber(r[iAmt]);59 if (!desc) { skipped.push({ line, reason: "description vide" }); return; }60 if (!unit) { skipped.push({ line, reason: "unité vide" }); return; }61 if (amt == null || amt < 0) { skipped.push({ line, reason: "prix invalide" }); return; }62 const taxRaw = (iTax >= 0 ? r[iTax] : "")?.toLowerCase() ?? "";63 const date = iDate >= 0 ? (r[iDate] ?? "").trim().slice(0, 10) : "";64 const code = iCode >= 0 ? (r[iCode] ?? "").trim() || null : null;65 out.push({ externalId: code ?? `${line}:${desc.slice(0, 40)}`, code, description: desc.slice(0, 400), unit: unit.slice(0, 20), amount: amt, category: iCat >= 0 ? r[iCat]?.trim() || null : null, includesMaterial: bool(iMat >= 0 ? r[iMat] : undefined), includesLabour: bool(iLab >= 0 ? r[iLab] : undefined), includesEquipment: bool(iEq >= 0 ? r[iEq] : undefined), taxBasis: taxRaw.includes("apres") || taxRaw.includes("after") || taxRaw.includes("incl") ? "after_tax" : taxRaw.includes("avant") || taxRaw.includes("before") || taxRaw.includes("excl") ? "before_tax" : "unknown", effectiveDate: /^\d{4}-\d{2}-\d{2}$/.test(date) ? date : null, region: iReg >= 0 ? r[iReg]?.trim() || null : null, note: iNote >= 0 ? r[iNote]?.trim().slice(0, 300) || null : null });66 });67 return { rows: out, skipped, header };68}69