# -*- coding: utf-8 -*-
"""重新分配「负责辖区」：离行外挂、外派点 属额外挂靠，不算正常辖区。
正常辖区 = 26 - 6 市外县域 - 2 额外 = 18 个，8 人分 2~3 个（差≤1）。
"""
import io, sys, openpyxl
sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8', errors='replace')

SRC = r"D:\workbbbbbb\workb\人员信息表.xlsx"

ALLOC = {
    "郑文斌": ["下城辖区", "信用小微事业部杭州中心", "总行部室"],
    "黎江余": ["城东辖区", "拱墅辖区"],
    "徐伟权": ["临平辖区", "钱塘辖区"],
    "孙银刚": ["城中辖区", "总行营业部辖区"],
    "毛晟渊": ["江干辖区", "滨江辖区", "萧山辖区"],
    "梁凯炯": ["科创金融事业总部_杭州科创中心", "余杭辖区"],
    "袁志伟": ["城西辖区", "杭州小微信贷中心"],
    "周苏海": ["西湖辖区", "上城辖区"],
}

wb = openpyxl.load_workbook(SRC)
ws = wb.worksheets[0]
hdr = {}
for c in ws[1]:
    v = str(c.value or "").strip()
    if v:
        hdr.setdefault(v, []).append(c.column)
C_NAME = hdr.get("名称", hdr.get("姓名", [1]))[0]
AREA_COLS = sorted(hdr.get("负责辖区", []))
print("AREA_COLS=", AREA_COLS)

cnt = 0
for r in range(2, ws.max_row + 1):
    nm = ws.cell(r, C_NAME).value
    if not nm:
        continue
    nm = str(nm).strip()
    ar = ALLOC.get(nm, [])
    for k, ci in enumerate(AREA_COLS):
        ws.cell(r, ci).value = ar[k] if k < len(ar) else None
    cnt += 1
    print("  %-4s %d 个: %s" % (nm, len(ar), ",".join(ar)))

total = sum(len(v) for v in ALLOC.values())
print("people=%d totalAreas=%d perCounts=%s" % (cnt, total, sorted(len(v) for v in ALLOC.values())))
wb.save(SRC)
print("saved", SRC)