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


class DBWorkCategories extends DBWork
{
  protected static $_cache_CategorienNachTyp = array();
  
  protected static $_cache_CategorienNachTypAs = array();
  private static $structuredCategories;
  private static $structuredCategoriesAs;
  protected static $_cache_numrows;
  protected static $_cache_numrowsAs;

  
  public $onlyAussenstelle = '';

  public function __construct($pDBTyp = 1, $pNewInstance = false)
  {
    parent::__construct($pDBTyp, $pNewInstance);
    $this->select = 'id, bezeichnung, vaterid, kategorieTyp, typ, catcolor, ' . $this->clob('metakeywords') . ', ' . $this->clob('metadescription') . ', position, AllgKnr, BuchKnr, '
      . $this->clob('beschreibung');
    $this->translationMapping = array('bezeichnung' => 'text', 'beschreibung' => 'beschreibung');
  }

  public function getAllQuery(): \SelectQueryBuilder
  {
    return $this->getSelectQueryBuilder()
      ->select($this->select)
      ->from($this->getTableName())
      ->paramRaw('table', $this->getTableName())
      ->orderByAsc('position');
  }

  public function getTableName(): string
  {
    return 'kategorien';
  }

  
  public function getSperreWeb($pPraefix = '', $pMitWhere = false)
  {
    return ($pMitWhere ? ' WHERE ' : ' AND ') . $this->getSperreWebBase($pPraefix);
  }

