public IEnumerable <AttendanceSummaryViewModel> GetAttendanceSummary(DateTime start, DateTime end, int structuralGroupId)
{
var db = ApplicationContext.DatabaseContext.Database;
using (var structureRepository = new StructureRepository(db, Services.MemberService))
{
var structure = structureRepository.GetStructureFrom(structuralGroupId);
var ids = new List <int>();
PopulateStructureIds(structure, ids);
var query = string.Format("SELECT [Date], Attendance, COUNT(*) AS Total FROM AttendanceRecord " +
"INNER JOIN AttendanceSet ON AttendanceRecord.AttendanceSetId = AttendanceSet.Id " +
"WHERE [Date] BETWEEN '{0}' and '{1}' " +
"AND StructuralGroupId IN ({2}) " +
"GROUP BY [Date], Attendance", start.ToString("yyyy-MM-dd"), end.ToString("yyyy-MM-dd"), string.Join(",", ids));
var table = DbUtils.ExecuteDataTable(db, query);
return(DateUtils.Range(start, end).Select(date =>
{
var presentRow = table.Select(string.Format("Date = '{0:yyyy-MM-dd}' AND Attendance = {1}", date, (int)Attendance.Present)).FirstOrDefault();
var absentRow = table.Select(string.Format("Date = '{0:yyyy-MM-dd}' AND Attendance = {1}", date, (int)Attendance.Absent)).FirstOrDefault();
var present = presentRow == null ? 0 : presentRow.Field <int>("Total");
var absent = absentRow == null ? 0 : absentRow.Field <int>("Total");
return new AttendanceSummaryViewModel
{
Date = date,
Present = present,
Absent = absent
};
}));
}
}
