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


class DBWorkGebaeude extends DBWork
{
  protected static $_cache_Gebaeude = array();
  
  public $dbadroperations;

  public function __construct($pDBTyp = 1, $pNewInstance = false)
  {
    
    parent::__construct($pDBTyp, $pNewInstance);
    $this->select = 'id, name, strasse, ort, hausmeister1, hausmeister2, hausmeister3,'
            . ' hausmadrnr, aussenstelle, hinweis1, hinweis2, email, internet, progheftname1,'
            . ' progheftname2, anfraumbez, telefon, fax, mobiltel, bus, bahn, sortierfeld,'
        . ' anfahrtlink, barrierefrei, ' . $this->clob('anfahrttext') . ', openstreetmapmlat, openstreetmapmlon';
    
    $this->dbadroperations = new DBWorkAdresse($pDBTyp, true);
  }

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

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

  
  public function getCurrentRow($pRefreshCache = false)
  {
    $tmp = $this->getNewObject();
    $tmp->id = $this->getDatamodulValue('id');
    $tmp->name = $this->getDatamodulValue('name');
    $tmp->strasse = $this->getDatamodulValue('strasse');
    $tmp->ort = $this->getDatamodulValue('ort');
    $tmp->hausmadrnr = $this->getDatamodulValue('hausmadrnr');
    $tmp->aussenstelle = $this->getDatamodulValue('aussenstelle');
    $tmp->hinweis1 = $this->getDatamodulValue('hinweis1');
    $tmp->hinweis2 = $this->getDatamodulValue('hinweis2');
    $tmp->progheftname1 = $this->getDatamodulValue('progheftname1');
    $tmp->progheftname2 = $this->getDatamodulValue('progheftname2');
    $tmp->email = $this->getDatamodulValue('email');
    $tmp->internet = $this->getDatamodulValue('internet');
    $tmp->anfraumbez = $this->getDatamodulValue('anfraumbez');
    $tmp->bus = $this->getDatamodulValue('bus');
    $tmp->bahn = $this->getDatamodulValue('bahn');
    $tmp->sortierfeld = $this->getDatamodulValue('sortierfeld');
    $tmp->anfahrtlink = $this->getDatamodulValue('anfahrtlink');
    $tmp->anfahrttext = $this->getDatamodulValue('anfahrttext');
    $tmp->barrierefrei = $this->getDatamodulValue('barrierefrei');
    $tmp->openstreetmapmlat = $this->getDatamodulValue('openstreetmapmlat');
    $tmp->openstreetmapmlon = $this->getDatamodulValue('openstreetmapmlon');
    
    
    if ($tmp->hausmadrnr <= 0) {
      
      $tmp->hausmeister1 = $this->getDatamodulValue('hausmeister1');
      $tmp->hausmeister2 = $this->getDatamodulValue('hausmeister2');
      $tmp->hausmeister3 = $this->getDatamodulValue('hausmeister3');
      $tmp->telefon = $this->getDatamodulValue('telefon');
      $tmp->fax = $this->getDatamodulValue('fax');
      $tmp->mobiltel = $this->getDatamodulValue('mobiltel');
    } else {
      
      $tmp = $this->fillAdrKommBlock2Gebaeude($tmp);
    }

    return $tmp;
  }

