public List<LimitStatsByDay> GetLimitStatsByDay(IOWLimit limit, DateTime? startTimestamp, DateTime? endTimestamp)
{
List<LimitStatsByDay> stats = new List<LimitStatsByDay>();
DateTime startDay = NormalizeStartDay(startTimestamp);
DateTime endDay = NormalizeEndDay(startDay, endTimestamp);
if (limit != null)
stats = GetLimitStatsOneLimit(limit, startDay, endDay);
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;
}
/*
* CalculateStatisticsForOneLimit()
*
* Handles a single limit for a specified time period. It reads the deviation table (IOWDeviation),
* chunks up deviations by day, and stores the results in the stats table (IOWStatsByDay).
*
* Since deviations can change as new tag data are received, the statistics table (which is derived from the deviations
* table) can also change. As changes are hard to figure out, this routine simply zeros out the stats table for the limit
* and time period in question, and recalculates the statistics for each day of interest.
*
* May be called by UpdateStatistics(), which loops through all limits. Can also be triggered when deviations are updated.
*/
public int CalculateStatisticsForOneLimit(IOWLimit limit, DateTime? startTimestamp, DateTime? endTimestamp)
{
int numberRecordsUpdated = 0;
// This routine calculates IOW deviation statistics for one limit for a specified time range.
// The start and end time must be at midnight, to match the records expected in IOWStatsByDay.
// Validation must be done by the caller.
// The start and end times must be at midnight. Limit the time period to the last 60 days.
// Default calculations to today.
DateTime startDay = NormalizeStartDay(startTimestamp);
DateTime endDay = NormalizeEndDay(startDay, endTimestamp);
// Get any stats that already exist for the time period of interest, and zero out the data.
// Do this before getting the deviations because it is possible that there are not any deviations, in which case
// the stats should be zeroed.
List<IOWStatsByDay> allStats = _iowStatsByDayRepository.GetAllList(p => p.IOWLimitId == limit.Id && p.Day >= startDay && p.Day <= endDay).ToList();
if( allStats != null && allStats.Count > 0 )
{
allStats.Select(p => { p.NumberDeviations = 0; p.DurationHours = 0; return p; }).ToList();
/*foreach(IOWStatsByDay stat in allStats)
{
stat.NumberDeviations = 0;
stat.DurationHours = 0;
}*/
}
List<IOWDeviation> deviations = GetDeviations(limit.Id, startDay);
if( deviations != null && deviations.Count > 0 )
{
IOWStatsByDay stat = null;
// Set the last end day to before the start of the processing period
DateTime startDeviation, endDeviation;
foreach(IOWDeviation dev in deviations)
{
// startDay = midnight on the day when the deviation starts; clamped to be no earlier than startTimestamp in the arguments
// endDay = midnight on the day after startDay
// startDeviation = start of the deviation for this day's statistics; clamped to be no earlier than startDay
// endDeviation = end of the deviation for this day's statistics; allowed to be beyond the end of this day; defaults to now
if (dev.StartTimestamp < startDay)
{
startDeviation = startDay;
}
else
{
startDay = dev.StartTimestamp.Date;
startDeviation = dev.StartTimestamp;
}
if (dev.EndTimestamp.HasValue)
endDeviation = dev.EndTimestamp.Value;
else
endDeviation = DateTime.Now;
// Process this deviation until we run out of days.
bool insertNewRecord = false;
while ( startDeviation < endDeviation )
{
endDay = startDay.AddDays(1);
double durationHours = ((endDeviation <= endDay ? endDeviation : endDay) - startDeviation).TotalHours;
// If we already have a stat record (from earlier in the loop) and it was for a different time period and it was created here,
// then insert it and start over. Otherwise, keep the last stat record as we might want to reuse it.
if (stat != null && stat.Day != startDay && insertNewRecord)
{
_iowStatsByDayRepository.Insert(stat);
allStats.Add(stat);
stat = null;
insertNewRecord = false;
}
// Look for an already existing stat record to update.
// If we do not already have a stat record OR the dates don't match, look for an existing one in the database
// If we can't find a stat record in the database, then create a new record. We'll insert it later.
if (stat == null || stat.Day != startDay)
{
stat = allStats.FirstOrDefault(p => p.Day == startDay);
if (stat == null)
{
stat = new IOWStatsByDay { IOWLimitId = limit.Id, Day = startDay, NumberDeviations = 0, DurationHours = 0, TenantId = limit.TenantId };
insertNewRecord = true;
}
else
insertNewRecord = false;
}
else
insertNewRecord = false;
// Update the stat record with new information
stat.NumberDeviations++;
stat.DurationHours += durationHours;
numberRecordsUpdated++;
// Move to the next day. Look to see if this deviation slides into the following day
startDay = startDay.AddDays(1);
endDay = startDay.AddDays(1);
startDeviation = startDay;
}
// Handle the last record, if any
if (insertNewRecord)
{
_iowStatsByDayRepository.Insert(stat);
allStats.Add(stat);
}
// ABP will automatically update open records, so no need to call the Update() method on IOWStatByDay.
} // foreach(IOWDeviation dev in deviations)
} // if( deviations != null && deviations.Count > 0 )
return numberRecordsUpdated;
}
public long InsertOrUpdateLimitAndGetId(IOWLimit input)
{
IOWLimit limit = null;
// If the limit id is present, assume the limit exists.
// Otherwise check to see if this limit exists.
if (input.Id > 0)
limit = FirstOrDefaultLimit(input.Id);
else
limit = FirstOrDefaultLimit(input.IOWVariableId, input.IOWLevelId);
if (limit == null)
limit = new IOWLimit
{
TenantId = input.TenantId,
IOWVariableId = input.IOWVariableId,
IOWLevelId = input.IOWLevelId
};
limit.StartDate = input.StartDate;
limit.EndDate = input.EndDate;
limit.Direction = input.Direction;
limit.Value = input.Value;
limit.Cause = input.Cause;
limit.Consequences = input.Consequences;
limit.Action = input.Action;
limit.LastCheckDate = input.LastCheckDate;
limit.LastStatus = input.LastStatus;
limit.LastDeviationStartTimestamp = input.LastDeviationStartTimestamp;
limit.LastDeviationEndTimestamp = input.LastDeviationEndTimestamp;
return _iowLimitRespository.InsertOrUpdateAndGetId(limit);
}
public LimitDto UpdateLimit(UpdateLimitInput input)
{
DateTime oldestDate = new DateTime(2014, 1, 1);
// This method accepts one limit for one variable.
// If the limit does not exist, it is added.
// If the limit exists, it is changed or deleted.
LimitDto output = null;
bool inputIsValid = false;
bool isActive = input.IsActive.HasValue ? input.IsActive.Value : true;
// Look for the variable.
IOWVariable variable = _iowManager.FirstOrDefaultVariable(input.IOWVariableId, input.VariableName);
if( variable != null )
{
// Does the specified limit exist? If so, get it. This will return null if nothing is found.
IOWLimit limit = _iowManager.FirstOrDefaultLimit(variable.Id, input.Id, input.LevelName);
// Does the specified level exist? It should.
IOWLevel level = _iowManager.FirstOrDefaultLevel(input.IOWLevelId, input.LevelName);
// There are five possibilities at this point:
// 1) We did not find a level ==> bad input, do nothing
// 2) We found a level, found a limit, isActive flag is true ==> update an existing limit
// 3) We found a level, found a limit, IsActive flag is false ==> delete an existing limit
// 4) We found a level, did not find a limit, IsActive flag is true ==> insert a new limit
// 5) We found a level, did not find a limit, IsActive flag is false ==> do nothing
if ( level == null )
{
// Case 1: Bad input
}
else if ( isActive )
{
// Case 2: IsActive is true AND limit exists ==> update an existing limit
// Case 4: IsActive is true AND limit does not exist ==> insert a new limit
// For case 4 (limit does not exist), need to create a limit record to continue
if ( limit == null )
limit = new IOWLimit
{
IOWVariableId = variable.Id,
IOWLevelId = level.Id,
LastCheckDate = null,
LastStatus = IOWStatus.Normal,
LastDeviationStartTimestamp = null,
LastDeviationEndTimestamp = null,
TenantId = variable.TenantId
};
limit.StartDate = new DateTime(Math.Max(input.StartDate.Ticks, oldestDate.Ticks));
limit.EndDate = input.EndDate;
limit.Direction = input.Direction;
limit.Value = input.Value;
limit.Cause = input.Cause;
limit.Consequences = input.Consequences;
limit.Action = input.Action;
limit.Id = _iowManager.InsertOrUpdateLimitAndGetId(limit);
inputIsValid = true;
}
else if ( limit != null && !isActive )
{
// Case 3: Limit exists and should be deleted
_iowManager.DeleteLimit(limit.Id);
inputIsValid = true;
}
if( inputIsValid )
output = new LimitDto
{
Id = limit.Id,
IOWVariableId = variable.Id,
IsActive = (limit != null && isActive ) ? true : false,
IOWLevelId = limit.IOWLevelId,
Name = level.Name,
Criticality = level.Criticality,
StartDate = limit.StartDate,
EndDate = limit.EndDate,
Direction = limit.Direction,
Value = limit.Value,
Cause = limit.Cause,
Consequences = limit.Consequences,
Action = limit.Action
};
}
return output;
}
