mtb_crm_asp/statistics_order_warnings.php

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

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

// 获取日期范围参数
$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;

// 获取所有业务员列表
$sql_employees = "SELECT id, em_user FROM employee ORDER BY em_user";
$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_max_threshold = 90; // 复购周期超过90天触发预警
$repurchase_cycle_min_threshold = 0.5; // 复购周期小于正常值的50%触发预警（异常频繁购买）
$inactive_threshold = 60; // 60天未购买视为不活跃客户
$normal_repurchase_days = 30; // 正常复购周期参考值（天）

// 页面头部
include('statistics_header.php');
?>

<div class="container">
    <div class="page-header">
        <h1 class="page-title">订单预警系统</h1>
        <p class="page-description">监控订单异常情况，提前预警潜在问题</p>
    </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 htmlspecialchars($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 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();
            
            // 获取订单金额下降的客户数
            $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);
            ?>
            
            <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">周期异常或不规律</div>
                </div>
            </div>
            
            <div class="col-md-3">
                <div class="stat-card danger">
                    <h3>长期不活跃客户</h3>
                    <div class="stat-value"><?php echo $inactive_customers_count; ?></div>
                    <div class="stat-desc"><?php echo $inactive_threshold; ?>天以上未下单</div>
                </div>
            </div>
            
            <div class="col-md-3">
                <div class="stat-card info">
                    <h3>客户活跃率</h3>
                    <div class="stat-value">
                        <?php 
                        $active_rate = ($warning_count['total_customers'] > 0) ? 
                            round(($warning_count['active_customers'] / $warning_count['total_customers']) * 100, 1) : 0;
                        echo $active_rate . '%'; 
                        ?>
                    </div>
                    <div class="stat-desc">选定周期内下单客户占比</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>
                    <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,
                    $selected_employee
                );
                
                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>" . htmlspecialchars($customer['cs_company']) . "</td>";
                    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>" . htmlspecialchars($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='7' class='text-center'>没有发现订单金额下降的客户</td></tr>";
                }
                ?>
            </tbody>
        </table>
    </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>
                    <th>业务员</th>
                    <th>操作</th>
                </tr>
            </thead>
            <tbody>
                <?php
                $abnormal_customers = getAbnormalRepurchaseCycleCustomers(
                    $conn, 
                    $current_start_date, 
                    $current_end_date, 
                    $repurchase_cycle_max_threshold, 
                    $repurchase_cycle_min_threshold,
                    $normal_repurchase_days,
                    false,
                    $selected_employee
                );
                
                while ($customer = $abnormal_customers->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 "<tr>";
                    echo "<td>" . htmlspecialchars($customer['cs_company']) . "</td>";
                    echo "<td>" . round($customer['avg_cycle'], 1) . "</td>";
                    echo "<td>" . round($customer['recent_cycle'], 1) . "</td>";
                    echo "<td class='{$deviation_class}'>" . $deviation_text . "</td>";
                    echo "<td>" . $customer['last_order_date'] . "</td>";
                    echo "<td>" . $customer['order_count'] . "</td>";
                    echo "<td>" . htmlspecialchars($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='8' class='text-center'>没有发现复购周期异常的客户</td></tr>";
                }
                ?>
            </tbody>
        </table>
    </div>
    
    <!-- 长期不活跃客户列表 -->
    <div class="warning-section" id="inactive-customers">
        <div class="section-header">
            <h2>长期不活跃客户</h2>
            <p>超过<?php echo $inactive_threshold; ?>天未下单的客户</p>
        </div>
        
        <table class="data-table">
            <thead>
                <tr>
                    <th>客户编码</th>
                    <th>客户名称</th>
                    <th>最后订单日期</th>
                    <th>不活跃天数</th>
                    <th>历史订单数</th>
                    <th>历史订单总额</th>
                    <th>业务员</th>
                    <th>操作</th>
                </tr>
            </thead>
            <tbody>
                <?php
                // 获取分页参数
                $page = isset($_GET['inactive_page']) ? intval($_GET['inactive_page']) : 1;
                $page_size = 10; // 每页显示10条记录
                
                // 获取总记录数
                $total_inactive = getInactiveCustomers($conn, $current_end_date, $inactive_threshold, true, 1, 10, $selected_employee);
                
                // 计算总页数
                $total_pages = ceil($total_inactive / $page_size);
                
                // 确保页码合法
                if ($page < 1) $page = 1;
                if ($page > $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 "<tr>";
                    echo "<td title='{$customer['cs_code']}'>" . htmlspecialchars($customer['cs_code']) . "</td>";
                    echo "<td>" . htmlspecialchars($customer['cs_company'] ?: '未填写') . "</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>" . htmlspecialchars($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 ($inactive_customers->num_rows == 0) {
                    echo "<tr><td colspan='8' 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 != 'inactive_page') {
                        $base_url .= $key . '=' . urlencode($value) . '&';
                    }
                }
                
                // 上一页链接
                if ($page > 1) {
                    echo "<li class='pager-item'><a class='pager-link' href='{$base_url}inactive_page=" . ($page - 1) . "#inactive-customers'>上一页</a></li>";
                } else {
                    echo "<li class='pager-item disabled'><a class='pager-link' href='#inactive-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}inactive_page=1#inactive-customers'>1</a></li>";
                    if ($start_page > 2) {
                        echo "<li class='pager-item disabled'><a class='pager-link' href='#inactive-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='#inactive-customers'>{$i}</a></li>";
                    } else {
                        echo "<li class='pager-item'><a class='pager-link' href='{$base_url}inactive_page={$i}#inactive-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='#inactive-customers'>...</a></li>";
                    }
                    echo "<li class='pager-item'><a class='pager-link' href='{$base_url}inactive_page={$total_pages}#inactive-customers'>{$total_pages}</a></li>";
                }
                
                // 下一页链接
                if ($page < $total_pages) {
                    echo "<li class='pager-item'><a class='pager-link' href='{$base_url}inactive_page=" . ($page + 1) . "#inactive-customers'>下一页</a></li>";
                } else {
                    echo "<li class='pager-item disabled'><a class='pager-link' href='#inactive-customers'>下一页</a></li>";
                }
                ?>
            </ul>
            <div class="pagination-info">
                共 <?php echo $total_inactive; ?> 条记录，当前显示第 <?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="chart-container">
            <canvas id="orderTrendChart"></canvas>
        </div>
        
        <?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
        WHERE order_date >= 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);
        ?>
    </div>
</div>

<style>
.container {
    padding: 20px;
    max-width: 1280px; /* 稍微扩大容器宽度 */
    margin: 0 auto;
}

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

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

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

.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 = $selected_employee > 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');
?> ?>