//------------------------------------------------------------------------------
// <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.Components;
using CommunityServer.Galleries;
using CommunityServer.Galleries.Components;
using CommunityServer.SqlDataProvider;
using Telligent.CommunityServer.MetadataExtractor;

namespace CommunityServer.Data
{
	/// <summary>
	/// Summary description for GallerySqlDataProvider.
	/// </summary>
	public class GallerySqlDataProvider : GalleryDataProvider
	{
		#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 GallerySqlDataProvider(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

		public override Hashtable GetGalleries(bool mergePermissions)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Sections_Get", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				Hashtable galleries = new Hashtable();

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@ApplicationType", SqlDbType.SmallInt).Value = ApplicationType.Gallery;
				myCommand.Parameters.Add("@AllowInactive", SqlDbType.Bit).Value = true;
				myCommand.Parameters.Add("@IncludePostTypeCounts", SqlDbType.Bit).Value = true;
				myCommand.Parameters.Add(this.SettingsIDParameter());

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

				// Get the requested forums
				while (dr.Read()) 
				{
					Gallery gallery = new Gallery();
					CommonDataProvider.PopulateSectionFromIDataRecord(dr,gallery);

					// Add the album to the Hashtable
					galleries.Add(gallery.ApplicationKey.ToLower(), gallery);
				}

				// Get the permissions
				if(dr.NextResult())
				{
					while(dr.Read())
					{
						// Get the section id
						GalleryPermission permission = new GalleryPermission();
						CommonDataProvider.PopulatePermissionFromIDataReader(permission, dr);

						// Are we applying this to a particular forum?
						if(dr["ApplicationKey"] != DBNull.Value)
						{
							string applicationKey = (string) dr["ApplicationKey"];
							Gallery gallery = galleries[applicationKey] as Gallery;

							if(gallery == null)
								continue;

							permission.SectionID = gallery.SectionID;
							gallery.PermissionSet.Add( permission.Name, permission );

//							if(!permission.View)
//								gallery.IsPrivate = true;
						}
						else
						{
							// Apply the permission to all forums
							foreach(Gallery gallery in galleries.Values)
							{
								permission.SectionID = gallery.SectionID;

								// Merge the permission
								if(gallery.PermissionSet[permission.Name] == null)
									gallery.PermissionSet.Add( permission.Name, permission );

//								if(!permission.View)
//									gallery.IsPrivate = true;
							}
						}
					}
				}

				//Get the post type summary
				if(dr.NextResult())
				{
					while(dr.Read())
					{
						if(dr["ApplicationKey"] != DBNull.Value)
						{
							string applicationKey = (string) dr["ApplicationKey"];
							Gallery gallery = galleries[applicationKey] as Gallery;

							if(gallery == null)
								continue;
							
							PopulateGalleryPostTypeStatistics(dr, gallery);
							
							// Update the Hashtable
							galleries[applicationKey] = gallery;
						}
					}
				}
            
				// Done with the reader and the connection
				dr.Close();
				myConnection.Close();

