Skip to content

Instantly share code, notes, and snippets.

@demonio
Last active January 27, 2016 15:42
Show Gist options
  • Save demonio/147717cb4dc292c53946 to your computer and use it in GitHub Desktop.
Save demonio/147717cb4dc292c53946 to your computer and use it in GitHub Desktop.
Biblioteca para salir del paso usando KumbiaPHP y MSSQL.

Esta clase empezo siendo un adapter para KumbiaPHP, para despues convertirse en una LIB y con ello poder olvidarme del viejo y pesado active record.

<?php
class Sqlsrv
{
public function __call( $method, $args=array() )
{
if ( substr($method, 0, 8) == 'find_by_' )
{
$field = substr($method, 8);
if ( isset($args[0]) )
{
$arg = array("conditions: $field='$args[0]'");
}
return call_user_func_array( array($this, 'find_first'), $arg );
}
}
public function __construct()
{
$field_metadata = $this->field_metadata();
foreach ($field_metadata['fields'] as $field) $this->$field = '';
}
public function connect()
{
if ( empty($_SESSION['connect']) )
{
$database = Config::get('config.application.database');
$databases = Config::read('databases');
$config = $databases[$database];
if ( empty($config['port']) ) $config['port'] = 1433;
$connectionInfo = array
(
"Database"=>$config['name'],
"UID"=>$config['username'],
"PWD"=>$config['password'],
"CharacterSet"=>$config['charset']
);
$_SESSION['connect'] = sqlsrv_connect($config['host'], $connectionInfo);
}
}
public function distinct($field)
{
$source = get_class($this);
$sql = "SELECT DISTINCT $field FROM $source";
$query = $this->query($sql);
while( $a = sqlsrv_fetch_array($query) )
{
if ( ! $a[$field] ) continue;
$b[] = $a[$field];
}
return $b;
}
public function errors() { return sqlsrv_errors(); }
/**
* CONNECT >> QUERY
*/
public function query($sql)
{
$this->connect();
$query = sqlsrv_query($_SESSION['connect'], $sql);
return $query;
}
/**
* CONNECT >> QUERY >> FETCH_OBJECT
*/
public function fetch_object($sql)
{
$query = $this->query($sql);
$field_metadata = $this->field_metadata($query);
$fields = $field_metadata['fields'];
$metadata = $field_metadata['metadata'];
$rows = array();
while( $o = sqlsrv_fetch_object($query) )
{
foreach ( $fields as $field )
{
if ($metadata[$field]->Type == 93)
{
$cols[$field] = ( is_null($o->$field) )
? NULL
: $o->$field->format('Y-m-d H:i:s');
}
else
{
$cols[$field] = $o->$field;
}
}
$rows[] = (object)$cols;
}
return $rows;
}
/**
* CONNECT >> QUERY >> FIELD_METADATA
*/
public function field_metadata($query='')
{
if ( ! $query )
{
$source = get_class($this);
$sql = "SELECT TOP 1 * FROM $source";
$query = $this->query($sql);
}
$fields = sqlsrv_field_metadata($query);
foreach ( $fields as $field )
{
$a['fields'][] = $field['Name'];
$a['metadata'][$field['Name']] = (object)$field;
}
$a['id'] = $a['fields'][0];
return $a;
}
/**
* CONNECT >> QUERY >> FETCH_OBJECT >> FIND_ALL_BY_SQL
*/
public function find()
{
$params = $this->getParams( func_get_args() );
$source = get_class($this);
#_::e($params);
$id = $this->field_metadata()['id'];
if ( ! empty($params[0]) and is_numeric($params[0]) )
{
$sql = "SELECT TOP 1 * FROM $source WHERE $id={$params[0]}";
}
else
{
$top = empty($params['limit']) ? '' : " TOP {$params['limit']}";
$cols = empty($params['columns']) ? '*': $params['columns'];
$conditions = empty($params['conditions']) ? '' : " WHERE {$params['conditions']}";
$order = empty($params['order']) ? '': " ORDER BY {$params['order']}";
$sql = "SELECT$top $cols FROM $source$conditions$order";
}
#_::e($sql);
$rows = $this->fetch_object($sql);
#_::e($rows);
return ( count($rows) == 1 ) ? $this->getFields($rows[0]) : $rows;
}
/**
* CONNECT >> QUERY >> FETCH_OBJECT >> FIND_ALL_BY_SQL
*/
public function find_all_by_sql($sql) { return $this->fetch_object($sql); }
/**
* CONNECT >> QUERY >> FETCH_OBJECT >> FIND_BY_SQL
*/
public function find_by_sql($sql)
{
$rows = $this->find_all_by_sql($sql);
if ( ! empty($rows[0]) ) return $rows[0];
}
/**
* CONNECT >> QUERY >> FETCH_OBJECT >> FIND_ALL_BY_SQL
*/
public function find_first()
{
$params = $this->getParams( func_get_args() );
if ( ! $params ) return;
$params['limit'] = 1;
return $this->find($params);
}
/**
* CONNECT >> QUERY >> FETCH_OBJECT >> FIND_ALL_BY_SQL
*/
public function getParams($params)
{
#_::e( gettype($params[0]) );
#_::e($params);
# SI ESTA VACIO O ES UN RECURSO
if ( empty($params[0]) ) return;
if ( gettype($params[0]) == 'resource' ) return;
# SI EL PARAMETRO 0 ES UN ARRAY, SUBIMOS UN NIVEL
if ( is_array($params[0]) )
{
$a = array();
foreach ($params[0] as $k => $v )
{
$a[$k] = $v;
}
return $a;
}
# ES UN ID
if ( preg_match( '/^\d+$/', $params[0] ) ) return $params;
# ES UN PARAMETRO POR NOMBRE
if ( preg_match( '/^\w+\: /', $params[0] ) )
{
$a = array();
foreach ($params as $k => $v )
{
$p = explode(': ', $v, 2 );
$a[$p[0]] = $p[1];
}
return $a;
}
}
/**
* CONVIERTE UNA MATRIZ EN UNA CADENA CON ESTE FORMATO:
* campo1='valor1', campo2='valor2', campo3='valor3'
* campo1='valor1' AND campo2='valor2' AND campo3='valor3'
*/
public function kv($beg='', $a=array(), $end='')
{
if ( ! $a ) $a = (array)$this->getFields();
$s = '';
foreach ($a as $k => $v)
{
if ( is_object($v) ) $v = $v->format('d-m-Y');
$s .= "$beg$k='$v'$end";
}
$s = ltrim($s, $beg);
$s = rtrim($s, $end);
return $s;
}
/**
* CUANDO SE OBTIENE UNA SOLA FILA SE DEVUELVE UN OBJETO CON LOS CAMPOS
*/
public function getFields($row='')
{
if ( ! $row ) return $this;
$field_metadata = $this->field_metadata();
foreach ($field_metadata['fields'] as $field)
{
if ( isset($row->$field) ) $this->$field = $row->$field;
}
return $this;
}
/**
* BUSCA EL REGISTRO INSERTADO O ACTUALIZADO SEGUN MATRIZ
*/
public function lastRow( $a=array() )
{
$source = get_class($this);
$where = $this->kv( '', $a, ' AND ' );
$pk = $this->field_metadata()['id'];
$sql = "SELECT TOP 1 * FROM $source WHERE $where ORDER BY $pk DESC";
#_::d($sql);
$row = $this->find_by_sql($sql);
return $row;
}
public function sql($sql) { return $this->query($sql); }
public function create($a)
{
$pk = $this->field_metadata()['id'];
unset($a[$pk]);
$values = $this->values( $a );
$source = get_class($this);
$sql = "INSERT INTO $source $values";
#_::d($sql);
$this->sql($sql);
$id = 1; #$this->lastRow($a)->$pk;
return $id;
}
public function update( $a=array() )
{
#_::d($this);
$pk = $this->field_metadata()['id'];
$source = get_class($this);
if ( $a )
{
$id = $a[$pk];
unset($a[$pk]);
$set = $this->kv( ', ', $a );
}
else
{
$id = $this->$pk;
unset($this->$pk);
$set = $this->kv( ', ' );
}
$where = "$pk=$id";
$sql = "UPDATE $source SET $set WHERE $where";
#_::d($sql);
$this->sql( $sql );
return $id;
}
public function save($a)
{
$pk = $this->field_metadata()['id'];
$action = empty($a[$pk]) ? 'create' : 'update';
#_::d($action);
return $this->$action($a);
}
/**
* CONVIERTE UNA MATRIZ EN UNA CADENA CON ESTE FORMATO:
* (campo1, campo2) VALUES ('valor1', 'valor2');
*/
public function values( $a=array() )
{
$s = '(';
foreach ( $a as $k => $v ) $s .= "$k, ";
$s = rtrim( $s, ', ' ) . ') VALUES (';
foreach ( $a as $k => $v ) $s .= "'$v', ";
return rtrim( $s, ', ' ) . ')';
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment