<?php

	/**
	 * System Having/Where filter
	 *
	 */
	define('FLT_SYSTEM', 1);

	/**
	 * User Having/Where filter
	 *
	 */
	define('FLT_NORMAL', 2);

	/**
	 * User "Search" Having/Where filter
	 *
	 */
	define('FLT_SEARCH', 3);

	/**
	 * User "View Menu" Having/Where filter
	 *
	 */
	define('FLT_VIEW', 4);

/**
* DBList
*
* Desciption
* @package kernel4
*/
class kDBList extends kDBBase {

	/**
	* Description
	*
	* @var array
	* @access public
	*/
	var $OrderFields;

	/**
	* Holds counted total number of records in the query - without pagination (system+user filters)
	*
	* @var int
	* @access public
	*/
	var $RecordsCount;


	/**
	 * Records count with system filters only applied
	 *
	 * @var int
	 * @access private
	 */
	var $NoFilterCount = 0;

	/**
	 * Record count selected to be
	 * showed on current page
	 *
	 * @var int
	 */
	var $SelectedCount=0;

	/**
	 * Array of records selected
	 *
	 * @var Array
	 * @access private
	 */
	var $Records;

	var $CurrentIndex = 0;

	/**
	* List items per-page
	*
	* @var int
	* @access public
	*/
	var $PerPage;

	/**
	* Pages count in list based on PerPage & RecordsCount attributes
	*
	* @var int
	* @access public
	*/
	var $TotalPages;

	/**
	* Description
	*
	* @var int
	* @access public
	*/

	var $Direction;
	/**
	* Holds current page number - used when forming LIMIT clause of SELECT statement
	*
	* @var int
	* @access public
	*/
	var $Page;

	/**
	* Holds offset for LIMIT clause, calculated in {@link kDBList::PerPage()}
	*
	* @var int
	* @access private
	*/
	var $Offset;

	/**
	 * Count SQL was already issued on query
	 *
	 * @var bool
	 * @access private
	 */
	var $hasCounted = false;

	/**
	 * Holds list WHERE filter object
	 *
	 * @var kMultipleFilter
	 * @access private
	 */
	var $WhereFilter = Array(FLT_SYSTEM => null, FLT_NORMAL => null, FLT_SEARCH => null, FLT_VIEW => null);

	/**
	 * Holds list HAVING filter object
	 *
	 * @var kMultipleFilter
	 * @access private
	 */
	var $HavingFilter = Array(FLT_SYSTEM => null, FLT_NORMAL => null, FLT_SEARCH => null, FLT_VIEW => null);

	var $GroupByFields = Array();

	var $Queried = false;
	var $Counted = false;

	/**
	* Creates kDBList
	*
	* @return kDBList
	*/
	function kDBList() {
		parent::kDBBase();
		$this->OrderFields = Array();

		$this->WhereFilter[FLT_SYSTEM]	=& $this->Application->makeClass('kMultipleFilter', FLT_TYPE_AND);
		$this->WhereFilter[FLT_NORMAL]	=& $this->Application->makeClass('kMultipleFilter', FLT_TYPE_OR);

		$this->WhereFilter[FLT_SEARCH]	=& $this->Application->makeClass('kMultipleFilter', FLT_TYPE_OR);
		$this->WhereFilter[FLT_SEARCH]->setType(FLT_TYPE_OR);

		$this->WhereFilter[FLT_VIEW]	=& $this->Application->makeClass('kMultipleFilter', FLT_TYPE_AND);

		$this->HavingFilter[FLT_SYSTEM] =& $this->Application->makeClass('kMultipleFilter', FLT_TYPE_AND);
		$this->HavingFilter[FLT_NORMAL] =& $this->Application->makeClass('kMultipleFilter', FLT_TYPE_OR);

		$this->HavingFilter[FLT_SEARCH] =& $this->Application->makeClass('kMultipleFilter', FLT_TYPE_OR);
		$this->HavingFilter[FLT_SEARCH]->setType(FLT_TYPE_OR);

		$this->HavingFilter[FLT_VIEW] =& $this->Application->makeClass('kMultipleFilter', FLT_TYPE_AND);

		$this->PerPage = -1;
	}

