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

class DBWorkKurseBase extends DBWork
{
  
  public $dbzusatztexte;

  
  public $dbwKurseFreidefFelder;

  
  public $dbwKurse2;

  
  public $objsettings; 
  
  public $objsettings_web;
  
  public $justNoAusfall = false; 

  
  public $keineHauptkurse = false;

  
  public $searchIntersectSpecialCategories = false;

  
  public $categorystring;

  protected static $_cache_KursCache = array();

  
  public $constop;        
  
  public $anmshrink;

  
  public $beratungstageKurseAusblenden = true;

  
  public $beruecksichtigeKurstagRaumanzeige = false;

  public const FILTER_KURSAUSFALL_ALLE = '';
  public const FILTER_KURSAUSFALL_OHNE_AUSGEFALLENE = 'ohne_ausgefallene';
  public const FILTER_KURSAUSFALL_NUR_AUSGEFALLENE = 'nur_ausgefallene';

  public const FILTER_KURSABSCHLUSS_OHNE_ABGESCHLOSSENE = 'ohne_abgeschlossene';
  public const FILTER_KURSABSCHLUSS_NUR_ABGESCHLOSSENE = 'nur_abgeschlossene';

  public const FILTER_KURSSTATUS_NICHT_BEGONNENE = 'nicht_begonnene';
  public const FILTER_KURSSTATUS_LAUFENDE_BY_DATUM = 'laufende';
  public const FILTER_KURSSTATUS_BEENDETE = 'beendete';
  public const FILTER_KURSSTATUS_LAUFENDE_BY_ABSCHLUSS = 'nur_laufende';

  
  public $selectFreidef;

  public function __construct($pDBTyp = 1, $pNewInstance = false)
  {
    parent::__construct($pDBTyp, $pNewInstance);
    $this->dbzusatztexte = new DBWorkKurszusatz($this->dbtyp);
    $this->dbwKurseFreidefFelder = new \DBWorkKurseFreidefFelderAb17($this->dbtyp);
    $this->dbwKurse2 = new DBWorkKurse2($this->dbtyp);

    $this->constop = new ConstantOperations();
    $this->anmshrink = '(b.status_1 in (' . $this->constop->getStatusangemeldetSQLStr() . ") or b.status_1 is null)\n ";
    

    $this->objsettings = ToolSettingsAPI::getToolSettings(0);
    $this->objsettings_web = ToolSettingsAPI::getToolSettings(4);

    $this->select = "k.nr, k.nr_anzeige, k.knr_org,k.beginn_dat, k.ende_dat, k.ort,k.dauer,k.aussenst,k.semester, \n
                     k.ausfall,k.kurzbez,k.beginn,k.ende,k.gebuehr,k.abschluss,k.stundenanz, \n
                     k.progheft,k.progh_sort,k.abwktage,k.dozentenbewerbung, k.hpm, k.kart_key, \n
                     k.teil_min,k.teil_max,k.teil_akt,k.fachb,k.fachb_bvv,k.fachb_land,k.anm_ende,k.geb_erm,k.kinderbetr,\n
                     k.kinder_geb,k.kinder_min,k.kinder_max,k.dauerdetai,k.geb_kind,k.geb_zuskind, \n
                     k.webflags, k.punkte, k.geb_schw1, k.geb_schw2, k.tn_schw_1, k.tn_schw_2, " . $this->clob('k.vertrag') . ", \n"
                     . $this->dboperation->dbparser->cast2Text('warnung', 'k') . ",\n"
                     . $this->dboperation->dbparser->cast2Text('titel', 'k') . ",\n"
                     . $this->dboperation->dbparser->cast2Varchar('gebuehrergaenztext', 'k') . ",\n"
                     . "k.kurs_art, k.kurs_ziel, k.ferienkal, k.koop, k.abw_anrechen, k.abrechnung, k.koop_dvv, k.tag_woche, \n"
                     . "k.tag_abend, k.pruef_dvv, k.uebernacht, k.ein_mehrw, k.ausst_tage, k.zielgr_dvv, k.auftrag, \n"



                     . "k.anm_art, \n"
                     . "k.emailstatus, k.kurs_zielg, k.kzielg_key, k.altervon, k.alterbis, k.sofortzahl, k.folgekurs, k.vorg_kurs, \n"
                     . 'k.verlauf, k.neuerkurs, k.vertrtage, k.teil_wart, k.abg_datum, ' . $this->clob('k.zeugnis') . ', k.anrechen, k.phase, k.nw_nr_alt, anm_ende_manu, k.punktesystem,  '
                     . "k.erm_schw2, k.dozstdreal, k.verweil, k.sprache, k.elearningKursID, k.bis_max, k.mandant, k.teinzel, k.kinder_ev, k.typ, k.uetag, k.minutentag, k.pausetag, \n"
                     . "k.teinzel_m, k.teinzel_w, k.teinzel_u, k.keinzel_m, k.keinzel_w, k.keinzel_u, k.tn_nichtnrw, k.zuseintext, k.zuseinn, k.ausg_text, k.ausg_betr, k.ausstell, k.ksttraeger, \n"
                     
                     . \implode(',', \array_map(function($i) {
                       return 'k.kursauswahl' . $i;
                     }, \range(0, 9))) . ",\n"
                     . \implode(',', \array_map(function($i) {
                       return 'k.kursauswahl' . $i;
                     }, \range(11, 16))) . ", \n"
                     . 'k.bearbeiter, k.ksk, ' . $this->clob('k.veranstaltertxt') . ', k.internetvon, k.internetbis';

    
    $this->selectFreidef = \implode(',', \array_map(function($i) {
      return 'kfreidef.kursauswahl' . $i;
    }, \range(17, 40)));


    
    
    $this->translationMapping = array('kurzbez' => 'kurzbez', 'titel' => 'titel', 'info' => 'info', 'material' => 'material', 'vorspann' => 'vorspann',
        'nachspann' => 'nachspann', 'individtermine' => 'individtermine', 'keinkursbeginn' => 'keinkursbeginn',
        'termindarstweb' => 'termindarstweb', 'gebuehrergaenztext' => 'gebuehrergaenztext', 'keinewebanmeld' => 'keinewebanmeld',
        'dauerdetai' => 'dauerdetai', 'aussenst' => 'aussenst', 'webinfo' => 'webinfo');
  }

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

  public function getCurrentRow($pRefreshCache = false)
  {
    $basyssettings = BASysSettingsAPI::getBASysSettings();
    $tmp = $this->getNewObject();
    $tmp->id = $this->getDatamodulValue('nr');
    $tmp->nrab = StringOperations::substr($tmp->id, $basyssettings->kursnrab - 1);
    $tmp->nr_anzeige = $this->getDatamodulValue('nr_anzeige');
    $tmp->nr_anzeigeab = StringOperations::substr($tmp->nr_anzeige, $basyssettings->kursnrab - 1);

    $tmp->beginn_dat = $this->getDatamodulValue('beginn_dat');
    $tmp->ende_dat = $this->getDatamodulValue('ende_dat');

    $tmp->wochentage = $this->datetimeop->getWeekDays($this->getDatamodulValue('knr_org'));
    
    $tmp->knr_org = StringOperations::rpad($this->getDatamodulValue('knr_org'), 10, ' ');
    $tmp->uetag = $this->getDatamodulValue('uetag');
    $tmp->minutentag = $this->getDatamodulValue('minutentag');
    $tmp->pausetag = $this->getDatamodulValue('pausetag');
    $tmp->fachb = $this->getDatamodulValue('fachb');
    $tmp->fachb_bvv = $this->getDatamodulValue('fachb_bvv');
    $tmp->fachb_land = $this->getDatamodulValue('fachb_land');
    $tmp->beginn_ende = $this->getDatamodulValue('beginn') . ' - ' . $this->getDatamodulValue('ende');
    $tmp->ort = $this->getDatamodulValue('ort');
    $tmp->dauer = $this->getDatamodulValue('dauer');
    $tmp->aussenst = $this->getDatamodulValue('aussenst');
    $tmp->semester = $this->getDatamodulValue('semester');
    $tmp->invisibleSemester = $tmp->semester;
    $tmp->ausfall = $this->getDatamodulValue('ausfall');
    $tmp->invisibleAusfall = $tmp->ausfall;
    $tmp->kurzbez = $this->getDatamodulValue('kurzbez');
    $tmp->dauerdetai = $this->getDatamodulValue('dauerdetai');
    $tmp->beginn = $this->getDatamodulValue('beginn');
    $tmp->ende = $this->getDatamodulValue('ende');
    $tmp->gebuehr = $this->getDatamodulValue('gebuehr');
    $tmp->gebuehrergaenztext = $this->getDatamodulValue('gebuehrergaenztext');
    $tmp->geb_erm = $this->getDatamodulValue('geb_erm');
    $tmp->kinderbetr = $this->getKinderBetreuung();
    $tmp->kinder_geb = $this->getDatamodulValue('kinder_geb');
    $tmp->kinder_min = $this->getDatamodulValue('kinder_min');
    $tmp->kinder_max = $this->getDatamodulValue('kinder_max');
    $tmp->geb_kind = $this->getDatamodulValue('geb_kind');
    $tmp->geb_zuskind = $this->getDatamodulValue('geb_zuskind');
    $tmp->abschluss = $this->getDatamodulValue('abschluss');
    $tmp->stundenanz = $this->mathop->truncateNull($this->getDatamodulValue('stundenanz'));
    $tmp->dozstdreal = $this->mathop->truncateNull($this->getDatamodulValue('dozstdreal'));
    $tmp->progheft = $this->getDatamodulValue('progheft');
    
    $tmp->progh_sort = $this->getDatamodulValue('progh_sort');
    $tmp->teil_min = $this->getDatamodulValue('teil_min');
    $tmp->teil_max = $this->getDatamodulValue('teil_max');
    $tmp->dozentenbewerbung = $this->getDatamodulValue('dozentenbewerbung');
    $tmp->abwktage = $this->getDatamodulValue('abwktage');

    $tmp->warnung = $this->getDatamodulValue('warnung');

    $tmp->titel = $this->getDatamodulValue('titel');
    $tmp->typ = $this->getDatamodulValue('typ');

    $tmp->hpm = $this->getDatamodulValue('hpm');
    $tmp->webflags = $this->getDatamodulValue('webflags');

    
    if ($tmp->webflags != 1 && System::isKuferSql5() && $tmp->typ == 'K') {
      $tmp->webflags = 4;
    }

    $tmp->websuchbegriffe = $this->getDatamodulValue('websuchbegriffe');

    $tmp->punkte = $this->getDatamodulValue('punkte');
    $tmp->geb_schw1 = $this->getDatamodulValue('geb_schw1');
    $tmp->geb_schw2 = $this->getDatamodulValue('geb_schw2');
    $tmp->tn_schw_1 = $this->getDatamodulValue('tn_schw_1');
    $tmp->tn_schw_2 = $this->getDatamodulValue('tn_schw_2');
    
    $tmp->erm_schw2 = $this->getDatamodulValue('erm_schw2');
    
    
    for ($i = 0; $i < 10; ++$i) {
      $tmp->{'kursauswahl' . $i} = $this->getDatamodulValue('kursauswahl' . $i);
    }
    $tmp->kursauswahl10 = $this->getDatamodulValue('kursauswahl0');
    for ($i = 11; $i <= 16; ++$i) {
      $tmp->{'kursauswahl' . $i} = $this->getDatamodulValue('kursauswahl' . $i);
    }
    
    if ($this->dboperation->fieldExists('kursauswahl17')) {
      
      for ($i = 17; $i <= 40; ++$i) {
        $tmp->{'kursauswahl' . $i} = $this->getDatamodulValue('kursauswahl' . $i);
      }
    }

    
    $tmp->anm_art = $this->getDatamodulValue('anm_art');
    $tmp->emailstatus = $this->getDatamodulValue('emailstatus');
    if ($tmp->emailstatus == '') {
      $tmp->emailstatus = 'A';
    } 
    $tmp->kart_key = $this->getDatamodulValue('kart_key');
    $tmp->kurs_zielg = $this->getDatamodulValue('kurs_zielg');
    $tmp->kzielg_key = $this->getDatamodulValue('kzielg_key');
    $tmp->altervon = $this->getDatamodulValue('altervon');
    $tmp->alterbis = $this->getDatamodulValue('alterbis');
    $tmp->sofortzahl = $this->getDatamodulValue('sofortzahl');
    
    $tmp->folgekurs = $this->getDatamodulValue('folgekurs');
    $tmp->vorg_kurs = $this->getDatamodulValue('vorg_kurs');
    $tmp->verlauf = $this->getDatamodulValue('verlauf');
    $tmp->neuerkurs = $this->getDatamodulValue('neuerkurs');
    $tmp->vertrtage = $this->getDatamodulValue('vertrtage');
    $tmp->vertrag = $this->getDatamodulValue('vertrag');
    $tmp->zeugnis = $this->getDatamodulValue('zeugnis');
    
    
    $tmp->abg_datum = $this->getDatamodulValue('abg_datum');
    $tmp->anrechen = $this->getDatamodulValue('anrechen');
    $tmp->phase = $this->getDatamodulValue('phase');
    $tmp->nw_nr_alt = $this->getDatamodulValue('nw_nr_alt');
    $tmp->anm_ende = $this->datetimeop->getEmptyStringWhenDefaultDateTime($this->getDatamodulValue('anm_ende'));
    $tmp->anm_ende_manu = $this->getDatamodulValue('anm_ende_manu');
    $tmp->punktesystem = $this->getDatamodulValue('punktesystem');
    $tmp->verweil = $this->getDatamodulValue('verweil');
    $tmp->sprache = $this->getDatamodulValue('sprache');
    $tmp->elearningKursID = $this->getDatamodulValue('elearningKursID');
    $tmp->bis_max = $this->getDatamodulValue('bis_max');
    $tmp->mandant = $this->getDatamodulValue('mandant');
    $tmp->teinzel = $this->getDatamodulValue('teinzel');
    $tmp->kinder_ev = $this->getDatamodulValue('kinder_ev');
    $tmp->teinzel_m = $this->getDatamodulValue('teinzel_m');
    $tmp->teinzel_w = $this->getDatamodulValue('teinzel_w');
    $tmp->teinzel_u = $this->getDatamodulValue('teinzel_u');
    $tmp->keinzel_m = $this->getDatamodulValue('keinzel_m');
    $tmp->keinzel_w = $this->getDatamodulValue('keinzel_w');
    $tmp->keinzel_u = $this->getDatamodulValue('keinzel_u');
    $tmp->tn_nichtnrw = $this->getDatamodulValue('tn_nichtnrw');
    $tmp->zuseintext = $this->getDatamodulValue('zuseintext');
    $tmp->zuseinn = $this->getDatamodulValue('zuseinn');
    $tmp->ausg_text = $this->getDatamodulValue('ausg_text');
    $tmp->ausg_betr = $this->getDatamodulValue('ausg_betr');
    $tmp->ausstell = $this->getDatamodulValue('ausstell');
    $tmp->ksttraeger = $this->getDatamodulValue('ksttraeger');
    $tmp->bearbeiter = $this->getDatamodulValue('bearbeiter');
    $tmp->internetvon = $this->getDatamodulValue('internetvon');
    $tmp->internetbis = $this->getDatamodulValue('internetbis');

    $tmp->kurs_art = $this->getDatamodulValue('kurs_art');
    $tmp->kurs_ziel = $this->getDatamodulValue('kurs_ziel');
    $tmp->ferienkal = $this->getDatamodulValue('ferienkal');
    $tmp->koop = $this->getDatamodulValue('koop');
    $tmp->abw_anrechen = $this->getDatamodulValue('abw_anrechen');
    $tmp->abrechnung = $this->getDatamodulValue('abrechnung');
    $tmp->koop_dvv = $this->getDatamodulValue('koop_dvv');
    $tmp->tag_woche = $this->getDatamodulValue('tag_woche');
    $tmp->tag_abend = $this->getDatamodulValue('tag_abend');
    $tmp->pruef_dvv = $this->getDatamodulValue('pruef_dvv');
    $tmp->uebernacht = $this->getDatamodulValue('uebernacht');
    $tmp->ein_mehrw = $this->getDatamodulValue('ein_mehrw');
    $tmp->ausst_tage = $this->getDatamodulValue('ausst_tage');
    $tmp->zielgr_dvv = $this->getDatamodulValue('zielgr_dvv');
    $tmp->auftrag = $this->getDatamodulValue('auftrag');
    $tmp->ksk = $this->getDatamodulValue('ksk');
    $tmp->veranstaltertxt = $this->getDatamodulValue('veranstaltertxt');

    return $tmp;
  }

