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 限制返回的产品数量
 * @param mixed $employee_filter 业务员过滤条件
 * @return mysqli_result 热门产品数据结果集
 */
function getTopProducts($conn, $start_date, $end_date, $limit = 5, $employee_filter = null) {
    $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";
    
    if ($employee_filter !== null) {
        $sql .= " JOIN customer c ON o.customer_id = c.id";
    }
    
    $sql .= " WHERE o.order_date BETWEEN '$start_date' AND '$end_date'";
    
    // 添加业务员过滤
    if ($employee_filter !== null) {
        if (is_array($employee_filter)) {
            if (count($employee_filter) > 0) {
                $employee_ids = implode(',', $employee_filter);
                $sql .= " AND c.cs_belong IN ($employee_ids)";
            }
        } else {
            $sql .= " AND c.cs_belong = $employee_filter";
        }
    }
    
    $sql .= " GROUP BY oi.product_id
            ORDER BY total_revenue DESC
            LIMIT $limit";
    
    $result = $conn->query($sql);
    return $result;
}

/**
 * 获取产品销售趋势
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @param int $product_id 产品ID，为0时获取所有产品的总体趋势
 * @param string $period 时间粒度 (day/week/month)
 * @param mixed $employee_filter 业务员过滤条件
 * @return mysqli_result 产品销售趋势数据结果集
 */
function getProductSalesTrend($conn, $start_date, $end_date, $product_id = 0, $period = 'month', $employee_filter = null) {
    $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 ($employee_filter !== null) {
        $sql .= " JOIN customer c ON o.customer_id = c.id";
    }
    
    $sql .= " WHERE o.order_date BETWEEN '$start_date' AND '$end_date'";
    
    if ($product_id > 0) {
        $sql .= " AND oi.product_id = $product_id";
    }
    
    // 添加业务员过滤
    if ($employee_filter !== null) {
        if (is_array($employee_filter)) {
            if (count($employee_filter) > 0) {
                $employee_ids = implode(',', $employee_filter);
                $sql .= " AND c.cs_belong IN ($employee_ids)";
            }
        } else {
            $sql .= " AND c.cs_belong = $employee_filter";
        }
    }
    
    $sql .= " GROUP BY time_period
              ORDER BY MIN(o.order_date)";
    
    $result = $conn->query($sql);
    return $result;
}

/**
 * 获取产品类别销售分布
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @param mixed $employee_filter 业务员过滤条件
 * @return mysqli_result 产品类别销售分布数据结果集
 */
function getProductCategorySales($conn, $start_date, $end_date, $employee_filter = null) {
    $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";
    
    if ($employee_filter !== null) {
        $sql .= " JOIN customer c ON o.customer_id = c.id";
    }
    
    $sql .= " WHERE o.order_date BETWEEN '$start_date' AND '$end_date'";
    
    // 添加业务员过滤
    if ($employee_filter !== null) {
        if (is_array($employee_filter)) {
            if (count($employee_filter) > 0) {
                $employee_ids = implode(',', $employee_filter);
                $sql .= " AND c.cs_belong IN ($employee_ids)";
            }
        } else {
            $sql .= " AND c.cs_belong = $employee_filter";
        }
    }
    
    $sql .= " GROUP BY p.category_id
            ORDER BY total_revenue DESC";
    
    $result = $conn->query($sql);
    return $result;
}

/**
 * 获取产品与地区关联分析
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @param mixed $employee_filter 业务员过滤条件
 * @param int $limit 限制返回的产品-地区组合数量
 * @return mysqli_result 产品与地区关联分析数据结果集
 */
function getProductRegionAnalysis($conn, $start_date, $end_date, $employee_filter = null, $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";
    
    $sql .= " WHERE o.order_date BETWEEN '$start_date' AND '$end_date'";
    
    // 添加业务员过滤
    if ($employee_filter !== null) {
        if (is_array($employee_filter)) {
            if (count($employee_filter) > 0) {
                $employee_ids = implode(',', $employee_filter);
                $sql .= " AND cu.cs_belong IN ($employee_ids)";
            }
        } else {
            $sql .= " AND cu.cs_belong = $employee_filter";
        }
    }
    
    $sql .= " GROUP BY oi.product_id, cu.cs_country
            ORDER BY total_revenue DESC
            LIMIT $limit";
    
    $result = $conn->query($sql);
    return $result;
}

