#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""生成 2026年10月销售部工作汇报（基于 销售经理月度汇报模版.pptx）。"""
import json
import os
from copy import copy, deepcopy

from pptx import Presentation
from pptx.util import Inches, Pt
from pptx.dml.color import RGBColor
from pptx.enum.text import PP_ALIGN, MSO_ANCHOR
from pptx.chart.data import CategoryChartData
from pptx.enum.chart import XL_CHART_TYPE, XL_LEGEND_POSITION

ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
TEMPLATE = '/Users/liuxinyuan/.hermes/profiles/zone-b/cache/documents/native-inputs/5e4ae913c6004bd40c754798caf61567ac33ab714196918ea2d25c9c29ed6f65/doc_353905a8149e_销售经理月度汇报模版.pptx'
OUT = os.path.join(ROOT, '经营分析/月报/2026年10月销售部工作汇报_草稿_20261108.pptx')
DATA = json.load(open(os.path.join(ROOT, '数据/202610_销售部月报_数据.json'), encoding='utf-8'))

DEPT_ORDER = ['大客户部', '销售拓展部', '生命科学部']
DEPT_LABEL = {'大客户部': '大客户部', '销售拓展部': '销售拓展部', '生命科学部': '生命科学部'}
DARK = RGBColor(0x0D, 0x2A, 0x52)
BLUE = RGBColor(0x15, 0x65, 0xC0)
GREY = RGBColor(0x65, 0x75, 0x8B)
RED = RGBColor(0xB4, 0x23, 0x18)
GREEN = RGBColor(0x06, 0x76, 0x47)
WHITE = RGBColor(0xFF, 0xFF, 0xFF)
BLACK = RGBColor(0x1F, 0x29, 0x37)
LIGHT = RGBColor(0xF3, 0xF7, 0xFC)


def set_text(shape, text, size=14, bold=False, color=BLACK, font='宋体', align=PP_ALIGN.LEFT):
    tf = shape.text_frame
    tf.clear()
    tf.word_wrap = True
    lines = str(text).split('\n')
    for i, line in enumerate(lines):
        p = tf.paragraphs[0] if i == 0 else tf.add_paragraph()
        p.alignment = align
        p.space_after = Pt(2)
        r = p.add_run()
        r.text = line
        r.font.size = Pt(size)
        r.font.bold = bold
        r.font.name = font
        r.font.color.rgb = color


def add_text(slide, x, y, w, h, lines, size=11, color=BLACK, bold_first=False):
    box = slide.shapes.add_textbox(Inches(x), Inches(y), Inches(w), Inches(h))
    tf = box.text_frame
    tf.word_wrap = True
    tf.margin_left = 0
    tf.margin_right = 0
    tf.margin_top = 0
    tf.margin_bottom = 0
    for i, line in enumerate(lines):
        p = tf.paragraphs[0] if i == 0 else tf.add_paragraph()
        p.space_after = Pt(3)
        p.line_spacing = 1.12
        r = p.add_run()
        r.text = line
        r.font.size = Pt(size)
        r.font.name = '宋体'
        r.font.color.rgb = color
        r.font.bold = bold_first and i == 0
    return box


def set_cell(cell, text, size=9, color=BLACK, bold=False, fill=None, align=PP_ALIGN.LEFT):
    cell.text = ''
    tf = cell.text_frame
    tf.word_wrap = True
    tf.margin_left = Inches(0.03)
    tf.margin_right = Inches(0.03)
    tf.margin_top = Inches(0.02)
    tf.margin_bottom = Inches(0.02)
    p = tf.paragraphs[0]
    p.alignment = align
    r = p.add_run()
    r.text = '' if text is None else str(text)
    r.font.size = Pt(size)
    r.font.name = '宋体'
    r.font.bold = bold
    r.font.color.rgb = color
    if fill is not None:
        cell.fill.solid()
        cell.fill.fore_color.rgb = fill


def set_table_header(table, size=9):
    for cell in table.rows[0].cells:
        for p in cell.text_frame.paragraphs:
            for r in p.runs:
                r.font.size = Pt(size)
                r.font.name = '宋体'
                r.font.bold = True
                r.font.color.rgb = WHITE


def fill_table(table, rows, start=0, size=9):
    for r, row in enumerate(rows, start):
        for c, val in enumerate(row):
            if c < len(table.columns) and r < len(table.rows):
                set_cell(table.cell(r, c), val, size=size)


