Skip to main content

Example: Dynamic Period Selection

Overview

This example demonstrates the sophisticated dynamic period selection system implemented in the AR Payment Reversal dashboard. The system intelligently determines available posting periods from SYSPRO, validates period status, handles multi-company scenarios, and provides user-friendly period selection while enforcing business rules around financial period management.

Business Context

Financial period management is critical for:

  • Compliance: Ensuring transactions post to correct accounting periods
  • Month-End Processing: Preventing posts to closed periods
  • Audit Trail: Maintaining accurate period-based reporting
  • Multi-Company: Supporting different period configurations per company
  • Backdating Control: Enforcing policies on historical posting

Implementation Architecture

Period Configuration Model

Data structures for period management:

// MepApps.Dash.Ar.Maint.PaymentReversal/Models/PeriodConfiguration.cs
public class PeriodConfiguration
{
public string CompanyId { get; set; }
public int CurrentYear { get; set; }
public int CurrentPeriod { get; set; }
public List<FinancialPeriod> AvailablePeriods { get; set; }
public PeriodSettings Settings { get; set; }
}

public class FinancialPeriod
{
public int Year { get; set; }
public int Period { get; set; }
public DateTime StartDate { get; set; }
public DateTime EndDate { get; set; }
public PeriodStatus Status { get; set; }
public bool IsLocked { get; set; }
public bool AllowPosting { get; set; }
public string Description { get; set; }
public int TransactionCount { get; set; }
public decimal TransactionTotal { get; set; }

// Computed properties
public string DisplayText => $"{Year:0000}/{Period:00} - {Description}";
public string StatusText => GetStatusText();
public bool IsSelectable => Status == PeriodStatus.Open && !IsLocked && AllowPosting;
public bool IsCurrent => Year == DateTime.Now.Year && Period == DateTime.Now.Month;
public bool IsFuture => EndDate > DateTime.Now;
public bool IsPast => StartDate < DateTime.Now.AddMonths(-1);

private string GetStatusText()
{
if (IsLocked) return "Locked";

return Status switch
{
PeriodStatus.Open => "Open",
PeriodStatus.Closed => "Closed",
PeriodStatus.Future => "Future",
PeriodStatus.YearEnd => "Year End",
_ => "Unknown"
};
}
}

public enum PeriodStatus
{
Open,
Closed,
Future,
YearEnd,
Audit,
Restricted
}

public class PeriodSettings
{
public bool AllowBackdating { get; set; }
public int MaxBackdatingMonths { get; set; }
public bool AllowFuturePosting { get; set; }
public int MaxFutureMonths { get; set; }
public bool RequireApprovalForClosed { get; set; }
public bool AutoSelectCurrentPeriod { get; set; }
public List<string> RestrictedPeriods { get; set; }
}

Period Service Implementation

Service for querying and validating periods:

