<?php
/**
 * @copyright (c) Kufer Software GmbH, Oderstraße 7, D-84453 Mühldorf
 */

define('BA_REPLIKAT', 1);
define('BA_DIREKT', 2);
define('BA_HYBRID', 3);



class LibInformation
{
  protected static $dbConnection = array();
  protected static $cms;
  protected static $cmsVersion;
  protected static $basedir;
  protected static $rootdir;

  public function __construct()
  {
    static::$basedir = str_replace('\\', '/', BASEDIR);
    static::$rootdir = dirname(static::$basedir, 2);
  }

  
  public static function isDirWriteable($pDirName, $recursive = false, $maxDepth = 5)
  {
    return FSOperations::isDirWriteable($pDirName, $recursive, $maxDepth);
  }

  
  public static function checkDbConnection($pNum)
  {
    $dboperation = DBOperation::getInstance($pNum);
    if ($dboperation == null) {
      return false;
    }

    return $dboperation->checkConnection();
  }

  
  public static function isDbParadox()
  {
    $conInfo = DBConnectionInfo::getInstance();
    if ($conInfo->db_typ == 'MYSQL' || $conInfo->db_typ == 'MYSQLI') {
      $dbOperation = DBOperation::getInstance(1);
      if (!$dbOperation->checkTableExists('hlsemester')) {
        return true;
      }
    }

    return false;
  }

  
  public static function checkAdminUser()
  {
    $adminUsers = new AdminUsers();
    $users = $adminUsers->getAllUsers();
    if (array_key_exists('su', $users) && $users['su']->checkPasswordOnly('kufer')) {
      return false;
    }

    if (array_key_exists('su', $users) && $users['su']->checkPasswordOnly('su')) {
      return false;
    }

    if (array_key_exists('admin', $users) && $users['admin']->checkPasswordOnly('password')) {
      return false;
    }

    return true;
  }

  
  public static function checkMailConfig(?string $testEMail)
  {
    $sender = \Kufer\Mail\Sender\EMailSenderFactory::create();

    $mail = new \Kufer\Mail\EMail\EMailHTML(
      KUFERWEB . '/' . KUFERTOOLS . ': Test E-Mail',
      'Wenn Sie diese E-Mail erhalten, sind die SMTP Einstellungen korrekt.'
    );

    $recipientSettings = null;
    if (!is_null($testEMail)) {
      $recipientSettings = new Kufer\Web\Classes\Mail\Sender\Settings\RecipientEinrichtung\EMailEinrichtungRecipientCustom($testEMail);
    }

    return $sender->informEinrichtung($mail, null, $recipientSettings)->hasSentAnyMails();
  }

  
  public static function checkCURLConfig()
  {
    $curl = curl_init();
    curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, true);

    $url = WEBSERVICE_URL . 'checkconnection';
    curl_setopt($curl, CURLOPT_URL, $url);
    if (CURL_USE) {
      if (CURL_PROXYSERVER != '') {
        curl_setopt($curl, CURLOPT_PROXY, CURL_PROXYSERVER);
      }
      if (CURL_PROXYPASS != '') {
        curl_setopt($curl, CURLOPT_PROXYUSERPWD, CURL_PROXYPASS);
      }
    }
    curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);

    curl_setopt($curl, CURLOPT_CONNECTTIMEOUT, 0);
    curl_setopt($curl, CURLOPT_TIMEOUT, 5); 

    $response = curl_exec($curl);
    if (!$response) {
      return array('status' => 0, 'error' => curl_error($curl));
    } else {
      return array('status' => 1);
    }
  }

  
  public static function getMemoryLimit()
  {
    return ini_get('memory_limit');
  }

  public static function getPostMaxSize()
  {
    return ini_get('post_max_size');
  }

  public static function getUploadMaxFileSize()
  {
    return ini_get('upload_max_filesize');
  }

  public static function getMssqlTextSize()
  {
    return ini_get('mssql.textsize');
  }

  public static function getMssqlTextLimit()
  {
    return ini_get('mssql.textlimit');
  }

  
  public static function getSaveMode()
  {
    if (trim(ini_get('save_mode')) == '') {
      $value = 'Off';
    } else {
      $value = ini_get('save_mode');
    }

    return $value;
  }

  public static function getFileUploads()
  {
    if (trim(ini_get('file_uploads')) == '') {
      $value = 'Off';
    } else {
      $value = ini_get('file_uploads');
    }

    return $value;
  }

  
  public static function getSemlistWb()
  {
    $toolsettings = ToolSettingsAPI::getToolSettings(4);

    return trim($toolsettings->semlist);
  }

  
  public static function getAvailableSemester()
  {
    $semapi = new SemesterHlAPI();

    return $semapi->getSemester(true, true, true);
  }

  
  public static function getSearchFields($pField)
  {
    $dbwb = new DBWorkKurseBase();
    switch ($pField) {
      default:
        return '';
      case 'stichwort':
        return $dbwb->getAvailableSearchFieldsStichwort();
      case 'ort':
        return $dbwb->getAvailableSearchFieldsOrt();
    }
  }

  
  public function getCountKurse()
  {
    $dbop = DBOperation::getInstance(1, true);
    $dbwk = new DBWorkKurseINetAktiv();
    $dbwk->fromobject = $dbwk->getNewObject();
    $dbwk->toobject = $dbwk->getNewObject();
    $dbwk->fromobject->semester = $this->getSemlistWb();
    $sql = 'SELECT count(nr) anzahl FROM kurse k WHERE 1 = 1';
    $sql .= $dbwk->getSearchSQL('k.');
    $dbop->clear();
    $dbop->query($sql);
    $dbop->selectrow(0);

    return $dbop->fieldbyname('anzahl');
  }

  
  public static function getCountCategoriesWithKurs()
  {
    $dbwc = new DBWorkCategories();

    return count($dbwc->getCategoriesWithKursINetAktiv());
  }

  
  public static function getBetriebsart()
  {
    $conInfo = DBConnectionInfo::getInstance();

    $dbtyp = StringOperations::strtolower($conInfo->db_typ);

    if (($dbtyp == 'mysql' || $dbtyp == 'mysqli') && !$conInfo->db_hybrid) {
      return BA_REPLIKAT;
    } elseif ($dbtyp == 'mssql' || $dbtyp == 'oracle' || $dbtyp == 'mssqlodbc' || $dbtyp == 'sqlsrv') {
      return BA_DIREKT;
    } else {
      return BA_HYBRID;
    }
  }

  
  public function getErrorArray()
  {
    $errors = array();
    $update = new LibUpdate();

    
    $conInfo = DBConnectionInfo::getInstance();
    if (!$this->checkDbConnection(1)) {
      $errors[] = array('text' => 'Die Verbindung zur Datenbank (1) kann <b>NICHT</b> aufgebaut werden bzw. es wurde noch kein Ersttransfer durchgeführt.', 'type' => 'error');
    } elseif (!$this->checkDbConnection(2)) {
      $errors[] = array('text' => 'Die Verbindung zur MSSQL Datenbank (2) kann <b>NICHT</b> aufgebaut werden.', 'type' => 'error');
    }

    if ($this->isDbParadox()) {
      $errors[] = array('text' => 'KuferWIN wird nicht mehr unterstützt! Sie können die Datenbank-Einstellungen '
          . '<a href="../webtools/config/dbeinstell.php">hier</a> ändern.', 'type' => 'error');
    }

    if ($conInfo->db_typ == 'MSSQL' || $conInfo->db2_typ == 'MSSQL') {
      $textlimit = $this->getMssqlTextLimit();
      $textsize = $this->getMssqlTextsize();
      $minimum = 512000;
      if ($textlimit < $minimum) {
        $errors[] = array('text' => 'mssql.textlimit sollte ' . $minimum . ' Bytes oder mehr betragen.', 'type' => 'warning', 'details' => '');
      }
      if ($textsize < $minimum) {
        $errors[] = array('text' => 'mssql.textsize sollte ' . $minimum . ' Bytes oder mehr betragen.', 'type' => 'warning', 'details' => '');
      }
    }

    if ($conInfo->db_typ == DBTypeEnum::MYSQLI) {
      try {
        if (\Kufer\Web\Classes\Db\DBWorkInformationSchemaTables::getDefaultInstance()->hasTemporaryKuferTransferTables()) {
          $errors[] = array(
            'text' => 'Tabellen einer abgebrochenen Übertragung existieren.',
            'type' => 'warning',
            'details' => 'Diese können unter '
               . '<a href="?action=functions&hardlink=mysql_struktur">"Funktionen / MySQL Struktur"</a> '
               . 'gelöscht werden'
          );
        }
      } catch (\Exception $ex) {
      }
    }

    
    if (!$this->checkAdminUser()) {
      $errors[] = array('text' => 'Der Standardbenutzer ist aktiv. Aus Sicherheitsgründen sollte er geändert werden!', 'type' => 'warning', 'details' => '');
    }
    $currDirName = ConstantOperations::getPathForKwCache();
    if (!FSOperations::isDirWriteable($currDirName)) {
      $errors[] = array('text' => 'Der aktuelle Pfad fürs Caching existiert nicht, oder ist nicht schreibbar: ' . $currDirName
        . '<br>Das Caching wird so nicht funktionieren!',
        'type' => 'warning',
        'details' => 'Sie können den Pfad unter <a href="?action=settings&hardlink=file_constants_allgemein">Einstellungen / Konstanten / Allgemein "Cacheverzeichnis"</a> ändern.');
    }
    
    $emailFilePath = \ToolSettingsAPI::getToolSettings(0)->emailfilepath;
    if (!FSOperations::isDirWriteable($emailFilePath)) {
      $errors[] = array('text' => 'Der aktuelle Pfad für die E-Mail-Dateien existiert nicht, oder ist nicht schreibbar: ' . $emailFilePath,
          'type' => 'warning',
          'details' => 'Sie können den Pfad unter <a href="?action=settings&sub1=2&sub2=5">"Einstellungen / System / Übermittlung der Anmeldungen"</a> ändern.');
    }
    
    $reqAry = $update->checkRequirements(Version::getCurrentVersion());
    foreach ($reqAry as $req) {
      if (!$req['check']) {
        $errors[] = array('text' => 'Systemvoraussetzung nicht erfüllt: ' . $req['label'] . ': ' . $req['value'], 'type' => 'error', 'details' => '');
      }
    }

    
    $basedir = str_replace('\\', '/', BASEDIR);
    $relDirs = array(
      'log/',
      'webtools/config/',
      'webbasys/layout/',
      'admin/backups/',
      'admin/updates/'
    );

    $hasKuferTools = \DBWorkTools::getDefaultInstance()->hasNotAllgemeineTools(false);

    
    if ($hasKuferTools) {
      array_push(
        $relDirs,
        'ablage/',
        'kufertools/settings/user/',
        'kufertools/settings/user/layout/'
      );
    }

    $dirs = array_map(static function(string $path) use ($basedir) {
      return \FSOperations::path($basedir, $path);
    }, $relDirs);
    unset($relDirs);

    if (SETTINGS_LOADFROMFILE) {
      $layoutpathwithDirSeparator = new Path(ConstantOperations::getPathForSettingsOrig());
      $layoutpath = $layoutpathwithDirSeparator->dirOnly();
      $dirs[] = $layoutpath->toString();
    }
    if (SIGHTS_LOADFROMFILE) {
      $sightspathwithDirSeparator = new Path(ConstantOperations::getPathForSightsOrig());
      $sightspath = $sightspathwithDirSeparator->dirOnly();
      $dirs[] = $sightspath->toString();
    }
    

    $settings = ToolSettingsAPI::getToolSettings(0);
    if ($settings->fileUploadPath != '') {
      $dirs[] = $settings->fileUploadPath;
    }

    foreach ($dirs as $dir) {
      if (!$this->isDirWriteable($dir, true, 2)) {
        $errors[] = array('text' => 'Keine Schreibberechtigung', 'details' => str_replace('/', DIRECTORY_SEPARATOR, $dir), 'type' => 'warning');
      }
    }

    
    if (version_compare(PHP_VERSION, '7.4.0') == -1 && function_exists('get_magic_quotes_gpc') && get_magic_quotes_gpc()) {
      $errors[] = array('text' => 'Die PHP-Einstellung "magic_quotes_gpc" ist aktiv. Es wird empfohlen diese zu deaktivieren, da sonst Probleme beim Bearbeiten der Sichten und in den Einstellungen auftreten können.', 'type' => 'warning', 'details' => '');
    }

    if (\SessionOperations::isReplikat() && $hasKuferTools) {
      $kurseCountByInternetaktiv = \DBWorkKurse::getDefaultInstance()->getKursCountByInternetaktiv();
      if ($kurseCountByInternetaktiv[true] > 0 && $kurseCountByInternetaktiv[false] === 0) {
        $errors[] = array(
          'text' => 'Es wurden keine Kurse ohne Internetfreigabe übertragen.',
          'details' => 'Aktivieren Sie bitte die Übertragung von Kursen ohne Internetfreigabe im Übertragungsprogramm.',
          'type' => 'warning'
        );
      }
    }

    return $errors;
  }

  
  public static function getReplicatedAt()
  {
    $dbwr = new DBWorkWebReplikatSettings();
    $dm = $dbwr->getReplicatedAt();

    return $dm->value;
  }

  public static function getBrVersion()
  {
    $dbwr = new DBWorkWebReplikatSettings();
    $dm = $dbwr->getSettingValue('brExeVersion');

    return $dm->value;
  }

  public static function getBrVersionKuferTransfer(): ?string
  {
    $dbwr = new DBWorkWebReplikatSettings();
    $dm = $dbwr->getSettingValue('brExeVersionKuferTransfer');
    if (is_null($dm->value) || $dm->value === '') {
      return null;
    }

    return $dm->value;
  }

  public function getBasysStructure()
  {
    if ($this->getBetriebsart() == BA_DIREKT) {
      $dbws = new DBWorkBASysSettings();
      $dm = $dbws->getSettings();

      return $dm->datum1;
    } else {
      $dbwr = new DBWorkWebReplikatSettings();
      $dm = $dbwr->getSettingValue('basysStructure');

      return $dm->value;
    }
  }

  public static function getReplikatStructure()
  {
    $dbwr = new DBWorkWebReplikatSettings();
    $dm = $dbwr->getSettingValue('structureVersion');

    return $dm->value;
  }

  public static function getPhpVersion()
  {
    return PHP_VERSION;
  }

  public static function isMariaDB()
  {
    $version = self::getMysqlVersion();

    return StringOperations::contains($version, 'MariaDB', false);
  }

  public static function getMariaDBVersion()
  {
    $version = self::getMysqlVersion();
    
    $matches = array();
    preg_match('/([0-9.]+)-MariaDB/', $version, $matches);

    if (isset($matches[1])) {
      return $matches[1];
    }

    return $version; 
  }

  public static function getMysqlVersion()
  {
    
    
    $dbop = DBOperation::getInstance(1);
    if ($dbop instanceof DBOperationMySQLi) {
      $dbop->openConnection();

      return $dbop->connection->server_info;
    } else {
      return mysql_get_server_info();
    }
  }

  
  public static function getDataNamesFromFolder($pFolderpath)
  {
    $pFolderpath = StringOperations::setLastChar($pFolderpath, '/');
    $cleanDataArray = array();
    $dataArray = \is_dir($pFolderpath) ? @scandir($pFolderpath) : false;
    if ($dataArray !== false) {
      foreach ($dataArray as $data) {
        if ($data != '.' && $data != '..') {
          array_push($cleanDataArray, $data);
        }
      }
    }

    return $cleanDataArray;
  }

  
  public function getCMS()
  {
    $t3Identifier = 'fileadmin';
    $wpIdentifier = 'wp-content';
    static::$cms = '';
    if (is_dir(static::$rootdir . '/' . $t3Identifier)) {
      static::$cms = 'TYPO3';
    } elseif (is_dir(static::$rootdir . '/' . $wpIdentifier)) {
      static::$cms = 'WordPress';
    }

    return static::$cms;
  }

  
  public function getCMSVersion()
  {
    if (static::$cms == 'TYPO3') {
      $versionFile = file_get_contents(static::$rootdir . '/typo3/sysext/core/Classes/Information/Typo3Version.php');
      $const = '/protected const VERSION = \'([\\d.]+)\';/m';
      preg_match_all($const, $versionFile, $match);
      static::$cmsVersion = $match[1][0];
    } elseif (static::$cms == 'WordPress') {
      include_once static::$rootdir . '/wp-includes/version.php';
      static::$cmsVersion = $wp_version;
    }

    return static::$cmsVersion;
  }
}