  public function getSperreWebBase($pPraefix = ''): string
  {
    if (KW_INTRANET) {
      return $this->dboperation->dbparser->isNull($pPraefix . 'sperre_intranet', "'W'") . "='F' ";
    } else {
      return $this->dboperation->dbparser->isNull($pPraefix . 'sperreweb', "'F'") . "<>'W' ";
    }
  }

  
  private function getSubCategories($pCategory, $pCategorieList)
  {
    $ary = array();
    for ($i = 0; $i < count($pCategorieList); $i++) {
      if ($pCategory->id == $pCategorieList[$i]->owner) {
        $pCategorieList[$i]->level = $pCategory->level + 1;
        $pCategorieList[$i]->ownerList = $pCategory->ownerList;
        array_push($pCategorieList[$i]->ownerList, $pCategory->id);
        $pCategorieList[$i]->subCategories = $this->getSubCategories($pCategorieList[$i], $pCategorieList);
        if (empty($pCategorieList[$i]->subCategories)) {
          $pCategorieList[$i]->countKurseComplete = $pCategorieList[$i]->countKurse;
        } else {
          $pCategorieList[$i]->countKurseComplete = 0;
          foreach ($pCategorieList[$i]->subCategories as $subCat) {
            $pCategorieList[$i]->countKurseComplete += $subCat->countKurseComplete;
          }
        }
        array_push($ary, $pCategorieList[$i]);
      }
    }

    return $ary;
  }

  
  protected function structureCategories($pCategories)
  {
    $categories = array();
    foreach ($pCategories as $c) {
      if ($c->owner == 0) {
        $c->level = 1;
        $c->subCategories = $this->getSubCategories($c, $pCategories);
        if ($c->countKurseComplete === false) {
          $c->countKurseComplete = 0;
          foreach ($c->subCategories as $subCat) {
            $c->countKurseComplete += $subCat->countKurseComplete;
          }
          $c->countKurseComplete += $c->countKurse;
          
        }
        array_push($categories, $c);
      }
    }

    return $categories;
  }

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

  
  public function getCurrentRow($pRefreshCache = false)
  {
    $tmp = $this->getNewObject();
    $tmp->id = $this->getDatamodulValue('id');
    $tmp->text = $this->getDatamodulValue('bezeichnung');
    $tmp->owner = $this->getDatamodulValue('vaterid');
    $tmp->kategorieTyp = $this->getDatamodulValue('kategorieTyp');
    $tmp->typ = $this->getDatamodulValue('typ');
    $tmp->catcolor = $this->getDatamodulValue('catcolor');
    $tmp->metakeywords = $this->getDatamodulValue('metakeywords');
    $tmp->metadescription = $this->getDatamodulValue('metadescription');
    $tmp->position = $this->getDatamodulValue('position');
    $tmp->beschreibungorig = $this->getDatamodulValue('beschreibung');
    $tmp->beschreibung = str_replace("\r\n", BR, $tmp->beschreibungorig);
    $tmp->AllgKnr = $this->getDatamodulValue('AllgKnr');
    $tmp->BuchKnr = $this->getDatamodulValue('BuchKnr');

    return $tmp;
  }

  
  public function canupdate($pVarName)
  {
    switch ($pVarName) {
      case 'beschreibungorig':
      case 'subCategories':
      case 'level':
      case 'ownerList':
      case 'countKurse':
      case 'countKurseComplete': return false;
      default: return parent::canupdate($pVarName);
    }
  }

  
  public function getCategories()
  {
    $sqlstr = "SELECT $this->select FROM kategorien ";
    $sqlstr .= $this->getSperreWeb('', true);
    $sqlstr .= ' ORDER BY position';
    $this->dboperation->clear();
    $this->dboperation->query($sqlstr);

    return $this->getAllRows();
  }

  
  public function getCountKategorienByType($pKategorieTyp)
  {
    $kategorieTyp = $this->dboperation->getString4Sql($pKategorieTyp);
    $sqlstr = "SELECT count(*) as anzahl FROM kategorien WHERE kategorieTyp = '$kategorieTyp'" . $this->getSperreWeb();
    $this->dboperation->clear();
    $this->dboperation->query($sqlstr);

    $this->dboperation->selectrow(0);

    return $this->getDatamodulValue('anzahl');
  }

  
  public function getCategoriesWithKurs()
  {
    $sqlstr = "SELECT $this->select FROM kategorien ";
    $sqlstr .= 'WHERE id IN (SELECT DISTINCT kategorie_id FROM kurse_kategorien) ' . $this->getSperreWeb();
    $sqlstr .= ' ORDER BY position';
    $this->dboperation->clear();
    $this->dboperation->query($sqlstr);

    return $this->getAllRows();
  }

  
  public function getCategoriesWithKursINetAktiv()
  {
    $toolsettings = ToolSettingsAPI::getToolSettings(4);
    $dbwk = new DBWorkKurseINetAktiv();
    $dbwk->newSearch();
    $dbwk->fromobject->semester = $toolsettings->semlist;
    $sqlstr = "SELECT $this->select FROM kategorien ";
    $sqlstr .= 'WHERE id IN (SELECT DISTINCT kategorie_id FROM kurse_kategorien kk ';
    $sqlstr .= 'RIGHT JOIN kurse k ON kk.knr = k.nr WHERE (1 = 1) ';
    $sqlstr .= $dbwk->getSearchSQL('k.');
    $sqlstr .= ') ' . $this->getSperreWeb();
    $sqlstr .= ' ORDER BY position';
    $this->dboperation->clear();
    $this->dboperation->query($sqlstr);

    return $this->getAllRows();
  }

  
  public function getMasterCategoriesByType($pKategorieTyp, $pRefreshCache = false)
  {
    static $categories = array();
    if (!$pRefreshCache && isset($categories[$pKategorieTyp])) {
      return $categories[$pKategorieTyp];
    }
    $categories[$pKategorieTyp] = $this->getChildCategoriesByType(0, $pKategorieTyp);

    return $categories[$pKategorieTyp];
  }

  
  public function getMasterCategoriesByTypeNotEmpty($pKategorieTyp, $pWhereZusatz = '', $pRefreshCache = false)
  {
    static $categories = array();
    if (!$pRefreshCache && isset($categories[$pKategorieTyp])) {
      return $categories[$pKategorieTyp];
    }
    $categories[$pKategorieTyp] = $this->getChildCategoriesByTypeNotEmpty(0, $pKategorieTyp, $pWhereZusatz);

    return $categories[$pKategorieTyp];
  }

  public function getMasterCategoriesIDsByType($pKategorieTyp)
  {
    $kategorietyp = $this->dboperation->getString4Sql($pKategorieTyp);
    $sqlstr = "SELECT id FROM kategorien WHERE vaterid = 0 AND kategorieTyp = '" . $kategorietyp . "' "
      . $this->getSperreWeb() . " ORDER BY position \n";
    $this->dboperation->clear();
    $this->dboperation->query($sqlstr);
    $ary = array();
    $rows = $this->dboperation->rowcount();
    if ($rows > 0) {
      for ($i = 0; $i < $rows; $i++) {
        $this->dboperation->selectrow($i);
        $tmp = $this->getDatamodulValue('id');
        if (isset($tmp)) {
          array_push($ary, $tmp);
        }
      }
    }

    return $ary;
  }

  
  private function _getCategoriesByTextStructured($pSearchText, $pCategoryList)
  {
    $categories = array();
    foreach ($pCategoryList as $cat) {
      if (StringOperations::strpos($cat->text, $pSearchText) !== false) {
        $categories[] = $cat;
      } else {
        $categories = array_merge($categories, $this->_getCategoriesByTextStructured($pSearchText, $cat->subCategories));
      }
    }

    return $categories;
  }

  
  public function getCategoriesByTextStructured($pSearchText, $pAlsoEmpty = true)
  {
    
    $catList = $this->getCategoriesByTypeStructured(1);
    $catList = array_merge($catList, $this->getCategoriesByTypeStructured(2, $pAlsoEmpty));

    return $this->_getCategoriesByTextStructured($pSearchText, $catList);
  }

