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



class DBWorkKurse extends DBWorkKurseBase
{
  protected static $_cache_Texte = array();

  
  public function __construct($pDBTyp = 1, $pNewInstance = false)
  {
    parent::__construct($pDBTyp, $pNewInstance);
  }

  
  public function setOrderByValues($pUrlparam, $pDefaultOrderby = null, $pDefaultOrderbyASC = null)
  {
    $values = array('orderby' => array(), 'orderbyasc' => array());
    $dataFromDefault = $this->getOrderByValuesExtended($pDefaultOrderby, $pDefaultOrderbyASC);
    $kurs = new Kurs();
    
    $isOrderBySet = ($pUrlparam->orderby != '');
    if ($isOrderBySet && (($pUrlparam->orderby == 'nr')
            
            || ($kurs->VarExist($pUrlparam->orderby) && $this->canupdate($pUrlparam->orderby)))) {
      $values['orderby']['orderby'] = $this->translateVarName($pUrlparam->orderby);
      if (DBWorkBASysSettings::isWienUni()) {
        $values['orderby']['orderby2'] = 'beginn_dat';
      }
    } elseif ($isOrderBySet && ($pUrlparam->orderby == 'ptitel')) {
      
      $values['orderby']['orderby'] = $this->dboperation->dbparser->concat(array('k.titel', 'k.kurzbez'));
    } elseif ($isOrderBySet && ($pUrlparam->orderby == 'beginndt')) {
      
      
      
      $values['orderby']['orderby'] = 'beginn_dat';
      $values['orderby']['orderby2'] = $this->dboperation->dbparser->lpad('beginn', 'k', 10, '0');
    } elseif ($isOrderBySet && ($pUrlparam->orderby == 'dozsort')) {
      $values['orderby']['orderby'] = 'dozsort';
    } elseif ($isOrderBySet && StringOperations::startsWith($pUrlparam->orderby, 'prog_')) {
      $field = StringOperations::substr($pUrlparam->orderby, 5);
      $dbwkaa = DBWorkKurszusatz::getDefaultInstance();
      if ($dbwkaa->getNewObject()->VarExist($field) && $dbwkaa->canupdate($field)) {
        $values['orderby']['orderby'] = 'kaa.' . $field;
      }
    } elseif (!is_null($pDefaultOrderby)) {
      
      $values['orderby'] = $dataFromDefault['orderby'];
    } else {
      
      $values['orderby']['orderby'] = $this->objsettings_web->sortkurse;
      if (is_numeric($values['orderby']['orderby'])) {
        $expanded = $this->getOrderByValuesExtended($this->objsettings_web->sortkurse, $this->objsettings_web->sortkurseasc);
        $values['orderby'] = $expanded['orderby'];
      }
      if ($this->objsettings_web->sortkurse != $this->objsettings_web->sortkurse2) {
        $nextCriterion = $this->objsettings_web->sortkurse2;
        if (is_numeric($nextCriterion)) {
          $newCritAry = $this->getOrderByValuesExtended($this->objsettings_web->sortkurse2, $this->objsettings_web->sortkurseasc2);
        } else {
          $newCritAry = array('orderby' => array('orderby' => $nextCriterion));
        }
        $values = $this->addOrderBys($values, $newCritAry);
      }
    }
    
    if ($values['orderby']['orderby'] === 'nr') {
      $sortknrab = $this->objsettings_web->sortknrab;
      if (is_numeric($sortknrab) && $sortknrab > 1) {
        $values['orderby']['orderby'] = $this->dboperation->dbparser->substring('k.nr', $sortknrab, 11 - $sortknrab);
      }
    }

    
    if (isset($pUrlparam->orderbyasc) && $pUrlparam->orderbyasc != '') {
      $values['orderbyasc']['orderbyasc'] = ($pUrlparam->orderbyasc == '1') ? '1' : '';
    } elseif (!is_null($pDefaultOrderbyASC)) {
      $values['orderbyasc'] = $dataFromDefault['orderbyasc'];
    } else {
      if (is_numeric($this->objsettings_web->sortkurse)) {
        $expanded = $this->getOrderByValuesExtended($this->objsettings_web->sortkurse, $this->objsettings_web->sortkurseasc);
        $values['orderbyasc'] = $expanded['orderbyasc'];
      } else {
        $values['orderbyasc']['orderbyasc'] = $this->objsettings_web->sortkurseasc;
      }
      if ($this->objsettings_web->sortkurse != $this->objsettings_web->sortkurse2) {
        $nextCriterion = $this->objsettings_web->sortkurse2;
        if (is_numeric($nextCriterion)) {
          $newCritAry = $this->getOrderByValuesExtended($this->objsettings_web->sortkurse2, $this->objsettings_web->sortkurseasc2);
        } else {
          $newCritAry = array('orderbyasc' => array('orderbyasc' => $this->objsettings_web->sortkurseasc2));
        }
        $values = $this->addOrderBys($values, $newCritAry, true);
      }
    }
    if (count($values['orderby']) != count($values['orderbyasc'])) {
      $orderbyasc = $values['orderbyasc']['orderbyasc'];
      $values['orderbyasc'] = array();
      for ($i = 1; $i <= count($values['orderby']); $i++) {
        $values['orderbyasc']['orderbyasc' . (($i == 1) ? '' : $i)] = $orderbyasc;
      }
    }
    $this->resetOrderby();
    foreach (array_merge($values['orderby'], $values['orderbyasc']) as $k => $v) {
      $this->{$k} = $v;
    }
  }

  private function addOrderBys($pOldValues, $pNewValues, $pAsc = false)
  {
    $name = 'orderby' . ($pAsc ? 'asc' : '');
    foreach ($pNewValues[$name] as $val) {
      $nextIndex = $this->getNextIndex($pOldValues[$name], $pAsc);
      $pOldValues[$name][$nextIndex] = $val;
    }

    return $pOldValues;
  }

