mtb_crm_asp/statistics_region.php

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

require_once 'statistics_utils.php';

/**
 * 获取客户国家分布
 * 
 * @param mysqli $conn 数据库连接
 * @param array|int|null $employee_filter 业务员ID或ID数组，用于过滤数据
 * @return mysqli_result 客户国家分布数据结果集
 */
function getCustomerCountryDistribution($conn, $employee_filter = null) {
    $employee_condition = "";
    
    // 如果有业务员过滤条件
    if ($employee_filter !== null) {
        if (is_array($employee_filter) && !empty($employee_filter)) {
            // 处理数组形式的业务员ID列表
            $emp_ids = [];
            foreach ($employee_filter as $emp_id) {
                if (is_numeric($emp_id)) {
                    $emp_ids[] = intval($emp_id);
                }
            }
            if (!empty($emp_ids)) {
                $emp_ids_str = implode(',', $emp_ids);
                $employee_condition = " WHERE cu.cs_belong IN ($emp_ids_str)";
            }
        } else if (is_numeric($employee_filter) && $employee_filter > 0) {
            // 处理单个业务员ID
            $employee_filter = intval($employee_filter);
            $employee_condition = " WHERE cu.cs_belong = $employee_filter";
        }
    }
    
    $sql = "SELECT 
                c.countryName, 
                COUNT(cu.id) as customer_count
            FROM customer cu
            JOIN country c ON cu.cs_country = c.id
            $employee_condition
            GROUP BY cu.cs_country
            ORDER BY customer_count DESC
            LIMIT 10";
    
    return $conn->query($sql);
}

/**
 * 获取不同地区的订单数量
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @param array|int|null $employee_filter 业务员ID或ID数组，用于过滤数据
 * @return mysqli_result 地区订单数据结果集
 */
function getOrdersByRegion($conn, $start_date, $end_date, $employee_filter = null) {
    $employee_condition = "";
    $params = [$start_date, $end_date];
    $types = "ss";
    
    // 如果有业务员过滤条件
    if ($employee_filter !== null) {
        if (is_array($employee_filter) && !empty($employee_filter)) {
            // 处理数组形式的业务员ID列表
            $emp_ids = [];
            foreach ($employee_filter as $emp_id) {
                if (is_numeric($emp_id)) {
                    $emp_ids[] = intval($emp_id);
                }
            }
            if (!empty($emp_ids)) {
                $emp_ids_str = implode(',', $emp_ids);
                $employee_condition = " AND cu.cs_belong IN ($emp_ids_str)";
            }
        } else if (is_numeric($employee_filter) && $employee_filter > 0) {
            // 处理单个业务员ID
            $employee_filter = intval($employee_filter);
            $employee_condition = " AND cu.cs_belong = $employee_filter";
        }
    }
    
    $sql = "SELECT 
                c.countryName, 
                COUNT(DISTINCT o.id) as order_count,
                SUM(o.total_amount) as total_amount,
                (SELECT SUM(oi_sub.quantity) 
                 FROM order_items oi_sub 
                 JOIN orders o_sub ON oi_sub.order_id = o_sub.id
                 JOIN customer cu_sub ON o_sub.customer_id = cu_sub.id
                 WHERE o_sub.order_date BETWEEN ? AND ?
                 AND cu_sub.cs_country = cu.cs_country
                 $employee_condition) as total_quantity
            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 ?
            $employee_condition
            GROUP BY cu.cs_country
            ORDER BY total_quantity DESC
            LIMIT 10";
    
    // 添加额外的参数用于子查询
    $params = array_merge([$start_date, $end_date], $params);
    $types = "ss" . $types;
    


    $stmt = $conn->prepare($sql);
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    return $stmt->get_result();
}

/**
 * 获取地区销售同比环比数据
 * 
 * @param mysqli $conn 数据库连接
 * @param string $current_start 当前周期开始日期
 * @param string $current_end 当前周期结束日期
 * @param array|int|null $employee_filter 业务员ID或ID数组，用于过滤数据
 * @return array 地区销售同比环比数据
 */