  protected function getCategoriesByTypeSimple($pKategorieTyp)
  {
    $kategorietyp = $this->dboperation->getString4Sql($pKategorieTyp);
    $this->dboperation->clear();
    $sqlstr = "SELECT $this->select FROM kategorien WHERE kategorieTyp = '" . $kategorietyp . "' "
      . $this->getSperreWeb() . " ORDER BY position \n";

    $this->dboperation->query($sqlstr);

    return $this->getAllRows();
  }

  
  public function getCategoriesByType($pKategorieTyp, $pRefreshCache = false)
  {
    $kategorietyp = $this->dboperation->getString4Sql($pKategorieTyp);


    
    $catCache = self::$_cache_CategorienNachTyp;
    if ($this->onlyAussenstelle !== '') {
      $catCache = self::$_cache_CategorienNachTypAs;
    }

    if ($pRefreshCache
            || !array_key_exists($kategorietyp, $catCache)) {
      $categories = $this->getCategoriesByTypeSimple($kategorietyp);


      
      if (SessionOperations::isKuferTools() && GetOperations::isDefined('tool_id')) {
        $toolsettings = ToolSettingsAPI::getToolSettings(GetOperations::getVarValue('tool_id'));
      } else {
        
        
        $toolsettings = ToolSettingsAPI::getToolSettings(4);
      }


      $nrCache = self::$_cache_numrows;
      if ($this->onlyAussenstelle !== '') {
        $nrCache = self::$_cache_numrowsAs;
      }
      $numrowsCached = isset($nrCache[$pKategorieTyp]) && !$pRefreshCache;

      if (!$numrowsCached) {
        
        $dbwk = new DBWorkKurseINetAktivFilter();
        $dbwk->newSearch();
        $dbwk->fromobject->semester = $toolsettings->semlist;
        if ($this->onlyAussenstelle != '') {
          $dbwk->fromobject->aussenst = $this->onlyAussenstelle;
        }
        $this->dboperation->clear();

        SessionOperations::applyKursKategorieRestrictions($dbwk->fromobject, $dbwk->toobject);

        $this->dboperation->query('SELECT nr FROM kurse k WHERE (1 = 1) ' . $dbwk->getSearchSQL('k.'));
        $knrs = $this->getAllRowsSingleColumn('nr');
        $this->dboperation->clear();

        $sqlstr = 'SELECT k.id, count(kk.id) AnzahlKurse FROM kategorien k '
          . ' LEFT JOIN kurse_kategorien kk ON k.id = kk.kategorie_id '
          . $this->getSperreWeb('k.', true)
          . ' AND ' . StringOperations::chunkArrayForInList($knrs, 'kk.knr')
          . ' GROUP BY k.id';
        $this->dboperation->query($sqlstr);
        $nrCache[$pKategorieTyp] = $this->dboperation->rowcount();
        $numRows = $nrCache[$pKategorieTyp];
        if ($this->onlyAussenstelle !== '') {
          self::$_cache_numrowsAs = $nrCache;
        } else {
          self::$_cache_numrows = $nrCache;
        }
      } else {
        $numRows = $nrCache[$pKategorieTyp];
      }

      $tmpAry = array();
      for ($i = 0; $i < $numRows; $i++) {
        $this->dboperation->selectrow($i);
        $tmpAry[$this->getDatamodulValue('id')] = $this->getDatamodulValue('AnzahlKurse');
      }

      for ($i = 0; $i < count($categories); $i++) {
        $categories[$i]->countKurse = $tmpAry[$categories[$i]->id];
      }

      $catCache[$kategorietyp] = $categories;
    }
    
    if ($this->onlyAussenstelle === '') {
      self::$_cache_CategorienNachTyp = $catCache;
    } else {
      self::$_cache_CategorienNachTypAs = $catCache;
    }

    return $catCache[$pKategorieTyp];
  }

  
  public function getCategoriesByTypeStructured($pKategorieTyp, $pAlsoEmpty = true, $pRefreshCache = false)
  {
    if (!isset(self::$structuredCategories)) {
      self::$structuredCategories = array();
    }

    if (!isset(self::$structuredCategoriesAs)) {
      self::$structuredCategoriesAs = array();
    }

    $cacheKey = $pKategorieTyp . '_' . ($pAlsoEmpty ? '1' : '0');

    if ($pRefreshCache
            || ($this->onlyAussenstelle === '' && !isset(self::$structuredCategories[$cacheKey]))
            || ($this->onlyAussenstelle !== '' && !isset(self::$structuredCategoriesAs[$cacheKey]))) {
      $categories = $this->getCategoriesByType($pKategorieTyp, true);
      $categories = $this->structureCategories($categories);

      if (!$pAlsoEmpty) {
        $categories = $this->removeEmptyCategory($categories);
      }

      if ($this->onlyAussenstelle === '') {
        self::$structuredCategories[$cacheKey] = $categories;
      } else {
        self::$structuredCategoriesAs[$cacheKey] = $categories;
      }
    }

    if ($this->onlyAussenstelle === '') {
      return self::$structuredCategories[$cacheKey];
    } else {
      return self::$structuredCategoriesAs[$cacheKey];
    }
  }

  
  protected function removeEmptyCategory($pCategories)
  {
    $result = array();
    foreach ($pCategories as $cat) {
      if ($cat->countKurse > 0) {
        $result[] = $cat;
      }
      if (!empty($cat->subCategories)) {
        
        $cat->subCategories = $this->removeEmptyCategory($cat->subCategories);
        if (count($cat->subCategories)) {
          $result[] = $cat;
        }
      }
    }

    return $result;
  }

  
  public function getCategoriesByTypeStructuredAndFlattened($pKategorieTyp, $pAlsoEmpty = true)
  {
    $cats = $this->getCategoriesByTypeStructured($pKategorieTyp, $pAlsoEmpty);
    $result = array();
    foreach ($cats as $cat) {
      $result = array_merge($result, $this->_flattenCategory($cat));
    }

    return $result;
  }

  
  private function _flattenCategory($pCategory)
  {
    if (!$pCategory instanceof Category) {
      throw new Exception('Invalid Argument Parameter is not a Category');
    }
    $result = array();
    $result[] = $pCategory;
    if (!empty($pCategory->subCategories)) {
      foreach ($pCategory->subCategories as $cat) {
        $result = array_merge($result, $this->_flattenCategory($cat));
      }
    }

    return $result;
  }

  
  public function getCategory($pCategory_Id)
  {
    if ($pCategory_Id === '' || is_null($pCategory_Id)) {
      return $this->getNewObject();
    }

    $category_id = $this->dboperation->getInt4Sql($pCategory_Id);
    static $categories = array();

    if (isset($categories[$category_id])) {
      return $categories[$category_id];
    }

    $sqlstr = "SELECT $this->select FROM kategorien WHERE id = $category_id" . $this->getSperreWeb();
    $this->dboperation->clear();
    $this->dboperation->query($sqlstr);
    $tmp = $this->getFirstRow();
    $categories[$category_id] = $tmp;

    return $tmp;
  }

  
  public function getCategoryStructured($pId, $pAlsoEmpty = true)
  {
    $catList = $this->getCategoriesByTypeStructured(1, $pAlsoEmpty);
    $catList = array_merge($catList, $this->getCategoriesByTypeStructured(2, $pAlsoEmpty));

    return $this->getCategoryStructuredFromList($pId, $catList);
  }

  
  public function getCategoryStructuredFromList($pId, $pCatList)
  {
    $catList = $pCatList;
    foreach ($catList as $cat) {
      if ($cat->id == $pId) {
        return $cat;
      } else {
        if (!empty($cat->subCategories)) {
          $res = $this->getCategoryStructuredFromList($pId, $cat->subCategories);
          if ($res !== false) {
            return $res;
          }
        }
      }
    }

    return false;
  }

  
  public function getChildCategoriesQuery($parentId, $typ = 0): \SelectQueryBuilder
  {
    $query = $this->getAllQuery()
      ->and($this->getSperreWebBase());

    if ($typ > 0) {
      $query = $query->and('kategorietyp = :sonderkatTyp:')
        ->paramInt('sonderkatTyp', $typ);
    }

    if (MathOperations::isNumeric($parentId)) {
      $query = $query
        ->and('vaterid = :vaterid:')
        ->paramInt('vaterid', $parentId);
    } else {
      $query = $query
        ->and('vaterid IN (:vaterIdQuery:)')
        ->paramSelectQuery(
          'vaterIdQuery',
          $this->getAllQuery()
            ->select('id')
            ->and('bezeichnung = :parentBez:')
            ->paramString('parentBez', $parentId)
        );
    }

    return $query;
  }

  
  public function getChildCategoriesIds($parentId, $typ = 0)
  {
    $builder = $this->getChildCategoriesQuery($parentId, $typ)
      ->select('id');

    $this->dboperation->clear();
    $this->dboperation->query($builder->getSQL());

    return $this->getAllRowsSingleColumn('id');
  }

  
  public function getChildCategories($pParent_Id = 0, $pTyp = 0)
  {
    if (is_null($pParent_Id)) {
      $pParent_Id = 0;
    }
    $sonderkat = '';
    $typ = $this->dboperation->getInt4Sql($pTyp);
    if ($typ > 0) {
      $sonderkat = ' and kategorietyp=' . $typ . ' ';
    }
    if (MathOperations::isNumeric($pParent_Id)) {
      $parent_id = $this->dboperation->getInt4Sql($pParent_Id);
      $sqlstr = "SELECT $this->select FROM kategorien WHERE vaterid=" . $parent_id . ' '
              . $this->getSperreWeb() . $sonderkat . 'ORDER BY position';
    } else {
      $sqlstr = "SELECT $this->select FROM kategorien WHERE vaterid IN ( SELECT id FROM kategorien where bezeichnung = '$pParent_Id' )"
              . $this->getSperreWeb() . $sonderkat . 'ORDER BY position';
    }
    $this->dboperation->clear();
    $this->dboperation->query($sqlstr);

    return $this->getAllRows();
  }

  
  public function getChildCategoriesByType($pParent_Id = 0, $pType = 0)
  {
    $parent_id = $this->dboperation->getInt4Sql($pParent_Id);
    $type = $this->dboperation->getInt4Sql($pType);
    $sqlstr = "SELECT $this->select FROM kategorien WHERE vaterid=" . $parent_id . ' AND kategorietyp=' . $type . ' '
       . $this->getSperreWeb() . ' ORDER BY position';
    $this->dboperation->clear();
    $this->dboperation->query($sqlstr);

    return $this->getAllRows();
  }

  
  public function getChildCategoriesByTypeNotEmpty($pParent_Id = 0, $pType = 0, $pWhereZusatz = '')
  {
    $parent_id = $this->dboperation->getInt4Sql($pParent_Id);
    $type = $this->dboperation->getInt4Sql($pType);
    $settings = ToolSettingsAPI::getToolSettings(4);
    $justNoAusfall = ($settings->colorkurseausf == '#blind');
    if ($justNoAusfall) {
      $pWhereZusatz .= " AND (kurs.ausfall<>'J' OR kurs.ausfall IS Null) ";
    }
    
    if ($this->dboperation->subselectPossible()) {
      
      
      
      
      $sqlstr = 'SELECT k.id, k.bezeichnung, k.vaterid, k.kategorietyp, '
        . 'k.position, ' . $this->clob('k.beschreibung') . ', k.allgknr, k.buchknr FROM kategorien k '
        . 'WHERE k.vaterid=' . $parent_id . ' AND k.kategorietyp=' . $type
        . ' AND (exists(select kk.kategorie_id from kurse_kategorien kk, kurse kurs '
        . '     WHERE k.id=kk.kategorie_id '
                . ' and kk.knr = kurs.nr ';
      $sqlstr .= ' AND ' . DBWorkKurseBase::getSQLInternetIntranetAktiv('kurs.');

      $sqlstr .= ' AND ' . $this->datetimeop->getGueltigPart('kurs.', 'internetvon', 'internetbis') . ' '
        . $pWhereZusatz
        . ') or exists(select uk.id from kategorien uk where uk.vaterid=k.id  ))'
        . $this->getSperreWeb('k.')
        . ' ORDER BY position';
      $this->dboperation->clear();
      $this->dboperation->query($sqlstr);
      $categories = $this->getAllRows();
    } else {
      
      
      
      
      $sqlstr = 'SELECT DISTINCT k.id, k.bezeichnung, k.vaterid, k.knrvon, k.knrbis, '
      . 'k.kategorietyp, k.position, k.AllgKnr, k.BuchKnr '
      . ' FROM kategorien k '
      . ' left join kurse_kategorien kk on k.id=kk.kategorie_id '
      . 'left join kurse kurs on kk.knr = kurs.nr'
      . ' WHERE ';
      $sqlstr .= DBWorkKurseBase::getSQLInternetIntranetAktiv('kurs.');

      $sqlstr .= ' AND ' . $this->datetimeop->getGueltigPart('kurs.', 'internetvon', 'internetbis')
      . ' and k.vaterid=' . $parent_id . ' AND k.kategorietyp=' . $type
      . $pWhereZusatz
      . $this->getSperreWeb('k.')
      . ' ORDER BY position';

      
      $sqlstr2 = 'SELECT DISTINCT k.id, k.bezeichnung, k.vaterid, k.knrvon, k.knrbis, '
        . 'k.kategorietyp, k.position, k.AllgKnr, k.BuchKnr'
        . ' FROM kategorien k inner join kategorien uk on k.id = uk.vaterid'
        . ' WHERE k.vaterid=' . $parent_id . ' AND k.kategorietyp=' . $type
        . $this->getSperreWeb('k.')
        . ' ORDER BY k.position';

      $this->dboperation->clear();
      $this->dboperation->query($sqlstr);
      $tmp = $this->getAllRows();
      $this->dboperation->clear();
      $this->dboperation->query($sqlstr2);

      $categories = array_merge($tmp, $this->getAllRows());

      
      usort($categories, array('SortFunctions', 'sortByPosition'));
      
      for ($i = 0; $i < count($categories) - 1; $i++) {
        if ($categories[$i]->id == $categories[$i + 1]->id) {
          unset($categories[$i]);
        }
      }
      
      $categories = array_values($categories);

      
      
      
      $sqlstr3 = 'SELECT id, ' . $this->clob('beschreibung') . ' FROM kategorien';
      $this->dboperation->clear();
      $this->dboperation->query($sqlstr3);
      $catDescription = $this->getAllRows();

      
      for ($i = 0; $i < count($categories); $i++) {
        $descFound = false;
        for ($j = 0; $j < count($catDescription) && !$descFound; $j++) {
          if ($categories[$i]->id == $catDescription[$j]->id) {
            $categories[$i]->beschreibung = $catDescription[$j]->beschreibung;
            $descFound = true;
          }
        }
      }
    }

    return $categories;
  }

  
  public function getCategoriesByKurs($pKnr, $pKategorieTyp)
  {
    $knr = $this->dboperation->getString4Sql($pKnr);

    return $this->getCategoriesByKursIds(array($knr), $pKategorieTyp);
  }

  
  public function getCategoriesByKursIds($pKnrAry, $pKategorieTyp)
  {
    if (!is_array($pKnrAry) || empty($pKnrAry)) {
      return array();
    }
    $kursIds = StringOperations::convertArray2InList($pKnrAry);
    $kategorietyp = $this->dboperation->getInt4Sql($pKategorieTyp);
    $select = StringOperations::applyPrefix($this->select, 'k');
    $sqlstr = "SELECT $select\n
               FROM kategorien k LEFT JOIN kurse_kategorien kk ON (k.id=kk.kategorie_id) \n
               WHERE k.kategorietyp= " . $kategorietyp . ' AND kk.knr in (' . $kursIds . ') '
              . $this->getSperreWeb('k.') . ' ORDER BY k.position';
    $this->dboperation->clear();
    $this->dboperation->query($sqlstr);

    return $this->getAllRows();
  }

  

  
  public function getCategoriesAndRootByKursIds($pKnrAry, $pKategorieTyp)
  {
    $categories = $this->getCategoriesByKursIds($pKnrAry, $pKategorieTyp);
    foreach ($categories as $categorie) {
      $parentCategories = $this->getParentCategories($categorie);
      $parentCnt = count($parentCategories);
      for ($i = 0; $i < $parentCnt; $i++) {
        $categorie->ownerList[$i] = $parentCategories[$i]->id;
      }
      
    }

    return $categories;
  }

  

  
  public function getCategoriesByOtherCategory($pKatId, $pKategorieTyp = 1, $pLevel = 2)
  {
    if ($pKatId == null) {
      return array();
    }
    $katId = $this->dboperation->getInt4Sql($pKatId);
    $katTyp = $this->dboperation->getInt4Sql($pKategorieTyp);
    $level = $this->dboperation->getInt4Sql($pLevel);

    
    static $cache = array();
    if (isset($cache[$katId . '_' . $pKategorieTyp])) {
      return $cache[$katId . '_' . $pKategorieTyp];
    }

    $katInfo = $this->getCategoriesByTypeStructuredAndFlattened($pKategorieTyp);

    $sql = 'select distinct(k.id) from kategorien k join kurse_kategorien kk on kk.kategorie_id = k.id '
            . 'where kk.knr in (select kkk.knr from kurse_kategorien kkk where kkk.kategorie_id = ' . $katId . ') and k.kategorietyp = ' . $katTyp;
    $this->dboperation->clear();
    $this->dboperation->query($sql);

    $rowcount = $this->dboperation->rowcount();
    $result = array();
    for ($i = 0; $i < $rowcount; $i++) {
      $this->dboperation->selectrow($i);
      $kat = $this->getCategoryStructuredFromList($this->getDatamodulValue('id'), $katInfo);
      while ($kat->level > $level) {
        $kat = $this->getCategoryStructuredFromList($kat->owner, $katInfo);
      }
      $result[] = $kat;
    }

    $unique = \ArrayOperations::uniqueByKey($result, 'id');

    $cache[$katId . '_' . $pKategorieTyp] = $unique;

    return $cache[$katId . '_' . $pKategorieTyp];
  }

  
  public function getCategoriesById($pIds)
  {
    if (is_int($pIds)) {
      $ids = array($pIds);
    } else {
      $ids = array();
      foreach ($pIds as $id) {
        $idInt = $this->dboperation->getInt4Sql($id);
        if (is_int($idInt)) {
          $ids[] = $idInt;
        }
      }
    }
    if (empty($ids)) {
      return array();
    }
    $sql = 'select ' . $this->select . ' from kategorien k where k.id in (' . StringOperations::convertArray2InList($ids) . ')'
           . ' order by k.position';

    $this->dboperation->clear();
    $this->dboperation->query($sql);

    return $this->getAllRows();
  }

