mtb_crm_asp/statistics_order_warnings.php

<?php
/**
 * 订单预警系统 - 监控订单异常情况
 */
require_once 'conn.php';
require_once 'statistics_utils.php';

// 检查登录状态
if (!isset($_SESSION['employee_id'])) {
    checkLogin();
}

// 获取当前登录用户信息
$current_user_id = $_SESSION['employee_id'];
$current_permission_role = 0;

// 获取当前用户权限角色
$current_user_id = intval($current_user_id); // 确保是整数
$query = "SELECT em_permission_role_id FROM employee WHERE id = $current_user_id";
$result = $conn->query($query);
if ($result && $row = $result->fetch_assoc()) {
    $current_permission_role = $row['em_permission_role_id'];
}

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

// 获取选中的业务员ID
$selected_employee = isset($_GET['employee_id']) ? intval($_GET['employee_id']) : 0;

// 确定要显示哪些业务员的数据
$employee_filter = null;

if ($selected_employee > 0) {
    // 如果选择了特定业务员，检查当前用户是否有权限查看该业务员的数据
    $has_permission = false;
    
    if ($current_permission_role == 1) {
        // 管理员可以查看所有业务员
        $has_permission = true;
    } else if ($current_permission_role == 2) {
        // 组长可以查看自己和组员
        $query = "SELECT id FROM employee WHERE id = $selected_employee AND (id = $current_user_id OR em_role = $current_user_id)";
        $result = $conn->query($query);
        $has_permission = ($result && $result->num_rows > 0);
    } else {
        // 普通业务员只能查看自己
        $has_permission = ($selected_employee == $current_user_id);
    }
    
    if ($has_permission) {
        $employee_filter = $selected_employee;
    } else {
        // 如果没有权限，重置为查看自己的数据
        $selected_employee = $current_user_id;
        $employee_filter = $current_user_id;
    }
} else {
    // 如果没有选择特定业务员，则按权限显示相应的业务员数据
    if ($current_permission_role == 1) {
        // 管理员可以看到所有业务员
        $employee_filter = null;
    } else if ($current_permission_role == 2) {
        // 组长可以看到自己和组员
        $visible_employees = [];
        $query = "SELECT id FROM employee WHERE id = $current_user_id OR em_role = $current_user_id";
        $result = $conn->query($query);
        
        if ($result) {
            while ($row = $result->fetch_assoc()) {
                $visible_employees[] = intval($row['id']);
            }
        }
        
        if (!empty($visible_employees)) {
            $employee_filter = $visible_employees;
        } else {
            $employee_filter = $current_user_id;
        }
    } else {
        // 普通业务员只能看到自己
        $employee_filter = $current_user_id;
    }
}

