<?php #pgs/xx_personal/class.php

class PersonalPage extends BasePage
{
  private $prefixImage = "upl/Personal/Bildadresse/";
  
  protected function remove($id) {
  	// TODO: Delete picture
  	return parent::remove($id);
  }
  
  protected function update($id) {
    $this->get($id);
    
    $qry = "UPDATE Personal SET OFnr = ?, Pnr = ?, Login = ?, Nachnamen = ?, Vornamen = ?, Kategorie = ?, Funktion = ? WHERE ID = ?";
    if ($stmt = $this->db->prepare($qry)) {
      $OFnr = $this->man->input["OFNR"] ?? $this->output["MAIN"]["OFNR"];
      $Pnr = $this->man->input["PNR"] ?? $this->output["MAIN"]["PNR"];
      $Login = $this->man->input["LOGIN"] ?? $this->output["MAIN"]["LOGIN"];
      $Nachnamen = $this->man->input["NACHNAMEN"] ?? $this->output["MAIN"]["NACHNAMEN"];
      $Vornamen = $this->man->input["VORNAMEN"] ?? $this->output["MAIN"]["VORNAMEN"];
      $Kategorie = $this->man->input["KATEGORIE"] ?? $this->output["MAIN"]["KATEGORIE"]["ID"];
      $Funktion = array_key_exists("FUNKTION", $this->man->input)
                ? $this->man->input["FUNKTION"]
                : (is_null($this->output["MAIN"]["FUNKTION"]) ? null : $this->output["MAIN"]["FUNKTION"]["ID"]);
      $stmt->bind_param(
        "iisssiii", 
        $OFnr,
        $Pnr,
        $Login,
        $Nachnamen,
        $Vornamen,
        $Kategorie,
        $Funktion,
        $id
      );
      if ($stmt->execute()) {
        $this->man->AddMessage("Personal 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() {
    $Pnr = $this->man->input["PNR"] ?? null;
    if ($this->man->input["AUTOPNR"] ?? false) {
      $res = $this->db->query("SELECT Pnr FROM Personal WHERE Pnr BETWEEN 500 AND 899 ORDER BY Pnr ASC");
      $Pnr = 500;
      while ($row = $res->fetch_assoc()) {
        if ($row["Pnr"]==$Pnr) {
          $Pnr++;
        } else {
          break;
    } } }
    $qry = "INSERT INTO Personal (OFnr, Pnr, Login, Nachnamen, Vornamen, Bildadresse, Kategorie, Funktion) VALUES (?,?,?,?,?,NULL,?,?)";
    if ($stmt = $this->db->prepare($qry)) {
      $OFnr = $this->man->input["OFNR"] ?? "56";
      $Login = $this->man->input["LOGIN"] ?? "";
      $Nachnamen = $this->man->input["NACHNAMEN"] ?? "";
      $Vornamen = $this->man->input["VORNAMEN"] ?? "";
      $Kategorie = $this->man->input["KATEGORIE"] ?? 0; // Insert will fail if not existing
      $Funktion = array_key_exists("FUNKTION", $this->man->input)
                ? $this->man->input["FUNKTION"]
                : null;
      $stmt->bind_param(
        "iisssii",
        $OFnr,
        $Pnr,
        $Login,
        $Nachnamen,
        $Vornamen,
        $Kategorie,
        $Funktion
      );
      $stmt->execute();
      if ($stmt->affected_rows==1) {
        $this->man->AddMessage("Personal wurde hinzugefügt!");
        $pid = $this->db->insert_id;
        
        $autoprozess = $this->man->input["AUTOPROZESS"] ?? "";
        if ($autoprozess!="") {
          $stmt2 = $this->db->prepare("SELECT Ziel FROM Prozesse WHERE Name = ?");
          $stmt2->bind_param("s", $autoprozess);
          $stmt2->execute();
          $res = $stmt2->get_result();
          while ($row = $res->fetch_assoc()) {
            $conditions = json_decode($row["Ziel"], true);
            foreach ($conditions as $cond) {
              switch ($cond["condition"]) {
                case "group_member":
                  $this->db->query("INSERT INTO Personal_Gruppen (Personal, Gruppen) VALUES (".$pid.", ".$cond["group"].")");
                  break;
        } } } }
        
        $this->get($pid);
        return 201;
      } else {
        $this->man->AddMessage("Personal konnte nicht hinzugefügt werden!");
        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 = "";
    $having = "";
    if ($id) {
      $where = "AND p.ID = ? ";
    } else if (sizeof($this->man->Filter())>0) {
      /*$where = "WHERE 1 ";*/
      foreach ($this->man->Filter() as $filter) {
        $and = "or"==$filter["and"] ? "OR " : "AND ";
        if ("none"==$filter["field"]) {
          $where.= $and."1 ";
        } else {
          switch ($filter["field"]) {
            case "OFNR":
              $where.= $and.$this->getFilterString($filter["op"], ["=", "<>", "<", ">"], "p", "OFnr", $filter["value"]);
              break;
            case "PNR":
              $where.= $and.$this->getFilterString($filter["op"], ["=", "<>", "<", ">"], "p", "Pnr", $filter["value"]);
              break;
            case "NACHNAMEN":
              $where.= $and.$this->getFilterString($filter["op"], ["like", "not like"], "p", "Nachnamen", $filter["value"]);
              break;
            case "VORNAMEN":
              $where.= $and.$this->getFilterString($filter["op"], ["like", "not like"], "p", "Vornamen", $filter["value"]);
              break;
            case "KATEGORIE":
              $where.= $and.$this->getFilterString($filter["op"], ["=", "<>"], "p", "Kategorie", $filter["value"]);
              break;
            case "FUNKTION":
              $where.= $and.$this->getFilterString($filter["op"], ["=", "<>"], "p", "Funktion", $filter["value"]);
              break;
            case "ABTEILUNGEN":
              $having.= $and."(1 ";
              foreach ($filter["value"] as $value) {
                $having.= "and ".$this->getFilterString($filter["op"], ["like", "not like"], "", "Abteilungen", ",".$value.",");
              }
              $having.= ") ";
              break;
            case "GRUPPEN":
              $having.= $and."(1 ";
              foreach ($filter["value"] as $value) {
                $having.= $and.$this->getFilterString($filter["op"], ["like", "not like"], "", "Gruppen", ",".$value.",");
              }
              $having.= ") ";
              break;
            case "LEHRGÄNGE":
              $having.= $and."(1 ";
              foreach ($filter["value"] as $value) {
                $having.= $and.$this->getFilterString($filter["op"], ["like", "not like"], "", "Lehrgänge", ",".$value.",");
              }
              $having.= ") ";
              break;
            case "EINWEISUNGEN":
              $having.= $and."(1 ";
              foreach ($filter["value"] as $value) {
                $having.= $and.$this->getFilterString($filter["op"], ["like", "not like"], "", "Einweisungen", ",".$value.",");
              }
              $having.= ") ";
              break;
            default:
              $where.= $and."1 ";
    } } } }
    
    
    $qry = "SELECT p.ID, p.OFnr, p.Pnr, p.Login, p.Vornamen, p.Nachnamen, p.Bildadresse, p.Kategorie, pk.Name KategorieName, "
          ."p.Funktion, pf.Name FunktionName, pf.Kürzel FunktionKürzel, "
          ."CONCAT(',', GROUP_CONCAT(DISTINCT pa.Abteilungen SEPARATOR ','), ',') Abteilungen, "
          ."CONCAT(',', GROUP_CONCAT(DISTINCT pg.Gruppen SEPARATOR ','), ',') Gruppen, "
          ."CONCAT(',', GROUP_CONCAT(DISTINCT pl.Lehrgänge SEPARATOR ','), ',') Lehrgänge, "
          ."CONCAT(',', GROUP_CONCAT(DISTINCT fe.Fahrzeuge SEPARATOR ','), ',') Einweisungen "
          ."FROM Personal p "
          ."LEFT JOIN Personal_Kategorien pk ON pk.ID=p.Kategorie "
          ."LEFT JOIN Personal_Funktionen pf ON pf.ID=p.Funktion "
          ."LEFT JOIN Personal_Abteilungen pa ON pa.Personal=p.ID "
          ."LEFT JOIN Struktur_Abteilungen sa ON sa.ID=pa.Abteilungen "
          ."LEFT JOIN Personal_Gruppen pg ON pg.Personal=p.ID "
          ."LEFT JOIN Struktur_Gruppen sg ON sg.ID=pg.Gruppen "
          ."LEFT JOIN Personal_Lehrgänge pl ON pl.Personal=p.ID "
          ."LEFT JOIN Lehrgänge l ON l.ID = pl.Lehrgänge "
          ."LEFT JOIN Fahrzeuge_Einweisungen fe ON fe.Personal=p.ID "
          ."LEFT JOIN Fahrzeuge f ON f.ID=fe.Fahrzeuge "
          ."WHERE 1 ".$where
          ."GROUP BY p.ID "
          ."HAVING 1 ".$having
          ."ORDER BY p.Nachnamen ASC, p.Vornamen ASC ";
    if ($stmt = $this->db->prepare($qry)) {
      if ($id) {
        $stmt->bind_param("i", $id);
      } else {
        $this->registerGroup("Führung");
        $this->registerGroup("Kraftfahrer");
        $this->registerGroup("Mannschaft");
      }
      $stmt->execute();
      $res = $stmt->get_result();
      while ($row = $res->fetch_assoc()) {
        $entry = [
          "ID" => $row["ID"],
          "MAIN" => [
            "OFNR" => $row["OFnr"],
            "PNR" => $row["Pnr"],
            "NR" => $row["OFnr"]."-".$row["Pnr"],
            "LOGIN" => $row["Login"],
            "EMAIL" => $row["Login"]."@feuerwehr-bs.net",
            "VORNAMEN" => $row["Vornamen"],
            "NACHNAMEN" => $row["Nachnamen"],
            "NAME" => substr($row["Vornamen"], 0, 1).". ".$row["Nachnamen"],
            "BILD" => [
              "EXISTIERT" => file_exists($this->prefixImage.$row["Bildadresse"]) && null!==$row["Bildadresse"],
              "ADRESSE" => $row["Bildadresse"],
              "PFAD" => "/".$this->prefixImage,
            ],
            "KATEGORIE" => ["ID" => $row["Kategorie"], "NAME" => $row["KategorieName"]],
            "FUNKTION" => isset($row["Funktion"]) ? ["ID" => $row["Funktion"], "NAME" => $row["FunktionName"], "KÜRZEL" => $row["FunktionKürzel"]] : null,
            "ADMIN" => $this->man->user->HasRight($this->adminRight),
          ],
          "SUB" => [
            "ABTEILUNGEN" => $this->getSub("ABTEILUNGEN", $row["Abteilungen"]),
            "GRUPPEN" => $this->getSub("GRUPPEN", $row["Gruppen"]),
            "LEHRGÄNGE" => $this->getSub("LEHRGÄNGE", $row["Lehrgänge"]),
            "EINWEISUNGEN" => $this->getSub("EINWEISUNGEN", $row["Einweisungen"]),
          ],
        ];
        $this->addEntryToOutput($row["KategorieName"], $entry, $id);
      }
      if ($id && $res->num_rows==0) {
        $this->man->AddMessage("Couldn't find requested resource!");
        return 404;
      }
    } else {
      $this->man->AddMessage("Mysql error: ".$this->db->error." / Qry: ".$qry);
      return 500;
    }
    return 200;
  }
  
  protected function fillOptions($admin = false) {
    $ret = array();
    $ret["FUNKTION"][] = ["ID" => "__NULL__", "KÜRZEL" => "-", "NAME" => "Ohne"];
    $res = $this->db->query("SELECT ID, Kürzel, Name FROM Personal_Funktionen");
    while ($row = $res->fetch_assoc()) {
      $ret["FUNKTION"][] = ["ID" => $row["ID"], "KÜRZEL" => $row["Kürzel"], "NAME" => $row["Name"]];
    }
    $res = $this->db->query("SELECT ID, Kürzel, Name FROM Lehrgänge");
    while ($row = $res->fetch_assoc()) {
      $ret["LEHRGÄNGE"][] = ["ID" => $row["ID"], "KÜRZEL" => $row["Kürzel"], "NAME" => $row["Name"]];
    }
    $res = $this->db->query("SELECT ID, Kürzel, Name FROM Fahrzeuge");
    while ($row = $res->fetch_assoc()) {
      $ret["EINWEISUNGEN"][] = ["ID" => $row["ID"], "KÜRZEL" => $row["Kürzel"], "NAME" => $row["Name"]];
    }
    $res = $this->db->query("SELECT ID, Kürzel, Name FROM Struktur_Abteilungen");
    while ($row = $res->fetch_assoc()) {
      $ret["ABTEILUNGEN"][] = ["ID" => $row["ID"], "KÜRZEL" => $row["Kürzel"], "NAME" => $row["Name"]];
    }
    $res = $this->db->query("SELECT ID, Kürzel, Name FROM Struktur_Gruppen");
    while ($row = $res->fetch_assoc()) {
      $ret["GRUPPEN"][] = ["ID" => $row["ID"], "KÜRZEL" => $row["Kürzel"], "NAME" => $row["Name"]];
    }
    $res = $this->db->query("SELECT ID, Name FROM Personal_Kategorien");
    while ($row = $res->fetch_assoc()) {
      $ret["KATEGORIE"][] = ["ID" => $row["ID"], "NAME" => $row["Name"]];
    }
    return $ret;
  }
}