mileage-bonus/_gen_intervention_v2.py

#!/usr/bin/env python3
"""Generate 租赁任务考核_2026年5月_干预情况说明.xlsx — GPS干预后的完整说明"""
import os, openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from collections import defaultdict

os.chdir('/Users/kkfluous/Downloads')

SRC = '租赁任务考核_2026年5月.xlsx'
BAK = '租赁任务考核_2026年5月_原版备份.xlsx'
INTV = '标黄为5月干预车辆.xlsx'

# Styles
HEAD = PatternFill('solid', fgColor='4472C4')
HFONT = Font(bold=True, color='FFFFFF', size=10)
SUB = PatternFill('solid', fgColor='D9E1F2')
ALT = PatternFill('solid', fgColor='F2F2F2')
TITLE_FONT = Font(bold=True, size=14)
SEC_FONT = Font(bold=True, size=12, color='1F4E78')
NOTE_FONT = Font(size=10)
BOLD_FONT = Font(bold=True, size=10)
RED_FONT = Font(size=10, color='C00000')
BORD = Border(Side('thin','B4B4B4'), Side('thin','B4B4B4'), Side('thin','B4B4B4'), Side('thin','B4B4B4'))
CC = Alignment(horizontal='center', vertical='center', wrap_text=True)
LL = Alignment(horizontal='left', vertical='center', wrap_text=True)

# ── Load intervention plates ──
wb_i = openpyxl.load_workbook(INTV)
ws_i = wb_i['Sheet1']
yellow_plates = set()
for row in ws_i.iter_rows(min_row=2, values_only=False):
    cell = row[0]
    fill = cell.fill
    is_yellow = (fill and fill.patternType == 'solid' and fill.start_color
                 and fill.start_color.rgb and 'FFFF00' in str(fill.start_color.rgb))
    if is_yellow:
        p = str(cell.value).strip() if cell.value else ''
        if p:
            yellow_plates.add(p)
wb_i.close()

# ── Load source (post-intervention) and backup (pre-intervention) ──
def load_records(fp):
    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):
        if row[0]:
            recs.append(dict(zip(h, row)))
    wb.close()
    return recs

src_recs = load_records(SRC)
bak_recs = load_records(BAK)

# Build plate -> {backup rec, source rec}
bak_by_plate = {}
for r in bak_recs:
    p = str(r['车牌号']).strip()
    # For duplicate plates, keep last
    key = (p, str(r.get('销售经理','')), str(r.get('项目名称','')))
    bak_by_plate[key] = r

src_by_plate = {}
for r in src_recs:
    p = str(r['车牌号']).strip()
    key = (p, str(r.get('销售经理','')), str(r.get('项目名称','')))
    src_by_plate[key] = r

# ── Analyze intervention impact ──
# For each source record that's an intervention vehicle, find its backup
intv_records = []
for key, sr in src_by_plate.items():
    p = key[0]
    if p not in yellow_plates:
        continue
    br = bak_by_plate.get(key)
    tbox = sr.get('原TBOX里程(km)')
    gps = sr.get('实际行驶里程(km)')
    test = sr.get('测试里程(km)')
    old_actual = float(br['实际行驶里程(km)'] or 0) if br else 0
    old_pass = str(br['是否达标']).strip() if br else '?'
    new_pass = str(sr['是否达标']).strip() if sr else '?'
    target = float(sr.get('应考核里程(km)', 0) or 0)

    tbox_val = float(tbox or 0)
    gps_val = float(gps or 0)
    test_val = float(test or 0)

    flip = ''
    if old_pass == '达标' and new_pass == '未达标':
        flip = '达→未达标'
    elif old_pass == '未达标' and new_pass == '达标':
        flip = '未→达标'

    has_gps = '有' if gps_val > 0 else '无GPS数据'

    intv_records.append({
        'plate': p, 'dept': sr.get('部门名称', ''), 'sales': sr.get('销售经理', ''),
        'client': sr.get('客户名称', ''), 'target_name': sr.get('考核目标', ''),
        'target_km': target, 'old_actual': old_actual, 'tbox': tbox_val,
        'gps': gps_val, 'test': test_val, 'old_pass': old_pass, 'new_pass': new_pass,
        'flip': flip, 'has_gps': has_gps
    })

