<?php
/**
* Classe controladora de banco de dados Microsoft SQL Server
* mssql_(*)
*
* @author Aderbal Nunes (hide@address.com) - 31 de julho de 2009 10:43:08
* @version 0.1.8
* @example
*
* // INSERIR
* $c = Controller::getInstance();
* $c->prepare("TABLE_NAME");
* if( $c->insert( array("string", 1, $c->date(date("d/m/Y")) ) ) echo "success";
*
* // ARQUIVO CSV http://pt.wikipedia.org/wiki/Comma-separated_values
* $c = Controller::getInstance();
* $c->prepare("TABLE_NAME");
* $c->setCSV("path/file.csv");
* $c->execute();
* echo $c->resultCount() // número de inserções
*
* // DEPOIS DE INSERIR
* echo $c->lastedId(); // último #ID registrado na tabela (se auto_increment)
*
* // SELECT
* $c = Controller::getInstance();
* foreach($c->select("SELECT * FROM TABLE_NAME") as $row){
* // var_dump($row); <-- stdClass
* echo $row->fieldName."<br />";
* }
*
* $params = array("name" => "Aderbal");
* $c = Controller::getInstance();
* $c->setFetchMode($c::FETCH_ROW); // default FETCH_OBJECT
* foreach($c->select("SELECT * FROM TABLE_NAME WHERE name LIKE '%:name%'", $params) as $row){
* // var_dump($row); <-- mixed Array
* echo $row['fieldName']."<br />";
* }
* echo $c->resultCount(). " encontrados.";
*
* // UPDATE
* $c = Controller::getInstance()
* $c->stmt("UPDATE TABLE_NAME SET nome=':nome' WHERE id=:id");
* if( $c->execute(array("nome" => "Aderbal Nunes", "id" => 1)) ) echo "success";
*
* // PREPARE
* $c = Controller::getInstance();
* $c->stmt( "SELECT * FROM TABLE_NAME WHERE name LIKE '%:name%'", array("name" => "Aderbal") );
* $c->execute();
* $c->fetchAll(); // mixed array result (default stdClass)
*
* // EXECUTA
* $c = Controller::getInstance();
* if( $c->exec("DELETE FROM TABLE_NAME WHERE id=:id", array("id" => 1)) ) echo "success";
*/
class Controller{
/**
* Nome da tabela para manipulação
*
* @var String
*/
private static $tabela;
/**
* Campos da tabela para insert
*
* @var String
*/
private static $campos;
/**
* Ãltimo registro inserido na tabela
*
* @var int
*/
private static $lastedId;
/**
* Link do banco
*
* @var resource
*/
private static $link;
/**
* Instância para Singleton
*
* @var Controller
*/
private static $instance;
/**
* String com os valores a serem inseridos
*
* @var String
*/
private static $values;
/**
* String com os campos da tabela para completar a query
* de insert
*
* @var String
*/
private static $fields;
/**
* Tipo de retorno da consulta
* FETCH_OBJECT / FETCH_ROW
*
* @var int
*/
private static $typeFetch = null;
/**
* String query
*
* @var String
*/
private static $query;
/**
* Resource SQL - retorno do método mssql_query()
*
* @var resource
*/
private static $sql;
/**
* Total de registros encontrados na consulta
*
* @var int
*/
private static $resultCount = 0;
/**
* Boolean para arquivo csv
*
* @var bool
*/
private static $csv = false;
/**
* Parâmetros encontrados a partir do CSV file
*
* @var mixed array
*/
private static $params;
/** constantes */
const FETCH_OBJECT = 1;
const FETCH_ROW = 2;
/**
* Construtor padrão, Singleton
*
*/
private function __construct(){
try{
self::$link = Database::getInstance();
}catch(Exception $e){
echo $e->getMessage();
exit();
}
}
/**
* Recupera a instância forçando singleton
*
* @return Controller
*/
public static function getInstance() {
if(!isset(self::$instance)) {
self::$instance = new Controller();
}
return self::$instance;
}
/**
* Retorna a string com os campos a serem inseridos na tabela
*
* @param String $tabela
*/
public function prepare($tabela){
self::$campos = null;
self::$tabela = $tabela;
try{
self::fieldsTable($tabela);
}catch(Exception $e){
echo $e->getMessage();
exit();
}
self::$campos = "(";
for($i = 0;$i < sizeof(self::$fields);$i++){
if($i <> sizeof(self::$fields)-1){
self::$campos .= self::$fields[$i].", ";
}else{
self::$campos .= self::$fields[$i];
}
}
self::$campos .= ")";
return self::$campos;
}
/**
* Seta os valores a serem inseridos na tabela
*
* @param Array[] $val
* @return String
*/
public function setValues($val){
if(is_array($val)){
if(sizeof(self::$fields) > sizeof($val) || sizeof($val) > sizeof(self::$fields)){
throw new Exception("Number of fields (".sizeof(self::$fields).") pt2en: Number of fields in the table does not give the number of past values (".sizeof($val).")");
}else{
self::$values = null;
self::$values = "(";
foreach($val as $i => $value){
// remove os SQL Injection
$value = self::clearInjection($value);
if($i <> sizeof($val) - 1){
self::$values .= is_int($value) || self::isDate($value)?"".$value.", ":"'".$value."', ";
}else{
self::$values .= is_int($value) || self::isDate($value)?"".$value.")":"'".$value."')";
}
}
return self::$values;
}
}else{
throw new Exception("Invalid values");
}
}
/**
* Insere valores em uma daterminada tabela
*
* @param Array[] $values
*/
public function insert($values=false){
if(is_array($values)){
try{
self::setValues($values);
}catch(Exception $e){
echo $e->getMessage();
}
}
if(self::$campos != null && self::$values != null){
$query = "INSERT INTO ".self::$tabela." ".self::$campos." ".
" VALUES ".self::$values;
}
if(mssql_query($query)) return true;
else return false;
}
/**
* Retorna o último registro da tabela inserido no momento
*
* @return int
*/
public function lastedId(){
if(isset(self::$lastedId) && self::$tabela != ""){
self::setFetchMode(self::FETCH_ROW);
$row = self::select("SELECT MAX(".self::$lastedId.") FROM ".self::$tabela);
return $row[0][0];
}else return null;
}
/**
* Anti-Injection
*
* @param String $data
* @return String
*/
public function clearInjection($data){
if(!self::isDate($data)){
if(!is_int($data)){
$data = preg_replace(sql_regcase("/(from|select|insert|delete|where|drop table|show tables|#|\*|--|\\\\)/"),"",$data);
$data = trim($data); //limpa espaços vazio
$data = strip_tags($data); //tira tags html e php
$data = addslashes($data); //Adiciona barras invertidas a uma string
}
}
return $data;
}
/**
* Verifica se o campo é data
*
* @param String $data
* @return bool
*/
public function isDate($data){
return strpos($data, "Convert") === false?false:true;
}
/**
* Seta campo a inserir como data
*
* @param String $date
* @return String
*/
public static function date($date){
return "Convert(Datetime,'".$date."',103)";
}
/**
* Seta o modo de retorno da consulta
* FETCH_OBJECT
* FETCH_ROW
*
* @param int $mode
*/
public function setFetchMode($mode){
if(isset($mode))
self::$typeFetch = $mode;
}
/**
* Set CSV file
*
* @param string $file
*/
public function setCSV($file){
$handler = file($file);
if(sizeof($handler) > 0){
$param = array();
foreach($handler as $line){
$e = explode(",", $line);
array_push($param, $e);
}
self::$params = $param;
self::$csv = true;
unset($param);
}else{
echo "ERROR: setCSV() => file not found";
exit();
}
}
/**
* Retorna uma query
*
* @param String $query
* @param [Array $params - Parâmetros da query, default: null]
* @param [int $fecthType - Tipo de retorno array ou object, default: FETCH_OBJECT]
* @return object
*/
public function select($query=null, $params=null, $fecthType=self::FETCH_OBJECT){
self::$resultCount = 0;
if($query == null){
echo "ERROR: select() => empty query";
exit();
}else{
$res = array();
if(!isset(self::$typeFetch)) self::$typeFetch = $typeFetch;
if($params != null){
$params = self::checkParams($params);
$query = $this->bindParams($query, $params);
}
$this->sql = mssql_query($query);
$res = $this->fetchAll(self::$typeFetch);
}
return $res;
}
/**
* Executa uma query sem retorno de dados
* ex.: UPDATE, DELETE etc
*
* @param String $query
* @param [Array $params]
* @return bool
*/
public function exec($query=null, $params=null){
if($query == null || $query == ""){
echo "ERROR: exec() => empty query";
exit();
}else{
if($params != null){
$params = self::checkParams($params);
$query = $this->bindParams($query, $params);
}
$this->sql = mssql_query($query);
return true;
}
}
/**
* Prepare Statement
*
* @param String $query
* @param Array [$params]
*/
public function stmt($query, $params=null){
if(isset($query) || $query != ""){
self::$query = $query;
if(isset($params)){
$params = self::checkParams($params);
self::$query = $this->bindParams($query, $params);
}
}else{ echo "ERROR: stmt() => empty query"; exit(); }
}
/**
* Execute um Statement
*
* @param [Array $params]
*/
public function execute($params=null){
self::$resultCount = 0;
if(self::$csv){
if(is_array(self::$params)){
// execute
foreach(self::$params as $param){
$this->insert($param);
self::$resultCount++;
}
self::$params = null;
self::$csv = false;
}
}else{
if(isset($params)){
$params = self::checkParams($params);
self::$query = $this->bindParams(self::$query, $params);
}
if(self::$query != "" || isset(self::$query)){
$this->sql = mssql_query(self::$query);
return true;
}else{
echo "ERROR: execute() => empty query";
exit();
}
}
}
/**
* Verifica o parametro passado para a query, e limpa se possÃvel os SQL Injections
*
* @param mixed Array $param
* @return mixed Array
*/
public static function checkParams($param){
if(is_array($param)){
foreach($param as $key => $par){
$param[$key] = self::clearInjection($par);
}
}
return $param;
}
/**
* Retorna array row
*
* @param [int $mode]
* @return mixed Array
*/
public function fetchAll($mode=self::FETCH_OBJECT){
if($this->sql != "" || isset($this->sql)){
if(self::$typeFetch == self::FETCH_ROW){
while($row = mssql_fetch_array($this->sql)){
$res[] = $row;
self::$resultCount++;
}
}else{
// default FETCH_OBJECT
while($row = mssql_fetch_object($this->sql)){
$res[] = $row;
self::$resultCount++;
}
}
mssql_free_result($this->sql);
return $res;
}else{
echo "ERROR: fetchAll() => No sql resource --> Controller::execute() ";
exit();
}
}
/**
* Retorna o total de registros da consulta
*
* @param int $resultCount
*/
public function resultCount(){
return self::$resultCount;
}
/**
* Adiciona os valores a serem usados na query
*
* @param String $query
* @param Array $params
* @return String
*/
private function bindParams($query, $params){
if(is_array($params)){
$params = self::checkParams($params);
foreach($params as $key => $val){
$query = preg_replace("/:".$key."/", $val, $query);
}
}else{
echo "ERROR: No params (is not array)";
exit();
}
return $query;
}
/**
* Retorna os campos da tabela
*
* @param String $tabela
* @return Array[]
*/
public function fieldsTable($tabela){
$arr = array();
$res = mssql_query("SELECT TOP 1 * FROM ".$tabela);
$i = 0;
while ($i < mssql_num_fields($res)) {
$meta = mssql_fetch_field($res, $i);
if (!$meta) {
throw new Exception("Information not available<br />\n");
}else{
if($i == 0) self::$lastedId = $meta->name;
else $arr[] = $meta->name;
}
$i++;
}
mssql_free_result($res);
self::$fields = $arr;
return $arr;
}
}
?>