using System ;
using System.Configuration ;
using System.Data ;
using System.Data.SqlClient ;
using System.Collections ;
using Microsoft.ApplicationBlocks.Data ;

namespace Microsoft.RFIDLab.EPCISDemo.Common
{
	/// <summary>
	/// Performs data-base operations for the track and trace application.
	/// </summary>
	public class TrackTraceDBManager
	{
		// collection to store connection-strings for various data-bases
		private static Hashtable connectionStrings ;
		// the current connection-string to be used by the instance
		private string currentConnectionString ;

		/// <summary>
		/// Called once when the class is loaded, and initializes the
		/// hash-table containing all connection-strings.
		/// </summary>
		static TrackTraceDBManager ()
		{
			connectionStrings = new Hashtable () ;
			// add the manufacturer's connection-string
			connectionStrings.Add ( Constants.Manufacturer,
				ConfigurationSettings.AppSettings[Constants.ManufacturerConnectionString] ) ;
			connectionStrings.Add ( Constants.ShippingAgent,
				ConfigurationSettings.AppSettings[Constants.ShippingAgentConnectionString] ) ;
			connectionStrings.Add ( Constants.ReceivingAgent,
				ConfigurationSettings.AppSettings[Constants.ReceivingAgentConnectionString] ) ;
			connectionStrings.Add ( Constants.Receiver,
				ConfigurationSettings.AppSettings[Constants.ReceiverConnectionString] ) ;
			connectionStrings.Add ( Constants.All, Constants.All ) ;

		} // end of static constructor ()

		/// <summary>
		/// Initializes the data-base manager with a specific DB instance.
		/// </summary>
		/// <param name="whichDatabase">The data-base instance to connect to.
		/// </param>
		public TrackTraceDBManager ( string whichDatabase )
		{
			/* branch based on the database selected and set the current
			 * connection-string */
			switch ( whichDatabase )
			{
				case Constants.Manufacturer: // manufacturer's data-base
					currentConnectionString = ( string )
						connectionStrings[Constants.Manufacturer] ;
					break ;

				case Constants.ShippingAgent: // shipping agent's data-base
					currentConnectionString = ( string )
						connectionStrings[Constants.ShippingAgent] ;
					break ;

				case Constants.ReceivingAgent: // receiving agent's data-base
					currentConnectionString = ( string )
						connectionStrings[Constants.ReceivingAgent] ;
					break ;

				case Constants.Receiver: // receiver's data-base
					currentConnectionString = ( string )
						connectionStrings[Constants.Receiver] ;
					break ;

				case Constants.All: // all data-bases
					currentConnectionString = ( string )
						connectionStrings[Constants.All] ;
					break ;

				default: // unknown data-base
					throw new ApplicationException ( "The database for " +
						whichDatabase + " is not registered. " ) ;

			} // end of switch

		} // end of parameterized constructor ()

		/// <summary>
		/// Initializes the data-base manager with all connection-strings.
		/// </summary>
		public TrackTraceDBManager () : this ( Constants.All )
		{
		} // end of default constructor ()

		/// <summary>
		/// Resets a data-base.
		/// </summary>
		public void ResetDatabase ()
		{
			// if all data-bases have to be reset
			if ( currentConnectionString == Constants.All )
			{
				// get an enumerator over the hash-table of connection-strings
				IDictionaryEnumerator ie = connectionStrings.GetEnumerator () ;

				while ( ie.MoveNext () ) // while there are items
				{
					// get the connection-string
					string connectionString = ( string ) ie.Value ;

					// if the connection-string is valid, reset the data-base
					if ( connectionString != Constants.All )
						Reset ( connectionString ) ; // end if
					
				} // end while

			} // end if

		} // end of ResetDatabase ()

