/// <summary> /// Gets the threads and accompanying statistics info, in the supportqueue 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="supportQueueID">The ID of the support queue to retrieve the threads for.</param> /// <returns>a dataView of Active threads</returns> public static DataView GetAllThreadsInSupportQueueAsDataView(List <int> accessableForums, int supportQueueID) { // return null, if the user does not have a valid list of forums to access if (accessableForums == null || accessableForums.Count <= 0) { return(null); } var qf = new QueryFactory(); var q = qf.Create(); var projectionFields = new List <object>(ThreadGuiHelper.BuildQueryProjectionForAllThreadsWithStats(qf)); projectionFields.AddRange(new[] { ForumFields.ForumName, UserFields.NickName.Source("PlacedInQueueUser").As("NickNamePlacedInQueue"), SupportQueueThreadFields.PlacedInQueueByUserID, SupportQueueThreadFields.PlacedInQueueOn, UserFields.NickName.Source("ClaimedThreadUser").As("NickNameClaimedThread"), SupportQueueThreadFields.ClaimedByUserID, SupportQueueThreadFields.ClaimedOn }); q.Select(projectionFields.ToArray()); q.From(ThreadGuiHelper.BuildFromClauseForAllThreadsWithStats(qf) .InnerJoin(qf.Forum).On(ThreadFields.ForumID == ForumFields.ForumID) .InnerJoin(qf.SupportQueueThread).On(ThreadFields.ThreadID == SupportQueueThreadFields.ThreadID) .InnerJoin(qf.User.As("PlacedInQueueUser")) .On(SupportQueueThreadFields.PlacedInQueueByUserID == UserFields.UserID.Source("PlacedInQueueUser")) .LeftJoin(qf.User.As("ClaimedThreadUser")) .On(SupportQueueThreadFields.ClaimedByUserID == UserFields.UserID.Source("ClaimedThreadUser"))); q.Where((ThreadFields.ForumID == accessableForums).And(SupportQueueThreadFields.QueueID == supportQueueID)); q.OrderBy(ThreadFields.ThreadLastPostingDate.Ascending()); TypedListDAO dao = new TypedListDAO(); var threadsInQueue = dao.FetchAsDataTable(q); return(threadsInQueue.DefaultView); }
/// <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 active threads. /// </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="forumsWithOnlyOwnThreads">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 dataView of Active threads</returns> public static DataView GetActiveThreadsAsDataView(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(new List <object>(ThreadGuiHelper.BuildQueryProjectionForAllThreadsWithStats(qf)) { ForumFields.ForumName } .ToArray()) .From(ThreadGuiHelper.BuildFromClauseForAllThreadsWithStats(qf) .InnerJoin(qf.Forum).On(ThreadFields.ForumID == ForumFields.ForumID)) .Where((ThreadFields.ForumID == accessableForums) .And(ThreadFields.IsClosed == false) .And(ThreadFields.MarkedAsDone == false) .And(ThreadFields.ThreadLastPostingDate >= DateTime.Now.AddHours((double)0 - hoursThreshold)) .And(ThreadGuiHelper.CreateThreadFilter(forumsWithThreadsFromOthers, userID))) .OrderBy(ThreadFields.ThreadLastPostingDate.Ascending()); var dao = new TypedListDAO(); var activeThreads = dao.FetchAsDataTable(q); return(activeThreads.DefaultView); }
/// <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 bookmarks with statistics for the user specified. /// </summary> /// <param name="userID">User ID.</param> /// <returns></returns> public static DataView GetBookmarksAsDataView(int userID) { var qf = new QueryFactory(); var q = qf.Create() .Select(new List <object>(ThreadGuiHelper.BuildQueryProjectionForAllThreadsWithStats(qf)) { ForumFields.ForumName, ForumFields.SectionID }.ToArray()) .From(ThreadGuiHelper.BuildFromClauseForAllThreadsWithStats(qf) .InnerJoin(qf.Forum).On(ThreadFields.ForumID == ForumFields.ForumID)) .Where(ThreadFields.ThreadID.In(qf.Create().Select(BookmarkFields.ThreadID).Where(BookmarkFields.UserID == userID))) .OrderBy(ThreadFields.ThreadLastPostingDate.Descending()); var dao = new TypedListDAO(); var bookmarkedThreads = dao.FetchAsDataTable(q); return(bookmarkedThreads.DefaultView); }
/// <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 dataView of the threads requested</returns> public static DataView GetLastThreadsForUserAsDataView(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); } int numberOfThreadsToFetch = pageSize; if (numberOfThreadsToFetch <= 0) { numberOfThreadsToFetch = 25; } var qf = new QueryFactory(); var q = qf.Create() .Select(ThreadGuiHelper.BuildQueryProjectionForAllThreadsWithStats(qf)) .From(ThreadGuiHelper.BuildFromClauseForAllThreadsWithStats(qf)) .Where((ThreadFields.ForumID == accessableForums) .And(ThreadFields.ThreadID.In(qf.Create() .Select(MessageFields.ThreadID) .Where(MessageFields.PostedByUserID == participantUserID))) .And(ThreadGuiHelper.CreateThreadFilter(forumsWithThreadsFromOthers, callingUserID))) .OrderBy(ThreadFields.ThreadLastPostingDate.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); } var dao = new TypedListDAO(); var lastThreads = dao.FetchAsDataTable(q); return(lastThreads.DefaultView); }
/// <summary> /// Gets the row count for the set of 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. /// </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> /// <returns>a dataView of the threads requested</returns> public static int GetRowCountLastThreadsForUserAsDataView(List <int> accessableForums, int participantUserID, List <int> forumsWithThreadsFromOthers, int callingUserID) { // return null, if the user does not have a valid list of forums to access if (accessableForums == null || accessableForums.Count <= 0) { return(0); } var qf = new QueryFactory(); var q = qf.Create() .Select(ThreadGuiHelper.BuildQueryProjectionForAllThreadsWithStats(qf)) .From(ThreadGuiHelper.BuildFromClauseForAllThreadsWithStats(qf)) .Where((ThreadFields.ForumID == accessableForums) .And(ThreadFields.ThreadID.In(qf.Create() .Select(MessageFields.ThreadID) .Where(MessageFields.PostedByUserID == participantUserID))) .And(ThreadGuiHelper.CreateThreadFilter(forumsWithThreadsFromOthers, callingUserID))); var dao = new TypedListDAO(); return(dao.GetScalar <int>(qf.Create().Select(Functions.CountRow()).From(q), null)); }
/// <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)); } }
/// <summary> /// Returns a list with aggregated data objects, one per thread, for the requested forum and page /// </summary> /// <param name="forumId">ID of Forum for which the Threadlist is required</param> /// <param name="pageNumber">The page number to fetch, which is used to fetch non-sticky posts</param> /// <param name="pageSize">The number of rows to fetch for the page. </param> /// <param name="canViewNormalThreadsStartedByOthers">If set to true, the user calling the method has the right to view threads started by others. /// Otherwise only the threads started by the user calling the method are returned.</param> /// <param name="userId">The userid of the user calling the method.</param> /// <returns>List with all the thread info, aggregated. Sticky threads are sorted to the top.</returns> public static async Task <List <AggregatedThreadRow> > GetAllThreadsInForumAggregatedDataAsync(int forumId, int pageNumber, int pageSize, bool canViewNormalThreadsStartedByOthers, int userId) { // create a query which always fetches the sticky threads, and besides those the threads which are visible to the user. // then sort the sticky threads at the top and page through the resultset. var qf = new QueryFactory(); var offsetStart = pageSize * (pageNumber - 1); if (offsetStart < 0) { offsetStart = 0; } var q = qf.Create() .From(ThreadGuiHelper.BuildFromClauseForAllThreadsWithStats(qf)) .Where(ThreadFields.ForumID.Equal(forumId)) .OrderBy(ThreadFields.IsSticky.Descending(), MessageFields.PostingDate.Source("LastMessage").Descending()) .Select <AggregatedThreadRow>(ThreadGuiHelper.BuildQueryProjectionForAllThreadsWithStats(qf).ToArray()) .Offset(offsetStart) // skip the pages we don't need. .Limit(pageSize + 1); // fetch 1 row extra, which we can use to determine whether there are more pages left. // if the user can't view threads started by others, filter out threads started by users different from userID. Otherwise just filter on forumid and stickyness. if (!canViewNormalThreadsStartedByOthers) { // caller can't view threads started by others: add a filter so that threads not started by calling user aren't enlisted. // however sticky threads are always returned so the filter contains a check so the limit is only applied on threads which aren't sticky // add a filter for sticky threads, add it with 'OR', so sticky threads are always accepted. The whole expression is and-ed to the already existing // expression q.AndWhere((ThreadFields.StartedByUserID.Equal(userId)).Or(ThreadFields.IsSticky.Equal(true))); } using (var adapter = new DataAccessAdapter()) { var toReturn = await adapter.FetchQueryAsync(q).ConfigureAwait(false); return(toReturn); } }
/// <summary> /// Returns a DataView object that contains a complete list of threads list for /// the requested forum and required date & time interval /// </summary> /// <param name="forumID">ID of Forum for which the Threadlist is required</param> /// <param name="limiter">Limits the Threadlist to between now and; last 48 Hrs, Last Week, Last Month, Last Year</param> /// <param name="minNumberOfThreadsToFetch">The minimum number of threads to fetch if there are less threads available in the limiter interval</param> /// <param name="minNumberOfNonStickyVisibleThreads">The minimum number of non-sticky visible threads to show. If the # of threads is lower than /// this number (due to the limiter value), the minNumberOfThreadsToFetch are fetched</param> /// <param name="canViewNormalThreadsStartedByOthers">If set to true, the user calling the method has the right to view threads started by others. /// Otherwise only the threads started by the user calling the method are returned.</param> /// <param name="userID">The userid of the user calling the method.</param> /// <returns>DataView with all the threads</returns> public static DataView GetAllThreadsInForumAsDataView(int forumID, ThreadListInterval limiter, short minNumberOfThreadsToFetch, short minNumberOfNonStickyVisibleThreads, bool canViewNormalThreadsStartedByOthers, int userID) { DateTime limiterDate; // convert the limiter enum to a datetime which we can use in the filters on the thread data, where we'll use the limiter date // as a filter for the last posting date of a post in a given thread. switch (limiter) { case ThreadListInterval.Last24Hours: limiterDate = DateTime.Today.AddHours(-24); break; case ThreadListInterval.Last48Hours: limiterDate = DateTime.Today.AddHours(-48); break; case ThreadListInterval.LastWeek: limiterDate = DateTime.Today.AddDays(-7); break; case ThreadListInterval.LastMonth: limiterDate = DateTime.Today.AddMonths(-1); break; case ThreadListInterval.LastYear: limiterDate = DateTime.Today.AddYears(-1); break; default: limiterDate = DateTime.Today.AddHours(-48); break; } var qf = new QueryFactory(); var q = qf.Create(); q.Select(ThreadGuiHelper.BuildQueryProjectionForAllThreadsWithStats(qf)); q.From(ThreadGuiHelper.BuildFromClauseForAllThreadsWithStats(qf)); q.Where(((ThreadFields.IsSticky == true).Or(ThreadFields.ThreadLastPostingDate >= limiterDate)).And(ThreadFields.ForumID == forumID)); // if the user can't view threads started by others, filter out threads started by users different from userID if (!canViewNormalThreadsStartedByOthers) { // caller can't view threads started by others: add a filter so that threads not started by calling user aren't enlisted. // however sticky threads are always returned so the filter contains a check so the limit is only applied on threads which aren't sticky // add a filter for sticky threads, add it with 'OR', so sticky threads are always accepted q.AndWhere((ThreadFields.StartedByUserID == userID).Or(ThreadFields.IsSticky == true)); } q.OrderBy(ThreadFields.IsSticky.Descending(), ThreadFields.IsClosed.Ascending(), ThreadFields.ThreadLastPostingDate.Descending()); var dao = new TypedListDAO(); var threads = dao.FetchAsDataTable(q); // count # non-sticky threads. If it's below a given minimum, refetch everything, but now don't fetch on date filtered but at least the // set minimum. Do this ONLY if the user can view other user's threads. If that's NOT the case, don't refetch anything. DataView stickyThreads = new DataView(threads, ThreadFieldIndex.IsSticky.ToString() + "=false", "", DataViewRowState.CurrentRows); if ((stickyThreads.Count < minNumberOfNonStickyVisibleThreads) && canViewNormalThreadsStartedByOthers) { // not enough threads available, fetch again, // first fetch the sticky threads. q = qf.Create(); q.Select(ThreadGuiHelper.BuildQueryProjectionForAllThreadsWithStats(qf)); q.From(ThreadGuiHelper.BuildFromClauseForAllThreadsWithStats(qf)); q.Where((ThreadFields.IsSticky == true).And(ThreadFields.ForumID == forumID)); q.OrderBy(ThreadFields.ThreadLastPostingDate.Descending()); threads = dao.FetchAsDataTable(q); // then fetch the rest. Fetch it into the same datatable object to append the rows to the already fetched sticky threads (if any) q = qf.Create(); q.Select(ThreadGuiHelper.BuildQueryProjectionForAllThreadsWithStats(qf)); q.From(ThreadGuiHelper.BuildFromClauseForAllThreadsWithStats(qf)); q.Where((ThreadFields.IsSticky == false).And(ThreadFields.ForumID == forumID)); q.Limit(minNumberOfThreadsToFetch); q.OrderBy(ThreadFields.ThreadLastPostingDate.Descending()); dao.FetchAsDataTable(q, threads); // sort closed threads to the bottom. Do this in-memory as it's a sort operation after projection. Doing it on the server would mean // a sort operation before projection. return(new DataView(threads, string.Empty, ThreadFieldIndex.IsClosed.ToString() + " ASC", DataViewRowState.CurrentRows)); } else { return(threads.DefaultView); } }