Example: Queue Management System

Overview

This example demonstrates the sophisticated queue management system implemented in the AR Payment Reversal dashboard. The queue serves as a staging area for payment reversals, allowing users to review, modify, and batch process reversals before committing them to SYSPRO. This pattern provides safety, flexibility, and efficiency in handling financial transactions.

Business Value

The queue management system addresses several critical business requirements:

• Review and Approval: Payments can be reviewed before processing
• Batch Processing: Multiple reversals can be processed together
• Error Recovery: Failed items can be retried without re-entry
• Audit Trail: Complete history of queued items
• Workflow Flexibility: Items can be added/removed before final processing

Implementation Architecture

Database Schema

The queue is backed by a custom SQL Server table with comprehensive tracking fields:

-- MepApps.Dash.Ar.Maint.PaymentReversal/Resources/SQL/CREATE TABLE CG_ArReversePaymentQueueHeader.sql
CREATE TABLE [dbo].[CG_ArReversePaymentQueueHeader](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Customer] [varchar](20) NOT NULL,
    [CheckNumber] [varchar](20) NOT NULL,
    [CheckValue] [decimal](18, 2) NULL,
    [PaymentDate] [datetime] NULL,
    [Bank] [varchar](10) NULL,
    [TrnYear] [decimal](4, 0) NULL,
    [TrnMonth] [decimal](2, 0) NULL,
    [Journal] [decimal](10, 0) NULL,
    
    -- Queue management fields
    [QueueStatus] [varchar](50) NULL DEFAULT 'Pending',
    [ProcessAttempts] [int] NOT NULL DEFAULT 0,
    [LastError] [varchar](500) NULL,
    [LockedBy] [varchar](20) NULL,
    [LockedDate] [datetime] NULL,
    
    -- Audit fields
    [CreatedDate] [datetime] NOT NULL DEFAULT GETDATE(),
    [CreatedBy] [varchar](20) NULL DEFAULT SYSTEM_USER,
    [ModifiedDate] [datetime] NULL,
    [ModifiedBy] [varchar](20) NULL,
    
 CONSTRAINT [PK_CG_ArReversePaymentQueueHeader] PRIMARY KEY CLUSTERED ([Id] ASC),
 CONSTRAINT [UQ_Payment] UNIQUE NONCLUSTERED 
    ([Customer], [CheckNumber], [TrnYear], [TrnMonth], [Journal])
)

-- Performance indexes
CREATE NONCLUSTERED INDEX [IX_Queue_Status] 
ON [dbo].[CG_ArReversePaymentQueueHeader] ([QueueStatus])
INCLUDE ([Customer], [CheckNumber], [CheckValue])

CREATE NONCLUSTERED INDEX [IX_Queue_Created] 
ON [dbo].[CG_ArReversePaymentQueueHeader] ([CreatedDate] DESC)

Queue Service Implementation

The service layer provides comprehensive queue management operations:

// MepApps.Dash.Ar.Maint.PaymentReversal/Services/QueueManagementService.cs
public class QueueManagementService : IQueueManagementService
{
    private readonly ILogger<QueueManagementService> _logger;
    private readonly IServiceProvider _serviceProvider;
    
