File size: 17,944 Bytes
b2aa446
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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)}]