function getRegionSalesComparison($conn, $current_start, $current_end, $employee_filter = null) {
    // 计算上一个相同时长的周期
    $current_start_date = new DateTime($current_start);
    $current_end_date = new DateTime($current_end);
    $interval = $current_start_date->diff($current_end_date);
    
    $prev_end_date = clone $current_start_date;
    $prev_end_date->modify('-1 day');
    $prev_start_date = clone $prev_end_date;
    $prev_start_date->sub($interval);
    
    $prev_start = $prev_start_date->format('Y-m-d');
    $prev_end = $prev_end_date->format('Y-m-d') . ' 23:59:59';
    
    // 如果有业务员过滤条件
    $employee_condition = "";
    
    if ($employee_filter !== null) {
        if (is_array($employee_filter) && !empty($employee_filter)) {
            // 处理数组形式的业务员ID列表
            $emp_ids = [];
            foreach ($employee_filter as $emp_id) {
                if (is_numeric($emp_id)) {
                    $emp_ids[] = intval($emp_id);
                }
            }
            if (!empty($emp_ids)) {
                $emp_ids_str = implode(',', $emp_ids);
                $employee_condition = " AND cu.cs_belong IN ($emp_ids_str)";
            }
        } else if (is_numeric($employee_filter) && $employee_filter > 0) {
            // 处理单个业务员ID
            $employee_filter = intval($employee_filter);
            $employee_condition = " AND cu.cs_belong = $employee_filter";
        }
    }
    
    // 获取当前周期数据
    $sql = "SELECT 
                c.countryName, 
                COUNT(o.id) as order_count,
                SUM(o.total_amount) as total_amount
            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 ?
            $employee_condition
            GROUP BY cu.cs_country
            ORDER BY total_amount DESC
            LIMIT 5";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ss", $current_start, $current_end);
    $stmt->execute();
    $current_result = $stmt->get_result();
    
    $current_data = [];
    while ($row = $current_result->fetch_assoc()) {
        $current_data[$row['countryName']] = [
            'order_count' => $row['order_count'],
            'total_amount' => $row['total_amount']
        ];
    }
    
    // 获取上一个周期数据
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ss", $prev_start, $prev_end);
    $stmt->execute();
    $prev_result = $stmt->get_result();
    
    $prev_data = [];
    while ($row = $prev_result->fetch_assoc()) {
        $prev_data[$row['countryName']] = [
            'order_count' => $row['order_count'],
            'total_amount' => $row['total_amount']
        ];
    }
    
    // 计算同比变化
    $comparison_data = [];
    foreach ($current_data as $country => $current) {
        $prev = $prev_data[$country] ?? ['order_count' => 0, 'total_amount' => 0];
        
        $order_growth = $prev['order_count'] > 0 
            ? (($current['order_count'] - $prev['order_count']) / $prev['order_count']) * 100 
            : 100;
            
        $amount_growth = $prev['total_amount'] > 0 
            ? (($current['total_amount'] - $prev['total_amount']) / $prev['total_amount']) * 100 
            : 100;
            
        $comparison_data[] = [
            'countryName' => $country,
            'current_orders' => $current['order_count'],
            'prev_orders' => $prev['order_count'],
            'order_growth' => $order_growth,
            'current_amount' => $current['total_amount'],
            'prev_amount' => $prev['total_amount'],
            'amount_growth' => $amount_growth
        ];
    }
    
    return $comparison_data;
}

/**
 * 渲染客户国家分布图
 * 
 * @param array $country_labels 国家标签
 * @param array $country_data 国家数据
 * @return void
 */
function renderCustomerCountryDistributionChart($country_labels, $country_data) {
    ?>
    <div class="chart-container">
        <div class="chart-header">
            <h2 class="chart-title">客户国家分布</h2>
        </div>
        <canvas id="countryDistributionChart"></canvas>
    </div>
    
    <script>
        // 客户国家分布图
        var countryDistributionCtx = document.getElementById('countryDistributionChart').getContext('2d');
        var countryDistributionChart = new Chart(countryDistributionCtx, {
            type: 'pie',
            data: {
                labels: <?php echo json_encode($country_labels); ?>,
                datasets: [{
                    data: <?php echo json_encode($country_data); ?>,
                    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)',
                        'rgba(199, 199, 199, 0.7)',
                        'rgba(83, 102, 255, 0.7)',
                        'rgba(40, 159, 64, 0.7)',
                        'rgba(210, 199, 199, 0.7)'
                    ],
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                plugins: {
                    legend: {
                        position: 'right',
                    }
                }
            }
        });
    </script>
    <?php
}

/**
 * 渲染地区订单分析图
 * 
 * @param array $region_labels 地区标签
 * @param array $region_orders 地区订单数量
 * @param array $region_quantities 地区产品数量
 * @return void
 */
