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

use Kufer\Web\Classes\Addfunctions\Guid;
use Kufer\Web\Classes\Identifier\Identifier;


abstract class DBWork
{
  
  protected $searchInfoExtractor;

  public function setSearchInfoExtractor($pExtractor)
  {
    $this->searchInfoExtractor = $pExtractor;
  }

  
  public $dboperation;
  
  
  public $orderby = '';
  public $orderby2 = '';
  public $orderby3 = '';
  public $orderby4 = '';
  public $orderby5 = '';
  public $orderby6 = '';
  public $orderby7 = '';
  public $orderby8 = '';
  public $orderby9 = '';
  public $orderby10 = '';
  
  public $orderbyasc = true;
  public $orderbyasc2 = true;
  public $orderbyasc3 = true;
  public $orderbyasc4 = true;
  public $orderbyasc5 = true;
  public $orderbyasc6 = true;
  public $orderbyasc7 = true;
  public $orderbyasc8 = true;
  public $orderbyasc9 = true;
  public $orderbyasc10 = true;
  
  
  public $fromobject;
  
  public $toobject;
  
  
  public $orgdata = true;
  
  
  public $admin = false;
  
  
  public $select = '';
  
  
  public $stringop;
  
  public $datetimeop;
  
  public $mathop;
  
  public $webprotidwk;

  
  public $countBefore;
  
  public $countBehind;
  
  public $countAll;
  
  public $dbtyp = 1;
  
  public $_updateBuffer;

  
  public $seitenind_anfary;
  public $seitenind_endary;
  
  public $buildseitenindex = true;

  
  public $updateAlways = array();

  
  public $tableName = '';

  
  protected $translationMapping = array();

  
  public function __construct($pDBnr = 1, $pNewInstance = false)
  {
    $this->dbtyp = $pDBnr;
    $this->dboperation = DBOperation::getInstance($pDBnr, $pNewInstance, false);

    $this->stringop = new StringOperations();
    $this->datetimeop = new DateTimeOperations($this->dboperation->getDBName());
    $this->mathop = new MathOperations();
    $this->webprotidwk = new DBWorkWarenkorbProtocol();
    
    $this->orgdata = (!$this->dboperation->connectionInfo->db_replikat) || ($pDBnr == 2);
    if (SessionOperations::getVarValueWTIntern('admin_id', '##') == '##') {
      SessionOperations::setVarValueWTIntern('admin_id', -1);
    }  
    $this->admin = (SessionOperations::getVarValueWTIntern('admin_id') == 1);
    $this->_updateBuffer = array();
    
    
    $this->countBefore = 0;
    $this->countBehind = 0;
    $this->countAll = 0;
  }

  abstract public function getTableName(): string;

  public function getTableNameWithoutAlias(): string
  {
    return \ArrayOperations::firstValue(explode(' ', $this->getTableName()));
  }

  public function getTablePraeffix(): string
  {
    return \ArrayOperations::lastValue(explode(' ', $this->getTableName()));
  }

  public function getSelectQueryBuilder(): \SelectQueryBuilder
  {
    return new SelectQueryBuilder($this->dboperation);
  }

  public function getSqlSnippetBuilder(): \SQLSnippetBuilder
  {
    return new SQLSnippetBuilder($this->dboperation);
  }

  public function getUpdateQueryBuilder(): \UpdateQueryBuilder
  {
    return new UpdateQueryBuilder($this->dboperation);
  }