  public function getSearchSQLINetAktiv($praeffix)
  {
    $sql = ' ' . $this->dboperation->dbparser->isNull($praeffix . 'internetaktiv', '\'F\'') . ' = \'W\'';

    
    if (! (SessionOperations::isKuferTools())) {
      $typo3Filter = Typo3Filter::getInstance();
      $aussenstellenIds = $typo3Filter->getFilterAussenstellen();

      if (count($aussenstellenIds) > 0) {
        $sql .= ' AND ' . $praeffix . 'aussenstelle IN( SELECT a.bezeichn FROM aussenst a WHERE id IN ( ' . implode(', ', $aussenstellenIds) . ' ) )';
      }
    }

    return $sql;
  }

  
  public function fillAdrKommBlock2Gebaeude($pGeb)
  {
    assert($pGeb->hausmadrnr > 0);

    
    $adresse = $this->dbadroperations->getAdresseKomm($pGeb->hausmadrnr);
    $pGeb->hausmeister1 = trim($adresse->name . ' ' . $adresse->vorname);
    $pGeb->hausmeister2 = $adresse->strasse;
    $pGeb->hausmeister3 = $adresse->ort;
    $pGeb->telefon = $adresse->getVarValueEx('tel', 'kommunikation');
    $pGeb->fax = $adresse->getVarValueEx('fax', 'kommunikation');
    $pGeb->mobiltel = $adresse->getVarValueEx('mobil', 'kommunikation');

    return $pGeb;
  }

  
  public function getGebaeudeByKnr($pKnr)
  {
    $knr = $this->dboperation->getString4Sql($pKnr);
    $sql = 'SELECT ' . StringOperations::applyPrefix($this->select, 'g')
            . ' FROM kurse k left join kursorte ko on k.ort = ko.mktext '
            . 'left join gebaeude g on ko.schule = g.name '
            . 'WHERE k.nr = \'' . $knr . '\' AND ' . $this->getSearchSQLINetAktiv('g.');
    $this->dboperation->query($sql);

    return $this->getFirstRow();
  }

  
  public function getGebaeudeByKursortNr($pID)
  {
    if ($pID === '' || !is_numeric($pID)) {
      return $this->getNewObject();
    }
    $sql = 'SELECT ' . StringOperations::applyPrefix($this->select, 'g')
            . ' FROM kursorte ko left join gebaeude g on ko.schule = g.name '
            . 'WHERE ko.nr = ' . $pID . ' AND ' . $this->getSearchSQLINetAktiv('g.');
    $this->dboperation->query($sql);

    return $this->getFirstRow();
  }

  
  public function getGebaeudebyNr($pGebNr, $pRefreshCache = false)
  {
    $gebNr = $this->dboperation->getInt4Sql($pGebNr);


    if ($pRefreshCache || !array_key_exists($gebNr, self::$_cache_Gebaeude)) {
      $sql = 'SELECT ' . $this->select . ' FROM gebaeude g WHERE g.id = \'' . $gebNr . '\' AND ' . $this->getSearchSQLINetAktiv('g.');
      $this->dboperation->query($sql);
      self::$_cache_Gebaeude[$gebNr] = $this->getFirstRow();
    }

    return self::$_cache_Gebaeude[$gebNr];
  }

  
  public function getGetLetterList($pLetterAry = array(), $pSemList = '')
  {
    $sql = 'SELECT ' . $this->dboperation->dbparser->substring('name', 1, 1) . ' AS buchstabe, count(*) AS anzahl '
        . 'FROM (SELECT g.id as id, g.name as name FROM gebaeude g'
        . " WHERE g.InternetAktiv = 'W' "
        . ' GROUP BY g.id, g.name) y '
        . 'GROUP BY ' . $this->dboperation->dbparser->substring('name', 1, 1);
    $this->dboperation->clear();
    $this->dboperation->query($sql);

    
    $letterAry = $pLetterAry;


    for ($i = 0; $i < $this->dboperation->rowcount(); $i++) {
      $this->dboperation->selectrow($i);
      $letter = StringOperations::strtoupper(StringOperations::substr($this->getDatamodulValue('buchstabe'), 0, 1));
      $count = $this->getDatamodulValue('anzahl');
      $letterAry[$letter] = $count;
    }

    
    if (array_key_exists('Sch', $letterAry)) {
      $sql = 'SELECT count(*) as anzahl FROM gebaeude g WHERE g.name like \'Sch%\' group by g.id';
      $this->dboperation->clear();
      $this->dboperation->query($sql);
      $this->dboperation->selectrow(0);
      $letterAry['Sch'] = $this->getDatamodulValue('anzahl');
      $letterAry['S'] = $letterAry['S'] - $letterAry['Sch'];
    }
    if (array_key_exists('St', $letterAry)) {
      $sql = 'SELECT count(*) as anzahl FROM gebaeude g where g.name like \'St%\' group by g.id';
      $this->dboperation->clear();
      $this->dboperation->query($sql);
      $this->dboperation->selectrow(0);
      $letterAry['St'] = $this->getDatamodulValue('anzahl');
      $letterAry['S'] = $letterAry['S'] - $letterAry['St'];
    }


    
    $dmArray = array();
    ksort($letterAry, SORT_STRING);
    foreach ($letterAry as $letter => $count) {
      $dm = new Buchstabe();
      $dm->buchstabe = $letter;
      $dm->anzahl_datensaetze = $count;
      array_push($dmArray, $dm);
    }

    return $dmArray;
  }

  
  public function getGebaeudeGruppiert($pLetter, $pSemlist, $pNurS = false)
  {
    $sPart = '';
    if ($pNurS && $pLetter === 'S') {
      $sPart = ' and not g.name like \'Sch%\' and not g.name like \'St%\' ';
    }
    $letter = $this->dboperation->getString4Sql($pLetter);
    $sql = 'SELECT ' . $this->select . " FROM gebaeude g WHERE g.name like '" . $letter . "%'" . $sPart . ' ORDER BY g.name';
    $this->dboperation->clear();
    $this->dboperation->query($sql);

    return $this->getAllRows();
  }

  
  public function getGebaeude()
  {
    $this->dboperation->clear();
    $sql = 'SELECT ' . $this->select . ' FROM gebaeude WHERE '
          . $this->getSearchSQLINetAktiv('') . ' ORDER BY sortierfeld, name';
    $this->dboperation->query($sql);

    return $this->getAllRows();
  }

