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


class DBWorkAnwesenheit extends DBWork
{
  protected static $_cache_Anwesenheiten = array();

  
  protected $tableNameExtra;

  public function __construct($pDBTyp = 1, $pNewInstance = false)
  {
    parent::__construct($pDBTyp, $pNewInstance);
    $this->select = 'id as anwid, abelegid, aktid, astatus, abemerkung, afehlminanfang, afehlminende, aprogresscheck, ahausaufgaben, aamscode';
    $this->tableNameExtra = 'anw_extra';
  }

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

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

  
  public function getCurrentRow($pRefreshCache = false)
  {
    $tmp = $this->getNewObject();

    $tmp->id = $this->getDatamodulValue('anwid');
    $tmp->astatus = StringOperations::strtoupper($this->getDatamodulValue('astatus'));
    if ($tmp->astatus != '') {
      $tmp->abemerkung = $this->getDatamodulValue('abemerkung');
    }
    $tmp->aprogresscheck = $this->getDatamodulValue('aprogresscheck');
    $tmp->ahausaufgaben = $this->getDatamodulValue('ahausaufgaben');
    $tmp->abelegid = $this->getDatamodulValue('abelegid');
    $tmp->aktid = $this->getDatamodulValue('aktid');
    $tmp->afehlminanfang = $this->getDatamodulValue('afehlminanfang');
    $tmp->afehlminende = $this->getDatamodulValue('afehlminende');
    $tmp->bearbeiter = $this->getDatamodulValue('bearbeiter');
    $tmp->bearbdatum = $this->getDatamodulValue('bearbdatum');
    $tmp->aamscode = $this->getDatamodulValue('aamscode');

    return $tmp;
  }

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

  
  public function getAnwesenheit($pId)
  {
    if (is_null($pId) || $pId === '') {
      return $this->getNewObject();
    }
    $id = $this->dboperation->getInt4Sql($pId);
    $sql = 'select ' . $this->select . ' from ' . $this->getTableName() . ' where id=' . $id . ';';
    $this->dboperation->clear();
    $this->dboperation->query($sql);

    return $this->getFirstRow();
  }

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

  
  public function getAnwesenheitenByBelegungUndKurstage($pBelegId, $pKurstage)
  {
    if (empty($pKurstage)) {
      return array();
    }

    $kurstageValues = \array_values($pKurstage);
    if ($kurstageValues[0] instanceof \Kurstag) {
      $kurstageIds = \ArrayOperations::mapAttribut($kurstageValues, 'id');
    } else {
      $kurstageIds = $kurstageValues;
    }

    $builder = new \SelectQueryBuilder($this->dboperation);
    $builder->select($this->select)
        ->from($this->getTableName())
        ->and('abelegid = :belegid:')
        ->and('aktid IN (:kurstageids:)')
        ->paramInt('belegid', $pBelegId)
        ->paramIntArray('kurstageids', $kurstageIds);

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

    return $this->getAllRows();
  }

  
  public function getAnwesenheitenByBelegungenIdsUndKurstageIds(array $belegungenIds, array $kurstageIds): array
  {
    if (empty($kurstageIds) || empty($belegungenIds)) {
      return array();
    }

    $builder = new \SelectQueryBuilder($this->dboperation);
    $builder->select($this->select)
      ->from($this->getTableName())
      ->and('abelegid IN (:belegids:)')
      ->and('aktid IN (:kurstageids:)')
      ->paramIntArray('belegids', $belegungenIds)
      ->paramIntArray('kurstageids', $kurstageIds);

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

    return $this->getAllRows();
  }

  
  public function getAnwesenheitenUndExtrasByBelegungUndKurstage($pBelegId, $pKurstage)
  {
    $anwesenheiten = $this->getAnwesenheitenByBelegungUndKurstage($pBelegId, $pKurstage);

    $result = array();
    $dbwAnwesenheitenExtras = new \DBWorkAnwesenheitExtra();
    foreach ($anwesenheiten as $anw) {
      $result[] = $anw;
      $anwExtras = $dbwAnwesenheitenExtras->getAnwesenheitenExtraForAnwesenheit($anw->getVarValue('id'));
      $anw->_eanws = $anwExtras;
      foreach ($anwExtras as $anwExtra) {
        $result[] = $anwExtra;
      }
    }

    return $result;
  }

  
  public function getAnwesenheitByBelegungKurstagQuery($pBelId, $pKtId): \SelectQueryBuilder
  {
    if ('' === $pBelId || '' === $pKtId) {
      throw new Exception('Ungültiges Argument für $pBelId oder $pKtId');
    }

    return $this->getAllQuery()
      ->and('abelegid = :belid:')
      ->and('aktid = :ktid:')
      ->paramInt('belid', $pBelId)
      ->paramInt('ktid', $pKtId);
  }

  
  public function getAnwesenheitByBelegungKurstagOrNull($pBelId, $pKtId)
  {
    if ('' === $pBelId || '' === $pKtId) {
      throw new Exception('Ungültiges Argument für $pBelId oder $pKtId');
    }

    $builder = $this->getAnwesenheitByBelegungKurstagQuery($pBelId, $pKtId);
    $this->dboperation->clear();
    $this->dboperation->query($builder->getSQL());

    return $this->getFirstRowOrNull();
  }

  
  public function getAnwesenheitByBelegungKurstag($pBelId, $pKtId)
  {
    if ('' === $pBelId || '' === $pKtId) {
      throw new Exception('Ungültiges Argument für $pBelId oder $pKtId');
    }

    $builder = $this->getAnwesenheitByBelegungKurstagQuery($pBelId, $pKtId);
    $this->dboperation->clear();
    $this->dboperation->query($builder->getSQL());

    return $this->getFirstRow();
  }

  
  public function insertAnwesenheit($pAnwesenheit, $pStatus5 = false)
  {
    $pAnwesenheit->id = $this->insertRecord($this->getTableName(), 'id', array('astatus', 'abelegid', 'aktid'), false);
    $this->saveChanging($this->getTableName(), '', '', $pAnwesenheit->id, 'id', 'I', $pStatus5);
    $orgobj = $this->getAnwesenheit($pAnwesenheit->id);
    $this->updateObject($pAnwesenheit, $orgobj, $this->getTableName(), 'id', 'A', false, $pStatus5);
    $this->setActionIDs();

    return $pAnwesenheit->id;
  }

  
  public function insertOrUpdate($pAnwesenheit, $pStatus5 = false)
  {
    $origAnwesenheit = $this->getAnwesenheitByBelegungKurstag($pAnwesenheit->abelegid, $pAnwesenheit->aktid);
    
    if ($origAnwesenheit != null && !$origAnwesenheit->isEmpty()) {
      $pAnwesenheit->setId($origAnwesenheit->id);
      $this->updateAnwesenheit($pAnwesenheit);

      return $pAnwesenheit->id;
    } else {
      return $this->insertAnwesenheit($pAnwesenheit, $pStatus5);
    }
  }

  
  public function updateExtraAnwsWithTable(\DBWorkAnwesenheitExtra $dbwAnwExtra, $pAnwesenheit, $pStatus5 = false)
  {
    $origAnwsExtra = empty($pAnwesenheit->id) ? array() : $dbwAnwExtra->getAnwesenheitenExtraForAnwesenheit($pAnwesenheit->id);
    
    $origAnwsExtraIds = \ArrayOperations::mapAttribut($origAnwsExtra, 'id');

    $anzahlAnwesenheiten = $pAnwesenheit->getCountExtraAnwesenheiten();
    $extraAnwIdAry = array();
    $newExtraAnws = array();
    $deleteExtraAnws = array();
    $updateExtraAnws = array();
    for ($i = 0; $i < $anzahlAnwesenheiten; $i++) {
      $anwExtra = $pAnwesenheit->getExtraAnwesenheit($i);
      if (empty($anwExtra->id) || !in_array($anwExtra->id, $origAnwsExtraIds)) {
        $newExtraAnws[] = $anwExtra;
      } else {
        $updateExtraAnws[] = $anwExtra;
      }
    }

    $updateExtraAnwsIds = \ArrayOperations::mapAttribut($updateExtraAnws, 'id');
    foreach ($origAnwsExtra as $origAnwExtra) {
      if (!in_array($origAnwExtra->id, $updateExtraAnwsIds)) {
        $deleteExtraAnws[] = $origAnwExtra;
      }
    }

    foreach ($updateExtraAnws as $updateExtraAnw) {
      if ($dbwAnwExtra->updateAnwesenheitExtraWithAnwesenheitId($updateExtraAnw, $pAnwesenheit->id, $pStatus5)) {
        $extraAnwIdAry[] = $updateExtraAnw->id;
      } else {
        $newExtraAnws[] = $updateExtraAnw; 
      }
    }

    foreach ($deleteExtraAnws as $deleteExtraAnw) {
      if (!$dbwAnwExtra->deleteAnwesenheitExtraWithAnwesenheitId($deleteExtraAnw->id, $pAnwesenheit->id, $pStatus5)) {
        $newExtraAnws[] = $deleteExtraAnw; 
      }
    }

    foreach ($newExtraAnws as $newExtraAnw) {
      $extraAnwIdAry[] = $dbwAnwExtra->insertAnwesenheitExtra($newExtraAnw, $pStatus5);
    }

    
    return $extraAnwIdAry;
  }

  
  public function updateExtraAnws($pAnwesenheit, $pStatus5 = false)
  {
    return $this->updateExtraAnwsWithTable(\DBWorkAnwesenheitExtra2::getDefaultInstance(1, true), $pAnwesenheit, $pStatus5);
  }

  
  public function updateExtraAnwsInTableAnwExtra($pAnwesenheit, $pStatus5 = false)
  {
    return $this->updateExtraAnwsWithTable(\DBWorkAnwesenheitExtra::getDefaultInstance(1, true), $pAnwesenheit, $pStatus5);
  }

  
  public function updateAnwesenheit($pAnwesenheit)
  {
    $organw = $this->getAnwesenheit($pAnwesenheit->id);
    $this->updateObject($pAnwesenheit, $organw, $this->getTableName(), 'id');
    $this->setActionIDs();
  }

  
  public function getAnwesenheitKurstagInfo($pKnr, $pVonDat, $pBisDat, $pAusgefalleneKtage = false, $pKtid = -1, $pIsDozTool = false)
  {
    $vonDat = $this->dboperation->getString4Sql($pVonDat);
    $bisDat = $this->dboperation->getString4Sql($pBisDat);
    $knr = $this->dboperation->getString4Sql($pKnr);
    $ktid = $this->dboperation->getInt4Sql($pKtid);

    $defaultDateInDbFormat = $this->datetimeop->getFormatDate4Server(DEFAULTDATE);

    if ($ktid > 0) {
      $where = " kt.id = $ktid ";
    } elseif (empty($pBisDat) || empty($pVonDat)) {
      $where = ' 1=0 '; 
    } else {
      $where = "(kt.knr='$knr') AND (" . $this->dboperation->dbparser->isNull('kt.datum', $defaultDateInDbFormat) . '>=' . $this->datetimeop->getFormatDate4Server($vonDat)
        . ') AND (' . $this->dboperation->dbparser->isNull('kt.datum', $defaultDateInDbFormat) . '<=' . $this->datetimeop->getFormatDateTime4Server($bisDat . ' ' . DEFAULTTIME_WITH_SECONDS) . ') ';
    }
    if (!$pAusgefalleneKtage) {
      $where .= ' AND (' . $this->getKurstagEinschraenkung() . ')';
    }

    if (DBWorkBASysSettings::isWienUni()) {
      $where .= ' AND NOT ' . $this->dboperation->dbparser->isNull('kt.ausf', "' '") . ' = \'!\' ';
    }

    if ($pIsDozTool) {
      $where .= ' AND ' . $this->dboperation->dbparser->isNull('kt.nodoztool', '\'F\'') . ' <> \'W\' ';
    }

    $sql = 'SELECT kt.id,' . $this->dboperation->dbparser->isNull('kt.datum', $defaultDateInDbFormat) . ' AS datum, '
      . $this->dboperation->dbparser->isNull('kt.zeit_beg', $defaultDateInDbFormat) . ' AS zeit_beg, '
      . $this->dboperation->dbparser->isNull('kt.zeit_ende', $defaultDateInDbFormat) . ' AS zeit_ende,'
      
      . $this->dboperation->dbparser->isNull('kt.minuten', 0) . ' AS minuten,'
      
      
      . $this->dboperation->dbparser->isNull('kt.ausf', "' '") . ' AS ausf,'
      . $this->dboperation->dbparser->isNull('kt.dnr', '0') . ' AS dnr,'
      . ' (CASE ' . $this->dboperation->dbparser->isNull('kt.dnr', '0') . " WHEN 0 THEN ' ' ELSE " . $this->dboperation->dbparser->isNull('d.name', "' '") . ' END) AS dozname,'
      . $this->dboperation->dbparser->isNull('kt.ort', "' '") . ' AS ort' . "\n"
      . ', (SELECT ' . $this->dboperation->dbparser->isNull('kf.fach', "' '")
      . ' from kurs_fach kf where kf.id = kt.fachid) as fach'
      . ', (SELECT ' . $this->dboperation->dbparser->isNull('kf.bemerkung', "' '")
      . ' from kurs_fach kf where kf.id = kt.fachid) AS bemerkung'

      . ' FROM kurstage kt INNER JOIN kurse k ON (k.nr=kt.knr)'
        . ' LEFT OUTER JOIN dozenten d ON (d.nr=kt.dnr)' . "\n"
      . " WHERE $where"
      . ' ORDER BY ' . $this->dboperation->dbparser->isNull('kt.datum', $defaultDateInDbFormat) . ', '
          . $this->dboperation->dbparser->isNull('kt.zeit_beg', $defaultDateInDbFormat) . ', '
          . $this->dboperation->dbparser->isNull('kt.zeit_ende', $defaultDateInDbFormat) . ', '
          . 'kt.ort, kt.id';

    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $result = array();
    $rowcount = $this->dboperation->rowcount();
    for ($i = 0; $i < $rowcount; $i++) {
      $result[] = $this->_getKurstagInfoFromResultset($i, $knr);
    }

    return $result;
  }

