using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Threading;
using System.Globalization;

/*=====================================================================

  File:      OrdersDB.cs for Adventure Works Cycles Storefront Sample
  Summary:   Middle tier component for manipulating sales orders.
  Date:	     June 16, 2003

---------------------------------------------------------------------

  This file is part of the Microsoft SQL Server Code Samples.
  Copyright (C) Microsoft Corporation.  All rights reserved.

This source code is intended only as a supplement to Microsoft
Development Tools and/or on-line documentation.  See these other
materials for detailed information regarding Microsoft code samples.

THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.

======================================================= */

namespace Microsoft.Samples.SqlServer
{
	//*******************************************************
	//
	// OrderDetails Class
	//
	// A simple data class that encapsulates details about
	// a particular order inside the AdventureWorks
	// database.
	//
	// Note that it is bad style to expose public fields.  So 
	// instead we maintain private fields with corresponding
	// public properties.  This helps avoid binary 
	// incompatibilities if the public fields were to have to
	// be changed to properties later.
	//
	//*******************************************************
	public class OrderDetails
	{
		private DateTime orderDate;

		private DateTime shipDate;

		private String orderTotal;

		private DataSet orderItems;

		// The fields above are exposed publically as properties
		// There is nothing fancy going on here, it is just better 
		// practice to not expose public fields.
		public DateTime OrderDate
		{
			get
			{
				return orderDate;
			}
			set
			{
				orderDate = value;
			}
		}

		public DateTime ShipDate
		{
			get
			{
				return shipDate;
			}
			set
			{
				shipDate = value;
			}
		}

		public String OrderTotal
		{
			get
			{
				return orderTotal;
			}
			set
			{
				orderTotal = value;
			}
		}

		public DataSet OrderItems
		{
			get
			{
				return orderItems;
			}
			set
			{
				orderItems = value;
			}
		}
	}

	//*******************************************************
	//
	// OrderHistoryDB Class
	//
	// Business/Data Logic Class that encapsulates all data
	// logic necessary to query past orders within the
	// AdventureWorks database.
	//
	//*******************************************************
	public class OrdersDB
	{
		//*******************************************************
		//
		// CustomerDB.GetCustomerOrders() Method <a name="GetCustomerOrders"></a>
		//
		// The GetCustomerOrders method returns a DataReader containing 
		// a list of all past orders placed by a specified customer.
		// The SQLDataReaderResult struct also returns the SQL connection,
		// which must be explicitly closed after the data from the DataReader
		// is bound into the controls.
		//
		// Other relevant sources:
		//     + <a href="usp_OrdersList.htm" style="color:green">usp_OrdersList Stored Procedure</a>
		//
		//*******************************************************
		public SqlDataReader GetCustomerOrders(String customerID)
		{
			// Create Instance of Connection and Command Object
			SqlConnection myConnection = new SqlConnection(
				Properties.Settings.Default.ConnectionString);
			SqlCommand myCommand = myConnection.CreateCommand();

			myCommand.CommandText = "usp_OrdersList";

			// Mark the Command as a SPROC
			myCommand.CommandType = CommandType.StoredProcedure;

			// Add Parameters to SPROC
			SqlParameter parameterCustomerid = new SqlParameter("@CustomerID",
																SqlDbType.Int,
																4);

			parameterCustomerid.Value = Int32.Parse(customerID,
													CultureInfo.
													InvariantCulture);
			myCommand.Parameters.Add(parameterCustomerid);

			SqlParameter parameterCulture = new SqlParameter("@Culture",
															 SqlDbType.NVarChar,
															 10);

			parameterCulture.Value = CultureInfo.CurrentUICulture.Name;
			myCommand.Parameters.Add(parameterCulture);

			// Return the datareader result\
			myConnection.Open();
			return myCommand.ExecuteReader(CommandBehavior.CloseConnection);
		}