  protected function postGetAllRowsHook($pDatamodules, $pRefreshCache = false)
  {
    if (count($pDatamodules) == 0) {
      return $pDatamodules;
    }

    $kursnummern = ArrayOperations::mapAttribut($pDatamodules, 'id');

    $zusatzTexteMapKnr = $this->dbzusatztexte->getZusatzTexteByKursIds($kursnummern);

    $requireFreidef = false;
    foreach ($pDatamodules as $kurs) {
      if (!isset($kurs->kursauswahl17)) {
        $requireFreidef = true;
        break;
      }
    }
    if ($requireFreidef) { 
      $freidefFelderAb17 = \ArrayOperations::setAttributAsKey($this->dbwKurseFreidefFelder->findByIds($kursnummern), 'id');
    }

    $tempkurse = DBWorkKurse::getDefaultInstance($this->dbtyp, true);
    $tempkurse->fillTexteCache($kursnummern, $pRefreshCache);

    $kurse2 = $this->dbwKurse2->getByIds($kursnummern);
    $kurse2MapKnr = ArrayOperations::setAttributAsKey($kurse2, 'id');

    $dbwDozent = DBWorkDozenten::getDefaultInstance($this->dbtyp, true, true);
    $hauptDozentenMapKnr = $dbwDozent->getHauptDozenten($kursnummern, true);

    
    
    foreach ($pDatamodules as $key => $kurs) {
      if (isset($zusatzTexteMapKnr[$kurs->id])) {
        $pDatamodules[$key]->zusatztexte = $zusatzTexteMapKnr[$kurs->id];
      }

      $kurs->anm_ende = \Kufer\Web\Classes\Addfunctions\KursOperations::getAnmEnde($kurs);

      $pDatamodules[$key]->hauptdozent = isset($hauptDozentenMapKnr[$kurs->id]) ? $hauptDozentenMapKnr[$kurs->id] : new Dozent();

      
      if ($requireFreidef && isset($freidefFelderAb17[$kurs->id])) {
        $felder = $freidefFelderAb17[$kurs->id];
        foreach ($this->dbwKurseFreidefFelder->getKursauswahlFields() as $varname) {
          $pDatamodules[$key]->{$varname} = $felder->{$varname};
        }
      }

      if (isset($kurse2MapKnr[$kurs->id])) {
        $pDatamodules[$key]->kurse2 = $kurse2MapKnr[$kurs->id];
      } else {
        $pDatamodules[$key]->kurse2 = $this->dbwKurse2->getNewObject();
      }

      
      $pDatamodules[$key] = $tempkurse->fillTexts($pDatamodules[$key]);

      $kurs->updateTeilnehmerzahlenFelder(\TeilnehmerzahlenFactory::getTnZahlenProviderFromDb($kurs));

      $kurs->anmeldungen = $kurs->teil_akt; 

      $kurs->anmeldungeng = \Kufer\Web\Classes\Ampel\AmpelStatus\AmpelStatusBerechnungFassade::getAmpel4Status($kurs, false, true, false);
      $kurs->anmeldungeng_web = \Kufer\Web\Classes\Ampel\AmpelStatus\AmpelStatusBerechnungFassade::getAmpel4Status($kurs, false, false, false);

      $kurs->anmeldungenAktuellVonMax = '<span class="anmeldungStatus' . CleanOperations::cleanStringForHtml($kurs->anmeldungeng) . ' anmeldungAktuell">'
              . CleanOperations::cleanStringForHtml($kurs->teil_akt) . '</span>/' . CleanOperations::cleanStringForHtml($kurs->teil_max);
    }

    
    
    
    

    return parent::postGetAllRowsHook($pDatamodules, $pRefreshCache);
  }

  
  public function fill4Lang($pDataModules, $pLangId): array
  {
    return $this->fill4LangKurse($pDataModules, $pLangId);
  }

  
  protected function getKinderBetreuung()
  {
    $value = $this->getDatamodulValue('kinderbetr');
    if ($value === 'W' || $value === 'J') {
      return 'J';
    }

    return 'N';
  }

  
  public function getDozentenForKurse($pKursIds, $pRefreshCache = false)
  {
    if (!is_array($pKursIds)) {
      throw new InvalidArgumentException('$pKnrs ist kein Array');
    }

    
    
    
    
    $webdarstBeruecksichtigen = true;
    $typBeruecksichtigen = true;

    $dbwTools = new DBWorkTools(1, true);
    $toolBenGruppeMapping = $dbwTools->getMappingToolIdBenGruppe();
    $toolId = GetOperations::getVarValue('tool_id', -1);
    $withoutDozTypAry = \Kufer\KuferTools\Veranstalter\Utils\VeranstalterUtils::getTypenNichtDozent();

    
    if (isset($toolBenGruppeMapping[$toolId]) && in_array($toolBenGruppeMapping[$toolId], array(KURZ_DOZENT, KURZ_HAUSMEISTER, KURZ_MITARBEITER, KURZ_VERANSTALTER))) {
      $webdarstBeruecksichtigen = false;
      
      if (in_array($toolBenGruppeMapping[$toolId], array(KURZ_MITARBEITER, KURZ_VERANSTALTER))) {
        $typBeruecksichtigen = false;
      }
      if ($toolBenGruppeMapping[$toolId] == KURZ_MITARBEITER) {
        $withoutDozTypAry = array();
      }
    }

    $dbwdoz = new DBWorkDozenten($this->dbtyp, true);

    return $dbwdoz->getDozenten4Kurse($pKursIds, $typBeruecksichtigen, $webdarstBeruecksichtigen, $webdarstBeruecksichtigen, $webdarstBeruecksichtigen, $withoutDozTypAry, $pRefreshCache);
  }

  
  protected function _setDozentenListe($dozAry, $tmp)
  {
    if (count($dozAry) > 0) {
      foreach ($dozAry as $doz) {
        $tmp->dozenten_liste .= '<div style="margin-top:5px;">' . CleanOperations::cleanStringForHtml($doz->name_titel . ' ' . $doz->name . ' ' . $doz->vorname) . '</div>';
      }
    }
  }

  
  protected function _setDozentenKommasepariert($dozAry, $tmp)
  {
    foreach ($dozAry as $doz) {
      $tmp->dozenten_kommasepariert .= $doz->name_titel . ' ' . $doz->name . ' ' . $doz->vorname . ', ';
    }
    $tmp->dozenten_kommasepariert = rtrim($tmp->dozenten_kommasepariert, ', ');
  }

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

  
  public function translateVarName($varname)
  {
    switch ($varname) {
      default: return DBWork::translateVarName($varname);
      case 'id': return 'nr';
      case 'nur_webinfo': return 'webinfo';
      case 'kursauswahl10': return 'kursauswahl0';
      case 'dauer_select': return 'dauer';
      case 'webinforvf': return 'webinfostream';
      case 'kursinforvf': return 'kursinfo';
      case 'nur_webinfohtml': return 'webinfohtml';
    }
  }

  
  public function canupdateDmz($pVarname): bool
  {
    if (\preg_match('/^kursauswahl([0-9]+)$/i', $pVarname, $matches) === 1) {
      return true;
    }

    switch ($pVarname) {
      case 'webinforvf':
      case 'kursinforvf':
        return true;
      default:
        return parent::canupdateDmz($pVarname);
    }
  }

  
  
  
  
  
  public function canupdate($varname)
  {
    $matches = null;
    if (\preg_match('/^kursauswahl([0-9]+)$/i', $varname, $matches) === 1) {
      return $matches[1] < 17;
    }

    switch ($varname) {
      default: return DBWork::canupdate($varname);
      case 'nrab':
      case 'nr_anzeigeab':
      case 'wochentage':
      case 'anmeldungeng':
      case 'teil_akt':
      case 'teil_frei':
      case 'anmeldungen':
      case 'kinderanzahl':
      case 'zusatztexte':
      case 'stichworte':
      case 'gebuehrtabelle':
      case 'hauptdozent':
      case 'haupt':
      case 'anmeldungeng_web':
      case 'qualifikationen':
      case 'sonderkategorie':
      case 'wochentage_suche':
      case 'veranart_suche':
      case 'veranart_suche_negativ':
      case 'zeitraum_suche':
      case 'zeitraum_suche2':
      case 'invisibleAusfall':
      case 'invisibleSemester':
        
      case 'suche_nurNichtVolleKurse':
      case 'suche_kennzeichen':
      case 'suche_kennzeichenAnd':
      case 'suche_anyKennzeichen':
      case 'suche_noKennzeichen':
      case 'suche_exceptKennzeichen':
      case 'suche_bezirk':
      case 'suche_ortPlz':
      case 'suche_ortUmkreisRadius':
      case 'suche_ortUmkreisPlz':
      case 'dozenten_liste':
      case 'dozenten_kommasepariert':
      case 'min_pro_ue':
        
      case 'berufsgruppe4kurs_html':
      case 'anmeldungenAktuellVonMax':
      case 'berufsgruppe4kurs_komma':
      case 'webinfo': 
      case 'arykennzeichen':
      case 'kurse2':
      case 'kinder_wart':
      case 'webinfohtml':
        return false;
      case 'webinforvf':
      case 'kursinforvf':
        return \System::isKuferSql5() && !\SessionOperations::isDmzBetrieb() && !\SessionOperations::isReplikat();
    }
  }

  
  public function getSearchSQLAussenstPart($praeffix)
  {
    if (is_array($this->fromobject->aussenst)) {
      $str = '';
      foreach ($this->fromobject->aussenst as $aussenst) {
        $aussenst = $this->dboperation->getString4Sql($aussenst);
        $str .= $this->dboperation->dbparser->caseInsensitiveLike($praeffix, 'aussenst', $aussenst . '%') . ' OR ';
      }
      if ($str != '') {
        $s = new StringObj($str);
        $str = $s->subString(0, -4)->toString();
      }
    } else {
      $aussenst = $this->dboperation->getString4Sql($this->fromobject->aussenst);
      $str = $this->dboperation->dbparser->caseInsensitiveLike($praeffix, 'aussenst', $aussenst . '%');
    }

    return ' AND (' . $str . ')';
  }

  
  public function getSearchSQLKnrPart($praeffix)
  {
    $str = '';
    
    if (is_array($this->fromobject->id)) {
      $knrAry = array_map(function($knr) { return $this->dboperation->getString4Sql($knr); }, $this->fromobject->id);
      $str .= ' AND ' . $praeffix . 'NR IN (' . StringOperations::convertArray2InList($knrAry) . ')';
    } else {
      if (trim($this->fromobject->id) != '') {
        $len = StringOperations::strlen(trim($this->fromobject->id));
        
        
        
        


        
        
        if (empty($this->toobject->id)) {
          $this->toobject->id = $this->fromobject->id;
        }
      } else {
        $len = StringOperations::strlen(trim($this->toobject->id));
        
        if (empty($this->fromobject->id)) {
          $this->fromobject->id = $this->toobject->id;
        }
      }

      $filterknrab = $this->objsettings_web->checkKursnummerKnrab;
      if (SessionOperations::isKuferTools()) {
        $filterknrab = $this->objsettings->kursFilterCheckKnrAb;
      }

      
      $knrab = 1;
      if ($filterknrab) {
        $basyssettings = BASysSettingsAPI::getBASysSettings();
        $knrab = $basyssettings->kursnrab;
      }

      
      if (DBWorkBASysSettings::isBlak()) {
        $knrab = 6;
      }

      if ($this->fromobject->id == $this->toobject->id) {
        $knr = $this->dboperation->getString4Sql($this->fromobject->id);
        $str .= ' AND (' . $this->dboperation->dbparser->caseInsensitiveLike($praeffix, 'nr_anzeige', $knr) . ') ';
      } else {
        
        $knrFrom = $this->dboperation->getString4Sql($this->fromobject->id);
        $knrTo = $this->dboperation->getString4Sql($this->toobject->id);
        $str .= ' AND (' . $this->dboperation->dbparser->caseInsensitiveBetween(
          '',
          $this->dboperation->dbparser->substring($praeffix . 'nr_anzeige', $knrab, $len),
          $this->stringop->getfilledStr($knrFrom, '0'),
          $this->stringop->getfilledStr($knrTo, 'ZZZZZZZZZZZ')
        );
        $str .= ") \n";
      }
    }

    return $str;
  }