/**
 * 获取产品销售概览数据
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @param int $category_filter 产品分类过滤
 * @param mixed $employee_filter 业务员过滤条件
 * @return array 产品销售概览数据
 */
function getProductSalesOverview($conn, $start_date, $end_date, $category_filter = 0, $employee_filter = null) {
    $where_clause = "WHERE o.order_date BETWEEN '$start_date' AND '$end_date'";
    
    if ($category_filter > 0) {
        $where_clause .= " AND p.category_id = $category_filter";
    }
    
    $sql = "SELECT 
                COUNT(DISTINCT oi.product_id) as total_products,
                SUM(oi.quantity) as total_quantity,
                SUM(oi.total_price) as total_revenue,
                AVG(oi.unit_price) as avg_unit_price,
                COUNT(DISTINCT o.id) as total_orders,
                SUM(oi.total_price) / COUNT(DISTINCT o.id) as avg_order_value,
                COUNT(DISTINCT o.customer_id) as total_customers
            FROM order_items oi
            JOIN orders o ON oi.order_id = o.id
            JOIN products p ON oi.product_id = p.id";
    
    if ($employee_filter !== null) {
        $sql .= " JOIN customer c ON o.customer_id = c.id";
    }
    
    $sql .= " $where_clause";
    
    // 添加业务员过滤
    if ($employee_filter !== null) {
        if (is_array($employee_filter)) {
            if (count($employee_filter) > 0) {
                $employee_ids = implode(',', $employee_filter);
                $sql .= " AND c.cs_belong IN ($employee_ids)";
            }
        } else {
            $sql .= " AND c.cs_belong = $employee_filter";
        }
    }
    
    $result = $conn->query($sql);
    return $result->fetch_assoc();
}

/**
 * 获取产品价格趋势分析
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @param int $product_id 产品ID
 * @param string $period 时间粒度
 * @param mixed $employee_filter 业务员过滤条件
 * @return mysqli_result 产品价格趋势数据
 */
function getProductPriceTrendAnalysis($conn, $start_date, $end_date, $product_id = 0, $period = 'month', $employee_filter = null) {
    $groupFormat = getPeriodFormat($period);
    
    $sql = "SELECT 
                DATE_FORMAT(o.order_date, '$groupFormat') as time_period,
                AVG(oi.unit_price) as avg_price,
                MIN(oi.unit_price) as min_price,
                MAX(oi.unit_price) as max_price
            FROM order_items oi
            JOIN orders o ON oi.order_id = o.id";
    
    if ($employee_filter !== null) {
        $sql .= " JOIN customer c ON o.customer_id = c.id";
    }
    
    $sql .= " WHERE o.order_date BETWEEN '$start_date' AND '$end_date'";
    
    if ($product_id > 0) {
        $sql .= " AND oi.product_id = $product_id";
    }
    
    // 添加业务员过滤
    if ($employee_filter !== null) {
        if (is_array($employee_filter)) {
            if (count($employee_filter) > 0) {
                $employee_ids = implode(',', $employee_filter);
                $sql .= " AND c.cs_belong IN ($employee_ids)";
            }
        } else {
            $sql .= " AND c.cs_belong = $employee_filter";
        }
    }
    
    $sql .= " GROUP BY time_period ORDER BY MIN(o.order_date)";
    
    $result = $conn->query($sql);
    return $result;
}

/**
 * 获取产品季节性分析
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @param int $product_id 产品ID
 * @param mixed $employee_filter 业务员过滤条件
 * @return mysqli_result 产品季节性分析数据
 */
function getProductSeasonalityAnalysis($conn, $start_date, $end_date, $product_id = 0, $employee_filter = null) {
    $sql = "SELECT 
                MONTH(o.order_date) as month,
                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 ($employee_filter !== null) {
        $sql .= " JOIN customer c ON o.customer_id = c.id";
    }
    
    $sql .= " WHERE o.order_date BETWEEN '$start_date' AND '$end_date'";
    
    if ($product_id > 0) {
        $sql .= " AND oi.product_id = $product_id";
    }
    
    // 添加业务员过滤
    if ($employee_filter !== null) {
        if (is_array($employee_filter)) {
            if (count($employee_filter) > 0) {
                $employee_ids = implode(',', $employee_filter);
                $sql .= " AND c.cs_belong IN ($employee_ids)";
            }
        } else {
            $sql .= " AND c.cs_belong = $employee_filter";
        }
    }
    
    $sql .= " GROUP BY MONTH(o.order_date)
              ORDER BY MONTH(o.order_date)";
    
    $result = $conn->query($sql);
    return $result;
}

