/// <summary>
/// Read an order from the database
/// </summary>
/// <param name="orderId">Order Id</param>
/// <returns>Details of the Order</returns>
public OrderInfo GetOrder(int orderId)
{
//Create a parameter
OracleParameter parm = new OracleParameter(PARM_ORDER_ID, OracleType.Number);
parm.Value = orderId;
//Execute a query to read the order
using (OracleDataReader rdr = OracleHelper.ExecuteReader(OracleHelper.ConnectionStringOrderDistributedTransaction, CommandType.Text, SQL_SELECT_ORDER, parm)) {
if (rdr.Read()) {
//Generate an order header from the first row
AddressInfo billingAddress = new AddressInfo(rdr.GetString(5), rdr.GetString(6), rdr.GetString(7), rdr.GetString(8), rdr.GetString(9), rdr.GetString(10), rdr.GetString(11), rdr.GetString(12), null, "email");
AddressInfo shippingAddress = new AddressInfo(rdr.GetString(13), rdr.GetString(14), rdr.GetString(15), rdr.GetString(16), rdr.GetString(17), rdr.GetString(18), rdr.GetString(19), rdr.GetString(20), null, "email");
OrderInfo order = new OrderInfo(orderId, rdr.GetDateTime(0), rdr.GetString(1), null, billingAddress, shippingAddress, rdr.GetDecimal(21), null, null);
IList<LineItemInfo> lineItems = new List<LineItemInfo>();
LineItemInfo item = null;
//Create the lineitems from the first row and subsequent rows
do {
item = new LineItemInfo(rdr.GetString(22), string.Empty, rdr.GetInt32(23), rdr.GetInt32(24), rdr.GetDecimal(25));
lineItems.Add(item);
} while (rdr.Read());
order.LineItems = new LineItemInfo[lineItems.Count];
lineItems.CopyTo(order.LineItems, 0);
return order;
}
}
return null;
}
/// <summary>
/// Read an order from the database
/// </summary>
/// <param name="orderId"></param>
/// <returns></returns>
public OrderInfo GetOrder(int orderId)
{
//Create a parameter
SqlParameter parm = new SqlParameter(PARM_ORDER_ID, SqlDbType.Int);
parm.Value = orderId;
//Execute a query to read the order
using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.ConnectionString, CommandType.Text, SQL_SELECT_ORDER, parm)) {
if (rdr.Read()) {
//Generate an order header from the first row
CreditCardInfo creditCard = new CreditCardInfo(rdr.GetString(2), rdr.GetString(3), rdr.GetString(4));
AddressInfo billingAddress = new AddressInfo(rdr.GetString(5), rdr.GetString(6), rdr.GetString(7), rdr.GetString(8), rdr.GetString(9), rdr.GetString(10), rdr.GetString(11), rdr.GetString(12), null);
AddressInfo shippingAddress = new AddressInfo(rdr.GetString(13), rdr.GetString(14), rdr.GetString(15), rdr.GetString(16), rdr.GetString(17), rdr.GetString(18), rdr.GetString(19), rdr.GetString(20), null);
OrderInfo order = new OrderInfo(orderId, rdr.GetDateTime(0), rdr.GetString(1), creditCard, billingAddress, shippingAddress, rdr.GetDecimal(21));
ArrayList lineItems = new ArrayList();
LineItemInfo item = null;
//Create the lineitems from the first row and subsequent rows
do{
item = new LineItemInfo(rdr.GetString(22), string.Empty, rdr.GetInt32(23), rdr.GetInt32(24), rdr.GetDecimal(25));
lineItems.Add(item);
}while(rdr.Read());
order.LineItems = (LineItemInfo[])lineItems.ToArray(typeof(LineItemInfo));
return order;
}
}
return null;
}
/// <summary>
/// Reduce the current quantity in stock for an order's lineitems
/// </summary>
/// <param name="items">An array of order line items</param>
public void TakeStock(LineItemInfo[] items)
{
// Get an instance of the Inventory DAL using the DALFactory
IInventory dalc = PetShop.DALFactory.Inventory.Create();
// Reduce the stock level in the data store
dalc.TakeStock(items);
}
/// <summary>
/// Constructor with specified initial values
/// </summary>
/// <param name="orderId">Unique identifier</param>
/// <param name="date">Order date</param>
/// <param name="userId">User placing order</param>
/// <param name="creditCard">Credit card used for order</param>
/// <param name="billing">Billing address for the order</param>
/// <param name="shipping">Shipping address for the order</param>
/// <param name="total">Order total value</param>
/// <param name="line">Ordered items</param>
/// <param name="authorization">Credit card authorization number</param>
public OrderInfo(int orderId, DateTime date, string userId, CreditCardInfo creditCard, AddressInfo billing, AddressInfo shipping, decimal total, LineItemInfo[] line, Nullable<int> authorization)
{
this.orderId = orderId;
this.date = date;
this.userId = userId;
this.creditCard = creditCard;
this.billingAddress = billing;
this.shippingAddress = shipping;
this.orderTotal = total;
this.lineItems = line;
this.authorizationNumber = authorization;
}
/// <summary>
/// Function to update inventory based on purchased items
/// Internally the function uses a batch query so the command is only sent to the database once
/// </summary>
/// <param name="items">Array of items purchased</param>
public void TakeStock(LineItemInfo[] items)
{
SqlParameter[] inventoryParms;
SqlCommand cmd = new SqlCommand();
//Open a connection
using (SqlConnection conn = new SqlConnection(SQLHelper.ConnectionString)) {
String strSQL = null;
int i = 0;
//Append a statement to the batch for each item in the array
foreach (LineItemInfo item in items) {
strSQL = strSQL + SQL_TAKE_INVENTORY;
inventoryParms = GetInventoryParameters(i);
strSQL = strSQL + "@Quantity" +i + " WHERE ItemId = @ItemId" + i+ ";";
//Bind parameters
inventoryParms[0].Value = item.Quantity;
inventoryParms[1].Value = item.ItemId;
foreach (SqlParameter parm in inventoryParms)
cmd.Parameters.Add(parm);
i++;
}
// Open the connection
conn.Open();
//Set up the command
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSQL;
//Execute the query
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
}
/// <summary>
/// Function to update inventory based on purchased items
/// Internally the function uses a batch query so the command is only sent to the database once
/// </summary>
/// <param name="items">Array of items purchased</param>
public void TakeStock(LineItemInfo[] items)
{
// Total number of parameters = (2 * number of lines)
int numberOfParameters = 2 * items.Length;
// Create a parameters array based on the number of items in the array
OracleParameter[] completeOrderParms = new OracleParameter[numberOfParameters];
// Create a string builder to hold the entire query
// Start the PL/SQL block
StringBuilder finalSQLQuery = new StringBuilder("BEGIN ");
int index = 0;
int i = 1;
// go through each item and bind parametes for the batch statement
foreach (LineItemInfo item in items) {
completeOrderParms[index] = new OracleParameter(":Quantity" + i, OracleType.Number);
completeOrderParms[index++].Value = item.Quantity;
completeOrderParms[index] = new OracleParameter(":ItemId" + i, OracleType.Char, 10);
completeOrderParms[index++].Value = item.ItemId;
// Append the current item query to the batch statement
finalSQLQuery.Append(string.Format(SQL_TAKE_INVENTORY, i));
finalSQLQuery.Append("; ");
i++;
}
// Close the PL/SQL block
finalSQLQuery.Append("END;");
// Finally execute the query
OracleHelper.ExecuteNonQuery(OracleHelper.ConnectionStringInventoryDistributedTransaction, CommandType.Text, finalSQLQuery.ToString(), completeOrderParms);
}
/// <summary>
/// Method to convert all cart items to order line items
/// </summary>
/// <returns>A new array of order line items</returns>
public LineItemInfo[] GetOrderLineItems()
{
LineItemInfo[] orderLineItems = new LineItemInfo[cartItems.Count];
int lineNum = 0;
foreach (CartItemInfo item in cartItems.Values)
orderLineItems[lineNum] = new LineItemInfo(item.ItemId, item.Name, ++lineNum, item.Quantity, item.Price);
return orderLineItems;
}
/// <summary>
/// Reduce the current quantity in stock for an order's lineitems
/// </summary>
/// <param name="items">An array of order line items</param>
public void TakeStock(LineItemInfo[] items)
{
// Reduce the stock level in the data store
dal.TakeStock(items);
}