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

namespace Microsoft.RFIDLab.EPCISDemo.Common
{
	/// <summary>
	/// Provides properties and methods for database operations
	/// </summary>
	public class DatabaseManager
	{
		private string connectionString;

		#region Constructor
		/// <summary>
		/// Constructor
		/// </summary>
		/// <param name="connectionString">connection string to connect to database</param>
		public DatabaseManager(string connectionString)
		{
			this.connectionString = connectionString;
		}
		#endregion

		#region Private Method GetConnection
		/// <summary>
		/// Create and open a SqlConnection
		/// </summary>
		/// <param name="connectionString">Connection string to the database</param>
		/// <returns>SqlConnection</returns>
		private SqlConnection GetConnection(string connectionString)
		{
			SqlConnection connection=new SqlConnection();
			
			connection = new SqlConnection(connectionString);
			connection.Open();
			
			return connection;
		}
		#endregion

		#region GetTagType
		/// <summary>
		/// Retrieve the tag type from database. This will identify the tag as 
		/// a pallet, case or each
		/// </summary>
		/// <param name="tagId">Hexadecimal EPC tag ID</param>
		/// <returns>Tag type</returns>
		public string GetTagType(string tagId)
		{
			SqlConnection connection = null;
			string errorMessage;
			string tagType;
			try
			{
				try
				{
					connection = GetConnection(connectionString);
				}
				catch
				{
					errorMessage = ExceptionManager.GetErrorMessage("DB_NO_CONNECTION");
					throw new ApplicationException(errorMessage);
				}

				object result = SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, "getTagType", new SqlParameter("@TagId", tagId));
				if (result == null)
				{
					errorMessage = string.Format("Tag cannot be decoded");
					throw new ApplicationException(errorMessage);
				}
				tagType = result.ToString();
			}
			catch(Exception exp)
			{
				errorMessage = string.Format(ExceptionManager.GetErrorMessage("DB_GENERAL_ERROR"), exp.Message);
				throw new ApplicationException(errorMessage);
			}
			finally
			{
				if(connection != null)
					connection.Dispose();
			}

			return tagType;
		}
		#endregion

		#region GetItemDetails
		/// <summary>
		/// Get details for an item from the databse by calling the stored procedure getItemDetails
		/// </summary>
		/// <param name="UPC">UPC for the item</param>
		/// <returns>DataSet containing item details</returns>
		public DataSet GetItemDetails (string UPC)
		{
			SqlConnection connection = null;
			string errorMessage;
			DataSet itemDetails = null;

			try
			{
				try
				{
					connection = GetConnection(connectionString);
				}
				catch
				{
					errorMessage = ExceptionManager.GetErrorMessage("DB_NO_CONNECTION");
					throw new ApplicationException(errorMessage);
				}

				// Call the stored procedure
				itemDetails = SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, "getItemDetails", new SqlParameter("@UPC", UPC));
			}
			catch(Exception exp)
			{
				errorMessage = string.Format(ExceptionManager.GetErrorMessage("DB_GENERAL_ERROR"), exp.Message);
				throw new ApplicationException(errorMessage);
			}
			finally
			{
				if(connection != null)
					connection.Dispose();
			}

