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

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using CommunityServer.Blogs.Components;
using CommunityServer.Components;
using CommunityServer.SqlDataProvider;

namespace CommunityServer.Data
{
	/// <summary>
	/// Summary description for WeblogSqlDataProvider.
	/// </summary>
	public  class WeblogSqlDataProvider : WeblogDataProvider
	{
        #region Member variables
        protected string databaseOwner	= "dbo";	// overwrite in web.config
        string connectionString = null;
        ProviderHelper sqlHelper = null;
        #endregion

        #region Constructor
        /****************************************************************
        // SqlDataProvider
        //
        /// <summary>
        /// Class constructor
        /// </summary>
        //
        ****************************************************************/
        public WeblogSqlDataProvider(string databaseOwner, string connectionString) 
        {

            // Read the connection string for this provider
            //
            this.connectionString = connectionString;

            // Read the database owner name for this provider
            //
            this.databaseOwner = databaseOwner;

            sqlHelper = ProviderHelper.Instance();

        }
        #endregion

        #region SettingsID 
        
        protected int GetSettingsID()
        {
            return  sqlHelper.GetSettingsID();
        }

        protected SqlParameter SettingsIDParameter()
        {
            SqlParameter p = new SqlParameter("@SettingsID",SqlDbType.Int);
            p.Value = GetSettingsID();
            return p;
        }
        #endregion

        #region Helpers
        protected SqlConnection GetSqlConnection () 
        {

            try 
            {
                return new SqlConnection(ConnectionString);
            } 
            catch 
            {
                throw new CSException(CSExceptionType.DataProvider, "SQL Connection String is invalid.");
            }

        }

        public string ConnectionString 
        {
            get 
            {
                return connectionString;
            }
            set 
            {
                connectionString = value;
            }
        }
        #endregion

		#region Archive

		public override Hashtable GetPostsByMonth(int BlogID, DateTime month)
		{
			using( SqlConnection connection = GetSqlConnection() ) 
			{
				using(SqlCommand command = new SqlCommand(databaseOwner + ".cs_weblog_PostsByMonth", connection))
				{
					command.CommandType = CommandType.StoredProcedure;

					command.Parameters.Add("@SectionID", SqlDbType.Int, 4).Value = BlogID;
					command.Parameters.Add("@Year", SqlDbType.Int, 4).Value = month.Year;
					command.Parameters.Add("@Month", SqlDbType.Int, 4).Value = month.Month;

					connection.Open();
					using(SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult))
					{
						Hashtable items = new Hashtable();
						ArchiveDataItem ad = null;
						while(reader.Read())
						{
							ad = new ArchiveDataItem();
							ad.Date = new DateTime((int)reader["Year"],(int)reader["Month"],(int)reader["Day"]);
							ad.Count = (int)reader["Count"];
							items.Add(ad.Date.Day,ad);
                            
						}

						reader.Close();
						connection.Close();
						command.Dispose();

						return items;
					}
				}
			}
		}



