Workhub / static /js /workbook.js
ag235772's picture
files Replacement
e298636
Raw History Blame Contribute Delete
10.9 kB
// Excel workbook <-> app state. Works in the browser (global ExcelJS) and in Node tests.
// Sheets managed by the app: <YYYY-MM> (one per month) · Summary · Weekly · Mail Templates · Script Templates · Hub Info
// Any other sheet the user adds is left untouched.
import { fmt, isoWeek, monthKey, parseISO, weekStart, addDays, pad, validDate } from "./core.js";
const EJ = () => globalThis.ExcelJS;
export const MONTH_SHEET = /^\d{4}-\d{2}$/;
const MANAGED = ["Summary", "Weekly", "Mail Templates", "Script Templates", "Hub Info"];
const HEAD = ["Entry No", "Date", "Week", "Work Type", "Product", "Title", "Item ID", "Description", "Logged At"];
const TPL_HEAD = ["ID", "Title", "Tags", "Language", "Body", "Learned phrases (JSON)", "Good", "Bad", "Created"];
const q = n => `'${n}'`;
export function cellText(v) {
if (v === null || v === undefined) return "";
if (v instanceof Date) return fmt(v);
if (typeof v === "object") {
if (v.richText) return v.richText.map(r => r.text).join("");
if ("result" in v) return cellText(v.result);
if ("text" in v) return cellText(v.text);
if ("error" in v) return "";
}
return String(v).trim();
}
function cellDate(v) {
if (v instanceof Date) return fmt(v);
if (v && typeof v === "object" && "result" in v) return cellDate(v.result);
const s = cellText(v); let m;
if ((m = s.match(/^(\d{4})-(\d{1,2})-(\d{1,2})/))) { const d = validDate(+m[1], +m[2], +m[3]); if (d) return fmt(d); }
if ((m = s.match(/^(\d{1,2})[\/\-.](\d{1,2})[\/\-.](\d{4})$/))) { const d = validDate(+m[3], +m[2], +m[1]); if (d) return fmt(d); }
return null;
}
const header = (ws, n) => {
const r = ws.getRow(1);
for (let c = 1; c <= n; c++) { const x = r.getCell(c); x.font = { bold: true, color: { argb: "FFFFFFFF" } }; x.fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FF1F3A5F" } }; }
ws.views = [{ state: "frozen", ySplit: 1 }];
};
export async function load(buffer) {
const wb = new (EJ().Workbook)();
if (buffer && buffer.byteLength) await wb.xlsx.load(buffer);
return wb;
}
export function readState(wb) {
const entries = [], used = new Set(), pending = [];
for (const ws of wb.worksheets.filter(w => MONTH_SHEET.test(w.name))) {
const idx = {}; ws.getRow(1).eachCell((c, i) => idx[cellText(c.value).toLowerCase()] = i);
for (const need of ["date", "work type", "product", "title", "item id"]) if (!idx[need]) throw new Error(`Sheet "${ws.name}" is missing the "${need}" column header.`);
ws.eachRow((row, rn) => {
if (rn === 1) return;
const g = k => idx[k] ? row.getCell(idx[k]).value : null, t = k => cellText(g(k));
if (!row.values.some(v => v !== null && v !== undefined && cellText(v) !== "")) return;
const date = cellDate(g("date")), wt = t("work type").toLowerCase(), pr = t("product").toUpperCase();
const where = `Sheet "${ws.name}" row ${rn}`;
if (!date) throw new Error(`${where}: date is not valid. Fix it in Excel (e.g. 2026-10-09) and retry — nothing was changed.`);
if (!["ticket", "poc"].includes(wt)) throw new Error(`${where}: Work Type must be Ticket or POC.`);
if (!["WAF", "WAS"].includes(pr)) throw new Error(`${where}: Product must be WAF or WAS.`);
const e = { entryNo: parseInt(t("entry no"), 10) || 0, date, workType: wt === "poc" ? "POC" : "Ticket", product: pr, title: t("title"), itemId: t("item id"), description: t("description"), loggedAt: t("logged at") };
if (!e.title || !e.itemId) throw new Error(`${where}: Title and Item ID are required.`);
if (e.entryNo && !used.has(e.entryNo)) { used.add(e.entryNo); entries.push(e); } else pending.push(e); // hand-typed rows get numbers
});
}
let next = Math.max(0, ...used) + 1;
for (const e of pending) { e.entryNo = next++; entries.push(e); }
entries.sort((a, b) => a.date < b.date ? -1 : a.date > b.date ? 1 : a.entryNo - b.entryNo);
const tpl = name => {
const ws = wb.getWorksheet(name); if (!ws) return null; const out = [];
ws.eachRow((r, rn) => {
if (rn === 1) return; const t = i => cellText(r.getCell(i).value); if (!t(1) && !t(2)) return;
let aliases = []; try { aliases = JSON.parse(t(6) || "[]"); } catch { /* ignore */ }
out.push({ id: t(1) || Math.random().toString(16).slice(2, 12), title: t(2), tags: t(3).split(",").map(s => s.trim()).filter(Boolean), lang: t(4), body: String(r.getCell(5).value ?? ""), aliases, good: +t(7) || 0, bad: +t(8) || 0, created: t(9) });
});
return out;
};
const info = wb.getWorksheet("Hub Info"); let owner = "";
if (info) info.eachRow(r => { if (cellText(r.getCell(1).value).toLowerCase() === "owner") owner = cellText(r.getCell(2).value); });
return { entries, mail: tpl("Mail Templates"), scripts: tpl("Script Templates"), owner };
}
export function writeState(wb, st) {
for (const ws of [...wb.worksheets]) if (MONTH_SHEET.test(ws.name) || MANAGED.includes(ws.name)) wb.removeWorksheet(ws.id);
const months = [...new Set(st.entries.map(e => e.date.slice(0, 7)))].sort();
const cnt = (rows, f) => rows.filter(f).length;
// ---- Summary (first, formulas referencing every month sheet)
const sm = wb.addWorksheet("Summary");
sm.addRow(["Month", "Tickets", "POCs", "WAF", "WAS", "Total"]); header(sm, 6);
months.forEach((m, i) => {
const rows = st.entries.filter(e => e.date.startsWith(m)), r = i + 2, S = q(m);
sm.addRow([m, { formula: `COUNTIF(${S}!$D:$D,"Ticket")`, result: cnt(rows, e => e.workType === "Ticket") }, { formula: `COUNTIF(${S}!$D:$D,"POC")`, result: cnt(rows, e => e.workType === "POC") },
{ formula: `COUNTIF(${S}!$E:$E,"WAF")`, result: cnt(rows, e => e.product === "WAF") }, { formula: `COUNTIF(${S}!$E:$E,"WAS")`, result: cnt(rows, e => e.product === "WAS") },
{ formula: `COUNT(${S}!$A:$A)`, result: rows.length }]);
});
const n = months.length, last = n + 1;
if (n) {
const col = "BCDEF", vals = ["Ticket", "POC", "WAF", "WAS", "total"].map(k => k === "total" ? st.entries.length : st.entries.filter(e => e.workType === k || e.product === k).length);
sm.addRow(["Average / month", ...[...col].map((c, i) => ({ formula: `ROUND(AVERAGE(${c}2:${c}${last}),2)`, result: Math.round(vals[i] / n * 100) / 100 }))]);
sm.addRow(["Total", ...[...col].map((c, i) => ({ formula: `SUM(${c}2:${c}${last})`, result: vals[i] }))]);
for (const r of [last + 1, last + 2]) sm.getRow(r).font = { bold: true };
}
sm.getColumn(1).width = 22; for (let c = 2; c <= 6; c++) sm.getColumn(c).width = 11;
// ---- Weekly
const wk = wb.addWorksheet("Weekly");
wk.addRow(["Week", "Week Start (Mon)", "Tickets", "POCs", "WAF", "WAS", "Total"]); header(wk, 7);
const weeks = [];
if (st.entries.length) for (let d = weekStart(parseISO(st.entries[0].date)); d <= parseISO(st.entries[st.entries.length - 1].date); d = addDays(d, 7)) weeks.push(d);
weeks.forEach((d, i) => {
const r = i + 2, key = isoWeek(d), ms = [...new Set([monthKey(d), monthKey(addDays(d, 6))])].filter(m => months.includes(m));
const rows = st.entries.filter(e => isoWeek(parseISO(e.date)) === key);
const f = (colLetter, val) => ms.map(m => `COUNTIFS(${q(m)}!$C:$C,$A${r},${q(m)}!$${colLetter}:$${colLetter},"${val}")`).join("+") || "0";
const tot = ms.map(m => `COUNTIFS(${q(m)}!$C:$C,$A${r})`).join("+") || "0";
wk.addRow([key, d, { formula: f("D", "Ticket"), result: cnt(rows, e => e.workType === "Ticket") }, { formula: f("D", "POC"), result: cnt(rows, e => e.workType === "POC") },
{ formula: f("E", "WAF"), result: cnt(rows, e => e.product === "WAF") }, { formula: f("E", "WAS"), result: cnt(rows, e => e.product === "WAS") }, { formula: tot, result: rows.length }]);
wk.getCell(`B${r}`).numFmt = "DD-MMM-YYYY";
});
if (weeks.length) {
const L = weeks.length + 1, per = k => st.entries.filter(e => k === "total" || e.workType === k || e.product === k).length;
wk.addRow(["Average / week", "", ...[..."CDEFG"].map((c, i) => ({ formula: `ROUND(AVERAGE(${c}2:${c}${L}),2)`, result: Math.round(per(["Ticket", "POC", "WAF", "WAS", "total"][i]) / weeks.length * 100) / 100 }))]);
wk.addRow(["Total", "", ...[..."CDEFG"].map((c, i) => ({ formula: `SUM(${c}2:${c}${L})`, result: per(["Ticket", "POC", "WAF", "WAS", "total"][i]) }))]);
wk.getRow(L + 1).font = { bold: true }; wk.getRow(L + 2).font = { bold: true };
if (n) { sm.addRow([]); sm.addRow(["Average / week (see Weekly)", ...[..."CDEFG"].map((c, i) => ({ formula: `Weekly!${c}${L + 1}`, result: Math.round(per(["Ticket", "POC", "WAF", "WAS", "total"][i]) / weeks.length * 100) / 100 }))]); sm.getRow(sm.rowCount).font = { bold: true }; }
}
wk.getColumn(1).width = 16; wk.getColumn(2).width = 17;
// ---- one sheet per month (the data)
for (const m of months) {
const ws = wb.addWorksheet(m); ws.addRow(HEAD); header(ws, HEAD.length);
for (const e of st.entries.filter(e => e.date.startsWith(m))) {
const r = ws.addRow([e.entryNo, parseISO(e.date), isoWeek(parseISO(e.date)), e.workType, e.product, e.title, e.itemId, e.description, e.loggedAt]);
r.getCell(2).numFmt = "DD-MMM-YYYY"; r.getCell(8).alignment = { wrapText: true, vertical: "top" };
}
[9, 13, 10, 11, 9, 40, 18, 60, 19].forEach((w, i) => ws.getColumn(i + 1).width = w);
ws.autoFilter = { from: "A1", to: { row: Math.max(2, ws.rowCount), column: HEAD.length } };
}
// ---- template libraries + info
for (const [name, items] of [["Mail Templates", st.mail || []], ["Script Templates", st.scripts || []]]) {
const ws = wb.addWorksheet(name); ws.addRow(TPL_HEAD); header(ws, TPL_HEAD.length);
for (const t of items) { const r = ws.addRow([t.id, t.title, t.tags.join(", "), t.lang, t.body, JSON.stringify(t.aliases), t.good, t.bad, t.created]); r.getCell(5).alignment = { wrapText: true, vertical: "top" }; }
[12, 38, 30, 11, 90, 30, 7, 7, 17].forEach((w, i) => ws.getColumn(i + 1).width = w);
}
const info = wb.addWorksheet("Hub Info");
[["Owner", st.owner || ""], ["Updated", new Date().toISOString().slice(0, 16).replace("T", " ")], ["App", "Work Hub v2"],
["Note", "Month sheets (YYYY-MM), Summary, Weekly and the template sheets are rebuilt by Work Hub. Add your own extra sheets freely."]].forEach(r => info.addRow(r));
info.getColumn(1).width = 12; info.getColumn(2).width = 100;
// sheet order: Summary, Weekly, months, templates, info (user's own sheets stay before them)
return wb;
}
export async function newWorkbookBuffer(st) {
const wb = writeState(new (EJ().Workbook)(), st);
return wb.xlsx.writeBuffer();
}
export async function updateBuffer(buffer, mutate) {
const wb = await load(buffer), st = readState(wb);
await mutate(st);
writeState(wb, st);
return { buffer: await wb.xlsx.writeBuffer(), state: st };
}
export const nextEntryNo = entries => Math.max(0, ...entries.map(e => e.entryNo)) + 1;
export const pad2 = pad;