sales-summary: count BOOKED orders only (payment received); add today's quotes (no payment) line — fixes $92k unpaid-quote inflating MTD

7bf6a1d0ce3c8cb77a4196092511caf8e6400d26 · 2026-07-02 07:53:32 -0700 · Steve

Files touched

M scripts/sales-summary.mjs

Diff

commit 7bf6a1d0ce3c8cb77a4196092511caf8e6400d26
Author: Steve <steve@designerwallcoverings.com>
Date:   Thu Jul 2 07:53:32 2026 -0700

    sales-summary: count BOOKED orders only (payment received); add today's quotes (no payment) line — fixes $92k unpaid-quote inflating MTD
---
 scripts/sales-summary.mjs | 22 ++++++++++++++++------
 1 file changed, 16 insertions(+), 6 deletions(-)

diff --git a/scripts/sales-summary.mjs b/scripts/sales-summary.mjs
index 18feb54..eb7e1be 100644
--- a/scripts/sales-summary.mjs
+++ b/scripts/sales-summary.mjs
@@ -51,13 +51,22 @@ async function main() {
     { limit: 2000, sort: [{ fieldName: DATE_FIELD, sortOrder: 'ascend' }] },
   );
 
-  let dayCount = 0, dayTotal = 0, mtdCount = 0, mtdTotal = 0;
+  // A BOOKED order (= a real sale) has money received (PAID ON ACCOUNT ≠ 0).
+  // No payment yet = an open QUOTE, which must NOT count as sales.
+  const isBooked = (f) => { const p = String(f['PAID ON ACCOUNT'] ?? '').trim(); return p !== '' && num(p) !== 0; };
+
+  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 amt = num(f[TOTAL_FIELD]);
-    mtdCount += 1; mtdTotal += amt;
-    if (String(f[DATE_FIELD] || '').trim() === todayFM) { dayCount += 1; dayTotal += amt; }
+    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 money = (n) => `$${n.toLocaleString('en-US', { minimumFractionDigits: 2, maximumFractionDigits: 2 })}`;
@@ -66,9 +75,10 @@ async function main() {
 
   const text =
     `*📊 Daily Sales Summary — ${dayLabel}*\n\n` +
-    `🗓️ *Today:* ${dayCount} order${dayCount === 1 ? '' : 's'} · *${money(dayTotal)}*\n` +
-    `📈 *Month-to-date (${monthLabel}):* ${mtdCount} order${mtdCount === 1 ? '' : 's'} · *${money(mtdTotal)}*\n\n` +
-    `_Source: FileMaker invoices — GRAND TOTAL by invoice date (Pacific). Excludes test #999999; refunds net out._`;
+    `🗓️ *Today — 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._`;
 
   if (DRY_RUN) { console.log('--- DRY RUN (not posted) ---\n' + text); return; }

← 4255ec9 sales-to-Slack via FileMaker: EOD daily+MTD summary + per-in · back to Filemaker Mcp · new-invoice-monitor: label booked sale vs unpaid quote (paym 700b05c →