// 获取业务员列表（基于权限）
$sql_employees = "";
if ($current_permission_role == 1) {
    // 管理员可以看到所有业务员
    $sql_employees = "SELECT id, em_user FROM employee WHERE em_role IS NOT NULL ORDER BY em_user";
} else if ($current_permission_role == 2) {
    // 组长可以看到自己和组员
    $sql_employees = "SELECT id, em_user FROM employee WHERE id = $current_user_id OR em_role = $current_user_id ORDER BY em_user";
} else {
    // 普通业务员只能看到自己
    $sql_employees = "SELECT id, em_user FROM employee WHERE id = $current_user_id";
}
$employees_result = $conn->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');
?>


    <div class="page-header">
        <h1 class="page-title">订单预警系统</h1>
        <p class="page-description">监控订单异常情况，提前预警潜在问题</p>
        
        <?php
        // 获取当前用户角色显示提示信息
        $role_info = "";
        
        if ($current_permission_role == 1) {
            // 管理员
            if ($selected_employee > 0) {
                $employee_name = "";
                $emp_query = "SELECT em_user FROM employee WHERE id = $selected_employee";
                $emp_result = $conn->query($emp_query);
                if ($emp_result && $emp_row = $emp_result->fetch_assoc()) {
                    $employee_name = $emp_row['em_user'];
                    $role_info = "您正在查看业务员 {$employee_name} 的数据";
                }
            } else {
                $role_info = "您正在查看所有业务员的数据";
            }
        } else if ($current_permission_role == 2) {
            // 组长
            if ($selected_employee > 0 && $selected_employee != $current_user_id) {
                $employee_name = "";
                $emp_query = "SELECT em_user FROM employee WHERE id = $selected_employee";
                $emp_result = $conn->query($emp_query);
                if ($emp_result && $emp_row = $emp_result->fetch_assoc()) {
                    $employee_name = $emp_row['em_user'];
                    $role_info = "您正在查看业务员 {$employee_name} 的数据";
                }
            } else if ($selected_employee == 0 || $selected_employee == $current_user_id) {
                $role_info = "您正在查看您的团队数据";
            }
        } else {
            // 普通业务员
            $role_info = "您正在查看自己的数据";
        }
        ?>
        <div class="role-info"><?php echo $role_info; ?></div>
    </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">
                <label for="employee_id">业务员</label>
                <select class="form-control" id="employee_id" name="employee_id">
                    <option value="0">全部业务员</option>
                    <?php while ($emp = $employees_result->fetch_assoc()): ?>
                    <option value="<?php echo $emp['id']; ?>" <?php echo $selected_employee == $emp['id'] ? 'selected' : ''; ?>><?php echo htmlspecialcharsFix($emp['em_user']); ?></option>
                    <?php endwhile; ?>
                </select>
            </div>
            <div class="form-group">
                <button type="submit" class="form-btn form-btn-primary">应用筛选</button>
            </div>
        </form>
    </div>
    
    <!-- 预警概览 -->
    <div class="warnings-overview">
        <div class="row">
            <?php
            // 获取总预警数
            $sql_total_warnings = "SELECT 
                (SELECT COUNT(*) FROM customer WHERE cs_deal = 3) as total_customers,
                (SELECT COUNT(DISTINCT customer_id) FROM orders WHERE is_deleted = 0 AND order_date BETWEEN ? AND ?) as active_customers";
            
            $stmt = $conn->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();
            
            // 获取订单金额下降的客户数 - 使用当前用户的筛选条件
            $query_employee_filter = null;
            if (is_array($employee_filter) && !empty($employee_filter)) {
                // 如果是组长查看团队，转换为SQL中的IN条件
                $query_employee_filter = implode(',', $employee_filter);
            } else if (!is_array($employee_filter) && $employee_filter > 0) {
                // 如果是查看单个业务员
                $query_employee_filter = $employee_filter;
            }
            
            $decreasing_amount_count = getDecreasingOrderAmountCustomers(
                $conn, 
                $current_start_date, 
                $current_end_date, 
                $previous_start_date, 
                $previous_end_date, 
                $order_amount_decrease_threshold, 
                true, 
                $query_employee_filter
            );
            
            // 获取复购周期异常（3个月内未录入订单）的客户数
            $abnormal_cycle_count = getAbnormalRepurchaseCycleCustomers(
                $conn, 
                $current_start_date, 
                $current_end_date, 
                $repurchase_cycle_threshold, 
                true, 
                $query_employee_filter
            );
            
            // 获取长期不活跃（3个月内没有客户信息修改）客户数
            $inactive_customers_count = getInactiveCustomers(
                $conn, 
                $current_end_date, 
                $inactive_threshold, 
                true, 
                1, 
                10, 
                $query_employee_filter
            );
            
            // 获取流失客户（1年内未录入订单）数
            $churn_customers_count = getChurnCustomers(
                $conn, 
                $current_end_date, 
                $churn_threshold, 
                true, 
                1, 
                10, 
                $query_employee_filter
            );
            ?>
            
            <div class="col-md-3">
                <div class="stat-card warning">
                    <h3>订单金额下降客户</h3>
                    <div class="stat-value"><?php echo $decreasing_amount_count; ?></div>
                    <div class="stat-desc">金额下降超过<?php echo abs($order_amount_decrease_threshold); ?>%</div>
                </div>
            </div>
            
            <div class="col-md-3">
                <div class="stat-card warning">
                    <h3>复购周期异常客户</h3>
                    <div class="stat-value"><?php echo $abnormal_cycle_count; ?></div>
                    <div class="stat-desc">3个月内未录入订单</div>
                </div>
            </div>
            
            <div class="col-md-3">
                <div class="stat-card danger">
                    <h3>流失客户</h3>
                    <div class="stat-value"><?php echo $churn_customers_count; ?></div>
                    <div class="stat-desc">1年内未录入订单</div>
                </div>
            </div>
            
            <div class="col-md-3">
                <div class="stat-card info">
                    <h3>长期不活跃客户</h3>
                    <div class="stat-value"><?php echo $inactive_customers_count; ?></div>
                    <div class="stat-desc">3个月内无客户信息更新</div>
                </div>
            </div>
        </div>
    </div>
    
    <!-- 订单金额下降客户列表 -->
    <div class="warning-section">
        <div class="section-header">
            <h2>订单金额下降客户</h2>
            <p>与上一周期相比，订单金额显著下降的客户</p>
        </div>
        
        <table class="data-table">
            <thead>
                <tr>
                    <th>本期订单金额</th>
                    <th>上期订单金额</th>
                    <th>变化百分比</th>
                    <th>最近出货日期</th>
                    <th>业务员</th>
                    <th>操作</th>
                </tr>
            </thead>
            <tbody>
                <?php
                $decreasing_customers = getDecreasingOrderAmountCustomers(
                    $conn, 
                    $current_start_date, 
                    $current_end_date, 
                    $previous_start_date, 
                    $previous_end_date, 
                    $order_amount_decrease_threshold,
                    false,
                    $query_employee_filter
                );
                
                while ($customer = $decreasing_customers->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 "<tr>";
                    echo "<td>¥" . number_format($customer['current_amount'], 2) . "</td>";
                    echo "<td>¥" . number_format($customer['previous_amount'], 2) . "</td>";
                    echo "<td class='{$change_class}'>" . $change_percent . "%</td>";
                    echo "<td>" . $customer['last_order_date'] . "</td>";
                    echo "<td>" . htmlspecialcharsFix($customer['em_user']) . "</td>";
                    echo "<td><a href='customer_detail.php?id=" . $customer['id'] . "&from_warning=1' class='action-btn action-btn-view'>查看</a></td>";
                    echo "</tr>";
                }
                
                if ($decreasing_customers->num_rows == 0) {
                    echo "<tr><td colspan='6' class='text-center'>没有发现订单金额下降的客户</td></tr>";
                }
                ?>
            </tbody>
        </table>
    </div>
    
    <!-- 复购周期异常客户列表 -->
    <div class="warning-section" id="abnormal-customers">
        <div class="section-header">
            <h2>复购周期异常客户</h2>
            <p>3个月内未录入订单的客户</p>
        </div>
        
        <table class="data-table">
            <thead>
                <tr>
                    <th>客户编码</th>
                    <th>上次订单日期</th>
                    <th>未订单天数</th>
                    <th>历史订单总数</th>
                    <th>历史订单总额</th>
                    <th>业务员</th>
                    <th>操作</th>
                </tr>
            </thead>
            <tbody>
                <?php
                // 获取分页参数
                $abnormal_page = isset($_GET['abnormal_page']) ? intval($_GET['abnormal_page']) : 1;
                $abnormal_page_size = 10; // 每页显示10条记录
                
                // 获取总记录数
                $total_abnormal = getAbnormalRepurchaseCycleCustomers($conn, $current_start_date, $current_end_date, $repurchase_cycle_threshold, true, $query_employee_filter);
                
                // 计算总页数
                $abnormal_total_pages = ceil($total_abnormal / $abnormal_page_size);
                
                // 确保页码合法
                if ($abnormal_page < 1) $abnormal_page = 1;
                if ($abnormal_page > $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,
                    $query_employee_filter,
                    $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 "<tr>";
                    echo "<td title='{$customer['cs_code']}'>" . htmlspecialcharsFix($customer['cs_code']) . "</td>";
                    echo "<td>" . ($customer['last_order_date'] ? $customer['last_order_date'] : '从未下单') . "</td>";
                    echo "<td class='{$inactive_class}'>" . $inactive_days . "</td>";
                    echo "<td>" . $customer['order_count'] . "</td>";
                    echo "<td>¥" . number_format($customer['total_amount'], 2) . "</td>";
                    echo "<td>" . htmlspecialcharsFix($customer['em_user']) . "</td>";
                    echo "<td><a href='customer_detail.php?id=" . $customer['id'] . "&from_warning=1' class='action-btn action-btn-view'>查看</a></td>";
                    echo "</tr>";
                }
                
                if ($abnormal_customers->num_rows == 0) {
                    echo "<tr><td colspan='7' class='text-center'>没有发现复购周期异常的客户</td></tr>";
                }
                ?>
            </tbody>
        </table>
        
        <!-- 分页控件 -->
        <?php if ($abnormal_total_pages > 1): ?>
        <div class="pagination-container">
            <ul class="pagination">
                <?php
                // 生成分页链接的基础URL
                $base_url = '?';
                foreach ($_GET as $key => $value) {
                    if ($key != 'abnormal_page') {
                        $base_url .= $key . '=' . urlencode($value) . '&';
                    }
                }
                
                // 上一页链接
                if ($abnormal_page > 1) {
                    echo "<li class='pager-item'><a class='pager-link' href='{$base_url}abnormal_page=" . ($abnormal_page - 1) . "#abnormal-customers'>上一页</a></li>";
                } else {
                    echo "<li class='pager-item disabled'><a class='pager-link' href='#abnormal-customers'>上一页</a></li>";
                }
                
                // 页码链接
                $start_page = max(1, $abnormal_page - 2);
                $end_page = min($abnormal_total_pages, $abnormal_page + 2);
                
                if ($start_page > 1) {
                    echo "<li class='pager-item'><a class='pager-link' href='{$base_url}abnormal_page=1#abnormal-customers'>1</a></li>";
                    if ($start_page > 2) {
                        echo "<li class='pager-item disabled'><a class='pager-link' href='#abnormal-customers'>...</a></li>";
                    }
                }
                
                for ($i = $start_page; $i <= $end_page; $i++) {
                    if ($i == $abnormal_page) {
                        echo "<li class='pager-item active'><a class='pager-link' href='#abnormal-customers'>{$i}</a></li>";
                    } else {
                        echo "<li class='pager-item'><a class='pager-link' href='{$base_url}abnormal_page={$i}#abnormal-customers'>{$i}</a></li>";
                    }
                }
                
                if ($end_page < $abnormal_total_pages) {
                    if ($end_page < $abnormal_total_pages - 1) {
                        echo "<li class='pager-item disabled'><a class='pager-link' href='#abnormal-customers'>...</a></li>";
                    }
                    echo "<li class='pager-item'><a class='pager-link' href='{$base_url}abnormal_page={$abnormal_total_pages}#abnormal-customers'>{$abnormal_total_pages}</a></li>";
                }
                
                // 下一页链接
                if ($abnormal_page < $abnormal_total_pages) {
                    echo "<li class='pager-item'><a class='pager-link' href='{$base_url}abnormal_page=" . ($abnormal_page + 1) . "#abnormal-customers'>下一页</a></li>";
                } else {
                    echo "<li class='pager-item disabled'><a class='pager-link' href='#abnormal-customers'>下一页</a></li>";
                }
                ?>
            </ul>
            <div class="pagination-info">
                共 <?php echo $total_abnormal; ?> 条记录，当前显示第 <?php echo $abnormal_page; ?> 页，共 <?php echo $abnormal_total_pages; ?> 页
            </div>
        </div>
        <?php endif; ?>
    </div>
    
    <!-- 流失客户列表 -->
    <div class="warning-section">
        <div class="section-header">
            <h2>流失客户</h2>
            <p>1年内未录入订单的客户</p>
        </div>
        
        <table class="data-table">
            <thead>
                <tr>
                    <th>客户编码</th>
                    <th>最后出货日期</th>
                    <th>未订单天数</th>
                    <th>历史订单数</th>
                    <th>历史订单总额</th>
                    <th>业务员</th>
                    <th>操作</th>
                </tr>
            </thead>
            <tbody>
                <?php
                // 获取分页参数
                $page = isset($_GET['churn_page']) ? intval($_GET['churn_page']) : 1;
                $page_size = 10; // 每页显示10条记录
                
                // 获取总记录数
                $total_churn = getChurnCustomers($conn, $current_end_date, $churn_threshold, true, 1, 10, $query_employee_filter);
                
                // 计算总页数
                $total_pages = ceil($total_churn / $page_size);
                
                // 确保页码合法
                if ($page < 1) $page = 1;
                if ($page > $total_pages && $total_pages > 0) $page = $total_pages;
                
                // 获取当页数据
                $churn_customers = getChurnCustomers($conn, $current_end_date, $churn_threshold, false, $page, $page_size, $query_employee_filter);
                
                while ($customer = $churn_customers->fetch_assoc()) {
                    $inactive_days = $customer['inactive_days'];
                    $inactive_class = $inactive_days > 365 ? 'text-danger' : 'text-warning';
                    
                    echo "<tr>";
                    echo "<td title='{$customer['cs_code']}'>" . htmlspecialcharsFix($customer['cs_code']) . "</td>";
                    echo "<td>" . ($customer['last_order_date'] ? $customer['last_order_date'] : '从未下单') . "</td>";
                    echo "<td class='{$inactive_class}'>" . $inactive_days . "</td>";
                    echo "<td>" . $customer['order_count'] . "</td>";
                    echo "<td>¥" . number_format($customer['total_amount'], 2) . "</td>";
                    echo "<td>" . htmlspecialcharsFix($customer['em_user']) . "</td>";
                    echo "<td><a href='customer_detail.php?id=" . $customer['id'] . "&from_warning=1' class='action-btn action-btn-view'>查看</a></td>";
                    echo "</tr>";
                }
                
                if ($churn_customers->num_rows == 0) {
                    echo "<tr><td colspan='7' class='text-center'>没有发现流失客户</td></tr>";
                }
                ?>
            </tbody>
        </table>
        
        <!-- 分页控件 -->
        <?php if ($total_pages > 1): ?>
        <div class="pagination-container">
            <ul class="pagination">
                <?php
                // 生成分页链接的基础URL
                $base_url = '?';
                foreach ($_GET as $key => $value) {
                    if ($key != 'churn_page') {
                        $base_url .= $key . '=' . urlencode($value) . '&';
                    }
                }
                
                // 上一页链接
                if ($page > 1) {
                    echo "<li class='pager-item'><a class='pager-link' href='{$base_url}churn_page=" . ($page - 1) . "#churn-customers'>上一页</a></li>";
                } else {
                    echo "<li class='pager-item disabled'><a class='pager-link' href='#churn-customers'>上一页</a></li>";
                }
                
                // 页码链接
                $start_page = max(1, $page - 2);
                $end_page = min($total_pages, $page + 2);
                
                if ($start_page > 1) {
                    echo "<li class='pager-item'><a class='pager-link' href='{$base_url}churn_page=1#churn-customers'>1</a></li>";
                    if ($start_page > 2) {
                        echo "<li class='pager-item disabled'><a class='pager-link' href='#churn-customers'>...</a></li>";
                    }
                }
                
                for ($i = $start_page; $i <= $end_page; $i++) {
                    if ($i == $page) {
                        echo "<li class='pager-item active'><a class='pager-link' href='#churn-customers'>{$i}</a></li>";
                    } else {
                        echo "<li class='pager-item'><a class='pager-link' href='{$base_url}churn_page={$i}#churn-customers'>{$i}</a></li>";
                    }
                }
                
                if ($end_page < $total_pages) {
                    if ($end_page < $total_pages - 1) {
                        echo "<li class='pager-item disabled'><a class='pager-link' href='#churn-customers'>...</a></li>";
                    }
                    echo "<li class='pager-item'><a class='pager-link' href='{$base_url}churn_page={$total_pages}#churn-customers'>{$total_pages}</a></li>";
                }
                
                // 下一页链接
                if ($page < $total_pages) {
                    echo "<li class='pager-item'><a class='pager-link' href='{$base_url}churn_page=" . ($page + 1) . "#churn-customers'>下一页</a></li>";
                } else {
                    echo "<li class='pager-item disabled'><a class='pager-link' href='#churn-customers'>下一页</a></li>";
                }
                ?>
            </ul>
            <div class="pagination-info">
                共 <?php echo $total_churn; ?> 条记录，当前显示第 <?php echo $page; ?> 页，共 <?php echo $total_pages; ?> 页
            </div>
        </div>
        <?php endif; ?>
    </div>
    
    <!-- 总体订单趋势图 -->
    <div class="chart-section">
        <div class="section-header">
            <h2>总体订单趋势</h2>
            <p>最近12个月的订单数量和金额趋势</p>
        </div>
        
        <div class="order-trend-chart-container">
            <canvas id="orderTrendChart"></canvas>
        </div>
        
        <style>
            /* 响应式布局：调整图表高度 */
            .order-trend-chart-container {
                width: 100%;
                height: auto;
            }
            
            @media (min-width: 768px) {
                .order-trend-chart-container {
                    height: 33vh; /* 视窗高度的1/3 */
                }
            }
        </style>
        
        <?php
        // 获取最近12个月的订单趋势数据
        $sql_trend = "SELECT 
            DATE_FORMAT(order_date, '%Y-%m') as month,
            COUNT(*) as order_count,
            SUM(total_amount) as total_amount
        FROM orders o
        JOIN customer c ON o.customer_id = c.id
        WHERE o.is_deleted = 0 AND order_date >= DATE_SUB(?, INTERVAL 11 MONTH)";
        
        // 添加业务员筛选条件
        if (is_array($query_employee_filter) && !empty($query_employee_filter)) {
            // 如果是组长查看团队数据
            $sql_trend .= " AND c.cs_belong IN (" . $query_employee_filter . ")";
        } else if (!is_array($query_employee_filter) && $query_employee_filter > 0) {
            // 如果是查看单个业务员数据
            $sql_trend .= " AND c.cs_belong = ?";
        }
        
        $sql_trend .= " GROUP BY DATE_FORMAT(order_date, '%Y-%m') ORDER BY month";
        
        $stmt = $conn->prepare($sql_trend);
        
        if (!is_array($query_employee_filter) && $query_employee_filter > 0) {
            $stmt->bind_param("si", $current_end_date, $query_employee_filter);
        } else {
            $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);
        ?>
    </div>


