ExportFiles/_generate_mcp_tech_kjb_detail_excel.py

# -*- coding: utf-8 -*-
"""从 MCP 明细 JSON 生成科技部老师业绩明细 Excel。
JSON 须由「对齐日报落月」的 SQL 生成，见同目录 mcp_tech_kjb_detail_aligned_YYYYMM.sql。
默认（无参数）：沿用 2026-03 输入/输出文件名，兼容旧用法。
示例（4 月）：
  python3 ExportFiles/_generate_mcp_tech_kjb_detail_excel.py \\
    --json ExportFiles/_mcp_tech_kjb_detail_202604.json \\
    --out ExportFiles/2026年4月_科技部老师业绩明细_MCP.xlsx \\
    --month 2026-04 --sql-ref mcp_tech_kjb_detail_aligned_202604.sql
"""
import argparse
import json
from pathlib import Path
from openpyxl import Workbook
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter
ROOT = Path(__file__).resolve().parent
HEADERS = [
    ("bizType", "业务类型"),
    ("storeName", "门店名称"),
    ("teacherId", "科技部老师ID"),
    ("teacherName", "科技部老师姓名"),
    ("teacherAccount", "科技部老师账号"),
    ("itemName", "品项名称"),
    ("itemId", "品项ID"),
    ("itemCategory", "品项分类"),
    ("beautyType", "科美类型"),
    ("amount", "业绩金额"),
    ("bizTime", "业绩时间"),
    ("refBillingId", "关联单号"),
    ("billingItemLineId", "明细行ID"),
    ("laborCost", "手工费"),
    ("billingDate", "单据日期"),
]
def parse_args():
    p = argparse.ArgumentParser(description="从 MCP JSON 生成科技部老师业绩明细 Excel")
    p.add_argument(
        "--json",
        type=Path,
        default=ROOT / "_mcp_tech_kjb_detail_202603.json",
        dest="data_json",
        help="合并后的 MCP 明细 JSON（数组）",
    )
    p.add_argument(
        "--out",
        type=Path,
        default=ROOT / "2026年3月_科技部老师业绩明细_MCP.xlsx",
        help="输出 xlsx 路径",
    )
    p.add_argument(
        "--month",
        default="2026-03",
        help="统计月（写入底部说明，如 2026-04）",
    )
    p.add_argument(
        "--sql-ref",
        default="mcp_tech_kjb_detail_aligned_202603.sql",
        help="底部说明中引用的 SQL 文件名",
    )
    return p.parse_args()
def main():
    args = parse_args()
    data_json = args.data_json
    out_xlsx = args.out
    month = args.month
    sql_ref = args.sql_ref
    with open(data_json, "r", encoding="utf-8") as f:
        rows = json.load(f)
    wb = Workbook()
    ws = wb.active
    ws.title = "科技部老师业绩明细"
    thin = Side(style="thin", color="FF000000")
    grid = Border(left=thin, right=thin, top=thin, bottom=thin)
    header_fill = PatternFill("solid", fgColor="FF4472C4")
    header_font = Font(bold=True, color="FFFFFFFF", size=10)
    header_align = Alignment(horizontal="center", vertical="center", wrap_text=True)
    total_fill = PatternFill("solid", fgColor="FFF2F2F2")
    total_font = Font(bold=True)
    for col, (_, title) in enumerate(HEADERS, 1):
        c = ws.cell(row=1, column=col, value=title)
        c.font = header_font
        c.fill = header_fill
        c.alignment = header_align
        c.border = grid
    keys = [k for k, _ in HEADERS]
    amount_col = keys.index("amount") + 1
    labor_col = keys.index("laborCost") + 1
    for r_i, r in enumerate(rows, start=2):
        for col, key in enumerate(keys, start=1):
            val = r.get(key)
            if val is None:
                val = ""
            elif key in ("amount", "laborCost"):
                val = float(val) if val != "" else 0
            c = ws.cell(row=r_i, column=col, value=val)
            c.border = grid
            if key in ("amount", "laborCost"):
                c.number_format = "#,##0.00"
                c.alignment = Alignment(horizontal="right", vertical="center")
            elif key in ("bizTime", "billingDate"):
                c.alignment = Alignment(horizontal="left", vertical="center")
            else:
                c.alignment = Alignment(horizontal="left", vertical="center", wrap_text=False)
    last_data = 1 + len(rows)
    total_row = last_data + 1
    amt_letter = get_column_letter(amount_col)
    lab_letter = get_column_letter(labor_col)
    ws.cell(row=total_row, column=1, value="合计").font = total_font
    ws.cell(row=total_row, column=1).fill = total_fill
    ws.cell(row=total_row, column=1).border = grid
    for col in range(2, len(HEADERS) + 1):
        ws.cell(row=total_row, column=col).fill = total_fill
        ws.cell(row=total_row, column=col).border = grid
    ws.cell(
        row=total_row,
        column=amount_col,
        value=f"=SUM({amt_letter}2:{amt_letter}{last_data})",
    )
    ws.cell(row=total_row, column=amount_col).number_format = "#,##0.00"
    ws.cell(row=total_row, column=amount_col).font = total_font
    ws.cell(row=total_row, column=amount_col).alignment = Alignment(horizontal="right")
    ws.cell(
        row=total_row,
        column=labor_col,
        value=f"=SUM({lab_letter}2:{lab_letter}{last_data})",
    )
    ws.cell(row=total_row, column=labor_col).number_format = "#,##0.00"
    ws.cell(row=total_row, column=labor_col).font = total_font
    ws.cell(row=total_row, column=labor_col).alignment = Alignment(horizontal="right")
    ws.freeze_panes = "A2"
    ws.auto_filter.ref = f"A1:{get_column_letter(len(HEADERS))}{last_data}"
    widths = [8, 16, 18, 12, 14, 22, 20, 8, 10, 12, 20, 20, 20, 10, 20]
    for i, w in enumerate(widths, 1):
        ws.column_dimensions[get_column_letter(i)].width = w
    note_row = total_row + 2
    note = (
        "数据来源：MCP 只读查询，落月与日报科技部老师统计一致。"
        f"开单=lq_kd_kjbsyj 关联 lq_kd_kdjlb，科美、有效，按单据 kdrq 落在 {month}；"
        "业绩时间列 bizTime 仍为子表 yjsj，单据日期 billingDate 为 kdrq。"
        f"退卡=lq_hytk_kjbsyj 关联 lq_hytk_hytk，科美、有效，按主表 tksj 落在 {month}；"
        "bizTime 为子表 tksj，billingDate 为主表 tksj。"
        "关联单号：开单 glkdbh，退卡 gltkbh；明细行：开单 F_kdpxid，退卡 F_CardReturn。"
        f"取数 SQL 模板：{sql_ref}"
    )
    ws.merge_cells(start_row=note_row, start_column=1, end_row=note_row + 1, end_column=len(HEADERS))
    nc = ws.cell(row=note_row, column=1, value=note)
    nc.font = Font(size=9, color="FF666666")
    nc.alignment = Alignment(wrap_text=True, vertical="top")
    wb.save(out_xlsx)
    print("Saved:", out_xlsx, "rows:", len(rows))
if __name__ == "__main__":
    main()