Download static/js/workbook.js from ag235772/Workhub: direct link, hf CLI and curl.
- Browser
- Download file 10.9 kB
-
https://huggingface.co/spaces/ag235772/Workhub/resolve/main/static/js/workbook.js
- Command line
-
hf download hf://spaces/ag235772/Workhub/static/js/workbook.js
-
curl -L -o workbook.js https://huggingface.co/spaces/ag235772/Workhub/resolve/main/static/js/workbook.js
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; | |