function renderRegionOrdersChart($region_labels, $region_orders, $region_quantities) {
    ?>
    <div class="chart-container">
        <div class="chart-header">
            <h2 class="chart-title">地区订单分析</h2>
        </div>
        <canvas id="regionOrdersChart"></canvas>
    </div>
    
    <script>
        // 地区订单分析图
        var regionOrdersCtx = document.getElementById('regionOrdersChart').getContext('2d');
        var regionOrdersChart = new Chart(regionOrdersCtx, {
            type: 'bar',
            data: {
                labels: <?php echo json_encode($region_labels); ?>,
                datasets: [
                    {
                        label: '订单数量',
                        data: <?php echo json_encode($region_orders); ?>,
                        backgroundColor: 'rgba(54, 162, 235, 0.6)',
                        borderColor: 'rgba(54, 162, 235, 1)',
                        borderWidth: 1,
                        yAxisID: 'y-orders'
                    },
                    {
                        label: '产品订购数量',
                        data: <?php echo json_encode($region_quantities); ?>,
                        backgroundColor: 'rgba(255, 99, 132, 0.6)',
                        borderColor: 'rgba(255, 99, 132, 1)',
                        borderWidth: 1,
                        yAxisID: 'y-quantity'
                    }
                ]
            },
            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
}

/**
 * 渲染地区销售同比环比表格
 * 
 * @param array $comparison_data 比较数据
 * @return void
 */
function renderRegionSalesComparisonTable($comparison_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>
                    <th>上期销售额</th>
                    <th>销售额增长率</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($comparison_data as $row): ?>
                <tr>
                    <td><?php echo htmlspecialchars($row['countryName']); ?></td>
                    <td><?php echo number_format($row['current_orders']); ?></td>
                    <td><?php echo number_format($row['prev_orders']); ?></td>
                    <td class="<?php echo $row['order_growth'] >= 0 ? 'positive' : 'negative'; ?>">
                        <?php echo number_format($row['order_growth'], 2); ?>%
                    </td>
                    <td>¥<?php echo number_format($row['current_amount'], 2); ?></td>
                    <td>¥<?php echo number_format($row['prev_amount'], 2); ?></td>
                    <td class="<?php echo $row['amount_growth'] >= 0 ? 'positive' : 'negative'; ?>">
                        <?php echo number_format($row['amount_growth'], 2); ?>%
                    </td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>
    <?php
}

/**
 * 获取地区总销售额及增长率
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @param array|int|null $employee_filter 业务员ID或ID数组，用于过滤数据
 * @return array 总销售额和增长率
 */
function getRegionTotalSales($conn, $start_date, $end_date, $employee_filter = null) {
    // 如果有业务员过滤条件
    $employee_condition = "";
    
    if ($employee_filter !== null) {
        if (is_array($employee_filter) && !empty($employee_filter)) {
            // 处理数组形式的业务员ID列表
            $emp_ids = [];
            foreach ($employee_filter as $emp_id) {
                if (is_numeric($emp_id)) {
                    $emp_ids[] = intval($emp_id);
                }
            }
            if (!empty($emp_ids)) {
                $emp_ids_str = implode(',', $emp_ids);
                $employee_condition = " AND cu.cs_belong IN ($emp_ids_str)";
            }
        } else if (is_numeric($employee_filter) && $employee_filter > 0) {
            // 处理单个业务员ID
            $employee_filter = intval($employee_filter);
            $employee_condition = " AND cu.cs_belong = $employee_filter";
        }
    }
    
    // 计算当前周期销售额
    $sql = "SELECT SUM(o.total_amount) as total_amount
            FROM orders o
            JOIN customer cu ON o.customer_id = cu.id
            WHERE o.order_date BETWEEN ? AND ?
            $employee_condition";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ss", $start_date, $end_date);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result->fetch_assoc();
    $current_amount = $row['total_amount'] ?? 0;
    
    // 计算上一个相同时长的周期
    $current_start_date = new DateTime($start_date);
    $current_end_date = new DateTime($end_date);
    $interval = $current_start_date->diff($current_end_date);
    
    $prev_end_date = clone $current_start_date;
    $prev_end_date->modify('-1 day');
    $prev_start_date = clone $prev_end_date;
    $prev_start_date->sub($interval);
    
    $prev_start = $prev_start_date->format('Y-m-d');
    $prev_end = $prev_end_date->format('Y-m-d') . ' 23:59:59';
    
    // 获取上一周期销售额
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ss", $prev_start, $prev_end);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result->fetch_assoc();
    $prev_amount = $row['total_amount'] ?? 0;
    
    // 计算增长率
    $growth = 0;
    if ($prev_amount > 0) {
        $growth = (($current_amount - $prev_amount) / $prev_amount) * 100;
    }
    
    return [
        'total_amount' => $current_amount,
        'growth' => $growth
    ];
}

/**
 * 获取活跃国家数
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @param array|int|null $employee_filter 业务员ID或ID数组，用于过滤数据
 * @return array 活跃国家信息
 */
function getActiveCountries($conn, $start_date, $end_date, $employee_filter = null) {
    // 如果有业务员过滤条件
    $employee_condition = "";
    
    if ($employee_filter !== null) {
        if (is_array($employee_filter) && !empty($employee_filter)) {
            // 处理数组形式的业务员ID列表
            $emp_ids = [];
            foreach ($employee_filter as $emp_id) {
                if (is_numeric($emp_id)) {
                    $emp_ids[] = intval($emp_id);
                }
            }
            if (!empty($emp_ids)) {
                $emp_ids_str = implode(',', $emp_ids);
                $employee_condition = " AND cu.cs_belong IN ($emp_ids_str)";
            }
        } else if (is_numeric($employee_filter) && $employee_filter > 0) {
            // 处理单个业务员ID
            $employee_filter = intval($employee_filter);
            $employee_condition = " AND cu.cs_belong = $employee_filter";
        }
    }
    
    $sql = "SELECT COUNT(DISTINCT cu.cs_country) as country_count
            FROM orders o
            JOIN customer cu ON o.customer_id = cu.id
            WHERE o.order_date BETWEEN ? AND ?
            $employee_condition";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ss", $start_date, $end_date);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result->fetch_assoc();
    
    return [
        'count' => $row['country_count'] ?? 0
    ];
}

/**
 * 获取各地区平均订单金额
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @param array|int|null $employee_filter 业务员ID或ID数组，用于过滤数据
 * @return array 各地区平均订单金额数据
 */
