<?php
////////////////////////////////////////////////////
// Conciliación: gestión de conciliación de activos
// Version: 1.0
//
// Copyright (C) 2014  Eustotem Multimedia, S.L.
//
// License: MIT, see LICENSE
////////////////////////////////////////////////////


class SQL{
	public static $debugMode=TRUE;		
	public static $haltOnError=FALSE;	
	public static $queryResult=NULL;	
	public static $lastId=NULL;			
	public static $numRows=NULL;		
	public static $lockTables=TRUE;		
	public static $raiseEvents=FALSE;	
	
	
	public static function getTree($tabla,$id,$parentfield) {
		$clave=self::getPrKeyInfo($tabla);
		$id=self::autoQuote($clave['Type'], $id);
		$idfield=$clave['Field'];
		$sql="
		SELECT 
			node.*, 
			(SELECT COUNT($idfield) AS cuenta FROM $tabla WHERE $parentfield=node.$idfield) AS __children,
			(SELECT COUNT(p.$idfield)-1 AS id FROM $tabla n, $tabla p WHERE n.lft BETWEEN p.lft AND p.rgt AND n.$idfield=node.$idfield) AS __level
		FROM 
			$tabla node, $tabla parent
		WHERE 
			node.lft BETWEEN parent.lft AND parent.rgt 
			AND parent.$idfield = $id 
		ORDER BY node.lft;
		";
		$q=self::query($sql);
		$ret=self::fetchAll($q);
		return $ret;
	}
	
	
	public static function sanitize($value){
		if (get_magic_quotes_gpc()) $value = stripslashes($value);
		$value = mysql_real_escape_string($value);
		return $value;
	}
	
	
	public static function query($sql,$inner=NULL){
		if($sql==NULL || $sql=="") return FALSE;		
		$temp=mysql_query($sql);
		if(!$temp){
			self::handle_error(mysql_error(), $sql);
		}else{
			self::$queryResult=$temp;
			if($inner==NULL){
				$t2=explode(" ",$sql);
				$command=strtoupper($t2[0]);					
				if(in_array($command,array("SELECT","SHOW","DESCRIBE","EXPLAIN"))){
					self::$numRows=self::getNumRows($temp);
					self::$lastId=NULL;
				}else{
					self::$lastId=self::getLastId();
					self::$numRows=NULL;
				}
			}
		}
		return $temp;
	}
	
	
	private static function autoQuote($tipo, $valor){
		$numTypes=array("BIT","TINYINT","BOOL","BOOLEAN","SMALLINT","MEDIUMINT","INT","INTEGER","BIGINT","FLOAT","DOUBLE","DECIMAL","DEC");
		$quoted=TRUE;
		for($i=0;$i<count($numTypes);$i++){
			if(stripos(strtoupper($tipo),$numTypes[$i])!==false){
				$quoted=FALSE;
				break;
			}
		}
		if($quoted){
			return "'".self::sanitize($valor)."'";
		}else{
			return $valor;
		}
	}
	
	
	public static function insertRecord($tabla,$data,$res){
		if(self::$raiseEvents)Utils::raiseAppEvent(6,$data,$res);				
		if(self::$lockTables) self::query("LOCK TABLES $tabla WRITE", TRUE);	
		
		$keys=array_keys($data);
		$keys2=array();
		$values=array();
		$f=self::getTableInfo($tabla);		
		foreach($f as $campo){
			if(in_array($campo['Field'], $keys)){
				$keys2[]=$campo['Field'];
				$values[]=self::autoQuote($campo['Type'], $data[$campo['Field']]);
			}
		}
		$sql="INSERT INTO $tabla (" . implode(", ",$keys2) . ") VALUES (" . implode(", ",$values) . ")";
		$q=self::query($sql);
		
		if(self::$lockTables) self::query("UNLOCK TABLES", TRUE);				
		if($q) if(self::$raiseEvents)Utils::raiseAppEvent(7,$data,$res);		
	}
	
	
	public static function updateRecord($tabla,$data,$res,$id){
		if(self::$raiseEvents)Utils::raiseAppEvent(8,$data,$res,$id);			
		if(self::$lockTables) self::query("LOCK TABLES $tabla WRITE", TRUE);	
		
		$keys=array_keys($data);
		$values=array();
		$f=self::getTableInfo($tabla);
		$clave=self::getPrKeyInfo($tabla);
		foreach($f as $campo){
			if(in_array($campo['Field'], $keys)){
				if($campo['Field']==$clave['Field']) continue;					
				$values[]=$campo['Field']."=".self::autoQuote($campo['Type'], $data[$campo['Field']]);
			}
		}
		
		$sql="UPDATE $tabla SET " . implode(", ",$values) . " WHERE {$clave['Field']}=".self::autoQuote($clave['Type'],$id);
		$q=self::query($sql);
		
		if(self::$lockTables) self::query("UNLOCK TABLES", TRUE);				
		if($q) if(self::$raiseEvents)Utils::raiseAppEvent(9,$data,$res,$id);	
	}
	
	
	public static function deleteRecords($tabla, $data, $res){
		if(self::$raiseEvents)Utils::raiseAppEvent(10,$data,$res);			
		if(self::$lockTables) self::query("LOCK TABLES $tabla WRITE", TRUE);	
		
		$ids=array();
		$clave=self::getPrKeyInfo($tabla);
		foreach($data as $dato){
			$ids[]=self::autoQuote($clave['Type'], $dato);
		}
		
		$sql="DELETE FROM $tabla WHERE {$clave['Field']} IN(" . implode(", ", $ids) . ")";
		$q=self::query($sql);
		
		
		if(self::$lockTables) self::query("UNLOCK TABLES", TRUE);				
		if($q) if(self::$raiseEvents)Utils::raiseAppEvent(11,$data,$res);	
	}
	

