// Excel workbook <-> app state. Works in the browser (global ExcelJS) and in Node tests. // Sheets managed by the app: (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;