131 lines
4.4 KiB
PHP
Executable File
131 lines
4.4 KiB
PHP
Executable File
<?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');
|
|
|
|
?>
|