<?php

class vvt extends client
{
  public function showVvtTexts()
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT * FROM vvt_vorlagen ORDER BY id ASC';
    $db->query($sql);
    return $db->resultset();
  }
  
  public function getNonusedVvtVorlagen(int $vvtId)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT COUNT(id) as amount FROM vvt_kunden WHERE vvt_id = :vvt_id';
    $db->query($sql);
    $db->bind(':vvt_id', $vvtId);
    $row                              = $db->single();
    return $row['amount'];
  }
  
  public function saveNewVvtText(array $values)
  {
    $insertArray                      = array(
        'name'                          => makeHtmlEntities($values['vvt_name']),
        'zweck'                         => makeHtmlEntities($values['zweck']),
        'beschr_warum_erlaubt'          => makeHtmlEntities($values['beschr_warum_erlaubt']),
        'betr_personen'                 => makeHtmlEntities($values['betr_personen']),
        'betr_daten_der_pers'           => makeHtmlEntities($values['betr_daten_der_pers']),
        'weitergabe'                    => makeHtmlEntities($values['weitergabe']),
        'uebermittlung_laender'         => makeHtmlEntities($values['uebermittlung_laender']),
        'aufbewahrungsfrist'            => makeHtmlEntities($values['aufbewahrungsfrist']),
        'tom'                           => makeHtmlEntities($values['tom']),
        'risiko_quellen_schaden'        => makeHtmlEntities($values['risiko_quellen_schaden']),
        'themenblock_id'                => $values['themenblock_id'],
        'angelegt'                      => time(),
    );
    return pdoDbInsert("vvt_vorlagen", $insertArray);
  }
  
  public function saveUpdateVvtText(array $values)
  {
    $updateArray                      = array(
        'name'                          => makeHtmlEntities($values['vvt_name']),
        'zweck'                         => makeHtmlEntities($values['zweck']),
        'beschr_warum_erlaubt'          => makeHtmlEntities($values['beschr_warum_erlaubt']),
        'betr_personen'                 => makeHtmlEntities($values['betr_personen']),
        'betr_daten_der_pers'           => makeHtmlEntities($values['betr_daten_der_pers']),
        'weitergabe'                    => makeHtmlEntities($values['weitergabe']),
        'uebermittlung_laender'         => makeHtmlEntities($values['uebermittlung_laender']),
        'aufbewahrungsfrist'            => makeHtmlEntities($values['aufbewahrungsfrist']),
        'tom'                           => makeHtmlEntities($values['tom']),
        'risiko_quellen_schaden'        => makeHtmlEntities($values['risiko_quellen_schaden']),
        'themenblock_id'                => $values['themenblock_id'],
        'geaendert'                     => time(),
    );
    $whereCause                             = 'id = :id';
    $whereVal                               = $values['id'];
    pdoDbUpdate("vvt_vorlagen", $updateArray, $whereCause, $whereVal);
  }
  
  public function saveUpdateVvtData(array $values)
  {
    $updateArray                      = array(
        'name'                          => makeHtmlEntities($values['vvt_name']),
        'zweck'                         => makeHtmlEntities($values['zweck']),
        'beschr_warum_erlaubt'          => makeHtmlEntities($values['beschr_warum_erlaubt']),
        'betr_personen'                 => makeHtmlEntities($values['betr_personen']),
        'betr_daten_der_pers'           => makeHtmlEntities($values['betr_daten_der_pers']),
        'weitergabe'                    => makeHtmlEntities($values['weitergabe']),
        'uebermittlung_laender'         => makeHtmlEntities($values['uebermittlung_laender']),
        'aufbewahrungsfrist'            => makeHtmlEntities($values['aufbewahrungsfrist']),
        'tom'                           => makeHtmlEntities($values['tom']),
        'risiko_quellen_schaden'        => makeHtmlEntities($values['risiko_quellen_schaden']),
        'verantwortliche_fachabteilung' => makeHtmlEntities($values['verantwortliche_fachabteilung']),
        'ds_folge_schaetzung'           => makeHtmlEntities($values['ds_folge_schaetzung']),
        'status'                        => $values['status'],
        'risiko_eintritt'               => $values['risiko_eintritt'],
        'risiko_schaden'                => $values['risiko_schaden'],
        'geaendert'                     => time(),
    );
    $whereCause                             = 'id = :id';
    $whereVal                               = $values['id_vvt'];
    pdoDbUpdate("vvt_kunden", $updateArray, $whereCause, $whereVal);
  }
  
  public function getDropdownVvtThemenblock(int $kundenId)
  {
    $db                             = $this->Database;
    $sql                            = 'SELECT 
                                        vvt_kunden.themenblock_id AS Vvt_Themenblock_id, 
                                        MIN(vvt_kunden.vvt_id) AS Vvt_vvt_id, 
                                        vvt_themenblock.themenblock AS TB_Name 
                                      FROM 
                                        vvt_kunden 
                                      LEFT JOIN 
                                        vvt_themenblock 
                                      ON 
                                        vvt_kunden.themenblock_id = vvt_themenblock.id 
                                      WHERE 
                                        kunden_id = :kunden_id 
                                      GROUP BY 
                                        TB_Name
                                      ORDER BY
                                        Vvt_Themenblock_id';
    $db->query($sql);
    $db->bind(':kunden_id', $kundenId);
    return $db->resultset();
  }
  
  public function getDropdownVvtThemenblockAll()
  {
    $db                             = $this->Database;
    $sql                            = 'SELECT * FROM vvt_themenblock ORDER BY id';
    $db->query($sql);
    return $db->resultset();
  }
  
  public function getVvtStatus()
  {
    $statusArray                      = array(
        'nicht erfüllt / fehlt - KRITISCH'    => 1,
        'nicht erfüllt / fehlt'               => 2,
        'erfüllt / ausreichend'               => 3,
        'nicht zutreffend'                    => 4,
        'prüfen'                              => 5,
        'unbearbeitet'                        => 6,
    );
    return $statusArray;
  }
  
  public function getVvtRiskProbability()
  {
    $ristArray                        = array(
        'gering'                      => 1,
        'möglich'                     => 2,
        'wahrschneinlich'             => 3,
    );
    return $ristArray;
  }
  
  public function getVvtRiskPotential()
  {
    $ristArray                        = array(
        'gering (kaum Auswirkungen)'                    => 1,
        'mittel (spürbare aber tragbare Auswirkungen)'   => 2,
        'hoch (schwerwiegend bis existenzbedrohend)'    => 3,
    );
    return $ristArray;
  }
  
  public function showVvtRiskProbability($val)
  {
    switch($val) {
      case 1:
        return 'gering';
        break;
      case 2:
        return 'möglich';
        break;
      case 3:
        return 'wahrschneinlich';
        break;
    }
  }
  
  public function showVvtRiskPotential($val)
  {
    switch($val) {
      case 1:
        return 'gering (kaum Auswirkungen)';
        break;
      case 2:
        return 'möglich (sürbare aber tragbare Auswirkungen)';
        break;
      case 3:
        return 'wahrschneinlich (schwerwiegend bis existenzbedrohend)';
        break;
    }
  }
  
  public function showVvtTextsById(int $id)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT * FROM vvt_vorlagen WHERE id = :id';
    $db->query($sql);
    $db->bind(':id', $id);
    return $db->single();
  }
  
  public function deleteVvtText(int $id)
  {
    $db                               = $this->Database;
    $sql                              = 'DELETE FROM vvt_vorlagen WHERE id = :id';
    $db->query($sql);
    $db->bind(':id', $id);
    $db->execute();
    
    $sqlVvtKunden                     = 'DELETE FROM vvt_kunden WHERE vvt_id = :vvt_id';
    $db->query($sqlVvtKunden);
    $db->bind(':vvt_id', $id);
    $db->execute();
  }
  
  public function setVvtTextToKunden(int $id)
  {
    $rowVvtText                       = $this->showVvtTextsById($id);
    $db                               = $this->Database;
    $sql                              = 'SELECT a.kunden_id FROM vvt_kunden a WHERE NOT EXISTS (SELECT kunden_id FROM vvt_kunden b WHERE b.kunden_id = a.kunden_id and b.vvt_id = :vvt_id) GROUP BY kunden_id';
    $db->query($sql);
    $db->bind(':vvt_id', $id);
    $rows                             = $db->resultset();
    foreach ($rows as $row) {
      $insertArray                          = array(
          'themenblock_id'                  => $rowVvtText['themenblock_id'],
          'kunden_id'                       => $row['kunden_id'],
          'name'                            => $rowVvtText['name'],
          'zweck'                           => $rowVvtText['zweck'],
          'beschr_warum_erlaubt'            => $rowVvtText['beschr_warum_erlaubt'],
          'betr_personen'                   => $rowVvtText['betr_personen'],
          'betr_daten_der_pers'             => $rowVvtText['betr_daten_der_pers'],
          'weitergabe'                      => $rowVvtText['weitergabe'],
          'uebermittlung_laender'           => $rowVvtText['uebermittlung_laender'],
          'aufbewahrungsfrist'              => $rowVvtText['aufbewahrungsfrist'],
          'tom'                             => $rowVvtText['tom'],
          'risiko_quellen_schaden'          => $rowVvtText['risiko_quellen_schaden'],
          'vvt_id'                          => $rowVvtText['id'],
          'angelegt'                        => time(),
          'status'                          => 6,
      );
      pdoDbInsert("vvt_kunden", $insertArray);
    }
  }
  
  public function getFirstVvtQuest(int $kunden_id)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT vvt_id FROM vvt_kunden WHERE kunden_id = :kunden_id AND themenblock_id = :themenblock_id ORDER BY id ASC LIMIT 1';
    $db->query($sql);
    $db->bind(':kunden_id', $kunden_id);
    $db->bind(':themenblock_id', 1);
    $row                              = $db->single();
    return $row['vvt_id'];
  }
  
  private function getFirstVvtSelectedThemenblock(int $kundenId)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT themenblock_id FROM vvt_kunden WHERE kunden_id = :kunden_id AND is_report = :is_report ORDER BY themenblock_id ASC LIMIT 1';
    $db->query($sql);
    $db->bind(':kunden_id', $kundenId);
    $db->bind(':is_report', 1);
    $row                              = $db->single();
    return $row['themenblock_id'];
  }
  
  public function getFirstVvtSelectedQuest(int $kundenId)
  {
    $db                               = $this->Database;
    $themenblockId                    = $this->getFirstVvtSelectedThemenblock($kundenId);
    $sql                              = 'SELECT vvt_id FROM vvt_kunden WHERE kunden_id = :kunden_id AND themenblock_id = :themenblock_id AND is_report = :is_report ORDER BY id ASC LIMIT 1';
    $db->query($sql);
    $db->bind(':kunden_id', $kundenId);
    $db->bind(':themenblock_id', $themenblockId);
    $db->bind(':is_report', 1);
    $row                              = $db->single();
    return $row['vvt_id'];
  }
  
  public function showQuestsByKundenIdAndQuest($kundenId, $vvtId)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT * FROM vvt_kunden WHERE kunden_id = :kunden_id AND vvt_id = :vvt_id';
    $db->query($sql);
    $db->bind(':kunden_id', $kundenId);
    $db->bind(':vvt_id', $vvtId);
    return $db->single();
  }
  
  public function getNextVvtQuest($kundenId, $themenblockId, $vvtId)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT vvt_id as next_quest FROM vvt_kunden WHERE kunden_id = :kunden_id AND themenblock_id = :themenblock_id AND vvt_id > :vvt_id ORDER BY vvt_id ASC LIMIT 1';
    $db->query($sql);
    $db->bind(':kunden_id', $kundenId);
    $db->bind(':themenblock_id', $themenblockId);
    $db->bind(':vvt_id', $vvtId);
    $row                              = $db->single();
    return $row['next_quest'];
  }
  
  public function getNextVvtSelectedQuest($kundenId, $themenblockId, $vvtId)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT vvt_id as next_quest FROM vvt_kunden WHERE kunden_id = :kunden_id AND themenblock_id = :themenblock_id AND vvt_id > :vvt_id AND is_report = :is_report ORDER BY vvt_id ASC LIMIT 1';
    $db->query($sql);
    $db->bind(':kunden_id', $kundenId);
    $db->bind(':themenblock_id', $themenblockId);
    $db->bind(':vvt_id', $vvtId);
    $db->bind(':is_report', 1);
    $row                              = $db->single();
    return $row['next_quest'];
  }
  
  public function getNextVvtThemenblock($kundenId, $themenblockId)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT themenblock_id FROM vvt_kunden WHERE kunden_id = :kunden_id AND themenblock_id > :themenblock_id ORDER BY themenblock_id ASC LIMIT 1';
    $db->query($sql);
    $db->bind(':kunden_id', $kundenId);
    $db->bind(':themenblock_id', $themenblockId);
    $row                              = $db->single();
    return $row['themenblock_id'];
  }
  
  public function getNextVvtSelectedThemenblock($kundenId, $themenblockId)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT themenblock_id FROM vvt_kunden WHERE kunden_id = :kunden_id AND themenblock_id > :themenblock_id AND is_report = :is_report ORDER BY themenblock_id ASC LIMIT 1';
    $db->query($sql);
    $db->bind(':kunden_id', $kundenId);
    $db->bind(':themenblock_id', $themenblockId);
    $db->bind(':is_report', 1);
    $row                              = $db->single();
    return $row['themenblock_id'];
  }
  
  public function getPreviousVvtQuest($kundenId, $themenblockId, $vvtId)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT vvt_id as previous_quest FROM vvt_kunden WHERE kunden_id = :kunden_id AND themenblock_id = :themenblock_id AND vvt_id < :vvt_id ORDER BY vvt_id DESC LIMIT 1';
    $db->query($sql);
    $db->bind(':kunden_id', $kundenId);
    $db->bind(':themenblock_id', $themenblockId);
    $db->bind(':vvt_id', $vvtId);
    $row                              = $db->single();
    return $row['previous_quest'];
  }
  
  public function getPreviousVvtSelectedQuest($kundenId, $themenblockId, $vvtId)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT vvt_id as previous_quest FROM vvt_kunden WHERE kunden_id = :kunden_id AND themenblock_id = :themenblock_id AND vvt_id < :vvt_id AND is_report = :is_report ORDER BY vvt_id DESC LIMIT 1';
    $db->query($sql);
    $db->bind(':kunden_id', $kundenId);
    $db->bind(':themenblock_id', $themenblockId);
    $db->bind(':vvt_id', $vvtId);
    $db->bind(':is_report', 1);
    $row                              = $db->single();
    return $row['previous_quest'];
  }
  
  public function getLastVvtQuest($kundenId, $themenblockId)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT vvt_id as previous_quest FROM vvt_kunden WHERE kunden_id = :kunden_id AND themenblock_id = :themenblock_id ORDER BY vvt_id DESC LIMIT 1';
    $db->query($sql);
    $db->bind(':kunden_id', $kundenId);
    $db->bind(':themenblock_id', $themenblockId);
    $row                              = $db->single();
    return $row['previous_quest'];
  }
  
  public function getLastVvtSelectedQuest($kundenId, $themenblockId)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT vvt_id as previous_quest FROM vvt_kunden WHERE kunden_id = :kunden_id AND themenblock_id = :themenblock_id AND is_report = :is_report ORDER BY vvt_id DESC LIMIT 1';
    $db->query($sql);
    $db->bind(':kunden_id', $kundenId);
    $db->bind(':themenblock_id', $themenblockId);
    $db->bind(':is_report', 1);
    $row                              = $db->single();
    return $row['previous_quest'];
  }
  
  public function getPreviousVvtThemenblock($kundenId, $themenblockId)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT themenblock_id FROM vvt_kunden WHERE kunden_id = :kunden_id AND themenblock_id < :themenblock_id ORDER BY themenblock_id DESC LIMIT 1';
    $db->query($sql);
    $db->bind(':kunden_id', $kundenId);
    $db->bind(':themenblock_id', $themenblockId);
    $row                              = $db->single();
    return $row['themenblock_id'];
  }
  
  public function getPreviousVvtSelectedThemenblock($kundenId, $themenblockId)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT themenblock_id FROM vvt_kunden WHERE kunden_id = :kunden_id AND themenblock_id < :themenblock_id AND is_report = :is_report ORDER BY themenblock_id DESC LIMIT 1';
    $db->query($sql);
    $db->bind(':kunden_id', $kundenId);
    $db->bind(':themenblock_id', $themenblockId);
    $db->bind(':is_report', 1);
    $row                              = $db->single();
    return $row['themenblock_id'];
  }
  
  public function getVvtThemenblockName($blockId)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT themenblock FROM vvt_themenblock WHERE id = :id';
    $db->query($sql);
    $db->bind(':id', $blockId);
    $row                              = $db->single();
    return $row['themenblock'];
  }
  
  public function deleteVvtTextKunden(int $id)
  {
    $db                               = $this->Database;
    $sql                              = 'DELETE FROM vvt_kunden WHERE id = :id';
    $db->query($sql);
    $db->bind(':id', $id);
    return $db->execute();
  }
  
  public function setReportToClient(int $idVvt)
  {
    $updateArray                      = array(
        'is_report'                   => 1,
    );
    $whereCause                       = 'id = :id';
    $whereVal                         = $idVvt;
    pdoDbUpdate("vvt_kunden", $updateArray, $whereCause, $whereVal);
  }
  
  public function getAmountSelectedVvtQuest(int $kundenId)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT COUNT(id) as amount FROM vvt_kunden WHERE kunden_id = :kunden_id AND is_report = :is_report';
    $db->query($sql);
    $db->bind(':kunden_id', $kundenId);
    $db->bind(':is_report', 1);
    $row                              = $db->single();
    return $row['amount'];
  }
  
  public function unselectVvtTextKunden(int $idVvt)
  {
    $updateArray                      = array(
        'is_report'                   => 0,
    );
    $whereCause                       = 'id = :id';
    $whereVal                         = $idVvt;
    pdoDbUpdate("vvt_kunden", $updateArray, $whereCause, $whereVal);
  }
  
  public function showVvtData(int $kundenId)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT * FROM vvt_kunden WHERE kunden_id = :kunden_id AND is_report = :is_report AND status != :status1 AND status != :status2';
    $db->query($sql);
    $db->bind(':kunden_id', $kundenId);
    $db->bind(':is_report', 1);
    $db->bind(':status1', 4);
    $db->bind(':status2', 6);
    return $db->resultset();
  }
  
  public function getYpos($val1, $val2)
  {
    if ($val1                       > $val2) {
      return $val1;
    } else {
      return $val2;
    }
  }
}

