/// <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 );
}
}