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(DISTINCT o.id) as total_orders,
                SUM(o.total_amount) as total_revenue,
                AVG(o.total_amount) as avg_order_value,
                COUNT(DISTINCT o.customer_id) as unique_customers,
                SUM(oi.quantity) as total_items_sold
            FROM orders o
            LEFT JOIN order_items oi ON o.id = oi.order_id 
            WHERE o.order_date BETWEEN ? AND ?
            AND o.order_status != 0"; // 排除已取消订单
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ss", $start_date, $end_date);
    $stmt->execute();
    return $stmt->get_result()->fetch_assoc();
}

/**
 * 获取订单转化率统计
 */
function getOrderConversionStats($conn, $start_date, $end_date) {
    $sql = "SELECT 
                order_status,
                COUNT(*) as count,
                SUM(total_amount) as amount
            FROM orders 
            WHERE order_date BETWEEN ? AND ?
            GROUP BY order_status";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ss", $start_date, $end_date);
    $stmt->execute();
    return $stmt->get_result();
}

/**
 * 获取产品类别销售统计
 */
function getProductCategorySales($conn, $start_date, $end_date) {
    $sql = "SELECT 
                pc.name as category_name,
                COUNT(DISTINCT o.id) as order_count,
                SUM(oi.quantity) as total_quantity,
                SUM(oi.total_price) as total_revenue
            FROM orders o
            JOIN order_items oi ON o.id = oi.order_id
            JOIN products p ON oi.product_id = p.id
            JOIN product_categories pc ON p.category_id = pc.id
            WHERE o.order_date BETWEEN ? AND ?
            AND o.order_status != 0
            GROUP BY pc.id
            ORDER BY total_revenue DESC";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ss", $start_date, $end_date);
    $stmt->execute();
    return $stmt->get_result();
}

/**
 * 获取客户地区分布
 */
function getCustomerDistribution($conn, $start_date, $end_date) {
    $sql = "SELECT 
                c.countryName as region,
                COUNT(DISTINCT o.customer_id) as customer_count,
                COUNT(o.id) as order_count,
                SUM(o.total_amount) as total_revenue
            FROM orders o
            JOIN customer cu ON o.customer_id = cu.id
            JOIN country c ON cu.cs_country = c.id
            WHERE o.order_date BETWEEN ? AND ?
            AND o.order_status != 0
            GROUP BY c.id
            ORDER BY total_revenue DESC";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ss", $start_date, $end_date);
    $stmt->execute();
    return $stmt->get_result();
}

/**
 * 获取销售员业绩统计
 */
function getEmployeePerformance($conn, $start_date, $end_date) {
    $sql = "SELECT 
                e.em_user as employee_name,
                COUNT(DISTINCT o.id) as order_count,
                COUNT(DISTINCT o.customer_id) as customer_count,
                SUM(o.total_amount) as total_revenue,
                AVG(o.total_amount) as avg_order_value
            FROM orders o
            JOIN employee e ON o.employee_id = e.id
            WHERE o.order_date BETWEEN ? AND ?
            AND o.order_status != 0
            GROUP BY e.id
            ORDER BY total_revenue DESC";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ss", $start_date, $end_date);
    $stmt->execute();
    return $stmt->get_result();
}

/**
 * 获取支付状态统计
 */
