Untitled

use kmalec;

/* Rozszerzenie tabeli Booking_detail o cenę pokoju w momencie rezerwacji */
ALTER TABLE Booking_detail ADD room_price int;
/* Rozszerzenie tabeli Bookings o sumę cen pokoi dla zamówienia */
ALTER TABLE Bookings ADD booking_price int;

/* Procedura dodawania rezerwacji */
CREATE PROCEDURE [dbo].[p_execute_reservation] @customer_id INT,
@room_id INT,
@spot_id INT,
@date_in SMALLDATETIME,
@date_out SMALLDATETIME,
@employee_id INT AS DECLARE @error AS VARCHAR(250);

IF @customer_id IS NULL
OR @room_id IS NULL
OR @spot_id IS NULL
OR @employee_id IS NULL
OR @date_in IS NULL
OR @date_out IS NULL
OR @date_out < @date_in BEGIN
SET
  @error = 'Błędne dane!';
RAISERROR (@error, 16, 1);
END EXECUTE p_check_room_availabilty @room_id, @date_in, @date_out;

INSERT INTO Bookings(customer_id, payment_status, payment_date)
VALUES
  (@customer_id, 'U', null);
  
DECLARE @booking_id int = SCOPE_IDENTITY();

/* Cena pokoju brana z tabeli Rooms */ 
INSERT INTO Booking_detail(booking_id, room_id, spot_id, booking_status,
  employee_id, date_in, date_out, room_price)
VALUES
  (@booking_id, @room_id, @spot_id,'U', @employee_id, @date_in, @date_out,
    (
      SELECT
        DISTINCT price
      FROM
        Rooms
        INNER JOIN Booking_detail Bd on Rooms.room_id = Bd.room_id
      WHERE
        Rooms.room_id = @room_id
    ) * DATEDIFF(DAY, @date_in, @date_out)
  );

/* Zsumowanie ceny pokoi dla bookingu z danym id */
UPDATE
  Bookings
SET
  booking_price =(SELECT
      SUM(room_price)
    FROM
      (
        SELECT
          room_price
        FROM
          Booking_detail
          INNER JOIN Bookings Bk on Booking_detail.booking_id = Bk.booking_id
        WHERE
          Booking_detail.booking_id = @booking_id
      ) as room_price)
WHERE
  booking_id = @booking_id;
GO;

/* Przykładowe uruchomienie procedury */
EXECUTE p_execute_reservation 4, 1, 3,'2022-10-30 00:00:00','2022-11-05 00:00:00', 1;