Spaces:
Paused
Paused
Download platform/modules/inventory.py from aios-loopable/cfo-os: direct link, hf CLI and curl.
- Browser
- Download file 13.8 kB
-
https://huggingface.co/spaces/aios-loopable/cfo-os/resolve/main/platform/modules/inventory.py
- Command line
-
hf download hf://spaces/aios-loopable/cfo-os/platform/modules/inventory.py
-
curl -L -o inventory.py https://huggingface.co/spaces/aios-loopable/cfo-os/resolve/main/platform/modules/inventory.py
13.8 kB
| """Inventory module — working capital tied up, dead stock, stockouts blocking revenue, | |
| ABC, slow movers, turnover. | |
| Valuation: on-hand qty (stock.quant, internal locations) × standard cost. (stock.quant.value | |
| is a non-stored computed field Odoo refuses to aggregate, so standard-cost valuation is used | |
| and is independently cross-checked.) Sales velocity from sale.order.line over LTM (RI+FFS). | |
| """ | |
| import sys | |
| from pathlib import Path | |
| sys.path.insert(0, str(Path(__file__).resolve().parents[1])) | |
| import pandas as pd | |
| import core.odoo as O | |
| import core.periods as P | |
| def _cat_main_map(): | |
| cats = O.search_read('product.category', [], ['id', 'complete_name']) | |
| out = {} | |
| for c in cats: | |
| parts = [x.strip() for x in (c['complete_name'] or '').split('/')] | |
| out[c['id']] = parts[1] if len(parts) >= 2 else (parts[0] if parts else None) | |
| return out | |
| def _build(t=None): | |
| """One master frame: product × on-hand × cost × category × LTM sales.""" | |
| t = t or P.today() | |
| o = O.get_odoo() | |
| # On-hand qty per product (internal locations only) | |
| q = o.read_group('stock.quant', [('location_id.usage', '=', 'internal')], | |
| ['product_id', 'quantity:sum'], ['product_id'], lazy=False) | |
| onhand = {O.m2o_id(r['product_id']): (r.get('quantity') or 0.0) | |
| for r in q if r.get('product_id')} | |
| # Products (coded). 'type' lets us separate storable goods from services | |
| # (delivery charges etc. are services — they can't "stock out"). | |
| fields = ['id', 'default_code', 'name', 'categ_id', 'standard_price', 'sale_ok', 'type'] | |
| prods = o.search_read('product.product', [('default_code', '!=', False)], fields) | |
| catmap = _cat_main_map() | |
| # LTM sales per product (line level, RI+FFS scope) | |
| lf, lt = P.ltm(t) | |
| s = o.read_group('sale.order.line', O.sale_line_domain(lf, lt), | |
| ['product_id', 'price_subtotal:sum', 'product_uom_qty:sum'], | |
| ['product_id'], lazy=False) | |
| rev = {O.m2o_id(r['product_id']): (r.get('price_subtotal') or 0.0) for r in s if r.get('product_id')} | |
| qty = {O.m2o_id(r['product_id']): (r.get('product_uom_qty') or 0.0) for r in s if r.get('product_id')} | |
| rows = [] | |
| for p in prods: | |
| pid = p['id'] | |
| oh = onhand.get(pid, 0.0) | |
| cost = p.get('standard_price') or 0.0 | |
| rows.append({ | |
| 'product_id': pid, | |
| 'sku': str(p['default_code']).strip(), | |
| 'name': p['name'], | |
| 'type': p.get('type') or '', | |
| 'storable': (p.get('type') == 'product'), | |
| 'category': catmap.get(O.m2o_id(p.get('categ_id'))) or '(uncategorized)', | |
| 'cost': cost, | |
| 'on_hand': oh, | |
| 'inv_value': oh * cost, | |
| 'rev_ltm': rev.get(pid, 0.0), | |
| 'qty_ltm': qty.get(pid, 0.0), | |
| }) | |
| return pd.DataFrame(rows), (lf, lt) | |
| def summary(t=None): | |
| df, (lf, lt) = _build(t) | |
| in_stock = df[df['on_hand'] > 0] | |
| dead = df[(df['inv_value'] >= 1000) & (df['rev_ltm'] < 100)] | |
| # stockouts only make sense for STORABLE goods (services like delivery never stock) | |
| stockout = df[(df['storable']) & (df['rev_ltm'] >= 5000) & (df['on_hand'] <= 0)] | |
| uncosted = in_stock[in_stock['cost'] <= 0] | |
| return { | |
| 'ltm_window': f'{lf} → {lt}', | |
| 'total_inv_value': float(df['inv_value'].sum()), | |
| 'total_on_hand_units': float(df['on_hand'].sum()), | |
| 'skus_in_stock': int((df['on_hand'] > 0).sum()), | |
| 'dead_skus': int(len(dead)), | |
| 'dead_value': float(dead['inv_value'].sum()), | |
| 'stockout_skus': int(len(stockout)), | |
| 'stockout_rev_ltm': float(stockout['rev_ltm'].sum()), | |
| 'uncosted_in_stock_skus': int(len(uncosted)), | |
| } | |
| def by_category(t=None): | |
| df, _ = _build(t) | |
| g = df.groupby('category').agg( | |
| inv_value=('inv_value', 'sum'), | |
| rev_ltm=('rev_ltm', 'sum'), | |
| skus=('product_id', 'count'), | |
| in_stock=('on_hand', lambda s: int((s > 0).sum())), | |
| ).reset_index() | |
| # crude turnover proxy: LTM revenue / on-hand value (higher = faster) | |
| g['turnover_x'] = g.apply(lambda r: (r['rev_ltm'] / r['inv_value']) if r['inv_value'] > 0 else None, axis=1) | |
| return g.sort_values('inv_value', ascending=False).to_dict('records') | |
| def dead_stock(t=None, limit=50): | |
| df, _ = _build(t) | |
| d = df[(df['inv_value'] >= 1000) & (df['rev_ltm'] < 100)] | |
| return d.sort_values('inv_value', ascending=False).head(limit)[ | |
| ['sku', 'name', 'category', 'on_hand', 'cost', 'inv_value', 'rev_ltm']].to_dict('records') | |
| def stockouts(t=None, limit=50): | |
| df, _ = _build(t) | |
| s = df[(df['storable']) & (df['rev_ltm'] >= 5000) & (df['on_hand'] <= 0)] | |
| return s.sort_values('rev_ltm', ascending=False).head(limit)[ | |
| ['sku', 'name', 'category', 'on_hand', 'rev_ltm', 'qty_ltm']].to_dict('records') | |
| def abc(t=None): | |
| """ABC by LTM revenue contribution. A=top 80%, B=next 15%, C=last 5%.""" | |
| df, _ = _build(t) | |
| d = df[df['rev_ltm'] > 0].sort_values('rev_ltm', ascending=False).copy() | |
| total = d['rev_ltm'].sum() or 1.0 | |
| d['cum'] = d['rev_ltm'].cumsum() / total | |
| def cls(c): | |
| return 'A' if c <= 0.80 else ('B' if c <= 0.95 else 'C') | |
| d['abc'] = d['cum'].apply(cls) | |
| out = d.groupby('abc').agg(skus=('product_id', 'count'), | |
| rev_ltm=('rev_ltm', 'sum'), | |
| inv_value=('inv_value', 'sum')).reset_index() | |
| return out.to_dict('records') | |
| # ----------------------------------------------------------- deeper analytics | |
| _CARRY_PCT = 25.0 # default annual carrying cost as a % of inventory value (capital + storage + obsolescence) | |
| _COVERAGE_BUCKETS = [('Critical (<15d)', 0, 15), ('Lean (15–45d)', 15, 45), | |
| ('Healthy (45–120d)', 45, 120), ('Ample (120–270d)', 120, 270), | |
| ('Overstock (270–540d)', 270, 540), ('Excess (>540d)', 540, 1e15)] | |
| def _enrich(df, target_days): | |
| """Add daily run-rate, days-of-supply, COGS, and excess-vs-target columns.""" | |
| df = df.copy() | |
| daily = df['qty_ltm'] / 365.0 | |
| df['daily_rate'] = daily | |
| df['cogs_ltm'] = df['qty_ltm'] * df['cost'] | |
| def _dos(r): | |
| if r['daily_rate'] > 0: | |
| return r['on_hand'] / r['daily_rate'] | |
| return float('inf') if r['on_hand'] > 0 else 0.0 | |
| df['dos'] = df.apply(_dos, axis=1) | |
| df['target_units'] = daily * target_days | |
| df['excess_units'] = (df['on_hand'] - df['target_units']).clip(lower=0) | |
| df['excess_value'] = df['excess_units'] * df['cost'] | |
| return df | |
| def analytics(t=None, target_days=180, carry_pct=_CARRY_PCT): | |
| """Inventory health analytics from ONE build: overstock (excess vs a target days-of-supply), the | |
| coverage-profile distribution, turnover, average days-of-supply, and the carrying cost of excess.""" | |
| df, (lf, lt) = _build(t) | |
| df = _enrich(df, target_days) | |
| instock = df[(df['storable']) & (df['on_hand'] > 0)] | |
| total_inv = float(df['inv_value'].sum()) | |
| total_cogs = float(df['cogs_ltm'].sum()) | |
| # overstock = sells, but holds materially more than the target coverage | |
| over = df[(df['storable']) & (df['on_hand'] > 0) & (df['qty_ltm'] > 0) | |
| & (df['dos'] > target_days) & (df['excess_value'] > 0)] | |
| over_value = float(over['excess_value'].sum()) | |
| overrows = [{'sku': r['sku'], 'code': r['sku'], 'product': r['name'], 'category': r['category'], | |
| 'on_hand': float(r['on_hand']), | |
| 'dos': (None if r['dos'] == float('inf') else round(float(r['dos']), 0)), | |
| 'excess_units': float(r['excess_units']), 'excess_value': float(r['excess_value']), | |
| 'carry_cost': float(r['excess_value']) * (carry_pct / 100.0), 'rev_ltm': float(r['rev_ltm'])} | |
| for _, r in over.sort_values('excess_value', ascending=False).head(80).iterrows()] | |
| dead = df[(df['inv_value'] >= 1000) & (df['rev_ltm'] < 100)] | |
| # coverage profile (in-stock storable, by days-of-supply) | |
| prof = [] | |
| for nm, lo, hi in _COVERAGE_BUCKETS: | |
| sub = instock[(instock['qty_ltm'] > 0) & (instock['dos'] >= lo) & (instock['dos'] < hi)] | |
| prof.append({'bucket': nm, 'skus': int(len(sub)), 'inv_value': float(sub['inv_value'].sum()), | |
| 'units': float(sub['on_hand'].sum())}) | |
| nosale = instock[instock['qty_ltm'] <= 0] | |
| prof.append({'bucket': 'No recent sales', 'skus': int(len(nosale)), | |
| 'inv_value': float(nosale['inv_value'].sum()), 'units': float(nosale['on_hand'].sum())}) | |
| healthy_val = sum(p['inv_value'] for p in prof if p['bucket'].startswith(('Healthy', 'Ample'))) | |
| excess_val = sum(p['inv_value'] for p in prof if p['bucket'].startswith(('Overstock', 'Excess', 'No recent'))) | |
| return { | |
| 'window': f'{lf} → {lt}', 'target_days': target_days, 'carry_pct': carry_pct, | |
| 'total_inv_value': total_inv, | |
| 'turnover_x': (total_cogs / total_inv) if total_inv else None, | |
| 'avg_dos': (total_inv / (total_cogs / 365.0)) if total_cogs else None, | |
| 'overstock': overrows, 'overstock_value': over_value, 'overstock_skus': int(len(over)), | |
| 'carrying_cost': over_value * (carry_pct / 100.0), | |
| 'coverage': prof, 'healthy_value': healthy_val, 'excess_value_total': excess_val, | |
| 'dead_value': float(dead['inv_value'].sum()), 'dead_skus': int(len(dead)), | |
| } | |
| def _bucket(dos_raw, on_hand, qty_ltm): | |
| """Coverage-bucket label for one SKU from its days-of-supply (matches the coverage profile).""" | |
| if on_hand <= 0: | |
| return 'Out of stock' | |
| if qty_ltm <= 0: | |
| return 'No recent sales' | |
| for nm, lo, hi in _COVERAGE_BUCKETS: | |
| if lo <= dos_raw < hi: | |
| return nm | |
| return _COVERAGE_BUCKETS[-1][0] | |
| # the coverage facet, worst→best, for the SKU directory filter | |
| COVERAGE_LABELS = ['Out of stock'] + [b[0] for b in _COVERAGE_BUCKETS] + ['No recent sales'] | |
| def sku_inventory(target_days=180, carry_pct=_CARRY_PCT, t=None): | |
| """Per-SKU-code inventory position for the SKU module — keyed by SKU code (variants merged the | |
| same way the SKU module merges them). CONSOLIDATED: on-hand stock is one physical warehouse, not | |
| brand-tagged, so this is brand-independent. Returns {code: {name, category, storable, on_hand, | |
| inv_value, unit_cost, qty_ltm, dos (None=never sells through), bucket, excess_units, excess_value, | |
| carry_cost, turns, target_units, target_days}}.""" | |
| df, _ = _build(t) | |
| df = _enrich(df, target_days) | |
| g = df.groupby('sku').agg( | |
| name=('name', 'first'), category=('category', 'first'), storable=('storable', 'max'), | |
| on_hand=('on_hand', 'sum'), inv_value=('inv_value', 'sum'), | |
| cogs_ltm=('cogs_ltm', 'sum'), qty_ltm=('qty_ltm', 'sum'), rev_ltm=('rev_ltm', 'sum'), | |
| ).reset_index() | |
| out = {} | |
| for r in g.itertuples(index=False): | |
| daily = r.qty_ltm / 365.0 | |
| dos_raw = (r.on_hand / daily) if daily > 0 else (float('inf') if r.on_hand > 0 else 0.0) | |
| target_units = daily * target_days | |
| excess_units = max(r.on_hand - target_units, 0.0) | |
| # code-level unit cost: on-hand-weighted where in stock, else the LTM COGS unit cost | |
| unit_cost = (r.inv_value / r.on_hand) if r.on_hand > 0 else ( | |
| (r.cogs_ltm / r.qty_ltm) if r.qty_ltm > 0 else 0.0) | |
| excess_value = excess_units * unit_cost | |
| out[r.sku] = { | |
| 'name': r.name, 'category': r.category, 'storable': bool(r.storable), | |
| 'on_hand': float(r.on_hand), 'inv_value': float(r.inv_value), | |
| 'unit_cost': float(unit_cost), 'qty_ltm': float(r.qty_ltm), | |
| 'dos': (None if dos_raw == float('inf') else round(float(dos_raw), 0)), | |
| 'bucket': _bucket(dos_raw, r.on_hand, r.qty_ltm), | |
| 'excess_units': float(excess_units), 'excess_value': float(excess_value), | |
| 'carry_cost': float(excess_value) * (carry_pct / 100.0), | |
| 'turns': (float(r.cogs_ltm) / float(r.inv_value)) if r.inv_value > 0 else None, | |
| 'target_units': float(target_units), 'target_days': target_days, | |
| } | |
| return out | |
| def validate(t=None): | |
| df, _ = _build(t) | |
| o = O.get_odoo() | |
| checks = [] | |
| # 1. On-hand units: Σ(per-product) == ungrouped Odoo total | |
| g = o.read_group('stock.quant', [('location_id.usage', '=', 'internal')], | |
| ['quantity:sum'], [], lazy=False) | |
| odoo_total = (g[0].get('quantity') or 0.0) if g else 0.0 | |
| # our df only covers coded products; compare coded-product on-hand to a coded-only Odoo total | |
| coded_ids = set(df['product_id']) | |
| ours = float(df['on_hand'].sum()) | |
| checks.append({ | |
| 'check': 'On-hand units (coded SKUs) ≤ Odoo total internal qty', | |
| 'a': round(ours, 0), 'b': round(odoo_total, 0), | |
| 'gap': round(odoo_total - ours, 0), | |
| 'ok': ours <= odoo_total + 1}) # coded ≤ all (uncoded/raw exist) | |
| # 2. Σ(category inv_value) == total inv_value | |
| cat_sum = sum(c['inv_value'] for c in by_category(t)) | |
| tot = float(df['inv_value'].sum()) | |
| checks.append({ | |
| 'check': 'Inv value: Σ(category) == total', | |
| 'a': round(cat_sum, 2), 'b': round(tot, 2), | |
| 'gap': round(cat_sum - tot, 2), | |
| 'ok': abs(cat_sum - tot) <= 1.0}) | |
| # 3. inv_value definition holds for a sampled SKU (qty×cost) | |
| smp = df[df['on_hand'] > 0].head(1) | |
| if len(smp): | |
| r = smp.iloc[0] | |
| recomputed = r['on_hand'] * r['cost'] | |
| checks.append({ | |
| 'check': f"inv_value == on_hand×cost (sample {r['sku']})", | |
| 'a': round(float(r['inv_value']), 2), 'b': round(float(recomputed), 2), | |
| 'gap': round(float(r['inv_value'] - recomputed), 2), | |
| 'ok': abs(r['inv_value'] - recomputed) <= 0.01}) | |
| return checks | |