102 lines
3.4 KiB
Python
102 lines
3.4 KiB
Python
#!/usr/bin/env python3
|
|
"""Fix May source: remove 4 vehicles from intervention, fix 粤A02239F GPS data."""
|
|
import os, shutil, openpyxl, warnings
|
|
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
|
|
from openpyxl.utils import get_column_letter
|
|
warnings.filterwarnings('ignore')
|
|
os.chdir('/Users/kkfluous/Downloads')
|
|
|
|
SRC = '租赁任务考核_2026年5月.xlsx'
|
|
BAK = '租赁任务考核_2026年5月_原版备份.xlsx'
|
|
|
|
wb = openpyxl.load_workbook(SRC)
|
|
ws = wb['业务考核视图']
|
|
h = [c.value for c in next(ws.iter_rows(min_row=1, max_row=1))]
|
|
print(f"Headers ({len(h)}):")
|
|
for i, hh in enumerate(h): print(f" [{i}] {hh}")
|
|
|
|
# Column indices
|
|
COL_PLATE = 0
|
|
COL_ACTUAL = h.index('实际行驶里程(km)') # GPS or仪表盘
|
|
COL_TBOX = h.index('原TBOX里程(km)')
|
|
COL_TEST = h.index('测试里程(km)')
|
|
COL_RATE = h.index('完成率(%)')
|
|
COL_PASS = h.index('是否达标')
|
|
|
|
YELLOW = PatternFill('solid', fgColor='FFFF00')
|
|
NO_FILL = PatternFill() # default/empty
|
|
|
|
# ── Fix 1: Remove 4 vehicles from intervention ──
|
|
REMOVE = {'粤A03186F', '粤A01128F', '粤A08391F', '粤A01396F'}
|
|
|
|
removed_count = 0
|
|
for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
|
|
plate = str(row[COL_PLATE].value or '').strip()
|
|
if plate not in REMOVE:
|
|
continue
|
|
|
|
tbox = row[COL_TBOX].value # this is the original仪表盘里程
|
|
if tbox is None or float(tbox or 0) <= 0:
|
|
continue
|
|
|
|
original_km = float(tbox)
|
|
target_km = float(row[h.index('应考核里程(km)')].value or 0)
|
|
|
|
# Restore: 实际行驶里程 = original仪表盘
|
|
row[COL_ACTUAL].value = original_km
|
|
row[COL_TBOX].value = None # clear TBOX (non-intervention)
|
|
row[COL_TEST].value = 0 # clear test
|
|
|
|
# Recalculate
|
|
rate = round(original_km / target_km * 100, 2) if target_km > 0 else 0
|
|
new_pass = '达标' if original_km >= target_km and target_km > 0 else '未达标'
|
|
row[COL_RATE].value = rate
|
|
row[COL_PASS].value = new_pass
|
|
|
|
# Remove yellow highlight
|
|
for c in range(len(h)):
|
|
row[c].fill = NO_FILL
|
|
|
|
removed_count += 1
|
|
print(f" 移除: {plate} 实际恢复={original_km} 达标={new_pass}")
|
|
|
|
print(f"移除干预: {removed_count} 辆")
|
|
|
|
# ── Fix 2: 粤A02239F GPS = 539.7 ──
|
|
plate = '粤A02239F'
|
|
for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
|
|
p = str(row[COL_PLATE].value or '').strip()
|
|
if p != plate:
|
|
continue
|
|
|
|
tbox = float(row[COL_TBOX].value or 0)
|
|
gps_new = 539.7
|
|
target_km = float(row[h.index('应考核里程(km)')].value or 0)
|
|
|
|
row[COL_ACTUAL].value = gps_new
|
|
row[COL_TEST].value = round(tbox - gps_new, 2)
|
|
rate = round(gps_new / target_km * 100, 2) if target_km > 0 else 0
|
|
new_pass = '达标' if gps_new >= target_km and target_km > 0 else '未达标'
|
|
row[COL_RATE].value = rate
|
|
row[COL_PASS].value = new_pass
|
|
|
|
print(f"\n修复: {plate} GPS={gps_new} TBOX={tbox} Test={round(tbox-gps_new,2)} 达标={new_pass}")
|
|
break
|
|
|
|
# ── Save ──
|
|
wb.save(SRC)
|
|
print(f"\n✅ 已保存: {SRC}")
|
|
|
|
# ── Quick stats ──
|
|
wb2 = openpyxl.load_workbook(SRC, data_only=True)
|
|
ws2 = wb2['业务考核视图']
|
|
h2 = [c.value for c in next(ws2.iter_rows(min_row=1, max_row=1))]
|
|
total = 0; pass_count = 0
|
|
for row in ws2.iter_rows(min_row=2, values_only=True):
|
|
if not row[0]: continue
|
|
r = dict(zip(h2, row))
|
|
total += 1
|
|
if r.get('是否达标') == '达标': pass_count += 1
|
|
wb2.close()
|
|
print(f"总记录={total} 达标={pass_count}")
|