mtb_crm_asp/export_statistics.php

<?php
require_once 'conn.php';
checkLogin();

// 处理日期范围
$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';

// 计算日期范围
$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');

// 设置日期范围
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';

// 准备导出数据
$data = [];

// 1. 销售概览
$sql = "SELECT 
            COUNT(id) as total_orders,
            SUM(total_amount) as total_revenue,
            AVG(total_amount) as avg_order_value
        FROM orders 
        WHERE order_date BETWEEN ? AND ?";

$stmt = $conn->prepare($sql);
$stmt->bind_param("ss", $start_date_sql, $end_date_sql);
$stmt->execute();
$result = $stmt->get_result();
$overview = $result->fetch_assoc();

$data['overview'] = [
    ['项目', '数值'],
    ['总订单数', $overview['total_orders']],
    ['总收入', $overview['total_revenue']],
    ['平均订单金额', $overview['avg_order_value']]
];

// 2. 地区订单分析
$sql = "SELECT 
            c.countryName, 
            COUNT(o.id) as order_count,
            SUM(o.total_amount) as total_amount,
            SUM(oi.quantity) as total_quantity
        FROM orders o
        JOIN customer cu ON o.customer_id = cu.id
        JOIN country c ON cu.cs_country = c.id
        LEFT JOIN order_items oi ON o.id = oi.order_id
        WHERE o.order_date BETWEEN ? AND ?
        GROUP BY cu.cs_country
        ORDER BY total_quantity DESC";

$stmt = $conn->prepare($sql);
$stmt->bind_param("ss", $start_date_sql, $end_date_sql);
$stmt->execute();
$result = $stmt->get_result();

$data['region_orders'] = [['国家/地区', '订单数量', '总金额', '产品数量']];
while ($row = $result->fetch_assoc()) {
    $data['region_orders'][] = [
        $row['countryName'],
        $row['order_count'],
        $row['total_amount'],
        $row['total_quantity']
    ];
}

// 3. 详细时间段订单趋势
$groupFormat = '%Y-%m-%d';
$intervalUnit = 'DAY';

if ($period == 'week') {
    $groupFormat = '%x-W%v'; // ISO year and week number
    $intervalUnit = 'WEEK';
} else if ($period == 'month') {
    $groupFormat = '%Y-%m';
    $intervalUnit = 'MONTH';
}

$sql = "SELECT 
            DATE_FORMAT(o.order_date, '$groupFormat') as time_period,
            COUNT(o.id) as order_count,
            SUM(oi.quantity) as total_quantity,
            SUM(o.total_amount) as total_amount
        FROM orders o
        LEFT JOIN order_items oi ON o.id = oi.order_id
        WHERE o.order_date BETWEEN ? AND ?
        GROUP BY time_period
        ORDER BY MIN(o.order_date)";

$stmt = $conn->prepare($sql);
$stmt->bind_param("ss", $start_date_sql, $end_date_sql);
$stmt->execute();
$result = $stmt->get_result();

$data['time_trend'] = [['时间段', '订单数量', '产品数量', '销售金额']];
while ($row = $result->fetch_assoc()) {
    $data['time_trend'][] = [
        $row['time_period'],
        $row['order_count'],
        $row['total_quantity'],
        $row['total_amount']
    ];
}

// 4. 热门产品
$sql = "SELECT 
            p.ProductName, 
            SUM(oi.quantity) as total_quantity,
            SUM(oi.total_price) as total_revenue
        FROM order_items oi
        JOIN products p ON oi.product_id = p.id
        JOIN orders o ON oi.order_id = o.id
        WHERE o.order_date BETWEEN ? AND ?
        GROUP BY oi.product_id
        ORDER BY total_revenue DESC
        LIMIT 20";

$stmt = $conn->prepare($sql);
$stmt->bind_param("ss", $start_date_sql, $end_date_sql);
$stmt->execute();
$result = $stmt->get_result();

$data['top_products'] = [['产品名称', '销售数量', '销售收入']];
while ($row = $result->fetch_assoc()) {
    $data['top_products'][] = [
        $row['ProductName'],
        $row['total_quantity'],
        $row['total_revenue']
    ];
}

