mileage-bonus/rebuild_summary.py

#!/usr/bin/env python3
"""按参考格式重建 6月汇总 sheet：剔除离职，转嫁用接收人格式"""
import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from collections import defaultdict, OrderedDict
import os, copy

os.chdir('/Users/kkfluous/Downloads')
fp = '里程任务考核_6月核算.xlsx'

# ── Config ──
RESIGNED = {'赵连飞', '伍仲文', '岑彦'}
TRANSFERS = [
    ('刘念念', '赵连飞', 2260.00),
    ('钟祥',   '伍仲文、岑彦', 180.00),
]
TRANSFER_TOTAL = sum(t[2] for t in TRANSFERS)
# Map resigned -> receiver for aggregation
TRANSFER_MAP = {'赵连飞': ('刘念念', 2260.00), '伍仲文': ('钟祥', 150.00), '岑彦': ('钟祥', 30.00)}

# ── Styles ──
HEAD_FILL = PatternFill('solid', fgColor='4472C4')
HEAD_FONT = Font(bold=True, color='FFFFFF', size=10, name='宋体')
SUB_FILL  = PatternFill('solid', fgColor='D9E1F2')
TITLE_FNT = Font(bold=True, size=12, name='宋体')
SECTION_FNT = Font(bold=True, size=11, name='宋体')
NOTE_FNT  = Font(size=10, color='C00000', name='宋体')
NORM_FNT  = Font(size=10, name='宋体')
BOLD_FNT  = Font(bold=True, size=10, name='宋体')
THIN = Side('thin', color='B4B4B4')
BORD = Border(THIN, THIN, THIN, THIN)
CC = Alignment(horizontal='center', vertical='center', wrap_text=True)
LL = Alignment(horizontal='left', vertical='center', wrap_text=True)

def hdr_row(ws, r, headers):
    for c, h in enumerate(headers, 1):
        cell = ws.cell(row=r, column=c, value=h)
        cell.font = HEAD_FONT; cell.fill = HEAD_FILL; cell.alignment = CC; cell.border = BORD

def data_row(ws, r, values, bold=False):
    for c, v in enumerate(values, 1):
        cell = ws.cell(row=r, column=c, value=v)
        cell.font = BOLD_FNT if bold else NORM_FNT
        cell.alignment = CC; cell.border = BORD
        if isinstance(v, float):
            cell.number_format = '#,##0.00'

def sub_total(ws, r, values):
    for c, v in enumerate(values, 1):
        cell = ws.cell(row=r, column=c, value=v)
        cell.font = BOLD_FNT; cell.fill = SUB_FILL
        cell.alignment = CC; cell.border = BORD
        if isinstance(v, float):
            cell.number_format = '#,##0.00'

# ── Step 1: Parse existing data ──
wb = openpyxl.load_workbook(fp)
ws_old = wb['6月汇总']

# Parse all考核应发 subsections
subsections = []  # [(title, [(name, vehicles, amount), ...], total_row)]
current_title = None
current_rows = []
in_detail = False

# Map person->dept from按销售人员 section
person_dept = {}
in_sales = False
for r in range(1, ws_old.max_row + 1):
    v1 = str(ws_old.cell(row=r, column=1).value or '')
    v2 = str(ws_old.cell(row=r, column=2).value or '')
    if '6月最终发放（按销售人员）' in v1:
        in_sales = True
        continue
    if in_sales:
        name = str(ws_old.cell(row=r, column=1).value or '')
        dept = str(ws_old.cell(row=r, column=2).value or '')
        if name == '合计':
            break
        if name and name != '销售人员' and dept:
            person_dept[name] = dept

print(f"人员部门映射: {person_dept}")

# Parse考核应发 subsections
for r in range(1, ws_old.max_row + 1):
    v1 = str(ws_old.cell(row=r, column=1).value or '')

    if v1.startswith('考核应发-'):
        if current_title and current_rows:
            subsections.append((current_title, current_rows))
        current_title = v1
        current_rows = []
        in_detail = True
        continue

    if current_title and in_detail:
        name = str(ws_old.cell(row=r, column=1).value or '')
        vehicles = ws_old.cell(row=r, column=2).value
        amount = ws_old.cell(row=r, column=3).value

        if name == '总计':
            subsections.append((current_title, current_rows))
            current_title = None
            current_rows = []
            in_detail = False
        elif name and name != '销售人员':
            current_rows.append((name, vehicles, amount))

