Thesaurus/ajax/getAuthorityData.php

<?php
/**
 * getAuthorityData.php - Liefert Normdaten für Bootstrap Table
 * 
 * Parameter (Bootstrap Table Server-Side):
 * - limit: Anzahl Einträge pro Seite
 * - offset: Start-Position
 * - search: Suchbegriff
 * - authType: Typ (Subject, Person, Corporate, Publisher, Classification)
 * 
 * Rückgabe: JSON mit total und rows
 */

header('Content-Type: application/json; charset=utf-8');

include "db_connection.php";

$conn = mysqli_connect(HOST, USER, PASSWORD, DATABASE);
if (mysqli_connect_errno()) {
    echo json_encode(['total' => 0, 'rows' => [], 'error' => 'Datenbankverbindung fehlgeschlagen']);
    exit();
}

mysqli_set_charset($conn, "utf8");

// Parameter auslesen
$limit = isset($_GET['limit']) ? $_GET['limit'] : 25;
$offset = isset($_GET['offset']) ? intval($_GET['offset']) : 0;

// "all" oder 0 bedeutet: alle Treffer ohne Limit
if ($limit === 'all' || $limit === '0' || intval($limit) <= 0) {
    $limitAll = true;
    $limit = 0;
} else {
    $limitAll = false;
    $limit = intval($limit);
}
$search = isset($_GET['search']) ? trim($_GET['search']) : '';
$authType = isset($_GET['authType']) ? trim($_GET['authType']) : 'Subject';

// Erlaubte Typen
$allowedTypes = ['Subject', 'Person', 'Corporate', 'Publisher', 'Classification'];
if (!in_array($authType, $allowedTypes)) {
    $authType = 'Subject';
}

// Basis-SQL
$baseSql = "FROM Anchor a 
            LEFT JOIN Entry e ON a.ID = e.ID 
            WHERE a.Type = '" . mysqli_real_escape_string($conn, $authType) . "'";

// Suchbedingung hinzufügen (case-insensitive)
// Scope Note (e.Comment) wird bewusst nicht durchsucht
if (!empty($search)) {
    // Trunkierung auswerten
    $leftTrunc  = (substr($search, 0, 1) === '*');
    $rightTrunc = (substr($search, -1)   === '*');
    
    // Sternchen entfernen, dann escapen
    $clean = trim($search, '*');
    $searchEscaped = mysqli_real_escape_string($conn, $clean);
    // LIKE-Sonderzeichen in den Daten escapen
    $searchEscaped = str_replace(['%', '_'], ['\\%', '\\_'], $searchEscaped);
    
    // Pattern bauen
    $pattern = ($leftTrunc  ? '%' : '') .
               $searchEscaped .
               ($rightTrunc ? '%' : '%');  // rechts immer trunkieren = bisheriges Verhalten
    
    $baseSql .= " AND (LOWER(e.Text) LIKE LOWER('{$pattern}') 
                      OR LOWER(e.CompleteText) LIKE LOWER('{$pattern}'))";
}

// Gesamtanzahl ermitteln
$countSql = "SELECT COUNT(*) as total " . $baseSql;
$countResult = mysqli_query($conn, $countSql);
$total = 0;
if ($countResult) {
    $countRow = mysqli_fetch_assoc($countResult);
    $total = intval($countRow['total']);
}

// ORDER BY je nach authType
// Classification: numerisch nach Notation (z.B. T 4.10 nach T 4.9)
// Alle anderen: deutsch-korrekt nach Unicode-Kollation (Umlaute, Groß/Klein, Sonderzeichen)
if ($authType === 'Classification') {
    // Dreistufige Sortierung:
    // 1. Buchstabenpräfix alphabetisch (G, S, T ...)
    // 2. Erste Zahl numerisch (Hauptgruppe vor Untergruppe: NULL kommt zuerst)
    // 3. Zweite Zahl numerisch (T 4.9 vor T 4.10)
    $orderClause = "
        SUBSTRING_INDEX(a.Classification, ' ', 1),
        CAST(
            NULLIF(SUBSTRING_INDEX(SUBSTRING_INDEX(a.Classification, ' ', -1), '.', 1),
                   a.Classification)
            AS UNSIGNED
        ),
        CAST(
            NULLIF(SUBSTRING_INDEX(a.Classification, '.', -1),
                   a.Classification)
            AS UNSIGNED
        )
    ";
} else {
    // utf8_unicode_ci: Umlaute korrekt (Ä=A, Ö=O, Ü=U),
    // Sonderzeichen nach Grundbuchstabe, Groß-/Klein ignoriert
    $orderClause = "e.Text COLLATE utf8_unicode_ci ASC";
}

// Daten abrufen
$dataSql = "SELECT a.ID, a.Type, a.DetailType, a.Classification, 
                   e.Text, e.CompleteText, e.Comment, 
                   e.DateCreated, e.DateModified " . $baseSql . " 
            ORDER BY " . $orderClause;

if (!$limitAll) {
    $dataSql .= " LIMIT $offset, $limit";
}

$dataResult = mysqli_query($conn, $dataSql);

$rows = [];
if ($dataResult) {
    while ($row = mysqli_fetch_assoc($dataResult)) {
        // Relationen zählen
        $relSql = "SELECT COUNT(*) as cnt FROM Linking WHERE IDAnchor = " . intval($row['ID']);
        $relResult = mysqli_query($conn, $relSql);
        $relCount = 0;
        if ($relResult) {
            $relRow = mysqli_fetch_assoc($relResult);
            $relCount = intval($relRow['cnt']);
        }
        
        // Deskriptor-Status ermitteln (USE-Relation = Non-Deskriptor)
        $useSql = "SELECT COUNT(*) as cnt FROM Linking WHERE IDAnchor = " . intval($row['ID']) . " AND UPPER(Relationtype) = 'USE'";
        $useResult = mysqli_query($conn, $useSql);
        $isDescriptor = true;
        if ($useResult) {
            $useRow = mysqli_fetch_assoc($useResult);
            if (intval($useRow['cnt']) > 0) {
                $isDescriptor = false;
            }
        }
        
        $rows[] = [
            'ID' => $row['ID'],
            'Type' => $row['Type'],
            'DetailType' => $row['DetailType'] ?? '',
            'Classification' => $row['Classification'] ?? '',
            'Text' => $row['Text'] ?? '',
            'CompleteText' => $row['CompleteText'] ?? '',
            'Comment' => $row['Comment'] ?? '',
            'DateCreated' => $row['DateCreated'] ?? '',
            'DateModified' => $row['DateModified'] ?? '',
            'RelationCount' => $relCount,
            'Descriptor' => $isDescriptor
        ];
    }
}

mysqli_close($conn);

echo json_encode([
    'total' => $total,
    'rows' => $rows
], JSON_UNESCAPED_UNICODE);
?>