Location: PHPKode > scripts > Controller > controller/Controller.class.php
<?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;
	}		
}
?>
Return current item: Controller