  public function getSearchSqlKeinUnterkursPart($praeffix)
  {
    $pre = $this->dboperation->getString4Sql($praeffix);

    return ' AND ' . $pre . 'nr NOT IN (SELECT unterkurs FROM kursgruppe)';
  }

  public function getSearchSqlKeinHauptkursPart($praeffix)
  {
    $pre = $this->dboperation->getString4Sql($praeffix);

    return ' AND ' . $pre . 'nr NOT IN (SELECT hauptkurs FROM kursgruppe)';
  }

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

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

  public static function getSQLInternetIntranetAktiv($praefix)
  {
    if (KW_INTRANET) {
      return $praefix . "intranet_aktiv = 'W' ";
    } else {
      return $praefix . "internetaktiv = 'W' ";
    }
  }

  
  protected function isHauptDozentVorhanden()
  {
    if (isset($this->fromobject->hauptdozent)
        
        && ((isset($this->fromobject->hauptdozent->name) && $this->fromobject->hauptdozent->name != '')
        
        || (isset($this->fromobject->hauptdozent->vorname) && $this->fromobject->hauptdozent->vorname != '')
        
        || (isset($this->fromobject->hauptdozent->namevorname) && $this->fromobject->hauptdozent->namevorname != '')
        
        || (isset($this->fromobject->hauptdozent->id) && ($this->fromobject->hauptdozent->id != '' && $this->fromobject->hauptdozent->id > -1)))) {
      return true;
    }

    return false;
  }

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

    

    $sql = ''; 

