#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""把 Q3 汇报 P11-P14 的卡点/进度/11月计划承接进 10 月 PPT。"""
import os
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.enum.shapes import MSO_SHAPE
from pptx.oxml.ns import qn
from lxml import etree

ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
SRC = os.path.join(ROOT, '经营分析/月报/2026年10月销售部工作汇报_草稿_20261108_v3.pptx')
OUT = os.path.join(ROOT, '经营分析/月报/2026年10月销售部工作汇报_草稿_20261108_v4.pptx')
LOGO = '/Users/liuxinyuan/work/公司资料/logo_transparent.png'

NAVY = RGBColor(0x0B, 0x2A, 0x4A)
BLUE = RGBColor(0x1F, 0x6F, 0xEB)
LIGHT = RGBColor(0xEA, 0xF2, 0xFB)
PALE = RGBColor(0xF5, 0xF8, 0xFC)
GREY = RGBColor(0x5B, 0x6B, 0x7F)
MIDGREY = RGBColor(0x8A, 0x99, 0xAA)
RED = RGBColor(0xC0, 0x39, 0x2B)
AMBER = RGBColor(0xB7, 0x79, 0x1F)
GREEN = RGBColor(0x1E, 0x84, 0x49)
WHITE = RGBColor(0xFF, 0xFF, 0xFF)


def set_run(run, size=9, color=NAVY, bold=False):
    run.font.size = Pt(size)
    run.font.bold = bold
    run.font.color.rgb = color
    run.font.name = 'Times New Roman'
    rPr = run._r.get_or_add_rPr()
    ea = rPr.find(qn('a:ea'))
    if ea is None:
        ea = etree.SubElement(rPr, qn('a:ea'))
    ea.set('typeface', '宋体')
    cs = rPr.find(qn('a:cs'))
    if cs is None:
        cs = etree.SubElement(rPr, qn('a:cs'))
    cs.set('typeface', '宋体')


def add_text(slide, x, y, w, h, text, size=9, color=NAVY, bold=False,
             align=PP_ALIGN.LEFT, anchor=MSO_ANCHOR.TOP, line_spacing=1.1):
    box = slide.shapes.add_textbox(Inches(x), Inches(y), Inches(w), Inches(h))
    tf = box.text_frame
    tf.word_wrap = True
    tf.margin_left = tf.margin_right = tf.margin_top = tf.margin_bottom = 0
    tf.vertical_anchor = anchor
    for i, line in enumerate(str(text).split('\n')):
        p = tf.paragraphs[0] if i == 0 else tf.add_paragraph()
        p.alignment = align
        p.line_spacing = line_spacing
        r = p.add_run(); r.text = line
        set_run(r, size, color, bold)
    return box


def add_rect(slide, x, y, w, h, fill, line=None, radius=False):
    shp = slide.shapes.add_shape(MSO_SHAPE.ROUNDED_RECTANGLE if radius else MSO_SHAPE.RECTANGLE,
                                 Inches(x), Inches(y), Inches(w), Inches(h))
    shp.fill.solid(); shp.fill.fore_color.rgb = fill
    if line:
        shp.line.color.rgb = line; shp.line.width = Pt(0.75)
    else:
        shp.line.fill.background()
    if radius:
        try:
            shp.adjustments[0] = 0.08
        except Exception:
            pass
    return shp


def add_header(slide, title, subtitle):
    add_rect(slide, 0, 0, 13.333, 0.06, BLUE)
    if os.path.exists(LOGO):
        slide.shapes.add_picture(LOGO, Inches(0.48), Inches(0.22), height=Inches(0.34))
    add_text(slide, 0.48, 0.60, 12.0, 0.42, title, size=18, color=NAVY, bold=True)
    add_text(slide, 0.48, 1.03, 12.3, 0.28, subtitle, size=9.2, color=GREY)


