sqlgenerator.cs

解压即可使用

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

using System;
using System.Text;
using CommunityServer.Blogs.Components;
using CommunityServer.Components;
using CommunityServer.Galleries.Components;

namespace CommunityServer.Data
{
	/// <summary>
	/// Helper class for generating dynamic SQL.
	/// </summary>
	public class SqlGenerator
	{
		private SqlGenerator()
		{
		}

        #region SearchText

        /// <summary>
        /// Builds the baseline query for searching against the searchbarrel table.
        /// </summary>
        public static string SearchText(SearchQuery query, SearchTerms terms, int settingsID, ApplicationType appType)//, out string searchSQL, out string recordCount)
        {
            bool hasORterms = false;
            string searchSQL = "SELECT DISTINCT B0.PostID, B0.SectionID, Weight = ({0}), P.PostDate FROM {1}, cs_Posts P, cs_Sections F  WHERE {2} {3} {4} AND F.SectionID = P.SectionID  AND P.SettingsID = {5} AND F.ApplicationType = {7} AND F.IsSearchable = 1 {6} ORDER BY Weight DESC, PostDate DESC";
            //recordCount = "SELECT TotalRecords = COUNT(DISTINCT B0.PostID) FROM {1}, cs_Posts P {6} WHERE {2} {3} AND {4} AND P.SettingsID = {5} {7}";
            string orSQL = "(";
            string[] clauses = new string[8];

            // OR clause in search terms
            //
            if (terms.Or.Length > 1) 
            {
                hasORterms = true;

                for (int i = 0; i < terms.Or.Length; i++) 
                {
                    string barrel = "B0.WordHash = {0}";

                    orSQL += string.Format(barrel, terms.Or[i]);

                    if ((i+1) < terms.Or.Length) 
                    {
                        orSQL += " OR ";
                    } 
                    else 
                    {
                        clauses[2] += orSQL += ") AND ";
                    }

                }
            }

            // AND clause in search terms
            //
            if (terms.And.Length > 0) 
            {

                for (int i = 0; i < terms.And.Length; i++) 
                {
                    string barrel = "B{0}";

                    // Build the clauses
                    if (hasORterms)
                        clauses[2] += string.Format(barrel, (i+1)) + ".WordHash = " + terms.And[i];
                    else
                        clauses[2] += string.Format(barrel, i) + ".WordHash = " + terms.And[i];

                    if ((i+1) < terms.And.Length) 
                    {
                        clauses[2] += " AND ";
                    }

                }

                clauses[2] += " AND ";
            }

            if(query.SectionsToSearch != null || query.SectionsToFilter != null || query.UsersToSearch != null)
            {
                StringBuilder sb = new StringBuilder();
                
            
                // FORUMS
                //
                if (query.SectionsToSearch != null && query.SectionsToSearch.Length > 0) 
                {
                    sb.Append(" AND B0.SectionID IN (");
                    for (int i = 0; i < query.SectionsToSearch.Length; i++) 
                    {
                        sb.Append(query.SectionsToSearch[i]);
                        // Build the clauses
                       // clauses[4] += barrel + query.SectionsToSearch[i];

                        if ((i+1) < query.SectionsToSearch.Length) 
                        {
                            sb.Append(",");
                        } 
                    }

                     sb.Append(")");
                }

                if(query.SectionsToFilter != null && query.SectionsToFilter.Count > 0)
                {
                    
                    sb.Append(" AND B0.SectionID IN (");
                    for(int i =0; i <query.SectionsToFilter.Count; i++)
                    {
                        sb.Append(((Section)query.SectionsToFilter[i]).SectionID.ToString() );
                        if ((i+1) < query.SectionsToFilter.Count) 
                        {
                            sb.Append(",");
                        } 
                    }
                    sb.Append(")");
                }

                // Users
                //
                if (query.UsersToSearch != null && query.UsersToSearch.Length > 0 && query.UsersToSearch[0] != "") 
                {
                    string usersSearch = string.Empty;

                    sb.Append(" AND P.UserID IN (");
                    for (int i = 0; i < query.UsersToSearch.Length; i++) 
                    {
                        string barrel = "P.UserID = ";
                         sb.Append(query.UsersToSearch[i]);

                        // Build the clauses
                        usersSearch += barrel + query.UsersToSearch[i];

                        if ((i+1) < query.UsersToSearch.Length) 
                        {
                            sb.Append(",");
                        } 
      

                    }
                     sb.Append(")");

                }
                clauses[4] = sb.ToString();

            }

            // Main Loop for AND clause in search terms
            //
            int loopCount = terms.And.Length;

            if (hasORterms)
                loopCount ++;

            if (loopCount > 0) 
            {
                for (int i = 0; i < loopCount; i++) 
                {
                    string barrel = "B{0}";

                    clauses[0] += string.Format(barrel, i) + ".Weight";
                    clauses[1] += "cs_SearchBarrel " + string.Format(barrel, i);
                    clauses[3] += string.Format(barrel, i) + ".PostID = P.PostID";

                    if ((i+1) < loopCount) 
                    {
                        clauses[0] += " + ";
                        clauses[1] += ", ";
                        clauses[3] += " AND ";
                    }
                }
            } 
            else 
            {
                clauses[0] = "0";
                clauses[1] = "cs_SearchBarrel B0";
                clauses[3] = "B0.PostID = P.PostID";
            }

            clauses[5] = settingsID.ToString();
            clauses[7] = ((int)appType).ToString();

            if(query.GroupToSearch != null && query.GroupToSearch.Length > 0)
            {
                clauses[6] = " AND F.SectionID = P.SectionID AND (";

                for(int i = 0; i < query.GroupToSearch.Length; i++)
                {
                    if(i != 0)
                        clauses[6] += " OR ";

                    clauses[6] += " F.GroupID = " + query.GroupToSearch[i];
                }

                clauses[6] += ")";
            }




            return  string.Format(searchSQL, clauses);
            //recordCount = string.Format(recordCount, clauses);
        }
        #endregion