print(f"Intervention records: {len(intv_records)}")
flip_down = [r for r in intv_records if r['flip'] == '达→未达标']
flip_up = [r for r in intv_records if r['flip'] == '未→达标']
no_gps = [r for r in intv_records if r['gps'] == 0]
print(f"达→未达标: {len(flip_down)}, 未→达标: {len(flip_up)}, 无GPS: {len(no_gps)}")

# ── Intervention vehicles by target type ──
by_target = defaultdict(list)
for r in intv_records:
    by_target[r['target_name']].append(r)

# ── Generate workbook ──
wb = openpyxl.Workbook()

def style_header(ws, row, ncols):
    for c in range(1, ncols+1):
        cell = ws.cell(row=row, column=c)
        cell.fill = HEAD; cell.font = HFONT; cell.alignment = CC; cell.border = BORD

def style_row(ws, row, ncols, alt=False):
    for c in range(1, ncols+1):
        cell = ws.cell(row=row, column=c)
        cell.border = BORD; cell.font = Font(size=10); cell.alignment = CC
        if alt: cell.fill = ALT

# ── Sheet 1: 测试与考核情况说明 ──
ws1 = wb.active
ws1.title = '测试与考核情况说明'
ws1.sheet_properties.tabColor = '4472C4'

ws1.merge_cells('A1:H1')
ws1.cell(row=1, column=1, value='2026年5月 业务考核 — GPS干预与考核情况说明').font = TITLE_FONT
ws1.cell(row=1, column=1).alignment = CC
ws1.row_dimensions[1].height = 32

desc = (
    f'5月共计{len(src_recs)}条考核记录，其中{len(yellow_plates)}辆车/{len(intv_records)}条记录涉及GPS里程干预。\n'
    '干预规则：对于标黄车辆，使用G7平台GPS每日里程（考核期内逐日累加）替代原仪表盘里程作为实际行驶里程参与考核。\n'
    f'GPS数据来源：hydrogen_energy.ln_vehicle_g7_mileage（101.133.130.65），按考核起止时间汇总每日total_mileage/100000=km。\n'
    f'干预结果：{len(flip_down)}条由达标翻转为未达标，{len(flip_up)}条由未达标翻转为达标，{len(no_gps)}条车辆无GPS数据（里程记为0）。'
)
ws1.merge_cells('A2:H2')
ws1.cell(row=2, column=1, value=desc).font = NOTE_FONT
ws1.cell(row=2, column=1).alignment = Alignment(horizontal='left', vertical='top', wrap_text=True)
ws1.row_dimensions[2].height = 80

r = 4
ws1.merge_cells(start_row=r, start_column=1, end_row=r, end_column=8)
ws1.cell(row=r, column=1, value='一、5月考核概况（GPS干预后）').font = SEC_FONT
r += 1

# Summary table
summary_headers = ['指标', '干预前', '干预后', '变化']
for c, h in enumerate(summary_headers, 1):
    ws1.cell(row=r, column=c, value=h)
style_header(ws1, r, 4)
r += 1

old_pass_count = sum(1 for r in bak_recs if str(r.get('是否达标','')).strip() == '达标')
new_pass_count = sum(1 for r in src_recs if str(r.get('是否达标','')).strip() == '达标')
old_fail = len(bak_recs) - old_pass_count
new_fail = len(src_recs) - new_pass_count