def new_table(slide, x, y, w, h, headers, rows, widths=None, size=9, header_size=9):
    shape = slide.shapes.add_table(len(rows) + 1, len(headers), Inches(x), Inches(y), Inches(w), Inches(h))
    table = shape.table
    for c, htext in enumerate(headers):
        set_cell(table.cell(0, c), htext, size=header_size, color=WHITE, bold=True, fill=DARK)
    for r, row in enumerate(rows, 1):
        for c, val in enumerate(row):
            set_cell(table.cell(r, c), val, size=size)
    if widths:
        for c, wd in enumerate(widths):
            table.columns[c].width = Inches(wd)
    return table


def delete_shape(shape):
    shape._element.getparent().remove(shape._element)


def add_chart(slide, x, y, w, h, categories, series, title='', colors=None):
    cd = CategoryChartData()
    cd.categories = categories
    for name, vals in series:
        cd.add_series(name, vals)
    gf = slide.shapes.add_chart(XL_CHART_TYPE.COLUMN_CLUSTERED, Inches(x), Inches(y), Inches(w), Inches(h), cd)
    ch = gf.chart
    ch.has_title = bool(title)
    if title:
        ch.chart_title.text_frame.text = title
        for p in ch.chart_title.text_frame.paragraphs:
            for r in p.runs:
                r.font.size = Pt(10)
                r.font.name = '宋体'
                r.font.bold = True
    ch.has_legend = True
    ch.legend.position = XL_LEGEND_POSITION.BOTTOM
    ch.legend.include_in_layout = False
    ch.font.size = Pt(9.5)
    ch.font.name = 'Times New Roman'
    if colors:
        for i, color in enumerate(colors):
            if i < len(ch.series):
                ch.series[i].format.fill.solid()
                ch.series[i].format.fill.fore_color.rgb = color
    return ch


def fmt(v, n=2):
    return '—' if v is None else f'{v:,.{n}f}'


prs = Presentation(TEMPLATE)
slides = list(prs.slides)

# ---------- Slide 1 cover ----------
for sh in slides[0].shapes:
    if not sh.has_text_frame:
        continue
    t = sh.text_frame.text
    if '销售部' in t and '工作汇报' in t:
        set_text(sh, '诊断原料销售部\n10月份工作汇报', size=26, bold=True, color=WHITE, font='宋体', align=PP_ALIGN.LEFT)
    elif '汇报人' in t:
        set_text(sh, '汇报人：刘新元   2026.11.9', size=14, color=WHITE)

# ---------- Slide 3 annual target ----------
s = slides[2]
# Remove old table/chart/text hint; rebuild with larger type and two departments split
for sh in list(s.shapes):
    if sh.has_table or sh.has_chart:
        delete_shape(sh)
    elif sh.has_text_frame and '使用柱形图表' in sh.text_frame.text:
        delete_shape(sh)

rs = json.load(open(os.path.join(ROOT, '数据/月报库/report_store.json'), encoding='utf-8'))
hist = rs['three_year']


def hist_dept(dept, year):
    vals = [v / 1e4 if v is not None else None for v in hist[dept][year]['months']]
    ytd = sum(v for v in vals[:10] if v is not None)
    return {'months': vals, 'ytd': ytd, 'full': sum(v for v in vals if v is not None), 'oct': vals[9]}


def pct(a, b):
    return '—' if not b else f'{(a / b - 1) * 100:+.1f}%'


rows = []
for dept in ['销售拓展部', '大客户部']:
    x = DATA['depts'][dept]
    h25 = hist_dept(dept, '2025')
    h24 = hist_dept(dept, '2024')
    rows.append([dept, '2026年（1-10月）', fmt(x['target_annual_wan'], 0), fmt(x['ytd_actual_wan']),
                 f"{x['annual_rate']:.1f}%", fmt(x['oct_actual_wan']), '—', pct(x['ytd_actual_wan'], h25['ytd'])])
    rows.append(['', '2025年', '—', fmt(h25['ytd']), '—', fmt(h25['oct']), fmt(h25['full']), pct(h25['ytd'], h24['ytd'])])
    rows.append(['', '2024年', '—', fmt(h24['ytd']), '—', fmt(h24['oct']), fmt(h24['full']), '—'])
