public List<LimitStatsByDay> GetLimitStatsByDayGroupByLevel(List<long> limitIds, DateTime? startTimestamp, DateTime? endTimestamp) { List<LimitStatsByDay> stats = new List<LimitStatsByDay>(); DateTime startDay = NormalizeStartDay(startTimestamp); DateTime endDay = NormalizeEndDay(startDay, endTimestamp); // Build a date array for what SHOULD be in the statistics table for each variable List<DateTime> datetimes = new List<DateTime>(); for (DateTime dt = startDay; dt < endDay; dt = dt.AddDays(1)) datetimes.Add(dt); // Get the unique set of limits var query0 = from lim in _iowLimitRespository.GetAllList() join l in limitIds on lim.Id equals l orderby l select new { LimitId = lim.Id, LevelName = lim.Level.Name, Criticality = lim.Level.Criticality, Direction = lim.Direction }; var allLimits = query0.ToList(); // This query returns the Cartesian product of all limits and dates var query1 = from lim in allLimits from dt in datetimes orderby lim.LimitId, dt select new { LimitId = lim.LimitId, LevelName = lim.LevelName, Criticality = lim.Criticality, Direction = lim.Direction, Day = dt }; var allLimitsAndDates = query1.ToList(); // This query joins the Cartesian product to the stats table, and fills in zeros whenever the stats table lacks a record var query2 = from a in allLimitsAndDates join s in _iowStatsByDayRepository.GetAllList(p => p.Day >= startDay && p.Day <= endDay) on new { LimitId = a.LimitId, Day = a.Day } equals new { LimitId = s.IOWLimitId, Day = s.Day } into joinedStats from t in joinedStats.DefaultIfEmpty(new IOWStatsByDay { NumberDeviations = 0, DurationHours = 0 }) orderby a.LimitId, a.Day select new { LimitId = a.LimitId, LevelName = a.LevelName, Criticality = a.Criticality, Direction = a.Direction, Day = a.Day, NumberDeviations = t.NumberDeviations, DurationHours = t.DurationHours }; var dataset = query2.ToList(); var query3 = from z in dataset group z by new { /*z.LimitId,*/ z.LevelName, z.Criticality, /*z.Direction,*/ z.Day } into g select new { LimitId = 0 /*g.Key.LimitId*/, LevelName = g.Key.LevelName, Criticality = g.Key.Criticality, Direction = Direction.None /*g.Key.Direction*/, Day = g.Key.Day, NumberDeviations = g.Sum(x => x.NumberDeviations), DurationHours = g.Sum(x => x.DurationHours) }; var results = query3.ToList(); if (results != null) { LimitStatsByDay stat = null; LimitStatsByDay lastStat = null; foreach (var d in results) { if (lastStat == null || lastStat.LimitId != d.LimitId || lastStat.LevelName != d.LevelName || lastStat.Criticality != d.Criticality) { if (lastStat != null) stats.Add(stat); stat = new LimitStatsByDay { LimitId = d.LimitId, LevelName = d.LevelName, Criticality = d.Criticality, Direction = d.Direction, Days = new List<LimitStatDays>() }; } stat.Days.Add(new LimitStatDays { Day = d.Day, NumberDeviations = d.NumberDeviations, DurationHours = d.DurationHours }); lastStat = stat; } stats.Add(stat); } return stats; }
private List<LimitStatsByDay> GetLimitStatsOneLimit(IOWLimit limit, DateTime startDay, DateTime endDay) { List<LimitStatsByDay> stats = new List<LimitStatsByDay>(); // Build a date array for what SHOULD be in the statistics table for each variable List<DateTime> datetimes = new List<DateTime>(); for (DateTime dt = startDay; dt < endDay; dt = dt.AddDays(1)) datetimes.Add(dt); // This query returns the Cartesian product of all limits and dates var query1 = from lim in _iowLimitRespository.GetAllList(p => p.Id == limit.Id) from dt in datetimes orderby lim.Id, dt select new { LimitId = lim.Id, LevelName = lim.Level.Name, Criticality = lim.Level.Criticality, Direction = lim.Direction, Day = dt }; var allLimitsAndDates = query1.ToList(); // This query joins the Cartesian product to the stats table, and fills in zeros whenever the stats table lacks a record var query2 = from a in allLimitsAndDates join s in _iowStatsByDayRepository.GetAllList(p => p.Day >= startDay && p.Day <= endDay) on new { LimitId = a.LimitId, Day = a.Day } equals new { LimitId = s.IOWLimitId, Day = s.Day } into joinedStats from t in joinedStats.DefaultIfEmpty( new IOWStatsByDay { NumberDeviations=0, DurationHours=0 }) orderby a.LimitId, a.Day select new { LimitId=a.LimitId, LevelName=a.LevelName, Criticality=a.Criticality, Direction=a.Direction, Day=a.Day, NumberDeviations=t.NumberDeviations, DurationHours=t.DurationHours}; var results = query2.ToList(); if( results != null ) { LimitStatsByDay stat = null; LimitStatsByDay lastStat = null; foreach(var d in results) { if( lastStat == null || lastStat.LimitId != d.LimitId ) { if (lastStat != null) stats.Add(stat); stat = new LimitStatsByDay { LimitId = d.LimitId, LevelName = d.LevelName, Criticality = d.Criticality, Direction = d.Direction, Days = new List<LimitStatDays>() }; } stat.Days.Add(new LimitStatDays { Day = d.Day, NumberDeviations = d.NumberDeviations, DurationHours = d.DurationHours }); lastStat = stat; } stats.Add(stat); } return stats; }