/* global XLSX, docx */ // ============================================================================ // DVT EXPORT — Excel + Word (.docx) builders for a filled DVT audit // ---------------------------------------------------------------------------- // Reuses the workbook structure of REFT Africa DVT_Aug2019 Fr.xlsx so the // exported file roundtrips with dvt-import.jsx: // Page de Garde, Introduction (skipped), Guidance, "Vérification des // Données", "Amélioration des Données". // // Public API exposed at window.dvtExporter: // exportDvtToXlsx(payload) → downloads .xlsx // exportDvtToDocx(payload) → downloads .docx // payload = { project, audit, verifications, improvements, aggregates, lang } // ============================================================================ (function () { const xlsxOk = typeof XLSX !== "undefined"; const docxOk = typeof docx !== "undefined"; function variancePct(verified, reported) { const v = Number(verified), r = Number(reported); if (!isFinite(v) || !isFinite(r) || v === 0) return null; return ((r - v) / v) * 100; } function fmtVar(v) { if (v == null || !isFinite(v)) return ""; return (v > 0 ? "+" : "") + v.toFixed(1) + "%"; } function fmtDate(d) { if (!d) return ""; try { return new Date(d).toLocaleDateString("fr-FR", { year: "numeric", month: "long", day: "numeric" }); } catch (e) { return String(d); } } // ============================================================================ // EXCEL EXPORT // ============================================================================ function exportDvtToXlsx(payload) { if (!xlsxOk) { window.alert("La librairie XLSX n'est pas disponible (CDN bloqué ?)."); return; } const { project, audit, verifications, improvements } = payload; const wb = XLSX.utils.book_new(); const blank = (n) => new Array(n).fill(""); // ---- Page de Garde ---- XLSX.utils.book_append_sheet(wb, XLSX.utils.aoa_to_sheet([ [], [], [], [], [], [], [], [], [], [], ["", "", "", "", "", "OUTIL DE VÉRIFICATION & D'AMÉLIORATION DE DONNÉES (DVT)"], [], [], ["", "", "Programme/Projet :", project ? (project.id + " — " + (project.nameFr || project.nameEn)) : "—"], ["", "", "Cycle :", audit ? audit.cycle : "—"], ["", "", "Site :", audit && audit.sites && audit.sites.code ? (audit.sites.code + " — " + audit.sites.name) : (audit && audit.site_name) || "—"], ["", "", "Période auditée :", audit ? audit.period_audited || "—" : "—"], ["", "", "Date :", fmtDate(audit && audit.updated_at)], ]), "Page de Garde"); XLSX.utils.book_append_sheet(wb, XLSX.utils.aoa_to_sheet([[]]), "Introduction"); XLSX.utils.book_append_sheet(wb, XLSX.utils.aoa_to_sheet([ ["Partie A. La Vérification des Données"], [], [1, "Tous les indicateurs requis doivent être sélectionnés pour la vérification."], [2, "Le document-source doit être spécifié."], [3, "Valeur vérifiée = recalcul à partir du document source."], [4, "Valeur rapportée = la valeur transmise officiellement."], [5, "% Variance = (Valeur Rapportée − Valeur Vérifiée) / Valeur Vérifiée × 100"], [6, "Variance moyenne = moyenne arithmétique des variances mensuelles définies."], [], ["Partie B. Amélioration des Données"], [1, "Tout indicateur de variance > ±5 % est répertorié."], [2, "Décrire l'étape où l'erreur a été introduite."], [3, "Lister les mesures correctives prises pour rectifier les données."], [4, "Décrire les étapes de suivi pour éviter la récurrence."], [5, "L'échéancier doit être précis (date)."], [6, "Indiquer la personne responsable."], ]), "Guidance"); // ---- Vérification des Données ---- const vAoa = []; for (let r = 0; r < 50; r++) vAoa.push(blank(12)); const setV = (row, col, v) => { vAoa[row - 1][col.charCodeAt(0) - 65] = v; }; setV(1, "A", "Pays :"); setV(1, "B", audit && audit.country || ""); setV(1, "F", "Région de :"); setV(1, "G", audit && audit.region || ""); setV(3, "A", "Département/Unité :"); setV(3, "B", audit && audit.department || ""); setV(3, "F", "District Sanitaire de :"); setV(3, "G", audit && audit.district || ""); setV(5, "A", "Nom du Site :"); setV(5, "B", audit && audit.programme_name || ""); setV(5, "F", "Nom du site :"); setV(5, "G", audit && audit.site_name || (audit && audit.sites && audit.sites.code) || ""); setV(7, "A", "Nom du Programme/Projet :"); setV(7, "B", project ? (project.id + " — " + (project.nameFr || project.nameEn)) : ""); setV(7, "F", "Période Auditée :"); setV(7, "G", audit && audit.period_audited || ""); setV(9, "A", "Chef d'Equipe :"); setV(9, "B", audit && audit.team_lead || ""); setV(9, "F", "Chef d'Equipe site :"); setV(9, "G", audit && audit.site_team_lead || ""); setV(11, "A", "Staff #1 :"); setV(11, "B", audit && audit.staff_1 || ""); setV(11, "F", "Staff du site #1 :"); setV(11, "G", audit && audit.site_staff_1 || ""); setV(13, "A", "Staff #2 :"); setV(13, "B", audit && audit.staff_2 || ""); setV(13, "F", "Staff du site #2 :"); setV(13, "G", audit && audit.site_staff_2 || ""); setV(15, "A", "Staff #3 :"); setV(15, "B", audit && audit.staff_3 || ""); setV(15, "F", "Staff du site #3 :"); setV(15, "G", audit && audit.site_staff_3 || ""); setV(17, "A", "Autres Participants :"); setV(17, "B", audit && audit.other_participants || ""); setV(19, "A", "A. Vérification des données"); setV(20, "A", "Indicateur"); setV(20, "B", "Document-Source"); setV(20, "C", "Mois 1:"); setV(20, "F", "Mois 2:"); setV(20, "I", "Mois 3:"); setV(20, "L", "Moy. Variance (%)"); setV(21, "A", "(Inclure toute désaggrégation requise, e.g. âge, sexe)"); setV(21, "B", "(Inclure la ligne, colonne, etc. de la source, si approprié)"); setV(21, "C", "Valeur Vérifiée"); setV(21, "D", "Valeur Rapportée"); setV(21, "E", "Variance (%)"); setV(21, "F", "Valeur Vérifiée"); setV(21, "G", "Valeur Rapportée"); setV(21, "H", "Variance (%)"); setV(21, "I", "Valeur Vérifiée"); setV(21, "J", "Valeur Rapportée"); setV(21, "K", "Variance (%)"); (verifications || []).slice(0, 20).forEach((r) => { const row = 21 + r.position; setV(row, "A", r.indicator_name || ""); setV(row, "B", r.doc_source || ""); if (r.m1_verified != null) setV(row, "C", Number(r.m1_verified)); if (r.m1_reported != null) setV(row, "D", Number(r.m1_reported)); const v1 = variancePct(r.m1_verified, r.m1_reported); if (v1 != null) setV(row, "E", Number(v1.toFixed(2))); if (r.m2_verified != null) setV(row, "F", Number(r.m2_verified)); if (r.m2_reported != null) setV(row, "G", Number(r.m2_reported)); const v2 = variancePct(r.m2_verified, r.m2_reported); if (v2 != null) setV(row, "H", Number(v2.toFixed(2))); if (r.m3_verified != null) setV(row, "I", Number(r.m3_verified)); if (r.m3_reported != null) setV(row, "J", Number(r.m3_reported)); const v3 = variancePct(r.m3_verified, r.m3_reported); if (v3 != null) setV(row, "K", Number(v3.toFixed(2))); const vars = [v1, v2, v3].filter((x) => x != null); if (vars.length > 0) { const avg = vars.reduce((s, x) => s + x, 0) / vars.length; setV(row, "L", Number(avg.toFixed(2))); } }); setV(43, "A", "Comments:"); if (audit && audit.comments_verification) setV(43, "B", audit.comments_verification); XLSX.utils.book_append_sheet(wb, XLSX.utils.aoa_to_sheet(vAoa), "Vérification des Données"); // ---- Amélioration des Données ---- const iAoa = []; for (let r = 0; r < 50; r++) iAoa.push(blank(13)); const setI = (row, col, v) => { iAoa[row - 1][col.charCodeAt(0) - 65] = v; }; setI(1, "A", "Pays :"); setI(1, "B", audit && audit.country || ""); setI(1, "D", "Région de :"); setI(1, "E", audit && audit.region || ""); setI(3, "A", "Département/Unité :"); setI(3, "B", audit && audit.department || ""); setI(3, "D", "District Sanitaire de :"); setI(3, "E", audit && audit.district || ""); setI(5, "A", "Nom du Site :"); setI(5, "B", audit && audit.programme_name || ""); setI(5, "D", "Nom du site :"); setI(5, "E", audit && audit.site_name || ""); setI(7, "A", "Nom du Programme/Projet :"); setI(7, "B", project ? (project.id + " — " + (project.nameFr || project.nameEn)) : ""); setI(7, "D", "Période Auditée :"); setI(7, "E", audit && audit.period_audited || ""); setI(9, "A", "Chef d'Equipe :"); setI(9, "B", audit && audit.team_lead || ""); setI(9, "D", "Chef d'Equipe site :"); setI(9, "E", audit && audit.site_team_lead || ""); setI(11, "A", "Staff #1 :"); setI(11, "B", audit && audit.staff_1 || ""); setI(11, "D", "Staff du site #1 :"); setI(11, "E", audit && audit.site_staff_1 || ""); setI(13, "A", "Staff #2 :"); setI(13, "B", audit && audit.staff_2 || ""); setI(13, "D", "Staff du site #2 :"); setI(13, "E", audit && audit.site_staff_2 || ""); setI(15, "A", "Staff #3 :"); setI(15, "B", audit && audit.staff_3 || ""); setI(15, "D", "Staff du site #3 :"); setI(15, "E", audit && audit.site_staff_3 || ""); setI(17, "A", "Autres Participants :"); setI(17, "B", audit && audit.other_participants || ""); setI(19, "A", "B. Amélioration des données"); setI(20, "A", "Indicateur(s) avec une variance de plus de +/- 5%"); setI(20, "B", "Problème de donnée identifié (où l'erreur a été introduite)"); setI(20, "C", "Mesures correctives prises"); setI(20, "D", "Etapes de suivi"); setI(20, "E", "Calendrier"); setI(20, "F", "Personne Responsable"); (improvements || []).slice(0, 16).forEach((r) => { const row = 20 + r.position; setI(row, "A", r.indicator_name || ""); setI(row, "B", r.problem || ""); setI(row, "C", r.corrective_action || ""); setI(row, "D", r.followup_steps || ""); setI(row, "E", r.due_date || ""); setI(row, "F", r.responsible || ""); }); setI(37, "A", "Commentaires :"); if (audit && audit.comments_improvement) setI(37, "B", audit.comments_improvement); XLSX.utils.book_append_sheet(wb, XLSX.utils.aoa_to_sheet(iAoa), "Amélioration des Données"); const code = (project && project.id) || "DVT"; const cycle = (audit && audit.cycle || "").replace(/[^A-Za-z0-9_-]/g, ""); const siteSlug = audit && audit.sites && audit.sites.code ? audit.sites.code : (audit && audit.site_name ? audit.site_name.replace(/[^A-Za-z0-9_-]/g, "_").slice(0, 20) : "site"); XLSX.writeFile(wb, "DVT-" + code + "-" + cycle + "-" + siteSlug + ".xlsx"); } // ============================================================================ // WORD (.docx) EXPORT // ============================================================================ function exportDvtToDocx(payload) { if (!docxOk) { window.alert("La librairie docx n'est pas disponible (CDN bloqué ?)."); return; } const { Document, Packer, Paragraph, TextRun, HeadingLevel, AlignmentType, Table, TableRow, TableCell, WidthType, ShadingType, } = docx; const { project, audit, verifications, improvements, aggregates } = payload; const SHADE = { ok: { fill: "DCFCE7", fg: "15803D" }, amber: { fill: "FEF3C7", fg: "A16207" }, bad: { fill: "FEE2E2", fg: "B91C1C" }, neutral:{ fill: "F3F4F6", fg: "374151" }, accent: { fill: "E0E7FF", fg: "1E3A8A" }, }; const statusForVar = (v) => v == null ? "neutral" : Math.abs(v) <= 5 ? "ok" : Math.abs(v) <= 10 ? "amber" : "bad"; function P(text, opts) { opts = opts || {}; const runs = Array.isArray(text) ? text.map((x) => (x instanceof TextRun ? x : new TextRun(typeof x === "string" ? x : x))) : [new TextRun({ text: text || "", bold: opts.bold, italics: opts.italics, size: opts.size, color: opts.color })]; return new Paragraph({ children: runs, heading: opts.heading, alignment: opts.alignment, spacing: opts.spacing || { before: 0, after: 80 }, pageBreakBefore: opts.pageBreakBefore, shading: opts.shading, }); } function H1(text) { return P(text, { heading: HeadingLevel.HEADING_1, spacing: { before: 240, after: 120 }, pageBreakBefore: true }); } function H1NoBreak(text) { return P(text, { heading: HeadingLevel.HEADING_1, spacing: { before: 240, after: 120 } }); } function buildTable(columns, rows) { const headerCells = columns.map((c) => new TableCell({ width: { size: c.width || (100 / columns.length), type: WidthType.PERCENTAGE }, shading: { type: ShadingType.CLEAR, fill: "F3F4F6" }, children: [new Paragraph({ children: [new TextRun({ text: c.label, bold: true, size: 16 })], alignment: c.align === "right" ? AlignmentType.RIGHT : c.align === "center" ? AlignmentType.CENTER : AlignmentType.LEFT, spacing: { before: 30, after: 30 }, })], })); const dataRows = rows.map((cells) => new TableRow({ children: columns.map((c, i) => { const cell = cells[i]; const text = cell == null ? "" : (typeof cell === "string" || typeof cell === "number" ? String(cell) : (cell.text != null ? String(cell.text) : "")); const bold = typeof cell === "object" && cell !== null && cell.bold; const color = typeof cell === "object" && cell !== null ? cell.color : undefined; const shading = typeof cell === "object" && cell !== null && cell.shading ? { type: ShadingType.CLEAR, fill: cell.shading } : undefined; return new TableCell({ width: { size: c.width || (100 / columns.length), type: WidthType.PERCENTAGE }, shading, children: [new Paragraph({ children: [new TextRun({ text, bold, color, size: 15 })], alignment: c.align === "right" ? AlignmentType.RIGHT : c.align === "center" ? AlignmentType.CENTER : AlignmentType.LEFT, spacing: { before: 20, after: 20 }, })], }); }), })); return new Table({ width: { size: 100, type: WidthType.PERCENTAGE }, rows: [new TableRow({ tableHeader: true, children: headerCells }), ...dataRows], }); } function buildKpiTileRow(tiles, perRow) { perRow = perRow || 4; const rows = []; for (let i = 0; i < tiles.length; i += perRow) { const slice = tiles.slice(i, i + perRow); const cells = slice.map((tile) => { const c = SHADE[tile.status || "neutral"]; return new TableCell({ width: { size: 100 / perRow, type: WidthType.PERCENTAGE }, shading: { type: ShadingType.CLEAR, fill: c.fill }, children: [ new Paragraph({ children: [new TextRun({ text: tile.label, bold: true, color: c.fg, size: 16 })], spacing: { before: 60, after: 30 } }), new Paragraph({ children: [new TextRun({ text: String(tile.value), bold: true, color: c.fg, size: 28 })], spacing: { before: 0, after: 30 } }), tile.sub ? new Paragraph({ children: [new TextRun({ text: tile.sub, color: c.fg, size: 14 })], spacing: { before: 0, after: 60 } }) : new Paragraph({ children: [], spacing: { after: 60 } }), ], }); }); while (cells.length < perRow) { cells.push(new TableCell({ width: { size: 100 / perRow, type: WidthType.PERCENTAGE }, children: [new Paragraph("")] })); } rows.push(new TableRow({ children: cells })); } return new Table({ width: { size: 100, type: WidthType.PERCENTAGE }, rows }); } const children = []; const today = new Date().toLocaleDateString("fr-FR"); const siteLbl = audit && audit.sites && audit.sites.code ? (audit.sites.code + " — " + (audit.sites.name || "")) : (audit && audit.site_name) || "—"; children.push( P([new TextRun({ text: "OUTIL DE VÉRIFICATION & D'AMÉLIORATION DE DONNÉES (DVT)", bold: true, size: 28, color: "6B7280" })], { alignment: AlignmentType.CENTER, spacing: { before: 2400, after: 100 } }), P([new TextRun({ text: "— REFT Africa —", italics: true, size: 22, color: "6B7280" })], { alignment: AlignmentType.CENTER, spacing: { before: 0, after: 480 } }), P([new TextRun({ text: project ? (project.id + " — " + (project.nameFr || project.nameEn)) : "—", bold: true, size: 36 })], { alignment: AlignmentType.CENTER, spacing: { before: 0, after: 60 } }), P([new TextRun({ text: "Cycle : ", bold: true }), new TextRun({ text: audit ? audit.cycle : "—" })], { alignment: AlignmentType.CENTER, spacing: { before: 60, after: 40 } }), P([new TextRun({ text: "Site : ", bold: true }), new TextRun({ text: siteLbl })], { alignment: AlignmentType.CENTER, spacing: { before: 0, after: 40 } }), P([new TextRun({ text: "Pays : ", bold: true }), new TextRun({ text: (audit && audit.country) || "—" }), new TextRun({ text: " · Région : ", bold: true }), new TextRun({ text: (audit && audit.region) || "—" }), new TextRun({ text: " · District : ", bold: true }), new TextRun({ text: (audit && audit.district) || "—" })], { alignment: AlignmentType.CENTER, spacing: { before: 0, after: 40 } }), P([new TextRun({ text: "Période auditée : ", bold: true }), new TextRun({ text: (audit && audit.period_audited) || "—" })], { alignment: AlignmentType.CENTER, spacing: { before: 0, after: 40 } }), P([new TextRun({ text: "Chef d'équipe : ", bold: true }), new TextRun({ text: (audit && audit.team_lead) || "—" }), new TextRun({ text: " · Chef équipe site : ", bold: true }), new TextRun({ text: (audit && audit.site_team_lead) || "—" })], { alignment: AlignmentType.CENTER, spacing: { before: 0, after: 40 } }), P([new TextRun({ text: "Date : ", bold: true }), new TextRun({ text: today })], { alignment: AlignmentType.CENTER, spacing: { before: 0, after: 600 } }) ); // ===== Synthèse ===== children.push(H1NoBreak("I. Synthèse")); children.push(buildKpiTileRow([ { label: "Indicateurs vérifiés", value: aggregates.measuredCount, status: "accent" }, { label: "Variance moy. |%|", value: aggregates.meanAbsVariance.toFixed(1) + "%", status: statusForVar(aggregates.meanAbsVariance) }, { label: "Hors seuil ±5%", value: aggregates.over5pctCount, status: aggregates.over5pctCount > 0 ? "bad" : "ok" }, { label: "Actions plan", value: (improvements || []).length, status: "amber" }, ], 4)); // ===== Partie A — Vérification ===== children.push(H1("II. Vérification des données (Partie A)")); const verifCols = [ { label: "#", width: 5, align: "center" }, { label: "Indicateur", width: 28 }, { label: "Doc-source", width: 18 }, { label: "M1 V/R", width: 11, align: "right" }, { label: "M2 V/R", width: 11, align: "right" }, { label: "M3 V/R", width: 11, align: "right" }, { label: "Moy. Var", width: 8, align: "right" }, ]; const verifRows = (verifications || []).filter((r) => r.indicator_name || r.m1_verified != null).map((r) => { const s = statusForVar(r.avg_var); return [ { text: r.position, bold: true }, r.indicator_name || "—", r.doc_source || "—", (r.m1_verified == null ? "—" : r.m1_verified) + "/" + (r.m1_reported == null ? "—" : r.m1_reported) + (r.m1_var != null ? " " + fmtVar(r.m1_var) : ""), (r.m2_verified == null ? "—" : r.m2_verified) + "/" + (r.m2_reported == null ? "—" : r.m2_reported) + (r.m2_var != null ? " " + fmtVar(r.m2_var) : ""), (r.m3_verified == null ? "—" : r.m3_verified) + "/" + (r.m3_reported == null ? "—" : r.m3_reported) + (r.m3_var != null ? " " + fmtVar(r.m3_var) : ""), { text: fmtVar(r.avg_var), shading: SHADE[s].fill, color: SHADE[s].fg, bold: true }, ]; }); children.push(buildTable(verifCols, verifRows)); if (audit && audit.comments_verification) { children.push(P([new TextRun({ text: "Commentaires (vérification) : ", bold: true }), new TextRun({ text: audit.comments_verification })], { italics: true, color: "6B7280", spacing: { before: 100, after: 200 } })); } // ===== Partie B — Amélioration ===== if (improvements && improvements.length > 0) { children.push(H1("III. Amélioration des données (Partie B)")); const improvCols = [ { label: "#", width: 5, align: "center" }, { label: "Indicateur", width: 16 }, { label: "Problème", width: 18 }, { label: "Mesure corrective", width: 18 }, { label: "Étapes de suivi", width: 16 }, { label: "Échéance", width: 10, align: "center" }, { label: "Responsable", width: 12 }, ]; const improvRows = improvements.filter((i) => i.indicator_name || i.problem).map((i) => [ { text: i.position, bold: true }, i.indicator_name || "—", i.problem || "—", i.corrective_action || "—", i.followup_steps || "—", i.due_date || "—", i.responsible || "—", ]); children.push(buildTable(improvCols, improvRows)); if (audit && audit.comments_improvement) { children.push(P([new TextRun({ text: "Commentaires (amélioration) : ", bold: true }), new TextRun({ text: audit.comments_improvement })], { italics: true, color: "6B7280", spacing: { before: 100, after: 200 } })); } } children.push(P("Document généré automatiquement par MELR — " + today, { italics: true, color: "9CA3AF", size: 14, alignment: AlignmentType.CENTER, spacing: { before: 600 } })); const doc = new Document({ creator: "MELR", title: "DVT — " + (project ? project.id : "") + " — " + (audit ? audit.cycle : ""), styles: { default: { document: { run: { font: "Calibri", size: 22 } }, heading1: { run: { font: "Calibri", size: 32, bold: true, color: "1F2937" }, paragraph: { spacing: { before: 240, after: 120 } } }, heading2: { run: { font: "Calibri", size: 24, bold: true, color: "374151" }, paragraph: { spacing: { before: 160, after: 80 } } }, }, }, sections: [{ properties: { page: { margin: { top: 1000, right: 800, bottom: 1000, left: 800 } } }, children, }], }); Packer.toBlob(doc).then((blob) => { const url = URL.createObjectURL(blob); const a = document.createElement("a"); const code = (project && project.id) || "DVT"; const cycle = (audit && audit.cycle || "").replace(/[^A-Za-z0-9_-]/g, ""); a.href = url; a.download = "DVT-" + code + "-" + cycle + ".docx"; document.body.appendChild(a); a.click(); document.body.removeChild(a); URL.revokeObjectURL(url); }).catch((e) => { console.error("[dvt-export] docx packer:", e); window.alert("Erreur génération .docx : " + e.message); }); } window.dvtExporter = { available: xlsxOk && docxOk, xlsxAvailable: xlsxOk, docxAvailable: docxOk, exportDvtToXlsx, exportDvtToDocx, }; })();