/// <summary>
/// Miguel Santana
/// Created: 2015/02/12
/// Get a list of all HotelGuest Objects
/// </summary>
///<remarks>
/// Rose Steffensmeier
/// Updated: 2015/03/12
/// Added try-catch blocks
/// </remarks>
/// <param name="hotelGuestID">Optional Parameter to specify a hotel guest to look up</param>
/// <returns></returns>
public static List <HotelGuest> HotelGuestGet(int?hotelGuestID = null)
{
SqlConnection conn = DatabaseConnection.GetDatabaseConnection();
var cmdText = "spSelectHotelGuest";
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@hotelGuestID", hotelGuestID);
List <HotelGuest> list = new List <HotelGuest>();
try
{
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
list.Add(
new HotelGuest(
reader.GetInt32(0), //HotelGuestID
reader.GetString(1), //FirstName
reader.GetString(2), //LastName
reader.GetString(3), //Address1
!reader.IsDBNull(4) ? reader.GetString(4) : null, //Address2
new CityState(
reader.GetString(5), //Zip
reader.GetString(6), //City
reader.GetString(7) //State
),
!reader.IsDBNull(8) ? reader.GetString(8) : null, //PhoneNumber
!reader.IsDBNull(9) ? reader.GetString(9) : null, //EmailAdddress
!reader.IsDBNull(10) ? reader.GetString(10) : null, //Room
!reader.IsDBNull(11) ? reader.GetString(11) : null, // PIN
reader.GetBoolean(12) //Active
)
);
}
}
else
{
throw new ApplicationException(hotelGuestID == null ? "Could not find any Hotel Guests" : "Hotel Guest ID number did not match any records.");
}
}
catch (SqlException)
{
throw;
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(list);
}
/// <summary>
/// Pat Banks
/// Created: 2015/04/14
/// Verifies the hotel guest pin to sign up for an item listing and create a booking
/// </summary>
/// <param name="inPIN">The pin to cross reference against HotelGuests</param>
/// <returns>Hotel guest that has that pin</returns>
public static HotelGuest VerifyHotelGuestPin(string inPIN)
{
SqlConnection conn = DatabaseConnection.GetDatabaseConnection();
var cmdText = "spSelectHotelGuestByPin";
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@guestPIN", inPIN);
HotelGuest foundGuest = null;
try
{
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
foundGuest = new HotelGuest(
reader.GetInt32(0), //HotelGuestID
reader.GetString(1), //FirstName
reader.GetString(2), //LastName
reader.GetString(3), //Address1
!reader.IsDBNull(4) ? reader.GetString(4) : null, //Address2
new CityState(
reader.GetString(5), //Zip
reader.GetString(6), //City
reader.GetString(7) //State
),
!reader.IsDBNull(8) ? reader.GetString(8) : null, //PhoneNumber
!reader.IsDBNull(9) ? reader.GetString(9) : null, //EmailAdddress
!reader.IsDBNull(10) ? reader.GetString(10) : null, //Room
!reader.IsDBNull(11) ? reader.GetString(11) : null, // PIN
reader.GetBoolean(12) //Active
);
} // end while
}
else
{
var ax = new ApplicationException("PIN not found");
throw ax;
}
}
catch (SqlException)
{
var ax = new ApplicationException("PIN not found");
throw ax;
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(foundGuest);
}
/// <summary>
/// Pat Banks
/// Created: 2015/04/11
/// Adds a new supplier from an application with supply cost in the supplier table
/// Adds User name to the supplier login table.
/// </summary>
/// <param name="oldApplication">The SupplierApplication object/record to be updated</param>
/// <param name="newApplication">The SupplierApplication object with the updated information for the record</param>
/// <param name="userName">The username for the supplier whose application is being updated</param>
/// <param name="supplyCost">The updated supply cost</param>
/// <returns>number of rows affected</returns>
public static int UpdateSupplierApplication(SupplierApplication oldApplication, SupplierApplication newApplication, string userName, decimal supplyCost, string password)
{
var conn = DatabaseConnection.GetDatabaseConnection();
var cmdText = "spInsertSupplierFromApplication";
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("@UserName", userName);
cmd.Parameters.AddWithValue("@SupplyCost", supplyCost);
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);
cmd.Parameters.AddWithValue("@password", password);
try
{
conn.Open();
rowsAffected = cmd.ExecuteNonQuery();
if (rowsAffected == 0)
{
throw new ApplicationException("Concurrency Violation");
}
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(rowsAffected);
}
