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