// MepApps.Dash.Ar.Maint.PaymentReversal/Services/PeriodService.cs
public class PeriodService : IPeriodService
{
private readonly ILogger<PeriodService> _logger;
private readonly IServiceProvider _serviceProvider;
private readonly ICacheService _cache;

public async Task<List<SelectionItem>> GetAvailablePeriodsAsync(
PeriodSelectionCriteria criteria = null)
{
criteria ??= PeriodSelectionCriteria.Default;

// Check cache first
var cacheKey = $"periods_{criteria.CompanyId}_{criteria.Module}";
var cached = _cache.Get<List<SelectionItem>>(cacheKey);
if (cached != null && criteria.UseCache)
{
_logger.LogDebug("Using cached periods for {Company}", criteria.CompanyId);
return cached;
}

using (var context = _serviceProvider.GetService<PluginSysproDataContext>())
{
// Complex SQL query to get period information from multiple SYSPRO tables
var sql = @"
WITH PeriodData AS (
SELECT
p.ArPeriod,
p.ArYear,
p.ArMonth,
p.ArPeriodStatus,
p.ArStartDate,
p.ArEndDate,
p.ArDescription,
CASE
WHEN p.ArPeriodStatus = 'O' THEN 1 -- Open
WHEN p.ArPeriodStatus = 'C' THEN 0 -- Closed
WHEN p.ArPeriodStatus = 'F' THEN 0 -- Future
ELSE 0
END AS IsOpen,
CASE
WHEN pl.PeriodLocked = 'Y' THEN 1
ELSE 0
END AS IsLocked,
-- Count existing transactions in period
(SELECT COUNT(*)
FROM ArCshJnlCtl
WHERE TrnYear = p.ArYear
AND TrnMonth = p.ArMonth) AS TransactionCount,
-- Sum transaction values
(SELECT ISNULL(SUM(ControlTotal), 0)
FROM ArCshJnlCtl
WHERE TrnYear = p.ArYear
AND TrnMonth = p.ArMonth) AS TransactionTotal,
-- Current period flag
CASE
WHEN c.CurrentArYear = p.ArYear
AND c.CurrentArPeriod = p.ArMonth THEN 1
ELSE 0
END AS IsCurrent
FROM ArPeriod p
LEFT JOIN ArPeriodLock pl
ON p.ArYear = pl.Year
AND p.ArMonth = pl.Period
CROSS JOIN ArControl c
WHERE 1=1
{GetPeriodFilter(criteria)}
),
EnhancedPeriods AS (
SELECT
*,
-- Calculate if period is selectable based on business rules
CASE
WHEN IsOpen = 1
AND IsLocked = 0
AND ArEndDate >= @MinDate
AND ArStartDate <= @MaxDate
THEN 1
ELSE 0
END AS IsSelectable,
-- Determine display order
ROW_NUMBER() OVER (
ORDER BY
IsCurrent DESC, -- Current period first
ArYear DESC,
ArMonth DESC
) AS DisplayOrder
FROM PeriodData
)
SELECT
ArPeriod AS Value,
CONCAT(
FORMAT(ArYear, '0000'), '/',
FORMAT(ArMonth, '00'), ' - ',
ArDescription,
CASE WHEN IsCurrent = 1 THEN ' (Current)' ELSE '' END,
CASE WHEN IsLocked = 1 THEN ' [Locked]' ELSE '' END,
CASE WHEN IsOpen = 0 THEN ' [Closed]' ELSE '' END
) AS Description,
IsSelectable,
DisplayOrder,
ArYear,
ArMonth,
ArStartDate,
ArEndDate,
IsOpen,
IsLocked,
IsCurrent,
TransactionCount,
TransactionTotal
FROM EnhancedPeriods
WHERE IsSelectable = 1 OR @IncludeNonSelectable = 1
ORDER BY DisplayOrder";

var parameters = new[]
{
new SqlParameter("@MinDate", GetMinDate(criteria)),
new SqlParameter("@MaxDate", GetMaxDate(criteria)),
new SqlParameter("@IncludeNonSelectable", criteria.IncludeNonSelectable)
};

var periods = await context.Database
.SqlQuery<PeriodQueryResult>(sql, parameters)
.ToListAsync();

// Apply additional business rules
periods = await ApplyBusinessRules(periods, criteria);

// Convert to SelectionItem
var selectionItems = periods
.Select(p => new SelectionItem
{
Value = p.Value,
Description = p.Description,
Tag = new FinancialPeriod
{
Year = p.ArYear,
Period = p.ArMonth,
StartDate = p.ArStartDate,
EndDate = p.ArEndDate,
Status = p.IsOpen ? PeriodStatus.Open : PeriodStatus.Closed,
IsLocked = p.IsLocked,
AllowPosting = p.IsSelectable,
TransactionCount = p.TransactionCount,
TransactionTotal = p.TransactionTotal
}
})
.ToList();

// Cache results
_cache.Set(cacheKey, selectionItems, TimeSpan.FromMinutes(5));

_logger.LogInformation("Retrieved {Count} periods for selection",
selectionItems.Count);

return selectionItems;
}
}

private string GetPeriodFilter(PeriodSelectionCriteria criteria)
{
var filters = new List<string>();

if (!string.IsNullOrEmpty(criteria.CompanyId))
{
filters.Add($"AND p.CompanyId = '{criteria.CompanyId}'");
}

if (criteria.YearFrom.HasValue)
{
filters.Add($"AND p.ArYear >= {criteria.YearFrom}");
}

if (criteria.YearTo.HasValue)
{
filters.Add($"AND p.ArYear <= {criteria.YearTo}");
}

if (criteria.OnlyOpen)
{
filters.Add("AND p.ArPeriodStatus = 'O'");
}

if (!criteria.IncludeLocked)
{
filters.Add("AND (pl.PeriodLocked IS NULL OR pl.PeriodLocked != 'Y')");
}

return string.Join(" ", filters);
}

private async Task<List<PeriodQueryResult>> ApplyBusinessRules(
List<PeriodQueryResult> periods,
PeriodSelectionCriteria criteria)
{
var settings = await GetPeriodSettings(criteria.CompanyId);
var filtered = new List<PeriodQueryResult>();

foreach (var period in periods)
{
// Apply backdating rules
if (period.ArEndDate < DateTime.Now.AddMonths(-settings.MaxBackdatingMonths))
{
if (!settings.AllowBackdating)
{
_logger.LogDebug("Period {Year}/{Month} excluded due to backdating rules",
period.ArYear, period.ArMonth);
continue;
}

// Mark as requiring approval
period.Description += " [Approval Required]";
}

// Apply future posting rules
if (period.ArStartDate > DateTime.Now.AddMonths(settings.MaxFutureMonths))
{
if (!settings.AllowFuturePosting)
{
_logger.LogDebug("Period {Year}/{Month} excluded due to future posting rules",
period.ArYear, period.ArMonth);
continue;
}
}

// Check restricted periods
var periodKey = $"{period.ArYear:0000}{period.ArMonth:00}";
if (settings.RestrictedPeriods?.Contains(periodKey) == true)
{
if (!criteria.IncludeRestricted)
{
_logger.LogDebug("Period {Year}/{Month} is restricted",
period.ArYear, period.ArMonth);
continue;
}

period.Description += " [Restricted]";
}

filtered.Add(period);
}

return filtered;
}
}

