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

class DBWorkKurszahlplan extends DBWork
{
  
  public $mitKinderZahlplan = true;

  
  public function __construct($pDBTyp = 1, $pNewInstance = false)
  {
    parent::__construct($pDBTyp, $pNewInstance);
    $this->select = 'kag.id, kag.knr, kag.beschreibung, kag.webdarst, kag.tn_von, kag.tn_bis, kag.zusatz, kag.sort ';
    $this->orderby = 'sort';
  }

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

  
  public function getCurrentRow($pRefreshCache = false)
  {
    $tmp = $this->getNewObject();
    $tmp->id = $this->getDatamodulValue('id');
    $tmp->knr = $this->getDatamodulValue('knr');
    $tmp->beschreibung = $this->getDatamodulValue('beschreibung');
    $tmpWebdarst = $this->getDatamodulValue('webdarst');
    $tmp->webdarst = $tmpWebdarst !== 'F';
    $tmp->tn_bis = $this->getDatamodulValue('tn_bis');
    $tmp->tn_von = $this->getDatamodulValue('tn_von');
    $tmp->zusatz = $this->getDatamodulValue('zusatz');
    
    
    
    $dbwkg = new DBWorkKursgebuehr(1, true);
    $knr = $tmp->knr;
    $knr = StringOperations::rpad($knr, 10, ' ');
    $knr .= $tmp->id;
    $tmp->kursgebuehren = $dbwkg->getGebuehrenbyKNR($knr);

    return $tmp;
  }

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

  
  public function getAnzahlKurszahlplaeneByKnr($pKnr)
  {
    $knr = $this->dboperation->getString4Sql($pKnr);
    $this->dboperation->clear();
    $sql = 'SELECT count(kag.id) as summe FROM kurs_abwgeb kag WHERE knr like \'' . $knr . '\'' . $this->getWBEinschraenkung() . $this->getKinderZahlplanEinschraenkung();
    $this->dboperation->query($sql);
    $this->dboperation->selectrow(0);
    $anz = $this->getDatamodulValue('summe');
    $this->dboperation->clear();

    return $anz;
  }

  protected function getWBEinschraenkung()
  {
    return ' AND ' . $this->dboperation->dbparser->isNull('webdarst', "'W'") . ' <> \'F\'';
  }

  protected function getKinderZahlplanEinschraenkung()
  {
    if ($this->mitKinderZahlplan) {
      return '';
    } else {
      return ' AND ' . $this->dboperation->dbparser->isNull('zusatz', "'F'") . ' <> \'K\'';
    }
  }

  
  public function getAnzahlKurszahlplaene4Grillhuette($pKnr)
  {
    $knr = $this->dboperation->getString4Sql($pKnr);
    $this->dboperation->clear();
    $sql = 'SELECT count(kag.id) as summe FROM kurs_abwgeb kag INNER JOIN kurse k on kag.knr = k.nr WHERE k.typ = \'B\' AND knr like \'' . $knr . '\'';
    
    $this->dboperation->query($sql);
    $this->dboperation->selectrow(0);

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

  
  public function getKurszahlplaeneByKnr($pKnr, $pRefreshcache = false)
  {
    
    static $zahlplan_cache = array();
    $knr = $this->dboperation->getString4Sql($pKnr);
    $cacheKey = ($this->mitKinderZahlplan ? 'MK_' : 'OK_') . $knr;
    if (!$pRefreshcache && array_key_exists($cacheKey, $zahlplan_cache)) {
      return $zahlplan_cache[$cacheKey];
    }

    $this->dboperation->clear();
    $sql = 'SELECT ' . $this->select . ' FROM kurs_abwgeb kag '
            . 'WHERE knr like \'' . $knr . '\'' . $this->getWBEinschraenkung() . $this->getKinderZahlplanEinschraenkung() . ' ORDER BY ' . $this->orderby;
    $this->dboperation->query($sql);

    $zahlplan_cache[$cacheKey] = $this->getAllRows();

    return $zahlplan_cache[$cacheKey];
  }

  
  public function getKurszahlplaene4Grillhuette($pIsSommerSaison, $pIsWochenEnde, $pKnr, $pFrei, $pRefreshcache = false)
  {
    static $zahlplan_cache = array();
    if (!$pRefreshcache && array_key_exists($pKnr, $zahlplan_cache)) {
      return $zahlplan_cache[$pKnr];
    }

    $knr = $this->dboperation->getString4Sql($pKnr);
    $this->dboperation->clear();
    $sql = 'SELECT ' . $this->select . ' '
           . 'FROM kurs_abwgeb kag '
           . 'WHERE knr like \'' . $knr . '\''
             . ' AND ' . $this->dboperation->dbparser->caseInsensitiveLike('', 'beschreibung', '%' . ($pIsSommerSaison ? 'sommer' : 'winter') . '%')
             . ' AND (' . $this->dboperation->dbparser->caseInsensitiveLike('', 'beschreibung', '%' . ($pIsWochenEnde ? 'wochenende' : 'werktag') . '%')
             . '  OR ' . $this->dboperation->dbparser->caseInsensitiveLike('', 'beschreibung', '%' . ($pIsWochenEnde ? 'Fr%-%So' : 'Mo%-%Do') . '%') . ')'
             . (trim($pFrei) !== '' ? ' AND ' . $this->dboperation->dbparser->caseInsensitiveLike('', 'beschreibung', '%' . trim($pFrei) . '%') : '')
           . ' ORDER BY ' . $this->orderby;
    
    $this->dboperation->query($sql);

    $zahlplan_cache[$knr] = $this->getAllRows();

    return $zahlplan_cache[$knr];
  }

  public function getKurszahlplanById($id, $knr, $pRefreshcache = false)
  {
    $zahlplaene = $this->getKurszahlplaeneByKnr($knr, $pRefreshcache);
    foreach ($zahlplaene as $plan) {
      if ((int)$plan->id === (int)$id) {
        return $plan;
      }
    }

    return false;
  }

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

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

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

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

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

    return $this->getAllRows();
  }

  
  public function findOneByFilter(\Kufer\Web\Classes\Dbfilter\KursZahlplaeneSQLFilter $filter): ?\Kurszahlplan
  {
    $builder = $this->findByFilterQuery($filter);

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

    return $this->getFirstRowOrNull();
  }

  
  public function countByFilter(\Kufer\Web\Classes\Dbfilter\KursZahlplaeneSQLFilter $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);
  }
}