<?php


/**
 *
 * @param string $connection_id
 *
 * @return PDO
 */
function connection($connection_id) {
  $host = $_SERVER['SABENTIS_DB_HOST'];
  $database = $_SERVER['SABENTIS_DB_DATABASE'];
  $username =  $_SERVER['SABENTIS_DB_LOGIN'];
  $password =  $_SERVER['SABENTIS_DB_PASSWORD'];

  static $connections = array();
  if (!isset($connections[$connection_id])) {
    $connection_options['pdo'] = array();
    $connection_options['pdo'][PDO::ATTR_ERRMODE] = PDO::ERRMODE_EXCEPTION;
    $connection_options['pdo'][PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE] = TRUE;
    $connection_options['pdo'][PDO::ATTR_STRINGIFY_FETCHES] = FALSE;

    $cnn = new \PDO("sqlsrv:server=$host;Database=$database",  $username, $password, $connection_options['pdo']);
    $connections[$connection_id] = $cnn;

  }
  return $connections[$connection_id];
}

/**
 * Summary of prepare
 *
 * @param mixed $connection
 * @param mixed $query
 * @return PDOStatement
 */
function prepare($connection, $query, $buffered = TRUE) {
  $pdo_options = array();
  $pdo_options[PDO::ATTR_EMULATE_PREPARES] = TRUE;
  $pdo_options[PDO::SQLSRV_ATTR_DIRECT_QUERY] = TRUE;
  if ($buffered) {
    $pdo_options[PDO::ATTR_CURSOR] = PDO::CURSOR_SCROLL;
    $pdo_options[PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE] = PDO::SQLSRV_CURSOR_BUFFERED;
  }
  return $connection->prepare($query, $pdo_options);
}

/**
 * Summary of execute
 *
 * @param PDO $connection
 * @param string $query
 *
 * @param PDOStatement;
 */
function execute($connection, $query, array $args = array(), $buffered = TRUE) {
  $st = prepare($connection, $query, $buffered);
  $st->execute($args);
  return $st;
}


//*******************************************************
// TEST BEGIN
//*******************************************************

$connection = connection('default');

// Drop
try {
  execute($connection, 'DROP TABLE TEST');
}
catch(\Exception $e) {}


// Recreate
execute($connection, "CREATE TABLE TEST([id] [int] IDENTITY(1,1) NOT NULL, [myinteger] int, [mybiginteger] bigint, [myfloat] float, [mysmallint] smallint, [mybit] bit, [myreal] real, [mynvarchar] nvarchar(max))");

$sample_data = [
    ':myinteger' => 25,
    ':mybiginteger' =>1236544123211,
    ':myfloat' =>23652.3221452,
    ':mysmallint' =>5632,
    ':mybit' => 1,
    ':myreal' => 1256325.3214587,
    ':mynvarchar' => 'en un lugar de la mancha de cuyo nombre no puedo acordarme...'
  ];

$columns = array_keys($sample_data);
array_walk($columns, function(&$item) {
  $item = substr($item, 1, strlen($item) - 1);
});

try {
  execute($connection, "INSERT INTO TEST(" . implode(',', $columns) . ") VALUES(" . implode(',', array_keys($sample_data)) . ")", $sample_data);

}
catch (\Throwable $error) {
  echo $error->getMessage();
  exit();
}

$statement = execute($connection, "SELECT * FROM TEST");

$null = [];
$columns = $statement->columnCount();
for ($i = 0; $i < $columns; $i++) {
  $meta = $statement->getColumnMeta($i);
  $type = $meta['sqlsrv:decl_type'];
  switch($type) {
    case 'varbinary':
      $null[$i] = NULL;
      $statement->bindColumn($i + 1, $null[$i], PDO::PARAM_LOB, 0, PDO::SQLSRV_ENCODING_BINARY);
      break;
    case 'nvarchar':
    case 'varchar':
      $null[$i] = NULL;
      $statement->bindColumn($i + 1, $null[$i], PDO::PARAM_STR, 0, PDO::SQLSRV_ENCODING_UTF8);
      break;
  }
}

foreach ($statement as $row) {
  foreach ($row as $key => $value) {
    if (is_numeric($key)) {
      continue;
    }
    echo "$key:" . gettype($value) . '<br/>';
  }
}

echo '<br/><br/>';

$statement = execute($connection, "SELECT * FROM TEST");

foreach ($statement as $row) {
  foreach ($row as $key => $value) {
    if (is_numeric($key)) {
      continue;
    }
    echo "$key:" . gettype($value) . '<br/>';
  }
}