using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;

namespace Reservation
{
	/// <summary>
	/// Booking form.
	/// Methods created or modified:<ul>
	/// <li>Page_Load</li>
	/// <li>bind</li>
	/// <li>dgBind</li>
	/// <li>update</li>
	/// <li>remove</li>
	/// <li>delete</li>
	/// <li>OnCalSelect</li>
	/// <li>OnBook</li>
	/// <li>OnCancel</li>
	/// <li>dgBookings_cmds</li></ul>
	/// <p>Copyright (c) 2002 Alexis Grandemange<br/>
	/// Mail: alexis.grandemange@pagebox.net</p>
	/// <pre>This program is free software; you can redistribute it and/or
	/// modify it under the terms of the GNU Lesser General Public
	/// License as published by the Free Software Foundation; version
	/// 2.1 of the License.
	/// This library is distributed in the hope that it will be useful,
	/// but WITHOUT ANY WARRANTY; without even the implied warranty of
	/// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
	/// GNU Lesser General Public License for more details.
	/// A copy of the GNU Lesser General Public License lesser.txt should be
	/// included in the distribution.</pre>
	/// </summary>
	public class Book : System.Web.UI.Page
	{
		protected System.Web.UI.WebControls.CheckBox H800;
		protected System.Web.UI.WebControls.CheckBox H900;
		protected System.Web.UI.WebControls.CheckBox H1000;
		protected System.Web.UI.WebControls.CheckBox H1100;
		protected System.Web.UI.WebControls.CheckBox H1200;
		protected System.Web.UI.WebControls.CheckBox H1300;
		protected System.Web.UI.WebControls.CheckBox H1400;
		protected System.Web.UI.WebControls.CheckBox H1500;
		protected System.Web.UI.WebControls.CheckBox H1600;
		protected System.Web.UI.WebControls.CheckBox H1700;
		protected System.Web.UI.WebControls.CheckBox H1800;
		protected System.Web.UI.WebControls.CheckBox H830;
		protected System.Web.UI.WebControls.CheckBox H930;
		protected System.Web.UI.WebControls.CheckBox H1030;
		protected System.Web.UI.WebControls.CheckBox H1130;
		protected System.Web.UI.WebControls.CheckBox H1230;
		protected System.Web.UI.WebControls.CheckBox H1330;
		protected System.Web.UI.WebControls.CheckBox H1430;
		protected System.Web.UI.WebControls.CheckBox H1530;
		protected System.Web.UI.WebControls.CheckBox H1630;
		protected System.Web.UI.WebControls.CheckBox H1730;
		protected System.Web.UI.WebControls.CheckBox H1830;
		protected System.Web.UI.WebControls.Button btnBook;
		protected System.Web.UI.WebControls.Calendar CalBook;
		protected System.Web.UI.WebControls.Button btnUnbook;
		protected System.Web.UI.WebControls.Label lblStatus;
		protected System.Web.UI.WebControls.DataGrid dgBookings;
		/// <summary>
		/// Logged user.
		/// </summary>
		private string id;
		/// <summary>
		/// Table of database schedule columns.
		/// First column: hour, second column: minutes.
		/// Used in OnCancel and dgBookings_cmds.
		/// </summary>
		string[,] cols = null;
	
		/// <summary>
		/// Constructor. Populates the cols schedule table.
		/// </summary>
		public Book()
		{
			Page.Init += new System.EventHandler(Page_Init);
			cols = new string[,] {
							  { "h800", "h830" },
							  { "h900", "h930" },
							  { "h1000", "h1030" },
							  { "h1100", "h1130" },
							  { "h1200", "h1230" },
							  { "h1300", "h1330" },
							  { "h1400", "h1430" },
							  { "h1500", "h1530" },
							  { "h1600", "h1630" },
							  { "h1700", "h1730" },
							  { "h1800", "h1830" }
								 };
		}

