// Stored Procedures
builder.HasAnnotation("SqlServer:IncludeProcedures", new
{
UpdateUserDeletionSettings = @"
CREATE PROCEDURE UpdateUserDeletionSettings
@DeletionTimeValue INT,
@DeletionTimeUnit NVARCHAR(20)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @DeletionThreshold DATETIME;
SET @DeletionThreshold = CASE
WHEN @DeletionTimeUnit = 'Minutes' THEN DATEADD(MINUTE, -@DeletionTimeValue, GETUTCDATE())
WHEN @DeletionTimeUnit = 'Hours' THEN DATEADD(HOUR, -@DeletionTimeValue, GETUTCDATE())
WHEN @DeletionTimeUnit = 'Days' THEN DATEADD(DAY, -@DeletionTimeValue, GETUTCDATE())
WHEN @DeletionTimeUnit = 'Weeks' THEN DATEADD(WEEK, -@DeletionTimeValue, GETUTCDATE())
WHEN @DeletionTimeUnit = 'Months' THEN DATEADD(MONTH, -@DeletionTimeValue, GETUTCDATE())
WHEN @DeletionTimeUnit = 'Years' THEN DATEADD(YEAR, -@DeletionTimeValue, GETUTCDATE())
ELSE GETUTCDATE()
END;
DELETE FROM dbo.AspNetUsers
WHERE User_Status_ID = 2 AND DeactivatedAt < @DeletionThreshold;
END",
UpdateOrderStatuses = @"
CREATE PROCEDURE UpdateOrderStatuses
@OverdueTimeValue INT,
@OverdueTimeUnit NVARCHAR(20)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @OverdueThreshold DATETIME;
SET @OverdueThreshold = CASE
WHEN @OverdueTimeUnit = 'Minutes' THEN DATEADD(MINUTE, -@OverdueTimeValue, GETUTCDATE())
WHEN @OverdueTimeUnit = 'Hours' THEN DATEADD(HOUR, -@OverdueTimeValue, GETUTCDATE())
WHEN @OverdueTimeUnit = 'Days' THEN DATEADD(DAY, -@OverdueTimeValue, GETUTCDATE())
WHEN @OverdueTimeUnit = 'Weeks' THEN DATEADD(WEEK, -@OverdueTimeValue, GETUTCDATE())
WHEN @OverdueTimeUnit = 'Months' THEN DATEADD(MONTH, -@OverdueTimeValue, GETUTCDATE())
WHEN @OverdueTimeUnit = 'Years' THEN DATEADD(YEAR, -@OverdueTimeValue, GETUTCDATE())
ELSE GETUTCDATE()
END;
UPDATE dbo.Orders
SET Order_Status_ID = CASE
WHEN Order_Date <= @OverdueThreshold THEN 2
ELSE Order_Status_ID
END
WHERE IsCollected = 0;
END",
GetQualifyingMembers = @"
CREATE PROCEDURE GetQualifyingMembers
@RewardCriteria NVARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
IF @RewardCriteria = 'Placed a Booking'
BEGIN
SELECT DISTINCT m.Member_ID
FROM Members m
INNER JOIN Bookings b ON b.Member_ID = m.Member_ID;
END
ELSE IF @RewardCriteria = 'Completed 10 Bookings in a Month'
BEGIN
SELECT m.Member_ID
FROM Members m
INNER JOIN Bookings b ON b.Member_ID = m.Member_ID
INNER JOIN Booking_Time_Slots bts ON b.Booking_ID = bts.Booking_ID
INNER JOIN Time_Slots ts ON bts.Time_Slot_ID = ts.Time_Slot_ID
WHERE ts.Slot_Date >= DATEADD(MONTH, -1, GETDATE())
GROUP BY m.Member_ID
HAVING COUNT(DISTINCT b.Booking_ID) >= 10;
END
ELSE IF @RewardCriteria = 'Made 20 Bookings in Last 3 Months'
BEGIN
SELECT m.Member_ID
FROM Members m
INNER JOIN Bookings b ON b.Member_ID = m.Member_ID
INNER JOIN Booking_Time_Slots bts ON b.Booking_ID = bts.Booking_ID
INNER JOIN Time_Slots ts ON bts.Time_Slot_ID = ts.Time_Slot_ID
WHERE ts.Slot_Date >= DATEADD(MONTH, -3, GETDATE())
GROUP BY m.Member_ID
HAVING COUNT(DISTINCT b.Booking_ID) >= 20;
END
ELSE IF @RewardCriteria = 'Placed First Order'
BEGIN
SELECT m.Member_ID
FROM Members m
WHERE EXISTS (
SELECT 1
FROM Orders o
WHERE o.Member_ID = m.Member_ID
AND o.Order_Date = (
SELECT MIN(o2.Order_Date)
FROM Orders o2
WHERE o2.Member_ID = m.Member_ID
)
);
END
ELSE IF @RewardCriteria = 'Made a Payment'
BEGIN
SELECT DISTINCT m.Member_ID
FROM Members m
INNER JOIN Orders o ON o.Member_ID = m.Member_ID
INNER JOIN Payments p ON p.Order_ID = o.Order_ID;
END
ELSE IF @RewardCriteria = 'High-Value Order'
BEGIN
SELECT DISTINCT m.Member_ID
FROM Members m
INNER JOIN Orders o ON o.Member_ID = m.Member_ID
WHERE o.Total_Price >= 1000;
END
ELSE IF @RewardCriteria = 'Large Quantity Order'
BEGIN
SELECT m.Member_ID
FROM Members m
WHERE (
SELECT SUM(ol.Quantity)
FROM Order_Lines ol
INNER JOIN Orders o ON ol.Order_ID = o.Order_ID
WHERE o.Member_ID = m.Member_ID
) >= 10;
END
ELSE
BEGIN
SELECT * FROM Members WHERE 1 = 0;
END
END"
});