Period Validation

Comprehensive period validation before posting:

// MepApps.Dash.Ar.Maint.PaymentReversal/Services/PeriodValidator.cs
public class PeriodValidator : IPeriodValidator
{
private readonly ILogger<PeriodValidator> _logger;
private readonly IPeriodService _periodService;

public async Task<ValidationResult> ValidatePeriodAsync(
string periodCode,
DateTime? transactionDate = null)
{
var result = new ValidationResult();

try
{
// Parse period code (YYYYMM format)
if (!TryParsePeriodCode(periodCode, out int year, out int month))
{
result.AddError("Period", $"Invalid period format: {periodCode}");
return result;
}

// Get period details
var period = await GetPeriodDetails(year, month);
if (period == null)
{
result.AddError("Period", $"Period {periodCode} not found");
return result;
}

// Validate period status
if (period.Status != PeriodStatus.Open)
{
result.AddError("Period",
$"Period {periodCode} is {period.Status}. Only open periods can be posted to.");
}

// Validate period lock
if (period.IsLocked)
{
result.AddError("Period",
$"Period {periodCode} is locked for posting");
}

// Validate against transaction date
if (transactionDate.HasValue)
{
if (transactionDate.Value < period.StartDate ||
transactionDate.Value > period.EndDate)
{
result.AddError("Period",
$"Transaction date {transactionDate:yyyy-MM-dd} is outside period " +
$"{period.StartDate:yyyy-MM-dd} to {period.EndDate:yyyy-MM-dd}",
ValidationSeverity.Warning);
}
}

// Check for year-end processing
if (period.Status == PeriodStatus.YearEnd)
{
result.AddError("Period",
"Cannot post to period during year-end processing",
ValidationSeverity.Error);
}

// Check transaction limits
if (period.TransactionCount > 10000)
{
result.AddError("Period",
$"Period {periodCode} has {period.TransactionCount} transactions. " +
"Consider posting to a different period.",
ValidationSeverity.Warning);
}

// Validate user permissions for period
var hasPermission = await ValidateUserPermission(period);
if (!hasPermission)
{
result.AddError("Period",
$"You do not have permission to post to period {periodCode}");
}

_logger.LogDebug("Period validation complete for {Period}. Valid: {IsValid}",
periodCode, result.IsValid);
}
catch (Exception ex)
{
_logger.LogError(ex, "Error validating period {Period}", periodCode);
result.AddError("Period", "Period validation error occurred");
}

return result;
}

private async Task<bool> ValidateUserPermission(FinancialPeriod period)
{
// Check if user has permission to post to restricted periods
if (period.Status == PeriodStatus.Restricted)
{
return await _userService.HasPermissionAsync("PostToRestrictedPeriod");
}

// Check backdating permission
if (period.EndDate < DateTime.Now.AddMonths(-1))
{
return await _userService.HasPermissionAsync("BackdateTransactions");
}

return true;
}
}