# total two departments
x_exp, x_big = DATA['depts']['销售拓展部'], DATA['depts']['大客户部']
h25e, h25b = hist_dept('销售拓展部', '2025'), hist_dept('大客户部', '2025')
h24e, h24b = hist_dept('销售拓展部', '2024'), hist_dept('大客户部', '2024')
t26 = x_exp['ytd_actual_wan'] + x_big['ytd_actual_wan']
t25 = h25e['ytd'] + h25b['ytd']
t24 = h24e['ytd'] + h24b['ytd']
rows.append(['合计（两部门）', '2026年（1-10月）', fmt(x_exp['target_annual_wan'] + x_big['target_annual_wan'], 0), fmt(t26),
             f'{t26 / (x_exp["target_annual_wan"] + x_big["target_annual_wan"]) * 100:.1f}%',
             fmt(x_exp['oct_actual_wan'] + x_big['oct_actual_wan']), '—', pct(t26, t25)])
rows.append(['', '2025年', '—', fmt(t25), '—', fmt(h25e['oct'] + h25b['oct']), fmt(h25e['full'] + h25b['full']), pct(t25, t24)])
rows.append(['', '2024年', '—', fmt(t24), '—', fmt(h24e['oct'] + h24b['oct']), fmt(h24e['full'] + h24b['full']), '—'])

tbl = new_table(s, 0.53, 1.52, 12.4, 3.10,
                ['部门', '年度', '年度目标\n（万元）', '1-10月实际\n（万元）', '年度完成率', '10月实际\n（万元）', '全年实际\n（万元）', '同比'],
                rows, widths=[1.55, 1.75, 1.35, 1.65, 1.15, 1.30, 1.40, 1.05], size=10.2, header_size=10)
# group shading for readability
for r in range(1, len(tbl.rows)):
    fill = RGBColor(0xF3, 0xF7, 0xFC) if r in (2, 3, 5, 6, 8, 9) else None
    if fill:
        for c in range(len(tbl.columns)):
            tbl.cell(r, c).fill.solid(); tbl.cell(r, c).fill.fore_color.rgb = fill
    # dept label only on first row of block, merge-like bold
    if r in (1, 4, 7):
        tbl.cell(r, 0).text_frame.paragraphs[0].runs[0].font.bold = True

add_text(s, 0.55, 4.70, 12.2, 0.35,
         ['2026年为1-10月；2025/2024为全年；同比按1-10月同口径。生命科学部未纳入本页，单列于部门汇总表。'],
         size=9, color=GREY)

# annual 3-year comparison, two departments
add_chart(s, 0.55, 4.98, 12.2, 2.12,
          ['2024年', '2025年', '2026年1-10月'],
          [('销售拓展部', [hist_dept('销售拓展部','2024')['ytd'], hist_dept('销售拓展部','2025')['ytd'], x_exp['ytd_actual_wan']]),
           ('大客户部', [hist_dept('大客户部','2024')['ytd'], hist_dept('大客户部','2025')['ytd'], x_big['ytd_actual_wan']])],
          '1-10月销售额对比（万元）', colors=[BLUE, RGBColor(0x2F, 0x80, 0xED)])

# ---------- Slide 4 customer development ----------
s = slides[3]
# title
for sh in s.shapes:
    if sh.has_text_frame and '部门年度客户开发目标' in sh.text_frame.text:
        set_text(sh, '2、客户开发与客户结构（实际）', size=14, bold=True, color=DARK)
# remove old table + hint
for sh in list(s.shapes):
    if sh.has_table:
        delete_shape(sh)
    elif sh.has_text_frame and '使用柱形图表' in sh.text_frame.text:
        delete_shape(sh)
months = [f'{i}月' for i in range(1, 11)]
cust = DATA['customers']
active = [cust.get(f'2026-{m:02d}', {}).get('active') for m in range(1, 11)]
first = [None] + [cust.get(f'2026-{m:02d}', {}).get('first') for m in range(2, 11)]
avg = []
for m in range(1, 11):
    a = cust.get(f'2026-{m:02d}', {}).get('active') or 0
    sales = DATA['total']['actual_monthly_wan'][m - 1]
    avg.append(round(sales / a, 2) if a else None)