    public async Task<QueuedPayments> GetQueuedPaymentsAsync(QueueFilter filter = null)
    {
        using (var context = _serviceProvider.GetService<PluginSysproDataContext>())
        {
            var query = context.CG_ArReversePaymentQueueHeaders.AsQueryable();
            
            // Apply filters
            if (filter != null)
            {
                if (!string.IsNullOrEmpty(filter.Status))
                {
                    query = query.Where(q => q.QueueStatus == filter.Status);
                }
                
                if (!string.IsNullOrEmpty(filter.Customer))
                {
                    query = query.Where(q => q.Customer.Contains(filter.Customer));
                }
                
                if (filter.DateFrom.HasValue)
                {
                    query = query.Where(q => q.CreatedDate >= filter.DateFrom.Value);
                }
                
                if (filter.DateTo.HasValue)
                {
                    query = query.Where(q => q.CreatedDate <= filter.DateTo.Value);
                }
            }
            
            // Get queue items with related data
            var queueItems = await query
                .OrderByDescending(q => q.CreatedDate)
                .Select(q => new QueuedPaymentViewModel
                {
                    Id = q.Id,
                    Customer = q.Customer,
                    CheckNumber = q.CheckNumber,
                    CheckValue = q.CheckValue,
                    PaymentDate = q.PaymentDate,
                    Bank = q.Bank,
                    Status = q.QueueStatus,
                    ProcessAttempts = q.ProcessAttempts,
                    LastError = q.LastError,
                    CreatedDate = q.CreatedDate,
                    CreatedBy = q.CreatedBy,
                    IsLocked = q.LockedBy != null,
                    LockedBy = q.LockedBy
                })
                .ToListAsync();
            
            // Enrich with customer names
            await EnrichWithCustomerData(queueItems);
            
            // Calculate summary statistics
            var summary = new QueueSummary
            {
                TotalItems = queueItems.Count,
                TotalValue = queueItems.Sum(q => q.CheckValue ?? 0),
                PendingCount = queueItems.Count(q => q.Status == "Pending"),
                ErrorCount = queueItems.Count(q => q.Status == "Error"),
                OldestItem = queueItems.Min(q => q.CreatedDate),
                StatusBreakdown = queueItems.GroupBy(q => q.Status)
                    .ToDictionary(g => g.Key, g => g.Count())
            };
            
            return new QueuedPayments
            {
                Items = queueItems,
                Summary = summary
            };
        }
    }
    
    public async Task<QueueOperationResult> AddToQueueAsync(
        IEnumerable<PaymentToQueue> payments)
    {
        var result = new QueueOperationResult();
        var addedIds = new List<int>();
        
        using (var context = _serviceProvider.GetService<PluginSysproDataContext>())
        using (var transaction = context.Database.BeginTransaction())
        {
            try
            {
                foreach (var payment in payments)
                {
                    // Check for duplicates
                    var exists = await context.CG_ArReversePaymentQueueHeaders
                        .AnyAsync(q => q.Customer == payment.Customer 
                                    && q.CheckNumber == payment.CheckNumber
                                    && q.TrnYear == payment.TrnYear
                                    && q.TrnMonth == payment.TrnMonth
                                    && q.Journal == payment.Journal);
                    
                    if (exists)
                    {
                        _logger.LogWarning("Payment already queued: {Customer}-{Check}", 
                            payment.Customer, payment.CheckNumber);
                        result.Skipped.Add($"{payment.Customer}-{payment.CheckNumber}");
                        continue;
                    }
                    
                    // Validate payment exists in SYSPRO
                    var sysproPayment = await ValidatePaymentInSyspro(payment);
                    if (!sysproPayment.IsValid)
                    {
                        result.Failed.Add(new FailedItem
                        {
                            Item = $"{payment.Customer}-{payment.CheckNumber}",
                            Reason = sysproPayment.ErrorMessage
                        });
                        continue;
                    }
                    
                    // Add to queue
                    var queueEntry = new CG_ArReversePaymentQueueHeader
                    {
                        Customer = payment.Customer,
                        CheckNumber = payment.CheckNumber,
                        CheckValue = payment.CheckValue,
                        PaymentDate = payment.PaymentDate,
                        Bank = payment.Bank,
                        TrnYear = payment.TrnYear,
                        TrnMonth = payment.TrnMonth,
                        Journal = payment.Journal,
                        QueueStatus = "Pending",
                        CreatedDate = DateTime.Now,
                        CreatedBy = GetCurrentUser()
                    };
                    
                    context.CG_ArReversePaymentQueueHeaders.Add(queueEntry);
                    await context.SaveChangesAsync();
                    
                    addedIds.Add(queueEntry.Id);
                    result.Succeeded++;
                }
                
                transaction.Commit();
                result.Success = true;
                result.AddedIds = addedIds;
                
                _logger.LogInformation("Queue operation complete. Added: {Added}, Skipped: {Skipped}, Failed: {Failed}",
                    result.Succeeded, result.Skipped.Count, result.Failed.Count);
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                _logger.LogError(ex, "Error adding payments to queue");
                result.Success = false;
                result.ErrorMessage = "Failed to add payments to queue";
                throw;
            }
        }
        
        return result;
    }
}

