Biblioteca para salir del paso usando KumbiaPHP y MSSQL.

readme.md

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.

sqlsrv.php

<?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, ', ' ) . ')';
    }
}