sales summary: report trailing 24h window via seen-ledger diff (Steve 2026-07-03)

1de626600385facc2f215ca5bc09aec772f3c558 · 2026-07-03 04:08:55 -0700 · Steve Abrams
Files touched

M scripts/sales-summary.mjs
Diff

commit 1de626600385facc2f215ca5bc09aec772f3c558
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Fri Jul 3 04:08:55 2026 -0700

    sales summary: report trailing 24h window via seen-ledger diff (Steve 2026-07-03)
---
 scripts/sales-summary.mjs | 100 ++++++++++++++++++++++++++++++++++++----------
 1 file changed, 78 insertions(+), 22 deletions(-)

diff --git a/scripts/sales-summary.mjs b/scripts/sales-summary.mjs
index fa92a33..677a251 100644
--- a/scripts/sales-summary.mjs
+++ b/scripts/sales-summary.mjs
@@ -1,10 +1,17 @@
 // End-of-day sales recap → Slack #new-order, sourced from the FileMaker invoice DB.
-// Sums GRAND TOTAL 2 by invoice Date for TODAY and MONTH-TO-DATE (Pacific Time).
-// Reuses this project's FileMaker Cloud client (Claris ID auth) — no Shopify needed.
+// Reports a TRAILING 24-HOUR window: everything new since the previous run (Steve,
+// 2026-07-03 "Yes. Everything in last 24 hours at 4:30."). The invoice layout has no
+// timestamp fields — only dates — so the window is implemented as a diff against a
+// ledger of invoice numbers already reported (data/sales-summary-ledger.json). That
+// catches late-day orders, backdated entries, and quote→booked conversions exactly.
+// MONTH-TO-DATE stays bucketed by invoice Date (Pacific Time).
 //
-//   node scripts/sales-summary.mjs            # compute + POST to Slack
-//   node scripts/sales-summary.mjs --dry-run  # compute + print only (no post)
+//   node scripts/sales-summary.mjs            # compute + POST to Slack + update ledger
+//   node scripts/sales-summary.mjs --dry-run  # compute + print only (no post, no ledger write)
 
+import { readFileSync, writeFileSync, renameSync, existsSync } from 'node:fs';
+import { join, dirname } from 'node:path';
+import { fileURLToPath } from 'node:url';
 import { findRecords } from '../src/fm-client.js';
 import { loadEnv, TZ, ptYMD, num, money } from '../lib/fm-script-helpers.js';
 
@@ -13,24 +20,43 @@ loadEnv(import.meta.url);
 const DB = 'invoice';
 const LAYOUT = 'GRAND TOTAL SALES';
 const TOTAL_FIELD = 'GRAND TOTAL 2';       // per-invoice grand total (merch + shipping + tax)
-const DATE_FIELD = 'Date';                 // invoice date
+const DATE_FIELD = 'Date';                 // invoice date (a DATE — the layout has no timestamps)
 const CHANNEL = process.env.SALES_SUMMARY_CHANNEL || 'C06D9C2PG1K'; // #new-order
 const DRY_RUN = process.argv.includes('--dry-run');
 const EXCLUDE_INVOICES = new Set(['999999']); // test/demo records
+const LEDGER_PATH = join(dirname(fileURLToPath(import.meta.url)), '..', 'data', 'sales-summary-ledger.json');
+const LOOKBACK_DAYS = 10;   // how far back we pull to catch backdated entries
+const PRUNE_DAYS = 60;      // drop ledger entries not seen in this many days
 
 const pad = (n) => String(n).padStart(2, '0');