		/// <summary>
		/// Adds a new EPC event into the data-base.
		/// </summary>
		/// <param name="epcTag">The EPC tag-ID to add. </param>
		/// <param name="tagEventStatusType">The type of event which occured.
		/// </param>
		/// <param name="eventDateTime">The date-time instant when the event
		/// occured. </param>
		public void AddEpcStatusInfo ( string epcTag, int tagEventStatusType,
			DateTime eventDateTime )
		{
			SqlConnection connection = null ;

			try
			{
				try
				{
					connection = GetConnection () ; // obtain a connection

				} // end of try nested block

				catch
				{
					throw new ApplicationException (
						ExceptionManager.GetErrorMessage ( "DB_NO_CONNECTION" ) ) ;

				} // end of catch block
			
				/* invoke the add EPC-status stored-procedure and pass
				 * parameters to it */
				SqlHelper.ExecuteScalar ( connection,
					Constants.AddEpcStatusInfoStoredProcName, epcTag,
					tagEventStatusType, eventDateTime ) ;

			} // end of outer try block

			catch ( Exception e )
			{
				throw new ApplicationException ( string.Format (
					ExceptionManager.GetErrorMessage (
					Constants.GeneralDataBaseError ), e.Message ) ) ;

			} // end of catch block

			finally
			{
				// if the connection is not null, dispose it
				if ( connection != null )
					connection.Dispose () ; // end if

			} // end of finally block
		
		} // end of AddEpcStatusInfo ()

		/// <summary>
		/// Adds multiple EPC events into the data-base.
		/// </summary>
		/// <param name="epcTags">The EPC tag-IDs to add. </param>
		/// <param name="tagEventStatusType">The type of event which occured.
		/// </param>
		/// <param name="eventDateTime">The date-time instant when the events
		/// occured. </param>
		public void AddEpcStatusInfo ( object[] epcTags, int tagEventStatusType,
			DateTime eventDateTime )
		{
			SqlConnection connection = null ;

			try
			{
				try
				{
					connection = GetConnection () ; // obtain a connection

				} // end of try nested block

				catch
				{
					throw new ApplicationException (
						ExceptionManager.GetErrorMessage ( "DB_NO_CONNECTION" ) ) ;

				} // end of catch block

				/* for each EPC tag, invoke the add EPC-status stored-procedure
				 * and pass parameters to it */
				foreach ( string epcTag in epcTags )			
					SqlHelper.ExecuteScalar ( connection,
						Constants.AddEpcStatusInfoStoredProcName, epcTag,
						tagEventStatusType, eventDateTime ) ; // end for

			} // end of outer try block

			catch ( Exception e )
			{
				throw new ApplicationException ( string.Format (
					ExceptionManager.GetErrorMessage (
					Constants.GeneralDataBaseError ), e.Message ) ) ;

			} // end of catch block

			finally
			{
				// if the connection is not null, dispose it
				if ( connection != null )
					connection.Dispose () ; // end if

			} // end of finally block
		
		} // end of AddEpcStatusInfo ()

		/// <summary>
		/// Adds a new EPC event into the data-base.
		/// </summary>
		/// <param name="epcTag">The EPC tag-ID to add. </param>
		/// <param name="tagEventStatusType">The type of event which occured.
		/// </param>
		/// <param name="eventDateTime">The date-time instant when the event
		/// occured. </param>
		/// <param name="eventFrom">The person sending this event. </param>
		/// <param name="eventTo">The person to whom this event is sent. </param>
		/// <param name="location">The event-location. </param>
		public void AddFullEpcStatusInfo ( string epcTag, int tagEventStatusType,
			DateTime eventDateTime, string eventFrom, string eventTo,
			string location )
		{
			SqlConnection connection = null ;

			try
			{
				try
				{
					connection = GetConnection () ; // obtain a connection

				} // end of try nested block

				catch
				{
					throw new ApplicationException (
						ExceptionManager.GetErrorMessage ( "DB_NO_CONNECTION" ) ) ;

				} // end of catch block
			
				/* invoke the add EPC-status stored-procedure and pass
				 * parameters to it */
				SqlHelper.ExecuteScalar ( connection,
					Constants.AddFullEpcStatusInfoStoredProcName, epcTag,
					tagEventStatusType, eventDateTime, eventFrom, eventTo,
					location ) ;

			} // end of outer try block

			catch ( Exception e )
			{
				throw new ApplicationException ( string.Format (
					ExceptionManager.GetErrorMessage (
					Constants.GeneralDataBaseError ), e.Message ) ) ;

			} // end of catch block

			finally
			{
				// if the connection is not null, dispose it
				if ( connection != null )
					connection.Dispose () ; // end if

			} // end of finally block
		
		} // end of AddFullEpcStatusInfo ()

