qshub / frontend /src /engine /cashflowExcel.js
basel00omar's picture
Update UI, add feedback system, extract name from email
369b6c9
Raw History Blame Contribute Delete
4.49 kB
// تصدير التدفق النقدي — Full monthly certificate schedule export (Excel).
// Ported from villa-qto. Lazy-import so xlsx stays out of the main bundle.
import * as XLSX from "xlsx-js-style";
const NAVY = "1A3C5E";
const SECTION = "2D6A9F";
const WHITE = "FFFFFF";
const headerStyle = { fill: { fgColor: { rgb: NAVY } }, font: { color: { rgb: WHITE }, bold: true }, alignment: { horizontal: "center", vertical: "center", wrapText: true } };
const titleStyle = { font: { bold: true, sz: 15, color: { rgb: NAVY } }, alignment: { horizontal: "center" } };
const labelStyle = { font: { bold: true, color: { rgb: NAVY } } };
const totalStyle = { fill: { fgColor: { rgb: SECTION } }, font: { color: { rgb: WHITE }, bold: true } };
const numStyle = { alignment: { horizontal: "right" }, numFmt: "#,##0" };
export function exportCashFlowToExcel(cfg, result, projectName) {
const headers = ["#", "Month", "Planned", "Cum %", "Advance Rec.", "Retention", "Net Cert.", "VAT", "Received", "Expenditure", "Net Cashflow", "Cumulative"];
const lastCol = headers.length - 1;
const { summary } = result;
const grid = [];
const merges = [];
grid.push([{ v: `Cash-Flow Forecast — ${projectName}`, s: titleStyle }]);
merges.push({ s: { r: 0, c: 0 }, e: { r: 0, c: lastCol } });
const info = [
["Contract Value (AED)", summary.contractValue],
["Advance Payment (AED)", summary.advancePayment],
["Total Retention (AED)", summary.totalRetentionWithheld],
["Working Capital Required (AED)", summary.workingCapitalRequired],
["Peak Negative @ Month", summary.peakNegativeMonth],
["2nd Trough @ Month", summary.secondTroughMonth || "—"],
["Payback @ Month", summary.paybackMonth],
["Total Expenditure (AED)", summary.totalExpenditure],
["Gross Profit (AED)", summary.totalProfit],
["Profit Margin %", summary.profitMarginPct],
["VAT %", cfg.vatPct],
];
info.forEach(([k, v]) => grid.push([{ v: k, s: labelStyle }, { v, s: typeof v === "number" ? numStyle : {} }]));
grid.push([{ v: "" }]);
grid.push(headers.map((h) => ({ v: h, s: headerStyle })));
for (const m of result.rows) {
grid.push([
{ v: m.monthNumber, s: { alignment: { horizontal: "center" } } },
{ v: m.monthLabel + (m.isRetentionRelease ? " (retention)" : ""), s: { alignment: { horizontal: "center" } } },
{ v: Math.round(m.plannedValue), s: numStyle },
{ v: m.cumulProgressPct / 100, s: { alignment: { horizontal: "right" }, numFmt: "0.0%" } },
{ v: Math.round(m.advanceRecovery), s: numStyle },
{ v: Math.round(m.retentionDeducted), s: numStyle },
{ v: Math.round(m.netCertificate), s: numStyle },
{ v: Math.round(m.vatOnCert), s: numStyle },
{ v: Math.round(m.paymentReceived), s: numStyle },
{ v: Math.round(m.expenditure), s: numStyle },
{ v: Math.round(m.netCashflow), s: numStyle },
{ v: Math.round(m.cumulCashflow), s: { ...numStyle, font: { color: { rgb: m.cumulCashflow < 0 ? "C00000" : "1B873F" } } } },
]);
}
const sum = (sel) => Math.round(result.rows.reduce((a, m) => a + sel(m), 0));
grid.push([
{ v: "TOTAL", s: totalStyle }, { v: "", s: totalStyle },
{ v: sum((m) => m.plannedValue), s: { ...numStyle, ...totalStyle } },
{ v: "", s: totalStyle },
{ v: sum((m) => m.advanceRecovery), s: { ...numStyle, ...totalStyle } },
{ v: sum((m) => m.retentionDeducted), s: { ...numStyle, ...totalStyle } },
{ v: sum((m) => m.netCertificate), s: { ...numStyle, ...totalStyle } },
{ v: sum((m) => m.vatOnCert), s: { ...numStyle, ...totalStyle } },
{ v: sum((m) => m.paymentReceived), s: { ...numStyle, ...totalStyle } },
{ v: sum((m) => m.expenditure), s: { ...numStyle, ...totalStyle } },
{ v: sum((m) => m.netCashflow), s: { ...numStyle, ...totalStyle } },
{ v: "", s: totalStyle },
]);
const ws = XLSX.utils.aoa_to_sheet(grid.map((line) => line.map((c) => c.v)));
grid.forEach((line, ri) => {
line.forEach((cell, ci) => {
const addr = XLSX.utils.encode_cell({ r: ri, c: ci });
if (ws[addr] && cell.s) ws[addr].s = cell.s;
});
});
ws["!merges"] = merges;
ws["!cols"] = [4, 14, 13, 8, 13, 12, 13, 11, 13, 13, 14, 14].map((wch) => ({ wch }));
const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "Cash Flow");
const safe = (projectName || "Project").replace(/[^\w؀-ۿ -]/g, "").replace(/ /g, "_") || "Project";
XLSX.writeFile(wb, `CashFlow_${safe}.xlsx`);
}