<?php
/**
 *  Copyright (C) 2010  Kai Dorschner
 *
 *  This program is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation, either version 3 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program.  If not, see <http://www.gnu.org/licenses/>.
 *
 * @author Kai Dorschner <the-hide@address.com>
 * @copyright Copyright 2010, Kai Dorschner
 * @license http://www.gnu.org/licenses/gpl.html GPLv3
 * @package mocovi
 * @subpackage controls
 */

class dbreader_control extends Control
{
	protected $db;
	protected $fetched;

	protected $defaultOptions = array
		( 'where'				=> ''		// MySQL WHERE clause
		, 'orderby'				=> ''		// MySQL ORDER BY clause
		, 'limit'				=> '30'		// MySQL LIMIT clause
		, 'preserveReferences'	=> 'false'	// Show only results from different tables if references do match! (foreign key constraints)
		);

	/**
	 * @override
	 */
	protected function _beforeCreateNode(DomNode $parent)
	{
		$this->node = $this->parentNode;
		$this->parentNode = $parent->parentNode;
		return false;
	}

	/**
	 * @override
	 */
	protected function _beforeRunChilds()
	{
		if(!$this->connectDatabase())
			return false;
		if($result = $this->db->query($this->createQuery()))
		{
			$all = array();
			while($fetched = $result->fetch_array(MYSQLI_ASSOC))
				$all[] = $fetched;
			if(count($all) > 0)
				foreach($this->find('dbfield') as $child)
					$child->fetched = $all[0];
			$clones = array();
			for($i = 1; $i < count($all); $i++)
				foreach($this->controls as $sub)
				{
					$clones[] = $clone = $sub->getClone()->load($this->node);
					foreach($clone->find('dbfield') as $cloneChild)
						$cloneChild->fetched = $all[$i];
					$clone->run();
				}
			$result->close();
		}
		if(($difference = $this->countAll() - $this->count()) > 0)
			ControlFactory::create(new DomElement('paragraph', 'and '.$difference.' more...'))->load($this->parentNode)->run();
		return true;
	}

	/**
	 * Creates a new MySQL database connection and sets encoding as utf8.
	 *
	 * @access protected
	 * @return $this
	 */
	protected function connectDatabase()
	{
		$this->db = new MySQLi();
		$this->db->connect
			( $GLOBALS['database']['host']
			, $GLOBALS['database']['username']
			, $GLOBALS['database']['password']
			, $this->getOption('use')
			);
		if ($this->db->connect_error)
			$this->error('Connect Error (' . $this->db->connect_errno . ') '.$this->db->connect_error);
		$this->db->query('SET NAMES utf8');
		$this->db->query('SET CHARACTER SET utf8');
		return $this;
	}

	public function countAll()
	{
		$tables = array();
		$fields = array();
		foreach($this->find('dbfield') as $field)
		{
			$val				= explode('.', $field->getOption('name'));
			$tables[$val[0]]	= $val[0];
			$fieldname			= $val[0].'.'.$val[1].' AS '.$val[0].'_'.$val[1];
			$fields[$fieldname]	= $fieldname;
		}
		if(count($tables) > 0)
		{
			if(strtolower($this->getOption('preserveReferences')) == 'true')
				$this->smartWHERE($tables);
			return	$this->db->query(
						 'SELECT COUNT(*) as count'
						.' FROM '.implode(', ', $tables)
						.($this->getOption('where') ? ' WHERE '.$this->getOption('where') : '')
					)
					->fetch_object()
					->count;
		}
		return null;
	}

	public function count()
	{
		$count = (int)$this->countAll();
		$limits = preg_split('/\s*,\s*/', $this->getOption('limit'));
		$limit = (int)$limits[count($limits) - 1];
		return ($count > $limit ? $limit : $count);
	}

	protected function createQuery()
	{
		$tables = array();
		$fields = array();
		foreach($this->find('dbfield') as $field)
		{
			$val				= explode('.', $field->getOption('name'));
			$tables[$val[0]]	= $val[0];
			$fieldname			= $val[0].'.'.$val[1].' AS '.$val[0].'_'.$val[1];
			$fields[$fieldname]	= $fieldname;
		}
		if(count($tables) > 0)
		{
			if(strtolower($this->getOption('preserveReferences')) == 'true')
				$this->smartWHERE($tables);
			return	 'SELECT '.implode(', ', $fields)
					.' FROM '.implode(', ', $tables)
					.($this->getOption('where')		? ' WHERE '.$this->getOption('where') : '')
					.($this->getOption('orderby')	? ' ORDER BY '.$this->getOption('orderby') : '')
					.($this->getOption('limit')		? ' LIMIT '.$this->getOption('limit') : '')
					;
		}
		return null;
	}

	/**
	 * This method enables following and regarding the references.
	 *
	 * Automatically adds "WHERE table1.table2_id = table2.id"
	 *
	 * Attention: Only use with InnoDB!
	 *
	 * @access protected
	 * @return void
	 * @param Array $tables
	 */
	protected function smartWHERE(Array $tables)
	{
		$information_schema = new MySQLi();
		$information_schema->connect
			( $GLOBALS['database']['host']
			, $GLOBALS['database']['username']
			, $GLOBALS['database']['password']
			, 'information_schema'
			);

		$conditions = array();
		foreach($tables as $table)
		{
			$result = $information_schema->query
				('
					SELECT
						  `TABLE_NAME`
						, `COLUMN_NAME`
						, `REFERENCED_TABLE_NAME`
						, `REFERENCED_COLUMN_NAME`
					FROM
						`KEY_COLUMN_USAGE`
					WHERE
						(`TABLE_NAME` = "'.$table.'")
						AND `REFERENCED_TABLE_NAME` IS NOT NULL
						AND `REFERENCED_COLUMN_NAME` IS NOT NULL
				');
			if($result)
				while($o = $result->fetch_object())
					if(in_array($o->REFERENCED_TABLE_NAME, $tables) && in_array($o->TABLE_NAME, $tables))
						$conditions[] = $o;
		}
		$where = array();
		foreach($conditions as $condition)
			$where[] = $condition->TABLE_NAME . '.' . $condition->COLUMN_NAME . ' = ' . $condition->REFERENCED_TABLE_NAME . '.' . $condition->REFERENCED_COLUMN_NAME;
		$this->options['where'] .= (strlen($this->options['where']) > 0 ? ' AND ' : '').implode(' AND ', $where);
		$information_schema->close();
	}
}