mtb_crm_asp/statistics_products.php

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

require_once 'statistics_utils.php';

/**
 * 获取热门产品数据
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @param int $limit 限制返回的产品数量
 * @return mysqli_result 热门产品数据结果集
 */
function getTopProducts($conn, $start_date, $end_date, $limit = 5) {
    $sql = "SELECT 
                p.ProductName, 
                SUM(oi.quantity) as total_quantity,
                SUM(oi.total_price) as total_revenue
            FROM order_items oi
            JOIN products p ON oi.product_id = p.id
            JOIN orders o ON oi.order_id = o.id
            WHERE o.order_date BETWEEN ? AND ?
            GROUP BY oi.product_id
            ORDER BY total_revenue DESC
            LIMIT ?";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ssi", $start_date, $end_date, $limit);
    $stmt->execute();
    return $stmt->get_result();
}

/**
 * 获取产品销售趋势
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @param int $product_id 产品ID，为0时获取所有产品的总体趋势
 * @param string $period 时间粒度 (day/week/month)
 * @return mysqli_result 产品销售趋势数据结果集
 */
function getProductSalesTrend($conn, $start_date, $end_date, $product_id = 0, $period = 'month') {
    $groupFormat = '%Y-%m-%d';
    if ($period == 'week') {
        $groupFormat = '%x-W%v'; // ISO year and week number
    } else if ($period == 'month') {
        $groupFormat = '%Y-%m';
    }
    
    $sql = "SELECT 
                DATE_FORMAT(o.order_date, '$groupFormat') as time_period,
                SUM(oi.quantity) as total_quantity,
                SUM(oi.total_price) as total_revenue,
                COUNT(DISTINCT o.id) as order_count
            FROM order_items oi
            JOIN orders o ON oi.order_id = o.id";
    
    if ($product_id > 0) {
        $sql .= " WHERE o.order_date BETWEEN ? AND ? AND oi.product_id = ?";
    } else {
        $sql .= " WHERE o.order_date BETWEEN ? AND ?";
    }
    
    $sql .= " GROUP BY time_period
              ORDER BY MIN(o.order_date)";
    
    $stmt = $conn->prepare($sql);
    
    if ($product_id > 0) {
        $stmt->bind_param("ssi", $start_date, $end_date, $product_id);
    } else {
        $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 getProductCategorySales($conn, $start_date, $end_date) {
    $sql = "SELECT 
                pc.name as category_name, 
                SUM(oi.quantity) as total_quantity,
                SUM(oi.total_price) as total_revenue,
                COUNT(DISTINCT o.id) as order_count
            FROM order_items oi
            JOIN products p ON oi.product_id = p.id
            JOIN product_categories pc ON p.category_id = pc.id
            JOIN orders o ON oi.order_id = o.id
            WHERE o.order_date BETWEEN ? AND ?
            GROUP BY p.category_id
            ORDER BY total_revenue DESC";
    
    $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 int $limit 限制返回的产品-地区组合数量
 * @return mysqli_result 产品与地区关联分析数据结果集
 */
function getProductRegionAnalysis($conn, $start_date, $end_date, $limit = 10) {
    $sql = "SELECT 
                p.ProductName, 
                c.countryName,
                SUM(oi.quantity) as total_quantity,
                SUM(oi.total_price) as total_revenue
            FROM order_items oi
            JOIN products p ON oi.product_id = p.id
            JOIN orders o ON oi.order_id = o.id
            JOIN customer cu ON o.customer_id = cu.id
            JOIN country c ON cu.cs_country = c.id
            WHERE o.order_date BETWEEN ? AND ?
            GROUP BY oi.product_id, cu.cs_country
            ORDER BY total_revenue DESC
            LIMIT ?";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ssi", $start_date, $end_date, $limit);
    $stmt->execute();
    return $stmt->get_result();
}

/**
 * 渲染热门产品表格
 * 
 * @param mysqli_result $top_products 热门产品数据
 * @return void
 */
function renderTopProductsTable($top_products) {
    ?>
    <div class="chart-container">
        <div class="chart-header">
            <h2 class="chart-title">热门产品</h2>
        </div>
        <table class="data-table">
            <thead>
                <tr>
                    <th>产品名称</th>
                    <th>销售数量</th>
                    <th>销售收入</th>
                </tr>
            </thead>
            <tbody>
                <?php while ($row = $top_products->fetch_assoc()): ?>
                <tr>
                    <td><?php echo htmlspecialchars($row['ProductName']); ?></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>
    <?php
}

/**
 * 渲染产品销售趋势图
 * 
 * @param array $time_labels 时间标签
 * @param array $quantities 产品销售数量
 * @param array $revenues 产品销售收入
 * @return void
 */
function renderProductSalesTrendChart($time_labels, $quantities, $revenues) {
    ?>
    <div class="chart-container">
        <div class="chart-header">
            <h2 class="chart-title">产品销售趋势</h2>
        </div>
        <canvas id="productSalesTrendChart"></canvas>
    </div>
    
    <script>
        // 产品销售趋势图
        var productSalesTrendCtx = document.getElementById('productSalesTrendChart').getContext('2d');
        var productSalesTrendChart = new Chart(productSalesTrendCtx, {
            type: 'line',
            data: {
                labels: <?php echo json_encode($time_labels); ?>,
                datasets: [
                    {
                        label: '销售数量',
                        data: <?php echo json_encode($quantities); ?>,
                        backgroundColor: 'rgba(54, 162, 235, 0.2)',
                        borderColor: 'rgba(54, 162, 235, 1)',
                        borderWidth: 2,
                        yAxisID: 'y-quantity',
                        tension: 0.1
                    },
                    {
                        label: '销售收入',
                        data: <?php echo json_encode($revenues); ?>,
                        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-quantity': {
                        type: 'linear',
                        position: 'left',
                        title: {
                            display: true,
                            text: '销售数量'
                        },
                        beginAtZero: true
                    },
                    'y-revenue': {
                        type: 'linear',
                        position: 'right',
                        title: {
                            display: true,
                            text: '销售收入'
                        },
                        beginAtZero: true,
                        grid: {
                            drawOnChartArea: false
                        }
                    }
                }
            }
        });
    </script>
    <?php
}

/**
 * 渲染产品类别销售分布图
 * 
 * @param array $categories 类别名称
 * @param array $quantities 类别销售数量
 * @param array $revenues 类别销售收入
 * @return void
 */
function renderProductCategorySalesChart($categories, $quantities, $revenues) {
    ?>
    <div class="chart-container">
        <div class="chart-header">
            <h2 class="chart-title">产品类别销售分布</h2>
        </div>
        <style>
            .pie-charts-container {
                display: flex;
                flex-direction: row;
                justify-content: space-between;
                margin-bottom: 20px;
            }
            .pie-chart-wrapper {
                flex: 0 0 48%;
                max-width: 48%;
            }
        </style>
        <div class="pie-charts-container">
            <div class="pie-chart-wrapper">
                <h3 style="text-align: center; margin-bottom: 15px;">产品类别销售数量分布</h3>
                <canvas id="categoryQuantityChart"></canvas>
            </div>
            <div class="pie-chart-wrapper">
                <h3 style="text-align: center; margin-bottom: 15px;">产品类别销售收入分布</h3>
                <canvas id="categoryRevenueChart"></canvas>
            </div>
        </div>
    </div>
    
    <script>
        // 产品类别数量分布图
        var categoryQuantityCtx = document.getElementById('categoryQuantityChart').getContext('2d');
        var categoryQuantityChart = new Chart(categoryQuantityCtx, {
            type: 'pie',
            data: {
                labels: <?php echo json_encode($categories); ?>,
                datasets: [{
                    data: <?php echo json_encode($quantities); ?>,
                    backgroundColor: [
                        'rgba(255, 99, 132, 0.7)',
                        'rgba(54, 162, 235, 0.7)',
                        'rgba(255, 206, 86, 0.7)',
                        'rgba(75, 192, 192, 0.7)',
                        'rgba(153, 102, 255, 0.7)',
                        'rgba(255, 159, 64, 0.7)'
                    ],
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: true,
                plugins: {
                    legend: {
                        position: 'bottom',
                    }
                }
            }
        });
        
        // 产品类别收入分布图
        var categoryRevenueCtx = document.getElementById('categoryRevenueChart').getContext('2d');
        var categoryRevenueChart = new Chart(categoryRevenueCtx, {
            type: 'pie',
            data: {
                labels: <?php echo json_encode($categories); ?>,
                datasets: [{
                    data: <?php echo json_encode($revenues); ?>,
                    backgroundColor: [
                        'rgba(255, 99, 132, 0.7)',
                        'rgba(54, 162, 235, 0.7)',
                        'rgba(255, 206, 86, 0.7)',
                        'rgba(75, 192, 192, 0.7)',
                        'rgba(153, 102, 255, 0.7)',
                        'rgba(255, 159, 64, 0.7)'
                    ],
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: true,
                plugins: {
                    legend: {
                        position: 'bottom',
                    }
                }
            }
        });
    </script>
    <?php
}

/**
 * 渲染产品与地区关联分析表格
 * 
 * @param mysqli_result $product_region_data 产品与地区关联数据
 * @return void
 */
function renderProductRegionAnalysisTable($product_region_data) {
    ?>
    <div class="chart-container">
        <div class="chart-header">
            <h2 class="chart-title">产品地区关联分析</h2>
        </div>
        <table class="data-table">
            <thead>
                <tr>
                    <th>产品名称</th>
                    <th>国家/地区</th>
                    <th>销售数量</th>
                    <th>销售收入</th>
                </tr>
            </thead>
            <tbody>
                <?php while ($row = $product_region_data->fetch_assoc()): ?>
                <tr>
                    <td><?php echo htmlspecialchars($row['ProductName']); ?></td>
                    <td><?php echo htmlspecialchars($row['countryName']); ?></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>
    <?php
}