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) {
?>
总销售额:
¥
新客户销售额:
¥
(%)
老客户销售额:
¥
(%)