mtb_crm_asp/statistics_sales.php

<?php
/**
 * 销售统计分析模块
 * 
 * 包含与销售相关的数据分析功能
 */

require_once 'statistics_utils.php';

/**
 * 获取销售概览数据
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @return array 销售概览数据
 */
function getSalesOverview($conn, $start_date, $end_date) {
    $sql = "SELECT 
                COUNT(id) as total_orders,
                SUM(total_amount) as total_revenue,
                AVG(total_amount) as avg_order_value
            FROM orders 
            WHERE order_date BETWEEN ? AND ?";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ss", $start_date, $end_date);
    $stmt->execute();
    $result = $stmt->get_result();
    return $result->fetch_assoc();
}

/**
 * 获取每月销售趋势
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @return mysqli_result 月度销售数据结果集
 */
function getMonthlySalesTrend($conn, $start_date, $end_date) {
    $sql = "SELECT 
                DATE_FORMAT(order_date, '%Y-%m') as month,
                COUNT(id) as orders,
                SUM(total_amount) as revenue
            FROM orders 
            WHERE order_date BETWEEN ? AND ?
            GROUP BY DATE_FORMAT(order_date, '%Y-%m')
            ORDER BY month";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ss", $start_date, $end_date);
    $stmt->execute();
    return $stmt->get_result();
}

/**
 * 获取详细时间段订单趋势
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @param string $period 时间粒度 (day/week/month)
 * @return mysqli_result 订单趋势数据结果集
 */
function getDetailedOrderTrend($conn, $start_date, $end_date, $period = 'day') {
    $groupFormat = '%Y-%m-%d';
    $intervalUnit = 'DAY';
    
    if ($period == 'week') {
        $groupFormat = '%x-W%v'; // ISO year and week number
        $intervalUnit = 'WEEK';
    } else if ($period == 'month') {
        $groupFormat = '%Y-%m';
        $intervalUnit = 'MONTH';
    }
    
    $sql = "SELECT 
                DATE_FORMAT(o.order_date, '$groupFormat') as time_period,
                COUNT(o.id) as order_count,
                SUM(oi.quantity) as total_quantity,
                SUM(o.total_amount) as total_amount
            FROM orders o
            LEFT JOIN order_items oi ON o.id = oi.order_id
            WHERE o.order_date BETWEEN ? AND ?
            GROUP BY time_period
            ORDER BY MIN(o.order_date)";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ss", $start_date, $end_date);
    $stmt->execute();
    return $stmt->get_result();
}

/**
 * 渲染销售概览卡片
 * 
 * @param array $sales_overview 销售概览数据
 * @return void
 */
function renderSalesOverviewCards($sales_overview) {
    ?>
    <div class="stats-grid">
        <div class="stat-card">
            <h3>总订单数</h3>
            <div class="stat-value"><?php echo number_format($sales_overview['total_orders']); ?></div>
        </div>
        
        <div class="stat-card">
            <h3>总收入</h3>
            <div class="stat-value">¥<?php echo number_format($sales_overview['total_revenue'], 2); ?></div>
        </div>
        
        <div class="stat-card">
            <h3>平均订单金额</h3>
            <div class="stat-value">¥<?php echo number_format($sales_overview['avg_order_value'], 2); ?></div>
        </div>
    </div>
    <?php
}

/**
 * 渲染月度销售趋势图
 * 
 * @param array $monthly_labels 月份标签
 * @param array $monthly_orders 月度订单数量
 * @param array $monthly_revenue 月度收入
 * @return void
 */
function renderMonthlySalesTrendChart($monthly_labels, $monthly_orders, $monthly_revenue) {
    ?>
    <div class="chart-container">
        <div class="chart-header">
            <h2 class="chart-title">销售趋势</h2>
        </div>
        <canvas id="salesTrendChart"></canvas>
    </div>
    
    <script>
        // 销售趋势图
        var salesTrendCtx = document.getElementById('salesTrendChart').getContext('2d');
        var salesTrendChart = new Chart(salesTrendCtx, {
            type: 'line',
            data: {
                labels: <?php echo json_encode($monthly_labels); ?>,
                datasets: [
                    {
                        label: '订单数量',
                        data: <?php echo json_encode($monthly_orders); ?>,
                        backgroundColor: 'rgba(54, 162, 235, 0.2)',
                        borderColor: 'rgba(54, 162, 235, 1)',
                        borderWidth: 2,
                        yAxisID: 'y-orders'
                    },
                    {
                        label: '销售收入',
                        data: <?php echo json_encode($monthly_revenue); ?>,
                        backgroundColor: 'rgba(255, 99, 132, 0.2)',
                        borderColor: 'rgba(255, 99, 132, 1)',
                        borderWidth: 2,
                        yAxisID: 'y-revenue'
                    }
                ]
            },
            options: {
                responsive: true,
                scales: {
                    'y-orders': {
                        type: 'linear',
                        position: 'left',
                        title: {
                            display: true,
                            text: '订单数量'
                        }
                    },
                    'y-revenue': {
                        type: 'linear',
                        position: 'right',
                        title: {
                            display: true,
                            text: '销售收入'
                        },
                        grid: {
                            drawOnChartArea: false
                        }
                    }
                }
            }
        });
    </script>
    <?php
}

/**
 * 渲染详细订单趋势图
 * 
 * @param array $time_labels 时间标签
 * @param array $time_orders 时间段订单数量
 * @param array $time_quantities 时间段产品数量
 * @param string $period 时间粒度
 * @return void
 */
function renderDetailedOrderTrendChart($time_labels, $time_orders, $time_quantities, $period = 'day') {
    $period_text = $period == 'day' ? '日' : ($period == 'week' ? '周' : '月');
    ?>
    <div class="chart-container">
        <div class="chart-header">
            <h2 class="chart-title">详细订单趋势 (<?php echo $period_text; ?>)</h2>
        </div>
        <canvas id="detailedOrdersChart"></canvas>
    </div>
    
    <script>
        // 详细时间段订单趋势图
        var detailedOrdersCtx = document.getElementById('detailedOrdersChart').getContext('2d');
        var detailedOrdersChart = new Chart(detailedOrdersCtx, {
            type: 'line',
            data: {
                labels: <?php echo json_encode($time_labels); ?>,
                datasets: [
                    {
                        label: '订单数量',
                        data: <?php echo json_encode($time_orders); ?>,
                        backgroundColor: 'rgba(75, 192, 192, 0.2)',
                        borderColor: 'rgba(75, 192, 192, 1)',
                        borderWidth: 2,
                        yAxisID: 'y-orders',
                        tension: 0.1
                    },
                    {
                        label: '产品订购数量',
                        data: <?php echo json_encode($time_quantities); ?>,
                        backgroundColor: 'rgba(255, 159, 64, 0.2)',
                        borderColor: 'rgba(255, 159, 64, 1)',
                        borderWidth: 2,
                        yAxisID: 'y-quantity',
                        tension: 0.1
                    }
                ]
            },
            options: {
                responsive: true,
                scales: {
                    x: {
                        title: {
                            display: true,
                            text: '时间'
                        }
                    },
                    'y-orders': {
                        type: 'linear',
                        position: 'left',
                        title: {
                            display: true,
                            text: '订单数量'
                        },
                        beginAtZero: true
                    },
                    'y-quantity': {
                        type: 'linear',
                        position: 'right',
                        title: {
                            display: true,
                            text: '产品订购数量'
                        },
                        beginAtZero: true,
                        grid: {
                            drawOnChartArea: false
                        }
                    }
                }
            }
        });
    </script>
    <?php
}