	/**
	 * Adds new or replaces old filter with same name
	 *
	 * @param string $name filter name (for internal use)
	 * @param string $clause where/having clause part (no OR/AND allowed)
	 * @param int $filter_type is filter having filter or where filter
	 * @param int $filter_scope filter subtype: FLT_NORMAL,FLT_SYSTEM,FLT_SEARCH,FLT_VIEW
	 * @access public
	 */
	function addFilter($name, $clause, $filter_type = WHERE_FILTER, $filter_scope = FLT_SYSTEM)
	{
		$filter_name = ($filter_type == WHERE_FILTER) ? 'WhereFilter' : 'HavingFilter';

		$filter =& $this->$filter_name;
		$filter =& $filter[$filter_scope];
		$filter->addFilter($name,$clause);
	}

	/**
	 * Removes specified filter from filters list
	 *
	 * @param string $name filter name (for internal use)
	 * @param int $filter_type is filter having filter or where filter
	 * @param int $filter_scope filter subtype: FLT_NORMAL,FLT_SYSTEM,FLT_SEARCH,FLT_VIEW
	 * @access public
	 */
	function removeFilter($name, $filter_type = WHERE_FILTER, $filter_scope = FLT_SYSTEM)
	{
		$filter_name = ($filter_type == WHERE_FILTER) ? 'WhereFilter' : 'HavingFilter';

		$filter =& $this->$filter_name;
		$filter =& $filter[$filter_scope];
		$filter->removeFilter($name);
	}

	/**
	 * Clear list filters
	 *
	 * @param bool $user	clear user filters
	 * @param bool $system	clear system filters
	 */
	function clearFilters($user=true,$system=true,$search=true,$view=true)
	{
		if($system)
		{
			$this->WhereFilter[FLT_SYSTEM]->clearFilters();
			$this->HavingFilter[FLT_SYSTEM]->clearFilters();
		}
		if($user)
		{
			$this->WhereFilter[FLT_NORMAL]->clearFilters();
			$this->HavingFilter[FLT_NORMAL]->clearFilters();
		}
		if($search)
		{
			$this->WhereFilter[FLT_SEARCH]->clearFilters();
			$this->HavingFilter[FLT_SEARCH]->clearFilters();
		}
		if($view)
		{
			$this->WhereFilter[FLT_VIEW]->clearFilters();
			$this->HavingFilter[FLT_VIEW]->clearFilters();
		}
	}

	/**
	* Counts the total number of records base on the query resulted from {@link kDBList::GetSelectSQL()}
	*
	* The method modifies the query to substitude SELECT part (fields listing) with COUNT(*).
	* Special care should be applied when working with lists based on grouped queries, all aggregate function fields
	* like SUM(), AVERAGE() etc. should be added to CountedSQL by using {@link kDBList::SetCountedSQL()}
	*
	* @access public
	* @param string
	* @return void
	*/
	function CountRecs()
	{
		$all_sql = $this->GetSelectSQL(true,false);
		$sql = $this->getCountSQL($all_sql);

		if( $this->GetGroupClause() )
		{
	    	$this->RecordsCount = count( $this->Conn->GetCol($sql) );
		}
		else
		{
			$this->RecordsCount = (int)$this->Conn->GetOne($sql);
		}

	    $system_sql = $this->GetSelectSQL(true,true);
	    if ($system_sql == $all_sql) { //no need to query the same again
	    	$this->NoFilterCount = $this->RecordsCount;
	    	return;
	    }
		$sql = $this->getCountSQL($system_sql);
	    $this->NoFilterCount = (int)$this->Conn->GetOne($sql);
	    $this->Counted = true;
	}