+const fmDate = ({ y, m, d }) => `${pad(m)}/${pad(d)}/${y}`; // FM returns zero-padded
+
+function loadLedger() {
+  if (!existsSync(LEDGER_PATH)) return null;
+  try { return JSON.parse(readFileSync(LEDGER_PATH, 'utf8')); } catch { return null; }
+}
+
+function saveLedger(ledger) {
+  const tmp = LEDGER_PATH + '.tmp';
+  writeFileSync(tmp, JSON.stringify(ledger, null, 1));
+  renameSync(tmp, LEDGER_PATH);
+}
 
 async function main() {
   const now = new Date();
   const { y, m, d } = ptYMD(now);
-  const monthStart = `${m}/1/${y}`;
-  const todayStr = `${m}/${d}/${y}`;
-  const todayFM = `${pad(m)}/${pad(d)}/${y}`; // FM returns zero-padded, e.g. 07/02/2026
+  const todayFM = fmDate({ y, m, d });
+  const yesterdayFM = fmDate(ptYMD(new Date(now.getTime() - 864e5)));
+  const todayISO = `${y}-${pad(m)}-${pad(d)}`;
+
+  // Pull window: month start (for MTD) or LOOKBACK_DAYS ago, whichever is earlier.
+  const lookback = ptYMD(new Date(now.getTime() - LOOKBACK_DAYS * 864e5));
+  const monthIsEarlier = y < lookback.y || (y === lookback.y && m < lookback.m);
+  const from = monthIsEarlier ? `${m}/1/${y}` : `${lookback.m}/${lookback.d}/${lookback.y}`;
 
-  // One MTD pull; bucket "today" client-side.
   const { records } = await findRecords(
     DB, LAYOUT,
-    [{ [DATE_FIELD]: `${monthStart}...${todayStr}` }],
+    [{ [DATE_FIELD]: `${from}...${m}/${d}/${y}` }],
     { limit: 2000, sort: [{ fieldName: DATE_FIELD, sortOrder: 'ascend' }] },
   );
 
@@ -40,19 +66,45 @@ async function main() {
     const p = String(f['PAID ON ACCOUNT'] ?? '').trim();
     return p !== '' && num(p) !== 0;
   };
+  const inCurrentMonth = (dateStr) => {
+    const [mm, , yy] = String(dateStr).split('/');
+    return Number(mm) === m && Number(yy) === y;
+  };
+
+  const ledger = loadLedger();
+  const firstRun = !ledger || !ledger.invoices;
+  const seen = firstRun ? {} : ledger.invoices;
+  const nextInvoices = { ...seen };
 
   let dayCount = 0, dayTotal = 0, mtdCount = 0, mtdTotal = 0, qCount = 0, qTotal = 0;
   for (const r of records) {
     const f = r.fieldData || {};
-    if (EXCLUDE_INVOICES.has(String(f['Invoice'] || '').trim())) continue;
+    const inv = String(f['Invoice'] || '').trim();
+    if (EXCLUDE_INVOICES.has(inv)) continue;
+    const key = inv || `rec:${r.recordId}`;
     const amt = num(f[TOTAL_FIELD]);
-    const isToday = String(f[DATE_FIELD] || '').trim() === todayFM;
-    if (isBooked(f)) {
-      mtdCount += 1; mtdTotal += amt;
-      if (isToday) { dayCount += 1; dayTotal += amt; }
-    } else if (isToday) {           // today's quotes with no money received yet
-      qCount += 1; qTotal += amt;
+    const dateStr = String(f[DATE_FIELD] || '').trim();
+    const booked = isBooked(f);
+
+    if (booked && inCurrentMonth(dateStr)) { mtdCount += 1; mtdTotal += amt; }
+
+    const prev = seen[key];
+    // First run has no ledger to diff against — approximate the 24h window as
+    // "dated yesterday or today". Every later run diffs against the ledger.
+    const inWindow = firstRun
+      ? (dateStr === todayFM || dateStr === yesterdayFM)
+      : (booked ? (!prev || prev.status !== 'booked') : !prev);
+    if (inWindow) {
+      if (booked) { dayCount += 1; dayTotal += amt; }
+      else { qCount += 1; qTotal += amt; }
     }
+    nextInvoices[key] = { status: booked ? 'booked' : 'quote', lastSeen: todayISO };
+  }
+
+  // Prune ledger entries not seen recently so the file stays small.
+  const cutoff = new Date(now.getTime() - PRUNE_DAYS * 864e5).toISOString().slice(0, 10);
+  for (const [key, v] of Object.entries(nextInvoices)) {
+    if ((v.lastSeen || '') < cutoff) delete nextInvoices[key];
   }
 
   const dayLabel = new Intl.DateTimeFormat('en-US', { timeZone: TZ, weekday: 'short', month: 'short', day: 'numeric', year: 'numeric' }).format(now);
@@ -60,12 +112,15 @@ async function main() {
 
   const text =
     `*📊 Daily Sales Summary — ${dayLabel}*\n\n` +
-    `🗓️ *Today — booked:* ${dayCount} order${dayCount === 1 ? '' : 's'} · *${money(dayTotal)}*\n` +
+    `🕓 *Last 24 hours — booked:* ${dayCount} order${dayCount === 1 ? '' : 's'} · *${money(dayTotal)}*\n` +
     `📈 *Month-to-date — booked (${monthLabel}):* ${mtdCount} order${mtdCount === 1 ? '' : 's'} · *${money(mtdTotal)}*\n` +
-    `📝 *Quotes today (no payment yet):* ${qCount} · ${money(qTotal)}\n\n` +
-    `_Booked = payment received (PAID ON ACCOUNT). Quotes = billed, no payment yet. Excludes test #999999; refunds net out._`;
+    `📝 *New quotes (last 24h):* ${qCount} · ${money(qTotal)}\n\n` +
+    `_Last 24h = everything new since the previous 4:30 report, incl. late-day + backdated orders and quotes that converted to booked. Booked = payment received (PAID ON ACCOUNT). Excludes test #999999; refunds net out._`;
 
-  if (DRY_RUN) { console.log('--- DRY RUN (not posted) ---\n' + text); return; }
+  if (DRY_RUN) {
+    console.log(`--- DRY RUN (not posted, ledger not written${firstRun ? '; first-run window = yesterday+today' : ''}) ---\n` + text);
+    return;
+  }
 
   const token = process.env.SLACK_BOT_TOKEN;
   if (!token) throw new Error('SLACK_BOT_TOKEN not set.');
@@ -76,7 +131,8 @@ async function main() {
   });
   const j = await res.json();
   if (!j.ok) throw new Error(`Slack post failed: ${j.error}`);
-  console.log(`✓ Posted to ${CHANNEL} (ts ${j.ts}) — Today ${dayCount}/${money(dayTotal)} · MTD ${mtdCount}/${money(mtdTotal)}`);
+  saveLedger({ lastRun: now.toISOString(), invoices: nextInvoices });
+  console.log(`✓ Posted to ${CHANNEL} (ts ${j.ts}) — 24h ${dayCount}/${money(dayTotal)} · MTD ${mtdCount}/${money(mtdTotal)}`);
 }
 
 main().catch((e) => { console.error('sales-summary error:', e.message); process.exit(1); });
← 70f934b daily sales summary: move fire time 23:55 -> 16:30 (4:30 PM · back to Filemaker Mcp · chore: macstudio3 migration — reconcile from mac2 + repoint 83d8494 →