function getAverageOrderByRegion($conn, $start_date, $end_date, $employee_filter = null) {
    // 如果有业务员过滤条件
    $employee_condition = "";
    
    if ($employee_filter !== null) {
        if (is_array($employee_filter) && !empty($employee_filter)) {
            // 处理数组形式的业务员ID列表
            $emp_ids = [];
            foreach ($employee_filter as $emp_id) {
                if (is_numeric($emp_id)) {
                    $emp_ids[] = intval($emp_id);
                }
            }
            if (!empty($emp_ids)) {
                $emp_ids_str = implode(',', $emp_ids);
                $employee_condition = " AND cu.cs_belong IN ($emp_ids_str)";
            }
        } else if (is_numeric($employee_filter) && $employee_filter > 0) {
            // 处理单个业务员ID
            $employee_filter = intval($employee_filter);
            $employee_condition = " AND cu.cs_belong = $employee_filter";
        }
    }
    
    $sql = "SELECT 
                c.countryName, 
                AVG(o.total_amount) as avg_amount,
                COUNT(o.id) as order_count
            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 ?
            $employee_condition
            GROUP BY cu.cs_country
            HAVING order_count >= 5
            ORDER BY avg_amount DESC
            LIMIT 10";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ss", $start_date, $end_date);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $regions = [];
    $total_avg = 0;
    $total_orders = 0;
    
    while ($row = $result->fetch_assoc()) {
        $regions[] = [
            'countryName' => $row['countryName'],
            'avg_amount' => $row['avg_amount'],
            'order_count' => $row['order_count']
        ];
        
        $total_avg += $row['avg_amount'] * $row['order_count'];
        $total_orders += $row['order_count'];
    }
    
    // 计算全球平均订单金额
    $global_avg = $total_orders > 0 ? $total_avg / $total_orders : 0;
    
    return [
        'regions' => $regions,
        'global_avg' => $global_avg
    ];
}

/**
 * 获取各地区产品类别偏好
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @param array|int|null $employee_filter 业务员ID或ID数组，用于过滤数据
 * @return array 各地区产品类别偏好数据
 */
function getRegionCategoryPreferences($conn, $start_date, $end_date, $employee_filter = null) {
    // 如果有业务员过滤条件
    $employee_condition = "";
    
    if ($employee_filter !== null) {
        if (is_array($employee_filter) && !empty($employee_filter)) {
            // 处理数组形式的业务员ID列表
            $emp_ids = [];
            foreach ($employee_filter as $emp_id) {
                if (is_numeric($emp_id)) {
                    $emp_ids[] = intval($emp_id);
                }
            }
            if (!empty($emp_ids)) {
                $emp_ids_str = implode(',', $emp_ids);
                $employee_condition = " AND cu.cs_belong IN ($emp_ids_str)";
            }
        } else if (is_numeric($employee_filter) && $employee_filter > 0) {
            // 处理单个业务员ID
            $employee_filter = intval($employee_filter);
            $employee_condition = " AND cu.cs_belong = $employee_filter";
        }
    }
    
    $sql = "SELECT 
                c.countryName, 
                pc.name as category_name,
                SUM(oi.quantity) as total_quantity
            FROM orders o
            JOIN customer cu ON o.customer_id = cu.id
            JOIN country c ON cu.cs_country = c.id
            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 ?
            $employee_condition
            GROUP BY cu.cs_country, p.category_id
            ORDER BY c.countryName, total_quantity DESC";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ss", $start_date, $end_date);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $preferences = [];
    $current_country = '';
    $country_data = [];
    
    while ($row = $result->fetch_assoc()) {
        if ($current_country != $row['countryName']) {
            if (!empty($current_country)) {
                $preferences[$current_country] = $country_data;
            }
            $current_country = $row['countryName'];
            $country_data = [];
        }
        
        $country_data[] = [
            'category' => $row['category_name'],
            'quantity' => $row['total_quantity']
        ];
    }
    
    // 添加最后一个国家的数据
    if (!empty($current_country)) {
        $preferences[$current_country] = $country_data;
    }
    
    // 只保留前5个主要市场
    $top_markets = array_slice(array_keys($preferences), 0, 5);
    $filtered_preferences = [];
    
    foreach ($top_markets as $market) {
        $filtered_preferences[$market] = array_slice($preferences[$market], 0, 5);
    }
    
    return $filtered_preferences;
}

/**
 * 获取地区销售增长趋势
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @param string $period 时间粒度 (day/week/month)
 * @param array|int|null $employee_filter 业务员ID或ID数组，用于过滤数据
 * @return array 地区销售增长趋势数据
 */
