Download platform/modules/amazon_profit_account.py from fsanyoto/loopable: direct link, hf CLI and curl.
- Browser
- Download file 46 kB
-
https://huggingface.co/spaces/fsanyoto/loopable/resolve/main/platform/modules/amazon_profit_account.py
- Command line
-
hf download hf://spaces/fsanyoto/loopable/platform/modules/amazon_profit_account.py
-
curl -L -o amazon_profit_account.py https://huggingface.co/spaces/fsanyoto/loopable/resolve/main/platform/modules/amazon_profit_account.py
46 kB
| """Read-only Amazon account figures from mirror aggregates and period-matched receipt costs.""" | |
| from __future__ import annotations | |
| import calendar | |
| import datetime as dt | |
| import math | |
| from decimal import Decimal, InvalidOperation, ROUND_HALF_UP | |
| from modules import amazon_profit as profit | |
| ECONOMICS = profit.ECONOMICS | |
| ADS_TABLES = tuple(f"amzn_ads_{kind}_advertised_products" for kind in ("sp", "sb", "sd")) | |
| ACCOUNT_FIELDS = frozenset(("sku", "asin", "period", "units_sold", "net_sales", "fees", | |
| "sponsored_ads", "net_proceeds", "cogs_total", "net_profit", "cost_source")) | |
| WHAT_IF_FIELDS = ACCOUNT_FIELDS | {"cogs_per_unit", "margin", "odoo_product", "units_per_sku"} | |
| ORDERED = "sales_ordered_product_sales_amount" | |
| REFUNDS = "sales_refunded_product_sales_amount" | |
| def _cents(value): | |
| if value is None or value == "": | |
| return None | |
| try: | |
| number = Decimal(str(value)) | |
| return int((number * 100).quantize(Decimal("1"), rounding=ROUND_HALF_UP)) if number.is_finite() else None | |
| except (InvalidOperation, ValueError, TypeError): | |
| return None | |
| def _metric(cents, sources, *, reason=None, no_prior=False, validated=True): | |
| return {"value": None if cents is None else cents / 100, "validated": validated and cents is not None, | |
| "reason": reason, "noPrior": bool(no_prior), "sourceTables": list(sources)} | |
| def _bounds(start, end, key): | |
| return {"key": key, "start": start.isoformat(), "end": end.isoformat(), | |
| "days": max(0, (end - start).days + 1)} | |
| def _year_before(day): | |
| return day.replace(year=day.year - 1, day=min(day.day, calendar.monthrange(day.year - 1, day.month)[1])) | |
| def _comparisons(bounds): | |
| start, end = (dt.date.fromisoformat(bounds[k]) for k in ("start", "end")) | |
| prior_end = start - dt.timedelta(days=1) | |
| return {"prior": _bounds(start - dt.timedelta(days=bounds["days"]), prior_end, "prior"), | |
| "yearAgo": _bounds(_year_before(start), _year_before(end), "yearAgo")} | |
| def stored_skus(runtime, *, fields=ACCOUNT_FIELDS): | |
| """Stream only this tenant's SKU table through the store's paged object reader.""" | |
| import core.user_tables as ut | |
| backend = runtime._document_store() if callable(getattr(runtime, "_document_store", None)) else runtime | |
| reader = getattr(backend, "iter_json_items", None) | |
| if not callable(reader): | |
| raise profit.SourceUnavailable("Amazon SKU cost lookup requires the paged store reader.") | |
| name = runtime.store_key(ut.STORE_KEY) if callable(getattr(runtime, "store_key", None)) else ut.STORE_KEY | |
| result = {} | |
| for batch in reader(name, (profit.TABLE_KEY, "rows"), batch_size=512): | |
| for _rid, row in batch: | |
| sku = str(row.get("sku") or "").strip() | |
| if not sku: | |
| continue | |
| if sku in result: | |
| raise profit.SourceUnavailable("Amazon SKU cost lookup has duplicate seller SKUs. Refresh the table.") | |
| result[sku] = {key: row.get(key) for key in fields} | |
| return result | |
| def _model_number(value, name, *, fraction=False, whole=False): | |
| try: | |
| number = Decimal(str(value)) | |
| except (InvalidOperation, ValueError, TypeError): | |
| raise ValueError(f"{name} must be a finite nonnegative number.") from None | |
| if not number.is_finite() or number < 0 or not math.isfinite(float(number)): | |
| raise ValueError(f"{name} must be a finite nonnegative number.") | |
| if fraction and number > 1: | |
| raise ValueError("TACoS must be a fraction from 0 to 1. For 10%, use 0.10.") | |
| if whole and number != number.to_integral_value(): | |
| raise ValueError("Monthly units must be a whole nonnegative number.") | |
| return number | |
| def what_if(sku, price=None, landed_cost=None, tacos=None, units=None, *, con, stored, | |
| settings, period=None, today=None): | |
| """Model a source-backed SKU with observed signed fees and no stored-state changes.""" | |
| today = today or profit._today() | |
| configured = profit.period_bounds(settings.get("period"), today) | |
| recorded = settings.get("lastPeriod") | |
| snapshot_missing = not isinstance(recorded, dict) or not all(recorded.get(key) for key in ("key", "start", "end", "label")) | |
| saved = configured if snapshot_missing else {key: recorded[key] for key in ("key", "start", "end", "label")} | |
| selected = profit.period_bounds(period or configured["key"], today) | |
| supplied = {name: _model_number(value, name, fraction=name == "tacos", whole=name == "units") | |
| for name, value in (("price", price), ("landed_cost", landed_cost), ("tacos", tacos), ("units", units)) | |
| if value is not None} | |
| sku = str(sku or "").strip() | |
| out = {"sku": sku, "label": "Model", "available": False, | |
| "baselinePeriod": saved, "selectedPeriod": selected} | |
| def unavailable(reason): | |
| return {**out, "reason": reason} | |
| row = stored.get(sku) | |
| if not row: | |
| return unavailable("This SKU is not in Command Center. Refresh the table first.") | |
| if not profit.links({"baseline": row}).get(sku, {}).get("code"): | |
| return unavailable("Link this SKU to an Odoo product first.") | |
| if snapshot_missing and configured["key"] in profit.PERIOD_DAYS: | |
| return unavailable("Receipt cost snapshot dates unavailable. Refresh Command Center for this period.") | |
| if (saved["start"], saved["end"]) != (selected["start"], selected["end"]): | |
| return unavailable("Receipt cost unavailable for selected window. Refresh Command Center for this period.") | |
| problem = _ready(con, ECONOMICS) | |
| if problem: | |
| return unavailable(problem) | |
| cols = profit._require(con, ECONOMICS, profit._ECONOMICS_REQUIRED) | |
| ads_cols = profit.ads_columns(cols) | |
| if not ads_cols: | |
| return unavailable("Economics ad spend totals unavailable. Resync Amazon economics.") | |
| required = ("sales_net_units_sold", "sales_net_product_sales_amount", "fees_total_amount") | |
| invalid = con.execute(f'SELECT count(*) FROM {ECONOMICS} WHERE msku = ? AND ' | |
| 'left(CAST(start_date AS VARCHAR),10) BETWEEN ? AND ? AND (' | |
| + " OR ".join(f'"{c}" IS NULL OR NOT isfinite("{c}")' for c in required) | |
| + "".join(f' OR ("{c}" IS NOT NULL AND NOT isfinite("{c}"))' for c in ads_cols) | |
| + " OR (" + " AND ".join(f'"{c}" IS NULL' for c in ads_cols) + ")" | |
| + ")", [sku, selected["start"], selected["end"]]).fetchone()[0] | |
| if invalid: | |
| return unavailable("Economics amounts unavailable. Resync Amazon economics.") | |
| item = next((item for item in profit.economics_by_sku(con, selected["start"], selected["end"]) | |
| if item["sku"] == sku), None) | |
| if item is None: | |
| return unavailable("No economics source rows for this SKU and period.") | |
| currency = profit.economics_currency(con) | |
| if not isinstance(currency, str) or len(currency) != 3 or not currency.isalpha(): | |
| return unavailable("Source currency unavailable. Resync Amazon economics.") | |
| cost, reason = _cost(item, row, selected, settings, today) | |
| if reason: | |
| return unavailable(reason) | |
| try: | |
| sold = _model_number(item["units"], "Source units", whole=True) | |
| sales = _model_number(item["net_sales"], "Net sales") | |
| spend = _model_number(item["ads"], "Ad spend") | |
| displayed_cost = _model_number(row.get("cogs_per_unit"), "Stored unit cost") | |
| total_cost = _model_number(row.get("cogs_total"), "Stored total cost") | |
| fees = Decimal(str(item["fees"])) | |
| stored_margin = Decimal(str(row.get("margin"))) | |
| if not sold or not sales or not fees.is_finite() or not stored_margin.is_finite() or spend > sales: | |
| return unavailable("Current unit economics cannot be represented. Positive net sales and units with TACoS at most 100% are required.") | |
| unit_cost = total_cost / sold | |
| if _cents(unit_cost) != _cents(displayed_cost) or cost != _cents(total_cost): | |
| return unavailable("Stored unit cost does not match receipt COGS. Refresh Command Center.") | |
| actual = profit.compute_row(item, float(unit_cost), 1) | |
| if (_cents(actual["net_profit"]) != _cents(row.get("net_profit")) | |
| or _cents(Decimal(str(actual["margin"])) * 100) != _cents(stored_margin)): | |
| return unavailable("Current margin does not reconcile. Refresh Command Center.") | |
| except (ValueError, InvalidOperation, TypeError, OverflowError): | |
| return unavailable("Current unit economics unavailable. Refresh Command Center.") | |
| baseline = {"price": sales / sold, "amazon_fees_per_unit": fees / sold, | |
| "landed_cost": unit_cost, "tacos": spend / sales} | |
| modeled_price = supplied.get("price", baseline["price"]) | |
| modeled_cost = supplied.get("landed_cost", baseline["landed_cost"]) | |
| modeled_tacos = supplied.get("tacos", baseline["tacos"]) | |
| monthly_units = supplied.get("units") | |
| ads = modeled_price * modeled_tacos | |
| fee_per_unit = baseline["amazon_fees_per_unit"] | |
| unit_profit = modeled_price - fee_per_unit - ads - modeled_cost | |
| result = {"price": modeled_price, "amazon_fees_per_unit": fee_per_unit, "ad_cost_per_unit": ads, | |
| "landed_cost": modeled_cost, "tacos": modeled_tacos, "profit_per_unit": unit_profit, | |
| "margin": unit_profit / modeled_price if modeled_price else None, | |
| "monthly_profit": unit_profit * monthly_units if monthly_units is not None else None, | |
| "units": monthly_units} | |
| if any(value is not None and not math.isfinite(float(value)) for value in result.values()): | |
| raise ValueError("Model inputs produce amounts outside the supported range.") | |
| return {**out, "available": True, "reason": None, "currency": currency, | |
| "basis": "Net product sales after source refunds and promotions. Observed Amazon fees stay fixed per unit.", | |
| "tacosScale": "Fraction from 0 to 1. For 10%, use 0.10.", | |
| "monthlyUnitsBasis": "Supplied assumption, separate from units sold in the baseline period.", | |
| "baseline": {**{key: float(value) for key, value in baseline.items()}, | |
| "units_sold": int(sold), "net_sales": float(sales), "net_profit": actual["net_profit"], | |
| "margin": actual["margin"], "stored_margin_percent": float(stored_margin)}, | |
| **{key: None if value is None else float(value) for key, value in result.items()}} | |
| def _ready(con, table): | |
| """Physical tables and completed sync markers are separate from a window's row count.""" | |
| from harness import amazon_store | |
| if not profit._columns(con, table): | |
| return f"Missing source: {table}" | |
| if not {"entity", "phase", "cursor_wd", "updated_at", "rows"} <= profit._columns(con, "_sync_state"): | |
| return f"Source readiness unavailable: {table}" | |
| state = amazon_store._read_state(con, table) | |
| if not state or state["state"] not in ("done", "live"): | |
| return f"Source still loading: {table}" | |
| return None | |
| def _cost(item, row, bounds, settings, today): | |
| """Use T31's FIFO snapshot only for its exact window and unchanged economics inputs.""" | |
| reason = "Receipt cost unavailable for selected window. Refresh Command Center for this period." | |
| if not row or settings.get("refusal"): | |
| return None, reason | |
| saved = profit.period_bounds(settings.get("period"), today) | |
| if (saved["start"], saved["end"]) != (bounds["start"], bounds["end"]) or row.get("period") != saved["label"]: | |
| return None, reason | |
| expected = {"net_sales": item["net_sales"], "fees": item["fees"], "sponsored_ads": item["ads"], | |
| "net_proceeds": item["net_sales"] - item["fees"] - item["ads"]} | |
| if any(_cents(value) is None for value in expected.values()): | |
| return None, "Economics amounts unavailable" | |
| if any(_cents(row.get(key)) != _cents(value) for key, value in expected.items()): | |
| return None, "Receipt cost snapshot no longer matches the mirror. Refresh Command Center." | |
| try: | |
| units_match = Decimal(str(row.get("units_sold"))) == Decimal(str(item["units"])) | |
| except (InvalidOperation, ValueError, TypeError): | |
| units_match = False | |
| cost = _cents(row.get("cogs_total")) | |
| source = str(row.get("cost_source") or "") | |
| # R43: a typed Amazon COGS stands only where the refresh found no Odoo receipt. | |
| valid_source = source in ("No units sold", profit.TYPED_COST_SOURCE) or all( | |
| part in ("Landed cost", "Purchase price (no landed costs)") for part in source.split(", ")) | |
| if not units_match or cost is None or not valid_source: | |
| return None, source or reason | |
| if _cents(row.get("net_profit")) != _cents(expected["net_proceeds"]) - cost: | |
| return None, "Receipt cost snapshot profit does not reconcile. Refresh Command Center." | |
| return cost, None | |
| def typed_cost_line(count): | |
| """How many SKUs in a window use a typed Amazon COGS, in plain words; None when none do.""" | |
| if not count: | |
| return None | |
| return f"{count} SKU uses a typed cost." if count == 1 else f"{count} SKUs use a typed cost." | |
| def _ads_coverage(con, bounds): | |
| """Read existing per-connection coverage without stamping connection or continuation state.""" | |
| from harness import amazon_ads_store as ads | |
| from harness import amazon_store as store | |
| sources = ["_sync_state", *ADS_TABLES] | |
| cols = profit._columns(con, "_sync_state") | |
| if not {"entity", "phase", "cursor_wd"} <= cols: | |
| return "Connect Amazon Ads", sources | |
| markers = {key: (phase, detail) for key, phase, detail in con.execute( | |
| "SELECT entity, phase, cursor_wd FROM _sync_state WHERE starts_with(entity, ?)", | |
| [store.entity_key("ads_")]).fetchall()} | |
| # Continuation keys start with sync_mark_/sync_floor_, outside the ads_ prefix. | |
| for key, phase, detail in con.execute( | |
| "SELECT entity, phase, cursor_wd FROM _sync_state WHERE starts_with(entity, ?) " | |
| "OR starts_with(entity, ?)", [store.entity_key(store.WATERMARK_PREFIX + "ads_"), | |
| store.entity_key(store.BACKFILL_FLOOR_PREFIX + "ads_")]).fetchall(): | |
| markers[key] = phase, detail | |
| prefix = ads.CONNECT_DAY_KEY + "::connection::" | |
| connections = {key[len(prefix):]: detail for key, (phase, detail) in markers.items() | |
| if key.startswith(prefix) and phase == "done"} | |
| if not connections and markers.get(ads.CONNECT_DAY_KEY, (None, None))[0] == "done": | |
| connections = {None: markers[ads.CONNECT_DAY_KEY][1]} | |
| if not connections: | |
| have = any(profit._columns(con, table) for table in ADS_TABLES) | |
| return ("Amazon Ads connect day unavailable" if have else "Connect Amazon Ads"), sources | |
| for table in ADS_TABLES: | |
| have = profit._columns(con, table) | |
| if not {"date", "sales"} <= have: | |
| return f"Missing source: {table}.sales", sources | |
| if "_connection_id" in have: | |
| ids = {row[0] or None for row in con.execute( | |
| f'SELECT DISTINCT _connection_id FROM "{table}"').fetchall()} | |
| if not ids <= connections.keys(): | |
| return "Amazon Ads connection coverage unavailable", sources | |
| elif any(cid is not None for cid in connections): | |
| return "Amazon Ads connection attribution unavailable", sources | |
| for cid, day in connections.items(): | |
| try: | |
| connected = dt.date.fromisoformat(str(day)[:10]).isoformat() | |
| except ValueError: | |
| return "Amazon Ads connect day unavailable", sources | |
| if bounds["start"] < connected: | |
| return "Amazon Ads history starts at the connect day", sources | |
| mark = table[len(store.PREFIX):] | |
| phase = markers.get(ads._state_key(store.entity_key(mark), cid), (None, None))[0] | |
| floor = markers.get(ads._state_key(store.entity_key(store.BACKFILL_FLOOR_PREFIX + mark), cid), (None, None))[1] | |
| last = markers.get(ads._state_key(store.entity_key(store.WATERMARK_PREFIX + mark), cid), (None, None))[1] | |
| try: | |
| floor = dt.date.fromisoformat(str(floor)[:10]).isoformat() | |
| last = dt.date.fromisoformat(str(last)[:10]).isoformat() | |
| except ValueError: | |
| return f"Amazon Ads coverage unavailable: {table}", sources | |
| if phase != "done" or floor > bounds["start"] or last < bounds["end"]: | |
| return f"Amazon Ads coverage incomplete: {table}", sources | |
| return None, sources | |
| def _attribution(con, bounds, daily): | |
| reason, sources = _ads_coverage(con, bounds) | |
| ads_by_day = {} | |
| if reason is None: | |
| union = " UNION ALL ".join(f'SELECT left(CAST(date AS VARCHAR),10) AS day, sales FROM "{t}"' | |
| for t in ADS_TABLES) | |
| rows = con.execute(f"SELECT day, SUM(sales), count(*), count(sales) FROM ({union}) " | |
| "WHERE day BETWEEN ? AND ? GROUP BY day ORDER BY day", | |
| [bounds["start"], bounds["end"]]).fetchall() | |
| if not rows: | |
| reason = "No Amazon Ads source rows for window" | |
| elif any(n != present or _cents(sales) is None for _day, sales, n, present in rows): | |
| reason = "Amazon Ads sales amounts unavailable" | |
| else: | |
| ads_by_day = {day: _cents(sales) for day, sales, _n, _present in rows} | |
| days = set(daily) | set(ads_by_day) | |
| series = [] | |
| for day in sorted(days): | |
| sales = daily.get(day) | |
| ad_sales = ads_by_day.get(day, 0) if reason is None else None | |
| missing = reason or ("No economics source rows for day" if sales is None else None) | |
| series.append({"day": day, "netSales": _metric(sales, [ECONOMICS], no_prior=sales is None, | |
| reason="No economics source rows for day" if sales is None else None), | |
| "adSales": _metric(ad_sales, sources, reason=reason, no_prior=reason is not None), | |
| "organicSales": _metric(None if missing else sales - ad_sales, [ECONOMICS, *sources], | |
| reason=missing, no_prior=missing is not None)}) | |
| total = sum(ads_by_day.values()) if reason is None else None | |
| product_sales = sum(daily.values()) if daily else None | |
| organic_reason = reason or ("No economics source rows for window" if product_sales is None else None) | |
| out = {"daily": series, "adSales": _metric(total, sources, reason=reason, no_prior=reason is not None), | |
| "organicSales": _metric(None if total is None or product_sales is None else product_sales - total, | |
| [ECONOMICS, *sources], reason=organic_reason, no_prior=organic_reason is not None), | |
| "reason": reason, "sourceTables": [ECONOMICS, *sources]} | |
| out["validate"] = validate_attribution(con, bounds, out) | |
| if not out["validate"]["ok"]: | |
| for key in ("adSales", "organicSales"): | |
| out[key]["validated"] = False | |
| out[key]["reason"] = out[key]["reason"] or out["validate"]["reason"] | |
| return out | |
| def validate_attribution(con, bounds, view): | |
| """Reconcile chart totals to independent scalar SQL and enforce the connection coverage wall.""" | |
| why, sources = _ads_coverage(con, bounds) | |
| why = why or view["reason"] | |
| checks = [] | |
| if why: | |
| return {"ok": False, "state": "unavailable", "reason": why, "sourceTables": sources, "checks": []} | |
| params = [bounds["start"], bounds["end"]] | |
| union = " UNION ALL ".join(f'SELECT date, sales FROM "{t}"' for t in ADS_TABLES) | |
| ad_total = _cents(con.execute(f"SELECT SUM(sales) FROM ({union}) WHERE " | |
| "left(CAST(date AS VARCHAR),10) BETWEEN ? AND ?", params).fetchone()[0]) | |
| net_total = _cents(con.execute(f"SELECT SUM(sales_net_product_sales_amount) FROM {ECONOMICS} WHERE " | |
| "left(CAST(start_date AS VARCHAR),10) BETWEEN ? AND ?", params).fetchone()[0]) | |
| def check(name, observed, expected): | |
| checks.append({"check": name, "ok": observed is not None and expected is not None and observed == expected, | |
| "a": None if observed is None else observed / 100, | |
| "b": None if expected is None else expected / 100}) | |
| ad_values = [_cents(day["adSales"]["value"]) for day in view["daily"]] | |
| organic_values = [_cents(day["organicSales"]["value"]) for day in view["daily"]] | |
| check("Daily ad sales equal advertised-product source sales", sum(ad_values) if None not in ad_values else None, ad_total) | |
| check("Attributed total equals advertised-product source sales", _cents(view["adSales"]["value"]), ad_total) | |
| expected_organic = None if ad_total is None or net_total is None else net_total - ad_total | |
| check("Organic total equals source net sales minus attributed sales", _cents(view["organicSales"]["value"]), expected_organic) | |
| check("Daily organic sales equal source net sales minus attributed sales", | |
| sum(organic_values) if None not in organic_values else None, expected_organic) | |
| ok = all(c["ok"] for c in checks) | |
| return {"ok": ok, "state": "tied" if ok else "failed", "reason": None if ok else "Attribution does not reconcile", | |
| "checks": checks, "sourceTables": [ECONOMICS, *sources]} | |
| def _window(con, bounds, stored, settings, today): | |
| sources = [ECONOMICS] | |
| cols = profit._require(con, ECONOMICS, profit._ECONOMICS_REQUIRED) | |
| ads_cols = profit.ads_columns(cols) | |
| optional = [column for column in (ORDERED, REFUNDS) if column in cols] | |
| sums = ", ".join(f'SUM("{c}"), count("{c}")' for c in optional) | |
| where = "left(CAST(start_date AS VARCHAR),10) BETWEEN ? AND ?" | |
| count, *breakdown = con.execute(f"SELECT count(*){', ' + sums if sums else ''} FROM {ECONOMICS} WHERE {where}", | |
| [bounds["start"], bounds["end"]]).fetchone() | |
| no_prior = count == 0 | |
| reason = "No economics source rows for window" if no_prior else None | |
| amounts = {column: _cents(breakdown[i * 2]) if breakdown[i * 2 + 1] == count and count else None | |
| for i, column in enumerate(optional)} | |
| invalid = con.execute( | |
| f"SELECT count(*) FROM {ECONOMICS} WHERE {where} AND (" | |
| + " OR ".join(f'"{c}" IS NULL OR NOT isfinite("{c}")' for c in | |
| ("sales_net_product_sales_amount", "fees_total_amount", "sales_net_units_sold")) | |
| + "".join(f' OR ("{c}" IS NOT NULL AND NOT isfinite("{c}"))' for c in ads_cols) | |
| + " OR NULLIF(trim(msku),'') IS NULL)", [bounds["start"], bounds["end"]]).fetchone()[0] | |
| if invalid: | |
| raise profit.SourceUnavailable("Economics amounts or SKU unavailable. Resync Amazon economics.") | |
| reason = reason or ("Missing source: economics ad spend totals" if not ads_cols else None) | |
| items = profit.economics_by_sku(con, bounds["start"], bounds["end"]) if not no_prior else [] | |
| activity = {sku for (sku,) in con.execute( | |
| f"SELECT msku FROM {ECONOMICS} WHERE {where} GROUP BY msku", | |
| [bounds["start"], bounds["end"]]).fetchall()} if count else set() | |
| skus, costs, typed = [], [], 0 | |
| for item in items: | |
| amounts_sku = [_cents(item[key]) for key in ("net_sales", "fees", "ads")] | |
| proceeds = amounts_sku[0] - amounts_sku[1] - amounts_sku[2] if None not in amounts_sku else None | |
| cost, cost_reason = _cost(item, stored.get(item["sku"]), bounds, settings, today) | |
| # A historical SKU with no selected-window activity contributes zero cost. | |
| if item["sku"] not in activity: | |
| cost, cost_reason = 0, None | |
| elif cost is not None and str((stored.get(item["sku"]) or {}).get("cost_source") or "") == profit.TYPED_COST_SOURCE: | |
| typed += 1 | |
| costs.append(cost) | |
| skus.append({"sku": item["sku"], "asin": item.get("asin"), "units": item["units"], | |
| "netSales": _metric(_cents(item["net_sales"]), sources, reason=reason, validated=reason is None), | |
| "netProceeds": _metric(proceeds if reason is None else None, sources, reason=reason), | |
| "cogs": _metric(cost, [profit.TABLE_KEY], reason=cost_reason), | |
| "netProfit": _metric(None if cost is None or reason or proceeds is None else proceeds - cost, | |
| [ECONOMICS, profit.TABLE_KEY], reason=reason or cost_reason)}) | |
| total = lambda field: sum(_cents(item[field]) for item in items) if reason is None else None | |
| net_sales, fees, spend = total("net_sales"), total("fees"), total("ads") | |
| proceeds = None if net_sales is None else net_sales - fees - spend | |
| ordered, refunds = amounts.get(ORDERED), amounts.get(REFUNDS) | |
| gross_bridge = net_sales is not None and ordered is not None and refunds is not None and ordered - refunds == net_sales | |
| cogs = sum(costs) if costs and all(c is not None for c in costs) else None | |
| cost_reason = next((s["cogs"]["reason"] for s in skus if s["cogs"]["value"] is None), reason) | |
| if cogs is None and not cost_reason: | |
| cost_reason = "Receipt cost unavailable for selected window" | |
| typed_line = typed_cost_line(typed) | |
| # The typed count rides on the COGS figure only: net profit keeps its own reason and checks. | |
| cogs_reason = cost_reason if cost_reason or cogs is None else typed_line | |
| components = [] | |
| def component(key, label, cents, tables, *, included=True, why=None): | |
| components.append({"key": key, "label": label, "included": included, | |
| **_metric(cents, tables, reason=why, no_prior=no_prior)}) | |
| component("sales", "Ordered product sales" if gross_bridge else "Net product sales", ordered if gross_bridge else net_sales, | |
| sources, why=reason) | |
| component("adSpend", "Ad spend", None if spend is None else -spend, sources, why=reason) | |
| component("amazonFees", "Amazon fees", None if fees is None else -fees, sources, why=reason) | |
| component("promotions", "Customer promotions", None, sources, included=False, | |
| why="Missing source: customer promotion breakdown. Fee-charge promotions are fee rebates.") | |
| component("refunds", "Refunds" if gross_bridge else "Refunds already included in net sales", | |
| None if refunds is None else -refunds, sources, included=gross_bridge, | |
| why=reason or ("Missing source: " + REFUNDS if refunds is None else None)) | |
| component("cogs", "COGS", None if cogs is None else -cogs, [profit.TABLE_KEY], why=cogs_reason) | |
| net = None if cogs is None or proceeds is None else proceeds - cogs | |
| metrics = {"netSales": _metric(net_sales, sources, reason=reason, no_prior=no_prior), | |
| "netProceeds": _metric(proceeds, sources, reason=reason, no_prior=no_prior), | |
| "cogs": _metric(cogs, [profit.TABLE_KEY], reason=cogs_reason, no_prior=no_prior), | |
| "netProfit": _metric(net, [ECONOMICS, profit.TABLE_KEY], reason=reason or cost_reason, no_prior=no_prior)} | |
| ranked = sorted(skus, key=lambda s: (-(s["netSales"]["value"] or 0), s["sku"])) | |
| mix = [{"sku": s["sku"], "sales": s["netSales"]} for s in ranked[:10]] | |
| if len(ranked) > 10: | |
| remainder = [_cents(s["netSales"]["value"]) for s in ranked[10:]] | |
| mix.append({"sku": "Other", "isOther": True, | |
| "sales": _metric(sum(remainder) if None not in remainder else None, sources, | |
| reason=reason, validated=reason is None)}) | |
| days = con.execute(f"SELECT left(CAST(start_date AS VARCHAR),10), SUM(sales_net_product_sales_amount) " | |
| f"FROM {ECONOMICS} WHERE {where} GROUP BY 1 ORDER BY 1", | |
| [bounds["start"], bounds["end"]]).fetchall() | |
| daily = {day: _cents(sales) for day, sales in days if _cents(sales) is not None} | |
| out = {"period": bounds, "noPrior": no_prior, "sourceRows": count, "reason": reason, | |
| "sourceTables": [ECONOMICS, profit.SETTLEMENTS, profit.TABLE_KEY], "metrics": metrics, | |
| "waterfall": {"components": components, "netProfit": metrics["netProfit"], | |
| "salesBasis": "orderedLessRefunds" if gross_bridge else "netProductSales", | |
| "reason": None if gross_bridge else "Refunds and promotions remain embedded in net product sales."}, | |
| "skus": skus, "salesMix": mix, "attribution": _attribution(con, bounds, daily), | |
| "typedCost": {"skus": typed, "line": typed_line}} | |
| out["validate"] = validate(con, out) | |
| out["waterfall"]["validate"] = {"ok": all(c["ok"] for c in out["validate"]["checks"] if "Waterfall" in c["check"]), | |
| "checks": [c for c in out["validate"]["checks"] if "Waterfall" in c["check"]]} | |
| out["salesMixValidate"] = next(c for c in out["validate"]["checks"] if c["check"].startswith("Top ten")) | |
| out["validated"] = out["validate"]["ok"] | |
| if not out["validated"]: | |
| metrics["netProfit"]["validated"] = False | |
| metrics["netProfit"]["reason"] = metrics["netProfit"]["reason"] or "Account P&L does not reconcile" | |
| return out | |
| def validate(con, view): | |
| """Independently reconcile the waterfall, SKU sums and settlement source aggregates.""" | |
| bounds, metrics = view["period"], view["metrics"] | |
| start, end = bounds["start"], bounds["end"] | |
| checks = [] | |
| def check(name, a, b, *, why=None, tolerance=0): | |
| ok = a is not None and b is not None and abs(a - b) <= tolerance and not why | |
| checks.append({"check": name, "ok": ok, "a": a, "b": b, "reason": why, | |
| "sourceTables": view["sourceTables"]}) | |
| observed = profit.net_proceeds_total(con, start, end) if view["sourceRows"] else None | |
| amount = metrics["netProceeds"]["value"] | |
| check("SKU net proceeds equal independent economics total", amount, observed, | |
| tolerance=0.01, why=view["reason"]) | |
| before = [c for c in view["waterfall"]["components"] if c["included"] and c["key"] != "cogs"] | |
| cents = [_cents(c["value"]) for c in before] | |
| bridge = sum(cents) / 100 if cents and all(c is not None for c in cents) else None | |
| check("Waterfall before COGS equals SKU net proceeds", bridge, amount, tolerance=0.001, | |
| why=view["reason"]) | |
| why = _ready(con, profit.SETTLEMENTS) | |
| settled, span, proceeds = None, None, bridge | |
| if why is None: | |
| try: | |
| currency = profit.economics_currency(con) | |
| span = profit.settlement_coverage(con, start, end, currency) | |
| # The tie reads only the days issued settlements cover, on both sides. | |
| lo, hi = span["start"] or start, span["end"] or end | |
| settled = profit.settlement_components(con, lo, hi, currency) | |
| why = settled["reason"] | |
| if span["start"] is None: | |
| settled = dict(settled, rows=0) | |
| why = f"Not verified: settlement components in {dt.date.fromisoformat(start):%B %Y}: no settled days." | |
| elif (lo, hi) != (start, end) and bridge is not None: | |
| proceeds = profit.net_proceeds_total(con, lo, hi) | |
| except profit.SourceUnavailable as exc: | |
| why = str(exc) | |
| target = settled["total"] if settled and settled["rows"] else None | |
| if target is not None and not math.isfinite(target): | |
| target, why = None, "Settlement amounts unavailable" | |
| tied, gap = profit._tie(proceeds, target) if proceeds is not None and target is not None else (False, None) | |
| gap = gap if gap is None or math.isfinite(gap) else None | |
| covered = {k: span[k] for k in ("start", "end", "label", "partial", "clear", "sentence")} if span else None | |
| checks.append({"check": "Net proceeds tie to settlements within 2%", | |
| "ok": tied and not why, | |
| "a": proceeds, "b": target, "gap": gap, "tolerance": profit.SETTLEMENT_TOLERANCE, | |
| "reason": why or ("No settlement source rows for window" if target is None else None), | |
| "components": [{**part, "gap": part["gap"] if math.isfinite(part["gap"]) else None} | |
| for part in settled["parts"]] if settled else [], | |
| "excluded": settled.get("excluded", {}) if settled else {}, | |
| "covered": covered, "note": covered and covered["sentence"], | |
| "sourceTables": [ECONOMICS, profit.SETTLEMENTS, "amzn_orders", *ADS_TABLES]}) | |
| values = [_cents(s["netProfit"]["value"]) for s in view["skus"]] | |
| sku_net = sum(values) / 100 if values and all(c is not None for c in values) else None | |
| check("Account net profit equals sum of SKU profit after COGS", metrics["netProfit"]["value"], sku_net, | |
| why=metrics["netProfit"]["reason"], tolerance=0.001) | |
| sku_before = [_cents(s["netProceeds"]["value"]) for s in view["skus"]] | |
| sku_costs = [_cents(s["cogs"]["value"]) for s in view["skus"]] | |
| after_costs = ((sum(sku_before) - sum(sku_costs)) / 100 | |
| if sku_before and None not in sku_before + sku_costs else None) | |
| check("Account net profit equals SKU net proceeds minus SKU COGS", metrics["netProfit"]["value"], after_costs, | |
| why=metrics["netProfit"]["reason"], tolerance=0.001) | |
| full = [_cents(c["value"]) for c in view["waterfall"]["components"] if c["included"]] | |
| check("Waterfall components sum to account net profit", sum(full) / 100 if full and None not in full else None, | |
| metrics["netProfit"]["value"], why=metrics["netProfit"]["reason"], tolerance=0.001) | |
| mix = [_cents(s["sales"]["value"]) for s in view["salesMix"]] | |
| check("Top ten plus Other preserves net product sales", sum(mix) / 100 if None not in mix else None, | |
| metrics["netSales"]["value"], why=view["reason"], tolerance=0.001) | |
| return {"ok": all(c["ok"] for c in checks), "state": "tied" if all(c["ok"] for c in checks) else | |
| "unavailable" if view["reason"] or metrics["netProfit"]["value"] is None or target is None else "failed", | |
| "checks": checks, "sourceTables": view["sourceTables"]} | |
| def validate_pace(con, bounds, pace, elapsed, month_days, reason): | |
| """Check the forecast arithmetic against independent source sums without certifying an outcome.""" | |
| if reason or not elapsed: | |
| return {"ok": False, "state": "unavailable", "reason": reason or "No complete days in current month", | |
| "sourceTables": [ECONOMICS, "_sync_state"], "checks": []} | |
| cols = profit._columns(con, ECONOMICS) | |
| ad_expr = " + ".join(f'COALESCE("{c}",0)' for c in profit.ads_columns(cols)) or "0" | |
| sales, proceeds = con.execute( | |
| f"SELECT SUM(sales_net_product_sales_amount), SUM(sales_net_product_sales_amount - fees_total_amount - ({ad_expr})) " | |
| f"FROM {ECONOMICS} WHERE left(CAST(start_date AS VARCHAR),10) BETWEEN ? AND ?", | |
| [bounds["start"], bounds["end"]]).fetchone() | |
| checks = [] | |
| for key, source in (("netSales", sales), ("netProceeds", proceeds)): | |
| expected = None if source is None else _cents(Decimal(str(source)) * month_days / elapsed) | |
| observed = _cents(pace[key]["value"]) | |
| checks.append({"check": f"{key} pace equals source daily rate times calendar days", | |
| "ok": expected is not None and observed is not None and abs(expected - observed) <= 1, | |
| "a": pace[key]["value"], "b": None if expected is None else expected / 100, | |
| "reason": pace[key]["reason"]}) | |
| return {"ok": all(c["ok"] for c in checks), "state": "estimate", "reason": None, | |
| "checks": checks, "sourceTables": [ECONOMICS, "_sync_state"]} | |
| class _SourceRows: | |
| def __init__(self, rows): | |
| self.rows = rows | |
| def fetchone(self): | |
| return self.rows[0] if self.rows else None | |
| def fetchall(self): | |
| return list(self.rows) | |
| def _settlement_source(sql): | |
| """Parse distinct settlement labels and referenced order dates without losing source lines or null keys.""" | |
| expressions = {"posted": ("posted_date", f"CAST({profit._moment_sql('s.posted_date')} AS DATE)")} | |
| for name, field in (("description", "amount_description"), ("kind", "amount_type"), | |
| ("transaction", "transaction_type")): | |
| expressions[name] = field, f"lower(regexp_replace(COALESCE({field}, ''), '[^a-zA-Z0-9]', '', 'g'))" | |
| fields = [field for field, _expression in expressions.values()] | |
| projection = ", ".join(expression.replace("s.posted_date", "posted_date") + " AS " + name | |
| for name, (_field, expression) in expressions.items()) | |
| keys = ", ".join(f"{field} AS source_{field}" for field in fields) | |
| labels = (f"labels AS MATERIALIZED (SELECT {keys}, {projection} " | |
| f"FROM (SELECT DISTINCT {', '.join(fields)} FROM {profit.SETTLEMENTS})), ") | |
| for name, (_field, expression) in expressions.items(): | |
| sql = sql.replace(expression + " AS " + name, "label." + name + " AS " + name) | |
| join = " AND ".join(f"s.{field} IS NOT DISTINCT FROM label.source_{field}" for field in fields) | |
| sql = sql.replace(f"FROM {profit.SETTLEMENTS} s JOIN streams", | |
| f"FROM {profit.SETTLEMENTS} s JOIN labels label ON {join} JOIN streams") | |
| sql = sql.replace("FROM amzn_orders GROUP BY amazon_order_id", | |
| f"FROM amzn_orders WHERE amazon_order_id IN (SELECT trim(order_id) " | |
| f"FROM {profit.SETTLEMENTS}) GROUP BY amazon_order_id") | |
| return sql.replace("WITH orders AS (", "WITH " + labels + "orders AS (", 1) | |
| class _ValidationSources: | |
| """Share settlement source preparation across this request's independent window sums.""" | |
| def __init__(self, con, bounds): | |
| self.con, self.results, self.spans = con, {}, {} | |
| self.bounds = [span for bound in bounds for span in (bound, *_comparisons(bound).values())] | |
| def _spans(self, currency=None): | |
| if currency in self.spans: | |
| return self.spans[currency] | |
| spans = {(bound["start"], bound["end"]) for bound in self.bounds} | |
| if currency is not None: | |
| for start, end in tuple(spans): | |
| covered = profit.settlement_coverage(self, start, end, currency) | |
| spans.add((covered["start"] or start, covered["end"] or end)) | |
| self.spans[currency] = sorted(spans) | |
| return self.spans[currency] | |
| def _components(self, sql, params): | |
| marker = "SELECT component, COALESCE(SUM(currency_amount), 0)" | |
| prefix, select = sql.split(marker, 1) | |
| aggregates, select = (marker + select).split("FROM dated WHERE", 1) | |
| aggregates = aggregates.removeprefix("SELECT component, ") | |
| # The grouping keys retain every input to the original window predicate and its bad-row counts. | |
| prefix += (" , prepared(component, basis, posted, dates, bad_dates, amount, source_rows, " | |
| "bad_amounts, unmatched_orders) AS (SELECT component, basis, posted, dates, bad_dates, " | |
| + aggregates + " FROM dated GROUP BY component, basis, posted, dates, bad_dates) ") | |
| spans = self._spans(params[0]) | |
| for bound in ("lo", "hi", "lo", "hi"): | |
| select = select.replace("CAST(? AS DATE)", "span." + bound, 1) | |
| ranges = ", ".join(f"({index}, CAST(? AS DATE), CAST(? AS DATE))" for index in range(len(spans))) | |
| select = f"FROM prepared JOIN (VALUES {ranges}) AS span(id, lo, hi) ON" + select | |
| select = select.replace("GROUP BY component ORDER BY component", | |
| "GROUP BY span.id, component ORDER BY span.id, component") | |
| bindings = list(params[:2]) + [value for span in spans for value in span] | |
| rows = self.con.execute(_settlement_source(prefix) + "SELECT span.id, component, " | |
| "COALESCE(SUM(amount), 0), SUM(source_rows), SUM(bad_amounts), SUM(unmatched_orders) " | |
| + select, bindings).fetchall() | |
| for index, span in enumerate(spans): | |
| self.results[(sql, tuple(params[:2]) + span * 2)] = [row[1:] for row in rows if row[0] == index] | |
| def _unscoped(self, sql): | |
| marker = "AND (CAST(" | |
| source, _date_filter = sql.split(marker, 1) | |
| source = source.replace("SELECT count(*)", f"SELECT CAST({profit._moment_sql('posted_date')} AS DATE) AS posted", 1) | |
| spans = self._spans(profit.economics_currency(self.con)) | |
| sums = ["count(*) FILTER (WHERE posted BETWEEN CAST(? AS DATE) AND CAST(? AS DATE) OR posted IS NULL)" | |
| for _span in spans] | |
| counts = self.con.execute("SELECT " + ", ".join(sums) + " FROM (" + source + ")", | |
| [value for span in spans for value in span]).fetchone() | |
| for index, count in enumerate(counts): | |
| self.results[(sql, spans[index])] = [(count,)] | |
| def execute(self, sql, params=()): | |
| params = tuple(params or ()) | |
| key = sql, params | |
| if key in self.results: | |
| return _SourceRows(self.results[key]) | |
| if sql == "SELECT column_name FROM information_schema.columns WHERE table_name = ?": | |
| self.results[key] = self.con.execute(sql, params).fetchall() | |
| return _SourceRows(self.results[key]) | |
| components = (sql.startswith("WITH orders AS (") and len(params) == 6 | |
| and sql.endswith(" GROUP BY component ORDER BY component")) | |
| unscoped = (sql.startswith(f"SELECT count(*) FROM {profit.SETTLEMENTS} s\n") | |
| and "WHERE h.settlement_id IS NULL" in sql and len(params) == 2) | |
| if components: | |
| self._components(sql, params) | |
| elif unscoped: | |
| self._unscoped(sql) | |
| if key in self.results: | |
| return _SourceRows(self.results[key]) | |
| return self.con.execute(sql, params) | |
| def _account_bounds(chosen, today): | |
| bounds = [chosen, _bounds(today.replace(day=1), today - dt.timedelta(days=1), "mtd")] | |
| current = today.replace(day=1) | |
| for _ in range(2): | |
| last = current - dt.timedelta(days=1) | |
| current = last.replace(day=1) | |
| bounds.append(_bounds(current, last, current.strftime("%Y-%m"))) | |
| return bounds | |
| def account_view(con, period=None, *, stored=None, settings=None, today=None): | |
| """Return selected, comparison and monthly account windows without refreshing stored inputs.""" | |
| today = today or profit._today() | |
| selected = profit.period_bounds(period or profit.last_complete_month(today), today) | |
| end = min(dt.date.fromisoformat(selected["end"]), today - dt.timedelta(days=1)) | |
| chosen = _bounds(dt.date.fromisoformat(selected["start"]), end, selected["key"]) | |
| if dt.date.fromisoformat(chosen["start"]).year < 2: | |
| raise ValueError("Choose a period with an available year-ago calendar window.") | |
| if not profit._mirror_has_economics(con): | |
| return {"empty": True, "reason": "Sync Amazon economics first", "period": chosen, | |
| "sourceTables": [ECONOMICS], "validated": False} | |
| problem = _ready(con, ECONOMICS) | |
| if problem: | |
| raise profit.SourceUnavailable(problem) | |
| profit._require(con, ECONOMICS, profit._ECONOMICS_REQUIRED) | |
| if con.execute(f"SELECT count(*) FROM {ECONOMICS} WHERE " | |
| "TRY_CAST(left(CAST(start_date AS VARCHAR),10) AS DATE) IS NULL").fetchone()[0]: | |
| raise profit.SourceUnavailable("Economics dates unavailable. Resync Amazon economics.") | |
| stored, settings = stored or {}, settings or {} | |
| periods = _account_bounds(chosen, today) | |
| con = _ValidationSources(con, periods) | |
| cache = {} | |
| def window(bounds): | |
| key = bounds["start"], bounds["end"] | |
| if key not in cache: | |
| cache[key] = _window(con, bounds, stored, settings, today) | |
| return {**cache[key], "period": bounds} | |
| def compared(bounds): | |
| return {**window(bounds), "comparisons": {key: window(span) for key, span in _comparisons(bounds).items()}} | |
| mtd = compared(periods[1]) | |
| months = [compared(bounds) for bounds in periods[2:]] | |
| pace = {} | |
| elapsed = mtd["period"]["days"] | |
| month_days = calendar.monthrange(today.year, today.month)[1] | |
| from harness import amazon_store | |
| coverage = [amazon_store._read_state(con, amazon_store.entity_key(prefix + "economics")) | |
| for prefix in (amazon_store.BACKFILL_FLOOR_PREFIX, amazon_store.WATERMARK_PREFIX)] | |
| floor, last = [str((state or {}).get("detail") or "")[:10] for state in coverage] | |
| pace_reason = None if all((state or {}).get("state") == "done" for state in coverage) and \ | |
| floor and last and floor <= mtd["period"]["start"] and last >= mtd["period"]["end"] else \ | |
| "Economics coverage unavailable for pace forecast" | |
| for key, metric in mtd["metrics"].items(): | |
| value = _cents(metric["value"]) | |
| forecast = None if value is None or not elapsed or pace_reason else int( | |
| (Decimal(value) * month_days / elapsed).quantize(Decimal("1"), rounding=ROUND_HALF_UP)) | |
| pace[key] = {**_metric(forecast, [*metric["sourceTables"], "_sync_state"], reason=metric["reason"] or pace_reason or | |
| ("No complete days in current month" if not elapsed else None), | |
| no_prior=metric["noPrior"] or bool(pace_reason)), "projected": True} | |
| return {**compared(chosen), "empty": False, "currency": profit.economics_currency(con), | |
| "monthToDate": mtd, "previousMonths": months, | |
| "paceForecast": {"metrics": pace, "elapsedDays": elapsed, "monthDays": month_days, | |
| "sourceTables": [ECONOMICS, profit.TABLE_KEY, "_sync_state"], | |
| "reason": pace_reason, | |
| "validate": validate_pace(con, mtd["period"], pace, elapsed, month_days, pace_reason), | |
| "basis": "Complete days through yesterday at a constant daily pace. This is an estimate."}} | |