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