# TIS (Tomago Industrial Supplies) — MYOB EXO → Evolution invoice migration ## Session handoff — 2026-07-26 ### Goal Migrate open AR from MYOB EXO into the Evolution tenant. Test tenant is the rehearsal; live import is Shane's job and is deliberately **not** on the client's task list. ### Tenants | | DB | Notes | |---|---|---| | LIVE | `304-1779936994` | company id 92, `xerosync=1` | | TEST | `304-1781836545` | company id 98, `sandbox_of=92` | Test is a copy of live; the only difference is the imported EXO invoices. Contacts are identical across both — the client migration was run on **both**, so a live→test refresh preserves `exoDebtorRef`, the CASH mapping and Olympic's active flag. --- ## State as of end of this session **Run 2 (credit-note fix) is loaded and reconciled — the gap is down to one invoice.** Load in TEST: **1,772 invoices / $580,469.07** (balance, GST-inclusive), ids 2043–3814, dates 2008-09-02 → 2026-07-24. Dump: `data/test_invoices_run2.tsv`. No duplicate `externalRef` or `reference`, no orphan `invoiceitems`. Run 2 vs run 1 is **purely additive: +15 rows, all credit notes, −$11,501.16, and not one existing row altered** (the balance delta reconciles to $0.00). Nothing was lost by re-running — idempotency holds again. Run 1, for the record, was 1,757 / $591,970.23 (files #36/#37, UI cutoff `01/07/2008`). Its UI showed **"Import request failed."** while the data landed intact. **That was NOT a response-side timeout** — diagnosed 2026-07-26, see bug 7 below. Still worth one dry run before the live import to confirm it reports zero new inserts. --- ## Bug 7 (2026-07-26) — the whole load has NO receivable leg in the GL Run 2 surfaced 15 × `SQLSTATE[23000] ... Column 'accname' cannot be null` — one per new credit note. Tracing it found the invoice/line data is fine but **the general ledger is not, for all 1,772 rows.** **Root cause:** this tenant's `preferences` row has `accRec = 0` / `accPay = 0`. LIVE (`304-1779936994`) has `11` / `43` — the sandbox copy never brought the preferences row across (live is row id 1, test is row id 3). `commitInvoice()` posts the receivable debit **last** and resolved its account name by scanning `getAccounts()`. With `accRec = 0` nothing matched, the name stayed null, and `gledger.accname` is `NOT NULL` — so the AR leg was rejected. The legacy `insertQuery()` **catches the PDOException and `echo`s it** rather than throwing, so the commit carried on and still flipped `commited = 1`. Verified: **0** AR rows across all 1,772 invoices — ```sql select count(*) from invoices i where i.commited = 1 and not exists (select 1 from gledger g where g.sourcetype = 'Invoice' and g.sourceid = i.id and g.memo like 'Sales Dispersment;AccReciev%'); ``` The echoed error text also landed in the **middle of the JSON response body**, so `json_decode` failed and jQuery fell into `.fail()`. That — not a timeout — is the "Import request failed." message, on both runs. **LIVE is not affected by this one:** `accRec = 11` and all 5 of its committed invoices have their AR leg. ### Bug 8 — phantom GST on mixed-tax invoices (all tenants, not just TIS) `commitInvoice()`'s tax loop read the rate table into `$tax`, then assigned the per-group tax **amount** to that same `$tax` inside the loop. From the second tax group onwards `foreach($tax as $val)` was iterating a float, the rate lookup silently no-opped, and the group inherited the **previous group's account and rate**. In this load: - invoice **2143** — $93.12 on tax code **FREE** posted **$9.31 to 49 GST Collected** - 2355 / 3376 / 3387 — same shape, but their FREE groups total $0.00 so they got away with a harmless $0.00 row LIVE TIS also has `FREE.collectedTax = 0`, so live was exposed too. The AP/bill poster had already been fixed for this exact bug; the AR/invoice side was missed. ### Fixed in code (branch `staging`, evolution repo) - `functions.php` — new `glAccName()`, never returns null. - `accounting.php` + `library/accounting.php` — control accounts resolved and validated **before** any GL is written (commit refused rather than half-posted); `$tax` clobber fixed; zero-rated groups post nothing instead of a $0.00 row. - `library/exoimport.php` — commit failures reported, invoice marked **draft** instead of silently counted as created. - `invoiceimport.php` — output buffered so a stray echo can never corrupt the JSON again; plus a target-company guard. ### To do on the test tenant **Decision (Shane, 2026-07-26): no GL backfill — resync the test tenant from live and rerun the import.** The reload clears the missing AR legs and the phantom GST together, and is needed for bug 4 anyway. 1. Resync `304-1781836545` from live. 2. Run section 1 + 3 of `reports/fix_test_tenant_control_accounts.sql` to confirm `accRec = 11` / `accPay = 43` came across with the resync. Only run the UPDATE in section 2 if they are still 0. 3. Re-extract + re-import (bug 4 cutoff fix). The new `commitInvoice()` guard means step 2 can't be silently skipped — a missing control account now refuses the commit and the import reports the invoice as a **draft** rather than counting it as created. ### Reconciliation result — one row left unexplained Like-for-like, both sides cut at 2026-07-23 (`reports/run2_l4l_*.csv`): | | Invoices | Value | |---|---|---| | James's dump | 1,684 | $515,069.06 | | **Matched** | **1,683** | | | In EXO, not in Evolution | 1 | $49.50 | | In Evolution, not in EXO | 62 | $59,569.17 | | Matched but amount differs | 19 | $466.39 | - **Missing 1** = `196259` only, the invoice with no customer id in MYOB. The 8 credits that were missing in run 1 all arrived. - **Evo-only 62** fully explained: 10 CASH ($44,034.26) + `00027272` ($4.62) + the 2 remaining not-in-his-dump ($16,313.72) + 26 rows dated 23 July ($8,868.50, his dump was cut mid-day) + invoice/credit pairs netting nil + 7 credits his dump never had (−$10,213.53). - **19 mismatches** = 6 part-paid ($468.26) + `CR151435` (**−$2.00, real, not rounding** — we hold −110.00, he has −108.00; unresolved) + 12 one-cent GST rounding ($0.13). - **Zero/no-line invoices: 1** — `196799`, genuinely nil, $0.00 in his dump too. ### Two findings from the credit notes that changed the client story - **The 2008 Peter Belcher account nets to $0.00.** A credit `CREDIT` dated 2011-01-24 for exactly **−$1,219.93** offsets all 9 invoices. It was never a write-off candidate — it was written off in 2011 and only looked open because the credit was being dropped. The 4+ years aging bucket is now $0.00. - **`193557` ($771.98) is credited** by `CR193557` on the same account (clientid 170), which answers one of the three "in Evolution but not in your dump" questions. Only `193731` ($15,392.03) and `185029` ($921.69) remain open. - Genuinely unapplied credits now carried across (no offsetting invoice): `CR175417` −$462.00 (clientid 225), `191386` −$259.60 (7), `195951` −$267.52 (982), `196039` −$2,337.50 (249), `CR195984` −$210.38 (120). ### Aging at 30/06/2026 (run 2) | Age | Invoices | Value | |---|---|---| | Under 3 months | 1,727 | $532,234.54 | | 3–12 months | 24 | $4,662.27 | | 1–2 years | 4 | $11,165.57 | | 2–3 years | 3 | $14,133.85 | | 3–4 years | 4 | $18,272.84 | | 4+ years | 10 | $0.00 | ### Done and verified - **Client migration** run on both tenants. `contacts.exoDebtorRef` column + index added; 265 existing clients backfilled; 13 supplier-only companies merged to dual-role (`is_client=1`); 4 absent companies created (clientid 1373–1376). All 17 EXO debtor ids resolve to exactly one client row. Script: `reports/migrate_missing_clients.sql` — now fully re-runnable (Step 4 DB-qualified + `NOT EXISTS`-guarded, Step 5 folds in the CASH and Olympic fixes, two VERIFY queries at the end). - **Import idempotency** — dedupe is on `invoices.sourceRef = 'MYOB_EXO:'` (single generic column, indexed `idx_sourceRef`). Verified: re-running exoimport over a loaded tenant creates zero duplicate references. **Changed 30/07/2026** — it used to be `externalTenant='MYOB_EXO' AND externalRef=`, but those two columns are the *Xero* link, so every committed import was pushed to Xero against tenant "MYOB_EXO" and flagged `review_flag=1 / syncerror=1` ("Forbidden AuthenticationUnsuccessful") — 1,762 invoices on the prod load. Migration: `evolution/migrations/invoices_source_ref.sql` (backfills sourceRef, frees the Xero columns, clears the false reviews). Any query or dump that still filters `externalTenant='MYOB_EXO'` returns zero rows after it runs. - **Positional column contract** between the SSMS extracts and `library/exoimport.php` — header 0-idx 0/9/13/18/21/22/28/30/36 (`INV_*`), lines 0-idx 3/8/10/12/14/16/17/18/20 (`IT_*`). Checked aligned. Never insert or reorder a SELECT column without updating the constants. ### Fixed this session 1. **Header/line extracts selected opposite sets.** The lines query filtered pre-cutoff rows on `ALLOCATEDBAL > 0` (= *has been paid*) while the header used `AMOUNT > ALLOCATEDBAL` (= *still outstanding*). `ALLOCATEDBAL` is the allocated/paid amount, so old fully-unpaid invoices got a header with **no lines** and imported as **$0.00** — the exact population an open-items migration exists for. Most likely cause of the earlier "import failed" and the $0.00 rows. Both extracts now share an identical `DECLARE @cutoff` + WHERE clause. 2. **Three inverted header columns** — `paidstatus` flagged fully-unpaid as paid; `paid` held the outstanding amount; `balance` held the paid amount. Masked today because exoimport recomputes paid/balance from lines (all land `paid=0`), but `paidstatus` is what the AR screens gate on. 3. **Stale `externalRef2` text** in `exoimport.php` (:14 comment, :295 error message) — the resolver has queried `exoDebtorRef` since yesterday, but the error message read as "the code hasn't deployed". Now says `exoDebtorRef`. ### Fixed in the second pass (after reconciling the 26/07 load) 4. **Both extracts dropped every pre-cutoff credit note.** `DR_TRANS.TRANSTYPE=1` covers credit notes as well as invoices — negative `AMOUNT`, `INVNO` usually but *not always* prefixed `CR` (`196492`, `196500`, `196570`, `196732`, `196754`, `196828` are unprefixed credits). The open-item test `AMOUNT > ALLOCATEDBAL` is sign-blind, so an unapplied −$108.00 credit failed `-108 > 0` and was excluded. Cost 8 rows / −$1,285.63. Now `ABS(AMOUNT - ALLOCATEDBAL) > 0.005` in **both** files — sign-safe, and still excludes fully-settled items. **Needs a re-extract + re-import to clear.** 5. **`paidstatus` flagged unapplied credits as paid** — same sign-blindness in the `<= 0` CASE. 15 credit notes in the current load carry `paidstatus=1` with a negative balance. Now tests `ABS(...) <= 0.005`. 6. **`reconcile.py::money()` scored accounting negatives as $0.00.** EXO writes credits as `(618.60)`. `float("(618.60)")` throws → the old code returned 0.0, so 7 rows that matched *to the cent* were reported as full-value mismatches and the 8 missing credits looked like harmless $0.00 rows. This is what made the first pass over this load read as "25 mismatches / 8 zero-value missing". Now parses brackets. **The parenthesis trap is the same class of bug as the thousands-separator one — treat any new EXO number field as guilty.** ### Source of truth for the SSMS queries - `reports/exo_extract_invoices.sql` — header extract - `reports/exo_extract_invoiceitems.sql` — line extract Shane runs these on the EXO SQL Server box. **Keep the repo copies in step with SSMS** — the repo copy is the reviewed one. `@cutoff` must be identical in both. --- ## Immediate next step 1. **Finish and send the reply to James** — `reports/reply_to_james_20260726.md`, rev 3, reconciled against run 2. Still **not sent**. It has an unfinished sentence left mid-edit under point 1 ("This fixes what would have been a h"). 2. **Decide the negative-invoice / `paidstatus` question below** before the live import — it is the one thing that would land wrong data in a Xero-connected tenant. 3. **Live import** once James confirms the cut-off (`01/07/2024` recommended). ### How to reconcile (reproduce or re-run) ```bash cd /config/workspace/projects/tis # 1. dump imported invoices out of TEST # (prod MySQL creds: memory prod_mysql_access — user `claude`, # host my.evolutionerp.com.au, pw must be single-quoted in bash) mysql -h my.evolutionerp.com.au -u claude -p'' -N -e \ "SELECT reference,date,balance,total,tax,clientid,id FROM \`304-1781836545\`.invoices WHERE sourceRef LIKE 'MYOB_EXO:%'" > data/test_invoices_20260726.tsv # 2. cut to James's last date for a like-for-like, else the 24 July rows # show up as 27 phantom evo-only invoices awk -F'\t' '$2<="2026-07-23"' data/test_invoices_20260726.tsv > /tmp/evo_l4l.tsv # 3. reconcile python3 reports/reconcile.py data/tis_invoices.csv /tmp/evo_l4l.tsv reports/final_l4l ``` Writes `*_missing_from_evo.csv`, `*_evo_only.csv`, `*_amount_mismatch.csv`. Zero/no-line check (note: `lines` is reserved in MariaDB — alias it `nlines`): ```sql SELECT i.reference, i.date, i.total, i.balance, COUNT(ii.id) nlines FROM `304-1781836545`.invoices i LEFT JOIN `304-1781836545`.invoiceitems ii ON ii.invoiceid = i.id WHERE i.sourceRef LIKE 'MYOB_EXO:%' GROUP BY i.id HAVING nlines = 0 OR i.total = 0; ``` **Stale baselines — do not quote these.** `memory/tis_project_location.md` under "Invoice reconciliation (2026-07-26…)" describes the *previous* overlay load (1,696 invoices / $576,157.18), and rev 1 of the James reply quoted 1,676 / $516,785.74 with an aging total of $576,157.18. Both superseded by the table above. `reports/baseline_*`, `after_reimport_*`, `likeforlike_*` and `new_*` CSVs are all pre-parser-fix — `final_l4l_*` is the only trustworthy set. --- ## Open decision (BLOCKS the live import): negative invoices vs credit notes EXO credit notes are being imported as **invoices with a negative total** into `invoices`. That is not how Evolution models a credit — credits live in `creditnotes` with `creditallocations` (see memory `evolution_creditnote_allocations`). Two consequences, neither yet resolved: 1. **`paidstatus` is wrong on all 30 imported credits** and the extract-side fix cannot fix it. `exoimport.php:455` calls `invoice::recomputeBalance()`, which ends with `library/invoice.php:637`: ```php update invoices set paidstatus = if(? > 0, 0, 1) where id = ? // $bal ``` Sign-blind — the *same* bug class as the two extract bugs. A credit with `balance = -1219.93` fails `> 0` and is flagged **paid**, so every unapplied credit reads as settled and won't surface as available on AR screens. Current state: 30 rows, `paidstatus=1`, `SUM(balance) = -$18,006.15`. **Do not just flip the comparison** — a genuinely *overpaid* invoice also has a negative balance and *is* correctly settled. The sign-safe test has to compare the balance against the document's own sign, e.g. settled when `balance = 0 OR SIGN(balance) <> SIGN(total + tax)`. This is shared library code used by every tenant and by the Xero payment path — it needs its own change, review and release note, not a drive-by edit inside the TIS work. 2. **Xero.** TIS live is `xerosync=1`. A negative `invoices` row pushed to Xero as an `ACCREC` is wrong; Xero wants an `ACCRECCREDIT`. Nobody has traced what `commitInvoice()` → Xero actually does with a negative total. **Check this before the live import runs with `$commit` on**, or 30 malformed documents go into the client's Xero. ## Open items **Code — uncommitted working copy on `evolution` branch `staging`:** - `library/exoimport.php` — the `resolveClient` → `exoDebtorRef` change (from the prior session) **and** this session's comment/message fixes. Both still need committing and deploying. Nothing else in the file changed. **Migration:** - Live import not yet run. Blocked on the test reconciliation coming back clean. **Data questions still with the client (James):** - Invoice `196259`, 02/07/2026, $49.50, order ref `195205-CARD` — no customer ID or name in MYOB, cannot be placed. - 3 invoices in Evolution but not in James's dump ($17,085.70, the largest being `193731` at $15,392.03) — paid/credited/voided after his dump? - ~6 invoices where Evolution is higher than his figure (~$468) — presumed part-paid, since payment history is not being imported. - **Cut-off recommendation (Shane's call, 2026-07-26): `01/07/2024`** as the importer's "ignore invoices dated before" date. On run 2 it drops 17 records / **$32,406.69** and keeps **1,755 / $548,062.38**. The 17 are the 10 Peter Belcher records — which now net to **$0.00**, so dropping them changes receivables by nothing — plus 7 CASH rows ($32,406.69). So the entire effect is the CASH group. - **Don't move the cut-off later to catch the last 3 CASH rows.** 289633, 289635 (09/09/2024) and 290513 (30/09/2024), $11,627.57, survive `01/07/2024`. A `01/01/2025` cut-off would sweep them up **but would also drop `CR175417` (−$462.00, clientid 225)** — a live customer's genuine credit. A date cut-off is sign-blind too. Handle those 3 individually; the reply asks James to decide. - Group A = 10 CASH-coded 2023/24 rows ($44,034.26), outstanding on our extract but absent from James's open-invoice dump entirely — still unexplained. Group B (2008 Peter Belcher) is **closed**: fully credited in 2011, nets to zero. - Draft reply: `reports/reply_to_james_20260726.md` — **rev 3, not yet sent**, and carries an unfinished sentence under point 1. Rev 1's "eight of those nine are $0.00 value" was the parser bug; its "your dump is invoices-only" was wrong (4 `CR` rows). Rev 2's aging table and write-off recommendation were superseded by the Belcher credit. **Watch:** - Mapping EXO debtor id `0` → CASH (clientid 41) silently pools any placeholder-0 historical row onto that one account. That is how the Group A rows landed there. - `DEFAULT_MIN_DATE = '2026-07-01'` in `exoimport.php:54` is the importer-side cutoff and is overridden at runtime from the UI. It must not contradict `@cutoff` in the SSMS extracts, or rows get pulled and then skipped. --- ## Gotchas that have already burned this project - **EXO export amounts carry thousands separators** (`3,248.11`). A bare `float()` scores them 0.00 and manufactures enormous phantom mismatches. `reconcile.py::money()` strips them — use it, don't hand-roll a parser. - **EXO writes negatives in brackets** (`(618.60)` = −618.60). Every credit note and return. `float()` throws and a naive parser scores 0.00 — see fix 6. - **`ALLOCATEDBAL` = allocated/paid, not outstanding.** Proved empirically. Every filter and column mapping in both extracts depends on this. - **`TRANSTYPE = 1` is invoices *and* credit notes.** Credits have a negative `AMOUNT`; `INVNO` is usually `CR`-prefixed but not reliably. Any comparison, filter or `paidstatus` expression over `AMOUNT`/`ALLOCATEDBAL` must be sign-safe (`ABS(a - b)`), never a bare `>` or `<= 0`. - **MySQL password in bash** — single-quote it (`-p'M15QL2023!_DEV3NV26'`); `\!` stays literal in non-interactive bash and you get "Access denied". - **Tenant tables are latin1**, params bind utf8mb4 — hence `CONVERT(exoDebtorRef USING utf8mb4)` in the resolver. - **`contacts` PK is `clientid`**, not `id`. MyISAM, no unique index. - **One contact per company** (`is_client=1 AND is_supplier=1`). Xero enforces unique contact Name; a duplicate company row breaks sync. --- ## Relevant memory files `tis_project_location`, `tomago_exo_data_mapping`, `tomago_exo_import`, `prod_mysql_access`, `evolution_master_branch_deprecated`.