/// <summary>
/// This method creates a new order item for the specified order
///
/// Test Case: Req-7
/// </summary>
/// <param name="new_order_item">New Cust Order Item object</param>
/// <param name="order_id">The order id related to the products</param>
private void InsertOrderItem(NewCustOrderItem new_order_item, int order_id)
{
ProductDAL prodDAL = new ProductDAL();
using (SqlConnection conn = new SqlConnection(defaultConnection))
{
string query = "insert into Cust_Order_Item (order_id, product_id, qty, sub_total) " +
"VALUES (@order_id, @product_id, @qty, @sub_total)";
SqlCommand myCmd = new SqlCommand(query, conn);
if (util.IsNumeric(order_id))
{
myCmd.Parameters.AddWithValue("@order_id", order_id);
}
else
{
myCmd.Parameters.AddWithValue("@order_id", DBNull.Value);
}
if (util.IsNumeric(new_order_item.product_id))
{
myCmd.Parameters.AddWithValue("@product_id", new_order_item.product_id);
}
else
{
myCmd.Parameters.AddWithValue("@product_id", DBNull.Value);
}
if (util.IsNumeric(new_order_item.qty))
{
myCmd.Parameters.AddWithValue("@qty", new_order_item.qty);
myCmd.Parameters.AddWithValue("@sub_total", new_order_item.qty * prodDAL.GetProductSalePrice(new_order_item.product_id));
}
else
{
myCmd.Parameters.AddWithValue("@qty", DBNull.Value);
}
conn.Open();
myCmd.ExecuteNonQuery();
}
}
/// <summary>
/// This method creates the order details in the database
///
/// Test Case: Req-6, Req-7
/// </summary>
/// <param name="new_order">New Cust Order object with new order information</param>
/// <returns>Status of the order</returns>
public OrderStatus CreateOrder(NewCustOrder new_order)
{
ProductDAL prodDAL = new ProductDAL();
using (SqlConnection conn = new SqlConnection(defaultConnection))
{
decimal total_price = 0;
string query = "insert into Cust_Order (customer_id, location_id, total_price, submit_ts, order_notes) " +
"VALUES (@customer_id, @location_id, @total_price, @submit_ts, @order_notes) " +
"select scope_identity()";
SqlCommand myCmd = new SqlCommand(query, conn);
if (util.IsNumeric(new_order.customer_id))
{
myCmd.Parameters.AddWithValue("@customer_id", new_order.customer_id);
}
else
{
myCmd.Parameters.AddWithValue("@customer_id", 0); // indicates direct customer
}
LocationCoordinates delivery_location = GetNewOrderDeliveryLocation(new_order.location);
myCmd.Parameters.AddWithValue("@location_id", delivery_location.location_id);
// generate the total price of the order
foreach (NewCustOrderItem oi in new_order.order_items)
{
total_price = total_price + oi.qty * prodDAL.GetProductSalePrice(oi.product_id);
}
myCmd.Parameters.AddWithValue("@total_price", total_price);
myCmd.Parameters.AddWithValue("@submit_ts", DateTime.Now);
myCmd.Parameters.AddWithValue("@order_notes", new_order.order_notes);
conn.Open();
// id is the newly generated order id
int order_id = (int)(decimal)myCmd.ExecuteScalar();
foreach (NewCustOrderItem oi in new_order.order_items)
{
InsertOrderItem(oi, order_id);
}
// Key = basket_id; Value = distance to delivery location
KeyValuePair <int, int> basket_kvp = FindAvailableBasket(delivery_location, order_id);
// update the order with basket and delivery info
UpdateCustOrderWithBasket(order_id, basket_kvp.Key, basket_kvp.Value);
// if this is a Direct Customer include the delivery information
if (new_order.customer_id == 0)
{
DeliverOrder(order_id);
}
// build a staus update to send to the customer
OrderStatus ord_status = new OrderStatus();
ord_status.order_id = order_id;
if (new_order.customer_id == 0)
{
ord_status.order_status = "Your order has been delivered";
}
else
{
ord_status.order_status = "Your order is being processed";
}
ord_status.customer_name = GetCustomerName((int)new_order.customer_id);
//ord_status.delivery_location = "Bay: " + new_order.location.bay + " Row: " + new_order.location.row_no + " Seat: " + new_order.location.seat_no;
ord_status.delivery_time = CalculateDeliveryTime(basket_kvp.Value).ToString();
return(ord_status);
}
}