abstract class LibLogReader
{
  protected $content = false;
  public $logEntrys = array();

  
  public function getContent()
  {
    $logPath = ConstantOperations::getPathForLog();

    
    if (is_null($logPath . $this->filename) || !is_readable($logPath . $this->filename)) {
      return false;
    }

    
    $fileWork = new FileWork();
    $fileWork->filename = $logPath . $this->filename;
    $fileWork->getContent();
    $this->content = $fileWork->content;
    $fileWork->clear();
  }

  abstract protected function sortContent(): void;

  
  public function parseLog()
  {
    
    if (!$this->content) {
      return false;
    }

    
    $this->sortContent();

    
    setlocale(LC_ALL, 'de_DE');

    
    foreach ($this->content as $line) {
      preg_replace_callback($this->preg, array($this, 'logEntry'), $line);
    }
  }

  
  public function getQuartal()
  {
    
    $quartale = $this->getQuartale();

    
    $quartal = GetOperations::getVarValue('quartal', $quartale[0]);

    
    return (in_array($quartal, $quartale)) ? $quartal : $quartale[0];
  }

  public function getHeutigesQuartal()
  {
    $heute = DateTimeOperations::getDateNow();

    return DateTimeOperations::getYear($heute) . '-' . DateTimeOperations::getQuartal($heute);
  }

  
  public function getLength()
  {
    
    $default = 20;

    
    $length = intval(GetOperations::getVarValue('anzahl', $default));

    
    return ($length >= 1) ? $length : $default;
  }

  
  public function getCurrentPage()
  {
    
    $default = 1;

    $maxPages = $this->getNumPages();

    
    $page = intval(GetOperations::getVarValue('seite', $default));

    
    return ($page >= 1 && $page <= $maxPages) ? $page : $default;
  }

  
  public function getQuartale()
  {
    
    if (empty($this->logEntrys)) {
      return array(0 => $this->getHeutigesQuartal());
    }
    $array = array();
    
    foreach (array_keys($this->logEntrys) as $key) {
      $array[] = $key;
    }
    
    array_multisort($array, SORT_DESC);

    return $array;
  }

  
  public function getNumLogs()
  {
    $qutal = $this->getQuartal();
    if (isset($this->logEntrys[$qutal])) {
      return count($this->logEntrys[$qutal]);
    } else {
      return 0;
    }
  }

  
  public function getNumPages()
  {
    $quartal = $this->getQuartal();
    $length = $this->getLength();
    
    if (isset($this->logEntrys[$quartal])) {
      $numPages = count($this->logEntrys[$quartal]) / $length;
    } else {
      $numPages = 1;
    }
    $intPages = intval($numPages);

    
    return ($intPages >= $numPages) ? $intPages : $intPages + 1;
    
  }

  
  public function getLogEntrys($pDoSort = true)
  {
    $quartal = $this->getQuartal();
    $length = $this->getLength();
    $page = $this->getCurrentPage();

    $logEntries = isset($this->logEntrys[$quartal]) ? $this->logEntrys[$quartal] : array();

    
    $pointer = ($page - 1) * $length;

    
    if ($pDoSort) {
      
      
      krsort($logEntries);
    }

    return array_slice($logEntries, $pointer, $length);
  }
}


