<?php
/*
Simple:Press
Main database routines
$LastChangedDate: 2011-04-26 05:52:27 -0700 (Tue, 26 Apr 2011) $
$Rev: 5981 $
*/

if(preg_match('#' . basename(__FILE__) . '#', $_SERVER['PHP_SELF'])) {
	die('Access Denied');
}

# ******************************************************************
# GROUP/FORUM VIEW AND GENERAL DB FUNCTIONS
# ******************************************************************

# ------------------------------------------------------------------
# sf_get_combined_groups_and_forums($groupid)
#
# Grabs all groups and forums. Note that the group data is repeated.
# Used to populate 'Select Forum Quicklinks' and Front Main page
# of forum (Group/Forum View)
#	$groupid:		Optional id to display just a single group
# ------------------------------------------------------------------
function sf_get_combined_groups_and_forums($groupid = null)
{
	global $wpdb, $session_groups, $current_user;

	If(is_null($groupid) ? $where='' : $where = " WHERE ".SFGROUPS.".group_id=".$groupid." ");

	# retrieve group and forum records
	$records = $wpdb->get_results(
			"SELECT ".SFGROUPS.".group_id, group_name, group_desc, group_rss, group_icon, group_message,
			 forum_id, forum_name, forum_slug, forum_desc, forum_status, forum_icon, forum_rss_private, post_id, topic_count, parent, children
			 FROM ".SFGROUPS."
			 JOIN ".SFFORUMS." ON ".SFGROUPS.".group_id = ".SFFORUMS.".group_id
			 ".$where."
			 ORDER BY group_seq, forum_seq;");

	# rebuild into an array grabbing permissions on the way
	$groups=array();

	if($records)
	{
		# Set initially to Access Denied in case current user can view no forums
		$groups[0]['group_id'] = "Access Denied";
		$gindex=-1;
		$findex=0;

		foreach($records as $record)
		{
			$groupid=$record->group_id;
			$forumid=$record->forum_id;

			if (sf_can_view_forum($forumid) || sf_user_can($current_user->ID, 'Can view forum lists only', $forumid) || sf_user_can($current_user->ID, 'Can view forum and topic lists only', $forumid))
			{
				if($gindex == -1 || $groups[$gindex]['group_id'] != $groupid)
				{
					$gindex++;
					$findex=-1;
					$groups[$gindex]['group_id']=$record->group_id;
					$groups[$gindex]['group_name']=$record->group_name;
					$groups[$gindex]['group_desc']=$record->group_desc;
					$groups[$gindex]['group_rss']=$record->group_rss;
					$groups[$gindex]['group_icon']=$record->group_icon;
					$groups[$gindex]['group_message']=$record->group_message;
				}
				if(isset($record->forum_id))
				{
						$groups[$gindex]['forums'][$findex]['forum_id']=$record->forum_id;
						$groups[$gindex]['forums'][$findex]['forum_name']=$record->forum_name;
						$groups[$gindex]['forums'][$findex]['forum_slug']=$record->forum_slug;
						$groups[$gindex]['forums'][$findex]['forum_desc']=$record->forum_desc;
						$groups[$gindex]['forums'][$findex]['forum_status']=$record->forum_status;
						$groups[$gindex]['forums'][$findex]['forum_icon']=$record->forum_icon;
						$groups[$gindex]['forums'][$findex]['forum_rss_private']=$record->forum_rss_private;
						$groups[$gindex]['forums'][$findex]['post_id']=$record->post_id;
						$groups[$gindex]['forums'][$findex]['topic_count']=$record->topic_count;
						$groups[$gindex]['forums'][$findex]['parent']=$record->parent;
						$groups[$gindex]['forums'][$findex]['children']=$record->children;
						$findex++;
				}
			}
		}
	} else {
		$records = sf_get_groups_all(false, false);
		if($records)
		{
			foreach($records as $record)
			{
				$groups[$gindex]['group_id']=$record->group_id;
				$groups[$gindex]['group_name']=$record->group_name;
				$groups[$gindex]['group_desc']=$record->group_desc;
				$groups[$gindex]['group_rss']=$record->group_rss;
				$groups[$gindex]['group_icon']=$record->group_icon;
				$groups[$gindex]['group_message']=$record->group_message;
				$gindex++;
			}
		}
	}
	$session_groups = $groups;
	return $groups;
}

# ------------------------------------------------------------------
# sf_get_combined_groups_and_forums_bloglink()
#
# Grabs all groups and forums. Soecial cut down version for
# populating the blog link add post drop down
# ------------------------------------------------------------------
function sf_get_combined_groups_and_forums_bloglink()
{
	global $wpdb, $current_user;

	# retrieve group and forum records
	$records = $wpdb->get_results(
			"SELECT ".SFGROUPS.".group_id, group_name,
			 forum_id, forum_name
			 FROM ".SFGROUPS."
			 JOIN ".SFFORUMS." ON ".SFGROUPS.".group_id = ".SFFORUMS.".group_id
			 ".$where."
			 ORDER BY group_seq, forum_seq;");

	# rebuild into an array grabbing permissions on the way
	$groups=array();
	$gindex=-1;
	$findex=0;
	if($records)
	{
		foreach($records as $record)
		{
			$groupid=$record->group_id;
			$forumid=$record->forum_id;

			if (sf_user_can($current_user->ID, 'Can create linked topics', $forumid) && sf_user_can($current_user->ID, 'Can start new topics', $forumid))
			{
				if($gindex == -1 || $groups[$gindex]['group_id'] != $groupid)
				{
					$gindex++;
					$findex=0;
					$groups[$gindex]['group_id']=$record->group_id;
					$groups[$gindex]['group_name']=$record->group_name;
				}
				if(isset($record->forum_id))
				{
					$groups[$gindex]['forums'][$findex]['forum_id']=$record->forum_id;
					$groups[$gindex]['forums'][$findex]['forum_name']=$record->forum_name;
					$findex++;
				}
			}
		}
	}

	return $groups;
}

function sf_get_combined_forum_stats($posts)
{
	global $wpdb, $current_user;

	$clause='';

	if($posts)
	{
		$pcount = count($posts);
		$done = 0;

		foreach ($posts as $post)
		{
			$clause.= "(".SFPOSTS.".post_id=".$post.")";
			$done++;
			if($done < $pcount) $clause.= " OR ";
		}
	} else {
		$record=array();
		$record['topic_count'] = '0';
		$record['post_count'] = '0';
		$record['udate'] = '';

		return $record;
	}

	$records = $wpdb->get_results(
 			"SELECT ".SFPOSTS.".post_id, ".SFPOSTS.".topic_id, topic_name, ".SFPOSTS.".forum_id, ".sf_zone_datetime('post_date').",
 			 UNIX_TIMESTAMP(post_date) as udate, guest_name, ".SFPOSTS.".user_id, post_content, post_status, ".SFMEMBERS.".display_name, post_index,
 			 ".SFFORUMS.".topic_count, ".SFFORUMS.".post_count, topic_slug
			 FROM ".SFPOSTS."
			 LEFT JOIN ".SFFORUMS." ON ".SFPOSTS.".forum_id = ".SFFORUMS.".forum_id
			 LEFT JOIN ".SFTOPICS." ON ".SFPOSTS.".topic_id = ".SFTOPICS.".topic_id
			 LEFT JOIN ".SFMEMBERS." ON ".SFPOSTS.".user_id = ".SFMEMBERS.".user_id
			 WHERE ".$clause.";", ARRAY_A);

	if($records)
	{
		$sfdisplay=array();
		$sfdisplay=sf_get_option('sfdisplay');

		$forums = array();
		foreach($records as $record)
		{
			$forums[$record['forum_id']] = $record;

			# do we need a post tooltip on the topic link?
			if (($sfdisplay['topics']['posttip']==true) && (sf_can_view_forum($record['forum_id'])==true) && (sf_user_can($current_user->ID, 'Can view forum lists only', $record['forum_id'])==false) &&  (sf_user_can($current_user->ID, 'Can view forum and topic lists only', $record['forum_id'])==false))
			{
				$forums[$record['forum_id']]['post_tip']=sf_filter_tooltip_display($record['post_content'], $record['post_status']);
				$forums[$record['forum_id']]['post_content']='';
			}
		}
	}

	return $forums;
}

# ------------------------------------------------------------------
# sf_get_group_record()
#
# Returns a single group row
# 	$groupid:		group_id of group to return
#	$asArray:		return as an array if true
# Note: No permission checking is performed
# ------------------------------------------------------------------
function sf_get_group_record($groupid, $asArray=false)
{
	global $wpdb;

	if(!$groupid) return '';

	$sql=(
			"SELECT *
			 FROM ".SFGROUPS."
			 WHERE group_id=".$groupid.";");
	if($asArray) return $wpdb->get_row($sql, ARRAY_A);
	return $wpdb->get_row($sql);
}

# ------------------------------------------------------------------
# sf_get_groups_all()
#
# Return ALL group records - no permission checking
#	$id_only:		Optionsal = return just ids
#	$asArray:		Optional - return as array
# ------------------------------------------------------------------
function sf_get_groups_all($id_only=false, $asArray=false)
{
	global $wpdb;

	if($id_only ? $FROM='group_id' : $FROM='*');

	$sql=("SELECT ".$FROM." FROM ".SFGROUPS." ORDER BY group_seq");
	if($asArray) return $wpdb->get_results($sql, ARRAY_A);
	return $wpdb->get_results($sql);
}

# ------------------------------------------------------------------
# sf_group_exists()
#
# Check the existence of a group by id
# 	$groupid:		group to check for
# ------------------------------------------------------------------
function sf_group_exists($groupid)
{
	global $wpdb;

	if(empty($groupid)) return false;
	if($wpdb->get_var(
			"SELECT group_name
			 FROM ".SFGROUPS."
			 WHERE group_id=".$groupid))
	{
		return true;
	}
	return false;
}

# ------------------------------------------------------------------
# sf_get_group_rss_url()
#
# Returns the RSS feed URL for a Group (custom or standard)
# 	$groupid:		group to return
# ------------------------------------------------------------------
function sf_get_group_rss_url($groupid)
{
	global $wpdb, $sfglobals;

	if (empty($groupid)) return '';

	$url = $wpdb->get_var("SELECT group_rss FROM ".SFGROUPS." WHERE group_id=".$groupid);
	if (empty($url))
    {
        $rssopt = sf_get_option('sfrss');
        if ($rssopt['sfrssfeedkey'] && isset($sfglobals['member']['feedkey']))
            $url = sf_build_qurl('group='.$groupid, 'xfeed=group', 'feedkey='.$sfglobals['member']['feedkey']);
        else
            $url = sf_build_qurl('group='.$groupid, 'xfeed=group');
    }

	return $url;
}

# ------------------------------------------------------------------
# sf_get_group_name_from_forum()
#
# Returns the Group Name when only the forum id is known
# 	$forumid:		forum to lookup for group name
# ------------------------------------------------------------------
function sf_get_group_name_from_forum($forumid)
{
	global $wpdb;

	if(!$forumid) return '';

	return $wpdb->get_var(
			"SELECT ".SFGROUPS.".group_name
			 FROM ".SFGROUPS."
			 JOIN ".SFFORUMS." ON ".SFFORUMS.".group_id = ".SFGROUPS.".group_id
			 WHERE ".SFFORUMS.".forum_id=".$forumid);
}

# ******************************************************************
# FORUM/TOPIC VIEW AND GENERAL DB FUNCTIONS
# ******************************************************************

# ------------------------------------------------------------------
# sf_get_combined_forums_and_topics($forumid)
#
# Grabs all forums and their topics. Note that the forum data is
# repeated. Used to populate Topic Listing page of topics
# (Forum/Topics View)
#	$forumid:			forum id to display
#	$currentpage:		index to paging
# ------------------------------------------------------------------
function sf_get_combined_forums_and_topics($forumid, $currentpage)
{
	global $wpdb, $sfvars, $sfglobals, $current_user;

	if(!$forumid) return '';

	$sfvars['searchresults'] = 0;

	# rebuild into an array
	$forums=array();

	# Set initially to Access Denied in case current user can view no forums
	$forums[0]['forum_id']="Access Denied";

	# quick permission check
	if(!sf_can_view_forum($forumid) && !sf_user_can($current_user->ID, 'Can view forum and topic lists only', $forumid)) return $forums;

	# some setup vars
	$startlimit = 0;

	# get post tooltip state
	$sfdisplay = array();
	$sfdisplay = sf_get_option('sfdisplay');

	# how many topics per page?
	$tpaged = $sfglobals['display']['topics']['perpage'];
	if(!$tpaged) $tpaged=20;

	# setup where we are in the topic list (paging)
	if($sfvars['searchpage'] == 0)
	{
		if($currentpage != 1)
		{
			$startlimit = ((($currentpage-1) * $tpaged));
		}
	} else {
		if($sfvars['searchpage'] == 1)
		{
			$currentpage = 1;
		} else {
			$startlimit = ((($sfvars['searchpage']-1) * $tpaged));
		}
	}

	$LIMIT = " LIMIT ".$startlimit.', '.$tpaged;

	if($sfvars['searchpage'] == 0)
	{
		$topicsort = $sfglobals['display']['topics']['sortnewtop'];
		if ($topicsort)
		{
			$ORDER = " ORDER BY topic_pinned DESC, ".SFTOPICS.".post_id DESC";
		} else {
			$ORDER = " ORDER BY topic_pinned DESC, ".SFTOPICS.".post_id ASC";
		}
	} else {
		$ORDER = " ORDER BY ".SFTOPICS.".post_id DESC";
	}

	if($sfvars['searchpage'] == 0)
	{
		# standar forum view
		$SELECT = "SELECT ";
		$MATCH = "";
		$ANDWHERE = "";
	} else {

		$searchvalue=urldecode($sfvars['searchvalue']);
		if(empty($searchvalue))
		{
			return '';
		}

		# what sort of search is it?
		if($sfvars['searchtype'] == 6) {

			# topic status search
			$SELECT = "SELECT SQL_CALC_FOUND_ROWS DISTINCT ";
			$MATCH = "";
			$ANDWHERE = " AND topic_status_flag=".$sfvars['searchvalue']." ";

		} elseif($sfvars['searchtype'] == 8) {

			# members 'posted in' sepcified forum search
			$userid = $sfvars['searchvalue'];
			$SELECT = "SELECT SQL_CALC_FOUND_ROWS DISTINCT ";
			$MATCH = "";
			$ANDWHERE = " AND ".SFPOSTS.".user_id=".$userid." ";

		} elseif($sfvars['searchtype'] == 9) {

			# members 'started' in sepcified forum search
			$userid = $sfvars['searchvalue'];
			$SELECT = "SELECT SQL_CALC_FOUND_ROWS DISTINCT ";
			$MATCH = "";
			$ANDWHERE = " AND ".SFPOSTS.".user_id=".$userid." ";

		} elseif($sfvars['searchinclude'] == 4) {

			# it's a tag search
			$searchtag = sf_create_slug($searchvalue, 'tag');
			$SELECT = "SELECT SQL_CALC_FOUND_ROWS DISTINCT ";
			$MATCH = SFTOPICS.".topic_id IN (SELECT topic_id FROM ".SFTAGMETA." JOIN ".SFTAGS." ON ".SFTAGMETA.".tag_id = ".SFTAGS.".tag_id
				WHERE tag_slug = '".$searchtag."' AND forum_id=".$forumid.") AND ";
			$ANDWHERE = "";

		} else {

			# general keyword search
			$SELECT = "SELECT SQL_CALC_FOUND_ROWS DISTINCT ";

			$searchterm = sf_construct_search_term($searchvalue, $sfvars['searchtype']);

			switch($sfvars['searchinclude'])
			{
				case 1:
					$MATCH = "(MATCH(".SFPOSTS.".post_content) AGAINST ('".esc_sql(like_escape($searchterm))."' IN BOOLEAN MODE) OR MATCH(".SFTOPICS.".topic_name) AGAINST ('".esc_sql(like_escape($searchterm))."' IN BOOLEAN MODE)) AND ";
					break;
				case 2:
					$MATCH = "MATCH(".SFPOSTS.".post_content) AGAINST ('".esc_sql(like_escape($searchterm))."' IN BOOLEAN MODE) AND ";
					break;
				case 3:
					$MATCH = "MATCH(".SFTOPICS.".topic_name) AGAINST ('".esc_sql(like_escape($searchterm))."' IN BOOLEAN MODE) AND ";
					break;
			}
			$ANDWHERE = "";
		}
	}

	# retrieve forum and topic records - is a type 1, 2, 3 or 8 then we need a separate query (for now)
	# NO post_content or post_index = 1
	if (!empty($sfvars['searchtype']) && ($sfvars['searchtype'] == 1 || $sfvars['searchtype'] == 2 || $sfvars['searchtype'] == 3 || $sfvars['searchtype'] == 8))
	{
		$records = sf_query_results(
			$SELECT.SFFORUMS.".forum_id, forum_slug, forum_name, forum_status, group_id, topic_count, forum_icon, forum_desc, topic_status_set,
			parent, children, forum_message,
			 ".SFTOPICS.".topic_id, topic_slug, topic_name, ".sf_zone_datetime('topic_date').",
			 topic_status, topic_pinned, topic_opened, topic_subs, topic_watches, topic_status_flag,
			 post_ratings, use_tags, blog_post_id, ".SFTOPICS.".post_id,  ".SFTOPICS.".post_count, ".SFTOPICS.".user_id, post_status
			 FROM ".SFFORUMS."
			 JOIN ".SFTOPICS." ON ".SFFORUMS.".forum_id = ".SFTOPICS.".forum_id
			 JOIN ".SFPOSTS." ON ".SFTOPICS.".topic_id = ".SFPOSTS.".topic_id
			 WHERE ".$MATCH.SFFORUMS.".forum_id=".$forumid.$ANDWHERE.$ORDER.$LIMIT.";");
	} else {
		$records = sf_query_results(
			$SELECT.SFFORUMS.".forum_id, forum_slug, forum_name, forum_status, group_id, topic_count, forum_icon, forum_desc, topic_status_set,
			parent, children, forum_message,
			 ".SFTOPICS.".topic_id, topic_slug, topic_name, ".sf_zone_datetime('topic_date').",
			 topic_status, topic_pinned, topic_opened, topic_subs, topic_watches, topic_status_flag,
			 post_ratings, use_tags, blog_post_id, ".SFTOPICS.".post_id,  ".SFTOPICS.".post_count, ".SFTOPICS.".user_id, post_content, post_status
			 FROM ".SFFORUMS."
			 JOIN ".SFTOPICS." ON ".SFFORUMS.".forum_id = ".SFTOPICS.".forum_id
			 JOIN ".SFPOSTS." ON ".SFTOPICS.".topic_id = ".SFPOSTS.".topic_id
			 WHERE ".$MATCH.SFFORUMS.".forum_id=".$forumid.$ANDWHERE." AND ".SFPOSTS.".post_index=1 ".$ORDER.$LIMIT.";");
	}

	if (!empty($sfvars['searchpage']) && $records)
	{
		$sfvars['searchresults'] = $wpdb->get_var("SELECT FOUND_ROWS()");
	}

	$findex=-1;
	$tindex=0;

	if($records)
	{
		$topiclist = '';
		foreach($records as $record)
		{
			$forumid=$record->forum_id;

			if($findex == -1 || $forums[$findex]['forum_id'] != $forumid)
			{
				$findex++;
				$tindex=0;
				$forums[$findex]['forum_id']=$record->forum_id;
				$forums[$findex]['forum_slug']=$record->forum_slug;
				$forums[$findex]['forum_name']=$record->forum_name;
				$forums[$findex]['forum_desc']=$record->forum_desc;
				$forums[$findex]['forum_status']=$record->forum_status;
				$forums[$findex]['group_id']=$record->group_id;
				$forums[$findex]['topic_count']=$record->topic_count;
				$forums[$findex]['forum_icon']=$record->forum_icon;
				$forums[$findex]['topic_status_set']=$record->topic_status_set;
				$forums[$findex]['post_ratings']=$record->post_ratings;
				$forums[$findex]['use_tags']=$record->use_tags;
				$forums[$findex]['parent']=$record->parent;
				$forums[$findex]['children']=$record->children;
				$forums[$findex]['forum_message']=$record->forum_message;
			}
			$forums[$findex]['topics'][$tindex]['topic_id']=$record->topic_id;
			$forums[$findex]['topics'][$tindex]['topic_slug']=$record->topic_slug;
			$forums[$findex]['topics'][$tindex]['topic_name']=$record->topic_name;
			$forums[$findex]['topics'][$tindex]['topic_date']=$record->topic_date;
			$forums[$findex]['topics'][$tindex]['topic_status']=$record->topic_status;
			$forums[$findex]['topics'][$tindex]['topic_pinned']=$record->topic_pinned;
			$forums[$findex]['topics'][$tindex]['topic_opened']=$record->topic_opened;
			$forums[$findex]['topics'][$tindex]['topic_subs']=$record->topic_subs;
			$forums[$findex]['topics'][$tindex]['topic_watches']=$record->topic_watches;
			$forums[$findex]['topics'][$tindex]['topic_status_flag']=$record->topic_status_flag;
			$forums[$findex]['topics'][$tindex]['post_ratings']=$record->post_ratings;
			$forums[$findex]['topics'][$tindex]['use_tags']=$record->use_tags;
			$forums[$findex]['topics'][$tindex]['blog_post_id']=$record->blog_post_id;
			$forums[$findex]['topics'][$tindex]['post_id']=$record->post_id;
			$forums[$findex]['topics'][$tindex]['post_count']=$record->post_count;
			$forums[$findex]['topics'][$tindex]['user_id']=$record->user_id;

			# do we need a post tooltip on the topic link?
			if ($sfdisplay['topics']['posttip'] && (!empty($sfvars['searchtype']) && $sfvars['searchtype'] != 8) && $current_user->sfaccess)
			{
				$forums[$findex]['topics'][$tindex]['post_tip'] = sf_filter_tooltip_display($record->post_content, $record->post_status);
			} else {
				$forums[$findex]['topics'][$tindex]['post_tip']="";
			}

			# save topic in list for getting tags later
			$topiclist[] = $record->topic_id;

			$tindex++;
		}

		# Now get tags in one query for these topics
		$topiclist = implode(',', $topiclist);
		$sql = "SELECT tag_name, tag_slug, topic_id
		FROM ".SFTAGS."
	 	JOIN ".SFTAGMETA." ON ".SFTAGMETA.".tag_id = ".SFTAGS.".tag_id
		WHERE topic_id IN (".$topiclist.")
		ORDER BY topic_id";
		$tags = $wpdb->get_results($sql);

		# Now sort the tags into the monsterous forum array
		if ($tags)
		{
			foreach ($forums as $findex => $forum)
			{
				foreach ($forum['topics'] as $tindex => $topic)
				{
					if ($topic['use_tags'])
					{
						$topictags = '';
						foreach ($tags as $tag)
						{
							if ($topic['topic_id'] == $tag->topic_id)
							{
								# build tag list for this forum/topic
								$topictags[] = $tag;
							}
						}
						# save the tags into the master array
						$forums[$findex]['topics'][$tindex]['tags'] = $topictags;
					}
				}
			}
		}
	} else {
		$record = sf_get_forum_record($forumid);
		if($record)
		{
			$forums[0]['forum_id']=$record->forum_id;
			$forums[0]['forum_slug']=$record->forum_slug;
			$forums[0]['forum_name']=$record->forum_name;
			$forums[0]['forum_desc']=$record->forum_desc;
			$forums[0]['forum_status']=$record->forum_status;
			$forums[0]['group_id']=$record->group_id;
			$forums[0]['topic_count']=$record->topic_count;
			$forums[0]['forum_icon']=$record->forum_icon;
			$forums[0]['topic_status_set']=$record->topic_status_set;
			$forums[0]['post_ratings']=$record->post_ratings;
			$forums[0]['use_tags']=$record->use_tags;
			$forums[0]['parent']=$record->parent;
			$forums[0]['children']=$record->children;
			$forums[0]['forum_message']=$record->forum_message;
		}
	}
	return $forums;
}

function sf_get_subforums($sublist)
{
    global $wpdb;

    if (!$sublist) return '';

    $subforums = unserialize($sublist);
    $forumlist = array();
    foreach ($subforums as $forumid)
    {
        if (sf_can_view_forum($forumid)) $forumlist[] = $forumid;
    }

    if (empty($forumlist)) return '';

    $where = " WHERE forum_id IN (" . implode(",", $forumlist) . ")";
    $subforums = $wpdb->get_results("SELECT * FROM ".SFFORUMS.$where." ORDER BY forum_seq", ARRAY_A);

    return $subforums;
}

# ------------------------------------------------------------------
# sf_get_combined_topic_stats()
#
# Returns the first and last post data for a topic
#	$postid:		post id from new posts list
#	$user_id		for checking of user has posted in topic		-
# ------------------------------------------------------------------
function sf_get_combined_topic_stats($posts, $userid=0)
{
	global $wpdb, $current_user;

	$clause='';
	if($posts)
	{
		$pcount = count($posts);
		$done = 0;

		foreach ($posts as $topic=>$postindex)
		{
			$clause.= "(topic_id = ".$topic." AND post_index = 1 OR topic_id = ".$topic." AND post_index=".$postindex.")";
			$done++;
			if($done < $pcount) $clause.= " OR ";
		}
	} else {
		return;
	}

	$records = $wpdb->get_results(
			"SELECT post_id, topic_id, forum_id, ".sf_zone_datetime('post_date').", UNIX_TIMESTAMP(post_date) as udate, guest_name, ".SFPOSTS.".user_id, post_index, post_status, post_content, ".SFMEMBERS.".display_name
			 FROM ".SFPOSTS."
			 LEFT JOIN ".SFMEMBERS." ON ".SFPOSTS.".user_id = ".SFMEMBERS.".user_id
			 WHERE ".$clause."
			 ORDER BY post_id ASC;", ARRAY_A);

	# If forum view are we looking to see if user has posted in ant of the topics?
	if($records && $userid)
	{
		$clause='';
		$pcount = count($posts);
		$done = 0;

		foreach ($posts as $topic=>$postindex)
		{
			$clause.= "(topic_id = ".$topic." AND user_id = ".$userid.")";
			$done++;
			if($done < $pcount) $clause.= " OR ";
		}

		$userposted = $wpdb->get_results(
				"SELECT DISTINCT topic_id, user_id
				 FROM ".SFPOSTS."
				 WHERE ".$clause.";");
	} else {
		$userposted = '';
	}
	# Now to format and combine results
	if ($records)
	{
		$sfdisplay=array();
		$sfdisplay=sf_get_option('sfdisplay');

		$topics = array();
		foreach ($records as $record)
		{
			$topics[$record['topic_id']][$record['post_index']] = $record;

			# do we need a post tooltip on the topic link?
			if ($sfdisplay['topics']['posttip'] && $current_user->sfaccess)
			{
				$topics[$record['topic_id']][$record['post_index']]['post_tip']=sf_filter_tooltip_display($record['post_content'], $record['post_status']);
				$topics[$record['topic_id']][$record['post_index']]['post_content']='';
			}
		}

		foreach ($records as $record)
		{
			$topics[$record['topic_id']]['thisuser'] = false;
			if ($userposted)
			{
				foreach ($userposted as $user)
				{
					if ($user->topic_id == $record['topic_id'])
					{
						$topics[$record['topic_id']]['thisuser'] = true;
						break;
					}
				}
			}
		}
		return $topics;
	} else {
		return '';
	}
}

# ******************************************************************
# FORUM/TOPIC VIEW AND GENERAL DB FUNCTIONS
# ******************************************************************

# ------------------------------------------------------------------
# sf_get_watched_topics($currentpagge)
#
# Grabs all watched topics
#	$currentpage:		index to paging
# ------------------------------------------------------------------
function sf_get_watched_topics($currentpage=0)
{
	global $wpdb, $current_user, $sfglobals;

	# quick permission check
	if (!$current_user->sfwatch) return '';

	$limit='';

	# get watched topics
	$list = $sfglobals['member']['watches'];
	if (empty($list)) return '';
	# create where clause of watched topics
	$where = " WHERE ".SFTOPICS.".topic_id IN (" . implode(",", $list) . ")";

	# retrieve watched topic records
	$query = "SELECT ".SFTOPICS.".topic_id, topic_slug, topic_name, ".sf_zone_datetime('topic_date').",
			 topic_status, topic_pinned, topic_opened, topic_watches, topic_status_flag,
			 blog_post_id, ".SFTOPICS.".forum_id, ".SFTOPICS.".post_id, ".SFTOPICS.".post_count,
			 forum_slug, forum_name, topic_status_set, post_ratings, group_id, post_content, post_status
			 FROM ".SFTOPICS."
			 JOIN ".SFFORUMS." ON ".SFFORUMS.".forum_id = ".SFTOPICS.".forum_id
			 JOIN ".SFPOSTS." ON ".SFTOPICS.".post_id = ".SFPOSTS.".post_id ".
			 $where." ORDER BY topic_date DESC ".$limit;

	$records = $wpdb->get_results($query, ARRAY_A);

	if($records)
	{
		$sfdisplay=array();
		$sfdisplay=sf_get_option('sfdisplay');

		# do we need a post tooltip on the topic link?
		if($sfdisplay['topics']['posttip'])
		{
			for($x=0; $x<count($records); $x++)
			{
				if ($current_user->sfaccess)
				{
					$records[$x]['post_tip']=sf_filter_tooltip_display($records[$x]['post_content'], $records[$x]['post_status']);
					$records[$x]['post_content']='';
				}
			}
		}
	}

	$watched['records'] = $records;
	$watched['count'] = count($list);

	return $watched;
}

# ------------------------------------------------------------------
# sf_get_subscribed_topics($currentpagge)
#
# Grabs all subscribed topics
#	$currentpage:		index to paging
# ------------------------------------------------------------------
function sf_get_subscribed_topics($currentpage=0)
{
	global $wpdb, $current_user, $sfglobals;

	$limit = '';

	# quick permission check
	if (!$current_user->sfsubscriptions) return '';

	# get subscribed topics
	$list = $sfglobals['member']['subscribe'];
	if (empty($list)) return '';

	# create where clause of subscribed topics
	$where = " WHERE ".SFTOPICS.".topic_id IN (" . implode(",", $list) . ")";

	# retrieve watched topic records
	$query = "SELECT ".SFTOPICS.".topic_id, topic_slug, topic_name, ".sf_zone_datetime('topic_date').",
			 topic_status, topic_pinned, topic_opened, topic_subs, topic_status_flag,
			 blog_post_id, ".SFTOPICS.".forum_id, ".SFTOPICS.".post_id, ".SFTOPICS.".post_count,
			 forum_slug, forum_name, topic_status_set, post_ratings, group_id, post_content, post_status
			 FROM ".SFTOPICS."
			 JOIN ".SFFORUMS." ON ".SFFORUMS.".forum_id = ".SFTOPICS.".forum_id
			 JOIN ".SFPOSTS." ON ".SFTOPICS.".post_id = ".SFPOSTS.".post_id ".
			 $where." ORDER BY topic_date DESC ".$limit;
	$records = $wpdb->get_results($query, ARRAY_A);

	if($records)
	{
		$sfdisplay=array();
		$sfdisplay=sf_get_option('sfdisplay');

		# do we need a post tooltip on the topic link?
		if ($sfdisplay['topics']['posttip'])
		{
			for($x=0; $x<count($records); $x++)
			{
				if ($current_user->sfaccess)
				{
					$records[$x]['post_tip']=sf_filter_tooltip_display($records[$x]['post_content'], $records[$x]['post_status']);
					$records[$x]['post_content']='';
				}
			}
		}
	}

	$subscribed['records'] = $records;
	$subscribed['count'] = count($list);

	return $subscribed;
}

# ------------------------------------------------------------------
# sf_get_related_topics($tags)
#
# Grabs related topics based on tags
# ------------------------------------------------------------------
function sf_get_related_topics($tags)
{
	global $wpdb;

	if(!$tags) return '';

	# build list of tags for the topic id
	$taglist = '';
	foreach ($tags as $tag)
	{
		if ($taglist == '')
		{
			$taglist = "('".$tag->tag_slug."'";
		} else {
			$taglist.= ",'".$tag->tag_slug."'";
		}
	}
	$taglist.= ")";

	# now grab the results
	$LIMIT = ' LIMIT 10';
	$ORDER = ' ORDER BY topic_id DESC';
	$WHERE = SFTOPICS.".topic_id IN (SELECT topic_id FROM ".SFTAGMETA." JOIN ".SFTAGS." ON ".SFTAGMETA.".tag_id = ".SFTAGS.".tag_id
		WHERE tag_slug IN ".$taglist.")";
	$topics = $wpdb->get_results(
			"SELECT SQL_CALC_FOUND_ROWS DISTINCT
			 ".SFTOPICS.".topic_id, topic_name, topic_slug, ".SFTOPICS.".forum_id, forum_name, forum_slug,
			 ".sf_zone_datetime('topic_date').", topic_status, topic_pinned, topic_opened,
			 topic_watches, topic_status_flag, blog_post_id, ".SFTOPICS.".post_id, ".SFTOPICS.".post_count,
			 topic_status_set, post_ratings, group_id
			 FROM ".SFTOPICS."
			 JOIN ".SFFORUMS." ON ".SFTOPICS.".forum_id = ".SFFORUMS.".forum_id
			 JOIN ".SFPOSTS." ON ".SFTOPICS.".topic_id = ".SFPOSTS.".topic_id
			 WHERE ".$WHERE.$ORDER.$LIMIT.";", ARRAY_A);

 	return $topics;
}

# ------------------------------------------------------------------
# sf_get_memberlists()
#
# Builds viewable member lists for current user
# ------------------------------------------------------------------
function sf_get_memberlists($currentpage, $search)
{
	global $wpdb, $current_user, $sfglobals;

	$data = '';
	$data->records = '';
	$data->count = 0;

	$sfmemberopts = sf_get_option('sfmemberopts');
	if ($current_user->forumadmin || ($sfmemberopts['sfshowmemberlist'] && $current_user->sfmemberlist))
	{
		# are we limiting member lists to memberships?
		$where = ' WHERE posts > -2';
        $sfmemberopts = sf_get_option('sfmemberopts');
		if ($sfmemberopts['sflimitmemberlist'] && !$current_user->forumadmin)
		{
			# get usergroups user has membership in
			$ugs = sf_get_user_memberships($current_user->ID);

			#if no usergroup memberships return empty list
			if ($ugs)
			{
				$ug_ids='';
				foreach ($ugs as $ug)
				{
					$ug_ids[] = $ug['usergroup_id'];
				}

				# if no visible usergroups, dont return any search results
				if (empty($ug_ids)) return $data;
			} else {
				return $data;
			}

			# create where clause based on user memberships
			$where.= " AND (".SFMEMBERSHIPS.".usergroup_id IN (" . implode(",", $ug_ids) . ") OR ".SFMEMBERSHIPS.".usergroup_id IS NULL)";
		}

		if ($search != '')
		{
			$where.= " AND ".SFMEMBERS.'.display_name LIKE "'.$search.'%"';
		}

		# how many members per page?
		$startlimit = 0;
		$tpaged = $sfglobals['display']['topics']['perpage'];
		if(!$tpaged) $tpaged=20;

		if ($currentpage != 1)
		{
			$startlimit = ((($currentpage-1) * $tpaged));
		}
		$limit = " LIMIT ".$startlimit.', '.$tpaged;

		# retrieve members list records
		$query = "SELECT SQL_CALC_FOUND_ROWS DISTINCT IFNULL(".SFMEMBERSHIPS.".usergroup_id, '0') as usergroup_id, ".SFMEMBERS.".user_id,
			".SFMEMBERS.".display_name, user_email, posts, lastvisit, IFNULL(usergroup_name, 'Admin') as usergroup_name,
			IFNULL(usergroup_desc, 'Forum Administrator') as usergroup_desc
			FROM ".SFUSERS."
			LEFT JOIN ".SFMEMBERS." ON ".SFUSERS.".ID = ".SFMEMBERS.".user_id
			LEFT JOIN ".SFMEMBERSHIPS." ON ".SFMEMBERSHIPS.".user_id = ".SFMEMBERS.".user_id
			LEFT JOIN ".SFUSERGROUPS." ON ".SFUSERGROUPS.".usergroup_id = ".SFMEMBERSHIPS.".usergroup_id
			".$where."
			ORDER BY usergroup_id, ".SFMEMBERS.".display_name "
			.$limit;

		$records = $wpdb->get_results($query, ARRAY_A);
		$data->records = $records;

		$data->count = $wpdb->get_var("SELECT FOUND_ROWS()");
	}

	return $data;
}

function sf_get_forum_memberships($user_id)
{
	global $wpdb, $current_user;

	if ($current_user->guest)
	{
		$value = sf_get_sfmeta('default usergroup', 'sfguests');
		$guests = $value[0]['meta_value'];
		$sql = "SELECT forum_id
				FROM ".SFPERMISSIONS."
				WHERE usergroup_id=".$guests;
	} else {
		$sql = "SELECT forum_id
				FROM ".SFPERMISSIONS."
				JOIN ".SFMEMBERSHIPS." ON ".SFPERMISSIONS.".usergroup_id = ".SFMEMBERSHIPS.".usergroup_id
				WHERE user_id=".$user_id;
	}

	return $wpdb->get_results($sql);
}

function sf_get_membership_count($usergroup_id)
{
	global $wpdb;

	if(!$usergroup_id) return '';

	$sql = "SELECT COUNT(*)
			FROM ".SFMEMBERSHIPS."
			WHERE ".SFMEMBERSHIPS.".usergroup_id=".$usergroup_id;
	return $wpdb->get_var($sql);
}

# ------------------------------------------------------------------
# sf_get_last_post_in_topic()
#
# Returns post details of the latest post in the requested topic
#	$topicid:		requested topic
# NOTE: This one remains as used in a template tag
# ------------------------------------------------------------------
function sf_get_last_post_in_topic($topicid)
{
	global $wpdb;

	if(!$topicid) return '';

	return $wpdb->get_row(
			"SELECT post_id, topic_id, forum_id, post_status, post_index, ".sf_zone_datetime('post_date').", UNIX_TIMESTAMP(post_date) as udate, guest_name, guest_email, ".SFPOSTS.".user_id, ".SFMEMBERS.".display_name, user_email
			 FROM ".SFPOSTS."
			 LEFT JOIN ".SFMEMBERS." ON ".SFPOSTS.".user_id = ".SFMEMBERS.".user_id
			 LEFT JOIN ".SFUSERS." ON ".SFPOSTS.".user_id = ".SFUSERS.".ID
			 WHERE topic_id = ".$topicid." AND post_status = 0
			 ORDER BY post_id DESC LIMIT 1");
}

# ------------------------------------------------------------------
# sf_get_postratings()
#
# Returns post ratings
# 	$postid:		post_id of post to return
#	$asArray:		return as an array if true
# Note: No permission checking is performed
# ------------------------------------------------------------------
function sf_get_postratings($postid, $asArray=false)
{
	global $wpdb;

	if(!$postid) return '';

	$sql=(
			"SELECT *
			 FROM ".SFPOSTRATINGS."
			 WHERE post_id=".$postid.";");
	if($asArray) return $wpdb->get_row($sql, ARRAY_A);
	return $wpdb->get_row($sql);
}

# ------------------------------------------------------------------
# sf_update_postratings()
#
# Upates post ratings
# 	$postid:		post_id
# 	$count:			number of votes
# 	$sum:			ratings sum
# 	$ips:			array of ips voted for guests
# 	$members:		members that have voted
# Note: No permission checking is performed
# ------------------------------------------------------------------
function sf_update_postratings($postid, $count, $sum, $ips, $members)
{
	global $wpdb;

	if(!$postid) return '';

	$sql=(
			"UPDATE ".SFPOSTRATINGS."
			 SET vote_count=$count, ratings_sum=$sum, ips='".$ips."', members='".$members."'
			 WHERE post_id=".$postid.";");
	$wpdb->query($sql);
	return;
}

# ------------------------------------------------------------------
# Add post ratings
# 	$postid:		post_id
# 	$count:			number of votes
# 	$sum:			ratings sum
# 	$ips:			array of ips voted for guests
# 	$members:		members that have voted
# Note: No permission checking is performed
# ------------------------------------------------------------------
function sf_add_postratings($postid, $count, $sum, $ips, $members)
{
	global $wpdb;

	if(!$postid) return '';

	$sql=(
		 	"INSERT INTO ".SFPOSTRATINGS." (post_id, vote_count, ratings_sum, ips, members)
			 VALUES ($postid, $count, $sum, '".$ips."', '".$members."');");
	$wpdb->query($sql);
	return;
}

# ------------------------------------------------------------------
# sf_get_topic_ratings()
#
# Returns post ratings
# 	$topicid:		post_id of post to return
# Note: No permission checking is performed
# ------------------------------------------------------------------
function sf_get_topic_ratings($topicid)
{
	global $wpdb;

	if(!$topicid) return '';

	$sql = ("
			SELECT vote_count, ratings_sum
			FROM ".SFPOSTRATINGS."
 			JOIN ".SFPOSTS." ON ".SFPOSTS.".topic_id = ".$topicid."
			WHERE ".SFPOSTRATINGS.".post_id = ".SFPOSTS.".post_id");
	return $wpdb->get_results($sql);
}

# ------------------------------------------------------------------
# sf_get_forum_record()
#
# Returns a single forum row
# 	$forumid:		forum_id of forum to return
# Note: No permission checking is performed
# ------------------------------------------------------------------
function sf_get_forum_record($forumid)
{
	global $wpdb, $session_forums;

	if(!$forumid) return '';

	# check if in the session forums cache
	if($session_forums)
	{
		foreach($session_forums as $forum)
		{
			if($forum->forum_id == $forumid)
			{
				return $forum;
			}
		}
	}

	$sql=(
			"SELECT *
			 FROM ".SFFORUMS."
			 WHERE forum_id=".$forumid.";");
	$thisforum = $wpdb->get_row($sql);
	$session_forums[] = $thisforum;
	return $thisforum;
}

# ------------------------------------------------------------------
# sf_get_forum_record_from_slug()
#
# Returns a single forum row
# 	$forumslug:		forum_slug of forum to return
#	$asArray:		return as an array if true
# Note: No permission checking is performed
# ------------------------------------------------------------------
function sf_get_forum_record_from_slug($forumslug, $asArray=false)
{
	global $wpdb;

	if(!$forumslug) return '';

	$sql=(
			"SELECT *
			 FROM ".SFFORUMS."
			 WHERE forum_slug='".$forumslug."';");
	if($asArray) return $wpdb->get_row($sql, ARRAY_A);
	return $wpdb->get_row($sql);
}

# ------------------------------------------------------------------
# sf_get_group_record_from_slug()
#
# Returns a single group and forum row
# 	$forumslug:		forum_slug of group and forum to return
#	$asArray:		return as an array if true
# Note: No permission checking is performed
# ------------------------------------------------------------------
function sf_get_group_record_from_slug($forumslug, $asArray=false)
{
	global $wpdb;

	if(!$forumslug) return '';

	$sql=(
			"SELECT *
			 FROM ".SFFORUMS."
             JOIN ".SFGROUPS." ON ".SFFORUMS.".group_id = ".SFGROUPS.".group_id
			 WHERE forum_slug='".$forumslug."';");
	if($asArray) return $wpdb->get_row($sql, ARRAY_A);
	return $wpdb->get_row($sql);
}

# ------------------------------------------------------------------
# sf_get_forums_all()
#
# Returns complete recordset of forums
# 	$id_only:		limit recordset to forum_id and slug only
#	$asArray:		return results as an array
# Note: No permission checking is performed
# ------------------------------------------------------------------
function sf_get_forums_all($id_only=false, $asArray=false)
{
	global $wpdb;

	if($id_only ? $FROM='forum_id, forum_slug' : $FROM='*');
	$sql=("SELECT ".$FROM." FROM ".SFFORUMS." ORDER BY forum_seq");
	if($asArray) return $wpdb->get_results($sql, ARRAY_A);
	return $wpdb->get_results($sql);
}

# ------------------------------------------------------------------
# sf_forum_exists()
#
# Check the existence of a forum by id
# 	$forumid:		forum to check for
# ------------------------------------------------------------------
function sf_forum_exists($forumid)
{
	global $wpdb;

	if(empty($forumid)) return false;
	if($wpdb->get_var(
			"SELECT forum_name
			 FROM ".SFFORUMS."
			 WHERE forum_id=".$forumid))
	{
		return true;
	}
	return false;
}

# ------------------------------------------------------------------
# sf_forum_locked()
#
# Returns the lock status of a forum
# 	$forumid:		forum to check for
# ------------------------------------------------------------------
function sf_forum_locked($forumid)
{
	global $wpdb;

	return $wpdb->get_var("SELECT forum_status FROM ".SFFORUMS." WHERE forum_id=".$forumid);
}

# ------------------------------------------------------------------
# sf_get_forum_rss_url()
#
# Returns the RSS URL for a forum (custom or standard)
# 	$forumid:		forum to return
#	$forumslug:		slug for the url
# ------------------------------------------------------------------
function sf_get_forum_rss_url($forumid, $forumslug)
{
	global $wpdb, $sfglobals;

	if (empty($forumid)) return '';

	$url = $wpdb->get_var("SELECT forum_rss FROM ".SFFORUMS." WHERE forum_id=".$forumid);
	if (empty($url))
    {
        $rssopt = sf_get_option('sfrss');
        if ($rssopt['sfrssfeedkey'])
            $url = sf_build_qurl('forum='.$forumslug, 'xfeed=forum', 'feedkey='.$sfglobals['member']['feedkey']);
        else
            $url = sf_build_qurl('forum='.$forumslug, 'xfeed=forum');
    }

	return $url;
}

# ------------------------------------------------------------------
# sf_get_topic_status_set()
#
# Returns the topic status set name for a forum
# 	$forumid:		forum to return
# ------------------------------------------------------------------
function sf_get_topic_status_set($forumid)
{
	global $wpdb;

	if(empty($forumid)) return '';
	return $wpdb->get_var(
			"SELECT topic_status_set FROM ".SFFORUMS." WHERE forum_id=".$forumid);
}

function sf_get_topic_status_from_forum($forumid, $statusflag)
{
	global $wpdb;

	if(!$forumid) return '';

	$flag='';
	$set=sf_get_topic_status_set($forumid);
	if($set != 0)
	{
		$flag=sf_get_topic_status_flag($set, $statusflag);
	}
	return $flag;
}

# ------------------------------------------------------------------
# sf_find_user_in_topic()
#
# Searches a topics posts to see if user has ever posted in it for
# the forums topic list icon
# 	$topicid:		topic to search
#	$userid:		user to look for
# %%FUTURE OPTIMISE%%
# ------------------------------------------------------------------
function sf_find_user_in_topic($topicid, $userid)
{
	global $wpdb;

	if(!$topicid || !$userid) return '';

	return $wpdb->get_col(
			"SELECT user_id
			 FROM ".SFPOSTS."
			 WHERE topic_id=".$topicid."
			 AND user_id=".$userid);
}

# ------------------------------------------------------------------
# sf_get_forum_from_topic()
#
# returng the firum id when only the topic is known
# 	$topicid:		topic to search
# ------------------------------------------------------------------

function sf_get_forum_from_topic($topicid)
{
	global $wpdb;

	if(!$topicid) return '';

	return $wpdb->get_var(
			"SELECT forum_id
			 FROM ".SFTOPICS."
			 WHERE topic_id=".$topicid);
}

# ------------------------------------------------------------------
# sf_get_forum_name()
#
# Returns forum name when only the slug is known
# 	$forumslug:		forum to return
# ------------------------------------------------------------------
function sf_get_forum_name($forumslug)
{
	global $wpdb;

	if(!$forumslug) return '';

	return $wpdb->get_var(
			"SELECT forum_name
			 FROM ".SFFORUMS."
			 WHERE forum_slug='".$forumslug."'");
}

# ------------------------------------------------------------------
# sf_get_forum_name_from_id()
#
# Returns forum name when only the slug is known
# 	$forumid:		forum to return
# ------------------------------------------------------------------
function sf_get_forum_name_from_id($forumid)
{
	global $wpdb;

	if(!$forumid) return '';

	return $wpdb->get_var(
			"SELECT forum_name
			 FROM ".SFFORUMS."
			 WHERE forum_id=".$forumid);
}

# ------------------------------------------------------------------
# sf_get_forum_slug()
#
# Returns forum slug when only the id is known
# 	$forumid:		forum to return
# ------------------------------------------------------------------
function sf_get_forum_slug($forumid)
{
	global $wpdb;

	if(!$forumid) return '';

	return $wpdb->get_var(
			"SELECT forum_slug
			 FROM ".SFFORUMS."
			 WHERE forum_id=".$forumid);
}

# ------------------------------------------------------------------
# sf_get_forum_id()
#
# Returns forum id when only the slug is known
# 	$forumslug:		forum to return
# ------------------------------------------------------------------

function sf_get_forum_id($forumslug)
{
	global $wpdb;

	if(!$forumslug) return '';

	return $wpdb->get_var(
			"SELECT forum_id
			 FROM ".SFFORUMS."
			 WHERE forum_slug='".$forumslug."'");
}

# ------------------------------------------------------------------
# sf_get_topics_forum_id()
#
# Returns forum id when only the topic id is known
# 	$topicid:		forum to return from topic record
# ------------------------------------------------------------------
function sf_get_topics_forum_id($topicid)
{
	global $wpdb;

	if(!$topicid) return '';

	return $wpdb->get_var(
			"SELECT forum_id
			 FROM ".SFTOPICS."
			 WHERE topic_id=".$topicid);
}

# ******************************************************************
# TOPIC/POST VIEW AND GENERAL DB FUNCTIONS
# ******************************************************************

# ------------------------------------------------------------------
# sf_get_combined_topics_and_posts()
#
# Returns a page of posts for specified topic
# 	$topicid:		topic and posts to load
# ------------------------------------------------------------------
function sf_get_combined_topics_and_posts($topicid)
{
	global $wpdb, $sfvars, $sfglobals;

	if(!$topicid) return '';

	# sadly have to grab the topic row first because we need obverride sort order if set
	$topic = $wpdb->get_row(
			"SELECT topic_id, topic_slug, ".SFTOPICS.".forum_id, topic_name, ".SFTOPICS.".post_count, topic_subs, topic_watches, topic_status, post_ratings, use_tags, blog_post_id,
			 forum_slug, forum_status, topic_status_set, topic_status_flag, user_id, use_tags
			 FROM ".SFTOPICS."
			 JOIN ".SFFORUMS." ON ".SFTOPICS.".forum_id = ".SFFORUMS.".forum_id
			 WHERE topic_id = ".$topicid.";", ARRAY_A);

	# quick permission check
 	if(!sf_can_view_forum($topic['forum_id'])) return '';

	# grab the tags if enabled for this forum
	if ($topic['use_tags'])
	{
		$sql = "SELECT ".SFTAGS.".tag_name, tag_slug
		FROM ".SFTAGMETA."
	 	JOIN ".SFTAGS." ON ".SFTAGMETA.".tag_id = ".SFTAGS.".tag_id
		WHERE ".SFTAGMETA.".topic_id = ".$topicid;
		$topic['tags'] = $wpdb->get_results($sql);
	}

	# now for the posts
	$ORDER="ASC"; # default
	if($sfglobals['display']['posts']['sortdesc']) $ORDER="DESC"; # global override

	$ppaged=$sfglobals['display']['posts']['perpage'];
	if(!$ppaged) $ppaged=20;

	if($sfvars['page'] == 1 ? $startlimit = 0 : $startlimit = ((($sfvars['page']-1) * $ppaged)));

	$topic['topic_page'] = $sfvars['page'];

	$tpages = ($topic['post_count'] / $ppaged);
	if(!is_int($tpages))
	{
		$tpages = intval($topic['post_count'] / $ppaged) +1;
	}
	$topic['topic_total_pages'] = $tpages;

	$LIMIT = ' LIMIT '.$startlimit.', '.$ppaged;

	$records = $wpdb->get_results(
			"SELECT ".SFPOSTS.".post_id, post_content, ".sf_zone_datetime('post_date').", UNIX_TIMESTAMP(post_date) as udate, ".SFPOSTS.".user_id, guest_name, guest_email,
			 post_status, post_pinned, post_index, post_edit,
			".SFMEMBERS.".display_name, admin, posts, signature, avatar, pm,
			 user_url, user_email, rating_id, vote_count, ratings_sum, ips, members
			 FROM ".SFPOSTS."
			 LEFT JOIN ".SFUSERS." ON ".SFPOSTS.".user_id = ".SFUSERS.".ID
			 LEFT JOIN ".SFMEMBERS." ON ".SFPOSTS.".user_id = ".SFMEMBERS.".user_id
			 LEFT JOIN ".SFPOSTRATINGS." ON ".SFPOSTRATINGS.".post_id = ".SFPOSTS.".post_id
			 WHERE topic_id = ".$topicid."
			 ORDER BY post_pinned DESC, ".SFPOSTS.".post_id ".$ORDER.$LIMIT, ARRAY_A);

	$topic['posts'] = $records;

	return $topic;
}

# ------------------------------------------------------------------
# sf_get_topic_record()
#
# Returns a single topic row
# 	$topicid:		topic_id of topic to return
# Note: No permission checking is performed
# ------------------------------------------------------------------
function sf_get_topic_record($topicid)
{
	global $wpdb, $session_topics;

	if(!$topicid) return '';

	# see if in the current session topics cache
	if($session_topics)
	{
		foreach($session_topics as $topic)
		{
			if($topic->topic_id == $topicid)
			{
				return $topic;
			}
		}
	}
	$sql=(
			"SELECT *
			 FROM ".SFTOPICS."
			 WHERE topic_id=".$topicid.";");
	$thistopic = $wpdb->get_row($sql);
	$session_topics[] = $thistopic;
	return $thistopic;
}

# ------------------------------------------------------------------
# sf_get_topic_record_from_slug()
#
# Returns a single topic row
# 	$topicslug:		topic_slug of topic to return
# Note: No permission checking is performed
# ------------------------------------------------------------------
function sf_get_topic_record_from_slug($topicslug)
{
	global $wpdb, $session_topics;

	if(!$topicslug) return '';

	# see if in the current session topics cache
	if($session_topics)
	{
		foreach($session_topics as $topic)
		{
			if($topic->topic_slug == $topicslug)
			{
				return $topic;
			}
		}
	}
	$sql=(
			"SELECT *
			 FROM ".SFTOPICS."
			 WHERE topic_slug='".$topicslug."';");
	$thistopic = $wpdb->get_row($sql);
	$session_topics[] = $thistopic;
	return $thistopic;
}

# ------------------------------------------------------------------
# sf_get_topic_record_from_blogpostid()
#
# Returns a single topic row
# 	$blogpostid:		blog post id to see if any linked topics
# Note: No permission checking is performed
# ------------------------------------------------------------------
function sf_get_topic_record_from_blogpostid($postid)
{
	global $wpdb, $session_topics;

	if (!$postid) return '';

	$sql = (
			"SELECT *
			 FROM ".SFTOPICS."
			 WHERE blog_post_id=".$postid);
	$thistopic = $wpdb->get_row($sql);
	return $thistopic;
}

# ------------------------------------------------------------------
# sf_get_topics_all()
#
# Returns complete recordset of topics
# 	$id_only:		limit recordset to topic_id and slug only
#	$asArray:		returb list as an aray
# Note: No permission checking is performed
# ------------------------------------------------------------------
function sf_get_topics_all($id_only=false, $asArray=false)
{
	global $wpdb;

	if($id_only ? $FROM='topic_id, topic_slug' : $FROM='*');

	$sql=("SELECT ".$FROM." FROM ".SFTOPICS);
	if($asArray) return $wpdb->get_results($sql, ARRAY_A);
	return $wpdb->get_results($sql);
}

# ------------------------------------------------------------------
# sf_topic_exists()
#
# Check the existence of a topic by id
# 	$topicid:		forum to check for
# ------------------------------------------------------------------
function sf_topic_exists($topicid)
{
	global $wpdb;

	if(empty($topicid)) return false;
	if($wpdb->get_var(
			"SELECT topic_name
			 FROM ".SFTOPICS."
			 WHERE topic_id=".$topicid))
	{
		return true;
	}
	return false;
}

# ------------------------------------------------------------------
# sf_get_topic_name()
#
# Returns topic name when only the topic slug is known
# 	$topicslug:		Topic to lookup
# ------------------------------------------------------------------
function sf_get_topic_name($topicslug)
{
	global $wpdb;

	if(!$topicslug) return '';

	return $wpdb->get_var(
			"SELECT topic_name
			 FROM ".SFTOPICS."
			 WHERE topic_slug='".$topicslug."'");
}

# ------------------------------------------------------------------
# sf_get_topic_slug()
#
# Returns topic slug when only the topic id is known
# 	$topicid:		Topic to lookup
# ------------------------------------------------------------------
function sf_get_topic_slug($topicid)
{
	global $wpdb;

	if(!$topicid) return '';

	return $wpdb->get_var(
			"SELECT topic_slug
			 FROM ".SFTOPICS."
			 WHERE topic_id=".$topicid);
}

# ------------------------------------------------------------------
# sf_get_topic_id()
#
# Returns topic id when only the topic slug is known
# 	$topicslug:		Topic to lookup
# ------------------------------------------------------------------
function sf_get_topic_id($topicslug)
{
	global $wpdb;

	if(!$topicslug) return '';

	return $wpdb->get_var(
			"SELECT topic_id
			 FROM ".SFTOPICS."
			 WHERE topic_slug='".$topicslug."'");
}

# ------------------------------------------------------------------
# sf_get_topic_name_from_id()
#
# Returns topic name when only the id is known
# 	$topicid:		topic to return
# ------------------------------------------------------------------
function sf_get_topic_name_from_id($topicid)
{
	global $wpdb;

	if(!$topicid) return '';

	return $wpdb->get_var(
			"SELECT topic_name
			 FROM ".SFTOPICS."
			 WHERE topic_id=".$topicid);
}


# ------------------------------------------------------------------
# sf_get_slugs_from_postid()
#
# Returns forum and topic slugs when only the post id is known
# 	$postid:		Post to lookup
# ------------------------------------------------------------------
function sf_get_slugs_from_postid($postid)
{
	global $wpdb;

	if(!$postid) return '';

	return $wpdb->get_row(
			"SELECT forum_slug, topic_slug, post_index
			 FROM ".SFPOSTS."
			 JOIN ".SFFORUMS." ON ".SFPOSTS.".forum_id = ".SFFORUMS.".forum_id
			 JOIN ".SFTOPICS." ON ".SFPOSTS.".topic_id = ".SFTOPICS.".topic_id
			 WHERE ".SFPOSTS.".post_id=".$postid.";");
}

# ------------------------------------------------------------------
# sf_get_posts_count_in_topic()
#
# Returns the post count from topic record
# 	$topicid:		Topic to lookup
# ------------------------------------------------------------------

function sf_get_posts_count_in_topic($topicid)
{
	global $wpdb;
	if(empty($topicid)) return 0;
	return $wpdb->get_var(
			"SELECT post_count
			 FROM ".SFTOPICS."
			 WHERE topic_id=".$topicid);
}

# ------------------------------------------------------------------
# sf_get_posts_count_in_linked_topic()
#
# Returns the post count of approved posts in topic
# 	$topicid:		Topic to lookup
#	$hidedupes		Hide duplicate comments
# ------------------------------------------------------------------
function sf_get_posts_count_in_linked_topic($topicid, $hidedupes)
{
	global $wpdb;
	if(empty($topicid)) return 0;

	$hide='';
	if($hidedupes) $hide = " AND comment_id IS NULL";

	return $wpdb->get_var(
		"SELECT COUNT(post_id)
		 FROM ".SFPOSTS."
		 WHERE topic_id=".$topicid." AND post_status=0".$hide);
}

function sf_update_topic_status_flag($statvalue, $topicid)
{
	global $wpdb;

	if(!$statvalue || !$topicid) return '';

	return $wpdb->query("UPDATE ".SFTOPICS." SET topic_status_flag=".$statvalue." WHERE topic_id=".$topicid);
}

# ******************************************************************
# NEW/UNREAD POST VIEWS DB FUNCTIONS
# ******************************************************************

# ------------------------------------------------------------------
# sf_get_admins_queued_posts()
#
# Returns the admins new post view
# ------------------------------------------------------------------
function sf_get_admins_queued_posts()
{
	global $wpdb, $sfvars;

	$newposts = '';
	$clause = '';

	$records = $wpdb->get_results(
			"SELECT ".SFWAITING.".forum_id, forum_slug, forum_name, topic_status_set, topic_id, ".SFWAITING.".post_count, ".SFWAITING.".post_id
			 FROM ".SFWAITING."
			 LEFT JOIN ".SFFORUMS." ON ".SFWAITING.".forum_id = ".SFFORUMS.".forum_id
			 ORDER BY forum_id;");

	if($records)
	{
		# now grab all of the post record we are going to need in one query
		$pcount = count($records);
		$done = 0;
		foreach ($records as $record)
		{
			$clause.= "(".SFTOPICS.".topic_id = ".$record->topic_id." AND ".SFPOSTS.".post_id >= ".$record->post_id.")";
			$done++;
			if($done < $pcount) $clause.= " OR ";
		}

		$preparedpostrecords = $wpdb->get_results(
				"SELECT ".SFPOSTS.".topic_id, post_content, post_index, ".SFPOSTS.".post_id, post_status, ".SFPOSTS.".user_id, ".SFMEMBERS.".display_name, guest_name, topic_slug, topic_status_flag, topic_name
				 FROM ".SFPOSTS."
				 LEFT JOIN ".SFTOPICS." ON ".SFPOSTS.".topic_id = ".SFTOPICS.".topic_id
				 LEFT JOIN ".SFMEMBERS." ON ".SFPOSTS.".user_id = ".SFMEMBERS.".user_id
				 WHERE ".$clause."
				 ORDER BY post_id;");

		$sfvars['queue'] = count($preparedpostrecords);


		$newposts = array();
		$findex=-1;
		$pindex=0;
		$tindex=0;

		foreach($records as $record)
		{
			# Check this still has posts in it and they were not removed (it happens)
			$postrecords='';
			foreach($preparedpostrecords as $prepared)
			{
				if($prepared->topic_id == $record->topic_id)
				{
					$postrecords[] = $prepared;
				}
			}
			# So - were they removed? if so donlt add them to the array and remove them from sfwaiting

			if($postrecords == '')
			{
				sf_remove_from_waiting(true, $record->topic_id, $record->post_id);
				continue;
			}

			$forumid=$record->forum_id;
			if($findex == -1 || $newposts[$findex]['forum_id'] != $forumid)
			{
				$findex++;
				$tindex=0;
				$pindex=0;
				$newposts[$findex]['forum_id']=$record->forum_id;
				$newposts[$findex]['forum_name']=$record->forum_name;
				$newposts[$findex]['forum_slug']=$record->forum_slug;
				$newposts[$findex]['topic_status_set']=$record->topic_status_set;
			}

			$newposts[$findex]['topics'][$tindex]['topic_id']=$record->topic_id;
			$newposts[$findex]['topics'][$tindex]['post_id']=$record->post_id;

			# isolate the post records for current topic
			$postrecords='';
			foreach($preparedpostrecords as $prepared)
			{
				if($prepared->topic_id == $record->topic_id)
				{
					$postrecords[] = $prepared;
				}
			}

			if($postrecords)
			{
				$newposts[$findex]['topics'][$tindex]['post_count']=count($postrecords);
				$pindex=0;
				foreach($postrecords as $postrecord)
				{
					$newposts[$findex]['topics'][$tindex]['topic_slug']=$postrecord->topic_slug;
					$newposts[$findex]['topics'][$tindex]['topic_status_flag']=$postrecord->topic_status_flag;
					$newposts[$findex]['topics'][$tindex]['topic_name']=$postrecord->topic_name;

					$newposts[$findex]['topics'][$tindex]['posts'][$pindex]['post_id']=$postrecord->post_id;
					$newposts[$findex]['topics'][$tindex]['posts'][$pindex]['post_status']=$postrecord->post_status;
					$newposts[$findex]['topics'][$tindex]['posts'][$pindex]['post_index']=$postrecord->post_index;
					$newposts[$findex]['topics'][$tindex]['posts'][$pindex]['post_content']=$postrecord->post_content;
					$newposts[$findex]['topics'][$tindex]['posts'][$pindex]['user_id']=$postrecord->user_id;
					if(empty($postrecord->user_id))
					{
						$newposts[$findex]['topics'][$tindex]['posts'][$pindex]['display_name']=$postrecord->guest_name;
						$thisuser = 'Guest';
					} else {
						$newposts[$findex]['topics'][$tindex]['posts'][$pindex]['display_name']=$postrecord->display_name;
						$thisuser = 'Member';
					}
					$newposts[$findex]['topics'][$tindex]['posts'][$pindex]['user_type']=$thisuser;
					$pindex++;
				}
			}
			$tindex++;
		}
	}

	# if no new posts then housekeep sfwaiting
	if(!$newposts) $wpdb->query("TRUNCATE ".SFWAITING);

	return $newposts;
}

# ------------------------------------------------------------------
# sf_combined_new_posts_list()
#
# Extend new post list to include all required data
#	$posts:		Posts array (forumid and topicid)
#	$forumsort	Sort list into group/forum sequence
# ------------------------------------------------------------------
function sf_combined_new_posts_list($posts, $forumsort=false)
{
	global $wpdb;

	$clause='';
	$record='';

	if($posts)
	{
		$pcount = count($posts);
		$done = 0;

		foreach ($posts as $post)
		{
			if (sf_can_view_forum($post->forum_id, true))
			{
                if ($clause != '') $clause.= ' OR ';
				$clause.= "(".SFTOPICS.".forum_id=".$post->forum_id." AND ".SFTOPICS.".topic_id=".$post->topic_id.")";
				$done++;
			}
        }

		# just return if nothing visible
		if ($clause == '') return '';

		if($forumsort)
		{
			$groupjoin = " JOIN ".SFGROUPS." ON ".SFFORUMS.".group_id = ".SFGROUPS.".group_id ";
			$orderby = " ORDER BY group_seq, forum_seq";
			$groupname = ', group_name';
		} else {
			$groupjoin = '';
			$orderby = ' ORDER BY post_id DESC';
			$groupname = '';
		}

		$record = $wpdb->get_results(
				"SELECT ".SFFORUMS.".forum_id, forum_name, forum_slug,
				 topic_name, topic_slug, ".SFTOPICS.".post_id, ".SFTOPICS.".topic_id, topic_status_set,
				 post_status, UNIX_TIMESTAMP(".SFPOSTS.".post_date) as udate, post_index, ".SFPOSTS.".user_id ".$groupname."
				 FROM ".SFFORUMS."
				 JOIN ".SFTOPICS." ON ".SFFORUMS.".forum_id = ".SFTOPICS.".forum_id
				 LEFT JOIN ".SFPOSTS." ON ".SFTOPICS.".post_id = ".SFPOSTS.".post_id
				 ".$groupjoin."
				 WHERE ".$clause.$orderby, ARRAY_A);
	}
	return $record;
}

# ------------------------------------------------------------------
# sf_get_users_new_post_list()
#
# Returns recordset of current users new-post-list
# 	$limit:		limit to x number of records
# ------------------------------------------------------------------
function sf_get_users_new_post_list($limit)
{
	global $current_user, $sfglobals;

	if ($current_user->member)
	{
		$newpostlist = $sfglobals['member']['newposts'];
		if($current_user->newpostlist == false)
		{
			$newpostlist = sf_update_users_newposts($newpostlist);
		}

		# we have a live user so construct SQL if anything in newpostslist
		if ($newpostlist['topics'][0] != 0)
		{
			$wanted = $limit;
			$where = ' WHERE';
			if (count($newpostlist['topics']) < $limit) $limit = count($newpostlist['topics']);
			for ($x=0; $x<$limit; $x++)
			{
				$where.= " topic_id=".$newpostlist['topics'][$x];
				if ($x != $limit-1) $where.= " OR";
			}

			$recordset1 = sf_get_users_new_post_list_db($where, '');

			# try and marry the extra count if not enough to satisfy $limit
			if ($limit < $wanted)
			{
				$limit = " LIMIT ".$wanted;
				$where = " WHERE post_status = 0 ";
				$recordset2 = sf_get_users_new_post_list_db($where, $limit);
				if ($recordset2)
				{
					for ($x=0; $x<count($recordset2); $x++)
					{
						if (!in_array($recordset2[$x]->topic_id, $newpostlist['topics']))
						{
							$recordset1[]=$recordset2[$x];
						}
						if (count($recordset1) == $wanted) break;
					}
				}
			}
			return sf_filter_new_post_list($recordset1);
		}
	}
	# but if not a member, empty post list of members query didnlt reach limit...
	if ($current_user->guest || $newpostlist['topics'][0] == 0)
	{
		$limit = " LIMIT ".$limit;
		$where = " WHERE post_status = 0 ";
		$recordset1 = sf_get_users_new_post_list_db($where, $limit);
		return sf_filter_new_post_list($recordset1);
	}
}

# ------------------------------------------------------------------
# sf_get_users_new_post_list_db()
#
# Support: Returns recordset of current users new-post-list
#	$where:		Option where clause on topic id
# 	$limit:		limit to x number of records
# ------------------------------------------------------------------
function sf_get_users_new_post_list_db($where, $limit)
{
	global $wpdb, $current_user;

	$where2 = '';
	if (!$current_user->forumadmin)
	{
		# limit to viewable forums based on permissions
		$forums = sf_get_forum_memberships($current_user->ID);
		if ($forums)
		{
			$forum_ids = '';
			foreach ($forums as $forum)
			{
				if (sf_can_view_forum($forum->forum_id))
				{
					$forum_ids[] = $forum->forum_id;
				}
			}
		} else {
			return '';
		}

		# create where clause based on forums that current user can view
		if ($forum_ids != '')
		{
            if ($where != '')
            {
			    $where2 = " AND ";
            } else {
                $where2 = " WHERE ";
            }
            $where2.= SFPOSTS.".forum_id IN (" . implode(",", $forum_ids) . ") = 1 ";
		} else {
            return '';
		}
	}

	$records = $wpdb->get_results(
			"SELECT DISTINCT forum_id, topic_id
			 FROM ".SFPOSTS
			 .$where.$where2."
			 ORDER BY post_id DESC"
			 .$limit.";");

	return $records;
}

# ------------------------------------------------------------------
# sf_filter_new_post_list()
#
# Support: Returns filtered list that current user has permissions to
#	$recordset:	Full list of forum/topics
# ------------------------------------------------------------------
function sf_filter_new_post_list($recordset)
{
	if(!$recordset) return '';

	$rlist = array();
	$x = 0;

	foreach($recordset as $record)
	{
		$rlist[$x]->forum_id=$record->forum_id;
		$rlist[$x]->topic_id=$record->topic_id;
		$x++;
	}
	return $rlist;
}

# ******************************************************************
# NEW POSTS FROM WAITING - DASHBOARD
# ******************************************************************

# ------------------------------------------------------------------
# sf_get_unread_forums()
#
# Returns list from the waiting table (Admins queue)
# ------------------------------------------------------------------
function sf_get_unread_forums()
{
	global $wpdb;

	return $wpdb->get_results(
			"SELECT topic_id, ".SFWAITING.".forum_id, forum_slug, forum_name, group_id, ".SFWAITING.".post_count, ".SFWAITING.".post_id, topic_status_set
			 FROM ".SFFORUMS."
			 JOIN ".SFWAITING." ON ".SFFORUMS.".forum_id = ".SFWAITING.".forum_id
			 WHERE ".SFWAITING.".post_count > 0
			 ORDER BY forum_id, topic_id");
}

# ------------------------------------------------------------------
# sf_get_awaiting_approval()
#
# Count of posts currently awaiting moderation
# ------------------------------------------------------------------
function sf_get_awaiting_approval()
{
	global $wpdb;
	return $wpdb->get_var(
			"SELECT COUNT(post_id) AS cnt
			 FROM ".SFPOSTS."
			 WHERE post_status=1");
}

# ------------------------------------------------------------------
# sf_topic_in_queue()
#
# returns true if the topic is in the admin queue
# 	$topicid		topic being tested
# ------------------------------------------------------------------
function sf_topic_in_queue($topicid)
{
	global $wpdb;

	if(!$topicid) return '';

	return $wpdb->get_var(
			"SELECT post_count
			 FROM ".SFWAITING."
			 WHERE topic_id=".$topicid);
}

# ******************************************************************
# STATISTICS VIEWS DB FUNCTIONS
# ******************************************************************

# ------------------------------------------------------------------
# sf_get_online_total()
#
# Returns total number of users currently tagged as online
# ------------------------------------------------------------------
function sf_get_online_total()
{
	global $wpdb;
	return $wpdb->get_var("SELECT COUNT(id) FROM ".SFTRACK);
}

# ------------------------------------------------------------------
# sf_get_members_online()
#
# Returns list of members currently tagged as online
# ------------------------------------------------------------------
function sf_get_members_online()
{
	global $wpdb;

	return $wpdb->get_results("
			SELECT trackuserid, display_name, forum_id, topic_id FROM ".SFTRACK."
			JOIN ".SFMEMBERS." ON ".SFTRACK.".trackuserid = ".SFMEMBERS.".user_id
			ORDER BY trackuserid");
}

# ------------------------------------------------------------------
# sf_is_online()
#
# Returns true if member is currently tagged as online
# ------------------------------------------------------------------
function sf_is_online($userid)
{
	global $wpdb, $session_online;

	if(!$userid) return '';

	if(!isset($session_online))
	{
		$session_online = $wpdb->get_col("SELECT trackuserid FROM ".SFTRACK);
	}
	if(in_array($userid, $session_online)) return true;
	return false;
}


# ------------------------------------------------------------------
# sf_get_stats_counts()
#
# Returns stats on group/forum/topic/post count
# ------------------------------------------------------------------
function sf_get_stats_counts()
{
	global $wpdb;

	$cnt->groups = 0;
	$cnt->forums = 0;
	$cnt->topics = 0;
	$cnt->posts = 0;

	$groupid='';

	$forums = $wpdb->get_results("SELECT group_id, forum_id, topic_count, post_count FROM ".SFFORUMS." ORDER BY group_id");
	if($forums)
	{
		foreach($forums as $forum)
		{
			if($forum->group_id != $groupid)
			{
				$groupid=$forum->group_id;
				$cnt->groups++;
			}
			$cnt->forums++;
			$cnt->topics+=$forum->topic_count;
			$cnt->posts+=$forum->post_count;
		}
	}
	return $cnt;
}

# ------------------------------------------------------------------
# sf_get_post_stats()
#
# Returns stats on posts (admins/moderators and members and updates
# the guest count
# ------------------------------------------------------------------
function sf_get_post_stats()
{
	global $wpdb;

	$sfcontrols = sf_get_option('sfcontrols');

	# if the hour has passed or been reset
	if($sfcontrols['hourflag'] != date('G'))
	{
		# get admins and moderators
		$records1 = $wpdb->get_results("
			SELECT user_id, display_name, posts, admin, moderator
			FROM ".SFMEMBERS."
			WHERE admin=1 OR moderator=1
			ORDER BY admin DESC, moderator DESC, posts DESC;");

		# now get members
		if(empty($sfcontrols['showtopcount']) || $sfcontrols['showtopcount']==0) $sfcontrols['showtopcount']=6;
		$records2 = $wpdb->get_results("
			SELECT SQL_CALC_FOUND_ROWS user_id, display_name, posts, admin, moderator
			FROM ".SFMEMBERS."
			WHERE admin=0 AND moderator=0
			ORDER BY posts DESC
			LIMIT 0,".$sfcontrols['showtopcount'].";");

		# save the members count
		$sfcontrols['membercount'] = $wpdb->get_var("SELECT FOUND_ROWS()");

		# Finally get guests
		$guests = $wpdb->get_col("
			SELECT DISTINCT guest_name
			FROM ".SFPOSTS."
			WHERE guest_name !='';");

		# save the guest count
		$sfcontrols['guestcount'] = count($guests);

		# merge and save the records
		$stats = array_merge($records1, $records2);
		$sfcontrols['statslist'] = $stats;

		# reset the hour flag and save the controls
		$sfcontrols['hourflag'] = date('G');
		sf_update_option('sfcontrols', $sfcontrols);
	} else {
		$stats = $sfcontrols['statslist'];
	}
	# return results
	return $stats;
}

# ------------------------------------------------------------------
# sf_guests_browsing()
#
# Calculates how many guests are browsing current forum or topic
# ------------------------------------------------------------------
function sf_guests_browsing()
{
	global $sfvars, $wpdb;

	$where='';

	if($sfvars['pageview']=='forum') $where = "forum_id=".$sfvars['forumid'];
	if($sfvars['pageview']=='topic') $where = "topic_id=".$sfvars['topicid'];
	if($where=='') return;

	return $wpdb->get_var("
			SELECT COUNT(id)
			FROM ".SFTRACK." WHERE trackuserid=0 AND ".$where);
}

# ------------------------------------------------------------------
# sf_update_max_online()
#
# Updates max online setting if exceeded and returns value
# ------------------------------------------------------------------
function sf_update_max_online($current)
{
	$sfcontrols = sf_get_option('sfcontrols');
	$max = $sfcontrols['maxonline'];
	if(empty($max)) $max = 0;

	if($current > $max)
	{
		$sfcontrols['maxonline'] = $current;
		sf_update_option('sfcontrols', $sfcontrols);
		return $current;
	}
	return $max;
}

# ******************************************************************
# FULL TOPIC SEARCH VIEW DB FUNCTIONS
# ******************************************************************

# ------------------------------------------------------------------
# sf_get_combined_full_topic_search()
#
# Grabs all forums and their topics in the search result.
# Also performs the full search for a members postings
# ------------------------------------------------------------------
function sf_get_combined_full_topic_search()
{
	global $wpdb, $sfvars, $current_user, $sfglobals;

	$sfvars['searchresults'] = 0;

	# how many topics per page?
	$tpaged = $sfglobals['display']['topics']['perpage'];
	if(!$tpaged) $tpaged=20;

	if($sfvars['searchpage'] == 1)
	{
		$startlimit = 0;
	} else {
		$startlimit = ((($sfvars['searchpage']-1) * $tpaged));
	}

	$LIMIT = ' LIMIT '.$startlimit.', '.$tpaged;
	$ORDER = ' ORDER BY topic_id DESC';

	# for admins search all forums, for users check permissions
	$where2 = '';
	if (!$current_user->forumadmin)
	{
		# limit to viewable forums based on permissions
		$forums = sf_get_forum_memberships($current_user->ID);
		if ($forums)
		{
			$forum_ids = '';
			foreach ($forums as $forum)
			{
				if (sf_can_view_forum($forum->forum_id))
				{
					$forum_ids[] = $forum->forum_id;
				}
			}
		} else {
			return '';
		}

		# create where clause based on forums that current user can view
		if ($forum_ids != '')
		{
			$where2 = " AND ".SFTOPICS.".forum_id IN (" . implode(",", $forum_ids) . ") ";
		} else {
            return '';
		}
	}

	$searchvalue=urldecode($sfvars['searchvalue']);
	if(empty($searchvalue))
	{
		return '';
	}

	if($sfvars['searchtype'] == 8) {

		# topics user has posted in
		$userid = $sfvars['searchvalue'];

		$records = sf_query_results(
				"SELECT SQL_CALC_FOUND_ROWS DISTINCT
				 ".SFTOPICS.".topic_id, topic_name, topic_slug, ".SFTOPICS.".forum_id, ".sf_zone_datetime('topic_date').", topic_status, topic_status_flag, ".SFTOPICS.".post_id, ".SFTOPICS.".post_count,
				 forum_name, forum_slug
				 FROM ".SFTOPICS."
				 JOIN ".SFFORUMS." ON ".SFTOPICS.".forum_id = ".SFFORUMS.".forum_id
				 JOIN ".SFPOSTS." ON ".SFTOPICS.".topic_id = ".SFPOSTS.".topic_id
				 WHERE ".SFPOSTS.".user_id = ".$userid.$where2.$ORDER.$LIMIT.";", ARRAY_A);

	} else if($sfvars['searchtype'] == 9) {

		# topics started by user
		$userid = $sfvars['searchvalue'];

		$records = sf_query_results(
				"SELECT SQL_CALC_FOUND_ROWS DISTINCT
				 ".SFTOPICS.".topic_id, topic_name, topic_slug, ".SFTOPICS.".forum_id, ".sf_zone_datetime('topic_date').", topic_status, topic_status_flag, ".SFTOPICS.".post_id, ".SFTOPICS.".post_count,
				 forum_name, forum_slug, post_content
				 FROM ".SFTOPICS."
				 JOIN ".SFFORUMS." ON ".SFTOPICS.".forum_id = ".SFFORUMS.".forum_id
				 JOIN ".SFPOSTS." ON ".SFTOPICS.".topic_id = ".SFPOSTS.".topic_id
				 WHERE ".SFPOSTS.".user_id = ".$userid.$where2." AND ".SFPOSTS.".post_index=1 ".$ORDER.$LIMIT.";", ARRAY_A);

	} elseif($sfvars['searchinclude'] == 4) {

		# it's a tag search
		$searchtag = sf_create_slug(urldecode($searchvalue), 'tag');
		$WHERE = SFTOPICS.".topic_id IN (SELECT topic_id FROM ".SFTAGMETA." JOIN ".SFTAGS." ON ".SFTAGMETA.".tag_id = ".SFTAGS.".tag_id
			WHERE tag_slug = '".$searchtag."') ";
			$records = sf_query_results(
				"SELECT SQL_CALC_FOUND_ROWS DISTINCT
				".SFTOPICS.".topic_id, topic_name, topic_slug, ".SFTOPICS.".forum_id, ".sf_zone_datetime('topic_date').", topic_status, topic_status_flag, ".SFTOPICS.".post_id, ".SFTOPICS.".post_count,
				forum_name, forum_slug, post_content
				FROM ".SFTOPICS."
				JOIN ".SFFORUMS." ON ".SFTOPICS.".forum_id = ".SFFORUMS.".forum_id
				JOIN ".SFPOSTS." ON ".SFTOPICS.".topic_id = ".SFPOSTS.".topic_id
				WHERE ".$WHERE." AND ".SFPOSTS.".post_index=1 ".$where2.$ORDER.$LIMIT.";", ARRAY_A);

	} else {

		$searchterm = sf_construct_search_term($searchvalue, $sfvars['searchtype']);

		switch($sfvars['searchinclude'])
		{
			case 1:
				$MATCH = "(MATCH(".SFPOSTS.".post_content) AGAINST ('".esc_sql(like_escape($searchterm))."' IN BOOLEAN MODE) OR MATCH(".SFTOPICS.".topic_name) AGAINST ('".esc_sql(like_escape($searchterm))."' IN BOOLEAN MODE)) ";
				break;
			case 2:
				$MATCH = "MATCH(".SFPOSTS.".post_content) AGAINST ('".esc_sql(like_escape($searchterm))."' IN BOOLEAN MODE) ";
				break;
			case 3:
				$MATCH = "MATCH(".SFTOPICS.".topic_name) AGAINST ('".esc_sql(like_escape($searchterm))."' IN BOOLEAN MODE) ";
				break;
		}

		$records = sf_query_results(
				"SELECT SQL_CALC_FOUND_ROWS DISTINCT
				 ".SFTOPICS.".topic_id, topic_name, topic_slug, ".SFTOPICS.".forum_id, ".sf_zone_datetime('topic_date').", topic_status, topic_status_flag, ".SFTOPICS.".post_id, ".SFTOPICS.".post_count,
				 forum_name, forum_slug, post_status
				 FROM ".SFTOPICS."
				 JOIN ".SFFORUMS." ON ".SFTOPICS.".forum_id = ".SFFORUMS.".forum_id
				 JOIN ".SFPOSTS." ON ".SFTOPICS.".topic_id = ".SFPOSTS.".topic_id
				 WHERE ".$MATCH.$where2.$ORDER.$LIMIT.";", ARRAY_A);
	}

	if($records)
	{
		$sfvars['searchresults'] = $wpdb->get_var("SELECT FOUND_ROWS()");

		$sfdisplay=array();
		$sfdisplay=sf_get_option('sfdisplay');

		# do we need a post tooltip on the topic link? (Not possible on topics user hs posted in type search)
		if($sfdisplay['topics']['posttip'] && $sfvars['searchtype'] != 8 && $current_user->sfaccess)
		{
			for($x=0; $x<count($records); $x++)
			{
				if (sf_can_view_forum($records[$x]['forum_id']))
				{
					$records[$x]['post_tip']=sf_filter_tooltip_display($records[$x]['post_content'], $records[$x]['post_status']);
					$records[$x]['post_content']='';
				}
			}
		}
	}

	return $records;
}

# ******************************************************************
# USER RELATED DB FUNCTIONS
# ******************************************************************

# ------------------------------------------------------------------
# sf_create_member_data()
#
# Filter Call
# On user registration sets up the new 'members' data row
#	$userid:		Passed in to filter
# ------------------------------------------------------------------
function sf_create_member_data($userid)
{
	global $wpdb;

	if(!$userid) return '';

	# Grab the data we need
	$sfprofile = sf_get_option('sfprofile');
	$user = $wpdb->get_row("SELECT user_login, display_name FROM ".SFUSERS." WHERE ID=".$userid);

	# Display Name validation
	$display_name = '';

	# use first and last name (or just one if only one available)
	if($sfprofile['nameformat'] == 3)
	{
		$first = get_user_meta($userid, 'first_name', true);
		$last  = get_user_meta($userid, 'last_name', true);
		if(!empty($first) || !empty($last))
		{
			$display_name = trim($first.' '.$last);
		}
	}

	# use the login name (which is also the default if 1 or 3 fail)
	if(empty($display_name) || $sfprofile['nameformat'] == 2)
	{
		$display_name = $user->user_login;
	}

	# use the WP recorded display name
	if($sfprofile['nameformat'] == 1)
	{
		$display_name = $user->display_name;
	}

	# use default login name if all else failed - this MUST exist
	if(empty($display_name)) $display_name = $user->user_login;
	$display_name = sf_filter_name_save($display_name);

	$admin = 0;
	$moderator = 0;
	$pm = 0;
	$avatar = 'a:1:{s:8:"uploaded";s:0:"";}';
	$signature = '';
	$posts = -1;
	$lastvisit = current_time('mysql');
	$checktime = current_time('mysql');
	$subscribe = '';
	$buddies = '';
	$watches = '';
	$newposts = '';
	$posts_rated = '';
	$admin_options = '';

    $useropts = array();
	$sfmemberopts = sf_get_option('sfmemberopts');
    if ($sfmemberopts['sfautosub'])
    {
        $useropts['autosubpost'] = 1;
    } else {
        $useropts['autosubpost'] = 0;
    }
    $useropts['hidestatus'] = 0;
    $useropts['timezone'] = sf_get_option('sfzone');
    if(empty($useropts['timezone'])) $useropts['timezone']=0;
    $useropts['editor'] = 1;
    $useropts['pmemail'] = 1;
    $useropts['namesync'] = 1;
	$sfmemberopts = sf_get_option('sfmemberopts');
	if ($sfmemberopts['sfautosub']) $useropts['autosubpost'] = 1;

	$user_options = serialize($useropts);

    # generate feedkey
    $feedkey = sprintf( '%04x%04x-%04x-%04x-%04x-%04x%04x%04x',
                mt_rand( 0, 0xffff ), mt_rand( 0, 0xffff ), mt_rand( 0, 0xffff ),
                mt_rand( 0, 0x0fff ) | 0x4000,
                mt_rand( 0, 0x3fff ) | 0x8000,
                mt_rand( 0, 0xffff ), mt_rand( 0, 0xffff ), mt_rand( 0, 0xffff ) );

	# save initial record
	$sql = "INSERT INTO ".SFMEMBERS."
		(user_id, display_name, admin, moderator, pm, avatar, signature, posts, lastvisit, checktime, subscribe, buddies, watches, newposts, posts_rated, admin_options, user_options, feedkey)
		VALUES
		({$userid}, '{$display_name}', {$admin}, {$moderator}, {$pm}, '{$avatar}', '{$signature}', {$posts}, '{$lastvisit}', '{$checktime}', '{$subscribe}', '{$buddies}', '{$watches}', '{$newposts}', '{$posts_rated}', '{$admin_options}', '{$user_options}', '{$feedkey}')";
	$wpdb->query($sql);

	# insert new user into usergroup based on wp role
	sf_map_role_to_ug($userid);

	return;
}

# ------------------------------------------------------------------
# sf_update_member_data()
#
# Filter Call
# On user wp profile updates, check if any spf stuff needs updating
#	$userid:		Passed in to filter
# ------------------------------------------------------------------
function sf_update_member_data($userid)
{
	global $wpdb;

	if(!$userid) return '';

	# are we syncing display names between WP and SPF?
	$options = sf_get_member_item($userid, 'user_options');
	if ($options['namesync'] == true)
	{
		$display_name = $wpdb->get_var("SELECT display_name FROM ".SFUSERS." WHERE ID=".$userid);
		sf_update_member_item($userid, 'display_name', $display_name);
	}

	return;
}

# ------------------------------------------------------------------
# sf_map_role_to_ug()
#
# helper function
# update usergroup memberships based on wp role
#	$userid:		id of user to check memberships
# ------------------------------------------------------------------
function sf_map_role_to_ug($userid, $role='')
{
    # make sure user has been created first since wp role hook fires before create hook
	if (!$userid || !sf_get_member_item($userid, 'user_id')) return '';

	# get the user's wp role
    if (empty($role))
    {
    	$user = new WP_User($userid);
    	$roles = $user->roles;
    } else {
        $roles = (array) $role;
    }

	# grab the user group for that wp role
	# if the role doesnt have a mapping, use the defaul user group for new members
    foreach ($roles as $role)
    {
    	$value = sf_get_sfmeta('default usergroup', $role);
    	if (!empty($value))
    	{
    		$ug = $value[0]['meta_value'];
    	} else {
    		$value = sf_get_sfmeta('default usergroup', 'sfmembers');
    		$ug = $value[0]['meta_value'];
    	}

    	sfc_add_membership($ug, $userid);
    }

	return;
}

# ------------------------------------------------------------------
# sf_delete_member_data()
#
# Filter Call
# On user deletion remove 'members' data row
#	$userid:		Passed in to filter
# ------------------------------------------------------------------
function sf_delete_member_data($userid)
{
	global $wpdb;

	if(!$userid) return '';

	# 1: get users email address
	$user_email = $wpdb->get_var("SELECT user_email FROM ".SFUSERS." WHERE ID=".$userid);

	# 2: get the users display name from members table
	$display_name = sf_get_member_item($userid, 'display_name');

	# 3: Set user name and email to guest name and meail in all of their posts
	$wpdb->query("UPDATE ".SFPOSTS." SET user_id=NULL, guest_name='".$display_name."', guest_email='".$user_email."' WHERE user_id=".$userid);

	# 4: Remove PM messages
	$wpdb->query("DELETE FROM ".SFMESSAGES." WHERE to_id=".$userid." OR from_id=".$userid);

	# 5: Remove subscriptions
	$subs = sf_get_member_item($userid, 'subscribe');
	if (!empty($subs))
	{
		foreach ($subs as $sub)
		{
			sf_remove_subscription($sub, $userid);
		}
	}

	# 6: Remove watches
	$watches = sf_get_member_item($userid, 'watches');
	if (!empty($watches))
	{
		foreach ($watches as $watch)
		{
			sf_remove_watch($watch, $userid);
		}
	}

	# 7: Remove posts rated
	$ratings = sf_get_member_item($userid, 'posts_rated');
	if (!empty($ratings))
	{
		foreach ($ratings as $postid)
		{
			sf_remove_postrated($postid, $userid);
		}
	}

    # 8: Remove from recent members list if present
    sf_remove_newuser($userid);

	# 8: Remove from Members table
	$wpdb->query("DELETE FROM ".SFMEMBERS." WHERE user_id=".$userid);

	# 9: Remove user group memberships
	$wpdb->query("DELETE FROM ".SFMEMBERSHIPS." WHERE user_id=".$userid);

	return;
}

# ------------------------------------------------------------------
# sf_track_logout()
#
# Filter Call
# Sets up the last visited upon user logout
# ------------------------------------------------------------------
function sf_track_logout()
{
	global $wpdb, $current_user;

	# re-use this for updating lastvisit (time at logout)
	sf_set_last_visited($current_user->ID);
	$wpdb->query("DELETE FROM ".SFTRACK." WHERE trackuserid=".$current_user->ID);
	sf_destroy_users_newposts($current_user->ID);

	return;
}

# ------------------------------------------------------------------
# sf_get_user_id_from_display_name()
#
# returns the id of a user
#	$display_name:		User to lookup
# ------------------------------------------------------------------
function sf_get_user_id_from_display_name($display_name)
{
	global $wpdb;

	if(!$display_name) return '';

	return $wpdb->get_var(
			"SELECT user_id
			 FROM ".SFMEMBERS."
			 WHERE display_name='".$display_name."'");
}

# ------------------------------------------------------------------
# sf_get_user_id_from_user_login()
#
# returns the id of a user
#	$user_login:		User to lookup
# ------------------------------------------------------------------
function sf_get_user_id_from_user_login($login_name)
{
	global $wpdb;

	if(!$login_name) return '';

	return $wpdb->get_var(
			"SELECT ID
			 FROM ".SFUSERS."
			 WHERE user_login='".$login_name."'");
}

# ------------------------------------------------------------------
# sf_is_subscribed()
#
# determine if user already subscribed to topic
#	$userid:		User being looked up
#	$topicid:		Topic subscribed to
# ------------------------------------------------------------------
function sf_is_subscribed($userid, $topicid)
{
	global $wpdb;

	if(!$userid || !$topicid) return '';

	$list = sf_get_member_item($userid, 'subscribe');
	if (empty($list))
	{
		return false;
	}
	$found = false;
	if (in_array($topicid, $list)) $found = true;
	return $found;
}

# ------------------------------------------------------------------
# sf_is_watching()
#
# determine if user already watcing a topic
#	$userid:		User being looked up
#	$topicid:		Topic watching
# ------------------------------------------------------------------
function sf_is_watching($userid, $topicid)
{
	global $wpdb;

	if(!$userid || !$topicid) return '';

	$list = sf_get_member_item($userid, 'watches');
	if (empty($list))
	{
		return false;
	}

	$found = false;
	if (in_array($topicid, $list)) $found = true;
	return $found;
}

# ******************************************************************
# SAVE ITEM FUNCTIONS
# ******************************************************************

# ------------------------------------------------------------------
# sf_save_edited_post()
#
# Saves a forum post following an edit in the UI
# Values in POST variables
# ------------------------------------------------------------------
function sf_save_edited_post()
{
	global $wpdb, $current_user;

	# post content
	$postcontent = $_POST['postitem'];

	sf_process_hook('sf_hook_pre_edit_post_save', array($_POST["pid"], $postcontent, $current_user->ID));

	$postcontent = sf_filter_content_save($postcontent, 'edit');

	# post edit array
	$postedit = array();
	$pedit = stripslashes($_POST['pedit']);
	if(!empty($pedit))
	{
		$postedit = unserialize($pedit);
	}
	$x = count($postedit);
	$edittime = current_time('mysql');
	$postedit[$x]['by'] = sf_filter_name_save($current_user->display_name);
	$postedit[$x]['at'] = strtotime($edittime);
	$postedit = serialize($postedit);

	$sql = "UPDATE ".SFPOSTS." SET post_content='".$postcontent."', post_edit='".$postedit."' WHERE post_id=".sf_esc_int($_POST["pid"]);

	if($wpdb->query($sql) === false)
	{
		update_sfnotice('sfmessage', '1@'.__("Update Failed!", "sforum"));
	} else {
		update_sfnotice('sfmessage', '0@'.__("Updated Post Saved", "sforum"));
	}

	# is it a blog linked topic post edit?
	if(sf_esc_int($_POST['blogpid']) && $_POST['sfedit'])
	{
		$ID = sf_esc_int($_POST['blogpid']);
		$post_content = $postcontent;

		$post = compact('ID', 'post_content');
		wp_update_post($post);
	}

	sf_process_hook('sf_hook_post_edit_post_save', array($_POST["pid"], $postcontent, $current_user->ID));

	return;
}

# ------------------------------------------------------------------
# sf_save_edited_topic()
#
# Saves a topic title following an edit in the UI
# Values in POST variables
# ------------------------------------------------------------------
function sf_save_edited_topic()
{
	global $wpdb, $sfglobals;

	$topicname = sf_filter_title_save($_POST['topicname']);

	if(empty($_POST['topicslug']))
	{
		$topicslug = sf_create_slug(sf_esc_str($_POST['topicname']), 'topic');
		if(empty($topicslug)) $topicslug = 'topic-'.sf_esc_int($_POST['tid']);
	} else {
		$topicslug = sf_esc_str($_POST['topicslug']);
	}

	$sql = 'UPDATE '.SFTOPICS.' SET topic_name="'.$topicname.'", topic_slug="'.$topicslug.'" WHERE topic_id='.sf_esc_int($_POST['tid']);

	if($wpdb->query($sql) === false)
	{
		update_sfnotice('sfmessage', '1@'.__("Update Failed!", "sforum"));
	} else {
		update_sfnotice('sfmessage', '0@'.__("Updated Topic Title Saved", "sforum"));
	}
	return;
}

# ------------------------------------------------------------------
# sf_save_subscription()
#
# Saves a user subscription following an edit in the UI
# 	$topicid:		The topic being subscribed to
#	$userid:		The user (passed because used in upgrade flow)
#	$retmessage:	True/False: Whether to retrun message (for UI)
# ------------------------------------------------------------------
function sf_save_subscription($topicid, $userid, $retmessage)
{
	global $wpdb, $current_user;

	if(!$userid || !$topicid) return '';

	if (($current_user->guest) || ($current_user->sfsubscriptions == false)) return;

	# is user already subscribed to this topic?
	if (sf_is_subscribed($userid, $topicid))
	{
		if($retmessage)
		{
			update_sfnotice('sfmessage', '1@'.__('You are already subscribed to this topic', "sforum"));
			return;
		}
	}

	# OK  -subscribe them to the topic
	$list = $wpdb->get_var("SELECT topic_subs FROM ".SFTOPICS." WHERE topic_id=".$topicid);
	$list = unserialize($list);
	$list[] = $userid;
	$list = serialize($list);
	$wpdb->query("UPDATE ".SFTOPICS." SET topic_subs = '".$list."' WHERE topic_id=".$topicid);

	# plus note the topic against their usermeta record
	$list = sf_get_member_item($userid, 'subscribe');
	$list[] = $topicid;
	sf_update_member_item($userid, 'subscribe', $list);

	if($retmessage)
	{
		update_sfnotice('sfmessage', '0@'.__('Subscription added', "sforum"));
	}

	return;
}

# ------------------------------------------------------------------
# sf_save_watch()
#
# Saves a user watch topic request following an edit in the UI
# 	$topicid:		The topic being watched
#	$userid:		The user (passed because used in upgrade flow)
#	$retmessage:	True/False: Whether to retrun message (for UI)
# ------------------------------------------------------------------
function sf_save_watch($topicid, $userid, $retmessage)
{
	global $wpdb, $current_user;

	if(!$userid || !$topicid) return '';

	if (($current_user->guest) || ($current_user->sfwatch == false)) return;

	# is user already watching this topic?
	if (sf_is_watching($userid, $topicid))
	{
		if ($retmessage)
		{
			update_sfnotice('sfmessage', '1@'.__('You are already watching this topic', "sforum"));
			return;
		}
	}

	# OK - watch the topic (in members table)
	$list = sf_get_member_item($userid, 'watches');
	$list[] = $topicid;
	sf_update_member_item($userid, 'watches', $list);

	# OK  -subscribe them to the topic
	$list = $wpdb->get_var("SELECT topic_watches FROM ".SFTOPICS." WHERE topic_id=".$topicid);
	$list = unserialize($list);
	$list[] = $userid;
	$list = serialize($list);
	$wpdb->query("UPDATE ".SFTOPICS." SET topic_watches = '".$list."' WHERE topic_id=".$topicid);

	if ($retmessage)
	{
		update_sfnotice('sfmessage', '0@'.__('Topic watch added', "sforum"));
	}

	return;
}

# ------------------------------------------------------------------
# sf_end_watch()
#
# Update watches from the UI - current user
# Values in POST variables
# ------------------------------------------------------------------
function sf_end_watch()
{
	global $current_user, $wpdb, $sfglobals;

	$topic = sf_esc_int($_GET['topic']);

	# remove current watch from member
	$list = $sfglobals['member']['watches'];
	if (!empty($list))
	{
		$newlist = '';
		foreach($list as $topicid)
		{
			if ($topic != $topicid)
			{
				$newlist[] = $topicid;
			}
		}
		sf_update_member_item($current_user->ID, 'watches', $newlist);
	}

	#remove the topic watch
	$list = $wpdb->get_var("SELECT topic_watches FROM ".SFTOPICS." WHERE topic_id=".$topic);
	if (!empty($list))
	{
		$newlist = '';
		$list = unserialize($list);
		foreach ($list as $userid)
		{
			if ($current_user->ID != $userid)
			{
				$newlist[] = $userid;
			}
		}
		if ($newlist != '') $newlist = serialize($newlist);
		$list = $wpdb->query("UPDATE ".SFTOPICS." SET topic_watches ='".$newlist."' WHERE topic_id=".$topic);
	}

	update_sfnotice('sfmessage', '0@'.__('Watches Updated', "sforum"));

	return;
}

# ------------------------------------------------------------------
# sf_end_sub()
#
# Update watches from the UI - current user
# Values in POST variables
# ------------------------------------------------------------------
function sf_end_sub()
{
	global $current_user, $wpdb, $sfglobals;

	$topic = sf_esc_int($_GET['topic']);

	# remove current subscription from member
	$list = $sfglobals['member']['subscribe'];
	if (!empty($list))
	{
		$newlist = '';
		foreach($list as $topicid)
		{
			if ($topic != $topicid)
			{
				$newlist[] = $topicid;
			}
		}
		sf_update_member_item($current_user->ID, 'subscribe', $newlist);
	}

	#remove the topic subscription
	$list = $wpdb->get_var("SELECT topic_subs FROM ".SFTOPICS." WHERE topic_id=".$topic);
	if (!empty($list))
	{
		$newlist = '';
		$list = unserialize($list);
		foreach ($list as $userid)
		{
			if ($current_user->ID != $userid)
			{
				$newlist[] = $userid;
			}
		}
		if ($newlist != '') $newlist = serialize($newlist);
		$list = $wpdb->query("UPDATE ".SFTOPICS." SET topic_subs ='".$newlist."' WHERE topic_id=".$topic);
	}

	update_sfnotice('sfmessage', '0@'.__('Subscriptions Updated', "sforum"));

	return;
}

# ------------------------------------------------------------------
# sf_remove_watch($topic, $userid)
#
# removes the topic watch for the specified user
# $topic			topic to be removed
# $userid			user to have watch removed
# ------------------------------------------------------------------
function sf_remove_watch($topic, $userid)
{
	global $wpdb, $sfglobals;

	if(!$userid || !$topic) return '';

	# remove current watch from member
	$list = $sfglobals['member']['watches'];
	if (!empty($list))
	{
		$newlist = '';
		foreach($list as $topicid)
		{
			if ($topic != $topicid)
			{
				$newlist[] = $topicid;
			}
		}
		sf_update_member_item($userid, 'watches', $newlist);
	}

	#remove the topic subscription
	$list = $wpdb->get_var("SELECT topic_watches FROM ".SFTOPICS." WHERE topic_id=".$topic);
	if (!empty($list))
	{
		$newlist = '';
		$list = unserialize($list);
		foreach ($list as $user)
		{
			if ($userid != $user)
			{
				$newlist[] = $user;
			}
		}
		if ($newlist != '') $newlist = serialize($newlist);
		$list = $wpdb->query("UPDATE ".SFTOPICS." SET topic_watches ='".$newlist."' WHERE topic_id=".$topic);
	}

	return;
}

# ------------------------------------------------------------------
# sf_remove_user_watches($userid)
#
# removes all topic watches for the specified user
# $userid			user to have all watches removed
# ------------------------------------------------------------------
function sf_remove_user_watches($userid)
{
    global $current_user;

    # make sure user is doing this
    if ($userid == $current_user->ID)
    {
    	# Remove watches
    	$watches = sf_get_member_item($userid, 'watches');
    	if (!empty($watches))
    	{
    		foreach ($watches as $watch)
    		{
    			sf_remove_watch($watch, $userid);
    		}
    	}
    }
}

# ------------------------------------------------------------------
# sf_remove_user_subs($userid)
#
# removes all topic subs for the specified user
# $userid			user to have subscriptions removed
# ------------------------------------------------------------------
function sf_remove_user_subs($userid)
{
    global $current_user;

    # make sure user is doing this
    if ($userid == $current_user->ID)
    {
    	# Remove subscriptions
    	$subs = sf_get_member_item($userid, 'subscribe');
    	if (!empty($subs))
    	{
    		foreach ($subs as $sub)
    		{
    			sf_remove_subscription($sub, $userid);
    		}
    	}
    }
}

# ------------------------------------------------------------------
# sf_add_postrating_vote()
#
# Saves a user watch topic request following an edit in the UI
# 	$postid:		The post being voted on
# ------------------------------------------------------------------
function sf_add_postrating_vote($postid)
{
	global $current_user, $sfglobals;

	if(!$postid) return '';

	# record the post as voted (in members table)
	$list = $sfglobals['member']['posts_rated'];
	$list[] = $postid;
	sf_update_member_item($current_user->ID, 'posts_rated', $list);

	return;
}

# ------------------------------------------------------------------
# sf_remove_postrated($topic, $userid)
#
# removes the post rated id for the specified user
# $postid			postid to be removed
# $userid			user to have watche removed
# ------------------------------------------------------------------
function sf_remove_postrated($postid, $userid)
{
	global $wpdb;

	if(!$userid || !$postid) return '';

	#remove the member id from post rated
	$list = $wpdb->get_var("SELECT members FROM ".SFPOSTRATINGS." WHERE post_id=".$postid);
	if (!empty($list))
	{
		$newlist = null;
		$list = unserialize($list);
		foreach ($list as $user)
		{
			if ($userid != $user)
			{
				$newlist[] = $user;
			}
		}
		if ($newlist) $newlist = serialize($newlist);
		$list = $wpdb->query("UPDATE ".SFPOSTRATINGS." SET members ='".$newlist."' WHERE post_id=".$postid);
	}

	return;
}

# ------------------------------------------------------------------
# sf_move_topic()
#
# Move topic from one forum to another
# Values in POST variables
# ------------------------------------------------------------------
function sf_move_topic()
{
	global $wpdb, $current_user;

	if(!$current_user->sfmovetopics)
	{
		update_sfnotice('sfmessage', '1@'.__('Access Denied', "sforum"));
		return;
	}

	if(empty($_POST['forumid']))
	{
		update_sfnotice('sfmessage', '1@'.__('Destination Forum not Selected', "sforum"));
		return;
	}

	$currentforumid = sf_esc_int($_POST['currentforumid']);
	$currenttopicid = sf_esc_int($_POST['currenttopicid']);
	$targetforumid  = sf_esc_int($_POST['forumid']);

	# change topic record to new forum id
	$wpdb->query("UPDATE ".SFTOPICS." SET forum_id = ".$targetforumid." WHERE topic_id=".$currenttopicid);

	if($wpdb === false)
	{
		update_sfnotice('sfmessage', '1@'.__("Topic Move Failed", "sforum"));
		return;
	}

	# check in 'waiting' to see if there is an unread post in there and change forum id if there is
	$wpdb->query("UPDATE ".SFWAITING." SET forum_id = ".$targetforumid." WHERE topic_id=".$currenttopicid);

	# change posts record(s) to new forum
	$wpdb->query("UPDATE ".SFPOSTS." SET forum_id = ".$targetforumid." WHERE topic_id=".$currenttopicid);

	# rebuild forum counts for old and new forums
	sf_build_forum_index($currentforumid);
	sf_build_forum_index($targetforumid);

	# relink to new forum if blog linked topic
	sf_relink_topic($currenttopicid, $currenttopicid, $targetforumid);

	# Ok - do not like doing this but....
	# There seems to have been times when a new post is made to the old forum id so we will now double check...
	$checkposts = $wpdb->get_results("SELECT post_id FROM ".SFPOSTS." WHERE forum_id=".$currentforumid." AND topic_id=".$currenttopicid);
	if($checkposts)
	{
		# made after most were moved
		sf_move_topic();
	} else {
		if($wpdb === false)
		{
			update_sfnotice('sfmessage', '1@'.__("Topic Move Failed", "sforum"));
		} else {
			update_sfnotice('sfmessage', '0@'.__("Topic Moved", "sforum"));
		}
	}
	return;
}

# ------------------------------------------------------------------
# sf_move_post()
#
# Move post from one topic and create new topic -same/another forum
# Values in POST variables
# ------------------------------------------------------------------
function sf_move_post()
{
	global $wpdb, $current_user;

	if(!$current_user->sfmoveposts)
	{
		update_sfnotice('sfmessage', '1@'.__('Access Denied', "sforum"));
		return;
	}
	if(empty($_POST['forumid']))
	{
		update_sfnotice('sfmessage', '1@'.__("Post move abandoned - No Forum selected!", "sforum"));
		return;
	}
	if(empty($_POST['newtopicname']))
	{
		update_sfnotice('sfmessage', '1@'.__("Post move abandoned - No Topic Defined!", "sforum"));
		return;
	}
	# extract data from POST
	$postid     = sf_esc_int($_POST['postid']);
	$oldtopicid = sf_esc_int($_POST['oldtopicid']);
	$oldforumid = sf_esc_int($_POST['oldforumid']);
	$newforumid = sf_esc_int($_POST['forumid']);
	$blogpostid = sf_esc_int($_POST['blogpostid']);
	$newtopicname  = sf_filter_title_save(trim(($_POST['newtopicname'])));

	# start with creating the new topic
	$newtopicslug = sf_create_slug($newtopicname, 'topic');

	# now create the topic and post records
	$wpdb->query(
		"INSERT INTO ".SFTOPICS."
		 (topic_name, topic_slug, topic_date, forum_id, post_count, blog_post_id, post_id)
		 VALUES
		 ('".$newtopicname."', '".$newtopicslug."', now(), ".$newforumid.", 1, '".$blogpostid."', ".$postid.");");

	if($wpdb === false)
	{
		update_sfnotice('sfmessage', '1@'.__("Post Move Failed", "sforum"));
		return;
	}
	$newtopicid = $wpdb->insert_id;

	# check the topic slug and if empty use the topic id
	if(empty($newtopicslug))
	{
		$newtopicslug = 'topic-'.$newtopicid;
		$thistopic = $wpdb->query("UPDATE ".SFTOPICS." SET topic_slug='".$slug."' WHERE topic_id=".$newtopicid);
	}

	# now check if old topic had just the one post and if so remove it
	$check = $wpdb->get_var("SELECT post_count FROM ".SFTOPICS." WHERE topic_id=".$oldtopicid);
	if($check == 1)
	{
		$wpdb->query("DELETE FROM ".SFTOPICS." WHERE topic_id=".$oldtopicid);
	}

	# update post record
	$wpdb->query(
		"UPDATE ".SFPOSTS."
		 SET topic_id=".$newtopicid.", forum_id=".$newforumid.", post_index=1, post_status=0
		 WHERE post_id=".$postid);

	# If old topic was in the admin queue then remove it. Assume it's read
	sf_remove_from_waiting(true, $oldtopicid, 0);

	# rebuild forum counts for old and new forums
	sf_build_forum_index($oldforumid);
	sf_build_forum_index($newforumid);
	sf_build_post_index($oldtopicid, sf_get_topic_slug($oldtopicid));

	# relink to new forum if blog linked topic and this was first post
	if($blogpostid)
	{
		sf_relink_topic($oldtopicid, $newtopicid, $newforumid);
	}

	if($wpdb == false)
	{
		update_sfnotice('sfmessage', '1@'.__("Post Move Failed", "sforum"));
	} else {
		update_sfnotice('sfmessage', '0@'.__("Post Moved", "sforum"));
	}
	return;
}

# ------------------------------------------------------------------
# sf_relink_topic()
#
# Relinks topic after a move
# 	$topicid	original topic ID
#	$newtopicid	new id if moved post
#	$newforumid	new forum id
# ------------------------------------------------------------------
function sf_relink_topic($topicid, $newtopicid, $newforumid)
{
	global $wpdb;

	# Check if the target topic is a linked topic
	$link = $wpdb->get_row("SELECT * FROM ".SFLINKS." WHERE topic_id=".$topicid);
	if($link)
	{
		sf_blog_links_control('update', $link->post_id, $newforumid, $newtopicid, $link->syncedit);
	}
	return;
}


function sf_reassign_post()
{
	global $wpdb, $current_user;

	if (!$current_user->sfreassign)
	{
		update_sfnotice('sfmessage', '1@'.__('Access Denied', "sforum"));
		return;
	}

	$postid = sf_esc_int($_POST['postid']);
	$olduserid = sf_esc_int($_POST['olduserid']);
	$newuserid = sf_esc_int($_POST['newuserid']);

	# transfer the post
	$wpdb->query(
		"UPDATE ".SFPOSTS."
		 SET user_id=".$newuserid."
		 WHERE post_id=".$postid);

	# update old user post counts
	$count = sf_get_member_item($olduserid, 'posts') - 1;
	sf_update_member_item($olduserid, 'posts', $count);

	# update new user post counts
	$count = sf_get_member_item($newuserid, 'posts') + 1;
	sf_update_member_item($newuserid, 'posts', $count);
}

function sf_change_topic_status()
{
	global $wpdb, $current_user;

	if(!$current_user->sfedit)
	{
		update_sfnotice('sfmessage', '1@'.__('Access Denied', "sforum"));
		return;
	}
	$topicid= sf_esc_int($_POST['id']);
	$statvalue = sf_esc_int($_POST['statvalue']);

	sf_update_topic_status_flag($statvalue, $topicid);

	update_sfnotice('sfmessage', '0@'.__("Topic Status Changed", "sforum"));
	return;
}

# ------------------------------------------------------------------
# sf_update_opened()
#
# Updates the number of times a topic is viewed
# 	$topicid:		The topic being opened for view
# ------------------------------------------------------------------
function sf_update_opened($topicid)
{
	global $wpdb, $sfvars;

	if(empty($topicid)) return;

	$ref=array();
	$ref=explode('/', $_SERVER['HTTP_REFERER']);
	$ref_topic = $ref[count($ref)-3];
	if(substr($ref_topic,0,5) == 'page-') $ref_topic = $ref[count($ref)-4];
	if($ref_topic == $sfvars['topicslug']) return;

	$current=$wpdb->get_var("SELECT topic_opened FROM ".SFTOPICS." WHERE topic_id=".$topicid);
	$current++;
	$wpdb->query("UPDATE ".SFTOPICS." SET topic_opened = ".$current." WHERE topic_id=".$topicid);
	return;
}

# ******************************************************************
# DELETE ITEM FUNCTIONS
# ******************************************************************

# ------------------------------------------------------------------
# sf_delete_topic()
#
# Delete a topic and all it;s posts
# 	$topicid:		The topic being subscribed to
#	$show:			True/False: Whether to return message (for UI)
# ------------------------------------------------------------------
function sf_delete_topic($topicid, $show=true)
{
	global $wpdb, $current_user;

	if(!$topicid) return '';

	if (!$current_user->sfdelete && !sf_is_forum_admin($current_user->ID) && !$current_user->sfdeleteown)
	{
		update_sfnotice('sfmessage', '1@'.__('Access Denied', "sforum"));
		return;
	}

	# We need to check for subscriptions on this topic
	$subs = $wpdb->get_var("SELECT topic_subs FROM ".SFTOPICS." WHERE  topic_id = ".$topicid);

	# Any subscriptions to remopve from user records?
	if ($subs)
	{
		$userlist = unserialize($subs);
		foreach ($userlist as $user)
		{
			$topiclist = sf_get_member_item($user, 'subscribe');
			if (!empty($topiclist))
			{
				$newlist = '';
				foreach ($topiclist as $topic)
				{
					if ($topic != $topicid) $newlist[] = $topic;
				}
				sf_update_member_item($user, 'subscribe', $newlist);
			}
		}
	}

	# Any watches to remove?
	$watches = $wpdb->get_var("SELECT topic_watches FROM ".SFTOPICS." WHERE  topic_id = ".$topicid);
	if ($watches)
	{
		$userlist = unserialize($watches);
		foreach ($userlist as $user)
		{
			$topiclist = sf_get_member_item($user, 'watches');
			if (!empty($topiclist))
			{
				$newlist = '';
				foreach ($topiclist as $topic)
				{
					if ($topic != $topicid) $newlist[] = $topic;
				}
				sf_update_member_item($user, 'watches', $newlist);
			}
		}
	}

	# remove any post ratings in this topic

	$rated = $wpdb->get_results("SELECT ".SFPOSTRATINGS.".post_id, members FROM ".SFPOSTRATINGS."
			 JOIN ".SFPOSTS." ON ".SFPOSTRATINGS.".post_id = ".SFPOSTS.".post_id
			 WHERE ".SFPOSTS.".topic_id=".$topicid);
	if ($rated)
	{
		foreach ($rated as $post)
		{
			# remove the post rating
			$wpdb->query("DELETE FROM ".SFPOSTRATINGS." WHERE post_id=".$post->post_id);

			# remove the posts rated data from sfmembers table
			$members = unserialize($post->members);
			if ($members)
			{
				# loop through each members that rated a post in this topic and remove the rating
				foreach ($members as $member)
				{
					$ratedposts = $wpdb->get_var("SELECT posts_rated FROM ".SFMEMBERS." WHERE user_id=".$member);
					if ($ratedposts)
					{
						# save any other rated posts only removing posts from this topic
						$new_ratings = '';
						$ratedposts = unserialize($ratedposts);
						foreach ($ratedposts as $ratedpost)
						{
							if ($ratedpost != $post->post_id)
							{
								$new_ratings[] = $ratedpost;
							}
						}
						if ($new_ratings == '')
						{
							$wpdb->query("UPDATE ".SFMEMBERS." SET posts_rated=NULL WHERE user_id=".$member);
						} else {
							$new_ratings = serialize($new_ratings);
							$wpdb->query("UPDATE ".SFMEMBERS." SET posts_rated='".$new_ratings."' WHERE user_id=".$member);
						}
					}
				}
			}
		}
	}

	# check of there is a post link to it?
	$row = $wpdb->get_row("SELECT blog_post_id, forum_id FROM ".SFTOPICS." WHERE topic_id = ".$topicid);
	if($row->blog_post_id != 0)
	{
		# break the link
		include_once(SF_PLUGIN_DIR.'/linking/library/sf-links-support.php');
		sf_blog_links_control('delete', $row->blog_post_id);
	}

	# delete from waiting just in case
	$wpdb->query("DELETE FROM ".SFWAITING." WHERE topic_id=".$topicid);

	# now delete from topic
	$wpdb->query("DELETE FROM ".SFTOPICS." WHERE topic_id=".$topicid);
	if($wpdb === false)
	{
		if($show) update_sfnotice('sfmessage', '1@'.__("Deletion Failed", "sforum"));
		return;
	}

	# topic delete hook
	if(function_exists('sf_hook_topic_delete'))
	{
		# grab the forum id
		$forumid = $wpdb->get_var("SELECT forum_id FROM ".SFTOPICS." WHERE  topic_id = ".$topicid);
		sf_process_hook('sf_hook_topic_delete', array($topicid, $forumid));
	}

	# now delete all the posts on the topic
	$wpdb->query("DELETE FROM ".SFPOSTS." WHERE topic_id=".$topicid);
	if($wpdb == false)
	{
		if($show) update_sfnotice('sfmessage', '1@'.__("Deletion of Posts in Topic Failed", "sforum"));
	} else {
		if($show) update_sfnotice('sfmessage', '0@'.__("Topic Deleted", "sforum"));
	}

	# delete from forums topic count
	sf_build_forum_index($row->forum_id);

	return;
}

# ------------------------------------------------------------------
# sf_delete_post()
#
# Delete a post
#	$postid:		The post to be deleted
# 	$topicid:		The topic post belongs to
#	$forumid:		The forum post belongs to
# ------------------------------------------------------------------
function sf_delete_post($postid, $topicid, $forumid, $show=true, $poster=0)
{
	global $wpdb, $current_user;

	if(!$postid || !$topicid || !$forumid) return '';

	if(!$current_user->sfdelete && !($current_user->sfdeleteown && $current_user->ID == $poster))
	{
		update_sfnotice('sfmessage', '1@'.__('Access Denied', "sforum"));
		return;
	}

	# Check post actually exsists - might be a browsser refresh!
	$target = $wpdb->get_var("SELECT post_id FROM ".SFPOSTS." WHERE post_id=".$postid);
	if(empty($target))
	{
		if($show) update_sfnotice('sfmessage', '0@'.__("Post Already Deleted", "sforum"));
		return;
	}

	# if just one post then remove topic as well
	if(sf_get_posts_count_in_topic($topicid) == 1)
	{
		sf_delete_topic($topicid);
	} else {
		# remove post ratings
		$rated = $wpdb->get_var("SELECT members FROM ".SFPOSTRATINGS." WHERE post_id=".$postid);
		if ($rated)
		{
			$members = unserialize($rated);
			if ($members)
			{
				# loop through each members that rated a post in this topic and remove the rating
				foreach ($members as $member)
				{
					$ratedposts = $wpdb->get_var("SELECT posts_rated FROM ".SFMEMBERS." WHERE user_id=".$member);
					if ($ratedposts)
					{
						# save any other rated posts only removing posts from this topic
						$new_ratings = '';
						$ratedposts = unserialize($ratedposts);
						foreach ($ratedposts as $ratedpost)
						{
							if ($ratedpost != $postid)
							{
								$new_ratings[] = $ratedpost;
							}
						}
						if ($new_ratings == '')
						{
							$wpdb->query("UPDATE ".SFMEMBERS." SET posts_rated=NULL WHERE user_id=".$member);
						} else {
							$new_ratings = serialize($new_ratings);
							$wpdb->query("UPDATE ".SFMEMBERS." SET posts_rated='".$new_ratings."' WHERE user_id=".$member);
						}
					}
				}
			}
		}
		$wpdb->query("DELETE FROM ".SFPOSTRATINGS." WHERE post_id=".$postid);

		$wpdb->query("DELETE FROM ".SFPOSTS." WHERE post_id=".$postid);
		if($wpdb === false)
		{
			if($show) update_sfnotice('sfmessage', '1@'.__("Deletion Failed", "sforum"));
		} else {
			if($show) update_sfnotice('sfmessage', '0@'.__("Post Deleted", "sforum"));
		}
		# re number post index
		sf_build_post_index($topicid, sf_get_topic_slug($topicid));
		sf_build_forum_index($forumid);
	}

	# post delete hook
	sf_process_hook('sf_hook_post_delete', array($postid, $topicid, $forumid));

	# need to look in sfwaiting to see if it's in there...
	sf_remove_from_waiting(true, $topicid, $postid);

	return;
}

# ------------------------------------------------------------------
# sf_remove_subscription()
#
# Removes a user subscription following edit or topic delete.
# 	$topicid:		The topic being unsubscribed from
#	$userid:		The user (passed because used in upgrade flow)
# ------------------------------------------------------------------
function sf_remove_subscription($topic, $userid)
{
	global $wpdb, $sfglobals;

	if(!$userid || !$topic) return '';

	# remove current subscription from member
	$list = sf_get_member_item($userid, 'subscribe');
	if (!empty($list))
	{
		$newlist = '';
		foreach ($list as $topicid)
		{
			if ($topic != $topicid)
			{
				$newlist[] = $topicid;
			}
		}
		sf_update_member_item($userid, 'subscribe', $newlist);
	}

	$list = $wpdb->get_var("SELECT topic_subs FROM ".SFTOPICS." WHERE topic_id=".$topic);
	if (!empty($list))
	{
		$newlist = '';
		$list = unserialize($list);
		foreach ($list as $user)
		{
			if ($userid != $user)
			{
				$newlist[] = $user;
			}
		}
		if ($newlist != '') $newlist = serialize($newlist);
		$wpdb->query("UPDATE ".SFTOPICS." SET topic_subs = '".$newlist."' WHERE topic_id=".$topic);
	}

	return;
}

# ******************************************************************
# EDIT TOOL ICONS
# ******************************************************************

# ------------------------------------------------------------------
# sf_icon_toggle()
#
# Toggle Tool Icon State
# ------------------------------------------------------------------
function sf_icon_toggle()
{
	global $sfglobals;

	$sfadminsettings = array();
	$sfadminsettings = sf_get_option('sfadminsettings');
	$state = $sfadminsettings['sftools'];
	if ($state ? $state = false : $state = true);
	$sfadminsettings['sftools'] = $state;
	sf_update_option('sfadminsettings', $sfadminsettings);
	$sfglobals['admin']['sftools'] = $state;

	return;
}

# ------------------------------------------------------------------
# sf_lock_topic_toggle()
#
# Toggle Topic Lock
#	Topicid:		Topic to lock/unlock
# ------------------------------------------------------------------
function sf_lock_topic_toggle($topicid)
{
	global $wpdb, $current_user;

	if(!$topicid) return '';

	if(!$current_user->sflock)
	{
		update_sfnotice('sfmessage', '1@'.__('Access Denied', "sforum"));
		return;
	}

	if($_POST['locktopicaction'].$topicid == get_sfsetting('sfaction')) return;

	$status = $wpdb->get_var("SELECT topic_status FROM ".SFTOPICS." WHERE topic_id=".sf_esc_int($topicid));
	if($status == 1 ? $status=0 : $status=1);

	$wpdb->query("UPDATE ".SFTOPICS." SET topic_status = ".$status." WHERE topic_id=".sf_esc_int($topicid));
	if($wpdb == false)
	{
		update_sfnotice('sfmessage', '1@'.__("Topic Lock Toggle Failed", "sforum"));
	} else {
		update_sfnotice('sfmessage', '0@'.__("Topic Lock Toggled", "sforum"));
		update_sfsetting('sfaction', sf_esc_str($_POST['locktopicaction'].$topicid));
	}
	return;
}

# ------------------------------------------------------------------
# sf_pin_topic_toggle()
#
# Toggle Topic Pin
#	Topicid:		Topic to pin/unpin
# ------------------------------------------------------------------
function sf_pin_topic_toggle($topicid)
{
	global $wpdb, $current_user;

	if(!$topicid) return '';

	if(!$current_user->sfpintopics)
	{
		update_sfnotice('sfmessage', '1@'.__('Access Denied', "sforum"));
		return;
	}

	if($_POST['pintopicaction'].$topicid == get_sfsetting('sfaction')) return;

	$status = $wpdb->get_var("SELECT topic_pinned FROM ".SFTOPICS." WHERE topic_id=".sf_esc_int($topicid));
	if($status == 1 ? $status=0 : $status=1);

	$wpdb->query("UPDATE ".SFTOPICS." SET topic_pinned = ".$status." WHERE topic_id=".sf_esc_int($topicid));
	if($wpdb == false)
	{
		update_sfnotice('sfmessage', '1@'.__("Topic Pin Toggle Failed", "sforum"));
	} else {
		update_sfnotice('sfmessage', '0@'.__("Topic Pin Toggled", "sforum"));
		update_sfsetting('sfaction', sf_esc_str($_POST['pintopicaction'].$topicid));
	}
	return;
}

# ------------------------------------------------------------------
# sf_pin_post_toggle()
#
# Toggle Post Pin
#	postid:		Post to pin/unpin
# ------------------------------------------------------------------
function sf_pin_post_toggle($postid)
{
	global $wpdb, $current_user;

	if(!$postid) return '';

	if(!$current_user->sfpinposts)
	{
		update_sfnotice('sfmessage', '1@'.__('Access Denied', "sforum"));
		return;
	}

	if($_POST['pinpostaction'].$postid == get_sfsetting('sfaction')) return;

	$status = $wpdb->get_var("SELECT post_pinned FROM ".SFPOSTS." WHERE post_id=".$postid);
	if($status == 1 ? $status = 0 : $status = 1);

	$wpdb->query("UPDATE ".SFPOSTS." SET post_pinned = ".$status." WHERE post_id=".sf_esc_int($postid));
	if($wpdb == false)
	{
		update_sfnotice('sfmessage', '1@'.__("Post Pin Toggle Failed", "sforum"));
	} else {
		update_sfnotice('sfmessage', '0@'.__("Post Pin Toggled", "sforum"));
		update_sfsetting('sfaction', sf_esc_str($_POST['pinpostaction']).$postid);
	}
	return;
}

# ******************************************************************
# DATA INTEGRITY MANAGEMENT
# ******************************************************************

# ------------------------------------------------------------------
# sf_build_post_index()
#
# Rebuilds the post index column (post sequence) and also sets the
# last post id and post count into the parent topic record
#	$topicid:		topic whose posts are being re-indexed
#	$topicslug:		slug to check sort order
# ------------------------------------------------------------------
function sf_build_post_index($topicid, $topicslug, $returnmsg=false)
{
	global $wpdb;

	if(!$topicid || !$topicslug) return '';

	# get topic posts is their display order
	$posts=$wpdb->get_results("SELECT post_id, post_index FROM ".SFPOSTS." WHERE topic_id = ".$topicid." ORDER BY post_pinned DESC, post_id ASC");
	if($posts)
	{
		$index = 1;
		foreach($posts as $post)
		{
			# update the post_index for each post to set display order
			$wpdb->query("UPDATE ".SFPOSTS." SET post_index = ".$index." WHERE post_id = ".$post->post_id);
			$index++;
		}
		$lastpost = $post->post_id;
	} else {
		$lastpost = 'NULL';
	}
	# update the topic with the last post id and the post count
	$wpdb->query("UPDATE ".SFTOPICS." SET post_id=".$lastpost.", post_count=".($index-1)." WHERE topic_id=".$topicid);

	if($returnmsg) update_sfnotice('sfmessage', '0@'.__("Verification Complete", "sforum"));

	return;
}

# ------------------------------------------------------------------
# sf_build_forum_index()
#
# Rebuilds the topic count and last post id in a forum record
#	$forumid:		forum needing updating
# ------------------------------------------------------------------
function sf_build_forum_index($forumid, $returnmsg=false)
{
	global $wpdb;

	if(!$forumid) return '';

	# get the topic and post counts for this forum
	$topiccount = $wpdb->get_var("SELECT COUNT(topic_id) FROM ".SFTOPICS." WHERE forum_id=".$forumid);
	$postcount  = $wpdb->get_var("SELECT COUNT(post_id) FROM ".SFPOSTS." WHERE forum_id=".$forumid);
	# get the last post that appeared in a topic within this forum
	$postid =  $wpdb->get_var("SELECT post_id FROM ".SFPOSTS." WHERE forum_id = ".$forumid." ORDER BY post_id DESC LIMIT 1");

	if(!$topiccount) $topiccount = 0;
	if(!$postcount)  $postcount = 0;
	if(!isset($postid)) $postid = 'NULL';

	# update forum record
	$wpdb->query("UPDATE ".SFFORUMS." SET post_id=".$postid.", post_count=".$postcount.", topic_count=".$topiccount." WHERE forum_id=".$forumid);

	if($returnmsg) update_sfnotice('sfmessage', '0@'.__("Verification Complete", "sforum"));

	return;
}

function sf_get_user_memberships($user_id)
{
	global $wpdb;

	if(!$user_id) return '';

	$sql = "SELECT ".SFMEMBERSHIPS.".usergroup_id, usergroup_name, usergroup_desc
			FROM ".SFMEMBERSHIPS."
			JOIN ".SFUSERGROUPS." ON ".SFUSERGROUPS.".usergroup_id = ".SFMEMBERSHIPS.".usergroup_id
			WHERE user_id=".$user_id;
	return $wpdb->get_results($sql, ARRAY_A);
}

function sf_check_membership($usergroup_id, $user_id)
{
	global $wpdb;

	if(!$usergroup_id || !$user_id) return '';

	$sql = "SELECT usergroup_id
			FROM ".SFMEMBERSHIPS."
			WHERE user_id=".$user_id." AND usergroup_id=".$usergroup_id;
	return $wpdb->get_results($sql, ARRAY_A);
}


function sf_get_pm_inbox_new_count($userid)
{
	global $wpdb, $sfglobals;

	if(!$userid) return '';

	return $wpdb->get_var("SELECT COUNT(*) FROM ".SFMESSAGES." WHERE to_id = ".$userid." AND inbox=1 AND message_status=0");
}

function sf_change_topic_tags($topicid, $newtags)
{
	global $wpdb, $sfglobals;

	if(!$topicid || !$newtags) return '';

	# remove any existing tags for the topic
	$oldtags = $wpdb->get_results("SELECT tag_id FROM ".SFTAGMETA." WHERE topic_id=".$topicid);
	foreach ($oldtags as $oldtag)
	{
		# grab all the tag rows and decrement the tag count
		$tagcount = $wpdb->get_var("SELECT tag_count FROM ".SFTAGS." WHERE tag_id=".$oldtag->tag_id);

		# decrement tag count and delete if it gets to zero or update the new count
		$tagcount--;
		if ($tagcount == 0)
		{
			$wpdb->query("DELETE FROM ".SFTAGS." WHERE tag_id=".$oldtag->tag_id); # count is zero so delete
		} else {
			$wpdb->query("UPDATE ".SFTAGS." SET tag_count=".$tagcount." WHERE tag_id=".$oldtag->tag_id); # update count
		}

		# remove all the tag meta entries for the topic
		$wpdb->query("DELETE FROM ".SFTAGMETA." WHERE topic_id=".$topicid);
	}

	# now add in the updated tags
    $tags = trim($newtags);
    $tags = trim($tags, ',');  # no extra commas allowed
	$tags = explode(',', $tags);
	$tags = array_unique($tags);  # remove any duplicates
	$tags = array_values($tags);  # put back in order
	if ($sfglobals['display']['topics']['maxtags'] > 0 && count($tags) > $sfglobals['display']['topics']['maxtags'])
	{
		$tags = array_slice($tags, 0, $sfglobals['display']['topics']['maxtags']);  # limit to maxt tags opton
	}
	sfc_add_tags($topicid, $tags);
}

function sf_mark_all_read()
{
    global $wpdb, $current_user;

    # just to be safe, make sure a member called
    if ($current_user->member)
    {
        sf_destroy_users_newposts($current_user->ID);
    }
}

# ------------------------------------------------------------------
# sf_get_blog_title_from_id()
#
# Returns the post title of a blog post from the post id
#	$postid:		blog post target id
# ------------------------------------------------------------------
function sf_get_blog_title_from_id($postid)
{
	global $wpdb;

	if(!$postid) return '';

	return $wpdb->get_var(
			"SELECT post_title
			 FROM ".$wpdb->prefix."posts
			 WHERE ID=".$postid);
}


# ------------------------------------------------------------------
# sf_query_results()
#
# Returns the query if good - displays error if sql invalid
# 4.2 Just used on two search queries
#	$sql:		query string
#	$type:		return type - defaults to OBJECT
# ------------------------------------------------------------------
function sf_query_results($sql, $type=OBJECT)
{
	global $wpdb;

	$wpdb->hide_errors();
	$records = $wpdb->get_results($sql, $type);

	if($wpdb->last_error == '')
	{
		return $records;
	} else {
		update_sfnotice('sfmessage', '1@'.__("Invalid Database Query", "sforum"));
		echo sf_render_queued_message();
		return '';
	}
}

?>