	function getCountSQL($sql)
	{
		if ( preg_match("/DISTINCT(.*?)FROM(?!_)/is",$sql,$regs ) )
		{
			return preg_replace("/^.*SELECT DISTINCT(.*?)FROM(?!_)/is", "SELECT COUNT(DISTINCT ".$regs[1].") AS count FROM", $sql);
		}
		else
		{
			return preg_replace("/^.*SELECT(.*?)FROM(?!_)/is", "SELECT COUNT(*) AS count FROM ", $sql);
		}
	}

	/**
	* Queries the database with SQL resulted from {@link kDBList::GetSelectSQL()} and stores result in {@link kDBList::SelectRS}
	*
	* All the sorting, pagination, filtration of the list should be set prior to calling Query().
	*
	* @access public
	* @param string
	* @return void
	*/
	function Query($force=false)
	{
		if (!$force && $this->Queried) return true;
		$q = $this->GetSelectSQL();
		
		//$rs = $this->Conn->SelectLimit($q, $this->PerPage, $this->Offset);

		//in case we have not counted records try to select one more item to find out if we have something more than perpage
		$limit = $this->Counted ? $this->PerPage : $this->PerPage+1;

		$sql = $q.' '.$this->Conn->getLimitClause($this->Offset,$limit);

		$this->Records = $this->Conn->Query($sql);
		$this->SelectedCount = count($this->Records);
		if (!$this->Counted) $this->RecordsCount = $this->SelectedCount;
		if (!$this->Counted && $this->SelectedCount > $this->PerPage && $this->PerPage != -1) $this->SelectedCount--;

		if ($this->Records === false) {
			//handle errors here
			return false;
		}
		$this->Queried = true;
		return true;
	}

	/**
	* Builds full select query except for LIMIT clause
	*
	* @access public
	* @return string
	*/
	function GetSelectSQL($for_counting=false,$system_filters_only=false)
	{
		$q = parent::GetSelectSQL($this->SelectClause);
		if(!$for_counting) $q = $this->addCalculatedFields($q);

		$where = $this->GetWhereClause($for_counting,$system_filters_only);
		$having = $this->GetHavingClause($for_counting,$system_filters_only);
		$order = $this->GetOrderClause();
		$group = $this->GetGroupClause();

		if (!empty($where)) $q .= ' WHERE ' . $where;
		if (!empty($group)) $q .= ' GROUP BY ' . $group;
		if (!empty($having)) $q .= ' HAVING ' . $having;
		if ( !$for_counting && !empty($order) ) $q .= ' ORDER BY ' . $order;

		return str_replace('%1$s',$this->TableName,$q);
	}

	function extractCalculatedFields($clause)
	{
		if ( is_array($this->CalculatedFields) ) {
			foreach($this->CalculatedFields as $field_name => $field_expression)
			{
				$clause = preg_replace('/`'.$field_name.'`/', $field_expression, $clause);
			}
		}
		return $clause;
	}

	/**
	* Returns WHERE clause of the query
	*
	* @access public
	* @param bool $for_counting merge where filters with having filters + replace field names for having fields with their values
	* @return string
	*/
	function GetWhereClause($for_counting=false,$system_filters_only=false)
	{
		$where =& $this->Application->makeClass('kMultipleFilter');

		$where->addFilter('system_where', $this->WhereFilter[FLT_SYSTEM] );

		if (!$system_filters_only) {
			$where->addFilter('view_where', $this->WhereFilter[FLT_VIEW] );
			$search_w = $this->WhereFilter[FLT_SEARCH]->getSQL();
			if( $search_w || $for_counting ) // move search_having to search_where in case search_where isset or we are counting
			{
				$search_h = $this->extractCalculatedFields( $this->HavingFilter[FLT_SEARCH]->getSQL() );
				$search_w = ($search_w && $search_h) ? $search_w.' OR '.$search_h : $search_w.$search_h;
				$where->addFilter('search_where', $search_w );
			}
		}

		if( $for_counting ) // add system_having and view_having to where
		{
			$where->addFilter('system_having', $this->extractCalculatedFields( $this->HavingFilter[FLT_SYSTEM]->getSQL() ) );
			if (!$system_filters_only) $where->addFilter('view_having', $this->extractCalculatedFields( $this->HavingFilter[FLT_VIEW]->getSQL() ) );
		}

		return $where->getSQL();
	}

