210 lines
8.2 KiB
PHP
Executable File
210 lines
8.2 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 == '') {
|
|
#Anzahl der getesteten Kandidaten
|
|
$result_anzget = $db->query("SELECT count(distinct ka_id) Anz
|
|
FROM dvm_term_kand a, dvm_tanpool b
|
|
WHERE a.tp_id = b.tp_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 dvm_tanpool b
|
|
WHERE verw_dat != '0000-00-00 00:00:00'
|
|
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 dvm_term_kand a, dvm_tanpool b
|
|
WHERE a.tp_id = b.tp_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 dvm_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 dvm_tanpool
|
|
WHERE tp_id = $row[tp_id]");
|
|
$row_tan = $result_tan->fetch_array();
|
|
|
|
$result_bestanden = $db->query("SELECT bestanden, vorname, nachname, id
|
|
FROM dvm_ergebnisse a, dvm_kandidat b
|
|
WHERE a.ka_id = $row[ka_id]
|
|
AND tan = '$row_tan[tan]'
|
|
AND a.ka_id = b.ka_id
|
|
");
|
|
$row_bestanden = $result_bestanden->fetch_array();
|
|
if($row_bestanden['bestanden'] == 0 and $row_bestanden['bestanden'] != ''){
|
|
$zaehler++;
|
|
# echo "$row_bestanden[nachname], $row_bestanden[vorname], $row_bestanden[id]<br>";
|
|
}
|
|
}
|
|
$smarty->assign('datenchk_anzdurchfaller', $zaehler);
|
|
|
|
# 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
|
|
, a.hochschule
|
|
, behinderung
|
|
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 tm_datum DESC, 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 dvm_ergebnisse
|
|
WHERE ka_id = '$row1[ka_id]'
|
|
AND bestanden = '1'");
|
|
$row_test = $result_test->fetch_array();
|
|
|
|
$result_perso = $db->query("SELECT count(*) Anz
|
|
FROM dvm_kandidat
|
|
WHERE perso = '$row1[perso]'
|
|
");
|
|
$row_perso = $result_perso->fetch_array();
|
|
if($row_perso['Anz'] > 1){
|
|
$perso_alert = "red";
|
|
}else{
|
|
$perso_alert = "black";
|
|
}
|
|
|
|
|
|
$result_anzhzb = $db->query("SELECT count(*) Anz
|
|
FROM dvm_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 dvm_dst_wunsch a, dvm_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
|
|
FROM dvm_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";
|
|
}
|
|
|
|
$result_anzzeug = $db->query("SELECT count(*) Anz
|
|
FROM dvm_upload
|
|
WHERE (beschreibung = 'Zeugnis 1'
|
|
OR beschreibung = 'Zeugnis 2')
|
|
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 dvm_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 dvm_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";
|
|
}else{
|
|
$image_notehzb = "ok.gif";
|
|
}
|
|
|
|
if($row_notehzb['zeugnisschnitt'] == "0.00" or $row_notehzb['zeugnisschnitt'] == ""){
|
|
$image_noteschnitt = "cross.jpg";
|
|
}else{
|
|
$image_noteschnitt = "ok.gif";
|
|
}
|
|
|
|
$row1['image_zusage'] = $image_zusage;
|
|
$row1['perso_alert'] = $perso_alert;
|
|
$row1['dstzusagebez'] = $dstzusagebez;
|
|
$row1['image_noteschnitt'] = $image_noteschnitt;
|
|
$row1['image_notehzb'] = $image_notehzb;
|
|
$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;
|
|
$table_data1[] = $row1;
|
|
}
|
|
$smarty->assign('table_data1', $table_data1);
|
|
|
|
}
|
|
|
|
$smarty->assign('action', "$action");
|
|
$smarty->display("$template/admin/$templatename");
|
|
|
|
?>
|