  public function getCategoriesByIdOrderByBezeichnung($pIds)
  {
    if (is_int($pIds)) {
      $ids = array($pIds);
    } else {
      $ids = array();
      foreach ($pIds as $id) {
        $idInt = $this->dboperation->getInt4Sql($id);
        if (is_int($idInt)) {
          $ids[] = $idInt;
        }
      }
    }
    if (empty($ids)) {
      return array();
    }
    $sql = 'select ' . $this->select . ' from kategorien k where k.id in (' . StringOperations::convertArray2InList($ids) . ')'
           . ' order by k.bezeichnung';

    $this->dboperation->clear();
    $this->dboperation->query($sql);

    return $this->getAllRows();
  }

  
  public function getParentCategories($pCat)
  {
    $result = array();
    $result[] = $pCat;
    $cat = $pCat;
    while ($cat->owner > 0) {
      $owner = $this->dboperation->getInt4Sql($cat->owner);
      $sqlstr = 'SELECT ' . $this->stringop->applyPrefix($this->select, 'k')
               . " FROM kategorien k WHERE id=$owner "
              . $this->getSperreWeb('k.');
      $this->dboperation->clear();
      $this->dboperation->query($sqlstr);
      $cat = $this->getFirstRow();
      $result[] = $cat;
    }

    return array_reverse($result);
  }

  
  public function getFirstCategoryForKurs($pKursnr, $pSonderkategorie = false)
  {
    if ($pSonderkategorie) {
      $katTypSql = 'and k.kategorieTyp=2 ';
    } else {
      $katTypSql = '';
    }
    $knr = $this->dboperation->getString4Sql($pKursnr);
    $sql = 'select ' . $this->stringop->applyPrefix($this->select, 'k')
      . ' from kurse_kategorien kk join kategorien k on k.id=kk.kategorie_id '
      . 'where kk.knr = \'' . $knr . '\' ' . $katTypSql . $this->getSperreWeb('k.')
      . ' order by k.kategorieTyp, k.position';
    $this->dboperation->clear();
    $this->dboperation->query($sql);

    return $this->getFirstRow();
  }

