← back to Dw Pitch Followup
scripts/build-lists.js
417 lines
// build-lists.js — READ-ONLY FileMaker → data/lists.json
//
// Three follow-up "pitch" populations for Designer Wallcoverings:
// List 1 Samples-sent clients — >=90% of ordered samples have shipped (follow up to convert).
// List 2 Quoted · No Order (4mo) — got a REAL quote in the last ~4 months (unbooked merch
// invoice with a real $ total + line details) but NEVER
// booked an order. "Invoiced · No Order" = quoted, didn't buy.
// List 3 Overdue vendor memos — samples WE requested >8 days ago, not yet received (chase vendor).
//
// No writes anywhere (lib/fm.js forces FM_READONLY=1). No email. Pure read → JSON.
import fs from 'node:fs';
import path from 'node:path';
import { execFileSync } from 'node:child_process';
import { fileURLToPath } from 'node:url';
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');
fs.mkdirSync(DATA_DIR, { recursive: true });
const TODAY = process.env.PITCH_TODAY ? new Date(process.env.PITCH_TODAY) : new Date(); // build time, not a hardcode (fixes negative-age bug)
const LOOKBACK_SENT_DAYS = Number(process.env.LOOKBACK_SENT_DAYS || 120); // List 1 recency window
const INVOICE_SINCE = process.env.INVOICE_SINCE || '03/01/2026'; // List 2: last ~4 months
const FIRM_ORDER_MIN = Number(process.env.FIRM_ORDER_MIN || 50); // List 2: TOTAL 1 > this = firm order
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.
function pick(fd, regexes) {
for (const re of regexes) {
for (const [k, v] of Object.entries(fd)) {
if (re.test(k) && v !== '' && v != null) return v;
}
}
return '';
}
function nval(v) { const n = Number(String(v).replace(/[^0-9.\-]/g, '')); return Number.isFinite(n) ? n : 0; }
function clean(s) { return String(s || '').replace(/[\r\n]+/g, ' ').trim(); }
// FileMaker MM/DD/YYYY → ISO (for sorting + the viewer's date chip). '' on parse fail.
function fmToISO(s) {
const m = clean(s).match(/(\d{1,2})\/(\d{1,2})\/(\d{4})/);
if (!m) return '';
const [, mm, dd, yyyy] = m;
const year = Number(yyyy);
if (year < 2000 || year > 2100) return ''; // reject source typos like "04/30/0225"
const d = new Date(year, Number(mm) - 1, Number(dd), 9, 0, 0);
return isNaN(d) ? '' : d.toISOString();
}
function daysSince(iso) {
if (!iso) return null;
return Math.round((TODAY - new Date(iso)) / 86400000);
}
function nameFromEmail(email) {
const lp = String(email || '').split('@')[0] || '';
return lp.replace(/[._-]+/g, ' ').replace(/\b\w/g, (c) => c.toUpperCase()).trim();
}
// ====================================================================
// LIST 1 — >=90% of ordered samples sent (drive off recent sends, then ratio)
// ====================================================================
async function buildList1() {
const since = fmDate(daysAgo(LOOKBACK_SENT_DAYS, TODAY));
const rows = await findAll(
'WALLPAPER', 'REPORT ON SAMPLES ORDERED',
{ 'Date WP Sample Sent': `>=${since}` },
{ pageSize: 100, cap: 6000, sort: [{ fieldName: 'Date WP Sample Sent', sortOrder: 'descend' }] }
);
// Group sent samples by account.
const byAcct = new Map();
for (const r of rows) {
const fd = r.fieldData;
const acct = String(pick(fd, [/^account$/i, /account *#/i, /account number/i]) || '').trim();
if (!acct) continue;
const sentISO = fmToISO(pick(fd, [/date wp sample sent/i]));
const sku = clean(pick(fd, [/combo sku/i, /^sku/i]));
const pattern = clean(pick(fd, [/name of pattern/i, /mfr pattern/i, /^pattern/i]));
const company = clean(pick(fd, [/clients::company/i, /company for client/i]));
const g = byAcct.get(acct) || { account: acct, lastSentISO: '', patterns: [], company: '' };
if (sentISO > g.lastSentISO) g.lastSentISO = sentISO;
if (company && !g.company) g.company = company;
if (sku || pattern) g.patterns.push([pattern, sku].filter(Boolean).join(' '));
byAcct.set(acct, g);
}
const accounts = [...byAcct.keys()];
const parents = await findProjectsByAccounts(accounts);
const out = [];
for (const [acct, g] of byAcct) {
const p = parents.get(acct);
if (!p) continue;
const fd = p.fieldData;
const sent = nval(fd['Count # Samples Sent']);
const ordered = nval(fd['Count # Client Samples Ordered']);
if (ordered < 1) continue;
const ratio = sent / ordered;
if (ratio < 0.9) continue;
const email = clean(pick(fd, [/^email address$/i, /email for gmail/i, /e-?mail/i]));
const company = g.company || nameFromEmail(email) || `Account ${acct}`;
out.push({
account: acct,
company,
email,
client_type: clean(pick(fd, [/^client type$/i])),
sent, ordered, missing: nval(fd['Number of Samples Missing']),
ratio: Math.round(ratio * 100),
patterns: [...new Set(g.patterns)].slice(0, 8),
when_iso: g.lastSentISO,
when_label: 'last sample sent',
created_at: g.lastSentISO,
});
}
out.sort((a, b) => (b.created_at || '').localeCompare(a.created_at || ''));
return out.slice(0, CAP);
}
// ====================================================================
// LIST 2 — "Quoted · No Order": got a real quote in the last ~4 months but never booked.
// This function only produces the CANDIDATE pool — accounts with a real-dollar invoice in
// the window (a quote OR a booked order). enrichMerch() then reads Date Order Booked to split
// booked vs quote, and main() keeps only accounts carrying an open quote with NO booked order.
// ====================================================================
async function buildList2() {
const rows = await findAll(
'invoice', 'main menu Print menu in invoice',
{ 'Date': `>=${INVOICE_SINCE}` },
{ pageSize: 100, cap: 4000, sort: [{ fieldName: 'Date', sortOrder: 'descend' }] }
);
const byAcct = new Map();
for (const r of rows) {
const fd = r.fieldData;
const acct = String(pick(fd, [/account *#/i, /^account$/i, /account number/i]) || '').trim();
if (!acct) continue;
const total = nval(pick(fd, [/grand total 2/i, /^total 1/i, /total/i]));
const detail = clean(pick(fd, [/^detail 1/i, /detail/i]));
const dISO = fmToISO(pick(fd, [/^date$/i, /date order booked/i]));
const inv = clean(pick(fd, [/^invoice$/i]));
const company = clean(pick(fd, [/sold company name/i, /company/i, /^name$/i]));
const g = byAcct.get(acct) || { account: acct, company, lastISO: '', count: 0, maxTotal: 0, details: [] };
g.count++;
if (company && !g.company) g.company = company;
if (total > g.maxTotal) g.maxTotal = total;
if (dISO > g.lastISO) g.lastISO = dISO;
if (detail) g.details.push(detail.slice(0, 80));
byAcct.set(acct, g);
}
// Candidate pool = accounts with a real-dollar invoice in the window (a quote OR a booked
// order — sample-only accounts are dropped). enrichMerch()+main() decide quote-vs-booked.
const kept = [...byAcct.values()].filter((g) => g.maxTotal > FIRM_ORDER_MIN);
const parents = await findProjectsByAccounts(kept.map((g) => g.account));
const out = kept.map((g) => {
const p = parents.get(g.account);
const email = p ? clean(pick(p.fieldData, [/^email address$/i, /email for gmail/i, /e-?mail/i])) : '';
return {
account: g.account,
company: clean(g.company) || nameFromEmail(email) || `Account ${g.account}`,
email,
client_type: p ? clean(pick(p.fieldData, [/^client type$/i])) : '',
invoice_count: g.count,
max_total: g.maxTotal,
details: [...new Set(g.details)].slice(0, 6),
when_iso: g.lastISO,
when_label: 'last invoiced',
created_at: g.lastISO,
};
});
out.sort((a, b) => (b.created_at || '').localeCompare(a.created_at || ''));
return out.slice(0, CAP);
}
// ====================================================================
// LIST 3 — samples WE requested >8 days ago, not yet received (chase vendor)
// ====================================================================
async function buildList3() {
// Actionable window: requested between OVERDUE_DAYS and OVERDUE_MAX_DAYS ago.
// A 2-year-old un-received memo is a dead ghost, not a vendor to chase today.
const before = fmDate(daysAgo(OVERDUE_DAYS, TODAY));
const after = fmDate(daysAgo(OVERDUE_MAX_DAYS, TODAY));
// NOTE: this layout returns 0 data rows unless an explicit sort is supplied
// (empty-match `=` criterion triggers the quirk). Drop the duplicate-on-layout
// `Date Discontinued` clause (it breaks the find).
const rows = await findAll(
'WALLPAPER', 'REPORT ON SAMPLES ORDERED',
{ 'Date Sample Request printed for vendor': `${after}...${before}`, 'Date WP Sample Sent': '=' },
{ pageSize: 100, cap: 4000, sort: [{ fieldName: 'Date Sample Request printed for vendor', sortOrder: 'ascend' }] }
);
const out = [];
for (const r of rows) {
const fd = r.fieldData;
const reqISO = fmToISO(pick(fd, [/date sample request printed for vendor/i]));
if (!reqISO) continue;
const acct = String(pick(fd, [/^account$/i, /account *#/i]) || '').trim();
out.push({
account: acct,
vendor: clean(pick(fd, [/^supplier$/i, /^vid$/i, /vendor/i])) || 'Unknown vendor',
company: clean(pick(fd, [/clients::company/i, /company for client/i, /company/i])),
sku: clean(pick(fd, [/combo sku/i])),
pattern: clean(pick(fd, [/name of pattern/i, /mfr pattern/i, /^pattern/i])),
req_iso: reqISO,
days_overdue: daysSince(reqISO),
when_iso: reqISO,
when_label: 'requested',
created_at: reqISO,
});
}
// Oldest-overdue first within vendor grouping.
out.sort((a, b) => (b.days_overdue || 0) - (a.days_overdue || 0));
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 sku = clean(fd['combo sku'] || '');
const label = sku || clean(fd['Mfr Pattern'] || '') || 'sample';
const sent = fmToISO(fd['Date WP Sample Sent']);
// `Date Discontinued` is a WALLPAPER product-level field: if present, this sampled item is
// DEAD STOCK — a "how did the samples go / ready to order?" follow-up can't convert on it.
// Carry it through so the viewer can red-flag "don't send a blanket pitch." (Steve 2026-07-13)
const disco = fmToISO(fd['Date Discontinued']);
const g = map.get(acct) || { sent: [], pending: [] };
if (sent) g.sent.push({ label, sku, date: sent, disco }); else g.pending.push({ label, sku, disco });
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).
// Each invoice flagged BOOKED (Date Order Booked present = a real sale) vs QUOTE (unbooked).
// lifetime = sum of BOOKED merch (real sales, all time); quoted = sum of open quotes.
// project = the most-recent order's line detail (room/project), best-available "current project".
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 booked = !!fmToISO(fd['Date Order Booked']);
const dISO = fmToISO(fd['Date Order Booked'] || fd['Date']);
// the item/sku on this order (line-1 detail), for the "ordered since sampling" flag
const item = clean(fd['DETAIL 1(1)'] || fd['Detail 1 Mfr Number(1)'] || '').slice(0, 60);
// project/room descriptor for this order (Sidemark first, else the line detail)
const proj = clean(fd['Sidemark 1(1)'] || '') || clean(fd['DETAIL 1(1)'] || '');
const g = map.get(acct) || { invoices: [], lifetime: 0, quoted: 0, project: '', projectISO: '' };
g.invoices.push({ invoice: inv, date: dISO, total, booked, item });
if (booked) g.lifetime += total; else g.quoted += total;
if (dISO && dISO > (g.projectISO || '') && proj) { g.project = proj; g.projectISO = dISO; }
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, 15);
}
return map;
}
// Per account → the client contact's first name (from Clients "Find a client" — a lean,
// portal-free layout with a real Name field, e.g. "Debbie Morris"). Used for the greeting.
async function enrichNames(accounts) {
const recs = await findByAccounts('Clients', 'Find a client', 'Account Number', accounts, { chunk: 40, per: 3 });
const map = new Map();
for (const rec of recs) {
const fd = rec.fieldData;
const acct = String(fd['Account Number'] || '').trim(); if (!acct || map.has(acct)) continue;
let nm = clean(fd['Name'] || '');
if (!nm) continue;
let first = nm.split(/[\s,]+/).find((w) => w && !/^\d+$/.test(w)) || '';
if (first && first === first.toUpperCase() && first.length > 1) first = first[0] + first.slice(1).toLowerCase();
if (first) map.set(acct, first);
}
return map;
}
// FileMaker combo sku (undashed, e.g. DWC319153) → dw_unified dw_sku (dashed, DWC-319153).
function dashSku(s) { return String(s || '').trim().replace(/^([A-Za-z]+)(\d.*)$/, '$1-$2'); }
// Look up each sample's product page URL + thumbnail image from the local dw_unified catalog
// (Postgres). Returns Map(dashedSku → { url, image }). URL only when the product is ACTIVE
// (published) so client links never 404; image whenever present.
function enrichSampleProducts(skus) {
const dashed = [...new Set(skus.map(dashSku).filter(Boolean))];
const map = new Map();
if (!dashed.length) return map;
const PSQL = process.env.PSQL_BIN || '/opt/homebrew/opt/postgresql@14/bin/psql';
const CONN = process.env.DW_UNIFIED_URL || 'postgresql://localhost/dw_unified';
const arr = '{' + dashed.map((s) => '"' + s.replace(/[^A-Za-z0-9._-]/g, '') + '"').join(',') + '}';
const q = `SELECT dw_sku, handle, coalesce(image_url,''), status FROM shopify_products WHERE dw_sku = ANY('${arr}')`;
try {
const out = execFileSync(PSQL, [CONN, '-tAF', '\t', '-c', q], { maxBuffer: 128 * 1024 * 1024 }).toString();
for (const line of out.split('\n')) {
if (!line.trim()) continue;
const [dw_sku, handle, image, status] = line.split('\t');
const active = /^active$/i.test(status || '');
map.set(dw_sku, {
url: (handle && active) ? `https://designerwallcoverings.com/products/${handle}` : '',
image: image || '',
});
}
} catch (e) { console.error('[build-lists] product lookup failed:', e.message); }
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, namesMap] = [await enrichSamples(accounts), await enrichMerch(accounts), await enrichNames(accounts)];
// Attach product page URL + thumbnail to each sample (from dw_unified).
const allSkus = [];
for (const g of samplesMap.values()) for (const it of [...g.sent, ...g.pending]) allSkus.push(it.sku || it.label);
const prodMap = enrichSampleProducts(allSkus);
let withImg = 0;
for (const g of samplesMap.values()) for (const it of [...g.sent, ...g.pending]) {
const p = prodMap.get(dashSku(it.sku || it.label));
if (p) { if (p.url) it.url = p.url; if (p.image) { it.image = p.image; withImg++; } }
}
console.log(`[build-lists] product-matched ${withImg} sample line(s) to images/links`);
let withSamples = 0, withMerch = 0, withName = 0;
for (const r of [...list1, ...list2]) {
const fn = namesMap.get(String(r.account));
if (fn) { r.first_name = fn; withName++; }
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.lifetime_sales = m.lifetime; // BOOKED merch only (real sales)
r.quoted_total = m.quoted; // open quotes (not yet booked)
if (m.project) r.project = m.project;
withMerch++;
}
}
// LIST 2 is "Quoted · No Order": keep ONLY accounts carrying an open (unbooked) merch quote
// with NO booked order ever. quoted_total / lifetime_sales come from enrichMerch (merch only,
// samples excluded). lifetime_sales is summed all-time BEFORE the 15-invoice display slice, so
// it's the authoritative "did they ever book" signal; merch_invoices is display best-effort.
const list2Quoted = [];
for (const r of list2) {
if (!(r.quoted_total > 0)) continue; // must carry a real open quote
if (r.lifetime_sales > 0) continue; // and have never booked a merch order
const quotes = (Array.isArray(r.merch_invoices) ? r.merch_invoices : []).filter((i) => !i.booked);
r.quote_total = r.quoted_total;
r.quote_count = quotes.length || undefined;
r.quote_items = [...new Set(quotes.map((i) => i.item).filter(Boolean))].slice(0, 6);
const lastQ = quotes.map((i) => i.date).filter(Boolean).sort().pop() || r.when_iso;
if (lastQ) { r.when_iso = lastQ; r.created_at = lastQ; }
r.when_label = 'last quoted';
list2Quoted.push(r);
}
list2.length = 0; list2.push(...list2Quoted);
// FLAG any invoice dated on/after the client started sampling — a possible conversion.
let withPost = 0;
for (const r of [...list1, ...list2]) {
const invs = Array.isArray(r.merch_invoices) ? r.merch_invoices : [];
const sampleDates = (Array.isArray(r.samples_sent) ? r.samples_sent : []).map((s) => s && s.date).filter(Boolean).sort();
const ref = sampleDates[0] || r.when_iso || '';
if (!invs.length || !ref) continue;
const post = invs.filter((inv) => inv.date && inv.date >= ref).map((inv) => ({ invoice: inv.invoice, total: inv.total, date: inv.date, booked: inv.booked, item: inv.item || '' }));
if (post.length) { r.post_sample_invoices = post; r.post_sample_since = ref; withPost++; }
}
console.log(`[build-lists] enriched: ${withSamples} rows w/ samples, ${withMerch} rows w/ merch orders, ${withName} rows w/ contact first name, ${withPost} rows w/ orders SINCE sampling`);
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, merch_min: MERCH_MIN, overdue_days: OVERDUE_DAYS, cap: CAP },
counts: { list1: list1.length, list2: list2.length, list3: list3.length },
list1, list2, list3,
};
fs.writeFileSync(path.join(DATA_DIR, 'lists.json'), JSON.stringify(payload, null, 2));
console.log(`[build-lists] done in ${((Date.now() - t0) / 1000).toFixed(1)}s →`, payload.counts, '($0 — local FileMaker reads)');
}
main().catch((e) => { console.error('[build-lists] FATAL', e); process.exit(1); });