public IEnumerable <IChartData> GetChartData(ChartGroupBy groupBy = ChartGroupBy.Week, AttendanceGraphBy graphBy = AttendanceGraphBy.Total, DateTime?startDate = null, DateTime?endDate = null, string groupIds = null, string campusIds = null, string scheduleIds = null, int?dataViewId = null)
{
return(new AttendanceService(new RockContext()).GetChartData(groupBy, graphBy, startDate, endDate, groupIds, campusIds, dataViewId, scheduleIds));
}
/// <summary>
/// Gets the chart data.
/// </summary>
/// <param name="groupBy">The group by.</param>
/// <param name="graphBy">The graph by.</param>
/// <param name="startDate">The start date.</param>
/// <param name="endDate">The end date.</param>
/// <param name="groupIds">The group ids.</param>
/// <param name="campusIds">The campus ids.</param>
/// <returns></returns>
public IEnumerable<IChartData> GetChartData( AttendanceGroupBy groupBy = AttendanceGroupBy.Week, AttendanceGraphBy graphBy = AttendanceGraphBy.Total, DateTime? startDate = null, DateTime? endDate = null, string groupIds = null, string campusIds = null )
{
var qry = Queryable().AsNoTracking().Where( a => a.DidAttend );
if ( startDate.HasValue )
{
qry = qry.Where( a => a.StartDateTime >= startDate.Value );
}
if ( endDate.HasValue )
{
qry = qry.Where( a => a.StartDateTime < endDate.Value );
}
if ( !string.IsNullOrWhiteSpace( groupIds ) )
{
var groupIdList = groupIds.Split( ',' ).Select( a => a.AsInteger() ).ToList();
qry = qry.Where( a => a.GroupId.HasValue && groupIdList.Contains( a.GroupId.Value ) );
}
if ( !string.IsNullOrWhiteSpace( campusIds ) )
{
var campusIdList = campusIds.Split( ',' ).Select( a => a.AsInteger() ).ToList();
qry = qry.Where( a => a.CampusId.HasValue && campusIdList.Contains( a.CampusId.Value ) );
}
//// for Date SQL functions, borrowed some ideas from http://stackoverflow.com/a/1177529/1755417 and http://stackoverflow.com/a/133101/1755417 and http://stackoverflow.com/a/607837/1755417
var qryWithSundayDate = qry.Select( a => new
{
Attendance = a,
SundayDate = SqlFunctions.DateAdd(
"day",
SqlFunctions.DateDiff( "day", "1900-01-01", SqlFunctions.DateAdd( "day", -SqlFunctions.DatePart( "weekday", a.StartDateTime ) + 1 + 1 + 6, a.StartDateTime ) ),
"1900-01-01" )
} );
var summaryQry = qryWithSundayDate.Select( a => new
{
// Build a CASE statement to group by week, or month, or year
SummaryDateTime = (DateTime)(
// GroupBy Week with Monday as FirstDayOfWeek ( +1 ) and Sunday as Summary Date ( +6 )
groupBy == AttendanceGroupBy.Week ? a.SundayDate :
// GroupBy Month
groupBy == AttendanceGroupBy.Month ? SqlFunctions.DateAdd( "day", -SqlFunctions.DatePart( "day", a.SundayDate ) + 1, a.SundayDate ) :
// GroupBy Year
groupBy == AttendanceGroupBy.Year ? SqlFunctions.DateAdd( "day", -SqlFunctions.DatePart( "dayofyear", a.SundayDate ) + 1, a.SundayDate ) :
// shouldn't happen
null
),
Campus = new
{
Id = a.Attendance.CampusId,
Name = a.Attendance.Campus.Name
},
Group = new
{
Id = a.Attendance.GroupId,
Name = a.Attendance.Group.Name
},
Schedule = new
{
Id = a.Attendance.ScheduleId,
Name = a.Attendance.Schedule.Name
}
} );
IList<AttendanceSummaryData> result = null;
if ( graphBy == AttendanceGraphBy.Total )
{
var groupByQry = summaryQry.GroupBy( a => new { a.SummaryDateTime } ).Select( s => new { s.Key, Count = s.Count() } ).OrderBy( o => o.Key );
result = groupByQry.ToList().Select( a => new AttendanceSummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesId = "Total",
YValue = a.Count
} ).ToList();
}
else if ( graphBy == AttendanceGraphBy.Campus )
{
var groupByQry = summaryQry.GroupBy( a => new { a.SummaryDateTime, Series = a.Campus } ).Select( s => new { s.Key, Count = s.Count() } ).OrderBy( o => o.Key );
result = groupByQry.ToList().Select( a => new AttendanceSummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesId = a.Key.Series.Name,
YValue = a.Count
} ).ToList();
}
else if ( graphBy == AttendanceGraphBy.Group )
{
var groupByQry = summaryQry.GroupBy( a => new { a.SummaryDateTime, Series = a.Group } ).Select( s => new { s.Key, Count = s.Count() } ).OrderBy( o => o.Key );
result = groupByQry.ToList().Select( a => new AttendanceSummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesId = a.Key.Series.Name,
YValue = a.Count
} ).ToList();
}
else if ( graphBy == AttendanceGraphBy.Schedule )
{
var groupByQry = summaryQry.GroupBy( a => new { a.SummaryDateTime, Series = a.Schedule } ).Select( s => new { s.Key, Count = s.Count() } ).OrderBy( o => o.Key );
result = groupByQry.ToList().Select( a => new AttendanceSummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesId = a.Key.Series.Name,
YValue = a.Count
} ).ToList();
}
return result;
}
/// <summary>
/// Gets the chart data.
/// </summary>
/// <returns></returns>
public IEnumerable<IChartData> GetChartData( AttendanceGroupBy groupBy = AttendanceGroupBy.Week, AttendanceGraphBy graphBy = AttendanceGraphBy.Total, DateTime? startDate = null, DateTime? endDate = null, string groupIds = null, string campusIds = null )
{
return new AttendanceService( new RockContext() ).GetChartData( groupBy, graphBy, startDate, endDate, groupIds, campusIds );
}
/// <summary>
/// Gets the chart data.
/// </summary>
/// <param name="groupBy">The group by.</param>
/// <param name="graphBy">The graph by.</param>
/// <param name="startDate">The start date.</param>
/// <param name="endDate">The end date.</param>
/// <param name="groupIds">The group ids.</param>
/// <param name="campusIds">The campus ids. Include the keyword 'null' in the list to include CampusId is null</param>
/// <param name="dataViewId">The data view identifier.</param>
/// <returns></returns>
public IEnumerable <IChartData> GetChartData(ChartGroupBy groupBy, AttendanceGraphBy graphBy, DateTime?startDate, DateTime?endDate, string groupIds, string campusIds, int?dataViewId)
{
return(GetChartData(groupBy, graphBy, startDate, endDate, groupIds, campusIds, dataViewId, null));
}
/// <summary>
/// Gets the chart data.
/// </summary>
/// <param name="groupBy">The group by.</param>
/// <param name="graphBy">The graph by.</param>
/// <param name="startDate">The start date.</param>
/// <param name="endDate">The end date.</param>
/// <param name="groupIds">The group ids.</param>
/// <param name="campusIds">The campus ids. Include the keyword 'null' in the list to include CampusId is null</param>
/// <param name="scheduleIds">The schedule ids.</param>
/// <param name="dataViewId">The data view identifier.</param>
/// <returns></returns>
public IEnumerable <IChartData> GetChartData(ChartGroupBy groupBy = ChartGroupBy.Week, AttendanceGraphBy graphBy = AttendanceGraphBy.Total, DateTime?startDate = null, DateTime?endDate = null, string groupIds = null, string campusIds = null, int?dataViewId = null, string scheduleIds = null)
{
var qryAttendance = Queryable().AsNoTracking()
.Where(a =>
a.DidAttend.HasValue &&
a.DidAttend.Value &&
a.PersonAlias != null);
if (startDate.HasValue)
{
qryAttendance = qryAttendance.Where(a => a.StartDateTime >= startDate.Value);
}
if (endDate.HasValue)
{
qryAttendance = qryAttendance.Where(a => a.StartDateTime < endDate.Value);
}
if (dataViewId.HasValue)
{
var rockContext = (RockContext)this.Context;
var dataView = new DataViewService(rockContext).Get(dataViewId.Value);
if (dataView != null)
{
var personService = new PersonService(rockContext);
var errorMessages = new List <string>();
ParameterExpression paramExpression = personService.ParameterExpression;
Expression whereExpression = dataView.GetExpression(personService, paramExpression, out errorMessages);
Rock.Web.UI.Controls.SortProperty sort = null;
var dataViewPersonIdQry = personService
.Queryable().AsNoTracking()
.Where(paramExpression, whereExpression, sort)
.Select(p => p.Id);
qryAttendance = qryAttendance.Where(a => dataViewPersonIdQry.Contains(a.PersonAlias.PersonId));
}
}
if (!string.IsNullOrWhiteSpace(groupIds))
{
var 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 = (campusIds ?? "").Split(',').ToList().Any(a => a.Equals("null", StringComparison.OrdinalIgnoreCase));
var campusIdList = (campusIds ?? "").Split(',').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);
}
// If schedules were included, filter attendances by those that have selected schedules
var scheduleIdList = (scheduleIds ?? "").Split(',').AsIntegerList();
scheduleIdList.Remove(0);
if (scheduleIdList.Any())
{
qryAttendance = qryAttendance.Where(a => a.ScheduleId.HasValue && scheduleIdList.Contains(a.ScheduleId.Value));
}
var qryAttendanceWithSummaryDateTime = qryAttendance.GetAttendanceWithSummaryDateTime(groupBy);
var summaryQry = qryAttendanceWithSummaryDateTime.Select(a => new
{
a.SummaryDateTime,
Campus = new
{
Id = a.Attendance.CampusId,
Name = a.Attendance.Campus.Name
},
Group = new
{
Id = a.Attendance.GroupId,
Name = a.Attendance.Group.Name
},
Schedule = new
{
Id = a.Attendance.ScheduleId,
Name = a.Attendance.Schedule.Name
},
Location = new
{
Id = a.Attendance.LocationId,
Name = a.Attendance.Location.Name
}
});
List <SummaryData> result = null;
if (graphBy == AttendanceGraphBy.Total)
{
var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key);
result = groupByQry.ToList().Select(a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = "Total",
YValue = a.Count
}).ToList();
}
else if (graphBy == AttendanceGraphBy.Campus)
{
var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime, Series = a.Campus }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key);
result = groupByQry.ToList().Select(a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = a.Key.Series.Name,
YValue = a.Count
}).ToList();
}
else if (graphBy == AttendanceGraphBy.Group)
{
var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime, Series = a.Group }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key);
result = groupByQry.ToList().Select(a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = a.Key.Series.Name,
YValue = a.Count
}).ToList();
}
else if (graphBy == AttendanceGraphBy.Schedule)
{
var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime, Series = a.Schedule }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key);
result = groupByQry.ToList().Select(a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = a.Key.Series.Name,
YValue = a.Count
}).ToList();
}
else if (graphBy == AttendanceGraphBy.Location)
{
var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime, Series = a.Location }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key);
result = groupByQry.ToList().Select(a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = a.Key.Series.Name,
YValue = a.Count
}).ToList();
}
return(result);
}
/// <summary>
/// Gets the chart data.
/// </summary>
/// <param name="groupBy">The group by.</param>
/// <param name="graphBy">The graph by.</param>
/// <param name="startDate">The start date.</param>
/// <param name="endDate">The end date.</param>
/// <param name="groupIds">The group ids.</param>
/// <param name="campusIds">The campus ids. Include the keyword 'null' in the list to include CampusId is null</param>
/// <param name="scheduleIds">The schedule ids.</param>
/// <param name="dataViewId">The data view identifier.</param>
/// <returns></returns>
public IEnumerable<IChartData> GetChartData( ChartGroupBy groupBy = ChartGroupBy.Week, AttendanceGraphBy graphBy = AttendanceGraphBy.Total, DateTime? startDate = null, DateTime? endDate = null, string groupIds = null, string campusIds = null, int? dataViewId = null, string scheduleIds = null )
{
var qryAttendance = Queryable().AsNoTracking()
.Where( a =>
a.DidAttend.HasValue &&
a.DidAttend.Value &&
a.PersonAlias != null );
if ( startDate.HasValue )
{
qryAttendance = qryAttendance.Where( a => a.StartDateTime >= startDate.Value );
}
if ( endDate.HasValue )
{
qryAttendance = qryAttendance.Where( a => a.StartDateTime < endDate.Value );
}
if ( dataViewId.HasValue )
{
var rockContext = (RockContext)this.Context;
var dataView = new DataViewService( rockContext ).Get( dataViewId.Value );
if ( dataView != null )
{
var personService = new PersonService( rockContext );
var errorMessages = new List<string>();
ParameterExpression paramExpression = personService.ParameterExpression;
Expression whereExpression = dataView.GetExpression( personService, paramExpression, out errorMessages );
Rock.Web.UI.Controls.SortProperty sort = null;
var dataViewPersonIdQry = personService
.Queryable().AsNoTracking()
.Where( paramExpression, whereExpression, sort )
.Select( p => p.Id );
qryAttendance = qryAttendance.Where( a => dataViewPersonIdQry.Contains( a.PersonAlias.PersonId ) );
}
}
if ( !string.IsNullOrWhiteSpace( groupIds ) )
{
var 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 = ( campusIds ?? "" ).Split( ',' ).ToList().Any( a => a.Equals( "null", StringComparison.OrdinalIgnoreCase ) );
var campusIdList = ( campusIds ?? "" ).Split( ',' ).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 );
}
// If schedules were included, filter attendances by those that have selected schedules
var scheduleIdList = ( scheduleIds ?? "" ).Split( ',' ).AsIntegerList();
scheduleIdList.Remove( 0 );
if ( scheduleIdList.Any() )
{
qryAttendance = qryAttendance.Where( a => a.ScheduleId.HasValue && scheduleIdList.Contains( a.ScheduleId.Value ) );
}
var qryAttendanceWithSummaryDateTime = qryAttendance.GetAttendanceWithSummaryDateTime( groupBy );
var summaryQry = qryAttendanceWithSummaryDateTime.Select( a => new
{
a.SummaryDateTime,
Campus = new
{
Id = a.Attendance.CampusId,
Name = a.Attendance.Campus.Name
},
Group = new
{
Id = a.Attendance.GroupId,
Name = a.Attendance.Group.Name
},
Schedule = new
{
Id = a.Attendance.ScheduleId,
Name = a.Attendance.Schedule.Name
},
Location = new
{
Id = a.Attendance.LocationId,
Name = a.Attendance.Location.Name
}
} );
List<SummaryData> result = null;
if ( graphBy == AttendanceGraphBy.Total )
{
var groupByQry = summaryQry.GroupBy( a => new { a.SummaryDateTime } ).Select( s => new { s.Key, Count = s.Count() } ).OrderBy( o => o.Key );
result = groupByQry.ToList().Select( a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = "Total",
YValue = a.Count
} ).ToList();
}
else if ( graphBy == AttendanceGraphBy.Campus )
{
var groupByQry = summaryQry.GroupBy( a => new { a.SummaryDateTime, Series = a.Campus } ).Select( s => new { s.Key, Count = s.Count() } ).OrderBy( o => o.Key );
result = groupByQry.ToList().Select( a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = a.Key.Series.Name,
YValue = a.Count
} ).ToList();
}
else if ( graphBy == AttendanceGraphBy.Group )
{
var groupByQry = summaryQry.GroupBy( a => new { a.SummaryDateTime, Series = a.Group } ).Select( s => new { s.Key, Count = s.Count() } ).OrderBy( o => o.Key );
result = groupByQry.ToList().Select( a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = a.Key.Series.Name,
YValue = a.Count
} ).ToList();
}
else if ( graphBy == AttendanceGraphBy.Schedule )
{
var groupByQry = summaryQry.GroupBy( a => new { a.SummaryDateTime, Series = a.Schedule } ).Select( s => new { s.Key, Count = s.Count() } ).OrderBy( o => o.Key );
result = groupByQry.ToList().Select( a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = a.Key.Series.Name,
YValue = a.Count
} ).ToList();
}
else if ( graphBy == AttendanceGraphBy.Location )
{
var groupByQry = summaryQry.GroupBy( a => new { a.SummaryDateTime, Series = a.Location } ).Select( s => new { s.Key, Count = s.Count() } ).OrderBy( o => o.Key );
result = groupByQry.ToList().Select( a => new SummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesName = a.Key.Series.Name,
YValue = a.Count
} ).ToList();
}
return result;
}
/// <summary>
/// Gets the chart data.
/// </summary>
/// <param name="groupBy">The group by.</param>
/// <param name="graphBy">The graph by.</param>
/// <param name="startDate">The start date.</param>
/// <param name="endDate">The end date.</param>
/// <param name="groupIds">The group ids.</param>
/// <param name="campusIds">The campus ids. Include the keyword 'null' in the list to include CampusId is null</param>
/// <param name="dataViewId">The data view identifier.</param>
/// <returns></returns>
public IEnumerable<IChartData> GetChartData( ChartGroupBy groupBy, AttendanceGraphBy graphBy, DateTime? startDate, DateTime? endDate, string groupIds, string campusIds, int? dataViewId )
{
return GetChartData( groupBy, graphBy, startDate, endDate, groupIds, campusIds, dataViewId, null );
}
/// <summary>
/// Gets the chart data.
/// </summary>
/// <param name="groupBy">The group by.</param>
/// <param name="graphBy">The graph by.</param>
/// <param name="startDate">The start date.</param>
/// <param name="endDate">The end date.</param>
/// <param name="groupIds">The group ids.</param>
/// <param name="campusIds">The campus ids.</param>
/// <returns></returns>
public IEnumerable <IChartData> GetChartData(AttendanceGroupBy groupBy = AttendanceGroupBy.Week, AttendanceGraphBy graphBy = AttendanceGraphBy.Total, DateTime?startDate = null, DateTime?endDate = null, string groupIds = null, string campusIds = null)
{
var qry = Queryable().AsNoTracking().Where(a => a.DidAttend.HasValue && a.DidAttend.Value);
if (startDate.HasValue)
{
qry = qry.Where(a => a.StartDateTime >= startDate.Value);
}
if (endDate.HasValue)
{
qry = qry.Where(a => a.StartDateTime < endDate.Value);
}
if (!string.IsNullOrWhiteSpace(groupIds))
{
var groupIdList = groupIds.Split(',').AsIntegerList();
qry = qry.Where(a => a.GroupId.HasValue && groupIdList.Contains(a.GroupId.Value));
}
if (!string.IsNullOrWhiteSpace(campusIds))
{
var campusIdList = campusIds.Split(',').AsIntegerList();
qry = qry.Where(a => a.CampusId.HasValue && campusIdList.Contains(a.CampusId.Value));
}
//// for Date SQL functions, borrowed some ideas from http://stackoverflow.com/a/1177529/1755417 and http://stackoverflow.com/a/133101/1755417 and http://stackoverflow.com/a/607837/1755417
var knownSunday = new DateTime(1966, 1, 30); // Because we can't use the @@DATEFIRST option in Linq to query how DATEPART("weekday",) will work, use a known Sunday date instead.
var qryWithSundayDate = qry.Select(a => new
{
Attendance = a,
SundayDate = SqlFunctions.DateAdd(
"day",
SqlFunctions.DateDiff("day",
"1900-01-01",
SqlFunctions.DateAdd("day",
(((SqlFunctions.DatePart("weekday", knownSunday) + 7) - SqlFunctions.DatePart("weekday", a.StartDateTime)) % 7),
a.StartDateTime
)
),
"1900-01-01"
)
});
var summaryQry = qryWithSundayDate.Select(a => new
{
// Build a CASE statement to group by week, or month, or year
SummaryDateTime = (DateTime)(
// GroupBy Week with Monday as FirstDayOfWeek ( +1 ) and Sunday as Summary Date ( +6 )
groupBy == AttendanceGroupBy.Week ? a.SundayDate :
// GroupBy Month
groupBy == AttendanceGroupBy.Month ? SqlFunctions.DateAdd("day", -SqlFunctions.DatePart("day", a.SundayDate) + 1, a.SundayDate) :
// GroupBy Year
groupBy == AttendanceGroupBy.Year ? SqlFunctions.DateAdd("day", -SqlFunctions.DatePart("dayofyear", a.SundayDate) + 1, a.SundayDate) :
// shouldn't happen
null
),
Campus = new
{
Id = a.Attendance.CampusId,
Name = a.Attendance.Campus.Name
},
Group = new
{
Id = a.Attendance.GroupId,
Name = a.Attendance.Group.Name
},
Schedule = new
{
Id = a.Attendance.ScheduleId,
Name = a.Attendance.Schedule.Name
}
});
List <AttendanceSummaryData> result = null;
if (graphBy == AttendanceGraphBy.Total)
{
var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key);
result = groupByQry.ToList().Select(a => new AttendanceSummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesId = "Total",
YValue = a.Count
}).ToList();
}
else if (graphBy == AttendanceGraphBy.Campus)
{
var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime, Series = a.Campus }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key);
result = groupByQry.ToList().Select(a => new AttendanceSummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesId = a.Key.Series.Name,
YValue = a.Count
}).ToList();
}
else if (graphBy == AttendanceGraphBy.Group)
{
var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime, Series = a.Group }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key);
result = groupByQry.ToList().Select(a => new AttendanceSummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesId = a.Key.Series.Name,
YValue = a.Count
}).ToList();
}
else if (graphBy == AttendanceGraphBy.Schedule)
{
var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime, Series = a.Schedule }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key);
result = groupByQry.ToList().Select(a => new AttendanceSummaryData
{
DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(),
DateTime = a.Key.SummaryDateTime,
SeriesId = a.Key.Series.Name,
YValue = a.Count
}).ToList();
}
if (result.Count == 1)
{
var dummyZeroDate = startDate ?? DateTime.MinValue;
result.Insert(0, new AttendanceSummaryData {
DateTime = dummyZeroDate, DateTimeStamp = dummyZeroDate.ToJavascriptMilliseconds(), SeriesId = result[0].SeriesId, YValue = 0
});
}
return(result);
}
