Download platform/modules/customer_card.py from fsanyoto/loopable: direct link, hf CLI and curl.
- Browser
- Download file 21.5 kB
-
https://huggingface.co/spaces/fsanyoto/loopable/resolve/main/platform/modules/customer_card.py
- Command line
-
hf download hf://spaces/fsanyoto/loopable/platform/modules/customer_card.py
-
curl -L -o customer_card.py https://huggingface.co/spaces/fsanyoto/loopable/resolve/main/platform/modules/customer_card.py
21.5 kB
| """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) | |