<?php #pgs/xx_termine/class.php

class TerminePage extends BasePage
{
  private $monthsback = 1;
  
  protected function update($id) {
    $this->get($id);
    $qry = "UPDATE Termine SET Beginn = ?, Ende = ?, Thema = ?, Ort = ?, Dienstplan = ?, "
          ."Kategorie = ?, Platzvergabe = ?, Ausgelost = ?, Gruppe = ? WHERE ID = ?";
    if ($stmt = $this->db->prepare($qry)) {
      $Beginn = $this->man->input["BEGINN"] ?? $this->output["MAIN"]["BEGINN"];
      $Ende = $this->man->input["ENDE"] ?? $this->output["MAIN"]["ENDE"];
      $Thema = $this->man->input["THEMA"] ?? $this->output["MAIN"]["THEMA"];
      $Ort = $this->man->input["ORT"] ?? $this->output["MAIN"]["ORT"];
      $Dienstplan = $this->man->input["DIENSTPLAN"] ?? $this->output["MAIN"]["DIENSTPLAN"]["ID"];
      $Kategorie = $this->man->input["KATEGORIE"] ?? $this->output["MAIN"]["KATEGORIE"]["ID"];
      $Platzvergabe = array_key_exists("PLATZVERGABE", $this->man->input)
                    ? $this->man->input["PLATZVERGABE"]
                    : (is_null($this->output["MAIN"]["PLATZVERGABE"]) ? null : $this->output["MAIN"]["PLATZVERGABE"]["ID"]);
      $Ausgelost = $this->man->input["AUSGELOST"] ?? $this->output["MAIN"]["AUSGELOST"];
      $Gruppe = array_key_exists("GRUPPE", $this->man->input)
                      ? $this->man->input["GRUPPE"]
                      : (is_null($this->output["MAIN"]["GRUPPE"]) ? null : $this->output["MAIN"]["GRUPPE"]["ID"]);
      $stmt->bind_param(
        "ssssiiiiii",
        $Beginn,
        $Ende,
        $Thema,
        $Ort,
        $Dienstplan,
        $Kategorie,
        $Platzvergabe,
        $Ausgelost,
        $Gruppe,
        $id
      );
      if ($stmt->execute()) {
        $this->man->AddMessage("Termin wurde aktualisiert!");
        $this->get($id);
        return 200;
      } else {
        $this->man->AddMessage("Mysql error: ".$this->db->error);
      }
    } else {
      $this->man->AddMessage("Mysql error: ".$this->db->error);
    }
    return 500;
  }
  
  protected function insert() {
    $qry = "INSERT INTO Termine (Beginn, Ende, Thema, Ort, Dienstplan, Kategorie, Platzvergabe, Gruppe) VALUES (?,?,?,?,?,?,?,?)";
    if ($stmt = $this->db->prepare($qry)) {
      $Beginn = $this->man->input["BEGINN"] ?? "";
      $Ende = $this->man->input["ENDE"] ?? "";
      $Thema = $this->man->input["THEMA"] ?? "";
      $Ort = $this->man->input["ORT"] ??"";
      $Dienstplan = $this->man->input["DIENSTPLAN"] ?? null;
      $Kategorie = $this->man->input["KATEGORIE"] ?? null;
      $Platzvergabe = array_key_exists("PLATZVERGABE", $this->man->input)
                    ? $this->man->input["PLATZVERGABE"]
                    : null;
      $Gruppe = array_key_exists("GRUPPE", $this->man->input)
                      ? $this->man->input["GRUPPE"]
                      : null;
      $stmt->bind_param(
        "ssssiiii",
        $Beginn,
        $Ende,
        $Thema,
        $Ort,
        $Dienstplan,
        $Kategorie,
        $Platzvergabe,
        $Gruppe
      );
      $stmt->execute();
      if ($stmt->affected_rows==1) {
        $this->man->AddMessage("Termin wurde hinzugefügt!");
        $this->get($this->db->insert_id);
        return 201;
      } else {
        $this->man->AddMessage("Termin konnte nicht hinzugefügt werden! ("
                              .$Beginn." / ".$Ende." / ".$Thema." / ".$Ort." / "
                              .$Dienstplan." / ".$Kategorie." / ".$Platzvergabe." / ".$Gruppe.")"
        );
        return 400;
      }
    } else {
      $this->man->AddMessage("Mysql error: ".$this->db->error);
      return 500;
    }
  }
    
