150 lines
8.6 KiB
PHP
150 lines
8.6 KiB
PHP
<?php
|
||
/**
|
||
* Bericht-Baukasten: frei konfigurierbare Auswertungen über Tickets.
|
||
* Alle SQL-Bausteine stammen aus festen Listen (kein Nutzereingabe-SQL), Werte laufen über Parameter.
|
||
*/
|
||
|
||
/** Gruppierungen: key => [Bezeichnung, SQL-Ausdruck-Generator, JOIN, Anzeige-Spalte]. */
|
||
function report_dimensions(PDO $pdo): array
|
||
{
|
||
$sqlite = $pdo->getAttribute(PDO::ATTR_DRIVER_NAME) === 'sqlite';
|
||
$bucket = fn(string $fmtSqlite, string $fmtMysql) => $sqlite ? "strftime('{$fmtSqlite}', t.created_at)" : "DATE_FORMAT(t.created_at, '{$fmtMysql}')";
|
||
return [
|
||
'department' => ['Abteilung', 'COALESCE(d.name, \'–\')', 'LEFT JOIN departments d ON d.id = t.department_id'],
|
||
'help_topic' => ['Hilfethema', 'COALESCE(h.name, \'ohne Thema\')', 'LEFT JOIN help_topics h ON h.id = t.help_topic_id'],
|
||
'agent' => ['Mitarbeiter', 'COALESCE(s.name, \'nicht zugewiesen\')', 'LEFT JOIN staff s ON s.id = t.assigned_staff_id'],
|
||
'team' => ['Team', 'COALESCE(tm.name, \'ohne Team\')', 'LEFT JOIN teams tm ON tm.id = t.team_id'],
|
||
'priority' => ['Priorität', 'p.name', 'JOIN priorities p ON p.id = t.priority_id'],
|
||
'status' => ['Status', 't.status', ''],
|
||
'resolution_code' => ['Abschlussgrund', "CASE t.resolution_code WHEN 'fixed' THEN 'Fehler behoben' WHEN 'guidance' THEN 'Anleitung oder Beratung' WHEN 'duplicate' THEN 'Doppelt / zusammengeführt' WHEN 'not_reproducible' THEN 'Nicht reproduzierbar' WHEN 'no_action' THEN 'Keine Maßnahme erforderlich' WHEN 'spam' THEN 'Spam oder Sperre' WHEN 'automated' THEN 'Automatisch geschlossen' WHEN 'other' THEN 'Sonstiges' ELSE 'Nicht erfasst' END", ''],
|
||
'source' => ['Quelle', 't.source', ''],
|
||
'day' => ['Tag', $bucket('%Y-%m-%d', '%Y-%m-%d'), ''],
|
||
'week' => ['Woche', $bucket('%Y-W%W', '%x-W%v'), ''],
|
||
'month' => ['Monat', $bucket('%Y-%m', '%Y-%m'), ''],
|
||
];
|
||
}
|
||
|
||
/** Kennzahlen: key => [Bezeichnung, SQL-Ausdruck, Einheit]. */
|
||
function report_metrics(PDO $pdo): array
|
||
{
|
||
$sqlite = $pdo->getAttribute(PDO::ATTR_DRIVER_NAME) === 'sqlite';
|
||
$hours = fn(string $from, string $to) => $sqlite
|
||
? "(julianday({$to}) - julianday({$from})) * 24"
|
||
: "TIMESTAMPDIFF(MINUTE, {$from}, {$to}) / 60";
|
||
return [
|
||
'total' => ['Tickets gesamt', 'COUNT(*)', ''],
|
||
'open' => ['Offen', "SUM(CASE WHEN t.status NOT IN ('resolved','closed') THEN 1 ELSE 0 END)", ''],
|
||
'resolved' => ['Gelöst/Geschlossen', "SUM(CASE WHEN t.status IN ('resolved','closed') THEN 1 ELSE 0 END)", ''],
|
||
'overdue' => ['Überfällig', "SUM(CASE WHEN t.is_overdue = 1 AND t.status NOT IN ('resolved','closed') THEN 1 ELSE 0 END)", ''],
|
||
'avg_response' => ['Ø Erste Antwort', 'AVG(CASE WHEN t.first_responded_at IS NOT NULL THEN ' . $hours('t.created_at', 't.first_responded_at') . ' END)', 'Std'],
|
||
'avg_resolve' => ['Ø Lösungszeit', 'AVG(CASE WHEN t.resolved_at IS NOT NULL THEN ' . $hours('t.created_at', 't.resolved_at') . ' END)', 'Std'],
|
||
'sla_response' => ['SLA Erste Antwort', 'AVG(CASE WHEN t.first_responded_at IS NOT NULL AND t.first_response_due_at IS NOT NULL THEN (CASE WHEN t.first_responded_at <= t.first_response_due_at THEN 100.0 ELSE 0.0 END) END)', '%'],
|
||
'sla_resolve' => ['SLA Lösung', 'AVG(CASE WHEN t.resolved_at IS NOT NULL AND t.resolution_due_at IS NOT NULL THEN (CASE WHEN t.resolved_at <= t.resolution_due_at THEN 100.0 ELSE 0.0 END) END)', '%'],
|
||
];
|
||
}
|
||
|
||
/** Zulässige Filter: key => SQL-Spalte. */
|
||
function report_filter_columns(): array
|
||
{
|
||
return ['departmentId' => 't.department_id', 'priorityId' => 't.priority_id', 'agentId' => 't.assigned_staff_id',
|
||
'helpTopicId' => 't.help_topic_id', 'status' => 't.status', 'source' => 't.source'];
|
||
}
|
||
|
||
/** Bereinigt eine Berichtskonfiguration auf erlaubte Werte (mit sinnvollen Standards). */
|
||
function report_clean_config(PDO $pdo, array $in): array
|
||
{
|
||
$dims = report_dimensions($pdo); $metrics = report_metrics($pdo);
|
||
$dimension = isset($dims[$in['dimension'] ?? '']) ? $in['dimension'] : 'department';
|
||
$chosen = array_values(array_filter((array) ($in['metrics'] ?? []), fn($m) => is_string($m) && isset($metrics[$m])));
|
||
$valid = fn($d) => is_string($d) && preg_match('/^\d{4}-\d{2}-\d{2}$/', $d) && strtotime($d) !== false;
|
||
$to = $valid($in['to'] ?? null) ? $in['to'] : date('Y-m-d');
|
||
$from = $valid($in['from'] ?? null) ? $in['from'] : date('Y-m-d', strtotime('-30 days'));
|
||
if ($from > $to) [$from, $to] = [$to, $from];
|
||
$filters = [];
|
||
foreach (array_keys(report_filter_columns()) as $k) {
|
||
if (isset($in['filters'][$k]) && is_scalar($in['filters'][$k]) && trim((string) $in['filters'][$k]) !== '') $filters[$k] = trim((string) $in['filters'][$k]);
|
||
}
|
||
$sort = $in['sort'] ?? 'label';
|
||
$sort = ($sort === 'label' || isset($metrics[$sort])) ? $sort : 'label';
|
||
return [
|
||
'dimension' => $dimension, 'metrics' => $chosen ?: ['total', 'open', 'resolved'],
|
||
'from' => $from, 'to' => $to, 'filters' => $filters, 'sort' => $sort, 'desc' => !empty($in['desc']),
|
||
];
|
||
}
|
||
|
||
/**
|
||
* Führt einen Bericht aus. $deptScope = erlaubte Abteilungs-IDs des Mitarbeiters (null = alle).
|
||
*
|
||
* @return array{columns: array<int,array{key:string,label:string,unit:string}>, rows: array<int,array>, totals: array}
|
||
*/
|
||
function report_run(PDO $pdo, array $config, ?array $deptScope): array
|
||
{
|
||
$config = report_clean_config($pdo, $config);
|
||
$dims = report_dimensions($pdo); $metrics = report_metrics($pdo);
|
||
[$dimLabel, $dimExpr, $dimJoin] = $dims[$config['dimension']];
|
||
|
||
$select = ["{$dimExpr} AS label"];
|
||
foreach ($config['metrics'] as $m) $select[] = $metrics[$m][1] . " AS m_{$m}";
|
||
$where = ['t.created_at >= ?', 't.created_at < ?'];
|
||
$params = [$config['from'] . ' 00:00:00', date('Y-m-d', strtotime($config['to'] . ' +1 day')) . ' 00:00:00'];
|
||
foreach ($config['filters'] as $k => $v) {
|
||
$where[] = report_filter_columns()[$k] . ' = ?';
|
||
$params[] = $v;
|
||
}
|
||
[$scopeSql, $scopeParams] = dept_filter_sql($deptScope, 't.department_id');
|
||
$joins = [$dimJoin];
|
||
$sql = 'SELECT ' . implode(', ', $select) . ' FROM tickets t ' . implode(' ', array_filter($joins))
|
||
. ' WHERE ' . implode(' AND ', $where) . $scopeSql . ' AND t.is_spam = 0 GROUP BY label';
|
||
$stmt = $pdo->prepare($sql);
|
||
$stmt->execute([...$params, ...$scopeParams]);
|
||
$rows = $stmt->fetchAll();
|
||
|
||
foreach ($rows as &$r) {
|
||
foreach ($config['metrics'] as $m) {
|
||
$v = $r["m_{$m}"];
|
||
$r[$m] = $v === null ? null : ($metrics[$m][2] === '' ? (int) $v : round((float) $v, 1));
|
||
unset($r["m_{$m}"]);
|
||
}
|
||
}
|
||
unset($r);
|
||
$key = $config['sort'];
|
||
usort($rows, function ($a, $b) use ($key) {
|
||
$c = $key === 'label' ? strnatcasecmp((string) $a['label'], (string) $b['label']) : (($a[$key] ?? -1) <=> ($b[$key] ?? -1));
|
||
return $c;
|
||
});
|
||
if ($config['desc']) $rows = array_reverse($rows);
|
||
|
||
$totals = [];
|
||
foreach ($config['metrics'] as $m) {
|
||
$vals = array_filter(array_column($rows, $m), fn($v) => $v !== null);
|
||
if ($metrics[$m][2] === '') $totals[$m] = array_sum($vals);
|
||
else $totals[$m] = $vals ? round(array_sum($vals) / count($vals), 1) : null; // Durchschnitt der Zeilen
|
||
}
|
||
$columns = [['key' => 'label', 'label' => $dimLabel, 'unit' => '']];
|
||
foreach ($config['metrics'] as $m) $columns[] = ['key' => $m, 'label' => $metrics[$m][0], 'unit' => $metrics[$m][2]];
|
||
return ['config' => $config, 'columns' => $columns, 'rows' => $rows, 'totals' => $totals];
|
||
}
|
||
|
||
/** Bericht als CSV (Semikolon-getrennt mit UTF-8-BOM für Excel). */
|
||
function report_to_csv(array $report): string
|
||
{
|
||
$out = fopen('php://temp', 'r+');
|
||
fwrite($out, "\xEF\xBB\xBF");
|
||
fputcsv($out, array_map(fn($c) => $c['label'] . ($c['unit'] ? " ({$c['unit']})" : ''), $report['columns']), ';');
|
||
foreach ($report['rows'] as $row) {
|
||
$line = [];
|
||
foreach ($report['columns'] as $c) {
|
||
$v = $row[$c['key']] ?? '';
|
||
// Schutz vor Formel-Injektion in Tabellenkalkulationen
|
||
if ($c['key'] === 'label' && is_string($v) && preg_match('/^[=+\-@]/', $v)) $v = "'" . $v;
|
||
$line[] = is_float($v) ? str_replace('.', ',', (string) $v) : $v;
|
||
}
|
||
fputcsv($out, $line, ';');
|
||
}
|
||
$tot = ['Gesamt'];
|
||
foreach (array_slice($report['columns'], 1) as $c) $tot[] = $report['totals'][$c['key']] ?? '';
|
||
fputcsv($out, $tot, ';');
|
||
rewind($out);
|
||
return (string) stream_get_contents($out);
|
||
}
|