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


class DBWorkAktuellesWT extends DBWork
{
  public function __construct($pDBTyp = 1, $pNewInstance = false)
  {
    
    $this->updateAlways = array('letzter_bearbeitung_datum');
    parent::__construct($pDBTyp, $pNewInstance);
    $this->select = 'id, titel, ' . $this->clob('inhalt') . ', erstelldatum, autor_id,
     benutzergruppe, letzter_bearbeiter, letzter_bearbeitung_datum, dnr_privatnachricht, gueltigbis, bezirk';
  }

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

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

  
  public function getCurrentRow($pRefreshCache = false)
  {
    $tmp = $this->getNewObject();
    $tmp->id = $this->getDatamodulValue('id');
    $tmp->titel = strtr($this->getDatamodulValue('titel'), '`', "'");
    $tmp->inhalt = strtr($this->getDatamodulValue('inhalt'), '`', "'");
    $tmp->autor_id = $this->getDatamodulValue('autor_id');
    $tmp->letzter_bearbeiter = $this->getDatamodulValue('letzter_bearbeiter');
    $tmp->letzte_bearbeitung_datetime = $this->getDatamodulValue('letzter_bearbeitung_datum');
    $tmp->erstell_datetime = $this->getDatamodulValue('erstelldatum');
    $tmp->benutzergruppe = $this->getDatamodulValue('benutzergruppe');
    $tmp->dnr_privatnachricht = $this->getDatamodulValue('dnr_privatnachricht');
    $tmp->gueltigBis = $this->getDatamodulValue('gueltigbis');
    $tmp->bezirk = $this->getDatamodulValue('bezirk');

    return $tmp;
  }

  
  public function translateVarName($varname)
  {
    switch ($varname) {
      case 'erstell_datetime':
      case 'erstell_datum':
        return 'erstelldatum';
      case 'letzte_bearbeitung_datetime':
      case 'letzte_bearbeitung_date':
        return 'letzter_bearbeitung_datum';
      default:
        return DBWork::translateVarName($varname);
    }
  }

  
  public function canupdate($varname)
  {
    switch ($varname) {
      default: return parent::canupdate($varname);
      case 'id': return false;
      case 'erstell_uhrzeit': return false;
      case 'letzter_bearbeitung_uhrzeit': return false;
    }
  }

  
  public function getAktuelles($pNewsId)
  {
    $news_id = $this->dboperation->getInt4Sql($pNewsId);
    if (empty($news_id)) {
      return $this->getNewObject();
    }
    $sql = 'SELECT ' . $this->select . ' FROM web_aktuelles'
           . ' WHERE id = \'' . $news_id . '\' ORDER BY id DESC';
    $this->dboperation->clear();
    $this->dboperation->query($sql);

    return $this->getFirstRow();
  }

  
  public function getAktuelles4User($pFachbereich = '', $pBenutzergruppeAry = array(), $pNewsId = 0)
  {
    $sql = $this->getAktuellesSQLString($pFachbereich, $pBenutzergruppeAry, $pNewsId, false);
    $sql .= $this->getCompleteOrderByDefault('id', 'wa', false);
    $this->dboperation->clear();
    $this->dboperation->query($sql);

    return $this->getAllRows();
  }

  
  public function getAktuellesIds4User($pFachbereich = '', $pBenutzergruppeAry = array(), $pNewsId = 0)
  {
    $sql = $this->getAktuellesSQLString($pFachbereich, $pBenutzergruppeAry, $pNewsId, true);
    $sql .= $this->getCompleteOrderByDefault('id', 'wa', false);
    $this->dboperation->clear();
    $this->dboperation->query($sql);

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

  
  public function getAktuellesByIds($pIds)
  {
    if (empty($pIds)) {
      return array();
    }
    $ids = array_map(array($this->dboperation, 'getInt4Sql'), $pIds);
    $sql = 'SELECT ' . $this->select . ' FROM web_aktuelles'
           . ' WHERE id IN (' . implode(', ', $ids) . ') '
           . $this->getCompleteOrderByDefault('id', '', false);
    $this->dboperation->clear();
    $this->dboperation->query($sql);

    return $this->getallRows();
  }

  
  public function getAktuellesByIdsOrdered($pIds)
  {
    if (empty($pIds)) {
      return array();
    }
    $ids = array_map(array($this->dboperation, 'getInt4Sql'), $pIds);
    $sql = 'SELECT ' . $this->select . ' FROM web_aktuelles'
           . ' WHERE id IN (' . implode(', ', $ids) . ') ';

    $this->dboperation->clear();
    $this->dboperation->query($sql);
    $this->dboperation->selectRow(0);
    $dbResult = $this->getAllRows();

    
    $dbResultById = ArrayOperations::setAttributAsKey($dbResult, 'id');
    $orderedResult = array();
    foreach ($ids as $id) {
      if (isset($dbResultById[$id])) {
        $orderedResult[] = $dbResultById[$id];
      }
    }

    return $orderedResult;
  }

  
  private function getAktuellesSQLString($pFachbereich = '', $pBenutzergruppeAry = array(), $pNewsId = 0, $pSelectOnlyIds = true)
  {
    $select = ($pSelectOnlyIds ? 'id' : $this->select);
    $snews = '';
    if (!empty($pNewsId)) {
      $news_id = $this->dboperation->getInt4Sql($pNewsId);
      $snews = ' AND wa.id = ' . $news_id . ' ';
    }
    $fachb = '';
    $ben = '';
    if (SessionOperations::isAdmin()) {
      if (!empty($pBenutzergruppeAry) && !in_array('-1', $pBenutzergruppeAry)) {
        $ben = 'AND ( ';
        foreach ($pBenutzergruppeAry as $benutzergruppe) {
          $benutzergruppe = $this->dboperation->getString4Sql($benutzergruppe);
          $ben .= 'wa.benutzergruppe like \'%' . $benutzergruppe . '%\' OR ';
        }
        $ben .= ' (1=0) ) ';
      }
      if (empty($pFachbereich)) {
        $fachbereich = '0';
      } else {
        $fachbereich = $this->dboperation->getString4Sql($pFachbereich);
      }
      
      if ($fachbereich != '-1') {
        $fachb = "AND wa.id IN (SELECT waf.akt_id FROM web_akt_fachb waf WHERE waf.fachb_kennz = '$fachbereich')";
      }
      $sql = 'SELECT ' . $select . ' FROM web_aktuelles wa WHERE (1=1) ' . $fachb . $ben . $snews;
    } 
    else {
      $user = SessionOperations::getCurrentWebToolsUser();
      if (!empty($pFachbereich) && $pFachbereich != '-1') {
        $fachbereich = $this->dboperation->getString4Sql($pFachbereich);
        $fachb = " AND fad.kennz = '" . $fachbereich . "' ";
        $fachalle = '';
      } elseif ($pFachbereich == '-1') {
        $fachb = '';
        $fachalle = 'OR waf.fachb_kennz = \'0\'';
      }
      
      else {
        $fachb = ' AND (1=0) ';
        $fachalle = 'OR waf.fachb_kennz = \'0\'';
      }
      if (in_array('-1', $pBenutzergruppeAry)) {
        $pBenutzergruppeAry = array(0, 1, 2, 3, 4);
      }
      if (!empty($pBenutzergruppeAry)) {
        $benBool = false;
        $ben = 'AND ( ';
        foreach ($pBenutzergruppeAry as $benutzergruppe) {
          switch ($benutzergruppe) {
            case -1:
              $userType = '';
              $ben .= 'wa.benutzergruppe like \'%0%\' OR ';
              break;
            case 0:
              $userType = '';
              $ben .= 'wa.benutzergruppe like \'%0%\' OR ';
              break;
              
            case 1:
              $userType = 'ben';
              break;
              
            case 2:
              $userType = 'dnr';
              break;
              
            case 3:
              $userType = 'tnr';
              break;
              
            case 4:
              $userType = 'anr';
              break;
            default:
              $userType = '';
              break;
          } 

          
          
          if (isset($user->{$userType}) && $user->{$userType} > 0) {
            $benutzergruppe = $this->dboperation->getString4Sql($benutzergruppe);
            $ben .= 'wa.benutzergruppe like \'%' . $benutzergruppe . '%\' OR ';
            $benBool = true;
          }
        } 
        if ($benBool == true) {
          $ben .= ' (1=0) ) ';
        }
        
        else {
          $ben = ' AND wa.benutzergruppe like \'%0%\'';
        }
      } 
      else {
        $ben = ' AND wa.benutzergruppe like \'%0%\'';
      }

      
      
      
      $pn = "AND ( wa.dnr_privatnachricht = '0' OR wa.dnr_privatnachricht = '-1' ) ";

      if (isset($user->dnr) && $user->dnr > 0) {
        $pn = " AND ( wa.dnr_privatnachricht = '0' OR wa.dnr_privatnachricht = '-1' ";
        $pn .= ' OR ' . $this->getPrivUserIdByDBType($user->dnr) . ' ';

        if (DBWorkBASysSettings::isSalzburgBildungswerk()) {
          $veranstalterDnr = \Kufer\Web\KuferTools\Veranstalter\Utils\VeranstalterUtils::getVeranstalterDnr();
          if ($veranstalterDnr > 0 && $veranstalterDnr != $user->dnr) {
            $pn .= ' OR ' . $this->getPrivUserIdByDBType($veranstalterDnr) . ' ';
          }
        }

        $pn .= ' ) ';
      }

      $bezirk = '';
      if (DBWorkBASysSettings::isSalzburgBildungswerk()) {
        $bezirk = ' AND (' . $this->dboperation->dbparser->isStringEmptyOrNull('wa.bezirk');
        $veranstalter = \Kufer\Web\KuferTools\Veranstalter\Utils\VeranstalterUtils::getVeranstalterDozent();
        if (trim($veranstalter->bezirk) != '') {
          $bezirk .= ' OR wa.bezirk = \'' . trim($veranstalter->bezirk) . '\' ';
        }
        $bezirk .= ') ';
      }

      $kennz = 'SELECT DISTINCT fad.kennz FROM fach_adr fad WHERE fad.nr = ' . $user->dnr . $fachb;
      $akt = "SELECT DISTINCT waf.akt_id FROM web_akt_fachb waf WHERE waf.fachb_kennz IN ($kennz) " . $fachalle;
      $sql = "SELECT $select FROM web_aktuelles wa WHERE wa.id IN ($akt) " . $ben . ' ' . $snews . ' ' . $pn . $bezirk;
      
      $sql .= $this->getSearchSQL('wa.');
    }

    return $sql;
  }

  
  public function getPrivUserIdByDBType($privuserid)
  {
    $connectionInfo = DBConnectionInfo::getInstance();
    $DBTyp = $connectionInfo->db_typ;
    switch ($DBTyp) {
      case 'ORACLE':
        return " REGEXP_LIKE(wa.dnr_privatnachricht,'([^0-9]|^)" . $privuserid . "([^0-9]|$)') ";
      default:
      case 'MSSQLODBC':
      case 'MSSQL':
        return " wa.dnr_privatnachricht like '%[^0123456789]" . $privuserid . "[^0123456789]%'  "
             . " OR wa.dnr_privatnachricht like '" . $privuserid . "[^0123456789]%'  "
             . " OR wa.dnr_privatnachricht like '%[^0123456789]" . $privuserid . "'  "
             . " OR wa.dnr_privatnachricht like '" . $privuserid . "' ";
      case 'MYSQL':
      case 'MYSQLI':
        return " wa.dnr_privatnachricht REGEXP '([^0-9]|^)" . $privuserid . "([^0-9]|$)' ";
    }
  }

  
  public function insertAktuelles($pNews, $pFachbereichAry = array('0'), $private = false)
  {
    
    $pNews->id = $this->insertRecord('web_aktuelles', 'id', 'titel', false);
    $this->saveChanging('web_aktuelles', '', '', $pNews->id, 'id', 'I');
    $orgAktuelles = $this->getAktuelles($pNews->id);
    $this->updateObject($pNews, $orgAktuelles, 'web_aktuelles', 'id', 'A');
    $this->setActionIDs();
    if (!$private) {
      $this->insertFachbereich($pFachbereichAry, $pNews->id);
    }

    return $pNews->id;
  }

  
  public function updateAktuelles($pNews, $pFachbereichAry)
  {
    
    
    $orgAktuelles = $this->getAktuelles($pNews->id);

    if (!empty($orgAktuelles->id)) {
      $this->updateObject($pNews, $orgAktuelles, 'web_aktuelles', 'id');
      $this->setActionIDs();
      $this->updateFachbereich($pFachbereichAry, $pNews->id);
    }
  }

  
  public function deleteAktuelles($pNews_id)
  {
    $news_id = $this->dboperation->getInt4Sql($pNews_id);
    if (!empty($news_id)) {
      $this->dboperation->clear();
      $this->dboperation->execute('DELETE FROM web_aktuelles WHERE id=' . $news_id);
      $this->saveChanging('web_aktuelles', '', '', "'" . $news_id . "'", '', 'D');
      $this->setActionIDs();

      
      $this->deleteFachbereiche($news_id);

      
      
      
    }
  }

  public function deleteAktuellesBulk($pNewsIds)
  {
    if (!is_array($pNewsIds)) {
      throw new Exception('Invalid Argument: $pNewsIds is not an Array');
    }
    foreach ($pNewsIds as $newsId) {
      $this->deleteAktuelles($newsId);
    }
  }

  public function getExistingNewsIds($pNewsIds)
  {
    if (!is_array($pNewsIds)) {
      throw new Exception('Illegal Argument: $pNewsIds is not an array');
    }
    $ids = array();
    foreach ($pNewsIds as $id) {
      $cleanId = $this->dboperation->getInt4Sql($id);
      if ($cleanId == $id) {
        $ids[] = $id;
      }
    }
    if (empty($ids)) {
      return array();
    }
    $sql = 'SELECT id FROM web_aktuelles WHERE id in (' . implode(',', $ids) . ');';
    $this->dboperation->clear();
    $this->dboperation->query($sql);

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

  
  public function getFachbereicheFromAktuelles($pNews_id)
  {
    $news_id = $this->dboperation->getInt4Sql($pNews_id);
    $sql = 'SELECT fachb_kennz FROM web_akt_fachb WHERE akt_id = ' . $news_id . ' ORDER BY id DESC';
    $this->dboperation->clear();
    $this->dboperation->query($sql);

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

  
  public function insertFachbereich($pFachbereichAry, $pNewsID)
  {
    $newsID = $this->dboperation->getInt4Sql($pNewsID);
    if (empty($pFachbereichAry)) {
      $pFachbereichAry = array('0');
    }
    foreach ($pFachbereichAry as $fachbereich) {
      $fachbereich = $this->dboperation->getString4Sql($fachbereich);
      $id = $this->insertRecord('web_akt_fachb', 'id', 'akt_id', false);
      $this->saveChanging('web_akt_fachb', '', '', $id, 'id', 'I');
      $this->dboperation->clear();
      $this->dboperation->execute("UPDATE web_akt_fachb SET fachb_kennz = '$fachbereich', akt_id = $newsID WHERE id = $id");
    }
  }

  
  public function updateFachbereich($pFachbereichAry, $pNewsID)
  {
    $news_id = $this->dboperation->getInt4Sql($pNewsID);

    $this->deleteFachbereiche($news_id);
    $this->insertFachbereich($pFachbereichAry, $news_id);
  }

  
  public function deleteFachbereiche($pNews_id)
  {
    $news_id = $this->dboperation->getInt4Sql($pNews_id);
    
    $this->dboperation->clear();
    $this->dboperation->execute('DELETE FROM web_akt_fachb WHERE akt_id=' . $news_id);
    $this->setActionIDs();
  }

  
  public function getPrivateNews()
  {
    $dbwuser = new DBWorkUser(1, true);
    $userid = SessionOperations::getUserIdentifier();
    $user = $dbwuser->getUser($userid);
    if ($user->dnr > 0) {
      $dnr = $this->dboperation->getInt4Sql($user->dnr);
      $sql = "SELECT $this->select FROM web_aktuelles wa WHERE dnr_privatnachricht = $dnr";
      $sql .= $this->getSearchSQL('wa.');
      $this->dboperation->clear();
      
      $this->dboperation->query($sql);

      return $this->getallRows();
    }

    return array();
  }

  

  
  public function getAktuellesBySearch()
  {
    $sql = 'SELECT ' . $this->select . ' FROM web_aktuelles wb ';
    $where = $this->getSearchSQL('wb.');
    if (StringOperations::strlen($where) < 4) {
      return array();
    }
    $sql .= ' WHERE ' . StringOperations::substr($where, 4);
    $this->dboperation->clear();
    $this->dboperation->query($sql);

    return $this->getAllRows();
  }

  public function getSearchSQL($praeffix)
  {
    $result = '';
    if (isset($this->fromobject, $this->toobject)) {
      
      $likeFelder = array('titel', 'inhalt');
      foreach ($likeFelder as $feld) {
        $val = $this->fromobject->getVarValue($feld);
        if ($val !== '') {
          $result .= ' AND ' . $this->dboperation->dbparser->caseInsensitiveLike($praeffix, $this->translateVarName($feld), $val);
        }
      }

      
      $likeFelder = array('letzter_bearbeiter', 'benutzergruppe');
      foreach ($likeFelder as $feld) {
        $val = $this->fromobject->getVarValue($feld);
        if ($val !== '') {
          $result .= ' AND ' . $this->dboperation->dbparser->caseInsensitiveLike($praeffix, $this->translateVarName($feld), $val);
        }
      }

      
      $datumsFelder = array('erstell_datetime', 'letzte_bearbeitung_datetime');
      foreach ($datumsFelder as $feld) {
        $val = $this->fromobject->getVarValue($feld);
        if ($val != '' && $this->datetimeop->isValidDate($val)) {
          $result .= ' AND (' . $praeffix . $this->translateVarName($feld) . '>=' . $this->getSQLValue($feld, $this->fromobject) . ')';
        }
        $val = $this->toobject->getVarValue($feld);
        if ($val != '' && $this->datetimeop->isValidDate($val)) {
          $result .= ' AND (' . $praeffix . $this->translateVarName($feld) . '<=' . $this->getSQLValue($feld, $this->toobject) . ')';
        }
      }

      
      $datumsFelder = array('gueltigBis');
      $dtop = new DateTimeOperations();
      foreach ($datumsFelder as $feld) {
        $val = $this->fromobject->getVarValue($feld);
        if ($val != '' && $this->datetimeop->isValidDate($val)) {
          $result .= ' AND (' . $praeffix . $this->translateVarName($feld) . '>=' . $this->getSQLValue($feld, $this->fromobject)
                     . ' OR ' . $praeffix . $this->translateVarName($feld) . ' = ' . $dtop->getFormatDate4Server(DEFAULTDATE) . ')';
        }
        
        $val = $this->toobject->getVarValue($feld);
        if ($val != '' && $this->datetimeop->isValidDate($val)) {
          $result .= ' AND (' . $praeffix . $this->translateVarName($feld) . '<=' . $this->getSQLValue($feld, $this->toobject)
                     . ' AND NOT ' . $praeffix . $this->translateVarName($feld) . ' IS NULL '
                     . ' AND NOT ' . $praeffix . $this->translateVarName($feld) . ' = ' . $dtop->getFormatDate4Server(DEFAULTDATE) . ')';
        }
      }
    }

    return $result;
  }

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

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

    return $this->getAllQuery()
      ->and($condition)
      ->orderBySrcCode($this->getCompleteOrderByDefault('id', $this->getTableName()));
  }

  public function findOneByFilter(\Kufer\Web\Classes\Dbfilter\AktuellesWTSQLFilter $filter, bool $refreshCache = false): ?\Aktuelles
  {
    $builder = $this->findByFilterQuery($filter);

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

    return $this->getFirstRowOrNull($refreshCache);
  }

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

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

    return $this->getAllRows();
  }

  
  public function findIdsByFilter(\Kufer\Web\Classes\Dbfilter\AktuellesWTSQLFilter $filter): array
  {
    $builder = $this->findByFilterQuery($filter)
      ->select(':table:.id');

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

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

  
  public function countByFilter(\Kufer\Web\Classes\Dbfilter\AktuellesWTSQLFilter $filter): int
  {
    $builder = $this->findByFilterQuery($filter)
      ->select('COUNT(*) AS anzahl');

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

    $this->dboperation->selectrow(0);

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

  public function existsByFilter(\Kufer\Web\Classes\Dbfilter\AktuellesWTSQLFilter $filter): bool
  {
    return $this->countByFilter($filter) > 0;
  }
}