/// <summary>
/// Gets an operation description for an alarm.
/// </summary>
/// <param name="alarm">Alarm to derive description for.</param>
/// <param name="tagName">Label of associated measurement.</param>
/// <returns>Derived label.</returns>
public static string GetOperationDescription(Alarm alarm, string tagName)
{
StringBuilder description;
description = new StringBuilder(tagName);
AlarmOperation operation = alarm.Operation.GetEnumValueOrDefault<AlarmOperation>(AlarmOperation.Equal);
switch (operation)
{
case AlarmOperation.Equal:
description.Append(" = ");
break;
case AlarmOperation.NotEqual:
description.Append(" != ");
break;
case AlarmOperation.GreaterOrEqual:
description.Append(" >= ");
break;
case AlarmOperation.LessOrEqual:
description.Append(" <= ");
break;
case AlarmOperation.GreaterThan:
description.Append(" > ");
break;
case AlarmOperation.LessThan:
description.Append(" < ");
break;
case AlarmOperation.Flatline:
if ((object)alarm.Delay == null)
return string.Empty;
description.Append(" flat-lined for ");
description.Append(alarm.Delay);
description.Append(" seconds");
return description.ToString();
default:
description.Append(operation.GetDescription());
break;
}
if (alarm.SetPoint.HasValue)
description.Append(alarm.SetPoint);
else
description.Append("undefined");
return description.ToString();
}
// Creates a data model based "RaisedAlarm" from an alarm
private RaisedAlarm CreateRaisedAlarm(Alarm alarm)
{
return new RaisedAlarm
{
ID = alarm.ID,
Severity = (int)alarm.Severity,
TimeRaised = ((DateTime)alarm.TimeRaised).ToString("MM/dd/yyyy HH:mm:ss.fff"),
TagName = alarm.TagName,
Description = alarm.Description
};
}
/// <summary>
/// Saves <see cref="Alarm"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="alarm">Information about <see cref="Alarm"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, Alarm alarm)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
string updateQuery;
Alarm createdAlarm = alarm;
string successMessage = "Alarm information saved successfully";
object associatedMeasurementId = (alarm.AssociatedMeasurementID != null) ? database.Guid(alarm.AssociatedMeasurementID.Value) : DBNull.Value;
AlarmMonitor monitor = AlarmMonitor.Default;
if (alarm.SetPointEnabled && (object)alarm.SetPoint == null)
throw new InvalidOperationException($"Set Point is required for alarms of type \"{((AlarmOperation)alarm.Operation).GetDescription()}\". Please define a Set Point.");
if (alarm.ToleranceEnabled && (object)alarm.Tolerance == null)
throw new InvalidOperationException($"Tolerance is required for alarms of type \"{((AlarmOperation)alarm.Operation).GetDescription()}\". Please define a Tolerance.");
if (alarm.DelayEnabled && (object)alarm.Delay == null)
throw new InvalidOperationException($"Delay is required for alarms of type \"{((AlarmOperation)alarm.Operation).GetDescription()}\". Please define a Delay.");
if (alarm.HysteresisEnabled && (object)alarm.Hysteresis == null)
throw new InvalidOperationException($"Hysteresis is required for alarms of type \"{((AlarmOperation)alarm.Operation).GetDescription()}\". Please define a Hysteresis.");
if (alarm.ID == 0)
{
string query = database.ParameterizedQueryString("INSERT INTO Alarm (NodeID, TagName, SignalID, AssociatedMeasurementID, Description, Severity, Operation, SetPoint, Tolerance, Delay, " +
"Hysteresis, LoadOrder, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) Values ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15}, {16})",
"nodeID", "tagName", "signalId", "associatedMeasurementId", "description", "severity", "operation", "setPoint", "tolerance", "delay",
"hysteresis", "loadOrder", "enabled", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, (alarm.NodeID != Guid.Empty) ? database.Guid(alarm.NodeID) : database.CurrentNodeID(),
alarm.TagName.ToNotNull(), database.Guid(alarm.SignalID), associatedMeasurementId, alarm.Description.ToNotNull(), alarm.Severity, alarm.Operation, alarm.SetPoint.ToNotNull(),
alarm.Tolerance.ToNotNull(), alarm.Delay.ToNotNull(), alarm.Hysteresis.ToNotNull(), alarm.LoadOrder, database.Bool(alarm.Enabled), CommonFunctions.CurrentUser, database.UtcNow,
CommonFunctions.CurrentUser, database.UtcNow);
createdAlarm = GetAlarm(database, string.Format("WHERE TagName = '{0}'", alarm.TagName));
}
else
{
string query = database.ParameterizedQueryString("UPDATE Alarm SET NodeID = {0}, TagName = {1}, SignalID = {2}, AssociatedMeasurementID = {3}, Description = {4}, Severity = {5}, " +
"Operation = {6}, SetPoint = {7}, Tolerance = {8}, Delay = {9}, Hysteresis = {10}, LoadOrder = {11}, Enabled = {12}, UpdatedBy = {13}, UpdatedOn = {14} WHERE ID = {15}",
"nodeID", "tagName", "signalId", "associatedMeasurementId", "description", "severity", "operation", "setPoint", "tolerance", "delay", "hysteresis",
"loadOrder", "enabled", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, (alarm.NodeID != Guid.Empty) ? database.Guid(alarm.NodeID) : database.CurrentNodeID(),
alarm.TagName, database.Guid(alarm.SignalID), associatedMeasurementId, alarm.Description.ToNotNull(), alarm.Severity, alarm.Operation,
alarm.SetPoint.ToNotNull(), alarm.Tolerance.ToNotNull(), alarm.Delay.ToNotNull(), alarm.Hysteresis.ToNotNull(), alarm.LoadOrder, database.Bool(alarm.Enabled),
CommonFunctions.CurrentUser, database.UtcNow, alarm.ID);
}
updateQuery = database.ParameterizedQueryString("UPDATE Alarm SET AssociatedMeasurementID = {0} WHERE ID = {1}", "associatedMeasurementId", "id");
if (alarm.CreateAssociatedMeasurement && (object)alarm.AssociatedMeasurementID == null)
{
alarm.AssociatedMeasurementID = CreateAlarmMeasurement(database, createdAlarm);
if ((object)alarm.AssociatedMeasurementID != null)
database.Connection.ExecuteNonQuery(updateQuery, DefaultTimeout, database.Guid(alarm.AssociatedMeasurementID.Value), createdAlarm.ID);
else
successMessage += " but failed to create associated measurement";
}
else if (!alarm.CreateAssociatedMeasurement && (object)alarm.AssociatedMeasurementID != null)
{
database.Connection.ExecuteNonQuery(updateQuery, DefaultTimeout, DBNull.Value, createdAlarm.ID);
DeleteAlarmMeasurement(database, createdAlarm.AssociatedMeasurementID.Value);
alarm.AssociatedMeasurementID = null;
}
if ((object)monitor != null)
monitor.UpdateDefinedAlarms();
try
{
CommonFunctions.SendCommandToService("ReloadConfig");
}
catch (Exception ex)
{
CommonFunctions.LogException(database, "Alarm Save", ex);
}
return successMessage;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
// Creates a measurement associated with the given alarm and returns the new measurements signal ID.
private static Guid? CreateAlarmMeasurement(AdoDataConnection database, Alarm alarm)
{
object nodeID;
Historian historian;
Measurement alarmMeasurement;
int signalTypeId;
try
{
nodeID = (alarm.NodeID != Guid.Empty) ? database.Guid(alarm.NodeID) : database.CurrentNodeID();
historian = Historian.GetHistorian(database, string.Format("WHERE Acronym = 'STAT' AND NodeID = '{0}'", nodeID));
signalTypeId = Convert.ToInt32(database.Connection.ExecuteScalar("SELECT ID FROM SignalType WHERE Acronym = 'ALRM'", DefaultTimeout));
alarmMeasurement = new Measurement()
{
HistorianID = historian.ID,
PointTag = alarm.TagName,
SignalTypeID = signalTypeId,
SignalReference = "ALARM!SERVICES-AL" + alarm.ID,
Description = "Measurement associated with alarm " + alarm.ID,
Internal = true,
Enabled = true
};
Measurement.Save(database, alarmMeasurement);
alarmMeasurement = Measurement.GetMeasurement(database, string.Format("WHERE PointTag = '{0}'", alarm.TagName));
return alarmMeasurement.SignalID;
}
catch
{
// Return null to indicate measurement
// was not saved to database
return null;
}
}
/// <summary>
/// Retrieves an <see cref="Alarm"/> information from the database based on query string filter.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="whereClause">Filter clause to append to the SELECT query.</param>
/// <returns><see cref="Alarm"/> information.</returns>
public static Alarm GetAlarm(AdoDataConnection database, string whereClause)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
DataTable alarmTable = database.Connection.RetrieveData(database.AdapterType, "SELECT * FROM Alarm " + whereClause);
if (alarmTable.Rows.Count == 0)
return null;
DataRow row = alarmTable.Rows[0];
object associatedMeasurementId = row.Field<object>("AssociatedMeasurementID");
Alarm alarm = new Alarm
{
NodeID = database.Guid(row, "NodeID"),
ID = row.ConvertField<int>("ID"),
TagName = row.Field<string>("TagName"),
SignalID = database.Guid(row, "SignalID"),
AssociatedMeasurementID = ((object)associatedMeasurementId != null) ? Guid.Parse(associatedMeasurementId.ToString()) : (Guid?)null,
Description = row.Field<string>("Description"),
Severity = row.ConvertField<int>("Severity"),
Operation = row.ConvertField<int>("Operation"),
SetPoint = row.ConvertNullableField<double>("SetPoint"),
Tolerance = row.ConvertNullableField<double>("Tolerance"),
Delay = row.ConvertNullableField<double>("Delay"),
Hysteresis = row.ConvertNullableField<double>("Hysteresis"),
LoadOrder = row.ConvertField<int>("LoadOrder"),
Enabled = row.ConvertField<bool>("Enabled")
};
return alarm;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Loads <see cref="Alarm"/> information as an <see cref="ObservableCollection{T}"/> style list.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="keys">Keys of the adapters to be loaded from the database.</param>
/// <returns>Collection of <see cref="Alarm"/>.</returns>
public static ObservableCollection<Alarm> Load(AdoDataConnection database, IList<int> keys)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
string query;
string commaSeparatedKeys;
Alarm[] alarmList = null;
DataTable alarmTable;
object associatedMeasurementId;
int id;
if ((object)keys != null && keys.Count > 0)
{
commaSeparatedKeys = keys.Select(key => "" + key.ToString() + "").Aggregate((str1, str2) => str1 + "," + str2);
query = database.ParameterizedQueryString(string.Format("SELECT NodeID, TagName, ID, SignalID, AssociatedMeasurementID, Description, Severity, Operation, " +
"SetPoint, Tolerance, Delay, Hysteresis, LoadOrder, Enabled FROM Alarm WHERE NodeID = {{0}} AND ID IN ({0})", commaSeparatedKeys), "nodeID");
alarmTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID());
alarmList = new Alarm[alarmTable.Rows.Count];
foreach (DataRow row in alarmTable.Rows)
{
id = row.ConvertField<int>("ID");
associatedMeasurementId = row.Field<object>("AssociatedMeasurementID");
alarmList[keys.IndexOf(id)] = new Alarm()
{
NodeID = database.Guid(row, "NodeID"),
ID = id,
TagName = row.Field<string>("TagName"),
SignalID = database.Guid(row, "SignalID"),
AssociatedMeasurementID = (associatedMeasurementId != null) ? Guid.Parse(associatedMeasurementId.ToString()) : (Guid?)null,
Description = row.Field<string>("Description"),
Severity = row.ConvertField<int>("Severity"),
Operation = row.ConvertField<int>("Operation"),
SetPoint = row.ConvertNullableField<double>("SetPoint"),
Tolerance = row.ConvertNullableField<double>("Tolerance"),
Delay = row.ConvertNullableField<double>("Delay"),
Hysteresis = row.ConvertNullableField<double>("Hysteresis"),
LoadOrder = row.ConvertField<int>("LoadOrder"),
Enabled = row.ConvertField<bool>("Enabled"),
CreateAssociatedMeasurement = (associatedMeasurementId != null)
};
}
}
return new ObservableCollection<Alarm>(alarmList ?? new Alarm[0]);
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Gets an operation description for an alarm.
/// </summary>
/// <param name="alarm">Alarm to derive description for.</param>
/// <param name="tagName">Label of associated measurement.</param>
/// <returns>Derived label.</returns>
public static string GetOperationDescription(Alarm alarm, string tagName)
{
StringBuilder description;
description = new StringBuilder(tagName);
AlarmOperation operation = alarm.Operation.GetEnumValueOrDefault <AlarmOperation>(AlarmOperation.Equal);
switch (operation)
{
case AlarmOperation.Equal:
description.Append(" = ");
break;
case AlarmOperation.NotEqual:
description.Append(" != ");
break;
case AlarmOperation.GreaterOrEqual:
description.Append(" >= ");
break;
case AlarmOperation.LessOrEqual:
description.Append(" <= ");
break;
case AlarmOperation.GreaterThan:
description.Append(" > ");
break;
case AlarmOperation.LessThan:
description.Append(" < ");
break;
case AlarmOperation.Flatline:
if ((object)alarm.Delay == null)
{
return(string.Empty);
}
description.Append(" flat-lined for ");
description.Append(alarm.Delay);
description.Append(" seconds");
return(description.ToString());
default:
description.Append(operation.GetDescription());
break;
}
if (alarm.SetPoint.HasValue)
{
description.Append(alarm.SetPoint);
}
else
{
description.Append("undefined");
}
return(description.ToString());
}
/// <summary>
/// Saves <see cref="Alarm"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="alarm">Information about <see cref="Alarm"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, Alarm alarm)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
string updateQuery;
Alarm createdAlarm = alarm;
string successMessage = "Alarm information saved successfully";
object associatedMeasurementId = (alarm.AssociatedMeasurementID != null) ? database.Guid(alarm.AssociatedMeasurementID.Value) : DBNull.Value;
AlarmMonitor monitor = AlarmMonitor.Default;
if (alarm.SetPointEnabled && (object)alarm.SetPoint == null)
{
throw new InvalidOperationException($"Set Point is required for alarms of type \"{((AlarmOperation)alarm.Operation).GetDescription()}\". Please define a Set Point.");
}
if (alarm.ToleranceEnabled && (object)alarm.Tolerance == null)
{
throw new InvalidOperationException($"Tolerance is required for alarms of type \"{((AlarmOperation)alarm.Operation).GetDescription()}\". Please define a Tolerance.");
}
if (alarm.DelayEnabled && (object)alarm.Delay == null)
{
throw new InvalidOperationException($"Delay is required for alarms of type \"{((AlarmOperation)alarm.Operation).GetDescription()}\". Please define a Delay.");
}
if (alarm.HysteresisEnabled && (object)alarm.Hysteresis == null)
{
throw new InvalidOperationException($"Hysteresis is required for alarms of type \"{((AlarmOperation)alarm.Operation).GetDescription()}\". Please define a Hysteresis.");
}
if (alarm.ID == 0)
{
string query = database.ParameterizedQueryString("INSERT INTO Alarm (NodeID, TagName, SignalID, AssociatedMeasurementID, Description, Severity, Operation, SetPoint, Tolerance, Delay, " +
"Hysteresis, LoadOrder, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) Values ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15}, {16})",
"nodeID", "tagName", "signalId", "associatedMeasurementId", "description", "severity", "operation", "setPoint", "tolerance", "delay",
"hysteresis", "loadOrder", "enabled", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, (alarm.NodeID != Guid.Empty) ? database.Guid(alarm.NodeID) : database.CurrentNodeID(),
alarm.TagName.ToNotNull(), database.Guid(alarm.SignalID), associatedMeasurementId, alarm.Description.ToNotNull(), alarm.Severity, alarm.Operation, alarm.SetPoint.ToNotNull(),
alarm.Tolerance.ToNotNull(), alarm.Delay.ToNotNull(), alarm.Hysteresis.ToNotNull(), alarm.LoadOrder, database.Bool(alarm.Enabled), CommonFunctions.CurrentUser, database.UtcNow,
CommonFunctions.CurrentUser, database.UtcNow);
createdAlarm = GetAlarm(database, string.Format("WHERE TagName = '{0}'", alarm.TagName));
}
else
{
string query = database.ParameterizedQueryString("UPDATE Alarm SET NodeID = {0}, TagName = {1}, SignalID = {2}, AssociatedMeasurementID = {3}, Description = {4}, Severity = {5}, " +
"Operation = {6}, SetPoint = {7}, Tolerance = {8}, Delay = {9}, Hysteresis = {10}, LoadOrder = {11}, Enabled = {12}, UpdatedBy = {13}, UpdatedOn = {14} WHERE ID = {15}",
"nodeID", "tagName", "signalId", "associatedMeasurementId", "description", "severity", "operation", "setPoint", "tolerance", "delay", "hysteresis",
"loadOrder", "enabled", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, (alarm.NodeID != Guid.Empty) ? database.Guid(alarm.NodeID) : database.CurrentNodeID(),
alarm.TagName, database.Guid(alarm.SignalID), associatedMeasurementId, alarm.Description.ToNotNull(), alarm.Severity, alarm.Operation,
alarm.SetPoint.ToNotNull(), alarm.Tolerance.ToNotNull(), alarm.Delay.ToNotNull(), alarm.Hysteresis.ToNotNull(), alarm.LoadOrder, database.Bool(alarm.Enabled),
CommonFunctions.CurrentUser, database.UtcNow, alarm.ID);
}
updateQuery = database.ParameterizedQueryString("UPDATE Alarm SET AssociatedMeasurementID = {0} WHERE ID = {1}", "associatedMeasurementId", "id");
if (alarm.CreateAssociatedMeasurement && (object)alarm.AssociatedMeasurementID == null)
{
alarm.AssociatedMeasurementID = CreateAlarmMeasurement(database, createdAlarm);
if ((object)alarm.AssociatedMeasurementID != null)
{
database.Connection.ExecuteNonQuery(updateQuery, DefaultTimeout, database.Guid(alarm.AssociatedMeasurementID.Value), createdAlarm.ID);
}
else
{
successMessage += " but failed to create associated measurement";
}
}
else if (!alarm.CreateAssociatedMeasurement && (object)alarm.AssociatedMeasurementID != null)
{
database.Connection.ExecuteNonQuery(updateQuery, DefaultTimeout, DBNull.Value, createdAlarm.ID);
DeleteAlarmMeasurement(database, createdAlarm.AssociatedMeasurementID.Value);
alarm.AssociatedMeasurementID = null;
}
if ((object)monitor != null)
{
monitor.UpdateDefinedAlarms();
}
try
{
CommonFunctions.SendCommandToService("ReloadConfig");
}
catch (Exception ex)
{
CommonFunctions.LogException(database, "Alarm Save", ex);
}
return(successMessage);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Loads <see cref="Alarm"/> information as an <see cref="ObservableCollection{T}"/> style list.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="keys">Keys of the adapters to be loaded from the database.</param>
/// <returns>Collection of <see cref="Alarm"/>.</returns>
public static ObservableCollection <Alarm> Load(AdoDataConnection database, IList <int> keys)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
string query;
string commaSeparatedKeys;
Alarm[] alarmList = null;
DataTable alarmTable;
object associatedMeasurementId;
int id;
if ((object)keys != null && keys.Count > 0)
{
commaSeparatedKeys = keys.Select(key => "" + key.ToString() + "").Aggregate((str1, str2) => str1 + "," + str2);
query = database.ParameterizedQueryString(string.Format("SELECT NodeID, TagName, ID, SignalID, AssociatedMeasurementID, Description, Severity, Operation, " +
"SetPoint, Tolerance, Delay, Hysteresis, LoadOrder, Enabled FROM Alarm WHERE NodeID = {{0}} AND ID IN ({0})", commaSeparatedKeys), "nodeID");
alarmTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID());
alarmList = new Alarm[alarmTable.Rows.Count];
foreach (DataRow row in alarmTable.Rows)
{
id = row.ConvertField <int>("ID");
associatedMeasurementId = row.Field <object>("AssociatedMeasurementID");
alarmList[keys.IndexOf(id)] = new Alarm()
{
NodeID = database.Guid(row, "NodeID"),
ID = id,
TagName = row.Field <string>("TagName"),
SignalID = database.Guid(row, "SignalID"),
AssociatedMeasurementID = (associatedMeasurementId != null) ? Guid.Parse(associatedMeasurementId.ToString()) : (Guid?)null,
Description = row.Field <string>("Description"),
Severity = row.ConvertField <int>("Severity"),
Operation = row.ConvertField <int>("Operation"),
SetPoint = row.ConvertNullableField <double>("SetPoint"),
Tolerance = row.ConvertNullableField <double>("Tolerance"),
Delay = row.ConvertNullableField <double>("Delay"),
Hysteresis = row.ConvertNullableField <double>("Hysteresis"),
LoadOrder = row.ConvertField <int>("LoadOrder"),
Enabled = row.ConvertField <bool>("Enabled"),
CreateAssociatedMeasurement = (associatedMeasurementId != null)
};
}
}
return(new ObservableCollection <Alarm>(alarmList ?? new Alarm[0]));
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}