Skip to main content

Example 05: Order Creation Audit Trail

Overview​

The dashboard implements comprehensive audit logging for all order creation activities, providing traceability, compliance support, and troubleshooting capabilities.

Audit Implementation​

Audit Table Structure​

CREATE TABLE CG_OrderAuditLog (
    AuditId INT IDENTITY PRIMARY KEY,
    OrderNumber VARCHAR(20),
    Action VARCHAR(50),
    UserName VARCHAR(50),
    ActionDate DATETIME DEFAULT GETDATE(),
    OldValue NVARCHAR(MAX),
    NewValue NVARCHAR(MAX),
    IPAddress VARCHAR(50),
    SessionId VARCHAR(50),
    AdditionalInfo NVARCHAR(MAX)
)

CREATE INDEX IX_OrderAudit_OrderNumber ON CG_OrderAuditLog(OrderNumber)
CREATE INDEX IX_OrderAudit_ActionDate ON CG_OrderAuditLog(ActionDate)

Audit Service​

public class AuditService
{
    public async Task LogOrderCreation(SummaryOrder order, string result)
    {
        var audit = new OrderAuditLog
        {
            OrderNumber = order.OrderNumber,
            Action = "ORDER_CREATED",
            UserName = GetCurrentUser(),
            ActionDate = DateTime.Now,
            NewValue = JsonConvert.SerializeObject(order),
            IPAddress = GetClientIP(),
            SessionId = GetSessionId(),
            AdditionalInfo = JsonConvert.SerializeObject(new
            {
                Result = result,
                Supplier = order.Supplier,
                TotalLines = order.OrderLines.Count,
                TotalValue = order.OrderLines.Sum(l => l.OrderQty * l.Price)
            })
        };
        
        await _context.OrderAuditLogs.AddAsync(audit);
        await _context.SaveChangesAsync();
    }
    
    public async Task LogOrderModification(
        string orderNumber, 
        object oldValue, 
        object newValue)
    {
        var audit = new OrderAuditLog
        {
            OrderNumber = orderNumber,
            Action = "ORDER_MODIFIED",
            UserName = GetCurrentUser(),
            OldValue = JsonConvert.SerializeObject(oldValue),
            NewValue = JsonConvert.SerializeObject(newValue)
        };
        
        await SaveAuditAsync(audit);
    }
}

Integration with Business Logic​

public async Task<CreateOrdersResult> CreateOrders(
    IEnumerable<SummaryOrder> orderSummaries)
{
    var result = new CreateOrdersResult();
    
    foreach (var order in orderSummaries)
    {
        try
        {
            // Create order
            var orderNumber = await PostToSyspro(order);
            order.OrderNumber = orderNumber;
            
            // Log success
            await _auditService.LogOrderCreation(order, "SUCCESS");
            
            result.SuccessfulOrders.Add(order);
        }
        catch (Exception ex)
        {
            // Log failure
            await _auditService.LogOrderCreation(order, $"FAILED: {ex.Message}");
            
            result.FailedOrders.Add(order);
        }
    }
    
    return result;
}

Audit Trail UI​

public class AuditHistoryViewModel : BaseViewModel
{
    public async Task LoadAuditHistory(string orderNumber)
    {
        var history = await _context.OrderAuditLogs
            .Where(a => a.OrderNumber == orderNumber)
            .OrderByDescending(a => a.ActionDate)
            .Select(a => new AuditHistoryItem
            {
                Action = a.Action,
                User = a.UserName,
                Date = a.ActionDate,
                Details = FormatAuditDetails(a)
            })
            .ToListAsync();
            
        AuditHistory = new ObservableCollection<AuditHistoryItem>(history);
    }
    
    private string FormatAuditDetails(OrderAuditLog audit)
    {
        switch (audit.Action)
        {
            case "ORDER_CREATED":
                var info = JsonConvert.DeserializeObject<dynamic>(audit.AdditionalInfo);
                return $"Created with {info.TotalLines} lines, value: {info.TotalValue:C}";
                
            case "ORDER_MODIFIED":
                return CompareChanges(audit.OldValue, audit.NewValue);
                
            default:
                return audit.AdditionalInfo;
        }
    }
}

Reporting Capabilities​

Audit Reports​

  1. User activity reports
  2. Order creation statistics
  3. Error frequency analysis
  4. Performance metrics
  5. Compliance reports

Search and Filter​

public async Task<IEnumerable<OrderAuditLog>> SearchAuditLogs(
    AuditSearchCriteria criteria)
{
    var query = _context.OrderAuditLogs.AsQueryable();
    
    if (!string.IsNullOrEmpty(criteria.OrderNumber))
        query = query.Where(a => a.OrderNumber == criteria.OrderNumber);
        
    if (!string.IsNullOrEmpty(criteria.UserName))
        query = query.Where(a => a.UserName == criteria.UserName);
        
    if (criteria.FromDate.HasValue)
        query = query.Where(a => a.ActionDate >= criteria.FromDate);
        
    if (criteria.ToDate.HasValue)
        query = query.Where(a => a.ActionDate <= criteria.ToDate);
        
    return await query.ToListAsync();
}

Data Retention​

Archival Strategy​

-- Archive old audit records
CREATE PROCEDURE ArchiveAuditLogs
    @DaysToKeep INT = 365
AS
BEGIN
    -- Move to archive table
    INSERT INTO CG_OrderAuditLog_Archive
    SELECT * FROM CG_OrderAuditLog
    WHERE ActionDate < DATEADD(DAY, -@DaysToKeep, GETDATE())
    
    -- Delete from main table
    DELETE FROM CG_OrderAuditLog
    WHERE ActionDate < DATEADD(DAY, -@DaysToKeep, GETDATE())
END

Benefits​

  • Complete traceability
  • Compliance support
  • Troubleshooting capability
  • Performance analysis
  • User accountability
  • Historical reporting