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






class DBWorkKursgebuehr extends DBWork
{
  protected static $_cache_GebuehrenByKNR = array();
  protected static $_cache_GebuehrenAllPartsByKnr = array();

  private $stdFrom;

  public function __construct($pDBnr = 1, $pNewInstance = false)
  {
    parent::__construct($pDBnr, $pNewInstance);
    $this->select = 'id, knr, schluessel, bezeichnung, ermaessigbar, gebuehr, erm_geb, gebart, dp, mwst, faelligkeit, kostenst, konto, fortlaufend, pauschalgebuehr ';
    $this->select = StringOperations::applyPrefix($this->select, 'kg');
    $this->select = $this->select . ', hl.mit_faktor';
    $this->stdFrom = 'kurs_geb kg left outer join hlgebarten hl on kg.gebart = hl.schluessel ';
  }

  

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

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

  
  public function getCurrentRow($pRefreshCache = false)
  {
    $tmp = $this->getNewObject();
    $tmp->id = $this->getDatamodulValue('id');
    $tmp->knr = $this->getDatamodulValue('knr');
    $tmp->schluessel = $this->getDatamodulValue('schluessel');
    $tmp->text = $this->getDatamodulValue('bezeichnung');
    $tmp->ermaessigbar = ($this->getDatamodulValue('ermaessigbar') == 'F');
    $tmp->geb_ermaess = $this->getDatamodulValue('ermaessigbar');
    $tmp->betrag = $this->getDatamodulValue('gebuehr');
    $tmp->betrag_ermaessigt = $this->getDatamodulValue('erm_geb');
    $tmp->gebart = $this->getDatamodulValue('gebart');
    $tmp->dp = $this->getDatamodulValue('dp');
    $tmp->mwst = $this->getDatamodulValue('mwst');
    $tmp->faelligkeit = $this->getDatamodulValue('faelligkeit');
    $tmp->kostenst = $this->getDatamodulValue('kostenst');
    $tmp->konto = $this->getDatamodulValue('konto');
    $tmp->fortlaufend = $this->getDatamodulValue('fortlaufend');
    $tmp->pauschalgebuehr = $this->getDatamodulValue('pauschalgebuehr');
    $tmp->mit_faktor = $this->getDatamodulValue('mit_faktor');

    return $tmp;
  }

  
  public function getGebuehrenbyKNR($pKurs_Id, $pRefreshCache = false)
  {
    $kurs_id = $this->dboperation->getString4Sql($pKurs_Id);

    if ($pRefreshCache || !array_key_exists($kurs_id, self::$_cache_GebuehrenByKNR)) {
      $this->dboperation->clear();
      $sql
        = "SELECT $this->select \n"
        . "FROM $this->stdFrom\n"
        . "WHERE kg.knr = '" . $kurs_id . "' \n " . $this->getSearchSQL('kg.') . "\n"
        . $this->getCompleteOrderByDefault('id', 'kg');
      $this->dboperation->query($sql);
      self::$_cache_GebuehrenByKNR[$kurs_id] = $this->getAllRows();
    }
    
    
    return unserialize(serialize(self::$_cache_GebuehrenByKNR[$kurs_id]));
  }

  
  public function getGebuehrenByKnrs($pKursIds, $pRefreshCache = false)
  {
    if (!is_array($pKursIds)) {
      throw new InvalidArgumentException('$pKursIds ist kein Array');
    }
    $fromCache = array();
    $knrsToFetch = array();
    foreach ($pKursIds as $knr) {
      $knr = $this->dboperation->getString4Sql($knr);
      if ($pRefreshCache || !isset(self::$_cache_GebuehrenByKNR[$knr])) {
        $knrsToFetch[] = $knr;
        $fromCache[$knr] = array();
      } else {
        $fromCache[$knr] = self::$_cache_GebuehrenByKNR[$knr];
      }
    }
    if (count($knrsToFetch) === 0) {
      return $fromCache;
    } 

    $sql = 'SELECT ' . $this->select . ' FROM ' . $this->stdFrom . 'WHERE kg.knr in (\'' . implode("','", $knrsToFetch) . "') " . $this->getSearchSQL('kg.')
        . $this->getCompleteOrderByDefault('id', 'kg');
    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $gebCount = $this->dboperation->rowcount();
    $fromdb = array();
    if ($gebCount === 0) {
      return $fromCache;
    } else {
      for ($i = 0; $i < $gebCount; $i++) {
        $this->dboperation->selectrow($i);
        $tmp = $this->getCurrentRow();
        
        
        
        if (!isset($fromdb[$tmp->knr])) {
          $fromdb[$tmp->knr] = array();
        }
        $fromdb[$tmp->knr][] = $tmp;
      }
    }
    
    foreach ($fromdb as $knr => $gebs) {
      self::$_cache_GebuehrenByKNR[$knr] = $gebs;
    }

    return array_merge($fromCache, $fromdb);
  }

  