	    public override ArrayList GetPostsByMonths(int BlogID)
        {
            using( SqlConnection connection = GetSqlConnection() ) 
            {
                using(SqlCommand command = new SqlCommand(databaseOwner + ".cs_weblog_PostsByMonthList", connection))
                {
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add("@SectionID", SqlDbType.Int, 4).Value = BlogID;

                    connection.Open();
                    using(SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult))
                    {
                        ArrayList items = new ArrayList();
                        ArchiveDataItem ad = null;
                        while(reader.Read())
                        {
                            ad = new ArchiveDataItem();
                            ad.Date = new DateTime((int)reader["Year"],(int)reader["Month"],1);
                            ad.Count = (int)reader["Count"];
                            items.Add(ad);
                            
                        }

                        reader.Close();
                        connection.Close();
                        command.Dispose();

                        return items;
                    }
               }
            }
        }

		#endregion


        #region GetWeblogPost
        /// <summary>
        /// Returns an instance of WeblogPost.
        /// </summary>
	    public override WeblogPost GetWeblogPost(int postID, bool includeCategories, bool isApproved)
	    {
            WeblogPost entry = null;
            using( SqlConnection connection = GetSqlConnection() ) 
            {
                using(SqlCommand command = new SqlCommand(databaseOwner + ".cs_weblog_Post_Get", connection))
                {
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add("@PostID", SqlDbType.Int, 4).Value = postID;
                    command.Parameters.Add("@IncludeCategories", SqlDbType.Bit).Value = includeCategories;
                    command.Parameters.Add("@IsApprovedFilter", SqlDbType.Bit).Value = isApproved;
                    command.Parameters.Add(SettingsIDParameter());

                    connection.Open();
                    using(SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        if(reader.Read())
                        {
                            entry = PopulateWeblogEntryFromIDataReader(reader, true);
                        }

                        if(includeCategories && reader.NextResult())
                        {
                            ArrayList al = new ArrayList();
                            while(reader.Read())
                            {
                                al.Add(reader["Name"] as string);
                            }
                            entry.Categories = (string[])al.ToArray(typeof(string));
                        }

                        reader.Close();
                    }
                }
            }

            return entry;
	    }
        #endregion

        #region GetBlogThreads

		public override PostSet GetThreadsTrackedByUser(int SectionID, int UserID)
		{
			using( SqlConnection connection = GetSqlConnection() ) 
			{
				using(SqlCommand command = new SqlCommand(databaseOwner + ".cs_weblog_TrackedThreadsByUser", connection))
				{
					command.CommandType = CommandType.StoredProcedure;

					command.Parameters.Add("@SectionID", SqlDbType.Int).Value = SectionID;
					command.Parameters.Add("@UserID", SqlDbType.Int, 4).Value = UserID;
					command.Parameters.Add(this.SettingsIDParameter());

					connection.Open();
					
					using(SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult))
					{
						PostSet ps = new PostSet();	
						while(dr.Read())
						{
							WeblogPost post = PopulateWeblogEntryFromIDataReader(dr);
							//PopulateWeblogEntryFromIDataReader(dr,post);
							ps.Posts.Add(post);
						}
						
						dr.Close();
						return ps;
					}
					
					
				}
			}
		}

        /// <summary>
        /// Returns an instance of ThreadSet by building a dynamic query based on 
        /// supplied BlogThreadQuery
	    public override ThreadSet GetBlogThreads(BlogThreadQuery query)
	    {
            using( SqlConnection connection = GetSqlConnection() ) 
            {
                using(SqlCommand command = new SqlCommand(databaseOwner + ".[cs_shared_Threads_GetThreadSet]", connection))
                {
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add("@SectionID", SqlDbType.Int).Value = query.SectionID;
                    command.Parameters.Add("@PageIndex", SqlDbType.Int, 4).Value = query.PageIndex;
                    command.Parameters.Add("@PageSize", SqlDbType.Int, 4).Value = SQLHelper.GetSafeSqlInt(query.PageSize) ;
					//command.Parameters.Add("@sqlPopulate", SqlDbType.NText).Value = SqlGenerator.BuildBlogThreadQuery(query,databaseOwner);
					command.Parameters.Add("@sqlPopulate", SqlDbType.NText).Value = new BlogThreadQueryBuilder(query,databaseOwner).BuildQuery() ;
					command.Parameters.Add("@UserID", SqlDbType.Int).Value = query.UserID;
                    command.Parameters.Add("@IncludeCategories", SqlDbType.Bit).Value = query.IncludeCategories;
					command.Parameters.Add("@IncludePageIndex", SqlDbType.Bit).Value = query.IncludePageIndex;
					command.Parameters.Add("@TotalRecords", SqlDbType.Int).Direction = ParameterDirection.Output;
                    command.Parameters.Add(SettingsIDParameter());

                    ThreadSet ts = new ThreadSet();

                    // Execute the command
					
                    //If we want to display the list of categories for the post, we will need to 
                    //use a dataset so we can join the child records
                    if(query.IncludeCategories)
                    {
                        DataSet ds = new DataSet();
                        SqlDataAdapter da = new SqlDataAdapter(command);

                        //wait as long as possible to open the conn
                        connection.Open();
                        da.Fill(ds);
                        connection.Close();

                        //keep a referece to the relation since it is used in the GetChildRows look up anyway
                        DataRelation relation = new DataRelation("Categories",ds.Tables[0].Columns["PostID"],ds.Tables[1].Columns["PostID"],false);
                        ds.Relations.Add(relation);
                        
                        DataRowCollection posts = ds.Tables[0].Rows;
                        foreach(DataRow dr in posts)
                        {
                            ts.Threads.Add(PopulateWeblogPostContentFromDataRow(dr,relation));
                        }
   
						if(query.IncludePageIndex)
						{
							DataRowCollection pages = ds.Tables[2].Rows;
							foreach(DataRow dr in pages)
								ts.PageIndex.Add(dr[0], dr[1]);
						}
						
						ds.Clear();
                        ds.Dispose();

                        ts.TotalRecords = (int) command.Parameters["@TotalRecords"].Value;

                    }
                    else
                    {
                        //No categories needed, so we can use a datareader.

                        connection.Open();
                        using(SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection))
                        {
						
                            while(dr.Read())
                            {
                                ts.Threads.Add(PopulateWeblogEntryFromIDataReader(dr,true));
                            }

                            //                            if(query.IncludeCategories)
                            //                                dr.NextResult();

                            //dr.NextResult();
					
							if(query.IncludePageIndex)
							{
								dr.NextResult();
								while(dr.Read())
									ts.PageIndex.Add(dr[0], dr[1]);
							}

                            dr.Close();
                            connection.Close();
                            ts.TotalRecords = (int) command.Parameters["@TotalRecords"].Value;
						}
                    }

                    return ts;

                }
            }
        }
        #endregion

        #region GetBlogPostSet
        /// <summary>
        /// Returns an instance of PostSet based on the BlogPostQuery
        /// </summary>
	    public override PostSet GetPosts(BlogThreadQuery query)
	    {
            using( SqlConnection connection = GetSqlConnection() ) 
            {
                using(SqlCommand command = new SqlCommand(databaseOwner + ".cs_weblog_Postset", connection))
                {
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add("@SectionID", SqlDbType.Int).Value = query.SectionID;
                    command.Parameters.Add("@PostID", SqlDbType.Int).Value = query.PostID;
					if (query.FilterByPostName)
						command.Parameters.Add("@PostName", SqlDbType.NVarChar).Value = query.PostName;
					else
						command.Parameters.Add("@PostName", SqlDbType.NVarChar).Value = DBNull.Value;

					command.Parameters.Add("@PageIndex", SqlDbType.Int, 4).Value = query.PageIndex;
                    command.Parameters.Add("@PageSize", SqlDbType.Int, 4).Value = SQLHelper.GetSafeSqlInt(query.PageSize) ;
                    command.Parameters.Add("@ReturnFullThread", SqlDbType.Bit, 1).Value = query.ReturnFullThread;
                    command.Parameters.Add("@UserID", SqlDbType.Int).Value = query.UserID;
                    command.Parameters.Add("@IncludeCategories", SqlDbType.Bit).Value = query.IncludeCategories;
                    command.Parameters.Add("@TotalRecords", SqlDbType.Int).Direction = ParameterDirection.Output;

					PostSet ps = new PostSet();

                    connection.Open();

                    //NOTE TO SELF: We return the results as seperate sets so that we can avoid some of the more
                    //expensive lookups done ont the main posts!
                    
                    using(SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        //this will return just one post, forget the rest if nothing found here
                        if(reader.Read())
                        {
                            WeblogPost entry = PopulateWeblogEntryFromIDataReader(reader,true);
                            //PopulateWeblogEntryFromIDataReader(reader,entry);
                            
                            if(query.IncludeCategories)
                            {
                                reader.NextResult();
                                ArrayList categories = new ArrayList();
                                while(reader.Read())
                                {
                                    categories.Add(reader["Name"] as string);
                                }
                                
                                entry.Categories = (string[])categories.ToArray(typeof(string));
                            }
                            
                            //we could be setting the thread starter right here?
                            ps.Posts.Add(entry);

                            //comment list
                            reader.NextResult();

                            while(reader.Read())
                            {
                                entry = PopulateWeblogEntryFromIDataReader(reader);
                                //PopulateWeblogEntryFromIDataReader(reader,entry);

                                ps.Posts.Add(entry);
                            }

                            reader.NextResult();
                            ps.TotalRecords = (int) command.Parameters["@TotalRecords"].Value;

                            reader.Close();
                        }
                        else
                        {
                            reader.Close();
                        }
                    }

                    connection.Close();

                    return ps;

                }
            }
	    }

        #endregion

        #region CreateUpdateDelete Posts
        #region AddPost
        /// <summary>
        /// Adds a new Weblog Post to the data store
        /// </summary>
	    public override BlogPostResults AddPost(WeblogPost post, User user, out int postID)
	    {
            postID = -1;

            // Create Instance of Connection and Command Object
            //
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_weblog_Post_Create", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

                // Add parameters
                //
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = post.SectionID;
                myCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = post.ParentID;
                myCommand.Parameters.Add("@AllowDuplicatePosts", SqlDbType.Bit).Value = true;
                myCommand.Parameters.Add("@DuplicateIntervalInMinutes", SqlDbType.Int).Value = 1;
                myCommand.Parameters.Add("@Subject", SqlDbType.NVarChar, 256).Value = post.Subject;
                myCommand.Parameters.Add("@IsLocked", SqlDbType.Bit).Value = post.IsLocked;
                myCommand.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = post.IsApproved;
                myCommand.Parameters.Add("@IsTracked", SqlDbType.Bit).Value = post.IsTracked;
                myCommand.Parameters.Add("@PostType", SqlDbType.Int).Value = post.PostType;
				myCommand.Parameters.Add("@PostMedia", SqlDbType.Int).Value = post.PostMedia;
				myCommand.Parameters.Add("@EmoticonID", SqlDbType.Int).Value = post.EmoticonID;
                myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = user.UserID;
                myCommand.Parameters.Add("@Body", SqlDbType.NText).Value = post.Body;
                myCommand.Parameters.Add("@FormattedBody", SqlDbType.NText).Value = post.FormattedBody;
                myCommand.Parameters.Add("@UserHostAddress", SqlDbType.NVarChar, 32).Value = post.UserHostAddress;
                myCommand.Parameters.Add("@PostDate",SqlDbType.DateTime,8).Value = post.PostDate;
                myCommand.Parameters.Add("@UserTime",SqlDbType.DateTime,8).Value = post.UserTime;
            	
				myCommand.Parameters.Add("@PostStatus", SqlDbType.Int, 4).Value = post.PostStatus;
				myCommand.Parameters.Add("@SpamScore", SqlDbType.Int, 4).Value = post.SpamScore;

				SerializerData data = post.GetSerializerData();


				myCommand.Parameters.Add("@PropertyNames", SqlDbType.NText).Value = data.Keys;
				myCommand.Parameters.Add("@PropertyValues", SqlDbType.NText).Value = data.Values;

                myCommand.Parameters.Add(SettingsIDParameter());
				
                
                myCommand.Parameters.Add("@IsSticky", SqlDbType.Bit).Value = false;
                myCommand.Parameters.Add("@StickyDate", SqlDbType.DateTime).Value = System.Data.SqlTypes.SqlDateTime.MinValue;


                myCommand.Parameters.Add("@PostName",SqlDbType.NVarChar,256).Value = DBValue(post.Name);
                myCommand.Parameters.Add("@TitleUrl",SqlDbType.NVarChar,256).Value = DBValue(post.TitleUrl);
                myCommand.Parameters.Add("@PostConfig",SqlDbType.Int,4).Value = post.PostConfig;
                myCommand.Parameters.Add("@BlogPostType",SqlDbType.TinyInt).Value = post.BlogPostType;

                if(post.BlogPostType == BlogPostType.Post)
                {
                    myCommand.Parameters.Add("@Categories",SqlDbType.NVarChar,4000).Value = ConvertCategoriesToXML(post.Categories);
                }
                else if(post.BlogPostType == BlogPostType.Article)
                {
                    myCommand.Parameters.Add("@Categories",SqlDbType.NVarChar,4000).Value = ConvertCategoriesToXML(post.Categories);
                }

                myCommand.Parameters.Add("@PostID", SqlDbType.Int).Direction = ParameterDirection.Output;

                myConnection.Open();
                myCommand.ExecuteNonQuery();

                // LN 5/27/04: try/catch added to get rid of exceptions
                   BlogPostResults result =  (BlogPostResults)(int)myCommand.Parameters["RETURN_VALUE"].Value;
                    if(result == BlogPostResults.Success)
                        postID = (int) myCommand.Parameters["@PostID"].Value;

                myConnection.Close();
                return result;
            }

	    }
        #endregion

        #region UpdatePost
	    public override BlogPostResults UpdatePost(WeblogPost post, int editedBy)
	    {
            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_weblog_Post_Update", myConnection);

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

                // Add Parameters to SPROC
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = post.SectionID;
                myCommand.Parameters.Add("@PostID", SqlDbType.Int, 4).Value = post.PostID;
                myCommand.Parameters.Add("@Subject", SqlDbType.NVarChar, 256).Value = post.Subject;
                myCommand.Parameters.Add("@Body", SqlDbType.NText).Value = post.Body;
                myCommand.Parameters.Add("@FormattedBody", SqlDbType.NText).Value = post.FormattedBody;
                myCommand.Parameters.Add("@EmoticonID", SqlDbType.Int).Value = post.EmoticonID;
                myCommand.Parameters.Add("@IsLocked", SqlDbType.Bit).Value = post.IsLocked;
                myCommand.Parameters.Add("@IsTracked", SqlDbType.Bit).Value = post.IsTracked;
                myCommand.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = post.IsApproved;
                myCommand.Parameters.Add("@EditedBy", SqlDbType.Int, 4).Value = editedBy;
				myCommand.Parameters.Add("@PostMedia", SqlDbType.Int).Value = post.PostMedia;
                myCommand.Parameters.Add("@PostDate",SqlDbType.DateTime,8).Value = post.PostDate;
                myCommand.Parameters.Add("@UserTime",SqlDbType.DateTime,8).Value = post.UserTime;
            	myCommand.Parameters.Add("@PostStatus", SqlDbType.Int, 4).Value = (int)post.PostStatus;
            	myCommand.Parameters.Add("@SpamScore", SqlDbType.Int, 4).Value = post.SpamScore;

				SerializerData data = post.GetSerializerData();


				myCommand.Parameters.Add("@PropertyNames", SqlDbType.NText).Value = data.Keys;
				myCommand.Parameters.Add("@PropertyValues", SqlDbType.NText).Value = data.Values;

                myCommand.Parameters.Add(SettingsIDParameter());

                // Allow Thread to update sticky properties.
                //
                if (post is IThread) 
                {
                    IThread thread = (IThread) post;
                    myCommand.Parameters.Add("@IsSticky", SqlDbType.Bit).Value = thread.IsSticky;
                    myCommand.Parameters.Add("@StickyDate", SqlDbType.DateTime).Value = thread.StickyDate;
                }

                myCommand.Parameters.Add("@PostName",SqlDbType.NVarChar,256).Value = DBValue(post.Name);
                myCommand.Parameters.Add("@TitleUrl",SqlDbType.NVarChar,256).Value = DBValue(post.TitleUrl);
                myCommand.Parameters.Add("@PostConfig",SqlDbType.Int,4).Value = post.PostConfig;
                myCommand.Parameters.Add("@BlogPostType",SqlDbType.TinyInt).Value = post.BlogPostType;

                if(post.BlogPostType == BlogPostType.Post)
                {
                    myCommand.Parameters.Add("@Categories",SqlDbType.NVarChar,4000).Value = ConvertCategoriesToXML(post.Categories);
                }
                else if(post.BlogPostType == BlogPostType.Article)
                {
                    myCommand.Parameters.Add("@Categories",SqlDbType.NVarChar,4000).Value = ConvertCategoriesToXML(post.Categories);
                }

               
                
                // Execute the command
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();

                BlogPostResults result =  (BlogPostResults)(int)myCommand.Parameters["RETURN_VALUE"].Value;

                return result;
            }
	    }
        #endregion

        #region DeletePost
	    public override void DeletePost(int sectionID, int postID, int userID, BlogPostType blogPostType)
	    {
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_weblog_Post_Delete", myConnection);

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add Parameters to SPROC
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int, 4).Value = sectionID;
				myCommand.Parameters.Add("@PostID", SqlDbType.Int, 4).Value = postID;
                myCommand.Parameters.Add("@UserID", SqlDbType.Int, 4).Value = userID;
                myCommand.Parameters.Add("@BlogPostType", SqlDbType.TinyInt).Value = blogPostType;
                myCommand.Parameters.Add(this.SettingsIDParameter());

                // Execute the command
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
	    }
        #endregion
        #endregion

        #region GetWeblogs
        /// <summary>
        /// Returns all of the Weblogs for the current settingsID
        /// </summary>
        /// <returns></returns>
	    public override Hashtable GetWeblogs()
	    {
            Hashtable weblogs = new Hashtable();
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_weblog_Weblogs_Get", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;
				

                // Add Parameters to SPROC
                myCommand.Parameters.Add(this.SettingsIDParameter());

                // Execute the command
                myConnection.Open();
                SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

                // Get the requested weblogs
                //
                Weblog w = null;
                while (dr.Read()) 
                {
                    w = new Weblog();
                    PopulateWeblogData(dr,w);

                    // add all weblogs into the Hashtable
                    //
                    weblogs.Add(w.ApplicationKey.ToLower(), w);

                }

                // Get the permissions
                //
                if (dr.NextResult()) 
                {

                    while (dr.Read()) 
                    {
                        // Get the forum
                        //
                        string appKey = dr["ApplicationKey"] as string;

						// Specific permissions
						if (appKey != null) {
							w = weblogs[appKey.ToLower()] as Weblog;
							if(w != null) {
								WeblogPermission bp = new WeblogPermission();
								CommonDataProvider.PopulatePermissionFromIDataReader( bp, dr );
								w.PermissionSet.Add(bp.Name, bp);
							}
						} else {

							// Global permissions
							WeblogPermission bp = new WeblogPermission();
							CommonDataProvider.PopulatePermissionFromIDataReader( bp, dr );

							foreach (Weblog wb in weblogs.Values) {
								if(!wb.PermissionSet.ContainsKey(bp.Name))
								wb.PermissionSet.Add(bp.Name,bp);
							}

						}

                    }


                }

             
            
                // Done with the reader and the connection
                //
                dr.Close();
                myConnection.Close();

            }
            return weblogs;
	    }
        #endregion

        #region GetBlogPermissions

        /// <summary>
        /// Returns the permissions for a specific Weblog
        /// </summary>
	    public override ArrayList GetBlogPermissions(int weblogID)
	    {
            // Create Instance of Connection and Command Object
            using( SqlConnection myConnection = GetSqlConnection() ) 
            {
                SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Section_Permissions_Get", myConnection);
                Hashtable blogPermissions = new Hashtable();
                SqlDataReader reader;

                // Mark the Command as a SPROC
                myCommand.CommandType = CommandType.StoredProcedure;

                // Add Parameters to SPROC
                myCommand.Parameters.Add("@SectionID", SqlDbType.Int, 4).Value = weblogID;
                myCommand.Parameters.Add("@ApplicationType", SqlDbType.TinyInt).Value = ApplicationType.Weblog;
                myCommand.Parameters.Add(this.SettingsIDParameter());
                

                myConnection.Open();

                using(reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult))
                {


                    while(reader.Read()) 
                    {
                        WeblogPermission bp = new WeblogPermission();
                        CommonDataProvider.PopulatePermissionFromIDataReader(bp,reader);

                        // Merge the permission
                        if (blogPermissions[bp.RoleID] != null) 
                        {

                        } 
                        else 
                        {
                            blogPermissions.Add(bp.RoleID, bp);
                        }

                    }
                }

                myConnection.Close();


				return new ArrayList(blogPermissions.Values);

			}
	    }
        #endregion

        #region Search

		public override PostSet SearchReindexPosts (int setsize, int settingsID) 
		{
			BlogThreadQuery query = new BlogThreadQuery();
			query.FirstPageOnly = true;
			query.PageSize = setsize;
			query.IncludeCategories = true;
			query.PublishedFilter = BlogPostPublishedFilter.Published;
			query.IncludePageIndex = true;
			query.BlogPostType = BlogPostType.Post | BlogPostType.Article;
			query.SortBy = BlogThreadSortBy.MostRecent;
			query.SortOrder = SortOrder.Descending;
		    
		
			ThreadSet ts = SearchIndexPosts(settingsID, query);

			PostSet postSet = new PostSet();
			foreach(WeblogPost wp in ts.Threads)
				postSet.Posts.Add(wp); 

			return postSet;
		}

		public override ThreadSet SearchIndexPosts (int setsize, BlogThreadQuery query)
		{
			using( SqlConnection connection = GetSqlConnection() ) 
			{
				using(SqlCommand command = new SqlCommand(databaseOwner + ".[cs_shared_Threads_GetThreadSet]", connection))
				{
					command.CommandType = CommandType.StoredProcedure;

					command.Parameters.Add("@SectionID", SqlDbType.Int).Value = query.SectionID;
					command.Parameters.Add("@PageIndex", SqlDbType.Int, 4).Value = query.PageIndex;
					command.Parameters.Add("@PageSize", SqlDbType.Int, 4).Value = SQLHelper.GetSafeSqlInt(query.PageSize) ;
					//command.Parameters.Add("@sqlPopulate", SqlDbType.NText).Value = SqlGenerator.BuildBlogThreadQuery(query,databaseOwner);

					string sqlindex = new BlogThreadQueryBuilder(query,databaseOwner).BuildQuery();
					string appTypeFilter = string.Format("S.ApplicationType={0} and S.IsSearchable=1", (int)ApplicationType.Weblog); 
					sqlindex = sqlindex.Replace("where ", string.Format("where P.IsIndexed=0 and {0} and ", appTypeFilter))  ;
					command.Parameters.Add("@sqlPopulate", SqlDbType.NText).Value = sqlindex;

					command.Parameters.Add("@UserID", SqlDbType.Int).Value = query.UserID;
					command.Parameters.Add("@IncludeCategories", SqlDbType.Bit).Value = query.IncludeCategories;
					command.Parameters.Add("@IncludePageIndex", SqlDbType.Bit).Value = query.IncludePageIndex;
					command.Parameters.Add("@TotalRecords", SqlDbType.Int).Direction = ParameterDirection.Output;
					command.Parameters.Add(SettingsIDParameter());

					ThreadSet ts = new ThreadSet();

					// Execute the command
					
					//If we want to display the list of categories for the post, we will need to 
					//use a dataset so we can join the child records
					if(query.IncludeCategories)
					{
						DataSet ds = new DataSet();
						SqlDataAdapter da = new SqlDataAdapter(command);

						//wait as long as possible to open the conn
						connection.Open();
						da.Fill(ds);
						connection.Close();

						//keep a referece to the relation since it is used in the GetChildRows look up anyway
						DataRelation relation = new DataRelation("Categories",ds.Tables[0].Columns["PostID"],ds.Tables[1].Columns["PostID"],false);
						ds.Relations.Add(relation);
                        
						DataRowCollection posts = ds.Tables[0].Rows;
						foreach(DataRow dr in posts)
						{
							ts.Threads.Add(PopulateWeblogPostContentFromDataRow(dr,relation));
						}
   
						if(query.IncludePageIndex)
						{
							DataRowCollection pages = ds.Tables[2].Rows;
							foreach(DataRow dr in pages)
								ts.PageIndex.Add(dr[0], dr[1]);
						}
						
						ds.Clear();
						ds.Dispose();

						ts.TotalRecords = (int) command.Parameters["@TotalRecords"].Value;

					}
					else
					{
						//No categories needed, so we can use a datareader.

						connection.Open();
						using(SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection))
						{
						
							while(dr.Read())
							{
								ts.Threads.Add(PopulateWeblogEntryFromIDataReader(dr,true));
							}

							//                            if(query.IncludeCategories)
							//                                dr.NextResult();

							//dr.NextResult();
					
							if(query.IncludePageIndex)
							{
								dr.NextResult();
								while(dr.Read())
									ts.PageIndex.Add(dr[0], dr[1]);
							}

							dr.Close();
							connection.Close();
							ts.TotalRecords = (int) command.Parameters["@TotalRecords"].Value;
						}
					}

					return ts;

				}
			}
		}


       #endregion

        public override void UpdateRecentContent(int SettingsID)
        {
            using(SqlConnection conn = GetSqlConnection())
            {
                using(SqlCommand command = new SqlCommand(databaseOwner + ".cs_weblog_UpdateWeblogRecentContent_Job", conn))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add("@SettingsID", SqlDbType.Int,4).Value = SettingsID;
                    conn.Open();
                    command.ExecuteNonQuery();
                    conn.Close();
                    command.Dispose();
                }
            }
        }

		public override void DeleteStaleSpamComments(int SettingsID, int ageInDays)
		{
			using (SqlConnection conn = GetSqlConnection())
			{
				using (SqlCommand command = new SqlCommand(databaseOwner + ".cs_weblog_DeleteStaleSpamComments", conn))
				{
					command.CommandType = CommandType.StoredProcedure;
					command.Parameters.Add("@SettingsID", SqlDbType.Int,4).Value = SettingsID;
					command.Parameters.Add("@ExpirationDays", SqlDbType.Int,4).Value = ageInDays;
					conn.Open();
					command.ExecuteNonQuery();
					conn.Close();
				}
			}
		}

        private static object DBValue(string text)
        {
            if(!Globals.IsNullorEmpty(text))
                return text;
            else
                return DBNull.Value;
        }
	}
}
