/// <summary> /// Binds the group members grid. /// </summary> protected void BindGroupMembersGrid( bool isExporting = false ) { if ( _group != null ) { pnlGroupMembers.Visible = true; lHeading.Text = string.Format( "{0} {1}", _group.GroupType.GroupTerm, _group.GroupType.GroupMemberTerm.Pluralize() ); if ( _group.GroupType.Roles.Any() ) { nbRoleWarning.Visible = false; rFilter.Visible = true; gGroupMembers.Visible = true; var rockContext = new RockContext(); if ( _group != null && _group.RequiredSignatureDocumentTemplateId.HasValue ) { Signers = new SignatureDocumentService( rockContext ) .Queryable().AsNoTracking() .Where( d => d.SignatureDocumentTemplateId == _group.RequiredSignatureDocumentTemplateId.Value && d.Status == SignatureDocumentStatus.Signed && d.BinaryFileId.HasValue && d.AppliesToPersonAlias != null ) .OrderByDescending( d => d.LastStatusDate ) .Select( d => d.AppliesToPersonAlias.PersonId ) .ToList(); } GroupMemberService groupMemberService = new GroupMemberService( rockContext ); var qry = groupMemberService.Queryable( "Person,GroupRole", true ).AsNoTracking() .Where( m => m.GroupId == _group.Id ); // Filter by First Name string firstName = tbFirstName.Text; if ( !string.IsNullOrWhiteSpace( firstName ) ) { qry = qry.Where( m => m.Person.FirstName.StartsWith( firstName ) || m.Person.NickName.StartsWith( firstName ) ); } // Filter by Last Name string lastName = tbLastName.Text; if ( !string.IsNullOrWhiteSpace( lastName ) ) { qry = qry.Where( m => m.Person.LastName.StartsWith( lastName ) ); } // Filter by role var validGroupTypeRoles = _group.GroupType.Roles.Select( r => r.Id ).ToList(); var roles = new List<int>(); foreach ( var roleId in cblRole.SelectedValues.AsIntegerList() ) { if ( validGroupTypeRoles.Contains( roleId ) ) { roles.Add( roleId ); } } if ( roles.Any() ) { qry = qry.Where( m => roles.Contains( m.GroupRoleId ) ); } // Filter by Group Member Status var statuses = new List<GroupMemberStatus>(); foreach ( string status in cblGroupMemberStatus.SelectedValues ) { if ( !string.IsNullOrWhiteSpace( status ) ) { statuses.Add( status.ConvertToEnum<GroupMemberStatus>() ); } } if ( statuses.Any() ) { qry = qry.Where( m => statuses.Contains( m.GroupMemberStatus ) ); } var genders = new List<Gender>(); foreach ( var item in cblGenderFilter.SelectedValues ) { var gender = item.ConvertToEnum<Gender>(); genders.Add( gender ); } if ( genders.Any() ) { qry = qry.Where( m => genders.Contains( m.Person.Gender ) ); } // Filter by Campus if ( cpCampusFilter.SelectedCampusId.HasValue ) { Guid familyGuid = new Guid( Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY ); int campusId = cpCampusFilter.SelectedCampusId.Value; var qryFamilyMembersForCampus = new GroupMemberService( rockContext ).Queryable().Where( a => a.Group.GroupType.Guid == familyGuid && a.Group.CampusId == campusId ); qry = qry.Where( a => qryFamilyMembersForCampus.Any( f => f.PersonId == a.PersonId ) ); } // Filter by Registration var instanceId = ddlRegistration.SelectedValueAsInt(); if ( instanceId.HasValue ) { var registrants = new RegistrationRegistrantService( rockContext ) .Queryable().AsNoTracking() .Where( r => r.Registration != null && r.Registration.RegistrationInstanceId == instanceId.Value && r.PersonAlias != null ) .Select( r => r.PersonAlias.PersonId ); qry = qry.Where( m => registrants.Contains( m.PersonId ) ); } // Filter by signed documents if ( Signers != null ) { if ( ddlSignedDocument.SelectedValue.AsBooleanOrNull() == true ) { qry = qry.Where( m => Signers.Contains( m.PersonId ) ); } else if ( ddlSignedDocument.SelectedValue.AsBooleanOrNull() == false ) { qry = qry.Where( m => !Signers.Contains( m.PersonId ) ); } } // Filter query by any configured attribute filters if ( AvailableAttributes != null && AvailableAttributes.Any() ) { var attributeValueService = new AttributeValueService( rockContext ); var parameterExpression = attributeValueService.ParameterExpression; foreach ( var attribute in AvailableAttributes ) { var filterControl = phAttributeFilters.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( w => attributeValues.Select( v => v.EntityId ).Contains( w.Id ) ); } } } } _inactiveStatus = DefinedValueCache.Read( Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_INACTIVE ); SortProperty sortProperty = gGroupMembers.SortProperty; bool hasGroupRequirements = new GroupRequirementService( rockContext ).Queryable().Where( a => a.GroupId == _group.Id ).Any(); // If there are group requirements that that member doesn't meet, show an icon in the grid bool includeWarnings = false; var groupMemberIdsThatLackGroupRequirements = new GroupService( rockContext ).GroupMembersNotMeetingRequirements( _group.Id, includeWarnings ).Select( a => a.Key.Id ); List<GroupMember> groupMembersList = null; if ( sortProperty != null && sortProperty.Property != "FirstAttended" && sortProperty.Property != "LastAttended" ) { groupMembersList = qry.Sort( sortProperty ).ToList(); } else { groupMembersList = qry.OrderBy( a => a.GroupRole.Order ).ThenBy( a => a.Person.LastName ).ThenBy( a => a.Person.FirstName ).ToList(); } // If there is a required signed document that member has not signed, show an icon in the grid var personIdsThatHaventSigned = new List<int>(); if ( Signers != null ) { var memberPersonIds = groupMembersList.Select( m => m.PersonId ).ToList(); personIdsThatHaventSigned = memberPersonIds.Where( i => !Signers.Contains( i ) ).ToList(); } // Since we're not binding to actual group member list, but are using AttributeField columns, // we need to save the group members into the grid's object list gGroupMembers.ObjectList = new Dictionary<string, object>(); groupMembersList.ForEach( m => gGroupMembers.ObjectList.Add( m.Id.ToString(), m ) ); gGroupMembers.EntityTypeId = EntityTypeCache.Read( Rock.SystemGuid.EntityType.GROUP_MEMBER.AsGuid() ).Id; var homePhoneType = DefinedValueCache.Read( Rock.SystemGuid.DefinedValue.PERSON_PHONE_TYPE_HOME ); var cellPhoneType = DefinedValueCache.Read( Rock.SystemGuid.DefinedValue.PERSON_PHONE_TYPE_MOBILE ); // If exporting to Excel, the selectAll option will be true, and home location should be calculated var homeLocations = new Dictionary<int, Location>(); if ( isExporting ) { foreach ( var m in groupMembersList ) { homeLocations.Add( m.Id, m.Person.GetHomeLocation( rockContext ) ); } } var groupMemberIds = groupMembersList.Select( m => m.Id ).ToList(); // Get all the group members with any associated registrations _groupMembersWithRegistrations = new RegistrationRegistrantService( rockContext ) .Queryable().AsNoTracking() .Where( r => r.Registration != null && r.Registration.RegistrationInstance != null && r.GroupMemberId.HasValue && groupMemberIds.Contains( r.GroupMemberId.Value ) ) .ToList() .GroupBy( r => r.GroupMemberId.Value ) .Select( g => new { GroupMemberId = g.Key, Registrations = g.ToList() .Select( r => new { Id = r.Registration.Id, Name = r.Registration.RegistrationInstance.Name } ).Distinct() .ToDictionary( r => r.Id, r => r.Name ) } ) .ToDictionary( r => r.GroupMemberId, r => r.Registrations ); var registrationField = gGroupMembers.ColumnsOfType<RockTemplateFieldUnselected>().FirstOrDefault(); if ( registrationField != null ) { registrationField.Visible = _groupMembersWithRegistrations.Any(); } var connectionStatusField = gGroupMembers.ColumnsOfType<DefinedValueField>().FirstOrDefault( a => a.DataField == "ConnectionStatusValueId" ); if ( connectionStatusField != null ) { connectionStatusField.Visible = _group.GroupType.ShowConnectionStatus; } string photoFormat = "<div class=\"photo-icon photo-round photo-round-xs pull-left margin-r-sm js-person-popover\" personid=\"{0}\" data-original=\"{1}&w=50\" style=\"background-image: url( '{2}' ); background-size: cover; background-repeat: no-repeat;\"></div>"; var attendanceFirstLast = new Dictionary<int, DateRange>(); bool showAttendance = GetAttributeValue( "ShowAttendance" ).AsBoolean() && _group.GroupType.TakesAttendance; gGroupMembers.ColumnsOfType<DateField>().First( a => a.DataField == "FirstAttended" ).Visible = showAttendance; gGroupMembers.ColumnsOfType<DateField>().First( a => a.DataField == "LastAttended" ).Visible = showAttendance; if ( showAttendance ) { foreach ( var attendance in new AttendanceService( rockContext ) .Queryable().AsNoTracking() .Where( a => a.GroupId.HasValue && a.GroupId.Value == _group.Id && a.DidAttend.HasValue && a.DidAttend.Value ) .GroupBy( a => a.PersonAlias.PersonId ) .Select( g => new { PersonId = g.Key, FirstAttended = g.Min( a => a.StartDateTime ), LastAttended = g.Max( a => a.StartDateTime ) } ) .ToList() ) { attendanceFirstLast.Add( attendance.PersonId, new DateRange( attendance.FirstAttended, attendance.LastAttended ) ); } } bool showDateAdded = GetAttributeValue( "ShowDateAdded" ).AsBoolean(); gGroupMembers.ColumnsOfType<DateField>().First( a => a.DataField == "DateTimeAdded" ).Visible = showDateAdded; var dataSource = groupMembersList.Select( m => new { m.Id, m.Guid, m.PersonId, m.Person.NickName, m.Person.LastName, Name = ( isExporting ? m.Person.LastName + ", " + m.Person.NickName : string.Format( photoFormat, m.PersonId, m.Person.PhotoUrl, ResolveUrl( "~/Assets/Images/person-no-photo-male.svg" ) ) + m.Person.NickName + " " + m.Person.LastName + ( ( hasGroupRequirements && groupMemberIdsThatLackGroupRequirements.Contains( m.Id ) ) ? " <i class='fa fa-exclamation-triangle text-warning'></i>" : string.Empty ) + ( !string.IsNullOrEmpty( m.Note ) ? " <i class='fa fa-file-text-o text-info'></i>" : string.Empty ) + ((personIdsThatHaventSigned.Contains( m.PersonId )) ? " <i class='fa fa-pencil-square-o text-danger'></i>" : string.Empty)), m.Person.BirthDate, m.Person.Age, m.Person.ConnectionStatusValueId, m.DateTimeAdded, FirstAttended = attendanceFirstLast.Where( a => a.Key == m.PersonId ).Select( a => a.Value.Start ).FirstOrDefault(), LastAttended = attendanceFirstLast.Where( a => a.Key == m.PersonId ).Select( a => a.Value.End ).FirstOrDefault(), Email = m.Person.Email, HomePhone = isExporting && homePhoneType != null ? m.Person.PhoneNumbers .Where( p => p.NumberTypeValueId.HasValue && p.NumberTypeValueId.Value == homePhoneType.Id ) .Select( p => p.NumberFormatted ) .FirstOrDefault() : string.Empty, CellPhone = isExporting && cellPhoneType != null ? m.Person.PhoneNumbers .Where( p => p.NumberTypeValueId.HasValue && p.NumberTypeValueId.Value == cellPhoneType.Id ) .Select( p => p.NumberFormatted ) .FirstOrDefault() : string.Empty, HomeAddress = homeLocations.ContainsKey( m.Id ) && homeLocations[m.Id] != null ? homeLocations[m.Id].FormattedAddress : string.Empty, Latitude = homeLocations.ContainsKey( m.Id ) && homeLocations[m.Id] != null ? homeLocations[m.Id].Latitude : (double?)null, Longitude = homeLocations.ContainsKey( m.Id ) && homeLocations[m.Id] != null ? homeLocations[m.Id].Longitude : (double?)null, GroupRole = m.GroupRole.Name, m.GroupMemberStatus, RecordStatusValueId = m.Person.RecordStatusValueId, IsDeceased = m.Person.IsDeceased } ).ToList(); if ( sortProperty != null ) { if ( sortProperty.Property == "FirstAttended" ) { if ( sortProperty.Direction == SortDirection.Descending ) { dataSource = dataSource.OrderByDescending( a => a.FirstAttended ?? DateTime.MinValue ).ToList(); } else { dataSource = dataSource.OrderBy( a => a.FirstAttended ?? DateTime.MinValue ).ToList(); } } if ( sortProperty.Property == "LastAttended" ) { if ( sortProperty.Direction == SortDirection.Descending ) { dataSource = dataSource.OrderByDescending( a => a.LastAttended ?? DateTime.MinValue ).ToList(); } else { dataSource = dataSource.OrderBy( a => a.LastAttended ?? DateTime.MinValue ).ToList(); } } } gGroupMembers.DataSource = dataSource; gGroupMembers.DataBind(); } else { nbRoleWarning.Text = string.Format( "{0} cannot be added to this {1} because the '{2}' group type does not have any roles defined.", _group.GroupType.GroupMemberTerm.Pluralize(), _group.GroupType.GroupTerm, _group.GroupType.Name ); nbRoleWarning.Visible = true; rFilter.Visible = false; gGroupMembers.Visible = false; } } else { pnlGroupMembers.Visible = false; } }