/**
 * 获取产品客户细分分析
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @param int $product_id 产品ID
 * @param mixed $employee_filter 业务员过滤条件
 * @return mysqli_result 产品客户细分分析数据
 */
function getProductCustomerSegmentAnalysis($conn, $start_date, $end_date, $product_id = 0, $employee_filter = null) {
    $sql = "SELECT 
                ct.businessType as segment_name,
                COUNT(DISTINCT o.customer_id) as customer_count,
                SUM(oi.quantity) as total_quantity,
                SUM(oi.total_price) as total_revenue,
                AVG(oi.unit_price) as avg_unit_price
            FROM order_items oi
            JOIN orders o ON oi.order_id = o.id
            JOIN customer c ON o.customer_id = c.id
            JOIN clienttype ct ON c.cs_type = ct.id";
    
    $sql .= " WHERE o.order_date BETWEEN '$start_date' AND '$end_date'";
    
    if ($product_id > 0) {
        $sql .= " AND oi.product_id = $product_id";
    }
    
    // 添加业务员过滤
    if ($employee_filter !== null) {
        if (is_array($employee_filter)) {
            if (count($employee_filter) > 0) {
                $employee_ids = implode(',', $employee_filter);
                $sql .= " AND c.cs_belong IN ($employee_ids)";
            }
        } else {
            $sql .= " AND c.cs_belong = $employee_filter";
        }
    }
    
    $sql .= " GROUP BY ct.id";
    
    $result = $conn->query($sql);
    return $result;
}

/**
 * 获取产品分类列表
 * 
 * @param mysqli $conn 数据库连接
 * @return mysqli_result 产品分类数据结果集
 */
function getProductCategories($conn) {
    $sql = "SELECT 
                id,
                parent_id,
                name,
                description,
                sort_order
            FROM product_categories 
            WHERE status = 1
            ORDER BY sort_order ASC, id ASC";
    
    $stmt = $conn->prepare($sql);
    $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
}

/**
 * 渲染产品销售概览
 */
function renderProductSalesOverview($overview) {
    // 处理可能为null的值
    $total_products = isset($overview['total_products']) ? $overview['total_products'] : 0;
    $total_quantity = isset($overview['total_quantity']) ? $overview['total_quantity'] : 0;
    $total_revenue = isset($overview['total_revenue']) ? $overview['total_revenue'] : 0;
    $avg_unit_price = isset($overview['avg_unit_price']) ? $overview['avg_unit_price'] : 0;
    $total_orders = isset($overview['total_orders']) ? $overview['total_orders'] : 0;
    $avg_order_value = isset($overview['avg_order_value']) ? $overview['avg_order_value'] : 0;
    ?>
    <div class="stats-card-container">
        <div class="stats-card">
            <div class="stats-card-header">
                <h3>总销售产品数</h3>
            </div>
            <div class="stats-card-body">
                <div class="stats-card-value"><?php echo number_format($total_products); ?></div>
                <div class="stats-card-subtitle">种类</div>
            </div>
        </div>
        
        <div class="stats-card">
            <div class="stats-card-header">
                <h3>总销售数量</h3>
            </div>
            <div class="stats-card-body">
                <div class="stats-card-value"><?php echo number_format($total_quantity); ?></div>
                <div class="stats-card-subtitle">件</div>
            </div>
        </div>
        
        <div class="stats-card">
            <div class="stats-card-header">
                <h3>总销售收入</h3>
            </div>
            <div class="stats-card-body">
                <div class="stats-card-value">¥<?php echo number_format($total_revenue, 2); ?></div>
                <div class="stats-card-subtitle">元</div>
            </div>
        </div>
        
        <div class="stats-card">
            <div class="stats-card-header">
                <h3>平均单价</h3>
            </div>
            <div class="stats-card-body">
                <div class="stats-card-value">¥<?php echo number_format($avg_unit_price, 2); ?></div>
                <div class="stats-card-subtitle">元/件</div>
            </div>
        </div>
        
        <div class="stats-card">
            <div class="stats-card-header">
                <h3>订单数量</h3>
            </div>
            <div class="stats-card-body">
                <div class="stats-card-value"><?php echo number_format($total_orders); ?></div>
                <div class="stats-card-subtitle">笔</div>
            </div>
        </div>
        
        <div class="stats-card">
            <div class="stats-card-header">
                <h3>平均订单金额</h3>
            </div>
            <div class="stats-card-body">
                <div class="stats-card-value">¥<?php echo number_format($avg_order_value, 2); ?></div>
                <div class="stats-card-subtitle">元/订单</div>
            </div>
        </div>
    </div>
    <?php
}

