Spaces:
Paused
Paused
Download platform/modules/pricing.py from aios-loopable/cfo-os: direct link, hf CLI and curl.
- Browser
- Download file 22.5 kB
-
https://huggingface.co/spaces/aios-loopable/cfo-os/resolve/main/platform/modules/pricing.py
- Command line
-
hf download hf://spaces/aios-loopable/cfo-os/platform/modules/pricing.py
-
curl -L -o pricing.py https://huggingface.co/spaces/aios-loopable/cfo-os/resolve/main/platform/modules/pricing.py
22.5 kB
| """Pricing module — per-SKU economics for pricing decisions. | |
| For every SKU (LTM): units, revenue, unit cost, avg selling price, GROSS margin $/%, markup % | |
| (= revenue/COGS − 1), and a fully-loaded NET margin after allocating operating expenses to the SKU. | |
| COST-TO-SKU ALLOCATION (tiered, channel-scoped, SELECTABLE driver): | |
| COGS is per-SKU already (Odoo Margin module). Operating expenses live per analytic | |
| (Fisch/Royal/Amazon/HQ). Each channel pool = its analytic opex; we distribute it across that | |
| channel's SKUs by a chosen DRIVER, and HQ overhead across all SKUs by the same driver: | |
| pool_C = channel_rate_C * Σ(channel revenue) # rate-based magnitude (robust to Amazon | |
| opex(sku)= Σ_C pool_C * driverC(sku)/Σ driverC + pool_HQ * driver(sku)/Σ driver | |
| # invoice revenue not fully visible per-SKU) | |
| Driver options: | |
| - 'cogs' : cost-weighted (default) — higher-cost items bear more overhead. | |
| - 'cbm' : physical size — units × volume (m³); BIGGER/bulkier SKUs absorb more (storage/freight/ | |
| FBA scale with size). Volume is on ~24% of SKUs in Odoo; the rest are imputed at the | |
| category (else global) median volume. Weight is unusable (~0% populated). | |
| - 'revenue': % of sale (reduces to channel_rate × revenue). | |
| - 'units' : per-unit. | |
| net(sku) = gross_margin(sku) − opex(sku). NET is a decision estimate; GROSS (margin/markup) is exact. | |
| Brand-filterable: team_id None = all channels (incl Amazon); 5 = Fisch, 6 = Royal (wholesale scope). | |
| READ-ONLY. | |
| """ | |
| import sys | |
| import statistics | |
| import datetime as dt | |
| from pathlib import Path | |
| sys.path.insert(0, str(Path(__file__).resolve().parents[1])) | |
| import core.odoo as O | |
| import core.periods as P | |
| _ANA = {1: 'Fisch', 2: 'Royal', 3: 'Amazon', 4: 'HQ', 5: 'Internal'} | |
| # Period: calendar 2025 (not LTM) so the pricing P&L ties to the 2025 P&L / GL / vendor analyses. | |
| _FY = ('2025-01-01', '2025-12-31') | |
| _FY_LABEL = 'FY2025' | |
| DRIVERS = {'cogs': 'COGS (cost-weighted)', 'cbm': 'CBM / volume (size)', 'revenue': 'Revenue', 'units': 'Units'} | |
| _DRIVER_SHORT = {'cogs': 'COGS', 'cbm': 'CBM', 'units': 'Units', 'revenue': 'Revenue'} | |
| def has_driver_data(row, driver): | |
| """True if the SKU has REAL (not imputed / defaulted) data for the chosen allocation driver.""" | |
| if driver == 'cbm': | |
| return bool(row.get('vol_known')) # volume on file in Odoo (else category-median imputed) | |
| if driver == 'cogs': | |
| return (row.get('cogs') or 0) > 0 # has a real product cost (else uncosted) | |
| if driver == 'units': | |
| return (row.get('units') or 0) > 0 | |
| return (row.get('revenue') or 0) > 0 # revenue: always present in the table | |
| def rates(t=None): | |
| """LTM opex rates per channel + HQ, from the analytic ledger. {channel: opex/revenue}.""" | |
| lf, lt = _FY | |
| o = O.get_odoo() | |
| rev = {_ANA.get(O.m2o_id(r['account_id'])): (r['amount'] or 0.0) for r in o.read_group( | |
| 'account.analytic.line', [('date', '>=', lf), ('date', '<=', lt), | |
| ('general_account_id.account_type', 'in', ['income', 'income_other'])], | |
| ['amount:sum', 'account_id'], ['account_id'], lazy=False) if O.m2o_id(r['account_id']) in _ANA} | |
| opx = {_ANA.get(O.m2o_id(r['account_id'])): -(r['amount'] or 0.0) for r in o.read_group( | |
| 'account.analytic.line', [('date', '>=', lf), ('date', '<=', lt), | |
| ('general_account_id.account_type', 'in', ['expense', 'expense_depreciation'])], | |
| ['amount:sum', 'account_id'], ['account_id'], lazy=False) if O.m2o_id(r['account_id']) in _ANA} | |
| tot_rev = sum(rev.values()) or 1.0 | |
| def rate(k): | |
| return (opx.get(k, 0.0) / rev[k]) if rev.get(k) else 0.0 | |
| return {'Fisch': rate('Fisch'), 'Royal': rate('Royal'), 'Amazon': rate('Amazon'), | |
| 'HQ': (opx.get('HQ', 0.0) + opx.get('Internal', 0.0)) / tot_rev, | |
| 'window': _FY_LABEL, '_rev': rev, '_opex': opx, '_tot_rev': tot_rev} | |
| def _meta(pids): | |
| prods = O.search_read('product.product', [('id', 'in', pids), ('active', 'in', [True, False])], | |
| ['default_code', 'name', 'categ_id', 'volume']) | |
| cats = {} | |
| for c in O.search_read('product.category', [], ['id', 'complete_name']): | |
| parts = [x.strip() for x in (c['complete_name'] or '').split('/')] | |
| cats[c['id']] = parts[1] if len(parts) >= 2 else (parts[0] if parts else None) | |
| # median volume per category + global, to impute the SKUs without volume on file | |
| by_cat = {} | |
| allv = [] | |
| for p in prods: | |
| v = p.get('volume') or 0.0 | |
| if v > 0: | |
| allv.append(v) | |
| by_cat.setdefault(O.m2o_id(p.get('categ_id')), []).append(v) | |
| gmed = statistics.median(allv) if allv else 0.0 | |
| cmed = {c: statistics.median(vs) for c, vs in by_cat.items()} | |
| out = {} | |
| for p in prods: | |
| cid = O.m2o_id(p.get('categ_id')) | |
| v = p.get('volume') or 0.0 | |
| out[p['id']] = {'sku': p.get('default_code') or f"#{p['id']}", 'name': p.get('name') or '', | |
| 'category': cats.get(cid) or '(uncategorized)', | |
| 'volume': v, 'vol_used': v if v > 0 else (cmed.get(cid) or gmed), 'vol_known': v > 0} | |
| return out | |
| def _build(team_id=None, driver='cogs', t=None): | |
| t = t or P.today() | |
| lf, lt = _FY | |
| o = O.get_odoo() | |
| rt = rates(t) | |
| gift = list(O.excluded_partner_ids()) | |
| win = [('order_id.date_order', '>=', f'{lf} 00:00:00'), ('order_id.date_order', '<=', f'{lt} 23:59:59')] | |
| sbase = [('order_id.state', 'in', ['sale', 'done']), ('product_id.type', '!=', 'service')] + win | |
| def by_prod(extra, fields): | |
| return {O.m2o_id(r['product_id']): r for r in o.read_group('sale.order.line', sbase + extra, | |
| fields + ['product_id'], ['product_id'], lazy=False) if r.get('product_id')} | |
| if team_id in (5, 6): # one wholesale BU | |
| allc = by_prod([('order_id.team_id', '=', team_id), ('order_partner_id', 'not in', gift)], | |
| ['price_subtotal:sum', 'product_uom_qty:sum', 'margin:sum']) | |
| chan_of = {pid: ('Fisch' if team_id == 5 else 'Royal') for pid in allc} | |
| chan_rev = {pid: {('Fisch' if team_id == 5 else 'Royal'): (a['price_subtotal'] or 0.0)} for pid, a in allc.items()} | |
| else: # all channels | |
| allc = by_prod([], ['price_subtotal:sum', 'product_uom_qty:sum', 'margin:sum']) | |
| rf = by_prod([('order_id.team_id', '=', 5), ('order_partner_id', 'not in', gift)], ['price_subtotal:sum']) | |
| rr = by_prod([('order_id.team_id', '=', 6), ('order_partner_id', 'not in', gift)], ['price_subtotal:sum']) | |
| ra = by_prod([('order_partner_id', 'in', gift)] if gift else [('id', '=', -1)], ['price_subtotal:sum']) | |
| chan_rev = {pid: {'Fisch': (rf.get(pid, {}).get('price_subtotal') or 0.0), | |
| 'Royal': (rr.get(pid, {}).get('price_subtotal') or 0.0), | |
| 'Amazon': (ra.get(pid, {}).get('price_subtotal') or 0.0)} for pid in allc} | |
| meta = _meta(list(allc)) | |
| # assemble per-SKU base | |
| skus = {} | |
| for pid, a in allc.items(): | |
| rev = a['price_subtotal'] or 0.0 | |
| if rev <= 0: | |
| continue | |
| units = a['product_uom_qty'] or 0.0 | |
| gm = a['margin'] or 0.0 | |
| cogs = rev - gm | |
| m = meta.get(pid, {}) | |
| skus[pid] = {'rev': rev, 'units': units, 'gm': gm, 'cogs': cogs, 'cr': chan_rev.get(pid, {}), | |
| 'cbm': units * (m.get('vol_used') or 0.0), 'm': m} | |
| # driver value per SKU + its apportionment to each channel (by revenue mix) | |
| def dval(s): | |
| return {'cogs': s['cogs'], 'cbm': s['cbm'], 'units': s['units'], 'revenue': s['rev']}.get(driver, s['cogs']) | |
| chans = ['Fisch', 'Royal', 'Amazon'] | |
| pool = {C: rt[C] * sum(s['cr'].get(C, 0.0) for s in skus.values()) for C in chans} # rate × visible channel rev | |
| pool_hq = rt['HQ'] * sum(s['rev'] for s in skus.values()) | |
| sumdrvC = {C: sum(dval(s) * (s['cr'].get(C, 0.0) / s['rev']) for s in skus.values() if s['rev']) for C in chans} | |
| sumdrv = sum(dval(s) for s in skus.values()) or 1.0 | |
| rows = [] | |
| for pid, s in skus.items(): | |
| load = pool_hq * (dval(s) / sumdrv) | |
| for C in chans: | |
| if sumdrvC[C] > 0 and s['rev']: | |
| load += pool[C] * (dval(s) * (s['cr'].get(C, 0.0) / s['rev'])) / sumdrvC[C] | |
| rev, gm, cogs, units = s['rev'], s['gm'], s['cogs'], s['units'] | |
| net = gm - load | |
| m = s['m'] | |
| rows.append({ | |
| 'product_id': pid, 'sku': m.get('sku', f'#{pid}'), 'code': m.get('sku', f'#{pid}'), | |
| 'product': m.get('name', ''), 'name': m.get('name', ''), 'category': m.get('category', '(uncategorized)'), | |
| 'units': units, 'revenue': rev, 'cogs': cogs, | |
| 'unit_cost': (cogs / units) if units else 0.0, 'avg_price': (rev / units) if units else 0.0, | |
| 'cbm_unit': m.get('volume', 0.0), 'cbm_used': m.get('vol_used', 0.0), 'cbm_total': s['cbm'], | |
| 'vol_known': m.get('vol_known', False), 'cbm_src': ('on file' if m.get('vol_known') else 'imputed'), | |
| 'gm_dollars': gm, 'gm_pct': (gm / rev * 100) if rev else 0.0, | |
| 'markup_pct': (gm / cogs * 100) if cogs > 0 else None, | |
| 'opex_load': load, 'opex_pct': (load / rev * 100) if rev else 0.0, | |
| 'net_dollars': net, 'net_pct': (net / rev * 100) if rev else 0.0, | |
| 'status': ('Below cost' if gm < 0 else 'Net-negative' if net < 0 else 'Thin (<10% net)' if (net / rev) < 0.10 else 'Healthy'), | |
| }) | |
| rows.sort(key=lambda r: -r['revenue']) | |
| return rows, rt | |
| def table(team_id=None, driver='cogs', t=None): | |
| return _build(team_id, driver, t)[0] | |
| def summary(team_id=None, driver='cogs', t=None, built=None): | |
| rows, rt = built or _build(team_id, driver, t) | |
| rev = sum(r['revenue'] for r in rows) | |
| gm = sum(r['gm_dollars'] for r in rows) | |
| net = sum(r['net_dollars'] for r in rows) | |
| mk = [r['markup_pct'] for r in rows if r['markup_pct'] is not None] | |
| vol_known = sum(1 for r in rows if r['vol_known']) | |
| driver_known = sum(1 for r in rows if has_driver_data(r, driver)) | |
| return { | |
| 'window': rt['window'], 'skus': len(rows), 'revenue': rev, 'driver': driver, 'driver_label': DRIVERS.get(driver, driver), | |
| 'driver_short': _DRIVER_SHORT.get(driver, driver), | |
| 'gm_dollars': gm, 'gm_pct': (gm / rev * 100) if rev else 0.0, | |
| 'net_dollars': net, 'net_pct': (net / rev * 100) if rev else 0.0, | |
| 'avg_markup': (sum(mk) / len(mk)) if mk else 0.0, | |
| 'below_cost_skus': sum(1 for r in rows if r['gm_dollars'] < 0), | |
| 'net_negative_skus': sum(1 for r in rows if r['net_dollars'] < 0), | |
| 'net_negative_rev': sum(r['revenue'] for r in rows if r['net_dollars'] < 0), | |
| 'vol_coverage': (vol_known / len(rows) * 100) if rows else 0.0, | |
| 'driver_known': driver_known, 'driver_coverage': (driver_known / len(rows) * 100) if rows else 0.0, | |
| 'rates': {k: round(rt[k] * 100, 1) for k in ('Fisch', 'Royal', 'Amazon', 'HQ')}, | |
| } | |
| def cost_drift(t=None, team_id=None): | |
| """Replacement-cost drift from confirmed PO lines: the net price paid per unit in the LAST | |
| 12 months vs the 12 months BEFORE, per product, joined to what the SELL price did over the | |
| same two windows (BU-scoped sell side; cost is company-wide). ERODING = cost up >5% while | |
| the selling price followed by less than half — the margin leaks silently until repriced. | |
| cost_impact_12m = (cost_now − cost_prior) × units sold last 12m (the annualized $ at stake).""" | |
| t = t or P.today() | |
| d24 = (t - dt.timedelta(days=730)).isoformat() | |
| d12 = (t - dt.timedelta(days=365)).isoformat() | |
| lines = O.search_read('purchase.order.line', | |
| [('order_id.state', 'in', ('purchase', 'done')), | |
| ('order_id.date_order', '>=', d24), | |
| ('product_qty', '>', 0), ('price_unit', '>', 0)], | |
| ['product_id', 'product_qty', 'product_uom_qty', 'price_subtotal', | |
| 'order_id']) | |
| oids = list({O.m2o_id(l['order_id']) for l in lines if l.get('order_id')}) | |
| od = {} | |
| for i in range(0, len(oids), 5000): | |
| for o_ in O.search_read('purchase.order', [('id', 'in', oids[i:i + 5000])], ['date_order']): | |
| od[o_['id']] = str(o_['date_order'])[:10] | |
| cur, prior = {}, {} # pid -> [net spend, qty] | |
| for l in lines: | |
| pid = O.m2o_id(l.get('product_id')) | |
| d = od.get(O.m2o_id(l.get('order_id'))) | |
| if not pid or not d: | |
| continue | |
| e = (cur if d >= d12 else prior).setdefault(pid, [0.0, 0.0]) | |
| e[0] += l.get('price_subtotal') or 0.0 | |
| # BASE-UoM qty, so a piece→case purchase-UoM switch doesn't fake a price spike | |
| e[1] += l.get('product_uom_qty') or l.get('product_qty') or 0.0 | |
| both = [p for p in cur if p in prior and prior[p][1] > 0 and cur[p][1] > 0] | |
| def _sell(a, b_): | |
| out = {} | |
| for g in O.read_group('sale.order.line', O.sale_line_domain(a, b_, team_id), | |
| ['price_subtotal:sum', 'product_uom_qty:sum'], ['product_id'], | |
| lazy=False): | |
| pid = O.m2o_id(g.get('product_id')) | |
| if pid: | |
| out[pid] = (g.get('price_subtotal') or 0.0, g.get('product_uom_qty') or 0.0) | |
| return out | |
| s_now, s_pri = _sell(d12, t.isoformat()), _sell(d24, d12) | |
| meta = {} | |
| for i in range(0, len(both), 5000): | |
| for p in O.search_read('product.product', | |
| [('id', 'in', both[i:i + 5000]), ('active', 'in', [True, False])], | |
| ['default_code', 'name', 'standard_price']): | |
| meta[p['id']] = p | |
| rows = [] | |
| for pid in both: | |
| c_now, c_pri = cur[pid][0] / cur[pid][1], prior[pid][0] / prior[pid][1] | |
| if c_pri <= 0: | |
| continue | |
| rn, qn = s_now.get(pid, (0.0, 0.0)) | |
| rp, qp = s_pri.get(pid, (0.0, 0.0)) | |
| asp_now = rn / qn if qn else None | |
| asp_pri = rp / qp if qp else None | |
| p = meta.get(pid, {}) | |
| rows.append({'pid': pid, 'code': (p.get('default_code') or '').strip(), | |
| 'product': p.get('name') or '', | |
| 'cost_prior': c_pri, 'cost_now': c_now, | |
| 'drift_pct': (c_now / c_pri - 1) * 100, | |
| 'buy_qty_12m': cur[pid][1], 'std_cost': p.get('standard_price') or 0.0, | |
| 'asp_now': asp_now, 'asp_prior': asp_pri, | |
| 'price_chg_pct': ((asp_now / asp_pri - 1) * 100) | |
| if (asp_now and asp_pri) else None, | |
| 'units_12m': qn, 'cost_impact_12m': (c_now - c_pri) * qn, | |
| 'gm_pct_now': ((asp_now - c_now) / asp_now * 100) if asp_now else None}) | |
| # a >3x (or <1/3) per-base-unit move is a UoM/master-data break, not market inflation — | |
| # surfaced as its own data-quality list so it can't pollute the erosion signal | |
| breaks = sorted((r for r in rows if not (1 / 3 <= (r['cost_now'] / r['cost_prior']) <= 3)), | |
| key=lambda r: -abs(r['drift_pct'])) | |
| broken = {r['pid'] for r in breaks} | |
| eroding = sorted((r for r in rows | |
| if r['pid'] not in broken | |
| and r['drift_pct'] > 5 and (r['units_12m'] or 0) > 0 | |
| and (r['price_chg_pct'] is None or r['price_chg_pct'] < r['drift_pct'] / 2)), | |
| key=lambda r: -(r['cost_impact_12m'] or 0)) | |
| improving = sorted((r for r in rows if r['pid'] not in broken | |
| and r['drift_pct'] < -5 and (r['units_12m'] or 0) > 0), | |
| key=lambda r: r['cost_impact_12m']) | |
| return {'rows': rows, 'eroding': eroding, 'improving': improving, 'breaks': breaks, | |
| 'n_products': len(rows), | |
| 'erosion_total': sum(r['cost_impact_12m'] for r in eroding), | |
| 'tailwind_total': sum(r['cost_impact_12m'] for r in improving), | |
| '_cur_spend': sum(e[0] for e in cur.values()), | |
| '_cur_domain_from': d12, 'windows': (d24, d12, t.isoformat())} | |
| def cost_drift_validate(cd, t=None): | |
| """The 12m PO spend our per-product weighting is built on == the server-side sum over the | |
| identical domain (two independent aggregation paths).""" | |
| t = t or P.today() | |
| dom = [('order_id.state', 'in', ('purchase', 'done')), | |
| ('order_id.date_order', '>=', cd['_cur_domain_from']), | |
| ('product_qty', '>', 0), ('price_unit', '>', 0)] | |
| srv = O.sum_field('purchase.order.line', dom, 'price_subtotal') | |
| return [{'check': 'Cost drift: Σ(per-product 12m PO spend) == server Σ(line subtotal), same domain', | |
| 'a': round(cd['_cur_spend'], 2), 'b': round(srv, 2), | |
| 'gap': round(cd['_cur_spend'] - srv, 2), | |
| 'ok': abs(cd['_cur_spend'] - srv) <= max(1.0, abs(srv) * 0.001)}] | |
| def _pnl_entities(t=None): | |
| """Actual LTM P&L per analytic entity (the basis the Management P&L is built from).""" | |
| lf, lt = _FY | |
| o = O.get_odoo() | |
| def grp(types): | |
| return {_ANA.get(O.m2o_id(r['account_id'])): (r['amount'] or 0.0) for r in o.read_group( | |
| 'account.analytic.line', [('date', '>=', lf), ('date', '<=', lt), | |
| ('general_account_id.account_type', 'in', types)], ['amount:sum', 'account_id'], ['account_id'], lazy=False) | |
| if O.m2o_id(r['account_id']) in _ANA} | |
| inc, cog, opx = grp(['income', 'income_other']), grp(['expense_direct_cost']), grp(['expense', 'expense_depreciation']) | |
| return {k: {'revenue': inc.get(k, 0.0), 'cogs': -cog.get(k, 0.0), 'opex': -opx.get(k, 0.0)} for k in _ANA.values()} | |
| def gl_pnl(t=None): | |
| """Actual LTM P&L straight from the posted GL (the official books) — the independent reconciliation | |
| target. Revenue − COGS − Opex = Net.""" | |
| lf, lt = _FY | |
| def s(types): | |
| return O.sum_field('account.move.line', [('parent_state', '=', 'posted'), ('date', '>=', lf), | |
| ('date', '<=', lt), ('account_id.account_type', 'in', types)], 'balance') | |
| rev = -s(['income', 'income_other']) # income is credit → flip to positive | |
| cogs = s(['expense_direct_cost']) | |
| opex = s(['expense', 'expense_depreciation']) | |
| return {'revenue': rev, 'cogs': cogs, 'gm': rev - cogs, 'opex': opex, 'net': rev - cogs - opex} | |
| def reconcile(team_id=None, driver='cogs', t=None, built=None): | |
| """Bridge the per-SKU P&L to the ACTUAL P&L: attributed SKUs + unattributed (Amazon-direct, not | |
| booked per-SKU) = the displayed P&L. Ties by construction; the unattributed line is the residual.""" | |
| rows, rt = built or _build(team_id, driver, t) | |
| ent = _pnl_entities(t) | |
| tot_rev = sum(e['revenue'] for e in ent.values()) or 1.0 | |
| hq_rate = (ent['HQ']['opex'] + ent['Internal']['opex']) / tot_rev | |
| if team_id in (5, 6): | |
| k = 'Fisch' if team_id == 5 else 'Royal' | |
| rev_t, cogs_t = ent[k]['revenue'], ent[k]['cogs'] | |
| opex_t = ent[k]['opex'] + hq_rate * ent[k]['revenue'] # BU opex + its share of HQ | |
| else: | |
| rev_t = sum(e['revenue'] for e in ent.values()) | |
| cogs_t = sum(e['cogs'] for e in ent.values()) | |
| opex_t = sum(e['opex'] for e in ent.values()) # all opex incl HQ | |
| net_t = rev_t - cogs_t - opex_t | |
| rev_s = sum(r['revenue'] for r in rows) | |
| gm_s = sum(r['gm_dollars'] for r in rows) | |
| cogs_s, opex_s = rev_s - gm_s, sum(r['opex_load'] for r in rows) | |
| net_s = gm_s - opex_s | |
| una = {'revenue': rev_t - rev_s, 'cogs': cogs_t - cogs_s, 'gm': (rev_t - rev_s) - (cogs_t - cogs_s), | |
| 'opex': opex_t - opex_s, 'net': net_t - net_s} | |
| pnl = {'revenue': rev_t, 'cogs': cogs_t, 'gm': rev_t - cogs_t, 'opex': opex_t, 'net': net_t} | |
| sku = {'revenue': rev_s, 'cogs': cogs_s, 'gm': gm_s, 'opex': opex_s, 'net': net_s} | |
| return {'pnl': pnl, 'sku': sku, 'unattrib': una, 'ties': abs((net_s + una['net']) - net_t) < 1.0} | |
| def page_data(team_id=None, driver='cogs', t=None): | |
| """One build → rows + summary + reconciliation + validation (avoids rebuilding 4×).""" | |
| built = _build(team_id, driver, t) | |
| return {'rows': built[0], 'summary': summary(team_id, driver, t, built=built), | |
| 'reconcile': reconcile(team_id, driver, t, built=built), | |
| 'validation': validate(team_id, driver, t, built=built)} | |
| def validate(team_id=None, driver='cogs', t=None, built=None): | |
| rows, rt = built or _build(team_id, driver, t) | |
| checks = [] | |
| lf, lt = _FY | |
| gift = list(O.excluded_partner_ids()) | |
| dom = [('order_id.state', 'in', ['sale', 'done']), ('order_id.date_order', '>=', f'{lf} 00:00:00'), | |
| ('order_id.date_order', '<=', f'{lt} 23:59:59'), ('product_id.type', '!=', 'service'), ('price_subtotal', '>', 0)] | |
| if team_id in (5, 6): | |
| dom += [('order_id.team_id', '=', team_id), ('order_partner_id', 'not in', gift)] | |
| indep = O.sum_field('sale.order.line', dom, 'price_subtotal') | |
| ours = sum(r['revenue'] for r in rows) | |
| checks.append({'check': 'Σ per-SKU revenue == scoped FY2025 (positive lines)', 'a': round(ours, 0), | |
| 'b': round(indep, 0), 'gap': round(ours - indep, 0), 'ok': abs(ours - indep) <= max(50.0, indep * 0.01)}) | |
| if rows: | |
| s = rows[0] | |
| checks.append({'check': f"GM == revenue−COGS (sample {s['sku']})", 'a': round(s['gm_dollars'], 2), | |
| 'b': round(s['revenue'] - s['cogs'], 2), 'gap': round(s['gm_dollars'] - (s['revenue'] - s['cogs']), 2), | |
| 'ok': abs(s['gm_dollars'] - (s['revenue'] - s['cogs'])) <= 0.5}) | |
| # THE finance check — per-SKU P&L reconciles to the actual P&L (and that target ties to the posted GL) | |
| rc = reconcile(team_id, driver, t, built=(rows, rt)) | |
| checks.append({'check': 'Attributed SKUs + unattributed net == P&L net (reconciles)', | |
| 'a': round(rc['sku']['net'] + rc['unattrib']['net'], 0), 'b': round(rc['pnl']['net'], 0), | |
| 'gap': round(rc['sku']['net'] + rc['unattrib']['net'] - rc['pnl']['net'], 0), 'ok': rc['ties']}) | |
| if team_id is None: | |
| gl = gl_pnl(t) | |
| checks.append({'check': 'P&L target (analytic) == posted GL net (actual books)', | |
| 'a': round(rc['pnl']['net'], 0), 'b': round(gl['net'], 0), | |
| 'gap': round(rc['pnl']['net'] - gl['net'], 0), 'ok': abs(rc['pnl']['net'] - gl['net']) <= 2.0}) | |
| return checks | |