Queue State Management

Managing queue item states and preventing concurrent modifications:

public class QueueStateManager
{
    private readonly object _lockObject = new object();
    private readonly Dictionary<int, QueueItemLock> _locks = new();
    
    public async Task<bool> LockItemAsync(int queueId, string userId)
    {
        lock (_lockObject)
        {
            if (_locks.ContainsKey(queueId))
            {
                var existingLock = _locks[queueId];
                if (existingLock.UserId != userId && !existingLock.IsExpired)
                {
                    return false; // Already locked by another user
                }
            }
            
            _locks[queueId] = new QueueItemLock
            {
                QueueId = queueId,
                UserId = userId,
                LockTime = DateTime.Now,
                ExpiresAt = DateTime.Now.AddMinutes(5)
            };
        }
        
        // Update database
        using (var context = _serviceProvider.GetService<PluginSysproDataContext>())
        {
            var item = await context.CG_ArReversePaymentQueueHeaders
                .FirstOrDefaultAsync(q => q.Id == queueId);
            
            if (item != null)
            {
                item.LockedBy = userId;
                item.LockedDate = DateTime.Now;
                await context.SaveChangesAsync();
                
                _logger.LogDebug("Queue item {Id} locked by {User}", queueId, userId);
                return true;
            }
        }
        
        return false;
    }
    
    public async Task ReleaseLocksAsync(string userId)
    {
        List<int> itemsToRelease;
        
        lock (_lockObject)
        {
            itemsToRelease = _locks
                .Where(kvp => kvp.Value.UserId == userId)
                .Select(kvp => kvp.Key)
                .ToList();
            
            foreach (var id in itemsToRelease)
            {
                _locks.Remove(id);
            }
        }
        
        // Update database
        if (itemsToRelease.Any())
        {
            using (var context = _serviceProvider.GetService<PluginSysproDataContext>())
            {
                await context.Database.ExecuteSqlCommandAsync(
                    "UPDATE CG_ArReversePaymentQueueHeader SET LockedBy = NULL, LockedDate = NULL WHERE Id IN ({0})",
                    string.Join(",", itemsToRelease));
                
                _logger.LogDebug("Released {Count} locks for user {User}", 
                    itemsToRelease.Count, userId);
            }
        }
    }
    
    public async Task<QueueStatusTransition> ChangeStatusAsync(
        int queueId, 
        string newStatus, 
        string reason = null)
    {
        using (var context = _serviceProvider.GetService<PluginSysproDataContext>())
        {
            var item = await context.CG_ArReversePaymentQueueHeaders
                .FirstOrDefaultAsync(q => q.Id == queueId);
            
            if (item == null)
            {
                throw new QueueItemNotFoundException($"Queue item {queueId} not found");
            }
            
            var oldStatus = item.QueueStatus;
            
            // Validate status transition
            if (!IsValidTransition(oldStatus, newStatus))
            {
                throw new InvalidStatusTransitionException(
                    $"Cannot transition from {oldStatus} to {newStatus}");
            }
            
            // Update status
            item.QueueStatus = newStatus;
            item.ModifiedDate = DateTime.Now;
            item.ModifiedBy = GetCurrentUser();
            
            if (newStatus == "Error")
            {
                item.ProcessAttempts++;
                item.LastError = reason;
            }
            
            await context.SaveChangesAsync();
            
            // Log transition
            await LogStatusTransition(queueId, oldStatus, newStatus, reason);
            
            return new QueueStatusTransition
            {
                QueueId = queueId,
                OldStatus = oldStatus,
                NewStatus = newStatus,
                TransitionTime = DateTime.Now,
                Reason = reason
            };
        }
    }
    