Period Selection UI

User interface for period selection:

<!-- Period Selection Control -->
<UserControl x:Class="MepApps.Dash.Ar.Maint.PaymentReversal.Controls.PeriodSelector">
<Grid>
<Grid.RowDefinitions>
<RowDefinition Height="Auto"/>
<RowDefinition Height="Auto"/>
<RowDefinition Height="Auto"/>
</Grid.RowDefinitions>

<!-- Period ComboBox -->
<ComboBox Grid.Row="0"
ItemsSource="{Binding AvailablePeriods}"
SelectedItem="{Binding SelectedPeriod}"
DisplayMemberPath="Description"
SelectedValuePath="Value">
<ComboBox.ItemContainerStyle>
<Style TargetType="ComboBoxItem">
<Style.Triggers>
<!-- Highlight current period -->
<DataTrigger Binding="{Binding Tag.IsCurrent}" Value="True">
<Setter Property="FontWeight" Value="Bold"/>
<Setter Property="Background" Value="LightGreen"/>
</DataTrigger>

<!-- Disable non-selectable periods -->
<DataTrigger Binding="{Binding Tag.AllowPosting}" Value="False">
<Setter Property="IsEnabled" Value="False"/>
<Setter Property="Foreground" Value="Gray"/>
</DataTrigger>

<!-- Mark locked periods -->
<DataTrigger Binding="{Binding Tag.IsLocked}" Value="True">
<Setter Property="Background" Value="LightPink"/>
<Setter Property="ToolTip" Value="This period is locked"/>
</DataTrigger>
</Style.Triggers>

<Setter Property="ToolTip">
<Setter.Value>
<ToolTip>
<StackPanel>
<TextBlock FontWeight="Bold">
<TextBlock.Text>
<MultiBinding StringFormat="Period {0}/{1:00}">
<Binding Path="Tag.Year"/>
<Binding Path="Tag.Period"/>
</MultiBinding>
</TextBlock.Text>
</TextBlock>
<TextBlock>
<TextBlock.Text>
<MultiBinding StringFormat="Date Range: {0:MMM dd} - {1:MMM dd, yyyy}">
<Binding Path="Tag.StartDate"/>
<Binding Path="Tag.EndDate"/>
</MultiBinding>
</TextBlock.Text>
</TextBlock>
<TextBlock Text="{Binding Tag.StatusText, StringFormat='Status: {0}'}"/>
<TextBlock Text="{Binding Tag.TransactionCount, StringFormat='Transactions: {0:N0}'}"/>
<TextBlock Text="{Binding Tag.TransactionTotal, StringFormat='Total: {0:C}'}"/>
</StackPanel>
</ToolTip>
</Setter.Value>
</Setter>
</Style>
</ComboBox.ItemContainerStyle>
</ComboBox>

