#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""在10月汇报P7后，插入赵云浩、刘子研年度KPI明细两页。"""
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.chart.data import CategoryChartData
from pptx.enum.chart import XL_CHART_TYPE, XL_LEGEND_POSITION
from pptx.oxml.ns import qn
from lxml import etree

ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
SRC = os.path.join(ROOT, '经营分析/月报/2026年10月销售部工作汇报_草稿_20261108_v6.pptx')
OUT = os.path.join(ROOT, '经营分析/月报/2026年10月销售部工作汇报_草稿_20261108_v7.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=8.5, 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)
    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


def rate_color(rate):
    if rate >= 80:
        return GREEN
    if rate >= 60:
        return AMBER
    return RED


def add_table(slide, headers, rows, widths, x=0.48, y=2.55, w=12.4, h=2.5,
              font_size=8.5, header_size=8.3, row_height=0.40):
    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.38)
    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 (1, 2, 3, 4) 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 (1, 2, 3, 4) else PP_ALIGN.LEFT
            p.line_spacing = 1.03
            r = p.add_run(); r.text = str(val)
            color = NAVY
            if j == 4:
                try:
                    color = rate_color(float(str(val).split('%')[0]))
                except Exception:
                    color = NAVY
            set_run(r, font_size, color, j == 0)


def add_cards(slide, target, actual, rate, gap, note=''):
    items = [('年度目标', f'{target:,.2f}', BLUE), ('1-10月实际', f'{actual:,.2f}', NAVY),
             ('年度达成率', f'{rate:.1f}%', rate_color(rate)), ('年度缺口', f'{gap:,.2f}', RED)]
    for (label, value, color), x in zip(items, [0.48, 3.55, 6.62, 9.69]):
        add_rect(slide, x, 1.45, 2.78, 0.82, PALE, line=RGBColor(0xDD, 0xE7, 0xF2), radius=True)
        add_text(slide, x + 0.18, 1.55, 2.45, 0.2, label, size=8.2, color=GREY)
        add_text(slide, x + 0.18, 1.78, 2.45, 0.3, value, size=16.5, color=color, bold=True)


def add_month_chart(slide, values, title):
    chart_data = CategoryChartData()
    chart_data.categories = [f'{i}月' for i in range(1, 11)]
    chart_data.add_series('月度实际', values)
    ch = slide.shapes.add_chart(XL_CHART_TYPE.COLUMN_CLUSTERED, Inches(0.48), Inches(5.05), Inches(7.35), Inches(1.82), chart_data).chart
    ch.has_title = True
    ch.chart_title.text_frame.text = title
    for p in ch.chart_title.text_frame.paragraphs:
        for r in p.runs:
            set_run(r, 9, NAVY, True)
    ch.has_legend = False
    ch.font.size = Pt(7.5); ch.font.name = 'Times New Roman'
    ch.plots[0].gap_width = 45
    ch.series[0].format.fill.solid(); ch.series[0].format.fill.fore_color.rgb = BLUE
    ch.value_axis.has_major_gridlines = True
    ch.value_axis.major_gridlines.format.line.color.rgb = RGBColor(0xE6, 0xEC, 0xF3)
    ch.value_axis.tick_labels.font.size = Pt(7)
    ch.category_axis.tick_labels.font.size = Pt(7.2)
    return ch


def add_header(s, title, subtitle):
    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, title, size=18, color=NAVY, bold=True)
    add_text(s, 0.48, 1.03, 12.3, 0.28, subtitle, size=9.2, color=GREY)


def add_footer(s, note):
    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, note, 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)


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

