Download platform/modules/backorders.py from fsanyoto/loopable: direct link, hf CLI and curl.
- Browser
- Download file 12.1 kB
-
https://huggingface.co/spaces/fsanyoto/loopable/resolve/9291c0100f4e7b8da2efcbbf1ebdea72ee1663e7/platform/modules/backorders.py
- Command line
-
hf download hf://spaces/fsanyoto/loopable@9291c0100f4e7b8da2efcbbf1ebdea72ee1663e7/platform/modules/backorders.py
-
curl -L -o backorders.py https://huggingface.co/spaces/fsanyoto/loopable/resolve/9291c0100f4e7b8da2efcbbf1ebdea72ee1663e7/platform/modules/backorders.py
12.1 kB
| """Backorders — what we owe customers, aged (the mirror image of AR aging). | |
| Every confirmed-but-undelivered sale line, aged against the order's promise date | |
| (commitment_date, else the earliest open outgoing picking's scheduled date), valued at the | |
| line's discounted unit price, joined to supply (on-hand + earliest inbound PO ETA) so each | |
| row carries ONE verb action: Ship (stock is on hand) / Expedite the PO (inbound exists) / | |
| Call the customer (no supply in sight). Line-level fill canon: ≥95% industry bar. | |
| Scope: wholesale teams (5/6), GIFTWARE/Amazon excluded — a backorder board is about promises | |
| to dealers. BU-filterable. | |
| """ | |
| import datetime as dt | |
| from harness.canonical_source import odoo as O | |
| import core.periods as P | |
| import modules.procurement as proc | |
| BUCKETS = ['not due', '0-7', '8-21', '22-45', '45+', 'no promise'] | |
| _OPEN_PICK_DOMAIN = [('state', 'in', ['confirmed', 'assigned', 'waiting']), | |
| ('picking_type_id.code', '=', 'outgoing')] | |
| def _chunk(ids, n=2000): | |
| ids = list(ids) | |
| for i in range(0, len(ids), n): | |
| yield ids[i:i + n] | |
| def _open_pickings(): | |
| """Open outgoing pickings → {origin(SO name): earliest scheduled date}.""" | |
| picks = O.search_read('stock.picking', list(_OPEN_PICK_DOMAIN), | |
| ['origin', 'scheduled_date']) | |
| sched = {} | |
| for p in picks: | |
| o = (p.get('origin') or '').strip() | |
| if not o: | |
| continue | |
| d = str(p.get('scheduled_date') or '')[:10] | |
| if d and (o not in sched or d < sched[o]): | |
| sched[o] = d | |
| return picks, sched | |
| def sku_backlog(t=None, team_id=None): | |
| """`(pre_book_by_code, open_backlog_by_code, report)` for the product catalogue. | |
| This is deliberately NOT `board()`'s ``not due`` bucket. That board enters through open | |
| pickings and substitutes a picking scheduled date when an order has no commitment date; | |
| pre-book means the stricter, stored-order fact ``commitment_date > end of t``. Both measures | |
| are confirmed wholesale goods that Odoo still calls pending, started, or partial. Their | |
| quantity is the positive part of ordered minus delivered, grouped by product and then by the | |
| catalogue's code. | |
| """ | |
| t = t or P.today() | |
| if isinstance(t, dt.datetime): | |
| t = t.date() | |
| end_of_day = f"{t.isoformat()} 23:59:59" | |
| teams = [team_id] if team_id is not None else O.active_team_ids() | |
| common = [ | |
| ('state', 'in', ['sale', 'done']), | |
| ('product_id', '!=', False), | |
| ('order_id.delivery_status', 'in', ['pending', 'started', 'partial']), | |
| ('product_id.type', '!=', 'service'), | |
| ] | |
| if teams: | |
| common.append(('order_id.team_id', 'in', teams)) | |
| excluded = O.excluded_partner_ids() | |
| if excluded: | |
| common.append(('order_partner_id', 'not in', list(excluded))) | |
| def _by_product(domain): | |
| groups = O.read_group('sale.order.line', domain, | |
| ['product_uom_qty:sum', 'qty_delivered:sum'], ['product_id'], | |
| lazy=False) | |
| from harness.canonical_source import active | |
| if active() is not None: | |
| return active().backlog(groups) | |
| out = {} | |
| for group in groups: | |
| pid = O.m2o_id(group.get('product_id')) | |
| if not pid: | |
| continue | |
| open_qty = max(0.0, float(group.get('product_uom_qty') or 0.0) | |
| - float(group.get('qty_delivered') or 0.0)) | |
| if open_qty > 1e-9: | |
| out[pid] = open_qty | |
| return out | |
| open_by_pid = _by_product(list(common)) | |
| pre_by_pid = _by_product(list(common) + [('order_id.commitment_date', '>', end_of_day)]) | |
| from harness.canonical_source import active | |
| pids = [] if active() is not None else sorted(set(open_by_pid) | set(pre_by_pid)) | |
| codes = {} | |
| for ch in _chunk(pids, 500): | |
| for product in O.search_read('product.product', | |
| [('id', 'in', ch), ('active', 'in', [True, False])], | |
| ['default_code']): | |
| pid = product.get('id') | |
| codes[pid] = str(product.get('default_code') or f"pid:{pid}").strip() | |
| def _by_code(by_pid): | |
| out = {} | |
| for pid, qty in by_pid.items(): | |
| code = codes.get(pid, f"pid:{pid}") | |
| out[code] = out.get(code, 0.0) + qty | |
| return out | |
| pre_by_code, open_by_code = ((pre_by_pid, open_by_pid) if active() is not None else | |
| (_by_code(pre_by_pid), _by_code(open_by_pid))) | |
| report = { | |
| 'as_of': t.isoformat(), | |
| 'team_ids': teams, | |
| 'pre_book_qty': round(sum(pre_by_code.values()), 2), | |
| 'pre_book_skus': len(pre_by_code), | |
| 'open_backlog_qty': round(sum(open_by_code.values()), 2), | |
| 'open_backlog_skus': len(open_by_code), | |
| } | |
| return pre_by_code, open_by_code, report | |
| def board(team_id=None, t=None): | |
| """The full open-line board + bucket rollup. Returns dict(rows, buckets, orders, coverage).""" | |
| t = t or P.today() | |
| today = t | |
| picks, sched = _open_pickings() | |
| # the sale orders behind those pickings, in wholesale scope | |
| orders = [] | |
| names = list(sched) | |
| ex = O.excluded_partner_ids() | |
| for ch in _chunk(names, 500): | |
| dom = [('name', 'in', ch), ('state', 'in', ['sale', 'done'])] | |
| dom.append(('team_id', '=', team_id) if team_id is not None | |
| else ('team_id', 'in', O.TEAM_IDS)) | |
| if ex: | |
| dom.append(('partner_id', 'not in', list(ex))) | |
| orders += O.search_read('sale.order', dom, | |
| ['name', 'partner_id', 'team_id', 'date_order', | |
| 'commitment_date']) | |
| by_oid = {o['id']: o for o in orders} | |
| # their lines, open qty computed locally (a domain cannot compare two fields) | |
| lines = [] | |
| for ch in _chunk(list(by_oid), 500): | |
| lines += O.search_read('sale.order.line', | |
| [('order_id', 'in', ch), ('display_type', '=', False), | |
| ('product_id', '!=', False)], | |
| ['order_id', 'order_partner_id', 'product_id', | |
| 'product_uom_qty', 'qty_delivered', 'price_unit', | |
| 'price_subtotal', 'name']) | |
| open_lines = [] | |
| for r in lines: | |
| openq = (r.get('product_uom_qty') or 0.0) - (r.get('qty_delivered') or 0.0) | |
| if openq <= 1e-3: | |
| continue | |
| qty = r.get('product_uom_qty') or 0.0 | |
| unit = (r.get('price_subtotal') or 0.0) / qty if qty else (r.get('price_unit') or 0.0) | |
| r['open_qty'] = openq | |
| r['open_value'] = openq * unit | |
| open_lines.append(r) | |
| # supply joins: on-hand + earliest inbound ETA per product | |
| pids = list({O.m2o_id(r['product_id']) for r in open_lines}) | |
| onhand, codes = {}, {} | |
| for ch in _chunk(pids): | |
| for p in O.search_read('product.product', | |
| [('id', 'in', ch), ('active', 'in', [True, False])], | |
| ['qty_available', 'default_code']): | |
| onhand[p['id']] = p.get('qty_available') or 0.0 | |
| codes[p['id']] = (p.get('default_code') or '').strip() | |
| _all_pol, pol = proc._open_po_lines() | |
| inbound = {} | |
| for l in pol: | |
| pid = O.m2o_id(l.get('product_id')) | |
| if not pid: | |
| continue | |
| cur = inbound.setdefault(pid, {'qty': 0.0, 'eta': None}) | |
| cur['qty'] += l.get('open_qty') or 0.0 | |
| eta = l.get('expected') or '' | |
| if eta and (cur['eta'] is None or eta < cur['eta']): | |
| cur['eta'] = eta | |
| rows = [] | |
| n_promised = 0 | |
| for r in open_lines: | |
| oid = O.m2o_id(r['order_id']) | |
| o = by_oid.get(oid) or {} | |
| promise = str(o.get('commitment_date') or '')[:10] or sched.get(o.get('name', ''), '') | |
| if str(o.get('commitment_date') or '')[:10]: | |
| n_promised += 1 | |
| if promise: | |
| days = (today - dt.date.fromisoformat(promise)).days | |
| bucket = ('not due' if days < 0 else '0-7' if days <= 7 | |
| else '8-21' if days <= 21 else '22-45' if days <= 45 else '45+') | |
| else: | |
| days, bucket = None, 'no promise' | |
| pid_prod = O.m2o_id(r['product_id']) | |
| oh = onhand.get(pid_prod, 0.0) | |
| inb = inbound.get(pid_prod) | |
| if oh >= r['open_qty']: | |
| action = 'Ship: stock on hand' | |
| elif inb and inb.get('eta'): | |
| action = f"Expedite PO: ETA {inb['eta']}" | |
| else: | |
| action = 'Call customer: no supply inbound' | |
| rows.append({ | |
| 'pid': O.m2o_id(r['order_partner_id']), | |
| 'customer': O.m2o_name(r['order_partner_id']), | |
| 'order': o.get('name', ''), | |
| 'bu': O.TEAM_NAMES.get(O.m2o_id(o.get('team_id')), '?'), | |
| 'sku': codes.get(pid_prod, ''), 'product': O.m2o_name(r['product_id']), | |
| 'open_qty': r['open_qty'], 'open_value': r['open_value'], | |
| 'promise': promise or '—', 'days_past': days if days is not None else '', | |
| 'bucket': bucket, 'on_hand': oh, | |
| 'inbound_eta': (inb or {}).get('eta') or '', | |
| 'action': action, | |
| }) | |
| rows.sort(key=lambda x: -x['open_value']) | |
| bucket_rollup = [] | |
| for bu in sorted({r['bu'] for r in rows}): | |
| for b in BUCKETS: | |
| v = sum(r['open_value'] for r in rows if r['bu'] == bu and r['bucket'] == b) | |
| if v: | |
| bucket_rollup.append({'bu': bu, 'bucket': b, 'value': v}) | |
| return { | |
| 'rows': rows, | |
| 'buckets': bucket_rollup, | |
| 'n_orders': len({r['order'] for r in rows}), | |
| 'open_value': sum(r['open_value'] for r in rows), | |
| 'past_value': sum(r['open_value'] for r in rows | |
| if r['bucket'] in ('0-7', '8-21', '22-45', '45+')), | |
| 'promise_coverage': (n_promised / len(open_lines) * 100.0) if open_lines else 0.0, | |
| 'n_pickings': len(picks), | |
| } | |
| def validate(t=None, team_id=None): | |
| """(1) the picking pull is complete (search_count ties the row pull — truncation guard); | |
| (2) the local open-qty arithmetic ties Odoo's own aggregates: for the pulled order set, | |
| Σ(product_uom_qty) − Σ(qty_delivered) via server-side read_group == Σ of our per-row | |
| open_qty (same universe, independent arithmetic path).""" | |
| b = board(team_id, t) | |
| n_count = O.get_odoo().search_count('stock.picking', list(_OPEN_PICK_DOMAIN)) | |
| checks = [{'check': 'open outgoing pickings: pull complete', | |
| 'a': b['n_pickings'], 'b': n_count, | |
| 'gap': b['n_pickings'] - n_count, 'ok': b['n_pickings'] == n_count}] | |
| oids = sorted({r['order'] for r in b['rows']}) | |
| if oids: | |
| dom = [('order_id.name', 'in', oids[:500]), ('display_type', '=', False), | |
| ('product_id', '!=', False)] | |
| try: | |
| g = O.read_group('sale.order.line', dom, | |
| ['product_uom_qty:sum', 'qty_delivered:sum'], [], lazy=False) | |
| srv_open = ((g[0].get('product_uom_qty') or 0.0) | |
| - (g[0].get('qty_delivered') or 0.0)) if g else 0.0 | |
| # NET open recomputed row-level (includes over-shipped negatives, which the board | |
| # excludes) so both sides measure the same universe | |
| rl = O.search_read('sale.order.line', dom, ['product_uom_qty', 'qty_delivered']) | |
| ours = sum((l.get('product_uom_qty') or 0.0) - (l.get('qty_delivered') or 0.0) | |
| for l in rl) | |
| checks.append({'check': 'open qty: server aggregate vs row arithmetic', | |
| 'a': round(ours, 2), 'b': round(srv_open, 2), | |
| 'gap': round(ours - srv_open, 2), | |
| 'ok': abs(ours - srv_open) < 1.0}) | |
| except Exception as e: | |
| checks.append({'check': 'open qty aggregate (read_group)', 'a': '—', | |
| 'b': str(e)[:60], 'gap': '', 'ok': False}) | |
| return checks | |