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


class SQLOperations
{
  
  public static function BuildAussenstFilterSqlFromSession($praefix)
  {
    $wk = new DBWorkWarenkorbAussenstFilter(); 

    $entries = $wk->getEntries(); 
    if (is_null($entries) || count($entries) == 0) { 
      return '';
    }

    $sql = '';
    $dbwaussenst = new DBWorkHLAussenstellen(); 

    foreach ($entries as $entry) { 
      $aussenst = $dbwaussenst->getAussenstelle($entry->id); 
      if (!$aussenst->isEmpty()) {
        $sql .= ' OR ' . $praefix . '.aussenst LIKE \'' . $aussenst->bezeichner . '%\'';
      }
    }

    $sql = StringOperations::substr($sql, 4); 

    if ($sql == '') {
      return '';
    }

    return ' AND (' . $sql . ')'; 
  }

  
  public static function unterKursFromKursGruppePart($pKursIdHauptkurs)
  {
    if (is_array($pKursIdHauptkurs)) {
      $whereKnr = 'hauptkurs in (' . StringOperations::convertArray2InList($pKursIdHauptkurs) . ')';
    } else {
      $whereKnr = 'hauptkurs = \'' . $pKursIdHauptkurs . "'";
    }

    return 'SELECT unterkurs FROM kursgruppe WHERE ' . $whereKnr;
  }

  
  public static function BuildWochentagSearchSQLFromSession()
  {
    $wk = new DBWorkWarenkorbWochentagFilter(); 

    $searchPattern = $wk->getWochentagSearchSQLKomplement();

    if ($searchPattern != '') {
      return SQLOperations::BuildWochentagSearchSQL($searchPattern);
    } else {
      return '';
    }
  }

  
  public static function BuildWochentagSearchSQL($pSearchPattern, $pBeruecksichtigeRaumanzeige = false)
  {
    $searchPattern = SecurityOperations::filterSQL($pSearchPattern, true);
    $dbw = new DBWorkKurseBase();

    $sql = ' AND (select count(*) from kurstage kt where kt.knr=k.nr';

    if ($pBeruecksichtigeRaumanzeige) {
      $sql .= ' AND NOT kt.ranzprognoanz = \'W\' ';
    }

    $sql .= ' AND ' . $dbw->dboperation->dbparser->getNrOfWeekDay('datum', 'kt');

    if ($dbw->objsettings_web->kfsWotagPositivAuswerten) {
      if ($searchPattern === '1,2,3,4,5,6,7') {
        
        $sql .= ' in (1,2,3,4,5,6,7)) > 0';
      } else {
        $positiveArray = array();
        for ($i = 1; $i <= 7; $i++) {
          if (false === StringOperations::strpos($searchPattern, (string)$i)) {
            $positiveArray[] = $i;
          }
        }
        $sql .= ' in (' . implode(',', $positiveArray) . ')) > 0';
      }
    } else {
      $sql .= ' in (' . $searchPattern . ')) = 0';

      
      if ($dbw->objsettings_web->kfsWotagMitKurstag == true) {
        $sql .= ' AND (select count(*) from kurstage kt where kt.knr=k.nr ) > 0';
      }
    }

    if ($searchPattern != '') {
      return $sql;
    }

    return '';
  }

  
  public static function BuildZeitraumFilterSqlFromSession($praefix)
  {
    $wk = new DBWorkWarenkorbZeitraumFilter(); 

    $entries = $wk->getEntriesIDSerialized(); 
    if (count($entries) == 0) { 
      return '';
    } else {
      return SQLOperations::GetZeitraumSQLString($entries, $praefix);
    }
  }

  
  public static function GetZeitraumSQLString($entries, $praefix)
  {
    $dboperation = DBOperation::getInstance(1);
    $sql = '';
    foreach ($entries as $entry) {
      $searchTime = array();
      if ($entry != '') {
        
        if (StringOperations::substr($entry, 0, 2) == 'zb') {
          $von = array(0, 1300, 1800, 700, 1130, 1400, 1700, 700, 1000, 1300, 1700, 800, 1100);
          $bis = array(1300, 1800, 2359, 1145, 1400, 1700, 2200, 1030, 1330, 1730, 2200, 1100, 1400);
          $index = StringOperations::substr($entry, 2);
          if (isset($von[$index], $bis[$index])) {
            $searchTime = array($von[$index], $bis[$index]);
          }
        }
        
        elseif (StringOperations::substr($entry, 0, 1) == 'a') {
          $tmpAry = explode('|', $entry);
          $searchTime = array($tmpAry[1], $tmpAry[2]);
        }
        $konjunkt = ' AND ';
        if ($searchTime[1] < $searchTime[0]) {
          $konjunkt = ' OR ';
        }
        
        
        $sql .= ' OR (' . $dboperation->dbparser->castReplace2Int('REPLACE (' . $praefix . 'BEGINN, \'.\', \'\') ', '')
                          . '>' . (($searchTime[0] > 0) ? '=' : '') . $searchTime[0] . $konjunkt
                          . $dboperation->dbparser->castReplace2Int('REPLACE (' . $praefix . 'BEGINN, \'.\', \'\') ', '')
                          . '<=' . $searchTime[1]
                      . ')';
      }
    }
    $sql = StringOperations::substr($sql, 4); 
    if ($sql == '') {
      return '';
    }

    return ' AND (' . $sql . ')'; 
  }

  
  public static function BuildSemesterFilterSqlFromSession($praefix)
  {
    $wk = new DBWorkWarenkorbSemesterFilter();  

    $entries = $wk->getEntries(); 
    if (is_null($entries) || count($entries) == 0) { 
      return '';
    }

    $sql = '';

    
    foreach ($entries as $entry) { 
      if ($entry->id != '') {
        $sql .= ' OR ' . $praefix . '.semester = \'' . $entry->id . '\'';
      }
    }

    $sql = StringOperations::substr($sql, 4); 

    if ($sql == '') {
      return '';
    }

    return ' AND (' . $sql . ')'; 
  }

  
  public static function buildKursfieldFilterFromSession($praefix)
  {
    $warenkorb = new DBWorkWarenkorbKursfieldFilter();
    $wkentries = $warenkorb->getEntries();

    if (is_null($wkentries) || count($wkentries) == 0) {
      return '';
    }

    $entries = array();

    
    foreach ($wkentries as $wkentry) {
      array_push($entries, unserialize($wkentry->id));
    }

    
    usort($entries, array('SQLOperations', '_kursfieldSortCallback'));

    $sql = '';
    $orOpen = false;

    for ($i = 0; $i < count($entries); $i++) {
      $entry = $entries[$i];
      if ($i == count($entries) - 1) {
        $nextEntry = null;
      } else {
        $nextEntry = $entries[$i + 1];
      }

      $sqlChunk = $praefix . '.' . $entry->id . " = '" . $entry->value . "'";

      
      if (($nextEntry != null) && ($entry->id == $nextEntry->id)) {
        if ($orOpen) { 
          $sql .= ' OR ' . $sqlChunk;
        } else {
          $sql .= ' AND (' . $sqlChunk;
        }

        $orOpen = true;
      } else { 
        if ($orOpen) { 
          $sql .= ' OR ' . $sqlChunk . ')';
        } else {
          $sql .= ' AND ' . $sqlChunk;
        }

        $orOpen = false;
      }
    }

    return $sql;
  }

  
  public static function _kursfieldSortCallback($val1, $val2)
  {
    return strcmp($val1->id, $val2->id);
  }

  
  public static function buildKursbeginnFilterSQLFromSession($praefix)
  {
    $dtop = new DateTimeOperations();
    $beginn = SessionOperations::getVarValueFilter('WKKURSBEGINNFILTER');
    if ($beginn != '' && $dtop->isValidDate($beginn)) {
      return ' and ' . $praefix . 'beginn_dat >= ' . $dtop->getValidDate($beginn) . ' ';
    } else {
      return '';
    }
  }

  
  public static function buildKursendeFilterSQLFromSession($praefix)
  {
    $dtop = new DateTimeOperations();
    $ende = SessionOperations::getVarValueFilter('WKKURSENDEFILTER');
    if ($ende != '' && $dtop->isValidDate($ende)) {
      return ' and ' . $praefix . 'ende_dat <= ' . $dtop->getValidDate($ende) . ' ';
    } else {
      return '';
    }
  }

  
  public static function ReBuildAussenstFilterSQL($praefix)
  {
    SessionOperations::setVarValueFilter('aussenstfiltersql', SQLOperations::BuildAussenstFilterSqlFromSession($praefix));
  }

  
  public static function ReBuildWochentagFilterSQL()
  {
    SessionOperations::setVarValueFilter('wochentagfiltersql', SQLOperations::BuildWochentagSearchSqlFromSession());
  }

  
  public static function ReBuildZeitraumFilterSQL($praefix)
  {
    SessionOperations::setVarValueFilter('zeitraumfiltersql', SQLOperations::BuildZeitraumFilterSqlFromSession($praefix));
  }

  
  public static function ReBuildSemesterFilterSQL($praefix)
  {
    SessionOperations::setVarValueFilter('semesterfiltersql', SQLOperations::BuildSemesterFilterSqlFromSession($praefix));
  }

  
  public static function reBuildKursfieldFilterSQL($praefix)
  {
    SessionOperations::setVarValueFilter(KURSFIELD_FILTER_SESSION_NAME, SQLOperations::buildKursfieldFilterFromSession($praefix));
  }

  
  public static function reBuildKursbeginnFilterSQL($praefix)
  {
    SessionOperations::setVarValueFilter('kursbeginnfiltersql', SQLOperations::buildKursbeginnFilterSQLFromSession($praefix));
  }

  
  public static function reBuildKursendeFilterSQL($praefix)
  {
    SessionOperations::setVarValueFilter('kursendefiltersql', SQLOperations::buildKursendeFilterSQLFromSession($praefix));
  }

  
  public static function concatWithAnd(array $arrayOfSQLStatements): string
  {
    return static::concatSqlStatements($arrayOfSQLStatements, ' AND ');
  }

  
  public static function concatWithOr(array $arrayOfSQLStatements): string
  {
    return static::concatSqlStatements($arrayOfSQLStatements, ' OR ');
  }

  private static function concatSqlStatements(array $arrayOfSQLStatements, string $glue): string
  {
    $gefuellteStatements = array_filter($arrayOfSQLStatements, function($sqlStatement)  {
      return trim($sqlStatement) != '';
    });

    if(count($gefuellteStatements) == 0) {
      return '';
    } else {
      return ' (' . implode($glue, $gefuellteStatements) . ') ';
    }
  }
}