<?php
#	SonicCampGuard
#
#	Copyright (c) 2024 Bundeswehr
#	Diese Anwendung wurde von Cedric Prietz entwickelt.
#	E-Mail: CedricPrietz@bundeswehr.org
#	App: 90-8303-54618
#
#	Letzte Änderung: 27.10.2024

require_once $_SERVER['DOCUMENT_ROOT'] . '/function.inc.php';
pageName('Bericht - Zonenauswertung');
requireLogin();
requireRole(['Auswerter', 'Bearbeiter', 'Administrator']);
showSessionAlert();

initializeDatabaseConnection();

// PDF-Bibliotheken laden
require_once '../assets/vendors/tcpdf/tcpdf.php';

// Excel-Bibliotheken laden
require '../assets/vendors/phpspreadsheet/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

// Exportieren : PDF
if (isset($_POST['exportReportPDF'])) {

    class AuditLogPDF extends TCPDF
    {

        public function Header()
        {
            $this->SetY(10);
            $this->SetFont('helvetica', '', 8);

            $leftMargin = 20;
            $this->SetX($leftMargin);
            $this->writeHTMLCell(60, 10, $this->GetX(), $this->GetY(), 'SONIC<b>CG</b>', 0, 0, false, true, 'L', true);

            $this->SetX(($this->w / 2) - 10);
            $this->writeHTMLCell(20, 10, $this->GetX(), $this->GetY(), '<b>VS-NfD</b>', 0, 0, false, true, 'C', true);

            $this->SetX($this->w - $leftMargin - 60);
            $this->writeHTMLCell(60, 10, $this->GetX(), $this->GetY(), 'ZONENAUSWERTUNG', 0, 0, false, true, 'R', true);

            $this->Ln(10);
        }

        public function Footer()
        {
            $this->SetY(-15); // Positioniert die Fußzeile 15 Punkte vom unteren Rand der Seite
            $this->SetFont('helvetica', '', 8);

            // Linke HTML-Zelle mit Erstellungsdatum und Benutzername (75% der Seitenbreite)
            $leftText = 'Dieses Dokument wurde am <b>' . date('Y-m-d H:i:s') . '</b> von <b>' . htmlspecialchars($_SESSION['DisplayName']) . '</b> elektronisch erstellt.';
            $this->writeHTMLCell($this->w * 0.75, 10, '', '', '<span>' . $leftText . '</span>', 0, 0, false, true, 'L', true);

            $rightText = 'Seite ' . $this->getAliasNumPage() . ' von ' . $this->getAliasNbPages();
            $rightMargin = 10; // Abstand vom rechten Rand
            $this->writeHTMLCell($this->w * 0.25 - $rightMargin, 10, $this->w * 0.75, '', '<span style="text-align: right; display: block;">' . $rightText . '</span>', 0, 0, false, true, 'R', true);

            $this->Ln(20);
        }


    }

    $pdf = new AuditLogPDF();

    $pdf->SetMargins(20, 20, 20);
    $pdf->SetAutoPageBreak(true, 25);

    $pdf->SetCreator(PDF_CREATOR);
    $pdf->SetAuthor('SONICCG');
    $pdf->SetTitle('Zonenauswertung');
    $pdf->SetSubject('Bericht');

    $pdf->AddPage();

    $pdf->SetFont('helvetica', '', 10);

    $html = '<h2>ZONENAUSWERTUNG</h2>';
    $html .= '<table cellpadding="5" cellspacing="0" border="0" style="width: 100%;"><tr><td></td></tr></table>';
    $html .= '<table cellpadding="3" cellspacing="0" border="0" style="background-color: #e8ecef; width: 100%;"><tr><td style="font-size: 10px; ;">NOTIZEN</td></tr></table>';
    $html .= '<table cellpadding="5" cellspacing="0" style="width: 100%; margin-bottom: 30px;"><tr><td style="border-bottom: 1px solid #000;"></td></tr><tr><td style="border-bottom: 1px solid #000;"></td></tr><tr><td style="border-bottom: 1px solid #000;"></td></tr><tr><td style="border-bottom: 1px solid #000;"></td></tr><tr><td style="border-bottom: 1px solid #000;"></td></tr><tr><td style="border-bottom: 1px solid #000;"></td></tr><tr><td style="border-bottom: 1px solid #000;"></td></tr><tr><td style="border-bottom: 1px solid #000;"></td></tr><tr><td style="border-bottom: 1px solid #000;"></td></tr><tr><td></td></tr></table>';
    $html .= '<table cellpadding="3" cellspacing="0" border="0" style="background-color: #e8ecef; width: 100%;"><tr><td style="font-size: 10px; ;">ZONEN</td></tr></table>';
    $html .= '<br><br>';

    $sql = "
            SELECT 
                *,
                COUNT(CASE WHEN Identities.Type = 'Military' AND 
                                Identities.CurrentZone = Zones.ZoneID AND
                                Identities.AccessCard IS NOT NULL AND 
                                Identities.AccessCard != '' AND
                                NOW() BETWEEN Identities.IdentityIN AND Identities.IdentityOUT 
                            THEN 1 END) AS Military,
                COUNT(CASE WHEN Identities.Type = 'Local' AND 
                                Identities.CurrentZone = Zones.ZoneID AND
                                Identities.AccessCard IS NOT NULL AND 
                                Identities.AccessCard != '' AND
                                NOW() BETWEEN Identities.IdentityIN AND Identities.IdentityOUT 
                            THEN 1 END) AS Local,
                COUNT(CASE WHEN Identities.Type = 'Civil' AND 
                                Identities.CurrentZone = Zones.ZoneID AND
                                Identities.AccessCard IS NOT NULL AND 
                                Identities.AccessCard != '' AND
                                NOW() BETWEEN Identities.IdentityIN AND Identities.IdentityOUT 
                            THEN 1 END) AS Civil,
                COUNT(CASE WHEN Identities.Type = 'Guest' AND 
                                Identities.CurrentZone = Zones.ZoneID AND
                                Identities.AccessCard IS NOT NULL AND 
                                Identities.AccessCard != '' AND
                                NOW() BETWEEN Identities.IdentityIN AND Identities.IdentityOUT 
                            THEN 1 END) AS Guest,
                COUNT(CASE WHEN Identities.CurrentZone = Zones.ZoneID AND
                                Identities.AccessCard IS NOT NULL AND 
                                Identities.AccessCard != '' AND
                                NOW() BETWEEN Identities.IdentityIN AND Identities.IdentityOUT 
                            THEN 1 END) AS Total
            FROM 
                Zones
            LEFT JOIN 
                Identities ON Identities.CurrentZone = Zones.ZoneID
            GROUP BY 
                Zones.ZoneID
            ORDER BY 
                Zones.ZoneName
        ";

    // SQL-Abfrage vorbereiten und ausführen
    $stmt = $pdo->prepare($sql);
    $stmt->execute();

    // Ergebnisse abrufen
    $zoneCounts = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Initialisiere die Summen
    $sumMilitary = 0;
    $sumLocal = 0;
    $sumCivil = 0;
    $sumGuest = 0;
    $sumTotal = 0;

    // Tabellenstruktur
    $html .= '<table cellpadding="3" cellspacing="0" border="1" style="width: 100%;">';
    $html .= '<tr>';
    $html .= '<td style="width: 30%; font-size: 8px;"><b>Zone</b></td>';
    $html .= '<td style="width: 14%; font-size: 8px; text-align: right;"><b>Soldaten</b></td>';
    $html .= '<td style="width: 14%; font-size: 8px; text-align: right;"><b>Ortskräfte</b></td>';
    $html .= '<td style="width: 14%; font-size: 8px; text-align: right;"><b>Zivilisten</b></td>';
    $html .= '<td style="width: 14%; font-size: 8px; text-align: right;"><b>Gäste</b></td>';
    $html .= '<td style="width: 14%; font-size: 8px; text-align: right;"><b>Gesamt</b></td>';
    $html .= '</tr>';

    // Daten ausgeben
    $html .= '<tbody>';
    foreach ($zoneCounts as $zone) {
        // Summiere die Werte für jede Zone
        $sumMilitary += $zone['Military'];
        $sumLocal += $zone['Local'];
        $sumCivil += $zone['Civil'];
        $sumGuest += $zone['Guest'];
        $sumTotal += $zone['Total'];

        $html .= '<tr>';
        $html .= '<td class="text-uppercase">' . htmlspecialchars($zone['ZoneName']) . '</td>';
        $html .= '<td style="text-align: right;">' . ($zone['Military'] ?? 0) . '</td>';
        $html .= '<td style="text-align: right;">' . ($zone['Local'] ?? 0) . '</td>';
        $html .= '<td style="text-align: right;">' . ($zone['Civil'] ?? 0) . '</td>';
        $html .= '<td style="text-align: right;">' . ($zone['Guest'] ?? 0) . '</td>';
        $html .= '<td style="text-align: right;">' . ($zone['Total'] ?? 0) . '</td>';
        $html .= '</tr>';
    }

    // Zeile für Gesamtsummen
    $html .= '<tr>';
    $html .= '<td style="font-size: 8px;"><b>Gesamt</b></td>';
    $html .= '<td style="font-size: 8px; text-align: right;"><b>' . $sumMilitary . '</b></td>';
    $html .= '<td style="font-size: 8px; text-align: right;"><b>' . $sumLocal . '</b></td>';
    $html .= '<td style="font-size: 8px; text-align: right;"><b>' . $sumCivil . '</b></td>';
    $html .= '<td style="font-size: 8px; text-align: right;"><b>' . $sumGuest . '</b></td>';
    $html .= '<td style="font-size: 8px; text-align: right;"><b>' . $sumTotal . '</b></td>';
    $html .= '</tr>';

    $html .= '</tbody>';
    $html .= '</table>';


    $pdf->writeHTML($html, true, false, true, false, '');

    $pdf->AddPage('L');

    $html2 = '<table cellpadding="3" cellspacing="0" border="0" style="background-color: #e8ecef; width: 100%;"><tr><td style="font-size: 10px; ;">IDENTITÄTEN</td></tr></table>';
    $html2 .= '<br><br>';
    $html2 .= '<table border="1" cellpadding="3" cellspacing="0" style="width: 100%; font-size: 9px;">
                    <tr style="font-size: 8px; font-weight: bold;">
                        <td width="15%">Zone</td>
                        <td width="7%">Karte</td>
                        <td width="35%">Identität</td>
                        <td width="35%">Funktion</td>
                        <td width="8%">Funk</td>
                    </tr>';

    try {
        // SQL-Abfrage ausführen und Ergebnisse verarbeiten
        $sql = "
                    SELECT *
                    FROM Identities
                    WHERE (NOW() BETWEEN IdentityIN AND IdentityOUT
                    OR DATE(NOW()) = DATE(IdentityIN)
                    OR DATE(NOW()) = DATE(IdentityOUT))
                    AND AccessCard IS NOT NULL
                    AND AccessCard != ''
                    ORDER BY Type, CurrentZone, AccessCard
                ";

        $stmt = $pdo->prepare($sql);
        $stmt->execute();
        $accessCards = $stmt->fetchAll(PDO::FETCH_ASSOC);

        if ($accessCards) {
            $currentType = '';
            foreach ($accessCards as $card) {
                if ($currentType !== $card['Type']) {
                    $currentType = $card['Type'];
                    $typeName = '';

                    switch ($card['Type']) {
                        case 'Military':
                            $typeName = 'Soldaten';
                            break;
                        case 'Local':
                            $typeName = 'Ortskräfte';
                            break;
                        case 'Civil':
                            $typeName = 'Zivilisten';
                            break;
                        case 'Guest':
                            $typeName = 'Gäste';
                            break;
                    }

                    $html2 .= '
                                <tr>
                                    <td colspan="5" style="background-color: #e8ecef; font-weight: bold;">
                                        ' . htmlspecialchars($typeName) . '
                                    </td>
                                </tr>';
                }

                $zoneColor = getZoneDetails($card['CurrentZone'], 'ZoneColor');
                $zoneName = getZoneDetails($card['CurrentZone'], 'ZoneName') ?: 'Keine Buchungshistorie';
                $zoneDisplay = '<span style="color: ' . htmlspecialchars($zoneColor) . ';">' . htmlspecialchars($zoneName) . '</span>';

                $identity = htmlspecialchars($card['Rank']) . ' ' . htmlspecialchars($card['Surname']) . ', ' . htmlspecialchars($card['Givenname']);

                $function = '';
                if (getPositionDetails('OwnerAccessCard', $card['AccessCard'], 'ID')) {
                    $function = getPositionDetails('OwnerAccessCard', $card['AccessCard'], 'PositionName') . ' - ' . getPositionDetails('OwnerAccessCard', $card['AccessCard'], 'Department');
                } elseif (getPositionDetails('SuccessorAccessCard', $card['AccessCard'], 'ID')) {
                    $function = getPositionDetails('SuccessorAccessCard', $card['AccessCard'], 'PositionName') . ' - ' . getPositionDetails('SuccessorAccessCard', $card['AccessCard'], 'Department');
                }

                $radio = '';
                if (getPositionDetails('OwnerAccessCard', $card['AccessCard'], 'ID')) {
                    $radio = getPositionDetails('OwnerAccessCard', $card['AccessCard'], 'Radio');
                } elseif (getPositionDetails('SuccessorAccessCard', $card['AccessCard'], 'ID')) {
                    $radio = getPositionDetails('SuccessorAccessCard', $card['AccessCard'], 'Radio');
                }

                $html2 .= '
                        <tr>
                            <td>' . $zoneDisplay . '</td>
                            <td>' . htmlspecialchars($card['AccessCard']) . '</td>
                            <td>' . $identity . '</td>
                            <td>' . htmlspecialchars($function) . '</td>
                            <td>' . htmlspecialchars($radio) . '</td>
                        </tr>';
            }
        } else {
            $html2 .= '
                    <tr>
                        <td colspan="5" style="text-align: center; color: #FF0000;">Keine Datensätze vorhanden</td>
                    </tr>';
        }
    } catch (PDOException $e) {
        $html2 .= '<tr><td colspan="5" style="color: #FF0000;">Datenbankfehler: ' . htmlspecialchars($e->getMessage()) . '</td></tr>';
    }

    $html2 .= '</table>';


    $pdf->writeHTML($html2, true, false, true, false, '');

    $pdf->Output('ZoneReport_' . date('Y-m-d_H-i-s') . '.pdf', 'D');

}