# ================= 赵云浩 =================
s = new_slide('赵云浩｜年度KPI明细', '个人口径｜2026年1-10月｜单位：万元')
add_cards(s, 1350.00, 532.67, 39.5, 817.33)
rows = [
    ['销售额完成率 X', '60%', '正常1,350\n底线810 / 挑战1,755', '532.67', '39.5% / 0', '低于底线 277.33；距挑战 1,222.33'],
    ['大客户丢失 Y1', '关键项内', '15个2025有销售集团', '丢4个', '0.5', '圣湘、明德、中道、华之源'],
    ['重点大客户增长 Z', '10%', '圣湘+万孚 662.60', '312.43\n47.2%', '0.5', '圣湘30.0%；万孚140.3%'],
    ['河南客户增长 D', '10%', '河南客户 91.23', '114.53\n125.5%', '1.0', '按客户名代理，附件清单待核'],
    ['上级主观评价', '20%', '—', '待补', '待补', '责任书要求直接/间接上级评价'],
]
add_table(s, ['KPI维度', '权重', '年度目标', '1-10月实际', '达成/系数', '口径/说明'], rows,
          widths=[1.8, 0.75, 2.0, 1.55, 1.2, 5.1], font_size=8.3, header_size=8.2, row_height=0.46)
add_month_chart(s, [23.744, 45.847, 59.387, 53.326, 36.469, 68.015, 46.598, 65.696, 63.634, 69.953], '月度实际（万元）')
add_rect(s, 8.12, 5.05, 4.76, 1.82, LIGHT, line=RGBColor(0xD4, 0xE2, 0xF4), radius=True)
add_text(s, 8.30, 5.18, 4.4, 0.22, '缺口与结构', size=10, color=BLUE, bold=True)
add_text(s, 8.30, 5.48, 4.4, 1.25,
         ['• 11-12月需 817.33 万，月均 408.67 万。',
          '• 圣湘+万孚合计 47.2%，是 Z 的主要拖累。',
          '• 河南客户 125.5%，是当前正增长项。'],
         size=8.3, color=NAVY, line_spacing=1.28)
add_footer(s, '目标/实际=目标基础数据与KPI看板1-10月；开票额=ERP含税代理；Y1/Z/D按责任书口径。')

# ================= 刘子研 =================
s = new_slide('刘子研｜年度KPI明细', '个人口径｜2026年1-10月｜单位：万元')
add_cards(s, 1300.00, 675.81, 52.0, 624.19)
rows = [
    ['年度销售目标 X1', '60%', '1,300.00', '675.81', '52.0% / —', '11-12月需 624.19，月均 312.10'],
    ['大客户丢失 Y1', '关键项内', '11个2025大客户集团', '丢5个', '0.5', '天隆、佰奥莱博、海尔施、雅为、陆军军医'],
    ['重点大客户增长 Z', '10%', '天隆+丽珠 500.17', '376.37\n75.3%', '0.5', '天隆52.4%；丽珠99.9%'],
    ['山头客户 E', '10%', '之江、艾德 2家', '1/2', '1.5', '之江3.45万完成；艾德未达1.5万'],
    ['上级主观评价', '未展示', '—', '待补', '待补', '截图未展示主观项'],
]
add_table(s, ['KPI维度', '权重', '年度目标', '1-10月实际', '达成/系数', '口径/说明'], rows,
          widths=[1.8, 0.75, 2.0, 1.55, 1.2, 5.1], font_size=8.3, header_size=8.2, row_height=0.46)
add_month_chart(s, [68.921, 47.801, 214.661, 38.242, 29.548, 108.699, 42.232, 17.662, 73.916, 34.125], '月度实际（万元）')
add_rect(s, 8.12, 5.05, 4.76, 1.82, LIGHT, line=RGBColor(0xD4, 0xE2, 0xF4), radius=True)
add_text(s, 8.30, 5.18, 4.4, 0.22, '缺口与结构', size=10, color=BLUE, bold=True)
add_text(s, 8.30, 5.48, 4.4, 1.25,
         ['• 11-12月需 624.19 万，月均 312.10 万。',
          '• Y1 丢 5 个集团；Z 天隆+丽珠 75.3%。',
          '• E 之江已完成；艾德 NGS 仍是缺口。'],
         size=8.3, color=NAVY, line_spacing=1.28)
add_footer(s, '目标/实际=目标基础数据与KPI看板1-10月；Y1按大客户1清单；Z天隆+丽珠；E之江/艾德。')

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

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