"""Spend & Payables — the expense-waste module (cost-waste brief recs 1/3/4): the first systematic analysis of vendor bills on this platform. 1. TERMS CAPTURE — per supplier: effective terms (per-bill due-date gap — partner-level terms are sparsely populated) vs actual bill→settlement days (payable full-reconcile counterpart date). Paying EARLY = free cash surrendered (the mirror of the receivables free-credit finding). China/import suppliers are deposit-based — DPO stretch is structurally capped, so the book is SPLIT by supplier country; the verb is pay-to-TERMS, never pay-late. 2. DUPLICATE BILLS — same supplier + same amount (±$1) within 45 days, or same non-empty vendor ref. Classic AP recovery: 0.1-0.5% of spend. 3. SPEND CUBE — NON-product bill lines (Stock Interim / inventory accounts excluded) by GL account × supplier × month: category fragmentation (consolidate + rebid) and MAVERICK % = lines that never touched a PO (no purchase_line_id). 4. FREIGHT RECOVERY — outbound freight PAID (the 'Freight Out' expense account) vs freight BILLED to customers (freight/shipping lines on customer invoices). NAW: distributors recapture only ~70%. READ-ONLY. Supplier names normalized with a dependency-free suffix-strip (upgrade path: RapidFuzz+cleanco per the brief). """ import re import datetime as dt import core.odoo as O import core.periods as P DUP_WINDOW_DAYS = 45 EARLY_GRACE_DAYS = 5 # paying ≤5d early is noise, not a finding FREIGHT_OUT_PAT = re.compile(r'freight out|freight-out', re.I) FREIGHT_BILL_PAT = re.compile(r'freight|shipping|delivery', re.I) # product-side interim/inventory accounts excluded from the OPEX cube PRODUCT_ACC_PAT = re.compile(r'stock interim|inventory|goods received', re.I) _SUFFIX = re.compile(r'\b(inc|llc|ltd|corp|corporation|co|company|s\.?a\.?|gmbh)\b\.?', re.I) def _norm_supplier(name): s = _SUFFIX.sub('', str(name or '').upper()) return re.sub(r'[^A-Z0-9 ]', '', s).strip() or str(name or '') def _chunk(ids, n=2000): ids = list(ids) for i in range(0, len(ids), n): yield ids[i:i + n] def _bills(lf, lt): return O.search_read('account.move', [('move_type', '=', 'in_invoice'), ('state', '=', 'posted'), ('invoice_date', '>=', lf), ('invoice_date', '<=', lt)], ['partner_id', 'invoice_date', 'invoice_date_due', 'amount_total', 'amount_untaxed', 'ref', 'payment_state', 'name']) def terms_capture(t=None): """Per-supplier payment discipline over the LTM + the duplicate-bill worklist.""" t = t or P.today() lf, lt = P.ltm(t) bills = _bills(lf, lt) # settlement date per bill: payable line's full-reconcile group, max counterpart date move_ids = [b['id'] for b in bills] pay_lines = [] for ch in _chunk(move_ids): pay_lines += O.search_read('account.move.line', [('move_id', 'in', ch), ('account_id.account_type', '=', 'liability_payable'), ('full_reconcile_id', '!=', False)], ['move_id', 'full_reconcile_id']) fr_by_move = {O.m2o_id(l['move_id']): O.m2o_id(l['full_reconcile_id']) for l in pay_lines} settle = {} for ch in _chunk(list(set(fr_by_move.values())), 4000): for l in O.search_read('account.move.line', [('full_reconcile_id', 'in', ch)], ['full_reconcile_id', 'date']): k = O.m2o_id(l['full_reconcile_id']) d = str(l.get('date') or '')[:10] if k and d: settle[k] = max(settle.get(k, ''), d) # supplier country for the import/domestic split pids = list({O.m2o_id(b['partner_id']) for b in bills}) country = {} for ch in _chunk(pids): for r in O.search_read('res.partner', [('id', 'in', ch)], ['country_id']): country[r['id']] = O.m2o_name(r.get('country_id')) or '(none)' per = {} for b in bills: pid = O.m2o_id(b['partner_id']) inv_d = str(b.get('invoice_date') or '')[:10] due_d = str(b.get('invoice_date_due') or '')[:10] if not pid or not inv_d: continue e = per.setdefault(pid, {'pid': pid, 'supplier': O.m2o_name(b['partner_id']), 'country': country.get(pid, '(none)'), 'bills': 0, 'spend': 0.0, 'terms': [], 'actual': [], 'early_cash': 0.0, 'early_bills': 0}) e['bills'] += 1 e['spend'] += b.get('amount_total') or 0.0 terms_days = None if due_d and due_d >= inv_d: terms_days = (dt.date.fromisoformat(due_d) - dt.date.fromisoformat(inv_d)).days e['terms'].append(terms_days) fr = fr_by_move.get(b['id']) pay_d = settle.get(fr, '') if fr else '' if pay_d and pay_d >= inv_d: actual = (dt.date.fromisoformat(pay_d) - dt.date.fromisoformat(inv_d)).days e['actual'].append(actual) if terms_days is not None and actual < terms_days - EARLY_GRACE_DAYS: early = terms_days - actual e['early_cash'] += early / 365.0 * (b.get('amount_total') or 0.0) e['early_bills'] += 1 def _med(v): v = sorted(v) return v[len(v) // 2] if v else None rows = [] for e in per.values(): seg = ('(country unset)' if e['country'] == '(none)' else 'import' if e['country'] not in ('United States', 'Canada') else 'domestic') rows.append({'pid': e['pid'], 'supplier': e['supplier'], 'country': e['country'], 'segment': seg, 'bills': e['bills'], 'spend': e['spend'], 'terms_days': _med(e['terms']), 'actual_days': _med(e['actual']), 'early_bills': e['early_bills'], 'early_cash': e['early_cash']}) rows.sort(key=lambda x: -x['early_cash']) # duplicate-bill candidates by_partner = {} for b in bills: by_partner.setdefault(O.m2o_id(b['partner_id']), []).append(b) dups = [] for pid, bs in by_partner.items(): bs = sorted(bs, key=lambda x: str(x.get('invoice_date') or '')) for i, a in enumerate(bs): for c in bs[i + 1:]: da = str(a.get('invoice_date') or '')[:10] dc = str(c.get('invoice_date') or '')[:10] if not da or not dc: continue gap = (dt.date.fromisoformat(dc) - dt.date.fromisoformat(da)).days if gap > DUP_WINDOW_DAYS: break amt_a, amt_c = a.get('amount_total') or 0.0, c.get('amount_total') or 0.0 same_ref = (a.get('ref') and a.get('ref') == c.get('ref')) # same vendor ref = strong signal at any gap; without a ref, only a same-amount # bill within 7 days counts (monthly recurring bills — rent, loans, standing # POs — land ~28-31 days apart and are NOT duplicates) if abs(amt_a - amt_c) <= 1.0 and amt_a > 100 and (same_ref or gap <= 7): dups.append({'supplier': O.m2o_name(a['partner_id']), 'bill_a': a['name'], 'bill_b': c['name'], 'date_a': da, 'date_b': dc, 'amount': amt_a, 'same_ref': 'yes' if same_ref else '', 'ref': str(a.get('ref') or '')[:24]}) dups.sort(key=lambda x: -x['amount']) return {'rows': rows, 'dups': dups, 'n_bills': len(bills), 'spend_total': sum(b.get('amount_total') or 0 for b in bills), 'early_cash_total': sum(r['early_cash'] for r in rows), 'dup_exposure': sum(d['amount'] for d in dups), 'window': (lf, lt)} def _bill_lines(lf, lt): lines = [] # account.move.line.display_type is a SELECTION ('product'/'tax'/'payment_term'/…), # not a boolean — '= False' matches nothing (found live 2026-07-05) got = O.search_read('account.move.line', [('move_id.move_type', '=', 'in_invoice'), ('parent_state', '=', 'posted'), ('move_id.invoice_date', '>=', lf), ('move_id.invoice_date', '<=', lt), ('display_type', '=', 'product'), ('price_subtotal', '!=', 0)], ['partner_id', 'account_id', 'price_subtotal', 'purchase_line_id', 'product_id', 'date']) acc_ids = list({O.m2o_id(l['account_id']) for l in got if l.get('account_id')}) atype = {} for ch in _chunk(acc_ids): for a in O.search_read('account.account', [('id', 'in', ch)], ['account_type']): atype[a['id']] = a.get('account_type') or '' for l in got: l['account'] = O.m2o_name(l.get('account_id')) l['atype'] = atype.get(O.m2o_id(l.get('account_id')), '') lines.append(l) return lines def spend_cube(t=None): """OPEX spend by GL category × supplier(normalized), with fragmentation and no-PO %. Scope = expense-type accounts ONLY (account_type 'expense'/'expense_depreciation'): inventory-interim (asset) and loan/advance (liability) bill lines are excluded by TYPE, and COGS (expense_direct_cost) belongs to the product side. Note: this company runs no PO process for indirect spend, so no-PO% ≈ 100% by construction — it becomes a signal only if POs are ever adopted for indirect purchases.""" t = t or P.today() lf, lt = P.ltm(t) lines = [l for l in _bill_lines(lf, lt) if l.get('atype') in ('expense', 'expense_depreciation')] cats, sup_cat = {}, {} for l in lines: acc = l['account'] amt = l.get('price_subtotal') or 0.0 sup = _norm_supplier(O.m2o_name(l.get('partner_id'))) c = cats.setdefault(acc, {'category': acc, 'spend': 0.0, 'lines': 0, 'maverick': 0.0, 'suppliers': set()}) c['spend'] += amt c['lines'] += 1 c['suppliers'].add(sup) if not l.get('purchase_line_id'): c['maverick'] += amt k = (acc, sup) sup_cat[k] = sup_cat.get(k, 0.0) + amt rows = [] for c in cats.values(): n_sup = len(c['suppliers']) top = sorted(((s, v) for (a, s), v in sup_cat.items() if a == c['category']), key=lambda kv: -kv[1]) rows.append({'category': c['category'], 'spend': c['spend'], 'lines': c['lines'], 'n_suppliers': n_sup, 'maverick_pct': (c['maverick'] / c['spend'] * 100) if c['spend'] else 0.0, 'top_supplier': top[0][0][:30] if top else '', 'top_share_pct': (top[0][1] / c['spend'] * 100) if (top and c['spend']) else 0.0, 'consolidate': ('yes' if n_sup >= 3 and c['spend'] > 10000 else '')}) rows.sort(key=lambda x: -x['spend']) return {'rows': rows, 'spend_total': sum(r['spend'] for r in rows), 'maverick_total': sum(r['spend'] * r['maverick_pct'] / 100 for r in rows), 'frag_categories': [r for r in rows if r['consolidate']], 'window': (lf, lt)} def freight_recovery(t=None): """Outbound freight paid (the 'Freight Out' expense account) vs freight billed to customers (freight/shipping/delivery lines on out_invoices), monthly + the headline recovery %. Company-level.""" t = t or P.today() lf, lt = P.ltm(t) paid_lines = [l for l in _bill_lines(lf, lt) if l['account'] and FREIGHT_OUT_PAT.search(l['account'])] paid_m = {} for l in paid_lines: m = str(l.get('date') or '')[:7] paid_m[m] = paid_m.get(m, 0.0) + (l.get('price_subtotal') or 0.0) billed = O.search_read('account.move.line', [('move_id.move_type', '=', 'out_invoice'), ('parent_state', '=', 'posted'), ('move_id.invoice_date', '>=', lf), ('move_id.invoice_date', '<=', lt), ('display_type', '=', 'product')], ['account_id', 'product_id', 'price_subtotal', 'date', 'partner_id']) billed_lines = [l for l in billed if FREIGHT_BILL_PAT.search(O.m2o_name(l.get('product_id')) or '') or FREIGHT_BILL_PAT.search(O.m2o_name(l.get('account_id')) or '')] billed_m, billed_cust = {}, {} for l in billed_lines: m = str(l.get('date') or '')[:7] amt = l.get('price_subtotal') or 0.0 billed_m[m] = billed_m.get(m, 0.0) + amt pid = O.m2o_id(l.get('partner_id')) if pid: billed_cust[pid] = billed_cust.get(pid, 0.0) + amt months = sorted(set(paid_m) | set(billed_m)) monthly = [{'month': m, 'paid': paid_m.get(m, 0.0), 'billed': billed_m.get(m, 0.0)} for m in months] paid_total = sum(paid_m.values()) billed_total = sum(billed_m.values()) return {'monthly': monthly, 'paid_total': paid_total, 'billed_total': billed_total, 'recovery_pct': (billed_total / paid_total * 100) if paid_total else None, 'n_billed_customers': len(billed_cust), 'window': (lf, lt)} def validate(t=None, team_id=None, pre=None): """(1) bill headline ties the server sum over the same domain; (2) the cube total + excluded product-account lines re-adds to the full bill-line total (nothing dropped silently).""" t = t or P.today() tc = pre or terms_capture(t) lf, lt = tc['window'] srv = O.sum_field('account.move', [('move_type', '=', 'in_invoice'), ('state', '=', 'posted'), ('invoice_date', '>=', lf), ('invoice_date', '<=', lt)], 'amount_total') a = tc['spend_total'] checks = [{'check': 'vendor-bill spend — client Σ == server Σ', 'a': round(a, 2), 'b': round(srv, 2), 'gap': round(a - srv, 2), 'ok': abs(a - srv) <= max(1.0, srv * 0.001)}] all_lines = _bill_lines(lf, lt) full = sum(l.get('price_subtotal') or 0 for l in all_lines) cube = spend_cube(t) excluded = sum(l.get('price_subtotal') or 0 for l in all_lines if l.get('atype') not in ('expense', 'expense_depreciation')) lhs = cube['spend_total'] + excluded checks.append({'check': 'opex cube + non-expense-type lines == all bill lines', 'a': round(lhs, 2), 'b': round(full, 2), 'gap': round(lhs - full, 2), 'ok': abs(lhs - full) < 1.0}) return checks