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

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überprü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_agzuw'");
$row = $result->fetch_array();
if ($row[0] != 1){ include("kurs/rechte.php"); exit;}
// Rechte&uuml;berpr&uuml;fung ende

if (isset($_GET['action']))
   $action = $_GET['action'];
else
   $action = "";

switch($action){
default:

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>Priorit&auml;ten bearbeiten</title>
</head>
<body>
<table border=\"0\" style=\"border-collapse: collapse\" width=\"100%\" cellpadding=\"0\" height=\"100%\" id=\"table1\">
    <tr>
        <td>
        <div align=\"center\">
            <table cellspacing=\"0\" cellpadding=\"0\" width=\"100%\" border=\"0\">
                <tr>
                    <td align=\"left\" width=\"1%\" width=\"10\" background=\"../images/box_e1.gif\">
                    <img height=\"40\" src=\"../images/blank.gif\" width=\"22\"></td>
                    <td align=\"left\" width=\"98%\" background=\"../images/box_top.gif\" valign=\"top\" colspan=\"2\">
                    <img height=\"10\" src=\"../images/blank.gif\" width=\"1\"><br>
                    <font class=\"hd\">AG Priorit&auml;ten bearbeiten</font><br>
                    <img height=\"5\" src=\"../images/blank.gif\" width=\"1\"></td>
                    <td 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 colspan=\"2\">
                <form action="; echo $_SERVER['PHP_SELF'] . "?action=bearbeiten"; echo" method=\"POST\" name=\"Wpf_bearb\">
                    <table border=\"0\" cellpadding=\"4\" cellspacing=\"0\" style=\"border-collapse: collapse\" bordercolor=\"#E7E8ED\" width=\"100%\">
                        <tr>
                            <td align='left' width=\"64%\"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>Bezeichnung</b>
                            </td>
                            <td width=\"24%\" align=\"center\" 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>Teilnehmer (Ist)</b>
                            </td>
                            <td width=\"12%\" align=\"center\" 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>Auswahl</b>
                            </td>
                        </tr>";
                        $db = dbconnect();
                        $query = "SELECT agtid, standort, ag, b.bezeichnung
                                    FROM stan_agtage a , stan_vertiefungsbereich b
                                   WHERE jahr='$jahrgang'
                                     AND a.vbid = b.vbid
                                   ORDER BY standort";
                        $result = $db->query($query)
                               or die ("Cannot execute query 1");
                        while ($row = $result->fetch_array()){
                          $result1 = $db->query("SELECT count(*) anzahl 
                                                   FROM stan_agtage_wunsch a, stud b
                                                  WHERE a.uid = b.uid
                                                    AND b.jahrgang = '$jahrgang'
                                                    AND a.agtid = '$row[agtid]'
                                                    AND a.prio = '0'
                                               ");
                          
                          $row1 = $result1->fetch_array();
                        echo "
                        <tr>
                            <td width=\"64%\" 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\">$row[standort] ($row[bezeichnung])</p>
                            </td>
                            <td width=\"24%\" 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=\"center\">$row1[anzahl]</p>
                            </td>
                            <td width=\"12%\" align=\"center\" 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>
                            <input type=\"radio\" value=\"$row[agtid]\" name=\"aus_bearb\" size=\"20\">
                            </td>
                        </tr>";
                           }
                      echo "
                      </table>
                      <br>
                      <input type=\"submit\" value=\"Abschicken\" name=\"B1\" size=\"20\"></p>
                      </form>
                      </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\" colspan=\"2\">
                    <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>
        </div>
        </td>
    </tr>
</table>
</body>
</html>";

break;
case "bearbeiten":
$agtid = $_POST['aus_bearb'];
setcookie("ck_agtid","");
setcookie("ck_agtid",$agtid);

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>Priorit&auml;ten bearbeiten</title>
</head>
<body>
<table border=\"0\" style=\"border-collapse: collapse\" width=\"100%\" cellpadding=\"0\" height=\"100%\" id=\"table1\">
    <tr>
        <td>
        <div align=\"center\">
            <table cellspacing=\"0\" cellpadding=\"0\" width=\"100%\" border=\"0\">
                <tr>
                    <td align=\"left\" width=\"1%\" width=\"10\" background=\"../images/box_e1.gif\">
                    <img height=\"40\" src=\"../images/blank.gif\" width=\"22\"></td>
                    <td align=\"left\" width=\"98%\" background=\"../images/box_top.gif\" valign=\"top\" colspan=\"2\">
                    <img height=\"10\" src=\"../images/blank.gif\" width=\"1\"><br>
                    <font class=\"hd\">Datum wählen</font><br>
                    <img height=\"5\" src=\"../images/blank.gif\" width=\"1\"></td>
                    <td 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 colspan=\"2\">
                <form action="; echo $_SERVER['PHP_SELF'] . "?action=bearbeiten2"; echo" method=\"POST\" name=\"Wpf_bearb\">
                    <table border=\"0\" cellpadding=\"4\" cellspacing=\"0\" style=\"border-collapse: collapse\" bordercolor=\"#E7E8ED\" width=\"100%\">
                        <tr>
                            <td align='left' width=\"64%\"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=\"24%\" align=\"center\" 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>Teilnehmer (Ist)</b>
                            </td>
                            <td width=\"12%\" align=\"center\" 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>Auswahl</b>
                            </td>
                        </tr>";
                        $db = dbconnect();
                        
                        $result_tmp1 = $db->query("DELETE FROM stan_tagesort_tmp WHERE user='$user_admin'");
                        
                        $result = $db->query("SELECT saaid, tag_dez
                                                 FROM stan_agtage_wunsch a, stan_agtage b
                                                WHERE a.agtid = '$_POST[aus_bearb]'
                                                  AND a.agtid = b.agtid
                                                  AND prio = '0'");

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


                           $result1 = $db->query("SELECT beginn
                                                     , ende
                                                     , DATE_Format(beginn, '%Y')AS beginn_jahr
                                                     , DATE_Format(beginn, '%m')AS beginn_monat
                                                     , DATE_Format(beginn, '%d')AS beginn_tag
                                                     , DATE_Format(ende, '%Y')AS ende_jahr
                                                     , DATE_Format(ende, '%m')AS ende_monat
                                                     , DATE_Format(ende, '%d')AS ende_tag
                                                  FROM stan_antrag
                                                 WHERE saaid = '$row[saaid]'
                                              ");
                 
                           while ($row1 = $result1->fetch_array()){
                           
				 $a = mktime(0, 0, 0, $row1[beginn_monat], $row1[beginn_tag], $row1[beginn_jahr]);
				 $e = mktime(0, 0, 0, $row1[ende_monat], $row1[ende_tag], $row1[ende_jahr]);

				if($row[tag_dez] != ''){//Wenn AG-Standorte gewählt wurden, dann Datum anzeigen
				  while($a <= $e){
				
				    if(date('w', $a) == $row[tag_dez]){
				    
				       $ag_tag = date('Y-m-d', $a);
				        
				        $result_tmp1 = $db->query("INSERT INTO stan_tagesort_tmp (user, saaid, agtid, tag)
				                                    VALUES ('$user_admin', '$row[saaid]', '$_POST[aus_bearb]', '$ag_tag')");
				    }
				    $a = strtotime('+1 day', $a);
				  }
				}
       
                           }
                        }
                        

                        $result2 = $db->query("SELECT tag, count(*) Anz, date_format(tag,'%d.%m.%Y') date_form
                                                  FROM stan_tagesort_tmp
                                                 GROUP BY tag
                                                 ORDER BY tag asc");
			
                        while ($row2 = $result2->fetch_array()){;
                        
                        
			switch(date('w', $row2[tag])){
				case 0:
					$wochentag = 'So';
					break;
				case 1:
					$wochentag = 'Mo';
					break;
				case 2:
					$wochentag = 'Di';
					break;
				case 3:
					$wochentag = 'Mi';
					break;
				case 4:
					$wochentag = 'Do';
					break;
				case 5:
					$wochentag = 'Fr';
					break;
				case 6:
					$wochentag = 'Sa';
					break;
			}

 			$datum = "$wochentag, $row2[date_form]";

                        echo "
                        <tr>
                            <td width=\"64%\" 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\">$datum</p>
                            </td>
                            <td width=\"24%\" 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=\"center\">$row2[Anz]</p>
                            </td>
                            <td width=\"12%\" align=\"center\" 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>
                            <input type=\"radio\" value=\"$row2[tag]\" name=\"aus_bearb\" size=\"20\">
                            </td>
                        </tr>";
                        }  
                      echo "
                      </table>
                      <br>
                      <input type=\"submit\" value=\"Abschicken\" name=\"B1\" size=\"20\"></p>
                      </form>
                      </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\" colspan=\"2\">
                    <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>
        </div>
        </td>
    </tr>
</table>
</body>
</html>";

break;
case "bearbeiten2":
$agtid = $_COOKIE['ck_agtid'];

if(isset($_POST['aus_bearb'])){
  $aus_dat = $_POST['aus_bearb'];
  setcookie("ck_aus_dat",$aus_dat);
}

if(!isset($aus_dat)){
  $aus_dat = $_COOKIE['ck_aus_dat'];
}

$db = dbconnect();
$query = $db->query("SELECT agtid, standort, ag, b.bezeichnung
                        FROM stan_agtage a , stan_vertiefungsbereich b
                       WHERE agtid='$agtid'
                         AND a.vbid = b.vbid
                       ORDER BY standort");
$row1 = $query->fetch_array();

$result2 = $db->query("SELECT tag, count(*) Anz, date_format(tag,'%d.%m.%Y') date_form
                          FROM stan_tagesort_tmp
                         GROUP BY tag
                         ORDER BY tag asc");

$row2 = $result2->fetch_array();



$sort = $_GET["sort"];
if(!isset($sort)){
 $sort = "nachname";
}



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>&Uuml;bersicht &uuml;ber zugewiesene PU-Standorte $stud_jahr[Jahrgang]</title>
</head>
<body>
<table border=\"0\" style=\"border-collapse: collapse\" width=\"100%\" cellpadding=\"0\" height=\"90%\">
    <tr>
        <td>
        <div align=\"center\">
            <table cellspacing=\"0\" cellpadding=\"0\" width=\"90%\" 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\">Adressliste: $row1[standort] ($row1[bezeichnung])</font><br>
                    <span class=\"sh\">Datum: $row2[date_form]<br>Anzahl: $row2[Anz]</span><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=\"2\" cellspacing=\"0\" style=\"border-collapse: collapse\" bordercolor=\"#E7E8ED\" width=\"100%\">
              <tr>
                <td width=\"60%\" 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\">
                  ";
                  if($sort == 'nachname'){
                    echo "<font size=\"2\"><a href=$_SERVER[PHP_SELF]?action=bearbeiten2&sort=nachname><b>Name, Vorname</b></a></font>";
                  }else{
                    echo "<font size=\"2\"><a href=$_SERVER[PHP_SELF]?action=bearbeiten2&sort=nachname>Name, Vorname</a></font>";
                  }
                  echo"
                </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\">
                ";
                if($sort == 'ag'){
                  echo"<font size=\"2\"><a href=$_SERVER[PHP_SELF]?action=bearbeiten2&sort=poStrasse><b>Straße</b></a></font>";
                }else{
                  echo"<font size=\"2\"><a href=$_SERVER[PHP_SELF]?action=bearbeiten2&sort=poStrasse>Straße</a></font>";
                }
                echo"
                </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\">
                ";
                if($sort == 'ag'){
                  echo"<font size=\"2\"><a href=$_SERVER[PHP_SELF]?action=bearbeiten2&sort=poplz><b>PLZ</b></a></font>";
                }else{
                  echo"<font size=\"2\"><a href=$_SERVER[PHP_SELF]?action=bearbeiten2&sort=poplz>PLZ</a></font>";
                }
                echo"
                </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\">
                ";
                if($sort == 'stg'){
                  echo"<font size=\"2\"><a href=$_SERVER[PHP_SELF]?action=bearbeiten2&sort=poOrt><b>Ort</b></a></font>";
                }else{
                  echo"<font size=\"2\"><a href=$_SERVER[PHP_SELF]?action=bearbeiten2&sort=poOrt>Ort</a></font>";
                }
                echo"
                </td>
              </tr>";


                $db = dbconnect();
                           
                $query = "SELECT a.uid, a.nachname, a.vorname, poplz, poOrt, poStrasse, a.ag, a.jahrgang, a.stg
                            FROM stud a, stan_antrag b, stan_tagesort_tmp c
                           WHERE c.saaid = b.saaid
                             AND b.uid = a.uid
                             AND c.tag='$aus_dat'
                           ORDER BY $sort, nachname";
#echo $query;
                $result = $db->query($query)
                       or die ("Cannot execute query");
               while ($row = $result->fetch_array()){

                echo "
              <tr>
                <td width=\"60%\" 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\">
                <font size=\"2\">$row[nachname], $row[vorname]</font>
                </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\">
                <font size=\"2\">$row[poStrasse]</font></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\">
                <font size=\"2\">$row[poplz]</font></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\">
                <font size=\"2\">$row[poOrt]</font></td>
              </tr>";
                   }

            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>
            <br>
        </div>
        </td>
    </tr>
</table>
</body>
</html>";
} //Ende Action Abfrage
?>