<?php
$user_admin=$_COOKIE["user_admin"];
$jahrgang=$_COOKIE["jahrgang"];
$hs=$_COOKIE["ck_hs"];

if ($jahrgang == ""){ require("jahrgang.php"); exit;} //Wenn man kein Jahrgang ausgew&auml;hlt hat wird die Jahrgangsauswahlseite geladen
include("kurs/datenbankanbindung.php");     // f&uuml;gt die Datenbankanbindung ein: Sys:\php\includes\kurs\datenbankanbindung.php

// Rechte&uuml;berpr&uuml;fung
$db = dbconnect();
if ($user_admin == ""){ require("index.php"); exit;} //Wenn man nicht angemeldet ist, darf man nicht auf die Seite
$result = $db->query("SELECT 1 FROM stan_admin_rechte, stan_admin_rechte_zuord , stan_admin where stan_admin_rechte.stan_admin_rolle = stan_admin_rechte_zuord.stan_admin_rolle AND stan_admin_rechte_zuord.said = stan_admin.said AND stan_admin.user = '$user_admin' AND stan_admin_rechte_zuord.stan_admin_rolle = 'a_offebeu'");
$row = $result->fetch_array();
if ($row[0] != 1){ include("kurs/rechte.php"); exit;}
// Rechte&uuml;berpr&uuml;fung ende





$query = "SELECT uid 
            FROM stud
           WHERE jahrgang='$jahrgang'
             AND durchgefallen!=\"Y\"
             AND uid in (SELECT uid FROM stan_antrag)
             AND hs = '$hs'";

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

