---
name: tis_project_location
description: "Tomago (TIS) EXO-sync reconciliation + missing-client work lives in /config/workspace/projects/tis (own folder, not evolution)"
metadata: 
  node_type: memory
  type: project
  originSessionId: 1fa4dfeb-809d-4edd-ad2a-83f2349af7a4
  modified: 2026-07-26T10:23:11.515Z
---

**START HERE: `projects/tis/HANDOFF.md`** (rev 2026-07-26, post-reconciliation) — current state, next step, open items, gotchas. **All reconciliation numbers further down this memory file are superseded** — HANDOFF.md carries the only trustworthy set.

**Reconciled load — run 2, 2026-07-26, TEST `304-1781836545`:** 1,772 invoices / $580,469.07. Like-for-like vs James's dump (both cut 2026-07-23): his 1,684 / $515,069.06, **matched 1,683**, missing-from-evo **1** (`196259`, $49.50, no customer id in MYOB — the only unexplained row in the whole reconciliation), evo-only 62 / $59,569.17 (all explained), mismatches 19 / $466.39 (6 part-paid + `CR151435` off by a real $2.00 + 12 × 1c GST). Run 2 vs run 1 was purely additive: +15 credit notes, −$11,501.16, no existing row altered.

**A recurring bug class — sign-blindness on EXO amounts. Three instances found, assume more:** `DR_TRANS.TRANSTYPE=1` includes **credit notes** (negative `AMOUNT`; `INVNO` only *usually* `CR`-prefixed — `196492`/`196500`/`196570`/`196732`/`196754`/`196828` are unprefixed credits). (1) `AMOUNT > ALLOCATEDBAL` dropped every pre-cutoff credit — 15 rows / −$11,501.16; now `ABS(AMOUNT - ALLOCATEDBAL) > 0.005` in both extracts. (2) the `paidstatus` `<= 0` CASE flagged unapplied credits as paid — fixed but **inert**, see below. (3) **EXO writes negatives in brackets** — `(618.60)` = −618.60; `reconcile.py::money()` scored them 0.00, manufacturing 7 phantom full-value mismatches and disguising 8 missing credits as harmless $0.00 rows. Same class as the thousands-separator trap: treat every EXO number field as guilty until proven.

**BLOCKS live import — negative invoices vs credit notes.** EXO credits import as negative-total rows in `invoices`, not as `creditnotes`+`creditallocations` ([[evolution_creditnote_allocations]]). So (a) `library/invoice.php:637` `paidstatus = if(balance > 0, 0, 1)` — same sign-blindness, in **shared** code — flags all 30 imported credits as paid (SUM(balance) −$18,006.15); it overwrites the extract's correct value via `recomputeBalance()`. Don't just flip it: an *overpaid* invoice also has a negative balance and *is* settled — test `SIGN(balance) <> SIGN(total+tax)`. (b) TIS live is `xerosync=1` and nobody has traced what `commitInvoice()` pushes for a negative total (Xero wants `ACCRECCREDIT`, not `ACCREC`).

**Cut-off pending with the client:** recommending `01/07/2024` — drops 17 / $32,406.69, keeps 1,755 / $548,062.38. The 2008 Peter Belcher batch is **closed, not a write-off**: a 2011 `CREDIT` row of exactly −$1,219.93 offsets all 9 invoices, so the account nets $0.00 and the 4+ years bucket is now zero. Don't push the cut-off to `01/01/2025` to catch the last 3 CASH rows ($11,627.57) — it would also drop `CR175417` (−$462.00), a live customer's real credit. Date cut-offs are sign-blind too.

TIS EXO→Evolution invoice-sync **data + reconciliation** work is kept OUT of the evolution repo, in `/config/workspace/projects/tis/` (created 2026-07-25, sibling to `projects/madalice`). Older sync tooling stays in evolution: `cron/TIS_TMP/`, `library/exoimport.php`. Data-model keys/gotchas in [[tomago_exo_data_mapping]].