  private function getCatIds($pCategories, $pRecursive = true)
  {
    $ids = array();
    if (!is_null($pCategories)) {
      foreach ($pCategories as $cat) {
        $ids[] = $cat->id;
        $ids = array_merge($ids, $this->getCatIds($cat->subCategories));
      }
    }

    return $ids;
  }

  
  public function getKursNrByCategory($pCategories)
  {
    $catIds = $this->getCatIds($pCategories);
    if (!is_null($catIds) && is_array($catIds) && !empty($catIds) && $catIds[0] != '' && $catIds[0] != 0) {
      $catIds = implode(', ', $catIds);
      $sql = 'SELECt knr FROM kurse_kategorien WHERE kategorie_id IN (' . $catIds . ')';
      $this->dboperation->clear();
      $this->dboperation->query($sql);

      return $this->getAllRowsSingleColumn('knr');
    }

    return array();
  }

  
  public function getItemIdSearchSQLCategoryRekursiv($pKatid, $count) 
  {
    $knrs = $this->getItemIdAryCategoryRekursiv($pKatid, $count);

    return "'" . implode("', '", $knrs) . "'";
  }

  public function getItemIdRekursivInStatement($pKatid, $count, $sqlVariable)
  {
    $knrs = $this->getItemIdAryCategoryRekursiv($pKatid, $count);

    return StringOperations::chunkArrayForInList($knrs, $sqlVariable);
  }

  
  public function getChildCategoriesIdsRecursive($pKatId, $maxRecurse = 30, $count = 0): array
  {
    $id = $pKatId;
    if (!\MathOperations::isNumeric($id)) {
      $id = $this->getIdFromBezeichnung($id);
      if (is_null($id)) {
        return array();
      }
    }

    if ($maxRecurse == $count) {
      return array($id);
    }

    $childKats = $this->getChildCategoriesIds($id);
    $childKatsOfChildKats = array();
    foreach ($childKats as $childKatId) {
      array_push($childKatsOfChildKats, ...$this->getChildCategoriesIdsRecursive($childKatId, $maxRecurse, $count + 1));
    }

    return array_unique(array_merge(array($id), $childKatsOfChildKats));
  }

