<?php
	/**
	 * Simple database connection class.
	 * 
	 * @link http://www.oldauntie.org
	 * @author Maggie Marple <oldauntie@oldauntie.org>
	 * @version 1.2
	 */
	class Connection{
		var $debug 		= false;
		var $sql 		= '';
		var $sqlstate 	= '';
		private $Conn = null;
		
		const RESULT_ASSOC 		= 1;
		const RESULT_NUM	 	= 2;
		const RESULT_BOTH 		= 3;
		
		/**
         * Creates a new Connection object.
         *
         * @param string $dbUser the user who connet to the database
         * @param string $dbPwd the password who connet to the database
         * @param string $dbName the database name to connect to
         * @param string $dbName the database host
         * @param boolean $autocommit set it to true if you don't want to change transaction isolation model
         * 
         */
		function __construct($dbUser='', $dbPwd='', $dbName='', $dbHost='', $autocommit=false){
			if( empty( $dbUser ) ){
				$this->setDefaultConnection();
			}else{
				$this->setConnection( $dbUser, $dbPwd, $dbName, $dbHost );
			}
			
			// set the transaction isolation model
			if( $this->Conn ){
				mysqli_autocommit ($this->Conn, $autocommit);
				$this->execute("SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED");
			}

		}
		
		
		/**
		 * Returns the *Singleton* instance of this class.
		 *
		 * @staticvar Singleton $instance The *Singleton* instances of this class.
		 *
		 * @return Singleton The *Singleton* instance.
		 */
		public static function getInstance()
		{
			static $instance = null;
			if (null === $instance) {
				$instance = new static();
			}

			return $instance;			
		}
		
		/**
		 * Private clone method to prevent cloning of the instance of the
		 * *Singleton* instance.
		 *
		 * @return void
		 */
		private function __clone()
		{
		}

		/**
		 * Private unserialize method to prevent unserializing of the *Singleton*
		 * instance.
		 *
		 * @return void
		 */
		private function __wakeup()
		{
		}
		
		
		/**
         * Sets the debug mode on (to be implemented).
         *
         * @param string $bDebug
         */
		function setDebug( $bDebug ){
			$this->debug = $bDebug;
		}
		
		/**
         * Create and set a database connection.
         *
         * @param string $dbUser the database username
         * @param string $dbPwd the database password
         * @param string $dbName the database name
         * @param string $dbHost the database host
         * 
         * @return	object
         */
		function setConnection($dbUser, $dbPwd, $dbName, $dbHost){
			$this->Conn = mysqli_connect($dbHost, $dbUser, $dbPwd, $dbName);
			return $this->Conn;
		}
		
		/**
         * Create and set the default database connection given all params from config file.
         *
         * @return	object
         */
		function setDefaultConnection(){
			return $this->setConnection(_DBUSER, _DBPWD, _DBNAME, _DBHOST);
		}
		
		
		
		function executePreparedQuery(){
		}
		
		
		/**
         * Execute an SQL statement.
         *
         * @param string $sSQl the statement to be executed
         * @param string $result_type detemine if an array, an object or both are returned
         * 
         * @return	mixed
         */
		function execute($sSQL, $result_type=MYSQLI_ASSOC)
		{
			$RV = null;
			
			if($result = mysqli_query($this->Conn, $sSQL, MYSQLI_USE_RESULT)){
				
				$nf = mysqli_field_count($this->Conn);
				
				if( $nf > 0 ){
					while($row = mysqli_fetch_array($result, $result_type)){
						$RV[] = $row;	
					}
				}else{
					$RV = true;
				}
			}else{
				$RV = false;
			}
			
			// set properties for logging purpose
			$this->sql			= $sSQL;
			$this->sqlstate		= mysqli_sqlstate( $this->Conn );
			
			return $RV;
		}
		
		/**
		 * Alias of selectOneRow()
		 *
		 * @param string $sql SQL statement
		 * 
		 * @return mixed Array or false
		 */
		function selectOne($sql)
		{
			return $this->selectOneRow($sql);
		}
		
		/**
		 * Select and return the result of one row
		 *
		 * @param string $sql SQL statement
		 * 
		 * @return mixed Array or false
		 */
		function selectOneRow($sql)
		{
			$aRV = array();
			
			$aRV = $this->execute($sql);
			
			if(empty($aRV)){
				return false;
			}else{
				return array_shift($aRV);
			}
		}
		
		
		/**
		 * Select one value and return it
		 *
		 * @param string $sql SQL statement
		 * 
		 * @return mixed Array or false
		 */
		function selectOneValue( $sql ){
			$aRV = $this->selectOne( $sql );

			if(empty($aRV)){
				return false;
			}else{
				return array_shift($aRV);
			}
		}
		
		
		function exists( $sql ){
			$aRV = $this->selectOne( $sql );
			
			if( empty( $aRV ) ){
				return false;
			}else{
				return true;
			}
		}
		
		
		public function escape ($string){
			return mysqli_real_escape_string( $this->Conn, $string );
		}
		
		/**
		 * Load data from a table (entity) given an array of filters
		 * 
		 * @param string $entity
		 * @param array $where
		 *        	array(field_name => param_value, [...]) 
		 * 			e.g. array('uid' = 1, 
		 * @param array $fields
		 * @return boolean
		 */
		static function entity_load($entity, $fields = array(), $where = array(), $order_by_clause = '') {
			// Dynamically compose a SQL query:
			$query 		= array ();
			$params 	= array ();
			$field_list = "*";
			
			if( is_array( $fields ) && empty( $fields ) == false){
				$field_list = implode(', ', $fields);
			}
			
			$sSQL = 'SELECT ' . $field_list . ' FROM ' . $entity . ' WHERE 1=1';

			
			if(is_array($where) && empty($where) == false){
				foreach ( $where as $key => $value ) {
					if (is_integer($value)){
						$query [] = "$key = %d";
						$params [] = $value;
					} else {
						$query [] = "LOWER($key) = LOWER('%s')";
						$params [] = $value;
					}
				}
				
				$sSQL .= ' AND ' . implode ( ' AND ', $query );
				
				// build the final Sql statement adding params
				$sSQL = vsprintf ( $sSQL, $params );
			}
			
			if( empty( $order_by_clause ) == false ){
				$sSQL .= ' ORDER BY ' . $order_by_clause;
			}
			
			$con = new Connection();
			$aRV = $con->execute( $sSQL );
			unset( $con );
			return $aRV;
		}
		
		
		static function entity_value_load( $entity, $field = '', $where = array() ){
			$rv = NULL;
			$entity_array = self::entity_load( $entity, array( $field ), $where );
			
			if( is_array( $entity_array ) == true && empty( $entity_array ) === false ){
				$aRV = array_shift( $entity_array );

				$rv = $aRV[ $field ];
			}
			return $rv;
		}
		
		
		static function entity_row_exists( $entity, $where = array() ){
			$rv = false;
			$entity_array = self::entity_load( $entity, array(), $where );
			
			if( is_array( $entity_array ) == true && empty( $entity_array ) === false ){
				$rv = true;
			}
			return $rv;
		}
		
		
		
		
		/**
		 * Select one value and return it
		 *
		 * @return mixed int or false
		 */
		function insertID(){
			return mysqli_insert_id($this->Conn);
		}
		
		/**
         * Commit a database transaction.
         *
         */
		function commit(){
			return mysqli_commit($this->Conn);
		}
		
		/**
         * Rollback a database transaction.
         *
         */
		function rollback(){
			return mysqli_rollback($this->Conn);
		}
		
		/**
		 * Close the database connection
		 */
		function close(){
			return mysqli_close($this->Conn);
		}
		
		
		public function import( $filename ){
			if( file_exists( $filename ) == true && is_readable( $filename ) == true ){
				$lines = file($filename);
			}else{
				return false;
			}
			
			d($lines);
			// Temporary variable, used to store current query
			$templine = '';
			$rv = true;
			
			// Loop through each line
			foreach ($lines as $line){
				// Skip it if it's a comment
				if ( substr( $line, 0, 2 ) == '--' || $line == '' ) {
					continue;
				}
				// Add this line to the current segment
				$templine .= $line;
				// If it has a semicolon at the end, it's the end of the query
				if (substr(trim($line), -1, 1) == ';'){
					// Perform the query
					// mysql_query($templine) or print('Error performing query \'<strong>' . $templine . '\': ' . mysql_error() . '<br /><br />');
					$rv = $rv && $this->execute( $templine );


					// Reset temp variable to empty
					$templine = '';
				}
			}
			
			return $rv;
		}
	}
