/// <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>
/// Creates the attachment filter. The filter filters on attachments with the specified approvalstate in the threads viewable by the calling user.
/// </summary>
/// <param name="accessableForums">The accessable forums.</param>
/// <param name="forumsWithApprovalRight">The forums with approval right.</param>
/// <param name="forumsWithThreadsFromOthers">The forums with threads from others.</param>
/// <param name="userID">The user ID.</param>
/// <param name="filter">The filter.</param>
/// <returns>ready to use predicate expression for fetch actions on attachments with the approval state specified in the threads
/// matching the forumID's.</returns>
private static PredicateExpression CreateAttachmentFilter(List <int> accessableForums, List <int> forumsWithApprovalRight,
List <int> forumsWithThreadsFromOthers, int userID, bool approvalState)
{
PredicateExpression filter = new PredicateExpression();
// specify the filter for the accessable forums. Do this by a fieldcomparerange predicate and filter on Thread.ForumID. As 'accessableForums' is a list
// the following statement will create a fieldcomparerange predicate for us.
if (accessableForums.Count == 1)
{
// optimization, specify the only value instead of the range, so we won't get a WHERE Field IN (@param) query which is slow on some
// databases, but we'll get a WHERE Field == @param
filter.Add(ThreadFields.ForumID == accessableForums[0]);
}
else
{
filter.Add(ThreadFields.ForumID == accessableForums);
}
// specify the filter for the forums with approval rights:
if (forumsWithApprovalRight.Count == 1)
{
// optimization, specify the only value instead of the range, so we won't get a WHERE Field IN (@param) query which is slow on some
// databases, but we'll get a WHERE Field == @param
filter.Add(ThreadFields.ForumID == forumsWithApprovalRight[0]);
}
else
{
filter.Add(ThreadFields.ForumID == forumsWithApprovalRight);
}
// Also filter on the threads viewable by the passed in userid, which is the caller of the method. If a forum isn't in the list of
// forumsWithThreadsFromOthers, only the sticky threads and the threads started by userid should be counted / taken into account.
filter.AddWithAnd(ThreadGuiHelper.CreateThreadFilter(forumsWithApprovalRight, userID));
// as last filter, we'll add a filter to only get the data for attachments which aren't approved yet.
filter.AddWithAnd(AttachmentFields.Approved == false);
return(filter);
}
/// <summary>
/// Gets the active threads with statistics.
/// </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 dataTable of Active threads with statistics
/// </returns>
public static DataTable GetActiveThreadsStatisticsAsDataTable(List <int> accessableForums, short hoursThreshold,
List <int> forumsWithThreadsFromOthers, int userID)
{
// 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()
.Select(ThreadFields.ThreadID.CountDistinct().As("AmountThreads"),
MessageFields.MessageID.Count().As("AmountPostings"),
ThreadFields.ThreadLastPostingDate.Max().As("LastPostingDate"))
.From(qf.Thread.InnerJoin(qf.Message).On(ThreadFields.ThreadID == MessageFields.ThreadID))
.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)));
var dao = new TypedListDAO();
return(dao.FetchAsDataTable(q));
//// create dyn. list and pull statistics using that list.
//ResultsetFields fields = new ResultsetFields(3);
//fields.DefineField(ThreadFields.ThreadID, 0, "AmountThreads", string.Empty, AggregateFunction.CountDistinct);
//fields.DefineField(MessageFields.MessageID, 1, "AmountPostings", string.Empty, AggregateFunction.Count);
//fields.DefineField(ThreadFields.ThreadLastPostingDate, 2, "LastPostingDate", string.Empty, AggregateFunction.Max);
//RelationCollection relations = new RelationCollection();
//relations.Add(ThreadEntity.Relations.MessageEntityUsingThreadID);
//PredicateExpression filter = new PredicateExpression();
//// only the forums the user has access to
//filter.Add(ThreadFields.ForumID == accessableForums.ToArray());
//// only the threads which are not closed
//filter.AddWithAnd(ThreadFields.IsClosed == false);
//// only the threads which are active (== not done)
//filter.AddWithAnd(ThreadFields.MarkedAsDone == false);
//// only threads which have been updated in the last Globals.HoursForActiveThreadsTreshold hours
//filter.AddWithAnd(ThreadFields.ThreadLastPostingDate >= DateTime.Now.AddHours((double)0 - hoursThreshold));
//// Also filter on the threads viewable by the passed in userid, which is the caller of the method. If a forum isn't in the list of
//// forumsWithThreadsFromOthers, only the sticky threads and the threads started by userid should be counted / taken into account.
//IPredicateExpression threadFilter = ThreadGuiHelper.CreateThreadFilter(forumsWithThreadsFromOthers, userID);
//filter.AddWithAnd(threadFilter);
//TypedListDAO dao = new TypedListDAO();
//DataTable toReturn = new DataTable();
//dao.GetMultiAsDataTable(fields, toReturn, 0, null, filter, relations, true, null, null, 0, 0);
//return toReturn;
}
/// <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 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>
/// Does the search using MS Full text search
/// </summary>
/// <param name="searchString">Search string.</param>
/// <param name="forumIDs">Forum Ids of forums to search into.</param>
/// <param name="orderFirstElement">Order first element setting.</param>
/// <param name="orderSecondElement">Order second element setting.</param>
/// <param name="forumsWithThreadsFromOthers">The forums with threads from others.</param>
/// <param name="userID">The userid of the calling user.</param>
/// <param name="targetToSearch">The target to search.</param>
/// <returns>
/// TypedList filled with threads matching the query.
/// </returns>
public static SearchResultTypedList DoSearch(string searchString, List <int> forumIDs, SearchResultsOrderSetting orderFirstElement,
SearchResultsOrderSetting orderSecondElement, List <int> forumsWithThreadsFromOthers, int userID, SearchTarget targetToSearch)
{
// the search utilizes full text search. It performs a CONTAINS upon the MessageText field of the Message entity.
string searchTerms = PrepareSearchTerms(searchString);
bool searchMessageText = (targetToSearch == SearchTarget.MessageText) || (targetToSearch == SearchTarget.MessageTextAndThreadSubject);
bool searchSubject = (targetToSearch == SearchTarget.ThreadSubject) || (targetToSearch == SearchTarget.MessageTextAndThreadSubject);
if (!(searchSubject || searchMessageText))
{
// no target specified, select message
searchMessageText = true;
}
PredicateExpression searchTermFilter = new PredicateExpression();
if (searchMessageText)
{
// Message contents filter
searchTermFilter.Add(new FieldCompareSetPredicate(ThreadFields.ThreadID, MessageFields.ThreadID,
SetOperator.In, new FieldFullTextSearchPredicate(MessageFields.MessageText, FullTextSearchOperator.Contains, searchTerms)));
}
if (searchSubject)
{
// Thread subject filter
if (searchMessageText)
{
searchTermFilter.AddWithOr(new FieldFullTextSearchPredicate(ThreadFields.Subject, FullTextSearchOperator.Contains, searchTerms));
}
else
{
searchTermFilter.Add(new FieldFullTextSearchPredicate(ThreadFields.Subject, FullTextSearchOperator.Contains, searchTerms));
}
}
IPredicateExpression mainFilter = searchTermFilter
.And(ForumFields.ForumID == forumIDs)
.And(ThreadGuiHelper.CreateThreadFilter(forumsWithThreadsFromOthers, userID));
ISortExpression sorter = new SortExpression();
// add first element
sorter.Add(CreateSearchSortClause(orderFirstElement));
if (orderSecondElement != orderFirstElement)
{
sorter.Add(CreateSearchSortClause(orderSecondElement));
}
SearchResultTypedList results = new SearchResultTypedList(false);
try
{
// get the data from the db.
results.Fill(500, sorter, false, mainFilter);
}
catch
{
// probably an error with the search words / user error. Swallow for now, which will result in an empty resultset.
}
return(results);
}
/// <summary>
/// Retrieves for all available sections all forums with all relevant statistical information. This information is stored per forum in an
/// AggregatedForumRow instance. The forum instances are indexed under their sectionid. Only forums which are vieable by the user are returned.
/// </summary>
/// <param name="availableSections">SectionCollection with all available sections</param>
/// <param name="accessableForums">List of accessable forums IDs.</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>
/// MultiValueHashtable with per key (sectionID) a set of AggregatedForumRow instance, one row per forum in the section. If a section contains no forums
/// displayable to the user it's not present in the returned hashtable.
/// </returns>
public static async Task <MultiValueHashtable <int, AggregatedForumRow> > GetAllAvailableForumsAggregatedData(EntityCollection <SectionEntity> availableSections,
List <int> accessableForums,
List <int> forumsWithThreadsFromOthers, int userId)
{
var toReturn = new MultiValueHashtable <int, AggregatedForumRow>();
// return an empty list, if the user does not have a valid list of forums to access
if (accessableForums == null || accessableForums.Count <= 0)
{
return(toReturn);
}
// fetch all forums with statistics in a dynamic list, while filtering on the list of accessable forums for this user.
// Create the filter separate of the query itself, as it's re-used multiple times.
var threadFilter = ThreadGuiHelper.CreateThreadFilter(forumsWithThreadsFromOthers, userId);
var qf = new QueryFactory();
var q = qf.Create()
.Select(() => new AggregatedForumRow()
{
ForumID = ForumFields.ForumID.ToValue <int>(),
ForumName = ForumFields.ForumName.ToValue <string>(),
ForumDescription = ForumFields.ForumDescription.ToValue <string>(),
ForumLastPostingDate = ForumFields.ForumLastPostingDate.ToValue <DateTime?>(),
// add a scalar query which retrieves the # of threads in the specific forum.
// this will result in the query:
// (
// SELECT COUNT(ThreadID) FROM Thread
// WHERE ForumID = Forum.ForumID AND threadfilter.
// ) As AmountThreads
AmountThreads = qf.Create()
.Select(ThreadFields.ThreadID.Count())
.CorrelatedOver(ThreadFields.ForumID.Equal(ForumFields.ForumID))
.Where(threadFilter)
.ToScalar().As("AmountThreads").ToValue <int>(),
// add a scalar query which retrieves the # of messages in the threads of this forum.
// this will result in the query:
// (
// SELECT COUNT(MessageID) FROM Message
// WHERE ThreadID IN
// (
// SELECT ThreadID FROM Thread WHERE ForumID = Forum.ForumID AND threadfilter
// )
// ) AS AmountMessages
AmountMessages = qf.Create()
.Select(MessageFields.MessageID.Count())
.Where(MessageFields.ThreadID.In(
qf.Create()
.Select(ThreadFields.ThreadID)
.CorrelatedOver(ThreadFields.ForumID.Equal(ForumFields.ForumID))
.Where(threadFilter)))
.ToScalar().As("AmountMessages").ToValue <int>(),
HasRSSFeed = ForumFields.HasRSSFeed.ToValue <bool>(),
SectionID = ForumFields.SectionID.ToValue <int>()
})
.Where(ForumFields.ForumID.In(accessableForums))
.OrderBy(ForumFields.OrderNo.Ascending(), ForumFields.ForumName.Ascending())
.CacheResultset(Globals.DefaultCacheDurationOfResultsets);
using (var adapter = new DataAccessAdapter())
{
var results = await adapter.FetchQueryAsync(q).ConfigureAwait(false);
foreach (var forum in results)
{
toReturn.Add(forum.SectionID, forum);
}
}
return(toReturn);
}
/// <summary>
/// Retrieves for all available sections all forums with all relevant statistical information. This information is stored per forum in a
/// DataView which is stored in the returned Dictionary, with the SectionID where the forum is located in as Key.
/// </summary>
/// <param name="availableSections">SectionCollection with all available sections</param>
/// <param name="accessableForums">List of accessable forums IDs.</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>
/// Dictionary with per key (sectionID) a dataview with forum information of all the forums in that section.
/// </returns>
/// <remarks>Uses dataviews because a dynamic list is executed to retrieve the information for the forums, which include aggregate info about
/// # of posts.</remarks>
public static Dictionary <int, DataView> GetAllAvailableForumsDataViews(SectionCollection availableSections, List <int> accessableForums,
List <int> forumsWithThreadsFromOthers, int userID)
{
Dictionary <int, DataView> toReturn = new Dictionary <int, DataView>();
// return an empty list, if the user does not have a valid list of forums to access
if (accessableForums == null || accessableForums.Count <= 0)
{
return(toReturn);
}
// fetch all forums with statistics in a dynamic list, while filtering on the list of accessable forums for this user.
// Create the filter separate of the query itself, as it's re-used multiple times.
IPredicateExpression threadFilter = ThreadGuiHelper.CreateThreadFilter(forumsWithThreadsFromOthers, userID);
var qf = new QueryFactory();
var q = qf.Create()
.Select(ForumFields.ForumID,
ForumFields.ForumName,
ForumFields.ForumDescription,
ForumFields.ForumLastPostingDate,
// add a scalar query which retrieves the # of threads in the specific forum.
// this will result in the query:
// (
// SELECT COUNT(ThreadID) FROM Thread
// WHERE ForumID = Forum.ForumID AND threadfilter.
// ) As AmountThreads
qf.Create()
.Select(ThreadFields.ThreadID.Count())
.CorrelatedOver(ThreadFields.ForumID == ForumFields.ForumID)
.Where(threadFilter)
.ToScalar().As("AmountThreads"),
// add a scalar query which retrieves the # of messages in the threads of this forum.
// this will result in the query:
// (
// SELECT COUNT(MessageID) FROM Message
// WHERE ThreadID IN
// (
// SELECT ThreadID FROM Thread WHERE ForumID = Forum.ForumID AND threadfilter
// )
// ) AS AmountMessages
qf.Create()
.Select(MessageFields.MessageID.Count())
.Where(MessageFields.ThreadID.In(
qf.Create()
.Select(ThreadFields.ThreadID)
.CorrelatedOver(ThreadFields.ForumID == ForumFields.ForumID)
.Where(threadFilter)))
.ToScalar().As("AmountMessages"),
ForumFields.HasRSSFeed,
ForumFields.SectionID)
.Where(ForumFields.ForumID == accessableForums)
.OrderBy(ForumFields.OrderNo.Ascending(), ForumFields.ForumName.Ascending());
var results = new TypedListDAO().FetchAsDataTable(q);
// Now per section create a new DataView in memory using in-memory filtering on the DataTable.
foreach (SectionEntity section in availableSections)
{
// Create view for current section and filter out rows we don't want. Do this with in-memory filtering of the dataview, so we don't
// have to execute multiple queries.
DataView forumsInSection = new DataView(results, "SectionID=" + section.SectionID, string.Empty, DataViewRowState.CurrentRows);
// add to sorted list with SectionID as key
toReturn.Add(section.SectionID, forumsInSection);
}
// return the dictionary
return(toReturn);
}
/// <summary>
/// Does the search using MS Full text search
/// </summary>
/// <param name="searchString">Search string.</param>
/// <param name="forumIds">Forum Ids of forums to search into.</param>
/// <param name="orderFirstElement">Order first element setting.</param>
/// <param name="orderSecondElement">Order second element setting.</param>
/// <param name="forumsWithThreadsFromOthers">The forums with threads from others.</param>
/// <param name="userId">The userid of the calling user.</param>
/// <param name="targetToSearch">The target to search.</param>
/// <returns>
/// TypedList filled with threads matching the query.
/// </returns>
public static async Task <List <SearchResultRow> > DoSearchAsync(string searchString, List <int> forumIds, SearchResultsOrderSetting orderFirstElement,
SearchResultsOrderSetting orderSecondElement, List <int> forumsWithThreadsFromOthers, int userId,
SearchTarget targetToSearch)
{
// the search utilizes full text search. It performs a CONTAINS upon the MessageText field of the Message entity.
var searchTerms = PrepareSearchTerms(searchString);
var searchMessageText = (targetToSearch == SearchTarget.MessageText) || (targetToSearch == SearchTarget.MessageTextAndThreadSubject);
var searchSubject = (targetToSearch == SearchTarget.ThreadSubject) || (targetToSearch == SearchTarget.MessageTextAndThreadSubject);
if (!(searchSubject || searchMessageText))
{
// no target specified, select message
searchMessageText = true;
}
var qf = new QueryFactory();
var searchTermFilter = new PredicateExpression();
if (searchMessageText)
{
// Message contents filter
searchTermFilter.Add(ThreadFields.ThreadID.In(qf.Create()
.Select(MessageFields.ThreadID)
.Where(new FieldFullTextSearchPredicate(MessageFields.MessageText, null, FullTextSearchOperator.Contains,
searchTerms))));
}
if (searchSubject)
{
// Thread subject filter
if (searchMessageText)
{
searchTermFilter.AddWithOr(new FieldFullTextSearchPredicate(ThreadFields.Subject, null, FullTextSearchOperator.Contains, searchTerms));
}
else
{
searchTermFilter.Add(new FieldFullTextSearchPredicate(ThreadFields.Subject, null, FullTextSearchOperator.Contains, searchTerms));
}
}
var q = qf.GetSearchResultTypedList()
.Where(searchTermFilter
.And(ForumFields.ForumID.In(forumIds))
.And(ThreadGuiHelper.CreateThreadFilter(forumsWithThreadsFromOthers, userId)))
.Limit(500)
.OrderBy(CreateSearchSortClause(orderFirstElement))
.Distinct();
if (orderSecondElement != orderFirstElement)
{
// simply call OrderBy again, it will append the sortclause to the existing one.
q.OrderBy(CreateSearchSortClause(orderSecondElement));
}
List <SearchResultRow> toReturn;
try
{
// get the data from the db.
using (var adapter = new DataAccessAdapter())
{
toReturn = await adapter.FetchQueryAsync(q).ConfigureAwait(false);
}
}
catch
{
// probably an error with the search words / user error. Swallow for now, which will result in an empty resultset.
toReturn = new List <SearchResultRow>();
}
return(toReturn);
}