while ($row2 = $result->fetch_array()){
$result_tmp1 = $db->query("DELETE FROM stan_tmp_check_data3 WHERE uid='$row2[uid]'");

$result_tmp1 = $db->query("INSERT INTO stan_tmp_check_data3
                                 SELECT t1.saaid
                                      , t1.vert_bereich
				      , t1.uid
				      , t1.beginn
				      , t1.ende
				      , t1.dst_id
				   FROM stan_antrag t1
				  WHERE t1.status = (
				                     SELECT MAX(t2.status)
				                       FROM stan_antrag t2
				                      WHERE t1.aendid = t2.aendid
				                        AND t2.status !='2' #hinzugefügt am 10.06.2009, weil Status 2 angezeigt wurde
				                    )
                                    AND t1.uid = '$row2[uid]'
                                    AND (t1.zuweisung is NULL OR t1.zuweisung='Z')
                          ");
}

echo"
<html>

<head>
<meta http-equiv=\"content-type\" content=\"text/html; charset=utf-8\">
<link rel=\"stylesheet\" href=\"../styles_pc.css\" type=\"text/css\">
<title>Beurteilungen</title>
</head>

<body>


            <table cellspacing=\"0\" cellpadding=\"0\" width=\"100%\" border=\"0\">
                <tr>
                    <td width=\"10\" background=\"../images/box_e1.gif\">
                    <img height=\"40\" src=\"../images/blank.gif\" width=\"22\"></td>
                    <td nowrap=\"nowrap\" align=\"left\" width=\"98%\" background=\"../images/box_top.gif\" valign=\"top\">
                    <img height=\"10\" src=\"../images/blank.gif\" width=\"1\"><br>
                    <font class=\"hd\">Beurteilungen</font><br>
                    <img height=\"5\" src=\"../images/blank.gif\" width=\"1\"></td>
                    <td nowrap=\"nowrap\" align=\"right\" width=\"1%\" background=\"../images/box_e2.gif\">
                    <img height=\"40\" src=\"../images/blank.gif\" width=\"22\"></td>
                </tr>
                <tr>
                    <td width=\"1%\" background=\"../images/box_l.gif\">
                    <img height=\"1\" src=\"../images/blank.gif\" width=\"22\"></td>
                    <td>

                    <table border=\"0\" cellpadding=\"4\" cellspacing=\"0\" style=\"border-collapse: collapse\" bordercolor=\"#E7E8ED\" width=\"100%\" id=\"AutoNumber1\">
                	<tr>
                    <td width=\"20%\" style=\"border-left-style: none; border-left-width: medium; border-right-style: none; border-right-width: medium; border-top-style: none; border-top-width: medium; border-bottom-style: solid; border-bottom-width: 1px\">
                    <p align=\"left\"><b>Name</b></p>
                    </td>
                    <td width=\"20%\" style=\"border-left-style: none; border-left-width: medium; border-right-style: none; border-right-width: medium; border-top-style: none; border-top-width: medium; border-bottom-style: solid; border-bottom-width: 1px\">
                    <b>AG</b>
                    </td>
                    <td width=\"20%\" style=\"border-left-style: none; border-left-width: medium; border-right-style: none; border-right-width: medium; border-top-style: none; border-top-width: medium; border-bottom-style: solid; border-bottom-width: 1px\">
                    <b>Datum</b>
                    </td>
                    <td width=\"20%\" style=\"border-left-style: none; border-left-width: medium; border-right-style: none; border-right-width: medium; border-top-style: none; border-top-width: medium; border-bottom-style: solid; border-bottom-width: 1px\">
                    <b>Ort</b>
                    </td>
                    <td width=\"20%\" style=\"border-left-style: none; border-left-width: medium; border-right-style: none; border-right-width: medium; border-top-style: none; border-top-width: medium; border-bottom-style: solid; border-bottom-width: 1px\">
                    <b>beurteilt</b>
                    </td>
                    </tr>";
                    $db = dbconnect();


$query = "SELECT uid, vorname, nachname, ag
            FROM stud
           WHERE jahrgang='$jahrgang'
             AND durchgefallen!=\"Y\"
             AND uid in (SELECT uid FROM stan_antrag)
             AND hs = '$hs'";

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

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

                $query8 = "SELECT ma.uid, DATE_FORMAT(beginn, '%d.%m.%Y') beginn, DATE_FORMAT(ende, '%d.%m.%Y') ende, bezeichnung, COALESCE( luecke, 'OK' ) AS Luecke, ma.saaid
                            FROM stan_tmp_check_data3 ma
                            LEFT JOIN ( SELECT saaid, 'Lücke folgt' AS luecke
                                          FROM stan_tmp_check_data3 a
                                         WHERE a.ende != (SELECT MAX( ende ) FROM stan_tmp_check_data3 x WHERE x.uid = a.uid)
                                           AND NOT EXISTS ( SELECT saaid FROM stan_tmp_check_data3 b
                                                             WHERE a.uid = b.uid
                                                               AND b.beginn = DATE_ADD( a.ende, INTERVAL 1 DAY )
                                                          )
                                            OR ( a.beginn IN (select beginn FROM stan_tmp_check_data3 d WHERE d.saaid != a.saaid)
                                                                 AND a.ende  IN (select ende FROM stan_tmp_check_data3 e WHERE e.saaid != a.saaid AND e.uid = '$row2[uid]')
                                               )
                                      ) L
                              ON L.saaid = ma.saaid
                           WHERE ma.uid = '$row2[uid]'
                           ORDER BY ma.uid, ma.beginn";

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

                    while ($row = $result8->fetch_array()){
                                   $result5 = $db->query("SELECT concat(bezeichnung, ' ',name) name, ort
                                                              FROM stan_dienststellen
                                                             WHERE dst_id ='$row[bezeichnung]'");
                                    $row5 = $result5->fetch_array();

                                    $result6 = $db->query("SELECT bez, ort
                                                              FROM stan_dienststellen_tmp
                                                             WHERE dst_id ='$row[bezeichnung]'");
                                    $row6 = $result6->fetch_array();

                                    if($row5['name'] != '' and $row6['bez'] == ''){
                                      $bezeichnung = $row5['name'];
                                      $ort = $row5['ort'];
                                    }


                                    if($row5['name'] == '' and $row6['bez'] != ''){
                                      $bezeichnung = $row6['bez'];
                                      $ort = $row6['ort'];
                                    }

                                    if($row5['name'] == '' and $row6['bez'] == ''){
                                      $bezeichnung = '<i>Zeitraum vorgemerkt</i>';
                                      $ort = '';
                                    }

                      $result3beu = $db->query("SELECT beurteilt
                                                   FROM stan_antrag
                                                  WHERE saaid='$row[saaid]'");
                      $row3beu = $result3beu->fetch_array();
                      if($row3beu['beurteilt'] != 'Y' ){
                      echo "
                          <tr>
                        <td width=\"20%\" style=\"border-left-style: none; border-left-width: medium; border-right-style: none; border-right-width: medium; border-top-style: none; border-top-width: medium; border-bottom-style: solid; border-bottom-width: 1px\">
                        <p align=\"left\">
                        <a href=bearbeiten.php?action=ubersicht&uid=$row2[uid]>$row2[nachname], $row2[vorname]</a>
                        </td>
                        <td width=\"20%\" style=\"border-left-style: none; border-left-width: medium; border-right-style: none; border-right-width: medium; border-top-style: none; border-top-width: medium; border-bottom-style: solid; border-bottom-width: 1px\">
                        $row2[ag]/$jahrgang
                        </td>
                        <td width=\"20%\" style=\"border-left-style: none; border-left-width: medium; border-right-style: none; border-right-width: medium; border-top-style: none; border-top-width: medium; border-bottom-style: solid; border-bottom-width: 1px\">
                        $row[beginn] - $row[ende]
                        </td>
                        <td width=\"20%\" style=\"border-left-style: none; border-left-width: medium; border-right-style: none; border-right-width: medium; border-top-style: none; border-top-width: medium; border-bottom-style: solid; border-bottom-width: 1px\">
                        $bezeichnung/$ort
                        </td>
                        <td width=\"20%\" style=\"border-left-style: none; border-left-width: medium; border-right-style: none; border-right-width: medium; border-top-style: none; border-top-width: medium; border-bottom-style: solid; border-bottom-width: 1px\">
                        <iframe src='ch_attr.php?saaid=$row[saaid]&status_param=$row3beu[beurteilt]' scrolling='no' marginheight='0' marginwidth='0' height='25' frameborder='0' width='25'></iframe>
                        </td>
                      </tr>";
                      }
                    }
$result_tmp1 = $db->query("DELETE FROM stan_tmp_check_data3 WHERE uid='$row2[uid]'");
}
                  echo "
                     </table>
                     </td>
                    <td width=\"1%\" background=\"../images/box_r.gif\">
                    <img height=\"1\" src=\"../images/blank.gif\" width=\"22\"></td>
                </tr>
                <tr>
                    <td nowrap=\"nowrap\" align=\"left\" width=\"1%\" background=\"../images/box_e3.gif\">
                    <img height=\"16\" src=\"../images/blank.gif\" width=\"22\"></td>
                    <td nowrap=\"nowrap\" align=\"left\" width=\"98%\" background=\"../images/box_bottom.gif\">
                    <img height=\"16\" src=\"../images/blank.gif\" width=\"8\"></td>
                    <td nowrap=\"nowrap\" align=\"right\" width=\"1%\" background=\"../images/box_e4.gif\">
                    <img height=\"16\" src=\"../images/blank.gif\" width=\"22\"> </td>
                </tr>
            </table>


</body>

</html>";
?>
