<?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 数据库连接
* @param array|int $employee_filter 业务员ID或ID数组，用于过滤数据 (可选)
* @return int 客户总数
*/
function getTotalCustomers($conn, $employee_filter = null) {
$sql = "SELECT COUNT(id) as total FROM customer";
// 如果有业务员过滤
if ($employee_filter !== null) {
if (is_array($employee_filter) && !empty($employee_filter)) {
// 处理数组形式的业务员ID列表
$emp_ids = array();
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);
$sql .= " WHERE cs_belong IN ($emp_ids_str)";
}
} else if (is_numeric($employee_filter) && $employee_filter > 0) {
// 处理单个业务员ID
$employee_filter = intval($employee_filter);
$sql .= " WHERE cs_belong = $employee_filter";
}
}
$result = $conn->query($sql);
$row = $result->fetch_assoc();
return $row['total'];
}
/**
* 获取指定时间段内新增客户数
*
* @param mysqli $conn 数据库连接
* @param string $start_date 开始日期
* @param string $end_date 结束日期
* @param array|int $employee_filter 业务员ID或ID数组，用于过滤数据 (可选)
* @return int 新增客户数
*/
function getNewCustomers($conn, $start_date, $end_date, $employee_filter = null) {
// 使用 mysqli_real_escape_string 防止 SQL 注入
$start_date = $conn->real_escape_string($start_date);
$end_date = $conn->real_escape_string($end_date);
$sql = "SELECT COUNT(id) as new_count
FROM customer
WHERE cs_addtime BETWEEN '$start_date' AND '$end_date'";
// 如果有业务员过滤
if ($employee_filter !== null) {
if (is_array($employee_filter) && !empty($employee_filter)) {
// 处理数组形式的业务员ID列表
$emp_ids = array();
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);
$sql .= " AND cs_belong IN ($emp_ids_str)";
}
} else if (is_numeric($employee_filter) && $employee_filter > 0) {
// 处理单个业务员ID
$employee_filter = intval($employee_filter);
$sql .= " AND cs_belong = $employee_filter";
}
}
$result = $conn->query($sql);
$row = $result->fetch_assoc();
return $row['new_count'];
}
/**
* 计算平均客户价值（客户平均订单金额）
*
* @param mysqli $conn 数据库连接
* @param string $start_date 开始日期
* @param string $end_date 结束日期
* @param array|int $employee_filter 业务员ID或ID数组，用于过滤数据 (可选)
* @return float 平均客户价值
*/
function getAverageCustomerValue($conn, $start_date, $end_date, $employee_filter = null) {
// 使用 mysqli_real_escape_string 防止 SQL 注入
$start_date = $conn->real_escape_string($start_date);
$end_date = $conn->real_escape_string($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
JOIN customer c ON o.customer_id = c.id
WHERE o.order_date BETWEEN '$start_date' AND '$end_date'";
// 如果有业务员过滤
if ($employee_filter !== null) {
if (is_array($employee_filter) && !empty($employee_filter)) {
// 处理数组形式的业务员ID列表
$emp_ids = array();
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);
$sql .= " AND c.cs_belong IN ($emp_ids_str)";
}
} else if (is_numeric($employee_filter) && $employee_filter > 0) {
// 处理单个业务员ID
$employee_filter = intval($employee_filter);
$sql .= " AND c.cs_belong = $employee_filter";
}
}
$sql .= " GROUP BY o.customer_id) as customer_values";
$result = $conn->query($sql);
$row = $result->fetch_assoc();
return $row['avg_value'] ? $row['avg_value'] : 0;
}
/**
* 计算客户留存率
*
* @param mysqli $conn 数据库连接
* @param string $start_date 开始日期
* @param string $end_date 结束日期
* @param array|int $employee_filter 业务员ID或ID数组，用于过滤数据 (可选)
* @return array 客户留存率数据
*/
function getCustomerRetentionRate($conn, $start_date, $end_date, $employee_filter = null) {
// 使用 mysqli_real_escape_string 防止 SQL 注入
$start_date = $conn->real_escape_string($start_date);
$end_date = $conn->real_escape_string($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 o.customer_id
FROM orders o
JOIN customer c ON o.customer_id = c.id
WHERE o.order_date BETWEEN '$previous_start' AND '$previous_end'";
// 如果有业务员过滤
if ($employee_filter !== null) {
if (is_array($employee_filter) && !empty($employee_filter)) {
// 处理数组形式的业务员ID列表
$emp_ids = array();
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);
$prev_sql .= " AND c.cs_belong IN ($emp_ids_str)";
}
} else if (is_numeric($employee_filter) && $employee_filter > 0) {
// 处理单个业务员ID
$employee_filter = intval($employee_filter);
$prev_sql .= " AND c.cs_belong = $employee_filter";
}
}
$prev_result = $conn->query($prev_sql);
$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 o.customer_id) as retained_count
FROM orders o
JOIN customer c ON o.customer_id = c.id
WHERE o.order_date BETWEEN '$start_date' AND '$end_date'
AND o.customer_id IN (" . implode(',', $previous_customers) . ")";
// 如果有业务员过滤
if ($employee_filter !== null) {
if (is_array($employee_filter) && !empty($employee_filter)) {
// 处理数组形式的业务员ID列表
$emp_ids = array();
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);
$current_sql .= " AND c.cs_belong IN ($emp_ids_str)";
}
} else if (is_numeric($employee_filter) && $employee_filter > 0) {
// 处理单个业务员ID
$employee_filter = intval($employee_filter);
$current_sql .= " AND c.cs_belong = $employee_filter";
}
}
$current_result = $conn->query($current_sql);
$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 结束日期
* @param array|int $employee_filter 业务员ID或ID数组，用于过滤数据 (可选)
* @return array 下单转换率数据
*/
function getOrderConversionRate($conn, $start_date, $end_date, $employee_filter = null) {
// 使用 mysqli_real_escape_string 防止 SQL 注入
$start_date = $conn->real_escape_string($start_date);
$end_date = $conn->real_escape_string($end_date);
// 获取指定时间段内总客户数
$total_sql = "SELECT COUNT(DISTINCT id) as total_count FROM customer WHERE cs_addtime <= '$end_date'";
// 如果有业务员过滤
if ($employee_filter !== null) {
if (is_array($employee_filter) && !empty($employee_filter)) {
// 处理数组形式的业务员ID列表
$emp_ids = array();
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);
$total_sql .= " AND cs_belong IN ($emp_ids_str)";
}
} else if (is_numeric($employee_filter) && $employee_filter > 0) {
// 处理单个业务员ID
$employee_filter = intval($employee_filter);
$total_sql .= " AND cs_belong = $employee_filter";
}
}
$total_result = $conn->query($total_sql);
$total_row = $total_result->fetch_assoc();
$total_customers = $total_row['total_count'];
// 获取有订单的客户数
$order_sql = "SELECT COUNT(DISTINCT o.customer_id) as order_count
FROM orders o
JOIN customer c ON o.customer_id = c.id
WHERE o.order_date BETWEEN '$start_date' AND '$end_date'";
// 如果有业务员过滤
if ($employee_filter !== null) {
if (is_array($employee_filter) && !empty($employee_filter)) {
// 处理数组形式的业务员ID列表
$emp_ids = array();
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);
$order_sql .= " AND c.cs_belong IN ($emp_ids_str)";
}
} else if (is_numeric($employee_filter) && $employee_filter > 0) {
// 处理单个业务员ID
$employee_filter = intval($employee_filter);
$order_sql .= " AND c.cs_belong = $employee_filter";
}
}
$order_result = $conn->query($order_sql);
$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
}
/**
* 获取客户价值分布数据
*
* @param mysqli $conn 数据库连接
* @param string $start_date 开始日期
* @param string $end_date 结束日期
* @return array 客户价值分布数据
*/
function getCustomerValueDistribution($conn, $start_date, $end_date) {
$sql = "SELECT
value_segment,
COUNT(customer_id) as customer_count,
SUM(total_amount) as total_amount
FROM (
SELECT
o.customer_id,
SUM(o.total_amount) as total_amount,
CASE
WHEN SUM(o.total_amount) > 100000 THEN '高价值客户(>10万)'
WHEN SUM(o.total_amount) > 50000 THEN '中高价值客户(5-10万)'
WHEN SUM(o.total_amount) > 10000 THEN '中价值客户(1-5万)'
WHEN SUM(o.total_amount) > 5000 THEN '低价值客户(5千-1万)'
ELSE '微价值客户(<5千)'
END as value_segment
FROM orders o
WHERE o.order_date BETWEEN ? AND ?
GROUP BY o.customer_id
) as customer_value
GROUP BY value_segment
ORDER BY
CASE value_segment
WHEN '高价值客户(>10万)' THEN 1
WHEN '中高价值客户(5-10万)' THEN 2
WHEN '中价值客户(1-5万)' THEN 3
WHEN '低价值客户(5千-1万)' THEN 4
ELSE 5
END";
$stmt = $conn->prepare($sql);
$stmt->bind_param("ss", $start_date, $end_date);
$stmt->execute();
$result = $stmt->get_result();
$value_segments = [];
$customer_counts = [];
$total_amounts = [];
$total_customers = 0;
while ($row = $result->fetch_assoc()) {
$value_segments[] = $row['value_segment'];
$customer_counts[] = $row['customer_count'];
$total_amounts[] = $row['total_amount'];
$total_customers += $row['customer_count'];
}
return [
'segments' => $value_segments,
'counts' => $customer_counts,
'amounts' => $total_amounts,
'total_customers' => $total_customers
];
}
/**
* 获取客户活跃度分析数据
*
* @param mysqli $conn 数据库连接
* @param string $end_date 截止日期
* @return array 客户活跃度分析数据
*/
function getCustomerActivityAnalysis($conn, $end_date) {
$sql = "SELECT
activity_level,
COUNT(*) as customer_count
FROM (
SELECT
o.customer_id,
CASE
WHEN DATEDIFF(?, MAX(o.order_date)) <= 30 THEN '活跃客户(30天内)'
WHEN DATEDIFF(?, MAX(o.order_date)) <= 90 THEN '一般活跃(90天内)'
WHEN DATEDIFF(?, MAX(o.order_date)) <= 180 THEN '低活跃(180天内)'
WHEN DATEDIFF(?, MAX(o.order_date)) <= 365 THEN '沉睡客户(1年内)'
ELSE '流失客户(超过1年)'
END as activity_level
FROM orders o
GROUP BY o.customer_id
) as customer_activity
GROUP BY activity_level
ORDER BY
CASE activity_level
WHEN '活跃客户(30天内)' THEN 1
WHEN '一般活跃(90天内)' THEN 2
WHEN '低活跃(180天内)' THEN 3
WHEN '沉睡客户(1年内)' THEN 4
ELSE 5
END";
$stmt = $conn->prepare($sql);
$end_date_formatted = date('Y-m-d', strtotime($end_date));
$stmt->bind_param("ssss", $end_date_formatted, $end_date_formatted, $end_date_formatted, $end_date_formatted);
$stmt->execute();
$result = $stmt->get_result();
$activity_levels = [];
$customer_counts = [];
while ($row = $result->fetch_assoc()) {
$activity_levels[] = $row['activity_level'];
$customer_counts[] = $row['customer_count'];
}
return [
'levels' => $activity_levels,
'counts' => $customer_counts
];
}
/**
* 获取客户流失风险分析数据
*
* @param mysqli $conn 数据库连接
* @param string $end_date 截止日期
* @return array 客户流失风险分析数据
*/
function getCustomerChurnRiskAnalysis($conn, $end_date) {
$sql = "SELECT
risk_level,
COUNT(*) as customer_count
FROM (
SELECT
c.id,
CASE
WHEN last_order_date IS NULL THEN '从未购买'
WHEN DATEDIFF(?, last_order_date) <= 90 THEN '低风险(90天内)'
WHEN DATEDIFF(?, last_order_date) <= 180 THEN '中风险(90-180天)'
WHEN DATEDIFF(?, last_order_date) <= 365 THEN '高风险(180-365天)'
ELSE '极高风险(超过1年)'
END as risk_level
FROM customer c
LEFT JOIN (
SELECT customer_id, MAX(order_date) as last_order_date
FROM orders
GROUP BY customer_id
) o ON c.id = o.customer_id
) as customer_risk
GROUP BY risk_level
ORDER BY
CASE risk_level
WHEN '低风险(90天内)' THEN 1
WHEN '中风险(90-180天)' THEN 2
WHEN '高风险(180-365天)' THEN 3
WHEN '极高风险(超过1年)' THEN 4
WHEN '从未购买' THEN 5
END";
$stmt = $conn->prepare($sql);
$end_date_formatted = date('Y-m-d', strtotime($end_date));
$stmt->bind_param("sss", $end_date_formatted, $end_date_formatted, $end_date_formatted);
$stmt->execute();
$result = $stmt->get_result();
$risk_levels = [];
$customer_counts = [];
while ($row = $result->fetch_assoc()) {
$risk_levels[] = $row['risk_level'];
$customer_counts[] = $row['customer_count'];
}
return [
'levels' => $risk_levels,
'counts' => $customer_counts
];
}
/**
* 获取客户来源分析数据
*
* @param mysqli $conn 数据库连接
* @return array 客户来源分析数据
*/
function getCustomerSourceAnalysis($conn) {
// 假设cs_from字段代表客户来源，需要根据实际情况调整SQL
$sql = "SELECT
source,
COUNT(*) as customer_count
FROM (
SELECT
id,
CASE
WHEN cs_from = 1 THEN '网站注册'
WHEN cs_from = 2 THEN '销售开发'
WHEN cs_from = 3 THEN '广告引流'
WHEN cs_from = 4 THEN '展会获取'
WHEN cs_from = 5 THEN '客户推荐'
ELSE '其他来源'
END as source
FROM customer
) as customer_source
GROUP BY source
ORDER BY customer_count DESC";
$result = $conn->query($sql);
$sources = [];
$counts = [];
while ($row = $result->fetch_assoc()) {
$sources[] = $row['source'];
$counts[] = $row['customer_count'];
}
return [
'sources' => $sources,
'counts' => $counts
];
}
/**
* 渲染客户价值分布图表
*
* @param array $value_data 客户价值分布数据
* @return void
*/
function renderCustomerValueCharts($value_data) {
?>
<div class="chart-container">
<div class="chart-header">
<h2 class="chart-title">客户价值分布</h2>
</div>
<div class="chart-row" style="display: flex; width: 100%; margin: 0 -10px;">
<div class="chart-column" style="flex: 0 0 50%; max-width: 50%; padding: 0 10px; box-sizing: border-box;">
<h3 style="text-align: center; margin-bottom: 15px;">客户价值分布(柱状图)</h3>
<canvas id="customerValueBarChart"></canvas>
</div>
<div class="chart-column" style="flex: 0 0 50%; max-width: 50%; padding: 0 10px; box-sizing: border-box;">
<h3 style="text-align: center; margin-bottom: 15px;">客户价值分布(饼图)</h3>
<canvas id="customerValuePieChart"></canvas>
</div>
</div>
<div class="customer-stats-summary">
<div class="stats-row">
<?php foreach ($value_data['segments'] as $index => $segment): ?>
<div class="stat-item">
<span class="stat-label"><?php echo $segment; ?>:</span>
<span class="stat-value"><?php echo number_format($value_data['counts'][$index]); ?>
(<?php echo ($value_data['total_customers'] > 0) ?
number_format(($value_data['counts'][$index] / $value_data['total_customers']) * 100, 1) : '0'; ?>%)</span>
<span class="stat-sub-value">¥<?php echo number_format($value_data['amounts'][$index], 2); ?></span>
</div>
<?php endforeach; ?>
</div>
</div>
</div>
<script>
// 客户价值分布柱状图
var valueBarCtx = document.getElementById('customerValueBarChart').getContext('2d');
var valueBarChart = new Chart(valueBarCtx, {
type: 'bar',
data: {
labels: <?php echo json_encode($value_data['segments']); ?>,
datasets: [{
label: '客户数量',
data: <?php echo json_encode($value_data['counts']); ?>,
backgroundColor: [
'rgba(54, 162, 235, 0.7)',
'rgba(75, 192, 192, 0.7)',
'rgba(255, 206, 86, 0.7)',
'rgba(255, 99, 132, 0.7)',
'rgba(153, 102, 255, 0.7)'
],
borderWidth: 1
}]
},
options: {
responsive: true,
scales: {
y: {
beginAtZero: true,
title: {
display: true,
text: '客户数量'
}
}
}
}
});
// 客户价值分布饼图
var valuePieCtx = document.getElementById('customerValuePieChart').getContext('2d');
var valuePieChart = new Chart(valuePieCtx, {
type: 'pie',
data: {
labels: <?php echo json_encode($value_data['segments']); ?>,
datasets: [{
data: <?php echo json_encode($value_data['counts']); ?>,
backgroundColor: [
'rgba(54, 162, 235, 0.7)',
'rgba(75, 192, 192, 0.7)',
'rgba(255, 206, 86, 0.7)',
'rgba(255, 99, 132, 0.7)',
'rgba(153, 102, 255, 0.7)'
],
borderWidth: 1
}]
},
options: {
responsive: true
}
});
</script>
<?php
}
/**
* 渲染客户活跃度分析图表
*
* @param array $activity_data 客户活跃度数据
* @return void
*/
function renderCustomerActivityChart($activity_data) {
?>
<div class="chart-container">
<div class="chart-header">
<h2 class="chart-title">客户活跃度分析</h2>
</div>
<canvas id="customerActivityChart"></canvas>
<div class="customer-stats-summary">
<div class="stats-row">
<?php
$total_customers = array_sum($activity_data['counts']);
foreach ($activity_data['levels'] as $index => $level):
?>
<div class="stat-item">
<span class="stat-label"><?php echo $level; ?>:</span>
<span class="stat-value"><?php echo number_format($activity_data['counts'][$index]); ?>
(<?php echo ($total_customers > 0) ?
number_format(($activity_data['counts'][$index] / $total_customers) * 100, 1) : '0'; ?>%)</span>
</div>
<?php endforeach; ?>
</div>
</div>
</div>
<script>
// 客户活跃度分析图
var activityCtx = document.getElementById('customerActivityChart').getContext('2d');
var activityChart = new Chart(activityCtx, {
type: 'bar',
data: {
labels: <?php echo json_encode($activity_data['levels']); ?>,
datasets: [{
label: '客户数量',
data: <?php echo json_encode($activity_data['counts']); ?>,
backgroundColor: [
'rgba(54, 162, 235, 0.7)',
'rgba(75, 192, 192, 0.7)',
'rgba(255, 206, 86, 0.7)',
'rgba(255, 99, 132, 0.7)',
'rgba(153, 102, 255, 0.7)'
],
borderWidth: 1
}]
},
options: {
responsive: true,
scales: {
y: {
beginAtZero: true,
title: {
display: true,
text: '客户数量'
}
}
}
}
});
</script>
<?php
}
/**
* 渲染客户流失风险分析图表
*
* @param array $risk_data 客户流失风险数据
* @return void
*/
function renderCustomerChurnRiskChart($risk_data) {
?>
<div class="chart-container">
<div class="chart-header">
<h2 class="chart-title">客户流失风险分析</h2>
</div>
<canvas id="customerRiskChart"></canvas>
<div class="customer-stats-summary">
<div class="stats-row">
<?php
$total_customers = array_sum($risk_data['counts']);
foreach ($risk_data['levels'] as $index => $level):
?>
<div class="stat-item">
<span class="stat-label"><?php echo $level; ?>:</span>
<span class="stat-value"><?php echo number_format($risk_data['counts'][$index]); ?>
(<?php echo ($total_customers > 0) ?
number_format(($risk_data['counts'][$index] / $total_customers) * 100, 1) : '0'; ?>%)</span>
</div>
<?php endforeach; ?>
</div>
</div>
</div>
<script>
// 客户流失风险分析图
var riskCtx = document.getElementById('customerRiskChart').getContext('2d');
var riskChart = new Chart(riskCtx, {
type: 'doughnut',
data: {
labels: <?php echo json_encode($risk_data['levels']); ?>,
datasets: [{
data: <?php echo json_encode($risk_data['counts']); ?>,
backgroundColor: [
'rgba(54, 162, 235, 0.7)',
'rgba(75, 192, 192, 0.7)',
'rgba(255, 206, 86, 0.7)',
'rgba(255, 99, 132, 0.7)',
'rgba(153, 102, 255, 0.7)'
],
borderWidth: 1
}]
},
options: {
responsive: true,
plugins: {
legend: {
position: 'right',
}
}
}
});
</script>
<?php
}
/**
* 渲染客户来源分析图表
*
* @param array $source_data 客户来源数据
* @return void
*/
function renderCustomerSourceChart($source_data) {
?>
<div class="chart-container">
<div class="chart-header">
<h2 class="chart-title">客户来源分析</h2>
</div>
<canvas id="customerSourceChart"></canvas>
<div class="customer-stats-summary">
<div class="stats-row">
<?php
$total_customers = array_sum($source_data['counts']);
foreach ($source_data['sources'] as $index => $source):
?>
<div class="stat-item">
<span class="stat-label"><?php echo $source; ?>:</span>
<span class="stat-value"><?php echo number_format($source_data['counts'][$index]); ?>
(<?php echo ($total_customers > 0) ?
number_format(($source_data['counts'][$index] / $total_customers) * 100, 1) : '0'; ?>%)</span>
</div>
<?php endforeach; ?>
</div>
</div>
</div>
<script>
// 客户来源分析图
var sourceCtx = document.getElementById('customerSourceChart').getContext('2d');
var sourceChart = new Chart(sourceCtx, {
type: 'pie',
data: {
labels: <?php echo json_encode($source_data['sources']); ?>,
datasets: [{
data: <?php echo json_encode($source_data['counts']); ?>,
backgroundColor: [
'rgba(54, 162, 235, 0.7)',
'rgba(75, 192, 192, 0.7)',
'rgba(255, 206, 86, 0.7)',
'rgba(255, 99, 132, 0.7)',
'rgba(153, 102, 255, 0.7)',
'rgba(255, 159, 64, 0.7)'
],
borderWidth: 1
}]
},
options: {
responsive: true,
plugins: {
legend: {
position: 'right',
}
}
}
});
</script>
<?php
}
/**
* 获取客户转化漏斗数据
*
* @param mysqli $conn 数据库连接
* @param string $start_date 开始日期
* @param string $end_date 结束日期
* @return array 客户转化漏斗数据
*/
function getCustomerConversionFunnel($conn, $start_date, $end_date) {
// 获取总客户数（潜在客户）
$total_sql = "SELECT COUNT(id) as total FROM customer";
$total_result = $conn->query($total_sql);
$total_row = $total_result->fetch_assoc();
$total_customers = $total_row['total'];
// 获取明确需求的客户数
$needs_sql = "SELECT COUNT(id) as needs_count FROM customer WHERE cs_deal = 2";
$needs_result = $conn->query($needs_sql);
$needs_row = $needs_result->fetch_assoc();
$needs_customers = $needs_row['needs_count'];
// 获取已成交客户数
$deal_sql = "SELECT COUNT(id) as deal_count FROM customer WHERE cs_deal = 3";
$deal_result = $conn->query($deal_sql);
$deal_row = $deal_result->fetch_assoc();
$deal_customers = $deal_row['deal_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();
$order_customers = $order_row['order_count'];
// 获取复购客户数（多次下单）
$repeat_sql = "SELECT COUNT(customer_id) as repeat_count FROM (
SELECT customer_id, COUNT(id) as order_count
FROM orders
WHERE order_date BETWEEN ? AND ?
GROUP BY customer_id
HAVING order_count > 1
) as repeat_customers";
$repeat_stmt = $conn->prepare($repeat_sql);
$repeat_stmt->bind_param("ss", $start_date, $end_date);
$repeat_stmt->execute();
$repeat_result = $repeat_stmt->get_result();
$repeat_row = $repeat_result->fetch_assoc();
$repeat_customers = $repeat_row['repeat_count'];
return [
'stages' => ['潜在客户', '明确需求', '已成交', '有效订单', '复购客户'],
'counts' => [$total_customers, $needs_customers, $deal_customers, $order_customers, $repeat_customers]
];
}
/**
* 渲染客户转化漏斗图表
*
* @param array $funnel_data 客户转化漏斗数据
* @return void
*/
function renderCustomerFunnelChart($funnel_data) {
?>
<div class="chart-container">
<div class="chart-header">
<h2 class="chart-title">客户转化漏斗</h2>
</div>
<canvas id="customerFunnelChart" style="max-height: 400px;"></canvas>
<div class="customer-stats-summary">
<div class="stats-row">
<?php
foreach ($funnel_data['stages'] as $index => $stage):
$current_count = $funnel_data['counts'][$index];
$prev_count = $index > 0 ? $funnel_data['counts'][$index-1] : $current_count;
$conversion_rate = $prev_count > 0 ? ($current_count / $prev_count) * 100 : 0;
?>
<div class="stat-item">
<span class="stat-label"><?php echo $stage; ?>:</span>
<span class="stat-value"><?php echo number_format($current_count); ?></span>
<?php if ($index > 0): ?>
<span class="stat-conversion">
转化率: <?php echo number_format($conversion_rate, 1); ?>%
</span>
<?php endif; ?>
</div>
<?php endforeach; ?>
</div>
</div>
</div>
<script>
// 客户转化漏斗图
var funnelCtx = document.getElementById('customerFunnelChart').getContext('2d');
var funnelChart = new Chart(funnelCtx, {
type: 'bar',
data: {
labels: <?php echo json_encode($funnel_data['stages']); ?>,
datasets: [{
label: '客户数量',
data: <?php echo json_encode($funnel_data['counts']); ?>,
backgroundColor: [
'rgba(54, 162, 235, 0.7)',
'rgba(75, 192, 192, 0.7)',
'rgba(255, 206, 86, 0.7)',
'rgba(255, 99, 132, 0.7)',
'rgba(153, 102, 255, 0.7)'
],
borderWidth: 1
}]
},
options: {
indexAxis: 'y',
responsive: true,
scales: {
x: {
beginAtZero: true,
title: {
display: true,
text: '客户数量'
}
}
},
plugins: {
tooltip: {
callbacks: {
afterLabel: function(context) {
var index = context.dataIndex;
if (index > 0) {
var currentValue = context.parsed.x;
var previousValue = context.dataset.data[index-1];
var conversionRate = previousValue > 0 ? (currentValue / previousValue * 100).toFixed(1) : 0;
return '转化率: ' + conversionRate + '%';
}
return '';
}
}
}
}
}
});
</script>
<?php
}