<?php
/**
 * This file implements the UserQuery class.
 *
 * This file is part of the b2evolution/evocms project - {@link http://b2evolution.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/}.
*
 * @license http://b2evolution.net/about/license.html GNU General Public License (GPL)
 *
 * @package evocore
 */
if( !defined('EVO_MAIN_INIT') ) die( 'Please, do not access this page directly.' );

load_class( '_core/model/db/_filtersql.class.php', 'FilterSQL' );

/**
 * UserQuery: help constructing queries on Users
 * @package evocore
 */
class UserQuery extends FilterSQL
{
	/**
	 * Fields of users table to search by keywords
	 *
	 */
	var $keywords_fields = 'user_login, user_firstname, user_lastname, user_nickname';

	/**
	 * 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)
	 * @param array Query params
	 */
	function __construct( $dbtablename = 'T_users', $dbprefix = 'user_', $dbIDname = 'user_ID', $params = array() )
	{
		global $collections_Module;

		$this->dbtablename = $dbtablename;
		$this->dbprefix = $dbprefix;
		$this->dbIDname = $dbIDname;

		// Params to build query
		$params = array_merge( array(
				'join_group'       => true,
				'join_sec_groups'  => false,
				'join_session'     => false,
				'join_country'     => true,
				'join_region'      => false,
				'join_subregion'   => false,
				'join_city'        => true,
				'join_colls'       => true,
				'join_lists'       => false,
				'join_user_tags'   => false,
				'grouped'          => false,
			), $params );

		$this->SELECT( 'user_ID, user_login, user_nickname, user_lastname, user_firstname, user_gender, user_source, user_created_datetime, user_profileupdate_date, user_lastseen_ts, user_level, user_status, user_avatar_file_ID, user_email, user_url, user_birthday_year, user_birthday_month, user_birthday_day, user_age_min, user_age_max, user_pass, user_salt, user_pass_driver, user_locale, user_unsubscribe_key, user_reg_ctry_ID, user_ctry_ID, user_rgn_ID, user_subrg_ID, user_city_ID, user_grp_ID' );
		$this->SELECT_add( ', IF( user_avatar_file_ID IS NOT NULL, 1, 0 ) as has_picture' );
		$this->FROM( $this->dbtablename );

		if( $params['join_group'] )
		{ // Join Group
			$this->SELECT_add( ', grp_ID, grp_name, grp_level' );
			//$this->SELECT_add( ', ( SELECT COUNT( sug_count.sug_grp_ID ) FROM T_users__secondary_user_groups AS sug_count WHERE sug_count.sug_user_ID = user_ID ) AS secondary_groups_count' );
			$this->FROM_add( 'LEFT JOIN T_groups ON user_grp_ID = grp_ID' );
		}

		if( $params['join_sec_groups'] )
		{	// Join Secondary groups:
			$this->SELECT_add( ', COUNT( DISTINCT sug_count.sug_grp_ID ) AS secondary_groups_count' );
			$this->FROM_add( 'LEFT JOIN T_users__secondary_user_groups AS sug_count ON sug_count.sug_user_ID = user_ID' );
		}

		if( $params['join_session'] )
		{ // Join Session
			$this->SELECT_add( ', MAX(T_sessions.sess_lastseen_ts) as sess_lastseen' );
			$this->FROM_add( 'LEFT JOIN T_sessions ON user_ID = sess_user_ID' );
		}

		if( $params['join_country'] )
		{ // Join Country
			$this->SELECT_add( ', c.ctry_name, c.ctry_code, rc.ctry_name AS reg_ctry_name, rc.ctry_code AS reg_ctry_code' );
			$this->FROM_add( 'LEFT JOIN T_regional__country AS c ON user_ctry_ID = c.ctry_ID ' );
			$this->FROM_add( 'LEFT JOIN T_regional__country AS rc ON user_reg_ctry_ID = rc.ctry_ID ' );
		}

		if( $params['join_region'] )
		{	// Join Region:
			$this->SELECT_add( ', rgn_name' );
			$this->FROM_add( 'LEFT JOIN T_regional__region ON user_rgn_ID = rgn_ID ' );
		}

		if( $params['join_subregion'] )
		{	// Join Sub-region:
			$this->SELECT_add( ', subrg_name' );
			$this->FROM_add( 'LEFT JOIN T_regional__subregion ON user_subrg_ID = subrg_ID ' );
		}

		if( $params['join_city'] )
		{ // Join City
			$this->SELECT_add( ', city_name, city_postcode' );
			$this->FROM_add( 'LEFT JOIN T_regional__city ON user_city_ID = city_ID ' );
		}

		if( $params['join_colls'] )
		{	// Join a count of collections:
			if( isset( $collections_Module ) )
			{	// We are handling blogs:
				$this->SELECT_add( ', COUNT( DISTINCT blog_ID ) AS nb_blogs' );
				$this->FROM_add( 'LEFT JOIN T_blogs on user_ID = blog_owner_user_ID ' );
			}
			else
			{
				$this->SELECT_add( ', 0 AS nb_blogs' );
			}
		}

		if( $params['join_user_tags'] )
		{
			$this->SELECT_add( ', user_tags' );
			$this->FROM_add( 'LEFT JOIN (
						SELECT uutg_user_ID, GROUP_CONCAT( uutg_emtag_ID ) AS user_tags, COUNT(*) AS user_tag_count
						FROM T_users__usertag
						GROUP BY uutg_user_ID
					) AS user_tags ON user_tags.uutg_user_ID = user_ID' );
		}

		if( $params['join_lists'] )
		{ // subscribed_list contains comma-separated list of newsletter IDs, "negative" IDs are unsubscribed to newsletter lists
			$this->SELECT_add( ', subscribed_list' );
			$this->FROM_add( 'LEFT JOIN (
						SELECT enls_user_ID, GROUP_CONCAT( IF( enls_subscribed = 1, enls_enlt_ID, CONCAT( "-", enls_enlt_ID ) ) ) AS subscribed_list, COUNT(*) AS subscribed_list_count
						FROM T_email__newsletter_subscription
						GROUP BY enls_user_ID
					) AS subscribed_lists on subscribed_lists.enls_user_ID = user_ID' );
		}

		if( $params['grouped'] )
		{ // Group by user group
			$this->GROUP_BY( 'user_ID, grp_ID' );
			$this->ORDER_BY( 'grp_name, *, user_profileupdate_date DESC, user_lastseen_ts DESC, user_ID ASC' );
		}
		else
		{
			$this->GROUP_BY( 'user_ID' );
			$this->ORDER_BY( '*, user_profileupdate_date DESC, user_lastseen_ts DESC, user_ID ASC' );
		}
	}


