mtb_crm_asp/statistics_customers.php

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

require_once 'statistics_utils.php';

/**
 * 获取客户类型分布
 * 
 * @param mysqli $conn 数据库连接
 * @return mysqli_result 客户类型分布数据结果集
 */
function getCustomerTypeDistribution($conn) {
    $sql = "SELECT 
                ct.businessType, 
                COUNT(c.id) as customer_count
            FROM customer c
            JOIN clienttype ct ON c.cs_type = ct.id
            GROUP BY c.cs_type";
    
    return $conn->query($sql);
}

/**
 * 获取成交阶段分布
 * 
 * @param mysqli $conn 数据库连接
 * @return mysqli_result 成交阶段分布数据结果集
 */
function getDealStageDistribution($conn) {
    $sql = "SELECT 
                cs_deal,
                CASE 
                    WHEN cs_deal = 1 THEN '背景调查'
                    WHEN cs_deal = 2 THEN '明确需求'
                    WHEN cs_deal = 3 THEN '已成交'
                    ELSE '其他'
                END as stage_name,
                COUNT(id) as customer_count
            FROM customer
            GROUP BY cs_deal";
    
    return $conn->query($sql);
}

/**
 * 获取客户增长趋势
 * 
 * @param mysqli $conn 数据库连接
 * @param int $months 获取多少个月的数据，默认12个月
 * @return mysqli_result 客户增长趋势数据结果集
 */
function getCustomerGrowthTrend($conn, $months = 12) {
    $sql = "SELECT 
                DATE_FORMAT(cs_addtime, '%Y-%m') as month,
                COUNT(id) as new_customers
            FROM customer
            WHERE cs_addtime >= DATE_SUB(CURDATE(), INTERVAL ? MONTH)
            GROUP BY DATE_FORMAT(cs_addtime, '%Y-%m')
            ORDER BY month";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("i", $months);
    $stmt->execute();
    return $stmt->get_result();
}

/**
 * 获取新老客户订单分析
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @return array 新老客户订单分析数据
 */
function getNewVsReturningCustomerOrders($conn, $start_date, $end_date) {
    // 获取选定日期范围内的订单
    $sql = "SELECT
                o.customer_id,
                COUNT(o.id) as order_count,
                SUM(o.total_amount) as total_amount,
                MIN(o.order_date) as first_order_date,
                MAX(c.cs_addtime) as customer_addtime
            FROM orders o
            JOIN customer c ON o.customer_id = c.id
            WHERE o.order_date BETWEEN ? AND ?
            GROUP BY o.customer_id";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ss", $start_date, $end_date);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $new_customers = 0;
    $returning_customers = 0;
    $new_customer_amount = 0;
    $returning_customer_amount = 0;
    
    while ($row = $result->fetch_assoc()) {
        // 查找之前是否有订单
        $prev_sql = "SELECT id FROM orders 
                     WHERE customer_id = ? 
                     AND order_date < ? 
                     LIMIT 1";
        
        $prev_stmt = $conn->prepare($prev_sql);
        $prev_stmt->bind_param("is", $row['customer_id'], $start_date);
        $prev_stmt->execute();
        $prev_result = $prev_stmt->get_result();
        
        if ($prev_result->num_rows > 0) {
            // 老客户
            $returning_customers++;
            $returning_customer_amount += $row['total_amount'];
        } else {
            // 新客户
            $new_customers++;
            $new_customer_amount += $row['total_amount'];
        }
    }
    
    return [
        'new_customers' => $new_customers,
        'returning_customers' => $returning_customers,
        'new_customer_amount' => $new_customer_amount,
        'returning_customer_amount' => $returning_customer_amount,
        'total_customers' => $new_customers + $returning_customers,
        'total_amount' => $new_customer_amount + $returning_customer_amount
    ];
}

/**
 * 渲染客户类型分布图
 * 
 * @param array $type_labels 类型标签
 * @param array $type_data 类型数据
 * @return void
 */
