deskhub/DeskHub-Webseite/staff/export.php

<?php
require_once __DIR__ . '/../includes/bootstrap.php';
$staff = require_admin_or_permission('export_tickets');
$pdo = get_db();
$myDeptIds = staff_department_ids($staff); // null = Admin, alle Abteilungen

$type = $_GET['type'] ?? 'tickets';

function csv_response(string $filename, array $header, array $rows): void
{
    header('Content-Type: text/csv; charset=UTF-8');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    $out = fopen('php://output', 'w');
    fwrite($out, "\xEF\xBB\xBF"); // UTF-8 BOM, damit Excel Umlaute korrekt anzeigt
    fputcsv($out, $header, ';');
    foreach ($rows as $row) {
        fputcsv($out, $row, ';');
    }
    fclose($out);
    exit;
}

if ($type === 'tickets') {
    // Dieselben Filter wie staff/tickets.php, inkl. Abteilungs-Zugriffsbeschränkung
    $filterDept   = $_GET['department_id'] ?? '';
    $filterStatus = $_GET['status'] ?? '';
    $filterPrio   = $_GET['priority_id'] ?? '';
    $filterAssign = $_GET['assigned'] ?? '';
    $filterTag    = $_GET['tag_id'] ?? '';
    $search       = trim($_GET['q'] ?? '');

    $where = [];
    $params = [];

    [$deptFilter, $deptParams] = dept_filter_sql($myDeptIds, 't.department_id');
    if ($deptFilter !== '') { $where[] = substr($deptFilter, 5); array_push($params, ...$deptParams); }

    if ($filterDept !== '') { $where[] = 't.department_id = ?'; $params[] = $filterDept; }
    if ($filterStatus !== '') { $where[] = 't.status = ?'; $params[] = $filterStatus; }
    if ($filterPrio !== '') { $where[] = 't.priority_id = ?'; $params[] = $filterPrio; }
    if ($filterAssign === 'me') { $where[] = 't.assigned_staff_id = ?'; $params[] = $staff['id']; }
    elseif ($filterAssign === 'unassigned') { $where[] = 't.assigned_staff_id IS NULL'; }
    elseif ($filterAssign !== '') { $where[] = 't.assigned_staff_id = ?'; $params[] = $filterAssign; }
    if ($filterTag !== '') { $where[] = 't.id IN (SELECT ticket_id FROM ticket_tags WHERE tag_id = ?)'; $params[] = $filterTag; }
    if ($search !== '') {
        $where[] = '(t.subject LIKE ? OR t.ticket_number LIKE ? OR t.requester_email LIKE ?)';
        $like = '%' . $search . '%';
        array_push($params, $like, $like, $like);
    }
    if ($filterStatus === '') { $where[] = "t.status != 'closed'"; }

    $sql = "SELECT t.ticket_number, t.subject, t.requester_name, t.requester_email, d.name AS department_name,
                   p.name AS priority_name, t.status, s.name AS staff_name, t.created_at, t.resolution_due_at
            FROM tickets t
            JOIN priorities p ON p.id = t.priority_id
            JOIN departments d ON d.id = t.department_id
            LEFT JOIN staff s ON s.id = t.assigned_staff_id";
    if ($where) { $sql .= ' WHERE ' . implode(' AND ', $where); }
    $sql .= ' ORDER BY t.resolution_due_at ASC LIMIT 2000';

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);

    $rows = [];
    foreach ($stmt->fetchAll() as $t) {
        [$statusLabel] = status_label($t['status']);
        $rows[] = [
            $t['ticket_number'], $t['subject'], $t['requester_name'], $t['requester_email'],
            $t['department_name'], $t['priority_name'], $statusLabel,
            $t['staff_name'] ?: '', $t['created_at'], $t['resolution_due_at'],
        ];
    }

    csv_response('tickets_' . date('Y-m-d') . '.csv',
        ['Ticketnummer', 'Betreff', 'Anfrager', 'E-Mail', 'Abteilung', 'Priorität', 'Status', 'Zugewiesen', 'Erstellt', 'Fällig'],
        $rows);
}

if ($type === 'report') {
    // Berichte sind seit Kurzem für alle Mitarbeiter sichtbar (abteilungsbeschränkt für Nicht-Admins),
    // der Export folgt derselben Regel wie staff/reports.php.
    $dim = $_GET['dim'] ?? 'dept';
    $dimColumn = ['dept' => 'department_id', 'topic' => 'help_topic_id', 'agent' => 'assigned_staff_id'][$dim] ?? 'department_id';

    $preset = $_GET['range'] ?? '30d';
    $today = new DateTime('today');
    switch ($preset) {
        case '7d': $from = (clone $today)->modify('-6 days'); $to = clone $today; break;
        case 'thismonth': $from = new DateTime(date('Y-m-01')); $to = clone $today; break;
        case 'thisyear': $from = new DateTime(date('Y-01-01')); $to = clone $today; break;
        case 'custom':
            $from = !empty($_GET['from']) ? new DateTime($_GET['from']) : (clone $today)->modify('-29 days');
            $to = !empty($_GET['to']) ? new DateTime($_GET['to']) : clone $today;
            break;
        default: $from = (clone $today)->modify('-29 days'); $to = clone $today;
    }
    if ($from > $to) { [$from, $to] = [$to, $from]; }
    $rangeFrom = $from->format('Y-m-d 00:00:00');
    $rangeTo = $to->format('Y-m-d 23:59:59');

    $rows = build_report_table($pdo, $dimColumn, $rangeFrom, $rangeTo, $myDeptIds);

    $labelMap = match ($dim) {
        'topic' => $pdo->query('SELECT id, name FROM help_topics')->fetchAll(PDO::FETCH_KEY_PAIR),
        'agent' => $pdo->query('SELECT id, name FROM staff')->fetchAll(PDO::FETCH_KEY_PAIR),
        default => $pdo->query('SELECT id, name FROM departments')->fetchAll(PDO::FETCH_KEY_PAIR),
    };
    $noneLabel = match ($dim) {
        'topic' => 'Kein Hilfethema',
        'agent' => 'Nicht zugewiesen',
        default => 'Ohne Abteilung',
    };

    $out = [];
    foreach ($rows as $key => $r) {
        $label = $key === 'none' ? $noneLabel : ($labelMap[(int) $key] ?? "#{$key}");
        $out[] = [
            $label, $r['geoeffnet'], $r['zugewiesen'], $r['ueberfaellig'], $r['geschlossen'],
            $r['wieder_geoeffnet'], $r['geloescht'],
            $r['servicezeit'] ?? '', $r['reaktionszeit'] ?? '',
        ];
    }

    csv_response('bericht_' . $dim . '_' . date('Y-m-d') . '.csv',
        ['Bezeichnung', 'Geöffnet', 'Zugewiesen', 'Überfällig', 'Geschlossen', 'Wieder geöffnet', 'Gelöscht', 'Servicezeit (Std)', 'Reaktionszeit (Std)'],
        $out);
}

http_response_code(400);
echo 'Unbekannter Export-Typ.';