dblist.php
No OneTemporary
Actions

Subscribers

None

File Metadata

Created: Tue, Mar 11, 1:09 AM

dblist.php
View Options

	<?php
	/**
	* @version $Id: dblist.php 16760 2023-11-14 08:33:00Z alex $
	* @package In-Portal
	* @copyright Copyright (C) 1997 - 2009 Intechnic. All rights reserved.
	* @license GNU/GPL
	* In-Portal is Open Source software.
	* This means that this software may have been modified pursuant
	* the GNU General Public License, and as distributed it includes
	* or is derivative of works licensed under the GNU General Public License
	* or other free or open source software licenses.
	* See http://www.in-portal.org/license for copyright notices and details.
	*/

	defined('FULL_PATH') or die('restricted access!');

	/**
	* DBList
	*
	*/
	class kDBList extends kDBBase implements Iterator, Countable {

	// kDBList filter types (then, types are divided into classes)

	/**
	* Having filter [step1]
	*
	*/
	const HAVING_FILTER = 1;

	/**
	* Where filter [step1]
	*
	*/
	const WHERE_FILTER = 2;

	/**
	* Aggregated filter [step1]
	*
	*/
	const AGGREGATE_FILTER = 3;

	/**
	* System filter [step2, AND]
	*
	*/
	const FLT_SYSTEM = 1;

	/**
	* User filter [step2, OR]
	* @deprecated
	*
	*/
	const FLT_NORMAL = 2;

	/**
	* User "Search" filter [step2, OR]
	*
	*/
	const FLT_SEARCH = 3;

	/**
	* User "View Menu" filter [step2, AND]
	*
	*/
	const FLT_VIEW = 4;

	/**
	* User "Custom" (above grid columns) filter [step2, AND]
	*
	*/
	const FLT_CUSTOM = 5;

	/**
	* kMultipleFilter AND filter [step3]
	*
	*/
	const FLT_TYPE_AND = 'AND';

	/**
	* kMultipleFilter OR filter [step3]
	*
	*/
	const FLT_TYPE_OR = 'OR';

	/**
	* Totals for fields specified in config
	*
	* @var Array
	* @access protected
	*/
	protected $Totals = Array ();

	/**
	* Remembers if totals were calculated
	*
	* @var bool
	* @access protected
	*/
	protected $TotalsCalculated = false;

	/**
	* List of "ORDER BY" fields
	*
	* @var Array
	* @access protected
	*/
	protected $OrderFields = Array ();

	/**
	* Counted total number of records in the query - without pagination (system+user filters)
	*
	* @var int
	* @access protected
	*/
	protected $RecordsCount = 0;

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

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

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

	/**
	* Current record index
	*
	* @var int
	* @access protected
	*/
	protected $CurrentIndex = 0;

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

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

	/**
	* Current page number - used when forming LIMIT clause of SELECT statement
	*
	* @var int
	* @access protected
	*/
	protected $Page;

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

	/**
	* WHERE filter objects
	*
	* @var kMultipleFilter[]
	* @access protected
	*/
	protected $WhereFilter = Array ();

	/**
	* HAVING filter objects
	*
	* @var kMultipleFilter[]
	* @access protected
	*/
	protected $HavingFilter = Array ();

	/**
	* AGGREGATED filter objects
	*
	* @var kMultipleFilter[]
	* @access protected
	*/
	protected $AggregateFilter = Array ();

	/**
	* List of "GROUP BY" fields
	*
	* @var Array
	* @access protected
	*/
	protected $GroupByFields = Array ();

	/**
	* Remembers if list was queried
	*
	* @var bool
	* @access protected
	*/
	protected $Queried = false;

	/**
	* Remembers if list was counted
	*
	* @var bool
	* @access protected
	*/
	protected $Counted = false;

	/**
	* Name of the grid, used to display the list
	*
	* @var string
	*/
	var $gridName = '';

	/**
	* Identifies this list as main on the page, that allows to react on "page", "per_page" and "sort_by" parameters from url
	*
	* @var bool
	* @access protected
	*/
	protected $mainList = false;

	/**
	* Holds field errors
	*
	* @var Array
	* @access protected
	*/
	protected $FieldErrors = Array ();

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

	foreach ( $this->getFilterStructure() as $filter_params ) {
	$property_name = $filter_params['type'];
	$filter_group =& $this->$property_name;

	$filter_group[$filter_params['class']] = $this->Application->makeClass(
	'kMultipleFilter',
	array($filter_params['join_using'])
	);
	}

	$this->PerPage = -1;
	}

	function setGridName($grid_name)
	{
	$this->gridName = $grid_name;
	}

	/**
	* Returns information about all possible filter types
	*
	* @return Array
	* @access protected
	*/
	protected function getFilterStructure()
	{
	$filters = Array (
	Array ('type' => 'WhereFilter', 'class' => self::FLT_SYSTEM, 'join_using' => self::FLT_TYPE_AND),
	Array ('type' => 'WhereFilter', 'class' => self::FLT_NORMAL, 'join_using' => self::FLT_TYPE_OR),
	Array ('type' => 'WhereFilter', 'class' => self::FLT_SEARCH, 'join_using' => self::FLT_TYPE_OR),
	Array ('type' => 'WhereFilter', 'class' => self::FLT_VIEW, 'join_using' => self::FLT_TYPE_AND),
	Array ('type' => 'WhereFilter', 'class' => self::FLT_CUSTOM, 'join_using' => self::FLT_TYPE_AND),

	Array ('type' => 'HavingFilter', 'class' => self::FLT_SYSTEM, 'join_using' => self::FLT_TYPE_AND),
	Array ('type' => 'HavingFilter', 'class' => self::FLT_NORMAL, 'join_using' => self::FLT_TYPE_OR),
	Array ('type' => 'HavingFilter', 'class' => self::FLT_SEARCH, 'join_using' => self::FLT_TYPE_OR),
	Array ('type' => 'HavingFilter', 'class' => self::FLT_VIEW, 'join_using' => self::FLT_TYPE_AND),
	Array ('type' => 'HavingFilter', 'class' => self::FLT_CUSTOM, 'join_using' => self::FLT_TYPE_AND),

	Array ('type' => 'AggregateFilter', 'class' => self::FLT_SYSTEM, 'join_using' => self::FLT_TYPE_AND),
	Array ('type' => 'AggregateFilter', 'class' => self::FLT_NORMAL, 'join_using' => self::FLT_TYPE_OR),
	Array ('type' => 'AggregateFilter', 'class' => self::FLT_VIEW, 'join_using' => self::FLT_TYPE_AND),
	);

	return $filters;
	}

	/**
	* 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,FLT_CUSTOM
	* @access public
	*/
	public function addFilter($name, $clause, $filter_type = self::WHERE_FILTER, $filter_scope = self::FLT_SYSTEM)
	{
	$this->getFilterCollection($filter_type, $filter_scope)->addFilter($name, $clause);
	}

	/**
	* Reads filter content
	*
	* @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,FLT_CUSTOM
	* @return string
	* @access public
	*/
	public function getFilter($name, $filter_type = self::WHERE_FILTER, $filter_scope = self::FLT_SYSTEM)
	{
	return $this->getFilterCollection($filter_type, $filter_scope)->getFilter($name);
	}

	/**
	* 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,FLT_CUSTOM
	* @access public
	*/
	public function removeFilter($name, $filter_type = self::WHERE_FILTER, $filter_scope = self::FLT_SYSTEM)
	{
	$this->getFilterCollection($filter_type, $filter_scope)->removeFilter($name);
	}

	/**
	* Returns filter collection.
	*
	* @param integer $filter_type Is filter having filter or where filter.
	* @param integer $filter_scope Filter subtype: FLT_NORMAL,FLT_SYSTEM,FLT_SEARCH,FLT_VIEW,FLT_CUSTOM.
	*
	* @return kMultipleFilter
	*/
	protected function getFilterCollection($filter_type = self::WHERE_FILTER, $filter_scope = self::FLT_SYSTEM)
	{
	$filter_source = array(
	self::WHERE_FILTER => 'WhereFilter',
	self::HAVING_FILTER => 'HavingFilter',
	self::AGGREGATE_FILTER => 'AggregateFilter'
	);

	/** @var kMultipleFilter[] $filters */
	$property_name = $filter_source[$filter_type];
	$filters =& $this->$property_name;

	return $filters[$filter_scope];
	}

	/**
	* Clear all filters
	*
	* @access public
	*/
	public function clearFilters()
	{
	foreach ( $this->getFilterStructure() as $filter_params ) {
	$property_name = $filter_params['type'];
	$filter_group =& $this->$property_name;

	$filter_group[$filter_params['class']]->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 protected
	*/
	protected function CountRecs()
	{
	$all_sql = $this->GetSelectSQL(true,false);
	$sql = $this->getCountSQL($all_sql);

	$this->Counted = true;

	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);
	if( $this->GetGroupClause() )
	{
	$this->NoFilterCount = count( $this->Conn->GetCol($sql) );
	}
	else
	{
	$this->NoFilterCount = (int)$this->Conn->GetOne($sql);
	}
	}

	/**
	* Returns record count in list with/without user filters applied
	*
	* @param bool $with_filters
	* @return int
	* @access public
	*/
	public function GetRecordsCount($with_filters = true)
	{
	if (!$this->Counted) {
	$this->CountRecs();
	}

	return $with_filters ? $this->RecordsCount : $this->NoFilterCount;
	}

	/**
	* Returns record count, that were actually selected
	*
	* @return int
	* @access public
	*/
	public function GetSelectedCount()
	{
	$this->Query();

	return $this->SelectedCount;
	}

	/**
	* Transforms given query into count query (DISTINCT is also processed)
	*
	* @param string $sql
	* @return string
	* @access public
	*/
	public function getCountSQL($sql)
	{
	if ( preg_match("/^\sSELECT\s+DISTINCT(.?\s)FROM(?!_)/is",$sql,$regs ) )
	{
	return preg_replace("/^\sSELECT\s+DISTINCT(.?\s)FROM(?!_)/is", "SELECT COUNT(DISTINCT ".$regs[1].") AS count FROM", $sql);
	}
	else
	{
	return preg_replace("/^\sSELECT(.?\s)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().
	*
	* @param bool $force force re-query, when already queried
	* @return bool
	* @access public
	*/
	public 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);

	if (!$this->Records && ($this->Page > 1)) {
	if ( $this->Application->isAdmin ) {
	// no records & page > 1, try to reset to 1st page (works only when list in not counted before)
	$this->Application->StoreVar($this->getPrefixSpecial() . '_Page', 1, true);
	$this->SetPage(1);
	$this->Query($force);
	}
	else if ( $this->Application->HttpQuery->refererIsOurSite() ) {
	// no records & page > 1, try to reset to last page
	$this->SetPage($this->GetTotalPages());
	$this->Query($force);
	}
	else {
	// no records & page > 1, show 404 page
	trigger_error('Unknown page <strong>' . $this->Page . '</strong> in <strong>' . $this->getPrefixSpecial() . '</strong> list, leading to "404 Not Found"', E_USER_NOTICE);
	$this->Application->UrlManager->show404();
	}
	}

	$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;

	$this->Application->HandleEvent(new kEvent($this->getPrefixSpecial() . ':OnAfterListQuery'));

	return true;
	}

	/**
	* Adds one more record to list virtually and updates all counters
	*
	* @param Array $record
	* @access public
	*/
	public function addRecord($record)
	{
	$this->Records[] = $record;
	$this->SelectedCount++;
	$this->RecordsCount++;
	}

	/**
	* Calculates totals based on config
	*
	* @access protected
	*/
	protected function CalculateTotals()
	{
	$fields = Array();
	$this->Totals = Array();

	if ($this->gridName) {
	$grids = $this->Application->getUnitOption($this->Prefix, 'Grids');
	$grid_fields = $grids[$this->gridName]['Fields'];
	}
	else {
	$grid_fields = $this->Fields;
	}

	foreach ($grid_fields as $field_name => $field_options) {
	if ($this->gridName && array_key_exists('totals', $field_options) && $field_options['totals']) {
	$totals = $field_options['totals'];
	}
	elseif (array_key_exists('totals', $this->Fields[$field_name]) && $this->Fields[$field_name]['totals']) {
	$totals = $this->Fields[$field_name]['totals'];
	}
	else {
	continue;
	}

	$calculated_field = array_key_exists($field_name, $this->CalculatedFields) && array_key_exists($field_name, $this->VirtualFields);
	$db_field = !array_key_exists($field_name, $this->VirtualFields);

	if ($calculated_field \|\| $db_field) {
	$field_expression = $calculated_field ? $this->CalculatedFields[$field_name] : '`'.$this->TableName.'`.`'.$field_name.'`';
	$fields[$field_name] = $totals.'('.$field_expression.') AS '.$field_name.'_'.$totals;
	}
	}

	if (!$fields) {
	return ;
	}

	$fields = str_replace('%1$s', $this->TableName, implode(', ', $fields));
	$sql = $this->GetSelectSQL(true, false, $fields);

	if ( preg_match("/DISTINCT(.*?\s)FROM(?!_)/is",$sql,$regs ) )
	{
	$sql = preg_replace("/^\sSELECT DISTINCT(.?\s)FROM(?!_)/is", 'SELECT '.$fields.' FROM', $sql);
	}
	else
	{
	$sql = preg_replace("/^\sSELECT(.?\s)FROM(?!_)/is", 'SELECT '.$fields.' FROM ', $sql);
	}

	$totals = $this->Conn->Query($sql);

	foreach($totals as $totals_row)
	{
	foreach($totals_row as $total_field => $field_value)
	{
	if(!isset($this->Totals[$total_field])) $this->Totals[$total_field] = 0;
	$this->Totals[$total_field] += $field_value;
	}
	}
	$this->TotalsCalculated = true;
	}

	/**
	* Returns previously calculated total (not formatted)
	*
	* @param string $field
	* @param string $total_function
	* @return float
	* @access public
	*/
	public function getTotal($field, $total_function)
	{
	if (!$this->TotalsCalculated) {
	$this->CalculateTotals();
	}

	return $this->Totals[$field . '_' . $total_function];
	}

	function setTotal($field, $total_function, $value)
	{
	$this->Totals[$field . '_' . $total_function] = $value;
	}

	function getTotalFunction($field)
	{
	if ($this->gridName) {
	$grids = $this->Application->getUnitOption($this->Prefix, 'Grids');
	$field_options = $grids[$this->gridName]['Fields'][$field];
	}
	else {
	$field_options = $this->Fields[$field];
	}

	if ($this->gridName && array_key_exists('totals', $field_options) && $field_options['totals']) {
	return $field_options['totals'];
	}
	elseif (array_key_exists('totals', $this->Fields[$field]) && $this->Fields[$field]['totals']) {
	return $this->Fields[$field]['totals'];
	}

	return false;
	}

	/**
	* Returns previously calculated total (formatted)
	*
	* @param string $field Field.
	* @param string $total_function Total function.
	* @param string $format Format.
	*
	* @return float
	*/
	public function GetFormattedTotal($field, $total_function, $format = null)
	{
	$res = $this->getTotal($field, $total_function);
	$formatter_class = $this->GetFieldOption($field, 'formatter');

	if ( $formatter_class ) {
	/** @var kFormatter $formatter */
	$formatter = $this->Application->recallObject($formatter_class);

	$res = $formatter->Format($res, $field, $this, $format);
	}

	return $res;
	}

	/**
	* Builds full select query except for LIMIT clause
	*
	* @param bool $for_counting
	* @param bool $system_filters_only
	* @param string $keep_clause
	* @return string
	* @access public
	*/
	public function GetSelectSQL($for_counting = false, $system_filters_only = false, $keep_clause = '')
	{
	$q = parent::GetSelectSQL($this->SelectClause);
	$q = !$for_counting ? $this->addCalculatedFields($q, 0) : str_replace('%2$s', '', $q);

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

	if ( $for_counting ) {
	$usage_string = $where . '\|' . $having . '\|' . $order . '\|' . $group . '\|' . $keep_clause;
	$optimizer = new LeftJoinOptimizer($q, $this->replaceModePrefix( str_replace('%1$s', $this->TableName, $usage_string) ));
	$q = $optimizer->simplify();
	}

	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 $this->replaceModePrefix( str_replace('%1$s', $this->TableName, $q) );
	}

	/**
	* Replaces all calculated field occurrences with their associated expressions
	*
	* @param string $clause where clause to extract calculated fields from
	* @param int $aggregated 0 - having + aggregated, 1 - having only, 2 - aggregated only
	* @param bool $replace_table
	* @return string
	* @access public
	*/
	public function extractCalculatedFields($clause, $aggregated = 1, $replace_table = false)
	{
	if ( !$clause ) {
	return '';
	}

	$fields = $this->getCalculatedFields($aggregated);

	if ( is_array($fields) && count($fields) > 0 ) {
	$fields = str_replace('%2$s', $this->Application->GetVar('m_lang'), $fields);

	foreach ($fields as $field_name => $field_expression) {
	$clause = preg_replace('/(\\(+)[(,` ]*' . $field_name . '[` ]{1}/', '\1 (' . $field_expression . ') ', $clause);
	$clause = preg_replace('/[,` ]{1}' . $field_name . '[` ]{1}/', ' (' . $field_expression . ') ', $clause);
	}
	}

	return $replace_table ? str_replace('%1$s', $this->TableName, $clause) : $clause;
	}

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

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

	if (!$system_filters_only) {
	$where->addFilter('view_where', $this->WhereFilter[self::FLT_VIEW] );

	// Handle general grid search filter. Append transformed having clause into where clause.
	$search_w = $this->WhereFilter[self::FLT_SEARCH]->getSQL();
	$search_h = $this->extractCalculatedFields($this->HavingFilter[self::FLT_SEARCH]->getSQL());
	$search_w = ($search_w && $search_h) ? $search_w . ' OR ' . $search_h : $search_w . $search_h;
	$where->addFilter('search_where', $search_w);

	// Handle custom per-column grid filters. Append transformed having clause into where clause.
	$search_w = $this->WhereFilter[self::FLT_CUSTOM]->getSQL();
	$search_h = $this->extractCalculatedFields($this->HavingFilter[self::FLT_CUSTOM]->getSQL());
	$search_w = ($search_w && $search_h) ? $search_w . ' AND ' . $search_h : $search_w . $search_h;
	$where->addFilter('custom_where', $search_w);
	}

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

	return $where->getSQL();
	}

	/**
	* Returns HAVING clause of the query
	*
	* @param bool $for_counting don't return having filter in case if this is counting sql
	* @param bool $system_filters_only return only system having filters
	* @param int $aggregated 0 - aggregated and having, 1 - having only, 2 - aggregated only
	* @return string
	* @access private
	*/
	private function GetHavingClause($for_counting=false, $system_filters_only=false, $aggregated = 0)
	{
	if ($for_counting) {
	/** @var kMultipleFilter $aggregate_filter */
	$aggregate_filter = $this->Application->makeClass('kMultipleFilter');

	$aggregate_filter->addFilter('aggregate_system', $this->AggregateFilter[kDBList::FLT_SYSTEM]);
	if (!$system_filters_only) {
	$aggregate_filter->addFilter('aggregate_view', $this->AggregateFilter[kDBList::FLT_VIEW]);
	}
	return $this->extractCalculatedFields($aggregate_filter->getSQL(), 2);
	}

	/** @var kMultipleFilter $having */
	$having = $this->Application->makeClass('kMultipleFilter');

	$having->addFilter('system_having', $this->HavingFilter[kDBList::FLT_SYSTEM] );
	if ($aggregated == 0) {
	if (!$system_filters_only) {
	$having->addFilter('view_aggregated', $this->AggregateFilter[kDBList::FLT_VIEW] );
	}
	$having->addFilter('system_aggregated', $this->AggregateFilter[kDBList::FLT_SYSTEM]);
	}

	if (!$system_filters_only) {
	// Don't add search/custom having filters here, because they're added to the where clause.
	$having->addFilter('view_having', $this->HavingFilter[kDBList::FLT_VIEW] );
	}

	return $having->getSQL();
	}

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

	/**
	* Adds new group by field
	*
	* @param string $field
	* @access public
	*/
	public function AddGroupByField($field)
	{
	$this->GroupByFields[$field] = $field;
	}

	/**
	* Removes group by field added before
	*
	* @param string $field
	* @access public
	*/
	public function RemoveGroupByField($field)
	{
	unset($this->GroupByFields[$field]);
	}

	/**
	* Adds order field to ORDER BY clause
	*
	* @param string $field Field name
	* @param string $direction Direction of ordering (asc\|desc)
	* @param bool $is_expression this is expression, that should not be escapted by "`" symbols
	* @return int
	* @access public
	*/
	public function AddOrderField($field, $direction = 'asc', $is_expression = false)
	{
	// original multilanguage field - convert to current lang field
	$formatter = isset($this->Fields[$field]['formatter']) ? $this->Fields[$field]['formatter'] : false;

	if ($formatter == 'kMultiLanguage' && !isset($this->Fields[$field]['master_field'])) {
	// for now kMultiLanguage formatter is only supported for real (non-virtual) fields
	$is_expression = true;
	$field = $this->getMLSortField($field);
	}

	if (!isset($this->Fields[$field]) && $field != 'RAND()' && !$is_expression) {
	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_NOTICE);
	}

	$this->OrderFields[] = Array($field, $direction, $is_expression);

	return count($this->OrderFields) - 1;
	}

	/**
	* Sets new order fields, replacing existing ones
	*
	* @param Array $order_fields
	* @return void
	* @access public
	*/
	public function setOrderFields($order_fields)
	{
	$this->OrderFields = $order_fields;
	}

	/**
	* Changes sorting direction for a given sorting field index
	*
	* @param int $field_index
	* @param string $direction
	* @return void
	* @access public
	*/
	public function changeOrderDirection($field_index, $direction)
	{
	if ( !isset($this->OrderFields[$field_index]) ) {
	return;
	}

	$this->OrderFields[$field_index][1] = $direction;
	}

	/**
	* Returns expression, used to sort given multilingual field
	*
	* @param string $field
	* @return string
	*/
	function getMLSortField($field)
	{
	$table_name = '`' . $this->TableName . '`';
	$lang = $this->Application->GetVar('m_lang');
	$primary_lang = $this->Application->GetDefaultLanguageId();

	$ret = 'IF(COALESCE(%1$s.l' . $lang . '_' . $field . ', ""), %1$s.l' . $lang . '_' . $field . ', %1$s.l' . $primary_lang . '_' . $field . ')';

	return sprintf($ret, $table_name);
	}

	/**
	* Removes all order fields
	*
	* @access public
	*/
	public 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.
	*
	* @return string
	* @access private
	*/
	private 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()' \|\| $field[2]) {
	$ret .= $field[0].' '.$field[1].',';
	}
	else {
	$ret .= (strpos($field[0], '.') === false ? '`'.$field[0] . '`' : $field[0]) . ' ' . $field[1] . ',';
	}
	}
	$ret = rtrim($ret, ',');
	return $ret;
	}

	/**
	* Returns order field name in given position
	*
	* @param int $pos
	* @param bool $no_default
	* @return string
	* @access public
	*/
	public function GetOrderField($pos = NULL, $no_default = false)
	{
	if ( !(isset($this->OrderFields[$pos]) && $this->OrderFields[$pos]) && !$no_default ) {
	$pos = 0;
	}

	if ( isset($this->OrderFields[$pos][0]) ) {
	$field = $this->OrderFields[$pos][0];
	$lang = $this->Application->GetVar('m_lang');

	if ( preg_match('/^IF$COALESCE\(.?\.(l' . $lang . '_.?), ""$,/', $field, $regs) ) {
	// undo result of kDBList::getMLSortField method
	return $regs[1];
	}

	return $field;
	}

	return '';
	}

	/**
	* Returns list order fields
	*
	* @return Array
	* @access public
	*/
	public function getOrderFields()
	{
	return $this->OrderFields;
	}

	/**
	* Returns order field direction in given position
	*
	* @param int $pos
	* @param bool $no_default
	* @return string
	* @access public
	*/
	public function GetOrderDirection($pos = NULL, $no_default = false)
	{
	if ( !(isset($this->OrderFields[$pos]) && $this->OrderFields[$pos]) && !$no_default ) {
	$pos = 0;
	}

	return isset($this->OrderFields[$pos][1]) ? $this->OrderFields[$pos][1] : '';
	}

	/**
	* Returns ID of currently processed record
	*
	* @return int
	* @access public
	*/
	public function GetID()
	{
	return $this->Queried ? $this->GetDBField($this->IDField) : null;
	}

	/**
	* Allows kDBTagProcessor.SectionTitle to detect if it's editing or new item creation
	*
	* @return bool
	* @access public
	*/
	public function IsNewItem()
	{
	// no such thing as NewItem for lists :)
	return false;
	}

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

	if (defined('DEBUG_MODE') && DEBUG_MODE && $this->Queried && !array_key_exists($name, $row)) {
	if ( $this->Application->isDebugMode() ) {
	$this->Application->Debugger->appendTrace();
	}

	trigger_error('Field "<strong>' . $name . '</strong>" doesn\'t exist in prefix <strong>' . $this->getPrefixSpecial() . '</strong>', E_USER_WARNING);
	return 'NO SUCH FIELD';
	}

	// return "null" for missing fields, because formatter require such behaviour !
	return array_key_exists($name, $row) ? $row[$name] : null;
	}

	/**
	* Checks if requested field is present after database query
	*
	* @param string $name
	* @return bool
	* @access public
	*/
	public function HasField($name)
	{
	$row =& $this->getCurrentRecord();
	return isset($row[$name]);
	}

	/**
	* Returns current record fields
	*
	* @return Array
	* @access public
	*/
	public function GetFieldValues()
	{
	$record =& $this->getCurrentRecord();

	return $record;
	}

	/**
	* Returns current record from list
	*
	* @param int $offset Offset relative to current record index
	* @return Array
	* @access public
	*/
	public function &getCurrentRecord($offset = 0)
	{
	$record_index = $this->CurrentIndex + $offset;

	if ($record_index >=0 && $record_index < $this->SelectedCount) {
	return $this->Records[$record_index];
	}

	$false = false;
	return $false;
	}

	/**
	* Goes to record with given index
	*
	* @param int $index
	* @access public
	*/
	public function GoIndex($index)
	{
	$this->CurrentIndex = $index;
	}

	/**
	* Goes to first record
	*
	* @access public
	*/
	public function GoFirst()
	{
	$this->CurrentIndex = 0;
	}

	/**
	* Goes to next record
	*
	* @access public
	*/
	public function GoNext()
	{
	$this->CurrentIndex++;
	}

	/**
	* Goes to previous record
	*
	* @access public
	*/
	public function GoPrev()
	{
	if ($this->CurrentIndex>0) {
	$this->CurrentIndex--;
	}
	}

	/**
	* Checks if there is no end of list
	*
	* @return bool
	* @access public
	*/
	public function EOL()
	{
	return ($this->CurrentIndex >= $this->SelectedCount);
	}

	/**
	* Returns total page count based on list per-page
	*
	* @return int
	* @access public
	*/
	public function GetTotalPages()
	{
	if ( !$this->Counted ) {
	$this->CountRecs();
	}

	if ( $this->PerPage == -1 ) {
	return 1;
	}

	$integer_part = ($this->RecordsCount - ($this->RecordsCount % $this->PerPage)) / $this->PerPage;
	$reminder = ($this->RecordsCount % $this->PerPage) != 0; // adds 1 if there is a reminder

	$this->TotalPages = $integer_part + $reminder;

	return $this->TotalPages;
	}

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

	/**
	* Returns records per page count
	*
	* @param bool $in_fact
	* @return int
	* @access public
	*/
	public function GetPerPage($in_fact = false)
	{
	if ($in_fact) {
	return $this->PerPage;
	}

	return $this->PerPage == -1 ? $this->RecordsCount : $this->PerPage;
	}

	/**
	* Sets current page in list
	*
	* @param int $page
	* @access public
	*/
	public 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();
	}

	/**
	* Returns current list page
	*
	* @return int
	* @access public
	*/
	public function GetPage()
	{
	return $this->Page;
	}

	/**
	* Sets list query offset
	*
	* @param int $offset
	* @access public
	*/
	public function SetOffset($offset)
	{
	$this->Offset = $offset;
	}

	/**
	* Gets list query offset
	*
	* @return int
	* @access public
	*/
	public function GetOffset()
	{
	return $this->Offset;
	}

	/**
	* Sets current item field value (doesn't apply formatting)
	*
	* @param string $name Name of the field
	* @param mixed $value Value to set the field to
	* @access public
	*/
	public 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
	*/
	public function linkToParent($special)
	{
	$parent_prefix = $this->Application->getUnitOption($this->Prefix, 'ParentPrefix');
	if ($parent_prefix) {
	$parent_table_key = $this->Application->getUnitOption($this->Prefix, 'ParentTableKey');
	if (is_array($parent_table_key)) $parent_table_key = getArrayValue($parent_table_key, $parent_prefix);
	$foreign_key_field = $this->Application->getUnitOption($this->Prefix, 'ForeignKey');
	if (is_array($foreign_key_field)) $foreign_key_field = getArrayValue($foreign_key_field, $parent_prefix);

	if (!$parent_table_key \|\| !$foreign_key_field) {
	return ;
	}

	/** @var kDBItem $parent_object */
	$parent_object = $this->Application->recallObject($parent_prefix.'.'.$special);

	if (!$parent_object->isLoaded()) {
	$this->addFilter('parent_filter', 'FALSE');
	trigger_error('Parent ID not found (prefix: "<strong>' . rtrim($parent_prefix.'.'.$special, '.') . '</strong>"; sub-prefix: "<strong>' . $this->getPrefixSpecial() . '</strong>")', E_USER_NOTICE);
	return ;
	}

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

	/**
	* Returns true if list was queried (same name as for kDBItem for easy usage)
	*
	* @return bool
	* @access public
	*/
	public function isLoaded()
	{
	return $this->Queried && !$this->EOL();
	}

	/**
	* Returns specified field value from all selected rows.
	* Don't affect current record index
	*
	* @param string $field
	* @param bool $formatted
	* @param string $format
	* @return Array
	* @access public
	*/
	public function GetCol($field, $formatted = false, $format = null)
	{
	$i = 0;
	$ret = Array ();

	if ($formatted && array_key_exists('formatter', $this->Fields[$field])) {
	/** @var kFormatter $formatter */
	$formatter = $this->Application->recallObject($this->Fields[$field]['formatter']);

	while ($i < $this->SelectedCount) {
	$ret[] = $formatter->Format($this->Records[$i][$field], $field, $this, $format);
	$i++;
	}
	}
	else {
	while ($i < $this->SelectedCount) {
	$ret[] = $this->Records[$i][$field];
	$i++;
	}
	}

	return $ret;
	}

	/**
	* Set's field error, if pseudo passed not found then create it with message text supplied.
	* Don't overwrite existing pseudo translation.
	*
	* @param string $field
	* @param string $pseudo
	* @param string $error_label
	* @param Array $error_params
	* @return bool
	* @access public
	* @see kSearchHelper::processRangeField()
	* @see kDateFormatter::Parse()
	*/
	public function SetError($field, $pseudo, $error_label = null, $error_params = null)
	{
	$error_field = isset($this->Fields[$field]['error_field']) ? $this->Fields[$field]['error_field'] : $field;
	$this->FieldErrors[$error_field]['pseudo'] = $pseudo;

	$var_name = $this->getPrefixSpecial() . '_' . $field . '_error';
	$previous_pseudo = $this->Application->RecallVar($var_name);

	if ( $previous_pseudo ) {
	// don't set more then one error on field
	return false;
	}

	$this->Application->StoreVar($var_name, $pseudo);

	return true;
	}

	/**
	* Returns error pseudo
	*
	* @param string $field
	* @return string
	* @access public
	* @see kSearchHelper::processRangeField()
	*/
	public function GetErrorPseudo($field)
	{
	if ( !isset($this->FieldErrors[$field]) ) {
	return '';
	}

	return isset($this->FieldErrors[$field]['pseudo']) ? $this->FieldErrors[$field]['pseudo'] : '';
	}

	/**
	* Removes error on field
	*
	* @param string $field
	* @access public
	*/
	public function RemoveError($field)
	{
	unset( $this->FieldErrors[$field] );
	}

	/**
	* Group list records by header, saves internal order in group
	*
	* @param string $heading_field
	* @access public
	*/
	public function groupRecords($heading_field)
	{
	$i = 0;
	$sorted = Array ();

	while ($i < $this->SelectedCount) {
	$sorted[ $this->Records[$i][$heading_field] ][] = $this->Records[$i];
	$i++;
	}

	$this->Records = Array ();

	foreach ($sorted as $heading => $heading_records) {
	$this->Records = array_merge_recursive($this->Records, $heading_records);
	}
	}

	/**
	* Reset list (use for requering purposes)
	*
	* @access public
	*/
	public function reset()
	{
	$this->Counted = false;
	$this->clearFilters();
	$this->ClearOrderFields();
	}

	/**
	* Checks if list was counted
	*
	* @return bool
	* @access public
	*/
	public function isCounted()
	{
	return $this->Counted;
	}

	/**
	* Tells, that given list is main
	*
	* @return bool
	* @access public
	*/
	public function isMainList()
	{
	return $this->mainList;
	}

	/**
	* Makes given list as main
	*
	* @access public
	*/
	public function becameMain()
	{
	$this->mainList = true;
	}

	/**
	* Moves recordset pointer to first element
	*
	* @return void
	* @access public
	* @implements Iterator::rewind
	*/
	public function rewind()
	{
	$this->Query();
	$this->GoFirst();
	}

	/**
	* Returns value at current position
	*
	* @return mixed
	* @access public
	* @implements Iterator::current
	*/
	function current()
	{
	return $this->getCurrentRecord();
	}

	/**
	* Returns key at current position
	*
	* @return mixed
	* @access public
	* @implements Iterator::key
	*/
	function key()
	{
	return $this->CurrentIndex;
	}

	/**
	* Moves recordset pointer to next position
	*
	* @return void
	* @access public
	* @implements Iterator::next
	*/
	function next()
	{
	$this->GoNext();
	}

	/**
	* Detects if current position is within recordset bounds
	*
	* @return bool
	* @access public
	* @implements Iterator::valid
	*/
	public function valid()
	{
	return !$this->EOL();
	}

	/**
	* Counts recordset rows
	*
	* @return int
	* @access public
	* @implements Countable::count
	*/
	public function count()
	{
	return $this->SelectedCount;
	}
	}


	class LeftJoinOptimizer {

	/**
	* Input sql for optimization
	*
	* @var string
	* @access private
	*/
	private $sql = '';

	/**
	* All sql parts, where LEFT JOINed table aliases could be used
	*
	* @var string
	* @access private
	*/
	private $usageString = '';

	/**
	* List of discovered LEFT JOINs
	*
	* @var Array
	* @access private
	*/
	private $joins = Array ();

	/**
	* LEFT JOIN relations
	*
	* @var Array
	* @access private
	*/
	private $joinRelations = Array ();

	/**
	* LEFT JOIN table aliases scheduled for removal
	*
	* @var Array
	* @access private
	*/
	private $aliasesToRemove = Array ();

	/**
	* Creates new instance of the class
	*
	* @param string $sql
	* @param string $usage_string
	*/
	public function __construct($sql, $usage_string)
	{
	$this->sql = $sql;
	$this->usageString = $usage_string;

	$this->parseJoins();
	}

	/**
	* Tries to remove unused LEFT JOINs
	*
	* @return string
	* @access public
	*/
	public function simplify()
	{
	if ( !$this->joins ) {
	// no LEFT JOIN used, return unchanged sql
	return $this->sql;
	}

	$this->updateRelations();
	$this->removeAliases();

	return $this->sql;
	}

	/**
	* Discovers LEFT JOINs based on given sql
	*
	* @return void
	* @throws RuntimeException When failed to parse a LEFT JOIN expression.
	*/
	private function parseJoins()
	{
	$joins_found = preg_match_all(
	'/LEFT\s+JOIN\s+(.?\|.?\s+AS\s+.?\|.?\s+.?)\s+ON\s+(.?\n\|.*?$)/si',
	$this->sql,
	$regs
	);

	if ( !$joins_found ) {
	$this->joins = array();

	return;
	}

	// Get all LEFT JOIN clause info from sql (without filters).
	foreach ( $regs[1] as $index => $join_expression ) {
	// Format (without quotes): "tbl_name [PARTITION (partition_names)] [[AS] alias] [index_hint_list]".
	$join_expression_parsed = preg_match(
	'/([\S]+)(\sPARTITION\s$[^)]+$)?(\s(AS\s)?([\S]+))?((USE\|IGNORE\|FORCE)\s+.*)?/',
	$join_expression,
	$match_parts
	);

	if ( !$join_expression_parsed ) {
	throw new RuntimeException('Unable to parse join expression: ' . $join_expression);
	}

	$joined_table_name = $match_parts[1];
	$table_alias = isset($match_parts[5]) && $match_parts[5] !== '' ? $match_parts[5] : $joined_table_name;

	$this->joins[$table_alias] = array(
	'table' => $joined_table_name,
	'join_clause' => $regs[0][$index],
	);
	}
	}

	/**
	* Detects relations between LEFT JOINs
	*
	* @return void
	* @access private
	*/
	private function updateRelations()
	{
	foreach ($this->joins as $table_alias => $left_join_info) {
	$escaped_alias = preg_quote($table_alias, '/');

	foreach ($this->joins as $sub_table_alias => $sub_left_join_info) {
	if ($table_alias == $sub_table_alias) {
	continue;
	}

	if ( $this->matchAlias($escaped_alias, $sub_left_join_info['join_clause']) ) {
	$this->joinRelations[] = $sub_table_alias . ':' . $table_alias;
	}
	}
	}
	}

	/**
	* Removes scheduled LEFT JOINs, but only if they are not protected
	*
	* @return void
	* @access private
	*/
	private function removeAliases()
	{
	$this->prepareAliasesRemoval();

	foreach ($this->aliasesToRemove as $to_remove_alias) {
	if ( !$this->aliasProtected($to_remove_alias) ) {
	$this->sql = str_replace($this->joins[$to_remove_alias]['join_clause'], '', $this->sql);
	}
	}
	}

	/**
	* Schedules unused LEFT JOINs to for removal
	*
	* @return void
	* @access private
	*/
	private function prepareAliasesRemoval()
	{
	foreach ($this->joins as $table_alias => $left_join_info) {
	$escaped_alias = preg_quote($table_alias, '/');

	if ( !$this->matchAlias($escaped_alias, $this->usageString) ) {
	$this->aliasesToRemove[] = $table_alias;
	}
	}
	}

	/**
	* Checks if someone wants to remove LEFT JOIN, but it's used by some other LEFT JOIN, that stays
	*
	* @param string $table_alias
	* @return bool
	* @access private
	*/
	private function aliasProtected($table_alias)
	{
	foreach ($this->joinRelations as $relation) {
	list ($main_alias, $used_alias) = explode(':', $relation);

	if ( ($used_alias == $table_alias) && !in_array($main_alias, $this->aliasesToRemove) ) {
	return true;
	}
	}

	return false;
	}

	/**
	* Matches given escaped alias to a string
	*
	* @param string $escaped_alias
	* @param string $string
	* @return bool
	* @access private
	*/
	private function matchAlias($escaped_alias, $string)
	{
	return preg_match('/\b(`' . $escaped_alias . '`\|' . $escaped_alias . ')\./s', $string);
	}
	}

dblist.phpNo OneTemporaryActions

File Metadata

dblist.phpView Options

Event Timeline

dblist.php
No OneTemporary
Actions

dblist.php
View Options