public static Dictionary <string, object> InsertHotelBooking(HotelBookingSave msg)
{
try
{
SQLClient sqlClient = new SQLClient();
string query = "insert into CheckTime (RoomTypeID, CustomerID, [Status], ExpectedCheckIn, ExpectedCheckOut) values (@RoomTypeID, @CustomerID, @Status, @ExpectedCheckIn, @ExpectedCheckOut)";
// Save the Booking in the Hotel
List <HotelBookingObject> hObjects = sqlClient.SaveHotelBooking(query, msg);
HotelBookingObject hObject = hObjects[0];
if (string.IsNullOrEmpty(hObject.Error))
{
return(DeserializeToDictionary(JsonConvert.SerializeObject(hObject)));
}
else
{
Error err = new Error();
err.ErrorMessage = hObject.Error;
return(DeserializeToDictionary(JsonConvert.SerializeObject(err)));
}
}
catch (Exception ex)
{
WriteToFile(string.Format("{0}, {1}, {2}", ex.Message, ex.InnerException, ex.StackTrace));
return(DeserializeToDictionary(string.Format(ex.ToString())));
}
}
public List <HotelBookingObject> SaveHotelBooking(string query, HotelBookingSave msg)
{
List <HotelBookingObject> hObjects = new List <HotelBookingObject>();
Utility.WriteToFile("Got to SQL Client Write To File Path");
Utility.WriteToFile("Request in Payload: " + JsonConvert.SerializeObject(msg.Entity));
string roomTypeQuery = string.Format("select RoomTypeID from RoomTypes where RoomTypeName = '{0}'", msg.Entity.RoomType);
string roomTypeID = ExecuteStringColumnReader(roomTypeQuery);
if (string.IsNullOrEmpty(roomTypeID))
{
HotelBookingObject hObject = new HotelBookingObject();
hObject.Error = "Either Room Type Does Not Exist in this Hotel or ReservationID does not exists. Available Room Types are: deluxe, regular, palatial";
hObjects.Add(hObject);
return(hObjects);
}
else
{
Utility.WriteToFile("Able to check Room Type");
using (SqlConnection sqlCon = new SqlConnection(_connString))
{
using (var command = new SqlCommand(query, sqlCon))
{
command.Parameters.AddWithValue("RoomTypeID", roomTypeID);
command.Parameters.AddWithValue("CustomerID", Convert.ToInt64(msg.Entity.CustomerId));
command.Parameters.AddWithValue("Status", msg.Entity.Status);
command.Parameters.AddWithValue("ExpectedCheckIn", msg.Entity.ExpectedCheckInTime);
command.Parameters.AddWithValue("ExpectedCheckOut", msg.Entity.ExpectedCheckOutTime);
if (sqlCon.State == ConnectionState.Closed)
{
sqlCon.Open();
}
Utility.WriteToFile("Execute My Query Here");
command.ExecuteNonQuery();
}
}
Utility.WriteToFile("Could Insert Hotel Booking Successfully");
string lastQuery = string.Format("select a.ReservationID, b.RoomTypeName, a.CustomerID, b.HourlyRate, a.Status, a.ExpectedCheckIn, a.ExpectedCheckOut from CheckTime as a, RoomTypes as b where a.RoomTypeID = b.RoomTypeID and a.CustomerID = {0} order by a.ReservationID desc", msg.Entity.CustomerId);
Utility.WriteToFile("Able to launch Last Query");
hObjects = FetchHotelBooking(lastQuery);
Utility.WriteToFile("Able to Execute Last Query Successfully");
return(hObjects);
}
}
public static HotelBookingObject ExecuteHotelBookFetch(long ReservationID)
{
SQLClient sqlClient = new SQLClient();
HotelBookingObject hObject = new HotelBookingObject();
string query = string.Format("select a.ReservationID, b.RoomTypeName, a.CustomerID, b.HourlyRate, a.Status, a.ExpectedCheckIn, a.ExpectedCheckOut from CheckTime as a, RoomTypes as b where a.RoomTypeID = b.RoomTypeID and a.ReservationID = {0}", ReservationID);
WriteToFile(string.Format("Booking Fetch Query: {0}", query));
List <HotelBookingObject> hObjects = sqlClient.FetchHotelBooking(query);
if (hObjects.Count < 1)
{
hObject.Error = "Either Room Type Does Not Exist in this Hotel or ReservationID does not exists. Available Room Types are: deluxe, regular, palatial";
}
else
{
hObject = hObjects[0];
}
return(hObject);
}
public static Dictionary <string, object> UpdateHotelBooking(long ReservationID, HotelBookingUpdate msg)
{
try
{
// Fetch Details of the Booking to update by ReservationID
HotelBookingObject hObject = ExecuteHotelBookFetch(ReservationID);
if (string.IsNullOrEmpty(hObject.Error))
{
HotelBookingUpdate updateMsg = new HotelBookingUpdate();
updateMsg.Patch = msg.Patch;
WriteToFile(string.Format("Update Msg Details: {0}", JsonConvert.SerializeObject(updateMsg)));
// Assign update msg with field values from the API Call if they exist
updateMsg.Patch.ReservationId = hObject.ReservationId;
updateMsg.Patch.Status = string.IsNullOrEmpty(msg.Patch.Status) ? hObject.Status : msg.Patch.Status;
updateMsg.Patch.ExpectedCheckInTime = string.IsNullOrEmpty(msg.Patch.ExpectedCheckInTime) ? hObject.ExpectedCheckInTime : msg.Patch.ExpectedCheckInTime;
updateMsg.Patch.ExpectedCheckOutTime = string.IsNullOrEmpty(msg.Patch.ExpectedCheckOutTime) ? hObject.ExpectedCheckOutTime : msg.Patch.ExpectedCheckOutTime;
SQLClient sqlClient = new SQLClient();
string query = string.Format("update CheckTime set [Status] = @Status, [ExpectedCheckIn] = @ExpectedCheckIn, [ExpectedCheckOut] = @ExpectedCheckOut where ReservationID = {0}", hObject.ReservationId);
// Update Hotel Booking by the ReservationID
List <HotelBookingObject> hObjects = sqlClient.UpdateHotelBooking(query, updateMsg);
hObject = hObjects[0];
return(DeserializeToDictionary(JsonConvert.SerializeObject(hObject)));
}
else
{
Error err = new Error();
err.ErrorMessage = hObject.Error;
return(DeserializeToDictionary(JsonConvert.SerializeObject(err)));
}
}
catch (Exception ex)
{
WriteToFile(string.Format("{0}, {1}, {2}", ex.Message, ex.InnerException, ex.StackTrace));
return(DeserializeToDictionary(string.Format(ex.ToString())));
}
}
public static Dictionary <string, object> GetHotelBooking(long ReservationID)
{
try
{
// Fetch a Booking by Reservation ID
HotelBookingObject hObject = ExecuteHotelBookFetch(ReservationID);
if (string.IsNullOrEmpty(hObject.Error))
{
return(DeserializeToDictionary(JsonConvert.SerializeObject(hObject)));
}
else
{
Error err = new Error();
err.ErrorMessage = hObject.Error;
return(DeserializeToDictionary(JsonConvert.SerializeObject(err)));
}
}
catch (Exception ex)
{
WriteToFile(string.Format("{0}, {1}, {2}", ex.Message, ex.InnerException, ex.StackTrace));
return(DeserializeToDictionary(string.Format(ex.ToString())));
}
}
public List <HotelBookingObject> FetchHotelBooking(string query)
{
List <HotelBookingObject> hObjects = new List <HotelBookingObject>();
using (SqlConnection sqlCon = new SqlConnection(_connString))
{
SqlCommand sqlCmd = new SqlCommand(query, sqlCon);
// Be sure the sqlconnection is open
if (sqlCon.State == ConnectionState.Closed)
{
sqlCon.Open();
}
using (var reader = sqlCmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
HotelBookingObject hObject = new HotelBookingObject();
hObject.ReservationId = reader[0].ToString();
hObject.RoomType = reader[1].ToString();
hObject.CustomerId = reader[2].ToString();
hObject.HourlyRate = reader[3].ToString();
hObject.Status = reader[4].ToString();
hObject.ExpectedCheckInTime = reader[5].ToString();
hObject.ExpectedCheckOutTime = reader[6].ToString();
hObjects.Add(hObject);
}
}
}
}
return(hObjects);
}
public static Dictionary <string, object> GetOverDueRate(long ReservationID, HotelBookingCalculate msg)
{
try
{
HotelBookingObject hObject = ExecuteHotelBookFetch(ReservationID);
if (string.IsNullOrEmpty(hObject.Error))
{
DateTime checkOutTime = Convert.ToDateTime(msg.CheckOutTime);
OverDue overDue = CalculateOverDueRate(checkOutTime, ReservationID, msg, hObject);
return(DeserializeToDictionary(JsonConvert.SerializeObject(overDue)));
}
else
{
Error err = new Error();
err.ErrorMessage = hObject.Error;
return(DeserializeToDictionary(JsonConvert.SerializeObject(err)));
}
}
catch (Exception ex)
{
WriteToFile(string.Format("{0}, {1}, {2}", ex.Message, ex.InnerException, ex.StackTrace));
return(DeserializeToDictionary(string.Format(ex.ToString())));
}
}
public static OverDue CalculateOverDueRate(DateTime CheckOutTime, long ReservationID, HotelBookingCalculate msg, HotelBookingObject hObject)
{
bool isWeekend = DateIsWeekend(CheckOutTime);
string query = string.Format("Select {0} from CheckTime as a, RoomOverDueRate as b where a.ReservationID = @ReservationID and a.RoomTypeID = b.RoomTypeID", isWeekend ? "b.WeekendRate" : "b.WeekDayRate");
SQLClient sqlclient = new SQLClient();
double overDueRate = sqlclient.GetOverDueRate(query, ReservationID);
DateTime expectedCheckIn = Convert.ToDateTime(hObject.ExpectedCheckInTime);
DateTime expectedCheckOut = Convert.ToDateTime(hObject.ExpectedCheckOutTime);
double expectedHours = expectedCheckOut.Subtract(expectedCheckIn).TotalHours;
bool hoursStretched = ExpectedHoursStretched(expectedHours);
int calculatedHours = Convert.ToInt32(Math.Ceiling(expectedHours));
WriteToFile(string.Format("Calculated Hours for ReservationID {0}: {1}", ReservationID, calculatedHours));
double initialAmount = Convert.ToDouble(hObject.HourlyRate) * calculatedHours;
double overDueAmount = GetOverDueAmount(CheckOutTime, expectedCheckOut, hoursStretched, overDueRate, Convert.ToDouble(hObject.HourlyRate));
double totalAmount = initialAmount + overDueAmount;
OverDue overdue = new OverDue
{
InitialAmount = initialAmount,
OverDueAmount = overDueAmount,
TotalAmount = totalAmount,
WeekendRate = isWeekend
};
return(overdue);
}
