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_max_threshold = 90; // 复购周期超过90天触发预警 $repurchase_cycle_min_threshold = 0.5; // 复购周期小于正常值的50%触发预警（异常频繁购买） $inactive_threshold = 60; // 60天未购买视为不活跃客户 $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); // 获取复购周期异常的客户数 $abnormal_cycle_count = getAbnormalRepurchaseCycleCustomers($conn, $current_start_date, $current_end_date, $repurchase_cycle_max_threshold, $repurchase_cycle_min_threshold, $normal_repurchase_days, true, $selected_employee); // 获取长期不活跃客户数 $inactive_customers_count = getInactiveCustomers($conn, $current_end_date, $inactive_threshold, true, 1, 10, $selected_employee); ?>

订单金额下降客户

金额下降超过%

复购周期异常客户

周期异常或不规律

长期不活跃客户

天以上未下单

客户活跃率

0) ? round(($warning_count['active_customers'] / $warning_count['total_customers']) * 100, 1) : 0; echo $active_rate . '%'; ?>
选定周期内下单客户占比

订单金额下降客户

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

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']) . "查看
没有发现订单金额下降的客户

复购周期异常客户

复购周期异常延长或缩短的客户

fetch_assoc()) { $deviation = round((($customer['recent_cycle'] - $normal_repurchase_days) / $normal_repurchase_days) * 100, 1); $deviation_text = $deviation > 0 ? "+{$deviation}%" : "{$deviation}%"; $deviation_class = $deviation > 50 ? 'text-danger' : ($deviation < -30 ? 'text-warning' : 'text-info'); echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; } if ($abnormal_customers->num_rows == 0) { echo ""; } ?>
客户名称 平均复购周期(天) 最近复购周期(天) 偏离正常值 最近订单日期 订单总数 业务员 操作
" . htmlspecialchars($customer['cs_company']) . "" . round($customer['avg_cycle'], 1) . "" . round($customer['recent_cycle'], 1) . "" . $deviation_text . "" . $customer['last_order_date'] . "" . $customer['order_count'] . "" . htmlspecialchars($customer['em_user']) . "查看
没有发现复购周期异常的客户

长期不活跃客户

超过天未下单的客户

