<?
	/* 3web.bg Framework
	 * Copyright 2010-2011 All Rights Reserved.
	 * Database Class
	 * PDO Singleton Class
	 * @file Database.php
	 * Last modified 03.01.2012
	 */
	 
class Database extends DbConfig
{
	
	const PREG_MAIL = '#^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$#i';
	
	private static $_instance = array();
	
	// Debugger on or off
	private static $debugger = 0;
	private static $debugger_data = array();
	
	// Current connection (PDO)
	private $_connection = NULL;
	
	// Current tables prefix
	private $_prefix = NULL;

	// Start of query execution
	private $start_exec_time;
	
	// The number of decimal points
	private $decimal_points = 2;
	
	// The number of affected rows on last query
	private $affected_rows = 0;
	
	// Connect to DB
	private function __construct($conn = 'default')
	{
		try {
			if (!isset(parent::$DB_CONNECTIONS[$conn])) {
				$error = "DatabaseError: Database $conn is not configured!";
				throw new DbException($error, DbException::DBConnectionError);
			}
			$conAuth = parent::$DB_CONNECTIONS[$conn];
			// try to connect
			$dsn = $conAuth['db_server_type'].':host='.$conAuth['db_host'].';dbname='.$conAuth['db_name'];
			$this->_connection = new PDO($dsn, $conAuth['db_user'], $conAuth['db_password']);
			// set error handling
			$this->_connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			// Set encoding
			$this->_connection->query("SET NAMES '" . $conAuth['db_charset'] . "'");
			$this->_prefix = $conAuth['db_table_prefix'];
		} catch(PDOException $e) {
			$lang = Registry::get('lang');
			$error = $lang['DBConnectionError'];
			if (empty($error)) $error = 'DatabaseError: Cannot connect to database!';
			throw new DbException($error, DbException::DBConnectionError);
		}
	}
	
	/**
	 * 
	 * Singleton
	 */
	public static function getInstance($conn = 'default')
	{
        if (!self::$_instance[$conn] instanceof self) {
			self::$_instance[$conn] = new self($conn);
		}
		return self::$_instance[$conn];
    }
	
	public function __clone()
	{
        trigger_error('Clone is not allowed.', E_USER_ERROR);
    }
	
	public function setDebugger($value)
	{
		self::$debugger = (bool)$value;
	}
	
	public function getDebugger()
	{
		return selft::$debugger;
	}
	
	public function setDecimalPoints($value)
	{
		$this->decimal_points = (int)$value;
	}
	
	public function getPDO()
	{
		return $this->_connection;
	}
	
	protected function markStart()
	{
		$this->start_exec_time = microtime(true);
	}
	
	protected function addDebugData($query)
	{
		$end_exec_time = microtime(true);
		self::$debugger_data[] = array("time" => round($end_exec_time - $this->start_exec_time, 4), "query" => $query);
	}
	
	public function formatDecimal($value)
	{
		return number_format($value, $this->decimal_points, '.', '');
	}
	
	public function quote($string, $type=null)
	{
        return $this->_connection->quote($string, $type);
    }
	
	/**
	 * Search function
	 */
    public function search($sql, $values_arr = array())
    {
	    //Prepare the PDO statement and execute it
        $select = $this->_connection->prepare($sql);        
        $select->execute($values_arr);

		return $select->fetchAll(PDO::FETCH_ASSOC);
    }
	
	/**
	 * Replaces the table prefix
	 */
	public function checkDbPrefix($query)
	{
		$query = str_replace('#_', $this->_prefix, $query);
		return $query;
	}

	/**
	 * Function used to return all found rows from the database
	 */
	public function fetchAll( $query, $params=null, $options=array() )
	{
		// Get start time
		$this->markStart();
		// Apply tablename prefix
		$query = $this->checkDbPrefix($query);
		// Execure the query
		$sth = $this->_connection->prepare($query, $options);
		try{
			$sth->execute( $params );
		} catch(PDOException $e) {
			$this->onDbError($query, $e->getMessage());
		}
		// Fetch data
		$result = $sth->fetchAll(PDO::FETCH_ASSOC);
		// If the debugger is on - add the time and the query
		if (self::$debugger) $this->addDebugData($query);
		// Return fetched data
		return $result;
	}


	/**
	 * Function used to return one found row from the database
	 */
	public function fetchRow( $query, $params=null, $options=array() )
	{
		// Get start time
		$this->markStart();
		
		$query = $this->checkDbPrefix($query);
		
		// Execure the query
		$sth = $this->_connection->prepare($query, $options);
		try{
			$sth->execute( $params );
		} catch(PDOException $e) {
			$this->onDbError($query, $e->getMessage());
		}
		
		// Fetch data
		$result = $sth->fetch(PDO::FETCH_ASSOC);
		
		// If the debugger is on - add the time and the query
		if (self::$debugger) $this->addDebugData($query);
		
		// Return fetched data
		return $result;
	}


