盤點單分析 · 開發規格

資料流、D1 schema、腳本、產物、URL 一覽

一、專案目的

每週把莫筆克匯出的盤點單灌進 D1，跨週累積後產出 dashboard + 週報，讓資訊部能：

  • 追蹤實體盤差（排除採購端帳務性沖銷）
  • 觀察建立者、廠商、原因細分的變化
  • 找出異常型態（大額短少、款式互串、批次帳修）
  • 累積到 4~6 週後有基礎做月度分析

主要目的是降低盤差失誤。單看一週看不出模式，跨週才能建立 baseline。

二、系統架構

莫筆克 Xsale ────► xlsx ────► @Tako
                                 │
                                 ▼
                 ┌──────────── loader.py ──────────┐
                 │                                  │
                 ▼                                  ▼
        D1 (batches/headers/lines)       Google Sheet（翠翠自己維護）
                 │
                 ▼
  ┌──────────────┼──────────────────────┐
  │              │                      │
  ▼              ▼                      ▼
build_dashboard.py     html-gen (each YAML)     build_report_wrapper.py
  │                    │                            │
  ▼                    ▼                            ▼
stocktake-        2026-Wxx-report.html         stocktake-report.html
dashboard.html                                  （iframe wrapper）
  │                    │                            │
  └────────────────────┼────────────────────────────┘
                       ▼
                   R2 keep (Cloudflare)
                       │
                       ▼
          qtracychen.net/c2c/projects/tracking/

三、資料流（每週）

  1. 翠翠從莫筆克匯 xlsx（該 ISO 週週一~週日、已結案、主倉、不分廠商）
  2. 翠翠 append 進 Google Sheet 新分頁（把 gid 傳給 @Tako）
  3. 翠翠傳 xlsx 給 @Tako
  4. @Tako 存 xlsx 到 ~/repo/notes/taco/stocktake/YYYY-Www.xlsx
  5. @Tako 跑 loader.py 灌 D1（自動擋重複 batch）
  6. @Tako 更新 D1 taco_stocktake_batches.sheet_gid
  7. @Tako 寫 YYYY-Www-report.yml 週報 YAML
  8. @Tako 跑三個 build：
    • html-gen 產週報 HTML → upload R2（timestamp URL）→ D1 taco_artifacts 更新
    • build_dashboard.py 產跨週 dashboard → upload R2（固定 slug 覆蓋）
    • build_report_wrapper.py 產週報 wrapper → upload R2（固定 slug 覆蓋）
  9. @Tako commit + push；給翠翠兩個固定 URL

四、D1 Schema

taco_stocktake_batches

每週一筆，記錄該週的原始匯出資料。

欄位 型別 說明
batch TEXT PK 例：2026-W33
source_file TEXT 例：2026-W33.xlsx
date_range TEXT 例：2026-08-10..2026-08-17
header_count / line_count INT 該 batch 的 headers/lines 筆數
sheet_gid TEXT Google Sheet 分頁 gid，dashboard「原始資料」連結用

taco_stocktake_headers

每張盤點單一筆。

主要欄位：stocktake_no (UNIQUE) / created_at / status / header_note / total_over / total_short / creator / warehouse / import_batch

taco_stocktake_lines

每張盤點單的每個明細品項一筆（一張單可有多個明細）。

主要欄位：stocktake_no (FK) / product_code / product_name / style / size / count_qty / diff / vendor / stock_cost / misc_cost / sales_cost / import_batch

成本欄位選用：目前用 stock_cost（存貨成本）算短少金額。其他 3 欄（vendor_price / misc_cost / sales_cost）目前未使用。若之後要改，需重跑所有週報表。

taco_stocktake_creator_roles

建立者角色 + 姓名對照。

欄位 型別 說明
creator TEXT PK (COLLATE NOCASE) 帳號名，例 yen / seiko01
role TEXT 'purchasing' 會被分析排除；其他值（staff）正常計入
display_name TEXT 中文姓名，dashboard 建立者 table 顯示用
note / added_at / added_by 輔助欄位

