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;
}