106 lines
4.0 KiB
PHP
106 lines
4.0 KiB
PHP
<?php
|
|
declare(strict_types=1);
|
|
require_once __DIR__ . '/common.php';
|
|
|
|
api_require_method('GET');
|
|
$staff = api_require_staff();
|
|
$pdo = get_db();
|
|
|
|
$departmentIds = staff_department_ids($staff);
|
|
[$deptFilter, $deptParams] = dept_filter_sql($departmentIds, 'department_id');
|
|
|
|
$stmt = $pdo->prepare(
|
|
"SELECT COUNT(*) FROM tickets
|
|
WHERE assigned_staff_id = ? AND status NOT IN ('resolved','closed'){$deptFilter}"
|
|
);
|
|
$stmt->execute([$staff['id'], ...$deptParams]);
|
|
$myOpen = (int) $stmt->fetchColumn();
|
|
|
|
$stmt = $pdo->prepare(
|
|
"SELECT COUNT(*) FROM tickets
|
|
WHERE status NOT IN ('resolved','closed') AND (is_overdue = 1 OR (sla_paused_at IS NULL AND resolution_due_at IS NOT NULL AND resolution_due_at < ?))
|
|
AND status NOT IN ('resolved','closed'){$deptFilter}"
|
|
);
|
|
$stmt->execute([db_now(), ...$deptParams]);
|
|
$overdue = (int) $stmt->fetchColumn();
|
|
|
|
$stmt = $pdo->prepare(
|
|
"SELECT COUNT(*) FROM tickets
|
|
WHERE assigned_staff_id IS NULL AND status NOT IN ('resolved','closed'){$deptFilter}"
|
|
);
|
|
$stmt->execute($deptParams);
|
|
$unassigned = (int) $stmt->fetchColumn();
|
|
|
|
$stmt = $pdo->prepare("SELECT COUNT(*) FROM tickets WHERE status = 'open'{$deptFilter}");
|
|
$stmt->execute($deptParams);
|
|
$unanswered = (int) $stmt->fetchColumn();
|
|
|
|
$todayStart = date('Y-m-d 00:00:00');
|
|
$stmt = $pdo->prepare("SELECT COUNT(*) FROM tickets WHERE resolved_at >= ?{$deptFilter}");
|
|
$stmt->execute([$todayStart, ...$deptParams]);
|
|
$resolvedToday = (int) $stmt->fetchColumn();
|
|
|
|
$periodStart = date('Y-m-d 00:00:00', strtotime('-30 days'));
|
|
$stmt = $pdo->prepare(
|
|
"SELECT
|
|
COUNT(*) AS total,
|
|
SUM(CASE WHEN resolution_due_at IS NULL OR resolved_at <= resolution_due_at THEN 1 ELSE 0 END) AS on_time
|
|
FROM tickets
|
|
WHERE resolved_at >= ?{$deptFilter}"
|
|
);
|
|
$stmt->execute([$periodStart, ...$deptParams]);
|
|
$slaRow = $stmt->fetch();
|
|
$slaTotal = (int) ($slaRow['total'] ?? 0);
|
|
$slaPercent = $slaTotal > 0 ? round(((int) $slaRow['on_time'] / $slaTotal) * 100, 1) : null;
|
|
|
|
// "Priorisiert": dieselbe Auswahl wie in staff/dashboard.php ("mir zugewiesen oder
|
|
// unbearbeitet"), damit die App-Übersicht dieselben Tickets zeigt wie die Web-Oberfläche.
|
|
[$deptFilterAliased, $deptParamsAliased] = dept_filter_sql($departmentIds, 't.department_id');
|
|
$stmt = $pdo->prepare(
|
|
"SELECT t.*, p.name AS priority_name, p.color AS priority_color, d.name AS department_name, s.name AS staff_name
|
|
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
|
|
WHERE t.status NOT IN ('resolved','closed') AND (t.assigned_staff_id = ? OR t.assigned_staff_id IS NULL){$deptFilterAliased}
|
|
ORDER BY t.resolution_due_at ASC
|
|
LIMIT 12"
|
|
);
|
|
$stmt->execute([$staff['id'], ...$deptParamsAliased]);
|
|
$recent = array_map('api_ticket_summary', $stmt->fetchAll());
|
|
|
|
$response = [
|
|
'myOpen' => $myOpen,
|
|
'overdue' => $overdue,
|
|
'unassigned' => $unassigned,
|
|
'unanswered' => $unanswered,
|
|
'resolvedToday' => $resolvedToday,
|
|
'slaPercent' => $slaPercent,
|
|
'recent' => $recent,
|
|
'departments' => null,
|
|
];
|
|
|
|
// Voller Überblick über alle Abteilungen nur für uneingeschränkten Zugriff (Admin).
|
|
if ($departmentIds === null) {
|
|
$stmt = $pdo->prepare(
|
|
"SELECT d.id, d.name,
|
|
COUNT(CASE WHEN t.status NOT IN ('resolved','closed') THEN 1 END) AS open_count,
|
|
COUNT(CASE WHEN t.status NOT IN ('resolved','closed')
|
|
AND (t.is_overdue = 1 OR (t.sla_paused_at IS NULL AND t.resolution_due_at IS NOT NULL AND t.resolution_due_at < ?))
|
|
THEN 1 END) AS overdue_count
|
|
FROM departments d
|
|
LEFT JOIN tickets t ON t.department_id = d.id
|
|
GROUP BY d.id, d.name
|
|
ORDER BY d.name"
|
|
);
|
|
$stmt->execute([db_now()]);
|
|
$rows = $stmt->fetchAll();
|
|
$response['departments'] = array_map(fn(array $row) => [
|
|
'name' => $row['name'],
|
|
'open' => (int) $row['open_count'],
|
|
'overdue' => (int) $row['overdue_count'],
|
|
], $rows);
|
|
}
|
|
|
|
api_json($response);
|