' . nl2br(e($segment)) . '
'; } return implode('', $parts) ?: nl2br(e($body)); } if ($format === 'html') return osticket_html_to_text($body); return preg_replace('/\n{3,}/', "\n\n", trim(preg_replace('/\r\n?/', "\n", $body))); } /** Datum aus dem Dump prüfen; sonst Ersatzwert. */ function osticket_date(?string $d, ?string $fallback = null): ?string { return $d !== null && preg_match('/^\d{4}-\d{2}-\d{2}( \d{2}:\d{2}:\d{2})?/', $d) && !str_starts_with($d, '0000') ? $d : $fallback; } // ─── Zuordnungen (Abteilungen, Themen, Mitarbeiter, Prioritäten) ───────────── /** * Baut die Zuordnung osTicket-ID -> DeskHub-ID. Mit $create legt sie fehlende Abteilungen und Hilfethemen an, sonst * werden nur vorhandene (gleicher Name) gefunden. Das Ergebnis wird im Zustand gespeichert. * * @return array{dept:array, topic:array, staff:array, prio:array, created:array{departments:int, topics:int}} */ function osticket_maps(PDO $pdo, array &$st, bool $create): array { $maps = ['dept' => [], 'topic' => [], 'staff' => [], 'prio' => [], 'created' => ['departments' => 0, 'topics' => 0]]; $defaultDept = (int) (get_setting('default_department_id') ?: 0); if (!$defaultDept) $defaultDept = (int) $pdo->query('SELECT id FROM departments ORDER BY is_default DESC, id LIMIT 1')->fetchColumn(); $maps['default_dept'] = $defaultDept; // Prioritäten: erst über den Namen, sonst über die Rangfolge (dringendste zuerst) $dh = $pdo->query('SELECT id, name, resolution_hours FROM priorities ORDER BY resolution_hours, id')->fetchAll(); $maps['default_prio'] = (int) ($pdo->query("SELECT id FROM priorities WHERE LOWER(name) IN ('normal','mittel') ORDER BY id LIMIT 1")->fetchColumn() ?: ($dh[(int) floor((count($dh) - 1) / 2)]['id'] ?? 1)); $ost = $st['prios']; uasort($ost, fn($a, $b) => ($a['u'] ?: 99) <=> ($b['u'] ?: 99)); $rank = 0; $n = max(1, count($ost)); foreach ($ost as $id => $p) { $match = null; foreach ($dh as $d) if (strcasecmp($d['name'], $p['d']) === 0 || strcasecmp($d['name'], $p['n']) === 0) { $match = (int) $d['id']; break; } $maps['prio'][(int) $id] = $match ?? (int) ($dh[(int) round($rank * (count($dh) - 1) / max(1, $n - 1))]['id'] ?? $maps['default_prio']); $rank++; } // Abteilungen $byName = []; foreach ($pdo->query('SELECT id, name FROM departments')->fetchAll() as $d) $byName[mb_strtolower($d['name'])] = (int) $d['id']; foreach ($st['departments'] as $id => $name) { $key = mb_strtolower(trim((string) $name)); if ($key === '') continue; if (isset($byName[$key])) { $maps['dept'][(int) $id] = $byName[$key]; continue; } if ($create) { $new = department_save($pdo, 0, ['name' => trim((string) $name)]); $byName[$key] = $new; $maps['dept'][(int) $id] = $new; $maps['created']['departments']++; } } // Hilfethemen $topicByName = []; foreach ($pdo->query('SELECT id, name FROM help_topics')->fetchAll() as $t) $topicByName[mb_strtolower($t['name'])] = (int) $t['id']; foreach ($st['topics'] as $id => $t) { $key = mb_strtolower(trim($t['n'])); if ($key === '') continue; if (isset($topicByName[$key])) { $maps['topic'][(int) $id] = $topicByName[$key]; continue; } if ($create) { $new = help_topic_save($pdo, 0, ['name' => trim($t['n']), 'department_id' => $maps['dept'][$t['d']] ?? $defaultDept, 'priority_id' => $maps['prio'][$t['p']] ?? $maps['default_prio'], 'is_active' => $t['on'] ? 1 : 0, 'is_public' => $t['pub'] ? 1 : 0]); $topicByName[$key] = $new; $maps['topic'][(int) $id] = $new; $maps['created']['topics']++; } } // Mitarbeiter über die E-Mail-Adresse $staffByEmail = []; foreach ($pdo->query('SELECT id, email FROM staff')->fetchAll() as $s) $staffByEmail[strtolower((string) $s['email'])] = (int) $s['id']; foreach ($st['staff'] as $id => $s) if ($s['e'] !== '' && isset($staffByEmail[$s['e']])) $maps['staff'][(int) $id] = $staffByEmail[$s['e']]; $st['maps'] = $maps; return $maps; } // ─── Durchgänge ────────────────────────────────────────────────────────────── /** * Führt einen Schritt eines Durchgangs aus. * * @return array{inserted:int, skipped:int, processed:int, next_offset:int, done:bool, notes:array} */ function osticket_import_step(PDO $pdo, string $dir, string $pass, int $offset, string $sqlPath = ''): array { @set_time_limit(300); $st = osticket_state_load($dir); $r = ['inserted' => 0, 'skipped' => 0, 'processed' => 0, 'next_offset' => $offset, 'done' => false, 'notes' => []]; switch ($pass) { case 'users': osticket_pass_users($pdo, $st, $offset, $r); break; case 'structure': $maps = osticket_maps($pdo, $st, true); $r['inserted'] = $maps['created']['departments'] + $maps['created']['topics']; $r['notes'][] = "{$maps['created']['departments']} Abteilung(en) und {$maps['created']['topics']} Hilfethema(en) angelegt, vorhandene gleichen Namens wiederverwendet."; $r['done'] = true; break; case 'tickets': osticket_pass_tickets($pdo, $st, $offset, $r); break; case 'threads': osticket_pass_threads($pdo, $st, $dir, $offset, $r); break; case 'attachments': osticket_pass_attachments($pdo, $st, $dir, $sqlPath, $offset, $r); break; default: throw new InvalidArgumentException('Unbekannter Durchgang.'); } osticket_state_save($dir, $st); return $r; } function osticket_pass_users(PDO $pdo, array &$st, int $offset, array &$r): void { $contacts = osticket_contacts($st); $ids = array_keys($contacts); $slice = array_slice($ids, $offset, OSTICKET_CHUNK); $pw = password_hash(bin2hex(random_bytes(8)), PASSWORD_DEFAULT); $chk = $pdo->prepare('SELECT id FROM users WHERE LOWER(email) = LOWER(?)'); $ins = $pdo->prepare('INSERT INTO users (name, email, password_hash, created_at) VALUES (?,?,?,?)'); foreach ($slice as $uid) { $c = $contacts[$uid]; if ($c['email'] === '' || !str_contains($c['email'], '@')) { $r['skipped']++; continue; } $chk->execute([$c['email']]); if ($chk->fetchColumn()) { $r['skipped']++; continue; } $ins->execute([mb_substr(osticket_fallback_name($c['email'], $c['name']), 0, 120), mb_substr($c['email'], 0, 150), $pw, db_now()]); $r['inserted']++; } $r['processed'] = count($slice); $r['next_offset'] = $offset + count($slice); $r['done'] = count($slice) < OSTICKET_CHUNK; } function osticket_pass_tickets(PDO $pdo, array &$st, int $offset, array &$r): void { $maps = $st['maps'] ?? osticket_maps($pdo, $st, false); $contacts = osticket_contacts($st); $prios = []; foreach ($pdo->query('SELECT * FROM priorities')->fetchAll() as $p) $prios[(int) $p['id']] = $p; $keys = array_keys($st['tickets']); $slice = array_slice($keys, $offset, OSTICKET_CHUNK); $chk = $pdo->prepare('SELECT id FROM tickets WHERE ticket_number = ?'); $getUser = $pdo->prepare('SELECT id FROM users WHERE LOWER(email) = LOWER(?)'); $ins = $pdo->prepare('INSERT INTO tickets (ticket_number, subject, user_id, requester_name, requester_email, department_id, help_topic_id, assigned_staff_id, priority_id, status, source, requester_ip, first_response_due_at, first_responded_at, resolution_due_at, resolved_at, closed_at, is_overdue, created_at, updated_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'); $sources = ['web' => 'web', 'email' => 'email', 'phone' => 'phone', 'api' => 'api']; foreach ($slice as $tid) { $t = $st['tickets'][$tid]; $status = osticket_status($st, $t['s']); if ($status === null) { $r['skipped']++; $r['notes']['deleted'] = 'Gelöschte osTicket-Tickets wurden nicht importiert.'; continue; } if ($t['n'] === '') { $r['skipped']++; continue; } $chk->execute([$t['n']]); if ($chk->fetchColumn()) { $r['skipped']++; $r['notes']['existing'] = 'Bereits vorhandene Ticketnummern wurden übersprungen.'; continue; } $contact = $contacts[$t['u']] ?? ['name' => '', 'email' => '']; $email = $contact['email']; $name = mb_substr(osticket_fallback_name($email, $contact['name']), 0, 120); $userId = null; if ($email !== '') { $getUser->execute([$email]); $userId = $getUser->fetchColumn() ?: null; } $cdata = $st['cdata'][$tid] ?? ['', 0]; $subject = mb_substr(trim($cdata[0]) !== '' ? trim($cdata[0]) : 'Ticket #' . $t['n'], 0, 255); $deptId = $maps['dept'][$t['d']] ?? $maps['default_dept']; $topicId = $maps['topic'][$t['t']] ?? null; $staffId = $maps['staff'][$t['a']] ?? null; if ($t['a'] > 0 && !$staffId) $r['unassigned'] = ($r['unassigned'] ?? 0) + 1; $prioId = $maps['prio'][$cdata[1]] ?? $maps['default_prio']; $created = osticket_date($t['c'], db_now()); $updated = osticket_date($t['lu'], $created); $closedAt = in_array($status, ['resolved', 'closed'], true) ? osticket_date($t['cl'], $updated) : null; $threadId = $st['ticket_thread'][$tid] ?? null; $firstResp = $threadId !== null ? osticket_date($st['first_resp'][(string) $threadId] ?? null) : null; $firstDue = $resDue = null; if (!in_array($status, ['resolved', 'closed'], true) && isset($prios[$prioId])) { $sla = calc_sla_dates($prios[$prioId], $created, $deptId, $topicId); $firstDue = $firstResp ? null : $sla['first_response_due_at']; $resDue = $sla['resolution_due_at']; } $ins->execute([$t['n'], $subject, $userId, $name, mb_substr($email, 0, 150), $deptId, $topicId, $staffId, $prioId, $status, $sources[strtolower($t['src'])] ?? 'web', $t['ip'] !== '' ? mb_substr($t['ip'], 0, 45) : null, $firstDue, $firstResp, $resDue, $closedAt, $closedAt, ($t['od'] && $closedAt === null) ? 1 : 0, $created, $updated]); $r['inserted']++; } if (!empty($r['unassigned'])) $r['notes']['unassigned'] = $r['unassigned'] . ' Ticket(s) waren Mitarbeitern zugewiesen, die es in DeskHub nicht gibt (Zuordnung über gleiche E-Mail-Adresse). Sie sind jetzt unzugewiesen.'; unset($r['unassigned']); $r['processed'] = count($slice); $r['next_offset'] = $offset + count($slice); $r['done'] = count($slice) < OSTICKET_CHUNK; } function osticket_pass_threads(PDO $pdo, array &$st, string $dir, int $offset, array &$r): void { $maps = $st['maps'] ?? osticket_maps($pdo, $st, false); $contacts = osticket_contacts($st); $file = $dir . '/entries.jsonl'; $fh = is_file($file) ? fopen($file, 'rb') : null; $lines = 0; $end = true; $ticketByNumber = $pdo->prepare('SELECT id FROM tickets WHERE ticket_number = ?'); $getUser = $pdo->prepare('SELECT id FROM users WHERE LOWER(email) = LOWER(?)'); $exists = $pdo->prepare('SELECT id FROM ticket_threads WHERE ticket_id = ? AND created_at = ? AND author_type = ? AND message = ? LIMIT 1'); $ins = $pdo->prepare('INSERT INTO ticket_threads (ticket_id, author_type, staff_id, user_id, message, is_internal_note, created_at) VALUES (?,?,?,?,?,?,?)'); $entryMap = $fh ? fopen($dir . '/entry_map.jsonl', 'ab') : null; $ticketCache = []; $lastTouched = []; if ($fh) { fseek($fh, $offset); while ($lines < OSTICKET_CHUNK && ($line = fgets($fh)) !== false) { $lines++; $e = json_decode($line, true); if (!is_array($e)) { $r['skipped']++; continue; } $osTid = $st['thread_ticket'][(string) $e['th']] ?? null; $number = $osTid !== null ? ($st['tickets'][(string) $osTid]['n'] ?? null) : null; if ($number === null || $number === '') { $r['skipped']++; continue; } if (!array_key_exists($number, $ticketCache)) { $ticketByNumber->execute([$number]); $ticketCache[$number] = (int) $ticketByNumber->fetchColumn() ?: null; } $dhTid = $ticketCache[$number]; if (!$dhTid) { $r['skipped']++; $r['notes']['no_ticket'] = 'Nachrichten zu nicht importierten Tickets wurden übersprungen.'; continue; } $type = strtoupper($e['ty']); $staffId = $e['st'] > 0 ? ($maps['staff'][$e['st']] ?? null) : null; $userId = null; $internal = 0; if ($type === 'N') { $author = $staffId ? 'staff' : 'system'; $internal = 1; } elseif ($e['st'] > 0 || $type === 'R') { $author = 'staff'; } else { $author = 'customer'; $email = $contacts[$e['us']]['email'] ?? ''; if ($email !== '') { $getUser->execute([$email]); $userId = $getUser->fetchColumn() ?: null; } } if (($e['st'] > 0 || $type === 'R') && !$staffId) $r['notes']['staff'] = 'Antworten von Mitarbeitern, die es in DeskHub nicht gibt (gleiche E-Mail-Adresse nötig), wurden ohne Mitarbeiter übernommen.'; $hasAtt = !empty($st['att'][(string) $e['id']]); $body = normalize_osticket_body($e['b'], $e['f'], $author); if (trim(strip_tags($body)) === '') { if (!$hasAtt) { $r['skipped']++; continue; } $body = $author === 'customer' ? '(Nachricht ohne Text, siehe Anhang)' : '(Nachricht ohne Text, siehe Anhang)
'; } $created = osticket_date($e['c'], db_now()); $exists->execute([$dhTid, $created, $author, $body]); $threadId = $exists->fetchColumn(); if (!$threadId) { $ins->execute([$dhTid, $author, $staffId, $userId, $body, $internal, $created]); $threadId = (int) $pdo->lastInsertId(); $r['inserted']++; } else { $r['skipped']++; } if ($hasAtt && $entryMap) fwrite($entryMap, json_encode([(int) $e['id'], (int) $threadId]) . "\n"); } $r['next_offset'] = ftell($fh); $end = feof($fh) || fgets($fh) === false; fclose($fh); if ($entryMap) fclose($entryMap); } $r['processed'] = $lines; $r['done'] = $end; if ($end) { // zum Schluss: Mitlesende (Collaborators) $addedCollab = 0; $existsC = $pdo->prepare('SELECT COUNT(*) FROM ticket_collaborators WHERE ticket_id = ? AND LOWER(email) = LOWER(?)'); $insC = $pdo->prepare('INSERT INTO ticket_collaborators (ticket_id, email, name, created_at) VALUES (?,?,?,?)'); foreach ($st['collab'] as $threadId => $uids) { $osTid = $st['thread_ticket'][(string) $threadId] ?? null; $number = $osTid !== null ? ($st['tickets'][(string) $osTid]['n'] ?? null) : null; if (!$number) continue; $ticketByNumber->execute([$number]); $dhTid = (int) $ticketByNumber->fetchColumn(); if (!$dhTid) continue; foreach (array_unique($uids) as $uid) { $c = $contacts[$uid] ?? null; if (!$c || $c['email'] === '') continue; $existsC->execute([$dhTid, $c['email']]); if ((int) $existsC->fetchColumn() > 0) continue; $insC->execute([$dhTid, $c['email'], mb_substr(osticket_fallback_name($c['email'], $c['name']), 0, 120), db_now()]); $addedCollab++; } } if ($addedCollab) $r['notes']['collab'] = "{$addedCollab} Mitlesende übernommen."; } } /** Dateiendung für gespeicherte Anhänge: nur bekannte, ungefährliche Typen, alles andere wird .bin. */ function osticket_safe_extension(string $name): string { $ext = strtolower(pathinfo($name, PATHINFO_EXTENSION)); $safe = ['png', 'jpg', 'jpeg', 'gif', 'webp', 'bmp', 'pdf', 'txt', 'log', 'csv', 'rtf', 'doc', 'docx', 'xls', 'xlsx', 'ppt', 'pptx', 'odt', 'ods', 'odp', 'zip', 'eml', 'msg', 'mp3', 'mp4', 'mov', 'heic']; return in_array($ext, $safe, true) ? $ext : 'bin'; } function osticket_pass_attachments(PDO $pdo, array &$st, string $dir, string $sqlPath, int $offset, array &$r): void { // Welche Dateien werden gebraucht? (nur Anhänge von Nachrichten, die importiert wurden und nicht zu groß sind) $wanted = []; foreach ($st['att'] as $entryId => $list) foreach ($list as [$fileId]) if (isset($st['files'][$fileId]) && $st['files'][$fileId]['s'] <= OSTICKET_ATTACHMENT_MAX_BYTES) $wanted[$fileId] = true; $parts = $dir . '/files'; if (!is_dir($parts)) @mkdir($parts, 0700, true); if (!$wanted || $st['chunk_start'] === null || $sqlPath === '' || !is_file($sqlPath)) { $r['notes'][] = $wanted ? 'In der Datei wurden keine Anhangsdaten gefunden.' : 'Keine Anhänge zum Übernehmen.'; $r['done'] = true; return; } $start = $offset > 0 ? $offset : (int) $st['chunk_start']; $fh = fopen($sqlPath, 'rb'); fseek($fh, $start); $read = 0; $ended = false; $inChunks = $offset > 0; while (!feof($fh) && $read < 16 * 1024 * 1024) { $line = fgets($fh, 64 * 1024 * 1024); if ($line === false) break; $read += strlen($line); $text = null; if ($line[0] === '(') { $text = $line; } elseif (str_starts_with($line, 'INSERT')) { $h = osticket_parse_header(rtrim($line)); if (!$h || osticket_table_key($h[0]) !== 'file_chunk') { $ended = true; break; } $inChunks = true; $text = $h[2]; // mysqldump: die Datensätze stehen in derselben Zeile wie INSERT } if ($text !== null && $text !== '') { foreach (osticket_rows($text) as $row) { if (count($row) < 3) continue; $fileId = (int) $row[0]; if (!isset($wanted[$fileId])) continue; $raw = (string) $row[2]; $data = str_starts_with($raw, '0x') ? (strlen($raw) > 2 ? (string) hex2bin(strlen($raw) % 2 ? '0' . substr($raw, 2) : substr($raw, 2)) : '') : $raw; file_put_contents($parts . '/' . $fileId . '.part', $data, FILE_APPEND); } continue; } if (str_starts_with($line, 'CREATE') || str_starts_with($line, 'ALTER') || str_starts_with($line, 'COMMIT')) { $ended = $inChunks || $ended || true; break; } } $next = $start + $read; $atEof = feof($fh); fclose($fh); $r['processed'] = 1; $r['next_offset'] = $next; if (!$ended && !$atEof) return; // Fertig mit dem Lesen: Dateien in den Upload-Ordner legen und mit den Nachrichten verknüpfen $entryThread = []; $mapFile = $dir . '/entry_map.jsonl'; if (is_file($mapFile)) foreach (file($mapFile, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES) ?: [] as $l) { $p = json_decode($l, true); if (is_array($p)) $entryThread[$p[0]] = $p[1]; } $uploads = rtrim((string) config()['uploads']['path'], '/' . chr(92)); if (!is_dir($uploads)) @mkdir($uploads, 0755, true); $ins = $pdo->prepare('INSERT INTO ticket_attachments (thread_id, original_name, stored_name, filesize, created_at) VALUES (?,?,?,?,?)'); $dup = $pdo->prepare('SELECT id FROM ticket_attachments WHERE thread_id = ? AND original_name = ? AND filesize = ? LIMIT 1'); $broken = 0; $tooBig = 0; $noThread = 0; foreach ($st['att'] as $entryId => $list) { $threadId = $entryThread[(int) $entryId] ?? null; foreach ($list as [$fileId, $attName]) { $meta = $st['files'][$fileId] ?? null; if (!$meta) continue; if ($meta['s'] > OSTICKET_ATTACHMENT_MAX_BYTES) { $tooBig++; continue; } if (!$threadId) { $noThread++; continue; } $part = $parts . '/' . $fileId . '.part'; if (!is_file($part) || filesize($part) !== $meta['s']) { $broken++; continue; } $orig = mb_substr(trim(basename(str_replace(chr(92), '/', ($attName !== null && $attName !== '' ? $attName : $meta['n']) ?: 'Anhang'))), 0, 255) ?: 'Anhang'; $dup->execute([$threadId, $orig, $meta['s']]); if ($dup->fetchColumn()) { $r['skipped']++; continue; } $stored = bin2hex(random_bytes(16)) . '.' . osticket_safe_extension($orig); if (!copy($part, $uploads . '/' . $stored)) { $broken++; continue; } $ins->execute([$threadId, $orig, $stored, $meta['s'], db_now()]); $r['inserted']++; } } foreach (glob($parts . '/*.part') ?: [] as $f) @unlink($f); if ($broken) $r['notes']['broken'] = "{$broken} Anhang/Anhänge unvollständig oder nicht lesbar und übersprungen."; if ($tooBig) $r['notes']['big'] = "{$tooBig} Anhang/Anhänge größer als " . (OSTICKET_ATTACHMENT_MAX_BYTES / 1048576) . ' MB übersprungen.'; if ($noThread) $r['notes']['nothread'] = "{$noThread} Anhang/Anhänge gehören zu Nachrichten, die nicht importiert wurden (Nachrichten zuerst importieren)."; $r['done'] = true; }