Download platform/modules/collections_exec.py from fsanyoto/loopable: direct link, hf CLI and curl.
- Browser
- Download file 19.9 kB
-
https://huggingface.co/spaces/fsanyoto/loopable/resolve/9291c0100f4e7b8da2efcbbf1ebdea72ee1663e7/platform/modules/collections_exec.py
- Command line
-
hf download hf://spaces/fsanyoto/loopable@9291c0100f4e7b8da2efcbbf1ebdea72ee1663e7/platform/modules/collections_exec.py
-
curl -L -o collections_exec.py https://huggingface.co/spaces/fsanyoto/loopable/resolve/9291c0100f4e7b8da2efcbbf1ebdea72ee1663e7/platform/modules/collections_exec.py
19.9 kB
| """Collections executive module — the LIVE Biweekly Executive Collection Report. | |
| Replicates the Excel "Royal Imports - Collection Report - Biweekly Executive" (Summary tab time-series | |
| + Overdue (FFS)/(RI) lists) but computed live from Odoo (read-only). | |
| AR as-of any date D = the receivable-account LEDGER balance: Σ balance of posted receivable move lines | |
| dated <= D (customer payments post a credit to the receivable account on the payment date, so the | |
| running account balance IS the AR). Validated to ~1% of the hardcoded report; today's value ties to | |
| Σ amount_residual exactly. BU split is by the CUSTOMER (res.partner.team_id 5=Fisch/FFS, 6=Royal/RI), | |
| NOT the invoice team (most invoices carry a generic team). Current aging uses exact per-invoice | |
| amount_residual; historical aging is a FIFO approximation conserved toward the netting total (the | |
| report's exact buckets come from Odoo's specific reconciliation, which the API cannot reconstruct — | |
| account.partial.reconcile.max_date errors server-side). READ-ONLY. | |
| """ | |
| import sys | |
| import datetime as dt | |
| from collections import defaultdict | |
| from pathlib import Path | |
| sys.path.insert(0, str(Path(__file__).resolve().parents[1])) | |
| import core.odoo as O | |
| import core.periods as P | |
| FFS, RI = 5, 6 | |
| BU_OF = {FFS: 'FFS', RI: 'RI'} | |
| AGE = ['At Date', '01-30', '31-60', '61-90', '91-120', '>120'] | |
| # Overdue-list aging (the report folds not-due + 0-30 days into "At Date"; overdue starts at 31 days). | |
| AGE_LIST = ['At Date', '31-60', '61-90', '91-120', '>120'] | |
| # Cash doesn't settle same-day, so the report's "as of" / ending period lags real-time by this many | |
| # days: the last few days of receipts haven't cleared in the bank yet and would understate collections | |
| # and overstate receivables. The ending period is therefore today - SETTLE_OFFSET_DAYS (real-time). | |
| SETTLE_OFFSET_DAYS = 7 | |
| # Targets from the report (col U). (goal_value, 'lower'|'higher' is-better) | |
| GOALS = { | |
| 'recv_FFS': (427708.0, 'lower'), 'recv_RI': (150789.0, 'lower'), | |
| 'dso': (30.0, 'lower'), 'dso_FFS': (30.0, 'lower'), 'dso_RI': (30.0, 'lower'), | |
| 'odpct_FFS': (10.0, 'lower'), 'odpct_RI': (10.0, 'lower'), 'odpct_total': (10.0, 'lower'), | |
| } | |
| def _d(s): | |
| try: | |
| return dt.date.fromisoformat(str(s)[:10]) | |
| except Exception: | |
| return None | |
| def _as_of(t=None): | |
| """The report's as-of / ending-period date: real-time minus the settlement lag (default), or an | |
| explicit override if a caller passes one. Using plain 'today' would understate the most recent | |
| receipts (cash takes ~SETTLE_OFFSET_DAYS to clear), so the ending period is offset back by it.""" | |
| return t or (P.today() - dt.timedelta(days=SETTLE_OFFSET_DAYS)) | |
| def _bucket(days): | |
| if days <= 0: | |
| return 'At Date' | |
| if days <= 30: | |
| return '01-30' | |
| if days <= 60: | |
| return '31-60' | |
| if days <= 90: | |
| return '61-90' | |
| if days <= 120: | |
| return '91-120' | |
| return '>120' | |
| def _lbucket(days): | |
| """Overdue-list bucket — not-due or within 30 days is 'At Date'; overdue starts at 31.""" | |
| if days <= 30: | |
| return 'At Date' | |
| if days <= 60: | |
| return '31-60' | |
| if days <= 90: | |
| return '61-90' | |
| if days <= 120: | |
| return '91-120' | |
| return '>120' | |
| def periods(t=None, n=14, step=14): | |
| """n biweekly periods ending at t (latest last). Each {label, end(date), beg(date), year, month}.""" | |
| t = _as_of(t) | |
| out = [] | |
| for i in range(n): | |
| end = t - dt.timedelta(days=step * i) | |
| out.append({'label': end.strftime('%m/%d'), 'end': end, 'beg': end - dt.timedelta(days=step - 1), | |
| 'year': end.year, 'month': end.month}) | |
| return list(reversed(out)) | |
| def pull(t=None): | |
| """The heavy one-time read: receivable ledger (invoice lines + reduction lines) + partner master. | |
| Records are normalized to plain dicts with parsed date strings + the customer's BU.""" | |
| rec = [('account_id.account_type', '=', 'asset_receivable'), ('parent_state', '=', 'posted')] | |
| inv = O.search_read('account.move.line', | |
| rec + [('move_id.move_type', 'in', ['out_invoice', 'out_refund'])], | |
| ['date', 'date_maturity', 'balance', 'amount_residual', 'partner_id'], limit=300000) | |
| red = O.search_read('account.move.line', | |
| rec + [('move_id.move_type', 'not in', ['out_invoice', 'out_refund'])], | |
| ['date', 'balance', 'partner_id'], limit=300000) | |
| ex = set(O.excluded_partner_ids()) | |
| pids = sorted({O.m2o_id(r['partner_id']) for r in inv + red | |
| if r.get('partner_id') and O.m2o_id(r['partner_id']) not in ex}) | |
| parts = {p['id']: p for p in O.search_read('res.partner', [('id', 'in', pids)], | |
| ['name', 'team_id', 'user_id', 'property_payment_term_id'])} | |
| def bu(pid): | |
| return BU_OF.get(O.m2o_id(parts.get(pid, {}).get('team_id'))) | |
| I, R = [], [] | |
| for r in inv: | |
| pid = O.m2o_id(r['partner_id']) | |
| if pid in ex or pid not in parts: | |
| continue | |
| I.append({'pid': pid, 'bu': bu(pid), 'date': str(r.get('date') or '')[:10], | |
| 'due': str(r.get('date_maturity') or r.get('date') or '')[:10], | |
| 'bal': r.get('balance') or 0.0, 'resid': r.get('amount_residual') or 0.0}) | |
| for r in red: | |
| pid = O.m2o_id(r['partner_id']) | |
| if pid in ex or pid not in parts: | |
| continue | |
| R.append({'pid': pid, 'bu': bu(pid), 'date': str(r.get('date') or '')[:10], | |
| 'bal': r.get('balance') or 0.0}) | |
| return {'inv': I, 'red': R, 'parts': parts, | |
| 'pulled_at': dt.datetime.now().strftime('%Y-%m-%d %H:%M')} | |
| # ---------------------------------------------------------------- as-of-date primitives | |
| def _recv_by_partner(data, D): | |
| """AR netting as-of D, per partner = Σ(invoice + reduction balances dated <= D).""" | |
| bal = defaultdict(float) | |
| for r in data['inv']: | |
| if r['date'] <= D: | |
| bal[r['pid']] += r['bal'] | |
| for r in data['red']: | |
| if r['date'] <= D: | |
| bal[r['pid']] += r['bal'] | |
| return bal | |
| def _agg_bu(by_partner, data): | |
| out = {'FFS': 0.0, 'RI': 0.0, 'total': 0.0} | |
| for pid, v in by_partner.items(): | |
| bu = BU_OF.get(O.m2o_id(data['parts'].get(pid, {}).get('team_id'))) | |
| if bu: | |
| out[bu] += v | |
| out['total'] += v | |
| return out | |
| def _aging(data, D, recv=None, exact=False): | |
| """Aging buckets ($) by BU as-of D, + overdue total by BU. exact=True uses live per-invoice | |
| amount_residual (current period); else FIFO (payments applied oldest-due-first). When `recv` | |
| (netting by BU) is given, the buckets are conserved to it — the difference (unapplied payments / | |
| credits sitting in the receivable account, not tied to an invoice) lands in 'At Date'.""" | |
| Dd = _d(D) | |
| inv_by = defaultdict(list) | |
| red_by = defaultdict(float) | |
| for r in data['inv']: | |
| if r['date'] <= D: | |
| inv_by[r['pid']].append(r) | |
| if not exact: | |
| for r in data['red']: | |
| if r['date'] <= D: | |
| red_by[r['pid']] += r['bal'] | |
| out = {'FFS': {b: 0.0 for b in AGE}, 'RI': {b: 0.0 for b in AGE}} | |
| overdue = {'FFS': 0.0, 'RI': 0.0} | |
| for pid, invs in inv_by.items(): | |
| bu = invs[0]['bu'] | |
| if bu not in out: | |
| continue | |
| if exact: | |
| for r in invs: | |
| amt = r['resid'] | |
| if abs(amt) < 1e-6: | |
| continue | |
| days = (Dd - (_d(r['due']) or Dd)).days | |
| out[bu][_bucket(days)] += amt | |
| if days > 30 and amt > 0: | |
| overdue[bu] += amt | |
| else: | |
| pay = -red_by.get(pid, 0.0) | |
| pos = [] | |
| for r in invs: | |
| if r['bal'] >= 0: | |
| pos.append([r['due'], r['bal']]) | |
| else: | |
| pay += -r['bal'] # credit notes act like reductions | |
| pos.sort(key=lambda x: x[0]) # oldest due first | |
| for it in pos: | |
| if pay <= 0: | |
| break | |
| take = min(pay, it[1]) | |
| it[1] -= take | |
| pay -= take | |
| for due, amt in pos: | |
| if amt < 1e-6: | |
| continue | |
| days = (Dd - (_d(due) or Dd)).days | |
| out[bu][_bucket(days)] += amt | |
| if days > 30 and amt > 0: | |
| overdue[bu] += amt | |
| if recv is not None: | |
| for bu in ('FFS', 'RI'): | |
| out[bu]['At Date'] += recv.get(bu, 0.0) - sum(out[bu].values()) | |
| return out, overdue | |
| def _ltm_invoiced(data, D): | |
| """Net invoiced (out_invoice − out_refund) in the trailing 365 days ending D, by BU — for DSO.""" | |
| lo = (_d(D) - dt.timedelta(days=365)).isoformat() | |
| out = {'FFS': 0.0, 'RI': 0.0, 'total': 0.0} | |
| for r in data['inv']: | |
| if lo < r['date'] <= D: | |
| if r['bu']: | |
| out[r['bu']] += r['bal'] | |
| out['total'] += r['bal'] | |
| return out | |
| def _flows(data, beg, end): | |
| """New sales (net invoiced) + new collection (payments) in [beg, end], by BU.""" | |
| b, e = beg.isoformat(), end.isoformat() | |
| sales = {'FFS': 0.0, 'RI': 0.0, 'total': 0.0} | |
| coll = {'FFS': 0.0, 'RI': 0.0, 'total': 0.0} | |
| for r in data['inv']: | |
| if b <= r['date'] <= e and r['bu']: | |
| sales[r['bu']] += r['bal'] | |
| sales['total'] += r['bal'] | |
| for r in data['red']: | |
| if b <= r['date'] <= e and r['bu']: | |
| coll[r['bu']] += -r['bal'] | |
| coll['total'] += -r['bal'] | |
| return sales, coll | |
| def _last_payment(data): | |
| lp = {} | |
| for r in data['red']: | |
| if r['bal'] < 0 and r['date']: | |
| if r['pid'] not in lp or r['date'] > lp[r['pid']]: | |
| lp[r['pid']] = r['date'] | |
| return lp | |
| def _idle(data, D, lp, recv_pp): | |
| Dd = _d(D) | |
| out = {'FFS': {'IDLE3': 0, 'IDLE6': 0, 'IDLE12': 0, 'total': 0}, | |
| 'RI': {'IDLE3': 0, 'IDLE6': 0, 'IDLE12': 0, 'total': 0}} | |
| for pid, bal in recv_pp.items(): | |
| if bal <= 1: | |
| continue | |
| bu = BU_OF.get(O.m2o_id(data['parts'].get(pid, {}).get('team_id'))) | |
| if bu not in out: | |
| continue | |
| last = lp.get(pid) | |
| months = ((Dd - _d(last)).days / 30.0) if last else 999 | |
| tier = 'IDLE12' if months > 12 else 'IDLE6' if months > 6 else 'IDLE3' if months > 3 else None | |
| if tier: | |
| out[bu][tier] += 1 | |
| out[bu]['total'] += 1 | |
| return out | |
| def _is_cod(term_name): | |
| return any(k in (term_name or '').lower() for k in ('immediate', 'cod', 'cash')) | |
| def _credit_terms(data, recv_pp): | |
| out = {'FFS': {'COD': 0, 'TOP': 0, 'total': 0}, 'RI': {'COD': 0, 'TOP': 0, 'total': 0}} | |
| for pid, bal in recv_pp.items(): | |
| if bal <= 1: | |
| continue | |
| p = data['parts'].get(pid, {}) | |
| bu = BU_OF.get(O.m2o_id(p.get('team_id'))) | |
| if bu not in out: | |
| continue | |
| out[bu]['COD' if _is_cod(O.m2o_name(p.get('property_payment_term_id'))) else 'TOP'] += 1 | |
| out[bu]['total'] += 1 | |
| return out | |
| # ---------------------------------------------------------------- assembly | |
| def summary(data, t=None): | |
| """The biweekly time-series — a list of per-period metric dicts (latest last).""" | |
| t = _as_of(t) | |
| lp = _last_payment(data) | |
| pers = periods(t) | |
| rows = [] | |
| for i, pr in enumerate(pers): | |
| D = pr['end'].isoformat() | |
| recv_pp = _recv_by_partner(data, D) | |
| recv = _agg_bu(recv_pp, data) | |
| aging, overdue = _aging(data, D, recv, exact=(i == len(pers) - 1)) | |
| ltm = _ltm_invoiced(data, D) | |
| sales, coll = _flows(data, pr['beg'], pr['end']) | |
| dso = {bu: (recv[bu] / (ltm[bu] / 365.0)) if ltm.get(bu) else None for bu in ('FFS', 'RI', 'total')} | |
| rows.append({'period': pr, 'recv': recv, 'overdue': overdue, 'aging': aging, 'ltm': ltm, | |
| 'dso': dso, 'sales': sales, 'coll': coll, | |
| 'idle': _idle(data, D, lp, recv_pp), 'terms': _credit_terms(data, recv_pp)}) | |
| for i, r in enumerate(rows): # change in receivable vs prior period end | |
| prev = rows[i - 1]['recv'] if i else None | |
| r['change'] = {bu: (r['recv'][bu] - prev[bu]) if prev else None for bu in ('FFS', 'RI', 'total')} | |
| return rows | |
| def overdue_list(data, team, t=None): | |
| """Current (as-of t) ranked overdue customers for a BU (5=FFS / 6=RI), exact per-invoice.""" | |
| t = _as_of(t) | |
| Dd = t | |
| bu = BU_OF.get(team) | |
| lp = _last_payment(data) | |
| by = {} | |
| for r in data['inv']: | |
| if r['bu'] != bu: | |
| continue | |
| amt = r['resid'] | |
| if abs(amt) < 1e-6: | |
| continue | |
| e = by.setdefault(r['pid'], dict({'pid': r['pid'], 'total': 0.0, 'overdue': 0.0}, **{b: 0.0 for b in AGE_LIST})) | |
| e['total'] += amt | |
| days = (Dd - (_d(r['due']) or Dd)).days | |
| e[_lbucket(days)] += amt | |
| if days > 30 and amt > 0: | |
| e['overdue'] += amt | |
| tot_od = sum(e['overdue'] for e in by.values() if e['overdue'] > 0) or 1.0 | |
| rows = [] | |
| for pid, e in by.items(): | |
| if e['overdue'] <= 0: | |
| continue | |
| p = data['parts'].get(pid, {}) | |
| last = lp.get(pid) | |
| months = ((Dd - _d(last)).days / 30.0) if last else 999 | |
| idle = 'IDLE12' if months > 12 else 'IDLE6' if months > 6 else 'IDLE3' if months > 3 else 'Active' | |
| term = O.m2o_name(p.get('property_payment_term_id')) | |
| rows.append(dict({'customer': p.get('name') or '(unknown)', 'odoo_id': pid, | |
| 'sales_rep': O.m2o_name(p.get('user_id')) or '', | |
| 'top': 'COD' if _is_cod(term) else (term or 'TOP'), | |
| 'overdue': e['overdue'], 'overdue_pct': e['overdue'] / tot_od * 100, | |
| 'idle': idle, 'total_recv': e['total']}, **{b: e[b] for b in AGE_LIST})) | |
| rows.sort(key=lambda x: -x['overdue']) | |
| for i, r in enumerate(rows, 1): | |
| r['no'] = i | |
| return rows | |
| # ---------------------------------------------------------------- Odoo Aged Receivable oracle | |
| #: The Aged Receivable line domain (modules/ar.py AGED_LINE_DOMAIN), restated so this oracle | |
| #: stays independent of the field it checks. | |
| AGED_LINES = [('account_id.account_type', '=', 'asset_receivable'), | |
| ('account_id.non_trade', '=', False), | |
| ('parent_state', '=', 'posted'), ('reconciled', '=', False)] | |
| #: Report columns as due-date day ranges past `t`: (key, newest days, oldest days or None). | |
| AGED_COLUMNS = (('current', None, 0), ('1_30', 1, 30), ('31_60', 31, 60), ('61_90', 61, 90), | |
| ('90_plus', 91, None)) | |
| def _due_window(t, newest, oldest): | |
| """Domain for lines due `newest`..`oldest` days before `t`; a line with no due date uses its date.""" | |
| def on(field): | |
| dom = [(field, '!=', False)] if field == 'date_maturity' else [] | |
| if newest is not None: | |
| dom.append((field, '<=', str(t - dt.timedelta(days=newest)))) | |
| if oldest is not None: | |
| dom.append((field, '>=', str(t - dt.timedelta(days=oldest)))) | |
| return dom | |
| due, plain = on('date_maturity'), [('date_maturity', '=', False)] + on('date') | |
| return ['|'] + ['&'] * (len(due) - 1) + due + ['&'] * (len(plain) - 1) + plain | |
| def odoo_aging_by_team(t=None, teams=(FFS, RI)): | |
| """Odoo's Aged Receivable per customer sales team, summed server side: {team: {column: $}}. | |
| The independent side of the Collection report tie. `total` is every open line; `overdue` is | |
| every line past due on `t` (no grace, R37). Read only. | |
| """ | |
| t = t or P.today() | |
| base = AGED_LINES + [('date', '<=', str(t))] | |
| out = {} | |
| for team in teams: | |
| dom = base + [('partner_id.team_id', '=', team)] | |
| row = {'total': O.sum_field('account.move.line', dom, 'amount_residual')} | |
| for key, newest, oldest in AGED_COLUMNS: | |
| row[key] = O.sum_field('account.move.line', dom + _due_window(t, newest, oldest), | |
| 'amount_residual') | |
| row['overdue'] = O.sum_field('account.move.line', dom + _due_window(t, 1, None), 'amount_residual') | |
| out[team] = row | |
| return out | |
| def _by_partner(team, t, window): | |
| dom = AGED_LINES + [('date', '<=', str(t)), ('partner_id.team_id', '=', team)] + window | |
| groups = O.read_group('account.move.line', dom, ['amount_residual:sum'], ['partner_id'], lazy=False) | |
| return {O.m2o_id(g['partner_id']): g.get('amount_residual') or 0.0 | |
| for g in groups if g.get('partner_id')} | |
| def odoo_overdue_by_partner(team, t=None): | |
| """{partner id: overdue $} for one customer sales team, from one server-side read_group.""" | |
| t = t or P.today() | |
| return _by_partner(team, t, _due_window(t, 1, None)) | |
| def odoo_open_by_partner(team, t=None): | |
| """{partner id: open receivable $} for one customer sales team.""" | |
| return _by_partner(team, t or P.today(), []) | |
| def odoo_terms(pids): | |
| """{partner id: payment term name} from res.partner, archived partners included.""" | |
| rows = O.search_read('res.partner', [('id', 'in', sorted(pids)), ('active', 'in', [True, False])], | |
| ['property_payment_term_id']) if pids else [] | |
| return {r['id']: O.m2o_name(r.get('property_payment_term_id')) or '' for r in rows} | |
| def goal_status(key, value): | |
| """('goal', 'Achieved'|'Unmet') for a metric vs the report's hardcoded target, or (None, None).""" | |
| g = GOALS.get(key) | |
| if not g or value is None: | |
| return None, None | |
| target, better = g | |
| ok = (value <= target) if better == 'lower' else (value >= target) | |
| return target, ('Achieved' if ok else 'Unmet') | |
| def outreach_preview(item): | |
| """Build a no-send collections preview from a mirror worklist item. | |
| This seam deliberately has no mail client, recipient lookup, queue call, or mutation. The | |
| ordinary Finance workflow can therefore help an owner prepare follow-up language while the | |
| separately safeguarded statements sender remains the only sanctioned outward path. | |
| """ | |
| item = item if isinstance(item, dict) else {} | |
| customer = str(item.get('customer') or 'Unavailable customer') | |
| document = str(item.get('document') or 'Unavailable document') | |
| amount = item.get('amount') | |
| amount_text = (f"${amount:,.2f}" if isinstance(amount, (int, float)) and not isinstance(amount, bool) | |
| else 'an unavailable balance') | |
| return { | |
| 'mode': 'preview_only', | |
| 'send_allowed': False, | |
| 'recipient': None, | |
| 'subject': f'Account follow-up for {document}', | |
| 'body': (f'{customer}: please review {document}, which currently shows ' | |
| f'{amount_text} outstanding. Confirm the source record before any follow-up.'), | |
| } | |
| def validate(data, t=None): | |
| """Aging buckets (conserved) tie to BU receivable; the Overdue list total ties to the summary's | |
| current overdue (the two independent code paths agree).""" | |
| t = _as_of(t) | |
| D = t.isoformat() | |
| recv = _agg_bu(_recv_by_partner(data, D), data) | |
| aging, overdue = _aging(data, D, recv, exact=True) | |
| ag_total = sum(aging['FFS'].values()) + sum(aging['RI'].values()) | |
| checks = [{'check': 'Aging buckets (FFS+RI) == receivable (FFS+RI)', | |
| 'a': round(ag_total, 2), 'b': round(recv['FFS'] + recv['RI'], 2), | |
| 'gap': round(ag_total - (recv['FFS'] + recv['RI']), 2), | |
| 'ok': abs(ag_total - (recv['FFS'] + recv['RI'])) <= 1.0}] | |
| od_list = sum(r['overdue'] for r in overdue_list(data, FFS, t)) + sum(r['overdue'] for r in overdue_list(data, RI, t)) | |
| od_sum = overdue['FFS'] + overdue['RI'] | |
| checks.append({'check': 'Overdue list total == summary current overdue', | |
| 'a': round(od_list, 2), 'b': round(od_sum, 2), 'gap': round(od_list - od_sum, 2), | |
| 'ok': abs(od_list - od_sum) <= 1.0}) | |
| return checks | |