	/**
	 * Restrict by user IDs
	 *
	 * @param array User IDs
	 */
	function where_user_IDs( $user_IDs )
	{
		global $DB;

		if( empty( $user_IDs ) )
		{	// Don't restrict:
			return;
		}

		$this->WHERE_and( 'user_ID IN ( '.$DB->quote( $user_IDs ).' ) ');
	}


	/**
	 * Restrict by members
	 *
	 * @param boolean TRUE to select only member of the current Blog
	 */
	function where_members( $members )
	{
		global $DB, $Collection, $Blog;

		if( empty( $members ) || is_admin_page() || empty( $Blog ) || $Blog->get_setting( 'allow_access' ) != 'members' )
		{ // Don't restrict
			return;
		}

		// Get blog owner
		$blogowner_SQL = new SQL();
		$blogowner_SQL->SELECT( 'user_ID' );
		$blogowner_SQL->FROM( 'T_users' );
		$blogowner_SQL->FROM_add( 'INNER JOIN T_blogs ON blog_owner_user_ID = user_ID' );
		$blogowner_SQL->WHERE( 'blog_ID = '.$DB->quote( $Blog->ID ) );

		// Calculate what users are members of the blog
		$userperms_SQL = new SQL();
		$userperms_SQL->SELECT( 'user_ID' );
		$userperms_SQL->FROM( 'T_users' );
		$userperms_SQL->FROM_add( 'INNER JOIN T_coll_user_perms ON ( bloguser_user_ID = user_ID AND bloguser_ismember = 1 )' );
		$userperms_SQL->WHERE( 'bloguser_blog_ID = '.$DB->quote( $Blog->ID ) );

		// Calculate what user groups are members of the blog
		$usergroups_SQL = new SQL();
		$usergroups_SQL->SELECT( 'user_ID' );
		$usergroups_SQL->FROM( 'T_users' );
		$usergroups_SQL->FROM_add( 'INNER JOIN T_groups ON grp_ID = user_grp_ID' );
		$usergroups_SQL->FROM_add( 'LEFT JOIN T_coll_group_perms ON ( bloggroup_ismember = 1
			AND ( bloggroup_group_ID = grp_ID
			      OR bloggroup_group_ID IN ( SELECT sug_grp_ID FROM T_users__secondary_user_groups WHERE sug_user_ID = user_ID ) ) )' );
		$usergroups_SQL->WHERE( 'bloggroup_blog_ID = '.$DB->quote( $Blog->ID ) );

		$members_count_sql = 'SELECT DISTINCT user_ID FROM ( '
			.$blogowner_SQL->get()
			.' UNION '
			.$userperms_SQL->get()
			.' UNION '
			.$usergroups_SQL->get().' ) members';

		$this->WHERE_and( 'user_ID IN ( '.$members_count_sql.' ) ');
	}


