435 lines
16 KiB
JavaScript
435 lines
16 KiB
JavaScript
/**
|
|
* services/export-xlsx.js
|
|
* Génération XLSX comprehensive — Situation des Marchés RLA Zone Sud
|
|
*/
|
|
const ExcelJS = require('exceljs');
|
|
const { buildRef } = require('./calc');
|
|
|
|
const C = {
|
|
NAVY: 'FF002D62',
|
|
WHITE: 'FFFFFFFF',
|
|
ACCENT: 'FF00D4FF',
|
|
GREEN: 'FF16A34A',
|
|
ORANGE: 'FFEA580C',
|
|
RED: 'FFDC2626',
|
|
YELLOW: 'FFEAB308',
|
|
GRAY: 'FF64748B',
|
|
ALT: 'FFF1F5F9',
|
|
LIGHT: 'FFE2E8F0',
|
|
CAPEX: 'FFD1FAE5',
|
|
OPEX: 'FFFEF3C7',
|
|
TOTAL: 'FFDBEAFE',
|
|
HEADER: 'FF0F172A',
|
|
};
|
|
|
|
function fill(argb) { return { type: 'pattern', pattern: 'solid', fgColor: { argb } }; }
|
|
function font(argb, bold = false, size = 10) { return { color: { argb }, bold, size }; }
|
|
function border(color = C.LIGHT) {
|
|
const s = { style: 'thin', color: { argb: color } };
|
|
return { top: s, bottom: s, left: s, right: s };
|
|
}
|
|
|
|
const ALL_REGIONS = ['Gabes', 'Gafsa', 'Kebili', 'Medenine', 'Sfax', 'Tataouine', 'Tozeur'];
|
|
|
|
function parseNum(v) {
|
|
if (v === null || v === undefined || v === '') return 0;
|
|
if (typeof v === 'object') return 0;
|
|
const n = parseFloat(String(v).replace(/\s/g, '').replace(',', '.'));
|
|
return isNaN(n) ? 0 : n;
|
|
}
|
|
|
|
function selectVal(v) {
|
|
if (!v) return '';
|
|
if (typeof v === 'object' && v.value !== undefined) return String(v.value);
|
|
if (Array.isArray(v)) return v.map(x => x.value !== undefined ? x.value : x).join(', ');
|
|
return String(v);
|
|
}
|
|
|
|
function parseDateFR(d) {
|
|
if (!d) return null;
|
|
const parts = String(d).split(/[\/\-]/);
|
|
if (parts.length === 3) {
|
|
const [a, b, c] = parts;
|
|
if (a.length === 4) return new Date(`${a}-${b}-${c}`);
|
|
if (c.length === 4) return new Date(`${c}-${b}-${a}`);
|
|
}
|
|
const dt = new Date(d);
|
|
return isNaN(dt.getTime()) ? null : dt;
|
|
}
|
|
|
|
function fmtDate(d) {
|
|
const dt = parseDateFR(d);
|
|
if (!dt) return '-';
|
|
return dt.toLocaleDateString('fr-FR', { day: '2-digit', month: '2-digit', year: 'numeric' });
|
|
}
|
|
|
|
function fmtMDT(val) {
|
|
const n = parseNum(val);
|
|
if (n === 0) return '0';
|
|
if (n >= 1000000) return `${(n / 1000000).toFixed(1)} MDT`;
|
|
if (n >= 1000) return `${(n / 1000).toFixed(0)} kDT`;
|
|
return `${n.toFixed(0)} DT`;
|
|
}
|
|
|
|
function isCloture(r) {
|
|
const obs = selectVal(r.observation).toLowerCase();
|
|
return obs.includes('clôtur') || obs.includes('clotur') || !!r.date_cloture;
|
|
}
|
|
|
|
function getDelai(r) {
|
|
const dField = r.delai_restant;
|
|
if (dField !== null && dField !== undefined && dField !== '') {
|
|
const v = parseInt(String(dField), 10);
|
|
if (!isNaN(v)) return v;
|
|
}
|
|
const fin = r.date_fin || r.date_fin_marche || r.datefin;
|
|
const dt = parseDateFR(fin);
|
|
if (!dt) return '-';
|
|
return Math.ceil((dt - new Date()) / 86400000);
|
|
}
|
|
|
|
async function generateXlsx(view, data, allRows) {
|
|
const wb = new ExcelJS.Workbook();
|
|
wb.creator = 'RLA API';
|
|
wb.company = 'Tunisie Telecom Zone Sud';
|
|
wb.created = new Date();
|
|
|
|
const rows = allRows || data.items || data.regions || [];
|
|
const actifs = allRows ? rows.filter(r => !isCloture(r)) : rows;
|
|
|
|
await buildSheet1(wb, actifs);
|
|
await buildSheet2(wb, actifs);
|
|
|
|
return wb.xlsx.writeBuffer();
|
|
}
|
|
|
|
async function buildSheet1(wb, actifs) {
|
|
const ws = wb.addWorksheet('Situation des Marchés');
|
|
ws.views = [{ state: 'frozen', ySplit: 9 }];
|
|
|
|
// Column widths
|
|
const cols = [
|
|
{ width: 40 }, { width: 20 }, { width: 22 }, { width: 12 },
|
|
{ width: 16 }, { width: 14 }, { width: 8 }, { width: 14 },
|
|
{ width: 8 }, { width: 14 }, { width: 14 }, { width: 8 }, { width: 22 },
|
|
];
|
|
ws.columns = cols.map((c, i) => ({ key: String.fromCharCode(65 + i), width: c.width }));
|
|
|
|
const today = new Date().toLocaleDateString('fr-FR');
|
|
const capex = actifs.filter(r => String(selectVal(r.nature)).toUpperCase().includes('CAPEX') ||
|
|
!String(selectVal(r.nature)).toUpperCase().includes('OPEX'));
|
|
const opex = actifs.filter(r => String(selectVal(r.nature)).toUpperCase().includes('OPEX'));
|
|
const totalBudget = actifs.reduce((s, r) => s + parseNum(r.tot_marche || r.totmarche || r.montant), 0);
|
|
|
|
const avgPhy = (() => {
|
|
const vals = actifs.map(r => parseNum(r.taux_phy || r.avt_phy)).filter(v => v > 0);
|
|
return vals.length ? vals.reduce((a, b) => a + b, 0) / vals.length : 0;
|
|
})();
|
|
|
|
// Row 1: Title
|
|
const r1 = ws.addRow(['📊 SITUATION DES MARCHÉS RLA — ZONE SUD', ...Array(12).fill('')]);
|
|
r1.height = 30;
|
|
ws.mergeCells('A1:M1');
|
|
const c1 = r1.getCell(1);
|
|
c1.fill = fill(C.NAVY);
|
|
c1.font = { color: { argb: C.WHITE }, bold: true, size: 16 };
|
|
c1.alignment = { horizontal: 'center', vertical: 'middle' };
|
|
|
|
// Row 2: Subtitle
|
|
const r2 = ws.addRow([`Tunisie Telecom • Direction Centrale Achats • Zone Sud`, ...Array(12).fill('')]);
|
|
r2.height = 18;
|
|
ws.mergeCells('A2:M2');
|
|
r2.getCell(1).fill = fill('FF0F172A');
|
|
r2.getCell(1).font = font('FFCBD5E1', false, 11);
|
|
r2.getCell(1).alignment = { horizontal: 'center', vertical: 'middle' };
|
|
|
|
// Row 3: Stats bar
|
|
const r3 = ws.addRow([
|
|
`📅 ${today} │ 📋 ${actifs.length} marchés │ 💰 ${fmtMDT(totalBudget)} │ 📈 Phy moy: ${avgPhy.toFixed(0)}%`,
|
|
...Array(12).fill(''),
|
|
]);
|
|
r3.height = 16;
|
|
ws.mergeCells('A3:M3');
|
|
r3.getCell(1).fill = fill('FF1E3A5F');
|
|
r3.getCell(1).font = font('FF94A3B8', false, 10);
|
|
r3.getCell(1).alignment = { horizontal: 'center', vertical: 'middle' };
|
|
|
|
// Row 4: empty
|
|
ws.addRow([]);
|
|
|
|
// Row 5: KPI headers
|
|
const r5 = ws.addRow(['📊 GLOBAL', '', '', '', '', '🟢 CAPEX', '', '', '', '', '🟠 OPEX', '', '']);
|
|
r5.height = 22;
|
|
ws.mergeCells('A5:E5'); ws.mergeCells('F5:J5'); ws.mergeCells('K5:M5');
|
|
const kpiHdrStyle = (cell, argb) => {
|
|
cell.fill = fill(argb);
|
|
cell.font = { color: { argb: C.WHITE }, bold: true, size: 11 };
|
|
cell.alignment = { horizontal: 'center', vertical: 'middle' };
|
|
};
|
|
kpiHdrStyle(r5.getCell(1), C.NAVY);
|
|
kpiHdrStyle(r5.getCell(6), 'FF16A34A');
|
|
kpiHdrStyle(r5.getCell(11), C.ORANGE);
|
|
|
|
const capexBudget = capex.reduce((s, r) => s + parseNum(r.tot_marche || r.totmarche || r.montant), 0);
|
|
const opexBudget = opex.reduce((s, r) => s + parseNum(r.tot_marche || r.totmarche || r.montant), 0);
|
|
const capexPhy = (() => { const v = capex.map(r => parseNum(r.taux_phy || r.avt_phy)).filter(x => x > 0); return v.length ? v.reduce((a,b)=>a+b,0)/v.length : 0; })();
|
|
const opexPhy = (() => { const v = opex.map(r => parseNum(r.taux_phy || r.avt_phy)).filter(x => x > 0); return v.length ? v.reduce((a,b)=>a+b,0)/v.length : 0; })();
|
|
|
|
// Row 6: KPI values
|
|
const r6 = ws.addRow([`${actifs.length} marchés`, '', '', '', '',
|
|
`${capex.length} marchés`, '', '', '', '', `${opex.length} marchés`, '', '']);
|
|
r6.height = 18;
|
|
ws.mergeCells('A6:E6'); ws.mergeCells('F6:J6'); ws.mergeCells('K6:M6');
|
|
[1, 6, 11].forEach(col => {
|
|
r6.getCell(col).font = { bold: true, size: 14, color: { argb: C.NAVY } };
|
|
r6.getCell(col).alignment = { horizontal: 'center', vertical: 'middle' };
|
|
});
|
|
|
|
// Row 7: KPI details
|
|
const r7 = ws.addRow([
|
|
`Budget: ${fmtMDT(totalBudget)} • Phy moy: ${avgPhy.toFixed(0)}%`, '', '', '', '',
|
|
`Budget: ${fmtMDT(capexBudget)} • Phy: ${capexPhy.toFixed(0)}%`, '', '', '', '',
|
|
`Budget: ${fmtMDT(opexBudget)} • Phy: ${opexPhy.toFixed(0)}%`, '', '',
|
|
]);
|
|
r7.height = 16;
|
|
ws.mergeCells('A7:E7'); ws.mergeCells('F7:J7'); ws.mergeCells('K7:M7');
|
|
[1, 6, 11].forEach(col => {
|
|
r7.getCell(col).font = { size: 9, color: { argb: C.GRAY } };
|
|
r7.getCell(col).alignment = { horizontal: 'center', vertical: 'middle' };
|
|
});
|
|
|
|
// Row 8: empty
|
|
ws.addRow([]);
|
|
|
|
// Row 9: Column headers
|
|
const HEADERS = ['Référence', 'Projet', 'Entrepreneur', 'Nature',
|
|
'Montant Marché', 'Av. Phy (DT)', 'Phy %', 'Av. Fin (DT)', 'Fin %',
|
|
'Début', 'Fin', 'Délai', 'Observation'];
|
|
const r9 = ws.addRow(HEADERS);
|
|
r9.height = 22;
|
|
r9.eachCell(cell => {
|
|
cell.fill = fill(C.HEADER);
|
|
cell.font = { color: { argb: C.WHITE }, bold: true, size: 10 };
|
|
cell.alignment = { horizontal: 'center', vertical: 'middle', wrapText: true };
|
|
cell.border = border(C.NAVY);
|
|
});
|
|
|
|
// Per-region data
|
|
for (const region of ALL_REGIONS) {
|
|
const regRows = actifs.filter(r => (r.region || '') === region);
|
|
if (!regRows.length) continue;
|
|
|
|
// Region header row
|
|
const rh = ws.addRow([`📍 ${region} — ${regRows.length} marchés`, ...Array(12).fill('')]);
|
|
rh.height = 18;
|
|
ws.mergeCells(`A${rh.number}:M${rh.number}`);
|
|
rh.getCell(1).fill = fill('FF1E3A5F');
|
|
rh.getCell(1).font = { bold: true, size: 11, color: { argb: C.ACCENT } };
|
|
rh.getCell(1).alignment = { horizontal: 'left', vertical: 'middle', indent: 1 };
|
|
|
|
let subtotalBudget = 0, subtotalPhy = 0, subtotalFin = 0, phyCount = 0;
|
|
|
|
for (let i = 0; i < regRows.length; i++) {
|
|
const r = regRows[i];
|
|
const nat = selectVal(r.nature);
|
|
const isCapex = nat.toUpperCase().includes('CAPEX');
|
|
const budget = parseNum(r.tot_marche || r.totmarche || r.montant);
|
|
const phyDT = parseNum(r.avt_phy);
|
|
const phyPct = parseNum(r.taux_phy || r.avt_phy);
|
|
const finDT = parseNum(r.avt_fin);
|
|
const finPct = parseNum(r.taux_fin);
|
|
const delai = getDelai(r);
|
|
|
|
subtotalBudget += budget;
|
|
if (phyPct > 0) { subtotalPhy += phyPct; phyCount++; }
|
|
subtotalFin += finDT;
|
|
|
|
const rd = ws.addRow([
|
|
buildRef(r),
|
|
r.projet || '',
|
|
r.entrepreneur || '',
|
|
nat,
|
|
budget || '',
|
|
phyDT || '',
|
|
phyPct > 0 ? phyPct / 100 : '',
|
|
finDT || '',
|
|
finPct > 0 ? finPct / 100 : '',
|
|
fmtDate(r.date_debut || r.debut_marche),
|
|
fmtDate(r.date_fin || r.date_fin_marche),
|
|
delai,
|
|
selectVal(r.observation),
|
|
]);
|
|
rd.height = 15;
|
|
|
|
const altFill = i % 2 === 1 ? fill(C.ALT) : undefined;
|
|
const natFill = isCapex ? fill(C.CAPEX) : fill(C.OPEX);
|
|
|
|
rd.eachCell((cell, col) => {
|
|
if (altFill) cell.fill = altFill;
|
|
if (col === 4) cell.fill = natFill;
|
|
cell.border = { bottom: { style: 'thin', color: { argb: C.LIGHT } } };
|
|
cell.alignment = { vertical: 'middle' };
|
|
if ([5, 6, 8].includes(col)) cell.numFmt = '#,##0';
|
|
if ([7, 9].includes(col)) cell.numFmt = '0%';
|
|
if ([12].includes(col)) cell.alignment = { horizontal: 'center', vertical: 'middle' };
|
|
});
|
|
}
|
|
|
|
// Subtotal row
|
|
const avgPct = phyCount > 0 ? subtotalPhy / phyCount : 0;
|
|
const rst = ws.addRow([
|
|
`Sous-total ${region} (${regRows.length})`, '', '',
|
|
'', subtotalBudget, '', avgPct / 100, '', '',
|
|
'', '', '', '',
|
|
]);
|
|
rst.height = 16;
|
|
ws.mergeCells(`A${rst.number}:D${rst.number}`);
|
|
rst.eachCell(cell => {
|
|
cell.fill = fill('FF1E3A5F');
|
|
cell.font = { bold: true, size: 9, color: { argb: C.WHITE } };
|
|
cell.border = { top: { style: 'medium', color: { argb: C.NAVY } }, bottom: { style: 'medium', color: { argb: C.NAVY } } };
|
|
});
|
|
rst.getCell(5).numFmt = '#,##0';
|
|
rst.getCell(7).numFmt = '0%';
|
|
rst.getCell(1).alignment = { horizontal: 'right', vertical: 'middle' };
|
|
|
|
ws.addRow([]); // spacer
|
|
}
|
|
|
|
// Grand total row
|
|
const gt = ws.addRow([
|
|
`TOTAL ZONE SUD (${actifs.length} marchés)`, '', '', '',
|
|
actifs.reduce((s, r) => s + parseNum(r.tot_marche || r.totmarche || r.montant), 0),
|
|
'', avgPhy / 100, '', '', '', '', '', '',
|
|
]);
|
|
ws.mergeCells(`A${gt.number}:D${gt.number}`);
|
|
gt.height = 22;
|
|
gt.eachCell(cell => {
|
|
cell.fill = fill(C.NAVY);
|
|
cell.font = { bold: true, size: 11, color: { argb: C.WHITE } };
|
|
cell.border = { top: { style: 'medium', color: { argb: C.ACCENT } } };
|
|
});
|
|
gt.getCell(5).numFmt = '#,##0';
|
|
gt.getCell(7).numFmt = '0%';
|
|
gt.getCell(1).alignment = { horizontal: 'right', vertical: 'middle' };
|
|
}
|
|
|
|
async function buildSheet2(wb, actifs) {
|
|
const ws = wb.addWorksheet('Estimation Évolution');
|
|
ws.views = [{ state: 'frozen', ySplit: 6 }];
|
|
|
|
const SEUIL_STD = parseFloat(process.env.SEUIL_STANDARD || 70);
|
|
|
|
function projection(r) {
|
|
const marche = parseNum(r.tot_marche || r.totmarche || r.montant);
|
|
const minDT = marche * (SEUIL_STD / 100);
|
|
const consomme = parseNum(r.avt_fin);
|
|
const debut = parseDateFR(r.date_debut || r.debut_marche);
|
|
const fin = parseDateFR(r.date_fin || r.date_fin_marche);
|
|
const now = new Date();
|
|
const elapsed = debut ? Math.max(0, (now.getFullYear()-debut.getFullYear())*12+(now.getMonth()-debut.getMonth())) : 0;
|
|
const total = (debut && fin) ? Math.max(1, (fin.getFullYear()-debut.getFullYear())*12+(fin.getMonth()-debut.getMonth())) : 0;
|
|
const parMois = elapsed > 0 ? consomme / elapsed : 0;
|
|
const projete = parMois * (total || elapsed);
|
|
let verdict;
|
|
if (projete > marche * 1.03) verdict = 'Avenant';
|
|
else if (projete >= minDT) verdict = 'Normal';
|
|
else verdict = 'Sous Min';
|
|
return { marche, minDT, consomme, parMois, projete, verdict };
|
|
}
|
|
|
|
ws.columns = [
|
|
{ width: 42 }, { width: 22 }, { width: 22 }, { width: 16 },
|
|
{ width: 14 }, { width: 14 }, { width: 14 }, { width: 14 }, { width: 18 },
|
|
];
|
|
|
|
const today = new Date().toLocaleDateString('fr-FR');
|
|
const COLS = 9;
|
|
|
|
// Row 1: Title
|
|
const r1 = ws.addRow(['ESTIMATION ÉVOLUTION — ZONE SUD', ...Array(COLS - 1).fill('')]);
|
|
r1.height = 30;
|
|
ws.mergeCells(`A1:I1`);
|
|
r1.getCell(1).fill = fill('FF6366F1');
|
|
r1.getCell(1).font = { color: { argb: C.WHITE }, bold: true, size: 16 };
|
|
r1.getCell(1).alignment = { horizontal: 'center', vertical: 'middle' };
|
|
|
|
const r2 = ws.addRow([`Tunisie Telecom • Direction Centrale Achats • Zone Sud`, ...Array(COLS - 1).fill('')]);
|
|
r2.height = 18;
|
|
ws.mergeCells('A2:I2');
|
|
r2.getCell(1).fill = fill('FF4F46E5');
|
|
r2.getCell(1).font = font(C.WHITE, false, 11);
|
|
r2.getCell(1).alignment = { horizontal: 'center', vertical: 'middle' };
|
|
|
|
const r3 = ws.addRow([`📅 ${today} │ 📋 ${actifs.length} marchés`, ...Array(COLS - 1).fill('')]);
|
|
r3.height = 16;
|
|
ws.mergeCells('A3:I3');
|
|
r3.getCell(1).fill = fill('FF4338CA');
|
|
r3.getCell(1).font = font('FFFDE8EC', false, 10);
|
|
r3.getCell(1).alignment = { horizontal: 'center', vertical: 'middle' };
|
|
|
|
ws.addRow([]);
|
|
|
|
// Column headers
|
|
const HEADERS2 = ['Référence','Projet','Entrepreneur','Marché DT','Min DT','Consommé DT','DT/Mois','Projeté DT','Résultat'];
|
|
const r5 = ws.addRow(HEADERS2);
|
|
r5.height = 22;
|
|
r5.eachCell(cell => {
|
|
cell.fill = fill('FF6366F1');
|
|
cell.font = { color: { argb: C.WHITE }, bold: true, size: 10 };
|
|
cell.alignment = { horizontal: 'center', vertical: 'middle', wrapText: true };
|
|
cell.border = border(C.NAVY);
|
|
});
|
|
|
|
const VERDICT_COLOR = { Normal: C.GREEN, 'Sous Min': 'FFDC2626', Avenant: 'FFEA580C' };
|
|
|
|
for (let i = 0; i < actifs.length; i++) {
|
|
const r = actifs[i];
|
|
const p = projection(r);
|
|
|
|
const rd = ws.addRow([
|
|
buildRef(r),
|
|
r.projet || '',
|
|
r.entrepreneur || '',
|
|
p.marche || '',
|
|
p.minDT || '',
|
|
p.consomme || '',
|
|
p.parMois || '',
|
|
p.projete || '',
|
|
p.verdict,
|
|
]);
|
|
rd.height = 15;
|
|
if (i % 2 === 1) rd.eachCell(cell => { cell.fill = fill(C.ALT); });
|
|
|
|
[4, 5, 6, 7, 8].forEach(col => {
|
|
rd.getCell(col).numFmt = '#,##0';
|
|
rd.getCell(col).alignment = { horizontal: 'right', vertical: 'middle' };
|
|
});
|
|
rd.getCell(9).font = { color: { argb: VERDICT_COLOR[p.verdict] || C.GRAY }, bold: true };
|
|
rd.eachCell(cell => {
|
|
cell.border = { bottom: { style: 'thin', color: { argb: C.LIGHT } } };
|
|
if (!cell.alignment) cell.alignment = { vertical: 'middle' };
|
|
});
|
|
}
|
|
|
|
// Synthèse en bas
|
|
ws.addRow([]);
|
|
const nNormal = actifs.filter(r => projection(r).verdict === 'Normal').length;
|
|
const nSous = actifs.filter(r => projection(r).verdict === 'Sous Min').length;
|
|
const nAv = actifs.filter(r => projection(r).verdict === 'Avenant').length;
|
|
const rSum = ws.addRow([`✅ Normal: ${nNormal}`, '', '', `❌ Sous Min: ${nSous}`, '', '', `⚠️ Avenant: ${nAv}`, '', '']);
|
|
ws.mergeCells(`A${rSum.number}:C${rSum.number}`);
|
|
ws.mergeCells(`D${rSum.number}:F${rSum.number}`);
|
|
ws.mergeCells(`G${rSum.number}:I${rSum.number}`);
|
|
rSum.height = 20;
|
|
rSum.getCell(1).font = { color: { argb: C.GREEN }, bold: true, size: 10 };
|
|
rSum.getCell(4).font = { color: { argb: 'FFDC2626' }, bold: true, size: 10 };
|
|
rSum.getCell(7).font = { color: { argb: C.ORANGE }, bold: true, size: 10 };
|
|
[1, 4, 7].forEach(col => {
|
|
rSum.getCell(col).alignment = { horizontal: 'center', vertical: 'middle' };
|
|
rSum.getCell(col).fill = fill(C.ALT);
|
|
});
|
|
}
|
|
|
|
module.exports = { generateXlsx };
|