summary_rows = [
    ('考核记录数(条)', len(bak_recs), len(src_recs), ''),
    ('达标记录数(条)', old_pass_count, new_pass_count, f'{new_pass_count - old_pass_count:+d}'),
    ('未达标记录数(条)', old_fail, new_fail, f'{new_fail - old_fail:+d}'),
    ('达标率', f'{old_pass_count/len(bak_recs)*100:.1f}%', f'{new_pass_count/len(src_recs)*100:.1f}%', ''),
    ('干预车辆数', len(yellow_plates), len(yellow_plates), ''),
    ('涉及考核记录', len(intv_records), len(intv_records), ''),
    ('有GPS数据', '', sum(1 for r in intv_records if r['gps'] > 0), ''),
    ('无GPS数据(里程=0)', '', len(no_gps), ''),
]

for row_data in summary_rows:
    for c, val in enumerate(row_data, 1):
        ws1.cell(row=r, column=c, value=val)
    style_row(ws1, r, 4)
    r += 1

r += 1
ws1.merge_cells(start_row=r, start_column=1, end_row=r, end_column=8)
ws1.cell(row=r, column=1, value='二、干预车型达标翻转统计').font = SEC_FONT
r += 1

# By target type
flip_headers = ['考核目标', '干预车辆数', '达→未达标', '未→达标', '有GPS数据', '无GPS数据']
for c, h in enumerate(flip_headers, 1):
    ws1.cell(row=r, column=c, value=h)
style_header(ws1, r, 6)
r += 1

# Sort: most affected first
target_order = sorted(by_target.keys(), key=lambda t: -len(by_target[t]))
for i, tgt in enumerate(target_order):
    recs = by_target[tgt]
    ws1.cell(row=r, column=1, value=tgt)
    ws1.cell(row=r, column=2, value=len(recs))
    ws1.cell(row=r, column=3, value=sum(1 for x in recs if x['flip'] == '达→未达标'))
    ws1.cell(row=r, column=4, value=sum(1 for x in recs if x['flip'] == '未→达标'))
    ws1.cell(row=r, column=5, value=sum(1 for x in recs if x['gps'] > 0))
    ws1.cell(row=r, column=6, value=sum(1 for x in recs if x['gps'] == 0))
    style_row(ws1, r, 6, alt=(i%2==1))
    r += 1

col_widths1 = [14, 15, 15, 15, 18, 18, 18, 18]
for i, w in enumerate(col_widths1, 1):
    ws1.column_dimensions[get_column_letter(i)].width = w

# ── Sheet 2: 干预车辆明细 ──
ws2 = wb.create_sheet('干预车辆明细')
ws2.sheet_properties.tabColor = 'ED7D31'

ws2.merge_cells('A1:N1')
ws2.cell(row=1, column=1, value='5月干预车辆 — 原仪表盘里程 vs GPS里程 逐条对比').font = TITLE_FONT
ws2.cell(row=1, column=1).alignment = CC
ws2.row_dimensions[1].height = 28

detail_headers = ['车牌号', '部门', '销售经理', '客户名称', '考核目标',
                   '考核天数', '应考核里程(km)', '原仪表盘里程(km)', 'GPS行驶里程(km)',
                   '测试里程(km)', '原是否达标', '干预后达标', '翻转', 'GPS数据']
r2 = 3
for c, h in enumerate(detail_headers, 1):
    ws2.cell(row=r2, column=c, value=h)
style_header(ws2, r2, 14)
r2 += 1

# Sort: flip-down first, then no-GPS, then others
intv_records.sort(key=lambda r: (0 if r['flip'] == '达→未达标' else 1 if r['gps'] == 0 else 2, r['plate']))

for i, rec in enumerate(intv_records):
    ws2.cell(row=r2, column=1, value=rec['plate'])
    ws2.cell(row=r2, column=2, value=rec['dept'])
    ws2.cell(row=r2, column=3, value=rec['sales'])
    ws2.cell(row=r2, column=4, value=rec['client'])
    ws2.cell(row=r2, column=5, value=rec['target_name'])
    ws2.cell(row=r2, column=6, value=rec.get('days', ''))
    ws2.cell(row=r2, column=7, value=round(rec['target_km'], 2))
    ws2.cell(row=r2, column=8, value=round(rec['tbox'], 2))
    ws2.cell(row=r2, column=9, value=round(rec['gps'], 2))
    ws2.cell(row=r2, column=10, value=round(rec['test'], 2))
    ws2.cell(row=r2, column=11, value=rec['old_pass'])
    ws2.cell(row=r2, column=12, value=rec['new_pass'])
    # Flip column with color
    flip_cell = ws2.cell(row=r2, column=13, value=rec['flip'])
    if rec['flip'] == '达→未达标':
        flip_cell.font = RED_FONT
    ws2.cell(row=r2, column=14, value=rec['has_gps'])

    style_row(ws2, r2, 14, alt=(i%2==1))
    r2 += 1