	/**
	 * Restrict with keywords
	 *
	 * @param string Keyword search string
	 */
	function where_keywords( $keywords, $search_kw_combine = 'AND' )
	{
		global $DB;

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

		$search = array();

		$kw_array = explode( ' ', $keywords );
		foreach( $kw_array as $kw )
		{
			// Note: we use CONCAT_WS (Concat With Separator) because CONCAT returns NULL if any arg is NULL
			$search[] = 'CONCAT_WS( " ", '.$this->keywords_fields.' ) LIKE "%'.$DB->escape($kw).'%"';
		}

		if( count( $search ) > 0 )
		{
			$this->WHERE_and( implode( ' '.$search_kw_combine.' ', $search ) );
		}
	}


	/**
	 * Restrict with email
	 *
	 * @param string Email
	 */
	function where_email( $email )
	{
		global $DB;

		if( $email !== '' )
		{	// Filter only by not empty email address:
			$this->WHERE_and( 'user_email LIKE '.$DB->quote( '%'.$email.'%' ) );
		}
	}


	/**
	 * Restrict with gender
	 *
	 * @param string Gender ( M, F, O, MF, MO, FO, MFO )
	 */
	function where_gender( $gender )
	{
		if( empty( $gender ) )
		{
			return;
		}

		switch( $gender )
		{
			case 'MF':
			case 'MO':
			case 'FO':
			case 'MFO':
				$this->add_filter_rule( 'gender', str_split( $gender ), NULL, 'OR' );
				break;
			case 'M':
			case 'F':
			case 'O':
				$this->add_filter_rule( 'gender', $gender );
				break;
		}
	}


	/**
	 * Restrict to user status, currently activated also means auto and manually activated users
	 *
	 * @param string user status ( 'activated', 'manualactivated', 'autoactivated', 'closed', 'deactivated', 'emailchanged', 'failedactivation', 'pendingdelete', 'new' )
	 * @param boolean set true to include users only with the given status, or set false to exclude users with the given status
	 * @param boolean set true to make exact comparing with selected status
	 */
	function where_status( $status, $include = true, $exactly = false )
	{
		if( empty( $status ) )
		{
			return;
		}

		if( ( $status == 'activated' || $status === 1 ) && !$exactly )
		{	// Activated, Manually activated, Autoactivated users:
			$this->add_filter_rule( 'status', array( 'activated', 'autoactivated', 'manualactivated' ), '=', 'OR' );
		}
		else
		{ // Other status check
			// init compare, which depends if we want to include or exclude users with the given status
			$this->add_filter_rule( 'status', $status, $include ? '=' : '<>' );
		}
	}


	/**
	 * Restrict to user registration date
	 *
	 * @param date Registration from date
	 * @param date Registration to date
	 */
	function where_registered_date( $min_date = NULL, $max_date = NULL )
	{
		if( ! empty( $min_date ) && ! empty( $max_date ) )
		{
			$this->add_filter_rule( 'regdate', array( $min_date, $max_date ), 'between', NULL, 'date' );
		}
		elseif( ! empty( $min_date ) )
		{
			$this->add_filter_rule( 'regdate', $min_date, '>=', NULL, 'date' );
		}
		elseif( ! empty( $max_date ) )
		{
			$this->add_filter_rule( 'regdate', $max_date, '<=', NULL, 'date' );
		}
	}


	/**
	 * Restrict to reported users
	 *
	 * @param boolean is reported
	 */
	function where_reported( $reported )
	{
		if( ! empty( $reported ) )
		{
			$this->add_filter_rule( 'report_count', 1, '>=' );
		}
	}


	/**
	 * Restrict to users with custom notifcation sender settings
	 *
	 * @param boolean with custom sender email
	 * @param boolean with custom sender name
	 */
	function where_custom_sender( $custom_sender_email, $custom_sender_name )
	{
		global $DB, $Settings;

		if( $custom_sender_email )
		{	// Restrict to users with custom notification sender email address:
			$this->add_filter_rule( 'custom_sender_email', 'yes' );
		}

		if( $custom_sender_name )
		{	// Restrict to users with custom notification sender name:
			$this->add_filter_rule( 'custom_sender_name', 'yes' );
		}
	}


