mtb_crm_asp/statistics_region.php

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

require_once 'statistics_utils.php';

/**
 * 获取客户国家分布
 * 
 * @param mysqli $conn 数据库连接
 * @return mysqli_result 客户国家分布数据结果集
 */
function getCustomerCountryDistribution($conn) {
    $sql = "SELECT 
                c.countryName, 
                COUNT(cu.id) as customer_count
            FROM customer cu
            JOIN country c ON cu.cs_country = c.id
            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 结束日期
 * @return mysqli_result 地区订单数据结果集
 */
function getOrdersByRegion($conn, $start_date, $end_date) {
    $sql = "SELECT 
                c.countryName, 
                COUNT(o.id) as order_count,
                SUM(o.total_amount) as total_amount,
                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
            LEFT JOIN order_items oi ON o.id = oi.order_id
            WHERE o.order_date BETWEEN ? AND ?
            GROUP BY cu.cs_country
            ORDER BY total_quantity DESC
            LIMIT 10";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ss", $start_date, $end_date);
    $stmt->execute();
    return $stmt->get_result();
}

/**
 * 获取地区销售同比环比数据
 * 
 * @param mysqli $conn 数据库连接
 * @param string $current_start 当前周期开始日期
 * @param string $current_end 当前周期结束日期
 * @return array 地区销售同比环比数据
 */
function getRegionSalesComparison($conn, $current_start, $current_end) {
    // 计算上一个相同时长的周期
    $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';
    
    // 获取当前周期数据
    $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 ?
            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
}