mtb_crm_asp/customers_stats.php

<?php
/**
 * 客户统计分析展示页面
 */
require_once 'conn.php';
require_once 'statistics_utils.php';
require_once 'statistics_customers.php';
require_once 'statistics_sales.php';
// 检查登录状态
if (!isset($_SESSION['employee_id'])) {
    checkLogin();
}

function formatCurrency($value) {
    return '¥' . number_format($value ?? 0, 2);
}

// 获取日期范围参数
$date_params = getDateRangeParams();
$start_date = $date_params['start_date_sql'];
$end_date = $date_params['end_date_sql'];
$date_range = $date_params['date_range'];
$period = $date_params['period'];

// 页面头部
include('statistics_header.php');
?>

<div class="container">
    <div class="page-header">
        <h1 class="page-title">客户统计分析</h1>
    </div>

    
    <!-- 日期筛选 -->
    <div class="filter-form">
        <form method="get" class="filter-form-inline">
            <div class="form-group">
                <label for="date_range">选择日期范围</label>
                <select class="form-control" id="date_range" name="date_range" onchange="toggleCustomDates()">
                    <option value="current_month" <?php echo $date_range == 'current_month' ? 'selected' : ''; ?>>本月</option>
                    <option value="last_month" <?php echo $date_range == 'last_month' ? 'selected' : ''; ?>>上月</option>
                    <option value="current_year" <?php echo $date_range == 'current_year' ? 'selected' : ''; ?>>今年</option>
                    <option value="last_30_days" <?php echo $date_range == 'last_30_days' ? 'selected' : ''; ?>>最近30天</option>
                    <option value="last_90_days" <?php echo $date_range == 'last_90_days' ? 'selected' : ''; ?>>最近90天</option>
                    <option value="custom" <?php echo $date_range == 'custom' ? 'selected' : ''; ?>>自定义日期范围</option>
                </select>
            </div>
            <div class="form-group custom-date-inputs" id="custom_start_date" style="display: <?php echo $date_range == 'custom' ? 'inline-block' : 'none'; ?>">
                <label for="start_date">开始日期</label>
                <input type="date" class="form-control" id="start_date" name="start_date" value="<?php echo $date_params['custom_start']; ?>">
            </div>
            <div class="form-group custom-date-inputs" id="custom_end_date" style="display: <?php echo $date_range == 'custom' ? 'inline-block' : 'none'; ?>">
                <label for="end_date">结束日期</label>
                <input type="date" class="form-control" id="end_date" name="end_date" value="<?php echo $date_params['custom_end']; ?>">
            </div>
            <div class="form-group">
                <button type="submit" class="btn">应用筛选</button>
            </div>
        </form>
    </div>

    <!-- 关键指标 -->
    <div class="key-metrics-section" >
        <div class="section-title">
            <h2>关键指标</h2>
        </div>
        <?php
        // 获取关键指标数据
        $total_customers = getTotalCustomers($conn);
        $new_customers = getNewCustomers($conn, $start_date, $end_date);
        $avg_customer_value = getAverageCustomerValue($conn, $start_date, $end_date);
        $retention_data = getCustomerRetentionRate($conn, $start_date, $end_date);
        $conversion_data = getOrderConversionRate($conn, $start_date, $end_date);

        // 组合所有指标数据
        $kpi_data = [
            'total_customers' => $total_customers,
            'new_customers' => $new_customers,
            'avg_customer_value' => $avg_customer_value,
            'retention_rate' => $retention_data['retention_rate'],
            'retained_count' => $retention_data['retained_count'],
            'total_previous' => $retention_data['total_previous'],
            'conversion_rate' => $conversion_data['conversion_rate'],
            'customers_with_orders' => $conversion_data['customers_with_orders']
        ];

        // 渲染关键指标卡片
        renderKeyMetricsCard($kpi_data);
        ?>
    </div>
    
    <!-- 新增客户列表 -->
    <div class="customer-section">
        <div class="section-title">
            <h2>新增客户明细</h2>
        </div>
        <?php
        // 获取新增客户详细数据
        $new_customers_details = getNewCustomersDetails($conn, $start_date, $end_date);
        
        // 渲染新增客户图表
        renderNewCustomersChart($new_customers_details);
        ?>
    </div>
    
    <!-- 业务员新增客户统计 -->
    <div class="employee-section">
        <div class="section-title">
            <h2>业务员新增客户统计</h2>
        </div>
        <?php
        // 获取各业务员新增客户数据
        $employee_new_customers = getNewCustomersByEmployee($conn, $start_date, $end_date);
        
        // 渲染业务员新增客户图表
        renderNewCustomersByEmployeeChart($employee_new_customers);
        ?>
    </div>
