// AdminUtility.cs
//

namespace ASPNET.StarterKit.Communities.Admin 
{
    using System;
    using System.Collections;
    using System.Web;
    using System.Data;
    using System.Data.SqlClient;
    using System.IO;
    using System.Web.UI.WebControls;
    using System.Text.RegularExpressions;
    using ASPNET.StarterKit.Communities;
    using ASPNET.StarterKit.Communities.Services;
      

    /// <summary>
    /// Contains all the methods used by the Admin pages.
    /// </summary>
	public class AdminUtility 
	{
    
		/// <summary>
		/// Make constructor private for static class.
		/// </summary>
		private AdminUtility(){}



		// *********************************************************************
		//  Edit Site and Section Methods
		// *********************************************************************


       
		/// <summary>
		/// Retrieves a list of all the Sections that can be Parent Sections. 
		/// </summary>
		public static DataSet GetParentSections()
		{
			SqlDataAdapter dadSectionParents = 
				new SqlDataAdapter( "Community_AdminGetParentSections", CommunityGlobals.ConnectionString );
			dadSectionParents.SelectCommand.CommandType = CommandType.StoredProcedure;
			DataSet dstSectionParents = new DataSet();
			dadSectionParents.Fill( dstSectionParents );
			return dstSectionParents;
		}
        

		/// <summary>
		/// Retrieves a list of all the Sections that can be Parent Sections. 
		/// </summary>
		public static DataRow GetSectionFromDB(int sectionID)
		{
			SqlDataAdapter dadSection = 
				new SqlDataAdapter( "Community_AdminGetSection", CommunityGlobals.ConnectionString );
			dadSection.SelectCommand.CommandType = CommandType.StoredProcedure;
			dadSection.SelectCommand.Parameters.Add( "@sectionID", sectionID );
			DataSet dstSection = new DataSet();
			dadSection.Fill( dstSection );
			return dstSection.Tables[0].Rows[0];
		}

       
        
		/// <summary>
		/// Adds a new section to the portal.
		/// </summary>


		/// <summary>
		/// Edits an existing portal section.
		/// </summary>

		public static void DeleteSection(int sectionID)
		{
			HttpContext.Current.Cache.Remove( CommunityGlobals.CacheKey("Sections") );
			SqlConnection conPortal = new SqlConnection( CommunityGlobals.ConnectionString );
			SqlCommand cmdDelete = new SqlCommand( "Community_AdminDeleteSection", conPortal );
			cmdDelete.CommandType = CommandType.StoredProcedure;
            
			cmdDelete.Parameters.Add( "@sectionID", sectionID );
			conPortal.Open();
			cmdDelete.ExecuteNonQuery();
			conPortal.Close();
		}        

        
		public static void MoveSectionUp(int sectionID)
		{
			HttpContext.Current.Cache.Remove( CommunityGlobals.CacheKey("Sections") );
			SqlConnection conPortal = new SqlConnection( CommunityGlobals.ConnectionString );
			SqlCommand cmdMoveUp = new SqlCommand( "Community_AdminMoveSectionUp", conPortal );
			cmdMoveUp.CommandType = CommandType.StoredProcedure;
            
			cmdMoveUp.Parameters.Add( "@sectionID", sectionID );
			conPortal.Open();
			cmdMoveUp.ExecuteNonQuery();
			conPortal.Close();
		}
        

		public static void MoveSectionDown(int sectionID)
		{
			HttpContext.Current.Cache.Remove( CommunityGlobals.CacheKey("Sections") );
			SqlConnection conPortal = new SqlConnection( CommunityGlobals.ConnectionString );
			SqlCommand cmdMoveDown = new SqlCommand( "Community_AdminMoveSectionDown", conPortal );
			cmdMoveDown.CommandType = CommandType.StoredProcedure;
            
			cmdMoveDown.Parameters.Add( "@sectionID", sectionID );
			conPortal.Open();
			cmdMoveDown.ExecuteNonQuery();
			conPortal.Close();
		}


