query($sql_employees); // 计算上一个时间段范围（用于比较） $previous_start_date = ''; $previous_end_date = ''; // 根据当前选择的日期范围，计算上一个对比时段 if ($date_range == 'current_month') { // 上个月 $previous_start_date = date('Y-m-01', strtotime('-1 month', strtotime($current_start_date))); $previous_end_date = date('Y-m-t', strtotime('-1 month', strtotime($current_end_date))); } elseif ($date_range == 'last_month') { // 上上个月 $previous_start_date = date('Y-m-01', strtotime('-2 month', strtotime($current_start_date))); $previous_end_date = date('Y-m-t', strtotime('-2 month', strtotime($current_end_date))); } elseif ($date_range == 'current_year') { // 去年同期 $previous_start_date = date('Y-01-01', strtotime('-1 year', strtotime($current_start_date))); $previous_end_date = date('Y-12-31', strtotime('-1 year', strtotime($current_end_date))); } elseif ($date_range == 'last_30_days' || $date_range == 'last_90_days' || $date_range == 'custom') { // 上一个同长度周期 $date_diff = (strtotime($current_end_date) - strtotime($current_start_date)) / (60 * 60 * 24); $previous_end_date = date('Y-m-d', strtotime('-1 day', strtotime($current_start_date))); $previous_start_date = date('Y-m-d', strtotime("-{$date_diff} day", strtotime($previous_end_date))); } // 阈值设置（可以移到数据库或配置文件中） $order_amount_decrease_threshold = -15; // 订单金额下降超过15%触发预警 $repurchase_cycle_threshold = 90; // 复购周期超过90天触发预警（3个月内未录入订单） $inactive_threshold = 90; // 90天未有客户信息修改视为不活跃客户（3个月） $churn_threshold = 365; // 365天未下单视为流失客户（1年） $normal_repurchase_days = 30; // 正常复购周期参考值（天） // 页面头部 include('statistics_header.php'); ?>

订单预警系统

监控订单异常情况，提前预警潜在问题

prepare($sql_total_warnings); $stmt->bind_param("ss", $current_start_date, $current_end_date); $stmt->execute(); $result = $stmt->get_result(); $warning_count = $result->fetch_assoc(); // 获取订单金额下降的客户数 $decreasing_amount_count = getDecreasingOrderAmountCustomers($conn, $current_start_date, $current_end_date, $previous_start_date, $previous_end_date, $order_amount_decrease_threshold, true, $selected_employee); // 获取复购周期异常（3个月内未录入订单）的客户数 $abnormal_cycle_count = getAbnormalRepurchaseCycleCustomers($conn, $current_start_date, $current_end_date, $repurchase_cycle_threshold, true, $selected_employee); // 获取长期不活跃（3个月内没有客户信息修改）客户数 $inactive_customers_count = getInactiveCustomers($conn, $current_end_date, $inactive_threshold, true, 1, 10, $selected_employee); // 获取流失客户（1年内未录入订单）数 $churn_customers_count = getChurnCustomers($conn, $current_end_date, $churn_threshold, true, 1, 10, $selected_employee); ?>

订单金额下降客户

金额下降超过%

复购周期异常客户

3个月内未录入订单

流失客户

1年内未录入订单

长期不活跃客户

3个月内无客户信息更新

订单金额下降客户

与上一周期相比，订单金额显著下降的客户

fetch_assoc()) { $change_percent = round((($customer['current_amount'] - $customer['previous_amount']) / $customer['previous_amount']) * 100, 1); $change_class = $change_percent < -20 ? 'text-danger' : 'text-warning'; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; } if ($decreasing_customers->num_rows == 0) { echo ""; } ?>
客户名称 本期订单金额 上期订单金额 变化百分比 最近出货日期 业务员 操作
" . htmlspecialchars($customer['cs_company']) . "¥" . number_format($customer['current_amount'], 2) . "¥" . number_format($customer['previous_amount'], 2) . "" . $change_percent . "%" . $customer['last_order_date'] . "" . htmlspecialchars($customer['em_user']) . "查看
没有发现订单金额下降的客户

