File size: 12,064 Bytes
c14ceee
 
 
 
 
 
 
 
 
 
 
 
 
f1ae536
c14ceee
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
79cd247
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
f1ae536
 
 
79cd247
 
 
 
 
 
 
 
 
 
 
 
 
f1ae536
 
79cd247
 
 
 
 
 
 
 
 
 
 
 
 
 
 
f1ae536
 
79cd247
 
 
 
 
 
 
 
 
 
 
c14ceee
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
4b54047
c14ceee
4b54047
c14ceee
4b54047
c14ceee
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
4b54047
c14ceee
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
4b54047
c14ceee
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
"""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