  public function getInsertIntoQueryBuilder(): \InsertIntoQueryBuilder
  {
    return new InsertIntoQueryBuilder($this->dboperation);
  }

  
  public function isTableEmpty(): bool
  {
    $sql = 'SELECT count(*) as count from ' . $this->getTableName();
    $this->dboperation->clear();
    $this->dboperation->query($sql);

    $count = $this->getDatamodulValue('count');

    return $count < 1;
  }

  
  public function getCurrentRow($refreshCache = false)
  {
  }

  
  public function getNewObject()
  {
    return new Datamodul();
  }

  
  public function getFirstRowSingleColumn(string $pColumnName)
  {
    $this->dboperation->resetToFirstRow();

    while ($this->dboperation->selectNextRow()) {
      return $this->getDatamodulValue($pColumnName);
    }

    return null;
  }

  
  public function getFirstRow(bool $pRefreshCache = false)
  {
    return $this->getFirstRowOrNull($pRefreshCache) ?? $this->getNewObject();
  }

  
  public function getFirstRowOrNull(bool $pRefreshCache = false)
  {
    if ($this->dboperation->rowcount() > 0) {
      $this->dboperation->selectrow(0);
      $tmp = $this->getCurrentRow($pRefreshCache);

      
      
      $ary = array($tmp);
      $translatedAry = $this->postGetAllRowsHook($ary, $pRefreshCache);
      if (isset($translatedAry[0])) {
        return $translatedAry[0];
      }
    }

    return null;
  }

  
  public function getLimitRows($limit, $forward = true, $idfield = '', $idvalue = '', $inFlag = '')
  {
    $ary = array();
    
    if ($limit <= 0) {
      return array();
    }
    $rows = $this->dboperation->rowcount();
    
    if ($forward) {
      $zaehler = 0;
      if ($idfield != '') {
        $this->dboperation->selectrow($zaehler); 
        
        if (strcmp($this->getDatamodulValue($idfield), $idvalue) === 0) {
          $zaehler++;
        } else {
          
          

          while ((strcmp($this->getDatamodulValue($idfield), $idvalue) !== 0) && ($zaehler <= $rows)) {
            $this->dboperation->selectrow($zaehler);
            $zaehler++;
          }
        }
      }
      
      if ($inFlag == 'true') {
        if (($zaehler % $limit) == 0 && $zaehler > $limit) {
          $zaehler -= $limit;
        } else {
          $zaehler = $zaehler - ($zaehler % $limit);
        }
      }
      $count = min($limit, ($rows - $zaehler));
      $count--;
      if ($rows > 0) {
        $ary = array();
        for ($i = 0;$i <= $count;$i++) {
          $this->dboperation->selectrow($i + $zaehler);
          $ary[$i] = $this->getCurrentRow();
        }
        $ary = $this->postGetAllRowsHook($ary);
      }
      $this->countBefore = $zaehler;
    }  
    else { 
      $zaehler = $rows - 1;
      if ($idfield != '') {
        $this->dboperation->selectrow($zaehler);
        if (strcmp($this->getDatamodulValue($idfield), $idvalue) === 0) {
          $zaehler--;
        } else {
          while ((strcmp($this->getDatamodulValue($idfield), $idvalue) !== 0) && ($zaehler > -1)) {
            $this->dboperation->selectrow($zaehler);
            $zaehler--;
          }
        }
        $zaehler++;
      }
      
      if ($inFlag == 'true') {
        $zaehler = ($zaehler - ($zaehler % $limit)) + $limit;
      }
      $count = min($limit, $zaehler);
      if ($rows > 0) {
        $c = 0;
        for ($i = ($zaehler - $count);$i < $zaehler;$i++) {
          $this->dboperation->selectrow($i);
          $ary[$c] = $this->getCurrentRow();
          $c++;
        }
        $ary = $this->postGetAllRowsHook($ary);
      }
      $this->countBefore = ($zaehler - $count);
    } 
    $this->countBehind = max(0, ($rows - $this->countBefore - $limit));
    $this->countAll = $rows;

    return $ary;
  }

  
  public function getAllRows(bool $pRefreshCache = false): array
  {
    $this->dboperation->resetToFirstRow();

    $ary = array(); 
    while ($this->dboperation->selectNextRow()) {
      $tmp = $this->getCurrentRow($pRefreshCache);
      if (isset($tmp)) {
        $ary[] = $tmp;
      }
    }

    return $this->postGetAllRowsHook($ary, $pRefreshCache);
  }

  
  public function getAllRowsMappedByAttribute(string $keyAttrName, bool $refreshCache = false): array
  {
    $this->dboperation->resetToFirstRow();

    $result = array();
    while ($this->dboperation->selectNextRow()) {
      $row = $this->getCurrentRow($refreshCache);
      $result[$row->{$keyAttrName}] = $row;
    }

    return $result;
  }

  
  public function getAllRowsStream($pRefreshCache = false): \Kufer\Web\Classes\Stream\AbstractStream
  {
    $ary = $this->getAllRows($pRefreshCache);

    return new \Kufer\Web\Classes\Stream\ArrayStream($ary);
  }

  
  public function getAllRowsGenerator($pRefreshCache = false)
  {
    $this->dboperation->resetToFirstRow();

    while ($this->dboperation->selectNextRow()) {
      yield \ArrayOperations::firstValue(
        $this->postGetAllRowsHook(array($this->getCurrentRow()), $pRefreshCache)
      );
    }
  }

  
  public function getAllRowsCustom(array $columns): array
  {
    $this->dboperation->resetToFirstRow();

    $result = array();
    while ($this->dboperation->selectNextRow()) {
      $tmp = new stdClass();
      foreach ($columns as $fieldname => $vartyp) {
        $tmp->{$fieldname} = $this->getValue($fieldname, $vartyp);
      }
      $result[] = $tmp;
    }

    return $result;
  }

  
  public function getAllRowsSingleColumn(string $pColumnName): array
  {
    $this->dboperation->resetToFirstRow();

    $result = array();
    while ($this->dboperation->selectNextRow()) {
      $result[] = $this->getDatamodulValue($pColumnName);
    }

    return $result;
  }

  
  public function getAllRowsSingleColumnUnique(string $pColumnName): array
  {
    $this->dboperation->resetToFirstRow();

    $result = array();
    while ($this->dboperation->selectNextRow()) {
      $value = $this->getDatamodulValue($pColumnName);
      $result[(string)$value] = $value;
    }

    return array_values($result);
  }

  
  public function newSearch(): void
  {
    $this->closeSearch();
    $this->fromobject = $this->getNewObject();
    $this->toobject = $this->getNewObject();
  }

  
  public function closeSearch(): void
  {
    unset($this->fromobject,$this->toobject);
  }

  
  public function getSearchSQL($praeffix)
  {
    return '';
  }

  
  public function createSearchSQL($praeffix, $builder)
  {
    return $builder;
  }

  
  public function getSQLValue($fieldname, $datamodul)
  {
    $varTyp = $datamodul->getTyp($fieldname);
    switch ($varTyp) {
      default:
      case \DatamodulVarType::TYPE_STRING:
        return $this->dboperation->getEnclosedString4Sql($datamodul->getVarValue($fieldname));
      case \DatamodulVarType::TYPE_INT:
        $value = $datamodul->getVarValue($fieldname);
        if (empty($value)) {
          return 0;
        }

        return $this->dboperation->getInt4Sql($value);
      case \DatamodulVarType::TYPE_DATE:
        return $this->datetimeop->getFormatDate4Server($datamodul->getVarValue($fieldname));
      case \DatamodulVarType::TYPE_DATETIME:
        return $this->datetimeop->getFormatDateTime4Server($datamodul->getVarValue($fieldname));
      case \DatamodulVarType::TYPE_TIME:
        return $this->datetimeop->getFormatDateTime4Server(DEFAULTDATE . ' ' . DateTimeOperations::getValidTimeString($datamodul->getVarValue($fieldname)));
      case \DatamodulVarType::TYPE_FLOAT:
        return $this->mathop->stringToFloat($datamodul->getVarValue($fieldname));
      case \DatamodulVarType::TYPE_FLOAT_DOT:
        return (float)$datamodul->getVarValue($fieldname);
      case \DatamodulVarType::TYPE_BOOL:
        if ($datamodul->getVarValue($fieldname) == true) {
          return 1;
        } else {
          return 0;
        }
        
      case \DatamodulVarType::TYPE_BOOL_JN:
        if ($datamodul->getVarValue($fieldname) == true) {
          return "'" . $this->dboperation->getString4Sql('J') . "'";
        } else {
          return "'" . $this->dboperation->getString4Sql('N') . "'";
        }
        
      case \DatamodulVarType::TYPE_BOOL_WF:
        if ($datamodul->getVarValue($fieldname) == true) {
          return "'" . $this->dboperation->getString4Sql('W') . "'";
        } else {
          return "'" . $this->dboperation->getString4Sql('F') . "'";
        }
        
      case \DatamodulVarType::TYPE_BOOL_VN:
        if ($datamodul->getVarValue($fieldname) == true) {
          return "'" . $this->dboperation->getString4Sql('V') . "'";
        } else {
          return "'" . $this->dboperation->getString4Sql('N') . "'";
        }
        
      case \DatamodulVarType::TYPE_BOOL_HALF_DEFAULT_TRUE:
        if ($datamodul->getVarValue($fieldname) == true) {
          return "'" . $this->dboperation->getString4Sql('1') . "'";
        } else {
          return "'" . $this->dboperation->getString4Sql('0.5') . "'";
        }
        
      case \DatamodulVarType::TYPE_WF_EMPTY:
        if ($datamodul->getVarValue($fieldname) === true) {
          return "'" . $this->dboperation->getString4Sql('W') . "'";
        } elseif ($datamodul->getVarValue($fieldname) === false) {
          return "'" . $this->dboperation->getString4Sql('F') . "'";
        } else {
          return '\'\'';
        }
        
      case \DatamodulVarType::TYPE_BINARY:
        $binaryValue = base64_decode($datamodul->getVarValue($fieldname));
        $data = unpack('H*hex', $binaryValue);

        return $this->dboperation->dbparser->hex($data['hex']);
      case \DatamodulVarType::TYPE_BINARY_NO_BASE64:
        $binaryValue = $datamodul->getVarValue($fieldname);
        $data = unpack('H*hex', $binaryValue);

        return $this->dboperation->dbparser->hex($data['hex']);
      case \DatamodulVarType::TYPE_GUID:
        $value = $datamodul->getVarValue($fieldname);
        
        return $this->dboperation->getGuidForSql($value);
    }
  }

  
  public function getDatamodulValue($pVarname, $pNameInResultSet = '')
  {
    $nameInResultSet = ($pNameInResultSet != '' ? $pNameInResultSet : $pVarname);
    static $datamodul = array();
    if (!array_key_exists(static::class, $datamodul)) {
      $datamodul[static::class] = $this->getNewObject();
    }

    return $this->getValue($nameInResultSet, $datamodul[static::class]->getTyp($pVarname));
  }