  public function getGebaeudemitKursen()
  {
    $dbworkkurse = new DBWorkKurseINetAktiv();
    $dbworkkurse->newSearch();

    $qb = $this->getSelectQueryBuilder();
    $sql = $qb->select('DISTINCT k.nr')
      ->from('kurse k')
      ->where('1=1 ' . $dbworkkurse->getSearchSQL('k.'))
      ->getSQL();

    $query = $this->getSelectQueryBuilder();
    $sql2 = $query->select('DISTINCT ' . StringOperations::applyPrefix($this->select, 'g'))
      ->from('kurse k')
      ->leftJoin('kursorte ko', 'k.ort = ko.mktext')
      ->leftJoin('gebaeude g', 'ko.schule = g.name')
      ->where('k.nr IN (:knrary:)')
      ->and($this->getSearchSQLINetAktiv('g.'))
      ->paramRaw('knrary', $sql)
      ->getSQL();

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

    return $this->getAllRows();
  }

  public function getGebaeudeLimitAktSemester(
    ?string $firstId,
    string $semlist,
    int $limit = 0,
    bool $forward = true
  ): array {
    if ($semlist == '') {
      return $this->getGebaeudeLimit($firstId, $limit, $forward);
    }

    $semlistIn = StringOperations::convertCommaSeparatedList2InList($semlist);
    
    $qb = $this->getSelectQueryBuilder();
    $sql = $qb->select('g.*')
                ->from('kurstage kt')
                ->join('kurse k', 'kt.knr = k.nr')
                ->join('kursorte ko', 'kt.ort = ko.mktext')
                ->join('gebaeude g', 'ko.schule = g.name')
                ->where('k.semester IN (:semester:)')
                ->addToWhere($this->getSearchSQL('g.'))
                ->groupBy('ko.schule')
                ->orderByAsc('g.sortierfeld')
                ->orderByAsc('g.name')
                ->paramRaw('semester', $semlistIn)
                ->getSQL();

    return $this->queryLimitRow($sql, $firstId, $limit, $forward);
  }

  
  public function getGebaeudeLimit(?string $firstId, int $limit = 0, bool $forward = true): array
  {
    
    $qb = $this->getSelectQueryBuilder();
    $sql = $qb->select($this->select)
              ->from($this->getTableName())
              ->where($this->getSearchSQLINetAktiv(''))
              ->orderByAsc('sortierfeld, name')
              ->getSQL();

    return $this->queryLimitRow($sql, $firstId, $limit, $forward);
  }

  private function queryLimitRow(string $sql, ?string $firstId, int $limit = 0, bool $forward = true): array
  {
    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $this->_buildSeitenIndexAry($this->dboperation->rowcount(), $limit);
    if (!is_null($firstId) && $firstId != '') {
      return $this->getLimitRows($limit, $forward, 'id', $firstId);
    } else {
      return $this->getLimitRows($limit, $forward);
    }
  }

  
  public function getGebaeudebyAussenstLimit($pAussenst, $limit = 0, $start_haus = null, $forward = true)
  {
    $aussenst = $this->dboperation->getString4Sql($pAussenst);
    $sql = 'SELECT ' . StringOperations::applyPrefix($this->select, 'g') . ' FROM gebaeude g '
           . 'WHERE g.aussenstelle = \'' . $aussenst . '\' AND '
          . $this->getSearchSQLINetAktiv('') . ' ORDER BY sortierfeld, name';
    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $this->_buildSeitenIndexAry($this->dboperation->rowcount(), $limit);
    if ($start_haus != null && isset($start_haus->id) && $start_haus->id != '') {
      $tmp = $this->getLimitRows($limit, $forward, 'id', $start_haus->id);
    } else {
      $tmp = $this->getLimitRows($limit, $forward);
    }

    return $tmp;
  }

  
  public function getCountGebaeude()
  {
    $sql = 'SELECT count(*) as anzahl From gebaeude WHERE ' . $this->getSearchSQLINetAktiv('');
    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $this->dboperation->selectrow(0);

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

  
  public function getCountGebaeudebyAussenst($pAussenst)
  {
    $aussenst = $this->dboperation->getString4Sql($pAussenst);
    $sql = 'SELECT count(*) as anzahl From gebaeude g WHERE g.aussenstelle = \'' . $aussenst . '\'
    AND ' . $this->getSearchSQLINetAktiv('');
    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $this->dboperation->selectrow(0);

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