_generate_mcp_tech_kjb_detail_excel.py
6.39 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
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
# -*- 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()