  private function getNextIndex($pOldValues, $pAsc = false)
  {
    $name = 'orderby' . ($pAsc ? 'asc' : '');
    if (!isset($pOldValues[$name])) {
      return $name;
    }
    for ($i = 2; $i <= 10; $i++) {
      $currentIndex = $name . $i;
      if (!isset($pOldValues[$currentIndex])) {
        return $name . $i;
      }
    }
  }

  
  public function getOrderByPart()
  {
    $orderByComplete = '';
    $orderByVariables = array('', 2, 3, 4, 5, 6, 7, 8, 9, 10);
    $orderByName = 'orderby';
    $orderByAscName = 'orderbyasc';
    foreach ($orderByVariables as $orderbyvariable) {
      $orderByNameFull = $orderByName . $orderbyvariable;
      $orderByAscNameFull = $orderByAscName . $orderbyvariable;
      $orderByTemp = '';
      if ($this->{$orderByNameFull} != '') {
        $orderByTemp = ' ' . $this->{$orderByNameFull};
      }
      if ($orderByTemp != '') {
        if ($this->{$orderByAscNameFull}) {
          $orderByTemp .= ' ASC';
        } else {
          $orderByTemp .= ' DESC';
        }
        if ($orderByComplete != '') {
          $orderByComplete .= ',' . $orderByTemp;
        } else {
          $orderByComplete .= $orderByTemp;
        }
      }
    }
    if ($orderByComplete != '') {
      $orderByComplete = ' ORDER BY' . $orderByComplete;
    }

    return $orderByComplete;
  }

  
  public function setOrderByValuesExtended($pArt, $pAsc)
  {
    $data = $this->getOrderByValuesExtended($pArt, $pAsc, true);
    $this->resetOrderby();
    foreach ($data as $k => $v) {
      $this->{$k} = $v;
    }
  }

  
  public function getOrderByValuesExtended($pArt, $pAsc, $pMerge = false)
  {
    $return = array('orderby' => array(), 'orderbyasc' => array());
    if ($pArt == WB_KURSE_SORTIERUNG_DATUM_BEGINN_KNR) {
      $return['orderby']['orderby'] = 'beginn_dat';
      $return['orderbyasc']['orderbyasc'] = $pAsc;
      $return['orderby']['orderby2'] = $this->dboperation->dbparser->lpad('beginn', 'k', 10, '0');
      $return['orderbyasc']['orderbyasc2'] = $pAsc;
      $return['orderby']['orderby3'] = 'nr';
      $return['orderbyasc']['orderbyasc3'] = $pAsc;
    } elseif ($pArt == WB_KURSE_SORTIERUNG_DATUM_KNR) {
      $return['orderby']['orderby'] = 'beginn_dat';
      $return['orderbyasc']['orderbyasc'] = $pAsc;
      $return['orderby']['orderby2'] = 'nr';
      $return['orderbyasc']['orderbyasc2'] = $pAsc;
    } elseif ($pArt == WB_KURSE_SORTIERUNG_KNR) {
      $return['orderby']['orderby'] = 'nr';
      $return['orderbyasc']['orderbyasc'] = $pAsc;
    } elseif ($pArt == WB_KURSE_SORTIERUNG_KURZBEZ_TITEL_KNR) {
      if (System::isDbMySql()) {
        $return['orderby']['orderby'] = 'concat(kurzbez, titel)';
        $return['orderbyasc']['orderbyasc'] = $pAsc;
      } else {
        $return['orderby']['orderby'] = 'nr';
        $return['orderbyasc']['orderbyasc'] = $pAsc;
      }
    } elseif ($pArt == WB_KURSE_SORTIERUNG_TITEL_KURZBEZ_KNR) {
      if (System::isDbMySql()) {
        $return['orderby']['orderby'] = 'concat(titel, kurzbez)';
        $return['orderbyasc']['orderbyasc'] = $pAsc;
      } else {
        $return['orderby']['orderby'] = 'nr';
        $return['orderbyasc']['orderbyasc'] = $pAsc;
      }
    } elseif ($pArt == WB_KURSE_SORTIERUNG_AS_ERST_BESTIMMTE) {
      if (System::isDbMySql()) {
        $statement = 'case ';
        $statement .= 'when aussenst = \'' . $this->objsettings_web->sortkurseasbestimmte . '\' then 1.0 ';
        $statement .= 'else 2.0 ';
        $statement .= 'end ';
        $return['orderby']['orderby'] = $statement;
        $return['orderbyasc']['orderbyasc'] = '1';
        $return['orderby']['orderby2'] = 'aussenst';
        $return['orderbyasc']['orderbyasc2'] = $pAsc;
      } else {
        $return['orderby']['orderby'] = 'aussenst';
        $return['orderbyasc']['orderbyasc'] = $pAsc;
      }
    } elseif ($pArt == WB_KURSE_SORTIERUNG_AS_ERST_BESTIMMTE_DANN_FREI) {
      if (System::isDbMySql()) {
        $statement = 'case ';
        $statement .= 'when aussenst = \'' . $this->objsettings_web->sortkurseasbestimmte . '\' then 1.0 ';
        $statement .= 'else 2.0 ';
        $statement .= 'end ';
        $return['orderby']['orderby'] = $statement;
        $return['orderbyasc']['orderbyasc'] = '1';
      } else {
        $return['orderby']['orderby'] = 'aussenst';
        $return['orderbyasc']['orderbyasc'] = $pAsc;
      }
    } elseif ($pArt == WB_KURSE_SORTIERUNG_ALTE_HINTEN) {
      if (System::isDbMySql() && is_numeric($this->objsettings_web->sortkurseAelterAlsXTageHinten)) {
        $relevantDate = DateTimeOperations::addDays(DateTimeOperations::getDateNow(), (-1) * $this->objsettings_web->sortkurseAelterAlsXTageHinten);
        $statement = 'case ';
        $statement .= 'when beginn_dat >= ' . $this->datetimeop->getFormatDate4Server($relevantDate) . ' then 1.0 ';
        $statement .= 'else 2.0 ';
        $statement .= 'end ';
        $return['orderby']['orderby'] = $statement;
        $return['orderbyasc']['orderbyasc'] = '1';
        $return['orderby']['orderby2'] = 'beginn_dat';
        $return['orderbyasc']['orderbyasc2'] = $pAsc;
      } else {
        $return['orderby']['orderby'] = 'beginn_dat';
        $return['orderbyasc']['orderbyasc'] = $pAsc;
      }
    } else {
      $return['orderby']['orderby'] = $pArt;
      $return['orderbyasc']['orderbyasc'] = $pAsc;
    }

    if ($pMerge) {
      return array_merge($return['orderby'], $return['orderbyasc']);
    }

    return $return;
  }

  
  public static function getDefaultInstance($pDBTyp = 1, $pNewInstance = false)
  {
    static $instance = null;
    if (!is_null($instance)) {
      return $instance;
    }
    $instance = new DBWorkKurse($pDBTyp, $pNewInstance);

    return $instance;
  }

  public function fillTexteCache($pKursNummern, $pRefreshCache = false)
  {
    if ($pRefreshCache) {
      $knrsNichtImCache = $pKursNummern;
    } else {
      $cache = self::$_cache_Texte;
      $knrsNichtImCache = array_filter($pKursNummern, function($knr) use ($cache) {return !array_key_exists($knr, $cache);});
    }

    if (count($knrsNichtImCache) > 0) {
      $knrList = "'" . implode("', '", $knrsNichtImCache) . "'";
      $this->dboperation->clear();
      $sql
        = 'SELECT k.nr, '
        . $this->clob('k.webinfo') . ', '
        . $this->clob('k.vorspann') . ', '
        . $this->clob('k.nachspann') . ', '
        . $this->clob('k.individtermine') . ', '
        . $this->clob('k.info') . ', '
        . $this->clob('k.keinkursbeginn') . ', '
        . $this->clob('k.termindarstweb') . ', '
        . $this->clob('k.gebuehrergaenztext') . ', '
        . $this->clob('k.keinewebanmeld') . ', '
        . $this->clob('k.intnotiz') . ', '
        . $this->clob('k.abg_bemerk') . ', '
        . $this->clob('k.material') . ', '
        . $this->clob('k.vkz_text') . ', '
        . $this->clob('k.websuchbegriffe');
      if (System::isKuferSql5()) {
        $sql .= ', ' . $this->clob('k.kursinfohtml');
        $sql .= ', ' . $this->clob('k.webinfohtml');
        $sql .= ', ' . $this->clob('k.zeugnishtml');
      }
      $sql .= ' FROM kurse k WHERE k.nr in (' . $knrList . ')';
      $this->dboperation->query($sql);
      $rowcount = $this->dboperation->rowcount();
      for ($i = 0; $i < $rowcount; $i++) {
        $this->dboperation->selectrow($i);
        $knr = $this->getDatamodulValue('nr');
        $tmparray = array();
        $tmparray['webinfo'] = $this->getDatamodulValue('webinfo');
        $tmparray['vorspann'] = $this->getDatamodulValue('vorspann');
        $tmparray['nachspann'] = $this->getDatamodulValue('nachspann');
        $tmparray['individtermine'] = $this->getDatamodulValue('individtermine');
        $tmparray['info'] = $this->getDatamodulValue('info');
        $tmparray['keinkursbeginn'] = $this->getDatamodulValue('keinkursbeginn');
        $tmparray['termindarstweb'] = $this->getDatamodulValue('termindarstweb');
        $tmparray['gebuehrergaenztext'] = $this->getDatamodulValue('gebuehrergaenztext');
        $tmparray['keinewebanmeld'] = $this->getDatamodulValue('keinewebanmeld');
        $tmparray['intnotiz'] = $this->getDatamodulValue('intnotiz');
        $tmparray['abg_bemerk'] = $this->getDatamodulValue('abg_bemerk');
        $tmparray['material'] = $this->getDatamodulValue('material');
        $tmparray['vkz_text'] = $this->getDatamodulValue('vkz_text');
        $tmparray['websuchbegriffe'] = $this->getDatamodulValue('websuchbegriffe');
        $tmparray['kursinfohtml'] = $this->getDatamodulValue('kursinfohtml');
        $tmparray['webinfohtml'] = $this->getDatamodulValue('webinfohtml');
        $tmparray['zeugnishtml'] = $this->getDatamodulValue('zeugnishtml');
        self::$_cache_Texte[$knr] = $tmparray;
      }
    }
  }

  
  public function fillTexts($pKurs)
  {
    
    if (count(self::$_cache_Texte[$pKurs->id]) > 0) {
      $pKurs->webinfo = self::$_cache_Texte[$pKurs->id]['webinfo'];
      $pKurs->vorspann = self::$_cache_Texte[$pKurs->id]['vorspann'];
      $pKurs->nachspann = self::$_cache_Texte[$pKurs->id]['nachspann'];
      $pKurs->individtermine = self::$_cache_Texte[$pKurs->id]['individtermine'];
      $pKurs->info = self::$_cache_Texte[$pKurs->id]['info'];
      $pKurs->keinkursbeginn = self::$_cache_Texte[$pKurs->id]['keinkursbeginn'];
      $pKurs->termindarstweb = self::$_cache_Texte[$pKurs->id]['termindarstweb'];
      $pKurs->gebuehrergaenztext = self::$_cache_Texte[$pKurs->id]['gebuehrergaenztext'];
      $pKurs->keinewebanmeld = self::$_cache_Texte[$pKurs->id]['keinewebanmeld'];
      $pKurs->intnotiz = self::$_cache_Texte[$pKurs->id]['intnotiz'];
      $pKurs->abg_bemerk = self::$_cache_Texte[$pKurs->id]['abg_bemerk'];
      $pKurs->material = self::$_cache_Texte[$pKurs->id]['material'];
      $pKurs->vkz_text = self::$_cache_Texte[$pKurs->id]['vkz_text'];
      $pKurs->websuchbegriffe = self::$_cache_Texte[$pKurs->id]['websuchbegriffe'];
      $pKurs->kursinfohtml = self::$_cache_Texte[$pKurs->id]['kursinfohtml'];
      $pKurs->webinfohtml = self::$_cache_Texte[$pKurs->id]['webinfohtml'];
      $pKurs->zeugnishtml = self::$_cache_Texte[$pKurs->id]['zeugnishtml'];
    }

    return $pKurs;
  }

  
  public function getWebflagsbyKnrs($knrarray)
  {
    $knrs = "''";
    for ($i = 0; $i < count($knrarray); $i++) {
      $knrs .= ", '" . $this->dboperation->getString4Sql($knrarray[$i]) . "'";
    }

    $this->dboperation->clear();
    $this->dboperation->query(
      'SELECT k.webflags FROM kurse k WHERE k.nr IN (' . $knrs . ')'
    );
    $result = \Kurs::WEBFLAG_KEINE + \Kurs::WEBFLAG_NORMAL + \Kurs::WEBFLAG_ELTERNKIND
              + \Kurs::WEBFLAG_FIRMA + \Kurs::WEBFLAG_MEHRFACH + \Kurs::WEBFLAG_PAAR;
    for ($i = 0; $i < $this->dboperation->rowcount(); $i++) {
      $this->dboperation->selectrow($i);
      $tmpflag = $this->getDatamodulValue('webflags');
      if ($tmpflag == 0) {
        $tmpflag = 2;
      }
      $result = $result & $tmpflag;
    }

    return $result;
  }