function getRegionGrowthTrends($conn, $start_date, $end_date, $period = 'month', $employee_filter = null) {
    $period_format = getPeriodFormat($period);
    
    // 如果有业务员过滤条件
    $employee_condition = "";
    
    if ($employee_filter !== null) {
        if (is_array($employee_filter) && !empty($employee_filter)) {
            // 处理数组形式的业务员ID列表
            $emp_ids = [];
            foreach ($employee_filter as $emp_id) {
                if (is_numeric($emp_id)) {
                    $emp_ids[] = intval($emp_id);
                }
            }
            if (!empty($emp_ids)) {
                $emp_ids_str = implode(',', $emp_ids);
                $employee_condition = " AND cu.cs_belong IN ($emp_ids_str)";
            }
        } else if (is_numeric($employee_filter) && $employee_filter > 0) {
            // 处理单个业务员ID
            $employee_filter = intval($employee_filter);
            $employee_condition = " AND cu.cs_belong = $employee_filter";
        }
    }
    
    $sql = "SELECT 
                c.countryName,
                DATE_FORMAT(o.order_date, ?) as time_period,
                SUM(o.total_amount) as total_amount
            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 ?
            $employee_condition
            GROUP BY cu.cs_country, time_period
            ORDER BY c.countryName, time_period";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("sss", $period_format, $start_date, $end_date);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $trends = [];
    $time_periods = [];
    
    while ($row = $result->fetch_assoc()) {
        if (!in_array($row['time_period'], $time_periods)) {
            $time_periods[] = $row['time_period'];
        }
        
        if (!isset($trends[$row['countryName']])) {
            $trends[$row['countryName']] = [];
        }
        
        $trends[$row['countryName']][$row['time_period']] = $row['total_amount'];
    }
    
    // 只保留前5个主要市场
    $top_markets = array_slice(array_keys($trends), 0, 5);
    $filtered_trends = [];
    
    foreach ($top_markets as $market) {
        $filtered_trends[$market] = $trends[$market];
    }
    
    return [
        'time_periods' => $time_periods,
        'trends' => $filtered_trends
    ];
}

/**
 * 获取地区季节性销售分析
 * 
 * @param mysqli $conn 数据库连接
 * @param array|int|null $employee_filter 业务员ID或ID数组，用于过滤数据
 * @return array 地区季节性销售分析数据
 */
function getRegionSeasonalAnalysis($conn, $employee_filter = null) {
    // 如果有业务员过滤条件
    $employee_condition = "";
    
    if ($employee_filter !== null) {
        if (is_array($employee_filter) && !empty($employee_filter)) {
            // 处理数组形式的业务员ID列表
            $emp_ids = [];
            foreach ($employee_filter as $emp_id) {
                if (is_numeric($emp_id)) {
                    $emp_ids[] = intval($emp_id);
                }
            }
            if (!empty($emp_ids)) {
                $emp_ids_str = implode(',', $emp_ids);
                $employee_condition = " AND cu.cs_belong IN ($emp_ids_str)";
            }
        } else if (is_numeric($employee_filter) && $employee_filter > 0) {
            // 处理单个业务员ID
            $employee_filter = intval($employee_filter);
            $employee_condition = " AND cu.cs_belong = $employee_filter";
        }
    }
    
    $sql = "SELECT 
                c.countryName,
                MONTH(o.order_date) as month,
                SUM(o.total_amount) as total_amount
            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 >= DATE_SUB(CURDATE(), INTERVAL 2 YEAR)
            $employee_condition
            GROUP BY cu.cs_country, month
            ORDER BY c.countryName, month";
    
    $result = $conn->query($sql);
    
    $seasonal = [];
    $months = range(1, 12);
    
    while ($row = $result->fetch_assoc()) {
        if (!isset($seasonal[$row['countryName']])) {
            $seasonal[$row['countryName']] = array_fill(1, 12, 0);
        }
        
        $seasonal[$row['countryName']][$row['month']] += $row['total_amount'];
    }
    
    // 只保留前5个主要市场
    $top_markets = array_slice(array_keys($seasonal), 0, 5);
    $filtered_seasonal = [];
    
    foreach ($top_markets as $market) {
        $filtered_seasonal[$market] = array_values($seasonal[$market]);
    }
    
    return [
        'months' => ['一月', '二月', '三月', '四月', '五月', '六月', '七月', '八月', '九月', '十月', '十一月', '十二月'],
        'data' => $filtered_seasonal
    ];
}

/**
 * 获取地区销售预测数据
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @param array|int|null $employee_filter 业务员ID或ID数组，用于过滤数据
 * @return array 地区销售预测数据
 */