		//*******************************************************
		//
		// OrdersDB.GetSalesOrderAddresses() Method <a name="GetCustomerDefaultAddresses"></a>
		//
		// The GetSalesOrderAddresses method returns 
		// a DataSet containing two tables -- the billing and shipping address tables for the 
		// specified sales order.  Each table has one row.
		//
		// Other relevant sources:
		//     + <a href="usp_OrdersAddresses" style="color:green">usp_OrdersAddresses Stored Procedure</a>
		//
		//*******************************************************
		public CustomerAddresses GetSalesOrderAddresses(int salesOrderID)
		{
			SqlConnection myConnection = new SqlConnection(
				Properties.Settings.Default.ConnectionString);
			SqlCommand myCommand = myConnection.CreateCommand();

			myCommand.CommandText = "usp_OrdersAddresses";

			SqlParameter customerIDParameter = new SqlParameter("@OrderID",
																SqlDbType.Int);

			myCommand.Parameters.Add(customerIDParameter);
			customerIDParameter.Value = salesOrderID;

			// Mark the Command as a SPROC
			myCommand.CommandType = CommandType.StoredProcedure;

			DataSet ds = new DataSet("Default Addresses");
            ds.Locale = CultureInfo.InvariantCulture;
            SqlDataAdapter sda = new SqlDataAdapter(myCommand);

			sda.Fill(ds);

			CustomerAddresses result = new CustomerAddresses(new
				CustomerAddress(), new CustomerAddress());

			if (ds.Tables.Count > 0)
			{
				result.BillingAddress.FillAddress(ds.Tables[0]);
				if (ds.Tables.Count > 1)
					result.ShippingAddress.FillAddress(ds.Tables[1]);
			}

			return result;
		}	

		//*******************************************************
		//
		// OrdersDB.GetOrderDetails() Method <a name="GetOrderDetails"></a>
		//
		// The GetOrderDetails method returns an OrderDetails
		// instance containing information about the specified
		// order.
		//
		// Other relevant sources:
		//     + <a href="usp_OrdersDetail.htm" style="color:green">usp_OrdersDetail Stored Procedure</a>
		// 
		//*******************************************************
		public OrderDetails GetOrderDetails(int orderID, string customerID)
		{
			// Create Instance of Connection and Command Object
			SqlConnection myConnection = new SqlConnection(
				Properties.Settings.Default.ConnectionString);
			SqlDataAdapter myAdapter = new SqlDataAdapter("usp_OrdersDetail",
														  myConnection);
			SqlCommand myCommand = myAdapter.SelectCommand;

			// Mark the Command as a SPROC
			myCommand.CommandType = CommandType.StoredProcedure;

			// Add Parameters to SPROC
			SqlParameter parameterOrderID = new SqlParameter("@OrderID",
															 SqlDbType.Int, 4);

			parameterOrderID.Value = orderID;
			myCommand.Parameters.Add(parameterOrderID);

			SqlParameter parameterCurrencyCode = new SqlParameter("@Culture",
																  SqlDbType.
																  NVarChar, 10);

			parameterCurrencyCode.Value = CultureInfo.CurrentUICulture.Name;
			myCommand.Parameters.Add(parameterCurrencyCode);

			SqlParameter parameterOrderDate = new SqlParameter("@OrderDate",
															   SqlDbType.
															   DateTime, 8);

			parameterOrderDate.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterOrderDate);

			SqlParameter parameterShipDate = new SqlParameter("@ShipDate",
															  SqlDbType.
															  DateTime, 8);

			parameterShipDate.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterShipDate);

			SqlParameter parameterOrderTotal = new SqlParameter("@OrderTotal",
																SqlDbType.
																NVarChar, 20);