<!-- Period Information Panel -->
<Border Grid.Row="1"
Background="LightYellow"
BorderBrush="Orange"
BorderThickness="1"
Padding="5"
Margin="0,5,0,0"
Visibility="{Binding ShowPeriodWarning,
Converter={StaticResource BoolToVisibilityConverter}}">
<StackPanel>
<TextBlock Text="⚠ Period Warning" FontWeight="Bold"/>
<TextBlock Text="{Binding PeriodWarningMessage}"
TextWrapping="Wrap"/>
</StackPanel>
</Border>

<!-- Period Details -->
<Expander Grid.Row="2" Header="Period Details" IsExpanded="False" Margin="0,5,0,0">
<Grid Margin="5">
<Grid.ColumnDefinitions>
<ColumnDefinition Width="Auto"/>
<ColumnDefinition Width="*"/>
</Grid.ColumnDefinitions>
<Grid.RowDefinitions>
<RowDefinition Height="Auto"/>
<RowDefinition Height="Auto"/>
<RowDefinition Height="Auto"/>
<RowDefinition Height="Auto"/>
<RowDefinition Height="Auto"/>
</Grid.RowDefinitions>

<TextBlock Grid.Row="0" Grid.Column="0" Text="Period:" Margin="0,2,10,2"/>
<TextBlock Grid.Row="0" Grid.Column="1"
Text="{Binding SelectedPeriod.Tag.DisplayText}"/>

<TextBlock Grid.Row="1" Grid.Column="0" Text="Date Range:" Margin="0,2,10,2"/>
<TextBlock Grid.Row="1" Grid.Column="1">
<TextBlock.Text>
<MultiBinding StringFormat="{}{0:MMM dd, yyyy} - {1:MMM dd, yyyy}">
<Binding Path="SelectedPeriod.Tag.StartDate"/>
<Binding Path="SelectedPeriod.Tag.EndDate"/>
</MultiBinding>
</TextBlock.Text>
</TextBlock>

<TextBlock Grid.Row="2" Grid.Column="0" Text="Status:" Margin="0,2,10,2"/>
<TextBlock Grid.Row="2" Grid.Column="1"
Text="{Binding SelectedPeriod.Tag.StatusText}"/>

<TextBlock Grid.Row="3" Grid.Column="0" Text="Transactions:" Margin="0,2,10,2"/>
<TextBlock Grid.Row="3" Grid.Column="1"
Text="{Binding SelectedPeriod.Tag.TransactionCount, StringFormat='{}{0:N0}'}"/>

<TextBlock Grid.Row="4" Grid.Column="0" Text="Total Posted:" Margin="0,2,10,2"/>
<TextBlock Grid.Row="4" Grid.Column="1"
Text="{Binding SelectedPeriod.Tag.TransactionTotal, StringFormat='{}{0:C}'}"/>
</Grid>
</Expander>
</Grid>
</UserControl>

Period Selection ViewModel

ViewModel managing period selection logic:

