/// <summary>
/// Saves <see cref="OutputStreamDevice"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="outputStreamDevice">Information about <see cref="OutputStreamDevice"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, OutputStreamDevice outputStreamDevice)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (outputStreamDevice.ID == 0)
{
query = database.ParameterizedQueryString("INSERT INTO OutputStreamDevice (NodeID, AdapterID, IDCode, Acronym, BpaAcronym, Name, " +
"PhasorDataFormat, FrequencyDataFormat, AnalogDataFormat, CoordinateFormat, LoadOrder, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn)" +
"VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15})", "nodeID", "adapterID", "idCode", "acronym",
"bpaAcronym", "name", "phasorDataFormat", "frequencyDataFormat", "analogDataFormat", "coordinateFormat", "loadOrder", "enabled",
"updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), outputStreamDevice.AdapterID, outputStreamDevice.IDCode,
outputStreamDevice.Acronym, outputStreamDevice.BpaAcronym.ToNotNull(), outputStreamDevice.Name, outputStreamDevice.PhasorDataFormat.ToNotNull(),
outputStreamDevice.FrequencyDataFormat.ToNotNull(), outputStreamDevice.AnalogDataFormat.ToNotNull(), outputStreamDevice.CoordinateFormat.ToNotNull(),
outputStreamDevice.LoadOrder, database.Bool(outputStreamDevice.Enabled), CommonFunctions.CurrentUser,
database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow);
}
else
{
OutputStreamDevice originalDevice = GetOutputStreamDevice(database, "WHERE ID = " + outputStreamDevice.ID);
query = database.ParameterizedQueryString("UPDATE OutputStreamDevice SET NodeID = {0}, AdapterID = {1}, IDCode = {2}, Acronym = {3}, BpaAcronym = {4}, " +
"Name = {5}, PhasorDataFormat = {6}, FrequencyDataFormat = {7}, AnalogDataFormat = {8}, CoordinateFormat = {9}, LoadOrder = {10}, Enabled = {11}, " +
" UpdatedBy = {12}, UpdatedOn = {13} WHERE ID = {14}", "nodeID", "adapterID", "idCode", "acronym", "bpaAcronym", "name",
"phasorDataFormat", "frequencyDataFormat", "analogDataFormat", "coordinateFormat", "loadOrder", "enabled", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(outputStreamDevice.NodeID), outputStreamDevice.AdapterID, outputStreamDevice.IDCode,
outputStreamDevice.Acronym, outputStreamDevice.BpaAcronym.ToNotNull(), outputStreamDevice.Name, outputStreamDevice.PhasorDataFormat.ToNotNull(),
outputStreamDevice.FrequencyDataFormat.ToNotNull(), outputStreamDevice.AnalogDataFormat.ToNotNull(), outputStreamDevice.CoordinateFormat.ToNotNull(),
outputStreamDevice.LoadOrder, database.Bool(outputStreamDevice.Enabled), CommonFunctions.CurrentUser,
database.UtcNow, outputStreamDevice.ID);
if (originalDevice != null && originalDevice.Acronym != outputStreamDevice.Acronym)
{
IList <int> keys = OutputStreamMeasurement.LoadKeys(database, originalDevice.AdapterID);
foreach (OutputStreamMeasurement measurement in OutputStreamMeasurement.Load(database, keys))
{
measurement.SignalReference = measurement.SignalReference.Replace(originalDevice.Acronym + "-", outputStreamDevice.Acronym + "-");
OutputStreamMeasurement.Save(database, measurement);
}
}
}
return("OutputStreamDevice information saved successfully");
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Creates and saves new <see cref="OutputStreamMeasurement"/> into the database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="outputStreamID">ID of the output stream.</param>
/// <param name="measurements">Collection of measurements to be added.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string AddMeasurements(AdoDataConnection database, int outputStreamID, ObservableCollection <Measurement> measurements)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
foreach (Measurement measurement in measurements)
{
OutputStreamMeasurement outputStreamMeasurement = new OutputStreamMeasurement
{
NodeID = (Guid)database.CurrentNodeID(),
AdapterID = outputStreamID,
HistorianID = measurement.HistorianID,
PointID = measurement.PointID,
SignalReference = measurement.SignalReference
};
Save(database, outputStreamMeasurement);
}
return("Output stream measurements added successfully.");
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Method to add <see cref="Measurement"/>s to <see cref="MeasurementGroup"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="measurementGroupID">ID of the <see cref="MeasurementGroup"/> to add <see cref="Measurement"/> to.</param>
/// <param name="measurementsToBeAdded">List of <see cref="Measurement"/> signal ids to be added.</param>
/// <returns>string, indicating success for UI display.</returns>
public static string AddMeasurements(AdoDataConnection database, int measurementGroupID, List <Guid> measurementsToBeAdded)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
foreach (Guid id in measurementsToBeAdded)
{
query = database.ParameterizedQueryString("INSERT INTO MeasurementGroupMeasurement (NodeID, MeasurementGroupID, SignalID) VALUES ({0}, {1}, {2})", "nodeID", "measurementGroupID", "signalID");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), measurementGroupID, database.Guid(id));
}
return("Measurements added to group successfully");
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// LoadKeys <see cref="PowerCalculation"/> information as an <see cref="ObservableCollection{T}"/> style list.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <returns>Collection of <see cref="int"/>.</returns>
public static IList <int> LoadKeys(AdoDataConnection database)
{
var createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
var calculationList = new List <int>();
var queryFormat = "SELECT ID FROM PowerCalculation WHERE NodeID = {0}";
var calculationTable = database.RetrieveData(queryFormat, database.CurrentNodeID());
foreach (DataRow row in calculationTable.Rows)
{
calculationList.Add(row.ConvertField <int>("ID"));
}
return(calculationList);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Saves <see cref="Subscriber"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="subscriber">Information about <see cref="Subscriber"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, Subscriber subscriber)
{
bool createdConnection = false;
SslPolicyErrors validPolicyErrors;
X509ChainStatusFlags validChainFlags;
string query;
try
{
createdConnection = CreateConnection(ref database);
validPolicyErrors = (subscriber.ValidPolicyErrors ?? SslPolicyErrors.None) | (subscriber.RemoteCertificateIsSelfSigned ? SslPolicyErrors.RemoteCertificateChainErrors : SslPolicyErrors.None);
validChainFlags = (subscriber.ValidChainFlags ?? X509ChainStatusFlags.NoError) | (subscriber.RemoteCertificateIsSelfSigned ? X509ChainStatusFlags.UntrustedRoot : X509ChainStatusFlags.NoError);
if (subscriber.ID == Guid.Empty)
{
query = database.ParameterizedQueryString("INSERT INTO Subscriber (NodeID, Acronym, Name, SharedSecret, AuthKey, ValidIPAddresses, RemoteCertificateFile, ValidPolicyErrors, ValidChainFlags, " +
"AccessControlFilter, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, " +
"{13}, {14})", "nodeID", "acronym", "name", "sharedSecret", "authKey", "validIPAddresses", "remoteCertificateFile", "validPolicyErrors", "validChainFlags",
"accessControlFilter", "enabled", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), subscriber.Acronym, subscriber.Name.ToNotNull(), subscriber.SharedSecret.ToNotNull(),
subscriber.AuthKey.ToNotNull(), subscriber.ValidIPAddresses.ToNotNull(), subscriber.RemoteCertificateFile.ToNotNull(), validPolicyErrors.ToString(),
validChainFlags.ToString(), subscriber.AccessControlFilter.ToNotNull(), database.Bool(subscriber.Enabled), CommonFunctions.CurrentUser, database.UtcNow,
CommonFunctions.CurrentUser, database.UtcNow);
}
else
{
query = database.ParameterizedQueryString("UPDATE Subscriber SET NodeID = {0}, Acronym = {1}, Name = {2}, SharedSecret = {3}, AuthKey = {4}, ValidIPAddresses = {5}, RemoteCertificateFile = {6}, " +
"ValidPolicyErrors = {7}, ValidChainFlags = {8}, AccessControlFilter = {9}, Enabled = {10}, UpdatedBy = {11}, UpdatedOn = {12} WHERE ID = {13}", "nodeID",
"acronym", "name", "sharedSecret", "authKey", "validIPAddresses", "remoteCertificateFile", "validPolicyErrors", "validChainFlags", "accessControlFilter",
"enabled", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(subscriber.NodeID), subscriber.Acronym, subscriber.Name.ToNotNull(), subscriber.SharedSecret.ToNotNull(),
subscriber.AuthKey.ToNotNull(), subscriber.ValidIPAddresses.ToNotNull(), subscriber.RemoteCertificateFile.ToNotNull(), validPolicyErrors.ToString(),
validChainFlags.ToString(), subscriber.AccessControlFilter.ToNotNull(), database.Bool(subscriber.Enabled), CommonFunctions.CurrentUser, database.UtcNow,
database.Guid(subscriber.ID));
}
try
{
CommonFunctions.SendCommandToService("ReloadConfig");
}
catch (Exception ex)
{
return("Subscriber information saved successfully. Failed to send ReloadConfig command to backend service." + Environment.NewLine + ex.Message);
}
return("Subscriber information saved successfully");
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Saves <see cref="MeasurementGroup"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="measurementGroup">Information about <see cref="MeasurementGroup"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, MeasurementGroup measurementGroup)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (measurementGroup.ID == 0)
{
query = database.ParameterizedQueryString("INSERT INTO MeasurementGroup (NodeID, Name, Description, FilterExpression, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) " +
"VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7})", "nodeID", "name", "description", "filterExpression", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), measurementGroup.Name, measurementGroup.Description.ToNotNull(),
measurementGroup.FilterExpression.ToNotNull(), CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow);
}
else
{
query = database.ParameterizedQueryString("UPDATE MeasurementGroup SET NodeID = {0}, Name = {1}, Description = {2}, FilterExpression = {3}, " +
"UpdatedBy = {4}, UpdatedOn = {5} WHERE ID = {6}", "nodeID", "name", "description", "filterExpression", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), measurementGroup.Name, measurementGroup.Description.ToNotNull(),
measurementGroup.FilterExpression.ToNotNull(), CommonFunctions.CurrentUser, database.UtcNow, measurementGroup.ID);
}
return("Measurement group information saved successfully");
}
catch (Exception ex)
{
return(ex.Message);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Saves <see cref="OutputStreamDeviceAnalog"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="outputStreamDeviceAnalog">Information about <see cref="OutputStreamDeviceAnalog"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, OutputStreamDeviceAnalog outputStreamDeviceAnalog)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (outputStreamDeviceAnalog.ID == 0)
{
query = database.ParameterizedQueryString("INSERT INTO OutputStreamDeviceAnalog (NodeID, OutputStreamDeviceID, Label, Type, ScalingValue, LoadOrder, " +
"UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9})", "nodeID",
"outputStreamDeviceID", "label", "type", "scalingValue", "loadOrder", "updatedBy", "updatedOn", "createdBy", "createdOn");
// TypeName, "typeName",
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), outputStreamDeviceAnalog.OutputStreamDeviceID,
outputStreamDeviceAnalog.Label, outputStreamDeviceAnalog.Type, outputStreamDeviceAnalog.ScalingValue, outputStreamDeviceAnalog.LoadOrder,
CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow);
//outputStreamDeviceAnalog.TypeName,
}
else
{
query = database.ParameterizedQueryString("UPDATE OutputStreamDeviceAnalog SET NodeID = {0}, OutputStreamDeviceID = {1}, Label = {2}, Type = {3}, " +
"ScalingValue = {4}, LoadOrder = {5}, UpdatedBy = {6}, UpdatedOn = {7} WHERE ID = {8}", "nodeID", "outputStreamDeviceID",
"label", "type", "scalingValue", "loadOrder", "updatedBy", "updatedOn", "id");
// TypeName= {6}, "typeName",
database.Connection.ExecuteNonQuery(query, DefaultTimeout, outputStreamDeviceAnalog.NodeID, outputStreamDeviceAnalog.OutputStreamDeviceID,
outputStreamDeviceAnalog.Label, outputStreamDeviceAnalog.Type, outputStreamDeviceAnalog.ScalingValue, outputStreamDeviceAnalog.LoadOrder,
CommonFunctions.CurrentUser, database.UtcNow, outputStreamDeviceAnalog.ID);
}
// OutputStreamDeviceAnalog.TypeName,
return("OutputStreamDeviceAnalog information saved successfully");
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Saves <see cref="OutputStreamDevicePhasor"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="outputStreamDevicePhasor">Information about <see cref="OutputStreamDevicePhasor"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, OutputStreamDevicePhasor outputStreamDevicePhasor)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (outputStreamDevicePhasor.ID == 0)
{
query = database.ParameterizedQueryString("INSERT INTO OutputStreamDevicePhasor (NodeID, OutputStreamDeviceID, Label, Type, Phase, ScalingValue, " +
"LoadOrder, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10})", "nodeID",
"outputStreamDeviceID", "label", "type", "phase", "scalingValue", "loadOrder", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), outputStreamDevicePhasor.OutputStreamDeviceID,
outputStreamDevicePhasor.Label, outputStreamDevicePhasor.Type, outputStreamDevicePhasor.Phase, outputStreamDevicePhasor.ScalingValue,
outputStreamDevicePhasor.LoadOrder, CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow);
// PhasorType, PhaseType @phasorName, @phaseType, OutputStreamDevicePhasor.PhasorType, OutputStreamDevicePhasor.PhaseType
}
else
{
query = "UPDATE OutputStreamDevicePhasor SET NodeID = {0}, OutputStreamDeviceID = {1}, Label = {2}, Type = {3}, " +
"Phase = {4}, ScalingValue = {5}, LoadOrder = {6}, UpdatedBy = {7}, UpdatedOn = {8} WHERE ID = {9}";
database.ExecuteNonQuery(DefaultTimeout, query, outputStreamDevicePhasor.NodeID, outputStreamDevicePhasor.OutputStreamDeviceID,
outputStreamDevicePhasor.Label, outputStreamDevicePhasor.Type, outputStreamDevicePhasor.Phase, outputStreamDevicePhasor.ScalingValue,
outputStreamDevicePhasor.LoadOrder, CommonFunctions.CurrentUser, database.UtcNow, outputStreamDevicePhasor.ID);
//PhasorType = @typeName, PhaseType = @PhaseType" OutputStreamDevicePhasor.PhasorType, OutputStreamDevicePhasor.PhaseType,
}
return("OutputStreamDevicePhasor information saved successfully");
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Saves <see cref="Company"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="powerCalculation">Information about <see cref="PowerCalculation"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, PowerCalculation powerCalculation)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (powerCalculation.ID == 0)
{
query = "INSERT INTO PowerCalculation (CircuitDescription, VoltageAngleSignalID, VoltageMagSignalID, CurrentAngleSignalID, CurrentMagSignalID, ActivePowerOutputSignalID, ReactivePowerOutputSignalID, ApparentPowerOutputSignalID, Enabled, NodeID) " +
"VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9})";
database.ExecuteNonQuery(DefaultTimeout, query, powerCalculation.CircuitDescription, powerCalculation.VoltagePhasor.AngleMeasurement.SignalID,
powerCalculation.VoltagePhasor.MagnitudeMeasurement.SignalID, powerCalculation.CurrentPhasor.AngleMeasurement.SignalID, powerCalculation.CurrentPhasor.MagnitudeMeasurement.SignalID,
(object)powerCalculation.ActivePowerOutputMeasurement?.SignalID ?? DBNull.Value, (object)powerCalculation.ReactivePowerOutputMeasurement?.SignalID ?? DBNull.Value, (object)powerCalculation.ApparentPowerOutputMeasurement?.SignalID ?? DBNull.Value,
powerCalculation.Enabled, database.CurrentNodeID());
}
else
{
query = "UPDATE PowerCalculation SET CircuitDescription = {0}, VoltageAngleSignalID = {1}, VoltageMagSignalID = {2}, CurrentAngleSignalID = {3}, CurrentMagSignalID = {4}, " +
"activePowerOutputSignalID = {5}, ReactivePowerOutputSignalID = {6}, ApparentPowerOutputSignalID = {7}, Enabled = {8} WHERE ID = {9}";
database.ExecuteNonQuery(DefaultTimeout, query, powerCalculation.CircuitDescription,
database.Guid(powerCalculation.VoltagePhasor.AngleMeasurement.SignalID), powerCalculation.VoltagePhasor.MagnitudeMeasurement.SignalID, powerCalculation.CurrentPhasor.AngleMeasurement.SignalID, powerCalculation.CurrentPhasor.MagnitudeMeasurement.SignalID,
(object)powerCalculation.ActivePowerOutputMeasurement?.SignalID ?? DBNull.Value, (object)powerCalculation.ReactivePowerOutputMeasurement?.SignalID ?? DBNull.Value, (object)powerCalculation.ApparentPowerOutputMeasurement?.SignalID ?? DBNull.Value, powerCalculation.Enabled, powerCalculation.ID);
}
return("Power Calculation information saved successfully");
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Adds measurement groups to <see cref="Subscriber"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="subscriberID">ID of the <see cref="Subscriber"/> to which measurements to be added.</param>
/// <param name="measurementGroupsToBeAdded">List of <see cref="MeasurementGroup"/> IDs to be added.</param>
/// <param name="allowed">boolean flag to indicate if measurement groups are allowed or denied.</param>
/// <returns>string, indicating success for UI display.</returns>
public static string AddMeasurementGroups(AdoDataConnection database, Guid subscriberID, List <int> measurementGroupsToBeAdded, bool allowed)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
foreach (int id in measurementGroupsToBeAdded)
{
query = database.ParameterizedQueryString("INSERT INTO SubscriberMeasurementGroup (NodeID, SubscriberID, MeasurementGroupID, Allowed, UpdatedOn, " +
"UpdatedBy, CreatedOn, CreatedBy) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7})", "nodeID", "subscriberID", "measurementGroupID",
"allowed", "updatedOn", "updatedBy", "createdOn", "createdBy");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), database.Guid(subscriberID), id, database.Bool(allowed),
database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser);
}
if (allowed)
{
return("Measurement groups added to allowed measurement groups list for subscriber successfully");
}
else
{
return("Measurement groups added to denied measurement groups list for subscriber successfully");
}
}
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>
/// 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 a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Historian"/> information.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param>
/// <param name="includeStatHistorian">Indicates if statistical historian included in the collection.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> containing ID and Name of historians defined in the database.</returns>
public static Dictionary<int, string> GetLookupList(AdoDataConnection database, bool isOptional = false, bool includeStatHistorian = true)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary<int, string> historianList = new Dictionary<int, string>();
if (isOptional)
historianList.Add(0, "Select Historian");
string query = database.ParameterizedQueryString("SELECT ID, Acronym FROM Historian WHERE Enabled = {0} AND NodeID = {1} ORDER BY LoadOrder", "enabled", "nodeID");
DataTable historianTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Bool(true), database.CurrentNodeID());
foreach (DataRow row in historianTable.Rows)
{
if (!includeStatHistorian)
{
if (row.Field<string>("Acronym").ToUpper() != "STAT")
historianList[row.ConvertField<int>("ID")] = row.Field<string>("Acronym");
}
else
historianList[row.ConvertField<int>("ID")] = row.Field<string>("Acronym");
}
return historianList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Loads <see cref="Node"/> information as an <see cref="ObservableCollection{T}"/> style list.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <returns>Collection of <see cref="Subscriber"/>.</returns>
public static ObservableCollection <Subscriber> Load(AdoDataConnection database)
{
ObservableCollection <Subscriber> subscriberList;
DataTable subscriberTable;
bool createdConnection = false;
string query;
SslPolicyErrors validPolicyErrors;
X509ChainStatusFlags validChainFlags;
try
{
createdConnection = CreateConnection(ref database);
subscriberList = new ObservableCollection <Subscriber>();
query = database.ParameterizedQueryString("SELECT ID, NodeID, Acronym, Name, SharedSecret, AuthKey, ValidIPAddresses, RemoteCertificateFile," +
" ValidPolicyErrors, ValidChainFlags, AccessControlFilter, Enabled FROM Subscriber WHERE NodeID = {0} ORDER BY Name", "nodeID");
subscriberTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID());
foreach (DataRow row in subscriberTable.Rows)
{
subscriberList.Add(new Subscriber()
{
ID = database.Guid(row, "ID"),
NodeID = database.Guid(row, "NodeID"),
Acronym = row.Field <string>("Acronym"),
Name = row.Field <string>("Name"),
SharedSecret = row.Field <string>("SharedSecret"),
AuthKey = row.Field <string>("AuthKey"),
ValidIPAddresses = row.Field <string>("ValidIPAddresses"),
RemoteCertificateFile = row.Field <string>("RemoteCertificateFile"),
ValidPolicyErrors = Enum.TryParse(row.Field <string>("ValidPolicyErrors"), out validPolicyErrors) ? validPolicyErrors : (SslPolicyErrors?)null,
ValidChainFlags = Enum.TryParse(row.Field <string>("ValidChainFlags"), out validChainFlags) ? validChainFlags : (X509ChainStatusFlags?)null,
AccessControlFilter = row.Field <string>("AccessControlFilter"),
Enabled = Convert.ToBoolean(row.Field <object>("Enabled")),
AllowedMeasurementGroups = GetAllowedMeasurementGroups(database, database.Guid(row, "ID")),
DeniedMeasurementGroups = GetDeniedMeasurementGroups(database, database.Guid(row, "ID")),
AvailableMeasurementGroups = GetAvailableMeasurementGroups(database, database.Guid(row, "ID")),
StatusColor = "gray",
Version = ""
});
}
return(subscriberList);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Saves <see cref="Historian"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="historian">Infomration about <see cref="Historian"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, Historian historian)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (historian.ID == 0)
{
query = database.ParameterizedQueryString("INSERT INTO Historian (NodeID, Acronym, Name, AssemblyName, TypeName, ConnectionString, IsLocal, " +
"MeasurementReportingInterval, Description, LoadOrder, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, " +
"{5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14})", "nodeID", "acronym", "name", "assemblyName", "typeName", "connectionString", "isLocal",
"measurementReportingInterval", "description", "loadOrder", "enabled", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, (historian.NodeID != Guid.Empty) ? database.Guid(historian.NodeID) : database.CurrentNodeID(),
historian.Acronym.Replace(" ", "").ToUpper(), historian.Name.ToNotNull(), historian.AssemblyName.ToNotNull(),
historian.TypeName.ToNotNull(), historian.ConnectionString.ToNotNull(), database.Bool(historian.IsLocal), historian.MeasurementReportingInterval,
historian.Description.ToNotNull(), historian.LoadOrder, database.Bool(historian.Enabled), CommonFunctions.CurrentUser, database.UtcNow,
CommonFunctions.CurrentUser, database.UtcNow);
}
else
{
query = database.ParameterizedQueryString("UPDATE Historian SET NodeID = {0}, Acronym = {1}, Name = {2}, AssemblyName = {3}, TypeName = {4}, " +
"ConnectionString = {5}, IsLocal = {6}, MeasurementReportingInterval = {7}, Description = {8}, LoadOrder = {9}, Enabled = {10}, " +
"UpdatedBy = {11}, UpdatedOn = {12} WHERE ID = {13}", "nodeID", "acronym", "name", "assemblyName", "typeName", "connectionString",
"isLocal", "measurementReportingInterval", "description", "loadOrder", "enabled", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, (historian.NodeID != Guid.Empty) ? database.Guid(historian.NodeID) : database.CurrentNodeID(),
historian.Acronym.Replace(" ", "").ToUpper(), historian.Name.ToNotNull(), historian.AssemblyName.ToNotNull(), historian.TypeName.ToNotNull(),
historian.ConnectionString.ToNotNull(), database.Bool(historian.IsLocal), historian.MeasurementReportingInterval, historian.Description.ToNotNull(),
historian.LoadOrder, database.Bool(historian.Enabled), CommonFunctions.CurrentUser, database.UtcNow, historian.ID);
}
return "Historian information saved successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Saves <see cref="OutputStreamDeviceDigital"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="outputStreamDeviceDigital">Information about <see cref="OutputStreamDeviceDigital"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, OutputStreamDeviceDigital outputStreamDeviceDigital)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (outputStreamDeviceDigital.ID == 0)
{
query = database.ParameterizedQueryString("INSERT INTO OutputStreamDeviceDigital (NodeID, OutputStreamDeviceID, Label, MaskValue, LoadOrder, " +
"UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8})", "nodeID", "outputStreamDeviceID", "label",
"maskValue", "loadOrder", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), outputStreamDeviceDigital.OutputStreamDeviceID,
outputStreamDeviceDigital.Label, outputStreamDeviceDigital.MaskValue, outputStreamDeviceDigital.LoadOrder, CommonFunctions.CurrentUser,
database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow);
}
else
{
query = database.ParameterizedQueryString("UPDATE OutputStreamDeviceDigital SET NodeID = {0}, OutputStreamDeviceID = {1}, Label = {2}, MaskValue = {3}, " +
"LoadOrder = {4}, UpdatedBy = {5}, UpdatedOn = {6} WHERE ID = {7}", "nodeID", "outputStreamDeviceID", "label", "maskValue", "loadOrder", "updatedBy",
"updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, outputStreamDeviceDigital.NodeID, outputStreamDeviceDigital.OutputStreamDeviceID,
outputStreamDeviceDigital.Label, outputStreamDeviceDigital.MaskValue, outputStreamDeviceDigital.LoadOrder, CommonFunctions.CurrentUser,
database.UtcNow, outputStreamDeviceDigital.ID);
}
return "OutputStreamDeviceDigital information saved successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
// Static Methods
/// <summary>
/// Loads <see cref="Adapter"/> IDs as an <see cref="IList{T}"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="adapterType"><see cref="AdapterType"/> collection to be returned.</param>
/// <param name="sortMember">The field to sort by.</param>
/// <param name="sortDirection"><c>ASC</c> or <c>DESC</c> for ascending or descending respectively.</param>
/// <returns>Collection of <see cref="Int32"/>.</returns>
public static IList <int> LoadIDs(AdoDataConnection database, AdapterType adapterType, string sortMember, string sortDirection)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
string viewName;
if (adapterType == AdapterType.Action)
{
viewName = "CustomActionAdapterDetail";
}
else if (adapterType == AdapterType.Filter)
{
viewName = "CustomFilterAdapterDetail";
}
else if (adapterType == AdapterType.Input)
{
viewName = "CustomInputAdapterDetail";
}
else
{
viewName = "CustomOutputAdapterDetail";
}
IList <int> adapterList = new List <int>();
string sortClause = string.Empty;
DataTable adapterTable;
string query;
if (!string.IsNullOrEmpty(sortMember))
{
sortClause = string.Format("ORDER BY {0} {1}", sortMember, sortDirection);
}
query = database.ParameterizedQueryString(string.Format("SELECT ID FROM {0} WHERE NodeID = {{0}} {1}", viewName, sortClause), "nodeID");
adapterTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID());
foreach (DataRow row in adapterTable.Rows)
{
adapterList.Add(row.ConvertField <int>("ID"));
}
return(adapterList);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Saves <see cref="MeasurementGroup"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="measurementGroup">Information about <see cref="MeasurementGroup"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, MeasurementGroup measurementGroup)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (measurementGroup.ID == 0)
{
query = database.ParameterizedQueryString("INSERT INTO MeasurementGroup (NodeID, Name, Description, FilterExpression, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) " +
"VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7})", "nodeID", "name", "description", "filterExpression", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), measurementGroup.Name, measurementGroup.Description.ToNotNull(),
measurementGroup.FilterExpression.ToNotNull(), CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow);
}
else
{
query = database.ParameterizedQueryString("UPDATE MeasurementGroup SET NodeID = {0}, Name = {1}, Description = {2}, FilterExpression = {3}, " +
"UpdatedBy = {4}, UpdatedOn = {5} WHERE ID = {6}", "nodeID", "name", "description", "filterExpression", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), measurementGroup.Name, measurementGroup.Description.ToNotNull(),
measurementGroup.FilterExpression.ToNotNull(), CommonFunctions.CurrentUser, database.UtcNow, measurementGroup.ID);
}
return "Measurement group information saved successfully";
}
catch (Exception ex)
{
return ex.Message;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Retrieves a <see cref="Dictionary{T1,T2}"/> style list of <see cref="MeasurementGroup"/> information.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> containing ID and Name of <see cref="MeasurementGroup"/>s defined in the database.</returns>
public static Dictionary<int, string> GetLookupList(AdoDataConnection database, bool isOptional = true)
{
Dictionary<int, string> measurementGroupList;
DataTable measurementGroupTable;
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
measurementGroupList = new Dictionary<int, string>();
if (isOptional)
measurementGroupList.Add(0, "Select MeasurementGroup");
query = database.ParameterizedQueryString("SELECT ID, Name FROM MeasurementGroup WHERE NodeID = {0} ORDER BY SourceIndex", "nodeID");
measurementGroupTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID());
foreach (DataRow row in measurementGroupTable.Rows)
measurementGroupList[row.ConvertField<int>("ID")] = row.Field<string>("Name");
return measurementGroupList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
// Static Methods
/// <summary>
/// Loads <see cref="MeasurementGroup"/> information as an <see cref="ObservableCollection{T}"/> style list.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <returns>Collection of <see cref="MeasurementGroup"/>.</returns>
public static ObservableCollection<MeasurementGroup> Load(AdoDataConnection database)
{
ObservableCollection<MeasurementGroup> measurementGroupList;
DataTable measurementGroupTable;
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
measurementGroupList = new ObservableCollection<MeasurementGroup>();
query = database.ParameterizedQueryString("SELECT NodeID, ID, Name, Description, FilterExpression FROM MeasurementGroup WHERE NodeID = {0} ORDER BY Name", "nodeID");
measurementGroupTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID());
foreach (DataRow row in measurementGroupTable.Rows)
{
measurementGroupList.Add(new MeasurementGroup()
{
NodeID = database.Guid(row, "NodeID"),
ID = row.ConvertField<int>("ID"),
Name = row.Field<string>("Name"),
Description = row.Field<object>("Description").ToNonNullString(),
FilterExpression = row.Field<object>("FilterExpression").ToNonNullString(),
});
}
measurementGroupList.Insert(0, new MeasurementGroup());
return measurementGroupList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Creates and saves new <see cref="OutputStreamMeasurement"/> into the database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="outputStreamID">ID of the output stream.</param>
/// <param name="measurements">Collection of measurements to be added.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string AddMeasurements(AdoDataConnection database, int outputStreamID, ObservableCollection<Measurement> measurements)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
foreach (Measurement measurement in measurements)
{
OutputStreamMeasurement outputStreamMeasurement = new OutputStreamMeasurement
{
NodeID = (Guid)database.CurrentNodeID(),
AdapterID = outputStreamID,
HistorianID = measurement.HistorianID,
PointID = measurement.PointID,
SignalReference = measurement.SignalReference
};
Save(database, outputStreamMeasurement);
}
return "Output stream measurements added successfully.";
}
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>
/// 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>
/// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Device"/> information.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="deviceType"><see cref="DeviceType"/> to filter data.</param>
/// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param>
/// <param name="showAll">boolean flag indicates if all the devices should be returned irrespective of node.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> containing ID and Name of devices defined in the database.</returns>
public static Dictionary<int, string> GetLookupList(AdoDataConnection database, DeviceType deviceType = DeviceType.DirectConnected, bool isOptional = false, bool showAll = false)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary<int, string> deviceList = new Dictionary<int, string>();
DataTable deviceTable;
string query;
if (isOptional)
deviceList.Add(0, "Select Device");
if (deviceType == DeviceType.Concentrator)
{
query = database.ParameterizedQueryString("SELECT ID, Acronym FROM Device WHERE IsConcentrator = {0} AND NodeID = {1} AND Enabled= {2} ORDER BY LoadOrder",
"isConcentrator", "nodeID", "Enabled");
deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Bool(true), database.CurrentNodeID(), database.Bool(true));
}
else if (deviceType == DeviceType.DirectConnected)
{
query = database.ParameterizedQueryString("SELECT ID, Acronym FROM Device WHERE IsConcentrator = {0} AND NodeID = {1} AND Enabled= {2} ORDER BY LoadOrder",
"isConcentrator", "nodeID", "Enabled");
deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Bool(false), database.CurrentNodeID(), database.Bool(true));
}
else
{
if (showAll)
{
query = "SELECT ID, Acronym FROM Device ORDER BY LoadOrder";
deviceTable = database.Connection.RetrieveData(database.AdapterType, query);
}
else
{
query = database.ParameterizedQueryString("SELECT ID, Acronym FROM Device WHERE NodeID = {0} ORDER BY LoadOrder", "nodeID");
deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID());
}
}
foreach (DataRow row in deviceTable.Rows)
deviceList[row.ConvertField<int>("ID")] = row.Field<string>("Acronym");
return deviceList;
}
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();
}
}
// Loads existing alarms defined in the database
private IEnumerable<Alarm> LoadDefinedAlarms()
{
// Create alarms using definitions from the database
AdoDataConnection database = null;
try
{
// Create database connection to get currently defined alarms
database = new AdoDataConnection(CommonFunctions.DefaultSettingsCategory);
string query = database.ParameterizedQueryString("SELECT * FROM Alarm WHERE NodeID = {0} AND Enabled <> 0", "nodeID");
DataTable alarms = database.Connection.RetrieveData(database.AdapterType, query, database.CurrentNodeID());
return alarms.Rows.Cast<DataRow>()
.Select(CreateAlarm)
.ToList();
}
finally
{
if ((object)database != null)
database.Dispose();
}
}
/// <summary>
/// Saves <see cref="Adapter"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="adapter">Information about <see cref="Adapter"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, Adapter adapter)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
string tableName;
if (adapter.Type == AdapterType.Action)
{
tableName = "CustomActionAdapter";
}
else if (adapter.Type == AdapterType.Filter)
{
tableName = "CustomFilterAdapter";
}
else if (adapter.Type == AdapterType.Input)
{
tableName = "CustomInputAdapter";
}
else
{
tableName = "CustomOutputAdapter";
}
if (adapter.ID == 0)
{
database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("INSERT INTO " + tableName + " (NodeID, AdapterName, AssemblyName, TypeName, ConnectionString, LoadOrder, " +
"Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) Values ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10})",
"nodeID", "adapterName", "assemblyName", "typeName", "connectionString", "loadOrder",
"enabled", "updatedBy", "updatedOn", "createdBy", "createdOn"), DefaultTimeout,
(adapter.NodeID != Guid.Empty) ? database.Guid(adapter.NodeID) : database.CurrentNodeID(), adapter.AdapterName, adapter.AssemblyName,
adapter.TypeName, adapter.ConnectionString.ToNotNull(), adapter.LoadOrder, database.Bool(adapter.Enabled), CommonFunctions.CurrentUser,
database.UtcNow, CommonFunctions.CurrentUser,
database.UtcNow);
}
else
{
database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("UPDATE " + tableName + " SET NodeID = {0}, AdapterName = {1}, AssemblyName = {2}, " +
"TypeName = {3}, ConnectionString = {4}, LoadOrder = {5}, Enabled = {6}, UpdatedBy = {7}, " +
"UpdatedOn = {8} WHERE ID = {9}", "nodeID", "adapterName", "assemblyName", "typeName", "connectionString",
"loadOrder", "enabled", "updatedBy", "updatedOn", "id"), DefaultTimeout, (adapter.NodeID != Guid.Empty) ? database.Guid(adapter.NodeID) : database.CurrentNodeID(),
adapter.AdapterName, adapter.AssemblyName, adapter.TypeName, adapter.ConnectionString.ToNotNull(), adapter.LoadOrder, database.Bool(adapter.Enabled),
CommonFunctions.CurrentUser, database.UtcNow, adapter.ID);
}
return("Adapter information saved successfully");
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
// Static Methods
/// <summary>
/// Loads <see cref="MeasurementGroup"/> information as an <see cref="ObservableCollection{T}"/> style list.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <returns>Collection of <see cref="MeasurementGroup"/>.</returns>
public static ObservableCollection <MeasurementGroup> Load(AdoDataConnection database)
{
ObservableCollection <MeasurementGroup> measurementGroupList;
DataTable measurementGroupTable;
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
measurementGroupList = new ObservableCollection <MeasurementGroup>();
query = database.ParameterizedQueryString("SELECT NodeID, ID, Name, Description, FilterExpression FROM MeasurementGroup WHERE NodeID = {0} ORDER BY Name", "nodeID");
measurementGroupTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID());
foreach (DataRow row in measurementGroupTable.Rows)
{
measurementGroupList.Add(new MeasurementGroup()
{
NodeID = database.Guid(row, "NodeID"),
ID = row.ConvertField <int>("ID"),
Name = row.Field <string>("Name"),
Description = row.Field <object>("Description").ToNonNullString(),
FilterExpression = row.Field <object>("FilterExpression").ToNonNullString(),
CurrentMeasurements = GetCurrentMeasurements(database, row.ConvertField <int>("ID")),
PossibleMeasurements = GetPossibleMeasurements(database, row.ConvertField <int>("ID"))
});
}
measurementGroupList.Insert(0, new MeasurementGroup());
return(measurementGroupList);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Subscriber"/> information.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> containing ID and Name of subscribers defined in the database.</returns>
public static Dictionary<Guid, string> GetLookupList(AdoDataConnection database, bool isOptional = false)
{
Dictionary<Guid, string> subscriberList;
DataTable subscriberTable;
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
subscriberList = new Dictionary<Guid, string>();
if (isOptional)
subscriberList.Add(Guid.Empty, "Select Subscriber");
query = database.ParameterizedQueryString("SELECT ID, Acronym FROM Subscriber WHERE Enabled = {0} AND NodeID = {1} ORDER BY Name", "enabled", "nodeID");
subscriberTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Bool(true), database.CurrentNodeID());
foreach (DataRow row in subscriberTable.Rows)
{
subscriberList[database.Guid(row, "ID")] = row.Field<string>("Acronym");
}
return subscriberList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Retrieves a <see cref="Dictionary{T1,T2}"/> style list of <see cref="MeasurementGroup"/> information.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> containing ID and Name of <see cref="MeasurementGroup"/>s defined in the database.</returns>
public static Dictionary <int, string> GetLookupList(AdoDataConnection database, bool isOptional = true)
{
Dictionary <int, string> measurementGroupList;
DataTable measurementGroupTable;
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
measurementGroupList = new Dictionary <int, string>();
if (isOptional)
{
measurementGroupList.Add(0, "Select MeasurementGroup");
}
query = database.ParameterizedQueryString("SELECT ID, Name FROM MeasurementGroup WHERE NodeID = {0} ORDER BY SourceIndex", "nodeID");
measurementGroupTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID());
foreach (DataRow row in measurementGroupTable.Rows)
{
measurementGroupList[row.ConvertField <int>("ID")] = row.Field <string>("Name");
}
return(measurementGroupList);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Saves <see cref="ApplicationRole"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="applicationRole">Information about <see cref="ApplicationRole"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, ApplicationRole applicationRole)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (applicationRole.ID == null || applicationRole.ID == Guid.Empty)
{
query = database.ParameterizedQueryString("INSERT INTO ApplicationRole (Name, Description, NodeID, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) Values ({0}, {1}, {2}, {3}, {4}, {5}, {6})", "name", "description", "nodeID", "updatedBy", "updatedBy", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, applicationRole.Name, applicationRole.Description.ToNotNull(), database.CurrentNodeID(), CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow);
}
else
{
query = database.ParameterizedQueryString("UPDATE ApplicationRole SET Name = {0}, Description = {1}, NodeID = {2}, UpdatedBy = {3}, UpdatedOn = {4} WHERE ID = {5}", "name", "description", "nodeID", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, applicationRole.Name, applicationRole.Description.ToNotNull(), applicationRole.NodeID, CommonFunctions.CurrentUser, database.UtcNow, database.Guid(applicationRole.ID));
}
return("Application role information saved successfully");
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Saves <see cref="OutputStreamDevice"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="outputStreamDevice">Information about <see cref="OutputStreamDevice"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, OutputStreamDevice outputStreamDevice)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (outputStreamDevice.ID == 0)
{
query = database.ParameterizedQueryString("INSERT INTO OutputStreamDevice (NodeID, AdapterID, IDCode, Acronym, BpaAcronym, Name, " +
"PhasorDataFormat, FrequencyDataFormat, AnalogDataFormat, CoordinateFormat, LoadOrder, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn)" +
"VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15})", "nodeID", "adapterID", "idCode", "acronym",
"bpaAcronym", "name", "phasorDataFormat", "frequencyDataFormat", "analogDataFormat", "coordinateFormat", "loadOrder", "enabled",
"updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), outputStreamDevice.AdapterID, outputStreamDevice.IDCode,
outputStreamDevice.Acronym, outputStreamDevice.BpaAcronym.ToNotNull(), outputStreamDevice.Name, outputStreamDevice.PhasorDataFormat.ToNotNull(),
outputStreamDevice.FrequencyDataFormat.ToNotNull(), outputStreamDevice.AnalogDataFormat.ToNotNull(), outputStreamDevice.CoordinateFormat.ToNotNull(),
outputStreamDevice.LoadOrder, database.Bool(outputStreamDevice.Enabled), CommonFunctions.CurrentUser,
database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow);
}
else
{
OutputStreamDevice originalDevice = GetOutputStreamDevice(database, "WHERE ID = " + outputStreamDevice.ID);
query = database.ParameterizedQueryString("UPDATE OutputStreamDevice SET NodeID = {0}, AdapterID = {1}, IDCode = {2}, Acronym = {3}, BpaAcronym = {4}, " +
"Name = {5}, PhasorDataFormat = {6}, FrequencyDataFormat = {7}, AnalogDataFormat = {8}, CoordinateFormat = {9}, LoadOrder = {10}, Enabled = {11}, " +
" UpdatedBy = {12}, UpdatedOn = {13} WHERE ID = {14}", "nodeID", "adapterID", "idCode", "acronym", "bpaAcronym", "name",
"phasorDataFormat", "frequencyDataFormat", "analogDataFormat", "coordinateFormat", "loadOrder", "enabled", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(outputStreamDevice.NodeID), outputStreamDevice.AdapterID, outputStreamDevice.IDCode,
outputStreamDevice.Acronym, outputStreamDevice.BpaAcronym.ToNotNull(), outputStreamDevice.Name, outputStreamDevice.PhasorDataFormat.ToNotNull(),
outputStreamDevice.FrequencyDataFormat.ToNotNull(), outputStreamDevice.AnalogDataFormat.ToNotNull(), outputStreamDevice.CoordinateFormat.ToNotNull(),
outputStreamDevice.LoadOrder, database.Bool(outputStreamDevice.Enabled), CommonFunctions.CurrentUser,
database.UtcNow, outputStreamDevice.ID);
if (originalDevice != null && originalDevice.Acronym != outputStreamDevice.Acronym)
{
IList<int> keys = OutputStreamMeasurement.LoadKeys(database, originalDevice.AdapterID);
foreach (OutputStreamMeasurement measurement in OutputStreamMeasurement.Load(database, keys))
{
measurement.SignalReference = measurement.SignalReference.Replace(originalDevice.Acronym + "-", outputStreamDevice.Acronym + "-");
OutputStreamMeasurement.Save(database, measurement);
}
}
}
return "OutputStreamDevice information saved successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Subscriber"/> information.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> containing ID and Name of subscribers defined in the database.</returns>
public static Dictionary <Guid, string> GetLookupList(AdoDataConnection database, bool isOptional = false)
{
Dictionary <Guid, string> subscriberList;
DataTable subscriberTable;
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
subscriberList = new Dictionary <Guid, string>();
if (isOptional)
{
subscriberList.Add(Guid.Empty, "Select Subscriber");
}
query = database.ParameterizedQueryString("SELECT ID, Acronym FROM Subscriber WHERE Enabled = {0} AND NodeID = {1} ORDER BY Name", "enabled", "nodeID");
subscriberTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Bool(true), database.CurrentNodeID());
foreach (DataRow row in subscriberTable.Rows)
{
subscriberList[database.Guid(row, "ID")] = row.Field <string>("Acronym");
}
return(subscriberList);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Saves <see cref="Device"/> information to database along with analogs and digital measurements if requested..
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="device">Information about <see cref="Device"/>.</param>
/// <param name="notifyService">Boolean value to notify service if needed.</param>
/// <param name="digitalCount">Number of digital measurements to add.</param>
/// <param name="analogCount">Number of analog measurements to add.</param>
/// <param name="digitalLabels">Collection of digital labels associated with a device in configuration frame.</param>
/// <param name="analogLabels">Collection of analog labels associated with a device in configuration frame.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string SaveWithAnalogsDigitals(AdoDataConnection database, Device device, bool notifyService, int digitalCount, int analogCount, List<string> digitalLabels = null, List<string> analogLabels = null)
{
bool createdConnection = false;
string query;
try
{
Device oldDevice = null;
createdConnection = CreateConnection(ref database);
object nodeID;
if (device.NodeID == Guid.Empty)
nodeID = database.CurrentNodeID();
else
nodeID = database.Guid(device.NodeID);
if (device.ID == 0)
{
query = database.ParameterizedQueryString("INSERT INTO Device (NodeID, ParentID, UniqueID, Acronym, Name, IsConcentrator, CompanyID, HistorianID, AccessID, VendorDeviceID, " +
"ProtocolID, Longitude, Latitude, InterconnectionID, ConnectionString, TimeZone, FramesPerSecond, TimeAdjustmentTicks, DataLossInterval, ContactList, " +
"MeasuredLines, LoadOrder, Enabled, AllowedParsingExceptions, ParsingExceptionWindow, DelayedConnectionInterval, AllowUseOfCachedConfiguration, " +
"AutoStartDataParsingSequence, SkipDisableRealTimeData, MeasurementReportingInterval, ConnectOndemand, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) Values " +
"({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15}, {16}, {17}, {18}, {19}, {20}, {21}, {22}, {23}, {24}, {25}, " +
"{26}, {27}, {28}, {29}, {30}, {31}, {32}, {33}, {34})", "nodeID", "parentID", "uniqueID", "acronym", "name", "isConcentrator", "companyID",
"historianID", "accessID", "vendorDeviceID", "protocolID", "longitude", "latitude", "interconnectionID", "connectionString", "timezone",
"framesPerSecond", "timeAdjustmentTicks", "dataLossInterval", "contactList", "measuredLines", "loadOrder", "enabled", "allowedParsingExceptions",
"parsingExceptionWindow", "delayedConnectionInterval", "allowUseOfCachedConfiguration", "autoStartDataParsingSequence", "skipDisableRealTimeData",
"measurementReportingInterval", "connectOndemand", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, nodeID,
device.ParentID.ToNotNull(), database.Guid(Guid.NewGuid()), device.Acronym.Replace(" ", "").ToUpper(), device.Name.ToNotNull(), database.Bool(device.IsConcentrator), device.CompanyID.ToNotNull(),
device.HistorianID.ToNotNull(), device.AccessID, device.VendorDeviceID.ToNotNull(),
device.ProtocolID.ToNotNull(), device.Longitude.ToNotNull(), device.Latitude.ToNotNull(), device.InterconnectionID.ToNotNull(),
BuildConnectionString(device), device.TimeZone.ToNotNull(), device.FramesPerSecond ?? 30, device.TimeAdjustmentTicks, device.DataLossInterval, device.ContactList.ToNotNull(), device.MeasuredLines.ToNotNull(),
device.LoadOrder, database.Bool(device.Enabled), device.AllowedParsingExceptions, device.ParsingExceptionWindow, device.DelayedConnectionInterval, database.Bool(device.AllowUseOfCachedConfiguration),
database.Bool(device.AutoStartDataParsingSequence), database.Bool(device.SkipDisableRealTimeData), device.MeasurementReportingInterval, database.Bool(device.ConnectOnDemand), CommonFunctions.CurrentUser,
database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow);
}
else
{
oldDevice = GetDevice(database, " WHERE ID = " + device.ID);
query = database.ParameterizedQueryString("UPDATE Device SET NodeID = {0}, ParentID = {1}, UniqueID = {2}, Acronym = {3}, Name = {4}, " +
"IsConcentrator = {5}, CompanyID = {6}, HistorianID = {7}, AccessID = {8}, VendorDeviceID = {9}, ProtocolID = {10}, Longitude = {11}, " +
"Latitude = {12}, InterconnectionID = {13}, ConnectionString = {14}, TimeZone = {15}, FramesPerSecond = {16}, TimeAdjustmentTicks = {17}, " +
"DataLossInterval = {18}, ContactList = {19}, MeasuredLines = {20}, LoadOrder = {21}, Enabled = {22}, AllowedParsingExceptions = {23}, " +
"ParsingExceptionWindow = {24}, DelayedConnectionInterval = {25}, AllowUseOfCachedConfiguration = {26}, AutoStartDataParsingSequence = {27}, " +
"SkipDisableRealTimeData = {28}, MeasurementReportingInterval = {29}, ConnectOnDemand = {30}, UpdatedBy = {31}, UpdatedOn = {32} WHERE ID = {33}",
"nodeID", "parentID", "uniqueID", "acronym", "name", "isConcentrator", "companyID", "historianID", "accessID", "vendorDeviceID", "protocolID",
"longitude", "latitude", "interconnectionID", "connectionString", "timezone", "framesPerSecond", "timeAdjustmentTicks", "dataLossInterval",
"contactList", "measuredLines", "loadOrder", "enabled", "allowedParsingExceptions", "parsingExceptionWindow", "delayedConnectionInterval",
"allowUseOfCachedConfiguration", "autoStartDataParsingSequence", "skipDisableRealTimeData", "measurementReportingInterval", "connectOnDemand",
"updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, nodeID,
device.ParentID.ToNotNull(), database.Guid(device.UniqueID), device.Acronym.Replace(" ", "").ToUpper(), device.Name.ToNotNull(), database.Bool(device.IsConcentrator), device.CompanyID.ToNotNull(),
device.HistorianID.ToNotNull(), device.AccessID, device.VendorDeviceID.ToNotNull(),
device.ProtocolID.ToNotNull(), device.Longitude.ToNotNull(), device.Latitude.ToNotNull(), device.InterconnectionID.ToNotNull(),
BuildConnectionString(device), device.TimeZone.ToNotNull(), device.FramesPerSecond ?? 30, device.TimeAdjustmentTicks, device.DataLossInterval, device.ContactList.ToNotNull(), device.MeasuredLines.ToNotNull(),
device.LoadOrder, database.Bool(device.Enabled), device.AllowedParsingExceptions, device.ParsingExceptionWindow, device.DelayedConnectionInterval, database.Bool(device.AllowUseOfCachedConfiguration),
database.Bool(device.AutoStartDataParsingSequence), database.Bool(device.SkipDisableRealTimeData), device.MeasurementReportingInterval, database.Bool(device.ConnectOnDemand), CommonFunctions.CurrentUser,
database.UtcNow, device.ID);
}
Device savedDevice = GetDevice(database, "WHERE Acronym = '" + device.Acronym.Replace(" ", "").ToUpper() + "'");
if ((object)savedDevice == null)
return "Device information saved successfully but failed to create measurements";
// Determine if device is using a phasor protocol
bool deviceIsUsingPhasorProtocol = (string.Compare(savedDevice.ProtocolCategory, "Phasor", StringComparison.OrdinalIgnoreCase) == 0);
// Add default measurements for non-concentrator devices when device protocol category is Phasor
if (!savedDevice.IsConcentrator && deviceIsUsingPhasorProtocol)
{
// Setup and/or validate default signals associated with non-concentrator devices (e.g., directly connected PMUs or PMUs in a concentrator)
foreach (TimeSeries.UI.DataModels.SignalType signal in TimeSeries.UI.DataModels.SignalType.GetPmuSignalTypes())
{
Measurement measurement;
if (signal.Suffix == "AV" && analogCount > 0)
{
for (int i = 1; i <= analogCount; i++)
{
measurement = Measurement.GetMeasurement(database, "WHERE DeviceID = " + savedDevice.ID + " AND SignalReference = '" + savedDevice.Acronym + "-AV" + i + "'");
if ((object)measurement == null)
{
measurement = new Measurement();
measurement.DeviceID = savedDevice.ID;
measurement.HistorianID = savedDevice.HistorianID;
measurement.PointTag = CommonPhasorServices.CreatePointTag(savedDevice.CompanyAcronym, savedDevice.Acronym, savedDevice.VendorAcronym, "ALOG", i);
measurement.SignalReference = savedDevice.Acronym + "-AV" + i;
measurement.Description = savedDevice.Name + (string.IsNullOrWhiteSpace(savedDevice.VendorDeviceName) ? "" : " " + savedDevice.VendorDeviceName) + " Analog Value " + i;
measurement.SignalTypeID = signal.ID;
measurement.PhasorSourceIndex = (int?)null;
measurement.Enabled = true;
if ((object)analogLabels != null && (object)analogLabels[i - 1] != null)
measurement.AlternateTag = analogLabels[i - 1];
Measurement.Save(database, measurement);
}
else if (measurement.SignalTypeID != signal.ID)
{
// Correct signal type if it has been changed
measurement.SignalTypeID = signal.ID;
Measurement.Save(database, measurement);
}
}
}
else if (signal.Suffix == "DV" && digitalCount > 0)
{
for (int i = 1; i <= digitalCount; i++)
{
measurement = Measurement.GetMeasurement(database, "WHERE DeviceID = " + savedDevice.ID + " AND SignalReference = '" + savedDevice.Acronym + "-DV" + i + "'");
if ((object)measurement == null)
{
measurement = new Measurement();
measurement.DeviceID = savedDevice.ID;
measurement.HistorianID = savedDevice.HistorianID;
measurement.PointTag = CommonPhasorServices.CreatePointTag(savedDevice.CompanyAcronym, savedDevice.Acronym, savedDevice.VendorAcronym, "DIGI", i);
measurement.SignalReference = savedDevice.Acronym + "-DV" + i;
measurement.SignalTypeID = signal.ID;
measurement.Description = savedDevice.Name + (string.IsNullOrWhiteSpace(savedDevice.VendorDeviceName) ? "" : " " + savedDevice.VendorDeviceName) + " Digital Value " + i;
measurement.PhasorSourceIndex = (int?)null;
measurement.Enabled = true;
if ((object)digitalLabels != null && (object)digitalLabels[i - 1] != null)
measurement.AlternateTag = digitalLabels[i - 1];
Measurement.Save(database, measurement);
}
else if (measurement.SignalTypeID != signal.ID)
{
// Correct signal type if it has been changed
measurement.SignalTypeID = signal.ID;
Measurement.Save(database, measurement);
}
}
}
else if (signal.Suffix == "FQ" || signal.Suffix == "DF" || signal.Suffix == "SF")
{
measurement = Measurement.GetMeasurement(database, "WHERE DeviceID = " + savedDevice.ID + " AND SignalTypeSuffix = '" + signal.Suffix + "'");
if ((object)measurement == null)
{
measurement = new Measurement();
measurement.DeviceID = savedDevice.ID;
measurement.HistorianID = savedDevice.HistorianID;
measurement.PointTag = CommonPhasorServices.CreatePointTag(savedDevice.CompanyAcronym, savedDevice.Acronym, savedDevice.VendorAcronym, signal.Acronym);
measurement.SignalReference = savedDevice.Acronym + "-" + signal.Suffix;
measurement.SignalTypeID = signal.ID;
measurement.Description = savedDevice.Name + (string.IsNullOrWhiteSpace(savedDevice.VendorDeviceName) ? "" : " " + savedDevice.VendorDeviceName) + " " + signal.Name;
measurement.PhasorSourceIndex = (int?)null;
measurement.Enabled = true;
Measurement.Save(database, measurement);
}
// Based on query filter of SignalTypeSuffix, the following will never be true
//else if (measurement.SignalTypeID != signal.ID)
//{
// // Correct signal type if it has been changed
// measurement.SignalTypeID = signal.ID;
// Measurement.Save(database, measurement);
//}
}
}
}
if (device.ID > 0)
{
if (!device.IsConcentrator)
{
// For existing non-concentrator devices, call Save on each phasor so that measurements related to those phasors will reflect possible device changes.
IList<int> keys = Phasor.LoadKeys(database, device.ID);
foreach (Phasor phasor in Phasor.Load(database, keys))
{
Phasor.SaveWithoutMeasurementUpdate(database, phasor);
}
}
// Update existing device measurements to reflect possible device changes
if ((object)oldDevice != null)
{
bool companyUpdated = (deviceIsUsingPhasorProtocol && savedDevice.CompanyID != oldDevice.CompanyID);
bool deviceRenamed = (deviceIsUsingPhasorProtocol && (string.CompareOrdinal(savedDevice.Acronym, oldDevice.Acronym) != 0 || string.CompareOrdinal(savedDevice.Name, oldDevice.Name) != 0));
bool historianUpdated = (savedDevice.HistorianID != oldDevice.HistorianID);
if (companyUpdated || deviceRenamed || historianUpdated)
{
string companyAcronym = "";
int underScoreIndex;
if (companyUpdated)
{
if (savedDevice.CompanyID.HasValue && !string.IsNullOrWhiteSpace(savedDevice.CompanyAcronym))
companyAcronym = savedDevice.CompanyAcronym;
}
foreach (Measurement measurement in Measurement.GetMeasurements(database, "WHERE DeviceID = " + oldDevice.ID))
{
if (companyUpdated)
{
// WARNING: This assumes company name is followed by an underscore - this may not be a valid assumption for custom point tag naming conventions
underScoreIndex = measurement.PointTag.ToNonNullString().IndexOf('_');
if (underScoreIndex > -1)
measurement.PointTag = companyAcronym + measurement.PointTag.Substring(underScoreIndex);
}
if (deviceRenamed)
{
measurement.PointTag = measurement.PointTag.Replace(oldDevice.Acronym, savedDevice.Acronym);
measurement.SignalReference = measurement.SignalReference.Replace(oldDevice.Acronym, savedDevice.Acronym);
measurement.Description = Regex.Replace(measurement.Description.ToNonNullString(), oldDevice.Name, savedDevice.Name, RegexOptions.IgnoreCase);
}
if (historianUpdated && string.Compare(measurement.HistorianAcronym, "STAT", StringComparison.OrdinalIgnoreCase) != 0)
measurement.HistorianID = savedDevice.HistorianID;
Measurement.Save(database, measurement);
}
}
// If changing the historian for a concentrator style device - must assume desire to change historian for all children devices
if (historianUpdated && device.IsConcentrator)
{
foreach (Device childDevice in GetDevices(database, "WHERE ParentID = " + device.ID))
{
// Recursively call this function for each child device with updated historian which will also fix measurement's historian
childDevice.HistorianID = savedDevice.HistorianID;
SaveWithAnalogsDigitals(database, childDevice, false, 0, 0);
}
}
}
}
try
{
// Notify service about configuration changes made here.
if (notifyService)
NotifyService(savedDevice);
}
catch (Exception ex)
{
return "Device information saved successfully. Failed to send Initialize command to backend service." + Environment.NewLine + ex.Message;
}
return "Device information saved successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
// Static Methods
/// <summary>
/// Loads <see cref="Alarm"/> IDs as an <see cref="IList{T}"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="sortMember">The field to sort by.</param>
/// <param name="sortDirection"><c>ASC</c> or <c>DESC</c> for ascending or descending respectively.</param>
/// <returns>Collection of <see cref="Int32"/>.</returns>
public static IList <int> LoadKeys(AdoDataConnection database, string sortMember = "", string sortDirection = "")
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
IList <int> alarmList = new List <int>();
string sortClause = string.Empty;
DataTable adapterTable;
string query;
if (!string.IsNullOrEmpty(sortMember))
{
sortClause = string.Format("ORDER BY {0} {1}", sortMember, sortDirection);
}
query = database.ParameterizedQueryString(string.Format("SELECT ID FROM Alarm WHERE NodeID = {{0}} {0}", sortClause), "nodeID");
adapterTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID());
foreach (DataRow row in adapterTable.Rows)
{
alarmList.Add(row.ConvertField <int>("ID"));
}
return(alarmList);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Retrieves a collection of devices where original source is not null for mirroring output stream.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="isOptional">Boolean flag indicating if selection is optional on the UI.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> type collection of Devices.</returns>
public static Dictionary<string, string> GetDevicesForMirroringOutputStream(AdoDataConnection database, bool isOptional = true)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary<string, string> deviceList = new Dictionary<string, string>();
if (isOptional)
deviceList.Add("", "Select Mirroring Source");
DataTable deviceTable;
string query;
query = database.ParameterizedQueryString("SELECT DISTINCT OriginalSource FROM Device WHERE NodeID = {0} AND OriginalSource IS NOT NULL ORDER BY OriginalSource", "nodeID");
deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID());
foreach (DataRow row in deviceTable.Rows)
deviceList[row.ConvertField<string>("OriginalSource")] = row.Field<string>("OriginalSource");
return deviceList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <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.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>
/// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="OutputStream"/> information.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> containing ID and Name of <see cref="OutputStream"/>s defined in the database.</returns>
public static Dictionary<int, string> GetLookupList(AdoDataConnection database, bool isOptional = true)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary<int, string> osList = new Dictionary<int, string>();
DataTable results = database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, Name FROM OutputStream WHERE NodeID = {0} ORDER BY Name", "nodeID"), DefaultTimeout, database.CurrentNodeID());
foreach (DataRow row in results.Rows)
osList[row.ConvertField<int>("ID")] = row.Field<string>("Name");
return osList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Adds measurement groups to <see cref="Subscriber"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="subscriberID">ID of the <see cref="Subscriber"/> to which measurements to be added.</param>
/// <param name="measurementGroupsToBeAdded">List of <see cref="MeasurementGroup"/> IDs to be added.</param>
/// <param name="allowed">boolean flag to indicate if measurement groups are allowed or denied.</param>
/// <returns>string, indicating success for UI display.</returns>
public static string AddMeasurementGroups(AdoDataConnection database, Guid subscriberID, List<int> measurementGroupsToBeAdded, bool allowed)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
foreach (int id in measurementGroupsToBeAdded)
{
query = database.ParameterizedQueryString("INSERT INTO SubscriberMeasurementGroup (NodeID, SubscriberID, MeasurementGroupID, Allowed, UpdatedOn, " +
"UpdatedBy, CreatedOn, CreatedBy) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7})", "nodeID", "subscriberID", "measurementGroupID",
"allowed", "updatedOn", "updatedBy", "createdOn", "createdBy");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), database.Guid(subscriberID), id, database.Bool(allowed),
database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser);
}
if (allowed)
return "Measurement groups added to allowed measurement groups list for subscriber successfully";
else
return "Measurement groups added to denied measurement groups list for subscriber successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Saves <see cref="OutputStream"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="outputStream">Information about <see cref="OutputStream"/>.</param>
/// <param name="mirrorMode">Boolean value to use mirror mode when saving output stream.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, OutputStream outputStream, bool mirrorMode)
{
bool createdConnection = false;
string query;
try
{
OutputStream oldOutputStream;
createdConnection = CreateConnection(ref database);
if (outputStream.ID == 0)
{
query = database.ParameterizedQueryString("INSERT INTO OutputStream (NodeID, Acronym, Name, Type, ConnectionString, IDCode, CommandChannel, DataChannel, " +
"AutoPublishConfigFrame, AutoStartDataChannel, NominalFrequency, FramesPerSecond, LagTime, LeadTime, UseLocalClockAsRealTime, AllowSortsByArrival, " +
"LoadOrder, Enabled, IgnoreBadTimeStamps, TimeResolution, AllowPreemptivePublishing, DownSamplingMethod, DataFormat, CoordinateFormat, " +
"CurrentScalingValue, VoltageScalingValue, AnalogScalingValue, DigitalMaskValue, PerformTimeReasonabilityCheck, UpdatedBy, UpdatedOn, " +
"CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15}, {16}, {17}, {18}, {19}, {20}, " +
"{21}, {22}, {23}, {24}, {25}, {26}, {27}, {28}, {29}, {30}, {31}, {32})", "nodeID", "acronym", "name", "type", "connectionString", "idCode",
"commandChannel", "dataChannel", "autoPublishConfigFrame", "autoStartDataChannel", "nominalFrequency", "framesPerSecond", "lagTime", "leadTime",
"useLocalClockAsRealTime", "allowSortsByArrival", "loadOrder", "enabled", "ignoreBadTimeStamps", "timeResolution", "allowPreemptivePublishing",
"downSamplingMethod", "dataFormat", "coordinateFormat", "currentScalingValue", "voltageScalingValue", "analogScalingValue", "digitalMaskValue",
"performTimeReasonabilityCheck", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query,
database.CurrentNodeID(), outputStream.Acronym.Replace(" ", "").ToUpper(), outputStream.Name.ToNotNull(), outputStream.Type - 1, outputStream.ConnectionString.ToNotNull(),
outputStream.IDCode, outputStream.CommandChannel.ToNotNull(), outputStream.DataChannel.ToNotNull(), database.Bool(outputStream.AutoPublishConfigFrame), database.Bool(outputStream.AutoStartDataChannel),
outputStream.NominalFrequency, outputStream.FramesPerSecond, outputStream.LagTime, outputStream.LeadTime, database.Bool(outputStream.UseLocalClockAsRealTime), database.Bool(outputStream.AllowSortsByArrival),
outputStream.LoadOrder, database.Bool(outputStream.Enabled), database.Bool(outputStream.IgnoreBadTimeStamps), outputStream.TimeResolution, database.Bool(outputStream.AllowPreemptivePublishing),
outputStream.DownSamplingMethod.ToNotNull(), outputStream.DataFormat.ToNotNull(), outputStream.CoordinateFormat.ToNotNull(), outputStream.CurrentScalingValue,
outputStream.VoltageScalingValue, outputStream.AnalogScalingValue, outputStream.DigitalMaskValue, database.Bool(outputStream.PerformTimestampReasonabilityCheck),
CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow);
}
else
{
oldOutputStream = GetOutputStream(database, " WHERE ID = " + outputStream.ID);
query = database.ParameterizedQueryString("UPDATE OutputStream SET NodeID = {0}, Acronym = {1}, Name = {2}, Type = {3}, ConnectionString = {4}, " +
"IDCode = {5}, CommandChannel = {6}, DataChannel = {7}, AutoPublishConfigFrame = {8}, AutoStartDataChannel = {9}, NominalFrequency = {10}, " +
"FramesPerSecond = {11}, LagTime = {12}, LeadTime = {13}, UseLocalClockAsRealTime = {14}, AllowSortsByArrival = {15}, LoadOrder = {16}, " +
"Enabled = {17}, IgnoreBadTimeStamps = {18}, TimeResolution = {19}, AllowPreemptivePublishing = {20}, DownSamplingMethod = {21}, " +
"DataFormat = {22}, CoordinateFormat = {23}, CurrentScalingValue = {24}, VoltageScalingValue = {25}, AnalogScalingValue = {26}, " +
"DigitalMaskValue = {27}, PerformTimeReasonabilityCheck = {28}, UpdatedBy = {29}, UpdatedOn = {30} WHERE ID = {31}", "nodeID", "acronym", "name",
"type", "connectionString", "idCode", "commandChannel", "dataChannel", "autoPublishConfigFrame", "autoStartDataChannel", "nominalFrequency",
"framesPerSecond", "lagTime", "leadTime", "useLocalClockAsRealTime", "allowSortsByArrival", "loadOrder", "enabled", "ignoreBadTimeStamps",
"timeResolution", "allowPreemptivePublishing", "downsamplingMethod", "dataFormat", "coordinateFormat", "currentScalingValue", "voltageScalingValue",
"analogScalingValue", "digitalMaskValue", "performTimeReasonabilityCheck", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout,
database.Guid(outputStream.NodeID), outputStream.Acronym.Replace(" ", "").ToUpper(), outputStream.Name.ToNotNull(), outputStream.Type - 1, outputStream.ConnectionString.ToNotNull(),
outputStream.IDCode, outputStream.CommandChannel.ToNotNull(), outputStream.DataChannel.ToNotNull(), database.Bool(outputStream.AutoPublishConfigFrame), database.Bool(outputStream.AutoStartDataChannel),
outputStream.NominalFrequency, outputStream.FramesPerSecond, outputStream.LagTime, outputStream.LeadTime, database.Bool(outputStream.UseLocalClockAsRealTime),
database.Bool(outputStream.AllowSortsByArrival), outputStream.LoadOrder, database.Bool(outputStream.Enabled), database.Bool(outputStream.IgnoreBadTimeStamps), outputStream.TimeResolution,
database.Bool(outputStream.AllowPreemptivePublishing), outputStream.DownSamplingMethod.ToNotNull(), outputStream.DataFormat.ToNotNull(), outputStream.CoordinateFormat.ToNotNull(),
outputStream.CurrentScalingValue, outputStream.VoltageScalingValue, outputStream.AnalogScalingValue, outputStream.DigitalMaskValue, database.Bool(outputStream.PerformTimestampReasonabilityCheck),
CommonFunctions.CurrentUser, database.UtcNow, outputStream.ID);
if (oldOutputStream != null && oldOutputStream.Acronym != outputStream.Acronym.Replace(" ", "").ToUpper())
{
ObservableCollection<Measurement> measurementList = Measurement.GetOutputStatisticMeasurements(database, oldOutputStream.Acronym);
foreach (Measurement measurement in measurementList)
{
measurement.SignalReference = measurement.SignalReference.Replace(oldOutputStream.Acronym, outputStream.Acronym.Replace(" ", "").ToUpper());
measurement.PointTag = measurement.PointTag.Replace(oldOutputStream.Acronym, outputStream.Acronym.Replace(" ", "").ToUpper());
measurement.Description = Regex.Replace(measurement.Description, oldOutputStream.Name, outputStream.Name ?? outputStream.Acronym.Replace(" ", "").ToUpper(), RegexOptions.IgnoreCase);
Measurement.Save(database, measurement);
}
SignalType qualityType = SignalType.Load(database).FirstOrDefault(type => type.Acronym == "QUAL");
if ((object)qualityType != null)
{
IList<int> keys = database.Connection.RetrieveData(database.AdapterType, string.Format("SELECT ID FROM OutputStreamMeasurement WHERE AdapterID = {0}", outputStream.ID))
.Select().Select(row => row.ConvertField<int>("ID")).ToList();
foreach (OutputStreamMeasurement measurement in OutputStreamMeasurement.Load(database, keys))
{
if (Regex.IsMatch(measurement.SignalReference, string.Format("{0}-{1}", oldOutputStream.Acronym, qualityType.Suffix)))
{
measurement.SignalReference = measurement.SignalReference.Replace(oldOutputStream.Acronym, outputStream.Acronym.Replace(" ", "").ToUpper());
OutputStreamMeasurement.Save(database, measurement);
}
}
}
}
}
if (mirrorMode)
{
// Get ID of the output stream if a new one was inserted above.
if (outputStream.ID == 0)
outputStream.ID = GetOutputStream(database, " WHERE Acronym = '" + outputStream.Acronym.Replace(" ", "").ToUpper() + "'").ID;
IList<int> keys = OutputStreamDevice.LoadKeys(database, outputStream.ID);
// Get all existing devices associated with output stream and delete them.
ObservableCollection<OutputStreamDevice> outputStreamDevices = OutputStreamDevice.Load(database, keys);
foreach (OutputStreamDevice outputStreamDevice in outputStreamDevices)
OutputStreamDevice.Delete(database, outputStream.ID, outputStreamDevice.Acronym);
if (!string.IsNullOrEmpty(outputStream.MirroringSourceDevice))
{
// Get list of input devices, filter by original source = outputstream.MirrorSourceDevice.
ObservableCollection<Device> devices = Device.GetDevices(database, "WHERE OriginalSource = '" + outputStream.MirroringSourceDevice + "'");
// Add these above input devices as output stream devices.
OutputStreamDevice.AddDevices(database, outputStream.ID, devices, true, true);
}
}
return "Output Stream Information Saved Successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Device"/> information.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="protocolType">Type of protocol to filter data.</param>
/// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> containing ID and Name of devices defined in the database.</returns>
public static Dictionary<int, string> GetLookupList(AdoDataConnection database, string protocolType, bool isOptional)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary<int, string> deviceList = new Dictionary<int, string>();
DataTable deviceTable;
string query;
if (isOptional)
deviceList.Add(0, "All Device");
query = database.ParameterizedQueryString("SELECT ID, Acronym, IsConcentrator FROM DeviceDetail WHERE NodeID = {0} AND ProtocolType = {1} ORDER BY LoadOrder",
"nodeID", "protocolType");
deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID(), protocolType);
foreach (DataRow row in deviceTable.Rows)
{
if (row.ConvertField<bool>("IsConcentrator"))
{
ObservableCollection<Device> devices = GetDevices(database, " WHERE ParentID = " + row.ConvertField<int>("ID"));
if (devices != null)
{
foreach (Device device in devices)
deviceList[device.ID] = device.Acronym;
}
}
else
{
deviceList[row.ConvertField<int>("ID")] = row.Field<string>("Acronym");
}
}
return deviceList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
// Static Methods
/// <summary>
/// Loads <see cref="Alarm"/> IDs as an <see cref="IList{T}"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="sortMember">The field to sort by.</param>
/// <param name="sortDirection"><c>ASC</c> or <c>DESC</c> for ascending or descending respectively.</param>
/// <returns>Collection of <see cref="Int32"/>.</returns>
public static IList<int> LoadKeys(AdoDataConnection database, string sortMember = "", string sortDirection = "")
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
IList<int> alarmList = new List<int>();
string sortClause = string.Empty;
DataTable adapterTable;
string query;
if (!string.IsNullOrEmpty(sortMember))
sortClause = string.Format("ORDER BY {0} {1}", sortMember, sortDirection);
query = database.ParameterizedQueryString(string.Format("SELECT ID FROM Alarm WHERE NodeID = {{0}} {0}", sortClause), "nodeID");
adapterTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID());
foreach (DataRow row in adapterTable.Rows)
{
alarmList.Add(row.ConvertField<int>("ID"));
}
return alarmList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Retrieves devices for output stream.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="outputStreamID">ID of the output stream to filter out devices that already exist.</param>
/// <returns>Collection of <see cref="Device"/>.</returns>
public static ObservableCollection<Device> GetNewDevicesForOutputStream(AdoDataConnection database, int outputStreamID)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
ObservableCollection<Device> deviceList = new ObservableCollection<Device>();
DataTable deviceTable;
string query;
// Note that OleDB does not support parameterized sub-query.
if (database.DatabaseType == DatabaseType.Access)
{
query = database.ParameterizedQueryString("SELECT * FROM DeviceDetail WHERE NodeID = {0} AND IsConcentrator = {1} AND Acronym NOT IN "
+ "(SELECT Acronym FROM OutputStreamDevice WHERE AdapterID = " + outputStreamID + ") ORDER BY Acronym", "nodeID", "isConcentrator");
deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID(), database.Bool(false));
}
else
{
query = database.ParameterizedQueryString("SELECT * FROM DeviceDetail WHERE NodeID = {0} AND IsConcentrator = {1} AND Acronym NOT IN "
+ "(SELECT Acronym FROM OutputStreamDevice WHERE AdapterID = {2}) ORDER BY Acronym", "nodeID", "isConcentrator", "adapterID");
deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID(), database.Bool(false), outputStreamID);
}
foreach (DataRow row in deviceTable.Rows)
{
deviceList.Add(new Device
{
NodeID = database.Guid(row, "NodeID"),
ID = row.ConvertField<int>("ID"),
ParentID = row.ConvertNullableField<int>("ParentID"),
UniqueID = database.Guid(row, "UniqueID"),
Acronym = row.Field<string>("Acronym"),
Name = row.Field<string>("Name"),
IsConcentrator = Convert.ToBoolean(row.Field<object>("IsConcentrator")),
CompanyID = row.ConvertNullableField<int>("CompanyID"),
HistorianID = row.ConvertNullableField<int>("HistorianID"),
AccessID = row.ConvertField<int>("AccessID"),
VendorDeviceID = row.ConvertNullableField<int>("VendorDeviceID"),
ProtocolID = row.ConvertNullableField<int>("ProtocolID"),
Longitude = row.ConvertNullableField<decimal>("Longitude"),
Latitude = row.ConvertNullableField<decimal>("Latitude"),
InterconnectionID = row.ConvertNullableField<int>("InterconnectionID"),
ConnectionString = ParseConnectionString(row.Field<string>("ConnectionString").ToNonNullString()),
AlternateCommandChannel = ParseAlternateCommand(row.Field<string>("ConnectionString").ToNonNullString()),
TimeZone = row.Field<string>("TimeZone"),
FramesPerSecond = Convert.ToInt32(row.Field<object>("FramesPerSecond") ?? 30),
TimeAdjustmentTicks = Convert.ToInt64(row.Field<object>("TimeAdjustmentTicks")),
DataLossInterval = row.ConvertField<double>("DataLossInterval"),
ContactList = row.Field<string>("ContactList"),
MeasuredLines = row.ConvertNullableField<int>("MeasuredLines"),
LoadOrder = row.ConvertField<int>("LoadOrder"),
Enabled = false, // We will use enable flag for check boxes on output stream device wizard so that we do not need to add selected flag.
CreatedOn = row.Field<DateTime>("CreatedOn"),
AllowedParsingExceptions = Convert.ToInt32(row.Field<object>("AllowedParsingExceptions")),
ParsingExceptionWindow = row.ConvertField<double>("ParsingExceptionWindow"),
DelayedConnectionInterval = row.ConvertField<double>("DelayedConnectionInterval"),
AllowUseOfCachedConfiguration = Convert.ToBoolean(row.Field<object>("AllowUseOfCachedConfiguration")),
AutoStartDataParsingSequence = Convert.ToBoolean(row.Field<object>("AutoStartDataParsingSequence")),
SkipDisableRealTimeData = Convert.ToBoolean(row.Field<object>("SkipDisableRealTimeData")),
MeasurementReportingInterval = Convert.ToInt32(row.Field<object>("MeasurementReportingInterval")),
ConnectOnDemand = Convert.ToBoolean(row.Field<object>("ConnectOnDemand")),
m_companyName = row.Field<string>("CompanyName"),
m_companyAcronym = row.Field<string>("CompanyAcronym"),
m_historianAcronym = row.Field<string>("HistorianAcronym"),
m_vendorDeviceName = row.Field<string>("VendorDeviceName"),
m_vendorAcronym = row.Field<string>("VendorAcronym"),
m_protocolName = row.Field<string>("ProtocolName"),
m_protocolCategory = row.Field<string>("Category"),
m_interconnectionName = row.Field<string>("InterconnectionName"),
m_nodeName = row.Field<string>("NodeName"),
m_parentAcronym = row.Field<string>("ParentAcronym"),
m_originalSource = row.Field<string>("OriginalSource")
});
}
return deviceList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Saves <see cref="OutputStreamMeasurement"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="outputStreamMeasurement">Information about <see cref="OutputStreamMeasurement"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, OutputStreamMeasurement outputStreamMeasurement)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (outputStreamMeasurement.ID == 0)
{
query = database.ParameterizedQueryString("INSERT INTO OutputStreamMeasurement (NodeID, AdapterID, HistorianID, PointID, SignalReference, " +
" UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8})",
"nodeID", "adapterID", "historianID", "pointID", "signalReference", "updatedBy", "updatedOn", "createdBy",
"createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, outputStreamMeasurement.NodeID == Guid.Empty ? database.CurrentNodeID() : database.Guid(outputStreamMeasurement.NodeID),
outputStreamMeasurement.AdapterID, outputStreamMeasurement.HistorianID.ToNotNull(), outputStreamMeasurement.PointID, outputStreamMeasurement.SignalReference,
CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow);
}
else
{
query = database.ParameterizedQueryString("UPDATE OutputStreamMeasurement SET NodeID = {0}, AdapterID = {1}, HistorianID = {2}, PointID = {3}, " +
"SignalReference = {4}, UpdatedBy = {5}, UpdatedOn = {6} WHERE ID = {7}", "nodeID", "adapterID",
"historianID", "pointID", "signalReference", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(outputStreamMeasurement.NodeID), outputStreamMeasurement.AdapterID,
outputStreamMeasurement.HistorianID.ToNotNull(), outputStreamMeasurement.PointID, outputStreamMeasurement.SignalReference,
CommonFunctions.CurrentUser, database.UtcNow, outputStreamMeasurement.ID);
}
return "OutputStreamMeasurement information saved successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
// Static Methods
/// <summary>
/// LoadKeys <see cref="Phasor"/> information as an <see cref="ObservableCollection{T}"/> style list.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="parentID">ID of the parent device to filter data.</param>
/// <param name="searchText">The text to search by.</param>
/// <param name="sortMember">The field to sort by.</param>
/// <param name="sortDirection"><c>ASC</c> or <c>DESC</c> for ascending or descending respectively.</param>
/// <returns>Collection of <see cref="Phasor"/>.</returns>
public static IList<int> LoadKeys(AdoDataConnection database, int parentID = 0, string searchText = "", string sortMember = "", string sortDirection = "")
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
IList<int> deviceList = new List<int>();
DataTable deviceTable;
string query;
string searchParam = null;
string searchQuery = null;
string sortClause = string.Empty;
if (!string.IsNullOrEmpty(searchText))
{
searchParam = string.Format("%{0}%", searchText);
if (database.IsJetEngine)
{
// Access queries do not support UPPER but are case-insensitive anyway
searchQuery = string.Format("ID LIKE '{0}' OR UniqueID LIKE '{0}' OR Acronym LIKE '{0}' " +
"OR Name LIKE '{0}' OR OriginalSource LIKE '{0}' OR Longitude LIKE '{0}' OR Latitude LIKE '{0}' " +
"OR ConnectionString LIKE '{0}' OR TimeZone LIKE '{0}' OR FramesPerSecond LIKE '{0}' " +
"OR ContactList LIKE '{0}' OR CompanyName LIKE '{0}' OR CompanyAcronym LIKE '{0}' " +
"OR CompanyMapAcronym LIKE '{0}' OR HistorianAcronym LIKE '{0}' OR VendorAcronym LIKE '{0}' " +
"OR VendorDeviceName LIKE '{0}' OR ProtocolName LIKE '{0}' OR InterconnectionName LIKE '{0}'",
searchParam.Replace("'", "''"));
}
else
{
searchQuery = database.ParameterizedQueryString("ID LIKE {0} OR UPPER(UniqueID) LIKE UPPER({0}) OR UPPER(Acronym) LIKE UPPER({0}) " +
"OR UPPER(Name) LIKE UPPER({0}) OR UPPER(OriginalSource) LIKE UPPER({0}) OR Longitude LIKE {0} OR Latitude LIKE {0} " +
"OR UPPER(ConnectionString) LIKE UPPER({0}) OR UPPER(TimeZone) LIKE UPPER({0}) OR FramesPerSecond LIKE {0} " +
"OR UPPER(ContactList) LIKE UPPER({0}) OR UPPER(CompanyName) LIKE UPPER({0}) OR UPPER(CompanyAcronym) LIKE UPPER({0}) " +
"OR UPPER(CompanyMapAcronym) LIKE UPPER({0}) OR UPPER(HistorianAcronym) LIKE UPPER({0}) OR UPPER(VendorAcronym) LIKE UPPER({0}) " +
"OR UPPER(VendorDeviceName) LIKE UPPER({0}) OR UPPER(ProtocolName) LIKE UPPER({0}) OR UPPER(InterconnectionName) LIKE UPPER({0})",
"searchParam");
}
}
if (!string.IsNullOrEmpty(sortMember))
sortClause = string.Format("ORDER BY {0} {1}", sortMember, sortDirection);
if (parentID > 0)
{
if (string.IsNullOrEmpty(searchText))
{
query = database.ParameterizedQueryString(string.Format("SELECT ID From DeviceDetail WHERE NodeID = {{0}} AND ParentID = {{1}} {0}", sortClause), "nodeID", "parentID");
deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID(), parentID);
}
else if (!database.IsJetEngine)
{
query = database.ParameterizedQueryString(string.Format("SELECT ID From DeviceDetail WHERE NodeID = {{0}} AND ParentID = {{1}} AND ({0}) {1}", searchText, sortClause), "nodeID", "parentID");
deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID(), parentID, searchParam);
}
else
{
query = database.ParameterizedQueryString(string.Format("SELECT ID From DeviceDetail WHERE NodeID = {{0}} AND ParentID = {{1}} AND ({0}) {1}", searchText, sortClause), "nodeID", "parentID");
deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID(), parentID);
}
}
else
{
if (string.IsNullOrEmpty(searchText))
{
query = database.ParameterizedQueryString(string.Format("SELECT ID From DeviceDetail WHERE NodeID = {{0}} {0}", sortClause), "nodeID");
deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID());
}
else if (!database.IsJetEngine)
{
query = database.ParameterizedQueryString(string.Format("SELECT ID From DeviceDetail WHERE NodeID = {{0}} AND ({0}) {1}", searchQuery, sortClause), "nodeID");
deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID(), searchParam);
}
else
{
query = database.ParameterizedQueryString(string.Format("SELECT ID From DeviceDetail WHERE NodeID = {{0}} AND ({0}) {1}", searchQuery, sortClause), "nodeID");
deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID());
}
}
foreach (DataRow row in deviceTable.Rows)
{
deviceList.Add(row.ConvertField<int>("ID"));
}
return deviceList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
// Static
/// <summary>
/// Creates <see cref="ObservableCollection{T}"/> type collection of <see cref="RealTimeStatistic"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <returns>Collection of <see cref="RealTimeStatistic"/>.</returns>
public static ObservableCollection <RealTimeStatistic> Load(AdoDataConnection database)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
ObservableCollection <RealTimeStatistic> realTimeStatisticList = new ObservableCollection <RealTimeStatistic>();
DataSet resultSet = new DataSet();
resultSet.EnforceConstraints = false;
// Get PDCs and directly connected devices.
resultSet.Tables.Add(database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, Acronym, Name FROM DeviceDetail " +
"WHERE NodeID = {0} AND (IsConcentrator = {1} OR ParentAcronym = {2} OR ParentAcronym IS NULL) AND Enabled = {3} ORDER BY Acronym", "nodeID", "isConcentrator", "parentAcronym", "enabled"),
DefaultTimeout, database.CurrentNodeID(), database.Bool(true), string.Empty, database.Bool(true)).Copy());
resultSet.Tables[0].TableName = "DirectDevices";
// Get all the devices connected via PDC.
resultSet.Tables.Add(database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, Acronym, Name, ParentID, " +
"ParentAcronym FROM DeviceDetail WHERE NodeID = {0} AND IsConcentrator = {1} AND Enabled = {2} AND ParentID > 0 ORDER BY Acronym", "nodeID", "isConcentrator", "enabled"),
DefaultTimeout, database.CurrentNodeID(), database.Bool(false), database.Bool(true)).Copy());
resultSet.Tables[1].TableName = "PdcDevices";
// Get output stream information.
resultSet.Tables.Add(database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, Acronym, Name FROM OutputStream WHERE " +
"NodeID = {0} AND Enabled = {1} ORDER BY Acronym", "nodeID", "enabled"), DefaultTimeout, database.CurrentNodeID(), database.Bool(true)).Copy());
resultSet.Tables[2].TableName = "OutputStreams";
resultSet.Tables.Add(database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, AdapterName FROM CustomActionAdapter WHERE " +
"NodeID = {0} AND TypeName = {1} AND Enabled = {2} ORDER BY AdapterName", "nodeID", "typeName", "enabled"), DefaultTimeout, database.CurrentNodeID(), "GSF.TimeSeries.Transport.DataPublisher", database.Bool(true)).Copy());
resultSet.Tables[3].TableName = "DataPublishers";
// Get list of statistic measurements detail.
ObservableCollection <StatisticMeasurement> statisticMeasurements = GetStatisticMeasurements(database);
bool expanded = (statisticMeasurements.Count < 100);
// We do this for later use in refreshing data.
StatisticMeasurements = new Dictionary <Guid, StatisticMeasurement>();
foreach (StatisticMeasurement statisticMeasurement in statisticMeasurements)
{
StatisticMeasurements.Add(statisticMeasurement.SignalID, statisticMeasurement);
}
// Create a system statistics list.
ObservableCollection <StreamStatistic> systemStatistics = new ObservableCollection <StreamStatistic>();
systemStatistics.Add(new StreamStatistic()
{
ID = 0,
Acronym = "SYSTEM",
Name = "System",
StatusColor = "Green",
Expanded = expanded,
StatisticMeasurementList = new ObservableCollection <StatisticMeasurement>(statisticMeasurements.Where(sm => sm.SignalReference.Contains("!SYSTEM"))),
DeviceStatisticList = new ObservableCollection <PdcDeviceStatistic>()
});
SystemStatistics = new Dictionary <int, StreamStatistic>();
foreach (StreamStatistic streamStatistic in systemStatistics)
{
// We do this to associate statistic measurement to parent output stream easily.
foreach (StatisticMeasurement measurement in streamStatistic.StatisticMeasurementList)
{
measurement.DeviceID = streamStatistic.ID;
}
streamStatistic.DeviceStatisticList.Insert(0, new PdcDeviceStatistic()
{
DeviceID = 0,
DeviceAcronym = "Run-time Statistics",
DeviceName = "",
Expanded = expanded,
StatisticMeasurementList = streamStatistic.StatisticMeasurementList
});
streamStatistic.StatisticMeasurementList = null;
// We do this for later use in refreshing data.
SystemStatistics.Add(streamStatistic.ID, streamStatistic);
}
// Create an input stream statistics list.
ObservableCollection <StreamStatistic> inputStreamStatistics = new ObservableCollection <StreamStatistic>
(
from stream in resultSet.Tables["DirectDevices"].AsEnumerable()
select new StreamStatistic
{
ID = Convert.ToInt32(stream.Field <object>("ID")),
Acronym = stream.Field <string>("Acronym"),
Name = stream.Field <string>("Name"),
StatusColor = "Gray",
Expanded = expanded,
StatisticMeasurementList = new ObservableCollection <StatisticMeasurement>
(
(
from statisticMeasurement in statisticMeasurements
where statisticMeasurement.DeviceID == Convert.ToInt32(stream.Field <object>("ID"))
select statisticMeasurement
).OrderBy(sm => sm.Source).ThenBy(sm => sm.LoadOrder)
),
DeviceStatisticList = new ObservableCollection <PdcDeviceStatistic>
(
from pdcdevice in resultSet.Tables["PdcDevices"].AsEnumerable()
where Convert.ToInt32(pdcdevice.Field <object>("ParentID")) == Convert.ToInt32(stream.Field <object>("ID"))
select new PdcDeviceStatistic()
{
DeviceID = Convert.ToInt32(pdcdevice.Field <object>("ID")),
ParentID = Convert.ToInt32(pdcdevice.Field <object>("ParentID")),
DeviceAcronym = pdcdevice.Field <string>("Acronym"),
DeviceName = pdcdevice.Field <string>("Name"),
Expanded = expanded,
StatisticMeasurementList = new ObservableCollection <StatisticMeasurement>
(
(
from statisticMeasurement in statisticMeasurements
where statisticMeasurement.DeviceID == Convert.ToInt32(pdcdevice.Field <object>("ID"))
select statisticMeasurement
).OrderBy(sm => sm.LoadOrder)
)
}
)
}
);
InputStreamStatistics = new Dictionary <int, StreamStatistic>();
DevicesWithStatisticMeasurements = new Dictionary <int, ObservableCollection <StatisticMeasurement> >();
foreach (StreamStatistic streamStatistic in inputStreamStatistics)
{
streamStatistic.DeviceStatisticList.Insert(0, new PdcDeviceStatistic()
{
DeviceID = 0,
DeviceAcronym = "Run-time Statistics",
DeviceName = "",
Expanded = expanded,
StatisticMeasurementList = new ObservableCollection <StatisticMeasurement>(streamStatistic.StatisticMeasurementList)
});
// We do this for later use in refreshing data.
InputStreamStatistics.Add(streamStatistic.ID, streamStatistic);
// We do this for use in Input Status & Monitoring screen to set proper status color.
if (streamStatistic.ID > 0)
{
DevicesWithStatisticMeasurements.Add(streamStatistic.ID, streamStatistic.StatisticMeasurementList);
foreach (PdcDeviceStatistic device in streamStatistic.DeviceStatisticList)
{
if (device.DeviceID > 0)
{
DevicesWithStatisticMeasurements.Add(device.DeviceID, device.StatisticMeasurementList);
}
}
}
streamStatistic.StatisticMeasurementList = null;
}
// Create an output stream statistics list.
ObservableCollection <StreamStatistic> outputStreamStatistics = new ObservableCollection <StreamStatistic>
(
from outputStream in resultSet.Tables["OutputStreams"].AsEnumerable()
select new StreamStatistic()
{
ID = Convert.ToInt32(outputStream.Field <object>("ID")),
Acronym = outputStream.Field <string>("Acronym"),
Name = outputStream.Field <string>("Name"),
StatusColor = "Gray",
DeviceStatisticList = new ObservableCollection <PdcDeviceStatistic>(),
Expanded = expanded,
StatisticMeasurementList = new ObservableCollection <StatisticMeasurement>
(
(
from statisticMeasurement in statisticMeasurements
where statisticMeasurement.SignalReference.StartsWith(outputStream.Field <string>("Acronym") + "!OS-")
select statisticMeasurement
).OrderBy(sm => sm.Source).ThenBy(sm => sm.LoadOrder)
)
}
);
OutputStreamStatistics = new Dictionary <int, StreamStatistic>();
foreach (StreamStatistic streamStatistic in outputStreamStatistics)
{
// We do this to associate statistic measurement to parent output stream easily.
foreach (StatisticMeasurement measurement in streamStatistic.StatisticMeasurementList)
{
measurement.DeviceID = streamStatistic.ID;
}
streamStatistic.DeviceStatisticList.Insert(0, new PdcDeviceStatistic
{
DeviceID = 0,
DeviceAcronym = "Run-time Statistics",
DeviceName = "",
Expanded = expanded,
StatisticMeasurementList = streamStatistic.StatisticMeasurementList
});
streamStatistic.StatisticMeasurementList = null;
// We do this for later use in refreshing data.
OutputStreamStatistics.Add(streamStatistic.ID, streamStatistic);
}
// Create a data publisher statistics list
ObservableCollection <StreamStatistic> dataPublisherStatistics = new ObservableCollection <StreamStatistic>(
from publisher in resultSet.Tables["DataPublishers"].AsEnumerable()
select new StreamStatistic()
{
ID = Convert.ToInt32(publisher.Field <object>("ID")),
Acronym = publisher.Field <string>("AdapterName"),
Name = "",
StatusColor = "Gray",
DeviceStatisticList = new ObservableCollection <PdcDeviceStatistic>(),
Expanded = expanded,
StatisticMeasurementList = new ObservableCollection <StatisticMeasurement>
(
(
from statisticMeasurement in statisticMeasurements
where statisticMeasurement.SignalReference.StartsWith(publisher.Field <string>("AdapterName") + "!PUB-")
select statisticMeasurement
).OrderBy(sm => sm.Source).ThenBy(sm => sm.LoadOrder)
)
}
);
DataPublisherStatistics = new Dictionary <int, StreamStatistic>();
foreach (StreamStatistic streamStatistic in dataPublisherStatistics)
{
// We do this to associate statistic measurement to parent output stream easily.
foreach (StatisticMeasurement measurement in streamStatistic.StatisticMeasurementList)
{
measurement.DeviceID = streamStatistic.ID;
}
streamStatistic.DeviceStatisticList.Insert(0, new PdcDeviceStatistic()
{
DeviceID = 0,
DeviceAcronym = "Run-time Statistics",
DeviceName = "",
Expanded = expanded,
StatisticMeasurementList = streamStatistic.StatisticMeasurementList
});
streamStatistic.StatisticMeasurementList = null;
// We do this for later use in refreshing data.
DataPublisherStatistics.Add(streamStatistic.ID, streamStatistic);
}
// Merge system, input and output stream statistics to create a realtime statistics list.
realTimeStatisticList.Add(new RealTimeStatistic()
{
SourceType = "System",
Expanded = false,
StreamStatisticList = systemStatistics
});
realTimeStatisticList.Add(new RealTimeStatistic()
{
SourceType = "Input Streams",
Expanded = false,
StreamStatisticList = inputStreamStatistics
});
realTimeStatisticList.Add(new RealTimeStatistic()
{
SourceType = "Output Streams",
Expanded = false,
StreamStatisticList = outputStreamStatistics
});
realTimeStatisticList.Add(new RealTimeStatistic()
{
SourceType = "Data Publisher",
Expanded = false,
StreamStatisticList = dataPublisherStatistics
});
return(realTimeStatisticList);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
// Static Methods
/// <summary>
/// Loads <see cref="OutputStream"/> information as an <see cref="ObservableCollection{T}"/> style list.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="enabledOnly">Boolean flag indicating if only enabled <see cref="OutputStream"/>s needed.</param>
/// <param name="sortMember">The field to sort by.</param>
/// <param name="sortDirection"><c>ASC</c> or <c>DESC</c> for ascending or descending respectively.</param>
/// <returns>Collection of <see cref="OutputStream"/>.</returns>
public static IList<int> LoadKeys(AdoDataConnection database, bool enabledOnly, string sortMember, string sortDirection)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
IList<int> outputStreamList = new List<int>();
DataTable outputStreamTable;
string query;
string sortClause = string.Empty;
if (!string.IsNullOrEmpty(sortMember))
sortClause = string.Format("ORDER BY {0} {1}", sortMember, sortDirection);
if (enabledOnly)
{
query = database.ParameterizedQueryString(string.Format("SELECT ID FROM OutputStreamDetail WHERE NodeID = {{0}} AND Enabled = {{1}} {0}", sortClause), "nodeID", "enabled");
outputStreamTable = database.Connection.RetrieveData(database.AdapterType, query, database.CurrentNodeID(), database.Bool(true));
}
else
{
query = database.ParameterizedQueryString(string.Format("SELECT * FROM OutputStreamDetail WHERE NodeID = {{0}} {0}", sortClause), "nodeID");
outputStreamTable = database.Connection.RetrieveData(database.AdapterType, query, database.CurrentNodeID());
}
foreach (DataRow row in outputStreamTable.Rows)
{
outputStreamList.Add(row.ConvertField<int>("ID"));
}
return outputStreamList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Gets statistic measurements from the database for current node.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <returns>Collection of <see cref="StatisticMeasurement"/>.</returns>
public static ObservableCollection <StatisticMeasurement> GetStatisticMeasurements(AdoDataConnection database)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
// Get Statistic Definitions
ObservableCollection <Statistic> statisticDefinitions = Statistic.Load(database);
// Get statistics measurements.
DataTable statisticMeasurements = database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT SignalID, ID, DeviceID, PointID, PointTag, SignalReference " +
"FROM StatisticMeasurement WHERE NodeID = {0}", "nodeID"), DefaultTimeout, database.CurrentNodeID());
// Assign min and max point IDs as we will need them to request data from web service.
MinPointID = int.MaxValue;
MaxPointID = int.MinValue;
foreach (DataRow row in statisticMeasurements.Rows)
{
int pointID = Convert.ToInt32(row.Field <object>("PointID"));
MinPointID = Math.Min(MinPointID, pointID);
MaxPointID = Math.Max(MaxPointID, pointID);
}
// Takes datarow from statisticMeasurements data table, and associates each row to their statistic source and returns KeyValuePair.
Func <DataRow, KeyValuePair <DataRow, string> > mapFunction = measurement =>
{
string signalReference = measurement.Field <string>("SignalReference");
string measurementSource;
if (StatisticsEngine.RegexMatch(signalReference, "SYSTEM"))
{
measurementSource = "System";
}
else if (StatisticsEngine.RegexMatch(signalReference, "PMU"))
{
measurementSource = "Device";
}
else if (StatisticsEngine.RegexMatch(signalReference, "OS"))
{
measurementSource = "OutputStream";
}
else if (StatisticsEngine.RegexMatch(signalReference, "IS"))
{
measurementSource = "InputStream";
}
else if (StatisticsEngine.RegexMatch(signalReference, "SUB"))
{
measurementSource = "Subscriber";
}
else if (StatisticsEngine.RegexMatch(signalReference, "PUB"))
{
measurementSource = "Publisher";
}
else
{
measurementSource = "???";
}
return(new KeyValuePair <DataRow, string>(measurement, measurementSource));
};
// Takes KeyValuePair and generates StatisticMeasurement record by maping statistic measurements with statistic definitions.
Func <KeyValuePair <DataRow, string>, StatisticMeasurement> selectFunction = keyvaluepair =>
{
DataRow measurement = keyvaluepair.Key;
string measurementSource = keyvaluepair.Value;
Debug.WriteLine(measurementSource);
string signalReference = measurement.Field <string>("SignalReference");
int measurementIndex = Convert.ToInt32(signalReference.Substring(signalReference.LastIndexOf("-ST") + 3));
Statistic statisticDefinition = new Statistic();
foreach (Statistic statistic in statisticDefinitions)
{
if (statistic.Source == measurementSource && statistic.SignalIndex == measurementIndex)
{
statisticDefinition = statistic;
break;
}
}
return(new StatisticMeasurement()
{
SignalID = database.Guid(measurement, "SignalID"),
ID = measurement.Field <string>("ID"),
DeviceID = Convert.ToInt32(measurement.Field <object>("DeviceID") ?? -1),
PointID = Convert.ToInt32(measurement.Field <object>("PointID")),
PointTag = measurement.Field <string>("PointTag"),
SignalReference = signalReference,
Source = measurementSource,
StatisticName = statisticDefinition.Name,
StatisticDescription = statisticDefinition.Description,
DataType = statisticDefinition.DataType,
DisplayFormat = statisticDefinition.DisplayFormat,
ConnectedState = statisticDefinition.IsConnectedState,
LoadOrder = statisticDefinition.LoadOrder,
TimeTag = "n/a",
Quality = "n/a",
Value = "--"
});
};
return(new ObservableCollection <StatisticMeasurement>(statisticMeasurements.Rows.Cast <DataRow>().Select(mapFunction).OrderBy(pair => pair.Value).Select(selectFunction).OrderBy(s => s.LoadOrder)));
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Saves <see cref="OutputStreamDevicePhasor"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="outputStreamDevicePhasor">Information about <see cref="OutputStreamDevicePhasor"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, OutputStreamDevicePhasor outputStreamDevicePhasor)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (outputStreamDevicePhasor.ID == 0)
{
query = database.ParameterizedQueryString("INSERT INTO OutputStreamDevicePhasor (NodeID, OutputStreamDeviceID, Label, Type, Phase, ScalingValue, " +
"LoadOrder, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10})", "nodeID",
"outputStreamDeviceID", "label", "type", "phase", "scalingValue", "loadOrder", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), outputStreamDevicePhasor.OutputStreamDeviceID,
outputStreamDevicePhasor.Label, outputStreamDevicePhasor.Type, outputStreamDevicePhasor.Phase, outputStreamDevicePhasor.ScalingValue,
outputStreamDevicePhasor.LoadOrder, CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow);
// PhasorType, PhaseType @phasorName, @phaseType, OutputStreamDevicePhasor.PhasorType, OutputStreamDevicePhasor.PhaseType
}
else
{
query = database.ParameterizedQueryString("UPDATE OutputStreamDevicePhasor SET NodeID = {0}, OutputStreamDeviceID = {1}, Label = {2}, Type = {3}, " +
"Phase = {4}, ScalingValue = {5}, LoadOrder = {6}, UpdatedBy = {7}, UpdatedOn = {8} WHERE ID = {9}", "nodeID", "outputStreamDeviceID", "label",
"type", "phase", "scalingValue", "loadOrder", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, outputStreamDevicePhasor.NodeID, outputStreamDevicePhasor.OutputStreamDeviceID,
outputStreamDevicePhasor.Label, outputStreamDevicePhasor.Type, outputStreamDevicePhasor.Phase, outputStreamDevicePhasor.ScalingValue,
outputStreamDevicePhasor.LoadOrder, CommonFunctions.CurrentUser, database.UtcNow, outputStreamDevicePhasor.ID);
//PhasorType = @typeName, PhaseType = @PhaseType" OutputStreamDevicePhasor.PhasorType, OutputStreamDevicePhasor.PhaseType,
}
return "OutputStreamDevicePhasor information saved successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Loads <see cref="CalculatedMeasurement"/> 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 measurement to be loaded from the database</param>
/// <returns>Collection of <see cref="CalculatedMeasurement"/>.</returns>
public static ObservableCollection <CalculatedMeasurement> Load(AdoDataConnection database, IList <int> keys)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
string query;
string commaSeparatedKeys;
CalculatedMeasurement[] calculatedMeasurementList = null;
DataTable calculatedMeasurementTable;
int id;
if ((object)keys != null && keys.Count > 0)
{
commaSeparatedKeys = keys.Select(key => key.ToString()).Aggregate((str1, str2) => str1 + "," + str2);
query = string.Format("SELECT NodeID, ID, Acronym, Name, AssemblyName, " +
"TypeName, ConnectionString, ConfigSection, InputMeasurements, OutputMeasurements, MinimumMeasurementsToUse, FramesPerSecond, LagTime, " +
"LeadTime, UseLocalClockAsRealTime, AllowSortsByArrival, LoadOrder, Enabled, IgnoreBadTimeStamps, TimeResolution, AllowPreemptivePublishing, " +
"DownSamplingMethod, NodeName, PerformTimeReasonabilityCheck From CalculatedMeasurementDetail WHERE ID IN ({0}) AND NodeID = '{1}'", commaSeparatedKeys, database.CurrentNodeID());
calculatedMeasurementTable = database.Connection.RetrieveData(database.AdapterType, query);
calculatedMeasurementList = new CalculatedMeasurement[calculatedMeasurementTable.Rows.Count];
foreach (DataRow row in calculatedMeasurementTable.Rows)
{
id = row.ConvertField <int>("ID");
calculatedMeasurementList[keys.IndexOf(id)] = new CalculatedMeasurement()
{
NodeID = database.Guid(row, "NodeID"),
ID = id,
Acronym = row.Field <string>("Acronym"),
Name = row.Field <string>("Name"),
AssemblyName = row.Field <string>("AssemblyName"),
TypeName = row.Field <string>("TypeName"),
ConnectionString = row.Field <string>("ConnectionString"),
ConfigSection = row.Field <string>("ConfigSection"),
InputMeasurements = row.Field <string>("InputMeasurements"),
OutputMeasurements = row.Field <string>("OutputMeasurements"),
MinimumMeasurementsToUse = row.ConvertField <int>("MinimumMeasurementsToUse"),
FramesPerSecond = Convert.ToInt32(row.Field <object>("FramesPerSecond") ?? 30),
LagTime = row.ConvertField <double>("LagTime"),
LeadTime = row.ConvertField <double>("LeadTime"),
UseLocalClockAsRealTime = Convert.ToBoolean(row.Field <object>("UseLocalClockAsRealTime")),
AllowSortsByArrival = Convert.ToBoolean(row.Field <object>("AllowSortsByArrival")),
LoadOrder = row.ConvertField <int>("LoadOrder"),
Enabled = Convert.ToBoolean(row.Field <object>("Enabled")),
IgnoreBadTimeStamps = Convert.ToBoolean(row.Field <object>("IgnoreBadTimeStamps")),
TimeResolution = Convert.ToInt32(row.Field <object>("TimeResolution")),
AllowPreemptivePublishing = Convert.ToBoolean(row.Field <object>("AllowPreemptivePublishing")),
DownsamplingMethod = row.Field <string>("DownSamplingMethod"),
m_nodeName = row.Field <string>("NodeName"),
PerformTimestampReasonabilityCheck = Convert.ToBoolean(row.Field <object>("PerformTimeReasonabilityCheck"))
};
}
}
return(new ObservableCollection <CalculatedMeasurement>(calculatedMeasurementList ?? new CalculatedMeasurement[0]));
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Loads <see cref="Adapter"/> information as an <see cref="ObservableCollection{T}"/> style list.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="adapterType"><see cref="AdapterType"/> collection to be returned.</param>
/// <param name="keys">Keys of the adapters to be loaded from the database.</param>
/// <returns>Collection of <see cref="Adapter"/>.</returns>
public static ObservableCollection <Adapter> Load(AdoDataConnection database, AdapterType adapterType, IList <int> keys)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
string viewName;
if (adapterType == AdapterType.Action)
{
viewName = "CustomActionAdapterDetail";
}
else if (adapterType == AdapterType.Filter)
{
viewName = "CustomFilterAdapterDetail";
}
else if (adapterType == AdapterType.Input)
{
viewName = "CustomInputAdapterDetail";
}
else
{
viewName = "CustomOutputAdapterDetail";
}
string query;
string commaSeparatedKeys;
Adapter[] adapterList = null;
DataTable adapterTable;
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, ID, AdapterName, AssemblyName, TypeName, ConnectionString, " +
"LoadOrder, Enabled, NodeName FROM {0} WHERE NodeID = {{0}} AND ID IN ({1})", viewName, commaSeparatedKeys), "nodeID");
adapterTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID());
adapterList = new Adapter[adapterTable.Rows.Count];
foreach (DataRow row in adapterTable.Rows)
{
id = row.ConvertField <int>("ID");
adapterList[keys.IndexOf(id)] = new Adapter()
{
NodeID = database.Guid(row, "NodeID"),
ID = id,
AdapterName = row.Field <string>("AdapterName"),
AssemblyName = row.Field <string>("AssemblyName"),
TypeName = row.Field <string>("TypeName"),
ConnectionString = row.Field <string>("ConnectionString"),
LoadOrder = row.ConvertField <int>("LoadOrder"),
Enabled = Convert.ToBoolean(row.Field <object>("Enabled")),
NodeName = row.Field <string>("NodeName"),
Type = adapterType
};
}
}
return(new ObservableCollection <Adapter>(adapterList ?? new Adapter[0]));
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Retrieves a <see cref="Node"/> defined in the database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <returns><see cref="Node"/> information.</returns>
public static Node GetCurrentNode(AdoDataConnection database)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
DataTable nodeTable;
string query;
query = database.ParameterizedQueryString("Select ID, Name, CompanyID, Longitude, Latitude, Description, " +
"ImagePath, Settings, MenuData, MenuType, Master, LoadOrder, Enabled, CompanyName " +
"From NodeDetail WHERE ID = {0} ORDER BY LoadOrder", "id");
nodeTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID());
if (nodeTable.Rows.Count == 0)
{
return(null);
}
DataRow row = nodeTable.Rows[0];
Node node = new Node
{
ID = database.Guid(row, "ID"),
Name = row.Field <string>("Name"),
CompanyID = row.ConvertNullableField <int>("CompanyID"),
Longitude = row.ConvertNullableField <decimal>("Longitude"),
Latitude = row.ConvertNullableField <decimal>("Latitude"),
Description = row.Field <string>("Description"),
ImagePath = row.Field <string>("ImagePath"),
Settings = row.Field <string>("Settings"),
MenuType = row.Field <string>("MenuType"),
MenuData = row.Field <string>("MenuData"),
Master = Convert.ToBoolean(row.Field <object>("Master")),
LoadOrder = row.ConvertField <int>("LoadOrder"),
Enabled = Convert.ToBoolean(row.Field <object>("Enabled")),
m_companyName = row.Field <string>("CompanyName")
};
return(node);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Saves <see cref="CalculatedMeasurement"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="calculatedMeasurement">Information about <see cref="CalculatedMeasurement"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, CalculatedMeasurement calculatedMeasurement)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (calculatedMeasurement.ID == 0)
{
query = database.ParameterizedQueryString("INSERT INTO CalculatedMeasurement (NodeID, Acronym, Name, AssemblyName, TypeName, ConnectionString, " +
"ConfigSection, InputMeasurements, OutputMeasurements, MinimumMeasurementsToUse, FramesPerSecond, LagTime, LeadTime, UseLocalClockAsRealTime, " +
"AllowSortsByArrival, LoadOrder, Enabled, IgnoreBadTimeStamps, TimeResolution, AllowPreemptivePublishing, DownsamplingMethod, " +
"PerformTimeReasonabilityCheck, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) Values ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, " +
"{9}, {10}, {11}, {12}, {13}, {14}, {15}, {16}, {17}, {18}, {19}, {20}, {21}, {22}, {23}, {24}, {25})", "nodeID", "acronym", "name",
"assemblyName", "typeName", "connectionString", "configSection", "inputMeasurements", "outputMeasurements", "minimumMeasurementsToUse",
"framesPerSecond", "lagTime", "leadTime", "useLocalClockAsRealTime", "allowSortsByArrival", "loadOrder", "enabled", "ignoreBadTimeStamps",
"timeResolution", "allowPreemptivePublishing", "downsamplingMethod", "performTimeReasonabilityCheck", "updatedBy", "updatedOn",
"createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query,
DefaultTimeout, database.CurrentNodeID(), calculatedMeasurement.Acronym.Replace(" ", "").ToUpper(), calculatedMeasurement.Name.ToNotNull(),
calculatedMeasurement.AssemblyName, calculatedMeasurement.TypeName, calculatedMeasurement.ConnectionString.ToNotNull(), calculatedMeasurement.ConfigSection.ToNotNull(),
calculatedMeasurement.InputMeasurements.ToNotNull(), calculatedMeasurement.OutputMeasurements.ToNotNull(), calculatedMeasurement.MinimumMeasurementsToUse,
calculatedMeasurement.FramesPerSecond, calculatedMeasurement.LagTime, calculatedMeasurement.LeadTime, database.Bool(calculatedMeasurement.UseLocalClockAsRealTime),
database.Bool(calculatedMeasurement.AllowSortsByArrival), calculatedMeasurement.LoadOrder, database.Bool(calculatedMeasurement.Enabled),
database.Bool(calculatedMeasurement.IgnoreBadTimeStamps), calculatedMeasurement.TimeResolution, database.Bool(calculatedMeasurement.AllowPreemptivePublishing),
calculatedMeasurement.DownsamplingMethod, database.Bool(calculatedMeasurement.PerformTimestampReasonabilityCheck), CommonFunctions.CurrentUser,
database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow);
}
else
{
query = database.ParameterizedQueryString("UPDATE CalculatedMeasurement SET NodeID = {0}, Acronym = {1}, Name = {2}, AssemblyName = {3}, " +
"TypeName = {4}, ConnectionString = {5}, ConfigSection = {6}, InputMeasurements = {7}, OutputMeasurements = {8}, MinimumMeasurementsToUse = {9}, " +
"FramesPerSecond = {10}, LagTime = {11}, LeadTime = {12}, UseLocalClockAsRealTime = {13}, AllowSortsByArrival = {14}, LoadOrder = {15}, " +
"Enabled = {16}, IgnoreBadTimeStamps = {17}, TimeResolution = {18}, AllowPreemptivePublishing = {19}, DownsamplingMethod = {20}, " +
"PerformTimeReasonabilityCheck = {21}, UpdatedBy = {22}, UpdatedOn = {23} WHERE ID = {24}", "nodeID", "acronym", "name", "assemblyName",
"typeName", "connectionString", "configSection", "inputMeasurements", "outputMeasurements", "minimumMeasurementsToUse", "framesPerSecond",
"lagTime", "leadTime", "useLocalClockAsRealTime", "allowSortsByArrival", "loadOrder", "enabled", "ignoreBadTimeStamps", "timeResolution",
"allowPreemptivePublishing", "downsamplingMethod", "performTimeReasonabilityCheck", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(calculatedMeasurement.NodeID),
calculatedMeasurement.Acronym.Replace(" ", "").ToUpper(), calculatedMeasurement.Name.ToNotNull(), calculatedMeasurement.AssemblyName,
calculatedMeasurement.TypeName, calculatedMeasurement.ConnectionString.ToNotNull(), calculatedMeasurement.ConfigSection.ToNotNull(), calculatedMeasurement.InputMeasurements.ToNotNull(),
calculatedMeasurement.OutputMeasurements.ToNotNull(), calculatedMeasurement.MinimumMeasurementsToUse, calculatedMeasurement.FramesPerSecond,
calculatedMeasurement.LagTime, calculatedMeasurement.LeadTime, database.Bool(calculatedMeasurement.UseLocalClockAsRealTime), database.Bool(calculatedMeasurement.AllowSortsByArrival),
calculatedMeasurement.LoadOrder, database.Bool(calculatedMeasurement.Enabled), database.Bool(calculatedMeasurement.IgnoreBadTimeStamps), calculatedMeasurement.TimeResolution,
database.Bool(calculatedMeasurement.AllowPreemptivePublishing), calculatedMeasurement.DownsamplingMethod, database.Bool(calculatedMeasurement.PerformTimestampReasonabilityCheck),
CommonFunctions.CurrentUser, database.UtcNow, calculatedMeasurement.ID);
}
return("Calculated measurement information saved successfully");
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
// Loads the data set used to determine effective permissions
private void LoadPermissionsDataSet()
{
Dictionary <string, string> dataTableDefinitions = new Dictionary <string, string>()
{
{ "ActiveMeasurement", "ActiveMeasurements" },
{ "Subscriber", "Subscribers" },
{ "SubscriberMeasurement", "SubscriberMeasurements" },
{ "SubscriberMeasurementGroup", "SubscriberMeasurementGroups" },
{ "MeasurementGroup", "MeasurementGroups" },
{ "MeasurementGroupMeasurement", "MeasurementGroupMeasurements" }
};
DataTable dataTable;
string queryFormat;
string parameterizedQuery;
m_subscriberPermissionsDataSet = new DataSet();
using (AdoDataConnection database = new AdoDataConnection(CommonFunctions.DefaultSettingsCategory))
{
foreach (KeyValuePair <string, string> definition in dataTableDefinitions)
{
queryFormat = string.Format("SELECT * FROM {0} WHERE NodeID = {{0}}", definition.Key);
parameterizedQuery = database.ParameterizedQueryString(queryFormat, "nodeID");
dataTable = database.Connection.RetrieveData(database.AdapterType, parameterizedQuery, DataExtensions.DefaultTimeoutDuration, database.CurrentNodeID());
dataTable.TableName = definition.Value;
dataTable.DataSet.Tables.Remove(dataTable);
m_subscriberPermissionsDataSet.Tables.Add(dataTable);
}
}
UpdateEffectivePermissions();
}
// Static Methods
/// <summary>
/// Loads <see cref="RealTimeStream"/> information as an <see cref="ObservableCollection{T}"/> style list.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <returns>Collection of <see cref="RealTimeStream"/>.</returns>
public static ObservableCollection <RealTimeStream> Load(AdoDataConnection database)
{
bool createdConnection = false;
try
{
ObservableCollection <RealTimeStream> realTimeStreamList = null;
createdConnection = CreateConnection(ref database);
DataSet resultSet = new DataSet();
resultSet.EnforceConstraints = false;
// Get PDCs list.
resultSet.Tables.Add(database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, Acronym, Name, ConnectionString, CompanyName, Enabled FROM DeviceDetail " +
"WHERE NodeID = {0} AND IsConcentrator = {1} AND Enabled = {2} ORDER BY Acronym", "nodeID", "isConcentrator", "enabled"), DefaultTimeout, database.CurrentNodeID(), database.Bool(true), database.Bool(true)).Copy());
resultSet.Tables[0].TableName = "PdcTable";
// Add a dummy device row in PDC table to associate PMUs which are not PDC and connected directly.
DataRow row = resultSet.Tables["PdcTable"].NewRow();
row["ID"] = 0;
row["Acronym"] = string.Empty;
row["Name"] = "Devices Connected Directly";
row["CompanyName"] = string.Empty;
row["Enabled"] = false;
resultSet.Tables["PdcTable"].Rows.Add(row);
// Get Non-PDC device list.
resultSet.Tables.Add(database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, Acronym, Name, AccessID, CompanyName, ProtocolName, VendorDeviceName, " +
"ParentAcronym, Enabled FROM DeviceDetail WHERE NodeID = {0} AND IsConcentrator = {1} AND Enabled = {2} ORDER BY Acronym", "nodeID", "isConcentrator", "enabled"),
DefaultTimeout, database.CurrentNodeID(), database.Bool(false), database.Bool(true)).Copy());
resultSet.Tables[1].TableName = "DeviceTable";
// Get non-statistical measurement list
resultSet.Tables.Add(database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, DeviceID, SignalID, PointID, PointTag, SignalReference, " +
"SignalAcronym, Description, SignalName, EngineeringUnits, HistorianAcronym, Subscribed, Internal FROM MeasurementDetail WHERE NodeID = {0} AND " +
"SignalAcronym <> {1} ORDER BY SignalReference", "nodeID", "signalAcronym"), DefaultTimeout, database.CurrentNodeID(), "STAT").Copy());
resultSet.Tables[2].TableName = "MeasurementTable";
// Query for any non-statistical measurements that are subscribed via GEP, but are a part of another node in the same database
// IMPORTANT: Make sure columns in this external node query exactly match those of the previous primary measurement query
DataTable otherMeasurements = database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, 0 AS DeviceID, SignalID, PointID, PointTag, SignalReference, " +
"SignalAcronym, Description, SignalName, EngineeringUnits, HistorianAcronym, Subscribed, Internal FROM MeasurementDetail WHERE NodeID <> {0} AND " +
"SignalAcronym <> {1} AND Subscribed <> 0 ORDER BY SignalReference", "nodeID", "signalAcronym"), DefaultTimeout, database.CurrentNodeID(), "STAT");
realTimeStreamList = new ObservableCollection <RealTimeStream>(
from pdc in resultSet.Tables["PdcTable"].AsEnumerable()
let settings = pdc.Field <string>("ConnectionString").ToNonNullString().ParseKeyValuePairs()
select new RealTimeStream
{
ID = pdc.ConvertField <int>("ID"),
Acronym = string.IsNullOrEmpty(pdc.Field <string>("Acronym")) ? "DIRECT CONNECTED" : pdc.Field <string>("Acronym"),
Name = pdc.Field <string>("Name"),
CompanyName = pdc.Field <string>("CompanyName"),
StatusColor = string.IsNullOrEmpty(pdc.Field <string>("Acronym")) ? "Transparent" : "Gray",
Enabled = Convert.ToBoolean(pdc.Field <object>("Enabled")),
Expanded = false,
DeviceList = new ObservableCollection <RealTimeDevice>(
from device in resultSet.Tables["DeviceTable"].AsEnumerable()
where device.Field <string>("ParentAcronym").ToNonNullString() == pdc.Field <string>("Acronym") && device.ConvertField <int>("AccessID") != GroupAccessID
select new RealTimeDevice
{
ID = device.ConvertNullableField <int>("ID"),
Acronym = device.Field <string>("Acronym"),
Name = device.Field <string>("Name"),
ProtocolName = device.Field <string>("ProtocolName"),
VendorDeviceName = device.Field <string>("VendorDeviceName"),
ParentAcronym = string.IsNullOrEmpty(device.Field <string>("ParentAcronym")) ? "DIRECT CONNECTED" : device.Field <string>("ParentAcronym"),
Expanded = false,
StatusColor = device.ConvertNullableField <int>("ID") == null ? "Transparent" : "Gray",
Enabled = Convert.ToBoolean(device.Field <object>("Enabled")),
MeasurementList = new ObservableCollection <RealTimeMeasurement>(
from measurement in resultSet.Tables["MeasurementTable"].AsEnumerable()
where measurement.ConvertNullableField <int>("DeviceID") == device.ConvertNullableField <int>("ID") && (measurement.ConvertField <bool>("Subscribed") || measurement.ConvertField <bool>("Internal") || (settings.ContainsKey("securityMode") && settings["securityMode"].Equals("None", StringComparison.OrdinalIgnoreCase))) //We will only display measurements which are internal or subscribed to avoid confusion.
select new RealTimeMeasurement
{
ID = measurement.Field <string>("ID"),
DeviceID = measurement.ConvertNullableField <int>("DeviceID"),
SignalID = Guid.Parse(measurement.Field <object>("SignalID").ToString()),
PointID = measurement.ConvertField <int>("PointID"),
PointTag = measurement.Field <string>("PointTag"),
SignalReference = measurement.Field <string>("SignalReference"),
Description = measurement.Field <string>("description"),
SignalName = measurement.Field <string>("SignalName"),
SignalAcronym = measurement.Field <string>("SignalAcronym"),
EngineeringUnit = measurement.Field <string>("SignalAcronym") == "FLAG" ? "Hex" : measurement.Field <string>("EngineeringUnits"),
Expanded = false,
Selected = false,
Selectable = measurement.Field <string>("SignalAcronym") == "IPHM" || measurement.Field <string>("SignalAcronym") == "IPHA" || measurement.Field <string>("SignalAcronym") == "VPHM" || measurement.Field <string>("SignalAcronym") == "VPHA" || measurement.Field <string>("SignalAcronym") == "FREQ",
LongTimeTag = "N/A",
TimeTag = "N/A",
Value = "--",
Quality = "N/A",
Foreground = new SolidColorBrush(Color.FromArgb(255, 0, 0, 0))
}
)
}
)
}
);
if (otherMeasurements.Rows.Count > 0)
{
// Add subscribed measurements from other nodes
realTimeStreamList.Add(new RealTimeStream
{
ID = 0,
Acronym = "SUBSCRIBED",
Name = "Subscribed Measurements",
CompanyName = string.Empty,
StatusColor = "Transparent",
Enabled = false,
Expanded = false,
DeviceList = new ObservableCollection <RealTimeDevice>(
otherMeasurements.Rows
.Cast <DataRow>()
.Where(measurement => measurement.ConvertNullableField <int>("DeviceID") == null)
.Select(measurement => measurement.Field <string>("SignalReference"))
.Select(GetSourceName)
.Distinct()
.Select(source => new RealTimeDevice
{
ID = 0,
Acronym = source,
Name = source,
ProtocolName = string.Empty,
VendorDeviceName = string.Empty,
ParentAcronym = "SUBSCRIBED",
Expanded = false,
StatusColor = "Gray",
Enabled = false,
MeasurementList = new ObservableCollection <RealTimeMeasurement>(
otherMeasurements.Rows
.Cast <DataRow>()
.Where(measurement => measurement.ConvertNullableField <int>("DeviceID") == null && measurement.Field <string>("SignalReference").StartsWith(source))
.Select(measurement => new RealTimeMeasurement
{
ID = measurement.Field <string>("ID"),
DeviceID = measurement.ConvertNullableField <int>("DeviceID"),
SignalID = Guid.Parse(measurement.Field <object>("SignalID").ToString()),
PointID = measurement.ConvertField <int>("PointID"),
PointTag = measurement.Field <string>("PointTag"),
SignalReference = measurement.Field <string>("SignalReference"),
Description = measurement.Field <string>("description"),
SignalName = measurement.Field <string>("SignalName"),
SignalAcronym = measurement.Field <string>("SignalAcronym"),
EngineeringUnit = measurement.Field <string>("SignalAcronym") == "FLAG" ? "Hex" : measurement.Field <string>("EngineeringUnits"),
Expanded = false,
Selected = false,
Selectable = measurement.Field <string>("SignalAcronym") == "IPHM" || measurement.Field <string>("SignalAcronym") == "IPHA" || measurement.Field <string>("SignalAcronym") == "VPHM" || measurement.Field <string>("SignalAcronym") == "VPHA" || measurement.Field <string>("SignalAcronym") == "FREQ",
LongTimeTag = "N/A",
TimeTag = "N/A",
Value = "--",
Quality = "N/A",
Foreground = new SolidColorBrush(Color.FromArgb(255, 0, 0, 0))
})
)
})
)
});
}
if (resultSet.Tables["MeasurementTable"].Select("DeviceID IS NULL").Length > 0)
{
// Add direct connected measurements with no associated device (DeviceID IS NULL)
realTimeStreamList.Add(new RealTimeStream
{
ID = 0,
Acronym = "CALCULATED",
Name = "Calculated Measurements",
CompanyName = string.Empty,
StatusColor = "Transparent",
Enabled = false,
Expanded = false,
DeviceList = new ObservableCollection <RealTimeDevice>(
resultSet.Tables["MeasurementTable"].Rows
.Cast <DataRow>()
.Where(measurement => measurement.ConvertNullableField <int>("DeviceID") == null)
.Select(measurement => measurement.Field <string>("SignalReference"))
.Select(GetSourceName)
.Distinct()
.Select(source => new RealTimeDevice
{
ID = 0,
Acronym = source,
Name = source,
ProtocolName = string.Empty,
VendorDeviceName = string.Empty,
ParentAcronym = "CALCULATED",
Expanded = false,
StatusColor = "Gray",
Enabled = false,
MeasurementList = new ObservableCollection <RealTimeMeasurement>(
resultSet.Tables["MeasurementTable"].Rows
.Cast <DataRow>()
.Where(measurement => measurement.ConvertNullableField <int>("DeviceID") == null && measurement.Field <string>("SignalReference").StartsWith(source))
.Select(measurement => new RealTimeMeasurement
{
ID = measurement.Field <string>("ID"),
DeviceID = measurement.ConvertNullableField <int>("DeviceID"),
SignalID = Guid.Parse(measurement.Field <object>("SignalID").ToString()),
PointID = measurement.ConvertField <int>("PointID"),
PointTag = measurement.Field <string>("PointTag"),
SignalReference = measurement.Field <string>("SignalReference"),
Description = measurement.Field <string>("description"),
SignalName = measurement.Field <string>("SignalName"),
SignalAcronym = measurement.Field <string>("SignalAcronym"),
EngineeringUnit = measurement.Field <string>("SignalAcronym") == "FLAG" ? "Hex" : measurement.Field <string>("EngineeringUnits"),
Expanded = false,
Selected = false,
Selectable = measurement.Field <string>("SignalAcronym") == "IPHM" || measurement.Field <string>("SignalAcronym") == "IPHA" || measurement.Field <string>("SignalAcronym") == "VPHM" || measurement.Field <string>("SignalAcronym") == "VPHA" || measurement.Field <string>("SignalAcronym") == "FREQ",
LongTimeTag = "N/A",
TimeTag = "N/A",
Value = "--",
Quality = "N/A",
Foreground = new SolidColorBrush(Color.FromArgb(255, 0, 0, 0))
})
)
})
)
});
}
// Assign parent references for real-time measurements
foreach (RealTimeStream stream in realTimeStreamList)
{
foreach (RealTimeDevice device in stream.DeviceList)
{
device.Parent = stream;
foreach (RealTimeMeasurement measurement in device.MeasurementList)
{
measurement.Parent = device;
}
}
}
return(realTimeStreamList);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
// Static Methods
/// <summary>
/// Loads <see cref="UserAccount"/> information as an OberservableCollection{T}"/> style list.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <returns>Collection of <see cref="UserAccount"/></returns>
public static ObservableCollection<UserAccount> Load(AdoDataConnection database)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
ObservableCollection<UserAccount> userAccountList = new ObservableCollection<UserAccount>();
DataTable userAccountTable = database.Connection.RetrieveData(database.AdapterType, "SELECT * From UserAccount WHERE DefaultNodeID = '" + database.CurrentNodeID() + "' ORDER BY Name");
foreach (DataRow row in userAccountTable.Rows)
{
userAccountList.Add(new UserAccount()
{
ID = database.Guid(row, "ID"),
Name = UserInfo.SIDToAccountName(row.Field<string>("Name")),
Password = row.Field<object>("Password") == null ? string.Empty : row.Field<string>("Password"),
FirstName = row.Field<object>("FirstName") == null ? string.Empty : row.Field<string>("FirstName"),
LastName = row.Field<object>("LastName") == null ? string.Empty : row.Field<string>("LastName"),
DefaultNodeID = database.Guid(row, "DefaultNodeID"),
Phone = row.Field<object>("Phone") == null ? string.Empty : row.Field<string>("Phone"),
Email = row.Field<object>("Email") == null ? string.Empty : row.Field<string>("Email"),
LockedOut = Convert.ToBoolean(row.Field<object>("LockedOut")),
UseADAuthentication = Convert.ToBoolean(row.Field<object>("UseADAuthentication")),
ChangePasswordOn = row.Field<object>("ChangePasswordOn") == null ? DateTime.MinValue : Convert.ToDateTime(row.Field<object>("ChangePasswordOn")),
CreatedOn = Convert.ToDateTime(row["CreatedOn"]),
CreatedBy = row.Field<string>("CreatedBy"),
UpdatedOn = Convert.ToDateTime(row.Field<object>("UpdatedOn")),
UpdatedBy = row.Field<string>("UpdatedBy")
});
}
userAccountList.Insert(0, new UserAccount
{
ID = Guid.Empty,
ChangePasswordOn = DateTime.Now.AddDays(90)
});
return userAccountList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Loads <see cref="Node"/> information as an <see cref="ObservableCollection{T}"/> style list.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <returns>Collection of <see cref="Subscriber"/>.</returns>
public static ObservableCollection<Subscriber> Load(AdoDataConnection database)
{
ObservableCollection<Subscriber> subscriberList;
DataTable subscriberTable;
bool createdConnection = false;
string query;
SslPolicyErrors validPolicyErrors;
X509ChainStatusFlags validChainFlags;
try
{
createdConnection = CreateConnection(ref database);
subscriberList = new ObservableCollection<Subscriber>();
query = database.ParameterizedQueryString("SELECT ID, NodeID, Acronym, Name, SharedSecret, AuthKey, ValidIPAddresses, RemoteCertificateFile," +
" ValidPolicyErrors, ValidChainFlags, AccessControlFilter, Enabled FROM Subscriber WHERE NodeID = {0} ORDER BY Name", "nodeID");
subscriberTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID());
foreach (DataRow row in subscriberTable.Rows)
{
subscriberList.Add(new Subscriber()
{
ID = database.Guid(row, "ID"),
NodeID = database.Guid(row, "NodeID"),
Acronym = row.Field<string>("Acronym"),
Name = row.Field<string>("Name"),
SharedSecret = row.Field<string>("SharedSecret"),
AuthKey = row.Field<string>("AuthKey"),
ValidIPAddresses = row.Field<string>("ValidIPAddresses"),
RemoteCertificateFile = row.Field<string>("RemoteCertificateFile"),
ValidPolicyErrors = Enum.TryParse(row.Field<string>("ValidPolicyErrors"), out validPolicyErrors) ? validPolicyErrors : (SslPolicyErrors?)null,
ValidChainFlags = Enum.TryParse(row.Field<string>("ValidChainFlags"), out validChainFlags) ? validChainFlags : (X509ChainStatusFlags?)null,
AccessControlFilter = row.Field<string>("AccessControlFilter"),
Enabled = Convert.ToBoolean(row.Field<object>("Enabled")),
AllowedMeasurementGroups = GetAllowedMeasurementGroups(database, database.Guid(row, "ID")),
DeniedMeasurementGroups = GetDeniedMeasurementGroups(database, database.Guid(row, "ID")),
AvailableMeasurementGroups = GetAvailableMeasurementGroups(database, database.Guid(row, "ID")),
StatusColor = "gray",
Version = ""
});
}
return subscriberList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Saves <see cref="UserAccount"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="userAccount">Information about <see cref="UserAccount"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, UserAccount userAccount)
{
const string ErrorMessage = "User name already exists.";
bool createdConnection = false;
string query;
string userAccountSID;
int existing;
try
{
createdConnection = CreateConnection(ref database);
string pColumn = "Password";
if (database.IsJetEngine)
pColumn = "[Password]";
object changePasswordOn = userAccount.ChangePasswordOn;
if (userAccount.ChangePasswordOn == DateTime.MinValue)
changePasswordOn = (object)DBNull.Value;
else if (database.IsJetEngine)
changePasswordOn = userAccount.ChangePasswordOn.ToOADate();
userAccountSID = UserInfo.UserNameToSID(userAccount.Name);
if (!userAccount.UseADAuthentication || !UserInfo.IsUserSID(userAccountSID))
userAccountSID = userAccount.Name;
if (userAccount.ID == Guid.Empty)
{
existing = Convert.ToInt32(database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT COUNT(*) FROM UserAccount WHERE Name = {0}", "name"), DefaultTimeout, userAccountSID));
if (existing > 0)
throw new InvalidOperationException(ErrorMessage);
query = database.ParameterizedQueryString("INSERT INTO UserAccount (Name, " + pColumn + ", FirstName, LastName, DefaultNodeID, Phone, Email, " +
"LockedOut, UseADAuthentication, ChangePasswordOn, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, " +
"{9}, {10}, {11}, {12}, {13})", "name", "password", "firstName", "lastName", "defaultNodeID", "phone", "email", "lockedOut", "useADAuthentication",
"changePasswordOn", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, userAccountSID,
userAccount.Password.ToNotNull(), userAccount.FirstName.ToNotNull(), userAccount.LastName.ToNotNull(), database.CurrentNodeID(),
userAccount.Phone.ToNotNull(), userAccount.Email.ToNotNull(), database.Bool(userAccount.LockedOut), database.Bool(userAccount.UseADAuthentication),
changePasswordOn, CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow);
CommonFunctions.LogEvent(string.Format("New user \"{0}\" created successfully by user \"{1}\".", userAccount.Name, CommonFunctions.CurrentUser), 2);
}
else
{
existing = database.ExecuteScalar<int>("SELECT COUNT(*) FROM UserAccount WHERE Name = {0} AND ID <> {1}", userAccountSID, userAccount.ID);
if (existing > 0)
throw new InvalidOperationException(ErrorMessage);
query = database.ParameterizedQueryString("UPDATE UserAccount SET Name = {0}, " + pColumn + " = {1}, FirstName = {2}, LastName = {3}, " +
"DefaultNodeID = {4}, Phone = {5}, Email = {6}, LockedOut = {7}, UseADAuthentication = {8}, ChangePasswordOn = {9}, UpdatedBy = {10}, " +
"UpdatedOn = {11} WHERE ID = {12}", "name", "password", "firstName", "lastName", "defaultNodeID", "phone", "email", "lockedOut",
"useADAuthentication", "changePasswordOn", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, userAccountSID,
userAccount.Password.ToNotNull(), userAccount.FirstName.ToNotNull(), userAccount.LastName.ToNotNull(), database.Guid(userAccount.DefaultNodeID),
userAccount.Phone.ToNotNull(), userAccount.Email.ToNotNull(), database.Bool(userAccount.LockedOut), database.Bool(userAccount.UseADAuthentication),
changePasswordOn, CommonFunctions.CurrentUser, database.UtcNow, database.Guid(userAccount.ID));
CommonFunctions.LogEvent(string.Format("Information about user \"{0}\" updated successfully by user \"{1}\".", userAccount.Name, CommonFunctions.CurrentUser), 3);
}
return "User account information saved successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Saves <see cref="Subscriber"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="subscriber">Information about <see cref="Subscriber"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, Subscriber subscriber)
{
bool createdConnection = false;
SslPolicyErrors validPolicyErrors;
X509ChainStatusFlags validChainFlags;
string query;
try
{
createdConnection = CreateConnection(ref database);
validPolicyErrors = (subscriber.ValidPolicyErrors ?? SslPolicyErrors.None) | (subscriber.RemoteCertificateIsSelfSigned ? SslPolicyErrors.RemoteCertificateChainErrors : SslPolicyErrors.None);
validChainFlags = (subscriber.ValidChainFlags ?? X509ChainStatusFlags.NoError) | (subscriber.RemoteCertificateIsSelfSigned ? X509ChainStatusFlags.UntrustedRoot : X509ChainStatusFlags.NoError);
if (subscriber.ID == Guid.Empty)
{
query = database.ParameterizedQueryString("INSERT INTO Subscriber (NodeID, Acronym, Name, SharedSecret, AuthKey, ValidIPAddresses, RemoteCertificateFile, ValidPolicyErrors, ValidChainFlags, " +
"AccessControlFilter, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, " +
"{13}, {14})", "nodeID", "acronym", "name", "sharedSecret", "authKey", "validIPAddresses", "remoteCertificateFile", "validPolicyErrors", "validChainFlags",
"accessControlFilter", "enabled", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), subscriber.Acronym, subscriber.Name.ToNotNull(), subscriber.SharedSecret.ToNotNull(),
subscriber.AuthKey.ToNotNull(), subscriber.ValidIPAddresses.ToNotNull(), subscriber.RemoteCertificateFile.ToNotNull(), validPolicyErrors.ToString(),
validChainFlags.ToString(), subscriber.AccessControlFilter.ToNotNull(), database.Bool(subscriber.Enabled), CommonFunctions.CurrentUser, database.UtcNow,
CommonFunctions.CurrentUser, database.UtcNow);
}
else
{
query = database.ParameterizedQueryString("UPDATE Subscriber SET NodeID = {0}, Acronym = {1}, Name = {2}, SharedSecret = {3}, AuthKey = {4}, ValidIPAddresses = {5}, RemoteCertificateFile = {6}, " +
"ValidPolicyErrors = {7}, ValidChainFlags = {8}, AccessControlFilter = {9}, Enabled = {10}, UpdatedBy = {11}, UpdatedOn = {12} WHERE ID = {13}", "nodeID",
"acronym", "name", "sharedSecret", "authKey", "validIPAddresses", "remoteCertificateFile", "validPolicyErrors", "validChainFlags", "accessControlFilter",
"enabled", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(subscriber.NodeID), subscriber.Acronym, subscriber.Name.ToNotNull(), subscriber.SharedSecret.ToNotNull(),
subscriber.AuthKey.ToNotNull(), subscriber.ValidIPAddresses.ToNotNull(), subscriber.RemoteCertificateFile.ToNotNull(), validPolicyErrors.ToString(),
validChainFlags.ToString(), subscriber.AccessControlFilter.ToNotNull(), database.Bool(subscriber.Enabled), CommonFunctions.CurrentUser, database.UtcNow,
database.Guid(subscriber.ID));
}
try
{
CommonFunctions.SendCommandToService("ReloadConfig");
}
catch (Exception ex)
{
return "Subscriber information saved successfully. Failed to send ReloadConfig command to backend service." + Environment.NewLine + ex.Message;
}
return "Subscriber information saved successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
// Static Methods
/// <summary>
/// Loads <see cref="ApplicationRole"/> information as an <see cref="ObservableCollection{T}"/> style list.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <returns>Collection of <see cref="ApplicationRole"/>.</returns>
public static ObservableCollection <ApplicationRole> Load(AdoDataConnection database)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
ObservableCollection <ApplicationRole> applicationRoleList = new ObservableCollection <ApplicationRole>();
string query = database.ParameterizedQueryString("SELECT * FROM ApplicationRole WHERE NodeID = {0} ORDER BY Name", "nodeID");
DataTable applicationRoleTable = database.Connection.RetrieveData(database.AdapterType, query, database.CurrentNodeID());
foreach (DataRow row in applicationRoleTable.Rows)
{
applicationRoleList.Add(new ApplicationRole()
{
ID = database.Guid(row, "ID"), //Guid.Parse(row.Field<object>("ID").ToString()),
Name = row.Field <string>("Name"),
Description = row.Field <string>("Description"),
NodeID = Guid.Parse(row.Field <object>("NodeID").ToString()),
CreatedOn = row.Field <DateTime>("CreatedOn"),
CreatedBy = row.Field <string>("CreatedBy"),
UpdatedOn = row.Field <DateTime>("UpdatedOn"),
UpdatedBy = row.Field <string>("UpdatedBy"),
CurrentUsers = GetCurrentUsers(database, Guid.Parse(row.Field <object>("ID").ToString())),
PossibleUsers = GetPossibleUsers(database, Guid.Parse(row.Field <object>("ID").ToString())),
CurrentGroups = GetCurrentGroups(database, Guid.Parse(row.Field <object>("ID").ToString())),
PossibleGroups = GetPossibleGroups(database, Guid.Parse(row.Field <object>("ID").ToString()))
});
}
return(applicationRoleList);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