headers = ['指标'] + months + ['说明']
rows = [
    ['活跃客户数'] + [str(x) if x is not None else '—' for x in active] + ['10月161家'],
    ['2026年首单客户'] + [('—' if x is None else str(x)) for x in first] + ['10月21家；1月无2025基线'],
    ['单客户月均销售额（万元）'] + [fmt(x) for x in avg] + ['10月2.40'],
]
new_table(s, 0.47, 1.10, 12.54, 2.15, headers, rows, widths=[2.0] + [0.78] * 10 + [2.4], size=8.5, header_size=8.5)
add_text(s, 0.55, 3.35, 12.2, 0.45,
         ['口径：活跃客户=当月有标准销售订单的客户（去重）；2026年首单客户=2026年首次下单客户；公司未下达月度客户开发目标，本表只列实际。'],
         size=8.5, color=GREY)
add_chart(s, 0.53, 3.95, 12.4, 3.15, months,
          [('活跃客户数', active), ('2026年首单客户', first)], '客户数与首单客户（家）', colors=[BLUE, RGBColor(0x2F, 0x80, 0xED)])

# ---------- Slide 5 individual ----------
s = slides[4]
tbl_shape = next(sh for sh in s.shapes if sh.has_table)
t1 = tbl_shape.table
# expand rows to 11 (header + 10 people)
while len(t1.rows) > 11:
    t1._tbl.remove(t1.rows[-1]._tr)
while len(t1.rows) < 11:
    t1._tbl.append(deepcopy(t1._tbl.tr_lst[-1]))
tbl_shape.height = Inches(3.05)
people = DATA['people_top'][:10]
headers = ['销售人员（部门）'] + [f'{i}月' for i in range(1, 11)] + ['累计完成', '年度目标', '年度完成率']
for c, h in enumerate(headers):
    set_cell(t1.cell(0, c), h, size=8, color=WHITE, bold=True, fill=DARK)
for r, p in enumerate(people, 1):
    set_cell(t1.cell(r, 0), f"{p['name']}（{p['dept']}）", size=8)
    for m in range(10):
        set_cell(t1.cell(r, 1 + m), fmt(p['monthly_wan'][m]), size=8)
    set_cell(t1.cell(r, 11), fmt(p['ytd_wan']), size=8)
    set_cell(t1.cell(r, 12), fmt(p['target_wan']), size=8)
    set_cell(t1.cell(r, 13), f"{p['rate']:.1f}%", size=8)
# title2 and table2 replace
for sh in s.shapes:
    if sh.has_text_frame and '个人客户开发目标' in sh.text_frame.text:
        set_text(sh, '4、部门累计达成情况（补充）', size=13, bold=True, color=DARK)
        sh.top = Inches(4.28)
        sh.height = Inches(0.30)
# remove old second table
tables = [sh for sh in s.shapes if sh.has_table]
if len(tables) >= 2:
    delete_shape(tables[1])
# add department summary at lower position
dept_rows = []
for d in DEPT_ORDER:
    x = DATA['depts'][d]
    dept_rows.append([d, fmt(x['target_annual_wan'], 0), fmt(x['ytd_actual_wan']), f"{x['annual_rate']:.1f}%",
                      fmt(x['oct_actual_wan']), fmt(x['oct_target_wan']),
                      f"{x['oct_actual_wan']/x['oct_target_wan']*100:.1f}%"])
dept_rows.append(['合计', '6,650', fmt(DATA['total']['ytd_actual_wan']), f"{DATA['total']['annual_rate']:.1f}%",
                  fmt(DATA['total']['oct_actual_wan']), fmt(DATA['total']['oct_target_wan']),
                  f"{DATA['total']['oct_actual_wan']/DATA['total']['oct_target_wan']*100:.1f}%"])
new_table(s, 0.67, 4.55, 12.26, 2.2,
          ['部门', '年度目标(万元)', '1-10月累计(万元)', '年度达成', '10月实际(万元)', '10月目标(万元)', '10月达成'],
          dept_rows, widths=[1.4, 1.7, 2.0, 1.4, 1.8, 1.8, 1.6], size=8.5, header_size=8.5)

