Command: python -c "from pathlib import Path; p=Path(r'C:\\Users\\ash\\Documents\\ShopeeApi\\app\\db.py'); lines=p.read_text(encoding='utf-8',errors='replace').splitlines(); pats=['def list_ready_to_ship_for_arrange','def get_ready_to_ship_overview','instant_delivery_orders','Instant Delivery','instant delivery']; hits=[i for i,x in enumerate(lines) if any(q in x for q in pats)]; ranges=[];[(ranges.append((max(0,i-60),min(len(lines),i+220)))) for i in hits];seen=set();[(print('---',a+1,b,'---'),[print(f'{j+1}: {lines[j]}') for j in range(a,b) if j not in seen and not seen.add(j)]) for a,b in ranges]"
Directory: projects
Status: SUCCESS
Exit code: 0
Cancel Job Rerun Command Refresh
--- 6384 6663 ---
6384: items.append(item)
6385: return items
6386:
6387:
6388: def _get_ready_to_ship_overview_uncached() -> dict[str, Any]:
6389: """Operational front-page metrics for the active packing window.
6390:
6391: Include both READY_TO_SHIP and PROCESSED so the dashboard does not go blank
6392: immediately after arranging shipment. Packing/arrange endpoints still use
6393: their own status-specific queries.
6394: """
6395: sid = _shop_id()
6396: with connect() as conn:
6397: row = conn.execute(
6398: """
6399: WITH item_qty AS (
6400: SELECT shop_id, order_sn, SUM(COALESCE(quantity, 0)) AS total_qty
6401: FROM order_items
6402: GROUP BY shop_id, order_sn
6403: )
6404: SELECT COUNT(*) AS order_count,
6405: COALESCE(SUM(COALESCE(o.total_amount, 0)), 0) AS total_amount,
6406: COALESCE(SUM(COALESCE(item_qty.total_qty, o.item_count, 0)), 0) AS product_quantity,
6407: COALESCE(MAX(o.currency), '') AS currency,
6408: MIN(o.create_time) AS oldest_create_time,
6409: MIN(o.ship_by_date) AS earliest_ship_by_date
6410: FROM orders o
6411: LEFT JOIN item_qty ON item_qty.shop_id=o.shop_id AND item_qty.order_sn=o.order_sn
6412: WHERE o.shop_id=? AND o.order_status IN ('READY_TO_SHIP','PROCESSED')
6413: """,
6414: (sid,),
6415: ).fetchone()
6416: orders = conn.execute(
6417: """
6418: WITH item_qty AS (
6419: SELECT shop_id, order_sn, SUM(COALESCE(quantity, 0)) AS total_qty
6420: FROM order_items
6421: GROUP BY shop_id, order_sn
6422: )
6423: SELECT o.order_sn, o.order_status, o.create_time, o.update_time, o.ship_by_date,
6424: o.total_amount, o.currency, o.buyer_username, o.payment_method,
6425: o.checkout_shipping_carrier, o.shipping_carrier,
6426: COALESCE(item_qty.total_qty, o.item_count, 0) AS product_quantity,
6427: o.item_count, o.package_count, o.has_detail, o.escrow_synced
6428: FROM orders o
6429: LEFT JOIN item_qty ON item_qty.shop_id=o.shop_id AND item_qty.order_sn=o.order_sn
6430: WHERE o.shop_id=? AND o.order_status IN ('READY_TO_SHIP','PROCESSED')
6431: ORDER BY COALESCE(o.create_time, o.update_time, 0) ASC, o.order_sn ASC
6432: LIMIT ?
6433: """,
6434: (sid, settings.ready_queue_limit),
6435: ).fetchall()
6436: status_counts = {
6437: str(x["order_status"]): int(x["n"] or 0)
6438: for x in conn.execute(
6439: """SELECT order_status,COUNT(*) n FROM orders
6440: WHERE shop_id=? AND order_status IN ('READY_TO_SHIP','PROCESSED')
6441: GROUP BY order_status""", (sid,)
6442: ).fetchall()
6443: }
6444: instant_delivery_orders = conn.execute(
6445: """
6446: WITH item_qty AS (
6447: SELECT shop_id, order_sn, SUM(COALESCE(quantity, 0)) AS total_qty
6448: FROM order_items
6449: GROUP BY shop_id, order_sn
6450: )
6451: SELECT o.order_sn, o.order_status, o.create_time, o.update_time, o.ship_by_date,
6452: o.buyer_username, o.checkout_shipping_carrier, o.shipping_carrier,
6453: COALESCE(item_qty.total_qty, o.item_count, 0) AS product_quantity
6454: FROM orders o
6455: LEFT JOIN item_qty ON item_qty.shop_id=o.shop_id AND item_qty.order_sn=o.order_sn
6456: WHERE o.shop_id=?
6457: AND o.order_status IN ('READY_TO_SHIP', 'PROCESSED')
6458: AND (
6459: LOWER(COALESCE(o.shipping_carrier, '')) LIKE '%instant delivery%'
6460: OR LOWER(COALESCE(o.checkout_shipping_carrier, '')) LIKE '%instant delivery%'
6461: )
6462: ORDER BY CASE WHEN o.order_status='READY_TO_SHIP' THEN 0 ELSE 1 END,
6463: COALESCE(o.create_time, o.update_time, 0) ASC,
6464: o.order_sn ASC
6465: LIMIT 100
6466: """,
6467: (sid,),
6468: ).fetchall()
6469: return {
6470: "status": "READY_TO_SHIP_AND_PROCESSED",
6471: "order_count": int(row["order_count"] or 0) if row else 0,
6472: "ready_order_count": int(status_counts.get("READY_TO_SHIP", 0)),
6473: "processed_order_count": int(status_counts.get("PROCESSED", 0)),
6474: "total_amount": float(row["total_amount"] or 0) if row else 0.0,
6475: "product_quantity": int(row["product_quantity"] or 0) if row else 0,
6476: "currency": row["currency"] if row else "",
6477: "oldest_create_time": row["oldest_create_time"] if row else None,
6478: "earliest_ship_by_date": row["earliest_ship_by_date"] if row else None,
6479: "orders": [dict(x) for x in orders],
6480: "instant_delivery_count": len(instant_delivery_orders),
6481: "instant_delivery_orders": [dict(x) for x in instant_delivery_orders],
6482: "cache_seconds": settings.dashboard_cache_seconds if settings.dashboard_cache_enabled else 0,
6483: }
6484:
6485:
6486: def get_ready_to_ship_overview() -> dict[str, Any]:
6487: return _cached(f"ready:overview:{_shop_id()}", settings.dashboard_cache_seconds, _get_ready_to_ship_overview_uncached)
6488:
6489:
6490: def _normalize_size_label(model_name: str | None, item_name: str | None = None) -> str:
6491: # For packing PRINT (DATA), follow Spread Data: 10ml tester is printed as 10, not 10T.
6492: _listing, size_code, _lookup = _spread_clean_listing_and_size(item_name or "", model_name or "")
6493: if size_code and size_code != "-":
6494: return size_code
6495: raw = f"{model_name or ''} {item_name or ''}".lower()
6496: if '35' in raw:
6497: return '35'
6498: if '10' in raw:
6499: return '10'
6500: if '5ml' in raw or '5 ml' in raw:
6501: return '5'
6502: return (model_name or '').strip() or '-'
6503:
6504:
6505: def _compact_order_list(order_sns: str | None, max_items: int = 12) -> dict[str, Any]:
6506: parts = [x for x in (order_sns or '').split('|') if x]
6507: shown = parts[:max_items]
6508: return {
6509: 'orders': shown,
6510: 'orders_text': ', '.join(shown),
6511: 'orders_hidden_count': max(0, len(parts) - len(shown)),
6512: 'order_total': len(parts),
6513: }
6514:
6515:
6516: def _get_ready_to_ship_packing_uncached() -> dict[str, Any]:
6517: """Spreadsheet-style PRINT (DATA) packing list for all READY_TO_SHIP orders.
6518:
6519: This mirrors the user's Spread Data.xlsx PRINT (DATA) sheet:
6520: Name | Size | Quantity | Location | Sticker.
6521: Shopee item titles are mapped to canonical perfume names with perfume_aliases.
6522: Volume/location/sticker come from perfume_storage when available.
6523: """
6524: sid = _shop_id()
6525: with connect() as conn:
6526: alias_rows = list(conn.execute("SELECT listing_name, listing_norm, canonical_name FROM perfume_aliases WHERE platform='SHOPEE'").fetchall())
6527: registry_maps = _load_perfume_registry_maps(conn, sid)
6528: stable_maps = _load_stable_product_mapping_maps(conn, sid)
6529: # Safety fallback: also load the bundled Diamond Perfume seed JSON directly.
6530: # This prevents old/partial database seed state from causing PRINT (DATA)
6531: # rows to show "Belum match Compare Name" after a rebuild order changes.
6532: for seed in _load_seed_json("perfume_aliases_seed.json"):
6533: if str(seed.get("platform") or "SHOPEE").upper() != "SHOPEE":
6534: continue
6535: listing = str(seed.get("listing_name") or "").strip()
6536: canonical = str(seed.get("canonical_name") or "").strip()
6537: if not listing or not canonical:
6538: continue
6539: alias_rows.append({
6540: "listing_name": listing,
6541: "listing_norm": str(seed.get("listing_norm") or _normalize_text_key(listing)).strip(),
6542: "canonical_name": canonical,
6543: })
6544: alias_maps = _build_perfume_alias_maps(alias_rows)
6545: registry_maps = _load_perfume_registry_maps(conn, sid)
6546: storage = _build_storage_lookup(
6547: conn.execute("SELECT canonical_name, volume, location, sticker, source FROM perfume_storage").fetchall()
6548: )
6549: ready_rows = conn.execute(
6550: """
6551: SELECT
6552: o.order_sn,
6553: o.create_time,
6554: o.total_amount,
6555: o.currency,
6556: oi.line_index,
6557: oi.item_id,
6558: oi.model_id,
6559: COALESCE(NULLIF(TRIM(oi.item_name),''),'Unknown item') AS item_name,
6560: COALESCE(NULLIF(TRIM(oi.model_name),''),'') AS model_name,
6561: COALESCE(NULLIF(TRIM(oi.item_sku),''), NULLIF(TRIM(oi.model_sku),''), '') AS sku,
6562: COALESCE(oi.quantity,0) AS quantity,
6563: COALESCE(oi.discounted_price, oi.original_price, 0) AS unit_price
6564: FROM orders o
6565: LEFT JOIN order_items oi ON oi.shop_id=o.shop_id AND oi.order_sn=o.order_sn
6566: WHERE o.shop_id=? AND o.order_status='READY_TO_SHIP'
6567: ORDER BY COALESCE(o.create_time, o.update_time, 0) ASC, o.order_sn ASC, oi.line_index ASC
6568: """,
6569: (sid,),
6570: ).fetchall()
6571: missing_detail = conn.execute(
6572: """
6573: SELECT COUNT(*) FROM orders o
6574: WHERE o.shop_id=? AND o.order_status='READY_TO_SHIP'
6575: AND NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.shop_id=o.shop_id AND oi.order_sn=o.order_sn)
6576: """,
6577: (sid,),
6578: ).fetchone()[0]
6579: summary = conn.execute(
6580: """
6581: SELECT COUNT(*) AS order_count,
6582: COALESCE(SUM(COALESCE(total_amount,0)),0) AS total_amount,
6583: COALESCE(MAX(currency),'') AS currency,
6584: MIN(create_time) AS oldest_create_time
6585: FROM orders
6586: WHERE shop_id=? AND order_status='READY_TO_SHIP'
6587: """,
6588: (sid,),
6589: ).fetchone()
6590:
6591: groups: dict[tuple[str, str], dict[str, Any]] = {}
6592: unmapped_count = 0
6593: total_qty = 0
6594: line_count = 0
6595: for row in ready_rows:
6596: d = dict(row)
6597: if not d.get("item_name") or d.get("line_index") is None:
6598: continue
6599: line_count += 1
6600: item_name = d.get("item_name") or "Unknown item"
6601: canonical, match_method, match_key = _match_perfume_alias(
6602: item_name, alias_maps, d.get("model_name") or "", registry_maps,
6603: item_id=d.get("item_id"), model_id=d.get("model_id"), stable_maps=stable_maps,
6604: )
6605: mapped = True
6606: if not canonical:
6607: # Fallback: keep the Shopee item title, but clearly mark it as unmapped.
6608: canonical = item_name
6609: mapped = False
6610: unmapped_count += 1
6611: size = _normalize_size_label(d.get("model_name"), item_name)
6612: qty = int(d.get("quantity") or 0)
6613: total_qty += qty
6614: key = (canonical, size)
6615: g = groups.setdefault(key, {
6616: "name": canonical,
6617: "canonical_name": canonical,
6618: "size": size,
6619: "quantity": 0,
6620: "order_sns": set(),
6621: "order_count": 0,
6622: "line_count": 0,
6623: "first_order_time": d.get("create_time"),
6624: "estimated_value": 0.0,
6625: "currency": d.get("currency") or (summary["currency"] if summary else ""),
6626: "mapped": mapped,
6627: "unmapped_lines": 0,
6628: "sample_listing": item_name,
6629: "sample_variation": d.get("model_name") or "",
6630: "sku": d.get("sku") or "",
6631: "match_method": match_method,
6632: "match_key": match_key,
6633: })
6634: g["quantity"] += qty
6635: g["order_sns"].add(d.get("order_sn"))
6636: g["line_count"] += 1
6637: g["estimated_value"] += qty * float(d.get("unit_price") or 0)
6638: if not mapped:
6639: g["mapped"] = False
6640: g["unmapped_lines"] += 1
6641: if d.get("create_time") and (not g.get("first_order_time") or d.get("create_time") < g.get("first_order_time")):
6642: g["first_order_time"] = d.get("create_time")
6643:
6644: items: list[dict[str, Any]] = []
6645: for i, g in enumerate(sorted(groups.values(), key=lambda x: (str(x["name"]).upper(), str(x["size"]))), start=1):
6646: srow = _find_storage_match(g.get("name"), storage) or {}
6647: orders_sorted = sorted([x for x in g.pop("order_sns") if x])
6648: g["row_no"] = i
6649: g["order_count"] = len(orders_sorted)
6650: g["orders"] = orders_sorted[:12]
6651: g["orders_text"] = ", ".join(orders_sorted[:12])
6652: g["orders_hidden_count"] = max(0, len(orders_sorted) - 12)
6653: g["volume"] = srow.get("volume") or ""
6654: g["location"] = srow.get("location") or ""
6655: g["sticker"] = srow.get("sticker") or ""
6656: g["storage_source"] = srow.get("source") or ""
6657: g["has_storage"] = bool(g["location"] or g["sticker"])
6658: items.append(g)
6659:
6660: return {
6661: "status": "READY_TO_SHIP",
6662: "mode": "spread_data_print_style",
6663: "columns": ["Name", "Size", "Quantity", "Location", "Sticker"],
--- 6399 6678 ---
6664: "order_count": int(summary["order_count"] or 0) if summary else 0,
6665: "product_quantity": int(total_qty or 0),
6666: "total_amount": float(summary["total_amount"] or 0) if summary else 0.0,
6667: "currency": summary["currency"] if summary else "",
6668: "oldest_create_time": summary["oldest_create_time"] if summary else None,
6669: "line_count": line_count,
6670: "missing_detail_orders": int(missing_detail or 0),
6671: "unmapped_count": int(unmapped_count or 0),
6672: "storage_missing_count": sum(1 for x in items if not x.get("has_storage")),
6673: "items": items,
6674: "cache_seconds": settings.dashboard_cache_seconds if settings.dashboard_cache_enabled else 0,
6675: "note": "Format packing ringkas: Name, Size, Quantity, Location, Sticker.",
6676: }
6677:
6678:
--- 6400 6679 ---
6679: def get_ready_to_ship_packing() -> dict[str, Any]:
--- 6420 6699 ---
6680: return _cached(f"ready:packing:{_shop_id()}", settings.dashboard_cache_seconds, _get_ready_to_ship_packing_uncached)
6681:
6682:
6683:
6684:
6685: # ---------------------------------------------------------------------------
6686: # Google Sheet resolver for the user's Spread Data formulas
6687: # ---------------------------------------------------------------------------
6688: # Source IDs were extracted from the user's public Spread Data formulas:
6689: # Compare Name (SHOPEE) -> 11IIeASqbDhKW7k9Sn-Mh0sijXJvKQwa0aG6Bkkok7fM
6690: # Stok/volume/location -> 1SXjMfiIBdSIsVhBMEtGbBfIr_MAsBSA163PyznhBCWE
6691: # Environment variables may override these if the user later changes files.
6692: GS_COMPARE_SHEET_ID = os.getenv("GS_COMPARE_SHEET_ID", "11IIeASqbDhKW7k9Sn-Mh0sijXJvKQwa0aG6Bkkok7fM")
6693: GS_STOCK_SHEET_ID = os.getenv("GS_STOCK_SHEET_ID", "1SXjMfiIBdSIsVhBMEtGbBfIr_MAsBSA163PyznhBCWE")
6694: GS_SPREAD_DATA_SHEET_ID = os.getenv("GS_SPREAD_DATA_SHEET_ID", "1HLArbP6TmebuVWtjaPSQv68tVtwcMV3ORF_gZ5ciLIc")
6695:
6696:
6697: def _packing_source_to_statuses(source: str) -> tuple[list[str], int | None, str]:
6698: src = (source or "ready").strip().lower()
6699: if src in {"processed", "arranged", "arranged-shipment"}:
--- 6421 6700 ---
6700: return ["PROCESSED"], 7, "PROCESSED"
--- 6426 6705 ---
6701: if src in {"recent", "test"}:
6702: return ["PROCESSED", "READY_TO_SHIP"], 7, "RECENT"
6703: return ["READY_TO_SHIP"], None, "READY_TO_SHIP"
6704:
6705:
--- 7671 7950 ---
7671: """,
7672: (sid, sid),
7673: ).fetchall()]
7674: orders_csv_path = workdir / "orders.csv"
7675: timeline_path = workdir / "orders_timeline.jsonl"
7676: manifest_path = workdir / "manifest.json"
7677: readme_path = workdir / "README.md"
7678:
7679: orders_csv_count = _export_orders_bundle_csv(orders_csv_path)
7680: timeline_count = _write_orders_timeline_jsonl(timeline_path, order_rows, bundle_maps)
7681:
7682: first_order = order_rows[0] if order_rows else {}
7683: last_order = order_rows[-1] if order_rows else {}
7684: manifest = {
7685: "export_type": "orders_bundle",
7686: "shop_id": sid,
7687: "generated_at": generated_at,
7688: "order_count": timeline_count,
7689: "first_order_create_time": first_order.get("create_time"),
7690: "first_order_create_time_iso": _timestamp_iso(first_order.get("create_time")),
7691: "last_order_create_time": last_order.get("create_time"),
7692: "last_order_create_time_iso": _timestamp_iso(last_order.get("create_time")),
7693: "item_count": bundle_maps["item_count"],
7694: "package_count": bundle_maps["package_count"],
7695: "escrow_count": bundle_maps["escrow_count"],
7696: "logistics_count": bundle_maps["logistics_count"],
7697: "error_count": bundle_maps["error_count"],
7698: "orders_csv_rows": orders_csv_count,
7699: "files": [
7700: "README.md",
7701: "manifest.json",
7702: "orders.csv",
7703: "orders_timeline.jsonl",
7704: ],
7705: "notes": [
7706: "orders_timeline.jsonl disusun ikut create_time (fallback update_time) dari paling awal ke paling terkini.",
7707: "Setiap order mengandungi _related.items, _related.packages, _related.escrow_detail, _related.logistics dan _related.sync_errors.",
7708: ],
7709: }
7710: readme_path.write_text(
7711: "# Shopee Order Bundle\n\n"
7712: f"Shop ID: {sid}\n"
7713: f"Generated at: {generated_at}\n\n"
7714: "Files:\n"
7715: "- `orders_timeline.jsonl`: satu JSON per order, disusun ikut masa dari awal ke terkini.\n"
7716: "- `orders.csv`: ringkasan order yang mudah dibaca di spreadsheet.\n"
7717: "- `manifest.json`: metadata, count, dan julat tarikh export.\n\n"
7718: "Setiap record dalam `orders_timeline.jsonl` mempunyai `_related` untuk items, packages,\n"
7719: "escrow detail, logistics, dan sync errors yang berkaitan.\n",
7720: encoding="utf-8",
7721: )
7722: manifest_path.write_text(json.dumps(manifest, ensure_ascii=False, indent=2), encoding="utf-8")
7723:
7724: with zipfile.ZipFile(temp, "w", compression=zipfile.ZIP_DEFLATED, compresslevel=6) as archive:
7725: for name in ("README.md", "manifest.json", "orders.csv", "orders_timeline.jsonl"):
7726: archive.write(workdir / name, arcname=name)
7727: temp.replace(destination)
7728: return manifest
7729:
7730:
7731: def list_ready_to_ship_for_arrange(limit: int = 100) -> list[dict[str, Any]]:
7732: """READY_TO_SHIP orders with package numbers, sorted by order masuk.
7733:
7734: Used by the guarded batch arrange-shipment action.
7735: """
7736: with connect() as conn:
7737: rows = conn.execute(
7738: """
7739: SELECT o.order_sn, o.create_time, o.update_time, o.buyer_username, o.total_amount, o.currency,
7740: o.shipping_carrier, o.checkout_shipping_carrier,
7741: GROUP_CONCAT(NULLIF(p.package_number,'')) AS package_numbers
7742: FROM orders o
7743: LEFT JOIN order_packages p ON p.shop_id=o.shop_id AND p.order_sn=o.order_sn
7744: WHERE o.shop_id=? AND o.order_status='READY_TO_SHIP'
7745: GROUP BY o.order_sn
7746: ORDER BY COALESCE(o.create_time, o.update_time, 0) ASC, o.order_sn ASC
7747: LIMIT ?
7748: """,
7749: (_shop_id(), int(limit)),
7750: ).fetchall()
7751: items: list[dict[str, Any]] = []
7752: for row in rows:
7753: d = dict(row)
7754: packages = [x for x in str(d.get("package_numbers") or "").split(",") if x and x.lower() != "none"]
7755: d["packages"] = packages or [None]
7756: items.append(d)
7757: return items
7758:
7759:
7760: def mark_order_processed_after_arrange(order_sn: str, status: str = "PROCESSED") -> None:
7761: """Local status update after Shopee ship_order succeeds.
7762:
7763: Next order sync will reconcile the final Shopee status. This is only used after
7764: successful write API response so the dashboard queue moves like Seller Centre.
7765: """
7766: now = utc_now_iso()
7767: sid = _shop_id()
7768: with _DB_LOCK, connect() as conn:
7769: conn.execute(
7770: "UPDATE orders SET order_status=?, synced_at=? WHERE shop_id=? AND order_sn=?",
7771: (status, now, sid, order_sn),
7772: )
7773: clear_cache("ready")
7774: clear_cache("summary")
7775: clear_cache("analytics")
7776:
7777:
7778: def list_orders_for_awb_data_info(source: str = "processed", limit: int = 5) -> list[dict[str, Any]]:
7779: """Pick recent orders for read-only AWB data-info testing.
7780:
7781: source=processed is useful after seller manually arranged shipment, while
7782: source=auto tries READY_TO_SHIP first then PROCESSED/SHIPPED.
7783: """
7784: source = (source or "processed").lower().strip()
7785: if source == "ready":
7786: status_groups = [["READY_TO_SHIP"], ["PROCESSED", "SHIPPED"]]
7787: elif source == "auto":
7788: status_groups = [["READY_TO_SHIP"], ["PROCESSED"], ["SHIPPED"]]
7789: elif source == "shipped":
7790: status_groups = [["SHIPPED"], ["PROCESSED"]]
7791: else:
7792: status_groups = [["PROCESSED"], ["SHIPPED"], ["READY_TO_SHIP"]]
7793: limit = max(1, min(int(limit or 5), 20))
7794: sid = _shop_id()
7795: with connect() as conn:
7796: for statuses in status_groups:
7797: placeholders = ",".join("?" for _ in statuses)
7798: rows = conn.execute(
7799: f"""
7800: SELECT o.order_sn, o.order_status, o.update_time, o.create_time, o.buyer_username,
7801: o.shipping_carrier, o.checkout_shipping_carrier,
7802: GROUP_CONCAT(NULLIF(p.package_number,'')) AS package_numbers,
7803: COUNT(NULLIF(p.package_number,'')) AS package_count_detected
7804: FROM orders o
7805: LEFT JOIN order_packages p ON p.shop_id=o.shop_id AND p.order_sn=o.order_sn
7806: WHERE o.shop_id=? AND o.order_status IN ({placeholders})
7807: GROUP BY o.order_sn
7808: ORDER BY COALESCE(o.update_time, o.create_time, 0) DESC, o.order_sn DESC
7809: LIMIT ?
7810: """,
7811: [sid, *statuses, limit],
7812: ).fetchall()
7813: if rows:
7814: items: list[dict[str, Any]] = []
7815: for row in rows:
7816: d = dict(row)
7817: packages = [x for x in str(d.get("package_numbers") or "").split(",") if x and x.lower() != "none"]
7818: d["packages"] = packages
7819: d["package_count_detected"] = len(packages)
7820: items.append(d)
7821: return items
7822: return []
7823:
7824:
7825: def canonical_name_is_registered(name: str, shop_id: int | None = None) -> bool:
7826: canonical = re.sub(r"\s+", " ", str(name or "").strip()).upper()
7827: if not canonical:
7828: return False
7829: sid = _shop_id(shop_id)
7830: with connect() as conn:
7831: row = conn.execute(
7832: """
7833: SELECT 1
7834: FROM (
7835: SELECT UPPER(TRIM(canonical_name)) AS canonical_name
7836: FROM canonical_products
7837: UNION ALL
7838: SELECT UPPER(TRIM(canonical_name))
7839: FROM perfume_name_registry
7840: WHERE shop_id IN (0, ?)
7841: UNION ALL
7842: SELECT UPPER(TRIM(canonical_name))
7843: FROM perfume_storage
7844: )
7845: WHERE canonical_name=?
7846: LIMIT 1
7847: """,
7848: (sid, canonical),
7849: ).fetchone()
7850: return bool(row)
7851:
7852:
7853: # ---------------------------------------------------------------------------
7854: # Ready stock (botol siap isi 35ml/10ml) � berasingan dari pati.
7855: # ---------------------------------------------------------------------------
7856:
7857:
7858: _READY_STOCK_MOVEMENT_TYPES = ("stock_in", "adjust", "release", "unrelease", "fulfill", "correction")
7859:
7860:
7861: def _ready_stock_row(conn: Any, shop_id: int, canonical_name: str, size_ml: int) -> dict[str, Any]:
7862: row = conn.execute(
7863: """SELECT shop_id,canonical_name,size_ml,on_hand,released,updated_at
7864: FROM ready_stock_items
7865: WHERE shop_id=? AND canonical_name=? AND size_ml=?""",
7866: (int(shop_id), str(canonical_name).strip().upper(), int(size_ml)),
7867: ).fetchone()
7868: if row:
7869: return dict(row)
7870: return {
7871: "shop_id": int(shop_id), "canonical_name": str(canonical_name).strip().upper(),
7872: "size_ml": int(size_ml), "on_hand": 0, "released": 0, "updated_at": "",
7873: }
7874:
7875:
7876: def ready_stock_apply_movement(
7877: *,
7878: shop_id: int,
7879: canonical_name: str,
7880: size_ml: int,
7881: movement_type: str,
7882: quantity_change: int,
7883: request_id: str,
7884: order_sn: str = "",
7885: note: str = "",
7886: device_id: str = "",
7887: actor: str = "",
7888: ) -> dict[str, Any]:
7889: """Apply a ready-stock movement atomically.
7890:
7891: movement_type:
7892: stock_in � scan masuk (on_hand += qty)
7893: adjust � tetap semula nilai on_hand (quantity_change = nilai baharu)
7894: release � tolak ke kedai (on_hand -= qty, released += qty)
7895: unrelease � tarik balik dari kedai (released -= qty, on_hand += qty)
7896: fulfill � dihantar guna ready stock (released -= qty)
7897: """
7898: sid = int(shop_id)
7899: canonical = str(canonical_name or "").strip().upper()
7900: size = int(size_ml)
7901: kind = str(movement_type or "").strip().lower()
7902: qty = int(quantity_change or 0)
7903: if kind not in _READY_STOCK_MOVEMENT_TYPES:
7904: raise ValueError(f"Jenis pergerakan ready stock tidak sah: {movement_type}")
7905: if not canonical:
7906: raise ValueError("canonical_name diperlukan")
7907: if size <= 0:
7908: raise ValueError("size_ml mesti lebih besar daripada 0")
7909: now = utc_now_iso()
7910: with _DB_LOCK, connect() as conn:
7911: existing = conn.execute(
7912: "SELECT 1 FROM ready_stock_movements WHERE request_id=?",
7913: (str(request_id),),
7914: ).fetchone()
7915: if existing:
7916: row = conn.execute(
7917: "SELECT * FROM ready_stock_movements WHERE request_id=?",
7918: (str(request_id),),
7919: ).fetchone()
7920: return {"ok": True, "duplicate": True, "movement": dict(row)}
7921: state = _ready_stock_row(conn, sid, canonical, size)
7922: on_hand = int(state.get("on_hand") or 0)
7923: released = int(state.get("released") or 0)
7924: if kind == "stock_in":
7925: if qty <= 0:
7926: raise ValueError("Kuantiti mestilah positif untuk stok masuk")
7927: on_hand += qty
7928: elif kind == "adjust":
7929: if qty < 0:
7930: raise ValueError("Nilai on_hand tidak boleh negatif")
7931: on_hand = qty
7932: elif kind == "release":
7933: if qty <= 0:
7934: raise ValueError("Kuantiti mestilah positif untuk tolakan")
7935: if qty > on_hand:
7936: raise ValueError(f"On-hand tidak cukup: ada {on_hand}, mahu tolak {qty}")
7937: on_hand -= qty
7938: released += qty
7939: elif kind == "unrelease":
7940: if qty <= 0:
7941: raise ValueError("Kuantiti mestilah positif")
7942: if qty > released:
7943: raise ValueError(f"Released tidak cukup: ada {released}, mahu tarik {qty}")
7944: released -= qty
7945: on_hand += qty
7946: elif kind == "fulfill":
7947: if qty <= 0:
7948: raise ValueError("Kuantiti mestilah positif untuk pemenuhan")
7949: if qty > released:
7950: raise ValueError(f"Released tidak cukup: ada {released}, mahu guna {qty}")