Agent-04 供应商与客户分析

-- 供应商对账单汇总
SELECT 
    sp.supplier_id,
    si.supplier_name,
    SUM(sp.purchase_amount) AS total_purchase,
    SUM(sp.return_amount) AS total_return,
    SUM(sp.purchase_amount) - SUM(sp.return_amount) AS net_purchase,
    SUM(CASE WHEN sp.payment_status = 'unpaid' THEN sp.purchase_amount ELSE 0 END) AS accounts_payable,
    MAX(sp.due_date) AS latest_due_date,
    today() - MAX(sp.due_date) AS aging_days,
    CASE 
        WHEN aging_days <= 30 THEN '🟢 正常'
        WHEN aging_days <= 60 THEN '🟡 预警'
        WHEN aging_days <= 90 THEN '🟠 警告'
        ELSE '🔴 危险'
    END AS aging_level
FROM supplier_purchases sp
JOIN supplier_info si ON sp.supplier_id = si.supplier_id
WHERE sp.purchase_date BETWEEN '{start_date}' AND '{end_date}'
GROUP BY sp.supplier_id, si.supplier_name
ORDER BY net_purchase DESC;

-- 供应商综合绩效评分
WITH delivery_score AS (
    SELECT 
        supplier_id,
        ROUND(SUM(is_on_time) * 100.0 / COUNT(*), 2) AS on_time_rate
    FROM supplier_delivery
    WHERE actual_delivery_date BETWEEN '{start_date}' AND '{end_date}'
    GROUP BY supplier_id
),
quality_score AS (
    SELECT 
        supplier_id,
        ROUND((1 - SUM(return_quantity) / SUM(quantity)) * 100, 2) AS quality_rate
    FROM supplier_quality
    WHERE return_date BETWEEN '{start_date}' AND '{end_date}'
    GROUP BY supplier_id
),
price_score AS (
    SELECT 
        supplier_id,
        ROUND((1 - avg_price / market_avg_price) * 100, 2) AS price_advantage
    FROM supplier_price
    WHERE last_update_date >= today() - 90
)
SELECT 
    si.supplier_name,
    ds.on_time_rate,
    qs.quality_rate,
    ps.price_advantage,
    ROUND((ds.on_time_rate * 0.3 + qs.quality_rate * 0.3 + ps.price_advantage * 0.2 + coop_score * 0.2), 2) AS total_score,
    CASE WHEN total_score >= 90 THEN 'A' WHEN total_score >= 75 THEN 'B' WHEN total_score >= 60 THEN 'C' ELSE 'D' END AS grade
FROM supplier_info si
LEFT JOIN delivery_score ds ON si.supplier_id = ds.supplier_id
LEFT JOIN quality_score qs ON si.supplier_id = qs.supplier_id
LEFT JOIN price_score ps ON si.supplier_id = ps.supplier_id
ORDER BY total_score DESC;

-- 客户对账综合查询
SELECT 
    cs.customer_id,
    ci.customer_name,
    ci.customer_type,
    SUM(cs.sale_amount) AS total_sales,
    SUM(cs.profit_amount) AS total_profit,
    ROUND(SUM(cs.profit_amount) / SUM(cs.sale_amount) * 100, 2) AS profit_margin,
    SUM(CASE WHEN cs.collection_status = 'collected' THEN cs.sale_amount ELSE 0 END) AS collected_amount,
    SUM(CASE WHEN cs.collection_status IN ('pending','overdue') THEN cs.sale_amount ELSE 0 END) AS credit_balance,
    ROUND(SUM(CASE WHEN cs.collection_status = 'collected' THEN cs.sale_amount ELSE 0 END) / SUM(cs.sale_amount) * 100, 2) AS collection_rate
FROM customer_sales cs
JOIN customer_info ci ON cs.customer_id = ci.customer_id
WHERE cs.sale_date BETWEEN '{start_date}' AND '{end_date}'
GROUP BY cs.customer_id, ci.customer_name, ci.customer_type
ORDER BY total_sales DESC;

-- 客户ABC分类（按年累计销售额占比）
WITH yearly_sales AS (
    SELECT 
        customer_id,
        SUM(sale_amount) AS annual_sales
    FROM customer_sales
    WHERE sale_date BETWEEN '{year}-01-01' AND '{year}-12-31'
    GROUP BY customer_id
),
sales_with_cumsum AS (
    SELECT 
        customer_id,
        annual_sales,
        SUM(annual_sales) OVER() AS total_sales,
        SUM(annual_sales) OVER(ORDER BY annual_sales DESC) AS cumsum_sales
    FROM yearly_sales
)
SELECT 
    ci.customer_name,
    s.annual_sales,
    ROUND(s.annual_sales / s.total_sales * 100, 2) AS sales_pct,
    ROUND(s.cumsum_sales / s.total_sales * 100, 2) AS cumsum_pct,
    CASE WHEN cumsum_pct <= 80 THEN 'A' WHEN cumsum_pct <= 95 THEN 'B' ELSE 'C' END AS abc_class
FROM sales_with_cumsum s
JOIN customer_info ci ON s.customer_id = ci.customer_id
ORDER BY s.annual_sales DESC;

-- 客户流失预警
SELECT 
    ci.customer_id,
    ci.customer_name,
    ci.customer_type,
    MAX(cs.sale_date) AS last_transaction_date,
    today() - MAX(cs.sale_date) AS days_since_transaction,
    CASE 
        WHEN days_since_transaction > 90 THEN '🔴 严重预警'
        WHEN days_since_transaction > 60 THEN '🟠 中度预警'
        WHEN days_since_transaction > 30 THEN '🟡 轻度预警'
        ELSE '🟢 正常'
    END AS warning_level
FROM customer_info ci
LEFT JOIN customer_sales cs ON ci.customer_id = cs.customer_id
GROUP BY ci.customer_id, ci.customer_name, ci.customer_type
HAVING days_since_transaction > 30 OR days_since_transaction IS NULL
ORDER BY days_since_transaction DESC NULLS FIRST;

-- 员工综合提成计算
WITH sales_summary AS (
    SELECT 
        salesperson_id,
        SUM(sale_amount) AS total_sales,
        SUM(profit_amount) AS total_profit,
        SUM(CASE WHEN collection_status = 'collected' THEN sale_amount ELSE 0 END) AS collected_amount
    FROM customer_sales
    WHERE sale_date BETWEEN '{start_date}' AND '{end_date}'
    GROUP BY salesperson_id
)
SELECT 
    e.employee_name,
    s.total_sales,
    s.collected_amount,
    ROUND(s.collected_amount / s.total_sales * 100, 2) AS collection_rate,
    ROUND(s.total_profit / s.total_sales * 100, 2) AS profit_margin,
    -- 综合提成 = 销售额 × 回款率系数 × 毛利率系数 × 基础提成率
    ROUND(s.total_sales * (s.collected_amount/s.total_sales) * (s.total_profit/s.total_sales) * 0.15, 2) AS commission_amount,
    -- 综合考核分
    ROUND((collection_rate * 0.4 + profit_margin * 0.3 + sales_share * 0.3), 2) AS performance_score
FROM sales_summary s
JOIN employee_info e ON s.salesperson_id = e.employee_id
ORDER BY commission_amount DESC;