复购周期异常客户

3个月内未录入订单的客户

$abnormal_total_pages && $abnormal_total_pages > 0) $abnormal_page = $abnormal_total_pages; // 获取当页数据 $abnormal_customers = getAbnormalRepurchaseCycleCustomers( $conn, $current_start_date, $current_end_date, $repurchase_cycle_threshold, false, $selected_employee, $abnormal_page, $abnormal_page_size ); while ($customer = $abnormal_customers->fetch_assoc()) { $inactive_days = $customer['inactive_days']; $inactive_class = $inactive_days > 60 ? 'text-danger' : 'text-warning'; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; } if ($abnormal_customers->num_rows == 0) { echo ""; } ?>
客户编码 客户名称 上次订单日期 未订单天数 历史订单总数 历史订单总额 业务员 操作
" . htmlspecialchars($customer['cs_code']) . "" . htmlspecialchars($customer['cs_company'] ?: '未填写') . "" . ($customer['last_order_date'] ? $customer['last_order_date'] : '从未下单') . "" . $inactive_days . "" . $customer['order_count'] . "¥" . number_format($customer['total_amount'], 2) . "" . htmlspecialchars($customer['em_user']) . "查看
没有发现复购周期异常的客户
1): ?>
共 条记录，当前显示第 页，共 页

流失客户

1年内未录入订单的客户

$total_pages && $total_pages > 0) $page = $total_pages; // 获取当页数据 $churn_customers = getChurnCustomers($conn, $current_end_date, $churn_threshold, false, $page, $page_size, $selected_employee); while ($customer = $churn_customers->fetch_assoc()) { $inactive_days = $customer['inactive_days']; $inactive_class = $inactive_days > 365 ? 'text-danger' : 'text-warning'; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; } if ($churn_customers->num_rows == 0) { echo ""; } ?>
客户编码 客户名称 最后出货日期 未订单天数 历史订单数 历史订单总额 业务员 操作
" . htmlspecialchars($customer['cs_code']) . "" . htmlspecialchars($customer['cs_company'] ?: '未填写') . "" . ($customer['last_order_date'] ? $customer['last_order_date'] : '从未下单') . "" . $inactive_days . "" . $customer['order_count'] . "¥" . number_format($customer['total_amount'], 2) . "" . htmlspecialchars($customer['em_user']) . "查看
没有发现流失客户
1): ?>
共 条记录，当前显示第 页，共 页

总体订单趋势

最近12个月的订单数量和金额趋势

