loopable / platform /modules /amazon_data_entry.py
fsanyoto's picture
Deploy AIOS web (React glide grid + FastAPI slice)
b2aa446 verified
Raw History Blame
17.9 kB
"""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)}]