--- name: evolution-inventory-analytics-suite description: The three legacy inventory-analytics reports were rebuilt in place as a modern Inventory Analytics suite metadata: node_type: memory type: project originSessionId: 0dfd6f0c-eab5-485e-946d-b5fccab00785 modified: 2026-07-19T04:24:11.346Z --- Rebuilt 2026-07-19: the three legacy inventory reports were modernised into an **Inventory Analytics** suite (catalogue group renamed `Stock Movement`→`Analytics` in `reportsdashboard.inc`): - **Product Sales Performance** (`invmonthlysales`) — was "Inventory Category Monthly Sales". Per-product committed-invoice sales over a range: qty/net/avg/GP$/GP%/trend sparkline + totals row. FY quick-period presets. - **Item History / Stock Card** (`invitemhist`) — was "Item History Detail". Single item: position (soh/onorder/allocated from `inventory_soh`) + sales KPIs + monthly trend + unified tx history (committed invoices, quotes, mfshoporders, purchases), each click-through to source. - **Stock Movement Ledger** (`invstckmovement`) — was "Stock Movement Report". Physical audit trail from `stockmovement` with running SOH; filter item/category/source/branch/date. **Key build decisions (why the file layout looks odd):** - **Rewrote legacy `.inc`/`.js` in place** (kept the lowercase filenames) so ~7 inbound deep-links across 6 files keep working: invedit.inc / invSohBranchCat.inc pass `?code=`/`?itemid=`; the mf drills pass `?startdate=&enddate=`. Each `.inc` reads those into deep-link JS vars and auto-runs. - **New endpoints are capital-S `*Save.php`** (e.g. `invitemhistSave.php`), NOT lowercase `*save.php`. Linux fs is case-sensitive, so these are distinct from any legacy lowercase `*save.php` — e.g. invitemdetail.inc still depends on the old `invitemhistsave.php`, left untouched. - All three follow the modern triad pattern (`.inc` Bootstrap-5 view + perm gate, `.js` AJAX+DataTables+CSV, `Save.php` JSON endpoint). DB::SELECT bound params only. Client-side DataTables sort (fixed server ORDER BY, no user sortby → no safeOrderBy needed). Inline-SVG sparklines, no chart lib. Perm gate: `admin || (mod_rep>=1 && mod_inv>=1)`; Product Sales Performance also allows `mod_sales>=1`. - Sales figures across all three use the canonical committed filter (`invoices.commited='1' AND status NOT IN('2','5')`) + per-line `ROUND(adjPrice*qty,2)` so they reconcile. See [[evolution_invoice_total_reconciliation]]. **Product Sales Performance — "Include job-dispatched items" option (added 2026-07-19):** opt-in checkbox (`incljobs=1`) that also counts stock dispatched against jobs but NOT on any invoice line — the gap being **text/lump-sum job invoices**, which write a single `invoiceitems` row with `src='job', srcid=0, itemid=` and touch nothing on `jobInventory`. So those dispatched items never reach the invoice-line-based report. - **Datable dispatched qty** = committed dispatch lines (`dispatchitems.qty` via `dispatch.status>=0`, dated by `DATE(dispatch.date)`) **minus** returns (`stockmovement src='return'`, dated by `usr_date`/`date`). This reconstructs `jobInventory.dispatched` (which `inventory::jobInventoryUpdate()` derives the same way) at 99.9% match, but per-movement-dated so it respects the date range + monthly trend. Valued at `jobInventory.price`/`cost` (per-line rounded). Guarded per row so combined qty/net stay correct. - **Double-count guard = the authoritative "already on an invoice line" test:** `NOT EXISTS (invoiceitems WHERE src='job' AND srcid = jobInventory.id AND committed)`. Do **NOT** use `jobInventory.invoiceid` — it's only set by the quote-extras path (`invoice.php::importJobItems`) and stays 0 for both itemised and text-only invoicing, so it under-reports. This `src='job'+srcid` join is how the codebase computes invoiced qty everywhere (`invoice.php:444`, `invoiceaddsave.php:1030`). - Endpoint restructured to a two-pass PHP merge (invoice pass grouped item+month, then optional job pass), keyed by inventory id; job-sourced portion tracked separately (`jobQty`/`jobNet`/`jobGp`) → shown as a flagged **Job Qty** column (badge+tooltip), toggled via `.pp-jobcol`. minQty/sort/cap moved to PHP (verified identical totals to old SQL path). Docs + static release-notes page updated in git. - **Prod `releasenotes` rows for the two job-dispatch options injected 2026-07-19: ids 127 (Product Sales Performance) + 128 (Item History)** — mirror the two `new` bullets on the static release-notes page; `status=0` unapproved, `public=1`, module `Inventory`, doc url → reporting/inventory.php, HTML-entity copy (ASCII-safe, latin1 conn). Shane to approve alongside 124–126 after docs git deploy. **Same option added to Item History / Stock Card (`invitemhistSave.php`) 2026-07-19:** the modernised item-drill had the SAME blindspot (KPIs/monthly all from `invoiceitems INNER JOIN invoices`) — in fact a regression, since the OLD lowercase `invitemhistsave.php` at least surfaced a `stockmovement` grid. Fixed with the identical opt-in (`incljobs=1`, checkbox `#ihInclJobs`): job pass scoped to the single item (`ji.inventoryId = ?`) merged into the monthly buckets + KPIs (combined), with `jobKpi{qty,net,gp}` returned separately → "of which Job Qty/Job Net" tiles + per-month `job N` badge; job dispatches also added to the unified tx list as module **Job Dispatch** (link `jobedit&id=`, returns negative). Same `NOT EXISTS(invoiceitems src='job' AND srcid=ji.id committed)` guard. Reconciles with Product Sales Performance. **Audit finding:** the ONLY other un-modernised item-sold reports with this blindspot are `topsellingsave.php` (getHist/getInv) and legacy `invitemhistsave.php`→`invitemdetail.inc` — NOT yet fixed (user opted to fix Item History only). `invstckmovement`/`invReorderInsights`/`invDemandFinishGoods` already read stockmovement/jobInventory (no blindspot); header-level financial/GL/AR/quote/per-job reports count the lump-sum line correctly by design. **Sanity-checked against PROD `108-1481181613`** (Chain-link Fittings cat 7, FY25-26): invoice-line report showed $269K net / 195 items but $519K / 267 items of off-invoice job dispatch was missed (~2×); guard correctly excluded 492 already-invoiced lines ($90K), included 5312 off-invoice ($519K, zero overlap). Concrete case: job 25811 billed by ONE text line (inv 36632, src='job' srcid=0, $32,325) yet dispatched 295 Chain-link Fittings ($8,720); item T3250-SPIGOT (6711) has zero invoice-line sales but 53 units/$3,865 off-invoice. **Docs:** `docs/reporting/inventory.php` "Inventory Analytics" section rewritten; What's New entry added. **Release notes:** prod `releasenotes` ids **124–126** (status=0 unapproved, public=1, module `Inventory`, doc url → reporting/inventory.php) — Shane to approve after docs git deploy. See [[live_inject_release_notes]] + [[feature_release_docs_policy]].