<?php
# Orner phpspreadsheet_1.8.1.0_php-7.0 für Hoschschule verwenden
#require_once "../phpspreadsheet-1.13_php-7.4/vendor/autoload.php";
require_once "../phpspreadsheet_1.8.1.0_php-7.0/vendor/autoload.php";
require_once ("../config.inc.php");

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
#$spreadsheet->getActiveSheet()->getPageSetup()->setFitToWidth(1);
#$spreadsheet->getActiveSheet()->getPageSetup()->setFitToHeight(0);
$activeSheet = $spreadsheet->getActiveSheet();

$activeSheet->getStyle('A1:J2')
    ->applyFromArray(array(
    'font' => array(
        'bold' => true
    )
));

    # Wann wurden die Ranglisten berechnet
    $query_tab_created = $db->query("SELECT date_format(create_time, '%d.%m.%Y - %H:%i:%s') create_time
                                       FROM INFORMATION_SCHEMA.TABLES
                                      WHERE table_schema = 'bpm'
                                        AND table_name = 'bpm_tmp_jahrgang_kandidat'
                                    ");
    $row_tab_created   = $query_tab_created->fetch_array();

$activeSheet->setCellValue('A1', 'Stand:');
$activeSheet->setCellValue('B1', "$row_tab_created[create_time]");
$activeSheet->setCellValue('A2', 'Ranglistenplatz');
$activeSheet->setCellValue('B2', 'Vorname');
$activeSheet->setCellValue('C2', 'Nachname');
$activeSheet->setCellValue('D2', 'Mail');
$activeSheet->setCellValue('E2', 'ID');
$activeSheet->setCellValue('F2', 'HZB');
$activeSheet->setCellValue('G2', 'Rangliste1');
$activeSheet->setCellValue('H2', 'Testergebnis');
$activeSheet->setCellValue('I2', 'Rangliste2');
$activeSheet->setCellValue('J2', 'Berechnung Rangliste3');
$activeSheet->freezePane('A3');

$query = $db->query("SELECT ka_id
                          , vorname
                          , nachname
                          , date_format(tm_datum, '%d.%m.%Y um %H:%i Uhr') tm_datum
                          , CASE tm_standort
		            WHEN 'L' THEN 'Ludwigsburg'
		            WHEN 'K' THEN 'Kehl'
		            ELSE ''
		            END as tm_standort
		          , testergebnis
		          , id
		          , mail
		          , hzb
		          , testergebnis
		          , rang1
		          , rang2
		          , rang3
		          , (rang1+rang2)/2 rang3_berech
                       FROM bpm_tmp_jahrgang_kandidat
                      ORDER BY rang3 ASC
                    ");

if ($query->num_rows > 0)
{
    $i = 3;

    while ($row = $query->fetch_assoc())
    {

        $activeSheet->setCellValue('A' . $i, "$row[rang3]");
        $activeSheet->setCellValue('B' . $i, "$row[nachname]");
        $activeSheet->setCellValue('C' . $i, "$row[vorname]");
        $activeSheet->setCellValue('D' . $i, "$row[mail]");
        $activeSheet->setCellValue('E' . $i, "$row[id]");
        $activeSheet->setCellValue('F' . $i, "$row[hzb]");
        $activeSheet->setCellValue('G' . $i, "$row[rang1]");
        $activeSheet->setCellValue('H' . $i, "$row[testergebnis]");
        $activeSheet->setCellValue('I' . $i, "$row[rang2]");
        $activeSheet->setCellValue('J' . $i, '=(G' . $i . '+I' . $i . ')/2');
        $i++;
    }

}
$spreadsheet->getActiveSheet()
    ->setTitle("Rangliste 3");
$spreadsheet->getActiveSheet()
    ->setSelectedCell('A1');
$spreadsheet->getActiveSheet()
    ->getColumnDimension('A')
    ->setAutoSize(true);
$spreadsheet->getActiveSheet()
    ->getColumnDimension('B')
    ->setAutoSize(true);
$spreadsheet->getActiveSheet()
    ->getColumnDimension('C')
    ->setAutoSize(true);
$spreadsheet->getActiveSheet()
    ->getColumnDimension('D')
    ->setAutoSize(true);
$spreadsheet->getActiveSheet()
    ->getColumnDimension('E')
    ->setAutoSize(true);
$spreadsheet->getActiveSheet()
    ->getColumnDimension('F')
    ->setAutoSize(true);
$spreadsheet->getActiveSheet()
    ->getColumnDimension('G')
    ->setAutoSize(true);
$spreadsheet->getActiveSheet()
    ->getColumnDimension('H')
    ->setAutoSize(true);
$spreadsheet->getActiveSheet()
    ->getColumnDimension('I')
    ->setAutoSize(true);
$spreadsheet->getActiveSheet()
    ->getColumnDimension('J')
    ->setAutoSize(true);

$writer = new Xlsx($spreadsheet);
$filename = 'Rangliste3.xlsx';

header('Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename=' . $filename);
header('Cache-Control: max-age=0');
$writer->save('php://output');

?>