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