File size: 12,151 Bytes
149aa87
 
 
 
 
 
 
 
 
 
 
 
 
2e7e617
149aa87
 
 
 
 
 
2e7e617
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
149aa87
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
2e7e617
149aa87
 
 
2e7e617
149aa87
2e7e617
149aa87
 
 
 
 
 
2e7e617
 
 
 
 
 
149aa87
2e7e617
 
149aa87
2e7e617
149aa87
 
 
 
 
 
 
 
2e7e617
 
 
 
 
149aa87
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
2e7e617
 
149aa87
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
2e7e617
 
149aa87
2e7e617
 
149aa87
 
2e7e617
149aa87
82cdb59
149aa87
82cdb59
149aa87
 
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
"""Order-to-Cash timing β€” where the days hide, by stage, each with its owner:

  order β†’ ship      (warehouse)   sale.order.date_order β†’ effective_date (delivery complete)
  ship β†’ invoice    (admin β€” a FREE DSO leak; links to the DNI list in Data Health)
  invoice β†’ paid    (collections) invoice date β†’ full-reconcile settlement date
                                  (same OCA partner_time_to_pay semantics as ar.days_to_pay)

Plus the terms-gap rollup: per payment term, contractual days vs $-weighted actual days β€”
"are the TERMS wrong?" β€” with the stranded free-credit $ it represents (reuses the validated
per-customer machinery in modules/ar.days_to_pay: dominant term, excess days, free credit).
Medians (not means) per stage β€” one whale order must not move the story.
"""
import datetime as dt
from copy import deepcopy

import core.odoo as O
import core.periods as P
import modules.ar as ar


_O2C_DASHBOARD = {
    'key': 'o2c-cycle',
    'title': 'Order to cash',
    'kind': 'metric_strip',
    'basisLabel': 'LTM confirmed wholesale sale/done orders; Fisch/Royal scoped; GIFTWARE DEALS excluded',
    'metrics': [
        {
            'key': 'order_to_ship_days', 'label': 'Order to ship', 'format': 'days',
            'state': 'unavailable', 'scope': 'business_unit',
            'basisLabel': 'Median days from confirmed order date to completed delivery date',
            'missingSource': 'The canonical mirror does not yet join confirmed sale orders to completed delivery dates.',
            'validationMethod': 'o2c.validate',
        },
        {
            'key': 'ship_to_invoice_days', 'label': 'Ship to invoice', 'format': 'days',
            'state': 'unavailable', 'scope': 'business_unit',
            'basisLabel': 'Median days from completed delivery to first posted customer invoice',
            'missingSource': 'The canonical mirror does not yet join completed deliveries to posted customer invoices.',
            'validationMethod': 'o2c.validate',
        },
        {
            'key': 'invoice_to_paid_days', 'label': 'Invoice to paid', 'format': 'days',
            'state': 'unavailable', 'scope': 'business_unit',
            'basisLabel': 'Median days for posted invoices aged at least 120 days to full reconciliation',
            'missingSource': 'The canonical mirror does not yet expose scoped full-reconcile settlement dates.',
            'validationMethod': 'o2c.validate',
        },
    ],
}


def dashboard_contract():
    """Return the mirror-safe O2C projection without performing a live read."""
    return deepcopy(_O2C_DASHBOARD)