	/**
	 * Restrict to users with tag
	 *
	 * @param string User should have all of these tags
	 * @param string User should not have any of these tags
	 */
	function where_tag( $user_tag = NULL, $not_user_tag = NULL)
	{
		if( trim( $user_tag ) !== '' )
		{
			$this->add_filter_rule( 'tags', $user_tag, 'user_tagged' );
		}
		if( trim( $not_user_tag ) !== '' )
		{
			$this->add_filter_rule( 'tags', $not_user_tag, 'user_not_tagged' );
		}
	}


	/**
	 * Restrict with primary user group
	 *
	 * @param integer Primary user group ID
	 */
	function where_group( $group_ID )
	{
		$group_ID = (int)$group_ID;

		if( $group_ID < 1 )
		{ // Group Id may be '0' - to show all groups, '-1' - to show all groups as ungrouped list
			return;
		}

		$this->add_filter_rule( 'group', $group_ID );
	}


	/**
	 * Restrict with secondary user group
	 *
	 * @param integer Secondary user group ID
	 */
	function where_secondary_group( $secondary_group_ID )
	{
		$secondary_group_ID = intval( $secondary_group_ID );

		if( $secondary_group_ID < 1 )
		{	// Group ID may be '0' - to show all groups
			return;
		}

		$this->add_filter_rule( 'group2', $secondary_group_ID );
	}


	/**
	 * Restrict with location (Country | Region | Subregion | City)
	 *
	 * @param string Field name of location (ctry | rgn | subrg | city)
	 * @param integer Location ID
	 */
	function where_location( $location, $ID )
	{
		global $DB;

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

		$this->WHERE_and( 'user_'.$location.'_ID = '.$DB->quote( $ID ) );
	}


	/**
	 * Restrict with age group
	 *
	 * @param integer Age min
	 * @param integer Age max
	 */
	function where_age_group( $age_min, $age_max )
	{
		global $DB;

		$sql_age = array();

		if( $age_min > 0 )
		{	// search_min_value BETWEEN user_age_min AND user_age_max
			$sql_age[] = '( '.$DB->quote( $age_min ).' >= user_age_min AND '.$DB->quote( $age_min ).' <= user_age_max )';
		}

		if( $age_max > 0 )
		{	// search_max_value BETWEEN user_age_min AND user_age_max
			$sql_age[] = '( '.$DB->quote( $age_max ).' >= user_age_min AND '.$DB->quote( $age_max ).' <= user_age_max )';
		}

		if( count( $sql_age ) > 0 )
		{
			$this->WHERE_and( implode( ' OR ', $sql_age ) );
		}
	}


	/**
	 * Restrict with user group level
	 *
	 * @param integer Minimum group level
	 * @param integer Maximum group level
	 */
	function where_group_level( $group_level_min, $group_level_max )
	{
		global $DB;

		if( $group_level_min < 0 )
		{ // Min group level is 0
			$group_level_min = 0;
		}

		if( $group_level_max > 10 )
		{ // Max group level is 10
			$group_level_max = 10;
		}

		$this->WHERE_and( 'grp_level >= '.$DB->quote( $group_level_min ) );
		$this->WHERE_and( 'grp_level <= '.$DB->quote( $group_level_max ) );
	}


	/**
	 * Select by organization ID
	 *
	 * @param integer Organization ID
	 */
	function where_organization( $org_ID )
	{
		global $DB;

		$org_ID = intval( $org_ID );

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

		$this->add_filter_rule( 'org', $org_ID );
	}


	/**
	 * Select by newsletter ID
	 *
	 * @param integer Newsletter ID
	 * @param boolean|NULL TRUE - only users with active subscription, FALSE - only unsubscribed users, NULL - both
	 */
	function where_newsletter( $newsletter_ID, $is_subscribed = true )
	{
		$newsletter_ID = intval( $newsletter_ID );

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

		$this->add_filter_rule( 'newsletter', array( $newsletter_ID, $is_subscribed ) );
	}


	/**
	 * Select by not subscribed newsletter ID
	 *
	 * @param integer Newsletter ID
	 */
	function where_not_newsletter( $not_newsletter_ID )
	{
		$not_newsletter_ID = intval( $not_newsletter_ID );

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

		$this->add_filter_rule( 'newsletter', $not_newsletter_ID, '!=' );
	}