/**
 * 渲染产品价格趋势图表
 */
function renderProductPriceTrendChart($price_trend_data) {
    $time_periods = [];
    $avg_prices = [];
    $min_prices = [];
    $max_prices = [];
    
    while ($row = $price_trend_data->fetch_assoc()) {
        $time_periods[] = $row['time_period'];
        $avg_prices[] = round($row['avg_price'], 2);
        $min_prices[] = round($row['min_price'], 2);
        $max_prices[] = round($row['max_price'], 2);
    }
    ?>
    <div class="chart-container">
        <div class="chart-header">
            <h2 class="chart-title">产品价格趋势分析</h2>
        </div>
        <canvas id="priceTrendChart"></canvas>
    </div>
    
    <script>
        var priceTrendCtx = document.getElementById('priceTrendChart').getContext('2d');
        new Chart(priceTrendCtx, {
            type: 'line',
            data: {
                labels: <?php echo json_encode($time_periods); ?>,
                datasets: [
                    {
                        label: '平均价格',
                        data: <?php echo json_encode($avg_prices); ?>,
                        borderColor: 'rgb(54, 162, 235)',
                        backgroundColor: 'rgba(54, 162, 235, 0.1)',
                        borderWidth: 2,
                        fill: false
                    },
                    {
                        label: '最低价格',
                        data: <?php echo json_encode($min_prices); ?>,
                        borderColor: 'rgb(75, 192, 192)',
                        backgroundColor: 'rgba(75, 192, 192, 0.1)',
                        borderWidth: 2,
                        fill: false
                    },
                    {
                        label: '最高价格',
                        data: <?php echo json_encode($max_prices); ?>,
                        borderColor: 'rgb(255, 99, 132)',
                        backgroundColor: 'rgba(255, 99, 132, 0.1)',
                        borderWidth: 2,
                        fill: false
                    }
                ]
            },
            options: {
                responsive: true,
                scales: {
                    y: {
                        beginAtZero: true,
                        title: {
                            display: true,
                            text: '价格 (元)'
                        }
                    }
                },
                plugins: {
                    title: {
                        display: true,
                        text: '产品价格变化趋势'
                    }
                }
            }
        });
    </script>
    <?php
}

/**
 * 渲染产品季节性分析图表
 */