class LibErrLogReader extends LibLogReader
{
  protected $filename = '/log.txt';
  protected $preg = '/([0-9]{4})\\.([a-z|A-Z]{3})\\.([0-9]{2}) ([0-9]{2}):([0-9]{2}):([0-9]{2}): (.*)/';
  protected $errorMsg = array('type' => '', 'text' => '', 'error' => '');

  
  protected function sortContent(): void
  {
    
    $array = array();

    foreach ($this->content as $line) {
      
      if (preg_match('/([0-9]{4})\\.([a-z|A-Z]{3})\\.([0-9]{2}) ([0-9]{2}):[0-9]{2}:[0-9]{2}: (.*)/', $line)) {
        
        if (isset($errmsg)) {
          
          $array[] = $errmsg;
        }

        
        $errmsg = '';
      }
      if (!isset($errmsg)) {
        $errmsg = '';
      }

      
      $errmsg .= trim($line) . ' ';
    }

    
    $array[] = $errmsg;

    
    $this->content = $array;
  }

  
  protected function logEntry($line)
  {
    
    $months = array('Jan' => 1, 'Feb' => 2, 'Mar' => 3, 'Apr' => 4, 'May' => 5, 'Jun' => 6, 'Jul' => 7, 'Aug' => 8, 'Sep' => 9, 'Oct' => 10, 'Nov' => 11, 'Dec' => 12);
    $quartal = array('Jan' => 1, 'Feb' => 1, 'Mar' => 1, 'Apr' => 2, 'May' => 2, 'Jun' => 2, 'Jul' => 3, 'Aug' => 3, 'Sep' => 3, 'Oct' => 4, 'Nov' => 4, 'Dec' => 4);

    
    $timestamp = mktime($line[4], $line[5], $line[6], $months[$line[2]], $line[3], $line[1]);

    
    $year = \DateTimeOperations::datefmt('yyyy', $timestamp);

    
    $preg = $this->getRegExpString();
    preg_replace_callback($preg, array($this, 'formatOutput'), $line[0]);
    
    $this->logEntrys[$year . '-' . $quartal[$line[2]]][] = array(
        'time' => \DateTimeOperations::datefmt('dd.MM. HH:mm:ss', $timestamp),
        'type' => $this->errorMsg['type'],
        'text' => $this->errorMsg['text'],
        'error' => $this->errorMsg['error'],
        'severity' => $this->errorMsg['severity']
    );
  }

