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


class DBWorkKennzAd extends DBWork
{
  

  
  public function __construct($pDBTyp = 1, $pNewInstance = false)
  {
    parent::__construct($pDBTyp, $pNewInstance);
    $this->select = 'ka.nr, ka.typ, ka.kennz, ka.text, ka.bearbeiter, ka.bearbdatum, ka.nr_int';
  }

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

  
  public function canupdate($pVarname)
  {
    
    switch ($pVarname) {
       case 'kennz':
       case 'nr':
       case 'typ':     return false;
       default:     return true;
     }
  }

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

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

  
  public function getCurrentRow($pRefreshCache = false)
  {
    $tmp = $this->getNewObject();
    $tmp->nr = $this->getDatamodulValue('nr');
    $tmp->typ = $this->getDatamodulValue('typ');
    $tmp->kennz = $this->getDatamodulValue('kennz');
    $tmp->text = $this->getDatamodulValue('text');
    $tmp->bearbeiter = $this->getDatamodulValue('bearbeiter');
    $tmp->bearbdatum = $this->getDatamodulValue('bearbdatum');
    $tmp->nr_int = $this->getDatamodulValue('nr_int');

    return $tmp;
  }

  public function getSearchSQL($pPraeffix)
  {
    $praeffix = $this->dboperation->getString4Sql($pPraeffix);
    $result = '';

    if (isset($this->fromobject, $this->toobject)) {
      if (isset($this->fromobject->kennz) && $this->fromobject->kennz != '') {
        $result .= ' AND ' . $praeffix . 'kennz >= ' . CleanOperations::cleanInt($this->fromobject->kennz);
      }
      if (isset($this->toobject->kennz) && $this->toobject->kennz != '') {
        $result .= ' AND ' . $praeffix . 'kennz <= ' . CleanOperations::cleanInt($this->toobject->kennz);
      }
      if (isset($this->toobject->typ) && $this->toobject->typ != '') {
        $result .= ' AND ' . $praeffix . 'typ = ' . $this->dboperation->getString4Sql($this->toobject->typ);
      }
      if (isset($this->fromobject->nr) && $this->fromobject->nr != '') {
        $result .= ' AND ' . $praeffix . 'nr >= ' . $this->dboperation->getString4Sql($this->fromobject->nr);
      }
      if (isset($this->toobject->nr) && $this->toobject->nr != '') {
        $result .= ' AND ' . $praeffix . 'nr <= ' . $this->dboperation->getString4Sql($this->toobject->nr);
      }
      if (isset($this->fromobject->nr_int) && $this->fromobject->nr_int != '') {
        $result .= ' AND ' . $praeffix . 'nr_int >= ' . CleanOperations::cleanInt($this->fromobject->nr_int);
      }
      if (isset($this->toobject->nr_int) && $this->toobject->nr_int != '') {
        $result .= ' AND ' . $praeffix . 'nr_int <= ' . CleanOperations::cleanInt($this->toobject->nr_int);
      }
    }
    if (StringOperations::strlen($result) > 4) {
      return StringOperations::substr($result, 4);
    } else {
      return '';
    }
  }

  
  public function getKennzAd($pKennzId, $pTyp, $pNr)
  {
    $cleanKennzId = $this->dboperation->getInt4Sql($pKennzId);
    $cleanTyp = $this->dboperation->getString4Sql($pTyp);
    $cleanNr = $this->dboperation->getString4Sql($pNr);

    $sql = 'SELECT ' . $this->select . ' FROM kennz_ad ka'
           . " WHERE ka.kennz = '" . $cleanKennzId . "' "
         . " AND ka.typ = '" . $cleanTyp . "' and ka.nr = '" . $cleanNr . "' ORDER BY ka.kennz";
    $this->dboperation->clear();
    $this->dboperation->query($sql);

    return $this->getFirstRow();
  }

  
  public function getKennzForTypUndNr($pTyp, $pNr)
  {
    if (\is_null($pNr)) {
      return array();
    }

    $this->dboperation->clear();
    $this->dboperation->query($this->getKennzForTypUndNrQuery($pTyp, $pNr)->getSQL());

    return $this->getAllRows();
  }

  
  public function getKennzIdsForTypUndNr($pTyp, $pNr): array
  {
    if (\is_null($pNr)) {
      return array();
    }

    $builder = $this->getKennzForTypUndNrQuery($pTyp, $pNr)
      ->select('ka.kennz');
    $this->dboperation->clear();
    $this->dboperation->query($builder->getSQL());

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

  
  public function getKennzCountForTypUndNr($pTyp, $pNr): int
  {
    if (\is_null($pNr)) {
      return 0;
    }

    $builder = $this->getKennzForTypUndNrQuery($pTyp, $pNr)
      ->select('COUNT(*) as anzahl');
    $this->dboperation->clear();
    $this->dboperation->query($builder->getSQLForInnerQuery());

    return (int)$this->getValue('anzahl', \DatamodulVarType::TYPE_INT);
  }

  public function getKennzForTypUndKennzQuery($pTyp, $pNr): SelectQueryBuilder
  {
    if (!\is_array($pNr) && !($pNr instanceof SelectQueryBuilder)) {
      $pNr = array($pNr);
    }

    $builder = $this->getSelectQueryBuilder()
        ->select($this->select)
        ->from('kennz_ad', 'ka')
        ->and('ka.typ = :kennzTyp:')
        ->and('ka.kennz IN (:kennzNr:)')
        ->orderByAsc('ka.kennz')
        ->paramString('kennzTyp', $pTyp);

    if ($pNr instanceof SelectQueryBuilder) {
      $builder = $builder->paramSelectQuery('kennzNr', $pNr);
    } else {
      $builder = $builder->paramStringArray('kennzNr', $pNr);
    }

    return $builder;
  }

  
  public function getKennzForTypUndNrQuery($pTyp, $pNr): SelectQueryBuilder
  {
    if (!\is_array($pNr) && !($pNr instanceof QueryBuilder)) {
      $pNr = array($pNr);
    }

    $builder = $this->getSelectQueryBuilder()
        ->select($this->select)
        ->from('kennz_ad', 'ka')
        ->and('ka.typ = :kennzTyp:')
        ->and('ka.nr IN (:kennzNr:)')
        ->orderByAsc('ka.kennz')
        ->paramString('kennzTyp', $pTyp);

    if ($pNr instanceof QueryBuilder) {
      $builder = $builder->paramRaw('kennzNr', $pNr->getSQLForInnerQuery());
    } else {
      $builder = $builder->paramStringArray('kennzNr', $pNr);
    }

    return $builder;
  }

  
  public function getUniqueKennzForTypUndNrQuery($pTyp, $pNr): SelectQueryBuilder
  {
    return $this->getKennzForTypUndNrQuery($pTyp, $pNr)
        ->select('DISTINCT ka.kennz');
  }

  
  public function getUniqueKennzForTypUndNr($pTyp, $pNr)
  {
    $this->dboperation->clear();
    $this->dboperation->query($this->getUniqueKennzForTypUndNrQuery($pTyp, $pNr)->getSQL());

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

  
  public function insertKennzeichen($pKennzId, $pTyp, $pNr)
  {
    if (!is_numeric($pKennzId)) {
      return null;
    }
    $kennzId = $this->dboperation->getInt4Sql($pKennzId);
    
    $kennzeichen = $this->getNewObject();
    $kennzeichen->kennz = $kennzId;
    $kennzeichen->typ = $pTyp;
    $kennzeichen->nr = $pNr;
    $kennzeichen->nr_int = $pNr;
    $kennzeichen->text = ' ';

    $kennzeichen->bearbeiter = SessionOperations::getCurrentWebToolsUserKennung();
    $kennzeichen->bearbdatum = DateTimeoperations::getDateTimeNow();

    
    $this->insertRecord('kennz_ad', $kennzId, $pTyp, $pNr);
    
    $this->saveChanging('kennz_ad', '', '', $kennzId, 'kennz', 'I', true);
    

    $orgkennzeichen = $this->getKennzAd($kennzId, $pTyp, $pNr);

    $this->updateObject($kennzeichen, $orgkennzeichen, 'kennz_ad', 'kennz', 'A', false, true);
    $this->setActionIDs();

    return $kennzId;
  }

  

  
  public function deleteKennzeichen($pKennzId, $pTyp, $pNr)
  {
    if (!is_numeric($pKennzId)) {
      return null;
    }
    $kennzId = $this->dboperation->getInt4Sql($pKennzId);
    $cleanTyp = $this->dboperation->getString4Sql($pTyp);
    $cleanNr = $this->dboperation->getString4Sql($pNr);
    $this->dboperation->clear();
    $sql = 'DELETE FROM kennz_ad WHERE kennz=' . $kennzId . " and typ = '" . $cleanTyp . "' and nr = '" . $cleanNr . "'";
    $this->dboperation->execute($sql);
    
    $this->saveChanging('kennz_ad', '', '', "'" . $kennzId . "'", '', 'D', true);
    $this->setActionIDs();
  }

  
  public function insertRecord($pTablename, $pKennzId, $pTyp, $pNr = false)
  {
    $tablename = $this->dboperation->getString4Sql($pTablename);
    $cleanTyp = $this->dboperation->getString4Sql($pTyp);
    $cleanNr = $this->dboperation->getString4Sql($pNr);
    $cleanKennzId = $this->dboperation->getString4Sql($pKennzId);
    $this->dboperation->clear();
    $sql = 'INSERT INTO ' . $tablename . " ( nr, typ, kennz ) VALUES ('" . $cleanNr . "','" . $cleanTyp . "','" . $cleanKennzId . "')";
    $this->dboperation->execute($sql);
  }

  

  
  public function findByTypAndKennz($pTyp, $pKennz)
  {
    $cleanTyp = $this->dboperation->getString4Sql($pTyp);
    $cleanKennz = $this->dboperation->getInt4Sql($pKennz);

    $sql = 'SELECT ' . $this->select . ' FROM kennz_ad ka'
           . ' WHERE ' . $this->dboperation->dbparser->caseInsensitiveLike('ka.', 'typ', $cleanTyp)
           . ' AND ' . $this->dboperation->dbparser->caseInsensitiveLike('ka.', 'kennz', $cleanKennz);

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

    return $this->getAllRows();
  }

  
  public function findByTypAndKennzBereich($pTyp, $pKennzFrom, $pKennzTo, $pNr = 0, $refreshCache = false)
  {
    $cleanTyp = $this->dboperation->getString4Sql($pTyp);
    $cleanKennzFrom = $this->dboperation->getInt4Sql($pKennzFrom);
    $cleanKennzTo = $this->dboperation->getInt4Sql($pKennzTo);
    $cleanTnr = $this->dboperation->getString4Sql($pNr);

    static $cache = array();

    $cacheKey = "$cleanTnr#$cleanKennzFrom#$cleanKennzTo";
    if (!isset($cache[$cacheKey])) {
      $sql = 'SELECT ' . $this->select . ' FROM kennz_ad ka'
             . ' WHERE ' . $this->dboperation->dbparser->caseInsensitiveLike('ka.', 'typ', $cleanTyp)
             . ' AND ' . $this->dboperation->dbparser->caseInsensitiveBetween('ka.', 'kennz', $cleanKennzFrom, $cleanKennzTo);
      if ($cleanTnr > 0) {
        $sql .= " AND ka.nr = '$cleanTnr'";
      }

      $this->dboperation->clear();
      $this->dboperation->query($sql);
      $cache[$cacheKey] = $this->getAllRows();
    }

    return $cache[$cacheKey];
  }

  
  public function getCountByTypAndKennz($pTyp, $pKennzFrom, $pKennzTo, $pNrs = array())
  {
    $cleanTyp = $this->dboperation->getString4Sql($pTyp);
    $cleanKennzFrom = $this->dboperation->getInt4Sql($pKennzFrom);
    $cleanKennzTo = $this->dboperation->getInt4Sql($pKennzTo);

    $nrPart = '';
    if (count($pNrs) > 0) {
      if ($pTyp === 'K') {
        $nrs = $this->dboperation->getStrings4Sql($pNrs);
      } else {
        $nrs = $this->dboperation->getInts4Sql($pNrs);
      }
      $nrPart = ' AND ka.nr in (' . StringOperations::convertArray2InList($nrs) . ')';
    }

    $sql = 'select ka.kennz, count(*) as anzahl from kennz_ad ka where ka.typ = \'' . $cleanTyp . '\' and ka.kennz >= ' . $cleanKennzFrom
            . ' AND ka.kennz <= ' . $cleanKennzTo . $nrPart . ' group by kennz';
    $this->dboperation->clear();
    $this->dboperation->query($sql);

    $result = array();
    $count = $this->dboperation->rowcount();
    for ($i = 0; $i < $count; $i++) {
      $this->dboperation->selectrow($i);
      $kennz = $this->getDatamodulValue('kennz');
      $anz = $this->getDatamodulValue('anzahl');
      $result[$kennz] = $anz;
    }

    return $result;
  }

  
  public function getKursnummern4DozentenBewerbungQuery($dnr): \SelectQueryBuilder
  {
    $dnr = \Kufer\Web\Classes\Identifier\IdentifierFactory::createFromIdIfNecessary($dnr);

    
    $kennzIdsBuilder = $this->getAllQuery()
      ->select('k.kennz')
      ->innerJoin('kennz k', 'k.kennz = :table:.kennz')
      ->and('k.dozbewerbung = :true:')
      ->and(':table:.typ = :typeDozent:')
      ->and(':table:.nr = :dnr:')
      ->paramString('true', 'W')
      ->paramString('typeDozent', 'D')
      ->paramInt('dnr', $dnr->getId());

    
    return $this->getAllQuery()
      ->select(':table:.nr')
      ->and(':table:.typ = :typeKurs:')
      ->and(':table:.kennz IN (:kennzIds:)')
      ->paramString('typeKurs', 'K')
      ->paramSelectQuery('kennzIds', $kennzIdsBuilder);
  }

  
  public function getKursnummern4DozentenBewerbung($dnr)
  {
    $builder = $this->getKursnummern4DozentenBewerbungQuery($dnr);

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

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

  
  public function getKursNr4Kennzeichen($pTnr, $pKennzIds, $pRefreshCache = false)
  {
    $pTnr = \Kufer\Web\Classes\Identifier\IdentifierFactory::createFromIdIfNecessary($pTnr);
    $cacheKey = $pTnr->getCacheKey();

    static $cache = array();

    
    if (is_array($pKennzIds) && count($pKennzIds) > 0) {
      $kennzIdList = StringOperations::convertArray2InList($pKennzIds);

      if (!isset($cache[$cacheKey]) || $pRefreshCache) {
        $sql = 'SELECT ' . $this->select . ' FROM kennz_ad ka'
             . ' WHERE ka.typ=\'K\' and ka.kennz in (' . $kennzIdList . ')'
             . ' ORDER BY ka.nr';
        $this->dboperation->clear();
        $this->dboperation->query($sql);
        $cache[$cacheKey] = $this->getAllRowsSingleColumn('nr');
      }
    } 

    return $cache[$cacheKey];
  }

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

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

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

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

    return $this->getAllRows();
  }

  
  public function countByFilter(\Kufer\Web\Classes\Dbfilter\KennzeichenAdSQLFilter $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 existsByFilter(\Kufer\Web\Classes\Dbfilter\KennzeichenAdSQLFilter $filter): bool
  {
    return $this->countByFilter($filter) > 0;
  }
}