  public function getGebuehrSummebyKNRGebart($pKurs_Id, $pGebArt, $erm = false)
  {
    $kurs_id = $this->dboperation->getString4Sql($pKurs_Id);
    $gebart = $this->dboperation->getInt4Sql($pGebArt);
    $gebuehrenSpalte = $erm ? 'erm_geb' : 'gebuehr';
    $this->dboperation->clear();
    $this->dboperation->query("SELECT SUM(kg.{$gebuehrenSpalte}) summe \n
                               FROM kurs_geb kg \n
                               WHERE kg.knr ='" . $kurs_id . "' AND kg.gebart = '" . $gebart . "' \n "
                               . $this->getSearchSQL('kg.'));
    $this->dboperation->selectrow(0);

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

  
  public function getGebuehrSummebyKNRGebartAllZahlplaene($pKurs_Id, $pGebArt)
  {
    $kurs_id = $this->dboperation->getString4Sql($pKurs_Id);
    $gebart = $this->dboperation->getInt4Sql($pGebArt);
    $knr = Kufer\Web\Classes\Api\Helpers\ZahlplanHelper::computeKnr($kurs_id, '%');
    $sql = 'SELECT sum(kg.gebuehr) as summe, kg.knr FROM kurs_geb kg '
        . "WHERE kg.knr like '$knr' AND kg.gebart = '$gebart' \n "
    . $this->getSearchSQL('kg.') . ' group by kg.knr';
    $this->dboperation->clear();
    $this->dboperation->query($sql);

    $count = $this->dboperation->rowcount();
    $result = array();
    for ($i = 0; $i < $count; $i++) {
      $this->dboperation->selectrow($i);
      $fullknr = $this->dboperation->fieldbyname('knr');
      if (strlen($fullknr) > 10) {
        $zpid = StringOperations::substr($fullknr, 10);
        $result[] = array('zpid' => $zpid, 'sum' => $this->dboperation->fieldbyname('summe'));
      }
    }

    return $result;
  }

  
  public function getGebuehrSummebyKNR($pKurs_Id)
  {
    $kurs_id = $this->dboperation->getString4Sql($pKurs_Id);
    $this->dboperation->clear();
    $this->dboperation->query("SELECT SUM(kg.gebuehr) summe \n
                               FROM kurs_geb kg \n
                               WHERE kg.knr ='" . $kurs_id . "' \n " . $this->getSearchSQL('kg.'));
    $this->dboperation->selectrow(0);

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

  public function isKursErmaessigbar(string $knr): bool
  {
    $gebuehren = $this->getGebuehrenbyKNR($knr);
    foreach ($gebuehren as $geb) {
      if ($geb->ermaessigbar == true) {
        return true;
      }
    }

    return false;
  }

  
  public function getKursgebuehrenErmaessigtTab($pKnr, $pTnr, $pPreisgr = '', $pErmanm = '', $pDatum = '', $pTn = null, $pZahlplanKursnummer = '')
  {
    if ($pZahlplanKursnummer == '') {
      $pZahlplanKursnummer = $pKnr;
    }
    
    $dbwerm = new DBWorkErmaessigung($this->dbtyp, true);
    if (is_null($pTn)) {
      $dbwtn = new DBWorkTeilnehmer($this->dbtyp, true);
      $tn = $dbwtn->getTeilnehm($pTnr);
    } else {
      $tn = $pTn;
    }
    $gebary = $this->getGebuehrenbyKNR($pZahlplanKursnummer); 
    
    $gebary = array_filter($gebary, function($gebuehr) { return $gebuehr->fortlaufend == ''; });
    
    $dbwGrundsys = new DBWorkBASysSettings(1, true);
    $grundsys = $dbwGrundsys->getSettings();

    $retour = array();

    $erm_proz = 0.0;

    
    if ($pErmanm != '' && $pErmanm > 0) {
      $erm = $dbwerm->getErmaessigungbyNr($pErmanm);
      $erm_proz = $erm[0]->prozente;
    } else {
      $erm_proz = $tn->erm_proz;
    }

    
    
    if (
      (
        (
          ($tn->erm_grund >= 1)
          && $dbwerm->isErmaessigungStillValid($pKnr, $tn->erm_ende, $grundsys->beruecksichtigeErmaessigungBeiKursbeginn)
          && APIGebuehren::greiftErmaessigungBeiDiesenKurs($pKnr, $tn->erm_grund)
        )
        
        
         || ($pErmanm > 0)
      )
      && ($pErmanm !== 0)) {
      $reason = '';
      if ($tn->erm_grund > 0) {
        $reason = " (erm. $tn->erm_grund)";
      }
      if ($erm_proz > 0) {
        if ($erm_proz == 33) {
          $erm_proz = 33.3333333333;
        }
        if ($erm_proz == 66) {
          $erm_proz = 66.6666666666;
        }

        
        foreach ($gebary as $geb) {
          
          
          $tmp = $this->_getStdTmpGeb($geb);
          if ($geb->ermaessigbar) {
            
            
            $tmp->gebuehr = $geb->betrag - MathOperations::allgRund(($geb->betrag * ($erm_proz / 100)));
            
            $tmp->gebuehr = MathOperations::allgRund($tmp->gebuehr, $grundsys->rundung);
            
            $tmp->bezeichnung .= $reason;
          } else {
            $tmp->gebuehr = $geb->betrag;
          }
          
          if ($geb->dp == 'W' || $geb->dp == 'T') {
            $tmp->dp_anzeige = 'ja';
          } else {
            $tmp->dp_anzeige = 'nein';
          }

          array_push($retour, $tmp);
        }
      } else {
        
        foreach ($gebary as $geb) {
          
          
          $geb->betrag_ermaessigt
              = (float)$this->mathop->stringToFloat($geb->betrag_ermaessigt);
          $tmp = $this->_getStdTmpGeb($geb);
          if ($geb->ermaessigbar && $geb->betrag_ermaessigt < $geb->betrag) {
            $tmp->gebuehr = $geb->betrag_ermaessigt;
            
            $tmp->bezeichnung .= $reason;
          } else {
            $tmp->gebuehr = $geb->betrag;
          }

          array_push($retour, $tmp);
        }
      }
    } else {
      foreach ($gebary as $geb) {
        

        $tmp = $this->_getStdTmpGeb($geb);

        array_push($retour, $tmp);
      }
    }

    return $retour;
  }

  
  public function _getStdTmpGeb($pKgeb)
  {
    $pKgeb->betrag = (float)$this->mathop->stringToFloat($pKgeb->betrag);

    $tmp = new TmpGeb();
    $tmp->markierung = '';
    $tmp->id = $pKgeb->id;
    $tmp->schluessel = $pKgeb->schluessel;
    $tmp->gebart = $pKgeb->gebart;
    $tmp->gebuehr = $pKgeb->betrag;
    $tmp->konto = $pKgeb->konto;
    $tmp->kostenst = $pKgeb->kostenst;
    if ($pKgeb->faelligkeit != '') {
      $tmp->faelligkeit = $pKgeb->faelligkeit;
    } else {
      $tmp->faelligkeit = DEFAULTDATE;
    }
    $tmp->mwst = $pKgeb->mwst;

    $tmp->ermaessigbar = $pKgeb->ermaessigbar;
    $tmp->bezeichnung = $pKgeb->text;
    
    $tmp->dp = $pKgeb->dp;

    return $tmp;
  }

  
  public function getKursgebuehrErmaessigt($pKnr, $pTnr, $pPreisgr = '', $pErmanm = -1, $pDatum = '', $pTn = null, $pZahlplanKursnummer = '')
  {
    $kursgebuehren = $this->getKursgebuehrenErmaessigtTab($pKnr, $pTnr, $pPreisgr, $pErmanm, $pDatum, $pTn, $pZahlplanKursnummer);

    return self::getSummeKursgebuehren($kursgebuehren);
  }

  public static function getSummeKursgebuehren($kursgebuehren)
  {
    $retour = 0.0;
    foreach ($kursgebuehren as $geb) {
      $retour += $geb->gebuehr;
    }

    return round($retour, 2);
  }

  
  public function getGebuehrenSummeSortiert($pKurs_Id, $pZpId = -1)
  {
    $retAry = new Gebuehr();
    $retAry->gebBetrag = 0;
    $retAry->gebErmbar = 0;
    $retAry->gebErm = 0;

    

    $dbwkz = new DBWorkKurszahlplan();
    if (is_numeric($pZpId) && $pZpId > 0) {
      $zpId = $pZpId;
    } else {
      $zpId = SessionOperations::getBelegungValueFromSession($pKurs_Id, 'zahlplan');
    }
    $zahlplan = $dbwkz->getKurszahlplanById($zpId, $pKurs_Id);

    if ($zahlplan === false) {
      $kurs_id = $this->dboperation->getString4Sql($pKurs_Id);
      $this->dboperation->clear();
      $this->dboperation->query("SELECT $this->select \n
                                 FROM $this->stdFrom\n
                                 WHERE kg.knr ='" . $kurs_id . "' \n " . $this->getSearchSQL('kg.'));
      $gebuehren = $this->getAllRows();
    } else {
      $gebuehren = $zahlplan->kursgebuehren;
    }

    foreach ($gebuehren as $gebuehr) {
      $retAry->gebBetrag += $this->mathop->stringToFloat($gebuehr->betrag);
      if ($gebuehr->ermaessigbar) {
        $retAry->gebErmbar += $this->mathop->stringToFloat($gebuehr->betrag);
        $retAry->gebErm += $this->mathop->stringToFloat($gebuehr->betrag_ermaessigt);
      }
    }

    $retAry->gebNoErm = ($retAry->gebBetrag - $retAry->gebErmbar);

    return $retAry;
  }

  
  public function isRatenKurs($pKid)
  {
    $kid = $this->dboperation->getString4Sql($pKid);
    $sql = 'SELECT COUNT(DISTINCT gebuehr) verschgeb FROM kurs_geb '
        . 'WHERE gebart = 12 AND knr LIKE \'' . $kid . '%\'';
    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $this->dboperation->selectrow(0);

    return $this->getDatamodulValue('verschgeb') == 1;
  }

  
  public function getCountRaten4Kurs($pKid)
  {
    $kid = $this->dboperation->getString4Sql($pKid);
    if (!$this->isRatenKurs($kid)) {
      return 0;
    }
    $sql = 'SELECT id FROM kurs_geb WHERE knr = \'' . $kid . '\' AND gebart = 12';
    $this->dboperation->clear();
    $this->dboperation->query($sql);

    return $this->dboperation->rowcount();
  }

  
  public function getHoeheRate4Kurs($pKid)
  {
    $kid = $this->dboperation->getString4Sql($pKid);
    if ($this->isRatenKurs($kid)) {
      $sql = 'SELECT gebuehr FROM kurs_geb WHERE knr = \'' . $kid . '\' AND gebart = 12';
      $this->dboperation->clear();
      $this->dboperation->query($sql);
      $this->dboperation->selectrow(0);

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

    return 0;
  }

  
  public function getHoeheAnzahlung($pKnr)
  {
    $knr = $this->dboperation->getString4Sql($pKnr);
    if (!$this->isRatenKurs($knr)) {
      return 0;
    }
    $sql = 'SELECT gebuehr FROM kurs_geb WHERE knr = \'' . $knr . '\' AND gebart = 11';
    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $this->dboperation->selectrow(0);

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

  
  public function getHoeheRestzahlung($pKnr)
  {
    $knr = $this->dboperation->getString4Sql($pKnr);
    if (!$this->isRatenKurs($knr)) {
      return 0;
    }
    $sql = 'SELECT gebuehr FROM kurs_geb WHERE knr = \'' . $knr . '\' AND gebart = 13';
    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $this->dboperation->selectrow(0);

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

  
  public function getHoeheSofortzahler(string $pKnr)
  {
    
    
    
    
    
    
    
    
    
    $knr = $this->dboperation->getString4Sql($pKnr);
    if (!$this->isRatenKurs($knr)) {
      return 0;
    }
    $sql = 'SELECT id FROM kurs_abwgeb WHERE knr = "' . $knr . '" AND beschreibung = "Sofortzahler" AND webdarst = "W";';
    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $this->dboperation->selectrow(0);

    $id = $this->dboperation->fieldbyname('id');

    $kpaddedTo10 = StringOperations::rpad($knr, 10, ' ');

    $sql = 'SELECT gebuehr FROM kurs_geb WHERE knr = "' . $kpaddedTo10 . $id . '"';
    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $this->dboperation->selectrow(0);

    return $this->getAllRows();
  }

  
  public function getMwstBetragInGebuehrenSumme($pKnr)
  {
    $knr = $this->dboperation->getString4Sql($pKnr);
    $sql = 'SELECT SUM(k.gebuehr-round((100/(m.prozente+100))*(((100-m.anteil)/100)*k.gebuehr),2))'
           . 'AS betrag FROM kurs_geb k LEFT OUTER JOIN mwst m ON k.mwst = m.schluessel '
           . 'WHERE k.knr = \'' . $knr . '\'';
    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $this->dboperation->selectrow(0);
    $betrag = $this->dboperation->fieldbyname('betrag');
    
    
    
    
    
    if (false !== StringOperations::strpos($betrag, ',')) {
      return MathOperations::stringToFloat($betrag);
    } else {
      return (float)($betrag === '' ? '0' : $betrag);
    }
  }

  
  public function hasKursFortlaufendeGebuehren($pKnr)
  {
    $knr = $this->dboperation->getString4Sql($pKnr);

    static $cache = null;
    if (!isset($cache[$knr])) {
      $this->dboperation->clear();
      $sql = 'SELECT count(*) AS anzahl FROM kurs_geb '
             . 'WHERE knr = \'' . $knr . '\' AND fortlaufend != \'\'';
      $this->dboperation->query($sql);
      $this->dboperation->selectrow(0);
      $cache[$knr] = $this->getDatamodulValue('anzahl') > 0;
    }

    return $cache[$knr];
  }

  public function getAllGebuehrenPartsForKnrs($pKursIds, $pRefreshCache = false)
  {
    $fromCacheOrFetch = $this->fromCacheOrFetch($pKursIds, self::$_cache_GebuehrenAllPartsByKnr, $pRefreshCache);

    $fromCache = $fromCacheOrFetch['fromCache'];
    $knrsToFetch = $fromCacheOrFetch['knrsToFetch'];
    if (count($knrsToFetch) === 0) {
      return $fromCache; 
    }

    $knrParts = array();
    foreach ($knrsToFetch as $knr) {
      $knrParts[] = $this->getSqlPartAllGebsForKnr($knr);
    }

    $sql = 'SELECT ' . $this->select . ' FROM ' . $this->stdFrom . 'WHERE (' . implode(') OR (', $knrParts) . ') ' . $this->getSearchSQL('kg.')
        . $this->getCompleteOrderByDefault('id', 'kg');
    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $gebCount = $this->dboperation->rowcount();
    if ($gebCount === 0) {
      return $fromCache;
    } else {
      $fromdb = array();
      for ($i = 0; $i < $gebCount; $i++) {
        $this->dboperation->selectrow($i);
        $tmp = $this->getCurrentRow();
        
        
        
        
        $knr = $this->extractKnr($tmp->knr);
        if (!isset($fromdb[$knr])) {
          $fromdb[$knr] = array();
        }
        $fromdb[$knr][] = $tmp;
      }
      
      foreach ($fromdb as $knr => $gebs) {
        self::$_cache_GebuehrenAllPartsByKnr[$knr] = $gebs;
      }

      return $fromdb + $fromCache;
    }
  }

  
  private function fromCacheOrFetch($pKursIds, $cache, $pRefreshCache = false)
  {
    if (!is_array($pKursIds)) {
      throw new InvalidArgumentException('$pKursIds ist kein Array');
    }
    $fromCache = array();
    $knrsToFetch = array();
    foreach ($pKursIds as $knr) {
      $knr = $this->dboperation->getString4Sql($knr);
      if ($pRefreshCache || !isset($cache[$knr])) {
        $knrsToFetch[] = $knr;
        $fromCache[$knr] = array();
      } else {
        $fromCache[$knr] = $cache[$knr];
      }
    }

    return array('fromCache' => $fromCache, 'knrsToFetch' => $knrsToFetch);
  }

  private function getSqlPartAllGebsForKnr($knr)
  {
    $k = $this->dboperation->getString4Sql($knr);
    $kpaddedTo10 = StringOperations::rpad($k, 10, ' ');

    return "kg.knr = '$k' or kg.knr like '$kpaddedTo10%'";
  }

  
  private function extractKnr($knr)
  {
    if (StringOperations::strlen($knr) > 10) {
      return trim(StringOperations::substr($knr, 0, 10));
    } else {
      return trim($knr);
    }
  }

  public function getOne($pId, $pIdFieldName = 'id', $pPrefix = 'kg.', $pRefreshCache = false) : Kursgebuehr
  {
    $queryBuilder = $this->getSelectQueryBuilder();
    $queryBuilder->select($this->select)
            ->from($this->stdFrom)
            ->where(':idFieldName: = :id:')
            ->paramFieldname('idFieldName', $pPrefix . $pIdFieldName)
            ->paramInt('id', $pId);
    $queryBuilder->addToWhere($this->getSearchSQL($pPrefix));
    $sql = $queryBuilder->getSQL();
    $this->dboperation->clear();
    $this->dboperation->query($sql);

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

  public function getAllQuery(): \SelectQueryBuilder
  {
    return $this->getSelectQueryBuilder()
      ->select($this->select)
      ->from($this->stdFrom)
      ->paramRaw('table', 'kg')
      ->orderBySrcCode($this->getCompleteOrderByDefault('id', 'kg'));
  }

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

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

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

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

    return $this->getAllRows();
  }

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

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

    return $this->getFirstRowOrNull();
  }

  
  public function countByFilter(\Kufer\Web\Classes\Dbfilter\KursgebuehrenSQLFilter $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 canupdate($varname)
  {
    switch ($varname) {
      case 'interner_typ': return false;
      default: return true;
    }
  }
}