forumthreadquerybuilder.cs

community server 源码

//------------------------------------------------------------------------------
// <copyright company="Telligent Systems">
//     Copyright (c) Telligent Systems Corporation.  All rights reserved.
// </copyright> 
//------------------------------------------------------------------------------

using CommunityServer.Discussions.Components;
using CommunityServer.Components;
using System.Collections;
using System.Text;

namespace CommunityServer.SqlDataProvider
{
	/// <summary>
	/// Impliments Application Sepecific components of the BaseThreadQueryBuilder.BuildQuery method
	/// </summary>
	public class ForumThreadQueryBuilder : BaseThreadQueryBuilder
	{
		private ForumThreadQuery query;
		public ForumThreadQueryBuilder(ThreadQuery query, string databaseOwner): base(query, databaseOwner)
		{
			this.query = (ForumThreadQuery)query;
		}

		#region QueryBulder Overrides

		/// <summary>
		/// ORDER BY, HAVING and GROUP BY objects appended after the WHERE clause
		/// </summary>
        protected override void ApplySort()
        {
			string order = "desc";
			if(query.SortOrder == SortOrder.Ascending)
				order = "asc";

			string groupBy = string.Empty;
			string orderBy = string.Empty;

            switch (query.SortBy) 
			{
				case SortThreadsBy.ThreadAuthor:
					orderBy = " order by P.PostAuthor";
					groupBy = "P.PostAuthor";
					break;
				case SortThreadsBy.TotalReplies:
					orderBy = " order by T.TotalReplies";
					groupBy = "T.TotalReplies";
					break;
				case SortThreadsBy.TotalRatings:
					orderBy = " order by case when T.TotalRatings > 0 then (convert(decimal,T.RatingSum) / convert(decimal,T.TotalRatings)) else 0 end" ;
					groupBy = "T.TotalRatings, T.RatingSum";
					break;
				case SortThreadsBy.Subject:
					orderBy = " order by P.Subject";
					groupBy = "P.Subject";
					break;
				case SortThreadsBy.TotalViews:
					orderBy = " order by T.TotalViews";
					groupBy = "T.TotalViews";
					break;
				default:
				case SortThreadsBy.LastPost:
					orderBy = " order by P.PostDate";
					groupBy = "P.PostDate";
					break;
			}

			// Add group by and having if necessary, then order by
			if(query.UncategorizedOnly)
			{
				sb.Append(" group by P.PostID, " + groupBy);
				sb.Append(" having count(PC.CategoryID) = 0");
			}
			sb.Append(orderBy + " " + order);


        }

		/// <summary>
		/// WHERE clause element to filter by date ranges
		/// </summary>
		protected override void ApplyDateFilter()
		{
			//Do we care if the post has been publshied
			//if(query.PublishedFilter != BlogPostPublishedFilter.All)
			//{
			//	sb.AppendFormat(" and P.IsApproved = {0} and P.PostDate <= getdate() ", (int)query.PublishedFilter);
			//}

			//Do we care about dates?
//			if(query.BlogThreadType != BlogThreadType.Recent && query.BlogThreadType != BlogThreadType.Category)
//			{
//				DateTime safeDate = SQLHelper.GetSafeSqlDateTime(query.DateFilter);
//                if(query.BlogThreadType == BlogThreadType.Day) {
//                    sb.AppendFormat(" and Day(P.UserTime) = {0} and Month(P.UserTime) = {1} and Year(P.UserTime) = {2} ",safeDate.Day,safeDate.Month,safeDate.Year);
//
//                }
//                else if(query.BlogThreadType == BlogThreadType.Month) {
//                    sb.AppendFormat(" and (P.UserTime >= '{0}' and P.UserTime < '{1}') ",SQLHelper.GetSafeSqlDateTimeFormat(safeDate),SQLHelper.GetSafeSqlDateTimeFormat(safeDate.AddMonths(1)));
//                }
//                else if (query.SortBy == BlogThreadSortBy.MostComments) {
//                    sb.AppendFormat(" and Day(P.UserTime) >= " + DateTime.Now.AddDays(-7) + " ");
//                }
//				else
//				{
//					sb.AppendFormat(" and Year(P.UserTime) = {0} ",safeDate.Year);
//				}
//
//			}
		}

		/// <summary>
		/// WHERE clause element to specify the valid IsApproved / PostDate values
		/// </summary>
		protected override void ApplyPublished()
		{
//			//Do we care if the post has been publshied
//			if(query.PublishedFilter != BlogPostPublishedFilter.All)
//			{
//				sb.AppendFormat(" and P.IsApproved = {0} and P.PostDate <= getdate() ", (int)query.PublishedFilter);
//			}
		}

		/// <summary>
		/// WHERE clause element to speficy the ApplicationPostType to return (bitwise comparison)
		/// </summary>
		protected override void ApplyPostType()
		{

//			sb.AppendFormat(" and P.ApplicationPostType & {0} <> 0 ", (int)query.BlogPostType);
//
//			if(query.PostConfig != BlogPostConfig.Empty)
//				sb.AppendFormat(" and P.PostConfiguration & {0} = {0} ",(int)query.PostConfig );
		}

		/// <summary>
		/// WHERE clause elemenet to apply values set in the FilterKey
		/// The base only runs this if the key length is greater than zero
		/// </summary>
		protected override void ApplyFilterKey()
		{
			sb.AppendFormat(" and S.SectionID in ({0}) and S.SectionID = P.SectionID and S.ApplicationType = {1} ", query.FilterKey, (int)ApplicationType.Forum);
		}

		protected override string GetSectionIDList()
		{
			ArrayList sections = Forums.GetForums(true, false, false, false);
			if (sections.Count > 0)
			{
				StringBuilder list = new StringBuilder();
				foreach (Forum s in sections)
				{
					if (list.Length > 0)
						list.Append(",");

					list.Append(s.SectionID);
				}

				return list.ToString();
			}
			else
				return "null";
		}

		#endregion

	}
}