		/// <summary>
		/// Adds multiple EPC events into the data-base.
		/// </summary>
		/// <param name="epcTags">The EPC tag-IDs to add. </param>
		/// <param name="tagEventStatusType">The type of event which occured.
		/// </param>
		/// <param name="eventDateTime">The date-time instant when the events
		/// occured. </param>
		/// <param name="eventFrom">The person sending this event. </param>
		/// <param name="eventTo">The person to whom this event is sent. </param>
		/// <param name="location">The event-location. </param>
		public void AddFullEpcStatusInfo ( object[] epcTags, int tagEventStatusType,
			DateTime eventDateTime, string eventFrom, string eventTo,
			string location )
		{
			SqlConnection connection = null ;

			try
			{
				try
				{
					connection = GetConnection () ; // obtain a connection

				} // end of try nested block

				catch
				{
					throw new ApplicationException (
						ExceptionManager.GetErrorMessage ( "DB_NO_CONNECTION" ) ) ;

				} // end of catch block

				/* for each EPC tag, invoke the add EPC-status stored-procedure
				 * and pass parameters to it */
				foreach ( string epcTag in epcTags )			
					SqlHelper.ExecuteScalar ( connection,
						Constants.AddFullEpcStatusInfoStoredProcName, epcTag,
						tagEventStatusType, eventDateTime, eventFrom, eventTo,
						location ) ; // end for

			} // end of outer try block

			catch ( Exception e )
			{
				throw new ApplicationException ( string.Format (
					ExceptionManager.GetErrorMessage (
					Constants.GeneralDataBaseError ), e.Message ) ) ;

			} // end of catch block

			finally
			{
				// if the connection is not null, dispose it
				if ( connection != null )
					connection.Dispose () ; // end if

			} // end of finally block
		
		} // end of AddEpcStatusInfo ()

		/// <summary>
		/// Gets all the EPC events for a specified tag-ID.
		/// </summary>
		/// <param name="epc">The EPC tag-ID. </param>
		/// <returns>Data-set containing all events. </returns>
		public DataSet GetEpcEvents ( string epc )
		{
			SqlConnection connection = null ;
			DataSet dataSet = null ; // the data-set to return

			try
			{
				try
				{
					connection = GetConnection () ; // get the connection

				} // end of nested try block

				catch
				{
					throw new ApplicationException (
						ExceptionManager.GetErrorMessage (
						Constants.NoConnectionWithDatabase ) ) ;

				} // end of catch block

				// get the reseult in a data-set
				dataSet = SqlHelper.ExecuteDataset (
					connection, Constants.GetEpcEventsStoredProcName, epc ) ;

			} // end of outer try block

			catch ( Exception e )
			{
				throw new ApplicationException ( string.Format (
					ExceptionManager.GetErrorMessage (
					Constants.GeneralDataBaseError ), e.Message ) ) ;

			} // end of catch block

			finally
			{
				// if the connection is not null, dispose it
				if ( connection != null )
					connection.Dispose () ; // end if

			} // end of finally block

			return dataSet ;

		} // end of GetEpcEvents ()

