`scripts/dashboard-revenue.mjs`

62 lines
// Compute the FMPro direct-invoicing revenue for the ads-dashboard (trailing 30d,
// booked/net, matching the dashboard's Shopify window) and write an AGGREGATE-ONLY
// JSON (no customer data). A separate push step scps it to the Kamatera box; the
// dashboard reads it and stamps it "as of" so a stale number can't look live.
//
//   node scripts/dashboard-revenue.mjs           # write /tmp/fmpro-revenue.json
//   node scripts/dashboard-revenue.mjs --print    # also print the summary
//
// Mirrors sales-summary.mjs semantics: booked = PAID ON ACCOUNT != 0; refunds are
// negative booked rows that net out of dollars but don't inflate order count;
// excludes test invoice #999999.

import { writeFileSync } from 'node:fs';
import { findRecords } from '../src/fm-client.js';
import { loadEnv, num } from '../lib/fm-script-helpers.js';

loadEnv(import.meta.url);

const DB = 'invoice', LAYOUT = 'GRAND TOTAL SALES';
const TOTAL_FIELD = 'GRAND TOTAL 2', DATE_FIELD = 'Date';
const WINDOW_DAYS = 30, FETCH_LIMIT = 5000;
const OUT = process.env.FMPRO_OUT || '/tmp/fmpro-revenue.json';
const pad = (n) => String(n).padStart(2, '0');
const fmt = (x) => `${pad(x.getMonth() + 1)}/${pad(x.getDate())}/${x.getFullYear()}`;

const now = new Date(), from = new Date(Date.now() - WINDOW_DAYS * 864e5);
const { records, dataInfo } = await findRecords(
  DB, LAYOUT,
  [{ [DATE_FIELD]: `${fmt(from)}...${fmt(now)}` }],
  { limit: FETCH_LIMIT, sort: [{ fieldName: DATE_FIELD, sortOrder: 'ascend' }] },
);

// Truncation guard — same failure class as sales-summary: never report a silently
// short fetch as a real total.
const found = Number(dataInfo?.foundCount ?? records.length);
const returned = Number(dataInfo?.returnedCount ?? records.length);
const truncated = found > returned || records.length >= FETCH_LIMIT;

let booked = 0, orders = 0, quotes = 0;
for (const r of records) {
  const f = r.fieldData || {};
  if (String(f['Invoice'] || '').trim() === '999999') continue;
  const paid = String(f['PAID ON ACCOUNT'] ?? '').trim();
  const amt = num(f[TOTAL_FIELD]);
  if (paid !== '' && num(paid) !== 0) { booked += amt; if (amt > 0) orders += 1; }
  else quotes += 1;
}

const out = {
  source: 'fmpro-invoicing',
  present: !truncated,
  truncated,
  window: `${fmt(from)}–${fmt(now)} (last ${WINDOW_DAYS}d)`,
  total: Math.round(booked * 100) / 100,
  orders,
  open_quotes: quotes,
  generated_at: now.toISOString(),
};
writeFileSync(OUT, JSON.stringify(out, null, 2));
if (process.argv.includes('--print')) console.log(out);
console.log(`wrote ${OUT}: $${out.total.toLocaleString()} / ${out.orders} booked (${truncated ? 'TRUNCATED' : 'ok'})`);