service/three_year_table_tool.py

def format_number_for_table(value):
    """用于表格的数字格式化：自动 B/M，保留适当小数"""
    if value is None or value == 0:
        return "0"
    if abs(value) >= 1_000_000_000:
        num = value / 1_000_000_000
        if num.is_integer():
            return f"{int(num)}B"
        else:
            return f"{num:.2f}B".rstrip('0').rstrip('.')
    elif abs(value) >= 1_000_000:
        num = value / 1_000_000
        if num.is_integer():
            return f"{int(num)}M"
        else:
            return f"{num:.1f}M".rstrip('0').rstrip('.')
    else:
        return f"{value:,.1f}".rstrip('0').rstrip('.')

def safe_float_or_zero(val):
    if val is None:
        return 0.0
    try:
        return float(val)
    except (ValueError, TypeError):
        return 0.0

def calculate_yoy_rate(current, previous):
    if previous == 0:
        return "+0.0%" if current >= 0 else "-0.0%"
    rate = (current - previous) / abs(previous) * 100
    sign = "+" if rate >= 0 else "-"
    return f"{sign}{abs(rate):.1f}%"

def build_table_format(three_year_data):
    # 按 fiscal_year 降序排列（最新在前）
    sorted_data = sorted(three_year_data, key=lambda x: x["fiscal_year"], reverse=True)
    
    # ✅ 生成年份标签：FY类型格式为"FY 2025"，季度格式为"2025 Q3"
    year_labels = []
    for item in sorted_data:
        if item['level'] == 'FY':
            year_labels.append(f"FY {item['fiscal_year']}")
        else:
            year_labels.append(f"{item['fiscal_year']} {item['level']}")
    
    # 提取数值（确保至少三年，不足用 0 补齐）
    while len(sorted_data) < 3:
        sorted_data.append({
            "fiscal_year": 0,
            "level": "N/A",
            "total_revenue": 0,
            "net_income": 0,
            "earnings_per_share": 0.0,
            "operating_expenses": 0,
            "operating_cash_flow": 0
        })
        year_labels.append("N/A")

    # 取前三
    y0, y1, y2 = sorted_data[0], sorted_data[1], sorted_data[2]

    # 构建 list_data
    list_data = [
        ["Category"] + year_labels[:3],
        ["Total Revenue",
         format_number_for_table(safe_float_or_zero(y0["total_revenue"])),
         format_number_for_table(safe_float_or_zero(y1["total_revenue"])),
         format_number_for_table(safe_float_or_zero(y2["total_revenue"]))],
        ["Net Income",
         format_number_for_table(safe_float_or_zero(y0["net_income"])),
         format_number_for_table(safe_float_or_zero(y1["net_income"])),
         format_number_for_table(safe_float_or_zero(y2["net_income"]))],
        ["Earnings Per Share",
         f"{safe_float_or_zero(y0['earnings_per_share']):.2f}",
         f"{safe_float_or_zero(y1['earnings_per_share']):.2f}",
         f"{safe_float_or_zero(y2['earnings_per_share']):.2f}"],
        ["Operating Expenses",
         format_number_for_table(safe_float_or_zero(y0["operating_expenses"])),
         format_number_for_table(safe_float_or_zero(y1["operating_expenses"])),
         format_number_for_table(safe_float_or_zero(y2["operating_expenses"]))],
        ["Operating Cash Flow",
         format_number_for_table(safe_float_or_zero(y0["operating_cash_flow"])),
         format_number_for_table(safe_float_or_zero(y1["operating_cash_flow"])),
         format_number_for_table(safe_float_or_zero(y2["operating_cash_flow"]))]
    ]

    # 构建 yoy_rates(三列:y0 vs y1, y1 vs y2, y2 vs y3)
    # 注意:第三年没有更早的年份来比较,所以显示"--"
    yoy_rates = [
        ["Category"] + year_labels[:3],  # 三年的标签
        ["Total Revenue",
         calculate_yoy_rate(safe_float_or_zero(y0["total_revenue"]), safe_float_or_zero(y1["total_revenue"])),
         calculate_yoy_rate(safe_float_or_zero(y1["total_revenue"]), safe_float_or_zero(y2["total_revenue"])),
         "--"],  # 最早一年没有更早的数据来比较
        ["Net Income",
         calculate_yoy_rate(safe_float_or_zero(y0["net_income"]), safe_float_or_zero(y1["net_income"])),
         calculate_yoy_rate(safe_float_or_zero(y1["net_income"]), safe_float_or_zero(y2["net_income"])),
         "--"],
        ["Earnings Per Share",
         calculate_yoy_rate(safe_float_or_zero(y0["earnings_per_share"]), safe_float_or_zero(y1["earnings_per_share"])),
         calculate_yoy_rate(safe_float_or_zero(y1["earnings_per_share"]), safe_float_or_zero(y2["earnings_per_share"])),
         "--"],
        ["Operating Expenses",
         calculate_yoy_rate(safe_float_or_zero(y0["operating_expenses"]), safe_float_or_zero(y1["operating_expenses"])),
         calculate_yoy_rate(safe_float_or_zero(y1["operating_expenses"]), safe_float_or_zero(y2["operating_expenses"])),
         "--"],
        ["Operating Cash Flow",
         calculate_yoy_rate(safe_float_or_zero(y0["operating_cash_flow"]), safe_float_or_zero(y1["operating_cash_flow"])),
         calculate_yoy_rate(safe_float_or_zero(y1["operating_cash_flow"]), safe_float_or_zero(y2["operating_cash_flow"])),
         "--"]
    ]

    return {
        "list_data": list_data,
        "yoy_rates": yoy_rates
    }