_generate_mcp_tech_kjb_detail_excel.py
5.5 KB
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
# -*- coding: utf-8 -*-
"""从 MCP 明细 JSON 生成科技部老师业绩明细 Excel（2026-03）。
JSON 须由「对齐日报落月」的 SQL 生成，见同目录 mcp_tech_kjb_detail_aligned_202603.sql：
开单：按 lq_kd_kdjlb.kdrq 落在统计月（不再仅用 lq_kd_kjbsyj.yjsj 筛月）；
退卡：按 lq_hytk_hytk.tksj 落在统计月（不再仅用 lq_hytk_kjbsyj.tksj 筛月）。
品项仍默认 F_ItemCategory=科美（与历史 MCP 导出一致）。"""
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
DATA_JSON = ROOT / "_mcp_tech_kjb_detail_202603.json"
OUT_XLSX = ROOT / "2026年3月_科技部老师业绩明细_MCP.xlsx"
HEADERS = [
("bizType", "业务类型"),
("storeName", "门店名称"),
("teacherId", "科技部老师ID"),
("teacherName", "科技部老师姓名"),
("teacherAccount", "科技部老师账号"),
("itemName", "品项名称"),
("itemId", "品项ID"),
("itemCategory", "品项分类"),
("beautyType", "科美类型"),
("amount", "业绩金额"),
("bizTime", "业绩时间"),
("refBillingId", "关联单号"),
("billingItemLineId", "明细行ID"),
("laborCost", "手工费"),
("billingDate", "单据日期"),
]
def main():
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 只读查询，落月与日报科技部老师统计一致。"
"开单=lq_kd_kjbsyj 关联 lq_kd_kdjlb，科美、有效，按单据 kdrq 落在 2026-03；"
"业绩时间列 bizTime 仍为子表 yjsj，单据日期 billingDate 为 kdrq。"
"退卡=lq_hytk_kjbsyj 关联 lq_hytk_hytk，科美、有效，按主表 tksj 落在 2026-03；"
"bizTime 为子表 tksj，billingDate 为主表 tksj。"
"关联单号：开单 glkdbh，退卡 gltkbh；明细行：开单 F_kdpxid，退卡 F_CardReturn。"
"取数 SQL 模板：mcp_tech_kjb_detail_aligned_202603.sql"
)
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()