private async Task ProcessFileAsync(HttpContent file)
{
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[] { '[', ']' });
}
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 = double.Parse(row[fieldIndexes[5]]),
Low = double.Parse(row[fieldIndexes[6]]),
Enabled = int.Parse(row[fieldIndexes[7]])
};
table.UpdateRecord(newRecord);
}
}
}
}
catch (Exception e)
{
LogExceptionHandler?.Invoke(e);
throw;
}
}
public void AddNewOrUpdateHourOfWeekLimit(HourOfWeekLimit record)
{
DataContext.Table <HourOfWeekLimit>().AddNewOrUpdateRecord(record);
}
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 ProcessSmartAlarms(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> runningData = 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))
{
int hourOfWeek = (int)point.Timestamp.DayOfWeek * 24 + point.Timestamp.Hour;
RunningAvgStdDev record = runningData.FirstOrDefault(x => x.ChannelID == point.ChannelID && x.HourOfWeek == hourOfWeek);
if (record == null)
{
record = new RunningAvgStdDev()
{
ChannelID = point.ChannelID,
Count = 0,
Sum = 0,
SumOfSquares = 0
};
runningData.Add(record);
}
if (point.SeriesID.ToString() == "Average")
{
record.Sum += point.Value;
record.SumOfSquares += (point.Value * point.Value);
++record.Count;
}
}
if (ignoreLargeValues)
{
runningData = runningData.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.Sum = 0;
x.SumOfSquares = 0;
x.Count = 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 record = runningData.FirstOrDefault(x => x.ChannelID == point.ChannelID && x.HourOfWeek == hourOfWeek);
if ((point.SeriesID.ToString() == "Average" && point.Value > (record.FirstPassStdDev * largeValueLevel)) || (point.SeriesID.ToString() == "Average" && point.Value < (record.FirstPassStdDev * largeValueLevel)))
{
continue;
}
if (record == null)
{
record = new RunningAvgStdDev()
{
ChannelID = point.ChannelID,
Count = 0,
Sum = 0,
SumOfSquares = 0
};
runningData.Add(record);
}
if (point.SeriesID.ToString() == "Average")
{
record.Sum += point.Value;
record.SumOfSquares += (point.Value * point.Value);
++record.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));
foreach (RunningAvgStdDev data in runningData.Where(x => x.ChannelID == channelId))
{
double average = data.Sum / (data.Count != 0 ? data.Count : 1);
double stdDev = Math.Sqrt(Math.Abs((data.SumOfSquares - 2 * average * data.Sum + data.Count * average * average) / ((data.Count != 1 ? data.Count : 2) - 1)));
float high = (float)Math.Round(average + stdDev * sigmaLevel, decimals);
float low = (float)Math.Round(average - stdDev * sigmaLevel, decimals);
HourOfWeekLimit hwl = DataContext.Table <HourOfWeekLimit>().QueryRecordWhere("ChannelID = {0} AND HourOfWeek = {1}", data.ChannelID, data.HourOfWeek);
if (hwl == null)
{
HourOfWeekLimit newrecord = new HourOfWeekLimit()
{
ChannelID = data.ChannelID,
AlarmTypeID = 4,
HourOfWeek = data.HourOfWeek,
Severity = 1,
High = high,
Low = low,
Enabled = 1
};
DataContext.Table <HourOfWeekLimit>().AddNewRecord(newrecord);
}
else if (hwl != null && overwriteOldAlarms)
{
hwl.ChannelID = data.ChannelID;
hwl.AlarmTypeID = 4;
hwl.HourOfWeek = data.HourOfWeek;
hwl.Severity = 1;
hwl.High = high;
hwl.Low = low;
hwl.Enabled = 1;
DataContext.Table <HourOfWeekLimit>().UpdateRecord(hwl);
}
}
ProgressUpdatedByMeter(channelName, (int)(100 * (++innerProgressCount) / innerProgressTotal));
}
ProgressUpdatedOverall(meterName, (int)(100 * (++progressCount) / progressTotal));
}
}