# ---------- Slide 6 target miss ----------
s = slides[5]
add_text(s, 0.65, 1.90, 11.9, 2.1, [
    f"10月合计完成{fmt(DATA['total']['oct_actual_wan'])}万元，目标{fmt(DATA['total']['oct_target_wan'])}万元，达成{DATA['total']['oct_actual_wan']/DATA['total']['oct_target_wan']*100:.1f}%。",
    f"大客户部{fmt(DATA['depts']['大客户部']['oct_actual_wan'])}/{fmt(DATA['depts']['大客户部']['oct_target_wan'])}万元（{DATA['depts']['大客户部']['oct_actual_wan']/DATA['depts']['大客户部']['oct_target_wan']*100:.1f}%）；销售拓展部{fmt(DATA['depts']['销售拓展部']['oct_actual_wan'])}/{fmt(DATA['depts']['销售拓展部']['oct_target_wan'])}万元（{DATA['depts']['销售拓展部']['oct_actual_wan']/DATA['depts']['销售拓展部']['oct_target_wan']*100:.1f}%）；生命科学部{fmt(DATA['depts']['生命科学部']['oct_actual_wan'])}/{fmt(DATA['depts']['生命科学部']['oct_target_wan'])}万元（{DATA['depts']['生命科学部']['oct_actual_wan']/DATA['depts']['生命科学部']['oct_target_wan']*100:.1f}%）。",
    '主要原因：①大客户部海尔施呼吸道五重被诺唯赞替换，STR冻干出货下降；丽珠HIV/HBV仍处小批量或客诉处理阶段；②销售拓展部客户项目集中在测试/验证，万孚POCT、圣湘呼吸道等在10月未形成规模订单；③生命科学部基数低，10月65个测试单对应34张标准订单，二单转化不足。',
    '改善措施：大客户按项目逐项推进丽珠HBV、迪安结核、天隆003006、科华Tth；拓展锁定万孚POCT、圣湘呼吸道、明日达冻干直扩；科研执行二单冲刺和14天转化机制。',
], size=9.2)
add_text(s, 0.65, 4.62, 12.2, 2.3, [
    '客户开发：10月活跃客户161家，2026年首单客户21家；公司未下达月度客户开发目标，按实际数跟踪。',
    '主要问题：新增首单集中在销售拓展部；生命科学部10月16家客户、65个测试单、34张标准订单，测试转订单和二单转化偏低；大客户新项目以存量客户升级为主。',
    '改善措施：①大客户按丽珠、迪安、天隆、海尔施、科华、复星、新产业逐户列11月项目清单；②拓展按万孚、圣湘、明日达、纳克/中道列测试转订单和回款清单；③科研按课题组二单清单推进，11月30日前完成15组二单、12月20日前25组。',
], size=9.2)

# ---------- Slide 7 receivables ----------
s = slides[6]
for sh in s.shapes:
    if sh.has_text_frame and '根据财务部提供' in sh.text_frame.text:
        set_text(sh, '截至2026-09-30：大客户部应收297.86万元、逾期175.75万元；销售拓展部应收692.88万元、逾期421.38万元；两部门合计应收990.73万元、逾期597.13万元。', size=9, color=GREY)
top = DATA['top_overdue'][:8]
rows = []
for x in top:
    action = ''
    if '复星' in x['customer']:
        action = '11月回款7万元+账期方案'
    elif '圣湘' in x['customer']:
        action = '质量协议+分批回款'
    elif '生科原' in x['customer'] or '生凌' in x['customer']:
        action = '陈溪子对账+催收'
    elif '冠牧' in x['customer']:
        action = '李江辉催收'
    elif '知因' in x['customer']:
        action = '刘虹晶对账'
    elif '丹威' in x['customer']:
        action = '刘子研对账/退换货抵扣'
    rows.append([x['customer'], f"{x['dept']}/{x['sp']}", fmt(x['ar_wan']), fmt(x['overdue_wan']), str(x['max_days']), action])
new_table(s, 0.55, 1.75, 12.35, 4.0,
          ['客户', '部门/销售', '应收(万元)', '逾期(万元)', '最长逾期(天)', '11月动作'],
          rows, widths=[3.6, 2.2, 1.3, 1.3, 1.3, 2.6], size=8, header_size=8)
add_text(s, 0.6, 5.95, 12.2, 1.0, [
    '超60天：大客户84.50万元/11户；销售拓展120.60万元/33户。一年以上：大客户2.90万元、销售拓展11.97万元。',
    '要求：11月30日前对前10户逐户形成“对账—承诺—回款”闭环；逾期60天以上客户由部门经理带队上门，回款结果纳入11月绩效。',
], size=9, color=RED)

# ---------- Slide 8 plan / issues ----------
s = slides[7]
# 把原来的三段计划文字改为 Q3 承接表
for sh in list(s.shapes):
    if sh.has_text_frame and '佰奥莱博5个冻干项目' in sh.text_frame.text:
        delete_shape(sh)
    if sh.has_text_frame and '工作计划点检' in sh.text_frame.text:
        set_text(sh, '1、月度工作计划点检（承接 Q3 汇报）', size=14, bold=True, color=DARK)
