/**
* Get number of groups, displayed on the legend
*
* @return int value of the last item displayed
*/
public function get_group_count()
{
$sql = 'SELECT group_legend
FROM ' . GROUPS_TABLE . '
ORDER BY group_legend DESC';
$result = $this->db->sql_query_limit($sql, 1);
$group_count = (int) $this->db->sql_fetchfield('group_legend');
$this->db->sql_freeresult($result);
return $group_count;
}
/**
* Return correct object for specified mode
*
* @param string $mode The feeds mode.
* @param int $forum_id Forum id specified by the script if forum feed provided.
* @param int $topic_id Topic id specified by the script if topic feed provided.
*
* @return object Returns correct feeds object for specified mode.
*/
function get_feed($mode, $forum_id, $topic_id)
{
switch ($mode) {
case 'forums':
if (!$this->config['feed_overall_forums']) {
return false;
}
return $this->container->get('feed.forums');
break;
case 'topics':
case 'topics_new':
if (!$this->config['feed_topics_new']) {
return false;
}
return $this->container->get('feed.topics');
break;
case 'topics_active':
if (!$this->config['feed_topics_active']) {
return false;
}
return $this->container->get('feed.topics_active');
break;
case 'news':
// Get at least one news forum
$sql = 'SELECT forum_id
FROM ' . FORUMS_TABLE . '
WHERE ' . $this->db->sql_bit_and('forum_options', FORUM_OPTION_FEED_NEWS, '<> 0');
$result = $this->db->sql_query_limit($sql, 1, 0, 600);
$s_feed_news = (int) $this->db->sql_fetchfield('forum_id');
$this->db->sql_freeresult($result);
if (!$s_feed_news) {
return false;
}
return $this->container->get('feed.news');
break;
default:
if ($topic_id && $this->config['feed_topic']) {
return $this->container->get('feed.topic')->set_topic_id($topic_id);
} else {
if ($forum_id && $this->config['feed_forum']) {
return $this->container->get('feed.forum')->set_forum_id($forum_id);
} else {
if ($this->config['feed_overall']) {
return $this->container->get('feed.overall');
}
}
}
return false;
break;
}
}
function get_item()
{
if (!isset($this->result)) {
if (!$this->get_sql()) {
return false;
}
// Query database
$sql = $this->db->sql_build_query('SELECT', $this->sql);
$this->result = $this->db->sql_query_limit($sql, $this->num_items);
}
return $this->db->sql_fetchrow($this->result);
}
/**
* Moves an item up/down
*
* @param int $teampage_id teampage_id of the item to be moved
* @param int $delta number of steps:
* - positive = move up
* - negative = move down
* @return bool True if the group was moved successfully
*/
public function move_teampage($teampage_id, $delta)
{
$delta = (int) $delta;
if (!$delta) {
return false;
}
$move_up = $delta > 0 ? true : false;
$data = $this->get_teampage_values($teampage_id);
$current_value = (int) $data['teampage_position'];
if ($current_value != self::GROUP_DISABLED) {
$this->db->sql_transaction('begin');
if (!$move_up && $data['teampage_parent'] == self::NO_PARENT) {
// If we move items down, we need to grab the one sibling more,
// so we do not ignore the children of the previous sibling.
// We will remove the additional sibling later on.
$delta = abs($delta) + 1;
}
$sql = 'SELECT teampage_id, teampage_position
FROM ' . TEAMPAGE_TABLE . '
WHERE teampage_parent = ' . (int) $data['teampage_parent'] . '
AND teampage_position' . ($move_up ? ' < ' : ' > ') . $current_value . '
ORDER BY teampage_position' . ($move_up ? ' DESC' : ' ASC');
$result = $this->db->sql_query_limit($sql, $delta);
$sibling_count = 0;
$sibling_limit = $delta;
// Reset the delta, as we recalculate the new real delta
$delta = 0;
while ($row = $this->db->sql_fetchrow($result)) {
$sibling_count++;
$delta = $current_value - $row['teampage_position'];
// Remove the additional sibling we added previously
// But only, if we included it, this is not be the case
// when we reached the end of our list
if (!$move_up && $data['teampage_parent'] == self::NO_PARENT && $sibling_count == $sibling_limit) {
$delta++;
}
}
$this->db->sql_freeresult($result);
if ($delta) {
$sql = 'SELECT COUNT(teampage_id) as num_items
FROM ' . TEAMPAGE_TABLE . '
WHERE teampage_id = ' . (int) $teampage_id . '
OR teampage_parent = ' . (int) $teampage_id;
$result = $this->db->sql_query($sql);
$num_items = (int) $this->db->sql_fetchfield('num_items');
$this->db->sql_freeresult($result);
// First we move all items between our current value and the target value up/down 1,
// so we have a gap for our item to move.
$sql = 'UPDATE ' . TEAMPAGE_TABLE . '
SET teampage_position = teampage_position' . ($move_up ? ' + ' : ' - ') . $num_items . '
WHERE teampage_position' . ($move_up ? ' >= ' : ' <= ') . ($current_value - $delta) . '
AND teampage_position' . ($move_up ? ' < ' : ' > ') . $current_value . '
AND NOT (teampage_id = ' . (int) $teampage_id . '
OR teampage_parent = ' . (int) $teampage_id . ')';
$this->db->sql_query($sql);
$delta = !$move_up && $data['teampage_parent'] == self::NO_PARENT ? abs($delta) - ($num_items - 1) : abs($delta);
// And now finally, when we moved some other items and built a gap,
// we can move the desired item to it.
$sql = 'UPDATE ' . TEAMPAGE_TABLE . '
SET teampage_position = teampage_position ' . ($move_up ? ' - ' : ' + ') . $delta . '
WHERE teampage_id = ' . (int) $teampage_id . '
OR teampage_parent = ' . (int) $teampage_id;
$this->db->sql_query($sql);
$this->db->sql_transaction('commit');
$this->cache->destroy('sql', TEAMPAGE_TABLE);
return true;
}
$this->db->sql_transaction('commit');
}
$this->cache->destroy('sql', TEAMPAGE_TABLE);
return false;
}
/**
* Change visibility status of one post or all posts of a topic
*
* @param $visibility int Element of {ITEM_APPROVED, ITEM_DELETED, ITEM_REAPPROVE}
* @param $post_id mixed Post ID or array of post IDs to act on,
* if it is empty, all posts of topic_id will be modified
* @param $topic_id int Topic where $post_id is found
* @param $forum_id int Forum where $topic_id is found
* @param $user_id int User performing the action
* @param $time int Timestamp when the action is performed
* @param $reason string Reason why the visibility was changed.
* @param $is_starter bool Is this the first post of the topic changed?
* @param $is_latest bool Is this the last post of the topic changed?
* @param $limit_visibility mixed Limit updating per topic_id to a certain visibility
* @param $limit_delete_time mixed Limit updating per topic_id to a certain deletion time
* @return array Changed post data, empty array if an error occurred.
*/
public function set_post_visibility($visibility, $post_id, $topic_id, $forum_id, $user_id, $time, $reason, $is_starter, $is_latest, $limit_visibility = false, $limit_delete_time = false)
{
if (!in_array($visibility, array(ITEM_APPROVED, ITEM_DELETED, ITEM_REAPPROVE))) {
return array();
}
if ($post_id) {
if (is_array($post_id)) {
$where_sql = $this->db->sql_in_set('post_id', array_map('intval', $post_id));
} else {
$where_sql = 'post_id = ' . (int) $post_id;
}
$where_sql .= ' AND topic_id = ' . (int) $topic_id;
} else {
$where_sql = 'topic_id = ' . (int) $topic_id;
// Limit the posts to a certain visibility and deletion time
// This allows us to only restore posts, that were approved
// when the topic got soft deleted. So previous soft deleted
// and unapproved posts are still soft deleted/unapproved
if ($limit_visibility !== false) {
$where_sql .= ' AND post_visibility = ' . (int) $limit_visibility;
}
if ($limit_delete_time !== false) {
$where_sql .= ' AND post_delete_time = ' . (int) $limit_delete_time;
}
}
$sql = 'SELECT poster_id, post_id, post_postcount, post_visibility
FROM ' . $this->posts_table . '
WHERE ' . $where_sql;
$result = $this->db->sql_query($sql);
$post_ids = $poster_postcounts = $postcounts = $postcount_visibility = array();
while ($row = $this->db->sql_fetchrow($result)) {
$post_ids[] = (int) $row['post_id'];
if ($row['post_visibility'] != $visibility) {
if ($row['post_postcount'] && !isset($poster_postcounts[(int) $row['poster_id']])) {
$poster_postcounts[(int) $row['poster_id']] = 1;
} else {
if ($row['post_postcount']) {
$poster_postcounts[(int) $row['poster_id']]++;
}
}
if (!isset($postcount_visibility[$row['post_visibility']])) {
$postcount_visibility[$row['post_visibility']] = 1;
} else {
$postcount_visibility[$row['post_visibility']]++;
}
}
}
$this->db->sql_freeresult($result);
if (empty($post_ids)) {
return array();
}
$data = array('post_visibility' => (int) $visibility, 'post_delete_user' => (int) $user_id, 'post_delete_time' => (int) $time ?: time(), 'post_delete_reason' => truncate_string($reason, 255, 255, false));
$sql = 'UPDATE ' . $this->posts_table . '
SET ' . $this->db->sql_build_array('UPDATE', $data) . '
WHERE ' . $this->db->sql_in_set('post_id', $post_ids);
$this->db->sql_query($sql);
// Group the authors by post count, to reduce the number of queries
foreach ($poster_postcounts as $poster_id => $num_posts) {
$postcounts[$num_posts][] = $poster_id;
}
// Update users postcounts
foreach ($postcounts as $num_posts => $poster_ids) {
if (in_array($visibility, array(ITEM_REAPPROVE, ITEM_DELETED))) {
$sql = 'UPDATE ' . $this->users_table . '
SET user_posts = 0
WHERE ' . $this->db->sql_in_set('user_id', $poster_ids) . '
AND user_posts < ' . $num_posts;
$this->db->sql_query($sql);
$sql = 'UPDATE ' . $this->users_table . '
SET user_posts = user_posts - ' . $num_posts . '
WHERE ' . $this->db->sql_in_set('user_id', $poster_ids) . '
AND user_posts >= ' . $num_posts;
$this->db->sql_query($sql);
} else {
$sql = 'UPDATE ' . $this->users_table . '
SET user_posts = user_posts + ' . $num_posts . '
WHERE ' . $this->db->sql_in_set('user_id', $poster_ids);
$this->db->sql_query($sql);
}
}
$update_topic_postcount = true;
// Sync the first/last topic information if needed
if (!$is_starter && $is_latest) {
if (!function_exists('update_post_information')) {
include $this->src_root_path . 'includes/functions_posting.' . $this->php_ext;
}
// update_post_information can only update the last post info ...
if ($topic_id) {
update_post_information('topic', $topic_id, false);
}
if ($forum_id) {
update_post_information('forum', $forum_id, false);
}
} else {
if ($is_starter && $topic_id) {
if (!function_exists('sync')) {
include $this->src_root_path . 'includes/functions_admin.' . $this->php_ext;
}
// ... so we need to use sync, if the first post is changed.
// The forum is resynced recursive by sync() itself.
sync('topic', 'topic_id', $topic_id, true);
// sync recalculates the topic replies and forum posts by itself, so we don't do that.
$update_topic_postcount = false;
}
}
$topic_update_array = array();
// Update the topic's reply count and the forum's post count
if ($update_topic_postcount) {
$field_alias = array(ITEM_APPROVED => 'posts_approved', ITEM_UNAPPROVED => 'posts_unapproved', ITEM_DELETED => 'posts_softdeleted', ITEM_REAPPROVE => 'posts_unapproved');
$cur_posts = array_fill_keys($field_alias, 0);
foreach ($postcount_visibility as $post_visibility => $visibility_posts) {
$cur_posts[$field_alias[(int) $post_visibility]] += $visibility_posts;
}
$sql_ary = array();
$recipient_field = $field_alias[$visibility];
foreach ($cur_posts as $field => $count) {
// Decrease the count for the old statuses.
if ($count && $field != $recipient_field) {
$sql_ary[$field] = " - {$count}";
}
}
// Add up the count from all statuses excluding the recipient status.
$count_increase = array_sum(array_diff($cur_posts, array($recipient_field)));
if ($count_increase) {
$sql_ary[$recipient_field] = " + {$count_increase}";
}
if (sizeof($sql_ary)) {
$forum_sql = array();
foreach ($sql_ary as $field => $value_change) {
$topic_update_array[] = 'topic_' . $field . ' = topic_' . $field . $value_change;
$forum_sql[] = 'forum_' . $field . ' = forum_' . $field . $value_change;
}
$sql = 'UPDATE ' . $this->forums_table . '
SET ' . implode(', ', $forum_sql) . '
WHERE forum_id = ' . (int) $forum_id;
$this->db->sql_query($sql);
}
}
if ($post_id) {
$sql = 'SELECT 1 AS has_attachments
FROM ' . POSTS_TABLE . '
WHERE topic_id = ' . (int) $topic_id . '
AND post_attachment = 1
AND post_visibility = ' . ITEM_APPROVED . '
AND ' . $this->db->sql_in_set('post_id', $post_id, true);
$result = $this->db->sql_query_limit($sql, 1);
$has_attachment = (bool) $this->db->sql_fetchfield('has_attachments');
$this->db->sql_freeresult($result);
if ($has_attachment && $visibility == ITEM_APPROVED) {
$topic_update_array[] = 'topic_attachment = 1';
} else {
if (!$has_attachment && $visibility != ITEM_APPROVED) {
$topic_update_array[] = 'topic_attachment = 0';
}
}
}
if (!empty($topic_update_array)) {
// Update the number for replies and posts, and update the attachments flag
$sql = 'UPDATE ' . $this->topics_table . '
SET ' . implode(', ', $topic_update_array) . '
WHERE topic_id = ' . (int) $topic_id;
$this->db->sql_query($sql);
}
return $data;
}
/**
* Performs a search on an author's posts without caring about message contents. Depends on display specific params
*
* @param string $type contains either posts or topics depending on what should be searched for
* @param boolean $firstpost_only if true, only topic starting posts will be considered
* @param array $sort_by_sql contains SQL code for the ORDER BY part of a query
* @param string $sort_key is the key of $sort_by_sql for the selected sorting
* @param string $sort_dir is either a or d representing ASC and DESC
* @param string $sort_days specifies the maximum amount of days a post may be old
* @param array $ex_fid_ary specifies an array of forum ids which should not be searched
* @param string $post_visibility specifies which types of posts the user can view in which forums
* @param int $topic_id is set to 0 or a topic id, if it is not 0 then only posts in this topic should be searched
* @param array $author_ary an array of author ids
* @param string $author_name specifies the author match, when ANONYMOUS is also a search-match
* @param array &$id_ary passed by reference, to be filled with ids for the page specified by $start and $per_page, should be ordered
* @param int $start indicates the first index of the page
* @param int $per_page number of ids each page is supposed to contain
* @return boolean|int total number of results
*/
public function author_search($type, $firstpost_only, $sort_by_sql, $sort_key, $sort_dir, $sort_days, $ex_fid_ary, $post_visibility, $topic_id, $author_ary, $author_name, &$id_ary, &$start, $per_page)
{
// No author? No posts
if (!sizeof($author_ary)) {
return 0;
}
// generate a search_key from all the options to identify the results
$search_key = md5(implode('#', array('', $type, $firstpost_only ? 'firstpost' : '', '', '', $sort_days, $sort_key, $topic_id, implode(',', $ex_fid_ary), $post_visibility, implode(',', $author_ary), $author_name)));
if ($start < 0) {
$start = 0;
}
// try reading the results from cache
$result_count = 0;
if ($this->obtain_ids($search_key, $result_count, $id_ary, $start, $per_page, $sort_dir) == SEARCH_RESULT_IN_CACHE) {
return $result_count;
}
$id_ary = array();
// Create some display specific sql strings
if ($author_name) {
// first one matches post of registered users, second one guests and deleted users
$sql_author = '(' . $this->db->sql_in_set('p.poster_id', array_diff($author_ary, array(ANONYMOUS)), false, true) . ' OR p.post_username ' . $author_name . ')';
} else {
$sql_author = $this->db->sql_in_set('p.poster_id', $author_ary);
}
$sql_fora = sizeof($ex_fid_ary) ? ' AND ' . $this->db->sql_in_set('p.forum_id', $ex_fid_ary, true) : '';
$sql_topic_id = $topic_id ? ' AND p.topic_id = ' . (int) $topic_id : '';
$sql_time = $sort_days ? ' AND p.post_time >= ' . (time() - $sort_days * 86400) : '';
$sql_firstpost = $firstpost_only ? ' AND p.post_id = t.topic_first_post_id' : '';
// Build sql strings for sorting
$sql_sort = $sort_by_sql[$sort_key] . ($sort_dir == 'a' ? ' ASC' : ' DESC');
$sql_sort_table = $sql_sort_join = '';
switch ($sql_sort[0]) {
case 'u':
$sql_sort_table = USERS_TABLE . ' u, ';
$sql_sort_join = $type == 'posts' ? ' AND u.user_id = p.poster_id ' : ' AND u.user_id = t.topic_poster ';
break;
case 't':
$sql_sort_table = $type == 'posts' && !$firstpost_only ? TOPICS_TABLE . ' t, ' : '';
$sql_sort_join = $type == 'posts' && !$firstpost_only ? ' AND t.topic_id = p.topic_id ' : '';
break;
case 'f':
$sql_sort_table = FORUMS_TABLE . ' f, ';
$sql_sort_join = ' AND f.forum_id = p.forum_id ';
break;
}
$m_approve_fid_sql = ' AND ' . $post_visibility;
/**
* Allow changing the query used to search for posts by author in fulltext_mysql
*
* @event core.search_mysql_author_query_before
* @var int result_count The previous result count for the format of the query.
* Set to 0 to force a re-count
* @var string sql_sort_table CROSS JOIN'ed table to allow doing the sort chosen
* @var string sql_sort_join Condition to define how to join the CROSS JOIN'ed table specifyed in sql_sort_table
* @var array author_ary Array of user_id containing the users to filter the results to
* @var string author_name An extra username to search on
* @var string sql_author SQL WHERE condition for the post author ids
* @var int topic_id Limit the search to this topic_id only
* @var string sql_topic_id SQL of topic_id
* @var string sort_by_sql The possible predefined sort types
* @var string sort_key The sort type used from the possible sort types
* @var string sort_dir "a" for ASC or "d" dor DESC for the sort order used
* @var string sql_sort The result SQL when processing sort_by_sql + sort_key + sort_dir
* @var string sort_days Time, in days, that the oldest post showing can have
* @var string sql_time The SQL to search on the time specifyed by sort_days
* @var bool firstpost_only Wether or not to search only on the first post of the topics
* @var array ex_fid_ary Forum ids that must not be searched on
* @var array sql_fora SQL query for ex_fid_ary
* @var string m_approve_fid_sql WHERE clause condition on post_visibility restrictions
* @var int start How many posts to skip in the search results (used for pagination)
* @since 3.1.5-RC1
*/
$vars = array('result_count', 'sql_sort_table', 'sql_sort_join', 'author_ary', 'author_name', 'sql_author', 'topic_id', 'sql_topic_id', 'sort_by_sql', 'sort_key', 'sort_dir', 'sql_sort', 'sort_days', 'sql_time', 'firstpost_only', 'ex_fid_ary', 'sql_fora', 'm_approve_fid_sql', 'start');
extract($this->src_dispatcher->trigger_event('core.search_mysql_author_query_before', compact($vars)));
// If the cache was completely empty count the results
$calc_results = $result_count ? '' : 'SQL_CALC_FOUND_ROWS ';
// Build the query for really selecting the post_ids
if ($type == 'posts') {
$sql = "SELECT {$calc_results}p.post_id\n\t\t\t\tFROM " . $sql_sort_table . POSTS_TABLE . ' p' . ($firstpost_only ? ', ' . TOPICS_TABLE . ' t ' : ' ') . "\n\t\t\t\tWHERE {$sql_author}\n\t\t\t\t\t{$sql_topic_id}\n\t\t\t\t\t{$sql_firstpost}\n\t\t\t\t\t{$m_approve_fid_sql}\n\t\t\t\t\t{$sql_fora}\n\t\t\t\t\t{$sql_sort_join}\n\t\t\t\t\t{$sql_time}\n\t\t\t\tORDER BY {$sql_sort}";
$field = 'post_id';
} else {
$sql = "SELECT {$calc_results}t.topic_id\n\t\t\t\tFROM " . $sql_sort_table . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p\n\t\t\t\tWHERE {$sql_author}\n\t\t\t\t\t{$sql_topic_id}\n\t\t\t\t\t{$sql_firstpost}\n\t\t\t\t\t{$m_approve_fid_sql}\n\t\t\t\t\t{$sql_fora}\n\t\t\t\t\tAND t.topic_id = p.topic_id\n\t\t\t\t\t{$sql_sort_join}\n\t\t\t\t\t{$sql_time}\n\t\t\t\tGROUP BY t.topic_id\n\t\t\t\tORDER BY {$sql_sort}";
$field = 'topic_id';
}
// Only read one block of posts from the db and then cache it
$result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start);
while ($row = $this->db->sql_fetchrow($result)) {
$id_ary[] = (int) $row[$field];
}
$this->db->sql_freeresult($result);
// retrieve the total result count if needed
if (!$result_count) {
$sql_found_rows = 'SELECT FOUND_ROWS() as result_count';
$result = $this->db->sql_query($sql_found_rows);
$result_count = (int) $this->db->sql_fetchfield('result_count');
$this->db->sql_freeresult($result);
if (!$result_count) {
return false;
}
}
if ($start >= $result_count) {
$start = floor(($result_count - 1) / $per_page) * $per_page;
$result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start);
while ($row = $this->db->sql_fetchrow($result)) {
$id_ary[] = (int) $row[$field];
}
$this->db->sql_freeresult($result);
$id_ary = array_unique($id_ary);
}
if (sizeof($id_ary)) {
$this->save_ids($search_key, '', $author_ary, $result_count, $id_ary, $start, $sort_dir);
$id_ary = array_slice($id_ary, 0, $per_page);
return $result_count;
}
return false;
}
/**
* Load the user's notifications
*
* @param array $options Optional options to control what notifications are loaded
* notification_id Notification id to load (or array of notification ids)
* user_id User id to load notifications for (Default: $user->data['user_id'])
* order_by Order by (Default: notification_time)
* order_dir Order direction (Default: DESC)
* limit Number of notifications to load (Default: 5)
* start Notifications offset (Default: 0)
* all_unread Load all unread notifications? If set to true, count_unread is set to true (Default: false)
* count_unread Count all unread notifications? (Default: false)
* count_total Count all notifications? (Default: false)
* @return array Array of information based on the request with keys:
* 'notifications' array of notification type objects
* 'unread_count' number of unread notifications the user has if count_unread is true in the options
* 'total_count' number of notifications the user has if count_total is true in the options
*/
public function load_notifications(array $options = array())
{
// Merge default options
$options = array_merge(array('notification_id' => false, 'user_id' => $this->user->data['user_id'], 'order_by' => 'notification_time', 'order_dir' => 'DESC', 'limit' => 0, 'start' => 0, 'all_unread' => false, 'count_unread' => false, 'count_total' => false), $options);
// If all_unread, count_unread must be true
$options['count_unread'] = $options['all_unread'] ? true : $options['count_unread'];
// Anonymous users and bots never receive notifications
if ($options['user_id'] == $this->user->data['user_id'] && ($this->user->data['user_id'] == ANONYMOUS || $this->user->data['user_type'] == USER_IGNORE)) {
return array('notifications' => array(), 'unread_count' => 0, 'total_count' => 0);
}
$notifications = $user_ids = array();
$load_special = array();
$total_count = $unread_count = 0;
if ($options['count_unread']) {
// Get the total number of unread notifications
$sql = 'SELECT COUNT(n.notification_id) AS unread_count
FROM ' . $this->notifications_table . ' n, ' . $this->notification_types_table . ' nt
WHERE n.user_id = ' . (int) $options['user_id'] . '
AND n.notification_read = 0
AND nt.notification_type_id = n.notification_type_id
AND nt.notification_type_enabled = 1';
$result = $this->db->sql_query($sql);
$unread_count = (int) $this->db->sql_fetchfield('unread_count');
$this->db->sql_freeresult($result);
}
if ($options['count_total']) {
// Get the total number of notifications
$sql = 'SELECT COUNT(n.notification_id) AS total_count
FROM ' . $this->notifications_table . ' n, ' . $this->notification_types_table . ' nt
WHERE n.user_id = ' . (int) $options['user_id'] . '
AND nt.notification_type_id = n.notification_type_id
AND nt.notification_type_enabled = 1';
$result = $this->db->sql_query($sql);
$total_count = (int) $this->db->sql_fetchfield('total_count');
$this->db->sql_freeresult($result);
}
if (!$options['count_total'] || $total_count) {
$rowset = array();
// Get the main notifications
$sql = 'SELECT n.*, nt.notification_type_name
FROM ' . $this->notifications_table . ' n, ' . $this->notification_types_table . ' nt
WHERE n.user_id = ' . (int) $options['user_id'] . ($options['notification_id'] ? is_array($options['notification_id']) ? ' AND ' . $this->db->sql_in_set('n.notification_id', $options['notification_id']) : ' AND n.notification_id = ' . (int) $options['notification_id'] : '') . '
AND nt.notification_type_id = n.notification_type_id
AND nt.notification_type_enabled = 1
ORDER BY n.' . $this->db->sql_escape($options['order_by']) . ' ' . $this->db->sql_escape($options['order_dir']);
$result = $this->db->sql_query_limit($sql, $options['limit'], $options['start']);
while ($row = $this->db->sql_fetchrow($result)) {
$rowset[$row['notification_id']] = $row;
}
$this->db->sql_freeresult($result);
// Get all unread notifications
if ($unread_count && $options['all_unread'] && !empty($rowset)) {
$sql = 'SELECT n.*, nt.notification_type_name
FROM ' . $this->notifications_table . ' n, ' . $this->notification_types_table . ' nt
WHERE n.user_id = ' . (int) $options['user_id'] . '
AND n.notification_read = 0
AND ' . $this->db->sql_in_set('n.notification_id', array_keys($rowset), true) . '
AND nt.notification_type_id = n.notification_type_id
AND nt.notification_type_enabled = 1
ORDER BY n.' . $this->db->sql_escape($options['order_by']) . ' ' . $this->db->sql_escape($options['order_dir']);
$result = $this->db->sql_query_limit($sql, $options['limit'], $options['start']);
while ($row = $this->db->sql_fetchrow($result)) {
$rowset[$row['notification_id']] = $row;
}
$this->db->sql_freeresult($result);
}
foreach ($rowset as $row) {
$notification = $this->get_item_type_class($row['notification_type_name'], $row);
// Array of user_ids to query all at once
$user_ids = array_merge($user_ids, $notification->users_to_query());
// Some notification types also require querying additional tables themselves
if (!isset($load_special[$row['notification_type_name']])) {
$load_special[$row['notification_type_name']] = array();
}
$load_special[$row['notification_type_name']] = array_merge($load_special[$row['notification_type_name']], $notification->get_load_special());
$notifications[$row['notification_id']] = $notification;
}
$this->user_loader->load_users($user_ids);
// Allow each type to load its own special items
foreach ($load_special as $item_type => $data) {
$item_class = $this->get_item_type_class($item_type);
$item_class->load_special($data, $notifications);
}
}
return array('notifications' => $notifications, 'unread_count' => $unread_count, 'total_count' => $total_count);
}
/**
* Checks whether a user can download from a particular PM
*
* @param \src\db\driver\driver_interface $db The database object
* @param int $user_id The user id
* @param int $msg_id The id of the PM that we are downloading from
*
* @return bool Whether the user is allowed to download from that PM or not
*/
function src_download_check_pm_auth($db, $user_id, $msg_id)
{
// Check if the attachment is within the users scope...
$sql = 'SELECT msg_id
FROM ' . PRIVMSGS_TO_TABLE . '
WHERE msg_id = ' . (int) $msg_id . '
AND (
user_id = ' . (int) $user_id . '
OR author_id = ' . (int) $user_id . '
)';
$result = $db->sql_query_limit($sql, 1);
$allowed = (bool) $db->sql_fetchfield('msg_id');
$db->sql_freeresult($result);
return $allowed;
}
/**
* {@inheritdoc}
*/
public function move($item_id, $delta)
{
if ($delta == 0) {
return false;
}
$this->acquire_lock();
$action = $delta > 0 ? 'move_up' : 'move_down';
$delta = abs($delta);
// Keep $this->get_sql_where() here, to ensure we are in the right tree.
$sql = 'SELECT *
FROM ' . $this->table_name . '
WHERE ' . $this->column_item_id . ' = ' . (int) $item_id . '
' . $this->get_sql_where();
$result = $this->db->sql_query_limit($sql, $delta);
$item = $this->db->sql_fetchrow($result);
$this->db->sql_freeresult($result);
if (!$item) {
$this->lock->release();
throw new \OutOfBoundsException($this->message_prefix . 'INVALID_ITEM');
}
/**
* Fetch all the siblings between the item's current spot
* and where we want to move it to. If there are less than $delta
* siblings between the current spot and the target then the
* item will move as far as possible
*/
$sql = "SELECT {$this->column_item_id}, {$this->column_parent_id}, {$this->column_left_id}, {$this->column_right_id}, {$this->column_item_parents}\n\t\t\tFROM " . $this->table_name . '
WHERE ' . $this->column_parent_id . ' = ' . (int) $item[$this->column_parent_id] . '
' . $this->get_sql_where() . '
AND ';
if ($action == 'move_up') {
$sql .= $this->column_right_id . ' < ' . (int) $item[$this->column_right_id] . ' ORDER BY ' . $this->column_right_id . ' DESC';
} else {
$sql .= $this->column_left_id . ' > ' . (int) $item[$this->column_left_id] . ' ORDER BY ' . $this->column_left_id . ' ASC';
}
$result = $this->db->sql_query_limit($sql, $delta);
$target = false;
while ($row = $this->db->sql_fetchrow($result)) {
$target = $row;
}
$this->db->sql_freeresult($result);
if (!$target) {
$this->lock->release();
// The item is already on top or bottom
return false;
}
/**
* $left_id and $right_id define the scope of the items that are affected by the move.
* $diff_up and $diff_down are the values to substract or add to each item's left_id
* and right_id in order to move them up or down.
* $move_up_left and $move_up_right define the scope of the items that are moving
* up. Other items in the scope of ($left_id, $right_id) are considered to move down.
*/
if ($action == 'move_up') {
$left_id = (int) $target[$this->column_left_id];
$right_id = (int) $item[$this->column_right_id];
$diff_up = (int) $item[$this->column_left_id] - (int) $target[$this->column_left_id];
$diff_down = (int) $item[$this->column_right_id] + 1 - (int) $item[$this->column_left_id];
$move_up_left = (int) $item[$this->column_left_id];
$move_up_right = (int) $item[$this->column_right_id];
} else {
$left_id = (int) $item[$this->column_left_id];
$right_id = (int) $target[$this->column_right_id];
$diff_up = (int) $item[$this->column_right_id] + 1 - (int) $item[$this->column_left_id];
$diff_down = (int) $target[$this->column_right_id] - (int) $item[$this->column_right_id];
$move_up_left = (int) $item[$this->column_right_id] + 1;
$move_up_right = (int) $target[$this->column_right_id];
}
// Now do the dirty job
$sql = 'UPDATE ' . $this->table_name . '
SET ' . $this->column_left_id . ' = ' . $this->column_left_id . ' + CASE
WHEN ' . $this->column_left_id . " BETWEEN {$move_up_left} AND {$move_up_right} THEN -{$diff_up}\n\t\t\t\tELSE {$diff_down}\n\t\t\tEND,\n\t\t\t" . $this->column_right_id . ' = ' . $this->column_right_id . ' + CASE
WHEN ' . $this->column_right_id . " BETWEEN {$move_up_left} AND {$move_up_right} THEN -{$diff_up}\n\t\t\t\tELSE {$diff_down}\n\t\t\tEND\n\t\t\tWHERE\n\t\t\t\t" . $this->column_left_id . " BETWEEN {$left_id} AND {$right_id}\n\t\t\t\tAND " . $this->column_right_id . " BETWEEN {$left_id} AND {$right_id}\n\t\t\t\t" . $this->get_sql_where();
$this->db->sql_query($sql);
$this->lock->release();
return true;
}
