/// <summary>
/// Binds the registrants grid.
/// </summary>
private void BindRegistrantsGrid( bool isExporting = false )
{
int? instanceId = hfRegistrationInstanceId.Value.AsIntegerOrNull();
if ( instanceId.HasValue )
{
using ( var rockContext = new RockContext() )
{
var registrationInstance = new RegistrationInstanceService( rockContext ).Get( instanceId.Value );
if ( registrationInstance != null &&
registrationInstance.RegistrationTemplate != null &&
registrationInstance.RegistrationTemplate.RequiredSignatureDocumentTemplateId.HasValue )
{
Signers = new SignatureDocumentService( rockContext )
.Queryable().AsNoTracking()
.Where( d =>
d.SignatureDocumentTemplateId == registrationInstance.RegistrationTemplate.RequiredSignatureDocumentTemplateId.Value &&
d.Status == SignatureDocumentStatus.Signed &&
d.BinaryFileId.HasValue &&
d.AppliesToPersonAlias != null )
.OrderByDescending( d => d.LastStatusDate )
.Select( d => d.AppliesToPersonAlias.PersonId )
.ToList();
}
// Start query for registrants
var qry = new RegistrationRegistrantService( rockContext )
.Queryable( "PersonAlias.Person.PhoneNumbers.NumberTypeValue,Fees.RegistrationTemplateFee,GroupMember.Group" ).AsNoTracking()
.Where( r =>
r.Registration.RegistrationInstanceId == instanceId.Value &&
r.PersonAlias != null &&
r.PersonAlias.Person != null );
// Filter by daterange
if ( drpRegistrantDateRange.LowerValue.HasValue )
{
qry = qry.Where( r =>
r.CreatedDateTime.HasValue &&
r.CreatedDateTime.Value >= drpRegistrantDateRange.LowerValue.Value );
}
if ( drpRegistrantDateRange.UpperValue.HasValue )
{
qry = qry.Where( r =>
r.CreatedDateTime.HasValue &&
r.CreatedDateTime.Value <= drpRegistrantDateRange.UpperValue.Value );
}
// Filter by first name
if ( !string.IsNullOrWhiteSpace( tbRegistrantFirstName.Text ) )
{
string rfname = tbRegistrantFirstName.Text;
qry = qry.Where( r =>
r.PersonAlias.Person.NickName.StartsWith( rfname ) ||
r.PersonAlias.Person.FirstName.StartsWith( rfname ) );
}
// Filter by last name
if ( !string.IsNullOrWhiteSpace( tbRegistrantLastName.Text ) )
{
string rlname = tbRegistrantLastName.Text;
qry = qry.Where( r =>
r.PersonAlias.Person.LastName.StartsWith( rlname ) );
}
// Filter by signed documents
if ( Signers != null )
{
if ( ddlSignedDocument.SelectedValue.AsBooleanOrNull() == true )
{
qry = qry.Where( r => Signers.Contains( r.PersonAlias.PersonId ) );
}
else if ( ddlSignedDocument.SelectedValue.AsBooleanOrNull() == false )
{
qry = qry.Where( r => !Signers.Contains( r.PersonAlias.PersonId ) );
}
}
if ( ddlInGroup.SelectedValue.AsBooleanOrNull() == true )
{
qry = qry.Where( r => r.GroupMemberId.HasValue );
}
else if ( ddlInGroup.SelectedValue.AsBooleanOrNull() == false )
{
qry = qry.Where( r => !r.GroupMemberId.HasValue );
}
bool preloadCampusValues = false;
var registrantAttributes = new List<AttributeCache>();
var personAttributes = new List<AttributeCache>();
var groupMemberAttributes = new List<AttributeCache>();
var registrantAttributeIds = new List<int>();
var personAttributesIds = new List<int>();
var groupMemberAttributesIds = new List<int>();
if ( isExporting )
{
// get list of home addresses
var personIds = qry.Select( r => r.PersonAlias.PersonId ).ToList();
_homeAddresses = Person.GetHomeLocations( personIds );
}
if ( RegistrantFields != null )
{
// Filter by any selected
foreach ( var personFieldType in RegistrantFields
.Where( f =>
f.FieldSource == RegistrationFieldSource.PersonField &&
f.PersonFieldType.HasValue )
.Select( f => f.PersonFieldType.Value ) )
{
switch ( personFieldType )
{
case RegistrationPersonFieldType.Campus:
{
preloadCampusValues = true;
var ddlCampus = phRegistrantFormFieldFilters.FindControl( "ddlCampus" ) as RockDropDownList;
if ( ddlCampus != null )
{
var campusId = ddlCampus.SelectedValue.AsIntegerOrNull();
if ( campusId.HasValue )
{
var familyGroupTypeGuid = Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY.AsGuid();
qry = qry.Where( r =>
r.PersonAlias.Person.Members.Any( m =>
m.Group.GroupType.Guid == familyGroupTypeGuid &&
m.Group.CampusId.HasValue &&
m.Group.CampusId.Value == campusId ) );
}
}
break;
}
case RegistrationPersonFieldType.Email:
{
var tbEmailFilter = phRegistrantFormFieldFilters.FindControl( "tbEmailFilter" ) as RockTextBox;
if ( tbEmailFilter != null && !string.IsNullOrWhiteSpace( tbEmailFilter.Text ) )
{
qry = qry.Where( r =>
r.PersonAlias.Person.Email != null &&
r.PersonAlias.Person.Email.Contains( tbEmailFilter.Text ) );
}
break;
}
case RegistrationPersonFieldType.Birthdate:
{
var drpBirthdateFilter = phRegistrantFormFieldFilters.FindControl( "drpBirthdateFilter" ) as DateRangePicker;
if ( drpBirthdateFilter != null )
{
if ( drpBirthdateFilter.LowerValue.HasValue )
{
qry = qry.Where( r =>
r.PersonAlias.Person.BirthDate.HasValue &&
r.PersonAlias.Person.BirthDate.Value >= drpBirthdateFilter.LowerValue.Value );
}
if ( drpBirthdateFilter.UpperValue.HasValue )
{
qry = qry.Where( r =>
r.PersonAlias.Person.BirthDate.HasValue &&
r.PersonAlias.Person.BirthDate.Value <= drpBirthdateFilter.UpperValue.Value );
}
}
break;
}
case RegistrationPersonFieldType.Grade:
{
var gpGradeFilter = phRegistrantFormFieldFilters.FindControl( "gpGradeFilter" ) as GradePicker;
if ( gpGradeFilter != null )
{
int? graduationYear = Person.GraduationYearFromGradeOffset( gpGradeFilter.SelectedValueAsInt( false ) );
if ( graduationYear.HasValue )
{
qry = qry.Where( r =>
r.PersonAlias.Person.GraduationYear.HasValue &&
r.PersonAlias.Person.GraduationYear == graduationYear.Value );
}
}
break;
}
case RegistrationPersonFieldType.Gender:
{
var ddlGenderFilter = phRegistrantFormFieldFilters.FindControl( "ddlGenderFilter" ) as RockDropDownList;
if ( ddlGenderFilter != null )
{
var gender = ddlGenderFilter.SelectedValue.ConvertToEnumOrNull<Gender>();
if ( gender.HasValue )
{
qry = qry.Where( r =>
r.PersonAlias.Person.Gender == gender );
}
}
break;
}
case RegistrationPersonFieldType.MaritalStatus:
{
var ddlMaritalStatusFilter = phRegistrantFormFieldFilters.FindControl( "ddlMaritalStatusFilter" ) as RockDropDownList;
if ( ddlMaritalStatusFilter != null )
{
var maritalStatusId = ddlMaritalStatusFilter.SelectedValue.AsIntegerOrNull();
if ( maritalStatusId.HasValue )
{
qry = qry.Where( r =>
r.PersonAlias.Person.MaritalStatusValueId.HasValue &&
r.PersonAlias.Person.MaritalStatusValueId.Value == maritalStatusId.Value );
}
}
break;
}
case RegistrationPersonFieldType.MobilePhone:
{
var tbPhoneFilter = phRegistrantFormFieldFilters.FindControl( "tbPhoneFilter" ) as RockTextBox;
if ( tbPhoneFilter != null && !string.IsNullOrWhiteSpace( tbPhoneFilter.Text ) )
{
string numericPhone = tbPhoneFilter.Text.AsNumeric();
qry = qry.Where( r =>
r.PersonAlias.Person.PhoneNumbers != null &&
r.PersonAlias.Person.PhoneNumbers.Any( n => n.Number.Contains( numericPhone ) ) );
}
break;
}
}
}
// Get all the registrant attributes selected to be on grid
registrantAttributes = RegistrantFields
.Where( f =>
f.Attribute != null &&
f.FieldSource == RegistrationFieldSource.RegistrationAttribute )
.Select( f => f.Attribute )
.ToList();
registrantAttributeIds = registrantAttributes.Select( a => a.Id ).Distinct().ToList();
// Filter query by any configured registrant attribute filters
if ( registrantAttributes != null && registrantAttributes.Any() )
{
var attributeValueService = new AttributeValueService( rockContext );
var parameterExpression = attributeValueService.ParameterExpression;
foreach ( var attribute in registrantAttributes )
{
var filterControl = phRegistrantFormFieldFilters.FindControl( "filter_" + attribute.Id.ToString() );
if ( filterControl != null )
{
var filterValues = attribute.FieldType.Field.GetFilterValues( filterControl, attribute.QualifierValues, Rock.Reporting.FilterMode.SimpleFilter );
var expression = attribute.FieldType.Field.AttributeFilterExpression( attribute.QualifierValues, filterValues, parameterExpression );
if ( expression != null )
{
var attributeValues = attributeValueService
.Queryable()
.Where( v => v.Attribute.Id == attribute.Id );
attributeValues = attributeValues.Where( parameterExpression, expression, null );
qry = qry
.Where( r => attributeValues.Select( v => v.EntityId ).Contains( r.Id ) );
}
}
}
}
// Get all the person attributes selected to be on grid
personAttributes = RegistrantFields
.Where( f =>
f.Attribute != null &&
f.FieldSource == RegistrationFieldSource.PersonAttribute )
.Select( f => f.Attribute )
.ToList();
personAttributesIds = personAttributes.Select( a => a.Id ).Distinct().ToList();
// Filter query by any configured person attribute filters
if ( personAttributes != null && personAttributes.Any() )
{
var attributeValueService = new AttributeValueService( rockContext );
var parameterExpression = attributeValueService.ParameterExpression;
foreach ( var attribute in personAttributes )
{
var filterControl = phRegistrantFormFieldFilters.FindControl( "filter_" + attribute.Id.ToString() );
if ( filterControl != null )
{
var filterValues = attribute.FieldType.Field.GetFilterValues( filterControl, attribute.QualifierValues, Rock.Reporting.FilterMode.SimpleFilter );
var expression = attribute.FieldType.Field.AttributeFilterExpression( attribute.QualifierValues, filterValues, parameterExpression );
if ( expression != null )
{
var attributeValues = attributeValueService
.Queryable()
.Where( v => v.Attribute.Id == attribute.Id );
attributeValues = attributeValues.Where( parameterExpression, expression, null );
qry = qry
.Where( r => attributeValues.Select( v => v.EntityId ).Contains( r.PersonAlias.PersonId ) );
}
}
}
}
// Get all the group member attributes selected to be on grid
groupMemberAttributes = RegistrantFields
.Where( f =>
f.Attribute != null &&
f.FieldSource == RegistrationFieldSource.GroupMemberAttribute )
.Select( f => f.Attribute )
.ToList();
groupMemberAttributesIds = groupMemberAttributes.Select( a => a.Id ).Distinct().ToList();
// Filter query by any configured person attribute filters
if ( groupMemberAttributes != null && groupMemberAttributes.Any() )
{
var attributeValueService = new AttributeValueService( rockContext );
var parameterExpression = attributeValueService.ParameterExpression;
foreach ( var attribute in groupMemberAttributes )
{
var filterControl = phRegistrantFormFieldFilters.FindControl( "filter_" + attribute.Id.ToString() );
if ( filterControl != null )
{
var filterValues = attribute.FieldType.Field.GetFilterValues( filterControl, attribute.QualifierValues, Rock.Reporting.FilterMode.SimpleFilter );
var expression = attribute.FieldType.Field.AttributeFilterExpression( attribute.QualifierValues, filterValues, parameterExpression );
if ( expression != null )
{
var attributeValues = attributeValueService
.Queryable()
.Where( v => v.Attribute.Id == attribute.Id );
attributeValues = attributeValues.Where( parameterExpression, expression, null );
qry = qry
.Where( r => r.GroupMemberId.HasValue &&
attributeValues.Select( v => v.EntityId ).Contains( r.GroupMemberId.Value ) );
}
}
}
}
}
// Sort the query
IOrderedQueryable<RegistrationRegistrant> orderedQry = null;
SortProperty sortProperty = gRegistrants.SortProperty;
if ( sortProperty != null )
{
orderedQry = qry.Sort( sortProperty );
}
else
{
orderedQry = qry
.OrderBy( r => r.PersonAlias.Person.LastName )
.ThenBy( r => r.PersonAlias.Person.NickName );
}
// increase the timeout just in case. A complex filter on the grid might slow things down
rockContext.Database.CommandTimeout = 180;
// Set the grids LinqDataSource which will run query and set results for current page
gRegistrants.SetLinqDataSource<RegistrationRegistrant>( orderedQry );
if ( RegistrantFields != null )
{
// Get the query results for the current page
var currentPageRegistrants = gRegistrants.DataSource as List<RegistrationRegistrant>;
if ( currentPageRegistrants != null )
{
// Get all the registrant ids in current page of query results
var registrantIds = currentPageRegistrants
.Select( r => r.Id )
.Distinct()
.ToList();
// Get all the person ids in current page of query results
var personIds = currentPageRegistrants
.Select( r => r.PersonAlias.PersonId )
.Distinct()
.ToList();
// Get all the group member ids and the group id in current page of query results
var groupMemberIds = new List<int>();
GroupLinks = new Dictionary<int, string>();
foreach ( var groupMember in currentPageRegistrants
.Where( m =>
m.GroupMember != null &&
m.GroupMember.Group != null )
.Select( m => m.GroupMember ) )
{
groupMemberIds.Add( groupMember.Id );
GroupLinks.AddOrIgnore( groupMember.GroupId,
isExporting ? groupMember.Group.Name :
string.Format( "<a href='{0}'>{1}</a>",
LinkedPageUrl( "GroupDetailPage", new Dictionary<string, string> { { "GroupId", groupMember.GroupId.ToString() } } ),
groupMember.Group.Name ) );
}
// If the campus column was selected to be displayed on grid, preload all the people's
// campuses so that the databind does not need to query each row
if ( preloadCampusValues )
{
PersonCampusIds = new Dictionary<int, List<int>>();
Guid familyGroupTypeGuid = Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY.AsGuid();
foreach ( var personCampusList in new GroupMemberService( rockContext )
.Queryable().AsNoTracking()
.Where( m =>
m.Group.GroupType.Guid == familyGroupTypeGuid &&
personIds.Contains( m.PersonId ) )
.GroupBy( m => m.PersonId )
.Select( m => new
{
PersonId = m.Key,
CampusIds = m
.Where( g => g.Group.CampusId.HasValue )
.Select( g => g.Group.CampusId.Value )
.ToList()
} ) )
{
PersonCampusIds.Add( personCampusList.PersonId, personCampusList.CampusIds );
}
}
// If there are any attributes that were selected to be displayed, we're going
// to try and read all attribute values in one query and then put them into a
// custom grid ObjectList property so that the AttributeField columns don't need
// to do the LoadAttributes and querying of values for each row/column
if ( personAttributesIds.Any() || groupMemberAttributesIds.Any() || registrantAttributeIds.Any() )
{
// Query the attribute values for all rows and attributes
var attributeValues = new AttributeValueService( rockContext )
.Queryable( "Attribute" ).AsNoTracking()
.Where( v =>
v.EntityId.HasValue &&
(
(
personAttributesIds.Contains( v.AttributeId ) &&
personIds.Contains( v.EntityId.Value )
) ||
(
groupMemberAttributesIds.Contains( v.AttributeId ) &&
groupMemberIds.Contains( v.EntityId.Value )
) ||
(
registrantAttributeIds.Contains( v.AttributeId ) &&
registrantIds.Contains( v.EntityId.Value )
)
)
)
.ToList();
// Get the attributes to add to each row's object
var attributes = new Dictionary<string, AttributeCache>();
RegistrantFields
.Where( f => f.Attribute != null )
.Select( f => f.Attribute )
.ToList()
.ForEach( a => attributes
.Add( a.Id.ToString() + a.Key, a ) );
// Initialize the grid's object list
gRegistrants.ObjectList = new Dictionary<string, object>();
// Loop through each of the current page's registrants and build an attribute
// field object for storing attributes and the values for each of the registrants
foreach ( var registrant in currentPageRegistrants )
{
// Create a row attribute object
var attributeFieldObject = new AttributeFieldObject();
// Add the attributes to the attribute object
attributeFieldObject.Attributes = attributes;
// Add any person attribute values to object
attributeValues
.Where( v =>
personAttributesIds.Contains( v.AttributeId ) &&
v.EntityId.Value == registrant.PersonAlias.PersonId )
.ToList()
.ForEach( v => attributeFieldObject.AttributeValues
.Add( v.AttributeId.ToString() + v.Attribute.Key, new AttributeValueCache( v ) ) );
// Add any group member attribute values to object
if ( registrant.GroupMemberId.HasValue )
{
attributeValues
.Where( v =>
groupMemberAttributesIds.Contains( v.AttributeId ) &&
v.EntityId.Value == registrant.GroupMemberId.Value )
.ToList()
.ForEach( v => attributeFieldObject.AttributeValues
.Add( v.AttributeId.ToString() + v.Attribute.Key, new AttributeValueCache( v ) ) );
}
// Add any registrant attribute values to object
attributeValues
.Where( v =>
registrantAttributeIds.Contains( v.AttributeId ) &&
v.EntityId.Value == registrant.Id )
.ToList()
.ForEach( v => attributeFieldObject.AttributeValues
.Add( v.AttributeId.ToString() + v.Attribute.Key, new AttributeValueCache( v ) ) );
// Add row attribute object to grid's object list
gRegistrants.ObjectList.Add( registrant.Id.ToString(), attributeFieldObject );
}
}
}
}
gRegistrants.DataBind();
}
}
}