print(f"\n解析到 {len(subsections)} 个考核应发 subsection:")

# ── Step 2: Filter out resigned, recalculate ──
filtered_subsections = []
grand_total = 0.0

for title, rows in subsections:
    filtered = [(n, v, a) for (n, v, a) in rows if n not in RESIGNED]
    # Recalculate vehicles and amounts
    new_vehicles = sum(int(v or 0) for _, v, _ in filtered)
    new_amount = sum(float(a or 0) for _, _, a in filtered)
    grand_total += new_amount
    filtered_subsections.append((title, filtered, new_vehicles, new_amount))
    removed = [(n, a) for (n, _, a) in rows if n in RESIGNED]
    if removed:
        print(f"  {title}: 剔除 {removed}, 新合计={new_amount:.2f}")

print(f"\n考核应发合计: {grand_total:.2f}")

# ── Step 3: Build最终发放 (aggregate by person + transfers) ──
person_data = defaultdict(lambda: {'vehicles': 0, 'amount': 0.0})
for title, rows, _, _ in filtered_subsections:
    for name, vehicles, amount in rows:
        person_data[name]['vehicles'] += int(vehicles or 0)
        person_data[name]['amount'] += float(amount or 0)

# Add transfers to receivers
for resigned, (receiver, amt) in TRANSFER_MAP.items():
    person_data[receiver]['amount'] += amt
    # Count vehicles? The reference doesn't seem to add vehicle count for transfers

# Remove resigned (should already be filtered, but just in case)
for name in list(person_data.keys()):
    if name in RESIGNED:
        del person_data[name]

final_total = sum(d['amount'] for d in person_data.values())
print(f"最终发放合计: {final_total:.2f}")

# Sort by department then name (matching reference order)
dept_order = ['业务二部', '业务三部', '业务五部', '业务六部']
def sort_key(item):
    name = item[0]
    dept = person_dept.get(name, '')
    try:
        di = dept_order.index(dept)
    except ValueError:
        di = 99
    return (di, name)

sorted_persons = sorted(person_data.items(), key=sort_key)

# ── Step 4: Department totals ──
dept_totals = defaultdict(float)
for name, data in sorted_persons:
    dept = person_dept.get(name, '')
    dept_totals[dept] += data['amount']

# ── Step 5: Rebuild the sheet ──
# Delete old sheet and create new
ws = wb['6月汇总']
ws.delete_rows(1, ws.max_row)

rn = 1

# === 一、考核应发明细 ===
ws.merge_cells(start_row=rn, start_column=1, end_row=rn, end_column=3)
ws.cell(row=rn, column=1, value='一、考核应发明细').font = TITLE_FNT
rn += 2

for title, rows, total_veh, total_amt in filtered_subsections:
    ws.cell(row=rn, column=1, value=title).font = SECTION_FNT
    rn += 1
    hdr_row(ws, rn, ['销售人员', '车辆数', '金额'])
    rn += 1

    # Sort rows by dept then name
    sorted_rows = sorted(rows, key=lambda x: sort_key((x[0],)) if x[0] in person_dept else (99, x[0]))

    for name, vehicles, amount in sorted_rows:
        data_row(ws, rn, [name, vehicles, round(float(amount or 0), 2)])
        rn += 1

    sub_total(ws, rn, ['总计', total_veh, round(total_amt, 2)])
    rn += 2

# 考核应发合计
ws.merge_cells(start_row=rn, start_column=1, end_row=rn, end_column=2)
ws.cell(row=rn, column=1, value='考核应发合计').font = BOLD_FNT
ws.cell(row=rn, column=1).alignment = CC
ws.cell(row=rn, column=3, value=round(grand_total, 2)).font = BOLD_FNT
ws.cell(row=rn, column=3).alignment = CC
ws.cell(row=rn, column=3).number_format = '#,##0.00'
for c in range(1, 4):
    ws.cell(row=rn, column=c).border = BORD
    ws.cell(row=rn, column=c).fill = SUB_FILL
rn += 2

# === 二、离职人员转嫁收入 ===
ws.merge_cells(start_row=rn, start_column=1, end_row=rn, end_column=3)
ws.cell(row=rn, column=1, value='二、离职人员转嫁收入').font = TITLE_FNT
rn += 1
hdr_row(ws, rn, ['接收人', '来源', '转嫁金额'])
rn += 1