    if ((isset($this->fromobject)) and (isset($this->toobject))) {
      
      
      if ($this->beratungstageKurseAusblenden) {
        $sql .= ' AND ( ' . $praeffix . 'typ IS NULL OR ' . $praeffix . "typ != 'B'  OR (" . $praeffix . "typ = 'B' and " . $praeffix . "webflags = '1'))";
      }


      
      if (isset($this->fromobject->typ)
          && ((is_array($this->fromobject->typ) && count($this->fromobject->typ) > 0)
          || trim($this->fromobject->typ) != '')) {

        if (is_array($this->fromobject->typ)) {
          $str = $this->stringop->convertArray2InList($this->fromobject->typ);
        } else {
          $str = "'" . $this->dboperation->getString4Sql($this->fromobject->typ) . "'";
        }

        $sql .= ' AND ' . $praeffix . 'typ in (' . $str . ') ';
      }

      
      if (isset($this->fromobject->semester) && $this->fromobject->semester != '') {
        $semester = $this->dboperation->getString4Sql($this->fromobject->semester);
        $swortsql = $this->stringop->convertCommaSeparatedList2InList($semester);
        $sql .= ' AND ' . $praeffix . 'semester in (' . $swortsql . ') ';
      }
      
      if (isset($this->fromobject->kurzbez) && trim($this->fromobject->kurzbez) != '') {
        $kurzbez = $this->dboperation->getString4Sql($this->fromobject->kurzbez);
        $sql .= ' AND ( ';
        $sql .= $this->_buildSearchString($kurzbez, $praeffix . 'kurzbez', 2);
        $sql .= $this->_buildSearchString($kurzbez, $praeffix . 'titel', 1);
        $sql .= ') ';
      }

      if (isset($this->fromobject->anm_art) && $this->fromobject->anm_art !== '') {
        $sql .= ' AND ' . $praeffix . 'anm_art = ' . $this->dboperation->getEnclosedString4Sql($this->fromobject->anm_art) . ' ';
      }

      
      if (isset($this->fromobject->verlauf) && $this->fromobject->verlauf != '') {
        $sql .= ' AND ' . $praeffix . "verlauf = '" . $this->dboperation->getString4Sql($this->fromobject->verlauf) . "' ";
      }

      
      if (isset($this->fromobject->aussenst) && ((is_array($this->fromobject->aussenst) && count($this->fromobject->aussenst) > 0) || (is_string($this->fromobject->aussenst) && trim($this->fromobject->aussenst) != ''))) {
        $sql .= $this->getSearchSQLAussenstPart($praeffix);
      }

      
      $verwaltend = isset($this->fromobject->verwaltend) && $this->fromobject->verwaltend;
      $planend = isset($this->fromobject->planend) && $this->fromobject->planend;

      if (($verwaltend || $planend) && !$this->admin) {
        $mitarbeiter = SessionOperations::getCurrentWebToolsMitarbeiter();

        $sqlStatements = array();
        if ($verwaltend) {
          $sqlStatements[] = '(' . $praeffix . "nr IN (SELECT ka.nr FROM kurse_aa ka WHERE ka.verwaltung_ma = '" . $mitarbeiter->kennung . "'))";
        }

        if ($planend) {
          $sqlStatements[] = $praeffix . "hpm = '" . $mitarbeiter->kennung . "'";
        }

        $sql .= ' AND ( ' . implode(' OR ', $sqlStatements) . ') ';
      }

      
      if (isset($this->fromobject->aussenstelle)) {
        
        if (is_array($this->fromobject->aussenstelle) && !empty($this->fromobject->aussenstelle)) {
          
          $dbwaussenstellen = new DBWorkHLAussenstellen(1, true);
          $aussenstellenBezeichnerAry = $dbwaussenstellen->getAussenstellenBezeichner();
          $sql .= ' AND ( ';
          foreach ($this->fromobject->aussenstelle as $aussenstellenId) {
            
            if (isset($aussenstellenBezeichnerAry[$aussenstellenId])) {
              $sql .= ' (' . $praeffix . "aussenst like '" . $this->dboperation->getString4Sql($aussenstellenBezeichnerAry[$aussenstellenId]) . "') OR ";
            }
          }
          $sql = StringOperations::substr($sql, 0, -3) . ') ';
        } elseif (trim($this->fromobject->aussenstelle) != '' && trim($this->fromobject->aussenstelle) != '-1') {
          
          $dbwaussenstellen = new DBWorkHLAussenstellen(1, true);
          $aussenstelle = $dbwaussenstellen->getAussenstelle($this->fromobject->aussenstelle);
          $sql .= ' AND (' . $praeffix . "aussenst like '" . $this->dboperation->getString4Sql($aussenstelle->bezeichner) . "')";
        }
      }

      
      if ((isset($this->justNoAusfall) && $this->justNoAusfall == true)) {
        $sql .= ' AND (' . $praeffix . "ausfall<>'J' OR " . $praeffix . 'ausfall IS Null)';
      } elseif (isset($this->fromobject->kursausfall) && trim($this->fromobject->kursausfall) != '') {
        if ($this->fromobject->kursausfall == self::FILTER_KURSAUSFALL_OHNE_AUSGEFALLENE) {
          $sql .= ' AND (' . $praeffix . "ausfall<>'J' OR " . $praeffix . 'ausfall IS Null)';
        } elseif ($this->fromobject->kursausfall == self::FILTER_KURSAUSFALL_NUR_AUSGEFALLENE) {
          $sql .= ' AND ' . $praeffix . "ausfall='J' ";
        }
      }
      
      if ((isset($this->justNoAbschluss) && $this->justNoAbschluss == true)) {
        $sql .= ' AND (' . $praeffix . "abschluss<>'J' OR " . $praeffix . 'abschluss IS Null)';
      } elseif (isset($this->fromobject->kursabschluss) && trim($this->fromobject->kursabschluss) != '') {
        if ($this->fromobject->kursabschluss == self::FILTER_KURSABSCHLUSS_OHNE_ABGESCHLOSSENE) {
          $sql .= ' AND (' . $praeffix . "abschluss<>'J' OR " . $praeffix . 'abschluss IS Null)';
        } elseif ($this->fromobject->kursabschluss == self::FILTER_KURSABSCHLUSS_NUR_ABGESCHLOSSENE) {
          $sql .= ' AND ' . $praeffix . "abschluss='J' ";
        }
      }

      if (isset($this->keineHauptkurse) && $this->keineHauptkurse) {
        $sql .= $this->getSearchSqlKeinHauptkursPart($praeffix);
      }
      
      if (isset($this->fromobject->kursstatus) && trim($this->fromobject->kursstatus) != '') {
        $sql .= self::getSQLString4KursStatus($this->fromobject->kursstatus, $this->datetimeop, $praeffix);
      }

      
      if (isset($this->fromobject->realkursstatus) && is_array($this->fromobject->realkursstatus)) {
        $realkursstatus = $this->fromobject->realkursstatus;
        
        if (in_array('', $realkursstatus)) {
          $realkursstatus[] = ' ';
        } elseif (in_array(' ', $realkursstatus)) {
          $realkursstatus[] = '';
        }

        if ($this->dboperation->dbparser->servertyp === \DBTypeEnum::ORACLE && in_array('', $realkursstatus)) {
          
          $realkursstatus = array_filter($realkursstatus, static function($str) {
            return $str !== '';
          });

          $realkursstatus = implode(',', $this->dboperation->getEnclosedStrings4Sql($realkursstatus));

          
          $sql .= ' AND (' . $praeffix . 'abschluss IS NULL OR ' . $this->dboperation->dbparser->isNull($praeffix . 'abschluss', "' '") . ' IN (' . $realkursstatus . '))';
        } else {
          $realkursstatus = implode(',', $this->dboperation->getEnclosedStrings4Sql($realkursstatus));

          
          $sql .= ' AND ' . $this->dboperation->dbparser->isNull($praeffix . 'abschluss', "' '") . ' IN (' . $realkursstatus . ')';
        }
      }

      
      if (isset($this->fromobject->ort) && !is_array($this->fromobject->ort)) {
        $this->fromobject->ort = array(trim($this->fromobject->ort));
      }

      if (isset($this->fromobject->ort)) {
        $arrayfilter = array_filter($this->fromobject->ort); 
        if (!empty($arrayfilter)) {
          $orte = $this->fromobject->ort;

          $noSearchFields = explode(',', $this->objsettings_web->excludeKursSearchFieldsOrt);
          $searchFields = $this->filterFieldsByTable($this->getAvailableSearchFieldsOrt());

          $ortString = '';
          $doSubselect = false;
          foreach ($orte as $ort) {
            $ort = $this->dboperation->getString4Sql($ort);
            
            
            $temp = $this->getSqlStringForExcludedFields($praeffix, 'ko', $ort, $noSearchFields, $searchFields, DBWorkBASysSettings::isAimHeilbronn());
            

            if ($temp == ' AND ()') {
              $startFlag = 2; 
            } else {
              $startFlag = 1; 
              $doSubselect = true;
            }
            $temp = StringOperations::substr(rtrim($temp), 0, -1);
            
            $temp .= $this->_buildSearchString($ort, $praeffix . 'ort', $startFlag, false, DBWorkBASysSettings::isAimHeilbronn());

            $ortString .= StringOperations::substr($temp, 6) . ' OR '; 
          }

          $ortString = StringOperations::substr(rtrim($ortString), 0, -3); 
          $ortString = '(' . $ortString . ')';
          $ortString = str_replace('_', ' ', $ortString);

          if ($doSubselect) {
            $sql .= ' AND k.nr IN ( SELECT k.nr FROM kurse k LEFT OUTER JOIN kursorte ko ON k.ort = ko.mktext WHERE ' . $ortString . ' )';
          } else {
            $sql .= ' AND ' . $ortString . ' ';
          }
          
        }
      }

      
      if (isset($this->fromobject->kursort) && trim($this->fromobject->kursort) != '' && trim($this->fromobject->kursort) != '-1') {
        $dbwkursort = new DBWorkKursorte();
        $ort = $dbwkursort->getKursort($this->fromobject->kursort);
        $sql .= ' AND (' . $praeffix . "ort like '%" . $ort->mktext . "%')";
      }

      
      if (isset($this->fromobject->suche_bezirk) && is_array($this->fromobject->suche_bezirk)) {
        
        if (DBWorkBASysSettings::isSalzburgBildungswerk() && in_array('Online', $this->fromobject->suche_bezirk)) {
          $sql .= ' AND (SELECT k2.webinar FROM kurse2 k2 WHERE k2.nr = ' . $praeffix . 'nr) IN (\'O\', \'H\') ';
        } else {
          $sql .= ' AND (' . $praeffix . 'ort IN (SELECT ko.mktext FROM kursorte ko WHERE ko.bezirk IN ( '
            . StringOperations::convertArray2InList($this->fromobject->suche_bezirk) . ')))';
        }
      }

      
      if (isset($this->fromobject->kurse2->kirchengemeinde) && trim($this->fromobject->kurse2->kirchengemeinde) != '') {
        if (!is_array($this->fromobject->kurse2->kirchengemeinde)) {
          $kirchengemeindeVals = array($this->fromobject->kurse2->kirchengemeinde);
        } else {
          $kirchengemeindeVals = $this->fromobject->kurse2->kirchengemeinde;
        }
        $inList = StringOperations::convertArray2InList($this->dboperation->getStrings4Sql($kirchengemeindeVals));
        $sql .= ' AND (SELECT k2.kirchengemeinde FROM kurse2 k2 WHERE k2.nr = ' . $praeffix . 'nr) IN (' . $inList . ') ';
      }

      if (isset($this->fromobject->suche_ortPlz) && is_array($this->fromobject->suche_ortPlz) && count($this->fromobject->suche_ortPlz) > 0) {
        $sql .= ' AND (' . $praeffix . 'ort IN (SELECT ko.mktext FROM kursorte ko WHERE ' . $this->dboperation->dbparser->substring('ko.ort', 1, 5) . ' IN ( '
          . StringOperations::convertArray2InList($this->dboperation->getStrings4Sql($this->fromobject->suche_ortPlz)) . ')))';
      }

      
      if (isset($this->fromobject->suche_ortUmkreisPlz) && trim($this->fromobject->suche_ortUmkreisPlz) != ''
              && isset($this->fromobject->suche_ortUmkreisRadius) && $this->fromobject->suche_ortUmkreisRadius > 0) {
        $sql .= $this->getSearchSql_ortUmkreisPlz();
      }

      if (isset($this->fromobject->ortProgheft1) && trim($this->fromobject->ortProgheft1) != '') {
        $dbwkursort = new DBWorkKursorte(1, true);
        $dbwkursort->newSearch();
        $dbwkursort->fromobject->progheft1 = $this->fromobject->ortProgheft1;
        $orte = $dbwkursort->getKursorte();
        $mktexte = ArrayOperations::mapAttribut($orte, 'mktext');
        $sqlMkTexte = "'" . implode("', '", $mktexte) . "'";
        $sql .= ' AND (' . $praeffix . 'ort in (' . $sqlMkTexte . '))';
      }

      
      if (isset($this->fromobject->fachbereich) && trim($this->fromobject->fachbereich) != '' && trim($this->fromobject->fachbereich) != '-1') {
        $fachbereich = $this->dboperation->getString4Sql($this->fromobject->fachbereich);
        $sql .= ' AND (' . $praeffix . "fachb like '" . $fachbereich . "%')";
      }

      
      $sql .= $this->zeitraumDatumEinschraenken($praeffix);

      
      
      if (isset($this->fromobject->beginn) && (!is_numeric($this->fromobject->beginn))) {
        $this->fromobject->beginn = str_replace(array(':', '-', ' '), '', $this->fromobject->beginn);
      }
      if (isset($this->toobject->beginn) && (!is_numeric($this->toobject->beginn))) {
        $this->toobject->beginn = str_replace(array(':', '-', ' '), '', $this->toobject->beginn);
      }
      $sqlPart = $this->dboperation->dbparser->castReplace2Int('REPLACE (' . $praeffix . 'beginn, \'.\', \'\')', '');
      if ((isset($this->fromobject->beginn) && trim($this->fromobject->beginn) != '')
         && (isset($this->toobject->beginn) && trim($this->toobject->beginn) != '')
        && ($this->fromobject->beginn > $this->toobject->beginn)) {
        $beginnFrom = $this->dboperation->getString4Sql($this->fromobject->beginn);
        $beginnTo = $this->dboperation->getString4Sql($this->toobject->beginn);
        $sql .= ' AND (' . $sqlPart . '>=' . $beginnFrom . ' OR ' . $sqlPart . '<=' . $beginnTo . ')';
      } else {
        if (isset($this->fromobject->beginn) && trim($this->fromobject->beginn) != '') {
          $beginnFrom = $this->dboperation->getString4Sql($this->fromobject->beginn);
          $sql .= ' AND ' . $sqlPart . ' >= ' . $beginnFrom;
        }
        if (isset($this->toobject->beginn) && trim($this->toobject->beginn) != '') {
          $beginnTo = $this->dboperation->getString4Sql($this->toobject->beginn);
          $sql .= ' AND ' . $sqlPart . ' <= ' . $beginnTo;
        }
      }
      
      if (isset($this->fromobject->ende) && (!is_numeric($this->fromobject->ende))) {
        $this->fromobject->ende = str_replace(array(':', '-', ' '), '', $this->fromobject->ende);
      }
      if (isset($this->toobject->ende) && (!is_numeric($this->toobject->ende))) {
        $this->toobject->ende = str_replace(array(':', '-', ' '), '', $this->toobject->ende);
      }
      $sqlPart = $this->dboperation->dbparser->castReplace2Int('REPLACE (' . $praeffix . 'ende, \'.\', \'\')', '');
      if ((isset($this->fromobject->ende) && trim($this->fromobject->ende) != '')
         && (isset($this->toobject->ende) && trim($this->toobject->ende) != '')
        && ($this->fromobject->ende > $this->toobject->ende)) {
        $endeFrom = $this->dboperation->getString4Sql($this->fromobject->ende);
        $endeTo = $this->dboperation->getString4Sql($this->toobject->ende);
        $sql .= ' AND (' . $sqlPart . '>=' . $endeFrom . ' OR ' . $sqlPart . '<=' . $endeTo . ')';
      } else {
        if (isset($this->fromobject->ende) && trim($this->fromobject->ende) != '') {
          $endeFrom = $this->dboperation->getString4Sql($this->fromobject->ende);
          $sql .= ' AND ' . $sqlPart . ' >= ' . $endeFrom;
        }
        if (isset($this->toobject->ende) && trim($this->toobject->ende) != '') {
          $endeTo = $this->dboperation->getString4Sql($this->toobject->ende);
          $sql .= ' AND ' . $sqlPart . ' <= ' . $endeTo;
        }
      }
      
      if (isset($this->fromobject->wochentage_suche) && $this->fromobject->wochentage_suche != '') {
        $sql .= SQLOperations::BuildWochentagSearchSQL($this->fromobject->wochentage_suche, $this->beruecksichtigeKurstagRaumanzeige);
      }

      if (isset($this->fromobject->zeitraum_suche) && $this->fromobject->zeitraum_suche != '') {
        $entries = explode('|', $this->fromobject->zeitraum_suche);
        $sql .= SQLOperations::GetZeitraumSQLString($entries, $praeffix);
      }
      if (isset($this->fromobject->zeitraum_suche2) && $this->fromobject->zeitraum_suche2 != '') {
        $sql .= SQLOperations::GetZeitraumSQLString(array($this->fromobject->zeitraum_suche2), $praeffix);
      }

      
      if (isset($this->fromobject->veranart_suche) && $this->fromobject->veranart_suche != '' && $this->fromobject->veranart_suche != -1) {
        if (is_array($this->fromobject->veranart_suche)) {
          $veranart_suche = implode(',', $this->dboperation->getStrings4Sql($this->fromobject->veranart_suche));
        } else {
          $veranart_suche = $this->dboperation->getString4Sql($this->fromobject->veranart_suche);
        }
        $sql .= ' and (' . $praeffix . 'kart_key in (' . StringOperations::convertCommaSeparatedList2InList($veranart_suche) . '))';
      }

      
      if (isset($this->fromobject->veranart_suche_negativ) && $this->fromobject->veranart_suche_negativ != '' && $this->fromobject->veranart_suche_negativ != -1) {
        $veranart_suche_negativ = $this->dboperation->getString4Sql($this->fromobject->veranart_suche_negativ);
        $sql .= ' and (' . $praeffix . 'kart_key not in (' . StringOperations::convertCommaSeparatedList2InList($veranart_suche_negativ) . '))';
      }

      
      if ((isset($this->fromobject->id) && (trim($this->fromobject->id) != '') || is_array($this->fromobject->id))
           || (isset($this->toobject->id) && (trim($this->toobject->id) != '') || is_array($this->fromobject->id))) {
        $sql .= $this->getSearchSQLKnrPart($praeffix);
      }

      
      if (isset($this->fromobject->dozentenbewerbung) && ($this->fromobject->dozentenbewerbung != '' || $this->toobject->dozentenbewerbung != '')) {
        $dozentenbewerbungFrom = $this->dboperation->getInt4Sql($this->fromobject->dozentenbewerbung);
        $dozentenbewerbungTo = $this->dboperation->getInt4Sql($this->toobject->dozentenbewerbung);
        $sql .= ' AND (' . $praeffix . "dozentenbewerbung BETWEEN \n
                      " . $dozentenbewerbungFrom . " AND \n
                      " . $dozentenbewerbungTo . ") \n";
      }

      
      if (isset($this->fromobject->stichworte) && trim($this->fromobject->stichworte) != '') {
        $stichwort = $this->dboperation->getString4Sql($this->fromobject->stichworte);

        $sql .= ' AND ( ';

        $availableOrtFieldsCount = count($this->getAvailableSearchFieldsStichwort());
        $excludedOrtFieldsCount = count(explode(',', $this->objsettings_web->excludeKursSearchFieldsStichwort));

        
        if ($excludedOrtFieldsCount == $availableOrtFieldsCount) {
          $sql .= StringOperations::substr($this->_buildSearchString($stichwort, 'k.kurzbez'), 4);
        } else {
          $noSearchFields = explode(',', $this->objsettings_web->excludeKursSearchFieldsStichwort);
          $searchFields = $this->filterFieldsByTable($this->getAvailableSearchFieldsStichwort());
          $sql .= StringOperations::substr($this->getSqlStringForExcludedFields($praeffix, 'kaa', $stichwort, $noSearchFields, $searchFields), 4);
        }

        
        if (isset($this->fromobject->stichwortFindetSchlagwort) && $this->fromobject->stichwortFindetSchlagwort === true) {
          $swPart = $this->getSqlPartStichwort($stichwort);
          if (StringOperations::strlen($swPart) > 0) {
            $sql .= ' OR' . $swPart;
          }
        }

        
        $sql .= $this->_extendSearchSqlStichwort($praeffix);
        $sql .= ') ';
      }

      
      
      
      if ((!isset($this->fromobject->stichwortFindetSchlagwort) || !$this->fromobject->stichwortFindetSchlagwort)
              && (isset($this->fromobject->schlagwort) && $this->fromobject->schlagwort != '')) {
        $schlagwort = $this->dboperation->getString4Sql($this->fromobject->schlagwort);

        $sql .= ' AND' . $this->getSqlPartStichwort($schlagwort);
      }

      
      if (isset($this->fromobject->kurs_zielg) && $this->fromobject->kurs_zielg != '') {
        $kurs_zielg = $this->dboperation->getString4Sql($this->fromobject->kurs_zielg);
        $sql .= ' AND kurs_zielg = \'' . $kurs_zielg . '\'';
      }
      
      if (isset($this->fromobject->altervon) && $this->fromobject->altervon != '') {
        
        $alter = $this->dboperation->getString4Sql($this->fromobject->altervon);
        $sql .= ' AND (altervon <= ' . $alter . ' AND alterbis >= ' . $alter . ') ';
      }
      