				return galleries;
			}
		}

		public override void UpdatePictureCategories(int galleryID, int postID, string[] categoryList)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Posts_UpdatePostsInCategories", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add parameters
				//
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = galleryID;
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;
				myCommand.Parameters.Add("@CategoryList", SqlDbType.NVarChar, 4000).Value = ConvertCategoriesToXML(categoryList);
				myCommand.Parameters.Add(this.SettingsIDParameter());
				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
		}

		public override ArrayList GetPictureCategories(int postID)
		{
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Posts_GetPostInCategories", myConnection);
				ArrayList categories = new ArrayList();

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

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;

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

				while(dr.Read())
				{
					// Add categories
					PostCategory category = new PostCategory();

					category.CategoryID =			(int) dr["CategoryID"];
					category.SectionID =				(int) dr["SectionID"];
					category.Name =					(string) dr["Name"];
					category.IsEnabled =			(bool) dr["IsEnabled"];
					category.ParentID =				(int) dr["ParentID"];

					if(dr["Description"] != DBNull.Value)
						category.Description =		(string) dr["Description"];

					categories.Add(category);
				}

				dr.Close();
				myConnection.Close();

				// return the collection of IDs
				return categories;
			}
		}

		public override void CreatePicture(GalleryPost galleryPost)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Post_CreateUpdate", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add parameters
				//
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = galleryPost.SectionID;
				myCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = galleryPost.ParentID;
				myCommand.Parameters.Add("@AllowDuplicatePosts", SqlDbType.Bit).Value = true;
				myCommand.Parameters.Add("@DuplicateIntervalInMinutes", SqlDbType.Int).Value = 0;
				myCommand.Parameters.Add("@Subject", SqlDbType.NVarChar, 256).Value = galleryPost.Subject;
				myCommand.Parameters.Add("@IsLocked", SqlDbType.Bit).Value = galleryPost.IsLocked;
				myCommand.Parameters.Add("@PostType", SqlDbType.Int).Value = galleryPost.PostType;
				myCommand.Parameters.Add("@PostMedia", SqlDbType.Int).Value = galleryPost.PostMedia;
				myCommand.Parameters.Add("@EmoticonID", SqlDbType.Int).Value = galleryPost.EmoticonID;
				myCommand.Parameters.Add("@PostAuthor", SqlDbType.NVarChar, 64).Value = galleryPost.Username;
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = galleryPost.AuthorID;
				myCommand.Parameters.Add("@Body", SqlDbType.NText).Value = galleryPost.Body;
				myCommand.Parameters.Add("@FormattedBody", SqlDbType.NText).Value = galleryPost.FormattedBody;
				myCommand.Parameters.Add("@UserHostAddress", SqlDbType.NVarChar, 32).Value = galleryPost.UserHostAddress;
				myCommand.Parameters.Add("@IsSticky", SqlDbType.Bit).Value = galleryPost.IsSticky;
				myCommand.Parameters.Add("@StickyDate", SqlDbType.DateTime).Value = galleryPost.StickyDate;
				myCommand.Parameters.Add("@PostDate", SqlDbType.DateTime).Value = galleryPost.PostDate;
				myCommand.Parameters.Add("@PostName", SqlDbType.NVarChar,256).Value = galleryPost.Name;
                myCommand.Parameters.Add("@ApplicationPostType", SqlDbType.Int,4).Value = galleryPost.GalleryPostType;
                myCommand.Parameters.Add("@PostDisplayName",SqlDbType.NVarChar,64).Value = galleryPost.User.DisplayName;

                myCommand.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = galleryPost.IsApproved;

				myCommand.Parameters.Add(this.SettingsIDParameter());


				SerializerData data = galleryPost.GetSerializerData();

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

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

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

				// LN 5/27/04: try/catch added to get rid of exceptions
				try 
				{
					galleryPost.PostID = (int) myCommand.Parameters["@PostID"].Value;
					galleryPost.ThreadID = (int) myCommand.Parameters["@ThreadID"].Value;
				} 
				catch {}

				if (galleryPost.PostID == -1) 
				{
					myConnection.Close();
					throw new CSException(CSExceptionType.PostDuplicate);
				}

				myConnection.Close();
			}
		}


		public override void UpdatePicture(GalleryPost galleryPost, int editedByUserID)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Post_Update", myConnection);

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

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@PostID", SqlDbType.Int, 4).Value = galleryPost.PostID;
				myCommand.Parameters.Add("@Subject", SqlDbType.NVarChar, 256).Value = galleryPost.Subject;
				myCommand.Parameters.Add("@Body", SqlDbType.NText).Value = galleryPost.Body;
				myCommand.Parameters.Add("@FormattedBody", SqlDbType.NText).Value = galleryPost.FormattedBody;
				myCommand.Parameters.Add("@EmoticonID", SqlDbType.Int).Value = galleryPost.EmoticonID;
				myCommand.Parameters.Add("@IsLocked", SqlDbType.Bit).Value = galleryPost.IsLocked;
				myCommand.Parameters.Add("@EditedBy", SqlDbType.Int).Value = editedByUserID;
				myCommand.Parameters.Add("@EditNotes", SqlDbType.NText).Value = galleryPost.EditNotes;
				myCommand.Parameters.Add("@IsSticky", SqlDbType.Bit).Value = galleryPost.IsSticky;
				myCommand.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = galleryPost.IsApproved;
				myCommand.Parameters.Add("@StickyDate", SqlDbType.DateTime).Value = galleryPost.StickyDate;
				myCommand.Parameters.Add("@IsAnnouncement", SqlDbType.Bit).Value = false;
				myCommand.Parameters.Add("@PostName", SqlDbType.NVarChar,256).Value = galleryPost.Name;
				myCommand.Parameters.Add("@PostDate", SqlDbType.DateTime).Value = galleryPost.PostDate;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				SerializerData data = galleryPost.GetSerializerData();

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

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

		public override void DeletePicture(int sectionID, int threadID)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Thread_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("@ThreadID", SqlDbType.Int, 4).Value = threadID;
				myCommand.Parameters.Add(this.SettingsIDParameter());
                myCommand.Parameters.Add("@ResetStatistics", SqlDbType.Bit).Value = true;
                myCommand.Parameters.Add("@DeletedBy", SqlDbType.Int).Value = CSContext.Current.User.UserID;
                myCommand.Parameters.Add("@Reason", SqlDbType.NVarChar, 1024).Value = "";

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

		public override void DeletePost(int postID, int userID)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Moderate_DeletePost", myConnection);

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

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@PostID", SqlDbType.Int, 4).Value = postID;
				myCommand.Parameters.Add("@DeletedBy", SqlDbType.Int, 4).Value = userID;
				myCommand.Parameters.Add("@Reason", SqlDbType.NVarChar, 1024).Value = "Blogger Delete";
				myCommand.Parameters.Add("@DeleteChildPosts", SqlDbType.Bit).Value = true;
				myCommand.Parameters.Add(this.SettingsIDParameter());

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


		public override ThreadSet GetPictures(GalleryThreadQuery 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.NVarChar, 4000).Value = SqlGenerator.BuildGalleryThreadQuery(query, databaseOwner);
					command.Parameters.Add("@sqlPopulate", SqlDbType.NText).Value = new GalleryThreadQueryBuilder(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(PopulateGalleryPostFromDataRow(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(PopulateGalleryPostFromIDataRecord(dr));

							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;

				}
			}
		}


		public override GalleryPost GetPicture(int postID, int userID, bool includeCategories)
		{
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_forums_Post", myConnection);

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

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
				myCommand.Parameters.Add("@TrackViews", SqlDbType.Bit).Value = false;
				myCommand.Parameters.Add("@IncludeCategories", SqlDbType.Bit).Value = includeCategories;
				myCommand.Parameters.Add(this.SettingsIDParameter());

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

				if (!dr.Read()) 
				{
					dr.Close();
					myConnection.Close();
					// we did not get back a post
					throw new CSException(CSExceptionType.PostNotFound, postID.ToString());
				}

				GalleryPost p = PopulateGalleryPostFromIDataRecord(dr);

				if (includeCategories)
				{
					dr.NextResult();
					ArrayList categories = new ArrayList();

					while (dr.Read())
					{
						categories.Add(dr["Name"]);
					}

					p.Categories = (string[]) categories.ToArray(typeof(string));
				}

				dr.Close();
				myConnection.Close();


				// we have a post to work with  
				return p;
			}
		}

		public override int GetRandomPostID(int sectionID, int categoryID)
		{
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Post_GetRandomPostID", myConnection);
				int postID;

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

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = sectionID;
				myCommand.Parameters.Add("@CategoryID", SqlDbType.Int).Value = categoryID;
				myCommand.Parameters.Add(this.SettingsIDParameter());

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

				if(!dr.Read())
				{
					dr.Close();
					myConnection.Close();
					return -1;
				}

				postID = (int) dr["PostID"];

				dr.Close();
				myConnection.Close();


				// we have a post to work with  
				return postID;
			}
		}

		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())
						{
							GalleryPost post = GalleryDataProvider.PopulateGalleryPostFromIDataRecord(dr);
							//PopulateWeblogEntryFromIDataReader(dr,post);
							ps.Posts.Add(post);
						}
						
						dr.Close();
						return ps;
					}
					
					
				}
			}
		}

		public override void CreateComment(GalleryPost post) 
		{ 

			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Post_CreateUpdate", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add parameters
				//
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = post.SectionID;
				myCommand.Parameters.Add("@ThreadID", SqlDbType.Int).Value = post.ThreadID;
				myCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = post.ParentID;
				myCommand.Parameters.Add("@AllowDuplicatePosts", SqlDbType.Bit).Value = GalleryConfiguration.Instance().EnableDuplicatePosts;
				myCommand.Parameters.Add("@DuplicateIntervalInMinutes", SqlDbType.Int).Value = GalleryConfiguration.Instance().DuplicatePostIntervalInMinutes;
				myCommand.Parameters.Add("@Subject", SqlDbType.NVarChar, 256).Value = post.Subject;
				myCommand.Parameters.Add("@IsLocked", SqlDbType.Bit).Value = post.IsLocked;
				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 = post.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("@IsApproved", SqlDbType.Bit).Value = post.IsApproved;
				myCommand.Parameters.Add("@IsSticky", SqlDbType.Bit).Value = false;
				myCommand.Parameters.Add("@StickyDate", SqlDbType.DateTime).Value = DateTime.Now;
				myCommand.Parameters.Add("@UpdateSectionStatistics", SqlDbType.Bit).Value = false;
                myCommand.Parameters.Add("@PostName", SqlDbType.NVarChar,256).Value = post.Name;
                myCommand.Parameters.Add("@ApplicationPostType", SqlDbType.Int,4).Value = post.GalleryPostType;
                myCommand.Parameters.Add("@PostDisplayName",SqlDbType.NVarChar,64).Value = post.User.DisplayName;



				myCommand.Parameters.Add(this.SettingsIDParameter());

				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("@PostID", SqlDbType.Int).Direction = ParameterDirection.Output;

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

				// LN 5/27/04: try/catch added to get rid of exceptions
				try 
				{
					post.PostID = (int) myCommand.Parameters["@PostID"].Value;
				} 
				catch {}

				if (post.PostID == -1) 
				{
					myConnection.Close();
					throw new CSException(CSExceptionType.PostDuplicate);
				}

				myConnection.Close();
			}
		}

		public override ArrayList GetGalleryPermissions(int galleryID) 
		{
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Section_Permissions_Get", myConnection);
				ArrayList permissions = new ArrayList();
				SqlDataReader reader;

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

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

				myConnection.Open();

				reader = myCommand.ExecuteReader();

				while(reader.Read()) 
				{
					GalleryPermission perm = new GalleryPermission();
					CommonDataProvider.PopulatePermissionFromIDataReader(perm, reader);
					permissions.Add(perm);
				}

				myConnection.Close();

				return permissions;
			}
		}

		public override bool ApprovePost(int postID, int userIDApprovedBy) 
		{
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Moderate_ApprovePost", myConnection);

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

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;
				myCommand.Parameters.Add("@ApprovedBy", SqlDbType.Int).Value = userIDApprovedBy;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				int iResult = Convert.ToInt32(myCommand.ExecuteScalar().ToString());
				myConnection.Close();

				return iResult == 1;        // was the post previously approved?
			}
		}
		public override void UpdatePictureMetadata(int postID, Metadata metadata)
		{
							
			object metaDataXML = ConvertMetadataToXML(metadata);
			bool allowEncodedChars = GalleryConfiguration.Instance().AllowEncodedUnicodeCharsInMetadata;

			if(allowEncodedChars)
			{
				try{UpdatePictureMetadata(postID, metaDataXML);}
				catch{allowEncodedChars = false;} //System.Data.SqlClient.SqlException: XML parsing error: Invalid unicode character
			}

			if(!allowEncodedChars)
			{
				metaDataXML = metaDataXML.ToString().Replace("&","&amp;");
				UpdatePictureMetadata(postID, metaDataXML);
			}
		}

		private void UpdatePictureMetadata(int postID, object metaDataXML)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_PostMetadata_Update", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add parameters
				//
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;
				myCommand.Parameters.Add("@MetadataList", SqlDbType.NText).Value = metaDataXML;
				myCommand.Parameters.Add(this.SettingsIDParameter());
				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
		}

        public override Metadata GetPictureMetadata(int postID)
		{
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_PostMetadata_Get", myConnection);
                Metadata metadata = new Metadata();
				SqlDataReader reader;

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

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				myConnection.Open();

				reader = myCommand.ExecuteReader();

				while(reader.Read()) 
				{
					PopulateMetadataFromIDataRecord(reader, metadata);
				}

				myConnection.Close();

				return metadata;
			}
		}

		public override Hashtable GetAllPictures(int galleryID)
		{
			Hashtable pictures = new Hashtable();
			GalleryThreadQuery query = new GalleryThreadQuery();
			query.SectionID = galleryID;
			query.PageSize = int.MaxValue;
			query.PageIndex = 0;
			query.UserID = CSContext.Current.UserID;
			query.IncludeCategories = false;
			query.IncludePageIndex = false;
			
			//Only return the pictures
			query.ApplicationPostType = GalleryPostType.Image;

			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.NVarChar, 4000).Value = SqlGenerator.BuildGalleryThreadQuery(query, databaseOwner);
					command.Parameters.Add("@sqlPopulate", SqlDbType.NText).Value = new GalleryThreadQueryBuilder(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());

					connection.Open();
					using(SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection))
					{
						
						while(dr.Read())
						{
							GalleryPost galleryPost = GalleryDataProvider.PopulateGalleryPostFromIDataRecord(dr);
							pictures.Add(galleryPost.PostID, galleryPost);
						}

						dr.Close();
						connection.Close();
					}
				}
			}
			
			return pictures;
			
		}

		#region Search

