shishengliang/mileage-bonus
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
mileage-bonus/docs/superpowers/plans/2026-04-02-monthly-excel-redesign.md
kkfluous 46fa8aea7a docs: 按月拆分Excel实现计划 
Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
2026-04-02 14:09:51 +08:00

707 lines
27 KiB
Markdown
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
# 按月拆分Excel + 业务员/车辆维度 实现计划
> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking.
**Goal:** 将单文件Q1汇总重构为3个月度独立Excel，每个文件新增12个业务员sheet和1个车辆考核追踪sheet。
**Architecture:** 将现有 `generate_q1_summary.py` 拆分为 `calc_engine.py`（计算引擎，不变）+ `excel_writer.py`（Excel输出，含新sheet）+ `main.py`（入口，按月循环生成）。计算引擎从原脚本提取，Excel输出按月独立调用。
**Tech Stack:** Python 3, openpyxl
---
### Task 1: 提取计算引擎到 calc_engine.py
**Files:**
- Create: `calc_engine.py`
- Modify: `generate_q1_summary.py`（后续Task删除）
- [ ] **Step 1: 创建 calc_engine.py，提取规则/读取/分组/计算逻辑**
从现有 `generate_q1_summary.py` 第1-222行提取为独立模块，封装为函数：
```python
# calc_engine.py
"""计算引擎：规则、读取、分组、结转/补发/累计逻辑"""
import openpyxl
from collections import defaultdict
RULES = {
'交投40辆4.5T普货': {'km': 3000, '奖': 150},
'交投190辆4.5T冷链车': {'km': 3000, '奖': 150},
'羚牛136辆4.5T冷链车': {'km': 5000, '奖': 260},
'恒运50辆4.5T普货': {'km': 5000, '奖': 260},
'恒运50辆4.5T 普货': {'km': 5000, '奖': 260},
'羚牛100辆18T': {'km': 6000, '奖': 1000},
}
DAYS = {1: 31, 2: 28, 3: 31}
def read_file(fp, month):
"""读取月度源数据，返回记录列表"""
wb = openpyxl.load_workbook(fp, data_only=True)
ws = wb['业务考核视图']
h = [c.value for c in next(ws.iter_rows(min_row=1, max_row=1))]
recs = []
for row in ws.iter_rows(min_row=2, values_only=True):
r = dict(zip(h, row))
r['考核天数'] = float(r.get('考核天数') or 0)
r['应考核里程(km)'] = float(r.get('应考核里程(km)') or 0)
r['实际行驶里程(km)'] = float(r.get('实际行驶里程(km)') or 0)
r['月份'] = month
rule = RULES.get(r.get('考核目标', ''), {})
r['月度目标里程'] = rule.get('km', 0)
r['对应月奖励金额'] = rule.get('奖', 0)
t, a = r['应考核里程(km)'], r['实际行驶里程(km)']
q = a >= t and t > 0
r['是否达标'] = '达标' if q else '未达标'
r['奖金'] = r['对应月奖励金额'] * (r['考核天数'] / DAYS[month]) if q else 0
r['多跑'] = max(0, a - t) if q else 0
r['可结转'] = int(r['多跑'] // r['月度目标里程']) if r['月度目标里程'] > 0 and q else 0
recs.append(r)
wb.close()
return recs
def grp(recs, month):
"""按(车牌号,销售经理)分组"""
gs = defaultdict(lambda: {'recs':[], '应考核':0, '实际':0, '奖金':0, '天数':0,
'有达标':False, '目标km':0, '奖励额':0})
for r in recs:
k = (r['车牌号'], r['销售经理'])
g = gs[k]
g['recs'].append(r)
g['应考核'] += r['应考核里程(km)']
g['实际'] += r['实际行驶里程(km)']
g['奖金'] += r['奖金']
g['天数'] += r['考核天数']
if r['是否达标'] == '达标': g['有达标'] = True
g['目标km'] = max(g['目标km'], r['月度目标里程'])
g['奖励额'] = max(g['奖励额'], r['对应月奖励金额'])
g['部门'] = r['部门名称']
g['销售'] = r['销售经理']
g['车牌'] = r['车牌号']
return dict(gs)
def calc_jan_carryover(G1):
"""计算1月结转"""
for k, g in G1.items():
tc = 0
for r in g['recs']:
if r['是否达标'] == '达标':
tc += r['可结转']
g['可结转'] = tc
def calc_feb(G1, G2):
"""计算2月各类发放，返回feb_data字典"""
feb_data = {'结转':[], '补发1月':[], '当月':[], '累计补发2月':[]}
for k, g2 in G2.items():
g1 = G1.get(k)
bf = g2['奖励额']
j_t = g1['应考核'] if g1 else 0
j_a = g1['实际'] if g1 else 0
j_q = g1['有达标'] if g1 else False
j_c = g1['可结转'] if g1 else 0
cum_t = j_t + g2['应考核']
cum_a = j_a + g2['实际']
cum_q = cum_a >= cum_t and cum_t > 0
carry = 0
if g1 and j_q and j_c >= 1:
carry = bf
feb_data['结转'].append({'车牌':k[0], '销售':g1['销售'], '部门':g2['部门'], '额':carry})
bp1 = 0
if g1 and not j_q and cum_q:
bp1 = g1['奖励额'] * (g1['天数'] / 31)
feb_data['补发1月'].append({'车牌':k[0], '销售':g1['销售'], '部门':g2['部门'], '额':bp1})
bonus2 = 0
if g2['有达标'] and carry == 0:
bonus2 = g2['奖金']
feb_data['当月'].append({'车牌':k[0], '销售':g2['销售'], '部门':g2['部门'], '额':bonus2})
cbp2 = 0
if not g2['有达标'] and carry == 0 and cum_q:
cbp2 = g2['奖励额'] * (g2['天数'] / 28)
feb_data['累计补发2月'].append({'车牌':k[0], '销售':g2['销售'], '部门':g2['部门'], '额':cbp2})
g2['cum_t'] = cum_t; g2['cum_a'] = cum_a; g2['cum_q'] = cum_q
g2['结转'] = carry; g2['补发1月'] = bp1
g2['补发1月对应'] = g1['销售'] if g1 and bp1 > 0 else ''
g2['当月奖金'] = bonus2; g2['累计补发2月'] = cbp2
g2['结转占位'] = carry > 0
fc = sum(r['可结转'] for r in g2['recs'] if r['是否达标'] == '达标')
jr = max(0, j_c - (1 if carry > 0 else 0))
g2['可结转'] = fc + jr
g2['2月已发'] = carry > 0 or bonus2 > 0 or cbp2 > 0
g2['1月已补发'] = bp1 > 0
return feb_data
def calc_mar(G1, G2, G3, feb_data):
"""计算3月各类发放，返回mar_data字典"""
mar_data = {'结转':[], '补发1月':[], '补发2月':[], '当月':[], '累计补发3月':[]}
for k, g3 in G3.items():
g1 = G1.get(k); g2 = G2.get(k)
bf = g3['奖励额']
j_t = g1['应考核'] if g1 else 0; j_a = g1['实际'] if g1 else 0
j_q = g1['有达标'] if g1 else False
j_paid = (g1['奖金'] > 0) if g1 else False
f_t = g2['应考核'] if g2 else 0; f_a = g2['实际'] if g2 else 0
f_q = g2['有达标'] if g2 else False
f_paid = g2['2月已发'] if g2 else False
f_carry = g2['可结转'] if g2 else 0
if g1 and j_q: j_paid = True
if g2 and g2.get('1月已补发', False): j_paid = True
cum_t = j_t + f_t + g3['应考核']
cum_a = j_a + f_a + g3['实际']
cum_q = cum_a >= cum_t and cum_t > 0
carry = 0
if g2 and f_carry >= 1:
carry = bf
mar_data['结转'].append({'车牌':k[0], '销售':g2['销售'], '部门':g3['部门'], '额':carry})
bj = 0
if g1 and not j_paid and cum_q:
bj = g1['奖励额'] * (g1['天数'] / 31)
mar_data['补发1月'].append({'车牌':k[0], '销售':g1['销售'], '部门':g3['部门'], '额':bj})
bf2 = 0
if g2 and not f_paid and cum_q:
bf2 = g2['奖励额'] * (g2['天数'] / 28)
mar_data['补发2月'].append({'车牌':k[0], '销售':g2['销售'], '部门':g3['部门'], '额':bf2})
bonus3 = 0
if g3['有达标'] and carry == 0:
bonus3 = g3['奖金']
mar_data['当月'].append({'车牌':k[0], '销售':g3['销售'], '部门':g3['部门'], '额':bonus3})
cbp3 = 0
if not g3['有达标'] and carry == 0 and cum_q:
cbp3 = g3['奖励额'] * (g3['天数'] / 31)
mar_data['累计补发3月'].append({'车牌':k[0], '销售':g3['销售'], '部门':g3['部门'], '额':cbp3})
g3['cum_t'] = cum_t; g3['cum_a'] = cum_a; g3['cum_q'] = cum_q
g3['结转'] = carry; g3['补发1月'] = bj
g3['补发1月对应'] = g1['销售'] if g1 and bj>0 else ''
g3['补发2月'] = bf2; g3['补发2月对应'] = g2['销售'] if g2 and bf2>0 else ''
g3['当月奖金'] = bonus3; g3['累计补发3月'] = cbp3
g3['结转占位'] = carry > 0
return mar_data
def collect_vehicle_payments(G, month_data_map):
"""收集车辆发放记录，返回 {车牌: [发放记录]}"""
payments = defaultdict(list)
info = {}
# 1月当月达标
for k, g in G[1].items():
plate = k[0]
if not info.get(plate):
first = g['recs'][0]
info[plate] = {'考核目标': first.get('考核目标',''), '月度奖励': g['奖励额'], '目标km': g['目标km']}
if g['奖金'] > 0:
payments[plate].append({'结算月':1,'对应考核月':1,'业务员':g['销售'],'金额':g['奖金'],'类型':'当月达标','部门':g['部门']})
type_map = {
'结转': lambda m: (m, m),
'补发1月': lambda m: (m, 1),
'补发2月': lambda m: (m, 2),
'当月': lambda m: (m, m),
'累计补发2月': lambda m: (m, 2),
'累计补发3月': lambda m: (m, 3),
}
for month, mdata in month_data_map.items():
for cat, dl in mdata.items():
settle, assess_fn = month, type_map.get(cat, lambda m: (m, m))
for d in dl:
s, a = assess_fn(month)
payments[d['车牌']].append({'结算月':s,'对应考核月':a,'业务员':d['销售'],'金额':d['额'],
'类型':cat.replace(f'{month}月','').replace('累计补发','累计补发'),'部门':d['部门']})
if not info.get(d['车牌']):
info[d['车牌']] = {'考核目标':'','月度奖励':0,'目标km':0}
# 补充info
for m in G:
for k, g in G[m].items():
if not info.get(k[0]):
first = g['recs'][0]
info[k[0]] = {'考核目标': first.get('考核目标',''), '月度奖励': g['奖励额'], '目标km': g['目标km']}
# 编期数
for plate in payments:
records = sorted(payments[plate], key=lambda x: (x['对应考核月'], x['结算月']))
for i, r in enumerate(records):
r['期数'] = i + 1
return payments, info
def read_master_vehicles(filepath='里程任务考核_2 月核算.xlsx'):
"""读取全量车辆台账"""
wb = openpyxl.load_workbook(filepath, data_only=True)
ws = wb['考核车辆总览']
h = [c.value for c in next(ws.iter_rows(min_row=1, max_row=1))]
vehicles = []
for row in ws.iter_rows(min_row=2, values_only=True):
r = dict(zip(h, row))
if r.get('车牌号'):
vehicles.append(r)
wb.close()
return vehicles
```
- [ ] **Step 2: 验证 calc_engine.py 可导入**
Run: `cd /Users/kkfluous/Downloads && python3 -c "from calc_engine import *; print('OK')"`
Expected: `OK`
- [ ] **Step 3: Commit**
```bash
git add calc_engine.py
git commit -m "refactor: extract calc_engine.py from generate_q1_summary.py"
```
---
### Task 2: 创建 excel_writer.py（现有sheet输出函数）
**Files:**
- Create: `excel_writer.py`
- [ ] **Step 1: 创建 excel_writer.py，提取Excel工具函数和现有sheet写入逻辑**
```python
# excel_writer.py
"""Excel输出：工具函数 + 各类sheet生成"""
import openpyxl
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill
from collections import defaultdict
hf = Font(bold=True)
hfl = PatternFill(start_color='D9E1F2', end_color='D9E1F2', fill_type='solid')
bd = Border(left=Side(style='thin'),right=Side(style='thin'),top=Side(style='thin'),bottom=Side(style='thin'))
def WH(ws, headers, row=1):
for c, h in enumerate(headers, 1):
cl = ws.cell(row=row, column=c, value=h)
cl.font = hf; cl.fill = hfl; cl.border = bd
cl.alignment = Alignment(horizontal='center', wrap_text=True)
def WR(ws, rn, vals):
for c, v in enumerate(vals, 1):
cl = ws.cell(row=rn, column=c, value=v); cl.border = bd
def AW(ws):
for col in ws.columns:
ml = max((len(str(c.value or '')) for c in col), default=0)
ws.column_dimensions[col[0].column_letter].width = min(ml+3, 22)
def R(v, n=2):
return round(v, n) if isinstance(v, (int, float)) and v else v
def agg(dl):
bp = defaultdict(lambda: {'n':0, 'a':0})
for d in dl: bp[d['销售']]['n'] += 1; bp[d['销售']]['a'] += d['额']
return dict(bp)
def write_sec(ws, rn, title, dl):
"""写入汇总section，返回下一行行号"""
ws.cell(row=rn, column=1, value=title).font = Font(bold=True, size=11); rn+=1
WH(ws, ['销售人员','车辆数','金额'], rn); rn+=1
bp = agg(dl); tv=ta=0
for p in sorted(bp.keys()):
WR(ws, rn, [p, bp[p]['n'], R(bp[p]['a'])]); tv+=bp[p]['n']; ta+=bp[p]['a']; rn+=1
WR(ws, rn, ['总计', tv, R(ta)]); ws.cell(row=rn,column=1).font=hf; return rn+2
def write_total(ws, rn, month, all_data):
"""写入合计（按销售/按部门）"""
total_bp = defaultdict(lambda: {'部门':'', '额':0})
for cat, dl in all_data.items():
for d in dl: total_bp[d['销售']]['额'] += d['额']; total_bp[d['销售']]['部门'] = d['部门']
ws.cell(row=rn, column=1, value=f'{month}月合计应发奖励（按销售人员）').font=Font(bold=True, size=11); rn+=1
WH(ws, ['销售人员','部门名称','合计应发奖励'], rn); rn+=1
gt=0
for p in sorted(total_bp.keys()):
WR(ws, rn, [p, total_bp[p]['部门'], R(total_bp[p]['额'])]); gt+=total_bp[p]['额']; rn+=1
WR(ws, rn, ['合计','', R(gt)]); ws.cell(row=rn,column=1).font=hf; rn+=2
by_dept = defaultdict(float)
for p,d in total_bp.items(): by_dept[d['部门']] += d['额']
ws.cell(row=rn, column=1, value=f'{month}月合计应发奖励（按部门）').font=Font(bold=True, size=11); rn+=1
WH(ws, ['部门名称','合计应发奖励'], rn); rn+=1
for dept in sorted(by_dept.keys()): WR(ws, rn, [dept, R(by_dept[dept])]); rn+=1
WR(ws, rn, ['合计', R(gt)]); ws.cell(row=rn,column=1).font=hf
# --- 各sheet生成函数 ---
# write_rules_sheet(wb) - 考核奖励规则
# write_detail_sheet(wb, records, month) - 里程明细
# write_calc_process_sheet(wb, G, month, ...) - 计算过程
# write_summary_sheet(wb, month, month_data) - 月汇总
# write_salesperson_sheet(wb, person, ...) - 业务员sheet（新增）
# write_vehicle_tracking_sheet(wb, ...) - 车辆考核追踪（新增）
# 每个函数的完整代码从现有脚本对应部分提取+扩展，此处省略重复
```
注意：各write函数的完整实现直接从现有 `generate_q1_summary.py` 的Excel输出部分（第224-677行）提取重构。每个函数接收 `wb`（Workbook）和所需数据，创建对应sheet。
- [ ] **Step 2: Commit**
```bash
git add excel_writer.py
git commit -m "refactor: create excel_writer.py with sheet generation functions"
```
---
### Task 3: 新增业务员sheet生成函数
**Files:**
- Modify: `excel_writer.py`
- [ ] **Step 1: 在 excel_writer.py 中实现 write_salesperson_sheet**
```python
def write_salesperson_sheet(wb, person, dept, settle_month, D, G, month_data, vehicle_payments):
"""
为指定业务员生成sheet，展示其所有车辆在当前核算月的考核和发放详情。
person: 销售经理姓名
settle_month: 当前核算月(1/2/3)
D: {月份: [记录列表]}
G: {月份: {(车牌,销售): group}}
month_data: 当月发放数据字典
vehicle_payments: {车牌: [发放记录]}
"""
ws = wb.create_sheet(f'业务员_{person}')
# 头部
ws.cell(row=1, column=1, value=f'{person} | {dept} | {settle_month}月考核').font = Font(bold=True, size=14)
# 收集该业务员在1-settle_month中有记录的所有车牌
plates = set()
for m in range(1, settle_month + 1):
for k in G.get(m, {}):
if k[1] == person:
plates.add(k[0])
# 计算该人本月发放总额
person_total = 0
for cat, dl in month_data.items():
for d in dl:
if d['销售'] == person:
person_total += d['额']
ws.cell(row=2, column=1, value=f'本月考核车辆: {len(plates)}辆 | 本月发放合计: {R(person_total)}元').font = Font(bold=True, size=11)
rn = 4
for plate in sorted(plates):
# 车辆信息行
# 取最新month的group信息
g_cur = None
for m in range(settle_month, 0, -1):
g_cur = G.get(m, {}).get((plate, person))
if g_cur: break
if not g_cur: continue
first_rec = g_cur['recs'][0]
ws.cell(row=rn, column=1, value=plate).font = Font(bold=True)
ws.cell(row=rn, column=2, value=first_rec.get('合同编号',''))
ws.cell(row=rn, column=3, value=first_rec.get('客户名称',''))
ws.cell(row=rn, column=4, value=first_rec.get('考核目标',''))
ws.cell(row=rn, column=5, value=f'月度目标{g_cur["目标km"]}km')
ws.cell(row=rn, column=6, value=f'月奖励{g_cur["奖励额"]}元')
rn += 1
# 表头
headers = ['月份','考核天数','应考核里程','实际里程','完成率','达标','奖金','发放类型']
if settle_month >= 2:
headers += ['累计应完成','累计实际','累计达标']
WH(ws, headers, rn); rn += 1
# 每月一行
cum_target = 0; cum_actual = 0
for m in range(1, settle_month + 1):
gm = G.get(m, {}).get((plate, person))
if not gm:
WR(ws, rn, [f'{m}月', '', '', '', '', '无记录']); rn += 1; continue
t = gm['应考核']; a = gm['实际']
cum_target += t; cum_actual += a
qual = '达标' if gm['有达标'] else '未达标'
rate = R(a/t, 2) if t > 0 else 0
# 确定发放类型和金额
pay_type = ''; pay_amt = 0
if m == settle_month:
# 当前核算月的发放
carry = gm.get('结转', 0)
bonus = gm.get('当月奖金', 0)
cum_bp = gm.get(f'累计补发{m}月', 0)
if carry > 0: pay_type = '结转'; pay_amt = carry
elif bonus > 0: pay_type = '当月达标'; pay_amt = bonus
elif cum_bp > 0: pay_type = f'累计补发{m}月'; pay_amt = cum_bp
# 补发之前月份的金额不在这行显示
elif m < settle_month:
pay_type = '(历史)'; pay_amt = gm.get('奖金', 0) if gm['有达标'] else 0
row_data = [f'{m}月', gm['天数'], R(t), R(a), f'{rate*100:.0f}%' if rate else '0%', qual,
R(pay_amt) if pay_amt > 0 else 0, pay_type]
if settle_month >= 2:
cum_q = '达标' if (cum_actual >= cum_target and cum_target > 0) else '未达标'
row_data += [R(cum_target), R(cum_actual), cum_q]
WR(ws, rn, row_data); rn += 1
# 补发行（当前核算月补发之前月份）
if settle_month >= 2:
g_settle = G.get(settle_month, {}).get((plate, person), {})
for prev_m in range(1, settle_month):
bp_key = f'补发{prev_m}月' if prev_m < settle_month else ''
bp_amt = g_settle.get(bp_key, 0) if bp_key else 0
if bp_amt > 0:
WR(ws, rn, [f'补发{prev_m}月', '', '', '', '', '', R(bp_amt), f'补发{prev_m}月'])
rn += 1
# 车辆小计 + 奖金池
pays = vehicle_payments.get(plate, [])
total_periods = len(pays)
plate_total_this_month = sum(p['金额'] for p in pays if p['结算月'] == settle_month and p['业务员'] == person)
ws.cell(row=rn, column=1, value=f'小计: {R(plate_total_this_month)}元').font = Font(italic=True)
ws.cell(row=rn, column=4, value=f'奖金池: 已发{total_periods}期/共12期, 剩余{12-total_periods}期').font = Font(italic=True)
rn += 2 # 空行分隔
# 尾部汇总
ws.cell(row=rn, column=1, value=f'{person} {settle_month}月合计: {len(plates)}辆车, 发放 {R(person_total)}元').font = Font(bold=True, size=12)
AW(ws)
```
- [ ] **Step 2: Commit**
```bash
git add excel_writer.py
git commit -m "feat: add write_salesperson_sheet function"
```
---
### Task 4: 新增车辆考核追踪sheet生成函数
**Files:**
- Modify: `excel_writer.py`
- [ ] **Step 1: 在 excel_writer.py 中实现 write_vehicle_tracking_sheet**
```python
def write_vehicle_tracking_sheet(wb, settle_month, G, master_vehicles, vehicle_payments, vehicle_info):
"""
车辆考核追踪sheet，全量492辆，每车一行。
settle_month: 当前核算月
master_vehicles: 全量车辆台账列表
"""
ws = wb.create_sheet('车辆考核追踪')
# 构建表头
headers = ['车牌号','车架号','归属公司','车型','考核目标','月度奖励金额']
for m in range(1, settle_month + 1):
headers += [f'{m}月业务员', f'{m}月应考核', f'{m}月实际', f'{m}月达标']
if settle_month >= 2:
headers += ['累计应完成','累计实际','累计达标']
headers += ['本月发放金额','发放给谁','发放类型','累计已发期数','累计已发金额','剩余可发期数']
WH(ws, headers)
rn = 2
for mv in master_vehicles:
plate = mv['车牌号']
info = vehicle_info.get(plate, {})
pays = vehicle_payments.get(plate, [])
row = [plate, mv.get('车架号',''), mv.get('归属公司',''), mv.get('车型确定',''),
info.get('考核目标',''), info.get('月度奖励',0) or '']
cum_t = 0; cum_a = 0
for m in range(1, settle_month + 1):
# 找该车在m月的所有group（可能有多个销售）
month_groups = [(k,g) for k,g in G.get(m, {}).items() if k[0] == plate]
if month_groups:
persons = ', '.join(sorted(set(g['销售'] for _,g in month_groups)))
t_sum = sum(g['应考核'] for _,g in month_groups)
a_sum = sum(g['实际'] for _,g in month_groups)
q = '达标' if any(g['有达标'] for _,g in month_groups) else '未达标'
cum_t += t_sum; cum_a += a_sum
row += [persons, R(t_sum), R(a_sum), q]
else:
row += ['', '', '', '']
if settle_month >= 2:
cum_q = '达标' if (cum_a >= cum_t and cum_t > 0) else '未达标'
row += [R(cum_t), R(cum_a), cum_q]
# 本月发放
this_month_pays = [p for p in pays if p['结算月'] == settle_month]
if this_month_pays:
amt = sum(p['金额'] for p in this_month_pays)
persons = ', '.join(sorted(set(p['业务员'] for p in this_month_pays)))
types = ', '.join(sorted(set(p['类型'] for p in this_month_pays)))
row += [R(amt), persons, types]
else:
row += [0, '', '']
# 奖金池
total_periods = len(pays)
total_amt = sum(p['金额'] for p in pays)
row += [total_periods, R(total_amt) if total_amt > 0 else 0, 12 - total_periods]
WR(ws, rn, row); rn += 1
AW(ws)
```
- [ ] **Step 2: Commit**
```bash
git add excel_writer.py
git commit -m "feat: add write_vehicle_tracking_sheet function"
```
---
### Task 5: 创建 main.py 入口，按月循环生成
**Files:**
- Create: `main.py`
- [ ] **Step 1: 创建 main.py**
```python
#!/usr/bin/env python3
"""按月生成独立Excel文件"""
import os
from calc_engine import *
from excel_writer import *
os.chdir('/Users/kkfluous/Downloads')
# 读取数据
print("读取源数据...")
D = {1: read_file('租赁任务考核_2026年1月.xlsx', 1),
2: read_file('租赁任务考核_2026年2月.xlsx', 2),
3: read_file('租赁任务考核_2026年3月.xlsx', 3)}
for m in [1,2,3]: print(f" {m}月: {len(D[m])}条")
# 分组
G = {m: grp(D[m], m) for m in [1,2,3]}
calc_jan_carryover(G[1])
# 计算
feb_data = calc_feb(G[1], G[2])
mar_data = calc_mar(G[1], G[2], G[3], feb_data)
# 发放追踪
month_data_map = {2: feb_data, 3: mar_data}
vehicle_payments, vehicle_info = collect_vehicle_payments(G, month_data_map)
master_vehicles = read_master_vehicles()
# 收集所有业务员
all_persons = {} # {name: dept}
for m in G:
for k, g in G[m].items():
all_persons[g['销售']] = g['部门']
# 按月生成
for settle_month in [1, 2, 3]:
print(f"\n生成 {settle_month}月核算文件...")
wb = openpyxl.Workbook()
# 确定当月数据
if settle_month == 1:
month_data = {'当月': [{'车牌':r['车牌号'],'销售':r['销售经理'],'部门':r['部门名称'],'额':r['奖金']}
for r in D[1] if r['是否达标']=='达标']}
elif settle_month == 2:
month_data = feb_data
else:
month_data = mar_data
# Sheet 1: 考核奖励规则
write_rules_sheet(wb)
# Sheet 2: 里程明细
write_detail_sheet(wb, D[settle_month], settle_month)
# Sheet 3: 计算过程
write_calc_process_sheet(wb, settle_month, G, feb_data if settle_month >= 2 else None)
# Sheet 4: 汇总
write_summary_sheet(wb, settle_month, month_data)
# Sheet 5-16: 业务员sheets
for person in sorted(all_persons.keys()):
dept = all_persons[person]
write_salesperson_sheet(wb, person, dept, settle_month, D, G, month_data, vehicle_payments)
# Sheet 17: 车辆考核追踪
write_vehicle_tracking_sheet(wb, settle_month, G, master_vehicles, vehicle_payments, vehicle_info)
# 删除默认空sheet
if 'Sheet' in wb.sheetnames:
del wb['Sheet']
fname = f'里程任务考核_{settle_month}月核算.xlsx'
wb.save(fname)
print(f" ✅ {fname} ({len(wb.sheetnames)} sheets)")
# 汇总打印
jan_total = sum(r['奖金'] for r in D[1] if r['是否达标']=='达标')
feb_total = sum(sum(d['额'] for d in v) for v in feb_data.values())
mar_total = sum(sum(d['额'] for d in v) for v in mar_data.values())
print(f"\n1月: {jan_total:.2f}, 2月: {feb_total:.2f}, 3月: {mar_total:.2f}")
print(f"Q1总计: {jan_total + feb_total + mar_total:.2f}")
```
- [ ] **Step 2: 运行验证**
Run: `python3 main.py`
Expected:
```
生成 1月核算文件...
✅ 里程任务考核_1月核算.xlsx (17 sheets)
生成 2月核算文件...
✅ 里程任务考核_2月核算.xlsx (17 sheets)
生成 3月核算文件...
✅ 里程任务考核_3月核算.xlsx (17 sheets)
1月: 21212.26, 2月: 21152.14, 3月: 56607.10
Q1总计: 98971.50
```
- [ ] **Step 3: Commit**
```bash
git add main.py
git commit -m "feat: main.py generates monthly Excel files with all sheets"
```
---
### Task 6: 验证与清理
**Files:**
- Verify: 3个输出xlsx文件
- Delete: `generate_q1_summary.py`（旧脚本，功能已迁移）
- [ ] **Step 1: 验证金额一致性**
```python
# 运行验证脚本检查：
# 1. 每个文件的汇总金额匹配
# 2. 业务员sheet各车小计之和 = 汇总中该人合计
# 3. 车辆追踪sheet发放合计 = 当月汇总合计
```
- [ ] **Step 2: 验证sheet数量**
每个文件应有：1(规则) + 1(明细) + 1(过程) + 1(汇总) + 12(业务员) + 1(车辆) = 17 sheets
- [ ] **Step 3: Commit + Tag**
```bash
git add -A
git commit -m "V2.0.0 按月拆分Excel + 业务员/车辆维度展示"
git tag V2.0.0
```
Reference in New Issue View Git Blame Copy Permalink