public static List <Bed> GetBeds() { List <Bed> beds = new List <Bed>(); string sqlQuery = "SELECT r.name as roomName,r.id as roomID, b.id as numBed, b.price, b.status FROM Beds b JOIN Rooms r on b.room_id = r.id WHERE b.status = 1;"; SqlCommand command = new SqlCommand(sqlQuery, connection); SqlDataReader dataReader = command.ExecuteReader(); if (dataReader.HasRows) { while (dataReader.Read()) { Bed newBed = new Bed(); newBed.Id = Convert.ToInt32(dataReader["numBed"]); newBed.RoomId = Convert.ToInt32(dataReader["roomID"]); newBed.RoomName = dataReader["roomName"].ToString(); newBed.Price = Convert.ToDouble(dataReader["price"]); newBed.Status = (bool)(dataReader["status"]); beds.Add(newBed); } } dataReader.Close(); command.Dispose(); return(beds); }
public static void MakeBedAvaible(Bed selectedBed) { string updateQuery = $"Update Beds SET status = '1' Where id = '{selectedBed.Id}';"; SqlCommand command = new SqlCommand(updateQuery, connection); command.ExecuteNonQuery(); command.Dispose(); }
public static Reservation GetUpdatedReservation(int editedReservationID) { string sqlQuery = ($"SELECT r.id as reservation_id, r.customer_id, r.arrivalDate, r.departureDate, r.comment, " + $"rb.bed_id, c.name, b.room_id, b.price, ro.name as roomName, c.email, c.firstName, c.phone " + $"FROM Reservations r " + $"JOIN Reservation_Beds rb on r.id = rb.reservation_id " + $"JOIN Customers c on c.id = r.customer_id " + $"JOIN Beds b on b.id = rb.bed_id " + $"JOIN Rooms ro on ro.id = b.room_id " + $"WHERE r.id = {editedReservationID};"); SqlCommand command = new SqlCommand(sqlQuery, connection); SqlDataReader dataReader = command.ExecuteReader(); Reservation editedReservation = new Reservation(); Customer newCustomer = new Customer(); if (dataReader.HasRows) { while (dataReader.Read()) { if (editedReservation.Customer == null) { editedReservation.Id = Convert.ToInt32(dataReader["reservation_id"]); editedReservation.CustomerName = dataReader["name"].ToString(); editedReservation.CustomerID = Convert.ToInt32(dataReader["customer_id"]); editedReservation.ArrivalDate = Convert.ToDateTime(dataReader["arrivalDate"]); editedReservation.DepartureDate = Convert.ToDateTime(dataReader["departureDate"]); editedReservation.Comment = dataReader["comment"].ToString(); newCustomer.Id = Convert.ToInt32(dataReader["customer_id"]); newCustomer.Name = dataReader["name"].ToString(); newCustomer.Email = dataReader["email"].ToString(); newCustomer.Phone = dataReader["phone"].ToString(); newCustomer.FirstName = dataReader["firstName"].ToString(); editedReservation.Customer = newCustomer; } Bed newBed = new Bed(); newBed.Id = Convert.ToInt32(dataReader["bed_id"]); newBed.RoomId = Convert.ToInt32(dataReader["room_id"]); newBed.RoomName = dataReader["roomName"].ToString(); newBed.Status = false; newBed.Price = Convert.ToDouble(dataReader["price"]); editedReservation.RevervedBeds.Add(newBed); } } dataReader.Close(); command.Dispose(); return(editedReservation); }
public static List <Bed> GetAvaibleBeds(DateTime bookingFromDate, DateTime bookingToDate, int excludedReservationID) { List <Bed> beds = new List <Bed>(); string bookingFromDateStr = bookingFromDate.ToString("MM/dd/yyyy"); string bookingToDateStr = bookingToDate.ToString("MM/dd/yyyy"); string sqlQuery = "SELECT ro.name as roomName,ro.id as roomID, b.id as numBed, b.price, b.status " + "from Beds b JOIN Rooms ro on ro.id = b.room_id " + "WHERE b.status = 1" + "AND b.id NOT IN(SELECT rb.bed_id from reservations r join Reservation_Beds rb on r.id = rb.reservation_id " + $"WHERE((arrivalDate <= '{bookingFromDateStr}' AND departureDate > '{bookingFromDateStr}') " + $"OR(arrivalDate < '{bookingToDateStr}' AND departureDate >= '{bookingToDateStr}') " + $"OR('{bookingFromDateStr}' < arrivalDate AND '{bookingToDateStr}' > arrivalDate)) " + $"AND(r.id <> {excludedReservationID}));"; SqlCommand command = new SqlCommand(sqlQuery, connection); SqlDataReader dataReader = command.ExecuteReader(); if (dataReader.HasRows) { while (dataReader.Read()) { Bed newBed = new Bed(); newBed.Id = Convert.ToInt32(dataReader["numBed"]); newBed.RoomId = Convert.ToInt32(dataReader["roomID"]); newBed.RoomName = dataReader["roomName"].ToString(); newBed.Price = Convert.ToDouble(dataReader["price"]); newBed.Status = (bool)(dataReader["status"]); beds.Add(newBed); } } dataReader.Close(); command.Dispose(); return(beds); }