#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""2026年10月逾期科研课题组 PI 级清单。

数据链：
  1) 逾期明细：2026年10月逾期应收回款计划-总表（回款进度） -科研.xlsx
     「附表_逾期&未逾期（汇总）」；逾期天数可转数字且逾期金额>0。
  2) 合同→课题组：数据/order_ketizu_map.json
     来源：最新 ERP 全年主档 商机(飞书)#（含课题组），生成 2026-10-29。
  3) 未命中的11个合同：逐单向飞书项目「销售管理」商机查询补齐，
     查询条件：商机关联销售订单 包含合同号；取商机名称即课题组。
"""
import os
import re
import json
import math
from collections import defaultdict, Counter
from datetime import datetime

import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.properties import PageSetupProperties

ROOT = "/Users/liuxinyuan/Desktop/Hermes输出-工作类"
SRC = "/Users/liuxinyuan/.hermes/profiles/zone-b/cache/documents/doc_190cba4be820_2026年10月逾期应收回款计划-总表（回款进度） -科研.xlsx"
MAP = os.path.join(ROOT, "数据", "order_ketizu_map.json")
OUT = os.path.join(ROOT, "经营分析", "2026年10月逾期科研课题组PI级清单_20261031_V01.xlsx")

# 11 个未命中合同：飞书项目「销售管理」商机 MQL 逐单查得
# contract -> (课题组/商机名称, CRM work_item_id)
CRM_FIX = {
    "ZHBR250321018": ("冯晔课题组", "5262628310"),
    "ZHBR250321016": ("朱永良课题组", "5159708943"),
    "ZHBR250623016": ("黄河课题组", "5159636795"),
    "ZHBR250623020": ("王青青课题组", "5159773476"),
    "ZHBR251210002": ("曾骥课题组", "6465697533"),
    "ZHBR260414060": ("秦志伟课题组", "6880273264"),
    "ZHBR251224021": ("李爱青课题组", "6503671568"),
    "ZHBR260608012": ("棉花遗传改良团队（导师：杨细燕）", "6329073560"),
    "ZHBR251204038": ("赵肃清课题组", "6436379662"),
    "ZHBR260608064": ("珠海聚鑫隆公司（非课题组）", "7010757376"),
    "ZHBR260203011": ("兰雨课题组", "6487588920"),
}

NAVY = "1F3864"; BLUE = "2F5597"; LIGHT = "D9E2F3"; PALE = "EEF3FA"
GREY = "F2F2F2"; WHITE = "FFFFFF"; RED = "C00000"; GREEN = "006100"
ORANGE = "FCE4D6"; YELLOW = "FFF2CC"; DARKRED = "F4CCCC"
THIN = Side(style="thin", color="BFBFBF")
BORDER = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
CENTER = Alignment(horizontal="center", vertical="center", wrap_text=True)
LEFT = Alignment(horizontal="left", vertical="center", wrap_text=True)
RIGHT = Alignment(horizontal="right", vertical="center")
NUM = '#,##0.00;[Red]-#,##0.00'
PCT = '0.0%;[Red]-0.0%'


def sf(v):
    try:
        if v is None or str(v).strip() in ("", "-", "—", "/"):
            return 0.0
        return float(str(v).replace(",", ""))
    except Exception:
        return 0.0


def norm_contract(c):
    m = re.search(r"(ZHBR\d{9,}|FO\d{8,})", str(c))
    return m.group(1) if m else str(c).strip()


def parse():
    wb = openpyxl.load_workbook(SRC, data_only=True)
    ws = wb["附表_逾期&未逾期（汇总）"]
    rows = []
    for r in range(4, ws.max_row + 1):
        cust = ws.cell(r, 3).value
        if not cust:
            continue
        try:
            days = int(float(ws.cell(r, 9).value))
        except Exception:
            continue
        amt = sf(ws.cell(r, 10).value)
        if amt <= 0:
            continue
        progress = sf(ws.cell(r, 13).value)
        contract = str(ws.cell(r, 5).value or "")
        rows.append({
            "dept": ws.cell(r, 1).value or "",
            "sales": ws.cell(r, 2).value or "",
            "customer": str(cust).strip(),
            "doc": ws.cell(r, 4).value or "",
            "contract": contract,
            "key": norm_contract(contract),
            "due": ws.cell(r, 7).value,
            "ar": sf(ws.cell(r, 8).value),
            "days": days,
            "overdue": amt,
            "progress": progress,
            "net": amt - progress,
        })
    m = json.load(open(MAP, encoding="utf-8"))["map"]
    for x in rows:
        k = x["key"]
        if k in CRM_FIX:
            x["group"], x["oppid"] = CRM_FIX[k]
            x["source"] = "CRM补查"
        else:
            x["group"] = m.get(k, "")
            x["source"] = "本地CRM映射" if x["group"] else "未匹配"
            x["oppid"] = ""
    return rows


def bucket(d):
    if d <= 30: return "0-30天"
    if d <= 60: return "31-60天"
    if d <= 90: return "61-90天"
    if d <= 180: return "91-180天"
    if d <= 365: return "181-365天"
    return "365天以上"


def agg(rows):
    a = {}
    for x in rows:
        g = x["group"] or "未匹配"
        if g not in a:
            a[g] = {"group": g, "overdue": 0, "progress": 0, "net": 0, "n": 0,
                    "max_days": 0, "over180": 0, "over365": 0, "customers": set(),
                    "sales": set(), "sources": set(), "oppids": set()}
        y = a[g]
        y["overdue"] += x["overdue"]; y["progress"] += x["progress"]; y["net"] += x["net"]
        y["n"] += 1; y["max_days"] = max(y["max_days"], x["days"])
        if x["days"] > 180: y["over180"] += x["overdue"]
        if x["days"] > 365: y["over365"] += x["overdue"]
        y["customers"].add(x["customer"]); y["sales"].add(x["sales"]); y["sources"].add(x["source"])
        if x["oppid"]: y["oppids"].add(x["oppid"])
    out = []
    for y in a.values():
        y["customers"] = "、".join(sorted(y["customers"]))
        y["sales"] = "、".join(sorted(y["sales"]))
        y["sources"] = "、".join(sorted(y["sources"]))
        y["oppids"] = "、".join(sorted(y["oppids"]))
        y["over180_pct"] = y["over180"] / y["overdue"] if y["overdue"] else 0
        if y["overdue"] >= 10000 and y["over180_pct"] >= 0.50:
            y["risk"] = "A类：金额大+账龄长"
        elif y["max_days"] > 180:
            y["risk"] = "B类：有长账龄"
        else:
            y["risk"] = "C类：新逾期/短期"
        out.append(y)
    return sorted(out, key=lambda z: -z["overdue"])


def style_title(ws, text, ncols, sub=None):
    ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=ncols)
    c = ws.cell(1, 1, text)
    c.font = Font(name="微软雅黑", bold=True, size=14, color=WHITE)
    c.fill = PatternFill("solid", fgColor=NAVY)
    c.alignment = LEFT
    ws.row_dimensions[1].height = 28
    if sub:
        ws.merge_cells(start_row=2, start_column=1, end_row=2, end_column=ncols)
        c2 = ws.cell(2, 1, sub)
        c2.font = Font(name="微软雅黑", size=9, color="404040")
        c2.fill = PatternFill("solid", fgColor=PALE)
        c2.alignment = LEFT
        ws.row_dimensions[2].height = 34


def header(ws, row, headers):
    for j, h in enumerate(headers, 1):
        c = ws.cell(row, j, h)
        c.font = Font(name="微软雅黑", bold=True, size=9, color=WHITE)
        c.fill = PatternFill("solid", fgColor=BLUE)
        c.alignment = CENTER
        c.border = BORDER
    ws.row_dimensions[row].height = 34


def cell(ws, r, c, v, fmt=None, align=None, bold=False, fill=None, color="000000"):
    x = ws.cell(r, c, v)
    x.font = Font(name="微软雅黑", size=9, bold=bold, color=color)
    x.border = BORDER
    x.alignment = align or LEFT
    if fmt: x.number_format = fmt
    if fill: x.fill = PatternFill("solid", fgColor=fill)


def widths(ws, arr):
    for i, w in enumerate(arr, 1):
        ws.column_dimensions[get_column_letter(i)].width = w


def autoheight(ws):
    for r in range(1, ws.max_row + 1):
        if r in (1, 2): continue
        lines = 1
        for c in ws[r]:
            if isinstance(c.value, str) and c.value:
                w = ws.column_dimensions[c.column_letter].width or 10
                lines = max(lines, math.ceil(len(c.value) / max(6, w / 2)))
        ws.row_dimensions[r].height = min(120, max(18, 14 * lines + 4))


def build():
    rows = parse()
    groups = agg(rows)
    total = sum(x["overdue"] for x in rows)
    total_prog = sum(x["progress"] for x in rows)
    total_net = total - total_prog
    matched = [x for x in rows if x["group"]]
    crm_fixed = [x for x in rows if x["source"] == "CRM补查"]
    unmatched = [x for x in rows if not x["group"]]
    aging = defaultdict(float)
    for x in rows: aging[bucket(x["days"])] += x["overdue"]
    sp = defaultdict(lambda: [0.0, 0])
    for x in rows:
        sp[x["sales"]][0] += x["overdue"]; sp[x["sales"]][1] += 1
    cum = 0; top10 = 0; top20 = 0
    for i, g in enumerate(groups, 1):
        cum += g["overdue"]
        if i == 10: top10 = cum
        if i == 20: top20 = cum

    wb = openpyxl.Workbook(); wb.remove(wb.active)

    # 00 结论
    ws = wb.create_sheet("00_结论与口径")
    widths(ws, [4, 20, 86])
    style_title(ws, "2026年10月逾期科研课题组｜PI级清单", 3,
                "截止：2026-09-30｜金额单位：元｜合同→课题组：本地CRM映射 + 11笔飞书项目商机逐单补查")
    r = 4
    ws.cell(r, 2, "一、先看结论").font = Font(name="微软雅黑", bold=True, size=11, color=NAVY)
    r += 1
    conclusions = [
        ("匹配结果", f"122笔全部落到课题组/商机：111笔由本地CRM映射命中，11笔本地未命中、逐单向飞书项目「销售管理」商机查询补齐。匹配率100%，无“未匹配”金额。"),
        ("总量", f"逾期 {total:,.2f} 元，122笔；10月已回款 {total_prog:,.2f} 元（{total_prog/total*100:.1f}%），净未回 {total_net:,.2f} 元；112/122笔本月未动。"),
        ("PI集中度", f"共 {len(groups)} 个课题组/商机。前10个课题组 {top10:,.2f} 元，占 {top10/total*100:.1f}%；前20个 {top20:,.2f} 元，占 {top20/total*100:.1f}%。比按单位看更分散，但仍应先攻前10。"),
        ("第一大组", f"戴春艳课题组（浙江中医药大学）{groups[0]['overdue']:,.2f} 元，占 {groups[0]['overdue']/total*100:.1f}%；5笔全部412天，来自同一合同 ZHBR250509007。"),
        ("第二大组", f"朱永良课题组（浙江大学）{groups[1]['overdue']:,.2f} 元，占 {groups[1]['overdue']/total*100:.1f}%；4笔，最长454天，>180天占 {groups[1]['over180_pct']*100:.1f}%。"),
        ("账龄", f"180天以上 {aging['181-365天']+aging['365天以上']:,.2f} 元，占 {((aging['181-365天']+aging['365天以上'])/total*100):.1f}%；365天以上 {aging['365天以上']:,.2f} 元，占 {aging['365天以上']/total*100:.1f}%。"),
        ("销售集中", "吴云 225,011.48 元（88.4%，84笔）；韩远怀 29,661.70 元（11.6%，38笔）。PI级清单里吴云名下仍是绝对大头。"),
        ("特殊项", "珠海聚鑫隆公司 246.50 元对应的是公司商机、不是课题组；已单列标注，不并入科研课题组口径。"),
    ]
    for k, v in conclusions:
        cell(ws, r, 2, k, bold=True, fill=PALE)
        cell(ws, r, 3, v)
        r += 1

    r += 1
    ws.cell(r, 2, "二、口径说明").font = Font(name="微软雅黑", bold=True, size=11, color=NAVY)
    r += 1
    notes = [
        ("逾期判据", "「附表_逾期&未逾期（汇总）」中逾期天数可转数字且逾期金额>0；未逾期行不计。"),
        ("合同归一", "合同编号可能是 `DC...-ZHBR...` 复合串，统一提取 `ZHBR`/`FO` 号后再匹配。"),
        ("本地映射", "数据/order_ketizu_map.json，来源：最新 ERP 全年主档 商机(飞书)#（含课题组），生成 2026-10-29；2533单，2023单有课题组，覆盖79.87%。"),
        ("CRM补查", "11个未命中合同逐单向飞书项目「销售管理」商机查询：WHERE 商机关联销售订单 包含合同号，取商机名称。明细见 03_CRM补查核验。"),
        ("金额", "逾期金额=J列；10月已回款=M列；净未回款=J-M；未做含税/非含税调整。"),
        ("用途", "内部催收优先级清单；不对外发送，不改变ERP/财务账。"),
    ]
    for k, v in notes:
        cell(ws, r, 2, k, bold=True, fill=GREY)
        cell(ws, r, 3, v)
        r += 1

    # 01 PI清单
    ws = wb.create_sheet("01_PI级重点清单")
    widths(ws, [4, 22, 22, 12, 12, 5, 7, 8, 7, 5])
    style_title(ws, "PI级重点清单（按逾期金额）", 10,
                "净未回=逾期-10月已回款；风险A=金额≥1万且180天以上≥50%；映射来源见02/03")
    header(ws, 3, ["排名", "课题组/商机", "往来单位", "逾期金额", "净未回款", "笔数", "最长天数", ">180天占比", "销售员", "风险"])
    r = 4
    for i, g in enumerate(groups, 1):
        fill = ORANGE if g["risk"].startswith("A") else (YELLOW if g["risk"].startswith("B") else None)
        cell(ws, r, 1, i, align=CENTER)
        cell(ws, r, 2, g["group"], bold=g["overdue"] >= 10000)
        cell(ws, r, 3, g["customers"])
        cell(ws, r, 4, g["overdue"], NUM, RIGHT, bold=g["overdue"] >= 10000, fill=fill)
        cell(ws, r, 5, g["net"], NUM, RIGHT)
        cell(ws, r, 6, g["n"], NUM, CENTER)
        cell(ws, r, 7, g["max_days"], NUM, CENTER, color=RED if g["max_days"] > 365 else "000000")
        cell(ws, r, 8, g["over180_pct"], PCT, CENTER, color=RED if g["over180_pct"] >= 0.6 else "000000")
        cell(ws, r, 9, g["sales"], align=CENTER)
        cell(ws, r, 10, g["risk"].split("：")[0], align=CENTER, fill=fill)
        r += 1
    cell(ws, r, 2, "合计", bold=True, fill=LIGHT)
    cell(ws, r, 4, total, NUM, RIGHT, bold=True, fill=LIGHT)
    cell(ws, r, 5, total_net, NUM, RIGHT, bold=True, fill=LIGHT)
    for c in [1, 3, 6, 7, 8, 9, 10]:
        cell(ws, r, c, "", fill=LIGHT)

    # 02 全部明细
    ws = wb.create_sheet("02_全部明细_含课题组")
    widths(ws, [5, 24, 9, 20, 19, 12, 8, 12, 12, 12, 24, 11, 12, 18])
    style_title(ws, "全部逾期明细（122笔，含课题组）", 14,
                "按课题组、逾期金额排序；合同号已归一；CRM商机ID仅CRM补查的11笔有值")
    header(ws, 3, ["序号", "往来单位", "销售员", "单据编号", "合同编号", "到期日", "逾期天数", "逾期金额", "10月已回", "净未回", "课题组/商机", "映射来源", "CRM商机ID", "备注"])
    r = 4
    idx = 0
    for x in sorted(rows, key=lambda z: (z["group"], -z["overdue"], z["days"])):
        idx += 1
        cell(ws, r, 1, idx, align=CENTER)
        cell(ws, r, 2, x["customer"])
        cell(ws, r, 3, x["sales"], align=CENTER)
        cell(ws, r, 4, x["doc"])
        cell(ws, r, 5, x["contract"])
        cell(ws, r, 6, x["due"].strftime("%Y-%m-%d") if isinstance(x["due"], datetime) else str(x["due"] or ""), align=CENTER)
        cell(ws, r, 7, x["days"], NUM, CENTER, color=RED if x["days"] > 365 else "000000")
        cell(ws, r, 8, x["overdue"], NUM, RIGHT)
        cell(ws, r, 9, x["progress"], NUM, RIGHT)
        cell(ws, r, 10, x["net"], NUM, RIGHT)
        cell(ws, r, 11, x["group"])
        cell(ws, r, 12, x["source"], align=CENTER)
        cell(ws, r, 13, x["oppid"], align=CENTER)
        note = "已回款" if x["progress"] >= x["overdue"] - 0.01 else ("非课题组商机" if "非课题组" in x["group"] else "")
        cell(ws, r, 14, note)
        r += 1

    # 03 CRM补查核验
    ws = wb.create_sheet("03_CRM补查核验")
    widths(ws, [5, 20, 24, 14, 10, 12, 12, 14, 16])
    style_title(ws, "11笔本地未命中合同的 CRM 商机补查", 9,
                "查询空间：飞书项目「销售管理」；条件：商机关联销售订单包含合同号；取商机名称")
    header(ws, 3, ["序号", "合同编号", "课题组/商机", "往来单位", "逾期天数", "逾期金额", "CRM商机ID", "映射来源", "核验"])
    r = 4
    for i, x in enumerate(sorted(crm_fixed, key=lambda z: -z["overdue"]), 1):
        cell(ws, r, 1, i, align=CENTER)
        cell(ws, r, 2, x["contract"])
        cell(ws, r, 3, x["group"], bold=True)
        cell(ws, r, 4, x["customer"])
        cell(ws, r, 5, x["days"], NUM, CENTER)
        cell(ws, r, 6, x["overdue"], NUM, RIGHT)
        cell(ws, r, 7, x["oppid"], align=CENTER)
        cell(ws, r, 8, "飞书项目 MQL 逐单查询", align=CENTER)
        cell(ws, r, 9, "已核验")
        r += 1
    cell(ws, r, 2, "合计", bold=True, fill=LIGHT)
    cell(ws, r, 6, sum(x["overdue"] for x in crm_fixed), NUM, RIGHT, bold=True, fill=LIGHT)

    # 04 账龄与集中度
    ws = wb.create_sheet("04_账龄与集中度")
    widths(ws, [24, 18, 14, 40])
    style_title(ws, "PI级账龄与集中度", 4, "金额单位：元")
    header(ws, 3, ["账龄档", "逾期金额", "占总额", "说明"])
    r = 4
    for k in ["0-30天", "31-60天", "61-90天", "91-180天", "181-365天", "365天以上"]:
        v = aging[k]
        cell(ws, r, 1, k, bold=k in ("181-365天", "365天以上"))
        cell(ws, r, 2, v, NUM, RIGHT)
        cell(ws, r, 3, v / total if total else 0, PCT, CENTER)
        cell(ws, r, 4, "重点风险" if k in ("181-365天", "365天以上") else "")
        r += 1
    cell(ws, r, 1, "合计", bold=True, fill=LIGHT)
    cell(ws, r, 2, total, NUM, RIGHT, bold=True, fill=LIGHT)
    cell(ws, r, 3, 1, PCT, CENTER, bold=True, fill=LIGHT)
    r += 2
    ws.cell(r, 1, "集中度").font = Font(name="微软雅黑", bold=True, size=10, color=NAVY)
    r += 1
    header(ws, r, ["口径", "金额", "占总额", "备注"])
    r += 1
    for label, amt, note in [("前5个课题组", sum(g["overdue"] for g in groups[:5]), "第一梯队"), ("前10个课题组", top10, "优先攻坚"), ("前20个课题组", top20, "覆盖近八成"), ("其余64个", total - top20, "长尾")]:
        cell(ws, r, 1, label)
        cell(ws, r, 2, amt, NUM, RIGHT)
        cell(ws, r, 3, amt / total if total else 0, PCT, CENTER)
        cell(ws, r, 4, note)
        r += 1
    r += 2
    ws.cell(r, 1, "按销售员").font = Font(name="微软雅黑", bold=True, size=10, color=NAVY)
    r += 1
    header(ws, r, ["销售员", "逾期金额", "占总额", "笔数"])
    r += 1
    for k, (v, n) in sorted(sp.items(), key=lambda kv: -kv[1][0]):
        cell(ws, r, 1, k)
        cell(ws, r, 2, v, NUM, RIGHT)
        cell(ws, r, 3, v / total if total else 0, PCT, CENTER)
        cell(ws, r, 4, n, NUM, CENTER)
        r += 1

    for ws in wb.worksheets:
        autoheight(ws)
        ws.sheet_view.zoomScale = 85
        ws.page_setup.orientation = "landscape"
        ws.page_setup.fitToWidth = 1
        ws.page_setup.fitToHeight = 0
        ws.sheet_properties.pageSetUpPr = PageSetupProperties(fitToPage=True)

    os.makedirs(os.path.dirname(OUT), exist_ok=True)
    wb.save(OUT)
    # audit JSON
    audit = {
        "generated": datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
        "source": os.path.basename(SRC),
        "total": total, "progress": total_prog, "net": total_net,
        "rows": len(rows), "groups": len(groups),
        "matched": len(matched), "crm_fixed": len(crm_fixed), "unmatched": len(unmatched),
        "top10": top10, "top20": top20,
        "mapping": [{"contract": x["contract"], "key": x["key"], "customer": x["customer"], "group": x["group"], "source": x["source"], "oppid": x["oppid"], "overdue": x["overdue"], "days": x["days"]} for x in rows],
    }
    json.dump(audit, open(os.path.join(ROOT, "数据", "overdue_pi_mapping_20261031.json"), "w"), ensure_ascii=False, indent=1)
    print("SAVED", OUT)
    print("rows", len(rows), "groups", len(groups), "matched", len(matched), "crm_fixed", len(crm_fixed), "unmatched", len(unmatched))
    print("total", total, "progress", total_prog, "net", total_net)
    print("top10", top10, top10/total, "top20", top20, top20/total)
    print("top5", [(g["group"], g["overdue"]) for g in groups[:5]])


if __name__ == "__main__":
    build()