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

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

initializeDatabaseConnection();

// 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;
use PhpOffice\PhpSpreadsheet\Style\Border;

// Exportieren: EXCEL
if (isset($_POST['exportXLSX'])) {

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

    // Definition des Arbeitsblatts für "Sperrliste"
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Sperrliste');

    try {
        // SQL-Abfrage zur Datenauswahl
        $sql = "
            SELECT 
                Surname,
                Givenname,
                IDCard,
                Birthday,
                Nationality,
                Company,
                Reason,
                Created
            FROM 
                BlacklistLocals
            ORDER BY 
                Created ASC
        ";

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

        // Tabellenkopf erstellen und formatieren
        $headers = ['Nachname', 'Vorname', 'Ausweis', 'Geburtstag', 'Nationalität', 'Firma', 'Begründung', 'Hinzugefügt'];
        $columnIndex = 'A';

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

            // Setze den Header-Wert und die Formatierung
            $sheet->setCellValue($cell, strtoupper($header)); // Großbuchstaben
            $sheet->getStyle($cell)->getFont()->setBold(true); // Fettschrift
            $sheet->getStyle($cell)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('D3D3D3'); // Hellgrauer Hintergrund

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

            $columnIndex++;
        }

        // Kopfzeile Höhe auf 25 setzen
        $sheet->getRowDimension(1)->setRowHeight(25);

        // Daten in die Tabelle einfügen
        $rowNumber = 2;
        foreach ($records as $record) {
            $sheet->setCellValue("A$rowNumber", $record['Surname']);
            $sheet->setCellValue("B$rowNumber", $record['Givenname']);
            $sheet->setCellValue("C$rowNumber", $record['IDCard']);
            $sheet->setCellValue("D$rowNumber", $record['Birthday'] ? date('Y-m-d', strtotime($record['Birthday'])) : 'Keine');
            $sheet->setCellValue("E$rowNumber", $record['Nationality']);
            $sheet->setCellValue("F$rowNumber", $record['Company']);
            $sheet->setCellValue("G$rowNumber", $record['Reason']);
            $sheet->setCellValue("H$rowNumber", $record['Created'] ? date('Y-m-d H:i:s', strtotime($record['Created'])) : 'Keine');

            // Zeilenhöhe und Formatierung
            $sheet->getRowDimension($rowNumber)->setRowHeight(20);
            $sheet->getStyle("A$rowNumber:H$rowNumber")->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
            $rowNumber++;
        }

        // Falls keine Datensätze vorhanden sind
        if ($rowNumber == 2) {
            $sheet->setCellValue('A2', 'Keine Datensätze vorhanden');
            $sheet->mergeCells("A2:H2");
            $sheet->getStyle("A2")->getFont()->getColor()->setRGB('FF0000');
            $sheet->getRowDimension(2)->setRowHeight(20);
            $sheet->getStyle("A2:H2")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $sheet->getStyle("A2:H2")->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        }

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

        // Automatische Tabellenrahmen um die Felder setzen
        $lastRow = $rowNumber - 1;
        $sheet->getStyle("A1:H$lastRow")->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);

    } catch (PDOException $e) {
        $sheet->setCellValue('A1', 'Datenbankfehler: ' . $e->getMessage());
        $sheet->mergeCells("A1:H1");
        $sheet->getStyle("A1:H1")->getFont()->getColor()->setRGB('FF0000');
        $sheet->getRowDimension(1)->setRowHeight(20);
        $sheet->getStyle("A1:H1")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle("A1:H1")->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
    }

    // Header für den Download setzen und Datei ausgeben
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="Sperrliste_' . 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">Sperrliste</h4>
        <span class="text-secondary">Militärische Sicherheit</span>
    </div>
    <div class="d-flex align-items-center flex-wrap text-nowrap">

        <a href="/identities/add-blacklist" class="btn btn-primary btn-icon-text me-2">
            <i class="btn-icon-prepend mdi mdi-plus"></i> Hinzufügen
        </a>

        <div class="dropdown" data-bs-toggle="tooltip" data-bs-placement="top" 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="exportXLSX">EXCEL</button>
                </form>
            </div>
        </div>

        <a href="/identities/identities" 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 col-md-9 order-2 order-xl-1 grid-margin">
        <div class="card">
            <div class="card-header">
                Personen
            </div>
            <div class="card-body">
                <?php
                try {
                    $sql = "SELECT * FROM 
                                    BlacklistLocals
                                ORDER BY 
                                    ID DESC
                            ";

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

                    if ($results) {
                        echo '<div class="table-responsive">';
                        echo "<table id='dTableLocalBlacklist' class='table table-bordered'>";
                        echo "<thead><tr><th>Nachname</th><th>Vorname</th><th>Ausweis-ID</th><th>Optionen</th></tr></thead>";

                        foreach ($results as $row) {
                            echo "<tr>";
                            echo "<td>{$row['Surname']}</td>";
                            echo "<td>{$row['Givenname']}</td>";
                            echo "<td>{$row['IDCard']}</td>";
                            echo '<td style="padding: .60rem .85rem;"><a href="/identities/edit-blacklist?id=' . $row['ID'] . '" class="btn btn-sm btn-inverse-light btn-icon-text" style="margin: 0px;"><i class="mdi mdi-text-box-edit"></i> Bearbeiten</a></td>';
                            echo "</tr>";
                        }

                        echo "</table>";
                        echo '</div>';
                    } else {
                        echo '<div class="alert alert-success mb-0" role="alert">
                                <i class="mdi mdi-check-all"></i>
                                Es befinden sich keine Einträge in der Sperrliste
                            </div>
                            ';
                    }
                } catch (PDOException $e) {
                    echo "Datenbankfehler: " . $e->getMessage();
                }
                ?>
            </div>
        </div>
    </div>

    <div class="col-12 col-md-3 order-1 order-xl-2 grid-margin">
        <div class="card">
            <div class="card-header bg-warning text-white">
                Information
            </div>
            <ul class="list-group list-group-flush">
                <li class="list-group-item">Die <b>Sperrliste</b> ist ein Verzeichnis von Ortskräften, denen der
                    <b>Zugang dauerhaft untersagt</b> wurde.
                </li>
            </ul>
        </div>
    </div>

</div>

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