  public function getItemIdAryCategoryRekursiv($pKatid, $count)
  {
    if (MathOperations::isNumeric($pKatid)) {
      $katid = $this->dboperation->getInt4Sql($pKatid);
    } else {
      $katid = $this->dboperation->getString4Sql($pKatid);
    }

    if ($katid === '') {
      return '';
    }

    $sqlstr1 = $this->getSql4ItemsInCategory($katid);
    $this->dboperation->clear();
    $this->dboperation->query($sqlstr1);
    $knrs = array();
    for ($i = 0; $i < $this->dboperation->rowcount(); $i++) {
      $this->dboperation->selectrow($i);
      $knr = $this->getDatamodulValue('nr');
      if ($knr != '') {
        $knrs[] = $knr;
      }
    }

    $cats = $this->getChildCategories($katid);

    for ($i = 0; $i < count($cats) && $count < 30; $i++) {
      $knrsSubCategory = $this->getItemIdAryCategoryRekursiv($cats[$i]->id, $count + 1);
      $knrs = array_merge($knrs, $knrsSubCategory);
    }

    return $knrs;
  }

  public function getIdFromBezeichnung($pKatBez): ?int
  {
    $builder = $this->getAllQuery()
      ->select('id')
      ->and('bezeichnung = :katBez:')
      ->paramString('katBez', $pKatBez);

    $this->dboperation->clear();
    $this->dboperation->query($builder->getSQL());

    if (!$this->dboperation->selectNextRow()) {
      return null;
    } else {
      return $this->getDatamodulValue('id');
    }
  }