q3_rows = [
    ['大客户销售目标达成率不好', '紧盯转产（内/外）；日级反馈/短会；销售工具', '10月104.08/200.74万元（51.8%）；天隆003006替换ZJ4；丽珠HBV客诉与2000T验证；迪安结核稳定；海尔施五重被诺唯赞替换', '部分推进'],
    ['测试单→销售单转化效率', '10月访谈销售/技术，整理高频问题Q&A知识库', '科研10月65个测试单/34张标准订单；Q&A知识库进度—', '动作未记录'],
    ['科研销售部进度滞后', 'KPI看板日/周/月；周会复盘盯产品线测试单', '10月4.46/89.7万元（5.0%）；65测试单/16客户/34订单；周会/复盘已执行；限制性内切酶后续—', '部分推进'],
    ['Q4客户高层拜访', '之江/艾德/安图/明德/天隆/迪安/复星，10-11月', '之江登革热092401选型；安图E10-000725采购；天隆003006；迪安结核；复星STD未闭环；艾德/明德互访—', '项目有进度/互访未记录'],
    ['产品迭代：冻干/全预混/LAMP/快体系/单酶适配', 'Q3产品迭代方向', '迪安000903 25min；复星STD与菲鹏对比；天隆003006客诉基本解除；单酶适配信息—', '部分'],
]
new_table(s, 0.62, 1.54, 12.1, 1.45,
          ['Q3遗留事项', 'Q3要求/目标', '10月进度（—=未查到记录，不推断）', '状态'],
          q3_rows, widths=[2.1, 2.9, 5.9, 1.2], size=6.8, header_size=6.8)
# 下移问题表标题与表格
for sh in s.shapes:
    if sh.has_text_frame and '月度存在的问题分析与改善措施' in sh.text_frame.text:
        sh.top = Inches(3.12)
tbl8_shape = next(sh for sh in s.shapes if sh.has_table and sh.top > Inches(3.3))
tbl8 = tbl8_shape.table
tbl8_shape.top = Inches(3.56)
tbl8_shape.height = Inches(3.0)
rows8 = [
    ['大客户', '海尔施五重被诺唯赞替换；STR冻干出货下降', '冻干海外商机+新项目；11月完成丽珠/迪安/天隆节点', '刘子研', '11/30'],
    ['销售拓展', '万孚POCT非特异；纳克/明德冻干客诉', '新酶验证、冻干工艺复核、客诉闭环', '赵云浩、陈溪子', '11/30'],
    ['生命科学', '65个测试单仅34张标准订单，二单少', '二单清单：11/30前15组，12/20前25组', '吴云、韩远怀', '11/30'],
]
fill_table(tbl8, rows8, 1, size=8.3)

# ---------- Slide 9 next month ----------
s = slides[8]
for sh in s.shapes:
    if sh.has_text_frame and '业绩目标可附商机表' in sh.text_frame.text:
        set_text(sh, '11月目标638.58万元：大客户200.74、销售拓展343.74、生命科学94.10', size=12, bold=True, color=DARK)
rows9 = []
for d in DEPT_ORDER:
    x = DATA['depts'][d]
    focus = {'大客户部': '丽珠HBV、迪安结核、天隆003006、海尔施新项目、科华Tth',
             '销售拓展部': '万孚POCT、圣湘呼吸道、明日达冻干、纳克/中道回款',
             '生命科学部': '二单冲刺；11/30前15组、12/20前25组'}[d]
    rows9.append([d, fmt(x['target_monthly_wan'][10]), fmt(x['ytd_actual_wan']), f"{x['annual_rate']:.1f}%", focus])
nov_total = sum(DATA['depts'][d]['target_monthly_wan'][10] for d in DEPT_ORDER)
rows9.append(['合计', fmt(nov_total), fmt(DATA['total']['ytd_actual_wan']), f"{DATA['total']['annual_rate']:.1f}%", '按部门项目清单周跟踪'])
new_table(s, 0.55, 1.70, 12.3, 2.15,
          ['部门', '11月目标(万元)', '1-10月累计(万元)', '年度达成', '11月重点'],
          rows9, widths=[1.5, 1.5, 1.8, 1.1, 6.0], size=8.5, header_size=8.5)