//		public override PostSet SearchReindexPosts(int setsize, int settingsID)
//		{
//			PostSet postSet = new PostSet();
//
//			using(SqlConnection myConnection = GetSqlConnection()) 
//			{
//				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_gallery_Search_PostReindex", myConnection);
//				myCommand.CommandType = CommandType.StoredProcedure;
//				myCommand.Parameters.Add("@RowCount", SqlDbType.Int).Value = setsize;
//				myCommand.Parameters.Add("@SettingsID",SqlDbType.Int,4).Value = settingsID;
//                
//				myConnection.Open();
//				using(SqlDataReader dr = myCommand.ExecuteReader()) 
//				{
//					while(dr.Read())
//					{
//						GalleryPost galleryPost = PopulateGalleryPostFromIDataRecord(dr);
//
//						// Some search specific ones
//						galleryPost.PostLevel = (int)dr["PostLevel"];
//						galleryPost.Replies = (int)dr["Replies"];
//                        galleryPost.UserID  = (int)dr["UserID"];
//
//						postSet.Posts.Add(galleryPost);
//					}
//				}
//
//				// Close the connection
//				myConnection.Close();
//				return postSet;
//			}
//		}


		public override PostSet SearchReindexPosts (int setsize, int settingsID) 
		{
			GalleryThreadQuery query = new GalleryThreadQuery();
			query.FirstPageOnly = true;
			query.PageSize = setsize;
			query.IncludeCategories = true;
			query.PublishedFilter = GalleryPostPublishedFilter.Published;
			query.IncludePageIndex = true;
			query.ApplicationPostType = GalleryPostType.Image; //not indexing comments yet
			query.SortBy = GalleryThreadSortBy.PictureDate;
			query.SortOrder = SortOrder.Descending;
		
			ThreadSet ts = SearchIndexPosts(settingsID, query);

			PostSet postSet = new PostSet();
			foreach(GalleryPost p in ts.Threads)
				postSet.Posts.Add(p); 

			return postSet;
		}

		public override ThreadSet SearchIndexPosts (int setsize, GalleryThreadQuery 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 GalleryThreadQueryBuilder(query,databaseOwner).BuildQuery();
                    string appTypeFilter = string.Format("S.ApplicationType={0} and S.IsSearchable=1", (int)ApplicationType.Gallery); 
					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(PopulateGalleryPostFromDataRow(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(PopulateGalleryPostFromIDataRecord(dr));
							}

							//                            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 ArrayList GetRoleQuotas()
		{

			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_RoleQuotas_Get", myConnection);
				ArrayList roleQuotas = new ArrayList();
				GalleryRoleQuota roleQuota;

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

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

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

					// Populate the Hashtable
					while(dr.Read())
					{
						// Add categories
						roleQuota = PopulateGalleryRoleQuotaFromIDataReader(dr);
						roleQuotas.Add(roleQuota);
					}

					dr.Close();
					myConnection.Close();
				}

				// return the collection of quotas
				return roleQuotas;
			}
		}

		public override void CreateUpdateDeleteGalleryRoleQuota(GalleryRoleQuota quota, DataProviderAction action)
		{
			using(SqlConnection myConnection = GetSqlConnection()) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_RoleQuota_CreateUpdateDelete", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				myCommand.Parameters.Add("@RoleID", SqlDbType.UniqueIdentifier).Value = quota.RoleID;
				if(action!=DataProviderAction.Delete)
				{
					myCommand.Parameters.Add("@DiskQuota", SqlDbType.BigInt).Value = quota.DiskQuota;
					myCommand.Parameters.Add("@ImageQuota", SqlDbType.Int).Value = quota.ImageQuota;
				}
				myCommand.Parameters.Add(this.SettingsIDParameter());
                
				myConnection.Open();

				myCommand.ExecuteNonQuery();

				myConnection.Close();
			}
		}


		#region GetPostSet

		/// <summary>
		/// Returns an instance of PostSet based on the GalleryThreadQuery
		/// </summary>
		public override PostSet GetPosts(GalleryThreadQuery 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())
						{
							GalleryPost entry = PopulateGalleryPostFromIDataRecord(reader);
                            
							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 = PopulateGalleryPostFromIDataRecord(reader);

								ps.Posts.Add(entry);
							}

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

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

					connection.Close();

					return ps;

				}
			}
		}

		#endregion

	}
}