**Tenant identity (confirmed 2026-07-26 from `evolution.companies`):** `304-1779936994` = "Tomago Industrial Supplies" (id 92, **LIVE**, `xerosync=1`); `304-1781836545` = "Tomago Industrial Supplies Test" (id 98, `sandbox_of=92`, `sandbox_last_sync` 2026-07-22, xerosync off). **Test is a copy of live; the ONLY difference is the imported EXO invoices** — live has 9 invoices, Test has 1392 (the same 9 + ~1383 imported). Contacts are byte-identical across the two. A sandbox refresh from live would wipe the imported invoices.

**Folder layout:**
- `data/tis_invoices.csv` — EXO invoice export (headerless; cols: EXO invoice-no, EXO clientid, client name, date `d/m/Y`, order-ref, gross amt).
- `data/prod_invoices.tsv` — dump of tenant `304-1781836545` invoices.
- `data/exo_missing_clients_raw.tsv` — raw `SELECT *` of the 17 missing debtors from EXO `DR_ACCS`.
- `reference/exo_dr_accs_schema.txt` — full EXO `DR_ACCS` column list (captured 2026-07-25).
- `reports/tis_invoice_diff.csv` — invoice reconciliation.
- `reports/reconcile.py` — **reusable** EXO-export vs Evolution reconciler. `python3 reconcile.py <exo.csv> <evo_dump.tsv> <prefix>`; evo dump = `SELECT reference,date,balance,total,tax,clientid,id FROM invoices WHERE externalTenant='MYOB_EXO'`. **GOTCHA that burned me: EXO export amounts carry thousands separators (`3,248.11`)** — a bare `float()` scores them 0.00 and manufactures huge phantom mismatches. Strip commas.
- `reports/missing_clients.csv` — the 17 missing EXO client ids + tenant supplier-side match.
- `reports/missing_clients_resolved.csv` — **import-ready** client details (company/address/phone/email/ABN/active) parsed from EXO.
- `reports/exo_missing_clients.sql` — the extraction query (verified against DR_ACCS; note: DR_ACCS has NO contact-person col — contacts live in EXO `CONTACTS` via `STATEMENT_CONTACT_SEQNO`).
- `reports/migrate_missing_clients.sql` — **ready-to-run** migration (the real one). Keeps **one contact per company** (Evolution/Xero single-contact model; Xero enforces unique contact Name — dup rows break sync). Steps: (1) add col `contacts.exoDebtorRef varchar(85)` + index; (2) backfill existing clients `exoDebtorRef=externalRef2`; (3) MERGE the 13 already-existing suppliers → set `is_client=1` + `exoDebtorRef=<EXO debtor id>` on the chosen master row (APPS→1213, Olympic→982, AHS→1368; 159/1217 dup-supplier + 943/947 dead flagless rows left alone); (4) INSERT the 4 truly-absent (832,1067,1480,1484). **The EXO debtor id now lives in `exoDebtorRef`, NOT externalRef2** (externalRef2 = the in-use creditor id; the two namespaces collide → matching externalRef2 mis-posts). Paired code change: `exoimport.php::resolveClient` now matches `exoDebtorRef` (done, uncommitted). User runs SQL directly on tenant.
  **Hardened 2026-07-26:** Step 4 now qualified + `NOT EXISTS`-guarded per row (was the one unqualified, unguarded statement — a third run added 4 dup contacts to the session default DB); new Step 5 folds in the ad-hoc CASH (`clientid 41 → exoDebtorRef '0'`) and Olympic-Fencing (`clientid 982 → status=1`) fixes; VERIFY now includes id `0` + a dup-debtor-id check. Whole script is re-runnable; swap the hard-coded `304-1781836545` for `304-1779936994` to target live.
  **STATUS: RUN on BOTH tenants 2026-07-26 ~18:28 and verified.** Both DBs identical: column+index present, 265 refs backfilled, 13 merges correct (159/1217/943/947 still untouched), 4 inserts at clientid 1373–1376, 17/17 debtor ids resolve to exactly one client row, no dup exoDebtorRef or dup company names, 156/158 distinct EXO ids in the invoice CSV now resolve. Open issues found at verification: (a) Olympic Fencing clientid 982 is `status=0` (inactive) yet has 67 invoices — `resolveClient` ignores status so import works, but it's hidden in-app and would push to Xero archived; (b) Step 4's INSERT is the only statement with **no DB prefix** and has no idempotency guard — a third run would silently add 4 dup contacts.
