echo " <tr>\n";
echo " <td align=\"left\">", gettext("Forthcoming Birthdays"), "</td>\n";
echo " </tr>\n";
echo " </table>\n";
echo " <table class=\"posthead\" width=\"100%\">\n";
echo " <tr>\n";
echo " <td align=\"center\">\n";
echo " <table class=\"posthead\" width=\"100%\">\n";
echo " <tr>\n";
echo " <td align=\"center\">\n";
echo " <table class=\"posthead\" border=\"0\" width=\"100%\" cellpadding=\"2\" cellspacing=\"0\">\n";
foreach ($user_birthdays_array as $user_birthday) {
echo " <tr>\n";
echo " <td valign=\"top\" align=\"center\" style=\"white-space: nowrap\" width=\"25\"><img src=\"", html_style_image('bullet.png'), "\" alt=\"", gettext("User"), "\" title=\"", gettext("User"), "\" /></td>\n";
echo " <td align=\"left\" valign=\"top\"><a href=\"user_profile.php?webtag={$webtag}&uid={$user_birthday['UID']}\" target=\"_blank\" class=\"popup 650x500\">", word_filter_add_ob_tags(format_user_name($user_birthday['LOGON'], $user_birthday['NICKNAME']), true), "</a></td>\n";
echo " <td align=\"right\" style=\"white-space: nowrap\" valign=\"top\"><span class=\"threadtime\">", format_birthday($user_birthday['DOB']), " </span></td>\n";
echo " </tr>\n";
}
echo " </table>\n";
echo " </td>\n";
echo " </tr>\n";
echo " <tr>\n";
echo " <td align=\"left\"> </td>\n";
echo " </tr>\n";
echo " </table>\n";
echo " </td>\n";
echo " </tr>\n";
echo " </table>\n";
echo " </td>\n";
echo " </tr>\n";
echo " </table>\n";
function user_get_profile($uid)
{
if (!($db = db::get())) {
return false;
}
if (!is_numeric($uid)) {
return false;
}
$peer_uid = session::get_value('UID');
if (!($table_prefix = get_table_prefix())) {
return false;
}
if (!($forum_fid = get_forum_fid())) {
return false;
}
$user_groups_array = array();
$user_prefs = user_get_prefs($uid);
$session_gc_maxlifetime = ini_get('session.gc_maxlifetime');
$session_cutoff_datetime = date(MYSQL_DATETIME, time() - $session_gc_maxlifetime);
$sql = "SELECT USER.UID, USER.LOGON, USER.NICKNAME, USER_PEER.PEER_NICKNAME, ";
$sql .= "UNIX_TIMESTAMP(USER_FORUM.LAST_VISIT) AS LAST_VISIT, ";
$sql .= "UNIX_TIMESTAMP(USER.REGISTERED) AS REGISTERED, ";
$sql .= "UNIX_TIMESTAMP(USER_TRACK.USER_TIME_BEST) AS USER_TIME_BEST, ";
$sql .= "UNIX_TIMESTAMP(USER_TRACK.USER_TIME_TOTAL) AS USER_TIME_TOTAL, ";
$sql .= "USER_PEER.RELATIONSHIP, SESSIONS.ID FROM USER USER ";
$sql .= "LEFT JOIN USER_PREFS USER_PREFS_GLOBAL ON (USER_PREFS_GLOBAL.UID = USER.UID) ";
$sql .= "LEFT JOIN `{$table_prefix}USER_PREFS` USER_PREFS_FORUM ";
$sql .= "ON (USER_PREFS_FORUM.UID = USER.UID) ";
$sql .= "LEFT JOIN `{$table_prefix}USER_PEER` USER_PEER ";
$sql .= "ON (USER_PEER.PEER_UID = USER.UID AND USER_PEER.UID = '{$peer_uid}') ";
$sql .= "LEFT JOIN USER_FORUM USER_FORUM ON (USER_FORUM.UID = USER.UID ";
$sql .= "AND USER_FORUM.FID = '{$forum_fid}') ";
$sql .= "LEFT JOIN `{$table_prefix}USER_TRACK` USER_TRACK ";
$sql .= "ON (USER_TRACK.UID = USER.UID) ";
$sql .= "LEFT JOIN SESSIONS ON (SESSIONS.UID = USER.UID ";
$sql .= "AND SESSIONS.TIME >= CAST('{$session_cutoff_datetime}' AS DATETIME)) ";
$sql .= "WHERE USER.UID = '{$uid}' ";
$sql .= "GROUP BY USER.UID";
if (!($result = $db->query($sql))) {
return false;
}
if ($result->num_rows == 0) {
return false;
}
$user_profile = $result->fetch_assoc();
if (isset($user_prefs['ANON_LOGON']) && $user_prefs['ANON_LOGON'] > USER_ANON_DISABLED) {
$anon_logon = $user_prefs['ANON_LOGON'];
} else {
$anon_logon = USER_ANON_DISABLED;
}
if ($anon_logon == USER_ANON_DISABLED && isset($user_profile['LAST_VISIT']) && $user_profile['LAST_VISIT'] > 0) {
$user_profile['LAST_LOGON'] = format_time($user_profile['LAST_VISIT']);
} else {
$user_profile['LAST_LOGON'] = gettext("Unknown");
}
if (isset($user_profile['REGISTERED']) && $user_profile['REGISTERED'] > 0) {
$user_profile['REGISTERED'] = format_date($user_profile['REGISTERED']);
} else {
$user_profile['REGISTERED'] = gettext("Unknown");
}
if (isset($user_profile['USER_TIME_BEST']) && $user_profile['USER_TIME_BEST'] > 0) {
$user_profile['USER_TIME_BEST'] = format_time_display($user_profile['USER_TIME_BEST']);
} else {
$user_profile['USER_TIME_BEST'] = gettext("Unknown");
}
if (isset($user_profile['USER_TIME_TOTAL']) && $user_profile['USER_TIME_TOTAL'] > 0) {
$user_profile['USER_TIME_TOTAL'] = format_time_display($user_profile['USER_TIME_TOTAL']);
} else {
$user_profile['USER_TIME_TOTAL'] = gettext("Unknown");
}
if (isset($user_prefs['DOB_DISPLAY']) && !empty($user_prefs['DOB']) && $user_prefs['DOB'] != "0000-00-00") {
if ($user_prefs['DOB_DISPLAY'] == USER_DOB_DISPLAY_BOTH) {
$user_profile['DOB'] = format_birthday($user_prefs['DOB']);
$user_profile['AGE'] = format_age($user_prefs['DOB']);
} else {
if ($user_prefs['DOB_DISPLAY'] == USER_DOB_DISPLAY_DATE) {
$user_profile['DOB'] = format_birthday($user_prefs['DOB']);
} else {
if ($user_prefs['DOB_DISPLAY'] == USER_DOB_DISPLAY_AGE) {
$user_profile['AGE'] = format_age($user_prefs['DOB']);
}
}
}
}
if (isset($user_prefs['PIC_URL']) && strlen($user_prefs['PIC_URL']) > 0) {
$user_profile['PIC_URL'] = $user_prefs['PIC_URL'];
}
if (isset($user_prefs['PIC_AID']) && is_md5($user_prefs['PIC_AID'])) {
$user_profile['PIC_AID'] = $user_prefs['PIC_AID'];
}
if (isset($user_prefs['AVATAR_URL']) && strlen($user_prefs['AVATAR_URL']) > 0) {
$user_profile['AVATAR_URL'] = $user_prefs['AVATAR_URL'];
}
if (isset($user_prefs['AVATAR_AID']) && is_md5($user_prefs['AVATAR_AID'])) {
$user_profile['AVATAR_AID'] = $user_prefs['AVATAR_AID'];
}
if (isset($user_prefs['HOMEPAGE_URL']) && strlen($user_prefs['HOMEPAGE_URL']) > 0) {
$user_profile['HOMEPAGE_URL'] = $user_prefs['HOMEPAGE_URL'];
}
if (!isset($user_profile['RELATIONSHIP'])) {
$user_profile['RELATIONSHIP'] = 0;
}
if (isset($user_profile['PEER_NICKNAME'])) {
if (!is_null($user_profile['PEER_NICKNAME']) && strlen($user_profile['PEER_NICKNAME']) > 0) {
$user_profile['NICKNAME'] = $user_profile['PEER_NICKNAME'];
}
}
if ($anon_logon == USER_ANON_DISABLED) {
if (isset($user_profile['ID'])) {
$user_profile['STATUS'] = gettext("Online");
} else {
$user_profile['STATUS'] = gettext("Inactive / Offline");
}
} else {
$user_profile['STATUS'] = gettext("Unknown");
}
if ($user_post_count = user_get_post_count($uid)) {
$user_profile['POST_COUNT'] = $user_post_count;
} else {
$user_profile['POST_COUNT'] = 0;
}
if ($user_local_time = user_format_local_time($user_prefs)) {
$user_profile['LOCAL_TIME'] = $user_local_time;
}
if (user_is_banned($uid)) {
$user_profile['USER_GROUPS'] = gettext("Banned");
} else {
perm_user_get_group_names($uid, $user_groups_array);
if (sizeof($user_groups_array) > 0) {
$user_profile['USER_GROUPS'] = implode(', ', $user_groups_array);
} else {
$user_profile['USER_GROUPS'] = gettext("Registered");
}
}
return $user_profile;
}
echo " </tr>\n";
if (($average_age = stats_get_average_age()) !== false) {
echo " <tr>\n";
echo " <td align=\"left\" style=\"white-space: nowrap\" width=\"40%\">", gettext("Average age"), ": </td>\n";
echo " <td align=\"left\">", format_number($average_age, 2), "</td>\n";
echo " </tr>\n";
} else {
echo " <tr>\n";
echo " <td align=\"left\" style=\"white-space: nowrap\" width=\"40%\">", gettext("Average age"), ": </td>\n";
echo " <td align=\"left\">", gettext("Unknown"), "</td>\n";
echo " </tr>\n";
}
if (($most_popular_birthday = stats_get_most_popular_birthday()) !== false) {
echo " <tr>\n";
echo " <td align=\"left\" style=\"white-space: nowrap\" width=\"40%\">", gettext("Most popular birthday"), ": </td>\n";
echo " <td align=\"left\">", format_birthday($most_popular_birthday['DOB']), " (", format_number($most_popular_birthday['DOB_COUNT']), " ", gettext("Users"), ")</td>\n";
echo " </tr>\n";
} else {
echo " <tr>\n";
echo " <td align=\"left\" style=\"white-space: nowrap\" width=\"40%\">", gettext("Most popular birthday"), ": </td>\n";
echo " <td align=\"left\">", gettext("No Birthday Data Available"), "</td>\n";
echo " </tr>\n";
}
echo " <tr>\n";
echo " <td align=\"left\" colspan=\"3\"> </td>\n";
echo " </tr>\n";
echo " </table>\n";
echo " </td>\n";
echo " </tr>\n";
echo " </table>\n";
echo " </td>\n";
function user_get_profile($uid)
{
if (!($db = db::get())) {
return false;
}
if (!is_numeric($uid)) {
return false;
}
if (!isset($_SESSION['UID']) || !is_numeric($_SESSION['UID'])) {
return false;
}
if (!($table_prefix = get_table_prefix())) {
return false;
}
if (!($forum_fid = get_forum_fid())) {
return false;
}
$user_prefs = user_get_prefs($uid);
$session_gc_maxlifetime = ini_get('session.gc_maxlifetime');
$session_cutoff_datetime = date(MYSQL_DATETIME, time() - $session_gc_maxlifetime);
$sql = "SELECT USER.UID, USER.LOGON, USER.NICKNAME, USER_PEER.PEER_NICKNAME, USER_PEER.RELATIONSHIP, ";
$sql .= "SESSIONS.ID, UNIX_TIMESTAMP(USER_FORUM.LAST_VISIT) AS LAST_VISIT, UNIX_TIMESTAMP(USER.REGISTERED) AS REGISTERED, ";
$sql .= "COALESCE(USER_POST_RATING.RATING, 0) AS POST_RATING, COUNT(POST_USER_RATING.RATING) AS POST_VOTE_TOTAL, ";
$sql .= "COALESCE(SUM(IF(POST_USER_RATING.RATING > 0, 1, 0)), 0) AS POST_VOTE_UP, ";
$sql .= "COALESCE(SUM(IF(POST_USER_RATING.RATING < 0, 1, 0)), 0) AS POST_VOTE_DOWN FROM USER USER ";
$sql .= "LEFT JOIN USER_PREFS USER_PREFS_GLOBAL ON (USER_PREFS_GLOBAL.UID = USER.UID) ";
$sql .= "LEFT JOIN `{$table_prefix}USER_PREFS` USER_PREFS_FORUM ON (USER_PREFS_FORUM.UID = USER.UID) ";
$sql .= "LEFT JOIN `{$table_prefix}USER_PEER` USER_PEER ON (USER_PEER.PEER_UID = USER.UID AND USER_PEER.UID = '{$_SESSION['UID']}') ";
$sql .= "LEFT JOIN USER_FORUM USER_FORUM ON (USER_FORUM.UID = USER.UID AND USER_FORUM.FID = '{$forum_fid}') ";
$sql .= "LEFT JOIN SESSIONS ON (SESSIONS.UID = USER.UID AND SESSIONS.TIME >= CAST('{$session_cutoff_datetime}' AS DATETIME)) ";
$sql .= "LEFT JOIN `{$table_prefix}POST_RATING` POST_USER_RATING ON (POST_USER_RATING.UID = USER.UID AND POST_USER_RATING.RATING IN (-1, 1)) ";
$sql .= "LEFT JOIN (SELECT POST.FROM_UID AS UID, SUM(POST_RATING.RATING) AS RATING FROM `{$table_prefix}POST` POST ";
$sql .= "INNER JOIN `{$table_prefix}POST_RATING` POST_RATING ON (POST_RATING.TID = POST.TID AND POST_RATING.PID = POST.PID) ";
$sql .= "WHERE POST.FROM_UID = '{$uid}' GROUP BY POST.FROM_UID) AS USER_POST_RATING ON (USER_POST_RATING.UID = USER.UID) ";
$sql .= "WHERE USER.UID = '{$uid}' GROUP BY USER.UID";
if (!($result = $db->query($sql))) {
return false;
}
if ($result->num_rows == 0) {
return false;
}
$user_profile = $result->fetch_assoc();
if (isset($user_prefs['ANON_LOGON']) && $user_prefs['ANON_LOGON'] > USER_ANON_DISABLED) {
$anon_logon = $user_prefs['ANON_LOGON'];
} else {
$anon_logon = USER_ANON_DISABLED;
}
if ($anon_logon == USER_ANON_DISABLED && isset($user_profile['LAST_VISIT']) && $user_profile['LAST_VISIT'] > 0) {
$user_profile['LAST_LOGON'] = format_date_time($user_profile['LAST_VISIT']);
} else {
$user_profile['LAST_LOGON'] = gettext("Unknown");
}
if (isset($user_profile['REGISTERED']) && $user_profile['REGISTERED'] > 0) {
$user_profile['REGISTERED'] = format_date_time($user_profile['REGISTERED']);
} else {
$user_profile['REGISTERED'] = gettext("Unknown");
}
if (isset($user_prefs['DOB_DISPLAY']) && !empty($user_prefs['DOB']) && $user_prefs['DOB'] != "0000-00-00") {
if ($user_prefs['DOB_DISPLAY'] == USER_DOB_DISPLAY_BOTH) {
$user_profile['DOB'] = format_birthday($user_prefs['DOB']);
$user_profile['AGE'] = format_age($user_prefs['DOB']);
} else {
if ($user_prefs['DOB_DISPLAY'] == USER_DOB_DISPLAY_DATE) {
$user_profile['DOB'] = format_birthday($user_prefs['DOB']);
} else {
if ($user_prefs['DOB_DISPLAY'] == USER_DOB_DISPLAY_AGE) {
$user_profile['AGE'] = format_age($user_prefs['DOB']);
}
}
}
}
if (isset($user_prefs['PIC_URL']) && filter_var($user_prefs['PIC_URL'], FILTER_VALIDATE_URL)) {
$user_profile['PIC_URL'] = $user_prefs['PIC_URL'];
}
if (isset($user_prefs['PIC_AID']) && is_numeric($user_prefs['PIC_AID'])) {
$user_profile['PIC_AID'] = $user_prefs['PIC_AID'];
}
if (isset($user_prefs['AVATAR_URL']) && filter_var($user_prefs['AVATAR_URL'], FILTER_VALIDATE_URL)) {
$user_profile['AVATAR_URL'] = $user_prefs['AVATAR_URL'];
}
if (isset($user_prefs['AVATAR_AID']) && is_numeric($user_prefs['AVATAR_AID'])) {
$user_profile['AVATAR_AID'] = $user_prefs['AVATAR_AID'];
}
if (isset($user_prefs['HOMEPAGE_URL']) && filter_var($user_prefs['HOMEPAGE_URL'], FILTER_VALIDATE_URL)) {
$user_profile['HOMEPAGE_URL'] = $user_prefs['HOMEPAGE_URL'];
}
if (!isset($user_profile['RELATIONSHIP'])) {
$user_profile['RELATIONSHIP'] = 0;
}
if (isset($user_profile['PEER_NICKNAME'])) {
if (!is_null($user_profile['PEER_NICKNAME']) && strlen($user_profile['PEER_NICKNAME']) > 0) {
$user_profile['NICKNAME'] = $user_profile['PEER_NICKNAME'];
}
}
if ($anon_logon == USER_ANON_DISABLED) {
if (isset($user_profile['ID'])) {
$user_profile['STATUS'] = gettext("Online");
} else {
$user_profile['STATUS'] = gettext("Inactive / Offline");
}
} else {
$user_profile['STATUS'] = gettext("Unknown");
}
if (($user_post_count = user_get_post_count($uid)) !== false) {
$user_profile['POST_COUNT'] = $user_post_count;
} else {
$user_profile['POST_COUNT'] = 0;
}
if (($user_local_time = user_format_local_time($user_prefs)) !== false) {
$user_profile['LOCAL_TIME'] = $user_local_time;
}
if (user_is_banned($uid)) {
$user_profile['GROUPS'] = gettext("Banned");
} else {
if ($user_groups_array = perm_user_get_group_names($uid)) {
$user_profile['GROUPS'] = implode(', ', $user_groups_array);
} else {
$user_profile['GROUPS'] = gettext("Registered");
}
}
return $user_profile;
}
function visitor_log_browse_items($user_search, $profile_items_array, $page, $sort_by, $sort_dir, $hide_empty, $hide_guests)
{
if (!($db = db::get())) {
return false;
}
if (!is_numeric($page) || $page < 1) {
return false;
}
$offset = calculate_page_offset($page, 10);
if (!is_array($profile_items_array)) {
return false;
}
// Fetch the table prefix.
if (!($table_prefix = get_table_prefix())) {
return false;
}
// Forum FID which we'll need later.
if (!($forum_fid = get_forum_fid())) {
return false;
}
// Permitted columns to sort the results by
$sort_by_array = array_keys($profile_items_array);
// Permitted sort directions.
$sort_dir_array = array('ASC', 'DESC');
// Check the specified sort by and sort directions. If they're
// invalid default to LAST_VISIT DESC.
if (!in_array($sort_by, $sort_by_array)) {
$sort_by = 'UID';
}
if (!in_array($sort_dir, $sort_dir_array)) {
$sort_dir = 'DESC';
}
// Get the current session's UID.
if (!isset($_SESSION['UID']) || !is_numeric($_SESSION['UID'])) {
return false;
}
// Constant for the relationship
$user_friend = USER_FRIEND;
// Named column NULL filtering
$column_null_filter_having_array = array('POST_COUNT' => '(POST_COUNT IS NOT NULL)', 'LAST_VISIT' => '(LAST_VISIT IS NOT NULL)', 'REGISTERED' => '(REGISTERED IS NOT NULL)', 'DOB' => '(DOB IS NOT NULL)', 'AGE' => '(AGE IS NOT NULL AND AGE > 0)', 'TIMEZONE' => '(TIMEZONE IS NOT NULL)', 'LOCAL_TIME' => '(LOCAL_TIME IS NOT NULL)', 'USER_RATING' => '(USER_RATING > 0)', 'POST_SCORES' => '(POST_VOTE_TOTAL > 0)');
// Column alias to perform sorting against for textual results.
$column_sort_alias = array('POST_SCORES' => 'POST_VOTE_TOTAL');
// Year, Month and Day for Age calculation
list($year, $month, $day) = explode('-', date(MYSQL_DATE, time()));
// Current Date for User's local time
$current_datetime = date(MYSQL_DATE_HOUR_MIN, time());
// Main Query
$select_sql = "SELECT SQL_CALC_FOUND_ROWS USER.UID, USER.LOGON, USER.NICKNAME, USER_PEER.RELATIONSHIP, ";
$select_sql .= "USER_PEER.PEER_NICKNAME, CAST(USER_TRACK.USER_VALUE AS UNSIGNED) AS POST_COUNT, ";
$select_sql .= "IF (USER_PREFS_GLOBAL.DOB_DISPLAY > 1, DATE_FORMAT(USER_PREFS_GLOBAL.DOB, '0000-%m-%d'), NULL) AS DOB, ";
$select_sql .= "IF (USER_PREFS_GLOBAL.DOB_DISPLAY IN (1, 3), {$year} - DATE_FORMAT(USER_PREFS_GLOBAL.DOB, '%Y') - ";
$select_sql .= "('00-{$month}-{$day}' < DATE_FORMAT(USER_PREFS_GLOBAL.DOB, '00-%m-%d')), ";
$select_sql .= "NULL) AS AGE, TIMEZONES.TZID AS TIMEZONE, UNIX_TIMESTAMP('{$current_datetime}') AS LOCAL_TIME, ";
$select_sql .= "UNIX_TIMESTAMP(USER.REGISTERED) AS REGISTERED, COALESCE(USER_PREFS_FORUM.AVATAR_URL, ";
$select_sql .= "USER_PREFS_GLOBAL.AVATAR_URL) AS AVATAR_URL, COALESCE(USER_PREFS_FORUM.AVATAR_AID, ";
$select_sql .= "USER_PREFS_GLOBAL.AVATAR_AID) AS AVATAR_AID, SEARCH_ENGINE_BOTS.SID, SEARCH_ENGINE_BOTS.NAME, ";
$select_sql .= "SEARCH_ENGINE_BOTS.URL, IF (USER_PREFS_GLOBAL.ANON_LOGON = 1, NULL, ";
$select_sql .= "UNIX_TIMESTAMP(VISITOR_LOG.LAST_LOGON)) AS LAST_VISIT, ";
$select_sql .= "COALESCE(USER_RATING.USER_RATING, 0) AS USER_RATING, ";
$select_sql .= "COALESCE(POST_RATING.POST_VOTE_TOTAL, 0) AS POST_VOTE_TOTAL, ";
$select_sql .= "COALESCE(POST_RATING.POST_VOTE_UP, 0) AS POST_VOTE_UP, ";
$select_sql .= "COALESCE(POST_RATING.POST_VOTE_DOWN, 0) AS POST_VOTE_DOWN ";
// Include the selected numeric (PIID) profile items
$profile_entry_array = array();
// Include the profile item types and options.
$profile_item_type_array = array();
$profile_item_options_array = array();
// Iterate through them.
foreach ($sort_by_array as $column) {
if (is_numeric($column)) {
$profile_entry_array[$column] = "USER_PROFILE_{$column}.ENTRY AS ENTRY_{$column} ";
$profile_item_type_array[] = "PROFILE_ITEM_{$column}.TYPE AS PROFILE_ITEM_TYPE_{$column} ";
$profile_item_options_array[] = "PROFILE_ITEM_{$column}.OPTIONS AS PROFILE_ITEM_OPTIONS_{$column} ";
}
}
// From portion which selects users and guests from the VISITOR_LOG table.
$from_sql = "FROM VISITOR_LOG LEFT JOIN USER ON (USER.UID = VISITOR_LOG.UID) ";
// Various joins we need for User's Age, DOB, etc.
$join_sql = "LEFT JOIN USER_PREFS USER_PREFS_GLOBAL ON (USER_PREFS_GLOBAL.UID = USER.UID) ";
$join_sql .= "LEFT JOIN `{$table_prefix}USER_PREFS` USER_PREFS_FORUM ON (USER_PREFS_FORUM.UID = USER.UID) ";
$join_sql .= "LEFT JOIN `{$table_prefix}USER_TRACK` USER_TRACK ON (USER_TRACK.UID = USER.UID AND USER_TRACK.USER_KEY = 'POST_COUNT') ";
$join_sql .= "LEFT JOIN `{$table_prefix}USER_PEER` USER_PEER ON (USER_PEER.PEER_UID = USER.UID AND USER_PEER.UID = '{$_SESSION['UID']}') ";
$join_sql .= "LEFT JOIN SEARCH_ENGINE_BOTS ON (SEARCH_ENGINE_BOTS.SID = VISITOR_LOG.SID) ";
$join_sql .= "LEFT JOIN TIMEZONES ON (TIMEZONES.TZID = USER_PREFS_GLOBAL.TIMEZONE) ";
$join_sql .= "LEFT JOIN (SELECT POST_RATING.UID, COUNT(POST_RATING.RATING) AS POST_VOTE_TOTAL, ";
$join_sql .= "SUM(IF(POST_RATING.RATING > 0, 1, 0)) AS POST_VOTE_UP, ";
$join_sql .= "SUM(IF(POST_RATING.RATING < 0, 1, 0)) AS POST_VOTE_DOWN ";
$join_sql .= "FROM `{$table_prefix}POST_RATING` POST_RATING GROUP BY POST_RATING.UID) AS POST_RATING ";
$join_sql .= "ON (POST_RATING.UID = VISITOR_LOG.UID) LEFT JOIN (SELECT POST.FROM_UID AS UID, ";
$join_sql .= "SUM(POST_RATING.RATING) AS USER_RATING FROM `{$table_prefix}POST` POST ";
$join_sql .= "INNER JOIN `{$table_prefix}POST_RATING` POST_RATING ON (POST_RATING.TID = POST.TID ";
$join_sql .= "AND POST_RATING.PID = POST.PID) GROUP BY POST.FROM_UID) AS USER_RATING ";
$join_sql .= "ON (USER_RATING.UID = VISITOR_LOG.UID) ";
// Joins on the selected numeric (PIID) profile items.
foreach ($sort_by_array as $column) {
if (is_numeric($column)) {
$join_sql .= "LEFT JOIN `{$table_prefix}PROFILE_ITEM` PROFILE_ITEM_{$column} ";
$join_sql .= "ON (PROFILE_ITEM_{$column}.PIID = '{$column}') ";
$join_sql .= "LEFT JOIN `{$table_prefix}USER_PROFILE` USER_PROFILE_{$column} ";
$join_sql .= "ON (USER_PROFILE_{$column}.PIID = PROFILE_ITEM_{$column}.PIID ";
$join_sql .= "AND USER_PROFILE_{$column}.UID = USER.UID ";
$join_sql .= "AND (USER_PROFILE_{$column}.PRIVACY = 0 ";
$join_sql .= "OR (USER_PROFILE_{$column}.PRIVACY = 1 ";
$join_sql .= "AND (USER_PEER.RELATIONSHIP & {$user_friend} > 0)))) ";
}
}
// The Where clause
$where_query_array = array("VISITOR_LOG.FORUM = '{$forum_fid}'");
// Having clause for filtering NULL columns.
$having_query_array = array();
// Filter by user name / search engine bot name
if ($user_search !== false && strlen(trim($user_search)) > 0) {
$user_search = $db->escape(str_replace('%', '', $user_search));
$user_search_sql = "(USER.LOGON LIKE '{$user_search}%' OR ";
$user_search_sql .= "USER.NICKNAME LIKE '{$user_search}%')";
$where_query_array[] = $user_search_sql;
}
// Hide Guests
if ($hide_guests === true) {
$where_query_array[] = "(USER.UID IS NOT NULL) ";
}
// Hide empty or NULL values
if ($hide_empty === true) {
foreach ($sort_by_array as $column) {
if (is_numeric($column)) {
$having_query_array[] = "(LENGTH(ENTRY_{$column}) > 0) ";
} else {
$having_query_array[] = $column_null_filter_having_array[$column];
}
}
}
// Main query NULL column filtering
if (sizeof($having_query_array) > 0) {
$having_sql = sprintf("HAVING %s", implode(" OR ", $having_query_array));
} else {
$having_sql = "";
}
if (sizeof($where_query_array) > 0) {
$where_sql = sprintf("WHERE %s", implode(" AND ", $where_query_array));
} else {
$where_sql = "";
}
// Sort direction specified?
if (is_numeric($sort_by)) {
$order_sql = "ORDER BY ENTRY_{$sort_by} {$sort_dir} ";
} else {
if (isset($column_sort_alias[$sort_by])) {
$order_sql = "ORDER BY {$column_sort_alias[$sort_by]} {$sort_dir} ";
} else {
$order_sql = "ORDER BY {$sort_by} {$sort_dir}";
}
}
// Limit the display to 10 per page.
$limit_sql = "LIMIT {$offset}, 10";
// Array to store our results in.
$user_array = array();
// Combine the profile columns with the main select SQL.
$query_array_merge = array_merge(array($select_sql), $profile_entry_array, $profile_item_type_array, $profile_item_options_array);
// Construct final SQL query.
$sql = implode(",", $query_array_merge) . "{$from_sql} {$join_sql} ";
$sql .= "{$where_sql} {$having_sql} {$order_sql} {$limit_sql}";
if (!($result = $db->query($sql))) {
return false;
}
// Fetch the number of total results
$sql = "SELECT FOUND_ROWS() AS ROW_COUNT";
if (!($result_count = $db->query($sql))) {
return false;
}
list($user_count) = $result_count->fetch_row();
// Check if we have any results.
if ($result->num_rows == 0 && $user_count > 0 && $page > 1) {
return visitor_log_browse_items($user_search, $profile_items_array, $page - 1, $sort_by, $sort_dir, $hide_empty, $hide_guests);
}
while (($user_data = $result->fetch_assoc()) !== null) {
if (isset($user_data['LOGON']) && isset($user_data['PEER_NICKNAME'])) {
if (!is_null($user_data['PEER_NICKNAME']) && strlen($user_data['PEER_NICKNAME']) > 0) {
$user_data['NICKNAME'] = $user_data['PEER_NICKNAME'];
}
}
if ($user_data['UID'] == 0) {
$user_data['LOGON'] = gettext("Guest");
$user_data['NICKNAME'] = gettext("Guest");
} else {
if (!isset($user_data['LOGON']) || is_null($user_data['LOGON'])) {
$user_data['LOGON'] = gettext("Unknown user");
$user_data['NICKNAME'] = "";
}
}
if (isset($user_data['LAST_VISIT']) && is_numeric($user_data['LAST_VISIT'])) {
$user_data['LAST_VISIT'] = format_date_time($user_data['LAST_VISIT']);
} else {
$user_data['LAST_VISIT'] = gettext("Unknown");
}
if (isset($user_data['REGISTERED']) && is_numeric($user_data['REGISTERED'])) {
$user_data['REGISTERED'] = format_date_time($user_data['REGISTERED']);
} else {
$user_data['REGISTERED'] = gettext("Unknown");
}
if (!isset($user_data['AGE']) || !is_numeric($user_data['AGE'])) {
$user_data['AGE'] = gettext("Unknown");
}
if (!($user_data['DOB'] = format_birthday($user_data['DOB']))) {
$user_data['DOB'] = gettext("Unknown");
}
$user_data['TIMEZONE'] = timezone_id_to_string($user_data['TIMEZONE']);
if (isset($user_data['LOCAL_TIME']) && is_numeric($user_data['LOCAL_TIME'])) {
$user_data['LOCAL_TIME'] = format_date_time($user_data['LOCAL_TIME']);
} else {
$user_data['LOCAL_TIME'] = gettext("Unknown");
}
if (!isset($user_data['POST_COUNT']) || !is_numeric($user_data['POST_COUNT'])) {
$user_data['POST_COUNT'] = 0;
}
if (!isset($user_data['POST_VOTE_TOTAL']) || !is_numeric($user_data['POST_VOTE_TOTAL'])) {
$user_data['POST_VOTE_TOTAL'] = 0;
}
if (!isset($user_data['POST_VOTE_DOWN']) || !is_numeric($user_data['POST_VOTE_DOWN'])) {
$user_data['POST_VOTE_DOWN'] = 0;
}
if (!isset($user_data['POST_VOTE_UP']) || !is_numeric($user_data['POST_VOTE_UP'])) {
$user_data['POST_VOTE_UP'] = 0;
}
$user_data['POST_SCORES'] = sprintf(gettext("%d total, %d down - %d up"), $user_data['POST_VOTE_TOTAL'], $user_data['POST_VOTE_DOWN'], $user_data['POST_VOTE_UP']);
$user_array[] = $user_data;
}
return array('user_count' => $user_count, 'user_array' => $user_array);
}
