mileage-bonus/post_process_transfer.py

#!/usr/bin/env python3
"""后处理：在核算文件中体现离职转嫁 — 当月转新销售，其他不发了"""
import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from copy import copy
import os

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

wb = openpyxl.load_workbook(fp)
ws = wb['6月汇总']

# ── 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='宋体')
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)
YELLOW_FILL = PatternFill('solid', fgColor='FFF2CC')

# ── Find key rows ──
row_考核应发合计 = None
row_最终发放_title = None
row_最终发放明细 = None
row_按销售人员 = None
row_按部门 = None
row_总览 = None

for r in range(1, ws.max_row + 1):
    v = str(ws.cell(row=r, column=1).value or '')
    if '考核应发合计' in v and r > 50:
        row_考核应发合计 = r
    elif '三、最终发放' in v:
        row_最终发放_title = r
    elif v == '最终发放明细':
        row_最终发放明细 = r
    elif '6月最终发放（按销售人员）' in v:
        row_按销售人员 = r
    elif '6月最终发放（按部门）' in v:
        row_按部门 = r
    elif v == '总览':
        row_总览 = r

print(f"考核应发合计: row {row_考核应发合计}")
print(f"三、最终发放: row {row_最终发放_title}")
print(f"最终发放明细: row {row_最终发放明细}")
print(f"按销售人员: row {row_按销售人员}")
print(f"按部门: row {row_按部门}")
print(f"总览: row {row_总览}")

# ── Transfer config ──
# (离职人员, 接收人, 当月转嫁金额, 不发了金额)
TRANSFERS = {
    '赵连飞': {'to': '刘念念', 'transfer': 2260.00, 'dept': '业务二部'},
    '伍仲文': {'to': '钟祥',   'transfer': 150.00,  'dept': '业务六部'},
    '岑彦':   {'to': '钟祥',   'transfer': 30.00,   'dept': '业务六部'},
}

# ── Step 1: Insert "二、离职人员当月奖金转嫁" section ──
# Insert 9 rows after 考核应发合计 row
insert_at = row_考核应发合计 + 1
ws.insert_rows(insert_at, 9)

# Write transfer section
r = insert_at
ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=3)
ws.cell(row=r, column=1, value='二、离职人员当月奖金转嫁').font = TITLE_FNT
r += 2

for c, h in enumerate(['离职人员', '转嫁至', '转嫁金额', '说明'], 1):
    cell = ws.cell(row=r, column=c, value=h)
    cell.font = HEAD_FONT; cell.fill = HEAD_FILL; cell.alignment = CC; cell.border = BORD
r += 1

total_transfer = 0
for name, info in TRANSFERS.items():
    ws.cell(row=r, column=1, value=name).font = NORM_FNT
    ws.cell(row=r, column=1).alignment = CC; ws.cell(row=r, column=1).border = BORD
    ws.cell(row=r, column=2, value=info['to']).font = NORM_FNT
    ws.cell(row=r, column=2).alignment = CC; ws.cell(row=r, column=2).border = BORD
    ws.cell(row=r, column=3, value=info['transfer']).font = NORM_FNT
    ws.cell(row=r, column=3).alignment = CC; ws.cell(row=r, column=3).border = BORD
    ws.cell(row=r, column=3).number_format = '#,##0.00'
    ws.cell(row=r, column=4, value='当月达标奖金转新销售（需确认是否发放）').font = NORM_FNT
    ws.cell(row=r, column=4).alignment = LL; ws.cell(row=r, column=4).border = BORD
    total_transfer += info['transfer']
    r += 1

# 合计行
for c in range(1, 5):
    ws.cell(row=r, column=c).fill = SUB_FILL; ws.cell(row=r, column=c).border = BORD
    ws.cell(row=r, column=c).font = BOLD_FNT; ws.cell(row=r, column=c).alignment = CC
ws.cell(row=r, column=1, value='合计')
ws.cell(row=r, column=3, value=total_transfer)
ws.cell(row=r, column=3).number_format = '#,##0.00'

# ── Step 2: Update 最终发放明细 (shifted down by 9 rows) ──
# Find the shifted positions
shift = 9
row_detail_header = row_最终发放明细 + shift  # 最终发放明细 header
row_detail_data_start = row_detail_header + 2  # first data row (after "销售人员|车辆数|金额")

print(f"\n最终发放明细 shifted to header row {row_detail_header}, data starts at {row_detail_data_start}")

# Parse and update the data rows
# Data rows are from row_detail_data_start to the 总计 row
resigned_names = set(TRANSFERS.keys())
receiver_additions = {'刘念念': 2260.0, '钟祥': 180.0}