// Exportieren : XLSX
if (isset($_POST['exportReportXLSX'])) {

    // Neues Spreadsheet-Objekt erstellen
    $spreadsheet = new Spreadsheet();

    // Definition der verschiedenen Typen und Arbeitsblätter in der gewünschten Reihenfolge
    $types = [
        'Military' => 'Soldaten',
        'Civil' => 'Zivilisten',
        'Guest' => 'Gäste',
        'Local' => 'Ortskräfte',
    ];

    try {
        // SQL-Abfrage, um alle Datensätze abzurufen
        $sql = "
                SELECT *
                FROM Identities
                WHERE (NOW() BETWEEN IdentityIN AND IdentityOUT
                OR DATE(NOW()) = DATE(IdentityIN)
                OR DATE(NOW()) = DATE(IdentityOUT))
                AND AccessCard IS NOT NULL
                AND AccessCard != ''
                ORDER BY Type, CurrentZone, AccessCard
            ";

        $stmt = $pdo->prepare($sql);
        $stmt->execute();
        $accessCards = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Arbeitsblätter für jeden Typ erstellen und in der gewünschten Reihenfolge anordnen
        foreach ($types as $typeKey => $typeName) {
            // Neues Arbeitsblatt für den Typ hinzufügen und als aktives Blatt setzen
            $sheet = $spreadsheet->createSheet();
            $sheet->setTitle($typeName);

            // Tabellenkopf erstellen und formatieren
            $headers = ['Zone', 'Karte', 'Schutzraum', 'Identität', 'Funktion', 'Funk'];
            $columnIndex = 'A';

            foreach ($headers as $header) {
                $cell = $columnIndex . '1';

                // Setze den Header-Wert und Formatierung
                $sheet->setCellValue($cell, strtoupper($header)); // Großbuchstaben
                $sheet->getStyle($cell)->getFont()->setBold(true); // Fettschrift

                if ($header === 'Schutzraum') {
                    // Spezielle Formatierung für "Schutzraum"-Header
                    $sheet->getStyle($cell)->getFont()->getColor()->setRGB(Color::COLOR_WHITE); // Weiße Schrift
                    $sheet->getStyle($cell)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('8B0000'); // Dunkelrot
                } else {
                    // Standardheader-Formatierung
                    $sheet->getStyle($cell)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('D3D3D3'); // Hellgrauer Hintergrund
                }

                // Vertikale Zentrierung für die Kopfzeile
                $sheet->getStyle($cell)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);

                $columnIndex++;
            }

            // Kopfzeile fixieren und Höhe auf 25 setzen
            $sheet->freezePane('A2');
            $sheet->getRowDimension(1)->setRowHeight(25);

            // Filterung der Daten für den aktuellen Typ
            $rowNumber = 2;
            foreach ($accessCards as $card) {
                if ($card['Type'] !== $typeKey)
                    continue;

                // Daten für die aktuellen Einträge formatieren
                $zoneName = getZoneDetails($card['CurrentZone'], 'ZoneName') ?: 'Keine Buchungshistorie';
                $identity = $card['Rank'] . ' ' . $card['Surname'] . ', ' . $card['Givenname'];

                $function = '';
                if ($positionId = getPositionDetails('OwnerAccessCard', $card['AccessCard'], 'ID')) {
                    $function = getPositionDetails('OwnerAccessCard', $card['AccessCard'], 'PositionName') . ' - ' . getPositionDetails('OwnerAccessCard', $card['AccessCard'], 'Department');
                } elseif ($positionId = getPositionDetails('SuccessorAccessCard', $card['AccessCard'], 'ID')) {
                    $function = getPositionDetails('SuccessorAccessCard', $card['AccessCard'], 'PositionName') . ' - ' . getPositionDetails('SuccessorAccessCard', $card['AccessCard'], 'Department');
                }

                $radio = getPositionDetails('OwnerAccessCard', $card['AccessCard'], 'Radio')
                    ?: getPositionDetails('SuccessorAccessCard', $card['AccessCard'], 'Radio');

                // Daten in die Zellen schreiben, einschließlich leerer "Schutzraum"-Spalte
                $sheet->setCellValue("A$rowNumber", $zoneName);
                $sheet->setCellValue("B$rowNumber", $card['AccessCard']);
                $sheet->setCellValue("C$rowNumber", ''); // Leere Zelle für Schutzraum
                $sheet->setCellValue("D$rowNumber", $identity);
                $sheet->setCellValue("E$rowNumber", $function);
                $sheet->setCellValue("F$rowNumber", $radio);

                // Spezielle Formatierung für die "Schutzraum"-Spalte
                $sheet->getStyle("C$rowNumber")->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('FFCCCC'); // Hellrot
                $sheet->getStyle("C$rowNumber")->getFont()->getColor()->setRGB(Color::COLOR_BLACK); // Schwarze Schrift

                // Zeilenhöhe auf 20 setzen und vertikal zentrieren
                $sheet->getRowDimension($rowNumber)->setRowHeight(20);
                $sheet->getStyle("A$rowNumber:F$rowNumber")->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);

                $rowNumber++;
            }

            if ($rowNumber == 2) { // Falls keine Datensätze für diesen Typ vorhanden sind
                $sheet->setCellValue('A2', 'Keine Datensätze vorhanden');
                $sheet->mergeCells("A2:F2");
                $sheet->getStyle("A2")->getFont()->getColor()->setRGB('FF0000');
                $sheet->getRowDimension(2)->setRowHeight(20); // Zeilenhöhe für die Meldung setzen
                $sheet->getStyle("A2:F2")->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
            }

            // Spaltenbreite automatisch an den Inhalt anpassen
            foreach (range('A', 'F') as $columnID) {
                $sheet->getColumnDimension($columnID)->setAutoSize(true);
            }
        }

        // Erstes (leeres) Standard-Arbeitsblatt entfernen, falls vorhanden
        $spreadsheet->removeSheetByIndex(0);

    } catch (PDOException $e) {
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('A1', 'Datenbankfehler: ' . $e->getMessage());
        $sheet->mergeCells("A1:F1");
        $sheet->getStyle("A1")->getFont()->getColor()->setRGB('FF0000');
        $sheet->getRowDimension(1)->setRowHeight(20); // Zeilenhöhe für die Fehlermeldung setzen
        $sheet->getStyle("A1:F1")->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
    }

    // Header für den Download setzen und Datei an den Output-Stream senden
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="ZoneReport_' . date('Y-m-d_H-i-s') . '.xlsx"');
    header('Cache-Control: max-age=0');

    $writer = new Xlsx($spreadsheet);
    ob_end_clean();
    $writer->save('php://output');
    exit;

}

