WKVS/www/intern/scripts/trainer/ajax-add-personen-excel.php

<?php

use Shuchkin\SimpleXLSX;

header('Content-Type: application/json');

if (!isset($baseDir))
    $baseDir = $_SERVER['DOCUMENT_ROOT'];

require_once $baseDir . '/../scripts/session_functions.php';

ini_wkvs_session();

check_user_permission('trainer');

verify_csrf();

require $baseDir . '/../scripts/db/db-functions.php';
require $baseDir . '/../scripts/db/db-tables.php';

require $baseDir . '/../composer/vendor/autoload.php';

$type = 'tr';

$dbconnection = require $baseDir . '/../scripts/db/db-verbindung-script.php';

if ($dbconnection['success'] !== true) {
    echo 'Critical DB Error.';
    exit;
}

if ($_FILES['xlsx_file']['error'] !== UPLOAD_ERR_OK) {
    echo json_encode(['success' => false, 'message' => 'Fehler beim Datei-Upload']);
    http_response_code(400);
    exit;
}

$tmpName = $_FILES['xlsx_file']['tmp_name'];

if (!class_exists('Shuchkin\\SimpleXLSX')) {
    echo json_encode(['success' => false, 'message' => 'Fehler beim Laden des Plugins']);
    http_response_code(400);
    exit;
}

$xlsx = SimpleXLSX::parse($tmpName);

$rows = $xlsx->rows();

$vereine_rows = db_select($mysqli, $tableVereine, 'verein', '', [], 'verein ASC');
$vereine = array_column($vereine_rows, 'verein');

if (count($rows) < 2) {
    echo json_encode(['success' => false, 'message' => 'Die Excel-Datei muss mindestens einen Eintrag enthalten']);
    http_response_code(400);
    exit;
}

$selectedverein = $_SESSION['selectedFreigabeTrainer'] ?? '';

$isAdmin = $selectedverein === "admin";

$columnMap = [
    'Nachname' => 'name',
    'Vorname' => 'vorname',
    'Geburtsdatum' => 'geburtsdatum',
    'Programm' => 'programm'
];

if ($isAdmin) {
    $columnMap['Verein'] = 'verein';
}

$header_row = false;

foreach ($rows as $row_index => $row) {
    $formated_row = array_map('trim', $row);

    $columnIndexes = [];

    $column_not_found = false;

    foreach ($columnMap as $excelHeader => $dbColumn) {
        $index = array_search($excelHeader, $formated_row);
        if ($index === false) {
            $column_not_found = true;
            break;
        }
        $columnIndexes[$dbColumn] = $index;
    }

    if ($column_not_found) {
        continue;
    } else {
        $header_row = $row_index;
        break;
    }
}

if (!$header_row) {
    echo json_encode(['success' => false, 'message' => 'Es wurde keine vollständige Header-Zeile gefunden']);
    http_response_code(400);
    exit;
}

for ($i = 0; $i <= (max($header_row, 1)); $i++) {
    unset($rows[$i]);
}

$programme_db = db_select($mysqli, $tableProgramme, 'programm', 'aktiv = ?', [1], 'programm ASC');

$programme = array_column($programme_db, 'programm');

$mysqli->begin_transaction();

$columns = ['name', 'vorname', 'geburtsdatum', 'programm', 'verein'];

$set = implode(', ', array_map(fn($col) => "$col = ?", $columns));

$sql = "INSERT INTO $tableTeilnehmende SET $set";

$stmt = $mysqli->prepare($sql);

$types = str_repeat('s', count($columns));

try {
    foreach ($rows as $row) {
        if (!array_filter($row))
            continue;

        $data = [];
        foreach ($columnIndexes as $dbCol => $excel_column_index) {
            $data[$dbCol] = isset($row[$excel_column_index]) ? trim($row[$excel_column_index]) : null;
        }

        if (!$isAdmin) {
            $data['verein'] = $selectedverein;
        } else {
            if (!in_array($data['verein'], $vereine)) {
                echo json_encode(['success' => false, 'message' => $data['verein'] . " existiert nicht im System"]);
                http_response_code(400);
                exit;
            }
        }


        $raw = trim($data['geburtsdatum']);

        $temp = DateTime::createFromFormat('d.m.Y', $raw);

        if ($temp && $temp->format('d.m.Y') === $raw) {
            $data['geburtsdatum'] = $temp->format('Y-m-d');
        } else {
            $data['geburtsdatum'] = substr($raw, 0, 10);
        }


        if (is_array($programme) && !(in_array($data['programm'], $programme))) {
            echo json_encode(['success' => false, 'message' => "Programm '{$data['programm']}' nicht valide bei " . $personEinzahl . " " . $data['name'] . " " . $data['vorname']]);
            http_response_code(400);
            exit;
        }

        $values = array_values($data);

        $stmt->bind_param($types, ...$values);

        if (!$stmt->execute()) {
            echo json_encode(['success' => false, 'message' => "Fehler beim Einfügen eines Datensatzes"]);
            http_response_code(400);
            exit;
        }

    }

    $stmt->close();

    $mysqli->commit();
} catch (Exception $e) {
    $mysqli->rollback();
    echo json_encode(['success' => false, 'message' => "Fehler beim Einfügen der Datensätze"]);
    http_response_code(400);
    exit;
}

echo json_encode(['success' => true, 'message' => count($rows) . " Datensätze importiert"]);
http_response_code(200);
exit;