function renderProductSeasonalityChart($seasonality_data) {
    $months = [];
    $quantities = [];
    $revenues = [];
    $order_counts = [];
    
    while ($row = $seasonality_data->fetch_assoc()) {
        $months[] = date('n月', mktime(0, 0, 0, $row['month'], 1));
        $quantities[] = (int)$row['total_quantity'];
        $revenues[] = round($row['total_revenue'], 2);
        $order_counts[] = (int)$row['order_count'];
    }
    ?>
    <div class="chart-container">
        <div class="chart-header">
            <h2 class="chart-title">产品季节性分析</h2>
        </div>
        <canvas id="seasonalityChart"></canvas>
    </div>
    
    <script>
        var seasonalityCtx = document.getElementById('seasonalityChart').getContext('2d');
        new Chart(seasonalityCtx, {
            type: 'bar',
            data: {
                labels: <?php echo json_encode($months); ?>,
                datasets: [
                    {
                        label: '销售数量',
                        data: <?php echo json_encode($quantities); ?>,
                        backgroundColor: 'rgba(54, 162, 235, 0.5)',
                        borderColor: 'rgb(54, 162, 235)',
                        borderWidth: 1,
                        yAxisID: 'y-quantity'
                    },
                    {
                        label: '销售收入',
                        data: <?php echo json_encode($revenues); ?>,
                        backgroundColor: 'rgba(255, 99, 132, 0.5)',
                        borderColor: 'rgb(255, 99, 132)',
                        borderWidth: 1,
                        yAxisID: 'y-revenue'
                    },
                    {
                        label: '订单数',
                        data: <?php echo json_encode($order_counts); ?>,
                        type: 'line',
                        fill: false,
                        borderColor: 'rgb(75, 192, 192)',
                        tension: 0.1,
                        yAxisID: 'y-orders'
                    }
                ]
            },
            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
                        }
                    },
                    'y-orders': {
                        type: 'linear',
                        position: 'right',
                        title: {
                            display: true,
                            text: '订单数'
                        },
                        grid: {
                            drawOnChartArea: false
                        }
                    }
                },
                plugins: {
                    title: {
                        display: true,
                        text: '产品销售季节性分布'
                    }
                }
            }
        });
    </script>
    <?php
}

/**
 * 渲染产品客户细分分析图表
 */
function renderProductCustomerSegmentChart($segment_data) {
    $segments = [];
    $customer_counts = [];
    $revenues = [];
    $avg_prices = [];
    
    while ($row = $segment_data->fetch_assoc()) {
        $segments[] = $row['segment_name'];
        $customer_counts[] = (int)$row['customer_count'];
        $revenues[] = round($row['total_revenue'], 2);
        $avg_prices[] = round($row['avg_unit_price'], 2);
    }
    ?>
    <div class="chart-container">
        <div class="chart-header">
            <h2 class="chart-title">产品客户细分分析</h2>
        </div>
        <div class="chart-row">
            <div class="chart-column">
                <canvas id="customerSegmentChart1"></canvas>
            </div>
            <div class="chart-column">
                <canvas id="customerSegmentChart2"></canvas>
            </div>
        </div>
    </div>
    
    <script>
        // 客户数量和收入分布
        var segmentCtx1 = document.getElementById('customerSegmentChart1').getContext('2d');
        new Chart(segmentCtx1, {
            type: 'bar',
            data: {
                labels: <?php echo json_encode($segments); ?>,
                datasets: [
                    {
                        label: '客户数量',
                        data: <?php echo json_encode($customer_counts); ?>,
                        backgroundColor: 'rgba(54, 162, 235, 0.5)',
                        borderColor: 'rgb(54, 162, 235)',
                        borderWidth: 1,
                        yAxisID: 'y-customers'
                    },
                    {
                        label: '销售收入',
                        data: <?php echo json_encode($revenues); ?>,
                        backgroundColor: 'rgba(255, 99, 132, 0.5)',
                        borderColor: 'rgb(255, 99, 132)',
                        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
                        }
                    }
                },
                plugins: {
                    title: {
                        display: true,
                        text: '客户细分分布'
                    }
                }
            }
        });

        // 平均单价分布
        var segmentCtx2 = document.getElementById('customerSegmentChart2').getContext('2d');
        new Chart(segmentCtx2, {
            type: 'radar',
            data: {
                labels: <?php echo json_encode($segments); ?>,
                datasets: [{
                    label: '平均单价',
                    data: <?php echo json_encode($avg_prices); ?>,
                    backgroundColor: 'rgba(75, 192, 192, 0.2)',
                    borderColor: 'rgb(75, 192, 192)',
                    pointBackgroundColor: 'rgb(75, 192, 192)',
                    pointBorderColor: '#fff',
                    pointHoverBackgroundColor: '#fff',
                    pointHoverBorderColor: 'rgb(75, 192, 192)'
                }]
            },
            options: {
                responsive: true,
                plugins: {
                    title: {
                        display: true,
                        text: '客户细分平均单价分布'
                    }
                }
            }
        });
    </script>
    <?php
}

/**
 * 获取产品增长率分析
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @param string $period 时间粒度
 * @param mixed $employee_filter 业务员过滤条件
 * @return array 产品增长率分析数据
 */