  public function _getKurstagInfoFromResultset(int $pIndex, string $pKnr): AnwesenheitsKurstagInfo
  {
    $this->dboperation->selectrow($pIndex);
    $tmp = new AnwesenheitsKurstagInfo();

    
    
    $dbwKurseTage = new DBWorkKurseTageMin();
    $tmp->ausf = $dbwKurseTage->getDatamodulValue('ausf');
    $tmp->beginn = $dbwKurseTage->getValue('zeit_beg', \DatamodulVarType::TYPE_TIME);
    $tmp->ende = $dbwKurseTage->getValue('zeit_ende', \DatamodulVarType::TYPE_TIME);
    $tmp->bemerkung = $dbwKurseTage->getDatamodulValue('bemerkung');
    $tmp->datum = $dbwKurseTage->getValue('datum', \DatamodulVarType::TYPE_DATE);
    $tmp->dnr = $dbwKurseTage->getDatamodulValue('dnr');
    $tmp->dozname = $dbwKurseTage->getDatamodulValue('dozname');
    $tmp->fach = $dbwKurseTage->getDatamodulValue('fach');
    $tmp->id = $dbwKurseTage->getDatamodulValue('id');

    $tmp->ue = MathOperations::floatToString(MathOperations::calculateUE($this->getDatamodulValue('minuten'), null, $pKnr));
    $tmp->renderdatum = $this->datetimeop->getWeekDayShort($tmp->datum) . ', ' . $this->datetimeop->getShortDateStr($this->getDatamodulValue('datum'));

    return $tmp;
  }

  
  public function getFachAnwesenheiten($pBelId, $pKursNr, $pVonDat, $pBisDat, $pIsAuswertFach)
  {
    $belid = $this->dboperation->getInt4Sql($pBelId);
    $kursNr = $this->dboperation->getString4Sql($pKursNr);
    $defaultDateInDbFormat = $this->datetimeop->getFormatDate4Server(DEFAULTDATE);

    $bisPart = '';
    
    if (!empty($pBisDat)) {
      $bisDatInDbFormat 
         = $this->datetimeop->getFormatDateTime4Server($this->dboperation->getString4Sql($pBisDat . ' 23:59:59'));
      $bisPart = ' AND ' . $this->dboperation->dbparser->isNull('kt.datum', $defaultDateInDbFormat) . '<='
        . $bisDatInDbFormat;
    }
    
    $vonPart = '';
    if (!empty($pVonDat)) {
      $vonDatInDbFormat 
        = $this->datetimeop->getFormatDateTime4Server($this->dboperation->getString4Sql($pVonDat) . ' ' . DEFAULTTIME_WITH_SECONDS);
      $vonPart = ' AND (' . $this->dboperation->dbparser->isNull('kt.datum', $vonDatInDbFormat) . '>='
      . $vonDatInDbFormat . ' OR kt.datum = ' . $this->datetimeop->getFormatDate4Server(DEFAULTDATE) . ')';
    }
    
    
    
    
    $fachPart = ' AND (' . $this->dboperation->dbparser->isNull('kt.fachid', 0) . ' IN '
      . '(SELECT f.fach_id FROM tn_fach f WHERE (f.beleg_id=b.id) ))';

    
    $select = 'b.id AS belegid, b.knr, b.tnr, kt.id as ktid, kt.fachid, kt.minuten, '
              . 'kf.fach, kf.bemerkung as fachbem, '
              . 'a.id as aid, a.abelegid, a.aktid, a.astatus ' . "\n";

    

    $sql = 'SELECT ' . $select
           . ' FROM belegung b INNER JOIN kurstage kt ON (b.knr=kt.knr) ' . "\n"
           . ' LEFT JOIN kurs_fach kf ON (kt.fachid=kf.id) ' . "\n"
           . ' LEFT JOIN anw a ON (kt.id = a.aktid) ' . "\n"
           . ' WHERE (b.id = a.abelegid or a.abelegid is NULL) AND b.id = ' . $belid
 
 
           . $vonPart
           . $bisPart . "\n"
           . $fachPart
           . ' ORDER BY kf.fach, kt.datum, kt.zeit_beg, kt.zeit_ende, a.id';

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

    $fachAnwKtAry = array();
    
    
    

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

    
    
    for ($i = 0; $i < $rowcount; $i++) {
      $this->dboperation->selectrow($i);
      $tmpFachAnwObj = $this->_getFachAnwInfoFromResultset($i, $kursNr);
      
      if ($tmpFachAnwObj->aid != '') {
        $anwIndAry[] = $tmpFachAnwObj->aid;
      }
      $fachAnwKtAry[] = $tmpFachAnwObj;
    } 

    
    
    
    
    
    

    $dbwEAnw = new DBWorkAnwesenheitExtra();
    
    $eanwAry = $dbwEAnw->getAnwesenheitenExtraForAnwesenheitIds($anwIndAry);

    
    foreach ($fachAnwKtAry as $tmpAnw) {
      
      
      
      $tmpEanwAry = $this->_getExtraAnwAry($eanwAry, $tmpAnw->aid);

      $tmpAnw->anwue = 0.00;
      
      if ($tmpAnw->astatus == 'A') {
        $tmpAnw->anwue = $tmpAnw->ue;
      }

      
      
      $sumAbwUE = 0.00;
      
      foreach ($tmpEanwAry as $tmpEAnw) {
        $bisMinuts = DateTimeOperations::convertTimeToMinutes($tmpEAnw->ebis);
        $vonMinuts = DateTimeOperations::convertTimeToMinutes($tmpEAnw->evon);
        $tmpAbwUE = MathOperations::calculateUE($bisMinuts - $vonMinuts, null, $kursNr); 
        if ($tmpEAnw->estatus == 'A') {
          
          $sumAbwUE -= $tmpAbwUE;
        } else {
          
          $sumAbwUE += $tmpAbwUE;
        }
      } 
      
      
      $tmpAnw->anwue -= $sumAbwUE;
    } 

    $collectedFachIds = array();
    
    $result = array();
    
    foreach ($fachAnwKtAry as $tmpFachAnwObj) {
      $fachId = $tmpFachAnwObj->fachid;

      
      if (!in_array($fachId, $collectedFachIds)) {
        $collectedFachIds[] = $fachId;
        
        
        $result[] = $tmpFachAnwObj;
      }
      
      else {
        
        
        
        $position = SearchOperations::getArrayIndexbyfieldnameUpper($result, 'fachid', $fachId);

        $aktFachAnwObj = $result[$position];
        $aktFachAnwObj->ue += $tmpFachAnwObj->ue;
        $aktFachAnwObj->anwue += $tmpFachAnwObj->anwue;
        

        $aktFachAnwObj->anwproz = MathOperations::floatToString($aktFachAnwObj->anwue / $aktFachAnwObj->ue * 100);
        
        
      }
    } 

    return $result;
  }

  

  
  public function getKurstageAndAnwesenheiten4Belegung(
    $pBelId,
    $pVonDat = '',
    $pBisDat = '',
    $pIsAnwControl = false,
    $pAnwStati = null
  ) {
    $belid = $this->dboperation->getInt4Sql($pBelId);

    
    
    
    
    

    $defaultDateInDbFormat = $this->datetimeop->getFormatDate4Server(DEFAULTDATE);

    $anwStatusPart = '';
    if ($pIsAnwControl) {
      
      $anwStatusPart .= 'AND NOT (a.astatus IS NULL OR a.astatus = \'\') ';
    }
    if (is_array($pAnwStati) && !empty($pAnwStati)) {
      
      $anwStatusPart .= ' AND (' . $this->dboperation->dbparser->isNull('a.astatus', "' '") . " in ('" . implode("','", $pAnwStati) . "')"
      . ' OR ' . $this->dboperation->dbparser->isNull('ae.estatus', "' '") . "  in ('" . implode("','", $pAnwStati) . "'))";
    }
    $bisPart = '';
    
    if (!empty($pBisDat)) {
      $bisDatInDbFormat 
         = $this->datetimeop->getFormatDateTime4Server($this->dboperation->getString4Sql($pBisDat . ' 23:59:59'));
      $bisPart = ' AND ' . $this->dboperation->dbparser->isNull('kt.datum', $defaultDateInDbFormat) . '<='
        . $bisDatInDbFormat;
    }
    
    $vonPart = '';
    if (!empty($pVonDat)) {
      $vonDatInDbFormat 
        = $this->datetimeop->getFormatDateTime4Server($this->dboperation->getString4Sql($pVonDat) . ' ' . DEFAULTTIME_WITH_SECONDS);
      $vonPart = ' AND (' . $this->dboperation->dbparser->isNull('kt.datum', $vonDatInDbFormat) . '>='
      . $vonDatInDbFormat . ' OR kt.datum = ' . $this->datetimeop->getFormatDate4Server(DEFAULTDATE) . ')';
    }
    
    
    
    


    $fachPart = ' AND (((SELECT ' . $this->dboperation->dbparser->isNull('s234', "'F'") . ' FROM switch)<>"W") OR (' . $this->dboperation->dbparser->isNull('kt.fachid', 0) . '=0) OR ('
      . $this->dboperation->dbparser->isNull('kt.fachid', 0) . ' IN '
      . '(SELECT f.fach_id FROM tn_fach f WHERE (f.beleg_id=b.id) )))';


    
    $select = 'b.id AS belegid, b.knr, b.tnr, kt.id as ktid, kt.datum, kt.fachid, kt.minuten, '
              . ' kf.fach, kf.bemerkung as fachbem, '
              . ' a.id as aid, a.abelegid, a.aktid, a.astatus, a.abemerkung ' . "\n";

    

    $sql = 'SELECT ' . $select
           . ' FROM belegung b INNER JOIN kurstage kt ON (b.knr=kt.knr) ' . "\n"
           . ' LEFT JOIN kurs_fach kf ON (kt.fachid=kf.id) ' . "\n"
           . ' LEFT JOIN anw a ON (kt.id = a.aktid) ' . "\n"
           . ' LEFT JOIN anw_extra ae on ae.eanwid = a.id ' . "\n"
           . ' WHERE (b.id = a.abelegid or a.abelegid is NULL) AND b.id = ' . $belid
 
 
           . $vonPart
           . $bisPart . "\n"
           . $fachPart . "\n"
           . $anwStatusPart
           . ' ORDER BY kt.datum, kt.zeit_beg, kt.zeit_ende, a.id';

    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $result = array();
    $anwIndAry = array();
    $rowcount = $this->dboperation->rowcount();

    
    for ($i = 0; $i < $rowcount; $i++) {
      $this->dboperation->selectrow($i);
      $tmpKtAnwObj = $this->_getTNTerminKalenderInfoFromResultset($i);
      
      if ($tmpKtAnwObj->aid != '') {
        $anwIndAry[] = $tmpKtAnwObj->aid;
      }
      $result[] = $tmpKtAnwObj;
    }
    $dbwEAnw = new DBWorkAnwesenheitExtra();
    
    $eanwAry = $dbwEAnw->getAnwesenheitenExtraForAnwesenheitIds($anwIndAry);

    
    foreach ($result as $tmpAnw) {
      
      
      
      $tmpEanwAry = $this->_getExtraAnwAry($eanwAry, $tmpAnw->aid);

      
      
      $sumAbwUE = 0.00;
      $tmpAnw->abwbem = '';
      if ($tmpAnw->astatus == 'E' || $tmpAnw->astatus == 'U') {
        
        $sumAbwUE = $tmpAnw->ktue;
        
        $tmpAnw->abwbem = $tmpAnw->abemerkung;
      }

      foreach ($tmpEanwAry as $tmpEAnw) {
        
        
        
        if ($tmpAnw->abwbem == '') {
          $tmpAnw->abwbem = $tmpEAnw->ebemerkung;
        }
        
        

        $bisMinuts = DateTimeOperations::convertTimeToMinutes($tmpEAnw->ebis);
        $vonMinuts = DateTimeOperations::convertTimeToMinutes($tmpEAnw->evon);
        $tmpAbwUE = MathOperations::calculateUE($bisMinuts - $vonMinuts, null, $tmpAnw->knr); 
        
        if ($tmpEAnw->estatus == 'A') {
          
          $sumAbwUE -= $tmpAbwUE;
        } else {
          
          $sumAbwUE += $tmpAbwUE;
        }
      } 
      
      
      $tmpAnw->abwue = $sumAbwUE;
      
      
    } 

    return $result;
  }

  

  
  private function _getExtraAnwAry($pEanwAry, $pId)
  {
    $result = array();
    foreach ($pEanwAry as $eAnw) {
      if ($eAnw->eanwid == $pId) {
        $result[] = $eAnw;
      }
    } 

    return $result;
  }

  

  
  private function _getTNTerminKalenderInfoFromResultset($pIndex)
  {
    $this->dboperation->selectrow($pIndex);
    $tmp = new AnwesenheitsTNTerminkalenderInfo();
    $tmp->belegid = $this->getDatamodulValue('belegid');
    $tmp->tnr = $this->getDatamodulValue('tnr');
    $tmp->knr = $this->getDatamodulValue('knr');
    $tmp->ktid = $this->getDatamodulValue('ktid');
    $tmp->datum = $this->datetimeop->getDateStr($this->getDatamodulValue('datum'));
    $tmp->wotaglang = $this->datetimeop->getWeekDay($tmp->datum);
    $tmp->datum_komplettlang = $tmp->wotaglang . ', ' . $tmp->datum;
    $tmp->ktfachid = $this->getDatamodulValue('fachid');
    $tmp->fach = $this->getDatamodulValue('fach');
    $tmp->fachbem = $this->getDatamodulValue('fachbem');
    
    $tmp->ktue = MathOperations::calculateUE($this->getDatamodulValue('minuten'), null, $tmp->knr);

    $tmp->aid = $this->getDatamodulValue('aid');
    $tmp->astatus = $this->getDatamodulValue('astatus');
    $tmp->abemerkung = $this->getDatamodulValue('abemerkung');
    
    $tmp->abwue = 0;
    $tmp->abwbem = '';
    
    
    return $tmp;
  }

  
  private function _getFachAnwInfoFromResultset($pIndex, $pKnr)
  {
    $this->dboperation->selectrow($pIndex);
    $tmp = new FachAnwesenheitInfo();
    $tmp->ktid = $this->getDatamodulValue('ktid');
    $tmp->fachid = $this->getDatamodulValue('fachid');
    $tmp->fach = $this->getDatamodulValue('fach');
    $tmp->fachbem = $this->getDatamodulValue('fachbem');
    
    
    $tmp->ue = MathOperations::calculateUE($this->getDatamodulValue('minuten'), null, $pKnr);
    
    $tmp->aid = $this->getDatamodulValue('aid');
    $tmp->astatus = $this->getDatamodulValue('astatus');

    
    $tmp->anwue = 0;
    
    $tmp->anwproz = 0;
    
    
    return $tmp;
  }

  

  
  public function getAnwesenheitBelegungInfo(\Kufer\Web\Classes\Db\AnwesenheitFilter $anwesenheitFilter)
  {
    $statusPart = '';
    if (is_array($anwesenheitFilter->statusAry) && !empty($anwesenheitFilter->statusAry)) {
      $statusPart .= ' AND ' . $this->dboperation->dbparser->isNull('status_1', "' '")
        . " IN ('"
        . implode(
          "','",
          \ConstantOperations::mapBelegungStatusToRealStatus($anwesenheitFilter->statusAry)
        ) . "')";
    }
    $defaultDateInDbFormat = $this->datetimeop->getFormatDate4Server(DEFAULTDATE);

    $eintrittpart = '';
    if (!empty($anwesenheitFilter->bisDate)) {
      $bisDatInDbFormat 
        = $this->datetimeop->getFormatDateTime4Server($this->dboperation->getString4Sql($anwesenheitFilter->bisDate . ' 23:59:59'));
      $eintrittpart = ' AND ' . $this->dboperation->dbparser->isNull('b.eintritt', $defaultDateInDbFormat) . '<='
        . $bisDatInDbFormat;
    }
    $austrittpart = '';
    if (!empty($anwesenheitFilter->vonDat)) {
      $invalidDateInDbFormat = $this->datetimeop->getInvalidDate4Server();
      $vonDatInDbFormat 
        = $this->datetimeop->getFormatDateTime4Server($this->dboperation->getString4Sql($anwesenheitFilter->vonDat) . ' ' . DEFAULTTIME_WITH_SECONDS);
      $austrittpart = ' AND (' . $this->dboperation->dbparser->isNull('b.austritt', $vonDatInDbFormat) . '>='
      . $vonDatInDbFormat . ' OR b.austritt = ' . $this->datetimeop->getFormatDate4Server(DEFAULTDATE)
      . ($invalidDateInDbFormat != '' ? ' OR b.austritt = ' . $invalidDateInDbFormat : '') . ')';
    }

    
    $deputatPart = '';
    $dbwBASysSettings = DBWorkBASysSettings::getDefaultInstance();
    $settings = $dbwBASysSettings->getSettings();
    if ($settings->notenverwaltungAktiviert && $anwesenheitFilter->ktId !== false && !is_null($anwesenheitFilter->ktId)) {
      $ktid = $this->dboperation->getInt4Sql($anwesenheitFilter->ktId);
      $deputatPart = ' AND (' . $this->dboperation->dbparser->isNull('(SELECT fachid FROM kurstage WHERE id=' . $ktid . ')', 0) . ' = 0'
      


;

      
      $fachid = $this->getFachIdForKurstag($ktid);
      if ($fachid > 0) {
        $deputatPart .= ' OR b.id IN (SELECT f.beleg_id FROM tn_fach f WHERE f.fach_id = ' . $fachid . ')';
      }
      $deputatPart .= ')';
    }

    $firmenPart = '';
    if (isset($anwesenheitFilter->firmaTnr) && $anwesenheitFilter->firmaTnr > 0) {
      $firmaTnr = $this->dboperation->getInt4Sql($anwesenheitFilter->firmaTnr);
      $firmenPart .= ' AND b.firma = ' . $firmaTnr . ' ';
    }

    $tnrPart = '';
    if (isset($anwesenheitFilter->teilnehmerTnr) && $anwesenheitFilter->teilnehmerTnr > 0) {
      $teilnehmerTnr = $this->dboperation->getInt4Sql($anwesenheitFilter->eilnehmerTnr);
      $tnrPart .= ' AND b.tnr = ' . $teilnehmerTnr . ' ';
    }

    $dbwBelegung = \DBWorkBelegung::getDefaultInstance($this->dbtyp);
    $dbwBelegung->newSearch();
    $dbwBelegung->toobject->status = 'F';
    if (!is_null($anwesenheitFilter->fnSetDBWorkBelegungOrder)) {
      $dbwBelegung = call_user_func($anwesenheitFilter->fnSetDBWorkBelegungOrder, $dbwBelegung);
    }

    $belegungenBuilder = $dbwBelegung->getBelegungenByKursIdQuery(
      $anwesenheitFilter->knr,
      $anwesenheitFilter->allowKinder5
    )
      ->select(
        array(
          'b.id AS belegid',
          "(CASE WHEN (:ifnull(b.status_1, ' '): = 'Z')"
          . ' AND (:ifnull(b.gekuendigt_zum, :defaultdate:): > :datenow:)'
          . ' THEN ' . $this->dboperation->dbparser->concat(array(":ifnull(b.status_1, ' '):", "'Z!'"))
          . " ELSE :ifnull(b.status_1, ' '): END) AS status_1",
          ':ifnull(b.eintritt, :defaultdate:): AS eintritt',
          ':ifnull(b.austritt, :defaultdate:): AS austritt',
          ':ifnull(b.gekuendigt_zum, :defaultdate:): AS gekuendigt_zum',
          ":ifnull(b.bemerkung, ' '): AS bemerkung,t.nr AS tnr",
          't.vorname AS tnvorname',
          't.name AS tnnachname',
          ':ifnull(t.gebdat, :defaultdate:): AS gebdat',
          ":ifnull(t.arbamtnr, ' '): AS arbamtnr",
          'b.firma'
        )
      )
      ->addToWhere($statusPart
      . $eintrittpart . $austrittpart
      . $deputatPart
      . $firmenPart
      . $tnrPart);
    if (is_null($anwesenheitFilter->fnSetDBWorkBelegungOrder)) {
      $belegungenBuilder->clearOrderBy()
        ->orderByAsc('t.name')
        ->orderByAsc('t.vorname');
    }

    $this->dboperation->clear();
    $this->dboperation->query($belegungenBuilder->getSQL());
    $result = array();

    
    
    $dbwBelegung = new DBWorkBelegung(1, false);
    $dbwBelegung->dboperation = $this->dboperation;
    $dbwTeilnehmer = new DBWorkTeilnehmer(1, false);
    $dbwTeilnehmer->dboperation = $this->dboperation;

    $dbwHLRolle = new DBWorkHLRolle(1, true);
    $this->dboperation->resetToFirstRow();
    while ($this->dboperation->selectNextRow()) {
      $tmp = new AnwesenheitsBelegungInfo();
      $tmp->belegid = $this->getDatamodulValue('belegid');
      $tmp->status_1 = $dbwBelegung->getDatamodulValue('status_1');
      $tmp->eintritt = $dbwBelegung->getDatamodulValue('eintritt');
      $tmp->austritt = $dbwBelegung->getDatamodulValue('austritt');
      $tmp->gekuendigt_zum = $dbwBelegung->getDatamodulValue('gekuendigt_zum');
      $tmp->bemerkung = $dbwBelegung->getDatamodulValue('bemerkung');
      $tmp->tnr = $dbwBelegung->getDatamodulValue('tnr');

      $tnVorname = $this->getDatamodulValue('tnvorname');
      $tnNachname = $this->getDatamodulValue('tnnachname');
      $namensTrenner = '';
      if ($tnVorname != '' && $tnNachname != '') {
        $namensTrenner = ' ';
      }
      $tmp->tnname = $tnNachname . $namensTrenner . $tnVorname;
      $tmp->tnnamevorname = $tmp->tnname;
      $tmp->tnvornamename = $tnVorname . $namensTrenner . $tnNachname;
      $tmp->firmaId = $dbwBelegung->getDatamodulValue('firma');
      $tmp->gebdat = $dbwTeilnehmer->getDatamodulValue('gebdat');
      $tmp->arbamtnr = $dbwTeilnehmer->getDatamodulValue('arbamtnr');
      $tmp->rollen = $dbwHLRolle->getRollenByBelegId($tmp->belegid);
      $result[] = $tmp;
    }
    $this->dboperation->clear();

    $toolsettingsAnwesenheit = ToolSettingsAPI::getToolSettings(\ToolsAPI::TOOL_ANWESENHEIT_DOZ);
    if ($toolsettingsAnwesenheit->showTeilnehmerbilder) {
      foreach ($result as $obj) {
        $obj->fotolink = FotoAPI::getFotoLinkForKuferTools('T', $obj->tnr);
      }
    }

    return $result;
  }

  
  public function getAnwesenheitsVerhaltenInfo($pBelegungInfoAry, $pKursTageInfo)
  {
    $dbwAnwVerhalten = new DBWorkAnwVerhalten();
    $dbwAnwVerhaltenText = new DBWorkAnwVerhaltenText();

    $anwVerhaltenAry = $dbwAnwVerhalten->getAnwVerhaltenByBelegungInfoAndKursTageInfoId($pBelegungInfoAry, $pKursTageInfo);

    $result = array();
    foreach ($anwVerhaltenAry as $anwVerhalten) {
      $anwVerhaltenText = $dbwAnwVerhaltenText->getAnwVerhaltenTextById($anwVerhalten->textid);

      $tmp = new AnwesenheitsVerhaltenInfo();
      $tmp->belegid = $anwVerhalten->belegid;
      $tmp->ktid = $anwVerhalten->ktid;
      $tmp->text = $anwVerhaltenText;
      $result[] = $tmp;
    }

    return $result;
  }

  
  public function getAnwesenheiten($pKnr, $pVonDat, $pBisDat, $pStatusAry = null, $pKtid = -1, $pRefreshCache = false, $pFirmaTnr = null)
  {
    $statuspart = StringOperations::convertArray2InList($pStatusAry);
    if ($statuspart != '') {
      $statuspart = ' AND ' . $this->dboperation->dbparser->isNull('b.status_1', "' '") . " in ($statuspart)";
    }

    $cacheKey = $pKnr . '|' . $pVonDat . '|' . $pBisDat . '|' . $statuspart . '|' . $pKtid;
    if ($pRefreshCache || !array_key_exists($cacheKey, self::$_cache_Anwesenheiten)) {
      $vonDat = $this->dboperation->getString4Sql($pVonDat);
      $bisDat = $this->dboperation->getString4Sql($pBisDat);
      $knr = $this->dboperation->getString4Sql($pKnr);
      $ktid = $this->dboperation->getInt4Sql($pKtid);

      $defaultDateInDbFormat = $this->datetimeop->getFormatDate4Server(DEFAULTDATE);

      if ($pKtid > 0) {
        $zeitraumpart = " kt.id = $ktid ";
      } elseif (empty($pBisDat) || empty($pVonDat)) {
        $zeitraumpart = ' 1=0 '; 
      } else {
        $zeitraumpart = " (kt.knr='$knr') AND (" . $this->dboperation->dbparser->isNull('kt.datum', $defaultDateInDbFormat) . '>=' . $this->datetimeop->getFormatDate4Server($vonDat) . ') AND ('
        . $this->dboperation->dbparser->isNull('kt.datum', $defaultDateInDbFormat) . '<=' . $this->datetimeop->getFormatDateTime4Server($bisDat . ' 23:59:59') . ') ';
      }

      $firmenPart = '';
      if (isset($pFirmaTnr) && $pFirmaTnr > 0) {
        $firmaTnr = $this->dboperation->getInt4Sql($pFirmaTnr);
        $firmenPart .= ' AND b.firma = ' . $firmaTnr . ' ';
      }

      

      $sql = 'SELECT a.id AS anwid,a.abelegid,a.aktid,a.aamscode, ' . $this->dboperation->dbparser->isNull('a.astatus', "' '") . ' AS astatus,'
        . $this->dboperation->dbparser->isNull('a.abemerkung', "' '") . ' AS abemerkung ,e.id AS extraid, e.eamscode,'
        . $this->dboperation->dbparser->isNull('e.estatus', "' '") . ' AS estatus,'
        . $this->dboperation->dbparser->isNull('e.ebemerkung', "' '") . ' AS ebemerkung,'
        . $this->dboperation->dbparser->isNull('e.evon', $defaultDateInDbFormat) . ' AS evon,'
        . $this->dboperation->dbparser->isNull('e.ebis', $defaultDateInDbFormat) . ' AS ebis,'
        . $this->dboperation->dbparser->isNull('a.afehlminanfang', '0') . ' AS afehlminanfang,'
        . $this->dboperation->dbparser->isNull('a.afehlminende', '0') . ' AS afehlminende,'
        . $this->dboperation->dbparser->isNull('a.aprogresscheck', "' '") . ' AS aprogresscheck,'
        . $this->dboperation->dbparser->isNull('a.ahausaufgaben', "' '") . ' AS ahausaufgaben, '
        . 'a.bearbeiter' . "\n"
        . ' FROM ' . $this->getTableName() . ' a INNER JOIN belegung b ON (b.id=a.abelegid) INNER JOIN kurstage kt ON (kt.id=a.aktid)'
        . 'LEFT OUTER JOIN ' . $this->tableNameExtra . ' e ON (e.eanwid=a.id)' . "\n"
        . ' WHERE ' . $zeitraumpart . $firmenPart
        
        . 'AND (' . $this->getKurstagEinschraenkung() . ')'
        . ' AND (' . $this->dboperation->dbparser->isNull('b.knr', "' '") . "='$knr') AND ("
        . $this->dboperation->dbparser->isNull('b.status_1', "' '") . "<>'F')" . "\n"
        . $statuspart . "\n"
        . ' ORDER BY a.abelegid, a.aktid, e.evon';

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

      $result = array();

      $tmpBelid = -1;
      $tmpKtid = -1;

      for ($i = 0; $i < $rowcount; $i++) {
        $this->dboperation->selectrow($i);

        if ($this->getDatamodulValue('abelegid') == $tmpBelid && $this->getDatamodulValue('aktid') == $tmpKtid) {
          

          $extra = $this->_getAnwExtra();
          if ($extra != null) {
            $tmp = array_pop($result);
            $tmp->addExtraAnwesenheit($extra);
            $result[] = $tmp;
          }
        } else {
          $tmp = $this->getCurrentRow();

          $extra = $this->_getAnwExtra();
          if ($extra != null) {
            $tmp->addExtraAnwesenheit($extra);
          }

          $result[] = $tmp;

          $tmpBelid = $tmp->abelegid;
          $tmpKtid = $tmp->aktid;
        }
      }

      self::$_cache_Anwesenheiten[$cacheKey] = $result;
    }

    return self::$_cache_Anwesenheiten[$cacheKey];
  }