  public function getValue($fieldname, $vartyp)
  {
    $value = $this->dboperation->fieldbyname($fieldname);
    switch ($vartyp) {
      case \DatamodulVarType::TYPE_BOOL:
        return $value == '1';
      case \DatamodulVarType::TYPE_BOOL_JN:
        return $value == 'J';
      case \DatamodulVarType::TYPE_BOOL_WF:
        return $value == 'W';
      case \DatamodulVarType::TYPE_BOOL_VN:
        return $value == 'V';
      case \DatamodulVarType::TYPE_BOOL_HALF_DEFAULT_TRUE:
        return $value !== '0.5';
      case \DatamodulVarType::TYPE_WF_EMPTY:
        if ($value === 'W') {
          return true;
        } elseif ($value === 'F') {
          return false;
        } else {
          return '';
        }
        
      case \DatamodulVarType::TYPE_DATE:
        return $this->datetimeop->getDateStr($value);
      case \DatamodulVarType::TYPE_DATETIME:
        return $this->datetimeop->getEmptyStringWhenDefaultDateTime($this->datetimeop->getDateTimeStr($value));
      case \DatamodulVarType::TYPE_TIME:
        return $this->datetimeop->getTimeStr($value);
      case \DatamodulVarType::TYPE_FLOAT:
        return $this->mathop->floatToString($value);
      case \DatamodulVarType::TYPE_INT:
        return intval($value);
      case \DatamodulVarType::TYPE_FLOAT_DOT:
        return floatval($value);
      case \DatamodulVarType::TYPE_BINARY:
        return base64_encode($value);
      case \DatamodulVarType::TYPE_BINARY_NO_BASE64:
        return $value;
      case \DatamodulVarType::TYPE_GUID:
        switch ($this->dboperation->dbparser->servertyp) {
          case 'ORACLE':
            return Guid::fromByteString($value ?? '');
          case 'MYSQL':
            return Guid::fromByteString($value);
          default:
            return new Guid($value ?? '');
        }
        
      case \DatamodulVarType::TYPE_STRING:
      default:
        return (string)$this->dboperation->decodeStringFromDb($value);
    }
  }