add_text(s, 0.6, 4.15, 12.2, 2.5, [
    '大客户：丽珠HBV 2000T验证与转产、迪安结核2次5-10万人份、天隆003006稳定供应、海尔施冻干海外新项目、科华Tth体系测试。',
    '销售拓展：万孚POCT非特异在Q4关闭并推进中试转产；圣湘呼吸道冻干/脓毒症、明日达冻干直扩形成订单；纳克、中道逾期回款。',
    '生命科学：11月目标94.10万元，按“二单冲刺”推进；优先29个<1,000元课题组和44个只有一单的新组；11/30前15组二单，12/20前25组。',
    '管理动作：每周一项目清单复盘；每月10日、20日、30日三次回款/订单节点检查；客户开发按首单客户21家为10月基线，11月不低于20家。',
], size=8.8)

# ---------- Slide 10 team ----------
s = slides[9]
tbl10 = next(sh for sh in s.shapes if sh.has_table).table
rows10 = [
    ['绩效结果', '大客户91.4；销售拓展82.5；科研经理64.0；科研工程师67.7', '低分：侯海瑜36、陈彩红57、徐卓凡63.9', '刘新元', '11/15'],
    ['回款', '大客户2人回款得分0；销售拓展6人回款0', '逐户回款计划+周跟踪，纳入11月绩效', '刘子研/赵云浩/陈溪子', '11/30'],
    ['客户开发', '科研新客户开发多人0分；10月首单客户21家', '课题组开发清单+测试转订单', '吴云/韩远怀', '11/30'],
    ['能力建设', '冻干、快速、全预混项目增多，销售技术准备不足', '产品/技术培训+项目案例复盘', '刘新元/技术支持', '11/30'],
    ['过程管理', '周会/复盘/日报执行不一致', '周会纪要+周报模板+抽查', '各部门经理', '每周'],
]
fill_table(tbl10, rows10, 1, size=8.2)

# ---------- Slide 11 competitor ----------
s = slides[10]
tbl11_shape = next(sh for sh in s.shapes if sh.has_table)
tbl11_shape.height = Inches(4.55)
tbl11 = tbl11_shape.table
# add rows for 5 competitors
while len(tbl11.rows) < 6:
    tbl11._tbl.append(deepcopy(tbl11._tbl.tr_lst[-1]))
rows11 = [
    ['诺唯赞', 'Taq/RT/qPCR全系；P142对照', '未披露', '—', '科研+工业；呼吸道、POCT、多重', '品牌稳、替换壁垒高；批次稳定性/价格被客户质疑', '10月提及209条；海尔施五重被替换、万孚用P142对照', '以批次稳定性+冻干/快速数据切入，不正面拼价', '飞书活动 2026-10-31 等'],
    ['翌圣', 'RT/冻干体系', '未披露', '—', '呼吸道、冻干', '冻干后性能、价格', '丹威呼吸道临床冻干后性能差异，丢单被翌圣做进去', '补冻干前后一致性数据，抢新项目', '飞书活动 2026-10-31'],
    ['AG', 'qPCR/逆转录', '—', '采购逆转录赠荧光定量，单次约5000元', '科研院所', '稳定、价格/赠品', '10月提及27条；南医大等稳定使用', '以快速/耐抑制+试用装切入', '飞书活动 2026-10-30'],
    ['菲鹏', 'SNP/STD酶', '价格合适', '—', 'SNP、STD', '真实样本荧光增量优于FM0181-4', '10月提及19条；复星STD项目对比', '补真实样本数据/荧光增量', '飞书活动 2026-10-30；大客户月报'],
    ['爱博泰克', '同源重组/高保真', '100T 550元（活动价）', '—', '科研克隆', '价格低；性能相当', '10月提及19条；国科大杭州等', '拆小包装/试用+性价比', '飞书活动 2026-10-28'],
]
for r, row in enumerate(rows11, 1):
    for c, val in enumerate(row):
        set_cell(tbl11.cell(r, c), val, size=7.5)
add_text(s, 0.25, 5.9, 12.5, 0.8,
         ['竞对提及来自10月飞书活动1,568条；关键词命中口径，未做归一化占比。价格未披露的写“未披露”，不补推断值。'],
         size=8, color=GREY)

# ---------- save ----------
os.makedirs(os.path.dirname(OUT), exist_ok=True)
prs.save(OUT)
print('saved', OUT)
print('slides', len(prs.slides), 'size', os.path.getsize(OUT))