for r in range(row_detail_data_start, ws.max_row + 1):
    name_cell = ws.cell(row=r, column=1)
    amt_cell = ws.cell(row=r, column=3)
    name = str(name_cell.value or '').strip()

    if name == '总计':
        # Update total (should remain same: 113408.28)
        break

    if not name or name == '':
        break

    # Update receiver amounts
    if name in receiver_additions:
        old_val = float(amt_cell.value or 0)
        new_val = old_val + receiver_additions[name]
        amt_cell.value = new_val
        print(f"  {name}: {old_val:.2f} → {new_val:.2f} (+{receiver_additions[name]:.2f})")

    # Update resigned amounts (keep only不发了)
    if name in TRANSFERS:
        old_val = float(amt_cell.value or 0)
        new_val = old_val - TRANSFERS[name]['transfer']
        amt_cell.value = new_val
        # Add note
        note_cell = ws.cell(row=r, column=4)
        note_cell.value = '不发了（仅当月转嫁）'
        note_cell.font = NOTE_FNT
        note_cell.alignment = CC
        print(f"  {name}: {old_val:.2f} → {new_val:.2f} (不发了)")

# ── Step 3: Update 按销售人员 section ──
row_sales_header = row_按销售人员 + shift
print(f"\n按销售人员 shifted to header row {row_sales_header}")

for r in range(row_sales_header + 1, ws.max_row + 1):
    name_cell = ws.cell(row=r, column=1)
    amt_cell = ws.cell(row=r, column=3)
    name = str(name_cell.value or '').strip()

    if name == '合计':
        break

    if not name:
        break

    if name in receiver_additions:
        old_val = float(amt_cell.value or 0)
        amt_cell.value = old_val + receiver_additions[name]

    if name in TRANSFERS:
        old_val = float(amt_cell.value or 0)
        amt_cell.value = old_val - TRANSFERS[name]['transfer']

# ── Step 4: Update 总览 section (add notes) ──
row_overview = row_总览 + shift
print(f"\n总览 shifted to row {row_overview}")

# Add notes below总览
note_row = row_overview + 5  # after "最终发放" row
ws.merge_cells(start_row=note_row, start_column=1, end_row=note_row, end_column=3)
ws.cell(row=note_row, column=1, value='').font = NORM_FNT
note_row += 1

ws.merge_cells(start_row=note_row, start_column=1, end_row=note_row, end_column=3)
ws.cell(row=note_row, column=1, value='备注：').font = BOLD_FNT
note_row += 1

notes = [
    '1. 离职人员（赵连飞、伍仲文、岑彦）当月达标奖金转嫁至接收人；结转、补发、累计补发等一律不发放。',
    '2. 转嫁金额需与相关部门确认是否发放给新销售。',
    '3. 赵连飞（2026-04-30离职）→ 刘念念；伍仲文（2026-05-09离职）→ 钟祥；岑彦（2026-05-09离职）→ 钟祥。',
]
for note in notes:
    ws.merge_cells(start_row=note_row, start_column=1, end_row=note_row, end_column=3)
    ws.cell(row=note_row, column=1, value=note).font = NOTE_FNT
    ws.cell(row=note_row, column=1).alignment = LL
    note_row += 1

# ── Step 5: Rename resigned sheets with 【已离职】prefix and move to end ──
resigned_sheet_names = {
    '二部-赵连飞': '【已离职】二部-赵连飞',
    '六部-伍仲文': '【已离职】六部-伍仲文',
    '六部-岑彦':   '【已离职】六部-岑彦',
}

for old_name, new_name in resigned_sheet_names.items():
    if old_name in wb.sheetnames:
        idx = wb.sheetnames.index(old_name)
        wb[old_name].title = new_name
        # Move to end
        wb.move_sheet(new_name, offset=len(wb.sheetnames) - 1 - idx)
        print(f"  Sheet renamed: {old_name} → {new_name} (moved to end)")

# ── Save ──
wb.save(fp)
print(f"\n✅ {fp} 已更新")

# ── Verify ──
wb2 = openpyxl.load_workbook(fp, data_only=True)
ws2 = wb2['6月汇总']
print("\n验证 - 最终发放明细:")
in_detail = False
for r in range(1, ws2.max_row + 1):
    v = str(ws2.cell(row=r, column=1).value or '')
    if v == '最终发放明细':
        in_detail = True
        continue
    if in_detail:
        name = ws2.cell(row=r, column=1).value
        amt = ws2.cell(row=r, column=3).value
        note = ws2.cell(row=r, column=4).value
        if name and name != '销售人员':
            print(f"  {name}: {amt}" + (f" [{note}]" if note else ""))
        if name == '总计':
            break

print("\nSheets:", wb2.sheetnames)
wb2.close()