目前資料：

  • staff：yen（顏嘉辰）· seiko01（吳佳純）· cs008（陳卉妍）
  • purchasing：PO012（毛）· PO014（Sandy）· pinguwu · kate0515c

taco_artifacts

所有產物索引（跨專案共用），本專案用 project_slug='stocktake-weekly'。kind 包括：source / report-source / report / report-r2 / dashboard / dashboard-r2 / report-wrapper / report-wrapper-r2 / script / migration。

Migrations 位置

~/repo/db/migrations/0025_taco_stocktake.sql、0026_taco_stocktake_creator_roles.sql、0027_taco_stocktake_creator_display_name.sql、0028_taco_stocktake_batches_sheet_gid.sql

五、腳本

腳本 位置 用途
loader.py notes/taco/stocktake/ xlsx → D1（擋重複 batch）
build_dashboard.py notes/taco/stocktake/ 撈所有 batch → 跨週 dashboard HTML
build_report_wrapper.py notes/taco/stocktake/ 讀 D1 各週 report-r2 URL → iframe wrapper HTML
gen.mjs tools/html-gen/ YAML → 週報 HTML（family=report）
upload.js ~/.claude/skills/r2-upload/ R2 上傳，支援 --keep（永久）跟 --slug <name>（固定 URL）
d1.mjs db/ D1 SQL 執行器

排除採購 SQL pattern

LEFT JOIN taco_stocktake_creator_roles r
  ON r.creator = h.creator COLLATE NOCASE
WHERE (r.role IS NULL OR r.role != 'purchasing')

短少金額算法

short_cost = (-diff) * stock_cost （僅 diff < 0 時計算）

原因細分抽取（dashboard + 週報 通用）

first = header_note.split('\n')[0].strip()
first = re.sub(r'【總盤差】.*', '', first).strip()
first = re.sub(r'^\d{1,2}[/\-]\d{1,2}\s*', '', first).strip()
reason = first or '(空)'

六、產物與 URL

產物 檔案位置 R2 URL 更新方式
Dashboard（跨週切） notes/taco/stocktake/stocktake-dashboard.html https://r2.qtracychen.net/keep/stocktake-dashboard.html 固定 slug 覆蓋
週報 wrapper（iframe 切週） notes/taco/stocktake/stocktake-report.html https://r2.qtracychen.net/keep/stocktake-report.html 固定 slug 覆蓋
各週獨立週報 notes/taco/stocktake/2026-Wxx-report.html https://r2.qtracychen.net/keep/{ts}-{hash}-2026-wxx-report.html 每週新 timestamp URL
網頁入口 notes/taco/qtracychen-net/c2c/projects/tracking/index.html https://qtracychen.net/c2c/projects/tracking/ wrangler pages deploy

網頁入口 tracking 頁

掛兩張卡片：

  • Dashboard（跨週可切） → 固定 URL
  • 週報（跨週可切） → 固定 URL

URL 永遠不變，新增週次不用改 tracking 頁。

七、資料規則（重要）

  • 排除採購：role='purchasing' 的 4 個帳號一律排除，資料端直接濾掉、無 UI toggle。翠翠 2026-08-10 決議。
  • 成本用 stock_cost：短少金額 = 短少件數 × stock_cost，未定案。
  • subtitle 用 ISO 週：2026 年 Wxx（08／10～08／16），若匯出區間跨到下週要在 meta 補「匯出區間」。
  • 建立者 display_name：新 creator 上線前先加 role='staff' + display_name 進 D1，dashboard/週報建立者 table 才會顯示姓名。
  • Google Sheet gid：每週新加 tab 要把 gid 存進 taco_stocktake_batches.sheet_gid，dashboard 副標「原始資料」連結才能跳對分頁。

八、目前狀態

  • 已上線批次：W32（08/0108/09，19 張 35 明細）· W33（08/1008/17，21 張 65 明細）
  • 排除採購後合計：40 張 100 明細，短少 162 件、NT$ 35,731
  • 樣本仍太少，累到 4~6 週後可討論月度分析