365 lines
15 KiB
PHP
Executable File
365 lines
15 KiB
PHP
Executable File
<?php
|
|
if(!isset($_SESSION)) { session_start(); }
|
|
include_once '../classes/TestProjektSmarty.class_subdir.php';
|
|
require_once("../config.inc.php");
|
|
$templatename = substr(basename($_SERVER['PHP_SELF']),0,-3)."html";
|
|
$smarty = new SmartyAdmin();
|
|
require_once "../language/german.inc.php";
|
|
|
|
if (isset($_GET['action'])) {
|
|
$action = $_GET['action'];
|
|
} else {
|
|
$action = '';
|
|
}
|
|
|
|
|
|
if ($action == '') {
|
|
$query0 = "SELECT distinct substr(id,-5) Jahrgang FROM `bpm_kandidat` ORDER BY `Jahrgang` DESC limit 2";
|
|
$result0 = $db->query($query0) or die("Cannot execute query1");
|
|
while ($row0 = $result0->fetch_array()) {
|
|
|
|
$id = $row0['Jahrgang'];
|
|
#Anzahl der getesteten Kandidaten
|
|
|
|
|
|
$result_anzget = $db->query("SELECT count(distinct ka_id) Anz
|
|
FROM bpm_term_kand a, bpm_tanpool b
|
|
WHERE a.tp_id = b.tp_id
|
|
AND a.ka_id in (SELECT ka_id from bpm_kandidat where id like '%".$id."')
|
|
AND b.login_dat != '0000-00-00 00:00:00'");
|
|
$row_anzget = $result_anzget->fetch_array();
|
|
#$smarty->assign('datenchk_anzget', $row_anzget['Anz']);
|
|
|
|
#Anzahl der Kandidaten mit ausstehendem Test
|
|
$result_aussttest = $db->query("SELECT count(*) Anz
|
|
FROM bpm_term_kand a, bpm_tanpool b
|
|
WHERE a.tp_id=b.tp_id
|
|
AND verw_dat != '0000-00-00 00:00:00'
|
|
AND a.ka_id in (SELECT ka_id from bpm_kandidat where id like '%".$id."')
|
|
AND b.login_dat = '0000-00-00 00:00:00'");
|
|
$row_aussttest = $result_aussttest->fetch_array();
|
|
#$smarty->assign('datenchk_aussttest', $row_aussttest['Anz']);
|
|
|
|
#Anzahl aller Anmeldung
|
|
$result_alleranm = $db->query("SELECT count(distinct ka_id) Anz
|
|
FROM bpm_term_kand a, bpm_tanpool b
|
|
WHERE a.tp_id = b.tp_id
|
|
AND a.ka_id in (SELECT ka_id from bpm_kandidat where id like '%".$id."')
|
|
AND b.verw_dat != '0000-00-00 00:00:00'");
|
|
$row_alleranm = $result_alleranm->fetch_array();
|
|
#$smarty->assign('datenchk_alleranm', $row_alleranm['Anz']);
|
|
|
|
#Anzahl Durchfaller - komplizierter. Wenn er nach dem ersten Mal durchgefallen doch bestanden hat, gilt er nicht als Durchfaller
|
|
$query = "SELECT ka_id, max(tp_id) tp_id
|
|
FROM bpm_term_kand
|
|
GROUP BY ka_id
|
|
";
|
|
|
|
$result = $db->query($query) or die("Cannot execute query1");
|
|
$zaehler = 0;
|
|
while ($row = $result->fetch_array()) {
|
|
$result_tan = $db->query("SELECT tan
|
|
FROM bpm_tanpool
|
|
WHERE tp_id = $row[tp_id]");
|
|
$row_tan = $result_tan->fetch_array();
|
|
|
|
$result_bestanden = $db->query("SELECT bestanden
|
|
FROM bpm_ergebnisse
|
|
WHERE ka_id = $row[ka_id]
|
|
AND ka_id in (SELECT ka_id from bpm_kandidat where id like '%".$id."')
|
|
AND tan = '$row_tan[tan]'");
|
|
$row_bestanden = $result_bestanden->fetch_array();
|
|
if($row_bestanden['bestanden'] == 0 and $row_bestanden['bestanden'] != ''){
|
|
$zaehler++;
|
|
}
|
|
}
|
|
# $smarty->assign('datenchk_anzdurchfaller', $zaehler);
|
|
|
|
#Anzahl aller Bewerberungen
|
|
$result_anzbewerb = $db->query("SELECT count(distinct ka_id) Anz
|
|
FROM bpm_upload
|
|
WHERE upload_dat != '0000-00-00 00:00:00'
|
|
AND ka_id in (SELECT ka_id from bpm_kandidat where id like '%".$id."')");
|
|
$row_anzbewerb = $result_anzbewerb->fetch_array();
|
|
# $smarty->assign('datenchk_anzbewerbungen', $row_anzbewerb['Anz']);
|
|
|
|
$row0['datenchk_anzget'] = $row_anzget['Anz'];
|
|
$row0['datenchk_aussttest'] = $row_aussttest['Anz'];
|
|
$row0['datenchk_alleranm'] = $row_alleranm['Anz'];
|
|
$row0['datenchk_anzdurchfaller'] = $zaehler;
|
|
$row0['datenchk_anzbewerbungen'] = $row_anzbewerb['Anz'];
|
|
$table_data[] = $row0;
|
|
|
|
}
|
|
$smarty->assign('table_data', $table_data);
|
|
/*
|
|
# Gespeicherte Werte
|
|
$query1 = "SELECT a.ka_id, perso, vorname, nachname, mail, 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
|
|
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 kurznotiz ASC, nachname ASC, vorname ASC
|
|
";
|
|
|
|
$result1 = $db->query($query1) or die("Cannot execute query1");
|
|
$row_cnt = $result1->num_rows;
|
|
$smarty->assign('dokverw_anzdb', $row_cnt);
|
|
|
|
while ($row1 = $result1->fetch_array()) {
|
|
|
|
$result_test = $db->query("SELECT summe
|
|
FROM bpm_ergebnisse
|
|
WHERE ka_id = '$row1[ka_id]'
|
|
AND bestanden = '1'");
|
|
$row_test = $result_test->fetch_array();
|
|
|
|
$result_perso = $db->query("SELECT count(*) Anz
|
|
FROM bpm_kandidat
|
|
WHERE perso = '$row1[perso]'
|
|
");
|
|
$row_perso = $result_perso->fetch_array();
|
|
if($row_perso['Anz'] > 1){
|
|
$perso_alert = "red";
|
|
}else{
|
|
$perso_alert = "black";
|
|
}
|
|
|
|
$result_rang3 = $db->query("SELECT rang3
|
|
FROM bpm_tmp_jahrgang_kandidat
|
|
WHERE ka_id = '$row1[ka_id]'
|
|
");
|
|
$row_rang3 = $result_rang3->fetch_array();
|
|
|
|
|
|
$result_anzhzb = $db->query("SELECT count(*) Anz
|
|
FROM bpm_upload
|
|
WHERE beschreibung = 'HZB'
|
|
AND ka_id = '$row1[ka_id]'");
|
|
$row_anzhzb = $result_anzhzb->fetch_array();
|
|
if($row_anzhzb['Anz'] > 0){
|
|
$image_hzb = "ok.gif";
|
|
}else{
|
|
$image_hzb = "cross.jpg";
|
|
}
|
|
|
|
$result_anzzusage = $db->query("SELECT b.bez
|
|
FROM bpm_dst_wunsch a, bpm_dienststellen b
|
|
WHERE a.dst_id = b.dst_id
|
|
AND a.zusage = 'J'
|
|
AND a.ka_id = '$row1[ka_id]'");
|
|
|
|
$row_anzzusage = $result_anzzusage->fetch_array();
|
|
if($row_anzzusage['bez'] != ''){
|
|
$image_zusage = "ok.gif";
|
|
$dstzusagebez = $row_anzzusage['bez'];
|
|
}else{
|
|
$image_zusage = "cross.jpg";
|
|
$dstzusagebez = "";
|
|
}
|
|
|
|
# Wunschhochschule
|
|
$result_wunschhs = $db->query("SELECT wunsch_hs, zugewiesene_hs
|
|
FROM bpm_erw_daten
|
|
WHERE ka_id = '$row1[ka_id]'");
|
|
$row_wunschhs = $result_wunschhs->fetch_array();
|
|
if($row_wunschhs['wunsch_hs'] == 'E'){
|
|
$wunsch_hs = "Egal";
|
|
}elseif($row_wunschhs['wunsch_hs'] == 'K'){
|
|
$wunsch_hs = "Kehl";
|
|
}elseif($row_wunschhs['wunsch_hs'] == 'L'){
|
|
$wunsch_hs = "Ludwigsburg";
|
|
}elseif($row_wunschhs['wunsch_hs'] == ''){
|
|
$wunsch_hs = "";
|
|
}
|
|
|
|
if($row_wunschhs['zugewiesene_hs'] == 'O'){
|
|
$zugewiesene_hs = "Offen";
|
|
}elseif($row_wunschhs['zugewiesene_hs'] == 'K'){
|
|
$zugewiesene_hs = "Kehl";
|
|
}elseif($row_wunschhs['zugewiesene_hs'] == 'L'){
|
|
$zugewiesene_hs = "Ludwigsburg";
|
|
}elseif($row_wunschhs['zugewiesene_hs'] == ''){
|
|
$zugewiesene_hs = "";
|
|
}
|
|
|
|
|
|
$result_anzzeug = $db->query("SELECT count(*) Anz
|
|
FROM bpm_upload
|
|
WHERE beschreibung like 'Zeugnis%'
|
|
AND ka_id = '$row1[ka_id]'");
|
|
$row_anzzeug = $result_anzzeug->fetch_array();
|
|
if($row_anzzeug['Anz'] >= 2){
|
|
$image_zeug = "ok.gif";
|
|
}else{
|
|
$image_zeug = "cross.jpg";
|
|
}
|
|
|
|
$result_anzleben = $db->query("SELECT count(*) Anz
|
|
FROM bpm_upload
|
|
WHERE beschreibung = 'Lebenslauf'
|
|
AND ka_id = '$row1[ka_id]'");
|
|
$row_anzleben = $result_anzleben->fetch_array();
|
|
if($row_anzleben['Anz'] >= 1){
|
|
$image_leben = "ok.gif";
|
|
}else{
|
|
$image_leben = "cross.jpg";
|
|
}
|
|
|
|
$result_notehzb = $db->query("SELECT hzb, zeugnisschnitt
|
|
FROM bpm_note
|
|
WHERE ka_id = '$row1[ka_id]'");
|
|
$row_notehzb = $result_notehzb->fetch_array();
|
|
if($row_notehzb['hzb'] == "0.00" or $row_notehzb['hzb'] == ""){
|
|
$image_notehzb = "cross.jpg";
|
|
$note_hzb = '';
|
|
}else{
|
|
$image_notehzb = "ok.gif";
|
|
$note_hzb = number_format($row_notehzb['hzb'], 1, ',', '.');
|
|
}
|
|
|
|
if($row_notehzb['zeugnisschnitt'] == "0.00" or $row_notehzb['zeugnisschnitt'] == ""){
|
|
$image_noteschnitt = "cross.jpg";
|
|
$note_wert = '';
|
|
}else{
|
|
$image_noteschnitt = "ok.gif";
|
|
$note_wert = number_format($row_notehzb['zeugnisschnitt'], 1, ',', '.');
|
|
}
|
|
|
|
$row1['image_zusage'] = $image_zusage;
|
|
$row1['perso_alert'] = $perso_alert;
|
|
$row1['rang3'] = $row_rang3['rang3'];
|
|
$row1['dstzusagebez'] = $dstzusagebez;
|
|
$row1['image_noteschnitt']= $image_noteschnitt;
|
|
$row1['note_wert'] = $note_wert;
|
|
$row1['image_notehzb'] = $image_notehzb;
|
|
$row1['note_hzb'] = $note_hzb;
|
|
$row1['image_leben'] = $image_leben;
|
|
$row1['image_zeug'] = $image_zeug;
|
|
$row1['anz_zeug'] = $row_anzzeug['Anz'];
|
|
$row1['image_hzb'] = $image_hzb;
|
|
$row1['testergebnis'] = $row_test['summe'];
|
|
$row1['wunsch_hs'] = $wunsch_hs;
|
|
$row1['zugewiesene_hs'] = $zugewiesene_hs;
|
|
$table_data1[] = $row1;
|
|
}
|
|
|
|
echo "<pre>";
|
|
#print_r($table_data1);
|
|
echo "</pre>";
|
|
|
|
$kurznotiz = array_column($table_data1, 'kurznotiz');
|
|
$ka_id = array_column($table_data1, 'ka_id');
|
|
$nachname = array_column($table_data1, 'nachname');
|
|
$rang3 = array_column($table_data1, 'rang3');
|
|
$hochschule = array_column($table_data1, 'hochschule');
|
|
$wunsch_hs = array_column($table_data1, 'wunsch_hs');
|
|
$zugewiesene_hs = array_column($table_data1, 'zugewiesene_hs');
|
|
$testergebnis = array_column($table_data1, 'testergebnis');
|
|
$verkuerzung = array_column($table_data1, 'verkuerzung');
|
|
$behinderung = array_column($table_data1, 'behinderung');
|
|
$zusage = array_column($table_data1, 'dstzusagebez');
|
|
$note_hzb = array_column($table_data1, 'note_hzb');
|
|
$note_wert = array_column($table_data1, 'note_wert');
|
|
$mail = array_column($table_data1, 'mail');
|
|
$image_hzb = array_column($table_data1, 'image_hzb');
|
|
$anz_zeug = array_column($table_data1, 'anz_zeug');
|
|
$image_leben = array_column($table_data1, 'image_leben');
|
|
$image_notehzb = array_column($table_data1, 'image_notehzb');
|
|
$image_noteschnitt = array_column($table_data1, 'image_noteschnitt');
|
|
|
|
#setlocale ( LC_COLLATE ,'de_DE.utf8');
|
|
setlocale (LC_ALL, 'de_DE@euro', 'de_DE', 'de', 'ge');
|
|
|
|
if($_GET['order'] == 4){
|
|
$order = 3;
|
|
}else{
|
|
$order = 4;
|
|
}
|
|
|
|
if(isset($_GET['sort'])){
|
|
$sort = $_GET['sort'];
|
|
}else{
|
|
$sort = "kurznotiz";
|
|
}
|
|
if($order == 3){
|
|
$smarty->assign('check_order_klartext', 'Absteigend');
|
|
}else{
|
|
$smarty->assign('check_order_klartext', 'Aufsteigend');
|
|
}
|
|
|
|
if($sort == "kurznotiz"){
|
|
$smarty->assign('check_sort_klartext', 'Kurznotiz');
|
|
}elseif($sort == "ka_id"){
|
|
$smarty->assign('check_sort_klartext', 'Bew. Nr.');
|
|
}elseif($sort == "nachname"){
|
|
$smarty->assign('check_sort_klartext', 'Name');
|
|
}elseif($sort == "perso"){
|
|
$smarty->assign('check_sort_klartext', 'Ausweis');
|
|
}elseif($sort == "rang3"){
|
|
$smarty->assign('check_sort_klartext', 'Ranglistenplatz 3');
|
|
}elseif($sort == "hochschule"){
|
|
$smarty->assign('check_sort_klartext', 'zuständige Hochschule');
|
|
}elseif($sort == "wunsch_hs"){
|
|
$smarty->assign('check_sort_klartext', 'Wunsch Hochschule');
|
|
}elseif($sort == "zugewiesene_hs"){
|
|
$smarty->assign('check_sort_klartext', 'Zugewiesene Hochschule');
|
|
}elseif($sort == "testergebnis"){
|
|
$smarty->assign('check_sort_klartext', 'Testergebnis');
|
|
}elseif($sort == "verkuerzung"){
|
|
$smarty->assign('check_sort_klartext', 'Verkürzung');
|
|
}elseif($sort == "behinderung"){
|
|
$smarty->assign('check_sort_klartext', 'Schwerbehinderung');
|
|
}elseif($sort == "zusage"){
|
|
$smarty->assign('check_sort_klartext', 'Stellenzusage');
|
|
}elseif($sort == "note_hzb"){
|
|
$smarty->assign('check_sort_klartext', 'Notenwert HZB');
|
|
}elseif($sort == "note_wert"){
|
|
$smarty->assign('check_sort_klartext', 'Notenwert Durchschnitt');
|
|
}elseif($sort == "mail"){
|
|
$smarty->assign('check_sort_klartext', 'Mailadresse');
|
|
}elseif($sort == "image_hzb"){
|
|
$smarty->assign('check_sort_klartext', 'PDF Upload HZB');
|
|
}elseif($sort == "anz_zeug"){
|
|
$smarty->assign('check_sort_klartext', 'PDF Upload Zeugnisse');
|
|
}elseif($sort == "image_leben"){
|
|
$smarty->assign('check_sort_klartext', 'Lebenslauf');
|
|
}elseif($sort == "image_notehzb"){
|
|
$smarty->assign('check_sort_klartext', 'Note HZB');
|
|
}elseif($sort == "image_noteschnitt"){
|
|
$smarty->assign('check_sort_klartext', 'Note Durchschnitt');
|
|
|
|
}
|
|
|
|
#Sortiertyp
|
|
if($sort == 'kurznotiz' OR $sort == 'perso' OR $sort == 'nachname' OR $sort == 'hochschule' OR $sort == 'wunsch_hs' OR $sort == 'zugewiesene_hs' OR $sort == 'note_hzb' OR $sort == 'note_wert'){
|
|
$sorttyp = SORT_LOCALE_STRING;
|
|
}elseif($sort == 'ka_id' OR $sort == 'rang3'){
|
|
$sorttyp = SORT_NUMERIC;
|
|
}else{
|
|
$sorttyp = SORT_REGULAR ;
|
|
}
|
|
$smarty->assign('check_sort', $sort);
|
|
$smarty->assign('check_order', $order);
|
|
# echo "array_multisort($$sort, $order, $sorttyp, table_data1)<br>";
|
|
array_multisort($$sort, $order, $nachname, $order, $sorttyp, $table_data1);
|
|
|
|
$smarty->assign('table_data1', $table_data1);
|
|
*/
|
|
}
|
|
|
|
$smarty->assign('action', "$action");
|
|
$smarty->display("$template/admin/$templatename");
|
|
|
|
?>
|