<?php

// permissions controll
defined('_EXEC') OR die('Unauthorized access');

/**
 * Třída pro práci s databází
 * @author  Hohn Morris
 * @link    http://www.johnmorrisonline.com/simple-php-class-prepared-statements-mysqli/
 * @edit 	Petr Hladík <hladpe@gmail.com>
 *          Přidán connector do frameworku SimpleCode
 *        	Přidán port do připojení MySQLi
 *        	Znaková sada práce s db
 *        	Prefix názvů tabulek
 *        	Podpora výpěru portu
 *        	setQuery()
 *        	loadResult()
 *        	loadAssoc()
 *        	loadObject()
 *        	loadAssocList()
 *        	loadObjectList()
 *        	
 */

/**
 * using examples:
 * $db = new DB('root', '', 'test');
 * print_r($db->select('SELECT * FROM objects WHERE ID = ?', array(10), array('%d')));
 *
 * ph:
 * $query = "SELECT id FROM #__table WHERE x = 1"
 * $db->setQuery($query);
 * print_r($db->loadResult());
 */

if ( !class_exists( 'SDatabase' ) )
{
	class SDatabase
	{
		/**
		 * připojení
		 *
		 * @var  MySQLi
		 */
		private $connection;

		public function __construct($user, $password, $database, $host = 'localhost', $port = null, $charset = 'utf8', $prefix = '')
		{
			$this->user 	= $user;
			$this->password = $password;
			$this->database = $database;
			$this->host 	= $host;
			$this->port 	= $port;
			$this->prefix 	= $prefix;
			$this->charset 	= strtolower(str_replace('-', '', $charset));
		}


		/**
		 * inicializace - zaregistruj se jako komponenta databáze
		 * @return
		 */
		public static function onInitialization()
		{
			$app = SimpleCode::getInstance();

			$db_host 	= Configuration::getConfig('db_host');
			$db_user 	= Configuration::getConfig('db_user');
			$db_pass 	= Configuration::getConfig('db_pass');
			$db_name 	= Configuration::getConfig('db_name');
			$db_port 	= Configuration::getConfig('db_port');
			$db_char 	= Configuration::getConfig('db_char');
			$db_prefix 	= Configuration::getConfig('db_prefix');
			$app->registerComponent('database', new SDatabase($db_user, $db_pass, $db_name, $db_host, $db_port, $db_char, $db_prefix));
			return;
		}

		public function setQuery($query)
		{
			$this->results = $this->query($query);
			return;
		}


		/**
		 * vrátí jeden výsledek dotazu
		 * @param  string $query dotaz na DB
		 * @return mixed        jedna hodnota
		 */
		public function loadResult($query = null)
		{
			if (!is_null($query))
			{
				$this->setQuery($query);
			}
			$result = null;
			if (!empty($this->results))
			{
				$result = $this->results[0];
				$result = reset($result);
			}
			return $result;
		}


		/**
		 * vrátí jeden řádek výsledku dotazu - asociativní pole
		 * @param  string 	$query 	dotaz na DB
		 * @return mixed        	pole
		 */
		public function loadAssoc($row_id = 0, $query = null)
		{
			if (!is_null($query))
			{
				$this->setQuery($query);
			}
			$result = null;
			if (!empty($this->results))
			{
				$result = $this->results[$row_id];
				$result = (array) $result;
			}
			return $result;
		}


		/**
		 * vrátí jeden řádek výsledku dotazu - objekt
		 * @param  string $query 	dotaz na DB
		 * @return mixed        	objekt
		 */
		public function loadObject($row_id = 0, $query = null)
		{
			if (!is_null($query))
			{
				$this->setQuery($query);
			}
			$result = null;
			if (!empty($this->results))
			{
				$result = $this->results[$row_id];
			}
			return $result;
		}


		/**
		 * vrátí výsledky dotazu - asociativní pole
		 * @param  string $alias 	indexace záznamů
		 * @param  string $query 	dotaz na DB
		 * @return mixed        	pole
		 */
		public function loadAssocList($alias = null, $query = null)
		{
			if (!is_null($query))
			{
				$this->setQuery($query);
			}
			$results = null;
			if (!empty($this->results))
			{
				$results = $this->results;
				if(!isset($alias{0}))
				{
					foreach ($results as $key => &$value)
					{
						$value = (array) $value;
					}
				}
				else
				{
					$new_array = array();
					foreach ($results as $key => $value)
					{
						$value = (array) $value;
						if (isset($value[$alias]))
						{
							$new_array[$value[$alias]] = $value;
						}
					}
					$results = $new_array;
				}
			}
			return $results;
		}


		/**
		 * vrátí výsledky dotazu - objekt
		 * @param  string $alias 	indexace záznamů
		 * @param  string $query 	dotaz na DB
		 * @return mixed        	objekt
		 */
		public function loadObjectList($alias = null, $query = null)
		{
			if (!is_null($query))
			{
				$this->setQuery($query);
			}
			$results = null;
			if (!empty($this->results))
			{
				if (!isset($alias{0}))
				{
					$results = $this->results;
				}
				else
				{
					$new_object = new StdClass();
					foreach ($this->results as $key => $value)
					{
						if (isset($value->$alias))
						{
							$o_alias = $value->$alias;
							$new_object->$o_alias = $value;
						}
					}
					$results = $new_object;
				}

			}
			return $results;
		}


		/**
		 * nahradí prefix tabulek za reálný název
		 * @param  string &$query dotaz / název tabulky
		 */
		private function replacePrefix(&$query)
		{
			$query = str_replace('#_', $this->prefix, $query);
		}

		/**
		 * odstranění připojení z třídy = bude se muset vytvořit nové připojení
		 *
		 * @return
		 */
		public function disconnect()
		{
			$this->connection = null;
			return;
		}


		protected function connect()
		{
			// navaž nové připojení
			if (is_null($this->connection))
			{
				$mysqli = new mysqli($this->host, $this->user, $this->password, $this->database, $this->port);
			}
			// využij stávající připojení
			else
			{
				$mysqli = $this->connection;
			}

			// ověření chyby při připojování - vyvolání chyby způsobí zavření aplikace
			if (isset($mysqli->connect_error) && $mysqli->connect_error != '')
			{
				$err = '[error number: ' . $mysqli->connect_errno . '] SDatabase >> connect() | '. $mysqli->connect_error;
				slog($err, ErrorLog::CORE);
			}


			// set charset
			if (!$mysqli->set_charset($this->charset))
			{
				slog("Error loading character set ".$this->charset.": ".$mysqli->error);
				$app = SimpleCode::getInstance();
				$app->close();
			}

			return $mysqli;
		}


		public function query($query)
		{
			$db = $this->connect();

			$results = null;
			
			// replace tables prefix
			$this->replacePrefix($query);

			$result = $db->query($query);
			
			if (is_object($result))
			{		
				while ( $row = $result->fetch_object() )
				{
					$results[] = $row;
				}
			}
			
			return $results;
		}


		public function insert($table, $data, $format)
		{
			// Check for $table or $data not set
			if ( empty( $table ) || empty( $data ) )
			{
				return false;
			}

			
			// Connect to the database
			$db = $this->connect();
			
			// Cast $data and $format to arrays
			$data = (array) $data;
			$format = (array) $format;
			
			// Build format string
			$format = implode('', $format); 
			$format = str_replace('%', '', $format);
			
			list( $fields, $placeholders, $values ) = $this->prep_query($data);
			
			// replace table prefix
			$this->replacePrefix($table);

			// Prepend $format onto $values
			array_unshift($values, $format); 
			// Prepary our query for binding
			$stmt = $db->prepare("INSERT INTO {$table} ({$fields}) VALUES ({$placeholders})");
			// Dynamically bind values
			call_user_func_array( array( $stmt, 'bind_param'), $this->ref_values($values));
			
			// Execute the query
			$stmt->execute();
			
			// Check for successful insertion
			if ( $stmt->affected_rows )
			{
				return true;
			}
			
			return false;
		}


		public function update($table, $data, $format, $where, $where_format)
		{
			// Check for $table or $data not set
			if ( empty( $table ) || empty( $data ) )
			{
				return false;
			}
			
			// Connect to the database
			$db = $this->connect();
			
			// Cast $data and $format to arrays
			$data = (array) $data;
			$format = (array) $format;
			
			// Build format array
			$format = implode('', $format); 
			$format = str_replace('%', '', $format);
			$where_format = implode('', $where_format); 
			$where_format = str_replace('%', '', $where_format);
			$format .= $where_format;
			
			list( $fields, $placeholders, $values ) = $this->prep_query($data, 'update');
			
			//Format where clause
			$where_clause = '';
			$where_values = '';
			$count = 0;
			
			foreach ( $where as $field => $value )
			{
				if ( $count > 0 )
				{
					$where_clause .= ' AND ';
				}
				
				$where_clause .= $field . '=?';
				$where_values[] = $value;
				
				$count++;
			}
			// Prepend $format onto $values
			array_unshift($values, $format);
			$values = array_merge($values, $where_values);

			// replace table prefix
			$this->replacePrefix($table);

			// Prepary our query for binding
			$stmt = $db->prepare("UPDATE {$table} SET {$placeholders} WHERE {$where_clause}");
			
			// Dynamically bind values
			call_user_func_array( array( $stmt, 'bind_param'), $this->ref_values($values));
			
			// Execute the query
			$stmt->execute();
			
			// Check for successful insertion
			if ( $stmt->affected_rows )
			{
				return true;
			}
			
			return false;
		}


		public function select($query, $data, $format)
		{
			// Connect to the database
			$db = $this->connect();
			
			// replace tables prefix
			$this->replacePrefix($query);

			//Prepare our query for binding
			$stmt = $db->prepare($query);
			
			//Normalize format
			$format = implode('', $format); 
			$format = str_replace('%', '', $format);
			
			// Prepend $format onto $values
			array_unshift($data, $format);
			
			//Dynamically bind values
			call_user_func_array( array( $stmt, 'bind_param'), $this->ref_values($data));
			
			//Execute the query
			$stmt->execute();
			
			//Fetch results
			$result = $stmt->get_result();
			
			//Create results object
			while ($row = $result->fetch_object())
			{
				$results[] = $row;
			}
			return $results;
		}


		public function delete($table, $id)
		{
			// Connect to the database
			$db = $this->connect();
			
			// replace table prefix
			$this->replacePrefix($table);
			
			// Prepary our query for binding
			$stmt = $db->prepare("DELETE FROM {$table} WHERE ID = ?");
			
			// Dynamically bind values
			$stmt->bind_param('d', $id);
			
			// Execute the query
			$stmt->execute();
			
			// Check for successful insertion
			if ( $stmt->affected_rows )
			{
				return true;
			}
		}


		private function prep_query($data, $type='insert')
		{
			// Instantiate $fields and $placeholders for looping
			$fields = '';
			$placeholders = '';
			$values = array();
			
			// Loop through $data and build $fields, $placeholders, and $values			
			foreach ( $data as $field => $value )
			{
				$fields .= "{$field},";
				$values[] = $value;
				
				if ( $type == 'update')
				{
					$placeholders .= $field . '=?,';
				} else {
					$placeholders .= '?,';
				}
				
			}
			
			// Normalize $fields and $placeholders for inserting
			$fields = substr($fields, 0, -1);
			$placeholders = substr($placeholders, 0, -1);
			
			return array( $fields, $placeholders, $values );
		}


		private function ref_values($array)
		{
			$refs = array();
			foreach ($array as $key => $value)
			{
				$refs[$key] = &$array[$key]; 
			}
			return $refs; 
		}

	}
}

?>