		/// <summary>
		/// Gets all the EPC events from the database and return by sort field
		/// </summary>
		/// <param name="epc">Order by Column </param>
		/// <returns>Data-set containing all EPC events. </returns>
		public DataSet GetAllEPCEvents ( string orderBy )
		{
			SqlConnection connection = null ;
			DataSet dataSet = null ; // the data-set to return
			if(orderBy == null)
				orderBy = "EPCEventDate"; //DateTime column of EPCEventStatus Table.
			else if(orderBy.Equals("EVENT DATE"))
				orderBy = "EPCEventDate";
			else if(orderBy.Equals("EVENT"))
				orderBy = "EPCEventId";
			else if(orderBy.Equals("EPC ID"))
				orderBy = "EPCId";
			else 
			{
				//I am passing invalid string. I should know what i am doing.
			}

			try
			{
				try
				{
					connection = GetConnection () ; // get the connection

				} // end of nested try block

				catch
				{
					throw new ApplicationException (
						ExceptionManager.GetErrorMessage (
						Constants.NoConnectionWithDatabase ) ) ;

				} // end of catch block

				// get the reseult in a data-set
				dataSet = SqlHelper.ExecuteDataset (
					connection, Constants.GetEPCEventsOrderBy, orderBy ) ;

			} // end of outer try block

			catch ( Exception e )
			{
				throw new ApplicationException ( string.Format (
					ExceptionManager.GetErrorMessage (
					Constants.GeneralDataBaseError ), e.Message ) ) ;

			} // end of catch block

			finally
			{
				// if the connection is not null, dispose it
				if ( connection != null )
					connection.Dispose () ; // end if

			} // end of finally block

			return dataSet ;

		} // end of GetAllEPCEvents ()

		/// <summary>
		/// Gets all the EPC events for a specified tag-ID.
		/// </summary>
		/// <param name="epc">The EPC tag-ID. </param>
		/// <returns>Data-set containing all events. </returns>
		public DataSet GetFullEpcEvents ( string epc )
		{
			SqlConnection connection = null ;
			DataSet dataSet = null ; // the data-set to return

			try
			{
				try
				{
					connection = GetConnection () ; // get the connection

				} // end of nested try block

				catch
				{
					throw new ApplicationException (
						ExceptionManager.GetErrorMessage (
						Constants.NoConnectionWithDatabase ) ) ;

				} // end of catch block

				// get the reseult in a data-set
				dataSet = SqlHelper.ExecuteDataset (
					connection, Constants.GetFullEpcEventsStoredProcName, epc ) ;

			} // end of outer try block

			catch ( Exception e )
			{
				throw new ApplicationException ( string.Format (
					ExceptionManager.GetErrorMessage (
					Constants.GeneralDataBaseError ), e.Message ) ) ;

			} // end of catch block

			finally
			{
				// if the connection is not null, dispose it
				if ( connection != null )
					connection.Dispose () ; // end if

			} // end of finally block

			return dataSet ;

		} // end of GetEpcEvents ()

		/// <summary>
		/// Gets all the EPC events from the database and return by sort field
		/// </summary>
		/// <param name="epc">Order by Column </param>
		/// <returns>Data-set containing all EPC events. </returns>
		public DataSet GetFullEPCEventsOrdered ( string orderBy )
		{
			SqlConnection connection = null ;
			DataSet dataSet = null ; // the data-set to return
			if(orderBy == null)
				orderBy = "EPCEventDate"; //DateTime column of EPCEventStatus Table.
			else if(orderBy.Equals("EVENT DATE"))
				orderBy = "EPCEventDate";
			else if(orderBy.Equals("EVENT"))
				orderBy = "EPCEventId";
			else if(orderBy.Equals("EPC ID"))
				orderBy = "EPCId, EPCStatusTable.EPCEventDate"; //Ramesh. Faster m/c log async events
			else if(orderBy.Equals("EVENT FROM"))               //get also by datetime.
				orderBy = "Event_From";
			else if(orderBy.Equals("EVENT TO"))
				orderBy = "Event_To";
			else if(orderBy.Equals("LOCATION"))
				orderBy = "Location";
			else 
			{
				//I am passing invalid string. I should know what i am doing.
			}

			try
			{
				try
				{
					connection = GetConnection () ; // get the connection

				} // end of nested try block

				catch
				{
					throw new ApplicationException (
						ExceptionManager.GetErrorMessage (
						Constants.NoConnectionWithDatabase ) ) ;

				} // end of catch block

				// get the reseult in a data-set
				dataSet = SqlHelper.ExecuteDataset (
					connection, Constants.GetFullEPCEventsOrderBy, orderBy ) ;

			} // end of outer try block

			catch ( Exception e )
			{
				throw new ApplicationException ( string.Format (
					ExceptionManager.GetErrorMessage (
					Constants.GeneralDataBaseError ), e.Message ) ) ;

			} // end of catch block

			finally
			{
				// if the connection is not null, dispose it
				if ( connection != null )
					connection.Dispose () ; // end if

			} // end of finally block

			return dataSet ;

		} // end of GetFullEPCEventsOrdered ()

		/// <summary>
		/// Resets the data-base based on the connection-string.
		/// </summary>
		private void Reset ( string connectionString )
		{
			SqlConnection connection = null ;

			try
			{
				try
				{
					// get the connection
					connection = GetConnection ( connectionString ) ;

				} // end of nested try block

				catch
				{
					throw new ApplicationException (
						ExceptionManager.GetErrorMessage (
						Constants.NoConnectionWithDatabase ) ) ;

				} // end of catch block

				// execute the reset-database stored procedure
				SqlHelper.ExecuteScalar ( connection,
					CommandType.StoredProcedure,
					Constants.ResetDatabaseStoredProcName ) ;
				// Load EPC Event Types into the EPCEventTypesTable.
				/* SqlHelper.ExecuteScalar ( connection,
					CommandType.StoredProcedure,
					Constants.InitializeEventTypesTable ) ; */

			} // end of outer try block

			catch ( Exception e )
			{
				throw new ApplicationException ( string.Format (
					ExceptionManager.GetErrorMessage (
					Constants.GeneralDataBaseError ),
					e.Message ) ) ;

			} // end of catch block

			finally
			{
				// if the connection is not null, dipose it
				if ( connection != null )
					connection.Dispose () ; // end if

			} // end of finally block

		} // end of Reset ()

		/// <summary>
		/// Gets an open connection with the data-base as per the
		/// connection-string.
		/// </summary>
		/// <param name="connectionString">The connection-string. </param>
		/// <returns>Live connection object. </returns>
		private SqlConnection GetConnection ( string connectionString )
		{
			SqlConnection connection = new SqlConnection (
				connectionString ) ; // create a connection object
			connection.Open () ; // open the connection
			return connection ; // return it

		} // end of GetConnection ()

		/// <summary>
		/// Gets an open connection with the current data-base.
		/// </summary>
		/// <returns>Live connection object. </returns>
		private SqlConnection GetConnection ()
		{
			// return a connection with the current connection-string
			return GetConnection ( currentConnectionString ) ;

		} // end of GetConnection ()

		/// <summary>
		/// Defines data-base constants for the application.
		/// </summary>
		public class Constants
		{
			// Constants for denoting various data-base instances
			
			public const string Manufacturer = "MANUFACTURER" ;
			public const string Receiver = "RECEIVER" ;
			public const string ShippingAgent = "SHIPPING_AGENT" ;
			public const string ReceivingAgent = "RECEIVING_AGENT" ;
			public const string All = "ALL" ;

			/* Constants for reading connection-strings from the 
			 * configuration file */
			public const string ManufacturerConnectionString =	"ManufacturerString" ;
			public const string ShippingAgentConnectionString =	"ShippingAgentString" ;
			public const string ReceivingAgentConnectionString = "ReceivingAgentString" ;
			public const string ReceiverConnectionString =	"ReceiverString" ;

