<?php
/**
 * This file implements the ItemQuery class.
 *
 * This file is part of the evoCore framework - {@link http://evocore.net/}
 * See also {@link https://github.com/b2evolution/b2evolution}.
 *
 * @license GNU GPL v2 - {@link http://b2evolution.net/about/gnu-gpl-license}
 *
 * @copyright (c)2003-2020 by Francois Planque - {@link http://fplanque.com/}
 *
 * @package evocore
 */
if( !defined('EVO_MAIN_INIT') ) die( 'Please, do not access this page directly.' );

load_class( '_core/model/db/_sql.class.php', 'SQL' );

/**
 * ItemQuery: help constructing queries on Items
 * @package evocore
 */
class ItemQuery extends SQL
{
	var $p;
	var $pl;
	var $title;
	var $blog;
	var $cat;
	var $catsel;
	var $show_statuses;
	var $tags;
	var $author;
	var $author_login;
	var $assignees;
	var $assignees_login;
	var $involved_user_IDs;
	var $involved_user_logins;
	var $statuses;
	var $types;
	var $itemtype_usage;
	var $dstart;
	var $dstop;
	var $timestamp_min;
	var $timestamp_max;
	var $keywords;
	var $keyword_scope;
	var $phrase;
	var $exact;
	var $featured;
	var $flagged;
	var $mustread;

	/**
	 * A query SELECT string to add other columns.
	 * It is set in case of the select queries when we need to order by custom fields.
	 *
	 * @var string
	 */
	var $orderby_select = '';

	/**
	 * A query FROM string to join other tables.
	 * It is set in case of the select queries when we need to order by custom fields.
	 *
	 * @var string
	 */
	var $orderby_from = '';

	/**
	 * Constructor.
	 *
	 * @param string Name of table in database
	 * @param string Prefix of fields in the table
	 * @param string Name of the ID field (including prefix)
	 */
	function __construct( $dbtablename, $dbprefix = '', $dbIDname )
	{
		$this->dbtablename = $dbtablename;
		$this->dbprefix = $dbprefix;
		$this->dbIDname = $dbIDname;

		$this->FROM( $this->dbtablename );
	}


	/**
	 * Restrict to a specific post
	 */
	function where_ID( $p = '', $title = '' )
	{
		$r = false;

		$this->p = $p;
		$this->title = $title;

		// if a post number is specified, load that post
		if( !empty($p) )
		{
			if( substr( $this->p, 0, 1 ) == '-' )
			{	// Starts with MINUS sign:
				$eq_p = ' <> ';
				$this->p = substr( $this->p, 1 );
			}
			else
			{
				$eq_p = ' = ';
			}

			$this->WHERE_and( $this->dbIDname.$eq_p.intval($this->p) );
			$r = true;
		}

		// if a post urltitle is specified, load that post
		if( !empty( $title ) )
		{
			global $DB;
			if( substr( $this->title, 0, 1 ) == '-' )
			{	// Starts with MINUS sign:
				$title = substr( $this->title, 1 );
				$this->WHERE_and( 'post_ID NOT IN ( SELECT slug_itm_ID FROM T_slug WHERE slug_title = '.$DB->quote( $title ).' )' );
			}
			else
			{
				$this->FROM_add( 'LEFT JOIN T_slug ON slug_itm_ID = post_ID AND slug_type = "item" AND slug_title = '.$DB->quote( $title ) );
				$this->WHERE_and( 'slug_title IS NOT NULL' );
			}
			$r = true;
		}

		return $r;
	}


	/**
	 * Restrict to a specific list of posts
	 */
	function where_ID_list( $pl = '' )
	{
		$this->pl = clear_ids_list( $pl );

		if( empty( $this->pl ) )
		{	// Nothing to filter:
			return;
		}

		if( substr( $pl, 0, 1 ) == '-' )
		{	// List starts with MINUS sign:
			$eq = 'NOT IN';
		}
		else
		{
			$eq = 'IN';
		}

		$this->WHERE_and( $this->dbIDname.' '.$eq.'( '.$this->pl.' )' );
	}


