App/pgs/11_dienstplaene/page.php

<?php #pgs/xx_dienstplaene/class.php

class DienstplänePage extends BasePage
{
  private $monthsback = 1;
  
  protected function update($id) {
    $this->get($id);
    $qry = "UPDATE Termine_Dienstpläne SET Name = ?, Jahr = ?, Abteilung = ? WHERE ID = ?";
    if ($stmt = $this->db->prepare($qry)) {
      $Name = $this->man->input["NAME"] ?? $this->output["MAIN"]["NAME"];
      $Jahr = $this->man->input["JAHR"] ?? $this->output["MAIN"]["JAHR"];
      $Abteilung = $this->man->input["ABTEILUNG"] ?? $this->output["MAIN"]["ABTEILUNG"]["ID"];
      $stmt->bind_param(
        "siii",
        $Name,
        $Jahr,
        $Abteilung,
        $id
      );
      if ($stmt->execute()) {
        $this->man->AddMessage("Dienstplan 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_Dienstpläne (Abteilung, Jahr, Name) VALUES (?,?,?)";
    if ($stmt = $this->db->prepare($qry)) {
      $Abteilung = $this->man->input["ABTEILUNG"] ?? null;
      $Jahr = $this->man->input["JAHR"] ?? 0;
      $Name = $this->man->input["NAME"] ?? "";
      $stmt->bind_param(
        "iis",
        $Abteilung,
        $Jahr,
        $Name
      );
      $stmt->execute();
      if ($stmt->affected_rows==1) {
        $this->man->AddMessage("Dienstplan wurde hinzugefügt!");
        $this->get($this->db->insert_id);
        return 201;
      } else {
        $this->man->AddMessage("Dienstplan konnte nicht hinzugefügt werden! (".$Abteilung." / ".$Jahr." / ".$Name.")");
        return 400;
      }
    } else {
      $this->man->AddMessage("Mysql error: ".$this->db->error);
    }
    return 500;
  }
    
  protected function get($id = null) {
  	$this->output = [];
    $filterID = false;
    if ($id!=null) {
      $filterID = true;
    }
    $qry = "SELECT td.ID, td.Abteilung, td.Jahr, td.Name, sa.Kürzel saKürzel, sa.Name saName "
          ."FROM Termine_Dienstpläne td LEFT JOIN Struktur_Abteilungen sa ON sa.ID=td.Abteilung "
          .($filterID ? "WHERE td.ID = ? " : "")
          ."ORDER BY td.Jahr DESC, td.Name DESC, sa.ID ASC";
    if ($stmt = $this->db->prepare($qry)) {
      if ($filterID) {
        $stmt->bind_param("i", $id);
      }
      $stmt->execute();
      $res = $stmt->get_result();
      while ($row = $res->fetch_assoc()) {
        $termine = $this->termineGetAll($row["ID"]);
        
        $entry = [
          "ID" => $row["ID"],
          "MAIN" => [
            "FULLNAME" => $row["Jahr"]."-".$row["Name"],
            "ABTEILUNG" => ["ID" => $row["Abteilung"], "KÜRZEL" => $row["saKürzel"], "NAME" => $row["saName"]],
            "JAHR" => $row["Jahr"],
            "NAME" => $row["Name"],
          ],
          "SUB" => [
            "TERMINE" => $termine,
          ],
        ];
        $this->addEntryToOutput("DEFAULT", $entry, $filterID);
      }
    } else {
      $this->man->AddMessage("Mysql error: ".$this->db->error);
    }
    
    return 200;
  }
  
  protected function fillOptions($admin = false) {
    $ret = array();
    //$ret["ABTEILUNG"][] = ["ID" => "__NULL__", "NAME" => "Ohne"];
    $res = $this->db->query("SELECT ID, Name FROM Struktur_Abteilungen");
    while ($row = $res->fetch_assoc()) {
      $ret["ABTEILUNG"][] = ["ID" => $row["ID"], "NAME" => $row["Name"]];
    }
    $ret["TERMINE"] = $this->termineGetAll(null);
    return $ret;
  }
  
  private function termineGetAll($dienstplanID) {
    $ret = [];
    $qry = "SELECT t.ID, t.Beginn, t.Ende, t.Thema, t.Ort, t.Verantwortliche, t.Kategorie, tk.Name KategorieName, tk.Farbe KategorieFarbe "
          ."FROM Termine t LEFT JOIN Termine_Kategorien tk ON tk.ID=t.Kategorie WHERE t.Dienstplan ".($dienstplanID===null ? "IS NULL " : "= ".$dienstplanID." ")
          ."ORDER BY t.Beginn ASC ";
    if ($res = $this->db->query($qry)) {
      while ($row = $res->fetch_assoc()) {
        $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"]));
        
        $entry = [
          "ID" => $row["ID"],
          "BEGINN" => $row["Beginn"],
          "BEGINN.DATUM" => date("D, d.m.Y", strtotime($row["Beginn"])),
          "BEGINN.ZEIT" => date("H:i", strtotime($row["Beginn"])),
          "ENDE" => $row["Ende"],
          "ENDE.DATUM" => date("d.m.Y", strtotime($row["Ende"])),
          "ENDE.ZEIT" => 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),
          "ENDE" => $row["Ende"],
          "THEMA" => $row["Thema"],
          "ORT" => $row["Ort"],
          "KATEGORIE" => $row["Kategorie"],
          "KATNAME" => $row["KategorieName"],
          "KATFARBE" => $row["KategorieFarbe"],
          "VERANTWORTLICHE" => $row["Verantwortliche"] ?? "Keine Verantwortliche",
        ];
        $ret[] = $entry;
      }
    } else {
      $this->man->AddMessage("Mysql error: ".$this->db->error);
    }
    return $ret;
  }
}