for receiver, source, amt in TRANSFERS:
    data_row(ws, rn, [receiver, source, amt])
    rn += 1

sub_total(ws, rn, ['合计', '', TRANSFER_TOTAL])
rn += 2

# === 三、最终发放 ===
ws.merge_cells(start_row=rn, start_column=1, end_row=rn, end_column=3)
ws.cell(row=rn, column=1, value='三、最终发放').font = TITLE_FNT
rn += 2

# 最终发放明细
ws.cell(row=rn, column=1, value='最终发放明细').font = SECTION_FNT
rn += 1
hdr_row(ws, rn, ['销售人员', '车辆数', '金额'])
rn += 1

for name, data in sorted_persons:
    data_row(ws, rn, [name, data['vehicles'], round(data['amount'], 2)])
    rn += 1

sub_total(ws, rn, ['总计', sum(d['vehicles'] for _, d in sorted_persons), round(final_total, 2)])
rn += 2

# 6月最终发放（按销售人员）
ws.cell(row=rn, column=1, value='6月最终发放（按销售人员）').font = SECTION_FNT
rn += 1
hdr_row(ws, rn, ['销售人员', '部门名称', '最终发放'])
rn += 1

for name, data in sorted_persons:
    dept = person_dept.get(name, '')
    data_row(ws, rn, [name, dept, round(data['amount'], 2)])
    rn += 1

sub_total(ws, rn, ['合计', '', round(final_total, 2)])
rn += 2

# 6月最终发放（按部门）
ws.cell(row=rn, column=1, value='6月最终发放（按部门）').font = SECTION_FNT
rn += 1
hdr_row(ws, rn, ['部门名称', '最终发放', ''])
rn += 1

for dept in dept_order:
    if dept in dept_totals:
        data_row(ws, rn, [dept, round(dept_totals[dept], 2), ''])
        rn += 1

sub_total(ws, rn, ['合计', round(sum(dept_totals.values()), 2), ''])
rn += 2

# === 总览 ===
ws.cell(row=rn, column=1, value='总览').font = SECTION_FNT
rn += 1
hdr_row(ws, rn, ['', '', ''])
# Override header style for总览
data_row(ws, rn, ['考核应发', round(final_total, 2), ''])
rn += 1
data_row(ws, rn, ['亏损拦截', 0, ''])
rn += 1
data_row(ws, rn, ['最终发放', round(final_total, 2), ''])
rn += 2

# === 备注 ===
note_text = '备注：赵连飞（2026-04-30离职）、伍仲文（2026-05-09离职）、岑彦（2026-05-09离职）\n三人已从上述汇总中剔除，其6月当月达标奖金已转嫁至新销售；其余结转、补发、累计补发等一律不发了。'
ws.merge_cells(start_row=rn, start_column=1, end_row=rn, end_column=3)
ws.cell(row=rn, column=1, value=note_text).font = NOTE_FNT
ws.cell(row=rn, column=1).alignment = Alignment(horizontal='left', vertical='center', wrap_text=True)
ws.row_dimensions[rn].height = 36

# ── Column widths ──
ws.column_dimensions['A'].width = 16
ws.column_dimensions['B'].width = 18
ws.column_dimensions['C'].width = 16

# ── Rename resigned sheets ──
for old_name, new_name in [('二部-赵连飞','【已离职】二部-赵连飞'),('六部-伍仲文','【已离职】六部-伍仲文'),('六部-岑彦','【已离职】六部-岑彦')]:
    if old_name in wb.sheetnames:
        wb[old_name].title = new_name
        wb.move_sheet(new_name, offset=len(wb.sheetnames)-1-wb.sheetnames.index(new_name))
        print(f"  Sheet: {old_name} → {new_name}")

# ── Copy to output ──
import shutil
out = '/Users/kkfluous/Downloads/20260713/里程任务考核_6月核算.xlsx'
wb.save(out)
print(f"\n✅ {out} 已重建")

# ── Verify ──
wb2 = openpyxl.load_workbook(out, data_only=True)
ws2 = wb2['6月汇总']
print(f"\n验证 ({ws2.max_row} rows):")
for row in ws2.iter_rows(min_row=1, max_row=ws2.max_row):
    vals = [str(c.value)[:50] if c.value is not None else '' for c in row]
    line = ' | '.join(vals)
    if line.replace(' | ', '').strip():
        print(f"  {line}")
wb2.close()