      if (isset($this->fromobject->zusatztexte, $this->toobject->zusatztexte)) {
        
        if (isset($this->fromobject->zusatztexte->monataltvon) && $this->fromobject->zusatztexte->monataltvon != '') {
          $alterVon = $this->dboperation->getInt4Sql($this->fromobject->zusatztexte->monataltvon);
          if ($alterVon > 0) {
            $alterVonJahr = floor($alterVon / 12);
            $sql .= " AND ((monataltvon > 0 AND $alterVon <= monataltvon) OR (altervon > 0 AND $alterVonJahr <= altervon)) ";
          }
        }
        if (isset($this->toobject->zusatztexte->monataltbis) && $this->toobject->zusatztexte->monataltbis != '') {
          $alterBis = $this->dboperation->getInt4Sql($this->toobject->zusatztexte->monataltbis);
          if ($alterBis > 0) {
            $alterBisJahr = floor($alterBis / 12);
            $sql .= " AND ((monataltbis > 0 AND monataltbis <= $alterBis) OR (alterbis > 0 AND alterbis <= $alterBisJahr)) ";
          }
        }
      }
      
      if (isset($this->fromobject->stundenanz) && $this->fromobject->stundenanz != '') {
        
        $stundenanzFrom = $this->dboperation->getString4Sql($this->fromobject->stundenanz);
        $sql .= ' AND stundenanz >= ' . $this->mathop->stringToFloat($stundenanzFrom) . ' ';
      }
      if (isset($this->toobject->stundenanz) && $this->toobject->stundenanz != '') {
        
        $stundenanzTo = $this->dboperation->getString4Sql($this->toobject->stundenanz);
        $sql .= ' AND stundenanz <= ' . $this->mathop->stringToFloat($stundenanzTo) . ' ';
      }
      
      
      
      
      for ($i = 0; $i < 17; $i++) {
        if ($i != 10) {
          $varname = 'kursauswahl' . $i;
          $fuzzy = $this->isKursauswahlSearchFuzzy();
          $equalityOperator = ($fuzzy ? 'like' : '=');
          if (isset($this->fromobject->{$varname}) && $this->fromobject->{$varname} != '') {
            if (($i == 2 || $i == 3) && DBWorkBASysSettings::isJenaWitelo()) {
              $sql .= $this->getSearchSqlWitelo($i);
            } else {
              $addition = ($fuzzy ? '%' : '');
              if (is_array($this->fromobject->{$varname})) {
                $tmp = '';
                foreach ($this->fromobject->{$varname} as $value) {
                  $tmp .= " OR $varname $equalityOperator '" . $this->dboperation->getString4Sql($value)
                          . ($value !== '' ? $addition : '')
                          . '\'';
                }
                $sql .= ' AND (' . StringOperations::substr($tmp, 4) . ') ';
              } else {
                $sql .= " AND $varname $equalityOperator '" . $this->dboperation->getString4Sql($this->fromobject->{$varname}) . $addition . '\' ';
              }
            }
          } elseif (isset($this->toobject->{$varname})) {
            $sql .= " AND NOT $varname $equalityOperator '" . $this->dboperation->getString4Sql($this->toobject->{$varname}) . '\' ';
          }
        }
      }

      $sql .= $this->getSearchSQLCategory($praeffix);

      
      if ($this->isHauptDozentVorhanden()) {
        $wheres = array();
        if (isset($this->fromobject->hauptdozent->name) && $this->fromobject->hauptdozent->name != '') {
          $name = $this->dboperation->getString4Sql($this->fromobject->hauptdozent->name);
          $wheres[] = "LOWER(d.name) like LOWER('%" . $name . "%')";
        }

        if (isset($this->fromobject->hauptdozent->vorname) && $this->fromobject->hauptdozent->vorname != '') {
          $vorname = $this->dboperation->getString4Sql($this->fromobject->hauptdozent->vorname);
          $wheres[] = "LOWER(d.vorname) like LOWER('%" . $vorname . "%')";
        }

        if (isset($this->fromobject->hauptdozent->namevorname) && ($this->fromobject->hauptdozent->namevorname != '')) {
          $namevorname = $this->dboperation->getString4Sql($this->fromobject->hauptdozent->namevorname);
          $query_namevorname = str_replace(' ', '%', $namevorname);
          $wheres[] = $this->dboperation->dbparser->concat(array("'%'", 'LOWER(d.vorname)', "'%'", 'LOWER(d.name)', "'%'")) . " like  LOWER('%" . $query_namevorname . "%')"
                  . ' OR ' . $this->dboperation->dbparser->concat(array("'%'", 'LOWER(d.name)', "'%'", 'LOWER(d.vorname)', "'%'")) . " like LOWER('%" . $query_namevorname . "%')";
        }

        if (isset($this->fromobject->hauptdozent->id) && ($this->fromobject->hauptdozent->id != '')) {
          $dozid = $this->fromobject->hauptdozent->id;
          if (is_numeric($dozid) && $dozid > -1) {
            $wheres[] = 'dk.dozent =  ' . $dozid;
          } elseif (is_array($dozid)) {
            $dozids = $this->dboperation->getInts4Sql($dozid);
            if (count($dozids) > 0) {
              $wheres[] = 'dk.dozent in (' . StringOperations::convertArray2InList($dozids) . ')';
            }
          }
        }
        if (count($wheres) > 0) {
          $where = implode(' AND ', $wheres);
          
          $sql .= ' AND k.nr IN ( SELECT dk.kurs FROM doz_kurs dk ' . $this->_extendSearchSqlDozentEinschraenkung($where, true, 'dk.', 'd.') . ' AND ' . $this->dboperation->dbparser->isStringEmptyOrNull('dk.art') . ' )';
        }
      }
      
      if (isset($this->fromobject->teilnehmername) && $this->fromobject->teilnehmername != '') {
        $query_namevorname = $this->dboperation->getString4Sql($this->fromobject->teilnehmername);
        $query_namevorname = str_replace(' ', '%', $query_namevorname);

        $sql .= ' AND ' . $praeffix . 'nr IN ('
                  . 'SELECT be.knr '
                  . 'FROM belegung be '
                  . 'LEFT JOIN teilnehm t ON be.tnr = t.nr '
                  . ' WHERE (1=1) ';

        
        if (isset($this->fromobject->tnstatus) && $this->fromobject->tnstatus != '') {
          $sql .= DBWorkBelegung::getSearchSQL4TNStatus('be.', $this->fromobject->tnstatus);
        }

        $sql .= ' AND ('
                . $this->dboperation->dbparser->concat(array("'%'", 'LOWER(t.vorname)', "'%'", 'LOWER(t.name)', "'%'")) . " like  LOWER('%" . $query_namevorname . "%')"
                . ' OR ' . $this->dboperation->dbparser->concat(array("'%'", 'LOWER(t.name)', "'%'", 'LOWER(t.vorname)', "'%'")) . " like LOWER('%" . $query_namevorname . "%'))";

        $sql .= ') ';
      }
      
      if (isset($this->fromobject->ma_planend_name) && $this->fromobject->ma_planend_name != '') {
        $query_namevorname = $this->dboperation->getString4Sql($this->fromobject->ma_planend_name);
        $query_namevorname = str_replace(' ', '%', $query_namevorname);

        
        $sql .= ' AND (' . $this->dboperation->dbparser->isNull($praeffix . 'hpm', '\'\'') . ' = \'' . $query_namevorname . '\'';
        
        $sql .= ' OR ' . $praeffix . 'hpm IN ('
                  . 'SELECT b.kennung '
                  . 'FROM benutzer b '
                  . 'WHERE '
                  . '('
                  . $this->dboperation->dbparser->concat(array("'%'", 'LOWER(b.vorname)', "'%'", 'LOWER(b.name)', "'%'")) . " like  LOWER('%" . $query_namevorname . "%')"
                  . ' OR ' . $this->dboperation->dbparser->concat(array("'%'", 'LOWER(b.name)', "'%'", 'LOWER(b.vorname)', "'%'")) . " like LOWER('%" . $query_namevorname . "%'))";

        $sql .= ')) ';
      }

      
      if (isset($this->fromobject->beendeteKurseAusblenden) && $this->fromobject->beendeteKurseAusblenden != '') {
        $sql .= self::getSQLBeendeteKurseAusblenden($this->fromobject->beendeteKurseAusblenden, $this->datetimeop, $this->fromobject->beendeteKurseOffset ?? 0, $praeffix);
      }

      if (isset($this->fromobject->kurse2->webinar) && $this->fromobject->kurse2->webinar != '') {
        if (!is_array($this->fromobject->kurse2->webinar)) {
          $webinarVals = array($this->fromobject->kurse2->webinar);
        } else {
          $webinarVals = $this->fromobject->kurse2->webinar;
        }
        if (in_array('O', $webinarVals) && $this->objsettings_web->kfsOnlineFindetAuchHybrid) {
          $webinarVals[] = 'H';
        }
        if (in_array('O', $webinarVals) && $this->objsettings_web->kfsOnlineFindetAuchBlendedLearning) {
          $webinarVals[] = 'B';
        }
        $inList = StringOperations::convertArray2InList($this->dboperation->getStrings4Sql($webinarVals));
        $sql .= ' AND (SELECT k2.webinar FROM kurse2 k2 WHERE k2.nr = ' . $praeffix . 'nr) IN (' . $inList . ') ';
      }

