/** * Returns whether or not the current user has new quote notifications. * @return int Number of unread quote notifications */ public static function has_new_quote_notifications() { $QuoteNotificationsCount = G::$Cache->get_value('notify_quoted_' . G::$LoggedUser['ID']); if ($QuoteNotificationsCount === false) { $sql = "\n\t\t\t\tSELECT COUNT(1)\n\t\t\t\tFROM users_notify_quoted AS q\n\t\t\t\t\tLEFT JOIN forums_topics AS t ON t.ID = q.PageID\n\t\t\t\t\tLEFT JOIN forums AS f ON f.ID = t.ForumID\n\t\t\t\t\tLEFT JOIN collages AS c ON q.Page = 'collages' AND c.ID = q.PageID\n\t\t\t\tWHERE q.UserID = " . G::$LoggedUser['ID'] . "\n\t\t\t\t\tAND q.UnRead\n\t\t\t\t\tAND (q.Page != 'forums' OR " . Forums::user_forums_sql() . ")\n\t\t\t\t\tAND (q.Page != 'collages' OR c.Deleted = '0')"; $QueryID = G::$DB->get_query_id(); G::$DB->query($sql); list($QuoteNotificationsCount) = G::$DB->next_record(); G::$DB->set_query_id($QueryID); G::$Cache->cache_value('notify_quoted_' . G::$LoggedUser['ID'], $QuoteNotificationsCount, 0); } return (int) $QuoteNotificationsCount; }
} if ($_GET['catchup']) { $DB->query("UPDATE users_notify_quoted SET UnRead = '0' WHERE UserID = '{$LoggedUser['ID']}'"); $Cache->delete_value('notify_quoted_' . $LoggedUser['ID']); header('Location: userhistory.php?action=quote_notifications'); die; } if (isset($LoggedUser['PostsPerPage'])) { $PerPage = $LoggedUser['PostsPerPage']; } else { $PerPage = POSTS_PER_PAGE; } list($Page, $Limit) = Format::page_limit($PerPage); // Get $Limit last quote notifications // We deal with the information about torrents and requests later on... $sql = "\n\tSELECT\n\t\tSQL_CALC_FOUND_ROWS\n\t\tq.Page,\n\t\tq.PageID,\n\t\tq.PostID,\n\t\tq.QuoterID,\n\t\tq.Date,\n\t\tq.UnRead,\n\t\tf.ID as ForumID,\n\t\tf.Name as ForumName,\n\t\tt.Title as ForumTitle,\n\t\ta.Name as ArtistName,\n\t\tc.Name as CollageName\n\tFROM users_notify_quoted AS q\n\t\tLEFT JOIN forums_topics AS t ON t.ID = q.PageID\n\t\tLEFT JOIN forums AS f ON f.ID = t.ForumID\n\t\tLEFT JOIN artists_group AS a ON a.ArtistID = q.PageID\n\t\tLEFT JOIN collages AS c ON c.ID = q.PageID\n\tWHERE q.UserID = {$LoggedUser['ID']}\n\t\tAND (q.Page != 'forums' OR " . Forums::user_forums_sql() . ")\n\t\tAND (q.Page != 'collages' OR c.Deleted = '0')\n\t\t{$UnreadSQL}\n\tORDER BY q.Date DESC\n\tLIMIT {$Limit}"; $DB->query($sql); $Results = $DB->to_array(false, MYSQLI_ASSOC, false); $DB->query('SELECT FOUND_ROWS()'); list($NumResults) = $DB->next_record(); $TorrentGroups = $Requests = array(); foreach ($Results as $Result) { if ($Result['Page'] == 'torrents') { $TorrentGroups[] = $Result['PageID']; } elseif ($Result['Page'] == 'requests') { $Requests[] = $Result['PageID']; } } $TorrentGroups = Torrents::get_groups($TorrentGroups, true, true, false); $Requests = Requests::get_requests($Requests); //Start printing page
} $SQL .= ' p.ID, p.AddedTime, p.Body, p.EditedUserID, p.EditedTime, ed.Username, p.TopicID, t.Title, t.LastPostID,'; if ($UserID === $LoggedUser['ID']) { $SQL .= ' l.PostID AS LastRead,'; } $SQL .= "\n\t\t\t\tt.IsLocked,\n\t\t\t\tt.IsSticky\n\t\t\tFROM forums_posts AS p\n\t\t\t\tLEFT JOIN users_main AS um ON um.ID = p.AuthorID\n\t\t\t\tLEFT JOIN users_info AS ui ON ui.UserID = p.AuthorID\n\t\t\t\tLEFT JOIN users_main AS ed ON ed.ID = p.EditedUserID\n\t\t\t\tJOIN forums_topics AS t ON t.ID = p.TopicID\n\t\t\t\tJOIN forums AS f ON f.ID = t.ForumID\n\t\t\t\tLEFT JOIN forums_last_read_topics AS l ON l.UserID = {$UserID} AND l.TopicID = t.ID\n\t\t\tWHERE p.AuthorID = {$UserID}\n\t\t\t\tAND " . Forums::user_forums_sql(); if ($ShowUnread) { $SQL .= ' AND ((t.IsLocked = \'0\' OR t.IsSticky = \'1\') AND (l.PostID < t.LastPostID OR l.PostID IS NULL) ) '; } $SQL .= ' ORDER BY p.ID DESC'; if ($ShowGrouped) { $SQL .= ' ) AS sub GROUP BY TopicID ORDER BY ID DESC'; } $SQL .= "\n\t\tLIMIT {$Limit}";
* Page (artist, collages, requests, torrents or forums) * PageID (ArtistID, CollageID, RequestID, GroupID, TopicID) * PostID (of the last read post) * ForumID * ForumName * Name (for artists and collages; carries the topic title for forum subscriptions) * LastPost (PostID of the last post) * LastPostTime * LastReadBody * LastReadEditedTime * LastReadUserID * LastReadUsername * LastReadAvatar * LastReadEditedUserID */ $DB->query("\n\t(SELECT\n\t\tSQL_CALC_FOUND_ROWS\n\t\ts.Page,\n\t\ts.PageID,\n\t\tlr.PostID,\n\t\tnull AS ForumID,\n\t\tnull AS ForumName,\n\t\tIF(s.Page = 'artist', a.Name, co.Name) AS Name,\n\t\tc.ID AS LastPost,\n\t\tc.AddedTime AS LastPostTime,\n\t\tc_lr.Body AS LastReadBody,\n\t\tc_lr.EditedTime AS LastReadEditedTime,\n\t\tum.ID AS LastReadUserID,\n\t\tum.Username AS LastReadUsername,\n\t\tui.Avatar AS LastReadAvatar,\n\t\tc_lr.EditedUserID AS LastReadEditedUserID\n\tFROM users_subscriptions_comments AS s\n\t\tLEFT JOIN users_comments_last_read AS lr ON lr.UserID = {$LoggedUser['ID']} AND lr.Page = s.Page AND lr.PageID = s.PageID\n\t\tLEFT JOIN artists_group AS a ON s.Page = 'artist' AND a.ArtistID = s.PageID\n\t\tLEFT JOIN collages AS co ON s.Page = 'collages' AND co.ID = s.PageID\n\t\tLEFT JOIN comments AS c ON c.ID = (\n\t\t\t\t\tSELECT MAX(ID)\n\t\t\t\t\tFROM comments\n\t\t\t\t\tWHERE Page = s.Page\n\t\t\t\t\t\tAND PageID = s.PageID\n\t\t\t\t)\n\t\tLEFT JOIN comments AS c_lr ON c_lr.ID = lr.PostID\n\t\tLEFT JOIN users_main AS um ON um.ID = c_lr.AuthorID\n\t\tLEFT JOIN users_info AS ui ON ui.UserID = um.ID\n\tWHERE s.UserID = {$LoggedUser['ID']} AND s.Page IN ('artist', 'collages', 'requests', 'torrents') AND (s.Page != 'collages' OR co.Deleted = '0')" . ($ShowUnread ? ' AND c.ID > IF(lr.PostID IS NULL, 0, lr.PostID)' : '') . "\n\tGROUP BY s.PageID)\n\tUNION ALL\n\t(SELECT 'forums', s.TopicID, lr.PostID, f.ID, f.Name, t.Title, p.ID, p.AddedTime, p_lr.Body, p_lr.EditedTime, um.ID, um.Username, ui.Avatar, p_lr.EditedUserID\n\tFROM users_subscriptions AS s\n\t\tLEFT JOIN forums_last_read_topics AS lr ON lr.UserID = {$LoggedUser['ID']} AND s.TopicID = lr.TopicID\n\t\tLEFT JOIN forums_topics AS t ON t.ID = s.TopicID\n\t\tLEFT JOIN forums AS f ON f.ID = t.ForumID\n\t\tLEFT JOIN forums_posts AS p ON p.ID = (\n\t\t\t\t\tSELECT MAX(ID)\n\t\t\t\t\tFROM forums_posts\n\t\t\t\t\tWHERE TopicID = s.TopicID\n\t\t\t\t)\n\t\tLEFT JOIN forums_posts AS p_lr ON p_lr.ID = lr.PostID\n\t\tLEFT JOIN users_main AS um ON um.ID = p_lr.AuthorID\n\t\tLEFT JOIN users_info AS ui ON ui.UserID = um.ID\n\tWHERE s.UserID = {$LoggedUser['ID']}" . ($ShowUnread ? " AND p.ID > IF(t.IsLocked = '1' AND t.IsSticky = '0'" . ", p.ID, IF(lr.PostID IS NULL, 0, lr.PostID))" : '') . ' AND ' . Forums::user_forums_sql() . "\n\tGROUP BY t.ID)\n\tORDER BY LastPostTime DESC\n\tLIMIT {$Limit}"); $Results = $DB->to_array(false, MYSQLI_ASSOC, false); $DB->query('SELECT FOUND_ROWS()'); list($NumResults) = $DB->next_record(); $Debug->log_var($Results, 'Results'); $TorrentGroups = $Requests = array(); foreach ($Results as $Result) { if ($Result['Page'] == 'torrents') { $TorrentGroups[] = $Result['PageID']; } elseif ($Result['Page'] == 'requests') { $Requests[] = $Result['PageID']; } } $TorrentGroups = Torrents::get_groups($TorrentGroups, true, true, false); $Requests = Requests::get_requests($Requests); ?>
} if (!empty($ThreadAfterDate)) { $SQL .= " AND t.CreatedTime >= '{$ThreadAfterDate}'"; } if (!empty($ThreadBeforeDate)) { $SQL .= " AND t.CreatedTime <= '{$ThreadBeforeDate}'"; } if (!empty($PostAfterDate)) { $SQL .= " AND p.AddedTime >= '{$PostAfterDate}'"; } if (!empty($PostBeforeDate)) { $SQL .= " AND p.AddedTime <= '{$PostBeforeDate}'"; } $SQL .= "\n\t\tORDER BY p.AddedTime DESC\n\t\tLIMIT {$Limit}"; } else { $SQL = "\n\t\tSELECT\n\t\t\tSQL_CALC_FOUND_ROWS\n\t\t\tt.ID,\n\t\t\tt.Title,\n\t\t\tt.ForumID,\n\t\t\tf.Name,\n\t\t\tt.LastPostTime,\n\t\t\t'',\n\t\t\t'',\n\t\t\tt.CreatedTime\n\t\tFROM forums_topics AS t\n\t\t\tJOIN forums AS f ON f.ID = t.ForumID\n\t\tWHERE " . Forums::user_forums_sql() . ' AND '; $SQL .= "t.Title LIKE '%"; $SQL .= implode("%' AND t.Title LIKE '%", $Words); $SQL .= "%' "; if (isset($SearchForums)) { $SQL .= " AND f.ID IN ({$SearchForums})"; } if (isset($AuthorID)) { $SQL .= " AND t.AuthorID = '{$AuthorID}' "; } if (!empty($ThreadAfterDate)) { $SQL .= " AND t.CreatedTime >= '{$ThreadAfterDate}'"; } if (!empty($ThreadBeforeDate)) { $SQL .= " AND t.CreatedTime <= '{$ThreadBeforeDate}'"; }
} list($Page, $Limit) = Format::page_limit($PerPage); $ShowUnread = !isset($_GET['showunread']) && !isset($HeavyInfo['SubscriptionsUnread']) || isset($HeavyInfo['SubscriptionsUnread']) && !!$HeavyInfo['SubscriptionsUnread'] || isset($_GET['showunread']) && !!$_GET['showunread']; $ShowCollapsed = !isset($_GET['collapse']) && !isset($HeavyInfo['SubscriptionsCollapse']) || isset($HeavyInfo['SubscriptionsCollapse']) && !!$HeavyInfo['SubscriptionsCollapse'] || isset($_GET['collapse']) && !!$_GET['collapse']; $sql = ' SELECT SQL_CALC_FOUND_ROWS MAX(p.ID) AS ID FROM forums_posts AS p LEFT JOIN forums_topics AS t ON t.ID = p.TopicID JOIN users_subscriptions AS s ON s.TopicID = t.ID LEFT JOIN forums AS f ON f.ID = t.ForumID LEFT JOIN forums_last_read_topics AS l ON p.TopicID = l.TopicID AND l.UserID = s.UserID WHERE s.UserID = ' . $LoggedUser['ID'] . ' AND p.ID <= IFNULL(l.PostID, t.LastPostID) AND ' . Forums::user_forums_sql(); if ($ShowUnread) { $sql .= ' AND IF(l.PostID IS NULL OR (t.IsLocked = \'1\' && t.IsSticky = \'0\'), t.LastPostID, l.PostID) < t.LastPostID'; } $sql .= "\n\tGROUP BY t.ID\n\tORDER BY t.LastPostID DESC\n\tLIMIT {$Limit}"; $PostIDs = $DB->query($sql); $DB->query('SELECT FOUND_ROWS()'); list($NumResults) = $DB->next_record(); if ($NumResults > $PerPage * ($Page - 1)) { $DB->set_query_id($PostIDs); $PostIDs = $DB->collect('ID'); $sql = ' SELECT f.ID AS ForumID, f.Name AS ForumName,