""" Afton UF Proportional JE Stager (the "proper way" redesign) ============================================================ For the no-method UF cleanup, Dr the income accounts in proportion to how historical direct-deposits actually distributed Cr income — i.e., reverse the over-count approximately where it actually happened. Approach: 1. Query all RBFCU Deposits + analyze their direct-cash Cr lines 2. Aggregate Cr lines by income account → dollar-weighted distribution 3. Allocate total cleanup amount ($200K) across income accounts using that dollar-weighted distribution 4. Build a single JE with: - One Dr line per income account, sized to its proportional share - One Cr line per UF entry being cleared (for per-pair traceability) 5. Stage to QBO Write Queue Defaults to DRY RUN. Pass --apply to stage. """ from __future__ import annotations import argparse import json import sys from collections import defaultdict from datetime import datetime from pathlib import Path if hasattr(sys.stdout, "reconfigure"): sys.stdout.reconfigure(encoding="utf-8", errors="replace") SCRIPT_DIR = Path(__file__).resolve().parent QBO_APP_DIR = SCRIPT_DIR.parent.parent / "Atlas" / "app" QUEUE_DIR = SCRIPT_DIR.parent.parent / "Team" / "Riv" / "j2_qbo_mcp" sys.path.insert(0, str(QBO_APP_DIR)) sys.path.insert(0, str(QUEUE_DIR)) import qbo_client as qbo # type: ignore from queue_backend import append as queue_append # type: ignore CLIENT = "afton" UF_ACCT_ID = "4" RBFCU_ACCT_ID = "61" FORENSICS_JSON = SCRIPT_DIR / "afton_uf_forensics.json" TXN_DATE = "2026-05-22" DOC_NUMBER = "AFTON-UF-PROP" # 13 chars BACKDATE_THRESHOLD_DAYS = 30 OUT_REPORT = SCRIPT_DIR / "afton_uf_proportional_je_report.md" def page_query(token: str, sql_base: str, entity: str) -> list[dict]: out: list[dict] = [] start = 1 page = 1000 while True: sql = f"{sql_base} STARTPOSITION {start} MAXRESULTS {page}" resp = qbo.query(CLIENT, token, sql) rows = resp.get(entity, []) if not rows: break out.extend(rows) if len(rows) < page: break start += page return out def classify_entry(r: dict) -> str: if r.get("create_time") and r.get("txn_date"): try: ct = datetime.fromisoformat(r["create_time"].replace("Z", "+00:00")).date() td = datetime.fromisoformat(r["txn_date"]).date() if (ct - td).days >= BACKDATE_THRESHOLD_DAYS: return "hold" except Exception: pass return "auto" def main() -> None: ap = argparse.ArgumentParser(description=__doc__) ap.add_argument("--apply", action="store_true") args = ap.parse_args() forensics = json.load(open(FORENSICS_JSON, encoding="utf-8")) no_method = [r for r in forensics["rows"] if not r.get("payment_method_id")] auto_entries = [r for r in no_method if classify_entry(r) == "auto"] hold_entries = [r for r in no_method if classify_entry(r) == "hold"] print(f"{len(no_method)} no-method UF entries: {len(auto_entries)} auto-plug, {len(hold_entries)} held") auto_total = sum(r["amount"] for r in auto_entries) print(f"Auto-plug total: ${auto_total:,.2f}") token = qbo.refresh_access_token(CLIENT) # ── Pull RBFCU Deposits + analyze direct Cr income lines ───────────── print("\nPulling all RBFCU Deposits to build income distribution...") all_deposits = page_query(token, "SELECT * FROM Deposit", "Deposit") rbfcu = [d for d in all_deposits if (d.get("DepositToAccountRef") or {}).get("value") == RBFCU_ACCT_ID] print(f" {len(rbfcu)} RBFCU deposits") # Pull Account table to filter to Income-type only print("Pulling Account chart...") accts_resp = qbo.query(CLIENT, token, "SELECT * FROM Account MAXRESULTS 1000") accts = {str(a["Id"]): a for a in accts_resp.get("Account", [])} print(f" {len(accts)} accounts") # Aggregate direct-cash Cr line dollar amounts by income account income_dist: dict[str, dict] = defaultdict(lambda: {"amount": 0.0, "lines": 0, "name": "", "type": ""}) for d in rbfcu: for line in d.get("Line", []): if line.get("DetailType") != "DepositLineDetail": continue if line.get("LinkedTxn"): continue # only direct-cash lines (no LinkedTxn) count as over-count culprits detail = line.get("DepositLineDetail", {}) or {} acct_ref = detail.get("AccountRef") or {} acct_id = str(acct_ref.get("value", "")) acct = accts.get(acct_id) if not acct: continue if acct.get("AccountType") != "Income": continue # skip non-income (refunds-to-expense, transfers, etc.) amt = line.get("Amount", 0) or 0 income_dist[acct_id]["amount"] += amt income_dist[acct_id]["lines"] += 1 income_dist[acct_id]["name"] = acct.get("Name", "") income_dist[acct_id]["type"] = acct.get("AccountType", "") total_income_dist = sum(v["amount"] for v in income_dist.values()) print(f"\nDirect-cash Income credits on RBFCU deposits: ${total_income_dist:,.2f} across {len(income_dist)} accounts") # ── Build proportional allocation ──────────────────────────────────── allocations = [] for acct_id, v in sorted(income_dist.items(), key=lambda x: -x[1]["amount"]): pct = v["amount"] / total_income_dist if total_income_dist else 0 allocated = round(auto_total * pct, 2) if allocated < 0.01: continue allocations.append({ "acct_id": acct_id, "name": v["name"], "historical_amount": v["amount"], "historical_pct": pct, "allocated": allocated, }) # Round-off correction: ensure Dr total exactly equals Cr total alloc_sum = sum(a["allocated"] for a in allocations) diff = round(auto_total - alloc_sum, 2) if abs(diff) > 0.001 and allocations: allocations[0]["allocated"] = round(allocations[0]["allocated"] + diff, 2) print(f" Rounding correction of ${diff:.2f} applied to {allocations[0]['name']}") print(f"\nProposed Dr allocation:") for a in allocations: print(f" {a['name']:40s} {a['historical_pct']*100:5.1f}% ${a['allocated']:>10,.2f}") # ── Build JE lines ──────────────────────────────────────────────────── je_lines: list[dict] = [] for a in allocations: je_lines.append({ "Description": ( f"UF cleanup proportional Dr — {a['name']} " f"({a['historical_pct']*100:.1f}% of historical deposit income credits)" ), "Amount": a["allocated"], "PostingType": "Debit", "AccountRef": {"value": a["acct_id"], "name": a["name"]}, }) # One Cr line per UF pmt for traceability per_pair_audit: list[str] = [] for r in auto_entries: je_lines.append({ "Description": ( f"UF clear: {r['customer']} | pmt {r['id']} | txn {r['txn_date']}" ), "Amount": r["amount"], "PostingType": "Credit", "AccountRef": {"value": UF_ACCT_ID, "name": "Undeposited Funds"}, }) per_pair_audit.append( f"{r['customer']}: ${r['amount']:.2f} pmt {r['id']} txn {r['txn_date']}" ) dr_total = sum(l["Amount"] for l in je_lines if l["PostingType"] == "Debit") cr_total = sum(l["Amount"] for l in je_lines if l["PostingType"] == "Credit") if abs(dr_total - cr_total) > 0.01: sys.exit(f"JE UNBALANCED: Dr=${dr_total:.2f} Cr=${cr_total:.2f} (diff ${dr_total - cr_total:.2f})") print(f"\nJE built: {len(je_lines)} lines Dr=Cr=${dr_total:,.2f}") print(f" Dr lines: {len(allocations)} (per income account, proportional)") print(f" Cr lines: {len(auto_entries)} (per UF pmt, individual clearing)") # ── Report ──────────────────────────────────────────────────────────── lines = [ "# Afton UF Proportional JE Plug — Report", f"_Generated: {datetime.utcnow().isoformat()}Z_", "", "## What this JE does", "", f"Reverses {len(auto_entries)} no-method UF entries totaling ${auto_total:,.2f} " f"by Dr'ing income accounts in proportion to where the historical direct-deposit " f"double-counts actually landed.", "", f"- 156 of 179 no-method entries are 'auto-plug' (same-day or backdated <30d)", f"- 23 entries held for separate review (heavily backdated 30+d)", "", "## Dr side — proportional Income allocation", "", f"Based on ${total_income_dist:,.2f} of historical direct-cash Cr income lines across " f"{len(rbfcu)} RBFCU Deposits, allocated to {len(allocations)} income accounts:", "", "| Income Account | Historical $ | Historical % | Dr Allocated |", "|---|---|---|---|", ] for a in allocations: lines.append( f"| {a['acct_id']} {a['name']} | ${a['historical_amount']:,.2f} | " f"{a['historical_pct']*100:.2f}% | ${a['allocated']:,.2f} |" ) lines.append(f"| **TOTAL** | ${total_income_dist:,.2f} | 100.00% | **${dr_total:,.2f}** |") lines.append("") lines.append("## Cr side — per-UF-entry clearing") lines.append("") lines.append(f"One Cr line per UF entry being cleared, totaling ${cr_total:,.2f} (= sum of Dr).") lines.append("") lines.append("(Per-pair detail captured in JE PrivateNote.)") lines.append("") lines.append("## Held entries (NOT in this JE — heavily backdated)") lines.append("") if not hold_entries: lines.append("_None._") else: lines.append("| Create Day | Txn Date | Customer | $ | Pmt Id |") lines.append("|---|---|---|---|---|") for r in sorted(hold_entries, key=lambda x: x.get("create_day", "")): lines.append( f"| {r.get('create_day','')} | {r.get('txn_date','')} | {r['customer']} | " f"${r['amount']:,.2f} | {r['id']} |" ) OUT_REPORT.write_text("\n".join(lines), encoding="utf-8") print(f"\nWrote {OUT_REPORT}") if not args.apply: print("\nDRY RUN — pass --apply to actually queue the JE") return # Build payload private_note = ( f"Afton UF no-method cleanup (proportional Income-routed). Reverses " f"{len(auto_entries)} UF entries ${auto_total:,.2f} by Dr'ing income accts " f"in proportion to historical RBFCU direct-deposit Cr distribution " f"(${total_income_dist:,.2f} across {len(rbfcu)} deposits). HELD: " f"{len(hold_entries)} heavily-backdated entries for separate review.\n\n" f"Dr allocation:\n" + "\n".join(f" {a['name']}: {a['historical_pct']*100:.1f}% = ${a['allocated']:,.2f}" for a in allocations) + "\n\nPer-pair Cr audit:\n" + "\n".join(per_pair_audit) ) payload = { "TxnDate": TXN_DATE, "Line": je_lines, "Adjustment": True, "DocNumber": DOC_NUMBER, "PrivateNote": private_note[:8000], } row_id = queue_append({ "timestamp": datetime.now().isoformat(timespec="seconds"), "client": CLIENT, "tool_called": "create_journal_entry", "payload_json": json.dumps(payload, default=str), "requested_by": "Ledger (afton_uf_proportional_je_stager.py)", "status": "Pending", "tier": 1, }) print(f"\nQUEUED as row {row_id}") print("Approve in QBO Write Queue Sheet → 'afton' tab → Y in Approve col → run worker") if __name__ == "__main__": main()