		public static void MakeSectionHome(int sectionID) 
		{
			HttpContext.Current.Cache.Remove( CommunityGlobals.CacheKey("Sections") );
			SqlConnection conPortal = new SqlConnection( CommunityGlobals.ConnectionString );
			SqlCommand cmd = new SqlCommand( "Community_SectionsMakeHome", conPortal );
			cmd.CommandType = CommandType.StoredProcedure;
            
			cmd.Parameters.Add( "@communityID", CommunityGlobals.CommunityID );
			cmd.Parameters.Add( "@sectionID", sectionID );
			conPortal.Open();
			cmd.ExecuteNonQuery();
			conPortal.Close();
		}


		public static int ValidateTransformations(ServerValidateEventArgs e)
		{
			// Assume the worst
			e.IsValid = false;
        
			// Split the transformation
			string[] splitTrans = Regex.Split(e.Value.Trim(), "\n");
            
			// Check if even number
			if ( (splitTrans.Length + 1) % 2 == 0 )
				return 1;

			// Make sure that to: follows from:
			for (int i=0;i<splitTrans.Length;i+=2)
			{
				if (!splitTrans[i].ToLower().StartsWith("from:"))
					return 2;

				if (!splitTrans[i+1].ToLower().StartsWith("to:")) 
					return 2;
			}
            
            
			// Things worked out after all, return 0            
			e.IsValid = true;
			return 0;
		}


		// *********************************************************************
		//  Edit Topics Methods
		// *********************************************************************


		public static void EnableTopicMenu(bool displayTopicMenu)
		{
			SqlConnection con = new SqlConnection( CommunityGlobals.ConnectionString );
			SqlCommand cmd = new SqlCommand( "Community_AdminEnableTopicMenu", con );
			cmd.CommandType = CommandType.StoredProcedure;
            
			cmd.Parameters.Add( "@communityID", CommunityGlobals.CommunityID);
			cmd.Parameters.Add( "@displayTopicMenu", displayTopicMenu);
			con.Open();
			cmd.ExecuteNonQuery();
			con.Close();
			CommunityUtility.ClearCommunityCache();
		}        





		public static void DeleteTopic(int topicID)
		{
			SqlConnection conPortal = new SqlConnection( CommunityGlobals.ConnectionString );
			SqlCommand cmdDelete = new SqlCommand( "Community_AdminDeleteTopic", conPortal );
			cmdDelete.CommandType = CommandType.StoredProcedure;
            
			cmdDelete.Parameters.Add( "@topicID", topicID );
			conPortal.Open();
			cmdDelete.ExecuteNonQuery();
			conPortal.Close();

			// Clear Cached topics
			TopicUtility.ClearCache();
		}        


		public static void AddTopic(string topicName, string topicDescription, HttpPostedFile proposedFile)
		{
			// Add Topic image
			string imageFileName = ImageUtility.AddCommunityImage(CommunityImageType.Topic, proposedFile);

			// Add Topic to database
			SqlConnection conPortal = new SqlConnection( CommunityGlobals.ConnectionString );
			SqlCommand cmdAdd = new SqlCommand( "Community_AdminAddTopic", conPortal );
			cmdAdd.CommandType = CommandType.StoredProcedure;
            
			cmdAdd.Parameters.Add( "@communityID", CommunityGlobals.CommunityID );
			cmdAdd.Parameters.Add( "@topicName", topicName );
			cmdAdd.Parameters.Add( "@topicDescription", topicDescription );
			cmdAdd.Parameters.Add( "@topicImage", imageFileName);
            
			conPortal.Open();
			cmdAdd.ExecuteNonQuery();
			conPortal.Close();

			// Clear Cached topics
			TopicUtility.ClearCache();
		}        


