Spaces:
Running on CPU Upgrade
Running on CPU Upgrade
Download platform/modules/amazon_data_entry.py from fsanyoto/runloopable: direct link, hf CLI and curl.
- Browser
- Download file 17.9 kB
-
https://huggingface.co/spaces/fsanyoto/runloopable/resolve/main/platform/modules/amazon_data_entry.py
- Command line
-
hf download hf://spaces/fsanyoto/runloopable/platform/modules/amazon_data_entry.py
-
curl -L -o amazon_data_entry.py https://huggingface.co/spaces/fsanyoto/runloopable/resolve/main/platform/modules/amazon_data_entry.py
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)}] | |