<style>

.page-header {
    margin-bottom: 30px;
}

.page-title {
    font-size: 24px;
    margin-top: 0;
    margin-bottom: 5px;
}

.page-description {
    color: #666;
    margin-bottom: 5px;
}

.role-info {
    color: #2196f3;
    font-size: 14px;
    margin-top: 5px;
    font-weight: 500;
}

.filter-form {
    background-color: #f8f9fa;
    border-radius: 4px;
    padding: 20px;
    margin-bottom: 30px;
    box-shadow: 0 1px 2px rgba(0,0,0,0.05);
}

.filter-form-inline {
    display: flex;
    flex-wrap: wrap;
    gap: 15px;
    align-items: end;
}

.warnings-overview {
    margin-bottom: 30px;
}

.warnings-overview .row {
    display: flex;
    flex-wrap: wrap;
    margin: 0 -10px;
}

.warnings-overview .col-md-3 {
    padding: 0 10px;
    width: 25%;
    flex: 0 0 25%;
    box-sizing: border-box;
}

@media (max-width: 992px) {
    .warnings-overview .col-md-3 {
        width: 50%;
        flex: 0 0 50%;
    }
}

@media (max-width: 576px) {
    .warnings-overview .col-md-3 {
        width: 100%;
        flex: 0 0 100%;
    }
}