			return itemDetails;
		}
		#endregion

		#region GetNextSerialNumber
		/// <summary>
		/// Get the next Serial number for a product to commission EPC
		/// </summary>
		/// <param name="productUPC">UPC of Bike</param>
		/// <returns>Next Serial Number</returns>
		public int GetNextSerialNumber(string productUPC)
		{
			SqlConnection connection = null;
			string errorMessage;
			int serialNumber;
			try
			{
				try
				{
					connection = GetConnection(connectionString);
				}
				catch
				{
					errorMessage = ExceptionManager.GetErrorMessage("DB_NO_CONNECTION");
					throw new ApplicationException(errorMessage);
				}

				object result = SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, "getProductSerialNumber", new SqlParameter("@UPC", productUPC));
				if (result == null)
				{
					errorMessage = string.Format(ExceptionManager.GetErrorMessage("DB_PRODUCT_NOT_FOUND"), productUPC);
					throw new ApplicationException(errorMessage);
				}
				serialNumber = Convert.ToInt32(result);
			}
			catch(Exception exp)
			{
				errorMessage = string.Format(ExceptionManager.GetErrorMessage("DB_GENERAL_ERROR"), exp.Message);
				throw new ApplicationException(errorMessage);
			}
			finally
			{
				if(connection != null)
					connection.Dispose();
			}

			return serialNumber;
		}
		#endregion

		#region GetNextCompanySerialNumber
		/// <summary>
		/// Get the next Serial number for SSCC to commission EPC
		/// </summary>
		/// <returns>Next Serial Number</returns>
		public int GetNextCompanySerialNumber()
		{
			SqlConnection connection = null;
			string errorMessage;
			int serialNumber;
			try
			{
				try
				{
					connection = GetConnection(connectionString);
				}
				catch
				{
					errorMessage = ExceptionManager.GetErrorMessage("DB_NO_CONNECTION");
					throw new ApplicationException(errorMessage);
				}

				object result = SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, "getCompanySerialNumber");
				if (result == null)
				{
					errorMessage = string.Format(ExceptionManager.GetErrorMessage("DB_PRODUCT_NOT_FOUND"));
					throw new ApplicationException(errorMessage);
				}
				serialNumber = Convert.ToInt32(result);
			}
			catch(Exception exp)
			{
				errorMessage = string.Format(ExceptionManager.GetErrorMessage("DB_GENERAL_ERROR"), exp.Message);
				throw new ApplicationException(errorMessage);
			}
			finally
			{
				if(connection != null)
					connection.Dispose();
			}

			return serialNumber;
		}
		#endregion

		
		#region GetPurchaseOrderDetails
		public DataSet GetPurchaseOrderDetails(int PONumber)
		{
			SqlConnection connection = null;
			string errorMessage;
			DataSet PODetails = null;
			try
			{
				try
				{
					connection = GetConnection(connectionString);
				}
				catch
				{
					errorMessage = ExceptionManager.GetErrorMessage("DB_NO_CONNECTION");
					throw new ApplicationException(errorMessage);
				}

				SqlParameter [] arParms = new SqlParameter[1];
			
				// @OrderId Input Parameter 
				arParms[0] = new SqlParameter("@OrderId", SqlDbType.Int, 20); 
				arParms[0].Value = PONumber;

				PODetails = SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, "getPurchaseOrderDetails", arParms);
			}
			catch(Exception exp)
			{
				errorMessage = string.Format(ExceptionManager.GetErrorMessage("DB_GENERAL_ERROR"), exp.Message);
				throw new ApplicationException(errorMessage);
			}
			finally
			{
				if(connection != null)
					connection.Dispose();
			}

			return PODetails;
		}
		#endregion

		#region
		public DataSet GetPurchaseOrderCaseEPCDetails(int orderNumber, int productId)
		{
			SqlConnection connection = null;
			string errorMessage;
			DataSet CaseEPCDetails = null;
			try
			{
				try
				{
					connection = GetConnection(connectionString);
				}
				catch
				{
					errorMessage = ExceptionManager.GetErrorMessage("DB_NO_CONNECTION");
					throw new ApplicationException(errorMessage);
				}

				SqlParameter [] arParms = new SqlParameter[2];
			
				// @OrderId Input Parameter 
				arParms[0] = new SqlParameter("@OrderId", SqlDbType.Int, 20); 
				arParms[0].Value = orderNumber;

				arParms[1] = new SqlParameter("@ProductId", SqlDbType.Int, 20);
				arParms[1].Value = productId;

				CaseEPCDetails = SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, "getPurchaseOrderCaseEPCDetails", arParms);
			}
			catch(Exception exp)
			{
				errorMessage = string.Format(ExceptionManager.GetErrorMessage("DB_GENERAL_ERROR"), exp.Message);
				throw new ApplicationException(errorMessage);
			}
			finally
			{
				if(connection != null)
					connection.Dispose();
			}

			return CaseEPCDetails;
		}
		#endregion

		#region
		public void UpdateProductNextSerialNumber(String productUPC)
		{
			SqlConnection connection = null;
			string errorMessage;
			try
			{
				try
				{
					connection = GetConnection(connectionString);
				}
				catch
				{
					errorMessage = ExceptionManager.GetErrorMessage("DB_NO_CONNECTION");
					throw new ApplicationException(errorMessage);
				}

				SqlParameter [] arParms = new SqlParameter[1];
			
				// @OrderId Input Parameter 
				arParms[0] = new SqlParameter("@UPC", SqlDbType.VarChar, 30); 
				arParms[0].Value = productUPC;

				SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, "updateProductSerialNumber", arParms);
			}
			catch(Exception exp)
			{
				errorMessage = string.Format(ExceptionManager.GetErrorMessage("DB_GENERAL_ERROR"), exp.Message);
				throw new ApplicationException(errorMessage);
			}
			finally
			{
				if(connection != null)
					connection.Dispose();
			}
		}
		#endregion
	
		#region AddProductEpc
		public void AddProductEpc(string productUpc, string epcTag, int tagType, string parentTag, int orderNumber, int productId, ArrayList childTags)
		{
			SqlConnection connection = null;
			string errorMessage;

			try
			{
				try
				{
					connection = GetConnection(connectionString);
				}
				catch
				{
					errorMessage = ExceptionManager.GetErrorMessage("DB_NO_CONNECTION");
					throw new ApplicationException(errorMessage);
				}

				SqlTransaction transaction = connection.BeginTransaction();
				try
				{					
					AddEpcTag(transaction, epcTag, tagType, null, orderNumber, productId,null);
				}
				catch (Exception exp)
				{
					transaction.Rollback();
					throw exp;
				}
				
				transaction.Commit();
			}
			catch(Exception exp)
			{
				errorMessage = string.Format(ExceptionManager.GetErrorMessage("DB_GENERAL_ERROR"), exp.Message);
				throw new ApplicationException(errorMessage);
			}
			finally
			{
				if(connection != null)
					connection.Dispose();
			}
		}
		#endregion

		#region AddPalletEpc
		public void AddPalletEpc(string epcTag, string parentTag, int orderNumber, int productId, ArrayList childTags)
		{
			SqlConnection connection = null;
			string errorMessage;

			try
			{
				try
				{
					connection = GetConnection(connectionString);
				}
				catch
				{
					errorMessage = ExceptionManager.GetErrorMessage("DB_NO_CONNECTION");
					throw new ApplicationException(errorMessage);
				}

				SqlTransaction transaction = connection.BeginTransaction();
				try
				{
					AddEpcTag(transaction, epcTag, 4, null, orderNumber, productId, childTags);
				}
				catch (Exception exp)
				{
					transaction.Rollback();
					throw exp;
				}
				transaction.Commit();
			}
			catch(Exception exp)
			{
				errorMessage = string.Format(ExceptionManager.GetErrorMessage("DB_GENERAL_ERROR"), exp.Message);
				throw new ApplicationException(errorMessage);
			}
			finally
			{
				if(connection != null)
					connection.Dispose();
			}
		}
		#endregion

		#region GetProductInPallet
		public DataSet GetProductInPallet (string palletTagId)
		{
			SqlConnection connection = null;
			string errorMessage;
			DataSet palletDetails = null;

			try
			{
				try
				{
					connection = GetConnection(connectionString);
				}
				catch
				{
					errorMessage = ExceptionManager.GetErrorMessage("DB_NO_CONNECTION");
					throw new ApplicationException(errorMessage);
				}

				// Call the stored procedure
				palletDetails = SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, "getProductsInPallet", new SqlParameter("@PalletTagId", palletTagId));
			}
			catch(Exception exp)
			{
				errorMessage = string.Format(ExceptionManager.GetErrorMessage("DB_GENERAL_ERROR"), exp.Message);
				throw new ApplicationException(errorMessage);
			}
			finally
			{
				if(connection != null)
					connection.Dispose();
			}

			return palletDetails;
		}
		#endregion

		#region AddEpcTag
		/// <summary>
		/// Add a new tag record in tag details table and associate this as the
		/// parent for all tags in childTags collection
		/// </summary>
		/// <param name="epcTag">Newly commissioned tag</param>
		/// <param name="tagType">Type of tag</param>
		/// <param name="childTags">List of all tags that will associate this as the parent</param>
		private void AddEpcTag (SqlTransaction trans, string epcTag, int tagType, string parentTag, int orderNumber, int productId, ArrayList childTags)
		{
			// Perform - (1) Add newly commission tag data
			//           (2) Associate child tags with this tag as parent
			// These operations need to be done in a transaction to maintain
			// clean data

			SqlParameter [] arParms = new SqlParameter[5];
	
			// @TagId Input Parameter 
			arParms[0] = new SqlParameter("@TagId", SqlDbType.NVarChar, 30); 
			arParms[0].Value = epcTag;

			// @TagTypeId Input Parameter 
			arParms[1] = new SqlParameter("@TagTypeId", SqlDbType.Int);
			arParms[1].Value = tagType;

			// @ParentTagId Input Parameter
			arParms[2] = new SqlParameter("@ParentTagId", SqlDbType.NVarChar, 30);
			arParms[2].Value = parentTag;

			// @OrderId Input Parameter
			arParms[3] = new SqlParameter("@OrderId", SqlDbType.Int);
			arParms[3].Value = orderNumber;

			//@ProductId Input Parameter
			arParms[4] = new SqlParameter("@ProductId", SqlDbType.Int);
			arParms[4].Value = productId;
			
			try
			{
				SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "addEpcTag", arParms);
			}
			catch (Exception exp)
			{
				throw exp;
			}

			if (childTags != null)
			{
				// Loop through the list of child tags and add parent tag data to
				// corresponding records 
				foreach (string childTag in childTags)
				{
					SqlParameter [] arChildParms = new SqlParameter[2];
	
					// @TagId Input Parameter 
					arChildParms[0] = new SqlParameter("@TagId", SqlDbType.NVarChar, 30); 
					arChildParms[0].Value = childTag;

					// @ParentTagId Input Parameter
					arChildParms[1] = new SqlParameter("@ParentTagId", SqlDbType.NVarChar, 30);
					arChildParms[1].Value = epcTag;

					try
					{
						SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "setParentTag", arChildParms);
					}
					catch (Exception exp)
					{
						throw exp;
					}
				}
			}
		}
		#endregion

		//Add EPCStatusEvent Information
		#region AddEpcStatusInformation
		/// <summary>
		/// Add a new tag record in tag details table and associate this as the
		/// parent for all tags in childTags collection
		/// </summary>
		/// <param name="epcTag">Newly commissioned tag</param>
		/// <param name="tagType">Type of tag</param>
		/// <param name="childTags">List of all tags that will associate this as the parent</param>
		public void AddEpcStatusInfo (string epcTag, int tagEventStatusType, DateTime eventDateTime)
		{
			SqlConnection connection = null;
			string errorMessage;

			try
			{
				connection = GetConnection(connectionString);
			}
			catch
			{
				errorMessage = ExceptionManager.GetErrorMessage("DB_NO_CONNECTION");
				throw new ApplicationException(errorMessage);
			}

			SqlParameter [] arParms = new SqlParameter[3];
	
			// @TagId Input Parameter 
			arParms[0] = new SqlParameter("@EPCId", SqlDbType.NVarChar, 30); 
			arParms[0].Value = epcTag;

			// @TagTypeId Input Parameter 
			arParms[1] = new SqlParameter("@EPCEventID", SqlDbType.Int);
			arParms[1].Value = tagEventStatusType;

			// @ParentTagId Input Parameter
			arParms[2] = new SqlParameter("@EPCEventDate", SqlDbType.DateTime);
			arParms[2].Value = eventDateTime;

			
			try
			{
				SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, "addEpcStatusInfo", arParms);
			}
			catch (Exception exp)
			{
				throw exp;
			}			
			
		}
		#endregion

		#region
		public DataSet GetPalletEPCInformationForOrder(int orderNumber)
		{
			SqlConnection connection = null;
			string errorMessage;
			DataSet palletEPCInformation = null;
			try
			{
				try
				{
					connection = GetConnection(connectionString);
				}
				catch
				{
					errorMessage = ExceptionManager.GetErrorMessage("DB_NO_CONNECTION");
					throw new ApplicationException(errorMessage);
				}

				SqlParameter [] arParms = new SqlParameter[1];
			
				// @OrderId Input Parameter 
				arParms[0] = new SqlParameter("@OrderId", SqlDbType.Int, 20); 
				arParms[0].Value = orderNumber;

				palletEPCInformation = SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, "getPurchaseOrderPalletEPCDetails", arParms);
			}
			catch(Exception exp)
			{
				errorMessage = string.Format(ExceptionManager.GetErrorMessage("DB_GENERAL_ERROR"), exp.Message);
				throw new ApplicationException(errorMessage);
			}
			finally
			{
				if(connection != null)
					connection.Dispose();
			}

			return palletEPCInformation;
		}
		#endregion

		#region
		public DataSet GetAssociatedCasesEPCInformation(int orderNumber,string palletEPC)
		{
			SqlConnection connection = null;
			string errorMessage;
			DataSet associatedCasesForPallet = null;
			try
			{
				try
				{
					connection = GetConnection(connectionString);
				}
				catch
				{
					errorMessage = ExceptionManager.GetErrorMessage("DB_NO_CONNECTION");
					throw new ApplicationException(errorMessage);
				}

				SqlParameter [] arParms = new SqlParameter[2];
			
				// @OrderId Input Parameter 
				arParms[0] = new SqlParameter("@OrderId", SqlDbType.Int, 20); 
				arParms[0].Value = orderNumber;

				//@PalletEPC Input Parameter
				arParms[1] = new SqlParameter("@PalletEPC", SqlDbType.VarChar, 30);
				arParms[1].Value = palletEPC;

				associatedCasesForPallet = SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, "getAssociatedCasesEPCInformation", arParms);
			}
			catch(Exception exp)
			{
				errorMessage = string.Format(ExceptionManager.GetErrorMessage("DB_GENERAL_ERROR"), exp.Message);
				throw new ApplicationException(errorMessage);
			}
			finally
			{
				if(connection != null)
					connection.Dispose();
			}

			return associatedCasesForPallet;
		}
		#endregion

		#region
		public void UpdateCompanyNextSerialNumber()
		{
			SqlConnection connection = null;
			string errorMessage;
			try
			{
				try
				{
					connection = GetConnection(connectionString);
				}
				catch
				{
					errorMessage = ExceptionManager.GetErrorMessage("DB_NO_CONNECTION");
					throw new ApplicationException(errorMessage);
				}

				SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, "updateCompanySerialNumber");
			}
			catch(Exception exp)
			{
				errorMessage = string.Format(ExceptionManager.GetErrorMessage("DB_GENERAL_ERROR"), exp.Message);
				throw new ApplicationException(errorMessage);
			}
			finally
			{
				if(connection != null)
					connection.Dispose();
			}
		}
		#endregion


		#region ResetPalletInformation
		/// <summary>
		/// Reset all pallet EPC information from the database
		/// </summary>		
		public void ResetPalletInformation()
		{
			SqlConnection connection = null;
			string errorMessage;
			try
			{
				try
				{
					connection = GetConnection(connectionString);
				}
				catch
				{
					errorMessage = ExceptionManager.GetErrorMessage("DB_NO_CONNECTION");
					throw new ApplicationException(errorMessage);
				}

				SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, "resetPalletInformation");			
			}
			catch(Exception exp)
			{
				errorMessage = string.Format(ExceptionManager.GetErrorMessage("DB_GENERAL_ERROR"), exp.Message);
				throw new ApplicationException(errorMessage);
			}
			finally
			{
				if(connection != null)
					connection.Dispose();
			}			
		}
		#endregion

		
		#region ResetDatabase
		/// <summary>
		/// Reset all case and pallet EPC information from the database
		/// </summary>		
		public void ResetDatabase()
		{
			SqlConnection connection = null;
			string errorMessage;
			try
			{
				try
				{
					connection = GetConnection(connectionString);
				}
				catch
				{
					errorMessage = ExceptionManager.GetErrorMessage("DB_NO_CONNECTION");
					throw new ApplicationException(errorMessage);
				}

				SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, "ResetDatabase");			
			}
			catch(Exception exp)
			{
				errorMessage = string.Format(ExceptionManager.GetErrorMessage("DB_GENERAL_ERROR"), exp.Message);
				throw new ApplicationException(errorMessage);
			}
			finally
			{
				if(connection != null)
					connection.Dispose();
			}			
		}
		#endregion
	}
}