  protected function getRegExpString()
  {
    return '/[0-9]{4}\\.[a-z|A-Z]{3}\\.[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}: Es ist ein kritischer Fehler aufgetreten: (.*?)!(.*?): (.*)/';
  }

  
  protected function formatOutput($msg)
  {
    $msgtext = \trim(\CleanOperations::cleanStringForHtml($msg[2]));
    $brhtml = \CleanOperations::cleanStringForHtml('<br/>');
    if (\StringOperations::startsWith($msgtext, $brhtml)) {
      $msgtext = \StringOperations::substr($msgtext, \StringOperations::strlen($brhtml));
    }
    
    $search = array('<br/>',   ',',      '  ');
    $replace = array(' ',       ', ',     ' ');
    $text = trim(\StringOperations::str_ireplace($search, $replace, $msgtext));

    
    $preg = '/([\'"].*?[\'"])/';
    $text = preg_replace($preg, '<span class="string">$1</span>', $text);

    
    $preg = '/SELECT[\\n\\r ]([^<].*?)[\\n\\r ]FROM/sim';
    while (preg_match($preg, $text) === 1) {
      $text = preg_replace($preg, 'SELECT <span class="information">$1</span> FROM', $text);
    }

    
    $preg = '/([\\) ]?)(\\(?)(SELECT|FROM|WHERE|ORDER)/i';
    $text = preg_replace($preg, '$1<br />$2$3', $text);

    
    $preg = '/(\\(|\\))/';
    $text = preg_replace($preg, '<span class="marker">$1</span>', $text);

    
    $preg = '/([>()\\ ]{1})(ALTER(?:\\s+DEFINER\\s*=\\s*\\S+)?\\s+EVENT|(ALTER(?:\\s+ONLINE|\\s+OFFLINE)?(?:\\s+IGNORE)?\\s+TABLE)|(ALTER(?:\\s+ALGORITHM\\s*=\\s*(?:UNDEFINED|MERGE|TEMPTABLE))?(?:\\s+DEFINER\\s*=\\s*\\S+)?(?:\\s+SQL\\s+SECURITY\\s+(?:DEFINER|INVOKER))?\\s+VIEW)|(ANALYZE(?:\\s+NO_WRITE_TO_BINLOG|\\s+LOCAL)?\\s+TABLE)|(CREATE(?:\\s+DEFINER\\s*=\\s*\\S+)?\\s+EVENT)|(CREATE(?:\\s+DEFINER\\s*=\\s*\\S+)?\\s+FUNCTION)|(CREATE(?:\\s+DEFINER\\s*=\\s*\\S+)?\\s+PROCEDURE)|(CREATE(?:\\s+ONLINE|\\s+OFFLINE)?(?:\\s+UNIQUE|\\s+FULLTEXT|\\s+SPATIAL)?\\s+INDEX)|(CREATE(?:\\s+TEMPORARY)?\\s+TABLE)|(CREATE(?:\\s+DEFINER\\s*=\\s*\\S+)?\\s+TRIGGER)|(CREATE(?:\\s+OR\\s+REPLACE)?(?:\\s+ALGORITHM\\s*=\\s*(?:UNDEFINED|MERGE|TEMPTABLE))?(?:\\s+DEFINER\\s*=\\s*\\S+)?(?:\\s+SQL\\s+SECURITY\\s+(?:DEFINER|INVOKER))?\\s+VIEW)|(DROP(?:\\s+ONLINE|\\s+OFFLINE)?\\s+INDEX)|(DROP(?:\\s+TEMPORARY)?\\s+TABLE)|(OPTIMIZE(?:\\s+NO_WRITE_TO_BINLOG|\\s+LOCAL)?\\s+TABLE)|(REPAIR(?:\\s+NO_WRITE_TO_BINLOG|\\s+LOCAL)?\\s+TABLE)|(SET(?:\\s+GLOBAL|\\s+SESSION)?\\s+TRANSACTION)|(SHOW(?:\\s+FULL)?\\s+COLUMNS)|(SHOW(?:\\s+STORAGE)?\\s+ENGINES)|(SHOW\\s+(?:INDEX|INDEXES|KEYS))|(SHOW(?:\\s+FULL)?\\s+PROCESSLIST)|(SHOW(?:\\s+GLOBAL|\\s+SESSION)?\\s+STATUS)|(SHOW(?:\\s+FULL)?\\s+TABLES)|(SHOW(?:\\s+GLOBAL|\\s+SESSION)?\\s+VARIABLES)|(ALTER\\s+(?:DATABASE|SCHEMA)|ALTER\\s+LOGFILE\\s+GROUP|ALTER\\s+SERVER|ALTER\\s+TABLESPACE|BACKUP\\s+TABLE|CACHE\\s+INDEX|CALL|CHANGE\\s+MASTER\\s+TO|CHECK\\s+TABLE|CHECKSUM\\s+TABLE|CREATE\\s+(?:DATABASE|SCHEMA)|CREATE\\s+LOGFILE\\s+GROUP|CREATE\\s+SERVER|CREATE\\s+TABLESPACE|CREATE\\s+USER|DELETE|DESCRIBE|DO|DROP\\s+(?:DATABASE|SCHEMA)|DROP\\s+EVENT|DROP\\s+FUNCTION|DROP\\s+PROCEDURE|DROP\\s+LOGFILE\\s+GROUP|DROP\\s+SERVER|DROP\\s+TABLESPACE|DROP\\s+TRIGGER|DROP\\s+USER|DROP\\s+VIEW|EXPLAIN|FLUSH|GRANT|HANDLER|HELP|INSERT|INSTALL\\s+PLUGIN|JOIN|KILL|LOAD\\s+DATA\\s+FROM\\s+MASTER|LOAD\\s+DATA|PURGE\\s+MASTER\\s+LOGS|RENAME\\s+(?:DATABASE|SCHEMA)|RENAME\\s+TABLE|RENAME\\s+USER|REPLACE|RESET\\s+MASTER|RESET\\s+SLAVE|RESTORE\\s+TABLE|REVOKE|SELECT|SET\\s+PASSWORD|SHOW\\s+AUTHORS|SHOW\\s+BINARY\\s+LOGS|SHOW\\s+BINLOG\\s+EVENTS|SHOW\\s+CHARACTER\\s+SET|SHOW\\s+COLLATION|SHOW\\s+CONTRIBUTORS|SHOW\\s+CREATE\\s+(?:DATABASE|SCHEMA)|SHOW\\s+CREATE\\s+TABLE|SHOW\\s+CREATE\\s+VIEW|SHOW\\s+(?:DATABASE|SCHEMA)S|SHOW\\s+ENGINE|SHOW\\s+ERRORS|SHOW\\s+GRANTS|SHOW\\s+MASTER\\s+STATUS|SHOW\\s+OPEN\\s+TABLES|SHOW\\s+PLUGINS|SHOW\\s+PRIVILEGES|SHOW\\s+SCHEDULER\\s+STATUS|SHOW\\s+SLAVE\\s+HOSTS|SHOW\\s+SLAVE\\s+STATUS|SHOW\\s+TABLE\\s+STATUS|SHOW\\s+TRIGGERS|SHOW\\s+WARNINGS|SHOW|START\\s+SLAVE|STOP\\s+SLAVE|TRUNCATE|UNINSTALL\\s+PLUGIN|UNION|UPDATE|USE)|(START\\s+TRANSACTION|COMMIT|ROLLBACK)|(SAVEPOINT|ROLLBACK\\s+TO\\s+SAVEPOINT)|((?:UN)?LOCK\\s+TABLES?)|(SET\\s+NAMES|SET\\s+CHARACTER\\s+SET)|(IN\\s+BOOLEAN\\s+MODE|IN\\s+NATURAL\\s+LANGUAGE\\s+MODE|WITH\\s+QUERY\\s+EXPANSION)|(IS|IS\\s+NULL)|(BETWEEN|NOT\\s+BETWEEN|IN|NOT\\s+IN)|(ANY|SOME)|(ALL)|(EXISTS|NOT\\s+EXISTS)|(WITH\\s+ROLLUP)|(SOUNDS\\s+LIKE)|(LIKE|NOT\\s+LIKE)|(NOT\\s+REGEXP|REGEXP)|(RLIKE)|(NOT|AND|OR|XOR)|(CASE)|(DIV)|(BINARY)|(CURRENT_DATE|CURRENT_TIME|CURRENT_TIMESTAMP|LOCALTIME|LOCALTIMESTAMP)|(INTERVAL)|(ACCESSIBLE|ADD|ALTER|ANALYZE|AS|ASC|ASENSITIVE|BEFORE|BOTH|BY|CASCADE|CHANGE|CHARACTER|CHECK|COLLATE|COLUMN|CONDITION|CONSTRAINT|CONTINUE|CONVERT|CREATE|CROSS|CURRENT_USER|CURSOR|DATABASE|DATABASES|DAY_HOUR|DAY_MICROSECOND|DAY_MINUTE|DAY_SECOND|DECLARE|DEFAULT|DELAYED|DESC|DETERMINISTIC|DISTINCT|DISTINCTROW|DROP|DUAL|EACH|ELSE|ELSEIF|ENCLOSED|ESCAPED|EXIT|FALSE|FETCH|FLOAT4|FLOAT8|FOR|FORCE|FOREIGN|FROM|FULLTEXT|GROUP|HAVING|HIGH_PRIORITY|HOUR_MICROSECOND|HOUR_MINUTE|HOUR_SECOND|IF|IGNORE|INDEX|INFILE|INNER|INOUT|INSENSITIVE|INT1|INT2|INT3|INT4|INT8|INTO|ITERATE|KEY|KEYS|LEADING|LEAVE|LEFT|LIMIT|LINEAR|LINES|LOAD|LOCK|LONG|LOOP|LOW_PRIORITY|MASTER_SSL_VERIFY_SERVER_CERT|MATCH|MINUTE_MICROSECOND|MINUTE_SECOND|MOD|MODIFIES|NATURAL|NO_WRITE_TO_BINLOG|NULL|ON|OPTIMIZE|OPTION|OPTIONALLY|ORDER|OUT|OUTER|OUTFILE|PRECISION|PRIMARY|PROCEDURE|PURGE|RANGE|READ|READS|READ_WRITE|REFERENCES|RELEASE|RENAME|REPEAT|REQUIRE|RESTRICT|RETURN|RIGHT|SCHEMA|SCHEMAS|SECOND_MICROSECOND|SENSITIVE|SEPARATOR|SET|SPATIAL|SPECIFIC|SQL|SQLEXCEPTION|SQLSTATE|SQLWARNING|SQL_BIG_RESULT|SQL_CALC_FOUND_ROWS|SQL_SMALL_RESULT|SSL|STARTING|STRAIGHT_JOIN|TABLE|TERMINATED|THEN|TO|TRAILING|TRIGGER|TRUE|UNDO|UNIQUE|UNLOCK|UNSIGNED|USAGE|USING|UTC_DATE|UTC_TIME|UTC_TIMESTAMP|VALUES|VARCHARACTER|VARYING|WHEN|WHERE|WHILE|WITH|WRITE|XOR|YEAR_MONTH|ZEROFILL))\\b(?!\\()|\\b(bit|tinyint|bool|boolean|smallint|mediumint|int|integer|bigint|float|double\\s+precision|double|real|decimal|dec|numeric|fixed|(date|datetime|timestamp|time|year)|(varchar|binary|varbinary|tinyblob|tinytext|blob|text|mediumblob|mediumtext|longblob|longtext|enum))\\b|\\b(coalesce|greatest|isnull|interval|least|(if|ifnull|nullif)|(ascii|bin|bit_length|char|char_length|character_length|concat|concat_ws|conv|elt|export_set|field|find_in_set|format|hex|insert|instr|lcase|left|length|load_file|locate|lower|lpad|ltrim|make_set|mid|oct|octet_length|ord|position|quote|repeat|replace|reverse|right|rpad|rtrim|soundex|sounds_like|space|substr|substring|substring_index|trim|ucase|unhex|upper)|(strcmp)|(abs|acos|asin|atan|atan2|ceil|ceiling|cos|cot|crc32|degrees|exp|floor|ln|log|log2|log10|mod|pi|pow|power|radians|rand|round|sign|sin|sqrt|tan|truncate)|(adddate|addtime|convert_tz|curdate|current_date|curtime|current_time|current_timestamp|date|datediff|date_add|date_format|date_sub|day|dayname|dayofmonth|dayofweek|dayofyear|extract|from_days|from_unixtime|get_format|hour|last_day|localtime|localtimestamp|makedate|maketime|microsecond|minute|month|monthname|now|period_add|period_diff|quarter|second|sec_to_time|str_to_date|subdate|subtime|sysdate|time|timediff|timestamp|timestampadd|timestampdiff|time_format|time_to_sec|to_days|unix_timestamp|utc_date|utc_time|utc_timestamp|week|weekday|weekofyear|year|yearweek)|(cast)|(extractvalue|updatexml)|(bit_count)|(aes_encrypt|aes_decrypt|compress|decode|encode|des_decrypt|des_encrypt|encrypt|md5|old_password|password|sha|sha1|uncompress|uncompressed_length)|(benchmark|charset|coercibility|collation|connection_id|current_user|database|found_rows|last_insert_id|row_count|schema|session_user|system_user|user|version)|(default|get_lock|inet_aton|inet_ntoa|is_free_lock|is_used_lock|master_pos_wait|name_const|release_lock|sleep|uuid|uuid_short|values)|(avg|bit_and|bit_or|bit_xor|count|count_distinct|group_concat|min|max|std|stddev|stddev_pop|stddev_samp|sum|var_pop|var_samp|variance)|(row)|(match|against))([()\\ ]{1})/i';
    $text = preg_replace($preg, '$1<u class="sql">$2</u>$3', $text);

    
    $preg = '/^(<br \\/>)+/';
    $text = preg_replace($preg, '', $text);

    
    $severity = (int)filter_var($msg[1], FILTER_SANITIZE_NUMBER_INT);
    
    $this->errorMsg = array(
          'type' => $msg[1],
          'text' => $text,
          'error' => $msg[3],
          'severity' => $severity
    );
  }
}