	/**
	 * Select by Email Campaign ID
	 *
	 * @param integer Email Campaign ID
	 * @param string Recipient type of email campaign: 'filter', 'receive', 'wait'
	 */
	function where_email_campaign( $ecmp_ID, $recipient_type = '', $recipient_action = '' )
	{
		global $DB;

		$ecmp_ID = intval( $ecmp_ID );

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

		$this->SELECT_add( ', csnd_status, csnd_emlog_ID' );
		$this->FROM_add( 'INNER JOIN T_email__campaign_send ON csnd_user_ID = user_ID AND csnd_camp_ID = '.$DB->quote( $ecmp_ID ) );

		// Get email log date and time:
		$this->SELECT_add( ', csnd_last_sent_ts, enls_user_ID, csnd_last_open_ts, csnd_last_click_ts, csnd_like, csnd_cta1, csnd_cta2, csnd_cta3' );

		// Get subscription status:
		$this->SELECT_add( ', enls_subscribed' );
		$this->FROM_add( 'LEFT JOIN T_email__campaign ON ecmp_ID = csnd_camp_ID' );
		$this->FROM_add( 'LEFT JOIN T_email__newsletter_subscription ON enls_enlt_ID = ecmp_enlt_ID AND enls_user_ID = user_ID AND enls_subscribed = 1' );

		// Get email address status:
		$this->SELECT_add( ', emadr_status, emadr_ID' );
		$this->FROM_add( 'LEFT JOIN T_email__address ON user_email = emadr_address' );

		switch( $recipient_type )
		{
			case 'ready_to_send':
				// Get recipients which are ready to receive the email campaign:
				$this->WHERE_and( 'csnd_status IN ( "ready_to_send", "ready_to_resend" )' );
				break;

			case 'send_error':
				// Get recipients which had error on receiving the email campaign
				// or if their email address has a blocked status("Permanent error" or "Spammer"):
				$this->WHERE_and( 'csnd_status = "send_error" OR '.get_mail_blocked_condition() );
				break;

			case 'sent':
			case 'skipped':
				// Get recipients which are skipped or have already received this newsletter:
				$this->WHERE_and( 'csnd_status = '.$DB->quote( $recipient_type ) );
				break;
		}

		switch( $recipient_action )
		{
			case 'img_loaded':
				$this->WHERE_and( 'csnd_last_open_ts IS NOT NULL' );
				break;

			case 'link_clicked':
				$this->WHERE_and( 'csnd_last_click_ts IS NOT NULL' );
				break;

			case 'cta1':
				$this->WHERE_and( 'csnd_cta1 = 1' );
				break;

			case 'cta2':
				$this->WHERE_and( 'csnd_cta2 = 1' );
				break;

			case 'cta3':
				$this->WHERE_and( 'csnd_cta3 = 1' );
				break;

			case 'liked':
				$this->WHERE_and( 'csnd_like = 1' );
				break;

			case 'disliked':
				$this->WHERE_and( 'csnd_like = -1' );
				break;

			case 'clicked_unsubscribe':
				$this->WHERE_and( 'csnd_clicked_unsubscribe = 1' );
				break;
		}
	}


	/**
	 * Select by viewed user
	 *
	 * @param integer User ID
	 */
	function where_viewed_user( $user_ID )
	{
		global $DB;
		$this->SELECT_add( ', upv_visited_user_ID, upv_visitor_user_ID, upv_last_visit_ts' );
		$this->FROM_add( 'RIGHT JOIN T_users__profile_visits ON upv_visitor_user_ID = user_ID AND upv_visited_user_ID = '.$DB->quote( $user_ID ) );
	}


