1. CREATE OR ALTER PROCEDURE SpTransactionTsk
2. @TransactionInfo NVARCHAR(MAX)
3. AS
4. BEGIN
5.    SET NOCOUNT ON
6.    
7.  BEGIN TRY
8.  BEGIN TRANSACTION
9.   -- FIRSTLY CREATE A TEMPORARY TABLE FOR THE INPUTS
10.   CREATE TABLE #Customer(TempCustomerId INT IDENTITY PRIMARY KEY, CustomerId INT);
11.   CREATE TABLE #Room(TempRoomdId INT IDENTITY PRIMARY KEY, RoomId INT);
12.   CREATE TABLE #Transaction(TempTransactionId INT IDENTITY PRIMARY KEY, TransactionId INT);
13.   CREATE TABLE #ServiceTransaction(TempServiceTransactonId INT IDENTITY PRIMARY KEY, ServiceTransactionId INT);
14.   CREATE TABLE #BookingTransaction(BookingTransactonId INT IDENTITY PRIMARY KEY, BookingTransactionId INT);
15.  
16.   -- DELARE VARIABLES TO STORE VALUES
17.   DECLARE @CustomerIds VARCHAR = ISNULL(JSON_VALUE(@TransactionInfo, '$.CustomerId'), '');
18.         DECLARE @RoomIds VARCHAR(50) = ISNULL(JSON_VALUE(@TransactionInfo, '$.RoomIds'), '');
19.         DECLARE @ServiceIds VARCHAR(50) = ISNULL(JSON_VALUE(@TransactionInfo, '$.ServiceIds'), '');
20.  
21.   DECLARE @CurrentDate SMALLDATETIME = GETDATE();
22.   -- CHECK IF CUSTOMER ID IS PRESENT
23.   IF @CustomerIds = ''
24.    BEGIN
25.     PRINT 'No Customer Specified'
26.     ROLLBACK TRANSACTION
27.     RETURN
28.    END
29.   ELSE
30.     BEGIN
31.     INSERT INTO #Customer (CustomerId)
32.     SELECT CAST(VALUE AS INT) AS ServiceId FROM STRING_SPLIT(@CustomerIds, ',')
33.    END
34.  
35.   DECLARE @NewTransactionId INT;
36.      EXEC SpTransactionIns @Amount = 100.00, @PaidAmount = 50.00, @DueAmount = 50.00, @TransactionDate = @CurrentDate,
37.       @STATUS = 'Pending', @TransactionType = 'Payment', @BranchId = 1, @CustomerId = 10,
38.       @UpdatedDate = @CurrentDate, @PersonUser = 1,
39.       @TransactionId = @NewTransactionId OUTPUT;
40.    
41.   INSERT INTO #Transaction(TransactionId) VALUES (@NewTransactionId)
42.  
43.   -- CHECK IF ROOM ID ARE PRESENT
44.   IF @RoomIds = ''
45.    BEGIN
46.     PRINT 'No Room Specified'
47.     ROLLBACK TRANSACTION
48.     RETURN
49.    END
50.   ELSE
51.     BEGIN
52.     INSERT INTO #Room(RoomId) SELECT CAST(VALUE AS INT) AS RoomId FROM STRING_SPLIT(@RoomIds, ',');
53.     DECLARE @TotalRooms INT = (SELECT COUNT(RoomId) FROM #Room)
54.    
55.     DECLARE @CurrentRoomId INT;
56.     DECLARE @Counter INT = 1;
57.  
58.    
59.  
60.  
61.     WHILE @Counter <= @TotalRooms
62.      BEGIN
63.       SELECT @CurrentRoomId = RoomId FROM #Room WHERE TempRoomdId = @Counter;
64.       IF @CurrentRoomId NOT IN (SELECT RoomId FROM Room)
65.        BEGIN
66.         SELECT 'SORRY THE ROOM DOESN''T EXIST' AS InvalidRoomId
67.         ROLLBACK TRANSACTION
68.         RETURN
69.        END
70.       ELSE
71.        BEGIN
72.         DECLARE @NewRoomStatus VARCHAR(50);
73.         EXEC SpRoomStatusCheck @CurrentRoomId, @RoomStatus = @NewRoomStatus OUTPUT;
74.  
75.      
76.         IF @NewRoomStatus = 'occupied'
77.          BEGIN
78.           PRINT 'The room is already booked';
79.           ROLLBACK TRANSACTION
80.           RETURN;
81.          END
82.         ELSE
83.           BEGIN
84.           DECLARE @NewBookingTransactionId INT;
85.          
86.           EXEC SpBookingTransactionIns
87.             @Transacti,
88.            @RoomId = @CurrentRoomId,
89.            @SubTotal = 99.99,
90.            @CheckIn = @CurrentDate,
91.            @CheckOut = NULL,
92.            @CreatedDate = @CurrentDate,
93.            @UpdatedDate = NULL,
94.             @Pers
95.             @BookingTransacti OUTPUT;
96.           INSERT INTO #BookingTransaction VALUES (@NewBookingTransactionId)
97.  
98.           EXEC SpRoomStatusUpd @CurrentRoomId;
99.           END
100.       END
101.  
102.      SET @Counter = @Counter + 1;
103.  
104.      END
105.  
106.     END
107.  
108.     -- FOR SERVICE TRANSACTION
109.     IF @ServiceIds = ''
110.     BEGIN
111.      PRINT 'No ServiceIds Specified'
112.     END
113.    ELSE
114.     BEGIN
115.      INSERT INTO #ServiceTransaction(ServiceTransactionId) SELECT CAST(VALUE AS INT) AS Service FROM STRING_SPLIT(@ServiceIds, ',');
116.      DECLARE @TotalService INT = (SELECT COUNT(ServiceTransactionId) FROM #ServiceTransaction)
117.    
118.      DECLARE @CurrentServiceId INT;
119.      DECLARE @ServiceCounter INT = 1;
120.      WHILE @ServiceCounter <= @TotalService
121.       BEGIN
122.         SELECT @CurrentServiceId = ServiceTransactionId FROM #ServiceTransaction WHERE TempServiceTransact
123.        IF @CurrentServiceId NOT IN (SELECT ServiceId FROM Service)
124.         BEGIN
125.          SELECT 'SORRY THE SERVICE DOESN''T EXIST' AS InvalidServiceId
126.          ROLLBACK TRANSACTION
127.          RETURN
128.       END
129.       ELSE
130.        BEGIN
131.         DECLARE @ServiceTransactionId INT;
132.         EXEC SpServiceTransactionIns @ServiceId = @CurrentServiceId,
133.          @Transacti,
134.         @SubTotal = 0,
135.         @CreatedDate = @CurrentDate,
136.         @UpdatedDate = @CurrentDate,
137.          @Pers
138.          @ServiceTransacti OUTPUT;
139.  
140.         INSERT INTO #ServiceTransaction(ServiceTransactionId) VALUES (@ServiceTransactionId)
141.        END
142.       END
143.  
144.     END
145.  
146.   COMMIT TRANSACTION
147.  END TRY
148.  BEGIN CATCH
149.   PRINT ERROR_MESSAGE()
150.    ROLLBACK TRANSACTION
151.  END CATCH
152.  SELECT * FROM [TRANSACTION] WHERE TransactionId IN (SELECT TransactionId FROM #Transaction);
153.  SELECT * FROM BookingTransaction WHERE BookingTransactionId IN (SELECT BookingTransactionId FROM #BookingTransaction);
154.  
155.  SELECT * FROM ServiceTransaction WHERE ServiceTransactionId IN (SELECT ServiceTransactionId FROM #ServiceTransaction);
156. END;