function getProductGrowthAnalysis($conn, $start_date, $end_date, $period = 'month', $employee_filter = null) {
    $groupFormat = getPeriodFormat($period);
    
    // 获取当前期间的数据
    $sql = "SELECT 
                p.ProductName,
                SUM(oi.total_price) as current_revenue,
                SUM(oi.quantity) as current_quantity,
                COUNT(DISTINCT o.id) as current_orders
            FROM order_items oi
            JOIN products p ON oi.product_id = p.id
            JOIN orders o ON oi.order_id = o.id";
    
    if ($employee_filter !== null) {
        $sql .= " JOIN customer c ON o.customer_id = c.id";
    }
    
    $sql .= " WHERE o.order_date BETWEEN '$start_date' AND '$end_date'";
    
    // 添加业务员过滤
    if ($employee_filter !== null) {
        if (is_array($employee_filter)) {
            if (count($employee_filter) > 0) {
                $employee_ids = implode(',', $employee_filter);
                $sql .= " AND c.cs_belong IN ($employee_ids)";
            }
        } else {
            $sql .= " AND c.cs_belong = $employee_filter";
        }
    }
    
    $sql .= " GROUP BY oi.product_id
            HAVING current_revenue > 0
            ORDER BY current_revenue DESC
            LIMIT 10";
    
    $current_data = $conn->query($sql);
    
    // 计算上一个时间段
    $date1 = new DateTime($start_date);
    $date2 = new DateTime($end_date);
    $interval = $date1->diff($date2);
    $days_diff = $interval->days;
    
    $prev_end = $date1->format('Y-m-d');
    $prev_start = $date1->modify("-{$days_diff} days")->format('Y-m-d');
    
    // 获取上一期间的数据
    $sql = "SELECT 
                p.ProductName,
                SUM(oi.total_price) as prev_revenue,
                SUM(oi.quantity) as prev_quantity,
                COUNT(DISTINCT o.id) as prev_orders
            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 '$prev_start' AND '$prev_end'
            GROUP BY oi.product_id";
    
    $prev_result = $conn->query($sql);
    
    $prev_data = [];
    while ($row = $prev_result->fetch_assoc()) {
        $prev_data[$row['ProductName']] = $row;
    }
    
    $growth_data = [];
    while ($current = $current_data->fetch_assoc()) {
        $product_name = $current['ProductName'];
        $prev = isset($prev_data[$product_name]) ? $prev_data[$product_name] : [
            'prev_revenue' => 0,
            'prev_quantity' => 0,
            'prev_orders' => 0
        ];
        
        $growth_data[] = [
            'product_name' => $product_name,
            'current_revenue' => $current['current_revenue'],
            'current_quantity' => $current['current_quantity'],
            'current_orders' => $current['current_orders'],
            'prev_revenue' => $prev['prev_revenue'],
            'prev_quantity' => $prev['prev_quantity'],
            'prev_orders' => $prev['prev_orders'],
            'revenue_growth' => calculateGrowthRate($current['current_revenue'], $prev['prev_revenue']),
            'quantity_growth' => calculateGrowthRate($current['current_quantity'], $prev['prev_quantity']),
            'orders_growth' => calculateGrowthRate($current['current_orders'], $prev['prev_orders'])
        ];
    }
    
    return $growth_data;
}

/**
 * 计算增长率
 */
function calculateGrowthRate($current, $previous) {
    if ($previous == 0) {
        return $current > 0 ? 100 : 0;
    }
    return round((($current - $previous) / $previous) * 100, 2);
}

/**
 * 渲染产品增长率分析
 */
