| Server IP : 202.61.199.114 / Your IP : 216.73.217.139 Web Server : nginx/1.22.1 System : Linux de.arni-solutions.de 6.1.0-49-amd64 #1 SMP PREEMPT_DYNAMIC Debian 6.1.174-1 (2026-05-26) x86_64 User : web20 ( 1018) PHP Version : 8.4.23 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : OFF | Sudo : ON | Pkexec : ON Directory : /var/www/clients/client1/seaside.pacim.de/web/ |
Upload File : |
<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
// Ab hier der geschützte Bereich der Seite
require_once 'includes/config.inc.php';
require_once 'includes/functions/import-manager.php';
// Prüfen, ob das Formular abgesendet wurde
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
if (isset($_POST['password']) && $_POST['password'] === LOGIN_PASS) {
setcookie('authenticated', 'true', time() + 3600);
header('Location: ' . $_SERVER['PHP_SELF']);
exit;
} else {
$error = "Falsches Passwort!";
}
}
// Prüfen, ob der Benutzer bereits authentifiziert ist
if (!isset($_COOKIE['authenticated'])) {
?>
<!DOCTYPE html>
<html lang="de">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Login</title>
<link href="/assets/css/bootstrap.min.css" rel="stylesheet">
<script src="/assets/js/jquery-3.6.0.min.js"></script>
<script src="/assets/js/bootstrap.min.js"></script>
</head>
<body class="bg-light">
<div class="container">
<div class="row justify-content-center">
<div class="col-md-6">
<div class="card mt-5">
<div class="card-header">
<h3 class="text-center">Login erforderlich</h3>
</div>
<div class="card-body">
<?php if (isset($error)): ?>
<div class="alert alert-danger" role="alert">
<?= htmlspecialchars($error) ?>
</div>
<?php endif; ?>
<form method="post" action="">
<div class="form-group">
<label for="password">Passwort:</label>
<input type="password" id="password" name="password" class="form-control" required>
</div>
<button type="submit" class="btn btn-primary btn-block mt-3">Einloggen</button>
</form>
</div>
</div>
</div>
</div>
</div>
</body>
</html>
<?php
exit;
}
// Datenbankverbindung aufbauen
$db = new MysqliDb(MYSQL_HOST, MYSQL_USER, MYSQL_PASS, MYSQL_DB);
/**
* Hilfsfunktion zum Extrahieren des Schiffnamens aus dem Dateinamen
*/
function extractShipName($filename)
{
$parts = explode('-', $filename);
return isset($parts[1]) ? trim($parts[1]) : 'Unbekanntes Schiff';
}
/**
* PHPExcel laden
*/
function loadExcelLibrary()
{
if (class_exists('PHPExcel_IOFactory')) {
return true;
}
$possibleFiles = array(
__DIR__ . '/Classes/PHPExcel/IOFactory.php',
__DIR__ . '/PHPExcel/IOFactory.php',
__DIR__ . '/includes/PHPExcel/IOFactory.php',
__DIR__ . '/vendor/phpoffice/phpexcel/Classes/PHPExcel/IOFactory.php',
dirname(__DIR__) . '/Classes/PHPExcel/IOFactory.php',
dirname(__DIR__) . '/vendor/phpoffice/phpexcel/Classes/PHPExcel/IOFactory.php',
);
foreach ($possibleFiles as $file) {
if (is_file($file)) {
require_once $file;
if (class_exists('PHPExcel_IOFactory')) {
return true;
}
}
}
return false;
}
/**
* Baut wie in deinem funktionierenden Viewer den echten Dateipfad:
* file_path + file_name
*/
function buildImportFileFullPath($filePath, $fileName)
{
$filePath = trim((string)$filePath);
$fileName = trim((string)$fileName);
if ($fileName === '') {
return false;
}
$candidates = array();
$combined = rtrim($filePath, '/\\') . DIRECTORY_SEPARATOR . $fileName;
$candidates[] = $combined;
$candidates[] = __DIR__ . DIRECTORY_SEPARATOR . ltrim($combined, '/\\');
if (!empty($_SERVER['DOCUMENT_ROOT'])) {
$candidates[] = rtrim($_SERVER['DOCUMENT_ROOT'], '/\\') . DIRECTORY_SEPARATOR . ltrim($combined, '/\\');
}
// Fallback wie in deinem bestehenden Leser
$candidates[] = $filePath . $fileName;
$candidates[] = __DIR__ . DIRECTORY_SEPARATOR . ltrim($filePath . $fileName, '/\\');
if (!empty($_SERVER['DOCUMENT_ROOT'])) {
$candidates[] = rtrim($_SERVER['DOCUMENT_ROOT'], '/\\') . DIRECTORY_SEPARATOR . ltrim($filePath . $fileName, '/\\');
}
foreach ($candidates as $candidate) {
if ($candidate && file_exists($candidate) && is_readable($candidate)) {
return $candidate;
}
}
return false;
}
/**
* Zellwert wie im funktionierenden Leser formatiert holen
*/
function getFormattedCellValue($cell)
{
try {
$value = $cell->getFormattedValue();
} catch (Exception $e) {
$value = $cell->getValue();
}
if (is_object($value) && method_exists($value, '__toString')) {
$value = (string)$value;
}
if (is_array($value) || is_object($value)) {
$value = '';
}
return trim((string)$value);
}
/**
* String für unscharfe Suche normalisieren
* z.B. EE-DF-861 => eedf861
* EE DF 861 => eedf861
* EEDF861 => eedf861
*/
function normalizeSearchString($value)
{
$value = trim((string)$value);
$value = mb_strtolower($value, 'UTF-8');
$replace = array(
'ä' => 'ae',
'ö' => 'oe',
'ü' => 'ue',
'ß' => 'ss',
);
$value = strtr($value, $replace);
// alles außer Buchstaben und Zahlen raus
$value = preg_replace('/[^[:alnum:]]+/u', '', $value);
return $value;
}
/**
* Suchstring in Tokens zerlegen
* Beispiel:
* "Uwe HH UD 4823" => ["Uwe", "HH", "UD", "4823"]
*/
function tokenizeSearchString($searchTerm)
{
$searchTerm = trim((string)$searchTerm);
if ($searchTerm === '') {
return array();
}
$parts = preg_split('/\s+/u', $searchTerm, -1, PREG_SPLIT_NO_EMPTY);
$tokens = array();
foreach ($parts as $part) {
$part = trim($part);
if ($part !== '') {
$tokens[] = $part;
}
}
return $tokens;
}
/**
* Prüft, ob Zelle Suchbegriff enthält
* - normale Suche
* - normalisierte Suche ohne Leerzeichen, Bindestriche usw.
*/
function cellMatchesSearch($cellValue, $searchTerm)
{
$cellValue = (string)$cellValue;
$searchTerm = trim((string)$searchTerm);
if ($searchTerm === '') {
return false;
}
if (mb_stripos($cellValue, $searchTerm, 0, 'UTF-8') !== false) {
return true;
}
$normalizedCell = normalizeSearchString($cellValue);
$normalizedSearch = normalizeSearchString($searchTerm);
if ($normalizedSearch === '') {
return false;
}
return mb_stripos($normalizedCell, $normalizedSearch, 0, 'UTF-8') !== false;
}
/**
* Prüft, ob die gesamte Zeile zum Suchstring passt
*
* Regeln:
* - Wenn kompletter Suchstring in einer Zelle vorkommt => Treffer
* - Sonst werden Such-Tokens gebildet
* - Jeder Token muss irgendwo in derselben Zeile vorkommen
* - Vergleich auch normalisiert
*/
function rowMatchesSearch(array $rowValues, $searchTerm)
{
$searchTerm = trim((string)$searchTerm);
if ($searchTerm === '') {
return false;
}
foreach ($rowValues as $value) {
if (cellMatchesSearch($value, $searchTerm)) {
return true;
}
}
$tokens = tokenizeSearchString($searchTerm);
if (empty($tokens)) {
return false;
}
$normalizedRowValues = array();
$plainRowValues = array();
foreach ($rowValues as $value) {
$plainRowValues[] = (string)$value;
$normalizedRowValues[] = normalizeSearchString($value);
}
foreach ($tokens as $token) {
$tokenFound = false;
$normalizedToken = normalizeSearchString($token);
foreach ($plainRowValues as $idx => $plainValue) {
if (mb_stripos($plainValue, $token, 0, 'UTF-8') !== false) {
$tokenFound = true;
break;
}
if ($normalizedToken !== '' && mb_stripos($normalizedRowValues[$idx], $normalizedToken, 0, 'UTF-8') !== false) {
$tokenFound = true;
break;
}
}
if (!$tokenFound) {
return false;
}
}
return true;
}
/**
* Header-Name normalisieren für Vergleich
*/
function normalizeHeaderName($value)
{
return normalizeSearchString($value);
}
/**
* Index einer Spalte über mehrere mögliche Headernamen finden
*/
function findHeaderIndex(array $headers, array $possibleNames)
{
$normalizedPossible = array();
foreach ($possibleNames as $name) {
$normalizedPossible[] = normalizeHeaderName($name);
}
foreach ($headers as $index => $header) {
$normalizedHeader = normalizeHeaderName($header);
if (in_array($normalizedHeader, $normalizedPossible, true)) {
return (int)$index;
}
}
return null;
}
/**
* Letzte Summenzeile ignorieren:
* wenn nur in Spalte 1 Inhalt steht und alle anderen leer sind
*/
function isSummaryRowLike(array $rowValues)
{
$hasFirst = false;
$hasOther = false;
foreach ($rowValues as $index => $value) {
$value = trim((string)$value);
if ($index === 0) {
if ($value !== '') {
$hasFirst = true;
}
} else {
if ($value !== '') {
$hasOther = true;
break;
}
}
}
return $hasFirst && !$hasOther;
}
/**
* Gruppierungsschlüssel aus Booking No, Cabin Number, Departure Date, Arrival Date
* Wenn eins davon fehlt, false zurückgeben
*/
function buildGroupedFingerprintByBusinessFields(array $headers, array $rowValues)
{
$bookingIndex = findHeaderIndex($headers, array('Booking No', 'Booking Number'));
$cabinIndex = findHeaderIndex($headers, array('Cabin Number', 'Cabin No'));
$departureIndex = findHeaderIndex($headers, array('Departure Date', 'Departure'));
$arrivalIndex = findHeaderIndex($headers, array('Arrival Date', 'Arrival'));
if ($bookingIndex === null || $cabinIndex === null || $departureIndex === null || $arrivalIndex === null) {
return false;
}
$booking = isset($rowValues[$bookingIndex]) ? normalizeSearchString($rowValues[$bookingIndex]) : '';
$cabin = isset($rowValues[$cabinIndex]) ? normalizeSearchString($rowValues[$cabinIndex]) : '';
$departure = isset($rowValues[$departureIndex]) ? normalizeSearchString($rowValues[$departureIndex]) : '';
$arrival = isset($rowValues[$arrivalIndex]) ? normalizeSearchString($rowValues[$arrivalIndex]) : '';
if ($booking === '' || $cabin === '' || $departure === '' || $arrival === '') {
return false;
}
return md5('grp|' . $booking . '|' . $cabin . '|' . $departure . '|' . $arrival);
}
function extractResultTravelDate(array $headers, array $rowValues)
{
$departureIndex = findHeaderIndex($headers, array('Departure Date', 'Departure'));
$arrivalIndex = findHeaderIndex($headers, array('Arrival Date', 'Arrival'));
$dateValue = '';
if ($departureIndex !== null && !empty($rowValues[$departureIndex])) {
$dateValue = $rowValues[$departureIndex];
} elseif ($arrivalIndex !== null && !empty($rowValues[$arrivalIndex])) {
$dateValue = $rowValues[$arrivalIndex];
}
$dateValue = trim((string)$dateValue);
if ($dateValue === '') {
return null;
}
$formats = array(
'd.m.Y',
'd.m.y',
'Y-m-d',
'd/m/Y',
'd/m/y'
);
foreach ($formats as $format) {
$dt = DateTime::createFromFormat($format, $dateValue);
if ($dt instanceof DateTime) {
return $dt->format('Y-m-d');
}
}
$timestamp = strtotime($dateValue);
if ($timestamp !== false) {
return date('Y-m-d', $timestamp);
}
return null;
}
/**
* Fallback-Fingerprint auf kompletter Zeile
*/
function buildRowFingerprint(array $values)
{
$normalized = array();
foreach ($values as $value) {
$value = trim((string)$value);
$value = preg_replace('/\s+/u', ' ', $value);
$normalized[] = mb_strtolower($value, 'UTF-8');
}
return md5(implode('|', $normalized));
}
/**
* Liefert Original-Zeichenarray, normalisierten Text und Mapping
* Mapping: Position im normalisierten String -> Position im Original-Zeichenarray
*/
function buildNormalizedTextMap($text)
{
$chars = preg_split('//u', (string)$text, -1, PREG_SPLIT_NO_EMPTY);
$normalizedText = '';
$map = array();
foreach ($chars as $i => $char) {
$normalizedChar = normalizeSearchString($char);
if ($normalizedChar === '') {
continue;
}
$normChars = preg_split('//u', $normalizedChar, -1, PREG_SPLIT_NO_EMPTY);
foreach ($normChars as $nc) {
$normalizedText .= $nc;
$map[] = $i;
}
}
return array($chars, $normalizedText, $map);
}
/**
* Liefert Highlight-Ranges für einen einzelnen Needle in einem Text
* Unterstützt auch normalisierte Treffer
*/
function getHighlightRangesForNeedle($text, $needle)
{
$needle = trim((string)$needle);
if ($needle === '') {
return array();
}
list($chars, $normalizedText, $map) = buildNormalizedTextMap($text);
$normalizedNeedle = normalizeSearchString($needle);
if ($normalizedText === '' || $normalizedNeedle === '') {
return array();
}
$ranges = array();
$offset = 0;
$needleLength = mb_strlen($normalizedNeedle, 'UTF-8');
while (true) {
$start = mb_stripos($normalizedText, $normalizedNeedle, $offset, 'UTF-8');
if ($start === false) {
break;
}
$end = $start + $needleLength - 1;
if (isset($map[$start]) && isset($map[$end])) {
$origStart = $map[$start];
$origEnd = $map[$end];
$ranges[] = array($origStart, $origEnd);
}
$offset = $start + 1;
}
return $ranges;
}
/**
* Überlappende Ranges zusammenführen
*/
function mergeHighlightRanges(array $ranges)
{
if (empty($ranges)) {
return array();
}
usort($ranges, function ($a, $b) {
if ($a[0] === $b[0]) {
return $a[1] <=> $b[1];
}
return $a[0] <=> $b[0];
});
$merged = array();
$current = $ranges[0];
for ($i = 1, $count = count($ranges); $i < $count; $i++) {
$range = $ranges[$i];
if ($range[0] <= $current[1] + 1) {
if ($range[1] > $current[1]) {
$current[1] = $range[1];
}
} else {
$merged[] = $current;
$current = $range;
}
}
$merged[] = $current;
return $merged;
}
/**
* Highlight für eine Zelle
* - kompletter Suchstring
* - zusätzlich alle Tokens einzeln
* So werden bei zellenübergreifender Suche alle passenden Teile hervorgehoben
*/
function highlightSearchTerm($text, $searchTerm)
{
$text = (string)$text;
$searchTerm = trim((string)$searchTerm);
if ($searchTerm === '') {
return htmlspecialchars($text, ENT_QUOTES, 'UTF-8');
}
$needles = array($searchTerm);
$tokens = tokenizeSearchString($searchTerm);
foreach ($tokens as $token) {
if ($token !== '') {
$needles[] = $token;
}
}
$needles = array_values(array_unique($needles));
$allRanges = array();
foreach ($needles as $needle) {
if (cellMatchesSearch($text, $needle)) {
$allRanges = array_merge($allRanges, getHighlightRangesForNeedle($text, $needle));
}
}
if (empty($allRanges)) {
return htmlspecialchars($text, ENT_QUOTES, 'UTF-8');
}
$ranges = mergeHighlightRanges($allRanges);
$chars = preg_split('//u', $text, -1, PREG_SPLIT_NO_EMPTY);
$out = '';
$rangeIndex = 0;
$currentRange = isset($ranges[0]) ? $ranges[0] : null;
foreach ($chars as $i => $char) {
while ($currentRange !== null && $i > $currentRange[1]) {
$rangeIndex++;
$currentRange = isset($ranges[$rangeIndex]) ? $ranges[$rangeIndex] : null;
}
$isHighlighted = ($currentRange !== null && $i >= $currentRange[0] && $i <= $currentRange[1]);
if ($isHighlighted) {
if ($i === $currentRange[0]) {
$out .= '<mark>';
}
$out .= htmlspecialchars($char, ENT_QUOTES, 'UTF-8');
if ($i === $currentRange[1]) {
$out .= '</mark>';
}
} else {
$out .= htmlspecialchars($char, ENT_QUOTES, 'UTF-8');
}
}
return $out;
}
/**
* Excel-Datei durchsuchen
* - Zeile 1 = Titel
* - Zeile 2 = Spaltenüberschriften
* - Suche ab Zeile 3
* - letzte Summenzeile ignorieren, wenn nur erste Spalte befüllt ist
*/
function searchInExcelFile($filePath, $fileName, $searchTerm, $maxMatchesPerFile = 100)
{
$results = array();
$resolvedPath = buildImportFileFullPath($filePath, $fileName);
if ($resolvedPath === false) {
return $results;
}
if (!loadExcelLibrary()) {
return $results;
}
try {
$excelReader = PHPExcel_IOFactory::createReaderForFile($resolvedPath);
$excelObj = $excelReader->load($resolvedPath);
$worksheet = $excelObj->getActiveSheet();
$highestColumn = $worksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
// Zeile 2 = sichtbare Tabellenüberschriften
$headers = array();
for ($col = 0; $col < $highestColumnIndex; $col++) {
$headerCell = $worksheet->getCellByColumnAndRow($col, 2);
$headerValue = getFormattedCellValue($headerCell);
$headers[$col] = ($headerValue !== '') ? $headerValue : ('Spalte ' . ($col + 1));
}
$rowCount = 0;
$matchCounter = 0;
$searchTerm = trim((string)$searchTerm);
foreach ($worksheet->getRowIterator() as $row) {
$rowCount++;
// Zeile 1 = Titel
// Zeile 2 = Header
// Daten ab Zeile 3
if ($rowCount <= 2) {
continue;
}
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false);
$rowValues = array();
$hasContent = false;
$colIndex = 0;
foreach ($cellIterator as $cell) {
$displayValue = getFormattedCellValue($cell);
$rowValues[$colIndex] = $displayValue;
if ($displayValue !== '') {
$hasContent = true;
}
$colIndex++;
}
for ($i = $colIndex; $i < $highestColumnIndex; $i++) {
$rowValues[$i] = '';
}
if (!$hasContent) {
continue;
}
// Letzte Summenzeile / Einspalten-Zeile ignorieren
if (isSummaryRowLike($rowValues)) {
continue;
}
// komplette Zeile prüfen
if (rowMatchesSearch($rowValues, $searchTerm)) {
$groupedFingerprint = buildGroupedFingerprintByBusinessFields($headers, $rowValues);
$fingerprint = ($groupedFingerprint !== false)
? $groupedFingerprint
: buildRowFingerprint($rowValues);
$results[] = array(
'row_number' => $rowCount,
'headers' => $headers,
'values' => $rowValues,
'fingerprint' => $fingerprint,
);
$matchCounter++;
if ($matchCounter >= $maxMatchesPerFile) {
break;
}
}
}
$excelObj->disconnectWorksheets();
unset($excelObj);
} catch (Exception $e) {
return $results;
}
return $results;
}
function formatImportFileLabel($fileName)
{
$name = basename((string)$fileName);
$date = '';
$ship = '';
$type = '';
$code = '';
if (preg_match('/^(\d{2}\.\d{2}\.\d{4})\s*-\s*([^-]+)\s*-\s*(.+)$/u', $name, $m)) {
$date = trim($m[1]);
$ship = trim($m[2]);
}
$type = '';
$parking = '';
// Parkplatztyp separat erkennen
if (preg_match('/\b(Außen|Aussen|Halle)\b/iu', $name, $m)) {
$parkingRaw = mb_strtolower($m[1], 'UTF-8');
if ($parkingRaw === 'aussen') {
$parking = 'Außen';
} else {
$parking = ucfirst($parkingRaw);
}
}
// Valet erkennen
if (preg_match('/\bValet(parken)?\b/iu', $name)) {
if ($parking !== '') {
$type = 'VALET ' . $parking;
} else {
$type = 'VALET';
}
} else {
// Kein Valet → normaler Parkplatztyp
if ($parking !== '') {
$type = $parking;
}
}
if (preg_match('/(?<![A-Z0-9])(PW[0-9]{2,}[A-Z0-9]*)(?![A-Z0-9])/iu', $name, $m)) {
$code = strtoupper($m[1]);
}
$parts = array();
if ($date !== '') {
$parts[] = $date;
}
if ($ship !== '') {
$parts[] = $ship;
}
if ($type !== '') {
$parts[] = $type;
}
if ($code !== '') {
$parts[] = $code;
}
if (!empty($parts)) {
return implode(' · ', $parts);
}
return $name;
}
/**
* Gruppiertes Ergebnis rendern
*/
function renderFileSearchResult(array $group, $searchTerm)
{
$html = '';
$file = $group['file'];
$html .= '<div class="mb-5">';
$html .= ' <div class="mb-2">';
$html .= ' <div class="font-weight-bold">' . count($group['rows']) . ' Treffer in Datei</div>';
$fileMd5 = isset($file['file_md5']) ? $file['file_md5'] : '';
$downloadUrl = '/file.php?token=YIz8HqUPWQ7cwcVHE57O&type=download&file=' . urlencode($fileMd5);
$fileLabel = formatImportFileLabel($file['file_name']);
$bookingNoIndex = findHeaderIndex($group['headers'], array('Booking No', 'Booking Number'));
$parkingBookingsIndex = findHeaderIndex($group['headers'], array('Parking Bookings', 'N° of Parking Bookings'));
$cruiseIdIndex = findHeaderIndex($group['headers'], array('Cruise ID', 'CruiseID'));
$cabinIndex = findHeaderIndex($group['headers'], array('Cabin Number', 'Cabin No'));
$html .= '<div class="file-actions mb-2">';
$html .= '<a href="#" class="btn-link file-link" '
. 'data-file-path="' . htmlspecialchars($file['file_path'], ENT_QUOTES, 'UTF-8') . '" '
. 'data-file-name="' . htmlspecialchars($file['file_name'], ENT_QUOTES, 'UTF-8') . '" '
. 'data-file-id="' . (int)$file['file_id'] . '">'
. htmlspecialchars($fileLabel, ENT_QUOTES, 'UTF-8')
. '</a>';
if ($fileMd5 !== '') {
$html .= '<a href="' . htmlspecialchars($downloadUrl, ENT_QUOTES, 'UTF-8') . '" '
. 'class="text-muted small btn-link ml-2" target="_blank">'
. 'Download'
. '</a>';
}
$html .= '</div>';
$html .= ' </div>';
$html .= ' <div class="p-0">';
$html .= ' <div class="table-responsive">';
$html .= ' <table class="table table-sm table-bordered table-striped mb-0">';
$html .= ' <thead class="thead-light"><tr>';
foreach ($group['headers'] as $header) {
$html .= '<th>' . htmlspecialchars($header, ENT_QUOTES, 'UTF-8') . '</th>';
}
$html .= ' </tr></thead>';
$html .= ' <tbody>';
foreach ($group['rows'] as $row) {
$html .= '<tr>';
foreach ($row['values'] as $colIndex => $value) {
$cellHtml = highlightSearchTerm($value, $searchTerm);
$clickValue = null;
// Booking No (nur wenn Parking Bookings vorhanden)
if (
$bookingNoIndex !== null &&
$parkingBookingsIndex !== null &&
(int)$colIndex === (int)$bookingNoIndex
) {
$parkingBookingValue = isset($row['values'][$parkingBookingsIndex])
? trim((string)$row['values'][$parkingBookingsIndex])
: '';
if ($parkingBookingValue !== '') {
$clickValue = trim((string)$value);
}
}
// Cruise ID
if (
$cruiseIdIndex !== null &&
(int)$colIndex === (int)$cruiseIdIndex
) {
$clickValue = trim((string)$value);
}
// Cabin Number
if (
$cabinIndex !== null &&
(int)$colIndex === (int)$cabinIndex
) {
$clickValue = trim((string)$value);
}
// Link nur einmal setzen
if ($clickValue !== null && $clickValue !== '') {
$cellHtml = '<a href="#" class="quick-search-link" '
. 'data-search="' . htmlspecialchars($clickValue, ENT_QUOTES, 'UTF-8') . '">'
. $cellHtml
. '</a>';
}
$html .= '<td>' . $cellHtml . '</td>';
}
$html .= '</tr>';
}
$html .= ' </tbody>';
$html .= ' </table>';
$html .= ' </div>';
$html .= ' </div>';
$html .= '</div>';
return $html;
}
/**
* Live-Suche in Import-Dateien der letzten 14 Tage
* gleiche Datensätze anhand Booking No + Cabin Number + Departure Date + Arrival Date zusammenfassen
* sonst Fallback auf komplette Zeile
*/
function performLiveImportSearch(MysqliDb $db, $searchTerm)
{
$searchTerm = trim((string)$searchTerm);
if ($searchTerm === '') {
return array(
'count' => 0,
'html' => '<div class="alert alert-secondary mb-0">Bitte Suchbegriff eingeben.</div>'
);
}
$fromDate = date('Y-m-d 00:00:00', strtotime('-14 days'));
$files = $db->where('file_category', 'aida_bookings')
->where('file_datetime', array('>=' => $fromDate))
->orderBy('file_datetime', 'desc')
->get('pacim_files', null, 'file_id, file_name, file_path, file_datetime, file_size, file_md5');
$fileGroups = array();
$globalUniqueLimit = 500;
$uniqueFingerprints = array();
foreach ($files as $file) {
$matches = searchInExcelFile($file['file_path'], $file['file_name'], $searchTerm, 200);
if (empty($matches)) {
continue;
}
foreach ($matches as $match) {
$fingerprint = $match['fingerprint'];
// gleiche Person/Buchung nicht doppelt aus derselben Datei aufnehmen
$uniqueKey = (int)$file['file_id'] . '_' . $fingerprint;
if (isset($uniqueFingerprints[$uniqueKey])) {
continue;
}
$uniqueFingerprints[$uniqueKey] = true;
$fileId = (int)$file['file_id'];
$travelDate = extractResultTravelDate($match['headers'], $match['values']);
$today = date('Y-m-d');
if (!isset($fileGroups[$fileId])) {
$fileGroups[$fileId] = array(
'file' => array(
'file_id' => $fileId,
'file_name' => $file['file_name'],
'file_path' => $file['file_path'],
'file_datetime' => $file['file_datetime'],
'file_md5' => isset($file['file_md5']) ? $file['file_md5'] : '',
),
'headers' => $match['headers'],
'rows' => array(),
'best_date' => $travelDate,
'has_future' => ($travelDate !== null && $travelDate >= $today) ? 1 : 0,
'file_datetime' => $file['file_datetime'],
);
}
$fileGroups[$fileId]['rows'][] = array(
'row_number' => (int)$match['row_number'],
'values' => $match['values'],
'travel_date' => $travelDate,
'is_future' => ($travelDate !== null && $travelDate >= $today) ? 1 : 0,
);
if ($travelDate !== null) {
if ($fileGroups[$fileId]['best_date'] === null || $travelDate > $fileGroups[$fileId]['best_date']) {
$fileGroups[$fileId]['best_date'] = $travelDate;
}
}
if (($travelDate !== null && $travelDate >= $today)) {
$fileGroups[$fileId]['has_future'] = 1;
}
if (count($uniqueFingerprints) >= $globalUniqueLimit) {
break 2;
}
}
}
if (empty($fileGroups)) {
return array(
'count' => 0,
'html' => '<div class="alert alert-warning mb-0">Keine Treffer in den letzten 14 Tagen gefunden.</div>'
);
}
uasort($fileGroups, function ($a, $b) {
// Dateien mit Zukunftstreffern zuerst
if ($a['has_future'] !== $b['has_future']) {
return $b['has_future'] <=> $a['has_future'];
}
// Danach spätestes Reisedatum DESC
if ($a['best_date'] !== $b['best_date']) {
return strcmp((string)$b['best_date'], (string)$a['best_date']);
}
// Danach neueste Datei zuerst
return strcmp((string)$b['file_datetime'], (string)$a['file_datetime']);
});
// Zeilen innerhalb jeder Datei wie in Excel sortieren
foreach ($fileGroups as $fileId => $group) {
usort($fileGroups[$fileId]['rows'], function ($a, $b) {
return $a['row_number'] <=> $b['row_number'];
});
}
$html = '';
$totalRows = 0;
foreach ($fileGroups as $group) {
$totalRows += count($group['rows']);
$html .= renderFileSearchResult($group, $searchTerm);
}
return array(
'count' => $totalRows,
'html' => $html
);
}
// AJAX Live-Suche
if (isset($_GET['ajax']) && $_GET['ajax'] === 'live_import_search') {
header('Content-Type: application/json; charset=utf-8');
$term = isset($_GET['q']) ? (string)$_GET['q'] : '';
$result = performLiveImportSearch($db, $term);
echo json_encode($result);
exit;
}
// Importe nach Datum gruppieren und anzeigen
$dateien = $db->where('file_category', 'aida_bookings')
->where('file_datetime', array('>=' => date('Y-m-d', strtotime('-6 weeks'))))
->orderBy('file_datetime', 'desc')
->get('pacim_files');
// Ein Array vorbereiten, um die Dateien nach Datum zu gruppieren
$groupedFiles = array();
foreach ($dateien as $datei) {
$date = date('Y-m-d', strtotime($datei['file_datetime']));
$groupedFiles[$date][] = $datei;
}
// POST-Anfrage verarbeiten, um Importe rückgängig zu machen
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
if (isset($_COOKIE['authenticated'])) {
if (isset($_POST['file_id']) && !isset($_POST['action'])) {
$fileId = (int)$_POST['file_id'];
undoImportsByFileIds($db, array($fileId));
}
if (isset($_POST['action']) && $_POST['action'] === 'undo_day' && !empty($_POST['date'])) {
$date = $_POST['date'];
$fileIds = $db->where('file_category', 'aida_bookings')
->where('file_datetime', array($date . ' 00:00:00', $date . ' 23:59:59'), 'BETWEEN')
->getValue('pacim_files', 'file_id', null);
undoImportsByFileIds($db, $fileIds ?: array());
}
if (isset($_POST['action']) && $_POST['action'] === 'undo_day_ship' && !empty($_POST['date']) && !empty($_POST['ship'])) {
$date = $_POST['date'];
$ship = $_POST['ship'];
$files = $db->where('file_category', 'aida_bookings')
->where('file_datetime', array($date . ' 00:00:00', $date . ' 23:59:59'), 'BETWEEN')
->get('pacim_files', null, 'file_id, file_name');
$fileIds = array();
foreach ($files as $f) {
if (extractShipName($f['file_name']) === $ship) {
$fileIds[] = (int)$f['file_id'];
}
}
undoImportsByFileIds($db, $fileIds);
}
}
}
?>
<!DOCTYPE html>
<html lang="de">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Letzte Importe</title>
<link href="/assets/css/bootstrap.min.css" rel="stylesheet">
<script src="/assets/js/jquery-3.6.0.min.js"></script>
<script src="/assets/js/bootstrap.min.js"></script>
<script src="/assets/js/import-manager.js"></script>
<style>
.modal-dialog {
width: 90%;
max-width: 90%;
height: 90%;
margin: auto;
margin-top: 5%;
}
.modal-content {
height: 100%;
}
.modal-body {
height: calc(100% - 56px);
overflow-y: auto;
}
.overlay-link {
position: fixed;
bottom: 20px;
right: 20px;
z-index: 1050;
}
body {
padding-top: 70px;
}
.file-actions {
line-height: 1.4;
}
.file-actions a {
text-decoration: none;
}
.file-actions a:hover {
text-decoration: underline;
}
.search-sticky-box {
position: fixed;
top: 0;
left: 0;
right: 0;
z-index: 2000;
background: #fff;
border-bottom: 1px solid #ddd;
padding: 10px 20px;
}
.search-bar {
max-width: 900px;
margin: 0 auto;
}
.search-input {
border-radius: 30px;
padding: 10px 15px;
border: 1px solid #ccc;
}
.search-input:focus {
box-shadow: 0 0 0 2px rgba(0,123,255,.15);
border-color: #007bff;
}
#live-search-results .table td,
#live-search-results .table th {
white-space: nowrap;
vertical-align: top;
}
#live-search-results mark {
background: #ffe082;
color: #000;
padding: 0 .15em;
border-radius: .2rem;
}
.search-loader {
display: none;
}
</style>
</head>
<body class="bg-light">
<a href="https://seaside.pacim.de/mobile/calendar.php?token=nico" target="_blank" class="btn btn-primary overlay-link">Anreisen</a>
<?php echo renderAidaImportButton(); ?>
<div class="search-sticky-box">
<div class="search-bar d-flex align-items-center">
<input
type="text"
id="live-import-search"
class="form-control search-input"
placeholder="Suche: Name, Buchung, Kennzeichen ..."
autocomplete="off"
>
<button type="button" id="clear-live-import-search" class="btn btn-link text-muted px-2">
✕
</button>
<div class="spinner-border spinner-border-sm text-primary ml-2 search-loader" id="live-search-loader"></div>
<div id="live-search-status" class="ml-3 small text-muted"></div>
</div>
</div>
<div class="container mt-1">
<div id="live-search-wrapper" class="mb-4" style="display:none;">
<div class="h3 mb-4" id="live-search-header">
0 Suchergebnisse
</div>
<div id="live-search-results"></div>
</div>
<div id="default-import-list">
<?php foreach ($groupedFiles as $date => $files): ?>
<?php
$db->where('import_datetime', $date . '%', 'LIKE');
$importDayCount = $db->getValue('pacim_import', 'count(import_id)');
?>
<div class="card mb-4">
<div class="card-header d-flex justify-content-between align-items-center">
<h5 class="mb-0">
<?= htmlspecialchars(date("d.m.Y", strtotime($date))) ?>
<small class="ml-2"><?= (int)$importDayCount; ?> Fahrzeuge importiert</small>
</h5>
<?php if ($importDayCount > 0): ?>
<form class="js-undo"
data-action="undo_day"
data-date="<?= htmlspecialchars($date) ?>"
data-confirm="Alle Importe vom <?= htmlspecialchars(date('d.m.Y', strtotime($date))) ?> rückgängig machen? Danach bekommst du die Shell-Befehle angezeigt.">
<button type="submit" class="btn btn-light text-danger btn-sm">Alles rückgängig</button>
</form>
<?php endif; ?>
</div>
<?php
$shipNames = array();
foreach ($files as $file) {
$shipName = extractShipName($file['file_name']);
$shipNames[$shipName][] = $file;
}
if (count($shipNames) > 1):
?>
<ul class="nav nav-tabs" id="tab-<?= htmlspecialchars($date) ?>" role="tablist">
<li class="nav-item">
<a class="nav-link active" id="all-tab-<?= htmlspecialchars($date) ?>" data-toggle="tab" href="#all-<?= htmlspecialchars($date) ?>" role="tab" aria-controls="all" aria-selected="true">Alle</a>
</li>
<?php foreach ($shipNames as $shipName => $shipFiles): ?>
<li class="nav-item">
<a class="nav-link" id="<?= htmlspecialchars($shipName) ?>-tab-<?= htmlspecialchars($date) ?>" data-toggle="tab" href="#<?= htmlspecialchars($shipName) ?>-<?= htmlspecialchars($date) ?>" role="tab" aria-controls="<?= htmlspecialchars($shipName) ?>" aria-selected="false"><?= htmlspecialchars($shipName) ?></a>
</li>
<?php endforeach; ?>
</ul>
<div class="tab-content">
<div class="tab-pane fade show active" id="all-<?= htmlspecialchars($date) ?>" role="tabpanel" aria-labelledby="all-tab-<?= htmlspecialchars($date) ?>">
<ul class="list-group list-group-flush">
<?php foreach ($files as $file): ?>
<?php
$db->where('import_file_id', $file['file_id']);
$importCount = $db->getValue('pacim_import', 'count(import_id)');
?>
<li class="list-group-item">
<a href="#" class="file-link" data-file-path="<?= htmlspecialchars($file['file_path']) ?>" data-file-name="<?= htmlspecialchars($file['file_name']) ?>" data-file-id="<?= htmlspecialchars($file['file_id']) ?>">
<?= htmlspecialchars($file['file_name']) ?>
<small style="color:#888;">
(<?= $importCount > 0 ? $importCount . " Imports, " : '' ?><?= htmlspecialchars($file['file_size']) ?> Bytes)
</small>
</a>
<?php
$db->where('import_file_id', $file['file_id']);
$hasImports = $db->has('pacim_import');
if ($hasImports):
?>
<form class="d-inline-block ml-3 js-undo"
data-action="undo_file"
data-file-id="<?= (int)$file['file_id'] ?>"
data-file-name="<?= htmlspecialchars($file['file_name']) ?>"
data-confirm="Import von "<?= htmlspecialchars($file['file_name']) ?>" rückgängig machen? Danach bekommst du den Shell-Befehl angezeigt.">
<button type="submit" class="btn btn-danger btn-sm">Import Rückgängig</button>
</form>
<?php endif; ?>
</li>
<?php endforeach; ?>
</ul>
</div>
<?php foreach ($shipNames as $shipName => $shipFiles): ?>
<?php
$totalShipImports = 0;
foreach ($shipFiles as $sf) {
$db->where('import_file_id', $sf['file_id']);
$totalShipImports += (int)$db->getValue('pacim_import', 'count(import_id)');
}
?>
<div class="tab-pane fade" id="<?= htmlspecialchars($shipName) ?>-<?= htmlspecialchars($date) ?>" role="tabpanel" aria-labelledby="<?= htmlspecialchars($shipName) ?>-tab-<?= htmlspecialchars($date) ?>">
<?php if ($totalShipImports > 0): ?>
<div class="p-3">
<form class="d-inline-block js-undo"
data-action="undo_day_ship"
data-date="<?= htmlspecialchars($date) ?>"
data-ship="<?= htmlspecialchars($shipName) ?>"
data-confirm="Alle Importe am <?= htmlspecialchars(date('d.m.Y', strtotime($date))) ?> für <?= htmlspecialchars($shipName) ?> rückgängig machen? Danach bekommst du die Shell-Befehle angezeigt.">
<button type="submit" class="btn btn-outline-danger btn-sm"><?= htmlspecialchars($shipName) ?>-Import rückgängig machen</button>
</form>
</div>
<?php endif; ?>
<ul class="list-group list-group-flush">
<?php foreach ($shipFiles as $file): ?>
<?php
$db->where('import_file_id', $file['file_id']);
$importCount = $db->getValue('pacim_import', 'count(import_id)');
?>
<li class="list-group-item">
<a href="#" class="file-link" data-file-path="<?= htmlspecialchars($file['file_path']) ?>" data-file-name="<?= htmlspecialchars($file['file_name']) ?>" data-file-id="<?= htmlspecialchars($file['file_id']) ?>">
<?= htmlspecialchars($file['file_name']) ?>
<small style="color:#888;">
(<?= $importCount > 0 ? $importCount . " Imports, " : '' ?><?= htmlspecialchars($file['file_size']) ?> Bytes)
</small>
</a>
<?php
$db->where('import_file_id', $file['file_id']);
$hasImports = $db->has('pacim_import');
if ($hasImports):
?>
<form class="d-inline-block ml-3 js-undo"
data-action="undo_file"
data-file-id="<?= (int)$file['file_id'] ?>"
data-file-name="<?= htmlspecialchars($file['file_name']) ?>"
data-confirm="Import von "<?= htmlspecialchars($file['file_name']) ?>" rückgängig machen? Danach bekommst du den Shell-Befehl angezeigt.">
<button type="submit" class="btn btn-danger btn-sm">Import Rückgängig</button>
</form>
<?php endif; ?>
</li>
<?php endforeach; ?>
</ul>
</div>
<?php endforeach; ?>
</div>
<?php else: ?>
<ul class="list-group list-group-flush">
<?php foreach ($files as $file): ?>
<?php
$db->where('import_file_id', $file['file_id']);
$importCount = $db->getValue('pacim_import', 'count(import_id)');
?>
<li class="list-group-item">
<a href="#" class="file-link" data-file-path="<?= htmlspecialchars($file['file_path']) ?>" data-file-name="<?= htmlspecialchars($file['file_name']) ?>" data-file-id="<?= htmlspecialchars($file['file_id']) ?>">
<?= htmlspecialchars($file['file_name']) ?>
<small style="color:#888;">
(<?= $importCount > 0 ? $importCount . " Imports, " : '' ?><?= htmlspecialchars($file['file_size']) ?> Bytes)
</small>
</a>
<?php
$db->where('import_file_id', $file['file_id']);
$hasImports = $db->has('pacim_import');
if ($hasImports):
?>
<form class="d-inline-block ml-3 js-undo"
data-action="undo_file"
data-file-id="<?= (int)$file['file_id'] ?>"
data-file-name="<?= htmlspecialchars($file['file_name']) ?>"
data-confirm="Import von "<?= htmlspecialchars($file['file_name']) ?>" rückgängig machen? Danach bekommst du den Shell-Befehl angezeigt.">
<button type="submit" class="btn btn-danger btn-sm">Import Rückgängig</button>
</form>
<?php endif; ?>
</li>
<?php endforeach; ?>
</ul>
<?php endif; ?>
</div>
<?php endforeach; ?>
</div>
</div>
<?php
require_once("includes/modals/html_excel.php");
require_once("includes/modals/html_shell.php");
?>
<script>
(function($) {
var searchTimer = null;
var $input = $('#live-import-search');
var $clear = $('#clear-live-import-search');
var $wrapper = $('#live-search-wrapper');
var $header = $('#live-search-header');
var $results = $('#live-search-results');
var $status = $('#live-search-status');
var $loader = $('#live-search-loader');
var $defaultList = $('#default-import-list');
function runLiveSearch() {
var query = $.trim($input.val());
if (query.length === 0) {
$wrapper.hide();
$header.text('0 Suchergebnisse');
$results.html('');
$status.text('');
$loader.hide();
$defaultList.show();
return;
}
$loader.show();
$status.text('Suche läuft ...');
$wrapper.show();
$defaultList.hide();
$.ajax({
url: '<?= htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') ?>',
type: 'GET',
dataType: 'json',
data: {
ajax: 'live_import_search',
q: query
},
success: function(response) {
$loader.hide();
if (response && typeof response.html !== 'undefined') {
var count = parseInt(response.count || 0, 10);
$header.text(count + ' Suchergebnisse');
$results.html(response.html);
$status.text(count + ' eindeutige Treffer gefunden');
} else {
$header.text('0 Suchergebnisse');
$results.html('<div class="alert alert-danger mb-0">Ungültige Serverantwort.</div>');
$status.text('Fehler bei der Suche');
}
},
error: function() {
$loader.hide();
$header.text('0 Suchergebnisse');
$results.html('<div class="alert alert-danger mb-0">Fehler bei der Live-Suche.</div>');
$status.text('Fehler bei der Suche');
}
});
}
$(document).on('click', '.quick-search-link', function(e) {
e.preventDefault();
var value = $(this).data('search') || '';
$input.val(value);
runLiveSearch();
$('html, body').animate({
scrollTop: 0
}, 200);
});
$input.on('input', function() {
clearTimeout(searchTimer);
searchTimer = setTimeout(runLiveSearch, 300);
});
$clear.on('click', function() {
$input.val('').trigger('input').focus();
});
})(jQuery);
</script>
</body>
</html>