mtb_crm_asp/statistics_utils.php

<?php
/**
 * 统计分析工具函数
 * 
 * 包含所有统计模块共用的工具函数和日期处理逻辑
 */

// 确保直接访问时需要先登录
require_once 'conn.php';

if (!isset($_SESSION['employee_id'])) {
    checkLogin();
}

//检查是否管理员
checkPermissionDie(1,2);


/**
 * 获取和处理日期范围参数
 * 
 * @return array 包含开始日期、结束日期和其他日期相关参数
 */
function getDateRangeParams() {
    // 计算日期范围
    $current_month_start = date('Y-m-01');
    $current_month_end = date('Y-m-t');
    $last_month_start = date('Y-m-01', strtotime('-1 month'));
    $last_month_end = date('Y-m-t', strtotime('-1 month'));
    $current_year_start = date('Y-01-01');
    $current_year_end = date('Y-12-31');

    // 可选的日期范围筛选
    $date_range = isset($_GET['date_range']) ? $_GET['date_range'] : 'current_month';
    $custom_start = isset($_GET['start_date']) ? $_GET['start_date'] : '';
    $custom_end = isset($_GET['end_date']) ? $_GET['end_date'] : '';
    $period = isset($_GET['period']) ? $_GET['period'] : 'day';

    // 设置日期范围
    if ($date_range == 'custom' && !empty($custom_start) && !empty($custom_end)) {
        $start_date = $custom_start;
        $end_date = $custom_end;
    } else {
        switch ($date_range) {
            case 'last_month':
                $start_date = $last_month_start;
                $end_date = $last_month_end;
                break;
            case 'current_year':
                $start_date = $current_year_start;
                $end_date = $current_year_end;
                break;
            case 'last_30_days':
                $start_date = date('Y-m-d', strtotime('-30 days'));
                $end_date = date('Y-m-d');
                break;
            case 'last_90_days':
                $start_date = date('Y-m-d', strtotime('-90 days'));
                $end_date = date('Y-m-d');
                break;
            case 'current_month':
            default:
                $start_date = $current_month_start;
                $end_date = $current_month_end;
                break;
        }
    }

    // 格式化日期用于SQL查询
    $start_date_sql = date('Y-m-d', strtotime($start_date));
    $end_date_sql = date('Y-m-d', strtotime($end_date)) . ' 23:59:59';

    return [
        'date_range' => $date_range,
        'custom_start' => $custom_start,
        'custom_end' => $custom_end,
        'period' => $period,
        'start_date' => $start_date,
        'end_date' => $end_date,
        'start_date_sql' => $start_date_sql,
        'end_date_sql' => $end_date_sql
    ];
}

/**
 * 生成图表颜色数组
 * 
 * @param int $count 需要的颜色数量
 * @param bool $transparent 是否透明
 * @return array 背景色和边框色数组
 */
function generateChartColors($count = 10, $transparent = true) {
    $colors = [
        ['rgba(255, 99, 132, ' . ($transparent ? '0.7' : '1') . ')', 'rgba(255, 99, 132, 1)'],
        ['rgba(54, 162, 235, ' . ($transparent ? '0.7' : '1') . ')', 'rgba(54, 162, 235, 1)'],
        ['rgba(255, 206, 86, ' . ($transparent ? '0.7' : '1') . ')', 'rgba(255, 206, 86, 1)'],
        ['rgba(75, 192, 192, ' . ($transparent ? '0.7' : '1') . ')', 'rgba(75, 192, 192, 1)'],
        ['rgba(153, 102, 255, ' . ($transparent ? '0.7' : '1') . ')', 'rgba(153, 102, 255, 1)'],
        ['rgba(255, 159, 64, ' . ($transparent ? '0.7' : '1') . ')', 'rgba(255, 159, 64, 1)'],
        ['rgba(199, 199, 199, ' . ($transparent ? '0.7' : '1') . ')', 'rgba(199, 199, 199, 1)'],
        ['rgba(83, 102, 255, ' . ($transparent ? '0.7' : '1') . ')', 'rgba(83, 102, 255, 1)'],
        ['rgba(40, 159, 64, ' . ($transparent ? '0.7' : '1') . ')', 'rgba(40, 159, 64, 1)'],
        ['rgba(210, 199, 199, ' . ($transparent ? '0.7' : '1') . ')', 'rgba(210, 199, 199, 1)']
    ];
    
    $result = [];
    
    // 确保有足够的颜色
    while (count($result) < $count) {
        foreach ($colors as $color) {
            $result[] = $color;
            if (count($result) >= $count) {
                break;
            }
        }
    }
    
    return array_slice($result, 0, $count);
}