def add_footer(slide, note):
    add_rect(slide, 0.48, 7.02, 12.35, 0.012, RGBColor(0xD9, 0xE2, 0xEE))
    add_text(slide, 0.48, 7.10, 9.9, 0.22, note, size=7.2, color=MIDGREY)
    add_text(slide, 10.55, 7.10, 2.25, 0.22, '守护生命健康，缔造美好生活', size=7.5, color=BLUE, bold=True, align=PP_ALIGN.RIGHT)


def add_table(slide, x, y, w, h, headers, rows, widths, font_size=8.8, header_size=8.5,
              row_height=0.58, center_cols=(), negative_col=None):
    nrows, ncols = len(rows) + 1, len(headers)
    table = slide.shapes.add_table(nrows, ncols, Inches(x), Inches(y), Inches(w), Inches(h)).table
    table.first_row = True
    table.horz_banding = False
    for j, wd in enumerate(widths):
        table.columns[j].width = Inches(wd)
    table.rows[0].height = Inches(0.42)
    for i in range(1, nrows):
        table.rows[i].height = Inches(row_height)

    for j, htext in enumerate(headers):
        cell = table.cell(0, j)
        cell.fill.solid(); cell.fill.fore_color.rgb = NAVY
        cell.margin_left = Inches(0.05); cell.margin_right = Inches(0.04)
        cell.margin_top = Inches(0.02); cell.margin_bottom = Inches(0.02)
        cell.vertical_anchor = MSO_ANCHOR.MIDDLE
        p = cell.text_frame.paragraphs[0]
        p.alignment = PP_ALIGN.CENTER if j in center_cols else PP_ALIGN.LEFT
        r = p.add_run(); r.text = htext
        set_run(r, header_size, WHITE, True)

    for i, row in enumerate(rows, start=1):
        fill = PALE if i % 2 == 0 else WHITE
        for j, val in enumerate(row):
            cell = table.cell(i, j)
            cell.fill.solid(); cell.fill.fore_color.rgb = fill
            cell.margin_left = Inches(0.05); cell.margin_right = Inches(0.04)
            cell.margin_top = Inches(0.02); cell.margin_bottom = Inches(0.02)
            cell.vertical_anchor = MSO_ANCHOR.MIDDLE
            p = cell.text_frame.paragraphs[0]
            p.alignment = PP_ALIGN.CENTER if j in center_cols else PP_ALIGN.LEFT
            p.line_spacing = 1.03
            r = p.add_run(); r.text = str(val)
            color = NAVY
            bold = j == 0
            if negative_col is not None and j == negative_col and '负向' in str(val):
                color = RED
            if j == 0:
                bold = True
            set_run(r, font_size, color, bold)
    return table


prs = Presentation(SRC)
blank = prs.slide_layouts[1]
new_ids = []

def new_slide(title, subtitle):
    s = prs.slides.add_slide(blank)
    for shape in list(s.shapes):
        if shape.is_placeholder:
            shape._element.getparent().remove(shape._element)
    add_header(s, title, subtitle)
    new_ids.append(prs.slides._sldIdLst[-1])
    return s

