"""Procurement module — biweekly buy list + the full Qualify-tab workflow, rebuilt from the Royal Imports Inventory System (v24/v25). Master data (Supplier / Country / Lead / CBM / container / first cost) comes from the curated map `procurement_suppliers.json` (~3,960 SKUs from the Inventory System Mastersheet). The team's live edits (change vendor/country/cost) are overlaid in the UI from a `procurement_overrides` store, so a re-vendored SKU regroups instantly without re-pulling Odoo. Alternative vendors + prices come from Odoo `product.supplierinfo` (the cheapest-vendor view). Demand / on-hand / incoming POs are live from Odoo (read-only). Reorder math (validated on the v24.4 Backend): 8-month demand = units sold same 8-month window last year (all channels) avg/day = 8M demand / window days days of cover = (on-hand + incoming PO) / avg/day reorder point = lead-time days * avg/day Reorder QTY = ceil(reorder point * 1.05) [Baseline] Reorder +trend = Reorder QTY * (YTD units this yr / last yr) BUY TODAY = days of cover < lead time READ-ONLY on Odoo; the team's Order Qty + master-data edits persist in the platform store. """ import sys import json import math import calendar import datetime as dt from pathlib import Path sys.path.insert(0, str(Path(__file__).resolve().parents[1])) from functools import lru_cache import core.odoo as O import core.periods as P _HERE = Path(__file__).resolve().parent _SAFETY = 0.05 _PROJ_MONTHS = 8 _NO_SVC = [('product_id.type', '!=', 'service')] @lru_cache(maxsize=1) def suppliers(): """SKU(default_code) -> {desc, unit, qty_per_unit, first_cost, cbm, max_container, vendor, country, lead} from the curated map.""" f = _HERE.parent / 'procurement_suppliers.json' try: return json.loads(f.read_text(encoding='utf-8')) except Exception: return {} def _add_months(d, n): m = d.month - 1 + n y = d.year + m // 12 m = m % 12 + 1 return dt.date(y, m, min(d.day, calendar.monthrange(y, m)[1])) def _proj_window(t): return t, _add_months(t, _PROJ_MONTHS) def _demand_domain(a, b): return [('order_id.state', 'in', ('sale', 'done')), ('order_id.date_order', '>=', a), ('order_id.date_order', '<=', b)] + _NO_SVC def _units(a, b): g = O.read_group('sale.order.line', _demand_domain(a, b), ['product_uom_qty:sum'], ['product_id'], lazy=False) return {O.m2o_id(r['product_id']): (r.get('product_uom_qty') or 0.0) for r in g if r.get('product_id')} def _incoming_po(): out = {} for r in O.read_group('purchase.order.line', [('state', '=', 'purchase')], ['product_qty:sum', 'qty_received:sum'], ['product_id'], lazy=False): pid = O.m2o_id(r.get('product_id')) q = (r.get('product_qty') or 0.0) - (r.get('qty_received') or 0.0) if pid and q > 0: out[pid] = q return out def _bought_12m(cutoff_iso): """Units PURCHASED from suppliers in the last 12 months (confirmed POs), per product — the reference for 'how much we actually bought last year' next to the projected need.""" out = {} for r in O.read_group('purchase.order.line', [('order_id.state', 'in', ('purchase', 'done')), ('order_id.date_order', '>=', cutoff_iso)], ['product_qty:sum'], ['product_id'], lazy=False): pid = O.m2o_id(r.get('product_id')) if pid: out[pid] = r.get('product_qty') or 0.0 return out def _supplierinfo_by_tmpl(): """{template_id: [{'vendor','vendor_id','price'}...]} — Odoo's vendors per product, for the multiple-vendor / cheapest-price views (Odoo prices are populated; lead times are not).""" out = {} for s in O.search_read('product.supplierinfo', [], ['product_tmpl_id', 'partner_id', 'price'], limit=100000): t = O.m2o_id(s.get('product_tmpl_id')) if not t: continue out.setdefault(t, []).append({'vendor': O.m2o_name(s.get('partner_id')), 'vendor_id': O.m2o_id(s.get('partner_id')), 'price': s.get('price') or 0.0}) return out def _preorders(cutoff_iso): """Per-product open customer demand (undelivered units on confirmed orders) + earliest need-by date. STORED fields only — `qty_to_deliver` is a computed field and filtering/aggregating it forces Odoo to compute across the whole line population (8+ min); instead sum the stored `product_uom_qty - qty_delivered`. Bounded to orders since `cutoff_iso` so ancient never-closed backorders don't dominate the figures. Returns ({pid: qty}, {pid: 'YYYY-MM-DD'}).""" dom = [('order_id.state', '=', 'sale'), ('order_id.date_order', '>=', cutoff_iso)] qty = {} for r in O.read_group('sale.order.line', dom, ['product_uom_qty:sum', 'qty_delivered:sum'], ['product_id'], lazy=False): pid = O.m2o_id(r.get('product_id')) d = (r.get('product_uom_qty') or 0.0) - (r.get('qty_delivered') or 0.0) if pid and d > 0: qty[pid] = d # need-by: only never-shipped lines (qty_delivered is STORED, so this filter is cheap) AND only # for products that actually have open qty — need_by is consumed ONLY when preorder_qty>0 (pid in # qty), so scoping the pull to those product_ids is both correct and much faster (~849 of ~2,870 # products → the search drops from ~8.4s to ~5s; the other ~2,000 products' rows were pure waste). open_pids = list(qty) if not open_pids: return qty, {} lines = O.search_read('sale.order.line', dom + [('qty_delivered', '=', 0), ('product_id', 'in', open_pids)], ['product_id', 'order_id'], limit=400000) odate = {} oids = list({O.m2o_id(l['order_id']) for l in lines if l.get('order_id')}) for o in O.search_read('sale.order', [('id', 'in', oids)], ['commitment_date', 'date_order']): d = o.get('commitment_date') or o.get('date_order') if d: odate[o['id']] = str(d)[:10] need = {} for l in lines: pid, oid = O.m2o_id(l['product_id']), O.m2o_id(l['order_id']) d = odate.get(oid) if pid and d and (pid not in need or d < need[pid]): need[pid] = d return qty, need def recommendations(t=None): """Base per-SKU table (curated master data + live Odoo demand/stock/PO + alt-vendors). The UI overlays the team's vendor/country/cost edits on top; reorder methods + CBM/container are here.""" t = t or P.today() sup = suppliers() if not sup: return [] codeset = set(sup.keys()) ps, pe = _proj_window(t) window_days = max(1, (pe - ps).days) d8a, d8b = _add_months(t, -12).isoformat(), _add_months(pe, -12).isoformat() yf, yt = P.ytd(t) lf, lt = P.ytd_last_year(t) prods, demand8, ytd_now, ytd_prev, inpo, si, preorders, bought = O.parallel([ lambda: O.search_read('product.product', [('default_code', '!=', False), ('active', 'in', [True, False])], ['default_code', 'name', 'qty_available', 'standard_price', 'product_tmpl_id']), lambda: _units(d8a, d8b), lambda: _units(yf, yt), lambda: _units(lf, lt), _incoming_po, _supplierinfo_by_tmpl, lambda: _preorders(d8a), lambda: _bought_12m(d8a), ]) pre_qty, pre_need = preorders today_iso = t.isoformat() by_code = {} for p in prods: c = (p.get('default_code') or '').strip() if c and c in codeset and c not in by_code: by_code[c] = p if not by_code: return [] rows = [] for code, p in by_code.items(): pid = p['id'] meta = sup.get(code, {}) lead = meta.get('lead') or 0 tmpl = O.m2o_id(p.get('product_tmpl_id')) alts = si.get(tmpl, []) priced = sorted((a for a in alts if (a.get('price') or 0) > 0), key=lambda a: a['price']) cheapest = priced[0] if priced else None cost = (meta.get('first_cost') or (cheapest['price'] if cheapest else None) or p.get('standard_price') or 0.0) d8 = demand8.get(pid, 0.0) avg_day = d8 / window_days if window_days else 0.0 on_hand = p.get('qty_available') or 0.0 po = inpo.get(pid, 0.0) avail = on_hand + po days_cover = (avail / avg_day) if avg_day > 0 else (None if avail <= 0 else 9999.0) reorder_pt = lead * avg_day rbase = math.ceil(reorder_pt * (1 + _SAFETY)) if reorder_pt > 0 else 0 yn, yp = ytd_now.get(pid, 0.0), ytd_prev.get(pid, 0.0) trend = (yn / yp) if yp > 0 else None rtrend = int(round(rbase * trend)) if trend is not None else rbase buy = (days_cover is not None and lead > 0 and avg_day > 0 and days_cover < lead) rows.append({ 'code': code, 'product': meta.get('desc') or p.get('name') or code, 'unit': meta.get('unit') or '', 'qty_per_unit': meta.get('qty_per_unit'), 'vendor': meta.get('vendor') or '(no supplier)', 'country': meta.get('country') or '', 'lead': lead, 'on_hand': on_hand, 'incoming_po': po, 'demand_8m': d8, 'avg_day': avg_day, 'bought_12m': bought.get(pid, 0.0), 'days_cover': days_cover, 'reorder_baseline': rbase, 'reorder_trend': rtrend, 'trend_pct': ((trend - 1) * 100) if trend is not None else None, 'ytd_units': yn, 'ytd_units_ly': yp, 'unit_cost': cost, 'buy': buy, 'cbm': meta.get('cbm'), 'max_container': meta.get('max_container'), 'preorder_qty': pre_qty.get(pid, 0.0), 'need_by': pre_need.get(pid) if pre_qty.get(pid, 0) > 0 else None, 'overdue': bool(pre_qty.get(pid, 0) > 0 and pre_need.get(pid) and pre_need.get(pid) < today_iso), 'email': meta.get('email'), 'city': meta.get('city'), 'street': meta.get('street'), 'port': meta.get('port'), 'n_vendors': len({a['vendor_id'] for a in alts if a.get('vendor_id')}), 'cheapest_vendor': cheapest['vendor'] if cheapest else None, 'cheapest_price': cheapest['price'] if cheapest else None, 'alt_vendors': priced, }) return rows def purchase_need_key(r): """Sort key = 'what we need to purchase', most urgent first. SKUs flagged BUY (past the reorder point) come before OK SKUs; within each, lower days-of-cover (closer to stocking out) ranks higher; a bigger suggested reorder breaks ties. No-demand / fully-covered SKUs (days_cover None or 9999) sink to the bottom. This is what orders the per-vendor line list so that in 'All SKUs' mode the actionable SKUs float to the top while the whole catalogue stays visible below.""" dc = r.get('days_cover') if dc is None or dc >= 9999: dc = float('inf') return (0 if r.get('buy') else 1, dc, -(r.get('reorder_trend') or 0)) def by_supplier(recs): """Group rows into per-vendor purchase orders (pure — call with overrides already applied). Each vendor's lines are ordered by purchase need (buy-first, most urgent on top — see purchase_need_key), so the buy list and the 'All SKUs per vendor' view surface what to order without hunting; the full SKU set for the vendor is still present, just sorted underneath.""" pos = {} for r in recs: v = r['vendor'] po = pos.setdefault(v, {'vendor': v, 'country': r.get('country', ''), 'lead': r.get('lead', 0), 'lines': [], 'skus': 0}) po['lines'].append(r) po['skus'] += 1 for po in pos.values(): po['lines'].sort(key=purchase_need_key) return sorted(pos.values(), key=lambda x: (x['vendor'] or '~')) def data_quality(): sup = suppliers() codes = list(sup.keys()) cs = set(codes) found = {c for c in ((p.get('default_code') or '').strip() for p in O.search_read('product.product', [('default_code', '!=', False), ('active', 'in', [True, False])], ['default_code'])) if c in cs} missing = [c for c in codes if c not in found] no_vendor = [c for c, d in sup.items() if not d.get('vendor')] no_lead = [c for c, d in sup.items() if not d.get('lead')] return [ {'issue': 'Curated SKU not found in Odoo (no live data)', 'count': len(missing), 'fix': 'Check the SKU internal reference matches Odoo, or remove it from the mapping.', 'sample': [{'default_code': c, 'name': sup.get(c, {}).get('desc')} for c in missing]}, {'issue': 'No supplier in the mapping', 'count': len(no_vendor), 'fix': 'Set the vendor on the SKU (Procurement page or Vendor module) so a PO can be grouped.', 'sample': [{'default_code': c, 'name': sup.get(c, {}).get('desc')} for c in no_vendor]}, {'issue': 'No lead time in the mapping', 'count': len(no_lead), 'fix': 'Set the supplier lead time (days) — the buy trigger needs it.', 'sample': [{'default_code': c, 'name': sup.get(c, {}).get('desc')} for c in no_lead]}, ] def _open_po_lines(): """All confirmed-PO lines still (partly) undelivered: state='purchase', open qty > 0, with the line's expected date. One pull powers the PO board, the per-SKU inbound match and the late chase list. (Same open-qty semantics as _incoming_po — the two are cross-validated.)""" lines = O.search_read('purchase.order.line', [('state', '=', 'purchase'), ('product_qty', '>', 0)], ['order_id', 'partner_id', 'product_id', 'product_qty', 'qty_received', 'price_unit', 'price_subtotal', 'date_planned']) out = [] for r in lines: open_qty = (r.get('product_qty') or 0) - (r.get('qty_received') or 0) if open_qty <= 1e-3: continue qty = r.get('product_qty') or 0 unit = (r['price_subtotal'] / qty) if qty else (r.get('price_unit') or 0) r['open_qty'] = open_qty r['open_value'] = open_qty * unit r['expected'] = str(r.get('date_planned') or '')[:10] out.append(r) return lines, out def open_pos(t=None): """The open-PO board matched to procurement: order-level list of every confirmed PO with undelivered quantity, per-SKU inbound (open qty + earliest ETA + late portion) keyed by default_code so it joins the buy list, and the late-delivery chase list.""" t = t or P.today() today = t.isoformat() _all, lines = _open_po_lines() pids = list({O.m2o_id(l['product_id']) for l in lines if l.get('product_id')}) codes = {} for i in range(0, len(pids), 5000): for p in O.search_read('product.product', [('id', 'in', pids[i:i + 5000]), ('active', 'in', [True, False])], ['default_code']): codes[p['id']] = (p.get('default_code') or '').strip() oids = list({O.m2o_id(l['order_id']) for l in lines if l.get('order_id')}) odates = {} for i in range(0, len(oids), 5000): for o_ in O.search_read('purchase.order', [('id', 'in', oids[i:i + 5000])], ['date_order', 'amount_total']): odates[o_['id']] = {'date_order': str(o_.get('date_order') or '')[:10], 'amount_total': o_.get('amount_total') or 0.0} by_po, by_sku, late_rows = {}, {}, [] for l in lines: po_id = O.m2o_id(l['order_id']) po_nm = O.m2o_name(l['order_id']) sup = O.m2o_name(l['partner_id']) pid = O.m2o_id(l['product_id']) code = codes.get(pid) or O.m2o_name(l['product_id']) exp = l['expected'] is_late = bool(exp and exp < today) e = by_po.setdefault(po_nm, { 'po': po_nm, 'supplier': sup, 'ordered': odates.get(po_id, {}).get('date_order', ''), 'po_value': odates.get(po_id, {}).get('amount_total', 0.0), 'expected': exp, 'open_lines': 0, 'open_value': 0.0, 'late_value': 0.0}) e['open_lines'] += 1 e['open_value'] += l['open_value'] if exp and (not e['expected'] or exp < e['expected']): e['expected'] = exp if is_late: e['late_value'] += l['open_value'] s = by_sku.setdefault(code, {'open_qty': 0.0, 'open_value': 0.0, 'eta': None, 'late_qty': 0.0, 'pos': set()}) s['open_qty'] += l['open_qty'] s['open_value'] += l['open_value'] s['pos'].add(po_nm) if exp and (s['eta'] is None or exp < s['eta']): s['eta'] = exp if is_late: s['late_qty'] += l['open_qty'] if is_late: days_late = (t - dt.date.fromisoformat(exp)).days late_rows.append({'supplier': sup, 'po': po_nm, 'sku': code, 'product': O.m2o_name(l['product_id']), 'open_qty': l['open_qty'], 'open_value': l['open_value'], 'expected': exp, 'days_late': days_late}) for s in by_sku.values(): s['pos'] = sorted(s['pos']) pos = sorted(by_po.values(), key=lambda x: (x['expected'] or '9999')) late_rows.sort(key=lambda x: -x['days_late']) return { 'pos': pos, 'by_sku': by_sku, 'late': late_rows, 'n_pos': len(pos), 'open_value': sum(p['open_value'] for p in pos), 'late_value': sum(p['late_value'] for p in pos), 'n_late_lines': len(late_rows), } def _pctl(sorted_vals, q): return sorted_vals[min(len(sorted_vals) - 1, int(len(sorted_vals) * q))] if sorted_vals else None def vendor_reliability(t=None): """Vendor delivery truth from COMPLETED POs (24m): actual lead (date_order → effective_date) and promise lateness (date_planned → effective_date), per vendor. purchase.order carries all three dates, so no picking join is needed (the 'incoming picking' route is polluted by customer returns — verified live: only 29% of incoming pickings match a PO). The buy trigger runs on the CURATED nominal lead; this measures how wrong that is per vendor.""" t = t or P.today() cutoff = (t - dt.timedelta(days=730)).isoformat() pos_ = O.search_read('purchase.order', [('state', 'in', ('purchase', 'done')), ('date_order', '>=', cutoff), ('effective_date', '!=', False)], ['name', 'partner_id', 'date_order', 'date_planned', 'effective_date', 'amount_total']) # nominal lead per vendor = the most common curated per-SKU lead for that vendor (upper-name join) nom = {} for d in suppliers().values(): v = (d.get('vendor') or '').strip().upper() if v and d.get('lead'): nom.setdefault(v, {}) nom[v][d['lead']] = nom[v].get(d['lead'], 0) + 1 nominal = {v: max(c.items(), key=lambda kv: kv[1])[0] for v, c in nom.items()} rows, per = [], {} for p in pos_: v = (O.m2o_name(p.get('partner_id')) or '(unknown)').strip() o = dt.date.fromisoformat(str(p['date_order'])[:10]) e = dt.date.fromisoformat(str(p['effective_date'])[:10]) lead = (e - o).days late = None if p.get('date_planned'): late = (e - dt.date.fromisoformat(str(p['date_planned'])[:10])).days rows.append({'po': p['name'], 'vendor': v, 'ordered': o.isoformat(), 'promised': str(p.get('date_planned') or '')[:10] or None, 'received': e.isoformat(), 'lead_days': lead, 'days_late': late, 'po_value': p.get('amount_total') or 0.0}) s = per.setdefault(v, {'vendor': v, 'n_pos': 0, 'value': 0.0, 'leads': [], 'lates': []}) s['n_pos'] += 1 s['value'] += p.get('amount_total') or 0.0 s['leads'].append(lead) if late is not None: s['lates'].append(late) vendors = [] for s in per.values(): s['leads'].sort() s['lates'].sort() vu = s['vendor'].upper() v_nom = nominal.get(vu) p90 = _pctl(s['leads'], 0.9) vendors.append({'vendor': s['vendor'], 'n_pos': s['n_pos'], 'value': s['value'], 'lead_med': _pctl(s['leads'], 0.5), 'lead_p90': p90, 'nominal_lead': v_nom, 'lead_gap': (p90 - v_nom) if (v_nom and p90 is not None) else None, 'on_promise_pct': (sum(1 for d in s['lates'] if d <= 0) / len(s['lates']) * 100) if s['lates'] else None, 'late_med': _pctl(s['lates'], 0.5), 'late_p90': _pctl(s['lates'], 0.9)}) vendors.sort(key=lambda x: -x['value']) all_lates = sorted(r['days_late'] for r in rows if r['days_late'] is not None) return {'vendors': vendors, 'rows': rows, 'n_pos': len(rows), 'n_late': sum(1 for d in all_lates if d > 0), 'late_share_pct': (sum(1 for d in all_lates if d > 0) / len(all_lates) * 100) if all_lates else None, 'late_med': _pctl(all_lates, 0.5), 'late_p90': _pctl(all_lates, 0.9), 'n_vendors': len(vendors), 'actual_p90_by_vendor': {v['vendor'].upper(): v['lead_p90'] for v in vendors if v['n_pos'] >= 3 and v['lead_p90'] is not None}} def buy_under_actual_lead(recs, vr): """SKUs the nominal-lead trigger says are SAFE but that are already past the reorder point under their vendor's ACTUAL p90 lead (vendors with 3+ completed POs). The honest delta — the base buy list is untouched.""" p90 = vr.get('actual_p90_by_vendor') or {} out = [] for r in recs: if r.get('buy'): continue ap = p90.get((r.get('vendor') or '').strip().upper()) if ap is None or r.get('days_cover') is None or r['days_cover'] >= 9999: continue eff = max(ap, r.get('lead') or 0) if r.get('avg_day', 0) > 0 and r['days_cover'] < eff: out.append({**r, 'actual_lead_p90': ap}) out.sort(key=lambda x: (x['days_cover'] if x['days_cover'] is not None else 9999)) return out def summary(t=None): t = t or P.today() recs = recommendations(t) ps, pe = _proj_window(t) vr = vendor_reliability(t) return { 'recs': recs, 'n_skus': len(recs), 'window_days': max(1, (pe - ps).days), 'safety_pct': _SAFETY * 100, 'inbound': open_pos(t), 'data_quality': data_quality(), 'vendor_reliability': vr, 'buy_flip': buy_under_actual_lead(recs, vr), 'pulled_at': dt.datetime.now().strftime('%Y-%m-%d %H:%M'), } def vendor_book(t=None): """Per-vendor rollup for the Vendor module: each vendor's SKU count, lead, country, demand value, and the SKUs they supply. Plus the multiple-vendor / cheapest-price view.""" recs = recommendations(t) multi = [r for r in recs if (r.get('n_vendors') or 0) > 1] return {'recs': recs, 'multi_vendor': multi, 'n_multi': len(multi)} def validate(t=None, team_id=None): t = t or P.today() _, pe = _proj_window(t) f, to = _add_months(t, -12).isoformat(), _add_months(pe, -12).isoformat() g = O.read_group('sale.order.line', _demand_domain(f, to), ['product_uom_qty:sum'], ['product_id'], lazy=False) per = sum((r.get('product_uom_qty') or 0.0) for r in g if r.get('product_id')) tot = O.sum_field('sale.order.line', _demand_domain(f, to), 'product_uom_qty') checks = [{'check': '8-month demand: Σ(per-SKU units) == line-level units', 'a': round(per, 2), 'b': round(tot, 2), 'gap': round(per - tot, 2), 'ok': abs(per - tot) <= 1.0}] # Open POs: row-by-row NET (product_qty − qty_received over ALL confirmed lines — the same # semantics as _incoming_po, over-receipts included) vs the _incoming_po read_group aggregate. # Two aggregation paths, small live-drift tolerance (two RPC snapshots on a live ledger). ib = open_pos(t) all_lines, _ = _open_po_lines() row_net = sum((l.get('product_qty') or 0) - (l.get('qty_received') or 0) for l in all_lines) agg_net = sum(_incoming_po().values()) checks.append({'check': 'Open POs: row-level Σ net qty == read_group Σ net qty (live-drift tol)', 'a': round(row_net, 2), 'b': round(agg_net, 2), 'gap': round(row_net - agg_net, 2), 'ok': abs(row_net - agg_net) <= max(1.0, abs(agg_net) * 0.001)}) # Internal consistency: the late chase list is exactly the late portion of the SKU rollup. late_val = sum(r['open_value'] for r in ib['late']) sku_late_val_ok = sum(p['late_value'] for p in ib['pos']) checks.append({'check': 'Open POs: Σ(late chase lines) == Σ(per-PO late value)', 'a': round(late_val, 2), 'b': round(sku_late_val_ok, 2), 'gap': round(late_val - sku_late_val_ok, 2), 'ok': abs(late_val - sku_late_val_ok) <= 1.0}) # Vendor reliability: the per-vendor rollup partitions the PO rows exactly (counts AND value). vr = vendor_reliability(t) vn = sum(v['n_pos'] for v in vr['vendors']) checks.append({'check': 'Vendor reliability: Σ(per-vendor POs) == PO rows pulled', 'a': vn, 'b': vr['n_pos'], 'gap': vn - vr['n_pos'], 'ok': vn == vr['n_pos']}) vv = sum(v['value'] for v in vr['vendors']) rv = sum(r['po_value'] for r in vr['rows']) checks.append({'check': 'Vendor reliability: Σ(per-vendor PO value) == Σ(row PO value)', 'a': round(vv, 2), 'b': round(rv, 2), 'gap': round(vv - rv, 2), 'ok': abs(vv - rv) <= 1.0}) return checks