function renderProductGrowthAnalysis($growth_data) {
    ?>
    <div class="chart-container">
        <div class="chart-header">
            <h2 class="chart-title">产品增长率分析</h2>
            <div class="chart-subtitle">与上一时期相比</div>
        </div>
        <table class="data-table">
            <thead>
                <tr>
                    <th>产品名称</th>
                    <th>当期收入</th>
                    <th>收入增长率</th>
                    <th>当期销量</th>
                    <th>销量增长率</th>
                    <th>当期订单数</th>
                    <th>订单增长率</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($growth_data as $row): ?>
                <tr>
                    <td><?php echo htmlspecialchars($row['product_name']); ?></td>
                    <td>¥<?php echo number_format($row['current_revenue'], 2); ?></td>
                    <td class="<?php echo $row['revenue_growth'] >= 0 ? 'positive' : 'negative'; ?>">
                        <?php echo ($row['revenue_growth'] >= 0 ? '+' : '') . $row['revenue_growth']; ?>%
                    </td>
                    <td><?php echo number_format($row['current_quantity']); ?></td>
                    <td class="<?php echo $row['quantity_growth'] >= 0 ? 'positive' : 'negative'; ?>">
                        <?php echo ($row['quantity_growth'] >= 0 ? '+' : '') . $row['quantity_growth']; ?>%
                    </td>
                    <td><?php echo number_format($row['current_orders']); ?></td>
                    <td class="<?php echo $row['orders_growth'] >= 0 ? 'positive' : 'negative'; ?>">
                        <?php echo ($row['orders_growth'] >= 0 ? '+' : '') . $row['orders_growth']; ?>%
                    </td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>
    
    <style>
    .positive {
        color: #4CAF50;
        font-weight: bold;
    }
    .negative {
        color: #f44336;
        font-weight: bold;
    }
    .chart-subtitle {
        font-size: 14px;
        color: #666;
        margin-top: 5px;
    }
    </style>
    <?php
}

/**
 * 获取产品购买频率分析
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @param mixed $employee_filter 业务员过滤条件
 * @return mysqli_result 产品购买频率分析数据
 */
function getProductPurchaseFrequency($conn, $start_date, $end_date, $employee_filter = null) {
    $sql = "SELECT 
                p.ProductName,
                COUNT(DISTINCT o.id) as order_count,
                COUNT(DISTINCT o.customer_id) as customer_count,
                COUNT(DISTINCT o.id) / COUNT(DISTINCT o.customer_id) as purchase_frequency,
                AVG(
                    CASE 
                        WHEN next_order.next_date IS NOT NULL 
                        THEN DATEDIFF(next_order.next_date, o.order_date)
                        ELSE NULL 
                    END
                ) as avg_days_between_orders
            FROM order_items oi
            JOIN products p ON oi.product_id = p.id
            JOIN orders o ON oi.order_id = o.id";
    
    if ($employee_filter !== null) {
        $sql .= " JOIN customer c ON o.customer_id = c.id";
    }
    
    $sql .= " LEFT JOIN (
                SELECT 
                    o1.customer_id,
                    o1.order_date,
                    MIN(o2.order_date) as next_date
                FROM orders o1
                LEFT JOIN orders o2 ON o1.customer_id = o2.customer_id 
                    AND o2.order_date > o1.order_date
                WHERE o1.order_date BETWEEN '$start_date' AND '$end_date'
                GROUP BY o1.customer_id, o1.order_date
            ) next_order ON o.customer_id = next_order.customer_id 
                AND o.order_date = next_order.order_date
            WHERE o.order_date BETWEEN '$start_date' AND '$end_date'";
    
    // 添加业务员过滤
    if ($employee_filter !== null) {
        if (is_array($employee_filter)) {
            if (count($employee_filter) > 0) {
                $employee_ids = implode(',', $employee_filter);
                $sql .= " AND c.cs_belong IN ($employee_ids)";
            }
        } else {
            $sql .= " AND c.cs_belong = $employee_filter";
        }
    }
    
    $sql .= " GROUP BY p.id
            HAVING order_count > 1
            ORDER BY purchase_frequency DESC
            LIMIT 10";
            
    $result = $conn->query($sql);
    return $result;
}

/**
 * 渲染产品购买频率分析
 */
function renderProductPurchaseFrequency($frequency_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>
                    <th>平均购买间隔(天)</th>
                </tr>
            </thead>
            <tbody>
                <?php while ($row = $frequency_data->fetch_assoc()): ?>
                <tr>
                    <td><?php echo htmlspecialchars($row['ProductName']); ?></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['purchase_frequency'], 2); ?>次/客户</td>
                    <td><?php echo $row['avg_days_between_orders'] ? number_format($row['avg_days_between_orders'], 1) : '-'; ?></td>
                </tr>
                <?php endwhile; ?>
            </tbody>
        </table>
    </div>
    <?php
}

/**
 * 获取新客户购买产品明细
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @param int $category_filter 分类过滤
 * @param mixed $employee_filter 业务员过滤条件
 * @return array 新客户购买产品明细数据
 */
