loopable / platform /modules /agent.py
fsanyoto's picture
Deploy AIOS web (React glide grid + FastAPI slice)
3331871 verified
Raw History Blame
20.3 kB
"""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'),
}