#!/usr/bin/env python3 # -*- coding: utf-8 -*- """把 PI 级逾期清单按销售员拆成吴云、韩远怀两个独立文件。""" import os import sys import json import math from collections import defaultdict 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输出-工作类" sys.path.insert(0, os.path.join(ROOT, "脚本")) import build_overdue_pi_list as base OUTDIR = os.path.join(ROOT, "经营分析") PEOPLE = [ ("吴云", "2026年10月逾期科研课题组PI级清单_吴云_20261031_V02.xlsx"), ("韩远怀", "2026年10月逾期科研课题组PI级清单_韩远怀_20261031_V02.xlsx"), ] def style_sheet(ws, ncols): 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) def plan_for(g): """Return (催款人, 预计回款时间, 预计回款金额, 账款风险).""" net = g["net"] if net <= 0.01: return (g["sales"].split("、")[0], "—", 0.0, "低") if g["max_days"] > 365 or (g["overdue"] >= 10000 and g["over180_pct"] >= 0.60): risk = "高" date = "2026-11-30" amt = round(net * 0.80, 2) elif g["max_days"] > 180 or g["over180_pct"] >= 0.30: risk = "中" date = "2026-12-15" amt = round(net, 2) else: risk = "低" date = "2026-11-15" amt = round(net, 2) return (g["sales"].split("、")[0], date, amt, risk) def build_person(sales, filename, all_rows): rows = [x for x in all_rows if x["sales"] == sales] groups = base.agg(rows) total = sum(x["overdue"] for x in rows) prog = sum(x["progress"] for x in rows) net = total - prog crm_rows = [x for x in rows if x["source"] == "CRM补查"] aging = defaultdict(float) for x in rows: aging[base.bucket(x["days"])] += x["overdue"] top5 = sum(g["overdue"] for g in groups[:5]) top10 = sum(g["overdue"] for g in groups[:10]) over180 = aging["181-365天"] + aging["365天以上"] plans = {g["group"]: plan_for(g) for g in groups} plan_total = sum(v[2] for v in plans.values()) cust = defaultdict(lambda: [0.0, 0]) for x in rows: cust[x["customer"]][0] += x["overdue"]; cust[x["customer"]][1] += 1 wb = openpyxl.Workbook() wb.remove(wb.active) # 00 结论 ws = wb.create_sheet("00_结论与口径") base.widths(ws, [4, 20, 88]) base.style_title(ws, f"2026年10月逾期科研课题组｜{sales} PI级清单", 3, "截止：2026-09-30｜金额单位：元｜本文件仅含该销售员名下；另一部分见同系列另一文件") r = 4 ws.cell(r, 2, "一、总览").font = Font(name="微软雅黑", bold=True, size=11, color=base.NAVY) r += 1 conclusions = [ ("范围", f"{sales} 名下逾期 {len(rows)} 笔、{len(groups)} 个课题组/商机；本文件不含另一销售员数据。"), ("金额", f"逾期 {total:,.2f} 元；10月已回款 {prog:,.2f} 元（{prog/total*100:.1f}%）；净未回 {net:,.2f} 元。"), ("集中度", f"前5个组 {top5:,.2f} 元（{top5/total*100:.1f}%）；前10个组 {top10:,.2f} 元（{top10/total*100:.1f}%）。"), ("账龄", f"180天以上 {over180:,.2f} 元（{over180/total*100:.1f}%）；365天以上 {aging['365天以上']:,.2f} 元（{aging['365天以上']/total*100:.1f}%）。"), ("最大组", f"{groups[0]['group']} {groups[0]['overdue']:,.2f} 元；{groups[0]['n']} 笔，最长 {groups[0]['max_days']} 天，180天以上占 {groups[0]['over180_pct']*100:.1f}%。"), ("CRM补查", f"本文件有 {len(crm_rows)} 笔为本地未命中、逐单向飞书项目商机补查；明细见 03_CRM补查核验。"), ("催款计划", f"首轮计划回款 {plan_total:,.2f} 元，占净未回 {plan_total/net*100:.1f}%；高风险按净未回80%作为首轮目标，中/低按100%。"), ("核对", f"吴云 + 韩远怀 两个文件合计应等于原PI清单 254,673.18 元；本文件 {total:,.2f} 元。"), ] for k, v in conclusions: base.cell(ws, r, 2, k, bold=True, fill=base.PALE) base.cell(ws, r, 3, v) r += 1 r += 1 ws.cell(r, 2, "二、口径").font = Font(name="微软雅黑", bold=True, size=11, color=base.NAVY) r += 1 for k, v in [ ("逾期", "逾期天数可转数字且逾期金额>0；未逾期不计。"), ("课题组", "合同号→课题组：本地 order_ketizu_map.json；未命中的逐单查飞书项目「销售管理」商机。"), ("金额", "逾期金额=J列；10月已回款=M列；净未回=J-M。"), ("拆分", "按销售员字段拆分，不按客户拆分；同一客户若跨销售员，会在两边分别出现。"), ]: base.cell(ws, r, 2, k, bold=True, fill=base.GREY) base.cell(ws, r, 3, v) r += 1 r += 1 ws.cell(r, 2, "三、催款计划口径").font = Font(name="微软雅黑", bold=True, size=11, color=base.NAVY) r += 1 for k, v in [ ("催款人", "默认取该组销售员；跨销售员的组取首位销售员，需线下确认。"), ("账款风险", "高=逾期>365天 或（逾期金额≥1万且180天以上占比≥60%）；中=最长逾期>180天但未达高；低=最长逾期≤180天；净未回0按低、不催。"), ("预计回款时间", "高=2026-11-30；中=2026-12-15；低=2026-11-15；已回款=—。日期为建议，需销售确认。"), ("预计回款金额", "高=净未回×80%（首轮目标，剩余列入后续）；中/低=净未回全额；已回款=0。"), ]: base.cell(ws, r, 2, k, bold=True, fill=base.GREY) base.cell(ws, r, 3, v) r += 1 # 01 PI list ws = wb.create_sheet("01_PI级重点清单") base.widths(ws, [4, 20, 18, 11, 11, 6, 8, 7, 11, 11, 8]) base.style_title(ws, f"{sales}｜PI级重点清单（按逾期金额）", 11, "新增催款人/预计回款时间/预计回款金额/账款风险；风险与计划口径见00页") base.header(ws, 3, ["排名", "课题组/商机", "往来单位", "逾期金额", "净未回款", "最长天数", ">180天占比", "催款人", "预计回款时间", "预计回款金额", "账款风险"]) r = 4 for i, g in enumerate(groups, 1): collector, pdate, pamt, prisk = plans[g["group"]] fill = base.DARKRED if prisk == "高" else (base.YELLOW if prisk == "中" else None) base.cell(ws, r, 1, i, align=base.CENTER) base.cell(ws, r, 2, g["group"], bold=g["overdue"] >= 10000) base.cell(ws, r, 3, g["customers"]) base.cell(ws, r, 4, g["overdue"], base.NUM, base.RIGHT, bold=g["overdue"] >= 10000, fill=fill) base.cell(ws, r, 5, g["net"], base.NUM, base.RIGHT) base.cell(ws, r, 6, g["max_days"], base.NUM, base.CENTER, color=base.RED if g["max_days"] > 365 else "000000") base.cell(ws, r, 7, g["over180_pct"], base.PCT, base.CENTER, color=base.RED if g["over180_pct"] >= 0.6 else "000000") base.cell(ws, r, 8, collector, align=base.CENTER) base.cell(ws, r, 9, pdate, align=base.CENTER) base.cell(ws, r, 10, pamt, base.NUM, base.RIGHT, bold=prisk == "高") base.cell(ws, r, 11, prisk, align=base.CENTER, fill=fill, bold=prisk == "高") r += 1 base.cell(ws, r, 2, "合计", bold=True, fill=base.LIGHT) base.cell(ws, r, 4, total, base.NUM, base.RIGHT, bold=True, fill=base.LIGHT) base.cell(ws, r, 5, net, base.NUM, base.RIGHT, bold=True, fill=base.LIGHT) base.cell(ws, r, 10, plan_total, base.NUM, base.RIGHT, bold=True, fill=base.LIGHT) for c in [1, 3, 6, 7, 8, 9, 11]: base.cell(ws, r, c, "", fill=base.LIGHT) r += 1 ws.merge_cells(start_row=r, start_column=2, end_row=r, end_column=11) note = ws.cell(r, 2, "注：高风险预计回款金额按净未回80%作为首轮目标，中/低按100%；日期为建议，最终以销售确认。") note.font = Font(name="微软雅黑", size=8, color="595959") note.alignment = base.LEFT # 02 detail ws = wb.create_sheet("02_全部明细_含课题组") base.widths(ws, [5, 24, 9, 20, 19, 12, 8, 12, 12, 12, 24, 11, 12, 18]) base.style_title(ws, f"{sales}｜全部逾期明细（{len(rows)}笔）", 14, "按课题组、逾期金额排序；合同号已归一；CRM商机ID仅补查笔有值") base.header(ws, 3, ["序号", "往来单位", "销售员", "单据编号", "合同编号", "到期日", "逾期天数", "逾期金额", "10月已回", "净未回", "课题组/商机", "映射来源", "CRM商机ID", "备注"]) r = 4 for i, x in enumerate(sorted(rows, key=lambda z: (z["group"], -z["overdue"], z["days"])), 1): base.cell(ws, r, 1, i, align=base.CENTER) base.cell(ws, r, 2, x["customer"]) base.cell(ws, r, 3, x["sales"], align=base.CENTER) base.cell(ws, r, 4, x["doc"]) base.cell(ws, r, 5, x["contract"]) base.cell(ws, r, 6, x["due"].strftime("%Y-%m-%d") if isinstance(x["due"], datetime) else str(x["due"] or ""), align=base.CENTER) base.cell(ws, r, 7, x["days"], base.NUM, base.CENTER, color=base.RED if x["days"] > 365 else "000000") base.cell(ws, r, 8, x["overdue"], base.NUM, base.RIGHT) base.cell(ws, r, 9, x["progress"], base.NUM, base.RIGHT) base.cell(ws, r, 10, x["net"], base.NUM, base.RIGHT) base.cell(ws, r, 11, x["group"]) base.cell(ws, r, 12, x["source"], align=base.CENTER) base.cell(ws, r, 13, x["oppid"], align=base.CENTER) note = "已回款" if x["progress"] >= x["overdue"] - 0.01 else ("非课题组商机" if "非课题组" in x["group"] else "") base.cell(ws, r, 14, note) r += 1 # 03 CRM补查 ws = wb.create_sheet("03_CRM补查核验") base.widths(ws, [5, 20, 24, 14, 10, 12, 12, 14, 16]) base.style_title(ws, f"{sales}｜CRM商机补查（{len(crm_rows)}笔）", 9, "仅列本地未命中、逐单回飞书项目商机补齐的合同") base.header(ws, 3, ["序号", "合同编号", "课题组/商机", "往来单位", "逾期天数", "逾期金额", "CRM商机ID", "映射来源", "核验"]) r = 4 for i, x in enumerate(sorted(crm_rows, key=lambda z: -z["overdue"]), 1): base.cell(ws, r, 1, i, align=base.CENTER) base.cell(ws, r, 2, x["contract"]) base.cell(ws, r, 3, x["group"], bold=True) base.cell(ws, r, 4, x["customer"]) base.cell(ws, r, 5, x["days"], base.NUM, base.CENTER) base.cell(ws, r, 6, x["overdue"], base.NUM, base.RIGHT) base.cell(ws, r, 7, x["oppid"], align=base.CENTER) base.cell(ws, r, 8, "飞书项目 MQL 逐单查询", align=base.CENTER) base.cell(ws, r, 9, "已核验") r += 1 if crm_rows: base.cell(ws, r, 2, "合计", bold=True, fill=base.LIGHT) base.cell(ws, r, 6, sum(x["overdue"] for x in crm_rows), base.NUM, base.RIGHT, bold=True, fill=base.LIGHT) # 04 账龄与集中度 ws = wb.create_sheet("04_账龄与集中度") base.widths(ws, [24, 18, 14, 40]) base.style_title(ws, f"{sales}｜账龄与集中度", 4, "金额单位：元") base.header(ws, 3, ["账龄档", "逾期金额", "占本文件", "说明"]) r = 4 for k in ["0-30天", "31-60天", "61-90天", "91-180天", "181-365天", "365天以上"]: v = aging[k] base.cell(ws, r, 1, k, bold=k in ("181-365天", "365天以上")) base.cell(ws, r, 2, v, base.NUM, base.RIGHT) base.cell(ws, r, 3, v / total if total else 0, base.PCT, base.CENTER) base.cell(ws, r, 4, "重点风险" if k in ("181-365天", "365天以上") else "") r += 1 base.cell(ws, r, 1, "合计", bold=True, fill=base.LIGHT) base.cell(ws, r, 2, total, base.NUM, base.RIGHT, bold=True, fill=base.LIGHT) base.cell(ws, r, 3, 1, base.PCT, base.CENTER, bold=True, fill=base.LIGHT) r += 2 ws.cell(r, 1, "集中度").font = Font(name="微软雅黑", bold=True, size=10, color=base.NAVY) r += 1 base.header(ws, r, ["口径", "金额", "占本文件", "备注"]) r += 1 for label, amt, note in [("前5个组", top5, "第一梯队"), ("前10个组", top10, "优先攻坚"), ("其余组", total - top10, "长尾")]: base.cell(ws, r, 1, label) base.cell(ws, r, 2, amt, base.NUM, base.RIGHT) base.cell(ws, r, 3, amt / total if total else 0, base.PCT, base.CENTER) base.cell(ws, r, 4, note) r += 1 r += 2 ws.cell(r, 1, "按往来单位 TOP10").font = Font(name="微软雅黑", bold=True, size=10, color=base.NAVY) r += 1 base.header(ws, r, ["往来单位", "逾期金额", "占本文件", "笔数"]) r += 1 for k, (v, n) in sorted(cust.items(), key=lambda kv: -kv[1][0])[:10]: base.cell(ws, r, 1, k) base.cell(ws, r, 2, v, base.NUM, base.RIGHT) base.cell(ws, r, 3, v / total if total else 0, base.PCT, base.CENTER) base.cell(ws, r, 4, n, base.NUM, base.CENTER) r += 1 for ws in wb.worksheets: base.autoheight(ws) style_sheet(ws, ws.max_column) out = os.path.join(OUTDIR, filename) wb.save(out) return { "sales": sales, "file": out, "rows": len(rows), "groups": len(groups), "total": total, "progress": prog, "net": net, "crm_fixed": len(crm_rows), "top5": top5, "top10": top10, "top_groups": [(g["group"], g["overdue"]) for g in groups[:10]], } def main(): all_rows = base.parse() out = [] for sales, fn in PEOPLE: out.append(build_person(sales, fn, all_rows)) print(json.dumps(out, ensure_ascii=False, indent=1)) print("CHECK total", sum(x["total"] for x in out), "rows", sum(x["rows"] for x in out), "groups", sum(x["groups"] for x in out)) json.dump({"generated": datetime.now().strftime("%Y-%m-%d %H:%M:%S"), "files": out}, open(os.path.join(ROOT, "数据", "overdue_pi_split_20261031.json"), "w"), ensure_ascii=False, indent=1) if __name__ == "__main__": main()