  protected function getSql4ItemsInCategory($pKatid)
  {
    $result = 'SELECT distinct k.nr '
            . 'FROM kurse k INNER JOIN kurse_kategorien kk ON (k.nr=kk.knr) ';
    if (MathOperations::isNumeric($pKatid)) {
      $result .= 'WHERE kk.kategorie_id = ' . $pKatid;
    } else {
      $result .= 'INNER JOIN kategorien kat ON kk.kategorie_id = kat.id '
            . "WHERE kat.bezeichnung LIKE '%" . $pKatid . "%'";
    }

    return $result;
  }

  public function findByFilterQuery(\Kufer\Web\Classes\Dbfilter\KategorienSQLFilter $filter): \SelectQueryBuilder
  {
    $cond = $filter->createSql($this->dboperation);

    return $this->getAllQuery()
      ->and($cond);
  }

  
  public function findByFilter(\Kufer\Web\Classes\Dbfilter\KategorienSQLFilter $filter): array
  {
    $builder = $this->findByFilterQuery($filter);

    $this->dboperation->clear();
    $this->dboperation->query($builder->getSQL());

    return $this->getAllRows();
  }

  public function countByFilter(\Kufer\Web\Classes\Dbfilter\KategorienSQLFilter $filter): int
  {
    $builder = $this->findByFilterQuery($filter)
      ->select('COUNT(*) AS anzahl');

    $this->dboperation->clear();
    $this->dboperation->query($builder->getSQLForInnerQuery());

    $this->dboperation->selectrow(0);

    return (int)$this->getValue('anzahl', \DatamodulVarType::TYPE_INT);
  }
}