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