#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""把赵云浩11月W1周报提炼成月报页，并回写11月待办中的进度。"""
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_v5.pptx')
OUT = os.path.join(ROOT, '经营分析/月报/2026年10月销售部工作汇报_草稿_20261108_v6.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)
WHITE = RGBColor(0xFF, 0xFF, 0xFF)


def set_run(run, size=8.4, 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.08):
    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)


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


def set_cell(cell, text, size=8.2, color=NAVY, bold=False, align=PP_ALIGN.LEFT):
    cell.text_frame.clear()
    p = cell.text_frame.paragraphs[0]
    p.alignment = align
    p.line_spacing = 1.02
    r = p.add_run(); r.text = str(text)
    set_run(r, size, color, bold)


prs = Presentation(SRC)

# ---- update P14 stuck rows with W1 progress ----
p14 = prs.slides[13]
table = None
for sh in p14.shapes:
    if sh.has_table:
        table = sh.table
        break
if table is None:
    raise SystemExit('P14 table not found')
# row order: 1 明日达829, 2 明日达M2244, 3 圣湘RT, 4 圣湘质量, 5 丹威, 6 新产业, 7 艾德
updates = {
    1: ('内部冻干样品已发客户，待测试；客户物料无阳性质粒。',
        '冻干后性能/耐抑制未解；阳性样本不足。',
        '跟进客户测试；补阳性样本；本周上机。'),
    2: ('可复现 20260208 批低浓度曲线下降；后续复测不稳定；客户要求 3–5d 加速。',
        '客诉未闭环；批次差异/放行标准未定。',
        '做 3–5d 加速；根因复测；质量+研发评审。'),
    4: ('1500ml 一周内、4000ml 两周内采购预期；质量协议仍待客户反馈。',
        '质量条款未定，影响协议签署。',
        '锁库存/预测单；质量协议逐条对齐。'),
}
for r, vals in updates.items():
    for c, val in zip((1, 2, 3), vals):
        cell = table.cell(r, c)
        color = RED if c == 2 else NAVY
        set_cell(cell, val, 8.2, color, False)

# ---- new W1 page ----
blank = prs.slide_layouts[1]
s = prs.slides.add_slide(blank)
for shape in list(s.shapes):
    if shape.is_placeholder:
        shape._element.getparent().remove(shape._element)

add_rect(s, 0, 0, 13.333, 0.06, BLUE)
if os.path.exists(LOGO):
    s.shapes.add_picture(LOGO, Inches(0.48), Inches(0.22), height=Inches(0.34))
add_text(s, 0.48, 0.60, 12.0, 0.42, '11月W1重点进展与新增卡点｜赵云浩', size=18, color=NAVY, bold=True)
add_text(s, 0.48, 1.03, 12.3, 0.28,
         '提炼自 11月W1周报｜只列影响 Q4 目标/客户决策的事项，日常测试不单列',
         size=9.2, color=GREY)

rows = [
    ['圣湘\n呼六预混',
     'MD2104-000838-DB：CY5 线型异常；4℃ 7d 荧光下降 19%（客户标准 15%）；空白起线（内标）。',
     '稳定性不达标；客户尚未筛到合格酶，仍有测试机会。',
     '按线型→4℃→空白顺序优化；本周给客户新方案。',
     '赵云浩 / 研发\n11/30'],
    ['圣湘\n呼吸道冻干',
     'NA 版本背景菌污染（流感嗜血、肺克、鲍曼、铜绿）；已安排重产洁净批次。',
     '质量风险；客户测试被污染卡住。',
     '洁净批次生产+客户复测；单酶测试同步。',
     '赵云浩 / 质量\n11/30'],
    ['圣湘\n公卫',
     '1500ml 一周内、4000ml 两周内采购预期；质量协议仍在客户侧。',
     '订单机会明确，协议/放行标准未定。',
     '锁库存/预测单；质量协议逐条对齐。',
     '刘子研 / 质量\n11/30'],
    ['明日达\n829 + M2244',
     '829 冻干样品已发客户待测；M2244 可复现 20260208 低浓度下降，复测不稳定，客户要 3–5d 加速。',
     '客诉未闭环；冻干后性能待验证。',
     '跟进 829 测试；M2244 做 3–5d 加速与根因复测。',
     '赵云浩 / 技术支持\n11/30'],
    ['安图\nTth',
     'E10-000725：40ml 成品、500ml 物料已备；价格仍在拉扯，走梯度价。',
     '订单临近，价格/审批未定。',
     '价格审批；跟进客户新测试结果。',
     '赵云浩 / 11/30'],
    ['万孚\nPOCT',
     '新酶尚未完成客户 POCT 上机；FE20-NA 小样已发；污水项目预计下周出结果。',
     'Q4 关键节点，数据未闭环。',
     'POCT 上机排期；污水结果；NA 背景核酸。',
     '赵云浩 / 11/30'],
    ['明德\n甲乙冻干',
     '批间异常怀疑冻干球堆叠超过 3–4 层；客户周一给验证结果。',
     '冻干工艺问题，影响批次稳定性。',
     '堆叠层数验证；固化生产工艺规范。',
     '赵云浩 / 技术支持\n11/30'],
]

headers = ['客户/项目', 'W1 进展', '判断/卡点', '11月动作', '责任人/时间']
widths = [1.45, 4.0, 2.75, 3.05, 1.15]
nrows, ncols = len(rows) + 1, 5
table = s.shapes.add_table(nrows, ncols, Inches(0.48), Inches(1.45), Inches(12.4), Inches(5.02)).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.40)
for i in range(1, nrows):
    table.rows[i].height = Inches(0.65)
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 (0, 4) else PP_ALIGN.LEFT
    r = p.add_run(); r.text = htext
    set_run(r, 8.4, 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 (0, 4) else PP_ALIGN.LEFT
        p.line_spacing = 1.02
        r = p.add_run(); r.text = str(val)
        color = RED if j == 2 else NAVY
        set_run(r, 8.0, color, j == 0)

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,
         '未单列：许昌学院回款到账、中道剩余 1 万回款、纳克/普泰日常测试；这些按周报继续跟踪。',
         size=7.2, color=GREY)
add_rect(s, 0.48, 7.02, 12.35, 0.012, RGBColor(0xD9, 0xE2, 0xEE))
add_text(s, 0.48, 7.10, 9.9, 0.22, '来源：销售工程师周报-赵云浩（2026年11月W1）；10月月报承接页。', size=7.1, color=MIDGREY)
add_text(s, 10.55, 7.10, 2.25, 0.22, '守护生命健康，缔造美好生活', size=7.5, color=BLUE, bold=True, align=PP_ALIGN.RIGHT)

# insert after P14 (index 14)
sldIdLst = prs.slides._sldIdLst
new_id = sldIdLst[-1]
sldIdLst.remove(new_id)
sldIdLst.insert(14, new_id)

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