/**

*

* @package phpBB3

* @version $Id: functions_user.php 8949 2008-09-26 21:29:05Z toonarmy $

* @copyright (c) 2005 phpBB Group

* @license http://opensource.org/licenses/gpl-license.php GNU Public License

*

*/

/**

* @ignore

*/

if (!defined('IN_PHPBB'))

{

	exit;

}

/**

* Obtain user_ids from usernames or vice versa. Returns false on

* success else the error string

*

* @param array &$user_id_ary The user ids to check or empty if usernames used

* @param array &$username_ary The usernames to check or empty if user ids used

* @param mixed $user_type Array of user types to check, false if not restricting by user type

*/

function user_get_id_name(&$user_id_ary, &$username_ary, $user_type = false)

{

	global $db;

	// Are both arrays already filled? Yep, return else

	// are neither array filled?

	if ($user_id_ary && $username_ary)

	{

		return false;

	}

	else if (!$user_id_ary && !$username_ary)

	{

		return 'NO_USERS';

	}

	$which_ary = ($user_id_ary) ? 'user_id_ary' : 'username_ary';

	if ($$which_ary && !is_array($$which_ary))

	{

		$$which_ary = array($$which_ary);

	}

	$sql_in = ($which_ary == 'user_id_ary') ? array_map('intval', $$which_ary) : array_map('utf8_clean_string', $$which_ary);

	unset($$which_ary);

	$user_id_ary = $username_ary = array();

	// Grab the user id/username records

	$sql_where = ($which_ary == 'user_id_ary') ? 'user_id' : 'username_clean';

	$sql = 'SELECT user_id, username

		FROM ' . USERS_TABLE . '

		WHERE ' . $db->sql_in_set($sql_where, $sql_in);

	if ($user_type !== false && !empty($user_type))

	{

		$sql .= ' AND ' . $db->sql_in_set('user_type', $user_type);

	}

	$result = $db->sql_query($sql);

	if (!($row = $db->sql_fetchrow($result)))

	{

		$db->sql_freeresult($result);

		return 'NO_USERS';

	}

	do

	{

		$username_ary[$row['user_id']] = $row['username'];

		$user_id_ary[] = $row['user_id'];

	}

	while ($row = $db->sql_fetchrow($result));

	$db->sql_freeresult($result);

	return false;

}

/**

* Get latest registered username and update database to reflect it

*/

function update_last_username()

{

	global $db;

	// Get latest username

	$sql = 'SELECT user_id, username, user_colour

		FROM ' . USERS_TABLE . '

		WHERE user_type IN (' . USER_NORMAL . ', ' . USER_FOUNDER . ')

		ORDER BY user_id DESC';

	$result = $db->sql_query_limit($sql, 1);

	$row = $db->sql_fetchrow($result);

	$db->sql_freeresult($result);

	if ($row)

	{

		set_config('newest_user_id', $row['user_id'], true);

		set_config('newest_username', $row['username'], true);

		set_config('newest_user_colour', $row['user_colour'], true);

	}

}

/**

* Updates a username across all relevant tables/fields

*

* @param string $old_name the old/current username

* @param string $new_name the new username

*/

function user_update_name($old_name, $new_name)

{

	global $config, $db, $cache;

	$update_ary = array(

		FORUMS_TABLE			=> array('forum_last_poster_name'),

		MODERATOR_CACHE_TABLE	=> array('username'),

		POSTS_TABLE				=> array('post_username'),

		TOPICS_TABLE			=> array('topic_first_poster_name', 'topic_last_poster_name'),

	);

	foreach ($update_ary as $table => $field_ary)

	{

		foreach ($field_ary as $field)

		{

			$sql = "UPDATE $table

				SET $field = '" . $db->sql_escape($new_name) . "'

				WHERE $field = '" . $db->sql_escape($old_name) . "'";

			$db->sql_query($sql);

		}

	}

	if ($config['newest_username'] == $old_name)

	{

		set_config('newest_username', $new_name, true);

	}

	// Because some tables/caches use username-specific data we need to purge this here.

	$cache->destroy('sql', MODERATOR_CACHE_TABLE);

}

