#!/usr/bin/env python3 # -*- coding: utf-8 -*- """佰奥莱博集团（4 个抬头）客户 360 一页纸 — 2024–2026.10 口径：西安佰奥莱博 + 西安瑞佰 + 上海基灵生物 + 基灵（西安）；标准销售订单、非赠品。 基灵定制码按《西安基灵下单货号品名定制对照表-2026V02》折算为我方货号。 """ import datetime as dt import json import os import re from collections import defaultdict import openpyxl from docx import Document from docx.enum.table import WD_TABLE_ALIGNMENT from docx.enum.text import WD_ALIGN_PARAGRAPH from docx.oxml import OxmlElement from docx.oxml.ns import qn from docx.shared import Cm, Pt, RGBColor BASE = '/Users/liuxinyuan/Desktop/Hermes输出-工作类' OUT = BASE + '/销售分析/客户360_西安佰奥莱博_2026-10-21.docx' LOGO = os.path.expanduser('~/work/公司资料/logo_transparent.png') SLOGAN = '守护生命健康，缔造美好生活' BLUE = RGBColor(0x1F, 0x4E, 0x79) GREY = RGBColor(0x6B, 0x7C, 0x8F) RED = RGBColor(0xC0, 0x39, 0x2B) P = {'2024': '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/2024和2025历史数据/2024全年订单.xlsx', '2025': '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/2024和2025历史数据/2025全年订单.xlsx', '2026': '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/月度销售额数据/销售订单_20260101-20261020.xlsx'} G = ('西安佰奥莱博生物科技有限公司', '西安瑞佰生物科技有限公司', '上海基灵生物科技有限公司', '基灵（西安）生物科技有限公司') MAP = {'FM513-007701-5P': 'FMD5084-000901', 'FM513-007702-FP': 'FM5144-007701-TP', 'FM507-007701': 'FMD2011', 'FMD5084-007702-5P': 'FMD5084-000901'} TODAY = dt.date(2026, 10, 21) rows = [] for y, p in P.items(): wb = openpyxl.load_workbook(p, data_only=True) ws = wb['Sheet1'] if y != '2026' else wb.active it = ws.iter_rows(values_only=True) H = [str(x).strip() if x is not None else '' for x in next(it)] i = {h: j for j, h in enumerate(H)} for r in it: if str(r[i['客户']] or '') not in G: continue if str(r[i['单据类型']] or '') != '标准销售订单' or str(r[i['是否赠品']] or '') == '是': continue q = float(r[i['销售数量']] or 0) pr = float(r[i['含税单价']] or 0) rows.append({'y': y, 'mon': str(r[i['合同月份']] if '合同月份' in i else r[i['合同日期']])[:7], 'sku': MAP.get(str(r[i['货号#']] or ''), str(r[i['货号#']] or '')), 'name': str(r[i['物料名称']] or ''), 'amt': q * pr}) T = {y: sum(r['amt'] for r in rows if r['y'] == y) for y in ('2024', '2025', '2026')} M = {y: [sum(r['amt'] for r in rows if r['y'] == y and r['mon'] == '%s-%02d' % (y, m)) for m in range(1, 13)] for y in T} J25 = sum(M['2025'][:10]) YOY = (T['2026'] / J25 - 1) * 100 G25 = (T['2025'] / T['2024'] - 1) * 100 TOT3 = sum(T.values()) SKU = defaultdict(lambda: defaultdict(float)) NAME = {} for r in rows: SKU[r['sku']][r['y']] += r['amt'] NAME.setdefault(r['sku'], r['name']) SKU_COUNT = len({r['sku'] for r in rows if r['y'] == '2026'}) # 量价（仅 T(50μl) 计价行） QV = defaultdict(lambda: [0.0, 0.0]) for r in rows: pass QV = defaultdict(lambda: [0.0, 0.0]) SKQV = defaultdict(lambda: defaultdict(lambda: [0.0, 0.0])) for y, p in P.items(): wb = openpyxl.load_workbook(p, data_only=True) ws = wb['Sheet1'] if y != '2026' else wb.active it = ws.iter_rows(values_only=True) H = [str(x).strip() if x is not None else '' for x in next(it)] i = {h: j for j, h in enumerate(H)} for r in it: if str(r[i['客户']] or '') not in G: continue if str(r[i['单据类型']] or '') != '标准销售订单' or str(r[i['是否赠品']] or '') == '是': continue unit = str(r[i['销售单位']] or '') qq = float(r[i['销售数量']] or 0) pr = float(r[i['含税单价']] or 0) if not (unit.startswith('T(') and qq > 0 and pr > 0): continue key = y if y != '2025' else '2025' QV[key][0] += qq QV[key][1] += qq * pr sku = MAP.get(str(r[i['货号#']] or ''), str(r[i['货号#']] or '')) SKQV[sku][key][0] += qq SKQV[sku][key][1] += qq * pr if y == '2025' and str(r[i['合同月份']])[:7] <= '2025-10': QV['2025_1_10'][0] += qq QV['2025_1_10'][1] += qq * pr if y == '2026': QV['2026_1_10'][0] += qq QV['2026_1_10'][1] += qq * pr P25_ = QV['2025_1_10'][1] / QV['2025_1_10'][0] P26_ = QV['2026_1_10'][1] / QV['2026_1_10'][0] DPI = (P26_ / P25_ - 1) * 100 DQI = (QV['2026_1_10'][0] / QV['2025_1_10'][0] - 1) * 100 VOL_EFF = (QV['2026_1_10'][0] - QV['2025_1_10'][0]) * P25_ / 1e4 PRC_EFF = QV['2026_1_10'][0] * (P26_ - P25_) / 1e4 AR = json.load(open(BASE + '/数据/2026年10月应收账龄_大客户部.json', encoding='utf-8'))['detail'] ar = [x for x in AR if any(g in str(x.get('cust', '')) for g in G)] AR_TOT = sum(float(x['amt']) for x in ar) AR_OD = sum(float(x.get('od_amt') or 0) for x in ar) AR_MAXD = max([int(x['od_days']) for x in ar if x.get('od_days')] or [0]) comp = json.load(open(BASE + '/数据/mcp_complaints.json', encoding='utf-8')) items = comp['items'] if isinstance(comp, dict) and 'items' in comp else comp COMPS = sorted([c for c in items if any(g in json.dumps(c, ensure_ascii=False) for g in G)], key=lambda c: c.get('created', '')) OPEN = [c for c in COMPS if c.get('status') != '结束'] OPEN_DAYS = max([(TODAY - dt.date.fromisoformat(c['created'])).days for c in OPEN] or [0]) def w(x): return '%.1f' % (x / 1e4) def chart(): bars = [] mx = max(max(M['2024']), max(M['2025']), max(M['2026'])) for i, m in enumerate(range(1, 13)): v25, v26 = M['2025'][i] / 1e4, M['2026'][i] / 1e4 bars.append((i + 1, v25, v26, mx / 1e4)) html = ['
', '
月度销售：2025 全年 vs 2026 年 1–10 月（万元，集团 4 个抬头）
', '
2025 年 %.1f 万元 ｜ 2026 年 1–10 月 %.1f 万元（同比 %.1f%%）
' % ( T['2025'] / 1e4, T['2026'] / 1e4, YOY)] for i, v25, v26, mxv in bars: html.append('
' '
' '
%d月
' % ( v25 / mxv * 100 if mxv else 0, i, v25, v26 / mxv * 100 if mxv else 0, i, v26, i)) html.append('
■ 2025■ 2026
') hp = BASE + '/销售分析/_chart_group360.html' open(hp, 'w', encoding='utf-8').write(''.join(html)) from playwright.sync_api import sync_playwright png = BASE + '/销售分析/_chart_group360.png' with sync_playwright() as p: b = p.chromium.launch(channel='chrome') pg = b.new_page(viewport={'width': 1560, 'height': 520}, device_scale_factor=2) pg.goto('file://' + hp) pg.wait_for_timeout(300) pg.locator('.w').screenshot(path=png) b.close() return png def set_font(run, size=8.5, bold=False, color=None): run.font.size = Pt(size); run.font.bold = bold; run.font.name = '微软雅黑' run._element.rPr.rFonts.set(qn('w:eastAsia'), '微软雅黑') if color is not None: run.font.color.rgb = color def shade(c, hexc): el = OxmlElement('w:shd'); el.set(qn('w:val'), 'clear'); el.set(qn('w:color'), 'auto'); el.set(qn('w:fill'), hexc) c._tc.get_or_add_tcPr().append(el) def cell(c, text, size=8.5, bold=False, color=None, align=WD_ALIGN_PARAGRAPH.LEFT): c.text = '' p = c.paragraphs[0]; p.alignment = align p.paragraph_format.space_before = Pt(1); p.paragraph_format.space_after = Pt(1) set_font(p.add_run(str(text)), size, bold, color) def table(doc, cols, widths): t = doc.add_table(rows=1, cols=cols); t.autofit = False; t.alignment = WD_TABLE_ALIGNMENT.CENTER for j in range(cols): t.rows[0].cells[j].width = Cm(widths[j]) b = OxmlElement('w:tblBorders') for e in ('top', 'left', 'bottom', 'right', 'insideH', 'insideV'): x = OxmlElement('w:%s' % e); x.set(qn('w:val'), 'single'); x.set(qn('w:sz'), '4'); x.set(qn('w:color'), 'DCE3EC') b.append(x) t._tbl.tblPr.append(b) return t def h(doc, text): p = doc.add_paragraph() p.paragraph_format.space_before = Pt(5); p.paragraph_format.space_after = Pt(2) set_font(p.add_run(text), 9.5, True, BLUE) def line(doc, text, size=8.3, color=None, bold=False, space=1): p = doc.add_paragraph() p.paragraph_format.space_before = Pt(0); p.paragraph_format.space_after = Pt(space) p.paragraph_format.line_spacing = 1.1 set_font(p.add_run(text), size, bold, color) doc = Document() s = doc.sections[0] s.page_width, s.page_height = Cm(21), Cm(29.7) s.top_margin, s.bottom_margin = Cm(0.9), Cm(0.9) s.left_margin, s.right_margin = Cm(1.2), Cm(1.2) hp = s.header.paragraphs[0] if os.path.exists(LOGO): hp.add_run().add_picture(LOGO, height=Cm(0.5)) set_font(hp.add_run(' 客户 360 卡 · 集团口径 · 2026-10-21'), 7, False, GREY) fp = s.footer.paragraphs[0]; fp.alignment = WD_ALIGN_PARAGRAPH.CENTER set_font(fp.add_run(SLOGAN + '　｜　佰奥莱博集团 4 个抬头　｜　含客户与金额信息，禁止对外'), 6.5, False, GREY) p = doc.add_paragraph(); p.paragraph_format.space_after = Pt(0) set_font(p.add_run('佰奥莱博集团 · 客户 360'), 15, True, BLUE) p = doc.add_paragraph(); p.paragraph_format.space_after = Pt(3) set_font(p.add_run('4 个抬头：西安佰奥莱博 / 西安瑞佰 / 上海基灵生物 / 基灵（西安）　｜　诊断原料 · 大客户销售部 · A 类 · 销售：刘子研　｜　数据截至 2026-10-20'), 7.2, False, GREY) kpi = doc.add_table(rows=2, cols=6) vals = [('三年累计', w(TOT3), '万', '2024–2026.10', False), ('2025 全年', w(T['2025']), '万', '+%.1f%%' % G25, False), ('2026 年 1–10 月', w(T['2026']), '万', '%.1f%%' % YOY, False), ('在采货号', str(SKU_COUNT), '个', '折算后', False), ('应收未收', w(AR_TOT), '万', '逾期 %s 万 · %d 天' % (w(AR_OD), AR_MAXD), True), ('客诉未闭环', str(len(OPEN)), '条', '已挂 %d 天' % OPEN_DAYS, True)] for j in range(6): top, bot = kpi.rows[0].cells[j], kpi.rows[1].cells[j] top.width = bot.width = Cm(3.06) cell(top, vals[j][0], 6.8, False, GREY) bot.paragraphs[0].text = '' pp = bot.paragraphs[0]; pp.paragraph_format.space_after = Pt(0) set_font(pp.add_run(vals[j][1]), 11.5, True, RED if vals[j][4] else RGBColor(0x12, 0x32, 0x4F)) set_font(pp.add_run(' ' + vals[j][2]), 6.8, False, GREY) set_font(pp.add_run('　' + vals[j][3]), 6.2, False, RED if vals[j][4] else GREY) shade(top, 'F4F7FB'); shade(bot, 'F4F7FB') h(doc, '一、销售趋势：集团口径三年 232.3 万，2026 年 1–10 月 63.7 万') line(doc, '2024 年 %s 万 → 2025 年 %s 万（同比 +%.1f%%）→ 2026 年 1–10 月 %s 万（同比 %.1f%%，2025 年同期 %s 万）。2026 年 8、9 月集团口径分别为 1.3 万、6.0 万，未出现单月归零；但西安单体 8、9 月无成交。' % ( w(T['2024']), w(T['2025']), G25, w(T['2026']), YOY, w(J25)), 8.2) ch = chart() doc.add_picture(ch, width=Cm(12.6)) doc.paragraphs[-1].alignment = WD_ALIGN_PARAGRAPH.CENTER doc.paragraphs[-1].paragraph_format.space_after = Pt(0) h(doc, '二、主销货号：FMD5084、FMD2011、FM513 三个体系占 2026 年 70%') t = table(doc, 5, [4.6, 7.4, 2.2, 2.2, 2.2]) for j, hd in enumerate(['货号（折算后）', '产品', '2024', '2025', '2026（1–10 月）']): cell(t.rows[0].cells[j], hd, 7.4, True, RGBColor(0xFF, 0xFF, 0xFF), WD_ALIGN_PARAGRAPH.RIGHT if j >= 2 else WD_ALIGN_PARAGRAPH.LEFT) shade(t.rows[0].cells[j], '1F4E79') top_skus = sorted(SKU.items(), key=lambda kv: -sum(kv[1].values()))[:6] for k, d in top_skus: r = t.add_row().cells nm = re.sub(r'\s*\(.*$', '', NAME.get(k, '')).replace('5×', '').replace('2×', '').strip()[:24] cell(r[0], k, 7.4) cell(r[1], nm, 7.4) for j, y in enumerate(('2024', '2025', '2026'), start=2): cell(r[j], w(d.get(y, 0)) if d.get(y) else '—', 7.4, align=WD_ALIGN_PARAGRAPH.RIGHT) line(doc, '单位：万元。基灵（西安）客户下单码已按对照表折算：FM513-007701-5P → FMD5084-000901、FM513-007702-FP → FM5144-007701-TP、FM507-007701 → FMD2011。2026 年前三个货号合计 44.8 万，占 70.3%。', 6.8, GREY, space=0) h(doc, '三、量价波动：销量 +40.0%，均价 −41.5%') line(doc, '2026 年 1–10 月 T(50μl) 口径销量 78.6 万 T，同比 +40.0%；加权均价 0.797 元/T，同比 −41.5%。量价拆解：销量效应 +30.6 万元、价格效应 −44.4 万元，净减 13.9 万元。金额下降由价格主导，销量为正向贡献。', 8.2) t = table(doc, 5, [4.4, 2.6, 2.6, 2.2, 2.8]) for j, hd in enumerate(['货号（折算后）', '2025 均价', '2026 均价', '变化', '2026 销量']): cell(t.rows[0].cells[j], hd, 7.2, True, RGBColor(0xFF, 0xFF, 0xFF), WD_ALIGN_PARAGRAPH.RIGHT if j >= 1 else WD_ALIGN_PARAGRAPH.LEFT) shade(t.rows[0].cells[j], '1F4E79') for sku, d in sorted(SKQV.items(), key=lambda kv: -kv[1].get('2026', [0, 0])[0])[:6]: r = t.add_row().cells cell(r[0], sku, 7.2) if '2025' in d and d['2025'][0] > 0: p25 = d['2025'][1] / d['2025'][0] cell(r[1], '%.2f' % p25, 7.2, align=WD_ALIGN_PARAGRAPH.RIGHT) p26 = d['2026'][1] / d['2026'][0] if d.get('2026') and d['2026'][0] else 0 cell(r[2], '%.2f' % p26 if p26 else '—', 7.2, align=WD_ALIGN_PARAGRAPH.RIGHT) cell(r[3], ('−%.1f%%' % abs((p26 / p25 - 1) * 100)) if p26 and p26 < p25 else ('+%.1f%%' % ((p26 / p25 - 1) * 100) if p26 else '—'), 7.2, align=WD_ALIGN_PARAGRAPH.RIGHT) else: cell(r[1], '—', 7.2, align=WD_ALIGN_PARAGRAPH.RIGHT) p26 = d['2026'][1] / d['2026'][0] if d.get('2026') and d['2026'][0] else 0 cell(r[2], '%.2f' % p26 if p26 else '—', 7.2, align=WD_ALIGN_PARAGRAPH.RIGHT) cell(r[3], '新增', 7.2, align=WD_ALIGN_PARAGRAPH.RIGHT) cell(r[4], '%.1f 万 T' % (d['2026'][0] / 1e4) if d.get('2026') and d['2026'][0] else '—', 7.2, align=WD_ALIGN_PARAGRAPH.RIGHT) line(doc, '单位：均价为元/T，按销量加权；仅含 T(50μl) 计价行，ml 计价行未计入。降价集中在 FMD5084（−54%）与 FM5144（−35%）；FMD2011、FM5144-007701-TP 以 0.65–0.85 元/T 新进场，拉低集团均价。', 6.6, GREY, space=0) h(doc, '四、客诉：5 起，全部发生在西安主体，1 起未闭环') t = table(doc, 4, [2.6, 8.8, 4.0, 3.0]) for j, hd in enumerate(['日期', '问题', '状态', '天数']): cell(t.rows[0].cells[j], hd, 7.4, True, RGBColor(0xFF, 0xFF, 0xFF), WD_ALIGN_PARAGRAPH.RIGHT if j == 3 else WD_ALIGN_PARAGRAPH.LEFT) shade(t.rows[0].cells[j], '1F4E79') for c in COMPS: days = (TODAY - dt.date.fromisoformat(c['created'])).days op = c.get('status') != '结束' r = t.add_row().cells cell(r[0], c['created'], 7.4) cell(r[1], str(c.get('name', '')).split('-', 2)[-1], 7.4) cell(r[2], '3个月改善闭环跟进中' if c.get('status') == '大区确认中' else c.get('status', ''), 7.4, op, RED if op else None) cell(r[3], '%d 天' % days, 7.4, align=WD_ALIGN_PARAGRAPH.RIGHT) line(doc, 'FM5144 一个货号占 3 条（两次沉淀、一次漏液）；瑞佰、基灵两家 2024 年以来无客诉记录。已关闭按处理历时计。', 6.8, GREY, space=0) h(doc, '五、在测项目与卡点（西安主体）｜风险与下一步') box = doc.add_table(rows=1, cols=2) box.autofit = False left, right = box.rows[0].cells left.width, right.width = Cm(10.8), Cm(7.6) left.text = '' proj = [('宠物真菌粪便直扩（卡住，需要优化酶在强粪便抑制背景下的性能）', RED), ('FM507 冻干后比液体滞后 1 个 Ct；新酶系 FM2181-4-020303、FMD5104-014429 滞后 2–3 个 Ct，配方未定（9-19、9-23、10-16）。', None), ('马 ACTB / FMD5084-000901（卡住）', RED), ('个别靶标 Ct 延迟约 3 个，加探针、翻倍镁离子均未解决 Rn 下降（9-23）。', None), ('二代 POC 平台（放缓）', RGBColor(0x5B, 0x7A, 0x52)), ('客户自评“项目定位错误、操作复杂”，不是重点（7-21）。', None), ('货号切换（已完成，配合兽药 GMP 已报证项目原料升级）', BLUE), ('FMD5084-000901 → FMD5084-007703-DB、FMD2011 由 2× 改 5×，7 月完成并按新货号下单。', None)] for txt, col in proj: p = left.add_paragraph() if left.paragraphs[0].text else left.paragraphs[0] p.paragraph_format.space_before = Pt(1); p.paragraph_format.space_after = Pt(0); p.paragraph_format.line_spacing = 1.1 set_font(p.add_run(txt), 7.6 if col else 7.2, bool(col), col) right.text = '' rp = [('风险', RED), ('集团应收 %s 万，逾期 %s 万（占 %.0f%%），最长 %d 天' % (w(AR_TOT), w(AR_OD), AR_OD / AR_TOT * 100, AR_MAXD), None), ('2026 年集团口径未断档，但西安单体 8、9 月无成交；原因记录未说明', None), ('收入集中在冻干预混液单产品线，第二产品线尚未形成', None), ('下一步', BLUE), ('① 逾期款逐票对账，锁付款计划｜刘子研 · 10 月底', None), ('② FM5144-007701 漏液闭环｜质量部 · 立刻', None), ('③ Q4 邀约明年业绩计划，锁最低采购量｜刘新元、刘子研 · 11 月中', None), ('④ 两个在测项目出配方结论｜技术支持 · 10 月底', None)] for i, (txt, col) in enumerate(rp): p = right.add_paragraph() if right.paragraphs[0].text else right.paragraphs[0] p.paragraph_format.space_before = Pt(1); p.paragraph_format.space_after = Pt(0); p.paragraph_format.line_spacing = 1.1 if col: set_font(p.add_run(txt), 7.8, True, col) else: set_font(p.add_run(('· ' if i < 4 else '') + txt), 7.0) doc.save(OUT) print('SAVED', OUT) print('KEY', {k: round(v / 1e4, 2) for k, v in T.items()}, 'TOT3', round(TOT3 / 1e4, 2), 'YOY', round(YOY, 1), 'G25', round(G25, 1), 'AR', AR_TOT, 'OD', AR_OD, 'SKU', SKU_COUNT)