Spaces:
Running
Running
| """ | |
| app.py — 基金 KPI Dashboard | |
| 支援： | |
| ‧ 特別收入基金 → FNGFRB2060（來源用途餘絀）/ FNGFRB2080（平衡表） | |
| ‧ 作業基金 → NBAWIR070（收支餘絀）/ FNGFRB2080（平衡表）/ FNWFRB2020（固定資產） | |
| """ | |
| from pathlib import Path | |
| from typing import Dict, List, Tuple | |
| import pandas as pd | |
| import streamlit as st | |
| from src.utils.parser import ( | |
| parse_xml_to_df, | |
| convert_excel_to_xlsx, | |
| convert_excel_to_ods, | |
| convert_doc_to_docx, | |
| ) | |
| from src.utils.visualizations import ( | |
| # 色彩 | |
| NEGATIVE, POSITIVE, NEUTRAL, WARN, INFO, SOURCE_MAIN, USES_MAIN, CARD_BG, TEXT_MAIN, | |
| # 特別收入基金 | |
| plot_balance_sheet, plot_budget_vs_actual, plot_execution_gauge, | |
| plot_kpi_trend, plot_monthly_stacked, plot_nested_donut, | |
| plot_sankey, plot_sankey_nba, plot_variance_bar, to_yi, | |
| # 作業基金 | |
| plot_surplus_waterfall, plot_fixed_assets_bar, plot_fixed_assets_treemap, | |
| plot_nba_compare, | |
| summarize_nba, summarize_fixed_assets, | |
| summarize_cashflow, plot_cashflow_waterfall, plot_cashflow_detail, plot_cashflow_bs_diff, | |
| ) | |
| # ── 頁面設定 ───────────────────────────────────────────────────────────────── | |
| st.set_page_config( | |
| page_title="基金 KPI Dashboard", | |
| page_icon="🏥", | |
| layout="wide", | |
| initial_sidebar_state="expanded", | |
| ) | |
| SAMPLES_DIR = Path("samples") | |
| # ── 全域樣式（淺色主題）───────────────────────────────────────────────────── | |
| CUSTOM_CSS = """ | |
| <style> | |
| @import url('https://fonts.googleapis.com/css2?family=DM+Sans:ital,opsz,wght@0,9..40,300;0,9..40,400;0,9..40,500;0,9..40,700;1,9..40,400&family=DM+Mono:wght@400;500&display=swap'); | |
| :root { | |
| --bg: #F0F4F8; | |
| --surface: #FFFFFF; | |
| --surface-2: #F7F9FB; | |
| --surface-3: #EDF1F5; | |
| --border: rgba(0,0,0,0.08); | |
| --border-hover: rgba(0,0,0,0.18); | |
| --text: #1A2332; | |
| --text-muted: #546E7A; | |
| --text-faint: #90A4AE; | |
| --source: #1976D2; | |
| --source-bg: rgba(25,118,210,0.08); | |
| --source-border: rgba(25,118,210,0.25); | |
| --uses: #7B1FA2; | |
| --uses-bg: rgba(123,31,162,0.08); | |
| --uses-border: rgba(123,31,162,0.25); | |
| --accent-green: #2E7D32; | |
| --accent-red: #C62828; | |
| --accent-amber: #E65100; | |
| --accent-info: #00838F; | |
| --accent-blue: #1976D2; | |
| --radius-sm: 8px; | |
| --radius-md: 12px; | |
| --radius-lg: 18px; | |
| } | |
| html, body, [class*="css"] { font-family: 'DM Sans', ui-sans-serif, system-ui, sans-serif; color: var(--text); } | |
| .stApp { | |
| background: var(--bg) !important; | |
| background-image: | |
| radial-gradient(ellipse 70% 35% at 5% -5%, rgba(25,118,210,0.06) 0%, transparent 55%), | |
| radial-gradient(ellipse 50% 25% at 95% 5%, rgba(123,31,162,0.04) 0%, transparent 50%) !important; | |
| } | |
| div.block-container { padding-top: 1.25rem; padding-bottom: 2.5rem; max-width: 1680px; } | |
| /* ── Hero ── */ | |
| .hero { | |
| background: linear-gradient(135deg, rgba(25,118,210,0.06), rgba(123,31,162,0.04)); | |
| border: 1px solid var(--border); border-radius: var(--radius-lg); | |
| padding: 1.5rem 2rem; margin-bottom: 1.5rem; | |
| } | |
| .hero-title { font-size: 1.75rem; font-weight: 700; color: var(--text); margin: 0 0 0.35rem; letter-spacing: -0.3px; } | |
| .hero-sub { font-size: 0.9rem; color: var(--text-muted); margin: 0; line-height: 1.6; } | |
| .hero-stat { display: inline-flex; align-items: center; gap: 0.3rem; margin-right: 1.2rem; } | |
| .hero-stat strong { color: var(--text); } | |
| /* ── Fund-type 標籤 ── */ | |
| .fund-badge { | |
| display: inline-flex; align-items: center; gap: 4px; | |
| border-radius: 999px; padding: 3px 12px; font-size: 0.78rem; font-weight: 600; | |
| margin-left: 0.75rem; vertical-align: middle; | |
| } | |
| .fund-badge.special { background: rgba(25,118,210,0.10); color: #1565C0; border: 1px solid rgba(25,118,210,0.25); } | |
| .fund-badge.operating { background: rgba(0,131,143,0.10); color: #006064; border: 1px solid rgba(0,131,143,0.25); } | |
| /* ── KPI 卡片 ── */ | |
| .kpi-card { | |
| background: var(--surface); border: 1px solid var(--border); border-radius: var(--radius-md); | |
| padding: 1rem 1.1rem; min-height: 112px; | |
| display: flex; flex-direction: column; justify-content: space-between; | |
| transition: transform .18s ease, border-color .18s ease, box-shadow .18s ease; | |
| position: relative; overflow: hidden; | |
| } | |
| .kpi-card::before { | |
| content: ''; position: absolute; top: 0; left: 0; right: 0; height: 3px; | |
| background: var(--kpi-accent, transparent); | |
| border-radius: var(--radius-md) var(--radius-md) 0 0; | |
| } | |
| .kpi-card:hover { transform: translateY(-2px); border-color: var(--border-hover); box-shadow: 0 6px 20px rgba(0,0,0,0.10); } | |
| .kpi-label { font-size: 0.72rem; font-weight: 600; color: var(--text-faint); text-transform: uppercase; letter-spacing: 0.8px; margin-bottom: 0.4rem; } | |
| .kpi-value { font-size: 1.65rem; font-weight: 700; color: var(--text); line-height: 1.1; font-variant-numeric: tabular-nums; letter-spacing: -0.5px; } | |
| .kpi-value.secondary { font-size: 1.1rem; color: var(--text-muted); } | |
| .kpi-delta { font-size: 0.8rem; font-weight: 500; margin-top: 0.3rem; display: flex; align-items: center; gap: 0.25rem; } | |
| /* ── 內容卡片 ── */ | |
| .section-card { | |
| background: var(--surface); border: 1px solid var(--border); border-radius: var(--radius-lg); | |
| padding: 1.25rem 1.4rem; margin-bottom: 1.1rem; box-shadow: 0 1px 4px rgba(0,0,0,0.05); | |
| } | |
| .card-title { font-size: 1rem; font-weight: 600; color: var(--text); margin: 0 0 0.2rem; letter-spacing: -0.15px; } | |
| .card-sub { font-size: 0.8rem; color: var(--text-muted); margin: 0 0 0.9rem; } | |
| /* ── 警示膠囊 ── */ | |
| .alerts-row { display: flex; flex-wrap: wrap; gap: 0.4rem; margin-bottom: 1rem; } | |
| .alert-pill { display: inline-flex; align-items: center; gap: 0.3rem; padding: 0.28rem 0.75rem; | |
| border-radius: 999px; font-size: 0.78rem; font-weight: 500; white-space: nowrap; } | |
| .alert-good { background: rgba(46,125,50,0.10); color: #2E7D32; border: 1px solid rgba(46,125,50,0.25); } | |
| .alert-warn { background: rgba(230,81,0,0.10); color: #E65100; border: 1px solid rgba(230,81,0,0.25); } | |
| .alert-bad { background: rgba(198,40,40,0.10); color: #C62828; border: 1px solid rgba(198,40,40,0.25); } | |
| .alert-info { background: rgba(25,118,210,0.10); color: #1976D2; border: 1px solid rgba(25,118,210,0.25); } | |
| /* ── 月份徽章 ── */ | |
| .month-badge { background: var(--surface); border: 1px solid var(--border); border-radius: var(--radius-md); | |
| padding: 0.75rem 1.1rem; text-align: center; box-shadow: 0 1px 4px rgba(0,0,0,0.05); } | |
| .month-badge .label { font-size: 0.7rem; color: var(--text-faint); text-transform: uppercase; letter-spacing: 1px; margin-bottom: 0.25rem; } | |
| .month-badge .value { font-size: 2.2rem; font-weight: 700; color: var(--text); line-height: 1; font-variant-numeric: tabular-nums; } | |
| .month-badge .note { font-size: 0.7rem; color: var(--text-faint); margin-top: 0.2rem; } | |
| /* ── 分隔線標題 ── */ | |
| .row-label { font-size: 0.7rem; font-weight: 600; color: var(--text-faint); text-transform: uppercase; | |
| letter-spacing: 1px; margin: 0.25rem 0 0.5rem; } | |
| /* ── 來源 / 用途標籤 ── */ | |
| .source-badge { display: inline-flex; align-items: center; gap: 5px; | |
| background: var(--source-bg); color: var(--source); border: 1px solid var(--source-border); | |
| border-radius: 999px; padding: 2px 10px; font-size: 0.72rem; font-weight: 600; } | |
| .uses-badge { display: inline-flex; align-items: center; gap: 5px; | |
| background: var(--uses-bg); color: var(--uses); border: 1px solid var(--uses-border); | |
| border-radius: 999px; padding: 2px 10px; font-size: 0.72rem; font-weight: 600; } | |
| /* ── Empty State ── */ | |
| .empty-state { text-align: center; padding: 3.5rem 1rem; color: var(--text-muted); } | |
| .empty-state .icon { font-size: 2.5rem; margin-bottom: 1rem; opacity: .5; } | |
| .empty-state h3 { font-size: 1.1rem; font-weight: 600; color: var(--text); margin: 0 0 0.5rem; } | |
| .empty-state p { font-size: 0.875rem; margin: 0; line-height: 1.6; } | |
| .empty-state ol { text-align: left; display: inline-block; margin: 1rem 0 0; font-size: 0.875rem; line-height: 2; } | |
| /* ── Sidebar ── */ | |
| section[data-testid="stSidebar"] { background: var(--surface) !important; border-right: 1px solid var(--border) !important; } | |
| section[data-testid="stSidebar"] .sidebar-section-title { | |
| font-size: 0.68rem; font-weight: 700; letter-spacing: 1.2px; text-transform: uppercase; | |
| color: var(--text-faint); margin: 1.25rem 0 0.5rem; padding-bottom: 0.4rem; border-bottom: 1px solid var(--border); | |
| } | |
| /* ── Tabs ── */ | |
| .stTabs [data-baseweb="tab-list"] { gap: 0.25rem; background: var(--surface-3) !important; border-radius: var(--radius-sm); padding: 0.25rem; } | |
| .stTabs [data-baseweb="tab"] { border-radius: 6px !important; font-weight: 500; font-size: 0.85rem; | |
| color: var(--text-muted) !important; padding: 0.45rem 0.9rem !important; transition: background .15s ease, color .15s ease; } | |
| .stTabs [aria-selected="true"] { background: var(--surface) !important; color: var(--text) !important; box-shadow: 0 1px 4px rgba(0,0,0,0.08); } | |
| .stSelectbox label, .stMultiSelect label { font-size: 0.8rem; color: var(--text-muted) !important; } | |
| [data-testid="stExpander"] summary { font-size: 0.85rem; font-weight: 500; } | |
| .mono { font-family: 'DM Mono', ui-monospace, monospace; } | |
| ::-webkit-scrollbar { width: 5px; height: 5px; } | |
| ::-webkit-scrollbar-track { background: var(--surface-3); } | |
| ::-webkit-scrollbar-thumb { background: rgba(0,0,0,0.18); border-radius: 99px; } | |
| </style> | |
| """ | |
| st.markdown(CUSTOM_CSS, unsafe_allow_html=True) | |
| # ══════════════════════════════════════════════════════════════════════════════ | |
| # 工具函式 | |
| # ══════════════════════════════════════════════════════════════════════════════ | |
| def fmt_yi(value) -> str: | |
| try: return f"{float(value):,.2f}" | |
| except: return "–" | |
| def fmt_pct(value) -> str: | |
| try: return f"{float(value):.1f}%" | |
| except: return "–" | |
| def read_local_samples() -> List[Tuple[str, bytes]]: | |
| if not SAMPLES_DIR.exists(): return [] | |
| return [(p.name, p.read_bytes()) for p in sorted(SAMPLES_DIR.glob("*.xml"))] | |
| def process_inputs(file_tuples: List[Tuple[str, bytes]]): | |
| results = [] | |
| for filename, content in file_tuples: | |
| try: | |
| parsed = parse_xml_to_df(content, filename=filename) | |
| parsed["filename"] = filename | |
| results.append(parsed) | |
| except Exception as e: | |
| results.append({"type": "UNKNOWN", "month": "00", "program_id": "", | |
| "data": pd.DataFrame(), "warnings": [], "errors": [str(e)], "filename": filename}) | |
| return results | |
| def to_file_tuples(uploaded_files) -> List[Tuple[str, bytes]]: | |
| return [(f.name, f.getvalue()) for f in uploaded_files] | |
| def build_upload_summary(parsed_results: List[dict]) -> dict: | |
| s = {"total": len(parsed_results), "ok": 0, "warning": 0, "error": 0} | |
| for item in parsed_results: | |
| if item.get("errors"): s["error"] += 1 | |
| elif item.get("warnings"): s["warning"] += 1; s["ok"] += 1 | |
| else: s["ok"] += 1 | |
| return s | |
| def select_unique_record(records: List[dict], label: str, key: str): | |
| if not records: return None | |
| options = {f"{item.get('month','00')} · {item.get('filename')}": item | |
| for item in sorted(records, key=lambda x: (x.get("month","00"), x.get("filename","")))} | |
| selected = st.selectbox(label, list(options.keys()), index=max(0, len(options)-1), key=key) | |
| return options[selected] | |
| def _get_val(df: pd.DataFrame, code: str, col: str, exact: bool = True) -> float: | |
| if df.empty or col not in df.columns or "科目代碼" not in df.columns: return 0.0 | |
| temp = df.copy(); temp["科目代碼"] = temp["科目代碼"].astype(str) | |
| mask = temp["科目代碼"] == str(code) if exact else temp["科目代碼"].str.startswith(str(code)) | |
| return to_yi(temp.loc[mask, col]).sum() if mask.any() else 0.0 | |
| def summarize_2060(df: pd.DataFrame) -> Dict[str, float]: | |
| empty = {k: 0.0 for k in ["source_execution_rate", "uses_execution_rate", | |
| "source_execution_rate_ytd", "uses_execution_rate_ytd", | |
| "surplus_month", "surplus_ytd", "ending_balance", "variance_ytd"]} | |
| if df.empty: return empty | |
| def gv(code, col, exact=True): | |
| return _get_val(df, code, col, exact) or _get_val(df, code, col, not exact) | |
| s_b = gv("4", "本年度法定預算數"); s_a = gv("4", "本年度截至本月份實際數") | |
| u_b = gv("5", "本年度法定預算數"); u_a = gv("5", "本年度截至本月份實際數") | |
| s_by = gv("4", "本年度截至本月份預算數"); u_by = gv("5", "本年度截至本月份預算數") | |
| return { | |
| "source_execution_rate": s_a / s_b * 100 if s_b else 0.0, | |
| "uses_execution_rate": u_a / u_b * 100 if u_b else 0.0, | |
| "source_execution_rate_ytd": s_a / s_by * 100 if s_by else 0.0, | |
| "uses_execution_rate_ytd": u_a / u_by * 100 if u_by else 0.0, | |
| "surplus_month": gv("6", "本月份實際數"), | |
| "surplus_ytd": gv("6", "本年度截至本月份實際數"), | |
| "ending_balance": gv("73", "本年度截至本月份實際數"), | |
| "variance_ytd": s_a - s_by if s_by else 0.0, | |
| } | |
| def summarize_2080(df: pd.DataFrame) -> Dict[str, float]: | |
| if df.empty or "金額" not in df.columns or "科目代碼" not in df.columns: | |
| return {"assets": 0.0, "liabilities": 0.0, "equity": 0.0} | |
| temp = df.copy(); temp["科目代碼"] = temp["科目代碼"].astype(str) | |
| return { | |
| "assets": to_yi(temp.loc[temp["科目代碼"].str.startswith("1"), "金額"]).sum(), | |
| "liabilities": to_yi(temp.loc[temp["科目代碼"].str.startswith("2"), "金額"]).sum(), | |
| "equity": to_yi(temp.loc[temp["科目代碼"].str.startswith("3"), "金額"]).sum(), | |
| } | |
| def build_trend_map(data_2060: List[dict]): | |
| if not data_2060: return {}, [] | |
| months = sorted({d["month"] for d in data_2060}) | |
| rows = [] | |
| for d in data_2060: | |
| df = d["data"].copy() | |
| if "科目代碼" not in df.columns: continue | |
| if "本月份實際數" not in df.columns: df["本月份實際數"] = 0 | |
| temp = df[["科目代碼", "本月份實際數"]].copy() | |
| temp["月份"] = d["month"]; temp["本月份實際數"] = to_yi(temp["本月份實際數"]) | |
| rows.append(temp) | |
| if not rows: return {}, months | |
| combined = pd.concat(rows, ignore_index=True) | |
| pivot = combined.pivot_table(index="科目代碼", columns="月份", values="本月份實際數", aggfunc="sum").reindex(columns=months).fillna(0) | |
| return {str(idx): row.tolist() for idx, row in pivot.iterrows()}, months | |
| def build_monthly_summary(data_2060: List[dict]) -> pd.DataFrame: | |
| rows = [] | |
| for item in sorted(data_2060, key=lambda x: x["month"]): | |
| s = summarize_2060(item["data"]) | |
| rows.append({"月份": item["month"], | |
| "來源累計執行率(%)": round(s["source_execution_rate"], 2), | |
| "用途累計執行率(%)": round(s["uses_execution_rate"], 2), | |
| "本月賸餘(短絀)": round(s["surplus_month"], 2), | |
| "累計賸餘(短絀)": round(s["surplus_ytd"], 2), | |
| "期末餘額": round(s["ending_balance"], 2)}) | |
| return pd.DataFrame(rows) | |
| def build_alerts_special(kpi: Dict[str, float]) -> List[Tuple[str, str]]: | |
| alerts = [] | |
| u = kpi.get("uses_execution_rate", 0); v = kpi.get("variance_ytd", 0); s = kpi.get("surplus_ytd", 0) | |
| if u < 70: alerts.append(("⚠ 用途執行率偏低，建議檢視進度或發包節點。", "bad")) | |
| elif u < 95: alerts.append(("◐ 用途執行率略低於目標，可追蹤落後科目。", "warn")) | |
| elif u <= 110: alerts.append(("✓ 用途執行率接近目標區間，整體表現穩健。", "good")) | |
| else: alerts.append(("↑ 用途執行率高於 110%，請留意超額執行。", "warn")) | |
| if v >= 0: alerts.append((f"+ 累計差異 +{v:.2f} 億，高於累計預算。", "warn" if v > 0.2 else "good")) | |
| else: alerts.append((f"– 累計差異 {v:.2f} 億，低於累計預算。", "bad" if v < -0.2 else "warn")) | |
| if s >= 0: alerts.append(("✓ 本年累計呈賸餘，期末資金緩衝相對充足。", "good")) | |
| else: alerts.append(("✗ 本年累計為短絀，建議檢視支出節奏與資金調度。", "bad")) | |
| return alerts | |
| def build_alerts_nba(kpi: dict) -> List[Tuple[str, str]]: | |
| alerts = [] | |
| ir = kpi.get("income_biz_rate", 0); er = kpi.get("expense_biz_rate", 0) | |
| sn = kpi.get("surplus_net", 0) | |
| if ir >= 100: alerts.append((f"✓ 業務收入達成率 {ir:.1f}%，超越全年預算。", "good")) | |
| elif ir >= 85: alerts.append((f"◐ 業務收入達成率 {ir:.1f}%，接近目標。", "warn")) | |
| else: alerts.append((f"⚠ 業務收入達成率 {ir:.1f}%，偏低。", "bad")) | |
| if er > 105: alerts.append((f"↑ 業務成本費用執行率 {er:.1f}%，超出預算，請留意。", "warn")) | |
| elif er >= 85: alerts.append((f"◑ 業務成本費用執行率 {er:.1f}%，進度正常。", "good")) | |
| else: alerts.append((f"↓ 業務成本費用執行率 {er:.1f}%，尚有空間。", "info")) | |
| if sn >= 0: alerts.append((f"✓ 本期賸餘 {sn:.2f} 億元，財務表現正向。", "good")) | |
| else: alerts.append((f"✗ 本期短絀 {abs(sn):.2f} 億元，建議檢視費控。", "bad")) | |
| return alerts | |
| # ══════════════════════════════════════════════════════════════════════════════ | |
| # UI 元件 | |
| # ══════════════════════════════════════════════════════════════════════════════ | |
| def render_alerts(alerts: List[Tuple[str, str]]): | |
| pills = [] | |
| for text, level in alerts: | |
| css = {"good": "alert-good", "warn": "alert-warn", "bad": "alert-bad"}.get(level, "alert-info") | |
| pills.append(f'<span class="alert-pill {css}">{text}</span>') | |
| st.markdown(f'<div class="alerts-row">{"".join(pills)}</div>', unsafe_allow_html=True) | |
| def render_kpi_card(label, value, unit="", delta_text="", delta_color="var(--text-muted)", | |
| accent_color="transparent", value_class=""): | |
| st.markdown( | |
| f"""<div class="kpi-card" style="--kpi-accent:{accent_color}"> | |
| <div class="kpi-label">{label}</div> | |
| <div> | |
| <span class="kpi-value {value_class}">{value}</span> | |
| {f'<span style="font-size:.8rem;color:var(--text-faint);margin-left:.3rem">{unit}</span>' if unit else ''} | |
| </div> | |
| <div class="kpi-delta" style="color:{delta_color}">{delta_text}</div> | |
| </div>""", unsafe_allow_html=True) | |
| def render_dataframe_with_sparkline(df: pd.DataFrame, trend_map: Dict[str, List[float]]): | |
| display = df.copy() | |
| if "科目代碼" in display.columns: | |
| display["科目代碼"] = display["科目代碼"].astype(str) | |
| display = display.sort_values("科目代碼", ascending=True, kind="stable") | |
| rename_map = {} | |
| for col in ["本年度法定預算數", "本月份實際數", "本月份預算數", | |
| "本年度截至本月份實際數", "本年度截至本月份預算數"]: | |
| if col in display.columns: | |
| display[col] = to_yi(display[col]) | |
| rename_map[col] = f"{col} (億元)" | |
| if rename_map: display = display.rename(columns=rename_map) | |
| ac = "本年度截至本月份實際數 (億元)"; bc = "本年度法定預算數 (億元)"; bcy = "本年度截至本月份預算數 (億元)" | |
| if ac in display.columns and bc in display.columns: | |
| display["執行率(%)"] = display.apply(lambda r: round(r[ac]/r[bc]*100, 1) if r[bc] else 0, axis=1) | |
| if ac in display.columns and bcy in display.columns: | |
| display["累計差異(億元)"] = (display[ac] - display[bcy]).round(2) | |
| if "科目代碼" in display.columns: | |
| display["跨月趨勢"] = display["科目代碼"].astype(str).map(lambda k: trend_map.get(str(k), [])) | |
| preferred = [c for c in ["科目代碼", "項目", "科目", bc, "本月份實際數 (億元)", ac, bcy, | |
| "執行率(%)", "累計差異(億元)", "跨月趨勢"] if c in display.columns] | |
| if preferred: display = display[preferred].copy() | |
| col_cfg = {} | |
| if "執行率(%)" in display.columns: | |
| col_cfg["執行率(%)"] = st.column_config.ProgressColumn("執行率(%)", min_value=0, max_value=150, format="%.1f%%") | |
| if "跨月趨勢" in display.columns: | |
| col_cfg["跨月趨勢"] = st.column_config.BarChartColumn("跨月趨勢", help="同一科目跨月份本月實際數（億元）") | |
| st.dataframe(display, use_container_width=True, column_config=col_cfg) | |
| def _rate_color(r: float) -> str: | |
| if r >= 100: return POSITIVE | |
| if r >= 80: return WARN | |
| return NEGATIVE | |
| # ══════════════════════════════════════════════════════════════════════════════ | |
| # Sidebar | |
| # ══════════════════════════════════════════════════════════════════════════════ | |
| with st.sidebar: | |
| st.markdown('<div class="sidebar-section-title">資料來源</div>', unsafe_allow_html=True) | |
| uploaded_files = st.file_uploader("上傳 XML 檔案（可多檔）", type=["xml"], accept_multiple_files=True, | |
| help="支援 FNGFRB2060 / FNGFRB2080 / NBAWIR070 / FNWFRB2020 格式") | |
| use_bundled_samples = st.toggle("未上傳時使用內建範例", value=True) | |
| with st.expander("⚙️ 檢視選項", expanded=False): | |
| show_tables = st.checkbox("顯示明細資料表（含 sparkline）", value=False) | |
| show_diagnostics = st.checkbox("顯示診斷 / 驗證訊息", value=True) | |
| with st.expander("🛠️ 工具", expanded=False): | |
| st.markdown("**格式轉換**") | |
| conv_mode = st.radio("轉換模式", ["doc → docx", "xls → xlsx", "xls/xlsx → ods"], label_visibility="collapsed") | |
| accepted = {"doc → docx": ["doc"], "xls → xlsx": ["xls"], "xls/xlsx → ods": ["xls","xlsx"]}[conv_mode] | |
| conv_file = st.file_uploader("選擇欲轉換的檔案", type=accepted, key="conv_up") | |
| if conv_file and st.button("🚀 開始轉換", use_container_width=True): | |
| try: | |
| with st.spinner("轉換中…"): | |
| raw = conv_file.read(); ext = conv_file.name.rsplit(".",1)[-1].lower() | |
| if "doc" in conv_mode: | |
| out_bytes = convert_doc_to_docx(raw); out_name = conv_file.name.replace(".doc",".docx") | |
| mime = "application/vnd.openxmlformats-officedocument.wordprocessingml.document" | |
| elif "ods" in conv_mode: | |
| out_bytes = convert_excel_to_ods(raw, ext); out_name = conv_file.name.replace(f".{ext}",".ods") | |
| mime = "application/vnd.oasis.opendocument.spreadsheet" | |
| else: | |
| out_bytes = convert_excel_to_xlsx(raw, ext); out_name = conv_file.name.replace(".xls",".xlsx") | |
| mime = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" | |
| st.success(f"完成！{out_name}") | |
| st.download_button("📥 下載", data=out_bytes, file_name=out_name, mime=mime) | |
| except Exception as e: | |
| st.error(f"轉換失敗：{e}") | |
| st.divider() | |
| st.markdown("**範例 XML 下載**") | |
| for name, content in read_local_samples(): | |
| st.download_button(label=name, data=content, file_name=name, mime="application/xml", key=f"dl_{name}") | |
| # ══════════════════════════════════════════════════════════════════════════════ | |
| # 資料載入 | |
| # ══════════════════════════════════════════════════════════════════════════════ | |
| input_tuples = to_file_tuples(uploaded_files) if uploaded_files else [] | |
| if not input_tuples and use_bundled_samples: | |
| input_tuples = read_local_samples() | |
| if not input_tuples: | |
| st.markdown("""<div class="hero"> | |
| <div class="hero-title">🏥 基金 KPI Dashboard</div> | |
| <p class="hero-sub">支援特別收入基金與作業基金，即時掌握收支執行、財務結構與固定資產進度。</p> | |
| </div> | |
| <div class="empty-state"> | |
| <div class="icon">📂</div> | |
| <h3>尚未載入任何資料</h3> | |
| <p>請透過以下任一方式開始分析：</p> | |
| <ol> | |
| <li>由左側上傳 XML 檔案（2060、2080、NBA收支餘絀、固定資產）</li> | |
| <li>開啟左側「未上傳時使用內建範例」開關</li> | |
| </ol> | |
| </div>""", unsafe_allow_html=True) | |
| st.stop() | |
| with st.spinner("解析中…"): | |
| all_parsed = process_inputs(input_tuples) | |
| summary = build_upload_summary(all_parsed) | |
| # ── 分類 ───────────────────────────────────────────────────────────────────── | |
| # 特別收入基金 | |
| data_2060 = [d for d in all_parsed if d.get("type") == "2060" and not d["data"].empty] | |
| data_2080 = [d for d in all_parsed if d.get("type") == "2080" and not d["data"].empty] | |
| # 作業基金 | |
| data_nba = [d for d in all_parsed if d.get("type") == "NBA" and not d["data"].empty] | |
| data_massets = [d for d in all_parsed if d.get("type") == "MASSETS" and not d["data"].empty] | |
| # 作業基金平衡表（BS_OPR）與特別收入基金平衡表結構相同，合併進 data_2080 顯示 | |
| data_bs_opr = [d for d in all_parsed if d.get("type") == "BS_OPR" and not d["data"].empty] | |
| data_2080_all = data_2080 + data_bs_opr # 統一用這個做平衡表選擇器 | |
| has_special = bool(data_2060 or data_2080) | |
| has_operating = bool(data_nba or data_massets or data_bs_opr) | |
| trend_map, _ = build_trend_map(data_2060) | |
| monthly_summary = build_monthly_summary(data_2060) | |
| # ── Hero ───────────────────────────────────────────────────────────────────── | |
| ok_html = f'<span class="hero-stat"><span style="color:var(--accent-green)">●</span> <strong>{summary["ok"]}</strong> 成功</span>' | |
| warn_html = f'<span class="hero-stat"><span style="color:var(--accent-amber)">●</span> <strong>{summary["warning"]}</strong> 警告</span>' | |
| err_html = f'<span class="hero-stat"><span style="color:var(--accent-red)">●</span> <strong>{summary["error"]}</strong> 錯誤</span>' | |
| badge_special = '<span class="fund-badge special">特別收入基金</span>' if has_special else "" | |
| badge_oper = '<span class="fund-badge operating">作業基金</span>' if has_operating else "" | |
| st.markdown(f"""<div class="hero"> | |
| <div class="hero-title">🏥 基金 KPI Dashboard {badge_special}{badge_oper}</div> | |
| <p class="hero-sub">共解析 <strong>{summary['total']}</strong> 份檔案 {ok_html}{warn_html}{err_html}</p> | |
| </div>""", unsafe_allow_html=True) | |
| # ── 篩選列 ─────────────────────────────────────────────────────────────────── | |
| fc1, fc2, fc3, fc4, fc5 = st.columns([1.1, 1.1, 1.1, 1.1, 0.5]) | |
| with fc1: | |
| selected_2060 = select_unique_record(data_2060, "2060 來源用途餘絀表", "sel_2060") if data_2060 else None | |
| with fc2: | |
| selected_2080 = select_unique_record(data_2080_all, "平衡表", "sel_2080") if data_2080_all else None | |
| with fc3: | |
| selected_nba = select_unique_record(data_nba, "NBA 收支餘絀表", "sel_nba") if data_nba else None | |
| with fc4: | |
| selected_massets = select_unique_record(data_massets, "固定資產執行情形表", "sel_massets") if data_massets else None | |
| with fc5: | |
| cur_month = next((d.get("month","–") for d in [selected_2060, selected_nba, selected_massets, selected_2080] if d), "–") | |
| st.markdown(f'<div class="month-badge"><div class="label">月份</div><div class="value">{cur_month}</div><div class="note">本次選擇</div></div>', unsafe_allow_html=True) | |
| # ── 診斷訊息 ───────────────────────────────────────────────────────────────── | |
| if show_diagnostics: | |
| with st.expander("🔎 診斷 / 驗證訊息", expanded=False): | |
| for item in all_parsed: | |
| icon = "✅" if not item.get("errors") else "❌" | |
| st.markdown(f"**{icon} {item.get('filename','(未命名)')}** — 類型：{item.get('type')} · 月份：{item.get('month')}") | |
| for w in item.get("warnings", []): st.warning(w) | |
| for e in item.get("errors", []): st.error(e) | |
| if not any([selected_2060, selected_2080, selected_nba, selected_massets]): | |
| st.markdown('<div class="empty-state"><div class="icon">⚠️</div><h3>可解析資料不足</h3><p>請確認 XML 格式正確。</p></div>', unsafe_allow_html=True) | |
| st.stop() | |
| # ══════════════════════════════════════════════════════════════════════════════ | |
| # KPI 摘要列 | |
| # ══════════════════════════════════════════════════════════════════════════════ | |
| kpi_sp = summarize_2060(selected_2060["data"]) if selected_2060 else {k:0.0 for k in ["source_execution_rate","uses_execution_rate","source_execution_rate_ytd","uses_execution_rate_ytd","surplus_month","surplus_ytd","ending_balance","variance_ytd"]} | |
| kpi_nba = summarize_nba(selected_nba["data"]) if selected_nba else {k:0.0 for k in ["income_biz_budget","income_biz_actual","income_biz_rate","expense_biz_budget","expense_biz_actual","expense_biz_rate","income_other_actual","expense_other_actual","surplus_biz","surplus_net"]} | |
| kpi_fa = summarize_fixed_assets(selected_massets["data"]) if selected_massets else {k:0.0 for k in ["budget_total","carryover","grand_total","actual_total","unpaid","exec_rate","project_count"]} | |
| bal = summarize_2080(selected_2080["data"]) if selected_2080 else {"assets":0.0,"liabilities":0.0,"equity":0.0} | |
| # 警示列 | |
| if selected_2060: render_alerts(build_alerts_special(kpi_sp)) | |
| if selected_nba: render_alerts(build_alerts_nba(kpi_nba)) | |
| # ══════════════════════════════════════════════════════════════════════════════ | |
| # 主 Tabs | |
| # ══════════════════════════════════════════════════════════════════════════════ | |
| tabs_labels = ["🏠 高管首頁"] | |
| if has_special: tabs_labels += ["📊 來源用途餘絀", "📈 跨月趨勢"] | |
| if has_operating: tabs_labels += ["🏥 作業基金收支", "🏗️ 固定資產"] | |
| if data_2080_all: tabs_labels += ["🗂️ 平衡表"] | |
| if len(data_2080_all) >= 1: tabs_labels += ["💰 現金流量推估"] | |
| tabs_labels += ["🧾 明細資料"] | |
| tabs = st.tabs(tabs_labels) | |
| tab_map = {name: tab for name, tab in zip(tabs_labels, tabs)} | |
| # ─────────────────────────────────────────────────────────────────────────── | |
| # ① 高管首頁 | |
| # ─────────────────────────────────────────────────────────────────────────── | |
| with tab_map["🏠 高管首頁"]: | |
| # ── 特別收入基金 KPI ── | |
| if has_special: | |
| st.markdown('<div class="row-label">特別收入基金 — 執行率</div>', unsafe_allow_html=True) | |
| c1, c2, c3, c4 = st.columns(4) | |
| src_rate = kpi_sp["source_execution_rate"]; use_rate = kpi_sp["uses_execution_rate"] | |
| src_ytd = kpi_sp["source_execution_rate_ytd"]; use_ytd = kpi_sp["uses_execution_rate_ytd"] | |
| with c1: render_kpi_card("基金來源執行率", fmt_pct(src_rate), "／全年", f"{'↑' if src_rate>=100 else '↓'} 對全年法定", SOURCE_MAIN, SOURCE_MAIN) | |
| with c2: render_kpi_card("基金用途執行率", fmt_pct(use_rate), "／全年", f"{'↑' if use_rate>=100 else '↓'} 對全年法定", USES_MAIN, USES_MAIN) | |
| with c3: render_kpi_card("來源執行率（累計）", fmt_pct(src_ytd), "／累計", "對應累計分配", SOURCE_MAIN, SOURCE_MAIN) | |
| with c4: render_kpi_card("用途執行率（累計）", fmt_pct(use_ytd), "／累計", "對應累計分配", USES_MAIN, USES_MAIN) | |
| st.markdown('<div class="row-label" style="margin-top:.6rem">特別收入基金 — 金額</div>', unsafe_allow_html=True) | |
| c5, c6, c7, c8 = st.columns(4) | |
| sm = kpi_sp["surplus_month"]; sy = kpi_sp["surplus_ytd"]; va = kpi_sp["variance_ytd"] | |
| a, li, eq = bal["assets"], bal["liabilities"], bal["equity"] | |
| with c5: render_kpi_card("本月賸餘(短絀)", fmt_yi(sm), "億元", "正值=賸餘", POSITIVE if sm>=0 else NEGATIVE, POSITIVE if sm>=0 else NEGATIVE) | |
| with c6: render_kpi_card("累計賸餘(短絀)", fmt_yi(sy), "億元", f"期末餘額 {fmt_yi(kpi_sp['ending_balance'])} 億", POSITIVE if sy>=0 else NEGATIVE, POSITIVE if sy>=0 else NEGATIVE) | |
| with c7: render_kpi_card("累計差異（實－預）", fmt_yi(va), "億元", "正值超累計預算", POSITIVE if va>=0 else NEGATIVE, INFO) | |
| with c8: render_kpi_card("資產/負債/淨資產", f"{a:.1f}/{li:.1f}/{eq:.1f}", "億元", "資產=負債+淨資產" if abs(a-li-eq)<0.1 else "⚠ 不平衡", NEUTRAL, "#7B1FA2", "secondary") | |
| # ── 作業基金 KPI ── | |
| if has_operating: | |
| st.markdown('<div class="row-label" style="margin-top:.8rem">作業基金 — 收支概況</div>', unsafe_allow_html=True) | |
| o1, o2, o3, o4, o5, o6 = st.columns(6) | |
| ir = kpi_nba["income_biz_rate"]; er = kpi_nba["expense_biz_rate"] | |
| with o1: render_kpi_card("業務收入法定預算", fmt_yi(kpi_nba["income_biz_budget"]), "億元", "", NEUTRAL, SOURCE_MAIN) | |
| with o2: render_kpi_card("業務收入累計實際", fmt_yi(kpi_nba["income_biz_actual"]), "億元", f"達成率 {ir:.1f}%", _rate_color(ir), SOURCE_MAIN) | |
| with o3: render_kpi_card("業務成本費用", fmt_yi(kpi_nba["expense_biz_actual"]), "億元", f"執行率 {er:.1f}%", _rate_color(er), USES_MAIN) | |
| with o4: render_kpi_card("業務外淨收入", fmt_yi(kpi_nba["income_other_actual"]-kpi_nba["expense_other_actual"]), "億元", "業務外收入－費用", POSITIVE if (kpi_nba["income_other_actual"]-kpi_nba["expense_other_actual"])>=0 else NEGATIVE, INFO) | |
| with o5: render_kpi_card("本期賸餘(短絀)", fmt_yi(kpi_nba["surplus_net"]), "億元", "", POSITIVE if kpi_nba["surplus_net"]>=0 else NEGATIVE, POSITIVE if kpi_nba["surplus_net"]>=0 else NEGATIVE) | |
| with o6: | |
| render_kpi_card("固定資產執行率", fmt_pct(kpi_fa["exec_rate"]), f"共 {kpi_fa['project_count']} 項計畫", | |
| f"合計預算 {fmt_yi(kpi_fa['grand_total'])} 億", _rate_color(kpi_fa["exec_rate"]), _rate_color(kpi_fa["exec_rate"])) | |
| # ── 圖表區 ── | |
| left, right = st.columns([1.15, 0.85]) | |
| with left: | |
| st.markdown('<div class="section-card"><div class="card-title">整體健康度</div><div class="card-sub">執行進度儀錶與資金流向。</div>', unsafe_allow_html=True) | |
| if selected_2060: | |
| g1, g2 = st.columns([1, 1.25]) | |
| with g1: | |
| st.plotly_chart(plot_execution_gauge(src_rate, use_rate, "佔全年法定預算 (%)"), use_container_width=True, key="home_g1") | |
| st.plotly_chart(plot_execution_gauge(src_ytd, use_ytd, "佔累計分配預算 (%)", "來源累計", "用途累計"), use_container_width=True, key="home_g2") | |
| with g2: | |
| st.plotly_chart(plot_sankey(selected_2060["data"], "本年度截至本月份實際數", "特別收入基金資金流向"), use_container_width=True, key="home_sankey") | |
| elif selected_nba: | |
| st.plotly_chart(plot_surplus_waterfall(selected_nba["data"]), use_container_width=True, key="home_wf") | |
| else: | |
| st.markdown('<div class="empty-state"><p>尚無資料</p></div>', unsafe_allow_html=True) | |
| st.markdown("</div>", unsafe_allow_html=True) | |
| if selected_2060: | |
| st.markdown('<div class="section-card"><div class="card-title">落差最大科目</div><div class="card-sub">紅色偏低、綠色偏高。</div>', unsafe_allow_html=True) | |
| st.plotly_chart(plot_variance_bar(selected_2060["data"]), use_container_width=True, key="home_var") | |
| st.markdown("</div>", unsafe_allow_html=True) | |
| elif selected_massets: | |
| st.markdown('<div class="section-card"><div class="card-title">固定資產計畫執行率</div><div class="card-sub">各計畫目前執行進度。</div>', unsafe_allow_html=True) | |
| st.plotly_chart(plot_fixed_assets_bar(selected_massets["data"]), use_container_width=True, key="home_fa") | |
| st.markdown("</div>", unsafe_allow_html=True) | |
| with right: | |
| if not monthly_summary.empty: | |
| st.markdown('<div class="section-card"><div class="card-title">近月趨勢</div><div class="card-sub">執行率與賸餘短絀變化。</div>', unsafe_allow_html=True) | |
| st.plotly_chart(plot_kpi_trend(monthly_summary), use_container_width=True, key="home_trend") | |
| st.markdown("</div>", unsafe_allow_html=True) | |
| if selected_2080: | |
| st.markdown('<div class="section-card"><div class="card-title">平衡表快照</div><div class="card-sub">資產 / 負債 / 淨資產結構。</div>', unsafe_allow_html=True) | |
| st.plotly_chart(plot_balance_sheet(selected_2080["data"]), use_container_width=True, key="home_bs") | |
| st.markdown("</div>", unsafe_allow_html=True) | |
| if selected_nba and not selected_2060: | |
| st.markdown('<div class="section-card"><div class="card-title">收支比較</div><div class="card-sub">各科目預算 vs 實際。</div>', unsafe_allow_html=True) | |
| st.plotly_chart(plot_nba_compare(selected_nba["data"]), use_container_width=True, key="home_nba_cmp") | |
| st.markdown("</div>", unsafe_allow_html=True) | |
| # ─────────────────────────────────────────────────────────────────────────── | |
| # ② 來源用途餘絀（特別收入基金） | |
| # ─────────────────────────────────────────────────────────────────────────── | |
| if "📊 來源用途餘絀" in tab_map: | |
| with tab_map["📊 來源用途餘絀"]: | |
| if not selected_2060: | |
| st.markdown('<div class="empty-state"><div class="icon">📄</div><h3>尚未上傳 2060 檔案</h3></div>', unsafe_allow_html=True) | |
| else: | |
| df60 = selected_2060["data"] | |
| df_src = df60[df60["科目代碼"].astype(str).str.startswith("4")].copy() if "科目代碼" in df60.columns else pd.DataFrame() | |
| df_use = df60[df60["科目代碼"].astype(str).str.startswith("5")].copy() if "科目代碼" in df60.columns else pd.DataFrame() | |
| s_b = _get_val(df60,"4","本年度法定預算數") or _get_val(df60,"4","本年度法定預算數",False) | |
| s_a = _get_val(df60,"4","本年度截至本月份實際數") or _get_val(df60,"4","本年度截至本月份實際數",False) | |
| u_b = _get_val(df60,"5","本年度法定預算數") or _get_val(df60,"5","本年度法定預算數",False) | |
| u_a = _get_val(df60,"5","本年度截至本月份實際數") or _get_val(df60,"5","本年度截至本月份實際數",False) | |
| st.markdown('<div class="card-title" style="margin-bottom:1rem;font-size:1.25rem">基金收支執行總覽</div>', unsafe_allow_html=True) | |
| b1,b2,b3,b4 = st.columns(4) | |
| with b1: render_kpi_card("來源法定預算", f"{s_b:,.2f}", "億元", "全年預估收入", NEUTRAL, SOURCE_MAIN) | |
| with b2: render_kpi_card("來源累計實際", f"{s_a:,.2f}", "億元", f"達成率：{s_a/s_b*100 if s_b else 0:.1f}%", SOURCE_MAIN, SOURCE_MAIN) | |
| with b3: render_kpi_card("用途法定預算", f"{u_b:,.2f}", "億元", "全年預估支出", NEUTRAL, USES_MAIN) | |
| with b4: render_kpi_card("用途累計實際", f"{u_a:,.2f}", "億元", f"執行率：{u_a/u_b*100 if u_b else 0:.1f}%", USES_MAIN, USES_MAIN) | |
| st.markdown('<div style="height:1.5rem"></div>', unsafe_allow_html=True) | |
| st.markdown('<div class="section-card"><div class="card-title">收支結構與缺口分析</div><div class="card-sub">對比預算與實際的組成比例，掌握未執行缺口。</div>', unsafe_allow_html=True) | |
| d1,d2 = st.columns(2) | |
| with d1: st.plotly_chart(plot_nested_donut(df_src, "💰 來源結構與佔比 (億元)"), use_container_width=True, key="sp_donut_src") | |
| with d2: st.plotly_chart(plot_nested_donut(df_use, "💸 用途結構與佔比 (億元)"), use_container_width=True, key="sp_donut_use") | |
| st.markdown("</div>", unsafe_allow_html=True) | |
| st.markdown('<div class="section-card"><div class="card-title">各科目執行進度追蹤</div><div class="card-sub">法定預算與累計實際之落差。</div>', unsafe_allow_html=True) | |
| r1,r2 = st.columns(2) | |
| with r1: st.plotly_chart(plot_budget_vs_actual(df_src, "來源科目：預算 vs 實際 (億元)"), use_container_width=True, key="sp_bar_src") | |
| with r2: st.plotly_chart(plot_budget_vs_actual(df_use, "用途科目：預算 vs 實際 (億元)"), use_container_width=True, key="sp_bar_use") | |
| st.markdown("</div>", unsafe_allow_html=True) | |
| st.markdown('<div class="section-card"><div class="card-title">資金流動生態系 (Sankey)</div><div class="card-sub">預算與實際資金路徑對比。</div>', unsafe_allow_html=True) | |
| s1,s2 = st.columns(2) | |
| with s1: st.plotly_chart(plot_sankey(df60, "本年度法定預算數", "【預算視角】預估資金流向 (億元)"), use_container_width=True, key="sp_san1") | |
| with s2: st.plotly_chart(plot_sankey(df60, "本年度截至本月份實際數", "【實際視角】真實資金流向 (億元)"), use_container_width=True, key="sp_san2") | |
| st.markdown("</div>", unsafe_allow_html=True) | |
| # ─────────────────────────────────────────────────────────────────────────── | |
| # ③ 平衡表 | |
| # ─────────────────────────────────────────────────────────────────────────── | |
| if "🗂️ 平衡表" in tab_map: | |
| with tab_map["🗂️ 平衡表"]: | |
| if not selected_2080: | |
| st.markdown('<div class="empty-state"><div class="icon">📄</div><h3>尚未上傳 2080 檔案</h3></div>', unsafe_allow_html=True) | |
| else: | |
| st.markdown('<div class="section-card"><div class="card-title">平衡表階層結構</div><div class="card-sub">Treemap 顯示大類與科目占比；點擊可放大子項目。</div>', unsafe_allow_html=True) | |
| st.plotly_chart(plot_balance_sheet(selected_2080["data"]), use_container_width=True, key="bs_main") | |
| st.markdown("</div>", unsafe_allow_html=True) | |
| a, li, eq = bal["assets"], bal["liabilities"], bal["equity"] | |
| m1,m2,m3 = st.columns(3) | |
| with m1: render_kpi_card("總資產", f"{a:.2f}", "億元", "", INFO, INFO) | |
| with m2: render_kpi_card("總負債", f"{li:.2f}", "億元", "", NEGATIVE, NEGATIVE) | |
| with m3: render_kpi_card("淨資產", f"{eq:.2f}", "億元", "資產−負債" if abs(a-li-eq)<0.1 else "⚠ 差異", POSITIVE, "#7B1FA2") | |
| # ─────────────────────────────────────────────────────────────────────────── | |
| # ④ 跨月趨勢（特別收入基金） | |
| # ─────────────────────────────────────────────────────────────────────────── | |
| if "📈 跨月趨勢" in tab_map: | |
| with tab_map["📈 跨月趨勢"]: | |
| if not data_2060: | |
| st.markdown('<div class="empty-state"><div class="icon">📈</div><h3>尚未上傳任何 2060 檔案</h3></div>', unsafe_allow_html=True) | |
| else: | |
| all_months = sorted({d["month"] for d in data_2060}) | |
| sel_months = st.multiselect("選擇月份區間", all_months, default=all_months[-min(6,len(all_months)):]) | |
| filtered = [d for d in data_2060 if d["month"] in sel_months] if sel_months else data_2060 | |
| src_dict, use_dict = {}, {} | |
| for item in filtered: | |
| df, m = item["data"], item["month"] | |
| if "科目代碼" not in df.columns: continue | |
| s_df = df[df["科目代碼"].astype(str).str.startswith("4")].copy() | |
| u_df = df[df["科目代碼"].astype(str).str.startswith("5")].copy() | |
| if not s_df.empty: src_dict[m] = s_df | |
| if not u_df.empty: use_dict[m] = u_df | |
| st.markdown('<div class="section-card"><div class="card-title">基金來源跨月趨勢</div><div class="card-sub">各來源科目隨月份變化。</div>', unsafe_allow_html=True) | |
| st.plotly_chart(plot_monthly_stacked(src_dict, "來源｜各月本月實際（億元）"), use_container_width=True, key="tr_src") | |
| st.markdown("</div>", unsafe_allow_html=True) | |
| st.markdown('<div class="section-card"><div class="card-title">基金用途跨月趨勢</div><div class="card-sub">快速抓出增加最快或異常波動的用途科目。</div>', unsafe_allow_html=True) | |
| st.plotly_chart(plot_monthly_stacked(use_dict, "用途｜各月本月實際（億元）"), use_container_width=True, key="tr_use") | |
| st.markdown("</div>", unsafe_allow_html=True) | |
| if not monthly_summary.empty: | |
| st.markdown('<div class="section-card"><div class="card-title">KPI 趨勢（全月）</div><div class="card-sub">含 100% 目標參考線。</div>', unsafe_allow_html=True) | |
| st.plotly_chart(plot_kpi_trend(monthly_summary, "全月 KPI 趨勢"), use_container_width=True, key="tr_kpi") | |
| st.markdown("</div>", unsafe_allow_html=True) | |
| # ─────────────────────────────────────────────────────────────────────────── | |
| # ⑤ 作業基金收支 | |
| # ─────────────────────────────────────────────────────────────────────────── | |
| if "🏥 作業基金收支" in tab_map: | |
| with tab_map["🏥 作業基金收支"]: | |
| if not selected_nba: | |
| st.markdown('<div class="empty-state"><div class="icon">🏥</div><h3>尚未上傳收支餘絀表</h3><p>請上傳 NBAWIR070 XML 檔案。</p></div>', unsafe_allow_html=True) | |
| else: | |
| df_nba = selected_nba["data"] | |
| df_nba["科目代碼"] = df_nba["科目代碼"].astype(str) | |
| # KPI 四欄 | |
| st.markdown('<div class="card-title" style="margin-bottom:1rem;font-size:1.25rem">作業基金收支執行總覽</div>', unsafe_allow_html=True) | |
| n1,n2,n3,n4 = st.columns(4) | |
| ib = kpi_nba["income_biz_budget"]; ia = kpi_nba["income_biz_actual"] | |
| eb = kpi_nba["expense_biz_budget"]; ea = kpi_nba["expense_biz_actual"] | |
| sb = kpi_nba["surplus_biz"]; sn = kpi_nba["surplus_net"] | |
| ir = kpi_nba["income_biz_rate"]; er = kpi_nba["expense_biz_rate"] | |
| with n1: render_kpi_card("業務收入法定預算", f"{ib:,.2f}", "億元", "全年預估", NEUTRAL, SOURCE_MAIN) | |
| with n2: render_kpi_card("業務收入累計實際", f"{ia:,.2f}", "億元", f"達成率：{ir:.1f}%", _rate_color(ir), SOURCE_MAIN) | |
| with n3: render_kpi_card("業務成本費用", f"{ea:,.2f}", "億元", f"執行率：{er:.1f}%", _rate_color(er), USES_MAIN) | |
| with n4: render_kpi_card("本期賸餘(短絀)", f"{sn:,.2f}", "億元", f"業務賸餘 {sb:.2f} 億", POSITIVE if sn>=0 else NEGATIVE, POSITIVE if sn>=0 else NEGATIVE) | |
| st.markdown('<div style="height:1.5rem"></div>', unsafe_allow_html=True) | |
| # 瀑布 + Sankey | |
| st.markdown('<div class="section-card"><div class="card-title">收支結構分析</div><div class="card-sub">業務→業務外→本期的收支瀑布，與資金流向。</div>', unsafe_allow_html=True) | |
| wf1, wf2 = st.columns([0.42, 0.58]) | |
| with wf1: st.plotly_chart(plot_surplus_waterfall(df_nba), use_container_width=True, key="nba_wf") | |
| ac_nba = next((c for c in ["本年度截止本月份實際數","本年度截至本月份實際數"] if c in df_nba.columns), "") | |
| with wf2: st.plotly_chart(plot_sankey_nba(df_nba, ac_nba, "收支資金流向（億元）"), use_container_width=True, key="nba_san") | |
| st.markdown("</div>", unsafe_allow_html=True) | |
| # 收入結構圓環 + 支出結構圓環 | |
| df_inc = df_nba[df_nba["科目代碼"].str.startswith("4")].copy() | |
| df_exp = df_nba[df_nba["科目代碼"].str.startswith("5")].copy() | |
| st.markdown('<div class="section-card"><div class="card-title">收支組成佔比</div><div class="card-sub">收入來源與費用結構的預算 vs 實際比例。</div>', unsafe_allow_html=True) | |
| p1, p2 = st.columns(2) | |
| with p1: st.plotly_chart(plot_nested_donut(df_inc, "💰 收入結構與佔比 (億元)"), use_container_width=True, key="nba_donut_inc") | |
| with p2: st.plotly_chart(plot_nested_donut(df_exp, "💸 費用結構與佔比 (億元)"), use_container_width=True, key="nba_donut_exp") | |
| st.markdown("</div>", unsafe_allow_html=True) | |
| # 科目比較長條 | |
| st.markdown('<div class="section-card"><div class="card-title">各科目預算 vs 實際</div><div class="card-sub">LEVEL=2 頂層科目對比。</div>', unsafe_allow_html=True) | |
| st.plotly_chart(plot_nba_compare(df_nba), use_container_width=True, key="nba_cmp") | |
| st.markdown("</div>", unsafe_allow_html=True) | |
| # ─────────────────────────────────────────────────────────────────────────── | |
| # ⑥ 固定資產 | |
| # ─────────────────────────────────────────────────────────────────────────── | |
| if "🏗️ 固定資產" in tab_map: | |
| with tab_map["🏗️ 固定資產"]: | |
| if not selected_massets: | |
| st.markdown('<div class="empty-state"><div class="icon">🏗️</div><h3>尚未上傳固定資產執行情形表</h3><p>請上傳 FNWFRB2020 XML 檔案。</p></div>', unsafe_allow_html=True) | |
| else: | |
| df_fa = selected_massets["data"] | |
| # KPI 五欄 | |
| st.markdown('<div class="card-title" style="margin-bottom:1rem;font-size:1.25rem">固定資產執行總覽</div>', unsafe_allow_html=True) | |
| f1,f2,f3,f4,f5 = st.columns(5) | |
| er_fa = kpi_fa["exec_rate"] | |
| with f1: render_kpi_card("本年度法定預算", fmt_yi(kpi_fa["budget_total"]), "億元", "", NEUTRAL, SOURCE_MAIN) | |
| with f2: render_kpi_card("以前年度保留數", fmt_yi(kpi_fa["carryover"]), "億元", "累積保留執行", NEUTRAL, INFO) | |
| with f3: render_kpi_card("合計可用預算", fmt_yi(kpi_fa["grand_total"]), "億元", "法定＋保留", NEUTRAL, USES_MAIN) | |
| with f4: render_kpi_card("實際執行數", fmt_yi(kpi_fa["actual_total"]), "億元", f"含應付未付 {fmt_yi(kpi_fa['unpaid'])} 億", _rate_color(er_fa), _rate_color(er_fa)) | |
| with f5: render_kpi_card("整體執行率", fmt_pct(er_fa), f"共 {kpi_fa['project_count']} 項計畫", "", _rate_color(er_fa), _rate_color(er_fa)) | |
| st.markdown('<div style="height:1.5rem"></div>', unsafe_allow_html=True) | |
| # 執行率長條 + Treemap | |
| st.markdown('<div class="section-card"><div class="card-title">各計畫執行進度</div><div class="card-sub">綠色≥100%、橙色≥70%、紅色＜70%；右側為預算規模分布。</div>', unsafe_allow_html=True) | |
| fa1, fa2 = st.columns([1.2, 0.8]) | |
| with fa1: st.plotly_chart(plot_fixed_assets_bar(df_fa), use_container_width=True, key="fa_bar") | |
| with fa2: st.plotly_chart(plot_fixed_assets_treemap(df_fa), use_container_width=True, key="fa_tree") | |
| st.markdown("</div>", unsafe_allow_html=True) | |
| # 明細表 | |
| st.markdown('<div class="section-card"><div class="card-title">固定資產明細資料</div><div class="card-sub">含差異或落後原因與改進措施。</div>', unsafe_allow_html=True) | |
| display_fa = df_fa.copy() | |
| for col in ["以前年度保留數","本年度法定預算數","合計","累計預算分配數","實支數","應付未付數","實際執行數-合計","比較增減"]: | |
| if col in display_fa.columns: | |
| display_fa[col] = (pd.to_numeric(display_fa[col], errors="coerce").fillna(0) / 1e8).round(3) | |
| display_fa = display_fa.rename(columns={col: f"{col}(億元)"}) | |
| st.dataframe(display_fa, use_container_width=True) | |
| st.markdown("</div>", unsafe_allow_html=True) | |
| # ─────────────────────────────────────────────────────────────────────────── | |
| # ⑦ 現金流量推估 | |
| # ─────────────────────────────────────────────────────────────────────────── | |
| if "💰 現金流量推估" in tab_map: | |
| with tab_map["💰 現金流量推估"]: | |
| if len(data_2080_all) < 1: | |
| st.markdown( | |
| '<div class="empty-state"><div class="icon">💰</div>' | |
| '<h3>需要至少一份平衡表</h3>' | |
| '<p>上傳期初（年初）與期末（年底）兩份平衡表，即可自動推估現金流量。</p></div>', | |
| unsafe_allow_html=True, | |
| ) | |
| else: | |
| # ── 選擇期初 / 期末平衡表 ────────────────────────────────────── | |
| st.markdown( | |
| '<div class="section-card">' | |
| '<div class="card-title">選擇比較期間</div>' | |
| '<div class="card-sub">' | |
| '選擇兩份平衡表作為期初與期末；若只有一份，系統以零為期初（僅供參考）。' | |
| '</div>', | |
| unsafe_allow_html=True, | |
| ) | |
| bs_options = { | |
| f"{d.get('month','?')} 月 · {d.get('sender', d.get('filename',''))}": d | |
| for d in sorted(data_2080_all, key=lambda x: x.get("month","00")) | |
| } | |
| bs_keys = list(bs_options.keys()) | |
| # ── 收支餘絀表選項（NBA 或 2060）──────────────────────────── | |
| income_stmt_all = data_nba + data_2060 # 合併兩種類型 | |
| income_options = { | |
| f"{d.get('month','?')} 月 · {d.get('sender', d.get('filename',''))} " | |
| f"[{'收支餘絀' if d.get('type')=='NBA' else '來源用途餘絀'}]": d | |
| for d in sorted(income_stmt_all, key=lambda x: x.get("month","00")) | |
| } | |
| income_keys = list(income_options.keys()) | |
| cf1, cf2 = st.columns(2) | |
| with cf1: | |
| key_start = st.selectbox("期初平衡表", bs_keys, | |
| index=0, key="cf_start") | |
| with cf2: | |
| key_end = st.selectbox("期末平衡表", bs_keys, | |
| index=len(bs_keys)-1, key="cf_end") | |
| # 收支餘絀表選擇（獨立一列，更清楚） | |
| st.markdown( | |
| '<div style="background:rgba(25,118,210,0.06);border:1px solid ' | |
| 'rgba(25,118,210,0.2);border-radius:8px;padding:.75rem 1rem;margin:.5rem 0">' | |
| '<span style="font-size:.8rem;font-weight:600;color:var(--source)">📋 本期賸餘來源</span>' | |
| '<span style="font-size:.78rem;color:var(--text-muted);margin-left:.5rem">' | |
| '必須從收支餘絀表取得，不可用平衡表的累積賸餘</span></div>', | |
| unsafe_allow_html=True, | |
| ) | |
| si1, si2 = st.columns([1.5, 1]) | |
| with si1: | |
| if income_keys: | |
| key_income = st.selectbox( | |
| "對應的收支餘絀表", | |
| income_keys, | |
| index=len(income_keys)-1, | |
| key="cf_income", | |
| help="選擇與比較期間對應的收支餘絀表，本期賸餘將自動帶入", | |
| ) | |
| selected_income = income_options[key_income] | |
| # 自動計算本期賸餘 | |
| if selected_income.get("type") == "NBA": | |
| auto_surplus = summarize_nba(selected_income["data"]).get("surplus_net", 0) | |
| else: | |
| auto_surplus = summarize_2060(selected_income["data"]).get("surplus_ytd", 0) | |
| else: | |
| key_income = None | |
| auto_surplus = 0.0 | |
| st.warning("尚未上傳收支餘絀表，請上傳後選擇，或於右側手動輸入本期賸餘。") | |
| with si2: | |
| surplus_input = st.number_input( | |
| "本期賸餘(短絀) 億元", | |
| value=float(round(auto_surplus, 2)), | |
| step=0.01, format="%.2f", key="cf_surplus", | |
| help="從所選收支餘絀表自動帶入，可手動修正", | |
| ) | |
| if income_keys and abs(surplus_input - auto_surplus) > 0.01: | |
| st.caption(f"⚠ 已手動調整（原始值：{auto_surplus:.2f} 億）") | |
| st.markdown("</div>", unsafe_allow_html=True) | |
| df_start = bs_options[key_start]["data"] | |
| df_end = bs_options[key_end]["data"] | |
| # ── 計算現金流量 ──────────────────────────────────────────────── | |
| cf_items = summarize_cashflow(df_start, df_end, surplus=surplus_input) | |
| # 期初 / 期末現金 | |
| cash_start = next((x["amount"] for x in cf_items if x["label"] == "期初現金及約當現金"), 0) | |
| cash_end = next((x["amount"] for x in cf_items if x["label"] == "期末現金及約當現金"), 0) | |
| net_change = next((x["amount"] for x in cf_items if x["label"] == "現金及約當現金淨增減"), 0) | |
| error_val = next((x["amount"] for x in cf_items if "推估誤差" in x["label"]), 0) | |
| # 三活動小計 | |
| op_sub = next((x["amount"] for x in cf_items if x.get("is_subtotal") and x["activity"]=="operating"), 0) | |
| inv_sub = next((x["amount"] for x in cf_items if x.get("is_subtotal") and x["activity"]=="investing"), 0) | |
| fin_sub = next((x["amount"] for x in cf_items if x.get("is_subtotal") and x["activity"]=="financing"), 0) | |
| # ── KPI 卡片 ──────────────────────────────────────────────────── | |
| st.markdown( | |
| '<div class="row-label">現金流量摘要</div>', | |
| unsafe_allow_html=True, | |
| ) | |
| ck1, ck2, ck3, ck4, ck5, ck6 = st.columns(6) | |
| with ck1: | |
| render_kpi_card("期初現金", fmt_yi(cash_start), "億元", "", NEUTRAL, SOURCE_MAIN) | |
| with ck2: | |
| render_kpi_card("期末現金", fmt_yi(cash_end), "億元", "", NEUTRAL, SOURCE_MAIN) | |
| with ck3: | |
| render_kpi_card("一、營業活動", fmt_yi(op_sub), "億元", "", | |
| POSITIVE if op_sub >= 0 else NEGATIVE, | |
| POSITIVE if op_sub >= 0 else NEGATIVE) | |
| with ck4: | |
| render_kpi_card("二、投資活動", fmt_yi(inv_sub), "億元", "", | |
| POSITIVE if inv_sub >= 0 else NEGATIVE, | |
| POSITIVE if inv_sub >= 0 else NEGATIVE) | |
| with ck5: | |
| render_kpi_card("三、籌資活動", fmt_yi(fin_sub), "億元", "", | |
| POSITIVE if fin_sub >= 0 else NEGATIVE, | |
| POSITIVE if fin_sub >= 0 else NEGATIVE) | |
| with ck6: | |
| # 排除科目的增減合計（揭露用） | |
| excl_total = sum( | |
| x["amount"] for x in cf_items | |
| if x.get("is_excluded") and not x["label"].startswith("──") | |
| ) | |
| err_color = POSITIVE if abs(error_val) < 0.5 else (WARN if abs(error_val) < 3 else NEGATIVE) | |
| render_kpi_card( | |
| "推估誤差", | |
| f"{error_val:+.2f}", | |
| "億元", | |
| f"排除科目增減 {excl_total:+.2f} 億（非現金）" if abs(excl_total) > 0.01 | |
| else ("✓ 推估合理" if abs(error_val) < 0.5 else "⚠ 誤差偏大，請檢視"), | |
| err_color, err_color, | |
| ) | |
| # ── 綜覽瀑布圖 + 平衡表差異圖 ──────────────────────────────────── | |
| st.markdown( | |
| '<div class="section-card">' | |
| '<div class="card-title">現金流量概覽</div>' | |
| '<div class="card-sub">' | |
| '左：三大活動小計瀑布圖；右：平衡表各科目增減（期末－期初）。' | |
| '</div>', | |
| unsafe_allow_html=True, | |
| ) | |
| wc1, wc2 = st.columns([1, 1.3]) | |
| with wc1: | |
| st.plotly_chart( | |
| plot_cashflow_waterfall(cf_items), | |
| use_container_width=True, key="cf_wf", | |
| ) | |
| with wc2: | |
| st.plotly_chart( | |
| plot_cashflow_bs_diff(df_start, df_end), | |
| use_container_width=True, key="cf_bs_diff", | |
| ) | |
| st.markdown("</div>", unsafe_allow_html=True) | |
| # ── 三活動細項 ────────────────────────────────────────────────── | |
| st.markdown( | |
| '<div class="section-card">' | |
| '<div class="card-title">各活動細項明細</div>' | |
| '<div class="card-sub">正值＝現金流入（綠），負值＝現金流出（紅）。</div>', | |
| unsafe_allow_html=True, | |
| ) | |
| d1, d2, d3 = st.columns(3) | |
| with d1: | |
| st.plotly_chart( | |
| plot_cashflow_detail(cf_items, "operating", "一、營業活動"), | |
| use_container_width=True, key="cf_op", | |
| ) | |
| with d2: | |
| st.plotly_chart( | |
| plot_cashflow_detail(cf_items, "investing", "二、投資活動"), | |
| use_container_width=True, key="cf_inv", | |
| ) | |
| with d3: | |
| st.plotly_chart( | |
| plot_cashflow_detail(cf_items, "financing", "三、籌資活動"), | |
| use_container_width=True, key="cf_fin", | |
| ) | |
| st.markdown("</div>", unsafe_allow_html=True) | |
| # ── 明細表格 ──────────────────────────────────────────────────── | |
| st.markdown( | |
| '<div class="section-card">' | |
| '<div class="card-title">現金流量推估明細表</div>' | |
| '<div class="card-sub">' | |
| '依間接法編製；以平衡表科目增減推算，本期賸餘為調整起點。' | |
| '⚠ 本表為推估性質，非正式財務報表，誤差來源主要為非現金調整項目不完整。' | |
| '</div>', | |
| unsafe_allow_html=True, | |
| ) | |
| cf_display = [] | |
| act_label = "" | |
| for x in cf_items: | |
| if "推估誤差" in x["label"]: | |
| continue | |
| act = x["activity"] | |
| # 加活動標題列 | |
| new_act_label = { | |
| "operating": "一、營業活動", | |
| "investing": "二、投資活動", | |
| "financing": "三、籌資活動", | |
| "total": "四、現金及約當現金", | |
| "check": "", | |
| }.get(act, "") | |
| if new_act_label and new_act_label != act_label: | |
| act_label = new_act_label | |
| cf_display.append({ | |
| "活動類別": act_label, | |
| "項目": "", | |
| "金額(億元)": None, | |
| "小計": "", | |
| }) | |
| row = { | |
| "活動類別": "", | |
| "項目": (" " if not x.get("is_subtotal") and not x.get("is_total") else "") + x["label"], | |
| "金額(億元)": None if (x.get("is_subtotal") or x.get("is_total")) else round(x["amount"], 2), | |
| "小計": round(x["amount"], 2) if (x.get("is_subtotal") or x.get("is_total")) else None, | |
| } | |
| cf_display.append(row) | |
| df_cf = pd.DataFrame(cf_display) | |
| st.dataframe( | |
| df_cf, | |
| use_container_width=True, | |
| column_config={ | |
| "金額(億元)": st.column_config.NumberColumn("金額(億元)", format="%.2f"), | |
| "小計": st.column_config.NumberColumn("小計(億元)", format="%.2f"), | |
| }, | |
| hide_index=True, | |
| ) | |
| st.markdown("</div>", unsafe_allow_html=True) | |
| # ── 排除科目說明 ──────────────────────────────────────────────── | |
| excl_items = [x for x in cf_items | |
| if x.get("is_excluded") and not x["label"].startswith("──")] | |
| pure_error = next((x["amount"] for x in cf_items | |
| if "推估誤差" in x["label"] and not x.get("is_excluded")), 0) | |
| st.markdown( | |
| '<div class="section-card">' | |
| '<div class="card-title">⚠ 推估限制與誤差說明</div>' | |
| '<div class="card-sub">' | |
| '現金期初期末為確定值；中間調整項目為推估，可能存在以下無法解釋的差異。' | |
| '</div>', | |
| unsafe_allow_html=True, | |
| ) | |
| # 誤差數字摘要 | |
| err_col1, err_col2 = st.columns([1, 2]) | |
| with err_col1: | |
| err_color = POSITIVE if abs(pure_error) < 0.5 else (WARN if abs(pure_error) < 3 else NEGATIVE) | |
| render_kpi_card( | |
| "無法解釋的差額", | |
| f"{pure_error:+.2f}", | |
| "億元", | |
| "確定現金增減 − 三活動合計", | |
| err_color, err_color, | |
| ) | |
| with err_col2: | |
| if abs(pure_error) < 0.5: | |
| st.success("✓ 推估誤差在合理範圍（< 0.5 億元），三大活動分類已涵蓋主要現金流動。") | |
| elif abs(pure_error) < 3: | |
| st.warning(f"⚠ 推估誤差 {pure_error:+.2f} 億元，可能有部分科目分類不完整或含非現金交易。") | |
| else: | |
| st.error(f"❌ 推估誤差 {pure_error:+.2f} 億元偏大，建議檢視以下排除科目或調整分類。") | |
| # 排除科目明細 | |
| if excl_items: | |
| st.markdown("**刻意排除的科目**（變動為非現金或已含於本期賸餘）：") | |
| excl_df = pd.DataFrame([{ | |
| "科目": x["label"], | |
| "期末−期初增減(億元)": round(x["amount"], 2), | |
| "排除原因": x.get("exclude_reason", ""), | |
| } for x in excl_items]) | |
| st.dataframe(excl_df, use_container_width=True, hide_index=True, | |
| column_config={ | |
| "期末−期初增減(億元)": st.column_config.NumberColumn(format="%+.2f"), | |
| }) | |
| excl_sum = sum(x["amount"] for x in excl_items) | |
| st.caption( | |
| f"排除科目增減合計：{excl_sum:+.2f} 億元。" | |
| "若這些科目有顯著變動，表示存在非現金交易（如以物抵債、賸餘轉增資等），" | |
| "無法在現金流量中反映。" | |
| ) | |
| else: | |
| st.info("本期排除科目均無增減，推估誤差為純分類誤差。") | |
| st.markdown("</div>", unsafe_allow_html=True) | |
| # ─────────────────────────────────────────────────────────────────────────── | |
| # ⑦ 明細資料 | |
| # ─────────────────────────────────────────────────────────────────────────── | |
| with tab_map["🧾 明細資料"]: | |
| if not selected_2060 and not selected_2080 and not selected_nba: | |
| st.markdown('<div class="empty-state"><div class="icon">🧾</div><h3>無可顯示的明細資料</h3></div>', unsafe_allow_html=True) | |
| else: | |
| if selected_2060: | |
| st.markdown('<div class="section-card"><div class="card-title">2060 明細資料（特別收入基金）</div><div class="card-sub">依科目代碼排序；含執行率進度條與跨月 sparkline。</div>', unsafe_allow_html=True) | |
| if show_tables: render_dataframe_with_sparkline(selected_2060["data"], trend_map) | |
| else: st.caption("請在左側「⚙️ 檢視選項」勾選「顯示明細資料表（含 sparkline）」以載入表格。") | |
| st.markdown("</div>", unsafe_allow_html=True) | |
| if selected_nba: | |
| st.markdown('<div class="section-card"><div class="card-title">收支餘絀明細（作業基金）</div><div class="card-sub">依科目代碼排序。</div>', unsafe_allow_html=True) | |
| if show_tables: | |
| d = selected_nba["data"].copy() | |
| ac = next((c for c in ["本年度截止本月份實際數","本年度截至本月份實際數"] if c in d.columns), None) | |
| if "科目代碼" in d.columns: | |
| d["科目代碼"] = d["科目代碼"].astype(str) | |
| d = d.sort_values("科目代碼", kind="stable") | |
| for col in ["本年度法定預算數","本月份實際數","本月份預算數", ac]: | |
| if col and col in d.columns: | |
| d[col] = (pd.to_numeric(d[col], errors="coerce").fillna(0)/1e8).round(3) | |
| d = d.rename(columns={col: f"{col}(億元)"}) | |
| if ac and f"{ac}(億元)" in d.columns and "本年度法定預算數(億元)" in d.columns: | |
| d["執行率(%)"] = d.apply(lambda r: round(r[f"{ac}(億元)"]/r["本年度法定預算數(億元)"]*100, 1) if r["本年度法定預算數(億元)"] else 0, axis=1) | |
| col_cfg = {} | |
| if "執行率(%)" in d.columns: | |
| col_cfg["執行率(%)"] = st.column_config.ProgressColumn("執行率(%)", min_value=0, max_value=150, format="%.1f%%") | |
| st.dataframe(d, use_container_width=True, column_config=col_cfg) | |
| else: st.caption("請在左側「⚙️ 檢視選項」勾選「顯示明細資料表（含 sparkline）」以載入表格。") | |
| st.markdown("</div>", unsafe_allow_html=True) | |
| if selected_2080: | |
| st.markdown('<div class="section-card"><div class="card-title">2080 平衡表明細</div><div class="card-sub">金額已換算為億元。</div>', unsafe_allow_html=True) | |
| if show_tables: | |
| t = selected_2080["data"].copy() | |
| if "金額" in t.columns: t["金額(億元)"] = (pd.to_numeric(t["金額"], errors="coerce").fillna(0)/1e8).round(4) | |
| st.dataframe(t, use_container_width=True) | |
| else: st.caption("請在左側「⚙️ 檢視選項」勾選「顯示明細資料表（含 sparkline）」以載入表格。") | |
| st.markdown("</div>", unsafe_allow_html=True) |