376 lines
16 KiB
Python
376 lines
16 KiB
Python
#!/usr/bin/env python3
|
|
"""重新生成 离职人员5月奖金处理明细.xlsx — 当月转嫁，其他不发了"""
|
|
import os, openpyxl, warnings
|
|
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
|
|
from openpyxl.utils import get_column_letter
|
|
from collections import defaultdict
|
|
from calc_engine import *
|
|
|
|
warnings.filterwarnings('ignore')
|
|
os.chdir('/Users/kkfluous/Downloads')
|
|
|
|
# ── Full calc ──
|
|
print("读取源数据...")
|
|
D = {}
|
|
for m in [1,2,3,4,5]:
|
|
D[m] = read_file(f'租赁任务考核_2026年{m}月.xlsx', m)
|
|
G = {m: grp(D[m], m) for m in [1,2,3,4,5]}
|
|
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)
|
|
apr_data = calc_apr(G[1], G[2], G[3], G[4], mar_data)
|
|
may_data = calc_may(G[1], G[2], G[3], G[4], G[5], apr_data)
|
|
|
|
RESIGNATIONS = {
|
|
'赵连飞': {'dept': '业务二部', 'transfer_to': '刘念念'},
|
|
'伍仲文': {'dept': '业务六部', 'transfer_to': '钟祥'},
|
|
'岑彦': {'dept': '业务六部', 'transfer_to': '钟祥'},
|
|
}
|
|
resigned = set(RESIGNATIONS.keys())
|
|
|
|
# ── Collect resigned payments ──
|
|
detail_records = []
|
|
for ptype, items in may_data.items():
|
|
for it in items:
|
|
if it['销售'] not in resigned:
|
|
continue
|
|
name = it['销售']
|
|
is_dangyue = (ptype == '当月')
|
|
transfer_to = RESIGNATIONS[name]['transfer_to']
|
|
handling = f'转{transfer_to}(需确认)' if is_dangyue else '不发了'
|
|
transfer_disp = transfer_to if is_dangyue else '—'
|
|
note = '5月当月达标，转新销售' if is_dangyue else f'{ptype}，不发了'
|
|
|
|
# Get additional info from G5
|
|
g5 = G[5].get((it['车牌'], name))
|
|
client = ''
|
|
for m in [1,2,3,4,5]:
|
|
for r in D[m]:
|
|
if str(r['车牌号']).strip() == it['车牌'] and r['销售经理'] == name:
|
|
client = r.get('客户名称', '')
|
|
break
|
|
if client: break
|
|
|
|
monthly_bonus = g5['奖励额'] if g5 else 0
|
|
days = g5['天数'] if g5 else ''
|
|
target_name = ''
|
|
if g5 and g5.get('recs'):
|
|
target_name = g5['recs'][0].get('考核目标', '')
|
|
|
|
# 盈亏查询月
|
|
if ptype == '当月' or ptype == '累计补发5月':
|
|
loss_month = 5
|
|
elif ptype == '结转':
|
|
loss_month = 5
|
|
elif ptype.startswith('补发'):
|
|
loss_month = int(ptype.replace('补发','').replace('月',''))
|
|
else:
|
|
loss_month = 5
|
|
|
|
detail_records.append({
|
|
'name': name, 'plate': it['车牌'], 'pay_type': ptype,
|
|
'amount': it['额'], 'handling': handling, 'transfer': transfer_disp,
|
|
'note': note, 'client': client, 'loss_month': loss_month,
|
|
'loss_status': '否', 'monthly_bonus': monthly_bonus,
|
|
'days': days, 'target': target_name,
|
|
})
|
|
|
|
print(f"离职人员发放记录: {len(detail_records)} 条")
|
|
|
|
# ── Styles ──
|
|
HEAD_FILL = PatternFill('solid', fgColor='4472C4')
|
|
HEAD_FONT = Font(bold=True, color='FFFFFF', size=10)
|
|
SUB_FILL = PatternFill('solid', fgColor='D9E1F2')
|
|
ALT_FILL = PatternFill('solid', fgColor='F2F2F2')
|
|
TITLE_FNT = Font(bold=True, size=14)
|
|
NOTE_FNT = Font(size=10, color='C00000')
|
|
NORM_FNT = Font(size=10)
|
|
BOLD_FNT = Font(bold=True, size=10)
|
|
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(ws, r, nc):
|
|
for c in range(1, nc+1):
|
|
cell = ws.cell(row=r, column=c)
|
|
cell.fill = HEAD_FILL; cell.font = HEAD_FONT; cell.alignment = CC; cell.border = BORD
|
|
|
|
def drw(ws, r, nc, alt=False):
|
|
for c in range(1, nc+1):
|
|
cell = ws.cell(row=r, column=c)
|
|
cell.font = NORM_FNT; cell.alignment = CC; cell.border = BORD
|
|
if alt: cell.fill = ALT_FILL
|
|
|
|
wb = openpyxl.Workbook()
|
|
|
|
# ═══════════════════════════════════════
|
|
# Sheet 1: 离职人员发放处理总览
|
|
# ═══════════════════════════════════════
|
|
ws1 = wb.active
|
|
ws1.title = '离职人员发放处理总览'
|
|
|
|
ws1.merge_cells('A1:G1')
|
|
ws1.cell(row=1, column=1, value='2026年5月核算 — 离职人员奖金处理方案').font = TITLE_FNT
|
|
ws1.cell(row=1, column=1).alignment = CC
|
|
ws1.row_dimensions[1].height = 30
|
|
|
|
ws1.merge_cells('A2:G2')
|
|
ws1.cell(row=2, column=1,
|
|
value='处理规则：仅5月当月达标奖金转给新接手的销售（需与其他部门确认是否发放）；其余结转、补发、累计补发等一律不发了。').font = NOTE_FNT
|
|
ws1.cell(row=2, column=1).alignment = LL
|
|
ws1.row_dimensions[2].height = 22
|
|
|
|
r = 4
|
|
oh = ['离职人员', '部门', '业务转嫁至', '不发了', '转新销售(需确认)', '原考核应发合计']
|
|
for c, h in enumerate(oh, 1):
|
|
ws1.cell(row=r, column=c, value=h)
|
|
hdr(ws1, r, 6)
|
|
r += 1
|
|
|
|
total_not = 0; total_trf = 0
|
|
for name in ['赵连飞', '伍仲文', '岑彦']:
|
|
info = RESIGNATIONS[name]
|
|
recs = [p for p in detail_records if p['name'] == name]
|
|
not_pay = sum(p['amount'] for p in recs if p['pay_type'] != '当月')
|
|
transfer = sum(p['amount'] for p in recs if p['pay_type'] == '当月')
|
|
total_not += not_pay; total_trf += transfer
|
|
ws1.cell(row=r, column=1, value=name)
|
|
ws1.cell(row=r, column=2, value=info['dept'])
|
|
ws1.cell(row=r, column=3, value=info['transfer_to'])
|
|
ws1.cell(row=r, column=4, value=round(not_pay, 2))
|
|
ws1.cell(row=r, column=5, value=round(transfer, 2))
|
|
ws1.cell(row=r, column=6, value=round(not_pay+transfer, 2))
|
|
drw(ws1, r, 6)
|
|
ws1.row_dimensions[r].height = 22
|
|
r += 1
|
|
|
|
# 合计
|
|
for c in range(1, 7):
|
|
ws1.cell(row=r, column=c).fill = SUB_FILL
|
|
ws1.cell(row=r, column=c).border = BORD
|
|
ws1.cell(row=r, column=c).font = BOLD_FNT
|
|
ws1.cell(row=r, column=c).alignment = CC
|
|
ws1.cell(row=r, column=1, value='合计')
|
|
ws1.cell(row=r, column=4, value=round(total_not, 2))
|
|
ws1.cell(row=r, column=5, value=round(total_trf, 2))
|
|
ws1.cell(row=r, column=6, value=round(total_not+total_trf, 2))
|
|
|
|
r += 2
|
|
ws1.merge_cells(start_row=r, start_column=1, end_row=r, end_column=6)
|
|
ws1.cell(row=r, column=1, value='转嫁接收方汇总（需与其他部门确认是否发放）').font = Font(bold=True, size=12, color='1F4E78')
|
|
ws1.cell(row=r, column=1).alignment = CC
|
|
r += 1
|
|
|
|
for c, hh in enumerate(['接收人', '来源离职人员', '转嫁金额', '备注'], 1):
|
|
ws1.cell(row=r, column=c, value=hh)
|
|
hdr(ws1, r, 4)
|
|
r += 1
|
|
|
|
by_recv = defaultdict(lambda: {'sources':[], 'amt':0})
|
|
for rec in detail_records:
|
|
if rec['pay_type'] == '当月':
|
|
t = rec['transfer']
|
|
by_recv[t]['sources'].append(rec['name'])
|
|
by_recv[t]['amt'] += rec['amount']
|
|
|
|
for receiver in ['刘念念', '钟祥']:
|
|
d = by_recv[receiver]
|
|
ws1.cell(row=r, column=1, value=receiver)
|
|
ws1.cell(row=r, column=2, value='、'.join(sorted(set(d['sources']))))
|
|
ws1.cell(row=r, column=3, value=round(d['amt'], 2))
|
|
ws1.cell(row=r, column=4, value='需与相关部门确认是否发放给新销售')
|
|
drw(ws1, r, 4)
|
|
r += 1
|
|
|
|
for i, w in enumerate([14, 22, 18, 18, 18, 18], 1):
|
|
ws1.column_dimensions[get_column_letter(i)].width = w
|
|
|
|
# ═══════════════════════════════════════
|
|
# Sheet 2: 发放明细(逐条)
|
|
# ═══════════════════════════════════════
|
|
ws2 = wb.create_sheet('发放明细(逐条)')
|
|
ws2.merge_cells('A1:M1')
|
|
ws2.cell(row=1, column=1, value='离职人员 — 5月考核应发逐条明细').font = TITLE_FNT
|
|
ws2.cell(row=1, column=1).alignment = CC
|
|
ws2.row_dimensions[1].height = 28
|
|
|
|
dh = ['离职人员', '车牌号', '发放类型', '考核应发', '盈亏查询月', '客户名称',
|
|
'客户盈亏', '处理方式', '转嫁接收人', '5月考核天数', '月度奖励', '考核目标', '备注']
|
|
r2 = 3
|
|
for c, h in enumerate(dh, 1):
|
|
ws2.cell(row=r2, column=c, value=h)
|
|
hdr(ws2, r2, 13)
|
|
r2 += 1
|
|
|
|
name_order = ['赵连飞', '伍仲文', '岑彦']
|
|
detail_records.sort(key=lambda x: (name_order.index(x['name']), 0 if x['pay_type']=='当月' else 1, x['plate']))
|
|
|
|
for i, rec in enumerate(detail_records):
|
|
ws2.cell(row=r2, column=1, value=rec['name'])
|
|
ws2.cell(row=r2, column=2, value=rec['plate'])
|
|
ws2.cell(row=r2, column=3, value=rec['pay_type'])
|
|
ws2.cell(row=r2, column=4, value=round(rec['amount'], 2))
|
|
ws2.cell(row=r2, column=5, value=f"{rec['loss_month']}月")
|
|
ws2.cell(row=r2, column=6, value=rec['client'])
|
|
ws2.cell(row=r2, column=7, value=rec['loss_status'])
|
|
ws2.cell(row=r2, column=8, value=rec['handling'])
|
|
ws2.cell(row=r2, column=9, value=rec['transfer'])
|
|
ws2.cell(row=r2, column=10, value=rec['days'] if rec['days'] else '')
|
|
ws2.cell(row=r2, column=11, value=rec['monthly_bonus'] if rec['monthly_bonus'] else '')
|
|
ws2.cell(row=r2, column=12, value=rec['target'])
|
|
ws2.cell(row=r2, column=13, value=rec['note'])
|
|
drw(ws2, r2, 13, alt=(i%2==1))
|
|
ws2.row_dimensions[r2].height = 22
|
|
r2 += 1
|
|
|
|
# 合计行
|
|
ws2.cell(row=r2, column=3, value='合计')
|
|
ws2.cell(row=r2, column=4, value=round(sum(r['amount'] for r in detail_records), 2))
|
|
for c in range(1, 14):
|
|
ws2.cell(row=r2, column=c).fill = SUB_FILL
|
|
ws2.cell(row=r2, column=c).border = BORD
|
|
ws2.cell(row=r2, column=c).font = BOLD_FNT
|
|
ws2.cell(row=r2, column=c).alignment = CC
|
|
|
|
for i, w in enumerate([10, 12, 14, 14, 10, 24, 8, 18, 14, 12, 10, 22, 28], 1):
|
|
ws2.column_dimensions[get_column_letter(i)].width = w
|
|
|
|
# ═══════════════════════════════════════
|
|
# Sheets 3-5: 个人明细
|
|
# ═══════════════════════════════════════
|
|
for name, title_txt, transfer_to in [
|
|
('赵连飞', '赵连飞（2026-04-30离职，业务转→刘念念）', '刘念念'),
|
|
('伍仲文', '伍仲文（2026-05-09离职，业务转→钟祥）', '钟祥'),
|
|
('岑彦', '岑彦（2026-05-09离职，业务转→钟祥）', '钟祥'),
|
|
]:
|
|
ws = wb.create_sheet(name)
|
|
ws.merge_cells('A1:K1')
|
|
ws.cell(row=1, column=1, value=title_txt).font = TITLE_FNT
|
|
ws.cell(row=1, column=1).alignment = CC
|
|
ws.row_dimensions[1].height = 28
|
|
|
|
ph = ['车牌号', '发放类型', '考核应发', '盈亏查询月', '客户名称', '客户盈亏',
|
|
'处理方式', '5月考核天数', '月度奖励', '考核目标', '备注']
|
|
r3 = 3
|
|
for c, h in enumerate(ph, 1):
|
|
ws.cell(row=r3, column=c, value=h)
|
|
hdr(ws, r3, 11)
|
|
r3 += 1
|
|
|
|
person_recs = [p for p in detail_records if p['name'] == name]
|
|
dangyue_recs = [p for p in person_recs if p['pay_type'] == '当月']
|
|
other_recs = [p for p in person_recs if p['pay_type'] != '当月']
|
|
|
|
for i, rec in enumerate(person_recs):
|
|
h_disp = rec['handling']
|
|
ws.cell(row=r3, column=1, value=rec['plate'])
|
|
ws.cell(row=r3, column=2, value=rec['pay_type'])
|
|
ws.cell(row=r3, column=3, value=round(rec['amount'], 2))
|
|
ws.cell(row=r3, column=4, value=f"{rec['loss_month']}月")
|
|
ws.cell(row=r3, column=5, value=rec['client'])
|
|
ws.cell(row=r3, column=6, value=rec['loss_status'])
|
|
ws.cell(row=r3, column=7, value=h_disp)
|
|
ws.cell(row=r3, column=8, value=rec['days'] if rec['days'] else '')
|
|
ws.cell(row=r3, column=9, value=rec['monthly_bonus'] if rec['monthly_bonus'] else '')
|
|
ws.cell(row=r3, column=10, value=rec['target'])
|
|
ws.cell(row=r3, column=11, value=rec['note'])
|
|
drw(ws, r3, 11, alt=(i%2==1))
|
|
ws.row_dimensions[r3].height = 22
|
|
r3 += 1
|
|
|
|
dn = sum(p['amount'] for p in dangyue_recs)
|
|
ot = sum(p['amount'] for p in other_recs)
|
|
gt = dn + ot
|
|
|
|
# 不发了小计
|
|
ws.cell(row=r3, column=2, value='不发了小计').font = BOLD_FNT
|
|
ws.cell(row=r3, column=3, value=round(ot, 2)).font = BOLD_FNT
|
|
for c in range(1, 12):
|
|
ws.cell(row=r3, column=c).fill = SUB_FILL; ws.cell(row=r3, column=c).border = BORD
|
|
ws.cell(row=r3, column=2).alignment = CC; ws.cell(row=r3, column=3).alignment = CC
|
|
r3 += 1
|
|
|
|
# 转嫁小计
|
|
ws.cell(row=r3, column=2, value=f'转{transfer_to}小计').font = BOLD_FNT
|
|
ws.cell(row=r3, column=3, value=round(dn, 2)).font = BOLD_FNT
|
|
ws.cell(row=r3, column=11, value='需与其他部门确认').font = Font(size=9, color='C00000')
|
|
for c in range(1, 12):
|
|
ws.cell(row=r3, column=c).fill = SUB_FILL; ws.cell(row=r3, column=c).border = BORD
|
|
ws.cell(row=r3, column=2).alignment = CC; ws.cell(row=r3, column=3).alignment = CC
|
|
r3 += 1
|
|
|
|
# 合计
|
|
ws.cell(row=r3, column=2, value='合计').font = BOLD_FNT
|
|
ws.cell(row=r3, column=3, value=round(gt, 2)).font = BOLD_FNT
|
|
for c in range(1, 12):
|
|
ws.cell(row=r3, column=c).fill = SUB_FILL; ws.cell(row=r3, column=c).border = BORD
|
|
ws.cell(row=r3, column=2).alignment = CC; ws.cell(row=r3, column=3).alignment = CC
|
|
|
|
for i, w in enumerate([12, 14, 14, 10, 26, 8, 18, 12, 10, 22, 30], 1):
|
|
ws.column_dimensions[get_column_letter(i)].width = w
|
|
|
|
# ═══════════════════════════════════════
|
|
# Sheets 6-7: 转嫁明细
|
|
# ═══════════════════════════════════════
|
|
for title_txt, sources, receiver in [
|
|
('转嫁至刘念念的奖金 — 来源：赵连飞(2026-04-30离职)', ['赵连飞'], '刘念念'),
|
|
('转嫁至钟祥的奖金 — 来源：伍仲文(2026-05-09离职)、岑彦(2026-05-09离职)', ['伍仲文', '岑彦'], '钟祥'),
|
|
]:
|
|
ws = wb.create_sheet(f'转{receiver}明细')
|
|
ws.merge_cells('A1:K1')
|
|
ws.cell(row=1, column=1, value=title_txt).font = TITLE_FNT
|
|
ws.cell(row=1, column=1).alignment = CC
|
|
ws.row_dimensions[1].height = 28
|
|
|
|
ws.merge_cells('A2:K2')
|
|
ws.cell(row=2, column=1, value='⚠ 以下金额需与其他部门确认是否可发放给新销售').font = NOTE_FNT
|
|
ws.cell(row=2, column=1).alignment = LL
|
|
|
|
th = ['来源离职人员', '车牌号', '发放类型', '考核应发', '盈亏查询月', '客户名称',
|
|
'客户盈亏', '5月考核天数', '月度奖励', '考核目标', '备注']
|
|
r4 = 4
|
|
for c, h in enumerate(th, 1):
|
|
ws.cell(row=r4, column=c, value=h)
|
|
hdr(ws, r4, 11)
|
|
r4 += 1
|
|
|
|
trf_recs = [p for p in detail_records if p['name'] in sources and p['pay_type'] == '当月']
|
|
for i, rec in enumerate(trf_recs):
|
|
ws.cell(row=r4, column=1, value=rec['name'])
|
|
ws.cell(row=r4, column=2, value=rec['plate'])
|
|
ws.cell(row=r4, column=3, value='当月')
|
|
ws.cell(row=r4, column=4, value=round(rec['amount'], 2))
|
|
ws.cell(row=r4, column=5, value=f"{rec['loss_month']}月")
|
|
ws.cell(row=r4, column=6, value=rec['client'])
|
|
ws.cell(row=r4, column=7, value=rec['loss_status'])
|
|
ws.cell(row=r4, column=8, value=rec['days'] if rec['days'] else '')
|
|
ws.cell(row=r4, column=9, value=rec['monthly_bonus'] if rec['monthly_bonus'] else '')
|
|
ws.cell(row=r4, column=10, value=rec['target'])
|
|
ws.cell(row=r4, column=11, value='需确认是否发给新销售')
|
|
drw(ws, r4, 11, alt=(i%2==1))
|
|
ws.row_dimensions[r4].height = 22
|
|
r4 += 1
|
|
|
|
ws.cell(row=r4, column=3, value='合计')
|
|
ws.cell(row=r4, column=4, value=round(sum(r['amount'] for r in trf_recs), 2))
|
|
for c in range(1, 12):
|
|
ws.cell(row=r4, column=c).fill = SUB_FILL; ws.cell(row=r4, column=c).border = BORD
|
|
ws.cell(row=r4, column=c).font = BOLD_FNT; ws.cell(row=r4, column=c).alignment = CC
|
|
|
|
for i, w in enumerate([14, 12, 14, 14, 10, 26, 8, 12, 10, 22, 22], 1):
|
|
ws.column_dimensions[get_column_letter(i)].width = w
|
|
|
|
# ── Save ──
|
|
out = 'new-kpi-202605_1/离职人员5月奖金处理明细.xlsx'
|
|
wb.save(out)
|
|
|
|
print(f'\n✅ {out} ({len(wb.sheetnames)} sheets)')
|
|
print(f'\n汇总:')
|
|
for name in ['赵连飞', '伍仲文', '岑彦']:
|
|
recs = [p for p in detail_records if p['name'] == name]
|
|
not_pay = sum(p['amount'] for p in recs if p['pay_type'] != '当月')
|
|
trf = sum(p['amount'] for p in recs if p['pay_type'] == '当月')
|
|
print(f' {name}: 不发了={not_pay:.2f} 转{RESIGNATIONS[name]["transfer_to"]}={trf:.2f} 合计={not_pay+trf:.2f}')
|
|
print(f' 总计: 不发了={total_not:.2f} 转新销售={total_trf:.2f}')
|