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

require_once $_SERVER['DOCUMENT_ROOT'] . '/function.inc.php';
pageName('Bericht - Militärische Abschirmdienst');
requireLogin();
requireRole(['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 : EXCEL
if (isset($_POST['exportXLSX'])) {

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

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

    try {
        // Angepasste SQL-Abfrage ausführen
        $sql = "
            SELECT 
                Surname,
                Givenname,
                LocalBAMADID,
                LocalBAMADResult,
                LocalBAMADDelivery,
                CASE 
                    WHEN (LocalBAMADResult IS NULL OR LocalBAMADResult = '') 
                        AND (LocalBAMADDelivery IS NOT NULL AND LocalBAMADDelivery != '' AND DATEDIFF(CURDATE(), LocalBAMADDelivery) > 180) THEN
                        CONCAT('Warten auf Ergebnis seit ', DATEDIFF(CURDATE(), LocalBAMADDelivery), ' Tagen')
                    WHEN LocalBAMADResult IS NULL OR LocalBAMADResult = '' THEN
                        'Bereits abgelaufen'
                    WHEN LocalBAMADDelivery IS NULL OR LocalBAMADDelivery = '' THEN
                        CASE
                            WHEN DATEDIFF(DATE_ADD(LocalBAMADResult, INTERVAL 2 YEAR), CURDATE()) <= 30 
                                AND DATEDIFF(DATE_ADD(LocalBAMADResult, INTERVAL 2 YEAR), CURDATE()) >= 0 THEN 
                                CONCAT('Läuft ab in ', DATEDIFF(DATE_ADD(LocalBAMADResult, INTERVAL 2 YEAR), CURDATE()), ' Tagen')
                            WHEN DATEDIFF(DATE_ADD(LocalBAMADResult, INTERVAL 2 YEAR), CURDATE()) < 0 THEN 
                                'Bereits abgelaufen'
                        END
                    WHEN LocalBAMADDelivery IS NOT NULL AND LocalBAMADDelivery > LocalBAMADResult THEN
                        CASE
                            WHEN DATEDIFF(CURDATE(), LocalBAMADDelivery) > 180 THEN 
                                CONCAT('Warten auf Ergebnis seit ', DATEDIFF(CURDATE(), LocalBAMADDelivery), ' Tagen')
                        END
                    WHEN LocalBAMADDelivery IS NOT NULL AND LocalBAMADDelivery <= LocalBAMADResult THEN
                        CASE
                            WHEN DATEDIFF(DATE_ADD(LocalBAMADResult, INTERVAL 2 YEAR), CURDATE()) <= 30 
                                AND DATEDIFF(DATE_ADD(LocalBAMADResult, INTERVAL 2 YEAR), CURDATE()) >= 0 THEN 
                                CONCAT('Läuft ab in ', DATEDIFF(DATE_ADD(LocalBAMADResult, INTERVAL 2 YEAR), CURDATE()), ' Tagen')
                            WHEN DATEDIFF(DATE_ADD(LocalBAMADResult, INTERVAL 2 YEAR), CURDATE()) < 0 THEN 
                                'Bereits abgelaufen'
                        END
                END AS Status
            FROM 
                Identities
            WHERE 
                Type = 'Local'
                AND (
                    (LocalBAMADDelivery IS NULL OR LocalBAMADDelivery = '' OR LocalBAMADDelivery <= LocalBAMADResult OR DATEDIFF(CURDATE(), LocalBAMADDelivery) > 180)
                    AND (
                        LocalBAMADResult IS NULL 
                        OR LocalBAMADResult = ''
                        OR DATEDIFF(DATE_ADD(LocalBAMADResult, INTERVAL 2 YEAR), CURDATE()) <= 30 
                        OR DATEDIFF(DATE_ADD(LocalBAMADResult, INTERVAL 2 YEAR), CURDATE()) < 0
                    )
                )
                AND (LocalBAMADResult IS NOT NULL AND LocalBAMADResult != '' 
                    OR LocalBAMADDelivery IS NOT NULL AND LocalBAMADDelivery != '')
            ORDER BY 
                DATE_ADD(LocalBAMADResult, INTERVAL 2 YEAR) ASC
        ";

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

        // Tabellenkopf erstellen und formatieren
        $headers = ['Nachname', 'Vorname', 'BAMAD-ID', 'Letztes Ergebnis', 'Letzte Abgabe', 'Status'];
        $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
            $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 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['LocalBAMADID']);
            $sheet->setCellValue("D$rowNumber", $record['LocalBAMADResult'] ? date('Y-m-d', strtotime($record['LocalBAMADResult'])) : 'Keine');
            $sheet->setCellValue("E$rowNumber", $record['LocalBAMADDelivery'] ? date('Y-m-d', strtotime($record['LocalBAMADDelivery'])) : 'Keine');
            $sheet->setCellValue("F$rowNumber", $record['Status']);

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

            $rowNumber++;
        }

        if ($rowNumber == 2) { // Falls keine Datensätze 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);
        }

    } catch (PDOException $e) {
        $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="Bericht_BAMAD_' . 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">Militärische Abschirmdienst</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="exportXLSX">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 col-md-9 grid-margin">
        <div class="card">
            <div class="card-header">
                Bericht
            </div>
            <div class="card-body">
                <?php
                try {
                    $sql = "
                                SELECT 
                                *,
                                CASE 
                                    WHEN (LocalBAMADResult IS NULL OR LocalBAMADResult = '') 
                                        AND (LocalBAMADDelivery IS NOT NULL AND LocalBAMADDelivery != '' AND DATEDIFF(CURDATE(), LocalBAMADDelivery) > 180) THEN
                                        CONCAT('Warten auf Ergebnis seit ', DATEDIFF(CURDATE(), LocalBAMADDelivery), ' Tagen')
                                    WHEN LocalBAMADResult IS NULL OR LocalBAMADResult = '' THEN
                                        'Bereits abgelaufen'
                                    WHEN LocalBAMADDelivery IS NULL OR LocalBAMADDelivery = '' THEN
                                        CASE
                                            WHEN DATEDIFF(DATE_ADD(LocalBAMADResult, INTERVAL 2 YEAR), CURDATE()) <= 30 
                                                AND DATEDIFF(DATE_ADD(LocalBAMADResult, INTERVAL 2 YEAR), CURDATE()) >= 0 THEN 
                                                CONCAT('Läuft ab in ', DATEDIFF(DATE_ADD(LocalBAMADResult, INTERVAL 2 YEAR), CURDATE()), ' Tagen')
                                            WHEN DATEDIFF(DATE_ADD(LocalBAMADResult, INTERVAL 2 YEAR), CURDATE()) < 0 THEN 
                                                'Bereits abgelaufen'
                                        END
                                    WHEN LocalBAMADDelivery IS NOT NULL AND LocalBAMADDelivery > LocalBAMADResult THEN
                                        CASE
                                            WHEN DATEDIFF(CURDATE(), LocalBAMADDelivery) > 180 THEN 
                                                CONCAT('Warten auf Ergebnis seit ', DATEDIFF(CURDATE(), LocalBAMADDelivery), ' Tagen')
                                        END
                                    WHEN LocalBAMADDelivery IS NOT NULL AND LocalBAMADDelivery <= LocalBAMADResult THEN
                                        CASE
                                            WHEN DATEDIFF(DATE_ADD(LocalBAMADResult, INTERVAL 2 YEAR), CURDATE()) <= 30 
                                                AND DATEDIFF(DATE_ADD(LocalBAMADResult, INTERVAL 2 YEAR), CURDATE()) >= 0 THEN 
                                                CONCAT('Läuft ab in ', DATEDIFF(DATE_ADD(LocalBAMADResult, INTERVAL 2 YEAR), CURDATE()), ' Tagen')
                                            WHEN DATEDIFF(DATE_ADD(LocalBAMADResult, INTERVAL 2 YEAR), CURDATE()) < 0 THEN 
                                                'Bereits abgelaufen'
                                        END
                                END AS Status
                            FROM 
                                Identities
                            WHERE 
                                Type = 'Local'
                                AND (
                                    (LocalBAMADDelivery IS NULL OR LocalBAMADDelivery = '' OR LocalBAMADDelivery <= LocalBAMADResult OR DATEDIFF(CURDATE(), LocalBAMADDelivery) > 180)
                                    AND (
                                        LocalBAMADResult IS NULL 
                                        OR LocalBAMADResult = ''
                                        OR DATEDIFF(DATE_ADD(LocalBAMADResult, INTERVAL 2 YEAR), CURDATE()) <= 30 
                                        OR DATEDIFF(DATE_ADD(LocalBAMADResult, INTERVAL 2 YEAR), CURDATE()) < 0
                                    )
                                )
                                AND (LocalBAMADResult IS NOT NULL AND LocalBAMADResult != '' 
                                    OR LocalBAMADDelivery IS NOT NULL AND LocalBAMADDelivery != '')
                            ORDER BY 
                                DATE_ADD(LocalBAMADResult, INTERVAL 2 YEAR) ASC;
                            ";

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

                    if ($results) {
                        echo "<table class='table table-bordered'>";
                        echo "<thead><tr><th>Nachname</th><th>Vorname</th><th>Letztes Ergebnis</th><th>Status</th></tr></thead>";

                        foreach ($results as $row) {
                            echo "<tr>";
                            echo "<td>{$row['Surname']}</td>";
                            echo "<td>{$row['Givenname']}</td>";
                            echo "<td>{$row['LocalBAMADResult']}</td>";
                            echo "<td>{$row['Status']}</td>";
                            echo "</tr>";
                        }

                        echo "</table>";
                    } else {
                        echo "Keine BAMAD-Ergebnisse die innerhalb der nächsten 30 Tage ablaufen oder bereits abgelaufen sind.";
                    }
                } catch (PDOException $e) {
                    echo "Datenbankfehler: " . $e->getMessage();
                }
                ?>
            </div>
        </div>
    </div>

    <div class="col-12 col-md-3 grid-margin">
        <div class="card">
            <div class="card-header bg-primary text-white">
                Information
            </div>
            <ul class="list-group list-group-flush">
                <li class="list-group-item"><b>Gültigkeit:</b> Die Ergebnisse sind zwei Jahre gültig.</li>
                <li class="list-group-item"><b>Ablaufüberwachung:</b> Diese Übersicht zeigt alle Ortskräfte, deren
                    Ergebnis:
                    <ul>
                        <li><b>Bereits abgelaufen</b> ist, oder</li>
                        <li><b>In den nächsten 30 Tagen abläuft.</b></li>
                    </ul>
                </li>
                <li class="list-group-item"><b>Neubewertung:</b> Falls bereits eine neue Überprüfung eingeleitet wurde,
                    erscheint diese in der Liste nur, wenn <b>nach 6 Monaten noch kein neues Ergebnis</b> vorliegt.
                </li>
            </ul>
        </div>
    </div>

</div>

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