</div>

<script>
function toggleCustomDates() {
    const dateRange = document.getElementById('date_range').value;
    const customDateInputs = document.querySelectorAll('.custom-date-inputs');
    
    if (dateRange === 'custom') {
        customDateInputs.forEach(el => el.style.display = 'inline-block');
    } else {
        customDateInputs.forEach(el => el.style.display = 'none');
    }
}
</script>

<?php
// 页面底部
include('statistics_footer.php');
?>

<?php
/**
 * 获取新增客户详细信息
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @return array 新增客户详细数据
 */
function getNewCustomersDetails($conn, $start_date, $end_date) {
    $sql = "SELECT 
                c.id,
                c.cs_company as company_name,
                c.cs_code as customer_code,
                co.countryName as country,
                ct.businessType as customer_type,
                e.em_user as employee_name,
                c.cs_addtime as add_date
            FROM customer c
            LEFT JOIN country co ON c.cs_country = co.id
            LEFT JOIN clienttype ct ON c.cs_type = ct.id
            LEFT JOIN employee e ON c.cs_belong = e.id
            WHERE c.cs_addtime BETWEEN ? AND ?
            ORDER BY c.cs_addtime DESC
            LIMIT 30";
            
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ss", $start_date, $end_date);
    $stmt->execute();
    
    $result = $stmt->get_result();
    $customers = [];
    
    while ($row = $result->fetch_assoc()) {
        $customers[] = $row;
    }
    
    return $customers;
}

/**
 * 获取各业务员新增客户统计
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @return array 业务员新增客户统计数据
 */
function getNewCustomersByEmployee($conn, $start_date, $end_date) {
    $sql = "SELECT 
                e.id as employee_id,
                e.em_user as employee_name,
                COUNT(c.id) as customer_count
            FROM employee e
            LEFT JOIN customer c ON e.id = c.cs_belong AND c.cs_addtime BETWEEN ? AND ?
            WHERE e.em_role IS NOT NULL
            GROUP BY e.id
            ORDER BY customer_count DESC";
            
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ss", $start_date, $end_date);
    $stmt->execute();
    
    $result = $stmt->get_result();
    $data = [];
    
    while ($row = $result->fetch_assoc()) {
        $data[] = $row;
    }
    
    return $data;
}

/**
 * 渲染新增客户图表
 * 
 * @param array $customers 新增客户数据
 * @return void
 */