  protected function get($id = null) {
  	$this->output = [];
  	if ($id===null && $this->man->Main()!=null) {
      $id = $this->man->Main();
    }
    $where = "";
    if ($id) {
      $where = "WHERE t.ID = ? ";
    } else if (sizeof($this->man->Filter())>0) {
      $where = "WHERE 1 ";
      foreach ($this->man->Filter() as $filter) {
        $where.= "or"==$filter["and"] ? "OR " : "AND ";
        if ("none"==$filter["field"]) {
          $where.= "1 ";
        } else {
          switch ($filter["field"]) {
            case "BEGINN":
              $where.= $this->getFilterString($filter["op"], ["<=", ">="], "t", "Beginn", $filter["value"]);
              break;
            case "ENDE":
              $where.= $this->getFilterString($filter["op"], ["<=", ">="], "t", "Ende", $filter["value"]);
              break;
            case "THEMA":
              $where.= $this->getFilterString($filter["op"], ["like", "not like"], "t", "Thema", $filter["value"]);
              break;
            case "DIENSTPLAN":
              $where.= $this->getFilterString($filter["op"], ["=", "<>"], "t", "Dienstplan", $filter["value"]);
              break;
            case "KATEGORIE":
              $where.= $this->getFilterString($filter["op"], ["=", "<>"], "t", "Kategorie", $filter["value"]);
              break;
            case "GRUPPE":
              $where.= $this->getFilterString($filter["op"], ["=", "<>"], "t", "Gruppe", $filter["value"]);
              break;
            default:
              $where.= "1 ";
      } } }
    } else {
      $datefrom = date("Y-m-d H:i:s");
      $this->man->filter[] = ["and" => "and", "field" => "ENDE", "op" => ">=", "value" => $datefrom];
      $where = "WHERE ".$this->getFilterString(">=", [">="], "t", "Ende", $datefrom);
    }
    
    $qry = "SELECT t.ID, t.Beginn, t.Ende, t.Ort, t.Thema, t.Verantwortliche, t.Ausgelost, IF(t.Beginn<NOW(), 1, 0) vorbei, t.Platzvergabe, "
          ."tp.MaxTeilnehmer, tp.Name PlatzvergabeName, t.Kategorie, ta.Name KategorieName, tt.Status, td.Abteilung, sa.Name AbteilungName, t.Gruppe, "
          ."sg.Name GruppeName, ta.Recht, r.Name RechtName, t.Dienstplan, td.Jahr DienstplanJahr, td.Name DienstplanName "
          .", (SELECT COUNT(*) FROM Termine_Teilnahmen tt2 WHERE tt2.Termine=t.ID AND tt2.Status>=1) numDabei "
          .", (SELECT COUNT(*) FROM Termine_Teilnahmen tt3 WHERE tt3.Termine=t.ID AND tt3.Status=0) numWill "
          .", (SELECT COUNT(*) FROM Termine_Teilnahmen tt4 LEFT JOIN Termine t2 ON t2.ID=tt4.Termine "
          ."   WHERE tt4.Personal=? AND t2.ID!=t.ID AND t2.Kategorie=t.Kategorie "
          ."   AND (t2.Beginn BETWEEN t.Beginn AND t.Beginn + INTERVAL 7 DAY OR "
          ."       ((tt4.Status>0 OR t2.Ausgelost=0) AND t2.Beginn BETWEEN t.Beginn - INTERVAL 7 DAY AND t.Beginn))) naheTermine "
          ."FROM Termine t "
          ."LEFT JOIN Termine_Platzvergaben tp ON tp.ID=t.Platzvergabe "
          ."LEFT JOIN Termine_Kategorien ta ON ta.ID=t.Kategorie "
          ."LEFT JOIN Rechte r ON r.ID=ta.Recht "
          ."LEFT JOIN Termine_Dienstpläne td ON td.ID=t.Dienstplan "
          ."LEFT JOIN Struktur_Abteilungen sa ON sa.ID=td.Abteilung "
          ."LEFT JOIN Struktur_Gruppen sg ON sg.ID=t.Gruppe "
          ."LEFT JOIN Termine_Teilnahmen tt ON t.ID=tt.Termine AND tt.Personal = ? "
          //.($filterID ? "WHERE t.ID = ? " : "WHERE t.Ende > NOW() + INTERVAL ? MONTH AND t.Beginn < NOW() + INTERVAL ? MONTH ")
          .$where
          ."ORDER BY t.Beginn ASC ";
    if ($stmt = $this->db->prepare($qry)) {
      $userID = $this->man->user->ID();
      if ($id) {
        $stmt->bind_param("iii", $userID, $userID, $id);
      } else {
        $stmt->bind_param("ii", $userID, $userID);
      }
      $stmt->execute();
      $res = $stmt->get_result();
      $currentGroup = [];
      while ($row = $res->fetch_assoc()) {
        $restrictions = $row["MaxTeilnehmer"]!==null && $row["MaxTeilnehmer"]>0 ? "Auf ".$row["MaxTeilnehmer"]." Plätze begrenzt!" : "Keine Platzbegrenzung.";
        $terminPrivileges = $row["RechtName"]!==null ? array($this->adminRight, $row["RechtName"]) : $this->adminRight;
        $isPrivileged = $this->man->user->HasRight($terminPrivileges) || "2"==$row["Status"];
        $erlaubt = "YES";
        
        $beginn_time = date("H:i", strtotime($row["Beginn"]));
        $ende_time = date("H:i", strtotime($row["Ende"]));
        $beginn_date = date("D, d.m.Y", strtotime($row["Beginn"]));
        $ende_date = "00:00"==$ende_time ? date("D, d.m.Y", strtotime($row["Ende"]) - 1) : date("D, d.m.Y", strtotime($row["Ende"]));
        
        $teilnahme = $this->teilnahmeGetAll($row["ID"]);
        $responsibles = [];
        foreach ($teilnahme as $teilnehmer) {
          if ("__CASE__:LEAD"==$teilnehmer["STATUS"]) {
            $responsibles[] = $teilnehmer["NAME"];
        } }
        
        $registration = null;
        // Has Dienst registration?
        if ($row["MaxTeilnehmer"]!==null ) {
          $erlaubt = "NO";
          // Does user meet requirements?
          if (($this->man->user->InAbteilung($row["Abteilung"]) && $this->man->user->InGruppe($row["Gruppe"])) || $isPrivileged) {
            $erlaubt = "YES";
            $freenum = intVal($row["MaxTeilnehmer"]) - intVal($row["numDabei"]);
            $registration = [
              "RESTRICTIONS" => $restrictions,
              "FREE" => ("1"==$row["Ausgelost"] && $freenum > 0 ? ["NUM" => $freenum] : null),
              "ACTION" => [],       // Entries of ["LINK" => "", "NAME" => ""],
            ];
            
            switch ($row["Status"]) {
              case "2": $registration["COMES"] = "__CASE__:LEAD"; break;
              case "1": $registration["COMES"] = "__CASE__:CAN";  break;
              case "0": $registration["COMES"] = (1 === $row["Ausgelost"] ? "__CASE__:WAIT" : "__CASE__:WANTS");  break;
              default:  $registration["COMES"] = "__CASE__:ADD";
            }
            
            $action_leave = ["ID" => "LEAVE", "NAME" => "Abmelden", "METHOD" => "DELETE", "STATUS" => 0, "USERID" => "", "IMG" => "cancel.png"];
            $action_join = ["ID" => "JOIN", "NAME" => "Anmelden", "METHOD" => "POST", "STATUS" => 0, "USERID" => "", "IMG" => "add.png"];
            $action_participate = ["ID" => "JOIN", "NAME" => "Mitmachen", "METHOD" => "POST", "STATUS" => 1, "USERID" => "", "IMG" => "add.png"];
            $action_upgrade = ["ID" => "JOIN", "NAME" => "Mitmachen", "METHOD" => "PATCH", "STATUS" => 1, "USERID" => "", "IMG" => "add.png"];
            $action_queue = ["ID" => "JOIN", "NAME" => "Warteliste", "METHOD" => "POST", "STATUS" => 0, "USERID" => "", "IMG" => "add.png"];
            
            // Die Sperrfrist ist zur Zeit aufgehoben
            $kann = true; //$isPrivileged || intVal($row["naheTermine"])==0;
            $platz = "0"==$row["MaxTeilnehmer"] || $row["numDabei"]<$row["MaxTeilnehmer"];
            
            $erlaubt = $kann ? "YES" : "WARN";
            
            if ($row["vorbei"]=="0") {
              if (null!==$row["Status"]) {
                if ("1"==$row["Ausgelost"] && "0"==$row["Status"] && $platz) {
                  $registration["ACTION"][] = $action_upgrade;
                }
                $registration["ACTION"][] = $action_leave;
              } elseif ($kann) {
                if ("1"==$row["Ausgelost"]) {
                  if ($platz) {
                    $registration["ACTION"][] = $action_participate;
                  } else {
                    $registration["ACTION"][] = $action_queue;
                  }
                } else {
                  $registration["ACTION"][] = $action_join;
        } } } } }
        if ("0"!=$row["vorbei"]) {
          $erlaubt = "NO";
        }
        
        $entry = [
          "ID" => $row["ID"],
          "MAIN" => [
            "BEGINN" => $row["Beginn"],
            "BEGINN.DATUM" => date("D, d.m.Y", strtotime($row["Beginn"])),
            "BEGINN.DATE" => date("Y-m-d", strtotime($row["Beginn"])),
            "BEGINN.ZEIT" => date("H:i", strtotime($row["Beginn"])),
            "BEGINN.TIME" => date("H:i", strtotime($row["Beginn"])),
            "ENDE" => $row["Ende"],
            "ENDE.DATUM" => date("d.m.Y", strtotime($row["Ende"])),
            "ENDE.DATE" => date("Y-m-d", strtotime($row["Ende"])),
            "ENDE.ZEIT" => date("H:i", strtotime($row["Ende"])),
            "ENDE.TIME" => date("H:i", strtotime($row["Ende"])),
            "DATUM" => $beginn_date.($beginn_date!=$ende_date ? " - ".$ende_date : ""),
            "ZEIT" => ("00:00"==$ende_time && "00:00"==$beginn_time ? "Ganztägig" : $beginn_time." - ".$ende_time),
            "ORT" => $row["Ort"],
            "KATEGORIE" => ["ID" => $row["Kategorie"], "NAME" => $row["KategorieName"]],
            "ABTEILUNG" => ["ID" => $row["Abteilung"], "NAME" => $row["AbteilungName"]],
            "THEMA"  => $row["Thema"],
            "DIENSTPLAN" => ["ID" => $row["Dienstplan"], "JAHR" => $row["DienstplanJahr"], "NAME" => $row["DienstplanName"]],
            "VERANTWORTLICHE" => implode(" / ", $responsibles),
            "GRUPPE" => isset($row["Gruppe"]) ? ["ID" => $row["Gruppe"], "NAME" => $row["GruppeName"]] : [],
            "PLATZVERGABE" => isset($row["Platzvergabe"]) ? ["ID" => $row["Platzvergabe"], "NAME" => $row["PlatzvergabeName"]] : [],
            "AUSGELOST" => "1"==$row["Ausgelost"],
            "ADMIN" => $isPrivileged,
          ],
          "SUB" => [
            "ANMELDUNG" => $registration,
            "TEILNAHME" => $isPrivileged ? $teilnahme : []
          ],
        ];
        $entry["MAIN"]["ERLAUBT"] = "__CASE__:".$erlaubt;
        $this->addEntryToOutput("DEFAULT", $entry, $id);
      }
    } else {
      $this->man->AddMessage("Mysql error: ".$this->db->error);
    }
    
    return 200;
  }
  