class LibDebugLogReader extends LibErrLogReader
{
  protected $filename = '/debug.txt';

  protected function getRegExpString()
  {
    return '/[0-9]{4}\\.[a-z|A-Z]{3}\\.[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}: (\\(\\d+\\)) message=\'(.*?)\' (file=\'.*) (function=\'.*) (line=\'.*)/';
  }

  
  public function getLength()
  {
    
    $default = 20;

    
    $length = intval(PostOperations::getVarValue('anzahl', $default));

    
    return ($length >= 1) ? $length : $default;
  }

  
  public function getQuartal()
  {
    
    
    $quartale = $this->getQuartale();

    
    
    $quartal = PostOperations::getVarValue('quartal', $quartale[0]);

    
    return (in_array($quartal, $quartale)) ? $quartal : $quartale[0];
  }

  
  public function getCurrentPage()
  {
    
    $default = 1;

    $maxPages = $this->getNumPages();

    
    
    $page = PostOperations::getVarValue('seite', $default);

    
    return ($page >= 1 && $page <= $maxPages) ? $page : $default;
  }

  
  protected function logEntry($line)
  {
    
    $months = array('Jan' => 1, 'Feb' => 2, 'Mar' => 3, 'Apr' => 4, 'May' => 5, 'Jun' => 6, 'Jul' => 7, 'Aug' => 8, 'Sep' => 9, 'Oct' => 10, 'Nov' => 11, 'Dec' => 12);
    $quartal = array('Jan' => 1, 'Feb' => 1, 'Mar' => 1, 'Apr' => 2, 'May' => 2, 'Jun' => 2, 'Jul' => 3, 'Aug' => 3, 'Sep' => 3, 'Oct' => 4, 'Nov' => 4, 'Dec' => 4);

    
    $timestamp = mktime($line[4], $line[5], $line[6], $months[$line[2]], $line[3], $line[1]);

    
    $year = \DateTimeOperations::datefmt('yyyy', $timestamp);

    
    $preg = $this->getRegExpString();
    preg_replace_callback($preg, array($this, 'formatOutput'), $line[0]);
    
    $this->logEntrys[$year . '-' . $quartal[$line[2]]][] = array(
      'time' => \DateTimeOperations::datefmt('dd.MM. HH:mm:ss', $timestamp),
      'type' => $this->errorMsg['type'],
      'text' => $this->errorMsg['text'],
      'error' => $this->errorMsg['error'],
      'severity' => $this->errorMsg['severity']
    );
  }

  
  protected function formatOutput($msg)
  {
    parent::formatOutput($msg);
    
    $this->errorMsg['type'] = \CleanOperations::cleanStringForHtml(StringOperations::substr($msg[2], 0, 60));
  }
}