  private function _getAnwExtra()
  {
    if ($this->getDatamodulValue('extraid') > 0) {
      $tmp = new AnwesenheitExtra();
      
      $dbwAnwExtra = new DBWorkAnwesenheitExtra();
      $tmp->id = $dbwAnwExtra->getDatamodulValue('extraid');
      $tmp->estatus = $dbwAnwExtra->getDatamodulValue('estatus');
      $tmp->ebemerkung = $dbwAnwExtra->getDatamodulValue('ebemerkung');
      $tmp->evon = $dbwAnwExtra->getDatamodulValue('evon');
      $tmp->ebis = $dbwAnwExtra->getDatamodulValue('ebis');
      $tmp->eanwid = $dbwAnwExtra->getDatamodulValue('anwid');
      $tmp->eamscode = $dbwAnwExtra->getDatamodulValue('eamscode');

      return $tmp;
    }

    return null;
  }

  
  public function hasKurstagAnwesenheiten(int $kurstagId): bool
  {
    $builder = $this->getAllQuery()
      ->select('COUNT(*) AS anzahl')
      ->and('aktid = :ktId:')
      ->paramInt('ktId', $kurstagId);

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

    $this->dboperation->selectNextRow();

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

  
  public function GetKnr($pAnwesenheit)
  {
    if ($pAnwesenheit == null) {
      throw new Exception('$pKursanwesenheit ist null');
    }
    if ($pAnwesenheit->aktid != 0) {
      static $dbwktag = null;
      if (is_null($dbwktag)) {
        $dbwktag = new DBWorkKurseTageWT();
      }
      $kurstag = $dbwktag->getKurstag($pAnwesenheit->aktid);
      if ($kurstag != null) {
        return $kurstag->knr;
      }
    }
    if ($pAnwesenheit->abelegid != 0) {
      static $dbwbeleg = null;
      if (is_null($dbwbeleg)) {
        $dbwbeleg = new DBWorkBelegung();
      }
      $belegung = $dbwbeleg->getBelegung($pAnwesenheit->abelegid);
      if ($belegung != null) {
        return $belegung->knr;
      }
    }

    throw new Exception('konnte keinen Kurs zu dieser Kursanwesenheit bestimmen');
  }

  
  public function getFachIdForKurstag($pKurstagId)
  {
    $tagid = $this->dboperation->getInt4Sql($pKurstagId);
    $sql = "SELECT kt.fachid FROM kurstage kt WHERE id=$tagid ";
    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $this->dboperation->selectrow(0);

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

  
  public function getAnwProzent($pBelegID, $pVon, $pBis, $pKnr, $pStatus)
  {
    $params = $pBelegID . ', ' . $this->datetimeop->getFormatDate4Server($pVon) . ', ' . $this->datetimeop->getFormatDate4Server($pBis) . ", '$pKnr', '$pStatus', NULL, NULL, NULL";
    $sql = $this->dboperation->dbparser->callFunction('ANW_PROZENT', $params, 'anw_prozent');

    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $this->dboperation->selectrow(0);
    $result = $this->getDatamodulValue('anw_prozent');

    return $this->mathop->floatToString($result);
  }

  public function getAnwTage($pBelegID, $pVon, $pBis, $pKnr, $pStatus)
  {
    if ($pStatus == 'null' || $pStatus == '') {
      $status = "' '";
    } else {
      $status = "'$pStatus'";
    }
    $defaultDateInDbFormat = $this->datetimeop->getFormatDate4Server(DEFAULTDATE);
    $sql = "SELECT count(*) as anzahl
    FROM belegung b
    INNER JOIN kurstage kt ON (kt.knr=b.knr)
    LEFT OUTER JOIN anw a ON ((a.aktid=kt.id) AND (a.abelegid=b.id))
    left outer join anw_extra ae on ae.eanwid = a.id
    WHERE (b.id = $pBelegID)
    AND ((" . $this->dboperation->dbparser->isNull($this->datetimeop->getFormatDate4Server($pVon), $defaultDateInDbFormat) . "=$defaultDateInDbFormat)
    OR (kt.datum>=" . $this->datetimeop->getFormatDate4Server($pVon) . '))
    AND ((' . $this->dboperation->dbparser->isNull($this->datetimeop->getFormatDate4Server($pBis), $defaultDateInDbFormat) . "=$defaultDateInDbFormat)
     OR (kt.datum<=" . $this->datetimeop->getFormatDate4Server($pBis) . '))
    AND (' . $this->getKurstagEinschraenkung() . ')
    AND (((SELECT ' . $this->dboperation->dbparser->isNull('s234', "'F'") . " FROM switch)<>'W') OR (" . $this->dboperation->dbparser->isNull('kt.fachid', '0') . '=0) OR
     (' . $this->dboperation->dbparser->isNull('kt.fachid', '0') . ") IN (SELECT tf.fach_id FROM tn_fach tf WHERE (tf.beleg_id=b.id)))
    and
    (('W' = " . $this->dboperation->dbparser->isNull("(CASE WHEN $status=' '
      THEN " . $this->dboperation->dbparser->isNull('(CASE WHEN ' . $this->dboperation->dbparser->isNull('a.astatus', "' '")
      . ' IN (SELECT ' . $this->dboperation->dbparser->isNull('ag.status', "' '") . ' FROM anw_hl_gruende ag WHERE '
      . $this->dboperation->dbparser->isNull('ag.anwesend', "' '") . "='W')
      THEN 'W' ELSE 'F' END)", "'F'")
      . 'ELSE ' . $this->dboperation->dbparser->isNull('(CASE WHEN ' . $this->dboperation->dbparser->isNull('a.astatus', "' '")
      . "=$status THEN 'W' ELSE 'F' END)", "'F'") . ' END)', "'F'")
      . ")
     or
      ('W' = " . $this->dboperation->dbparser->isNull("(CASE WHEN $status=' '
       THEN " . $this->dboperation->dbparser->isNull('(CASE WHEN ' . $this->dboperation->dbparser->isNull('ae.estatus', "' '")
       . ' IN (SELECT ' . $this->dboperation->dbparser->isNull('ag.status', "' '") . ' FROM anw_hl_gruende ag WHERE
       ' . $this->dboperation->dbparser->isNull('ag.anwesend', "' '") . "='W')
       THEN 'W' ELSE 'F' END)", "'F'")
       . 'ELSE ' . $this->dboperation->dbparser->isNull('(CASE WHEN ' . $this->dboperation->dbparser->isNull('ae.estatus', "' '") . "=$status
        THEN 'W' ELSE 'F' END)", "'F'") . ' END)', "'F'")
      . ')
    )';
    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $this->dboperation->selectrow(0);

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

  
  public function getAbwTage($pBelegID, $pVon, $pBis, $pKnr, $pStatus)
  {
    $vonIstInteressant = true;
    if (DateTimeOperations::isDateTimeEmptyOrDefault($pVon) || $pVon === 'null') {
      $vonIstInteressant = false;
    }
    $bisIstInteressant = true;
    if (DateTimeOperations::isDateTimeEmptyOrDefault($pBis) || $pBis === 'null') {
      $bisIstInteressant = false;
    }

    $defaultDateInDbFormat = $this->datetimeop->getFormatDateTime4Server(DEFAULTDATE . ' ' . DEFAULTTIME_WITH_SECONDS);
    $invalidDateInDbFormat = $this->datetimeop->getInvalidDate4Server();
    
    $sql = "SELECT count(*) as anzahl
    FROM belegung b
    INNER JOIN kurstage kt ON (kt.knr=b.knr)
    LEFT OUTER JOIN anw a ON ((a.aktid=kt.id) AND (a.abelegid=b.id))
    left outer join anw_extra ae on ae.eanwid = a.id
    WHERE (b.id = $pBelegID) ";
    
    
    if ($vonIstInteressant) {
      $sql .= ' AND (kt.datum>=' . $this->datetimeop->getFormatDate4Server($pVon) . ')';
    }
    
    
    if ($bisIstInteressant) {
      $sql .= ' AND (kt.datum<=' . $this->datetimeop->getFormatDate4Server($pBis) . ')';
    }
    $sql .= ' AND (' . $this->getKurstagEinschraenkung() . ')
    AND (((SELECT ' . $this->dboperation->dbparser->isNull('s234', "'F'") . " FROM switch)<>'W') OR (" . $this->dboperation->dbparser->isNull('kt.fachid', '0') . '=0) OR
     (' . $this->dboperation->dbparser->isNull('kt.fachid', '0') . ') IN (SELECT tf.fach_id FROM tn_fach tf WHERE (tf.beleg_id=b.id)))';
    
    $sql .= ' AND (((' . $this->dboperation->dbparser->isNull('b.eintritt', $defaultDateInDbFormat) . "=$defaultDateInDbFormat"
      . ') OR (' . $this->dboperation->dbparser->isNull('b.eintritt', $defaultDateInDbFormat) . '<='
      . $this->dboperation->dbparser->isNull('kt.datum', $defaultDateInDbFormat) . ')))';
    
    $sql .= ' AND ((' . $this->dboperation->dbparser->isNull('b.gekuendigt_zum', $defaultDateInDbFormat) . "=$defaultDateInDbFormat)"
      . ($invalidDateInDbFormat != '' ? ' OR b.gekuendigt_zum = ' . $invalidDateInDbFormat : '')
      . ' OR (' . $this->dboperation->dbparser->isNull('b.gekuendigt_zum', $defaultDateInDbFormat) . '>'
      . $this->dboperation->dbparser->isNull('kt.datum', $defaultDateInDbFormat) . '))'
      . ' AND ((' . $this->dboperation->dbparser->isNull('b.austritt', $defaultDateInDbFormat) . '=' . $defaultDateInDbFormat . ')'
      . ($invalidDateInDbFormat != '' ? ' OR b.austritt = ' . $invalidDateInDbFormat : '')
      . ' OR (' . $this->dboperation->dbparser->isNull('b.austritt', $defaultDateInDbFormat) . '>='
      . $this->dboperation->dbparser->isNull('kt.datum', $defaultDateInDbFormat) . '))';

    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $this->dboperation->selectrow(0);
    $tageDieErDaGewesenSeinMuesste = $this->dboperation->fieldbyname('anzahl');

    return $tageDieErDaGewesenSeinMuesste - $this->getAnwTage($pBelegID, $pVon, $pBis, $pKnr, $pStatus);
  }

  
  protected function getKurstagEinschraenkung()
  {
    $dbwHlGrundSys = new DBWorkHLGrundSys();
    $anwKurstageX = $dbwHlGrundSys->getFeldWert('ANW_KURSTAGEX');

    $ktStatusAusf = "'A'";
    if ($anwKurstageX === 'W') {
      $ktStatusAusf .= ", 'X', '!'";
    }

    return $this->dboperation->dbparser->isNull('kt.ausf', "' '") . 'NOT IN (' . $ktStatusAusf . ')';
  }
}