public static DataTable GetUserUsingUserNamePasswordAsDataTable(string userName, string password)
{
var fields = new ResultsetFields(4);
fields.DefineField(UserFields.Id, 0);
fields.DefineField(UserFields.FullName, 1);
fields.DefineField(UserFields.IsAdmin, 2);
fields.DefineField(UserFields.IsInstructor, 3);
var filter = new PredicateExpression
{
(UserFields.Username == userName | UserFields.Email == userName),
UserFields.Password == password
};
var relations = new RelationCollection()
{
};
var toReturn = new DataTable();
var dao = new TypedListDAO();
dao.GetMultiAsDataTable(fields, toReturn, 0, null, filter, relations, true, null, null, 0, 0);
return(toReturn);
}
/// <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>
/// Will return the StartMessageNo for including it in the URL when redirecting to a page with messages in the given
/// thread. The page started with StartMessageNo will contain the message with ID messageID. Paging is done using the
/// maxAmountMessagesPerPage property in Application.
/// </summary>
/// <param name="threadID">ID of the thread to which the messages belong</param>
/// <param name="messageID"></param>
/// <returns></returns>
public static int GetStartAtMessageForGivenMessageAndThread(int threadID, int messageID, int maxAmountMessagesPerPage)
{
var qf = new QueryFactory();
var q = qf.Create()
.Select(MessageFields.MessageID)
.Where(MessageFields.ThreadID == threadID)
.OrderBy(MessageFields.PostingDate.Ascending())
.Distinct();
var dao = new TypedListDAO();
var dynamicList = dao.FetchAsDataTable(q);
int startAtMessage = 0;
int rowIndex = 0;
if (dynamicList.Rows.Count > 0)
{
// there are messages. Find the row with messageID. There can be only one row with this messageID
for (int i = 0; i < dynamicList.Rows.Count; i++)
{
if (((int)dynamicList.Rows[i]["MessageID"]) == messageID)
{
// found the row
rowIndex = i;
break;
}
}
}
startAtMessage = (rowIndex / maxAmountMessagesPerPage) * maxAmountMessagesPerPage;
// done
return(startAtMessage);
}
/// <summary>
/// Constructs a DataView from the datatable which contains all sections available, plus the # of forums in the section.
/// Sections and forums are sorted on OrderNo ascending, then on Name ascending.
/// </summary>
/// <param name="excludeEmptySections">If set to true, empty sections are ignored.</param>
/// <returns>
/// DataView with all the sections available, including statistics, directly bindable to webcontrols
/// </returns>
public static DataView GetAllSectionsWStatisticsAsDataView(bool excludeEmptySections)
{
// join with a derived table, which calculates the number of forums per section. This allows us to re-use the
// scalar values in multiple places (projection and where clause), without re-calculating the scalar per row.
var qf = new QueryFactory();
var q = qf.Create()
.Select(SectionFields.SectionID,
SectionFields.SectionName,
SectionFields.SectionDescription,
SectionFields.OrderNo,
qf.Field("ForumCountList", "ForumCount").As("AmountForums"))
.From(qf.Section.InnerJoin(
qf.Create()
.Select(ForumFields.ForumID.Count().As("ForumCount"),
ForumFields.SectionID)
.GroupBy(ForumFields.SectionID)
.As("ForumCountList"))
.On(ForumFields.SectionID.Source("ForumCountList") == SectionFields.SectionID))
.OrderBy(SectionFields.OrderNo.Ascending(), SectionFields.SectionName.Ascending());
if (excludeEmptySections)
{
q.AndWhere(qf.Field("ForumCountList", "ForumCount") != 0);
}
TypedListDAO dao = new TypedListDAO();
var results = dao.FetchAsDataTable(q);
return(results.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>
/// Checks the if thread is already bookmarked.
/// </summary>
/// <param name="userID">User ID.</param>
/// <param name="threadID">Thread ID.</param>
/// <returns>true if the thread is bookmarked</returns>
public static bool CheckIfThreadIsAlreadyBookmarked(int userID, int threadID)
{
var qf = new QueryFactory();
var q = qf.Create()
.Select(BookmarkFields.ThreadID)
.Where((BookmarkFields.ThreadID == threadID).And(BookmarkFields.UserID == userID));
var dao = new TypedListDAO();
return(dao.GetScalar <int?>(q, null) != null);
}
/// <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 all the banned users as a dataview. This is returned as a dataview because only the nicknames are required, so a dynamic list is
/// used to avoid unnecessary data fetching.
/// </summary>
/// <returns>dataview with the nicknames of the users which are banned on the useraccount: the IsBanned property is set for these users.</returns>
/// <remarks>This list of nicknames is cached in the application object so these users can be logged off by force.</remarks>
public static DataView GetAllBannedUserNicknamesAsDataView()
{
var qf = new QueryFactory();
var q = qf.Create()
.Select(UserFields.NickName)
.Where(UserFields.IsBanned == true);
var dao = new TypedListDAO();
var results = dao.FetchAsDataTable(q);
return(results.DefaultView);
}
/// <summary>
/// Gets the bookmark statistics for the user with id passed in.
/// </summary>
/// <param name="userID">User ID.</param>
/// <returns></returns>
public static DataTable GetBookmarkStatisticsAsDataTable(int userID)
{
var qf = new QueryFactory();
var q = qf.Create()
.Select(BookmarkFields.ThreadID.CountDistinct().As("AmountThreads"),
MessageFields.MessageID.Count().As("AmountPostings"),
ThreadFields.ThreadLastPostingDate.Max().As("LastPostingDate"))
.From(qf.Bookmark
.InnerJoin(qf.Thread).On(BookmarkFields.ThreadID == ThreadFields.ThreadID)
.InnerJoin(qf.Message).On(ThreadFields.ThreadID == MessageFields.ThreadID))
.Where(BookmarkFields.UserID == userID);
var dao = new TypedListDAO();
return(dao.FetchAsDataTable(q));
}
/// <summary>
/// Gets the attachments for the message with the messageid passed in. Attachments are sorted by AddedOn ascending.
/// The result is returned as a dynamic list, because we don't want the actual attachment data.
/// </summary>
/// <param name="messageID">The message ID.</param>
/// <returns>Dataview with 0 or more attachments related to the message with the id passed in. The rows don't contain the actual attachment data</returns>
public static DataView GetAttachmentsAsDataView(int messageID)
{
var q = new QueryFactory().Create()
.Select(AttachmentFields.AttachmentID,
AttachmentFields.Filename,
AttachmentFields.Approved,
AttachmentFields.Filesize,
AttachmentFields.AddedOn)
.Where(AttachmentFields.MessageID == messageID)
.OrderBy(AttachmentFields.AddedOn.Ascending());
var attachments = new TypedListDAO().FetchAsDataTable(q);
return(attachments.DefaultView);
}
/// <summary>
/// Gets the total number of threads in support queues. Only the count of 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>
/// <returns>total number of threads in support queues</returns>
public static int GetTotalNumberOfThreadsInSupportQueues(List <int> accessableForums)
{
// return 0, 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(SupportQueueThreadFields.ThreadID.Count().As("NumberOfThreadsInQueues"))
.From(qf.SupportQueueThread.InnerJoin(qf.Thread).On(SupportQueueThreadFields.ThreadID == ThreadFields.ThreadID))
.Where(ThreadFields.ForumID == accessableForums);
var dao = new TypedListDAO();
return(dao.GetScalar <int>(q, null));
}
/// <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>
/// Checks if the message with the ID specified is first message in thread with id specified.
/// </summary>
/// <param name="threadID">The thread ID.</param>
/// <param name="messageID">The message ID.</param>
/// <returns>true if message is first message in thread, false otherwise</returns>
public static bool CheckIfMessageIsFirstInThread(int threadID, int messageID)
{
// use a scalar query, which obtains the first MessageID in a given thread. We sort on posting date ascending, and simply read
// the first messageid. If that's not available or not equal to messageID, the messageID isn't the first post in the thread, otherwise it is.
var qf = new QueryFactory();
var q = qf.Create()
.Select(MessageFields.MessageID)
.Where(MessageFields.ThreadID == threadID)
.OrderBy(MessageFields.PostingDate.Ascending())
.Limit(1);
var dao = new TypedListDAO();
var firstMessageId = dao.GetScalar <int?>(q, null);
if (firstMessageId.HasValue)
{
return(firstMessageId.Value == messageID);
}
// not found.
return(false);
}
/// <summary>
/// Gets all attachments which have to be approved as data view, filtered on the two passed in list of forum id's.
/// It doesn't return the file contents for each attachment, it just returns the other data of each attachment, as well as some other related data.
/// </summary>
/// <param name="accessableForums">The accessable forums by the user calling.</param>
/// <param name="forumsWithApprovalRight">The forums the calling user has attachment approval rights.</param>
/// <param name="forumsWithThreadsFromOthers">The forums the calling user can view normal threads from others.</param>
/// <param name="userID">The user ID of the calling user.</param>
/// <returns>DataView with the data requested.</returns>
public static DataView GetAllAttachmentsToApproveAsDataView(List <int> accessableForums, List <int> forumsWithApprovalRight,
List <int> forumsWithThreadsFromOthers, int userID)
{
if ((accessableForums == null) || (accessableForums.Count <= 0))
{
// doesn't have access to any forum, return
return(null);
}
if ((forumsWithApprovalRight == null) || (forumsWithApprovalRight.Count <= 0))
{
// doesn't have a forum with attachment approval right
return(null);
}
// we'll use a dynamic list for the data to return. This is similar to GetAttachmentsAsDataView, however now we'll add more data + we'll
// filter on more things. The data we'll add is the ForumID of the thread containing the messagee the attachment is attached to, as well as
// the userid of the poster of the message the attachment is attached to, and the threadid of the thread the message is in.
// We've to filter the list of attachments based on the forums accessable by the calling user, the list of forums the calling user has approval
// rights on and by the forums on which the user can see other user's threads. We'll create a predicate expression for this, and will add
// for each of these filters a separate predicate to this predicate expression and specify AND, so they all have to be true
var qf = new QueryFactory();
var q = qf.Create()
.Select(AttachmentFields.AttachmentID,
AttachmentFields.MessageID,
AttachmentFields.Filename,
AttachmentFields.Approved,
AttachmentFields.Filesize,
AttachmentFields.AddedOn,
MessageFields.PostedByUserID,
ThreadFields.ForumID,
ThreadFields.ThreadID)
.From(qf.Attachment
.InnerJoin(qf.Message).On(AttachmentFields.MessageID == MessageFields.MessageID)
.InnerJoin(qf.Thread).On(MessageFields.ThreadID == ThreadFields.ThreadID))
.Where(CreateAttachmentFilter(accessableForums, forumsWithApprovalRight, forumsWithThreadsFromOthers, userID, false))
.OrderBy(AttachmentFields.AddedOn.Ascending());
var attachments = new TypedListDAO().FetchAsDataTable(q);
return(attachments.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>
/// Constructs a dataview with all the roles available, complete with statistics (#users, if the role is used as anonymous role or default user role)
/// </summary>
/// <returns>DataView with all the Roles available, directly bindable to webcontrols</returns>
public static DataView GetAllRolesWithStatisticsAsDataView()
{
// create dynamic list, with all fields of Role and 3 extra fields: one field for the # of users in the role, one field which
// signals if the role is the defaultnewuserrole and one field which signals if the role is the anonymous role. The # of users field is
// used in the query, the other two fields are added later for efficiency.
var qf = new QueryFactory();
var q = qf.Create()
.Select(RoleFields.RoleID,
RoleFields.RoleDescription,
// now add the # of users subquery to the resultset. This will result in the query:
// (
// SELECT COUNT(UserID)
// FROM RoleUser
// WHERE RoleUser.RoleID = Role.RoleID
// ) AS AmountUsersInRole
qf.Create()
.Select(RoleUserFields.UserID.Count())
.CorrelatedOver(RoleUserFields.RoleID == RoleFields.RoleID)
.ToScalar()
.As("AmountUsersInRole"))
.OrderBy(RoleFields.RoleDescription.Ascending());
var dao = new TypedListDAO();
var results = dao.FetchAsDataTable(q);
// we now fetch the system data which contains the two role id's we've to check with in the results to return.
SystemDataEntity systemData = SystemGuiHelper.GetSystemSettings();
// now add 2 columns to the datatable, booleans, which are used to store the flags for IsDefaultNewUserRole and IsAnonymousRole, so the complete
// set of data can be processed in a list form.
results.Columns.Add(new DataColumn("IsDefaultNewUserRole", typeof(bool)));
results.Columns.Add(new DataColumn("IsAnonymousRole", typeof(bool)));
foreach (DataRow row in results.Rows)
{
row["IsDefaultNewUserRole"] = ((int)row["RoleID"] == systemData.DefaultRoleNewUser);
row["IsAnonymousRole"] = ((int)row["RoleID"] == systemData.AnonymousRole);
}
// done, return the dataview of this datatable
return(results.DefaultView);
}
protected override void OnInit(EventArgs e)
{
base.OnInit(e);
RelationCollection locationToMessage = new RelationCollection
{
new EntityRelation(LocationFields.LocationId, DeviceFields.LocationId, RelationType.OneToOne),
new EntityRelation(DeviceFields.DeviceId, DeviceMessageFields.DeviceId, RelationType.OneToOne),
new EntityRelation(DeviceMessageFields.MessageId, MessageFields.MessageId, RelationType.OneToOne)
};
ResultsetFields fields = new ResultsetFields(7);
fields.DefineField(MessageFields.MessageId, 0, "MessageId");
fields.DefineField(MessageFields.Title, 1, "Title");
fields.DefineField(MessageFields.Body, 2, "Body");
fields.DefineField(MessageFields.CreateTime, 3, "CreateTime");
fields.DefineField(MessageFields.StartTime, 4, "StartTime");
fields.DefineField(MessageFields.EndTime, 5, "EndTime");
fields.DefineField(LocationFields.Name, 6, "LocationName");
DataTable dynamicList = new DataTable();
TypedListDAO dao = new TypedListDAO();
if (Roles.IsUserInRole("Service Administrator"))
{
dao.GetMultiAsDataTable(fields, dynamicList, 0, null, null, locationToMessage, true, null, null, 0, 0);
}
else if (Roles.IsUserInRole("Organization Administrator"))
{
dao.GetMultiAsDataTable(fields, dynamicList, 0, null,
new PredicateExpression(LocationFields.OrganizationId == Membership.GetUser().GetUserId().OrganizationId),
locationToMessage, true, null, null, 0, 0);
}
//else
// messages.GetMulti(EcMessageBankFields.CustomerLocationId == user.LocationId);
if (dynamicList.Rows.Count > 0)
{
bool alternate = false;
foreach (DataRow message in dynamicList.Rows)
{
TableRow messageHeader = new TableRow()
{
CssClass = alternate ? "alternate" : ""
};
messageHeader.Controls.Add(new TableCell()
{
Text = message["CreateTime"].ToString()
});
messageHeader.Controls.Add(new TableCell()
{
Text = message["LocationName"].ToString()
});
MainTable.Controls.Add(messageHeader);
TableRow messageBody = new TableRow()
{
CssClass = alternate ? "alternate" : ""
};
messageBody.Controls.Add(new TableCell()
{
ColumnSpan = 2,
Text =
"<strong style='float:left; display:inline-block; padding-right:5px;'>" +
message["Title"] + "</strong>" + message["Body"]
});
MainTable.Controls.Add(messageBody);
alternate = !alternate;
}
}
else
{
TableRow empty_list = new TableRow();
empty_list.Controls.Add(new TableCell()
{
Text = "No messages to display."
});
MainTable.Controls.Add(empty_list);
}
}
/// <summary>
/// Calculates the statistics for the Northwind database and shows them in the form, illustrating the
/// direct database power LLBLGen Pro offers you through scalar functions, aggregates, group by and expressions.
/// </summary>
/// <remarks>No error checking is applied to this routine, it's for illustrating the framework functionality.</remarks>
private void CalculateStatistics()
{
// get amount customers
CustomerCollection customers = new CustomerCollection();
int amountCustomers = customers.GetDbCount();
_amountCustomersTextBox.Text = amountCustomers.ToString();
// get all order prices
ResultsetFields orderPricesFields = new ResultsetFields(2);
orderPricesFields.DefineField(OrderDetailFields.OrderId, 0, "OrderId");
orderPricesFields.DefineField(OrderDetailFields.ProductId, 1, "OrderPrice", "", AggregateFunction.Sum);
orderPricesFields[1].ExpressionToApply = (OrderDetailFields.Quantity * OrderDetailFields.UnitPrice);
IGroupByCollection groupBy = new GroupByCollection();
groupBy.Add(orderPricesFields[0]);
// fetch in a datatable, orderid + the order price.
DataTable orderPrices = new DataTable();
TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable(orderPricesFields, orderPrices, 0, null, null, null, true, groupBy, null, 0, 0);
// calculate average order price and which customer has the most expensive order
decimal averageOrderPrice = 0.0M;
decimal highestOrderPrice = 0.0M;
int orderIdWithHighestPrice = 0;
for (int i = 0; i < orderPrices.Rows.Count; i++)
{
decimal currentOrderPrice = (decimal)orderPrices.Rows[i]["OrderPrice"];
if (currentOrderPrice > highestOrderPrice)
{
highestOrderPrice = currentOrderPrice;
orderIdWithHighestPrice = (int)orderPrices.Rows[i]["OrderId"];
}
averageOrderPrice += currentOrderPrice;
}
averageOrderPrice = averageOrderPrice / orderPrices.Rows.Count;
_highestOrderPriceTextBox.Text = highestOrderPrice.ToString("C");
_averageOrderPriceTextBox.Text = averageOrderPrice.ToString("C");
// get order with highest price. Pull customer also in 1 go
IPrefetchPath prefetchPathCustomer = new PrefetchPath((int)EntityType.OrderEntity);
prefetchPathCustomer.Add(OrderEntity.PrefetchPathCustomer);
OrderEntity highestOrder = new OrderEntity();
highestOrder.FetchUsingPK(orderIdWithHighestPrice, prefetchPathCustomer);
_customerWithHighestOrder = highestOrder.Customer; // already loaded through prefetch path.
_highestOrderCustomerTextBox.Text = _customerWithHighestOrder.CompanyName;
// get customer with most orders.
ResultsetFields orderCustomerFields = new ResultsetFields(2);
orderCustomerFields.DefineField(OrderFields.CustomerId, 0, "CustomerID");
orderCustomerFields.DefineField(OrderFields.OrderId, 1, "AmountOrders", "", AggregateFunction.Count);
groupBy = new GroupByCollection();
groupBy.Add(orderCustomerFields[0]);
SortExpression sorter = new SortExpression();
sorter.Add(new SortClause(orderCustomerFields[1], SortOperator.Descending));
// now fetch the list, just 1 row, ordered descending on amount
DataTable orderCustomer = new DataTable();
dao.GetMultiAsDataTable(orderCustomerFields, orderCustomer, 1, sorter, null, null, true, groupBy, null, 0, 0);
_mostOrdersPerCustomerTextBox.Text = orderCustomer.Rows[0]["AmountOrders"].ToString();
// we'll assume the data was there, so there is a row.
_customerWithMostOrders = new CustomerEntity(orderCustomer.Rows[0]["CustomerID"].ToString());
_mostOrdersCustomerTextBox.Text = _customerWithMostOrders.CompanyName;
}
/// <summary>
/// Re-parses all messages from start date till now or when amountToIndex is reached. This routine will read messagetext for a message,
/// parse it, and update the MessageTextAsXML field with the parse result.
/// </summary>
/// <param name="amountToParse">Amount to parse.</param>
/// <param name="startDate">Start date.</param>
/// <param name="reGenerateHTML">If true, the HTML is also re-generated and saved.</param>
/// <returns>the amount of messages re-parsed</returns>
public static int ReParseMessages(int amountToParse, DateTime startDate, bool reGenerateHTML, ParserData parserData)
{
// index is blocks of 100 messages.
var qf = new QueryFactory();
var q = qf.Create()
.Select(MessageFields.MessageID, MessageFields.MessageText)
.Where(MessageFields.PostingDate >= new DateTime(startDate.Year, startDate.Month, startDate.Day, 0, 0, 0, 0));
if(amountToParse <= 0)
{
// If we don't have a specific amount of messages to parse, then parse all messages posted till Now.
q.AndWhere(MessageFields.PostingDate <= DateTime.Now);
}
TypedListDAO dao = new TypedListDAO();
bool parsingFinished = false;
int amountProcessed = 0;
int pageSize = 100;
int pageNo = 1;
while(!parsingFinished)
{
q.Page(pageNo, pageSize);
DataTable messagesToParse = dao.FetchAsDataTable(q);
parsingFinished = (messagesToParse.Rows.Count <= 0);
if(!parsingFinished)
{
foreach(DataRow row in messagesToParse.Rows)
{
MessageEntity directUpdater = new MessageEntity();
directUpdater.IsNew = false;
string messageXML = string.Empty;
string messageHTML = string.Empty;
TextParser.ReParseMessage((string)row["MessageText"], reGenerateHTML, parserData, out messageXML, out messageHTML);
// use the directupdater entity to create an update query without fetching the entity first.
directUpdater.Fields[(int)MessageFieldIndex.MessageID].ForcedCurrentValueWrite((int)row["MessageID"]);
directUpdater.MessageTextAsXml = messageXML;
if(reGenerateHTML)
{
directUpdater.MessageTextAsHTML=messageHTML;
}
directUpdater.Fields.IsDirty=true;
// no transactional update.
directUpdater.Save();
}
amountProcessed += messagesToParse.Rows.Count;
pageNo++;
if(amountToParse > 0)
{
parsingFinished = (amountToParse <= amountProcessed);
}
}
}
return amountProcessed;
}
/// <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);
}
}
/// <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);
}
