public async Task <IActionResult> AttendeeByAttendeeId(Guid attendeeId)
{
var res = new AttendeeResult();
return(Json(res));
}
/// <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 );
}
}