  public function getTypByKnr($knr)
  {
    $sql = $this->getSelectQueryBuilder()
                ->select($this->select)
                ->from($this->getTableName() . ' k')
                ->where('k.nr = :knr:')
                ->paramString('knr', $knr)
                ->getSQL();
    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $this->dboperation->selectrow(0);

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

  public function getKursTypenInDB()
  {
    $sql = $this->getSelectQueryBuilder()
      ->select('distinct typ')
      ->from($this->getTableName() . ' k')
      ->getSQL();
    $this->dboperation->clear();
    $this->dboperation->query($sql);

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

    return $kurstypen;
  }

  public function getKurstypBezeichnung4Kurstyp($pTyp)
  {
    
    $bezeichnungen = array('' => 'Normalkurs', 'on' => 'Normalkurs', 'E' => 'Einzelveranstaltung', 'X' => 'Ausstellung',
      'T' => 'Termine auf Anfrage', 'B' => 'Beratungstage', 'K' => 'Kinderkurs', 'L' => 'Eltern-Kind-Kurs',
      'V' => 'Bestellung', 'G' => 'Gasttag', 'A' => 'Akademietag', 'M' => 'Musikkurs', 'C' => 'PC-Nutzung');

    return $bezeichnungen[$pTyp];
  }

  public function isFriedrichshafenVeranstaltungsartenGemischt($knrarray)
  {
    $knrs = "''";
    for ($i = 0; $i < count($knrarray); $i++) {
      $knrs .= ", '" . $this->dboperation->getString4Sql($knrarray[$i]) . "'";
    }
    $this->dboperation->clear();
    $this->dboperation->query(
      'SELECT k.kart_key FROM kurse k WHERE k.nr IN (' . $knrs . ')'
    );
    $veranartDabei = false;
    $nichtVeranartDabei = false;
    for ($i = 0; $i < $this->dboperation->rowcount(); $i++) {
      $this->dboperation->selectrow($i);
      $kart_key = $this->getDatamodulValue('kart_key');
      if ($kart_key == '03' || $kart_key == '3') {
        $veranartDabei = true;
      } else {
        $nichtVeranartDabei = true;
      }
    }
    if ($veranartDabei && $nichtVeranartDabei) {
      return true;
    }

    return false;
  }

  public function isFirmenUndNichtFirmenanmeldungbyKnrs($knrarray)
  {
    $knrs = "''";
    for ($i = 0; $i < count($knrarray); $i++) {
      $knrs .= ", '" . $this->dboperation->getString4Sql($knrarray[$i]) . "'";
    }

    $this->dboperation->clear();
    $this->dboperation->query(
      'SELECT k.webflags FROM kurse k WHERE k.nr IN (' . $knrs . ')'
    );
    
    $firmenanmeldungDabei = false;
    $nichtfirmenanmeldungDabei = false;
    for ($i = 0; $i < $this->dboperation->rowcount(); $i++) {
      $this->dboperation->selectrow($i);
      $tmpflag = $this->getDatamodulValue('webflags');
      if ($tmpflag == 0) {
        $nichtfirmenanmeldungDabei = true;
      }
      if (($tmpflag & \Kurs::WEBFLAG_FIRMA) == \Kurs::WEBFLAG_FIRMA) {
        $firmenanmeldungDabei = true;
      } else {
        $nichtfirmenanmeldungDabei = true;
      }
    }
    if ($firmenanmeldungDabei && $nichtfirmenanmeldungDabei) {
      return true;
    }

    return false;
  }

  
  public function getKurseByIDs($ids)
  {
    if (count($ids) == 0) {
      return array();
    }

    
    $cleanIds = array_map(array($this->dboperation, 'getString4Sql'), $ids);
    $whereIDs = '(nr = \'' . implode('\' OR nr = \'', $cleanIds) . '\')';
    $this->dboperation->clear();
    $this->dboperation->query("SELECT $this->select FROM kurse k WHERE $whereIDs");

    return $this->getAllRows();
  }

  
  public function getKursInfosById($pKnrAry)
  {
    if (is_string($pKnrAry)) {
      $pKnrAry = array($pKnrAry);
    }

    return $this->getKurseByIDs($pKnrAry);
  }

  
  public function getKursByELearningId($pEId)
  {
    $eId = $this->dboperation->getInt4Sql($pEId);
    $sql = 'SELECT ' . $this->select . ' FROM kurse k WHERE k.elearningkursid=' . $eId;
    $this->dboperation->clear();
    $this->dboperation->query($sql);

    return $this->getFirstRow();
  }

  
  public function getKurse4DemnaechstMenue(
    $pAnzahl,
    $pDateStart,
    $pDateStop,
    $pSemester = '',
    $pBeginnUhr = '00:00',
    $pRandomSort = false,
    $pZeigeAusgebuchteKurse = false,
    $pVeranartList = '',
    $pNurNichtAusgefallene = false,
    $pFuerAnmeldungGesperrteKurseAusblenden = false,
    $pKeineUnterkurse = false
  ) {
    $stop = $this->datetimeop->getFormatDate4Server($pDateStop);
    $start = $this->datetimeop->getFormatDate4Server($pDateStart);

    $sql = 'SELECT k.nr FROM kurse k '
          . 'LEFT JOIN kurse2 k2 ON k.nr = k2.nr '
          . 'WHERE k.beginn_dat >= ' . $start . ' AND k.beginn_dat <= ' . $stop . ' '
           . 'AND (' . $this->dboperation->dbparser->lpad('beginn', 'k', 10, '0') . ' >= \'' . $this->stringop->lpad($pBeginnUhr, 10, '0')
             . '\' OR k.beginn_dat != ' . $start . ') ';
    if ($pSemester != '') {
      $sql .= ' AND k.semester IN (' . $this->stringop->convertCommaSeparatedList2InList($pSemester) . ') ';
    }
    if (!$pZeigeAusgebuchteKurse) {
      $sql .= $this->getSqlPartKeineAusgebuchtenKurse();
    }
    if ($pVeranartList != '') {
      $sql .= ' and (k.kart_key in (' . StringOperations::convertCommaSeparatedList2InList($pVeranartList) . '))';
    }
    if ($pNurNichtAusgefallene) {
      $sql .= ' and ' . $this->dboperation->dbparser->isNull('k.ausfall', "'N'") . "<>'J' ";
    }
    if ($pFuerAnmeldungGesperrteKurseAusblenden) {
      $sql .= ' and ' . $this->dboperation->dbparser->mod('k.webflags', 2) . ' != 1 ';
    }
    if ($pKeineUnterkurse) {
      $sql .= $this->getSearchSqlKeinUnterkursPart('k.');
    }

    
    
    $sql .= $this->getSearchSQL('k.');

    $sql .= $this->getCompleteOrderByDefault('nr', 'k.', true);

    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $aryAllKurse = $this->getAllRowsSingleColumn('nr');

    $aryKurse = array();

    if ($pAnzahl == -1) {
      $anz = count($aryAllKurse);
    } else {
      $anz = min($pAnzahl, count($aryAllKurse));
    }

    $randIndexAry = array();
    for ($i = 0; $i < $anz; $i++) {
      if ($pRandomSort) {
        do {
          $randIndex = rand(0, count($aryAllKurse) - 1);
        } while (in_array($randIndex, $randIndexAry));

        array_push($randIndexAry, $randIndex);
        array_push($aryKurse, $aryAllKurse[$randIndex]);
      } else {
        array_push($aryKurse, $aryAllKurse[$i]);
      }
    }

    return $aryKurse;
  }

  
  public function getKursIds4RestplatzBoerse(
    $pDateStart,
    $pCountWorkdays,
    $pSemester = '',
    $pZeigeAusgebuchteKurse = false,
    $pVeranartList = '',
    $pNurNichtAusgefallene = false,
    $pFuerAnmeldungGesperrteKurseAusblenden = false,
    $pKeineUnterkurse = false
  ) {
    $beginnDaten = DateTimeOperations::getNextWorkdays($pCountWorkdays, $pDateStart);
    if (count($beginnDaten) == 0) {
      return array();
    }
    $beginnDaten = array_map(function($value) {
      return $this->datetimeop->getFormatDate4Server($value);
    }, $beginnDaten);

    $sql = 'SELECT k.nr FROM kurse k WHERE ';
    $sql .= 'k.beginn_dat in (' . $this->stringop->convertArray2InList($beginnDaten, true) . ')';
    $sql .= ' AND (SELECT count(id) FROM belegung b WHERE b.knr = k.nr AND ' . $this->anmshrink . ') >= k.teil_min ';
    if ($pSemester != '') {
      $sql .= ' AND k.semester in (' . $this->stringop->convertCommaSeparatedList2InList($pSemester) . ') ';
    }
    if (!$pZeigeAusgebuchteKurse) {
      $sql .= 'AND (SELECT count(id) FROM belegung b WHERE b.knr = k.nr AND ' . $this->anmshrink . ') < k.teil_max ';
    }
    if ($pVeranartList != '') {
      $sql .= ' and (k.kart_key in (' . StringOperations::convertCommaSeparatedList2InList($pVeranartList) . '))';
    }
    if ($pNurNichtAusgefallene) {
      $sql .= ' and ' . $this->dboperation->dbparser->isNull('k.ausfall', "'N'") . "<>'J' ";
    }
    if ($pFuerAnmeldungGesperrteKurseAusblenden) {
      $sql .= ' and ' . $this->dboperation->dbparser->mod('k.webflags', 2) . ' != 1 ';
    }
    if ($pKeineUnterkurse) {
      $sql .= $this->getSearchSqlKeinUnterkursPart('k.');
    }
    $sql .= $this->getSearchSQL('k.');

    $sql .= 'ORDER BY beginn_dat, ' . $this->dboperation->dbparser->lpad('beginn', 'k', 10, '0') . ', nr';
    $this->dboperation->clear();
    $this->dboperation->query($sql);

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

  public static $_getKursIdsByCategoryCache = array();

  
  public function getKursIdsByCategory($pKatId = false, $pRefreshCache = false)
  {
    if ($pKatId !== false && !$pRefreshCache && isset(self::$_getKursIdsByCategoryCache[$pKatId])) {
      return self::$_getKursIdsByCategoryCache[$pKatId];
    }

    $searchsql = trim($this->getSearchSQL('k.'));

    $katEinschraenkung = '';
    if ($pKatId !== false) {
      $kat_id = $this->dboperation->getInt4Sql($pKatId);
      $katEinschraenkung = ' kk.kategorie_id = ' . $kat_id;
    } else {
      if (StringOperations::startsWith($searchsql, 'AND')) {
        $searchsql = StringOperations::substr($searchsql, 4);
      }
    } 

    $where = $katEinschraenkung . ' ' . $searchsql;

    $sqlstr = "SELECT k.nr, kk.kategorie_id  \n
               FROM kurse k  \n
               INNER JOIN kurse_kategorien kk ON (k.nr=kk.knr) \n
               WHERE $where \n
               ORDER BY k.beginn_dat";

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

    $data = array();

    $rowcount = $this->dboperation->rowcount();
    for ($i = 0; $i < $rowcount; ++$i) {
      $this->dboperation->selectrow($i);
      $tempKatid = $this->getDatamodulValue('kategorie_id');
      $tempKnr = $this->getDatamodulValue('nr');

      if (!array_key_exists($tempKatid, $data)) {
        $data[$tempKatid] = array();
      }

      $data[$tempKatid][] = $tempKnr;
    }

    if ($pKatId === false) {
      self::$_getKursIdsByCategoryCache = $data;

      return $data;
    } else {
      if (!isset($data[$pKatId])) {
        return null;
      }
      self::$_getKursIdsByCategoryCache[$pKatId] = $data[$pKatId];

      return $data[$pKatId];
    }
  }

  

  
  public function getKursebyCategoryLimit($kat_id, $start_kurs, $limit = 0, $forward = true, $pSetDetailBlaetternToSess = true)
  { 
    
    
    
    
    


    
    
    
    


    
    
    
    


    
    


    
    


    


















    $sqlstr = 'SELECT ' . $this->select . " \n";

    $orderByDoz = StringOperations::strstr($this->orderby, 'dozsort') !== false;
    if ($orderByDoz) { 
      $sqlstr .= $this->dboperation->dbparser->dozOrderBySelect();
    }

    $sqlstr .= "FROM kurse k \n";

    $sqlstr .= ' LEFT OUTER JOIN kurse_aa kaa ON k.nr=kaa.nr ';

    $sqlstr .= 'WHERE ';

    $wherepart = $this->getWhereForGetKurseByCategoryLimit($kat_id);

    $sqlstr .= $wherepart;

    
    
    
    
    
    

    
    
    
    


    $CastToInt = array();
    
    for ($i = 0; $i < 10; $i++) {
      if ($i == 0) {
        $index = '';
      } else {
        $index = $i + 1;
      }
      if (isset($this->objsettings_web->{'proghsortasint' . $index}, $this->{'orderby' . $index})
           && $this->objsettings_web->{'proghsortasint' . $index} == true && $this->{'orderby' . $index} == 'progh_sort') {
        $CastToInt[] = true;
      } else {
        $CastToInt[] = false;
      }
    }
    $praefix = 'k';
    
    if ($orderByDoz) {
      $praefix = '';
    }

    $sqlstr .= $this->getCompleteOrderByDefault('nr', $praefix, true, $CastToInt);

    

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

    $rowcount = $this->dboperation->rowcount();

    
    
    
    
    
    if ($pSetDetailBlaetternToSess) {
      $allKursnr4Detailblaettern = $this->getAllRowsSingleColumn('nr');
      $this->setDetailBlaetternArray($allKursnr4Detailblaettern);
    }


    
    $urlParameterSuche = new URLParameterSuche();
    
    
    if ($urlParameterSuche->suchesetzen === 'true' || $urlParameterSuche->suchesetzen === 'false') {
      StatistikAPI::writeSuchanfrage(StatistikAPI::getParamsSuchanfrage($rowcount));
    }

    
    
    
    $this->_buildSeitenIndexAry($rowcount, $limit);
    if (!empty($start_kurs) && $start_kurs->id != '') {
      $tmpUrlParam = new URLParameterNavigationKursuebersicht();
      $retour = $this->getLimitRows($limit, $forward, 'nr', $start_kurs->id, $tmpUrlParam->inFlag);
    } else {
      $retour = $this->getLimitRows($limit, $forward);
    }

    
    return $retour;
  }

  

  public function getWhereForGetKurseByCategoryLimit($pKatid = -1)
  {
    if ($pKatid === '') {
      $pKatid = -1;
    }

    $wherepart = '';

    $katId = $this->dboperation->getInt4Sql($pKatid);

    if ($katId == -2) {
      $wherepart .= ' k.nr IN ( SELECT kk.knr FROM kurse_kategorien kk join kategorien kat on kat.id = kk.kategorie_id WHERE kat.kategorietyp = 2 ) ';
    } elseif ($katId == -3) {
      $wherepart .= ' k.nr IN ( SELECT kk.knr FROM kurse_kategorien kk join kategorien kat on kat.id = kk.kategorie_id WHERE kat.kategorietyp = 1 ) ';
    } elseif ($katId != -1) {
      $wherepart .= ' k.nr IN ( SELECT kk.knr FROM kurse_kategorien kk WHERE kk.kategorie_id = ' . $katId . ' ) ';
    }

    
    
    
    if ($wherepart == '') {
      
      $wherepart .= StringOperations::substr($this->getSearchSQL('k.'), 4);
    } else {
      
      $wherepart .= $this->getSearchSQL('k.');
    }

    
    if ($this->objsettings_web->unterkurseInDerKurssucheAusblenden) {
      $wherepart = '(' . $wherepart . ')';
      $wherepart .= $this->getSearchSqlKeinUnterkursPart('k.');
    }

    return $wherepart;
  }

  
  public function getKurseByAnmeldeartQuery($anmeldeArtAry = array(), $notAnmeldeArtAry = array()): \SelectQueryBuilder
  {
    $builder = $this->getAllQuery()
      ->and('(1=1) ' . $this->getSearchSQL('k.') . " \n")
      ->orderBySrcCode($this->getCompleteOrderByDefault('nr', 'k'));

    $webflags = array(
      \Kurs::WEBFLAG_KEINE, 
      \Kurs::WEBFLAG_NORMAL, 
      \Kurs::WEBFLAG_ELTERNKIND, 
      \Kurs::WEBFLAG_FIRMA, 
      \Kurs::WEBFLAG_MEHRFACH, 
      \Kurs::WEBFLAG_PAAR 
    );

    if (is_array($anmeldeArtAry) && !empty($anmeldeArtAry)) {
      $anmeldeArtAry = array_filter($anmeldeArtAry, static function($anmeldeArt) use ($webflags): bool {
        return in_array($anmeldeArt, $webflags);
      });

      $builder = $builder
        ->andExpr(function(\SelectQueryBuilder $builder) use ($anmeldeArtAry): \SelectQueryBuilder {
          $builder = $builder->or('(1=0)'); 
          foreach ($anmeldeArtAry as $anmeldeArt) {
            $builder = $builder
              ->or($this->dboperation->dbparser->bitAND('webflags', $anmeldeArt) . ' = ' . $anmeldeArt);
          }

          if (in_array(\Kurs::WEBFLAG_NORMAL, $anmeldeArtAry)) {
            $builder = $builder->or(':table:.webflags = 0');
          }

          return $builder;
        });
    }
    if (is_array($notAnmeldeArtAry) && !empty($notAnmeldeArtAry)) {
      $notAnmeldeArtAry = array_filter($notAnmeldeArtAry, static function($anmeldeArt) use ($webflags): bool {
        return in_array($anmeldeArt, $webflags);
      });

      $builder = $builder
        ->andExpr(function(\SelectQueryBuilder $builder) use ($notAnmeldeArtAry): \SelectQueryBuilder {
          return  $builder
            ->notExpr(function(\SelectQueryBuilder $builder) use ($notAnmeldeArtAry): \SelectQueryBuilder {
              $builder = $builder->or('(1=0)'); 

              foreach ($notAnmeldeArtAry as $anmeldeArt) {
                $builder = $builder
                  ->or($this->dboperation->dbparser->bitAND('webflags', $anmeldeArt) . ' = ' . $anmeldeArt);
              }

              return $builder;
            });
        });
    }

    return $builder;
  }

  
  public function getKurseIdsByAnmeldeArt(array $pAnmeldeArtAry = array(), array $pNotAnmeldeArtAry = array()): array
  {
    $builder = $this->getKurseByAnmeldeartQuery($pAnmeldeArtAry, $pNotAnmeldeArtAry)
      ->select(':table:.nr');

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

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

  
  public function getKurseByAnmeldeArtLimit($pAnmeldeArtAry = array(), $pNotAnmeldeArtAry = array(), $start_kurs = null, $limit = 0, $forward = true)
  {
    $builder = $this->getKurseByAnmeldeartQuery($pAnmeldeArtAry, $pNotAnmeldeArtAry)
      ->select($this->select);

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

    $this->_buildSeitenIndexAry($this->dboperation->rowcount(), $limit);

    if (isset($start_kurs) && $start_kurs !== null && $start_kurs->id != '') {
      $tmpUrlParam = new URLParameterNavigationKursuebersicht();

      return $this->getLimitRows($limit, $forward, 'nr', $start_kurs->id, $tmpUrlParam->inFlag);
    } else {
      return $this->getLimitRows($limit, $forward);
    }
  }

  
  public function getKursIdsbyAnmeldeArt($pAnmeldeArtAry = array(), $pNotAnmeldeArtAry = array(), $pOnlyBookable = false)
  {
    $selectTmp = $this->select;
    $this->select = ' k.nr, k.anm_ende, k.elearningKursID ';
    $kursAry = $this->getKurseByAnmeldeArtLimit($pAnmeldeArtAry, $pNotAnmeldeArtAry, null, 100000);
    $this->select = $selectTmp;
    $teilnehmer = \SessionOperations::getCurrentWebToolsTeilnehmer();

    $knrAry = array();
    
    
    if ($pOnlyBookable) {
      foreach ($kursAry as $kurs) {
        
        
        $gestern = DateTimeOperations::getNextDate(DateTimeOperations::getDateNow(), -1);
        
        $anmende = DateTimeOperations::getDateFromDatetimeStr($kurs->anm_ende);
        if (($anmende == '' || DateTimeOperations::isDateAfter($anmende, $gestern))
            && !ToolsAPI::isPersonWithoutMoodleCredentialsForElearningCourse($teilnehmer, $kurs)
            && ToolsAPI::isTnMoodleRegisterableOrAlreadyRegistered($teilnehmer, $kurs)) {
          
          

          $knrAry[] = $kurs->id;
        }
      } 

      return $knrAry;
    } 
    
    else {
      foreach ($kursAry as $kurs) {
        $knrAry[] = $kurs->id;
      }
    }

    return $knrAry;
  }

  

  
  public function setDetailBlaetternArray($pObjs)
  {
    
    
    $aktobjindary = array();
    
    foreach ($pObjs as $obj) {
      if ($obj instanceof Datamodul) {
        $aktobjindary[] = $obj->id;
      } else {
        $aktobjindary[] = $obj;
      }
    }

    SessionOperations::setVarValueSuche('aktobjindary', $aktobjindary);
  }

  
  public function getDistinctAussenstByKnrs($pKnrs)
  {
    $sql = 'SELECT DISTINCT(aussenst) FROM kurse k WHERE k.nr IN (' . $pKnrs . ') ORDER BY aussenst';
    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $retour = array();
    for ($i = 0; $i < $this->dboperation->rowcount(); $i++) {
      $this->dboperation->selectrow($i);
      $tmp = $this->getDatamodulValue('aussenst');
      if ($tmp != '') {
        array_push($retour, $tmp);
      }
    }

    return $retour;
  }

  
  public function getKurseLastMinuteLimit($pBeginnInnerhalbTagen, $start_kurs, $limit = 0, $forward = true, $pNichtAnmeldbareKurseAusblenden = false)
  {
    $sqlstr = 'SELECT ' . $this->select . " \n";

    $orderByDoz = StringOperations::strstr($this->orderby, 'dozsort') !== false;
    if ($orderByDoz) { 
      $sqlstr .= $this->dboperation->dbparser->dozOrderBySelect();
    }

    $sqlstr .= " FROM kurse k\n";
    $sqlstr .= " LEFT JOIN kurse2 k2 ON k.nr = k2.nr\n";
    $sqlstr .= ' WHERE ';

    $lastMinutePart = $this->getLastMinuteSqlPart($pBeginnInnerhalbTagen);
    $sqlstr .= $lastMinutePart;

    $where = '';
    if ($pNichtAnmeldbareKurseAusblenden) {
      if ($lastMinutePart != '') {
        $where .= ' AND ';
      }
      $where .= $this->dboperation->dbparser->mod('webflags', 2) . ' != 1';
    }

    $sqlstr .= $where . ' ';


    $CastToInt = array();
    
    for ($i = 0; $i < 10; $i++) {
      if ($i == 0) {
        $index = '';
      } else {
        $index = $i + 1;
      }
      if (isset($this->objsettings_web->{'proghsortasint' . $index}) && $this->objsettings_web->{'proghsortasint' . $index} == true && $this->{'orderby' . $index} == 'progh_sort') {
        $CastToInt[] = true;
      } else {
        $CastToInt[] = false;
      }
    }
    $praefix = 'k';
    
    if ($orderByDoz) {
      $praefix = '';
    }
    
    if (is_numeric($this->objsettings_web->sortknrab) && $this->objsettings_web->sortknrab > 1) {
      $praefix = '';
    }
    $sqlstr .= $this->getCompleteOrderByDefault('nr', $praefix, true, $CastToInt);

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

    $this->_buildSeitenIndexAry($this->dboperation->rowcount(), $limit);

    if (!empty($start_kurs) && $start_kurs->id != '') {
      $tmpUrlParam = new URLParameterNavigationKursuebersicht();

      return $this->getLimitRows($limit, $forward, 'nr', $start_kurs->id, $tmpUrlParam->inFlag);
    } else {
      return $this->getLimitRows($limit, $forward);
    }
  }

  
  public function getLastMinuteKurs($pKnr, $pBeginnInnerhalbTagen)
  {
    $sqlstr = "SELECT k.nr \n";
    $sqlstr .= " FROM kurse k\n";
    $sqlstr .= " LEFT JOIN kurse2 k2 ON k.nr = k2.nr\n";
    $sqlstr .= ' WHERE ';

    $sqlstr .= $this->getLastMinuteSqlPart($pBeginnInnerhalbTagen);

    
    $sqlstr .= "\n" . " and k.nr = '" . $pKnr . "'";
    $this->dboperation->clear();
    $this->dboperation->query($sqlstr);

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

  
  public function getLastMinuteSqlPart($pBeginnInnerhalbTagen)
  {
    $where = '';
    
    $this->fromobject->beginn_dat = $this->datetimeop->getDateNow();
    $this->toobject->beginn_dat = $this->datetimeop->getNextDate(
      $this->datetimeop->getDateNow(),
      $pBeginnInnerhalbTagen
    );
    $where .= $this->getSearchSQL('k.'); 

    
    $where .= "\n" . ' and ' . $this->dboperation->dbparser->isNull('k.ausfall', "'N'") . "<>'J' ";
    
    $where .= "\n" . ' and ' . $this->dboperation->dbparser->isNull('k.abschluss', "'N'") . "<>'J' ";
    
    if ($this->dboperation->subselectPossible()) {
      $where .= "\n" . $this->getSqlPartKeineAusgebuchtenKurse();
    }
    $where = trim($where);
    $wherebeginn = StringOperations::strtoupper(StringOperations::substr($where, 0, 4));
    if ($wherebeginn == 'AND ') {
      $where = trim(StringOperations::substr($where, 4));
    }

    return $where;
  }

  
  public function getAnzahlBuchbareKurse($pKnrs)
  {
    if (count($pKnrs) === 0) {
      return 0;
    }
    $knrs = array();
    foreach ($pKnrs as $knr) {
      $knrs[] = $this->dboperation->getString4Sql($knr);
    }
    $sql = 'SELECT count(*) as anzahl FROM kurse k '
            . 'LEFT JOIN kurse2 k2 ON k.nr = k2.nr '
            . 'WHERE k.nr IN (' . $this->stringop->convertArray2InList($knrs) . ') '
            . $this->getSqlPartKeineAusgebuchtenKurse();
    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $this->dboperation->selectrow(0);

    return $this->dboperation->fieldbyname('anzahl');
  }

  
  public function updateTeilAkt($pKnr)
  {
    if (!SessionOperations::isReplikat()
      || (REPLIKATMITONLINEBUCHUNG && $this->dboperation->connectionInfo->db_hybrid && $this->dbtyp == 2)) {
      $knr = $this->dboperation->getString4Sql($pKnr);
      $sql = $this->dboperation->dbparser->angemeldetAnz($pKnr);

      if ($sql != '') {
        if (true) {
          $sql_teilakt = "SELECT teil_akt FROM kurse WHERE nr='$knr'";
          
          $this->dboperation->clear();
          $this->dboperation->query($sql_teilakt);
          $this->dboperation->selectrow(0);
          $anzahlalt = $this->getDatamodulValue('teil_akt');

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

          
          $this->dboperation->clear();
          $this->dboperation->query($sql_teilakt);
          $this->dboperation->selectrow(0);
          $anzahlneu = $this->getDatamodulValue('teil_akt');

          if ($anzahlalt !== $anzahlneu) {
            $this->saveChanging('kurse', $anzahlalt, $anzahlneu, "'$knr'", 'teil_akt', 'U');
            $this->setActionIDs();
          }
        } else {
          
          $this->dboperation->clear();
          $this->dboperation->execute($sql);
        }
      }
    }
  }

  

  
  public function getKalenderInfo($sqlStatement)
  {
    $this->dboperation->clear();
    $this->dboperation->query($sqlStatement);

    $retour = array(array());
    for ($i = 0; $i < $this->dboperation->rowcount(); $i++) {
      $this->dboperation->selectrow($i);
      $tmp = $this->getDatamodulValue('nr');
      $titel = $this->getDatamodulValue('kurzbez');
      $datum = $this->getValue('datum', \DatamodulVarType::TYPE_DATE);
      $day = StringOperations::substr($datum, 0, StringOperations::strpos($datum, '.'));
      if (StringOperations::strlen($day) > 0 && $day[0] == '0') {
        $day = StringOperations::substr($day, 1);
      }
      if ($tmp != '') {
        $retour[$day][] = array($tmp, $titel);
      }
    }

    return $retour;
  }

  public function getKalenderInfoSqlStatement()
  {
    $sql = 'SELECT k.nr, k.beginn_dat, k.kurzbez, k.beginn_dat as datum FROM kurse k ';
    $where = $this->getSearchSQL('k.');
    $where = trim($where);
    $wherebeginn = StringOperations::strtoupper(StringOperations::substr($where, 0, 4));
    if ($wherebeginn == 'AND ') {
      $where = trim(StringOperations::substr($where, 4));
    }

    $sql .= ' WHERE ' . $where;

    return $sql;
  }

  public function getKalenderInfoKurstageSqlStatement($ausgefalleneNichtAnzeigen = true)
  {
    $sql = 'SELECT k.nr, k.beginn_dat, k.kurzbez, CASE WHEN t.datum is NULL THEN k.beginn_dat ELSE t.datum END AS datum FROM kurse k, kurstage t ';
    $where = $this->getSearchSQL('k.');
    $where .= ' AND k.nr = t.knr';

    
    $where .= ' AND ' . $this->getSelectQueryBuilder()
        ->where('datum > \'' . date_create($this->fromobject->kurstagedatum)->format('Y-m-d') . '\'')
        ->getSQLCondition();

    $where .= ' AND ' . $this->getSelectQueryBuilder()
        ->where('datum < \'' . date_create($this->toobject->kurstagedatum)->format('Y-m-d') . '\'')
        ->getSQLCondition();

    
    if ($ausgefalleneNichtAnzeigen) {
      $where .= ' AND ' . $this->getSelectQueryBuilder()
          ->where('t.ausf <> "A"')
          ->getSQLCondition();
    }

    $where = trim($where);
    $wherebeginn = StringOperations::strtoupper(StringOperations::substr($where, 0, 4));
    if ($wherebeginn == 'AND ') {
      $where = trim(StringOperations::substr($where, 4));
    }

    $sql .= ' WHERE ' . $where;

    return $sql;
  }

  
  public function getSonstigeEinnahmenAusgaben($pKnr)
  {
    $knr = $this->dboperation->getString4Sql($pKnr);
    $sql = "SELECT zuseintext, ausg_text, zuseinn, ausg_betr from kurse where nr = '" . $knr . "'";
    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $retour = '';
    
    if ($this->dboperation->rowcount() > 0) {
      $this->dboperation->selectrow(0);
      $retour = new SonstigeEinnahmenAusgaben();
      $retour->einnahmetext = $this->getDatamodulValue('zuseintext');
      $retour->einnahmebetrag = $this->getDatamodulValue('zuseinn');
      $retour->ausgabetext = $this->getDatamodulValue('ausg_text');
      $retour->ausgabebetrag = $this->getDatamodulValue('ausg_betr');
    }

    return $retour;
  }

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

  
  public function isKursgruppe($pKursNr)
  {
    $kursNr = $this->dboperation->getString4Sql($pKursNr);
    $sql = 'select id from kursgruppe where hauptkurs=\'' . $kursNr . '\'';
    $this->dboperation->clear();
    $this->dboperation->query($sql);

    return $this->dboperation->rowcount() > 0;
  }

  
  public function isUnterkurs($pKursNr)
  {
    $kursNr = $this->dboperation->getString4Sql($pKursNr);
    $sql = 'select id from kursgruppe where unterkurs=\'' . $kursNr . '\'';
    $this->dboperation->clear();
    $this->dboperation->query($sql);

    return $this->dboperation->rowcount() > 0;
  }

  public function isHauptkurs($pKnr)
  {
    $sql = 'select id from kursgruppe where hauptkurs=\'' . $pKnr . '\'';
    $this->dboperation->clear();
    $this->dboperation->query($sql);

    return $this->dboperation->rowcount() > 0;
  }

  public function getHauptkurs($pKnr)
  {
    $kursNr = $this->dboperation->getString4Sql($pKnr);
    $sql = 'select ' . $this->select . ' from kurse k where nr in (SELECT hauptkurs FROM kursgruppe WHERE unterkurs=\'' . $kursNr . '\')';
    $this->dboperation->clear();
    $this->dboperation->query($sql);

    return $this->getFirstRow();
  }

  public function getHauptKursNr($pKursNr)
  {
    $kursNr = $this->dboperation->getString4Sql($pKursNr);
    $sql = 'select hauptkurs from kursgruppe where unterkurs=\'' . $kursNr . '\'';
    $this->dboperation->clear();
    $this->dboperation->query($sql);
    if ($this->dboperation->rowcount() > 0) {
      $this->dboperation->selectrow(0);

      return $this->getDatamodulValue('hauptkurs');
    } else {
      return false;
    }
  }

  
  public function getWeitereUnterkurse(string $unterkursKnr): array
  {
    $qbSub1 = $this->getSelectQueryBuilder();
    $subselect1 = $qbSub1->select('hauptkurs')
                         ->from('kursgruppe')
                         ->where('unterkurs = :knr:')
                         ->paramString('knr', $unterkursKnr)
                         ->getSQL();

    $qbSub2 = $this->getSelectQueryBuilder();
    $subselect2 = $qbSub2->select('unterkurs')
                          ->from('kursgruppe')
                          ->where('hauptkurs = (:subselect:)')
                          ->and('unterkurs != :knr:')
                          ->paramRaw('subselect', $subselect1)
                          ->paramString('knr', $unterkursKnr)
                          ->getSQL();

    $qb = $this->getSelectQueryBuilder();
    $sql = $qb->select($this->select)
              ->from('kurse k')
              ->where('k.nr in (:subselect:)')
              ->paramRaw('subselect', $subselect2)
              ->getSQL();

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

    return $this->getAllRows();
  }

  
  public function getUnterkurse($pKnr, $pNurTeilzeit, $pNurVollzeit)
  {
    return $this->getUnterkurseForKnrs(array($pKnr), $pNurTeilzeit, $pNurVollzeit);
  }

  

  
  public function getUnterkurseForKnrs($pKursIds, $pNurTeilzeit, $pNurVollzeit)
  {
    if ($pNurTeilzeit == true && $pNurVollzeit == true) {
      return array();
    }

    if ($pNurTeilzeit) {
      $vollzeitOrTeilzeitEinschraenkung = "AND k.verweil = 'T' ";
    } elseif ($pNurVollzeit) {
      $vollzeitOrTeilzeitEinschraenkung = "AND k.verweil = 'V' ";
    } else {
      $vollzeitOrTeilzeitEinschraenkung = '';
    }

    if (DBWorkBASysSettings::isIHK()) {
      
      $orderby = $this->dboperation->dbparser->substring('k.nr', 6, 2) . ', k.beginn_dat';
    } elseif (DBWorkBASysSettings::isAugsburgKolping() || DBWorkBASysSettings::isFIHH() || DBWorkBASysSettings::isRMA()) {
      $orderby = 'k.beginn_dat';
    } elseif (DBWorkBASysSettings::isOsnabrueckKKVHS()) {
      $orderby = 'k.nr';
    } else {
      $orderby = 'k.ort';
    }
    if (isset($this->enforceBeginnDatSort) && $this->enforceBeginnDatSort) {
      $orderby = 'k.beginn_dat, k.nr';
    }

    $sql
      = 'SELECT ' . $this->select . ' FROM kursgruppe kg JOIN kurse k ON kg.unterkurs = k.nr ' . "\n"
      . ' WHERE kg.hauptkurs IN (\'' . implode('\', \'', $pKursIds) . '\')' . "\n"
       . $vollzeitOrTeilzeitEinschraenkung . ' ' . $this->getSearchSQL('k.') . ' '
      . 'ORDER BY ' . $orderby;

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

    return $this->getAllRows();
  }

  

  
  public function isOhneKursgruppe($pKursNr)
  {
    $kursNr = $this->dboperation->getString4Sql($pKursNr);
    $sql = 'select id from kursgruppe where unterkurs=\'' . $kursNr . '\' or hauptkurs=\'' . $kursNr . '\'';
    $this->dboperation->clear();
    $this->dboperation->query($sql);

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

  
  public function getKursgruppenTitel($pKursNr)
  {
    $kursNr = $this->dboperation->getString4Sql($pKursNr);
    $sql = 'select k.kurzbez, k.titel from kursgruppe kg join kurse k on kg.hauptkurs = k.nr where kg.unterkurs = \'' . $kursNr . '\'';
    $this->dboperation->clear();
    $this->dboperation->query($sql);
    if ($this->dboperation->rowcount() == 0) {
      return '';
    } else {
      $this->dboperation->selectrow(0);
      $titel = $this->getDatamodulValue('titel');
      if ($titel != '') {
        return $titel;
      } else {
        return $this->getDatamodulValue('kurzbez');
      }
    }
  }

  
  public function getKursgruppenNr($pKursNr)
  {
    $kursNr = $this->dboperation->getString4Sql($pKursNr);
    $sql = 'select k.nr, k.kurzbez, k.titel from kursgruppe kg join kurse k on kg.hauptkurs = k.nr where kg.unterkurs = \'' . $kursNr . '\'';
    $this->dboperation->clear();
    $this->dboperation->query($sql);
    if ($this->dboperation->rowcount() == 0) {
      return '';
    } else {
      $this->dboperation->selectrow(0);

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

  
  public function getKursgruppenNrAndTitel($pKursNr)
  {
    $kursNr = $this->dboperation->getString4Sql($pKursNr);
    $sql = 'select k.nr, k.kurzbez, k.titel from kursgruppe kg join kurse k on kg.hauptkurs = k.nr where kg.unterkurs = \'' . $kursNr . '\'';
    $this->dboperation->clear();
    $this->dboperation->query($sql);
    if ($this->dboperation->rowcount() == 0) {
      return '';
    } else {
      $this->dboperation->selectrow(0);
      $result[$this->getDatamodulValue('nr')] = $this->getDatamodulValue('kurzbez');

      return $result;
    }
  }

  
  public function getTitelDerUnterKurse($pKursNr)
  {
    $kursNr = $this->dboperation->getString4Sql($pKursNr);
    $sql = 'select nr, kurzbez from kurse where nr in (select unterkurs from kursgruppe kg where kg.hauptkurs=\'' . $kursNr . '\')';
    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $rowcount = $this->dboperation->rowcount();
    if (0 == $rowcount) {
      return false;
    } else {
      $result = array();
      for ($i = 0; $i < $rowcount; $i++) {
        $this->dboperation->selectrow($i);
        $result[$this->getDatamodulValue('nr')] = $this->getDatamodulValue('kurzbez');
      }

      return $result;
    }
  }

  
  public function getZimmerUnterKurs($pKursNr, $pArt)
  {
    if ($pArt != 'EZ' && $pArt != 'DZ') {
      return false;
    }
    $unterkurse = $this->getTitelDerUnterKurse($pKursNr);
    $kriterium = 'Zweibettzimmer';
    if ($pArt == 'EZ') {
      $kriterium = 'Einbettzimmer';
    }
    foreach ($unterkurse as $knr => $titel) {
      if (false !== StringOperations::strpos($titel, $kriterium)) {
        return $knr;
      }
    }

    return false;
  }

  
  public function getAlternativKurseIdsByKnr($pKursNr)
  {
    $knr = $this->dboperation->getString4Sql($pKursNr);

    $query = 'select a.alternativkurs from alternat a join kurse k on k.nr = '
             . "a.alternativkurs where a.knr = '" . $knr . "' " . $this->getSearchSQL('k.');

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

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

  
  public function getAehnlicheKurseIdsByKnr($pKursNr)
  {
    $knr = $this->dboperation->getString4Sql($pKursNr);

    $query = 'select a.aehnlich from kurs_aehnlich a join kurse k on k.nr = '
             . "a.aehnlich where a.kurs = '" . $knr . "' " . $this->getSearchSQL('k.')
            . ' order by a.lfd';

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

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

  
  public function getIdsVonAnderenKursenDieVonTeilnehmernDiesesKursesGebuchtWurden($pKnr, $pRefreshCache = false)
  {
    $knr = $this->dboperation->getString4Sql($pKnr);
    static $cache = array();
    if ($pRefreshCache || !isset($cache[$knr])) {
      $sql = 'SELECT distinct(k.nr) FROM belegung b1 JOIN belegung b ON b1.tnr = b.tnr JOIN kurse k on k.nr = b.knr '
              . "WHERE b1.knr='$knr' and b.knr <> '$knr' and b1.tnr>0 and " 
              . $this->anmshrink . $this->getSearchSQL('k.');
      $sql .= $this->getCompleteOrderByDefault('nr', 'k.');
      $this->dboperation->clear();
      $this->dboperation->query($sql);
      $cache[$knr] = $this->getAllRowsSingleColumn('nr');
    }

    return $cache[$knr];
  }

  public function getKursIdsWithMonthAndYearByYear($pYear = false)
  {
    $lower = $upper = false;
    if ($pYear !== false) {
      $year = $this->dboperation->getInt4Sql($pYear);
      if (is_numeric($year)) {
        $lower = '01.01.' . $year;
        $upper = '31.12.' . $year;
      }
    }

    return $this->getKursIdsWithMonthAndYear($lower, $upper);
  }

  public function getKursIdsWithMonthAndYear($from = false, $to = false, $joinKurseAa = false)
  {
    $join = $joinKurseAa ? 'join kurse_aa kaa on k.nr = kaa.nr' : '';
    $sql = "select k.nr, k.beginn_dat from kurse k $join WHERE 1 = 1 "
               . $this->getSearchSQL('k.');

    if ($from && $to && $this->datetimeop->isValidDate($from) && $this->datetimeop->isValidDate($to)) {
      $sql .= ' AND k.beginn_dat BETWEEN ' . $this->datetimeop->getFormatDate4Server($from) . ' AND ' . $this->datetimeop->getFormatDate4Server($to);
    }

    $sql .= $this->getCompleteOrderByDefault('', 'k.');

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

    $out = array();

    $rowcount = $this->dboperation->rowcount();
    for ($i = 0; $i < $rowcount; $i++) {
      $this->dboperation->selectrow($i);
      $date = new Datetime($this->getDatamodulValue('beginn_dat'));

      $out[] = array(
          'nr' => $this->getDatamodulValue('nr'),
          'monat' => date_format($date, 'm'),
          'jahr' => date_format($date, 'Y')
          );
    }

    return $out;
  }

  
  public function getKnrGrillhuetteByKnrSuffixAndYear(string $pKnrSuffix, string $pYear): ?string
  {
    $queryBuilder = $this->getSelectQueryBuilder()
      ->select('nr')
      ->from($this->getTableName())
      ->where('typ = \'B\'')
      ->and('nr LIKE :nr:')
      ->and('beginn_dat = :beginn_dat:')
      ->and('ende_dat = :ende_dat:')
      ->paramString('nr', '%' . $pKnrSuffix)
      ->paramDate('beginn_dat', '01.01.' . $pYear)
      ->paramDate('ende_dat', '31.12.' . $pYear);

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

    return $this->getFirstRow()->id;
  }

  
  public function getKursNrKurszbezMitUngenauerKnr(string $pUngenaueKnr): array
  {
    
    $connectionInfo = DBConnectionInfo::getInstance();
    $isMySQL = $connectionInfo->db_typ == DBTypeEnum::MYSQL || $connectionInfo->db_typ == DBTypeEnum::MYSQLI;

    $limit = $isMySQL ? ' LIMIT 20 ' : '';
    $top = $isMySQL ? '' : ' TOP 20 ';

    $qb = $this->getSelectQueryBuilder();
    $qb->select(':top: nr, kurzbez ')
        ->from($this->getTableName())
        ->where('nr LIKE :ungenaueknr: :limit:')
        ->paramString('ungenaueknr', '%' . $pUngenaueKnr . '%')
        ->paramStringNoQuotes('top', $top)
        ->paramStringNoQuotes('limit', $limit);
    $this->dboperation->clear();
    $this->dboperation->query($qb->getSQL());

    return $this->getAllRows();
  }

  
  public function getSqlPartKeineAusgebuchtenKurse($pPraefixKurs = 'k.', $pPraefixKurs2 = 'k2.')
  {
    $sqlPartMaxKinderTnKi = '';
    if (System::isKuferSql5()) {
      $sqlPartMaxKinderTnKi = 'AND (' . $this->dboperation->dbparser->isNull($pPraefixKurs . 'kinder_max', 0) . ' = 0 '
              . 'OR (SELECT count(b.id) FROM belegung b WHERE b.knr = ' . $pPraefixKurs . 'nr AND ' . $this->anmshrink
              . ' ' . DBWorkBelegung::getKindBelegungenSQLPart() . ') < ' . $pPraefixKurs . 'kinder_max) ';

      $sqlPartMaxKinderTnKi .= 'AND (' . $this->dboperation->dbparser->isNull($pPraefixKurs2 . 'tnki_max', 0) . ' = 0 '
              . 'OR (SELECT count(b.id) FROM belegung b WHERE b.knr = ' . $pPraefixKurs . 'nr AND ' . $this->anmshrink
              . ') < ' . $pPraefixKurs2 . 'tnki_max) ';
    }

    return ' AND (SELECT count(b.id) FROM belegung b WHERE b.knr = ' . $pPraefixKurs . 'nr AND ' . $this->anmshrink
            . ' ' . DBWorkBelegung::getNichtKindBelegungenSQLPart() . ') < ' . $pPraefixKurs . 'teil_max ' . $sqlPartMaxKinderTnKi;
  }

  
  public function getKoelnAKSVerschiebungsKurseQuery($kursNr, $coincidentCharsInKnrCount): \SelectQueryBuilder
  {
    $first4Chars = \StringOperations::substr($kursNr, 0, $coincidentCharsInKnrCount);


    return $this->getAllQuery()
      ->and('k.nr_anzeige LIKE :like4Chars:')
      ->and('k.nr_anzeige <> :knr:')
      ->paramString('like4Chars', $first4Chars . '%')
      ->paramString('knr', $kursNr);
  }

  
  public function getKoelnAKSVerschiebungsKurseIds($kursNr, $coincidentCharsInKnrCount)
  {
    $builder = $this->getKoelnAKSVerschiebungsKurseQuery($kursNr, $coincidentCharsInKnrCount)
      ->select('k.nr');

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

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

  

  
  public function getKoelnAKSVerschiebungsKurseIdsCount($kursNr, $coincidentCharsInKnrCount)
  {
    $builder = $this->getKoelnAKSVerschiebungsKurseQuery($kursNr, $coincidentCharsInKnrCount)
      ->select('COUNT(k.nr) AS anzahl');

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

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

  public function getKurseBeendete()
  {
    $stringOps = new \StringOperations();
    $offsetDays = $this->objsettings_web->KurseOffset_beendete; 
    $semesterBeendete = $this->objsettings_web->semlist_beendete;
    $semesterBeendeteInList = $stringOps->convertCommaSeparatedList2InList($semesterBeendete);

    $dtops = new \DateTimeOperations();
    $datumBisNonServer = \DateTimeOperations::getDateDaysBefore(\DateTimeOperations::getDateNow(), $offsetDays);
    $datumBis = $dtops->getFormatDate4Server($datumBisNonServer);
    $sql = 'SELECT ' . $this->select . ' FROM kurse k '
      . ' WHERE k.semester IN (' . $semesterBeendeteInList . ') AND k.ende_dat <= ' . $datumBis . ' '
      . $this->getSearchSQL('k.')
      . $this->getOrderByPart();

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

    return $this->getAllRows();
  }

  
  public function getKursCountByInternetaktiv(): array
  {
    $builder = $this->getSelectQueryBuilder()
      ->select(array('internetaktiv, COUNT(internetaktiv)'))
      ->from($this->getTableName())
      ->groupBy('internetaktiv');

    $this->dboperation->clear();
    if (!$this->dboperation->query($builder->getSQL(), false)) {
      return array(
        true => 0,
        false => 0
      );
    }

    $result = array();
    $this->dboperation->resetToFirstRow();
    while ($this->dboperation->selectNextRow()) {
      $status = $this->getValue('internetaktiv', \DatamodulVarType::TYPE_BOOL_WF);
      $count = $this->getValue('anzahl', \DatamodulVarType::TYPE_INT);

      $result[$status] = $count + ($result[$status] ?? 0);
    }

    if (!isset($result[true])) {
      $result[true] = 0;
    }

    if (!isset($result[false])) {
      $result[false] = 0;
    }

    return $result;
  }

  
  public function getBuchbareKurseIds(bool $ignoreAnmeldeart = false): array
  {
    $builder = ($ignoreAnmeldeart
        ? $this->getKurseWithFilterQuery()
        : $this->getKurseByAnmeldeartQuery(array(\Kurs::WEBFLAG_NORMAL), array(\Kurs::WEBFLAG_KEINE)))
      ->select(array('k.nr', 'k.beginn_dat', 'k.anm_ende', 'k.elearningKursID', 'k.anm_ende_manu'));

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

    $dummyKurs = $this->getNewObject();
    $teilnehmer = \SessionOperations::getCurrentWebToolsTeilnehmer();

    $result = array();
    $this->dboperation->resetToFirstRow();
    while ($this->dboperation->selectNextRow()) {
      $dummyKurs->id = $this->getDatamodulValue('nr');
      
      $dummyKurs->beginn_dat = $this->getDatamodulValue('beginn_dat');

      $dummyKurs->elearningKursID = $this->getDatamodulValue('elearningKursID');
      $dummyKurs->anm_ende = $this->datetimeop->getEmptyStringWhenDefaultDateTime($this->getDatamodulValue('anm_ende'));
      $dummyKurs->anm_ende_manu = $this->getDatamodulValue('anm_ende_manu');

      $anmeldeEndeRaw = \Kufer\Web\Classes\Addfunctions\KursOperations::getAnmEnde($dummyKurs);
      $anmeldeEnde = $anmeldeEndeRaw === '' ? '' : \explode(' ', \DateTimeOperations::getValidDateTimeString($anmeldeEndeRaw))[0];

      
      
      $gestern = DateTimeOperations::getNextDate(DateTimeOperations::getDateNow(), -1);
      
      if (($anmeldeEnde == '' || \DateTimeOperations::isDateAfter($anmeldeEnde, $gestern))
        && !ToolsAPI::isPersonWithoutMoodleCredentialsForElearningCourse($teilnehmer, $dummyKurs)
        && ToolsAPI::isTnMoodleRegisterableOrAlreadyRegistered($teilnehmer, $dummyKurs)) {
        $result[] = $dummyKurs->id; 
      }
    }

    return $result;
  }
}