function getRegionSalesForecast($conn, $start_date, $end_date, $employee_filter = null) {
    // 如果有业务员过滤条件
    $employee_condition = "";
    
    if ($employee_filter !== null) {
        if (is_array($employee_filter) && !empty($employee_filter)) {
            // 处理数组形式的业务员ID列表
            $emp_ids = [];
            foreach ($employee_filter as $emp_id) {
                if (is_numeric($emp_id)) {
                    $emp_ids[] = intval($emp_id);
                }
            }
            if (!empty($emp_ids)) {
                $emp_ids_str = implode(',', $emp_ids);
                $employee_condition = " AND cu.cs_belong IN ($emp_ids_str)";
            }
        } else if (is_numeric($employee_filter) && $employee_filter > 0) {
            // 处理单个业务员ID
            $employee_filter = intval($employee_filter);
            $employee_condition = " AND cu.cs_belong = $employee_filter";
        }
    }
    
    // 获取过去12个月的销售数据作为基础
    $sql = "SELECT 
                c.countryName,
                MONTH(o.order_date) as month,
                YEAR(o.order_date) as year,
                SUM(o.total_amount) as total_amount
            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 >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
            $employee_condition
            GROUP BY cu.cs_country, year, month
            ORDER BY c.countryName, year, month";
    
    $result = $conn->query($sql);
    
    $historical = [];
    
    while ($row = $result->fetch_assoc()) {
        $period = $row['year'] . '-' . str_pad($row['month'], 2, '0', STR_PAD_LEFT);
        
        if (!isset($historical[$row['countryName']])) {
            $historical[$row['countryName']] = [];
        }
        
        $historical[$row['countryName']][$period] = $row['total_amount'];
    }
    
    // 生成未来6个月的预测
    $forecast = [];
    $periods = [];
    $current_month = (int)date('m');
    $current_year = (int)date('Y');
    
    // 收集所有历史数据点时间
    $all_periods = [];
    foreach ($historical as $country => $data) {
        foreach (array_keys($data) as $period) {
            $all_periods[$period] = true;
        }
    }
    $all_periods = array_keys($all_periods);
    sort($all_periods);
    
    // 生成未来6个月的预测点
    $future_periods = [];
    for ($i = 1; $i <= 6; $i++) {
        $forecast_month = ($current_month + $i) % 12;
        $forecast_month = $forecast_month == 0 ? 12 : $forecast_month;
        $forecast_year = $current_year + floor(($current_month + $i - 1) / 12);
        $period = $forecast_year . '-' . str_pad($forecast_month, 2, '0', STR_PAD_LEFT);
        $future_periods[] = $period;
    }
    
    // 合并历史和预测时间点
    $all_chart_periods = array_merge($all_periods, $future_periods);
    
    // 只选取主要的5个市场做预测
    $top_markets = array_slice(array_keys($historical), 0, 5);
    $forecast_data = [];
    
    foreach ($top_markets as $market) {
        $forecast_data[$market] = [];
        
        // 为每个市场生成简单的预测
        // 这里使用简单的线性增长预测
        // 实际应用中可以采用更复杂的时间序列预测算法
        
        // 计算过去几个月的平均增长率
        $growth_rate = 0.05; // 默认月度增长率为5%
        
        if (count($historical[$market]) >= 2) {
            $values = array_values($historical[$market]);
            $start_val = array_shift($values);
            $end_val = array_pop($values);
            
            if ($start_val > 0) {
                $periods_count = count($historical[$market]) - 1;
                $total_growth = ($end_val / $start_val) - 1;
                $growth_rate = pow(1 + $total_growth, 1 / $periods_count) - 1;
                
                // 限制增长率在合理范围内
                $growth_rate = max(-0.2, min(0.2, $growth_rate));
            }
        }
        
        // 对于历史数据，直接使用实际值
        foreach ($all_periods as $period) {
            $forecast_data[$market][$period] = [
                'value' => $historical[$market][$period] ?? null,
                'is_forecast' => false
            ];
        }
        
        // 对于预测数据，基于最后一个历史数据点和增长率计算
        $last_period = end($all_periods);
        $last_value = $historical[$market][$last_period] ?? array_values($historical[$market])[count($historical[$market])-1];
        
        foreach ($future_periods as $i => $period) {
            $forecast_value = $last_value * pow(1 + $growth_rate, $i + 1);
            $forecast_data[$market][$period] = [
                'value' => $forecast_value,
                'is_forecast' => true
            ];
        }
    }
    
    return [
        'periods' => $all_chart_periods,
        'forecast' => $forecast_data
    ];
}

/**
 * 渲染平均订单金额分析图表
 * 
 * @param array $region_data 地区平均订单金额数据
 * @return void
 */
function renderAverageOrderByRegionChart($region_data) {
    $region_labels = [];
    $avg_amounts = [];
    $order_counts = [];
    
    foreach ($region_data as $region) {
        $region_labels[] = $region['countryName'];
        $avg_amounts[] = $region['avg_amount'];
        $order_counts[] = $region['order_count'];
    }
    ?>
    <div class="chart-header">
        <h2 class="chart-title">地区平均订单金额分析</h2>
    </div>
    <canvas id="avgOrderChart"></canvas>
    
    <script>
        // 地区平均订单金额分析图
        var avgOrderCtx = document.getElementById('avgOrderChart').getContext('2d');
        var avgOrderChart = new Chart(avgOrderCtx, {
            type: 'bar',
            data: {
                labels: <?php echo json_encode($region_labels); ?>,
                datasets: [
                    {
                        label: '平均订单金额',
                        data: <?php echo json_encode($avg_amounts); ?>,
                        backgroundColor: 'rgba(75, 192, 192, 0.6)',
                        borderColor: 'rgba(75, 192, 192, 1)',
                        borderWidth: 1,
                        yAxisID: 'y-amount'
                    },
                    {
                        type: 'line',
                        label: '订单数量',
                        data: <?php echo json_encode($order_counts); ?>,
                        backgroundColor: 'rgba(255, 159, 64, 0.6)',
                        borderColor: 'rgba(255, 159, 64, 1)',
                        borderWidth: 2,
                        fill: false,
                        yAxisID: 'y-count'
                    }
                ]
            },
            options: {
                responsive: true,
                scales: {
                    x: {
                        title: {
                            display: true,
                            text: '地区'
                        }
                    },
                    'y-amount': {
                        type: 'linear',
                        position: 'left',
                        title: {
                            display: true,
                            text: '平均订单金额'
                        }
                    },
                    'y-count': {
                        type: 'linear',
                        position: 'right',
                        title: {
                            display: true,
                            text: '订单数量'
                        },
                        grid: {
                            drawOnChartArea: false
                        }
                    }
                }
            }
        });
    </script>
    <?php
}