	/**
	 * Restrict to specific collection/chapters (blog/categories)
	 *
	 * @param integer
	 * @param string List of cats to restrict to
	 * @param array Array of cats to restrict to
	 */
	function where_chapter( $blog, $cat = '', $catsel = array() )
	{
		global $cat_array; // this is required for the cat_req() callback in compile_cat_array()

		$blog = intval($blog);	// Extra security

		// Save for future use (permission checks..)
		$this->blog = $blog;

		$this->FROM_add( 'INNER JOIN T_postcats ON '.$this->dbIDname.' = postcat_post_ID
											INNER JOIN T_categories ON postcat_cat_ID = cat_ID' );

		$BlogCache = & get_BlogCache();
		$current_Blog = $BlogCache->get_by_ID( $blog );

		$this->WHERE_and( $current_Blog->get_sql_where_aggregate_coll_IDs( 'T_categories.cat_blog_ID' ) );


		$cat_array = NULL;
		$cat_modifier = NULL;

		// Compile the real category list to use:
		// TODO: allow to pass the compiled vars directly to this class
		compile_cat_array( $cat, $catsel, /* by ref */ $cat_array, /* by ref */ $cat_modifier, /* TODO $blog == 1 ? 0 : */ $blog );

		if( ! empty($cat_array) )
		{	// We want to restict to some cats:
			global $DB;

			if( $cat_modifier == '-' )
			{
				$eq = 'NOT IN';
			}
			else
			{
				$eq = 'IN';
			}
			$whichcat = 'postcat_cat_ID '. $eq.' ('.$DB->quote( $cat_array ). ') ';

			// echo $whichcat;
			$this->WHERE_and( $whichcat );

			if( $cat_modifier == '*' )
			{ // We want the categories combined! (i-e posts must be in ALL requested cats)
				$this->GROUP_BY( $this->dbIDname.' HAVING COUNT(postcat_cat_ID) = '.count($cat_array) );
			}
		}
	}


	/**
	 * Restrict to specific collection/chapters (blog/categories)
	 *
	 * @param object Blog
	 * @param array Categories IDs
	 * @param string Use '-' to exclude the categories
	 * @param string 'wide' to search in extra cats too
	 *               'main' for main cat only
	 *               'extra' for extra cats only
	 * @param string Collection IDs
	 */
	function where_chapter2( & $Blog, $cat_array, $cat_modifier, $cat_focus = 'wide', $coll_IDs = NULL )
	{
		// Save for future use (permission checks..)
		$this->blog = $Blog->ID;
		$this->Blog = $Blog;
		$this->cat_array = $cat_array;
		$this->cat_modifier = $cat_modifier;

		if( ! empty( $cat_array ) && ( $cat_focus == 'wide' || $cat_focus == 'extra' ) )
		{	// Select extra categories if we want filter by several categories:
			$sql_join_categories = ( $cat_focus == 'extra' ) ? ' AND post_main_cat_ID != cat_ID' : '';
			$this->FROM_add( 'INNER JOIN T_postcats ON '.$this->dbIDname.' = postcat_post_ID' );
			$this->FROM_add( 'INNER JOIN T_categories ON postcat_cat_ID = cat_ID'.$sql_join_categories );
			// fp> we try to restrict as close as possible to the posts but I don't know if it matters
			$cat_ID_field = 'T_postcats.postcat_cat_ID';
		}
		elseif( get_allow_cross_posting() >= 1 )
		{	// Select extra categories if cross posting is enabled:
			$this->FROM_add( 'INNER JOIN T_postcats ON '.$this->dbIDname.' = postcat_post_ID' );
			$this->FROM_add( 'INNER JOIN T_categories ON postcat_cat_ID = cat_ID' );
			$cat_ID_field = 'T_postcats.postcat_cat_ID';
		}
		else
		{	// Select only main categories:
			$this->FROM_add( 'INNER JOIN T_categories ON post_main_cat_ID = cat_ID' );
			$cat_ID_field = 'post_main_cat_ID';
		}

		if( ! empty( $coll_IDs ) )
		{ // Force to aggregate the collection IDs from current param and not from blog setting
			$this->WHERE_and( $Blog->get_sql_where_aggregate_coll_IDs( 'T_categories.cat_blog_ID', $coll_IDs ) );
		}
		elseif( $cat_focus == 'main' )
		{ // We are requesting a narrow search
			$this->WHERE_and( 'T_categories.cat_blog_ID = '.$Blog->ID );
		}
		else
		{ // Aggregate the collections IDs from blog setting
			$this->WHERE_and( $Blog->get_sql_where_aggregate_coll_IDs( 'T_categories.cat_blog_ID' ) );
		}


		if( ! empty($cat_array) )
		{	// We want to restict to some cats:
			global $DB;

			if( $cat_modifier == '-' )
			{
				$eq = 'NOT IN';
			}
			else
			{
				$eq = 'IN';
			}
			$whichcat = $cat_ID_field.' '.$eq.' ('.$DB->quote( $cat_array ). ') ';

			// echo $whichcat;
			$this->WHERE_and( $whichcat );

			if( $cat_modifier == '*' )
			{ // We want the categories combined! (i-e posts must be in ALL requested cats)
				$this->GROUP_BY( $this->dbIDname.' HAVING COUNT('.$cat_ID_field.') = '.count($cat_array) );
			}
		}
	}


	/**
	 * Restrict to the visibility/sharing statuses we want to show
	 *
	 * @param array Restrict to these statuses
	 * @param string What blogs should be used to check the status permissions:
	 *               - Empty string to use current blog setting
	 *               - A list separated by ','
	 *               - Value '*' to use all blogs
	 *               - Value '-' to use only current blog without aggregated blog
	 */
	function where_visibility( $show_statuses, $aggregate_coll_IDs = NULL )
	{
		$this->show_statuses = $show_statuses;

		if( ! isset( $this->blog ) )
		{
			debug_die( 'Status restriction requires to work with a specific blog first.' );
		}

		if( empty( $aggregate_coll_IDs ) && ! empty( $this->blog ) )
		{ // Blog IDs are not defined, Use them depending on current collection setting
			// NOTE! collection can be 0, for example, on disp=usercomments|useritems where we display data from all collections
			$BlogCache = & get_BlogCache();
			$Collection = $Blog = & $BlogCache->get_by_ID( $this->blog );
			$aggregate_coll_IDs = $Blog->get_setting( 'aggregate_coll_IDs' );
		}

		$blog_IDs = array();
		if( empty( $aggregate_coll_IDs ) || $aggregate_coll_IDs == '-' )
		{ // Get status restriction only for current blog
			$this->WHERE_and( statuses_where_clause( $show_statuses, $this->dbprefix, $this->blog, 'blog_post!', true, $this->author ) );
			return; // Exit here, because we don't need to check the permissions for multiple blogs
		}

		// Check status permission for multiple blogs
		if( $aggregate_coll_IDs == '*' )
		{ // Get the status restrictions for all blogs
			// Load all collections in single query, because otherwise we may have too many queries (1 query for each collection) later:
			// fp> TODO: PERF: we probably want to remove this later when we restrict the use of '*'
			$BlogCache = & get_BlogCache();
			$BlogCache->load_all();
			$blog_IDs = $BlogCache->get_ID_array();
		}
		else
		{ // Get the status restrictions for several blogs
			$blog_IDs = explode( ',', $aggregate_coll_IDs );
		}

		$status_coll_clauses = array();
		foreach( $blog_IDs as $blog_ID )
		{	// Check status permission for each blog separately:
			$statuses_where_clause = statuses_where_clause( $show_statuses, $this->dbprefix, $blog_ID, 'blog_post!', true, $this->author );
			if( ! isset( $status_coll_clauses[ $statuses_where_clause ] ) )
			{	// Initialize array item for each different status condition:
				$status_coll_clauses[ $statuses_where_clause ] = array();
			}
			// Group collections by same status condition:
			$status_coll_clauses[ $statuses_where_clause ][] = $blog_ID;
		}
		$status_restrictions = array();
		foreach( $status_coll_clauses as $status_coll_clause => $status_coll_IDs )
		{	// Initialize status permission restriction for each grouped condition that is formed above:
			$status_restrictions[] = 'T_categories.cat_blog_ID IN ( '.implode( ',', $status_coll_IDs ).' ) AND '.$status_coll_clause;
		}

		$this->WHERE_and( '( '.implode( ' ) OR ( ', $status_restrictions ).' )' );
	}


	/**
	 * Restrict to the featured/non featured posts if requested
	 *
	 * @param boolean|NULL Restrict to featured
	 */
	function where_featured( $featured = NULL )
	{
		$this->featured = $featured;

		if( is_null( $this->featured ) )
		{ // no restriction
			return;
		}
		elseif( !empty( $this->featured ) )
		{ // restrict to featured
			$this->WHERE_and( $this->dbprefix.'featured <> 0' );
		}
		else
		{ // restrict to NON featured
			$this->WHERE_and( $this->dbprefix.'featured = 0' );
		}
	}


	/**
	 * Restrict to specific tags
	 *
	 * @param string List of tags to restrict to
	 * @param string Condition to search by tags, 'OR' - if item has at least one tag, 'AND' - item must has all tags from the list
	 */
	function where_tags( $tags, $tags_operator = 'OR' )
	{
		global $DB;

		$this->tags = $tags;

		if( empty( $tags ) )
		{
			return;
		}

		$tags = explode( ',', $tags );

		if( $tags_operator == 'AND' )
		{	// Search items with ALL tags from the restriction list:
			$this->WHERE_and( '( SELECT COUNT( itag_tag_ID )
				 FROM T_items__tag
				INNER JOIN T_items__itemtag ON tag_ID = itag_tag_ID
				WHERE itag_itm_ID = post_ID
				  AND tag_name IN ( '.$DB->quote( $tags ).' )
				) = '.count( $tags ) );
		}
		else // 'OR'
		{	// Search items with at least one tag from the restriction list:
			$this->FROM_add( 'INNER JOIN T_items__itemtag ON post_ID = itag_itm_ID' );
			$this->FROM_add( 'INNER JOIN T_items__tag ON itag_tag_ID = tag_ID AND tag_name IN ( '.$DB->quote( $tags ).' )' );
		}
	}


	/**
	 * Restrict to specific authors by users IDs
	 *
	 * @param string List of authors IDs to restrict to (must have been previously validated)
	 */
	function where_author( $author_IDs )
	{
		$this->author = clear_ids_list( $author_IDs );

		if( empty( $this->author ) )
		{
			return;
		}

		if( substr( $author_IDs, 0, 1 ) == '-' )
		{ // Exclude the users IF a list starts with MINUS sign:
			$eq = 'NOT IN';
		}
		else
		{ // Include the users:
			$eq = 'IN';
		}

		$this->WHERE_and( $this->dbprefix.'creator_user_ID '.$eq.' ( '.$this->author.' )' );
	}


	/**
	 * Restrict to specific authors by users logins
	 *
	 * @param string List of authors logins to restrict to (must have been previously validated)
	 */
	function where_author_logins( $author_logins )
	{
		$this->author_login = $author_logins;

		if( empty( $this->author_login ) )
		{
			return;
		}

		if( substr( $this->author_login, 0, 1 ) == '-' )
		{ // Exclude the users IF a list starts with MINUS sign:
			$eq = 'NOT IN';
			$users_IDs = get_users_IDs_by_logins( substr( $this->author_login, 1 ) );
		}
		else
		{ // Include the users:
			$eq = 'IN';
			$users_IDs = get_users_IDs_by_logins( $this->author_login );
		}

		if( ! empty( $users_IDs ) )
		{
			$this->WHERE_and( $this->dbprefix.'creator_user_ID '.$eq.' ( '.$users_IDs.' )' );
		}
	}


	/**
	 * Restrict to specific assignees by users IDs
	 *
	 * @param string List of assignees IDs to restrict to (must have been previously validated)
	 */
	function where_assignees( $assignees )
	{
		$this->assignees = clear_ids_list( $assignees );

		if( empty( $assignees ) )
		{
			return;
		}

		if( $assignees == '-' )
		{	// List is ONLY a MINUS sign (we want only those not assigned)
			$this->WHERE_and( $this->dbprefix.'assigned_user_ID IS NULL' );
		}

		if( empty( $this->assignees ) )
		{
			return;
		}

		if( substr( $assignees, 0, 1 ) == '-' )
		{	// List starts with MINUS sign:
			$this->WHERE_and( '( '.$this->dbprefix.'assigned_user_ID IS NULL
			                  OR '.$this->dbprefix.'assigned_user_ID NOT IN ('.$this->assignees.') )' );
		}
		else
		{
			$this->WHERE_and( $this->dbprefix.'assigned_user_ID IN ('.$this->assignees.')' );
		}
	}


	/**
	 * Restrict to specific assignees by users logins
	 *
	 * @param string List of assignees logins to restrict to (must have been previously validated)
	 */
	function where_assignees_logins( $assignees_logins )
	{
		$this->assignees_logins = $assignees_logins;

		if( empty( $this->assignees_logins ) )
		{
			return;
		}

		if( $this->assignees_logins == '-' )
		{	// List is ONLY a MINUS sign (we want only those not assigned)
			$this->WHERE_and( $this->dbprefix.'assigned_user_ID IS NULL' );
		}
		elseif( substr( $this->assignees_logins, 0, 1 ) == '-' )
		{	// List starts with MINUS sign:
			$this->WHERE_and( '( '.$this->dbprefix.'assigned_user_ID IS NULL
			                  OR '.$this->dbprefix.'assigned_user_ID NOT IN ('.get_users_IDs_by_logins( substr( $this->assignees_logins, 1 ) ).') )' );
		}
		else
		{
			$this->WHERE_and( $this->dbprefix.'assigned_user_ID IN ('.get_users_IDs_by_logins( $this->assignees_logins ).')' );
		}
	}


	/**
	 * Restrict to specific assignee or author
	 *
	 * @param integer assignee or author to restrict to (must have been previously validated)
	 */
	function where_author_assignee( $author_assignee )
	{
		global $DB;

		$this->author_assignee = $author_assignee;

		if( empty( $author_assignee ) )
		{
			return;
		}

		$this->WHERE_and( '( '.$this->dbprefix.'creator_user_ID = '.$DB->quote( $author_assignee ).' OR '.
											$this->dbprefix.'assigned_user_ID = '.$DB->quote( $author_assignee ).' )' );
	}


	/**
	 * Restrict items that have any comment OR internal/meta comment by the specific users
	 *
	 * @param string List of user IDs to restrict to (must have been previously validated)
	 */
	function where_involves( $involved_user_IDs )
	{
		$this->involved_user_IDs = clear_ids_list( $involved_user_IDs );

		if( empty( $this->involved_user_IDs ) )
		{
			return;
		}

		if( substr( $involved_user_IDs, 0, 1 ) == '-' )
		{	// Exclude the users IF a list starts with MINUS sign:
			$eq = 'NOT IN';
		}
		else
		{	// Include the users:
			$eq = 'IN';
		}

		$this->FROM_add( 'INNER JOIN T_comments AS involved_id ON involved_id.comment_item_ID = post_ID' );
		$this->WHERE_and( 'involved_id.comment_author_user_ID '.$eq.' ( '.$this->involved_user_IDs.' )' );
	}


	/**
	 * Restrict items that have any comment OR internal/meta comment by the specific users
	 *
	 * @param string List of involved user logins to restrict to (must have been previously validated)
	 */
	function where_involves_logins( $involved_user_logins )
	{
		$this->involved_user_logins = $involved_user_logins;

		if( empty( $this->involved_user_logins ) )
		{
			return;
		}

		if( substr( $involved_user_logins, 0, 1 ) == '-' )
		{	// Exclude the users IF a list starts with MINUS sign:
			$eq = 'NOT IN';
			$involved_user_IDs = get_users_IDs_by_logins( substr( $this->involved_user_logins, 1 ) );
		}
		else
		{	// Include the users:
			$eq = 'IN';
			$involved_user_IDs = get_users_IDs_by_logins( $this->involved_user_logins );
		}

		if( ! empty( $involved_user_IDs ) )
		{	// Filter only if correct users are found by logins:
			$this->FROM_add( 'INNER JOIN T_comments AS involved_login ON involved_login.comment_item_ID = post_ID' );
			$this->WHERE_and( 'involved_login.comment_author_user_ID '.$eq.' ( '.$involved_user_IDs.' )' );
		}
	}


	/**
	 * Restrict to specific locale
	 *
	 * @param string locale to restrict to ('all' if you don't want to restrict)
	 */
	function where_locale( $locale )
	{
		global $DB;

		if( $locale == 'all' )
		{
			return;
		}

		$this->WHERE_and( $this->dbprefix.'locale LIKE '.$DB->quote($locale.'%') );
	}


	/**
	 * Restrict to specific (extended) statuses
	 *
	 * @param string List of statuses to restrict to (must have been previously validated)
	 */
	function where_statuses( $statuses )
	{
		$this->statuses = clear_ids_list( $statuses );

		if( empty( $statuses ) )
		{
			return;
		}

		if( $statuses == '-' )
		{	// List is ONLY a MINUS sign (we want only those not assigned)
			$this->WHERE_and( $this->dbprefix.'pst_ID IS NULL' );
		}

		if( empty( $this->statuses ) )
		{
			return;
		}

		if( substr( $statuses, 0, 1 ) == '-' )
		{	// List starts with MINUS sign:
			$this->WHERE_and( '( '.$this->dbprefix.'pst_ID IS NULL
			                  OR '.$this->dbprefix.'pst_ID NOT IN ('.$this->statuses.') )' );
		}
		else
		{
			$this->WHERE_and( $this->dbprefix.'pst_ID IN ('.$this->statuses.')' );
		}
	}


	/**
	 * Restrict to specific (extended) statuses
	 *
	 * @param array Array of statuses to restrict to (must have been previously validated)
	 */
	function where_statuses_array( $statuses )
	{
		if( ! is_array( $statuses ) )
		{	// Wrong data:
			return;
		}

		$filter_without_status = false;
		foreach( $statuses as $s => $status_ID )
		{
			if( $status_ID == '-' )
			{	// Filter by "No status":
				$filter_without_status = true;
				unset( $statuses[ $s ] );
				continue;
			}

			$status_ID = intval( $status_ID );

			if( empty( $status_ID ) )
			{	// Remove a not number value from list:
				unset( $statuses[ $s ] );
				continue;
			}

			// Update value to integer format:
			$statuses[ $s ] = $status_ID;
		}

		$where_statuses = array();
		if( $filter_without_status )
		{	// Filter by "No status":
			$where_statuses[] = $this->dbprefix.'pst_ID IS NULL';
		}
		if( ! empty( $statuses ) )
		{	// Filter by specific statuses:
			$where_statuses[] = $this->dbprefix.'pst_ID IN ( '.implode( ',', $statuses ).' )';
		}

		if( ! empty( $where_statuses ) )
		{	// Apply filter by statuses:
			$this->WHERE_and( implode( ' OR ', $where_statuses ) );
		}
	}


	/**
	 * Restrict to specific post types
	 *
	 * @param string List of types to restrict to (must have been previously validated)
	 */
	function where_types( $types )
	{
		$this->types = clear_ids_list( $types );

		if( empty( $types ) )
		{
			return;
		}

		if( $types == '-' )
		{	// List is ONLY a MINUS sign (we want only those not assigned)
			$this->WHERE_and( $this->dbprefix.'ityp_ID IS NULL' );
		}

		if( empty( $this->types ) )
		{
			return;
		}

		if( substr( $types, 0, 1 ) == '-' )
		{	// List starts with MINUS sign:
			$this->WHERE_and( '( '.$this->dbprefix.'ityp_ID IS NULL
			                  OR '.$this->dbprefix.'ityp_ID NOT IN ('.$this->types.') )' );
		}
		else
		{
			$this->WHERE_and( $this->dbprefix.'ityp_ID IN ('.$this->types.')' );
		}
	}


	/**
	 * Restrict to specific post types usage
	 *
	 * @param string List of types usage to restrict to (must have been previously validated):
	 *               Allowed values: post, page, intro-front, intro-main, intro-cat, intro-tag, intro-sub, intro-all, special,
	 *                               *featured* - to get also featured posts
	 */
	function where_itemtype_usage( $itemtype_usage )
	{
		global $DB;

		$this->itemtype_usage = $itemtype_usage;

		if( empty( $itemtype_usage ) )
		{
			return;
		}

		$featured_sql_where = '';
		$not_featured_sql_where = '';
		if( strpos( $itemtype_usage, '*featured*' ) !== false )
		{	// Get also featured posts:
			$itemtype_usage = preg_replace( '#,?\*featured\*,?#', '', $itemtype_usage );
			$featured_sql_where .= ' OR post_featured = 1';
			$not_featured_sql_where .= ' AND post_featured != 1';
		}

		$this->FROM_add( 'LEFT JOIN T_items__type ON ityp_ID = '.$this->dbprefix.'ityp_ID' );

		if( $itemtype_usage == '-' )
		{	// List is ONLY a MINUS sign (we want only those not assigned)
			$this->WHERE_and( $this->dbprefix.'ityp_ID IS NULL'.$not_featured_sql_where );
		}
		elseif( substr( $itemtype_usage, 0, 1 ) == '-' )
		{	// List starts with MINUS sign:
			$itemtype_usage = explode( ',', substr( $itemtype_usage, 1 ) );
			$this->WHERE_and( '( '.$this->dbprefix.'ityp_ID IS NULL
			                  OR ( ityp_usage NOT IN ( '.$DB->quote( $itemtype_usage ).' )'.$not_featured_sql_where.' ) )' );
		}
		else
		{
			$itemtype_usage = explode( ',', $itemtype_usage );
			$this->WHERE_and( '( ityp_usage IN ( '.$DB->quote( $itemtype_usage ).' )'.$featured_sql_where.' )' );
		}
	}


	/**
	 * Restricts the datestart param to a specific date range.
	 *
	 * Start date gets restricted to minutes only (to make the query more
	 * cachable).
	 *
	 * Priorities:
	 *  -dstart and/or dstop
	 *  -week + m
	 *  -m
	 * @todo  -dstart + x days
	 * @see ItemList2::get_advertised_start_date()
	 *
	 * @param string YYYYMMDDHHMMSS (everything after YYYY is optional) or ''
	 * @param integer week number or ''
	 * @param string YYYYMMDDHHMMSS to start at, '' for first available
	 * @param string YYYYMMDDHHMMSS to stop at
	 * @param mixed Do not show posts before this timestamp, can be 'now'
	 * @param mixed Do not show posts after this timestamp, can be 'now'
	 */
	function where_datestart( $m = '', $w = '', $dstart = '', $dstop = '', $timestamp_min = '', $timestamp_max = 'now' )
	{
		global $time_difference, $DB;

		$this->m = $m;
		$this->w = $w;
		$this->dstart = $dstart;
		$this->dstop = $dstop;
		$this->timestamp_min = $timestamp_min;
		$this->timestamp_max = $timestamp_max;


		$start_is_set = false;
		$stop_is_set = false;


		// if a start date is specified in the querystring, crop anything before
		if( !empty($dstart) )
		{
			// Add trailing 0s: YYYYMMDDHHMMSS
			$dstart0 = $dstart.'00000000000000';  // TODO: this is NOT correct, should be 0101 for month

			// Start date in MySQL format: seconds get omitted (rounded to lower to minute for caching purposes)
			$dstart_mysql = substr($dstart0,0,4).'-'.substr($dstart0,4,2).'-'.substr($dstart0,6,2).' '
											.substr($dstart0,8,2).':'.substr($dstart0,10,2);

			$this->WHERE_and( $this->dbprefix.'datestart >= '.$DB->quote( $dstart_mysql ).'
													OR ( '.$this->dbprefix.'datedeadline IS NULL AND '.$this->dbprefix.'datestart >= '.$DB->quote( $dstart_mysql ).' )' );

			$start_is_set = true;
		}


		// if a stop date is specified in the querystring, crop anything before
		if( !empty($dstop) )
		{
			switch( strlen( $dstop ) )
			{
				case '4':
					// We have only year, add one to year
					$dstop_mysql = ($dstop+1).'-01-01 00:00:00';
					break;

				case '6':
					// We have year month, add one to month
					$dstop_mysql = date("Y-m-d H:i:s ", mktime(0, 0, 0, substr($dstop,4,2)+1, 01, substr($dstop,0,4)));
					break;

				case '8':
					// We have year mounth day, add one to day
					$dstop_mysql = date("Y-m-d H:i:s ", mktime(0, 0, 0, substr($dstop,4,2), (substr($dstop,6,2) + 1 ), substr($dstop,0,4)));
					break;

				case '10':
					// We have year mounth day hour, add one to hour
					$dstop_mysql = date("Y-m-d H:i:s ", mktime( ( substr($dstop,8,2) + 1 ), 0, 0, substr($dstop,4,2), substr($dstop,6,2), substr($dstop,0,4)));
					break;

				case '12':
					// We have year mounth day hour minute, add one to minute
					$dstop_mysql = date("Y-m-d H:i:s ", mktime( substr($dstop,8,2), ( substr($dstop,8,2) + 1 ), 0, substr($dstop,4,2), substr($dstop,6,2), substr($dstop,0,4)));
					break;

				default:
					// add one to second
					// Stop date in MySQL format: seconds get omitted (rounded to lower to minute for caching purposes)
					$dstop_mysql = substr($dstop,0,4).'-'.substr($dstop,4,2).'-'.substr($dstop,6,2).' '
											.substr($dstop,8,2).':'.substr($dstop,10,2);
			}

			$this->WHERE_and( $this->dbprefix.'datestart < '.$DB->quote( $dstop_mysql ) ); // NOT <= comparator because we compare to the superior stop date

			$stop_is_set = true;
		}


		if( !$start_is_set || !$stop_is_set )
		{

			if( !is_null($w)  // Note: week # can be 0
					&& strlen($m) == 4 )
			{ // If a week number is specified (with a year)

				// Note: we use PHP to calculate week boundaries in order to handle weeks
				// that overlap 2 years properly, even when start on week is monday (which MYSQL won't handle properly)
				$start_date_for_week = get_start_date_for_week( $m, $w, locale_startofweek() );

				$this->WHERE_and( $this->dbprefix."datestart >= '".date('Y-m-d',$start_date_for_week)."'" );
				$this->WHERE_and( $this->dbprefix."datestart < '".date('Y-m-d',$start_date_for_week+604800 )."'" ); // + 7 days

				$start_is_set = true;
				$stop_is_set = true;
			}
			elseif( !empty($m) )
			{	// We want to restrict on an interval:
				$this->WHERE_and( 'EXTRACT(YEAR FROM '.$this->dbprefix.'datestart)='.intval(substr($m,0,4)) );
				if( strlen($m) > 5 )
					$this->WHERE_and( 'EXTRACT(MONTH FROM '.$this->dbprefix.'datestart)='.intval(substr($m,4,2)) );
				if( strlen($m) > 7 )
					$this->WHERE_and( 'EXTRACT(DAY FROM '.$this->dbprefix.'datestart)='.intval(substr($m,6,2)) );
				if( strlen($m) > 9 )
					$this->WHERE_and( 'EXTRACT(HOUR FROM '.$this->dbprefix.'datestart)='.intval(substr($m,8,2)) );
				if( strlen($m) > 11 )
					$this->WHERE_and( 'EXTRACT(MINUTE FROM '.$this->dbprefix.'datestart)='.intval(substr($m,10,2)) );
				if( strlen($m) > 13 )
					$this->WHERE_and( 'EXTRACT(SECOND FROM '.$this->dbprefix.'datestart)='.intval(substr($m,12,2)) );

				$start_is_set = true;
				$stop_is_set = true;
			}

		}


		// TODO: start + x days
		// TODO: stop - x days


		// SILENT limits!

		// Timestamp limits:
		if( $timestamp_min == 'now' )
		{
			// echo 'hide past';
			$timestamp_min = time();
		}
		if( !empty($timestamp_min) )
		{ // Hide posts before
			// echo 'hide before '.$timestamp_min;
			$date_min = remove_seconds( $timestamp_min + $time_difference );
			$this->WHERE_and( $this->dbprefix.'datestart >= '.$DB->quote( $date_min ) );
		}

		if( $timestamp_max == 'now' )
		{
			// echo 'hide future';
			$timestamp_max = time();
		}
		if( !empty($timestamp_max) )
		{ // Hide posts after
			// echo 'after';
			$date_max = remove_seconds( $timestamp_max + $time_difference );
			$this->WHERE_and( $this->dbprefix.'datestart <= '.$DB->quote( $date_max ) );
		}

	}


	/**
	 * Restricts creation date to a specific date range.
	 *
 	 * @param mixed Do not show posts CREATED after this timestamp
	 */
	function where_datecreated( $timestamp_created_max = 'now' )
	{
		global $time_difference, $DB;

		if( !empty($timestamp_created_max) )
		{ // Hide posts after
			// echo 'after';
			$date_max = date('Y-m-d H:i:s', $timestamp_created_max + $time_difference );
			$this->WHERE_and( $this->dbprefix.'datecreated <= '.$DB->quote( $date_max ) );
		}

	}


	/**
	 * Restrict with keywords
	 *
	 * @param string Keyword search string
	 * @param string Search for entire phrase or for individual words: 'OR', 'AND', 'sentence'(or '1')
	 * @param string Require exact match of title or contents — does NOT apply to tags which are always an EXACT match
	 * @param string Scope of keyword search string: 'title', 'content', 'tags', 'excerpt', 'titletag', 'metadesc', 'metakeywords'
	 */
	function where_keywords( $keywords, $phrase, $exact, $keyword_scope = 'title,content' )
	{
		global $DB;

		$this->keywords = utf8_trim( $keywords );
		$this->keyword_scope = $keyword_scope;
		$this->phrase = $phrase;
		$this->exact = $exact;

		if( empty( $keywords ) )
		{ // Nothing to search, Exit here:
			return;
		}

		$search_sql = array();

		// Determine what fields should be used in search:
		$search_fields = array();
		$keyword_scopes = explode( ',', $keyword_scope );
		foreach( $keyword_scopes as $keyword_scope )
		{
			switch( $keyword_scope )
			{
				case 'title':
					$search_fields[] = $this->dbprefix.'title';
					break;

				case 'content':
					$search_fields[] = $this->dbprefix.'content';
					break;

				case 'tags':
					$this->FROM_add( 'LEFT JOIN T_items__itemtag ON post_ID = itag_itm_ID' );
					$this->FROM_add( 'LEFT JOIN T_items__tag ON itag_tag_ID = tag_ID' );
					$this->GROUP_BY( 'post_ID' );
					// Tags are always an EXACT match:
					$search_sql[] = 'tag_name = '.$DB->quote( $keywords );
					break;

				case 'excerpt':
					$search_fields[] = $this->dbprefix.'excerpt';
					break;

				case 'titletag':
					$search_fields[] = $this->dbprefix.'titletag';
					break;

				case 'metadesc':
					$this->FROM_add( 'LEFT JOIN T_items__item_settings AS is_md ON post_ID = is_md.iset_item_ID AND is_md.iset_name = "metadesc"' );
					$search_fields[] = 'is_md.iset_value';
					break;

				case 'metakeywords':
					$this->FROM_add( 'LEFT JOIN T_items__item_settings AS is_mk ON post_ID = is_mk.iset_item_ID AND is_mk.iset_name = "metakeywords"' );
					$search_fields[] = 'is_mk.iset_value';
					break;

				// TODO: add more.
			}
		}

		if( empty( $search_fields ) && empty( $search_sql ) )
		{	// No correct fields to search, Exit here:
			return;
		}

		// Set sql operator depending on parameter:
		if( in_array( strtolower( $phrase ), array( 'or', '1', 'sentence' ) ) )
		{
			$operator_sql = 'OR';
		}
		else
		{
			$operator_sql =  'AND';
		}

		if( ! empty( $search_fields ) )
		{	// Do search if at least one field is requested:

			if( $exact )
			{	// We want exact match of each search field:
				$mask = '';
			}
			else
			{	// The words/sentence are/is to be included in the each search field:
				$mask = '%';
			}

			if( $phrase == '1' || $phrase == 'sentence' )
			{	// Sentence search:
				foreach( $search_fields as $search_field )
				{
					$search_sql[] = $search_field.' LIKE '.$DB->quote( $mask.$keywords.$mask );
				}
			}
			else
			{	// Word search:

				// Put spaces instead of commas:
				$keywords = preg_replace( '/, +/', ',', $keywords );
				$keywords = utf8_trim( str_replace( array( ',', '"' ), ' ', $keywords ) );

				// Split by each word:
				$keywords = preg_split( '/\s+/', $keywords );

				foreach( $keywords as $keyword )
				{
					$search_field_sql = array();
					foreach( $search_fields as $search_field )
					{
						$search_field_sql[] = $search_field.' LIKE '.$DB->quote( $mask.$keyword.$mask );
					}
					$search_sql[] = '( '.implode( ' OR ', $search_field_sql ).' )';
				}
			}
		}

		// Concat the searches:
		$search_sql = '( '.implode( ' '.$operator_sql.' ', $search_sql ).' )';

		$this->WHERE_and( $search_sql );
	}


	/**
	 * Restrict to the flagged items
	 *
	 * @param boolean TRUE - Restrict to flagged items, FALSE - Don't restrict/Get all items
	 */
	function where_flagged( $flagged = false )
	{
		global $current_User;

		$this->flagged = $flagged;

		if( ! $this->flagged )
		{	// Don't restrict if it is not requested or if user is not logged in:
			return;
		}

		// Get items which are flagged by current user:
		$this->FROM_add( 'INNER JOIN T_items__user_data ON '.$this->dbIDname.' = itud_item_ID
			AND itud_flagged_item = 1
			AND itud_user_ID = '.( is_logged_in() ? $current_User->ID : '-1' ) );
	}


	/**
	 * Restrict with locale visibility by current navigation locale
	 */
	function where_locale_visibility()
	{
		if( is_admin_page() )
		{	// Don't restrict this in back-office:
			return;
		}

		global $DB, $current_locale;

		$this->WHERE_and( 'post_locale_visibility = "always" OR post_locale = '.$DB->quote( $current_locale ) );
	}


	/**
	 * Restrict to the flagged items
	 *
	 * @param boolean TRUE/1/'all' - Restrict to all(Read and Unread) items with "must read" flag,
	 *                FALSE - Don't restrict/Get all items,
	 *               'unread' - Items which are not read by current User yet,
	 *               'read' - Items which are already read by current User.
	 * 
	 */
	function where_mustread( $mustread = false )
	{
		global $current_User, $DB;

		$this->mustread = $mustread;

		if( ! $this->mustread )
		{	// Don't restrict if it is not requested:
			return;
		}

		if( $mustread === 'read' )
		{	// Get Items which are already read by current User:
			if( is_logged_in() )
			{	// For logged in user:
				$this->FROM_add( 'INNER JOIN T_items__user_data AS mustread_status ON mustread_status.itud_item_ID = post_ID AND mustread_status.itud_user_ID = '.$DB->quote( $current_User->ID ) );
				$this->WHERE_and( 'mustread_status.itud_read_item_ts >= post_contents_last_updated_ts' );
			}
			else
			{	// Decide all Items are not read by not logged in user,
				// because we store read status only for logged in users:
				$this->WHERE_and( 'FALSE' );
				return;
			}
		}
		elseif( $mustread === 'unread' )
		{	// Get Items which are NOT read by current User yet:
			if( is_logged_in() )
			{	// For logged in user:
				$this->FROM_add( 'LEFT JOIN T_items__user_data AS mustread_status ON mustread_status.itud_item_ID = post_ID AND mustread_status.itud_user_ID = '.$DB->quote( $current_User->ID ) );
				$this->WHERE_and( 'mustread_status.itud_read_item_ts IS NULL OR mustread_status.itud_read_item_ts < post_contents_last_updated_ts' );
			}
			// Decide all Items are not read by not logged in user,
			// because we store read status only for logged in users:
		}

		// Get items which are flagged by current user:
		$this->FROM_add( 'INNER JOIN T_items__item_settings AS is_mustread ON '.$this->dbIDname.' = is_mustread.iset_item_ID
			AND is_mustread.iset_name = "mustread"
			AND is_mustread.iset_value = 1' );
	}


	/**
	 * Restrict to specific renderer plugins
	 *
	 * @param array Renderer plugin codes
	 */
	function where_renderers( $renderers )
	{
		global $DB, $Plugins;

		if( empty( $renderers ) )
		{	// No filters:
			return;
		}

		foreach( $renderers as $r => $renderer )
		{	// Escape chars:
			$renderers[ $r ] = $DB->escape( $renderer );
		}

		$sql_conditions = array();

		if( isset( $Plugins, $this->Blog ) )
		{	// Get default renderer plugins for current Collection:
			$default_renderers = $Plugins->validate_renderer_list( array( 'default' ), array(
				'setting_name' => 'coll_apply_rendering',
				'Blog'         => $this->Blog,
			) );
			if( ! empty( $default_renderers ) )
			{
				foreach( $renderers as $renderer )
				{
					if( in_array( $renderer, $default_renderers ) )
					{	// If at least one default renderer plugin is used to filter then get items which still use default renderers:
						$sql_conditions[] = $this->dbprefix.'renderers = "default"';
						break;
					}
				}
			}
		}

		// Filter by selected renderers:
		$sql_conditions[] = $this->dbprefix.'renderers REGEXP "(^|\.)('.implode( '|', $renderers ).')(\.|$)"';

		$this->WHERE_and( implode( ' OR ', $sql_conditions ) );
	}


	/**
	 * Generate order by clause
	 *
	 * @param $order_by
	 * @param $order_dir
	 */
	function gen_order_clause( $order_by, $order_dir, $dbprefix, $dbIDname )
	{
		global $DB;

		if( $order_by == 'mustread' )
		{	// Special ordering for "must read" items:
			$order_by = '';
			$order_dir = '';

			// 1) Order by item read status of current logged in User:
			//    1 - new pages
			//    2 - updated pages
			//    3 - already read pages
			if( is_logged_in() )
			{	// If user is logged in:
				global $current_User;
				$this->orderby_select .= 'IF( mustread_order.itud_read_item_ts IS NULL, 1, IF( mustread_order.itud_read_item_ts < post_contents_last_updated_ts, 2, 3 ) ) AS post_mustread';
				$this->orderby_from .= 'LEFT JOIN T_items__user_data AS mustread_order ON post_ID = mustread_order.itud_item_ID AND mustread_order.itud_user_ID = '.$DB->quote( $current_User->ID );
				$order_by = 'mustread';
				$order_dir = 'ASC';
			}

			// 2) Order by contents last updated DESC:
			$order_by .= ',contents_last_updated_ts';
			$order_dir .= ',DESC';

			// 3) Default orders of current collection:
			$order_by .= ','.get_blog_order( $this->Blog, 'field' );
			$order_dir .= ','.get_blog_order( $this->Blog, 'dir' );

			$order_by = trim( $order_by, ',' );
			$order_dir = trim( $order_dir, ',' );
		}

		$order_by = str_replace( ' ', ',', $order_by );
		$orderby_array = explode( ',', $order_by );

		if( in_array( 'numviews', $orderby_array ) )
		{	// Special case for numviews:
			$this->orderby_from .= ' LEFT JOIN ( SELECT itud_item_ID, COUNT(*) AS post_numviews FROM T_items__user_data GROUP BY itud_item_ID ) AS numviews
				ON post_ID = numviews.itud_item_ID ';
		}

		// asimo> $nullable_fields may be used if we would like to order the null values into the end of the result
		// asimo> It would move the null values into the end no matter what kind of direction are we sorting
		// Set of sort options which are nullable
		// $nullable_fields = array( 'order', 'priority' );

		$available_fields = get_available_sort_options( $this->blog, false, true );
		// Custom sort options
		$custom_sort_fields = $available_fields['custom'];

		foreach( $custom_sort_fields as $key => $field_name )
		{
			$table_alias = $key.'_table';
			$field_value = $table_alias.'.icfv_value';
			if( strpos( $key, 'custom_double' ) === 0 || strpos( $key, 'custom_computed' ) === 0 )
			{	// Double and computed values should be compared as numbers and not like strings
				$field_value .= '+0';
			}
			if( in_array( $key, $orderby_array ) )
			{
				if( empty( $this->orderby_from ) )
				{
					$this->orderby_from .= ' ';
				}
				// $nullable_fields[$key] = $field_value;
				$this->orderby_from .= 'LEFT JOIN T_items__item_custom_field as '.$table_alias.' ON post_ID = '.$table_alias.'.icfv_item_ID AND '
						.$table_alias.'.icfv_itcf_name = '.$DB->quote( $field_name );
				$order_by = str_replace( $key, $field_value, $order_by );
			}
			$custom_sort_fields[$key] = $field_value;
		}

		$available_fields = array_merge( array_keys( $available_fields['general'] ), array_values( $custom_sort_fields ) );
		// Extend general list to allow order posts by these fields as well for some special cases
		$available_fields[] = 'creator_user_ID';
		$available_fields[] = 'assigned_user_ID';
		$available_fields[] = 'pst_ID';
		$available_fields[] = 'datedeadline';
		$available_fields[] = 'ityp_ID';
		$available_fields[] = 'status';
		$available_fields[] = 'T_categories.cat_name';
		$available_fields[] = 'T_categories.cat_order';
		$available_fields[] = 'matched_tags_num'; // This is a virtual column only for order, real post_matched_tags_num doesn't exist in DB
		$available_fields[] = 'mustread'; // This is a virtual column only for order, real post_mustread doesn't exist in DB

		if( in_array( 'order', $orderby_array ) )
		{	// If list is ordered by field 'order':
			if( ( $order_i = array_search( 'order', $available_fields ) ) !== false )
			{	// Use an order per category instead of old field 'post_order':
				$available_fields[ $order_i ] = 'postcatsorders.postcat_order';
			}
			// Join table of categories for field 'postcat_order':
			$current_cat_ID = ( isset( $this->cat_array ) && count( $this->cat_array ) == 1 ? $DB->quote( $this->cat_array[0] ) : 'post_main_cat_ID' );
			$this->FROM_add( 'INNER JOIN T_postcats AS postcatsorders ON postcatsorders.postcat_post_ID = post_ID AND '.$current_cat_ID.' = postcatsorders.postcat_cat_ID' );
			// Join table to know collection of main category when list should be ordered by SUM of all extra categories from not main(cross-posted) collection:
			$this->FROM_add( 'INNER JOIN T_categories AS postmaincat ON post_main_cat_ID = postmaincat.cat_ID' );
			// Replace field to real name:
			$order_by = str_replace( 'order', 'postcatsorders.postcat_order', $order_by );
		}

		if( in_array( 'matched_tags_num', $orderby_array ) )
		{	// Special selection to order by highest number of matched tags:
			$this->orderby_from .= ' LEFT JOIN ( SELECT itag_itm_ID, COUNT(*) AS post_matched_tags_num FROM T_items__itemtag GROUP BY itag_itm_ID ) AS matched_tags_num
				ON post_ID = matched_tags_num.itag_itm_ID ';
		}

		$order_clause = gen_order_clause( $order_by, $order_dir, $dbprefix, $dbIDname, $available_fields );

		// asimo> The following commented code parts handles the nullable fields order, to move them NULL values into the end of the result
		// asimo> !!!Do NOT remove!!!
//		$orderby_fields = explode( ',', $order_clause );
//		foreach( $orderby_array as $index => $orderby_field )
//		{
//			$field_name = NULL;
//			$additional_clause = 0;
//			if( in_array( $orderby_field, $nullable_fields ) )
//			{ // This is an item nullable field
//				$field_name = $dbprefix.$orderby_field;
//			}
//			elseif( strpos( $orderby_field, 'custom_' ) === 0 )
//			{ // This is an item custom field which are always nullable
//				$field_name = $nullable_fields[$orderby_field];
//			}
//
//			if( empty( $field_name ) || ( strpos( $order_clause, $field_name ) === false ) )
//			{ // The field is not nullable or it is not present in the final order clause
//				continue;
//			}
//
//			// Insert 'order null values into the end' order clause
//			array_splice( $orderby_fields, $index + $additional_clause, 0, array( ' (CASE WHEN '.$field_name.' IS NULL then 1 ELSE 0 END)' ) );
//			$additional_clause++;
//		}
//		$order_clause = implode( ',', $orderby_fields );

		if( strpos( $this->itemtype_usage, '*featured*' ) !== false )
		{	// If we get featured posts together with other post types(like intro) then we should order featured posts below not featured posts:
			$order_clause = trim( 'post_featured, '.$order_clause, ', ' );
		}

		return $order_clause;
	}


	/**
	 * Get additional SELECT clause if it is required because of custom order_by fields
	 *
	 * @param string Before the SELECT clause
	 * @return string the SELECT clause to select the custom fields
	 */
	function get_orderby_select( $prefix = ', ' )
	{
		return empty( $this->orderby_select ) ? '' : $prefix.$this->orderby_select;
	}


	/**
	 * Get additional FROM clause if it is required because of custom order_by fields
	 *
	 * @param return before the FROM clause
	 * @return string the FROM clause to JOIN the custom fields tables
	 */
	function get_orderby_from( $prefix = '' )
	{
		return $prefix.$this->orderby_from;
	}
}

?>