def _median(v):
    v = sorted(v)
    n = len(v)
    if not n:
        return None
    return v[n // 2] if n % 2 else (v[n // 2 - 1] + v[n // 2]) / 2.0


def _d(s):
    s = str(s or '')[:10]
    try:
        return dt.date.fromisoformat(s)
    except ValueError:
        return None


def stages(team_id=None, t=None):
    """Per-BU stage medians + monthly trend over the LTM. Coverage counts are honest:
    each stage only scores orders where both endpoints exist."""
    t = t or P.today()
    lf, lt = P.ltm(t)
    ex = O.excluded_partner_ids()

    dom = [('state', 'in', ['sale', 'done']),
           ('date_order', '>=', f'{lf} 00:00:00'), ('date_order', '<=', f'{lt} 23:59:59')]
    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', 'date_order', 'effective_date', 'team_id'])
    by_name = {o['name']: o for o in orders}

    # invoices LTM joined by exact invoice_origin (multi-origin merges skipped β€” counted)
    inv_dom = [('move_type', '=', 'out_invoice'), ('state', '=', 'posted'),
               ('invoice_date', '>=', lf), ('invoice_date', '<=', lt)]
    if ex:
        inv_dom.append(('partner_id', 'not in', list(ex)))
    invs = O.search_read('account.move', inv_dom,
                         ['id', 'invoice_origin', 'invoice_date', 'name'])
    first_inv = {}
    eligible_invoice_ids = []
    multi_origin = 0
    for iv in invs:
        og = (iv.get('invoice_origin') or '').strip()
        if not og:
            continue
        if ',' in og:
            # Count only a merge that intersects this already-scoped order population.  A
            # company-wide count here would leak the other BU through a scoped response.
            if any(part.strip() in by_name for part in og.split(',')):
                multi_origin += 1
            continue
        if og not in by_name:
            continue
        if iv.get('id'):
            eligible_invoice_ids.append(iv['id'])
        d = str(iv.get('invoice_date') or '')[:10]
        if d and (og not in first_inv or d < first_inv[og]):
            first_inv[og] = d

    # invoice β†’ settled (receivable full-reconcile max counterpart date), LTM invoices
    pay_dom = [('move_id.move_type', '=', 'out_invoice'), ('move_id.state', '=', 'posted'),
               ('account_id.account_type', '=', 'asset_receivable'),
               ('full_reconcile_id', '!=', False), ('date', '>=', lf)]
    if ex:
        pay_dom.append(('partner_id', 'not in', list(ex)))
    # The invoice-to-paid cohort must stay inside the same sale-order scope.  Pulling every
    # receivable reconcile line made the consolidated pay median appear inside a BU response.
    pay_dom.append(('move_id', 'in', eligible_invoice_ids))
    rec_lines = (O.search_read('account.move.line', pay_dom, ['date', 'full_reconcile_id'])
                 if eligible_invoice_ids else [])
    fr_ids = list({O.m2o_id(l['full_reconcile_id']) for l in rec_lines})
    settle = {}
    # raw lines + local max β€” a grouped read here makes the server echo the full id-list
    # domain back per group (__domain) and dies with a MemoryError at 5k-id chunks
    for i in range(0, len(fr_ids), 5000):
        for l in O.search_read('account.move.line',
                               [('full_reconcile_id', 'in', fr_ids[i:i + 5000])],
                               ['full_reconcile_id', 'date']):
            k = O.m2o_id(l.get('full_reconcile_id'))
            d = str(l.get('date') or '')[:10]
            if k and d:
                settle[k] = max(settle.get(k, ''), d)
    # survivorship guard: a recent invoice only appears here if it is ALREADY reconciled,
    # so the newest cohort is all fast payers. Score only invoices β‰₯120d old (they've had
    # time to be slow); the honest label carries into the UI.
    pay_cutoff = (t - dt.timedelta(days=120)).isoformat()
    pay_days = []
    for l in rec_lines:
        i_d, s_d = _d(l.get('date')), _d(settle.get(O.m2o_id(l['full_reconcile_id'])))
        if i_d and s_d and s_d >= i_d and i_d.isoformat() <= pay_cutoff:
            pay_days.append(((s_d - i_d).days, i_d))

    # assemble per-order stage observations
    obs = []          # {bu, month, ship_days?, inv_days?}
    for o in orders:
        od, sd = _d(o.get('date_order')), _d(o.get('effective_date'))
        bu = O.TEAM_NAMES.get(O.m2o_id(o.get('team_id')), '?')
        month = str(o.get('date_order') or '')[:7]
        ship = (sd - od).days if od and sd and sd >= od else None
        iv = _d(first_inv.get(o['name']))
        invd = (iv - sd).days if sd and iv and iv >= sd else None
        obs.append({'bu': bu, 'month': month, 'ship': ship, 'inv': invd})

    def _roll(rows, key):
        vals = [r[key] for r in rows if r.get(key) is not None]
        return _median(vals), len(vals)

    by_bu = []
    for bu in sorted({r['bu'] for r in obs}):
        sub = [r for r in obs if r['bu'] == bu]
        m_ship, n_ship = _roll(sub, 'ship')
        m_inv, n_inv = _roll(sub, 'inv')
        by_bu.append({'bu': bu, 'order_to_ship': m_ship, 'n_ship': n_ship,
                      'ship_to_invoice': m_inv, 'n_inv': n_inv})
    m_pay = _median([d for d, _ in pay_days])

    trend = []
    months = sorted({r['month'] for r in obs})
    for m in months:
        sub = [r for r in obs if r['month'] == m]
        s, ns = _roll(sub, 'ship')
        v, nv = _roll(sub, 'inv')
        p_vals = [d for d, i_d in pay_days if i_d.isoformat()[:7] == m]
        if ns >= 10 and s is not None:
            trend.append({'month': m, 'stage': 'order-to-ship', 'days': s})
        if nv >= 10 and v is not None:
            trend.append({'month': m, 'stage': 'ship-to-invoice', 'days': v})
        if len(p_vals) >= 10:
            trend.append({'month': m, 'stage': 'invoice-to-paid', 'days': _median(p_vals)})

    ship_all, n_ship_all = _roll(obs, 'ship')
    inv_all, n_inv_all = _roll(obs, 'inv')
    return {
        'by_bu': by_bu, 'pay_median': m_pay, 'n_pay': len(pay_days), 'trend': trend,
        'ship_median': ship_all, 'n_ship': n_ship_all,
        'inv_median': inv_all, 'n_inv': n_inv_all,
        'n_orders': len(orders), 'n_invoices': len(eligible_invoice_ids),
        'multi_origin': multi_origin,
        'window': (lf, lt),
    }


def terms_gap(t=None):
    """Per payment term: contractual days vs invoiced-$-weighted actual days-to-pay and the
    stranded free-credit $ β€” rolled up from ar.days_to_pay's validated per-customer rows
    (dominant term per customer)."""
    dtp = ar.days_to_pay(t, limit=100000)
    rows = dtp['rows'] if isinstance(dtp, dict) else dtp
    per = {}
    for r in rows:
        term = r.get('terms') or '(none)'
        eff = r.get('avg_days_ytd') if r.get('avg_days_ytd') is not None else r.get('avg_days')
        if eff is None:
            continue
        e = per.setdefault(term, {'term': term, 'term_days': r.get('term_days') or 0,
                                  'w': 0.0, 'wd': 0.0, 'customers': 0, 'free_credit': 0.0,
                                  'open': 0.0})
        inv_w = max(r.get('open') or 0.0, 0.0) + 1.0   # weight by open AR (+1 floor so
                                                       # zero-AR customers still count)
        e['w'] += inv_w
        e['wd'] += eff * inv_w
        e['customers'] += 1
        e['free_credit'] += r.get('free_credit') or 0.0
        e['open'] += r.get('open') or 0.0
    out = []
    for e in per.values():
        actual = e['wd'] / e['w'] if e['w'] else None
        out.append({'term': e['term'], 'term_days': e['term_days'],
                    'actual_days': actual,
                    'gap_days': (actual - e['term_days']) if actual is not None else None,
                    'customers': e['customers'], 'open_ar': e['open'],
                    'free_credit': e['free_credit']})
    out.sort(key=lambda x: -(x['free_credit'] or 0))
    return out


def validate(t=None, team_id=None):
    """Pull-completeness both sides: LTM order count and posted-invoice count vs
    independent search_count on the same domains."""
    t = t or P.today()
    lf, lt = P.ltm(t)
    ex = O.excluded_partner_ids()
    dom = [('state', 'in', ['sale', 'done']),
           ('date_order', '>=', f'{lf} 00:00:00'), ('date_order', '<=', f'{lt} 23:59:59')]
    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)))
    b = stages(team_id, t)
    n_orders = O.get_odoo().search_count('sale.order', dom)
    scoped_orders = O.search_read('sale.order', dom, ['name'])
    scoped_names = [row.get('name') for row in scoped_orders if row.get('name')]
    inv_dom = [('move_type', '=', 'out_invoice'), ('state', '=', 'posted'),
               ('invoice_date', '>=', lf), ('invoice_date', '<=', lt),
               ('invoice_origin', 'in', scoped_names)]
    if ex:
        inv_dom.append(('partner_id', 'not in', list(ex)))
    n_inv = O.get_odoo().search_count('account.move', inv_dom) if scoped_names else 0
    return [
        {'check': 'LTM orders: pull complete', 'a': b['n_orders'], 'b': n_orders,
         'gap': b['n_orders'] - n_orders, 'ok': b['n_orders'] == n_orders},
        {'check': 'LTM posted invoices: pull complete', 'a': b['n_invoices'], 'b': n_inv,
         'gap': b['n_invoices'] - n_inv, 'ok': b['n_invoices'] == n_inv},
    ]