1// UQO Éval — Quantity Survey : données de coûts RSMeans (Gordian) localisées pour le Québec — base `data/rsmeans.db` construite par2// scripts/rsmeans-build.py (voir docs/construction/RSMEANS.md). Lecture seule. Aucun prix n'est inventé : un prix « par facteur » est3// étiqueté comme tel (national × facteur de localisation par division, validé sur les villes extraites en entier).4import Database from "better-sqlite3";5import path from "path";6import fs from "fs";7import type { MeasureUnit, Project } from "./types";89export type RsmLabor = "STD" | "OPN" | "RES" | "FMR";10export const RSM_LABORS: { code: RsmLabor; fr: string; en: string }[] = [11 { code: "STD", fr: "Syndiqué (Standard Union) — commercial", en: "Standard Union — commercial" },12 { code: "OPN", fr: "Non syndiqué (Open Shop) — commercial", en: "Open Shop — commercial" },13 { code: "RES", fr: "Résidentiel (livre Residential)", en: "Residential (Residential book)" },14 { code: "FMR", fr: "Entretien et réparation (FMR)", en: "Facility Maintenance & Repair" },15];16export const RSM_DIVISIONS: Record<string, { fr: string; en: string }> = {17 "01": { fr: "Exigences générales", en: "General Requirements" }, "02": { fr: "Conditions existantes", en: "Existing Conditions" }, "03": { fr: "Béton", en: "Concrete" }, "04": { fr: "Maçonnerie", en: "Masonry" }, "05": { fr: "Métaux", en: "Metals" }, "06": { fr: "Bois, plastiques, composites", en: "Wood, Plastics, Composites" }, "07": { fr: "Protection thermique et contre l'humidité", en: "Thermal & Moisture Protection" }, "08": { fr: "Ouvertures", en: "Openings" }, "09": { fr: "Finitions", en: "Finishes" }, "10": { fr: "Spécialités", en: "Specialties" }, "11": { fr: "Équipement", en: "Equipment" }, "12": { fr: "Ameublement", en: "Furnishings" }, "13": { fr: "Constructions spéciales", en: "Special Construction" }, "14": { fr: "Équipement de transport", en: "Conveying Equipment" }, "21": { fr: "Protection incendie", en: "Fire Suppression" }, "22": { fr: "Plomberie", en: "Plumbing" }, "23": { fr: "CVCA", en: "HVAC" }, "25": { fr: "Automatisation intégrée", en: "Integrated Automation" }, "26": { fr: "Électricité", en: "Electrical" }, "27": { fr: "Communications", en: "Communications" }, "28": { fr: "Sécurité électronique", en: "Electronic Safety & Security" }, "31": { fr: "Terrassement", en: "Earthwork" }, "32": { fr: "Aménagements extérieurs", en: "Exterior Improvements" }, "33": { fr: "Services publics", en: "Utilities" }, "34": { fr: "Transport", en: "Transportation" }, "35": { fr: "Ouvrages maritimes et hydrauliques", en: "Waterway & Marine" }, "41": { fr: "Manutention", en: "Material Processing & Handling" }, "44": { fr: "Contrôle de la pollution", en: "Pollution Control" }, "46": { fr: "Traitement de l'eau", en: "Water & Wastewater Equipment" }, "48": { fr: "Production d'électricité", en: "Electrical Power Generation" },18};1920export interface RsmLocation { id: number; key: string; name: string; cciCode: string | null; country: string; state: string; full: boolean; lat: number | null; lng: number | null }21export interface RsmLine { line: string; mf95: string | null; division: string; shortDesc: string; fullDesc: string; unit: string | null; crew: string | null; dailyOutput: number | null; laborHours: number | null; format: string | null; percentLine: boolean; green: boolean; indent: number; books: string[]; refs: string[] }22export interface RsmPrice {23 line: string; locationId: number; locationName: string; labor: RsmLabor; laborRequested: RsmLabor;24 /** coûts « bare » (sans frais généraux ni profit de l'installateur) — base des lignes d'estimation */25 material: number | null; labor_cost: number | null; equipment: number | null; total: number | null;26 /** avec O&P de l'installateur (référence RSMeans « Total O&P ») */27 materialOp: number | null; laborOp: number | null; equipmentOp: number | null; totalOp: number | null;28 /** « full » = prix extrait tel quel pour cette ville ; « factor » = national × facteur de division (échantillonné et validé) */29 method: "full" | "factor"; factor: { material: number | null; labor: number | null; equipment: number | null; n: number; sd: number | null } | null;30 release: string;31}32export interface RsmFactor { locationId: number; labor: RsmLabor; division: string; materialF: number | null; laborF: number | null; equipmentF: number | null; nMaterial: number; nLabor: number; nEquipment: number; sdMaterial: number | null; sdLabor: number | null; sdEquipment: number | null; method: string }3334export function rsmeansDbPath(): string { return process.env.RSMEANS_DB ?? path.join(process.cwd(), "data", "rsmeans.db"); }35export function rsmeansAvailable(): boolean { return fs.existsSync(rsmeansDbPath()); }36let db: Database.Database | null = null;37export function getRsmeansDb(): Database.Database {38 if (db) return db;39 if (!rsmeansAvailable()) throw new Error("Base RSMeans absente (data/rsmeans.db) — voir scripts/rsmeans-build.py");40 db = new Database(rsmeansDbPath(), { readonly: true, fileMustExist: true });41 return db;42}43export function resetRsmeansDb(): void { if (db) { db.close(); db = null; } }4445export function rsmMeta(d = getRsmeansDb()): Record<string, unknown> {46 const rows = d.prepare("SELECT key, value FROM rsm_meta").all() as { key: string; value: string }[];47 return Object.fromEntries(rows.map((r) => { try { return [r.key, JSON.parse(r.value)]; } catch { return [r.key, r.value]; } }));48}49export function rsmLocations(d = getRsmeansDb()): RsmLocation[] {50 return (d.prepare("SELECT * FROM rsm_locations ORDER BY full DESC, name").all() as Record<string, unknown>[]).map((r) => ({ id: Number(r.id), key: String(r.key), name: String(r.name), cciCode: (r.cci_code as string | null) ?? null, country: String(r.country), state: String(r.state), full: Number(r.full) === 1, lat: (r.lat as number | null) ?? null, lng: (r.lng as number | null) ?? null }));51}52function rowToLine(r: Record<string, unknown>): RsmLine {53 return { line: String(r.line), mf95: (r.mf95 as string | null) ?? null, division: String(r.division), shortDesc: String(r.short_desc), fullDesc: String(r.full_desc), unit: (r.unit as string | null) || null, crew: (r.crew as string | null) || null, dailyOutput: (r.daily_output as number | null) ?? null, laborHours: (r.labor_hours as number | null) ?? null, format: (r.format as string | null) ?? null, percentLine: Number(r.percent_line) === 1, green: Number(r.green) === 1, indent: Number(r.indent ?? 0), books: String(r.books ?? "").split(",").filter(Boolean), refs: String(r.refs ?? "").split(",").filter(Boolean) };54}55export function rsmLine(line: string, d = getRsmeansDb()): RsmLine | null {56 const r = d.prepare("SELECT * FROM rsm_lines WHERE line = ?").get(line) as Record<string, unknown> | undefined;57 return r ? rowToLine(r) : null;58}59/** Recherche plein texte (FTS5) ou par préfixe de numéro de ligne ; `priced` = seulement les lignes à prix (pas les en-têtes). */60export function rsmSearch(q: string, opts: { division?: string | null; limit?: number; priced?: boolean } = {}, d = getRsmeansDb()): RsmLine[] {61 const limit = Math.min(500, Math.max(1, opts.limit ?? 50)); const term = q.trim();62 const where: string[] = []; const args: unknown[] = [];63 if (opts.division) { where.push("l.division = ?"); args.push(opts.division); }64 if (opts.priced !== false) where.push("l.unit IS NOT NULL AND l.unit <> '' AND l.percent_line = 0");65 if (!term) return (d.prepare(`SELECT l.* FROM rsm_lines l ${where.length ? "WHERE " + where.join(" AND ") : ""} ORDER BY l.line LIMIT ?`).all(...args, limit) as Record<string, unknown>[]).map(rowToLine);66 if (/^\d{4,12}$/.test(term.replace(/\s/g, ""))) { const pre = term.replace(/\s/g, ""); return (d.prepare(`SELECT l.* FROM rsm_lines l WHERE l.line LIKE ? ${where.length ? "AND " + where.join(" AND ") : ""} ORDER BY l.line LIMIT ?`).all(pre + "%", ...args, limit) as Record<string, unknown>[]).map(rowToLine); }67 // FTS : chaque mot en préfixe, guillemets neutralisés68 const fts = term.split(/\s+/).map((w) => w.replace(/["'*^]/g, "")).filter((w) => w.length >= 2).map((w) => `"${w}"*`).join(" ");69 if (!fts) return [];70 return (d.prepare(`SELECT l.* FROM rsm_fts f JOIN rsm_lines l ON l.line = f.line WHERE rsm_fts MATCH ? ${where.length ? "AND " + where.join(" AND ") : ""} ORDER BY bm25(rsm_fts), l.line LIMIT ?`).all(fts, ...args, limit) as Record<string, unknown>[]).map(rowToLine);71}72export function rsmFactors(locationId: number, d = getRsmeansDb()): RsmFactor[] {73 return (d.prepare("SELECT * FROM rsm_factors WHERE location_id = ? ORDER BY labor, division").all(locationId) as Record<string, unknown>[]).map((r) => ({ locationId: Number(r.location_id), labor: r.labor as RsmLabor, division: String(r.division), materialF: (r.material_f as number | null) ?? null, laborF: (r.labor_f as number | null) ?? null, equipmentF: (r.equipment_f as number | null) ?? null, nMaterial: Number(r.n_material ?? 0), nLabor: Number(r.n_labor ?? 0), nEquipment: Number(r.n_equipment ?? 0), sdMaterial: (r.sd_material as number | null) ?? null, sdLabor: (r.sd_labor as number | null) ?? null, sdEquipment: (r.sd_equipment as number | null) ?? null, method: String(r.method) }));74}7576const NATIONAL_ID = 431;77const LABOR_FALLBACK: Record<RsmLabor, RsmLabor[]> = { STD: ["STD", "OPN", "RES", "FMR"], OPN: ["OPN", "STD", "RES", "FMR"], RES: ["RES", "STD", "OPN", "FMR"], FMR: ["FMR", "STD", "OPN", "RES"] };78const r2 = (x: number | null) => (x == null ? null : Math.round(x * 100) / 100);7980/** Prix d'une ligne pour une localisation et un type de main-d'œuvre : extrait tel quel (villes complètes) ou national × facteur de division. */81export function rsmPrice(line: string, locationId: number, labor: RsmLabor, d = getRsmeansDb()): RsmPrice | null {82 const loc = d.prepare("SELECT * FROM rsm_locations WHERE id = ?").get(locationId) as Record<string, unknown> | undefined;83 if (!loc) return null;84 const meta = rsmMeta(d);85 const pick = (lid: number): Record<string, unknown> | null => {86 for (const lab of LABOR_FALLBACK[labor]) { const r = d.prepare("SELECT * FROM rsm_prices WHERE line = ? AND location_id = ? AND labor = ?").get(line, lid, lab) as Record<string, unknown> | undefined; if (r) return r; }87 return null;88 };89 const num = (v: unknown) => (v == null ? null : Number(v));90 if (Number(loc.full) === 1) {91 const r = pick(locationId);92 if (r) return { line, locationId, locationName: String(loc.name), labor: r.labor as RsmLabor, laborRequested: labor, material: num(r.material), labor_cost: num(r.labor_cost), equipment: num(r.equipment), total: num(r.total), materialOp: num(r.material_op), laborOp: num(r.labor_op), equipmentOp: num(r.equipment_op), totalOp: num(r.total_op), method: "full", factor: null, release: String(meta.release ?? "") };93 // ligne absente de l'extraction complète (livre non couvert) → repli national × facteurs de validation de la ville94 }95 const n = pick(NATIONAL_ID); if (!n) return null;96 const lab = n.labor as RsmLabor;97 // facteur de la SECTION (4 chiffres) puis de la DIVISION (2 chiffres) pour ce type de MO ; sinon autre type de MO ; sinon moyenne de la ville98 const labs = [lab, ...LABOR_FALLBACK[lab].filter((x) => x !== lab)];99 const byKey = (key: string) => { for (const lb of labs) { const r = d.prepare("SELECT * FROM rsm_factors WHERE location_id = ? AND labor = ? AND division = ? AND material_f IS NOT NULL").get(locationId, lb, key) as Record<string, unknown> | undefined; if (r) return r; } return undefined; };100 let provincial = false;101 let f = byKey(line.slice(0, 4)) ?? byKey(line.slice(0, 2))102 ?? (d.prepare("SELECT location_id, labor, division, AVG(material_f) material_f, AVG(labor_f) labor_f, AVG(equipment_f) equipment_f, SUM(n_material) n_material, NULL sd_material FROM rsm_factors WHERE location_id = ? AND length(division) = 2 AND material_f IS NOT NULL").get(locationId) as Record<string, unknown> | undefined);103 if (!f || f.material_f == null) {104 // ville sans facteur (extraction incomplète) : moyenne provinciale de la section/division, étiquetée105 provincial = true;106 f = (d.prepare("SELECT ? location_id, labor, division, AVG(material_f) material_f, AVG(labor_f) labor_f, AVG(equipment_f) equipment_f, 0 n_material, NULL sd_material FROM rsm_factors WHERE division = ? AND labor = ? AND material_f IS NOT NULL").get(locationId, line.slice(0, 4), lab) as Record<string, unknown> | undefined)107 ?? (d.prepare("SELECT ? location_id, labor, division, AVG(material_f) material_f, AVG(labor_f) labor_f, AVG(equipment_f) equipment_f, 0 n_material, NULL sd_material FROM rsm_factors WHERE division = ? AND material_f IS NOT NULL").get(locationId, line.slice(0, 2)) as Record<string, unknown> | undefined);108 if (!f || f.material_f == null) return null;109 }110 const mf = Number(f.material_f), lf = f.labor_f == null ? mf : Number(f.labor_f), ef = f.equipment_f == null ? 1 : Number(f.equipment_f);111 const mul = (v: unknown, k: number) => (v == null ? null : r2(Number(v) * k));112 const material = mul(n.material, mf), labor_cost = mul(n.labor_cost, lf), equipment = mul(n.equipment, ef);113 const total = material == null && labor_cost == null && equipment == null ? null : r2((material ?? 0) + (labor_cost ?? 0) + (equipment ?? 0));114 const materialOp = mul(n.material_op, mf), laborOp = mul(n.labor_op, lf), equipmentOp = mul(n.equipment_op, ef);115 const totalOp = materialOp == null && laborOp == null && equipmentOp == null ? null : r2((materialOp ?? 0) + (laborOp ?? 0) + (equipmentOp ?? 0));116 return { line, locationId, locationName: String(loc.name) + (provincial ? " (facteur provincial moyen)" : ""), labor: lab, laborRequested: labor, material, labor_cost, equipment, total, materialOp, laborOp, equipmentOp, totalOp, method: "factor", factor: { material: mf, labor: f.labor_f == null ? null : lf, equipment: f.equipment_f == null ? null : ef, n: Number(f.n_material ?? 0), sd: f.sd_material == null ? null : Number(f.sd_material) }, release: String(meta.release ?? "") };117}118119/* ------------------------------------------------------------ unités RSMeans → unités du module */120121export interface RsmUnitMap { unit: MeasureUnit; divisor: number; note: string | null }122/** Convertit l'unité RSMeans en unité de mesure du module ; `divisor` ramène le prix RSMeans à l'unité du module (ex. M.S.F. = 1 000 pi² → ÷ 1 000). */123export function rsmUnit(u: string | null): RsmUnitMap | null {124 if (!u) return null;125 const k = u.trim();126 const M: Record<string, RsmUnitMap> = {127 "S.F.": { unit: "ft2", divisor: 1, note: null }, "SFCA": { unit: "ft2", divisor: 1, note: "pi² de surface de contact (coffrage)" }, "SF Flr.": { unit: "ft2", divisor: 1, note: "pi² de plancher" }, "SF Surf": { unit: "ft2", divisor: 1, note: "pi² de surface" }, "SF Shlf": { unit: "ft2", divisor: 1, note: "pi² de tablette" }, "SF Stg.": { unit: "ft2", divisor: 1, note: "pi² de scène" }, "SF Hor.": { unit: "ft2", divisor: 1, note: "pi² horizontal" }, "SF Roof": { unit: "ft2", divisor: 1, note: "pi² de toit" }, "SF Wall": { unit: "ft2", divisor: 1, note: "pi² de mur" },128 "S.Y.": { unit: "ft2", divisor: 9, note: "verge carrée = 9 pi²" }, "Sq.": { unit: "ft2", divisor: 100, note: "square (toiture) = 100 pi²" }, "C.S.F.": { unit: "ft2", divisor: 100, note: "100 pi²" }, "M.S.F.": { unit: "ft2", divisor: 1000, note: "1 000 pi²" }, "CSF Flr": { unit: "ft2", divisor: 100, note: "100 pi² de plancher" },129 "L.F.": { unit: "ft", divisor: 1, note: null }, "V.L.F.": { unit: "ft", divisor: 1, note: "pi lin. vertical" }, "C.L.F.": { unit: "ft", divisor: 100, note: "100 pi lin." }, "M.L.F.": { unit: "ft", divisor: 1000, note: "1 000 pi lin." }, "LF Hdr": { unit: "ft", divisor: 1, note: "pi lin. de linteau" }, "LF Edge": { unit: "ft", divisor: 1, note: "pi lin. de rive" }, "Inch": { unit: "in", divisor: 1, note: null },130 "C.Y.": { unit: "yd3", divisor: 1, note: null }, "L.C.Y.": { unit: "yd3", divisor: 1, note: "verge cube foisonnée" }, "B.C.Y.": { unit: "yd3", divisor: 1, note: "verge cube en place" }, "E.C.Y.": { unit: "yd3", divisor: 1, note: "verge cube compactée" }, "C.F.": { unit: "ft3", divisor: 1, note: null }, "C.C.F.": { unit: "ft3", divisor: 100, note: "100 pi³" }, "M.C.F.": { unit: "ft3", divisor: 1000, note: "1 000 pi³" },131 "Hr.": { unit: "h", divisor: 1, note: null },132 };133 if (M[k]) return M[k];134 if (k === "%" || k === "% Vol." || k === "Costs" || k === "Total" || k === "Payroll") return null;135 // tout le reste est une unité de comptage (Ea., Opng., Pair, Set, Job, Riser, Ton, Lb., Gal., Day, Week, Month, M.B.F., System…)136 return { unit: "unit", divisor: 1, note: `unité RSMeans : ${k}` };137}138139/* --------------------------------------------------------- localisation d'un projet */140141const BY_NAME: [RegExp, number][] = [142 [/\bhull\b/i, 722], [/gatineau|aylmer|buckingham|masson/i, 238], [/montr[eé]al|montreal/i, 419], [/qu[eé]bec(?!.*(?:ouest|est))|sainte-foy|beauport/i, 516], [/\blaval\b/i, 349], [/sherbrooke|magog/i, 586], [/trois-rivi|trois rivi/i, 646], [/chicoutimi|saguenay|jonqui/i, 114], [/granby/i, 721], [/joliette/i, 723], [/rimouski/i, 724], [/rouyn/i, 725], [/hyacinthe/i, 726], [/sorel/i, 728], [/j[eé]r[oô]me/i, 727], [/cap-de-la-madeleine/i, 92], [/charlesbourg/i, 104], [/l[eé]vis/i, 516], [/longueuil|brossard|boucherville/i, 419], [/terrebonne|repentigny|mascouche/i, 723], [/blainville|boisbriand|mirabel|sainte-th[eé]r[eè]se/i, 727], [/drummondville/i, 726], [/shawinigan/i, 646], [/victoriaville/i, 646], [/salaberry|valleyfield|ch[aâ]teauguay|vaudreuil/i, 419],143];144const BY_REGION: Record<string, number> = { "QC-MTL": 419, "QC-QUE": 516, "QC-GAT": 238, "QC-OUT": 238, "QC-LAV": 349, "QC-SHE": 586, "QC-EST": 586, "QC-TR": 646, "QC-MAU": 646, "QC-SAG": 114, "QC-SLSJ": 114, "QC-BSL": 724, "QC-AT": 725, "QC-MON": 726, "QC-LAN": 723, "QC-LAU": 727, "QC-CDQ": 726 };145/** Localisation RSMeans d'un projet : municipalité (nom), sinon région, sinon Montréal (signalé). */146export function rsmLocationForProject(p: Pick<Project, "municipality" | "regionCode" | "address">): { id: number; reason: "municipality" | "region" | "default" } {147 const txt = `${p.municipality ?? ""} ${p.address ?? ""}`;148 for (const [re, id] of BY_NAME) if (re.test(txt)) return { id, reason: "municipality" };149 if (p.regionCode && BY_REGION[p.regionCode]) return { id: BY_REGION[p.regionCode], reason: "region" };150 return { id: 419, reason: "default" };151}152