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
}

/**
 * 获取新客户购买产品明细
 * 
 * @param mysqli $conn 数据库连接
 * @param string $start_date 开始日期
 * @param string $end_date 结束日期
 * @param int $category_id 产品分类ID，0表示所有分类
 * @return array 新客户购买产品数据
 */
function getNewCustomerProductPurchases($conn, $start_date, $end_date, $category_id = 0) {
    // 获取在指定日期范围内首次购买的客户
    $new_customer_sql = "
        SELECT DISTINCT o.customer_id
        FROM orders o
        WHERE o.order_date BETWEEN ? AND ?
        AND o.order_status != 0
        AND NOT EXISTS (
            SELECT 1 FROM orders o2 
            WHERE o2.customer_id = o.customer_id 
            AND o2.order_date < ?
            AND o2.order_status != 0
        )
    ";
    
    $stmt = $conn->prepare($new_customer_sql);
    $stmt->bind_param("sss", $start_date, $end_date, $start_date);
    $stmt->execute();
    $new_customers_result = $stmt->get_result();
    
    $new_customer_ids = [];
    while ($row = $new_customers_result->fetch_assoc()) {
        $new_customer_ids[] = $row['customer_id'];
    }
    
    // 如果没有新客户，返回空数组
    if (empty($new_customer_ids)) {
        return [];
    }
    
    // 构建查询条件中的客户ID列表
    $customer_ids_str = implode(',', $new_customer_ids);
    
    // 查询这些新客户购买的产品
    $category_filter = "";
    if ($category_id > 0) {
        $category_filter = "AND p.category_id = " . intval($category_id);
    }
    
    $product_sql = "
        SELECT 
            p.id,
            p.ProductName as product_name,
            pc.name as category_name,
            COUNT(DISTINCT o.id) as order_count,
            COUNT(DISTINCT o.customer_id) as customer_count,
            SUM(oi.quantity) as total_quantity,
            SUM(oi.total_price) as total_revenue,
            AVG(oi.unit_price) as avg_price
        FROM orders o
        JOIN order_items oi ON o.id = oi.order_id
        JOIN products p ON oi.product_id = p.id
        LEFT JOIN product_categories pc ON p.category_id = pc.id
        WHERE o.customer_id IN ({$customer_ids_str})
        AND o.order_date BETWEEN ? AND ?
        AND o.order_status != 0
        {$category_filter}
        GROUP BY p.id
        ORDER BY total_revenue DESC
    ";
    
    $stmt = $conn->prepare($product_sql);
    $stmt->bind_param("ss", $start_date, $end_date);
    $stmt->execute();
    
    $result = $stmt->get_result();
    $products = [];
    
    while ($row = $result->fetch_assoc()) {
        $products[] = $row;
    }
    
    return [
        'new_customer_count' => count($new_customer_ids),
        'products' => $products
    ];
}

/**
 * 渲染新客户产品购买明细
 * 
 * @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
}