      if (isset($this->fromobject->kurse2->newsl_weitergabe) && is_bool($this->fromobject->kurse2->newsl_weitergabe)) {
        $check = $this->dboperation->dbparser->isNull('k2.newsl_weitergabe', '\'F\'');

        $sql .= ' AND ' . $praeffix . 'nr IN (SELECT k2.nr FROM kurse2 k2 WHERE' . $check . ' = \'W\')';
      }

      
      if ((isset($this->fromobject->ausgebuchteKurseAusblenden) && $this->fromobject->ausgebuchteKurseAusblenden)
          || (isset($this->fromobject->suche_nurNichtVolleKurse) && $this->fromobject->suche_nurNichtVolleKurse)) {
        $sqlPartMaxKinderTnKi = '';
        if (System::isKuferSql5()) {
          $sqlPartMaxKinderTnKi = 'AND (' . $this->dboperation->dbparser->isNull($praeffix . 'kinder_max', 0) . ' = 0 '
                  . 'OR (SELECT count(b.id) FROM belegung b WHERE b.knr = ' . $praeffix . 'nr AND ' . $this->anmshrink
                  . ' ' . DBWorkBelegung::getKindBelegungenSQLPart() . ') < ' . $praeffix . 'kinder_max) ';

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

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

      
      if (isset($this->fromobject->mandant)
              && ((is_string($this->fromobject->mandant) && trim($this->fromobject->mandant) != '') || is_array($this->fromobject->mandant))) {
        if (is_array($this->fromobject->mandant)) {
          $mandanten = $this->fromobject->mandant;
        } else {
          $mandanten = array($this->fromobject->mandant);
        }
        $mandantenClear = array();
        foreach ($mandanten as $mandant) {
          $clearedMandant = $this->dboperation->getInt4Sql($mandant);
          if (is_numeric($clearedMandant)) {
            $mandantenClear[] = $clearedMandant;
          }
        }
        if (count($mandantenClear) > 0) {
          $sql .= ' AND ' . $praeffix . 'mandant in (' . $this->stringop->convertArray2InList($mandantenClear) . ') ';
        }
      }

      if (isset($this->fromobject->suche_faecher_ids) && is_array($this->fromobject->suche_faecher_ids) && !empty($this->fromobject->suche_faecher_ids)) {
        $filterKursFach = \Kufer\Web\Classes\Dbfilter\KurseFaecherSQLFilter::create();
        $filterKursFach->inFachId($this->fromobject->suche_faecher_ids);
        $dbwKursFach = \DBWorkKursFach::getDefaultInstance($this->dbtyp);

        $sql .= ' AND ' . $praeffix . 'nr IN (' . $dbwKursFach->findByFilterQuery($filterKursFach)->select(':table:.knr')->getSQLForInnerQuery() . ') ';
      }

      if (isset($this->fromobject->neuerkurs) && $this->fromobject->neuerkurs != '') {
        if ($this->fromobject->neuerkurs === true || $this->fromobject->neuerkurs != 'F') {
          $cmp = 'W';
        } else {
          $cmp = 'F';
        }
        $sql .= ' AND ' . $praeffix . 'neuerkurs = \'' . $cmp . '\'';
      }

      if (isset($this->fromobject->gebuehr) && $this->fromobject->gebuehr != '') {
        $amount = MathOperations::stringToFloat($this->fromobject->gebuehr);
        if (is_numeric($amount)) {
          $sql .= ' AND ' . $praeffix . 'gebuehr >= ' . $this->dboperation->getString4Sql($amount) . ' ';
        }
      }
      if (isset($this->toobject->gebuehr) && $this->toobject->gebuehr != '') {
        $amount = MathOperations::stringToFloat($this->toobject->gebuehr);
        if (is_numeric($amount)) {
          $sql .= ' AND ' . $praeffix . 'gebuehr <= ' . $this->dboperation->getString4Sql($amount) . ' ';
        }
      }
      if (isset($this->fromobject->suche_kennzeichen) && $this->fromobject->suche_kennzeichen != '') {
        $criterion = $this->fromobject->suche_kennzeichen;
        if (is_numeric($criterion)) {
          $criterion = array($criterion);
        }
        if (is_array($criterion)) {
          $ids = $this->dboperation->getInts4Sql($criterion);
          if (count($ids) > 0) {
            $sql .= ' AND k.nr IN ( SELECT ka.nr FROM kennz_ad ka WHERE ka.typ=\'K\' AND ka.kennz IN (' . $this->stringop->convertArray2InList($ids) . ') )';
          }
        }
      }
      if (isset($this->toobject->suche_kennzeichen) && $this->toobject->suche_kennzeichen != '') {
        $criterion = $this->toobject->suche_kennzeichen;
        if (is_numeric($criterion)) {
          $criterion = array($criterion);
        }
        if (is_array($criterion)) {
          $ids = $this->dboperation->getInts4Sql($criterion);
          if (count($ids) > 0) {
            $sql .= ' AND k.nr IN ( SELECT ka.nr FROM kennz_ad ka WHERE ka.typ=\'K\' AND ka.kennz NOT IN (' . $this->stringop->convertArray2InList($ids) . ') )';
          }
        }
      }
      if (isset($this->fromobject->suche_anyKennzeichen) && $this->fromobject->suche_anyKennzeichen) {
        $sql .= ' AND k.nr IN ( SELECT ka.nr FROM kennz_ad ka WHERE ka.typ=\'K\')';
      }
      if (isset($this->fromobject->suche_noKennzeichen) && $this->fromobject->suche_noKennzeichen) {
        $sql .= ' AND k.nr not IN ( SELECT ka.nr FROM kennz_ad ka WHERE ka.typ=\'K\')';
      }
      if (isset($this->fromobject->suche_exceptKennzeichen) && $this->fromobject->suche_exceptKennzeichen) {
        $inStatement = '(' . implode(', ', $this->fromobject->suche_exceptKennzeichen) . ')';
        $sql .= ' AND k.nr not IN ( SELECT ka.nr FROM kennz_ad ka WHERE ka.typ=\'K\' and ka.kennz in ' . $inStatement . ')';
      }
      if (isset($this->fromobject->suche_kennzeichenAnd) && $this->fromobject->suche_kennzeichenAnd != '') {
        if ($this->dboperation->getDBName() === 'MYSQL') {
          $criterion = $this->fromobject->suche_kennzeichenAnd;
          if (is_numeric($criterion)) {
            $criterion = array($criterion);
          }
          if (is_array($criterion)) {
            $ids = $this->dboperation->getInts4Sql($criterion);
            if (count($ids) > 0) {
              sort($ids);
              $separator = ' ';
              $groupConcat = $this->dboperation->dbparser->concat(array("'$separator'", $this->dboperation->dbparser->groupConcat('ka.', 'kennz', 'ka.kennz', $separator . $separator), "'$separator'"));
              $match = '%' . $separator . implode($separator . '%' . $separator, array_unique($ids)) . $separator . '%';
              $sql .= ' AND k.nr IN ( SELECT ka.nr FROM kennz_ad ka WHERE ka.typ=\'K\' group by ka.nr having '
                      . $this->dboperation->dbparser->caseInsensitiveCompOperation('like', '', $groupConcat, $match)
                              . ')';
              
            }
          }
        }
        
      }
      if (isset($this->fromobject->kzielg_key) && $this->fromobject->kzielg_key != '') {
        $sql .= ' AND ' . $praeffix . 'kzielg_key = "' . $this->dboperation->getString4Sql($this->fromobject->kzielg_key) . '"';
      }

      if (($this->fromobject->kurstage_gestern_und_heute ?? false) || ($this->fromobject->kurstage_gestern_und_heute_dozent ?? false)) {
        $kurstageFilter = \Kufer\Web\Classes\Dbfilter\KurstageSQLFilter::create();
        $kurstageFilter->all(function(\Kufer\Web\Classes\Dbfilter\KurstageSQLFilter $filter) {
          if ($this->fromobject->kurstage_gestern_und_heute_dozent ?? false) {
            try {
              $filter->equalsDozentId((int)\SessionOperations::getCurrentWebToolsDozent()->id);
            } catch (Exception $ex) {
              
            }
          }

          $filter->any(function(\Kufer\Web\Classes\Dbfilter\KurstageSQLFilter $filter) {
            $today = \DateTimeOperations::getDateNow();
            $filter->equalsDatum($today);
            $filter->equalsDatum(\DateTimeOperations::addDays($today, -1));
          });
        });

        $dbwKurstage = \DBWorkKurseTage::getDefaultInstance($this->dboperation->dbnr);
        $builder = $dbwKurstage->findByFilterQuery($kurstageFilter)->select('kt.knr');

        $sql .= ' AND ' . $praeffix . 'nr IN (' . $builder->getSQLForInnerQuery() . ')';
      }

      if ($this->fromobject->kurstage_aktiv_lehrend_dozent ?? false) {
        try {
          $kurstageFilter = \Kufer\Web\Classes\Dbfilter\KurstageSQLFilter::create();
          $kurstageFilter->equalsDozentId((int)\SessionOperations::getCurrentWebToolsDozent()->id);

          $dbwKurstage = \DBWorkKurseTage::getDefaultInstance($this->dboperation->dbnr);
          $builder = $dbwKurstage->findByFilterQuery($kurstageFilter)->select('kt.knr');

          $sql .= ' AND ' . $praeffix . 'nr IN (' . $builder->getSQLForInnerQuery() . ')';
        } catch (Exception $ex) {
          
        }
      }
    }
    
    
    
    
    if (isset($this->fromobject->elearningKursID) && $this->fromobject->elearningKursID != '') {
      
      if (DBWorkBASysSettings::isBlak()) {
        $sql .= $this->getSQLSpecialKursebyFlagsInKursId(array('E'), 'elearningKursID');
      } else {
        
        $sql .= ' AND ' . $praeffix . 'elearningKursID > 0';
      }
    }

    if (isset($this->fromobject->other_kriterien) && $this->fromobject->other_kriterien != '') {
      switch ($this->fromobject->other_kriterien) {
        case 'infuture':
          $sql .= ' AND ' . $this->getSelectQueryBuilder()
            ->where($praeffix . 'beginn_dat >= :datenow:')
            ->getSQLCondition();
          break;
        case 'inpast':
          $sql .= ' AND ' . $this->getSelectQueryBuilder()
              ->where($praeffix . 'ende_dat < :datenow:')
              ->getSQLCondition();

          break;
      }
    }

    
    
    if (isset($this->fromobject->blak_kriterien) && $this->fromobject->blak_kriterien != '') {
      switch ($this->fromobject->blak_kriterien) {
        case 'elearning_kurse':
          
          
          
          if (DBWorkBASysSettings::isBlak()) {
            $sql .= $this->getSQLSpecialKursebyFlagsInKursId(array('E'), 'blak_kriterien');
          } else {
            
            $sql .= ' AND ' . $praeffix . 'elearningKursID > 0';
          }
          break;
          
        case 'lernzielkontrollen':
          $sql .= $this->getSQLSpecialKursebyFlagsInKursId(array('L'), 'blak_kriterien');
          break;
          
        case 'webinare':
          $sql .= $this->getSQLSpecialKursebyFlagsInKursId(array('W'), 'blak_kriterien');
          break;
          
        case 'praesenz':
          $excludeFlags = array('E', 'L', 'W');
          
          $sql .= $this->getSQLSpecialKursebyFlagsInKursId($excludeFlags, 'blak_kriterien', true);
          break;

        case '':
        default:
          break;
      }
    } 

    $sql .= $this->getSearchSQLKursOrte($praeffix);
    $sql .= $this->getSearchSQLVeranstalter($praeffix);

    return $sql;
  }

  

  protected function zeitraumDatumEinschraenken($praeffix)
  {
    $datumsbezeichnung = $praeffix . 'beginn_dat';
    if (isset($this->fromobject->kurstagedatum)) {
      $datumsbezeichnung = 't.datum';
      $this->fromobject->beginn_dat = $this->fromobject->kurstagedatum;
    }

    if ($this->fromobject->beginn_dat instanceof DateTimeObj) {
      $this->fromobject->beginn_dat = $this->fromobject->beginn_dat->getDate();
    }
    if ($this->toobject->beginn_dat instanceof DateTimeObj) {
      $this->toobject->beginn_dat = $this->toobject->beginn_dat->getDate();
    }
    $sql = '';
    if (isset($this->fromobject->beginn_dat) && !DateTimeOperations::isDateTimeEmptyOrDefault(trim($this->fromobject->beginn_dat)) && $this->datetimeop->isValidDate($this->fromobject->beginn_dat)) {
      $sql .= ' AND (' . $datumsbezeichnung . '>=' . $this->getSQLValue('beginn_dat', $this->fromobject) . ')';
    }
    if (isset($this->toobject->beginn_dat) && !DateTimeOperations::isDateTimeEmptyOrDefault(trim($this->toobject->beginn_dat)) && $this->datetimeop->isValidDate($this->toobject->beginn_dat)) {
      $sql .= ' AND (' . $datumsbezeichnung . '<=' . $this->getSQLValue('beginn_dat', $this->toobject) . ')';
    }
    if (isset($this->fromobject->ende_dat) && !DateTimeOperations::isDateTimeEmptyOrDefault(trim($this->fromobject->ende_dat)) && $this->datetimeop->isValidDate($this->fromobject->ende_dat)) {
      $sql .= ' AND (' . $praeffix . 'ende_dat>=' . $this->getSQLValue('ende_dat', $this->fromobject) . ')';
    }
    if (isset($this->toobject->ende_dat) && !DateTimeOperations::isDateTimeEmptyOrDefault(trim($this->toobject->ende_dat)) && $this->datetimeop->isValidDate($this->toobject->ende_dat)) {
      $sql .= ' AND (' . $praeffix . 'ende_dat<=' . $this->getSQLValue('ende_dat', $this->toobject) . ')';
    }

    return $sql;
  }

  private function getSearchSQLWitelo(int $index) : string
  {
    $result = '';
    
    if ($index == 2) {
      $witeloVal = $this->dboperation->getString4Sql($this->fromobject->kursauswahl2);
      if (StringOperations::strtoupper($this->fromobject->kursauswahl2) === 'VS') {
        $result = " AND kursauswahl2 ='VS' ";
      } else {
        
        $result = ' AND (kursauswahl2<=' . $this->dboperation->dbparser->cast2Int($witeloVal) . " or kursauswahl2 = 'VS') and (kursauswahl3 >= " . $this->dboperation->dbparser->cast2Int($witeloVal) . " and kursauswahl3 <> 'VS') ";
      }
    }

    return $result;
  }

  private function getSearchSQLKursOrte($praeffix)
  {
    $sql = '';
    if (isset($this->fromobject->dbwkursorte) && $this->fromobject->dbwkursorte instanceof DBWorkKursorte && isset($this->fromobject->dbwkursorte->fromobject)) {
      $mkTexte = $this->fromobject->dbwkursorte->getKursortMkTexte();
      if (count($mkTexte) == 0) {
        $sql .= ' AND 1=0 ';
      } else {
        $sql .= ' AND ' . $praeffix . 'ort IN(' . StringOperations::convertArray2InList($mkTexte) . ')';
      }
    }

    return $sql;
  }

  private function getSearchSQLVeranstalter($praeffix)
  {
    $sql = '';
    if (isset($this->fromobject->dbwDozentenVeranstalter) && $this->fromobject->dbwDozentenVeranstalter instanceof DBWorkDozenten && isset($this->fromobject->dbwDozentenVeranstalter->fromobject)) {
      $dozIds = $this->fromobject->dbwDozentenVeranstalter->getDozentenIdsbyFilter();

      if (count($dozIds) == 0) {
        $sql .= ' AND 1=0 ';
      } else {
        $typenVeranstalter = \Kufer\KuferTools\Veranstalter\Utils\VeranstalterUtils::getTypenVeranstalter();
        $where = 'dk.dozent IN (' . StringOperations::convertArray2InList($dozIds, true) . ') AND dk.typ IN (' . StringOperations::convertArray2InList($typenVeranstalter) . ')';
        $sql .= ' AND ' . $praeffix . 'nr IN (SELECT dk.kurs FROM doz_kurs dk ' . $this->_extendSearchSqlDozentEinschraenkung($where, true, 'dk.', 'd.') . ' AND ' . $this->dboperation->dbparser->isStringEmptyOrNull('dk.art') . ')';
      }

      $sql .= ' ';
    }

    return $sql;
  }

