File size: 20,302 Bytes
3331871 | 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 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 | """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 `<dim>_id` the raw value
# (allocation -> 'Salesperson only', allocation_id -> 'salesperson'). Reading `<dim>_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'),
}
|