= DATE_SUB(?, INTERVAL 11 MONTH) GROUP BY DATE_FORMAT(order_date, '%Y-%m') ORDER BY month"; $stmt = $conn->prepare($sql_trend); $stmt->bind_param("s", $current_end_date); $stmt->execute(); $trend_result = $stmt->get_result(); $months = []; $order_counts = []; $order_amounts = []; while ($row = $trend_result->fetch_assoc()) { $months[] = $row['month']; $order_counts[] = $row['order_count']; $order_amounts[] = $row['total_amount']; } // 转为JSON格式，用于JavaScript图表 $months_json = json_encode($months); $order_counts_json = json_encode($order_counts); $order_amounts_json = json_encode($order_amounts); ?>
0 ? " AND c.cs_belong = " . intval($selected_employee) : ""; // 如果只需要计数 if ($count_only) { $sql = "SELECT COUNT(DISTINCT c.id) as count FROM customer c LEFT JOIN ( SELECT customer_id, SUM(total_amount) as amount FROM orders WHERE order_date BETWEEN '{$current_start}' AND '{$current_end}' GROUP BY customer_id ) current_period ON c.id = current_period.customer_id LEFT JOIN ( SELECT customer_id, SUM(total_amount) as amount FROM orders WHERE order_date BETWEEN '{$previous_start}' AND '{$previous_end}' GROUP BY customer_id ) previous_period ON c.id = previous_period.customer_id JOIN employee e ON c.cs_belong = e.id WHERE previous_period.amount > 0 AND (current_period.amount IS NULL OR (current_period.amount / previous_period.amount - 1) * 100 <= {$threshold}) AND c.cs_deal = 3{$employee_filter}"; $result = $conn->query($sql); $row = $result->fetch_assoc(); return $row['count']; } // 如果需要详细数据 $sql = "SELECT c.id, c.cs_company, IFNULL(current_period.amount, 0) as current_amount, previous_period.amount as previous_amount, e.em_user, IFNULL((SELECT MAX(order_date) FROM orders WHERE customer_id = c.id), '') as last_order_date FROM customer c LEFT JOIN ( SELECT customer_id, SUM(total_amount) as amount FROM orders WHERE order_date BETWEEN '{$current_start}' AND '{$current_end}' GROUP BY customer_id ) current_period ON c.id = current_period.customer_id LEFT JOIN ( SELECT customer_id, SUM(total_amount) as amount FROM orders WHERE order_date BETWEEN '{$previous_start}' AND '{$previous_end}' GROUP BY customer_id ) previous_period ON c.id = previous_period.customer_id JOIN employee e ON c.cs_belong = e.id WHERE previous_period.amount > 0 AND (current_period.amount IS NULL OR (current_period.amount / previous_period.amount - 1) * 100 <= {$threshold}) AND c.cs_deal = 3{$employee_filter} ORDER BY (current_period.amount / previous_period.amount) ASC"; return $conn->query($sql); } /** * 获取复购周期异常的客户（3个月内未录入订单） */ function getAbnormalRepurchaseCycleCustomers($conn, $current_start, $current_end, $threshold, $count_only = false, $selected_employee = 0, $page = 1, $page_size = 10) { // 构建业务员筛选条件 $employee_filter = $selected_employee > 0 ? " AND c.cs_belong = " . intval($selected_employee) : ""; if ($count_only) { $sql = "SELECT COUNT(DISTINCT c.id) as count FROM customer c LEFT JOIN ( SELECT customer_id, MAX(order_date) as last_order_date FROM orders GROUP BY customer_id ) last_orders ON c.id = last_orders.customer_id JOIN employee e ON c.cs_belong = e.id WHERE c.cs_deal = 3 AND ( last_orders.last_order_date IS NULL OR DATEDIFF('{$current_end}', last_orders.last_order_date) > {$threshold} ){$employee_filter}"; $result = $conn->query($sql); $row = $result->fetch_assoc(); return $row['count']; } $offset = ($page - 1) * $page_size; $sql = "SELECT c.id, c.cs_company, c.cs_code, last_orders.last_order_date, CASE WHEN last_orders.last_order_date IS NULL THEN DATEDIFF('{$current_end}', c.cs_addtime) ELSE DATEDIFF('{$current_end}', last_orders.last_order_date) END as inactive_days, IFNULL(order_stats.order_count, 0) as order_count, IFNULL(order_stats.total_amount, 0) as total_amount, e.em_user FROM customer c LEFT JOIN ( SELECT customer_id, MAX(order_date) as last_order_date FROM orders GROUP BY customer_id ) last_orders ON c.id = last_orders.customer_id LEFT JOIN ( SELECT customer_id, COUNT(*) as order_count, SUM(total_amount) as total_amount FROM orders GROUP BY customer_id ) order_stats ON c.id = order_stats.customer_id JOIN employee e ON c.cs_belong = e.id WHERE c.cs_deal = 3 AND ( last_orders.last_order_date IS NULL OR DATEDIFF('{$current_end}', last_orders.last_order_date) > {$threshold} ){$employee_filter} ORDER BY inactive_days DESC LIMIT {$offset}, {$page_size}"; return $conn->query($sql); } /** * 获取长期不活跃的客户（3个月内没有客户信息修改） */ function getInactiveCustomers($conn, $end_date, $inactive_days, $count_only = false, $page = 1, $page_size = 10, $selected_employee = 0) { // 构建业务员筛选条件 $employee_filter = $selected_employee > 0 ? " AND c.cs_belong = " . intval($selected_employee) : ""; if ($count_only) { $sql = "SELECT COUNT(*) as count FROM customer c JOIN employee e ON c.cs_belong = e.id WHERE c.cs_deal = 3 AND DATEDIFF('{$end_date}', c.cs_updatetime) > {$inactive_days}{$employee_filter}"; $result = $conn->query($sql); $row = $result->fetch_assoc(); return $row['count']; } $offset = ($page - 1) * $page_size; $sql = "SELECT c.id, c.cs_company, c.cs_code, last_orders.last_order_date, DATEDIFF('{$end_date}', c.cs_updatetime) as inactive_days, c.cs_updatetime as last_update_time, IFNULL(order_stats.order_count, 0) as order_count, IFNULL(order_stats.total_amount, 0) as total_amount, e.em_user FROM customer c LEFT JOIN ( SELECT customer_id, MAX(order_date) as last_order_date FROM orders GROUP BY customer_id ) last_orders ON c.id = last_orders.customer_id LEFT JOIN ( SELECT customer_id, COUNT(*) as order_count, SUM(total_amount) as total_amount FROM orders GROUP BY customer_id ) order_stats ON c.id = order_stats.customer_id JOIN employee e ON c.cs_belong = e.id WHERE c.cs_deal = 3 AND DATEDIFF('{$end_date}', c.cs_updatetime) > {$inactive_days}{$employee_filter} ORDER BY inactive_days DESC LIMIT {$offset}, {$page_size}"; return $conn->query($sql); } /** * 获取流失客户（1年内未录入订单） */ function getChurnCustomers($conn, $end_date, $churn_days, $count_only = false, $page = 1, $page_size = 10, $selected_employee = 0) { // 构建业务员筛选条件 $employee_filter = $selected_employee > 0 ? " AND c.cs_belong = " . intval($selected_employee) : ""; if ($count_only) { $sql = "SELECT COUNT(*) as count FROM customer c LEFT JOIN ( SELECT customer_id, MAX(order_date) as last_order_date FROM orders GROUP BY customer_id ) last_orders ON c.id = last_orders.customer_id JOIN employee e ON c.cs_belong = e.id WHERE c.cs_deal = 3 AND ( last_orders.last_order_date IS NULL OR DATEDIFF('{$end_date}', last_orders.last_order_date) > {$churn_days} ){$employee_filter}"; $result = $conn->query($sql); $row = $result->fetch_assoc(); return $row['count']; } $offset = ($page - 1) * $page_size; $sql = "SELECT c.id, c.cs_company, c.cs_code, last_orders.last_order_date, CASE WHEN last_orders.last_order_date IS NULL THEN DATEDIFF('{$end_date}', c.cs_addtime) ELSE DATEDIFF('{$end_date}', last_orders.last_order_date) END as inactive_days, IFNULL(order_stats.order_count, 0) as order_count, IFNULL(order_stats.total_amount, 0) as total_amount, e.em_user FROM customer c LEFT JOIN ( SELECT customer_id, MAX(order_date) as last_order_date FROM orders GROUP BY customer_id ) last_orders ON c.id = last_orders.customer_id LEFT JOIN ( SELECT customer_id, COUNT(*) as order_count, SUM(total_amount) as total_amount FROM orders GROUP BY customer_id ) order_stats ON c.id = order_stats.customer_id JOIN employee e ON c.cs_belong = e.id WHERE c.cs_deal = 3 AND ( last_orders.last_order_date IS NULL OR DATEDIFF('{$end_date}', last_orders.last_order_date) > {$churn_days} ){$employee_filter} ORDER BY inactive_days DESC LIMIT {$offset}, {$page_size}"; return $conn->query($sql); } // 页面底部 include('statistics_footer.php'); ?>