	/**
	 * Function used to return one cell from the database
	 */
	public function fetchOne( $query, $params=null, $options=array() ){
		
		// Get start time
		$this->markStart();
		
		$query = $this->checkDbPrefix($query);
		
		// Execure the query
		$sth = $this->_connection->prepare($query, $options);
		try{
			$sth->execute( $params );
		} catch(PDOException $e) {
			$this->onDbError($query, $e->getMessage());
		}
		
		// Fetch data
		$result = $sth->fetch(PDO::FETCH_NUM);
		
		// If the debugger is on - add the time and the query
		if (self::$debugger) $this->addDebugData($query);
		
		// Return fetched data
		return $result[0];
	}

	/**
	 * The function returns the id from the last inserted item in the database
	 */
	public function lastInsertId()
	{
		return $this->_connection->lastInsertId(); 
	}
	
	public function affectedRows()
	{
		return $this->affected_rows; 
	}


	/**
	 * The function is used to insert data in the database.
	 * 
	 * First parameter is the name of the table
	 * Second parameter is the data we want to insert. It must be an array.
	 *
	 * @param table string
	 * @param data array
	 * @param secure bool
	 * @param options array
	 */
	public function insert($table, $data, $secure=false, $options=array()) {

		// If the insert data is not an array
		if(!is_array($data)) return;

		// Filter the input data
		foreach($data as $col => $val){
			$cols[] = $col;
			
			if($secure == true){
				if(is_float($val)){
					$vals[] = $this->formatDecimal($val);
				}
				else{
					$vals[] = $this->quote(htmlspecialchars(trim($val)));
				}
			}
			else{
				if(is_float($val)){
					$vals[] = $this->formatDecimal($val);
				}
				else{
					$vals[] = $this->quote($val);
				}
			} 
		}
		
		$table = $this->checkDbPrefix($table);
		
		// Form the SQL query
		$query = "INSERT INTO $table (" . implode(', ', $cols) . ') VALUES (' . implode(', ', $vals) . ')';
		
		// Get start time
		$this->markStart();
		
		// Execure the query		
		$sth = $this->_connection->prepare($query, $options);
		try {
			$sth->execute();
		} catch(PDOException $e) {
			$this->onDbError($query, $e->getMessage());
		}
		
		// If the debugger is on - add the time and the query
		if (self::$debugger) $this->addDebugData($query);
	}


	/**
	 * The function is used to delete data from the database.
	 * 
	 * First parameter is the name of the table
	 * Second parameter forms the where clause of the sql. It must be a string.
	 * Third parameter forms the limit clause of the sql. It must be a string.
	 *
	 * @param table string
	 * @param where string
	 * @param limit string
	 * @param options array
	 */
	public function delete($table, $where, $limit='', $options=array())
	{

		// Check the data
		if(!isset($table) || !is_string($table)) $this->onDbError('Missing table name on Delete attampt', '');
		if(!isset($where) || !is_string($where)) $this->onDbError('Missing $where array on Delete attampt', '');
		if(!empty($limit) && !is_int($limit)) $this->onDbError('Limit count is not integer on Delete attampt', '');
		
		// Set some limit
		if(!empty($limit)) $limit = " LIMIT ".$limit;
		
		$table = $this->checkDbPrefix($table);

		// Clear the data
		$table = htmlspecialchars(addslashes(trim($table)));
		$where = htmlspecialchars(trim($where));

		// Form the SQL query
		$query = "DELETE from $table WHERE " . $where . $limit;
		
		unset($where, $limit);
		
		// Get start time
		$this->markStart();
		
		// Execure the query	
		$sth = $this->_connection->prepare($query, $options);
		try {
			$sth->execute();
		} catch(PDOException $e) {
			$this->onDbError($query, $e->getMessage());
		}
		
		// If the debugger is on - add the time and the query
		if (self::$debugger) $this->addDebugData($query);
	}


	/**
	 * The function is used to update data from the database.
	 * 
	 * First parameter is the name of the table
	 * Second parameter is the updated data. It must be a array.
	 * Third parameter forms the where clause of the sql. It must be a string.
	 *
	 * @param table string
	 * @param update array
	 * @param where string
	 * @param filter bool
	 * @param options array
	 */
	public function update($table, array $update, $where, $filter = true, $options=array())
	{
		// Clear the data and form the update string
		$update_string = '';
		$update_array = array();
		foreach ($update as $key => $val) {
			if ($filter == true) {
				if (!is_float($val)) {
					$val = htmlspecialchars(trim($val));
				} else {
					$val = $this->formatDecimal($val);
				}
			}
			$val = $this->quote($val);
			$update_array[] = $key . '=' . $val;
		}
		$update_string = implode(", ", $update_array);
		
		$table = $this->checkDbPrefix($table);
		
		if($where) $where_q = " WHERE ".$where;
	       
		// Form the SQL query
		$query = "UPDATE $table SET " . $update_string . $where_q;
		
		// Unset variables
		unset($update_string, $where);
		
		// Get start time
		$this->markStart();
		
		// Execure the query
		$sth = $this->_connection->prepare($query, $options);
		try {
			$sth->execute();
		} catch(PDOException $e) {
			$this->onDbError($query, $e->getMessage());
		}
		
		// If the debugger is on - add the time and the query
		if (self::$debugger) $this->addDebugData($query);
	}


