//Justin Pennington 2/14/15
/// <summary>
/// Marks passed Lists as inactive
/// </summary>
/// <param name="inLists">List to be deactivated</param>
/// <returns>
/// rowsAffected
/// </returns>
public static int DeleteLists(Lists inLists)
{
//make connection to Database
var conn = DatabaseConnection.GetDatabaseConnection();
var cmdText = "spDeleteLists";
var cmd = new SqlCommand(cmdText, conn);
var rowsAffected = 0;
//set up parameters for the stored Procedure
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@original_SupplierID", inLists.SupplierID);
cmd.Parameters.AddWithValue("@original_ItemListID", inLists.ItemListID);
cmd.Parameters.AddWithValue("@original_DateListed", inLists.DateListed);
try
{
conn.Open();
rowsAffected = cmd.ExecuteNonQuery();
if (rowsAffected == 0)
{
throw new ApplicationException("Concurrency Violation");
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(rowsAffected); // needs to be rows affected
}
/// <summary>
/// Justin Pennington
/// Created: 2015/02/14
/// Changes the event from active to inactive
/// </summary>
/// <param name="eventToBeDeleted">The Event to be set inactive</param>
/// <returns>returns number of rows affected</returns>
public static int DeleteEventItem(Event eventToBeDeleted)
{
var conn = DatabaseConnection.GetDatabaseConnection();
var cmdText = "spDeleteEventItem";
var cmd = new SqlCommand(cmdText, conn);
var rowsAffected = 0;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EventItemName", eventToBeDeleted.EventItemName);
cmd.Parameters.AddWithValue("@EventItemID", eventToBeDeleted.EventItemID);
cmd.Parameters.AddWithValue("@EventTypeID", eventToBeDeleted.EventTypeID);
cmd.Parameters.AddWithValue("@Transportation", eventToBeDeleted.Transportation);
cmd.Parameters.AddWithValue("@EventDescription", eventToBeDeleted.Description);
cmd.Parameters.AddWithValue("@EventOnsite", eventToBeDeleted.OnSite);
try
{
conn.Open();
rowsAffected = cmd.ExecuteNonQuery();
if (rowsAffected == 0)
{
throw new ApplicationException("Concurrency Violation");
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(rowsAffected); // needs to be rows affected
}
//Justin Pennington 2/14/15
/// <summary>
/// Adds new lists object
/// </summary>
/// <param name="newLists">Object holding new listing data</param>
/// <returns>
/// rowsAffected
/// </returns>
public static int AddLists(Lists newLists)
{
//Connect to Database
var conn = DatabaseConnection.GetDatabaseConnection();
var cmdText = "spInsertListsItem";
var cmd = new SqlCommand(cmdText, conn);
var rowsAffected = 0;
//Set up Parameters for the Stored Procedures
cmd.Parameters.AddWithValue("@SupplierID", newLists.SupplierID);
cmd.Parameters.AddWithValue("@ItemListID", newLists.ItemListID);
cmd.Parameters.AddWithValue("@DateListed", newLists.DateListed);
try
{
conn.Open();
rowsAffected = cmd.ExecuteNonQuery();
if (rowsAffected == 0)
{
throw new ApplicationException("Concurrency Violation");
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(rowsAffected);
}
/// <summary>
/// Updates database information and compares old information with current database values
/// to ensure that the database has not been modified in the meantime
/// </summary>
/// <param name="oldList">Old values to ensure data has not been modified</param>
/// <param name="newList">New values to be written to database</param>
/// <returns>
/// Execution command
/// </returns>
public static int updateBookingLineItem(BookingLineItem newItem, BookingLineItem oldItem)
{
var conn = DatabaseConnection.GetDatabaseConnection();
string sql = @"spBookingLineItemInsert";
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddWithValue("@BookingID", newItem.BookingID);
command.Parameters.AddWithValue("@ItemListID", newItem.ItemListID);
command.Parameters.AddWithValue("@Quantity", newItem.Quantity);
command.Parameters.AddWithValue("@original_BookingID", oldItem.BookingID);
command.Parameters.AddWithValue("@original_ItemListID", oldItem.ItemListID);
command.Parameters.AddWithValue("@original_Quantity", oldItem.Quantity);
try
{
conn.Open();
return(command.ExecuteNonQuery());
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
}
/// <summary>
/// Justin Pennington
/// Created: 2015/02/14
///
/// Updates an EventType object and database record
/// </summary>
/// </summary>
/// <param name="oldEventType">The EventType object to be updated</param>
/// <param name="newEventType">The EventType object with the updated changes</param>
/// <returns>Returns the number of rows affected (should be 1)</returns>
public static int UpdateEventType(EventType oldEventType, EventType newEventType)
{
var conn = DatabaseConnection.GetDatabaseConnection();
var cmdText = "spUpdateEventType";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
var rowsAffected = 0;
// set command type to stored procedure and add parameters
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EventName", newEventType.EventName);
cmd.Parameters.AddWithValue("@originalEventTypeID", oldEventType.EventTypeID);
cmd.Parameters.AddWithValue("@originalEventName", oldEventType.EventName);
try
{
conn.Open();
rowsAffected = cmd.ExecuteNonQuery();
if (rowsAffected == 0)
{
throw new ApplicationException("Concurrency Violation");
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(rowsAffected);
}
/// <summary>
/// Justin Pennington
/// Created: 2015/02/14
/// Archives an event type record
/// </summary>
/// <param name="eventTypeToArchive">The EventType object to be archived</param>
/// <returns>returns number of rows affected</returns>
public static int DeleteEventType(EventType eventTypeToArchive)
{
var conn = DatabaseConnection.GetDatabaseConnection();
var cmdText = "spArchiveEventType";
var cmd = new SqlCommand(cmdText, conn);
var rowsAffected = 0;
cmd.CommandType = CommandType.StoredProcedure;
//Set up parameters for EventType
cmd.Parameters.AddWithValue("@EventTypeID", eventTypeToArchive.EventTypeID);
try
{
conn.Open();
rowsAffected = cmd.ExecuteNonQuery();
if (rowsAffected == 0)
{
throw new ApplicationException("Concurrency Violation");
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(rowsAffected);
}
/// <summary>
/// Ryan Blake
/// Created: 2015/02/12
/// Method takes in a parameter of newEmployee
/// Database is queried using stored procedure and looks for matching
/// information from the object passed to the method
/// </summary>
/// <remarks>
/// Miguel Santana
/// Updated: 2015/02/26
/// Renamed stored procedure to spInsertEmployee
/// </remarks>
/// <param name="newEmployee">Employee object to add to databse</param>
/// <exception cref="ApplicationException">Exception is thrown if no rows affected were returned</exception>
/// /// <returns>Number of rows affected</returns>
public static int AddEmployee(Employee newEmployee)
{
int rowsAffected = 0;
var conn = DatabaseConnection.GetDatabaseConnection();
var cmdText = "spInsertEmployee";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@firstName", newEmployee.FirstName);
cmd.Parameters.AddWithValue("@lastName", newEmployee.LastName);
cmd.Parameters.AddWithValue("@empPassword", newEmployee.Password);
cmd.Parameters.AddWithValue("@empLevel", newEmployee.Level);
cmd.Parameters.AddWithValue("@active", newEmployee.Active);
try
{
conn.Open();
rowsAffected = cmd.ExecuteNonQuery();
if (rowsAffected == 0)
{
throw new ApplicationException("There was a problem adding the employee to the database. Please try again.");
}
}
catch (Exception)
{
throw;
}
return(rowsAffected);
}
/// <summary>
/// Bryan Hurst
/// Created: 4/23/2015
/// Method for deletion of test records created with the unit tests
/// </summary>
/// <param name="TestListing">The ItemListing added during testing -- to be deleted</param>
/// <returns>Number of rows affected</returns>
public static int DeleteItemListingTestItem(ItemListing TestListing)
{
var conn = DatabaseConnection.GetDatabaseConnection();
var cmdText = "spDeleteTestItemListing";
var cmd = new SqlCommand(cmdText, conn);
var rowsAffected = 0;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EndDate", TestListing.EndDate);
try
{
conn.Open();
rowsAffected = cmd.ExecuteNonQuery();
if (rowsAffected == 0)
{
throw new ApplicationException("Concurrency Violation");
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(rowsAffected); // needs to be rows affected
}
/// <summary>
/// Bryan Hurst
/// Created: 2015/04/23
/// Method for the deletion of test login records in the database
/// </summary>
/// <param name="supplierLoginToDelete">The SupplierLogin object used for testing -- to be deleted</param>
/// <returns>number of rows affected</returns>
public static int DeleteTestSupplierLogin(SupplierLogin supplierLoginToDelete)
{
var conn = DatabaseConnection.GetDatabaseConnection();
string storedProcedure = "spDeleteTestSupplierLogin";
var cmd = new SqlCommand(storedProcedure, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserName", supplierLoginToDelete.UserName);
int rowsAffected;
try
{
conn.Open();
rowsAffected = cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(rowsAffected);
}
/// <summary>
/// Rose Steffensmeier
/// Created: 2015/04/03
/// </summary>
/// <remarks>
/// Rose Steffensmeier
/// Updated: 2015/04/13
/// Added new paramenter
/// </remarks>
/// <param name="userName">The username the supplier wants.</param>
/// <exception cref="SqlException">Goes off if unable to connect to the database or the username is already taken.</exception>
/// <returns>The number of rows that were affected. Should never be greater than one.</returns>
public int AddSupplierLogin(String userName, int supplierID)
{
int rowsAdded = 0;
var conn = DatabaseConnection.GetDatabaseConnection();
string query = "spInsertSupplierLogin";
var cmd = new SqlCommand(query, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@userName", userName);
cmd.Parameters.AddWithValue("@supplierID", supplierID);
try
{
conn.Open();
rowsAdded = cmd.ExecuteNonQuery();
return(rowsAdded);
}
catch (SqlException)
{
throw;
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
}
/// <summary>
/// Pat Banks
/// Created: 2015/03/03
/// Creates a connection with database and
/// calls the stored procedure spArchiveInvoiceGuestBooking
/// that updates database with information to archive an invoice, hotel guest and any bookings for that guest
/// </summary>
/// <param name="originalInvoice">invoice that was fetched from database - used to check for concurrency errors</param>
/// <param name="updatedInvoice">information that needs to be updated in the database</param>
/// <returns>Number of rows affected</returns>
public int ArchiveGuestInvoice(int GuestID)
{
var conn = DatabaseConnection.GetDatabaseConnection();
var cmdText = "spArchiveInvoiceGuestBooking";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
var numRows = 0;
//parameters for stored procedure
cmd.Parameters.AddWithValue("@GuestID", GuestID);
//connect to db
try
{
conn.Open();
numRows = cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return numRows;
}
/// <summary>
/// Rose Steffensmeier
/// Created: 2015/04/03
/// Updates a supplier's password
/// </summary>
/// <param name="newPassword">The string containing the new password for a supplier</param>
/// <param name="oldLogin">The SupplierLogin object matching the Supplier whose login information is being updated</param>
/// <returns>number of rows affected</returns>
public int UpdateSupplierPassword(string newPassword, SupplierLogin oldLogin)
{
var conn = DatabaseConnection.GetDatabaseConnection();
string storedProcedure = "spUpdateSupplierPassword";
var cmd = new SqlCommand(storedProcedure, conn);
cmd.CommandType = CommandType.StoredProcedure;
//Updated Supplier Info:
cmd.Parameters.AddWithValue("@Password", newPassword);
//Old Supplier Info
cmd.Parameters.AddWithValue("@original_UserName", oldLogin.UserName);
cmd.Parameters.AddWithValue("@original_Password", oldLogin.UserPassword);
cmd.Parameters.AddWithValue("@original_SupplierID", oldLogin.SupplierID);
int rowsAffected;
try
{
conn.Open();
rowsAffected = cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(rowsAffected);
}
/// <summary>
/// Marks passed BookingLineItem as inactive
/// </summary>
/// <param name="itemToDelete"></param>
/// <returns>
/// Execution command
/// </returns>
public static int deleteBookingLineItem(BookingLineItem itemToDelete)
{
var conn = DatabaseConnection.GetDatabaseConnection();
string sql = @"spBookingLineItemSelectSingle";
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddWithValue("@BookingID", itemToDelete.BookingID);
command.Parameters.AddWithValue("@ItemListID", itemToDelete.ItemListID);
command.Parameters.AddWithValue("@Quantity", itemToDelete.Quantity);
try
{
conn.Open();
return(command.ExecuteNonQuery());
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
}
/// <summary>
/// Justin Pennington
/// Created: 2015/02/14
///
/// Add the event type to the database
/// </summary>
/// <param name="newEventType">String containing the name of a new EventType</param>
/// <returns>number of rows affected: 0 fails and a 1 for success</returns>
public static int AddEventType(string newEventType)
{
var conn = DatabaseConnection.GetDatabaseConnection();
var cmdText = "spInsertEventType";
var cmd = new SqlCommand(cmdText, conn);
var rowsAffected = 0;
//set up parameters for EventType
cmd.Parameters.AddWithValue("@EventName", newEventType);
cmd.CommandType = CommandType.StoredProcedure;
try
{
conn.Open();
rowsAffected = cmd.ExecuteNonQuery();
if (rowsAffected == 0)
{
throw new ApplicationException("Concurrency Violation");
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(rowsAffected);
}
/// <summary>
/// Tyler Collins
/// Created: 2015/02/11
/// DELETEs (Sets Boolean Active field to false) an ItemListing record in the Database using a Stored Procedure.
/// </summary>
/// <remarks>
/// Tyler Collins
/// Updated: 2015/02/26
/// Now up to date with most recent ItemListing object class
/// </remarks>
/// <param name="itemListingToDelete">Requires the ItemListing object which matches the record to be DELETED in the Database.</param>
/// <returns>Returns the number of rows affected.</returns>
public static int DeleteItemListing(ItemListing itemListingToDelete)
{
var conn = DatabaseConnection.GetDatabaseConnection();
string storedProcedure = "spDeleteItemListing";
var cmd = new SqlCommand(storedProcedure, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ItemListID", itemListingToDelete.ItemListID);
cmd.Parameters.AddWithValue("@StartDate", itemListingToDelete.StartDate);
cmd.Parameters.AddWithValue("@EndDate", itemListingToDelete.EndDate);
cmd.Parameters.AddWithValue("@EventItemID", itemListingToDelete.EventID);
cmd.Parameters.AddWithValue("@Price", itemListingToDelete.Price);
cmd.Parameters.AddWithValue("@MaxNumGuests", itemListingToDelete.MaxNumGuests);
cmd.Parameters.AddWithValue("@CurrentNumGuests", itemListingToDelete.CurrentNumGuests);
cmd.Parameters.AddWithValue("@SupplierID", itemListingToDelete.SupplierID);
int rowsAffected;
try
{
conn.Open();
rowsAffected = cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(rowsAffected);
}
/// <summary>
/// Justin Pennington
/// Created: 2015/02/14
/// Gets an eventTypeID, retrieves data from databases
/// </summary>
/// <param name="eventTypeID">String containing the EventTypeID matching a specific EventType record/object</param>
/// <returns>Returns an EventType object</returns>
public static EventType GetEventType(string eventTypeID)
{
EventType theEventType = new EventType();
// set up the database call
var conn = DatabaseConnection.GetDatabaseConnection();
string cmdText = "spSelectEventType";
var cmd = new SqlCommand(cmdText, conn);
cmd.Parameters.AddWithValue("@EventTypeID", eventTypeID);
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows == true)
{
theEventType.EventTypeID = reader.GetInt32(0);
theEventType.EventName = reader.GetString(1);
}
else
{
var ax = new ApplicationException("Data not found!");
throw ax;
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(theEventType);
}
/// <summary>
/// Created by Justin Pennington 2015/02/25
/// Adds a SupplierFeedbackRecord to the database
/// </summary>
/// <param name="newFeedbackRecord">Contains values to be added to the database</param>
/// <returns>rowsAffected (int)</returns>
public static int AddSupplierFeedbackRecord(SupplierFeedbackRecord newFeedbackRecord)
{
//Connect To Database
var conn = DatabaseConnection.GetDatabaseConnection();
var cmdText = "spInsertSupplierFeedbackRecord";
var cmd = new SqlCommand(cmdText, conn);
var rowsAffected = 0;
cmd.CommandType = CommandType.StoredProcedure;
//Set up Parameters For the Stored Procedure
cmd.Parameters.AddWithValue("@SupplierID", newFeedbackRecord.SupplierID);
cmd.Parameters.AddWithValue("@EmployeeID", newFeedbackRecord.EmployeeID);
cmd.Parameters.AddWithValue("@Rating", newFeedbackRecord.Rating);
cmd.Parameters.AddWithValue("@Notes", newFeedbackRecord.RatingNotes);
try
{
conn.Open();
rowsAffected = cmd.ExecuteNonQuery();
if (rowsAffected == 0)
{
throw new ApplicationException("Concurrency Violation");
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(rowsAffected);
}
/// <summary>
/// Created by Justin Pennington 2015/02/25
/// Deletes a SupplierFeedbackRecord from the database (no archive setting as of 2015/02/25)
/// </summary>
/// <remarks>
/// </remarks>
/// <param name="supplierFeedback">A Supplier feedback record Object that contain all the information to be deleted</param>
/// <exception cref="SQLException">Delete Fails</exception>
/// <returns>rowsAffected (int)</returns>
public static int DeleteSupplierFeedbackRecord(SupplierFeedbackRecord supplierFeedback)
{
var conn = DatabaseConnection.GetDatabaseConnection();
var cmdText = "spDeleteSupplierFeedbackRecord";
var cmd = new SqlCommand(cmdText, conn);
var rowsAffected = 0;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@RatingID", supplierFeedback.RatingID);
cmd.Parameters.AddWithValue("@SupplierID", supplierFeedback.SupplierID);
cmd.Parameters.AddWithValue("@EmployeeID", supplierFeedback.EmployeeID);
cmd.Parameters.AddWithValue("@Rating", supplierFeedback.Rating);
cmd.Parameters.AddWithValue("@Notes", supplierFeedback.RatingNotes);
try
{
conn.Open();
rowsAffected = cmd.ExecuteNonQuery();
if (rowsAffected == 0)
{
throw new ApplicationException("Concurrency Violation");
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(rowsAffected); // needs to be rows affected
}
/// <summary>
/// Matt Lapka
/// Created: 2015/02/08
/// Updates an existing Supplier Application Record already in the Database
/// </summary>
/// <param name="oldApplication">A SupplierApplication Object that contains all the information of the record to be changed</param>
/// <param name="newApplication">A SupplierApplication Object that contains all the information to change in the record</param>
/// <returns>int reflecting number of rows affected</returns>
public static int UpdateSupplierApplication(SupplierApplication oldApplication, SupplierApplication newApplication)
{
var conn = DatabaseConnection.GetDatabaseConnection();
var cmdText = "spUpdateSupplierApplication";
var cmd = new SqlCommand(cmdText, conn);
var rowsAffected = 0;
// set command type to stored procedure and add parameters
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@CompanyName", newApplication.CompanyName);
cmd.Parameters.AddWithValue("@CompanyDescription", newApplication.CompanyDescription);
cmd.Parameters.AddWithValue("@FirstName", newApplication.FirstName);
cmd.Parameters.AddWithValue("@LastName", newApplication.LastName);
cmd.Parameters.AddWithValue("@Address1", newApplication.Address1);
cmd.Parameters.AddWithValue("@Address2", newApplication.Address2);
cmd.Parameters.AddWithValue("@Zip", newApplication.Zip);
cmd.Parameters.AddWithValue("@PhoneNumber", newApplication.PhoneNumber);
cmd.Parameters.AddWithValue("@EmailAddress", newApplication.EmailAddress);
cmd.Parameters.AddWithValue("@ApplicationDate", newApplication.ApplicationDate);
cmd.Parameters.AddWithValue("@ApplicationStatus", newApplication.ApplicationStatus);
cmd.Parameters.AddWithValue("@LastStatusDate", newApplication.LastStatusDate);
cmd.Parameters.AddWithValue("@Remarks", newApplication.Remarks);
cmd.Parameters.AddWithValue("@originalApplicationID", oldApplication.ApplicationID);
cmd.Parameters.AddWithValue("@originalCompanyName", oldApplication.CompanyName);
cmd.Parameters.AddWithValue("@originalCompanyDescription", oldApplication.CompanyDescription);
cmd.Parameters.AddWithValue("@originalFirstName", oldApplication.FirstName);
cmd.Parameters.AddWithValue("@originalLastName", oldApplication.LastName);
cmd.Parameters.AddWithValue("@originalAddress1", oldApplication.Address1);
cmd.Parameters.AddWithValue("@originalAddress2", oldApplication.Address2);
cmd.Parameters.AddWithValue("@originalZip", oldApplication.Zip);
cmd.Parameters.AddWithValue("@originalPhoneNumber", oldApplication.PhoneNumber);
cmd.Parameters.AddWithValue("@originalEmailAddress", oldApplication.EmailAddress);
cmd.Parameters.AddWithValue("@originalApplicationDate", oldApplication.ApplicationDate);
cmd.Parameters.AddWithValue("@originalApplicationStatus", oldApplication.ApplicationStatus);
cmd.Parameters.AddWithValue("@originalLastStatusDate", oldApplication.LastStatusDate);
cmd.Parameters.AddWithValue("@originalRemarks", oldApplication.Remarks);
try
{
conn.Open();
rowsAffected = cmd.ExecuteNonQuery();
if (rowsAffected == 0)
{
throw new ApplicationException("Concurrency Violation");
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(rowsAffected);
}
/// <summary>
/// Tyler Collins
/// Created: 2015/02/10
/// Retrieves all ItemListing data from the Database using a Stored Procedure.
/// Creates an ItemListing object from retrieved data.
/// Adds ItemListing object to List of ItemListing objects.
/// </summary>
/// <remarks>
/// Tyler Collins
/// Updated: 2015/02/26
/// Now up to date with most recent ItemListing object class
/// </remarks>
/// <returns>List of Active ItemListing objects</returns>
public static List <ItemListing> GetItemListingList()
{
List <ItemListing> itemListingList = new List <ItemListing>();
var conn = DatabaseConnection.GetDatabaseConnection();
string storedProcedure = "spSelectAllItemListings";
var cmd = new SqlCommand(storedProcedure, conn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
ItemListing currentItemListing = new ItemListing();
currentItemListing.StartDate = reader.GetDateTime(0);
currentItemListing.EndDate = reader.GetDateTime(1);
currentItemListing.ItemListID = reader.GetInt32(2);
currentItemListing.EventID = reader.GetInt32(3);
currentItemListing.Price = reader.GetDecimal(4);
currentItemListing.SupplierID = reader.GetInt32(5);
currentItemListing.CurrentNumGuests = reader.GetInt32(6);
currentItemListing.MaxNumGuests = reader.GetInt32(7);
currentItemListing.MinNumGuests = reader.GetInt32(8);
currentItemListing.EventName = reader.GetString(9);
currentItemListing.SupplierName = reader.GetString(10);
if (currentItemListing.EndDate > DateTime.Now)
{
itemListingList.Add(currentItemListing);
}
}
}
else
{
var pokeball = new ApplicationException("Data Not Found!");
throw pokeball;
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(itemListingList);
}
/// <summary>
/// Miguel Santana
/// Created: 2015/02/12
/// Updates a hotel guest with new information
/// </summary>
/// <remarks>
/// Rose Steffensmeier
/// Updated: 02/23/2015
/// added room number field
/// </remarks>
/// <param name="oldHotelGuest">Object containing original information about a hotel guest</param>
/// <param name="newHotelGuest">Object containing new hotel guest information</param>
/// <returns>Number of rows effected</returns>
public static int HotelGuestUpdate(HotelGuest oldHotelGuest, HotelGuest newHotelGuest)
{
var conn = DatabaseConnection.GetDatabaseConnection();
var cmdText = "spUpdateHotelGuest";
var cmd = new SqlCommand(cmdText, conn);
var numRows = 0;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@firstName", newHotelGuest.FirstName);
cmd.Parameters.AddWithValue("@lastName", newHotelGuest.LastName);
cmd.Parameters.AddWithValue("@zip", newHotelGuest.CityState.Zip);
cmd.Parameters.AddWithValue("@address1", newHotelGuest.Address1);
cmd.Parameters.AddWithValue("@address2", newHotelGuest.Address2);
cmd.Parameters.AddWithValue("@phoneNumber", newHotelGuest.PhoneNumber);
cmd.Parameters.AddWithValue("@email", newHotelGuest.EmailAddress);
cmd.Parameters.AddWithValue("@room", newHotelGuest.Room);
cmd.Parameters.AddWithValue("@active", newHotelGuest.Active);
cmd.Parameters.AddWithValue("@guestpin", newHotelGuest.GuestPIN);
cmd.Parameters.AddWithValue("@original_hotelGuestID", oldHotelGuest.HotelGuestID);
cmd.Parameters.AddWithValue("@original_firstName", oldHotelGuest.FirstName);
cmd.Parameters.AddWithValue("@original_lastName", oldHotelGuest.LastName);
cmd.Parameters.AddWithValue("@original_zip", oldHotelGuest.CityState.Zip);
cmd.Parameters.AddWithValue("@original_address1", oldHotelGuest.Address1);
cmd.Parameters.AddWithValue("@original_address2", oldHotelGuest.Address2);
cmd.Parameters.AddWithValue("@original_phoneNumber", oldHotelGuest.PhoneNumber);
cmd.Parameters.AddWithValue("@original_email", oldHotelGuest.EmailAddress);
cmd.Parameters.AddWithValue("@original_room", oldHotelGuest.Room);
cmd.Parameters.AddWithValue("@original_active", oldHotelGuest.Active);
cmd.Parameters.AddWithValue("@original_guestpin", oldHotelGuest.GuestPIN);
try
{
conn.Open();
numRows = cmd.ExecuteNonQuery();
if (numRows == 0)
{
throw new ApplicationException("Concurrency Violation");
}
}
catch (SqlException)
{
throw;
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(numRows);
}
/// <summary>
/// Tyler Collins
/// Created: 2015/02/03
/// Retrieves all Supplier data from the Database using a Stored Procedure.
/// Creates a Supplier object from retrieved data.
/// Adds Supplier object to List of Supplier objects.
/// </summary>
/// <returns>List of Supplier objects</returns>
public static List <Supplier> GetSupplierList()
{
List <Supplier> supplierList = new List <Supplier>();
var conn = DatabaseConnection.GetDatabaseConnection();
string storedProcedure = "spSelectAllSuppliers";
var cmd = new SqlCommand(storedProcedure, conn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var currentSupplier = new Supplier();
currentSupplier.SupplierID = reader.GetInt32(0);
currentSupplier.CompanyName = reader.GetString(1);
currentSupplier.FirstName = reader.GetString(2);
currentSupplier.LastName = reader.GetString(3);
currentSupplier.Address1 = reader.GetString(4);
currentSupplier.Address2 = !reader.IsDBNull(5) ? currentSupplier.Address2 = reader.GetString(5) : null;
currentSupplier.Zip = reader.GetString(6);
currentSupplier.PhoneNumber = reader.GetString(7);
currentSupplier.EmailAddress = reader.GetString(8);
currentSupplier.ApplicationID = reader.GetInt32(9);
currentSupplier.SupplyCost = reader.GetDecimal(10);
supplierList.Add(currentSupplier);
}
}
else
{
var pokeball = new ApplicationException("Data Not Found!");
throw pokeball;
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(supplierList);
}
/// <summary>
/// Matt Lapka
/// Created: 2015/02/08
/// Retrieves a list of all Supplier Application Records from the Database
/// </summary>
/// <remarks>
/// Rose Steffensmeier
/// Updated: 2015/04/03
/// added param to input so that stored procedure will work, param does not affect the actual
/// </remarks>
/// <returns>List of SupplierApplication objects</returns>
public static List <SupplierApplication> GetSupplierApplicationList()
{
var ApplicationList = new List <SupplierApplication>();
var conn = DatabaseConnection.GetDatabaseConnection();
var cmdText = "spSelectAllSupplierApplication";
var cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var currentSupplierApplication = new SupplierApplication();
currentSupplierApplication.ApplicationID = (int)reader.GetValue(0);
currentSupplierApplication.CompanyName = reader.GetValue(1).ToString();
currentSupplierApplication.CompanyDescription = !reader.IsDBNull(2) ? currentSupplierApplication.CompanyDescription = reader.GetValue(2).ToString() : null;
currentSupplierApplication.FirstName = reader.GetValue(3).ToString();
currentSupplierApplication.LastName = reader.GetValue(4).ToString();
currentSupplierApplication.Address1 = reader.GetValue(5).ToString();
currentSupplierApplication.Address2 = reader.GetValue(6).ToString();
currentSupplierApplication.Zip = reader.GetValue(7).ToString();
currentSupplierApplication.PhoneNumber = reader.GetValue(8).ToString();
currentSupplierApplication.EmailAddress = reader.GetValue(9).ToString();
currentSupplierApplication.ApplicationDate = (DateTime)reader.GetValue(10);
currentSupplierApplication.ApplicationStatus = reader.GetValue(11).ToString();
currentSupplierApplication.LastStatusDate = reader.GetDateTime(12);
currentSupplierApplication.Remarks = !reader.IsDBNull(13) ? currentSupplierApplication.Remarks = reader.GetValue(13).ToString() : null;
ApplicationList.Add(currentSupplierApplication);
}
}
else
{
var ex = new ApplicationException("No Supplier Applications Found!");
throw ex;
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(ApplicationList);
}
/// <summary>
/// Tony Noel
/// Created: 2015/02/13
/// Creates a list of Event listings with an ItemListID, Quantity, and readable event info
/// to populate the lists of Event Listings
/// List consists of active listings that have start time after DateTime.Now
/// </summary>
/// <returns>a list of ItemListingDetails objects (is created from two tables, ItemListing and Event Item)</returns>
public static List <ItemListingDetails> GetItemListingDetailsList()
{
var activeListingDetailsList = new List <ItemListingDetails>();
//Set up database call
var conn = DatabaseConnection.GetDatabaseConnection();
string query = "spSelectItemListingDetailsList";
var cmd = new SqlCommand(query, conn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows == true)
{
while (reader.Read())
{
var currentItemListingDetails = new ItemListingDetails();
//Below are found on the ItemListing table (ItemListID is a foreign key on booking)
currentItemListingDetails.ItemListID = reader.GetInt32(0);
currentItemListingDetails.MaxNumGuests = reader.GetInt32(1);
currentItemListingDetails.CurrentNumGuests = reader.GetInt32(2);
currentItemListingDetails.StartDate = reader.GetDateTime(3);
currentItemListingDetails.EndDate = reader.GetDateTime(4);
//Below are found on the EventItem table
currentItemListingDetails.EventID = reader.GetInt32(5);
currentItemListingDetails.EventName = reader.GetString(6);
currentItemListingDetails.EventDescription = reader.GetString(7);
//this is from itemlisting table
currentItemListingDetails.Price = reader.GetDecimal(8);
activeListingDetailsList.Add(currentItemListingDetails);
}
}
else
{
var ax = new ApplicationException("Booking data not found!");
throw ax;
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(activeListingDetailsList);
}
/// <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>
/// Pat Banks
/// Created: 2015/03/11
/// Retrieves the event listing information to enable
/// user to see current number of spots available for a selected listing
/// </summary>
/// <param name="itemListID">Id for the itemListing</param>
/// <returns>An ItemListingDetails object whose ID matches the passed ID</returns>
public static ItemListingDetails GetItemListingDetails(int itemListID)
{
var eventItemListing = new ItemListingDetails();
//Set up database call
var conn = DatabaseConnection.GetDatabaseConnection();
string query = "spSelectItemListingDetailsByID";
var cmd = new SqlCommand(query, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@itemListID", itemListID);
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows == true)
{
while (reader.Read())
{
//Below are found on the ItemListing table (ItemListID is a foreign key on booking)
eventItemListing.ItemListID = reader.GetInt32(0);
eventItemListing.MaxNumGuests = reader.GetInt32(1);
eventItemListing.CurrentNumGuests = reader.GetInt32(2);
eventItemListing.StartDate = reader.GetDateTime(3);
eventItemListing.EndDate = reader.GetDateTime(4);
//Below are found on the EventItem table
eventItemListing.EventID = reader.GetInt32(5);
eventItemListing.EventName = reader.GetString(6);
eventItemListing.EventDescription = reader.GetString(7);
//this is from itemlisting table
eventItemListing.Price = reader.GetDecimal(8);
}
}
else
{
var ax = new ApplicationException("Event data not found!");
throw ax;
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(eventItemListing);
}
/// <summary>
/// Tyler Collins
/// Created: 2015/02/04
/// UPDATEs a Supplier record in the Database with new using a Stored Procedure.
/// </summary>
/// <param name="newSupplierInfo">Requires the Supplier object containing the new information</param>
/// <param name="oldSupplierInfo">Requires the Supplier object to replace that matches the record in the Database</param>
/// <returns>Returns the number of rows affected.</returns>
public static int UpdateSupplier(Supplier newSupplierInfo, Supplier oldSupplierInfo)
{
var conn = DatabaseConnection.GetDatabaseConnection();
string storedProcedure = "spUpdateSupplier";
var cmd = new SqlCommand(storedProcedure, conn);
cmd.CommandType = CommandType.StoredProcedure;
//Updated Supplier Info:
cmd.Parameters.AddWithValue("@CompanyName", newSupplierInfo.CompanyName);
cmd.Parameters.AddWithValue("@FirstName", newSupplierInfo.FirstName);
cmd.Parameters.AddWithValue("@LastName", newSupplierInfo.LastName);
cmd.Parameters.AddWithValue("@Address1", newSupplierInfo.Address1);
cmd.Parameters.AddWithValue("@Address2", newSupplierInfo.Address2);
cmd.Parameters.AddWithValue("@Zip", newSupplierInfo.Zip);
cmd.Parameters.AddWithValue("@PhoneNumber", newSupplierInfo.PhoneNumber);
cmd.Parameters.AddWithValue("@EmailAddress", newSupplierInfo.EmailAddress);
cmd.Parameters.AddWithValue("@ApplicationID", newSupplierInfo.ApplicationID);
cmd.Parameters.AddWithValue("@SupplyCost", newSupplierInfo.SupplyCost);
//Old Supplier Info
cmd.Parameters.AddWithValue("@SupplierID", oldSupplierInfo.SupplierID);
cmd.Parameters.AddWithValue("@originalCompanyName", oldSupplierInfo.CompanyName);
cmd.Parameters.AddWithValue("@originalFirstName", oldSupplierInfo.FirstName);
cmd.Parameters.AddWithValue("@originalLastName", oldSupplierInfo.LastName);
cmd.Parameters.AddWithValue("@originalAddress1", oldSupplierInfo.Address1);
cmd.Parameters.AddWithValue("@originalAddress2", oldSupplierInfo.Address2);
cmd.Parameters.AddWithValue("@originalZip", oldSupplierInfo.Zip);
cmd.Parameters.AddWithValue("@originalPhoneNumber", oldSupplierInfo.PhoneNumber);
cmd.Parameters.AddWithValue("@originalEmailAddress", oldSupplierInfo.EmailAddress);
cmd.Parameters.AddWithValue("@originalApplicationID", oldSupplierInfo.ApplicationID);
cmd.Parameters.AddWithValue("@originalSupplyCost", oldSupplierInfo.SupplyCost);
int rowsAffected;
try
{
conn.Open();
rowsAffected = cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(rowsAffected);
}
/// <summary>
/// Tyler Collins
/// Created: 2015/02/03
/// Retrieves Supplier information from the Database using a Stored Procedure.
/// Creates a Supplier object.
/// </summary>
/// <remarks>
/// Ryan Blake
/// Updated: 2015/04/03
/// Added Supplier Cost
/// </remarks>
/// <param name="supplierID">Requires a SupplierID to SELECT the correct Supplier record.</param>
/// <returns>Supplier object</returns>
public static Supplier GetSupplier(string supplierID)
{
Supplier supplierToRetrieve = new Supplier();
SqlConnection conn = DatabaseConnection.GetDatabaseConnection();
string storedProcedure = "spSelectSupplier";
SqlCommand cmd = new SqlCommand(storedProcedure, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@SupplierID", supplierID);
try
{
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
supplierToRetrieve.SupplierID = reader.GetInt32(0);
supplierToRetrieve.CompanyName = reader.GetString(1);
supplierToRetrieve.FirstName = reader.GetString(2);
supplierToRetrieve.LastName = reader.GetString(3);
supplierToRetrieve.Address1 = reader.GetString(4);
supplierToRetrieve.Address2 = !reader.IsDBNull(5) ? supplierToRetrieve.Address2 = reader.GetString(5) : null;
supplierToRetrieve.Zip = reader.GetString(6);
supplierToRetrieve.PhoneNumber = reader.GetString(7);
supplierToRetrieve.EmailAddress = reader.GetString(8);
supplierToRetrieve.ApplicationID = reader.GetInt32(9);
supplierToRetrieve.SupplyCost = reader.GetDecimal(10);
}
else
{
var pokeball = new ApplicationException("Requested ID did not match any records.");
throw pokeball;
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(supplierToRetrieve);
}
/// <summary>
/// Pat Banks
/// Created: 2015/03/03
/// Creates a connection with database and
/// calls the stored procedure spSelectAllInvoices
/// that querys the database for a list of all active invoices
/// </summary>
/// <remarks>
/// Pat Banks
/// Updated: 2015/03/19
/// Made a generic accessor by moving if active test to InvoiceManager
/// </remarks>
/// <returns>List of InvoiceDetails</returns>
public static List<InvoiceDetails> GetAllInvoicesList()
{
//create list of InvoiceDetail Objects to store the invoice information
var guestList = new List<InvoiceDetails>();
var conn = DatabaseConnection.GetDatabaseConnection();
string query = "spSelectAllInvoices";
SqlCommand command = new SqlCommand(query, conn);
command.CommandType = CommandType.StoredProcedure;
//connect to db and retrieve information
try
{
conn.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var details = new InvoiceDetails();
details.InvoiceID = reader.GetInt32(0);
details.HotelGuestID = reader.GetInt32(1);
details.DateOpened = reader.GetDateTime(2);
if (!reader.IsDBNull(3)) details.DateClosed = reader.GetDateTime(3);
if (!reader.IsDBNull(4)) details.TotalPaid = reader.GetDecimal(4);
details.Active = reader.GetBoolean(5);
details.GuestLastName = reader.GetValue(6).ToString();
details.GuestFirstName = reader.GetValue(7).ToString();
details.GuestRoomNum = reader.GetValue(8).ToString();
guestList.Add(details);
}
}
else
{
throw new ApplicationException("No invoices found.");
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return guestList;
}
/// <summary>
/// Tyler Collins
/// Created: 2015/02/10
/// Retrieves ItemListing data from the Database using a Stored Procedure.
/// Creates an ItemListing object.
/// </summary>
/// <remarks>
/// Tyler Collins
/// Updated: 2015/02/26
/// Now up to date with most recent ItemListing object class
/// </remarks>
/// <param name="itemListID">Requires the ItemListID to SELECT the correct ItemListing record.</param>
/// <returns>ItemListing object</returns>
public static ItemListing GetItemListing(string itemListID)
{
ItemListing itemListingToRetrieve = new ItemListing();
SqlConnection conn = DatabaseConnection.GetDatabaseConnection();
string storedProcedure = "spSelectItemListing";
SqlCommand cmd = new SqlCommand(storedProcedure, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ItemListID", itemListID);
try
{
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
itemListingToRetrieve.StartDate = reader.GetDateTime(0);
itemListingToRetrieve.EndDate = reader.GetDateTime(1);
itemListingToRetrieve.ItemListID = reader.GetInt32(2);
itemListingToRetrieve.EventID = reader.GetInt32(3);
itemListingToRetrieve.Price = reader.GetDecimal(4);
//Are we using QuanityOffered and ProductSize since these are Event Items? O.o
itemListingToRetrieve.SupplierID = reader.GetInt32(5);
itemListingToRetrieve.MaxNumGuests = reader.GetInt32(7);
itemListingToRetrieve.MinNumGuests = reader.GetInt32(8);
itemListingToRetrieve.CurrentNumGuests = reader.GetInt32(6);
}
else
{
var pokeball = new ApplicationException("Requested ID did not match any records.");
throw pokeball;
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(itemListingToRetrieve);
}