			// Stored-procedure names
		
			/// Stored-procedure name for resetting the data-base.
			public const string ResetDatabaseStoredProcName = "ResetDatabase" ;
			/// Stored-procedure name for adding EPC event-status.
			public const string AddEpcStatusInfoStoredProcName = "AddEpcStatusInfo" ;
			/// Stored-procedure name for adding EPC events with full-information
			public const string AddFullEpcStatusInfoStoredProcName = "AddFullEpcStatusInfo" ;
			/// Stored-procedure name for getting all EPC events.
			public const string GetEpcEventsStoredProcName = "GetEPCEvents" ;
			/// Stored-procedure name for getting all EPC events ordered by column name.
			public const string GetEPCEventsOrderBy = "GetEPCEventsOrdered" ;
			/// Stored-procedure name for getting full EPC events
			public const string GetFullEpcEventsStoredProcName = "GetFullEPCEvents" ;
			/// Stored-procedure name for getting full EPC events ordered by column name.
			public const string GetFullEPCEventsOrderBy = "GetFullEPCEventsOrdered" ;
			/// Stored-procedure name for initializing EPCEventTypesTable
			public const string InitializeEventTypesTable = "InitializeEPCEventTypesTable";

			// Exception-messages
			
			public const string NoConnectionWithDatabase = "DB_NO_CONNECTION" ;
			public const string GeneralDataBaseError = "DB_GENERAL_ERROR" ;

			// Item-EPC Event status constants
	
			/// ASN from manufacturer to receiver.
			public const int AsnFromManufacturerToReceiver = 1 ;
			/// Acknowledgement from receiver to manufacturer.
			public const int AcknowledgementFromReceiverToManufacturer = 2 ;
			/// Shipment notification from manufacturer to shipping-agent.
			public const int ShippingNotificationFromManufacturerToShippingAgent = 3 ;
			/// Shipping order confirmation from shipping-agent to manufacturer.
			public const int ShippingOrderConfFromShippingAgentToManufacturer = 4 ;

			/// Item pick-up event from shipping-agent.
			public const int ItemPickUpEventFromShippingAgent = 11 ;
			/// Notify of ASN from shipping-agent to receiving-agent.
			public const int NotifyOfAsnFromShippingAgentToReceivingAgent = 12 ;
			/// Acknowledgement from receiving-agent to shipping-agent.
			public const int AcknowledgementFromReceivingAgentToShippingAgent = 13 ;

			/// Notify of shipping-order confirmation from shipping-agent to
			/// manufacturer.
			public const int NotifyOfShippingOrderConfFromShippingAgentToManufacturer = 14 ;
			/// Item-delivered from shipping-agent to receiving-agent.
			public const int ItemDeliveredFromShippingAgentToReceivingAgent = 15 ;
			
			/// Shipping Order Request from Manufacturer.
			public const int SA_ShippingOrderRequestFromManufacturer = 16 ;
			
			/// Item-received event from receiving-agent.
			public const int ItemReceivedFromReceivingAgent = 21 ;
			
			/// Notification of shipment-receipt from receiving-agent to
			/// shipping-agent.
			public const int NotifyOfShipmentReceiptFromReceivingAgentToShippingAgent = 22 ;
			/// Item-delievered event from receiving-agent to receiver.
			public const int ItemDeliveredFromReceivingAgentToReceiver = 23 ;
			/// Item-received event from receiver to receiving-agent.
			public const int ItemReceivedFromReceiverToReceivingAgent = 31 ;
			/// Notification of shipment-receipt from receiver to manufacturer.
			public const int NotifyOfShipmentReceiptFromReceiverToManufacturer = 32 ;				

		} // end of Constants

	} // class definition over

} // end of namespace

