/// <summary> /// Pat Banks /// Created: 2015/03/19 /// /// Takes data from the presentation layer and determines the results of attempting to add a booking /// </summary> /// <param name="bookingToAdd">Booking information from presentation Layer form</param> /// <returns>Results of adding the booking</returns> public ResultsEdit AddBookingResult(Booking bookingToAdd) { if (bookingToAdd.Quantity == 0) { return ResultsEdit.QuantityZero; } try { //calls method to add a booking and update itemListing Table with current number of guests int result = BookingAccessor.AddBooking(bookingToAdd); if (result == 2) { //update cache RefreshItemListingDetailsListCacheData(); return ResultsEdit.Success; } return ResultsEdit.DatabaseError; } catch (Exception ex) { throw ex; } }
/// <summary> /// Tony Noel /// Created: 2015/02/03 /// Selects a specific booking from the database /// </summary> /// <remarks> /// Tony Noel /// Updated: 2015/03/03 /// </remarks> /// <param name="BookingID">Takes an input of an int- the BookingID number to locate the requested record.</param> /// <returns>Output is a booking object to hold the booking record.</returns> public static Booking GetBooking(int BookingID) { //create Booking object to store info Booking BookingToGet = new Booking(); var conn = DatabaseConnection.GetDatabaseConnection(); string query = "spSelectBookingByID"; SqlCommand command = new SqlCommand(query, conn); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@bookingID", BookingID); //connect to db try { conn.Open(); SqlDataReader reader = command.ExecuteReader(); if (reader.HasRows == true) { while (reader.Read()) { BookingToGet.BookingID = reader.GetInt32(0); BookingToGet.GuestID = reader.GetInt32(1); BookingToGet.EmployeeID = reader.GetInt32(2); BookingToGet.ItemListID = reader.GetInt32(3); BookingToGet.Quantity = reader.GetInt32(4); BookingToGet.DateBooked = reader.GetDateTime(5); BookingToGet.Discount = reader.GetDecimal(6); BookingToGet.Active = reader.GetBoolean(7); BookingToGet.TicketPrice = reader.GetDecimal(8); BookingToGet.ExtendedPrice = reader.GetDecimal(9); BookingToGet.TotalCharge = reader.GetDecimal(10); } } else { throw new ApplicationException("BookingID does not match an ID on record."); } } catch (Exception) { throw; } finally { conn.Close(); } return BookingToGet; }
/// <summary> /// Tony Noel /// Created: 2015/02/03 /// a method used to insert a booking into the database /// </summary> /// <remarks> /// Pat Banks /// Updated: 2015/02/19 /// exception handling if add wasn't successful /// Pat Banks /// Updated: 2015/04/25 /// SP also updates the Current Number of guests based on the total number of bookings for that Item ID /// </remarks> /// <param name="toAdd">input- a Booking object to be inserted</param> /// <returns>Output is the number of rows affected by the insert; 2 is considered a success /// One row updated for the booking and one for the ItemListing CurrentNumGuests /// </returns> public static int AddBooking(Booking toAdd) { var conn = DatabaseConnection.GetDatabaseConnection(); var cmdText = "spInsertBookingUpdateListingNum"; var cmd = new SqlCommand(cmdText, conn); int rowsAffected = 0; //Set command type to stored procedure and add parameters cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@GuestID", toAdd.GuestID); cmd.Parameters.AddWithValue("@EmployeeID", toAdd.EmployeeID); cmd.Parameters.AddWithValue("@ItemListID", toAdd.ItemListID); cmd.Parameters.AddWithValue("@Quantity", toAdd.Quantity); cmd.Parameters.AddWithValue("@DateBooked", toAdd.DateBooked); cmd.Parameters.AddWithValue("@Discount", toAdd.Discount); cmd.Parameters.AddWithValue("@TicketPrice", toAdd.TicketPrice); cmd.Parameters.AddWithValue("@ExtendedPrice", toAdd.ExtendedPrice); cmd.Parameters.AddWithValue("@TotalCharge", toAdd.TotalCharge); try { conn.Open(); rowsAffected = (int)cmd.ExecuteNonQuery(); if (rowsAffected == 0) { throw new ApplicationException("Error adding new database entry"); } } catch (Exception) { throw; } finally { conn.Close(); } return rowsAffected; }
/// <summary> /// Pat Banks /// Created: 2015/03/19 /// Gathers form data to submit to database for changes /// </summary> /// <returns>booking of the new information</returns> private Booking GatherFormInformation() { //gets quantity from the up/down quantity field int qty = (int)(UdAddBookingQuantity.Value); //get discount from form decimal discount = (decimal)(UdDiscount.Value); //calculate values for the tickets decimal extendedPrice = _bookingManager.CalcExtendedPrice(CurrentBookingDetails.TicketPrice, qty); decimal totalPrice = _bookingManager.CalcTotalCharge(discount, extendedPrice); Booking editedBooking = new Booking(CurrentBookingDetails.BookingID, CurrentBookingDetails.GuestID, _eId, CurrentBookingDetails.ItemListID, qty, DateTime.Now, discount, CurrentBookingDetails.Active, CurrentBookingDetails.TicketPrice, extendedPrice, totalPrice); return editedBooking; }
public void TestBookingConstructor() { //booking object created booking = new Booking(); booking.GuestID = 100; booking.EmployeeID = 100; booking.ItemListID = 101; booking.Quantity = 2; booking.DateBooked = DateTime.Now; booking.TicketPrice = 1234m; booking.ExtendedPrice = 40m; booking.Discount = .1m; booking.TotalCharge = 36m; }
/// <summary> /// Tony Noel /// Created: 2015/02/11 /// a method to collect all information from the form /// Then after taking each variable and testing them in their specific validation method, parses them /// into the correct variable needed to be stored as a ItemListingDetails /// </summary> /// <remarks> /// Pat Banks /// Updated: 2015/03/11 /// Added up/down controls to allow for easier user data entry /// </remarks> private Booking GatherFormInformation() { ItemListingDetails selectedItemListing = GetSelectedItem(); //gets quantity from the up/down quantity field int qty = (int)(UdAddBookingQuantity.Value); //get discount from form decimal discount = (decimal)(UdDiscount.Value); //calculate values for the tickets decimal extendedPrice = _bookingManager.CalcExtendedPrice(selectedItemListing.Price, qty); decimal totalPrice = _bookingManager.CalcTotalCharge(discount, extendedPrice); Booking bookingToAdd = new Booking(CurrentInvoice.HotelGuestID, _eId, selectedItemListing.ItemListID, qty, DateTime.Now, selectedItemListing.Price, extendedPrice, discount, totalPrice); return bookingToAdd; }
/// <summary> /// Pat Banks /// created: 2015/04/22 /// Need confirmation from guest before can officially submit the booking /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnConfirm_Click(object sender, EventArgs e) { Booking webBookingToAdd = new Booking((int)foundGuest.HotelGuestID, 100, selectedItemListing.ItemListID, ticketQty, DateTime.Now, selectedItemListing.Price, extendedPrice, discount, totalPrice); ResultsEdit addResult = myManager.AddBookingResult(webBookingToAdd); switch (addResult) { case ResultsEdit.Success: showError("Thank You, " + foundGuest.GetFullName + ". <br>You have successfully signed up for:\n" + selectedItemListing.EventName + "."); clearFields(); gvListings.DataBind(); confirmDetails.Style.Add("display", "none"); ticketRequest.Style.Add("display", "block"); break; case ResultsEdit.ListingFull: showError("Sorry, that event is full!"); break; case ResultsEdit.DatabaseError: showError("Sorry, there was a problem registering for this event.\nPlease contact the front desk."); break; } }
/// <summary> /// Tony Noel /// Created: 2015/03/27 /// /// Deletes only the dummy booking record from the database for testing. /// </summary> /// <param name="testBook">The Booking Object used for testing</param> public static void testBook(Booking testBook) { //establish connection SqlConnection conn = DatabaseConnection.GetDatabaseConnection(); //write some query text string query = "DELETE FROM Booking WHERE GuestID = 100 AND EmployeeID = 100 AND ItemListID = 100 AND TotalCharge= 36"; //create a Sql Command SqlCommand cmd = new SqlCommand(query, conn); try { //this part must be in the try as it is attempting to establish connection. //open connection conn.Open(); //execute the command and capture the results to a SqlDataReader SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows == true) { reader.Read(); } /*else { var up = new ApplicationException("Your record could not be made."); throw up; }*/ } catch (Exception) { throw; } finally { conn.Close(); } }
/// <summary> /// Tony Noel /// Created: 2015/05/01 /// /// Used to grab a list of all bookings where the ItemListID links to a active ItemListing only. /// </summary> /// <returns>A List object of Booking objects</returns> public static List<Booking> GetAllBookings() { var result = new List<Booking>(); SqlConnection conn = DatabaseConnection.GetDatabaseConnection(); string query = "Select BookingID, GuestID, EmployeeID, Booking.ItemlistID, Quantity, DateBooked, Discount, Booking.Active, TicketPrice, ExtendedPrice, TotalCharge FROM Booking, ItemListing Where Booking.ItemListID = ItemListing.ItemListID AND ItemListing.Active = 1 AND Booking.Quantity != 0"; SqlCommand cmd = new SqlCommand(query, conn); try { conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { var myBook = new Booking(); myBook.BookingID = reader.GetInt32(0); myBook.GuestID = reader.GetInt32(1); myBook.EmployeeID = reader.GetInt32(2); myBook.ItemListID = reader.GetInt32(3); myBook.Quantity = reader.GetInt32(4); myBook.DateBooked = reader.GetDateTime(5); myBook.Discount = reader.GetDecimal(6); myBook.Active = reader.GetBoolean(7); myBook.TicketPrice = reader.GetDecimal(8); myBook.ExtendedPrice = reader.GetDecimal(9); myBook.TotalCharge = reader.GetDecimal(10); result.Add(myBook); } } else { var ex = new ApplicationException("Requested object did not match any records."); throw ex; } } catch (Exception) { throw; } finally { conn.Close(); } return result; }
/// <summary> /// Pat Banks /// Created: 2015/03/19 /// /// Gives the results of editing a booking to the presentation layer /// </summary> /// <remarks> /// Pat Banks /// Updated: 2015/03/30 /// /// Updated to include data cache refresh /// </remarks> /// <param name="originalQty">The amount of people who could sign up</param> /// <param name="editedBooking">The Booking object with the updated information</param> /// <returns>An enumerated result depicting pass or fail</returns> public ResultsEdit EditBookingResult(int originalQty, Booking editedBooking) { try { //A variable to hold the difference between the number of guests on the original reservation, and the old reservation int numGuestsDifference = SpotsReservedDifference(editedBooking.Quantity, originalQty); // creates an ItemListing object by retrieving the record of the specific object based on it's ItemListID ItemListingDetails originalItem = RetrieveItemListingDetailsList(editedBooking.ItemListID); //assigned the difference of the MaxNumGuests - currentNum of guests int quantityOffered = AvailableQuantity(originalItem.MaxNumGuests, originalItem.CurrentNumGuests); //If the quantity offered is 0, and the new quantity is going up from the original amount booked, alerts the staff and returns. if (quantityOffered == 0 && (numGuestsDifference > editedBooking.Quantity)) { return ResultsEdit.ListingFull; } //Method to check the number of guests added to a reservation against the available quantity for the event if (numGuestsDifference > quantityOffered) { return ResultsEdit.ListingFull; } if (editedBooking.Quantity == 0) { return ResultsEdit.QuantityZero; } //send the changes to the database int numRows = EditBooking(editedBooking); if (numRows == 2) { //refresh Data Cache RefreshItemListingDetailsListCacheData(); return ResultsEdit.Success; } return ResultsEdit.ChangedByOtherUser; } catch (Exception ex) { throw ex; } }
/// <summary> /// Tony Noel /// Created: 2015/02/05 /// /// EditBooking- a method used to update a booking through the data access layer to be added to the database /// As the BookingID number will not change or be updated in the database the method uses the same booking ID number to search /// the database through the Retrieve Booking method. This will pull the originally record into an object "oldOne". Then the /// original record and the new booking object that was passed to the method can both be passed to upDateBooking to be updated. /// </summary> /// <param name="newOne">Takes an input of a booking object</param> /// <returns> Returns an int- the number of rows affected, if add is successful</returns> public int EditBooking(Booking newOne) { Booking oldOne = RetrieveBooking(newOne.BookingID); var numRows = BookingAccessor.UpdateBooking(oldOne, newOne); return numRows; }
/// <summary> /// Tony Noel /// Created: 2015/02/03 /// Updates a booking in the database /// </summary> /// <remarks> /// Tony Noel /// Updated: 2015/03/02 /// Added discount, extended price, ticket price and total charge fields. /// /// Pat Banks /// Updated: 2015/04/25 /// StoredProcedure also updates the Current Number of guests based on the total number of bookings for that Item ID /// </remarks> /// <param name="oldOne">The original Booking object/values</param> /// <param name="toUpdate">The new booking object values to replace the old</param> /// <returns>Output is the rows affected by the update</returns> public static int UpdateBooking(Booking oldOne, Booking toUpdate) { var conn = DatabaseConnection.GetDatabaseConnection(); var cmdText = "spUpdateBookingUpdateListingNum"; var cmd = new SqlCommand(cmdText, conn); int rowsAffected = 0; //Set command type to stored procedure and add parameters cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@Quantity", toUpdate.Quantity); cmd.Parameters.AddWithValue("@Discount", toUpdate.Discount); cmd.Parameters.AddWithValue("@Active", toUpdate.Active); cmd.Parameters.AddWithValue("@TicketPrice", toUpdate.TicketPrice); cmd.Parameters.AddWithValue("@ExtendedPrice", toUpdate.ExtendedPrice); cmd.Parameters.AddWithValue("@TotalCharge", toUpdate.TotalCharge); cmd.Parameters.AddWithValue("@original_BookingID", oldOne.BookingID); cmd.Parameters.AddWithValue("@original_GuestID", oldOne.GuestID); cmd.Parameters.AddWithValue("@original_EmployeeID", oldOne.EmployeeID); cmd.Parameters.AddWithValue("@original_ItemListID", oldOne.ItemListID); cmd.Parameters.AddWithValue("@original_Quantity", oldOne.Quantity); cmd.Parameters.AddWithValue("@original_DateBooked", oldOne.DateBooked); cmd.Parameters.AddWithValue("@original_Discount", oldOne.Discount); cmd.Parameters.AddWithValue("@original_Active", oldOne.Active); cmd.Parameters.AddWithValue("@original_TicketPrice", oldOne.TicketPrice); cmd.Parameters.AddWithValue("@original_ExtendedPrice", oldOne.ExtendedPrice); cmd.Parameters.AddWithValue("@original_TotalCharge", oldOne.TotalCharge); try { conn.Open(); rowsAffected = cmd.ExecuteNonQuery(); } catch (Exception) { throw; } finally { conn.Close(); } return rowsAffected; }
public void TestBookingConstructor() { //booking object created booking = new Booking(guestID, empID, itemID, bQuantity, dateBooked, ticket, extended, discount, total); }
public void TestUpdateBookingAccess() { // Updates the dummy booking in the database, first captures the dummy bookingID from database //using a TestAccessor BookingID = TestCleanupAccessor.GetBooking(); //Assigns one booking object to be the old record and one to be the new record Booking old = BookingAccessor.GetBooking(BookingID); Booking newB = new Booking(guestID, empID, itemID, 3, dateBooked, ticket, extended, discount, total); //Updates the old with the new quantity int rows = BookingAccessor.UpdateBooking(old, newB); int expected = 3; //Grabs the record to test and see if the update went through Booking toCheck = BookingAccessor.GetBooking(BookingID); Assert.AreEqual(expected, toCheck.Quantity); }