Files

kkfluous 46fa8aea7a docs: 按月拆分Excel实现计划

Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

2026-04-02 14:09:51 +08:00

27 KiB

Raw Permalink Blame History

按月拆分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行提取为独立模块，封装为函数：

# 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

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写入逻辑

# 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

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

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

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

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

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

#!/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

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: 验证金额一致性

# 运行验证脚本检查：
# 1. 每个文件的汇总金额匹配
# 2. 业务员sheet各车小计之和 = 汇总中该人合计
# 3. 车辆追踪sheet发放合计 = 当月汇总合计

Step 2: 验证sheet数量

每个文件应有：1(规则) + 1(明细) + 1(过程) + 1(汇总) + 12(业务员) + 1(车辆) = 17 sheets

Step 3: Commit + Tag

git add -A
git commit -m "V2.0.0 按月拆分Excel + 业务员/车辆维度展示"
git tag V2.0.0

27 KiB Raw Permalink Blame History Unescape Escape

按月拆分Excel + 业务员/车辆维度 实现计划

Task 1: 提取计算引擎到 calc_engine.py

Task 2: 创建 excel_writer.py（现有sheet输出函数）

Task 3: 新增业务员sheet生成函数

Task 4: 新增车辆考核追踪sheet生成函数

Task 5: 创建 main.py 入口，按月循环生成

Task 6: 验证与清理

27 KiB

Raw Permalink Blame History

按月拆分Excel + 业务员/车辆维度实现计划