function forum_links_get_links_by_page($page = 1)
{
if (!($db = db::get())) {
return false;
}
if (!is_numeric($page) || $page < 1) {
$page = 1;
}
$offset = calculate_page_offset($page, 10);
if (!($table_prefix = get_table_prefix())) {
return false;
}
$forum_links_array = array();
$sql = "SELECT SQL_CALC_FOUND_ROWS LID, POS, URI, TITLE ";
$sql .= "FROM `{$table_prefix}FORUM_LINKS` ";
$sql .= "ORDER BY POS ASC LIMIT {$offset}, 10";
if (!($result = $db->query($sql))) {
return false;
}
$sql = "SELECT FOUND_ROWS() AS ROW_COUNT";
if (!($result_count = $db->query($sql))) {
return false;
}
list($forum_links_count) = $result_count->fetch_row();
if ($result->num_rows == 0 && $forum_links_count > 0 && $page > 1) {
return forum_links_get_links_by_page($page - 1);
}
while ($forum_links_data = $result->fetch_assoc()) {
if (!isset($forum_links_data['URI'])) {
$forum_links_data['URI'] = "";
}
if (!isset($forum_links_data['TITLE'])) {
$forum_links_data['TITLE'] = "-";
}
$forum_links_array[] = $forum_links_data;
}
return array('forum_links_array' => $forum_links_array, 'forum_links_count' => $forum_links_count);
}
function threads_search_user_subscriptions($thread_search, $interest_type = THREAD_NOINTEREST, $page = 1)
{
if (!($db = db::get())) {
return false;
}
if (!isset($_SESSION['UID']) || !is_numeric($_SESSION['UID'])) {
return false;
}
if (!is_numeric($interest_type)) {
$interest_type = THREAD_NOINTEREST;
}
if (!is_numeric($page)) {
$page = 1;
}
if (!($table_prefix = get_table_prefix())) {
return false;
}
$offset = calculate_page_offset($page, 20);
$thread_search = $db->escape($thread_search);
$thread_subscriptions_array = array();
if ($interest_type != THREAD_NOINTEREST) {
$sql = "SELECT SQL_CALC_FOUND_ROWS THREAD.TID, ";
$sql .= "TRIM(CONCAT_WS(' ', COALESCE(FOLDER.PREFIX, ''), THREAD.TITLE)) AS TITLE, ";
$sql .= "USER_THREAD.INTEREST FROM `{$table_prefix}THREAD` THREAD ";
$sql .= "LEFT JOIN `{$table_prefix}FOLDER` FOLDER ON (FOLDER.FID = THREAD.FID) ";
$sql .= "LEFT JOIN `{$table_prefix}USER_THREAD` USER_THREAD ON (USER_THREAD.TID = THREAD.TID ";
$sql .= "AND USER_THREAD.UID = '{$_SESSION['UID']}') WHERE USER_THREAD.INTEREST = '{$interest_type}' ";
$sql .= "AND THREAD.TITLE LIKE '{$thread_search}%' ORDER BY THREAD.MODIFIED DESC ";
$sql .= "LIMIT {$offset}, 20";
} else {
$sql = "SELECT SQL_CALC_FOUND_ROWS THREAD.TID, ";
$sql .= "TRIM(CONCAT_WS(' ', COALESCE(FOLDER.PREFIX, ''), THREAD.TITLE)) AS TITLE, ";
$sql .= "USER_THREAD.INTEREST FROM `{$table_prefix}THREAD` THREAD ";
$sql .= "LEFT JOIN `{$table_prefix}FOLDER` FOLDER ON (FOLDER.FID = THREAD.FID) ";
$sql .= "LEFT JOIN `{$table_prefix}USER_THREAD` USER_THREAD ON (USER_THREAD.TID = THREAD.TID ";
$sql .= "AND USER_THREAD.UID = '{$_SESSION['UID']}') WHERE USER_THREAD.INTEREST <> 0 ";
$sql .= "AND THREAD.TITLE LIKE '{$thread_search}%' ORDER BY THREAD.MODIFIED DESC ";
$sql .= "LIMIT {$offset}, 20";
}
if (!($result = $db->query($sql))) {
return false;
}
$sql = "SELECT FOUND_ROWS() AS ROW_COUNT";
if (!($result_count = $db->query($sql))) {
return false;
}
list($thread_subscriptions_count) = $result_count->fetch_row();
if ($result->num_rows == 0 && $thread_subscriptions_count > 0 && $page > 1) {
return threads_search_user_subscriptions($thread_search, $interest_type, $page - 1);
}
while (($thread_data_array = $result->fetch_assoc()) !== null) {
$thread_subscriptions_array[] = $thread_data_array;
}
return array('thread_count' => $thread_subscriptions_count, 'thread_array' => $thread_subscriptions_array);
}
function folders_search_user_subscriptions($folder_search, $interest_type = FOLDER_NOINTEREST, $page = 1)
{
if (!($db = db::get())) {
return false;
}
if (!isset($_SESSION['UID']) || !is_numeric($_SESSION['UID'])) {
return false;
}
if (!is_numeric($page)) {
$page = 1;
}
if (!is_numeric($interest_type)) {
$interest_type = FOLDER_NOINTEREST;
}
$offset = calculate_page_offset($page, 20);
if (!($table_prefix = get_table_prefix())) {
return false;
}
$folder_search = $db->escape($folder_search);
$folder_subscriptions_array = array();
$folders = folder_get_available();
if ($interest_type != FOLDER_NOINTEREST) {
$sql = "SELECT SQL_CALC_FOUND_ROWS FOLDER.FID, FOLDER.TITLE, ";
$sql .= "USER_FOLDER.INTEREST FROM `{$table_prefix}FOLDER` FOLDER ";
$sql .= "LEFT JOIN `{$table_prefix}USER_FOLDER` USER_FOLDER ";
$sql .= "ON (USER_FOLDER.FID = FOLDER.FID AND USER_FOLDER.UID = '{$_SESSION['UID']}') ";
$sql .= "WHERE USER_FOLDER.INTEREST = '{$interest_type}' ";
$sql .= "AND FOLDER.TITLE LIKE '{$folder_search}%' ";
$sql .= "AND FOLDER.FID IN ({$folders}) ";
$sql .= "ORDER BY FOLDER.POSITION DESC ";
$sql .= "LIMIT {$offset}, 20";
} else {
$sql = "SELECT SQL_CALC_FOUND_ROWS FOLDER.FID, FOLDER.TITLE, ";
$sql .= "USER_FOLDER.INTEREST FROM `{$table_prefix}FOLDER` FOLDER ";
$sql .= "LEFT JOIN `{$table_prefix}USER_FOLDER` USER_FOLDER ";
$sql .= "ON (USER_FOLDER.FID = FOLDER.FID AND USER_FOLDER.UID = '{$_SESSION['UID']}') ";
$sql .= "WHERE FOLDER.FID IN ({$folders}) ";
$sql .= "AND FOLDER.TITLE LIKE '{$folder_search}%' ";
$sql .= "ORDER BY FOLDER.POSITION DESC ";
$sql .= "LIMIT {$offset}, 20";
}
if (!($result = $db->query($sql))) {
return false;
}
$sql = "SELECT FOUND_ROWS() AS ROW_COUNT";
if (!($result_count = $db->query($sql))) {
return false;
}
list($folder_subscriptions_count) = $result_count->fetch_row();
if ($result->num_rows == 0 && $folder_subscriptions_count > 0 && $page > 1) {
return folders_search_user_subscriptions($folder_search, $interest_type, $page - 1);
}
while (($folder_data_array = $result->fetch_assoc()) !== null) {
$folder_subscriptions_array[] = $folder_data_array;
}
return array('folder_count' => $folder_subscriptions_count, 'folder_array' => $folder_subscriptions_array);
}
function profile_items_get_by_page($psid, $page = 1)
{
if (!($db = db::get())) {
return false;
}
if (!is_numeric($psid)) {
return false;
}
if (!is_numeric($page) || $page < 1) {
$page = 1;
}
$offset = calculate_page_offset($page, 10);
if (!($table_prefix = get_table_prefix())) {
return false;
}
$profile_items_array = array();
$sql = "SELECT SQL_CALC_FOUND_ROWS PIID, NAME, TYPE, OPTIONS, POSITION ";
$sql .= "FROM `{$table_prefix}PROFILE_ITEM` WHERE PSID = '{$psid}' ";
$sql .= "ORDER BY POSITION, PIID LIMIT {$offset}, 10";
if (!($result = $db->query($sql))) {
return false;
}
$sql = "SELECT FOUND_ROWS() AS ROW_COUNT";
if (!($result_count = $db->query($sql))) {
return false;
}
list($profile_items_count) = $result_count->fetch_row();
if ($result->num_rows == 0 && $profile_items_count > 0 && $page > 1) {
return profile_items_get_by_page($psid, $page - 1);
}
while ($profile_item = $result->fetch_assoc()) {
$profile_items_array[] = $profile_item;
}
return array('profile_items_array' => $profile_items_array, 'profile_items_count' => $profile_items_count);
}
function perm_group_get_users($gid, $page = 1)
{
if (!($db = db::get())) {
return false;
}
if (!is_numeric($gid)) {
return 0;
}
if (!is_numeric($page) || $page < 1) {
$page = 1;
}
$offset = calculate_page_offset($page, 20);
$group_user_array = array();
$sql = "SELECT SQL_CALC_FOUND_ROWS GROUP_USERS.UID, USER.LOGON, USER.NICKNAME ";
$sql .= "FROM GROUPS INNER JOIN GROUP_USERS ON (GROUP_USERS.GID = GROUPS.GID) ";
$sql .= "INNER JOIN USER ON (USER.UID = GROUP_USERS.UID) WHERE GROUPS.GID = '{$gid}' ";
$sql .= "ORDER BY USER.LOGON LIMIT {$offset}, 20";
if (!($result = $db->query($sql))) {
return false;
}
$sql = "SELECT FOUND_ROWS() AS ROW_COUNT";
if (!($result_count = $db->query($sql))) {
return false;
}
list($group_user_count) = $result_count->fetch_row();
if ($result->num_rows == 0 && $group_user_count > 0 && $page > 1) {
return perm_group_get_users($gid, $page - 1);
}
while (($user_data = $result->fetch_assoc()) !== null) {
$group_user_array[] = $user_data;
}
return array('user_count' => $group_user_count, 'user_array' => $group_user_array);
}
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);
}
function pm_fetch_search_results($sort_by = 'CREATED', $sort_dir = 'DESC', $page = 1, $limit = 10)
{
if (!($db = db::get())) {
return false;
}
$sort_by_array = array('PM.SUBJECT', 'TYPE', 'PM.FROM_UID', 'PM_TYPE.UID', 'CREATED');
$sort_dir_array = array('ASC', 'DESC');
if (!is_numeric($page) || $page < 1) {
$page = 1;
}
if (!is_numeric($limit)) {
$limit = 10;
}
$limit = abs($limit);
$offset = calculate_page_offset($page, $limit);
if (!in_array($sort_by, $sort_by_array)) {
$sort_by = 'CREATED';
}
if (!in_array($sort_dir, $sort_dir_array)) {
$sort_dir = 'DESC';
}
if (!isset($_SESSION['UID']) || !is_numeric($_SESSION['UID'])) {
return false;
}
$sql = "SELECT SQL_CALC_FOUND_ROWS PM.MID, PM.REPLY_TO_MID, PM.FROM_UID, PM_TYPE.TYPE, ";
$sql .= "PM.SUBJECT, UNIX_TIMESTAMP(PM.CREATED) AS CREATED, USER.LOGON AS FROM_LOGON, ";
$sql .= "USER.NICKNAME AS FROM_NICKNAME FROM PM INNER JOIN PM_TYPE ";
$sql .= "ON (PM_TYPE.MID = PM.MID) INNER JOIN PM_SEARCH_RESULTS ";
$sql .= "ON (PM_SEARCH_RESULTS.MID = PM.MID) LEFT JOIN USER ON (USER.UID = PM.FROM_UID) ";
$sql .= "WHERE PM_SEARCH_RESULTS.UID = '{$_SESSION['UID']}' ";
$sql .= "GROUP BY PM.MID ORDER BY {$sort_by} {$sort_dir} LIMIT {$offset}, {$limit}";
if (!($result = $db->query($sql))) {
return false;
}
$sql = "SELECT FOUND_ROWS() AS ROW_COUNT";
if (!($result_count = $db->query($sql))) {
return false;
}
list($message_count) = $result_count->fetch_row();
if ($result->num_rows == 0 && $message_count > 0 && $page > 1) {
return pm_fetch_search_results($sort_by, $sort_dir, $page - 1, $limit);
}
return pm_process_result($result, $message_count);
}
function admin_get_visitor_log($page = 1, $group_by = ADMIN_VISITOR_LOG_GROUP_NONE, $sort_by = 'LAST_LOGON', $sort_dir = 'DESC')
{
if (!($db = db::get())) {
return false;
}
if (!is_numeric($page) || $page < 1) {
$page = 0;
}
if (!($table_prefix = get_table_prefix())) {
return false;
}
if (!($forum_fid = get_forum_fid())) {
return false;
}
if (!isset($_SESSION['UID']) || !is_numeric($_SESSION['UID'])) {
return false;
}
$offset = calculate_page_offset($page, 10);
$group_by_array = array(ADMIN_VISITOR_LOG_GROUP_NONE => 'VISITOR_LOG.VID', ADMIN_VISITOR_LOG_GROUP_IP => 'VISITOR_LOG.IPADDRESS', ADMIN_VISITOR_LOG_GROUP_REFERER => 'VISITOR_LOG.REFERER', ADMIN_VISITOR_LOG_GROUP_USER_AGENT => 'VISITOR_LOG.USER_AGENT');
if (!isset($group_by_array[$group_by])) {
$group_by = ADMIN_LOG_GROUP_NONE;
}
switch ($group_by) {
case ADMIN_VISITOR_LOG_GROUP_IP:
$sort_by_array = array('LOGON' => 'LOGON_COUNT', 'LAST_LOGON' => 'LOGON_COUNT', 'IPADDRESS' => 'VISITOR_LOG.IPADDRESS', 'REFERER' => 'REFERER_COUNT', 'USER_AGENT' => 'USER_AGENT_COUNT');
break;
case ADMIN_VISITOR_LOG_GROUP_REFERER:
$sort_by_array = array('LOGON' => 'LOGON_COUNT', 'LAST_LOGON' => 'LOGON_COUNT', 'IPADDRESS' => 'IPADDRESS_COUNT', 'REFERER' => 'VISITOR_LOG.REFERER', 'USER_AGENT' => 'USER_AGENT_COUNT');
break;
case ADMIN_VISITOR_LOG_GROUP_USER_AGENT:
$sort_by_array = array('LOGON' => 'LOGON_COUNT', 'LAST_LOGON' => 'LOGON_COUNT', 'IPADDRESS' => 'IPADDRESS_COUNT', 'REFERER' => 'REFERER_COUNT', 'USER_AGENT' => 'VISITOR_LOG.USER_AGENT');
break;
default:
$sort_by_array = array('LOGON' => 'USER.LOGON', 'LAST_LOGON' => 'VISITOR_LOG.LAST_LOGON', 'IPADDRESS' => 'VISITOR_LOG.IPADDRESS', 'REFERER' => 'VISITOR_LOG.REFERER', 'USER_AGENT' => 'VISITOR_LOG.USER_AGENT');
break;
}
$sort_dir_array = array('ASC', 'DESC');
if (!isset($sort_by_array[$sort_by])) {
$sort_by = 'LAST_LOGON';
}
if (!in_array($sort_dir, $sort_dir_array)) {
$sort_dir = 'DESC';
}
$users_get_recent_array = array();
$sql = "SELECT SQL_CALC_FOUND_ROWS VISITOR_LOG.UID, USER.LOGON, USER.NICKNAME, ";
$sql .= "USER_PEER.PEER_NICKNAME, UNIX_TIMESTAMP(MAX(VISITOR_LOG.LAST_LOGON)) AS LAST_LOGON, ";
$sql .= "VISITOR_LOG.IPADDRESS, VISITOR_LOG.REFERER, VISITOR_LOG.USER_AGENT, ";
$sql .= "{$group_by_array[$group_by]} AS GROUP_BY, COUNT(*) AS LOGON_COUNT, ";
$sql .= "COUNT(DISTINCT VISITOR_LOG.IPADDRESS) AS IPADDRESS_COUNT, ";
$sql .= "COUNT(DISTINCT VISITOR_LOG.REFERER) AS REFERER_COUNT, ";
$sql .= "COUNT(DISTINCT VISITOR_LOG.USER_AGENT) AS USER_AGENT_COUNT, ";
$sql .= "SEB.SID, SEB.NAME, SEB.URL FROM VISITOR_LOG VISITOR_LOG ";
$sql .= "LEFT JOIN USER USER ON (USER.UID = VISITOR_LOG.UID) ";
$sql .= "LEFT JOIN `{$table_prefix}USER_PEER` USER_PEER ";
$sql .= "ON (USER_PEER.PEER_UID = USER.UID AND USER_PEER.UID = '{$_SESSION['UID']}') ";
$sql .= "LEFT JOIN SEARCH_ENGINE_BOTS SEB ON (SEB.SID = VISITOR_LOG.SID) ";
$sql .= "WHERE VISITOR_LOG.LAST_LOGON IS NOT NULL AND VISITOR_LOG.LAST_LOGON > 0 ";
$sql .= "AND VISITOR_LOG.FORUM = '{$forum_fid}' GROUP BY GROUP_BY ";
$sql .= "ORDER BY {$sort_by_array[$sort_by]} {$sort_dir} ";
$sql .= "LIMIT {$offset}, 10";
if (!($result = $db->query($sql))) {
return false;
}
$sql = "SELECT FOUND_ROWS() AS ROW_COUNT";
if (!($result_count = $db->query($sql))) {
return false;
}
list($users_get_recent_count) = $result_count->fetch_row();
if ($result->num_rows == 0 && $users_get_recent_count > 0 && $page > 1) {
return admin_get_visitor_log($page - 1, $group_by, $sort_by);
}
while (($visitor_array = $result->fetch_assoc()) !== null) {
if (isset($visitor_array['LOGON']) && isset($visitor_array['PEER_NICKNAME'])) {
if (!is_null($visitor_array['PEER_NICKNAME']) && strlen($visitor_array['PEER_NICKNAME']) > 0) {
$visitor_array['NICKNAME'] = $visitor_array['PEER_NICKNAME'];
}
}
if ($visitor_array['UID'] == 0) {
$visitor_array['LOGON'] = gettext("Guest");
$visitor_array['NICKNAME'] = gettext("Guest");
} else {
if (!isset($visitor_array['LOGON']) || is_null($visitor_array['LOGON'])) {
$visitor_array['LOGON'] = gettext("Unknown user");
$visitor_array['NICKNAME'] = "";
}
}
if (!isset($visitor_array['REFERER']) || strlen(trim($visitor_array['REFERER'])) == 0) {
$visitor_array['REFERER'] = "";
}
$users_get_recent_array[] = $visitor_array;
}
return array('user_count' => $users_get_recent_count, 'user_array' => $users_get_recent_array);
}
function pm_fetch_search_results($sort_by = 'CREATED', $sort_dir = 'DESC', $page = 1, $limit = 10)
{
if (!($db = db::get())) {
return false;
}
$sort_by_array = array('PM.SUBJECT', 'TYPE', 'PM.FROM_UID', 'PM.TO_UID', 'CREATED');
$sort_dir_array = array('ASC', 'DESC');
if (!is_numeric($page) || $page < 1) {
$page = 1;
}
if (!is_numeric($limit)) {
$limit = 10;
}
$limit = abs($limit);
$offset = calculate_page_offset($page, $limit);
if (!in_array($sort_by, $sort_by_array)) {
$sort_by = 'CREATED';
}
if (!in_array($sort_dir, $sort_dir_array)) {
$sort_dir = 'DESC';
}
if (($uid = session::get_value('UID')) === false) {
return false;
}
$message_array = array();
$sql = "SELECT SQL_CALC_FOUND_ROWS PM_SEARCH_RESULTS.MID, PM_SEARCH_RESULTS.TYPE, ";
$sql .= "PM_SEARCH_RESULTS.FROM_UID, PM_SEARCH_RESULTS.TO_UID, PM_SEARCH_RESULTS.RECIPIENTS, ";
$sql .= "PM_SEARCH_RESULTS.SUBJECT, UNIX_TIMESTAMP(PM_SEARCH_RESULTS.CREATED) AS CREATED, ";
$sql .= "FUSER.LOGON AS FLOGON, FUSER.NICKNAME AS FNICK, ";
$sql .= "TUSER.LOGON AS TLOGON, TUSER.NICKNAME AS TNICK FROM PM_SEARCH_RESULTS ";
$sql .= "LEFT JOIN PM ON (PM.MID = PM_SEARCH_RESULTS.MID) ";
$sql .= "LEFT JOIN USER FUSER ON (PM_SEARCH_RESULTS.FROM_UID = FUSER.UID) ";
$sql .= "LEFT JOIN USER TUSER ON (PM_SEARCH_RESULTS.TO_UID = TUSER.UID) ";
$sql .= "WHERE PM_SEARCH_RESULTS.UID = '{$uid}' AND PM.MID IS NOT NULL ";
$sql .= "ORDER BY {$sort_by} {$sort_dir} LIMIT {$offset}, {$limit}";
if (!($result = $db->query($sql))) {
return false;
}
$sql = "SELECT FOUND_ROWS() AS ROW_COUNT";
if (!($result_count = $db->query($sql))) {
return false;
}
list($message_count) = $result_count->fetch_row();
if ($result->num_rows == 0 && $message_count > 0 && $page > 1) {
return pm_fetch_search_results($sort_by, $sort_dir, $page - 1, $limit);
}
while ($pm_data = $result->fetch_assoc()) {
if (isset($pm_data['FLOGON']) && isset($pm_data['PFNICK'])) {
if (!is_null($pm_data['PFNICK']) && strlen($pm_data['PFNICK']) > 0) {
$pm_data['FNICK'] = $pm_data['PFNICK'];
}
}
if (isset($pm_data['TLOGON']) && isset($pm_data['PTNICK'])) {
if (!is_null($pm_data['PTNICK']) && strlen($pm_data['PTNICK']) > 0) {
$pm_data['TNICK'] = $pm_data['PTNICK'];
}
}
if (!isset($pm_data['FLOGON'])) {
$pm_data['FLOGON'] = gettext("Unknown user");
}
if (!isset($pm_data['FNICK'])) {
$pm_data['FNICK'] = "";
}
if (!isset($pm_data['TLOGON'])) {
$pm_data['TLOGON'] = gettext("Unknown user");
}
if (!isset($pm_data['TNICK'])) {
$pm_data['TNICK'] = "";
}
$message_array[$pm_data['MID']] = $pm_data;
}
pms_have_attachments($message_array);
return array('message_count' => $message_count, 'message_array' => $message_array);
}
function admin_get_visitor_log($page = 1)
{
if (!($db = db::get())) {
return false;
}
if (!is_numeric($page) || $page < 1) {
$page = 0;
}
$offset = calculate_page_offset($page, 10);
if (!($table_prefix = get_table_prefix())) {
return false;
}
if (!($forum_fid = get_forum_fid())) {
return false;
}
$users_get_recent_array = array();
$uid = session::get_value('UID');
$sql = "SELECT SQL_CALC_FOUND_ROWS VISITOR_LOG.UID, USER.LOGON, ";
$sql .= "USER.NICKNAME, USER_PEER.PEER_NICKNAME, ";
$sql .= "UNIX_TIMESTAMP(VISITOR_LOG.LAST_LOGON) AS LAST_LOGON, ";
$sql .= "VISITOR_LOG.IPADDRESS, VISITOR_LOG.REFERER, ";
$sql .= "SEB.SID, SEB.NAME, SEB.URL FROM VISITOR_LOG VISITOR_LOG ";
$sql .= "LEFT JOIN USER USER ON (USER.UID = VISITOR_LOG.UID) ";
$sql .= "LEFT JOIN `{$table_prefix}USER_PEER` USER_PEER ";
$sql .= "ON (USER_PEER.PEER_UID = USER.UID AND USER_PEER.UID = '{$uid}') ";
$sql .= "LEFT JOIN SEARCH_ENGINE_BOTS SEB ";
$sql .= "ON (SEB.SID = VISITOR_LOG.SID) ";
$sql .= "WHERE VISITOR_LOG.LAST_LOGON IS NOT NULL AND VISITOR_LOG.LAST_LOGON > 0 ";
$sql .= "AND VISITOR_LOG.FORUM = '{$forum_fid}' ";
$sql .= "ORDER BY VISITOR_LOG.LAST_LOGON DESC LIMIT {$offset}, 10";
if (!($result = $db->query($sql))) {
return false;
}
$sql = "SELECT FOUND_ROWS() AS ROW_COUNT";
if (!($result_count = $db->query($sql))) {
return false;
}
list($users_get_recent_count) = $result_count->fetch_row();
if ($result->num_rows == 0 && $users_get_recent_count > 0 && $page > 1) {
return admin_get_visitor_log($page - 1);
}
while ($visitor_array = $result->fetch_assoc()) {
if (isset($visitor_array['LOGON']) && isset($visitor_array['PEER_NICKNAME'])) {
if (!is_null($visitor_array['PEER_NICKNAME']) && strlen($visitor_array['PEER_NICKNAME']) > 0) {
$visitor_array['NICKNAME'] = $visitor_array['PEER_NICKNAME'];
}
}
if ($visitor_array['UID'] == 0) {
$visitor_array['LOGON'] = gettext("Guest");
$visitor_array['NICKNAME'] = gettext("Guest");
} else {
if (!isset($visitor_array['LOGON']) || is_null($visitor_array['LOGON'])) {
$visitor_array['LOGON'] = gettext("Unknown user");
$visitor_array['NICKNAME'] = "";
}
}
if (isset($visitor_array['REFERER']) && strlen(trim($visitor_array['REFERER'])) > 0) {
$forum_uri_preg = preg_quote(html_get_forum_uri(), '/');
if (preg_match("/^{$forum_uri_preg}/iu", trim($visitor_array['REFERER'])) > 0) {
$visitor_array['REFERER'] = "";
}
} else {
$visitor_array['REFERER'] = "";
}
$users_get_recent_array[] = $visitor_array;
}
return array('user_count' => $users_get_recent_count, 'user_array' => $users_get_recent_array);
}
function user_get_word_filter_list($page)
{
if (!($db = db::get())) {
return false;
}
if (!is_numeric($page)) {
$page = 1;
}
$offset = calculate_page_offset($page, 10);
$word_filter_array = array();
if (!($table_prefix = get_table_prefix())) {
return false;
}
if (!isset($_SESSION['UID']) || !is_numeric($_SESSION['UID'])) {
return false;
}
$sql = "SELECT SQL_CALC_FOUND_ROWS FID, FILTER_NAME, MATCH_TEXT, ";
$sql .= "REPLACE_TEXT, FILTER_TYPE, FILTER_ENABLED ";
$sql .= "FROM `{$table_prefix}WORD_FILTER` ";
$sql .= "WHERE UID = '{$_SESSION['UID']}' ORDER BY FID ";
$sql .= "LIMIT {$offset}, 10";
if (!($result = $db->query($sql))) {
return false;
}
$sql = "SELECT FOUND_ROWS() AS ROW_COUNT";
if (!($result_count = $db->query($sql))) {
return false;
}
list($word_filter_count) = $result_count->fetch_row();
if ($result->num_rows == 0 && $word_filter_count > 0 && $page > 1) {
return user_get_word_filter_list($page - 1);
}
while (($word_filter_data = $result->fetch_assoc()) !== null) {
$word_filter_array[$word_filter_data['FID']] = $word_filter_data;
}
return array('word_filter_count' => $word_filter_count, 'word_filter_array' => $word_filter_array);
}
function perm_group_get_users($gid, $page = 1)
{
if (!($db = db::get())) {
return false;
}
if (!is_numeric($gid)) {
return 0;
}
if (!is_numeric($page) || $page < 1) {
$page = 1;
}
$offset = calculate_page_offset($page, 20);
if (!perm_is_group($gid)) {
return false;
}
$group_user_array = array();
$sql = "SELECT SQL_CALC_FOUND_ROWS GROUP_USERS.UID, ";
$sql .= "USER.LOGON, USER.NICKNAME FROM GROUP_USERS ";
$sql .= "INNER JOIN GROUP_PERMS ON (GROUP_PERMS.GID = GROUP_USERS.GID) ";
$sql .= "INNER JOIN USER ON (USER.UID = GROUP_USERS.UID) ";
$sql .= "WHERE GROUP_USERS.GID = '{$gid}' GROUP BY GROUP_USERS.UID ";
$sql .= "LIMIT {$offset}, 20";
if (!($result = $db->query($sql))) {
return false;
}
$sql = "SELECT FOUND_ROWS() AS ROW_COUNT";
if (!($result_count = $db->query($sql))) {
return false;
}
list($group_user_count) = $result_count->fetch_row();
if ($result->num_rows == 0 && $group_user_count > 0 && $page > 1) {
return perm_group_get_users($gid, $page - 1);
}
while ($user_data = $result->fetch_assoc()) {
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 (!isset($user_data['LOGON'])) {
$user_data['LOGON'] = gettext("Unknown user");
}
if (!isset($user_data['NICKNAME'])) {
$user_data['NICKNAME'] = "";
}
$group_user_array[] = $user_data;
}
return array('user_count' => $group_user_count, 'user_array' => $group_user_array);
}
function search_fetch_results($page, $sort_by = null, $sort_dir = null)
{
if (!is_numeric($page) || $page < 1) {
$page = 1;
}
$offset = calculate_page_offset($page, 20);
if (!($db = db::get())) {
return false;
}
if (!($table_prefix = get_table_prefix())) {
return false;
}
if (!isset($_SESSION['UID']) || !is_numeric($_SESSION['UID'])) {
return false;
}
$search_keywords = search_get_keywords();
if (!in_array($sort_dir, array(SEARCH_SORT_ASC, SEARCH_SORT_DESC))) {
$sort_dir = SEARCH_SORT_ASC;
}
$sort_dir = $sort_dir == SEARCH_SORT_DESC ? 'DESC' : 'ASC';
$sql = "SELECT SQL_CALC_FOUND_ROWS THREAD.FID, THREAD.TID, POST.PID, THREAD.BY_UID, POST.FROM_UID, ";
$sql .= "USER_TRACK.USER_VALUE AS KEYWORDS, UNIX_TIMESTAMP(POST.CREATED) AS CREATED, ";
$sql .= "USER.LOGON AS FROM_LOGON, COALESCE(USER_PEER.PEER_NICKNAME, USER.NICKNAME) AS FROM_NICKNAME ";
$sql .= "FROM SEARCH_RESULTS INNER JOIN `{$table_prefix}THREAD` THREAD ON (THREAD.TID = SEARCH_RESULTS.TID) ";
$sql .= "INNER JOIN `{$table_prefix}FOLDER` FOLDER ON (FOLDER.FID = THREAD.FID) ";
$sql .= "INNER JOIN `{$table_prefix}POST` POST ON (POST.TID = SEARCH_RESULTS.TID AND POST.PID = SEARCH_RESULTS.PID) ";
$sql .= "INNER JOIN USER ON (USER.UID = POST.FROM_UID) LEFT JOIN `{$table_prefix}USER_PEER` USER_PEER ";
$sql .= "ON (USER_PEER.PEER_UID = POST.FROM_UID AND USER_PEER.UID = '{$_SESSION['UID']}') ";
$sql .= "LEFT JOIN `{$table_prefix}USER_TRACK` USER_TRACK ON (USER_TRACK.UID = SEARCH_RESULTS.UID ";
$sql .= "AND USER_TRACK.USER_KEY = 'LAST_SEARCH_KEYWORDS') ";
$sql .= "WHERE SEARCH_RESULTS.UID = '{$_SESSION['UID']}' ";
$sql .= "AND ((USER_PEER.RELATIONSHIP & " . USER_IGNORED_COMPLETELY . ") = 0 ";
$sql .= "OR USER_PEER.RELATIONSHIP IS NULL) ";
$sql .= "AND ((USER_PEER.RELATIONSHIP & " . USER_IGNORED . ") = 0 ";
$sql .= "OR USER_PEER.RELATIONSHIP IS NULL) ";
switch ($sort_by) {
case SEARCH_SORT_RELEVANCE:
$sql .= "ORDER BY SEARCH_RESULTS.RELEVANCE {$sort_dir} LIMIT {$offset}, 20";
break;
case SEARCH_SORT_NUM_REPLIES:
$sql .= "ORDER BY THREAD.LENGTH {$sort_dir} LIMIT {$offset}, 20";
break;
case SEARCH_SORT_FOLDER_NAME:
$sql .= "ORDER BY FOLDER.TITLE {$sort_dir} LIMIT {$offset}, 20";
break;
case SEARCH_SORT_AUTHOR_NAME:
$sql .= "ORDER BY FROM_NICKNAME {$sort_dir} LIMIT {$offset}, 20";
break;
default:
$sql .= "ORDER BY POST.CREATED {$sort_dir} LIMIT {$offset}, 20";
break;
}
if (!($result = $db->query($sql))) {
return false;
}
$sql = "SELECT FOUND_ROWS() AS ROW_COUNT";
if (!($result_count = $db->query($sql))) {
return false;
}
list($result_count) = $result_count->fetch_row();
if ($result->num_rows == 0 && $result_count > 0 && $page > 1) {
return search_fetch_results($page - 1, $sort_by, $sort_dir);
}
$search_results_array = array();
while (($search_result = $result->fetch_assoc()) !== null) {
$search_result['KEYWORDS'] = $search_keywords;
if (isset($search_result['FROM_LOGON']) && isset($search_result['PEER_NICKNAME'])) {
if (!is_null($search_result['PEER_NICKNAME']) && strlen($search_result['PEER_NICKNAME']) > 0) {
$search_result['FROM_NICKNAME'] = $search_result['PEER_NICKNAME'];
}
}
if (!isset($search_result['FROM_LOGON'])) {
$search_result['FROM_LOGON'] = gettext("Unknown user");
}
if (!isset($search_result['FROM_NICKNAME'])) {
$search_result['FROM_NICKNAME'] = gettext("Unknown user");
}
$search_results_array[] = $search_result;
}
return array('result_count' => $result_count, 'result_array' => $search_results_array);
}
function links_get_all($invisible = false, $sort_by = "TITLE", $sort_dir = "ASC", $page = 1)
{
if (!($db = db::get())) {
return false;
}
$sort_by_array = array('TITLE', 'DESCRIPTION', 'CREATED', 'RATING');
$sort_dir_array = array('ASC', 'DESC');
if (!is_bool($invisible)) {
$invisible = false;
}
if (!is_numeric($page) || $page < 1) {
return $page = 1;
}
if (!in_array($sort_by, $sort_by_array)) {
$sort_by = 'TITLE';
}
if (!in_array($sort_dir, $sort_dir_array)) {
$sort_dir = 'ASC';
}
if (!($table_prefix = get_table_prefix())) {
return false;
}
$offset = calculate_page_offset($page, 20);
$links_array = array();
if ($invisible === false) {
$sql = "SELECT SQL_CALC_FOUND_ROWS LINKS.LID, LINKS.UID, USER.LOGON, USER.NICKNAME, LINKS.URI, LINKS.TITLE, ";
$sql .= "LINKS.DESCRIPTION, LINKS.VISIBLE, UNIX_TIMESTAMP(LINKS.CREATED) AS CREATED, LINKS.CLICKS, ";
$sql .= "AVG(LINKS_VOTE.RATING) AS RATING FROM `{$table_prefix}LINKS` LINKS ";
$sql .= "LEFT JOIN `{$table_prefix}LINKS_VOTE` LINKS_VOTE ON (LINKS.LID = LINKS_VOTE.LID) ";
$sql .= "LEFT JOIN USER USER ON (LINKS.UID = USER.UID) WHERE LINKS.VISIBLE = 'Y' ";
$sql .= "AND LINKS.APPROVED IS NOT NULL GROUP BY LINKS.LID ";
$sql .= "ORDER BY {$sort_by} {$sort_dir} LIMIT {$offset}, 20";
} else {
$sql = "SELECT SQL_CALC_FOUND_ROWS LINKS.LID, LINKS.UID, USER.LOGON, USER.NICKNAME, LINKS.URI, LINKS.TITLE, ";
$sql .= "LINKS.DESCRIPTION, LINKS.VISIBLE, UNIX_TIMESTAMP(LINKS.CREATED) AS CREATED, LINKS.CLICKS, ";
$sql .= "AVG(LINKS_VOTE.RATING) AS RATING FROM `{$table_prefix}LINKS` LINKS ";
$sql .= "LEFT JOIN `{$table_prefix}LINKS_VOTE` LINKS_VOTE ON (LINKS.LID = LINKS_VOTE.LID) ";
$sql .= "LEFT JOIN USER USER ON (LINKS.UID = USER.UID) WHERE LINKS.APPROVED IS NOT NULL ";
$sql .= "GROUP BY LINKS.LID ORDER BY {$sort_by} {$sort_dir} LIMIT {$offset}, 20";
}
if (!($result = $db->query($sql))) {
return false;
}
$sql = "SELECT FOUND_ROWS() AS ROW_COUNT";
if (!($result_count = $db->query($sql))) {
return false;
}
list($links_count) = $result_count->fetch_row();
if ($result->num_rows == 0 && $links_count > 0 && $page > 1) {
return links_get_all($invisible, $sort_by, $sort_dir, $page - 1);
}
while (($links_data = $result->fetch_assoc()) !== null) {
if (isset($links_data['LOGON']) && isset($links_data['PEER_NICKNAME'])) {
if (!is_null($links_data['PEER_NICKNAME']) && strlen($links_data['PEER_NICKNAME']) > 0) {
$links_data['NICKNAME'] = $links_data['PEER_NICKNAME'];
}
}
if (!isset($links_data['LOGON'])) {
$links_data['LOGON'] = gettext("Unknown user");
}
if (!isset($links_data['NICKNAME'])) {
$links_data['NICKNAME'] = "";
}
$links_array[$links_data['LID']] = $links_data;
}
return array('links_count' => $links_count, 'links_array' => $links_array);
}
function rss_feed_get_feeds($page = 1)
{
if (!($db = db::get())) {
return false;
}
if (!is_numeric($page) || $page < 1) {
$page = 1;
}
$offset = calculate_page_offset($page, 10);
if (!($table_prefix = get_table_prefix())) {
return false;
}
if (!isset($_SESSION['UID']) || !is_numeric($_SESSION['UID'])) {
return false;
}
$rss_feed_array = array();
$sql = "SELECT SQL_CALC_FOUND_ROWS RSS_FEEDS.RSSID, RSS_FEEDS.NAME, USER.LOGON, ";
$sql .= "USER.NICKNAME, USER_PEER.PEER_NICKNAME, RSS_FEEDS.FID, RSS_FEEDS.URL, ";
$sql .= "RSS_FEEDS.PREFIX, RSS_FEEDS.FREQUENCY, RSS_FEEDS.MAX_ITEM_COUNT ";
$sql .= "FROM `{$table_prefix}RSS_FEEDS` RSS_FEEDS ";
$sql .= "LEFT JOIN USER USER ON (USER.UID = RSS_FEEDS.UID) ";
$sql .= "LEFT JOIN `{$table_prefix}USER_PEER` USER_PEER ";
$sql .= "ON (USER_PEER.PEER_UID = RSS_FEEDS.UID ";
$sql .= "AND USER_PEER.UID = '{$_SESSION['UID']}') ";
$sql .= "LIMIT {$offset}, 10";
if (!($result = $db->query($sql))) {
return false;
}
$sql = "SELECT FOUND_ROWS() AS ROW_COUNT";
if (!($result_count = $db->query($sql))) {
return false;
}
list($rss_feed_count) = $result_count->fetch_row();
if ($result->num_rows == 0 && $rss_feed_count > 0 && $page > 1) {
return rss_feed_get_feeds($page - 1);
}
while (($rss_feed_data = $result->fetch_assoc()) !== null) {
if (isset($rss_feed_data['LOGON']) && isset($rss_feed_data['PEER_NICKNAME'])) {
if (!is_null($rss_feed_data['PEER_NICKNAME']) && strlen($rss_feed_data['PEER_NICKNAME']) > 0) {
$rss_feed_data['NICKNAME'] = $rss_feed_data['PEER_NICKNAME'];
}
}
if (!isset($rss_feed_data['LOGON'])) {
$rss_feed_data['LOGON'] = gettext("Unknown user");
}
if (!isset($rss_feed_data['NICKNAME'])) {
$rss_feed_data['NICKNAME'] = "";
}
$rss_feed_array[] = $rss_feed_data;
}
return array('rss_feed_array' => $rss_feed_array, 'rss_feed_count' => $rss_feed_count);
}
function forum_search($forum_search, $page, $sort_by, $sort_dir)
{
if (!($db = db::get())) {
return false;
}
if (!is_numeric($page)) {
return false;
}
$offset = calculate_page_offset($page, 10);
$sort_by_array = array('FORUM_NAME', 'FORUM_DESC', 'LAST_VISIT');
$sort_dir_array = array('ASC', 'DESC');
if (!in_array($sort_by, $sort_by_array)) {
$sort_by = 'LAST_VISIT';
}
if (!in_array($sort_dir, $sort_dir_array)) {
$sort_dir = 'DESC';
}
if (($uid = session::get_value('UID')) === false) {
return false;
}
if (strlen(trim($forum_search)) == 0) {
return false;
}
$forums_array = array();
$forum_search_array = explode(";", $forum_search);
$forum_search_array = array_map('forum_search_array_clean', $forum_search_array);
$forum_search_webtag = implode("%' OR FORUMS.WEBTAG LIKE '%", $forum_search_array);
$forum_search_svalue = implode("%' OR FORUM_SETTINGS.SVALUE LIKE '%", $forum_search_array);
$sql = "SELECT SQL_CALC_FOUND_ROWS CONCAT(FORUMS.DATABASE_NAME, '`.`', FORUMS.WEBTAG, '_') AS PREFIX, ";
$sql .= "FORUM_SETTINGS_NAME.SVALUE AS FORUM_NAME, FORUM_SETTINGS_DESC.SVALUE AS FORUM_DESC, ";
$sql .= "FORUMS.FID, FORUMS.WEBTAG, FORUMS.ACCESS_LEVEL, USER_FORUM.INTEREST FROM FORUMS ";
$sql .= "LEFT JOIN FORUM_SETTINGS FORUM_SETTINGS ON (FORUM_SETTINGS.FID = FORUMS.FID) ";
$sql .= "LEFT JOIN FORUM_SETTINGS FORUM_SETTINGS_NAME ON (FORUM_SETTINGS_NAME.FID = FORUMS.FID AND FORUM_SETTINGS_NAME.SNAME = 'forum_name') ";
$sql .= "LEFT JOIN FORUM_SETTINGS FORUM_SETTINGS_DESC ON (FORUM_SETTINGS_DESC.FID = FORUMS.FID AND FORUM_SETTINGS_DESC.SNAME = 'forum_desc') ";
$sql .= "LEFT JOIN USER_FORUM ON (USER_FORUM.FID = FORUMS.FID AND USER_FORUM.UID = '{$uid}') ";
$sql .= "WHERE FORUMS.ACCESS_LEVEL > -1 AND (FORUMS.WEBTAG LIKE ";
$sql .= "'%{$forum_search_webtag}%' OR FORUM_SETTINGS.SVALUE LIKE ";
$sql .= "'%{$forum_search_svalue}%') GROUP BY FORUMS.FID ";
$sql .= "ORDER BY {$sort_by} {$sort_dir} LIMIT {$offset}, 10";
if (!($result_forums = $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($forums_count) = $result_count->fetch_row();
if ($result->num_rows == 0 && $forums_count > 0 && $page > 1) {
return forum_search($forum_search, $page - 1, $sort_by, $sort_dir);
}
while ($forum_data = $result_forums->fetch_assoc()) {
$forum_fid = $forum_data['FID'];
// Check the forum name is set. If it isn't set it to 'A Beehive Forum'
if (!isset($forum_data['FORUM_NAME']) || strlen(trim($forum_data['FORUM_NAME'])) < 1) {
$forum_data['FORUM_NAME'] = "A Beehive Forum";
}
// Check the forum description is set.
if (!isset($forum_data['FORUM_DESC']) || strlen(trim($forum_data['FORUM_DESC'])) < 1) {
$forum_data['FORUM_DESC'] = "";
}
// Check the LAST_VISIT column to make sure its OK.
if (!isset($forum_data['LAST_VISIT']) || is_null($forum_data['LAST_VISIT'])) {
$forum_data['LAST_VISIT'] = 0;
}
// Unread cut-off stamp.
$unread_cutoff_datetime = forum_get_unread_cutoff_datetime();
// Get available folders for queries below
$folders = folder_get_available_by_forum($forum_fid);
// Get any unread messages
if ($unread_cutoff_datetime !== false) {
$sql = "SELECT SUM(THREAD.LENGTH) - SUM(COALESCE(USER_THREAD.LAST_READ, 0)) AS UNREAD_MESSAGES ";
$sql .= "FROM `{$forum_data['PREFIX']}THREAD` THREAD LEFT JOIN `{$forum_data['PREFIX']}USER_THREAD` USER_THREAD ";
$sql .= "ON (USER_THREAD.TID = THREAD.TID AND USER_THREAD.UID = '{$uid}') WHERE THREAD.FID IN ({$folders}) ";
$sql .= "AND (THREAD.MODIFIED > CAST('{$unread_cutoff_datetime}' AS DATETIME)) ";
if (!($result_unread_count = $db->query($sql))) {
return false;
}
list($unread_messages) = $result_unread_count->fetch_row();
$forum_data['UNREAD_MESSAGES'] = $unread_messages;
} else {
$forum_data['UNREAD_MESSAGES'] = 0;
}
// Total number of messages
$sql = "SELECT SUM(THREAD.LENGTH) AS NUM_MESSAGES FROM `{$forum_data['PREFIX']}THREAD` THREAD ";
$sql .= "WHERE THREAD.FID IN ({$folders}) ";
if (!($result_messages_count = $db->query($sql))) {
return false;
}
$num_messages_data = $result_messages_count->fetch_assoc();
if (!isset($num_messages_data['NUM_MESSAGES']) || is_null($num_messages_data['NUM_MESSAGES'])) {
$forum_data['NUM_MESSAGES'] = 0;
} else {
$forum_data['NUM_MESSAGES'] = $num_messages_data['NUM_MESSAGES'];
}
// Get unread to me message count
$sql = "SELECT COUNT(POST.PID) AS UNREAD_TO_ME ";
$sql .= "FROM `{$forum_data['PREFIX']}THREAD` THREAD ";
$sql .= "LEFT JOIN `{$forum_data['PREFIX']}POST` POST ";
$sql .= "ON (POST.TID = THREAD.TID) WHERE THREAD.FID IN ({$folders}) ";
$sql .= "AND POST.TO_UID = '{$uid}' AND POST.VIEWED IS NULL ";
if (!($result_unread_to_me = $db->query($sql))) {
return false;
}
$forum_unread_post_data = $result_unread_to_me->fetch_assoc();
if (!isset($forum_unread_post_data['UNREAD_TO_ME']) || is_null($forum_unread_post_data['UNREAD_TO_ME'])) {
$forum_data['UNREAD_TO_ME'] = 0;
} else {
$forum_data['UNREAD_TO_ME'] = $forum_unread_post_data['UNREAD_TO_ME'];
}
// Sometimes the USER_THREAD table might have a higher count that the thread
// length due to table corruption. I've only seen this on the SF provided
// webspace but none the less we do this check here anyway.
if ($forum_data['NUM_MESSAGES'] < 0) {
$forum_data['NUM_MESSAGES'] = 0;
}
if ($forum_data['UNREAD_MESSAGES'] < 0) {
$forum_data['UNREAD_MESSAGES'] = 0;
}
if ($forum_data['UNREAD_TO_ME'] < 0) {
$forum_data['UNREAD_TO_ME'] = 0;
}
$forums_array[] = $forum_data;
}
return array('forums_array' => $forums_array, 'forums_count' => $forums_count);
}
function get_my_forums($view_type, $page = 1, $sort_by = 'LAST_VISIT', $sort_dir = 'DESC')
{
if (!($db = db::get())) {
return false;
}
if (!is_numeric($view_type)) {
return false;
}
if (!is_numeric($page) || $page < 1) {
$page = 1;
}
$offset = calculate_page_offset($page, 10);
$sort_by_array = array('FORUM_NAME', 'FORUM_DESC', 'LAST_VISIT');
$sort_dir_array = array('ASC', 'DESC');
if (!in_array($sort_by, $sort_by_array)) {
$sort_by = 'LAST_VISIT';
}
if (!in_array($sort_dir, $sort_dir_array)) {
$sort_dir = 'DESC';
}
if (($uid = session::get_value('UID')) === false) {
return false;
}
$forums_array = array();
if ($view_type == FORUMS_SHOW_ALL) {
$sql = "SELECT SQL_CALC_FOUND_ROWS CONCAT(FORUMS.DATABASE_NAME, '`.`', FORUMS.WEBTAG, '_') AS PREFIX, ";
$sql .= "FORUM_SETTINGS_NAME.SVALUE AS FORUM_NAME, FORUM_SETTINGS_DESC.SVALUE AS FORUM_DESC, ";
$sql .= "FORUMS.FID, FORUMS.WEBTAG, FORUMS.ACCESS_LEVEL, USER_FORUM.INTEREST, UNIX_TIMESTAMP(USER_FORUM.LAST_VISIT) AS LAST_VISIT FROM FORUMS ";
$sql .= "LEFT JOIN FORUM_SETTINGS FORUM_SETTINGS_NAME ON (FORUM_SETTINGS_NAME.FID = FORUMS.FID AND FORUM_SETTINGS_NAME.SNAME = 'forum_name') ";
$sql .= "LEFT JOIN FORUM_SETTINGS FORUM_SETTINGS_DESC ON (FORUM_SETTINGS_DESC.FID = FORUMS.FID AND FORUM_SETTINGS_DESC.SNAME = 'forum_desc') ";
$sql .= "LEFT JOIN USER_FORUM ON (USER_FORUM.FID = FORUMS.FID AND USER_FORUM.UID = '{$uid}') ";
$sql .= "WHERE FORUMS.ACCESS_LEVEL > -1 AND FORUMS.ACCESS_LEVEL < 3 AND (USER_FORUM.INTEREST > -1 ";
$sql .= "OR USER_FORUM.INTEREST IS NULL) ORDER BY {$sort_by} {$sort_dir} LIMIT {$offset}, 10";
} else {
if ($view_type == FORUMS_SHOW_FAVS) {
$sql = "SELECT SQL_CALC_FOUND_ROWS CONCAT(FORUMS.DATABASE_NAME, '`.`', FORUMS.WEBTAG, '_') AS PREFIX, ";
$sql .= "FORUM_SETTINGS_NAME.SVALUE AS FORUM_NAME, FORUM_SETTINGS_DESC.SVALUE AS FORUM_DESC, ";
$sql .= "FORUMS.FID, FORUMS.WEBTAG, FORUMS.ACCESS_LEVEL, USER_FORUM.INTEREST, UNIX_TIMESTAMP(USER_FORUM.LAST_VISIT) AS LAST_VISIT FROM FORUMS ";
$sql .= "LEFT JOIN FORUM_SETTINGS FORUM_SETTINGS_NAME ON (FORUM_SETTINGS_NAME.FID = FORUMS.FID AND FORUM_SETTINGS_NAME.SNAME = 'forum_name') ";
$sql .= "LEFT JOIN FORUM_SETTINGS FORUM_SETTINGS_DESC ON (FORUM_SETTINGS_DESC.FID = FORUMS.FID AND FORUM_SETTINGS_DESC.SNAME = 'forum_desc') ";
$sql .= "LEFT JOIN USER_FORUM ON (USER_FORUM.FID = FORUMS.FID AND USER_FORUM.UID = '{$uid}') ";
$sql .= "WHERE FORUMS.ACCESS_LEVEL > -1 AND FORUMS.ACCESS_LEVEL < 3 AND USER_FORUM.INTEREST = 1 ";
$sql .= "ORDER BY {$sort_by} {$sort_dir} LIMIT {$offset}, 10";
} else {
if ($view_type == FORUMS_SHOW_IGNORED) {
$sql = "SELECT SQL_CALC_FOUND_ROWS CONCAT(FORUMS.DATABASE_NAME, '`.`', FORUMS.WEBTAG, '_') AS PREFIX, ";
$sql .= "FORUM_SETTINGS_NAME.SVALUE AS FORUM_NAME, FORUM_SETTINGS_DESC.SVALUE AS FORUM_DESC, ";
$sql .= "FORUMS.FID, FORUMS.WEBTAG, FORUMS.ACCESS_LEVEL, USER_FORUM.INTEREST, UNIX_TIMESTAMP(USER_FORUM.LAST_VISIT) AS LAST_VISIT FROM FORUMS ";
$sql .= "LEFT JOIN FORUM_SETTINGS FORUM_SETTINGS_NAME ON (FORUM_SETTINGS_NAME.FID = FORUMS.FID AND FORUM_SETTINGS_NAME.SNAME = 'forum_name') ";
$sql .= "LEFT JOIN FORUM_SETTINGS FORUM_SETTINGS_DESC ON (FORUM_SETTINGS_DESC.FID = FORUMS.FID AND FORUM_SETTINGS_DESC.SNAME = 'forum_desc') ";
$sql .= "LEFT JOIN USER_FORUM ON (USER_FORUM.FID = FORUMS.FID AND USER_FORUM.UID = '{$uid}') ";
$sql .= "WHERE FORUMS.ACCESS_LEVEL > -1 AND FORUMS.ACCESS_LEVEL < 3 AND USER_FORUM.INTEREST = -1 ";
$sql .= "ORDER BY {$sort_by} {$sort_dir} LIMIT {$offset}, 10";
}
}
}
if (!($result = $db->query($sql))) {
return false;
}
$sql = "SELECT FOUND_ROWS() AS ROW_COUNT";
if (!($result_count = $db->query($sql))) {
return false;
}
list($forums_count) = $result_count->fetch_row();
if ($result->num_rows == 0 && $forums_count > 0 && $page > 1) {
return get_my_forums($view_type, $page - 1, $sort_by, $sort_dir);
}
while ($forum_data = $result->fetch_assoc()) {
$forum_fid = $forum_data['FID'];
if (!isset($forum_data['FORUM_NAME']) || strlen(trim($forum_data['FORUM_NAME'])) < 1) {
$forum_data['FORUM_NAME'] = gettext("A Beehive Forum");
}
if (!isset($forum_data['FORUM_DESC']) || strlen(trim($forum_data['FORUM_DESC'])) < 1) {
$forum_data['FORUM_DESC'] = "";
}
if (!isset($forum_data['LAST_VISIT']) || is_null($forum_data['LAST_VISIT'])) {
$forum_data['LAST_VISIT'] = 0;
}
$unread_cutoff_datetime = forum_get_unread_cutoff_datetime();
$folders = folder_get_available_by_forum($forum_fid);
if ($unread_cutoff_datetime !== false) {
$sql = "SELECT SUM(THREAD.LENGTH) - SUM(COALESCE(USER_THREAD.LAST_READ, 0)) FROM `{$forum_data['PREFIX']}THREAD` THREAD ";
$sql .= "LEFT JOIN `{$forum_data['PREFIX']}USER_THREAD` USER_THREAD ON (USER_THREAD.TID = THREAD.TID AND USER_THREAD.UID = '{$uid}') ";
$sql .= "LEFT JOIN `{$forum_data['PREFIX']}USER_FOLDER` USER_FOLDER ON (USER_FOLDER.FID = THREAD.FID AND USER_FOLDER.UID = '{$uid}') ";
$sql .= "WHERE THREAD.FID IN ({$folders}) AND (USER_FOLDER.INTEREST > -1 OR USER_FOLDER.INTEREST IS NULL) ";
$sql .= "AND (USER_THREAD.INTEREST > -1 OR USER_THREAD.INTEREST IS NULL) ";
$sql .= "AND (THREAD.MODIFIED > CAST('{$unread_cutoff_datetime}' AS DATETIME)) ";
if (!($result_unread_count = $db->query($sql))) {
return false;
}
list($unread_messages) = $result_unread_count->fetch_row();
$forum_data['UNREAD_MESSAGES'] = $unread_messages;
} else {
$forum_data['UNREAD_MESSAGES'] = 0;
}
$sql = "SELECT COALESCE(SUM(THREAD.LENGTH), 0) FROM `{$forum_data['PREFIX']}THREAD` THREAD ";
$sql .= "LEFT JOIN `{$forum_data['PREFIX']}USER_THREAD` USER_THREAD ON (USER_THREAD.TID = THREAD.TID AND USER_THREAD.UID = '{$uid}') ";
$sql .= "LEFT JOIN `{$forum_data['PREFIX']}USER_FOLDER` USER_FOLDER ON (USER_FOLDER.FID = THREAD.FID AND USER_FOLDER.UID = '{$uid}') ";
$sql .= "WHERE THREAD.FID IN ({$folders}) AND (USER_FOLDER.INTEREST > -1 OR USER_FOLDER.INTEREST IS NULL) ";
$sql .= "AND (USER_THREAD.INTEREST > -1 OR USER_THREAD.INTEREST IS NULL) ";
if (!($result_messages_count = $db->query($sql))) {
return false;
}
list($num_messages) = $result_messages_count->fetch_row();
$forum_data['NUM_MESSAGES'] = $num_messages;
$sql = "SELECT COUNT(POST.PID) AS UNREAD_TO_ME ";
$sql .= "FROM `{$forum_data['PREFIX']}THREAD` THREAD ";
$sql .= "LEFT JOIN `{$forum_data['PREFIX']}POST` POST ";
$sql .= "ON (POST.TID = THREAD.TID) WHERE THREAD.FID IN ({$folders}) ";
$sql .= "AND POST.TO_UID = '{$uid}' AND POST.VIEWED IS NULL ";
if (!($result_unread_to_me = $db->query($sql))) {
return false;
}
list($unread_to_me) = $result_unread_to_me->fetch_row();
$forum_data['UNREAD_TO_ME'] = $unread_to_me;
if ($forum_data['NUM_MESSAGES'] < 0) {
$forum_data['NUM_MESSAGES'] = 0;
}
if ($forum_data['UNREAD_MESSAGES'] < 0) {
$forum_data['UNREAD_MESSAGES'] = 0;
}
if ($forum_data['UNREAD_TO_ME'] < 0) {
$forum_data['UNREAD_TO_ME'] = 0;
}
$forums_array[] = $forum_data;
}
return array('forums_array' => $forums_array, 'forums_count' => $forums_count);
}
