<?php
/**
 * @copyright (c) Kufer Software GmbH, Loisachstrasse 4, D-84453 Mühldorf
 */


class DBWorkKurseTageWT extends DBWorkKurseTage
{
  public $toolsApi;

  public function __construct($pDBTyp = 1, $newInstance = false, $pAddKurstagWithSelectedStatus = true)
  {
    parent::__construct($pDBTyp, $newInstance, false, $pAddKurstagWithSelectedStatus);

    $this->toolsApi = new ToolsAPI();
  }

  
  
  public function getCurrentRow($pRefreshCache = false)
  {
    $tmp = parent::getCurrentRow($pRefreshCache);
    if ($tmp->dozent->webdoznichtkurs == 'W' || $tmp->dozent->nointernet) {
      $tmp->dozent = new Dozent();
    }

    return $tmp;
  }

  
  public function getJoinPartner($pOnlyCurrentLoggedInDozent = false)
  {
    $sqlstr = '';
    if (!$this->admin) {
      if ($pOnlyCurrentLoggedInDozent) {
        $sqlstr .= " INNER JOIN web_users wu ON (kt.dnr = wu.dnr) \n";
      }
    }

    return $sqlstr;
  }

  
  public function getKurstagebyKursNR($pKurs_Id, $pRestrictKt = -1, $pOnlyCurrentLoggedInDozent = false, $pOnlyDifferentKt = false)
  {
    if ($pRestrictKt !== false && $pRestrictKt >= 0) {
      $heute = $this->datetimeop->getDateNow();
      $vondat = $this->datetimeop->getDateDaysBefore($heute, $pRestrictKt);
      $this->fromobject->datum = $vondat;
    }

    $kurs_id = $this->dboperation->getString4Sql($pKurs_Id);
    $sql = 'SELECT ' . $this->select . " \n"
      . "  FROM kurstage kt \n" . $this->getJoinPartner($pOnlyCurrentLoggedInDozent)
      . "  WHERE (kt.knr = '" . $kurs_id . "'";


    if ($this->dboperation->subselectPossible() && ToolSettingsAPI::getMitUnterKursenSetting()) {
      
      $sql .= ' OR kt.knr IN (' . SQLOperations::unterKursFromKursGruppePart($kurs_id) . ') ';
    }

    $sql .= ' ) ';
    if (!$this->admin && $pOnlyCurrentLoggedInDozent) {
      $userid = $this->dboperation->getInt4Sql(SessionOperations::getUserID());
      $sql .= ' AND wu.id=' . $userid . ' ';
    }

    $sql .= $this->getSearchSQL('kt.') . " \n "
    . $this->getCompleteOrderByDefault(array('datum', 'zeit_beg'), 'kt');

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

    $ktAry = $this->getAllRows();
    if ($pOnlyDifferentKt) {
      $tmpAry = array();
      foreach ($ktAry as $key => $kt) {
        $tmp = $kt;
        unset($ktAry[$key]); 
        if ($this->_isKurstagDifferentWT($ktAry, $tmp)) {
          $tmpAry[] = $tmp;
        }
      }

      return $tmpAry;
    }

    return $ktAry;
  }

  
  private function getSQLPart4KursNrDozID($pKursNr, $pDozID)
  {
    $kursNr = $this->dboperation->getString4Sql($pKursNr);
    $dozID = $this->dboperation->getInt4Sql($pDozID);
    $sql = 'SELECT ' . $this->select . " \n"
           . "FROM kurstage kt \n"
           . "JOIN kurse k on k.nr = kt.knr \n"
           . 'WHERE kt.dnr = ' . $dozID . " \n"
           . "AND ( kt.knr = '" . $kursNr . "'\n";

    if ($this->dboperation->subselectPossible() && ToolSettingsAPI::getMitUnterKursenSetting()) {
      
      $sql .= ' OR kt.knr IN (' . SQLOperations::unterKursFromKursGruppePart($kursNr) . ')';
    }

    $sql .= ') ' . $this->getSearchSQL('kt.') . " \n";
    $sql .= $this->getCompleteOrderByDefault(array('datum', 'zeit_beg'), 'kt');

    return $sql;
  }

  
  public function getKurstageByKursNRDozID($pKursNr, $pDozID)
  {
    $sql = $this->getSQLPart4KursNrDozID($pKursNr, $pDozID);

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

    return $this->getAllRows();
  }

  
  public function getKurstageIdsByKursNrDozID($pKursNr, $pDozID)
  {
    $sql = $this->getSQLPart4KursNrDozID($pKursNr, $pDozID);

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

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

  
  public function getKurstageIdsByDozID($pDozID)
  {
    $sqlstr = 'SELECT kt.id'
        . ' FROM kurstage kt LEFT JOIN kurse k ON(kt.knr=k.nr) ';
    $sqlstr .= ' WHERE 1=1 ' . $this->getSearchSQL('kt.');

    if (!$this->admin) {
      $dozId = $this->dboperation->getInt4Sql($pDozID);
      $sqlstr .= '  AND kt.dnr=' . $dozId . ' ';
    }

    $sqlstr .= $this->getCompleteOrderByDefault('knr', 'kt');
    if ($this->getOrderbyDefault('id') != 'id') {
      $sqlstr .= ', kt.id';
    }

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

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

  
  public function getKurstageByTnLimit($pTnr, $pStartKt = null, $pLimit = 0, $pForward = true, $pKursNrAry = null)
  {
    $tmp = parent::getKurstageByTnLimit($pTnr, $pStartKt, $pLimit, $pForward, $pKursNrAry);

    return $this->_getBelegungsInfo4KurstageByTnLimit($tmp, $pTnr);
  }

  
  public function getKurstageFromIDs4Tn($pKurstageIDAry, $pTnr, $pRefreshCache = false)
  {
    $tmp = parent::getKurstageFromIDs($pKurstageIDAry, $pRefreshCache);

    return $this->_getBelegungsInfo4KurstageByTnLimit($tmp, $pTnr);
  }

  
  public function getKurstageByTnLimitOnlyDifferent($pTnr, $pStartKt, $pLimit = 0, $pForward = true, $pKursNrAry = null)
  {
    $tmp = parent::getKurstageByTnLimitOnlyDifferent($pTnr, $pStartKt, $pLimit, $pForward, $pKursNrAry);

    return $this->_getBelegungsInfo4KurstageByTnLimit($tmp, $pTnr);
  }

  public function getKurstageByTnAndBelegungenQuery($tnr, $belegungen): \SelectQueryBuilder
  {
    $builder = $this->getKurstageByTnLimitQuery($tnr, \array_unique(\ArrayOperations::mapAttribut($belegungen, 'knr')));

    return $builder->andExpr(function(\SelectQueryBuilder $builder) {
      return $builder
        ->or('kt.fachid IN (:subSelectFachQuery:)')
        ->or('kt.fachid = 0')
        ->or('kt.fachid IS NULL')
        ->paramSelectQuery(
          'subSelectFachQuery',
          (new \DBWorkTNFach())->getAllQuery()
            ->select('tnf.fach_id')
            ->and('tnf.beleg_id = b.id')
        );
    });
  }

  public function getKurstageByTnAndBelegungen($tnr, $belegungen): array
  {
    $builder = $this->getKurstageByTnAndBelegungenQuery($tnr, $belegungen);

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

    $belegungenMappedByKursNr = \ArrayOperations::setAttributAsKey($belegungen, 'knr');
    $result = $this->getAllRows();
    foreach ($result as $kurstag) {
      $kurstag->belegung = $belegungenMappedByKursNr[$kurstag->knr];
    }

    return $result;
  }

  public function getKurstageIdsByTnAndBelegungen($tnr, $belegungen): array
  {
    $builder = $this->getKurstageByTnAndBelegungenQuery($tnr, $belegungen)
      ->select('kt.id');

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

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

  
  public function getKurstageIdsByTNOnlyDifferent($pTnr, $pKursNrAry = null)
  {
    $select = $this->select;
    $this->select = $this->minselect; 
    $ktAry = parent::getKurstageByTnLimitOnlyDifferent($pTnr, null, 0, true, $pKursNrAry);
    $this->select = $select;

    $return = array();
    foreach ($ktAry as $kt) {
      $return[] = $kt->id;
    }

    return $return;
  }

  
  private function _getBelegungsInfo4KurstageByTnLimit($pKurstage, $pTnr)
  {
    $dbwBelegung = new DBWorkBelegung($this->dbtyp, true);
    $kurstagCount = count($pKurstage);
    for ($i = 0; $i < $kurstagCount; $i++) {
      $belegungen = $dbwBelegung->getBelegungenbyKursTeilnehmerNR($pKurstage[$i]->knr, $pTnr);
      $pKurstage[$i]->belegung = $belegungen[0];
    }

    return $pKurstage;
  }

  public function isDozentLeiterFuerKTinZeitraum($pDozid, $pVonDat, $pBisDat)
  {
    
    
    $vondat = $this->dboperation->getString4Sql($pVonDat);
    $bisdat = $this->dboperation->getString4Sql($pBisDat);
    $dozid = $this->dboperation->getInt4Sql($pDozid);

    $sql = '';
    $sql .= ' SELECT count(*) AS anzahl';
    $sql .= ' FROM kurstage kt JOIN doz_kurs dk ON kt.knr = dk.kurs';
    if (DateTimeOperations::isValidDate($vondat)) {
      $sql .= ' WHERE kt.datum >= ' . $this->datetimeop->getFormatDate4Server($vondat);
    }
    if (DateTimeOperations::isValidDate($bisdat)) {
      $sql .= ' AND kt.datum <= ' . $this->datetimeop->getFormatDate4Server($bisdat);
    }
    $sql .= ' AND ' . $this->dboperation->dbparser->isNull('dk.isleiter', '\'F\'') . '=\'W\' AND dk.dozent = ' . $dozid;
    $sql .= ' AND ' . $this->dboperation->dbparser->isStringEmptyOrNull('dk.art'); 
    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $this->dboperation->selectrow(0);
    
    return ($this->getDatamodulValue('anzahl') > 0);
  }

  
  public function isKurstagAbgeschlossen($pKtid, $pDate, $pKnr)
  {
    $ktid = $this->dboperation->getInt4Sql($pKtid);
    $date = $this->dboperation->getString4Sql($pDate);
    $knr = $this->dboperation->getString4Sql($pKnr);
    $sql = '';
    $sql .= ' SELECT count(*) anzahl';
    $sql .= ' FROM belegung b ';
    $sql .= ' WHERE \'' . $knr . '\' = b.knr ';
    
    $sql .= ' AND b.status_1 IN (\'A\',\' \',\'\') ';
    
    if (DateTimeOperations::isValidDate($date)) {
      $sql .= ' AND( b.eintritt <= ' . $this->datetimeop->getFormatDate4Server($date);
      $sql .= ' OR b.eintritt = ' . $this->datetimeop->getFormatDate4Server(DEFAULTDATE);
      $sql .= ' OR b.eintritt is NULL )';

      $sql .= ' AND( b.austritt >= ' . $this->datetimeop->getFormatDate4Server($date);
      $sql .= ' OR b.austritt = ' . $this->datetimeop->getFormatDate4Server(DEFAULTDATE);
      $sql .= ' OR b.austritt is NULL )';
    }
    
    $sql .= 'AND (' . $this->dboperation->dbparser->isNull('(SELECT deputatid FROM kurstage WHERE id=' . $ktid . ')', 0) . ' = 0'


;

    $dbwKa = new DBWorkAnwesenheit2($this->dbtyp, true);
    
    $fachid = $dbwKa->getFachIdForKurstag($ktid);
    if ($fachid > 0) {
      $sql .= ' OR b.id IN (SELECT f.beleg_id FROM tn_fach f WHERE f.fach_id = ' . $fachid . ')';
    }
    $sql .= ') ';

    
    $sql .= 'AND NOT b.id IN (SELECT a.abelegid FROM ' . $dbwKa->getAnwTableName() . ' a WHERE ' . $ktid . ' = a.aktid)';
    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $this->dboperation->selectrow(0);

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

  
  public function getKurstageByKursnummern($pKursnummern)
  {
    $kursnummern = $this->dboperation->getStrings4Sql($pKursnummern);

    $sql = 'SELECT ' . $this->select . ' '
           . 'FROM kurstage kt '
           . 'WHERE kt.knr IN (\'' . implode('\',\'', $kursnummern) . '\')'
            . $this->getSearchSQL('kt.')
           . $this->getCompleteOrderByDefault(array('datum', 'zeit_beg'), 'kt');

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

    return $this->getAllRows();
  }
}