  public function getIdentifier($idColName = 'id', $guidColName = 'guid'): Identifier
  {
    $id = $this->getValue($idColName, \DatamodulVarType::TYPE_INT);
    $guid = $this->getValue($guidColName, \DatamodulVarType::TYPE_GUID);

    return new Identifier($id, $guid);
  }

  
  public function translateVarName($varname)
  {
    return $varname;
  }

  
  public function canupdate($varname)
  {
    switch ($varname) {
      case 'id': return false;
      default: return true;
    }
  }

  
  public function canupdateDmz($pVarname): bool
  {
    return $this->canupdate($pVarname);
  }

  
  public function saveChanging($tablename, $oldvalue, $newvalue, $id, $fieldname, $sqltype = 'U', $pStatus5 = false): void
  {
    $allgsettings = AllgSettingsAPI::getAllgSettings();

    if ($allgsettings->protocolprio == '0') { 
      return;
    }

    
    if ($this->orgdata) {
      $status = 2;
    } else {
      if ($pStatus5) {
        $status = 5;
      } else {
        $status = 0;
      }
    }

    $user_id = SessionOperations::getUserID();
    if (!is_numeric($user_id)) {
      $user_id = -1;
    }
    
    if (!(in_array($sqltype, array('U', 'A')) && $oldvalue === $newvalue)) {
      $sql = "INSERT INTO web_protocol \n
              (tablename,user_id,identifier, \n
               oldvalue,newvalue,fieldname, \n
               sqltime,\n
               sqltype,
               status) \n
               VALUES ('" . $tablename . "'," . $user_id . ',' . $id . ",
                       '" . stripslashes(strtr($oldvalue, "'", '`')) . "','" . stripslashes(strtr($newvalue, "'", '`')) . "','" . $fieldname . "',
                       " . $this->datetimeop->getFormateDateTime4ServerNow() . ",
                       '" . StringOperations::strtoupper($sqltype) . "',
                       " . $status . ')';
      $this->dboperation->clear();
      $this->dboperation->execute($sql);
      $lastid = $this->dboperation->getLastID('web_protocol');
      $this->webprotidwk->addEntry($lastid);
    }
  }

  
  public function updateChanging($id, $idfield, $tablename, $fieldname, $value): void
  {
    
    
    
    if (!in_array($fieldname . ' = ' . $value, $this->_updateBuffer)) {
      array_push($this->_updateBuffer, $fieldname . ' = ' . $value);
    }
  }

  
  public function executeUpdateBuffer($pId, $pIdField, $pTablename): void
  {
    if (!empty($this->_updateBuffer)) {
      $tablename = $this->dboperation->getString4Sql($pTablename);
      $idfield = $this->dboperation->getString4Sql($pIdField);
      $id = SecurityOperations::filterSQL($pId); 
      $sql = "UPDATE $tablename SET ";
      
      $substr = '';
      foreach ($this->_updateBuffer as $buff) {
        $buffi = trim($buff);
        
        if (strrpos($buffi, '=') !== StringOperations::strlen($buffi) - 1) {
          $substr .= ', ' . $buff;
        }
      }
      if (StringOperations::strlen($substr) === 0) {
        return;
      }
      $sql .= StringOperations::substr($substr, 1);
      $sql .= " WHERE $idfield = $id ;";

      $this->dboperation->clear();
      $outcome = $this->dboperation->execute($sql);
      if (!$outcome) {
        Logger::logg("Fehler beim Ausführen des Statements:\n" . $sql . "\n\n SQL Error:\n" . $this->dboperation->getError(), 356);
      }
      $this->_updateBuffer = array();
    }
  }

  
  public function setActionIDs(): void
  {
    $allgsettings = AllgSettingsAPI::getAllgSettings();

    if ($allgsettings->protocolprio == '0') { 
      return;
    }

    $this->dboperation->clear();
    
    
    $this->dboperation->query('SELECT * FROM web_protocol WHERE id = -1');
    if ($this->dboperation->fieldExists('actionid')) {
      if ($this->webprotidwk->EntryCount() > 0) {
        $tmpids = $this->webprotidwk->getEntriesIDSerialized();
        $sql = 'UPDATE web_protocol SET actionid = ' . $tmpids[0] . '
                 WHERE id IN (' . implode(',', $tmpids)
                 . ') AND (actionid IS NULL OR actionid=0) ';
        $this->dboperation->clear();
        $this->dboperation->execute($sql);
        $this->webprotidwk->clear();
      }
    }
  }

  
  protected function applyPrefixToFieldList(string $fieldlist, string $prefix): string
  {
    $prefixMitPunkt = trim($prefix);
    if (!StringOperations::endsWith($prefixMitPunkt, '.')) {
      $prefixMitPunkt .= '.';
    }

    $fieldsWithPrefix = array();
    $fields = explode(',', $fieldlist);
    foreach ($fields as $field) {
      if (!StringOperations::contains($field, '.')) {
        $fieldsWithPrefix[] = $prefixMitPunkt . trim($field);
      } else {
        
        $fieldsWithPrefix[] = trim($field);
      }
    }

    return implode(', ', $fieldsWithPrefix);
  }

  
  public function insertRecord($pTablename, $pIdField, $pDummyfield, $pGentableexists = false)
  {
    if (!is_array($pDummyfield)) {
      $dummyfield = array($pDummyfield);
    } else {
      $dummyfield = $pDummyfield;
    }
    $dummyString = implode(', ', $dummyfield);
    $dummylen = count($dummyfield);
    $emptyStr = "''";
    $emptyAry = array();
    for ($i = 0;$i < $dummylen; $i++) {
      $emptyAry[] = $emptyStr;
    }
    $emptyAryString = implode(',', $emptyAry);
    $tablename = $this->dboperation->getString4Sql($pTablename);
    if (!$pGentableexists) {
      $this->dboperation->clear();
      $this->dboperation->execute('INSERT INTO ' . $tablename . ' (' . $dummyString
            . ') VALUES (' . $emptyAryString . ')');

      return abs($this->dboperation->getLastID($tablename));
    } else {
      $idfield = $this->dboperation->getString4Sql($pIdField);
      $tmpid = $this->dboperation->getNextIDGen($tablename, $idfield);
      
      $this->dboperation->execute('INSERT INTO ' . $tablename . ' (' . $idfield . ") VALUES ('" . $tmpid . "')");

      return abs($tmpid);
    }
  }

  
  public function updateObject($editobj, $orgobj, $tablename, $idfield, $sqltyp = 'U', $pDontProtocol = false, $pStatus5 = false)
  {
    if (get_class($editobj) == get_class($orgobj)) {
      $fieldcount = $editobj->getVarCount();
      for ($i = 0;$i < $fieldcount;$i++) {
        $varname = $editobj->getVarName($i);
        if ($this->canupdate($varname)) {
          
          
          if (in_array($varname, $this->updateAlways) || $editobj->getVarValue($varname) !== $orgobj->getVarValue($varname)) {
            if (!$pDontProtocol && $editobj->getTyp($varname) !== \DatamodulVarType::TYPE_BINARY_NO_BASE64) {
              $this->saveChanging(
                $tablename,
                $orgobj->getVarValue($varname),
                $editobj->getVarValue($varname),
                $this->getSQLValue($idfield, $orgobj),
                $this->translateVarName($varname),
                $sqltyp,
                $pStatus5
              );
            }

            $this->updateChanging(
              $this->getSQLValue($idfield, $orgobj),
              $this->translateVarName($idfield),
              $tablename,
              $this->translateVarName($varname),
              $this->getSQLValue($varname, $editobj)
            );
          }
        }
      }
      $this->executeUpdateBuffer(
        $this->getSQLValue($idfield, $orgobj),
        $this->translateVarName($idfield),
        $tablename
      );
    }
  }

  
  public function getOrderbyDefault($defaultorderbystr)
  {
    return $this->stringop->getfilledStr($this->orderby, $defaultorderbystr);
  }

  
  protected function getCompleteOrderByDefault($pDefaultField, $pPraefix = '', $pOrderByAsc = true, $pCastToInt = false)
  {
    $names = $this->translateDefaultFields($pDefaultField);
    $objectAttrs = $this->getOrderbyObjects();
    $orderbydefault = new CompleteOrderByDefault($this->dboperation, $names, $pOrderByAsc, $pCastToInt, $pPraefix, $this->getLastOrderBy(), $objectAttrs);

    return $orderbydefault->getCompleteOrderByDefault();
  }

  private function translateDefaultFields($pDefaultField)
  {
    return array_map(array($this, 'translateVarName'), (array)$pDefaultField);
  }

  
  final protected function getOrderbyObjects(): array
  {
    $orderbyattrs = array();

    for ($i = 0; $i < 10; $i++) {
      $index = $i == 0 ? '' : $i + 1;
      $orderbyAttr = $this->{'orderby' . $index};
      if (trim($orderbyAttr) != '') {
        
        if (preg_match('/^(([a-z0-9]+\\.)?[a-z0-9_]+, *)*([a-z0-9]+\\.)?[a-z0-9_]+$/', $orderbyAttr)) {
          $sortFields = \array_values(\array_filter(\StringOperations::explodeAndTrim($orderbyAttr, ',')));
          if (!empty($sortFields)) {
            $first = $sortFields[0];
            $orderbyattrs[] = new OrderBy($this->translateVarNameForOrderBy($first), $this->{'orderbyasc' . $index});
            foreach (\array_slice($sortFields, 1) as $sortField) {
              $orderbyattrs[] = new OrderBy($this->translateVarNameForOrderBy($sortField));
            }
          }
        } else {
          $orderbyattrs[] = new OrderBy($this->translateVarNameForOrderBy($orderbyAttr), $this->{'orderbyasc' . $index});
        }
      }
    }

    return $orderbyattrs;
  }

  
  protected function translateVarNameForOrderBy($varname)
  {
    return $this->translateVarName($varname);
  }

  
  protected function getLastOrderBy(): string
  {
    return '';
  }

  
  public function getInvertAsc($ascstr)
  {
    if ($this->orderbyasc == true) {
      return $ascstr;
    } else {
      $ascstr = StringOperations::strtoupper($ascstr);
      if (($ascstr == 'ASC') or ($ascstr == '')) {
        return 'DESC';
      } else {
        return 'ASC';
      }
    }
  }

  
  public function getInvertEqualchar($equalchar)
  {
    if ($this->orderbyasc == true) {
      return $equalchar;
    } else {
      if ($equalchar == '<') {
        return '>';
      } else {
        return '<';
      }
    }
  }

  
  public function setBuildSeitenindex($pVal): void
  {
    $this->buildseitenindex = $pVal;
  }

  
  public function _buildSeitenIndexAry($pRowcount, $pLimit): void
  {
    $this->fillSearchInfoExtractor($pRowcount);

    if (is_null($this->seitenind_anfary)) {
      $this->seitenind_anfary = array();
    }
    if (is_null($this->seitenind_endary)) {
      $this->seitenind_endary = array();
    }
    
    
    
    
    
    if ($this->buildseitenindex == false) {
      return;
    }

    if ($pLimit == 0) {
      return;
    }

    $seitenanzahl = ceil($pRowcount / $pLimit);

    for ($i = 0;$i < $seitenanzahl;$i++) {
      $oldrownr = $this->dboperation->actualrow;
      
      if ($this->dboperation->selectrow($i * $pLimit)) {
        if (!array_key_exists($i, $this->seitenind_anfary)) {
          $this->seitenind_anfary[$i] = new stdClass();
        }
        $this->seitenind_anfary[$i]->key = $this->dboperation->actualrow;
        
        $this->seitenind_anfary[$i]->value = $this->getDatamodulValue($this->translateVarName('id'));
        $this->dboperation->actualrow = $oldrownr;
      }
      if ($this->dboperation->selectrow(($pLimit - 1) + $i * $pLimit)) {
        if (!array_key_exists($i, $this->seitenind_endary)) {
          $this->seitenind_endary[$i] = new stdClass();
        }
        $this->seitenind_endary[$i]->key = $this->dboperation->actualrow;
        $this->seitenind_endary[$i]->value = $this->getDatamodulValue($this->translateVarName('id'));

        $this->dboperation->actualrow = $oldrownr;
      }
    }  
    
    
    
    
    $oldrownr = $this->dboperation->actualrow;
    if ($this->dboperation->selectrow($pRowcount - 1)) {
      if (!array_key_exists((int)($seitenanzahl - 1), $this->seitenind_endary)) {
        $this->seitenind_endary[$seitenanzahl - 1] = new stdClass();
      }
      $this->seitenind_endary[$seitenanzahl - 1]->key = $this->dboperation->actualrow;
      $this->seitenind_endary[$seitenanzahl - 1]->value = $this->getDatamodulValue($this->translateVarName('id'));

      $this->dboperation->actualrow = $oldrownr;
    }
    
    
    
    SessionOperations::setVarValueSuche('seitenanzahl', $seitenanzahl);
    SessionOperations::setVarValueSuche('seitenindary', array('anf' => $this->seitenind_anfary, 'end' => $this->seitenind_endary));
    SessionOperations::setVarValueSuche('datenanzahl', $pRowcount);
  }

  
  protected function fillSearchInfoExtractor($pRowcount): void
  {
    if (!is_null($this->searchInfoExtractor)) {
      $fields = $this->searchInfoExtractor->getInterestingFields();
      $availableFields = array();
      foreach ($fields as $field) {
        if ($this->dboperation->fieldExists($field)) {
          $availableFields[] = $field;
        }
      }
      $idField = $this->searchInfoExtractor->getIdField();

      for ($i = 0; $i < $pRowcount; $i++) {
        $this->dboperation->selectrow($i);
        foreach ($availableFields as $field) {
          $this->searchInfoExtractor->addInfo($field, $this->getDatamodulValue($field));
        }
        $this->searchInfoExtractor->addId($this->getDatamodulValue($idField));
      }
    }
  }

  
  public function clob($fieldName, $asFieldName = '')
  {
    return $this->dboperation->dbparser->clob($fieldName, $asFieldName);
  }

  
  public function checkTableExists($pTableName, $pRefreshCache = false)
  {
    return $this->dboperation->checkTableExists($pTableName, $pRefreshCache);
  }

  
  public function createInStatement($datenbankfeld, $werteArray)
  {
    if ($datenbankfeld == '') {
      throw new InvalidArgumentException('Das Argument $datenbankfeld darf nicht leer oder null sein.');
    }

    if (!is_array($werteArray)) {
      throw new InvalidArgumentException('Das Argument $werteArray muss ein Array sein.');
    }

    if (empty($werteArray)) {
      throw new InvalidArgumentException('Das Argument $werteArray muss Werte beinhalten.');
    }
    $werteArray = array_values($werteArray);
    $type = gettype($werteArray[0]); 

    $istNumerisch = $this->IsIntegerOrFloatOrDouble($werteArray[0]);

    $anfuehrungsstrich = $istNumerisch ? '' : "'";

    $return = $datenbankfeld . ' IN (';
    $return .= \implode(',', \array_map(function($wert) use ($anfuehrungsstrich, $type) {
      if (gettype($wert) != $type) {
        throw new InvalidArgumentException('Das Argument $werteArray muss Werte beinhalten, welche alle vom gleichen Typ sind. (numerisch oder string)');
      }

      return $anfuehrungsstrich . $wert . $anfuehrungsstrich;
    }, $werteArray));
    $return .= ')';

    return $return;
  }

  
  public function setOrderBySettings($pToolSettings): void
  {
    $this->orderby = $pToolSettings->sortkurse;
    $this->orderbyasc = $pToolSettings->sortkurseasc;
    $this->orderby2 = $pToolSettings->sortkurse2;
    $this->orderbyasc2 = $pToolSettings->sortkurseasc2;
    $this->setOrderAktuellesSemester($pToolSettings);
    $this->setOrderAussenstelleForMitarbeiter($pToolSettings);
  }

  
  public function setOrder($pFieldName, $pValue, $pAsc = true): void
  {
    $statement = 'case ';

    if ($pAsc) {
      $gewichtungUebereinstimmung = '1.0';
      $gewichtungElse = '2.0';
    } else {
      $gewichtungUebereinstimmung = '2.0';
      $gewichtungElse = '1.0';
    }

    
    
    $statement .= 'when ' . $pFieldName . '= \'' . $pValue . '\' then ' . $gewichtungUebereinstimmung . ' ';
    $statement .= 'else ' . $gewichtungElse . ' ';
    $statement .= 'end ';
    for ($i = 9; $i >= 1; $i--) {
      $nachfolger = $i + 1;
      $aktueller = $i === 1 ? '' : $i;
      $this->{'orderby' . $nachfolger} = $this->{'orderby' . $aktueller};
      $this->{'orderbyasc' . $nachfolger} = $this->{'orderbyasc' . $aktueller};
    }

    $this->orderby = $statement;
    $this->orderbyasc = 'asc';
  }

  
  public function setOrderLike($pFieldName, $pValue): void
  {
    $statement = 'case ';

    
    
    $statement .= 'when ' . $pFieldName . ' like \'' . $pValue . '\' then 1.0 ';
    $statement .= 'else 2.0 ';
    $statement .= 'end ';
    for ($i = 9; $i >= 1; $i--) {
      $nachfolger = $i + 1;
      $aktueller = $i === 1 ? '' : $i;
      $this->{'orderby' . $nachfolger} = $this->{'orderby' . $aktueller};
      $this->{'orderbyasc' . $nachfolger} = $this->{'orderbyasc' . $aktueller};
    }

    $this->orderby = $statement;
    $this->orderbyasc = 'asc';
  }

  
  public function setOrderAktuellesSemester($pToolSettings): void
  {
    if (trim($pToolSettings->aktuelles_semester) != '') {
      $this->setOrder('semester', $pToolSettings->aktuelles_semester);
    }
  }

  
  public function setOrderAussenstelleForMitarbeiter($pToolSettings): void
  {
    if (isset($pToolSettings->maAussenstKurseZuerstAnzeigen) && $pToolSettings->maAussenstKurseZuerstAnzeigen) {
      $mitarbeiter = \SessionOperations::getCurrentWebToolsMitarbeiter();
      $this->setOrder('k.aussenst', $mitarbeiter->aussenstelle);
    }
  }

  
  public function setPersonenOrderBySettings($pToolSettings, string $pPerson, string $pDefaultSort = 'name'): void
  {
    $this->orderby = $pToolSettings->{'sort' . $pPerson};
    if ($this->orderby === '') {
      $this->orderby = ($pPerson === 'tn' ? 't.' : '') . $pDefaultSort;
    }
    $this->orderbyasc = $pToolSettings->{'sort' . $pPerson . 'asc'};
    $matches = array();
    $prefix = '';
    if (empty($this->orderby2) && preg_match('/([A-Za-z]+\\.)?vorname/', $this->orderby, $matches)) {
      

      
      
      
      if (!empty($matches[1])) {
        $prefix = $matches[1];
      }
      $this->orderby2 = $prefix . 'name';
      $this->orderbyasc2 = $this->orderbyasc;
    } elseif (empty($this->orderby2) && preg_match('/([A-Za-z]+\\.)?name/', $this->orderby, $matches)) {
      

      
      
      
      if (!empty($matches[1])) {
        $prefix = $matches[1];
      }
      $this->orderby2 = $prefix . 'vorname';
      $this->orderbyasc2 = $this->orderbyasc;
    }
  }

  protected function IsIntegerOrFloatOrDouble($value): bool
  {
    $type = gettype($value); 

    $istNumerisch = false;
    if ($type == 'integer' || $type == 'double' || $type == 'float') {
      $istNumerisch = true;
    }

    return $istNumerisch;
  }

  
  public function fill4Lang($pDataModules, $pLangId): array
  {
    if (!is_array($pDataModules)) {
      if (!$pDataModules instanceof Datamodul) {
        throw new InvalidArgumentExceptionExtended('$pDataModules', $pDataModules, '$pDataModules muss ein Array mit Datamodulen oder zumindest ein Datamodul sein');
      } else {
        $pDataModules = array($pDataModules);
      }
    }
    
    

    $dbwMspStamm = new DBWorkMspStamm($this->dbtyp, true);
    $ids = ArrayOperations::mapAttribut($pDataModules, 'id');

    $langValues = $dbwMspStamm->getByLangId($ids, $pLangId, $this->getTableName());
    if (KW_MULTILANG_NODEFAULT) {
      foreach ($pDataModules as $dm) {
        $currentTranslation = $langValues[$dm->id]; 
        foreach ($this->translationMapping as $nameDb => $nameDm) {
          $dm->{$nameDm} = (isset($currentTranslation[$nameDb]) ? $currentTranslation[$nameDb] : '');
        }
      }
    } else {
      foreach ($pDataModules as $dm) {
        $currentTranslation = $langValues[$dm->id];
        foreach ($this->translationMapping as $feldNameDb => $feldNameDataModul) {
          if (isset($currentTranslation[$feldNameDb])) {
            $dm->{$feldNameDataModul} = $currentTranslation[$feldNameDb];
          }
        }
      }
    }

    return $pDataModules;
  }

  
  public function fill4LangKurse($pDataModules, $pLangId): array
  {
    if (!is_array($pDataModules)) {
      if (!$pDataModules instanceof Datamodul) {
        throw new InvalidArgumentExceptionExtended('$pDataModules', $pDataModules, '$pDataModules muss ein Array mit Datamodulen oder zumindest ein Datamodul sein');
      } else {
        $pDataModules = array($pDataModules);
      }
    }

    
    
    $dbwMspKurse = new DBWorkMspKurse($this->dbtyp, true);

    
    $ids = ArrayOperations::mapAttribut($pDataModules, 'id');

    
    
    $langValues = $dbwMspKurse->getByLangId($ids, $pLangId);
    if (KW_MULTILANG_NODEFAULT) {
      foreach ($pDataModules as $dm) {
        $currentTranslation = $langValues[$dm->id]; 
        foreach ($this->translationMapping as $nameDb => $nameDm) {
          $dm->{$nameDm} = (isset($currentTranslation[$nameDb]) ? $currentTranslation[$nameDb] : '');
        }
      }
    } else {
      foreach ($pDataModules as $dm) {
        $currentTranslation = $langValues[$dm->id];
        foreach ($currentTranslation as $field => $translation) {
          if ($translation !== '' && isset($this->translationMapping[$field])) {
            $nameDm = $this->translationMapping[$field];
            $dm->{$nameDm} = $translation;
          }
        }
      }
    }

    return $pDataModules;
  }

  
  protected function postGetAllRowsHook($pDatamodules, $pRefreshCache = false)
  {
    if (!empty($this->translationMapping) && \SessionOperations::isMehrsprachigkeitAktiv() && !SessionOperations::isKuferTools()) {
      $langId = \SessionOperations::getCurrentLanguageKey();
      
      

      if ($langId !== KUFERWEB_SPRACHE_STANDARD) {
        return $this->fill4Lang($pDatamodules, $langId);
      }
    }

    return $pDatamodules;
  }

  
  protected function fuegeFremdobjektHinzu($datamodules, $fremdschluessel, $attribut, $dbworkFremdobjekt, $funktionsnameAufDbwork, $fremdobjektId)
  {
    $fremdschluesselAry = ArrayOperations::mapAttribut($datamodules, $fremdschluessel);
    
    
    $cleanFremdSchluesselAry = array_filter($fremdschluesselAry, function($id) {return is_numeric($id) || !empty($id);});

    $frembObjekte = call_user_func(array($dbworkFremdobjekt, $funktionsnameAufDbwork), $cleanFremdSchluesselAry);
    $frembObjekteById = ArrayOperations::setAttributAsKey($frembObjekte, $fremdobjektId);

    foreach ($datamodules as $key => $row) {
      if (isset($frembObjekteById[$row->{$fremdschluessel}])) {
        $datamodules[$key]->{$attribut} = $frembObjekteById[$row->{$fremdschluessel}];
      } else {
        $datamodules[$key]->{$attribut} = $dbworkFremdobjekt->getNewObject();
      }
    }

    return $datamodules;
  }

  
  public function getColumnFromRows(string $columnName): array
  {
    return \iterator_to_array($this->getColumnFromRowsGenerator($columnName), false);
  }

  
  public function getColumnFromRowsGenerator(string $columnName)
  {
    $this->dboperation->resetToFirstRow();

    while ($this->dboperation->selectNextRow()) {
      yield $this->getDatamodulValue($columnName);
    }
  }

  public static function getDefaultInstance($pDBTyp = 1, $pNewInstance = false)
  {
    static $instance = array();
    if (!array_key_exists(static::class, $instance)) {
      $instance[static::class] = new static($pDBTyp, $pNewInstance);
    }

    return $instance[static::class];
  }

  
  public function resetOrderBy()
  {
    $this->orderby = '';
    $this->orderbyasc = '';
    $this->orderby2 = '';
    $this->orderbyasc2 = '';
    $this->orderby3 = '';
    $this->orderbyasc3 = '';
    $this->orderby4 = '';
    $this->orderbyasc4 = '';
    $this->orderby5 = '';
    $this->orderbyasc5 = '';
    $this->orderby6 = '';
    $this->orderbyasc6 = '';
    $this->orderby7 = '';
    $this->orderbyasc7 = '';
    $this->orderby8 = '';
    $this->orderbyasc8 = '';
    $this->orderby9 = '';
    $this->orderbyasc9 = '';
    $this->orderby10 = '';
    $this->orderbyasc10 = '';
  }

  public function generateUniqueGuid(string $column = 'guid'): \Kufer\Web\Classes\Addfunctions\Guid
  {
    $builder = $this->getSelectQueryBuilder()
      ->select('guid')
      ->from($this->getTableName())
      ->where(':isGuid:');

    $fnExists = function(\Kufer\Web\Classes\Addfunctions\Guid $guid) use ($column, $builder): bool {
      $this->dboperation->clear();
      $this->dboperation->query($builder
        ->paramId('isGuid', \Kufer\Web\Classes\Identifier\Identifier::fromGuid($guid), 'id', $column)
        ->getSQL());

      return ($this->dboperation->rowcount() == 1);
    };

    $counter = 0;
    do {
      $guid = \Kufer\Web\Classes\Addfunctions\Guid::generate();
    } while ($counter++ < 5 && $fnExists($guid));

    if ($counter == 6) {
      throw new \RuntimeException('Es konnte keine einzigartige GUID für ' . $this->getTableName() . '.' . $column . ' generiert werden!');
    }

    return $guid;
  }
}