	public static function getLastId(){
		return mysql_fetch_assoc(mysql_query("SELECT LAST_INSERT_ID()"));
	}

	
	public static function getNumRows($recordset){
		if(get_resource_type($recordset)!="mysql result" || $recordset===TRUE || $recordset===FALSE) return 0;
		return mysql_num_rows($recordset);
	}


	public static function handle_error($error, $sql){
		if(self::$debugMode==TRUE){
			echo "<pre style='border:1px solid #900; background-color:#EFDC86; padding:15px; color:#600; font:11px Verdana;'>";
			echo "<strong>ERROR SQL</strong>\n\n<strong>Description:</strong> ".$error."\n\n<strong>Source</strong>: ".$sql."\n";
			echo "</pre>";
		}
		if(self::$haltOnError==TRUE) die();
	}
	

	public static function fetchOne($sql){
		if($sql==NULL || $sql=="") return FALSE;
		$temp=explode(" ",$sql);
		if(!in_array(strtoupper($temp[0]), array("SELECT","DESCRIBE","SHOW","EXPLAIN"))) return FALSE;
		return mysql_fetch_assoc(self::query($sql,TRUE));
	}
	
	
	public static function fetchAll($recordset){
		if(get_resource_type($recordset)!="mysql result" || $recordset===TRUE || $recordset===FALSE) return FALSE;
		$temp=array();
		while($f=mysql_fetch_assoc($recordset)){
			$temp[]=$f;
		}
		return $temp;
	}
	
	
	public static function getTableInfo($tabla){
		return self::fetchAll(self::query("show FULL columns FROM $tabla",TRUE));
	}
	
	public static function getPrKeyInfo($tabla){
		return self::fetchOne("show FULL columns FROM $tabla WHERE `KEY`='PRI'",TRUE);
	}
	
	public static function toJson($resultset,$id_field,$extra=NULL){
		if($extra==NULL){
			$response->page = 1;
			$response->total = 1;
			$response->records = count($resultset);
		}else{
			$response->page = (int)$extra['page'];
			$response->total = (int)$extra['total'];
			$response->records = (int)$extra['records'];
		}
		$i=0;
		foreach($resultset as $row) {
			if($row['rgt'] == $row['lft']+1) $leaf = 'true';else $leaf='false';
			$yuyu=array();
			foreach($row as $nombre=>$campo){
				$yuyu[$nombre]=$campo;
			}
			$yuyu['isLeaf']=$leaf;
			$yuyu['expanded']=TRUE;
			$response->rows[$i]=$yuyu;
			$i++;
		} 
		return json_encode($response);
	}
}
?>