        #region BuildBlogThreadQuery
        public static string BuildBlogThreadQuery(BlogThreadQuery query, string databaseOwner)
        {
            StringBuilder sb = new StringBuilder();

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

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

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

            if(query.FilterKey.Length > 0)
                sb.AppendFormat(", {0}.cs_Sections S ",databaseOwner);

            //Are we filtering by CategoryID (this has nothing to do with returning categories)
            if(query.CategoryID > 0)
            {
                sb.AppendFormat(" , {0}.cs_Post_Categories C, {0}.cs_Posts_InCategories PC ",databaseOwner);
            }

            sb.Append(" where ");

            if(query.HasBlog)
                sb.AppendFormat(" P.SectionID = {0} and ",query.BlogID);

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

            if(query.HasGroup)
                sb.AppendFormat(" P.SectionID = S.SectionID and  S.GroupID = {0} and ",query.BlogGroupID);

			if(query.FilterKey.Length > 0)
				sb.AppendFormat(" S.SectionID in ({0}) and S.SectionID = P.SectionID and S.ApplicationType = 1 and ", query.FilterKey);

            //Add standard filters
            sb.AppendFormat(" B.PostID = P.PostID and P.PostLevel = 1 and B.BlogPostType & {1} <> 0  ",query.BlogID,(int)query.BlogPostType);
            sb.AppendFormat("  and P.SettingsID = {0} and B.SettingsID = {0} ", ProviderHelper.Instance().GetSettingsID());
            
			if(query.PostConfig != BlogPostConfig.Empty)
				sb.AppendFormat(" and B.PostConfig & {0} = {0} ",(int)query.PostConfig );
			
			//Add category filter if necessary
            if(query.CategoryID > 0)
            {
                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);
            }

            

            //Do we care if the post has been publshied
            if(query.IsPublished)
            {
                sb.Append(" and P.IsApproved = 1 and P.PostDate <= getdate()  ");
            }

            //Do we care about dates?
            if(query.BlogThreadType != BlogThreadType.Recent && query.BlogThreadType != BlogThreadType.Category)
            {
                DateTime safeDate = GetSafeSqlDateTime(query.DateFilter);
                if(query.BlogThreadType == BlogThreadType.Day)
                {
                    sb.AppendFormat(" and Day(B.BloggerTime) = {0} and Month(B.BloggerTime) = {1} and Year(B.BloggerTime) = {2} ",safeDate.Day,safeDate.Month,safeDate.Year);

                }
                else if(query.BlogThreadType == BlogThreadType.Month)
                {
                    sb.AppendFormat(" and (B.BloggerTime >= '{0}' and B.BloggerTime < '{1}') ",safeDate,safeDate.AddMonths(1));
                }
                else
                {
                    sb.AppendFormat(" and Year(B.BloggerTime) = {0} ",safeDate.Year);
                }

            }

