import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from collections import defaultdict

FP = '/Users/kkfluous/Downloads/租赁任务考核_2026年5月.xlsx'

def num(x):
    try: return float(x or 0)
    except: return 0.0

# 读数据
wb_r = openpyxl.load_workbook(FP, data_only=True)
ws_r = wb_r['业务考核视图']
h = [c.value for c in next(ws_r.iter_rows(min_row=1, max_row=1))]
recs = [dict(zip(h, row)) for row in ws_r.iter_rows(min_row=2, values_only=True) if row[0]]

# 备份仪表盘
import os
bak = '/Users/kkfluous/Downloads/租赁任务考核_2026年5月_原版备份.xlsx'
bak_map = {}
if os.path.exists(bak):
    wb_b = openpyxl.load_workbook(bak, data_only=True)
    for row in wb_b['业务考核视图'].iter_rows(min_row=2, values_only=True):
        p = str(row[0]).strip() if row[0] else ''
        bak_map[p] = num(row[14])
    wb_b.close()

test_recs = [r for r in recs if num(r.get('测试里程(km)')) > 0]

flipped = []
for r in test_recs:
    p = str(r['车牌号']).strip()
    old_km = bak_map.get(p, 0)
    new_km = num(r.get('实际行驶里程(km)'))
    t = num(r.get('应考核里程(km)'))
    if t <= 0: continue
    if old_km >= t and new_km < t:
        flipped.append(r)

# 按车型+部门+销售分组
by_target = defaultdict(lambda: defaultdict(lambda: defaultdict(int)))
for r in flipped:
    tgt = r.get('考核目标', '未知车型')
    dept = r.get('部门名称', '')
    sales = r.get('销售经理', '')
    by_target[tgt][dept][sales] += 1

wb_r.close()

# 写sheet
wb = openpyxl.load_workbook(FP)
ws = wb['测试与考核情况说明']

SUB   = PatternFill('solid', fgColor='D9E1F2')
HEAD  = PatternFill('solid', fgColor='4472C4')
ALT   = PatternFill('solid', fgColor='F2F2F2')
THIN  = Side('thin', color='B4B4B4')
BD    = Border(THIN, THIN, THIN, THIN)
CC = Alignment(horizontal='center', vertical='center', wrap_text=True)
LL = Alignment(horizontal='left', vertical='center', wrap_text=True)

# 找到section 二所在的行
section2_row = None
for r in range(1, ws.max_row + 1):
    v = ws.cell(row=r, column=1).value
    if v and '二、测试车辆考核达标情况说明' in str(v):
        section2_row = r
        break

if section2_row:
    # 删除section 二及之后的所有行（保留数据重新写）
    # 找到从section2_row+1开始到section三之前的所有行
    section3_row = None
    for r in range(section2_row + 1, ws.max_row + 1):
        v = ws.cell(row=r, column=1).value
        if v and '三、里程' in str(v):
            section3_row = r
            break
    if not section3_row:
        section3_row = ws.max_row + 1

    # 清除section2+1 到 section3-1 的内容
    for r in range(section2_row + 1, section3_row):
        for c in range(1, 7):
            ws.cell(row=r, column=c).value = None
            ws.cell(row=r, column=c).fill = PatternFill()

    r = section2_row + 1

    # 描述行
    ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=6)
    ws.cell(row=r, column=1,
        value=f'共{len(test_recs)}条考核车辆涉及测试；扣减其测试里程后，共 {len(flipped)} 条考核车辆记录由"达标"转判为"未达标"。')
    ws.cell(row=r, column=1).font = Font(size=10); ws.cell(row=r, column=1).alignment = LL
    ws.row_dimensions[r].height = 28
    r += 2

    # 按车型展示，多车型一行
    targets_sorted = sorted(by_target.keys())
    triples = [targets_sorted[i:i+2] for i in range(0, len(targets_sorted), 2)]

    for target_group in triples:
        for ti, tgt in enumerate(target_group):
            dept_data = by_target[tgt]
            dept_order = sorted(dept_data.keys(), key=lambda d: -sum(dept_data[d].values()))
            total_flip = sum(sum(s.values()) for s in dept_data.values())

            base_col = ti * 3 + 1  # 每车型占3列(车型名|部门-销售|数量)

            # 车型标题行
            ws.merge_cells(start_row=r, start_column=base_col, end_row=r, end_column=base_col+2)
            ws.cell(row=r, column=base_col, value=f'{tgt}（{total_flip}辆）').fill = SUB
            ws.cell(row=r, column=base_col).font = Font(bold=True, color='1F4E78')
            ws.cell(row=r, column=base_col).alignment = CC
            for j in range(base_col, base_col+3):
                ws.cell(row=r, column=j).border = BD
            r += 1

            # 部门-销售-数量表头
            for j, hh in enumerate(['部门', '销售经理', '数量'], base_col):
                ws.cell(row=r, column=j, value=hh).fill = HEAD
                ws.cell(row=r, column=j).font = Font(bold=True, color='FFFFFF')
                ws.cell(row=r, column=j).alignment = CC
                ws.cell(row=r, column=j).border = BD
            r += 1

            # 数据行
            row_idx = 0
            for dept in dept_order:
                for sales in sorted(dept_data[dept].keys()):
                    cnt = dept_data[dept][sales]
                    ws.cell(row=r, column=base_col, value=dept).alignment = CC
                    ws.cell(row=r, column=base_col+1, value=sales).alignment = CC
                    ws.cell(row=r, column=base_col+2, value=cnt).alignment = CC
                    for j in range(base_col, base_col+3):
                        ws.cell(row=r, column=j).border = BD
                        if row_idx % 2 == 1:
                            ws.cell(row=r, column=j).fill = ALT
                    ws.row_dimensions[r].height = 22
                    r += 1
                    row_idx += 1
            # 小计行
            ws.cell(row=r, column=base_col, value='合计').font = Font(bold=True)
            ws.cell(row=r, column=base_col).alignment = CC
            ws.cell(row=r, column=base_col+2, value=total_flip).font = Font(bold=True)
            ws.cell(row=r, column=base_col+2).alignment = CC
            for j in range(base_col, base_col+3):
                ws.cell(row=r, column=j).fill = SUB
                ws.cell(row=r, column=j).border = BD
            r += 2

        # 跳到下一个triple的起始行
        # Find max row reached in this triplet
        pass  # r already at right position

    r += 1

# 列宽
for i, w in enumerate([18, 22, 22, 50, 18, 18], 1):
    ws.column_dimensions[get_column_letter(i)].width = w

wb.save(FP)
print(f'✅ 更新完成')