/// <summary>
        /// Binds the attendees grid.
        /// </summary>
        private void BindAttendeesGrid()
        {
            var dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues( drpSlidingDateRange.DelimitedValues );
            if ( dateRange.End == null || dateRange.End > RockDateTime.Now )
            {
                dateRange.End = RockDateTime.Now;
            }

            var rockContext = new RockContext();

            // make a qryPersonAlias so that the generated SQL will be a "WHERE .. IN ()" instead of an OUTER JOIN (which is incredibly slow for this)
            var qryPersonAlias = new PersonAliasService( rockContext ).Queryable();

            var qryAttendance = new AttendanceService( rockContext ).Queryable();

            qryAttendance = qryAttendance.Where( a => a.DidAttend.HasValue && a.DidAttend.Value );
            var groupType = this.GetSelectedTemplateGroupType();
            var qryAllVisits = qryAttendance;
            if ( groupType != null )
            {
                var childGroupTypeIds = new GroupTypeService( rockContext ).GetChildGroupTypes( groupType.Id ).Select( a => a.Id );
                qryAllVisits = qryAttendance.Where( a => childGroupTypeIds.Any( b => b == a.Group.GroupTypeId ) );
            }
            else
            {
                return;
            }

            var groupIdList = new List<int>();
            string groupIds = GetSelectedGroupIds().AsDelimited( "," );
            if ( !string.IsNullOrWhiteSpace( groupIds ) )
            {
                groupIdList = groupIds.Split( ',' ).AsIntegerList();
                qryAttendance = qryAttendance.Where( a => a.GroupId.HasValue && groupIdList.Contains( a.GroupId.Value ) );
            }

            //// If campuses were included, filter attendances by those that have selected campuses
            //// if 'null' is one of the campuses, treat that as a 'CampusId is Null'
            var includeNullCampus = clbCampuses.SelectedValues.Any( a => a.Equals( "null", StringComparison.OrdinalIgnoreCase ) );
            var campusIdList = clbCampuses.SelectedValues.AsIntegerList();

            // remove 0 from the list, just in case it is there
            campusIdList.Remove( 0 );

            if ( campusIdList.Any() )
            {
                if ( includeNullCampus )
                {
                    // show records that have a campusId in the campusIdsList + records that have a null campusId
                    qryAttendance = qryAttendance.Where( a => ( a.CampusId.HasValue && campusIdList.Contains( a.CampusId.Value ) ) || !a.CampusId.HasValue );
                }
                else
                {
                    // only show records that have a campusId in the campusIdList
                    qryAttendance = qryAttendance.Where( a => a.CampusId.HasValue && campusIdList.Contains( a.CampusId.Value ) );
                }
            }
            else if ( includeNullCampus )
            {
                // 'null' was the only campusId in the campusIds parameter, so only show records that have a null CampusId
                qryAttendance = qryAttendance.Where( a => !a.CampusId.HasValue );
            }

            // have the "Missed" query be the same as the qry before the Main date range is applied since it'll have a different date range
            var qryMissed = qryAttendance;

            if ( dateRange.Start.HasValue )
            {
                qryAttendance = qryAttendance.Where( a => a.StartDateTime >= dateRange.Start.Value );
            }

            if ( dateRange.End.HasValue )
            {
                qryAttendance = qryAttendance.Where( a => a.StartDateTime < dateRange.End.Value );
            }

            // we want to get the first 2 visits at a minimum so we can show the date in the grid
            int nthVisitsTake = 2;
            int? byNthVisit = null;

            if ( radByVisit.Checked )
            {
                // If we are filtering by nth visit, we might want to get up to first 5
                byNthVisit = ddlNthVisit.SelectedValue.AsIntegerOrNull();
                if ( byNthVisit.HasValue && byNthVisit > 2 )
                {
                    nthVisitsTake = byNthVisit.Value;
                }
            }

            ChartGroupBy groupBy = hfGroupBy.Value.ConvertToEnumOrNull<ChartGroupBy>() ?? ChartGroupBy.Week;

            IQueryable<PersonWithSummary> qryByPersonWithSummary = null;

            if ( byNthVisit.HasValue && byNthVisit.Value == 0 )
            {
                // Show members of the selected groups that did not attend at all during selected date range

                // Get all the person ids that did attend
                var attendeePersonIds = qryAttendance.Select( a => a.PersonAlias.PersonId );

                // Get all the active members of the selected groups who have no attendance within selected date range and campus
                qryByPersonWithSummary = new GroupMemberService( rockContext )
                    .Queryable().AsNoTracking()
                    .Where( m =>
                        groupIdList.Contains( m.GroupId ) &&
                        !attendeePersonIds.Contains( m.PersonId ) &&
                        m.GroupMemberStatus == GroupMemberStatus.Active )
                    .Select( m => new PersonWithSummary
                    {
                        PersonId = m.PersonId,
                        FirstVisits = new DateTime[] { }.AsQueryable(),
                        LastVisit = new AttendancePersonAlias(),
                        AttendanceSummary = new DateTime[] { }.AsQueryable()
                    } );
            }
            else
            {
                var qryAttendanceWithSummaryDateTime = qryAttendance.GetAttendanceWithSummaryDateTime( groupBy );
                var qryGroup = new GroupService( rockContext ).Queryable();

                var qryJoinPerson = qryAttendance.Join(
                    qryPersonAlias,
                    k1 => k1.PersonAliasId,
                    k2 => k2.Id,
                    ( a, pa ) => new
                    {
                        CampusId = a.CampusId,
                        GroupId = a.GroupId,
                        ScheduleId = a.ScheduleId,
                        StartDateTime = a.StartDateTime,
                        PersonAliasId = pa.Id,
                        PersonAliasPersonId = pa.PersonId
                    } );

                var qryJoinFinal = qryJoinPerson.Join(
                    qryGroup,
                    k1 => k1.GroupId,
                    k2 => k2.Id,
                    ( a, g ) => new AttendancePersonAlias
                    {
                        CampusId = a.CampusId,
                        GroupId = a.GroupId,
                        GroupName = g.Name,
                        ScheduleId = a.ScheduleId,
                        StartDateTime = a.StartDateTime,
                        PersonAliasId = a.PersonAliasId,
                        PersonAliasPersonId = a.PersonAliasPersonId
                    } );

                var qryByPerson = qryJoinFinal.GroupBy( a => a.PersonAliasPersonId ).Select( a => new
                {
                    PersonId = a.Key,
                    Attendances = a
                } );

                int? attendedMinCount = null;
                int? attendedMissedCount = null;
                DateRange attendedMissedDateRange = new DateRange();
                if ( radByPattern.Checked )
                {
                    attendedMinCount = tbPatternXTimes.Text.AsIntegerOrNull();
                    if ( cbPatternAndMissed.Checked )
                    {
                        attendedMissedCount = tbPatternMissedXTimes.Text.AsIntegerOrNull();
                        attendedMissedDateRange = new DateRange( drpPatternDateRange.LowerValue, drpPatternDateRange.UpperValue );
                        if ( !attendedMissedDateRange.Start.HasValue || !attendedMissedDateRange.End.HasValue )
                        {
                            nbMissedDateRangeRequired.Visible = true;
                            return;
                        }
                    }
                }

                nbMissedDateRangeRequired.Visible = false;

                // get either the first 2 visits or the first 5 visits (using a const take of 2 or 5 vs a variable to help the SQL optimizer)
                qryByPersonWithSummary = qryByPerson.Select( a => new PersonWithSummary
                {
                    PersonId = a.PersonId,
                    FirstVisits = qryAllVisits.Where( b => qryPersonAlias.Where( pa => pa.PersonId == a.PersonId ).Any( pa => pa.Id == b.PersonAliasId ) ).Select( s => s.StartDateTime ).OrderBy( x => x ).Take( 2 ),
                    LastVisit = a.Attendances.OrderByDescending( x => x.StartDateTime ).FirstOrDefault(),
                    AttendanceSummary = qryAttendanceWithSummaryDateTime.Where( x => qryPersonAlias.Where( pa => pa.PersonId == a.PersonId ).Any( pa => pa.Id == x.Attendance.PersonAliasId ) ).GroupBy( g => g.SummaryDateTime ).Select( s => s.Key )
                } );

                if ( nthVisitsTake > 2 )
                {
                    qryByPersonWithSummary = qryByPerson.Select( a => new PersonWithSummary
                    {
                        PersonId = a.PersonId,
                        FirstVisits = qryAllVisits.Where( b => qryPersonAlias.Where( pa => pa.PersonId == a.PersonId ).Any( pa => pa.Id == b.PersonAliasId ) ).Select( s => s.StartDateTime ).OrderBy( x => x ).Take( 5 ),
                        LastVisit = a.Attendances.OrderByDescending( x => x.StartDateTime ).FirstOrDefault(),
                        AttendanceSummary = qryAttendanceWithSummaryDateTime.Where( x => qryPersonAlias.Where( pa => pa.PersonId == a.PersonId ).Any( pa => pa.Id == x.Attendance.PersonAliasId ) ).GroupBy( g => g.SummaryDateTime ).Select( s => s.Key )
                    } );
                }

                if ( byNthVisit.HasValue )
                {
                    // only return attendees where their nth visit is within the selected daterange
                    int skipCount = byNthVisit.Value - 1;
                    qryByPersonWithSummary = qryByPersonWithSummary.Where( a => a.FirstVisits.OrderBy( x => x ).Skip( skipCount ).Take( 1 ).Any( d => d >= dateRange.Start && d < dateRange.End ) );
                }

                if ( attendedMinCount.HasValue )
                {
                    qryByPersonWithSummary = qryByPersonWithSummary.Where( a => a.AttendanceSummary.Count() >= attendedMinCount );
                }

                if ( attendedMissedCount.HasValue )
                {
                    if ( attendedMissedDateRange.Start.HasValue && attendedMissedDateRange.End.HasValue )
                    {
                        var attendedMissedPossible = GetPossibleAttendancesForDateRange( attendedMissedDateRange, groupBy );
                        int attendedMissedPossibleCount = attendedMissedPossible.Count();

                        qryMissed = qryMissed.Where( a => a.StartDateTime >= attendedMissedDateRange.Start.Value && a.StartDateTime < attendedMissedDateRange.End.Value );
                        var qryMissedAttendanceByPersonAndSummary = qryMissed.GetAttendanceWithSummaryDateTime( groupBy )
                            .GroupBy( g1 => new { g1.SummaryDateTime, g1.Attendance.PersonAlias.PersonId } )
                            .GroupBy( a => a.Key.PersonId )
                            .Select( a => new
                            {
                                PersonId = a.Key,
                                AttendanceCount = a.Count()
                            } );

                        var qryMissedByPerson = qryMissedAttendanceByPersonAndSummary
                            .Where( x => ( attendedMissedPossibleCount - x.AttendanceCount ) >= attendedMissedCount );

                        // filter to only people that missed at least X weeks/months/years between specified missed date range
                        qryByPersonWithSummary = qryByPersonWithSummary.Where( a => qryMissedByPerson.Any( b => b.PersonId == a.PersonId ) );
                    }
                }
            }

            var personService = new PersonService( rockContext );

            // Filter by dataview
            var dataViewId = dvpDataView.SelectedValueAsInt();
            if ( dataViewId.HasValue )
            {
                var dataView = new DataViewService( _rockContext ).Get( dataViewId.Value );
                if ( dataView != null )
                {
                    var errorMessages = new List<string>();
                    ParameterExpression paramExpression = personService.ParameterExpression;
                    Expression whereExpression = dataView.GetExpression( personService, paramExpression, out errorMessages );

                    SortProperty sort = null;
                    var dataViewPersonIdQry = personService
                        .Queryable().AsNoTracking()
                        .Where( paramExpression, whereExpression, sort )
                        .Select( p => p.Id );

                    qryByPersonWithSummary = qryByPersonWithSummary.Where( a => dataViewPersonIdQry.Contains( a.PersonId ) );
                }
            }

            // declare the qryResult that we'll use in case they didn't choose IncludeParents or IncludeChildren (and the Anonymous Type will also work if we do include parents or children)
            var qryPerson = personService.Queryable();

            var qryResult = qryByPersonWithSummary.Join(
                    qryPerson,
                    a => a.PersonId,
                    p => p.Id,
                    ( a, p ) => new
                        {
                            a.PersonId,
                            ParentId = (int?)null,
                            ChildId = (int?)null,
                            Person = p,
                            Parent = (Person)null,
                            Child = (Person)null,
                            a.FirstVisits,
                            a.LastVisit,
                            p.PhoneNumbers,
                            a.AttendanceSummary
                        } );

            var includeParents = hfViewBy.Value.ConvertToEnumOrNull<ViewBy>().GetValueOrDefault( ViewBy.Attendees ) == ViewBy.ParentsOfAttendees;
            var includeChildren = hfViewBy.Value.ConvertToEnumOrNull<ViewBy>().GetValueOrDefault( ViewBy.Attendees ) == ViewBy.ChildrenOfAttendees;

            // if Including Parents, join with qryChildWithParent instead of qryPerson
            if ( includeParents )
            {
                var qryChildWithParent = new PersonService( rockContext ).GetChildWithParent();
                qryResult = qryByPersonWithSummary.Join(
                    qryChildWithParent,
                    a => a.PersonId,
                    p => p.Child.Id,
                    ( a, p ) => new
                    {
                        a.PersonId,
                        ParentId = (int?)p.Parent.Id,
                        ChildId = (int?)null,
                        Person = p.Child,
                        Parent = p.Parent,
                        Child = (Person)null,
                        a.FirstVisits,
                        a.LastVisit,
                        p.Parent.PhoneNumbers,
                        a.AttendanceSummary
                    } );
            }

            if ( includeChildren )
            {
                var qryParentWithChildren = new PersonService( rockContext ).GetParentWithChild();
                qryResult = qryByPersonWithSummary.Join(
                    qryParentWithChildren,
                    a => a.PersonId,
                    p => p.Parent.Id,
                    ( a, p ) => new
                    {
                        a.PersonId,
                        ParentId = (int?)null,
                        ChildId = (int?)p.Child.Id,
                        Person = p.Parent,
                        Parent = (Person)null,
                        Child = p.Child,
                        a.FirstVisits,
                        a.LastVisit,
                        p.Child.PhoneNumbers,
                        a.AttendanceSummary
                    } );
            }

            var parentField = gAttendeesAttendance.Columns.OfType<PersonField>().FirstOrDefault( a => a.HeaderText == "Parent" );
            if ( parentField != null )
            {
                parentField.Visible = includeParents;
            }

            var parentEmailField = gAttendeesAttendance.Columns.OfType<RockBoundField>().FirstOrDefault( a => a.HeaderText == "Parent Email" );
            if ( parentEmailField != null )
            {
                parentEmailField.ExcelExportBehavior = includeParents ? ExcelExportBehavior.AlwaysInclude : ExcelExportBehavior.NeverInclude;
            }

            var childField = gAttendeesAttendance.Columns.OfType<PersonField>().FirstOrDefault( a => a.HeaderText == "Child" );
            if ( childField != null )
            {
                childField.Visible = includeChildren;
            }

            var childEmailField = gAttendeesAttendance.Columns.OfType<RockBoundField>().FirstOrDefault( a => a.HeaderText == "Child Email" );
            if ( childEmailField != null )
            {
                childEmailField.ExcelExportBehavior = includeChildren ? ExcelExportBehavior.AlwaysInclude : ExcelExportBehavior.NeverInclude;
            }

            SortProperty sortProperty = gAttendeesAttendance.SortProperty;

            if ( sortProperty != null )
            {
                if ( sortProperty.Property == "AttendanceSummary.Count" )
                {
                    if ( sortProperty.Direction == SortDirection.Descending )
                    {
                        qryResult = qryResult.OrderByDescending( a => a.AttendanceSummary.Count() );
                    }
                    else
                    {
                        qryResult = qryResult.OrderBy( a => a.AttendanceSummary.Count() );
                    }
                }
                else if ( sortProperty.Property == "FirstVisit.StartDateTime" )
                {
                    if ( sortProperty.Direction == SortDirection.Descending )
                    {
                        qryResult = qryResult.OrderByDescending( a => a.FirstVisits.Min() );
                    }
                    else
                    {
                        qryResult = qryResult.OrderBy( a => a.FirstVisits.Min() );
                    }
                }
                else
                {
                    qryResult = qryResult.Sort( sortProperty );
                }
            }
            else
            {
                qryResult = qryResult.OrderBy( a => a.Person.LastName ).ThenBy( a => a.Person.NickName );
            }

            var attendancePercentField = gAttendeesAttendance.Columns.OfType<RockTemplateField>().First( a => a.HeaderText.EndsWith( "Attendance %" ) );
            attendancePercentField.HeaderText = string.Format( "{0}ly Attendance %", groupBy.ConvertToString() );

            // Calculate all the possible attendance summary dates
            UpdatePossibleAttendances( dateRange, groupBy );

            // pre-load the schedule names since FriendlyScheduleText requires building the ICal object, etc
            _scheduleNameLookup = new ScheduleService( rockContext ).Queryable()
                .ToList()
                .ToDictionary( k => k.Id, v => v.FriendlyScheduleText );

            if ( includeParents )
            {
                gAttendeesAttendance.PersonIdField = "ParentId";
                gAttendeesAttendance.DataKeyNames = new string[] { "ParentId", "PersonId" };
            }
            else if ( includeChildren )
            {
                gAttendeesAttendance.PersonIdField = "ChildId";
                gAttendeesAttendance.DataKeyNames = new string[] { "ChildId", "PersonId" };
            }
            else
            {
                gAttendeesAttendance.PersonIdField = "PersonId";
                gAttendeesAttendance.DataKeyNames = new string[] { "PersonId" };
            }

            // Create the dynamic attendance grid columns as needed
            CreateDynamicAttendanceGridColumns();

            try
            {
                nbAttendeesError.Visible = false;

                // increase the timeout from 30 to 90. The Query can be slow if SQL hasn't calculated the Query Plan for the query yet.
                // Sometimes, most of the time consumption is figuring out the Query Plan, but after it figures it out, it caches it so that the next time it'll be much faster
                rockContext.Database.CommandTimeout = 90;
                gAttendeesAttendance.SetLinqDataSource( qryResult.AsNoTracking() );

                gAttendeesAttendance.DataBind();
            }
            catch ( Exception exception )
            {
                LogAndShowException( exception );
            }
        }