# ============ Q3 P11-P12 issues ============
s = new_slide('Q3 问题承接：大客户 / 测试单 / 科研', '承接 Q3 汇报 P11–P12｜10 月卡点与进度｜11 月计划')
rows = [
    ['大客户销售目标达成率不好',
     '盯转产（内部/外部）；日级反馈/拉通表；周中、周末短会；销售工具继续打磨',
     '10月 104.08/200.74 万元，51.8%。天隆 003006 替换 ZJ4；丽珠 HBV 客诉与 2000T 验证；迪安结核稳定；海尔施五重被诺唯赞替换。',
     '丽珠 HBV 2000T 转产；迪安结核 2 次 5–10 万人份；天隆 003006 稳定供应；海尔施冻干海外新项目；科华 Tth 体系测试。',
     '刘新元 / 刘子研\n11/30'],
    ['测试单 → 销售单转化效率',
     '10月访谈销售/技术，整理高频问题 Q&A，沉淀到销售工具；周会复盘测试单结构',
     '科研 10 月 65 个测试单、34 张标准订单；16 家客户。Q&A 知识库未查到记录。',
     '11/30 前完成 Q&A 知识库；二单清单：11/30 前 15 组、12/20 前 25 组；测试单按周复盘。',
     '刘欣 / 吴云 / 韩远怀\n11/30'],
    ['科研销售进度严重滞后',
     'KPI 看板日/周/月进度；周会复盘：老产品是否负向、新产品是否正向且反馈正常',
     '10月 4.46/89.7 万元，5.0%；65 个测试单、16 家客户、34 张标准订单；限制性内切酶推出 2 个月仅 6 单；周会/复盘已执行。',
     '11 月目标 94.10 万元；二单冲刺：11/30 前 15 组、12/20 前 25 组；限制性内切酶按客户逐户复盘。',
     '吴云 / 韩远怀\n11/30'],
]
add_table(s, 0.48, 1.45, 12.4, 4.9,
          ['Q3 问题', 'Q3 要求 / 改善措施', '10 月卡点与进度', '11 月计划', '责任人 / 时间'],
          rows, widths=[1.7, 2.75, 3.4, 3.2, 1.35], font_size=8.3, header_size=8.5,
          row_height=1.45, center_cols=(4,))
add_rect(s, 0.48, 6.45, 12.4, 0.38, LIGHT, line=RGBColor(0xD4, 0xE2, 0xF4), radius=True)
add_text(s, 0.62, 6.53, 12.0, 0.2,
         '口径：Q3 问题与改善措施取原汇报；10 月进度取 10 月月报/订单；“未查到记录”不推断。',
         size=7.6, color=GREY)
add_footer(s, 'Q3 P11–P12 承接；10月数据截至 2026-10-31。')

# ============ Q3 P13 test-order monitor ============
s = new_slide('产品线测试单监控：10 月复盘 → 11 月周跟踪', '承接 Q3 汇报 P13｜测试单按订单主产品线归集｜单位：单')
cards = [('10月测试单', '353', BLUE), ('环比9月', '-12.4%', RED), ('测试客户', '137', NAVY), ('科研测试单', '65', AMBER)]
for (label, value, color), x in zip(cards, [0.55, 3.55, 6.55, 9.55]):
    add_rect(s, x, 1.45, 2.75, 0.78, PALE, line=RGBColor(0xDD, 0xE7, 0xF2), radius=True)
    add_text(s, x + 0.18, 1.55, 2.4, 0.2, label, size=8.2, color=GREY)
    add_text(s, x + 0.18, 1.78, 2.4, 0.3, value, size=16, color=color, bold=True)

rows = [
    ['生命科学-分子生物学', '85', '46', '-45.9%', '负向', '科研二单冲刺；高校客户复测；11月每周跟踪'],
    ['诊断原料-RT', '66', '58', '-12.1%', '负向', '重点客户复测；RT 单管/冻干方案'],
    ['诊断原料-PCR', '51', '56', '+9.8%', '正向', '测试转标准订单；抓 TOP 客户'],
    ['生命科学-蛋白质研究', '42', '49', '+16.7%', '正向', '保持；补充应用数据'],
    ['生命科学-测序试剂', '34', '33', '-2.9%', '平稳', '新项目测试；NGS 客户清单'],
    ['诊断原料-恒温扩增', '19', '21', '+10.5%', '正向', 'LAMP 客户测试；Bst 适配'],
    ['诊断原料-分子酶', '13', '15', '+15.4%', '正向', '单酶适配信息开放；技术陪访'],
]
add_table(s, 0.48, 2.55, 12.4, 3.55,
          ['产品线', '9月测试单', '10月测试单', '环比', '判定', '11月动作'],
          rows, widths=[2.75, 1.1, 1.1, 0.95, 0.8, 5.7], font_size=8.8, header_size=8.5,
          row_height=0.50, center_cols=(1, 2, 3, 4), negative_col=4)
