private List <AlarmRangeLimit> InitializeRangeLimitTable(AdoDataConnection connection, Channel channel)
{
// Fill the range limit table with range limits for the given channel
TableOperations <AlarmRangeLimit> alarmRangeLimitTable = new TableOperations <AlarmRangeLimit>(connection);
List <AlarmRangeLimit> alarmRangeLimits = alarmRangeLimitTable
.QueryRecordsWhere("ChannelID = {0}", channel.ID)
.ToList();
// If limits exist for the given channel,
// range limit table has been successfully initialized
if (alarmRangeLimits.Count != 0)
{
return(alarmRangeLimits);
}
// Get the default range limits for the measurement type and characteristic of this channel
TableOperations <DefaultAlarmRangeLimit> defaultAlarmRangeLimitTable = new TableOperations <DefaultAlarmRangeLimit>(connection);
int measurementTypeID = channel.MeasurementTypeID;
int measurementCharacteristicID = channel.MeasurementCharacteristicID;
List <DefaultAlarmRangeLimit> defaultAlarmRangeLimits = defaultAlarmRangeLimitTable
.QueryRecordsWhere("MeasurementTypeID = {0} AND MeasurementCharacteristicID = {1}", measurementTypeID, measurementCharacteristicID)
.ToList();
// If there are no default limits for the channel,
// then the range limit table has been successfully initialized
if (defaultAlarmRangeLimits.Count == 0)
{
return(alarmRangeLimits);
}
foreach (DefaultAlarmRangeLimit defaultAlarmRangeLimit in defaultAlarmRangeLimits)
{
AlarmRangeLimit alarmRangeLimit = new AlarmRangeLimit()
{
ChannelID = channel.ID,
AlarmTypeID = defaultAlarmRangeLimit.AlarmTypeID,
Severity = defaultAlarmRangeLimit.Severity,
High = defaultAlarmRangeLimit.High,
Low = defaultAlarmRangeLimit.Low,
RangeInclusive = defaultAlarmRangeLimit.RangeInclusive,
PerUnit = defaultAlarmRangeLimit.PerUnit,
IsDefault = true,
Enabled = true
};
alarmRangeLimitTable.AddNewRecord(alarmRangeLimit);
}
return(alarmRangeLimits);
}
public AlarmRangeLimit CreateNewAlarmRangeLimit(AlarmRangeLimitView record)
{
AlarmRangeLimit arl = new AlarmRangeLimit();
arl.ID = record.ID;
arl.ChannelID = record.ChannelID;
arl.AlarmTypeID = record.AlarmTypeID;
arl.Enabled = record.Enabled;
arl.Severity = record.Severity;
arl.High = record.High;
arl.Low = record.Low;
arl.RangeInclusive = record.RangeInclusive;
arl.PerUnit = record.PerUnit;
arl.IsDefault = record.IsDefault;
return(arl);
}
private bool CheckAlarm(Channel channel, TrendingDataPoint trendingPoint, AlarmRangeLimit alarmRangeLimit)
{
double highLimit = 0.0D;
double lowLimit = 0.0D;
bool highValid = true;
bool lowValid = true;
double perUnitValue = channel.PerUnitValue ?? 1.0D;
double factor = alarmRangeLimit.PerUnit ? perUnitValue : 1.0D;
if ((object)alarmRangeLimit.High != null)
{
highLimit = factor * alarmRangeLimit.High.GetValueOrDefault();
highValid = Convert.ToBoolean(alarmRangeLimit.RangeInclusive) ^ (trendingPoint.Value <= highLimit);
}
if ((object)alarmRangeLimit.Low != null)
{
lowLimit = factor * alarmRangeLimit.Low.GetValueOrDefault();
lowValid = Convert.ToBoolean(alarmRangeLimit.RangeInclusive) ^ (trendingPoint.Value >= lowLimit);
}
return(!lowValid || !highValid);
}
private async Task ProcessFileAsync(HttpContent file)
{
string csvFileData = await file.ReadAsStringAsync();
try
{
string[] csvRows = csvFileData.Split('\n');
string[] tableFields = csvRows[0].Split(',');
using (DataContext dataContext = new DataContext())
{
TableOperations <AlarmRangeLimit> table = dataContext.Table <AlarmRangeLimit>();
for (int i = 1; i < csvRows.Length; ++i)
{
string[] row = csvRows[i].Split(',');
AlarmRangeLimit newRecord = new AlarmRangeLimit();
newRecord.ID = int.Parse(row[0]);
newRecord.ChannelID = int.Parse(row[1]);
newRecord.Severity = int.Parse(row[4]);
newRecord.High = double.Parse(row[5]);
newRecord.Low = double.Parse(row[6]);
newRecord.RangeInclusive = bool.Parse(row[7]);
newRecord.PerUnit = bool.Parse(row[8]);
newRecord.Enabled = bool.Parse(row[9]);
newRecord.IsDefault = bool.Parse(row[17]);
table.UpdateRecord(newRecord);
}
}
}
catch (LogException ex)
{
Debug.WriteLine(ex.Message);
}
}
private async Task ProcessFileAsync(HttpContent file, string referrer)
{
string csvFileData = await file.ReadAsStringAsync();
try
{
string[] csvRows = csvFileData.Split('\n');
string[] tableFields = csvRows[0].Split(',');
for (int i = 0; i < tableFields.Length; i++)
{
tableFields[i] = tableFields[i].Trim();
tableFields[i] = tableFields[i].Trim(new char[] { '[', ']' });
}
if (referrer.Contains("ChannelsWithHourlyLimits.cshtml") || referrer.Contains("MetersWithHourlyLimits.cshtml") || referrer.Contains("HourOfWeekLimits.cshtml"))
{
int[] fieldIndexes = new int[8];
fieldIndexes[0] = Array.IndexOf(tableFields, "AlarmID");
fieldIndexes[1] = Array.IndexOf(tableFields, "AlarmChannelID");
fieldIndexes[2] = Array.IndexOf(tableFields, "AlarmAlarmTypeID");
fieldIndexes[3] = Array.IndexOf(tableFields, "AlarmHourOfWeek");
fieldIndexes[4] = Array.IndexOf(tableFields, "AlarmSeverity");
fieldIndexes[5] = Array.IndexOf(tableFields, "AlarmHigh");
fieldIndexes[6] = Array.IndexOf(tableFields, "AlarmLow");
fieldIndexes[7] = Array.IndexOf(tableFields, "AlarmEnabled");
if (!fieldIndexes.Any(n => n < 0)) // Check if any indexes are negative (missing)
{
using (DataContext dataContext = new DataContext())
{
TableOperations <HourOfWeekLimit> table = dataContext.Table <HourOfWeekLimit>();
for (int i = 1; i < csvRows.Length; ++i)
{
string[] row = csvRows[i].Split(',');
HourOfWeekLimit newRecord = new HourOfWeekLimit()
{
ID = int.Parse(row[fieldIndexes[0]]),
ChannelID = int.Parse(row[fieldIndexes[1]]),
AlarmTypeID = int.Parse(row[fieldIndexes[2]]),
HourOfWeek = int.Parse(row[fieldIndexes[3]]),
Severity = int.Parse(row[fieldIndexes[4]]),
High = float.Parse(row[fieldIndexes[5]]),
Low = float.Parse(row[fieldIndexes[6]]),
Enabled = int.Parse(row[fieldIndexes[7]])
};
table.UpdateRecord(newRecord);
}
}
}
}
else if (referrer.Contains("ChannelsWithNormalLimits.cshtml") || referrer.Contains("MetersWithNormalLimits.cshtml"))
{
int channelIdIndex = Array.IndexOf(tableFields, "ChannelID");
int highIndex = Array.IndexOf(tableFields, "ChannelHigh");
int lowIndex = Array.IndexOf(tableFields, "ChannelLow");
using (DataContext dataContext = new DataContext())
{
TableOperations <AlarmRangeLimit> table = dataContext.Table <AlarmRangeLimit>();
for (int i = 1; i < csvRows.Length; ++i)
{
string[] row = csvRows[i].Split(',');
AlarmRangeLimit record = table.QueryRecordWhere("ChannelID = {0}", int.Parse(row[channelIdIndex]));
if (record == null)
{
continue;
}
record.High = float.Parse(row[highIndex]);
record.Low = float.Parse(row[lowIndex]);
table.UpdateRecord(record);
}
}
}
}
catch (Exception e)
{
LogExceptionHandler?.Invoke(e);
throw;
}
}
public void ProcessSmartAlarmsNormal(IEnumerable <int> meterIds, IEnumerable <int> typeIds, DateTime startDate, DateTime endDate, int sigmaLevel, int decimals, bool ignoreLargeValues, bool overwriteOldAlarms, int largeValueLevel)
{
int progressTotal = (meterIds.Any() ? meterIds.Count() : 1);
int progressCount = 0;
ProgressUpdatedOverall("", (int)(100 * (progressCount) / progressTotal));
string historianServer = DataContext.Connection.ExecuteScalar <string>("SELECT Value FROM Setting WHERE Name = 'Historian.Server'") ?? "127.0.0.1";
string historianInstance = DataContext.Connection.ExecuteScalar <string>("SELECT Value FROM Setting WHERE Name = 'Historian.Instance'") ?? "XDA";
foreach (int meterId in meterIds)
{
string characteristicList = "(" + string.Join(",", typeIds) + ")";
IEnumerable <int> channelIds = DataContext.Table <Channel>().QueryRecordsWhere("MeterID = {0} AND MeasurementCharacteristicID IN " + characteristicList, meterId).Select(x => x.ID);
string meterName = DataContext.Connection.ExecuteScalar <string>("Select Name from Meter where ID = {0}", meterId);
ProgressUpdatedOverall(meterName, (int)(100 * (progressCount) / progressTotal));
List <TrendingData> trendingData = new List <TrendingData>();
List <RunningAvgStdDev> normalRunningData = new List <RunningAvgStdDev>();
ProgressUpdatedByMeter("Querying openHistorian...", 0);
using (openHistorian.XDALink.Historian historian = new Historian(historianServer, historianInstance))
{
foreach (openHistorian.XDALink.TrendingDataPoint point in historian.Read(channelIds, startDate, endDate))
{
RunningAvgStdDev normalRecord = normalRunningData.FirstOrDefault(x => x.ChannelID == point.ChannelID);
if (normalRecord == null)
{
normalRecord = new RunningAvgStdDev()
{
ChannelID = point.ChannelID,
Count = 0,
Sum = 0,
SumOfSquares = 0
};
normalRunningData.Add(normalRecord);
}
if (point.SeriesID.ToString() == "Average")
{
normalRecord.Sum += point.Value;
normalRecord.SumOfSquares += (point.Value * point.Value);
++normalRecord.Count;
}
}
if (ignoreLargeValues)
{
normalRunningData = normalRunningData.Select(x =>
{
double average = x.Sum / (x.Count != 0 ? x.Count : 1);
x.FirstPassStdDev = Math.Sqrt(Math.Abs((x.SumOfSquares - 2 * average * x.Sum + x.Count * average * average) / ((x.Count != 1 ? x.Count : 2) - 1)));
x.Count = 0;
x.Sum = 0;
x.SumOfSquares = 0;
return(x);
}).ToList();
ProgressUpdatedByMeter("Querying openHistorian for second pass...", 0);
foreach (openHistorian.XDALink.TrendingDataPoint point in historian.Read(channelIds, startDate, endDate))
{
int hourOfWeek = (int)point.Timestamp.DayOfWeek * 24 + point.Timestamp.Hour;
RunningAvgStdDev normalRecord = normalRunningData.FirstOrDefault(x => x.ChannelID == point.ChannelID);
if ((point.SeriesID.ToString() == "Average" && point.Value > (normalRecord.FirstPassStdDev * largeValueLevel)) || (point.SeriesID.ToString() == "Average" && point.Value < (normalRecord.FirstPassStdDev * largeValueLevel)))
{
continue;
}
if (point.SeriesID.ToString() == "Average")
{
normalRecord.Sum += point.Value;
normalRecord.SumOfSquares += (point.Value * point.Value);
++normalRecord.Count;
}
}
}
}
int innerProgressTotal = (channelIds.Any() ? channelIds.Count() : 1);
int innerProgressCount = 0;
foreach (int channelId in channelIds)
{
string channelName = DataContext.Connection.ExecuteScalar <string>("Select Name from Channel where ID = {0}", channelId);
ProgressUpdatedByMeter(channelName, (int)(100 * (innerProgressCount) / innerProgressTotal));
RunningAvgStdDev record = normalRunningData.Where(x => x.ChannelID == channelId).FirstOrDefault();
if (record != null)
{
double average = record.Sum / (record.Count != 0 ? record.Count : 1);
double stdDev = Math.Sqrt(Math.Abs((record.SumOfSquares - 2 * average * record.Sum + record.Count * average * average) / ((record.Count != 1 ? record.Count : 2) - 1)));
float high = (float)Math.Round(average + stdDev * sigmaLevel, decimals);
float low = (float)Math.Round(average - stdDev * sigmaLevel, decimals);
AlarmRangeLimit hwl = DataContext.Table <AlarmRangeLimit>().QueryRecordWhere("ChannelID = {0}", record.ChannelID);
if (hwl == null)
{
AlarmRangeLimit newRecord = new AlarmRangeLimit()
{
ChannelID = record.ChannelID,
AlarmTypeID = 5,
Severity = 1,
High = high,
Low = low,
RangeInclusive = 0,
PerUnit = 0,
Enabled = 1,
IsDefault = false
};
DataContext.Table <AlarmRangeLimit>().AddNewRecord(newRecord);
}
else if (hwl != null && overwriteOldAlarms)
{
hwl.High = high;
hwl.Low = low;
DataContext.Table <AlarmRangeLimit>().UpdateRecord(hwl);
}
}
ProgressUpdatedByMeter(channelName, (int)(100 * (++innerProgressCount) / innerProgressTotal));
}
ProgressUpdatedOverall(meterName, (int)(100 * (++progressCount) / progressTotal));
}
}