	/**
	 * Depricated method
	 *
	 * @param string $clause
	 * @todo REMOVE
	 */
	function SetWhereClause($clause)
	{
		if( $this->Application->isDebugMode() )
		{
			global $debugger;
			$debugger->appendTrace();
		}
		trigger_error('Depricated method <b>kDBList->SetWhereClause</b>. Use <b>kDBList->addFilter</b> instead.', E_USER_ERROR);
	}

	/**
	* Returns HAVING clause of the query
	*
	* @param bool $for_counting don't return having filter in case if this is counting sql
	* @return string
	* @access public
	*/
	function GetHavingClause($for_counting=false, $system_filters_only=false)
	{
		if( $for_counting ) return '';

		$having =& $this->Application->makeClass('kMultipleFilter');

		$having->addFilter('system_having', $this->HavingFilter[FLT_SYSTEM] );
		if (!$system_filters_only) {
			$having->addFilter('view_having', $this->HavingFilter[FLT_VIEW] );
			$search_w = $this->WhereFilter[FLT_SEARCH]->getSQL();
			if (!$search_w) {
				$having->addFilter('search_having', $this->HavingFilter[FLT_SEARCH] );
			}
		}

		return $having->getSQL();
	}

	/**
	* Returns GROUP BY clause of the query
	*
	* @access public
	* @return string
	*/
	function GetGroupClause()
	{
		return $this->GroupByFields ? implode(',', $this->GroupByFields) : '';
	}

	function AddGroupByField($field)
	{
		$this->GroupByFields[$field] = $field;
	}

	function RemoveGroupByField($field)
	{
		unset($this->GroupByFields[$field]);
	}

	/**
	* Adds order field to ORDER BY clause
	*
	* @access public
	* @param string $field Field name
	* @param string $direction Direction of ordering (asc|desc)
	* @return void
	*/
	function AddOrderField($field, $direction)
	{
		// original multilanguage field - convert to current lang field
		if (getArrayValue($this->Fields, $field, 'formatter') == 'kMultiLanguage' && !getArrayValue($this->Fields, $field, 'master_field')) {
			$lang = $this->Application->GetVar('m_lang');
			$field = 'l'.$lang.'_'.$field;
		}
		
		if( !isset($this->Fields[$field]) )
		{
			trigger_error('<span class="debug_error">Incorrect sorting</span> defined (field = <b>'.$field.'</b>; direction = <b>'.$direction.'</b>) in config for prefix <b>'.$this->Prefix.'</b>', E_USER_WARNING);
		}
		
		$this->OrderFields[] = Array($field, $direction);
	}

	/**
	* Removes all order fields
	*
	* @access public
	* @return void
	*/
	function ClearOrderFields()
	{
		$this->OrderFields = Array();
	}

	/**
	* Returns ORDER BY Clause of the query
	*
	*	The method builds order by clause by iterating {@link kDBList::OrderFields} array and concatenating it.
	*
	* @access public
	* @return string
	*/
	function GetOrderClause()
	{
		$ret = '';
		foreach ($this->OrderFields as $field) {

			$name = $field[0];
			$ret .= isset($this->Fields[$name]) && !isset($this->VirtualFields[$name]) ? '`'.$this->TableName.'`.' : '';
			if ($field[0] == 'RAND()') {
				$ret .= $field[0].' '.$field[1].',';
			}
			else {
				$ret .= '`'.$field[0] . '` ' . $field[1] . ',';
			}
		}
		$ret = rtrim($ret, ',');
		return $ret;
	}

	function GetOrderField($pos=NULL)
	{
		if(!(isset($this->OrderFields[$pos]) && $this->OrderFields[$pos]) )
		{
			$pos = 0;
		}
		return isset($this->OrderFields[$pos][0]) ? $this->OrderFields[$pos][0] : '';
	}