	/**
	 * Select by registration IP range
	 *
	 * @param string Min IP address
	 * @param string Max IP address
	 */
	function where_reg_ip( $reg_ip_min, $reg_ip_max )
	{
		global $DB;

		$reg_ip_min = ip2int( $reg_ip_min );
		$reg_ip_max = ip2int( $reg_ip_max );

		if( empty( $reg_ip_min ) && empty( $reg_ip_max ) )
		{	// No IP filters:
			return;
		}

		// Join User settings table:
		$this->FROM_add( 'INNER JOIN T_users__usersettings
			 ON uset_user_ID = user_ID
			AND uset_name = "created_fromIPv4"' );

		if( ! empty( $reg_ip_min ) )
		{	// Restrict with MIN registration IP address:
			$this->WHERE_and( 'uset_value >= '.$DB->quote( $reg_ip_min ) );
		}

		if( ! empty( $reg_ip_max ) )
		{	// Restrict with MAX registration IP address:
			$this->WHERE_and( 'uset_value <= '.$DB->quote( $reg_ip_max ) );
		}
	}


	/**
	 * Restrict with user group level
	 *
	 * @param integer Minimum user level
	 * @param integer Maximum user level
	 */
	function where_level( $user_level_min, $user_level_max )
	{
		if( $user_level_min < 0 || is_null($user_level_min) )
		{ // Min group level is 0
			$user_level_min = 0;
		}

		if( $user_level_max > 10 || is_null($user_level_max) )
		{ // Max group level is 10
			$user_level_max = 10;
		}

		if( $user_level_min > 0 || $user_level_max < 10 )
		{	// Filter only with actual values:
			$this->add_filter_rule( 'level', array( $user_level_min, $user_level_max ), 'between' );
		}
	}


	/**
	 * Restrict to users with duplicate emails
	 */
	function where_duplicate_email()
	{
		$this->SELECT_add( ', email_user_count' );
		$this->FROM_add( 'LEFT JOIN ( SELECT user_email AS dup_email, COUNT(*) AS email_user_count FROM T_users GROUP BY user_email ) AS dup_emails ON dup_emails.dup_email = T_users.user_email' );
		$this->WHERE_and( 'email_user_count > 1' );
	}


	/**
	 * Restrict with user name or email
	 *
	 * @param string Value
	 * @param string Operator
	 */
	function filter_field_name_email( $value, $operator )
	{
		return $this->get_where_condition( 'CONCAT_WS( " ", user_login, user_email, user_firstname, user_lastname, user_nickname )', $value, $operator );
	}


	/**
	 * Restrict with user first name
	 *
	 * @param string Value
	 * @param string Operator
	 */
	function filter_field_firstname( $value, $operator )
	{
		return $this->get_where_condition( 'user_firstname', $value, $operator );
	}


	/**
	 * Restrict with user last name
	 *
	 * @param string Value
	 * @param string Operator
	 */
	function filter_field_lastname( $value, $operator )
	{
		return $this->get_where_condition( 'user_lastname', $value, $operator );
	}


	/**
	 * Restrict with user nickname
	 *
	 * @param string Value
	 * @param string Operator
	 */
	function filter_field_nickname( $value, $operator )
	{
		return $this->get_where_condition( 'user_nickname', $value, $operator );
	}


	/**
	 * Restrict with user email
	 *
	 * @param string Value
	 * @param string Operator
	 */
	function filter_field_email( $value, $operator )
	{
		return $this->get_where_condition( 'user_email', $value, $operator );
	}


	/**
	 * Restrict with user gender
	 *
	 * @param string Value
	 * @param string Operator
	 */
	function filter_field_gender( $value, $operator )
	{
		if( in_array( $value, array( 'M', 'F', 'O' ) ) )
		{
			return $this->get_where_condition( 'user_gender', $value, $operator );
		}
	}


	/**
	 * Restrict with user country
	 *
	 * @param string Value
	 * @param string Operator
	 */
	function filter_field_country( $value, $operator )
	{
		if( ! empty( $value ) )
		{
			return $this->get_where_condition( 'user_ctry_ID', $value, $operator );
		}
	}


	/**
	 * Restrict with user level
	 *
	 * @param string Value
	 * @param string Operator
	 */
	function filter_field_level( $value, $operator )
	{
		return $this->get_where_condition( 'user_level', $value, $operator );
	}


	/**
	 * Restrict with user organization
	 *
	 * @param string Value
	 * @param string Operator
	 */
	function filter_field_org( $value, $operator )
	{
		if( $operator == 'equal' || $operator == 'not_equal' )
		{	// If operator is allowed for this filter:
			global $DB;
			$this->SELECT_add( ', uorg_org_ID, uorg_accepted, uorg_role, uorg_priority' );
			$this->FROM_add( 'LEFT JOIN T_users__user_org ON uorg_user_ID = user_ID' );
			return '( SELECT uorg_org_ID FROM T_users__user_org WHERE uorg_user_ID = user_ID AND uorg_org_ID = '.$DB->quote( $value ).' ) '.( $operator == 'equal' ? 'IS NOT NULL' : 'IS NULL' );
		}
	}


	/**
	 * Restrict if user uses custom sender email address
	 *
	 * @param string Value
	 * @param string Operator
	 */
	function filter_field_custom_sender_email( $value, $operator )
	{
		if( $value == 'yes' || $value == 'no' )
		{	// If value is allowed for this filter:
			global $Settings, $DB;
			$this->FROM_add( 'LEFT JOIN T_users__usersettings as custom_sender_email ON custom_sender_email.uset_user_ID = user_ID AND custom_sender_email.uset_name = "notification_sender_email"' );
			$operator1 = ( $value == 'yes' ? 'IS NOT NULL AND' : 'IS NULL OR' );
			$operator2 = ( $value == 'yes' ? '<>' : '=' );
			return 'custom_sender_email.uset_value '.$operator1.' custom_sender_email.uset_value '.$operator2.' '.$DB->quote( $Settings->get( 'notification_sender_email' ) );
		}
	}


	/**
	 * Restrict if user uses custom sender name
	 *
	 * @param string Value
	 * @param string Operator
	 */
	function filter_field_custom_sender_name( $value, $operator )
	{
		if( $value == 'yes' || $value == 'no' )
		{	// If value is allowed for this filter:
			global $Settings, $DB;
			$this->FROM_add( 'LEFT JOIN T_users__usersettings as custom_sender_name ON custom_sender_name.uset_user_ID = user_ID AND custom_sender_name.uset_name = "notification_sender_name"' );
			$operator1 = ( $value == 'yes' ? 'IS NOT NULL AND' : 'IS NULL OR' );
			$operator2 = ( $value == 'yes' ? '<>' : '=' );
			return 'custom_sender_name.uset_value '.$operator1.' custom_sender_name.uset_value '.$operator2.' '.$DB->quote( $Settings->get( 'notification_sender_name' ) );
		}
	}


	/**
	 * Restrict with user fields (Specific criteria)
	 *
	 * @param string Value
	 * @param string Operator
	 */
	function filter_field_criteria( $value, $operator )
	{
		if( ! preg_match( '#^(\d+):(contains|not_contains):(.+)$#', $value, $m ) )
		{	// Skip wrong value:
			return;
		}

		$user_field_def_ID = intval( $m[1] );
		$user_field_operator = trim( strip_tags( $m[2] ) );
		$user_field_value = trim( strip_tags( $m[3] ) );
		if( $user_field_def_ID <= 0 || $user_field_value == '' || $user_field_operator == '' )
		{	// Skip wrong value:
			return;
		}

		global $DB;

		switch( $user_field_operator )
		{
			case 'contains':
				$word_operator = 'LIKE';
				$field_condition_start = 'uf_ufdf_ID = '.$DB->quote( $user_field_def_ID );
				$field_condition_end = '';
				break;

			case 'not_contains':
				$word_operator = 'NOT LIKE';
				$field_condition_start = '( uf_ufdf_ID = '.$DB->quote( $user_field_def_ID );
				// This condition selects users which have no the requested field in DB, i.e. thier requested field has no the requested value:
				$field_condition_end = ' ) OR ( SELECT COUNT( uf_ID ) FROM yb_users__fields WHERE uf_user_ID = user_ID AND uf_ufdf_ID = '.$DB->quote( $user_field_def_ID ).' ) = 0';
				break;

			default:
				debug_die( 'Unknown operator "'.$user_field_operator.'" for user searching by specific criteria' );
		}

		$word_sql_conditions = array();
		$words = explode( ' ', $user_field_value );
		foreach( $words as $word )
		{	// Find each word separately:
			$word_sql_conditions[] = 'uf_varchar '.$word_operator.' '.$DB->quote( '%'.$word.'%' );
		}

		// Join table for columns uf_ufdf_ID and uf_varchar:
		$this->FROM_add( 'LEFT JOIN T_users__fields ON uf_user_ID = user_ID' );

		// Build SQL condition for specific criteria:
		$criteria_sql_condition = '( '.$field_condition_start.' AND ';
		if( count( $word_sql_conditions ) > 1 )
		{
			$criteria_sql_condition .= '( '.implode( ' OR ', $word_sql_conditions ).' )';
		}
		else
		{
			$criteria_sql_condition .= $word_sql_conditions[0];
		}
		$criteria_sql_condition .= $field_condition_end.' )';

		return $criteria_sql_condition;
	}


	/**
	 * Restrict with user last seen date
	 *
	 * @param string Value
	 * @param string Operator
	 */
	function filter_field_lastseen( $value, $operator )
	{
		if( ! empty( $value ) )
		{
			return $this->get_where_condition( 'DATE( IFNULL( user_lastseen_ts, "1970-01-01" ) )', $value, $operator );
		}
	}


	/**
	 * Restrict with user last seen date
	 *
	 * @param string Value
	 * @param string Operator
	 */
	function filter_field_source( $value, $operator )
	{
		return $this->get_where_condition( 'user_source', $value, $operator );
	}


	/**
	 * Restrict with user report count
	 *
	 * @param string Value
	 * @param string Operator
	 */
	function filter_field_report_count( $value, $operator )
	{
		$value = intval( $value );

		if( $value > 0 && $operator == 'greater_or_equal' )
		{
			$this->SELECT_add( ', user_rep' );
			$this->FROM_add( 'LEFT JOIN ( SELECT urep_target_user_ID, COUNT( DISTINCT urep_reporter_ID ) AS user_rep FROM T_users__reports GROUP BY urep_target_user_ID ) AS urep ON urep.urep_target_user_ID = user_ID' );

			return $this->get_where_condition( 'user_rep', $value, $operator );
		}
	}


	/**
	 * Restrict with user primary group
	 *
	 * @param string Value
	 * @param string Operator
	 */
	function filter_field_group( $value, $operator )
	{
		$value = intval( $value );

		if( $value > 0 && ( $operator == 'equal' || $operator == 'not_equal' ) )
		{
			return $this->get_where_condition( 'user_grp_ID', $value, $operator );
		}
	}


	/**
	 * Restrict with user secondary group
	 *
	 * @param string Value
	 * @param string Operator
	 */
	function filter_field_group2( $value, $operator )
	{
		$value = intval( $value );

		if( $value > 0 && ( $operator == 'equal' || $operator == 'not_equal' ) )
		{	// If value and operator are allowed for this filter:
			global $DB;
			$this->FROM_add( 'LEFT JOIN T_users__secondary_user_groups AS sug_filter ON sug_filter.sug_user_ID = user_ID' );
			return '( SELECT sug_grp_ID FROM T_users__secondary_user_groups WHERE sug_user_ID = user_ID AND sug_grp_ID = '.$DB->quote( $value ).' ) '.( $operator == 'equal' ? 'IS NOT NULL' : 'IS NULL' );
		}
	}


	/**
	 * Restrict with user account status
	 *
	 * @param string Value
	 * @param string Operator
	 */
	function filter_field_status( $value, $operator )
	{
		return $this->get_where_condition( 'user_status', $value, $operator );
	}


	/**
	 * Restrict with user registration date
	 *
	 * @param string Value
	 * @param string Operator
	 */
	function filter_field_regdate( $value, $operator )
	{
		if( ! empty( $value ) )
		{
			return $this->get_where_condition( 'DATE( user_created_datetime )', $value, $operator );
		}
	}


	/**
	 * Restrict with user newsletter
	 *
	 * @param string Value
	 * @param string Operator
	 */
	function filter_field_newsletter( $value, $operator )
	{
		if( is_array( $value ) && count( $value ) == 2 )
		{	// Special case for additional param to also get user which will be unsubscribed:
			$is_subscribed = $value[1];
			$value = intval( $value[0] );
		}
		else
		{	// Get only subscribed users:
			$is_subscribed = 1;
			$value = intval( $value );
		}

		if( $value > 0 && ( $operator == 'equal' || $operator == 'not_equal' ) )
		{	// If value and operator are allowed for this filter:
			$restrict_is_subscribed = '';
			if( $is_subscribed !== NULL )
			{	// Get only subscribed or unsubscribed users:
				$restrict_is_subscribed = ' AND enls_subscribed = '.( $is_subscribed ? '1' : '0' );
			}

			global $DB;
			$this->SELECT_add( ', enls_last_sent_manual_ts, enls_last_sent_auto_ts, enls_last_open_ts, enls_last_click_ts, enls_send_count, enls_subscribed, enls_subscribed_ts, enls_unsubscribed_ts, enls_enlt_ID' );
			$this->FROM_add( 'INNER JOIN T_email__newsletter_subscription ON enls_user_ID = user_ID'.( $is_subscribed === NULL ? ' AND enls_enlt_ID = '.$DB->quote( $value ) : '' ) );

			return '( SELECT enls_enlt_ID FROM T_email__newsletter_subscription WHERE enls_user_ID = user_ID AND enls_enlt_ID = '.$DB->quote( $value ).$restrict_is_subscribed.' ) '.( $operator == 'equal' ? 'IS NOT NULL' : 'IS NULL' );
		}
	}


	/**
	 * Restrict with user tags
	 *
	 * @param string Value
	 * @param string Operator
	 */
	function filter_field_tags( $value, $operator )
	{
		$tags = array_unique( array_map( 'trim', explode( ',', $value ) ) );

		foreach( $tags as $t => $tag )
		{
			if( $tag === '' )
			{	// Remove empty tags:
				unset( $tags[ $t ] );
			}
		}

		if( count( $tags ) > 0 && ( $operator == 'user_tagged' || $operator == 'user_not_tagged' ) )
		{	// If value and operator are allowed for this filter:
			global $DB;
			return '( SELECT COUNT( uutg_emtag_ID )
				 FROM T_users__tag
				 INNER JOIN T_users__usertag ON uutg_emtag_ID = utag_ID
				WHERE uutg_user_ID = user_ID
				  AND utag_name IN ( '.$DB->quote( $tags ).' )
				) '.( $operator == 'user_tagged' ? '= '.count( $tags ) : '= 0' );
		}
	}
}

?>
