<?php

class tom extends client
{
  public function saveUpdateTomData(array $values)
  {
    $updateArray                            = array(
        'frage'                             => makeHtmlEntities($values['frage']),
        'bemerkung_begruendung'             => makeHtmlEntities($values['bemerkung_begruendung']),
        'empfehlung'                        => makeHtmlEntities($values['empfehlung']),
        'geaendert'                         => time(),
        'status'                            => $values['status'],
    );
    $whereCause                             = 'id = :id';
    $whereVal                               = $values['id'];
    pdoDbUpdate("tom_kunden", $updateArray, $whereCause, $whereVal);
  }
  
  
  public function showQuestsByKundenIdAndQuest($kundenId, $questId)
  {
    $db                             = $this->Database;
    $sql                            = 'SELECT * FROM tom_kunden WHERE kunden_id = :kunden_id AND frage_id = :frage_id';
    $db->query($sql);
    $db->bind(':kunden_id', $kundenId);
    $db->bind(':frage_id', $questId);
    return $db->single();
  }
  
  public function getFirstQuest(int $kundenId)
  {
    $db                             = $this->Database;
    $sql                            = 'SELECT frage_id FROM tom_kunden WHERE kunden_id = :kunden_id AND themenblock_id = :themenblock_id ORDER BY themenfrage_id ASC LIMIT 1';
    $db->query($sql);
    $db->bind(':kunden_id', $kundenId);
    $db->bind(':themenblock_id', 1);
    $row                            = $db->single();
    return $row['frage_id'];
  }
  
  public function showCompletedPercent(int $kundenId)
  {
    $amountQuests                   = $this->getAmountQuestions($kundenId);
    $amountUncompletedQuests        = $this->getAmountUncompletedQuestions($kundenId);
    
    $length                         = round(100 / $amountQuests * ($amountQuests - $amountUncompletedQuests),1);
    return $length;
  }
  
  public function getAmountQuestions(int $kundenId)
  {
    $db                             = $this->Database;
    $sql                            = 'SELECT COUNT(frage_id) AS amount FROM `tom_kunden` WHERE kunden_id = :kunden_id AND status != :status';
    $db->query($sql);
    $db->bind(':kunden_id', $kundenId);
    $db->bind(':status', 4);
    $row                            = $db->single();
    return $row['amount'];
  }
  
  public function getAmountUncompletedQuestions(int $kundenId)
  {
    $db                             = $this->Database;
    $sql                            = 'SELECT COUNT(frage_id) AS amount FROM `tom_kunden` WHERE kunden_id = :kunden_id AND status >= :status';
    $db->query($sql);
    $db->bind(':kunden_id', $kundenId);
    $db->bind(':status', 5);
    $row                            = $db->single();
    return $row['amount'];
  }
  
  public function getDropdownThemenblock(int $kundenId)
  {
    $db                             = $this->Database;
    $sql                            = 'SELECT 
                                        tom_kunden.themenblock_id AS Tom_Themenblock_id, 
                                        MIN(tom_kunden.frage_id) AS Tom_frage_id, 
                                        tom_themenblock.themenblock AS TB_Name 
                                      FROM 
                                        tom_kunden 
                                      LEFT JOIN 
                                        tom_themenblock 
                                      ON 
                                        tom_kunden.themenblock_id = tom_themenblock.themenblock_id 
                                      WHERE 
                                        kunden_id = :kunden_id 
                                      GROUP BY 
                                        TB_Name';
    $db->query($sql);
    $db->bind(':kunden_id', $kundenId);
    return $db->resultset();
  }
  
  public function getDropdownThemenblockAll()
  {
    $db                             = $this->Database;
    $sql                            = 'SELECT * FROM tom_themenblock ORDER BY id';
    $db->query($sql);
    return $db->resultset();
  }
  
  public function getStatusColor(int $status)
  {
    switch ($status) {
      case 1:
        return "red";
        break;
      case 2:
        return "orange";
        break;
      case 3:
        return "green";
        break;
      case 4:
        return "gray";
        break;
      case 5:
        return "yellow";
        break;
      case 6:
        return "";
        break;
      default:
        return "";
    }
  }
  
  public function getStatusTextColor(int $status)
  {
    switch ($status) {
      case 1:
        return "#FFF";
        break;
      case 2:
        return "#FFF";
        break;
      case 3:
        return "#FFF";
        break;
      case 4:
        return "#FFF";
        break;
      case 5:
        return "#000";
        break;
      case 6:
        return "";
        break;
      default:
        return "#FFF";
    }
  }
  