    private bool IsValidTransition(string fromStatus, string toStatus)
    {
        var validTransitions = new Dictionary<string, List<string>>
        {
            ["Pending"] = new List<string> { "Processing", "Cancelled", "Error" },
            ["Processing"] = new List<string> { "Completed", "Error", "Pending" },
            ["Error"] = new List<string> { "Pending", "Cancelled" },
            ["Completed"] = new List<string>(), // Terminal state
            ["Cancelled"] = new List<string> { "Pending" } // Can be reactivated
        };
        
        return validTransitions.ContainsKey(fromStatus) && 
               validTransitions[fromStatus].Contains(toStatus);
    }
}

Queue UI Implementation

The UI provides rich interaction with the queue:

<!-- MepApps.Dash.Ar.Maint.PaymentReversal/Views/QueueManagementView.xaml -->
<UserControl x:Class="MepApps.Dash.Ar.Maint.PaymentReversal.Views.QueueManagementView">
    <Grid>
        <Grid.RowDefinitions>
            <RowDefinition Height="Auto"/> <!-- Toolbar -->
            <RowDefinition Height="Auto"/> <!-- Filters -->
            <RowDefinition Height="*"/>    <!-- Queue Grid -->
            <RowDefinition Height="Auto"/> <!-- Summary -->
        </Grid.RowDefinitions>
        
        <!-- Toolbar -->
        <ToolBar Grid.Row="0">
            <Button Command="{Binding RefreshQueueCommand}">
                <StackPanel Orientation="Horizontal">
                    <Image Source="/Images/refresh.png" Width="16" Height="16"/>
                    <TextBlock Text="Refresh" Margin="5,0,0,0"/>
                </StackPanel>
            </Button>
            
            <Separator/>
            
            <Button Command="{Binding ProcessSelectedCommand}"
                    IsEnabled="{Binding HasSelectedItems}">
                <StackPanel Orientation="Horizontal">
                    <Image Source="/Images/process.png" Width="16" Height="16"/>
                    <TextBlock Text="Process Selected" Margin="5,0,0,0"/>
                </StackPanel>
            </Button>
            
            <Button Command="{Binding RemoveSelectedCommand}"
                    IsEnabled="{Binding HasSelectedItems}">
                <StackPanel Orientation="Horizontal">
                    <Image Source="/Images/remove.png" Width="16" Height="16"/>
                    <TextBlock Text="Remove Selected" Margin="5,0,0,0"/>
                </StackPanel>
            </Button>
            
            <Separator/>
            
            <Button Command="{Binding RetryErrorsCommand}"
                    IsEnabled="{Binding HasErrors}">
                <StackPanel Orientation="Horizontal">
                    <Image Source="/Images/retry.png" Width="16" Height="16"/>
                    <TextBlock Text="Retry Errors" Margin="5,0,0,0"/>
                </StackPanel>
            </Button>
        </ToolBar>
        
        <!-- Filters -->
        <Expander Grid.Row="1" Header="Filters" IsExpanded="False">
            <Grid Margin="5">
                <Grid.ColumnDefinitions>
                    <ColumnDefinition Width="Auto"/>
                    <ColumnDefinition Width="150"/>
                    <ColumnDefinition Width="Auto"/>
                    <ColumnDefinition Width="150"/>
                    <ColumnDefinition Width="Auto"/>
                    <ColumnDefinition Width="150"/>
                    <ColumnDefinition Width="*"/>
                    <ColumnDefinition Width="Auto"/>
                </Grid.ColumnDefinitions>
                
                <Label Grid.Column="0" Content="Status:"/>
                <ComboBox Grid.Column="1" 
                         ItemsSource="{Binding StatusOptions}"
                         SelectedItem="{Binding SelectedStatus}"/>
                
                <Label Grid.Column="2" Content="Customer:"/>
                <TextBox Grid.Column="3" 
                        Text="{Binding CustomerFilter, UpdateSourceTrigger=PropertyChanged}"/>
                
                <Label Grid.Column="4" Content="Date Range:"/>
                <DatePicker Grid.Column="5" 
                           SelectedDate="{Binding DateFilter}"/>
                
                <Button Grid.Column="7" 
                       Content="Apply Filters"
                       Command="{Binding ApplyFiltersCommand}"/>
            </Grid>
        </Expander>
        
        <!-- Queue Grid -->
        <DataGrid Grid.Row="2" 
                 ItemsSource="{Binding QueueItems}"
                 SelectedItem="{Binding SelectedItem}"
                 SelectionMode="Extended"
                 AutoGenerateColumns="False"
                 CanUserAddRows="False"
                 CanUserDeleteRows="False">
            
            <DataGrid.Columns>
                <DataGridCheckBoxColumn Header="Select" 
                                       Binding="{Binding IsSelected}"
                                       Width="50"/>
                
                <DataGridTextColumn Header="Status" 
                                   Binding="{Binding Status}"
                                   Width="80">
                    <DataGridTextColumn.CellStyle>
                        <Style TargetType="DataGridCell">
                            <Style.Triggers>
                                <DataTrigger Binding="{Binding Status}" Value="Error">
                                    <Setter Property="Background" Value="LightPink"/>
                                </DataTrigger>
                                <DataTrigger Binding="{Binding Status}" Value="Processing">
                                    <Setter Property="Background" Value="LightBlue"/>
                                </DataTrigger>
                                <DataTrigger Binding="{Binding Status}" Value="Completed">
                                    <Setter Property="Background" Value="LightGreen"/>
                                </DataTrigger>
                            </Style.Triggers>
                        </Style>
                    </DataGridTextColumn.CellStyle>
                </DataGridTextColumn>
                
                <DataGridTextColumn Header="Customer" 
                                   Binding="{Binding Customer}"
                                   Width="100"/>
                
                <DataGridTextColumn Header="Check #" 
                                   Binding="{Binding CheckNumber}"
                                   Width="100"/>
                
                <DataGridTextColumn Header="Amount" 
                                   Binding="{Binding CheckValue, StringFormat=C}"
                                   Width="100"/>
                
                <DataGridTextColumn Header="Date" 
                                   Binding="{Binding PaymentDate, StringFormat=d}"
                                   Width="100"/>
                
                <DataGridTextColumn Header="Attempts" 
                                   Binding="{Binding ProcessAttempts}"
                                   Width="70"/>
                
                <DataGridTextColumn Header="Error" 
                                   Binding="{Binding LastError}"
                                   Width="*"/>
                
                <DataGridTemplateColumn Header="Actions" Width="100">
                    <DataGridTemplateColumn.CellTemplate>
                        <DataTemplate>
                            <StackPanel Orientation="Horizontal">
                                <Button Content="View"
                                       Command="{Binding DataContext.ViewDetailsCommand, 
                                                RelativeSource={RelativeSource AncestorType=UserControl}}"
                                       CommandParameter="{Binding}"
                                       Margin="2"/>
                                
                                <Button Content="Retry"
                                       Command="{Binding DataContext.RetryItemCommand,
                                                RelativeSource={RelativeSource AncestorType=UserControl}}"
                                       CommandParameter="{Binding}"
                                       Visibility="{Binding IsError, 
                                                   Converter={StaticResource BoolToVisibilityConverter}}"
                                       Margin="2"/>
                            </StackPanel>
                        </DataTemplate>
                    </DataGridTemplateColumn.CellTemplate>
                </DataGridTemplateColumn>
            </DataGrid.Columns>
            
            <DataGrid.ContextMenu>
                <ContextMenu>
                    <MenuItem Header="Process" 
                             Command="{Binding ProcessSelectedCommand}"/>
                    <MenuItem Header="Remove" 
                             Command="{Binding RemoveSelectedCommand}"/>
                    <Separator/>
                    <MenuItem Header="Export to Excel" 
                             Command="{Binding ExportCommand}"/>
                </ContextMenu>
            </DataGrid.ContextMenu>
        </DataGrid>
        
        <!-- Summary Bar -->
        <StatusBar Grid.Row="3">
            <StatusBarItem>
                <TextBlock Text="{Binding QueueSummary.TotalItems, 
                                 StringFormat='Total Items: {0}'}"/>
            </StatusBarItem>
            <Separator/>
            <StatusBarItem>
                <TextBlock Text="{Binding QueueSummary.TotalValue, 
                                 StringFormat='Total Value: {0:C}'}"/>
            </StatusBarItem>
            <Separator/>
            <StatusBarItem>
                <TextBlock Text="{Binding QueueSummary.PendingCount, 
                                 StringFormat='Pending: {0}'}"/>
            </StatusBarItem>
            <Separator/>
            <StatusBarItem>
                <TextBlock Text="{Binding QueueSummary.ErrorCount, 
                                 StringFormat='Errors: {0}'}"
                          Foreground="{Binding QueueSummary.ErrorCount, 
                                      Converter={StaticResource ErrorCountToColorConverter}}"/>
            </StatusBarItem>
        </StatusBar>
    </Grid>
