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



class DBWorkKursorte extends DBWork
{
  protected static $_cache_Kursorte = array();

  
  public function __construct($pDBTyp = 1, $pNewInstance = false)
  {
    parent::__construct($pDBTyp, $pNewInstance);
    $this->select = 'ko.nr, ko.bez, ko.aussenstelle, ko.ort, ko.progheft1, ko.progheft2, ko.progheft3, ko.mktext, '
                     . 'ko.raum_nr, ko.schule, ko.stock, ko.strasse, ko.email, ko.fax, ko.mobiltel, ko.telefon, '
                     . 'ko.doppel, ko.fer_kal, ko.kost_std, ko.kost_tag, ko.platz, ko.qm,'
                     . 'ko.hausmeister1, ko.hausmeister2, ko.hausmeister3, ko.hausmeister4, ko.hausmadrnr,'
                     . 'ko.hinweis, ko.info, ko.mktext_bf, ko.strasse_bf, ko.ort_bf, '
                     . 'ko.art, ko.internet, ko.AnfahrtLink, ko.krit1, ko.krit2, ko.krit3, '
                     . 'ko.krit4, ko.krit5, ko.krit6, ko.bezgrafik, ko.barrierefrei, ko.mietetag, ko.mieteue, '
                     . 'ko.mietepauschal, ko.miethtue, ko.mietabend, ko.kost_mwst, ko.mietbar, '
                     . 'ko.kost_pauschal, ko.kost_htstd, ko.kost_abend, ko.nichtweb, ko.kirchengemeinde, ko.kirchenbezirk, '
                     . 'ko.gemeinde, ko.stadtteil, ko.bezirk, ko.bundesland, ko.openstreetmapmlat, ko.openstreetmapmlon, '
                     . 'ko.raumreservtools';
    $this->translationMapping = array('progheft1' => 'progheft1', 'progheft2' => 'progheft2', 'progheft3' => 'progheft3', 'mktext' => 'mktext');
  }

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

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

  
  public function getCurrentRow($pRefreshCache = false)
  {
    $tmp = $this->getNewObject();
    $tmp->id = $this->getDatamodulValue('nr');
    $tmp->bezeichnung = $this->getDatamodulValue('bez');
    $tmp->aussenstelle = $this->getDatamodulValue('aussenstelle');
    $tmp->ort = $this->getDatamodulValue('ort');
    $tmp->progheft1 = $this->getDatamodulValue('progheft1');
    $tmp->progheft2 = $this->getDatamodulValue('progheft2');
    $tmp->progheft3 = $this->getDatamodulValue('progheft3');
    $tmp->mktext = $this->getDatamodulValue('mktext');
    $tmp->raumnr = $this->getDatamodulValue('raum_nr');
    $tmp->schule = $this->getDatamodulValue('schule');
    $tmp->stock = $this->getDatamodulValue('stock');
    $tmp->strasse = $this->getDatamodulValue('strasse');
    $tmp->email = $this->getDatamodulValue('email');
    $tmp->fax = $this->getDatamodulValue('fax');
    $tmp->mobiltel = $this->getDatamodulValue('mobiltel');
    $tmp->telefon = $this->getDatamodulValue('telefon');
    $tmp->doppel
            = ($this->getDatamodulValue('doppel') == 'W' ? 'Ja' : 'Nein');
    $tmp->ferienkalender = $this->getDatamodulValue('fer_kal');
    $tmp->kost_std = $this->getDatamodulValue('kost_std');
    $tmp->kost_tag = $this->getDatamodulValue('kost_tag');
    $tmp->platz = $this->getDatamodulValue('platz');
    $tmp->qm = $this->getDatamodulValue('qm');
    $tmp->hausmeister1 = $this->getDatamodulValue('hausmeister1');
    $tmp->hausmeister2 = $this->getDatamodulValue('hausmeister2');
    $tmp->hausmeister3 = $this->getDatamodulValue('hausmeister3');
    $tmp->hausmeister4 = $this->getDatamodulValue('hausmeister4');
    $tmp->hinweis = $this->getDatamodulValue('hinweis');
    $tmp->info = $this->getDatamodulValue('info');
    $tmp->hausmadrnr = $this->getDatamodulValue('hausmAdrNr');
    $tmp->art = $this->getDatamodulValue('art');
    $tmp->internet = $this->getDatamodulValue('internet');
    $tmp->mktext_bf = $this->getDatamodulValue('mktext_bf');
    $tmp->strasse_bf = $this->getDatamodulValue('strasse_bf');
    $tmp->ort_bf = $this->getDatamodulValue('ort_bf');
    $tmp->anfahrtbeschreibung = $this->getDatamodulValue('AnfahrtBeschreibung');
    $tmp->anfahrtlink = $this->getDatamodulValue('AnfahrtLink');
    $tmp->krit1 = $this->getDatamodulValue('krit1');
    $tmp->krit2 = $this->getDatamodulValue('krit2');
    $tmp->krit3 = $this->getDatamodulValue('krit3');
    $tmp->krit4 = $this->getDatamodulValue('krit4');
    $tmp->krit5 = $this->getDatamodulValue('krit5');
    $tmp->krit6 = $this->getDatamodulValue('krit6');
    $tmp->bezgrafik = $this->getDatamodulValue('bezgrafik');
    $tmp->barrierefrei = $this->getDatamodulValue('barrierefrei');
    $tmp->kost_mwst = $this->getDatamodulValue('kost_mwst');
    $tmp->mieteue = $this->getDatamodulValue('mieteue');
    $tmp->mietetag = $this->getDatamodulValue('mietetag');
    $tmp->miethtue = $this->getDatamodulValue('miethtue');
    $tmp->kost_pauschal = $this->getDatamodulValue('kost_pauschal');
    $tmp->mietepauschal = $this->getDatamodulValue('mietepauschal');
    $tmp->mietabend = $this->getDatamodulValue('mietabend');
    $tmp->mietbar = $this->getDatamodulValue('mietbar');
    $tmp->kost_pauschal = $this->getDatamodulValue('kost_pauschal');
    $tmp->kost_htstd = $this->getDatamodulValue('kost_htstd');
    $tmp->kost_abend = $this->getDatamodulValue('kost_abend');
    $tmp->nichtweb = $this->getDatamodulValue('nichtweb');
    $tmp->kirchengemeinde = $this->getDatamodulValue('kirchengemeinde');
    $tmp->kirchenbezirk = $this->getDatamodulValue('kirchenbezirk');
    $tmp->gemeinde = $this->getDatamodulValue('gemeinde');
    $tmp->stadtteil = $this->getDatamodulValue('stadtteil');
    $tmp->bezirk = $this->getDatamodulValue('bezirk');
    $tmp->bundesland = $this->getDatamodulValue('bundesland');
    $tmp->openstreetmapmlat = $this->getDatamodulValue('openstreetmapmlat');
    $tmp->openstreetmapmlon = $this->getDatamodulValue('openstreetmapmlon');
    $tmp->raumreservtools = $this->getDatamodulValue('raumreservtools');

    return $tmp;
  }

  
  public function canupdate($varname)
  {
    switch ($varname) {
      default:
        return parent::canupdate($varname);
      case 'doppel':
      case 'schule_suche_exakt':
        
      case 'smsgrafik':
        return false;
    }
  }

  
  public function getSQLValue($fieldname, $datamodul)
  {
    if ($fieldname == 'nr') {
      return $datamodul->id;
    }

    return parent::getSQLValue($fieldname, $datamodul);
  }

  
  public function translateVarName($varname)
  {
    
    switch ($varname) {
      default: 
        return parent::translateVarName($varname);
      case 'id': return 'nr';
      case 'bezeichnung': return 'bez';
      case 'ferienkalender': return 'fer_kal';
    }
  }

  
  public function getSearchSQL($pPraeffix)
  {
    $praeffix = $this->dboperation->getString4Sql($pPraeffix);
    $sql = '';

    if ((isset($this->fromobject)) and (isset($this->toobject))) {
      if (isset($this->fromobject->schule) && !empty($this->fromobject->schule)) {
        if (is_array($this->fromobject->schule)) {
          $sql .= ' AND ( (1=0)';
          foreach ($this->fromobject->schule as $schule) {
            $schule = $this->dboperation->getString4Sql($schule);
            $sql .= ' OR ' . $this->dboperation->dbparser->caseInsensitiveLike($praeffix, 'schule', '%' . $schule . '%');
          }
          $sql .= ' ) ';
        } else {
          $schule = $this->dboperation->getString4Sql($this->fromobject->schule);
          $sql .= ' AND ' . $this->dboperation->dbparser->caseInsensitiveLike($praeffix, 'schule', '%' . $schule . '%');
        }
      }
      if (isset($this->fromobject->schule_exact_ary) && !empty($this->fromobject->schule_exact_ary) && is_array($this->fromobject->schule_exact_ary)) {
        $sql .= ' AND ( (1=0)';
        foreach ($this->fromobject->schule_exact_ary as $schule) {
          $schule = $this->dboperation->getString4Sql($schule);
          if ($schule === '') {
            $sql .= ' OR ' . $this->dboperation->dbparser->isStringEmptyOrNull($praeffix . 'schule');
          } else {
            $sql .= ' OR ' . $praeffix . 'schule = \'' . $schule . '\'';
          }
        }
        $sql .= ' ) ';
      }
      if (isset($this->fromobject->schule_suche_exakt) && $this->fromobject->schule_suche_exakt != '') {
        $schuleSucheExakt = $this->dboperation->getString4Sql($this->fromobject->schule_suche_exakt);
        $sql .= ' AND ' . $this->dboperation->dbparser->caseInsensitiveLike($praeffix, 'schule', $schuleSucheExakt);
      }
      if (isset($this->fromobject->mietbar) && $this->fromobject->mietbar) {
        $sql .= ' AND ' . $this->dboperation->dbparser->isNull($praeffix . 'mietbar', "'F'") . " = 'W'";
      }
      if (isset($this->fromobject->eigenerraum) && $this->fromobject->eigenerraum) {
        $sql .= ' AND ' . $this->dboperation->dbparser->isNull($praeffix . 'eigenerraum', "'F'") . " = 'W'";
      }
      
      if (!SessionOperations::isKuferTools()) {
        $typo3Filter = Typo3Filter::getInstance();
        $aussenstellenIds = $typo3Filter->getFilterAussenstellen();

        if (count($aussenstellenIds) > 0) {
          $aussenstellenIds = array_map(function($id) { return $this->dboperation->getInt4Sql($id); }, $aussenstellenIds);
          $sql .= ' AND ' . $praeffix . 'aussenstelle IN( SELECT a.bezeichn FROM aussenst a WHERE id IN ( ' . implode(', ', $aussenstellenIds) . ' ) )';
        }
      } else { 
        if (isset($this->fromobject->aussenstelle) && $this->fromobject->aussenstelle != '') {
          $aussenstelle = $this->dboperation->getString4Sql($this->fromobject->aussenstelle);
          $sql .= ' AND ' . $praeffix . 'aussenstelle = \'' . $aussenstelle . '\' ';
        }
      }
      if (isset($this->fromobject->progheft1) && $this->fromobject->progheft1 != '') {
        $progheft1 = $this->dboperation->getString4Sql($this->fromobject->progheft1);
        $sql .= ' AND ' . $praeffix . 'progheft1 = \'' . $progheft1 . '\' ';
      }
      if (isset($this->fromobject->kirchengemeinde) && $this->fromobject->kirchengemeinde != '') {
        $kirchengemeinde = $this->dboperation->getString4Sql($this->fromobject->kirchengemeinde);
        $sql .= ' AND ' . $praeffix . 'kirchengemeinde = \'' . $kirchengemeinde . '\' ';
      }
      if (isset($this->fromobject->kirchenbezirk) && $this->fromobject->kirchenbezirk != '') {
        $kirchenbezirk = $this->dboperation->getString4Sql($this->fromobject->kirchenbezirk);
        $sql .= ' AND ' . $praeffix . 'kirchenbezirk = \'' . $kirchenbezirk . '\' ';
      }
      if (isset($this->fromobject->bezirk) && $this->fromobject->bezirk != '') {
        $bezirk = $this->dboperation->getString4Sql($this->fromobject->bezirk);
        $sql .= ' AND ' . $praeffix . 'bezirk like \'' . $bezirk . '%\' ';
      }
      if (isset($this->fromobject->gemeinde) && $this->fromobject->gemeinde != '') {
        $gemeinde = $this->dboperation->getString4Sql($this->fromobject->gemeinde);
        $sql .= ' AND ' . $praeffix . 'gemeinde like \'%' . $gemeinde . '%\' ';
      }
      if (isset($this->fromobject->stadtteil) && $this->fromobject->stadtteil != '') {
        $stadtteil = $this->dboperation->getString4Sql($this->fromobject->stadtteil);
        $sql .= ' AND ' . $praeffix . 'stadtteil like \'%' . $stadtteil . '%\' ';
      }
      if (isset($this->fromobject->bundesland) && $this->fromobject->bundesland != '') {
        $bundesland = $this->dboperation->getString4Sql($this->fromobject->bundesland);
        $sql .= ' AND ' . $praeffix . 'bundesland like \'%' . $bundesland . '%\' ';
      }
      if (isset($this->fromobject->mktext) && $this->fromobject->mktext != '') {
        $mktext = $this->dboperation->getString4Sql($this->fromobject->mktext);
        $sql .= ' AND ' . $praeffix . 'mktext like \'%' . $mktext . '%\' ';
      }
      if (isset($this->fromobject->doppel) && $this->fromobject->doppel != '') {
        if ($this->fromobject->doppel == 'Ja') {
          $sql .= ' AND ' . $praeffix . 'doppel = \'W\' ';
        } else {
          $sql .= ' AND (' . $praeffix . 'doppel = != \'W\' OR ' . $praeffix . 'doppel IS NULL) ';
        }
      }

      if ($this->fromobject->raumreservierungKuferTools ?? false) {
        $sql .= ' AND ' . $this->dboperation->dbparser->isNull($praeffix . 'raumreservtools', '\'F\'') . ' = \'W\'';
      }
    }

    return $sql;
  }

