"""Command Center Data Entry: one row per Amazon SKU that lacks link, units, volume or cost data. `ut_amazon_data_entry` is a projection of `ut_amazon_sku_profit`, so its All view is the incomplete set and a complete SKU leaves it (R8). A person's cell edit writes back to the SKU table in the same request (`write_back`, called from the row hook in `routes_amazon_profit`). Carton CBM pre-fills from Amazon per-unit volume and names its source. A pre-filled value stays here: only a typed value is written back. """ from __future__ import annotations import datetime as dt import json import math import core.user_tables as _registry from core import amazon_inputs, module_tables from modules import amazon_profit as A TABLE_KEY = "ut_amazon_data_entry" TABLE_LABEL = "Command Center Data Entry" TEMPLATE_KEY = "amazon_data_entry" VIA = "amazon_data_entry" WRITER = A.WRITER VOLUMES_KEY = "amazon_data_entry_volumes" NO_ADD_LINE = "Rows here are Amazon SKUs that lack data. Edit a row instead." _registry.register_module_tables(TABLE_KEY) module_tables.register_no_add_records(TABLE_KEY, line=NO_ADD_LINE) #: Cells a person edits here; each one writes back to the same key on the SKU table. ENTRY_KEYS = ("odoo_product", "units_per_sku", "match_state") + A.ENTRY_KEYS #: Cell edits that change the SKU's Odoo link, so its cost is recomputed at once. LINK_KEYS = ("odoo_product", "units_per_sku", "match_state") _OWNED = {f["key"]: f for f in A.OWNED_FIELDS + A.TRACK_FIELDS} FIELDS = ( A._field("sku", "SKU", "text", "Amazon seller SKU.", computed=True), A._field("title", "Title", "text", "Listing title.", computed=True), A._field("missing", "Missing", "text", "What this SKU still needs.", computed=True), _OWNED["odoo_product"], _OWNED["units_per_sku"], _OWNED["match_state"], _OWNED["suggested_product"], _OWNED["carton_size"], _OWNED["units_per_carton"], _OWNED["carton_cbm"], A._field("cbm_source", "CBM source", "text", "Where Carton CBM comes from.", computed=True), _OWNED["amazon_cogs"], ) FIELD_KEYS = tuple(f["key"] for f in FIELDS) #: Gap words, in column order. The Missing cell joins the ones that apply. GAPS = (("odoo_product", "No Odoo product"), ("units_per_sku", "no units per SKU"), ("match_state", "not linked"), ("volume", "no unit volume"), ("cost", "no cost")) ENTERED = "Entered" #: Per-unit volume sources in the order they are trusted. Each yields cubic meters per unit. PLANNING = ("amzn_inventory_planning", "Amazon inventory planning") STORAGE_FEES = ("amzn_storage_fees", "Amazon storage fees") FEE_ESTIMATES = ("amzn_fba_fee_estimates", "Amazon FBA fee estimate") CATALOG = ("amzn_catalog_items", "Amazon catalog package") _CUBIC_INCH_M3 = 0.0254 ** 3 _LENGTH_M = {"inches": 0.0254, "inch": 0.0254, "in": 0.0254, "centimeters": 0.01, "centimetres": 0.01, "centimeter": 0.01, "cm": 0.01, "millimeters": 0.001, "millimeter": 0.001, "mm": 0.001, "meters": 1.0, "meter": 1.0, "m": 1.0, "feet": 0.3048, "foot": 0.3048, "ft": 0.3048} def _number(value): """A finite float from a cell, or None.""" if value is None or isinstance(value, bool): return None try: number = float(str(value).strip()) except (TypeError, ValueError): return None return number if math.isfinite(number) else None def _positive(value): number = _number(value) return number if number is not None and number > 0 else None def _text(number, decimals): return f"{number:.{decimals}f}".rstrip("0").rstrip(".") def _volume_m3(value, unit): """Cubic meters for a volume in cubic feet, meters, inches or centimeters; None otherwise.""" number = _positive(value) if number is None: return None word = "".join(ch for ch in str(unit or "").lower() if ch.isalnum()) if word in ("cubicinches", "cubicinch", "in3", "cuin"): return number * _CUBIC_INCH_M3 if word in ("cubiccentimeters", "cubiccentimetres", "cm3", "cucm", "cc"): return number * 1e-6 return amazon_inputs.convert(number, unit, "cu m") def _sides_m3(sides, unit): """Cubic meters of a box from three side lengths in one length unit; None otherwise.""" scale = _LENGTH_M.get(str(unit or "").strip().lower()) numbers = [_positive(side) for side in sides] if scale is None or None in numbers: return None return numbers[0] * numbers[1] * numbers[2] * scale ** 3 # ---------------------------------------------------------------- per-unit volume (mirror reads) def _columns(con, table): try: return A._columns(con, table) except Exception: # noqa: BLE001 return set() def _newest(cols, table): if "_snapshot_date" not in cols: return "TRUE" return (f'CAST("_snapshot_date" AS VARCHAR) = ' f'(SELECT MAX(CAST("_snapshot_date" AS VARCHAR)) FROM {table})') def _text_col(name): return f'NULLIF(TRIM(CAST("{name}" AS VARCHAR)), \'\')' def _keyed(con, table, key, columns): """`{key: (values...)}` from the newest snapshot, one row per key; {} when a column is absent.""" cols = _columns(con, table) if key not in cols or not set(columns) <= cols: return {} picked = ", ".join(f"MIN({_text_col(c)})" for c in columns) try: rows = con.execute(f"SELECT {_text_col(key)}, {picked} FROM {table} WHERE {_newest(cols, table)} " f"AND {_text_col(key)} IS NOT NULL GROUP BY 1").fetchall() except Exception: # noqa: BLE001 return {} return {str(r[0]): r[1:] for r in rows} def _catalog_m3(raw): """Cubic meters from a catalog `item_package_dimensions` JSON cell, or None.""" try: data = json.loads(raw) if isinstance(raw, str) else raw except (TypeError, ValueError): return None if isinstance(data, list): data = data[0] if data else None if not isinstance(data, dict): return None sides, units = [], set() for name in ("length", "width", "height"): side = data.get(name) if not isinstance(side, dict): return None sides.append(side.get("value")) units.add(str(side.get("unit") or "").strip().lower()) return _sides_m3(sides, units.pop()) if len(units) == 1 else None def unit_volumes(con, rows): """`{sku: [cubic meters per unit, source words]}` from the first Amazon source that has one.""" if con is None: return {} planning = _keyed(con, PLANNING[0], "sku", ("item_volume", "volume_unit_measurement")) storage = _keyed(con, STORAGE_FEES[0], "asin", ("item_volume", "volume_units")) fees = _keyed(con, FEE_ESTIMATES[0], "sku", ("longest_side", "median_side", "shortest_side", "unit_of_dimension")) catalog = _keyed(con, CATALOG[0], "asin", ("attributes_item_package_dimensions",)) out = {} for row in rows.values(): sku = str(row.get("sku") or "").strip() asin = str(row.get("asin") or "").strip() if not sku: continue found = ( (PLANNING[1] + " item volume", _volume_m3(*planning[sku]) if sku in planning else None), (STORAGE_FEES[1] + " item volume", _volume_m3(*storage[asin]) if asin in storage else None), (FEE_ESTIMATES[1] + " dimensions", _sides_m3(fees[sku][:3], fees[sku][3]) if sku in fees else None), (CATALOG[1] + " dimensions", _catalog_m3(catalog[asin][0]) if asin in catalog else None), ) source, volume = next(((s, v) for s, v in found if v), (None, None)) if volume: out[sku] = [round(volume, 9), source] return out def cached_volumes(st): """The per-unit volumes the last full sync read, so a one-SKU sync needs no mirror.""" doc = st.get(VOLUMES_KEY) return dict((doc or {}).get("skus") or {}) if isinstance(doc, dict) else {} def _save_volumes(st, volumes): stamp = dt.datetime.now(dt.timezone.utc).isoformat(timespec="seconds") st.update(VOLUMES_KEY, lambda _doc: {"skus": volumes, "readAt": stamp}) # ---------------------------------------------------------------- the incomplete set (pure) def entered_unit_volume(row): """Cubic meters per unit from a typed Carton CBM over Units per carton, or None.""" cbm, units = _positive(row.get("carton_cbm")), _positive(row.get("units_per_carton")) return cbm / units if cbm and units else None def gaps(row, volume=None): """The gap keys a SKU row has: link, units, Linked status, unit volume and cost.""" have = { "odoo_product": bool(str(row.get("odoo_product") or "").strip()), "units_per_sku": _positive(row.get("units_per_sku")) is not None, "match_state": str(row.get("match_state") or "").strip() == A.LINKED, "volume": bool(volume) or entered_unit_volume(row) is not None, "cost": _number(row.get("cogs_per_unit")) is not None or _number(row.get("amazon_cogs")) is not None, } return [key for key, _word in GAPS if not have[key]] def missing_words(keys): """One plain line naming each gap, or "".""" words = [word for key, word in GAPS if key in keys] line = ", ".join(words) return line[:1].upper() + line[1:] if line else "" def carton(row, volume=None): """`(carton CBM cell, source words)`: the typed value, else Amazon per-unit volume times units per carton.""" typed = _positive(row.get("carton_cbm")) if typed is not None: return _text(typed, 6), ENTERED if not volume: return None, "No Amazon volume. Enter Carton CBM." per_unit, source = volume units = _positive(row.get("units_per_carton")) if units is None: return None, f"{source}: {_text(per_unit, 6)} per unit. Enter units per carton." return _text(per_unit * units, 6), f"{source} times {_text(units, 4)} units" def entry_cells(row, volume=None): """The Data Entry cells for one SKU row, as stored strings; `missing` is "" when complete.""" cbm, source = carton(row, volume) cells = {key: (str(row.get(key)).strip() or None) if row.get(key) is not None else None for key in FIELD_KEYS if key not in ("missing", "carton_cbm", "cbm_source")} cells.update(sku=str(row.get("sku") or "").strip(), missing=missing_words(gaps(row, volume)), carton_cbm=cbm, cbm_source=source) return cells # ---------------------------------------------------------------- the table def specs(): return [{"key": TABLE_KEY, "label": TABLE_LABEL, "sources": [A.TABLE_KEY], "fields": A._definition_fields(FIELDS, ENTRY_KEYS)}] def _mirror_edit_grants(st): """Give each editor of the SKU table edit here too; the write-back still checks the SKU table.""" from core import shares want = [e for e in shares.grants("database", A.TABLE_KEY, st=st)["entries"] if e.get("role") == "edit"] if not want: return held = shares.grants("database", TABLE_KEY, st=st) have = {(e.get("user"), e.get("role")) for e in held["entries"]} extra = [{"user": e["user"], "role": "edit"} for e in want if (e["user"], "edit") not in have] if extra: shares.set_grants("database", TABLE_KEY, held["entries"] + extra, owner=held["owner"] or WRITER, st=st) def ensure(st, username=""): """Create or adopt the Data Entry table, its seeded view and its editors. Returns the key.""" module_tables.ensure_module_table(st, username, specs(), owner=WRITER, error=A.TableRefused) _mirror_edit_grants(st) owner, _label = A.default_owner(st) A.ensure_views(st, owner or username, table_key=TABLE_KEY, template_key=TEMPLATE_KEY) return TABLE_KEY def table_rows(st): return A.table_rows(st, TABLE_KEY) def _drop(st, keep, *, scope=None): """Remove rows whose SKU is not in `keep` (within `scope` when given) and duplicate SKU rows.""" rows = table_rows(st) seen, dropped = set(), [] for rid in sorted(rows, key=A._rid_order): sku = str(rows[rid].get("sku") or "").strip() if scope is not None and sku not in scope: continue if sku not in keep or sku in seen: dropped.append(rid) seen.add(sku) if not dropped: return 0 def _remove(cur): table = cur.get(TABLE_KEY) if table is None: return cur for rid in dropped: (table.get("rows") or {}).pop(rid, None) (table.get("_rowCreationOrder") or {}).pop(rid, None) (table.get(_registry.CELL_NOTES_KEY) or {}).pop(rid, None) return cur _registry.update_with_activity(TABLE_KEY, _remove, st=st, by=WRITER, via=VIA, row_ids=dropped, flush="sync") return len(dropped) def sync(st, con=None, *, skus=None, by=""): """Write one row per incomplete SKU and remove complete ones. Returns the counts. With `skus` it rebuilds those SKUs only, on cached volumes, and never creates the table.""" import core.user_tables as ut if skus is not None: if ut.get_one(TABLE_KEY, st) is None: return {"skipped": "Data Entry does not exist yet."} volumes = cached_volumes(st) scope = {str(s or "").strip() for s in skus} else: ensure(st, by) amazon_inputs.seed_fba_capacity(st) volumes = unit_volumes(con, A.table_rows(st)) if con is not None else cached_volumes(st) if con is not None: _save_volumes(st, volumes) scope = None computed = {} for row in A.table_rows(st).values(): sku = str(row.get("sku") or "").strip() if not sku or (scope is not None and sku not in scope): continue cells = entry_cells(row, volumes.get(sku)) if cells["missing"]: computed[sku] = cells written = module_tables.write_module_rows(st, TABLE_KEY, computed, key_column="sku", writer=WRITER, via=VIA, label=TABLE_LABEL, origin="Command Center", error=A.TableRefused) removed = _drop(st, set(computed), scope=scope) return dict(written, removed=removed, rows=len(table_rows(st)), volumes=len(volumes)) # ---------------------------------------------------------------- write-back def entry_value(field, value): """The SKU table cell a Data Entry edit writes, or None to clear it.""" text = "" if value is None else str(value).strip() if field in ("odoo_product", "carton_size"): return text or None if field == "match_state": state = A.LEGACY_MATCH_STATES.get(text, text) return state if state in A.MATCH_STATES else None if field == "units_per_sku": return A.units_text(text) if _positive(text) else None if field == "units_per_carton": number = _positive(text) return _text(number, 4) if number else None if field == "carton_cbm": number = _positive(text) return _text(number, 6) if number else None if field == "amazon_cogs": number = _number(text) return f"{number:.2f}" if number is not None and number >= 0 else None raise ValueError(f"{field} is not a Data Entry column.") def may_write_back(st, user): """An Administrator or an editor of the SKU table; a Data Entry grant alone never writes back.""" import core.user_tables as ut try: record = (st.get("users") or {}).get(str(user or "")) or {} except Exception: # noqa: BLE001 record = {} admin = isinstance(record, dict) and str(record.get("role") or "").lower() == "admin" return ut.may_edit_database(A.TABLE_KEY, str(user or ""), is_admin=admin, st=st) def write_back(st, row_id, field, value, *, user=""): """Write one Data Entry edit to the SKU table through `patch_cells` with `by=WRITER`. `patch_cells` emits no row event, so this never re-enters a hook. A typed Odoo product also sets Linked. Returns `{sku, written, changes}`; `sku` is None for an unknown row.""" import core.user_tables as ut entry = table_rows(st).get(str(row_id)) or {} sku = str(entry.get("sku") or "").strip() if not sku or field not in ENTRY_KEYS: return {"sku": sku or None, "written": False, "changes": {}} target = next((rid for rid, row in A.table_rows(st).items() if str(row.get("sku") or "").strip() == sku), None) if target is None or not may_write_back(st, user): return {"sku": sku, "written": False, "changes": {}} changes = {field: entry_value(field, value)} if field == "odoo_product" and changes[field]: changes["match_state"] = A.LINKED written = ut.patch_cells(A.TABLE_KEY, target, changes, st, by=WRITER, via=VIA) return {"sku": sku, "written": bool(written), "changes": changes} # ---------------------------------------------------------------- validation (rule 8) def validate(st): """Data Entry's row count ties to an independent count of incomplete SKU rows.""" volumes = cached_volumes(st) incomplete = set() for row in A.table_rows(st).values(): sku = str(row.get("sku") or "").strip() units = _positive(row.get("units_per_sku")) volume = volumes.get(sku) or (_positive(row.get("carton_cbm")) and _positive(row.get("units_per_carton"))) cost = row.get("cogs_per_unit") not in (None, "") or row.get("amazon_cogs") not in (None, "") if sku and not (str(row.get("odoo_product") or "").strip() and units and volume and cost and str(row.get("match_state") or "").strip() == A.LINKED): incomplete.add(sku) listed = [str(r.get("sku") or "").strip() for r in table_rows(st).values()] return [{"check": "Data Entry lists each incomplete SKU once", "ok": sorted(listed) == sorted(incomplete), "rows": len(listed), "incomplete": len(incomplete)}]