File size: 21,479 Bytes
c72f0a1 b2aa446 c72f0a1 b2aa446 c72f0a1 b2aa446 c72f0a1 b2aa446 c72f0a1 b2aa446 c72f0a1 b2aa446 c72f0a1 b2aa446 c72f0a1 b2aa446 c72f0a1 b2aa446 c72f0a1 b2aa446 c72f0a1 | 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 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 | """Customer Card observations from the tenant mirror and independent receivable checks."""
from __future__ import annotations
import datetime as dt
import json
from decimal import Decimal
from core import periods
class MirrorUnavailable(RuntimeError):
"""A required observation is missing from the serving mirror."""
def _cursor(cursor, rt):
if cursor is not None:
if rt is not None:
rt.assert_datastore_matches()
return cursor
from harness import runtime
return runtime.mirror_cursor(rt or runtime.get_runtime("royal-imports"))
def _columns(cursor, table, required=()):
try:
columns = {str(row[1]) for row in cursor.execute(f"PRAGMA table_info('{table}')").fetchall()}
except Exception as exc:
raise MirrorUnavailable(f"The {table} observation is unavailable.") from exc
missing = set(required) - columns
if missing:
raise MirrorUnavailable(f"The {table} observation lacks {', '.join(sorted(missing))}.")
return columns
def _rows(cursor, sql, params=()):
result = cursor.execute(sql, list(params))
names = [col[0] for col in result.description]
return [dict(zip(names, row)) for row in result.fetchall()]
def _money(value):
if value is None:
raise MirrorUnavailable("A required amount has not been observed.")
return float(Decimal(str(value)).quantize(Decimal("0.01")))
def _date(value):
day = periods.business_day(value)
return day.isoformat() if day else None
def _positive_id(value):
if isinstance(value, bool):
raise ValueError("Customer identifiers must be positive integers.")
parsed = int(value)
if parsed <= 0 or str(parsed) != str(value):
raise ValueError("Customer identifiers must be positive integers.")
return parsed
def _customer(cursor, customer_id, team_id=None):
columns = _columns(cursor, "res_partner", ("id", "name", "team_id"))
agent_column = "agent_ids" if "agent_ids" in columns else "agent_id"
if agent_column not in columns:
raise MirrorUnavailable("The customer's assigned agent has not been observed.")
rows = _rows(cursor, f"SELECT id, name, team_id, {agent_column} AS assigned_agents "
"FROM res_partner WHERE id = ?", (customer_id,))
if not rows:
raise KeyError(customer_id)
row = rows[0]
if str(row["name"] or "").strip().upper() == "GIFTWARE DEALS":
raise PermissionError("This customer is outside the wholesale customer scope.")
if team_id is not None:
from core import odoo
teams = odoo.active_team_ids()
if team_id not in teams:
raise ValueError("Select one of the tenant's sales teams.")
assigned_team = row["team_id"]
if assigned_team in teams:
admitted = assigned_team == team_id
else:
_columns(cursor, "sale_order", ("partner_id", "team_id", "state"))
admitted = bool(cursor.execute(
"SELECT 1 FROM sale_order WHERE partner_id = ? AND team_id = ? "
"AND state IN ('sale', 'done') LIMIT 1", [customer_id, team_id]).fetchone())
if not admitted:
raise PermissionError("This customer belongs to another business unit.")
return row
def _invoice_where():
return ("partner_id = ? AND state = 'posted' "
"AND move_type IN ('out_invoice', 'out_refund') "
"AND amount_residual_signed <> 0")
def _invoice_total(cursor, customer_id):
_columns(cursor, "account_move", ("partner_id", "state", "move_type", "amount_residual_signed"))
unknown = cursor.execute(
"SELECT COUNT(*) FROM account_move WHERE partner_id = ? AND state = 'posted' "
"AND move_type IN ('out_invoice', 'out_refund') AND amount_residual_signed IS NULL",
[customer_id]).fetchone()[0]
if unknown:
raise MirrorUnavailable("An invoice's open amount has not been observed.")
result = cursor.execute(
"SELECT COALESCE(SUM(amount_residual_signed), 0), COUNT(*) "
"FROM account_move WHERE " + _invoice_where(), [customer_id]).fetchone()
return _money(result[0]), int(result[1])
def _agent_ids(value):
if value in (None, False, ""):
return []
if isinstance(value, str):
try:
value = json.loads(value)
except ValueError as exc:
raise MirrorUnavailable("The assigned agent observation is invalid.") from exc
if not isinstance(value, (list, tuple)):
value = [value]
return list(dict.fromkeys(int(item) for item in value if item))
def card(customer_id, cursor=None, *, rt=None, team_id=None, invoice_limit=100,
invoice_offset=0, today=None, authorize=None, include_agents=True, user=None):
"""Return one customer's paged invoices, full balance, latest product prices and agent contacts.
authorize(section, rows) must admit every source row or raise before any card leaves the API.
"""
customer_id = _positive_id(customer_id)
if type(invoice_limit) is not int or invoice_limit < 1:
raise ValueError("Invoice page size must be a positive integer.")
if type(invoice_offset) is not int or invoice_offset < 0:
raise ValueError("Invoice offset must be a nonnegative integer.")
cursor = _cursor(cursor, rt)
customer = _customer(cursor, customer_id, team_id)
if authorize:
authorize("customer", [customer])
total, count = _invoice_total(cursor, customer_id)
_columns(cursor, "account_move", ("id", "name", "invoice_date", "invoice_date_due", "payment_state"))
if authorize:
identities = _rows(cursor, "SELECT id FROM account_move WHERE " + _invoice_where(), (customer_id,))
authorize("invoices", identities)
invoices = _rows(cursor,
"SELECT id, name AS number, invoice_date AS date, invoice_date_due AS dueDate, "
"amount_residual_signed AS openAmount, payment_state AS paymentState, move_type AS kind "
"FROM account_move WHERE " + _invoice_where()
+ " ORDER BY invoice_date_due NULLS LAST, invoice_date NULLS LAST, id LIMIT ? OFFSET ?",
(customer_id, invoice_limit, invoice_offset))
for row in invoices:
row["date"], row["dueDate"] = _date(row["date"]), _date(row["dueDate"])
row["openAmount"] = _money(row["openAmount"])
today = today or periods.today()
if isinstance(today, str):
today = dt.date.fromisoformat(today)
date_from, date_to = periods.ltm(today)
_columns(cursor, "sale_order", ("id", "partner_id", "team_id", "date_order", "state"))
_columns(cursor, "sale_order_line", ("id", "order_id", "order_partner_id", "product_id",
"product_name", "price_unit", "product_uom_name"))
_columns(cursor, "product_product", ("id", "type"))
from core import odoo
lo, hi = periods.business_day_bounds(date_from, date_to)
params = [customer_id, customer_id, lo, hi]
teams = odoo.active_team_ids()
team_where = " AND so.team_id = ?" if team_id is not None else (
" AND so.team_id IN (" + ','.join('?' for _ in teams) + ")" if teams else " AND FALSE")
if team_id is not None:
team_where = " AND so.team_id = ?"
params.append(team_id)
else:
from core import odoo
teams = odoo.active_team_ids()
team_where = f" AND so.team_id IN ({', '.join('?' * len(teams))})" if teams else " AND FALSE"
params.extend(teams)
lines = _rows(cursor,
"SELECT sol.id, sol.product_id AS productId, sol.product_name AS name, "
"sol.price_unit AS price, sol.product_uom_name AS unit, so.date_order AS lastOrderDate, "
"pp.type AS productType "
"FROM sale_order_line sol JOIN sale_order so ON so.id = sol.order_id "
"LEFT JOIN product_product pp ON pp.id = sol.product_id "
"WHERE so.partner_id = ? AND sol.order_partner_id = ? AND so.state IN ('sale', 'done') "
"AND sol.product_id IS NOT NULL AND CAST(so.date_order AS TIMESTAMP) >= ? "
"AND CAST(so.date_order AS TIMESTAMP) < ?"
+ team_where + " ORDER BY so.date_order DESC, sol.id DESC", params)
if any(line["productType"] in (None, "") for line in lines):
raise MirrorUnavailable("A purchased product's type has not been observed.")
lines = [line for line in lines if line["productType"] != "service"]
if authorize:
authorize("products", lines)
products, seen = [], set()
for line in lines:
if line["productId"] in seen:
continue
seen.add(line["productId"])
if line["price"] is None or line["unit"] in (None, ""):
raise MirrorUnavailable("The customer's latest price or unit has not been observed.")
products.append({"productId": int(line["productId"]), "name": line["name"],
"lastOrderDate": _date(line["lastOrderDate"]),
"price": float(line["price"]), "unit": line["unit"], "saleLineId": int(line["id"])})
agents = []
ids = _agent_ids(customer["assigned_agents"]) if include_agents else []
if ids:
_columns(cursor, "res_partner", ("phone", "email"))
agents = _rows(cursor, "SELECT id, name, phone, email FROM res_partner "
"WHERE id IN (SELECT unnest(?::BIGINT[])) ORDER BY id", (ids,))
if {row["id"] for row in agents} != set(ids):
raise MirrorUnavailable("An assigned agent is missing from the mirror.")
by_id = {row["id"]: row for row in agents}
agents = [by_id[aid] for aid in ids]
if authorize:
authorize("agents", agents)
for agent in agents:
agent["phone"] = agent["phone"] or None
agent["email"] = agent["email"] or None
if rt is not None or user is not None:
from core import personal_reads
personal_reads.record_rows("customer_data", [{"customer": customer["name"]}],
st=rt, user=user)
return {"customer": {"id": customer_id, "name": customer["name"]},
"businessUnit": team_id, "state": "ready", "total": total,
"invoices": {"rows": invoices, "total": total, "totalCount": count,
"offset": invoice_offset, "limit": invoice_limit,
"hasMore": invoice_offset + len(invoices) < count},
"products": products, "productWindow": {"from": date_from, "to": date_to},
"agent": agents[0] if agents else None, "agents": agents,
"source": {"observation": "tenant_mirror", "proof": "res.partner.credit",
"reconciliation": "not_checked"}}
def validate(team_id=None, *, customer_id=None, cursor=None, rt=None, source_client=None):
"""Tie serving balances to independently read live res.partner.credit without using mirror credit."""
cursor = _cursor(cursor, rt)
if customer_id is not None:
ids = [_positive_id(customer_id)]
else:
_columns(cursor, "account_move", ("partner_id", "state", "move_type"))
ids = [int(row[0]) for row in cursor.execute(
"SELECT DISTINCT partner_id FROM account_move WHERE partner_id IS NOT NULL "
"AND state = 'posted' AND move_type IN ('out_invoice', 'out_refund') ORDER BY partner_id"
).fetchall()]
observed = {}
for pid in ids:
if team_id is not None:
try:
_customer(cursor, pid, team_id)
except PermissionError:
if customer_id is not None:
raise
continue
observed[pid] = _invoice_total(cursor, pid)[0]
if not observed:
return [{"check": "Customer receivables observed", "a": 0, "b": 1, "gap": -1, "ok": False}]
if source_client is None:
from core import odoo
source_client = odoo.get_odoo()
oracle = source_client.search_read("res.partner",
[("id", "in", list(observed)), ("active", "in", [True, False])], ["id", "credit"], limit=None)
live = {int(row["id"]): _money(row["credit"]) for row in oracle}
return [{"check": f"Customer {pid} open total = independent Odoo receivable", "a": amount,
"b": live.get(pid), "gap": _money(Decimal(str(amount)) - Decimal(str(live[pid])))
if pid in live else None,
"ok": pid in live and Decimal(str(amount)) == Decimal(str(live[pid]))}
for pid, amount in observed.items()]
def fixture_cursor():
"""Build synthetic native DuckDB observations without opening a tenant store or Odoo."""
import duckdb
cursor = duckdb.connect(":memory:")
cursor.execute("CREATE TABLE res_partner (id BIGINT, name VARCHAR, team_id BIGINT, "
"agent_ids VARCHAR, phone VARCHAR, email VARCHAR)")
cursor.execute("INSERT INTO res_partner VALUES "
"(10, 'Fixture Royal', 6, '[90]', NULL, NULL), "
"(11, 'Fixture Fisch', 5, '[]', NULL, NULL), "
"(12, 'GIFTWARE DEALS', 6, '[]', NULL, NULL), "
"(90, 'Fixture Agent', NULL, '[]', '555-0100', 'agent@example.invalid')")
cursor.execute("CREATE TABLE account_move (id BIGINT, partner_id BIGINT, name VARCHAR, "
"invoice_date DATE, invoice_date_due DATE, amount_residual_signed DECIMAL(18,2), "
"payment_state VARCHAR, move_type VARCHAR, state VARCHAR, team_id BIGINT)")
cursor.execute("INSERT INTO account_move SELECT i, 10, 'INV-' || i, DATE '2026-10-01', "
"DATE '2026-11-01', 1, 'not_paid', 'out_invoice', 'posted', 1 FROM range(1, 502) r(i)")
cursor.execute("INSERT INTO account_move VALUES "
"(600,10,'CR-1','2026-10-01','2026-11-01',-20,'partial','out_refund','posted',1), "
"(601,11,'OTHER','2026-10-01','2026-11-01',900,'not_paid','out_invoice','posted',1), "
"(602,10,'DRAFT','2026-10-01','2026-11-01',800,'not_paid','out_invoice','draft',1), "
"(603,10,'PAID','2026-10-01','2026-11-01',0,'paid','out_invoice','posted',1), "
"(604,10,'BILL','2026-10-01','2026-11-01',700,'not_paid','in_invoice','posted',1)")
cursor.execute("CREATE TABLE sale_order (id BIGINT, partner_id BIGINT, team_id BIGINT, "
"date_order TIMESTAMP, state VARCHAR)")
cursor.execute("INSERT INTO sale_order VALUES "
"(1,10,6,'2026-09-01','sale'), (2,10,6,'2026-10-01','done'), "
"(3,11,5,'2026-10-02','sale'), (4,10,6,'2026-10-03','draft'), "
"(5,10,6,'2025-01-01','sale'), (6,12,6,'2026-10-04','sale'), "
"(7,10,5,'2026-10-05','sale'), (8,10,6,'2026-10-20','sale')")
cursor.execute("CREATE TABLE product_product (id BIGINT, type VARCHAR)")
cursor.execute("INSERT INTO product_product VALUES "
"(20,'product'), (21,'product'), (22,'product'), (23,'product'), (24,'service')")
cursor.execute("CREATE TABLE sale_order_line (id BIGINT, order_id BIGINT, order_partner_id BIGINT, "
"product_id BIGINT, product_name VARCHAR, price_unit DOUBLE, product_uom_name VARCHAR)")
cursor.execute("INSERT INTO sale_order_line VALUES "
"(1,1,10,20,'Fixture Product',7.5,'Pack'), (2,2,10,20,'Fixture Product',8.75,'Roll'), "
"(3,3,11,20,'Fixture Product',99,'Box'), (4,4,10,20,'Fixture Product',98,'Box'), "
"(5,5,10,21,'Old Product',6,'Each'), (6,6,12,20,'Fixture Product',97,'Box'), "
"(7,7,10,22,'Fisch Product',5,'Each'), (8,8,10,20,'Fixture Product',96,'Box'), "
"(9,2,11,23,'Mismatched Customer',95,'Each'), "
"(10,2,10,24,'Fixture Service',4,'Hour')")
return cursor
def selftest():
"""Prove invoice isolation, uncapped paging, latest own prices and an independent oracle offline."""
cursor = fixture_cursor()
try:
observed = card(10, cursor, team_id=6, today="2026-10-13", invoice_limit=1)
assert observed["total"] == 481, "total includes another customer's invoice"
assert observed["invoices"]["totalCount"] == 502
assert len(observed["invoices"]["rows"]) == 1 and observed["invoices"]["hasMore"]
tail = card(10, cursor, team_id=6, today="2026-10-13", invoice_offset=500)
assert len(tail["invoices"]["rows"]) == 2 and tail["total"] == 481
assert any(row["openAmount"] == -20 for row in tail["invoices"]["rows"])
assert all(row["productId"] != 24 for row in observed["products"]), "service entered the customer catalog"
assert observed["products"] == [{"productId": 20, "name": "Fixture Product",
"lastOrderDate": "2026-10-01", "price": 8.75, "unit": "Roll", "saleLineId": 2}]
cursor.execute("UPDATE product_product SET type = 'consu' WHERE id = 20")
consumable = card(10, cursor, team_id=6, today="2026-10-13")
assert consumable["products"] == observed["products"], "consumable left the customer catalog"
assert consumable["total"] == 481
cursor.execute("UPDATE product_product SET type = 'product' WHERE id = 20")
assert observed["agent"]["phone"] == "555-0100"
assert observed["agent"]["email"] == "agent@example.invalid"
for pid, team in ((10, 5), (12, 6)):
try:
card(pid, cursor, team_id=team, today="2026-10-13")
except PermissionError:
pass
else:
raise AssertionError("customer scope widened")
assert card(11, cursor, team_id=5, today="2026-10-13")["total"] == 900
assert card(11, cursor, team_id=5, today="2026-10-13")["agent"] is None
class Oracle:
def search_read(self, model, domain, fields, limit=None):
assert model == "res.partner" and fields == ["id", "credit"] and limit is None
assert domain[0] == ("id", "in", [10])
return [{"id": 10, "credit": 481}]
checks = validate(customer_id=10, cursor=cursor, source_client=Oracle())
assert len(checks) == 1 and checks[0]["ok"]
cursor.execute("UPDATE account_move SET amount_residual_signed = 2 WHERE id = 1")
assert validate(customer_id=10, cursor=cursor, source_client=Oracle())[0]["ok"] is False
cursor.execute("UPDATE account_move SET amount_residual_signed = 1.01 WHERE id = 1")
assert validate(customer_id=10, cursor=cursor, source_client=Oracle())[0]["ok"] is False
cursor.execute("UPDATE account_move SET amount_residual_signed = NULL WHERE id = 1")
try:
card(10, cursor, team_id=6, today="2026-10-13")
except MirrorUnavailable:
pass
else:
raise AssertionError("an unknown invoice amount became a partial total")
cursor.execute("UPDATE account_move SET amount_residual_signed = 1 WHERE id = 1")
cursor.execute("INSERT INTO res_partner VALUES "
"(91, 'Second Fixture Agent', NULL, '[]', '555-0101', 'second@example.invalid')")
cursor.execute("UPDATE res_partner SET agent_ids = '[91,90]' WHERE id = 10")
assert card(10, cursor, today="2026-10-13")["agent"]["id"] == 91
cursor.execute("UPDATE account_move SET amount_residual_signed = 0 WHERE partner_id = 11")
empty = card(11, cursor, today="2026-10-13")
assert empty["total"] == 0 and empty["invoices"]["totalCount"] == 0
for mutation in ("UPDATE product_product SET type = NULL WHERE id = 20",
"DELETE FROM product_product WHERE id = 20",
"ALTER TABLE product_product DROP COLUMN type",
"DROP TABLE product_product"):
cursor.execute(mutation)
try:
card(10, cursor, team_id=6, today="2026-10-13")
except MirrorUnavailable:
pass
else:
raise AssertionError("an unknown product type became a partial catalog")
print("PASS customer-card offline fixture: isolation, paging, prices, product types, agents, oracle drift")
finally:
cursor.close()
if __name__ == "__main__":
import argparse
parser = argparse.ArgumentParser(description=__doc__)
mode = parser.add_mutually_exclusive_group(required=True)
mode.add_argument("--selftest", action="store_true")
mode.add_argument("--validate", action="store_true", help="Read live Odoo credit independently.")
parser.add_argument("--mirror", help="Scratch native DuckDB observation, opened read-only.")
parser.add_argument("--customer-id", type=int)
parser.add_argument("--team-id", type=int, help="One of the tenant's sales team ids.")
args = parser.parse_args()
if args.selftest:
selftest()
else:
if not args.mirror or args.customer_id is None:
parser.error("--validate requires --mirror and --customer-id")
import duckdb
import sys
with duckdb.connect(args.mirror, read_only=True) as observation:
checks = validate(cursor=observation, customer_id=args.customer_id, team_id=args.team_id)
for check in checks:
print("ok" if check["ok"] else "FAIL", check["check"],
"observation=", check["a"], "oracle=", check["b"], "gap=", check["gap"])
sys.exit(0 if checks and all(check["ok"] for check in checks) else 1)
|