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


class DBOperationMssqlSqlsrv extends DBOperation
{
  
  private $_lastSQLStatement = '';

  
  private $_fieldMetaData;
  
  private $_countFields;

  
  private $_countRows;

  public function __construct($connectionInfo, $pDBnr)
  {
    parent::__construct($connectionInfo, $pDBnr);
  }

  
  public function connect_db($dbserver, $dbname, $dbuser, $dbpass)
  {
    $info = array('Database' => $dbname, 'UID' => $dbuser, 'PWD' => $dbpass);
    if (System::isUnicode() || !System::isWindows()) {
      $info['CharacterSet'] = 'UTF-8';
    }
    $this->connection = @sqlsrv_connect($dbserver, $info);
    if (!is_resource($this->connection) && $this->connection === false) {
      $errors = sqlsrv_errors();
      $errorDetails = '';
      foreach ($errors as $error) {
        $errorDetails .= $error[0] . ': ' . $error[1] . ' - ' . $error[2] . "\n";
      }
      Logger::logg($errorDetails, 1250);
      $this->throwCriticalError('Die Verbindung zum MSSQL-Datenbankserver "' . $dbserver . '" konnte nicht hergestellt werden! ' . "\n" . trim($errorDetails));
    }
    
    $this->execute('SET LANGUAGE deutsch;');
  }

  
  public function query($sql)
  {
    $this->openConnection();
    $this->_lastSQLStatement = $sql;
    
    $this->resultset = sqlsrv_query($this->connection, $this->encodeStringForDb($sql), array(), array('scrollable' => SQLSRV_CURSOR_CLIENT_BUFFERED));
    if ($this->resultset === false) {
      $this->throwCriticalError('Das SQL-Statement enthält Fehler! <br/>', $sql);
    }

    $this->_fieldMetaData = sqlsrv_field_metadata($this->resultset);
    $this->_countFields = count($this->_fieldMetaData);
    $this->_countRows = sqlsrv_num_rows($this->resultset);
  }

  
  public function execute($sql)
  {
    $this->openConnection();
    $this->_lastSQLStatement = $sql;
    $res = sqlsrv_query($this->connection, $this->encodeStringForDb($sql));
    if ($res === false) {
      $this->throwCriticalError('Falsche Benutzung von execute(sql)');
    } else {
      $this->_fieldMetaData = array();
      $this->_countFields = 0;
      $this->_countRows = 0;

      return true;
    }
  }

  
  public function callStoredProcedure($pSQL)
  {
    return $this->execute($pSQL);
  }

  
  public function rowcount()
  {
    return $this->_countRows;
  }

  
  public function selectrow($rownr)
  {
    
    if ($rownr < $this->rowcount() && $rownr > -1) {
      if (sqlsrv_fetch($this->resultset, SQLSRV_SCROLL_ABSOLUTE, $rownr)) {
        $this->actualrow = $rownr;
        
        
        
        
        $this->currentRow = array_change_key_case($this->_fetch_array($this->resultset, SQLSRV_FETCH_BOTH, SQLSRV_SCROLL_ABSOLUTE, $rownr), CASE_LOWER);

        return true;
      } else {
        return false;
      }
    } else {
      return false;
    }
  }

  
  private function _fetch_array($stmt, $fetchType = SQLSRV_FETCH_BOTH, $row = SQLSRV_SCROLL_RELATIVE, $offset = 0)
  {
    $tmp = sqlsrv_fetch_array($stmt, $fetchType, $row, $offset);
    if (is_array($tmp)) {
      foreach ($tmp as $key => $value) {
        if ($value instanceof DateTime) {
          $tmp[$key] = $value->format('Y-m-d H:i:s');
        }
      }
    }

    return $tmp;
  }

  
  public function debug()
  {
    echo 'Anzahl Zeilen: ' . $this->rowcount() . "\n";
    for ($i = 0; $i < $this->rowcount(); $i++) {
      $this->selectrow($i);
      foreach ($this->_fieldMetaData as $meta) {
        $name = $meta['Name'];
        echo "$name: " . $this->fieldbyname($name) . ', ';
      }
      echo "\n";
    }
  }

  
  public function fieldExists($name)
  {
    $fieldMetaData = $this->_fieldMetaData;
    $numFields = $this->_countFields;
    $ret = false;
    for ($i = 0; $i < $numFields; ++$i) {
      if (StringOperations::strtolower($fieldMetaData[$i]['Name']) == StringOperations::strtolower($name)) {
        $ret = true;
        break;
      }
    }

    return $ret;
  }

  
  public function getError()
  {
    $error = sqlsrv_errors(SQLSRV_ERR_ERRORS);
    $firstError = $error[0];
    $tmp = 'Error # ' . $firstError['code'] . ': ' . $firstError['message'] . " \n - letztes abgegebenes SQL-Statement: " . $this->_lastSQLStatement . " <br />\n";
    foreach ($firstError as $key => $value) {
      if (!is_numeric($key)) {
        $tmp .= $key . ' = ' . $value . ' ; ';
      }
    }
    $tmp .= "<br />\n";

    return $tmp;
  }

  
  public function checkConnection($pCheckTables = true)
  {
    $conInfo = DBConnectionInfo::getInstance();
    if ($this->dbnr == 1) {
      $dbserver = $conInfo->db_server;
      $dbname = $conInfo->db_name;
      $dbuser = $conInfo->db_user;
      $dbpass = $conInfo->db_password;
    } else {
      $dbserver = $conInfo->db2_server;
      $dbname = $conInfo->db2_name;
      $dbuser = $conInfo->db2_user;
      $dbpass = $conInfo->db2_password;
    }
    $Info = array('Database' => $dbname, 'UID' => $dbuser, 'PWD' => $dbpass);
    $connection = @sqlsrv_connect($dbserver, $Info);

    if (!$connection) {
      return false;
    }
    
    $res = @sqlsrv_query($connection, 'SET LANGUAGE deutsch;');
    if ($res) {
      sqlsrv_free_stmt($res);
    }

    if ($pCheckTables) {
      $res = @sqlsrv_query($connection, 'select top 1 id from aussenst');
      if (!$res) {
        return false;
      }

      sqlsrv_free_stmt($res);

      $res = @sqlsrv_query($connection, 'select top 1 id from kategorien');
      if (!$res) {
        return false;
      }
    }

    
    
    $this->connect_db($dbserver, $dbname, $dbuser, $dbpass);

    return true;
  }