/**

* Adds an user

*

* @param mixed $user_row An array containing the following keys (and the appropriate values): username, group_id (the group to place the user in), user_email and the user_type(usually 0). Additional entries not overridden by defaults will be forwarded.

* @param string $cp_data custom profile fields, see custom_profile::build_insert_sql_array

* @return the new user's ID.

*/

function user_add($user_row, $cp_data = false)

{

	global $db, $user, $auth, $config, $phpbb_root_path, $phpEx;

	if (empty($user_row['username']) || !isset($user_row['group_id']) || !isset($user_row['user_email']) || !isset($user_row['user_type']))

	{

		return false;

	}

	$username_clean = utf8_clean_string($user_row['username']);

	if (empty($username_clean))

	{

		return false;

	}

	$sql_ary = array(

		'username'			=> $user_row['username'],

		'username_clean'	=> $username_clean,

		'user_password'		=> (isset($user_row['user_password'])) ? $user_row['user_password'] : '',

		'user_pass_convert'	=> 0,

		'user_email'		=> strtolower($user_row['user_email']),

		'user_email_hash'	=> crc32(strtolower($user_row['user_email'])) . strlen($user_row['user_email']),

		'group_id'			=> $user_row['group_id'],

		'user_type'			=> $user_row['user_type'],

	);

	// These are the additional vars able to be specified

	$additional_vars = array(

		'user_permissions'	=> '',

		'user_timezone'		=> $config['board_timezone'],

		'user_dateformat'	=> $config['default_dateformat'],

		'user_lang'			=> $config['default_lang'],

		'user_style'		=> (int) $config['default_style'],

		'user_actkey'		=> '',

		'user_ip'			=> '',

		'user_regdate'		=> time(),

		'user_passchg'		=> time(),

		'user_options'		=> 895,

		'user_inactive_reason'	=> 0,

		'user_inactive_time'	=> 0,

		'user_lastmark'			=> time(),

		'user_lastvisit'		=> 0,

		'user_lastpost_time'	=> 0,

		'user_lastpage'			=> '',

		'user_posts'			=> 0,

		'user_dst'				=> (int) $config['board_dst'],

		'user_colour'			=> '',

		'user_occ'				=> '',

		'user_interests'		=> '',

		'user_avatar'			=> '',

		'user_avatar_type'		=> 0,

		'user_avatar_width'		=> 0,

		'user_avatar_height'	=> 0,

		'user_new_privmsg'		=> 0,

		'user_unread_privmsg'	=> 0,

		'user_last_privmsg'		=> 0,

		'user_message_rules'	=> 0,

		'user_full_folder'		=> PRIVMSGS_NO_BOX,

		'user_emailtime'		=> 0,

		'user_notify'			=> 0,

		'user_notify_pm'		=> 1,

		'user_notify_type'		=> NOTIFY_EMAIL,

		'user_allow_pm'			=> 1,

		'user_allow_viewonline'	=> 1,

		'user_allow_viewemail'	=> 1,

		'user_allow_massemail'	=> 1,

		'user_sig'					=> '',

		'user_sig_bbcode_uid'		=> '',

		'user_sig_bbcode_bitfield'	=> '',

		'user_form_salt'			=> unique_id(),

	);

	// Now fill the sql array with not required variables

	foreach ($additional_vars as $key => $default_value)

	{

		$sql_ary[$key] = (isset($user_row[$key])) ? $user_row[$key] : $default_value;

	}

	// Any additional variables in $user_row not covered above?

	$remaining_vars = array_diff(array_keys($user_row), array_keys($sql_ary));

	// Now fill our sql array with the remaining vars

	if (sizeof($remaining_vars))

	{

		foreach ($remaining_vars as $key)

		{

			$sql_ary[$key] = $user_row[$key];

		}

	}

	$sql = 'INSERT INTO ' . USERS_TABLE . ' ' . $db->sql_build_array('INSERT', $sql_ary);

	$db->sql_query($sql);

	$user_id = $db->sql_nextid();

	// Insert Custom Profile Fields

	if ($cp_data !== false && sizeof($cp_data))

	{

		$cp_data['user_id'] = (int) $user_id;

		if (!class_exists('custom_profile'))

		{

			include_once($phpbb_root_path . 'includes/functions_profile_fields.' . $phpEx);

		}

		$sql = 'INSERT INTO ' . PROFILE_FIELDS_DATA_TABLE . ' ' .

			$db->sql_build_array('INSERT', custom_profile::build_insert_sql_array($cp_data));

		$db->sql_query($sql);

	}

	// Place into appropriate group...

	$sql = 'INSERT INTO ' . USER_GROUP_TABLE . ' ' . $db->sql_build_array('INSERT', array(

		'user_id'		=> (int) $user_id,

		'group_id'		=> (int) $user_row['group_id'],

		'user_pending'	=> 0)

	);

	$db->sql_query($sql);

	// Now make it the users default group...

	group_set_user_default($user_row['group_id'], array($user_id), false);

	// set the newest user and adjust the user count if the user is a normal user and no activation mail is sent

	if ($user_row['user_type'] == USER_NORMAL)

	{

		set_config('newest_user_id', $user_id, true);

		set_config('newest_username', $user_row['username'], true);

		set_config('num_users', $config['num_users'] + 1, true);

		$sql = 'SELECT group_colour

			FROM ' . GROUPS_TABLE . '

			WHERE group_id = ' . (int) $user_row['group_id'];

		$result = $db->sql_query_limit($sql, 1);

		$row = $db->sql_fetchrow($result);

		$db->sql_freeresult($result);

		set_config('newest_user_colour', $row['group_colour'], true);

	}

	return $user_id;

}

