Spaces:
Running on CPU Upgrade
Running on CPU Upgrade
Download platform/modules/amazon_dashboard.py from fsanyoto/runloopable: direct link, hf CLI and curl.
- Browser
- Download file 44.7 kB
-
https://huggingface.co/spaces/fsanyoto/runloopable/resolve/main/platform/modules/amazon_dashboard.py
- Command line
-
hf download hf://spaces/fsanyoto/runloopable/platform/modules/amazon_dashboard.py
-
curl -L -o amazon_dashboard.py https://huggingface.co/spaces/fsanyoto/runloopable/resolve/main/platform/modules/amazon_dashboard.py
44.7 kB
| """Account figures from tenant mirrors, with explicit gaps in currency, marketplace and cost basis.""" | |
| from __future__ import annotations | |
| from dataclasses import dataclass | |
| from datetime import date, timedelta | |
| from decimal import Decimal, InvalidOperation | |
| from itertools import groupby | |
| from contextlib import contextmanager | |
| import heapq | |
| import math | |
| import re | |
| KIND = "amazon_dashboard" | |
| ADS_TABLES = tuple(f"amzn_ads_{kind}_campaigns" for kind in ("sp", "sb", "sd")) | |
| SOURCE_DATABASES = { | |
| "amzn_order_metrics": "ut_amzn_order_metrics", | |
| "amzn_order_items": "ut_amzn_order_items", | |
| "amzn_fba_customer_returns": "ut_amzn_fba_customer_returns", | |
| "amzn_fba_shipped_sales": "ut_amzn_fba_shipped_sales", | |
| "amzn_mfn_returns": "ut_amzn_mfn_returns", | |
| "amzn_settlements": "ut_amzn_settlements", | |
| "amzn_transactions": "ut_amzn_transactions", | |
| **{table: "ut_" + table for table in ADS_TABLES}, | |
| "product_product": "product_data", | |
| } | |
| CONNECTORS = ("amazon", "amazon_ads", "odoo") | |
| _IDENTIFIER = re.compile(r"^[a-z_][a-z0-9_]*$") | |
| _ZERO = Decimal(0) | |
| class Unavailable(ValueError): | |
| """A figure has no safe source basis.""" | |
| class ValidationFailed(RuntimeError): | |
| """A served figure did not pass its mirror reconciliation.""" | |
| def _number(value): | |
| try: | |
| result = Decimal(str(value)) | |
| except (InvalidOperation, ValueError, TypeError): | |
| raise Unavailable("A required mirror value is missing or invalid.") from None | |
| if not result.is_finite(): | |
| raise Unavailable("A required mirror value is not finite.") | |
| return result | |
| def _day(value): | |
| text = str(value or "")[:10] | |
| try: | |
| return date.fromisoformat(text) | |
| except ValueError: | |
| try: | |
| day, month, year = text.split(".") | |
| return date(int(year), int(month), int(day)) | |
| except (ValueError, TypeError): | |
| raise Unavailable("A required mirror date is missing or invalid.") from None | |
| def periods(start, end, compare="prior"): | |
| """Return inclusive current and reference windows without inventing a missing reference.""" | |
| start, end = date.fromisoformat(str(start)), date.fromisoformat(str(end)) | |
| if start > end: | |
| raise ValueError("Choose an ordered period.") | |
| if compare == "prior": | |
| span = end - start + timedelta(days=1) | |
| return start, end, start - span, start - timedelta(days=1) | |
| if compare == "yoy": | |
| def previous(day): | |
| try: | |
| return day.replace(year=day.year - 1) | |
| except ValueError: | |
| return day.replace(year=day.year - 1, day=28) | |
| return start, end, previous(start), previous(end) | |
| raise ValueError("Choose prior or yoy for comparison.") | |
| def _quote(name): | |
| if not _IDENTIFIER.fullmatch(name): | |
| raise ValueError("Unknown mirror identifier.") | |
| return '"' + name + '"' | |
| class MirrorReader: | |
| """Stream projected rows from an already admitted tenant cursor without opening a store.""" | |
| def __init__(self, cursor): | |
| self.cursor = cursor | |
| self._columns = {} | |
| def columns(self, table): | |
| if table not in SOURCE_DATABASES: | |
| raise ValueError("Unknown dashboard source.") | |
| if table not in self._columns: | |
| self.cursor.execute("SELECT column_name FROM information_schema.columns " | |
| "WHERE table_schema='main' AND table_name=?", [table]) | |
| names = set() | |
| while batch := self.cursor.fetchmany(256): | |
| names.update(row[0] for row in batch) | |
| self._columns[table] = names | |
| return self._columns[table] | |
| def rows(self, table, columns, start, end, date_column, *, order=None, filters=None): | |
| have = self.columns(table) | |
| missing = set(columns) - have | |
| if missing: | |
| raise Unavailable(f"{table} lacks {', '.join(sorted(missing))}.") | |
| projection = ", ".join(_quote(name) for name in columns) | |
| sql, params, clauses = f"SELECT {projection} FROM {_quote(table)}", [], [] | |
| if date_column: | |
| column = _quote(date_column) | |
| day = (f"coalesce(try_cast(left(cast({column} AS VARCHAR),10) AS DATE), " | |
| f"cast(try_strptime(left(cast({column} AS VARCHAR),10),'%d.%m.%Y') AS DATE))") | |
| clauses.append(f"({day} BETWEEN ? AND ? OR {day} IS NULL)") | |
| params = [start, end] | |
| for name, values in (filters or {}).items(): | |
| if name not in have: | |
| raise Unavailable(f"{table} lacks {name}.") | |
| values = tuple(values) | |
| if not values: | |
| return | |
| clauses.append(f"{_quote(name)} IN ({', '.join('?' for _ in values)})") | |
| params.extend(values) | |
| if clauses: | |
| sql += " WHERE " + " AND ".join(clauses) | |
| if order: | |
| sql += " ORDER BY " + _quote(order) | |
| self.cursor.execute(sql, params) | |
| while batch := self.cursor.fetchmany(1024): | |
| for row in batch: | |
| yield dict(zip(columns, row)) | |
| class FixtureReader: | |
| """Read supplied mirror rows without a runtime, database, file or write.""" | |
| def __init__(self, tables): | |
| self.tables = tables | |
| self.reads = [] | |
| def columns(self, table): | |
| return {key for row in self.tables.get(table, ()) for key in row} | |
| def rows(self, table, columns, start, end, date_column, *, order=None, filters=None): | |
| self.reads.append(table) | |
| missing = set(columns) - self.columns(table) | |
| if missing: | |
| raise Unavailable(f"{table} lacks {', '.join(sorted(missing))}.") | |
| rows = self.tables.get(table, ()) | |
| if order: | |
| rows = sorted(rows, key=lambda row: str(row.get(order) or "")) | |
| for row in rows: | |
| if any(row.get(name) not in values for name, values in (filters or {}).items()): | |
| continue | |
| if date_column and not start <= _day(row.get(date_column)) <= end: | |
| continue | |
| yield {key: row.get(key) for key in columns} | |
| class CostLink: | |
| """A trusted SKU and unit mapping with an explicit validity interval and cost currency.""" | |
| product_id: int | |
| units_per_sku: Decimal | |
| uom_id: int | |
| currency: str | |
| connection_id: str | |
| marketplace_id: str | |
| valid_from: date | |
| valid_to: date | |
| evidence: str = "" | |
| class Source: | |
| totals: dict | |
| days: dict | |
| scope: tuple | |
| rows: int | |
| def _source(reader, table, start, end, date_column, measures, currency_column=None, *, extra=(), visit=None): | |
| have = reader.columns(table) | |
| required = [date_column, "_connection_id", "marketplace_id", *measures.values(), *extra] | |
| if currency_column: | |
| required.append(currency_column) | |
| optional = [name for name in ("marketplace_id", "profile_id", "seller_id") if name in have] | |
| columns = list(dict.fromkeys(required + optional)) | |
| totals = {key: _ZERO for key in measures} | |
| days, scope, count = {}, None, 0 | |
| for row in reader.rows(table, columns, start, end, date_column): | |
| day = _day(row[date_column]).isoformat() | |
| owner = str(row.get("_connection_id") or "").strip() | |
| currency = str(row.get(currency_column) or "").strip() if currency_column else None | |
| if not owner or not row.get("marketplace_id") or (currency_column and not re.fullmatch(r"[A-Z]{3}", currency)): | |
| raise Unavailable(f"{table} has no proven connection, marketplace or currency scope.") | |
| row_scope = (owner, row.get("marketplace_id") or None, currency, row.get("profile_id") or None, | |
| row.get("seller_id") or None) | |
| if scope is not None and scope != row_scope: | |
| raise Unavailable(f"{table} mixes connection, marketplace, profile or currency scopes.") | |
| scope = row_scope | |
| if visit is not None and visit(row) is False: | |
| continue | |
| values = {key: _number(row[column]) for key, column in measures.items()} | |
| if "units" in values and (values["units"] < 0 or values["units"] != values["units"].to_integral_value()): | |
| raise Unavailable(f"{table} has no valid whole unit quantity.") | |
| bucket = days.setdefault(day, {key: _ZERO for key in measures}) | |
| for key, value in values.items(): | |
| totals[key] += value | |
| bucket[key] += value | |
| count += 1 | |
| if not count: | |
| raise Unavailable(f"{table} has no rows for this period.") | |
| return Source(totals, days, scope, count) | |
| def _same_market(*sources, same_connection=False): | |
| scopes = [source.scope for source in sources] | |
| if any(not scope[1] for scope in scopes): | |
| raise Unavailable("The mirrors do not establish a shared marketplace.") | |
| if len({scope[1] for scope in scopes}) != 1: | |
| raise Unavailable("The mirrors name different marketplaces.") | |
| currencies = {scope[2] for scope in scopes if scope[2]} | |
| if len(currencies) > 1: | |
| raise Unavailable("The mirrors name different currencies.") | |
| if len({scope[4] for scope in scopes if scope[4]}) > 1: | |
| raise Unavailable("The mirrors name different seller accounts.") | |
| if same_connection and len({scope[0] for scope in scopes}) != 1: | |
| raise Unavailable("The mirrors name different Amazon connections.") | |
| if not same_connection and len({scope[0] for scope in scopes}) > 1: | |
| if any(not scope[4] for scope in scopes) or len({scope[4] for scope in scopes}) != 1: | |
| raise Unavailable("The mirrors do not establish a shared seller account.") | |
| def _ratio(top, bottom): | |
| if bottom <= 0: | |
| raise Unavailable("The denominator is zero or unavailable.") | |
| return top / bottom * 100 | |
| def _amount(value): | |
| result = float(value) | |
| if not math.isfinite(result): | |
| raise Unavailable("A mirror amount exceeds the supported numeric range.") | |
| return round(result, 4) | |
| def _series(source, measure, label): | |
| return {"key": measure, "label": label, | |
| "points": [{"x": day, "y": _amount(values[measure])} for day, values in sorted(source.days.items())]} | |
| def _ads(reader, start, end): | |
| parts = [_source(reader, table, start, end, "date", {"spend": "cost", "ad_sales": "sales"}, | |
| "currency", extra=("profile_id",)) for table in ADS_TABLES] | |
| first = parts[0] | |
| if not first.scope[3] or any(part.scope != first.scope for part in parts): | |
| raise Unavailable("The campaign reports do not share one proven Ads profile and currency.") | |
| if any(len(part.days) != (end - start).days + 1 for part in parts): | |
| raise Unavailable("The campaign reports do not establish coverage for every day in this period.") | |
| if any(value < 0 for part in parts for values in part.days.values() for value in values.values()): | |
| raise Unavailable("A campaign report has a negative spend or sales basis.") | |
| days, totals = {}, {"spend": _ZERO, "ad_sales": _ZERO} | |
| for part in parts: | |
| for key, value in part.totals.items(): | |
| totals[key] += value | |
| for day, values in part.days.items(): | |
| bucket = days.setdefault(day, {"spend": _ZERO, "ad_sales": _ZERO}) | |
| for key, value in values.items(): | |
| bucket[key] += value | |
| return Source(totals, days, first.scope, sum(part.rows for part in parts)) | |
| def _mix(reader, start, end, sales): | |
| table = "amzn_order_items" | |
| have = reader.columns(table) | |
| columns = ["sku", "purchase_date", "quantity", "item_price", "currency", "_connection_id", "order_status"] | |
| columns.extend(name for name in ("marketplace_id", "seller_id") if name in have) | |
| total, best = _ZERO, [] | |
| rows = reader.rows(table, columns, start, end, "purchase_date", order="sku") | |
| for sku, group in groupby(rows, key=lambda row: row["sku"]): | |
| amount = _ZERO | |
| for row in group: | |
| if str(row["order_status"]).lower() in ("cancelled", "canceled", "pending"): | |
| continue | |
| scope = (row["_connection_id"], row.get("marketplace_id"), row["currency"], None, row.get("seller_id")) | |
| _same_market(sales, Source({}, {}, scope, 1), same_connection=True) | |
| if not sku: | |
| raise Unavailable("An order item has no SKU.") | |
| # Amazon item_price is the extended line price, never a unit price. | |
| amount += _number(row["item_price"]) | |
| total += amount | |
| heapq.heappush(best, (amount, str(sku))) | |
| if len(best) > 10: | |
| heapq.heappop(best) | |
| if abs(total - sales.totals["sales"]) > Decimal("0.01"): | |
| raise Unavailable("Order item sales do not reconcile to the order metrics period.") | |
| points = [{"x": sku, "y": _amount(amount)} for amount, sku in sorted(best, reverse=True)] | |
| other = total - sum((amount for amount, _sku in best), _ZERO) | |
| if other: | |
| points.append({"x": "Other", "y": _amount(other)}) | |
| return total, [{"key": "sales", "label": "Sales", "points": points}] | |
| def _profit(reader, start, end, sales, ads, cost_links): | |
| _same_market(sales, ads) | |
| if not cost_links: | |
| raise Unavailable("No proven Amazon SKU and Odoo unit mapping is available for this period.") | |
| costs = {} | |
| columns = ("id", "standard_price", "uom_id", "currency_code") | |
| product_ids = {link.product_id for link in cost_links.values() if isinstance(link, CostLink)} | |
| for row in reader.rows("product_product", columns, start, end, None, filters={"id": product_ids}): | |
| if row["id"] in costs: | |
| raise Unavailable("The Odoo cost mirror has duplicate product identities.") | |
| costs[row["id"]] = row | |
| cogs = _ZERO | |
| def transaction(row): | |
| nonlocal cogs | |
| if row["transaction_status"] != "RELEASED": | |
| raise Unavailable("The transaction mirror contains amounts that are not released.") | |
| kind = row["transaction_type"] | |
| if kind != "Shipment": | |
| raise Unavailable("A transaction has no proven shipped product cost basis.") | |
| link = cost_links.get(str(row["sku"])) | |
| if (not isinstance(link, CostLink) or not link.evidence.strip() | |
| or not (link.valid_from <= start and link.valid_to >= end)): | |
| raise Unavailable("A transaction has no proven SKU and unit mapping for this period.") | |
| if ((link.connection_id, link.marketplace_id, link.currency) != sales.scope[:3] | |
| or _number(link.units_per_sku) <= 0): | |
| raise Unavailable("The SKU cost mapping does not match this account scope.") | |
| cost = costs.get(link.product_id) | |
| if not cost or cost["uom_id"] != link.uom_id or cost["currency_code"] != link.currency: | |
| raise Unavailable("The Odoo cost mirror does not establish the mapped unit and currency.") | |
| quantity = _number(row["item_quantity"]) | |
| if quantity < 0 or quantity != quantity.to_integral_value(): | |
| raise Unavailable("A transaction quantity has no proven sign convention.") | |
| unit_cost = _number(cost["standard_price"]) | |
| if unit_cost < 0: | |
| raise Unavailable("The mapped Odoo product cost is negative.") | |
| cogs += quantity * _number(link.units_per_sku) * unit_cost | |
| return True | |
| # Each item contributes cost, but only position zero carries the transaction total. | |
| tx = _source(reader, "amzn_transactions", start, end, "posted_date", {}, "currency_code", | |
| extra=("sku", "item_quantity", "item_position", "total_amount", "transaction_type", "transaction_status"), | |
| visit=transaction) | |
| _same_market(sales, tx, same_connection=True) | |
| tx_total = _ZERO | |
| tx_columns = ("posted_date", "item_position", "total_amount") | |
| for row in reader.rows("amzn_transactions", tx_columns, start, end, "posted_date"): | |
| if _number(row["item_position"]) == 0: | |
| tx_total += _number(row["total_amount"]) | |
| booked_ads = _ZERO | |
| def settlement(row): | |
| nonlocal booked_ads | |
| if row["amount_type"] not in ("ItemPrice", "ItemFees", "Promotion", "ServiceFee"): | |
| raise Unavailable("A settlement line has no proven operating profit basis.") | |
| if row["amount_description"] == "Cost of Advertising": | |
| booked_ads += _number(row["amount"]) | |
| return True | |
| settled = _source(reader, "amzn_settlements", start, end, "posted_date", {"cash": "amount"}, "currency", | |
| extra=("amount_type", "amount_description"), visit=settlement) | |
| _same_market(sales, settled, same_connection=True) | |
| cash = settled.totals["cash"] | |
| if abs(cash - tx_total) > Decimal("0.01"): | |
| raise Unavailable("Posted settlement lines do not reconcile to released transaction totals.") | |
| net = cash - booked_ads - ads.totals["spend"] - cogs | |
| bridge = [("Sales", sales.totals["sales"]), ("Settlement adjustments", cash - booked_ads - sales.totals["sales"]), | |
| ("Ad spend", -ads.totals["spend"]), ("Product cost", -cogs), ("Net profit", net)] | |
| points = [{"x": label, "y": _amount(value), "total": label in ("Sales", "Net profit")} for label, value in bridge] | |
| return net, [{"key": "profit", "label": "Profit", "points": points}] | |
| def _figures(reader, start, end, cost_links): | |
| figures, sources = [], {} | |
| def load(name, fn): | |
| try: | |
| sources[name] = fn() | |
| except Unavailable as exc: | |
| sources[name] = exc | |
| def need(name): | |
| value = sources[name] | |
| if isinstance(value, Unavailable): | |
| raise value | |
| return value | |
| def add(identity, label, tables, fn, unit="currency"): | |
| figure = {"id": identity, "label": label, "value": None, "unit": unit, "series": [], | |
| "source_table": tables[0], "source_tables": list(tables), | |
| "source_databases": [SOURCE_DATABASES[table] for table in tables], | |
| "validated": False, "reason": None} | |
| try: | |
| value, series, basis = fn() | |
| figure.update(value=_amount(value), series=series, | |
| unit=basis.scope[2] if unit == "currency" else unit, | |
| scope={"connection_id": basis.scope[0], "marketplace_id": basis.scope[1], | |
| "currency": basis.scope[2], "profile_id": basis.scope[3], "seller_id": basis.scope[4]}) | |
| except Unavailable as exc: | |
| figure["reason"] = str(exc) | |
| figures.append(figure) | |
| def metrics(): | |
| intervals = set() | |
| def interval(row): | |
| pieces = str(row["interval"]).split("--") | |
| if len(pieces) != 2 or _day(pieces[1]) != _day(pieces[0]) + timedelta(days=1): | |
| raise Unavailable("Order metrics do not establish nonoverlapping daily intervals.") | |
| day = _day(pieces[0]).isoformat() | |
| if day in intervals: | |
| raise Unavailable("Order metrics contain overlapping daily intervals.") | |
| intervals.add(day) | |
| result = _source(reader, "amzn_order_metrics", start, end, "interval", | |
| {"sales": "total_sales_amount", "units": "unit_count"}, "total_sales_currency_code", visit=interval) | |
| if len(result.days) != (end - start).days + 1: | |
| raise Unavailable("Order metrics do not cover every day in this period.") | |
| if any(value < 0 for values in result.days.values() for value in values.values()): | |
| raise Unavailable("Order metrics contain a negative sales or units basis.") | |
| return result | |
| load("sales", metrics) | |
| load("ads", lambda: _ads(reader, start, end)) | |
| sales_tables = ("amzn_order_metrics",) | |
| add("sales", "Sales", sales_tables, lambda: (need("sales").totals["sales"], [], need("sales"))) | |
| def profit(): | |
| sales, ads = need("sales"), need("ads") | |
| value, series = _profit(reader, start, end, sales, ads, cost_links) | |
| return value, series, sales | |
| profit_tables = (*sales_tables, "amzn_settlements", "amzn_transactions", *ADS_TABLES, "product_product") | |
| load("profit", profit) | |
| def profit_value(series=False): | |
| value, points, basis = need("profit") | |
| return value, points if series else [], basis | |
| add("net_profit", "Net profit", profit_tables, profit_value) | |
| add("ad_spend", "Ad spend", ADS_TABLES, lambda: (need("ads").totals["spend"], [], need("ads"))) | |
| add("acos", "Ad cost share", ADS_TABLES, | |
| lambda: (_ratio(need("ads").totals["spend"], need("ads").totals["ad_sales"]), [], need("ads")), "percent") | |
| def tacos(): | |
| sales, ads = need("sales"), need("ads") | |
| _same_market(sales, ads) | |
| return _ratio(ads.totals["spend"], sales.totals["sales"]), [], sales | |
| add("tacos", "Total ad cost", (*sales_tables, *ADS_TABLES), tacos, "percent") | |
| add("units", "Units", sales_tables, lambda: (need("sales").totals["units"], [], need("sales")), "units") | |
| def returns(): | |
| returned = _source(reader, "amzn_fba_customer_returns", start, end, "return_date", {"units": "quantity"}) | |
| shipped = _source(reader, "amzn_fba_shipped_sales", start, end, "shipment_date", {"units": "quantity"}, "currency") | |
| def merchant_return(row): | |
| if not str(row["merchant_sku"] or "").strip(): | |
| raise Unavailable("amzn_mfn_returns has no merchant_sku for a returned item.") | |
| return True | |
| merchant_returns = _source(reader, "amzn_mfn_returns", start, end, "return_request_date", | |
| {"units": "return_quantity"}, "currency_code", | |
| extra=("merchant_sku",), visit=merchant_return) | |
| def merchant_shipment(row): | |
| channel = str(row["fulfillment_channel"] or "").strip().lower() | |
| if channel in ("afn", "amazon", "fba"): | |
| return False | |
| if channel not in ("mfn", "merchant"): | |
| raise Unavailable("amzn_order_items has no proven MFN or merchant fulfillment_channel.") | |
| if not str(row["sku"] or "").strip(): | |
| raise Unavailable("amzn_order_items has no SKU for an MFN shipment.") | |
| return True | |
| try: | |
| merchant_shipped = _source(reader, "amzn_order_items", start, end, "shipment_date", | |
| {"units": "quantity_shipped"}, "currency", | |
| extra=("fulfillment_channel", "sku"), visit=merchant_shipment) | |
| except Unavailable as exc: | |
| raise Unavailable(f"Account return rate needs MFN shipment evidence. {exc}") from None | |
| _same_market(returned, shipped, merchant_returns, merchant_shipped, same_connection=True) | |
| return (_ratio(returned.totals["units"] + merchant_returns.totals["units"], | |
| shipped.totals["units"] + merchant_shipped.totals["units"]), [], shipped) | |
| add("return_rate", "Return rate", ("amzn_fba_customer_returns", "amzn_fba_shipped_sales", | |
| "amzn_mfn_returns", "amzn_order_items"), returns, "percent") | |
| def trend(organic=False): | |
| sales, ads = need("sales"), need("ads") | |
| _same_market(sales, ads) | |
| if set(sales.days) != set(ads.days): | |
| raise Unavailable("Sales and campaign reports do not cover the same days.") | |
| if not organic: | |
| return sales.totals["sales"], [_series(sales, "sales", "Sales"), _series(ads, "ad_sales", "Ad sales")], sales | |
| points = [] | |
| for day in sorted(sales.days): | |
| amount = sales.days[day]["sales"] - ads.days[day]["ad_sales"] | |
| if amount < 0: | |
| raise Unavailable("Attributed ad sales exceed account sales on a reported day.") | |
| points.append({"x": day, "y": _amount(amount)}) | |
| return sales.totals["sales"], [{"key": "organic", "label": "Organic sales", "points": points}, | |
| _series(ads, "ad_sales", "Ad sales")], sales | |
| add("sales_trend", "Sales over time", (*sales_tables, *ADS_TABLES), trend) | |
| add("organic_ad_sales", "Sales sources", (*sales_tables, *ADS_TABLES), lambda: trend(True)) | |
| def mix(): | |
| sales = need("sales") | |
| value, series = _mix(reader, start, end, sales) | |
| return value, series, sales | |
| add("sales_mix", "Sales mix", (*sales_tables, "amzn_order_items"), mix) | |
| add("pnl_waterfall", "Profit and loss", profit_tables, lambda: profit_value(True)) | |
| return figures | |
| def _mirror_reference(reader, start, end, cost_links): | |
| """Reconcile raw mirror evidence without calling the display aggregators.""" | |
| scopes, cache = {}, {} | |
| def rows(table, date_column, fields, currency=None, *, order=None): | |
| columns = list(dict.fromkeys([date_column, "_connection_id", "marketplace_id", *fields] | |
| + ([currency] if currency else []) | |
| + [key for key in ("profile_id", "seller_id") if key in reader.columns(table)])) | |
| count, scope = 0, None | |
| for row in reader.rows(table, columns, start, end, date_column, order=order): | |
| owner = str(row.get("_connection_id") or "").strip() | |
| money = str(row.get(currency) or "").strip() if currency else None | |
| if not owner or not row.get("marketplace_id") or (currency and not re.fullmatch(r"[A-Z]{3}", money)): | |
| raise Unavailable(f"{table} has no proven connection, marketplace or currency scope.") | |
| identity = (owner, row["marketplace_id"], money, row.get("profile_id") or None, | |
| row.get("seller_id") or None) | |
| if scope is not None and scope != identity: | |
| raise Unavailable(f"{table} mixes connection, marketplace, profile or currency scopes.") | |
| scope = identity | |
| scopes[table] = identity | |
| count += 1 | |
| yield row, _day(row[date_column]).isoformat() | |
| if not count: | |
| raise Unavailable(f"{table} has no rows for this period.") | |
| def same_market(*tables, same_connection=False): | |
| _same_market(*(Source({}, {}, scopes[table], 1) for table in tables), same_connection=same_connection) | |
| def quantity(value): | |
| number = _number(value) | |
| if number < 0 or number != number.to_integral_value(): | |
| raise Unavailable("A mirror quantity has no valid whole unit basis.") | |
| return number | |
| def rounded(value): | |
| number = float(value) | |
| if not math.isfinite(number): | |
| raise Unavailable("A mirror amount exceeds the supported numeric range.") | |
| return round(number, 4) | |
| def percent(numerator, denominator): | |
| if denominator <= 0: | |
| raise Unavailable("The denominator is zero or unavailable.") | |
| return 100 * numerator / denominator | |
| def metrics(): | |
| days, sales, units = {}, _ZERO, _ZERO | |
| for row, day in rows("amzn_order_metrics", "interval", ("total_sales_amount", "unit_count"), | |
| "total_sales_currency_code"): | |
| interval = str(row["interval"]).split("--") | |
| if len(interval) != 2 or _day(interval[1]) - _day(interval[0]) != timedelta(days=1): | |
| raise Unavailable("Order metrics do not establish nonoverlapping daily intervals.") | |
| if day in days: | |
| raise Unavailable("Order metrics contain overlapping daily intervals.") | |
| amount, count = _number(row["total_sales_amount"]), quantity(row["unit_count"]) | |
| if amount < 0: | |
| raise Unavailable("Order metrics contain a negative sales or units basis.") | |
| days[day] = amount | |
| sales += amount | |
| units += count | |
| if len(days) != (end - start).days + 1: | |
| raise Unavailable("Order metrics do not cover every day in this period.") | |
| return sales, units, days | |
| def ads(): | |
| days, spend, attributed = {}, _ZERO, _ZERO | |
| for table in ADS_TABLES: | |
| daily = {} | |
| for row, day in rows(table, "date", ("cost", "sales", "profile_id"), "currency"): | |
| cost, sales = _number(row["cost"]), _number(row["sales"]) | |
| bucket = daily.setdefault(day, [_ZERO, _ZERO]) | |
| bucket[0] += cost | |
| bucket[1] += sales | |
| spend += cost | |
| attributed += sales | |
| days[day] = days.get(day, _ZERO) + sales | |
| if len(daily) != (end - start).days + 1: | |
| raise Unavailable("The campaign reports do not establish coverage for every day in this period.") | |
| if any(amount < 0 for amounts in daily.values() for amount in amounts): | |
| raise Unavailable("A campaign report has a negative spend or sales basis.") | |
| if not scopes[ADS_TABLES[0]][3] or len({scopes[table] for table in ADS_TABLES}) != 1: | |
| raise Unavailable("The campaign reports do not share one proven Ads profile and currency.") | |
| return spend, attributed, days | |
| def get(name): | |
| if name not in cache: | |
| try: | |
| cache[name] = {"sales": metrics, "ads": ads, "profit": profit}[name]() | |
| except Unavailable as exc: | |
| cache[name] = exc | |
| if isinstance(cache[name], Unavailable): | |
| raise cache[name] | |
| return cache[name] | |
| def profit(): | |
| sales, _, _ = get("sales") | |
| spend, _, _ = get("ads") | |
| same_market("amzn_order_metrics", ADS_TABLES[0]) | |
| if not cost_links: | |
| raise Unavailable("No proven Amazon SKU and Odoo unit mapping is available for this period.") | |
| products = {} | |
| product_ids = {link.product_id for link in cost_links.values() if isinstance(link, CostLink)} | |
| for row in reader.rows("product_product", ("id", "standard_price", "uom_id", "currency_code"), | |
| start, end, None, filters={"id": product_ids}): | |
| if row["id"] in products: | |
| raise Unavailable("The Odoo cost mirror has duplicate product identities.") | |
| products[row["id"]] = row | |
| cogs, released = _ZERO, _ZERO | |
| fields = ("sku", "item_quantity", "item_position", "total_amount", "transaction_type", "transaction_status") | |
| for row, _ in rows("amzn_transactions", "posted_date", fields, "currency_code"): | |
| if row["transaction_status"] != "RELEASED": | |
| raise Unavailable("The transaction mirror contains amounts that are not released.") | |
| if row["transaction_type"] != "Shipment": | |
| raise Unavailable("A transaction has no proven shipped product cost basis.") | |
| link = cost_links.get(str(row["sku"])) | |
| if (not isinstance(link, CostLink) or not link.evidence.strip() | |
| or link.valid_from > start or link.valid_to < end): | |
| raise Unavailable("A transaction has no proven SKU and unit mapping for this period.") | |
| if ((link.connection_id, link.marketplace_id, link.currency) != scopes["amzn_order_metrics"][:3] | |
| or _number(link.units_per_sku) <= 0): | |
| raise Unavailable("The SKU cost mapping does not match this account scope.") | |
| product = products.get(link.product_id) | |
| if not product or product["uom_id"] != link.uom_id or product["currency_code"] != link.currency: | |
| raise Unavailable("The Odoo cost mirror does not establish the mapped unit and currency.") | |
| cost = _number(product["standard_price"]) | |
| if cost < 0: | |
| raise Unavailable("The mapped Odoo product cost is negative.") | |
| cogs += (quantity(row["item_quantity"]) * _number(link.units_per_sku)) * cost | |
| if _number(row["item_position"]) == 0: | |
| released += _number(row["total_amount"]) | |
| same_market("amzn_order_metrics", "amzn_transactions", same_connection=True) | |
| cash, advertising = _ZERO, _ZERO | |
| for row, _ in rows("amzn_settlements", "posted_date", ("amount", "amount_type", "amount_description"), "currency"): | |
| if row["amount_type"] not in ("ItemPrice", "ItemFees", "Promotion", "ServiceFee"): | |
| raise Unavailable("A settlement line has no proven operating profit basis.") | |
| amount = _number(row["amount"]) | |
| cash += amount | |
| if row["amount_description"] == "Cost of Advertising": | |
| advertising += amount | |
| same_market("amzn_order_metrics", "amzn_settlements", same_connection=True) | |
| if abs(released - cash) > Decimal("0.01"): | |
| raise Unavailable("Posted settlement lines do not reconcile to released transaction totals.") | |
| receipts = cash - advertising | |
| net = receipts - (cogs + spend) | |
| points = [{"x": label, "y": rounded(amount), "total": total} for label, amount, total in ( | |
| ("Sales", sales, True), ("Settlement adjustments", receipts - sales, False), | |
| ("Ad spend", -spend, False), ("Product cost", -cogs, False), ("Net profit", net, True))] | |
| return net, [{"key": "profit", "label": "Profit", "points": points}] | |
| def returns(): | |
| returned, shipped = _ZERO, _ZERO | |
| for row, _ in rows("amzn_fba_customer_returns", "return_date", ("quantity",)): | |
| returned += quantity(row["quantity"]) | |
| for row, _ in rows("amzn_fba_shipped_sales", "shipment_date", ("quantity",), "currency"): | |
| shipped += quantity(row["quantity"]) | |
| for row, _ in rows("amzn_mfn_returns", "return_request_date", ("return_quantity", "merchant_sku"), "currency_code"): | |
| if not str(row["merchant_sku"] or "").strip(): | |
| raise Unavailable("amzn_mfn_returns has no merchant_sku for a returned item.") | |
| returned += quantity(row["return_quantity"]) | |
| count = 0 | |
| try: | |
| fields = ("quantity_shipped", "fulfillment_channel", "sku") | |
| for row, _ in rows("amzn_order_items", "shipment_date", fields, "currency"): | |
| channel = str(row["fulfillment_channel"] or "").strip().lower() | |
| if channel in ("afn", "amazon", "fba"): | |
| continue | |
| if channel not in ("mfn", "merchant"): | |
| raise Unavailable("amzn_order_items has no proven MFN or merchant fulfillment_channel.") | |
| if not str(row["sku"] or "").strip(): | |
| raise Unavailable("amzn_order_items has no SKU for an MFN shipment.") | |
| shipped += quantity(row["quantity_shipped"]) | |
| count += 1 | |
| if not count: | |
| raise Unavailable("amzn_order_items has no MFN shipment rows for this period.") | |
| except Unavailable as exc: | |
| raise Unavailable(f"Account return rate needs MFN shipment evidence. {exc}") from None | |
| same_market("amzn_fba_customer_returns", "amzn_fba_shipped_sales", "amzn_mfn_returns", | |
| "amzn_order_items", same_connection=True) | |
| return percent(returned, shipped), [] | |
| def trend(organic=False): | |
| total, _, sales_days = get("sales") | |
| _, _, ad_days = get("ads") | |
| same_market("amzn_order_metrics", ADS_TABLES[0]) | |
| if sales_days.keys() != ad_days.keys(): | |
| raise Unavailable("Sales and campaign reports do not cover the same days.") | |
| points, attributed = [], [] | |
| for day in sorted(sales_days): | |
| amount = sales_days[day] - ad_days[day] if organic else sales_days[day] | |
| if organic and amount < 0: | |
| raise Unavailable("Attributed ad sales exceed account sales on a reported day.") | |
| points.append({"x": day, "y": rounded(amount)}) | |
| attributed.append({"x": day, "y": rounded(ad_days[day])}) | |
| return total, [{"key": "organic" if organic else "sales", | |
| "label": "Organic sales" if organic else "Sales", "points": points}, | |
| {"key": "ad_sales", "label": "Ad sales", "points": attributed}] | |
| def mix(): | |
| sales = get("sales")[0] | |
| total, leaders = _ZERO, [] | |
| items = rows("amzn_order_items", "purchase_date", ("sku", "order_status", "item_price", "quantity"), | |
| "currency", order="sku") | |
| for sku, group in groupby(items, key=lambda item: item[0]["sku"]): | |
| amount = _ZERO | |
| for row, _ in group: | |
| if str(row["order_status"]).lower() in ("cancelled", "canceled", "pending"): | |
| continue | |
| same_market("amzn_order_metrics", "amzn_order_items", same_connection=True) | |
| if not sku: | |
| raise Unavailable("An order item has no SKU.") | |
| amount += _number(row["item_price"]) | |
| total += amount | |
| leaders.append((amount, str(sku))) | |
| leaders.sort(reverse=True) | |
| del leaders[10:] | |
| if abs(total - sales) > Decimal("0.01"): | |
| raise Unavailable("Order item sales do not reconcile to the order metrics period.") | |
| points = [{"x": sku, "y": rounded(amount)} for amount, sku in leaders] | |
| remainder = total - sum((amount for amount, _ in leaders), _ZERO) | |
| if remainder: | |
| points.append({"x": "Other", "y": rounded(remainder)}) | |
| return total, [{"key": "sales", "label": "Sales", "points": points}] | |
| def total_ad_cost(): | |
| total = get("sales")[0] | |
| spend = get("ads")[0] | |
| same_market("amzn_order_metrics", ADS_TABLES[0]) | |
| return percent(spend, total), [] | |
| sales_tables = ("amzn_order_metrics",) | |
| profit_tables = (*sales_tables, "amzn_settlements", "amzn_transactions", *ADS_TABLES, "product_product") | |
| references = [] | |
| specs = ( | |
| ("sales", sales_tables, "currency", lambda: (get("sales")[0], [])), | |
| ("net_profit", profit_tables, "currency", lambda: (get("profit")[0], [])), | |
| ("ad_spend", ADS_TABLES, "currency", lambda: (get("ads")[0], [])), | |
| ("acos", ADS_TABLES, "percent", lambda: (percent(*get("ads")[:2]), [])), | |
| ("tacos", (*sales_tables, *ADS_TABLES), "percent", total_ad_cost), | |
| ("units", sales_tables, "units", lambda: (get("sales")[1], [])), | |
| ("return_rate", ("amzn_fba_customer_returns", "amzn_fba_shipped_sales", "amzn_mfn_returns", | |
| "amzn_order_items"), "percent", returns), | |
| ("sales_trend", (*sales_tables, *ADS_TABLES), "currency", trend), | |
| ("organic_ad_sales", (*sales_tables, *ADS_TABLES), "currency", lambda: trend(True)), | |
| ("sales_mix", (*sales_tables, "amzn_order_items"), "currency", mix), | |
| ("pnl_waterfall", profit_tables, "currency", lambda: get("profit")), | |
| ) | |
| for identity, tables, unit, calculate in specs: | |
| reference = {"id": identity, "value": None, "unit": unit, "series": [], "reason": None, | |
| "source_table": tables[0], "source_tables": list(tables), | |
| "source_databases": [SOURCE_DATABASES[table] for table in tables]} | |
| try: | |
| value, series = calculate() | |
| scope = scopes["amzn_fba_shipped_sales" if identity == "return_rate" else tables[0]] | |
| reference.update(value=rounded(value), series=series, unit=scope[2] if unit == "currency" else unit, | |
| scope=dict(zip(("connection_id", "marketplace_id", "currency", "profile_id", "seller_id"), scope))) | |
| except Unavailable as exc: | |
| reference["reason"] = str(exc) | |
| references.append(reference) | |
| return references | |
| class Validation: | |
| ok: bool | |
| reason: str | None | |
| def validate(figure, reference): | |
| """Reject discrepancies against independent raw mirror totals and every plotted point.""" | |
| keys = ("id", "value", "unit", "series", "scope", "source_table", "source_tables", "source_databases") | |
| if (any(figure.get(key) != reference.get(key) for key in keys) | |
| or bool(figure.get("reason")) != bool(reference.get("reason"))): | |
| raise ValidationFailed("The figure does not reconcile to its mirror tables.") | |
| return Validation(figure["value"] is not None and not figure["reason"], figure["reason"]) | |
| def dashboard_from_reader(start, end, *, reader, compare="prior", cost_links=None): | |
| """Return the C6 figure contract after current and reference figures pass mirror validation.""" | |
| start, end, prior_start, prior_end = periods(start, end, compare) | |
| figures = _figures(reader, start, end, cost_links) | |
| prior = _figures(reader, prior_start, prior_end, cost_links) | |
| audit = _mirror_reference(reader, start, end, cost_links) | |
| prior_audit = _mirror_reference(reader, prior_start, prior_end, cost_links) | |
| identities = [figure["id"] for figure in figures] | |
| if any([figure["id"] for figure in group] != identities for group in (prior, audit, prior_audit)): | |
| raise ValidationFailed("Current and reference validation must cover every figure.") | |
| for figure, before, expected, expected_before in zip(figures, prior, audit, prior_audit): | |
| proof, previous_proof = validate(figure, expected), validate(before, expected_before) | |
| if not isinstance(proof, Validation) or not isinstance(previous_proof, Validation): | |
| raise ValidationFailed("Every figure must call validate before it can be served.") | |
| if not proof.ok and figure["value"] is not None: | |
| raise ValidationFailed(proof.reason) | |
| if not previous_proof.ok and before["value"] is not None: | |
| raise ValidationFailed(previous_proof.reason) | |
| figure["validated"], figure["reason"] = proof.ok, proof.reason | |
| compatible = (figure.get("scope") == before.get("scope") and figure["unit"] == before["unit"]) | |
| no_prior = not previous_proof.ok or not compatible | |
| previous = before["value"] if not no_prior else None | |
| absolute = round(figure["value"] - previous, 4) if proof.ok and previous is not None else None | |
| percent = round(absolute / abs(previous) * 100, 4) if absolute is not None and previous else None | |
| figure["noPrior"] = no_prior | |
| figure["change"] = {"previous": previous, "absolute": absolute, "percent": percent, | |
| "reason": ("No prior data. " + (previous_proof.reason or "The prior scope differs.")) | |
| if no_prior else ("The prior value is zero." if previous == 0 else None)} | |
| return {"kind": KIND, "start": start.isoformat(), "end": end.isoformat(), "compare": compare, | |
| "prior_start": prior_start.isoformat(), "prior_end": prior_end.isoformat(), "figures": figures, | |
| "basis": "Account return rate compares FBA plus merchant fulfilled units returned with both channels' " | |
| "shipped units within the same period, using return dates and shipment dates. " | |
| "Net profit reconciles posted operating settlements and released transactions, " | |
| "replaces booked advertising with campaign spend and subtracts mapped product cost."} | |
| def _tenant_reader(tenant, st, allowed_sources): | |
| """Open only the admitted tenant mirror and withhold unproven product costs.""" | |
| from harness import runtime as tenant_runtime | |
| if not tenant or getattr(st, "key", None) != tenant: | |
| raise Unavailable("The dashboard belongs to another workspace.") | |
| st.assert_datastore_matches() | |
| allowed = frozenset(allowed_sources or ()) | |
| class AdmittedReader(MirrorReader): | |
| def columns(self, table): | |
| if table == "product_product" or SOURCE_DATABASES.get(table) not in allowed: | |
| raise Unavailable("This source is unavailable with your access.") | |
| return super().columns(table) | |
| cursor = tenant_runtime.mirror_cursor(st, require_ready=False) | |
| try: | |
| cursor.execute("BEGIN TRANSACTION") | |
| yield AdmittedReader(cursor) | |
| finally: | |
| cursor.close() | |
| def dashboard(tenant, *, start, end, st, compare="prior", allowed_sources=(), cost_links=None): | |
| """Serve current and comparison figures through the tenant-owned admitted reader.""" | |
| with _tenant_reader(tenant, st, allowed_sources) as reader: | |
| return dashboard_from_reader(start, end, reader=reader, compare=compare, cost_links=None) | |
| def validate_figure(tenant, figure, *, start, end, st, compare="prior", allowed_sources=(), cost_links=None): | |
| """Recheck the full current and comparison payload against independent mirror evidence.""" | |
| identity = figure.get("id") | |
| with _tenant_reader(tenant, st, allowed_sources) as reader: | |
| expected = dashboard_from_reader(start, end, reader=reader, compare=compare, cost_links=None) | |
| matches = [row for row in expected["figures"] if row["id"] == identity] | |
| ok = len(matches) == 1 and matches[0].get("validated") is True and figure == matches[0] | |
| return {"ok": ok, "id": identity, "reason": None if ok else "The figure does not tie to its source."} | |