  public function getOnlyIds($dbwFunction, $params)
  {
    $select = $this->select;
    $this->select = 'ko.nr';
    $kurstage = call_user_func_array($dbwFunction, $params);
    $this->select = $select;

    return ArrayOperations::mapAttribut($kurstage, 'id');
  }

  
  public function getKursorte()
  {
    $this->dboperation->clear();
    $this->dboperation->query(
      'SELECT ' . $this->select
      . " FROM kursorte ko\n"
      . ' WHERE 1=1 ' . $this->getSearchSQL('ko.')
      . ' ORDER BY mktext'
    );

    return $this->getAllRows();
  }

  public function getKursortMkTexte()
  {
    $this->dboperation->clear();
    $this->dboperation->query(
      'SELECT DISTINCT ko.mktext '
      . " FROM kursorte ko\n"
      . ' WHERE 1=1 ' . $this->getSearchSQL('ko.')
    );

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

  
  public function getKursOrteByIds($pIdAry)
  {
    if (count($pIdAry) === 0) {
      return array();
    }

    $cleanIds = array_map(array($this->dboperation, 'getString4Sql'), $pIdAry);
    $idStr = implode(', ', $cleanIds);

    $sql = 'SELECT ' . $this->select
           . ' FROM kursorte ko'
           . ' WHERE ko.nr IN(' . $idStr . ')';

    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $kursOrtAry = $this->getAllRows();

    
    $resultAry = array();
    foreach ($pIdAry as $id) {
      foreach ($kursOrtAry as $kursOrt) {
        if ($kursOrt->id == $id) {
          $resultAry[] = $kursOrt;
          break;
        }
      }
    }

    return $resultAry;
  }

  
  public function getKursorteSQLPart($pLogin_id = -1, $pAdmin = -1, $pRestrict = true, $pSelect = '', $pRaumVerfuegbarkeit = false)
  {
    $select = SecurityOperations::filterSQL($pSelect);
    if (empty($select)) {
      $select = $this->select;
    }

    $sql = 'SELECT ' . $select . ' ';
    $sql .= 'FROM kursorte ko ';
    $sql .= 'WHERE 1=1 ' . $this->getSearchSQL('ko.');

    if ($pRestrict) {
      $dbwPrivilegesOrte = new DBWorkWebPrivOrt($this->dbtyp);
      $gebsql = $dbwPrivilegesOrte->getPrivilegEinschraenkungForKursorte('ko.', $pLogin_id, $pAdmin, $pRestrict);

      
      if ($gebsql != '') {
        $sql .= ' and ' . $gebsql . ' ';
      }
    } 

    
    
    if (!isset($pRaumVerfuegbarkeit) || !$pRaumVerfuegbarkeit) {
      

      $sql .= ' and ' . $this->dboperation->dbparser->isNull('ko.NichtVerfuegbar', "'F'") . ' <> \'W\' ';
    }

    return $sql;
  }

  
  public function getKursorteLimit($start_kursort, $limit = 0, $forward = true, $pLogin_id = -1, $pAdmin = -1, $pRestrict = true)
  {
    $sqlstr = $this->getKursorteSQLPart($pLogin_id, $pAdmin, $pRestrict);
    $sqlstr .= $this->getCompleteOrderByDefault('nr', 'ko');
    if ($this->getOrderbyDefault('nr') != 'nr') {
      $sqlstr .= ', ko.nr';
    }
    $this->dboperation->clear();
    $this->dboperation->query($sqlstr);

    
    $helpary = array();
    $rows = $this->dboperation->rowcount();
    if ($rows > 0) {
      for ($i = 0; $i < $rows; $i++) {
        $this->dboperation->selectrow($i);
        $tmp = $this->getDatamodulValue('nr');
        $helpary[] = $tmp;
      }
    }
    if (SessionOperations::isKuferTools()) {
      ToolsAPI::setTotalData($helpary);
    }

    
    
    
    $this->_buildSeitenIndexAry($this->dboperation->rowcount(), $limit);
    if (!empty($start_kursort) && $start_kursort->id != '') {
      return $this->getLimitRows($limit, $forward, 'nr', $start_kursort->id);
    } else {
      return $this->getLimitRows($limit, $forward);
    }
  }

  
  public function getAllKursortIds($pUserId, $pAdmin, $pRestrict = true, $pRaumVerfuegbarkeit = false)
  {
    $sqlstr = $this->getKursorteSQLPart($pUserId, $pAdmin, $pRestrict, 'ko.nr', $pRaumVerfuegbarkeit);
    $sqlstr .= $this->getCompleteOrderByDefault('nr', 'ko');
    if ($this->getOrderbyDefault('nr') != 'nr') {
      $sqlstr .= ', ko.nr';
    }

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

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

  
  public function getRelevanteGebaude($pUserId, $pAdmin, $pRestrict = true, $pRaumVerfuegbarkeit = false)
  {
    
    $sqlstr = $this->getKursorteSQLPart($pUserId, $pAdmin, $pRestrict, 'distinct(ko.schule)', $pRaumVerfuegbarkeit);

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

    $gebNames = array();
    $rowcount = $this->dboperation->rowcount();
    for ($i = 0; $i < $rowcount; ++$i) {
      $this->dboperation->selectrow($i);
      $gebNames[] = $this->getDatamodulValue('schule');
    }

    return $gebNames;
  }

  
  public function getKurseOrteByIds($pIds)
  {
    
    if (!is_array($pIds) || count($pIds) === 0) {
      return array();
    }

    $sql = 'SELECT ' . $this->select . ' FROM kursorte ko WHERE ' . $this->createInStatement('ko.nr', $pIds);
    $sql .= $this->getCompleteOrderByDefault('nr', 'ko');

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

    return $this->getAllRows();
  }

  
  public function getAllKursorte($pLogin_id = -1, $pAdmin = -1, $pRestrict = true, $pRaumVerfuegbarkeit = false, $orderby = 'mktext')
  {
    $sqlstr = $this->getKursorteSQLPart($pLogin_id, $pAdmin, $pRestrict, 'ko.nr, ko.bez, ko.mktext, ko.aussenstelle', $pRaumVerfuegbarkeit);

    $sqlstr .= ' ORDER BY ' . $orderby;
    $this->dboperation->clear();
    $this->dboperation->query($sqlstr);

    return $this->getAllRows();
  }

  
  public function getKursort($pOrt_Id, $pRefreshCache = false)
  {
    if ($pOrt_Id === '') {
      return $this->getNewObject();
    }

    $ort_id = $this->dboperation->getInt4Sql($pOrt_Id);

    if ($pRefreshCache || !array_key_exists($ort_id, self::$_cache_Kursorte)) {
      $this->dboperation->clear();
      $this->dboperation->query('SELECT ' . $this->select
                      . " FROM kursorte ko WHERE ko.nr='" . $ort_id . "'");
      self::$_cache_Kursorte[$ort_id] = $this->getFirstRow();
    }

    return self::$_cache_Kursorte[$ort_id];
  }

  
  public function getKursortByBezeichnung($ort, $pRefreshCache = false)
  {
    static $cache = array();
    if (!isset($cache[$ort]) || $pRefreshCache) {
      $builder = $this->getSelectQueryBuilder();
      $builder = $builder->select($this->select)
                         ->from('kursorte', 'ko')
                         ->where('ko.bez = :ort:')
                         ->paramString('ort', $ort);
      $this->dboperation->clear();
      $this->dboperation->query($builder->getSQL());
      $cache[$ort] = $this->getFirstRow(true);
    }

    return $cache[$ort];
  }

  
  public function getKursortbymktext($ort, $pClearCache = false)
  {
    $ort = $this->dboperation->getString4Sql($ort);
    static $cache = array();
    if (!$pClearCache && isset($cache[$ort])) {
      return $cache[$ort];
    }

    $this->dboperation->clear();
    $this->dboperation->query('SELECT ' . $this->select . " FROM kursorte ko WHERE ko.mktext='"
        . $ort . "'");
    $cache[$ort] = $this->getFirstRow();

    return $cache[$ort];
  }

  
  public function getKursorteByMktext($ortAry)
  {
    $cleanOrtAry = $this->dboperation->getStrings4Sql($ortAry);
    $ortAbfrage = "'" . implode("', '", $cleanOrtAry) . "'";

    $this->dboperation->clear();
    $this->dboperation->query('SELECT ' . $this->select . ' FROM kursorte ko WHERE ko.mktext in (' . $ortAbfrage . ')');

    return $this->getAllRows();
  }

  
  public function getKursorteByMktextary($ortAry, $refreshCache = false)
  {
    if (count($ortAry) == 0) {
      return array();
    }

    $separator = '#';
    static $cache = array();
    $cachekey = implode($separator, $ortAry);
    if (!isset($cache[$cachekey]) || $refreshCache) {
      $cleanOrtAry = $this->dboperation->getStrings4Sql($ortAry);
      $orteSql = "'" . implode("', '", $cleanOrtAry) . "'";

      $this->dboperation->clear();
      $this->dboperation->query('SELECT ' . $this->select . ' FROM kursorte ko WHERE ko.mktext in(' . $orteSql . ')');
      $cache[$cachekey] = $this->getAllRows();
    }

    return $cache[$cachekey];
  }

  
  public function getKursortebyGebaeude($pOrtId, $pClearCache = false)
  {
    $ortId = $this->dboperation->getInt4Sql($pOrtId);

    static $cache = array();
    if (!$pClearCache && isset($cache[$ortId])) {
      return $cache[$ortId];
    }

    $schule = $this->getGebaeudenamebyId($ortId);

    $this->dboperation->clear();
    $sql = 'SELECT ' . $this->select . " \n
            FROM kursorte ko \n
            WHERE ko.schule='" . $schule . "'";
    $sql .= $this->getCompleteOrderByDefault('mktext', 'ko');
    $this->dboperation->query($sql);
    $cache[$ortId] = $this->getAllRows();

    return $cache[$ortId];
  }

  
  public function getGebaeudenamebyId($pOrtId)
  {
    $ortId = $this->dboperation->getInt4Sql($pOrtId);
    $geb = new DBWorkGebaeude();
    $result = $geb->getGebaeudebyNr($ortId);

    return $result->name;
  }

  
  public function getKursorteByKurs($pKnr, $pZusammenfassen = false)
  {
    $knr = $this->dboperation->getString4Sql($pKnr);
    if ($knr == '') {
      return array();
    }
    
    $sql = 'SELECT distinct ' . $this->select . ' FROM kursorte ko LEFT JOIN kurstage kt ON '
        . 'ko.mktext=kt.ort WHERE ';
    $knrs = array($knr);
    if (ToolSettingsAPI::getMitUnterKursenSetting()) {
      $this->dboperation->clear();
      $this->dboperation->query(SQLOperations::unterKursFromKursGruppePart($knr));

      $knrsaussubselect = $this->getAllRowsSingleColumn('unterkurs');
      $knrs = !empty($knrsaussubselect) ? array_merge($knrs, $knrsaussubselect) : $knrs;
    }

    $knrsinlist = \StringOperations::convertArray2InList($knrs);
    $where = ' kt.knr IN (' . $knrsinlist . ') AND ' . $this->dboperation->dbparser->isNull('kt.ausf', '\'-\'') . ' <> \'A\' ';

    if ($pZusammenfassen) {
      $koNrs = $this->getOnlyDifferentKoNrs($where);
      if (count($koNrs) > 0) {
        $sql .= 'ko.nr IN ( ' . implode(', ', $koNrs) . ' )';
      } else {
        return array();
      }
    } else {
      $sql .= $where;
    }
    $sql .= $this->getCompleteOrderByDefault('nr', 'ko');
    if ($this->getOrderbyDefault('nr') != 'nr') {
      $sql .= ', ko.nr';
    }
    $this->dboperation->clear();
    $this->dboperation->query($sql);

    return $this->getAllRows();
  }

  private function getOnlyDifferentKoNrs($where)
  {
    $this->dboperation->clear();
    $this->dboperation->query('SELECT min(ko.nr) as nr FROM kursorte ko LEFT JOIN kurstage kt ON ko.mktext=kt.ort WHERE ' . $where . ' GROUP BY ko.strasse, ko.ort');

    $koNrs = array();
    $rowcount = $this->dboperation->rowcount();
    for ($i = 0; $i < $rowcount; $i++) {
      $this->dboperation->selectrow($i);
      $koNrs[] = $this->dboperation->fieldbyname('nr');
    }

    return $koNrs;
  }

  
  public function getSeminarOrte()
  {
    $sql = 'SELECT DISTINCT progheft1 FROM kursorte'
           . ' WHERE NOT progheft1 = \' \' '
           . $this->getCompleteOrderByDefault('progheft1');

    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $kursOrte = $this->getAllRows();

    $result = array();
    foreach ($kursOrte as $kursOrt) {
      $result[] = $kursOrt->progheft1;
    }

    return $result;
  }

  
  public function getAnfahrtBeschreibungByKursort($pKursortNr, $pRefreshCache = false)
  {
    if (is_null($pKursortNr) || $pKursortNr === '') {
      return '';
    }
    static $cache = array();
    if (isset($cache[$pKursortNr]) && !$pRefreshCache) {
      return $cache[$pKursortNr];
    }
    $sql = 'SELECT ' . $this->clob('ko.anfahrtbeschreibung') . ' FROM kursorte ko WHERE ko.nr=' . $pKursortNr;
    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $this->dboperation->selectrow(0);
    $cache[$pKursortNr] = $this->getDatamodulValue('anfahrtbeschreibung');

    return $cache[$pKursortNr];
  }

  
  

  
  public function updateKursort($kursort, $user_id, $pNoProtocol = false)
  {
    $orgkursort = $this->getKursort($kursort->id, true);
    $this->updateObject($kursort, $orgkursort, 'kursorte', 'id', 'U', $pNoProtocol);
    $this->setActionIDs();
  }

  public function getKursorteByKursNrAryQuery($pKnrs): \SelectQueryBuilder
  {
    return $this->getAllQuery()
      ->innerJoin('kurse k', 'k.ort = :table:.mktext')
      ->and('k.nr IN (:knrs:)')
      ->paramStringArray('knrs', $pKnrs)
      ->orderByAsc(':table:.mktext');
  }

  
  public function getKursorteByKursNrAry($pKnrs)
  {
    
    if (!is_array($pKnrs) || count($pKnrs) == 0) {
      return array();
    }

    
    if (count($pKnrs) > 100) {
      return \ArrayOperations::uniqueByKey((new \Kufer\Web\Classes\Stream\ArrayStream($pKnrs))
        ->chunks(100)
        ->map(function($knrs): array {
          return $this->getKursorteByKursNrAry($knrs);
        })
        ->flatten()
        ->toArray(), 'id');
    }

    $builder = $this->getKursorteByKursNrAryQuery($pKnrs);

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

    $allRows = $this->getAllRows();

    return \ArrayOperations::uniqueByKey($allRows, 'id');
  }
  

  public function getKursorteMitKursen()
  {
    $dbworkkurse = new DBWorkKurseINetAktiv();
    $dbworkkurse->newSearch();
    $dbworkkurse->fromobject->semester = $dbworkkurse->objsettings_web->semlist;

    $kursQuery = StringOperations::removeLeading(' AND', $dbworkkurse->getSearchSQL('k.'));

    
    
    
    $query = $this->getSelectQueryBuilder();
    $sql = $query->select('DISTINCT ' . $this->select)
      ->from('kurse k')
      ->leftJoin('kursorte ko', 'k.ort = ko.mktext')
      ->where($kursQuery)
      ->and('(ko.nichtweb = \'\' or ko.nichtweb = \'F\' or ko.nichtweb = null)')
      ->orderBySrcCode($this->getCompleteOrderByDefault('ko.mktext'))
      ->getSQL();
    $this->dboperation->clear();
    $this->dboperation->query($sql);

    return $this->getAllRows();
  }

  public function getAllQuery(): \SelectQueryBuilder
  {
    return $this->getSelectQueryBuilder()
      ->select($this->select)
      ->from('kursorte', 'ko')
      ->paramRaw('table', 'ko')
      ->orderBySrcCode($this->getCompleteOrderByDefault('nr') !== 'nr'
        ? $this->getCompleteOrderByDefault('nr', 'ko') . ', ko.nr' : $this->getCompleteOrderByDefault('nr', 'ko'));
  }

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

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

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

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

    return $this->getAllRows();
  }

  
  public function findIdsByFilter(\Kufer\Web\Classes\Dbfilter\KursorteSQLFilter $filter): array
  {
    $builder = $this->findByFilterQuery($filter)
      ->select(':table:.nr');

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

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

  
  public function countByFilter(\Kufer\Web\Classes\Dbfilter\KursorteSQLFilter $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);
  }

  public function existsByFilter(\Kufer\Web\Classes\Dbfilter\KursorteSQLFilter $filter): bool
  {
    return $this->countByFilter($filter) > 0;
  }
}