"""Agent module — per-agent (res.partner.agent_ids) analytics. An *agent* owns a **book** of customers (the same attribute the Customers module slices by). This module reports that book the way Sales/Customers/SKU report the whole company: a period scorecard with custom date windows (Today / WTD / Last week / MTD / QTD / YTD / any custom range), a sales trend, returns, top SKUs (with profit/order) and the FULL customer list — INCLUDING inactive accounts (no recent orders) so a rep sees who they've stopped selling to. Scope: reuses the Sales `order_domain` (Fisch+Royal, excluded accounts removed, state sale/done) so numbers tie to every other module. Returns are consolidated (credit notes aren't BU-tagged); everything else is BU-filterable via team_id. """ import sys import datetime as dt from pathlib import Path sys.path.insert(0, str(Path(__file__).resolve().parents[1])) import core.odoo as O import core.periods as P import modules.sales as sales_mod import modules.customers as cust_mod def options(t=None, team_id=None): """Agent names with book activity — for the page/drawer picker.""" return cust_mod.agent_options(t, team_id) def _book(name): """frozenset of every partner id assigned to the agent (incl. inactive). None only for 'All'.""" return cust_mod.agent_partner_ids(name) def _rev(date_from, date_to, team_id, book): return O.sum_field('sale.order', sales_mod.order_domain(date_from, date_to, team_id, partner_ids=book), 'amount_untaxed') def _orders(date_from, date_to, team_id, book): return O.get_odoo().search_count('sale.order', sales_mod.order_domain(date_from, date_to, team_id, partner_ids=book)) def _custs(date_from, date_to, team_id, book): return O.distinct_count('sale.order', sales_mod.order_domain(date_from, date_to, team_id, partner_ids=book), 'partner_id') # ------------------------------------------------------------ period scorecard (custom dating) # Today / WTD / Last week / MTD / QTD / YTD — each carries its window so the UI can decompose it # and so "what did this agent sell this/last week" is a click, not a date-math exercise. _PERIODS = [('Today', 'today'), ('Week to date', 'wtd'), ('Last week', 'lwk'), ('Month to date', 'mtd'), ('Quarter to date', 'qtd'), ('Year to date', 'ytd')] def _window(key, t): if key == 'today': return P._d(t), P._d(t) if key == 'lwk': # the full prior Mon–Sun week f, _tt = P.wtd(t) start = dt.date.fromisoformat(f) - dt.timedelta(days=7) return start.isoformat(), (dt.date.fromisoformat(f) - dt.timedelta(days=1)).isoformat() return {'wtd': P.wtd, 'mtd': P.mtd, 'qtd': P.qtd, 'ytd': P.ytd}[key](t) def scorecard(name, t=None, team_id=None): """Book revenue (+ YoY same-period), orders for Today / WTD / Last week / MTD / QTD / YTD.""" t = t or P.today() book = _book(name) out = [] for label, key in _PERIODS: f, tt = _window(key, t) wk = key in ('today', 'wtd', 'lwk') # weekday-align the short windows' LY compare cf, ct = P.shift_year(f, tt, weeks=wk) rev, rev_ly = _rev(f, tt, team_id, book), _rev(cf, ct, team_id, book) out.append({'key': key, 'label': label, 'date_from': f, 'date_to': tt, 'cmp_from': cf, 'cmp_to': ct, 'revenue': rev, 'revenue_ly': rev_ly, 'yoy_pct': P.yoy_pct(rev, rev_ly), 'orders': _orders(f, tt, team_id, book)}) return out def headline(name, date_from, date_to, team_id=None): """Book KPIs for an ARBITRARY window (custom dating): revenue + YoY (same window LY), orders, active customers, AOV and returns $ / return rate.""" book = _book(name) cf, ct = P.shift_year(date_from, date_to, weeks=False) rev, rev_ly = _rev(date_from, date_to, team_id, book), _rev(cf, ct, team_id, book) orders = _orders(date_from, date_to, team_id, book) ret = sales_mod._returns_amt(date_from, date_to, book) return {'date_from': date_from, 'date_to': date_to, 'cmp_from': cf, 'cmp_to': ct, 'revenue': rev, 'revenue_ly': rev_ly, 'yoy_pct': P.yoy_pct(rev, rev_ly), 'orders': orders, 'customers': _custs(date_from, date_to, team_id, book), 'aov': (rev / orders) if orders else 0.0, 'returns': ret, 'return_rate_pct': (ret / rev * 100.0) if rev else 0.0} # ------------------------------------------------------------ sales trend def _book_monthly_rev(book, date_from, date_to, team_id=None): g = O.read_group('sale.order', sales_mod.order_domain(date_from, date_to, team_id, partner_ids=book), ['amount_untaxed:sum'], ['date_order:month'], lazy=False) out = {} for r in g: ym = ((r.get('__range') or {}).get('date_order:month') or {}).get('from', '')[:7] if ym: out[ym] = r.get('amount_untaxed') or 0.0 return out def monthly(name, n=13, t=None, team_id=None): """Book sales per month vs the same month last year (one month-grouped query over 2 years).""" t = t or P.today() book = _book(name) mrev = _book_monthly_rev(book, dt.date(t.year - 2, t.month, 1).isoformat(), t.isoformat(), team_id) rows = [] for ym, _s, _e in P.month_starts(n, t): y, m = int(ym[:4]) - 1, int(ym[5:7]) this, last = mrev.get(ym, 0.0), mrev.get(f'{y:04d}-{m:02d}', 0.0) rows.append({'month': ym, 'revenue': this, 'revenue_ly': last, 'yoy_pct': P.yoy_pct(this, last)}) return rows # ------------------------------------------------------------ full customer book (incl. inactive) def customers(name, t=None, team_id=None): """EVERY customer in the agent's book, including inactive accounts (no YTD/LY orders) — those show $0 with status 'Inactive'/'Dormant'. Each row is clickable to the customer drawer and carries recency so a rep can see who's gone quiet. Sorted by YTD revenue desc (inactive last).""" t = t or P.today() book = _book(name) yf, yt = P.ytd(t) lf, lt = P.ytd_last_year(t) this = cust_mod._cust_rev(yf, yt, team_id, book) last = cust_mod._cust_rev(lf, lt, team_id, book) lastord = cust_mod._last_order_dates(None, None, team_id, book) # all-time last order = recency ids = list(book) if book is not None else list(set(this) | set(last)) attrs = cust_mod._partner_attrs(set(ids)) namemap = {r['id']: r.get('name') for r in O.search_read('res.partner', [('id', 'in', ids)], ['name'])} rows = [] for p in ids: tr = this.get(p, {}).get('rev', 0.0) lr = last.get(p, {}).get('rev', 0.0) a = attrs.get(p, {}) lo = lastord.get(p, '') recency = (t - dt.date.fromisoformat(lo)).days if lo else None status = 'Active' if tr > 0 else ('Dormant' if (lr > 0 or lo) else 'Inactive') rows.append({'pid': p, 'customer': namemap.get(p) or (this.get(p) or last.get(p) or {}).get('name', '?'), 'rev_ytd': tr, 'rev_ly': lr, 'change': tr - lr, 'yoy_pct': P.yoy_pct(tr, lr), 'orders': this.get(p, {}).get('orders', 0), 'last_order': lo, 'recency_days': recency, 'status': status, 'city': a.get('city', '(none)'), 'state': a.get('state', '(none)'), 'agent': a.get('agent', name)}) rows.sort(key=lambda r: (r['rev_ytd'] <= 0, -r['rev_ytd'], -(r['rev_ly']))) return rows # ------------------------------------------------------------ top SKUs (with profit/order) def top_skus(name, t=None, team_id=None, top=30): """The book's top SKUs YTD (line-level), each with revenue, units, margin and profit/order.""" t = t or P.today() book = _book(name) if book is not None and not book: return [] yf, yt = P.ytd(t) lex = [('order_partner_id', 'in', list(book))] if book is not None else None return sales_mod.decompose(yf, yt, team_id, line_extra=lex, top=top)['skus'] # ------------------------------------------------------------ returns (book-scoped, consolidated) def returns_trend(name, n=13, t=None): return sales_mod.returns_monthly(n, t, partner_ids=_book(name)) def returns_headline(name, t=None): return sales_mod.returns_headline(t, partner_ids=_book(name)) # ------------------------------------------------------------ all-agents rollup (the page table) def _returns_by_agent(t=None): """{agent_name: returns$ YTD} — credit notes mapped to each customer's agent.""" by_p = sales_mod.returns_by_partner(t) attrs = cust_mod._partner_attrs(list(by_p)) agg = {} for pid, amt in by_p.items(): a = (attrs.get(pid) or {}).get('agent') or '(none)' agg[a] = agg.get(a, 0.0) + amt return agg def rollup(t=None, team_id=None): """Every agent ranked by YTD book revenue (+ YoY, customers, orders) with returns $ and return rate. Reuses the Customers MECE agent rollup, so Σ(agents) == total YTD revenue.""" rows = cust_mod.by_dimension('agent', t, team_id=team_id) ret = _returns_by_agent(t) for r in rows: r['agent'] = r['group'] r['returns'] = ret.get(r['group'], 0.0) r['return_rate_pct'] = (r['returns'] / r['revenue'] * 100.0) if r.get('revenue') else 0.0 return rows # ------------------------------------------------------------ VALIDATION def validate(t=None, team_id=None): """Reconcile the agent rollup to Odoo. (1) Σ(agent book revenue) == total YTD revenue — the rollup is MECE over customers. (2) A sampled agent's scorecard YTD == its headline YTD.""" t = t or P.today() yf, yt = P.ytd(t) checks = [] rows = rollup(t, team_id=team_id) agent_sum = sum(r['revenue'] for r in rows) total = O.sum_field('sale.order', sales_mod.order_domain(yf, yt, team_id), 'amount_untaxed') checks.append({'check': 'YTD revenue: Σ(agent book) == total', 'a': round(agent_sum, 2), 'b': round(total, 2), 'gap': round(agent_sum - total, 2), 'ok': abs(agent_sum - total) <= max(1.0, 0.001 * (total or 1))}) # sampled agent: scorecard YTD == headline YTD for the same window sample = next((r['agent'] for r in rows if r['agent'] not in ('(none)',)), None) if sample: sc_ytd = next((s['revenue'] for s in scorecard(sample, t, team_id) if s['key'] == 'ytd'), 0.0) hl = headline(sample, yf, yt, team_id)['revenue'] checks.append({'check': f'Agent "{sample}": scorecard YTD == headline YTD', 'a': round(sc_ytd, 2), 'b': round(hl, 2), 'gap': round(sc_ytd - hl, 2), 'ok': abs(sc_ytd - hl) <= 1.0}) checks.extend(validate_measures(t=t, team_id=team_id)) return checks def validate_measures(t=None, team_id=None, days=90): """⭐⭐ W37-T12 — the minted PER-AGENT lookback columns, against a DIRECT Odoo aggregate. ⛔ WHICH AGENT SOURCE, AND THE TICKET REQUIRES IT SAID OUT LOUD: this reconciles ROUTE 1, the CUSTOMER-MASTER BOOK (`res.partner.agent_ids` -> the mirror's `res_partner.agent_id`, which is `sales_lines`' `agent` dim). It is NOT the OCA commission route below — measured 2026-08-19, 9 agents carry route-1 revenue against 12 on commission lines and 17 carrying the flag, so the two produce materially different rankings and a check that mixed them would be comparing two different questions and calling the gap an error. ⚠ THE ORACLE IS THE ORDER HEADER, not the mirror the columns are served from. `sale.order` grouped by `partner_id`, mapped to each customer's agent CLIENT-SIDE — a different model, a different grain and a different code path from `store_query`'s line-level sum, so agreement between them is evidence rather than tautology. ⚠ Windowed to the MIRROR'S newest order, like `product_data.validate_measures`, so the residual is about EDITS to a shared period and not about orders the mirror has never seen. """ from harness import datastore as DS from harness import semantic as sem t = t or P.today() checks = [] try: if not DS.ready(): return [{'check': 'agent lookback measures reconcile to Odoo', 'a': 'no mirror', 'b': '-', 'ok': False, 'detail': 'the tenant store is not readable, so this is UNPROVEN, which standing rule ' '8 does not accept as green'}] except Exception as e: # noqa: BLE001 return [{'check': 'agent lookback measures reconcile to Odoo', 'a': type(e).__name__, 'b': '-', 'ok': False, 'detail': str(e)[:200]}] offer = sem.entity_measures('odoo_agents') checks.append({'check': 'the agent measure OFFER is non-empty and every key resolves ' '(owner item 4 / R1)', 'a': len(offer), 'b': '>0', 'ok': bool(offer), 'detail': {'keys': [m['key'] for m in offer], 'refused': sem.entity_measure_refusals('odoo_agents')}}) if not offer: return checks con = DS.ro_cursor() try: newest = con.execute('SELECT max(date_order) FROM sale_order').fetchone() finally: con.close() d_to = t - dt.timedelta(days=2) if newest and newest[0]: try: d_to = min(d_to, dt.date.fromisoformat(str(newest[0])[:10]) - dt.timedelta(days=1)) except ValueError: pass d_from = d_to - dt.timedelta(days=days) DF, DT = d_from.isoformat(), d_to.isoformat() ours = sem.entity_measure_values('odoo_agents', ['revenue'], date_from=DF, date_to=DT, team_id=team_id, offer=offer) # THE ORACLE — order headers, grouped by customer, mapped to that customer's agent here. o = O.get_odoo() grp = o.read_group('sale.order', sales_mod.order_domain(DF, DT, team_id), ['partner_id', 'amount_untaxed:sum'], ['partner_id'], lazy=False) pids = sorted({r['partner_id'][0] for r in grp if r.get('partner_id')}) agent_of = {} for i in range(0, len(pids), 500): for p in o.search_read('res.partner', [('id', 'in', pids[i:i + 500])], ['id', 'agent_ids']): ag = (p.get('agent_ids') or []) if ag: agent_of[p['id']] = ag[0] # the Customers-module convention: agent_ids[0] theirs = {} for r in grp: if not r.get('partner_id'): continue a = agent_of.get(r['partner_id'][0]) if a is not None: theirs[a] = theirs.get(a, 0.0) + r['amount_untaxed'] ours_tot = round(sum(c.get('revenue', 0) for c in ours.values()), 2) theirs_tot = round(sum(theirs.values()), 2) # ⚠ ORDER-HEADER vs LINE-SUM is a REAL basis difference (an order's untaxed total includes # lines this topic's service filter drops), so the tolerance is a stated 3% rather than a # cent — and the FIGURE is reported so a drift is readable instead of absorbed. gap = ours_tot - theirs_tot checks.append({ 'check': 'per-agent revenue (BOOK route) vs an INDEPENDENT Odoo order-header aggregate ' 'mapped through res.partner.agent_ids', 'a': ours_tot, 'b': theirs_tot, 'gap': round(gap, 2), 'ok': bool(theirs_tot) and abs(gap) <= 0.03 * theirs_tot, 'detail': {'window': [DF, DT], 'agents_ours': len(ours), 'agents_theirs': len(theirs), 'gap_pct': round(gap / theirs_tot * 100, 3) if theirs_tot else None, 'route': 'customer-master book (res.partner.agent_ids), NOT the OCA ' 'commission table; the two name different people'}}) # ⛔ AND PER AGENT, because a total can agree while every row is keyed wrong — the join-key # trap contract C1 names. Here the key is the `res.partner` id at both ends. off_by = sorted(((abs(ours.get(a, {}).get('revenue', 0.0) - v), a) for a, v in theirs.items() if v), reverse=True) bad = [(a, round(ours.get(a, {}).get('revenue', 0.0), 2), round(theirs[a], 2)) for d, a in off_by if d > 0.03 * theirs[a]] checks.append({ 'check': 'each agent\'s own figure ties (the C1 join-key test: a wrong key agrees in ' 'total and disagrees on every row)', 'a': len(bad), 'b': 0, 'ok': not bad, 'detail': {'worst': [(a, ours_v, th_v) for a, ours_v, th_v in bad[:5]], 'agents_compared': len(theirs)}}) return checks # ------------------------------------------------------------ INVOICE-LINE ATTRIBUTION (2026-07-28) # A SECOND agent source. Everything above this line attributes by BOOK — the customer's assigned # agent (res.partner.agent_ids) — over confirmed SALES ORDERS. This section attributes per INVOICE # LINE, from the OCA sale-commission module, via the semantic layer (topics invoice_lines / # commission_lines). The two disagree on purpose and answer different questions: # # book -> "whose customer is this / who owns the relationship" (order basis) # invoice -> "what was actually credited to an agent on the billing" + the ONLY source that can # say what is NOT allocated to an agent (invoice basis) # # ⚠ A NAME ON A COMMISSION LINE IS NOT NECESSARILY AN AGENT — `res.partner.agent` is the flag. # "Anna" and "Shantal Erlich" are internal SALESPEOPLE who carry commission lines; the `agent` # dim excludes them and `include_salespeople` folds them back in as a clearly-labelled variant. # See [[invoice-line-agent-commission]]. _ALLOC_LABEL = {'agent': 'Allocated to an agent', 'salesperson': 'Salesperson only', 'none': 'Not allocated'} def invoice_line_rollup(t=None, team_id=None, include_salespeople=False): """Per-name invoice-line revenue + the MECE allocation split, for a YTD window. Returns {'by_agent': [...], 'allocation': [...], 'total': float, 'allocated': float, 'unallocated': float, 'basis': str} — or {'error': msg} when the tenant store is not ready (this path is store-only; there is no live fallback that stays honest about the unallocated bucket). """ import harness.semantic as S t = t or P.today() yf, yt = P.ytd(t) dim = 'commission_name' if include_salespeople else 'agent' try: by = S.store_query('invoice_lines', ['invoiced_line_sales'], group_by=[dim], date_from=yf, date_to=yt, team_id=team_id, limit=200).get('rows') or [] alloc = S.store_query('invoice_lines', ['invoiced_line_sales'], group_by=['allocation'], date_from=yf, date_to=yt, team_id=team_id, limit=10).get('rows') or [] tot = (S.store_query('invoice_lines', ['invoiced_line_sales'], date_from=yf, date_to=yt, team_id=team_id).get('rows') or [{}])[0].get('invoiced_line_sales') or 0.0 except Exception as e: # store not ready / model error — say so, don't fake return {'error': str(e)} # store_query row shape: the DIM KEY carries the display NAME and `_id` the raw value # (allocation -> 'Salesperson only', allocation_id -> 'salesperson'). Reading `_name` # returns None for every row and silently renders the whole table as "no agent". rows = [{'agent': (r.get(dim) or '(no agent on the line)'), 'revenue': r.get('invoiced_line_sales') or 0.0} for r in by] rows.sort(key=lambda r: -r['revenue']) amap = {r.get('allocation_id') or 'none': (r.get('invoiced_line_sales') or 0.0) for r in alloc} allocated = amap.get('agent', 0.0) return { 'by_agent': rows, 'allocation': [{'bucket': _ALLOC_LABEL[k], 'revenue': amap.get(k, 0.0)} for k in ('agent', 'salesperson', 'none') if k in amap or True], 'total': tot, 'allocated': allocated, 'unallocated': tot - allocated, 'strict_none': amap.get('none', 0.0), 'salesperson_only': amap.get('salesperson', 0.0), 'window': (yf, yt), 'basis': ('invoice line · commission names incl. salespeople' if include_salespeople else 'invoice line · real agents only'), }