Spaces:
Paused
Paused
Download platform/modules/returns.py from aios-loopable/cfo-os: direct link, hf CLI and curl.
- Browser
- Download file 12.3 kB
-
https://huggingface.co/spaces/aios-loopable/cfo-os/resolve/main/platform/modules/returns.py
- Command line
-
hf download hf://spaces/aios-loopable/cfo-os/platform/modules/returns.py
-
curl -L -o returns.py https://huggingface.co/spaces/aios-loopable/cfo-os/resolve/main/platform/modules/returns.py
12.3 kB
| """Returns module — the credit-note lens nobody reads: 14% of billing documents are refunds. | |
| Governing question: WHERE DO RETURNS CONCENTRATE — WHICH SKUs (quality signal), WHICH | |
| CUSTOMERS (behavior signal), WHICH CATEGORIES (product-line signal), WHICH SUPPLIERS | |
| (sourcing-quality signal) AND WHICH AGENTS (book-behavior signal) — AND IS THE RATE MOVING? | |
| Odoo has no return-reason field, so the concentration IS the diagnostic. Rates are always shown | |
| next to raw $ — a big seller with average rate is noise; a small line with 4x the company rate | |
| is the finding. | |
| Supplier attribution: curated procurement map (default_code) first, else the DOMINANT vendor | |
| from confirmed PO history (most units bought), else '(no supplier data)'. Agent attribution: | |
| the customer's res.partner agent — credit-note team_id is NOT trustworthy. | |
| Company-level (credit notes carry team_id=1 for everything). READ-ONLY. | |
| """ | |
| import sys | |
| 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 | |
| import modules.customers as cust_mod | |
| import modules.procurement as proc_mod | |
| _EX_MOVE = [] # posted customer docs only | |
| def _mdom(move_type, a, b): | |
| return [('move_type', '=', move_type), ('state', '=', 'posted'), | |
| ('invoice_date', '>=', a), ('invoice_date', '<=', b)] | |
| def _ldom(move_type, a, b): | |
| return [('move_id.move_type', '=', move_type), ('parent_state', '=', 'posted'), | |
| ('move_id.invoice_date', '>=', a), ('move_id.invoice_date', '<=', b), | |
| ('product_id', '!=', False)] | |
| def build(t=None): | |
| t = t or P.today() | |
| d12 = (t - dt.timedelta(days=365)).isoformat() | |
| d13m = (t.replace(day=1) - dt.timedelta(days=380)).replace(day=1).isoformat() | |
| ti = t.isoformat() | |
| # ---- headline + monthly (from the MOVES — count + untaxed value) ----------------------- | |
| def _monthly(mt): | |
| out = {} | |
| for g in O.read_group('account.move', _mdom(mt, d13m, ti), | |
| ['amount_untaxed:sum'], ['invoice_date:month'], lazy=False): | |
| k = g.get('invoice_date:month') | |
| out[str(k)] = {'value': g.get('amount_untaxed') or 0.0, 'n': g.get('__count') or 0} | |
| return out | |
| ref_m, inv_m = _monthly('out_refund'), _monthly('out_invoice') | |
| months = sorted(set(ref_m) | set(inv_m), | |
| key=lambda m: dt.datetime.strptime(m, '%B %Y')) | |
| monthly = [] | |
| for m in months: | |
| r, i = ref_m.get(m, {'value': 0, 'n': 0}), inv_m.get(m, {'value': 0, 'n': 0}) | |
| monthly.append({'month': m, 'refund_value': r['value'], 'n_refunds': r['n'], | |
| 'invoice_value': i['value'], 'n_invoices': i['n'], | |
| 'rate_pct': (r['value'] / i['value'] * 100) if i['value'] else None}) | |
| # ---- by customer (moves, 12m) ---------------------------------------------------------- | |
| def _by_partner(mt): | |
| out = {} | |
| for g in O.read_group('account.move', _mdom(mt, d12, ti), | |
| ['amount_untaxed:sum'], ['partner_id'], lazy=False): | |
| pid = O.m2o_id(g.get('partner_id')) | |
| if pid: | |
| out[pid] = {'name': O.m2o_name(g.get('partner_id')), | |
| 'value': g.get('amount_untaxed') or 0.0, 'n': g.get('__count') or 0} | |
| return out | |
| ref_c, inv_c = _by_partner('out_refund'), _by_partner('out_invoice') | |
| by_customer = [] | |
| for pid, r in ref_c.items(): | |
| inv = inv_c.get(pid, {'value': 0.0, 'n': 0}) | |
| by_customer.append({'pid': pid, 'customer': r['name'], 'ret_value': r['value'], | |
| 'n_refunds': r['n'], 'invoiced': inv['value'], | |
| 'rate_pct': (r['value'] / inv['value'] * 100) if inv['value'] else None}) | |
| by_customer.sort(key=lambda x: -x['ret_value']) | |
| # ---- by SKU (refund LINES vs invoice LINES, 12m) ---------------------------------------- | |
| def _by_product(mt): | |
| out = {} | |
| for g in O.read_group('account.move.line', _ldom(mt, d12, ti), | |
| ['price_subtotal:sum', 'quantity:sum'], ['product_id'], lazy=False): | |
| pid = O.m2o_id(g.get('product_id')) | |
| if pid: | |
| out[pid] = {'value': g.get('price_subtotal') or 0.0, | |
| 'qty': g.get('quantity') or 0.0} | |
| return out | |
| ref_p, inv_p = _by_product('out_refund'), _by_product('out_invoice') | |
| # meta for the UNION of refunded + sold products (category/supplier denominators need the | |
| # invoice side too) | |
| all_pids = list(set(ref_p) | set(inv_p)) | |
| meta = {} | |
| for i in range(0, len(all_pids), 5000): | |
| for p in O.search_read('product.product', | |
| [('id', 'in', all_pids[i:i + 5000]), | |
| ('active', 'in', [True, False])], | |
| ['default_code', 'name', 'categ_id']): | |
| meta[p['id']] = p | |
| by_sku = [] | |
| for pid, r in ref_p.items(): | |
| inv = inv_p.get(pid, {'value': 0.0, 'qty': 0.0}) | |
| m = meta.get(pid, {}) | |
| by_sku.append({'pid': pid, 'code': (m.get('default_code') or '').strip() or f'#{pid}', | |
| 'product': m.get('name') or '', | |
| 'ret_value': r['value'], 'ret_units': r['qty'], | |
| 'sold_value': inv['value'], 'sold_units': inv['qty'], | |
| 'rate_pct': (r['value'] / inv['value'] * 100) if inv['value'] else None}) | |
| by_sku.sort(key=lambda x: -x['ret_value']) | |
| # ---- by CATEGORY (product-line signal: which kinds of products come back) --------------- | |
| def _cat_of(pid): | |
| return O.m2o_name((meta.get(pid) or {}).get('categ_id')) or '(none)' | |
| cat = {} | |
| for pid, r in ref_p.items(): | |
| e = cat.setdefault(_cat_of(pid), {'ret_value': 0.0, 'ret_units': 0.0, | |
| 'sold_value': 0.0, 'n_skus': 0}) | |
| e['ret_value'] += r['value'] | |
| e['ret_units'] += r['qty'] | |
| e['n_skus'] += 1 | |
| for pid, r in inv_p.items(): | |
| e = cat.setdefault(_cat_of(pid), {'ret_value': 0.0, 'ret_units': 0.0, | |
| 'sold_value': 0.0, 'n_skus': 0}) | |
| e['sold_value'] += r['value'] | |
| by_category = [{'category': k, **v, | |
| 'rate_pct': (v['ret_value'] / v['sold_value'] * 100) | |
| if v['sold_value'] else None} | |
| for k, v in cat.items() if v['ret_value'] > 0] | |
| by_category.sort(key=lambda x: -x['ret_value']) | |
| # ---- by SUPPLIER (sourcing-quality signal) ---------------------------------------------- | |
| # dominant vendor per product from confirmed PO history (most units bought), one grouped read | |
| po_vendor, _best_q = {}, {} | |
| try: | |
| for g in O.read_group('purchase.order.line', | |
| [('order_id.state', 'in', ('purchase', 'done')), | |
| ('product_id', '!=', False)], | |
| ['product_qty:sum'], ['product_id', 'partner_id'], lazy=False): | |
| pid = O.m2o_id(g.get('product_id')) | |
| q = g.get('product_qty') or 0.0 | |
| v = O.m2o_name(g.get('partner_id')) | |
| if pid and v and q > _best_q.get(pid, 0.0): | |
| _best_q[pid] = q | |
| po_vendor[pid] = v | |
| except Exception: | |
| pass | |
| sup_map = proc_mod.suppliers() | |
| def _vendor_of(pid): | |
| code = ((meta.get(pid) or {}).get('default_code') or '').strip() | |
| cur = sup_map.get(code) or {} | |
| return (cur.get('vendor') or '').strip() or po_vendor.get(pid) or '(no supplier data)' | |
| sup = {} | |
| for pid, r in ref_p.items(): | |
| e = sup.setdefault(_vendor_of(pid), {'ret_value': 0.0, 'ret_units': 0.0, | |
| 'sold_value': 0.0, 'n_skus': 0}) | |
| e['ret_value'] += r['value'] | |
| e['ret_units'] += r['qty'] | |
| e['n_skus'] += 1 | |
| for pid, r in inv_p.items(): | |
| e = sup.setdefault(_vendor_of(pid), {'ret_value': 0.0, 'ret_units': 0.0, | |
| 'sold_value': 0.0, 'n_skus': 0}) | |
| e['sold_value'] += r['value'] | |
| by_supplier = [{'supplier': k, **v, | |
| 'rate_pct': (v['ret_value'] / v['sold_value'] * 100) | |
| if v['sold_value'] else None} | |
| for k, v in sup.items() if v['ret_value'] > 0] | |
| by_supplier.sort(key=lambda x: -x['ret_value']) | |
| # ---- by AGENT (book-behavior signal; agent = the customer's res.partner agent, NOT the | |
| # credit-note team_id). Denominator = the agent's WHOLE invoiced book, not just refunders. | |
| attrs = cust_mod._partner_attrs(list(set(ref_c) | set(inv_c))) | |
| def _agent_of(pid): | |
| return (attrs.get(pid) or {}).get('agent') or '(none)' | |
| ag = {} | |
| for pid, r in ref_c.items(): | |
| e = ag.setdefault(_agent_of(pid), {'ret_value': 0.0, 'n_refunds': 0, 'invoiced': 0.0, | |
| 'top_customer': '', 'top_value': 0.0, 'top_pid': None}) | |
| e['ret_value'] += r['value'] | |
| e['n_refunds'] += r['n'] | |
| if r['value'] > e['top_value']: | |
| e['top_value'] = r['value'] | |
| e['top_customer'] = r['name'] | |
| e['top_pid'] = pid | |
| for pid, r in inv_c.items(): | |
| e = ag.setdefault(_agent_of(pid), {'ret_value': 0.0, 'n_refunds': 0, 'invoiced': 0.0, | |
| 'top_customer': '', 'top_value': 0.0, 'top_pid': None}) | |
| e['invoiced'] += r['value'] | |
| by_agent = [{'agent': k, **v, | |
| 'rate_pct': (v['ret_value'] / v['invoiced'] * 100) if v['invoiced'] else None} | |
| for k, v in ag.items() if v['ret_value'] > 0] | |
| by_agent.sort(key=lambda x: -x['ret_value']) | |
| # ---- headline --------------------------------------------------------------------------- | |
| n_ref = sum(r['n'] for r in ref_c.values()) | |
| ref_val = sum(r['value'] for r in ref_c.values()) | |
| n_inv = sum(r['n'] for r in inv_c.values()) | |
| inv_val = sum(r['value'] for r in inv_c.values()) | |
| top10 = sum(r['ret_value'] for r in by_sku[:10]) | |
| return {'monthly': monthly, 'by_customer': by_customer, 'by_sku': by_sku, | |
| 'by_category': by_category, 'by_supplier': by_supplier, 'by_agent': by_agent, | |
| 'n_refunds': n_ref, 'refund_value': ref_val, | |
| 'n_invoices': n_inv, 'invoice_value': inv_val, | |
| 'doc_rate_pct': (n_ref / n_inv * 100) if n_inv else None, | |
| 'value_rate_pct': (ref_val / inv_val * 100) if inv_val else None, | |
| 'top10_share_pct': (top10 / ref_val * 100) if ref_val else None, | |
| 'window': (d12, ti)} | |
| def validate(t=None, team_id=None, pre=None): | |
| t = t or P.today() | |
| b = pre or build(t) | |
| d12, ti = b['window'] | |
| checks = [] | |
| srv_val = O.sum_field('account.move', _mdom('out_refund', d12, ti), 'amount_untaxed') | |
| a = sum(r['ret_value'] for r in b['by_customer']) | |
| checks.append({'check': 'Returns: Σ(per-customer refunds) == server Σ(credit-note untaxed)', | |
| 'a': round(a, 2), 'b': round(srv_val, 2), 'gap': round(a - srv_val, 2), | |
| 'ok': abs(a - srv_val) <= max(1.0, abs(srv_val) * 0.001)}) | |
| srv_line = O.sum_field('account.move.line', _ldom('out_refund', d12, ti), 'price_subtotal') | |
| a2 = sum(r['ret_value'] for r in b['by_sku']) | |
| checks.append({'check': 'Returns: Σ(per-SKU refund lines) == server Σ(refund product lines)', | |
| 'a': round(a2, 2), 'b': round(srv_line, 2), 'gap': round(a2 - srv_line, 2), | |
| 'ok': abs(a2 - srv_line) <= max(1.0, abs(srv_line) * 0.001)}) | |
| # the three new rollups are re-groupings of the SAME universes — they must tie exactly | |
| for key, base, label in (('by_category', a2, 'per-SKU lines'), | |
| ('by_supplier', a2, 'per-SKU lines'), | |
| ('by_agent', sum(r['ret_value'] for r in b['by_customer']), | |
| 'per-customer refunds')): | |
| s = sum(r['ret_value'] for r in b[key]) | |
| checks.append({'check': f'Returns: Σ({key}) == Σ({label})', | |
| 'a': round(s, 2), 'b': round(base, 2), 'gap': round(s - base, 2), | |
| 'ok': abs(s - base) <= 1.0}) | |
| return checks | |