318 lines
14 KiB
PHP
Executable File
318 lines
14 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:AK1')->applyFromArray(array(
|
|
'font' => array(
|
|
'bold' => true
|
|
)
|
|
));
|
|
|
|
|
|
$activeSheet->setCellValue('A1', 'Kurznotiz');
|
|
$activeSheet->setCellValue('B1', 'Bewerbernr.');
|
|
$activeSheet->setCellValue('C1', 'Anrede');
|
|
$activeSheet->setCellValue('D1', 'Vorname');
|
|
$activeSheet->setCellValue('E1', 'Nachname');
|
|
$activeSheet->setCellValue('F1', 'Ausweis');
|
|
$activeSheet->setCellValue('G1', 'Mail');
|
|
$activeSheet->setCellValue('H1', 'Straße');
|
|
$activeSheet->setCellValue('I1', 'PLZ');
|
|
$activeSheet->setCellValue('J1', 'Ort');
|
|
$activeSheet->setCellValue('K1', 'Geburtsdatum');
|
|
$activeSheet->setCellValue('L1', 'Testdatum');
|
|
$activeSheet->setCellValue('M1', 'Zuständige Hochschule');
|
|
$activeSheet->setCellValue('M1', 'Wunsch Hochschule');
|
|
$activeSheet->setCellValue('O1', 'Zugewiesene Hochschule');
|
|
|
|
$activeSheet->setCellValue('P1', 'Satzergänzung');
|
|
$activeSheet->setCellValue('Q1', 'Gemeinsamkeiten');
|
|
$activeSheet->setCellValue('R1', 'Rechenaufgaben');
|
|
$activeSheet->setCellValue('S1', 'Zahlenreihen');
|
|
$activeSheet->setCellValue('T1', 'Rechenzeichen');
|
|
$activeSheet->setCellValue('U1', 'Matrizen');
|
|
$activeSheet->setCellValue('V1', 'Merkfähigkeit verbal');
|
|
$activeSheet->setCellValue('W1', 'Testergebnis');
|
|
|
|
$activeSheet->setCellValue('X1', 'Verkürzung');
|
|
$activeSheet->setCellValue('Y1', 'Schwerbehinderung');
|
|
$activeSheet->setCellValue('Z1', 'PDF Upload HZB');
|
|
$activeSheet->setCellValue('AA1', 'PDF Upload Zeugnisse');
|
|
$activeSheet->setCellValue('AB1', 'Lebenslauf');
|
|
$activeSheet->setCellValue('AC1', 'Note HZB');
|
|
$activeSheet->setCellValue('AD1', 'Notenwert HZB');
|
|
$activeSheet->setCellValue('AE1', 'Note Durchschnitt');
|
|
$activeSheet->setCellValue('AF1', 'Notewert Durchschnitt');
|
|
$activeSheet->setCellValue('AG1', 'Bezeichnung Ausbildungsstelle');
|
|
$activeSheet->setCellValue('AH1', 'Straße Ausbildungsstelle');
|
|
$activeSheet->setCellValue('AI1', 'PLZ Ausbildungsstelle');
|
|
$activeSheet->setCellValue('AJ1', 'Ort Ausbildungsstelle');
|
|
$activeSheet->setCellValue('AK1', 'Ranglistenplatz 3');
|
|
$activeSheet->freezePane('A2');
|
|
|
|
$query = $db->query("SELECT a.ka_id
|
|
,CASE anrede
|
|
WHEN 'W' THEN 'Frau'
|
|
WHEN 'M' THEN 'Herr'
|
|
ELSE '' END as Anrede
|
|
,perso
|
|
,vorname
|
|
,nachname
|
|
,mail
|
|
,str
|
|
,plz
|
|
,ort
|
|
,mail
|
|
,date_format(gebdat, '%d.%m.%Y') Gebdat
|
|
,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
|
|
,behinderung
|
|
,verkuerzung
|
|
,a.hochschule
|
|
,kurznotiz
|
|
,e.tan
|
|
FROM bpm_kandidat a, bpm_term_kand b, bpm_termine c, bpm_ergebnisse d, bpm_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 bpm_ergebnisse)
|
|
AND a.ka_id IN (SELECT distinct ka_id FROM bpm_upload WHERE upload_dat != '0000-00-00 00:00:00')
|
|
AND d.bestanden = '1'
|
|
AND e.tan=d.tan
|
|
ORDER BY nachname ASC, vorname ASC
|
|
");
|
|
|
|
if ($query->num_rows > 0) {
|
|
$i = 2;
|
|
while ($row = $query->fetch_assoc()) {
|
|
|
|
$result_test = $db->query("SELECT satzerg, gemeinsamkeiten, rechenaufgaben, zahlenreihen, rechenzeichen, matrizen, merkf, summe
|
|
FROM bpm_ergebnisse
|
|
WHERE ka_id = '$row[ka_id]'
|
|
AND tan = '$row[tan]'
|
|
AND bestanden = '1'");
|
|
$row_test = $result_test->fetch_array();
|
|
|
|
|
|
$result_rang3 = $db->query("SELECT rang3
|
|
FROM bpm_tmp_jahrgang_kandidat
|
|
WHERE ka_id = '$row[ka_id]'
|
|
");
|
|
$row_rang3 = $result_rang3->fetch_array();
|
|
|
|
if ($row['verkuerzung'] == 'J') {
|
|
$verkuerzung = 'Ja';
|
|
} else {
|
|
$verkuerzung = '';
|
|
}
|
|
if ($row['behinderung'] == 'J') {
|
|
$behinderung = 'Ja';
|
|
} else {
|
|
$behinderung = '';
|
|
}
|
|
|
|
$result_anzhzb = $db->query("SELECT count(*) Anz
|
|
FROM bpm_upload
|
|
WHERE beschreibung = 'HZB'
|
|
AND ka_id = '$row[ka_id]'");
|
|
$row_anzhzb = $result_anzhzb->fetch_array();
|
|
if ($row_anzhzb['Anz'] > 0) {
|
|
$pdf_hzb = "Ja";
|
|
} else {
|
|
$pdf_hzb = "Nein";
|
|
}
|
|
|
|
$result_anzzeug = $db->query("SELECT count(*) Anz
|
|
FROM bpm_upload
|
|
WHERE beschreibung like 'Zeugnis%'
|
|
AND ka_id = '$row[ka_id]'");
|
|
$row_anzzeug = $result_anzzeug->fetch_array();
|
|
|
|
$result_anzleben = $db->query("SELECT count(*) Anz
|
|
FROM bpm_upload
|
|
WHERE beschreibung = 'Lebenslauf'
|
|
AND ka_id = '$row[ka_id]'");
|
|
$row_anzleben = $result_anzleben->fetch_array();
|
|
if ($row_anzleben['Anz'] >= 1) {
|
|
$leben = "Ja";
|
|
} else {
|
|
$leben = "Nein";
|
|
}
|
|
|
|
$result_notehzb = $db->query("SELECT hzb, zeugnisschnitt
|
|
FROM bpm_note
|
|
WHERE ka_id = '$row[ka_id]'");
|
|
$row_notehzb = $result_notehzb->fetch_array();
|
|
if ($row_notehzb['hzb'] == "0.00" or $row_notehzb['hzb'] == "") {
|
|
$notehzb = "Nein";
|
|
$note_hzb = '';
|
|
} else {
|
|
$notehzb = "Ja";
|
|
$note_hzb = number_format($row_notehzb['hzb'], 1, ',', '.');
|
|
}
|
|
|
|
if ($row_notehzb['zeugnisschnitt'] == "0.00" or $row_notehzb['zeugnisschnitt'] == "") {
|
|
$noteschnitt = "Nein";
|
|
$note_wert = '';
|
|
} else {
|
|
$noteschnitt = "Ja";
|
|
$note_wert = number_format($row_notehzb['zeugnisschnitt'], 1, ',', '.');
|
|
}
|
|
|
|
$result_dst = $db->query("SELECT bez, str, plz, ort
|
|
FROM bpm_dienststellen a
|
|
WHERE dst_id IN (SELECT dst_id
|
|
FROM bpm_dst_wunsch b
|
|
WHERE a.dst_id = b.dst_id
|
|
AND b.ka_id = '$row[ka_id]'
|
|
AND zusage ='J')");
|
|
$row_dst = $result_dst->fetch_array();
|
|
if ($row_dst['bez'] != "") {
|
|
$dst_bez = $row_dst['bez'];
|
|
$dst_str = $row_dst['str'];
|
|
$dst_plz = $row_dst['plz'];
|
|
$dst_ort = $row_dst['ort'];
|
|
} else {
|
|
$dst_bez = '';
|
|
$dst_str = '';
|
|
$dst_plz = '';
|
|
$dst_ort = '';
|
|
}
|
|
|
|
$result_erwhs = $db->query("SELECT wunsch_hs, zugewiesene_hs
|
|
FROM bpm_erw_daten
|
|
WHERE ka_id = '$row[ka_id]'
|
|
");
|
|
$row_erwhs = $result_erwhs->fetch_array();
|
|
|
|
if($row_erwhs['wunsch_hs'] == 'E'){
|
|
$wunsch_hs = "Egal";
|
|
}elseif($row_erwhs['wunsch_hs'] == 'K'){
|
|
$wunsch_hs = "Kehl";
|
|
}elseif($row_erwhs['wunsch_hs'] == 'L'){
|
|
$wunsch_hs = "Ludwigsburg";
|
|
}elseif($row_erwhs['wunsch_hs'] == ''){
|
|
$wunsch_hs = "";
|
|
}
|
|
|
|
if($row_erwhs['zugewiesene_hs'] == 'O'){
|
|
$zugewiesene_hs = "Offen";
|
|
}elseif($row_erwhs['zugewiesene_hs'] == 'K'){
|
|
$zugewiesene_hs = "Kehl";
|
|
}elseif($row_erwhs['zugewiesene_hs'] == 'L'){
|
|
$zugewiesene_hs = "Ludwigsburg";
|
|
}elseif($row_erwhs['zugewiesene_hs'] == ''){
|
|
$zugewiesene_hs = "";
|
|
}
|
|
|
|
|
|
$activeSheet->setCellValue('A' . $i, $row['kurznotiz']);
|
|
$activeSheet->setCellValue('B' . $i, $row['ka_id']);
|
|
$activeSheet->setCellValue('C' . $i, "$row[Anrede]");
|
|
$activeSheet->setCellValue('D' . $i, "$row[vorname]");
|
|
$activeSheet->setCellValue('E' . $i, "$row[nachname]");
|
|
$activeSheet->setCellValue('F' . $i, "$row[perso]");
|
|
$activeSheet->setCellValue('G' . $i, "$row[mail]");
|
|
$activeSheet->setCellValue('H' . $i, "$row[str]");
|
|
$activeSheet->setCellValue('I' . $i, "$row[plz]");
|
|
$activeSheet->setCellValue('J' . $i, "$row[ort]");
|
|
$activeSheet->setCellValue('K' . $i, "$row[Gebdat]");
|
|
$activeSheet->setCellValue('L' . $i, "$row[tm_datum] ($row[tm_standort])");
|
|
$activeSheet->setCellValue('M' . $i, $row['hochschule']);
|
|
$activeSheet->setCellValue('N' . $i, $wunsch_hs);
|
|
$activeSheet->setCellValue('O' . $i, $zugewiesene_hs);
|
|
|
|
$activeSheet->setCellValue('P' . $i, "$row_test[satzerg]");
|
|
$activeSheet->setCellValue('Q' . $i, "$row_test[gemeinsamkeiten]");
|
|
$activeSheet->setCellValue('R' . $i, "$row_test[rechenaufgaben]");
|
|
$activeSheet->setCellValue('S' . $i, "$row_test[zahlenreihen]");
|
|
$activeSheet->setCellValue('T' . $i, "$row_test[rechenzeichen]");
|
|
$activeSheet->setCellValue('U' . $i, "$row_test[matrizen]");
|
|
$activeSheet->setCellValue('V' . $i, "$row_test[merkf]");
|
|
$activeSheet->setCellValue('W' . $i, "$row_test[summe] / 130");
|
|
|
|
$activeSheet->setCellValue('X' . $i, $verkuerzung);
|
|
$activeSheet->setCellValue('Y' . $i, $behinderung);
|
|
$activeSheet->setCellValue('Z' . $i, $pdf_hzb);
|
|
$activeSheet->setCellValue('AA' . $i, "$row_anzzeug[Anz] / 2");
|
|
$activeSheet->setCellValue('AB' . $i, $leben);
|
|
$activeSheet->setCellValue('AC' . $i, $notehzb);
|
|
$activeSheet->setCellValue('AD' . $i, $note_hzb);
|
|
$activeSheet->setCellValue('AE' . $i, $noteschnitt);
|
|
$activeSheet->setCellValue('AF' . $i, $note_wert);
|
|
$activeSheet->setCellValue('AG' . $i, $dst_bez);
|
|
$activeSheet->setCellValue('AH' . $i, $dst_str);
|
|
$activeSheet->setCellValue('AI' . $i, $dst_plz);
|
|
$activeSheet->setCellValue('AJ' . $i, $dst_ort);
|
|
$activeSheet->setCellValue('AK' . $i, $row_rang3['rang3']);
|
|
$i++;
|
|
}
|
|
|
|
}
|
|
|
|
$spreadsheet->getActiveSheet()->setTitle("Datencheck");
|
|
$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);
|
|
$spreadsheet->getActiveSheet()->getColumnDimension('K')->setAutoSize(true);
|
|
$spreadsheet->getActiveSheet()->getColumnDimension('L')->setAutoSize(true);
|
|
$spreadsheet->getActiveSheet()->getColumnDimension('M')->setAutoSize(true);
|
|
$spreadsheet->getActiveSheet()->getColumnDimension('N')->setAutoSize(true);
|
|
$spreadsheet->getActiveSheet()->getColumnDimension('O')->setAutoSize(true);
|
|
$spreadsheet->getActiveSheet()->getColumnDimension('P')->setAutoSize(true);
|
|
$spreadsheet->getActiveSheet()->getColumnDimension('Q')->setAutoSize(true);
|
|
$spreadsheet->getActiveSheet()->getColumnDimension('R')->setAutoSize(true);
|
|
$spreadsheet->getActiveSheet()->getColumnDimension('S')->setAutoSize(true);
|
|
$spreadsheet->getActiveSheet()->getColumnDimension('T')->setAutoSize(true);
|
|
$spreadsheet->getActiveSheet()->getColumnDimension('U')->setAutoSize(true);
|
|
$spreadsheet->getActiveSheet()->getColumnDimension('V')->setAutoSize(true);
|
|
$spreadsheet->getActiveSheet()->getColumnDimension('W')->setAutoSize(true);
|
|
$spreadsheet->getActiveSheet()->getColumnDimension('X')->setAutoSize(true);
|
|
$spreadsheet->getActiveSheet()->getColumnDimension('Y')->setAutoSize(true);
|
|
$spreadsheet->getActiveSheet()->getColumnDimension('Z')->setAutoSize(true);
|
|
$spreadsheet->getActiveSheet()->getColumnDimension('AA')->setAutoSize(true);
|
|
$spreadsheet->getActiveSheet()->getColumnDimension('AB')->setAutoSize(true);
|
|
$spreadsheet->getActiveSheet()->getColumnDimension('AC')->setAutoSize(true);
|
|
$spreadsheet->getActiveSheet()->getColumnDimension('AD')->setAutoSize(true);
|
|
$spreadsheet->getActiveSheet()->getColumnDimension('AE')->setAutoSize(true);
|
|
$spreadsheet->getActiveSheet()->getColumnDimension('AF')->setAutoSize(true);
|
|
$spreadsheet->getActiveSheet()->getColumnDimension('AG')->setAutoSize(true);
|
|
$spreadsheet->getActiveSheet()->getColumnDimension('AH')->setAutoSize(true);
|
|
$spreadsheet->getActiveSheet()->getColumnDimension('AI')->setAutoSize(true);
|
|
$spreadsheet->getActiveSheet()->getColumnDimension('AJ')->setAutoSize(true);
|
|
$spreadsheet->getActiveSheet()->getColumnDimension('AK')->setAutoSize(true);
|
|
$spreadsheet->getActiveSheet()->setSelectedCell('A1');
|
|
$writer = new Xlsx($spreadsheet);
|
|
$filename = 'Datencheck.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');
|
|
|
|
?>
|