/**
 * 渲染地区产品类别偏好图表
 * 
 * @param array $preferences 地区产品类别偏好数据
 * @return void
 */
function renderRegionCategoryPreferencesChart($preferences) {
    ?>
    <div class="chart-header">
        <h2 class="chart-title">各地区产品类别偏好</h2>
    </div>
    
    <div class="grid-row">
        <?php foreach ($preferences as $country => $categories): ?>
            <?php
            $category_labels = [];
            $category_data = [];
            
            foreach ($categories as $cat) {
                $category_labels[] = $cat['category'];
                $category_data[] = $cat['quantity'];
            }
            ?>
            <div class="grid-column">
                <div class="subchart-container">
                    <h3 class="subchart-title"><?php echo $country; ?></h3>
                    <canvas id="categoryChart<?php echo md5($country); ?>"></canvas>
                </div>
            </div>
            
            <script>
                // <?php echo $country; ?> 产品类别偏好
                var categoryCtx<?php echo md5($country); ?> = document.getElementById('categoryChart<?php echo md5($country); ?>').getContext('2d');
                var categoryChart<?php echo md5($country); ?> = new Chart(categoryCtx<?php echo md5($country); ?>, {
                    type: 'doughnut',
                    data: {
                        labels: <?php echo json_encode($category_labels); ?>,
                        datasets: [{
                            data: <?php echo json_encode($category_data); ?>,
                            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)'
                            ],
                            borderWidth: 1
                        }]
                    },
                    options: {
                        responsive: true,
                        plugins: {
                            legend: {
                                position: 'right',
                            }
                        }
                    }
                });
            </script>
        <?php endforeach; ?>
    </div>
    <?php
}

/**
 * 渲染地区销售增长趋势图表
 * 
 * @param array $growth_data 地区销售增长趋势数据
 * @return void
 */
function renderRegionGrowthTrendsChart($growth_data) {
    $time_periods = $growth_data['time_periods'];
    $trends = $growth_data['trends'];
    
    $datasets = [];
    $colors = [
        ['rgba(255, 99, 132, 0.6)', 'rgba(255, 99, 132, 1)'],
        ['rgba(54, 162, 235, 0.6)', 'rgba(54, 162, 235, 1)'],
        ['rgba(255, 206, 86, 0.6)', 'rgba(255, 206, 86, 1)'],
        ['rgba(75, 192, 192, 0.6)', 'rgba(75, 192, 192, 1)'],
        ['rgba(153, 102, 255, 0.6)', 'rgba(153, 102, 255, 1)']
    ];
    
    $i = 0;
    foreach ($trends as $country => $data) {
        $dataset = [
            'label' => $country,
            'data' => [],
            'backgroundColor' => $colors[$i % count($colors)][0],
            'borderColor' => $colors[$i % count($colors)][1],
            'borderWidth' => 2,
            'fill' => false,
            'tension' => 0.1
        ];
        
        foreach ($time_periods as $period) {
            $dataset['data'][] = $data[$period] ?? null;
        }
        
        $datasets[] = $dataset;
        $i++;
    }
    ?>
    <div class="chart-header">
        <h2 class="chart-title">地区销售增长趋势</h2>
    </div>
    <canvas id="growthTrendsChart"></canvas>
    
    <script>
        // 地区销售增长趋势图
        var growthTrendsCtx = document.getElementById('growthTrendsChart').getContext('2d');
        var growthTrendsChart = new Chart(growthTrendsCtx, {
            type: 'line',
            data: {
                labels: <?php echo json_encode($time_periods); ?>,
                datasets: <?php echo json_encode($datasets); ?>
            },
            options: {
                responsive: true,
                scales: {
                    x: {
                        title: {
                            display: true,
                            text: '时间段'
                        }
                    },
                    y: {
                        title: {
                            display: true,
                            text: '销售额'
                        }
                    }
                }
            }
        });
    </script>
    <?php
}

/**
 * 渲染地区季节性分析图表
 * 
 * @param array $seasonal_data 地区季节性分析数据
 * @return void
 */
function renderRegionSeasonalAnalysisChart($seasonal_data) {
    $months = $seasonal_data['months'];
    $data = $seasonal_data['data'];
    
    $datasets = [];
    $colors = [
        ['rgba(255, 99, 132, 0.6)', 'rgba(255, 99, 132, 1)'],
        ['rgba(54, 162, 235, 0.6)', 'rgba(54, 162, 235, 1)'],
        ['rgba(255, 206, 86, 0.6)', 'rgba(255, 206, 86, 1)'],
        ['rgba(75, 192, 192, 0.6)', 'rgba(75, 192, 192, 1)'],
        ['rgba(153, 102, 255, 0.6)', 'rgba(153, 102, 255, 1)']
    ];
    
    $i = 0;
    foreach ($data as $country => $values) {
        $datasets[] = [
            'label' => $country,
            'data' => $values,
            'backgroundColor' => $colors[$i % count($colors)][0],
            'borderColor' => $colors[$i % count($colors)][1],
            'borderWidth' => 2,
            'fill' => false,
            'tension' => 0.1
        ];
        $i++;
    }
    ?>
    <div class="chart-header">
        <h2 class="chart-title">地区季节性销售分析</h2>
    </div>
    <canvas id="seasonalAnalysisChart"></canvas>
    
    <script>
        // 地区季节性销售分析图
        var seasonalCtx = document.getElementById('seasonalAnalysisChart').getContext('2d');
        var seasonalChart = new Chart(seasonalCtx, {
            type: 'line',
            data: {
                labels: <?php echo json_encode($months); ?>,
                datasets: <?php echo json_encode($datasets); ?>
            },
            options: {
                responsive: true,
                scales: {
                    x: {
                        title: {
                            display: true,
                            text: '月份'
                        }
                    },
                    y: {
                        title: {
                            display: true,
                            text: '销售额'
                        }
                    }
                }
            }
        });
    </script>
    <?php
}

