104 lines
3.7 KiB
PHP
Executable File
104 lines
3.7 KiB
PHP
Executable File
<?php
|
|
|
|
require_once "../phpspreadsheet_1.8.1.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:G1')->applyFromArray(
|
|
array(
|
|
'font' => array(
|
|
'bold' => true
|
|
)
|
|
)
|
|
);
|
|
|
|
|
|
$activeSheet->setCellValue('A1', 'Ranglistenplatz');
|
|
$activeSheet->setCellValue('B1', 'Vorname');
|
|
$activeSheet->setCellValue('C1', 'Nachname');
|
|
$activeSheet->setCellValue('D1', 'Mail');
|
|
$activeSheet->setCellValue('E1', 'ID');
|
|
$activeSheet->setCellValue('F1', 'Testdatum');
|
|
$activeSheet->setCellValue('G1', 'Ergebnis');
|
|
$activeSheet->freezePane('H2');
|
|
|
|
$query = $db->query("SELECT a.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
|
|
, summe
|
|
, id
|
|
, mail
|
|
FROM dvm_kandidat a, dvm_term_kand b, dvm_termine c, dvm_ergebnisse d, dvm_tanpool e
|
|
WHERE a.ka_id = b.ka_id
|
|
AND b.tm_id = c.tm_id
|
|
AND a.ka_id = d.ka_id
|
|
AND b.tp_id = e.tp_id
|
|
AND a.ka_id IN (SELECT distinct ka_id FROM dvm_ergebnisse)
|
|
AND a.ka_id IN (SELECT distinct ka_id FROM dvm_upload WHERE upload_dat != '0000-00-00 00:00:00')
|
|
AND d.bestanden = '1'
|
|
AND e.tan=d.tan
|
|
ORDER BY summe DESC, nachname ASC, vorname ASC
|
|
");
|
|
|
|
if($query->num_rows > 0) {
|
|
$i = 2;
|
|
$rangliste = 0;
|
|
$lfd_nr2 = 0;
|
|
$lfd_nr3 = 0;
|
|
$summevorher = 0;
|
|
|
|
while($row = $query->fetch_assoc()) {
|
|
|
|
$lfd_nr2++;
|
|
|
|
$summe = $row['summe'];
|
|
if($summe == $summevorher){
|
|
$rangliste = $lfd_nr3;
|
|
}else{
|
|
$rangliste = $lfd_nr2;
|
|
$lfd_nr3 = $lfd_nr2;
|
|
}
|
|
$summevorher = $row['summe'];
|
|
|
|
$activeSheet->setCellValue('A'.$i , $rangliste);
|
|
$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[tm_datum] ($row[tm_standort])");
|
|
$activeSheet->setCellValue('G'.$i , $row['summe']);
|
|
$i++;
|
|
}
|
|
|
|
}
|
|
$spreadsheet->getActiveSheet()->setTitle("Rangliste 2");
|
|
$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);
|
|
|
|
$writer = new Xlsx($spreadsheet);
|
|
$filename = 'Rangliste2.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');
|
|
|
|
?>
|