Пример #1
0
 /// <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;
 }
Пример #2
0
        /// <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;
            }
        }
Пример #3
0
 /// <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);
 }
Пример #4
0
        /// <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;
        }
Пример #5
0
 /// <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;
 }
Пример #6
0
 /// <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;
 }
Пример #7
0
        /// <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;
        }
Пример #8
0
        /// <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;
		}
Пример #9
0
		/// <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;
		}
Пример #10
0
        /// <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;
        }
Пример #11
0
        /// <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;
        }