  protected function fillOptions($admin = false) {
    $ret = array();
    $res = $this->db->query("SELECT ID, Jahr, Name FROM Termine_Dienstpläne ORDER BY Jahr DESC, Name DESC");
    while ($row = $res->fetch_assoc()) {
      $ret["DIENSTPLAN"][] = ["ID" => $row["ID"], "JAHR" => $row["Jahr"], "NAME" => $row["Name"]];
    }
    $res = $this->db->query("SELECT ID, Name FROM Termine_Kategorien");
    while ($row = $res->fetch_assoc()) {
      $ret["KATEGORIE"][] = ["ID" => $row["ID"], "NAME" => $row["Name"]];
    }
    $res = $this->db->query("SELECT ID, Kürzel, Name FROM Struktur_Abteilungen");
    while ($row = $res->fetch_assoc()) {
      $ret["ABTEILUNG"][] = ["ID" => $row["ID"], "KÜRZEL" => $row["Kürzel"], "NAME" => $row["Name"]];
    }
    $ret["PLATZVERGABE"][] = ["ID" => "__NULL__", "NAME" => "Ohne"];
    $res = $this->db->query("SELECT ID, Name FROM Termine_Platzvergaben ORDER BY Name ASC");
    while ($row = $res->fetch_assoc()) {
      $ret["PLATZVERGABE"][] = ["ID" => $row["ID"], "NAME" => $row["Name"]];
    }
    $ret["GRUPPE"][] = ["ID" => "__NULL__", "KÜRZEL" => "Ohne", "NAME" => "Ohne"];
    $res = $this->db->query("SELECT ID, Kürzel, Name FROM Struktur_Gruppen ORDER BY Name ASC");
    while ($row = $res->fetch_assoc()) {
      $ret["GRUPPE"][] = ["ID" => $row["ID"], "KÜRZEL" => $row["Kürzel"], "NAME" => $row["Name"]];
    }
    if ($admin) {
      $res = $this->db->query("SELECT p.ID, Nachnamen, Vornamen, pk.Name pkName FROM Personal p LEFT JOIN Personal_Kategorien pk ON pk.ID=p.Kategorie "
                             ."ORDER BY pk.Reihenfolge ASC, Nachnamen ASC, Vornamen ASC");
      $lastKategorie = "";
      while ($row = $res->fetch_assoc()) {
        if ($row["pkName"]!=$lastKategorie) {
          $ret["TEILNAHME"][] = ["ID" => 0, "NAME" => " === ".$row["pkName"]." === "];
          $lastKategorie = $row["pkName"];
        }
        $ret["TEILNAHME"][] = ["ID" => $row["ID"], "NAME" => preg_replace("/(?<![ -])\p{Ll}+/u", ".", $row["Vornamen"])." ".$row["Nachnamen"]];
    } }
    return $ret;
  }
  