  private function getSearchSql_ortUmkreisPlz()
  {
    $result = '';
    if (!$this->checkTableExists('web_geodb_dist')) {
      Logger::logg('Wollte eine Umkreissuche durchführen, aber es gibt die Distanzen Tabelle nicht!', 7001);
    } else {
      try {
        $dbwDist = DBWorkWebGeodbDist::getDefaultInstance();
        $plzs = $dbwDist->getNeighbourPlzWithinRadius($this->fromobject->suche_ortUmkreisPlz, $this->fromobject->suche_ortUmkreisRadius);
        $sqlOrtLike = array();
        foreach ($plzs as $plz) {
          $sqlOrtLike[] = " ko.ort like '$plz%'";
        }
        if (count($sqlOrtLike) > 0) {
          $result .= ' AND k.nr IN ( SELECT k2.nr FROM kurse k2 LEFT OUTER JOIN kursorte ko ON k2.ort = ko.mktext WHERE ' . implode(' or ', $sqlOrtLike) . ' )';
        } else {
          $result .= ' AND 1 = 0';
        }
      } catch (InvalidArgumentException $ex) {
        Logger::logg('Fehler bei Umkreissuche per PLZ: ' . $ex->getMessage(), 7002);
      }
    }

    return $result;
  }

  
  public static function getSQLString4KursStatus($pKursStatus, $pDateTimeOps, $pPraefix = 'k.')
  {
    $sql = '';
    
    $dateNow = $pDateTimeOps->getFormatDate4Server(DateTimeOperations::getDateNow());
    switch ($pKursStatus) {
      case self::FILTER_KURSSTATUS_NICHT_BEGONNENE:
        $sql .= ' AND (' . $pPraefix . 'beginn_dat>' . $dateNow . ')';
        break;
      case self::FILTER_KURSSTATUS_LAUFENDE_BY_DATUM:
        $sql .= ' AND (' . $pPraefix . 'beginn_dat<=' . $dateNow . ' AND ' . $pPraefix . 'ende_dat>=' . $dateNow . ')';
        break;
      case self::FILTER_KURSSTATUS_BEENDETE:
        $sql .= ' AND (' . $pPraefix . 'ende_dat<' . $dateNow . ' OR ' . $pPraefix . 'ende_dat=' . $pDateTimeOps->getFormatDate4Server(DEFAULTDATE) . ')';
        break;
      case self::FILTER_KURSSTATUS_LAUFENDE_BY_ABSCHLUSS:
        $sql .= ' AND (' . $pPraefix . 'abschluss = \'L\') ';
        break;
      default:
        break;
    } 

    return $sql;
  }
  

  public static function getSQLBeendeteKurseAusblenden($pEinstellung, $pDateTimeOps, $pOffset = 0, $pPraefix = 'k.')
  {
    $sql = '';
    if ($pEinstellung != '') {
      switch ($pEinstellung) {
        case 'ende_dat':
          
          $sql .= ' AND (' . $pPraefix . 'ende_dat>=' . $pDateTimeOps->getFormatDate4Server(DateTimeOperations::getDateNow())
                         . ' OR ' . $pPraefix . 'ende_dat=' . $pDateTimeOps->getFormatDate4Server(DEFAULTDATE)
                         . ')';
          break;
        case 'kurstag_ende_dat':
          
          $sql .= ' AND ( (SELECT max(datum) FROM kurstage kurst WHERE kurst.knr = ' . $pPraefix . 'nr) >= ' . $pDateTimeOps->getFormatDate4Server(DateTimeOperations::getDateNow()) . ')';
          break;
        case 'beginn_dat':
          
          $date = DateTimeOperations::getDateNow();
          if (isset($pOffset) && is_numeric($pOffset)) {
            $date = DateTimeOperations::addDays($date, (-1) * $pOffset);
          }
          $sql .= ' AND (' . $pPraefix . 'beginn_dat>=' . $pDateTimeOps->getFormatDate4Server($date) . ' OR ' . $pPraefix . 'beginn_dat=' . $pDateTimeOps->getFormatDate4Server(DEFAULTDATE) . ')';
          break;
        case 'beginn_oder_endedat':
          
          $date = DateTimeOperations::getDateNow();
          if (isset($pOffset) && is_numeric($pOffset)) {
            $date = DateTimeOperations::addDays($date, (-1) * $pOffset);
          }
          $sql .= ' AND ((' . $pPraefix . 'beginn_dat>=' . $pDateTimeOps->getFormatDate4Server($date)
                        . ' OR ' . $pPraefix . 'beginn_dat=' . $pDateTimeOps->getFormatDate4Server(DEFAULTDATE) . ')'
                  . ' AND (' . $pPraefix . 'ende_dat>=' . $pDateTimeOps->getFormatDate4Server(DateTimeOperations::getDateNow())
                        . ' OR ' . $pPraefix . 'ende_dat=' . $pDateTimeOps->getFormatDate4Server(DEFAULTDATE) . '))';
          break;

        default:
          break;
      }
    }

    return $sql;
  }

  

  
  public function getSQLSpecialKursebyFlagsInKursId($pFlags, $pFromAttrStr, $pExclude = false)
  {
    $sql = '';
    
    if (!is_array($pFlags) || count($pFlags) == 0) {
      return $sql;
    }
    
    $pos = 9;
    if (DBWorkBASysSettings::isKesselLenting()) {
      $pos = 6; 
    } elseif (DBWorkBASysSettings::isBlak()) {
      $pos = 9;
    }

    $flagsStr = \StringOperations::convertArray2InList($pFlags);

    return ' AND ' . ($pExclude ? 'NOT ' : '') . '(' . $this->dboperation->dbparser->substring('k . nr', $pos + 1, 1) . ' IN (' . $flagsStr . '))';
  }

  

