/// <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 );
}
}
/// <summary>
/// Gets the possible attendances for the date range.
/// </summary>
/// <param name="dateRange">The date range.</param>
/// <param name="attendanceGroupBy">The attendance group by type.</param>
/// <returns></returns>
public List<DateTime> GetPossibleAttendancesForDateRange( DateRange dateRange, ChartGroupBy attendanceGroupBy )
{
TimeSpan dateRangeSpan = dateRange.End.Value - dateRange.Start.Value;
var result = new List<DateTime>();
if ( attendanceGroupBy == ChartGroupBy.Week )
{
var endOfFirstWeek = dateRange.Start.Value.EndOfWeek( RockDateTime.FirstDayOfWeek );
var endOfLastWeek = dateRange.End.Value.EndOfWeek( RockDateTime.FirstDayOfWeek );
var weekEndDate = endOfFirstWeek;
while ( weekEndDate <= endOfLastWeek )
{
// Weeks are summarized as the last day of the "Rock" week (Sunday)
result.Add( weekEndDate );
weekEndDate = weekEndDate.AddDays( 7 );
}
}
else if ( attendanceGroupBy == ChartGroupBy.Month )
{
var endOfFirstMonth = dateRange.Start.Value.AddDays( -( dateRange.Start.Value.Day - 1 ) ).AddMonths( 1 ).AddDays( -1 );
var endOfLastMonth = dateRange.End.Value.AddDays( -( dateRange.End.Value.Day - 1 ) ).AddMonths( 1 ).AddDays( -1 );
//// Months are summarized as the First Day of the month: For example, 5/1/2015 would include everything from 5/1/2015 - 5/31/2015 (inclusive)
var monthStartDate = new DateTime( endOfFirstMonth.Year, endOfFirstMonth.Month, 1 );
while ( monthStartDate <= endOfLastMonth )
{
result.Add( monthStartDate );
monthStartDate = monthStartDate.AddMonths( 1 );
}
}
else if ( attendanceGroupBy == ChartGroupBy.Year )
{
var endOfFirstYear = new DateTime( dateRange.Start.Value.Year, 1, 1 ).AddYears( 1 ).AddDays( -1 );
var endOfLastYear = new DateTime( dateRange.End.Value.Year, 1, 1 ).AddYears( 1 ).AddDays( -1 );
//// Years are summarized as the First Day of the year: For example, 1/1/2015 would include everything from 1/1/2015 - 12/31/2015 (inclusive)
var yearStartDate = new DateTime( endOfFirstYear.Year, 1, 1 );
while ( yearStartDate <= endOfLastYear )
{
result.Add( yearStartDate );
yearStartDate = yearStartDate.AddYears( 1 );
}
}
// only include current and previous dates
var currentDateTime = RockDateTime.Now;
result = result.Where( a => a <= currentDateTime.Date ).ToList();
return result;
}
/// <summary>
/// Updates the possible attendance summary dates
/// </summary>
/// <param name="dateRange">The date range.</param>
/// <param name="attendanceGroupBy">The attendance group by.</param>
public void UpdatePossibleAttendances( DateRange dateRange, ChartGroupBy attendanceGroupBy )
{
_possibleAttendances = GetPossibleAttendancesForDateRange( dateRange, attendanceGroupBy );
}
/// <summary>
/// Binds the attendees grid.
/// </summary>
private void BindAttendeesGrid( bool isExporting = false )
{
// Get Group Type filter
var groupTypes = this.GetSelectedGroupTypes();
if ( groupTypes == null || !groupTypes.Any() )
{
return;
}
var groupTypeIdList = groupTypes.Select( t => t.Id ).ToList();
// Get the daterange filter
var dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues( drpSlidingDateRange.DelimitedValues );
if ( dateRange.End == null )
{
dateRange.End = RockDateTime.Now;
}
var start = dateRange.Start;
var end = dateRange.End;
// Get the group filter
var groupIdList = new List<int>();
string groupIds = GetSelectedGroupIds().AsDelimited( "," );
if ( !string.IsNullOrWhiteSpace( groupIds ) )
{
groupIdList = groupIds.Split( ',' ).AsIntegerList();
}
// 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();
campusIdList.Remove( 0 ); // remove 0 from the list, just in case it is there
if ( !includeNullCampus && !campusIdList.Any() )
{
campusIdList = null;
}
// If schedules were included, filter attendance by those that have the selected schedules
var scheduleIdList = spSchedules.SelectedValues.AsIntegerList();
scheduleIdList.Remove( 0 );
if ( !scheduleIdList.Any() )
{
scheduleIdList = null;
}
// we want to get the first 2 visits at a minimum so we can show the dates 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;
}
}
bool showNonAttenders = byNthVisit.HasValue && byNthVisit.Value == 0;
// Get any attendance pattern filters
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;
// Determine how dates shold be grouped
ChartGroupBy groupBy = hfGroupBy.Value.ConvertToEnumOrNull<ChartGroupBy>() ?? ChartGroupBy.Week;
// Determine if parents or children are being included with results
var includeParents = hfViewBy.Value.ConvertToEnumOrNull<ViewBy>().GetValueOrDefault( ViewBy.Attendees ) == ViewBy.ParentsOfAttendees;
var includeChildren = hfViewBy.Value.ConvertToEnumOrNull<ViewBy>().GetValueOrDefault( ViewBy.Attendees ) == ViewBy.ChildrenOfAttendees;
// Atttendance results
var allAttendeeVisits = new Dictionary<int, AttendeeVisits>();
var allResults = new List<AttendeeResult>();
// Collection of async queries to run before assembling data
var qryTasks = new List<Task>();
DataTable dtAttendeeLastAttendance = null;
DataTable dtAttendees = null;
DataTable dtAttendeeFirstDates = null;
List<int> personIdsWhoDidNotMiss = null;
if ( !showNonAttenders )
{
// Call the stored procedure to get all the person ids and their attendance dates for anyone
// whith attendance that matches the selected criteria.
qryTasks.Add( Task.Run( () =>
{
DataTable dtAttendeeDates = AttendanceService.GetAttendanceAnalyticsAttendeeDates(
groupIdList, start, end, campusIdList, includeNullCampus, scheduleIdList ).Tables[0];
foreach ( DataRow row in dtAttendeeDates.Rows )
{
int personId = (int)row["PersonId"];
allAttendeeVisits.AddOrIgnore( personId, new AttendeeVisits() );
var result = allAttendeeVisits[personId];
result.PersonId = personId;
DateTime summaryDate = DateTime.MinValue;
switch ( groupBy )
{
case ChartGroupBy.Week: summaryDate = (DateTime)row["SundayDate"]; break;
case ChartGroupBy.Month: summaryDate = (DateTime)row["MonthDate"]; break;
case ChartGroupBy.Year: summaryDate = (DateTime)row["YearDate"]; break;
}
if ( !result.AttendanceSummary.Contains( summaryDate ) )
{
result.AttendanceSummary.Add( summaryDate );
}
}
} ) );
// Call the stored procedure to get the last attendance
qryTasks.Add( Task.Run( () =>
{
dtAttendeeLastAttendance = AttendanceService.GetAttendanceAnalyticsAttendeeLastAttendance(
groupIdList, start, end, campusIdList, includeNullCampus, scheduleIdList ).Tables[0];
} ) );
// Call the stored procedure to get the names/demographic info for attendess
qryTasks.Add( Task.Run( () =>
{
dtAttendees = AttendanceService.GetAttendanceAnalyticsAttendees(
groupIdList, start, end, campusIdList, includeNullCampus, scheduleIdList, includeParents, includeChildren ).Tables[0];
} ) );
// If checking for missed attendance, get the people who missed that number of dates during the missed date range
if ( attendedMissedCount.HasValue &&
attendedMissedDateRange.Start.HasValue &&
attendedMissedDateRange.End.HasValue )
{
qryTasks.Add( Task.Run( () =>
{
personIdsWhoDidNotMiss = new List<int>();
DataTable dtAttendeeDatesMissed = AttendanceService.GetAttendanceAnalyticsAttendeeDates(
groupIdList, attendedMissedDateRange.Start.Value, attendedMissedDateRange.End.Value,
campusIdList, includeNullCampus, scheduleIdList ).Tables[0];
var missedResults = new Dictionary<int, AttendeeResult>();
foreach ( DataRow row in dtAttendeeDatesMissed.Rows )
{
int personId = (int)row["PersonId"];
missedResults.AddOrIgnore( personId, new AttendeeResult() );
var missedResult = missedResults[personId];
missedResult.PersonId = personId;
DateTime summaryDate = DateTime.MinValue;
switch ( groupBy )
{
case ChartGroupBy.Week: summaryDate = (DateTime)row["SundayDate"]; break;
case ChartGroupBy.Month: summaryDate = (DateTime)row["MonthDate"]; break;
case ChartGroupBy.Year: summaryDate = (DateTime)row["YearDate"]; break;
}
if ( !missedResult.AttendanceSummary.Contains( summaryDate ) )
{
missedResult.AttendanceSummary.Add( summaryDate );
}
}
var missedPossibleDates = GetPossibleAttendancesForDateRange( attendedMissedDateRange, groupBy );
int missedPossibleCount = missedPossibleDates.Count();
personIdsWhoDidNotMiss = missedResults
.Where( m => missedPossibleCount - m.Value.AttendanceSummary.Count < attendedMissedCount.Value )
.Select( m => m.Key )
.ToList();
} ) );
}
// Call the stored procedure to get the first five dates that any person attended this group type
qryTasks.Add( Task.Run( () =>
{
dtAttendeeFirstDates = AttendanceService.GetAttendanceAnalyticsAttendeeFirstDates(
groupTypeIdList, groupIdList, start, end, campusIdList, includeNullCampus, scheduleIdList ).Tables[0];
} ) );
}
else
{
qryTasks.Add( Task.Run( () =>
{
DataSet ds = AttendanceService.GetAttendanceAnalyticsNonAttendees(
groupTypeIdList, groupIdList, start, end, campusIdList, includeNullCampus, scheduleIdList, includeParents, includeChildren );
DataTable dtNonAttenders = ds.Tables[0];
dtAttendeeFirstDates = ds.Tables[1];
dtAttendeeLastAttendance = ds.Tables[2];
foreach ( DataRow row in dtNonAttenders.Rows )
{
int personId = (int)row["Id"];
var result = new AttendeeResult();
result.PersonId = personId;
var person = new PersonInfo();
person.NickName = row["NickName"].ToString();
person.LastName = row["LastName"].ToString();
person.Email = row["Email"].ToString();
person.Birthdate = row["BirthDate"] as DateTime?;
person.Age = Person.GetAge( person.Birthdate );
person.ConnectionStatusValueId = row["ConnectionStatusValueId"] as int?;
result.Person = person;
if ( includeParents )
{
result.ParentId = (int)row["ParentId"];
var parent = new PersonInfo();
parent.NickName = row["ParentNickName"].ToString();
parent.LastName = row["ParentLastName"].ToString();
parent.Email = row["ParentEmail"].ToString();
parent.Birthdate = row["ParentBirthDate"] as DateTime?;
parent.Age = Person.GetAge( parent.Birthdate );
result.Parent = parent;
}
if ( includeChildren )
{
var child = new PersonInfo();
result.ChildId = (int)row["ChildId"];
child.NickName = row["ChildNickName"].ToString();
child.LastName = row["ChildLastName"].ToString();
child.Email = row["ChildEmail"].ToString();
child.Birthdate = row["ChildBirthDate"] as DateTime?;
child.Age = Person.GetAge( child.Birthdate );
result.Child = child;
}
allResults.Add( result );
}
} ) );
}
// If a dataview filter was included, find the people who match that criteria
List<int> dataViewPersonIds = null;
qryTasks.Add( Task.Run( () =>
{
var dataViewId = dvpDataView.SelectedValueAsInt();
if ( dataViewId.HasValue )
{
dataViewPersonIds = new List<int>();
var dataView = new DataViewService( _rockContext ).Get( dataViewId.Value );
if ( dataView != null )
{
var errorMessages = new List<string>();
var dvPersonService = new PersonService( _rockContext );
ParameterExpression paramExpression = dvPersonService.ParameterExpression;
Expression whereExpression = dataView.GetExpression( dvPersonService, paramExpression, out errorMessages );
SortProperty sort = null;
var dataViewPersonIdQry = dvPersonService
.Queryable().AsNoTracking()
.Where( paramExpression, whereExpression, sort )
.Select( p => p.Id );
dataViewPersonIds = dataViewPersonIdQry.ToList();
}
}
} ) );
// Wait for all the queries to finish
Task.WaitAll( qryTasks.ToArray() );
if ( !showNonAttenders )
{
var attendees = allAttendeeVisits.AsQueryable();
// If dataview filter was included remove anyone not in that dataview
if ( dataViewPersonIds != null )
{
attendees = attendees.Where( p => dataViewPersonIds.Contains( p.Key ) );
}
// If filter for number missed was included, remove anyone who did not match that filter
if ( personIdsWhoDidNotMiss != null )
{
attendees = attendees.Where( p => !personIdsWhoDidNotMiss.Contains( p.Key ) );
}
// If filtering by minimum times attended
if ( attendedMinCount.HasValue )
{
attendees = attendees.Where( p => p.Value.AttendanceSummary.Count() >= attendedMinCount );
}
// Force filter application
allAttendeeVisits = attendees.ToDictionary( k => k.Key, v => v.Value );
// Add the First Visit information
foreach ( DataRow row in dtAttendeeFirstDates.Rows )
{
int personId = (int)row["PersonId"];
if ( allAttendeeVisits.ContainsKey( personId ) )
{
allAttendeeVisits[personId].FirstVisits.Add( (DateTime)row["StartDate"] );
}
}
// If filtering based on visit time, only include those who visited the selected time during the date range
if ( byNthVisit.HasValue )
{
int skipCount = byNthVisit.Value - 1;
allAttendeeVisits = allAttendeeVisits
.Where( p => p.Value.FirstVisits.Skip( skipCount ).Take( 1 ).Any( d => d >= start && d < end ) )
.ToDictionary( k => k.Key, v => v.Value );
}
// Add the Last Attended information
if ( dtAttendeeLastAttendance != null )
{
foreach ( DataRow row in dtAttendeeLastAttendance.Rows )
{
int personId = (int)row["PersonId"];
if ( allAttendeeVisits.ContainsKey( personId ) )
{
var result = allAttendeeVisits[personId];
if ( result.LastVisit == null )
{
var lastAttendance = new PersonLastAttendance();
lastAttendance.CampusId = row["CampusId"] as int?;
lastAttendance.GroupId = row["GroupId"] as int?;
lastAttendance.GroupName = row["GroupName"].ToString();
lastAttendance.RoleName = row["RoleName"].ToString();
lastAttendance.InGroup = !string.IsNullOrWhiteSpace( lastAttendance.RoleName );
lastAttendance.ScheduleId = row["ScheduleId"] as int?;
lastAttendance.StartDateTime = (DateTime)row["StartDateTime"];
lastAttendance.LocationId = row["LocationId"] as int?;
lastAttendance.LocationName = row["LocationName"].ToString();
result.LastVisit = lastAttendance;
}
}
}
}
// Add the Demographic information
if ( dtAttendees != null )
{
var newResults = new Dictionary<int, AttendeeResult>();
foreach ( DataRow row in dtAttendees.Rows )
{
int personId = (int)row["Id"];
if ( allAttendeeVisits.ContainsKey( personId ) )
{
var result = new AttendeeResult( allAttendeeVisits[personId] );
var person = new PersonInfo();
person.NickName = row["NickName"].ToString();
person.LastName = row["LastName"].ToString();
person.Email = row["Email"].ToString();
person.Birthdate = row["BirthDate"] as DateTime?;
person.Age = Person.GetAge( person.Birthdate );
person.ConnectionStatusValueId = row["ConnectionStatusValueId"] as int?;
result.Person = person;
if ( includeParents )
{
result.ParentId = (int)row["ParentId"];
var parent = new PersonInfo();
parent.NickName = row["ParentNickName"].ToString();
parent.LastName = row["ParentLastName"].ToString();
parent.Email = row["ParentEmail"].ToString();
parent.Birthdate = row["ParentBirthDate"] as DateTime?;
parent.Age = Person.GetAge( parent.Birthdate );
result.Parent = parent;
}
if ( includeChildren )
{
var child = new PersonInfo();
result.ChildId = (int)row["ChildId"];
child.NickName = row["ChildNickName"].ToString();
child.LastName = row["ChildLastName"].ToString();
child.Email = row["ChildEmail"].ToString();
child.Birthdate = row["ChildBirthDate"] as DateTime?;
child.Age = Person.GetAge( child.Birthdate );
result.Child = child;
}
allResults.Add( result );
}
}
}
}
else
{
// If dataview filter was included remove anyone not in that dataview
if ( dataViewPersonIds != null )
{
allResults = allResults
.Where( p => dataViewPersonIds.Contains( p.PersonId ) )
.ToList();
}
// Add the first visit dates for people
foreach ( DataRow row in dtAttendeeFirstDates.Rows )
{
int personId = (int)row["PersonId"];
foreach ( var result in allResults.Where( r => r.PersonId == personId ) )
{
result.FirstVisits.Add( (DateTime)row["StartDate"] );
}
}
// Add the Last Attended information
if ( dtAttendeeLastAttendance != null )
{
foreach ( DataRow row in dtAttendeeLastAttendance.Rows )
{
int personId = (int)row["PersonId"];
foreach ( var result in allResults.Where( r => r.PersonId == personId ) )
{
if ( result.LastVisit == null )
{
var lastAttendance = new PersonLastAttendance();
lastAttendance.CampusId = row["CampusId"] as int?;
lastAttendance.GroupId = row["GroupId"] as int?;
lastAttendance.GroupName = row["GroupName"].ToString();
lastAttendance.RoleName = row["RoleName"].ToString();
lastAttendance.InGroup = !string.IsNullOrWhiteSpace( lastAttendance.RoleName );
lastAttendance.ScheduleId = row["ScheduleId"] as int?;
lastAttendance.StartDateTime = (DateTime)row["StartDateTime"];
lastAttendance.LocationId = row["LocationId"] as int?;
lastAttendance.LocationName = row["LocationName"].ToString();
result.LastVisit = lastAttendance;
}
}
}
}
}
// Begin formatting the columns
var qryResult = allResults.AsQueryable();
var personUrlFormatString = ( (RockPage)this.Page ).ResolveRockUrl( "~/Person/{0}" );
var personHyperLinkField = gAttendeesAttendance.Columns.OfType<HyperLinkField>().FirstOrDefault( a => a.HeaderText == "Name" );
if ( personHyperLinkField != null )
{
personHyperLinkField.DataNavigateUrlFormatString = personUrlFormatString;
}
var parentHyperLinkField = gAttendeesAttendance.Columns.OfType<HyperLinkField>().FirstOrDefault( a => a.HeaderText == "Parent" );
if ( parentHyperLinkField != null )
{
parentHyperLinkField.Visible = includeParents;
parentHyperLinkField.DataNavigateUrlFormatString = personUrlFormatString;
}
var parentField = gAttendeesAttendance.Columns.OfType<RockBoundField>().FirstOrDefault( a => a.HeaderText == "Parent" );
if ( parentField != null )
{
parentField.ExcelExportBehavior = includeParents ? ExcelExportBehavior.AlwaysInclude : ExcelExportBehavior.NeverInclude;
}
var parentEmailField = gAttendeesAttendance.Columns.OfType<RockBoundField>().FirstOrDefault( a => a.HeaderText == "Parent Email" );
if ( parentEmailField != null )
{
parentEmailField.ExcelExportBehavior = includeParents ? ExcelExportBehavior.AlwaysInclude : ExcelExportBehavior.NeverInclude;
}
var childHyperLinkField = gAttendeesAttendance.Columns.OfType<HyperLinkField>().FirstOrDefault( a => a.HeaderText == "Child" );
if ( childHyperLinkField != null )
{
childHyperLinkField.Visible = includeChildren;
childHyperLinkField.DataNavigateUrlFormatString = personUrlFormatString;
}
var childfield = gAttendeesAttendance.Columns.OfType<RockBoundField>().FirstOrDefault( a => a.HeaderText == "Child" );
if ( childfield != null )
{
childfield.ExcelExportBehavior = includeChildren ? ExcelExportBehavior.AlwaysInclude : ExcelExportBehavior.NeverInclude;
}
var childEmailField = gAttendeesAttendance.Columns.OfType<RockBoundField>().FirstOrDefault( a => a.HeaderText == "Child Email" );
if ( childEmailField != null )
{
childEmailField.ExcelExportBehavior = includeChildren ? ExcelExportBehavior.AlwaysInclude : ExcelExportBehavior.NeverInclude;
}
var childAgeField = gAttendeesAttendance.Columns.OfType<RockBoundField>().FirstOrDefault( a => a.HeaderText == "Child Age" );
if ( childAgeField != null )
{
childAgeField.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;
gAttendeesAttendance.SetLinqDataSource( qryResult );
var currentPageItems = gAttendeesAttendance.DataSource as List<AttendeeResult>;
if ( currentPageItems != null )
{
var currentPagePersonIds = new List<int>();
if ( includeParents )
{
currentPagePersonIds = currentPageItems.Select( i => i.ParentId ).ToList();
gAttendeesAttendance.PersonIdField = "ParentId";
gAttendeesAttendance.DataKeyNames = new string[] { "ParentId", "PersonId" };
}
else if ( includeChildren )
{
currentPagePersonIds = currentPageItems.Select( i => i.ChildId ).ToList();
gAttendeesAttendance.PersonIdField = "ChildId";
gAttendeesAttendance.DataKeyNames = new string[] { "ChildId", "PersonId" };
}
else
{
currentPagePersonIds = currentPageItems.Select( i => i.PersonId ).ToList();
gAttendeesAttendance.PersonIdField = "PersonId";
gAttendeesAttendance.DataKeyNames = new string[] { "PersonId" };
}
LoadCurrentPageObjects( currentPagePersonIds );
}
_currentlyExporting = isExporting;
gAttendeesAttendance.DataBind();
_currentlyExporting = false;
}
catch ( Exception exception )
{
LogAndShowException( exception );
}
}
