auto-save: 2026-07-07T14:31:09 (2 files) — public/index.html scripts/build-lists.js

28a382be343a0e6869f7a37c54fcf67f493299df · 2026-07-07 14:31:11 -0700 · Steve Abrams
Files touched

M public/index.html
M scripts/build-lists.js
Diff

commit 28a382be343a0e6869f7a37c54fcf67f493299df
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Tue Jul 7 14:31:11 2026 -0700

    auto-save: 2026-07-07T14:31:09 (2 files) — public/index.html scripts/build-lists.js
---
 public/index.html      |  2 +-
 scripts/build-lists.js | 84 ++++++++++++++++++++++++++++++++++++++++++++++++--
 2 files changed, 83 insertions(+), 3 deletions(-)

diff --git a/public/index.html b/public/index.html
index 36bfe04..cd3f7a0 100644
--- a/public/index.html
+++ b/public/index.html
@@ -189,7 +189,7 @@ function samplesBlock(r){
   const li = (arr,cls,suffix)=>arr.slice(0,12).map(s=>{
     const name = typeof s==='string'?s:(s.label||s.sku||'sample');
     const when = (s&&s.date)?` · ${esc(fmtDay(s.date))}`:'';
-    return `<li><span class="dot ${cls}"></span>${esc(lbl(name))}${suffix?when:''}</li>`;
+    return `<li><span class="dot ${cls}"></span>${esc(name)}${suffix?when:''}</li>`;
   }).join('');
   let out='';
   if(sent.length) out+=`<div class="drow"><b>Samples sent (${sent.length})</b><ul class="slist">${li(sent,'ok',true)}</ul></div>`;
diff --git a/scripts/build-lists.js b/scripts/build-lists.js
index fb8bb8d..c8310d6 100644
--- a/scripts/build-lists.js
+++ b/scripts/build-lists.js
@@ -10,7 +10,7 @@
 import fs from 'node:fs';
 import path from 'node:path';
 import { fileURLToPath } from 'node:url';
-import { findAll, findProjectsByAccounts, fmDate, daysAgo } from '../lib/fm.js';
+import { findAll, findProjectsByAccounts, findRecords, fmDate, daysAgo } from '../lib/fm.js';
 
 const __dirname = path.dirname(fileURLToPath(import.meta.url));
 const DATA_DIR = path.join(__dirname, '..', 'data');
@@ -23,6 +23,9 @@ const FIRM_ORDER_MIN = Number(process.env.FIRM_ORDER_MIN || 50);          // Lis
 const OVERDUE_DAYS = Number(process.env.OVERDUE_DAYS || 8);               // List 3: requested > N days ago
 const OVERDUE_MAX_DAYS = Number(process.env.OVERDUE_MAX_DAYS || 180);     // List 3: ignore ghost memos older than this
 const CAP = Number(process.env.LIST_CAP || 600);
+// Merchandise vs sample invoices: real orders are generally > $250; sample invoices run
+// ~$4.25–$35 (Steve 2026-07-07). Anything at/below this is treated as a sample, not merch.
+const MERCH_MIN = Number(process.env.MERCH_MIN || 250);
 
 // --- generic helpers ------------------------------------------------
 // Pick the first populated value whose key matches any of the regexes.
@@ -202,14 +205,91 @@ async function buildList3() {
   return out.slice(0, CAP);
 }
 
+// ====================================================================
+// ENRICHMENT — attach to list1/list2 rows: samples sent/pending + merch invoices
+// ====================================================================
+// Bulk OR-find by account, in chunks (Data API caps a single find's page size).
+async function findByAccounts(db, layout, field, accounts, { chunk = 30, per = 60 } = {}) {
+  const out = [];
+  const uniq = [...new Set(accounts.filter(Boolean).map(String))];
+  for (let i = 0; i < uniq.length; i += chunk) {
+    const slice = uniq.slice(i, i + chunk);
+    const query = slice.map((a) => ({ [field]: `==${a}` }));
+    try {
+      const r = await findRecords(db, layout, query, { limit: slice.length * per });
+      out.push(...(r.records || []));
+    } catch (e) { if (String(e.fmCode) !== '401') throw e; } // 401 = no matches
+  }
+  return out;
+}
+
+// Per account → which samples SHIPPED (Date WP Sample Sent present) vs still PENDING.
+async function enrichSamples(accounts) {
+  const recs = await findByAccounts('WALLPAPER', 'REPORT ON SAMPLES ORDERED', 'account', accounts, { chunk: 30, per: 60 });
+  const map = new Map();
+  for (const rec of recs) {
+    const fd = rec.fieldData;
+    const acct = String(fd['account'] || '').trim(); if (!acct) continue;
+    const label = clean(fd['combo sku'] || '') || clean(fd['Mfr Pattern'] || '') || 'sample';
+    const sent = fmToISO(fd['Date WP Sample Sent']);
+    const g = map.get(acct) || { sent: [], pending: [] };
+    if (sent) g.sent.push({ label, date: sent }); else g.pending.push({ label });
+    map.set(acct, g);
+  }
+  for (const g of map.values()) {
+    g.sent.sort((a, b) => (b.date || '').localeCompare(a.date || ''));
+    g.sent = g.sent.slice(0, 20); g.pending = g.pending.slice(0, 20);
+  }
+  return map;
+}
+
+// Per account → MERCHANDISE invoices (real orders > MERCH_MIN; sample invoices excluded).
+async function enrichMerch(accounts) {
+  const recs = await findByAccounts('invoice', 'Order Detail', 'Account #', accounts, { chunk: 25, per: 40 });
+  const map = new Map();
+  const seen = new Set();
+  for (const rec of recs) {
+    const fd = rec.fieldData;
+    const acct = String(fd['Account #'] || '').trim(); if (!acct) continue;
+    const inv = clean(fd['Invoice'] || ''); if (!inv) continue;
+    const total = nval(fd['merch tot2'] || fd['MERCHANDISE TOTAL(1)'] || fd['TOTAL 1(1)'] || 0);
+    if (total <= MERCH_MIN) continue; // real order vs sample invoice
+    const key = acct + '|' + inv; if (seen.has(key)) continue; seen.add(key);
+    const dISO = fmToISO(fd['Date Order Booked'] || fd['Date']);
+    const g = map.get(acct) || { invoices: [], total: 0 };
+    g.invoices.push({ invoice: inv, date: dISO, total });
+    g.total += total;
+    map.set(acct, g);
+  }
+  for (const g of map.values()) {
+    g.invoices.sort((a, b) => (b.date || '').localeCompare(a.date || ''));
+    g.invoices = g.invoices.slice(0, 12);
+  }
+  return map;
+}
+
 // ====================================================================
 async function main() {
   const t0 = Date.now();
   console.log('[build-lists] FileMaker reads (read-only)…');
   const [list1, list2, list3] = [await buildList1(), await buildList2(), await buildList3()];
+
+  // Enrich client lists (1 & 2) with samples sent/pending + merchandise invoices.
+  console.log('[build-lists] enriching samples + merchandise invoices…');
+  const accounts = [...new Set([...list1, ...list2].map((r) => String(r.account)))];
+  const [samplesMap, merchMap] = [await enrichSamples(accounts), await enrichMerch(accounts)];
+  let withSamples = 0, withMerch = 0;
+  for (const r of [...list1, ...list2]) {
+    const s = samplesMap.get(String(r.account));
+    if (s && (s.sent.length || s.pending.length)) { r.samples_sent = s.sent; r.samples_pending = s.pending; withSamples++; }
+    const m = merchMap.get(String(r.account));
+    if (m && m.invoices.length) { r.merch_invoices = m.invoices; r.merch_total = m.total; withMerch++; }
+  }
+  console.log(`[build-lists] enriched: ${withSamples} rows w/ samples, ${withMerch} rows w/ merch orders`);
+
   const payload = {
     generatedAt: new Date().toISOString(),
-    thresholds: { ratio_min: 0.9, lookback_sent_days: LOOKBACK_SENT_DAYS, invoice_since: INVOICE_SINCE, firm_order_min: FIRM_ORDER_MIN, overdue_days: OVERDUE_DAYS, cap: CAP },
+    thresholds: { ratio_min: 0.9, lookback_sent_days: LOOKBACK_SENT_DAYS, invoice_since: INVOICE_SINCE, firm_order_min: FIRM_ORDER_MIN, merch_min: MERCH_MIN, overdue_days: OVERDUE_DAYS, cap: CAP },
     counts: { list1: list1.length, list2: list2.length, list3: list3.length },
     list1, list2, list3,
   };
← c660a28 Compact cards + Columns/One-list layout toggle + ▸details ex · back to Dw Pitch Followup · Enrich cards with samples sent/pending + merchandise invoice 56b2381 →