/// <summary>
/// Binds the registrations grid.
/// </summary>
private void BindRegistrationsGrid()
{
int? instanceId = hfRegistrationInstanceId.Value.AsIntegerOrNull();
if ( instanceId.HasValue )
{
using ( var rockContext = new RockContext() )
{
var registrationEntityType = EntityTypeCache.Read( typeof( Rock.Model.Registration ) );
var instance = new RegistrationInstanceService( rockContext ).Get( instanceId.Value );
_instanceHasCost = instance != null && instance.RegistrationTemplate.Cost > 0.0m;
var qry = new RegistrationService( rockContext )
.Queryable( "PersonAlias.Person,Registrants.PersonAlias.Person,Registrants.Fees.RegistrationTemplateFee" )
.AsNoTracking()
.Where( r => r.RegistrationInstanceId == instanceId.Value );
if ( drpRegistrationDateRange.LowerValue.HasValue )
{
qry = qry.Where( r =>
r.CreatedDateTime.HasValue &&
r.CreatedDateTime.Value >= drpRegistrationDateRange.LowerValue.Value );
}
if ( drpRegistrationDateRange.UpperValue.HasValue )
{
qry = qry.Where( r =>
r.CreatedDateTime.HasValue &&
r.CreatedDateTime.Value <= drpRegistrationDateRange.UpperValue.Value );
}
if ( !string.IsNullOrWhiteSpace( tbRegistrationRegisteredByFirstName.Text ) )
{
string pfname = tbRegistrationRegisteredByFirstName.Text;
qry = qry.Where( r =>
r.FirstName.StartsWith( pfname ) ||
r.PersonAlias.Person.NickName.StartsWith( pfname ) ||
r.PersonAlias.Person.FirstName.StartsWith( pfname ) );
}
if ( !string.IsNullOrWhiteSpace( tbRegistrationRegisteredByLastName.Text ) )
{
string plname = tbRegistrationRegisteredByLastName.Text;
qry = qry.Where( r =>
r.LastName.StartsWith( plname ) ||
r.PersonAlias.Person.LastName.StartsWith( plname ) );
}
if ( !string.IsNullOrWhiteSpace( tbRegistrationRegistrantFirstName.Text ) )
{
string rfname = tbRegistrationRegistrantFirstName.Text;
qry = qry.Where( r =>
r.Registrants.Any( p =>
p.PersonAlias.Person.NickName.StartsWith( rfname ) ||
p.PersonAlias.Person.FirstName.StartsWith( rfname ) ) );
}
if ( !string.IsNullOrWhiteSpace( tbRegistrationRegistrantLastName.Text ) )
{
string rlname = tbRegistrationRegistrantLastName.Text;
qry = qry.Where( r =>
r.Registrants.Any( p =>
p.PersonAlias.Person.LastName.StartsWith( rlname ) ) );
}
// If filtering on payment status, need to do some sub-querying...
if ( ddlRegistrationPaymentStatus.SelectedValue != "" && registrationEntityType != null )
{
// Get all the registrant costs
var rCosts = new Dictionary<int, decimal>();
qry
.Select( r => new
{
RegistrationId = r.Id,
Costs = r.Registrants.Sum( p => p.Cost ),
Fees = r.Registrants.SelectMany( p => p.Fees ).Sum( f => f.Cost )
} ).ToList()
.ForEach( c =>
rCosts.AddOrReplace( c.RegistrationId, c.Costs + c.Fees ) );
var rPayments = new Dictionary<int, decimal>();
new FinancialTransactionDetailService( rockContext )
.Queryable().AsNoTracking()
.Where( d =>
d.EntityTypeId.HasValue &&
d.EntityId.HasValue &&
d.EntityTypeId.Value == registrationEntityType.Id &&
rCosts.Keys.Contains( d.EntityId.Value ) )
.Select( d => new
{
RegistrationId = d.EntityId.Value,
Payment = d.Amount
} )
.ToList()
.GroupBy( d => d.RegistrationId )
.Select( d => new
{
RegistrationId = d.Key,
Payments = d.Sum( p => p.Payment )
} )
.ToList()
.ForEach( p =>
rPayments.AddOrReplace( p.RegistrationId, p.Payments ) );
var rPmtSummary = rCosts
.Join( rPayments, c => c.Key, p => p.Key, ( c, p ) => new
{
RegistrationId = c.Key,
Costs = c.Value,
Payments = p.Value
} )
.ToList();
var ids = new List<int>();
if ( ddlRegistrationPaymentStatus.SelectedValue == "Paid in Full" )
{
ids = rPmtSummary
.Where( r => r.Costs <= r.Payments )
.Select( r => r.RegistrationId )
.ToList();
}
else
{
ids = rPmtSummary
.Where( r => r.Costs > r.Payments )
.Select( r => r.RegistrationId )
.ToList();
}
qry = qry.Where( r => ids.Contains( r.Id ) );
}
IOrderedQueryable<Registration> orderedQry = null;
SortProperty sortProperty = gRegistrations.SortProperty;
if ( sortProperty != null )
{
orderedQry = qry.Sort( sortProperty );
}
else
{
orderedQry = qry.OrderByDescending( r => r.CreatedDateTime );
}
gRegistrations.SetLinqDataSource( orderedQry );
// Get all the payments for any registrations being displayed on the current page.
// This is used in the RowDataBound event but queried now so that each row does
// not have to query for the data.
var currentPageRegistrations = gRegistrations.DataSource as List<Registration>;
if ( currentPageRegistrations != null && registrationEntityType != null )
{
var registrationIds = currentPageRegistrations
.Select( r => r.Id )
.ToList();
RegistrationPayments = new FinancialTransactionDetailService( rockContext )
.Queryable().AsNoTracking()
.Where( d =>
d.EntityTypeId.HasValue &&
d.EntityId.HasValue &&
d.EntityTypeId.Value == registrationEntityType.Id &&
registrationIds.Contains( d.EntityId.Value ) )
.ToList();
}
gRegistrations.DataBind();
}
}
}
/// <summary>
/// Binds the registrations grid.
/// </summary>
private void BindRegistrationsGrid()
{
int? instanceId = hfRegistrationInstanceId.Value.AsIntegerOrNull();
if ( instanceId.HasValue )
{
using ( var rockContext = new RockContext() )
{
var registrationEntityType = EntityTypeCache.Read( typeof( Rock.Model.Registration ) );
var instance = new RegistrationInstanceService( rockContext ).Get( instanceId.Value );
decimal cost = instance.RegistrationTemplate.Cost;
if ( instance.RegistrationTemplate.SetCostOnInstance ?? false )
{
cost = instance.Cost ?? 0.0m;
}
_instanceHasCost = cost > 0.0m;
var qry = new RegistrationService( rockContext )
.Queryable( "PersonAlias.Person,Registrants.PersonAlias.Person,Registrants.Fees.RegistrationTemplateFee" )
.AsNoTracking()
.Where( r =>
r.RegistrationInstanceId == instanceId.Value &&
!r.IsTemporary );
if ( drpRegistrationDateRange.LowerValue.HasValue )
{
qry = qry.Where( r =>
r.CreatedDateTime.HasValue &&
r.CreatedDateTime.Value >= drpRegistrationDateRange.LowerValue.Value );
}
if ( drpRegistrationDateRange.UpperValue.HasValue )
{
qry = qry.Where( r =>
r.CreatedDateTime.HasValue &&
r.CreatedDateTime.Value <= drpRegistrationDateRange.UpperValue.Value );
}
if ( !string.IsNullOrWhiteSpace( tbRegistrationRegisteredByFirstName.Text ) )
{
string pfname = tbRegistrationRegisteredByFirstName.Text;
qry = qry.Where( r =>
r.FirstName.StartsWith( pfname ) ||
r.PersonAlias.Person.NickName.StartsWith( pfname ) ||
r.PersonAlias.Person.FirstName.StartsWith( pfname ) );
}
if ( !string.IsNullOrWhiteSpace( tbRegistrationRegisteredByLastName.Text ) )
{
string plname = tbRegistrationRegisteredByLastName.Text;
qry = qry.Where( r =>
r.LastName.StartsWith( plname ) ||
r.PersonAlias.Person.LastName.StartsWith( plname ) );
}
if ( !string.IsNullOrWhiteSpace( tbRegistrationRegistrantFirstName.Text ) )
{
string rfname = tbRegistrationRegistrantFirstName.Text;
qry = qry.Where( r =>
r.Registrants.Any( p =>
p.PersonAlias.Person.NickName.StartsWith( rfname ) ||
p.PersonAlias.Person.FirstName.StartsWith( rfname ) ) );
}
if ( !string.IsNullOrWhiteSpace( tbRegistrationRegistrantLastName.Text ) )
{
string rlname = tbRegistrationRegistrantLastName.Text;
qry = qry.Where( r =>
r.Registrants.Any( p =>
p.PersonAlias.Person.LastName.StartsWith( rlname ) ) );
}
// If filtering on payment status, need to do some sub-querying...
if ( ddlRegistrationPaymentStatus.SelectedValue != "" && registrationEntityType != null )
{
// Get all the registrant costs
var rCosts = new Dictionary<int, decimal>();
qry.ToList()
.Select( r => new
{
RegistrationId = r.Id,
DiscountCosts = r.Registrants.Sum( p => (decimal?)( p.DiscountedCost( r.DiscountPercentage, r.DiscountAmount) ) ) ?? 0.0m,
} ).ToList()
.ForEach( c =>
rCosts.AddOrReplace( c.RegistrationId, c.DiscountCosts ) );
var rPayments = new Dictionary<int, decimal>();
new FinancialTransactionDetailService( rockContext )
.Queryable().AsNoTracking()
.Where( d =>
d.EntityTypeId.HasValue &&
d.EntityId.HasValue &&
d.EntityTypeId.Value == registrationEntityType.Id &&
rCosts.Keys.Contains( d.EntityId.Value ) )
.Select( d => new
{
RegistrationId = d.EntityId.Value,
Payment = d.Amount
} )
.ToList()
.GroupBy( d => d.RegistrationId )
.Select( d => new
{
RegistrationId = d.Key,
Payments = d.Sum( p => p.Payment )
} )
.ToList()
.ForEach( p =>
rPayments.AddOrReplace( p.RegistrationId, p.Payments ) );
var rPmtSummary = rCosts
.Join( rPayments, c => c.Key, p => p.Key, ( c, p ) => new
{
RegistrationId = c.Key,
Costs = c.Value,
Payments = p.Value
} )
.ToList();
var ids = new List<int>();
if ( ddlRegistrationPaymentStatus.SelectedValue == "Paid in Full" )
{
ids = rPmtSummary
.Where( r => r.Costs <= r.Payments )
.Select( r => r.RegistrationId )
.ToList();
}
else
{
ids = rPmtSummary
.Where( r => r.Costs > r.Payments )
.Select( r => r.RegistrationId )
.ToList();
}
qry = qry.Where( r => ids.Contains( r.Id ) );
}
SortProperty sortProperty = gRegistrations.SortProperty;
if ( sortProperty != null )
{
// If sorting by Total Cost or Balance Due, the database query needs to be run first without ordering,
// and then ordering needs to be done in memory since TotalCost and BalanceDue are not databae fields.
if ( sortProperty.Property == "TotalCost" )
{
if ( sortProperty.Direction == SortDirection.Ascending )
{
gRegistrations.SetLinqDataSource( qry.ToList().OrderBy( r => r.TotalCost ).AsQueryable() );
}
else
{
gRegistrations.SetLinqDataSource( qry.ToList().OrderByDescending( r => r.TotalCost ).AsQueryable() );
}
}
else if ( sortProperty.Property == "BalanceDue" )
{
if ( sortProperty.Direction == SortDirection.Ascending )
{
gRegistrations.SetLinqDataSource( qry.ToList().OrderBy( r => r.BalanceDue ).AsQueryable() );
}
else
{
gRegistrations.SetLinqDataSource( qry.ToList().OrderByDescending( r => r.BalanceDue ).AsQueryable() );
}
}
else
{
gRegistrations.SetLinqDataSource( qry.Sort( sortProperty ) );
}
}
else
{
gRegistrations.SetLinqDataSource( qry.OrderByDescending( r => r.CreatedDateTime ) );
}
// Get all the payments for any registrations being displayed on the current page.
// This is used in the RowDataBound event but queried now so that each row does
// not have to query for the data.
var currentPageRegistrations = gRegistrations.DataSource as List<Registration>;
if ( currentPageRegistrations != null && registrationEntityType != null )
{
var registrationIds = currentPageRegistrations
.Select( r => r.Id )
.ToList();
RegistrationPayments = new FinancialTransactionDetailService( rockContext )
.Queryable().AsNoTracking()
.Where( d =>
d.EntityTypeId.HasValue &&
d.EntityId.HasValue &&
d.EntityTypeId.Value == registrationEntityType.Id &&
registrationIds.Contains( d.EntityId.Value ) )
.ToList();
}
var discountCodeHeader = gRegistrations.Columns.GetColumnByHeaderText( "Discount Code" );
if ( discountCodeHeader != null )
{
discountCodeHeader.Visible = GetAttributeValue( "DisplayDiscountCodes" ).AsBoolean();
}
gRegistrations.DataBind();
}
}
}
