<?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 == '')
{
    # Auswahlmaske für welche Studienbeginn die Ranglisten berechnet werden
    $german = $db->query("SET lc_time_names = 'de_DE';");

    $query1 = "SELECT ausbildungsbeginn
               FROM bpm_stichtage
              ORDER BY ausbildungsbeginn ASC";

    $result1 = $db->query($query1) or die("Cannot execute query1");

    while ($row1 = $result1->fetch_array())
    {
        $table_data1[] = $row1;
    }
    $smarty->assign('table_data1', $table_data1);
}

if ($action == 'ausgabe')
{
    # Berechnung der Personen, für die eine Rangliste erzeugt wird
    # 1) Personen Selektieren, die zum ausgewählten Jahrgang gehören
    # 2) Personen, die keine HZB Note abgegeben haben löschen
    # 3) Personen löschen, die sich nicht beworben haben (Dokumente nicht abschließend hochgeladen haben beim Bewerbungsverfahren)
    # 4) ausgeschlossene Personen löschen
    # Berechnung der Ranglistenplätze 1-3
    

    # 1) Personen des Jahrgangs selektieren
    $ausbildungsbeginn = $_POST['ausbildungsbeginn'];

    # Performance erhöhen, commit erst am Ende absetzen
    $db->autocommit(false);

    # Drop Table, damit das create Datum ausgelesen werden kann in den Ranglisten
    $sql1 = $db->query("DROP TABLE bpm_tmp_jahrgang_kandidat");
    $sql1 = $db->query("CREATE TABLE `bpm_tmp_jahrgang_kandidat` (
                                     `tjk_id` int(11) NOT NULL,
                                     `ka_id` int(11) NOT NULL,
                                     `vorname` varchar(250) NOT NULL,
                                     `nachname` varchar(250) NOT NULL,
                                     `id` varchar(200) NOT NULL,
                                     `mail` varchar(250) NOT NULL,
                                     `ausbildungsbeginn` int(4) NOT NULL,
                                     `hzb` decimal(10,2) NOT NULL,
                                     `tm_datum` datetime DEFAULT NULL,
                                     `tm_standort` enum('K','L') NOT NULL,
                                     `testergebnis` int(5) NOT NULL COMMENT 'Nur bestandene Tests',
                                     `rang1` int(11) NOT NULL,
                                     `rang2` int(11) NOT NULL,
                                     `rang3` int(11) NOT NULL
                                     ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");
    $sql1 = $db->query("ALTER TABLE `bpm_tmp_jahrgang_kandidat` ADD PRIMARY KEY (`tjk_id`);");
    $sql1 = $db->query("ALTER TABLE `bpm_tmp_jahrgang_kandidat` MODIFY `tjk_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;");

    $query = "SELECT ka_id, vorname, nachname,id, mail
                FROM bpm_kandidat
                where upper(nachname) not like '%TEST%'
            ORDER BY nachname ASC, vorname ASC
             ";

    $result = $db->query($query) or die("Cannot execute query");

    while ($row = $result->fetch_array())
    {

        # Prüfung der Testgültigkeit: 1) Wann hat der Kandidat den Test gemacht
        $query_bpmtesttg = $db->query("SELECT date_format(tm_datum, '%Y-%m-%d') tm_datum
                                     FROM bpm_term_kand a, bpm_termine b, bpm_kandidat c
                                    WHERE a.tm_id = b.tm_id
                                      AND a.ka_id = c.ka_id
                                      AND upper(c.id) = '$row[id]'
                                    ORDER BY tk_id desc
                                    LIMIT 1
                                 ");
        $row_bpmtesttg = $query_bpmtesttg->fetch_array();

        # Prüfung der Testgültigkeit: 2) Für welchen Ausbildungsbeginn(/Semester) gilt der Test
        $query_bpmausbb = $db->query("SELECT ausbildungsbeginn
                                     FROM bpm_stichtage
                                    WHERE beginn <= '$row_bpmtesttg[tm_datum]'
                                      AND ende >= '$row_bpmtesttg[tm_datum]'
                                    LIMIT 1
                                 ");
        $row_bpmausbb = $query_bpmausbb->fetch_array();

        if ($row_bpmausbb['ausbildungsbeginn'] == $ausbildungsbeginn)
        {
            $nachname = addslashes(trim($row['nachname']));
            $vorname = addslashes(trim($row['vorname']));

            $query_bpmnote = $db->query("SELECT hzb
                                     FROM bpm_note
                                    WHERE ka_id = '$row[ka_id]'
                                    ORDER BY no_id desc
                                    LIMIT 1
                                 ");
            $row_bpmanote = $query_bpmnote->fetch_array();
            $hzb = $row_bpmanote['hzb'];

            $query_bpmerg = $db->query("SELECT summe, bestanden, tm_datum, tm_standort
                                  FROM bpm_ergebnisse a, bpm_tanpool b, bpm_term_kand c, bpm_termine d
                                 WHERE a.tan=b.tan
                                   AND b.tp_id=c.tp_id
                                   AND c.tm_id=d.tm_id
                                   AND a.ka_id = '$row[ka_id]'
                                 ORDER BY eg_id DESC
                                  Limit 1
                                   ");
            $row_bpmerg = $query_bpmerg->fetch_array();
            $summe = $row_bpmerg['summe'];
            $tm_datum = $row_bpmerg['tm_datum'];
            $tm_standort = $row_bpmerg['tm_standort'];

            if ($row_bpmerg['bestanden'] == 1)
            {
                # Nur wenn der Test bestanden wurde hinzufügen
                $sql1 = $db->query("insert into bpm_tmp_jahrgang_kandidat (ka_id, vorname, nachname, id, mail, ausbildungsbeginn, hzb, tm_datum, tm_standort, testergebnis) VALUES('$row[ka_id]', '$vorname', '$nachname', '$row[id]', '$row[mail]', '$ausbildungsbeginn', '$hzb', '$tm_datum', '$tm_standort', '$summe')");
            }
        }
    }

    # 2) Personen ohne HZB löschen
    $sql1 = $db->query("DELETE FROM bpm_tmp_jahrgang_kandidat WHERE hzb='0.00'");
    $sql1 = $db->query("DELETE FROM bpm_tmp_jahrgang_kandidat WHERE ka_id NOT IN (SELECT ka_id FROM bpm_ergebnisse)");

    # 3) Personen ohne vollständige Bewerbung löschen
    $sql1 = $db->query("DELETE FROM bpm_tmp_jahrgang_kandidat WHERE ka_id in (SELECT ka_id FROM bpm_upload WHERE upload_dat='0000-00-00 00:00:00')");
    $sql1 = $db->query("DELETE FROM bpm_tmp_jahrgang_kandidat WHERE ka_id NOT IN (SELECT ka_id FROM bpm_upload)");

    # 4) Ausgeschlossene Personen löschen
    $sql1 = $db->query("DELETE FROM bpm_tmp_jahrgang_kandidat WHERE ka_id in (select ka_id from bpm_ausgeschlossene_kandidaten)");

    # Ränge berechnen
    # Rangliste1
    $query1 = "SELECT ka_id, vorname, nachname
   		                  , hzb
   		                  , id
   		                  , mail
                      FROM bpm_tmp_jahrgang_kandidat
                  ORDER BY hzb ASC, nachname ASC, vorname ASC
                   ";
    $result1 = $db->query($query1) or die("Cannot execute query1");

    $rangliste1 = 0;
    $lfd_nr2_1 = 0;
    $lfd_nr3_1 = 0;
    $hzbvorher = 0;

    while ($row1 = $result1->fetch_array())
    {

        $lfd_nr2_1++;

        $hzb = $row1['hzb'];
        if ($hzb == $hzbvorher)
        {
            $rangliste1 = $lfd_nr3_1;
        }
        else
        {
            $rangliste1 = $lfd_nr2_1;
            $lfd_nr3_1 = $lfd_nr2_1;
        }
        $hzbvorher = $row1['hzb'];
        $sql1 = $db->query("UPDATE bpm_tmp_jahrgang_kandidat SET rang1='$rangliste1' WHERE ka_id=$row1[ka_id]");
    }

    # Rangliste2
    $query2 = "SELECT 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
		                  , testergebnis
		                  , id
		                  , mail
                   FROM bpm_tmp_jahrgang_kandidat
               ORDER BY testergebnis DESC, nachname ASC, vorname ASC
                ";

    $result2 = $db->query($query2) or die("Cannot execute query2");

    $rangliste2 = 0;
    $lfd_nr2_2 = 0;
    $lfd_nr3_2 = 0;
    $testergebnisvorher = 0;

    while ($row2 = $result2->fetch_array())
    {

        $lfd_nr2_2++;

        $testergebnis = $row2['testergebnis'];
        if ($testergebnis == $testergebnisvorher)
        {
            $rangliste2 = $lfd_nr3_2;
        }
        else
        {
            $rangliste2 = $lfd_nr2_2;
            $lfd_nr3_2 = $lfd_nr2_2;
        }
        $testergebnisvorher = $row2['testergebnis'];

        $sql1 = $db->query("UPDATE bpm_tmp_jahrgang_kandidat SET rang2='$rangliste2' WHERE ka_id=$row2[ka_id]");
    }
    $smarty->assign('table_data1', $table_data1);

    # Rangliste3
    $query3 = "SELECT ka_id, (rang1+rang2)/2 rang3
                      FROM bpm_tmp_jahrgang_kandidat
                  ORDER BY rang3 ASC, nachname ASC, vorname ASC
                   ";
    $result3 = $db->query($query3) or die("Cannot execute query3");

    $rangliste3 = 0;
    $lfd_nr2_3 = 0;
    $lfd_nr3_3 = 0;
    $rang3vorher = 0;

    while ($row3 = $result3->fetch_array())
    {

        $lfd_nr2_3++;

        $rang3 = $row3['rang3'];
        if ($rang3 == $rang3vorher)
        {
            $rangliste3 = $lfd_nr3_3;
        }
        else
        {
            $rangliste3 = $lfd_nr2_3;
            $lfd_nr3_3 = $lfd_nr2_3;
        }
        $rang3vorher = $row3['rang3'];
        $sql1 = $db->query("UPDATE bpm_tmp_jahrgang_kandidat SET rang3='$rangliste3' WHERE ka_id=$row3[ka_id]");
    }

    $db->autocommit(true);
}

$smarty->assign('action', "$action");
$smarty->display("$template/admin/$templatename");

?>