Пример #1
0
        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);
        }
Пример #2
0
        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();
        }
Пример #3
0
        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);
        }
Пример #4
0
        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);
        }