.stat-card {
    border-radius: 8px;
    padding: 20px;
    color: #fff;
    height: 100%;
    margin-bottom: 20px;
    box-shadow: 0 3px 6px rgba(0,0,0,0.16);
    text-align: center;
    transition: transform 0.3s ease;
}

.stat-card:hover {
    transform: translateY(-5px);
}

.stat-card h3 {
    margin-top: 0;
    font-size: 16px;
    font-weight: 500;
    margin-bottom: 10px;
}

.stat-value {
    font-size: 32px;
    font-weight: 600;
    margin-bottom: 10px;
}

.stat-desc {
    font-size: 15px;
    opacity: 0.8;
}

.stat-card.warning {
    background-color: #ff9800;
}

.stat-card.danger {
    background-color: #f44336;
}

.stat-card.info {
    background-color: #2196f3;
}

.stat-card.success {
    background-color: #4caf50;
}

.warning-section {
    background-color: white;
    border-radius: 8px;
    padding: 25px;
    margin-bottom: 35px;
    box-shadow: 0 2px 8px rgba(0,0,0,0.08);
}

.section-header {
    margin-bottom: 20px;
}

.section-header h2 {
    font-size: 20px;
    margin-top: 0;
    margin-bottom: 5px;
}

.section-header p {
    color: #666;
    font-size: 15px;
    margin-bottom: 15px;
}