		public static void EditTopic(int topicID, bool removeImage, string topicName, string topicDescription, HttpPostedFile proposedFile)
		{

			// Add image to database
			string imageFileName = 	ImageUtility.AddCommunityImage(CommunityImageType.Global, proposedFile );


			SqlConnection conPortal = new SqlConnection( CommunityGlobals.ConnectionString );
			SqlCommand cmdEdit = new SqlCommand( "Community_AdminEditTopic", conPortal );
			cmdEdit.CommandType = CommandType.StoredProcedure;
			cmdEdit.Parameters.Add( "@communityID", CommunityGlobals.CommunityID );
			cmdEdit.Parameters.Add( "@topicID", topicID );
			cmdEdit.Parameters.Add( "@topicName", topicName );
			cmdEdit.Parameters.Add( "@topicDescription", topicDescription );
			cmdEdit.Parameters.Add( "@topicImage", imageFileName);
			cmdEdit.Parameters.Add( "@removeImage", removeImage);
            
			conPortal.Open();
			cmdEdit.ExecuteNonQuery();
			conPortal.Close();


			// Clear Cached topics
			TopicUtility.ClearCache();
		}        







		// *********************************************************************
		//  Edit Users Methods
		// *********************************************************************

		public static int GetTotalUsersForLetter(string letter)
		{
			if (letter == "All")
				letter = "0";
                
			SqlConnection conPortal = new SqlConnection( CommunityGlobals.ConnectionString );
			SqlCommand cmdGet = new SqlCommand( "Community_GetTotalUsersForLetter", conPortal );
			cmdGet.CommandType = CommandType.StoredProcedure;
            
			cmdGet.Parameters.Add( "@communityID", CommunityGlobals.CommunityID );
			cmdGet.Parameters.Add( "@letter", letter);
			conPortal.Open();
			int result = (int)cmdGet.ExecuteScalar();
			conPortal.Close();
			return result;
		}


	


		public static DataSet GetUsersForLetter(string letter, int sortBy, int orderBy) 
		{
			if (letter == "All")
				letter = "";
                
			SqlDataAdapter dadGet = new SqlDataAdapter( "Community_AdminGetUsersForLetter", CommunityGlobals.ConnectionString);
			dadGet.SelectCommand.CommandType = CommandType.StoredProcedure;
			dadGet.SelectCommand.Parameters.Add("@communityID", CommunityGlobals.CommunityID);
			dadGet.SelectCommand.Parameters.Add("@letter", letter);
			dadGet.SelectCommand.Parameters.Add("@sortBy", sortBy);
			dadGet.SelectCommand.Parameters.Add("@orderBy", orderBy);
			DataSet dstUsers = new DataSet();
			dadGet.Fill(dstUsers);
			return dstUsers;
		}


		// *********************************************************************
		//  Edit Messages Methods
		// *********************************************************************
        
		public static SqlDataReader GetMessageNames()
		{
			SqlConnection conPortal = new SqlConnection( CommunityGlobals.ConnectionString );
			SqlCommand cmdGet = new SqlCommand( "Community_AdminGetMessageNames", conPortal );
			cmdGet.CommandType = CommandType.StoredProcedure;

			cmdGet.Parameters.Add("@communityID", CommunityGlobals.CommunityID);
            
			conPortal.Open();
			SqlDataReader dr = cmdGet.ExecuteReader(CommandBehavior.CloseConnection);
			return dr;
		}

		public static SqlDataReader GetMessage(string messageName)
		{
			SqlConnection conPortal = new SqlConnection( CommunityGlobals.ConnectionString );
			SqlCommand cmdGet = new SqlCommand( "Community_AdminGetMessage", conPortal );
			cmdGet.CommandType = CommandType.StoredProcedure;
			cmdGet.Parameters.Add("@communityID", CommunityGlobals.CommunityID);
			cmdGet.Parameters.Add("@messageName", messageName);
            
			conPortal.Open();
			SqlDataReader dr = cmdGet.ExecuteReader(CommandBehavior.CloseConnection);
			return dr;
		}
        
		public static void EditMessage(string messageName, string messageTitle, string messageBody)
		{
			SqlConnection conPortal = new SqlConnection( CommunityGlobals.ConnectionString );
			SqlCommand cmdEdit = new SqlCommand( "Community_AdminEditMessage", conPortal );
			cmdEdit.CommandType = CommandType.StoredProcedure;