function getNewCustomerProductPurchases($conn, $start_date, $end_date, $category_filter = 0, $employee_filter = null) {
    // 获取符合条件的新客户订单
    $sql = "SELECT 
                p.ProductName,
                p.id as product_id,
                pc.name as category_name,
                COUNT(DISTINCT o.customer_id) as customer_count,
                COUNT(DISTINCT o.id) as order_count,
                SUM(oi.quantity) as total_quantity,
                SUM(oi.total_price) as total_revenue,
                AVG(oi.unit_price) as avg_unit_price
            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
            JOIN customer c ON o.customer_id = c.id
            WHERE o.order_date BETWEEN '$start_date' AND '$end_date'
            AND o.id IN (
                SELECT MIN(id) 
                FROM orders 
                WHERE order_date BETWEEN '$start_date' AND '$end_date'";
    
    if ($employee_filter !== null) {
        $sql .= " AND customer_id IN (
                    SELECT id FROM customer WHERE ";
        
        if (is_array($employee_filter)) {
            if (count($employee_filter) > 0) {
                $employee_ids = implode(',', $employee_filter);
                $sql .= "cs_belong IN ($employee_ids))";
            } else {
                $sql .= "1=1)";
            }
        } else {
            $sql .= "cs_belong = $employee_filter)";
        }
    }
    
    $sql .= " GROUP BY customer_id
            )";
    
    if ($category_filter > 0) {
        $sql .= " AND p.category_id = $category_filter";
    }
    
    // 添加业务员过滤
    if ($employee_filter !== null) {
        if (is_array($employee_filter)) {
            if (count($employee_filter) > 0) {
                $employee_ids = implode(',', $employee_filter);
                $sql .= " AND c.cs_belong IN ($employee_ids)";
            }
        } else {
            $sql .= " AND c.cs_belong = $employee_filter";
        }
    }
    
    $sql .= " GROUP BY p.id
              ORDER BY customer_count DESC, total_revenue DESC";
    
    $result = $conn->query($sql);
    
    // 查询新客户总数
    $sql_count = "SELECT 
                    COUNT(DISTINCT o.customer_id) as total_new_customers
                FROM orders o
                JOIN customer c ON o.customer_id = c.id
                WHERE o.order_date BETWEEN '$start_date' AND '$end_date'
                AND o.id IN (
                    SELECT MIN(id) 
                    FROM orders 
                    WHERE order_date BETWEEN '$start_date' AND '$end_date'";
    
    if ($employee_filter !== null) {
        $sql_count .= " AND customer_id IN (
                        SELECT id FROM customer WHERE ";
        
        if (is_array($employee_filter)) {
            if (count($employee_filter) > 0) {
                $employee_ids = implode(',', $employee_filter);
                $sql_count .= "cs_belong IN ($employee_ids))";
            } else {
                $sql_count .= "1=1)";
            }
        } else {
            $sql_count .= "cs_belong = $employee_filter)";
        }
    }
    
    $sql_count .= " GROUP BY customer_id
                )";
    
    if ($employee_filter !== null) {
        if (is_array($employee_filter)) {
            if (count($employee_filter) > 0) {
                $employee_ids = implode(',', $employee_filter);
                $sql_count .= " AND c.cs_belong IN ($employee_ids)";
            }
        } else {
            $sql_count .= " AND c.cs_belong = $employee_filter";
        }
    }
    
    $result_count = $conn->query($sql_count);
    $count_row = $result_count->fetch_assoc();
    $new_customer_count = $count_row ? $count_row['total_new_customers'] : 0;
    
    // 格式化返回数据
    $products = [];
    if ($result) {
        while ($row = $result->fetch_assoc()) {
            $products[] = [
                'product_name' => $row['ProductName'],
                'product_id' => $row['product_id'],
                'category_name' => $row['category_name'],
                'customer_count' => $row['customer_count'],
                'order_count' => $row['order_count'],
                'total_quantity' => $row['total_quantity'],
                'total_revenue' => $row['total_revenue'],
                'avg_price' => $row['avg_unit_price']
            ];
        }
    }
    
    return [
        'products' => $products,
        'new_customer_count' => $new_customer_count
    ];
}