304 lines
16 KiB
PHP
304 lines
16 KiB
PHP
<?php
|
|
declare(strict_types=1);
|
|
|
|
/**
|
|
* osTicket-Import (Kern): liest einen SQL-Dump (phpMyAdmin oder mysqldump, auch mit anderem Tabellenpräfix als "ost_")
|
|
* und übernimmt Kunden, Abteilungen, Hilfethemen, Tickets, Nachrichten und Anhänge in DeskHub.
|
|
*
|
|
* Ablauf (jeweils in kleinen Schritten, damit auch Dumps mit mehreren hundert MB im Browser laufen):
|
|
* 1. osticket_analyze_step(): liest die Datei blockweise, merkt sich die nötigen Daten (Zustandsordner) und schreibt
|
|
* Nachrichtentexte zeilenweise in entries.jsonl.
|
|
* 2. osticket_import_step(): pro Durchgang (users, structure, tickets, threads, attachments) werden Datensätze ab
|
|
* einem Offset übernommen. Jeder Schritt meldet next_offset, damit der Aufrufer nichts überspringt.
|
|
*/
|
|
|
|
const OSTICKET_ATTACHMENT_MAX_BYTES = 50 * 1024 * 1024;
|
|
|
|
/** Tabellen, die gelesen werden (Schlüssel = Name ohne Präfix) mit den Spalten von osTicket 1.18 für Dumps ohne Spaltenliste. */
|
|
function osticket_tables(): array
|
|
{
|
|
return [
|
|
'user' => ['id', 'org_id', 'default_email_id', 'status', 'name', 'created', 'updated'],
|
|
'user_email' => ['id', 'user_id', 'flags', 'address'],
|
|
'ticket' => ['ticket_id', 'ticket_pid', 'number', 'user_id', 'user_email_id', 'status_id', 'dept_id', 'sla_id', 'topic_id', 'staff_id', 'team_id', 'email_id', 'lock_id', 'flags', 'sort', 'ip_address', 'source', 'source_extra', 'isoverdue', 'isanswered', 'duedate', 'est_duedate', 'reopened', 'closed', 'lastupdate', 'created', 'updated'],
|
|
'ticket__cdata' => ['ticket_id', 'subject', 'priority'],
|
|
'ticket_priority' => ['priority_id', 'priority', 'priority_desc', 'priority_color', 'priority_urgency', 'ispublic'],
|
|
'ticket_status' => ['id', 'name', 'state', 'mode', 'flags', 'sort', 'properties', 'created', 'updated'],
|
|
'thread' => ['id', 'object_id', 'object_type', 'extra', 'lastresponse', 'lastmessage', 'created'],
|
|
'thread_collaborator' => ['id', 'flags', 'thread_id', 'user_id', 'role', 'created', 'updated'],
|
|
'thread_entry' => ['id', 'pid', 'thread_id', 'staff_id', 'user_id', 'type', 'flags', 'poster', 'editor', 'editor_type', 'source', 'title', 'body', 'format', 'ip_address', 'extra', 'recipients', 'created', 'updated'],
|
|
'department' => ['id', 'pid', 'tpl_id', 'sla_id', 'schedule_id', 'email_id', 'autoresp_email_id', 'manager_id', 'flags', 'name', 'signature', 'ispublic', 'group_membership', 'ticket_auto_response', 'message_auto_response', 'path', 'updated', 'created'],
|
|
'help_topic' => ['topic_id', 'topic_pid', 'ispublic', 'isactive', 'ticket_auto_response', 'noautoresp', 'flags', 'status_id', 'priority_id', 'dept_id', 'staff_id', 'team_id', 'sla_id', 'page_id', 'sequence_id', 'sort', 'topic', 'number_format', 'notes', 'created', 'updated'],
|
|
'staff' => ['staff_id', 'dept_id', 'role_id', 'username', 'firstname', 'lastname', 'passwd', 'backend', 'email'],
|
|
'attachment' => ['id', 'object_id', 'type', 'file_id', 'name', 'inline', 'lang'],
|
|
'file' => ['id', 'ft', 'bk', 'type', 'size', 'key', 'signature', 'name', 'attrs', 'created'],
|
|
'file_chunk' => ['file_id', 'chunk_id', 'filedata'],
|
|
];
|
|
}
|
|
|
|
/** Tabellenname mit beliebigem Präfix -> Schlüssel aus osticket_tables() oder null. */
|
|
function osticket_table_key(string $table): ?string
|
|
{
|
|
static $keys = null;
|
|
$keys ??= (function () { $k = array_keys(osticket_tables()); usort($k, fn($a, $b) => strlen($b) <=> strlen($a)); return $k; })();
|
|
foreach ($keys as $key) {
|
|
if ($table === $key) return $key;
|
|
if (str_ends_with($table, '_' . $key) || str_ends_with($table, $key) && strlen($table) > strlen($key) && $table[strlen($table) - strlen($key) - 1] === '_') return $key;
|
|
}
|
|
return null;
|
|
}
|
|
|
|
/** Inhalt zwischen Anführungszeichen (MySQL-Maskierung) in den echten Text umwandeln. */
|
|
function osticket_unescape(string $s): string
|
|
{
|
|
if (!str_contains($s, '\\') && !str_contains($s, "''")) return $s;
|
|
return (string) preg_replace_callback('/\\\\(.)|\'\'/s', function (array $m): string {
|
|
if ($m[0] === "''") return "'";
|
|
return match ($m[1]) { '0' => "\0", 'n' => "\n", 'r' => "\r", 't' => "\t", 'b' => "\x08", 'Z' => "\x1a", '%' => '\\%', '_' => '\\_', default => $m[1] };
|
|
}, $s);
|
|
}
|
|
|
|
/**
|
|
* Zerlegt "(a,'b'),(c,NULL)" in Wertelisten. Strings werden entmaskiert, NULL wird null, alles andere (Zahlen, 0x…) bleibt Text.
|
|
* Funktioniert für eine Zeile pro Datensatz (phpMyAdmin) genauso wie für mehrere Datensätze in einer Zeile (mysqldump).
|
|
*
|
|
* @return Generator<int, array<int, ?string>>
|
|
*/
|
|
function osticket_rows(string $s): Generator
|
|
{
|
|
$n = strlen($s);
|
|
$i = 0;
|
|
while ($i < $n) {
|
|
$i = strpos($s, '(', $i);
|
|
if ($i === false) return;
|
|
$i++;
|
|
$vals = [];
|
|
$closed = false;
|
|
while ($i < $n) {
|
|
while ($i < $n && ($s[$i] === ' ' || $s[$i] === "\t")) $i++;
|
|
if ($i >= $n) break;
|
|
if ($s[$i] === "'") {
|
|
$j = $i + 1;
|
|
$buf = '';
|
|
while (true) {
|
|
$k = $j + strcspn($s, "'\\", $j);
|
|
if ($k >= $n) { $buf .= substr($s, $j); $j = $n; break; }
|
|
$buf .= substr($s, $j, $k - $j);
|
|
if ($s[$k] === '\\') { $buf .= substr($s, $k, 2); $j = $k + 2; continue; }
|
|
if ($k + 1 < $n && $s[$k + 1] === "'") { $buf .= "''"; $j = $k + 2; continue; }
|
|
$j = $k + 1;
|
|
break;
|
|
}
|
|
$vals[] = osticket_unescape($buf);
|
|
$i = $j;
|
|
} else {
|
|
$k = $i + strcspn($s, ',)', $i);
|
|
$tok = trim(substr($s, $i, $k - $i));
|
|
$vals[] = strcasecmp($tok, 'NULL') === 0 ? null : $tok;
|
|
$i = $k;
|
|
}
|
|
while ($i < $n && ($s[$i] === ' ' || $s[$i] === "\t")) $i++;
|
|
if ($i < $n && $s[$i] === ',') { $i++; continue; }
|
|
if ($i < $n && $s[$i] === ')') { $i++; $closed = true; break; }
|
|
}
|
|
if ($closed) yield $vals;
|
|
}
|
|
}
|
|
|
|
/** "INSERT INTO `t` (`a`,`b`) VALUES (..." -> [Tabelle, Spalten|null, Rest nach VALUES] oder null. */
|
|
function osticket_parse_header(string $line): ?array
|
|
{
|
|
if (!preg_match('/^INSERT\s+(?:IGNORE\s+)?INTO\s+`?([A-Za-z0-9_]+)`?\s*(?:\(([^)]*)\))?\s*VALUES\s*(.*)$/s', $line, $m)) return null;
|
|
$cols = isset($m[2]) && $m[2] !== '' ? array_map(fn($c) => trim($c, " `\t"), explode(',', $m[2])) : null;
|
|
return [$m[1], $cols, $m[3]];
|
|
}
|
|
|
|
// ─── Zustand ─────────────────────────────────────────────────────────────────
|
|
|
|
function osticket_state_dir(): string
|
|
{
|
|
$dir = sys_get_temp_dir() . '/deskhub_import_' . preg_replace('/[^a-zA-Z0-9]/', '', session_id() ?: 'cli');
|
|
if (!is_dir($dir)) @mkdir($dir, 0700, true);
|
|
return $dir;
|
|
}
|
|
|
|
function osticket_state_empty(): array
|
|
{
|
|
return ['users' => [], 'emails' => [], 'tickets' => [], 'cdata' => [], 'thread_ticket' => [], 'ticket_thread' => [], 'collab' => [], 'first_resp' => [],
|
|
'departments' => [], 'topics' => [], 'staff' => [], 'prios' => [], 'statuses' => [], 'att' => [], 'files' => [], 'inline_att' => 0,
|
|
'entry_count' => 0, 'chunk_start' => null, 'ctx' => null, 'done' => false, 'maps' => null];
|
|
}
|
|
|
|
function osticket_state_load(string $dir): array
|
|
{
|
|
$f = $dir . '/state.json';
|
|
$st = is_file($f) ? json_decode((string) file_get_contents($f), true) : null;
|
|
return is_array($st) ? $st + osticket_state_empty() : osticket_state_empty();
|
|
}
|
|
|
|
function osticket_state_save(string $dir, array $st): void
|
|
{
|
|
file_put_contents($dir . '/state.json', json_encode($st, JSON_UNESCAPED_UNICODE | JSON_INVALID_UTF8_SUBSTITUTE), LOCK_EX);
|
|
}
|
|
|
|
function osticket_state_clear(string $dir): void
|
|
{
|
|
foreach (glob($dir . '/*') ?: [] as $f) @unlink($f);
|
|
@rmdir($dir);
|
|
}
|
|
|
|
// ─── Analyse ─────────────────────────────────────────────────────────────────
|
|
|
|
/** Liest einen Block der Datei ab $offset. Gibt ['offset' => neuer Offset, 'done' => bool, 'counts' => [...]] zurück. */
|
|
function osticket_analyze_step(string $sqlPath, string $dir, int $offset, int $maxBytes = 4 * 1024 * 1024): array
|
|
{
|
|
if ($offset === 0) { foreach (['state.json', 'entries.jsonl', 'entry_map.jsonl'] as $f) @unlink($dir . '/' . $f); }
|
|
$st = osticket_state_load($dir);
|
|
$fh = fopen($sqlPath, 'rb');
|
|
if (!$fh) throw new RuntimeException('Die SQL-Datei konnte nicht geöffnet werden.');
|
|
fseek($fh, $offset);
|
|
$entries = fopen($dir . '/entries.jsonl', 'ab');
|
|
$tables = osticket_tables();
|
|
$read = 0;
|
|
$ctx = $st['ctx']; // ['key' => Tabellenschlüssel|null, 'cols' => Spalten]
|
|
|
|
while (!feof($fh) && $read < $maxBytes) {
|
|
$lineStart = $offset + $read;
|
|
$line = fgets($fh, 64 * 1024 * 1024);
|
|
if ($line === false) break;
|
|
$read += strlen($line);
|
|
|
|
if ($line[0] === 'I' && str_starts_with($line, 'INSERT')) {
|
|
$h = osticket_parse_header(rtrim($line));
|
|
$ctx = null;
|
|
if (!$h) continue;
|
|
$key = osticket_table_key($h[0]);
|
|
if ($key === null) continue;
|
|
if ($key === 'file_chunk') { $st['chunk_start'] ??= $lineStart; continue; }
|
|
$ctx = ['key' => $key, 'cols' => $h[1] ?? $tables[$key]];
|
|
$rest = $h[2];
|
|
if ($rest !== '') osticket_consume($st, $ctx, $rest, $entries);
|
|
if (str_ends_with(rtrim($line), ';')) $ctx = null;
|
|
continue;
|
|
}
|
|
if ($ctx === null) continue;
|
|
if ($line[0] !== '(') { $ctx = null; continue; }
|
|
osticket_consume($st, $ctx, $line, $entries);
|
|
if (substr(rtrim($line), -1) === ';') $ctx = null;
|
|
}
|
|
$newOffset = $offset + $read;
|
|
$done = feof($fh) || $line === false;
|
|
fclose($fh);
|
|
fclose($entries);
|
|
$st['ctx'] = $done ? null : $ctx;
|
|
if ($done) $st['done'] = true;
|
|
osticket_state_save($dir, $st);
|
|
return ['offset' => $newOffset, 'done' => $done, 'counts' => osticket_counts($st)];
|
|
}
|
|
|
|
/** Verarbeitet die Datensätze einer Zeile für die aktuelle Tabelle. */
|
|
function osticket_consume(array &$st, array $ctx, string $text, $entriesFh): void
|
|
{
|
|
$key = $ctx['key'];
|
|
$cols = $ctx['cols'];
|
|
$nc = count($cols);
|
|
foreach (osticket_rows($text) as $vals) {
|
|
if (count($vals) !== $nc) continue;
|
|
$r = array_combine($cols, $vals);
|
|
switch ($key) {
|
|
case 'user':
|
|
$st['users'][$r['id']] = ['n' => (string) ($r['name'] ?? ''), 'd' => (int) ($r['default_email_id'] ?? 0)];
|
|
break;
|
|
case 'user_email':
|
|
$st['emails'][$r['id']] = [(int) $r['user_id'], (string) $r['address']];
|
|
break;
|
|
case 'ticket':
|
|
$st['tickets'][$r['ticket_id']] = ['n' => (string) $r['number'], 'u' => (int) $r['user_id'], 's' => (int) $r['status_id'], 'd' => (int) $r['dept_id'], 't' => (int) $r['topic_id'],
|
|
'a' => (int) $r['staff_id'], 'src' => (string) ($r['source'] ?? ''), 'c' => $r['created'], 'cl' => $r['closed'] ?? null, 'lu' => $r['lastupdate'] ?? null,
|
|
'od' => (int) ($r['isoverdue'] ?? 0), 'ip' => (string) ($r['ip_address'] ?? '')];
|
|
break;
|
|
case 'ticket__cdata':
|
|
$st['cdata'][$r['ticket_id']] = [(string) ($r['subject'] ?? ''), (int) ($r['priority'] ?? 0)];
|
|
break;
|
|
case 'thread':
|
|
if (($r['object_type'] ?? '') === 'T') { $st['thread_ticket'][$r['id']] = (int) $r['object_id']; $st['ticket_thread'][$r['object_id']] = (int) $r['id']; }
|
|
break;
|
|
case 'thread_collaborator':
|
|
if ((int) $r['user_id'] > 0) $st['collab'][$r['thread_id']][] = (int) $r['user_id'];
|
|
break;
|
|
case 'thread_entry':
|
|
$st['entry_count']++;
|
|
if (($r['type'] ?? '') === 'R') {
|
|
$tid = (string) $r['thread_id'];
|
|
if (!isset($st['first_resp'][$tid]) || strcmp((string) $r['created'], $st['first_resp'][$tid]) < 0) $st['first_resp'][$tid] = (string) $r['created'];
|
|
}
|
|
fwrite($entriesFh, json_encode(['id' => (int) $r['id'], 'th' => (int) $r['thread_id'], 'st' => (int) $r['staff_id'], 'us' => (int) $r['user_id'], 'ty' => (string) $r['type'],
|
|
'po' => (string) ($r['poster'] ?? ''), 'b' => (string) ($r['body'] ?? ''), 'f' => (string) ($r['format'] ?? 'html'), 'c' => $r['created']], JSON_UNESCAPED_UNICODE | JSON_INVALID_UTF8_SUBSTITUTE) . "\n");
|
|
break;
|
|
case 'department':
|
|
$st['departments'][$r['id']] = (string) $r['name'];
|
|
break;
|
|
case 'help_topic':
|
|
$st['topics'][$r['topic_id']] = ['n' => (string) $r['topic'], 'd' => (int) $r['dept_id'], 'p' => (int) ($r['priority_id'] ?? 0), 'on' => (int) ($r['isactive'] ?? 1), 'pub' => (int) ($r['ispublic'] ?? 1)];
|
|
break;
|
|
case 'staff':
|
|
$st['staff'][$r['staff_id']] = ['n' => trim(($r['firstname'] ?? '') . ' ' . ($r['lastname'] ?? '')), 'e' => strtolower((string) ($r['email'] ?? ''))];
|
|
break;
|
|
case 'ticket_priority':
|
|
$st['prios'][$r['priority_id']] = ['n' => (string) ($r['priority'] ?? ''), 'd' => (string) ($r['priority_desc'] ?? ''), 'u' => (int) ($r['priority_urgency'] ?? 0)];
|
|
break;
|
|
case 'ticket_status':
|
|
$st['statuses'][$r['id']] = ['n' => (string) $r['name'], 's' => (string) ($r['state'] ?? '')];
|
|
break;
|
|
case 'attachment':
|
|
if (($r['type'] ?? '') === 'H') {
|
|
if ((int) ($r['inline'] ?? 0) === 1) $st['inline_att']++;
|
|
else $st['att'][$r['object_id']][] = [(int) $r['file_id'], $r['name'] === null ? null : (string) $r['name']];
|
|
}
|
|
break;
|
|
case 'file':
|
|
$st['files'][$r['id']] = ['n' => (string) $r['name'], 't' => (string) ($r['type'] ?? ''), 's' => (int) ($r['size'] ?? 0)];
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
|
|
/** E-Mail-Adresse und Name je osTicket-Kunde. */
|
|
function osticket_contacts(array $st): array
|
|
{
|
|
$byUser = [];
|
|
foreach ($st['emails'] as $id => [$uid, $addr]) $byUser[$uid][(int) $id] = $addr;
|
|
$out = [];
|
|
foreach ($st['users'] as $uid => $u) {
|
|
$addr = $byUser[(int) $uid][$u['d']] ?? (isset($byUser[(int) $uid]) ? reset($byUser[(int) $uid]) : '');
|
|
$out[(int) $uid] = ['name' => $u['n'], 'email' => trim((string) $addr)];
|
|
}
|
|
// Adressen ohne passenden Kundeneintrag trotzdem verfügbar machen
|
|
foreach ($byUser as $uid => $list) if (!isset($out[$uid])) $out[$uid] = ['name' => '', 'email' => trim((string) reset($list))];
|
|
return $out;
|
|
}
|
|
|
|
/** Status eines osTicket-Tickets -> DeskHub-Status oder null (gelöschte Tickets werden nicht importiert). */
|
|
function osticket_status(array $st, int $statusId): ?string
|
|
{
|
|
$s = $st['statuses'][$statusId] ?? null;
|
|
if ($s) {
|
|
$state = strtolower($s['s']);
|
|
if ($state === 'deleted') return null;
|
|
if ($state === 'open') return 'open';
|
|
if ($state === 'archived') return 'closed';
|
|
if ($state === 'closed') return preg_match('/gelöst|geloest|resolved/i', $s['n']) ? 'resolved' : 'closed';
|
|
}
|
|
return [1 => 'open', 2 => 'resolved', 3 => 'closed', 4 => 'closed'][$statusId] ?? ($statusId === 5 ? null : 'closed');
|
|
}
|
|
|
|
function osticket_counts(array $st): array
|
|
{
|
|
$tickets = 0;
|
|
foreach ($st['tickets'] as $t) if (osticket_status($st, $t['s']) !== null) $tickets++;
|
|
$attFiles = [];
|
|
foreach ($st['att'] as $list) foreach ($list as [$fid]) $attFiles[$fid] = $st['files'][$fid]['s'] ?? 0;
|
|
return ['users' => count(array_filter(osticket_contacts($st), fn($c) => $c['email'] !== '')), 'departments' => count($st['departments']), 'topics' => count($st['topics']),
|
|
'tickets' => $tickets, 'threads' => (int) $st['entry_count'], 'attachments' => count($attFiles), 'attachment_bytes' => array_sum($attFiles)];
|
|
}
|