<?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&nbsp;3');
  }elseif($sort == "hochschule"){
    $smarty->assign('check_sort_klartext', 'zust&auml;ndige&nbsp;Hochschule');
  }elseif($sort == "wunsch_hs"){
    $smarty->assign('check_sort_klartext', 'Wunsch&nbsp;Hochschule');
  }elseif($sort == "zugewiesene_hs"){
    $smarty->assign('check_sort_klartext', 'Zugewiesene&nbsp;Hochschule');
  }elseif($sort == "testergebnis"){
    $smarty->assign('check_sort_klartext', 'Testergebnis');
  }elseif($sort == "verkuerzung"){
    $smarty->assign('check_sort_klartext', 'Verk&uuml;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&nbsp;HZB');
  }elseif($sort == "note_wert"){
    $smarty->assign('check_sort_klartext', 'Notenwert&nbsp;Durchschnitt');
  }elseif($sort == "mail"){
    $smarty->assign('check_sort_klartext', 'Mailadresse');
  }elseif($sort == "image_hzb"){
    $smarty->assign('check_sort_klartext', 'PDF&nbsp;Upload&nbsp;HZB');
  }elseif($sort == "anz_zeug"){
    $smarty->assign('check_sort_klartext', 'PDF&nbsp;Upload&nbsp;Zeugnisse');
  }elseif($sort == "image_leben"){
    $smarty->assign('check_sort_klartext', 'Lebenslauf');
  }elseif($sort == "image_notehzb"){
    $smarty->assign('check_sort_klartext', 'Note&nbsp;HZB');
  }elseif($sort == "image_noteschnitt"){
    $smarty->assign('check_sort_klartext', 'Note&nbsp;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");

?>