			if(query.SortOrder == SortOrder.Ascending)
			{
				sb.Append(" Order by P.PostDate asc ");
			}
			else
			{
				sb.Append(" Order by P.PostDate desc ");
			}

            //Send back the query
            return sb.ToString();
        }

        #endregion

		#region BuildGalleryThreadQuery

		public static string BuildGalleryThreadQuery(GalleryThreadQuery query, string databaseOwner)
		{

			StringBuilder sb = new StringBuilder();

			// Start with the basic tables
			sb.AppendFormat("select T.ThreadID from {0}.cs_Threads T ", databaseOwner);
			sb.AppendFormat("right join {0}.cs_Posts P on (P.ThreadID = T.ThreadID and P.PostLevel = 1) ", databaseOwner);
			sb.AppendFormat("right join {0}.cs_Sections F on (F.SectionID = T.SectionID and F.ApplicationType = {1}) ", databaseOwner, (int)ApplicationType.Gallery);
			sb.AppendFormat("left join {0}.cs_PostMetadata M on (M.PostID = P.PostID and M.MetaKey = '{1}') ", databaseOwner, ExifProperty.DateTimeOriginal.ToString());

			// Are we filtering by CategoryID (this has nothing to do with returning categories)
			if(query.HasCategory || query.HasNoCategories)
				sb.AppendFormat("left join {0}.cs_Posts_InCategories PC on (PC.PostID = P.PostID)",databaseOwner);

			sb.Append("where ");

			if(query.HasFolder)
				sb.AppendFormat("T.SectionID = {0} and ", query.SectionID);

			if(query.IsGalleryEnabled)
				sb.Append("F.IsActive = 1 and ");

			if(query.OnlyApproved)
				sb.AppendFormat("T.IsApproved = 1 and ");

			if(query.HasGroup)
				sb.AppendFormat("P.SectionID = F.SectionID and F.GroupID = {0} and ", query.GroupID);

			if(query.FilterKey.Length > 0)
				sb.AppendFormat("F.SectionID in ({0}) and ", query.FilterKey);

			// Add standard filters
			sb.AppendFormat("T.SettingsID = {0} and P.SettingsID = {0} ", ProviderHelper.Instance().GetSettingsID());

			// Add category filter if necessary
			if(!query.HasNoCategories && query.HasCategory)
				sb.AppendFormat(" and PC.CategoryID = {0}", query.CategoryID);

			// Ordering
			string order = "desc";
			if(query.SortOrder == SortOrder.Ascending)
				order = "asc";

			// Sort by
			string groupBy = string.Empty;
			string orderBy = string.Empty;
			switch(query.SortBy)
			{
				case GalleryThreadSortBy.Author:
					orderBy = " order by P.PostAuthor";
					groupBy = "P.PostAuthor";
					break;
				case GalleryThreadSortBy.Comments:
					orderBy = " order by T.TotalReplies";
					groupBy = "T.TotalReplies";
					break;
				case GalleryThreadSortBy.Rating:
					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 GalleryThreadSortBy.Subject:
					orderBy = " order by P.Subject";
					groupBy = "P.Subject";
					break;
				case GalleryThreadSortBy.Views:
					orderBy = " order by T.TotalViews";
					groupBy = "T.TotalViews";
					break;
				default:
				case GalleryThreadSortBy.ThreadDate:
					orderBy = " order by P.PostDate";
					groupBy = "P.PostDate";
					break;
				case GalleryThreadSortBy.PictureDate:
					orderBy = " order by M.MetaValue " + order + ", P.PostDate";
					groupBy = "M.MetaValue, P.PostDate";
					break;
			}

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

			// Send back the query
			return sb.ToString();
		}

		#endregion

        private static DateTime GetSafeSqlDateTime (DateTime date) 
        {

            if (date == DateTime.MinValue)
                return (DateTime) System.Data.SqlTypes.SqlDateTime.MinValue;
            return date;

        }

	}
}