class LibAnmLogReader extends LibLogReader
{
  protected $filename = '/Anmeldelog.txt';
  protected $preg = '/([0-9]{4})\\.([a-z|A-Z]{3})\\.([0-9]{2}) ([0-9]{2}):([0-9]{2}):([0-9]{2}):  (.*)/s';

  
  protected function sortContent(): void
  {
    
    $months = array('Jan' => 1, 'Feb' => 2, 'Mar' => 3, 'Apr' => 4, 'May' => 5, 'Jun' => 6, 'Jul' => 7, 'Aug' => 8, 'Sep' => 9, 'Oct' => 10, 'Nov' => 11, 'Dec' => 12);

    
    $array = array();
    foreach ($this->content as $line) {
      
      $key = array();
      $key[] = preg_replace($this->preg, '$1', $line);
      $key[] = $months[trim(preg_replace($this->preg, '$2', $line))];
      $key[] = preg_replace($this->preg, '$3$4', $line);
      $key = implode('', $key);

      $line = \CleanOperations::cleanStringForHtml($line);

      
      if (array_key_exists($key, $array)) {
        $array[$key] .= '<br />' . $line;
      } else {
        $array[$key] = $line;
      }
    }

    
    ksort($array);

    
    $this->content = array();
    foreach ($array as $val) {
      $this->content[] = $val;
    }
  }

  
  protected function logEntry($line)
  {
    
    $months = array('Jan' => 1, 'Feb' => 2, 'Mar' => 3, 'Apr' => 4, 'May' => 5, 'Jun' => 6, 'Jul' => 7, 'Aug' => 8, 'Sep' => 9, 'Oct' => 10, 'Nov' => 11, 'Dec' => 12);
    $quartal = array('Jan' => 1, 'Feb' => 1, 'Mar' => 1, 'Apr' => 2, 'May' => 2, 'Jun' => 2, 'Jul' => 3, 'Aug' => 3, 'Sep' => 3, 'Oct' => 4, 'Nov' => 4, 'Dec' => 4);

    
    $timestamp = mktime($line[4], $line[5], $line[6], $months[$line[2]], $line[3], $line[1]);

    
    $year = \DateTimeOperations::datefmt('yyyy', $timestamp);

    
    $preg = '/([0-9]{4})\\.([a-z|A-Z]{3})\\.([0-9]{2}) ([0-9]{2}:[0-9]{2}:[0-9]{2}): (.*)/';
    $this->logEntrys[$year . '-' . $quartal[$line[2]]][] = array(
        'time' => \DateTimeOperations::datefmt('dd.MM. HH', $timestamp) . ' Uhr',
        'type' => '',
        'text' => preg_replace($preg, '<u>$4</u>: $5', $line[0]),
        'error' => ''
    );
  }
}


class LibLoginLogReader extends LibLogReader
{
  
  private $dbwWLP;

  
  private $filter;

  public function __construct()
  {
    $this->dbwWLP = new \DBWorkWebLoginProtocol(1, true);

    $this->filter = $this->createSQLFilter();
  }

  protected function sortContent(): void
  {
  }