function getPaymentStatusStats($conn, $start_date, $end_date) {
    $sql = "SELECT 
                payment_status,
                COUNT(*) as count,
                SUM(total_amount) as amount
            FROM orders 
            WHERE order_date BETWEEN ? AND ?
            AND order_status != 0
            GROUP BY payment_status";
    
    $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 结束日期
 * @return mysqli_result 月度销售数据结果集
 */
function getMonthlySalesTrend($conn, $start_date, $end_date) {
    $sql = "SELECT 
                DATE_FORMAT(order_date, '%Y-%m') as month,
                COUNT(DISTINCT id) as orders,
                SUM(total_amount) as revenue,
                COUNT(DISTINCT customer_id) as unique_customers
            FROM orders 
            WHERE order_date BETWEEN ? AND ?
            AND order_status != 0
            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';
    
    if ($period == 'week') {
        $groupFormat = '%x-W%v';
    } else if ($period == 'month') {
        $groupFormat = '%Y-%m';
    }
    
    $sql = "SELECT 
                DATE_FORMAT(o.order_date, '$groupFormat') as time_period,
                COUNT(DISTINCT o.id) as order_count,
                SUM(oi.quantity) as total_quantity,
                SUM(o.total_amount) as total_amount,
                COUNT(DISTINCT o.customer_id) as unique_customers
            FROM orders o
            LEFT JOIN order_items oi ON o.id = oi.order_id
            WHERE o.order_date BETWEEN ? AND ?
            AND o.order_status != 0
            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 class="stat-card">
            <h3>独立客户数</h3>
            <div class="stat-value"><?php echo number_format($sales_overview['unique_customers']); ?></div>
        </div>

        <div class="stat-card">
            <h3>总销售数量</h3>
            <div class="stat-value"><?php echo number_format($sales_overview['total_items_sold']); ?></div>
        </div>
    </div>
    <?php
}

/**
 * 渲染订单转化率分析
 */
function renderConversionAnalysis($conversion_stats) {
    $status_names = [
        1 => '待确认',
        2 => '已确认',
        3 => '生产中',
        4 => '已发货',
        5 => '已完成',
        0 => '已取消'
    ];
    
    $total_orders = 0;
    $data = [];
    while ($row = $conversion_stats->fetch_assoc()) {
        $total_orders += $row['count'];
        $data[$row['order_status']] = $row;
    }
    ?>
    <div class="analysis-grid">
        <div>
            <canvas id="orderStatusChart"></canvas>
        </div>
        <div class="table-responsive">
            <table class="data-table">
                <thead>
                    <tr>
                        <th>订单状态</th>
                        <th>订单数</th>
                        <th>转化率</th>
                        <th>金额</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($status_names as $status_id => $status_name): ?>
                        <?php if (isset($data[$status_id])): ?>
                        <tr>
                            <td><?php echo $status_name; ?></td>
                            <td><?php echo number_format($data[$status_id]['count']); ?></td>
                            <td><?php echo number_format(($data[$status_id]['count'] / $total_orders) * 100, 1); ?>%</td>
                            <td>¥<?php echo number_format($data[$status_id]['amount'], 2); ?></td>
                        </tr>
                        <?php endif; ?>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>
    <script>
        var orderStatusCtx = document.getElementById('orderStatusChart').getContext('2d');
        new Chart(orderStatusCtx, {
            type: 'doughnut',
            data: {
                labels: <?php 
                    $labels = [];
                    $values = [];
                    foreach ($status_names as $status_id => $status_name) {
                        if (isset($data[$status_id])) {
                            $labels[] = $status_name;
                            $values[] = $data[$status_id]['count'];
                        }
                    }
                    echo json_encode($labels);
                ?>,
                datasets: [{
                    data: <?php echo json_encode($values); ?>,
                    backgroundColor: [
                        '#FF6384',
                        '#36A2EB',
                        '#FFCE56',
                        '#4BC0C0',
                        '#9966FF',
                        '#FF9F40'
                    ]
                }]
            },
            options: {
                responsive: true,
                plugins: {
                    legend: {
                        position: 'right'
                    },
                    title: {
                        display: true,
                        text: '订单状态分布'
                    }
                }
            }
        });
    </script>
    <?php
}

/**
 * 渲染产品类别销售分析图表
 */
function renderCategorySalesChart($category_sales) {
    $labels = [];
    $quantities = [];
    $revenues = [];
    
    while ($row = $category_sales->fetch_assoc()) {
        $labels[] = $row['category_name'];
        $quantities[] = $row['total_quantity'];
        $revenues[] = $row['total_revenue'];
    }
    ?>
    <div class="analysis-grid">
        <div>
            <canvas id="categorySalesChart"></canvas>
        </div>
        <div class="table-responsive">
            <table class="data-table">
                <thead>
                    <tr>
                        <th>产品类别</th>
                        <th>订单数</th>
                        <th>销售数量</th>
                        <th>销售金额</th>
                    </tr>
                </thead>
                <tbody>
                    <?php
                    $category_sales->data_seek(0);
                    while ($row = $category_sales->fetch_assoc()):
                    ?>
                    <tr>
                        <td><?php echo $row['category_name']; ?></td>
                        <td><?php echo number_format($row['order_count']); ?></td>
                        <td><?php echo number_format($row['total_quantity']); ?></td>
                        <td>¥<?php echo number_format($row['total_revenue'], 2); ?></td>
                    </tr>
                    <?php endwhile; ?>
                </tbody>
            </table>
        </div>
    </div>
    <script>
        var categorySalesCtx = document.getElementById('categorySalesChart').getContext('2d');
        new Chart(categorySalesCtx, {
            type: 'bar',
            data: {
                labels: <?php echo json_encode($labels); ?>,
                datasets: [
                    {
                        label: '销售数量',
                        data: <?php echo json_encode($quantities); ?>,
                        backgroundColor: 'rgba(54, 162, 235, 0.5)',
                        borderColor: 'rgba(54, 162, 235, 1)',
                        borderWidth: 1,
                        yAxisID: 'y-quantity'
                    },
                    {
                        label: '销售金额',
                        data: <?php echo json_encode($revenues); ?>,
                        backgroundColor: 'rgba(255, 99, 132, 0.5)',
                        borderColor: 'rgba(255, 99, 132, 1)',
                        borderWidth: 1,
                        yAxisID: 'y-revenue'
                    }
                ]
            },
            options: {
                responsive: true,
                scales: {
                    'y-quantity': {
                        type: 'linear',
                        position: 'left',
                        title: {
                            display: true,
                            text: '销售数量'
                        }
                    },
                    'y-revenue': {
                        type: 'linear',
                        position: 'right',
                        title: {
                            display: true,
                            text: '销售金额'
                        },
                        grid: {
                            drawOnChartArea: false
                        }
                    }
                }
            }
        });
    </script>
    <?php
}

/**
 * 渲染客户地区分布图表
 */
function renderCustomerDistributionChart($customer_distribution) {
    $regions = [];
    $customers = [];
    $revenues = [];
    
    while ($row = $customer_distribution->fetch_assoc()) {
        $regions[] = $row['region'];
        $customers[] = $row['customer_count'];
        $revenues[] = $row['total_revenue'];
    }
    ?>
    <div class="analysis-grid">
        <div>
            <canvas id="regionDistributionChart"></canvas>
        </div>
        <div class="table-responsive">
            <table class="data-table">
                <thead>
                    <tr>
                        <th>地区</th>
                        <th>客户数</th>
                        <th>订单数</th>
                        <th>销售金额</th>
                    </tr>
                </thead>
                <tbody>
                    <?php
                    $customer_distribution->data_seek(0);
                    while ($row = $customer_distribution->fetch_assoc()):
                    ?>
                    <tr>
                        <td><?php echo $row['region']; ?></td>
                        <td><?php echo number_format($row['customer_count']); ?></td>
                        <td><?php echo number_format($row['order_count']); ?></td>
                        <td>¥<?php echo number_format($row['total_revenue'], 2); ?></td>
                    </tr>
                    <?php endwhile; ?>
                </tbody>
            </table>
        </div>
    </div>
    <script>
        var regionDistributionCtx = document.getElementById('regionDistributionChart').getContext('2d');
        new Chart(regionDistributionCtx, {
            type: 'bar',
            data: {
                labels: <?php echo json_encode($regions); ?>,
                datasets: [
                    {
                        label: '客户数',
                        data: <?php echo json_encode($customers); ?>,
                        backgroundColor: 'rgba(75, 192, 192, 0.5)',
                        borderColor: 'rgba(75, 192, 192, 1)',
                        borderWidth: 1,
                        yAxisID: 'y-customers'
                    },
                    {
                        label: '销售金额',
                        data: <?php echo json_encode($revenues); ?>,
                        backgroundColor: 'rgba(255, 159, 64, 0.5)',
                        borderColor: 'rgba(255, 159, 64, 1)',
                        borderWidth: 1,
                        yAxisID: 'y-revenue'
                    }
                ]
            },
            options: {
                responsive: true,
                scales: {
                    'y-customers': {
                        type: 'linear',
                        position: 'left',
                        title: {
                            display: true,
                            text: '客户数'
                        }
                    },
                    'y-revenue': {
                        type: 'linear',
                        position: 'right',
                        title: {
                            display: true,
                            text: '销售金额'
                        },
                        grid: {
                            drawOnChartArea: false
                        }
                    }
                }
            }
        });
    </script>
    <?php
}

/**
 * 渲染销售员业绩表格
 */
function renderEmployeePerformanceTable($employee_performance) {
    ?>
    <div class="table-responsive">
        <table class="data-table">
            <thead>
                <tr>
                    <th>销售员</th>
                    <th>订单数</th>
                    <th>客户数</th>
                    <th>总销售额</th>
                    <th>平均订单金额</th>
                </tr>
            </thead>
            <tbody>
                <?php while ($row = $employee_performance->fetch_assoc()): ?>
                <tr>
                    <td><?php echo $row['employee_name']; ?></td>
                    <td><?php echo number_format($row['order_count']); ?></td>
                    <td><?php echo number_format($row['customer_count']); ?></td>
                    <td>¥<?php echo number_format($row['total_revenue'], 2); ?></td>
                    <td>¥<?php echo number_format($row['avg_order_value'], 2); ?></td>
                </tr>
                <?php endwhile; ?>
            </tbody>
        </table>
    </div>
    <?php
}

/**
 * 渲染支付状态分析图表
 */
function renderPaymentStatusChart($payment_stats) {
    $status_names = [
        0 => '未付款',
        1 => '部分付款',
        2 => '已付清'
    ];
    
    $data = [];
    while ($row = $payment_stats->fetch_assoc()) {
        $data[$row['payment_status']] = $row;
    }
    ?>
    <div class="analysis-grid">
        <div>
            <canvas id="paymentStatusChart"></canvas>
        </div>
        <div class="table-responsive">
            <table class="data-table">
                <thead>
                    <tr>
                        <th>支付状态</th>
                        <th>订单数</th>
                        <th>订单金额</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($status_names as $status_id => $status_name): ?>
                        <?php if (isset($data[$status_id])): ?>
                        <tr>
                            <td><?php echo $status_name; ?></td>
                            <td><?php echo number_format($data[$status_id]['count']); ?></td>
                            <td>¥<?php echo number_format($data[$status_id]['amount'], 2); ?></td>
                        </tr>
                        <?php endif; ?>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>
    <script>
        var paymentStatusCtx = document.getElementById('paymentStatusChart').getContext('2d');
        new Chart(paymentStatusCtx, {
            type: 'pie',
            data: {
                labels: <?php 
                    $labels = [];
                    $values = [];
                    foreach ($status_names as $status_id => $status_name) {
                        if (isset($data[$status_id])) {
                            $labels[] = $status_name;
                            $values[] = $data[$status_id]['amount'];
                        }
                    }
                    echo json_encode($labels);
                ?>,
                datasets: [{
                    data: <?php echo json_encode($values); ?>,
                    backgroundColor: [
                        '#FF6384',
                        '#36A2EB',
                        '#4BC0C0'
                    ]
                }]
            },
            options: {
                responsive: true,
                plugins: {
                    legend: {
                        position: 'right'
                    },
                    title: {
                        display: true,
                        text: '支付状态分布'
                    }
                }
            }
        });
    </script>
    <?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');
        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',
                        tension: 0.1
                    },
                    {
                        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',
                        tension: 0.1
                    }
                ]
            },
            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');
        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
}