Inventory ABC Analysis

Inventory ABC Analysis — Technical Spec

Screen + job registry: app/im2.py (ABC_JOBS, _abc_worker, routes below). Report engine: app/abc_report.py. Template: app/templates/abc.html. QuickBooks access: app/qb.py (QBO, the droplet's own Intuit app).

A port of skills/qb_inventory_activity/scripts/{inv_pull,build_costed_report}.py — same methodology, same layout, same cost-basis priority.

Routes

All four depend on current_user — any signed-in role can run the report. Only abc_write (admin) may set update_im2; the run route 403s otherwise, and the select is not rendered for non-admins.

Validation on /run

• a_pct, b_pct float, window_days int, else HTTP 400.
• a_pct <= 0 or b_pct <= 0 or a_pct + b_pct >= 100 → 400.
• window_days must be exactly 90, 180 or 365 → 400.
• include_on_hand / update_im2 truthy set: yes|y|true|1.
• Concurrency: max 2 running jobs, else HTTP 429.

Job registry

ABC_JOBS is a plain in-process dict, one entry per run: {state, message, user, started, filename, blob}. Entries older than 7200 s are dropped on the next /run. The xlsx lives in memory in job["blob"] — nothing is written to disk and every job is lost on app restart. _abc_worker runs on a daemon threading.Thread and writes progress strings into job["message"]; the page polls status every 2 s.

Pull (abc_report.pull)

_page() pages 100 at a time via qbo.query:

• Item WHERE Type = 'Inventory' AND Active = true (ORDERBY Id)
• Invoice, SalesReceipt, Bill, Purchase, CreditMemo WHERE TxnDate >= <today - window_days> (ORDERBY TxnDate)

CreditMemo is pulled but never consumed in build() — see limits.

Math (abc_report.build)

• Receipts/cost layers: Bill + Purchase, lines with ItemBasedExpenseLineDetail, Qty>0 and Amount → unit cost Amount/Qty; in-window rows also add to recv_q/recv_amt.
• Issues: Invoice + SalesReceipt, lines with SalesItemLineDetail → iss_q. All pulled rows count, i.e. the whole window.
• FIFO WAC: layers sorted newest first, consumed up to QtyOnHand; full coverage → FIFO wtd avg cost; short coverage with a QB cost → blended (tv + gap*cost)/qoh; short coverage with no QB cost → partial-layer average labelled with the coverage %.
• Cost fallback chain: FIFO → PurchaseCost → avg receipt in window → last paid PO (hist sorted by date, last) → difflib.get_close_matches on the normalised item name at cutoff=0.85 among items with a cost (only when qoh is non-zero), with a hard-coded AF30- × 0.75 adjustment → Uncosted — no basis in QB (ref = 0).
• IM2 flags: _no_cost_flags(conn) = select sku, coalesce(no_cost_reason,'other') from items where no_cost_expected, keyed by SKU. A flagged item is forced to ref = 0, abcval = 0, source No cost expected (IM2: <reason>). When the flag table returns nothing at all, the code falls back to a regex on name/sku/description for \b(surplus|used)\b → Surplus/Used — $0 per Dave.
• ABC basis: abcq = iss + (qoh if inc_qoh else 0), abcval = abcq * ref. Rows are sorted -abcval; running share run; the class is decided on the pre-item cumulative (prev = run - pct), so the item that crosses a threshold stays in the higher class. abcval == 0 is forced to C.
• Write-back (_abc_worker): select sku, abc_code from items where archived_at is null; for each SKU present with a different code, apply_change(conn, sku, "abc_code", code, user, source="abc-report") — one audit_log row per item.

Workbook

Two sheets, Summary moved to the front. Item Detail columns A–O as listed in the operator page; header style _head(); freeze D2; autofilter A1:O{max}; columns F,G,H,I,J,K hidden. Summary carries the A/B/C band table, a Cost Source rollup (counts + on-hand value) and a Notes block (totals, uncosted count, no-cost-expected count, negative-qoh count, and the standing cautions). Filename Inventory_ABC_Analysis_<ISO date>_{inclOnHand|issuedOnly}.xlsx.

build() returns (fname, bytes, stats, codes); the docstring says it returns three values — cosmetic only.

Known limits

1. CreditMemo is queried every run and never used — returns/credits do not reduce issued quantity, and the pull is slower than it needs to be.
2. Jobs and their spreadsheets are in-process only: an app restart or a second app worker means a running job or a completed download disappears (404).
3. stats/codes come from the same run that produced the file, but a write-back happens after the file is built — the spreadsheet is the record of what was written, and nothing records the ABC run itself except the per-item audit rows.
4. The similar-item cost estimate and the AF30- 0.75 factor are heuristics baked into the code, not configuration.
5. Inventory Adjustments are not a movement type; Qty On Hand is current, not period-start (both stated in the workbook Notes).