  private function createSQLFilter(): \Kufer\Web\Classes\Dbfilter\WebLoginProtocolSQLFilter
  {
    $filter = \Kufer\Web\Classes\Dbfilter\WebLoginProtocolSQLFilter::create();

    $this->fillSearchModul($filter, $this->getModul());
    $this->fillSearchVorgang($filter, $this->getVorgang());

    
    

    $qrtlAry = explode('-', $this->getQuartal()); 
    $qMonth = $qrtlAry[1] * 3;
    if ($qMonth < 10) {
      $qMonth = '0' . $qMonth;
    }
    $qDate = '01.' . $qMonth . '.' . $qrtlAry[0];

    $filter->betweenZeitpunkt(
      \DateTimeOperations::getQuartalerstTag($qDate) . ' ' . DEFAULTTIME_WITH_SECONDS,
      \DateTimeOperations::getQuartalletztTag($qDate) . ' 23:59:59'
    );

    $filter->startsWithLogin($this->getLoginName());

    return $filter;
  }

  
  private function fillSearchModul(\Kufer\Web\Classes\Dbfilter\WebLoginProtocolSQLFilter $filter, $pModul): void
  {
    switch ($pModul) {
      default:
      case 'alle':
        return; 
      case 'kufertools':
        $filter->equalsModul(WebLoginProtocol::LOGIN_PROT_MODUL_KUFERTOOLS);
        break;
      case 'webadmin':
        $filter->equalsModul(WebLoginProtocol::LOGIN_PROT_MODUL_WEBADMIN);
    }
  }

  
  private function fillSearchVorgang(\Kufer\Web\Classes\Dbfilter\WebLoginProtocolSQLFilter $filter, $pVorgang): void
  {
    switch ($pVorgang) {
      default:
      case 'alle':
        return; 
      case 'login':
        $filter->equalsVorgang(WebLoginProtocol::LOGIN_PROT_VORGANG_LOGIN);
        break;
      case 'logout':
        $filter->equalsVorgang(WebLoginProtocol::LOGIN_PROT_VORGANG_LOGOUT);
        break;
      case 'loginerr':
        $filter->equalsVorgang(WebLoginProtocol::LOGIN_PROT_VORGANG_LOGIN_ERR);
        break;
    }
  }

  
  public function getContent()
  {
    
    $this->content = $this->dbwWLP->findByFilter($this->filter);
  }

  
  public function getQuartale()
  {
    
    $dtAry = $this->dbwWLP->getDateTimesOfFirstAndLast();
    if (false == $dtAry) {
      return array();
    }

    $firstDate = \StringOperations::substr($dtAry[0], 0, 10);
    $lastDate = \StringOperations::substr($dtAry[1], 0, 10);

    $lastQuartal = \DateTimeOperations::getQuartal($lastDate);
    $firstQuartal = \DateTimeOperations::getQuartal($firstDate);

    $lastYear = \DateTimeOperations::getYearFromValidDate($lastDate);
    $fistYear = \DateTimeOperations::getYearFromValidDate($firstDate);

    $aktQuartal = $firstQuartal;
    $aktYear = $fistYear;

    $qartalAry = $this->_getQuartaleBetweenDates($aktYear, $aktQuartal, $lastYear, $lastQuartal);

    return array_reverse($qartalAry);
  }

  
  private function _getQuartaleBetweenDates($pFromYear, $pFromQuartal, $pToYear, $pToQuartal)
  {
    $qartalAry = array();

    do {
      $qartalAry[] = $pFromYear . '-' . $pFromQuartal;

      $pFromQuartal++;
      if ($pFromQuartal > 4) {
        $pFromQuartal = 1;
        $pFromYear++;
      }
      
      
    } while ($pFromYear < $pToYear || ($pFromYear == $pToYear && $pFromQuartal <= $pToQuartal));

    return $qartalAry;
  }

  
  public function getModul()
  {
    return GetOperations::getVarValue('modul', 'alle');
  }

  
  public function getVorgang()
  {
    return GetOperations::getVarValue('vorgang', 'alle');
  }

  
  public function getLoginName()
  {
    return GetOperations::getVarValue('loginname', '');
  }

  
  public function parseLog()
  {
    foreach ($this->content as $loginProtocol) {
      $date = StringOperations::substr($loginProtocol->zeitpunkt, 0, 10);
      $quartal = \DateTimeOperations::getQuartal($date);
      $year = \DateTimeOperations::getYearFromValidDate($date);

      $datumStunde = StringOperations::substr($loginProtocol->zeitpunkt, 0, 6) . ' ' . StringOperations::substr($loginProtocol->zeitpunkt, 11, 2);

      if (!isset($this->logEntrys[$year . '-' . $quartal][$datumStunde])) {
        $this->logEntrys[$year . '-' . $quartal][$datumStunde] = array(
          'time' => $datumStunde . ' Uhr',
          'type' => '',
          'text' => $this->getText($loginProtocol),
          'error' => ''
          );
      } else {
        $this->logEntrys[$year . '-' . $quartal][$datumStunde]['text'] .= $this->getText($loginProtocol);
      }
    }

    foreach ($this->logEntrys as $key => $logEntry) {
      uksort($logEntry, array('LibLoginLogReader', 'sortLogEntrysDatum'));
      $this->logEntrys[$key] = $logEntry;
    }
  }

  
  public static function sortLogEntrysDatum($pKey1, $pKey2)
  {
    
    $date1 = StringOperations::substr($pKey1, 0, 6) . '2000'; 
    $date2 = StringOperations::substr($pKey2, 0, 6) . '2000';

    if ($date1 == $date2) {
      $time1 = StringOperations::substr($pKey1, 7, 2);
      $time2 = StringOperations::substr($pKey2, 7, 2);

      if ((int)($time1) < (int)($time2)) {
        return 1;
      } else {
        return -1;
      }
    } else {
      if (DateTimeOperations::isDateBefore($date1, $date2)) {
        return 1;
      } else {
        return -1;
      }
    }
  }

  
  protected function getText($pWebLoginProtocol)
  {
    $uhrzeit = \DateTimeOperations::getTimeStr($pWebLoginProtocol->zeitpunkt);
    $result = '<u>' . $uhrzeit . '</u> <b class="login_name">' . \CleanOperations::cleanStringForHtml($pWebLoginProtocol->login) . '</b> ';
    $class = '';

    switch ($pWebLoginProtocol->modul) {
      default:
      case WebLoginProtocol::LOGIN_PROT_MODUL_KUFERTOOLS:
        $programm = KUFERTOOLS;
        $class .= ' loginLog_kufertools';
        break;
      case WebLoginProtocol::LOGIN_PROT_MODUL_WEBADMIN:
        $programm = WEBADMIN;
        $class .= ' loginLog_webadmin';
        break;
    }

    switch ($pWebLoginProtocol->vorgang) {
      default:
      case WebLoginProtocol::LOGIN_PROT_VORGANG_LOGIN:
        $result .= 'hat sich erfolgreich in <b>' . $programm . '</b> eingeloggt.';
        $class .= ' loginLog_login';
        break;
      case WebLoginProtocol::LOGIN_PROT_VORGANG_LOGOUT:
        $result .= 'hat sich aus <b>' . $programm . '</b> ausgeloggt.';
        $class .= ' loginLog_logout';
        break;
      case WebLoginProtocol::LOGIN_PROT_VORGANG_LOGIN_ERR:
        $result .= 'hat sich erfolglos versucht in <b>' . $programm . '</b> einzuloggen.';
        $class .= ' loginLog_login_err';
        break;
    }

    return '<span class="loginLogRow ' . $class . '">' . $result . '</span>';
  }
}