""" 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 import re as re_module 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, load_generic_excel, ) from metrics_calculator import ( calc_daily_metrics, calc_weekly_metrics, calc_monthly_metrics, generate_deep_insights, calc_generic_metrics, calc_generic_trend, calc_generic_distribution, calc_generic_ranking, generate_generic_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 ) from page_layouts import ( get_kpi_grid, get_chart_left_zone, get_insight_right_zone, get_full_width_zone, get_two_column_zones, ) from quality_inspector import QualityInspector from theme_manager import theme_to_rgb_colors, get_theme from report_config import ( ReportConfig, PageDef, MetricDef, PeriodType, ChartType, validate_six_confirmations, ) from quality_rules import SLIDE_WIDTH, SLIDE_HEIGHT, CONTENT_LEFT, CONTENT_TOP_BASE, FOOTER_TOP # 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) def _remove_shape(shape): """Remove a python-pptx shape from its parent tree.""" el = shape.element el.getparent().remove(el) def _remove_empty_cover_kpi_placeholders(slide): """ Remove template KPI cards when generic cover data does not provide values. This prevents empty rounded rectangles from staying on the cover. """ kpi_pattern = re_module.compile(r'\{kpi\d+_(label|value)\}') placeholder_shapes = [ shape for shape in slide.shapes if shape.has_text_frame and kpi_pattern.search(shape.text_frame.text or '') ] if not placeholder_shapes: return x_min = min(int(shape.left) for shape in placeholder_shapes) x_max = max(int(shape.left) + int(shape.width) for shape in placeholder_shapes) y_min = min(int(shape.top) for shape in placeholder_shapes) y_max = max(int(shape.top) + int(shape.height) for shape in placeholder_shapes) pad = Emu(220000) to_remove = [] for shape in slide.shapes: sx = int(shape.left) sy = int(shape.top) sw = int(shape.width) sh = int(shape.height) in_region = ( sx >= x_min - pad and sx + sw <= x_max + pad and sy >= y_min - pad and sy + sh <= y_max + pad ) is_text_placeholder = shape in placeholder_shapes is_empty_kpi_card = ( in_region and getattr(shape, 'auto_shape_type', None) == MSO_SHAPE.ROUNDED_RECTANGLE ) if is_text_placeholder or is_empty_kpi_card: to_remove.append(shape) for shape in to_remove: _remove_shape(shape) # ============================================================================== # 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 # ============================================================================== # DYNAMIC / UNIVERSAL REPORT BUILDER # ============================================================================== def build_report(data_file: str, config: ReportConfig, output_path: str) -> str: master_path = config.template_path or get_master_template('daily') prs = Presentation(master_path) df = load_generic_excel(data_file) if config.require_six_confirmations: confirmation_issues = validate_six_confirmations(config, list(df.columns)) if confirmation_issues: raise ValueError('生成前六项确认未通过：\n- ' + '\n- '.join(confirmation_issues)) profile = config.data_profiling or {} colors = theme_to_rgb_colors(config.theme) metrics = calc_generic_metrics(df, config) content_top = _detect_content_top(prs.slides[1]) if len(prs.slides) > 1 else 1524000 total_pages = len([p for p in config.pages if p.selected]) if total_pages == 0: total_pages = len(config.pages) for page_idx, page_def in enumerate(config.pages): if not page_def.selected: continue page_num = page_idx + 1 if page_def.page_type == 'cover': _build_cover_page(prs, config, colors) elif page_def.page_type == 'toc': _build_toc_page(prs, config, colors) elif page_def.page_type == 'kpi_overview': _build_kpi_overview_page(prs, config, metrics, colors, content_top, df, profile) elif page_def.page_type == 'trend': _build_trend_page(prs, config, df, profile, colors, content_top) elif page_def.page_type == 'distribution': _build_distribution_page(prs, config, df, profile, colors, content_top, page_def) elif page_def.page_type == 'ranking': _build_ranking_page(prs, config, df, profile, colors, content_top, page_def) elif page_def.page_type == 'summary': _build_summary_page(prs, config, metrics, profile, colors, content_top, page_def) elif page_def.page_type == 'end': _build_end_page(prs, config, colors) for slide in prs.slides: _ensure_word_wrap_all(slide) _delete_template_slides(prs) prs.save(output_path) print(f"Report saved: {output_path}") return output_path def quality_assured_build(data_file: str, config: ReportConfig, output_path: str) -> tuple: if config.require_six_confirmations: df = load_generic_excel(data_file) confirmation_issues = validate_six_confirmations(config, list(df.columns)) if confirmation_issues: raise ValueError('生成前六项确认未通过：\n- ' + '\n- '.join(confirmation_issues)) inspector = QualityInspector(theme_to_rgb_colors(config.theme)) return inspector.quality_assured_build( build_fn=lambda d, c: _build_without_save(d, c, config), data=data_file, config=config, output_path=output_path, ) def _build_without_save(data_file, temp_config, original_config): from pptx import Presentation as Prs prs = Prs(get_master_template('daily')) df = load_generic_excel(data_file) profile = original_config.data_profiling or {} colors = theme_to_rgb_colors(original_config.theme) metrics = calc_generic_metrics(df, original_config) content_top = _detect_content_top(prs.slides[1]) if len(prs.slides) > 1 else 1524000 for page_def in original_config.pages: if not page_def.selected: continue if page_def.page_type == 'cover': _build_cover_page(prs, original_config, colors) elif page_def.page_type == 'kpi_overview': _build_kpi_overview_page(prs, original_config, metrics, colors, content_top, df, profile) elif page_def.page_type == 'trend': if not _build_trend_page(prs, original_config, df, profile, colors, content_top): _build_fallback_analysis_page(prs, original_config, page_def, df, profile, metrics, colors, content_top) elif page_def.page_type == 'distribution': if not _build_distribution_page(prs, original_config, df, profile, colors, content_top, page_def): _build_fallback_analysis_page(prs, original_config, page_def, df, profile, metrics, colors, content_top) elif page_def.page_type == 'ranking': if not _build_ranking_page(prs, original_config, df, profile, colors, content_top, page_def): _build_fallback_analysis_page(prs, original_config, page_def, df, profile, metrics, colors, content_top) elif page_def.page_type == 'summary': _build_summary_page(prs, original_config, metrics, profile, colors, content_top, page_def) elif page_def.page_type == 'end': _build_end_page(prs, original_config, colors) elif page_def.page_type == 'toc': _build_toc_page(prs, original_config, colors) for slide in prs.slides: _ensure_word_wrap_all(slide) _delete_template_slides(prs) return prs def _build_cover_page(prs, config, colors): slide = _duplicate_slide(prs, prs.slides[0]) _replace_all_placeholders(slide, { '{report_title}': config.title, '{report_type}': '数据报告', '{date}': config.period_str or config.date_range[0].strftime('%Y年%m月%d日'), '{department}': config.source_label, '{period}': config.period_str, '{gen_time}': datetime.now().strftime('%Y-%m-%d %H:%M'), }) _remove_empty_cover_kpi_placeholders(slide) _add_footer_if_missing(slide, f'数据来源：{config.source_label} | 1/{len(config.pages)}') def _build_fallback_analysis_page(prs, config, page_def, df, profile, metrics, colors, content_top): """ Fallback page builder: generates analysis text from available data when the primary page type cannot produce content (e.g. no time columns for trend, no category columns for distribution). Produces at least 4 deep analysis blocks with data citations. """ slide = _duplicate_slide(prs, prs.slides[1]) page_title = page_def.title if page_def and page_def.title else f'{config.title}数据分析' _replace_all_placeholders(slide, { '{report_title}': config.title, '{date}': config.period_str, '{page_title}': page_title, '{source}': config.source_label, '{period}': '', '{page_num}': '', }) num_cols = profile.get('numeric_columns', []) cat_cols = profile.get('category_columns', []) insight_items = [] if num_cols: top_metric = num_cols[0] top_name = top_metric.get('inferred_label', top_metric['column_name']) top_vals = df[top_metric['column_name']].dropna() if len(top_vals) > 0: mean_val = top_vals.mean() max_val = top_vals.max() min_val = top_vals.min() median_val = top_vals.median() total_val = top_vals.sum() insight_items.append({ 'title': f'{top_name}整体概览', 'content': f'报告周期内，{top_name}统计数据共包含 {len(top_vals)} 条有效记录。' f'总和为 {total_val:,.0f}，平均值为 {mean_val:,.2f}，中位数为 {median_val:,.2f}。' f'最大值为 {max_val:,.2f}，最小值为 {min_val:,.2f}。' f'{"数据波动范围较大，最大值与最小值差距显著，说明不同条目间差异明显，建议深入分析极端值成因" if min_val > 0 and max_val / max(min_val, 1) > 100 else "数据整体分布较为均衡，波动性在合理范围内"}。' f'中位数与平均值的偏差反映了数据的{"右偏分布（少数大值拉高了均值），说明存在显著头部效应" if median_val < mean_val * 0.8 else "左偏分布" if median_val > mean_val * 1.2 else "较为对称，数据呈正态分布趋势"}。', }) insight_items.append({ 'title': f'{top_name}分段分析', 'content': f'对 {top_name} 进行四分段统计：上四分位数（25%数据高于此值）为 {top_vals.quantile(0.75):,.2f}，' f'下四分位数（25%数据低于此值）为 {top_vals.quantile(0.25):,.2f}，' f'四分位距（IQR）为 {top_vals.quantile(0.75) - top_vals.quantile(0.25):,.2f}。' f'{"IQR较大，数据分布较为离散，不同类别的表现差异明显，需关注尾部类别的提升空间" if (top_vals.quantile(0.75) - top_vals.quantile(0.25)) > abs(mean_val) * 0.5 else "IQR在合理范围内，数据集中度较好"}。' f'建议按四分位将数据分为四组，重点跟踪上四分位组的表现，识别可复制的成功因素。', }) if cat_cols and num_cols: cat = cat_cols[0] cat_name = cat.get('inferred_label', cat['column_name']) num = num_cols[0] num_name = num.get('inferred_label', num['column_name']) cat_unique = df[cat['column_name']].dropna().nunique() insight_items.append({ 'title': f'{cat_name}分类覆盖分析', 'content': f'数据共覆盖 {cat_unique} 个不同的{cat_name}，在 {num_name} 维度上呈现差异化分布。' f'不同{cat_name}对整体{num_name}的贡献度各异，建议按贡献度大小将{cat_name}进行分类管理。' f'高贡献类别应重点维护和深度挖掘，中等贡献类别需持续培育和资源投入，' f'低贡献类别可评估其战略价值，适当调整投入节奏。建议建立分类分级管理体系，' f'每月跟踪各类别的变化趋势和占比波动。', }) if len(num_cols) >= 2: num1 = num_cols[0] num2 = num_cols[1] ratio = df[num1['column_name']].sum() / max(df[num2['column_name']].sum(), 1) insight_items.append({ 'title': '关键比率与效率指标', 'content': f'{num1.get("inferred_label", num1["column_name"])}与{num2.get("inferred_label", num2["column_name"])}的比率为 {ratio:.2f}，' f'该比率是衡量业务效率的重要参考指标。' f'{"比率处于较高水平，表明单位投入产出效率良好" if ratio > 1 else "比率偏低，单位投入的产出效益有限，存在效率提升空间"}。' f'建议将此比率纳入定期监控指标，按月环比追踪变化趋势，' f'并针对低比率项目制定专项提升计划，分析制约因素和可优化环节。', }) insight_items.append({ 'title': '数据质量与代表性评估', 'content': f'本报告基于共 {len(df)} 条记录进行分析，数据覆盖范围包括上述多个维度。' f'建议在后续周期中持续关注数据完整性和及时性，确保分析结果准确反映业务真实情况。' f'对于数据量较小或集中度较高的维度，应结合业务判断进行解读，避免以偏概全。' f'同时建议补充更多维度的数据（如时间序列数据、竞品对标数据等），' f'以支撑更全面的分析视角和更精准的决策建议。', }) if not insight_items: insight_items = [{ 'title': '数据总览', 'content': f'当前数据集包含 {len(df)} 条记录，{len(df.columns)} 个字段。' f'数值字段 {len(num_cols)} 个，分类字段 {len(cat_cols)} 个。' f'建议结合业务场景规划具体的数据分析维度，' f'以生成更具洞察力和指导意义的数据报告。', }] if num_cols and len(df) > 0: top_col = num_cols[0] chart_zone = get_chart_left_zone(content_top, 0.4) text_zone = get_insight_right_zone(content_top, 0.4) sample_vals = df[top_col['column_name']].dropna().head(10).tolist() sample_labels = [f'记录{i+1}' for i in range(len(sample_vals))] if sample_vals: add_bar_chart(slide, sample_labels, sample_vals, Emu(chart_zone.x), Emu(chart_zone.y), Emu(chart_zone.width), Emu(chart_zone.height), series_name=top_col.get('inferred_label', top_col['column_name']), color=colors.get('primary')) _add_structured_insight(slide, insight_items, Emu(text_zone.x), Emu(text_zone.y), Emu(text_zone.width), Emu(text_zone.height)) else: zone = get_full_width_zone(content_top) _add_structured_insight(slide, insight_items, Emu(zone.x), Emu(zone.y), Emu(zone.width), Emu(zone.height)) def _build_toc_page(prs, config, colors): slide = _duplicate_slide(prs, prs.slides[1]) active_pages = [p for p in config.pages if p.selected and p.page_type not in ('cover', 'toc', 'end')] _replace_all_placeholders(slide, { '{report_title}': config.title, '{date}': config.period_str, '{page_title}': '目录', '{source}': config.source_label, '{period}': f'2/{len(config.pages)}', '{page_num}': '', }) for i, page in enumerate(active_pages[:6], 1): _replace_placeholder(slide, f'{{chapter{i}_title}}', page.title) _replace_placeholder(slide, f'{{chapter{i}_desc}}', page.conclusion_title or page.title) def _build_kpi_overview_page(prs, config, metrics, colors, content_top, df=None, profile=None): slide = _duplicate_slide(prs, prs.slides[1]) page_title = '核心指标概览' _replace_all_placeholders(slide, { '{report_title}': config.title, '{date}': config.period_str, '{page_title}': page_title, '{source}': config.source_label, '{period}': '', '{page_num}': '', }) kpi_items = [] primary_vals = {} all_vals = {} for md in config.metrics: if md.metric_type.value == 'kpi' and md.selected: val = metrics.get(md.name, 0) display_val = format(val, md.format_spec) if isinstance(val, (int, float)) else str(val) kpi_items.append({ 'label': md.label, 'value': display_val, 'unit': md.unit, 'change': '', 'sub': '', }) if md.is_primary: primary_vals[md.label] = val all_vals[md.label] = val if kpi_items: _add_kpi_cards(slide, kpi_items[:6], start_y=Emu(content_top)) insight_items = [] kpi_names = [m.label for m in config.metrics if m.selected] kpi_str = "、".join(kpi_names[:6]) if kpi_names else "各指标" primary_kpis = [m for m in config.metrics if m.is_primary and m.selected] if not primary_kpis: primary_kpis = [m for m in config.metrics if m.selected][:3] kpi_detail_parts = [] for i, pk in enumerate(primary_kpis): val = all_vals.get(pk.label, 0) unit_str = pk.unit if pk.unit else '' display_val = format(val, pk.format_spec) if isinstance(val, (int, float)) else str(val) kpi_detail_parts.append(f'{pk.label}: {display_val}{unit_str}') insight_items.append({ 'title': '核心数据概览', 'content': f'本期报告涵盖 {kpi_str} 共 {len(kpi_names)} 项核心指标。' f'{"；".join(kpi_detail_parts[:4])}。' f'其中{"、".join(p.label for p in primary_kpis[:3])}为本次分析的重点关注指标。' f'建议将这些指标与历史同期数据进行纵向对比，以及与行业基准进行横向对标，以全面评估当前业务健康度。' f'对于波动较大的指标，需深入追溯其背后的业务动因，判断是否为趋势性变化还是季节性波动。', }) cat_cols = profile.get('category_columns', []) if profile else [] num_cols = profile.get('numeric_columns', []) if profile else [] total_rows = profile.get('total_rows', 0) if profile else 0 if cat_cols: top_cats = [c.get('inferred_label', c.get('column_name', '')) for c in cat_cols[:3]] cat_details = [] for c in cat_cols[:3]: uc = c.get('unique_count', 'N/A') cat_details.append(f'{c.get("inferred_label", c.get("column_name", ""))}({uc}类)') insight_items.append({ 'title': '数据覆盖与维度分析', 'content': f'数据覆盖 {total_rows:,} 条记录，包含 {", ".join(cat_details)} 等多个分析维度。' f'丰富的维度数据支持从 {", ".join(top_cats)} 等角度进行多维度联动分析。' f'建议关注各维度下的数据分布特征，识别高贡献或异常的分类群体，' f'针对性地分析不同维度的表现差异，为精细化运营和数据驱动决策提供支撑。', }) if len(config.metrics) >= 3: compare_items = [] for a, b in zip(primary_kpis[:2], primary_kpis[1:3]): va = all_vals.get(a.label, 0) vb = all_vals.get(b.label, 0) if va and vb: ratio = round(va / vb, 2) if vb else 0 compare_items.append(f'{a.label}与{b.label}的比值为 {ratio}') if compare_items: insight_items.append({ 'title': '指标间关联分析', 'content': f'{"；".join(compare_items)}。通过指标间的比值关系可以发现数据的内在规律，' f'比值异常偏离正常区间时需重点关注。建议进一步计算各指标与核心业务目标之间的相关系数，' f'量化不同指标对业务目标的影响力排序，将有限资源聚焦在驱动型指标上。', }) else: insight_items.append({ 'title': '指标间关联分析', 'content': f'本期核心指标包括 {", ".join(p.label for p in primary_kpis[:3])}。' f'建议通过散点图或相关系数分析探索指标间的线性/非线性关系，识别是否存在协同或对冲效应。' f'同时建议按时间序列分析各指标的周期性规律，为资源配置和预测提供依据。', }) insight_items.append({ 'title': '关键发现与行动建议', 'content': f'综合分析 {len(kpi_names)} 项指标，建议重点关注以下方向：' f'(1) 定期监控核心指标的趋势变化，建立异常预警机制，当指标偏离正常区间时及时触发排查流程；' f'(2) 深化多维度交叉分析，挖掘不同群体间的结构差异，识别增长机会和风险点；' f'(3) 结合业务经验和外部数据，验证数据指标的准确性和合理性；' f'(4) 将分析结论转化为可执行的具体行动项，明确责任人和时间节点，建立跟踪闭环机制。', }) kpi_rows = 2 if len(kpi_items) > 3 else 1 kpi_grid_bottom = int(content_top) + Emu(3048000) if kpi_rows == 2: kpi_grid_bottom += Emu(3429000) insight_zone_y = kpi_grid_bottom + Emu(254000) remaining_height = int(FOOTER_TOP - insight_zone_y - Emu(180000)) if remaining_height >= Emu(1400000): compact_items = insight_items[:2] if kpi_rows == 2 else insight_items[:3] _add_structured_insight(slide, compact_items, Emu(CONTENT_LEFT), Emu(insight_zone_y), Emu(SLIDE_WIDTH - 2 * CONTENT_LEFT), Emu(remaining_height), title_size=Pt(10), body_size=Pt(9), min_body_size=Pt(8)) def _build_trend_page(prs, config, df, profile, colors, content_top): slide = _duplicate_slide(prs, prs.slides[1]) time_cols = profile.get('time_columns', []) num_cols = profile.get('numeric_columns', []) if not time_cols or not num_cols: return False time_col = time_cols[0]['column_name'] metric_col = num_cols[0]['column_name'] label = num_cols[0].get('inferred_label', metric_col) page_title = f'{label}趋势' _replace_all_placeholders(slide, { '{report_title}': config.title, '{date}': config.period_str, '{page_title}': page_title, '{source}': config.source_label, '{period}': '', '{page_num}': '', }) trend_data = calc_generic_trend(df, time_col, metric_col) if trend_data.get('dates'): chart_zone = get_chart_left_zone(content_top, 0.6) text_zone = get_insight_right_zone(content_top, 0.6) add_line_chart(slide, trend_data['dates'], trend_data['values'], Emu(chart_zone.x), Emu(chart_zone.y), Emu(chart_zone.width), Emu(chart_zone.height), series_name=label, color=colors.get('primary')) dates = trend_data['dates'] vals = trend_data['values'] n = len(vals) first_v, last_v = vals[0], vals[-1] change = last_v - first_v change_pct = round(change / first_v * 100, 1) if first_v else 0 max_v = max(vals) if vals else 0 min_v = min(vals) if vals else 0 max_idx = vals.index(max_v) if vals else 0 min_idx = vals.index(min_v) if vals else 0 peak_date = dates[max_idx] if max_idx < len(dates) else 'N/A' trough_date = dates[min_idx] if min_idx < len(dates) else 'N/A' direction_text = '上升' if change > 0 else '下降' if change < 0 else '平稳' volatility = round((max_v - min_v) / (sum(vals) / n) * 100, 1) if sum(vals) else 0 if vals else 0 insight_items = [ { 'title': f'{label}整体趋势概况', 'content': f'在报告周期内共采集 {n} 个时间点的数据，{label}' f'从 {dates[0]} 的 {first_v:,.0f} 变动至 {dates[-1]} 的 {last_v:,.0f}，' f'整体{direction_text}{abs(change_pct):.1f}%，{direction_text}趋势{"显著" if abs(change_pct) > 20 else "温和" if abs(change_pct) > 5 else "较为平缓"}。' f'数据变化轨迹反映出{"持续向好的增长态势" if direction_text == "上升" and abs(change_pct) > 10 else "温和改善的积极信号" if direction_text == "上升" else "回调盘整的阶段性特征" if direction_text == "下降" else "平稳运行的基本状态"}，' f'建议将当前趋势与业务目标和历史同期数据进行交叉对比，评估达成全年目标的可行性。如需更详尽的趋势分析，建议增加数据采集频度和时间跨度。', }, { 'title': '峰值与谷值分析', 'content': f'周期内最高值出现在 {peak_date}，为 {max_v:,.0f}；' f'最低值出现在 {trough_date}，为 {min_v:,.0f}。' f'极值差距 {max_v - min_v:,.0f}，波动幅度 {volatility}%，' f'{"波动显著，需关注异常节点的驱动因素，建议排查是否受节假日、促销活动、外部政策变化等因素影响" if volatility > 30 else "波动在可控范围内，但仍需对异常波动保持警觉"}{"." if volatility > 30 else "，建立异常值的快速预警和响应机制。"}', }, { 'title': '趋势阶段性特征', 'content': f'前半程（{dates[0]}至{dates[min(n//2, n-1)]}）' f'{"呈上升态势" if sum(vals[:n//2]) < sum(vals[n//2:]) else "呈下降态势" if sum(vals[:n//2]) > sum(vals[n//2:]) else "基本持平"}，' f'后半程均值为 {sum(vals[n//2:])/(n-n//2):,.0f}。建议结合业务事件节点深入分析拐点成因，' f'重点关注是否存在季节性波动、周期性波动或外部冲击等结构性因素。' f'若数据量较少，趋势解读应以业务经验为主，辅以数据验证。', }, { 'title': '业务启示', 'content': f'综合趋势分析，当前数据反映出{"积极向好的发展态势" if direction_text == "上升" and abs(change_pct) > 10 else "温和稳定的运行动态" if abs(change_pct) <= 10 else "需重点关注的下行风险"}。' f'建议{"加大资源投入以把握增长机遇，同时关注增速的可持续性，避免盲目扩张" if direction_text == "上升" else "排查下降原因并制定针对性应对措施，分析是短期波动还是长期趋势转折" if direction_text == "下降" else "保持当前运营节奏，同时关注潜在变化信号，适时调整策略" if direction_text == "平稳" else "继续观察数据走势"}。' f'建议将数据与业务KPI目标进行对标分析，定期回顾趋势变化。', }, ] _add_structured_insight(slide, insight_items, Emu(text_zone.x), Emu(text_zone.y), Emu(text_zone.width), Emu(text_zone.height)) return True return False def _build_distribution_page(prs, config, df, profile, colors, content_top, page_def=None): slide = _duplicate_slide(prs, prs.slides[1]) cat_cols = profile.get('category_columns', []) num_cols = profile.get('numeric_columns', []) if not cat_cols: return False elem = (page_def.elements or [{}])[0] if page_def else {} cat_col = elem.get('category') or cat_cols[0]['column_name'] cat_label = elem.get('category_label') or next( (c.get('inferred_label', cat_col) for c in cat_cols if c['column_name'] == cat_col), cat_col) metric_col = elem.get('metric') or (num_cols[0]['column_name'] if num_cols else None) metric_label = elem.get('metric_label') or (next( (c.get('inferred_label', metric_col) for c in num_cols if c['column_name'] == metric_col), metric_col) if metric_col else '') page_title = page_def.title if page_def and page_def.title else f'{cat_label}分布' _replace_all_placeholders(slide, { '{report_title}': config.title, '{date}': config.period_str, '{page_title}': page_title, '{source}': config.source_label, '{period}': '', '{page_num}': '', }) dist = calc_generic_distribution(df, cat_col, metric_col, top_n=8) if dist.get('categories'): chart_zone = get_chart_left_zone(content_top, 0.55) text_zone = get_insight_right_zone(content_top, 0.55) if len(dist['categories']) <= 8: add_doughnut_chart(slide, dist['categories'], dist['values'], Emu(chart_zone.x), Emu(chart_zone.y), Emu(chart_zone.width), Emu(chart_zone.height), colors=colors.get('series')) else: add_bar_chart(slide, dist['categories'], dist['values'], Emu(chart_zone.x), Emu(chart_zone.y), Emu(chart_zone.width), Emu(chart_zone.height), series_name=metric_label, color=colors.get('primary')) cats, vals, pcts = dist['categories'], dist['values'], dist['percentages'] grand_total = sum(vals) top3_pct = sum(pcts[:3]) top1_name, top1_val, top1_pct = cats[0], vals[0], pcts[0] metric_suffix = metric_label if metric_label else '数量' insight_items = [ { 'title': f'{cat_label}分布概况', 'content': f'共有 {len(cats)} 个不同的{cat_label}，覆盖范围' f'{"广泛" if len(cats) >= 8 else "较为丰富" if len(cats) >= 5 else "相对集中"}。' f'前3名合计占比 {top3_pct:.1f}%，集中度' f'{"较高，呈现显著的头部集中特征" if top3_pct > 70 else "中等，呈现梯度递减分布" if top3_pct > 50 else "较低，分布较为均衡"}。', }, { 'title': f'排名第一: {top1_name}', 'content': f'{top1_name}以 {top1_val:,}{metric_suffix}（占比 {top1_pct:.1f}%）位居榜首，' f'{"是第二名" + cats[1] + "的" + f"{round(top1_val/vals[1],1)}" + "倍，优势极为显著" if len(cats) > 1 else "是该维度中最重要的类别"}。' f'该类别贡献了超过三分之一的{metric_label}，是整体业务的基本盘和核心增长极。', }, ] if len(vals) >= 3: top3_sum = sum(vals[:3]) tail_sum = sum(vals[3:]) tail_pct = sum(pcts[3:]) insight_items.append({ 'title': '长尾分布特征', 'content': f'前三名累计 {top3_sum:,}{metric_suffix}（{top3_pct:.1f}%），' f'剩余 {len(cats)-3} 个合计 {tail_sum:,}{metric_suffix}（{tail_pct:.1f}%），' f'属于{"头部集中型分布" if top3_pct > 70 else "相对均衡分布" if top3_pct < 50 else "梯度递减型分布"}。' f'头部贡献了绝大部分{metric_label}，尾部虽数量众多但单个贡献有限。', }) if len(vals) > 1: avg_val = sum(vals) / len(vals) cv = round(vals[0] / avg_val, 1) if avg_val else 0 median_idx = len(vals) // 2 median_val = vals[median_idx] insight_items.append({ 'title': '差异化与离散度分析', 'content': f'排名第一的{cat_label}{top1_name}的{metric_suffix}是全部分类均值的 {cv} 倍，' f'中位数分类（第{median_idx+1}名）为 {median_val:,}{metric_suffix}，' f'表明该维度{"差异化显著，资源集中度较高" if cv > 3 else "差异化适中，各分类间差距可控" if cv > 1.5 else "分布较为均匀"}。' f'头部与中位数的差距反映了{cat_label}维度上的分层特征，是运营资源重点倾斜方向。', }) insight_items.append({ 'title': '业务启示', 'content': f'建议重点关注 {cats[0]} 的增量拓展与存量维护，同时深入分析排名中位类别的提升空间。' f'对于 {metric_label}贡献较小的尾部类别（如占比低于3%的分类），可评估是否优化资源配置、' f'调整运营策略或将资源向高回报类别倾斜。结合{cat_label}维度持续跟踪分布变化，及时把握结构性机会。', }) _add_structured_insight(slide, insight_items, Emu(text_zone.x), Emu(text_zone.y), Emu(text_zone.width), Emu(text_zone.height)) return True return False def _build_ranking_page(prs, config, df, profile, colors, content_top, page_def=None): slide = _duplicate_slide(prs, prs.slides[1]) cat_cols = profile.get('category_columns', []) num_cols = profile.get('numeric_columns', []) if not cat_cols or not num_cols: return False elem = (page_def.elements or [{}])[0] if page_def else {} rank_col = elem.get('category') or cat_cols[-1]['column_name'] rank_label = elem.get('category_label') or next( (c.get('inferred_label', rank_col) for c in cat_cols if c['column_name'] == rank_col), rank_col) metric_col = elem.get('metric') or num_cols[0]['column_name'] metric_label = elem.get('metric_label') or next( (c.get('inferred_label', metric_col) for c in num_cols if c['column_name'] == metric_col), metric_col) page_title = page_def.title if page_def and page_def.title else f'{rank_label}TOP排行' _replace_all_placeholders(slide, { '{report_title}': config.title, '{date}': config.period_str, '{page_title}': page_title, '{source}': config.source_label, '{period}': '', '{page_num}': '', }) ranking = calc_generic_ranking(df, rank_col, metric_col, top_n=15) if ranking: chart_zone = get_chart_left_zone(content_top, 0.6) text_zone = get_insight_right_zone(content_top, 0.6) names = [r['name'] for r in ranking] vals = [r['value'] for r in ranking] add_bar_chart(slide, names, vals, Emu(chart_zone.x), Emu(chart_zone.y), Emu(chart_zone.width), Emu(chart_zone.height), series_name=metric_label, color=colors.get('primary')) total_val = sum(vals) top3_names = [r['name'] for r in ranking[:3]] top3_vals = [r['value'] for r in ranking[:3]] top3_pct = [round(v / total_val * 100, 1) for v in top3_vals] if total_val else [0, 0, 0] top1_vs_last = round(vals[0] / vals[-1], 1) if len(vals) > 1 and vals[-1] > 0 else 'N/A' insight_items = [ { 'title': f'{rank_label}TOP排行概况', 'content': f'共展示 {len(ranking)} 个排名项，前3名分别为 {top3_names[0]}、{top3_names[1]}、' f'{top3_names[2]}，累计 {sum(top3_vals):,}{metric_label}（{sum(top3_pct):.1f}%）。' f'前三名合计贡献超过总量的三分之一，表明{rank_label}维度呈现{"显著的头部集中特征" if sum(top3_pct) > 60 else "梯度递减的分布格局" if sum(top3_pct) > 40 else "相对均衡的分布态势"}。', }, { 'title': f'榜首分析: {top3_names[0]}', 'content': f'{top3_names[0]}以 {top3_vals[0]:,}{metric_label}（占比 {top3_pct[0]:.1f}%）位居榜首，' f'{"是第2名" + top3_names[1] + "的" + f"{round(top3_vals[0]/top3_vals[1],1)}倍，领先优势显著" if len(ranking) > 1 and top3_vals[1] > 0 else "优势突出"}。' f'作为排名第一的{rank_label}，其业绩表现直接影响整体业务大盘，建议重点关注其可持续增长策略。', }, { 'title': '头部与尾部差距分析', 'content': f'第1名与第{len(ranking)}名差距达 {top1_vs_last} 倍，' f'前5名平均 {round(sum(vals[:5])/5):,}{metric_label}，' f'后5名平均 {round(sum(vals[-5:])/5):,}{metric_label}，' f'前后差距约 {round((sum(vals[:5])/5)/(sum(vals[-5:])/5),1) if sum(vals[-5:]) > 0 else "N/A"} 倍。' f'{"头部效应极为明显，需关注是否因资源分配不均导致" if isinstance(top1_vs_last, float) and top1_vs_last > 10 else "差距较为显著，存在分层优化的空间" if isinstance(top1_vs_last, float) and top1_vs_last > 5 else "梯度分布相对均衡，可针对性提升各层级表现"}。', }, { 'title': '累计贡献率与分层分析', 'content': f'前5名累计贡献 {sum(vals[:5]):,}{metric_label}（{round(sum(vals[:5])/total_val*100,1) if total_val else 0}%），' f'前10名累计贡献 {sum(vals[:10]):,}{metric_label}（{round(sum(vals[:10])/total_val*100,1) if total_val else 0}%），' f'剩余 {len(ranking)-10} 名合计贡献 {sum(vals[10:]):,}{metric_label}（{round(sum(vals[10:])/total_val*100,1) if total_val else 0}%）。' f'从分层结构来看，可划分为三个梯队：第一梯队（前3名）为业绩核心贡献者，第二梯队（第4-8名）为稳定输出层，' f'第三梯队（第9名及以后）为潜力提升层。', }, { 'title': '业务建议', 'content': f'重点关注 {", ".join(top3_names)} 的发展动态，提炼其成功经验并推广至团队。' f'对于排名靠后的{rank_label}，可评估其增长潜力与资源匹配度，' f'识别可突破的增量空间。建议建立{rank_label}的绩效考核与激励体系，' f'通过标杆带动和梯队培养实现整体业绩提升。', }, ] _add_structured_insight(slide, insight_items, Emu(text_zone.x), Emu(text_zone.y), Emu(text_zone.width), Emu(text_zone.height)) return True return False def _build_summary_page(prs, config, metrics, profile, colors, content_top, page_def=None): slide = _duplicate_slide(prs, prs.slides[1]) page_title = page_def.title if page_def and page_def.title else '总结与建议' _replace_all_placeholders(slide, { '{report_title}': config.title, '{date}': config.period_str, '{page_title}': page_title, '{source}': config.source_label, '{period}': '', '{page_num}': '', }) elem = (page_def.elements or [{}])[0] if page_def else {} if elem.get('support_status') is not None: status = elem['support_status'] dept = elem.get('support_by_dept', {}) sc = elem.get('support_count', 0) cc = elem.get('closed_count', 0) close_rate = round(cc / sc * 100, 1) if sc else 0 fully_closed = status.get('已闭环', 0) partial_closed = status.get('部分闭环', 0) not_closed = status.get('未闭环', 0) insight_items = [{ 'title': '支持需求总览', 'content': f'本期共产生 {sc} 项跨部门支持需求，其中已闭环 {cc} 项（含完全闭环 {fully_closed} 项、部分闭环 {partial_closed} 项），' f'闭环率 {close_rate}%。未闭环需求 {sc - cc} 项（占比 {round((sc-cc)/sc*100,1) if sc else 0}%），' f'闭环率{"较高，跨部门协作效率良好" if close_rate >= 60 else "处于中等水平，仍有提升空间" if close_rate >= 30 else "偏低，需重点关注闭环推动"}。' f'跨部门支持是保障项目推进的重要环节，高效的闭环机制有助于提升客户满意度和订单转化效率。', }] if status: total_status = sum(status.values()) fully_pct = round(fully_closed / total_status * 100, 1) if total_status else 0 partial_pct = round(partial_closed / total_status * 100, 1) if total_status else 0 not_pct = round(not_closed / total_status * 100, 1) if total_status else 0 insight_items.append({ 'title': '闭环状态明细', 'content': f'已闭环 {fully_closed} 项（{fully_pct}%）、部分闭环 {partial_closed} 项（{partial_pct}%）、' f'未闭环 {not_closed} 项（{not_pct}%）。' f'其中完全闭环占比{"超过七成，闭环质量较高" if fully_pct >= 70 else "处于中等水平" if fully_pct >= 40 else "偏低，需提升闭环完整性"}。' f'部分闭环表明需求已部分满足但未完全解决，需持续跟踪至彻底闭环。', }) if dept: dept_top = list(dept.items())[:5] dept_top_sum = sum(v for _, v in dept_top) dept_total = sum(dept.values()) dept_str = '、'.join([f'{k}（{v}项）' for k, v in dept_top]) avg_dept_load = round(dept_total / len(dept), 1) if dept else 0 max_dept = dept_top[0] insight_items.append({ 'title': '支持部门工作量分布', 'content': f'需求覆盖 {len(dept)} 个部门/科室，前5个部门承接 {dept_top_sum} 项（{round(dept_top_sum/dept_total*100,1) if dept_total else 0}%）。' f'Top部门：{dept_str}。其中{max_dept[0]}承接最多（{max_dept[1]}项），' f'平均每个部门承接 {avg_dept_load} 项。请关注工作量较大的部门资源分配是否充足，' f'同时识别是否有部门长期未被分配需求（可能表明资源未充分利用）。', }) if sc - cc > 0: insight_items.append({ 'title': '未闭环需求跟进建议', 'content': f'当前仍有 {sc - cc} 项需求未完成闭环。建议按以下策略推进：第一，按紧急程度和影响范围对未闭环需求进行优先级排序，' f'高优需求指定专人负责限期解决；第二，建立周度闭环跟踪机制，定期更新需求处理进展；' f'第三，对于跨部门协同的复杂需求，建议指定牵头部门统筹协调推进，' f'并建立问题升级机制（当需求超期未解决时自动升级至更高层级协调）。', }) insight_items.append({ 'title': '闭环效率提升建议', 'content': f'为持续提升支持需求闭环效率，建议：一是建立标准化的需求流转流程，明确各环节责任人和响应时限；' f'二是定期开展闭环案例复盘，提炼最佳实践并在团队内推广；' f'三是建立闭环率考核指标，将闭环时效纳入部门协作评价体系，' f'通过制度保障跨部门协作的效率和质量。', }) else: insight_items = generate_generic_insights(profile, metrics) zone = get_full_width_zone(content_top) _add_structured_insight(slide, insight_items, Emu(zone.x), Emu(zone.y), Emu(zone.width), Emu(zone.height)) def _build_end_page(prs, config, colors): slide = _duplicate_slide(prs, prs.slides[3] if len(prs.slides) > 3 else prs.slides[0]) total = len([p for p in config.pages if p.selected]) _add_footer_if_missing(slide, f'数据来源：{config.source_label} | {total}/{total}') _replace_all_placeholders(slide, { '{report_title}': config.title, }) # ============================================================================== # 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)