/**
 * 格式化数值，处理空值和小数位数
 * 
 * @param mixed $value 要格式化的值
 * @param int $decimals 小数位数
 * @return string 格式化后的数值
 */
function formatNumber($value, $decimals = 2) {
    if ($value === null || $value === '') {
        return '0';
    }
    return number_format((float)$value, $decimals);
}

/**
 * 获取时间粒度对应的MySQL DATE_FORMAT格式
 * 
 * @param string $period 时间粒度 (day/week/month)
 * @return string MySQL DATE_FORMAT格式字符串
 */
function getPeriodFormat($period) {
    switch ($period) {
        case 'week':
            return '%x-W%v'; // ISO year and week number
        case 'month':
            return '%Y-%m';
        case 'day':
        default:
            return '%Y-%m-%d';
    }
}

/**
 * 获取新增客户详细信息
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @return array 新增客户详细数据
 */
function getNewCustomersDetails($conn, $start_date, $end_date) {
    $sql = "SELECT 
                c.id,
                c.cs_company as company_name,
                c.cs_code as customer_code,
                co.countryName as country,
                ct.businessType as customer_type,
                e.em_user as employee_name,
                c.cs_addtime as add_date
            FROM customer c
            LEFT JOIN country co ON c.cs_country = co.id
            LEFT JOIN clienttype ct ON c.cs_type = ct.id
            LEFT JOIN employee e ON c.cs_belong = e.id
            WHERE c.cs_addtime BETWEEN ? AND ?
            ORDER BY c.cs_addtime DESC
            LIMIT 30";
            
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ss", $start_date, $end_date);
    $stmt->execute();
    
    $result = $stmt->get_result();
    $customers = [];
    
    while ($row = $result->fetch_assoc()) {
        $customers[] = $row;
    }
    
    return $customers;
}

/**
 * 获取各业务员新增客户统计
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @return array 业务员新增客户统计数据
 */
function getNewCustomersByEmployee($conn, $start_date, $end_date) {
    $sql = "SELECT 
                e.id as employee_id,
                e.em_user as employee_name,
                COUNT(c.id) as customer_count
            FROM employee e
            LEFT JOIN customer c ON e.id = c.cs_belong AND c.cs_addtime BETWEEN ? AND ?
            WHERE e.em_role IS NOT NULL
            GROUP BY e.id
            ORDER BY customer_count DESC";
            
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ss", $start_date, $end_date);
    $stmt->execute();
    
    $result = $stmt->get_result();
    $data = [];
    
    while ($row = $result->fetch_assoc()) {
        $data[] = $row;
    }
    
    return $data;
}

/**
 * 渲染新增客户图表
 * 
 * @param array $customers 新增客户数据
 * @return void
 */
