<?php
/**
 * 独立发票报表页面
 * 功能：多条件查询、数据统计、图表展示、Excel导出、附件查看
 * 完全独立，不依赖layout.php的逻辑
 */
ob_start();
session_start();

// ========== 修复：将use语句移到文件顶部（兼容低版本PHP） ==========
require_once __DIR__ . '/vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

// ========== 1. 数据库配置（和原有系统保持一致） ==========
$host = 'localhost';      // 替换为你的数据库主机
$user = 'invoice_system';           // 替换为你的数据库账号
$pwd = '123456';                // 替换为你的数据库密码
$dbname = 'invoice_system';// 替换为你的数据库名
$conn = mysqli_connect($host, $user, $pwd, $dbname);
if (!$conn) {
    die('数据库连接失败：' . mysqli_connect_error());
}
mysqli_set_charset($conn, 'utf8mb4');

// ========== 2. 登录验证（和原有系统保持一致） ==========
if (empty($_SESSION['user_info'])) {
    header('Location: login.php'); // 替换为你的登录页面路径
    exit;
}

// ========== 3. Excel导出功能（修复语法错误） ==========
if (isset($_GET['export']) && $_GET['export'] == 'excel') {
    // 创建Spreadsheet对象（已在顶部引入类）
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('发票报表_'.date('YmdHis'));

    // 设置表头（保留原有表头）
    $header = ['ID', '发票号码', '开票日期', '金额（元）', '供应商', '客户', '添加时间', '备注'];
    $col = 'A';
    foreach ($header as $h) {
        $sheet->setCellValue($col.'1', $h);
        // 表头样式：加粗+居中
        $sheet->getStyle($col.'1')->getFont()->setBold(true);
        $sheet->getStyle($col.'1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $col++;
    }

    // 构建查询条件（保留原有逻辑）
    $where = buildWhereClause();
    // 查询数据（保留原有逻辑）
    $res = mysqli_query($conn, "SELECT i.*, s.name as supplier_name, c.name as customer_name 
            FROM invoice i 
            LEFT JOIN supplier s ON i.supplier_id = s.id 
            LEFT JOIN customer c ON i.customer_id = c.id 
            $where ORDER BY i.invoice_date DESC");
    
    // 填充数据（保留原有逻辑）
    $rowNum = 2;
    $totalAmount = 0;
    while ($row = mysqli_fetch_assoc($res)) {
        $sheet->setCellValue('A'.$rowNum, $row['id']);
        $sheet->setCellValue('B'.$rowNum, $row['invoice_no']);
        $sheet->setCellValue('C'.$rowNum, $row['invoice_date']);
        $sheet->setCellValue('D'.$rowNum, $row['amount']);
        $sheet->setCellValue('E'.$rowNum, $row['supplier_name']);
        $sheet->setCellValue('F'.$rowNum, $row['customer_name']);
        $sheet->setCellValue('G'.$rowNum, $row['create_time']);
        $sheet->setCellValue('H'.$rowNum, $row['remark'] ?? '');
        
        // 数据居中对齐
        $sheet->getStyle('A'.$rowNum.':H'.$rowNum)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        
        $totalAmount += $row['amount'];
        $rowNum++;
    }

    // 总计行（保留原有逻辑，优化样式）
    $sheet->setCellValue('C'.$rowNum, '总计');
    $sheet->setCellValue('D'.$rowNum, $totalAmount);
    $sheet->getStyle('C'.$rowNum.':D'.$rowNum)->getFont()->setBold(true);
    $sheet->getStyle('C'.$rowNum.':D'.$rowNum)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

    // 自动调整列宽
    foreach (range('A', 'H') as $column) {
        $sheet->getColumnDimension($column)->setAutoSize(true);
    }

    // 导出设置（适配XLSX格式）
    $fileName = '发票报表_'.date('YmdHis').'.xlsx';
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="'.$fileName.'"');
    header('Cache-Control: max-age=0');
    header('Pragma: public');

    // 生成并输出Excel文件
    $writer = new Xlsx($spreadsheet);
    $writer->save('php://output');
    
    // 释放内存
    $spreadsheet->disconnectWorksheets();
    unset($spreadsheet);
    exit;
}

// ========== 4. 构建查询条件函数（保留原有逻辑） ==========
function buildWhereClause() {
    global $conn;
    $conditions = [];
    // 关键词
    if (!empty($_GET['keyword'])) {
        $keyword = mysqli_real_escape_string($conn, trim($_GET['keyword']));
        $conditions[] = "(i.invoice_no LIKE '%$keyword%' OR s.name LIKE '%$keyword%' OR c.name LIKE '%$keyword%')";
    }
    // 时间范围
    if (!empty($_GET['start_date'])) {
        $start = mysqli_real_escape_string($conn, trim($_GET['start_date']));
        $conditions[] = "i.invoice_date >= '$start'";
    }
    if (!empty($_GET['end_date'])) {
        $end = mysqli_real_escape_string($conn, trim($_GET['end_date']));
        $conditions[] = "i.invoice_date <= '$end'";
    }
    // 供应商
    if (intval($_GET['supplier_id']) > 0) {
        $conditions[] = "i.supplier_id = ".intval($_GET['supplier_id']);
    }
    // 客户
    if (intval($_GET['customer_id']) > 0) {
        $conditions[] = "i.customer_id = ".intval($_GET['customer_id']);
    }
    // 金额范围
    if (!empty($_GET['min_amount'])) {
        $conditions[] = "i.amount >= ".floatval($_GET['min_amount']);
    }
    if (!empty($_GET['max_amount'])) {
        $conditions[] = "i.amount <= ".floatval($_GET['max_amount']);
    }
    return empty($conditions) ? '' : "WHERE ".implode(' AND ', $conditions);
}

// ========== 5. 统计数据查询（保留原有逻辑） ==========
$where = buildWhereClause();
// 基础统计
$stat = [
    'total_count' => 0, 'total_amount' => 0, 
    'avg_amount' => 0, 'max_amount' => 0, 'min_amount' => 0
];
$statRes = mysqli_query($conn, "SELECT 
        COUNT(*) as total_count,
        SUM(amount) as total_amount,
        AVG(amount) as avg_amount,
        MAX(amount) as max_amount,
        MIN(amount) as min_amount
        FROM invoice i 
        LEFT JOIN supplier s ON i.supplier_id = s.id 
        LEFT JOIN customer c ON i.customer_id = c.id 
        $where");
if ($statRes) {
    $stat = mysqli_fetch_assoc($statRes);
    $stat = array_map(function($v) { return $v ?: 0; }, $stat);
}

// 按供应商统计（图表用）
$supplierStat = [];
$supRes = mysqli_query($conn, "SELECT s.name, SUM(i.amount) as total 
        FROM invoice i 
        LEFT JOIN supplier s ON i.supplier_id = s.id 
        $where 
        GROUP BY s.id 
        ORDER BY total DESC 
        LIMIT 10");
if ($supRes) {
    while ($row = mysqli_fetch_assoc($supRes)) {
        $supplierStat[] = $row;
    }
}

// ========== 6. 渲染下拉框选项（保留原有逻辑） ==========
// 供应商
$supplierOptions = '<option value="">全部供应商</option>';
$supOptRes = mysqli_query($conn, "SELECT id, name FROM supplier ORDER BY name");
if ($supOptRes) {
    while ($row = mysqli_fetch_assoc($supOptRes)) {
        $selected = (intval($_GET['supplier_id']) == $row['id']) ? 'selected' : '';
        $supplierOptions .= "<option value='{$row['id']}' $selected>{$row['name']}</option>";
    }
}

// 客户
$customerOptions = '<option value="">全部客户</option>';
$cusOptRes = mysqli_query($conn, "SELECT id, name FROM customer ORDER BY name");
if ($cusOptRes) {
    while ($row = mysqli_fetch_assoc($cusOptRes)) {
        $selected = (intval($_GET['customer_id']) == $row['id']) ? 'selected' : '';
        $customerOptions .= "<option value='{$row['id']}' $selected>{$row['name']}</option>";
    }
}

// ========== 7. 渲染报表列表（保留原有逻辑） ==========
$reportList = '';
$listRes = mysqli_query($conn, "SELECT i.*, s.name as supplier_name, c.name as customer_name 
        FROM invoice i 
        LEFT JOIN supplier s ON i.supplier_id = s.id 
        LEFT JOIN customer c ON i.customer_id = c.id 
        $where ORDER BY i.invoice_date DESC");
if ($listRes && mysqli_num_rows($listRes) > 0) {
    while ($row = mysqli_fetch_assoc($listRes)) {
        $attachHtml = empty($row['attach_path']) ? '-' : "<a href='uploads/{$row['attach_path']}' target='_blank' style='color:#3498db;'>查看</a>";
        $reportList .= "
        <tr>
            <td>{$row['id']}</td>
            <td>{$row['invoice_no']}</td>
            <td>{$row['invoice_date']}</td>
            <td>{$row['amount']}</td>
            <td>{$row['supplier_name']}</td>
            <td>{$row['customer_name']}</td>
            <td>{$attachHtml}</td>
            <td>{$row['create_time']}</td>
        </tr>";
    }
} else {
    $reportList = '<tr><td colspan="8" style="text-align:center;">暂无符合条件的发票数据</td></tr>';
}
?>

<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>发票报表分析 - 发票管理系统</title>
    <style>
        * { margin: 0; padding: 0; box-sizing: border-box; font-family: "Microsoft YaHei", sans-serif; }
        body { background-color: #f5f7fa; padding: 20px; }
        .container { max-width: 1400px; margin: 0 auto; background: #fff; border-radius: 8px; box-shadow: 0 2px 12px rgba(0,0,0,0.1); overflow: hidden; }
        .header { background-color: #2c3e50; color: #fff; padding: 15px 20px; font-size: 18px; font-weight: bold; }
        .content { padding: 20px; }
        .stat-card-group { display: grid; grid-template-columns: repeat(5, 1fr); gap: 15px; margin-bottom: 20px; }
        .stat-card { background: #f8f9fa; padding: 15px; border-radius: 8px; text-align: center; }
        .stat-card h4 { color: #666; margin: 0; font-size: 14px; }
        .stat-card p { font-size: 24px; font-weight: bold; margin: 5px 0 0; }
        .form-group { margin-bottom: 15px; }
        .form-control { width: 100%; padding: 8px 12px; border: 1px solid #e6e9ed; border-radius: 4px; font-size: 14px; }
        .btn { padding: 8px 16px; border: none; border-radius: 4px; cursor: pointer; font-size: 14px; margin-right: 10px; text-decoration: none; display: inline-block; text-align: center; }
        .btn-blue { background-color: #3498db; color: #fff; }
        .btn-green { background-color: #2ecc71; color: #fff; }
        .btn-gray { background-color: #95a5a6; color: #fff; }
        .filter-form { background: #f8f9fa; padding: 20px; border-radius: 8px; margin-bottom: 20px; }
        .filter-row { display: grid; grid-template-columns: repeat(6, 1fr); gap: 10px; margin-bottom: 10px; }
        .chart-area { margin-bottom: 20px; }
        .table { width: 100%; border-collapse: collapse; }
        .table th, .table td { padding: 8px 12px; border: 1px solid #e6e9ed; text-align: left; }
        .table th { background-color: #f8f9fa; font-weight: bold; }
        .back-link { margin-top: 20px; display: inline-block; color: #3498db; text-decoration: none; }
    </style>
    <!-- ECharts图表库 -->
    <script src="https://cdn.jsdelivr.net/npm/echarts/dist/echarts.min.js"></script>
</head>
<body>
    <div class="container">
        <!-- 页面头部 -->
        <div class="header">
            发票报表分析
            <a href="layout.php" style="color: #fff; font-size: 14px; font-weight: normal; float: right; text-decoration: none;">返回系统首页</a>
        </div>

        <!-- 主要内容 -->
        <div class="content">
            <!-- 统计卡片 -->
            <div class="stat-card-group">
                <div class="stat-card">
                    <h4>发票总数</h4>
                    <p style="color: #3498db;"><?= $stat['total_count'] ?></p>
                </div>
                <div class="stat-card">
                    <h4>总金额（元）</h4>
                    <p style="color: #2ecc71;"><?= $stat['total_amount'] ?></p>
                </div>
                <div class="stat-card">
                    <h4>平均金额</h4>
                    <p style="color: #9b59b6;"><?= round($stat['avg_amount'], 2) ?></p>
                </div>
                <div class="stat-card">
                    <h4>最大金额</h4>
                    <p style="color: #e74c3c;"><?= $stat['max_amount'] ?></p>
                </div>
                <div class="stat-card">
                    <h4>最小金额</h4>
                    <p style="color: #f39c12;"><?= $stat['min_amount'] ?></p>
                </div>
            </div>

            <!-- 筛选表单 -->
            <div class="filter-form">
                <form method="get">
                    <div class="filter-row">
                        <div>
                            <label>关键词</label>
                            <input type="text" name="keyword" class="form-control" value="<?= $_GET['keyword'] ?? '' ?>" placeholder="发票号/供应商/客户">
                        </div>
                        <div>
                            <label>开始日期</label>
                            <input type="date" name="start_date" class="form-control" value="<?= $_GET['start_date'] ?? '' ?>">
                        </div>
                        <div>
                            <label>结束日期</label>
                            <input type="date" name="end_date" class="form-control" value="<?= $_GET['end_date'] ?? '' ?>">
                        </div>
                        <div>
                            <label>供应商</label>
                            <select name="supplier_id" class="form-control"><?= $supplierOptions ?></select>
                        </div>
                        <div>
                            <label>客户</label>
                            <select name="customer_id" class="form-control"><?= $customerOptions ?></select>
                        </div>
                        <div>
                            <label>金额范围（元）</label>
                            <div style="display:flex; gap:5px;">
                                <input type="number" name="min_amount" class="form-control" value="<?= $_GET['min_amount'] ?? '' ?>" placeholder="最小">
                                <input type="number" name="max_amount" class="form-control" value="<?= $_GET['max_amount'] ?? '' ?>" placeholder="最大">
                            </div>
                        </div>
                    </div>
                    <div>
                        <button type="submit" class="btn btn-blue">查询报表</button>
                        <button type="button" onclick="window.location.href='?export=excel&<?= http_build_query($_GET) ?>'" class="btn btn-green">导出Excel</button>
                        <a href="report.php" class="btn btn-gray">重置筛选</a>
                    </div>
                </form>
            </div>

            <!-- 图表展示 -->
            <div class="chart-area">
                <h4 style="margin-bottom:15px;">供应商金额分布</h4>
                <div id="supplierChart" style="width:100%; height:300px;"></div>
            </div>

            <!-- 报表列表 -->
            <div>
                <h4 style="margin-bottom:15px;">发票明细</h4>
                <table class="table">
                    <thead>
                        <tr>
                            <<th>ID</</th>
                            <<th>发票号码</</th>
                            <<th>开票日期</</th>
                            <<th>金额（元）</</th>
                            <<th>供应商</</th>
                            <<th>客户</</th>
                            <<th>附件</</th>
                            <<th>添加时间</</th>
                        </tr>
                    </thead>
                    <tbody><?= $reportList ?></tbody>
                </table>
            </div>

            <!-- 返回链接 -->
            <a href="layout.php" class="back-link">← 返回系统首页</a>
        </div>
    </div>

    <!-- 图表初始化脚本 -->
    <script>
        var supChart = echarts.init(document.getElementById('supplierChart'));
        var supData = <?= json_encode($supplierStat) ?>;
        supChart.setOption({
            tooltip: { trigger: 'axis', axisPointer: { type: 'shadow' } },
            xAxis: { type: 'category', data: supData.map(item => item.name) },
            yAxis: { type: 'value', name: '金额（元）' },
            series: [{
                name: '发票金额',
                type: 'bar',
                data: supData.map(item => item.total),
                itemStyle: { color: '#3498db' }
            }]
        });
        window.onresize = function() { supChart.resize(); };
    </script>
</body>
</html>