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>
        <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%;
            }
            .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>
        <div class="pie-charts-container">
            <div class="pie-chart-wrapper">
                <h3 style="text-align: center; margin-bottom: 15px;">客户数量分布</h3>
                <canvas id="newVsReturningCustomersChart"></canvas>
            </div>
            <div class="pie-chart-wrapper">
                <h3 style="text-align: center; margin-bottom: 15px;">销售额分布</h3>
                <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 ($new_vs_returning['total_customers'] > 0) ? number_format(($new_vs_returning['new_customers'] / $new_vs_returning['total_customers']) * 100, 1) : '0'; ?>%)</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 ($new_vs_returning['total_customers'] > 0) ? number_format(($new_vs_returning['returning_customers'] / $new_vs_returning['total_customers']) * 100, 1) : '0'; ?>%)</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 ($new_vs_returning['total_amount'] > 0) ? number_format(($new_vs_returning['new_customer_amount'] / $new_vs_returning['total_amount']) * 100, 1) : '0'; ?>%)</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 ($new_vs_returning['total_amount'] > 0) ? number_format(($new_vs_returning['returning_customer_amount'] / $new_vs_returning['total_amount']) * 100, 1) : '0'; ?>%)</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,
                maintainAspectRatio: true,
                plugins: {
                    legend: {
                        position: 'bottom',
                    }
                }
            }
        });
        
        // 新老客户销售额图
        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,
                maintainAspectRatio: true,
                plugins: {
                    legend: {
                        position: 'bottom',
                    }
                }
            }
        });
    </script>
    <?php
}

/**
 * 获取客户总数
 * 
 * @param mysqli $conn 数据库连接
 * @return int 客户总数
 */
function getTotalCustomers($conn) {
    $sql = "SELECT COUNT(id) as total FROM customer";
    $result = $conn->query($sql);
    $row = $result->fetch_assoc();
    return $row['total'];
}

/**
 * 获取指定时间段内新增客户数
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @return int 新增客户数
 */
function getNewCustomers($conn, $start_date, $end_date) {
    $sql = "SELECT COUNT(id) as new_count 
            FROM customer 
            WHERE cs_addtime BETWEEN ? AND ?";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ss", $start_date, $end_date);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result->fetch_assoc();
    return $row['new_count'];
}

/**
 * 计算平均客户价值（客户平均订单金额）
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @return float 平均客户价值
 */
function getAverageCustomerValue($conn, $start_date, $end_date) {
    $sql = "SELECT AVG(customer_value) as avg_value FROM (
                SELECT 
                    o.customer_id,
                    SUM(o.total_amount) as customer_value
                FROM orders o
                WHERE o.order_date BETWEEN ? AND ?
                GROUP BY o.customer_id
            ) as customer_values";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ss", $start_date, $end_date);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result->fetch_assoc();
    return $row['avg_value'] ? $row['avg_value'] : 0;
}

/**
 * 计算客户留存率
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @return array 客户留存率数据
 */
function getCustomerRetentionRate($conn, $start_date, $end_date) {
    // 获取之前时间段的客户
    $previous_start = date('Y-m-d', strtotime('-1 year', strtotime($start_date)));
    $previous_end = date('Y-m-d', strtotime('-1 day', strtotime($start_date)));
    
    // 之前时间段的客户ID
    $prev_sql = "SELECT DISTINCT customer_id 
                FROM orders 
                WHERE order_date BETWEEN ? AND ?";
    
    $prev_stmt = $conn->prepare($prev_sql);
    $prev_stmt->bind_param("ss", $previous_start, $previous_end);
    $prev_stmt->execute();
    $prev_result = $prev_stmt->get_result();
    
    $previous_customers = [];
    while ($row = $prev_result->fetch_assoc()) {
        $previous_customers[] = $row['customer_id'];
    }
    
    $previous_count = count($previous_customers);
    
    // 如果没有之前的客户，返回0
    if ($previous_count == 0) {
        return [
            'retained_count' => 0,
            'total_previous' => 0,
            'retention_rate' => 0
        ];
    }
    
    // 查询当前时间段内，之前客户中再次购买的客户数
    $current_sql = "SELECT COUNT(DISTINCT customer_id) as retained_count
                   FROM orders
                   WHERE order_date BETWEEN ? AND ?
                   AND customer_id IN (" . implode(',', $previous_customers) . ")";
    
    $current_stmt = $conn->prepare($current_sql);
    $current_stmt->bind_param("ss", $start_date, $end_date);
    $current_stmt->execute();
    $current_result = $current_stmt->get_result();
    $row = $current_result->fetch_assoc();
    
    $retained_count = $row['retained_count'];
    $retention_rate = ($retained_count / $previous_count) * 100;
    
    return [
        'retained_count' => $retained_count,
        'total_previous' => $previous_count,
        'retention_rate' => $retention_rate
    ];
}

/**
 * 计算下单转换率
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @return array 下单转换率数据
 */
function getOrderConversionRate($conn, $start_date, $end_date) {
    // 获取指定时间段内总客户数
    $total_sql = "SELECT COUNT(DISTINCT id) as total_count FROM customer WHERE cs_addtime <= ?";
    $total_stmt = $conn->prepare($total_sql);
    $total_stmt->bind_param("s", $end_date);
    $total_stmt->execute();
    $total_result = $total_stmt->get_result();
    $total_row = $total_result->fetch_assoc();
    $total_customers = $total_row['total_count'];
    
    // 获取有订单的客户数
    $order_sql = "SELECT COUNT(DISTINCT customer_id) as order_count 
                 FROM orders 
                 WHERE order_date BETWEEN ? AND ?";
    $order_stmt = $conn->prepare($order_sql);
    $order_stmt->bind_param("ss", $start_date, $end_date);
    $order_stmt->execute();
    $order_result = $order_stmt->get_result();
    $order_row = $order_result->fetch_assoc();
    $customers_with_orders = $order_row['order_count'];
    
    // 计算转换率
    $conversion_rate = ($total_customers > 0) ? ($customers_with_orders / $total_customers) * 100 : 0;
    
    return [
        'total_customers' => $total_customers,
        'customers_with_orders' => $customers_with_orders,
        'conversion_rate' => $conversion_rate
    ];
}

/**
 * 渲染关键指标仪表板
 * 
 * @param array $kpi_data 关键指标数据
 * @return void
 */
function renderKeyMetricsCard($kpi_data) {
    ?>
    <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($kpi_data['total_customers']); ?></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($kpi_data['new_customers']); ?></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($kpi_data['avg_customer_value'], 2); ?></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($kpi_data['retention_rate'], 1); ?>%</div>
                <div class="stats-card-subtitle"><?php echo number_format($kpi_data['retained_count']); ?> / <?php echo number_format($kpi_data['total_previous']); ?></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($kpi_data['conversion_rate'], 1); ?>%</div>
                <div class="stats-card-subtitle"><?php echo number_format($kpi_data['customers_with_orders']); ?> / <?php echo number_format($kpi_data['total_customers']); ?></div>
            </div>
        </div>
    </div>
    <?php
}