.chart-section {
    background-color: white;
    border-radius: 4px;
    padding: 20px;
    margin-bottom: 30px;
    box-shadow: 0 1px 2px rgba(0,0,0,0.05);
}

.chart-container {
    height: 350px;
}

.text-danger {
    color: #f44336;
}

.text-warning {
    color: #ff9800;
}

.text-info {
    color: #2196f3;
}

/* 调整表格和内容样式 */
.data-table {
    margin-bottom: 20px;
    width: 100%;
    font-size: 14px; /* 增加表格字体大小 */
    border-collapse: separate;
    border-spacing: 0;
    table-layout: fixed; /* 固定表格布局 */
}

.data-table th {
    font-size: 15px; /* 表头字体大小 */
    padding: 12px 15px;
    background-color: #f5f5f5;
    font-weight: 600;
}

.data-table td {
    padding: 12px 15px;
    vertical-align: middle;
    white-space: nowrap; /* 防止文本换行 */
    overflow: hidden; /* 溢出隐藏 */
    text-overflow: ellipsis; /* 文本溢出显示省略号 */
}

/* 为长期不活跃客户表格设置列宽 */
#inactive-customers .data-table th:nth-child(1), /* 客户编码 */
#inactive-customers .data-table td:nth-child(1) {
    width: 12%;
}