	function GetOrderDirection($pos=NULL)
	{
		if( !getArrayValue($this->OrderFields, $pos) ) $pos = 0;
		return getArrayValue($this->OrderFields, $pos, 1);
	}

	/**
	* Return unformatted field value
	*
	* @param string
	* @return mixed
	* @access public
	*/
	function GetDBField($name)
	{
		$row =& $this->getCurrentRecord();
		return $row[$name];
	}

	function HasField($name)
	{
		$row =& $this->getCurrentRecord();
		return isset($row[$name]);
	}

	function GetFieldValues()
	{
		return $this->getCurrentRecord();
	}

	function &getCurrentRecord()
	{
		return $this->Records[$this->CurrentIndex];
	}

	/**
	* Description
	*
	* @access public
	* @param string
	* @return void
	*/
	function GoFirst()
	{
		$this->CurrentIndex = 0;
	}

	/**
	* Description
	*
	* @access public
	* @return void
	*/
	function GoNext()
	{
		$this->CurrentIndex++;
	}

	/**
	* Description
	*
	* @access public
	* @return void
	*/
	function GoPrev()
	{
		if ($this->CurrentIndex>0)
			$this->CurrentIndex--;
	}

	/**
	* Description
	*
	* @access public
	* @return bool
	*/
	function EOL()
	{
		return ($this->CurrentIndex >= $this->SelectedCount);
	}

	/**
	* Description
	*
	* @access public
	* @param string
	* @return void
	*/
	function GetTotalPages()
	{
		if (!$this->Counted) $this->CountRecs();
		if ($this->PerPage == -1) return 1;
		$this->TotalPages = (($this->RecordsCount - ($this->RecordsCount % $this->PerPage)) / $this->PerPage) // integer part of division
								+ (($this->RecordsCount % $this->PerPage) != 0);  // adds 1 if there is a reminder
		return $this->TotalPages;
	}

	/**
	* Sets number of records to query per page
	*
	* @access public
	* @param int $per_page Number of records to display per page
	* @return void
	*/
	function SetPerPage($per_page)
	{
		$this->PerPage = $per_page;
	}

	function GetPerPage()
	{
		return $this->PerPage == -1 ? $this->RecordsCount : $this->PerPage;
	}

	/**
	* Description
	*
	* @access public
	* @param int $page
	* @return void
	*/
	function SetPage($page)
	{
		if ($this->PerPage == -1) {
			$this->Page = 1;
			return;
		}
		if ($page < 1) $page = 1;
		$this->Offset = ($page-1)*$this->PerPage;
		if ($this->Counted && $this->Offset > $this->RecordsCount) {
			$this->SetPage(1);
		}
		else {
			$this->Page = $page;
		}
		//$this->GoFirst();
	}

	/**
	* Sets current item field value
	* (doesn't apply formatting)
	*
	* @access public
	* @param string $name Name of the field
	* @param mixed $value Value to set the field to
	* @return void
	*/
	function SetDBField($name,$value)
	{
		$this->Records[$this->CurrentIndex][$name] = $value;
	}

	/**
	 * Apply where clause, that links this object to it's parent item
	 *
	 * @param string $special
	 * @access public
	 */
	function linkToParent($special)
	{
		$parent_prefix = $this->Application->getUnitOption($this->Prefix, 'ParentPrefix');
		if($parent_prefix)
		{
			$parent_table_key = $this->Application->getUnitOption($this->Prefix, 'ParentTableKey');
			$foreign_key_field = $this->Application->getUnitOption($this->Prefix, 'ForeignKey');

			$parent_object =& $this->Application->recallObject($parent_prefix.'.'.$special);
			$parent_id = $parent_object->GetDBField($parent_table_key);

			$this->addFilter('parent_filter', '`'.$this->TableName.'`.`'.$foreign_key_field.'` = '.$parent_id);	// only for list in this case
		}
	}
}

?>