/// <summary> /// Gets the active threads visible to the user. /// </summary> /// <param name="accessableForums">A list of accessable forums IDs, which the user has permission to access.</param> /// <param name="hoursThreshold">The hours threshold for the query to fetch the active threads. All threads within this threshold's period of time (in hours) /// are fetched.</param> /// <param name="forumsWithThreadsFromOthers">The forums for which the calling user can view other users' threads. Can be null</param> /// <param name="userId">The userid of the calling user.</param> /// <returns>a list with objects representing the Active threads</returns> public static async Task <List <AggregatedThreadRow> > GetActiveThreadsAggregatedData(List <int> accessableForums, short hoursThreshold, List <int> forumsWithThreadsFromOthers, int userId) { if (accessableForums == null || accessableForums.Count <= 0) { return(null); } var qf = new QueryFactory(); var q = qf.Create() .Select <AggregatedThreadRow>(ThreadGuiHelper.BuildQueryProjectionForAllThreadsWithStatsWithForumName(qf).ToArray()) .From(ThreadGuiHelper.BuildFromClauseForAllThreadsWithStats(qf) .InnerJoin(qf.Forum).On(ThreadFields.ForumID.Equal(ForumFields.ForumID))) .Where(ThreadFields.ForumID.In(accessableForums) .And(ThreadFields.IsClosed.Equal(false)) .And(ThreadFields.MarkedAsDone.Equal(false)) .And(MessageFields.PostingDate.Source("LastMessage").GreaterEqual(DateTime.Now.AddHours((double)0 - hoursThreshold))) .And(ThreadGuiHelper.CreateThreadFilter(forumsWithThreadsFromOthers, userId))) .OrderBy(MessageFields.PostingDate.Source("LastMessage").Ascending()); using (var adapter = new DataAccessAdapter()) { return(await adapter.FetchQueryAsync(q).ConfigureAwait(false)); } }
/// <summary> /// Gets the bookmarks with statistics for the user specified. /// </summary> /// <param name="userID">User ID.</param> /// <returns></returns> public static async Task <List <AggregatedThreadRow> > GetBookmarksAggregatedDataAsync(int userID) { var qf = new QueryFactory(); var q = qf.Create() .Select <AggregatedThreadRow>(ThreadGuiHelper.BuildQueryProjectionForAllThreadsWithStatsWithForumName(qf).ToArray()) .From(ThreadGuiHelper.BuildFromClauseForAllThreadsWithStats(qf) .InnerJoin(qf.Forum).On(ThreadFields.ForumID.Equal(ForumFields.ForumID))) .Where(ThreadFields.ThreadID.In(qf.Create() .Select(BookmarkFields.ThreadID) .Where(BookmarkFields.UserID.Equal(userID)))) .OrderBy(MessageFields.PostingDate.Source("LastMessage").Descending()); using (var adapter = new DataAccessAdapter()) { return(await adapter.FetchQueryAsync(q).ConfigureAwait(false)); } }
/// <summary> /// Gets the last pageSize threads in which the user specified participated with one or more messages for the page specified. /// Threads which aren't visible for the calling user are filtered out. If pageNumber is 0, pageSize is used to limit the list to the pageSize /// </summary> /// <param name="accessableForums">A list of accessable forums IDs, which the user calling the method has permission to access.</param> /// <param name="participantUserId">The participant user ID of the user of which the threads have to be obtained.</param> /// <param name="forumsWithThreadsFromOthers">The forums with threads from others.</param> /// <param name="callingUserId">The calling user ID.</param> /// <param name="pageSize">Size of the page.</param> /// <param name="pageNumber">The page number to fetch.</param> /// <returns>a list with objects representing the last threads for the user</returns> public static async Task <List <AggregatedThreadRow> > GetLastThreadsForUserAggregatedDataAsync(List <int> accessableForums, int participantUserId, List <int> forumsWithThreadsFromOthers, int callingUserId, int pageSize, int pageNumber) { // return null, if the user does not have a valid list of forums to access if (accessableForums == null || accessableForums.Count <= 0) { return(null); } var numberOfThreadsToFetch = pageSize; if (numberOfThreadsToFetch <= 0) { numberOfThreadsToFetch = 25; } var qf = new QueryFactory(); var q = qf.Create() .Select <AggregatedThreadRow>(ThreadGuiHelper.BuildQueryProjectionForAllThreadsWithStatsWithForumName(qf).ToArray()) .From(ThreadGuiHelper.BuildFromClauseForAllThreadsWithStats(qf) .InnerJoin(qf.Forum).On(ThreadFields.ForumID.Equal(ForumFields.ForumID))) .Where(ThreadFields.ForumID.In(accessableForums) .And(ThreadFields.ThreadID.In(qf.Create() .Select(MessageFields.ThreadID) .Where(MessageFields.PostedByUserID.Equal(participantUserId)))) .And(ThreadGuiHelper.CreateThreadFilter(forumsWithThreadsFromOthers, callingUserId))) .OrderBy(MessageFields.PostingDate.Source("LastMessage").Descending()); if (pageNumber <= 0) { // no paging // get the last numberOfThreadsToFetch, so specify a limit equal to the numberOfThreadsToFetch specified q.Limit(numberOfThreadsToFetch); } else { // use paging q.Page(pageNumber, numberOfThreadsToFetch); } using (var adapter = new DataAccessAdapter()) { return(await adapter.FetchQueryAsync(q).ConfigureAwait(false)); } }
/// <summary> /// Gets the threads and accompanying statistics info, in the supportqueues specified. Only the threads which are in the forums in the list of /// accessable forums are returned. /// </summary> /// <param name="accessableForums">A list of accessable forums IDs, which the user has permission to access.</param> /// <param name="supportQueueIds">The support queue IDs to obtain the threads info for.</param> /// <returns> /// a list of aggregated support queue contents rows, one per thread, or an empty list if no forums were accessible. /// </returns> public static async Task <List <AggregatedSupportQueueContentsRow> > GetAllThreadsInSpecifiedSupportQueuesAsync(List <int> accessableForums, int[] supportQueueIds) { // return null, if the user does not have a valid list of forums to access if (accessableForums == null || accessableForums.Count <= 0) { return(new List <AggregatedSupportQueueContentsRow>()); } var qf = new QueryFactory(); var projectionFields = new List <object>(ThreadGuiHelper.BuildQueryProjectionForAllThreadsWithStatsWithForumName(qf)); projectionFields.AddRange(new[] { SupportQueueThreadFields.QueueID, UserFields.NickName.Source("PlacedInQueueUser").As("PlacedInQueueByNickName"), SupportQueueThreadFields.PlacedInQueueByUserID, SupportQueueThreadFields.PlacedInQueueOn, UserFields.NickName.Source("ClaimedThreadUser").As("ClaimedByNickName"), SupportQueueThreadFields.ClaimedByUserID, SupportQueueThreadFields.ClaimedOn }); var q = qf.Create() .Select <AggregatedSupportQueueContentsRow>(projectionFields.ToArray()) .From(ThreadGuiHelper.BuildFromClauseForAllThreadsWithStats(qf) .InnerJoin(qf.Forum).On(ThreadFields.ForumID.Equal(ForumFields.ForumID)) .InnerJoin(qf.SupportQueueThread).On(ThreadFields.ThreadID.Equal(SupportQueueThreadFields.ThreadID)) .InnerJoin(qf.User.As("PlacedInQueueUser")) .On(SupportQueueThreadFields.PlacedInQueueByUserID.Equal(UserFields.UserID.Source("PlacedInQueueUser"))) .LeftJoin(qf.User.As("ClaimedThreadUser")) .On(SupportQueueThreadFields.ClaimedByUserID.Equal(UserFields.UserID.Source("ClaimedThreadUser")))) .Where(ThreadFields.ForumID.In(accessableForums.ToArray()).And(SupportQueueThreadFields.QueueID.In(supportQueueIds))) .OrderBy(SupportQueueThreadFields.QueueID.Ascending(), MessageFields.PostingDate.Source("LastMessage").Ascending()); using (var adapter = new DataAccessAdapter()) { return(await adapter.FetchQueryAsync(q).ConfigureAwait(false)); } }