function renderCustomerTypeChart($type_labels, $type_data) {
    ?>
    <div class="chart-container">
        <div class="chart-header">
            <h2 class="chart-title">客户类型分布</h2>
        </div>
        <canvas id="customerTypeChart"></canvas>
    </div>
    
    <script>
        // 客户类型分布图
        var customerTypeCtx = document.getElementById('customerTypeChart').getContext('2d');
        var customerTypeChart = new Chart(customerTypeCtx, {
            type: 'doughnut',
            data: {
                labels: <?php echo json_encode($type_labels); ?>,
                datasets: [{
                    data: <?php echo json_encode($type_data); ?>,
                    backgroundColor: [
                        'rgba(54, 162, 235, 0.7)',
                        'rgba(255, 99, 132, 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
}

/**
 * 渲染成交阶段分布图
 * 
 * @param array $stage_labels 阶段标签
 * @param array $stage_data 阶段数据
 * @return void
 */
function renderDealStageChart($stage_labels, $stage_data) {
    ?>
    <div class="chart-container">
        <div class="chart-header">
            <h2 class="chart-title">成交阶段分布</h2>
        </div>
        <canvas id="dealStageChart"></canvas>
    </div>
    
    <script>
        // 成交阶段分布图
        var dealStageCtx = document.getElementById('dealStageChart').getContext('2d');
        var dealStageChart = new Chart(dealStageCtx, {
            type: 'bar',
            data: {
                labels: <?php echo json_encode($stage_labels); ?>,
                datasets: [{
                    label: '客户数量',
                    data: <?php echo json_encode($stage_data); ?>,
                    backgroundColor: [
                        'rgba(255, 206, 86, 0.7)',
                        'rgba(54, 162, 235, 0.7)',
                        'rgba(255, 99, 132, 0.7)'
                    ],
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                scales: {
                    y: {
                        beginAtZero: true,
                        title: {
                            display: true,
                            text: '客户数量'
                        }
                    }
                }
            }
        });
    </script>
    <?php
}

/**
 * 渲染客户增长趋势图
 * 
 * @param array $growth_labels 增长标签
 * @param array $growth_data 增长数据
 * @return void
 */
function renderCustomerGrowthChart($growth_labels, $growth_data) {
    ?>
    <div class="chart-container">
        <div class="chart-header">
            <h2 class="chart-title">客户增长趋势</h2>
        </div>
        <canvas id="customerGrowthChart"></canvas>
    </div>
    
    <script>
        // 客户增长趋势图
        var customerGrowthCtx = document.getElementById('customerGrowthChart').getContext('2d');
        var customerGrowthChart = new Chart(customerGrowthCtx, {
            type: 'line',
            data: {
                labels: <?php echo json_encode($growth_labels); ?>,
                datasets: [{
                    label: '新增客户',
                    data: <?php echo json_encode($growth_data); ?>,
                    backgroundColor: 'rgba(75, 192, 192, 0.2)',
                    borderColor: 'rgba(75, 192, 192, 1)',
                    borderWidth: 2,
                    tension: 0.1
                }]
            },
            options: {
                responsive: true,
                scales: {
                    y: {
                        beginAtZero: true,
                        title: {
                            display: true,
                            text: '客户数量'
                        }
                    }
                }
            }
        });
    </script>
    <?php
}

/**
 * 渲染新老客户分析图
 * 
 * @param array $new_vs_returning 新老客户数据
 * @return void
 */
function renderNewVsReturningCustomersChart($new_vs_returning) {
    ?>
    <div class="chart-container">
        <div class="chart-header">
            <h2 class="chart-title">新老客户分析</h2>
        </div>
        <div class="chart-row">
            <div class="chart-column">
                <canvas id="newVsReturningCustomersChart"></canvas>
            </div>
            <div class="chart-column">
                <canvas id="newVsReturningAmountChart"></canvas>
            </div>
        </div>
        <div class="customer-stats-summary">
            <div class="stats-row">
                <div class="stat-item">
                    <span class="stat-label">总客户数:</span>
                    <span class="stat-value"><?php echo number_format($new_vs_returning['total_customers']); ?></span>
                </div>
                <div class="stat-item">
                    <span class="stat-label">新客户:</span>
                    <span class="stat-value"><?php echo number_format($new_vs_returning['new_customers']); ?> 
                    (<?php echo number_format(($new_vs_returning['new_customers'] / $new_vs_returning['total_customers']) * 100, 1); ?>%)</span>
                </div>
                <div class="stat-item">
                    <span class="stat-label">老客户:</span>
                    <span class="stat-value"><?php echo number_format($new_vs_returning['returning_customers']); ?> 
                    (<?php echo number_format(($new_vs_returning['returning_customers'] / $new_vs_returning['total_customers']) * 100, 1); ?>%)</span>
                </div>
            </div>
            <div class="stats-row">
                <div class="stat-item">
                    <span class="stat-label">总销售额:</span>
                    <span class="stat-value">¥<?php echo number_format($new_vs_returning['total_amount'], 2); ?></span>
                </div>
                <div class="stat-item">
                    <span class="stat-label">新客户销售额:</span>
                    <span class="stat-value">¥<?php echo number_format($new_vs_returning['new_customer_amount'], 2); ?> 
                    (<?php echo number_format(($new_vs_returning['new_customer_amount'] / $new_vs_returning['total_amount']) * 100, 1); ?>%)</span>
                </div>
                <div class="stat-item">
                    <span class="stat-label">老客户销售额:</span>
                    <span class="stat-value">¥<?php echo number_format($new_vs_returning['returning_customer_amount'], 2); ?> 
                    (<?php echo number_format(($new_vs_returning['returning_customer_amount'] / $new_vs_returning['total_amount']) * 100, 1); ?>%)</span>
                </div>
            </div>
        </div>
    </div>
    
    <script>
        // 新老客户数量图
        var newVsReturningCtx = document.getElementById('newVsReturningCustomersChart').getContext('2d');
        var newVsReturningChart = new Chart(newVsReturningCtx, {
            type: 'pie',
            data: {
                labels: ['新客户', '老客户'],
                datasets: [{
                    data: [
                        <?php echo $new_vs_returning['new_customers']; ?>, 
                        <?php echo $new_vs_returning['returning_customers']; ?>
                    ],
                    backgroundColor: [
                        'rgba(54, 162, 235, 0.7)',
                        'rgba(255, 99, 132, 0.7)'
                    ],
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                plugins: {
                    legend: {
                        position: 'bottom',
                    },
                    title: {
                        display: true,
                        text: '客户数量分布'
                    }
                }
            }
        });
        
        // 新老客户销售额图
        var amountCtx = document.getElementById('newVsReturningAmountChart').getContext('2d');
        var amountChart = new Chart(amountCtx, {
            type: 'pie',
            data: {
                labels: ['新客户销售额', '老客户销售额'],
                datasets: [{
                    data: [
                        <?php echo $new_vs_returning['new_customer_amount']; ?>, 
                        <?php echo $new_vs_returning['returning_customer_amount']; ?>
                    ],
                    backgroundColor: [
                        'rgba(54, 162, 235, 0.7)',
                        'rgba(255, 99, 132, 0.7)'
                    ],
                    borderWidth: 1
                }]
            },
            options: {
                responsive: true,
                plugins: {
                    legend: {
                        position: 'bottom',
                    },
                    title: {
                        display: true,
                        text: '销售额分布'
                    }
                }
            }
        });
    </script>
    <style>
        .chart-row {
            display: flex;
            flex-wrap: wrap;
        }
        .chart-column {
            flex: 0 0 50%;
            max-width: 50%;
        }
        .customer-stats-summary {
            margin-top: 20px;
            padding: 15px;
            background-color: #f9f9f9;
            border-radius: 5px;
        }
        .stats-row {
            display: flex;
            margin-bottom: 15px;
        }
        .stat-item {
            flex: 1;
            padding: 0 10px;
        }
        .stat-label {
            display: block;
            font-weight: bold;
            margin-bottom: 5px;
            color: #555;
        }
        .stat-value {
            font-size: 16px;
            color: #333;
        }
    </style>
    <?php
}