Spaces:
Running on CPU Upgrade
Running on CPU Upgrade
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},
]
|