include($_SERVER['DOCUMENT_ROOT'] . '/header.inc.php');
?>

<div class="d-flex justify-content-between align-items-center flex-wrap grid-margin">
    <div>
        <h4 class="mb-md-0">Zonenauswertung</h4>
        <span class="text-secondary">Bericht</span>
    </div>
    <div class="d-flex align-items-center flex-wrap text-nowrap">

        <div class="dropdown" data-bs-toggle="tooltip" data-bs-placement="left" title="Exportieren">
            <button class="btn btn-success btn-icon me-2" type="button" id="dropdownMenuButton"
                data-bs-toggle="dropdown" aria-haspopup="true" aria-expanded="false">
                <i class="mdi mdi-download"></i>
            </button>
            <div class="dropdown-menu" aria-labelledby="dropdownMenuButton">
                <form action="" method="post">
                    <button class="dropdown-item" name="exportReportPDF">PDF</button>
                    <button class="dropdown-item" name="exportReportXLSX">EXCEL</button>
                </form>
            </div>
        </div>

        <a href="/reports/reportboard" class="btn btn-secondary btn-icon mb-md-0">
            <i class="mdi mdi-arrow-left-top"></i>
        </a>
    </div>
</div>

<div class="row">

    <div class="col-12 grid-margin">
        <div class="card">
            <div class="card-header">
                Personen
            </div>
            <div class="card-body">

                <?php
                try {
                    // SQL-Abfrage für die Zonen- und Typenzählung
                    $sql = "
                            SELECT 
                                *,
                                COUNT(CASE WHEN Identities.Type = 'Military' AND 
                                                Identities.CurrentZone = Zones.ZoneID AND
                                                Identities.AccessCard IS NOT NULL AND 
                                                Identities.AccessCard != '' AND
                                                NOW() BETWEEN Identities.IdentityIN AND Identities.IdentityOUT 
                                            THEN 1 END) AS Military,
                                COUNT(CASE WHEN Identities.Type = 'Local' AND 
                                                Identities.CurrentZone = Zones.ZoneID AND
                                                Identities.AccessCard IS NOT NULL AND 
                                                Identities.AccessCard != '' AND
                                                NOW() BETWEEN Identities.IdentityIN AND Identities.IdentityOUT 
                                            THEN 1 END) AS Local,
                                COUNT(CASE WHEN Identities.Type = 'Civil' AND 
                                                Identities.CurrentZone = Zones.ZoneID AND
                                                Identities.AccessCard IS NOT NULL AND 
                                                Identities.AccessCard != '' AND
                                                NOW() BETWEEN Identities.IdentityIN AND Identities.IdentityOUT 
                                            THEN 1 END) AS Civil,
                                COUNT(CASE WHEN Identities.Type = 'Guest' AND 
                                                Identities.CurrentZone = Zones.ZoneID AND
                                                Identities.AccessCard IS NOT NULL AND 
                                                Identities.AccessCard != '' AND
                                                NOW() BETWEEN Identities.IdentityIN AND Identities.IdentityOUT 
                                            THEN 1 END) AS Guest,
                                COUNT(CASE WHEN Identities.CurrentZone = Zones.ZoneID AND
                                                Identities.AccessCard IS NOT NULL AND 
                                                Identities.AccessCard != '' AND
                                                NOW() BETWEEN Identities.IdentityIN AND Identities.IdentityOUT 
                                            THEN 1 END) AS Total
                            FROM 
                                Zones
                            LEFT JOIN 
                                Identities ON Identities.CurrentZone = Zones.ZoneID
                            GROUP BY 
                                Zones.ZoneID
                            ORDER BY 
                                Zones.ZoneName
                        ";

                    // SQL-Abfrage vorbereiten und ausführen
                    $stmt = $pdo->prepare($sql);
                    $stmt->execute();

                    // Ergebnisse abrufen
                    $zoneCounts = $stmt->fetchAll(PDO::FETCH_ASSOC);

                    // Initialisiere die Summen
                    $sumMilitary = 0;
                    $sumLocal = 0;
                    $sumCivil = 0;
                    $sumGuest = 0;
                    $sumTotal = 0;

                    // Tabellenstruktur
                    echo '<div class="table-responsive">';
                    echo '<table class="table table-bordered">';
                    echo '<thead>';
                    echo '<tr>';
                    echo '<th>Zone</th>';
                    echo '<th class="text-end" width="12%">Soldaten</th>';
                    echo '<th class="text-end" width="12%">Ortskräfte</th>';
                    echo '<th class="text-end" width="12%">Zivilisten</th>';
                    echo '<th class="text-end" width="12%">Gäste</th>';
                    echo '<th class="text-end" width="12%">Gesamt</th>';
                    echo '</tr>';
                    echo '</thead>';

                    // Daten ausgeben
                    echo '<tbody>';
                    foreach ($zoneCounts as $zone) {
                        // Summiere die Werte für jede Zone
                        $sumMilitary += $zone['Military'];
                        $sumLocal += $zone['Local'];
                        $sumCivil += $zone['Civil'];
                        $sumGuest += $zone['Guest'];
                        $sumTotal += $zone['Total'];

                        echo '<tr>';
                        echo '<td class="text-uppercase">' . htmlspecialchars($zone['ZoneName']) . '</td>';
                        echo '<td class="text-end">' . ($zone['Military'] ?? 0) . '</td>';
                        echo '<td class="text-end">' . ($zone['Local'] ?? 0) . '</td>';
                        echo '<td class="text-end">' . ($zone['Civil'] ?? 0) . '</td>';
                        echo '<td class="text-end">' . ($zone['Guest'] ?? 0) . '</td>';
                        echo '<td class="text-end">' . ($zone['Total'] ?? 0) . '</td>';
                        echo '</tr>';
                    }

                    // Zeile für Gesamtsummen
                    echo '<tr class="table-primary">';
                    echo '<td>Gesamt</td>';
                    echo '<td class="text-end">' . $sumMilitary . '</td>';
                    echo '<td class="text-end">' . $sumLocal . '</td>';
                    echo '<td class="text-end">' . $sumCivil . '</td>';
                    echo '<td class="text-end">' . $sumGuest . '</td>';
                    echo '<td class="text-end">' . $sumTotal . '</td>';
                    echo '</tr>';

                    echo '</tbody>';
                    echo '</table>';
                    echo '</div>';

                } catch (PDOException $e) {
                    echo 'Datenbankfehler: ' . $e->getMessage();
                }
                ?>


            </div>
        </div>
    </div>

    <div class="col-12 grid-margin">
        <div class="card">
            <div class="card-header">
                Gesamtübersicht
            </div>
            <div class="card-body">
                <div class="table-responsive">
                    <table class="table table-bordered">
                        <thead>
                            <tr>
                                <th>Zone</th>
                                <th>Karte</th>
                                <th>Identität</th>
                                <th>Funktion</th>
                                <th>Funkrufnummer</th>
                            </tr>
                        </thead>
                        <?php
                        try {

                            $sql = "
                                SELECT *
                                FROM Identities
                                WHERE (NOW() BETWEEN IdentityIN AND IdentityOUT
                                OR DATE(NOW()) = DATE(IdentityIN)
                                OR DATE(NOW()) = DATE(IdentityOUT))
                                AND AccessCard IS NOT NULL
                                AND AccessCard != ''
                                ORDER BY Type, CurrentZone, AccessCard
                        ";

                            // Abfrage vorbereiten und ausführen
                            $stmt = $pdo->prepare($sql);
                            $stmt->execute();

                            // Ergebnisse abrufen
                            $accessCards = $stmt->fetchAll(PDO::FETCH_ASSOC);

                            // Prüfen, ob Ergebnisse vorhanden sind
                            if ($accessCards) {

                                $currentType = '';

                                foreach ($accessCards as $card) {

                                    if ($currentType !== $card['Type']) {

                                        $currentType = $card['Type'];

                                        if ($card['Type'] == 'Military') {
                                            $typeName = 'Soldaten';
                                        } elseif ($card['Type'] == 'Local') {
                                            $typeName = 'Ortskräfte';
                                        } elseif ($card['Type'] == 'Civil') {
                                            $typeName = 'Zivilisten';
                                        } elseif ($card['Type'] == 'Guest') {
                                            $typeName = 'Gäste';
                                        }

                                        echo "<tr><td colspan='5' class='bg-light pt-1 pb-1 text-primary text-uppercase fs-12px' style='letter-spacing: 1px;'>" . $typeName . "</td></tr>";

                                    }

                                    echo '<tr>';

                                    echo '<td>';
                                    if (!empty($card['CurrentZone'])) {
                                        echo '<span style="color: ' . getZoneDetails($card['CurrentZone'], 'ZoneColor') . '">' . getZoneDetails($card['CurrentZone'], 'ZoneName') . '</span>';
                                    } else {
                                        echo '<span class="text-warning">Keine Buchungshistorie</span>';
                                    }
                                    echo '</td>';

                                    echo '<td>' . $card['AccessCard'] . '</td>';
                                    echo '<td>' . $card['Rank'] . ' ' . $card['Surname'] . ', ' . $card['Givenname'] . '</td>';

                                    if (getPositionDetails('OwnerAccessCard', $card['AccessCard'], 'ID')) {
                                        echo "<td>" . getPositionDetails('OwnerAccessCard', $card['AccessCard'], 'PositionName') . " - " . getPositionDetails('OwnerAccessCard', $card['AccessCard'], 'Department') . "</td>";
                                    } elseif (getPositionDetails('SuccessorAccessCard', $card['AccessCard'], 'ID')) {
                                        echo "<td>" . getPositionDetails('SuccessorAccessCard', $card['AccessCard'], 'PositionName') . " - " . getPositionDetails('SuccessorAccessCard', $card['AccessCard'], 'Department') . "</td>";
                                    }

                                    if (getPositionDetails('OwnerAccessCard', $card['AccessCard'], 'ID')) {
                                        echo "<td>" . getPositionDetails('OwnerAccessCard', $card['AccessCard'], 'Radio') . "</td>";
                                    } elseif (getPositionDetails('SuccessorAccessCard', $card['AccessCard'], 'ID')) {
                                        echo "<td>" . getPositionDetails('SuccessorAccessCard', $card['AccessCard'], 'Radio') . "</td>";
                                    }


                                    echo '</tr>';
                                }
                            } else {
                                echo '<td colspan="3">Keine Datensätze vorhanden</td>';
                            }

                        } catch (PDOException $e) {
                            echo 'Datenbankfehler: ' . $e->getMessage();
                        }

                        ?>
                    </table>
                </div>
            </div>
        </div>
    </div>

</div>

<?php include($_SERVER['DOCUMENT_ROOT'] . '/footer.inc.php'); ?>