/// <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.
/// </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 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>
/// 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);
}
}