			parameterOrderTotal.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterOrderTotal);

			DataSet myDataSet = new DataSet("OrderDetails");
            myDataSet.Locale = CultureInfo.InvariantCulture;

            myAdapter.Fill(myDataSet);

			// ship date is null if order doesn't exist, or belongs to a different user
			if (parameterShipDate.Value != DBNull.Value)
			{
				// Create and Populate OrderDetails Struct using
				// Output Params from the SPROC, as well as the
				// data reader.
				OrderDetails myOrderDetails = new OrderDetails();

				myOrderDetails.OrderDate = (DateTime)parameterOrderDate.Value;
				myOrderDetails.ShipDate = (DateTime)parameterShipDate.Value;
				myOrderDetails.OrderTotal = (String)parameterOrderTotal.Value;
				myOrderDetails.OrderItems = myDataSet;

				// Return the information about the order as an instance of the OrderDetails class.
				return myOrderDetails;
			}
			else
				return null;
		}

		//*******************************************************
		//
		// OrdersDB.GetShippingMethods() Method <a name="GetShippingMethods"></a>
		//
		// The GetShippingMethods method returns 
		// a forward-only, read-only DataReader.  This returns a list of all
		// shipping methods. 
		//
		// Other relevant sources:
		//     + <a href="usp_OrdersShippingMethods" style="color:green">usp_OrdersShippingMethods Stored Procedure</a>
		//
		//*******************************************************
		public SqlDataReader GetShippingMethods()
		{
			// Create Instance of Connection and Command Object
			SqlConnection myConnection = new SqlConnection(
				Properties.Settings.Default.ConnectionString);
			SqlCommand myCommand = myConnection.CreateCommand();

			myCommand.CommandText = "usp_OrdersShippingMethods";

			// Mark the Command as a SPROC
			myCommand.CommandType = CommandType.StoredProcedure;

			// Return the datareader result
			myConnection.Open();
			return myCommand.ExecuteReader(CommandBehavior.CloseConnection);
		}

		//*******************************************************
		//
		// OrdersDB.CalculateShippingDate() Method <a name="CalculateShippingDate"></a>
		//
		// The CalculateShippingDate method would be where you would
		// place all of the code necessary to calculate the shipping
		// ETA.  For now, we are just making up a random date.
		//
		//*******************************************************
		public DateTime CalculateShippingDate(String customerID, string cartID)
		{
			Random x = new Random();
			double myrandom = (double)x.Next(0, 3);

			return DateTime.Now.AddDays(myrandom);
		}

		//*******************************************************
		//
		// OrdersDB.PlaceOrder() Method <a name="PlaceOrder"></a>
		//
		// The PlaceOrder method places an order within the
		// AdventureWorks Database and then clears out the current
		// items within the shopping cart.
		//
		// Other relevant sources:
		//     + <a href="usp_OrdersAdd.htm" style="color:green">usp_OrdersAdd Stored Procedure</a>
		//
		//*******************************************************
		public int PlaceOrder(string customerID, string cartID,
							  int shippingMethod, Constants.Status orderStatus,
							  int billToAddressID, int shipToAddressID)
		{
			// Create Instance of Connection and Command Object
			SqlConnection myConnection = new SqlConnection(
				Properties.Settings.Default.ConnectionString);
			SqlCommand myCommand =
					   new SqlCommand("usp_OrdersAdd", myConnection);

			// Mark the Command as a SPROC
			myCommand.CommandType = CommandType.StoredProcedure;

			// Add Parameters to SPROC
			SqlParameter parameterCustomerID = new SqlParameter("@CustomerID",
																SqlDbType.Int,
																4);

			parameterCustomerID.Value = Int32.Parse(customerID,
													CultureInfo.
													InvariantCulture);
			myCommand.Parameters.Add(parameterCustomerID);

			SqlParameter parameterCartID = new SqlParameter("@CartID",
															SqlDbType.NVarChar,
															50);

			parameterCartID.Value = cartID;
			myCommand.Parameters.Add(parameterCartID);

			SqlParameter parameterShipDate = new SqlParameter("@ShipDate",
															  SqlDbType.
															  DateTime, 8);

			parameterShipDate.Value = CalculateShippingDate(customerID, cartID);
			myCommand.Parameters.Add(parameterShipDate);

			SqlParameter parameterOrderDate = new SqlParameter("@OrderDate",
															   SqlDbType.
															   DateTime, 8);

			parameterOrderDate.Value = DateTime.Now;
			myCommand.Parameters.Add(parameterOrderDate);

			SqlParameter parameterShippingMethod = new SqlParameter(
				"@ShippingMethod", SqlDbType.Int, 4);

			parameterShippingMethod.Value = shippingMethod;
			myCommand.Parameters.Add(parameterShippingMethod);

			SqlParameter parameterOrderStatus = new SqlParameter("@Status",
																 SqlDbType.Int,
																 4);

			parameterOrderStatus.Value = orderStatus;
			myCommand.Parameters.Add(parameterOrderStatus);

			SqlParameter parameterBillToAddressID = new SqlParameter(
				"@BillToAddressID", SqlDbType.Int, 4);

			parameterBillToAddressID.Value = billToAddressID;
			myCommand.Parameters.Add(parameterBillToAddressID);

			SqlParameter parameterShipToAddressID = new SqlParameter(
				"@ShipToAddressID", SqlDbType.Int, 4);

			parameterShipToAddressID.Value = shipToAddressID;
			myCommand.Parameters.Add(parameterShipToAddressID);

			SqlParameter parameterOrderID = new SqlParameter("@OrderID",
															 SqlDbType.Int, 4);

			parameterOrderID.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterOrderID);

			// Open the connection and execute the Command
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			finally
			{
				if (myConnection.State != ConnectionState.Closed)
					myConnection.Close();
			}

			// Return the OrderID
			return (int)parameterOrderID.Value;
		}
	}
}