function renderNewCustomersChart($customers) {
    if (empty($customers)) {
        echo '<div class="alert alert-info">该时间段内没有新增客户数据</div>';
        return;
    }
    ?>
    <div class="chart-container">
        <div class="table-responsive">
            <table class="data-table">
                <thead>
                    <tr>
                        <th>客户名称</th>
                        <th>客户编码</th>
                        <th>国家/地区</th>
                        <th>客户类型</th>
                        <th>负责业务员</th>
                        <th>添加日期</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($customers as $customer): ?>
                    <tr>
                        <td><?php echo htmlspecialchars($customer['company_name']); ?></td>
                        <td><?php echo htmlspecialchars($customer['customer_code']); ?></td>
                        <td><?php echo htmlspecialchars($customer['country']); ?></td>
                        <td><?php echo htmlspecialchars($customer['customer_type'] ?: '未分类'); ?></td>
                        <td><?php echo htmlspecialchars($customer['employee_name']); ?></td>
                        <td><?php echo date('Y-m-d', strtotime($customer['add_date'])); ?></td>
                    </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>
    
    <!-- 新增客户添加时间分布图 -->
    <div class="chart-container">
        <div>
            <canvas id="newCustomersChart"></canvas>
        </div>
    </div>
    
    <script>
    // 准备图表数据
    <?php
    // 按日期分组客户数量
    $dates = [];
    $counts = [];
    $dateGroups = [];
    
    foreach ($customers as $customer) {
        $date = date('Y-m-d', strtotime($customer['add_date']));
        if (!isset($dateGroups[$date])) {
            $dateGroups[$date] = 0;
        }
        $dateGroups[$date]++;
    }
    
    // 排序日期
    ksort($dateGroups);
    
    foreach ($dateGroups as $date => $count) {
        $dates[] = $date;
        $counts[] = $count;
    }
    ?>
    
    var newCustomersCtx = document.getElementById('newCustomersChart').getContext('2d');
    new Chart(newCustomersCtx, {
        type: 'bar',
        data: {
            labels: <?php echo json_encode($dates); ?>,
            datasets: [{
                label: '新增客户数量',
                data: <?php echo json_encode($counts); ?>,
                backgroundColor: 'rgba(54, 162, 235, 0.5)',
                borderColor: 'rgba(54, 162, 235, 1)',
                borderWidth: 1
            }]
        },
        options: {
            responsive: true,
            scales: {
                y: {
                    beginAtZero: true,
                    title: {
                        display: true,
                        text: '客户数量'
                    }
                },
                x: {
                    title: {
                        display: true,
                        text: '日期'
                    }
                }
            },
            plugins: {
                title: {
                    display: true,
                    text: '新增客户时间分布'
                }
            }
        }
    });
    </script>
    <?php
}

/**
 * 渲染业务员新增客户图表
 * 
 * @param array $employee_data 业务员新增客户数据
 * @return void
 */
function renderNewCustomersByEmployeeChart($employee_data) {
    if (empty($employee_data)) {
        echo '<div class="alert alert-info">该时间段内没有业务员新增客户数据</div>';
        return;
    }
    
    // 准备图表数据
    $employee_names = [];
    $customer_counts = [];
    
    foreach ($employee_data as $row) {
        $employee_names[] = $row['employee_name'];
        $customer_counts[] = $row['customer_count'];
    }
    
    // 生成图表背景色
    $colors = generateChartColors(count($employee_data));
    $backgroundColors = [];
    $borderColors = [];
    
    foreach ($colors as $color) {
        $backgroundColors[] = $color[0];
        $borderColors[] = $color[1];
    }
    ?>
    
    <div class="analysis-grid">
        <div>
            <canvas id="employeeNewCustomersChart"></canvas>
        </div>
        <div class="table-responsive">
            <table class="data-table">
                <thead>
                    <tr>
                        <th>业务员</th>
                        <th>新增客户数量</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($employee_data as $row): ?>
                    <tr>
                        <td><?php echo htmlspecialchars($row['employee_name']); ?></td>
                        <td><?php echo number_format($row['customer_count']); ?></td>
                    </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>
    
    <script>
    var employeeNewCustomersCtx = document.getElementById('employeeNewCustomersChart').getContext('2d');
    new Chart(employeeNewCustomersCtx, {
        type: 'bar',
        data: {
            labels: <?php echo json_encode($employee_names); ?>,
            datasets: [{
                label: '新增客户数量',
                data: <?php echo json_encode($customer_counts); ?>,
                backgroundColor: <?php echo json_encode($backgroundColors); ?>,
                borderColor: <?php echo json_encode($borderColors); ?>,
                borderWidth: 1
            }]
        },
        options: {
            responsive: true,
            scales: {
                y: {
                    beginAtZero: true,
                    title: {
                        display: true,
                        text: '客户数量'
                    }
                }
            },
            plugins: {
                title: {
                    display: true,
                    text: '业务员新增客户统计'
                }
            }
        }
    });
    </script>
    <?php
}