← back to Filemaker Mcp

sales-to-Slack via FileMaker: EOD daily+MTD summary + per-invoice monitor + launchd timers (reuses fm-client; posts to #new-order)

4255ec91f95cb57238bee9d7a48246a22eb3888f · 2026-07-02 07:41:40 -0700 · Steve

Files touched

• M .gitignore
• A launchd/com.steve.daily-sales-summary.plist
• A launchd/com.steve.new-invoice-monitor.plist
• A scripts/new-invoice-monitor.mjs
• A scripts/sales-summary.mjs

Diff

commit 4255ec91f95cb57238bee9d7a48246a22eb3888f
Author: Steve <steve@designerwallcoverings.com>
Date:   Thu Jul 2 07:41:40 2026 -0700

    sales-to-Slack via FileMaker: EOD daily+MTD summary + per-invoice monitor + launchd timers (reuses fm-client; posts to #new-order)
---
 .gitignore                                  |  1 +
 launchd/com.steve.daily-sales-summary.plist | 11 ++++
 launchd/com.steve.new-invoice-monitor.plist | 12 ++++
 scripts/new-invoice-monitor.mjs             | 94 +++++++++++++++++++++++++++++
 scripts/sales-summary.mjs                   | 87 ++++++++++++++++++++++++++
 5 files changed, 205 insertions(+)

diff --git a/.gitignore b/.gitignore
index f3ad563..1ce9022 100644
--- a/.gitignore
+++ b/.gitignore
@@ -8,3 +8,4 @@ tmp/
 dist/
 build/
 data/
+data/.last-invoice
diff --git a/launchd/com.steve.daily-sales-summary.plist b/launchd/com.steve.daily-sales-summary.plist
new file mode 100644
index 0000000..6da8d46
--- /dev/null
+++ b/launchd/com.steve.daily-sales-summary.plist
@@ -0,0 +1,11 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
+<plist version="1.0"><dict>
+  <key>Label</key><string>com.steve.daily-sales-summary</string>
+  <key>ProgramArguments</key><array><string>/usr/local/bin/node</string><string>/Users/stevestudio2/Projects/filemaker-mcp/scripts/sales-summary.mjs</string></array>
+  <key>WorkingDirectory</key><string>/Users/stevestudio2/Projects/filemaker-mcp</string>
+  <key>StartCalendarInterval</key><dict><key>Hour</key><integer>23</integer><key>Minute</key><integer>55</integer></dict>
+  <key>StandardOutPath</key><string>/tmp/daily-sales-summary.log</string>
+  <key>StandardErrorPath</key><string>/tmp/daily-sales-summary.log</string>
+  <key>EnvironmentVariables</key><dict><key>PATH</key><string>/usr/local/bin:/usr/bin:/bin</string></dict>
+</dict></plist>
diff --git a/launchd/com.steve.new-invoice-monitor.plist b/launchd/com.steve.new-invoice-monitor.plist
new file mode 100644
index 0000000..77ad41e
--- /dev/null
+++ b/launchd/com.steve.new-invoice-monitor.plist
@@ -0,0 +1,12 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
+<plist version="1.0"><dict>
+  <key>Label</key><string>com.steve.new-invoice-monitor</string>
+  <key>ProgramArguments</key><array><string>/usr/local/bin/node</string><string>/Users/stevestudio2/Projects/filemaker-mcp/scripts/new-invoice-monitor.mjs</string></array>
+  <key>WorkingDirectory</key><string>/Users/stevestudio2/Projects/filemaker-mcp</string>
+  <key>StartInterval</key><integer>180</integer>
+  <key>RunAtLoad</key><true/>
+  <key>StandardOutPath</key><string>/tmp/new-invoice-monitor.log</string>
+  <key>StandardErrorPath</key><string>/tmp/new-invoice-monitor.log</string>
+  <key>EnvironmentVariables</key><dict><key>PATH</key><string>/usr/local/bin:/usr/bin:/bin</string></dict>
+</dict></plist>
diff --git a/scripts/new-invoice-monitor.mjs b/scripts/new-invoice-monitor.mjs
new file mode 100644
index 0000000..f3894cd
--- /dev/null
+++ b/scripts/new-invoice-monitor.mjs
@@ -0,0 +1,94 @@
+// Per-sale Slack ping → #new-order, sourced from the FileMaker invoice DB.
+// Polls today's invoices; posts one card per NEW invoice (number > last seen).
+// First run seeds the cursor without posting a backlog.
+//
+//   node scripts/new-invoice-monitor.mjs            # poll + post new invoices
+//   node scripts/new-invoice-monitor.mjs --dry-run  # print what it WOULD post
+
+import { readFileSync, writeFileSync, existsSync } from 'node:fs';
+import { fileURLToPath } from 'node:url';
+import { dirname, join } from 'node:path';
+import { findRecords } from '../src/fm-client.js';
+
+const __dir = dirname(fileURLToPath(import.meta.url));
+try {
+  for (const line of readFileSync(join(__dir, '..', '.env'), 'utf8').split('\n')) {
+    const m = line.match(/^\s*([A-Z0-9_]+)\s*=\s*(.*)\s*$/);
+    if (m && !(m[1] in process.env)) process.env[m[1]] = m[2].replace(/^["']|["']$/g, '').trim();
+  }
+} catch { /* env may already be exported */ }
+
+const TZ = 'America/Los_Angeles';
+const DB = 'invoice';
+const LAYOUT = 'GRAND TOTAL SALES';
+const CHANNEL = process.env.SALES_SUMMARY_CHANNEL || 'C06D9C2PG1K'; // #new-order
+const DRY_RUN = process.argv.includes('--dry-run');
+const CURSOR = join(__dir, '..', 'data', '.last-invoice');
+const EXCLUDE = new Set(['999999']);
+
+const num = (v) => { const n = parseFloat(v); return Number.isFinite(n) ? n : 0; };
+const money = (n) => `$${n.toLocaleString('en-US', { minimumFractionDigits: 2, maximumFractionDigits: 2 })}`;
+
+function ptYMD(d) {
+  const p = new Intl.DateTimeFormat('en-CA', { timeZone: TZ, year: 'numeric', month: '2-digit', day: '2-digit' }).formatToParts(d);
+  const g = (t) => p.find((x) => x.type === t).value;
+  return { y: +g('year'), m: +g('month'), d: +g('day') };
+}
+
+async function post(text) {
+  const token = process.env.SLACK_BOT_TOKEN;
+  const res = await fetch('https://slack.com/api/chat.postMessage', {
+    method: 'POST',
+    headers: { Authorization: `Bearer ${token}`, 'Content-Type': 'application/json; charset=utf-8' },
+    body: JSON.stringify({ channel: CHANNEL, text, unfurl_links: false }),
+  });
+  const j = await res.json();
+  if (!j.ok) throw new Error(`Slack post failed: ${j.error}`);
+  return j.ts;
+}
+
+async function main() {
+  const { y, m, d } = ptYMD(new Date());
+  // Poll today's invoices (sales come in same-day); sort by invoice number ascending.
+  const { records } = await findRecords(
+    DB, LAYOUT,
+    [{ Date: `${m}/${d}/${y}` }],
+    { limit: 500, sort: [{ fieldName: 'Invoice', sortOrder: 'ascend' }] },
+  );
+
+  // Numeric invoices only, excluding test records.
+  const rows = records
+    .map((r) => r.fieldData || {})
+    .filter((f) => /^\d+$/.test(String(f['Invoice'] || '').trim()) && !EXCLUDE.has(String(f['Invoice']).trim()))
+    .map((f) => ({ n: parseInt(f['Invoice'], 10), f }))
+    .sort((a, b) => a.n - b.n);
+
+  const lastSeen = existsSync(CURSOR) ? parseInt(readFileSync(CURSOR, 'utf8').trim(), 10) || 0 : null;
+
+  if (lastSeen === null) {
+    const max = rows.length ? rows[rows.length - 1].n : 0;
+    if (!DRY_RUN) writeFileSync(CURSOR, String(max));
+    console.log(`[seed] no cursor — set last-invoice=${max} (${rows.length} today), no posts.`);
+    return;
+  }
+
+  const fresh = rows.filter((r) => r.n > lastSeen && num(r.f['GRAND TOTAL 2']) !== 0); // skip empty shells
+  if (!fresh.length) { console.log(`No new invoices (last=${lastSeen}, ${rows.length} today).`); return; }
+
+  let maxPosted = lastSeen;
+  for (const { n, f } of fresh) {
+    const client = String(f['Sold Company Name Lookup'] || 'Customer').replace(/\s+$/, '');
+    const total = num(f['GRAND TOTAL 2']);
+    const item = String(f['DETAIL 1(1)'] || f['DETAIL 1'] || '').replace(/\s+$/, '').slice(0, 80);
+    const rep = f['SALES PERSON'] ? ` · ${f['SALES PERSON']}` : '';
+    const tag = total < 0 ? '↩️ *Refund*' : '🛎️ *New sale*';
+    const text = `${tag} — ${client} · Invoice #${n} · *${money(total)}*${rep}${item ? `\n• ${item}` : ''}`;
+    if (DRY_RUN) console.log('[would post] ' + text.replace(/\n/g, '  '));
+    else { await post(text); }
+    if (n > maxPosted) maxPosted = n;
+  }
+  if (!DRY_RUN) writeFileSync(CURSOR, String(maxPosted));
+  console.log(`${DRY_RUN ? '[dry] ' : ''}posted ${fresh.length} new invoice(s); cursor → ${maxPosted}.`);
+}
+
+main().catch((e) => { console.error('new-invoice-monitor error:', e.message); process.exit(1); });
diff --git a/scripts/sales-summary.mjs b/scripts/sales-summary.mjs
new file mode 100644
index 0000000..18feb54
--- /dev/null
+++ b/scripts/sales-summary.mjs
@@ -0,0 +1,87 @@
+// 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.
+//
+//   node scripts/sales-summary.mjs            # compute + POST to Slack
+//   node scripts/sales-summary.mjs --dry-run  # compute + print only (no post)
+
+import { readFileSync } from 'node:fs';
+import { fileURLToPath } from 'node:url';
+import { dirname, join } from 'node:path';
+import { findRecords } from '../src/fm-client.js';
+
+// --- load .env into process.env (dotenv isn't installed here) ---
+const __dir = dirname(fileURLToPath(import.meta.url));
+try {
+  for (const line of readFileSync(join(__dir, '..', '.env'), 'utf8').split('\n')) {
+    const m = line.match(/^\s*([A-Z0-9_]+)\s*=\s*(.*)\s*$/);
+    if (m && !(m[1] in process.env)) process.env[m[1]] = m[2].replace(/^["']|["']$/g, '').trim();
+  }
+} catch { /* env may already be exported (e.g. launchd) */ }
+
+const TZ = 'America/Los_Angeles';
+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 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 pad = (n) => String(n).padStart(2, '0');
+const num = (v) => { const n = parseFloat(v); return Number.isFinite(n) ? n : 0; };
+
+function ptYMD(d) {
+  const p = new Intl.DateTimeFormat('en-CA', { timeZone: TZ, year: 'numeric', month: '2-digit', day: '2-digit' }).formatToParts(d);
+  const g = (t) => p.find((x) => x.type === t).value;
+  return { y: +g('year'), m: +g('month'), d: +g('day') };
+}
+
+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
+
+  // One MTD pull; bucket "today" client-side.
+  const { records } = await findRecords(
+    DB, LAYOUT,
+    [{ [DATE_FIELD]: `${monthStart}...${todayStr}` }],
+    { limit: 2000, sort: [{ fieldName: DATE_FIELD, sortOrder: 'ascend' }] },
+  );
+
+  let dayCount = 0, dayTotal = 0, mtdCount = 0, mtdTotal = 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 money = (n) => `$${n.toLocaleString('en-US', { minimumFractionDigits: 2, maximumFractionDigits: 2 })}`;
+  const dayLabel = new Intl.DateTimeFormat('en-US', { timeZone: TZ, weekday: 'short', month: 'short', day: 'numeric', year: 'numeric' }).format(now);
+  const monthLabel = new Intl.DateTimeFormat('en-US', { timeZone: TZ, month: 'long', year: 'numeric' }).format(now);
+
+  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._`;
+
+  if (DRY_RUN) { console.log('--- DRY RUN (not posted) ---\n' + text); return; }
+
+  const token = process.env.SLACK_BOT_TOKEN;
+  if (!token) throw new Error('SLACK_BOT_TOKEN not set.');
+  const res = await fetch('https://slack.com/api/chat.postMessage', {
+    method: 'POST',
+    headers: { Authorization: `Bearer ${token}`, 'Content-Type': 'application/json; charset=utf-8' },
+    body: JSON.stringify({ channel: CHANNEL, text, unfurl_links: false }),
+  });
+  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)}`);
+}
+
+main().catch((e) => { console.error('sales-summary error:', e.message); process.exit(1); });

← 5a0d50e auto-save: 2026-07-02T07:11:42 (1 files) — scripts/relookup-  ·  back to Filemaker Mcp  ·  sales-summary: count BOOKED orders only (payment received); 7bf6a1d →