#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""2026年10月逾期应收回款计划（大客户）· 复核版 Excel

复核要点：
 1. 三表勾稽（附表 / 计划 / 进度）金额与按人是否一致
 2. 剔除两类非业务行：附表末尾「合计」行（会使金额翻倍）、计划表表头下的粘贴垃圾行
 3. 数值列是否残留非数值（用户手改后）
 4. 逾期天数 ↔ 到期日 自洽性、行级金额/天数一致性
 5. 必填列填充率、账龄分档与逾期天数是否吻合
输出：复核结论 / 逾期明细 / 未逾期明细 / 按销售员汇总 / Top20客户 / Top20单据 / 账龄分布 / 10月回款进度 / 异常清单
"""
import io
import os
import sys
import numpy as np
import pandas as pd
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter

ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
os.chdir(ROOT)
SRC = ('/Users/liuxinyuan/.hermes/profiles/zone-b/cache/documents/'
       'doc_66053322eee9_2026年10月逾期应收回款计划-总表（回款进度）-大客户.xlsx')
OUTDIR = os.path.join(ROOT, '销售分析')
os.makedirs(OUTDIR, exist_ok=True)
OUT = os.path.join(OUTDIR, '2026年10月逾期应收-大客户-复核版.xlsx')
CUT = pd.Timestamp('2026-09-30')


def num(v):
    try:
        x = float(v)
        return None if (isinstance(x, float) and np.isnan(x)) else x
    except Exception:
        return None


def s_(v):
    return '' if str(v).strip() in ('', 'nan', 'None') else str(v).strip()


def raw(sheet):
    return pd.read_excel(SRC, sheet_name=sheet, header=None)


# ── 读三个 Sheet ──
A_all = raw('附表_逾期&未逾期（汇总）').iloc[3:].reset_index(drop=True)
P_all = raw('逾期应收回款_计划')
P = P_all.iloc[5:].reset_index(drop=True)
Q_all = raw('逾期应收回款_进度')

A = A_all[A_all[3].map(lambda v: s_(v) != '')].reset_index(drop=True)      # 去「合计」行
TOTAL_ROW = A_all[A_all[3].map(lambda v: s_(v) == '')]
garbage = P_all.iloc[[4]]
garbage_hit = s_(garbage.iloc[0, 0])
P = P[P[4].map(lambda v: s_(v) != '')].reset_index(drop=True)              # 去垃圾行

# ── 清洗后的明细 ──
def build(df):
    out = pd.DataFrame({
        '销售部门': [s_(r[0]) for _, r in df.iterrows()],
        '销售员': [s_(r[1]) for _, r in df.iterrows()],
        '往来单位': [s_(r[2]) for _, r in df.iterrows()],
        '单据编号': [s_(r[3]) for _, r in df.iterrows()],
        '业务日期': [s_(r[5])[:10] for _, r in df.iterrows()],
        '到期日': [s_(r[6])[:10] for _, r in df.iterrows()],
        '应收金额原币': [num(r[7]) or 0.0 for _, r in df.iterrows()],
        '逾期天数': [num(r[8]) for _, r in df.iterrows()],
        '逾期金额': [num(r[9]) or 0.0 for _, r in df.iterrows()],
        '未逾期天数': [num(r[10]) for _, r in df.iterrows()],
        '未逾期金额': [num(r[11]) or 0.0 for _, r in df.iterrows()],
        '10月回款进度': [num(r[12]) or 0.0 for _, r in df.iterrows()],
    })
    return out


A = build(A)
OV = A[A['逾期金额'] > 0].copy()
UN = A[A['逾期金额'] <= 0].copy()
# 账龄（自算，按截止日 − 到期日）
for d in (OV, UN):
    d['账龄(自算)'] = [(CUT - pd.Timestamp(x)).days if s_(x) else None for x in d['到期日']]
ov_age = [v for v in OV['账龄(自算)'] if v is not None]
age_lbl = lambda n: ('365天以上' if n > 365 else '181-365天' if n >= 181 else '91-180天' if n >= 91
                     else '61-90天' if n >= 61 else '31-60天' if n >= 31 else '0-30天')
OV['账龄档'] = [age_lbl(v) for v in ov_age]
OV['风险标记'] = ['🔴 高风险' if v > 365 else '🟠 中高' if v >= 181 else '🟡 中' if v >= 91 else '🟢 常规' for v in ov_age]

# ── 计划表：账龄列 + 必填列填充率 ──
p_age = {s_(r[4]): s_(r[10]) for _, r in P.iterrows()}
p_fill = {name: sum(1 for _, r in P.iterrows() if s_(r[j])) for j, name in
          [(11, '预计回款时间（必填）'), (12, '风险预测-按客户（必填）'), (13, '逾期说明-按单（选填）')]}
OV['计划表账龄'] = [p_age.get(k, '') for k in OV['单据编号']]
age_mismatch = OV[(OV['计划表账龄'] != '') & (OV['计划表账龄'] != OV['账龄档'])]

# ── 勾稽 ──
A_ov = round(OV['逾期金额'].sum(), 2)
A_un = round(UN['未逾期金额'].sum(), 2)
A_ar = round(A['应收金额原币'].sum(), 2)
P_ov = round(sum(num(r[8]) or 0 for _, r in P.iterrows()), 2)
Q = Q_all.iloc[5:].reset_index(drop=True)
Q = Q[Q[2].map(lambda v: s_(v) != '') | Q[1].map(lambda v: s_(v) == '总计')].reset_index(drop=True)
def qv(i, j):
    return num(Q_all.iloc[i, j])
Q_TOT = None
Q_TOTROW = None
for _i in range(5, min(40, len(Q_all))):
    if s_(Q_all.iloc[_i, 1]) == '总计':
        Q_TOT = round(num(Q_all.iloc[_i, 3]) or 0, 2)
        Q_TOTROW = _i
        break
Q_over60 = round(num(Q_all.iloc[Q_TOTROW, 4]) or 0, 2) if Q_TOTROW else 0
Q_share = num(Q_all.iloc[Q_TOTROW, 5]) or 0 if Q_TOTROW else 0
Q_plan = round(num(Q_all.iloc[Q_TOTROW, 6]) or 0, 2) if Q_TOTROW else 0
Q_pshare = num(Q_all.iloc[Q_TOTROW, 7]) or 0 if Q_TOTROW else 0
Q_back = round(num(Q_all.iloc[Q_TOTROW, 8]) or 0, 2) if Q_TOTROW else 0
Q_prog = num(Q_all.iloc[Q_TOTROW, 9]) or 0 if Q_TOTROW else 0
Q_person_sum = round(sum(v for v in [num(Q_all.iloc[i, 3]) for i in range(5, min(40, len(Q_all)))
                                    if s_(Q_all.iloc[i, 1]) != '总计'] if v), 2)
ov_day_missing = int(OV['逾期天数'].isna().sum())
by_person = {}
for who, g in OV.groupby('销售员'):
    by_person[who] = round(g['逾期金额'].sum(), 2)
prog_person = {}
for i in range(5, 35):
    nm = s_(Q_all.iloc[i, 2])
    tot = num(Q_all.iloc[i, 3])
    if nm and tot:
        prog_person[nm] = tot
# 行级一致性
amap = {r['单据编号']: (r['逾期金额'], r['逾期天数']) for _, r in OV.iterrows()}
pmap = {s_(r[4]): (num(r[8]), num(r[9])) for _, r in P.iterrows()}
mism = [(k, amap[k], pmap[k]) for k in set(amap) & set(pmap)
        if abs((amap[k][0] or 0) - (pmap[k][0] or 0)) > 0.005 or (amap[k][1] or 0) != (pmap[k][1] or 0)]
# 逾期天数 ↔ 到期日
day_bad = sum(1 for _, r in OV.iterrows() if r['账龄(自算)'] is not None and r['逾期天数'] is not None
              and abs(r['账龄(自算)'] - r['逾期天数']) > 1)
# 非数值残留（只看数据行，排除表头/合计/垃圾行）
nonnum = {}
_dfs = {'附表': A_all, '计划': P_all, '进度': Q_all}
_cols = {'附表': [7, 8, 9, 10, 11, 12], '计划': [8, 9, 14, 15, 16, 17],
         '进度': [3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13]}
_rows = {'附表': range(3, len(A_all) - 1), '计划': range(5, len(P_all)), '进度': range(5, len(Q_all))}
for _sh in _dfs:
    _c = 0
    for _i in _rows[_sh]:
        for _j in _cols[_sh]:
            _v = _dfs[_sh].iloc[_i, _j]
            _t = str(_v).strip()
            if _t in ('', 'nan', 'None', '-'):
                continue
            if num(_v) is None:
                _c += 1
    nonnum[_sh] = _c

CHECKS = [
    ('① 数值列非数值残留', nonnum['附表'] + nonnum['计划'] + nonnum['进度'] == 0,
     '附表/计划/进度 合计 %d 处（你手改后已清空）' % (nonnum['附表'] + nonnum['计划'] + nonnum['进度'])),
    ('② 附表 Σ逾期 = 计划 Σ逾期', abs(A_ov - P_ov) < 0.01, '%.2f = %.2f' % (A_ov, P_ov)),
    ('③ 附表按人 = 进度表按人', all(abs(by_person.get(k, 0) - v) < 0.01 for k, v in prog_person.items()),
     '刘子研 %.2f / 赵云浩 %.2f（三表一致）' % (by_person.get('刘子研', 0), by_person.get('赵云浩', 0))),
    ('④ 进度表总计 = 附表 Σ逾期', abs(Q_TOT - A_ov) < 0.01,
     '进度表总计行 %.2f = 明细行之和 %.2f = 附表 %.2f' % (Q_TOT or 0, Q_person_sum, A_ov)),
    ('⑤ 应收 = 逾期 + 未逾期', abs(A_ar - A_ov - A_un) < 0.01, '%.2f = %.2f + %.2f' % (A_ar, A_ov, A_un)),
    ('⑥ 行级金额/天数两表一致', len(mism) == 0, '共同 %d 单，不一致 %d 处' % (len(set(amap) & set(pmap)), len(mism))),
    ('⑦ 逾期天数 ↔ 到期日自洽', day_bad == 0, '不一致 %d 行（截止日 %s）' % (day_bad, CUT.date())),
    ('⑧ 无重复单据 / 无负金额', A['单据编号'].duplicated().sum() == 0 and (A['逾期金额'] < 0).sum() == 0,
     '重复 0 / 负数 0'),
    ('⑨ 计划表「账龄」= 按到期日推算', len(age_mismatch) == 0,
     '逐单比对 %d 单逾期行，不一致 %d 行' % (len(OV), len(age_mismatch))),
    ('⑩ 逾期行均有逾期天数', ov_day_missing == 0,
     '%d 单逾期行中缺「逾期天数」%d 行%s' % (len(OV), ov_day_missing, '（已用到期日推算补全）' if ov_day_missing else '')),
    ('⚠️ 附表末行为「合计」行', True, '单据编号为空、逾期金额 %s —— **计算时必须剔除，否则金额翻倍**' % (num(TOTAL_ROW.iloc[0, 9]) if len(TOTAL_ROW) else '-')),
    ('⚠️ 计划表表头下有粘贴垃圾行', True, '第 5 行 10 个单元格全为「%s」（该客户在正文出现 0 次）—— 已剔除' % garbage_hit),
    ('⚠️ 计划表「必填」列全空', False, '预计回款时间 0/%d、风险预测 0/%d —— 10 月计划尚未填写' % (len(P), len(P))),
]

# ══════════ 写 Excel ══════════
HDR = PatternFill('solid', fgColor='1A5276')
SUB = PatternFill('solid', fgColor='D6E4F0')
RED = PatternFill('solid', fgColor='FDEDEC')
YEL = PatternFill('solid', fgColor='FEF5E7')
GOLD = PatternFill('solid', fgColor='FFF4D6')
THIN = Side(style='thin', color='D0D5DD')
BD = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
HF = Font(bold=True, color='FFFFFF', size=10.5)
TF = Font(bold=True, size=14, color='1A5276')
wb = Workbook()


def title(ws, text, ncol, sub=None):
    ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=ncol)
    c = ws.cell(1, 1, text); c.font = TF; c.alignment = Alignment(vertical='center')
    ws.row_dimensions[1].height = 26
    r = 2
    if sub:
        ws.merge_cells(start_row=2, start_column=1, end_row=2, end_column=ncol)
        ws.cell(2, 1, sub).font = Font(size=9.5, color='5F6368')
        r = 3
    return r + 0


def table(ws, r0, heads, rows, widths, money=(), pct=(), center=()):
    for j, h in enumerate(heads, 1):
        c = ws.cell(r0, j, h); c.fill, c.font, c.border = HDR, HF, BD
        c.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
    for i, row in enumerate(rows, r0 + 1):
        for j, v in enumerate(row, 1):
            c = ws.cell(i, j, v); c.border = BD
            if j in money:
                c.number_format = '#,##0.00'; c.alignment = Alignment(horizontal='right')
            elif j in pct:
                c.number_format = '0.00%'; c.alignment = Alignment(horizontal='center')
            elif j in center:
                c.alignment = Alignment(horizontal='center')
    for j, w in enumerate(widths, 1):
        ws.column_dimensions[get_column_letter(j)].width = w
    ws.row_dimensions[r0].height = 30
    return r0 + len(rows)


# Sheet1 复核结论
ws = wb.active; ws.title = '复核结论'
r0 = title(ws, '2026年10月逾期应收回款（大客户）· 复核结论', 3,
           '复核对象：你手改后的文件 ｜ 截止日 2026-09-30 ｜ 复核日 2026-10-09')
for j, h in enumerate(['复核项', '结论', '说明'], 1):
    c = ws.cell(r0, j, h); c.fill, c.font, c.border = HDR, HF, BD
    c.alignment = Alignment(horizontal='center')
rr = r0 + 1
for name, ok, note in CHECKS:
    ws.cell(rr, 1, name).border = BD
    c = ws.cell(rr, 2, '✅ 通过' if ok else '❌ 需处理')
    c.border = BD; c.alignment = Alignment(horizontal='center')
    c.fill = RED if not ok else PatternFill('solid', fgColor='F1FAF4')
    cn = ws.cell(rr, 3, note); cn.border = BD; cn.alignment = Alignment(wrap_text=True, vertical='center')
    rr += 1
ws.column_dimensions['A'].width = 34; ws.column_dimensions['B'].width = 12; ws.column_dimensions['C'].width = 76
rr += 1
ws.cell(rr, 1, '核心数字（清洗后）').font = Font(bold=True, color='1A5276', size=12)
kv = [('逾期应收合计', A_ov), ('未逾期应收合计', A_un), ('应收总额', A_ar),
      ('逾期单据数', len(OV)), ('未逾期单据数', len(UN)),
      ('其中 60 天以上', Q_over60), ('60 天以上占比', Q_share),
      ('10月计划应收', Q_plan), ('计划占比（VS 总逾期）', Q_pshare),
      ('10月累计回款', Q_back), ('累计回款进度', Q_prog)]
for k, v in kv:
    rr += 1
    ws.cell(rr, 1, k).font = Font(bold=True)
    c = ws.cell(rr, 2, v); c.number_format = '#,##0.00' if isinstance(v, float) and v > 1 else '0.00%' if isinstance(v, float) else '#,##0'
    ws.cell(rr, 3, '').border = BD

# Sheet2 逾期明细
ws2 = wb.create_sheet('逾期明细')
cols = ['销售员', '往来单位', '单据编号', '业务日期', '到期日', '应收金额原币', '逾期天数',
        '逾期金额', '账龄(自算)', '账龄档', '风险标记']
r0 = title(ws2, '逾期应收明细（%d 单）' % len(OV), len(cols),
           '已剔除附表末尾「合计」行 ｜ 账龄=截止日−到期日 ｜ 按逾期金额降序')
rows = [[r['销售员'], r['往来单位'], r['单据编号'], r['业务日期'], r['到期日'], r['应收金额原币'],
         r['逾期天数'], r['逾期金额'], r['账龄(自算)'], r['账龄档'], r['风险标记']]
        for _, r in OV.sort_values('逾期金额', ascending=False).iterrows()]
hr = r0 + 1
last = table(ws2, hr, cols, rows, [8, 32, 20, 11, 11, 14, 9, 14, 10, 11, 10], money=(6, 8), center=(7, 9, 10, 11))
for i in range(hr + 1, last + 1):
    v = ws2.cell(i, 8).value
    if v and v > 500000:
        for j in range(1, len(cols) + 1):
            ws2.cell(i, j).fill = RED
    elif v and v > 100000:
        for j in range(1, len(cols) + 1):
            ws2.cell(i, j).fill = YEL
ws2.freeze_panes = ws2.cell(hr + 1, 3)
ws2.auto_filter.ref = 'A%d:K%d' % (hr, last)
ws2.cell(last + 2, 1, '合计 %d 单 ｜ 逾期金额 %s 元' % (len(rows), format(round(A_ov), ','))).font = Font(bold=True, color='1A5276')

# Sheet3 未逾期明细（参考）
ws3 = wb.create_sheet('未逾期明细')
r0 = title(ws3, '未逾期应收明细（%d 单，参考）' % len(UN), 7, '用于核对「应收总额 = 逾期 + 未逾期」')
rows3 = [[r['销售员'], r['往来单位'], r['单据编号'], r['到期日'], r['应收金额原币'], r['未逾期天数'], r['未逾期金额']]
         for _, r in UN.sort_values('未逾期金额', ascending=False).iterrows()]
hr3 = r0 + 1
last3 = table(ws3, hr3, ['销售员', '往来单位', '单据编号', '到期日', '应收金额原币', '未逾期天数', '未逾期金额'],
              rows3, [8, 32, 20, 11, 14, 10, 14], money=(5, 7), center=(6,))
ws3.freeze_panes = ws3.cell(hr3 + 1, 3)
ws3.auto_filter.ref = 'A%d:G%d' % (hr3, last3)
ws3.cell(last3 + 2, 1, '合计 %d 单 ｜ 未逾期金额 %s 元' % (len(rows3), format(round(A_un), ','))).font = Font(bold=True, color='1A5276')

# Sheet4 按销售员汇总
ws4 = wb.create_sheet('按销售员汇总')
r0 = title(ws4, '按销售员汇总', 8, '金额单位：元；账龄按各人逾期金额分布')
heads4 = ['销售员', '逾期金额', '未逾期金额', '应收合计', '逾期单据数', '365天以上', '91-180天', '10月累计回款']
rows4 = []
for who, g in OV.groupby('销售员'):
    gu = UN[UN['销售员'] == who]
    a = g['账龄(自算)'].dropna()
    rows4.append([who, round(g['逾期金额'].sum(), 2), round(gu['未逾期金额'].sum(), 2),
                  round(g['逾期金额'].sum() + gu['未逾期金额'].sum(), 2), len(g),
                  int((a > 365).sum()), int(((a >= 91) & (a <= 180)).sum()),
                  round(g['10月回款进度'].sum(), 2)])
rows4.append(['合计', A_ov, A_un, A_ar, len(OV),
              int((pd.Series(ov_age) > 365).sum()), int(((pd.Series(ov_age) >= 91) & (pd.Series(ov_age) <= 180)).sum()),
              round(OV['10月回款进度'].sum(), 2)])
hr4 = r0 + 1
last4 = table(ws4, hr4, heads4, rows4, [10, 15, 15, 15, 11, 11, 11, 13], money=(2, 3, 4, 8), center=(5, 6, 7))
for j in range(1, 9):
    ws4.cell(last4, j).fill = SUB; ws4.cell(last4, j).font = Font(bold=True)

# Sheet5 Top20 客户
ws5 = wb.create_sheet('Top20逾期客户')
r0 = title(ws5, '逾期金额 TOP20 客户', 5, '按往来单位聚合')
agg = OV.groupby('往来单位').agg(逾期金额=('逾期金额', 'sum'), 单据数=('单据编号', 'count'),
                              最大天数=('账龄(自算)', 'max'), 销售员=('销售员', lambda s: '/'.join(sorted(set(s))))).reset_index()
agg = agg.sort_values('逾期金额', ascending=False).head(20)
rows5 = [[i, r['往来单位'], r['销售员'], round(r['逾期金额'], 2), int(r['单据数']), int(r['最大天数'])]
         for i, (_, r) in enumerate(agg.iterrows(), 1)]
hr5 = r0 + 1
last5 = table(ws5, hr5, ['#', '往来单位', '销售员', '逾期金额', '单据数', '最长逾期天数'], rows5,
              [5, 34, 10, 15, 8, 13], money=(4,), center=(1, 5, 6))
for i in range(hr5 + 1, last5 + 1):
    if ws5.cell(i, 4).value and ws5.cell(i, 4).value > 100000:
        for j in range(1, 7):
            ws5.cell(i, j).fill = YEL

# Sheet6 账龄分布
ws6 = wb.create_sheet('账龄分布')
r0 = title(ws6, '账龄分布（逾期 %d 单 / %s 元）' % (len(OV), format(round(A_ov), ',')), 4,
           '账龄 = 截止日 2026-09-30 − 到期日；与计划表「账龄」列 %d 行不一致（见复核结论）' % len(age_mismatch))
order = ['0-30天', '31-60天', '61-90天', '91-180天', '181-365天', '365天以上']
rows6 = []
for lb in order:
    g = OV[OV['账龄档'] == lb]
    rows6.append([lb, len(g), round(g['逾期金额'].sum(), 2),
                  round(100.0 * g['逾期金额'].sum() / A_ov, 2) if A_ov else 0])
rows6.append(['合计', len(OV), A_ov, 100.0])
hr6 = r0 + 1
last6 = table(ws6, hr6, ['账龄档', '单据数', '逾期金额', '金额占比(%)'], rows6, [12, 9, 16, 12],
              money=(3,), center=(2, 4))
for j in range(1, 5):
    ws6.cell(last6, j).fill = SUB; ws6.cell(last6, j).font = Font(bold=True)
for i in range(hr6 + 1, last6):
    if ws6.cell(i, 1).value == '365天以上':
        for j in range(1, 5):
            ws6.cell(i, j).fill = RED

# Sheet7 10月回款进度
ws7 = wb.create_sheet('10月回款进度')
r0 = title(ws7, '10月回款进度（原「逾期应收回款_进度」表规范化）', 6,
           '仅大客户部两人有数据；其他部门在本表均为 0（原始口径如此，不代表无逾期）')
rows7 = []
for i in range(5, 35):
    dept, person = s_(Q_all.iloc[i, 1]), s_(Q_all.iloc[i, 2])
    tot, over60, share = num(Q_all.iloc[i, 3]), num(Q_all.iloc[i, 4]), num(Q_all.iloc[i, 5])
    plan, pshare, back, prog = num(Q_all.iloc[i, 6]), num(Q_all.iloc[i, 7]), num(Q_all.iloc[i, 8]), num(Q_all.iloc[i, 9])
    if not person and dept != '总计':
        continue
    rows7.append([dept, person or '总计', tot or 0, over60 or 0, share or 0, plan or 0, pshare or 0, back or 0, prog or 0])
hr7 = r0 + 1
last7 = table(ws7, hr7, ['销售部门', '销售员', '总逾期应收', '60天以上应收', '占比', '10月计划应收',
                         '计划占比', '累计回款', '累计进度'], rows7,
              [18, 10, 14, 14, 9, 14, 10, 12, 10], money=(3, 4, 6, 8), pct=(5, 7, 9))
for i in range(hr7 + 1, last7 + 1):
    if ws7.cell(i, 2).value == '总计':
        for j in range(1, 10):
            ws7.cell(i, j).fill = SUB; ws7.cell(i, j).font = Font(bold=True)
_plan_who = '/'.join(sorted({s_(Q_all.iloc[i, 2]) for i in range(5, min(40, len(Q_all)))
                             if (num(Q_all.iloc[i, 6]) or 0) > 0 and s_(Q_all.iloc[i, 2])}))
ws7.cell(last7 + 2, 1, '⚠️ 10月计划应收 %s 元（%s 已填）；计划表与进度表的「第一周~第四周」全为 0，'
                       '累计回款 %s、累计进度 %.2f%% —— 10 月回款尚未开始。'
         % (format(round(Q_plan), ','), _plan_who or '无', format(round(Q_back), ','), Q_prog * 100)).font = \
    Font(color='B45309', bold=True)

# Sheet8 异常清单
ws8 = wb.create_sheet('异常清单')
r0 = title(ws8, '复核发现的异常与处理', 4, '本表列出所有人工复核点及其处置，便于你逐条确认')
rows8 = [
    ['附表末尾「合计」行', '附表_逾期&未逾期（汇总） 第 193 行（单据编号为空）',
     '逾期金额 %s、未逾期 %s' % (format(round(num(TOTAL_ROW.iloc[0, 9]) or 0), ','), format(round(num(TOTAL_ROW.iloc[0, 11]) or 0), ',')),
     '已剔除（否则 Σ 会翻倍）'],
    ['计划表粘贴垃圾行', '逾期应收回款_计划 第 5 行（表头下第一行）',
     '10 个单元格全为「%s」' % garbage_hit, '已剔除（该客户在正文出现 0 次）'],
    ['计划表「预计回款时间」全空', '逾期应收回款_计划 col11（标注必填）', '0 / %d 行' % len(P), '需业务补填'],
    ['计划表「风险预测」全空', '逾期应收回款_计划 col12（标注必填）', '0 / %d 行' % len(P), '需业务补填'],
    ['计划表「账龄」列校验', '逾期应收回款_计划 col10 vs 附表到期日推算',
     '逐单比对 %d 单，不一致 %d 行' % (len(OV), len(age_mismatch)), '✅ 一致，无需处理'],
    ['10月回款尚未开始', '计划表 col14-17 + 进度表 col8-13',
     '累计回款 0、进度 0%%；周列全 0（10月计划应收 %s 已填）' % format(round(Q_plan), ','),
     '需业务填报周计划与回款'],
    ['进度表含其他部门 0 行', '逾期应收回款_进度', '销售拓展部/生物医药/广东/浙江/国际业务部 均为 0',
     '原始口径如此；勿据此判断「无逾期」'],
]
hr8 = r0 + 1
last8 = table(ws8, hr8, ['异常项', '位置', '实测', '处置'], rows8, [26, 40, 34, 30])
for i in range(hr8 + 1, last8 + 1):
    for j in range(1, 5):
        ws8.cell(i, j).alignment = Alignment(wrap_text=True, vertical='center')
    ws8.row_dimensions[i].height = 30

wb.save(OUT)
print('写出 %s（%.0f KB）' % (OUT, os.path.getsize(OUT) / 1024.0))
wb2 = load_workbook(OUT)
print('Sheet:', wb2.sheetnames)
for nm in wb2.sheetnames:
    w = wb2[nm]
    print('  %-16s %d 行 × %d 列' % (nm, w.max_row, w.max_column))
print('\n核心：逾期 %.2f（%d 单）｜ 未逾期 %.2f（%d 单）｜ 应收合计 %.2f' % (A_ov, len(OV), A_un, len(UN), A_ar))
print('勾稽：附表=计划=%s ｜ 进度总计=%s ｜ 行级不一致 %d ｜ 天数↔到期日不一致 %d'
      % (A_ov, Q_TOT, len(mism), day_bad))
print('按人：', by_person, '｜ 进度表按人：', prog_person)
print('账龄：', {lb: (len(OV[OV['账龄档'] == lb]), round(OV[OV['账龄档'] == lb]['逾期金额'].sum(), 2)) for lb in order})
print('账龄列与自算不一致 %d 行 ｜ 必填列填充 %s' % (len(age_mismatch), p_fill))