/// <summary>
/// This method takes the bay, row no and seat no to determine the location id, location cell id, x and y coordinates
///
/// Test Case: Req-5, Req-6
/// </summary>
/// <param name="location">New Order Location object that contains the bay, row_no and seat_no</param>
/// <returns>Location Coordinates (i.e. location_id, location_cell_id</returns>
private LocationCoordinates GetNewOrderDeliveryLocation(NewOrderLocation location)
{
var delivery_location = new LocationCoordinates();
using (SqlConnection conn = new SqlConnection(defaultConnection))
{
string query = "select l.location_id, l.location_cell_id, lc.x_coord, lc.y_coord " +
"from location l inner join location_cell lc on l.location_cell_id = lc.location_cell_id " +
"where l.bay = @bay and l.row_no = @row_no and l.seat_no = @seat_no";
using (SqlCommand myCmd = new SqlCommand(query, conn))
{
SqlDataReader dr = null;
conn.Open();
myCmd.Parameters.AddWithValue("@bay", location.bay);
myCmd.Parameters.AddWithValue("@row_no", location.row_no);
myCmd.Parameters.AddWithValue("@seat_no", location.seat_no);
dr = myCmd.ExecuteReader();
while (dr.Read())
{
delivery_location.location_id = int.Parse(dr["location_id"].ToString());
delivery_location.location_cell_id = int.Parse(dr["location_cell_id"].ToString());
delivery_location.x = int.Parse(dr["x_coord"].ToString());
delivery_location.y = int.Parse(dr["y_coord"].ToString());
}
return(delivery_location);
}
}
}
/// <summary>
/// This method determines which basket to give the order to.
///
/// Test Case: Req-8, Req-9, Req-11
/// </summary>
/// <param name="delivery_location">Object containing the delivery location</param>
/// <param name="order_id">The related order id</param>
/// <returns>KeyValuePair: basket_id, distance to customer</returns>
private KeyValuePair <int, int> FindAvailableBasket(LocationCoordinates delivery_location, int order_id)
{
int basket_id = 0;
int min_distance = 9999;
// KeyValuePair - Key = bsaket_id; Value = location_cell_id
var basketList = new List <KeyValuePair <int, int> >();
var basket = new BasketDAL();
var locationDAL = new LocationDAL();
basketList = basket.BasketHasStock(order_id);
// get basket that is closest to delivery location
foreach (KeyValuePair <int, int> kvp in basketList)
{
var basket_location = locationDAL.GetLocationCell(kvp.Value);
int basket_distance = locationDAL.CalculateDistanceToCustomer(delivery_location.x, delivery_location.y, basket_location.x_coord, basket_location.y_coord);
if (basket_distance < min_distance)
{
min_distance = basket_distance;
basket_id = kvp.Key;
}
}
return(new KeyValuePair <int, int>(basket_id, min_distance));
}
/// <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);
}
}
