""" PPT builder: assemble daily/weekly/monthly reports by duplicating master templates and filling charts, tables, KPI cards, and structured insight text blocks. Key design principle: Conclusion-first page titles + structured multi-paragraph insights (title + body per paragraph) aligned with reference PPT style. """ import copy import os import sys from pathlib import Path from datetime import datetime, timedelta sys.path.insert(0, str(Path(__file__).parent)) from pptx import Presentation from pptx.util import Emu, Pt from pptx.dml.color import RGBColor from pptx.enum.text import PP_ALIGN from pptx.enum.shapes import MSO_SHAPE from data_loader import load_daily, load_weekly, load_monthly, load_date_range from metrics_calculator import calc_daily_metrics, calc_weekly_metrics, calc_monthly_metrics, generate_deep_insights from chart_factory import ( add_column_chart, add_bar_chart, add_line_chart, add_doughnut_chart, add_pie_chart, add_funnel_chart, add_horizontal_bar_chart, add_grouped_bar_chart, add_table ) # Colors — aligned with reference design theme YAML C_PRIMARY = RGBColor(0x1E, 0x3A, 0x5F) C_ACCENT = RGBColor(0x10, 0xB9, 0x81) C_ACCENT_NEG = RGBColor(0xEF, 0x44, 0x44) C_SECONDARY = RGBColor(0x64, 0x74, 0x8B) C_DARK = RGBColor(0x1F, 0x3A, 0x5C) C_WHITE = RGBColor(0xFF, 0xFF, 0xFF) C_GRAY_BG = RGBColor(0xF2, 0xF2, 0xF2) C_TEXT = RGBColor(0x33, 0x33, 0x33) C_TEXT_GRAY = RGBColor(0x66, 0x66, 0x66) C_LINE = RGBColor(0xD9, 0xD9, 0xD9) C_CARD_BG = RGBColor(0xE7, 0xF0, 0xF7) C_GREEN = RGBColor(0x10, 0xB9, 0x81) C_RED = RGBColor(0xEF, 0x44, 0x44) C_ORANGE = RGBColor(0xED, 0x7D, 0x31) # ============================================================================== # MASTER / SLIDE HELPERS # ============================================================================== def get_master_template(report_type: str) -> str: """Route report type to corresponding master template.""" base = os.path.join(os.path.dirname(__file__), '..', 'assets') template_map = { 'daily': os.path.join(base, 'report-master.pptx'), 'weekly': os.path.join(base, 'weekly-master.pptx'), 'monthly': os.path.join(base, 'monthly-master.pptx'), } path = template_map.get(report_type, template_map['daily']) if os.path.exists(path): return os.path.abspath(path) # Fallbacks for fallback in [template_map['daily']]: if os.path.exists(fallback): return os.path.abspath(fallback) raise FileNotFoundError(f"Master template not found for {report_type}") def _detect_content_top(slide) -> int: """Detect content start Y from a content slide template by reading {page_title} position.""" page_title_bottom = Emu(1422400) # daily default for shape in slide.shapes: if shape.has_text_frame and '{page_title}' in shape.text_frame.text: page_title_bottom = shape.top + shape.height break # Gap: generous spacing between page title and content to avoid crowding gap = Emu(381000) return int(page_title_bottom) + int(gap) def _delete_template_slides(prs, count=4): for _ in range(count): if len(prs.slides) == 0: break rId = prs.slides._sldIdLst[0].rId prs.part.drop_rel(rId) del prs.slides._sldIdLst[0] def _duplicate_slide(prs, source_slide): blank_layout = prs.slide_layouts[6] new_slide = prs.slides.add_slide(blank_layout) for shape in source_slide.shapes: el = shape.element new_el = copy.deepcopy(el) new_slide.shapes._spTree.insert_element_before(new_el, 'p:extLst') return new_slide def _replace_placeholder(slide, placeholder, new_text): for shape in slide.shapes: if not shape.has_text_frame: continue for para in shape.text_frame.paragraphs: if placeholder in para.text: para.text = para.text.replace(placeholder, str(new_text)) for run in para.runs: run.font.name = '微软雅黑' def _replace_all_placeholders(slide, mapping: dict): for placeholder, new_text in mapping.items(): _replace_placeholder(slide, placeholder, new_text) # ============================================================================== # NAVIGATION TABS # ============================================================================== def _add_nav_tabs(slide, tabs, active_index=0, slide_width=None, tab_y=Emu(254000), tab_h=Emu(762000), underline_h=Emu(127000)): if slide_width is None: slide_width = slide.shapes._spTree.getparent().getparent().attrib.get('cx') slide_width = Emu(int(slide_width)) if slide_width else Emu(16256000) n = len(tabs) tab_w = Emu(int(slide_width) // n) for i, label in enumerate(tabs): x = Emu(i * int(tab_w)) box = slide.shapes.add_textbox(x, tab_y, tab_w, tab_h) p = box.text_frame.paragraphs[0] p.text = label p.font.size = Pt(11) p.font.name = '微软雅黑' p.font.color.rgb = C_PRIMARY if i == active_index else C_TEXT_GRAY p.alignment = PP_ALIGN.CENTER if i == active_index: line = slide.shapes.add_shape(MSO_SHAPE.RECTANGLE, x, Emu(457200), tab_w, underline_h) line.fill.solid() line.fill.fore_color.rgb = C_PRIMARY line.line.fill.background() # ============================================================================== # KPI CARDS # ============================================================================== def _add_kpi_cards(slide, kpis, start_x=Emu(762000), start_y=Emu(1651000)): """Draw 3x2 KPI card grid. Each kpi: {'label', 'value', 'unit', 'change', 'sub'}""" positions = [ (start_x, start_y), (Emu(5778500), start_y), (Emu(10795000), start_y), (start_x, Emu(start_y + 3429000)), (Emu(5778500), Emu(start_y + 3429000)), (Emu(10795000), Emu(start_y + 3429000)), ] for i, kpi in enumerate(kpis[:6]): if i >= len(positions): break x, y = positions[i] w, h = Emu(4699000), Emu(3048000) card = slide.shapes.add_shape(MSO_SHAPE.ROUNDED_RECTANGLE, x, y, w, h) card.fill.solid() card.fill.fore_color.rgb = C_CARD_BG card.line.fill.background() # Label lbl = slide.shapes.add_textbox(Emu(x + 508000), Emu(y + 228600), Emu(2540000), Emu(406400)) p = lbl.text_frame.paragraphs[0] p.text = kpi.get('label', '') p.font.size = Pt(14) p.font.color.rgb = C_TEXT_GRAY p.font.name = '微软雅黑' # Value val = slide.shapes.add_textbox(Emu(x + 508000), Emu(y + 762000), Emu(2540000), Emu(698500)) p = val.text_frame.paragraphs[0] p.text = str(kpi.get('value', '')) p.font.size = Pt(36) p.font.bold = True p.font.color.rgb = C_PRIMARY p.font.name = 'Arial' # Unit unit = kpi.get('unit', '') if unit: ubox = slide.shapes.add_textbox(Emu(x + 3048000), Emu(y + 1016000), Emu(508000), Emu(381000)) p = ubox.text_frame.paragraphs[0] p.text = unit p.font.size = Pt(14) p.font.color.rgb = C_TEXT_GRAY p.font.name = '微软雅黑' # Change badge chg = kpi.get('change', '') if chg: cbox = slide.shapes.add_textbox(Emu(x + 508000), Emu(y + 1778000), Emu(4064000), Emu(304800)) p = cbox.text_frame.paragraphs[0] p.text = chg p.font.size = Pt(12) chg_str = str(chg) is_positive = chg_str.startswith('+') or any(k in chg_str for k in ['↑', '提升', '增长', '上调', '增加', '大幅', '好', '突破', '达成', '优化']) is_negative = chg_str.startswith('-') or any(k in chg_str for k in ['↓', '下滑', '下降', '减少', '回落', '滞后', '堆积', '阻塞', '缺口', '延迟']) if is_negative: p.font.color.rgb = C_RED elif is_positive: p.font.color.rgb = C_GREEN else: p.font.color.rgb = C_TEXT_GRAY p.font.name = '微软雅黑' # Sub note with semantic background color tag (e.g. "日均51笔") sub = kpi.get('sub', '') if sub: sub_text = _truncate_text(sub, 20) tag_color = _sentiment_color(sub_text) tag_x = Emu(x + 508000) tag_y = Emu(y + 2159000) tag_w = Emu(min(len(sub_text) * 220000 + 400000, 3600000)) tag_h = Emu(304800) if tag_color: tag_bg = slide.shapes.add_shape(MSO_SHAPE.ROUNDED_RECTANGLE, tag_x, tag_y, tag_w, tag_h) tag_bg.fill.solid() tag_bg.fill.fore_color.rgb = tag_color tag_bg.line.fill.background() sbox = slide.shapes.add_textbox(tag_x, tag_y, tag_w, tag_h) p = sbox.text_frame.paragraphs[0] p.text = sub_text p.font.size = Pt(11) p.font.color.rgb = C_TEXT_GRAY p.font.name = '微软雅黑' p.alignment = PP_ALIGN.CENTER # ============================================================================== # TEXT BLOCKS # ============================================================================== def _add_text_block(slide, title, body, left, top, width, height, title_size=Pt(14), body_size=Pt(11), line_space=Pt(6)): """Single text box with title + body.""" box = slide.shapes.add_textbox(left, top, width, height) tf = box.text_frame tf.word_wrap = True p = tf.paragraphs[0] p.text = title p.font.size = title_size p.font.bold = True p.font.color.rgb = C_PRIMARY if title else C_TEXT p.font.name = '微软雅黑' if body: p2 = tf.add_paragraph() p2.text = body p2.font.size = body_size p2.font.color.rgb = C_TEXT p2.font.name = '微软雅黑' p2.space_before = line_space p2.line_spacing = 1.3 def _estimate_text_height(items, title_size_pt, body_size_pt, width_emu, line_spacing=1.15, title_extra=1.3): """Estimate rendered text height in EMU for adaptive font sizing.""" width_pt = width_emu / 12700.0 chars_per_line_body = max(10, int(width_pt / (body_size_pt * 1.15))) chars_per_line_title = max(10, int(width_pt / (title_size_pt * 1.15))) line_height_body = int(body_size_pt * line_spacing * 12700) line_height_title = int(title_size_pt * title_extra * 12700) total = 0 for item in items: title = item.get('title', '') content = item.get('content', '') title_lines = max(1, (len(title) + chars_per_line_title - 1) // chars_per_line_title) content_lines = max(1, (len(content) + chars_per_line_body - 1) // chars_per_line_body) total += title_lines * line_height_title + content_lines * line_height_body + int(6 * 12700) return total def _add_structured_insight(slide, items, left, top, width, height, title_size=Pt(12), body_size=Pt(11), max_items=None, min_body_size=Pt(9)): """ High-density structured multi-paragraph insight block. items: list of {'title': str, 'content': str} Features: - No truncation; full content rendered - No max_items limit by default (render all) - Auto-shrink body font to fit within height (down to min_body_size) - Compact line spacing (1.15) to maximize density - Each bullet has emoji + bold title + normal body """ if not items: return # Adaptive font sizing: shrink body_size until it fits target_height = int(height) # title_size/body_size may be EMU integers or Pt objects; normalize to pt _ts = float(title_size) / 12700.0 if float(title_size) > 1000 else float(title_size) _bs = float(body_size) / 12700.0 if float(body_size) > 1000 else float(body_size) _min_bs = float(min_body_size) / 12700.0 if float(min_body_size) > 1000 else float(min_body_size) ts_pt = _ts bs_pt = _bs min_bs_pt = _min_bs # Binary-search-like shrink to fit while bs_pt > min_bs_pt: est = _estimate_text_height(items, ts_pt, bs_pt, int(width)) if est <= target_height: break bs_pt -= 0.5 ts_pt = max(bs_pt + 1, ts_pt - 0.25) box = slide.shapes.add_textbox(left, top, width, height) tf = box.text_frame tf.word_wrap = True first = True for item in items[:max_items] if max_items else items: if not first: spacer = tf.add_paragraph() spacer.text = '' spacer.space_before = Pt(3) title = item.get('title', '') emoji = _emoji_for_item(title) # Avoid double emoji if emoji and title.startswith(emoji): emoji = '' title_text = f'{emoji} {title}' if emoji else title p = tf.paragraphs[0] if first else tf.add_paragraph() p.text = title_text p.font.size = Pt(ts_pt) p.font.bold = True p.font.color.rgb = C_PRIMARY p.font.name = '微软雅黑' p.line_spacing = 1.15 first = False content = item.get('content', '') if content: p2 = tf.add_paragraph() p2.text = content p2.font.size = Pt(bs_pt) p2.font.color.rgb = C_TEXT p2.font.name = '微软雅黑' p2.line_spacing = 1.15 p2.space_before = Pt(1) # ============================================================================== # ALERT / ACTION / ISSUE / GOAL CARDS # ============================================================================== def _add_alert_cards(slide, alerts, start_y=Emu(1651000)): """Draw 1-3 alert cards horizontally. Supports 严重/中度/一般 levels.""" colors = {'严重': C_RED, '警告': C_ORANGE, '关注': C_PRIMARY, '中度': C_ORANGE, '一般': C_SECONDARY} positions = [Emu(762000), Emu(5778500), Emu(10795000)] for i, alert in enumerate(alerts[:3]): x = positions[i] y = start_y lvl = alert.get('level', '关注') c = colors.get(lvl, C_PRIMARY) bar = slide.shapes.add_shape(MSO_SHAPE.RECTANGLE, x, y, Emu(50800), Emu(2286000)) bar.fill.solid() bar.fill.fore_color.rgb = c bar.line.fill.background() tbox = slide.shapes.add_textbox(Emu(x + 101600), Emu(y + 228600), Emu(4064000), Emu(406400)) p = tbox.text_frame.paragraphs[0] p.text = alert.get('title', '') p.font.size = Pt(15) p.font.bold = True p.font.color.rgb = C_TEXT p.font.name = '微软雅黑' dbox = slide.shapes.add_textbox(Emu(x + 101600), Emu(y + 762000), Emu(4064000), Emu(1270000)) tf = dbox.text_frame tf.word_wrap = True p = tf.paragraphs[0] p.text = alert.get('detail', '') p.font.size = Pt(11) p.font.color.rgb = C_TEXT p.font.name = '微软雅黑' def _add_action_cards(slide, actions, start_y=Emu(2540000)): """Draw 3 action cards horizontally.""" positions = [Emu(762000), Emu(5778500), Emu(10795000)] for i, act in enumerate(actions[:3]): x = positions[i] y = start_y bar = slide.shapes.add_shape(MSO_SHAPE.RECTANGLE, x, y, Emu(50800), Emu(406400)) bar.fill.solid() bar.fill.fore_color.rgb = C_PRIMARY bar.line.fill.background() tbox = slide.shapes.add_textbox(Emu(x + 101600), Emu(y + 952500), Emu(4064000), Emu(406400)) p = tbox.text_frame.paragraphs[0] p.text = act.get('title', '') p.font.size = Pt(17) p.font.bold = True p.font.color.rgb = C_TEXT p.font.name = '微软雅黑' dbox = slide.shapes.add_textbox(Emu(x + 101600), Emu(y + 1524000), Emu(4064000), Emu(3429000)) tf = dbox.text_frame tf.word_wrap = True p = tf.paragraphs[0] p.text = act.get('detail', '') p.font.size = Pt(11) p.font.color.rgb = C_TEXT p.font.name = '微软雅黑' p.line_spacing = 1.3 def _add_issue_cards(slide, issues, start_y=Emu(1524000)): """Draw stacked issue cards with severity, title, detail, action.""" colors = {'严重': C_RED, '中度': C_ORANGE, '轻度': C_PRIMARY, '一般': C_SECONDARY} for i, issue in enumerate(issues[:3]): x = Emu(762000) y = Emu(int(start_y) + i * (1778000 + 254000)) sev = issue.get('severity', '中度') c = colors.get(sev, C_ORANGE) bar = slide.shapes.add_shape(MSO_SHAPE.RECTANGLE, x, y, Emu(50800), Emu(1778000)) bar.fill.solid() bar.fill.fore_color.rgb = c bar.line.fill.background() sbox = slide.shapes.add_textbox(Emu(x + 101600), Emu(y + 228600), Emu(660400), Emu(304800)) p = sbox.text_frame.paragraphs[0] p.text = sev p.font.size = Pt(11) p.font.bold = True p.font.color.rgb = c p.font.name = '微软雅黑' tbox = slide.shapes.add_textbox(Emu(x + 1778000), Emu(y + 228600), Emu(13462000), Emu(355600)) p = tbox.text_frame.paragraphs[0] p.text = issue.get('title', '') p.font.size = Pt(13) p.font.bold = True p.font.color.rgb = C_TEXT p.font.name = '微软雅黑' dbox = slide.shapes.add_textbox(Emu(x + 101600), Emu(y + 698500), Emu(14224000), Emu(355600)) p = dbox.text_frame.paragraphs[0] p.text = issue.get('detail', '') p.font.size = Pt(11) p.font.color.rgb = C_TEXT p.font.name = '微软雅黑' abox = slide.shapes.add_textbox(Emu(x + 101600), Emu(y + 1193800), Emu(14224000), Emu(609600)) tf = abox.text_frame tf.word_wrap = True p = tf.paragraphs[0] p.text = f"建议措施：{issue.get('action', '')}" p.font.size = Pt(11) p.font.color.rgb = C_TEXT_GRAY p.font.name = '微软雅黑' def _add_goal_cards(slide, goals, start_y=Emu(1524000)): """Draw G1-G4 goal cards in 2x2 grid with icon+title+detail.""" sy = int(start_y) positions = [ (Emu(762000), Emu(sy)), (Emu(8318500), Emu(sy)), (Emu(762000), Emu(sy + 1879600)), (Emu(8318500), Emu(sy + 1879600)), ] icon_chars = ['🎯', '💰', '🚀', '⚡'] for i, goal in enumerate(goals[:4]): x, y = positions[i] gid = goal.get('id', f'G{i+1}') gbox = slide.shapes.add_textbox(x, Emu(y + 101600), Emu(635000), Emu(355600)) p = gbox.text_frame.paragraphs[0] p.text = f"{icon_chars[i % len(icon_chars)]} {gid}" p.font.size = Pt(16) p.font.bold = True p.font.color.rgb = C_PRIMARY p.font.name = 'Arial' tbox = slide.shapes.add_textbox(Emu(x + 863600), Emu(y + 101600), Emu(6096000), Emu(355600)) p = tbox.text_frame.paragraphs[0] p.text = goal.get('title', '') p.font.size = Pt(14) p.font.bold = True p.font.color.rgb = C_TEXT p.font.name = '微软雅黑' dbox = slide.shapes.add_textbox(Emu(x + 228600), Emu(y + 571500), Emu(6731000), Emu(863600)) tf = dbox.text_frame tf.word_wrap = True p = tf.paragraphs[0] p.text = goal.get('detail', '') p.font.size = Pt(11) p.font.color.rgb = C_TEXT_GRAY p.font.name = '微软雅黑' p.line_spacing = 1.3 def _add_summary_text(slide, text, left=Emu(1016000), top=Emu(5435600), width=Emu(14224000), height=Emu(1270000)): box = slide.shapes.add_textbox(left, top, width, height) tf = box.text_frame tf.word_wrap = True p = tf.paragraphs[0] p.text = text p.font.size = Pt(12) p.font.color.rgb = C_TEXT p.font.name = '微软雅黑' p.line_spacing = 1.3 # ============================================================================== # STRUCTURED INSIGHT GENERATORS # ============================================================================== def _insight_trend_structured(trend_dates, trend_vals, metrics, period_name='近10天'): """Generate structured multi-paragraph trend insight.""" items = [] if not trend_vals or len(trend_vals) < 2: items.append({'title': '数据概览', 'content': f'{period_name}订单数据平稳，暂无明显波动。'}) return items peak = max(trend_vals) peak_idx = trend_vals.index(peak) low = min(trend_vals) low_idx = trend_vals.index(low) last = trend_vals[-1] first = trend_vals[0] total = sum(trend_vals) avg = total / len(trend_vals) # Paragraph 1: Order scale curr_orders = metrics.get('tracking_orders', last) prev_orders = metrics.get('prev_tracking_orders', 0) order_chg = _pct_val(curr_orders, prev_orders) curr_qty = metrics.get('total_qty', 0) prev_qty = metrics.get('prev_total_qty', 0) qty_chg = _pct_val(curr_qty, prev_qty) avg_size = metrics.get('avg_order_size', 0) prev_avg_size = metrics.get('prev_avg_order_size', 0) scale_text = f'今日订单量{curr_orders}单' if prev_orders > 0: diff = curr_orders - prev_orders scale_text += f'，较昨日{"增加" if diff >= 0 else "减少"}{abs(diff)}单' if curr_qty > 0: scale_text += f'，订单总数量{curr_qty:,}台' if prev_qty > 0: qdiff = curr_qty - prev_qty scale_text += f'，较昨日{"增加" if qdiff >= 0 else "减少"}{abs(qdiff)}台' if avg_size > 0: scale_text += f'，单笔订单平均规模{avg_size:.0f}台' if prev_avg_size > 0: adiff = avg_size - prev_avg_size if abs(adiff) >= 1: scale_text += f'（{"上升" if adiff >= 0 else "下降"}{abs(adiff):.0f}台）' items.append({'title': '订单规模分析', 'content': scale_text}) # Paragraph 2: Peak fluctuation peak_text = '' if peak == last: peak_text = f'今日达到峰值{peak}单，为{period_name}最高水平。' elif low == last: peak_text = f'今日回落至{last}单，为{period_name}最低水平。' else: peak_text = f'峰值出现在{trend_dates[peak_idx]}（{peak}单），低谷在{trend_dates[low_idx]}（{low}单）。' # Describe recovery pattern if len(trend_vals) >= 3: recent = trend_vals[-3:] if recent[-1] > recent[-2] and recent[-2] < recent[0]: peak_text += f'连续回落后今日回升至{last}单，呈现反弹态势。' elif recent[-1] < recent[-2]: peak_text += f'近期呈回落趋势，需关注后续走势。' items.append({'title': '峰值波动', 'content': peak_text}) # Paragraph 3: Activity / update updated = metrics.get('updated_orders', 0) prev_updated = metrics.get('prev_updated_orders', 0) if updated > 0 or prev_updated > 0: act_text = f'今日进度更新{updated}单' if prev_updated > 0: udiff = updated - prev_updated upct = _pct_val(updated, prev_updated) act_text += f'，较昨日{"增加" if udiff >= 0 else "减少"}{abs(udiff)}单（{upct:+.1f}%）' if abs(upct) > 20: act_text += '，团队活跃度波动较大。' if abs(upct) > 30 else '，团队活跃度有所变化。' else: act_text += '，团队活跃度保持平稳。' else: act_text += '。' items.append({'title': '活跃度分析', 'content': act_text}) return items def _insight_status_structured(status_dist, prev_status_dist=None): """Generate structured status distribution insight.""" items = [] total = sum(status_dist.values()) if not total: items.append({'title': '状态概览', 'content': '暂无订单状态数据。'}) return items max_status = max(status_dist.items(), key=lambda x: x[1]) max_pct = max_status[1] / total * 100 # Production share (C+D) prod = status_dist.get('已付订金待生产', 0) + status_dist.get('已生产待付尾款', 0) prod_pct = prod / total * 100 status_text = f'{max_status[0]}占比最高（{max_status[1]}单，{max_pct:.1f}%）' if prod_pct > 0: status_text += f'。生产端（已付订金+已生产）合计{prod}单（{prod_pct:.1f}%）' if prod_pct > 30: status_text += '，生产推进力度加大。' else: status_text += '。' items.append({'title': '状态分布特征', 'content': status_text}) # WoW change if prev_status_dist: changes = [] for name, curr in status_dist.items(): prev = prev_status_dist.get(name, 0) if prev > 0: chg = _pct_val(curr, prev) if abs(chg) > 5: changes.append(f'{name}{chg:+.1f}%') if changes: items.append({'title': '状态变化（vs 昨日）', 'content': ' | '.join(changes[:4])}) return items def _insight_region_structured(region_dist): """Generate structured regional insight with top countries.""" items = [] if not region_dist: items.append({'title': '区域概览', 'content': '暂无区域分布数据。'}) return items sorted_regions = sorted(region_dist.items(), key=lambda x: -x[1]['qty']) top3 = sorted_regions[:3] total = sum(v['qty'] for v in region_dist.values()) top3_pct = sum(v['qty'] for _, v in top3) / total * 100 if total else 0 top_names = [k for k, _ in top3] items.append({'title': '核心市场', 'content': f'{"、".join(top_names)}三大核心市场合计占比{top3_pct:.1f}%，是海外订单的核心增长引擎。'}) # Each region detail for name, data in sorted_regions[:5]: top_c = data.get('top_countries', []) top_c_str = '/'.join([c['country'] for c in top_c[:3]]) if top_c else '' change = data.get('change_pct', 0) if change is None: chg_str = '' elif change > 0: chg_str = f'（+{change:.1f}%）' elif change < 0: chg_str = f'（{change:.1f}%）' else: chg_str = '' content = f'{data["pct"]:.1f}% | {data["qty"]:,}台' if top_c_str: content += f' | {top_c_str}为主力' if change != 0: content += f' {chg_str}' if change < 0: content += ' | 需关注' items.append({'title': name, 'content': content}) return items def _insight_team_structured(team, total_qty=0, per_capita=0, countries_covered=0): """Generate structured team performance insight.""" items = [] if not team: items.append({'title': '团队概览', 'content': '暂无团队绩效数据。'}) return items n_members = len(team) avg = per_capita or _safe_div(sum(v.get('orders', 0) for v in team.values()), n_members) top = max(team.items(), key=lambda x: x[1].get('orders', 0)) overview = f'团队共{n_members}人，' if countries_covered: overview += f'覆盖{countries_covered}国，' overview += f'人均追踪{avg:.0f}单。' items.append({'title': '团队概况', 'content': overview}) # Top performers sorted_team = sorted(team.items(), key=lambda x: -x[1].get('orders', 0)) for name, data in sorted_team[:2]: orders = data.get('orders', 0) qty = data.get('qty', 0) comment = '增长主力' if orders > avg * 1.3 else '稳健跟进' content = f'{name} {orders}单' if qty: content += f'/{qty:,}台' content += f' - {comment}' items.append({'title': '领跑者点评', 'content': content}) # Find laggard low = min(team.items(), key=lambda x: x[1].get('orders', 0)) if low[1].get('orders', 0) < avg * 0.7: items.append({'title': '关注提醒', 'content': f'{low[0]}订单量低于团队均值，建议关注产能提升空间。'}) return items def _insight_stage_structured(stage_analysis, funnel): """Generate structured monthly stage funnel insight.""" items = [] early = stage_analysis.get('early', {}) mid = stage_analysis.get('mid', {}) late = stage_analysis.get('late', {}) a_b = early.get('orders', 0) items.append({ 'title': '前期Pipeline充足', 'content': f'合同拟定中(A) + 已锁定待付订金(B)共{a_b}单，占总量的{early.get("pct", 0):.1f}%，后续转化空间充足。' }) c_d = mid.get('orders', 0) items.append({ 'title': '中期生产推进', 'content': f'已付订金待生产(C) + 已生产待付尾款(D)共{c_d}单，占总量的{mid.get("pct", 0):.1f}%。' }) e_f = late.get('orders', 0) items.append({ 'title': '后期交付待加速', 'content': f'已付尾款待发运(E) + 已发运(F)仅{e_f}单，占总量的{late.get("pct", 0):.1f}%。' }) if early.get('pct', 0) > 40: items.append({ 'title': '⚠ 风险提示', 'content': f'近半数订单仍停留在合同拟定阶段，需关注A→B的转化效率，加速合同确认和订金回收。' }) return items def _insight_top_countries_structured(top_countries_change, total_qty, top_n=6): """Generate structured TOP countries insight.""" items = [] if not top_countries_change: items.append({'title': '国家概览', 'content': '暂无国家分布数据。'}) return items sorted_items = sorted(top_countries_change.items(), key=lambda x: -x[1]['qty']) top_list = sorted_items[:top_n] total_top = sum(v['qty'] for _, v in top_list) pct = total_top / total_qty * 100 if total_qty else 0 items.append({'title': '集中度分析', 'content': f'Top {top_n}目的国合计覆盖{total_top:,}台，占总量的{pct:.1f}%，重点市场集中度高。'}) for i, (country, data) in enumerate(top_list, 1): chg = data.get('change_pct', 0) comment = '' if chg is None: comment = '新增市场，潜力可期' chg_str = '' elif chg > 30: comment = '本周增长最快市场之一' chg_str = f'（{chg:+.1f}%）' elif chg > 10: comment = '持续增长' chg_str = f'（{chg:+.1f}%）' elif chg < -10: comment = '虽有下滑但仍高位' if data['qty'] > total_qty * 0.05 else '需关注' chg_str = f'（{chg:.1f}%）' elif chg < 0: comment = '小幅回落' chg_str = f'（{chg:.1f}%）' else: comment = 'steady增长' if i <= 3 else '新兴市场，潜力可期' chg_str = f'（{chg:+.1f}%）' if chg != 0 else '' items.append({'title': f'{i}. {country} {data["qty"]:,}台{chg_str}', 'content': comment}) return items # ============================================================================== # TEXT / LAYOUT HELPERS # ============================================================================== def _truncate_text(text, max_chars=60): """Truncate text to max_chars, appending '...' if truncated.""" if not text: return text if len(text) > max_chars: return text[:max_chars - 1] + '...' return text def _sentiment_color(text): """Return a light background color based on text sentiment.""" if not text: return None text = str(text) positive_words = ['提升', '增长', '上调', '增加', '高', '好', '大幅', '冲刺', '领跑', '上升', '扩大', '优化', '改善', '突破', '达成'] negative_words = ['下滑', '下降', '减少', '低', '差', '回落', '下滑', '滞后', '堆积', '阻塞', '缺口', '延迟', '超期', '逾期', '风险', '警告'] pos_score = sum(1 for w in positive_words if w in text) neg_score = sum(1 for w in negative_words if w in text) if neg_score > pos_score: return RGBColor(0xFE, 0xE2, 0xE2) # light red ~ #EF444420 if pos_score > neg_score: return RGBColor(0xD1, 0xFA, 0xE5) # light green ~ #10B98120 return None import re def _emoji_for_item(title): """Return an emoji prefix based on title keywords.""" if not title: return '📈' title = str(title) # Skip if title already starts with an emoji if re.match(r'^[\U0001F300-\U0001F9FF\u2600-\u26FF\u2700-\u27BF]', title): return '' if any(k in title for k in ['风险', '警告', '关注', '下滑', '下降', '延迟', '超期', '缺口', '阻塞']): return '⚠️' if any(k in title for k in ['建议', '措施', '行动', '协调', '对接']): return '💡' if any(k in title for k in ['目标', '计划', '冲刺', '展望', '聚焦']): return '🎯' if any(k in title for k in ['增长', '上升', '提升', '峰值', '领跑', '突破', '活跃', '好转']): return '📈' return '💡' def _add_footer_if_missing(slide, footer_text, slide_width=None): """Add a bottom footer bar to slides that don't already have one (Cover, TOC, End).""" if slide_width is None: slide_width = slide.shapes._spTree.getparent().getparent().attrib.get('cx') slide_width = Emu(int(slide_width)) if slide_width else Emu(16256000) # Check if footer already exists has_footer = False for shape in slide.shapes: if shape.has_text_frame and '数据来源' in shape.text_frame.text: has_footer = True break if has_footer: return bar = slide.shapes.add_shape(MSO_SHAPE.RECTANGLE, 0, Emu(8824000), slide_width, Emu(320000)) bar.fill.solid() bar.fill.fore_color.rgb = C_PRIMARY bar.line.fill.background() box = slide.shapes.add_textbox(Emu(762000), Emu(8824000), Emu(14000000), Emu(320000)) p = box.text_frame.paragraphs[0] p.text = footer_text p.font.size = Pt(10) p.font.color.rgb = C_WHITE p.font.name = '微软雅黑' def _ensure_word_wrap_all(slide): """Enable word_wrap on all text frames in a slide.""" for shape in slide.shapes: if shape.has_text_frame: shape.text_frame.word_wrap = True for para in shape.text_frame.paragraphs: for run in para.runs: run.font.name = '微软雅黑' # ============================================================================== # MATH HELPERS # ============================================================================== def _pct_val(curr, prev): if prev and prev != 0: return (curr - prev) / prev * 100 return None def _format_pct(pct, with_sign=True, suffix='%', zero_suffix=''): """Safely format a percentage value. Returns '—' if pct is None.""" if pct is None: return '—' sign = '+' if with_sign and pct >= 0 else '' return f"{sign}{pct:.1f}{suffix}{zero_suffix}" def _pct_str(curr, prev): if prev and prev != 0: pct = round((curr - prev) / prev * 100, 1) sign = '+' if pct >= 0 else '' return f"{sign}{pct}% vs 上期" return "—" def _safe_div(a, b): return round(a / b, 1) if b else 0 # ============================================================================== # DAILY REPORT # ============================================================================== def build_daily_report(data_file: str, date: datetime, output_path: str, department='海外事业部', source='海外订单日报系统'): master_path = get_master_template('daily') prs = Presentation(master_path) content_top = _detect_content_top(prs.slides[1]) df = load_daily(data_file, date) prev_date = date - timedelta(days=1) try: prev_df = load_daily(data_file, prev_date) except Exception: prev_df = None metrics = calc_daily_metrics(df, prev_df) prev_metrics = calc_daily_metrics(prev_df, None) if prev_df is not None else {} date_str = date.strftime('%Y年%m月%d日') period_str = date.strftime('%Y年%m月%d日') # ---- Page 1: Cover ---- slide = _duplicate_slide(prs, prs.slides[0]) _replace_all_placeholders(slide, { '{report_title}': '海外订单数据日报', '{report_type}': '数据日报', '{date}': date_str, '{department}': department, '{period}': period_str, '{gen_time}': datetime.now().strftime('%Y-%m-%d %H:%M'), }) _add_footer_if_missing(slide, f'数据来源：{source} | 1/8') cover_kpis = [ ('在跟订单', metrics['tracking_orders'], '单', _pct_str(metrics['tracking_orders'], metrics.get('prev_tracking_orders', 0))), ('订单总数量', f"{metrics['total_qty']:,}", '台', _pct_str(metrics['total_qty'], metrics.get('prev_total_qty', 0))), ('今日已更新', metrics['updated_orders'], '单', _pct_str(metrics['updated_orders'], metrics.get('prev_updated_orders', 0))), ('支持需求', metrics['support_requests'], '项', '需跨部门协调'), ] for i, (lbl, val, unit, chg) in enumerate(cover_kpis, 1): _replace_placeholder(slide, f'{{kpi{i}_label}}', lbl) _replace_placeholder(slide, f'{{kpi{i}_value}}', str(val)) # ---- Page 2: KPI Overview ---- s2 = _duplicate_slide(prs, prs.slides[1]) _replace_all_placeholders(s2, { '{report_title}': '海外订单数据日报', '{date}': date_str, '{page_title}': '今日核心指标概览', '{source}': source, '{period}': '2/8', '{page_num}': '', }) kpis = [ {'label': '在跟订单总数', 'value': metrics['tracking_orders'], 'unit': '单', 'change': _pct_str(metrics['tracking_orders'], metrics.get('prev_tracking_orders', 0)), 'sub': '日均跟踪'}, {'label': '订单总数量', 'value': f"{metrics['total_qty']:,}", 'unit': '台', 'change': _pct_str(metrics['total_qty'], metrics.get('prev_total_qty', 0)), 'sub': '规模稳定'}, {'label': '今日已更新', 'value': metrics['updated_orders'], 'unit': '单', 'change': _pct_str(metrics['updated_orders'], metrics.get('prev_updated_orders', 0)), 'sub': '团队活跃'}, {'label': '下月预测交付', 'value': metrics['forecast_next'], 'unit': '台', 'change': _pct_str(metrics['forecast_next'], metrics.get('prev_forecast_next', 0)), 'sub': '交付预期'}, {'label': '支持需求总数', 'value': metrics['support_requests'], 'unit': '项', 'change': '需跨部门协调', 'sub': '建议集中处理'}, {'label': '已发运订单', 'value': metrics['shipped_orders'], 'unit': '单', 'change': f'共{metrics.get("shipped_orders", 0) * 8}台 | {metrics["shipped_orders"] - metrics.get("prev_shipped_orders", 0)}单 vs 昨日', 'sub': '交付稳步推进'}, ] _add_kpi_cards(s2, kpis, start_y=Emu(content_top)) # ---- Page 3: 10-Day Trend ---- s3 = _duplicate_slide(prs, prs.slides[1]) trend_dates = [] trend_vals = [] for d_offset in range(-9, 1): d = date + timedelta(days=d_offset) try: tdf = load_daily(data_file, d) trend_dates.append(d.strftime('%m/%d')) trend_vals.append(len(tdf)) except Exception: pass # Generate conclusion title trend_conclusion = '近10天订单趋势' if len(trend_vals) >= 2: if trend_vals[-1] > trend_vals[-2]: trend_conclusion = '近10天订单趋势：订单规模回升' elif trend_vals[-1] < trend_vals[-2]: trend_conclusion = '近10天订单趋势：订单量继续回落' peak = max(trend_vals) if trend_vals[-1] == peak: trend_conclusion = '近10天订单趋势：今日达到峰值' _replace_all_placeholders(s3, { '{report_title}': '海外订单数据日报', '{date}': date_str, '{page_title}': trend_conclusion, '{source}': source, '{period}': '3/8', '{page_num}': '', }) if len(trend_dates) >= 2: add_column_chart(s3, trend_dates, trend_vals, Emu(762000), Emu(content_top), Emu(8890000), Emu(5334000), series_name='订单量', color=C_ACCENT, category_axis_title='日期', value_axis_title='订单数') insight_items = generate_deep_insights('daily', 'trend', metrics, prev_metrics=prev_metrics, trend_dates=trend_dates, trend_vals=trend_vals) _add_structured_insight(s3, insight_items, Emu(9906000), Emu(content_top), Emu(4826000), Emu(5334000)) # ---- Page 4: Status Distribution ---- s4 = _duplicate_slide(prs, prs.slides[1]) status_names = list(metrics['status_dist'].keys()) status_vals = list(metrics['status_dist'].values()) total_status = sum(status_vals) prod_share = metrics.get('production_share', 0) status_title = '订单状态分布' if prod_share > 0: status_title = f'订单状态分布：生产端占比提升至{prod_share:.1f}%' _replace_all_placeholders(s4, { '{report_title}': '海外订单数据日报', '{date}': date_str, '{page_title}': status_title, '{source}': source, '{period}': '4/8', '{page_num}': '', }) if status_names and total_status > 0: # Left donut chart: 55% width chart_w = Emu(int(prs.slide_width) * 0.55) add_doughnut_chart(s4, status_names, status_vals, Emu(762000), Emu(content_top), chart_w, Emu(5334000), show_legend=True, show_data_labels=True, show_percent=True, ring_ratio=0.6) # Right side: status change + deep insights (no table to save space for dense text) text_left = Emu(int(prs.slide_width) * 0.62) text_w = Emu(int(prs.slide_width) * 0.36) prev_status = prev_metrics.get('status_dist', {}) # Status change text: "合同拟定中 -30.8% ↓ | 已付订金待生产 +55.6% ↑" changes = [] for name, curr in metrics['status_dist'].items(): prev = prev_status.get(name, 0) if prev > 0: chg = _pct_val(curr, prev) if chg is not None: arrow = '↑' if chg >= 0 else '↓' changes.append(f'{name} {_format_pct(chg)}{arrow}') if changes: change_text = ' | '.join(changes[:4]) _add_text_block(s4, '状态变化（vs 昨日）', change_text, text_left, Emu(content_top), text_w, Emu(609600), title_size=Pt(12), body_size=Pt(10)) # Deep insight fills remaining right-side space insight_items = generate_deep_insights('daily', 'status', metrics, prev_status_dist=prev_status) insight_top = Emu(int(content_top) + 685800) insight_height = Emu(5334000 - 685800) _add_structured_insight(s4, insight_items, text_left, insight_top, text_w, insight_height) # ---- Page 5: Owner Distribution ---- s5 = _duplicate_slide(prs, prs.slides[1]) owner_names = list(metrics['owner_dist'].keys())[:8] owner_vals = list(metrics['owner_dist'].values())[:8] top_owner = owner_names[0] if owner_names else '' second_owner = owner_names[1] if len(owner_names) > 1 else '' owner_title = '负责人订单分布' if top_owner and second_owner: owner_title = f'负责人订单分布：{top_owner}、{second_owner}领跑' _replace_all_placeholders(s5, { '{report_title}': '海外订单数据日报', '{date}': date_str, '{page_title}': owner_title, '{source}': source, '{period}': '5/8', '{page_num}': '', }) if owner_names: chart_w = Emu(int(prs.slide_width) * 0.55) text_left = Emu(int(prs.slide_width) * 0.62) text_w = Emu(int(prs.slide_width) * 0.36) add_horizontal_bar_chart(s5, owner_names, owner_vals, Emu(762000), Emu(content_top), chart_w, Emu(5334000), series_name='订单笔数', color=C_ACCENT, reverse_order=True, value_axis_title='订单笔数') # Deep insight: owner distribution analysis prev_owner_dist = prev_metrics.get('owner_dist', {}) if prev_metrics else {} insight_items = generate_deep_insights('daily', 'owner', metrics, prev_owner_dist=prev_owner_dist) _add_structured_insight(s5, insight_items, text_left, Emu(content_top), text_w, Emu(5334000)) # ---- Page 6: Country TOP8 ---- s6 = _duplicate_slide(prs, prs.slides[1]) countries = list(metrics['country_top8'].keys())[:8] country_vals = list(metrics['country_top8'].values())[:8] top_country = countries[0] if countries else '' country_title = '目的国家TOP8' if top_country: country_title = f'目的国家TOP8：{top_country}订单量领先' _replace_all_placeholders(s6, { '{report_title}': '海外订单数据日报', '{date}': date_str, '{page_title}': country_title, '{source}': source, '{period}': '6/8', '{page_num}': '', }) if countries: chart_w = Emu(int(prs.slide_width) * 0.55) text_left = Emu(int(prs.slide_width) * 0.62) text_w = Emu(int(prs.slide_width) * 0.36) add_horizontal_bar_chart(s6, countries, country_vals, Emu(762000), Emu(content_top), chart_w, Emu(5334000), series_name='订单量(台)', color=C_ACCENT, reverse_order=True, value_axis_title='订单量（台）') # Deep insight: country top8 analysis insight_items = generate_deep_insights('daily', 'country', metrics) _add_structured_insight(s6, insight_items, text_left, Emu(content_top), text_w, Emu(5334000)) # ---- Page 7: Support Analysis ---- s7 = _duplicate_slide(prs, prs.slides[1]) alerts = metrics['alerts'] alert_title = '异常告警' if alerts: severe = [a for a in alerts if a.get('level') == '严重'] if severe: alert_title = f'异常告警：{severe[0]["title"]}' else: alert_title = f'异常告警：{alerts[0]["title"]}' _replace_all_placeholders(s7, { '{report_title}': '海外订单数据日报', '{date}': date_str, '{page_title}': alert_title, '{source}': source, '{period}': '7/8', '{page_num}': '', }) # Unified left-chart + right-insight layout sc = metrics.get('support_categories', {}) chart_w = Emu(int(prs.slide_width) * 0.55) text_left = Emu(int(prs.slide_width) * 0.62) text_w = Emu(int(prs.slide_width) * 0.36) if sc: sc_names = list(sc.keys()) sc_vals = list(sc.values()) add_doughnut_chart(s7, sc_names, sc_vals, Emu(762000), Emu(content_top), chart_w, Emu(5334000), show_legend=True, show_data_labels=True, show_percent=True, ring_ratio=0.6) # Deep insight: alerts & support analysis insight_items = generate_deep_insights('daily', 'alert', metrics) _add_structured_insight(s7, insight_items, text_left, Emu(content_top), text_w, Emu(5334000)) # ---- Page 8: Key Points ---- s8 = _duplicate_slide(prs, prs.slides[1]) _replace_all_placeholders(s8, { '{report_title}': '海外订单数据日报', '{date}': date_str, '{page_title}': '明日工作重点', '{source}': source, '{period}': '8/8', '{page_num}': '', }) # Left chart: overdue orders horizontal bar (or support categories fallback) overdue = metrics.get('overdue_orders', []) chart_w = Emu(int(prs.slide_width) * 0.55) text_left = Emu(int(prs.slide_width) * 0.62) text_w = Emu(int(prs.slide_width) * 0.36) if overdue: o_countries = [o['country'] for o in overdue[:8]] o_days = [o['days'] for o in overdue[:8]] add_horizontal_bar_chart(s8, o_countries, o_days, Emu(762000), Emu(content_top), chart_w, Emu(4826000), series_name='超期天数', color=C_ORANGE, reverse_order=True, value_axis_title='天数') elif metrics.get('support_categories'): sc = metrics['support_categories'] add_horizontal_bar_chart(s8, list(sc.keys()), list(sc.values()), Emu(762000), Emu(content_top), chart_w, Emu(4826000), series_name='需求数', color=C_ACCENT, reverse_order=True, value_axis_title='数量') # Deep insight: tomorrow's action items action_items = generate_deep_insights('daily', 'action', metrics) _add_structured_insight(s8, action_items, text_left, Emu(content_top), text_w, Emu(5334000)) for slide in prs.slides: _ensure_word_wrap_all(slide) _delete_template_slides(prs) prs.save(output_path) print(f"Daily report saved: {output_path}") # ============================================================================== # WEEKLY REPORT # ============================================================================== def build_weekly_report(data_file: str, year: int, week: int, output_path: str, department='海外事业部', source='海外订单日报系统'): master_path = get_master_template('weekly') prs = Presentation(master_path) content_top = _detect_content_top(prs.slides[1]) df, prev_df = load_weekly(data_file, year, week) metrics = calc_weekly_metrics(df, prev_df) period_str = f"{year}年第{week}周" date_range_str = f"{df['_data_date'].min().strftime('%m/%d')} - {df['_data_date'].max().strftime('%m/%d')}" # Page 1: Cover slide = _duplicate_slide(prs, prs.slides[0]) _replace_all_placeholders(slide, { '{report_title}': '海外订单数据周报', '{report_type}': 'Weekly Overseas Order Data Report', '{date}': period_str, '{department}': department, '{period}': date_range_str, '{gen_time}': datetime.now().strftime('%Y-%m-%d %H:%M'), }) _add_footer_if_missing(slide, f'数据来源：{source} | 1/9') # Fix {kpi4_label} {kpi4_value} with actual metric (下月预测交付) cover_kpis = [ ('跟踪订单笔数', f"{metrics['tracking_orders']:,}", '笔', _pct_str(metrics['tracking_orders'], metrics.get('prev_tracking_orders', 0))), ('订单总数量', f"{metrics['total_qty']:,}", '台', _pct_str(metrics['total_qty'], metrics.get('prev_total_qty', 0))), ('覆盖目的国', f"{metrics['countries']}", '个', '全球布局持续深化'), ('下月预测交付', f"{metrics['forecast_next']:,}", '台', _pct_str(metrics['forecast_next'], metrics.get('prev_forecast_next', 0))), ] for i, (lbl, val, unit, chg) in enumerate(cover_kpis, 1): _replace_placeholder(slide, f'{{kpi{i}_label}}', lbl) _replace_placeholder(slide, f'{{kpi{i}_value}}', str(val)) nav_labels = ['周汇总', '趋势图', '环比分析', '区域排行', '问题建议', '下周计划'] # Page 2: Weekly Summary (KPI cards) s2 = _duplicate_slide(prs, prs.slides[1]) t_chg = _pct_val(metrics['tracking_orders'], metrics.get('prev_tracking_orders', 0)) q_chg = _pct_val(metrics['total_qty'], metrics.get('prev_total_qty', 0)) t_chg_str = _format_pct(t_chg) q_chg_str = _format_pct(q_chg) _replace_all_placeholders(s2, { '{report_title}': '海外订单数据周报', '{date}': period_str, '{page_title}': f"周汇总：跟踪订单环比{t_chg_str}，订单总量增长{q_chg_str}", '{source}': source, '{period}': '2/9', '{page_num}': '', }) _add_nav_tabs(s2, nav_labels, active_index=0, slide_width=prs.slide_width) kpis = [ {'label': '跟踪订单笔数', 'value': f"{metrics['tracking_orders']:,}", 'unit': '笔', 'change': f'{t_chg_str} vs W1 ({metrics.get("prev_tracking_orders", 0)}笔)' if t_chg is not None else ('新增' if metrics['tracking_orders'] > 0 else '—'), 'sub': f'日均{metrics["avg_daily_orders"]:.0f}笔'}, {'label': '订单总数量', 'value': f"{metrics['total_qty']:,}", 'unit': '台', 'change': f'{q_chg_str} vs W1 ({metrics.get("prev_total_qty", 0):,})' if q_chg is not None else ('新增' if metrics['total_qty'] > 0 else '—'), 'sub': f'日均{metrics["avg_daily_qty"]:.0f}台'}, {'label': '已发运订单', 'value': metrics['shipped_orders'], 'unit': '笔', 'change': _pct_str(metrics['shipped_orders'], metrics.get('prev_shipped_orders', 0)), 'sub': '环比大幅提升'}, {'label': '覆盖目的国', 'value': metrics['countries'], 'unit': '个', 'change': '持平', 'sub': '全球布局持续深化'}, {'label': '进度更新订单', 'value': metrics['updated_orders'], 'unit': '笔', 'change': _pct_str(metrics['updated_orders'], metrics.get('prev_updated_orders', 0)), 'sub': '推进效率提升'}, {'label': '下月预测交付', 'value': f"{metrics['forecast_next']:,}", 'unit': '台', 'change': _pct_str(metrics['forecast_next'], metrics.get('prev_forecast_next', 0)), 'sub': '交付预期大幅上调'}, ] _add_kpi_cards(s2, kpis, start_y=Emu(content_top)) # Page 3: 7-Day Trend s3 = _duplicate_slide(prs, prs.slides[1]) trend = metrics.get('daily_trend', {}) trend_title = '7日趋势：订单总量稳步上升' if trend: dates = list(trend.keys()) vals = list(trend.values()) if vals: peak = max(vals) peak_date = dates[vals.index(peak)] if vals[-1] >= vals[0]: trend_title = f'7日趋势：订单总量稳步上升，峰值出现在{peak_date}' else: trend_title = f'7日趋势：订单量有所波动，峰值出现在{peak_date}' _replace_all_placeholders(s3, { '{report_title}': '海外订单数据周报', '{date}': period_str, '{page_title}': trend_title, '{source}': source, '{period}': '3/9', '{page_num}': '', }) _add_nav_tabs(s3, nav_labels, active_index=1, slide_width=prs.slide_width) trend = metrics.get('daily_trend', {}) if trend: dates = list(trend.keys()) vals = list(trend.values()) chart_w = Emu(int(prs.slide_width) * 0.55) text_left = Emu(int(prs.slide_width) * 0.62) text_w = Emu(int(prs.slide_width) * 0.36) add_line_chart(s3, dates, vals, Emu(762000), Emu(content_top), chart_w, Emu(5334000), series_name='订单量', color=C_ACCENT, category_axis_title='日期（MM/DD）', value_axis_title='订单数') peak = max(vals) if vals else 0 peak_date = dates[vals.index(peak)] if vals else '' avg = sum(vals) // len(vals) if vals else 0 prev_avg = metrics.get('prev_avg_daily_orders', 0) above_days = metrics.get('days_above_prev_avg', 0) total_days = len(vals) # Deep insight: weekly trend analysis insight_items = generate_deep_insights('weekly', 'weekly_trend', metrics, trend_dates=dates, trend_vals=vals) _add_structured_insight(s3, insight_items, text_left, Emu(content_top), text_w, Emu(5334000)) # Page 4: WoW Analysis s4 = _duplicate_slide(prs, prs.slides[1]) _replace_all_placeholders(s4, { '{report_title}': '海外订单数据周报', '{date}': period_str, '{page_title}': '环比分析：各阶段全面增长，已发运环节增幅最大', '{source}': source, '{period}': '4/9', '{page_num}': '', }) _add_nav_tabs(s4, nav_labels, active_index=2, slide_width=prs.slide_width) sw_data = metrics.get('status_wow', {}) if sw_data: names = list(sw_data.keys()) current_vals = [v['current'] for v in sw_data.values()] previous_vals = [v['previous'] for v in sw_data.values()] # Replace None with 0 for chart data to avoid crashes changes = [v['change_pct'] if v['change_pct'] is not None else 0 for v in sw_data.values()] chart_w = Emu(int(prs.slide_width) * 0.55) text_left = Emu(int(prs.slide_width) * 0.62) text_w = Emu(int(prs.slide_width) * 0.36) # Grouped bar chart: 本期 vs 上期 add_grouped_bar_chart(s4, names, [('本期', current_vals), ('上期', previous_vals)], Emu(762000), Emu(content_top), chart_w, Emu(4826000), colors=[C_ACCENT, C_SECONDARY], show_legend=True, show_data_labels=True, category_axis_title='订单状态', value_axis_title='订单数') # WoW增幅 labels below chart wow_label_items = [] for k, v in sw_data.items(): pct_str = _format_pct(v['change_pct']) arrow = '↑' if v['change_pct'] is not None and v['change_pct'] >= 0 else '↓' if v['change_pct'] is not None else '' wow_label_items.append(f'{k} {pct_str}{arrow}') _add_text_block(s4, '环比增幅', ' | '.join(wow_label_items), Emu(762000), Emu(6604000), chart_w, Emu(609600), title_size=Pt(11), body_size=Pt(10)) # Deep insight: WoW analysis insight_items = generate_deep_insights('weekly', 'weekly_wow', metrics) _add_structured_insight(s4, insight_items, text_left, Emu(content_top), text_w, Emu(4826000)) # Page 5: Region Distribution s5 = _duplicate_slide(prs, prs.slides[1]) _replace_all_placeholders(s5, { '{report_title}': '海外订单数据周报', '{date}': period_str, '{page_title}': '区域分布：中东增速领跑，欧洲为唯一下滑区域', '{source}': source, '{period}': '5/9', '{page_num}': '', }) _add_nav_tabs(s5, nav_labels, active_index=3, slide_width=prs.slide_width) rdist = metrics.get('region_dist', {}) if rdist: regions = list(rdist.keys()) qtys = [v['qty'] for v in rdist.values()] chart_w = Emu(int(prs.slide_width) * 0.55) text_left = Emu(int(prs.slide_width) * 0.62) text_w = Emu(int(prs.slide_width) * 0.36) add_doughnut_chart(s5, regions, qtys, Emu(762000), Emu(content_top), chart_w, Emu(5334000), show_legend=True, show_data_labels=True, show_percent=True, ring_ratio=0.6) # Deep insight: regional analysis insight_items = generate_deep_insights('weekly', 'weekly_region', metrics) _add_structured_insight(s5, insight_items, text_left, Emu(content_top), text_w, Emu(5334000)) # Page 6: TOP Countries s6 = _duplicate_slide(prs, prs.slides[1]) _replace_all_placeholders(s6, { '{report_title}': '海外订单数据周报', '{date}': period_str, '{page_title}': 'TOP国家排行：科威特344台居首，TOP15贡献70%+总量', '{source}': source, '{period}': '6/9', '{page_num}': '', }) _add_nav_tabs(s6, nav_labels, active_index=3, slide_width=prs.slide_width) topc_change = metrics.get('top_countries_change', {}) if topc_change: countries = list(topc_change.keys())[:10] vals = [v['qty'] for v in list(topc_change.values())[:10]] chart_w = Emu(int(prs.slide_width) * 0.55) text_left = Emu(int(prs.slide_width) * 0.62) text_w = Emu(int(prs.slide_width) * 0.36) add_horizontal_bar_chart(s6, countries, vals, Emu(762000), Emu(content_top), chart_w, Emu(4826000), series_name='订单量(台)', color=C_ACCENT, reverse_order=True, value_axis_title='订单量（台）') # Deep insight: top countries analysis insight_items = generate_deep_insights('weekly', 'weekly_country', metrics) _add_structured_insight(s6, insight_items, text_left, Emu(content_top), text_w, Emu(4826000)) # Page 7: Team Tracking s7 = _duplicate_slide(prs, prs.slides[1]) team = metrics.get('team', {}) n_members = len(team.get('owners', {})) if team else 0 n_growers = sum(1 for v in metrics.get('team_wow', {}).values() if v.get('change', 0) > 0) team_title = f'团队追踪：{n_members}人团队全面覆盖，{n_growers}人实现增长' _replace_all_placeholders(s7, { '{report_title}': '海外订单数据周报', '{date}': period_str, '{page_title}': team_title, '{source}': source, '{period}': '7/9', '{page_num}': '', }) _add_nav_tabs(s7, nav_labels, active_index=4, slide_width=prs.slide_width) team = metrics.get('team', {}) owners = team.get('owners', {}) if owners: names = list(owners.keys()) vals = list(owners.values()) chart_w = Emu(int(prs.slide_width) * 0.55) text_left = Emu(int(prs.slide_width) * 0.62) text_w = Emu(int(prs.slide_width) * 0.36) add_horizontal_bar_chart(s7, names, vals, Emu(762000), Emu(content_top), chart_w, Emu(4826000), series_name='订单笔数', color=C_ACCENT, reverse_order=True, value_axis_title='订单笔数') # Deep insight: team tracking insight_items = generate_deep_insights('weekly', 'weekly_team', metrics) _add_structured_insight(s7, insight_items, text_left, Emu(content_top), text_w, Emu(4826000)) # Page 8: Issues s8 = _duplicate_slide(prs, prs.slides[1]) _replace_all_placeholders(s8, { '{report_title}': '海外订单数据周报', '{date}': period_str, '{page_title}': '问题识别：系统数据匹配问题为本周首要障碍', '{source}': source, '{period}': '8/9', '{page_num}': '', }) _add_nav_tabs(s8, nav_labels, active_index=4, slide_width=prs.slide_width) # Left chart: support request categories sc = metrics.get('support_categories', {}) chart_w = Emu(int(prs.slide_width) * 0.55) text_left = Emu(int(prs.slide_width) * 0.62) text_w = Emu(int(prs.slide_width) * 0.36) if sc: add_horizontal_bar_chart(s8, list(sc.keys()), list(sc.values()), Emu(762000), Emu(content_top), chart_w, Emu(4826000), series_name='需求数', color=C_ORANGE, reverse_order=True, value_axis_title='数量') # Right: deep insight insight_items = generate_deep_insights('weekly', 'weekly_issue', metrics) _add_structured_insight(s8, insight_items, text_left, Emu(content_top), text_w, Emu(5334000)) # Page 9: Next Week Plan s9 = _duplicate_slide(prs, prs.slides[1]) _replace_all_placeholders(s9, { '{report_title}': '海外订单数据周报', '{date}': period_str, '{page_title}': '下周计划：聚焦发运交付，冲刺交付目标', '{source}': source, '{period}': '9/9', '{page_num}': '', }) _add_nav_tabs(s9, nav_labels, active_index=5, slide_width=prs.slide_width) # Left chart: goals as column chart goals = metrics.get('next_week_goals', []) chart_w = Emu(int(prs.slide_width) * 0.55) text_left = Emu(int(prs.slide_width) * 0.62) text_w = Emu(int(prs.slide_width) * 0.36) if goals: goal_names = [g['title'].split('：')[0] for g in goals[:4]] goal_nums = [g.get('number', 0) for g in goals[:4]] add_column_chart(s9, goal_names, goal_nums, Emu(762000), Emu(content_top), chart_w, Emu(4826000), series_name='目标数量', color=C_ACCENT, category_axis_title='目标', value_axis_title='数量') # Deep insight: next week plan insight_items = generate_deep_insights('weekly', 'weekly_plan', metrics) _add_structured_insight(s9, insight_items, text_left, Emu(content_top), text_w, Emu(5334000)) for slide in prs.slides: _ensure_word_wrap_all(slide) _delete_template_slides(prs) prs.save(output_path) print(f"Weekly report saved: {output_path}") # ============================================================================== # MONTHLY REPORT # ============================================================================== def build_monthly_report(data_file: str, year: int, month: int, output_path: str, department='海外事业部', source='海外订单日报系统'): master_path = get_master_template('monthly') prs = Presentation(master_path) content_top = _detect_content_top(prs.slides[1]) df, prev_df, yoy_df = load_monthly(data_file, year, month) metrics = calc_monthly_metrics(df, prev_df, yoy_df) period_str = f"{year}年{month}月" # Page 1: Cover slide = _duplicate_slide(prs, prs.slides[0]) _replace_all_placeholders(slide, { '{report_title}': '海外订单月度数据报告', '{report_type}': 'Monthly Overseas Order Data Report', '{date}': period_str, '{department}': department, '{period}': period_str, '{gen_time}': datetime.now().strftime('%Y-%m-%d %H:%M'), }) _add_footer_if_missing(slide, f'数据来源：{source} | 1/11') cover_kpis = [ ('合同总数', f"{metrics['total_contracts']:,}"), ('车辆总数', f"{metrics['total_qty']:,}"), ('目的国家', f"{metrics['countries']}+"), ('负责团队', '9人'), ] for i, (lbl, val) in enumerate(cover_kpis, 1): _replace_placeholder(slide, f'{{kpi{i}_label}}', lbl) _replace_placeholder(slide, f'{{kpi{i}_value}}', val) # Page 2: TOC s_toc = _duplicate_slide(prs, prs.slides[2]) _add_footer_if_missing(s_toc, f'数据来源：{source} | 2/11') _replace_all_placeholders(s_toc, { '{chapter1_title}': '月度总览', '{chapter1_desc}': '核心KPI一览：合同总数、车辆规模、新签与发运表现', '{chapter2_title}': '订单状态分析', '{chapter2_desc}': '订单阶段漏斗：从合同拟定到发运的全流程追踪', '{chapter3_title}': '区域与趋势', '{chapter3_desc}': '区域分布、国家排名与30日追踪趋势', '{chapter4_title}': '团队与展望', '{chapter4_desc}': '团队绩效、支持需求与下月工作规划', }) nav_labels = ['月度总览', '订单状态', '区域趋势', '团队展望'] # Page 3: Monthly Overview s3 = _duplicate_slide(prs, prs.slides[1]) _replace_all_placeholders(s3, { '{report_title}': '海外订单月度数据报告', '{date}': period_str, '{page_title}': f"{month}月核心指标：累计追踪{metrics['total_contracts']:,}单，覆盖{metrics['total_qty']:,}台车辆", '{source}': source, '{period}': '3/11', '{page_num}': '', }) _add_nav_tabs(s3, nav_labels, active_index=0, slide_width=prs.slide_width) kpis = [ {'label': '合同总数', 'value': f"{metrics['total_contracts']:,}", 'unit': '单', 'change': f'日均{metrics["avg_daily_orders"]:.0f}单', 'sub': '覆盖全月'}, {'label': '车辆总数', 'value': f"{metrics['total_qty']:,}", 'unit': '台', 'change': f"{metrics['shipped_qty']:,}台已发运", 'sub': '交付持续推进'}, {'label': f'{month}月新签', 'value': metrics['new_contracts'], 'unit': '单', 'change': f"{metrics['new_qty']:,}台", 'sub': '新签势头良好'}, {'label': '已发运', 'value': metrics['shipped_orders'], 'unit': '单', 'change': f"{metrics['shipped_qty']:,}台", 'sub': '交付稳步推进'}, {'label': '目的国', 'value': f"{metrics['countries']}+", 'unit': '个', 'change': '全球市场布局', 'sub': '持续深化'}, {'label': '待处理需求', 'value': metrics['support_count'], 'unit': '单', 'change': f"{metrics['support_pct']}%订单涉及", 'sub': '需跨部门协调'}, ] _add_kpi_cards(s3, kpis, start_y=Emu(content_top)) # Monthly overview: KPI cards only, no bottom insight text to avoid overlap with cards # Page 4: Status Funnel s4 = _duplicate_slide(prs, prs.slides[1]) _replace_all_placeholders(s4, { '{report_title}': '海外订单月度数据报告', '{date}': period_str, '{page_title}': '订单阶段漏斗：合同拟定与生产中订单占主导地位', '{source}': source, '{period}': '4/11', '{page_num}': '', }) _add_nav_tabs(s4, nav_labels, active_index=1, slide_width=prs.slide_width) funnel = metrics.get('status_funnel', {}) if funnel: names = list(funnel.keys()) orders = [v['orders'] for v in funnel.values()] chart_w = Emu(int(prs.slide_width) * 0.55) text_left = Emu(int(prs.slide_width) * 0.62) text_w = Emu(int(prs.slide_width) * 0.36) add_funnel_chart(s4, names, orders, Emu(762000), Emu(content_top), chart_w, Emu(5334000), show_data_labels=True, show_percent=True) # Deep insight: monthly funnel insight_items = generate_deep_insights('monthly', 'monthly_funnel', metrics) _add_structured_insight(s4, insight_items, text_left, Emu(content_top), text_w, Emu(5334000)) # Page 5: Region Distribution s5 = _duplicate_slide(prs, prs.slides[1]) _replace_all_placeholders(s5, { '{report_title}': '海外订单月度数据报告', '{date}': period_str, '{page_title}': '区域分布：拉美、东南亚、非洲三大市场并驾齐驱', '{source}': source, '{period}': '5/11', '{page_num}': '', }) _add_nav_tabs(s5, nav_labels, active_index=2, slide_width=prs.slide_width) rdist = metrics.get('region_dist', {}) if rdist: regions = list(rdist.keys()) qtys = [v['qty'] for v in rdist.values()] chart_w = Emu(int(prs.slide_width) * 0.55) text_left = Emu(int(prs.slide_width) * 0.62) text_w = Emu(int(prs.slide_width) * 0.36) add_doughnut_chart(s5, regions, qtys, Emu(762000), Emu(content_top), chart_w, Emu(5334000), show_legend=True, show_data_labels=True, show_percent=True, ring_ratio=0.6) # Deep insight: monthly region insight_items = generate_deep_insights('monthly', 'monthly_region', metrics) _add_structured_insight(s5, insight_items, text_left, Emu(content_top), text_w, Emu(5334000)) # Page 6: TOP10 Countries s6 = _duplicate_slide(prs, prs.slides[1]) top10 = metrics.get('top_countries', {}) top_country = list(top10.keys())[0] if top10 else '' top_qty = list(top10.values())[0]['qty'] if top10 else 0 top10_title = f'Top 10目的国：{top_country}{top_qty:,}台领跑' if top_country else 'Top 10目的国：重点市场领跑' _replace_all_placeholders(s6, { '{report_title}': '海外订单月度数据报告', '{date}': period_str, '{page_title}': top10_title, '{source}': source, '{period}': '6/11', '{page_num}': '', }) _add_nav_tabs(s6, nav_labels, active_index=2, slide_width=prs.slide_width) top10 = metrics.get('top_countries', {}) if top10: countries = list(top10.keys()) qtys = [v['qty'] for v in top10.values()] chart_w = Emu(int(prs.slide_width) * 0.55) text_left = Emu(int(prs.slide_width) * 0.62) text_w = Emu(int(prs.slide_width) * 0.36) add_horizontal_bar_chart(s6, countries, qtys, Emu(762000), Emu(content_top), chart_w, Emu(4826000), series_name='订单量(台)', color=C_ACCENT, reverse_order=True, value_axis_title='订单量（台）') # Deep insight: monthly top countries insight_items = generate_deep_insights('monthly', 'monthly_country', metrics) _add_structured_insight(s6, insight_items, text_left, Emu(content_top), text_w, Emu(4826000)) # Page 7: 30-Day Trend s7 = _duplicate_slide(prs, prs.slides[1]) _replace_all_placeholders(s7, { '{report_title}': '海外订单月度数据报告', '{date}': period_str, '{page_title}': '30日追踪趋势：下旬订单活跃度显著提升', '{source}': source, '{period}': '7/11', '{page_num}': '', }) _add_nav_tabs(s7, nav_labels, active_index=2, slide_width=prs.slide_width) trend = metrics.get('daily_trend', {}) chart_w = Emu(int(prs.slide_width) * 0.55) text_left = Emu(int(prs.slide_width) * 0.62) text_w = Emu(int(prs.slide_width) * 0.36) if trend: dates = list(trend.keys()) vals = list(trend.values()) add_line_chart(s7, dates, vals, Emu(762000), Emu(content_top), chart_w, Emu(5334000), series_name='订单量', color=C_ACCENT, category_axis_title='日期（MM/DD）', value_axis_title='订单数') tbp = metrics.get('trend_by_period', {}) late_change = tbp.get('late_change_pct', 0) late_change_str = _format_pct(late_change, with_sign=True) if late_change is not None else '—' # Deep insight: monthly trend insight_items = generate_deep_insights('monthly', 'monthly_trend', metrics) _add_structured_insight(s7, insight_items, text_left, Emu(content_top), text_w, Emu(5334000)) # Page 8: Team Performance s8 = _duplicate_slide(prs, prs.slides[1]) _replace_all_placeholders(s8, { '{report_title}': '海外订单月度数据报告', '{date}': period_str, '{page_title}': '团队绩效：9位负责人均匀分布，多人领跑', '{source}': source, '{period}': '8/11', '{page_num}': '', }) _add_nav_tabs(s8, nav_labels, active_index=3, slide_width=prs.slide_width) team = metrics.get('team', {}) if team: names = list(team.keys()) orders = [v['orders'] for v in team.values()] qtys = [v['qty'] for v in team.values()] chart_w = Emu(int(prs.slide_width) * 0.55) text_left = Emu(int(prs.slide_width) * 0.62) text_w = Emu(int(prs.slide_width) * 0.36) # Horizontal bar chart for orders + secondary series for qty add_grouped_bar_chart(s8, names, [('订单数', orders), ('车辆数', qtys)], Emu(762000), Emu(content_top), chart_w, Emu(4826000), colors=[C_ACCENT, C_ORANGE], show_legend=True, show_data_labels=True, category_axis_title='负责人', value_axis_title='数量') # Deep insight: monthly team insight_items = generate_deep_insights('monthly', 'monthly_team', metrics) _add_structured_insight(s8, insight_items, text_left, Emu(content_top), text_w, Emu(4826000)) # Page 9: Support Analysis s9 = _duplicate_slide(prs, prs.slides[1]) _replace_all_placeholders(s9, { '{report_title}': '海外订单月度数据报告', '{date}': period_str, '{page_title}': '支持需求分析：财务、售后、法务为三大核心诉求', '{source}': source, '{period}': '9/11', '{page_num}': '', }) _add_nav_tabs(s9, nav_labels, active_index=3, slide_width=prs.slide_width) sc = metrics.get('support_categories', {}) if sc: cats = list(sc.keys()) vals = list(sc.values()) add_horizontal_bar_chart(s9, cats, vals, Emu(762000), Emu(content_top), Emu(8636000), Emu(5334000), series_name='需求数', color=C_ACCENT, reverse_order=True, value_axis_title='需求数') top_cat = max(sc.items(), key=lambda x: x[1]) # Deep insight: monthly support insight_items = generate_deep_insights('monthly', 'monthly_support', metrics) _add_structured_insight(s9, insight_items, Emu(9779000), Emu(content_top), Emu(5715000), Emu(5334000)) # Page 10: Next Month Plan s10 = _duplicate_slide(prs, prs.slides[1]) _replace_all_placeholders(s10, { '{report_title}': '海外订单月度数据报告', '{date}': period_str, '{page_title}': f'{month+1 if month < 12 else 1}月展望：预测交付{metrics["forecast_next"]}台，重点关注交付转化', '{source}': source, '{period}': '10/11', '{page_num}': '', }) _add_nav_tabs(s10, nav_labels, active_index=3, slide_width=prs.slide_width) # Left chart: next month goals as column chart goals = metrics.get('next_month_goals', []) chart_w = Emu(int(prs.slide_width) * 0.55) text_left = Emu(int(prs.slide_width) * 0.62) text_w = Emu(int(prs.slide_width) * 0.36) if goals: goal_names = [g['title'].split('：')[0] for g in goals[:5]] goal_nums = [g.get('number', 0) for g in goals[:5]] add_column_chart(s10, goal_names, goal_nums, Emu(762000), Emu(content_top), chart_w, Emu(4826000), series_name='目标数量', color=C_ACCENT, category_axis_title='目标', value_axis_title='数量') # Deep insight: monthly plan insight_items = generate_deep_insights('monthly', 'monthly_plan', metrics) _add_structured_insight(s10, insight_items, text_left, Emu(content_top), text_w, Emu(5334000)) # Page 11: End s_end = _duplicate_slide(prs, prs.slides[3]) _add_footer_if_missing(s_end, f'数据来源：{source} | 11/11') _replace_all_placeholders(s_end, { '{report_title}': '海外订单月度数据报告', '{date}': period_str, '{department}': department, }) end_kpis = [ ('合同总数', f"{metrics['total_contracts']:,}"), ('车辆总数', f"{metrics['total_qty']:,}"), ('目的国家', f"{metrics['countries']}+"), ('团队', '9人'), ] for i, (lbl, val) in enumerate(end_kpis, 1): _replace_placeholder(s_end, f'{{kpi{i}_label}}', lbl) _replace_placeholder(s_end, f'{{kpi{i}_value}}', val) for slide in prs.slides: _ensure_word_wrap_all(slide) _delete_template_slides(prs) prs.save(output_path) print(f"Monthly report saved: {output_path}") # ============================================================================== # CLI # ============================================================================== if __name__ == '__main__': import sys if len(sys.argv) >= 4: cmd = sys.argv[1] data_file = sys.argv[2] output = sys.argv[3] if cmd == 'daily': d = datetime.strptime(sys.argv[4], '%Y-%m-%d') build_daily_report(data_file, d, output) elif cmd == 'weekly': build_weekly_report(data_file, int(sys.argv[4]), int(sys.argv[5]), output) elif cmd == 'monthly': build_monthly_report(data_file, int(sys.argv[4]), int(sys.argv[5]), output)