Agent-05 财务与月度分析

-- 月度收入分析
WITH monthly_data AS (
    SELECT 
        toStartOfMonth(order_date) AS month,
        SUM(total_amount) AS gross_sales,
        SUM(total_amount * (1 - discount_rate)) AS net_sales,
        SUM(cost_amount) AS total_cost,
        (net_sales - total_cost) AS gross_profit,
        (net_sales - total_cost) / NULLIF(net_sales, 0) * 100 AS gross_margin_pct
    FROM sales_order
    WHERE order_date >= toStartOfMonth(addMonths(today(), -12))
      AND order_status NOT IN ('cancelled', 'returned')
    GROUP BY toStartOfMonth(order_date)
),
expense_data AS (
    SELECT 
        toStartOfMonth(expense_date) AS month,
        SUM(amount) AS total_expense,
        SUM(CASE WHEN expense_type = 'rent' THEN amount ELSE 0 END) AS rent_expense,
        SUM(CASE WHEN expense_type = 'salary' THEN amount ELSE 0 END) AS salary_expense
    FROM expense_record
    WHERE expense_date >= toStartOfMonth(addMonths(today(), -12))
    GROUP BY toStartOfMonth(expense_date)
)
SELECT 
    m.month,
    m.gross_sales,
    m.net_sales,
    m.gross_profit,
    m.gross_margin_pct,
    (m.gross_profit - e.total_expense) AS net_profit,
    (m.gross_profit - e.total_expense) / NULLIF(m.net_sales, 0) * 100 AS net_margin_pct,
    e.total_expense,
    e.total_expense / NULLIF(m.net_sales, 0) * 100 AS expense_ratio_pct
FROM monthly_data m
LEFT JOIN expense_data e ON m.month = e.month
ORDER BY m.month DESC;

-- 库存周转分析
WITH sales_data AS (
    SELECT 
        toStartOfMonth(ship_date) AS month,
        SUM(cost_amount) AS cogs
    FROM sales_order_detail
    WHERE ship_date >= toStartOfMonth(addMonths(today(), -12))
    GROUP BY toStartOfMonth(ship_date)
),
avg_inventory AS (
    SELECT 
        toStartOfMonth(trans_date) AS month,
        AVG(end_of_month_inventory_value) AS avg_inventory_value
    FROM inventory_transaction
    WHERE trans_date >= toStartOfMonth(addMonths(today(), -12))
    GROUP BY toStartOfMonth(trans_date)
)
SELECT 
    s.month,
    a.avg_inventory_value,
    s.cogs,
    a.avg_inventory_value / NULLIF(s.cogs / 30, 0) AS inventory_turnover_days,
    s.cogs / NULLIF(a.avg_inventory_value, 0) AS inventory_turnover_rate
FROM sales_data s
LEFT JOIN avg_inventory a ON s.month = a.month
ORDER BY s.month DESC;

-- 应收账款账龄分析
SELECT 
    c.customer_code,
    c.customer_name,
    SUM(CASE WHEN due_days <= 0 THEN outstanding_amount ELSE 0 END) AS not_due_amount,
    SUM(CASE WHEN due_days BETWEEN 1 AND 30 THEN outstanding_amount ELSE 0 END) AS overdue_1_30,
    SUM(CASE WHEN due_days BETWEEN 31 AND 60 THEN outstanding_amount ELSE 0 END) AS overdue_31_60,
    SUM(CASE WHEN due_days BETWEEN 61 AND 90 THEN outstanding_amount ELSE 0 END) AS overdue_61_90,
    SUM(CASE WHEN due_days > 90 THEN outstanding_amount ELSE 0 END) AS overdue_90_plus,
    SUM(outstanding_amount) AS total_outstanding
FROM (
    SELECT 
        ar.customer_id,
        ar.invoice_amount,
        ar.paid_amount,
        ar.outstanding_amount,
        toDate(ar.due_date) - today() AS due_days
    FROM ar_record ar
    WHERE ar.outstanding_amount > 0
) t
JOIN customer_master c ON t.customer_id = c.customer_id
GROUP BY c.customer_code, c.customer_name
ORDER BY total_outstanding DESC
LIMIT 50;