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 结束日期
 * @param array|int $employee_filter 业务员ID或ID数组，用于过滤数据 (可选)
 * @return array 新增客户详细数据
 */
function getNewCustomersDetails($conn, $start_date, $end_date, $employee_filter = null) {
    // 使用 mysqli_real_escape_string 防止 SQL 注入
    $start_date = $conn->real_escape_string($start_date);
    $end_date = $conn->real_escape_string($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 '$start_date' AND '$end_date'";
    
    // 如果有业务员过滤条件
    if ($employee_filter !== null) {
        if (is_array($employee_filter) && !empty($employee_filter)) {
            // 处理数组形式的业务员ID列表
            $emp_ids = array();
            foreach ($employee_filter as $emp_id) {
                if (is_numeric($emp_id)) {
                    $emp_ids[] = intval($emp_id);
                }
            }
            if (!empty($emp_ids)) {
                $emp_ids_str = implode(',', $emp_ids);
                $sql .= " AND c.cs_belong IN ($emp_ids_str)";
            }
        } else if (is_numeric($employee_filter) && $employee_filter > 0) {
            // 处理单个业务员ID
            $employee_filter = intval($employee_filter);
            $sql .= " AND c.cs_belong = $employee_filter";
        }
    }
    
    $sql .= " ORDER BY c.cs_addtime DESC LIMIT 30";
    
    $result = $conn->query($sql);
    $customers = [];
    
    if ($result) {
        while ($row = $result->fetch_assoc()) {
            $customers[] = $row;
        }
    }
    
    return $customers;
}

/**
 * 获取各业务员新增客户统计
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @param array $visible_employees 可见的业务员ID列表 (可选)
 * @return array 业务员新增客户统计数据
 */
function getNewCustomersByEmployee($conn, $start_date, $end_date, $visible_employees = null) {
    // 使用 mysqli_real_escape_string 防止 SQL 注入
    $start_date = $conn->real_escape_string($start_date);
    $end_date = $conn->real_escape_string($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 '$start_date' AND '$end_date'
            WHERE e.em_role IS NOT NULL";
    
    // 如果指定了可见业务员列表，则添加过滤条件
    if ($visible_employees !== null && !empty($visible_employees)) {
        $emp_ids = array();
        foreach ($visible_employees as $emp_id) {
            if (is_numeric($emp_id)) {
                $emp_ids[] = intval($emp_id);
            }
        }
        if (!empty($emp_ids)) {
            $emp_ids_str = implode(',', $emp_ids);
            $sql .= " AND e.id IN ($emp_ids_str)";
        }
    }
    
    $sql .= " GROUP BY e.id ORDER BY customer_count DESC";
    
    $result = $conn->query($sql);
    $data = [];
    
    if ($result) {
        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
}

/**
 * 根据用户角色获取可见的业务员列表
 * 
 * @param mysqli $conn 数据库连接
 * @param int $user_id 当前用户ID
 * @return array 可访问的业务员ID和姓名列表
 */
function getAccessibleEmployees($conn, $user_id) {
    // 获取当前用户信息
    $sql = "SELECT em_user, em_role FROM employee WHERE id = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("i", $user_id);
    $stmt->execute();
    $result = $stmt->get_result();
    $user = $result->fetch_assoc();
    $role = $user['em_role'] ?? 0;
    
    $employees = [];
    
    if ($role == 1) {
        // 管理员可以看到所有业务员
        $sql = "SELECT id, em_user FROM employee WHERE em_role IS NOT NULL ORDER BY em_user";
        $result = $conn->query($sql);
        while ($row = $result->fetch_assoc()) {
            $employees[] = [
                'id' => $row['id'],
                'name' => $row['em_user']
            ];
        }
    } else if ($role == 2) {
        // 获取组长自己和其团队成员
        $sql = "SELECT id, em_user FROM employee WHERE id = ? OR em_role = ? ORDER BY em_user";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("ii", $user_id, $user_id);
        $stmt->execute();
        $result = $stmt->get_result();
        while ($row = $result->fetch_assoc()) {
            $employees[] = [
                'id' => $row['id'],
                'name' => $row['em_user']
            ];
        }
    } else {
        // 普通业务员只能看到自己
        $sql = "SELECT id, em_user FROM employee WHERE id = ?";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("i", $user_id);
        $stmt->execute();
        $result = $stmt->get_result();
        while ($row = $result->fetch_assoc()) {
            $employees[] = [
                'id' => $row['id'],
                'name' => $row['em_user']
            ];
        }
    }
    
    return $employees;
}

/**
 * 根据用户角色获取默认的业务员筛选列表
 * 
 * @param mysqli $conn 数据库连接
 * @param int $user_id 当前用户ID
 * @return array|int|null 业务员筛选值，可能是单个ID、ID数组或null
 */
function getDefaultEmployeeFilter($conn, $user_id) {
    // 获取当前用户角色
    $sql = "SELECT em_role FROM employee WHERE id = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("i", $user_id);
    $stmt->execute();
    $result = $stmt->get_result();
    $user = $result->fetch_assoc();
    $role = $user['em_role'] ?? 0;
    
    if ($role == 1) {
        // 管理员默认看所有人
        return null;
    } else if ($role == 2) {
        // 团队组长默认看他的团队
        $sql = "SELECT id FROM employee WHERE id = ? OR em_role = ?";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("ii", $user_id, $user_id);
        $stmt->execute();
        $result = $stmt->get_result();
        $emp_ids = [];
        while ($row = $result->fetch_assoc()) {
            $emp_ids[] = $row['id'];
        }
        return $emp_ids;
    } else {
        // 普通业务员只看自己
        return $user_id;
    }
}

/**
 * 渲染新客户产品购买明细
 * 
 * @param array $product_data 产品购买数据
 * @return void
 */
function renderNewCustomerProductPurchases($product_data) {
    if (empty($product_data) || empty($product_data['products'])) {
        echo '<div class="alert alert-info">该时间段内没有新客户购买产品数据</div>';
        return;
    }
    
    $products = $product_data['products'];
    $new_customer_count = $product_data['new_customer_count'];
    ?>
    
    <div class="section-intro">
        <p>本期间共有 <strong><?php echo number_format($new_customer_count); ?></strong> 名新客户进行了购买。以下是他们购买的产品明细：</p>
    </div>
    
    <div class="table-responsive">
        <table class="data-table">
            <thead>
                <tr>
                    <th>产品名称</th>
                    <th>产品分类</th>
                    <th>订单数</th>
                    <th>购买客户数</th>
                    <th>销售数量</th>
                    <th>销售金额</th>
                    <th>平均单价</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($products as $product): ?>
                <tr>
                    <td><?php echo htmlspecialchars($product['product_name']); ?></td>
                    <td><?php echo htmlspecialchars($product['category_name'] ?: '未分类'); ?></td>
                    <td><?php echo number_format($product['order_count']); ?></td>
                    <td><?php echo number_format($product['customer_count']); ?></td>
                    <td><?php echo number_format($product['total_quantity']); ?></td>
                    <td><?php echo formatCurrency($product['total_revenue']); ?></td>
                    <td><?php echo formatCurrency($product['avg_price']); ?></td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>
    
    <!-- 新客户产品购买分布图 -->
    <div class="chart-container">
        <div>
            <canvas id="newCustomerProductChart"></canvas>
        </div>
    </div>
    
    <script>
    <?php
    // 准备图表数据
    $product_names = [];
    $product_quantities = [];
    $product_revenues = [];
    
    // 只取前10个产品用于图表显示
    $top_products = array_slice($products, 0, 10);
    
    foreach ($top_products as $product) {
        $product_names[] = $product['product_name'];
        $product_quantities[] = $product['total_quantity'];
        $product_revenues[] = $product['total_revenue'];
    }
    
    // 生成图表背景色
    $colors = generateChartColors(count($top_products));
    $backgroundColors = [];
    
    foreach ($colors as $color) {
        $backgroundColors[] = $color[0];
    }
    ?>
    
    var newCustomerProductCtx = document.getElementById('newCustomerProductChart').getContext('2d');
    new Chart(newCustomerProductCtx, {
        type: 'bar',
        data: {
            labels: <?php echo json_encode($product_names); ?>,
            datasets: [
                {
                    label: '销售数量',
                    data: <?php echo json_encode($product_quantities); ?>,
                    backgroundColor: 'rgba(54, 162, 235, 0.7)',
                    borderColor: 'rgba(54, 162, 235, 1)',
                    borderWidth: 1,
                    yAxisID: 'y-quantity'
                },
                {
                    label: '销售金额',
                    data: <?php echo json_encode($product_revenues); ?>,
                    backgroundColor: 'rgba(255, 99, 132, 0.7)',
                    borderColor: 'rgba(255, 99, 132, 1)',
                    borderWidth: 1,
                    yAxisID: 'y-revenue',
                    type: 'line',
                    fill: false
                }
            ]
        },
        options: {
            responsive: true,
            scales: {
                'y-quantity': {
                    type: 'linear',
                    position: 'left',
                    title: {
                        display: true,
                        text: '销售数量'
                    },
                    beginAtZero: true
                },
                'y-revenue': {
                    type: 'linear',
                    position: 'right',
                    title: {
                        display: true,
                        text: '销售金额'
                    },
                    beginAtZero: true,
                    grid: {
                        drawOnChartArea: false
                    }
                }
            },
            plugins: {
                title: {
                    display: true,
                    text: '新客户热门购买产品 (Top 10)'
                }
            }
        }
    });
    </script>
    
    <!-- 新客户产品类别分布图 -->
    <div class="chart-container">
        <div>
            <canvas id="newCustomerCategoryChart"></canvas>
        </div>
    </div>
    
    <script>
    <?php
    // 按产品类别分组
    $categories = [];
    $category_data = [];
    
    foreach ($products as $product) {
        $category = $product['category_name'] ?: '未分类';
        if (!isset($category_data[$category])) {
            $category_data[$category] = [
                'quantity' => 0,
                'revenue' => 0
            ];
        }
        $category_data[$category]['quantity'] += $product['total_quantity'];
        $category_data[$category]['revenue'] += $product['total_revenue'];
    }
    
    $category_names = array_keys($category_data);
    $category_quantities = array_column($category_data, 'quantity');
    $category_revenues = array_column($category_data, 'revenue');
    
    // 计算占比
    $total_revenue = array_sum($category_revenues);
    $revenue_percentages = [];
    
    foreach ($category_revenues as $revenue) {
        $revenue_percentages[] = round(($revenue / $total_revenue) * 100, 1);
    }
    
    // 生成图表背景色
    $category_colors = generateChartColors(count($category_data), false);
    $category_bg_colors = array_column($category_colors, 0);
    ?>
    
    var newCustomerCategoryCtx = document.getElementById('newCustomerCategoryChart').getContext('2d');
    new Chart(newCustomerCategoryCtx, {
        type: 'pie',
        data: {
            labels: <?php echo json_encode($category_names); ?>,
            datasets: [{
                data: <?php echo json_encode($revenue_percentages); ?>,
                backgroundColor: <?php echo json_encode($category_bg_colors); ?>,
                borderWidth: 1
            }]
        },
        options: {
            responsive: true,
            plugins: {
                title: {
                    display: true,
                    text: '新客户产品类别销售占比'
                },
                tooltip: {
                    callbacks: {
                        label: function(context) {
                            return context.label + ': ' + context.raw + '%';
                        }
                    }
                }
            }
        }
    });
    </script>
    <?php
}