/**

* Remove User

*/

function user_delete($mode, $user_id, $post_username = false)

{

	global $cache, $config, $db, $user, $auth;

	global $phpbb_root_path, $phpEx;

	$sql = 'SELECT *

		FROM ' . USERS_TABLE . '

		WHERE user_id = ' . $user_id;

	$result = $db->sql_query($sql);

	$user_row = $db->sql_fetchrow($result);

	$db->sql_freeresult($result);

	if (!$user_row)

	{

		return false;

	}

	// Before we begin, we will remove the reports the user issued.

	$sql = 'SELECT r.post_id, p.topic_id

		FROM ' . REPORTS_TABLE . ' r, ' . POSTS_TABLE . ' p

		WHERE r.user_id = ' . $user_id . '

			AND p.post_id = r.post_id';

	$result = $db->sql_query($sql);

	$report_posts = $report_topics = array();

	while ($row = $db->sql_fetchrow($result))

	{

		$report_posts[] = $row['post_id'];

		$report_topics[] = $row['topic_id'];

	}

	$db->sql_freeresult($result);

	if (sizeof($report_posts))

	{

		$report_posts = array_unique($report_posts);

		$report_topics = array_unique($report_topics);

		// Get a list of topics that still contain reported posts

		$sql = 'SELECT DISTINCT topic_id

			FROM ' . POSTS_TABLE . '

			WHERE ' . $db->sql_in_set('topic_id', $report_topics) . '

				AND post_reported = 1

				AND ' . $db->sql_in_set('post_id', $report_posts, true);

		$result = $db->sql_query($sql);

		$keep_report_topics = array();

		while ($row = $db->sql_fetchrow($result))

		{

			$keep_report_topics[] = $row['topic_id'];

		}

		$db->sql_freeresult($result);

		if (sizeof($keep_report_topics))

		{

			$report_topics = array_diff($report_topics, $keep_report_topics);

		}

		unset($keep_report_topics);

		// Now set the flags back

		$sql = 'UPDATE ' . POSTS_TABLE . '

			SET post_reported = 0

			WHERE ' . $db->sql_in_set('post_id', $report_posts);

		$db->sql_query($sql);

		if (sizeof($report_topics))

		{

			$sql = 'UPDATE ' . TOPICS_TABLE . '

				SET topic_reported = 0

				WHERE ' . $db->sql_in_set('topic_id', $report_topics);

			$db->sql_query($sql);

		}

	}

	// Remove reports

	$db->sql_query('DELETE FROM ' . REPORTS_TABLE . ' WHERE user_id = ' . $user_id);

	if ($user_row['user_avatar'] && $user_row['user_avatar_type'] == AVATAR_UPLOAD)

	{

		avatar_delete('user', $user_row);

	}

	switch ($mode)

	{

		case 'retain':

			$db->sql_transaction('begin');

			if ($post_username === false)

			{

				$post_username = $user->lang['GUEST'];

			}

			// If the user is inactive and newly registered we assume no posts from this user being there...

			if ($user_row['user_type'] == USER_INACTIVE && $user_row['user_inactive_reason'] == INACTIVE_REGISTER && !$user_row['user_posts'])

			{

			}

			else

			{

				$sql = 'UPDATE ' . FORUMS_TABLE . '

					SET forum_last_poster_id = ' . ANONYMOUS . ", forum_last_poster_name = '" . $db->sql_escape($post_username) . "', forum_last_poster_colour = ''

					WHERE forum_last_poster_id = $user_id";

				$db->sql_query($sql);

				$sql = 'UPDATE ' . POSTS_TABLE . '

					SET poster_id = ' . ANONYMOUS . ", post_username = '" . $db->sql_escape($post_username) . "'

					WHERE poster_id = $user_id";

				$db->sql_query($sql);

				$sql = 'UPDATE ' . POSTS_TABLE . '

					SET post_edit_user = ' . ANONYMOUS . "

					WHERE post_edit_user = $user_id";

				$db->sql_query($sql);

				$sql = 'UPDATE ' . TOPICS_TABLE . '

					SET topic_poster = ' . ANONYMOUS . ", topic_first_poster_name = '" . $db->sql_escape($post_username) . "', topic_first_poster_colour = ''

					WHERE topic_poster = $user_id";

				$db->sql_query($sql);

				$sql = 'UPDATE ' . TOPICS_TABLE . '

					SET topic_last_poster_id = ' . ANONYMOUS . ", topic_last_poster_name = '" . $db->sql_escape($post_username) . "', topic_last_poster_colour = ''

					WHERE topic_last_poster_id = $user_id";

				$db->sql_query($sql);

				// Since we change every post by this author, we need to count this amount towards the anonymous user

				// Update the post count for the anonymous user

				if ($user_row['user_posts'])

				{

					$sql = 'UPDATE ' . USERS_TABLE . '

						SET user_posts = user_posts + ' . $user_row['user_posts'] . '

						WHERE user_id = ' . ANONYMOUS;

					$db->sql_query($sql);

				}

			}

			$db->sql_transaction('commit');

		break;

		case 'remove':

			if (!function_exists('delete_posts'))

			{

				include($phpbb_root_path . 'includes/functions_admin.' . $phpEx);

			}

			$sql = 'SELECT topic_id, COUNT(post_id) AS total_posts

				FROM ' . POSTS_TABLE . "

				WHERE poster_id = $user_id

				GROUP BY topic_id";

			$result = $db->sql_query($sql);

			$topic_id_ary = array();

			while ($row = $db->sql_fetchrow($result))

			{

				$topic_id_ary[$row['topic_id']] = $row['total_posts'];

			}

			$db->sql_freeresult($result);

			if (sizeof($topic_id_ary))

			{

				$sql = 'SELECT topic_id, topic_replies, topic_replies_real

					FROM ' . TOPICS_TABLE . '

					WHERE ' . $db->sql_in_set('topic_id', array_keys($topic_id_ary));

				$result = $db->sql_query($sql);

				$del_topic_ary = array();

				while ($row = $db->sql_fetchrow($result))

				{

					if (max($row['topic_replies'], $row['topic_replies_real']) + 1 == $topic_id_ary[$row['topic_id']])

					{

						$del_topic_ary[] = $row['topic_id'];

					}

				}

				$db->sql_freeresult($result);

				if (sizeof($del_topic_ary))

				{

					$sql = 'DELETE FROM ' . TOPICS_TABLE . '

						WHERE ' . $db->sql_in_set('topic_id', $del_topic_ary);

					$db->sql_query($sql);

				}

			}

			// Delete posts, attachments, etc.

			delete_posts('poster_id', $user_id);

		break;

	}

	$db->sql_transaction('begin');

	$table_ary = array(USERS_TABLE, USER_GROUP_TABLE, TOPICS_WATCH_TABLE, FORUMS_WATCH_TABLE, ACL_USERS_TABLE, TOPICS_TRACK_TABLE, TOPICS_POSTED_TABLE, FORUMS_TRACK_TABLE, PROFILE_FIELDS_DATA_TABLE, MODERATOR_CACHE_TABLE, DRAFTS_TABLE, BOOKMARKS_TABLE);

	foreach ($table_ary as $table)

	{

		$sql = "DELETE FROM $table

			WHERE user_id = $user_id";

		$db->sql_query($sql);

	}

	$cache->destroy('sql', MODERATOR_CACHE_TABLE);

	// Remove any undelivered mails...

	$sql = 'SELECT msg_id, user_id

		FROM ' . PRIVMSGS_TO_TABLE . '

		WHERE author_id = ' . $user_id . '

			AND folder_id = ' . PRIVMSGS_NO_BOX;

	$result = $db->sql_query($sql);

	$undelivered_msg = $undelivered_user = array();

	while ($row = $db->sql_fetchrow($result))

	{

		$undelivered_msg[] = $row['msg_id'];

		$undelivered_user[$row['user_id']][] = true;

	}

	$db->sql_freeresult($result);

	if (sizeof($undelivered_msg))

	{

		$sql = 'DELETE FROM ' . PRIVMSGS_TABLE . '

			WHERE ' . $db->sql_in_set('msg_id', $undelivered_msg);

		$db->sql_query($sql);

	}

	$sql = 'DELETE FROM ' . PRIVMSGS_TO_TABLE . '

		WHERE author_id = ' . $user_id . '

			AND folder_id = ' . PRIVMSGS_NO_BOX;

	$db->sql_query($sql);

	// Delete all to-information

	$sql = 'DELETE FROM ' . PRIVMSGS_TO_TABLE . '

		WHERE user_id = ' . $user_id;

	$db->sql_query($sql);

	// Set the remaining author id to anonymous - this way users are still able to read messages from users being removed

	$sql = 'UPDATE ' . PRIVMSGS_TO_TABLE . '

		SET author_id = ' . ANONYMOUS . '

		WHERE author_id = ' . $user_id;

	$db->sql_query($sql);

	$sql = 'UPDATE ' . PRIVMSGS_TABLE . '

		SET author_id = ' . ANONYMOUS . '

		WHERE author_id = ' . $user_id;

	$db->sql_query($sql);

	foreach ($undelivered_user as $_user_id => $ary)

	{

		if ($_user_id == $user_id)

		{

			continue;

		}

		$sql = 'UPDATE ' . USERS_TABLE . '

			SET user_new_privmsg = user_new_privmsg - ' . sizeof($ary) . ',

				user_unread_privmsg = user_unread_privmsg - ' . sizeof($ary) . '

			WHERE user_id = ' . $_user_id;

		$db->sql_query($sql);

	}

	$db->sql_transaction('commit');

	// Reset newest user info if appropriate

	if ($config['newest_user_id'] == $user_id)

	{

		update_last_username();

	}

	// Decrement number of users if this user is active

	if ($user_row['user_type'] != USER_INACTIVE && $user_row['user_type'] != USER_IGNORE)

	{

		set_config('num_users', $config['num_users'] - 1, true);

	}

	return false;

}

