QualifyingMembers Stored Procedure
Wed Aug 14 2024 14:17:21 GMT+0000 (Coordinated Universal Time)
Saved by @iamkatmakhafola
//Stored Procedure 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 DISTINCT 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(b.Booking_ID) >= 10; END ELSE IF @RewardCriteria = 'Made 20 Bookings in Last 3 Months' BEGIN SELECT DISTINCT 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(b.Booking_ID) >= 20; END -- Add more conditions based on other reward criteria as needed ELSE IF @RewardCriteria = 'Placed First Order' BEGIN SELECT DISTINCT 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(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 Payments p ON p.Member_ID = m.Member_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_Amount >= 1000; -- Example threshold, adjust as needed END ELSE IF @RewardCriteria = 'Large Quantity Order' BEGIN SELECT DISTINCT m.Member_ID FROM Members m INNER JOIN Order_Items oi ON oi.Order_ID = ( SELECT TOP 1 o.Order_ID FROM Orders o WHERE o.Member_ID = m.Member_ID ORDER BY o.Order_Date DESC ) WHERE oi.Quantity >= 10; -- Example threshold, adjust as needed END ELSE BEGIN -- Return an empty set if criteria do not match SELECT * FROM Members WHERE 1 = 0; END END //Service public class QualifyingMembersService : IHostedService, IDisposable { private readonly IServiceProvider _serviceProvider; private Timer _timer; public QualifyingMembersService(IServiceProvider serviceProvider) { _serviceProvider = serviceProvider; } public Task StartAsync(CancellationToken cancellationToken) { _timer = new Timer(UpdateQualifyingMembers, null, TimeSpan.Zero, TimeSpan.FromMinutes(1)); // Check every 1 minute return Task.CompletedTask; } private async void UpdateQualifyingMembers(object state) { using (var scope = _serviceProvider.CreateScope()) { var context = scope.ServiceProvider.GetRequiredService<AppDbContext>(); var postedRewards = await context.Rewards .Where(r => r.IsPosted) .ToListAsync(); foreach (var reward in postedRewards) { var rewardType = await context.Reward_Types .FindAsync(reward.Reward_Type_ID); if (rewardType != null) { var qualifyingMembers = await GetQualifyingMembersAsync(rewardType.Reward_Criteria); foreach (var member in qualifyingMembers) { var existingRewardMember = await context.Reward_Members .FirstOrDefaultAsync(rm => rm.Member_ID == member.Member_ID && rm.Reward_ID == reward.Reward_ID); if (existingRewardMember == null) { var rewardMember = new Reward_Member { Member_ID = member.Member_ID, Reward_ID = reward.Reward_ID, IsRedeemed = false }; context.Reward_Members.Add(rewardMember); } } } } await context.SaveChangesAsync(); } } public async Task<List<Member>> GetQualifyingMembersAsync(string criteria) { var criteriaParam = new SqlParameter("@Criteria", criteria); return await _serviceProvider.GetRequiredService<AppDbContext>() .Members .FromSqlRaw("EXEC GetQualifyingMembers @Criteria", criteriaParam) .ToListAsync(); } public Task StopAsync(CancellationToken cancellationToken) { _timer?.Change(Timeout.Infinite, 0); return Task.CompletedTask; } public void Dispose() { _timer?.Dispose(); } } //UpdateQualifyingMembersForReward private async Task UpdateQualifyingMembersForReward(int rewardId) { // Fetch reward and reward type var reward = await _appDbContext.Rewards.FindAsync(rewardId); if (reward != null) { var rewardType = await _appDbContext.Reward_Types.FindAsync(reward.Reward_Type_ID); if (rewardType != null) { // Use QualifyingMembersService to get qualifying members var qualifyingMembers = await _qualifyingMembersService.GetQualifyingMembersAsync(rewardType.Reward_Criteria); foreach (var member in qualifyingMembers) { var existingRewardMember = await _appDbContext.Reward_Members .FirstOrDefaultAsync(rm => rm.Member_ID == member.Member_ID && rm.Reward_ID == reward.Reward_ID); // Add qualifying members to the Reward_Member table if (existingRewardMember == null) { var rewardMember = new Reward_Member { Member_ID = member.Member_ID, Reward_ID = reward.Reward_ID, IsRedeemed = false }; _appDbContext.Reward_Members.Add(rewardMember); } } await _appDbContext.SaveChangesAsync(); } } }
Comments