$total_pages && $total_pages > 0) $page = $total_pages; // 获取当页数据 $inactive_customers = getInactiveCustomers($conn, $current_end_date, $inactive_threshold, false, $page, $page_size, $selected_employee); while ($customer = $inactive_customers->fetch_assoc()) { $inactive_days = $customer['inactive_days']; $inactive_class = $inactive_days > 90 ? 'text-danger' : 'text-warning'; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; } if ($inactive_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 = ?" : ""; // 如果只需要计数 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 ? AND ? 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 ? AND ? 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 <= ?) AND c.cs_deal = 3" . $employee_filter; $stmt = $conn->prepare($sql); if ($selected_employee > 0) { $stmt->bind_param("ssssdi", $current_start, $current_end, $previous_start, $previous_end, $threshold, $selected_employee); } else { $stmt->bind_param("ssssd", $current_start, $current_end, $previous_start, $previous_end, $threshold); } $stmt->execute(); $result = $stmt->get_result(); $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 ? AND ? 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 ? AND ? 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 <= ?) AND c.cs_deal = 3" . $employee_filter . " ORDER BY (current_period.amount / previous_period.amount) ASC"; $stmt = $conn->prepare($sql); if ($selected_employee > 0) { $stmt->bind_param("ssssdi", $current_start, $current_end, $previous_start, $previous_end, $threshold, $selected_employee); } else { $stmt->bind_param("ssssd", $current_start, $current_end, $previous_start, $previous_end, $threshold); } $stmt->execute(); return $stmt->get_result(); } /** * 获取复购周期异常的客户 */ function getAbnormalRepurchaseCycleCustomers($conn, $current_start, $current_end, $max_threshold, $min_threshold, $normal_cycle, $count_only = false, $selected_employee = 0) { // 构建业务员筛选条件 $employee_filter = $selected_employee > 0 ? " AND c.cs_belong = ?" : ""; if ($count_only) { $sql = "SELECT COUNT(DISTINCT t.customer_id) as count FROM ( SELECT o.customer_id, COUNT(o.id) as order_count, AVG(DATEDIFF(o.order_date, prev_order.order_date)) as avg_cycle, MAX(DATEDIFF(o.order_date, prev_order.order_date)) as recent_cycle FROM orders o JOIN customer c ON o.customer_id = c.id JOIN orders prev_order ON o.customer_id = prev_order.customer_id AND prev_order.order_date < o.order_date WHERE o.order_date BETWEEN ? AND ?" . $employee_filter . " GROUP BY o.customer_id HAVING order_count > 1 AND (recent_cycle > ? OR recent_cycle < (? * ?)) ) t"; $stmt = $conn->prepare($sql); if ($selected_employee > 0) { $stmt->bind_param("ssiddd", $current_start, $current_end, $selected_employee, $max_threshold, $normal_cycle, $min_threshold); } else { $stmt->bind_param("ssddd", $current_start, $current_end, $max_threshold, $normal_cycle, $min_threshold); } $stmt->execute(); $result = $stmt->get_result(); $row = $result->fetch_assoc(); return $row['count']; } // 使用子查询方式，先获取所有符合条件的客户及其复购周期数据 $sql = "SELECT abnormal.customer_id as id, c.cs_company, abnormal.order_count, abnormal.avg_cycle, abnormal.recent_cycle, abnormal.last_order_date, e.em_user, CASE WHEN abnormal.recent_cycle > ? THEN 1 /* 周期过长 */ ELSE 2 /* 周期过短 */ END as cycle_type, CASE WHEN abnormal.recent_cycle > ? THEN abnormal.recent_cycle ELSE (? * ?) - abnormal.recent_cycle END as sort_value FROM ( SELECT o.customer_id, COUNT(o.id) as order_count, AVG(DATEDIFF(o.order_date, prev_order.order_date)) as avg_cycle, MAX(DATEDIFF(o.order_date, prev_order.order_date)) as recent_cycle, (SELECT MAX(order_date) FROM orders WHERE customer_id = o.customer_id) as last_order_date FROM orders o JOIN customer c ON o.customer_id = c.id JOIN orders prev_order ON o.customer_id = prev_order.customer_id AND prev_order.order_date < o.order_date WHERE o.order_date BETWEEN ? AND ?" . $employee_filter . " GROUP BY o.customer_id HAVING order_count > 1 AND (recent_cycle > ? OR recent_cycle < (? * ?)) ) as abnormal JOIN customer c ON abnormal.customer_id = c.id JOIN employee e ON c.cs_belong = e.id ORDER BY sort_value DESC"; $stmt = $conn->prepare($sql); if ($selected_employee > 0) { $stmt->bind_param("ddddssiddd", $max_threshold, $max_threshold, $normal_cycle, $min_threshold, $current_start, $current_end, $selected_employee, $max_threshold, $normal_cycle, $min_threshold); } else { $stmt->bind_param("ddddssddd", $max_threshold, $max_threshold, $normal_cycle, $min_threshold, $current_start, $current_end, $max_threshold, $normal_cycle, $min_threshold); } $stmt->execute(); return $stmt->get_result(); } /** * 获取长期不活跃的客户 */ 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 = ?" : ""; 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(?, last_orders.last_order_date) > ? )" . $employee_filter; $stmt = $conn->prepare($sql); if ($selected_employee > 0) { $stmt->bind_param("sii", $end_date, $inactive_days, $selected_employee); } else { $stmt->bind_param("si", $end_date, $inactive_days); } $stmt->execute(); $result = $stmt->get_result(); $row = $result->fetch_assoc(); return $row['count']; } $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(?, c.cs_addtime) ELSE DATEDIFF(?, 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(?, last_orders.last_order_date) > ? )" . $employee_filter . " ORDER BY inactive_days DESC LIMIT ?, ?"; $offset = ($page - 1) * $page_size; $stmt = $conn->prepare($sql); if ($selected_employee > 0) { $stmt->bind_param("sssiiii", $end_date, $end_date, $end_date, $inactive_days, $selected_employee, $offset, $page_size); } else { $stmt->bind_param("sssiii", $end_date, $end_date, $end_date, $inactive_days, $offset, $page_size); } $stmt->execute(); return $stmt->get_result(); } // 页面底部 include('statistics_footer.php'); ?>