col_widths2 = [12, 10, 10, 26, 20, 8, 14, 16, 16, 14, 10, 10, 10, 10]
for i, w in enumerate(col_widths2, 1):
    ws2.column_dimensions[get_column_letter(i)].width = w

# ── Sheet 3: 业务考核视图 (current, post-intervention) ──
ws3 = wb.create_sheet('业务考核视图')
ws3.sheet_properties.tabColor = '70AD47'

src_h = list(src_recs[0].keys())
for c, h in enumerate(src_h, 1):
    ws3.cell(row=1, column=c, value=h)
style_header(ws3, 1, len(src_h))

for i, rec in enumerate(src_recs):
    for c, h in enumerate(src_h, 1):
        val = rec.get(h)
        if val is not None:
            ws3.cell(row=i+2, column=c, value=val)
    style_row(ws3, i+2, len(src_h), alt=(i%2==1))

for i in range(1, len(src_h)+1):
    ws3.column_dimensions[get_column_letter(i)].width = 12

# ── Sheet 4: 干预车辆汇总(按部门+销售) ──
ws4 = wb.create_sheet('干预汇总(按部门销售)')
ws4.sheet_properties.tabColor = 'FFC000'

ws4.merge_cells('A1:F1')
ws4.cell(row=1, column=1, value='干预车辆 — 按部门+销售经理汇总').font = TITLE_FONT
ws4.cell(row=1, column=1).alignment = CC

# Aggregate
by_dept_sales = defaultdict(lambda: {'total': 0, 'flip_down': 0, 'flip_up': 0, 'no_gps': 0, 'has_gps': 0, 'gps_amount': 0})
for rec in intv_records:
    key = (rec['dept'], rec['sales'])
    by_dept_sales[key]['total'] += 1
    if rec['flip'] == '达→未达标':
        by_dept_sales[key]['flip_down'] += 1
    elif rec['flip'] == '未→达标':
        by_dept_sales[key]['flip_up'] += 1
    if rec['gps'] > 0:
        by_dept_sales[key]['has_gps'] += 1
        by_dept_sales[key]['gps_amount'] += rec['gps']
    else:
        by_dept_sales[key]['no_gps'] += 1

agg_headers = ['部门', '销售经理', '干预车辆数', '达→未达标', '未→达标', '无GPS数据']
r4 = 3
for c, h in enumerate(agg_headers, 1):
    ws4.cell(row=r4, column=c, value=h)
style_header(ws4, r4, 6)
r4 += 1

for (dept, sales), data in sorted(by_dept_sales.items()):
    ws4.cell(row=r4, column=1, value=dept)
    ws4.cell(row=r4, column=2, value=sales)
    ws4.cell(row=r4, column=3, value=data['total'])
    ws4.cell(row=r4, column=4, value=data['flip_down'])
    ws4.cell(row=r4, column=5, value=data['flip_up'])
    ws4.cell(row=r4, column=6, value=data['no_gps'])
    style_row(ws4, r4, 6)
    r4 += 1

col_widths4 = [14, 12, 12, 12, 12, 12]
for i, w in enumerate(col_widths4, 1):
    ws4.column_dimensions[get_column_letter(i)].width = w

# ── Save ──
out_path = 'new-kpi-202605_1/租赁任务考核_2026年5月_干预情况说明.xlsx'
wb.save(out_path)
print(f'✅ {out_path} ({len(wb.sheetnames)} sheets)')