#inactive-customers .data-table th:nth-child(2), /* 客户名称 */
#inactive-customers .data-table td:nth-child(2) {
    width: 20%;
}

#inactive-customers .data-table th:nth-child(3), /* 最后出货日期 */
#inactive-customers .data-table td:nth-child(3) {
    width: 12%;
}

#inactive-customers .data-table th:nth-child(4), /* 不活跃天数 */
#inactive-customers .data-table td:nth-child(4) {
    width: 10%;
}

#inactive-customers .data-table th:nth-child(5), /* 历史订单数 */
#inactive-customers .data-table td:nth-child(5) {
    width: 10%;
}

#inactive-customers .data-table th:nth-child(6), /* 历史订单总额 */
#inactive-customers .data-table td:nth-child(6) {
    width: 12%;
}

#inactive-customers .data-table th:nth-child(7), /* 业务员 */
#inactive-customers .data-table td:nth-child(7) {
    width: 12%;
}

#inactive-customers .data-table th:nth-child(8), /* 操作 */
#inactive-customers .data-table td:nth-child(8) {
    width: 12%;
}

.data-table td:last-child {
    min-width: 80px; /* 确保操作列有足够宽度 */
    text-align: center;
}

.data-table tr:hover {
    background-color: #f9f9f9;
}

