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

if (!defined('BASEDIR')) {
  include_once '..' . DIRECTORY_SEPARATOR . 'constants.php';
}


class DBWorkStammBv extends DBWorkGenerated
{
  public function __construct($pDBTyp = 1, $pNewInstance = false)
  {
    parent::__construct($pDBTyp, $pNewInstance);
    $this->select = 'id, typ, nr, blz, konto, bankbez, iban, bic, lfd, ls_jn, ls_von, ls_bis, '
      . $this->dboperation->dbparser->cast2Varchar('bemerkung') . ', inhaber, sepa, mandatam, mandatid, '
      . $this->dboperation->dbparser->cast2Varchar('mandatbem') . ', '
      . 'bearbeiter, bearbdatum, erstelltvon, erstelltam';
    $this->_dataModulFields = array(
      'typ', 'blz', 'konto', 'bankbez', 'iban', 'bic', 'ls_jn', 'bemerkung', 'inhaber', 'sepa', 'mandatid', 'ls_von',
      'ls_bis', 'mandatam', 'mandatbem', 'nr', 'lfd', 'id',
      'bearbeiter', 'bearbdatum', 'erstelltvon', 'erstelltam'
    );
  }

  public function getCurrentRow($pRefreshCache = false)
  {
    $stammBv = parent::getCurrentRow($pRefreshCache);

    
    
    if ($stammBv->typ === 'T' && trim($stammBv->mandatid) === '') {
      $stammBv->mandatid = $this->getDefaultSepaMandatId($stammBv->nr);
    }

    return $stammBv;
  }

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

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

  
  public static function getDefaultSepaMandatId($pTnr)
  {
    $dbwHlGrundsys = new DBWorkHLGrundSys(1, true);
    $hlGrundsysSettings = $dbwHlGrundsys->getSettingsAry(array('LSMandatTNPrae', 'LSMandatTNStellen', 'LSMandatTNPost'));

    $praefix = (isset($hlGrundsysSettings['LSMandatTNPrae'])) ? $hlGrundsysSettings['LSMandatTNPrae']->wert : 'T';
    $suffix = (isset($hlGrundsysSettings['LSMandatTNPost'])) ? $hlGrundsysSettings['LSMandatTNPost']->wert : '';
    $anzahlStellen = (isset($hlGrundsysSettings['LSMandatTNStellen'])) ? $hlGrundsysSettings['LSMandatTNStellen']->wert : 10;

    return $praefix . (MathOperations::fuelleAufMit0($pTnr, $anzahlStellen)) . $suffix;
  }

  
  public function getStammBvByTnr($pTnr)
  {
    $this->dboperation->clear();
    $this->dboperation->query($this->_sqlStatement4StammBvByTnr($pTnr));

    return $this->getAllRows();
  }

  
  public function getHauptStammBvByTnr($pTnr)
  {
    $this->dboperation->clear();
    $this->dboperation->query($this->_sqlStatement4StammBvByTnr($pTnr));

    return $this->getFirstRow();
  }

  
  public function getHauptStammBvsByTnrs($pTnrAry, $pRefreshCache = false)
  {
    if (count($pTnrAry) === 0) {
      return array();
    }
    $toFetch = TnStammbvCache::getKeysNotInCache($pTnrAry, $pRefreshCache);
    if (count($toFetch) > 0) {
      $this->dboperation->clear();
      $this->dboperation->query($this->_sqlStatement4StammBvByTnr($toFetch));
      if ($pRefreshCache) {
        TnStammbvCache::clearCache();
      }
      TnStammbvCache::fillDatamoduleInCache($this->getAllRows($pRefreshCache));
    }

    return TnStammbvCache::getByKeys($pTnrAry);
  }

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

  
  public function getStammBvByTnrAndIban($tnr, $iban)
  {
    $builder = $this->getAllQuery()
      ->and('nr = :tnr:')
      ->and('typ = :typTn:')
      ->paramInt('tnr', $tnr)
      ->paramString('typTn', 'T');

    if (is_null($iban) || $iban === '') {
      $builder = $builder
        ->and(':isemptyornull(iban):');
    } else {
      $builder = $builder
        ->and('iban IS NOT NULL')
        ->and('iban = :iban:')
        ->paramString('iban', $iban);
    }

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

    return $this->getFirstRow();
  }

  
  public function getStammBvByTnrAndKonto($pTnr, $pBlz, $pKonto)
  {
    $tnr = $this->dboperation->getInt4Sql($pTnr);
    $blz = $this->dboperation->getString4Sql($pBlz);
    $konto = $this->dboperation->getString4Sql($pKonto);

    $sql = 'SELECT ' . $this->select
           . ' FROM ' . $this->getTableName()
           . ' WHERE nr = ' . $tnr
           . ' AND typ = \'T\''
           . ' AND blz = \'' . $blz . '\''
           . ' AND konto = \'' . $konto . '\'';

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

    return $this->getFirstRow();
  }

  
  private function _sqlStatement4StammBvByTnr($pTnrAry)
  {
    if (!is_array($pTnrAry)) {
      $pTnrAry = array($pTnrAry);
    }

    $cleanTnrAry = $this->dboperation->getInts4Sql($pTnrAry);

    return 'SELECT ' . $this->select
           . ' FROM ' . $this->getTableName()
           . ' WHERE nr IN (' . implode(', ', $cleanTnrAry) . ')'
           . ' AND typ = \'T\''
           . ' ORDER BY lfd ASC';
  }

  
  public function deleteStammBv($pId, $pDontProtocol = false, $pDmzBetrieb = false)
  {
    $cleanId = $this->dboperation->getInt4Sql($pId);
    $this->dboperation->clear();
    $this->dboperation->execute('DELETE FROM stammbv WHERE id=' . $cleanId);
    if (!$pDontProtocol) {
      $this->saveChanging('stammbv', '', '', "'" . $cleanId . "'", '', 'D', $pDmzBetrieb);
    }
    $this->setActionIDs();
  }

  
  public function insertStammBv($pStammBv, $pDontProtocol = false, $pDmzBetrieb = false)
  {
    $pStammBv->id = $this->insertRecord('stammbv', 'id', 'nr', false);
    if (!$pDontProtocol) {
      $this->saveChanging('stammbv', '', '', $pStammBv->id, 'id', 'I', $pDmzBetrieb);
    }
    $orgStammBv = $this->getOneNoFilter($pStammBv->id);
    $this->updateObject($pStammBv, $orgStammBv, 'stammbv', 'id', 'A', $pDontProtocol, $pDmzBetrieb);
    $this->setActionIDs();

    return $pStammBv->id;
  }

  
  public function updateStammBv($pStammBv, $pDontProtocol = false, $pDmzBetrieb = false)
  {
    $origStammBv = $this->getOne($pStammBv->id);
    $this->updateObject($pStammBv, $origStammBv, 'stammbv', 'id', 'U', $pDontProtocol, $pDmzBetrieb);
    $this->setActionIDs();
  }

  
  public function isTnOwner($pId, $pTnr)
  {
    return $this->_isOwner($pId, $pTnr, 'T');
  }

  
  private function _isOwner($pId, $pPnr, $pTyp)
  {
    $cleanId = $this->dboperation->getInt4Sql($pId);
    $cleanPnr = $this->dboperation->getInt4Sql($pPnr);
    $cleanTyp = $this->dboperation->getString4Sql($pTyp);

    $sql = 'SELECT count(*)'
           . ' FROM ' . $this->getTableName()
           . ' WHERE id = ' . $cleanId
           . ' AND nr =  ' . $cleanPnr
           . ' AND typ = \'' . $cleanTyp . '\'';

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

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

  
  public function getMandatDatum4Tn($pTnr)
  {
    $cleanTnr = $this->dboperation->getInt4Sql($pTnr);

    $sql = 'SELECT mandatam'
           . ' FROM ' . $this->getTableName()
           . ' WHERE nr =  ' . $cleanTnr
           . ' AND typ = \'T\''
           . ' ORDER BY lfd ASC';

    $this->dboperation->clear();
    $this->dboperation->query($sql);
    if ($this->dboperation->rowcount() > 0) {
      $this->dboperation->selectrow(0);

      return $this->getDatamodulValue('mandatam');
    } else {
      return DEFAULTDATE;
    }
  }

  
  private function _getIbanPart($pIban)
  {
    $cleanIban = $this->dboperation->getString4Sql(trim($pIban));
    if (StringOperations::stripos($cleanIban, '*') === false) {
      
      
      return ' ( iban = \'' . $cleanIban . '\' OR iban like \'%*' . StringOperations::substr($cleanIban, -3) . '\' )';
    } else {
      
      return ' iban like \'%' . StringOperations::substr($cleanIban, -3) . '\'';
    }
  }

  
  public function hasTnSepaMandat($pTnr, $pIban)
  {
    $cleanIban = $this->dboperation->getString4Sql(trim($pIban));
    if (trim($cleanIban) == '') {
      return false;
    }


    $sql = $this->getTnSepaMandatSQLString($pTnr, $this->select, $pIban);

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

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

  
  public function getTnSepaMandatAmForWbLogin($pTnr)
  {
    
    $cleanTnr = $this->dboperation->getInt4Sql($pTnr);
    $sql = 'SELECT MAX(mandatam) as sepamandatam FROM stammbv WHERE stammbv.nr = ' . $cleanTnr . ' AND stammbv.typ = \'T\'';

    $this->dboperation->clear();
    $this->dboperation->query($sql);
    if ($this->dboperation->rowcount() > 0) {
      $this->dboperation->selectrow(0);

      return $this->getDatamodulValue('sepamandatam');
    } else {
      return '';
    }
  }

  
  private function getTnSepaMandatSQLString($pTnr, $pSelect, $pIBAN = null)
  {
    $dateNow = $this->datetimeop->getDateNow();
    $cleanTnr = $this->dboperation->getInt4Sql($pTnr);

    $ibanSql = '';
    if (!is_null($pIBAN)) {
      $ibanSql = $this->_getIbanPart($pIBAN);
    }

    $sql = ' SELECT ' . SecurityOperations::filterSQL(trim($pSelect), true) . ' '
           . ' FROM stammbv '
           . ' WHERE nr = ' . $cleanTnr
           . ' AND typ = \'T\'';

    
    if (\System::isKuferSQL5()) {
      $sql .= ' AND sepa = \'S\'';
    }

    $sql .= ' AND NOT mandatam = ' . $this->datetimeop->getFormatDate4Server(DEFAULTDATE)
           . ' AND mandatam <= ' . $this->datetimeop->getFormatDate4Server($dateNow);

    if ($ibanSql != '') {
      $sql .= ' AND ' . $ibanSql;
    }

    $sql .= ' ORDER BY lfd ASC';

    return $sql;
  }

  
  public function checkSepaMandatFromTn($pStammBv)
  {
    $tnr = $this->dboperation->getInt4Sql($pStammBv->nr);
    $iban = $this->dboperation->getString4Sql($pStammBv->iban);

    $isIbanShortened = false;
    if (strpos($iban, '*') !== false) {
      $iban = str_replace('*', '', $iban);
      $isIbanShortened = true;
    }

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

    $sql = 'SELECT bv.mandatam AS mandatam, '
           . 'CASE '
           . 'WHEN ' . $this->dboperation->dbparser->isNull('MAX(ze.xfer_datum)', $defaultDate4Server)
           . '>' . $this->dboperation->dbparser->isNull('MAX(ka.xferdat)', $defaultDate4Server)
           . '    AND ' . $this->dboperation->dbparser->isNull('MAX(ze.xfer_datum)', $defaultDate4Server)
           . '>' . $this->dboperation->dbparser->isNull('MAX(khk.xferdatum)', $defaultDate4Server)
           . 'THEN MAX(ze.xfer_datum) '
           . 'WHEN ' . $this->dboperation->dbparser->isNull('MAX(ka.xferdat)', $defaultDate4Server)
           . '>' . $this->dboperation->dbparser->isNull('MAX(ze.xfer_datum)', $defaultDate4Server)
           . '    AND ' . $this->dboperation->dbparser->isNull('MAX(ka.xferdat)', $defaultDate4Server)
           . '>' . $this->dboperation->dbparser->isNull('MAX(khk.xferdatum)', $defaultDate4Server)
           . 'THEN MAX(ka.xferdat) '
           . 'ELSE MAX(khk.xferdatum) '
           . 'END AS xfer_datum '
           . 'FROM teilnehm t '
           . 'INNER JOIN stammbv bv ON (t.nr = bv.nr AND bv.typ = \'T\') '
           . 'LEFT OUTER JOIN zahl_ein ze ON ((ze.persnr = t.nr OR ze.abw_tnr = t.nr) AND ze.iban = bv.iban AND ze.perstyp = \'T\') '
           . 'LEFT OUTER JOIN kasse ka ON (ka.dotnnr = t.nr AND ka.iban = bv.iban AND ka.doz_tn = \'T\' AND ka.typ <> 3) '
           . 'LEFT OUTER JOIN khk ON ((khk.tdnr = t.nr OR ' . $this->dboperation->dbparser->isNull('khk.abwzahler', -1) . ' = t.nr) AND khk.iban = bv.iban AND khk.uelsanweis = \'1\' AND khk.typ = \'T\') '
           . 'LEFT OUTER JOIN (SELECT COUNT(DISTINCT lfd) AS anz_bv, nr FROM stammbv WHERE typ = \'T\' GROUP BY nr, typ) bv2 ON (bv2.nr = t.nr) '
           . 'WHERE t.nr = ' . $tnr . ' AND bv.iban ' . ($isIbanShortened ? "like '%" : "= '") . $iban . "' "
           . 'GROUP BY bv.mandatam';

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

    $isSepaMandatValid = false;

    $rows = $this->dboperation->rowcount();
    for ($i = 0; $i < $rows; $i++) {
      $this->dboperation->selectrow($i);
      $mandatam = $this->datetimeop->getDateStr($this->dboperation->fieldbyname('mandatam'));
      $xferDatum = $this->datetimeop->getDateStr($this->dboperation->fieldbyname('xfer_datum'));

      
      
      if ($xferDatum != '' && DateTimeOperations::getComparableDate(DateTimeOperations::addDaysMonthsYears($xferDatum, 0, 0, 3)) >= DateTimeOperations::getComparableDate($this->datetimeop->getDateNow())
              || $mandatam != '' && DateTimeOperations::getComparableDate(DateTimeOperations::addDaysMonthsYears($mandatam, 0, 0, 3)) >= DateTimeOperations::getComparableDate($this->datetimeop->getDateNow())) {
        $isSepaMandatValid = true;
        break;
      }
    }

    return $isSepaMandatValid;
  }
}