/**

* Flips user_type from active to inactive and vice versa, handles group membership updates

*

* @param string $mode can be flip for flipping from active/inactive, activate or deactivate

*/

function user_active_flip($mode, $user_id_ary, $reason = INACTIVE_MANUAL)

{

	global $config, $db, $user, $auth;

	$deactivated = $activated = 0;

	$sql_statements = array();

	if (!is_array($user_id_ary))

	{

		$user_id_ary = array($user_id_ary);

	}

	if (!sizeof($user_id_ary))

	{

		return;

	}

	$sql = 'SELECT user_id, group_id, user_type, user_inactive_reason

		FROM ' . USERS_TABLE . '

		WHERE ' . $db->sql_in_set('user_id', $user_id_ary);

	$result = $db->sql_query($sql);

	while ($row = $db->sql_fetchrow($result))

	{

		$sql_ary = array();

		if ($row['user_type'] == USER_IGNORE || $row['user_type'] == USER_FOUNDER ||

			($mode == 'activate' && $row['user_type'] != USER_INACTIVE) ||

			($mode == 'deactivate' && $row['user_type'] == USER_INACTIVE))

		{

			continue;

		}

		if ($row['user_type'] == USER_INACTIVE)

		{

			$activated++;

		}

		else

		{

			$deactivated++;

			// Remove the users session key...

			$user->reset_login_keys($row['user_id']);

		}

		$sql_ary += array(

			'user_type'				=> ($row['user_type'] == USER_NORMAL) ? USER_INACTIVE : USER_NORMAL,

			'user_inactive_time'	=> ($row['user_type'] == USER_NORMAL) ? time() : 0,

			'user_inactive_reason'	=> ($row['user_type'] == USER_NORMAL) ? $reason : 0,

		);

		$sql_statements[$row['user_id']] = $sql_ary;

	}

	$db->sql_freeresult($result);

	if (sizeof($sql_statements))

	{

		foreach ($sql_statements as $user_id => $sql_ary)

		{

			$sql = 'UPDATE ' . USERS_TABLE . '

				SET ' . $db->sql_build_array('UPDATE', $sql_ary) . '

				WHERE user_id = ' . $user_id;

			$db->sql_query($sql);

		}

		$auth->acl_clear_prefetch(array_keys($sql_statements));

	}

	if ($deactivated)

	{

		set_config('num_users', $config['num_users'] - $deactivated, true);

	}

	if ($activated)

	{

		set_config('num_users', $config['num_users'] + $activated, true);

	}

	// Update latest username

	update_last_username();

}