// MepApps.Dash.Ar.Maint.PaymentReversal/ViewModels/PeriodSelectorViewModel.cs
public class PeriodSelectorViewModel : BaseViewModel
{
private readonly IPeriodService _periodService;
private readonly IPeriodValidator _periodValidator;
private readonly ILogger<PeriodSelectorViewModel> _logger;

private ObservableCollection<SelectionItem> _availablePeriods;
public ObservableCollection<SelectionItem> AvailablePeriods
{
get => _availablePeriods;
set => SetField(ref _availablePeriods, value);
}

private SelectionItem _selectedPeriod;
public SelectionItem SelectedPeriod
{
get => _selectedPeriod;
set
{
if (SetField(ref _selectedPeriod, value))
{
OnPeriodChanged();
}
}
}

private bool _showPeriodWarning;
public bool ShowPeriodWarning
{
get => _showPeriodWarning;
set => SetField(ref _showPeriodWarning, value);
}

private string _periodWarningMessage;
public string PeriodWarningMessage
{
get => _periodWarningMessage;
set => SetField(ref _periodWarningMessage, value);
}

public async Task InitializeAsync()
{
try
{
IsLoading = true;

// Load available periods
var criteria = new PeriodSelectionCriteria
{
OnlyOpen = true,
IncludeLocked = false,
YearFrom = DateTime.Now.Year - 1,
YearTo = DateTime.Now.Year + 1,
UseCache = true
};

var periods = await _periodService.GetAvailablePeriodsAsync(criteria);
AvailablePeriods = new ObservableCollection<SelectionItem>(periods);

// Auto-select current period if configured
var settings = await _periodService.GetPeriodSettings();
if (settings.AutoSelectCurrentPeriod)
{
var currentPeriod = periods.FirstOrDefault(p =>
((FinancialPeriod)p.Tag).IsCurrent);

if (currentPeriod != null)
{
SelectedPeriod = currentPeriod;
_logger.LogDebug("Auto-selected current period: {Period}",
currentPeriod.Description);
}
}

// If no current period or auto-select disabled, select first available
if (SelectedPeriod == null && periods.Any())
{
SelectedPeriod = periods.First();
}
}
catch (Exception ex)
{
_logger.LogError(ex, "Error initializing period selector");
ShowError("Failed to load available periods");
}
finally
{
IsLoading = false;
}
}

private async void OnPeriodChanged()
{
if (SelectedPeriod?.Tag == null)
{
ShowPeriodWarning = false;
return;
}

var period = (FinancialPeriod)SelectedPeriod.Tag;

// Validate selected period
var validation = await _periodValidator.ValidatePeriodAsync(
$"{period.Year:0000}{period.Period:00}");

if (validation.Warnings.Any())
{
ShowPeriodWarning = true;
PeriodWarningMessage = string.Join("\n", validation.Warnings.Select(w => w.Message));
}
else
{
ShowPeriodWarning = false;
}

// Raise period changed event
PeriodChanged?.Invoke(this, new PeriodChangedEventArgs
{
Period = period,
PeriodCode = $"{period.Year:0000}{period.Period:00}",
IsValid = validation.IsValid
});
}

public event EventHandler<PeriodChangedEventArgs> PeriodChanged;
}

Period Auto-Detection

Intelligent period detection based on transaction dates:

public class PeriodAutoDetector
{
public async Task<string> DetectPeriodForTransaction(
DateTime transactionDate,
string companyId = null)
{
using (var context = _serviceProvider.GetService<PluginSysproDataContext>())
{
// Find period containing transaction date
var period = await context.Database
.SqlQuery<PeriodInfo>(@"
SELECT TOP 1
ArPeriod,
ArYear,
ArMonth,
ArPeriodStatus
FROM ArPeriod
WHERE ArStartDate <= @TransDate
AND ArEndDate >= @TransDate
AND ArPeriodStatus = 'O'
ORDER BY ArYear DESC, ArMonth DESC",
new SqlParameter("@TransDate", transactionDate))
.FirstOrDefaultAsync();

if (period == null)
{
// No open period for date, find nearest open period
period = await FindNearestOpenPeriod(transactionDate);
}

return period?.ArPeriod;
}
}
}

Benefits

  1. Compliance: Ensures transactions post to correct periods
  2. Flexibility: Supports various business rules and restrictions
  3. User Experience: Clear visual feedback on period status
  4. Performance: Caching reduces database queries
  5. Audit Trail: Complete tracking of period selections

Summary

The dynamic period selection system provides intelligent, rule-based financial period management for the AR Payment Reversal dashboard. Through comprehensive validation, visual feedback, and business rule enforcement, the system ensures accurate period posting while maintaining flexibility for various organizational requirements and compliance needs.