  public function closeConnection()
  {
    @sqlsrv_close($this->connection);
    $this->connection = false;
    Connections::CloseConnection($this->dbnr);
  }

  
  public function checkTableExists($pTableName, $pRefreshCache = false)
  {
    if (self::$_tableExistsCache == null) {
      self::$_tableExistsCache = array();
    }

    if (!$pRefreshCache && isset(self::$_tableExistsCache[$pTableName])) {
      return self::$_tableExistsCache[$pTableName];
    }

    $sql = "SELECT * FROM information_schema.TABLES WHERE table_name = '" . $pTableName . "' and table_catalog = '" . $this->connectionInfo->db_name . "'";
    $this->clear();
    $this->query($sql);
    self::$_tableExistsCache[$pTableName] = ($this->rowcount() == 1);

    return self::$_tableExistsCache[$pTableName];
  }

  public function beginTransaction(): bool
  {
    $this->openConnection();

    return sqlsrv_begin_transaction($this->connection);
  }

  public function commitTransaction(): bool
  {
    return sqlsrv_commit($this->connection);
  }

  public function rollbackTransaction(): bool
  {
    return sqlsrv_rollback($this->connection);
  }

  private function getEncoderFromInternToDb()
  {
    static $encode = null;
    if (is_null($encode)) {
      $encode = new EncodeCharsetFromTo(CodingOperations::getInternalEncoding(), UTF_8);
    }

    return $encode;
  }

  public function decodeStringFromDb($value)
  {
    if ($this->connectionInfo->db_isEncodingUtf8) {
      $encoder = $this->getEncoderFromInternToDb();

      return $encoder->decode($value);
    }

    return $value;
  }

  public function encodeStringForDb($value)
  {
    if ($this->connectionInfo->db_isEncodingUtf8) {
      $encoder = $this->getEncoderFromInternToDb();

      return $encoder->encode($value);
    }

    return $value;
  }

  public function getEnclosedString4Sql(string $str): string
  {
    $isKuferSql5 = false;
    try {
      $isKuferSql5 = \System::isKuferSql5();
    } catch (\Throwable $ex) {
    }

    if ($isKuferSql5) {
      return 'N' . parent::getEnclosedString4Sql($str);
    } else {
      return parent::getEnclosedString4Sql($str);
    }
  }

  

  
  public function fieldbyname($name)
  {
    $normalfieldname = StringOperations::strtolower($name);

    if (isset($this->currentRow[$normalfieldname])) {
      $value = $this->currentRow[$normalfieldname];
      
      if (StringOperations::substr($value, StringOperations::strlen($value) - 1, 1) == \StringOperations::chr_windows1252(32)) {
        $value = StringOperations::substr($value, 0, StringOperations::strlen($value) - 1);
      }
      
      
      return $value;
    } else {
      return '';
    } 
  }

  
  public function getLastID($tablename)
  {
    $this->clear();
    $this->query('SELECT @@IDENTITY LID');
    $this->selectrow(0);

    return abs($this->fieldbyName('LID'));
  }

  public function getDBName()
  {
    return 'MSSQL';
  }

  public function getColumnsOfTable(string $table): array
  {
    $result = array();

    $this->clear();
    $this->query("SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('dbo.$table')");
    for ($i = 0; $i < $this->rowcount(); ++$i) {
      $this->selectrow($i);
      $result[] = $this->fieldbyname('name');
    }

    return $result;
  }
  
}