		/// <summary>
		/// Security check: user in user type. 
		/// </summary>
		private void Page_Load(object sender, System.EventArgs e)
		{
			id = (string)Context.Session["userid"];
			if (id == null) 
			{
				Context.Session.Add("from", "Book.aspx");
				Context.Response.Redirect("Login.aspx");
				return;
			}
			short type = (short)Context.Session["type"];
			if (type > 1) 
			{
				Context.Session.Add("status", "Log-in with a customer or proxy user id");
				Context.Session.Add("from", "Book.aspx");
				Context.Response.Redirect("Login.aspx");
				return;
			}
			if (!IsPostBack) 
			{
				OleDbConnection odc = new OleDbConnection(Global.connectionString);
				odc.Open();
				dgBind(odc);
				odc.Close();
			}
		}

		private void Page_Init(object sender, EventArgs e)
		{
			//
			// CODEGEN: This call is required by the ASP.NET Web Form Designer.
			//
			InitializeComponent();
		}

		#region Web Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{    
			this.dgBookings.ItemCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.dgBookings_cmds);
			this.btnBook.Click += new System.EventHandler(this.OnBook);
			this.btnUnbook.Click += new System.EventHandler(this.OnCancel);
			this.CalBook.SelectionChanged += new System.EventHandler(this.OnCalSelect);
			this.Load += new System.EventHandler(this.Page_Load);

		}
		#endregion

		/// <summary>
		/// Populates an array of DgRows with the user schedule date and time.
		/// Binds the array to the DataGrid.
		/// </summary>
		/// <param name="odc">Database connection</param>
		private void dgBind(OleDbConnection odc) 
		{
			OleDbCommand odcmd = new OleDbCommand("select day, hour, minute from bookings where id='" +
				id.PadRight(10, ' ') + "' and day>=" + DateTime.Today.ToFileTime(), odc);
			OleDbDataReader odr = odcmd.ExecuteReader();
			ArrayList al = new ArrayList();
			while(odr.Read()) 
			{
				decimal d = (decimal)odr["day"];
				DgRow dr = new DgRow((long)d, (short)odr["hour"], (short)odr["minute"]);
				al.Add(dr);
			}
			odr.Close();
			dgBookings.DataSource = al;
			dgBookings.DataBind();
		}

		/// <summary>
		/// Event fired when the user selects a date on the calendar.
		/// </summary>
		private void OnCalSelect(object sender, System.EventArgs e)
		{
			DateTime dt = CalBook.SelectedDate;
			OleDbConnection odc = new OleDbConnection(Global.connectionString);
			odc.Open();
			bind(dt.ToFileTime(), odc);
			odc.Close();
			btnBook.Enabled = true;
			btnUnbook.Enabled = true;
		}

		/// <summary>
		/// Called by OnCalSelect, OnBook, OnCancel.
		/// Populate schedule from the resources table.
		/// </summary>
		/// <param name="ticks">Tick number (time)</param>
		/// <param name="odc">Database connection</param>
		private void bind(long ticks, OleDbConnection odc) 
		{
			dgBind(odc);
			H800.Enabled = false;
			H830.Enabled = false;
			H900.Enabled = false;
			H930.Enabled = false;
			H1000.Enabled = false;
			H1030.Enabled = false;
			H1100.Enabled = false;
			H1130.Enabled = false;
			H1200.Enabled = false;
			H1230.Enabled = false;
			H1300.Enabled = false;
			H1330.Enabled = false;
			H1400.Enabled = false;
			H1430.Enabled = false;
			H1500.Enabled = false;
			H1530.Enabled = false;
			H1600.Enabled = false;
			H1630.Enabled = false;
			H1700.Enabled = false;
			H1730.Enabled = false;
			H1800.Enabled = false;
			H1830.Enabled = false;
			OleDbCommand odcmd = new OleDbCommand(
				"select * from resources where id='available ' and time=" + ticks, odc); 
			OleDbDataReader odr = odcmd.ExecuteReader();
			if (odr.Read())
			{
				short h = (short)odr["h800"];
				if (h > 0)
					H800.Enabled = true;
				h = (short)odr["h830"];
				if (h > 0)
					H830.Enabled = true;
				h = (short)odr["h900"];
				if (h > 0)
					H900.Enabled = true;
				h = (short)odr["h930"];
				if (h > 0)
					H930.Enabled = true;
				h = (short)odr["h1000"];
				if (h > 0)
					H1000.Enabled = true;
				h = (short)odr["h1030"];
				if (h > 0)
					H1030.Enabled = true;
				h = (short)odr["h1100"];
				if (h > 0)
					H1100.Enabled = true;
				h = (short)odr["h1130"];
				if (h > 0)
					H1130.Enabled = true;
				h = (short)odr["h1200"];
				if (h > 0)
					H1200.Enabled = true;
				h = (short)odr["h1230"];
				if (h > 0)
					H1230.Enabled = true;
				h = (short)odr["h1300"];
				if (h > 0)
					H1300.Enabled = true;
				h = (short)odr["h1330"];
				if (h > 0)
					H1330.Enabled = true;
				h = (short)odr["h1400"];
				if (h > 0)
					H1400.Enabled = true;
				h = (short)odr["h1430"];
				if (h > 0)
					H1430.Enabled = true;
				h = (short)odr["h1500"];
				if (h > 0)
					H1500.Enabled = true;
				h = (short)odr["h1530"];
				if (h > 0)
					H1530.Enabled = true;
				h = (short)odr["h1600"];
				if (h > 0)
					H1600.Enabled = true;
				h = (short)odr["h1630"];
				if (h > 0)
					H1630.Enabled = true;
				h = (short)odr["h1700"];
				if (h > 0)
					H1700.Enabled = true;
				h = (short)odr["h1730"];
				if (h > 0)
					H1730.Enabled = true;
				h = (short)odr["h1800"];
				if (h > 0)
					H1800.Enabled = true;
				h = (short)odr["h1830"];
				if (h > 0)
					H1830.Enabled = true;
			}
			odr.Close();
			CheckBox[,] cbs = new CheckBox[,] {
							  { H800, H830 },
							  { H900, H930 },
							  { H1000, H1030 },
							  { H1100, H1130 },
							  { H1200, H1230 },
							  { H1300, H1330 },
							  { H1400, H1430 },
							  { H1500, H1530 },
							  { H1600, H1630 },
							  { H1700, H1730 },
							  { H1800, H1830 }
											  };
			for (int i = 0; i < 11; ++i) 
			{
				for (int j = 0; j < 2; ++j)
					cbs[i, j].Checked = false;
			}
			odcmd.CommandText = "select hour, minute from bookings where id='" + id.PadRight(10, ' ') + 
				"' and day=" + ticks; 
			odr = odcmd.ExecuteReader();
			if (odr.Read())
			{
				short h = (short)odr["hour"];
				short mn = (short)odr["minute"];
				cbs[h - 8, mn / 30].Checked = true;
			}
			odr.Close();
		}

		/// <summary>
		/// Event fired when the user clicks on the Book button.
		/// Calls update.
		/// </summary>
		private void OnBook(object sender, System.EventArgs e)
		{
			DateTime dt = CalBook.SelectedDate;
			long ticks = dt.ToFileTime();
			OleDbConnection odc = new OleDbConnection(Global.connectionString);
			odc.Open();
			OleDbTransaction odt = odc.BeginTransaction();
			bool rc = false;
			if (H800.Checked) 
				rc = update("8, 0", "h800", odc, odt, ticks);
			else
				rc = remove(8, 0, "h800", odc, odt, ticks);
			if (rc) 
			{
				if (H830.Checked) 
					rc = update("8, 30", "h830", odc, odt, ticks);
				else
					rc = remove(8, 30, "h830", odc, odt, ticks);
			}
			if (rc) 
			{
				if (H900.Checked) 
					rc = update("9, 0", "h900", odc, odt, ticks);
				else
					rc = remove(9, 0, "h900", odc, odt, ticks);
			}
			if (rc) 
			{
				if (H930.Checked) 
					rc = update("9, 30", "h930", odc, odt, ticks);
				else
					rc = remove(9, 30, "h930", odc, odt, ticks);
			}
			if (rc) 
			{
				if (H1000.Checked) 
					rc = update("10, 0", "h1000", odc, odt, ticks);
				else
					rc = remove(10, 0, "h1000", odc, odt, ticks);
			}
			if (rc) 
			{
				if (H1030.Checked) 
					rc = update("10, 30", "h1030", odc, odt, ticks);
				else
					rc = remove(10, 30, "h1030", odc, odt, ticks);
			}
			if (rc) 
			{
				if (H1100.Checked) 
					rc = update("11, 0", "h1100", odc, odt, ticks);
				else
					rc = remove(11, 0, "h1100", odc, odt, ticks);
			}
			if (rc) 
			{
				if (H1130.Checked) 
					rc = update("11, 30", "h1130", odc, odt, ticks);
				else
					rc = remove(11, 30, "h1130", odc, odt, ticks);
			}
			if (rc) 
			{
				if (H1200.Checked) 
					rc = update("12, 0", "h1200", odc, odt, ticks);
				else
					rc = remove(12, 0, "h1200", odc, odt, ticks);
			}
			if (rc) 
			{
				if (H1230.Checked) 
					rc = update("12, 30", "h1230", odc, odt, ticks);
				else
					rc = remove(12, 30, "h1230", odc, odt, ticks);
			}
			if (rc) 
			{
				if (H1300.Checked) 
					rc = update("13, 0", "h1300", odc, odt, ticks);
				else
					rc = remove(13, 0, "h1300", odc, odt, ticks);
			}
			if (rc) 
			{
				if (H1330.Checked) 
					rc = update("13, 30", "h1330", odc, odt, ticks);
				else
					rc = remove(13, 30, "h1330", odc, odt, ticks);
			}
			if (rc) 
			{
				if (H1400.Checked) 
					rc = update("14, 0", "h1400", odc, odt, ticks);
				else
					rc = remove(14, 0, "h1400", odc, odt, ticks);
			}
			if (rc) 
			{
				if (H1430.Checked) 
					rc = update("14, 30", "h1430", odc, odt, ticks);
				else
					rc = remove(14, 30, "h1430", odc, odt, ticks);
			}
			if (rc) 
			{
				if (H1500.Checked) 
					rc = update("15, 0", "h1500", odc, odt, ticks);
				else
					rc = remove(15, 0, "h1500", odc, odt, ticks);
			}
			if (rc) 
			{
				if (H1530.Checked) 
					rc = update("15, 30", "h1530", odc, odt, ticks);
				else
					rc = remove(15, 30, "h1530", odc, odt, ticks);
			}
			if (rc) 
			{
				if (H1600.Checked) 
					rc = update("16, 0", "h1600", odc, odt, ticks);
				else
					rc = remove(16, 0, "h1600", odc, odt, ticks);
			}
			if (rc) 
			{
				if (H1630.Checked) 
					rc = update("16, 30", "h1630", odc, odt, ticks);
				else
					rc = remove(16, 30, "h1630", odc, odt, ticks);
			}
			if (rc) 
			{
				if (H1700.Checked) 
					rc = update("17, 0", "h1700", odc, odt, ticks);
				else
					rc = remove(17, 0, "h1700", odc, odt, ticks);
			}
			if (rc) 
			{
				if (H1730.Checked) 
					rc = update("17, 30", "h1730", odc, odt, ticks);
				else
					rc = remove(17, 30, "h1730", odc, odt, ticks);
			}
			if (rc) 
			{
				if (H1800.Checked) 
					rc = update("18, 0", "h1800", odc, odt, ticks);
				else
					rc = remove(18, 0, "h1800", odc, odt, ticks);
			}
			if (rc) 
			{
				if (H1830.Checked) 
					rc = update("18, 30", "h1830", odc, odt, ticks);
				else
					rc = remove(18, 30, "h1830", odc, odt, ticks);
			}
			if (rc)
				odt.Commit();
			else
				odt.Rollback();
			bind(ticks, odc);
			odc.Close();
		}

		/// <summary>
		/// Updates bookings and resources tables (new booking).
		/// </summary>
		/// <param name="hm">hh, mm string</param>
		/// <param name="col">resources column name</param>
		/// <param name="odc">Database connection</param>
		/// <param name="odt">Database transaction</param>
		/// <param name="ticks">Tick number (time)</param>
		/// <returns>true if the update is possible (available resources)</returns>
		private bool update(string hm, string col, OleDbConnection odc, OleDbTransaction odt,
			long ticks) 
		{
			OleDbCommand odcmd = new OleDbCommand(
				"insert into bookings (id, day, hour, minute) values ('" + id + "', " + ticks +
				", " + hm + ")", odc, odt);
			odcmd.ExecuteNonQuery();
			short h = -1;
			odcmd.CommandText = "select " + col + " from resources where id='available ' and time=" + ticks; 
			OleDbDataReader odr = odcmd.ExecuteReader();
			if (odr.Read())
			{
				h = (short)odr[col];
				odr.Close();
				-- h;
				if (h < 0)
					return false;
				odcmd.CommandText = "update resources set " + col + "=" + h +
					" where id='available ' and time=" + ticks; 
				odcmd.ExecuteNonQuery();
			} 
			else 
			{
				odr.Close();
				return false;
			}
			h = 0;
			odcmd.CommandText = "select " + col + " from resources where id='booked    ' and time=" + ticks; 
			odr = odcmd.ExecuteReader();
			if (odr.Read())
			{
				h = (short)odr[col];
				++ h;
				odr.Close();
			} 
			else 
			{
				odr.Close();
				odcmd.CommandText = "insert into resources (id, time) values ('booked', " + ticks + ")";
				odcmd.ExecuteNonQuery();
				h = 1;
			}
			odcmd.CommandText = "update resources set " + col + "=" + h + " where id='booked    ' and time="
				+ ticks; 
			odcmd.ExecuteNonQuery();
			return true;
		}

		/// <summary>
		/// Updates bookings and resources tables (remove booking).
		/// </summary>
		/// <param name="hh">hour</param>
		/// <param name="mm">minutes</param>
		/// <param name="col">resources column name</param>
		/// <param name="odc">Database connection</param>
		/// <param name="odt">Database transaction</param>
		/// <param name="ticks">Tick number (time)</param>
		/// <returns>true</returns>
		private bool remove(int hh, int mm, string col, OleDbConnection odc, OleDbTransaction odt,
			long ticks) 
		{
			OleDbCommand odcmd = new OleDbCommand("select hour, minute from bookings where id='" +
				id.PadRight(10, ' ') + "' and day=" + ticks + " and hour=" + hh + " and minute=" + mm,
				odc, odt); 
			OleDbDataReader odr = odcmd.ExecuteReader();
			if (odr.Read()) 
			{
				odr.Close();
				delete(odcmd, odr, ticks, col, hh, mm);
			} 
			else
				odr.Close();
			return true;
		}

		/// <summary>
		/// Remove a booking from bookings, available and booked resources.
		/// Called by remove and dgBookings_cmds.
		/// </summary>
		/// <param name="odcmd">Database command</param>
		/// <param name="odr">Database reader</param>
		/// <param name="ticks">Tick number (time)</param>
		/// <param name="col">resources column name</param>
		/// <param name="hh">hour</param>
		/// <param name="mm">minutes</param>
		private void delete(OleDbCommand odcmd, OleDbDataReader odr, long ticks, string col, int hh,
			int mm) 
		{
			odcmd.CommandText = "delete from bookings where id='" + id.PadRight(10, ' ') +
				"' and day=" + ticks + " and hour=" + hh + " and minute=" + mm; 
			odcmd.ExecuteNonQuery();
			odcmd.CommandText = "select " + col + " from resources where id='available ' and time="
				+ ticks;
			odr = odcmd.ExecuteReader();
			if (odr.Read()) 
			{
				short h = (short)odr[col];
				++ h;
				odr.Close();
				odcmd.CommandText = "update resources set " + col + "=" + h +
					" where id='available ' and time=" + ticks; 
				odcmd.ExecuteNonQuery();
			} 
			else
				odr.Close();
			odcmd.CommandText = "select " + col + " from resources where id='booked    ' and time="
				+ ticks; 
			odr = odcmd.ExecuteReader();
			if (odr.Read())
			{
				short h = (short)odr[col];
				-- h;
				odr.Close();
				odcmd.CommandText = "update resources set " + col + "=" + h +
					" where id='booked    ' and time=" + ticks; 
				odcmd.ExecuteNonQuery();
			} 
			else
				odr.Close();
		}
		/// <summary>
		/// Event fired when the user clicks on the Cancel button.
		/// Rolls back previous user selection.
		/// </summary>
		private void OnCancel(object sender, System.EventArgs e)
		{
			DateTime dt = CalBook.SelectedDate;
			OleDbConnection odc = new OleDbConnection(Global.connectionString);
			odc.Open();
			OleDbConnection odc2 = new OleDbConnection(Global.connectionString);
			odc2.Open();
			OleDbTransaction odt = odc.BeginTransaction();
			long ticks = dt.ToFileTime();
			OleDbCommand odcmd = new OleDbCommand("select hour, minute from bookings where id='" +
				id.PadRight(10, ' ') + "' and day=" + ticks, odc, odt); 
			OleDbDataReader odr = odcmd.ExecuteReader();
			while (odr.Read())
			{
				short h = (short)odr["hour"];
				short mn = (short)odr["minute"];
				string col = cols[h - 8, mn / 30];
				OleDbCommand odcmd2 = new OleDbCommand("select " + col +
					" from resources where id='available ' and time=" + ticks, odc2, odt); 
				OleDbDataReader odr2 = odcmd2.ExecuteReader();
				if (odr2.Read())
				{
					h = (short)odr2[col];
					++ h;
					odr2.Close();
					odcmd2.CommandText = "update resources set " + col + "=" + h +
						" where id='available ' and time=" + ticks; 
					odcmd2.ExecuteNonQuery();
				} 
				else
					odr2.Close();
				odcmd2.CommandText =
					"select " + col + " from resources where id='booked    ' and time=" + ticks; 
				odr2 = odcmd2.ExecuteReader();
				if (odr2.Read())
				{
					h = (short)odr2[col];
					-- h;
					odr2.Close();
					odcmd2.CommandText = "update resources set " + col + "=" + h +
						" where id='booked    ' and time=" + ticks; 
					odcmd2.ExecuteNonQuery();
				} 
				else
					odr2.Close();
			}
			odr.Close();
			odcmd.CommandText = "delete from bookings where id='" + id.PadRight(10, ' ') +
				"' and day=" + ticks; 
			odcmd.ExecuteNonQuery();
			odt.Commit();
			bind(ticks, odc);
			odc.Close();
			odc2.Close();
		}

		/// <summary>
		/// ItemCommand of the DataGrid.
		/// Handle clicks on bookRem and bookSel buttons.
		/// Delete booking or select the calendar entry and schedule.
		/// </summary>
		private void dgBookings_cmds(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
		{
			int sel = e.Item.ItemIndex;
			if (sel != -1) 
			{
				string datetime = (string)dgBookings.DataKeys[sel];
				string[] dts = datetime.Split(new Char[] { ' ', ':' });
				DateTime dt = DateTime.Parse(dts[0]);
				long ticks = dt.ToFileTime();
				OleDbConnection odc = new OleDbConnection(Global.connectionString);
				odc.Open();
				Button b = (Button)e.CommandSource;
				if (b.CommandName.Equals("bookRem")) 
				{
					int hh = int.Parse(dts[1]);
					int mm = int.Parse(dts[2]);
					OleDbCommand odcmd = new OleDbCommand();
					odcmd.Connection = odc;
					OleDbDataReader odr = null;
					string col = cols[hh - 8, mm / 30];
					delete(odcmd, odr, ticks, col, hh, mm);
				} 
				else 
					CalBook.SelectedDate = dt;
				bind(ticks, odc);
				btnBook.Enabled = true;
				btnUnbook.Enabled = true;
				odc.Close();
			}
		}
	}
	/// <summary>
	/// Datagrid row.
	/// </summary>
	public sealed class DgRow 
	{
		string datetime;
		public DgRow(long ticks, short hour, short minutes) 
		{
			DateTime dt = DateTime.FromFileTime(ticks);
			string hh, mm;
			if (hour < 10)
				hh = " 0" + hour;
			else
				hh = " " + hour;
			if (minutes < 10)
				mm = ":0" + minutes;
			else
				mm = ":" + minutes;
			datetime = dt.ToShortDateString() + hh + mm;
		}
		public string date 
		{
			get { return datetime; }
		}
	}
}