/* © 2026 K2B NETWORK SOLUTIONS LLC. All rights reserved. */ /* eslint-disable */ // OldReportsData — turns what the AI page reader returned for an "old reports" packet into import-ready // datasets with tie-out checks. PURE LOGIC: no DOM, no network, no storage. // // OldReportsData.PAGE_TYPES / IMPORT_ORDER the catalog (labels, where each page type goes, why not) // OldReportsData.buildDatasets(results, opts) -> { datasets, reference, blankPages, problems, crossChecks, pageNotes, overall } // OldReportsData.applyFix(dataset, fixId) -> a NEW dataset (checks recomputed); never mutates // OldReportsData.csvOf(dataset) -> Excel-safe CSV text (BOM, every cell quoted) // OldReportsData.estimateCost(pageCount, pricing) -> { low, high, typical, text } // OldReportsData.create({ destFields }) -> an instance bound to a destination-field map (node tests) // // results[i] = what POST /api/ai-import/read-page returned plus { fileName, pageNo }: // { fileName, pageNo, ok, pageType, orientation, legibility, notes[], data } (failures: ok:false, error, code) // Headers of every dataset are the destination field LABELS of DMS.importDests() (the wizard auto-maps them 100%). // All cell values are strings exactly as a CSV would carry them: amounts "1234.50" (2 decimals, no thousands // separator), dates MM/DD/YYYY, blank "" (never "null"). // Loaded in the browser as window.OldReportsData; in node (unit tests) as module.exports. (function (root) { "use strict"; // ═══════════════════════ 1. catalog ═══════════════════════ const PAGE_TYPES = { deal_recap: { label: "Deal recap", importable: true, dest: "deals", why: "", goes: "Deals — closed-deal history" }, posting_worksheet: { label: "Deal posting worksheet", importable: false, dest: null, goes: "", why: "It shows how one deal was split into debits and credits, but it has no debit / credit columns and the same amounts are already in the journal documents and the trial balance. Importing it would count the deal twice, so K2B only uses it to double-check the deal recap." }, journal_document: { label: "Journal document", importable: true, dest: "glJournal", why: "", goes: "General Ledger — journal entries" }, fi_gl_setup: { label: "F&I to GL interface setup screen", importable: false, dest: null, goes: "", why: "This is the old system's settings screen (which general-ledger accounts F&I income was posted to). K2B has its own posting map under Settings, so the screen is kept for you to compare with, not imported." }, chart_of_accounts: { label: "Chart of accounts", importable: true, dest: "glChart", why: "", goes: "General Ledger — chart of accounts" }, aged_schedule: { label: "Aged schedule", importable: true, dest: "schedule", why: "", goes: "Aged schedule balances (sub-ledger detail)" }, account_detail: { label: "GL account detail screen", importable: false, dest: null, goes: "", why: "A screen print of one account's activity. The entries on it are already inside the account balances of the trial balance, so importing them would count them twice." }, account_history: { label: "Account history screen", importable: false, dest: null, goes: "", why: "A screen print of one account's history. The entries on it are already inside the account balances of the trial balance, so importing them would count them twice." }, gm_operating_report:{ label: "GM dealer operating report", importable: false, dest: null, goes: "", why: "This is the manufacturer's financial statement. K2B builds that report from the ledger, so there is nothing to import; the page is kept as a reference." }, vehicle_master: { label: "Vehicle master report", importable: true, dest: "inventory", why: "", goes: "Inventory — vehicles" }, ro_history: { label: "Repair-order history / RO detail", importable: true, dest: "roHistory", why: "", goes: "Service — repair orders (history from the old system)" }, // RC25 (imp) parts_inventory: { label: "Parts inventory / parts master", importable: true, dest: "partsMaster", why: "", goes: "Parts — master list and on-hand" }, // RC25 (imp) warranty_claims: { label: "Open warranty claims", importable: true, dest: "warrantyClaims", why: "", goes: "Warranty claims (open claims from the old system)" }, // RC33 (svi) trial_balance: { label: "Trial balance", importable: true, dest: "glTrial", why: "", goes: "General Ledger — trial balance (opening balances)" }, balance_sheet: { label: "Balance sheet", importable: false, dest: null, goes: "", why: "A statement built from the trial balance. It is used to double-check the trial balance (see the cross-checks) and is not imported; K2B builds its own balance sheet from the ledger." }, profit_loss: { label: "Profit and loss statement", importable: false, dest: null, goes: "", why: "A statement built from the trial balance. It is used to double-check the trial balance (see the cross-checks) and is not imported; K2B builds its own profit and loss from the ledger." }, other: { label: "Other page", importable: false, dest: null, goes: "", why: "K2B did not recognise this page as one of the old-system reports it can use, so it is not imported. Check the page and re-read it if it should be one of the others." }, blank: { label: "Blank page", importable: false, dest: null, goes: "", why: "Nothing is printed on this page." }, }; const IMPORT_ORDER = [ { dest: "glChart", label: "Chart of accounts", why: "Every other general-ledger import looks accounts up in the chart, so it goes first. An account that is missing is added here on purpose rather than guessed later." }, { dest: "glTrial", label: "Trial balance (opening balances)", why: "The trial balance sets the opening balances (one balanced entry dated at the report's as-of date). It needs the chart to exist, and the schedules and journal documents are checked against it." }, { dest: "inventory", label: "Vehicle master (inventory)", why: "Vehicle records come next so deals can find their stock numbers. No journal entry is posted; the old ledger's GL cost is kept as a reference figure only." }, { dest: "schedule", label: "Aged schedules (factory receivables and customer A/R)", why: "The schedules are the detail behind trial-balance accounts 261, 220 and 220A, so they follow the trial balance. That lets the preview show how the imported total compares with the ledger. Nothing is posted." }, { dest: "deals", label: "Deals (closed-deal history)", why: "Deals are history records only (nothing is posted). They go after inventory so each deal's stock number matches a vehicle, and before the journal documents because a document's date comes from its deal." }, { dest: "glJournal", label: "Journal documents", why: "Journal documents post to the General Ledger, so they go last: they need the chart, their dates come from the deals, and any dated on or before the trial-balance date are already counted in the opening balances." }, // RC25 (imp) { dest: "partsMaster", label: "Parts inventory (the parts list)", why: "The parts list is records only (part number, bin, on-hand, cost). Nothing is posted; the value on hand is tied to the parts inventory account on Accounting › Reconcile inventory to GL." }, { dest: "roHistory", label: "Repair-order history", why: "Repair orders are history records on the server (closed ROs) or open repair orders on the board; no journal is posted. They go after the vehicles so a VIN can be looked up, and the open ones are tied to work in process on the reconciliation screen." }, ]; const DEST_NAME = { glChart: "Chart of accounts", glTrial: "Trial balance (opening balances)", inventory: "Inventory — vehicles", schedule: "Aged schedule balances", deals: "Deals — closed-deal history", glJournal: "Journal entries", roHistory: "Service — repair orders (history from the old system)", partsMaster: "Parts — master list and on-hand" }; // RC25 (imp) // ═══════════════════════ 2. small helpers ═══════════════════════ const isNum = (x) => typeof x === "number" && isFinite(x); const cents = (x) => (isNum(x) ? Math.round(x * 100 + (x < 0 ? -1e-7 : 1e-7)) : 0); const str = (v) => (v == null || typeof v === "object" ? "" : String(v).replace(/\s+/g, " ").trim()); // an object / array where text should be is "blank", never "[object Object]" const up = (v) => str(v).toUpperCase(); const acctN = (v) => up(v).replace(/\s+/g, ""); const pad2 = (n) => (n < 10 ? "0" : "") + n; const money = (v) => { if (!isNum(v)) return ""; const r = Math.round(v * 100) / 100; return (r === 0 ? 0 : r).toFixed(2); }; const intf = (v) => (isNum(v) ? String(Math.round(v)) : ""); const numf = (v) => (isNum(v) ? String(+v.toFixed(4)) : ""); const usd = (c) => { const neg = c < 0, a = Math.abs(c); return (neg ? "-" : "") + String(Math.floor(a / 100)).replace(/\B(?=(\d{3})+(?!\d))/g, ",") + "." + pad2(a % 100); }; const numOrNull = (v) => (isNum(v) ? v : (typeof v === "string" && v.trim() !== "" && isFinite(+v.replace(/[$,]/g, "")) ? +v.replace(/[$,]/g, "") : null)); const plural = (n, one, many) => n + " " + (n === 1 ? one : (many || one + "s")); const uniq = (a) => Array.from(new Set(a)); const sum = (a) => a.reduce((s, x) => s + x, 0); function parseDate(s) { if (s == null) return null; const t = String(s).trim(); if (!t) return null; let m, y, mo, d; if ((m = /^(\d{1,2})[-\/.](\d{1,2})[-\/.](\d{2}|\d{4})$/.exec(t))) { mo = +m[1]; d = +m[2]; y = +m[3]; if (m[3].length === 2) y += y >= 70 ? 1900 : 2000; } else if ((m = /^(\d{4})[-\/.](\d{1,2})[-\/.](\d{1,2})(?:$|[T\s])/.exec(t))) { y = +m[1]; mo = +m[2]; d = +m[3]; } // RC25 (imp) fix round: 2021/02/02 too (a page printed year first) — it used to be dropped as "no date" else return null; if (mo < 1 || mo > 12 || d < 1 || d > 31 || y < 1900 || y > 2200) return null; const dt = new Date(Date.UTC(y, mo - 1, d)); if (dt.getUTCMonth() !== mo - 1) return null; return { y, m: mo, d, t: dt.getTime() }; } const mdy = (D) => (D ? pad2(D.m) + "/" + pad2(D.d) + "/" + D.y : ""); const isoOf = (D) => (D ? D.y + "-" + pad2(D.m) + "-" + pad2(D.d) : ""); const dateCell = (s) => mdy(parseDate(s)); // account order of the old print: 4-digit numbers starting with 0 first, then by number, then by letters const acctSortKey = (a) => { const m = /^(\d+)([A-Z]*)$/.exec(a); return m ? [m[1].length === 4 && m[1][0] === "0" ? 0 : 1, parseInt(m[1], 10), m[2]] : [2, 0, a]; }; const cmpAcct = (a, b) => { const x = acctSortKey(a), y = acctSortKey(b); return x[0] - y[0] || x[1] - y[1] || (x[2] < y[2] ? -1 : x[2] > y[2] ? 1 : 0); }; // trial-balance band of an account: 4-digit numbers starting with 0 are the 000-099 group, otherwise the hundreds const bandOf = (a) => { const d = (/^\d+/.exec(a) || [""])[0]; if (!d) return -1; return d.length === 4 && d[0] === "0" ? 0 : Math.floor(parseInt(d.slice(0, 3), 10) / 100) * 100; }; const isBsAcct = (a) => /^[123]/.test(a); // balance-sheet accounts (200s assets, 300s liabilities + equity); everything else is profit and loss const shortFile = (f) => { const b = String(f || "").split(/[\\/]/).pop().replace(/\.[A-Za-z0-9]{2,4}$/, ""); return b.length > 34 ? b.slice(0, 31) + "…" : b; }; // ═══════════════════════ 3. checks, rows, datasets ═══════════════════════ const LV = { ok: 0, warn: 1, error: 2 }; const worst = (a, b) => (LV[b] > LV[a] ? b : a); function mkCheck(level, id, title, detail, extra) { const c = { id, level, title, detail: detail || "" }; if (extra) for (const k in extra) if (extra[k] !== undefined) c[k] = extra[k]; if (!c.rows) c.rows = []; return c; } // a growing list of checks plus per-row marks function Collector(n) { this.checks = []; this.rowInfo = Array.from({ length: n }, () => ({ level: "ok", notes: [] })); } Collector.prototype.add = function (c) { this.checks.push(c); if (c.level !== "ok") (c.rows || []).forEach((i) => { const ri = this.rowInfo[i]; if (!ri) return; ri.level = worst(ri.level, c.level); ri.notes.push(c.title); }); return c; }; Collector.prototype.mark = function (i, level, text) { const ri = this.rowInfo[i]; if (!ri) return; ri.level = worst(ri.level, level); ri.notes.push(text); }; // ═══════════════════════ 4. reading several copies of the same thing (page repeats, three sort orders) ═══════════════════════ // A "read" is one page; its records are keyed. The same key on several reads is voted per field: // one distinct value = fine; a strict majority wins (and is listed); anything else needs the user's choice. const vkey = (v) => { if (v == null || v === "") return "e"; if (typeof v === "number") return isFinite(v) ? "n" + Math.round(v * 10000) : "e"; return "s" + up(v); }; function mergeReads(reads, cfg) { const fields = cfg.fields, soft = cfg.soft || {}, dec = cfg.decisions || {}; const byKey = new Map(), order = []; reads.forEach((rd) => rd.recs.forEach((rc) => { let e = byKey.get(rc.key); if (!e) { e = { key: rc.key, entries: [] }; byKey.set(rc.key, e); order.push(e); } e.entries.push({ rd, vals: rc.vals }); })); return order.map((e) => { const out = { key: e.key, vals: {}, dis: [], reads: e.entries.map((x) => x.rd), nReads: e.entries.length, first: e.entries[0] }; fields.forEach((f) => { const groups = new Map(); e.entries.forEach((en) => { if (!(f in en.vals)) return; const v = en.vals[f], k = vkey(v); let g = groups.get(k); if (!g) { g = { vkey: k, value: v, refs: [], n: 0, rank: en.rd.rank }; groups.set(k, g); } g.refs.push(en.rd.ref); g.n++; if (en.rd.rank < g.rank) g.rank = en.rd.rank; }); const gs = Array.from(groups.values()); if (!gs.length) { out.vals[f] = null; return; } if (gs.length === 1) { out.vals[f] = gs[0].value; return; } gs.sort((a, b) => b.n - a.n || a.rank - b.rank); const picked = dec[e.key + "|" + f]; let win = null, by = null; if (picked !== undefined) { win = gs.find((g) => g.vkey === picked) || null; if (win) by = "user"; } if (!win && gs[0].n > gs[1].n) { win = gs[0]; by = "majority"; } if (!win) win = gs.slice().sort((a, b) => a.rank - b.rank)[0]; // provisional only: nothing is resolved until by !== null out.vals[f] = win.value; out.dis.push({ field: f, options: gs, winner: win.vkey, by, soft: !!soft[f] }); }); return out; }); } // ═══════════════════════ 5. destination tables (labels, formats) ═══════════════════════ const T_ = (s) => s.split(" "); const FMT = (() => { const o = { glTrial: { acct: "t", name: "t", mtd: "$", ytd: "$", mtdCount: "i", ytdCount: "i", rowKind: "t" }, glChart: { acct: "t", name: "t", type: "t", alt: "t", dc: "t", costAcct: "t", invAcct: "t" }, inventory: { stock: "t", vin: "t", asking: "$", miles: "i", newUsed: "t", yr: "t", mk: "t", mdl: "t", color: "t", invAcct: "t", invoiceDate: "d", invoiceCost: "$", repairs: "$", glCost: "$", age: "i", pack: "$" }, schedule: { control: "t", name: "t", days: "i", balance: "$", glAcct: "t", asOf: "d" }, glJournal: { ref: "t", date: "d", acct: "t", amount: "$", desc: "t", entryDesc: "t", jrn: "i", control: "t", controlName: "t" }, // RC25 (imp) roHistory: { roNo: "t", flag: "t", date: "d", customerAccount: "t", customerName: "t", custStreet: "t", custCity: "t", custState: "t", custPostal: "t", custPhone: "t", vin: "t", stock: "t", vehicle: "t", odometer: "i", tech: "t", advisor: "t", partsSale: "$", partsCost: "$", laborSale: "$", laborCost: "$", subletSale: "$", subletCost: "$", supplies: "$", salesTax: "$", total: "$", customerPay: "$", otherPay: "$", status: "t", lineText: "t" }, // RC33 (svi): + the customer's name / address and the open-RO costs // RC33 (svi): an A/R aging in age columns, open warranty claims arAging: { control: "t", name: "t", current: "$", d31_60: "$", d61_90: "$", d91_120: "$", over120: "$", total: "$", gl: "t", asOf: "d" }, warrantyClaims: { claim: "t", ro: "t", date: "d", amount: "$", paid: "$", status: "t" }, partsMaster: { part: "t", desc: "t", bin: "t", onHand: "i", cost: "$", list: "$", source: "t", core: "$" }, deals: {}, }; const d = o.deals; T_("dealNo invNo lender fiMgr salesperson buyer cobuyer street city state postal stock vin vehicle type tradeStock tradeDesc tradeVin add1Label add2Label add3Label add4Label comm1Name comm2Name comm3Name payoffLender").forEach((k) => { d[k] = "t"; }); d.date = "d"; d.phone = "t"; T_("miles tradeMiles term").forEach((k) => { d[k] = "i"; }); T_("invoicePrice holdback pack add1Amt add2Amt add3Amt add4Amt sfd glRepairs totalRepairs totalCost salePrice cashReceived cashStated tradeAcv tradeAllow trade2Acv trade2Allow grossProfit reserve clPrem clCost ahPrem ahCost vscSale vscCost gapSale gapCost miscFi fiProfit holdbackProfit miscDealer totalDealerProfit comm1Flat comm1Amt comm2Flat comm2Amt comm3Flat comm3Amt netNet amtFinanced financeCharge payment payoff1").forEach((k) => { d[k] = "$"; }); T_("contractRate buyRate comm1Pct comm2Pct comm3Pct").forEach((k) => { d[k] = "n"; }); return o; })(); const cell = (f, v) => { if (v == null) return ""; if (f === "$") return money(typeof v === "number" ? v : numOrNull(v)); if (f === "i") return typeof v === "number" ? intf(v) : str(v); if (f === "n") return typeof v === "number" ? numf(v) : str(v); return str(v); }; const emptyCell = (v) => v == null || v === "" || (typeof v === "number" && !isFinite(v)); function normalizeDests(src) { const out = {}; if (!src) return out; if (Array.isArray(src)) { // DMS.importDests() list src.forEach((d) => { const f = {}; (d.fields || []).forEach((x) => { f[x.key] = x.label; }); out[d.key] = { label: d.label, fields: f }; }); return out; } Object.keys(src).forEach((k) => { const v = src[k]; if (!v || typeof v !== "object") return; if (v.fields && typeof v.fields === "object" && !Array.isArray(v.fields)) out[k] = { label: v.label, fields: v.fields }; else out[k] = { label: undefined, fields: v }; }); return out; } function browserDests() { try { const D = root.DMS; if (D && typeof D.importDests === "function") return normalizeDests(D.importDests()); } catch (e) { /* none */ } return {}; } // ═══════════════════════ 6. page normalisation ═══════════════════════ const cmpPage = (a, b) => (a.fileName < b.fileName ? -1 : a.fileName > b.fileName ? 1 : 0) || (+a.pageNo || 0) - (+b.pageNo || 0) || a.idx - b.idx; function normPage(r, idx) { r = r || {}; const fileName = str(r.fileName), pageNo = r.pageNo; const ok = r.ok !== false; const known = PAGE_TYPES[r.pageType] ? r.pageType : null; const data = r.data && typeof r.data === "object" ? r.data : null; return { idx, fileName, pageNo, ref: { fileName, pageNo }, label: (fileName ? shortFile(fileName) + " " : "") + "page " + pageNo, ok, pageType: ok ? (known || "other") : "failed", rawType: r.pageType, orientation: r.orientation || "upright", legibility: r.legibility || "good", notes: Array.isArray(r.notes) ? r.notes.map(str).filter(Boolean) : [], data, error: r.error ? str(r.error) : "", code: r.code ? str(r.code) : "", }; } const refKey = (ref) => ref.fileName + "#" + ref.pageNo; const refLabel = (ref) => (ref.fileName ? shortFile(ref.fileName) + " " : "") + "page " + ref.pageNo; const refsLabel = (refs) => uniq((refs || []).map(refLabel)).join(", "); const ILLEGIBLE_RE = /illegib|unreadable|not legible|cannot be read|can.t be read|could not (be )?read|couldn.t read|too blurred|hard to read|\bfad(ed|ing)\b|smudg|smear|uncertain|ambiguous|best reading|not guessed|looks like the letter|unclear|dropout/i; // a note that says cells were hard to read — not one that says "faded but readable", "every amount was readable", or that only mentions a header stamp const POSITIVE_RE = /\b(still|but|and|were|was|all|every\b[^.]{0,30}) ?(readable|legible)\b/i, STRONG_RE = /illegib|unreadable|not legible|not readable|cannot be read|can.t be read|could not (be )?read|couldn.t read|too blurred|best reading|uncertain|ambiguous|not guessed|looks like the letter|dropout|set to null/i, STAMP_RE = /\b(print date|version stamp|time at the top|time stamp|header)\b/i; const CELLISH_RE = /\b(cells?|digits?|amounts?|columns?|accounts?|rows?|figures?|numbers?)\b/i; function hardNote(n) { if (!ILLEGIBLE_RE.test(n) || /\bnot (unreadable|illegible|guessed at)\b/i.test(n)) return false; if (STAMP_RE.test(n) && !CELLISH_RE.test(n)) return false; // only the print date / version stamp is affected if (STRONG_RE.test(n)) return true; return !POSITIVE_RE.test(n); } const strongNote = (n) => hardNote(n) && STRONG_RE.test(n); function legibilityCheck(pages) { const hits = []; const snippet = (t) => { const m = ILLEGIBLE_RE.exec(t); if (!m || t.length <= 200) return t; const a = Math.max(0, m.index - 90), b = Math.min(t.length, m.index + m[0].length + 90); return (a > 0 ? "…" : "") + t.slice(a, b) + (b < t.length ? "…" : ""); }; pages.forEach((p) => { const note = p.notes.find(strongNote) || p.notes.find(hardNote); if (p.legibility === "poor" || note) hits.push({ p, poor: p.legibility === "poor", note }); }); if (!hits.length) return null; const detail = hits.map((h) => h.p.label + (h.poor ? " (poor legibility)" : "") + (h.note ? ": " + snippet(h.note) : "")).join("\n"); return mkCheck("warn", "pages.legibility", plural(hits.length, "page") + " had cells that were hard to read", "Check the numbers on these pages against the printout. Nothing is guessed: a cell that could not be read is left blank.\n" + detail, { pages: hits.map((h) => h.p.ref) }); } // ═══════════════════════ 7. the packet context ═══════════════════════ function makeCtx(results, opts, cfg) { opts = opts || {}; // the same file + page number supplied twice with identical content (a retry, a double click) is ONE page: dropped silently. // Different content under the same page number is a second reading of that page and is kept (it votes); a copy under another page number is a "repeat". const seenPage = new Set(); const pages = (Array.isArray(results) ? results : []).map(normPage).filter((p) => { let sig; try { sig = p.fileName + "#" + p.pageNo + "|" + p.pageType + "|" + (p.ok ? JSON.stringify(p.data) + "|" + p.legibility + "|" + p.notes.join("\u0001") : p.code + "|" + p.error); } catch (e) { return true; } if (seenPage.has(sig)) return false; seenPage.add(sig); return true; }); const given = normalizeDests(opts.destFields); const dests = Object.keys(given).length ? given : (cfg.dests && Object.keys(cfg.dests).length ? cfg.dests : browserDests()); const ctx = { opts, cfg, pages, dests, byType: {}, decisions: opts.decisions || {}, cache: {} }; pages.forEach((p) => { if (p.ok && p.data) (ctx.byType[p.pageType] = ctx.byType[p.pageType] || []).push(p); }); Object.keys(ctx.byType).forEach((k) => ctx.byType[k].sort(cmpPage)); ctx.of = (t) => ctx.byType[t] || []; ctx.destLabel = (dest) => (dests[dest] && dests[dest].label) || DEST_NAME[dest] || dest; ctx.labels = (dest) => (dests[dest] && dests[dest].fields) || {}; // chart accounts of K2B itself (to validate account numbers): opts.chartAccounts, else the shared chart, else the built-in library ctx.k2bChart = (() => { let list = opts.chartAccounts; if (!list) { try { const D = root.DMS; const live = D && D.glAccounts ? D.glAccounts() : null; list = live && live.length ? live : (root.GL_ACCOUNTS || null); } catch (e) { list = null; } } const m = new Map(); (list || []).forEach((a) => { const k = acctN(typeof a === "string" ? a : (a.acct || a.account || a.no)); if (k) m.set(k, a); }); return m; })(); ctx.table = (dest, recs, core) => { const fm = ctx.labels(dest), fmt = FMT[dest] || {}; const keys = Object.keys(fm).filter((k) => fmt[k]); Object.keys(fmt).forEach((k) => { if (!keys.includes(k)) keys.push(k); }); const used = keys.filter((k) => (core || []).includes(k) || recs.some((r) => !emptyCell(r[k]))); const headers = used.map((k) => fm[k] || k); const keyOf = {}; used.forEach((k) => { keyOf[fm[k] || k] = k; }); const rows = recs.map((r) => { const o = {}; used.forEach((k) => { o[fm[k] || k] = cell(fmt[k], r[k]); }); return o; }); return { headers, rows, keyOf, unknownFields: Object.keys(fm).length ? used.filter((k) => !fm[k]) : [] }; }; return ctx; } // ═══════════════════════ 8. finishing a dataset ═══════════════════════ const enc = encodeURIComponent; function choicesOf(merged, rowOf, fieldLabel, fmtField, hintFn) { const out = []; merged.forEach((m) => m.dis.forEach((d) => { const rowKey = m.key; const ch = { id: rowKey + "|" + d.field, rowKey, row: rowOf(m), field: d.field, label: fieldLabel(d.field), soft: d.soft, resolved: d.by !== null, by: d.by, chosen: d.winner, options: [] }; ch.options = d.options.map((o) => ({ vkey: o.vkey, value: o.value, display: fmtField(d.field, o.value) || "(blank)", votes: o.n, pages: o.refs, picked: o.vkey === d.winner, fixId: "choose|" + enc(rowKey) + "|" + d.field + "|" + enc(o.vkey), hint: "", suggested: false, })); if (hintFn) hintFn(m, d, ch); out.push(ch); })); return out; } // checks that report the disagreements between copies; `named(choice)` gives the row's display name function readChecks(col, choices, named, what) { const unres = choices.filter((c) => !c.resolved), maj = choices.filter((c) => c.resolved && c.by === "majority"), usr = choices.filter((c) => c.by === "user"); const line = (c) => named(c) + " " + c.label + ": " + c.options.map((o) => o.display + " (" + refsLabel(o.pages) + (o.votes > 1 ? ", " + o.votes + " reads" : "") + ")").join(" vs "); const hard = unres.filter((c) => !c.soft), soft = unres.filter((c) => c.soft); if (hard.length) col.add(mkCheck("error", "reads.choose", plural(hard.length, "value") + " read differently on " + what + " — you must choose", "Two readings disagree and neither has a majority, so K2B will not pick one for you. Choose the right value for each.\n" + hard.slice(0, 12).map(line).join("\n") + (hard.length > 12 ? "\n… and " + (hard.length - 12) + " more" : ""), { rows: uniq(hard.map((c) => c.row)), keys: uniq(hard.map((c) => c.rowKey)), choices: hard.map((c) => c.id) })); if (soft.length) col.add(mkCheck("warn", "reads.chooseText", plural(soft.length, "wording difference") + " between readings of " + what, "The readings disagree on names or descriptions (no numbers involved). The first reading is used until you choose.\n" + soft.slice(0, 12).map(line).join("\n"), { rows: uniq(soft.map((c) => c.row)), keys: uniq(soft.map((c) => c.rowKey)), choices: soft.map((c) => c.id) })); if (maj.length) col.add(mkCheck("warn", "reads.majority", plural(maj.length, "value") + " decided by majority of readings", "These were read differently on different copies; the value most copies agree on is used. You can override any of them.\n" + maj.slice(0, 12).map(line).join("\n") + (maj.length > 12 ? "\n… and " + (maj.length - 12) + " more" : ""), { rows: uniq(maj.map((c) => c.row)), keys: uniq(maj.map((c) => c.rowKey)), choices: maj.map((c) => c.id) })); if (usr.length) col.add(mkCheck("ok", "reads.user", plural(usr.length, "value") + " chosen by you", usr.slice(0, 12).map(line).join("\n"), { rows: uniq(usr.map((c) => c.row)), keys: uniq(usr.map((c) => c.rowKey)) })); } // assemble the dataset object; `rebuild(decisions)` makes a fresh one with a new decision list function finish(ctx, spec, col, rebuild) { const checks = col.checks; let status = "ok"; checks.forEach((c) => { status = worst(status, c.level); }); const counts = { ok: 0, warn: 0, error: 0 }; checks.forEach((c) => { counts[c.level]++; }); const choices = spec.choices || []; const ds = { id: spec.id, pageType: spec.pageType, dest: spec.dest, destLabel: ctx.destLabel(spec.dest), sheetName: spec.sheetName, headers: spec.headers, rows: spec.rows, rowCount: spec.rows.length, records: spec.records, rowKeys: spec.rowKeys, rowInfo: col.rowInfo, pages: spec.pages, checks, printed: spec.printed || {}, importOpts: spec.importOpts || {}, status, counts, notes: spec.notes || [], choices, needsChoice: choices.filter((c) => !c.resolved && !c.soft).length, importable: spec.importable !== false, whyNot: spec.whyNot || "", fieldKeys: spec.keyOf, decisions: spec.decisions || [], suggestions: spec.suggestions || null, fixes: [], }; (spec.fixes || []).forEach((f) => ds.fixes.push({ id: f.id, label: f.label, kind: f.kind, rows: f.rows || [], apply: () => applyFix(ds, f.id) })); Object.defineProperty(ds, "_rebuild", { value: rebuild, enumerable: false }); return ds; } // fixes that choose one reading function choiceFixes(choices, named) { const out = []; choices.filter((c) => !c.resolved).forEach((c) => c.options.forEach((o) => out.push({ id: o.fixId, kind: "choose", rows: [c.row], label: "Use " + o.display + " for " + named(c) + " " + c.label + " (read on " + refsLabel(o.pages) + ")" + (o.suggested ? " — the one that fits the other figures" : "") }))); // a value the majority of the readings decided (or that the user chose) can still be overridden choices.filter((c) => c.resolved && (c.by === "majority" || c.by === "user")).forEach((c) => { const cur = c.options.find((o) => o.picked); c.options.filter((o) => !o.picked).forEach((o) => out.push({ id: o.fixId, kind: "choose", rows: [c.row], label: "Use " + o.display + " instead of " + (cur ? cur.display : "the current reading") + " for " + named(c) + " " + c.label + " (read on " + refsLabel(o.pages) + ")" + (c.by === "majority" ? " — overrides the majority" : " — change your choice") })); }); return out; } // decisions list -> { merge: {"key|field": vkey}, accts: {from: to}, dates: {doc: mdy} } function indexDecisions(list) { const d = { merge: {}, accts: {}, dates: {} }; (list || []).forEach((x) => { if (x.t === "choose") d.merge[x.key + "|" + x.field] = x.pick; else if (x.t === "acct") d.accts[x.from] = x.to; else if (x.t === "date") d.dates[x.doc] = x.date; }); return d; } // ═══════════════════════ 9. dates the packet agrees on (as-of date of the trial balance) ═══════════════════════ function packetDates(ctx) { if (ctx.cache.dates) return ctx.cache.dates; const bs = [], pl = [], tb = []; ctx.of("balance_sheet").forEach((p) => { const d = parseDate(p.data.asOf); if (d) bs.push({ d, p }); }); ctx.of("profit_loss").forEach((p) => { if (str(p.data.comparisonColumn)) return; const d = parseDate(p.data.thru); if (d) pl.push({ d, p }); }); ctx.of("trial_balance").forEach((p) => { const d = parseDate(p.data.printedDate); if (d) tb.push({ d, p }); }); let pick = null, source = "", conflict = null; const given = ctx.opts.asOf ? parseDate(ctx.opts.asOf instanceof Date ? isoOf({ y: ctx.opts.asOf.getFullYear(), m: ctx.opts.asOf.getMonth() + 1, d: ctx.opts.asOf.getDate() }) : ctx.opts.asOf) : null; if (given) { pick = given; source = "you"; } else if (bs.length) { pick = bs[0].d; source = "the balance sheet"; } else if (pl.length) { pick = pl[0].d; source = "the profit and loss statement"; } else if (tb.length) { pick = tb[0].d; source = "the print date"; } const all = bs.concat(pl).map((x) => x.d.t); if (all.length && uniq(all).length > 1) conflict = "The balance sheet and profit and loss pages show different dates (" + uniq(bs.concat(pl).map((x) => mdy(x.d))).join(", ") + ")."; const printed = tb.length ? tb[0].d : null; ctx.cache.dates = { asOf: pick, source, conflict, printed }; return ctx.cache.dates; } // PROFIT (LOSS) on the balance sheet (the one number that must tie exactly to the trial balance) function bsProfit(ctx) { if (ctx.cache.bsProfit !== undefined) return ctx.cache.bsProfit; let found = null; ctx.of("balance_sheet").forEach((p) => (Array.isArray(p.data.lines) ? p.data.lines : []).forEach((l) => { if (found) return; if ((acctN(l.acct) === "399" || /^PROFIT\s*\(?LOSS\)?/i.test(str(l.description))) && isNum(l.ytd)) found = { value: l.ytd, page: p }; })); ctx.cache.bsProfit = found; return found; } // ═══════════════════════ 10. trial balance ═══════════════════════ function tbKind(r) { const k = str(r.rowKind).toLowerCase().replace(/[\s\-]+/g, "_"); if (k === "account" || k === "account_total" || k === "group_total" || k === "grand_total") return k; const a = acctN(r.acct), d = up(r.description); if (!a && /^GROUP\b.*\bTOTAL/.test(d)) return "group_total"; if (!a && /^(GRAND |REPORT |FINAL )?TOTAL\b/.test(d)) return "grand_total"; if (/\b(ACCOUNT|ACCT)\s+TOTAL\b/.test(d)) return "account_total"; return "account"; } const pnOf = (p) => (isNum(p.data.pageNo) ? p.data.pageNo : 1e9); // culprit hints for a sum that is off by `diff` cents: an account that equals the gap, or half of it (a sign flip) function culprits(accts, diff) { const out = []; accts.forEach((a) => { const v = cents(a.m.vals.ytd); if (v !== 0 && (v === diff || v === -diff || 2 * v === diff || 2 * v === -diff)) out.push(a.i); }); return out.slice(0, 5); } function tbMerge(ctx, dix) { const pages = ctx.of("trial_balance").filter((p) => Array.isArray(p.data.rows)); const sorted = pages.slice().sort((a, b) => pnOf(a) - pnOf(b) || cmpPage(a, b)); const reads = sorted.map((p, i) => { const seen = {}, recs = []; p.data.rows.forEach((r, ri) => { r = r || {}; const kind = tbKind(r), a = acctN(r.acct); let base = kind === "account" ? (a ? "A|" + a : "A|?" + p.idx + "." + ri) : kind === "account_total" ? "T|" + (a || up(r.description)) : kind === "group_total" ? "G|" + up(r.description) : "Z|TOTAL"; seen[base] = (seen[base] || 0) + 1; const key = seen[base] > 1 ? base + "#" + seen[base] : base; recs.push({ key, vals: { kind, acct: a, name: str(r.description), mtd: numOrNull(r.mtd), ytd: numOrNull(r.ytd), mtdCount: numOrNull(r.mtdCount), ytdCount: numOrNull(r.ytdCount) } }); }); return { id: refKey(p.ref), ref: p.ref, rank: i, recs, page: p, keys: recs.map((x) => x.key) }; }); // repeated pages: most of a page's rows were already read from an earlier page const union = new Map(); const repeats = []; reads.forEach((rd) => { let best = null, bestN = 0; const counts = new Map(); rd.keys.forEach((k) => { const f = union.get(k); if (f) counts.set(f, (counts.get(f) || 0) + 1); }); counts.forEach((n, f) => { if (n > bestN) { bestN = n; best = f; } }); if (best && bestN >= Math.max(1, Math.ceil(rd.keys.length * 0.5))) { rd.repeatOf = best; repeats.push(rd); } rd.keys.forEach((k) => { if (!union.has(k)) union.set(k, rd); }); }); const merged = mergeReads(reads, { fields: ["name", "mtd", "ytd", "mtdCount", "ytdCount"], soft: { name: true }, decisions: dix.merge }); merged.forEach((m) => { m.kind = m.first.vals.kind; m.acct = m.first.vals.acct; }); return { reads, repeats, merged, sorted }; } function buildTrial(ctx, decList) { const dix = indexDecisions(decList); const core = tbMerge(ctx, dix); const { merged, reads, repeats, sorted } = core; const n = merged.length; const col = new Collector(n); const named = (c) => (c.rowKey.replace(/^[ATGZ]\|/, "").replace(/#\d+$/, "") || "row"); const fmtField = (f, v) => (f === "mtd" || f === "ytd" ? money(v) : f === "name" ? str(v) : intf(v)); const labels = { name: "description", mtd: "month-to-date amount", ytd: "year-to-date amount", mtdCount: "month-to-date count", ytdCount: "year-to-date count" }; const rowIdx = new Map(); merged.forEach((m, i) => rowIdx.set(m, i)); const choices = choicesOf(merged, (m) => rowIdx.get(m), (f) => labels[f] || f, fmtField, null); // records + sheet const recs = merged.map((m) => ({ acct: m.kind === "group_total" || m.kind === "grand_total" ? "" : m.acct, name: m.vals.name, mtd: m.vals.mtd, ytd: m.vals.ytd, mtdCount: m.vals.mtdCount, ytdCount: m.vals.ytdCount, rowKind: m.kind, })); const tab = ctx.table("glTrial", recs, ["acct", "name", "mtd", "ytd", "rowKind"]); const accts = []; merged.forEach((m, i) => { if (m.kind === "account") accts.push({ m, i }); }); const byPrefix = new Map(); accts.forEach((a) => { const p = a.m.acct.slice(0, 3); (byPrefix.get(p) || byPrefix.set(p, []).get(p)).push(a); }); // — pages const dupPages = repeats.map((rd) => rd.page); if (repeats.length) { col.add(mkCheck("ok", "tb.repeats", plural(repeats.length, "page") + " supplied more than once — counted once", repeats.map((rd) => rd.page.label + " repeats " + rd.repeatOf.page.label).join("\n") + "\nThe repeated rows are not added twice. Any cell the two readings disagree on is listed separately.", { pages: repeats.map((rd) => rd.page.ref) })); } const uniqPages = sorted.filter((p) => !dupPages.includes(p)); const nums = uniq(uniqPages.map((p) => p.data.pageNo).filter(isNum)); if (nums.length) { const mx = Math.max.apply(null, nums), missing = []; for (let i = 1; i <= mx; i++) if (!nums.includes(i)) missing.push(i); if (missing.length) col.add(mkCheck("error", "tb.missingPages", "Trial balance " + (missing.length === 1 ? "page " : "pages ") + missing.join(", ") + " " + (missing.length === 1 ? "is" : "are") + " missing", "The pages you supplied are numbered up to " + mx + ", but " + missing.join(", ") + (missing.length === 1 ? " is" : " are") + " not here, so accounts are missing and the totals cannot tie. Add the missing page(s).", { keys: missing.map(String) })); } // — required cells const noAcct = [], noYtd = [], noMtd = []; merged.forEach((m, i) => { if (m.kind !== "account") return; if (!m.acct || m.key.indexOf("A|?") === 0) noAcct.push(i); if (m.vals.ytd == null) noYtd.push(i); if (m.vals.mtd == null) noMtd.push(i); }); if (noAcct.length) col.add(mkCheck("error", "tb.requiredAcct", plural(noAcct.length, "row") + " without a readable account number", "An account row needs its account number. Re-read the page or fix the number.", { rows: noAcct })); if (noYtd.length) col.add(mkCheck("error", "tb.requiredYtd", plural(noYtd.length, "account") + " without a readable year-to-date amount", "The year-to-date amount is what is posted, so it cannot be blank. Account(s): " + noYtd.slice(0, 12).map((i) => merged[i].acct).join(", "), { rows: noYtd, keys: noYtd.map((i) => merged[i].acct) })); if (noMtd.length) col.add(mkCheck("warn", "tb.mtdBlank", plural(noMtd.length, "account") + " without a readable month-to-date amount", "The month-to-date column is kept for reference only, but it cannot be checked for these accounts: " + noMtd.slice(0, 12).map((i) => merged[i].acct).join(", "), { rows: noMtd, keys: noMtd.map((i) => merged[i].acct) })); const odd = accts.filter((a) => a.m.acct && !/^\d{3,4}[A-Z]{0,2}$/.test(a.m.acct)); if (odd.length) col.add(mkCheck("warn", "tb.oddAccounts", plural(odd.length, "account number") + " with an unusual shape", "Account numbers are normally 3 or 4 digits with an optional letter. Check: " + odd.slice(0, 12).map((a) => a.m.acct).join(", "), { rows: odd.map((a) => a.i), keys: odd.map((a) => a.m.acct) })); // — Account Total rows (tolerance: one cent — the old print itself is sometimes a cent off) const ats = []; merged.forEach((m, i) => { if (m.kind === "account_total") ats.push({ m, i }); }); const covered = new Set(); let exactN = 0; const cent = [], bad = [], noKids = [], cntBad = []; ats.forEach(({ m, i }) => { const kids = byPrefix.get(m.acct) || []; if (!kids.length) { noKids.push(i); return; } if (m.vals.ytd == null || m.vals.mtd == null) { bad.push({ i, why: "the printed total could not be read", rows: [i].concat(kids.map((k) => k.i)) }); return; } const dm = cents(m.vals.mtd) - sum(kids.map((k) => cents(k.m.vals.mtd))), dy = cents(m.vals.ytd) - sum(kids.map((k) => cents(k.m.vals.ytd))); const worstD = Math.max(Math.abs(dm), Math.abs(dy)); let likely = []; if (worstD > 1) { const dd = dy || dm, f = dy ? "ytd" : "mtd"; likely = digitSlip(kids.map((k) => cents(k.m.vals[f])), -dd).map((x) => kids[x].i); if (likely.length > 3) likely = []; } if (worstD === 0) { exactN++; kids.forEach((k) => covered.add(k.i)); } else if (worstD <= 1) { cent.push({ i, m, dm, dy, kids }); kids.forEach((k) => covered.add(k.i)); } else bad.push({ i, why: "Account Total " + m.acct + ": printed " + usd(cents(m.vals.ytd)) + " year to date" + (dm ? ", " + usd(cents(m.vals.mtd)) + " month to date" : "") + " but its accounts add up to " + usd(cents(m.vals.ytd) - dy) + (dm ? " / " + usd(cents(m.vals.mtd) - dm) : "") + " (off by " + usd(Math.abs(dy || dm)) + ")" + (likely.length ? ". Likely: " + likely.map((x) => merged[x].acct + " (" + usd(cents(merged[x].vals.ytd)) + ")").join(", ") : ""), rows: [i].concat(likely.length ? likely : kids.map((k) => k.i)) }); const pc = m.vals.ytdCount, sc = sum(kids.map((k) => k.m.vals.ytdCount || 0)); if ((pc != null || kids.some((k) => k.m.vals.ytdCount != null)) && (pc || 0) !== sc) cntBad.push({ i, text: m.acct + ": printed count " + (pc == null ? "blank" : pc) + " but the accounts add up to " + sc }); }); if (bad.length) col.add(mkCheck("error", "tb.accountTotals", plural(bad.length, "Account Total row") + " does not equal its accounts", bad.slice(0, 10).map((b) => b.why).join("\n") + "\nCheck the account lines and the total against the printout.", { rows: uniq([].concat.apply([], bad.map((b) => b.rows))), keys: bad.map((b) => merged[b.i].acct) })); else col.add(mkCheck("ok", "tb.accountTotals", ats.length ? "All " + ats.length + " Account Total rows equal the sum of their accounts" + (cent.length ? " (" + cent.length + " within one cent)" : "") : "No Account Total rows on these pages", cent.length ? cent.map((c) => "Account " + c.m.acct + ": the old print is off by one cent — printed " + usd(cents(c.m.vals.ytd)) + " year to date, its accounts add up to " + usd(cents(c.m.vals.ytd) - c.dy) + ". Accepted (tolerance $0.01); the group and grand totals still tie exactly.").join("\n") : "", { rows: cent.map((c) => c.i), keys: cent.map((c) => c.m.acct), oneCent: cent.map((c) => c.m.acct) })); if (noKids.length) col.add(mkCheck("warn", "tb.accountTotalsNoLines", plural(noKids.length, "Account Total row") + " with none of its accounts on these pages", "Account(s) " + noKids.map((i) => merged[i].acct).join(", ") + " have a printed total but their account lines were not found — a page may be missing.", { rows: noKids })); if (cntBad.length) col.add(mkCheck("warn", "tb.counts", plural(cntBad.length, "count total") + " does not equal its accounts' counts", "Counts are for reference only. " + cntBad.slice(0, 8).map((c) => c.text).join("; "), { rows: cntBad.map((c) => c.i) })); // — Group totals (exact) const gts = []; merged.forEach((m, i) => { if (m.kind === "group_total") gts.push({ m, i }); }); const gBad = [], gOk = []; gts.forEach(({ m, i }) => { const r = /(\w{3})\s*-\s*(\w{3})/.exec(m.vals.name); if (!r) { gBad.push({ i, why: "\"" + m.vals.name + "\" — could not tell which accounts this group covers", rows: [i] }); return; } const lo = r[1].toUpperCase(), hi = r[2].toUpperCase(); const mem = accts.filter((a) => { const p = a.m.acct.slice(0, 3); return p >= lo && p <= hi; }); if (m.vals.ytd == null || m.vals.mtd == null) { gBad.push({ i, why: m.vals.name + ": the printed total could not be read", rows: [i] }); return; } const dm = cents(m.vals.mtd) - sum(mem.map((a) => cents(a.m.vals.mtd))), dy = cents(m.vals.ytd) - sum(mem.map((a) => cents(a.m.vals.ytd))); if (dm === 0 && dy === 0) { gOk.push(i); return; } const cand = mem.filter((a) => !covered.has(a.i)).map((a) => a.i); gBad.push({ i, why: m.vals.name + ": printed " + usd(cents(m.vals.ytd)) + " year to date" + (dm ? " / " + usd(cents(m.vals.mtd)) + " month to date" : "") + " but the " + mem.length + " accounts in it add up to " + usd(cents(m.vals.ytd) - dy) + (dm ? " / " + usd(cents(m.vals.mtd) - dm) : "") + " (off by " + usd(Math.abs(dy || dm)) + ")" + (cand.length ? ". Re-check " + (cand.length > 8 ? "the accounts not covered by an Account Total: " : "") + cand.slice(0, 8).map((x) => merged[x].acct).join(", ") : ". Every account is covered by an Account Total that ties, so a whole account line may be missing"), rows: [i].concat(cand.length ? cand : mem.map((a) => a.i)) }); }); if (gBad.length) col.add(mkCheck("error", "tb.groupTotals", plural(gBad.length, "Group total") + " does not equal its accounts", gBad.map((b) => b.why).join("\n"), { rows: uniq([].concat.apply([], gBad.map((b) => b.rows))), keys: gBad.map((b) => merged[b.i].vals.name) })); else if (gts.length) col.add(mkCheck("ok", "tb.groupTotals", "All " + gts.length + " Group totals equal the sum of their accounts (year to date and month to date)", "", { rows: gOk })); else col.add(mkCheck("warn", "tb.groupTotals", "No Group total rows on these pages", "Without them the accounts can only be checked against the grand total.")); // — grand total and the sum of every account const gr = []; merged.forEach((m, i) => { if (m.kind === "grand_total") gr.push({ m, i }); }); const sY = sum(accts.map((a) => cents(a.m.vals.ytd))), sM = sum(accts.map((a) => cents(a.m.vals.mtd))); if (!accts.length) col.add(mkCheck("error", "tb.balance", "No account rows were found", "The trial balance pages had no account lines.")); else if (sY !== 0 || sM !== 0) { const cu = sY !== 0 ? culprits(accts, sY) : []; col.add(mkCheck("error", "tb.balance", "The accounts do not add up to 0.00", "Year to date the accounts add up to " + usd(sY) + (sM ? " and month to date to " + usd(sM) : "") + " — debits and credits must be equal, so a number was misread or an account is missing." + (cu.length ? " An account with exactly this amount (or half of it — a flipped sign): " + cu.map((i) => merged[i].acct).join(", ") + "." : " An account worth " + usd(-sY) + " may be missing, or one amount is wrong by that much."), { rows: cu, keys: cu.map((i) => merged[i].acct), difference: sY })); } else col.add(mkCheck("ok", "tb.balance", "All " + accts.length + " accounts add up to 0.00 / 0.00 (debits = credits)", "Year to date and month to date both balance.")); if (gr.length) { const g = gr[0].m, bad0 = (g.vals.ytd != null && cents(g.vals.ytd) !== 0) || (g.vals.mtd != null && cents(g.vals.mtd) !== 0); if (g.vals.ytd == null || g.vals.mtd == null) col.add(mkCheck("warn", "tb.grandTotal", "The printed grand total could not be read", "", { rows: [gr[0].i] })); else if (bad0) col.add(mkCheck("error", "tb.grandTotal", "The printed grand total is not 0.00", "Printed " + usd(cents(g.vals.ytd)) + " year to date / " + usd(cents(g.vals.mtd)) + " month to date.", { rows: [gr[0].i] })); else col.add(mkCheck("ok", "tb.grandTotal", "The printed grand total is 0.00 / 0.00", "")); } else if (accts.length) col.add(mkCheck("warn", "tb.grandTotal", "No grand total row was found", "The last page of the report prints it; it may not have been supplied.")); if (gr.length > 1) col.add(mkCheck("warn", "tb.grandTotalMany", plural(gr.length, "grand total row") + " found", "Only one is expected.", { rows: gr.map((g) => g.i) })); // — duplicate accounts after merging (a number that appears on two different pages / twice on a page) const dupA = []; const seenA = new Map(); accts.forEach((a) => { const k = a.m.acct; if (!k) return; if (seenA.has(k)) dupA.push(a.i, seenA.get(k)); else seenA.set(k, a.i); }); if (dupA.length) col.add(mkCheck("error", "tb.duplicateAccounts", "The same account number appears twice", "Account(s): " + uniq(dupA.map((i) => merged[i].acct)).join(", ") + ". An account prints once on a trial balance; one of them was probably misread.", { rows: uniq(dupA), keys: uniq(dupA.map((i) => merged[i].acct)) })); // — as-of date, profit tie-out against the balance sheet, accounts K2B does not know const dt = packetDates(ctx); const asOfD = dt.asOf; if (!asOfD) col.add(mkCheck("warn", "tb.asOf", "No as-of date found", "The trial balance does not print its as-of date and no balance sheet or profit and loss page is in the packet. Type the as-of date in the import options before importing.")); else if (dt.source === "the print date") col.add(mkCheck("warn", "tb.asOf", "As-of date taken from the print date (" + mdy(asOfD) + ") — please confirm", "The trial balance does not print its as-of date and there is no balance sheet or profit and loss page in the packet. The date it was printed is used; change it in the import options if the books were closed as of an earlier date.")); else col.add(mkCheck(dt.conflict ? "warn" : "ok", "tb.asOf", "As-of date " + mdy(asOfD) + " (from " + dt.source + ")", dt.conflict || (dt.printed ? "The report was printed " + mdy(dt.printed) + "." : ""))); const bsp = bsProfit(ctx); let profitCheck = null; if (bsp && accts.length) { const plSum = sum(accts.filter((a) => !isBsAcct(a.m.acct)).map((a) => cents(a.m.vals.ytd))); profitCheck = cents(bsp.value) === -plSum ? mkCheck("ok", "x.profit", "Balance sheet PROFIT (LOSS) " + usd(cents(bsp.value)) + " equals the trial balance's profit and loss accounts", "Minus the sum of the P&L accounts on the trial balance (" + usd(plSum) + " debit = loss) matches the balance sheet exactly (" + bsp.page.label + ").", { pages: [bsp.page.ref], cross: true }) : mkCheck("error", "x.profit", "Balance sheet PROFIT (LOSS) does not equal the trial balance's profit and loss accounts", "The balance sheet (" + bsp.page.label + ") prints PROFIT (LOSS) " + usd(cents(bsp.value)) + " but minus the sum of the P&L accounts on the trial balance is " + usd(-plSum) + " (a difference of " + usd(cents(bsp.value) + plSum) + "). A trial balance amount or the balance sheet was misread.", { pages: [bsp.page.ref], cross: true }); col.add(profitCheck); } const unknown = []; if (ctx.k2bChart.size) accts.forEach((a) => { if (a.m.acct && !ctx.k2bChart.has(a.m.acct)) unknown.push(a); }); if (unknown.length) col.add(mkCheck("warn", "tb.unknownAccounts", plural(unknown.length, "account") + " not in the K2B chart yet", unknown.slice(0, 15).map((a) => a.m.acct + " " + a.m.vals.name).join("\n") + (unknown.length > 15 ? "\n… and " + (unknown.length - 15) + " more" : "") + "\nImport the chart of accounts first, or tick \"Add accounts that are missing from the chart\" when you import the trial balance.", { rows: unknown.map((a) => a.i), keys: unknown.map((a) => a.m.acct) })); const lg = legibilityCheck(sorted); if (lg) col.add(lg); readChecks(col, choices, (c) => c.rowKey.replace(/^[ATGZ]\|/, "").replace(/#\d+$/, ""), "the trial balance"); const sheetName = "Trial balance" + (asOfD ? " as of " + mdy(asOfD) : ""); const notes = []; if (repeats.length) notes.push(plural(repeats.length, "page") + " supplied more than once — counted once."); const spec = { id: "glTrial", pageType: "trial_balance", dest: "glTrial", sheetName, headers: tab.headers, rows: tab.rows, records: recs, keyOf: tab.keyOf, rowKeys: merged.map((m) => (m.kind === "account" ? m.acct : m.kind === "account_total" ? "Total " + m.acct : m.vals.name)), pages: sorted.map((p) => p.ref), notes, decisions: decList, printed: { printedDate: dt.printed ? mdy(dt.printed) : "", asOf: asOfD ? mdy(asOfD) : "", asOfSource: dt.source, accounts: accts.length, accountTotals: ats.length, groupTotals: gts.map((g) => ({ label: g.m.vals.name, mtd: g.m.vals.mtd, ytd: g.m.vals.ytd })), grand: gr.length ? { mtd: gr[0].m.vals.mtd, ytd: gr[0].m.vals.ytd } : null, sumYtd: sY / 100, sumMtd: sM / 100 }, importOpts: asOfD ? { asOf: isoOf(asOfD) } : {}, choices, fixes: choiceFixes(choices, (c) => c.rowKey.replace(/^[ATGZ]\|/, "").replace(/#\d+$/, "")), suggestions: unknown.length ? { unknownAccounts: unknown.map((a) => ({ acct: a.m.acct, name: a.m.vals.name })) } : null, }; return finish(ctx, spec, col, (d) => buildTrial(ctx, d)); } // ═══════════════════════ 11. shared helpers for account numbers ═══════════════════════ const ACCT_RE = /^\d{3,4}[A-Z]{0,2}$/; const CONFUSABLE = { O: "0", "0": "O", I: "1", "1": "I", S: "5", "5": "S", B: "8", "8": "B", Z: "2", "2": "Z" }; // every account number one look-alike character away (O<->0, I<->1, S<->5, B<->8, Z<->2) function lookAlikes(a) { const out = []; for (let i = 0; i < a.length; i++) { const r = CONFUSABLE[a[i]]; if (r) out.push(a.slice(0, i) + r + a.slice(i + 1)); } return uniq(out); } // accounts the packet itself shows: trial balance rows and chart-of-accounts rows (first reading of each) function packetAccounts(ctx) { if (ctx.cache.pacc) return ctx.cache.pacc; const m = new Map(); ctx.of("trial_balance").forEach((p) => (Array.isArray(p.data.rows) ? p.data.rows : []).forEach((r) => { if (r && tbKind(r) === "account") { const a = acctN(r.acct); if (a && !m.has(a)) m.set(a, { name: str(r.description), from: "trial balance" }); } })); ctx.of("chart_of_accounts").forEach((p) => (Array.isArray(p.data.accounts) ? p.data.accounts : []).forEach((r) => { const a = acctN(r && r.acct); if (a && !m.has(a)) m.set(a, { name: str(r.description), from: "chart of accounts" }); })); ctx.cache.pacc = m; return m; } const normType = (t) => { const s = str(t).toLowerCase(); return s ? s[0].toUpperCase() + s.slice(1) : ""; }; // account type for a NEW account, the way the importer infers it from the number function inferType(a, ctx) { if (/^0\d{3}/.test(a)) return "Expense"; const base = (a.match(/^\d+/) || [""])[0]; const k = base && ctx.k2bChart.get(base); if (k && k.type) return normType(k.type); return ({ 1: "Asset", 2: "Asset", 3: "Liability", 4: "Sales", 5: "Sales", 6: "Cost", 7: "Expense", 8: "Income", 9: "Income" })[base[0]] || "Expense"; } // ═══════════════════════ 12. chart of accounts ═══════════════════════ function buildChart(ctx, decList) { const dix = indexDecisions(decList); const pages = ctx.of("chart_of_accounts").filter((p) => Array.isArray(p.data.accounts)); const sorted = pages.slice().sort((a, b) => { const fa = a.data.accounts.length ? acctN(a.data.accounts[0].acct) : "", fb = b.data.accounts.length ? acctN(b.data.accounts[0].acct) : ""; return cmpAcct(fa, fb) || cmpPage(a, b); }); const reads = sorted.map((p, i) => { const seen = {}, recs = []; p.data.accounts.forEach((r, ri) => { r = r || {}; let a = acctN(r.acct); if (dix.accts[a]) a = dix.accts[a]; let base = a ? "C|" + a : "C|?" + p.idx + "." + ri; seen[base] = (seen[base] || 0) + 1; const key = seen[base] > 1 ? base + "#" + seen[base] : base; recs.push({ key, vals: { acct: a, name: r.description == null ? null : str(r.description), alt: r.alt == null ? null : str(r.alt), dc: r.dc == null ? null : str(r.dc), costAcct: r.costAcct == null ? null : acctN(r.costAcct), invAcct: r.invAcct == null ? null : acctN(r.invAcct) } }); }); return { id: refKey(p.ref), ref: p.ref, rank: i, recs, page: p, keys: recs.map((x) => x.key) }; }); const union = new Map(), repeats = []; reads.forEach((rd) => { let best = null, bestN = 0; const counts = new Map(); rd.keys.forEach((k) => { const f = union.get(k); if (f) counts.set(f, (counts.get(f) || 0) + 1); }); counts.forEach((n, f) => { if (n > bestN) { bestN = n; best = f; } }); if (best && bestN >= Math.max(1, Math.ceil(rd.keys.length * 0.5))) { rd.repeatOf = best; repeats.push(rd); } rd.keys.forEach((k) => { if (!union.has(k)) union.set(k, rd); }); }); const merged = mergeReads(reads, { fields: ["name", "alt", "dc", "costAcct", "invAcct"], soft: { name: true }, decisions: dix.merge }); merged.forEach((m) => { m.acct = m.first.vals.acct; }); const n = merged.length, col = new Collector(n); const rowIdx = new Map(); merged.forEach((m, i) => rowIdx.set(m, i)); const labels = { name: "name", alt: "alter number", dc: "D/C flag", costAcct: "cost account", invAcct: "inventory account" }; const choices = choicesOf(merged, (m) => rowIdx.get(m), (f) => labels[f] || f, (f, v) => str(v), null); // type: the printed "S" is not an account type — keep K2B's own type for an account it has, infer it for a new one const newAccts = []; const alnum = (t) => up(t).replace(/[^A-Z0-9]/g, ""); const renamed = []; // printed name differs from K2B's name for the same account (more than punctuation / spacing) let cosmetic = 0; const recs = merged.map((m, i) => { const k = ctx.k2bChart.get(m.acct); let type = "", name = m.vals.name == null ? "" : m.vals.name; if (k && k.name && typeof k.name === "string" && name) { if (alnum(k.name) === alnum(name)) { if (k.name !== name) cosmetic++; } else renamed.push(i); name = k.name; } else if (k && k.name && typeof k.name === "string" && !name) name = k.name; if (k && k.type && typeof k.type === "string" && k.type !== "x") type = normType(k.type); else if (m.acct) { type = inferType(m.acct, ctx); newAccts.push(i); } return { acct: m.acct, name, printedName: m.vals.name == null ? "" : m.vals.name, type, alt: m.vals.alt, dc: m.vals.dc, costAcct: m.vals.costAcct, invAcct: m.vals.invAcct }; }); const tab = ctx.table("glChart", recs, ["acct", "name", "type"]); // required cells const noAcct = [], noName = [], unread = []; merged.forEach((m, i) => { if (!m.acct) noAcct.push(i); if (!m.vals.name) noName.push(i); const f = ["dc", "costAcct", "invAcct", "alt"].filter((x) => m.vals[x] == null); if (f.length) unread.push({ i, f }); }); if (noAcct.length) col.add(mkCheck("error", "chart.requiredAcct", plural(noAcct.length, "row") + " without a readable account number", "An account needs its number.", { rows: noAcct })); if (noName.length) col.add(mkCheck("error", "chart.requiredName", plural(noName.length, "account") + " without a readable name", "Accounts: " + noName.slice(0, 12).map((i) => merged[i].acct || "?").join(", "), { rows: noName, keys: noName.map((i) => merged[i].acct) })); if (unread.length) col.add(mkCheck("warn", "chart.unreadable", plural(unread.length, "account") + " with cells that could not be read", "These cells were too faded to read and are left blank (nothing was guessed):\n" + unread.slice(0, 14).map((u) => merged[u.i].acct + ": " + u.f.map((x) => labels[x]).join(", ")).join("\n") + (unread.length > 14 ? "\n… and " + (unread.length - 14) + " more" : ""), { rows: unread.map((u) => u.i), keys: unread.map((u) => merged[u.i].acct) })); // duplicate account numbers (kept, never merged away) const firstOf = new Map(), dupRows = []; merged.forEach((m, i) => { if (!m.acct) return; if (firstOf.has(m.acct)) { dupRows.push(firstOf.get(m.acct)); dupRows.push(i); } else firstOf.set(m.acct, i); }); if (dupRows.length) col.add(mkCheck("error", "chart.duplicates", "Duplicate account number: the same number is listed more than once", "Account(s) " + uniq(dupRows.map((i) => merged[i].acct)).join(", ") + ". A chart lists each account once, so one of them was probably misread. Both rows are kept so you can see them.", { rows: uniq(dupRows), keys: uniq(dupRows.map((i) => merged[i].acct)) })); // non-numeric account numbers, with a look-alike fix when one fits const fixes = []; const nonNum = []; merged.forEach((m, i) => { if (m.acct && !ACCT_RE.test(m.acct)) nonNum.push(i); }); if (nonNum.length) { const have = new Set(merged.map((m) => m.acct)); nonNum.forEach((i) => { const a = merged[i].acct; // all look-alike variants (up to 2 swaps) that are valid account numbers nobody else uses let cands = lookAlikes(a); lookAlikes(a).forEach((x) => lookAlikes(x).forEach((y) => cands.push(y))); cands = uniq(cands).filter((c) => ACCT_RE.test(c) && !have.has(c)); const prev = i > 0 ? merged[i - 1].acct : "", next = i < n - 1 ? merged[i + 1].acct : ""; const fit = cands.filter((c) => (!prev || !ACCT_RE.test(prev) || cmpAcct(prev, c) < 0) && (!next || !ACCT_RE.test(next) || cmpAcct(c, next) < 0)); (fit.length ? fit : []).slice(0, 2).forEach((c) => fixes.push({ id: "acct|" + enc(a) + "|" + enc(c), kind: "acct", rows: [i], label: "Use " + c + " instead of " + a + (ctx.k2bChart.has(c) ? " (account " + c + " exists in the K2B chart)" : "") })); }); col.add(mkCheck("error", "chart.nonNumeric", plural(nonNum.length, "account number") + " is not a number", nonNum.map((i) => "\"" + merged[i].acct + "\" (" + (merged[i].vals.name || "no name") + ")").join(", ") + ". An account number is digits with an optional letter at the end; a letter inside it is usually a misread digit.", { rows: nonNum, keys: nonNum.map((i) => merged[i].acct) })); } // print order const ordBad = []; for (let i = 1; i < n; i++) { const a = merged[i - 1].acct, b = merged[i].acct; if (a && b && ACCT_RE.test(a) && ACCT_RE.test(b) && cmpAcct(a, b) >= 0 && a !== b) ordBad.push(i); } if (ordBad.length) col.add(mkCheck("warn", "chart.order", plural(ordBad.length, "account") + " out of order", "The old chart prints in ascending account order. Out of order: " + ordBad.slice(0, 10).map((i) => merged[i - 1].acct + " then " + merged[i].acct).join("; "), { rows: ordBad, keys: ordBad.map((i) => merged[i].acct) })); const dcBad = []; merged.forEach((m, i) => { if (m.vals.dc && !/^[YN]( \d+)?$/i.test(m.vals.dc)) dcBad.push(i); }); if (dcBad.length) col.add(mkCheck("warn", "chart.dc", plural(dcBad.length, "D/C flag") + " is not Y or N", "Kept exactly as printed. " + dcBad.slice(0, 10).map((i) => merged[i].acct + " \"" + merged[i].vals.dc + "\"").join(", "), { rows: dcBad, keys: dcBad.map((i) => merged[i].acct) })); const linkBad = []; merged.forEach((m, i) => { ["costAcct", "invAcct"].forEach((f) => { const v = m.vals[f]; if (v && !ACCT_RE.test(v)) linkBad.push(i); }); }); if (linkBad.length) col.add(mkCheck("warn", "chart.links", plural(uniq(linkBad).length, "account") + " with an odd cost / inventory account", "Check: " + uniq(linkBad).slice(0, 10).map((i) => merged[i].acct + " (" + merged[i].vals.costAcct + " / " + merged[i].vals.invAcct + ")").join(", "), { rows: uniq(linkBad) })); if (repeats.length) col.add(mkCheck("ok", "chart.repeats", plural(repeats.length, "page") + " supplied more than once — counted once", repeats.map((rd) => rd.page.label + " repeats " + rd.repeatOf.page.label).join("\n"), { pages: repeats.map((rd) => rd.page.ref) })); // new accounts get an inferred type const unknownNew = ctx.k2bChart.size ? newAccts.filter((i) => !ctx.k2bChart.has(merged[i].acct)) : []; if (unknownNew.length) col.add(mkCheck("warn", "chart.newAccounts", plural(unknownNew.length, "account") + " new to the K2B chart — type guessed from the number", "The old print has no usable account type, so a new account's type is inferred from its number: " + unknownNew.slice(0, 12).map((i) => merged[i].acct + " = " + recs[i].type).join(", ") + ". Change it in the chart afterwards if it is wrong.", { rows: unknownNew, keys: unknownNew.map((i) => merged[i].acct) })); if (ctx.k2bChart.size && (cosmetic || renamed.length)) col.add(mkCheck("ok", "chart.names", "K2B keeps its own account names" + (renamed.length ? " (" + plural(renamed.length, "name") + " differ from the old print)" : ""), cosmetic + " names differ only in hyphens or spacing" + (renamed.length ? ". Different wording on the old print:\n" + renamed.slice(0, 10).map((i) => merged[i].acct + ": K2B \"" + recs[i].name + "\", old print \"" + recs[i].printedName + "\"").join("\n") + (renamed.length > 10 ? "\n… and " + (renamed.length - 10) + " more" : "") : "") + ". The import brings in the alter number, D/C flag, cost and inventory accounts and leaves existing names alone.", { rows: renamed, keys: renamed.map((i) => merged[i].acct) })); const lg = legibilityCheck(sorted); if (lg) col.add(lg); readChecks(col, choices, (c) => c.rowKey.replace(/^C\|/, "").replace(/#\d+$/, ""), "the chart of accounts"); // trial-balance accounts these chart pages do not list: a SEPARATE suggestion, never added silently const notes = ["The old print's account type column (S on every row) is not imported: K2B keeps the type of each account it already has and infers the type of a new one from its number."]; let suggestions = null; const tbAcc = packetAccounts(ctx); const mine = new Set(merged.map((m) => m.acct)); const fromTb = []; tbAcc.forEach((v, a) => { if (v.from === "trial balance" && !mine.has(a)) fromTb.push({ acct: a, name: v.name, type: inferType(a, ctx), inK2B: ctx.k2bChart.size ? ctx.k2bChart.has(a) : null }); }); if (fromTb.length) { const miss = fromTb.filter((x) => x.inK2B === false); suggestions = { fromTrialBalance: fromTb }; notes.push("The trial balance in this packet lists " + plural(fromTb.length, "account") + " that these chart pages do not" + (ctx.k2bChart.size ? " (" + miss.length + " of them are not in the K2B chart)" : "") + ". They are NOT added from here; if you want them in the chart, tick \"Add accounts that are missing from the chart\" when you import the trial balance."); } if (repeats.length) notes.push(plural(repeats.length, "page") + " supplied more than once — counted once."); const spec = { id: "glChart", pageType: "chart_of_accounts", dest: "glChart", sheetName: "Chart of accounts", headers: tab.headers, rows: tab.rows, records: recs, keyOf: tab.keyOf, rowKeys: merged.map((m) => m.acct), pages: sorted.map((p) => p.ref), notes, decisions: decList, printed: { accounts: n, pages: sorted.length }, importOpts: {}, choices, fixes: fixes.concat(choiceFixes(choices, (c) => c.rowKey.replace(/^C\|/, "").replace(/#\d+$/, ""))), suggestions, }; return finish(ctx, spec, col, (d) => buildChart(ctx, d)); } // ═══════════════════════ 13. vehicle master (the same cars in three sort orders) ═══════════════════════ const VFIELDS = ["year", "nu", "make", "model", "color", "invoiceDate", "age", "retail", "purchaseCost", "totalRepairs", "totalCost", "glCost", "vin", "miles"]; const VLABEL = { year: "year", nu: "new / used", make: "make", model: "model", color: "color", invoiceDate: "invoice date", age: "days in stock", retail: "retail price", purchaseCost: "purchase cost", totalRepairs: "total repairs", totalCost: "total cost", glCost: "GL cost", vin: "VIN", miles: "miles" }; const SORT_RANK = { inventory_account: 0, vin: 1, invoice_date: 2, other: 3 }; const SORT_NAME = { inventory_account: "inventory-account sort", vin: "VIN sort", invoice_date: "invoice-date sort", other: "other sort" }; function vinOk(v) { if (typeof v !== "string" || v.length !== 17) return false; const T = {}; "0123456789".split("").forEach((c, i) => { T[c] = i; }); "ABCDEFGH".split("").forEach((c, i) => { T[c] = i + 1; }); "JKLMN".split("").forEach((c, i) => { T[c] = i + 1; }); T.P = 7; T.R = 9; "STUVWXYZ".split("").forEach((c, i) => { T[c] = i + 2; }); const W = [8, 7, 6, 5, 4, 3, 2, 10, 0, 9, 8, 7, 6, 5, 4, 3, 2]; let s = 0; for (let i = 0; i < 17; i++) { if (!(v[i] in T)) return false; s += T[v[i]] * W[i]; } const r = s % 11; return v[8] === (r === 10 ? "X" : String(r)); } function permutations(arr) { if (arr.length > 6) return null; const out = []; const rec = (a, k) => { if (k === a.length) { out.push(a.slice()); return; } for (let i = k; i < a.length; i++) { [a[k], a[i]] = [a[i], a[k]]; rec(a, k + 1); [a[k], a[i]] = [a[i], a[k]]; } }; rec(arr.slice(), 0); return out; } const vehKey = (r, p, ri) => { const s = up(r.stock).replace(/\s+/g, ""); return s || "?" + p.idx + "." + ri; }; const normVDate = (s) => { const d = parseDate(s); return d ? mdy(d) : str(s); }; function vehVals(r) { return { year: numOrNull(r.year), nu: up(r.nu), make: str(r.make), model: str(r.model), color: str(r.color), invoiceDate: normVDate(r.invoiceDate), age: numOrNull(r.age), retail: numOrNull(r.retail), purchaseCost: numOrNull(r.purchaseCost), totalRepairs: numOrNull(r.totalRepairs), totalCost: numOrNull(r.totalCost), glCost: numOrNull(r.glCost), vin: up(r.vin).replace(/\s+/g, ""), miles: numOrNull(r.miles) }; } // sum of a group of rows the way the printed subtotal lines add (null = 0), in cents function vSums(rows) { return { n: rows.length, retail: sum(rows.map((r) => cents(r.retail))), purchaseCost: sum(rows.map((r) => cents(r.purchaseCost))), totalRepairs: sum(rows.map((r) => cents(r.totalRepairs))), totalCost: sum(rows.map((r) => cents(r.totalCost))), glCost: sum(rows.map((r) => cents(r.glCost))) }; } const VSUM_LABEL = { n: "vehicles", retail: "retail", purchaseCost: "purchase cost", totalRepairs: "repairs", totalCost: "total cost", glCost: "GL cost" }; function vDiff(s, pr) { const want = { n: pr.vehicles != null ? pr.vehicles : pr.totalCars, retail: cents(pr.retail), purchaseCost: cents(pr.purchaseCost), totalRepairs: cents(pr.totalRepairs), totalCost: cents(pr.totalCost), glCost: cents(pr.glCost) }; const out = []; Object.keys(VSUM_LABEL).forEach((k) => { if (k === "n" ? s[k] !== want[k] : s[k] !== want[k]) out.push({ k, got: s[k], want: want[k] }); }); return out; } const vFmt = (k, v) => (k === "n" ? String(v) : usd(v)); const vDiffText = (d) => d.map((x) => VSUM_LABEL[x.k] + " " + vFmt(x.k, x.got) + " vs printed " + vFmt(x.k, x.want)).join("; "); // best page order for the inventory-account sort: a group label prints BELOW its rows, so rows left without a label at the end of a page belong // to the first subtotal of the NEXT PRINTED page. The pdf can hold the pages in reverse order, so try the orders and keep the one where every subtotal ties. function inferInvOrder(pages, prior, valsOf) { valsOf = valsOf || ((r) => r.vals); const sim = (order) => { const assign = new Map(), printed = new Map(); let carry = []; order.forEach((pg) => { const first = pg.subs.length ? acctN(pg.subs[0].invAcct) : ""; let run = []; pg.rows.forEach((r) => { if (r.printedInv) { run.forEach((x) => assign.set(x.key, r.printedInv)); run = []; assign.set(r.key, r.printedInv); } else run.push(r); }); if (carry.length && first) { carry.forEach((x) => assign.set(x.key, first)); carry = []; } carry = carry.concat(run); pg.subs.forEach((s) => { const a = acctN(s.invAcct); if (a && !printed.has(a)) printed.set(a, s); }); }); const byAcct = new Map(); order.forEach((pg) => pg.rows.forEach((r) => { const a = assign.get(r.key); if (a) (byAcct.get(a) || byAcct.set(a, []).get(a)).push(valsOf(r)); })); let tied = 0; const detail = []; printed.forEach((s, a) => { const rows = byAcct.get(a) || [], d = vDiff(vSums(rows), s); if (!d.length) tied++; else detail.push({ acct: a, diff: d }); }); return { assign, tied, total: printed.size, leftover: carry, detail, order }; }; const base = pages.slice(); const perms = permutations(base); if (!perms) { // more than six pages: every order cannot be tried, so try the ones a scanner really produces — the order given, its reverse, the order of the PAGE numbers const cands = []; if (prior) cands.push(prior); cands.push(base); cands.push(base.slice().reverse()); let b = null; cands.forEach((p) => { const r = sim(p), sc = r.tied * 1000 - r.leftover.length; if (!b || sc > b.sc) { r.sc = sc; b = r; } }); b.searched = true; b.limited = true; b.tried = cands.length; return b; } let best = null; const cand = []; if (prior) cand.push(prior); perms.forEach((p) => { if (!prior || p.some((x, i) => x !== prior[i])) cand.push(p); }); // identity first among the rest so ties keep the order the pages were given in cand.forEach((p) => { const r = sim(p); const sc = r.tied * 1000 - r.leftover.length; if (!best || sc > best.sc) { r.sc = sc; best = r; } }); best.searched = true; best.tried = cand.length; return best; } function buildVehicles(ctx, decList) { const dix = indexDecisions(decList); const pages = ctx.of("vehicle_master").filter((p) => Array.isArray(p.data.rows)); const sortOf = (p) => { const s = str(p.data.sortedBy); return s === "invoice_date" || s === "inventory_account" || s === "vin" ? s : "other"; }; const items = pages.map((p) => { const seen = {}; const rows = p.data.rows.map((r, ri) => { r = r || {}; let key = vehKey(r, p, ri); seen[key] = (seen[key] || 0) + 1; if (seen[key] > 1) key += "#" + seen[key]; return { key, ri, printedInv: acctN(r.invAcct), vals: vehVals(r) }; }); return { p, sort: sortOf(p), rows, subs: Array.isArray(p.data.subtotals) ? p.data.subtotals.filter(Boolean) : [], totals: p.data.totals && p.data.totals.totalCars != null ? p.data.totals : null, keys: rows.map((x) => x.key) }; }); // group by sort; a page whose rows were mostly read before (same sort) is a repeat const bySort = {}; items.forEach((it) => { (bySort[it.sort] = bySort[it.sort] || []).push(it); }); const repeats = []; Object.keys(bySort).forEach((s) => { const have = new Map(); const keep = []; bySort[s].forEach((it) => { let best = null, bn = 0; const cnt = new Map(); it.keys.forEach((k) => { const f = have.get(k); if (f) cnt.set(f, (cnt.get(f) || 0) + 1); }); cnt.forEach((n, f) => { if (n > bn) { bn = n; best = f; } }); if (best && bn >= Math.max(1, Math.ceil(it.keys.length * 0.5))) { it.repeatOf = best; repeats.push(it); } else keep.push(it); it.keys.forEach((k) => { if (!have.has(k)) have.set(k, it); }); }); bySort[s].uniq = keep; }); // inventory-account sort: infer the page order let invInfo = null, invGiven = [], invOrder = (bySort.inventory_account && bySort.inventory_account.uniq) || []; if (invOrder.length) { const notesPrior = invOrder.map((it) => { const m = /PAGE\s+(\d+)\s+of\s+\d+/i.exec(it.p.notes.join(" ")); return m ? +m[1] : null; }); let prior = null; if (notesPrior.every((x) => x != null) && uniq(notesPrior).length === notesPrior.length) prior = invOrder.map((it, i) => ({ it, n: notesPrior[i] })).sort((a, b) => a.n - b.n).map((x) => x.it); invGiven = invOrder.slice(); invInfo = inferInvOrder(invOrder, prior); invOrder = invInfo.order; } // order of the other sorts: by first VIN / first invoice date so the result does not depend on upload order const vinOrder = ((bySort.vin && bySort.vin.uniq) || []).slice().sort((a, b) => ((a.rows[0] || { vals: { vin: "" } }).vals.vin < (b.rows[0] || { vals: { vin: "" } }).vals.vin ? -1 : 1)); const dateOrder = ((bySort.invoice_date && bySort.invoice_date.uniq) || []).slice().sort((a, b) => cmpPage(a.p, b.p)); const otherOrder = ((bySort.other && bySort.other.uniq) || []).slice().sort((a, b) => cmpPage(a.p, b.p)); const ordered = [].concat(invOrder, vinOrder, dateOrder, otherOrder, repeats); const reads = ordered.map((it, i) => ({ id: refKey(it.p.ref), ref: it.p.ref, rank: SORT_RANK[it.sort] * 100 + i, sort: it.sort, page: it.p, recs: it.rows.map((r) => ({ key: r.key, vals: r.vals })), item: it })); const merged = mergeReads(reads, { fields: VFIELDS, soft: { make: true, model: true, color: true }, decisions: dix.merge }); // the first search used the figures as read on the inventory-account pages. When no order ties, search again with the figures after the vote and the // user's choices (a value read differently on another sort may be the very reason the subtotal did not tie) if (invInfo && invInfo.searched && invInfo.tied < invInfo.total) { const win = new Map(); merged.forEach((m) => win.set(m.key, m.vals)); const again = inferInvOrder(invGiven, null, (r) => win.get(r.key) || r.vals); if (again.tied > invInfo.tied) { invInfo = again; invOrder = again.order; } } const assign = invInfo ? invInfo.assign : new Map(); merged.forEach((m) => { m.stock = m.key.replace(/#\d+$/, ""); m.inv = assign.get(m.key) || ""; m.sorts = uniq(m.reads.map((rd) => rd.sort)); }); const n = merged.length, col = new Collector(n); const rowIdx = new Map(); merged.forEach((m, i) => rowIdx.set(m, i)); const rowOf = (m) => rowIdx.get(m); // print date of the report (age = print date - invoice date) let printDate = null; { const c = new Map(); pages.forEach((p) => p.notes.forEach((t) => { const m = /printed\s+(\d{1,2}[-\/]\d{1,2}[-\/]\d{2,4})/i.exec(t); const d = m && parseDate(m[1]); if (d) c.set(d.t, { d, n: ((c.get(d.t) || { n: 0 }).n) + 1 }); })); let b = null; c.forEach((v) => { if (!b || v.n > b.n) b = v; }); if (b) printDate = b.d; if (!printDate) { const c2 = new Map(); merged.forEach((m) => { const d = parseDate(m.vals.invoiceDate); if (d && isNum(m.vals.age)) { const t = d.t + m.vals.age * 864e5; c2.set(t, (c2.get(t) || 0) + 1); } }); let bt = null, bn = 0; c2.forEach((cn, t) => { if (cn > bn) { bn = cn; bt = t; } }); if (bt != null && bn >= 3) { const x = new Date(bt); printDate = { y: x.getUTCFullYear(), m: x.getUTCMonth() + 1, d: x.getUTCDate(), t: bt }; } } } const ageOf = (v) => { const d = parseDate(v.invoiceDate); if (!d || !printDate) return null; return Math.round((printDate.t - d.t) / 864e5); }; // hints for the user's choice: which reading makes the row consistent const hint = (m, d, ch) => { const f = d.field; const test = (o) => { const v = Object.assign({}, m.vals); v[f] = o.value; if (f === "purchaseCost" || f === "totalRepairs" || f === "glCost") return cents(v.glCost) === cents(v.purchaseCost) + cents(v.totalRepairs); if (f === "age" || f === "invoiceDate") { const e = ageOf(v); return e == null ? null : (isNum(v.age) ? v.age === e : e === 0); } if (f === "vin") return vinOk(v.vin) ? true : (v.vin.length === 17 ? false : null); return null; }; const res = ch.options.map(test); if (res.some((x) => x === null)) return; const good = res.filter(Boolean).length; ch.options.forEach((o, i) => { o.hint = res[i] ? "fits the other figures on this vehicle" : "does not fit the other figures on this vehicle"; if (res[i] && good === 1) o.suggested = true; }); }; const choices = choicesOf(merged, rowOf, (f) => VLABEL[f] || f, (f, v) => (f === "retail" || f === "purchaseCost" || f === "totalRepairs" || f === "totalCost" || f === "glCost" ? money(v) : isNum(v) ? String(v) : str(v)), hint); // records + sheet const nuWord = (v) => (v === "N" ? "New" : v === "U" ? "Used" : v); // the old print has no pack column: TOTAL COST = purchase + repairs + pack, so the pack is the difference (the importer needs it - without it the unit's pack is lost) const packOf = (v) => (isNum(v.totalCost) && isNum(v.purchaseCost) ? (cents(v.totalCost) - cents(v.purchaseCost) - cents(v.totalRepairs)) / 100 : null); const recs = merged.map((m) => ({ stock: m.stock, vin: m.vals.vin, asking: m.vals.retail, miles: m.vals.miles, newUsed: nuWord(m.vals.nu), yr: m.vals.year == null ? "" : String(m.vals.year), mk: m.vals.make, mdl: m.vals.model, color: m.vals.color, invAcct: m.inv, invoiceDate: m.vals.invoiceDate, invoiceCost: m.vals.purchaseCost, repairs: m.vals.totalRepairs, glCost: m.vals.glCost, age: m.vals.age, pack: packOf(m.vals) })); const tab = ctx.table("inventory", recs, ["stock", "vin", "newUsed", "yr", "mk", "mdl", "invAcct", "invoiceCost", "glCost", "pack"]); const rowsOf = (pred) => { const o = []; merged.forEach((m, i) => { if (pred(m)) o.push(i); }); return o; }; const stk = (idx) => idx.slice(0, 14).map((i) => merged[i].stock).join(", ") + (idx.length > 14 ? " … +" + (idx.length - 14) : ""); // — required: a stock number or VIN const noId = rowsOf((m) => m.key.charAt(0) === "?" && !m.vals.vin); if (noId.length) col.add(mkCheck("error", "veh.requiredId", plural(noId.length, "vehicle") + " without a stock number or VIN", "Each vehicle needs a stock # or VIN.", { rows: noId })); // — the cents identity every row of the print obeys const glBad = rowsOf((m) => m.vals.glCost != null && m.vals.purchaseCost != null && cents(m.vals.glCost) !== cents(m.vals.purchaseCost) + cents(m.vals.totalRepairs)); if (glBad.length) col.add(mkCheck("error", "veh.glCost", plural(glBad.length, "vehicle") + " where GL cost is not purchase cost + repairs", glBad.slice(0, 10).map((i) => { const v = merged[i].vals; return merged[i].stock + ": GL cost " + money(v.glCost) + " but purchase " + money(v.purchaseCost) + " + repairs " + money(v.totalRepairs || 0) + " = " + money((cents(v.purchaseCost) + cents(v.totalRepairs)) / 100); }).join("\n"), { rows: glBad, keys: glBad.map((i) => merged[i].stock) })); else col.add(mkCheck("ok", "veh.glCost", "GL cost = purchase cost + repairs on all " + merged.length + " vehicles", "")); // — age if (printDate) { const ageBad = rowsOf((m) => { if (!parseDate(m.vals.invoiceDate)) return m.vals.invoiceDate === "" && isNum(m.vals.age); const e = ageOf(m.vals); return isNum(m.vals.age) ? m.vals.age !== e : e !== 0; }); if (ageBad.length) col.add(mkCheck("warn", "veh.age", plural(ageBad.length, "vehicle") + " whose days in stock do not match the invoice date", "Days in stock should be the print date (" + mdy(printDate) + ") minus the invoice date.\n" + ageBad.slice(0, 10).map((i) => { const m = merged[i]; return m.stock + ": invoice " + (m.vals.invoiceDate || "blank") + ", printed " + (isNum(m.vals.age) ? m.vals.age : "blank") + " days, expected " + ageOf(m.vals); }).join("\n"), { rows: ageBad, keys: ageBad.map((i) => merged[i].stock) })); else col.add(mkCheck("ok", "veh.age", "Days in stock = print date (" + mdy(printDate) + ") minus invoice date on all vehicles", "")); } // — VIN check digit (a VIN read the same way on several copies that still fails is a non-standard VIN, not a misread) const vinFail = [], vinAgreed = []; merged.forEach((m, i) => { const v = m.vals.vin; if (v.length !== 17) return; if (vinOk(v)) return; const dv = m.dis.find((d) => d.field === "vin"); if (m.nReads >= 2 && !dv) vinAgreed.push(i); else vinFail.push(i); }); if (vinFail.length) col.add(mkCheck("warn", "veh.vin", plural(vinFail.length, "VIN") + (vinFail.length === 1 ? " fails" : " fail") + " the VIN check digit", "A character may have been misread: " + stk(vinFail), { rows: vinFail, keys: vinFail.map((i) => merged[i].stock) })); if (vinAgreed.length) col.add(mkCheck("ok", "veh.vinAgreed", plural(vinAgreed.length, "VIN") + (vinAgreed.length === 1 ? " fails the check digit but was" : " fail the check digit but were") + " read the same way on every copy", stk(vinAgreed) + " — most likely a non-standard VIN on the old system rather than a misread.", { rows: vinAgreed, keys: vinAgreed.map((i) => merged[i].stock) })); // — shop-equipment style lines (no make, no real VIN) const shop = rowsOf((m) => !m.vals.make); if (shop.length) col.add(mkCheck("warn", "veh.noMake", plural(shop.length, "line") + " without a make", stk(shop) + " — a line with no make and no color looks like shop equipment rather than a vehicle. It is counted in the report's Total Cars, so it is kept; remove it in Inventory afterwards if it is not a vehicle.", { rows: shop, keys: shop.map((i) => merged[i].stock) })); // — subtotals (units, retail, purchase, repairs, total cost, GL cost) of the merged vehicles per inventory account const printedSubs = new Map(); invOrder.forEach((it) => it.subs.forEach((s) => { const a = acctN(s.invAcct); if (a && !printedSubs.has(a)) printedSubs.set(a, s); })); const subBad = [], subGood = []; printedSubs.forEach((s, a) => { const idx = rowsOf((m) => m.inv === a), sm = vSums(idx.map((i) => merged[i].vals)), d = vDiff(sm, s); if (!d.length) { subGood.push(a); return; } let extra = ""; if (d.some((x) => x.k === "n" && x.want > x.got)) { // a vehicle without an inventory account may be the missing one const lone = rowsOf((m) => !m.inv).filter((i) => !vDiff(vSums(idx.map((j) => merged[j].vals).concat([merged[i].vals])), s).length); if (lone.length === 1) extra = " Adding " + merged[lone[0]].stock + " (which is not in the inventory-account sort) would make it tie."; } subBad.push({ a, idx, text: "Account " + a + ": " + vDiffText(d) + "." + extra }); }); // — inventory accounts const sortsSeen = uniq(items.map((it) => it.sort)); if (invInfo) { const noInv = rowsOf((m) => !m.inv); if (invInfo.order.length > 1 && printedSubs.size) { const same = invInfo.order.every((it, i) => it === invGiven[i]); const orderText = invInfo.order.map((it) => it.p.label).join(" → "); if (subBad.length) col.add(mkCheck("warn", "veh.pageOrderNone", "No page order makes every inventory-account subtotal tie", "K2B tried " + (invInfo.limited ? "the order the pages were given in and its reverse" : "all " + invInfo.tried + " possible orders of the " + invInfo.order.length + " pages") + " (rows left without a group label at the bottom of a page belong to the first group of the next printed page). The order that ties the most subtotals, " + subGood.length + " of " + printedSubs.size + ", is: " + orderText + ". The subtotal check names the ones that do not tie: a figure on one of these pages was probably misread.", { keys: subBad.map((b) => b.a) })); else if (!same) col.add(mkCheck("ok", "veh.pageOrder", "Inventory-account pages put in print order", "The pages were not in print order. " + (invInfo.limited ? "Trying the order given and its reverse" : "Trying the possible orders") + ", only this one makes every printed subtotal tie: " + orderText + ".")); else col.add(mkCheck("ok", "veh.pageOrder", "Inventory-account pages are in print order", (invInfo.limited ? "Checked the order given and its reverse" : "Checked every possible order of the " + invInfo.order.length + " pages") + "; this one ties.")); } if (invInfo.leftover.length) col.add(mkCheck("error", "veh.noLabel", plural(invInfo.leftover.length, "vehicle") + " at the end of the last page have no inventory account", "Their group label prints on the next page, which is not in the packet: " + invInfo.leftover.slice(0, 10).map((r) => r.key).join(", "), { keys: invInfo.leftover.map((r) => r.key) })); const rest = noInv.filter((i) => !invInfo.leftover.some((r) => r.key === merged[i].key)); if (rest.length) col.add(mkCheck("warn", "veh.noInvAcct", plural(rest.length, "vehicle") + " not in the inventory-account sort", stk(rest) + " — they were read from another sort order only, so their inventory account is unknown.", { rows: rest, keys: rest.map((i) => merged[i].stock) })); } else if (merged.length) { col.add(mkCheck("warn", "veh.noInvAcct", "No inventory-account sort in the packet", "Without it the inventory account of each vehicle (and so truck / car and new / used from the account) is unknown, and the subtotals cannot be checked.")); } if (printedSubs.size) { if (subBad.length) col.add(mkCheck("error", "veh.subtotals", plural(subBad.length, "inventory-account subtotal") + " does not tie", subBad.map((b) => b.text).join("\n"), { rows: uniq([].concat.apply([], subBad.map((b) => b.idx))), keys: subBad.map((b) => b.a) })); else col.add(mkCheck("ok", "veh.subtotals", "All " + subGood.length + " inventory-account subtotals tie (" + subGood.join(", ") + ")", "Units, retail, purchase cost, repairs, total cost and GL cost all equal the printed subtotal lines.")); } // — report totals over every vehicle const totalsItem = items.find((it) => it.totals); const totals = totalsItem ? totalsItem.totals : null; if (totals) { const d = vDiff(vSums(merged.map((m) => m.vals)), totals); if (d.length) col.add(mkCheck("error", "veh.totals", "The vehicles do not add up to the printed report total", "Total Cars " + totals.totalCars + ": " + vDiffText(d) + ".", { keys: ["Total Cars"] })); else col.add(mkCheck("ok", "veh.totals", "All " + merged.length + " vehicles equal the printed report total (Total Cars " + totals.totalCars + ", GL cost " + usd(cents(totals.glCost)) + ")", "")); // each sort order on its own: complete? tied? Object.keys(bySort).forEach((s) => { const u = bySort[s].uniq; if (!u.length) return; const keys = new Set(); u.forEach((it) => it.keys.forEach((k) => keys.add(k))); const missing = rowsOf((m) => !keys.has(m.key)); if (missing.length === 0) { /* complete: the figures are checked above on the merged vehicles */ } else if (missing.length && missing.length <= 5) { col.add(mkCheck("error", "veh.sortMissing." + s, plural(missing.length, "vehicle") + " missing from the " + SORT_NAME[s], "The " + SORT_NAME[s] + " has " + keys.size + " vehicles but the report total is " + totals.totalCars + ". Found on the other sort order(s) only: " + stk(missing) + ". A row was probably skipped when this sort was read.", { rows: missing, keys: missing.map((i) => merged[i].stock) })); } else if (missing.length) { col.add(mkCheck("ok", "veh.sortCoverage." + s, "The " + SORT_NAME[s] + " in this packet covers " + keys.size + " of " + totals.totalCars + " vehicles", "The other " + missing.length + " are on pages that are not in the packet, so this sort can only confirm the vehicles it shows.", {})); } }); } else if (merged.length) { col.add(mkCheck("warn", "veh.totals", "The report total page is not in the packet", "The last page of each sort order prints \"Total Cars\" and the report totals; without it the vehicles cannot be checked against them.")); } // — seen once (only meaningful when more than one sort was supplied) if (sortsSeen.length >= 2) { const once = rowsOf((m) => m.nReads === 1); if (once.length) col.add(mkCheck("warn", "veh.seenOnce", plural(once.length, "vehicle") + " seen once", stk(once) + " — each appears in only one sort order, so nothing confirms its figures. They are valid but worth a look.", { rows: once, keys: once.map((i) => merged[i].stock) })); } else if (merged.length) { col.add(mkCheck("warn", "veh.oneSort", "Only one sort order of the vehicle report was supplied", "Printing the report in a second sort order lets K2B compare every figure; with one copy nothing can be cross-checked.")); } if (repeats.length) col.add(mkCheck("ok", "veh.repeats", plural(repeats.length, "page") + " supplied more than once — counted once", repeats.map((it) => it.p.label + " repeats " + it.repeatOf.p.label).join("\n"), { pages: repeats.map((it) => it.p.ref) })); const lg = legibilityCheck(pages.slice().sort(cmpPage)); if (lg) col.add(lg); readChecks(col, choices, (c) => c.rowKey.replace(/#\d+$/, ""), "the vehicles"); const notes = []; if (printDate) notes.push("Vehicle master printed " + mdy(printDate) + "."); if (sortsSeen.length) notes.push("Sort orders read: " + sortsSeen.map((s) => SORT_NAME[s]).join(", ") + "."); const spec = { id: "inventory", pageType: "vehicle_master", dest: "inventory", sheetName: "Vehicle master", headers: tab.headers, rows: tab.rows, records: recs, keyOf: tab.keyOf, rowKeys: merged.map((m) => m.stock), pages: pages.slice().sort(cmpPage).map((p) => p.ref), notes, decisions: decList, printed: { printDate: printDate ? mdy(printDate) : "", totals: totals, subtotals: Array.from(printedSubs.values()), sorts: sortsSeen, vehicles: n }, importOpts: {}, choices, fixes: choiceFixes(choices, (c) => c.rowKey.replace(/#\d+$/, "")), }; return finish(ctx, spec, col, (d) => buildVehicles(ctx, d)); } // ═══════════════════════ 14. helpers for finding the likely misread cell ═══════════════════════ // which of these amounts (cents) would, if misread by a single digit / swapped digits / flipped sign, explain a total that is `diff` cents too high? function digitSlip(values, diff) { const out = []; const ds = (c) => String(Math.abs(c)); values.forEach((v, i) => { if (v == null || diff === 0) return; const w = v - diff; if (w === -v) { out.push(i); return; } // sign flipped if ((v < 0) !== (w < 0) && v !== 0 && w !== 0) return; const a = ds(v), b = ds(w); if (a.length === b.length) { let dif = 0, pos = -1; for (let k = 0; k < a.length; k++) if (a[k] !== b[k]) { dif++; pos = k; } if (dif === 1) { out.push(i); return; } // one digit if (dif === 2 && pos > 0 && a[pos] === b[pos - 1] && a[pos - 1] === b[pos]) out.push(i); // two digits swapped } }); return out; } // ═══════════════════════ 15. aged schedules (261, 220, 220A) ═══════════════════════ const titleTokens = (t) => up(t).replace(/[^A-Z0-9 &\/]/g, " ").split(/\s+/).filter((w) => w && w !== "SCHEDULE" && !/^\d+$/.test(w)); const similarTitle = (a, b) => { const x = titleTokens(a), y = titleTokens(b); if (!x.length || !y.length) return true; const sa = new Set(x), sb = new Set(y); let n = 0; sa.forEach((w) => { if (sb.has(w)) n++; }); return n / Math.max(sa.size, sb.size) >= 0.5 || n === Math.min(sa.size, sb.size); }; function schedGroups(ctx) { const groups = []; ctx.of("aged_schedule").filter((p) => Array.isArray(p.data.rows)).forEach((p) => { const gl = acctN(p.data.glAcct), title = str(p.data.title); let g = groups.find((x) => x.gl === gl && similarTitle(x.title, title)); if (!g) { g = { gl, title, name: str(p.data.glAcctName), pages: [] }; groups.push(g); } g.pages.push(p); }); return groups; } const SCHEDULE_OK = { "261": 1, "220": 1, "220A": 1 }; function buildSchedule(ctx, gi, decList) { const dix = indexDecisions(decList); const g = schedGroups(ctx)[gi]; // the printed totals as numbers (anything that is not a number is "not printed / not readable", never NaN) const cleanT = (t) => (t && typeof t === "object" ? { final: numOrNull(t.final), count: numOrNull(t.count), over30: numOrNull(t.over30), over30Count: numOrNull(t.over30Count) } : null); const hasTotals = (p) => { const t = cleanT(p.data.totals); return !!t && t.final != null; }; // totals page last; otherwise by file / page number const sorted = g.pages.slice().sort((a, b) => (hasTotals(a) ? 1 : 0) - (hasTotals(b) ? 1 : 0) || cmpPage(a, b)); const reads = sorted.map((p, i) => { const seen = {}, recs = []; p.data.rows.forEach((r, ri) => { r = r || {}; const c = up(r.control).replace(/\s+/g, ""); const base = c || "?" + ri; seen[base] = (seen[base] || 0) + 1; recs.push({ key: base + "#" + seen[base], vals: { name: str(r.description), days: numOrNull(r.days), amount: numOrNull(r.amount) }, control: str(r.control), ri }); }); const sig = recs.map((x) => x.key + "|" + x.vals.days + "|" + cents(x.vals.amount)); return { id: refKey(p.ref), ref: p.ref, rank: i, recs, page: p, sig }; }); // repeated pages (most rows identical to an earlier page) vote on the same rows instead of adding new ones const repeats = []; reads.forEach((rd, i) => { rd.scope = "p" + i; for (let j = 0; j < i; j++) { const other = reads[j]; if (other.repeatOf) continue; const set = new Set(other.sig); const hit = rd.sig.filter((s) => set.has(s)).length; if (rd.sig.length && hit >= Math.ceil(rd.sig.length * 0.7)) { rd.repeatOf = other; rd.scope = other.scope; repeats.push(rd); break; } } rd.recs = rd.recs.map((x) => ({ key: rd.scope + "|" + x.key, vals: x.vals, control: x.control, ri: x.ri })); }); const merged = mergeReads(reads, { fields: ["name", "days", "amount"], soft: { name: true }, decisions: dix.merge }); merged.forEach((m) => { m.control = m.first.rd.recs.find((x) => x.key === m.key).control; }); const n = merged.length, col = new Collector(n); const rowIdx = new Map(); merged.forEach((m, i) => rowIdx.set(m, i)); const choices = choicesOf(merged, (m) => rowIdx.get(m), (f) => ({ name: "description", days: "days", amount: "balance" })[f] || f, (f, v) => (f === "amount" ? money(v) : f === "days" ? intf(v) : str(v)), null); const dateP = sorted.map((p) => parseDate(p.data.printedDate)).filter(Boolean); const asOfD = dateP.length ? dateP[0] : null; const asOfCell = asOfD ? mdy(asOfD) : ""; const glCell = g.gl || ""; const recs = merged.map((m) => ({ control: m.control, name: m.vals.name, days: m.vals.days, balance: m.vals.amount, glAcct: glCell, asOf: asOfCell })); const tab = ctx.table("schedule", recs, ["control", "balance", "glAcct", "asOf"]); const label = "Schedule " + (g.gl || "?") + (g.name ? " " + g.name : ""); // required cells const noCtl = [], noAmt = [], noDays = []; merged.forEach((m, i) => { if (!m.control) noCtl.push(i); if (m.vals.amount == null) noAmt.push(i); if (m.vals.days == null) noDays.push(i); }); if (noCtl.length) col.add(mkCheck("error", "sched.requiredControl", plural(noCtl.length, "row") + " without a readable control number", "A control # (customer account or stock) is needed to post the row to the right account. Nothing is guessed.", { rows: noCtl })); if (noAmt.length) col.add(mkCheck("error", "sched.requiredAmount", plural(noAmt.length, "row") + " without a readable balance", "Control #: " + noAmt.slice(0, 12).map((i) => merged[i].control || "?").join(", "), { rows: noAmt, keys: noAmt.map((i) => merged[i].control) })); if (noDays.length) col.add(mkCheck("warn", "sched.days", plural(noDays.length, "row") + " without readable days", "Without days the age of the balance is unknown: " + noDays.slice(0, 12).map((i) => merged[i].control).join(", "), { rows: noDays, keys: noDays.map((i) => merged[i].control) })); // tie-out against the printed totals (the last page of a schedule prints them) const withT = sorted.filter(hasTotals); let totals = withT.length ? cleanT(withT[0].data.totals) : null; const distinctT = uniq(withT.map((p) => JSON.stringify(p.data.totals))); if (distinctT.length > 1) col.add(mkCheck("warn", "sched.totalsMany", "Several different printed totals for " + label, withT.map((p) => p.label + ": " + JSON.stringify(p.data.totals)).join("\n") + "\nThe first is used.")); const amts = merged.map((m) => cents(m.vals.amount)); const sAll = sum(amts), overIdx = merged.map((m, i) => (isNum(m.vals.days) && m.vals.days > 30 ? i : -1)).filter((i) => i >= 0); const sOver = sum(overIdx.map((i) => amts[i])); if (!totals) col.add(mkCheck("warn", "sched.noTotals", label + ": no page with the printed totals", "The last page of a schedule prints FINAL TOTAL, COUNT TOTAL and the over-30 figures. Without it the " + merged.length + " rows cannot be checked.", { })); else { const slip = (diff, pool) => { const idx = digitSlip(pool.map((i) => amts[i]), diff).map((k) => pool[k]); return idx.length <= 4 ? idx : []; }; const problems = []; const tot = totals; if (tot.final != null && sAll !== cents(tot.final)) { const diff = sAll - cents(tot.final); const cand = slip(diff, merged.map((m, i) => i)); problems.push({ id: "sched.sum", title: label + ": the rows add up to " + usd(sAll) + " but FINAL TOTAL prints " + usd(cents(tot.final)), text: (diff < 0 ? "The rows are " + usd(-diff) + " short — a row of " + usd(-diff) + " may have been skipped, or a number misread." : "The rows are " + usd(diff) + " too high — one amount may have been misread by " + usd(diff) + " or a row read twice.") + (cand.length ? " Likely rows: " + cand.slice(0, 5).map((i) => merged[i].control + " (" + money(merged[i].vals.amount) + ")").join(", ") + "." : ""), rows: cand, keys: cand.map((i) => merged[i].control) }); } if (tot.count != null && merged.length !== tot.count) problems.push({ id: "sched.count", title: label + ": " + merged.length + " rows but COUNT TOTAL prints " + tot.count, text: merged.length < tot.count ? tot.count - merged.length + " row(s) were not read." : merged.length - tot.count + " row(s) too many were read.", rows: [], keys: [] }); if (tot.over30 != null && sOver !== cents(tot.over30)) { const diff = sOver - cents(tot.over30); const cand = slip(diff, overIdx); problems.push({ id: "sched.over30", title: label + ": rows over 30 days add up to " + usd(sOver) + " but OVER 30 $ prints " + usd(cents(tot.over30)), text: (diff < 0 ? "Short by " + usd(-diff) + "." : "Too high by " + usd(diff) + ".") + (cand.length ? " Likely rows: " + cand.slice(0, 5).map((i) => merged[i].control + " (" + money(merged[i].vals.amount) + ")").join(", ") + "." : ""), rows: cand, keys: cand.map((i) => merged[i].control) }); } if (tot.over30Count != null && overIdx.length !== tot.over30Count) problems.push({ id: "sched.over30Count", title: label + ": " + overIdx.length + " rows over 30 days but OVER 30 CNT prints " + tot.over30Count, text: "Check the days column too: a row's days decide which side of 30 it falls.", rows: [], keys: [] }); problems.forEach((pr) => col.add(mkCheck("error", pr.id, pr.title, pr.text, { rows: pr.rows, keys: pr.keys }))); if (!problems.length) col.add(mkCheck("ok", "sched.totals", label + ": " + merged.length + " rows tie to the printed totals", "FINAL TOTAL " + usd(cents(tot.final)) + (tot.count != null ? ", COUNT " + tot.count : "") + (tot.over30 != null ? ", over 30 days " + usd(cents(tot.over30)) + (tot.over30Count != null ? " / " + tot.over30Count : "") : "") + (sorted.length > 1 ? " (summed across " + sorted.length + " pages)" : "") + ".")); } // a row seen in only one of several readings of the same page const lone = []; merged.forEach((m, i) => { const sc = m.key.split("|")[0]; const nReadsOfScope = reads.filter((rd) => rd.scope === sc).length; if (nReadsOfScope > 1 && m.nReads === 1) lone.push(i); }); if (lone.length) col.add(mkCheck("warn", "sched.rowOnce", plural(lone.length, "row") + " appear in only one of two readings of the same page", lone.slice(0, 12).map((i) => merged[i].control).join(", "), { rows: lone, keys: lone.map((i) => merged[i].control) })); if (repeats.length) col.add(mkCheck("ok", "sched.repeats", plural(repeats.length, "page") + " supplied more than once — counted once", repeats.map((rd) => rd.page.label + " repeats " + rd.repeatOf.page.label).join("\n"), { pages: repeats.map((rd) => rd.page.ref) })); if (!asOfD) col.add(mkCheck("warn", "sched.asOf", label + ": no print date found", "The schedule's balances are as of the day it was printed. Type the as-of date in the import options.")); if (dateP.length > 1 && uniq(dateP.map((d) => d.t)).length > 1) col.add(mkCheck("warn", "sched.asOfMany", label + ": its pages show different print dates", dateP.map((d) => mdy(d)).join(", ") + ". The first is used.")); const supported = !!SCHEDULE_OK[g.gl]; if (!supported) col.add(mkCheck("error", "sched.unsupported", label + ": K2B cannot import this schedule here", "Only schedules 261 (factory receivables), 220 and 220A (customer A/R) can be imported as sub-ledger detail. Post any other schedule as a journal entry.")); const lg = legibilityCheck(sorted); if (lg) col.add(lg); readChecks(col, choices, (c) => c.rowKey.split("|").slice(1).join("|").replace(/#\d+$/, ""), label); const notes = []; const tbAsOf = packetDates(ctx).asOf; if (asOfD && tbAsOf && tbAsOf.t > asOfD.t) notes.push("Printed " + mdy(asOfD) + ", " + Math.round((tbAsOf.t - asOfD.t) / 864e5) + " days before the trial balance date " + mdy(tbAsOf) + " — the schedule total will not equal the trial-balance balance of account " + g.gl + " because of what happened in between."); const spec = { id: "schedule:" + (g.gl || ("t" + gi)), pageType: "aged_schedule", dest: "schedule", sheetName: label, headers: tab.headers, rows: tab.rows, records: recs, keyOf: tab.keyOf, rowKeys: merged.map((m) => m.control), pages: sorted.map((p) => p.ref), notes, decisions: decList, importable: supported, whyNot: supported ? "" : "Only schedules 261, 220 and 220A can be imported here.", printed: { glAcct: g.gl, title: g.title, glAcctName: g.name, printedDate: asOfCell, totals: totals || null, rows: merged.length, over30: sOver / 100, over30Count: overIdx.length }, importOpts: Object.assign({}, asOfD ? { asOf: isoOf(asOfD) } : {}, g.gl ? { glAcct: g.gl } : {}), choices, fixes: choiceFixes(choices, (c) => c.rowKey.split("|").slice(1).join("|").replace(/#\d+$/, "")), }; return finish(ctx, spec, col, (d) => buildSchedule(ctx, gi, d)); } // ═══════════════════════ 16. deal recaps ═══════════════════════ const ADD_N = [1, 2, 3, 4], COMM_N = [1, 2, 3]; function flatDeal(d) { const sold = d.sold || {}, t1 = d.trade1 || {}, t2 = d.trade2 || {}, cost = d.cost || {}, front = d.front || {}, fi = d.fi || {}, dl = d.dealer || {}, fin = d.financing || {}; const comm = Array.isArray(d.commissions) ? d.commissions : [], adds = Array.isArray(cost.adds) ? cost.adds : []; const tm = /^\s*\d{4}\s+([NU])\b/i.exec(str(sold.desc)); const zeroDesc = (s) => { const t = str(s); return t === "0" ? "" : t; }; const v = { dealNo: str(d.dealNo), invNo: str(d.invNo), date: dateCell(d.date), lender: str(d.lender), fiMgr: str((comm[2] || {}).name), salesperson: str(d.salesperson), buyer: str(d.buyer), cobuyer: str(d.cobuyer), street: str(d.street), city: str(d.city), state: str(d.state), postal: str(d.postal), phone: str(d.phone), stock: str(sold.stock), vin: up(sold.vin).replace(/\s+/g, ""), vehicle: str(sold.desc), miles: numOrNull(sold.miles), type: tm ? (tm[1].toUpperCase() === "N" ? "New" : "Used") : "", tradeStock: str(t1.stock), tradeDesc: zeroDesc(t1.desc), tradeVin: up(t1.vin).replace(/\s+/g, ""), tradeMiles: numOrNull(t1.miles) || null, invoicePrice: numOrNull(cost.invoice), holdback: numOrNull(cost.holdback), pack: numOrNull(cost.pack), sfd: numOrNull(cost.sfd), glRepairs: numOrNull(cost.glRepairs), totalRepairs: numOrNull(cost.totalRepairs), totalCost: numOrNull(cost.totalCost), salePrice: numOrNull(front.sellingPrice), cashReceived: numOrNull(front.cashReceived), cashStated: numOrNull(front.cashStated), tradeAcv: numOrNull(front.trade1Acv), tradeAllow: numOrNull(front.trade1Allow), trade2Acv: numOrNull(front.trade2Acv), trade2Allow: numOrNull(front.trade2Allow), grossProfit: numOrNull(front.grossProfit), contractRate: numOrNull(fi.contractRate), buyRate: numOrNull(fi.buyRate), reserve: numOrNull(fi.reserve), clPrem: numOrNull(fi.clPrem), clCost: numOrNull(fi.clCost), ahPrem: numOrNull(fi.ahPrem), ahCost: numOrNull(fi.ahCost), vscSale: numOrNull(fi.vscSale), vscCost: numOrNull(fi.vscCost), gapSale: numOrNull(fi.gapSale), gapCost: numOrNull(fi.gapCost), miscFi: numOrNull(fi.miscFi), fiProfit: numOrNull(fi.fiProfit), holdbackProfit: numOrNull(dl.holdback), miscDealer: numOrNull(dl.miscDealer), totalDealerProfit: numOrNull(dl.totalDealerProfit), netNet: numOrNull(d.netNet), amtFinanced: numOrNull(fin.amountFinanced), financeCharge: numOrNull(fin.financeCharge), term: numOrNull(fin.termMonths), payment: numOrNull(fin.payment), payoffLender: str(fin.payoffLender), payoff1: numOrNull(fin.payoff1), _dealerPack: numOrNull(dl.pack), _trade2Desc: zeroDesc(t2.desc), _trade2Stock: str(t2.stock), _tradeVin2: up(t2.vin).replace(/\s+/g, ""), _sfdLabel: str(cost.sfdLabel), }; ADD_N.forEach((k) => { const a = adds[k - 1] || {}; v["add" + k + "Label"] = str(a.label); v["add" + k + "Amt"] = numOrNull(a.amount); }); COMM_N.forEach((k) => { const c = comm[k - 1] || {}; v["comm" + k + "Name"] = str(c.name); v["comm" + k + "Pct"] = numOrNull(c.pct); v["comm" + k + "Flat"] = numOrNull(c.flat); v["comm" + k + "Amt"] = numOrNull(c.amount); v["_c" + k + "Base"] = numOrNull(c.base); }); return v; } const DEAL_SOFT = { buyer: 1, cobuyer: 1, street: 1, city: 1, vehicle: 1, salesperson: 1, lender: 1, fiMgr: 1, tradeDesc: 1, comm1Name: 1, comm2Name: 1, comm3Name: 1, payoffLender: 1, add1Label: 1, add2Label: 1, add3Label: 1, add4Label: 1, _sfdLabel: 1, _trade2Desc: 1 }; function dealCore(ctx, dix) { const pages = ctx.of("deal_recap").filter((p) => p.data && typeof p.data === "object"); const reads = pages.map((p, i) => { const v = flatDeal(p.data), no = v.dealNo; return { id: refKey(p.ref), ref: p.ref, rank: i, page: p, recs: [{ key: no ? "D|" + no : "D|?" + p.idx, vals: v }] }; }); const fields = reads.length ? Object.keys(reads[0].recs[0].vals) : []; const merged = mergeReads(reads, { fields, soft: DEAL_SOFT, decisions: dix.merge }); merged.sort((a, b) => { const x = a.key.slice(2), y = b.key.slice(2); return (/^\d+$/.test(x) && /^\d+$/.test(y) ? +x - +y : x < y ? -1 : x > y ? 1 : 0); }); return { merged, reads, pages }; } // the printed relationships of a recap, in cents (tolerance: 2 cents, the old print rounds) const TOL = 2; function dealRules(v) { const out = [], c = cents; const adds = sum(ADD_N.map((k) => c(v["add" + k + "Amt"]))); if (v.totalCost != null && v.holdback == null && v.totalRepairs == null && v.invoicePrice != null) { const comp = c(v.invoicePrice) + c(v.pack) + adds + c(v.sfd) + c(v.glRepairs); out.push({ id: "cost", printed: c(v.totalCost), computed: comp }); } if (v.grossProfit != null && v.salePrice != null && v.totalCost != null) { const over = (c(v.tradeAcv) - c(v.tradeAllow)) + (c(v.trade2Acv) - c(v.trade2Allow)); const comp = c(v.salePrice) - c(v.totalCost) + over; out.push({ id: "gross", printed: c(v.grossProfit), computed: comp, flipped: c(v.salePrice) - c(v.totalCost) - over }); } if (v.fiProfit != null) { const comp = c(v.reserve) + (c(v.clPrem) - c(v.clCost)) + (c(v.ahPrem) - c(v.ahCost)) + (c(v.vscSale) - c(v.vscCost)) + (c(v.gapSale) - c(v.gapCost)) + c(v.miscFi); out.push({ id: "fi", printed: c(v.fiProfit), computed: comp }); } if (v.totalDealerProfit != null && v.grossProfit != null) { const comp = c(v.grossProfit) + c(v.fiProfit) + c(v.holdbackProfit) + c(v._dealerPack) + c(v.miscDealer); out.push({ id: "tdp", printed: c(v.totalDealerProfit), computed: comp }); } COMM_N.forEach((k) => { const amt = v["comm" + k + "Amt"], base = v["_c" + k + "Base"], pct = v["comm" + k + "Pct"], flat = v["comm" + k + "Flat"]; if (amt == null || base == null) return; const comp = Math.round(base * (pct || 0)) + c(flat); // base x pct% in cents (pct is a percent) out.push({ id: "comm" + k, printed: c(amt), computed: comp, minimum: c(amt) > comp + TOL }); }); if (v.netNet != null && v.totalDealerProfit != null) { const comp = c(v.totalDealerProfit) - sum(COMM_N.map((k) => c(v["comm" + k + "Amt"]))); out.push({ id: "net", printed: c(v.netNet), computed: comp }); } if (v.financeCharge != null && v.payment != null && v.term != null && v.amtFinanced != null) { out.push({ id: "fin", printed: c(v.financeCharge), computed: Math.round(v.payment * v.term * 100) - c(v.amtFinanced) }); } return out; } const RULE_TITLE = { cost: "Total cost = invoice + pack + add-ons + special finance discount + GL repairs", gross: "Gross profit = selling price − total cost + (trade ACV − allowance)", fi: "F&I profit = reserve + (premium − cost) of each product + misc F&I", tdp: "Total dealer profit = gross + F&I + holdback + pack + misc dealer income", comm1: "Commission 1 = % × base + flat (or the printed minimum)", comm2: "Commission 2 = % × base + flat (or the printed minimum)", comm3: "Commission 3 (F&I manager) = % × base + flat (or the printed minimum)", net: "Net / net = total dealer profit − commissions", fin: "Finance charge = payment × term − amount financed", }; function buildDeals(ctx, decList) { const dix = indexDecisions(decList); const core = dealCore(ctx, dix); const { merged, reads, pages } = core; const n = merged.length, col = new Collector(n); const rowIdx = new Map(); merged.forEach((m, i) => rowIdx.set(m, i)); const dealNoOf = (m) => m.key.slice(2); const fmtDeal = (f, v) => (typeof v === "number" ? (/Pct|Rate/.test(f) ? numf(v) : f === "miles" || f === "term" ? intf(v) : money(v)) : str(v)); const choices = choicesOf(merged, (m) => rowIdx.get(m), (f) => f.replace(/^_/, ""), fmtDeal, null); const recs = merged.map((m) => m.vals); const tab = ctx.table("deals", recs, ["dealNo", "salePrice"]); // required / shape const noSale = [], badNo = [], noDate = []; merged.forEach((m, i) => { if (m.vals.salePrice == null) noSale.push(i); if (!/^\d+$/.test(m.vals.dealNo)) badNo.push(i); if (!m.vals.date) noDate.push(i); }); if (noSale.length) col.add(mkCheck("error", "deal.requiredSale", plural(noSale.length, "deal") + " without a readable selling price", "Deal(s) " + noSale.map((i) => dealNoOf(merged[i])).join(", "), { rows: noSale, keys: noSale.map((i) => dealNoOf(merged[i])) })); if (badNo.length) col.add(mkCheck("error", "deal.number", plural(badNo.length, "deal") + " without a readable deal number", "A deal number is digits, kept exactly as printed.", { rows: badNo })); if (noDate.length) col.add(mkCheck("warn", "deal.noDate", plural(noDate.length, "deal") + " with no delivery date printed", "Deal(s) " + noDate.map((i) => dealNoOf(merged[i])).join(", ") + " print a blank date; the deal is imported without a delivery date. A journal document for it uses its own print date.", { rows: noDate, keys: noDate.map((i) => dealNoOf(merged[i])) })); // tie-outs: one check per rule, listing every deal that does not tie const results = merged.map((m) => dealRules(m.vals)); const rules = ["cost", "gross", "fi", "tdp", "comm1", "comm2", "comm3", "net", "fin"]; rules.forEach((rid) => { const per = results.map((rs, i) => ({ i, r: rs.find((x) => x.id === rid) })).filter((x) => x.r); if (!per.length) return; const bad = [], mini = []; per.forEach(({ i, r }) => { const diff = r.printed - r.computed; if (/^comm/.test(rid)) { if (r.printed >= r.computed - TOL) { if (r.minimum) mini.push({ i, r }); return; } } else if (Math.abs(diff) <= TOL) return; bad.push({ i, r, diff }); }); if (bad.length) { col.add(mkCheck("warn", "deal." + rid, RULE_TITLE[rid] + " — " + plural(bad.length, "deal") + " does not tie", bad.map(({ i, r, diff }) => "Deal " + dealNoOf(merged[i]) + ": printed " + usd(r.printed) + ", computed " + usd(r.computed) + " (difference " + usd(diff) + ")" + (rid === "gross" && r.flipped != null && Math.abs(r.printed - r.flipped) <= TOL ? " — it ties with the over/under allowance sign reversed" : "") + (/^comm/.test(rid) ? " — a commission below % × base + flat" : "")).join("\n"), { rows: bad.map((b) => b.i), keys: bad.map((b) => dealNoOf(merged[b.i])) })); } else { col.add(mkCheck("ok", "deal." + rid, RULE_TITLE[rid] + " — ties on " + (per.length === n ? "all " + n : per.length + " of " + n) + " deal" + (n === 1 ? "" : "s"), mini.length ? mini.map(({ i, r }) => "Deal " + dealNoOf(merged[i]) + ": " + usd(r.printed) + " printed vs " + usd(r.computed) + " computed — the printed amount is a minimum (mini), kept as printed.").join("\n") : "", { rows: [], mini: mini.map((x) => dealNoOf(merged[x.i])) })); } }); // VIN check digits (a misread character breaks them) const vinBad = []; merged.forEach((m, i) => { [m.vals.vin, m.vals.tradeVin].forEach((v) => { if (v && v.length === 17 && !vinOk(v)) vinBad.push(i); }); }); if (vinBad.length) col.add(mkCheck("warn", "deal.vin", plural(uniq(vinBad).length, "deal") + " with a VIN that fails the check digit", "Check the VIN against the printout: deal(s) " + uniq(vinBad).map((i) => dealNoOf(merged[i])).join(", "), { rows: uniq(vinBad), keys: uniq(vinBad).map((i) => dealNoOf(merged[i])) })); const trade2 = merged.map((m, i) => (m.vals._trade2Stock || m.vals._trade2Desc ? i : -1)).filter((i) => i >= 0); if (trade2.length) col.add(mkCheck("warn", "deal.trade2", plural(trade2.length, "deal") + " with a second trade-in vehicle", "K2B keeps one trade-in vehicle per deal; the second trade's allowance and ACV are imported, its vehicle details are not.", { rows: trade2 })); // posting worksheets of the same deals wsChecks(ctx, merged.map((m) => ({ no: dealNoOf(m), v: m.vals }))).forEach((c) => { const i = merged.findIndex((m) => dealNoOf(m) === c.dealNo); if (i >= 0) col.add(Object.assign({}, c, { rows: c.level === "ok" ? [] : [i], cross: true })); }); const lg = legibilityCheck(pages); if (lg) col.add(lg); readChecks(col, choices, (c) => "Deal " + c.rowKey.slice(2), "the deal recaps"); const dupPages = reads.length - n; const notes = ["The third header line of a recap (0-227 on every one) is not a manager number and is not imported; the F&I manager is the name on the third commission line."]; if (dupPages > 0) notes.push(plural(dupPages, "recap page") + " repeated a deal already read — counted once."); const spec = { id: "deals", pageType: "deal_recap", dest: "deals", sheetName: "Deal recaps", headers: tab.headers, rows: tab.rows, records: recs, keyOf: tab.keyOf, rowKeys: merged.map((m) => dealNoOf(m)), pages: pages.map((p) => p.ref), notes, decisions: decList, printed: { deals: n, netNet: sum(merged.map((m) => cents(m.vals.netNet))) / 100 }, importOpts: {}, choices, fixes: choiceFixes(choices, (c) => "Deal " + c.rowKey.slice(2)), }; return finish(ctx, spec, col, (d) => buildDeals(ctx, d)); } // ═══════════════════════ 17. posting worksheets (reference): they must balance and agree with their deal recap ═══════════════════════ const WS_DEBIT = new Set(["Total Cost", "Trade 1 Allow/ACV", "Cash Down", "Cost", "In Transit", "Misc AR"]); function wsChecks(ctx, deals) { const out = []; ctx.of("posting_worksheet").filter((p) => Array.isArray(p.data.lines)).forEach((p) => { const d = p.data, own = d.lines.filter((l) => l && !/^fragment/i.test(str(l.note))); let dr = 0, cr = 0; own.forEach((l) => { if (WS_DEBIT.has(str(l.label))) dr += cents(l.amount); else cr += cents(l.amount); }); const dealNo = str(d.dealNo); if (dr !== cr) out.push(mkCheck("warn", "ws.balance", "Posting worksheet " + dealNo + " does not balance", p.label + ": the debit-side lines add up to " + usd(dr) + " and the credit-side lines to " + usd(cr) + " (difference " + usd(dr - cr) + "). The print has no debit / credit columns, so the side of each line is taken from its label.", { dealNo, pages: [p.ref], keys: [dealNo] })); else out.push(mkCheck("ok", "ws.balance", "Posting worksheet " + dealNo + " balances", p.label + ": debit-side and credit-side lines both add up to " + usd(dr) + ".", { dealNo, pages: [p.ref], keys: [dealNo] })); const deal = deals.find((x) => x.no === dealNo); if (!deal) return; const r = deal.v, by = (label, acct) => own.find((l) => str(l.label) === label && (acct == null || str(l.acct) === acct)); const eq = (label, acct, expect, what) => { const l = by(label, acct); return { ok: !!l && Math.abs(cents(l.amount) - cents(expect)) <= TOL, what: what + ": worksheet " + (l ? money(l.amount) : "missing") + " vs recap " + money(expect || 0) }; }; const items = [ eq("Selling Price", null, r.salePrice, "selling price"), eq("Total Cost", "650A", (r.totalCost || 0) - (r.pack || 0), "total cost (recap less pack)"), eq("Trade 1 Allow/ACV", null, r.tradeAcv, "trade ACV"), eq("Over/Undr", null, (r.tradeAcv || 0) - (r.tradeAllow || 0), "trade over/under"), eq("PayOff", null, r.payoff1, "payoff"), eq("Cash Down", null, r.cashReceived, "cash down"), eq("Gap/PD", null, r.gapSale, "gap"), eq("Svc Contracts", null, r.vscSale, "service contract"), eq("Cost", null, r.vscCost, "service contract cost"), eq("In Transit", null, (r.amtFinanced || 0) - (r.sfd || 0), "in transit (amount financed less special finance discount)"), ].filter((it, i) => i !== 3 || by("Over/Undr", null)); const bad = items.filter((it) => !it.ok); out.push(bad.length ? mkCheck("warn", "ws.recap", "Posting worksheet " + dealNo + " does not agree with the deal recap", p.label + ": " + bad.map((b) => b.what).join("; ") + ".", { dealNo, pages: [p.ref], keys: [dealNo] }) : mkCheck("ok", "ws.recap", "Posting worksheet " + dealNo + " agrees with the deal recap", "All " + items.length + " lines checked (selling price, cost, trade, payoff, cash down, products, in transit).", { dealNo, pages: [p.ref], keys: [dealNo] })); }); return out; } // ═══════════════════════ 18. journal documents ═══════════════════════ const dealMap = (ctx) => { if (!ctx.cache.dealMap) { const m = new Map(); try { dealCore(ctx, indexDecisions([])).merged.forEach((x) => m.set(x.key.slice(2), x.vals)); } catch (e) { /* none */ } ctx.cache.dealMap = m; } return ctx.cache.dealMap; }; // how a deal's journal document posts the recap (the first store's chart): account(s) -> what the recap says it must equal (+ debit / − credit) const JR_MAP = [ [["650B", "650A"], (r) => (r.totalCost || 0) - (r.pack || 0), "cost of sale"], [["450B", "450A"], (r) => -(r.salePrice || 0), "selling price"], [["225A"], (r) => r.cashReceived || 0, "cash down"], [["455"], (r) => -(r.vscSale || 0), "service contract sale"], [["655"], (r) => r.vscCost || 0, "service contract cost"], [["455A"], (r) => -(r.gapSale || 0), "gap sale"], [["655A"], (r) => r.gapCost || 0, "gap cost"], [["808"], (r) => -(r.reserve || 0), "reserve"], [["205"], (r) => r.amtFinanced || 0, "contract in transit"], [["262O", "2620"], (r) => r.reserve || 0, "due from lender (reserve)"], ]; function buildJournals(ctx, decList) { const dix = indexDecisions(decList); const pages = ctx.of("journal_document").filter((p) => Array.isArray(p.data.lines)); const docs = new Map(); pages.forEach((p) => { const doc = str(p.data.document), jr = str(p.data.journal), k = jr + "|" + doc; let d = docs.get(k); if (!d) { d = { key: k, doc, jr, pages: [] }; docs.set(k, d); } d.pages.push(p); }); const docList = Array.from(docs.values()).sort((a, b) => (/^\d+$/.test(a.doc) && /^\d+$/.test(b.doc) ? +a.doc - +b.doc : a.doc < b.doc ? -1 : a.doc > b.doc ? 1 : 0) || (a.jr < b.jr ? -1 : 1)); const reads = [], repeats = []; let rank = 0; docList.forEach((d) => { d.pages.sort(cmpPage); const mine = []; d.pages.forEach((p, pi) => { const seen = {}, recs = []; p.data.lines.forEach((l, li) => { l = l || {}; const ao = acctN(l.acct), ctl = up(l.control).replace(/\s+/g, ""); const base = ao + "|" + ctl; seen[base] = (seen[base] || 0) + 1; const acct = dix.accts[ao] || ao; recs.push({ key: base + "#" + seen[base], acctOrig: ao, control: str(l.control), vals: { acct, amount: numOrNull(l.amount), acctName: str(l.acctName), controlName: str(l.controlName), entryDesc: str(l.entryDescription) } }); }); const rd = { id: refKey(p.ref), ref: p.ref, rank: rank++, page: p, recs, docKey: d.key, doc: d, sig: recs.map((x) => x.key + "|" + cents(x.vals.amount)) }; rd.scope = d.key + "/" + pi; for (let j = 0; j < mine.length; j++) { const o = mine[j]; if (o.repeatOf) continue; const set = new Set(o.sig); const hit = rd.sig.filter((s) => set.has(s)).length; if (rd.sig.length && hit >= Math.ceil(rd.sig.length * 0.5)) { rd.repeatOf = o; rd.scope = o.scope; repeats.push(rd); break; } } rd.recs = rd.recs.map((x) => Object.assign({}, x, { key: rd.scope + "|" + x.key })); mine.push(rd); reads.push(rd); }); }); const merged = mergeReads(reads, { fields: ["acct", "amount", "acctName", "controlName", "entryDesc"], soft: { acctName: true, controlName: true, entryDesc: true }, decisions: dix.merge }); merged.forEach((m) => { m.docKey = m.first.rd.docKey; m.doc = m.first.rd.doc; m.control = m.first.rd.recs.find((x) => x.key === m.key).control; }); const n = merged.length, col = new Collector(n); const rowIdx = new Map(); merged.forEach((m, i) => rowIdx.set(m, i)); const lineName = (m) => m.doc.doc + " " + m.vals.acct; const choices = choicesOf(merged, (m) => rowIdx.get(m), (f) => ({ acct: "account", amount: "amount", acctName: "account name", controlName: "control name", entryDesc: "description" })[f] || f, (f, v) => (f === "amount" ? money(v) : str(v)), null); const deals = dealMap(ctx); const dt = packetDates(ctx); const refOf = (d) => (docList.filter((x) => x.doc === d.doc).length > 1 ? d.jr + "-" + d.doc : d.doc); const fixes = []; // dates: the matching deal recap (document # = deal #), else the print date and a warning const docInfo = docList.map((d) => { const rec = deals.get(d.doc), pd = parseDate(d.pages[0].data.printedDate); let date = "", src = ""; if (dix.dates[d.doc]) { date = dix.dates[d.doc]; src = "you"; } else if (rec && rec.date) { date = rec.date; src = "recap"; } else if (pd) { date = mdy(pd); src = "print"; } return Object.assign(d, { date, src, printDate: pd ? mdy(pd) : "", hasDeal: !!rec }); }); const byDoc = new Map(); merged.forEach((m, i) => { (byDoc.get(m.docKey) || byDoc.set(m.docKey, []).get(m.docKey)).push(i); }); const dateOf = (m) => docInfo.find((d) => d.key === m.docKey).date; const recs = merged.map((m, i) => { const d = docInfo.find((x) => x.key === m.docKey); return { ref: refOf(d), date: d.date, acct: m.vals.acct, amount: m.vals.amount, desc: m.vals.entryDesc, entryDesc: str(d.pages[0].data.description), jrn: /^\d+$/.test(d.jr) ? +d.jr : null, control: m.control, controlName: m.vals.controlName }; }); const tab = ctx.table("glJournal", recs, ["ref", "date", "acct", "amount"]); // required cells const noAcct = [], noAmt = []; merged.forEach((m, i) => { if (!m.vals.acct) noAcct.push(i); if (m.vals.amount == null) noAmt.push(i); }); if (noAcct.length) col.add(mkCheck("error", "journal.requiredAcct", plural(noAcct.length, "line") + " without a readable account", "A journal line needs its account number.", { rows: noAcct })); if (noAmt.length) col.add(mkCheck("error", "journal.requiredAmount", plural(noAmt.length, "line") + " without a readable amount", "Account(s): " + noAmt.slice(0, 12).map((i) => merged[i].vals.acct).join(", "), { rows: noAmt, keys: noAmt.map((i) => lineName(merged[i])) })); // each document against its deal recap (computed first: a line that disagrees with the recap is the likely culprit of an unbalanced document) const disagree = []; docInfo.forEach((d) => { const rec = deals.get(d.doc); if (!rec) return; const idx = byDoc.get(d.key) || [], bad = []; JR_MAP.forEach(([accts, fn, what]) => { const hit = idx.find((i) => accts.includes(merged[i].vals.acct)); const want = cents(fn(rec)); if (hit == null) { if (want !== 0) bad.push({ text: what + " (" + accts[0] + ") missing — the recap says " + usd(want), rows: [] }); return; } const got = cents(merged[hit].vals.amount); if (Math.abs(got - want) > TOL) bad.push({ text: what + " (" + merged[hit].vals.acct + ") is " + usd(got) + " but the recap says " + usd(want), rows: [hit] }); }); if (bad.length) disagree.push({ d, bad }); }); const recapRows = new Set(); disagree.forEach((x) => x.bad.forEach((b) => b.rows.forEach((r) => recapRows.add(r)))); // debits = credits for every document const unbal = []; docInfo.forEach((d) => { const idx = byDoc.get(d.key) || [], dr = sum(idx.map((i) => Math.max(0, cents(merged[i].vals.amount)))), cr = sum(idx.map((i) => Math.max(0, -cents(merged[i].vals.amount)))); d.debits = dr; d.credits = cr; d.lines = idx.length; if (dr !== cr) { const diff = dr - cr; let sl = digitSlip(idx.map((i) => cents(merged[i].vals.amount)), diff).map((k) => idx[k]); const both = sl.filter((i) => recapRows.has(i)); if (both.length) sl = both; if (sl.length > 4) sl = []; unbal.push({ d, idx, diff, sl }); } }); if (unbal.length) col.add(mkCheck("error", "journal.balance", plural(unbal.length, "document") + " where debits do not equal credits", unbal.map((u) => "Document " + u.d.doc + ": debits " + usd(u.d.debits) + ", credits " + usd(u.d.credits) + " (difference " + usd(Math.abs(u.diff)) + ")" + (u.sl.length ? ". A misread amount that would explain it: " + u.sl.slice(0, 4).map((i) => merged[i].vals.acct + " " + money(merged[i].vals.amount)).join(", ") : ". A line may be missing: one for " + usd(Math.abs(u.diff)) + " " + (u.diff > 0 ? "credit" : "debit"))).join("\n"), { rows: uniq([].concat.apply([], unbal.map((u) => (u.sl.length ? u.sl : u.idx)))), keys: unbal.map((u) => u.d.doc) })); else if (docInfo.length) col.add(mkCheck("ok", "journal.balance", docInfo.length === 1 ? "Debits equal credits (" + usd(docInfo[0].debits) + ")" : "Debits equal credits on all " + docInfo.length + " documents", docInfo.map((d) => "Document " + d.doc + ": " + usd(d.debits) + " each side (" + d.lines + " lines)").join("\n"))); // account numbers must exist in the K2B chart or somewhere in this packet; a one-character look-alike is offered as a fix const pk = packetAccounts(ctx), kc = ctx.k2bChart; if (kc.size || pk.size) { const unk = new Map(); merged.forEach((m, i) => { const a = m.vals.acct; if (!a) return; if (kc.has(a) || pk.has(a)) return; (unk.get(a) || unk.set(a, []).get(a)).push(i); }); if (unk.size) { const parts = []; unk.forEach((idx, a) => { const alts = lookAlikes(a).filter((c) => kc.has(c) || pk.has(c)); alts.forEach((c) => fixes.push({ id: "acct|" + enc(a) + "|" + enc(c), kind: "acct", rows: idx, label: "Use " + c + " instead of " + a + " (" + (kc.has(c) ? "account " + c + " is in the K2B chart" : "account " + c + " is on the " + pk.get(c).from + " in this packet") + ")" })); parts.push("Account " + a + " (" + plural(idx.length, "line") + ") is not in the K2B chart or anywhere in this packet" + (alts.length ? " — did you mean " + alts.join(" or ") + "? A letter and a digit that look alike (O and 0, I and 1, S and 5, B and 8, Z and 2) are easy to mix up." : ".")); }); col.add(mkCheck("error", "journal.accounts", plural(unk.size, "account number") + " not found", parts.join("\n"), { rows: uniq([].concat.apply([], Array.from(unk.values()))), keys: Array.from(unk.keys()) })); } else { const onlyPk = new Set(); merged.forEach((m) => { const a = m.vals.acct; if (a && kc.size && !kc.has(a) && pk.has(a)) onlyPk.add(a); }); col.add(mkCheck("ok", "journal.accounts", "Every account number exists" + (kc.size ? " in the K2B chart" : " in this packet"), onlyPk.size ? "Account(s) " + Array.from(onlyPk).join(", ") + " are not in the K2B chart yet but are on the " + "trial balance / chart pages in this packet; import those first." : "")); } } else col.add(mkCheck("warn", "journal.accounts", "Account numbers could not be validated", "There is no chart of accounts or trial balance to check them against.")); const renamed = Object.keys(dix.accts); if (renamed.length) col.add(mkCheck("ok", "journal.renamed", plural(renamed.length, "account number") + " changed by you", renamed.map((a) => a + " → " + dix.accts[a]).join(", "))); // dates const printDated = docInfo.filter((d) => d.src === "print"), noDate = docInfo.filter((d) => !d.date); if (noDate.length) col.add(mkCheck("error", "journal.date", plural(noDate.length, "document") + " without a date", "Document(s) " + noDate.map((d) => d.doc).join(", ") + " have no matching deal recap and no readable print date. Set the date.", { rows: [].concat.apply([], noDate.map((d) => byDoc.get(d.key) || [])), keys: noDate.map((d) => d.doc) })); if (printDated.length) { const idx = [].concat.apply([], printDated.map((d) => byDoc.get(d.key) || [])); col.add(mkCheck("warn", "journal.date", plural(printDated.length, "document") + " dated from the print date — please confirm", "A journal document print has no entry date. Document(s) " + printDated.map((d) => d.doc + " (printed " + d.printDate + ")").join(", ") + " have no deal recap in this packet, so the print date is used. Confirm it, or enter the real posting date.", { rows: idx, keys: printDated.map((d) => d.doc) })); printDated.forEach((d) => fixes.push({ id: "date|" + enc(d.doc) + "|" + enc(d.printDate), kind: "date", rows: byDoc.get(d.key) || [], label: "Confirm the print date " + d.printDate + " for document " + d.doc })); } const fromRecap = docInfo.filter((d) => d.src === "recap"), byUser = docInfo.filter((d) => d.src === "you"); if (fromRecap.length) col.add(mkCheck("ok", "journal.dateRecap", plural(fromRecap.length, "document") + " dated from the matching deal recap", fromRecap.map((d) => "Document " + d.doc + " = deal " + d.doc + ", delivered " + d.date).join("\n"))); if (byUser.length) col.add(mkCheck("ok", "journal.dateUser", plural(byUser.length, "document date") + " set by you", byUser.map((d) => "Document " + d.doc + ": " + d.date).join("\n"))); if (disagree.length) col.add(mkCheck("warn", "journal.recap", plural(disagree.length, "document") + " does not agree with its deal recap", disagree.map((x) => "Document " + x.d.doc + ": " + x.bad.map((b) => b.text).join("; ")).join("\n"), { rows: uniq([].concat.apply([], disagree.map((x) => [].concat.apply([], x.bad.map((b) => b.rows))))), keys: disagree.map((x) => x.d.doc) })); else { const withRecap = docInfo.filter((d) => deals.get(d.doc)); if (withRecap.length) col.add(mkCheck("ok", "journal.recap", withRecap.length === 1 ? "Document " + withRecap[0].doc + " agrees with its deal recap" : plural(withRecap.length, "document") + " agree with their deal recaps", "Selling price, cost, cash down, products, reserve and contract in transit were compared.")); } // already inside the opening balances? if (dt.asOf && ctx.of("trial_balance").length) { const before = docInfo.filter((d) => { const x = parseDate(d.date); return x && x.t <= dt.asOf.t; }); if (before.length) col.add(mkCheck("warn", "journal.beforeAsOf", plural(before.length, "document") + " dated on or before the trial balance date (" + mdy(dt.asOf) + ")", "The trial balance already contains everything posted up to " + mdy(dt.asOf) + ", so importing these documents on top of it would count them twice: " + before.map((d) => d.doc + " (" + d.date + ")").join(", ") + ". Import them only if you are not importing the trial balance, or if you want them for history and will reverse them.", { rows: [].concat.apply([], before.map((d) => byDoc.get(d.key) || [])), keys: before.map((d) => d.doc), cross: true })); } if (repeats.length) col.add(mkCheck("ok", "journal.repeats", plural(repeats.length, "page") + " supplied more than once — counted once", repeats.map((rd) => rd.page.label + " repeats " + rd.repeatOf.page.label).join("\n"), { pages: repeats.map((rd) => rd.page.ref) })); const lg = legibilityCheck(pages.slice().sort(cmpPage)); if (lg) col.add(lg); readChecks(col, choices, (c) => lineName(merged.find((m) => m.key === c.rowKey)), "the journal documents"); const spec = { id: "glJournal", pageType: "journal_document", dest: "glJournal", sheetName: "Journal documents", headers: tab.headers, rows: tab.rows, records: recs, keyOf: tab.keyOf, rowKeys: merged.map((m) => m.doc.doc + " " + m.vals.acct), pages: pages.slice().sort(cmpPage).map((p) => p.ref), notes: [], decisions: decList, printed: { documents: docInfo.map((d) => ({ journal: d.jr, document: d.doc, description: str(d.pages[0].data.description), printedDate: d.printDate, date: d.date, dateFrom: d.src, lines: d.lines, debits: d.debits / 100, credits: d.credits / 100 })) }, importOpts: {}, choices, fixes: fixes.concat(choiceFixes(choices, (c) => lineName(merged.find((m) => m.key === c.rowKey)))), }; return finish(ctx, spec, col, (d) => buildJournals(ctx, d)); } // ═══════════════════════ 19. balance sheet / profit and loss (reference pages): internal totals, repeats, and the trial balance ═══════════════════════ const BS_COLS = ["ytd", "current"]; const PL_COLS = ["mtdSales", "mtdCost", "mtdGross", "mtdCount", "ytdSales", "ytdCost", "ytdGross", "ytdCount"]; const isCountCol = (c) => /Count$/.test(c); function stmtLines(p, cols) { const out = []; (Array.isArray(p.data.lines) ? p.data.lines : []).forEach((l, i) => { l = l || {}; const vals = {}; let any = false; cols.forEach((c) => { const v = numOrNull(l[c]); vals[c] = v; if (v != null) any = true; }); const acct = acctN(l.acct); if (!acct && !any) return; // heading / blank line out.push({ page: p, index: i, acct, desc: str(l.description), vals, computed: !acct, sig: acct + "|" + up(l.description) }); }); return out; } const MASKS = {}; function masksFor(n) { if (MASKS[n]) return MASKS[n]; const arr = []; for (let m = 0; m < (1 << n); m++) { let pc = 0, x = m; while (x) { pc += x & 1; x >>= 1; } arr.push([m, pc]); } arr.sort((a, b) => a[1] - b[1] || b[0] - a[0]); return (MASKS[n] = arr.map((x) => x[0])); } // a total line equals the lines above it since the previous total (in that column), plus any of the earlier totals not yet used up. // Every column is followed on its own: some totals print only one column (gross) and the others (sales, cost, count) add up through the lower totals. function runTotals(lines, cols) { const byLine = new Map(); cols.forEach((c) => { if (!lines.some((l) => l.vals[c] != null)) return; const cv = (v) => (v == null ? 0 : isCountCol(c) ? v : cents(v)), tol = isCountCol(c) ? 0 : 1; let acc = 0, accN = 0, U = [], win = []; lines.forEach((ln, li) => { const val = ln.vals[c]; if (!ln.computed) { acc += cv(val); if (val != null) { accN++; win.push(ln); } return; } if (val == null) return; // this total does not print this column const pool = U.slice(-10), n = pool.length, want = cv(val); let best = null; for (const m of masksFor(n)) { let s = acc; for (let k = 0; k < n; k++) if (m & (1 << k)) s += pool[k].v; const d = want - s, ok = Math.abs(d) <= tol; if (!best || (ok && !best.ok) || (ok === best.ok && !ok && Math.abs(d) < Math.abs(best.d))) best = { m, ok, d }; if (ok) break; } const standalone = !best.ok && accN === 0; // a figure with nothing above it to add up (e.g. OTHER INCOME & DEDUCT): cannot be checked const used = new Set(); if (best.ok) for (let k = 0; k < n; k++) if (best.m & (1 << k)) used.add(pool[k]); if (!standalone || true) { let r = byLine.get(li); if (!r) { r = { li, ln, ok: true, diffs: {}, cols: [], unverified: [] }; byLine.set(li, r); } r.cols.push(c); (r.win = r.win || {})[c] = win.slice(); if (standalone) r.unverified.push(c); else { r.diffs[c] = best.d; if (!best.ok) r.ok = false; } } U = U.filter((u) => !used.has(u)); U.push({ li, v: want }); acc = 0; accN = 0; win = []; }); }); return Array.from(byLine.values()).sort((a, b) => a.li - b.li); } // the balance sheet prints assets (2xx, accumulated depreciation 35x) on the left and liabilities + equity (3xx) on the right; the reader lists the left block // first and its continuation on the next page, so follow each side on its own function bsSides(lines) { let side = "L"; const L = [], R = []; lines.forEach((l) => { if (!l.computed) side = /^(2|35)/.test(l.acct) ? "L" : /^3/.test(l.acct) ? "R" : side; (side === "L" ? L : R).push(l); }); return L.concat(R); } function stmtGroup(pages, cols, reorder) { // drop repeated pages (most lines identical in the same place), compare the repeat with the original const keep = [], repeats = [], diffs = []; pages.forEach((p) => { const lines = stmtLines(p, cols); let rep = null; for (const k of keep) { const set = new Set(k.lines.map((l) => l.sig)); const hit = lines.filter((l) => set.has(l.sig)).length; if (lines.length && hit >= Math.ceil(lines.length * 0.8)) { rep = k; break; } } if (rep) { repeats.push({ p, of: rep.p }); const byS = new Map(); rep.lines.forEach((l) => { const a = byS.get(l.sig) || []; a.push(l); byS.set(l.sig, a); }); const used = {}; lines.forEach((l) => { const arr = byS.get(l.sig); const u = used[l.sig] || 0; used[l.sig] = u + 1; const o = arr && arr[u]; if (!o) return; cols.forEach((c) => { const a = o.vals[c], b = l.vals[c]; if ((a == null) !== (b == null) || (a != null && cents(a) !== cents(b))) diffs.push({ line: l, other: o, col: c, a, b, pages: [rep.p, p] }); }); }); } else keep.push({ p, lines }); }); // page order: the given order, else the order in which the most totals tie const seq = (ord) => { const all = [].concat.apply([], ord.map((k) => k.lines)); return reorder ? reorder(all) : all; }; let order = keep, best = runTotals(seq(order), cols); let score = best.filter((r) => r.ok).length; if (score < best.length) { const perms = permutations(keep.map((k, i) => i)); if (perms) perms.forEach((pm) => { const ord = pm.map((i) => keep[i]), r = runTotals(seq(ord), cols), sc = r.filter((x) => x.ok).length; if (sc > score) { score = sc; best = r; order = ord; } }); } return { lines: seq(order), totals: best, repeats, diffs, pages: keep.map((k) => k.p), order: order.map((k) => k.p) }; } // which of the lines a failing total adds up would, misread by one digit / two digits swapped / a flipped sign, explain the gap? function likelyLines(r, c) { if (isCountCol(c) || !r.win || !r.win[c] || !r.win[c].length) return ""; const w = r.win[c], idx = digitSlip(w.map((l) => cents(l.vals[c])), -r.diffs[c]); return idx.length && idx.length <= 6 ? ". Likely line" + (idx.length > 1 ? "s" : "") + ": " + idx.map((k) => w[k].acct + " " + w[k].desc + " (" + money(w[k].vals[c]) + ")").join(", ") : ""; } function stmtChecks(label, grp, cols, idPrefix) { const checks = []; const bad = grp.totals.filter((r) => !r.ok), checkedN = grp.totals.filter((r) => r.cols.some((c) => r.unverified.indexOf(c) < 0)).length; if (grp.repeats.length) checks.push(mkCheck("ok", idPrefix + ".repeats", plural(grp.repeats.length, "page") + " supplied more than once — counted once", grp.repeats.map((r) => r.p.label + " repeats " + r.of.label).join("\n"), { pages: grp.repeats.map((r) => r.p.ref) })); if (grp.diffs.length) checks.push(mkCheck("warn", idPrefix + ".repeatDiff", plural(grp.diffs.length, "figure") + " read differently on the repeated page", grp.diffs.slice(0, 10).map((d) => d.line.desc + " (" + d.line.acct + ") " + d.col + ": " + (d.a == null ? "blank" : money(d.a)) + " on " + d.pages[0].label + " vs " + (d.b == null ? "blank" : money(d.b)) + " on " + d.pages[1].label).join("\n"), { keys: uniq(grp.diffs.map((d) => d.line.desc || d.line.acct)), lines: grp.diffs.map((d) => ({ page: d.line.page.ref, index: d.line.index, description: d.line.desc })), pages: uniq(grp.diffs.map((d) => d.pages[1].ref)) })); if (bad.length) checks.push(mkCheck("warn", idPrefix + ".totals", plural(bad.length, "total line") + " in the " + label + " does not equal the lines above it", bad.slice(0, 10).map((r) => { const c = r.cols.filter((x) => r.diffs[x] != null && Math.abs(r.diffs[x]) > (isCountCol(x) ? 0 : 1))[0]; return "\"" + r.ln.desc + "\" (" + r.ln.page.label + "): " + c + " prints " + (isCountCol(c) ? r.ln.vals[c] : usd(cents(r.ln.vals[c]))) + " but the lines it adds up to give " + (isCountCol(c) ? r.ln.vals[c] - r.diffs[c] : usd(cents(r.ln.vals[c]) - r.diffs[c])) + " (off by " + (isCountCol(c) ? Math.abs(r.diffs[c]) : usd(Math.abs(r.diffs[c]))) + ")" + likelyLines(r, c); }).join("\n") + (bad.length > 10 ? "\n… and " + (bad.length - 10) + " more" : "") + "\nA line above the total was probably misread or skipped.", { keys: bad.map((r) => r.ln.desc), lines: bad.map((r) => ({ page: r.ln.page.ref, index: r.ln.index, description: r.ln.desc })), pages: uniq(bad.map((r) => r.ln.page.ref)) })); else if (grp.totals.length) checks.push(mkCheck("ok", idPrefix + ".totals", "All " + checkedN + " total lines in the " + label + " equal the lines above them", grp.totals.length > checkedN ? plural(grp.totals.length - checkedN, "figure") + " (" + grp.totals.filter((r) => r.cols.every((c) => r.unverified.indexOf(c) >= 0)).map((r) => r.ln.desc).join(", ") + ") stand alone with no lines above to add up, so they are not checked." : "")); // every account line of a profit and loss prints sales, cost and gross: gross = sales + cost. A line where it does not is named by its account if (cols.indexOf("ytdGross") >= 0) { const badL = []; grp.lines.filter((l) => !l.computed).forEach((l) => ["mtd", "ytd"].forEach((b) => { const sv = l.vals[b + "Sales"], cv2 = l.vals[b + "Cost"], gv = l.vals[b + "Gross"]; if (sv != null && cv2 != null && gv != null && cents(sv) + cents(cv2) !== cents(gv)) badL.push({ l, b, sv, cv2, gv }); })); if (badL.length) checks.push(mkCheck("warn", idPrefix + ".lineGross", plural(badL.length, "line") + " in the " + label + " where sales + cost does not equal gross", badL.slice(0, 10).map((x) => "\"" + x.l.desc + "\" (" + x.l.acct + ", " + x.l.page.label + ") " + (x.b === "mtd" ? "month" : idPrefix === "plc" ? "comparison column" : "year to date") + ": sales " + money(x.sv) + " + cost " + money(x.cv2) + " = " + usd(cents(x.sv) + cents(x.cv2)) + " but gross prints " + usd(cents(x.gv))).join("\n") + (badL.length > 10 ? "\n… and " + (badL.length - 10) + " more" : "") + "\nOne of the three figures on that line was probably misread.", { keys: uniq(badL.map((x) => x.l.acct + " " + x.l.desc)), lines: badL.map((x) => ({ page: x.l.page.ref, index: x.l.index, description: x.l.desc })), pages: uniq(badL.map((x) => x.l.page.ref)) })); } return checks; } // the 'month' block of the profit and loss prints on both the year-to-date pages and the comparison pages: two readings of the same figures function monthAgreement(a, b) { const idx = new Map(); a.lines.forEach((l) => { const k = l.sig; const arr = idx.get(k) || []; arr.push(l); idx.set(k, arr); }); const used = {}; const diffs = []; let compared = 0; b.lines.forEach((l) => { const arr = idx.get(l.sig), u = used[l.sig] || 0; used[l.sig] = u + 1; const o = arr && arr[u]; if (!o) { diffs.push({ l, text: "\"" + (l.desc || l.acct) + "\" is not on the year-to-date pages" }); return; } ["mtdSales", "mtdCost", "mtdGross", "mtdCount"].forEach((c) => { compared++; const x = o.vals[c], y = l.vals[c]; if ((x == null) !== (y == null) || (x != null && cents(x) !== cents(y))) diffs.push({ l, text: "\"" + (l.desc || l.acct) + "\" " + c + ": " + (x == null ? "blank" : money(x)) + " vs " + (y == null ? "blank" : money(y)) }); }); }); a.lines.forEach((l) => { if (!b.lines.some((x) => x.sig === l.sig)) diffs.push({ l, text: "\"" + (l.desc || l.acct) + "\" is not on the comparison pages" }); }); return { diffs, compared }; } function statements(ctx, tbDec) { const out = { reference: {}, cross: [] }; const bsPages = ctx.of("balance_sheet").filter((p) => Array.isArray(p.data.lines)); const plPages = ctx.of("profit_loss").filter((p) => Array.isArray(p.data.lines)); let bsG = null, plG = null, plC = null; if (bsPages.length) { bsG = stmtGroup(bsPages, ["ytd"], bsSides); out.reference.balance_sheet = stmtChecks("balance sheet", bsG, ["ytd"], "bs"); } const plY = plPages.filter((p) => !str(p.data.comparisonColumn)), plX = plPages.filter((p) => str(p.data.comparisonColumn)); if (plY.length || plX.length) { out.reference.profit_loss = []; if (plY.length) { plG = stmtGroup(plY, PL_COLS); out.reference.profit_loss = out.reference.profit_loss.concat(stmtChecks("profit and loss (month and year to date)", plG, PL_COLS, "pl")); } if (plX.length) { const cmpName = uniq(plX.map((p) => str(p.data.comparisonColumn))).join(", "); plC = stmtGroup(plX, PL_COLS); out.reference.profit_loss = out.reference.profit_loss.concat(stmtChecks("profit and loss comparison (" + cmpName + ")", plC, PL_COLS, "plc")); out.cross.push(mkCheck("ok", "x.comparison", "Pages with a \"" + cmpName + "\" column are a comparison month, not year to date", plX.map((p) => p.label).join(", ") + ": the second figure column on these pages is " + cmpName + ", so it is never used as year to date in any check. Their month column is compared with the year-to-date pages instead.", { pages: plX.map((p) => p.ref) })); if (plG) { const ma = monthAgreement(plG, plC); out.reference.profit_loss.push(ma.diffs.length ? mkCheck("warn", "plc.month", "The month figures differ between the year-to-date and comparison pages", ma.diffs.slice(0, 10).map((d) => d.text).join("\n") + "\nThese are two readings of the same month; one of them has a misread figure.", { keys: uniq(ma.diffs.map((d) => d.l.desc || d.l.acct)), lines: ma.diffs.map((d) => ({ page: d.l.page.ref, index: d.l.index, description: d.l.desc })), pages: uniq(ma.diffs.map((d) => d.l.page.ref)) }) : mkCheck("ok", "plc.month", "The month figures agree on the year-to-date and comparison pages", ma.compared + " figures compared (two independent readings of the same month).")); } } } // — against the trial balance const tb = tbMerge(ctx, indexDecisions(tbDec || [])).merged.filter((m) => m.kind === "account"); if (!tb.length) return out; const T = new Map(); tb.forEach((m) => T.set(m.acct, { ytd: cents(m.vals.ytd), mtd: cents(m.vals.mtd), name: m.vals.name })); const tbPL = sum(Array.from(T.entries()).filter(([a]) => !isBsAcct(a)).map(([, v]) => v.ytd)); if (bsG) { const used = new Set(), unmatched = []; let matched = 0; bsG.lines.filter((l) => !l.computed && l.vals.ytd != null && !/^PROFIT\s*\(?LOSS/i.test(l.desc)).forEach((l) => { const a = l.acct, v = cents(l.vals.ytd); const keys = Array.from(T.keys()), fam = keys.filter((k) => k.slice(0, 3) === a.slice(0, 3)), exact = T.has(a) ? [a] : [], ge = fam.filter((k) => k >= a); let hit = null; for (const s of [exact, fam, ge]) { if (!s.length) continue; const t = sum(s.map((k) => T.get(k).ytd)); if (Math.abs(t - v) <= 1 || Math.abs(-t - v) <= 1) { hit = s; break; } } if (hit) { hit.forEach((k) => used.add(k)); matched++; } else { exact.forEach((k) => used.add(k)); if (v !== 0 || exact.length) unmatched.push(l); } }); const omitted = Array.from(T.entries()).filter(([a, v]) => isBsAcct(a) && v.ytd !== 0 && !used.has(a)).map(([a, v]) => ({ acct: a, name: v.name, ytd: v.ytd })); if (unmatched.length) out.cross.push(mkCheck("warn", "x.bsLines", plural(unmatched.length, "balance sheet line") + " does not match the trial balance", unmatched.slice(0, 10).map((l) => l.acct + " " + l.desc + ": balance sheet " + usd(cents(l.vals.ytd)) + " — no trial balance account (or group of accounts) has that balance").join("\n") + "\nOne of the two was probably misread.", { keys: unmatched.map((l) => l.acct), lines: unmatched.map((l) => ({ page: l.page.ref, index: l.index, description: l.desc })), pages: uniq(unmatched.map((l) => l.page.ref)), cross: true })); else out.cross.push(mkCheck("ok", "x.bsLines", "All " + matched + " balance sheet lines match the trial balance", "Each line equals a trial balance account (or a family of accounts) with the sign flipped for liabilities and equity.", { cross: true })); const find = (re) => bsG.lines.find((l) => l.computed && re.test(l.desc) && l.vals.ytd != null); const ta = find(/^TOTAL\s+ASSET/i), tle = find(/^TOTAL\s+LIAB.*EQUITY/i); const omSum = sum(omitted.map((o) => o.ytd)); if (ta && tle) { const gap = cents(tle.vals.ytd) - cents(ta.vals.ytd); const names = omitted.map((o) => o.acct + " " + o.name + " (" + usd(o.ytd) + ")").join(", "); if (Math.abs(gap) <= 1 && !omitted.length) out.cross.push(mkCheck("ok", "x.bsTotals", "The balance sheet balances (TOTAL ASSET = TOTAL LIAB. & EQUITY)", "", { cross: true })); else if (Math.abs(gap + omSum) <= 1 || Math.abs(gap - omSum) <= 1) out.cross.push(mkCheck("ok", "x.bsTotals", "The printed balance sheet is out of balance by " + usd(Math.abs(gap)) + ", and the trial balance explains it", "TOTAL ASSET " + usd(cents(ta.vals.ytd)) + " vs TOTAL LIAB. & EQUITY " + usd(cents(tle.vals.ytd)) + ". The old balance sheet layout leaves out these trial balance accounts, which add up to exactly the difference: " + names + ". (Informational — the trial balance itself balances.)", { keys: omitted.map((o) => o.acct), cross: true, informational: true })); else out.cross.push(mkCheck("warn", "x.bsTotals", "The printed balance sheet is out of balance by " + usd(Math.abs(gap)) + " and the trial balance does not explain it", "TOTAL ASSET " + usd(cents(ta.vals.ytd)) + " vs TOTAL LIAB. & EQUITY " + usd(cents(tle.vals.ytd)) + ". Trial balance accounts the balance sheet does not show: " + (names || "none") + " (net " + usd(omSum) + "). A balance sheet line or a trial balance amount was probably misread.", { keys: omitted.map((o) => o.acct), cross: true })); } } if (plG) { const ni = plG.lines.find((l) => l.computed && /^NET INCOME/i.test(l.desc) && l.vals.ytdGross != null); if (ni) { const claimed = new Set(); const keys = Array.from(T.keys()); const fam = (p3) => keys.filter((k) => k.slice(0, 3) === p3); plG.lines.filter((l) => !l.computed).forEach((l) => { const a = l.acct, padded = a.length === 2 ? "0" + a : a; fam(padded.slice(0, 3)).forEach((k) => claimed.add(k)); if (/^4\d\d$/.test(a)) fam(String(+a + 200)).forEach((k) => claimed.add(k)); if (a === "807") fam("851").forEach((k) => claimed.add(k)); }); // a computed line that equals minus a whole group of accounts (e.g. OTHER INCOME & DEDUCT = the 900s) covers that group [0, 400, 600, 800, 900].forEach((b) => { const g = keys.filter((k) => bandOf(k) === b); if (!g.length) return; const gs = sum(g.map((k) => T.get(k).ytd)); if (gs !== 0 && plG.lines.some((l) => l.computed && l.vals.ytdGross != null && cents(l.vals.ytdGross) === -gs)) g.forEach((k) => claimed.add(k)); }); const omitted = keys.filter((k) => !isBsAcct(k) && !claimed.has(k) && (T.get(k).ytd !== 0 || T.get(k).mtd !== 0)).map((k) => ({ acct: k, name: T.get(k).name, ytd: T.get(k).ytd })); const omSum = sum(omitted.map((o) => o.ytd)); const printed = cents(ni.vals.ytdGross), expected = -(tbPL - omSum), diff = printed - (-tbPL); const names = omitted.map((o) => o.acct + " " + o.name + " (" + usd(o.ytd) + ")").join(", "); if (Math.abs(diff) <= 1) out.cross.push(mkCheck("ok", "x.plNet", "The profit and loss NET INCOME " + usd(printed) + " equals the trial balance's P&L accounts", "Year to date, " + ni.page.label + ".", { cross: true })); else if (Math.abs(printed - expected) <= 1) out.cross.push(mkCheck("ok", "x.plNet", "The printed NET INCOME (" + usd(printed) + ") differs from the trial balance (" + usd(-tbPL) + ") by " + usd(Math.abs(diff)) + ", and the trial balance explains it", "The old profit and loss layout leaves out these trial balance accounts, which add up to exactly that difference: " + names + ". (Informational.)", { keys: omitted.map((o) => o.acct), cross: true, informational: true })); else out.cross.push(mkCheck("warn", "x.plNet", "The printed NET INCOME (" + usd(printed) + ") differs from the trial balance (" + usd(-tbPL) + ") and the layout does not explain it", "Trial balance P&L accounts the statement does not show: " + (names || "none") + " (net " + usd(omSum) + "). A profit and loss line or a trial balance amount was probably misread.", { keys: omitted.map((o) => o.acct), cross: true })); } } return out; } // ═══════════════════════ 20. the packet ═══════════════════════ const FAIL_MSG = { not_configured: "AI reading is not set up yet.", no_consent: "AI reading has not been approved by an administrator yet.", forbidden: "Your role cannot use AI reading.", too_large: "The page picture was too large.", rate_limited: "Too many pages were sent at once.", daily_limit: "The daily page limit was reached.", auth_failed: "The AI key was rejected.", no_credit: "The AI account is out of credit.", refused: "The AI declined to read this page.", truncated: "The page was too dense to read in one go.", unreadable: "The page could not be read.", upstream: "The AI service did not answer.", }; const RETRY_OK = { rate_limited: 1, upstream: 1, refused: 1, truncated: 1, unreadable: 1 }; function problemsOf(ctx) { const out = []; ctx.pages.forEach((p) => { if (!p.ok) out.push({ page: p.ref, level: "error", code: p.code || "failed", title: "This page could not be read", detail: p.error || FAIL_MSG[p.code] || "The reader did not return this page.", retryable: !!RETRY_OK[p.code] }); else if (!p.data) out.push({ page: p.ref, level: "error", code: "empty", title: "This page came back empty", detail: "The reader returned no data for " + p.label + ". Read it again.", retryable: true }); else if (p.pageType === "other") out.push({ page: p.ref, level: "warn", code: "other", title: "This page was not recognised", detail: p.label + " is not one of the old-system reports K2B can use" + (p.rawType && p.rawType !== "other" ? " (the reader called it \"" + p.rawType + "\")" : "") + ". Check the page; read it again if it should be one of the others.", retryable: true }); if (p.ok && p.data && p.legibility === "poor") out.push({ page: p.ref, level: "warn", code: "poor_legibility", title: "This page is hard to read", detail: p.label + " was read with poor legibility. Check every number against the printout, or scan it again at a higher quality.", retryable: true }); }); // pages of an importable type whose data does not have the expected shape const need = { trial_balance: "rows", chart_of_accounts: "accounts", aged_schedule: "rows", vehicle_master: "rows", journal_document: "lines", ro_history: "rows", parts_inventory: "rows", warranty_claims: "rows" }; // RC25 (imp); RC33 (svi): + warranty claims ctx.pages.forEach((p) => { if (!p.ok || !p.data) return; const k = need[p.pageType]; if (k && !Array.isArray(p.data[k])) out.push({ page: p.ref, level: "error", code: "bad_shape", title: "This page's data is incomplete", detail: p.label + " was recognised as a " + PAGE_TYPES[p.pageType].label.toLowerCase() + " but no " + k + " came back. Read it again.", retryable: true }); }); return out; } function pageNotesOf(ctx) { const out = []; ctx.pages.forEach((p) => { if (!p.ok) return; if (p.legibility === "poor") out.push({ page: p.ref, pageType: p.pageType, level: "warn", text: "Poor legibility: check the numbers on this page against the printout." }); p.notes.forEach((t) => out.push({ page: p.ref, pageType: p.pageType, level: hardNote(t) || /^Could not read /i.test(t) ? "warn" : "info", text: t })); }); return out; } // ═══════════════════════ RC25 (imp): repair-order history pages and parts inventory pages ═══════════════════════ // Simple lists (no sort-order voting): every row of every page in printed order, keyed by RO number / part number; the same key // on two pages is one record when the figures agree (a page scanned twice) and an error to look at when they differ; a page that // prints totals is checked against its own rows. function buildRoHistory(ctx, decList) { const pages = ctx.of("ro_history").filter((p) => Array.isArray(p.data.rows)).sort(cmpPage); const recs = [], byNo = new Map(), conflicts = [], repeats = [], pageSums = []; pages.forEach((p) => { let cnt = 0; const sums = { parts: 0, labor: 0, sublet: 0, tax: 0, total: 0 }; p.data.rows.forEach((r) => { r = r || {}; const no = acctN(r.roNo).replace(/^#/, ""); const stW = up(r.status); const flag = /^[IOHW]$/.test(up(r.payType)) ? up(r.payType) : (stW === "OPEN" ? "O" : up(r.payType)); // RC33 (svi, map gap 1): the account and the NAME are two things — a page that prints only a name in the customer column gives the name, never the account const custTxt = str(r.customer), nameTxt = str(r.customerName); const looksName = (t) => /[A-Za-z]{2}/.test(t) && /[\s,]/.test(t) && !/^(INTERNAL|INT|WARRANTY|WARR|WTY|VOID|SHOP|POLICY|DEALER|BODY)(?![A-Z])/i.test(t); const acctTxt = nameTxt || !looksName(custTxt) ? custTxt : ""; const rec = { roNo: no, flag, date: dateCell(r.date), customerAccount: acctTxt, customerName: nameTxt || (looksName(custTxt) ? custTxt : ""), custStreet: str(r.address), custCity: str(r.city), custState: str(r.state), custPostal: str(r.zip), custPhone: str(r.phone), vin: acctN(r.vin), stock: acctN(r.stock), vehicle: str(r.vehicle), odometer: numOrNull(r.miles), tech: str(r.tech), advisor: str(r.advisor), partsSale: numOrNull(r.parts), partsCost: numOrNull(r.partsCost), laborSale: numOrNull(r.labor), laborCost: numOrNull(r.laborCost), subletSale: numOrNull(r.sublet), subletCost: numOrNull(r.subletCost), supplies: numOrNull(r.supplies), salesTax: numOrNull(r.tax), total: numOrNull(r.total), customerPay: numOrNull(r.customerPay), otherPay: numOrNull(r.warrantyInternal), status: stW, lineText: str(r.lines) }; // RC25 (imp) fix round (attack L3): a date that is not a date, or an amount with more than two decimals, is never blanked or // rounded on the way to the wizard — the printed text is kept, so the import refuses that row with the reason const bad = []; if (str(r.date) && !rec.date) bad.push({ k: "date", raw: str(r.date), why: "the date \"" + str(r.date) + "\" is not a date" }); [["partsSale", r.parts], ["laborSale", r.labor], ["subletSale", r.sublet], ["supplies", r.supplies], ["salesTax", r.tax], ["total", r.total], ["customerPay", r.customerPay], ["otherPay", r.warrantyInternal], ["laborCost", r.laborCost], ["partsCost", r.partsCost], ["subletCost", r.subletCost]].forEach(([k, raw]) => { const v = rec[k]; if (isNum(v) && Math.abs(v * 100 - Math.round(v * 100)) > 1e-6) bad.push({ k, raw: String(raw), why: "the amount " + String(raw) + " has more than two decimals" }); }); if (bad.length) rec._bad = bad; cnt++; ["parts", "labor", "sublet", "tax", "total"].forEach((k) => { const v = rec[{ parts: "partsSale", labor: "laborSale", sublet: "subletSale", tax: "salesTax", total: "total" }[k]]; if (isNum(v)) sums[k] += cents(v); }); if (!no) { recs.push(Object.assign(rec, { _page: p.ref, _i: recs.length })); return; } const first = byNo.get(no); if (first) { const same = ["total", "laborSale", "partsSale", "date", "customerAccount"].every((k) => String(first[k] == null ? "" : first[k]) === String(rec[k] == null ? "" : rec[k])); if (same) repeats.push({ no, page: p.ref, firstPage: first._page }); else conflicts.push({ no, page: p.ref, firstPage: first._page, i: first._i }); return; } byNo.set(no, Object.assign(rec, { _page: p.ref, _i: recs.length })); recs.push(rec); }); const t = p.data.totals || {}; if (isNum(t.count) || isNum(t.total)) pageSums.push({ p, cnt, sums, printed: t }); }); const col = new Collector(recs.length); recs.forEach((r, i) => { if (!r.roNo) col.mark(i, "error", "no RO number"); }); const noNo = recs.map((r, i) => (!r.roNo ? i : -1)).filter((i) => i >= 0); if (noNo.length) col.add(mkCheck("error", "ro.noNumber", plural(noNo.length, "row") + " without a repair-order number", "Every row needs the RO number printed on the page; these rows cannot be imported.", { rows: noNo })); if (conflicts.length) col.add(mkCheck("error", "ro.conflict", plural(conflicts.length, "repair order") + " read twice with different figures", conflicts.slice(0, 10).map((c) => "RO " + c.no + ": " + refLabel(c.firstPage) + " vs " + refLabel(c.page)).join("\n") + " — the first reading is used; check the pages.", { rows: conflicts.map((c) => c.i) })); if (repeats.length) col.add(mkCheck("ok", "ro.repeats", plural(repeats.length, "repair order") + " printed on more than one page — counted once", repeats.slice(0, 8).map((c) => "RO " + c.no).join(", "))); pageSums.forEach((ps) => { const bad = []; if (isNum(ps.printed.count) && ps.printed.count !== ps.cnt) bad.push("count printed " + ps.printed.count + ", rows read " + ps.cnt); ["parts", "labor", "sublet", "tax", "total"].forEach((k) => { if (isNum(ps.printed[k]) && cents(ps.printed[k]) !== ps.sums[k]) bad.push(k + " printed " + usd(cents(ps.printed[k])) + ", rows add to " + usd(ps.sums[k])); }); if (bad.length) col.add(mkCheck("error", "ro.totals." + refKey(ps.p.ref), "The rows of " + ps.p.label + " do not add up to its printed totals", bad.join("; ") + ". A row was probably misread — check the page.", { pages: [ps.p.ref] })); else col.add(mkCheck("ok", "ro.totals." + refKey(ps.p.ref), ps.p.label + ": the rows add up to the printed totals" + (isNum(ps.printed.total) ? " (" + usd(cents(ps.printed.total)) + ")" : ""), "")); }); const lg = legibilityCheck(pages); if (lg) col.add(lg); const tab = ctx.table("roHistory", recs, ["roNo", "flag", "date", "total"]); const labelOf = {}; Object.keys(tab.keyOf).forEach((h) => { labelOf[tab.keyOf[h]] = h; }); const badRows = []; recs.forEach((r, i) => { if (!r._bad) return; badRows.push(i); r._bad.forEach((b) => { if (labelOf[b.k]) tab.rows[i][labelOf[b.k]] = b.raw; }); col.mark(i, "error", r._bad.map((b) => b.why).join("; ")); }); if (badRows.length) col.add(mkCheck("error", "ro.unreadable", plural(badRows.length, "row") + " with a date or an amount that cannot be right", badRows.slice(0, 10).map((i) => "RO " + (recs[i].roNo || "?") + ": " + recs[i]._bad.map((b) => b.why).join("; ")).join("\n") + " — these rows are refused by the import as printed; check the page.", { rows: badRows })); const spec = { id: "roHistory", pageType: "ro_history", dest: "roHistory", sheetName: "Repair-order history", headers: tab.headers, rows: tab.rows, records: recs, keyOf: tab.keyOf, rowKeys: recs.map((r) => r.roNo), pages: pages.map((p) => p.ref), notes: ["Closed repair orders become history (no journal entry); open ones go on the service board. Matched by the RO number exactly as printed."], decisions: decList, printed: { pages: pages.length, ros: recs.length }, importOpts: {}, choices: [], fixes: [] }; return finish(ctx, spec, col, (d) => buildRoHistory(ctx, d)); } /* ═══ RC33 (svi): an A/R AGING printed in age columns (current / 31-60 / 61-90 / 91-120 / over 120 / total) → the A/R aging list (dest arAging, one row per customer), and the open warranty claims → the warranty claims list. Each row is checked against the printed total of its own page; every row stays "read from a scanned page (AI) — check" until approved. ═══ */ const AGING_COLS = ["current", "d31_60", "d61_90", "d91_120", "over120"]; const isAgingPage = (p) => Array.isArray(p.data.rows) && p.data.rows.some((r) => r && AGING_COLS.concat(["total"]).some((k) => !emptyCell(r[k]))); function buildAging(ctx, g, gi, decList) { const pages = g.pages.slice().sort(cmpPage); const recs = [], seen = new Map(), conflicts = []; pages.forEach((p) => p.data.rows.forEach((r) => { r = r || {}; const c = up(r.control).replace(/\s+/g, ""); const rec = { control: c, name: str(r.description), current: numOrNull(r.current), d31_60: numOrNull(r.d31_60), d61_90: numOrNull(r.d61_90), d91_120: numOrNull(r.d91_120), over120: numOrNull(r.over120), total: numOrNull(r.total), gl: g.gl || "220", asOf: "" }; if (c && seen.has(c)) { const f = seen.get(c); if (cents(f.total) !== cents(rec.total)) conflicts.push(c); return; } if (c) seen.set(c, rec); recs.push(Object.assign(rec, { _page: p.ref })); })); const col = new Collector(recs.length); const sumOf = (r) => AGING_COLS.reduce((t, k) => t + (isNum(r[k]) ? cents(r[k]) : 0), 0); const bad = []; recs.forEach((r, i) => { if (!r.control) col.mark(i, "error", "no customer / account number"); if (isNum(r.total) && sumOf(r) !== cents(r.total)) { bad.push(i); col.mark(i, "error", "the age columns add up to " + usd(sumOf(r)) + " but the total says " + usd(cents(r.total))); } }); if (bad.length) col.add(mkCheck("error", "aging.rowTotal", plural(bad.length, "customer") + " whose age columns do not add up to the total", bad.slice(0, 10).map((i) => recs[i].control).join(", ") + " — a figure was probably misread; check the page.", { rows: bad })); if (conflicts.length) col.add(mkCheck("error", "aging.conflict", plural(conflicts.length, "customer") + " read twice with different totals", conflicts.slice(0, 10).join(", "))); const dateP = pages.map((p) => parseDate(p.data.printedDate)).filter(Boolean); if (dateP.length) recs.forEach((r) => { r.asOf = mdy(dateP[0]); }); const lg = legibilityCheck(pages); if (lg) col.add(lg); const tab = ctx.table("arAging", recs, ["control", "total"]); const label = "A/R aging " + (g.gl || "220"); const spec = { id: "aging:" + (g.gl || ("t" + gi)), pageType: "aged_schedule", dest: "arAging", sheetName: label, headers: tab.headers, rows: tab.rows, records: recs, keyOf: tab.keyOf, rowKeys: recs.map((r) => r.control), pages: pages.map((p) => p.ref), notes: ["One row per customer: the age buckets become the customer's A/R lines; tied to " + (g.gl || "220") + " on the import review. Nothing is posted at import."], decisions: decList, printed: { glAcct: g.gl, rows: recs.length }, importOpts: Object.assign({}, dateP.length ? { asOf: isoOf(dateP[0]) } : {}, g.gl ? { glAcct: g.gl } : {}), choices: [], fixes: [] }; return finish(ctx, spec, col, (d) => buildAging(ctx, g, gi, d)); } function buildWarrantyClaims(ctx, decList) { const pages = ctx.of("warranty_claims").filter((p) => Array.isArray(p.data.rows)).sort(cmpPage); const recs = [], seen = new Map(); pages.forEach((p) => p.data.rows.forEach((r) => { r = r || {}; const c = up(r.claim).replace(/\s+/g, ""); const rec = { claim: c, ro: acctN(r.roNo), date: dateCell(r.date), amount: numOrNull(r.amount), paid: numOrNull(r.paid), status: str(r.status) }; if (c && seen.has(c)) return; if (c) seen.set(c, rec); recs.push(Object.assign(rec, { _page: p.ref })); })); const col = new Collector(recs.length); recs.forEach((r, i) => { if (!r.claim) col.mark(i, "error", "no claim number"); if (r.amount == null) col.mark(i, "error", "no readable claim amount"); }); pages.forEach((p) => { const t = p.data.totals || {}; if (isNum(t.amount)) { const sAll = recs.filter((r) => r._page === p.ref).reduce((x, r) => x + (isNum(r.amount) ? cents(r.amount) : 0), 0); col.add(mkCheck(sAll === cents(t.amount) ? "ok" : "error", "claims.totals." + refKey(p.ref), sAll === cents(t.amount) ? p.label + ": the claims add up to the printed total (" + usd(sAll) + ")" : "The claims of " + p.label + " add up to " + usd(sAll) + " but the page prints " + usd(cents(t.amount)), "")); } }); const lg = legibilityCheck(pages); if (lg) col.add(lg); const tab = ctx.table("warrantyClaims", recs, ["claim", "ro", "amount"]); const spec = { id: "warrantyClaims", pageType: "warranty_claims", dest: "warrantyClaims", sheetName: "Warranty claims", headers: tab.headers, rows: tab.rows, records: recs, keyOf: tab.keyOf, rowKeys: recs.map((r) => r.claim), pages: pages.map((p) => p.ref), notes: ["The open claims are tied to 263 WARRANTY CLAIMS on the import review. Nothing is posted at import."], decisions: decList, printed: { pages: pages.length, claims: recs.length }, importOpts: {}, choices: [], fixes: [] }; return finish(ctx, spec, col, (d) => buildWarrantyClaims(ctx, d)); } function buildPartsInventory(ctx, decList) { const pages = ctx.of("parts_inventory").filter((p) => Array.isArray(p.data.rows)).sort(cmpPage); const recs = [], byNo = new Map(), conflicts = [], repeats = [], pageSums = []; pages.forEach((p) => { let cnt = 0, value = 0; p.data.rows.forEach((r) => { r = r || {}; const no = acctN(r.part); const rec = { part: no, desc: str(r.description), bin: str(r.bin), onHand: numOrNull(r.onHand), cost: numOrNull(r.cost), list: numOrNull(r.list), source: str(r.source), core: numOrNull(r.core) }; cnt++; if (isNum(rec.onHand) && isNum(rec.cost)) value += Math.round(rec.onHand * cents(rec.cost)); if (!no) { recs.push(Object.assign(rec, { _page: p.ref, _i: recs.length })); return; } const first = byNo.get(no); if (first) { const same = ["onHand", "cost", "bin"].every((k) => String(first[k] == null ? "" : first[k]) === String(rec[k] == null ? "" : rec[k])); if (same) repeats.push({ no, page: p.ref }); else conflicts.push({ no, page: p.ref, firstPage: first._page, i: first._i }); return; } byNo.set(no, Object.assign(rec, { _page: p.ref, _i: recs.length })); recs.push(rec); }); const t = p.data.totals || {}; if (isNum(t.count) || isNum(t.onHandValue)) pageSums.push({ p, cnt, value, printed: t }); }); const col = new Collector(recs.length); const noNo = recs.map((r, i) => (!r.part ? i : -1)).filter((i) => i >= 0); noNo.forEach((i) => col.mark(i, "error", "no part number")); if (noNo.length) col.add(mkCheck("error", "parts.noNumber", plural(noNo.length, "row") + " without a part number", "Every row needs the part number; these rows cannot be imported.", { rows: noNo })); if (conflicts.length) col.add(mkCheck("error", "parts.conflict", plural(conflicts.length, "part") + " read twice with different figures", conflicts.slice(0, 10).map((c) => c.no + ": " + refLabel(c.firstPage) + " vs " + refLabel(c.page)).join("\n") + " — the first reading is used; check the pages.", { rows: conflicts.map((c) => c.i) })); if (repeats.length) col.add(mkCheck("ok", "parts.repeats", plural(repeats.length, "part") + " printed on more than one page — counted once", repeats.slice(0, 8).map((c) => c.no).join(", "))); pageSums.forEach((ps) => { const bad = []; if (isNum(ps.printed.count) && ps.printed.count !== ps.cnt) bad.push("count printed " + ps.printed.count + ", rows read " + ps.cnt); if (isNum(ps.printed.onHandValue) && cents(ps.printed.onHandValue) !== ps.value) bad.push("value on hand printed " + usd(cents(ps.printed.onHandValue)) + ", rows (on hand × cost) add to " + usd(ps.value)); if (bad.length) col.add(mkCheck("error", "parts.totals." + refKey(ps.p.ref), "The rows of " + ps.p.label + " do not add up to its printed totals", bad.join("; ") + ". A row was probably misread — check the page.", { pages: [ps.p.ref] })); else col.add(mkCheck("ok", "parts.totals." + refKey(ps.p.ref), ps.p.label + ": the rows add up to the printed totals" + (isNum(ps.printed.onHandValue) ? " (" + usd(cents(ps.printed.onHandValue)) + " on hand)" : ""), "")); }); const lg = legibilityCheck(pages); if (lg) col.add(lg); const tab = ctx.table("partsMaster", recs, ["part", "onHand", "cost"]); const spec = { id: "partsMaster", pageType: "parts_inventory", dest: "partsMaster", sheetName: "Parts inventory", headers: tab.headers, rows: tab.rows, records: recs, keyOf: tab.keyOf, rowKeys: recs.map((r) => r.part), pages: pages.map((p) => p.ref), notes: ["Matched by part number; on-hand counts are set from the page when the import option is ticked. Nothing is posted — tie the value on hand to the ledger on Reconcile inventory to GL."], decisions: decList, printed: { pages: pages.length, parts: recs.length }, importOpts: { applyOnHand: true, updatePrices: true }, choices: [], fixes: [] }; return finish(ctx, spec, col, (d) => buildPartsInventory(ctx, d)); } function buildDatasetsImpl(results, opts, cfg) { opts = opts || {}; const ctx = makeCtx(results, opts, cfg || {}); const decs = opts.decisions || {}; const datasets = [], problems = problemsOf(ctx); const guard = (what, fn) => { try { const d = fn(); if (d) datasets.push(d); } catch (e) { problems.push({ page: null, level: "error", code: "internal", title: "K2B could not process the " + what, detail: "Something unexpected was in the readings of the " + what + " (" + (e && e.message ? e.message : e) + "). Read those pages again.", retryable: true }); } }; if (ctx.of("chart_of_accounts").some((p) => Array.isArray(p.data.accounts))) guard("chart of accounts", () => buildChart(ctx, decs.glChart || [])); if (ctx.of("trial_balance").some((p) => Array.isArray(p.data.rows))) guard("trial balance", () => buildTrial(ctx, decs.glTrial || [])); if (ctx.of("vehicle_master").some((p) => Array.isArray(p.data.rows))) guard("vehicle master", () => buildVehicles(ctx, decs.inventory || [])); schedGroups(ctx).forEach((g, gi) => (g.pages.some(isAgingPage) ? guard("A/R aging " + (g.gl || "?"), () => buildAging(ctx, g, gi, decs["aging:" + (g.gl || ("t" + gi))] || [])) : guard("schedule " + (g.gl || "?"), () => buildSchedule(ctx, gi, decs["schedule:" + (g.gl || ("t" + gi))] || [])))); // RC33 (svi): an aging in age columns is the A/R aging list if (ctx.of("deal_recap").length) guard("deal recaps", () => buildDeals(ctx, decs.deals || [])); if (ctx.of("journal_document").some((p) => Array.isArray(p.data.lines))) guard("journal documents", () => buildJournals(ctx, decs.glJournal || [])); if (ctx.of("ro_history").some((p) => Array.isArray(p.data.rows))) guard("repair-order history", () => buildRoHistory(ctx, decs.roHistory || [])); // RC25 (imp) if (ctx.of("parts_inventory").some((p) => Array.isArray(p.data.rows))) guard("parts inventory", () => buildPartsInventory(ctx, decs.partsMaster || [])); // RC25 (imp) if (ctx.of("warranty_claims").some((p) => Array.isArray(p.data.rows))) guard("warranty claims", () => buildWarrantyClaims(ctx, decs.warrantyClaims || [])); // RC33 (svi) // reference-only pages (+ the checks that make them useful), blank pages const reference = []; let st = { reference: {}, cross: [] }; try { st = statements(ctx, decs.glTrial || []); } catch (e) { problems.push({ page: null, level: "error", code: "internal", title: "K2B could not compare the statements", detail: String(e && e.message || e), retryable: false }); } const wsAll = (() => { try { const dc = dealCore(ctx, indexDecisions(decs.deals || [])).merged; return wsChecks(ctx, dc.map((m) => ({ no: m.key.slice(2), v: m.vals }))); } catch (e) { return []; } })(); Object.keys(PAGE_TYPES).forEach((t) => { const info = PAGE_TYPES[t]; if (info.importable || t === "blank") return; const ps = ctx.pages.filter((p) => p.ok && p.pageType === t); if (!ps.length) return; let checks = st.reference[t] || []; if (t === "posting_worksheet") checks = wsAll; const details = ps.map((p) => ({ page: p.ref, summary: p.data && typeof p.data.summary === "string" ? p.data.summary : "" })).filter((d) => d.summary); let note = ""; if (t === "balance_sheet" || t === "profit_loss") note = "Not imported. Used to double-check the trial balance (see the cross-checks)."; if (t === "posting_worksheet") note = "Not imported. Each worksheet is checked against its deal recap."; reference.push({ pageType: t, label: info.label, why: info.why, note, pages: ps.sort(cmpPage).map((p) => p.ref), count: ps.length, checks, details }); }); const blankPages = ctx.pages.filter((p) => p.ok && p.pageType === "blank").sort(cmpPage).map((p) => p.ref); // cross-report checks (some are also attached to their dataset) const cross = []; const pushAll = (arr) => arr.forEach((c) => cross.push(c)); const tbDs = datasets.find((d) => d.id === "glTrial"); if (tbDs) tbDs.checks.filter((c) => c.id === "x.profit" || c.id === "tb.asOf" && c.level !== "ok").forEach((c) => cross.push(c)); pushAll(st.cross); pushAll(wsAll.filter((c) => c.id === "ws.recap" || c.level !== "ok")); const jDs = datasets.find((d) => d.id === "glJournal"); if (jDs) jDs.checks.filter((c) => c.id === "journal.beforeAsOf" || c.id === "journal.recap").forEach((c) => cross.push(c)); const dt = packetDates(ctx); if (dt.conflict) cross.push(mkCheck("warn", "x.asof", "The statements show different dates", dt.conflict)); // overall const all = []; datasets.forEach((d) => d.checks.forEach((c) => all.push(c))); reference.forEach((r) => r.checks.forEach((c) => all.push(c))); cross.forEach((c) => all.push(c)); const overall = { status: "ok", errors: 0, warnings: 0, problems: problems.filter((p) => p.level === "error").length, datasets: datasets.length, rows: sum(datasets.map((d) => d.rowCount)), pages: ctx.pages.length }; all.forEach((c) => { if (c.level === "error") overall.errors++; else if (c.level === "warn") overall.warnings++; }); if (overall.errors || overall.problems) overall.status = "error"; else if (overall.warnings) overall.status = "warn"; const pageTypes = {}; ctx.pages.forEach((p) => { (pageTypes[p.ok ? p.pageType : "failed"] = pageTypes[p.ok ? p.pageType : "failed"] || []).push(p.ref); }); return { datasets, reference, blankPages, problems, crossChecks: cross, pageNotes: pageNotesOf(ctx), overall, pageTypes, asOf: dt.asOf ? { date: mdy(dt.asOf), iso: isoOf(dt.asOf), source: dt.source } : null }; } // ═══════════════════════ 21. fixes ═══════════════════════ function parseFix(id) { const parts = String(id).split("|").map((x) => { try { return decodeURIComponent(x); } catch (e) { return x; } }); if (parts[0] === "choose" && parts.length === 4) return { t: "choose", key: parts[1], field: parts[2], pick: parts[3] }; if (parts[0] === "acct" && parts.length === 3 && parts[1] && parts[2]) return { t: "acct", from: acctN(parts[1]), to: acctN(parts[2]) }; if (parts[0] === "date" && parts.length === 3) { const d = parseDate(parts[2]); if (d) return { t: "date", doc: parts[1], date: mdy(d) }; throw new Error("\"" + parts[2] + "\" is not a date (use MM/DD/YYYY)."); } throw new Error("Unknown fix: " + id); } function applyFix(dataset, fixId) { if (!dataset || typeof dataset._rebuild !== "function") throw new Error("This dataset cannot be changed: it was not built by OldReportsData.buildDatasets."); const dec = parseFix(fixId); if (dec.t === "choose") { const ch = (dataset.choices || []).find((c) => c.rowKey === dec.key && c.field === dec.field); if (!ch || !ch.options.some((o) => o.vkey === dec.pick)) throw new Error("That choice is not one of the readings of this value."); } const same = (x) => x.t === dec.t && (dec.t === "choose" ? x.key === dec.key && x.field === dec.field : dec.t === "acct" ? x.from === dec.from : x.doc === dec.doc); return dataset._rebuild((dataset.decisions || []).filter((x) => !same(x)).concat([dec])); } // ═══════════════════════ 22. CSV, cost ═══════════════════════ function csvOf(ds) { const q = (v) => { let s = v == null ? "" : String(v); if (/^[=+@]/.test(s) || /^-(?![\d.])/.test(s)) s = "'" + s; // never let a cell be read as a formula in Excel return '"' + s.replace(/"/g, '""') + '"'; }; const lines = [ds.headers.map(q).join(",")]; ds.rows.forEach((r) => lines.push(ds.headers.map((h) => q(r[h])).join(","))); return "\uFEFF" + lines.join("\r\n") + "\r\n"; } function estimateCost(pageCount, pricing) { const n = Math.max(0, Math.floor(+pageCount || 0)); pricing = pricing || {}; const inR = +pricing.inputPerMTok, outR = +pricing.outputPerMTok, rated = isFinite(inR) && isFinite(outR) && inR > 0 && outR > 0; let typical = isNum(pricing.estPerPageUsd) && pricing.estPerPageUsd > 0 ? pricing.estPerPageUsd : (rated ? (3500 * inR + 350 * inR + 7000 * outR) / 1e6 : 0); if (!typical) return { pages: n, low: 0, high: 0, typical: 0, text: n ? "The price per page is shown once AI reading is set up." : "No pages to read." }; let lowP = rated ? (3850 * inR + 1500 * outR) / 1e6 : typical * 0.3, highP = rated ? (3850 * inR + 16000 * outR) / 1e6 : typical * 2.3; lowP = Math.min(lowP, typical); highP = Math.max(highP, typical); const r2 = (x) => Math.round(x * 100) / 100; const low = r2(n * lowP), high = r2(n * highP), typ = r2(n * typical); const d = (raw) => (raw > 0 && raw < 0.005 ? "under $0.01" : "$" + (Math.round(raw * 100) / 100).toFixed(2)); const text = n === 0 ? "No pages to read." : "About " + d(n * typical) + " for " + plural(n, "page") + " (between " + d(n * lowP) + " and " + d(n * highP) + " depending on how dense the pages are)" + (pricing.model ? " with " + pricing.model : "") + ". Blank pages cost the least; this is an estimate until the pages are read."; return { pages: n, low, high, typical: typ, perPage: r2(typical * 1000) / 1000, text }; } // ═══════════════════════ 23. the public object ═══════════════════════ function create(cfg) { cfg = cfg || {}; const bound = { dests: normalizeDests(cfg.destFields) }; return { PAGE_TYPES, IMPORT_ORDER, buildDatasets: (results, opts) => buildDatasetsImpl(results, opts, bound), applyFix, csvOf, estimateCost, create, lookAlikes, version: "1.0", }; } const api = create({}); if (typeof module !== "undefined" && module.exports) module.exports = api; if (root) root.OldReportsData = api; })(typeof window !== "undefined" ? window : (typeof globalThis !== "undefined" ? globalThis : this));