</UserControl>

Queue Processing Logic

Processing queued items with error handling and retry logic:

public class QueueProcessor
{
    private readonly ILogger<QueueProcessor> _logger;
    private readonly IArReversePaymentService _reversalService;
    private readonly IQueueManagementService _queueService;
    
    public async Task<ProcessingResult> ProcessQueueAsync(
        IEnumerable<int> queueIds,
        ProcessingOptions options = null)
    {
        options ??= ProcessingOptions.Default;
        var result = new ProcessingResult();
        
        // Lock items for processing
        var lockedItems = await LockItemsForProcessing(queueIds);
        
        try
        {
            // Group by customer for efficiency
            var groupedItems = lockedItems.GroupBy(i => i.Customer);
            
            foreach (var customerGroup in groupedItems)
            {
                if (options.StopOnError && result.FailedCount > 0)
                {
                    break;
                }
                
                try
                {
                    // Process customer's payments
                    var customerResult = await ProcessCustomerPayments(
                        customerGroup.Key,
                        customerGroup.ToList(),
                        options);
                    
                    result.Merge(customerResult);
                }
                catch (Exception ex)
                {
                    _logger.LogError(ex, "Error processing customer {Customer}", 
                        customerGroup.Key);
                    
                    // Mark items as error
                    foreach (var item in customerGroup)
                    {
                        await _queueService.ChangeStatusAsync(
                            item.Id, 
                            "Error", 
                            ex.Message);
                        result.FailedCount++;
                    }
                }
            }
        }
        finally
        {
            // Release locks
            await ReleaseProcessingLocks(lockedItems);
        }
        
        return result;
    }
}

Benefits and Outcomes

1. Reduced Errors: Queue validation catches issues before SYSPRO posting
2. Improved Efficiency: Batch processing reduces system load
3. Better Control: Users can review and modify before committing
4. Enhanced Recovery: Failed items can be corrected and retried
5. Complete Audit Trail: All queue operations are logged

Related Documentation

• Payment Reversal Workflow
• Data Services
• UI Architecture

Summary

The queue management system provides a robust, user-friendly mechanism for staging and processing payment reversals. Through careful state management, comprehensive error handling, and intuitive UI design, the system ensures reliable processing while giving users full control over the reversal workflow. This pattern can be adapted for other batch processing scenarios requiring review and approval workflows.