--SP to create bookings.
/ We will need TO pass customerId, roomIds OR room numbers WITH DATE range AND this should mark those rooms AS occupied FOR those days. /
-- FIRST CREATE A SP TO UPDATE THE ROOM STATUS
CREATE PROCEDURE SpRoomStatusUpdate
@RoomId INT
AS
BEGIN
SET NOCOUNT ON
UPDATE Room SET RoomStatus = 'Occupied' WHERE RoomId = @RoomId;
PRINT 'updated room status for Room Id ' + CAST(@RoomId AS VARCHAR)
END
-- CREATE A SP TO CHECK FOR ROOM AVAILABILITY
CREATE PROCEDURE SpRoomStatusCheck
@RoomId INT,
@RoomStatus NVARCHAR(50) OUTPUT
AS
BEGIN
SELECT @RoomStatus = RoomStatus FROM Room WHERE RoomId = @RoomId;
END
-- CHECK IF THE TRANSACTION EXIST FOR THE CUSTOMER
ALTER PROCEDURE SpTransactionCustomerCheck
@CustomerId INT,
@NewCustomerId VARCHAR(50) OUTPUT,
@TransactionId INT OUTPUT
AS
BEGIN
SELECT @NewCustomerId = 'Exists', @TransactionId = TransactionId FROM [TRANSACTION] WHERE CustomerId = @CustomerId;
END
-- Corrected Room Booking Procedure
CREATE PROCEDURE SpRoomBookingTsk
@RoomId INT,
@RentingDays INT,
@BookingTransactionId INT OUTPUT,
@PaidAmount MONEY,
@TransactionType VARCHAR(20),
@BranchId INT,
@CustomerId INT
AS
BEGIN
-- FIRST CHECK IF THE ROOM IS AVAILABLE
DECLARE @NewRoomStatus VARCHAR(50);
EXEC SpRoomStatusCheck @RoomId, @RoomStatus = @NewRoomStatus OUTPUT; -- Corrected the stored procedure call
-- IF THE ROOM IS ALREADY BOOKED
IF @NewRoomStatus = 'Occupied'
BEGIN
PRINT 'The room is already booked';
END
-- IF THE ROOM ISN'T ALREADY BOOKED
ELSE
BEGIN
BEGIN TRANSACTION
BEGIN TRY
-- VARIABLE TO STORE CHECKIN DATE + CHECKOUT DATE
-- CHECKIN DATE
DECLARE @CheckInDate VARCHAR(50);
SET @CheckInDate = GETDATE();
-- CHECKOUT DATE
DECLARE @CheckoutDate VARCHAR(50);
SET @CheckoutDate = DATEADD(MONTH, @RentingDays, @CheckInDate);
-- VARIABLE TO GET PRICE FROM ROOM
DECLARE @RoomPrice MONEY;
SELECT @RoomPrice = Price FROM Room WHERE RoomId = @RoomId;
DECLARE @DueAmount MONEY;
SET @DueAmount = @RoomPrice - @PaidAmount;
DECLARE @STATUS VARCHAR(50);
IF @DueAmount > 0
SET @STATUS = 'Pending'
ELSE
SET @STATUS = 'Paid'
-- UPDATE THE ROOM STATUS AS BOOKED
EXEC SpRoomStatusUpdate @RoomId; -- Corrected the stored procedure name
DECLARE @CustomerTrans VARCHAR(50);
DECLARE @ExistingTransactionId INT;
EXEC SpTransactionCustomerCheck @CustomerId, @NewCustomerId = @CustomerTrans OUTPUT, @TransactionId = @ExistingTransactionId OUTPUT;
IF @CustomerTrans = 'Exists'
BEGIN
DECLARE @NewBookingTransactionId INT;
EXEC SpInsertBookingTransaction
@TransactionId = @ExistingTransactionId,
@RoomId = @RoomId,
@SubTotal = @RoomPrice,
@CheckIn = @CheckInDate,
@CheckOut = @CheckoutDate,
@CreatedDate = @CheckInDate,
@UpdatedDate = @CheckInDate,
@PersonUser = 123,
@BookingTransactionId = @NewBookingTransactionId OUTPUT; -- Corrected the output parameter
END
ELSE
BEGIN
DECLARE @NewTransactionId INT;
EXEC SpInsertTransaction
@Amount = @RoomPrice,
@PaidAmount = @PaidAmount,
@DueAmount = @DueAmount,
@TransactionDate = @CheckInDate,
@STATUS = @STATUS,
@TransactionType = @TransactionType,
@BranchId = @BranchId,
@CustomerId = @CustomerId,
@UpdatedDate = @CheckoutDate, -- Corrected the parameter name
@PersonUser = 1,
@TransactionId = @NewTransactionId OUTPUT;
DECLARE @CurrentBookingTransactionId INT;
EXEC SpInsertBookingTransaction
@TransactionId = @NewTransactionId,
@RoomId = @RoomId,
@SubTotal = @RoomPrice,
@CheckIn = @CheckInDate,
@CheckOut = @CheckoutDate,
@CreatedDate = @CheckInDate,
@UpdatedDate = @CheckoutDate,
@PersonUser = 1,
@BookingTransactionId = @CurrentBookingTransactionId OUTPUT;
END
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
COMMIT TRANSACTION
END
END
DECLARE @VeryNewBookingTransactionId INT;
EXEC SpRoomBookingTsk
@RoomId = 4,
@RentingDays = 4,
@BookingTransactionId = @VeryNewBookingTransactionId OUTPUT,
@PaidAmount = 120,
@TransactionType = 'Cash',
@BranchId = 1,
@CustomerId = 3;