/**

* Add a ban or ban exclusion to the banlist. Bans either a user, an IP or an email address

*

* @param string $mode Type of ban. One of the following: user, ip, email

* @param mixed $ban Banned entity. Either string or array with usernames, ips or email addresses

* @param int $ban_len Ban length in minutes

* @param string $ban_len_other Ban length as a date (YYYY-MM-DD)

* @param boolean $ban_exclude Exclude these entities from banning?

* @param string $ban_reason String describing the reason for this ban

* @return boolean

*/

function user_ban($mode, $ban, $ban_len, $ban_len_other, $ban_exclude, $ban_reason, $ban_give_reason = '')

{

	global $db, $user, $auth, $cache;

	// Delete stale bans

	$sql = 'DELETE FROM ' . BANLIST_TABLE . '

		WHERE ban_end < ' . time() . '

			AND ban_end <> 0';

	$db->sql_query($sql);

	$ban_list = (!is_array($ban)) ? array_unique(explode("\n", $ban)) : $ban;

	$ban_list_log = implode(', ', $ban_list);

	$current_time = time();

	// Set $ban_end to the unix time when the ban should end. 0 is a permanent ban.

	if ($ban_len)

	{

		if ($ban_len != -1 || !$ban_len_other)

		{

			$ban_end = max($current_time, $current_time + ($ban_len) * 60);

		}

		else

		{

			$ban_other = explode('-', $ban_len_other);

			if (sizeof($ban_other) == 3 && ((int)$ban_other[0] < 9999) &&

				(strlen($ban_other[0]) == 4) && (strlen($ban_other[1]) == 2) && (strlen($ban_other[2]) == 2))

			{

				$ban_end = max($current_time, gmmktime(0, 0, 0, (int)$ban_other[1], (int)$ban_other[2], (int)$ban_other[0]));

			}

			else

			{

				trigger_error('LENGTH_BAN_INVALID');

			}

		}

	}

	else

	{

		$ban_end = 0;

	}

	$founder = $founder_names = array();

	if (!$ban_exclude)