Spaces:
Running on CPU Upgrade
Running on CPU Upgrade
File size: 17,944 Bytes
8133df9 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 | """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)}]
|