	/**
	 * The function is used to just execute a query.
	 */
	public function query($query, $params=null, $options=array())
	{
		// Get start time
		$this->markStart();
		
		$query = $this->checkDbPrefix($query);
		
		// Execure the query
		$sth = $this->_connection->prepare($query, $options);
		try {
			$sth->execute($params);
			$this->affected_rows = $sth->rowCount();
		} catch(PDOException $e) {
			$this->onDbError($query, $e->getMessage());
		}
		
		// If the debugger is on - add the time and the query
		if (self::$debugger) $this->addDebugData($query);
	}


	/** 
	 * Function displaying the debugger. Debugger display information about the last executed queries.
	 */
	public function showSQLDebugger()
	{
	
		// Check if the debugger is on
		if (self::$debugger){
		
			// Form the main table
			$return = '';
			
			$return .= '
				<table border="1" cellpadding="2" cellspacing="2" width="100%" style="font-size: 11px;clear:both;" class="debugger">
					<tr>
						<td align="center" colspan="3"><b>MySQL Debugger Results</b></td>
					</tr>
					<tr>
						<td align="center" width="5%"><i>Count</i></td>
						<td align="left" width="75%"><i>SQL query</i></td>
						<td align="center"><i>Time to execute</i></td>
					</tr>
			';
			
			// Store all queries time
			$time = 0;
			
			// Add queries data
			foreach (self::$debugger_data as $key => $value) {
				$temp = $key+1;
				if ( $value['time'] >= 1 ) 
					$return .= '<tr style="color:red">';
				else
					$return .= '<tr>';
				
				$return .= '
						<td align="center"><b>'.$temp.'</b></td>
						<td align="left"><b>'.$value['query'].'</b></td>
						<td align="center"><b>'.$value['time'].'</b></td>
					</tr>
				';
				$time += $value['time'];
			}
			
				$return .= '
					<tr>
						<td align="center" style="color: red"> - - </td>
						<td align="left" style="color: red"><b>All Queries Time</b></td>
						<td align="center" style="color: red"><b>'.$time.'</b></td>
					</tr>
				';
			
			// Show the SQL debugger
			echo $return . "</table><br /><br />";
		}
	}
	
	protected function onDbError($query, $message)
	{
		if (self::$debugger) {
			$this->addDebugData($query);
			$this->addDebugData($message);
			$this->showSQLDebugger();
		}
		$lang = Registry::get('lang');
		if (empty($lang['DBStatementError'])) $lang['DBStatementError'] = 'A database error has occured. Please, try again later!';
		exit ($lang['DBStatementError']);
	}
	
	public function validate($data, $validators)
	{
		$error = array();
		foreach ($validators as $k => $v) {
			$val = $data[$k];
			if ($v[0] && empty($val) && $val !== 0) $error[] = $v[1];
			elseif (!empty($v[2])) {
				switch ($v[2]) {
					case 'str':
						if ((notblank($v[3]) && strlen($val) < $v[3]) || (notblank($v[4]) && strlen($val) > $v[4])) $error[] = $v[5];
						if (notblank($v[6]) && preg_match($v[6], $val) == 0) $error[] = $v[7];
					break;
					case 'arr':
						if ((notblank($v[3]) && count($val) < $v[3]) || (notblank($v[4]) && count($val) > $v[4])) $error[] = $v[5];
					break;
					case 'int':
						$val = (int)$val;
						if ((notblank($v[3]) && $val < $v[3]) || (notblank($v[4]) && $val > $v[4])) $error[] = $v[5];
					break;
					case 'flt':
						$val = (float)$val;
						if ((notblank($v[3]) && $val < $v[3]) || (notblank($v[4]) && $val > $v[4])) $error[] = $v[5];
					break;
				}
			}
		}
		return $error;
	}

	public function validateExisting($data, $validators)
	{
		$error = array();
		foreach ($validators as $k => $v) {
			$val = $this->quote($data[$k]);
			$query = "SELECT COUNT(*) FROM `$v[0]` WHERE `$k`=$val";
			if ($v[2] > 0) $query .= " AND `$v[1]`<>" . (int)$v[2];
			$check = (int)$this->fetchOne($query);
			if ($check > 0) $error[] = $v[3];
		}
		return $error;
	}

}

class DbException extends Exception
{
	const DBConnectionError = 1;
}

/*
 *	returns true if the variable is not empty ot its a zero
 */
function notblank($var)
{
	if (empty($var)) {
		if ($var === 0) return true;
		return false;
	}
	return true;
}