Spaces:
Paused
Paused
Download platform/modules/spend.py from aios-loopable/cfo-os: direct link, hf CLI and curl.
- Browser
- Download file 15 kB
-
https://huggingface.co/spaces/aios-loopable/cfo-os/resolve/main/platform/modules/spend.py
- Command line
-
hf download hf://spaces/aios-loopable/cfo-os/platform/modules/spend.py
-
curl -L -o spend.py https://huggingface.co/spaces/aios-loopable/cfo-os/resolve/main/platform/modules/spend.py
15 kB
| """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 |