/// <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); } }