- `reports/exo_extract_invoices.sql` + `reports/exo_extract_invoiceitems.sql` — **the corrected SQL Server (SSMS) extract queries, source of truth** (added 2026-07-26). Shane runs these on the EXO box; keep the repo copy in step with SSMS. Both carry an identical `DECLARE @cutoff` + WHERE — see the header/line mismatch note in [[tomago_exo_data_mapping]].
- `reports/insert_missing_clients.sql` — **SUPERSEDED, do not run** (INSERT-only version that duplicated the 13 existing companies). Kept for reference.

**Invoice reconciliation (2026-07-26, after the client-migration fixes + re-import over TIS Test):** Test tenant now holds **1696** `externalTenant='MYOB_EXO'` invoices, $576,157.18, all committed, **zero duplicate references** (re-running exoimport over the top is safe — idempotency holds). Missing-from-EXO-export collapsed from 317 ($103,154) to **17 ($1,580.10)** = 8 zero-value rows + 8 dated 2026-07-23 (after the loaded export) + 1 blank-debtor row. Amount mismatches: 25, net −$1,493, of which ~12 are 1c GST rounding and 7 are credit notes the invoices-only export shows as $0. **Aged tail: 19 invoices > 12 months old = $45,254.19** — all of them either the 10 CASH-coded 2023/24 rows ($44,034.26, individually $2.5k–$6.9k, do NOT look like counter sales — EXO id 0 may be a placeholder in the historical open-items export) or 9 × 2008 Peter Belcher rows ($1,219.93). Everything under 3 months = $525,931.84. Client reply drafted at `reports/reply_to_james_20260726.md` (recommends writing off the $45,254.19). **Watch:** mapping EXO id 0 → CASH silently pools any placeholder-0 historical row onto clientid 41.

**Earlier reconciliation (2026-07-25, dates ≤ 2026-07-22):** match key = CSV col1 (EXO invoice-no) = `invoices.reference`; CSV amount = `invoices.balance` (gross). Result: **309 CSV invoices missing from evo** (bulk May–Jul 2026 → sync dropping current-period chunk), **19 evo-only** = 7 `CR*` credit notes (CSV is invoices-only) + 3 non-EXO noise + 9 real (6 have matching `CR<num>` so evo/EXO agree; only 185029/193557/193731 genuinely evo-only).

**Missing-client blocker (why ~210 invoices won't sync):** 17 EXO debtor accounts have no `is_client=1` contact in the tenant. All resolve via `contacts.externalRef2 = <EXO ACCNO> AND is_client=1`. 13 of the 17 ALREADY exist but only as `is_client=0` (supplier/creditor side, different externalRef2 — the debtor/creditor `externalRef2` collision from [[tomago_exo_data_mapping]]); 4 are fully absent (832,1067,1480,1484). Fix = create client contacts (externalRef2=ACCNO, is_client=1) from `missing_clients_resolved.csv`. Caveats: EXO `832 Peter Belcher` is ISACTIVE=N (archived) yet has 8 invoices; 6 rows have no ABN, 3 no email; COD rows 1480/1484 have name/phone jumbled into address fields.

**Still-open follow-ups (not yet done):** (1) CASH — EXO id `0` → tenant `contacts.clientid=41` (externalRef2='1'); ~96 CASH invoices won't resolve by externalRef2='0' (that maps to a non-client). (2) Blank/shifted EXO ids in the CSV export: "Allied Cranes Hire" (→ existing clientid 12, EXO id 1361) and "Lucas Drilling Pty Ltd" (→ clientid 128, EXO id 1289) — export dropped their ids; both exist, just need id back-fill. Together CASH+these block ~another 100 invoices.

**Prod/EXO access:** Evolution tenant via `claude` MySQL user per [[prod_mysql_access]]. EXO itself (MYOB EXO = SQL Server) is NOT directly reachable by Claude — the user runs EXO queries and pastes results back.