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