basethreadquerybuilder.cs

community server 源码

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

using System.Text;
using CommunityServer.Components;
using System.Text.RegularExpressions;

namespace CommunityServer.SqlDataProvider
{
	/// <summary>
	/// Generates a query to retrive a list of PostID's based on a ThreadQuery object
	/// </summary>
	public abstract class BaseThreadQueryBuilder
	{
		
		#region Instance

		private ThreadQuery query;
		protected string databaseOwner;
		protected StringBuilder sb;

		/// <summary>
		/// Create an instance of the object with shared access to the databaseOwner string and
		/// a StringBuilder object
		/// </summary>
		/// <param name="query">an object derrived from ThreadQuery</param>
		/// <param name="databaseOwner"></param>
		public BaseThreadQueryBuilder(ThreadQuery query, string databaseOwner)
		{
			this.query = query;
			this.databaseOwner = databaseOwner;
			sb = new StringBuilder();
		}

		#endregion

		/// <summary>
		/// Builds up the stringbuilder string with the SQL query
		/// </summary>
		/// <returns>The postID SQL query as a string</returns>
		public string BuildQuery()
		{

			sb.Append("SET Transaction Isolation Level Read UNCOMMITTED Select ");

			if(query.FirstPageOnly)
				sb.AppendFormat(" top {0}", query.PageSize);

			//Start with the basic tables
			sb.AppendFormat(" P.PostID From {0}.cs_Posts P ",databaseOwner);

			sb.AppendFormat("right join {0}.cs_Threads t on (P.ThreadID = T.ThreadID) ", databaseOwner);

			if(query.FilterKey.Length > 0 || query.RequireSectionIsActive || query.FilterByGroup || query.FilterBySection)
			{
				//sb.AppendFormat(", {0}.cs_Sections S ",databaseOwner);
				sb.AppendFormat("inner join {0}.cs_Sections S on S.SectionID = P.SectionID ", databaseOwner);
			}
			//Are we filtering by CategoryID (this has nothing to do with returning categories)
			if(query.FilterByCategory || query.UncategorizedOnly)
			{
				sb.AppendFormat("left outer join {0}.cs_Posts_InCategories PC on PC.PostID = P.PostID ",databaseOwner);
				sb.AppendFormat("left outer join  {0}.cs_Post_Categories C on PC.CategoryID = C.CategoryID ",databaseOwner);
			}

			AddAditionalTables();

			sb.Append(" where ");

			sb.AppendFormat(" P.SettingsID = {0} ", ProviderHelper.Instance().GetSettingsID());

			if(query.FilterByPost)
				sb.AppendFormat(" and P.PostID = {0} ",query.PostID);
			else if(query.FilterByMultiplePosts)
				sb.AppendFormat(" and ( P.PostID IN ({0}) ) ", Globals.JoinIntArray(",", query.MultiplePostIDs));

			if(query.FilterByPostName)
				sb.AppendFormat(" and P.PostName = N'{0}' ", query.PostName.Replace("'", "''"));

			if(query.FilterByParent)
				sb.AppendFormat(" and P.ParentID = {0} ",query.ParentID);

            if(query.FilterBySection)
                sb.AppendFormat(" and P.SectionID = {0} ",query.SectionID);
            else
            {
                string sectionList = GetSectionIDList();
                if(sectionList != null)
                    sb.AppendFormat(" and P.SectionID in ({0}) ", sectionList);
            }
			if(query.FilterByUserID)
				sb.AppendFormat(" and P.UserID = {0} ",query.UserID);


			if(query.RequireSectionIsActive)
				sb.Append(" and S.IsActive = 1 ");

			if(query.FilterByGroup)
				sb.AppendFormat("and S.GroupID = {0} ",query.GroupID);

			if(query.FilterKey.Length > 0)
				ApplyFilterKey();

			if(query.PostMedia != PostMediaType.Empty)
				sb.AppendFormat(" and (P.PostMedia & {0} = {0}) ",(int)query.PostMedia );
			
			if(query.PostStatus != PostStatus.Ignore)
				sb.AppendFormat(" and (P.PostStatus & {0} = {0})", (int) query.PostStatus);
			
			if(query.FilterBySpamScore)
			{
				if(query.MinimumSpamScore > -1 && query.MaximumSpamScore > -1)
				{
					sb.AppendFormat(" and (P.SpamScore >={0} and P.SpamScore < {1})", query.MinimumSpamScore, query.MaximumSpamScore);
				}
				else if(query.MinimumSpamScore > -1)
				{
					sb.AppendFormat(" and P.SpamScore >= {0}", query.MinimumSpamScore);
				}
				else
				{
					sb.AppendFormat(" and P.SpamScore < {0}", query.MaximumSpamScore);
				}
			}

			ApplyPostType();

			//Add category filter if necessary
			if(!query.UncategorizedOnly && query.FilterByCategory)
			{
				sb.AppendFormat(" and P.SectionID = C.SectionID and C.CategoryID = {0} and C.CategoryID = PC.CategoryID and PC.CategoryID = {0} and PC.PostID = P.PostID ",query.CategoryID);
			}

			// Add tag filter if necessary
			if (query.FilterByTags)
			{
				StringBuilder listOfTags = new StringBuilder();
				foreach (string tag in query.Tags)
				{
					if (listOfTags.Length > 0)
						listOfTags.Append(",");

					listOfTags.Append("N'");
					listOfTags.Append(tag.Replace("'", "''"));
					listOfTags.Append("'");
				}

				if (!query.LogicallyOrTags)
				{
					// and tags
					sb.Append(" and P.PostID in (select tP.PostID from cs_Post_Categories tC inner join cs_Posts_InCategories tPiC on tPiC.CategoryID = tC.CategoryID inner join cs_Posts tP on tPiC.PostID = tP.PostID where tC.IsEnabled = 1 and tC.Name in (");
					sb.Append(listOfTags.ToString());
					sb.Append(") and tC.SettingsID = ");
					sb.Append(ProviderHelper.Instance().GetSettingsID());
					sb.Append("	and tP.SettingsID = ");
					sb.Append(ProviderHelper.Instance().GetSettingsID());

                    if(query.FilterBySection)
					    sb.Append("	and tC.SectionID = " + query.SectionID.ToString());
                    else
                    {
                        string sectionList = GetSectionIDList();
                        if(sectionList != null)
                        {
                            sb.Append(" and tC.SectionID in (" + sectionList + ") ");
                        }
                    }
					sb.Append(" group by tP.PostID, tP.PostDate having count(*) = ");
					sb.Append(query.Tags.Length);
					sb.Append(")" );
				}
				else
				{
					// or tags
					sb.Append(" and P.PostID in (select tPiC.PostID from cs_Post_Categories tC inner join cs_Posts_InCategories tPiC on tPiC.CategoryID = tC.CategoryID where tC.Name in (");
					sb.Append(listOfTags.ToString());
					sb.Append(") and tC.SettingsID = ");
					sb.Append(ProviderHelper.Instance().GetSettingsID());

                    if(query.FilterBySection)
                        sb.Append("	and tC.SectionID = " + query.SectionID.ToString());
                    else
                    {
                        string sectionList = GetSectionIDList();
                        if(sectionList != null)
                        {
                            sb.Append(" and tC.SectionID in (" + sectionList + ") ");
                        }
                    }

					sb.Append(" and tC.IsEnabled = 1 ) ");
				}
			}

			ApplyPublished();

			ApplyDateFilter();

			ApplySort();

			return sb.ToString(); 

		}




		#region QueryBuilder Stubs

		/// <summary>
		/// FROM clause element to add additional tables to the query (optional)
		/// </summary>
		protected virtual void AddAditionalTables() {}

		/// <summary>
		/// WHERE clause element to filter by date ranges (optional)
		/// </summary>
		protected virtual void ApplyDateFilter(){}

		/// <summary>
		/// WHERE clause element to specify the valid IsApproved / PostDate values
		/// </summary>
		protected abstract void ApplyPublished();

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

		/// <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 abstract void ApplyFilterKey();

		/// <summary>
		/// ORDER BY, HAVING and GROUP BY objects appended after the WHERE clause
		/// </summary>
		protected abstract void ApplySort();

		/// <summary>
		/// Retrieves a comma-seperated list of SectionIDs the current user has access to (this is called when a SectionID is not specified on the query)
		/// </summary>
		/// <returns></returns>
		protected abstract string GetSectionIDList();

		#endregion

	}
}