  public function getThemenblockName(int $blockId)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT themenblock FROM tom_themenblock WHERE themenblock_id = :themenblock_id';
    $db->query($sql);
    $db->bind(':themenblock_id', $blockId);
    $row                              = $db->single();
    return $row['themenblock'];
  }
  
  public function getNextQuest($kundenId, $themenblockId, $frageId)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT frage_id as next_quest FROM tom_kunden WHERE kunden_id = :kunden_id AND themenblock_id = :themenblock_id AND themenfrage_id > :themenfrage_id ORDER BY themenfrage_id ASC LIMIT 1';
    $db->query($sql);
    $db->bind(':kunden_id', $kundenId);
    $db->bind(':themenblock_id', $themenblockId);
    $db->bind(':themenfrage_id', $frageId);
    $row                              = $db->single();
    if ($row['next_quest']            == "") {
      return '0';
    } else {
      return $row['next_quest'];
    }
  }
  
  public function getNextThemenblock($kundenId, $themenblockId)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT themenblock_id FROM tom_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 getPreviousQuest($kundenId, $themenblockId, $frageId)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT frage_id as previous_quest FROM tom_kunden WHERE kunden_id = :kunden_id AND themenblock_id = :themenblock_id AND themenfrage_id < :themenfrage_id ORDER BY themenfrage_id DESC LIMIT 1';
    $db->query($sql);
    $db->bind(':kunden_id', $kundenId);
    $db->bind(':themenblock_id', $themenblockId);
    $db->bind(':themenfrage_id', $frageId);
    $row                              = $db->single();
    if ($row['previous_quest']        == "") {
      return '0';
    } else {
      return $row['previous_quest'];
    }
  }
  
  public function getPreviousThemenblock($kundenId, $themenblockId)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT themenblock_id FROM tom_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 getLastQuest($kundenId, $themenblockId)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT frage_id as last_quest FROM tom_kunden WHERE kunden_id = :kunden_id AND themenblock_id = :themenblock_id ORDER BY themenfrage_id DESC LIMIT 1';
    $db->query($sql);
    $db->bind(':kunden_id', $kundenId);
    $db->bind(':themenblock_id', $themenblockId);
    $row                              = $db->single();
    return $row['last_quest']; 
  }
  
  public function getStatus()
  {
    $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 getStatusVal(int $value)
  {
    switch ($value) {
      case 1:
        return 'nicht erfüllt / fehlt - KRITISCH';
        break;
      case 2:
        return 'nicht erfüllt / fehlt';
        break;
      case 3:
        return 'erfüllt / ausreichend';
        break;
      case 4:
        return 'nicht zutreffend';
        break;
      case 5:
        return 'prüfen';
        break;
      case 6:
        return 'unbearbeitet';
        break;
    }
  }
  
  public function showTomTexts()
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT * FROM tom_vorlagen ORDER BY id ASC';
    $db->query($sql);
    return $db->resultset();
  }
  
  public function showTomTextsById(int $id)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT * FROM tom_vorlagen WHERE id = :id';
    $db->query($sql);
    $db->bind(':id', $id);
    return $db->single();
  }
  
  public function saveNewTomText(array $values)
  {
    $insertArray                      = array(
        'frage'                       => makeHtmlEntities($values['frage']),
        'themenfrage_id'              => $values['themenfrage_id'],
        'themenblock_id'              => $values['themenblock_id'],
        'empfehlung'                  => makeHtmlEntities($values['empfehlung']),
        'bemerkung_begruendung'       => makeHtmlEntities($values['bemerkung_begruendung']),
        'angelegt'                    => time(),
    );
    return pdoDbInsert("tom_vorlagen", $insertArray);
  }
  
  public function saveUpdateTomText(array $values)
  {
    $updateArray                      = array(
        'frage'                       => makeHtmlEntities($values['frage']),
        'themenfrage_id'              => $values['themenfrage_id'],
        'themenblock_id'              => $values['themenblock_id'],
        'empfehlung'                  => makeHtmlEntities($values['empfehlung']),
        'bemerkung_begruendung'       => makeHtmlEntities($values['bemerkung_begruendung']),
        'geaendert'                   => time(),
    );
    $whereCause                             = 'id = :id';
    $whereVal                               = $values['id'];
    pdoDbUpdate("tom_vorlagen", $updateArray, $whereCause, $whereVal);
  }
  
  public function getUsedThemenFrageId(int $id)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT themenfrage_id FROM tom_vorlagen WHERE themenblock_id = :themenblock_id ORDER BY themenfrage_id ASC';
    $db->query($sql);
    $db->bind(':themenblock_id', $id);
    return $db->resultset();
  }
  
  public function getNonusedTomVorlagen($frageId)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT COUNT(id) as amount FROM tom_kunden WHERE frage_id = :frage_id';
    $db->query($sql);
    $db->bind(':frage_id', $frageId);
    $row                              = $db->single();
    return $row['amount'];
  }
  
  public function setTextToKunden(int $frageId)
  {
    $rowTomText                       = $this->showTomTextsById($frageId);
    $db                               = $this->Database;
    $sql                              = 'SELECT a.kunden_id FROM tom_kunden a WHERE NOT EXISTS (SELECT kunden_id FROM tom_kunden b WHERE b.kunden_id = a.kunden_id and b.frage_id = :frage_id) GROUP BY kunden_id';
    $db->query($sql);
    $db->bind(':frage_id', $frageId);
    $rows                             = $db->resultset();
    
    foreach ($rows as $row) {
      $insertArray                  = array(
          'kunden_id'               => $row['kunden_id'],
          'frage_id'                => $frageId,
          'frage'                   => $rowTomText['frage'],
          'bemerkung_begruendung'   => $rowTomText['bemerkung_begruendung'],
          'themenblock_id'          => $rowTomText['themenblock_id'],
          'themenfrage_id'          => $rowTomText['themenfrage_id'],
          'empfehlung'              => $rowTomText['empfehlung'],
          'angelegt'                => time(),
          'status'                  => 5,
          'belong_to_user_uuid'     => $_SESSION[ALLOW],
      );
      pdoDbInsert("tom_kunden", $insertArray);
    }
  }
  
  public function deleteTomText(int $id)
  {
    $db                               = $this->Database;
    $sql                              = 'DELETE FROM tom_vorlagen WHERE id = :id';
    $db->query($sql);
    $db->bind(':id', $id);
    $db->execute();
    
    $sqlTomKunden                     = 'DELETE FROM tom_kunden WHERE frage_id = :frage_id';
    $db->query($sqlTomKunden);
    $db->bind(':frage_id', $id);
    $db->execute();
  }
  
  public function deleteTomTextKunden(int $id)
  {
    $db                               = $this->Database;
    $sql                              = 'DELETE FROM tom_kunden WHERE id = :id';
    $db->query($sql);
    $db->bind(':id', $id);
    return $db->execute();
  }
  
  public function showTomData(int $kundenId)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT * FROM tom_kunden WHERE kunden_id = :kunden_id ORDER BY themenblock_id ASC, themenfrage_id ASC';
    $db->QUery($sql);
    $db->bind(':kunden_id', $kundenId);
    return $db->resultset();
  }
  
  public function showStatistics()
  {
    $db                               = $this->Database;
    $sqlThemenblock                   = 'SELECT  * FROM tom_themenblock ORDER BY themenblock_id ASC';
    $db->query($sqlThemenblock);
    return $db->resultset();
  }
  
  public function getAmountQuestsPerBlock($kundenId, $themenblockId)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT COUNT(id) as amount FROM tom_kunden WHERE kunden_id = :kunden_id AND themenblock_id = :themenblock_id';
    $db->query($sql);
    $db->bind(':kunden_id', $kundenId);
    $db->bind(':themenblock_id', $themenblockId);
    $row                              = $db->single();
    return $row['amount'];
  }
  
  public function getAmountQuestsPerBlockByStatus($kundenId, $themenblockId, $status)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT COUNT(id) as amount FROM tom_kunden WHERE kunden_id = :kunden_id AND themenblock_id = :themenblock_id AND status = :status';
    $db->query($sql);
    $db->bind(':kunden_id', $kundenId);
    $db->bind(':themenblock_id', $themenblockId);
    $db->bind(':status', $status);
    $row                              = $db->single();
    return $row['amount'];
  }
  
  public function getAmountQuestsTotal(int $kundenId)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT COUNT(id) as amount FROM tom_kunden WHERE kunden_id = :kunden_id';
    $db->query($sql);
    $db->bind(':kunden_id', $kundenId);
    $row                              = $db->single();
    return $row['amount'];
  }
  
  public function getAmountQuestsTotalByStatus($kundenId, $status)
  {
    $db                               = $this->Database;
    $sql                              = 'SELECT COUNT(id) as amount FROM tom_kunden WHERE kunden_id = :kunden_id AND status = :status';
    $db->query($sql);
    $db->bind(':kunden_id', $kundenId);
    $db->bind(':status', $status);
    $row                              = $db->single();
    return $row['amount'];
  }
}

