sqlgenerator.cs

本系统是在asp版《在线文件管理器》的基础上设计制作

//                        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;

        }


		#region Helpers for building SQL query. Added by [e-mail]
		#region BuildKeywordClauses
		// ATTENTION: you MUST keep the pattern in a pair of () !!!
		private static string BuildKeywordClauses(string pattern, SearchTerms terms)
		{
			string clauses = null;

			// OR clause in search terms
			//
			if (terms.Or.Length > 1) 
			{
				for (int i = 0; i < terms.Or.Length; i++) 
				{
					clauses += string.Format(pattern, terms.Or[i]);

					if ((i+1) < terms.Or.Length) 
					{
						clauses += " OR ";
					}
				}
			}

			// AND clause in search terms
			//
			if (terms.And.Length > 0) 
			{
				if (clauses != null)
				{
					clauses += " AND ";
				}
				for (int i = 0; i < terms.And.Length; i++) 
				{
					// Build the clauses
					clauses += string.Format(pattern, terms.And[i]);

					if ((i+1) < terms.And.Length) 
					{
						clauses += " AND ";
					}
				}
			}
			return clauses;
		}
		#endregion

		#region BuildSectionAndUserClauses
		private static string BuildSectionAndUserClauses(SearchQuery query)
		{
			string clauses = null;

			if(query.SectionsToSearch != null || query.SectionsToFilter != null || query.UsersToSearch != null)
			{
				StringBuilder sb = new StringBuilder();
            
				// Sections
				//
				if (query.SectionsToSearch != null && query.SectionsToSearch.Length > 0) 
				{
					sb.Append(" P.SectionID IN (");
					for (int i = 0; i < query.SectionsToSearch.Length; i++) 
					{
						sb.Append(query.SectionsToSearch[i]);
						if ((i+1) < query.SectionsToSearch.Length) 
						{
							sb.Append(",");
						} 
					}
					sb.Append(")");
				}

				if(query.SectionsToFilter != null && query.SectionsToFilter.Count > 0)
				{
					if (sb.Length > 0)
						sb.Append(" AND ");
					sb.Append(" P.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] != "") 
				{
					if (sb.Length > 0)
						sb.Append(" AND ");
					sb.Append(" P.UserID IN (");
					for (int i = 0; i < query.UsersToSearch.Length; i++) 
					{
						sb.Append(query.UsersToSearch[i]);
						if ((i+1) < query.UsersToSearch.Length) 
						{
							sb.Append(",");
						} 
					}
					sb.Append(")");
				}
				clauses = sb.ToString();
			}
			return clauses;
		}
		#endregion

		#region BuildGroupClauses
		private static string BuildGroupClauses(SearchQuery query)
		{
			string clauses = null;

			if(query.GroupToSearch != null && query.GroupToSearch.Length > 0)
			{
				clauses = " AND F.SectionID = P.SectionID AND (";
				for(int i = 0; i < query.GroupToSearch.Length; i++)
				{
					if(i != 0)
						clauses += " OR ";
					clauses += " F.GroupID = " + query.GroupToSearch[i];
				}
				clauses += ")";
			}
			return clauses;
		}
		#endregion

		#endregion
	}
}