add_rect(s, 0.48, 6.36, 12.4, 0.4, LIGHT, line=RGBColor(0xD4, 0xE2, 0xF4), radius=True)
add_text(s, 0.62, 6.45, 12.0, 0.2,
         '11月机制：每周一更新上周测试单；负向产品线由产品经理+区域经理联合说明；基础物料/未分类不计入。',
         size=7.6, color=GREY)
add_footer(s, '数据：orders_detail_2026；测试单=is_test订单；环比=(10月-9月)/9月。')

# ============ Q3 P14 Q4 customer visits ============
s = new_slide('Q4 客户高层拜访：10 月进度与 11 月计划', '承接 Q3 汇报 P14｜目标客户、进展、卡点、下一步')
rows = [
    ['之江', 'RT 单管/冻干；高层引荐',
     '10/31 登革热项目；11/4 开单 3.45 万；客户定版 003006。卡点：NGS 部门尚未进入。',
     '003006 批量订单；NGS 部门拜访；登革热项目放大。', '刘新元\n11/30'],
    ['艾德', 'NGS/肿瘤 SNP；研发+采购',
     '10/27 肿瘤 SNP、FFPE 复杂样本需求；10/16 艾德范思拜访。卡点：NGS 部门/采购未闭环。',
     'NGS 部门+采购总监拜访；SNP 复杂样本测试单。', '刘新元\n11/30'],
    ['安图', 'Tth 自动化/POCT',
     'E10-000725 已采购；Tth 体系测试推进。卡点：自动化/POCT 适配未落单。',
     'Tth 自动化/POCT 测试单；高层维护。', '赵云浩\n11/30'],
    ['明德', '公卫/呼吸道/STD；高层维护',
     '10/23、10/13 跟进；新采购订单推进、回款推进。卡点：冻干重复性、项目并行。',
     '公卫/呼吸道/STD 订单；冻干工艺复核；回款。', '赵云浩\n11/30'],
    ['天隆', '003006/全预混；高层维护',
     '003006 替换 ZJ4；客诉基本解除；10/22、10/28 拜访。卡点：2027 目标/价格。',
     '003006 稳定供应；2027 年度目标沟通；新项目。', '刘新元\n11/30'],
    ['迪安', '结核/000903；董事长',
     '结核项目稳定；000903 25min 方案推进。卡点：2 次 5–10 万人份未落。',
     '结核 2 次 5–10 万人份；000903 25min 放大。', '刘新元\n11/30'],
    ['复星', 'STD；CEO/CTO',
     'STD 与菲鹏对比未闭环；回款压力。卡点：项目结论、账期。',
     'STD 闭环；回款 7 万+账期方案；高层互访。', '刘新元\n11/30'],
]
add_table(s, 0.48, 1.45, 12.4, 4.95,
          ['客户', '目标 / 主推', '10 月进度 / 卡点', '11 月计划', '责任人 / 时间'],
          rows, widths=[0.8, 2.25, 4.4, 3.7, 1.25], font_size=8.2, header_size=8.4,
          row_height=0.61, center_cols=(0, 4))
add_rect(s, 0.48, 6.55, 12.4, 0.38, LIGHT, line=RGBColor(0xD4, 0xE2, 0xF4), radius=True)
add_text(s, 0.62, 6.63, 12.0, 0.2,
         '口径：10月进度取飞书活动/订单/月报；未闭环事项标“卡点”，不把意向写成结果。',
         size=7.6, color=GREY)
add_footer(s, 'Q3 P14 承接；客户拜访责任人按原计划列示。')

# move new slides to after P9 (index 9)
sldIdLst = prs.slides._sldIdLst
for offset, new_id in enumerate(new_ids):
    sldIdLst.remove(new_id)
    sldIdLst.insert(9 + offset, new_id)

prs.save(OUT)
print('saved', OUT)
print('slides', len(prs.slides))