// 5. 业务员销售业绩
$sql = "SELECT 
            e.em_user as employee_name,
            COUNT(o.id) as order_count,
            SUM(o.total_amount) as total_sales,
            AVG(o.total_amount) as avg_order_value
        FROM orders o
        JOIN employee e ON o.employee_id = e.id
        WHERE o.order_date BETWEEN ? AND ?
        GROUP BY o.employee_id
        ORDER BY total_sales DESC";

$stmt = $conn->prepare($sql);
$stmt->bind_param("ss", $start_date_sql, $end_date_sql);
$stmt->execute();
$result = $stmt->get_result();

$data['employee_performance'] = [['业务员', '订单数量', '销售总额', '平均订单金额']];
while ($row = $result->fetch_assoc()) {
    $data['employee_performance'][] = [
        $row['employee_name'],
        $row['order_count'],
        $row['total_sales'],
        $row['avg_order_value']
    ];
}

// 设置适当的响应头，用于Excel导出
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="CRM统计分析_' . date('Y-m-d') . '.xls"');
header('Cache-Control: max-age=0');
?>
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <style>
        table {
            border-collapse: collapse;
            width: 100%;
            margin-bottom: 20px;
        }
        th, td {
            border: 1px solid #ddd;
            padding: 8px;
            text-align: left;
        }
        th {
            background-color: #f2f2f2;
            font-weight: bold;
        }
        h2 {
            margin-top: 30px;
            margin-bottom: 10px;
        }
    </style>
</head>
<body>
    <h1>CRM统计分析报告</h1>
    <p>日期范围: <?php echo $start_date; ?> 至 <?php echo $end_date; ?></p>
    
    <h2>1. 销售概览</h2>
    <table>
        <?php foreach ($data['overview'] as $row): ?>
        <tr>
            <?php foreach ($row as $cell): ?>
            <td><?php echo $cell; ?></td>
            <?php endforeach; ?>
        </tr>
        <?php endforeach; ?>
    </table>
    
    <h2>2. 地区订单分析</h2>
    <table>
        <?php foreach ($data['region_orders'] as $index => $row): ?>
        <tr>
            <?php foreach ($row as $cell): ?>
            <?php if ($index === 0): ?>
            <th><?php echo $cell; ?></th>
            <?php else: ?>
            <td><?php echo $cell; ?></td>
            <?php endif; ?>
            <?php endforeach; ?>
        </tr>
        <?php endforeach; ?>
    </table>
    
    <h2>3. <?php echo $period == 'day' ? '每日' : ($period == 'week' ? '每周' : '每月'); ?>订单趋势</h2>
    <table>
        <?php foreach ($data['time_trend'] as $index => $row): ?>
        <tr>
            <?php foreach ($row as $cell): ?>
            <?php if ($index === 0): ?>
            <th><?php echo $cell; ?></th>
            <?php else: ?>
            <td><?php echo $cell; ?></td>
            <?php endif; ?>
            <?php endforeach; ?>
        </tr>
        <?php endforeach; ?>
    </table>
    
    <h2>4. 热门产品</h2>
    <table>
        <?php foreach ($data['top_products'] as $index => $row): ?>
        <tr>
            <?php foreach ($row as $cell): ?>
            <?php if ($index === 0): ?>
            <th><?php echo $cell; ?></th>
            <?php else: ?>
            <td><?php echo $cell; ?></td>
            <?php endif; ?>
            <?php endforeach; ?>
        </tr>
        <?php endforeach; ?>
    </table>
    
    <h2>5. 业务员销售业绩</h2>
    <table>
        <?php foreach ($data['employee_performance'] as $index => $row): ?>
        <tr>
            <?php foreach ($row as $cell): ?>
            <?php if ($index === 0): ?>
            <th><?php echo $cell; ?></th>
            <?php else: ?>
            <td><?php echo $cell; ?></td>
            <?php endif; ?>
            <?php endforeach; ?>
        </tr>
        <?php endforeach; ?>
    </table>
</body>
</html>