Chiyao Inventory Sheets

Chiyao Inventory Sheets

Overview

Use this skill to turn Chiyao invoice export workbooks into the same folder and workbook structure as the historical handoff package:

• top-level month folder
• source copies named like 2026.01销项.xlsx and 2026.01进项.xlsx
• 出库单/ with one Excel per customer/date group
• 入库单/ with one Excel per supplier/date group

Prefer the bundled script for generation; it encodes the verified filtering, grouping, naming, and validation rules from the January 2026 handoff.

Inputs

Ask only if a required file is missing or ambiguous. Otherwise infer paths from the user's message and nearby examples.

Required inputs:

• --sample-zip: historical sample zip containing month folder, source workbooks, 出库单/, and 入库单/.
• --sales-src: current month 销项 workbook.
• --purchase-src: current month 进项 workbook.
• --output-dir: final folder to create.
• --month-label: output source workbook prefix, for example 2026.01.

The source workbooks must contain 信息汇总表. The script expects these columns:

• common: 数电发票号码, 开票日期, 税收分类编码, 货物或应税劳务名称, 规格型号, 单位, 数量, 发票状态, 是否正数发票
• sales/outbound: 购买方名称
• purchase/inbound: 销方名称

Generation Rules

Use the same business rules unless the user explicitly overrides them:

• 出库单 groups by 购买方名称 + 开票日期.
• 入库单 groups by 销方名称 + 开票日期.
• Same party on the same date is merged into one workbook, even when there are multiple invoices.
• Same party on different dates creates multiple files; add (2), (3) in generation order.
• Include only rows where 发票状态=正常, 是否正数发票=是, and 税收分类编码 starts with 103.
• Exclude red/negative invoices and non-inventory service categories such as electricity, service fees, rent, insurance, and payment fees.
• Strip invoice tax prefixes from item names, e.g. *方便食品*荷叶夹 becomes 荷叶夹.
• Preserve source values for specification, unit, and quantity instead of recalculating.

Output Format

Use templates from the sample zip. The generated single-party workbook should keep the sample layout:

• A1: 驰肴出货单 or 驰肴入货单
• A3: 日期: YYYY年M月D日
• A5: 客户：公司名称
• A10:D10: 项目名称 / 规格型号 / 单位 / 数量
• data begins at row 11

For source workbook copies, name them:

• {month-label}销项.xlsx
• {month-label}进项.xlsx

Script

Run:

python3 ~/.codex/skills/chiyao-inventory-sheets/scripts/generate_chiyao_inventory_sheets.py \
  --sample-zip "/path/to/2025年11月.zip" \
  --sales-src "/path/to/2026.01xiao销项.xlsx" \
  --purchase-src "/path/to/1月进项.xlsx" \
  --output-dir "/Users/fengjin/Desktop/2026一月份进库、出库单" \
  --month-label "2026.01"

Options:

• --overwrite-empty: allow using an existing empty output folder.
• --force: delete and recreate an existing output folder. Use only when the user explicitly asks to overwrite.

Prefer the bundled Python runtime from Codex workspace dependencies when available, because it includes openpyxl.

Validation

After generation, verify and report:

• output folder exists and contains the two source copies plus 出库单/ and 入库单/
• generated outbound and inbound file counts
• total checked .xlsx files and whether any failed to open
• at least 3 sampled outbound and 3 sampled inbound files: title, date, party, and populated detail-row count
• excluded row summary, grouped by status/positive flag/tax-code prefix

For the January 2026 source files used in the original run, expected counts were:

• 出库单: 65 workbooks, 58 customers
• 入库单: 13 workbooks, 10 suppliers