  private function teilnahmeGetAll($terminID) {
    $ret = [];
    $qry = "SELECT p.ID, p.Nachnamen, p.Vornamen, GROUP_CONCAT(l.Kürzel SEPARATOR ', ') lehrgänge, tt.Status "
          ."FROM Termine_Teilnahmen tt "
          ."LEFT JOIN Personal p ON p.ID=tt.Personal "
          ."LEFT JOIN Personal_Lehrgänge pl ON pl.Personal=tt.Personal "
          ."LEFT JOIN Lehrgänge l ON l.ID=pl.Lehrgänge "
          ."WHERE tt.Termine = ? "
          ."GROUP BY p.ID ORDER BY tt.Status DESC, p.Kategorie DESC, p.Nachnamen ASC, p.Vornamen ASC, l.ID ASC";
    if ($stmt = $this->db->prepare($qry)) {
      $stmt->bind_param("i", $terminID);
      $stmt->execute();
      $res = $stmt->get_result();
      while ($row = $res->fetch_assoc()) {
        $status = $row["Status"]==2 ? "LEAD" : ($row["Status"]==1 ? "CAN" : "WANT");
        $entry = [
          "ID" => $row["ID"],
          "NAME" => preg_replace("/(?<![ -])\p{Ll}+/u", ".", $row["Vornamen"])." ".$row["Nachnamen"],
          "NACHNAMEN" => $row["Nachnamen"],
          "VORNAMEN" => $row["Vornamen"],
          "STATUS" => "__CASE__:".$status,
          "LEHRGÄNGE" => $row["lehrgänge"] ?? "Keine",
        ];
        $ret[] = $entry;
      }
    } else {
      $this->man->AddMessage("Mysql error: ".$this->db->error);
    }
    return $ret;
} }