function renderNewCustomersChart($customers) {
    if (empty($customers)) {
        echo '<div class="alert alert-info">该时间段内没有新增客户数据</div>';
        return;
    }
    ?>
    <div class="chart-container">
        <div class="table-responsive">
            <table class="data-table">
                <thead>
                    <tr>
                        <th>客户名称</th>
                        <th>客户编码</th>
                        <th>国家/地区</th>
                        <th>客户类型</th>
                        <th>负责业务员</th>
                        <th>添加日期</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($customers as $customer): ?>
                    <tr>
                        <td><?php echo htmlspecialchars($customer['company_name']); ?></td>
                        <td><?php echo htmlspecialchars($customer['customer_code']); ?></td>
                        <td><?php echo htmlspecialchars($customer['country']); ?></td>
                        <td><?php echo htmlspecialchars($customer['customer_type'] ?: '未分类'); ?></td>
                        <td><?php echo htmlspecialchars($customer['employee_name']); ?></td>
                        <td><?php echo date('Y-m-d', strtotime($customer['add_date'])); ?></td>
                    </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>
    
    <!-- 新增客户添加时间分布图 -->
    <div class="chart-container">
        <div>
            <canvas id="newCustomersChart"></canvas>
        </div>
    </div>
    
    <script>
    // 准备图表数据
    <?php
    // 按日期分组客户数量
    $dates = [];
    $counts = [];
    $dateGroups = [];
    
    foreach ($customers as $customer) {
        $date = date('Y-m-d', strtotime($customer['add_date']));
        if (!isset($dateGroups[$date])) {
            $dateGroups[$date] = 0;
        }
        $dateGroups[$date]++;
    }
    
    // 排序日期
    ksort($dateGroups);
    
    foreach ($dateGroups as $date => $count) {
        $dates[] = $date;
        $counts[] = $count;
    }
    ?>
    
    var newCustomersCtx = document.getElementById('newCustomersChart').getContext('2d');
    new Chart(newCustomersCtx, {
        type: 'bar',
        data: {
            labels: <?php echo json_encode($dates); ?>,
            datasets: [{
                label: '新增客户数量',
                data: <?php echo json_encode($counts); ?>,
                backgroundColor: 'rgba(54, 162, 235, 0.5)',
                borderColor: 'rgba(54, 162, 235, 1)',
                borderWidth: 1
            }]
        },
        options: {
            responsive: true,
            scales: {
                y: {
                    beginAtZero: true,
                    title: {
                        display: true,
                        text: '客户数量'
                    }
                },
                x: {
                    title: {
                        display: true,
                        text: '日期'
                    }
                }
            },
            plugins: {
                title: {
                    display: true,
                    text: '新增客户时间分布'
                }
            }
        }
    });
    </script>
    <?php
}

/**
 * 渲染业务员新增客户图表
 * 
 * @param array $employee_data 业务员新增客户数据
 * @return void
 */
function renderNewCustomersByEmployeeChart($employee_data) {
    if (empty($employee_data)) {
        echo '<div class="alert alert-info">该时间段内没有业务员新增客户数据</div>';
        return;
    }
    
    // 准备图表数据
    $employee_names = [];
    $customer_counts = [];
    
    foreach ($employee_data as $row) {
        $employee_names[] = $row['employee_name'];
        $customer_counts[] = $row['customer_count'];
    }
    
    // 生成图表背景色
    $colors = generateChartColors(count($employee_data));
    $backgroundColors = [];
    $borderColors = [];
    
    foreach ($colors as $color) {
        $backgroundColors[] = $color[0];
        $borderColors[] = $color[1];
    }
    ?>
    
    <div class="analysis-grid">
        <div>
            <canvas id="employeeNewCustomersChart"></canvas>
        </div>
        <div class="table-responsive">
            <table class="data-table">
                <thead>
                    <tr>
                        <th>业务员</th>
                        <th>新增客户数量</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($employee_data as $row): ?>
                    <tr>
                        <td><?php echo htmlspecialchars($row['employee_name']); ?></td>
                        <td><?php echo number_format($row['customer_count']); ?></td>
                    </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>
    
    <script>
    var employeeNewCustomersCtx = document.getElementById('employeeNewCustomersChart').getContext('2d');
    new Chart(employeeNewCustomersCtx, {
        type: 'bar',
        data: {
            labels: <?php echo json_encode($employee_names); ?>,
            datasets: [{
                label: '新增客户数量',
                data: <?php echo json_encode($customer_counts); ?>,
                backgroundColor: <?php echo json_encode($backgroundColors); ?>,
                borderColor: <?php echo json_encode($borderColors); ?>,
                borderWidth: 1
            }]
        },
        options: {
            responsive: true,
            scales: {
                y: {
                    beginAtZero: true,
                    title: {
                        display: true,
                        text: '客户数量'
                    }
                }
            },
            plugins: {
                title: {
                    display: true,
                    text: '业务员新增客户统计'
                }
            }
        }
    });
    </script>
    <?php
}