Back to: ASP.NET Core Web API Tutorials
Implementing the Hotel Cancellation Module
In this article, I will discuss implementing the Hotel Cancellation Module in our Application using our ASP.NET Core Web API Application. Please read our previous article on how to Implement the Hotel Booking Module.
Let us implement the Hotel Cancellation Module, i.e., implement Partial and Full Cancellation in our Hotel Booking Application. Let us create Stored Procedures, DTOs, a Repository class, and the Cancellation Controller for effectively managing Hotel Cancellation functionalities such as calculating the cancellation charge, creating a new cancellation request, approving or rejecting the cancellation request, processing the refund, etc.
Database Schema Changes for Effective Hotel Cancellation Module:
As I already told you from the beginning, we might change the database schema as we progress. In order to manage the cancellations efficiently, especially when dealing with Full and Partial Cancellations, we need to make some modifications. Let us proceed and make the changes first.
CancellationRequests Table
Replace the Cancellations with the CancellationRequests table. This table is used to store the information required for initiating the cancellation process. It records each cancellation request, including the reservation involved, the user who requested the cancellation, the administrative review, and the reason for cancellation. This allows for both full and partial cancellations. So, please execute the following SQL Script to create the CancellationRequests table:
-- CancellationRequests Table
CREATE TABLE CancellationRequests (
CancellationRequestID INT PRIMARY KEY IDENTITY(1,1),
ReservationID INT,
UserID INT,
CancellationType NVARCHAR(50),
RequestedOn DATETIME DEFAULT GETDATE(),
Status NVARCHAR(50),
AdminReviewedByID INT,
ReviewDate DATETIME,
CancellationReason NVARCHAR(255),
FOREIGN KEY (ReservationID) REFERENCES Reservations(ReservationID),
FOREIGN KEY (UserID) REFERENCES Users(UserID),
FOREIGN KEY (AdminReviewedByID) REFERENCES Users(UserID)
);
GO
CancellationPolicies Table:
This table defines the cancellation policies, which could vary based on the time before the check-in date, type of reservation, or other factors. By specifying the cancellation charge as a percentage or a minimum charge, we ensure how cancellation costs are calculated, adapting to various pricing and booking scenarios. So, please execute the following SQL Script to create the CancellationRequests table:
-- CancellationPolicies Table
CREATE TABLE CancellationPolicies (
PolicyID INT PRIMARY KEY IDENTITY(1,1),
Description NVARCHAR(255),
CancellationChargePercentage DECIMAL(5,2),
MinimumCharge DECIMAL(10,2),
EffectiveFromDate DATETIME,
EffectiveToDate DATETIME
);
GO
CancellationDetails Table
This table contains the information about which rooms are cancellated of a reservation. In case of full cancellation, it stores all canceled room information for the same canceled request ID. So, please execute the following SQL Script to create the CancellationRequests table:
-- CancellationDetails Table
CREATE TABLE CancellationDetails (
CancellationDetailID INT PRIMARY KEY IDENTITY(1,1),
CancellationRequestID INT,
ReservationRoomID INT,
FOREIGN KEY (CancellationRequestID) REFERENCES CancellationRequests(CancellationRequestID),
FOREIGN KEY (ReservationRoomID) REFERENCES ReservationRooms(ReservationRoomID)
);
GO
CancellationCharges Table:
This table will include all the necessary fields to record detailed information about the cancellation charges, such as the total cost of the booking, the actual cancellation charge applied, the cancellation percentage used, the minimum charge if applicable, and a description of the applied cancellation policy. This table will be populated when the cancellation is approved by the admin.
CREATE TABLE CancellationCharges
(
CancellationRequestID INT PRIMARY KEY,
TotalCost DECIMAL(10,2),
CancellationCharge DECIMAL(10,2),
CancellationPercentage DECIMAL(10,2),
MinimumCharge DECIMAL(10,2),
PolicyDescription NVARCHAR(255),
FOREIGN KEY (CancellationRequestID) REFERENCES CancellationRequests(CancellationRequestID)
);
Refunds Table Modification
This newly structured Refunds table is used to handle the financial refunds for cancellations. The CancellationCharge and NetRefundAmount fields ensure that you can calculate the exact refund amounts after calculating the cancellation fees. The link to the CancellationRequestID ensures that each refund is directly traceable to a specific cancellation. So, please execute the following SQL Script to drop and create the Refunds table with additional columns.
-- First, if the Refunds table already exists, delete it:
IF OBJECT_ID('Refunds', 'U') IS NOT NULL
DROP TABLE Refunds;
GO
-- Then Create the Refund table
CREATE TABLE Refunds (
RefundID INT PRIMARY KEY IDENTITY(1,1),
PaymentID INT,
RefundAmount DECIMAL(10,2),
RefundDate DATETIME DEFAULT GETDATE(),
RefundReason NVARCHAR(255),
RefundMethodID INT,
ProcessedByUserID INT,
RefundStatus NVARCHAR(50),
CancellationCharge DECIMAL(10,2) DEFAULT 0,
NetRefundAmount DECIMAL(10,2),
CancellationRequestID INT,
FOREIGN KEY (PaymentID) REFERENCES Payments(PaymentID),
FOREIGN KEY (RefundMethodID) REFERENCES RefundMethods(MethodID),
FOREIGN KEY (ProcessedByUserID) REFERENCES Users(UserID),
FOREIGN KEY (CancellationRequestID) REFERENCES CancellationRequests(CancellationRequestID)
);
GO
Demo Cancellation Policies:
The following policies cover different scenarios, such as early cancellations, last-minute cancellations, and varying cancellation charges based on the timing relative to the check-in date.
- Early Cancellation (No Charge): There is no cancellation charge if a cancellation is made more than 30 days before the check-in date.
- Moderate Cancellation (10% Charge): If you cancel between 15 and 30 days before the check-in date, a cancellation charge of 10% of the booking cost will be applied.
- Late Cancellation (25% Charge): If a cancellation is made between 7 and 14 days before the check-in date, a cancellation charge of 25% of the booking cost is applied.
- Last-Minute Cancellation (50% Charge): If a cancellation is made less than 7 days before the check-in date, a cancellation charge of 50% of the booking cost is applied.
- Special High Season Policy (100% Charge): Any cancellation incurs a 100% charge regardless of when it is made during the high season.
So, please execute the following SQL INSERT statements to populate the CancellationPolicies table with demo policies. The MinimumCharge is set to 0 in these examples, but you can adjust it if there’s a need to ensure a minimum fee is always collected, irrespective of the percentage calculated.
-- Insert Early Cancellation Policy
INSERT INTO CancellationPolicies (Description, CancellationChargePercentage, MinimumCharge, EffectiveFromDate, EffectiveToDate)
VALUES ('No charge if cancelled more than 30 days before check-in', 0, 0, '2024-01-01', '2024-12-31');
-- Insert Moderate Cancellation Policy
INSERT INTO CancellationPolicies (Description, CancellationChargePercentage, MinimumCharge, EffectiveFromDate, EffectiveToDate)
VALUES ('10% charge if cancelled between 15 and 30 days before check-in', 10, 0, '2024-01-01', '2024-12-31');
-- Insert Late Cancellation Policy
INSERT INTO CancellationPolicies (Description, CancellationChargePercentage, MinimumCharge, EffectiveFromDate, EffectiveToDate)
VALUES ('25% charge if cancelled between 7 and 14 days before check-in', 25, 0, '2024-01-01', '2024-12-31');
-- Insert Last-Minute Cancellation Policy
INSERT INTO CancellationPolicies (Description, CancellationChargePercentage, MinimumCharge, EffectiveFromDate, EffectiveToDate)
VALUES ('50% charge if cancelled less than 7 days before check-in', 50, 0, '2024-01-01', '2024-12-31');
-- Insert Special High Season Policy
INSERT INTO CancellationPolicies (Description, CancellationChargePercentage, MinimumCharge, EffectiveFromDate, EffectiveToDate)
VALUES ('100% charge for any cancellation during high season', 100, 0, '2024-08-01', '2024-08-31');
Modifying the Status Column Check Constraint in the Reservations Table:
Currently, the Reservations table Status column contains the check constraint which allows only ‘Reserved’, ‘Checked-in’, ‘Checked-out’, ‘Cancelled’ values. Now, I want to add ‘Partially Cancelled’ to the check constraint as we are also going to implement the Partial Cancellation. To do so, we need to drop and recreate the check constraint.
Drop the Existing Check Constraint
First, you need to find the name of the check constraint. You can do this by querying the INFORMATION_SCHEMA.TABLE_CONSTRAINTS views or using SQL Server Management Studio (SSMS) to look up the constraint.
SELECT
CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
TABLE_NAME = 'Reservations' AND
CONSTRAINT_TYPE = 'CHECK';
Once I execute the above query, it gives me the following output:
Once you find the constraint name, then you can drop the constraint by using the following SQL Statement. You just need to replace the name that you found in your query. In my case, the check constraint name is CK__Reservati__Statu__60A75C0F.
ALTER TABLE Reservations DROP CONSTRAINT CK__Reservati__Statu__60A75C0F;
Once you drop the constraint, now, you can add a new check constraint that includes the “Partially Cancelled” status along with the existing ones by executing the following SQL Statement. Here, explicitly, I am specifying the constraint name as CK_Reservations_Status.
ALTER TABLE Reservations
ADD CONSTRAINT CK_Reservations_Status CHECK (Status IN ('Reserved', 'Checked-in', 'Checked-out', 'Cancelled', 'Partially Cancelled'));
Stored Procedures for Managing Hotel Cancellation Module:
We will start by creating the following stored procedures in the SQL Server database to effectively Manage the Hotel Cancellation Functionalities.
Stored Procedure to Get Cancellation Policies
The following stored procedure retrieves active cancellation policies currently applicable. It is used primarily to display these policies to users or administrators to inform them of the terms under which bookings can be canceled and the associated fees.
-- Get Cancellation Policies
-- This stored procedure retrieves active cancellation policies for display purposes.
CREATE OR ALTER PROCEDURE spGetCancellationPolicies
@Status BIT OUTPUT, -- Output parameter for status (1 = Success, 0 = Failure)
@Message NVARCHAR(255) OUTPUT -- Output parameter for messages
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
SELECT PolicyID, Description, CancellationChargePercentage, MinimumCharge, EffectiveFromDate, EffectiveToDate
FROM CancellationPolicies
WHERE EffectiveFromDate <= GETDATE() AND EffectiveToDate >= GETDATE();
SET @Status = 1; -- Success
SET @Message = 'Policies retrieved successfully.';
END TRY
BEGIN CATCH
SET @Status = 0; -- Failure
SET @Message = ERROR_MESSAGE();
END CATCH
END;
GO
Stored Procedure to Calculate Cancellation Charges
The following stored procedure calculates the cancellation charges based on the applicable cancellation policies. It differentiates between full and partial cancellations and computes the charges accordingly, which is essential for financial management and ensuring that cancellation policies are enforced correctly.
-- Calculate Cancellation Charges
-- Calculates the cancellation charges based on the policies.
CREATE OR ALTER PROCEDURE spCalculateCancellationCharges
@ReservationID INT,
@RoomsCancelled RoomIDTableType READONLY,
@TotalCost DECIMAL(10,2) OUTPUT,
@CancellationCharge DECIMAL(10,2) OUTPUT,
@CancellationPercentage DECIMAL(10,2) OUTPUT,
@PolicyDescription NVARCHAR(255) OUTPUT,
@Status BIT OUTPUT,
@Message NVARCHAR(MAX) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CheckInDate DATE;
DECLARE @TotalRoomsCount INT, @CancelledRoomsCount INT;
BEGIN TRY
-- Fetch check-in date
SELECT @CheckInDate = CheckInDate FROM Reservations WHERE ReservationID = @ReservationID;
IF @CheckInDate IS NULL
BEGIN
SET @Status = 0; -- Failure
SET @Message = 'No reservation found with the given ID.';
RETURN;
END
-- Determine if the cancellation is full or partial
SELECT @TotalRoomsCount = COUNT(*) FROM ReservationRooms WHERE ReservationID = @ReservationID;
SELECT @CancelledRoomsCount = COUNT(*) FROM @RoomsCancelled;
IF @CancelledRoomsCount = @TotalRoomsCount
BEGIN
-- Full cancellation: Calculate based on total reservation cost
SELECT @TotalCost = SUM(TotalAmount)
FROM Payments
WHERE ReservationID = @ReservationID;
END
ELSE
BEGIN
-- Partial cancellation: Calculate based on specific rooms' costs from PaymentDetails
SELECT @TotalCost = SUM(pd.Amount)
FROM PaymentDetails pd
INNER JOIN ReservationRooms rr ON pd.ReservationRoomID = rr.ReservationRoomID
INNER JOIN @RoomsCancelled rc ON rr.RoomID = rc.RoomID
WHERE rr.ReservationID = @ReservationID;
END
-- Check if total cost was calculated
IF @TotalCost IS NULL
BEGIN
SET @Status = 0; -- Failure
SET @Message = 'Failed to calculate total costs.';
RETURN;
END
-- Fetch the appropriate cancellation policy based on the check-in date
SELECT TOP 1 @CancellationPercentage = CancellationChargePercentage,
@PolicyDescription = Description
FROM CancellationPolicies
WHERE EffectiveFromDate <= @CheckInDate AND EffectiveToDate >= @CheckInDate
ORDER BY EffectiveFromDate DESC; -- In case of overlapping policies, the most recent one is used
-- Calculate the cancellation charge
SET @CancellationCharge = @TotalCost * (@CancellationPercentage / 100);
SET @Status = 1; -- Success
SET @Message = 'Calculation successful';
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK;
SET @Status = 0; -- Failure
SET @Message = ERROR_MESSAGE();
END CATCH
END;
GO
Stored Procedure to Create Cancellation Request
The following stored procedure initiates a cancellation request. It validates the reservation details, checks whether the requested rooms have already been canceled, and records a new cancellation request if valid. This is important for managing cancellations efficiently and ensuring that only eligible bookings are processed for cancellation.
-- Create Cancellation Request
-- This Stored Procedure creates a cancellation request after validating the provided information.
CREATE OR ALTER PROCEDURE spCreateCancellationRequest
@UserID INT,
@ReservationID INT,
@RoomsCancelled RoomIDTableType READONLY, -- Table-valued parameter
@CancellationReason NVARCHAR(MAX),
@Status BIT OUTPUT, -- Output parameter for operation status
@Message NVARCHAR(255) OUTPUT, -- Output parameter for operation message
@CancellationRequestID INT OUTPUT -- Output parameter to store the newly created CancellationRequestID
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON; -- Automatically roll-back the transaction on error.
DECLARE @CancellationType NVARCHAR(50);
DECLARE @TotalRooms INT, @CancelledRoomsCount INT, @RemainingRoomsCount INT;
DECLARE @ExistingStatus NVARCHAR(50);
DECLARE @CheckInDate DATE, @CheckOutDate DATE;
-- Retrieve reservation details
SELECT @ExistingStatus = Status, @CheckInDate = CheckInDate, @CheckOutDate = CheckOutDate
FROM Reservations
WHERE ReservationID = @ReservationID;
-- Validation for reservation status and dates
IF @ExistingStatus = 'Cancelled' OR GETDATE() >= @CheckInDate
BEGIN
SET @Status = 0; -- Failure
SET @Message = 'Cancellation not allowed. Reservation already fully cancelled or past check-in date.';
RETURN;
END
-- Prevent cancellation of already cancelled or pending cancellation rooms
IF EXISTS (
SELECT 1
FROM CancellationDetails cd
JOIN CancellationRequests cr ON cd.CancellationRequestID = cr.CancellationRequestID
JOIN ReservationRooms rr ON cd.ReservationRoomID = rr.ReservationRoomID
JOIN @RoomsCancelled rc ON rr.RoomID = rc.RoomID
WHERE cr.ReservationID = @ReservationID AND cr.Status IN ('Approved', 'Pending')
)
BEGIN
SET @Status = 0; -- Failure
SET @Message = 'One or more rooms have already been cancelled or cancellation is pending.';
RETURN;
END
SELECT @TotalRooms = COUNT(*) FROM ReservationRooms WHERE ReservationID = @ReservationID;
SELECT @CancelledRoomsCount = COUNT(*) FROM CancellationDetails cd
JOIN CancellationRequests cr ON cd.CancellationRequestID = cr.CancellationRequestID
WHERE cr.ReservationID = @ReservationID AND cr.Status IN ('Approved');
-- Calculate remaining rooms that are not yet cancelled
SET @RemainingRoomsCount = @TotalRooms - @CancelledRoomsCount;
-- Determine the type of cancellation based on remaining rooms to be cancelled
IF (@RemainingRoomsCount = (SELECT COUNT(*) FROM @RoomsCancelled))
SET @CancellationType = 'Full'
ELSE
SET @CancellationType = 'Partial';
BEGIN TRY
BEGIN TRANSACTION
-- Insert into CancellationRequests
INSERT INTO CancellationRequests (ReservationID, UserID, CancellationType, RequestedOn, Status, CancellationReason)
VALUES (@ReservationID, @UserID, @CancellationType, GETDATE(), 'Pending', @CancellationReason);
SET @CancellationRequestID = SCOPE_IDENTITY();
-- Insert into CancellationDetails for rooms not yet cancelled
INSERT INTO CancellationDetails (CancellationRequestID, ReservationRoomID)
SELECT @CancellationRequestID, rr.ReservationRoomID
FROM ReservationRooms rr
JOIN @RoomsCancelled rc ON rr.RoomID = rc.RoomID
WHERE rr.ReservationID = @ReservationID;
COMMIT TRANSACTION;
SET @Status = 1; -- Success
SET @Message = 'Cancellation request created successfully.';
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
SET @Status = 0; -- Failure
SET @Message = ERROR_MESSAGE();
END CATCH
END;
GO
Stored Procedure to Get All Cancellations
The following Stored procedure fetches all cancellation requests, optionally filtered by status and date range. It supports administrative needs to monitor, review, and manage cancellation requests across the hotel booking system.
-- Get All Cancellations
-- This procedure fetches all cancellations based on the optional status filter.
CREATE OR ALTER PROCEDURE spGetAllCancellations
@Status NVARCHAR(50) = NULL,
@DateFrom DATETIME = NULL,
@DateTo DATETIME = NULL,
@StatusOut BIT OUTPUT, -- Output parameter for operation status
@MessageOut NVARCHAR(255) OUTPUT -- Output parameter for operation message
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX), @Params NVARCHAR(MAX);
-- Initialize dynamic SQL query
SET @SQL = N'SELECT CancellationRequestID, ReservationID, UserID, CancellationType, RequestedOn, Status FROM CancellationRequests WHERE 1=1';
-- Append conditions dynamically based on the input parameters
IF @Status IS NOT NULL
SET @SQL += N' AND Status = @Status';
IF @DateFrom IS NOT NULL
SET @SQL += N' AND RequestedOn >= @DateFrom';
IF @DateTo IS NOT NULL
SET @SQL += N' AND RequestedOn <= @DateTo';
-- Define parameters for dynamic SQL
SET @Params = N'@Status NVARCHAR(50), @DateFrom DATETIME, @DateTo DATETIME';
BEGIN TRY
-- Execute dynamic SQL
EXEC sp_executesql @SQL, @Params, @Status = @Status, @DateFrom = @DateFrom, @DateTo = @DateTo;
-- If successful, set output parameters
SET @StatusOut = 1; -- Success
SET @MessageOut = 'Cancellations retrieved successfully.';
END TRY
BEGIN CATCH
-- If an error occurs, set output parameters to indicate failure
SET @StatusOut = 0; -- Failure
SET @MessageOut = ERROR_MESSAGE();
END CATCH
END;
GO
Stored Procedure to Review Cancellation Request
The following Stored procedure is used by administrators to review and either approve or reject a cancellation request. It includes functionality to update the status of rooms and reservations based on the approval status and to calculate and record cancellation charges if the cancellation is approved.
-- Review Cancellation Request
-- This procedure is used by an admin to review and either approve or reject a cancellation request.
CREATE OR ALTER PROCEDURE spReviewCancellationRequest
@CancellationRequestID INT,
@AdminUserID INT,
@ApprovalStatus NVARCHAR(50), -- 'Approved' or 'Rejected'
@Status BIT OUTPUT, -- Output parameter for operation status
@Message NVARCHAR(255) OUTPUT -- Output parameter for operation message
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON; -- Automatically roll-back the transaction on error.
DECLARE @ReservationID INT, @CalcStatus BIT, @CalcMessage NVARCHAR(MAX);
DECLARE @RoomsCancelled AS RoomIDTableType;
DECLARE @CalcTotalCost DECIMAL(10,2), @CalcCancellationCharge DECIMAL(10,2),
@CalcCancellationPercentage DECIMAL(10,2), @CalcPolicyDescription NVARCHAR(255);
BEGIN TRY
-- Validate the existence of the Cancellation Request
IF NOT EXISTS (SELECT 1 FROM CancellationRequests WHERE CancellationRequestID = @CancellationRequestID)
BEGIN
SET @Status = 0; -- Failure
SET @Message = 'Cancellation request does not exist.';
RETURN;
END
-- Validate the Admin User exists and is active
IF NOT EXISTS (SELECT 1 FROM Users WHERE UserID = @AdminUserID AND IsActive = 1)
BEGIN
SET @Status = 0; -- Failure
SET @Message = 'Admin user does not exist or is not active.';
RETURN;
END
-- Validate the Approval Status
IF @ApprovalStatus NOT IN ('Approved', 'Rejected')
BEGIN
SET @Status = 0; -- Failure
SET @Message = 'Invalid approval status.';
RETURN;
END
BEGIN TRANSACTION
-- Update the Cancellation Requests
UPDATE CancellationRequests
SET Status = @ApprovalStatus, AdminReviewedByID = @AdminUserID, ReviewDate = GETDATE()
WHERE CancellationRequestID = @CancellationRequestID;
SELECT @ReservationID = ReservationID FROM CancellationRequests WHERE CancellationRequestID = @CancellationRequestID;
IF @ApprovalStatus = 'Approved'
BEGIN
-- Fetch all rooms associated with the cancellation request
INSERT INTO @RoomsCancelled (RoomID)
SELECT rr.RoomID
FROM CancellationDetails cd
JOIN ReservationRooms rr ON cd.ReservationRoomID = rr.ReservationRoomID
WHERE cd.CancellationRequestID = @CancellationRequestID;
-- Call the calculation procedure
EXEC spCalculateCancellationCharges
@ReservationID = @ReservationID,
@RoomsCancelled = @RoomsCancelled,
@TotalCost = @CalcTotalCost OUTPUT,
@CancellationCharge = @CalcCancellationCharge OUTPUT,
@CancellationPercentage = @CalcCancellationPercentage OUTPUT,
@PolicyDescription = @CalcPolicyDescription OUTPUT,
@Status = @CalcStatus OUTPUT,
@Message = @CalcMessage OUTPUT;
IF @CalcStatus = 0 -- Check if the charge calculation was unsuccessful
BEGIN
SET @Status = 0; -- Failure
SET @Message = 'Failed to calculate cancellation charges: ' + @CalcMessage;
ROLLBACK TRANSACTION;
RETURN;
END
-- Insert into CancellationCharges table
INSERT INTO CancellationCharges (CancellationRequestID, TotalCost, CancellationCharge, CancellationPercentage, PolicyDescription)
VALUES (@CancellationRequestID, @CalcTotalCost, @CalcCancellationCharge, @CalcCancellationPercentage, @CalcPolicyDescription);
UPDATE Rooms
SET Status = 'Available'
WHERE RoomID IN (SELECT RoomID FROM @RoomsCancelled);
UPDATE Reservations
SET Status = CASE
WHEN (SELECT COUNT(*) FROM ReservationRooms WHERE ReservationID = @ReservationID) =
(SELECT COUNT(*) FROM @RoomsCancelled)
THEN 'Cancelled'
ELSE 'Partially Cancelled'
END
WHERE ReservationID = @ReservationID;
END
COMMIT TRANSACTION;
SET @Status = 1; -- Success
SET @Message = 'Cancellation request handled successfully.';
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
SET @Status = 0; -- Failure
SET @Message = ERROR_MESSAGE();
END CATCH
END;
GO
Stored Procedure to Get Cancellations for Refund Processing
The following stored procedure will retrieve cancellation requests that meet the specific refund criteria. The Cancellations that are Approved by the admin are considered for Refunding.
-- Get Cancellations for Refund
-- This procedure is used by an admin to fetch cancellations that are approved and either have no refund record
-- or need refund action (Pending or Failed, excluding Completed)
CREATE OR ALTER PROCEDURE spGetCancellationsForRefund
AS
BEGIN
SET NOCOUNT ON;
SELECT
cr.CancellationRequestID,
cr.ReservationID,
cr.UserID,
cr.CancellationType,
cr.RequestedOn,
cr.Status,
ISNULL(r.RefundID, 0) AS RefundID, -- Use 0 or another appropriate default value to indicate no refund has been initiated
ISNULL(r.RefundStatus, 'Not Initiated') AS RefundStatus -- Use 'Not Initiated' or another appropriate status
FROM
CancellationRequests cr
LEFT JOIN
Refunds r ON cr.CancellationRequestID = r.CancellationRequestID
WHERE
cr.Status = 'Approved'
AND (r.RefundStatus IS NULL OR r.RefundStatus IN ('Pending', 'Failed'));
END;
GO
Stored Procedure to Initiate Refund
The following stored procedure processes refunds for approved cancellations. After deducting the cancellation charges, it calculates the net refund amount and records the refund details. This is important for managing customer refunds efficiently and ensuring customer satisfaction.
-- Process Refund
-- Processes refunds for approved cancellations.
CREATE OR ALTER PROCEDURE spProcessRefund
@CancellationRequestID INT,
@ProcessedByUserID INT,
@RefundMethodID INT,
@RefundID INT OUTPUT, -- Output parameter for the newly created Refund ID
@Status BIT OUTPUT, -- Output parameter for operation status
@Message NVARCHAR(255) OUTPUT -- Output parameter for operation message
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON; -- Automatically roll-back the transaction on error.
DECLARE @PaymentID INT, @RefundAmount DECIMAL(10,2), @CancellationCharge DECIMAL(10,2), @NetRefundAmount DECIMAL(10,2);
BEGIN TRY
BEGIN TRANSACTION
-- Validate the existence of the CancellationRequestID and its approval status
IF NOT EXISTS (SELECT 1 FROM CancellationRequests
WHERE CancellationRequestID = @CancellationRequestID AND Status = 'Approved')
BEGIN
SET @Status = 0; -- Failure
SET @Message = 'Invalid CancellationRequestID or the request has not been approved.';
RETURN;
END
-- Retrieve the total amount and cancellation charge from the CancellationCharges table
SELECT
@PaymentID = p.PaymentID,
@RefundAmount = cc.TotalCost,
@CancellationCharge = cc.CancellationCharge
FROM CancellationCharges cc
JOIN Payments p ON p.ReservationID = (SELECT ReservationID FROM CancellationRequests WHERE CancellationRequestID = @CancellationRequestID)
WHERE cc.CancellationRequestID = @CancellationRequestID;
-- Calculate the net refund amount after deducting the cancellation charge
SET @NetRefundAmount = @RefundAmount - @CancellationCharge;
-- Insert into Refunds table, mark the Refund Status as Pending
INSERT INTO Refunds (PaymentID, RefundAmount, RefundDate, RefundReason, RefundMethodID, ProcessedByUserID, RefundStatus, CancellationCharge, NetRefundAmount, CancellationRequestID)
VALUES (@PaymentID, @NetRefundAmount, GETDATE(), 'Cancellation Approved', @RefundMethodID, @ProcessedByUserID, 'Pending', @CancellationCharge, @NetRefundAmount, @CancellationRequestID);
-- Capture the newly created Refund ID
SET @RefundID = SCOPE_IDENTITY();
COMMIT TRANSACTION;
SET @Status = 1; -- Success
SET @Message = 'Refund processed successfully.';
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
SET @Status = 0; -- Failure
SET @Message = ERROR_MESSAGE();
END CATCH
END;
GO
Stored Procedure to Update Refund Status
The following Stored Procedure updates the status of a refund (e.g., from pending to processed). It ensures that the refund process is tracked and updated properly in the system, which is vital for financial accounting and customer service.
-- Update Refund Status
CREATE OR ALTER PROCEDURE spUpdateRefundStatus
@RefundID INT,
@NewRefundStatus NVARCHAR(50),
@Status BIT OUTPUT,
@Message NVARCHAR(255) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON; -- Automatically roll-back the transaction on error.
-- Define valid statuses, adjust these as necessary for your application
DECLARE @ValidStatuses TABLE (Status NVARCHAR(50));
INSERT INTO @ValidStatuses VALUES ('Pending'), ('Processed'), ('Completed'), ('Failed');
BEGIN TRY
BEGIN TRANSACTION
-- Check current status of the refund to avoid updating final states like 'Completed'
DECLARE @CurrentStatus NVARCHAR(50);
SELECT @CurrentStatus = RefundStatus FROM Refunds WHERE RefundID = @RefundID;
IF @CurrentStatus IS NULL
BEGIN
SET @Status = 0; -- Failure
SET @Message = 'Refund not found.';
ROLLBACK TRANSACTION;
RETURN;
END
IF @CurrentStatus = 'Completed'
BEGIN
SET @Status = 0; -- Failure
SET @Message = 'Refund is already completed and cannot be updated.';
ROLLBACK TRANSACTION;
RETURN;
END
-- Validate the new refund status
IF NOT EXISTS (SELECT 1 FROM @ValidStatuses WHERE Status = @NewRefundStatus)
BEGIN
SET @Status = 0; -- Failure
SET @Message = 'Invalid new refund status provided.';
ROLLBACK TRANSACTION;
RETURN;
END
-- Update the Refund Status if validations pass
UPDATE Refunds
SET RefundStatus = @NewRefundStatus
WHERE RefundID = @RefundID;
IF @@ROWCOUNT = 0
BEGIN
SET @Status = 0; -- Failure
SET @Message = 'No refund found with the provided RefundID.';
ROLLBACK TRANSACTION;
RETURN;
END
COMMIT TRANSACTION;
SET @Status = 1; -- Success
SET @Message = 'Refund status updated successfully.';
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
SET @Status = 0; -- Failure
SET @Message = ERROR_MESSAGE();
END CATCH
END;
GO
Creating Hotel Cancellation DTOs:
Next, we need to create the DTOs required for managing Hotel Cancellations. Please create a folder called CancellationDTOs inside the DTOs folder, where we will add all the DTOs related to Hotel Cancellations.
CancellationPoliciesResponseDTO
Create a class file named CancellationPoliciesResponseDTO.cs within the CancellationDTOs folder and copy and paste the following code. This DTO will hold the properties required to fetch the policy details.
namespace HotelBookingAPI.DTOs.CancellationDTOs
{
public class CancellationPoliciesResponseDTO
{
public bool Status { get; set; }
public string Message { get; set; }
public List<CancellationPolicyDTO> Policies { get; set; }
}
public class CancellationPolicyDTO
{
public int PolicyID { get; set; }
public string Description { get; set; }
public decimal CancellationChargePercentage { get; set; }
public decimal MinimumCharge { get; set; }
public DateTime EffectiveFromDate { get; set; }
public DateTime EffectiveToDate { get; set; }
}
}
CreateCancellationRequestDTO
Create a class file named CreateCancellationRequestDTO.cs within the CancellationDTOs folder, and then copy and paste the following code. This DTO will hold the properties required to create a new Cancellation Request.
using System.ComponentModel.DataAnnotations;
namespace HotelBookingAPI.DTOs.CancellationDTOs
{
public class CreateCancellationRequestDTO
{
[Required(ErrorMessage = "UserID is required.")]
public int UserID { get; set; }
[Required(ErrorMessage = "ReservationID is required.")]
public int ReservationID { get; set; }
[Required(ErrorMessage = "RoomsCancelled list cannot be empty.")]
[MinLength(1, ErrorMessage = "At least one room must be cancelled.")]
public List<int> RoomsCancelled { get; set; }
public string? CancellationReason { get; set; }
}
}
CreateCancellationResponseDTO
Create a class file named CreateCancellationResponseDTO.cs within the CancellationDTOs folder, and then copy and paste the following code. This DTO will hold the properties that we return after creating a new Cancellation Request.
namespace HotelBookingAPI.DTOs.CancellationDTOs
{
public class CreateCancellationResponseDTO
{
public int CancellationId { get; set; }
public bool Status { get; set; }
public string Message { get; set; }
}
}
AllCancellationsRequestDTO
Create a class file named AllCancellationsRequestDTO.cs within the CancellationDTOs folder, and then copy and paste the following code. This DTO will hold the properties required to fetch all cancellation information based on the provided optional parameters.
using System.ComponentModel.DataAnnotations;
namespace HotelBookingAPI.DTOs.CancellationDTOs
{
public class AllCancellationsRequestDTO
{
[StringLength(50, ErrorMessage = "Status length cannot exceed 50 characters.")]
public string? Status { get; set; }
[DataType(DataType.Date)]
public DateTime? DateFrom { get; set; }
[DataType(DataType.Date)]
public DateTime? DateTo { get; set; }
}
}
AllCancellationsResponseDTO
Create a class file named AllCancellationsResponseDTO.cs within the CancellationDTOs folder, and then copy and paste the following code. This DTO will hold the properties that return Cancellation information.
namespace HotelBookingAPI.DTOs.CancellationDTOs
{
public class AllCancellationsResponseDTO
{
public bool Status { get; set; }
public string Message { get; set; }
public List<CancellationRequestDTO> Cancellations { get; set; }
}
public class CancellationRequestDTO
{
public int CancellationRequestID { get; set; }
public int ReservationID { get; set; }
public int UserID { get; set; }
public string CancellationType { get; set; }
public DateTime RequestedOn { get; set; }
public string Status { get; set; }
}
}
CalculateCancellationChargesReuestDTO
Create a class file named CalculateCancellationChargesReuestDTO.cs within the CancellationDTOs folder, and then copy and paste the following code. This DTO will hold the properties required to calculate the cancellation charges.
namespace HotelBookingAPI.DTOs.CancellationDTOs
{
public class CalculateCancellationChargesResponseDTO
{
public decimal TotalCost { get; set; }
public decimal CancellationCharge { get; set; }
public decimal CancellationPercentage { get; set; }
public string PolicyDescription { get; set; }
public bool Status { get; set; }
public string Message { get; set; }
}
}
CalculateCancellationChargesResponseDTO
Create a class file named CalculateCancellationChargesResponseDTO.cs within the CancellationDTOs folder, and then copy and paste the following code. This DTO will hold the properties that we return after calculating the Cancellation Charges.
using System.ComponentModel.DataAnnotations;
namespace HotelBookingAPI.DTOs.CancellationDTOs
{
public class CalculateCancellationChargesRequestDTO
{
[Required(ErrorMessage = "ReservationID is required.")]
public int ReservationID { get; set; }
[Required(ErrorMessage = "RoomsCancelled list cannot be empty.")]
[MinLength(1, ErrorMessage = "At least one room must be cancelled.")]
public List<int> RoomsCancelled { get; set; }
}
}
ReviewCancellationRequestDTO
Create a class file named ReviewCancellationRequestDTO.cs within the CancellationDTOs folder, and then copy and paste the following code. This DTO will hold the properties required for the admin to approve or reject the Cancellation Request after reviewing it.
using System.ComponentModel.DataAnnotations;
namespace HotelBookingAPI.DTOs.CancellationDTOs
{
public class ReviewCancellationRequestDTO
{
[Required(ErrorMessage = "CancellationRequestID is required.")]
public int CancellationRequestID { get; set; }
[Required(ErrorMessage = "AdminUserID is required.")]
public int AdminUserID { get; set; }
[Required(ErrorMessage = "ApprovalStatus is required.")]
[StringLength(50, ErrorMessage = "ApprovalStatus length cannot exceed 50 characters.")]
public string ApprovalStatus { get; set; }
}
}
ReviewCancellationResponseDTO
Create a class file named ReviewCancellationResponseDTO.cs within the CancellationDTOs folder, and then copy and paste the following code. This DTO will hold the required properties that we return after approving and rejecting the Cancellation Request.
namespace HotelBookingAPI.DTOs.CancellationDTOs
{
public class ReviewCancellationResponseDTO
{
public bool Status { get; set; }
public string Message { get; set; }
}
}
CancellationForRefundResponseDTO
Create a class file named CancellationForRefundResponseDTO.cs within the CancellationDTOs folder and copy and paste the following code. This DTO will hold the properties of the Cancellation that will be refunded.
namespace HotelBookingAPI.DTOs.CancellationDTOs
{
public class CancellationForRefundResponseDTO
{
public bool Status { get; set; }
public string Message { get; set; }
public List<CancellationForRefundDTO> CancellationsToRefund { get; set; }
}
public class CancellationForRefundDTO
{
public int CancellationRequestID { get; set; }
public int ReservationID { get; set; }
public int UserID { get; set; }
public string CancellationType { get; set; }
public DateTime RequestedOn { get; set; }
public string Status { get; set; }
public int RefundID { get; set; }
public string RefundStatus { get; set; }
}
}
ProcessRefundRequestDTO
Create a class file named ProcessRefundRequestDTO.cs within the CancellationDTOs folder, and then copy and paste the following code. This DTO will hold the properties required for processing the Refund of an approved Cancellation Request.
using System.ComponentModel.DataAnnotations;
namespace HotelBookingAPI.DTOs.CancellationDTOs
{
public class ProcessRefundRequestDTO
{
[Required(ErrorMessage = "CancellationRequestID is required.")]
public int CancellationRequestID { get; set; }
[Required(ErrorMessage = "ProcessedByUserID is required.")]
public int ProcessedByUserID { get; set; }
[Required(ErrorMessage = "RefundMethodID is required.")]
public int RefundMethodID { get; set; }
}
}
ProcessRefundResponseDTO
Create a class file named ProcessRefundResponseDTO.cs within the CancellationDTOs folder, and then copy and paste the following code. This DTO will hold the required properties that we return after processing the refund.
namespace HotelBookingAPI.DTOs.CancellationDTOs
{
public class ProcessRefundResponseDTO
{
public int RefundID { get; set; }
public bool Status { get; set; }
public string Message { get; set; }
}
}
UpdateRefundStatusRequestDTO
Create a class file named UpdateRefundStatusRequestDTO.cs within the CancellationDTOs folder, and then copy and paste the following code. This DTO will hold the properties required to update the Refund Request Status.
using System.ComponentModel.DataAnnotations;
namespace HotelBookingAPI.DTOs.CancellationDTOs
{
public class UpdateRefundStatusRequestDTO
{
[Required(ErrorMessage = "RefundID is required.")]
public int RefundID { get; set; }
[Required(ErrorMessage = "NewRefundStatus is required.")]
[StringLength(50, ErrorMessage = "NewRefundStatus length cannot exceed 50 characters.")]
public string NewRefundStatus { get; set; }
}
}
UpdateRefundStatusResponseDTO
Create a class file named UpdateRefundStatusResponseDTO.cs within the CancellationDTOs folder, and then copy and paste the following code. This DTO will hold the required properties that we return after updating the status of the Refund Request.
namespace HotelBookingAPI.DTOs.CancellationDTOs
{
public class UpdateRefundStatusResponseDTO
{
public bool Status { get; set; }
public string Message { get; set; }
}
}
Creating Hotel Cancellation Repository:
Next, we need to create the Hotel Cancellation Repository class to implement the business and data access logic required for Hotel Cancellation and Refund Processing. This Repository class will consume the Hotel Cancellation and Refund Related Stored Procedures and DTOs. So, create a class file named CancellationRepository.cs within the Repository folder and copy and paste the following code.
using HotelBookingAPI.Connection;
using HotelBookingAPI.DTOs.CancellationDTOs;
using Microsoft.Data.SqlClient;
using System.Data;
namespace HotelBookingAPI.Repository
{
public class CancellationRepository
{
private readonly SqlConnectionFactory _connectionFactory;
public CancellationRepository(SqlConnectionFactory connectionFactory)
{
_connectionFactory = connectionFactory;
}
public async Task<CancellationPoliciesResponseDTO> GetCancellationPoliciesAsync()
{
var response = new CancellationPoliciesResponseDTO
{
Policies = new List<CancellationPolicyDTO>()
};
try
{
using var connection = _connectionFactory.CreateConnection();
using var command = new SqlCommand("spGetCancellationPolicies", connection);
command.CommandType = CommandType.StoredProcedure;
var statusParam = new SqlParameter("@Status", SqlDbType.Bit) { Direction = ParameterDirection.Output };
var messageParam = new SqlParameter("@Message", SqlDbType.NVarChar, 255) { Direction = ParameterDirection.Output };
command.Parameters.Add(statusParam);
command.Parameters.Add(messageParam);
await connection.OpenAsync();
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
response.Policies.Add(new CancellationPolicyDTO
{
PolicyID = reader.GetInt32(reader.GetOrdinal("PolicyID")),
Description = reader.GetString(reader.GetOrdinal("Description")),
CancellationChargePercentage = reader.GetDecimal(reader.GetOrdinal("CancellationChargePercentage")),
MinimumCharge = reader.GetDecimal(reader.GetOrdinal("MinimumCharge")),
EffectiveFromDate = reader.GetDateTime(reader.GetOrdinal("EffectiveFromDate")),
EffectiveToDate = reader.GetDateTime(reader.GetOrdinal("EffectiveToDate"))
});
}
}
response.Status = (bool)statusParam.Value;
response.Message = messageParam.Value as string;
}
catch (SqlException ex)
{
response.Status = false;
response.Message = $"Database error occurred: {ex.Message}";
}
catch (Exception ex)
{
response.Status = false;
response.Message = $"An error occurred: {ex.Message}";
}
return response;
}
public async Task<CalculateCancellationChargesResponseDTO> CalculateCancellationChargesAsync(CalculateCancellationChargesRequestDTO request)
{
var response = new CalculateCancellationChargesResponseDTO();
try
{
using var connection = _connectionFactory.CreateConnection();
using var command = new SqlCommand("spCalculateCancellationCharges", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@ReservationID", request.ReservationID);
command.Parameters.AddWithValue("@RoomsCancelled", CreateRoomsCancelledTable(request.RoomsCancelled));
var totalCostParam = new SqlParameter("@TotalCost", SqlDbType.Decimal) { Precision = 10, Scale = 2, Direction = ParameterDirection.Output };
var cancellationChargeParam = new SqlParameter("@CancellationCharge", SqlDbType.Decimal) { Precision = 10, Scale = 2, Direction = ParameterDirection.Output };
var cancellationPercentageParam = new SqlParameter("@CancellationPercentage", SqlDbType.Decimal) { Precision = 10, Scale = 2, Direction = ParameterDirection.Output };
var policyDescriptionParam = new SqlParameter("@PolicyDescription", SqlDbType.NVarChar, 255) { Direction = ParameterDirection.Output };
var statusParam = new SqlParameter("@Status", SqlDbType.Bit) { Direction = ParameterDirection.Output };
var messageParam = new SqlParameter("@Message", SqlDbType.NVarChar, -1) { Direction = ParameterDirection.Output };
command.Parameters.Add(totalCostParam);
command.Parameters.Add(cancellationChargeParam);
command.Parameters.Add(cancellationPercentageParam);
command.Parameters.Add(policyDescriptionParam);
command.Parameters.Add(statusParam);
command.Parameters.Add(messageParam);
await connection.OpenAsync();
await command.ExecuteNonQueryAsync();
response.Status = (bool)statusParam.Value;
response.Message = messageParam.Value as string;
if (response.Status)
{
response.TotalCost = (decimal)totalCostParam.Value;
response.CancellationCharge = (decimal)cancellationChargeParam.Value;
response.CancellationPercentage = (decimal)cancellationPercentageParam.Value;
response.PolicyDescription = policyDescriptionParam.Value as string;
}
}
catch (SqlException ex)
{
response.Status = false;
response.Message = $"Database error occurred: {ex.Message}";
}
catch (Exception ex)
{
response.Status = false;
response.Message = $"An error occurred: {ex.Message}";
}
return response;
}
public async Task<CreateCancellationResponseDTO> CreateCancellationRequestAsync(CreateCancellationRequestDTO request)
{
var response = new CreateCancellationResponseDTO();
try
{
using var connection = _connectionFactory.CreateConnection();
using var command = new SqlCommand("spCreateCancellationRequest", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@UserID", request.UserID);
command.Parameters.AddWithValue("@ReservationID", request.ReservationID);
command.Parameters.AddWithValue("@RoomsCancelled", CreateRoomsCancelledTable(request.RoomsCancelled));
command.Parameters.AddWithValue("@CancellationReason", request.CancellationReason);
var messageParam = new SqlParameter("@Message", SqlDbType.NVarChar, 255) { Direction = ParameterDirection.Output };
var statusParam = new SqlParameter("@Status", SqlDbType.Bit) { Direction = ParameterDirection.Output };
var cancellationRequestIDParam = new SqlParameter("@CancellationRequestID", SqlDbType.Int) { Direction = ParameterDirection.Output };
command.Parameters.Add(statusParam);
command.Parameters.Add(messageParam);
command.Parameters.Add(cancellationRequestIDParam);
await connection.OpenAsync();
await command.ExecuteNonQueryAsync();
response.Status = (bool)statusParam.Value;
response.Message = messageParam.Value as string;
if(response.Status)
{
response.CancellationId = (int)command.Parameters["@CancellationRequestID"].Value;
}
}
catch (SqlException ex)
{
response.Status = false;
response.Message = $"Database error occurred: {ex.Message}";
}
catch (Exception ex)
{
response.Status = false;
response.Message = $"An error occurred: {ex.Message}";
}
return response;
}
public async Task<AllCancellationsResponseDTO> GetAllCancellationsAsync(AllCancellationsRequestDTO request)
{
var response = new AllCancellationsResponseDTO
{
Cancellations = new List<CancellationRequestDTO>()
};
try
{
using var connection = _connectionFactory.CreateConnection();
using var command = new SqlCommand("spGetAllCancellations", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Status", request.Status ?? (object)DBNull.Value);
command.Parameters.AddWithValue("@DateFrom", request.DateFrom ?? (object)DBNull.Value);
command.Parameters.AddWithValue("@DateTo", request.DateTo ?? (object)DBNull.Value);
var statusOutParam = new SqlParameter("@StatusOut", SqlDbType.Bit) { Direction = ParameterDirection.Output };
var messageOutParam = new SqlParameter("@MessageOut", SqlDbType.NVarChar, 255) { Direction = ParameterDirection.Output };
command.Parameters.Add(statusOutParam);
command.Parameters.Add(messageOutParam);
await connection.OpenAsync();
using (var reader = await command.ExecuteReaderAsync())
{
while (reader.Read())
{
response.Cancellations.Add(new CancellationRequestDTO
{
CancellationRequestID = reader.GetInt32(reader.GetOrdinal("CancellationRequestID")),
ReservationID = reader.GetInt32(reader.GetOrdinal("ReservationID")),
UserID = reader.GetInt32(reader.GetOrdinal("UserID")),
CancellationType = reader.GetString(reader.GetOrdinal("CancellationType")),
RequestedOn = reader.GetDateTime(reader.GetOrdinal("RequestedOn")),
Status = reader.GetString(reader.GetOrdinal("Status"))
});
}
}
response.Status = (bool)statusOutParam.Value;
response.Message = messageOutParam.Value as string;
}
catch (SqlException ex)
{
response.Status = false;
response.Message = $"Database error occurred: {ex.Message}";
}
catch (Exception ex)
{
response.Status = false;
response.Message = $"An error occurred: {ex.Message}";
}
return response;
}
public async Task<ReviewCancellationResponseDTO> ReviewCancellationRequestAsync(ReviewCancellationRequestDTO request)
{
var response = new ReviewCancellationResponseDTO();
try
{
using var connection = _connectionFactory.CreateConnection();
using var command = new SqlCommand("spReviewCancellationRequest", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@CancellationRequestID", request.CancellationRequestID);
command.Parameters.AddWithValue("@AdminUserID", request.AdminUserID);
command.Parameters.AddWithValue("@ApprovalStatus", request.ApprovalStatus);
var statusParam = new SqlParameter("@Status", SqlDbType.Bit) { Direction = ParameterDirection.Output };
var messageParam = new SqlParameter("@Message", SqlDbType.NVarChar, 255) { Direction = ParameterDirection.Output };
command.Parameters.Add(statusParam);
command.Parameters.Add(messageParam);
await connection.OpenAsync();
await command.ExecuteNonQueryAsync();
response.Status = (bool)statusParam.Value;
response.Message = messageParam.Value as string;
}
catch (SqlException ex)
{
response.Status = false;
response.Message = $"Database error occurred: {ex.Message}";
}
catch (Exception ex)
{
response.Status = false;
response.Message = $"An error occurred: {ex.Message}";
}
return response;
}
public async Task<CancellationForRefundResponseDTO> GetCancellationsForRefundAsync()
{
var response = new CancellationForRefundResponseDTO
{
CancellationsToRefund = new List<CancellationForRefundDTO>()
};
try
{
using var connection = _connectionFactory.CreateConnection();
using var command = new SqlCommand("spGetCancellationsForRefund", connection);
command.CommandType = CommandType.StoredProcedure;
await connection.OpenAsync();
using (var reader = await command.ExecuteReaderAsync())
{
while (reader.Read())
{
response.CancellationsToRefund.Add(new CancellationForRefundDTO
{
CancellationRequestID = reader.GetInt32("CancellationRequestID"),
ReservationID = reader.GetInt32("ReservationID"),
UserID = reader.GetInt32("UserID"),
CancellationType = reader.GetString("CancellationType"),
RequestedOn = reader.GetDateTime("RequestedOn"),
Status = reader.GetString("Status"),
RefundID = reader.GetInt32("RefundID"),
RefundStatus = reader.GetString("RefundStatus")
});
}
}
response.Status = true;
response.Message = $"Success";
}
catch (SqlException ex)
{
response.Status = false;
response.Message = $"Database error occurred: {ex.Message}";
}
catch (Exception ex)
{
response.Status = false;
response.Message = $"An error occurred: {ex.Message}";
}
return response;
}
public async Task<ProcessRefundResponseDTO> ProcessRefundAsync(ProcessRefundRequestDTO request)
{
var response = new ProcessRefundResponseDTO();
try
{
using var connection = _connectionFactory.CreateConnection();
using var command = new SqlCommand("spProcessRefund", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@CancellationRequestID", request.CancellationRequestID);
command.Parameters.AddWithValue("@ProcessedByUserID", request.ProcessedByUserID);
command.Parameters.AddWithValue("@RefundMethodID", request.RefundMethodID);
var refundIDParam = new SqlParameter("@RefundID", SqlDbType.Int) { Direction = ParameterDirection.Output };
var statusParam = new SqlParameter("@Status", SqlDbType.Bit) { Direction = ParameterDirection.Output };
var messageParam = new SqlParameter("@Message", SqlDbType.NVarChar, 255) { Direction = ParameterDirection.Output };
command.Parameters.Add(refundIDParam);
command.Parameters.Add(statusParam);
command.Parameters.Add(messageParam);
await connection.OpenAsync();
await command.ExecuteNonQueryAsync();
response.Status = (bool)statusParam.Value;
response.Message = messageParam.Value as string;
if(response.Status)
{
response.RefundID = (int)command.Parameters["@RefundID"].Value;
}
}
catch (SqlException ex)
{
response.Status = false;
response.Message = $"Database error occurred: {ex.Message}";
}
catch (Exception ex)
{
response.Status = false;
response.Message = $"An error occurred: {ex.Message}";
}
return response;
}
public async Task<UpdateRefundStatusResponseDTO> UpdateRefundStatusAsync(UpdateRefundStatusRequestDTO request)
{
var response = new UpdateRefundStatusResponseDTO();
try
{
using var connection = _connectionFactory.CreateConnection();
using var command = new SqlCommand("spUpdateRefundStatus", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@RefundID", request.RefundID);
command.Parameters.AddWithValue("@NewRefundStatus", request.NewRefundStatus);
var statusParam = new SqlParameter("@Status", SqlDbType.Bit) { Direction = ParameterDirection.Output };
var messageParam = new SqlParameter("@Message", SqlDbType.NVarChar, 255) { Direction = ParameterDirection.Output };
command.Parameters.Add(statusParam);
command.Parameters.Add(messageParam);
await connection.OpenAsync();
await command.ExecuteNonQueryAsync();
response.Status = (bool)statusParam.Value;
response.Message = messageParam.Value as string;
}
catch (SqlException ex)
{
response.Status = false;
response.Message = $"Database error occurred: {ex.Message}";
}
catch (Exception ex)
{
response.Status = false;
response.Message = $"An error occurred: {ex.Message}";
}
return response;
}
private DataTable CreateRoomsCancelledTable(IEnumerable<int> roomIds)
{
var table = new DataTable();
table.Columns.Add("RoomID", typeof(int));
foreach (var id in roomIds)
{
table.Rows.Add(id);
}
return table;
}
}
}
Explanation of Cancellation Repository Methods:
The CancellationRepository class interacts with the SQL Server database to handle various tasks related to managing cancellation requests for a hotel booking system. The following is the objective for each method within this repository class:
- GetCancellationPoliciesAsync: This method retrieves all active cancellation policies from the database using a stored procedure. It is useful for displaying cancellation policies to users, ensuring they understand the terms before making or cancelling a booking.
- CalculateCancellationChargesAsync: This method calculates the cancellation charges based on the specific cancellation policy applicable to a reservation. It takes a cancellation request and computes fees or penalties based on the number of rooms cancelled, the reservation details, and the cancellation policy in effect.
- CreateCancellationRequestAsync: Initiates a cancellation request for a booking. It records the details of the cancellation in the database, including which rooms are canceled and the reason for cancellation.
- GetAllCancellationsAsync: This method fetches all cancellations that meet certain criteria, such as status and date range, from the database. It allows administrators to review all cancellations over a specific period or inspect cancellations based on their current status.
- ReviewCancellationRequestAsync: Allows an administrator to review a cancellation request and either approve or reject it. This method updates the status of the cancellation request based on administrative review, impacting subsequent processing like refunds.
- GetCancellationsForRefundAsync: Retrieves a list of all approved cancellations that are pending a refund or where previous refund attempts have failed. This method is essential for ensuring that all eligible customers receive their refunds timely and accurately.
- ProcessRefundAsync: Processes a refund for an approved cancellation. This involves calculating the refund amount, updating the database with refund details, and possibly interacting with a payment gateway.
- UpdateRefundStatusAsync: This function updates the status of a refund in the system. It could be necessary after a refund has been processed through a payment gateway or if an error occurred during the refund process that needs rectification.
Register Cancellation Repository:
Next, we need to register the CancellationRepository into the dependency injection container so that the framework can inject the object wherever we need this repository instance, mostly in the Controller class. So, please add the following code to the Program.cs class file:
builder.Services.AddScoped<CancellationRepository>();
Creating Cancellation Controller:
Let us create the Hotel Cancellation Controller and use the methods defined in the above CancellationRepository class. So, create a new Empty API Controller named CancellationController within the Controllers folder and copy and paste the following code.
using HotelBookingAPI.DTOs.CancellationDTOs;
using HotelBookingAPI.Models;
using HotelBookingAPI.Repository;
using Microsoft.AspNetCore.Mvc;
using System.Net;
namespace HotelBookingAPI.Controllers
{
[ApiController]
[Route("[controller]")]
public class CancellationController : ControllerBase
{
private readonly ILogger<CancellationController> _logger;
private readonly CancellationRepository _cancellationRepository;
public CancellationController(ILogger<CancellationController> logger, CancellationRepository cancellationRepository)
{
_logger = logger;
_cancellationRepository = cancellationRepository;
}
[HttpGet("GetCancellationPolicies")]
public async Task<APIResponse<CancellationPoliciesResponseDTO>> GetCancellationPolicies()
{
_logger.LogInformation("Request received for GetCancellationPolicies");
try
{
var result = await _cancellationRepository.GetCancellationPoliciesAsync();
return new APIResponse<CancellationPoliciesResponseDTO>(result, "Cancellation policies retrieved successfully.");
}
catch (Exception ex)
{
_logger.LogError(ex, "Failed to retrieve cancellation policies");
return new APIResponse<CancellationPoliciesResponseDTO>(HttpStatusCode.InternalServerError, "Failed to retrieve cancellation policies", ex.Message);
}
}
[HttpPost("CalculateCancellationCharges")]
public async Task<APIResponse<CalculateCancellationChargesResponseDTO>> CalculateCancellationCharges([FromBody] CalculateCancellationChargesRequestDTO model)
{
_logger.LogInformation("Request Received for CalculateCancellationCharges: {@CalculateCancellationChargesRequestDTO}", model);
if (!ModelState.IsValid)
{
_logger.LogInformation("Invalid Data in the Request Body for CalculateCancellationCharges");
return new APIResponse<CalculateCancellationChargesResponseDTO>(HttpStatusCode.BadRequest, "Invalid Data in the Request Body");
}
try
{
var result = await _cancellationRepository.CalculateCancellationChargesAsync(model);
if (result.Status)
{
return new APIResponse<CalculateCancellationChargesResponseDTO>(result, "Cancellation charges calculated successfully.");
}
return new APIResponse<CalculateCancellationChargesResponseDTO>(HttpStatusCode.BadRequest, "Failed to calculate cancellation charges", result.Message);
}
catch (Exception ex)
{
_logger.LogError(ex, "Failed to calculate cancellation charges");
return new APIResponse<CalculateCancellationChargesResponseDTO>(HttpStatusCode.InternalServerError, "Failed to calculate cancellation charges", ex.Message);
}
}
[HttpPost("CreateCancellationRequest")]
public async Task<APIResponse<CreateCancellationResponseDTO>> CreateCancellationRequest([FromBody] CreateCancellationRequestDTO model)
{
_logger.LogInformation("Request Received for CreateCancellationRequest: {@CreateCancellationRequestDTO}", model);
if (!ModelState.IsValid)
{
_logger.LogInformation("Invalid Data in the Request Body for CreateCancellationRequest");
return new APIResponse<CreateCancellationResponseDTO>(HttpStatusCode.BadRequest, "Invalid Data in the Request Body");
}
try
{
var result = await _cancellationRepository.CreateCancellationRequestAsync(model);
if (result.Status)
{
return new APIResponse<CreateCancellationResponseDTO>(result, "Cancellation request created successfully.");
}
return new APIResponse<CreateCancellationResponseDTO>(HttpStatusCode.BadRequest, "Failed to create cancellation request", result.Message);
}
catch (Exception ex)
{
_logger.LogError(ex, "Failed to create cancellation request");
return new APIResponse<CreateCancellationResponseDTO>(HttpStatusCode.InternalServerError, "Failed to create cancellation request", ex.Message);
}
}
[HttpPost("GetAllCancellations")]
public async Task<APIResponse<AllCancellationsResponseDTO>> GetAllCancellations([FromBody] AllCancellationsRequestDTO model)
{
_logger.LogInformation("Request Received for GetAllCancellations: {@AllCancellationsRequestDTO}", model);
try
{
var result = await _cancellationRepository.GetAllCancellationsAsync(model);
return new APIResponse<AllCancellationsResponseDTO>(result, "All cancellations retrieved successfully.");
}
catch (Exception ex)
{
_logger.LogError(ex, "Failed to retrieve all cancellations");
return new APIResponse<AllCancellationsResponseDTO>(HttpStatusCode.InternalServerError, "Failed to retrieve all cancellations", ex.Message);
}
}
[HttpPost("ReviewCancellationRequest")]
public async Task<APIResponse<ReviewCancellationResponseDTO>> ReviewCancellationRequest([FromBody] ReviewCancellationRequestDTO model)
{
_logger.LogInformation("Request Received for ReviewCancellationRequest: {@ReviewCancellationRequestDTO}", model);
if (!ModelState.IsValid)
{
_logger.LogInformation("Invalid Data in the Request Body for ReviewCancellationRequest");
return new APIResponse<ReviewCancellationResponseDTO>(HttpStatusCode.BadRequest, "Invalid Data in the Request Body");
}
try
{
var result = await _cancellationRepository.ReviewCancellationRequestAsync(model);
if (result.Status)
{
return new APIResponse<ReviewCancellationResponseDTO>(result, "Cancellation request reviewed successfully.");
}
return new APIResponse<ReviewCancellationResponseDTO>(HttpStatusCode.BadRequest, "Failed to review cancellation request", result.Message);
}
catch (Exception ex)
{
_logger.LogError(ex, "Failed to review cancellation request");
return new APIResponse<ReviewCancellationResponseDTO>(HttpStatusCode.InternalServerError, "Failed to review cancellation request", ex.Message);
}
}
[HttpGet("CancellationsForRefund")]
public async Task<APIResponse<CancellationForRefundResponseDTO>> GetCancellationsForRefund()
{
_logger.LogInformation("Request Received for GetCancellationsForRefund");
try
{
var result = await _cancellationRepository.GetCancellationsForRefundAsync();
if (result == null || !result.CancellationsToRefund.Any())
return new APIResponse<CancellationForRefundResponseDTO>(HttpStatusCode.NotFound, "No cancellations found that need refunding.");
return new APIResponse<CancellationForRefundResponseDTO>(result, "All cancellations to be Refund retrieved successfully.");
}
catch (Exception ex)
{
_logger.LogError(ex, "Failed to retrieve all cancellations to be refunded");
return new APIResponse<CancellationForRefundResponseDTO>(HttpStatusCode.InternalServerError, "Failed to retrieve all cancellations to be refunded", ex.Message);
}
}
[HttpPost("ProcessRefund")]
public async Task<APIResponse<ProcessRefundResponseDTO>> ProcessRefund([FromBody] ProcessRefundRequestDTO model)
{
_logger.LogInformation("Request Received for ProcessRefund: {@ProcessRefundRequestDTO}", model);
if (!ModelState.IsValid)
{
_logger.LogInformation("Invalid Data in the Request Body for ProcessRefund");
return new APIResponse<ProcessRefundResponseDTO>(HttpStatusCode.BadRequest, "Invalid Data in the Request Body");
}
try
{
var result = await _cancellationRepository.ProcessRefundAsync(model);
if (result.Status)
{
return new APIResponse<ProcessRefundResponseDTO>(result, "Refund processed successfully.");
}
return new APIResponse<ProcessRefundResponseDTO>(HttpStatusCode.BadRequest, "Failed to process refund", result.Message);
}
catch (Exception ex)
{
_logger.LogError(ex, "Failed to process refund");
return new APIResponse<ProcessRefundResponseDTO>(HttpStatusCode.InternalServerError, "Failed to process refund", ex.Message);
}
}
[HttpPost("UpdateRefundStatus")]
public async Task<APIResponse<UpdateRefundStatusResponseDTO>> UpdateRefundStatus([FromBody] UpdateRefundStatusRequestDTO model)
{
_logger.LogInformation("Request Received for UpdateRefundStatus: {@UpdateRefundStatusRequestDTO}", model);
if (!ModelState.IsValid)
{
_logger.LogInformation("Invalid Data in the Request Body for UpdateRefundStatus");
return new APIResponse<UpdateRefundStatusResponseDTO>(HttpStatusCode.BadRequest, "Invalid Data in the Request Body");
}
try
{
var result = await _cancellationRepository.UpdateRefundStatusAsync(model);
if (result.Status)
{
return new APIResponse<UpdateRefundStatusResponseDTO>(result, "Refund status updated successfully.");
}
return new APIResponse<UpdateRefundStatusResponseDTO>(HttpStatusCode.BadRequest, "Failed to update refund status", result.Message);
}
catch (Exception ex)
{
_logger.LogError(ex, "Failed to update refund status");
return new APIResponse<UpdateRefundStatusResponseDTO>(HttpStatusCode.InternalServerError, "Failed to update refund status", ex.Message);
}
}
}
}
Explanation of Controller Action Methods:
Let’s understand the objective of each action method of the CancellationController class, which is responsible for handling various aspects of the hotel cancellation module.
- GetCancellationPolicies: This method retrieves a list of all active cancellation policies. It is important to inform users about how cancellations are handled, including any fees or conditions associated with canceling a booking.
- CalculateCancellationCharges: This method calculates the cancellation charges based on the provided cancellation request details. It is used when a customer wishes to understand the financial implications of cancelling their booking before actually making a cancellation request.
- CreateCancellationRequest: This method creates a new cancellation request. It is the key method for customers who decide to proceed with cancelling their booking. It captures all necessary details about the cancellation and initiates any processes required to manage the cancellation within the system.
- GetAllCancellations: This method retrieves a list of all cancellations based on specified criteria, such as date range and status. It supports administrative tasks, allowing hotel staff or management to oversee all past and current cancellations.
- ReviewCancellationRequest: Allows an administrator or authorized user to review a cancellation request and either approve or reject it based on the hotel’s policies and the circumstances of the cancellation.
- GetCancellationsForRefund: This function fetches all cancellations eligible for a refund but has not yet been processed. This action is important for ensuring that customers receive timely refunds following an approved cancellation, particularly focusing on those that might have been missed or are pending due to processing issues.
- ProcessRefund: Processes a refund for a cancellation that has been approved.
- UpdateRefundStatus: This function updates the status of a previously processed refund. It can be necessary in cases where the refund process encounters issues, such as failed transactions or adjustments to the refund amount due to changing circumstances or additional information.
In this article, I explain the Hotel Cancellation Module of our Hotel Booking Application. I hope you enjoy this article. Please take a few minutes to give your valuable feedback about this article. Your feedback means a lot to me and motivates me to give better examples and explanations.
With this, we have successfully implemented the Hotel Booking Application. Yes, there are many flaws in this Application, which I am rectifying and will provide an updated version soon. But overall, if you go through all the modules, then you will definitely understand the overall functionalities of the Hotel Booking Application.
About the Author: Pranaya Rout
Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.