using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Web;
using System.Web.Caching;
using ASPNET.StarterKit.Communities;

namespace ASPNET.StarterKit.Communities {
	/// <summary>
	/// Sort direction for managing poll choices.
	/// </summary>
	public enum PollChoiceDirection {
		Up,
		Down
	}

	/// <summary>
	/// Utility class for all functions related to the Voting WebBox.
	/// </summary>
	public class VotingUtility {
		// Default cache timeout.
		// Caches are generally updated in realtime when the data is changed.
		private static readonly int DEFAULT_CACHE_TIMEOUT=20;

		/// <summary>
		/// Grabs an available Poll, if any, for the end-user to be 
		/// rendered as a WebBox.
		/// </summary>
		/// <remarks>Use <see cref="GetPolls"/> for retrieving all polls.</remarks>
		public static PollDetails GetPollForWebBox(int Community_ID, int Section_ID, string Username) {
			SqlConnection myConnection = new SqlConnection(CommunityGlobals.ConnectionString);
			SqlCommand myCommand = new SqlCommand("Community_VotingGetPollsForWebBox",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			SqlParameter myParam;

			myParam = new SqlParameter("@InCommunity_ID",SqlDbType.Int);
			myParam.Value=Community_ID;
			myCommand.Parameters.Add(myParam);

			myParam = new SqlParameter("@InSection_ID",SqlDbType.Int);
			myParam.Value=Section_ID;
			myCommand.Parameters.Add(myParam);

			myParam = new SqlParameter("@InUsername",SqlDbType.NVarChar,50);
			myParam.IsNullable=true;
			myParam.Value=Username;
			myCommand.Parameters.Add(myParam);

			myConnection.Open();
			SqlDataReader myReader = myCommand.ExecuteReader();
			ArrayList PollList = new ArrayList();
			while(myReader.Read()) {
				PollList.Add(myReader.GetInt32(0));
			}
			myConnection.Close();

			if (PollList.Count>0) {
				// We have some Polls to randomly choose from	
				Random rnd = new Random();
				int PollIndex = rnd.Next(0,PollList.Count);
				return GetPollForWebBox((int)PollList[PollIndex]);
			} else {
				// There are no Polls available for this user or section.
				return null;
			}
		}

		/// <summary>
		/// Use for getting a specific poll for the Voting Control WebBox
		/// </summary>
		public static PollDetails GetPollForWebBox(int Poll_ID) {
			DataRow poll = GetPoll(Poll_ID);
			DataTable pollChoices = VotingUtility.GetPollChoices(Poll_ID);
			ChoiceCollection choices = new ChoiceCollection();
			int i=0;
			foreach (DataRow row in pollChoices.Rows) {
				choices.Add((int)row["PollChoice_ID"],new ChoiceInfo((string)row["choice"],i,(int)row["PollChoice_ID"]));
				i++;
			}
			// Return PollDetails to the calling object.
			return new PollDetails(Poll_ID,(string)poll["PollQuestion"],choices,(bool)poll["DisplayResultsToPublic"]);
		}

		/// <summary>
		/// Returns a single Poll record.
		/// </summary>
		public static DataRow GetPoll(int Poll_ID) {
			return GetPollCache(Poll_ID);
		}

		/// <summary>
		/// Updates the sort order or a PollChoice
		/// </summary>
		public static void UpdatePollChoiceSortOrder(int PollChoice_ID, PollChoiceDirection Direction) {
			SqlConnection myConnection = new SqlConnection(CommunityGlobals.ConnectionString);
			SqlCommand myCommand = new SqlCommand("Community_VotingPollChoicesUpdateSortOrder",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			SqlParameter myParam;

			myParam = new SqlParameter("@InPollChoice_ID",SqlDbType.Int);
			myParam.Value=PollChoice_ID;
			myCommand.Parameters.Add(myParam);

			myParam = new SqlParameter("@InDirection",SqlDbType.Bit);
			if (Direction==PollChoiceDirection.Up)
				myParam.Value=1;
			else
				myParam.Value=0;
			myCommand.Parameters.Add(myParam);

			myParam = new SqlParameter("@OutPoll_ID",SqlDbType.Int);
			myParam.Direction=ParameterDirection.Output;
			myCommand.Parameters.Add(myParam);

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

			// Update Cache
			UpdatePollChoiceCache((int)myCommand.Parameters["@OutPoll_ID"].Value);
		}

		/// <summary>
		/// Returns all Polls for administration purposes.
		/// </summary>
		public static SqlDataReader GetPolls(int Community_ID) {
			SqlConnection myConnection = new SqlConnection(CommunityGlobals.ConnectionString);
			SqlCommand myCommand = new SqlCommand("Community_VotingGetPolls",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			SqlParameter myParam;

			myParam = new SqlParameter("@InCommunity_ID",SqlDbType.Int);
			myParam.Value=Community_ID;
			myCommand.Parameters.Add(myParam);

			myConnection.Open();
			return myCommand.ExecuteReader(CommandBehavior.CloseConnection);
		}

		public static DataTable GetPollChoices(int Poll_ID) {
			return GetPollChoiceCache(Poll_ID);
		}

		/// <summary>
		/// Returns all Roles allowed to view a given Poll.
		/// </summary>
		public static string[] GetPollRoles(int Poll_ID) {
		    ArrayList colRoles = new ArrayList();
		
			SqlConnection myConnection = new SqlConnection(CommunityGlobals.ConnectionString);
			SqlCommand myCommand = new SqlCommand("Community_VotingGetPollRoles",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			SqlParameter myParam;

			myParam = new SqlParameter("@InPoll_ID",SqlDbType.Int);
			myParam.Value=Poll_ID;
			myCommand.Parameters.Add(myParam);

			myConnection.Open();
			SqlDataReader dr = myCommand.ExecuteReader();
			while (dr.Read())
			 colRoles.Add((string)dr["RoleName"]);;
		    myConnection.Close();
		    if (colRoles.Count == 0)
		      colRoles.Add("Community-Everyone");

		    return (string[])colRoles.ToArray(typeof(string));
		}

		/// <summary>
		/// Returns all Sections allowed to view a given Poll.
		/// </summary>
		public static string[] GetPollSections(int Poll_ID) {
			ArrayList colSections = new ArrayList();
			
			SqlConnection myConnection = new SqlConnection(CommunityGlobals.ConnectionString);
			SqlCommand myCommand = new SqlCommand("Community_VotingGetPollSections",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			SqlParameter myParam;

			myParam = new SqlParameter("@InPoll_ID",SqlDbType.Int);
			myParam.Value=Poll_ID;
			myCommand.Parameters.Add(myParam);
			
			myConnection.Open();
			SqlDataReader dr = myCommand.ExecuteReader();
			while (dr.Read())
			 colSections.Add((string)dr["section_name"]);
		    myConnection.Close();
		    return (string[])colSections.ToArray(typeof(string));
		}

		/// <summary>
		/// Returns the number of votes for each PollChoice in a given Poll.
		/// </summary>
		public static Hashtable GetPollResults(int Poll_ID) {
			SqlConnection myConnection = new SqlConnection(CommunityGlobals.ConnectionString);
			SqlCommand myCommand = new SqlCommand("Community_VotingGetPollResults",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			SqlParameter myParam;

			myParam = new SqlParameter("@InPoll_ID",SqlDbType.Int);
			myParam.Value=Poll_ID;
			myCommand.Parameters.Add(myParam);
			
			myConnection.Open();
			SqlDataReader myReader = myCommand.ExecuteReader();
			Hashtable results = new Hashtable();
			while (myReader.Read()) {
				int amount=0;
				// If there are no votes (NULL) we default to 0
				if (!myReader.IsDBNull(1))
					amount=myReader.GetInt32(1);
				results.Add(myReader.GetInt32(0),amount);
			}
			myConnection.Close();
			return results;
		}

		/// <summary>
		/// Return a users vote for a given Poll.  
		/// </summary>
		/// <remarks>Only use for authenticated users.  Anonymous users are all lumped under one username.</remarks>
		public static ChoiceInfo GetUsersChoice(int Poll_ID, string Username) {
			SqlConnection myConnection = new SqlConnection(CommunityGlobals.ConnectionString);
			SqlCommand myCommand = new SqlCommand("Community_VotingGetUsersChoice",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			SqlParameter myParam;

			myParam = new SqlParameter("@InPoll_ID",SqlDbType.Int);
			myParam.Value=Poll_ID;
			myCommand.Parameters.Add(myParam);

			myParam = new SqlParameter("@InUsername",SqlDbType.NVarChar,50);
			myParam.Value=Username;
			myCommand.Parameters.Add(myParam);

			myParam = new SqlParameter("@OutPollChoice_ID",SqlDbType.Int);
			myParam.Direction=ParameterDirection.Output;
			myCommand.Parameters.Add(myParam);

			myParam = new SqlParameter("@OutChoice",SqlDbType.NVarChar,100);
			myParam.Direction=ParameterDirection.Output;
			myCommand.Parameters.Add(myParam);
			
			myConnection.Open();
			myCommand.ExecuteNonQuery();
			myConnection.Close();

			ChoiceInfo choice=new ChoiceInfo(String.Empty,0,0);
			if (!Convert.IsDBNull(myCommand.Parameters["@OutPollChoice_ID"].Value)) {
				choice.PollChoice_ID=(int)myCommand.Parameters["@OutPollChoice_ID"].Value;
				choice.Choice=(string)myCommand.Parameters["@OutChoice"].Value;
			}
			return choice;
		}

		/// <summary>
		/// Create's a new poll.
		/// </summary>
		public static int CreatePoll(int Community_ID, string PollQuestion, DateTime StartDate, DateTime EndDate, bool IsGlobal, bool DisplayResultsToPublic, bool IsActive) {
			SqlConnection myConnection = new SqlConnection(CommunityGlobals.ConnectionString);
			SqlCommand myCommand = new SqlCommand("Community_VotingPollsInsert",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			SqlParameter myParam;

			myParam = new SqlParameter("@InCommunity_ID",SqlDbType.Int);
			myParam.Value=Community_ID;
			myCommand.Parameters.Add(myParam);

			myParam = new SqlParameter("@InPollQuestion",SqlDbType.NVarChar,255);
			myParam.Value=PollQuestion;
			myCommand.Parameters.Add(myParam);

			myParam = new SqlParameter("@InStartDate",SqlDbType.DateTime);
			if (StartDate.Equals(DateTime.MinValue))
				myParam.Value=DBNull.Value;
			else
				myParam.Value=StartDate;
			myCommand.Parameters.Add(myParam);

			myParam = new SqlParameter("@InEndDate",SqlDbType.DateTime);
			if (EndDate.Equals(DateTime.MaxValue))
				myParam.Value=DBNull.Value;
			else
				myParam.Value=EndDate;
			myCommand.Parameters.Add(myParam);
			
			myParam = new SqlParameter("@InIsGlobal",SqlDbType.Bit);
			myParam.Value=IsGlobal?1:0;
			myCommand.Parameters.Add(myParam);

			myParam = new SqlParameter("@InDisplayResultsToPublic",SqlDbType.Bit);
			myParam.Value=DisplayResultsToPublic?1:0;
			myCommand.Parameters.Add(myParam);

			myParam = new SqlParameter("@InIsActive",SqlDbType.Bit);
			myParam.Value=IsActive?1:0;
			myCommand.Parameters.Add(myParam);

			myParam = new SqlParameter("@OutPoll_ID",SqlDbType.Int);
			myParam.Direction=ParameterDirection.Output;
			myCommand.Parameters.Add(myParam);

			myConnection.Open();
			myCommand.ExecuteNonQuery();
			myConnection.Close();
			
			// Update Poll Cache
			UpdatePollCache((int)myCommand.Parameters["@OutPoll_ID"].Value);

			return (int)myCommand.Parameters["@OutPoll_ID"].Value;
		}

	
		/// <summary>
		/// Updates an existing Poll.
		/// </summary>
		public static void UpdatePoll(int Poll_ID, string PollQuestion, DateTime StartDate, DateTime EndDate, bool IsGlobal, bool DisplayResultsToPublic, bool IsActive) {
			SqlConnection myConnection = new SqlConnection(CommunityGlobals.ConnectionString);
			SqlCommand myCommand = new SqlCommand("Community_VotingPollsUpdate",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			SqlParameter myParam;

			myParam = new SqlParameter("@InPoll_ID",SqlDbType.Int);
			myParam.Value=Poll_ID;
			myCommand.Parameters.Add(myParam);

			myParam = new SqlParameter("@InPollQuestion",SqlDbType.NVarChar,255);
			myParam.Value=PollQuestion;
			myCommand.Parameters.Add(myParam);

			myParam = new SqlParameter("@InStartDate",SqlDbType.DateTime);
			if (StartDate.Equals(DateTime.MinValue))