.action-btn {
    padding: 5px 10px;
    margin: 0 3px;
    min-width: 60px;
    display: inline-block;
    text-align: center;
    border-radius: 4px;
    font-size: 13px;
    transition: all 0.3s ease;
}

.action-btn-view {
    background-color: #2196f3;
    color: white;
    border: none;
}

.action-btn-view:hover {
    background-color: #0d8aee;
    box-shadow: 0 2px 5px rgba(0,0,0,0.2);
}

/* 为所有客户列表表格添加共同样式 */
.warning-section .data-table {
    border: 1px solid #eee;
    border-radius: 4px;
    box-shadow: 0 1px 3px rgba(0,0,0,0.05);
}

.warning-section .data-table th:first-child,
.warning-section .data-table td:first-child {
    padding-left: 20px; /* 左侧留出更多间距 */
}

/* 表格内数字列的对齐方式 */
.warning-section .data-table td:nth-child(3),
.warning-section .data-table td:nth-child(4),
.warning-section .data-table td:nth-child(5) {
    text-align: left;
}

/* 调整整体字体大小 */
body {
    font-size: 14px;
}

/* 分页样式 */
.pagination-container {
    margin-top: 25px;
    display: flex;
    flex-direction: column;
    align-items: center;
}

.pagination {
    display: flex;
    list-style: none;
    padding: 0;
    margin-bottom: 15px;
}

.pagination .pager-item {
    margin: 0 2px;
}

.pagination .pager-link {
    padding: 8px 16px;
    border-radius: 4px;
    margin: 0 3px;
    font-weight: 500;
    border: 1px solid #ddd;
    background-color: #fff;
    color: #2196f3;
    text-decoration: none;
    display: inline-block;
}

.pagination .pager-item.active .pager-link {
    background-color: #2196f3;
    color: white;
    border-color: #2196f3;
}

.pagination .pager-item.disabled .pager-link {
    color: #999;
    cursor: not-allowed;
    background-color: #f5f5f5;
}

.pagination-info {
    margin-top: 10px;
    font-size: 14px;
    color: #666;
}

.form-btn {
    padding: 8px 15px;
    border-radius: 4px;
    cursor: pointer;
    font-size: 14px;
    border: none;
    transition: all 0.3s ease;
}

.form-btn-primary {
    background-color: #2196f3;
    color: white;
}