/**
 * 渲染地区销售预测图表
 * 
 * @param array $forecast_data 地区销售预测数据
 * @return void
 */
function renderRegionSalesForecastChart($forecast_data) {
    $periods = $forecast_data['periods'];
    $forecast = $forecast_data['forecast'];
    
    $datasets = [];
    $colors = [
        ['rgba(255, 99, 132, 0.6)', 'rgba(255, 99, 132, 1)'],
        ['rgba(54, 162, 235, 0.6)', 'rgba(54, 162, 235, 1)'],
        ['rgba(255, 206, 86, 0.6)', 'rgba(255, 206, 86, 1)'],
        ['rgba(75, 192, 192, 0.6)', 'rgba(75, 192, 192, 1)'],
        ['rgba(153, 102, 255, 0.6)', 'rgba(153, 102, 255, 1)']
    ];
    
    $i = 0;
    foreach ($forecast as $country => $data) {
        $historical_data = [];
        $forecast_data = [];
        
        foreach ($periods as $period) {
            if (isset($data[$period])) {
                if ($data[$period]['is_forecast']) {
                    $historical_data[] = null;
                    $forecast_data[] = $data[$period]['value'];
                } else {
                    $historical_data[] = $data[$period]['value'];
                    $forecast_data[] = null;
                }
            } else {
                $historical_data[] = null;
                $forecast_data[] = null;
            }
        }
        
        $datasets[] = [
            'label' => $country . ' (历史)',
            'data' => $historical_data,
            'backgroundColor' => $colors[$i % count($colors)][0],
            'borderColor' => $colors[$i % count($colors)][1],
            'borderWidth' => 2,
            'fill' => false
        ];
        
        $datasets[] = [
            'label' => $country . ' (预测)',
            'data' => $forecast_data,
            'backgroundColor' => $colors[$i % count($colors)][0],
            'borderColor' => $colors[$i % count($colors)][1],
            'borderWidth' => 2,
            'borderDash' => [5, 5],
            'fill' => false
        ];
        
        $i++;
    }
    ?>
    <div class="chart-header">
        <h2 class="chart-title">地区销售预测 (未来6个月)</h2>
    </div>
    <canvas id="forecastChart"></canvas>
    
    <script>
        // 地区销售预测图
        var forecastCtx = document.getElementById('forecastChart').getContext('2d');
        var forecastChart = new Chart(forecastCtx, {
            type: 'line',
            data: {
                labels: <?php echo json_encode($periods); ?>,
                datasets: <?php echo json_encode($datasets); ?>
            },
            options: {
                responsive: true,
                scales: {
                    x: {
                        title: {
                            display: true,
                            text: '时间段'
                        }
                    },
                    y: {
                        title: {
                            display: true,
                            text: '销售额'
                        }
                    }
                },
                plugins: {
                    tooltip: {
                        callbacks: {
                            title: function(tooltipItems) {
                                return tooltipItems[0].label;
                            }
                        }
                    }
                }
            }
        });
    </script>
    <?php
}

/**
 * 渲染热门地区表格
 * 
 * @param array $region_labels 地区标签
 * @param array $region_order_counts 地区订单数量
 * @param array $region_quantities 地区产品数量
 * @param array $region_amounts 地区销售金额
 * @return void
 */
function renderTopRegionsTable($region_labels, $region_order_counts, $region_quantities, $region_amounts) {
    ?>
    <table class="data-table">
        <thead>
            <tr>
                <th>排名</th>
                <th>国家/地区</th>
                <th>订单数</th>
                <th>产品数量</th>
                <th>销售金额</th>
                <th>平均订单金额</th>
            </tr>
        </thead>
        <tbody>
            <?php for ($i = 0; $i < count($region_labels); $i++): ?>
                <tr>
                    <td><?php echo $i + 1; ?></td>
                    <td><?php echo htmlspecialchars($region_labels[$i]); ?></td>
                    <td><?php echo number_format($region_order_counts[$i]); ?></td>
                    <td><?php echo number_format($region_quantities[$i]); ?></td>
                    <td>¥<?php echo number_format($region_amounts[$i], 2); ?></td>
                    <td>¥<?php echo number_format($region_order_counts[$i] > 0 ? $region_amounts[$i] / $region_order_counts[$i] : 0, 2); ?></td>
                </tr>
            <?php endfor; ?>
        </tbody>
    </table>
    <?php
}