  protected function getSqlPartStichwort($pStichWort)
  {
    $useOr = true;
    $startMustMatch = false;
    $groupConcat = false;

    if (DBWorkBASysSettings::isWesslingCcg()) {
      $useOr = false;
    }

    if (!$this->objsettings_web->kfsStichwortNichtUebergreifendSuchen) {
      $groupConcat = true;
      $useOr = false;
    }

    
    if ($this->dboperation->getDBName() !== DBTypeEnum::MYSQL && $this->dboperation->getDBName() !== DBTypeEnum::MYSQLI) {
      $groupConcat = false;
    }

    $where = $this->_buildSearchString($pStichWort, 'swo.stichwort', 2, $useOr, $startMustMatch, $groupConcat);

    
    
    
    

    if ($groupConcat) {
      if (SECOND_QUERY_FOR_STICHWORT) {
        
        $swSql = 'SELECT swo.knr FROM stichwor swo group by knr having ( ' . $where . ' )';
        $this->dboperation->clear();
        $this->dboperation->query($swSql);
        $numrows = $this->dboperation->rowcount();
        if ($numrows > 0) {
          $knrs = array();
          for ($i = 0; $i < $numrows; $i++) {
            $this->dboperation->selectrow($i);
            $knrs[] = $this->dboperation->fieldbyname('knr');
          }
          $sql = ' k.nr IN ( ' . StringOperations::convertArray2InList($knrs) . ')';
        } else {
          $sql = '';
        }
      } else {
        $sql = ' k.nr IN( '
                . 'SELECT swo.knr FROM stichwor swo group by knr having ( ' . $where . ' )) ';
      }
    } else {
      $sql = ' k.nr IN( '
              . 'SELECT swo.knr FROM stichwor swo WHERE ' . $where . ') ';
    }

    return $sql;
  }

  
  protected function isKursauswahlSearchFuzzy()
  {
    return true;
  }

  
  protected function _extendSearchSqlDozentEinschraenkung($pSearchWhere, $pDozentenMustBeJoined, $pPraefixDozkurs, $pPraefixDozent)
  {
    assert($pSearchWhere != '', '$pSearchWhere darf NIE leer sein');
    $result = '';
    if ($pDozentenMustBeJoined) {
      $praefix = StringOperations::substr($pPraefixDozent, 0, -1); 
      $result .= " LEFT JOIN dozenten {$praefix} ON {$pPraefixDozent}nr = {$pPraefixDozkurs}dozent";
    }

    return $result . ' WHERE ' . $pSearchWhere;
  }

  
  public function getAvailableSearchFieldsStichwort()
  {
    return array(
        'info' => 'Info',
        'webinfo' => 'Ankündigungstext Web',
        'nr_anzeige' => 'Kursnummer',
        'kurzbez' => 'Kurstitel',
        'titel' => 'Kurstitel fürs Programmheft',
        'aussenst' => 'Außenstellenbezeichnung',
        'material' => 'Mitzubringen/Material',
        'vorspann' => 'Vorspann',
        'nachspann' => 'Nachspann',
        'websuchbegriffe' => 'Web-Suchbegriffe',
        
        'kurs_zielg' => 'Zielgruppe (Kursstamm 3. Seite)',
        'kaa.Voraussetzungen' => 'Voraussetzungen',
        'kaa.ziel' => 'Ziel',
        'kaa.anmelde' => 'Anmeldebedingungen',
        'kaa.zielgruppe' => 'Zielgruppe (Kursstamm Planung/Progh.)',
        'kaa.zielgruppe_bf' => 'Zielgruppe (barrierefrei)',
        'kaa.titel_bf' => 'Programmhefttitel (barrierefrei)',
        'kaa.kurzbez_bf' => 'Titel (barrierefrei)',
        'kaa.vorspann_bf' => 'Vorspann (barrierefrei)',
        'kaa.nachspann_bf' => 'Nachspann (barrierefrei)',
        'kaa.infoberatungstext' => 'Info und Beratung',
        'kaa.ziel_bf' => 'Ziel (barrierefrei)'
    );
  }

  
  public function getAvailableSearchFieldsOrt()
  {
    return array(
        'ko.ort' => 'PLZ / Ort',
        'ko.strasse' => 'Straße',
        
        
        'ko.progheft1:ko.progheft2' => 'Eintrag ins Programmheft',
        'ko.bez' => 'Kurzbezeichnung',
        'ko.schule' => 'Gebäude',
        'ko.aussenstelle' => 'Außenstelle'
    );
  }

  
  public function getSqlStringForExcludedFields($pStdPraeffix, $pPraeffix, $pSearchString, $pNoSearchFields, $pSearchFields, $pStartMustMatch = false)
  {
    $isFirst = true;
    $sql = ' AND (';
    $noSearchFields = array();
    $multipleNoSearchFields = array();

    
    
    
    foreach ($pNoSearchFields as $noSearchField) {
      if (StringOperations::strstr($noSearchField, ':')) {
        $multipleNoSearchFields = array_merge(explode(':', $noSearchField), $multipleNoSearchFields);
      } else {
        array_push($noSearchFields, $noSearchField);
      }
    }
    $allNoSearchFields = array_merge($noSearchFields, $multipleNoSearchFields);

    foreach ($pSearchFields as $field => $tableKey) {
      $startFlag = ($isFirst) ? 2 : 1;
      $tableField = $field;
      if ($tableKey != '') {
        $tableField = $tableKey . '.' . $field;
      }
      if (!in_array($tableField, $allNoSearchFields)) {
        if ($tableKey == '') {
          $sql .= $this->_buildSearchString($pSearchString, $pStdPraeffix . $field, $startFlag, false, $pStartMustMatch);
          $isFirst = false;
        } elseif ($tableKey == $pPraeffix) {
          $sql .= $this->_buildSearchString($pSearchString, $tableKey . '.' . $field, $startFlag, false, $pStartMustMatch);
          $isFirst = false;
        }
      }
    }
    $sql .= ')';

    return $sql;
  }

  
  protected function _extendSearchSqlStichwort($pStdPraeffix)
  {
    $sql = '';
    if (isset($this->fromobject, $this->fromobject->stichworte) && trim($this->fromobject->stichworte) != ''
        && isset($this->fromobject->stichwortFindetDoz) && $this->fromobject->stichwortFindetDoz === true) {
      $sdInfo = '';
      $stichwort = $this->dboperation->getString4Sql($this->fromobject->stichworte);
      
      $useOr = !DBWorkBASysSettings::isWesslingCcg();
      $sdName = $this->_buildSearchString($stichwort, 'd.name', 2, $useOr);
      $sdVorName = $this->_buildSearchString($stichwort, 'd.vorname', 1, $useOr);
      if ($this->fromobject->suche_dozinfo == true) {
        $sdInfo = $this->_buildSearchString($stichwort, 'd.info', 1, $useOr);
      }
      
      
      $searchWhere = ' ( ' . $sdName . $sdVorName . $sdInfo . ' )
                      AND (dk.typ = \'D\' OR dk.typ = \'\') '
                      . ' AND ' . $this->dboperation->dbparser->isStringEmptyOrNull('dk.art') 
                     . ' ';
      $searchWhere = $this->_extendSearchSqlDozentEinschraenkung($searchWhere, false, 'dk.', 'd.');
      $searchWhere .= ') ';

      $sql .= ' OR ' . $pStdPraeffix . 'nr IN( '
      . 'SELECT dk.kurs
       FROM doz_kurs dk
       JOIN dozenten d ON dk.dozent = d.nr ' . $searchWhere . ' ';
    }
    if (isset($this->fromobject, $this->fromobject->stichworte) && trim($this->fromobject->stichworte) != ''
            && isset($this->fromobject->stichwortFindetKategorienbezeichnung) && $this->fromobject->stichwortFindetKategorienbezeichnung) {
      $dbwcat = new DBWorkCategories(1, true);
      $sql .= $this->handleSearchSqlCategory(array($this->fromobject->stichworte), $dbwcat, $pStdPraeffix, 'OR');
    }

    return $sql;
  }

  
  public function filterFieldsByTable($pSearchAry)
  {
    $returnAry = array();
    foreach ($pSearchAry as $searchField => $searchName) {
      
      if (StringOperations::strstr($searchField, ':')) {
        $completeColumnParts = explode(':', $searchField);
        foreach ($completeColumnParts as $completeColumnPart) {
          if (StringOperations::strstr($completeColumnPart, '.')) {
            $columnParts = explode('.', $completeColumnPart);
            $returnAry[$columnParts[1]] = $columnParts[0];
          } else {
            $returnAry[$searchField] = '';
          }
        }
      } else {
        if (StringOperations::strstr($searchField, '.')) {
          $columnParts = explode('.', $searchField);
          $returnAry[$columnParts[1]] = $columnParts[0];
        } else {
          $returnAry[$searchField] = '';
        }
      }
    }

    return $returnAry;
  }

  protected function handleSearchSqlCategory($kategorieAry, $dbwcat, $praeffix = '', $operator = 'AND')
  {
    $i = 0;
    $sql = '';
    if (is_array($kategorieAry)) {
      if (!isset($kategorieAry[0])) {
        $i = 1;
      }
      while (isset($kategorieAry[$i])) {
        if ((!is_numeric($kategorieAry[$i]) && !empty($kategorieAry[$i])) || $kategorieAry[$i] > 0) {
          $swortsql = $this->getSearchSQLCategoryRekursiv($praeffix, $kategorieAry[$i], $dbwcat, 0);
          $titel = '';
          
          if (DBWorkBASysSettings::isFIHH() && isset($kategorieAry[$i]) && !is_numeric($kategorieAry[$i])) {
            $kategorieEscaped = $this->dboperation->getString4Sql($kategorieAry[$i]);
            
            $titel .= ' OR ( ';
            $titel .= $this->_buildSearchString($kategorieEscaped, $praeffix . 'kurzbez', 2);
            $titel .= $this->_buildSearchString($kategorieEscaped, $praeffix . 'titel', 1);
            $titel .= ') ';
          }

          if ($swortsql != '') {
            $sql .= ' ' . $operator . ' ( ' . $praeffix . 'nr in (' . $swortsql . ') ' . $titel . ') ';
          } else {
            $sql .= ' ' . $operator . ' ( ' . $praeffix . "nr in ('') " . $titel . ') ';
          }
        }
        $i++;
      }
    }

    return $sql;
  }

  
  public function getSearchSQLCategory($pPraeffix)
  {
    if ($this->categorystring != '') {
      return $this->categorystring;
    }

    $praeffix = $this->dboperation->getString4Sql($pPraeffix);
    $sql = '';
    $dbwcat = new DBWorkCategories($this->dbtyp, true);

    
    if (isset($this->fromobject->kategorie)) {
      $sql .= $this->handleSearchSqlCategory($this->fromobject->kategorie, $dbwcat, $praeffix);
    }
    
    if (isset($this->fromobject->sonderkategorie)
        && is_array($this->fromobject->sonderkategorie)
        && count(array_filter($this->fromobject->sonderkategorie, function($id) { return $id != -1;})) > 0) {
      $swortsql = '';
      $unionOrIntersectionOperator = $this->searchIntersectSpecialCategories ? ' AND ' : ' OR ';
      foreach ($this->fromobject->sonderkategorie as $katid) {
        if ($katid != -1) {
          $swortsql .= ($swortsql !== '' ? $unionOrIntersectionOperator : '') . $dbwcat->getItemIdRekursivInStatement($katid, 0, $praeffix . 'nr');
        }
      }
      $sql .= ' AND (' . $swortsql . ')';
    } else {
      
      
      if (isset($this->fromobject->sonderkategorie) && !is_array($this->fromobject->sonderkategorie) && ($this->fromobject->sonderkategorie > 0)) {
        $swortsql = $dbwcat->getItemIdRekursivInStatement($this->fromobject->sonderkategorie, 0, $praeffix . 'nr');
        $sql .= ' AND ' . $swortsql;
      }
    }

    $this->categorystring = $sql;

    return $sql;
  }

  
  public function getSearchSQLCategoryRekursiv($praeffix, $pKatid, $dbwcat, $count)
  {
    return $dbwcat->getItemIdSearchSQLCategoryRekursiv($pKatid, $count);
  }

  
  public function _buildSearchString($expression, $field, $startFlag = 0, $pUseOr = false, $pStartMustMatch = false, $pGroupConcat = false)
  {
    $matches = array();
    
    $replacementEscapedBackslash = '{ESCAPED_BACKSLASH}';
    $expression = str_replace("\\'", $replacementEscapedBackslash, $expression);
    $matchCount = preg_match_all('/(\\\\"|")([^"\\\\]*)(\\\\"|")/', $expression, $matches);
    $matches = $matches[2]; 
    if ($matchCount > 0) {
      $expression = preg_replace('/(\\\\"|")([^"\\\\]*)(\\\\"|")/', '', $expression);
    }

    $parts = explode(' ', $expression); 
    foreach ($parts as $part) { 
      if (trim($part) != '') { 
        array_push($matches, $part);
      }
    }

    
    $andOr = ($pUseOr) ? ' OR  ' : ' AND ';

    $str = '';
    foreach ($matches as $match) {
      if ($match != '') {
        $search = $pStartMustMatch ? $match . '%' : '%' . $match . '%';
        if ($pGroupConcat) {
          $str .= $andOr . $this->dboperation->dbparser->caseInsensitiveGroupConcatLike('', $field, $search);
        } else {
          $str .= $andOr . $this->dboperation->dbparser->caseInsensitiveLike('', $field, $search);
        }
      }
    }

    switch ($startFlag) {
      case 0: 
        if ($str != '') {
          $str = ' AND (' . StringOperations::substr($str, 5) . ')';
        }
        break;
      case 1: 
        if ($str != '') {
          $str = ' OR (' . StringOperations::substr($str, 5) . ')';
        }
        break;
      case 2: 
        if ($str != '') {
          $str = '(' . StringOperations::substr($str, 5) . ')';
        }
        break;
    }

    return str_replace($replacementEscapedBackslash, "\\'", $str);
  }

  
  public function getKurs($pKurs_Id, $pRefreshcache = false)
  {
    $kurs_id = $this->dboperation->getString4Sql($pKurs_Id);
    if ($kurs_id == '') {
      return $this->getNewObject();
    }

    if (!$pRefreshcache && array_key_exists($kurs_id, self::$_cache_KursCache)) {
      return clone self::$_cache_KursCache[$kurs_id];
    }

    $this->dboperation->clear();
    $builder = $this->getAllQuery()
        ->and('k.nr = :kursId:')
        ->paramString('kursId', $kurs_id);
    $this->dboperation->query($builder->getSQL());

    self::$_cache_KursCache[$kurs_id] = $this->getFirstRow($pRefreshcache);

    return unserialize(serialize(self::$_cache_KursCache[$kurs_id]));
  }

  
  public function getKurse(?array $pKursnummernArray = null)
  {
    $sqlstr = $this->_getKursSQLPart($this->select);

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

    return $this->getAllRows();
  }

  
  public function getKursIds()
  {
    $sqlstr = $this->_getKursSQLPart('k.nr');

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

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

  
  private function _getKursSQLPart($pSelect)
  {
    return "SELECT $pSelect \n FROM kurse k \n "
              
              . "LEFT OUTER JOIN kurse_aa kaa on k.nr=kaa.nr \n "
              . 'WHERE (1 = 1)' . $this->getSearchSQL('k.') . " \n"
               . $this->getCompleteOrderByDefault('nr', 'k');
  }

  public function getKurseWithFilterQuery(): \SelectQueryBuilder
  {
    $result = $this->getAllQuery();
    $searchSql = $this->getSearchSQL('k.');
    if ($searchSql !== '') {
      $result = $result->and('1=1')->addToWhere($searchSql);
    }

    return $result;
  }

  
  public function getBuchbareKurseIds(): array
  {
    $builder = $this->getKurseWithFilterQuery()
      ->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;
  }

  
  public function getAllKursnummern()
  {
    $builder = $this->getKurseWithFilterQuery()
      ->select('k.nr');

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

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

  public function getAllQuery(): SelectQueryBuilder
  {
    return $this->getSelectQueryBuilder()
        ->select($this->select . ($this->selectFreidef === '' ? '' : ', ' . $this->selectFreidef))
        ->from('kurse', 'k')
        ->leftOuterJoin('kurse_aa kaa', 'k.nr = kaa.nr')
        ->leftOuterJoin('kurse_freideffelder kfreidef', 'k.nr = kfreidef.nr')
        ->orderBySrcCode($this->getCompleteOrderByDefault('nr', 'k'))
        ->paramRaw('table', 'k');
  }

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

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

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

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

    return $this->getAllRows();
  }

  
  public function findIdsByFilter(\Kufer\Web\Classes\Dbfilter\KurseSQLFilter $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\KurseSQLFilter $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 getKurseByKursnummern($pKursnummern, $pRefreshCache = false)
  {
    
    if (!is_array($pKursnummern) || empty($pKursnummern)) {
      return array();
    }

    $where = ' ' . $this->createInStatement('k.nr', $pKursnummern) . ' ';
    $where .= $this->getSearchSQL('k.');

    $sqlstr = 'SELECT ' . $this->select . " \n FROM kurse k \n "
              
              . "LEFT OUTER JOIN kurse_aa kaa on k.nr=kaa.nr \n ";
    $sqlstr .= 'WHERE ' . $where . " \n"
               . $this->getCompleteOrderByDefault('nr', 'k');

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

    return $this->getAllRows($pRefreshCache);
  }

  
  public function getKurseByKursnummernKeepOrder($pKursnummern, $pRefreshCache = false)
  {
    $pKursnummern = \array_filter($pKursnummern);
    if (!is_array($pKursnummern) || count($pKursnummern) === 0) {
      return array();
    }

    $knrToFetch = KurseCache::getKeysNotInCache($pKursnummern, $pRefreshCache);

    if (count($knrToFetch) > 0) {
      $builder = $this->getAllQuery()
        ->and('k.nr IN (:kurseIds:)')
        ->paramStringArray('kurseIds', $knrToFetch);

      $this->dboperation->clear();
      $this->dboperation->query($builder->getSQL());
      $allRows = $this->getAllRows();
      KurseCache::fillDatamoduleInCache($allRows);
    }

    return KurseCache::getByKeys($pKursnummern);
  }

  
  public function getDozentenNummern()
  {
    $sql = 'SELECT distinct dk.dozent
            FROM kurse k
            JOIN doz_kurs dk on dk.kurs = k.nr
            LEFT JOIN kurse_aa kaa ON k.nr = kaa.nr
            WHERE (dk.typ=\'D\' OR ' . $this->dboperation->dbparser->isStringEmptyOrNull('dk.typ') . ') ' . $this->getSearchSQL('k.');

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

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

    return $ids;
  }

  
  public function existsKurse()
  {
    $sql = "SELECT count(*) AS anzahl FROM kurse k \n"
           . 'WHERE 1=1 '
            . $this->getSearchSQL('k.');

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

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

  
  public function getAllSemester()
  {
    $sql = 'select distinct(semester) as semester from kurse;';
    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $result = array();

    $rowcount = $this->dboperation->rowcount();
    for ($i = 0; $i < $rowcount; $i++) {
      $this->dboperation->selectrow($i);
      $semester = $this->getDatamodulValue('semester');
      if (trim($semester) != '') {
        $result[] = $semester;
      }
    }

    return $result;
  }

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

  
  public function getParentCategoryIdsForKurs($pKnr)
  {
    $knr = $this->dboperation->getString4Sql($pKnr);
    $sql = "select distinct(l1.id) id from kategorien l1 join kategorien l2 on l1.id = l2.vaterid where l2.id in (select kategorie_id from kurse_kategorien where knr = '$knr')";

    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $result = array();
    $rowcount = $this->dboperation->rowcount();
    for ($i = 0; $i < $rowcount; $i++) {
      $this->dboperation->selectrow($i);
      $result[] = $this->dboperation->fieldbyname('id');
    }

    return $result;
  }

  
  public function getCurrentTnKartenKurs()
  {
    $sql = 'SELECT ' . $this->select . ' FROM kurse k WHERE k.typ=\'P\' AND'
        . ' k.beginn_dat <= ' . $this->datetimeop->getFormateDate4ServerNow() . ' AND'
        . ' k.ende_dat >= ' . $this->datetimeop->getFormateDate4ServerNow()
        . ' ORDER BY k.nr asc';
    $this->dboperation->clear();
    $this->dboperation->query($sql);

    return $this->getFirstRow();
  }

  
  public function updateKurs($kurs, $pDmzBetrieb = false, $pDontProtocol = false)
  {
    $orgkurs = $this->getKurs($kurs->id, true);
    $this->updateObject($kurs, $orgkurs, 'kurse', 'id', 'U', $pDontProtocol, $pDmzBetrieb);
    $kurs->zusatztexte->id = $kurs->id;
    $this->dbzusatztexte->updateZusatzTexte($kurs->zusatztexte, $pDmzBetrieb, $pDontProtocol);
    $kurs->kurse2->id = $kurs->id;
    $this->dbwKurse2->updateKurse2($kurs->kurse2, $pDmzBetrieb, $pDontProtocol);
    $this->dbwKurseFreidefFelder->updateFreidefFeld($kurs, $pDmzBetrieb, $pDontProtocol);

    $this->setActionIDs();
    $this->getKurs($kurs->id, true);
  }
}