.form-btn-primary:hover {
    background-color: #0d8aee;
    box-shadow: 0 2px 5px rgba(0,0,0,0.2);
}

/* 添加斑马纹和悬停效果 */
.data-table tr:nth-child(even) {
    background-color: #f8f8f8;
}

.data-table tbody tr:hover {
    background-color: #f0f7ff;
}
</style>

<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');
    }
}

// 订单趋势图
document.addEventListener('DOMContentLoaded', function() {
    const ctx = document.getElementById('orderTrendChart').getContext('2d');
    
    const months = <?php echo $months_json; ?>;
    const orderCounts = <?php echo $order_counts_json; ?>;
    const orderAmounts = <?php echo $order_amounts_json; ?>;
    
    const chart = new Chart(ctx, {
        type: 'line',
        data: {
            labels: months,
            datasets: [
                {
                    label: '订单数量',
                    data: orderCounts,
                    backgroundColor: 'rgba(54, 162, 235, 0.2)',
                    borderColor: 'rgba(54, 162, 235, 1)',
                    borderWidth: 2,
                    yAxisID: 'y-axis-1'
                },
                {
                    label: '订单金额',
                    data: orderAmounts,
                    backgroundColor: 'rgba(255, 99, 132, 0.2)',
                    borderColor: 'rgba(255, 99, 132, 1)',
                    borderWidth: 2,
                    yAxisID: 'y-axis-2'
                }
            ]
        },
        options: {
            responsive: true,
            maintainAspectRatio: false,
            scales: {
                'y-axis-1': {
                    type: 'linear',
                    position: 'left',
                    title: {
                        display: true,
                        text: '订单数量'
                    }
                },
                'y-axis-2': {
                    type: 'linear',
                    position: 'right',
                    title: {
                        display: true,
                        text: '订单金额'
                    },
                    grid: {
                        drawOnChartArea: false
                    }
                }
            }
        }
    });
});
</script>

<?php
/**
 * 获取订单金额下降的客户
 */
function getDecreasingOrderAmountCustomers($conn, $current_start, $current_end, $previous_start, $previous_end, $threshold, $count_only = false, $selected_employee = 0) {
    // 构建业务员筛选条件
    $employee_filter = "";
    if (!empty($selected_employee)) {
        if (is_numeric($selected_employee)) {
            // 单个业务员
            $employee_filter = " AND c.cs_belong = " . intval($selected_employee);
        } else if (strpos($selected_employee, ',') !== false) {
            // 多个业务员（逗号分隔的字符串）
            $employee_filter = " AND c.cs_belong IN (" . $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 is_deleted = 0 AND 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 is_deleted = 0 AND 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 is_deleted = 0 AND customer_id = c.id), '') as last_order_date
    FROM customer c
    LEFT JOIN (
        SELECT customer_id, SUM(total_amount) as amount
        FROM orders
        WHERE is_deleted = 0 AND 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 is_deleted = 0 AND 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 = "";
    if (!empty($selected_employee)) {
        if (is_numeric($selected_employee)) {
            // 单个业务员
            $employee_filter = " AND c.cs_belong = " . intval($selected_employee);
        } else if (strpos($selected_employee, ',') !== false) {
            // 多个业务员（逗号分隔的字符串）
            $employee_filter = " AND c.cs_belong IN (" . $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
            WHERE is_deleted = 0
            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
        WHERE is_deleted = 0
        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
        WHERE is_deleted = 0
        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 = "";
    if (!empty($selected_employee)) {
        if (is_numeric($selected_employee)) {
            // 单个业务员
            $employee_filter = " AND c.cs_belong = " . intval($selected_employee);
        } else if (strpos($selected_employee, ',') !== false) {
            // 多个业务员（逗号分隔的字符串）
            $employee_filter = " AND c.cs_belong IN (" . $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
        WHERE is_deleted = 0
        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
        WHERE is_deleted = 0
        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 = "";
    if (!empty($selected_employee)) {
        if (is_numeric($selected_employee)) {
            // 单个业务员
            $employee_filter = " AND c.cs_belong = " . intval($selected_employee);
        } else if (strpos($selected_employee, ',') !== false) {
            // 多个业务员（逗号分隔的字符串）
            $employee_filter = " AND c.cs_belong IN (" . $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
            WHERE is_deleted = 0
            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
        WHERE is_deleted = 0
        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
        WHERE is_deleted = 0
        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');
?>