/// <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")),
AllowedMeasurements = GetAllowedMeasurements(database, database.Guid(row, "ID")),
DeniedMeasurements = GetDeniedMeasurements(database, database.Guid(row, "ID")),
AvailableMeasurements = GetAvailableMeasurements(database, database.Guid(row, "ID")),
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="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="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();
}
}
}
// Static Constructor
static SecurityHub()
{
CategorizedSettingsElementCollection systemSettings = ConfigurationFile.Current.Settings["systemSettings"];
// Retrieve default NodeID
DefaultNodeID = Guid.Parse(systemSettings["NodeID"].Value.ToNonNullString(Guid.NewGuid().ToString()));
// Determine whether the node exists in the database and create it if it doesn't
if (DefaultNodeID != Guid.Empty)
{
using (AdoDataConnection connection = new AdoDataConnection("securityProvider"))
{
const string NodeCountFormat = "SELECT COUNT(*) FROM Node";
const string NodeInsertFormat = "INSERT INTO Node(Name, Description, Enabled) VALUES('Default', 'Default node', 1)";
const string NodeUpdateFormat = "UPDATE Node SET ID = {0}";
int nodeCount = connection.ExecuteScalar <int?>(NodeCountFormat) ?? 0;
if (nodeCount == 0)
{
connection.ExecuteNonQuery(NodeInsertFormat);
connection.ExecuteNonQuery(NodeUpdateFormat, connection.Guid(DefaultNodeID));
}
}
}
}
/// <summary>
/// Retrieves collection of <see cref="UserAccount"/>s currently NOT assinged to security group.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="groupID">ID of <see cref="SecurityGroup"/> to filter users.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> type collection of <see cref="UserAccount"/>s currently NOT assigned to <see cref="SecurityGroup"/>.</returns>
public static Dictionary <Guid, string> GetPossibleUsers(AdoDataConnection database, Guid groupID)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary <Guid, string> possibleGroupUsers = new Dictionary <Guid, string>();
string query = database.ParameterizedQueryString("SELECT ID, Name FROM UserAccount WHERE ID NOT IN (SELECT UserAccountID FROM SecurityGroupUserAccount WHERE SecurityGroupID = {0}) ORDER BY Name", "groupID");
DataTable possibleUsersTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Guid(groupID));
foreach (DataRow row in possibleUsersTable.Rows)
{
possibleGroupUsers[database.Guid(row, "ID")] = UserInfo.SIDToAccountName(row.Field <string>("Name"));
}
return(possibleGroupUsers);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Deletes <see cref="UserAccount"/> from <see cref="ApplicationRole"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="roleID">ID of <see cref="ApplicationRole"/> from which <see cref="UserAccount"/>s are being deleted.</param>
/// <param name="usersToBeDeleted">List of <see cref="UserAccount"/> IDs to be deleted.</param>
/// <returns>string, for display use, indicating success.</returns>
public static string RemoveUsers(AdoDataConnection database, Guid roleID, List <Guid> usersToBeDeleted)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
foreach (Guid id in usersToBeDeleted)
{
string userName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM UserAccount WHERE ID = {0}", "userAccountID"), DefaultTimeout, database.Guid(id)).ToNonNullString();
string roleName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM ApplicationRole WHERE ID = {0}", "applicationRoleID"), DefaultTimeout, database.Guid(roleID)).ToNonNullString();
string query = database.ParameterizedQueryString("DELETE FROM ApplicationRoleUserAccount WHERE ApplicationRoleID = {0} AND UserAccountID = {1}", "roleID", "userID");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(roleID), database.Guid(id));
CommonFunctions.LogEvent($"User \"{UserInfo.SIDToAccountName(userName)}\" removed from role \"{roleName}\" by user \"{CommonFunctions.CurrentUser}\".", 5);
}
return("User accounts deleted from role successfully");
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Adds <see cref="SecurityGroup"/> to <see cref="ApplicationRole"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="roleID">ID of <see cref="ApplicationRole"/> to which <see cref="SecurityGroup"/>s are being added.</param>
/// <param name="groupsToBeAdded">List of <see cref="SecurityGroup"/> IDs to be added.</param>
/// <returns>string, for display use, indicating success.</returns>
public static string AddGroups(AdoDataConnection database, Guid roleID, List <Guid> groupsToBeAdded)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
foreach (Guid id in groupsToBeAdded)
{
string groupName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM SecurityGroup WHERE ID = {0}", "securityGroupID"), DefaultTimeout, database.Guid(id)).ToNonNullString();
string roleName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM ApplicationRole WHERE ID = {0}", "applicationRoleID"), DefaultTimeout, database.Guid(roleID)).ToNonNullString();
string query = database.ParameterizedQueryString("INSERT INTO ApplicationRoleSecurityGroup (ApplicationRoleID, SecurityGroupID) Values ({0}, {1})", "roleID", "groupID");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(roleID), database.Guid(id));
CommonFunctions.LogEvent($"Group \"{UserInfo.SIDToAccountName(groupName)}\" added to role \"{roleName}\" by user \"{CommonFunctions.CurrentUser}\".", 10);
}
return("Security groups added to role successfully");
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Node"/> 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 nodes defined in the database.</returns>
public static Dictionary <Guid, string> GetLookupList(AdoDataConnection database, bool isOptional = false)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary <Guid, string> nodeList = new Dictionary <Guid, string>();
if (isOptional)
{
nodeList.Add(Guid.Empty, "Select Node");
}
string query = database.ParameterizedQueryString("SELECT ID, Name FROM Node WHERE Enabled = {0} ORDER BY LoadOrder", "enabled");
DataTable nodeTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Bool(true));
foreach (DataRow row in nodeTable.Rows)
{
nodeList[database.Guid(row, "ID")] = row.Field <string>("Name");
}
return(nodeList);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
// Static Methods
/// <summary>
/// Loads <see cref="Node"/> 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="Guid"/>.</returns>
public static IList <Guid> LoadKeys(AdoDataConnection database, string sortMember = "", string sortDirection = "")
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
IList <Guid> nodeList = new List <Guid>();
string sortClause = string.Empty;
DataTable nodeTable;
if (!string.IsNullOrEmpty(sortMember))
{
sortClause = string.Format("ORDER BY {0} {1}", sortMember, sortDirection);
}
nodeTable = database.Connection.RetrieveData(database.AdapterType, string.Format("Select ID From NodeDetail {0}", sortClause));
foreach (DataRow row in nodeTable.Rows)
{
nodeList.Add(database.Guid(row, "ID"));
}
return(nodeList);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Deletes <see cref="UserAccount"/> from <see cref="SecurityGroup"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="groupID">ID of <see cref="SecurityGroup"/> from which <see cref="UserAccount"/>s are being deleted.</param>
/// <param name="usersToBeDeleted">List of <see cref="UserAccount"/> IDs to be deleted.</param>
/// <returns>string, for display use, indicating success.</returns>
public static string RemoveUsers(AdoDataConnection database, Guid groupID, List <Guid> usersToBeDeleted)
{
bool createdConnection = false;
string query;
string userName;
string securityGroupName;
try
{
createdConnection = CreateConnection(ref database);
foreach (Guid id in usersToBeDeleted)
{
userName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM UserAccount WHERE ID = {0}", "userID"), database.Guid(id)).ToNonNullString();
securityGroupName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM SecurityGroup WHERE ID = {0}", "securityGroupID"), database.Guid(groupID)).ToNonNullString();
query = database.ParameterizedQueryString("DELETE FROM SecurityGroupUserAccount WHERE SecurityGroupID = {0} AND UserAccountID = {1}", "groupID", "userID");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(groupID), database.Guid(id));
CommonFunctions.LogEvent(string.Format("User \"{0}\" successfully removed from security group \"{1}\" by user \"{2}\".", UserInfo.SIDToAccountName(userName), UserInfo.SIDToAccountName(securityGroupName), CommonFunctions.CurrentUser), 9);
}
return("User accounts deleted from group successfully");
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Retrieves collection of <see cref="SecurityGroup"/>s assigned to <see cref="ApplicationRole"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="roleID">ID of the <see cref="ApplicationRole"/> to search for.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> type collection of <see cref="SecurityGroup"/>.</returns>
public static Dictionary <Guid, string> GetCurrentGroups(AdoDataConnection database, Guid roleID)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary <Guid, string> currentGroups = new Dictionary <Guid, string>();
string query = database.ParameterizedQueryString("SELECT * FROM AppRoleSecurityGroupDetail WHERE ApplicationRoleID = {0} ORDER BY SecurityGroupName", "applicationRoleID");
DataTable currentGroupsTable = database.Connection.RetrieveData(database.AdapterType, query, database.Guid(roleID));
foreach (DataRow row in currentGroupsTable.Rows)
{
currentGroups[database.Guid(row, "SecurityGroupID")] = UserInfo.SIDToAccountName(row.Field <string>("SecurityGroupName"));
}
return(currentGroups);
}
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>
/// Deletes <see cref="SecurityGroup"/> from <see cref="ApplicationRole"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="roleID">ID of <see cref="ApplicationRole"/> from which <see cref="SecurityGroup"/>s are being deleted.</param>
/// <param name="groupsToBeDeleted">List of <see cref="SecurityGroup"/> IDs to be deleted.</param>
/// <returns>string, for display use, indicating success.</returns>
public static string RemoveGroups(AdoDataConnection database, Guid roleID, List <Guid> groupsToBeDeleted)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
foreach (Guid id in groupsToBeDeleted)
{
string groupName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM SecurityGroup WHERE ID = {0}", "securityGroupID"), DefaultTimeout, database.Guid(id)).ToNonNullString();
string roleName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM ApplicationRole WHERE ID = {0}", "applicationRoleID"), DefaultTimeout, database.Guid(roleID)).ToNonNullString();
string query = database.ParameterizedQueryString("DELETE FROM ApplicationRoleSecurityGroup WHERE ApplicationRoleID = {0} AND SecurityGroupID = {1}", "roleID", "groupID");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(roleID), database.Guid(id));
CommonFunctions.LogEvent(string.Format("Group \"{0}\" removed from role \"{1}\" by user \"{2}\".", UserInfo.SIDToAccountName(groupName), roleName, CommonFunctions.CurrentUser), 11);
}
return("Security groups deleted from role successfully");
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Retrieves <see cref="Dictionary{T1,T2}"/> type collection of <see cref="Measurement"/> denied for <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 filter data.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> type collection of SignalID and PointTag of <see cref="Measurement"/>.</returns>
public static Dictionary <Guid, string> GetDeniedMeasurements(AdoDataConnection database, Guid subscriberID)
{
Dictionary <Guid, string> deniedMeasurements;
DataTable deniedMeasurementTable;
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
deniedMeasurements = new Dictionary <Guid, string>();
query = database.ParameterizedQueryString("SELECT SignalID, PointTag FROM SubscriberMeasurementDetail WHERE SubscriberID = {0} AND Allowed = {1} ORDER BY PointTag", "subscriberID", "allowed");
deniedMeasurementTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Guid(subscriberID), database.Bool(false));
foreach (DataRow row in deniedMeasurementTable.Rows)
{
deniedMeasurements[database.Guid(row, "SignalID")] = row.Field <string>("PointTag");
}
return(deniedMeasurements);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Retrieves collection of <see cref="UserAccount"/>s currently assinged to security group.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="groupID">ID of <see cref="SecurityGroup"/> to filter users.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> type collection of <see cref="UserAccount"/>s currently assigned to <see cref="SecurityGroup"/>.</returns>
public static Dictionary <Guid, string> GetCurrentUsers(AdoDataConnection database, Guid groupID)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary <Guid, string> currentUsers = new Dictionary <Guid, string>();
DataTable currentUsersTable = database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT * FROM SecurityGroupUserAccountDetail WHERE SecurityGroupID = {0} ORDER BY UserName", "groupID"), database.Guid(groupID));
foreach (DataRow row in currentUsersTable.Rows)
{
currentUsers[database.Guid(row, "UserAccountID")] = UserInfo.SIDToAccountName(row.Field <string>("UserName"));
}
return(currentUsers);
}
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>
/// Validates security roles for all defined nodes.
/// </summary>
/// <param name="database">Data connection to use for database operations.</param>
/// <param name="defaultSecurityRoles">Default security roles that should exist.</param>
private static void ValidateSecurityRoles(AdoDataConnection database, string defaultSecurityRoles)
{
// Queries
const string RoleCountFormat = "SELECT COUNT(*) FROM ApplicationRole WHERE NodeID = {0} AND Name = {1}";
if (string.IsNullOrEmpty(defaultSecurityRoles))
{
defaultSecurityRoles = "Administrator, Owner, Viewer";
}
string[] roles = defaultSecurityRoles.Split(',').Select(role => role.Trim()).Where(role => !string.IsNullOrEmpty(role)).ToArray();
// For each Node in new database make sure all roles exist
DataTable dataTable = database.RetrieveData("SELECT ID FROM Node");
foreach (DataRow row in dataTable.Rows)
{
Guid nodeID = row.ConvertField <Guid>("ID");
foreach (string role in roles)
{
if ((database.ExecuteScalar <int?>(RoleCountFormat, database.Guid(nodeID), role) ?? 0) == 0)
{
AddRolesForNode(database, nodeID, role);
}
}
}
}
/// <summary>
/// Loads <see cref="IaonTree"/> 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="IaonTree"/>.</returns>
public static ObservableCollection <IaonTree> Load(AdoDataConnection database)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
ObservableCollection <IaonTree> iaonTreeList;
DataTable rootNodesTable = new DataTable();
rootNodesTable.Columns.Add(new DataColumn("AdapterType", Type.GetType("System.String")));
DataRow row;
row = rootNodesTable.NewRow();
row["AdapterType"] = "Input Adapters";
rootNodesTable.Rows.Add(row);
row = rootNodesTable.NewRow();
row["AdapterType"] = "Action Adapters";
rootNodesTable.Rows.Add(row);
row = rootNodesTable.NewRow();
row["AdapterType"] = "Output Adapters";
rootNodesTable.Rows.Add(row);
DataSet resultSet = new DataSet();
resultSet.Tables.Add(rootNodesTable);
DataTable iaonTreeTable = database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT * FROM IaonTreeView WHERE NodeID = {0}", "nodeID"), database.CurrentNodeID());
resultSet.EnforceConstraints = false;
resultSet.Tables.Add(iaonTreeTable.Copy());
resultSet.Tables[0].TableName = "RootNodesTable";
resultSet.Tables[1].TableName = "AdapterData";
iaonTreeList = new ObservableCollection <IaonTree>(from item in resultSet.Tables["RootNodesTable"].AsEnumerable()
select new IaonTree
{
m_adapterType = item.Field <string>("AdapterType"),
m_adapterList = new ObservableCollection <Adapter>(from obj in resultSet.Tables["AdapterData"].AsEnumerable()
where obj.Field <string>("AdapterType") == item.Field <string>("AdapterType")
select new Adapter
{
NodeID = database.Guid(obj, "NodeID"),
ID = obj.ConvertField <int>("ID"),
AdapterName = obj.Field <string>("AdapterName"),
AssemblyName = obj.Field <string>("AssemblyName"),
TypeName = obj.Field <string>("TypeName"),
ConnectionString = obj.Field <string>("ConnectionString")
})
});
return(iaonTreeList);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Retrieves a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Measurement"/> assigned 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 filter data.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> containing SignalID and PointTag of <see cref="Measurement"/>s assigned to <see cref="MeasurementGroup"/>.</returns>
public static Dictionary <Guid, string> GetCurrentMeasurements(AdoDataConnection database, int measurementGroupId)
{
Dictionary <Guid, string> currentMeasurements;
DataTable currentMeasurementTable;
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
currentMeasurements = new Dictionary <Guid, string>();
query = database.ParameterizedQueryString("SELECT * FROM MeasurementGroupMeasDetail WHERE MeasurementGroupID = {0} ORDER BY PointID", "measurementGroupID");
currentMeasurementTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, measurementGroupId);
foreach (DataRow row in currentMeasurementTable.Rows)
{
currentMeasurements[database.Guid(row, "SignalID")] = row.Field <string>("PointTag");
}
return(currentMeasurements);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Removed measurement groups from <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 measurement groups to be removed.</param>
/// <param name="measurementGroupsToBeRemoved">List of <see cref="MeasurementGroup"/> IDs to be removed.</param>
/// <returns>string, indicating success for UI display.</returns>
public static string RemoveMeasurementGroups(AdoDataConnection database, Guid subscriberID, List <int> measurementGroupsToBeRemoved)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
foreach (int id in measurementGroupsToBeRemoved)
{
query = database.ParameterizedQueryString("DELETE FROM SubscriberMeasurementGroup WHERE SubscriberID = {0} AND MeasurementGroupID = {1}", "subscriberID", "measurementGroupID");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(subscriberID), id);
}
return("Measurement groups removed from allowed measurement groups list for subscriber successfully");
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Removes measurements from <see cref="Subscriber"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="subscriberID">ID of the <see cref="Subscriber"/> from which measurements to be removed.</param>
/// <param name="measurementsToBeRemoved">List of <see cref="Measurement"/> IDs to be removed.</param>
/// <returns>string, indicating success for UI display.</returns>
public static string RemoveMeasurements(AdoDataConnection database, Guid subscriberID, List <Guid> measurementsToBeRemoved)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
foreach (Guid id in measurementsToBeRemoved)
{
query = database.ParameterizedQueryString("DELETE FROM SubscriberMeasurement WHERE SubscriberID = {0} AND SignalID = {1}", "subscriberID", "signalID");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(subscriberID), database.Guid(id));
}
return("Selected measurements removed successfully");
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Adds <see cref="UserAccount"/> to <see cref="ApplicationRole"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="roleID">ID of <see cref="ApplicationRole"/> to which <see cref="UserAccount"/>s are being added.</param>
/// <param name="usersToBeAdded">List of <see cref="UserAccount"/> IDs to be added.</param>
/// <returns>string, for display use, indicating success.</returns>
public static string AddUsers(AdoDataConnection database, Guid roleID, List <Guid> usersToBeAdded)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
foreach (Guid id in usersToBeAdded)
{
string userName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM UserAccount WHERE ID = {0}", "userAccountID"), DefaultTimeout, database.Guid(id)).ToNonNullString();
string roleName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM ApplicationRole WHERE ID = {0}", "applicationRoleID"), DefaultTimeout, database.Guid(roleID)).ToNonNullString();
string query = database.ParameterizedQueryString("INSERT INTO ApplicationRoleUserAccount (ApplicationRoleID, UserAccountID) VALUES ({0}, {1})", "roleID", "userID");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(roleID), database.Guid(id));
CommonFunctions.LogEvent(string.Format("User \"{0}\" added to role \"{1}\" by user \"{2}\".", UserInfo.SIDToAccountName(userName), roleName, CommonFunctions.CurrentUser), 4);
}
return("User accounts added to role successfully");
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="UserAccount"/> 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 user accounts defined in the database.</returns>
public static Dictionary <Guid, string> GetLookupList(AdoDataConnection database, bool isOptional = false)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary <Guid, string> userAccountList = new Dictionary <Guid, string>();
if (isOptional)
{
userAccountList.Add(Guid.Empty, "Select UserAccount");
}
DataTable userAccountTable = database.Connection.RetrieveData(database.AdapterType, "SELECT ID, Name FROM UserAccount ORDER BY Name");
foreach (DataRow row in userAccountTable.Rows)
{
userAccountList[database.Guid(row, "ID")] = UserInfo.SIDToAccountName(row.Field <string>("Name"));
}
return(userAccountList);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Loads <see cref="OutputStreamDevice"/> 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 datbase</param>
/// <returns>Collection of <see cref="OutputStreamDevice"/>.</returns>
public static ObservableCollection <OutputStreamDevice> Load(AdoDataConnection database, IList <int> keys)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
string query;
string commaSeparatedKeys;
OutputStreamDevice[] outputStreamDeviceList = null;
DataTable outputStreamDeviceTable;
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, AdapterID, ID, IDCode, Acronym, BpaAcronym, Name, PhasorDataFormat, " +
"FrequencyDataFormat, AnalogDataFormat, CoordinateFormat, LoadOrder, Enabled, Virtual " +
"FROM OutputStreamDeviceDetail WHERE ID IN ({0})", commaSeparatedKeys);
outputStreamDeviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout);
outputStreamDeviceList = new OutputStreamDevice[outputStreamDeviceTable.Rows.Count];
foreach (DataRow row in outputStreamDeviceTable.Rows)
{
id = row.ConvertField <int>("ID");
outputStreamDeviceList[keys.IndexOf(id)] = new OutputStreamDevice()
{
NodeID = database.Guid(row, "NodeID"),
AdapterID = row.ConvertField <int>("AdapterID"),
ID = id,
IDCode = row.ConvertField <int>("IDCode"),
Acronym = row.Field <string>("Acronym"),
BpaAcronym = row.Field <string>("BpaAcronym"),
Name = row.Field <string>("Name"),
PhasorDataFormat = row.Field <string>("PhasorDataFormat"),
FrequencyDataFormat = row.Field <string>("FrequencyDataFormat"),
AnalogDataFormat = row.Field <string>("AnalogDataFormat"),
CoordinateFormat = row.Field <string>("CoordinateFormat"),
LoadOrder = row.ConvertField <int>("LoadOrder"),
Enabled = Convert.ToBoolean(row.Field <object>("Enabled")),
m_virtual = Convert.ToBoolean(row.Field <object>("Virtual"))
};
}
}
return(new ObservableCollection <OutputStreamDevice>(outputStreamDeviceList ?? new OutputStreamDevice[0]));
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Loads <see cref="Historian"/> 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 to load, if any.</param>
/// <returns>Collection of <see cref="Historian"/>.</returns>
public static ObservableCollection <Historian> Load(AdoDataConnection database, IList <int> keys)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
string query;
string commaSeparatedKeys;
Historian[] historianList = null;
DataTable historianTable;
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, Acronym, Name, AssemblyName, TypeName, " +
"ConnectionString, IsLocal, Description, LoadOrder, Enabled, MeasurementReportingInterval, NodeName" +
" FROM HistorianDetail WHERE NodeID = {{0}} AND ID IN ({0})", commaSeparatedKeys), "nodeID");
historianTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID());
historianList = new Historian[historianTable.Rows.Count];
foreach (DataRow row in historianTable.Rows)
{
id = row.ConvertField <int>("ID");
historianList[keys.IndexOf(id)] = new Historian()
{
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"),
IsLocal = Convert.ToBoolean(row.Field <object>("IsLocal")),
Description = row.Field <string>("Description"),
LoadOrder = row.ConvertField <int>("LoadOrder"),
Enabled = Convert.ToBoolean(row.Field <object>("Enabled")),
MeasurementReportingInterval = row.ConvertField <int>("MeasurementReportingInterval"),
m_nodeName = row.Field <string>("NodeName")
};
}
}
return(new ObservableCollection <Historian>(historianList ?? new Historian[0]));
}
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>
/// <param name="keys">Keys of the nodes to be loaded from the database.</param>
/// <returns>Collection of <see cref="Node"/>.</returns>
public static ObservableCollection <Node> Load(AdoDataConnection database, IList <Guid> keys)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
string query;
string commaSeparatedKeys;
Node[] nodeList = null;
DataTable nodeTable;
Guid id;
if ((object)keys != null && keys.Count > 0)
{
commaSeparatedKeys = keys.Select(key => "'" + key.ToString() + "'").Aggregate((str1, str2) => str1 + "," + str2);
query = string.Format("Select ID, Name, CompanyID, Longitude, Latitude, Description, ImagePath, Settings, MenuData, " +
"MenuType, Master, LoadOrder, Enabled, CompanyName From NodeDetail WHERE ID IN ({0})", commaSeparatedKeys);
nodeTable = database.Connection.RetrieveData(database.AdapterType, query);
nodeList = new Node[nodeTable.Rows.Count];
foreach (DataRow row in nodeTable.Rows)
{
id = database.Guid(row, "ID");
nodeList[keys.IndexOf(id)] = new Node()
{
ID = 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(new ObservableCollection <Node>(nodeList ?? new Node[0]));
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Retrieves an <see cref="Alarm"/> information from the database based on query string filter.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="whereClause">Filter clause to append to the SELECT query.</param>
/// <returns><see cref="Alarm"/> information.</returns>
public static Alarm GetAlarm(AdoDataConnection database, string whereClause)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
DataTable alarmTable = database.Connection.RetrieveData(database.AdapterType, "SELECT * FROM Alarm " + whereClause);
if (alarmTable.Rows.Count == 0)
{
return(null);
}
DataRow row = alarmTable.Rows[0];
object associatedMeasurementId = row.Field <object>("AssociatedMeasurementID");
Alarm alarm = new Alarm
{
NodeID = database.Guid(row, "NodeID"),
ID = row.ConvertField <int>("ID"),
TagName = row.Field <string>("TagName"),
SignalID = database.Guid(row, "SignalID"),
AssociatedMeasurementID = ((object)associatedMeasurementId != null) ? Guid.Parse(associatedMeasurementId.ToString()) : (Guid?)null,
Description = row.Field <string>("Description"),
Severity = row.ConvertField <int>("Severity"),
Operation = row.ConvertField <int>("Operation"),
SetPoint = row.ConvertNullableField <double>("SetPoint"),
Tolerance = row.ConvertNullableField <double>("Tolerance"),
Delay = row.ConvertNullableField <double>("Delay"),
Hysteresis = row.ConvertNullableField <double>("Hysteresis"),
LoadOrder = row.ConvertField <int>("LoadOrder"),
Enabled = row.ConvertField <bool>("Enabled")
};
return(alarm);
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Adds role for newly added node, e.g., Administrator, Editor, Viewer.
/// </summary>
/// <param name="database">Data connection to use for database operations.</param>
/// <param name="nodeID">Node ID to which roles are being assigned.</param>
/// <param name="roleName">Name of role to be added.</param>
private static void AddRolesForNode(AdoDataConnection database, Guid nodeID, string roleName)
{
// Queries
const string InsertRoleFormat = "INSERT INTO ApplicationRole(Name, Description, NodeID, UpdatedBy, CreatedBy) VALUES('{0}', '{0} Role', {{0}}, {{1}}, {{2}})";
string currentUserSID = UserInfo.UserNameToSID(UserInfo.CurrentUserID);
database.ExecuteNonQuery(string.Format(InsertRoleFormat, roleName), database.Guid(nodeID), currentUserSID, currentUserSID);
}
/// <summary>
/// Loads <see cref="OutputStreamDevicePhasor"/> 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 measuremnets to be loaded from the database</param>
/// <returns>Collection of <see cref="OutputStreamDevicePhasor"/>.</returns>
public static ObservableCollection <OutputStreamDevicePhasor> Load(AdoDataConnection database, IList <int> keys)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
string query;
string commaSeparatedKeys;
OutputStreamDevicePhasor[] outputStreamDevicePhasorList = null;
DataTable outputStreamDevicePhasorTable;
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, OutputStreamDeviceID, ID, Label, Type, Phase, ScalingValue, LoadOrder " +
"FROM OutputStreamDevicePhasor WHERE ID IN ({0})", commaSeparatedKeys));
outputStreamDevicePhasorTable = database.Connection.RetrieveData(database.AdapterType, query);
outputStreamDevicePhasorList = new OutputStreamDevicePhasor[outputStreamDevicePhasorTable.Rows.Count];
foreach (DataRow row in outputStreamDevicePhasorTable.Rows)
{
id = row.ConvertField <int>("ID");
outputStreamDevicePhasorList[keys.IndexOf(id)] = new OutputStreamDevicePhasor()
{
NodeID = database.Guid(row, "NodeID"),
OutputStreamDeviceID = row.ConvertField <int>("OutputStreamDeviceID"),
ID = id,
Label = row.Field <string>("Label"),
Type = row.Field <string>("Type"),
Phase = row.Field <string>("Phase"),
ScalingValue = row.ConvertField <int>("ScalingValue"),
LoadOrder = row.ConvertField <int>("LoadOrder"),
m_phaseType = row.Field <string>("Phase") == "+" ? "Positive Sequence" : row.Field <string>("Phase") == "-" ? "Negative Sequence" :
row.Field <string>("Phase") == "0" ? "Zero Sequence" : row.Field <string>("Phase") == "A" ? "Phase A" :
row.Field <string>("Phase") == "B" ? "Phase B" : "Phase C",
m_phasorType = row.Field <string>("Type") == "V" ? "Voltage" : "Current"
};
}
}
return(new ObservableCollection <OutputStreamDevicePhasor>(outputStreamDevicePhasorList ?? new OutputStreamDevicePhasor[0]));
}
finally
{
if (createdConnection && database != null)
{
database.Dispose();
}
}
}
/// <summary>
/// Loads <see cref="OutputStreamMeasurement"/> 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="OutputStreamMeasurement"/>.</returns>
public static ObservableCollection <OutputStreamMeasurement> Load(AdoDataConnection database, IList <int> keys)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
string query;
string commaSeparatedKeys;
OutputStreamMeasurement[] outputStreamMeasurementList = null;
DataTable outputStreamMeasurementTable;
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, AdapterID, ID, HistorianID, PointID, SignalReference, SourcePointTag, HistorianAcronym " +
"FROM OutputStreamMeasurementDetail WHERE ID IN ({0})", commaSeparatedKeys);
outputStreamMeasurementTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout);
outputStreamMeasurementList = new OutputStreamMeasurement[outputStreamMeasurementTable.Rows.Count];
foreach (DataRow row in outputStreamMeasurementTable.Rows)
{
id = row.ConvertField <int>("ID");
outputStreamMeasurementList[keys.IndexOf(id)] = new OutputStreamMeasurement()
{
NodeID = database.Guid(row, "NodeID"),
AdapterID = row.ConvertField <int>("AdapterID"),
ID = id,
HistorianID = row.ConvertNullableField <int>("HistorianID"),
PointID = row.ConvertField <int>("PointID"),
SignalReference = row.Field <string>("SignalReference"),
m_sourcePointTag = row.Field <string>("SourcePointTag"),
m_historianAcronym = row.Field <string>("HistorianAcronym")
};
}
}
return(new ObservableCollection <OutputStreamMeasurement>(outputStreamMeasurementList ?? new OutputStreamMeasurement[0]));
}
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>
/// Loads <see cref="OutputStreamMeasurement"/> 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="OutputStreamMeasurement"/>.</returns>
public static ObservableCollection<OutputStreamMeasurement> Load(AdoDataConnection database, IList<int> keys)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
string query;
string commaSeparatedKeys;
OutputStreamMeasurement[] outputStreamMeasurementList = null;
DataTable outputStreamMeasurementTable;
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, AdapterID, ID, HistorianID, PointID, SignalReference, SourcePointTag, HistorianAcronym " +
"FROM OutputStreamMeasurementDetail WHERE ID IN ({0})", commaSeparatedKeys);
outputStreamMeasurementTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout);
outputStreamMeasurementList = new OutputStreamMeasurement[outputStreamMeasurementTable.Rows.Count];
foreach (DataRow row in outputStreamMeasurementTable.Rows)
{
id = row.ConvertField<int>("ID");
outputStreamMeasurementList[keys.IndexOf(id)] = new OutputStreamMeasurement()
{
NodeID = database.Guid(row, "NodeID"),
AdapterID = row.ConvertField<int>("AdapterID"),
ID = id,
HistorianID = row.ConvertNullableField<int>("HistorianID"),
PointID = row.ConvertField<int>("PointID"),
SignalReference = row.Field<string>("SignalReference"),
m_sourcePointTag = row.Field<string>("SourcePointTag"),
m_historianAcronym = row.Field<string>("HistorianAcronym")
};
}
}
return new ObservableCollection<OutputStreamMeasurement>(outputStreamMeasurementList ?? new OutputStreamMeasurement[0]);
}
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 output stream.
/// </summary>
/// <param name="database">Source database connection.</param>
/// <param name="whereClause">Where filter clause.</param>
/// <returns>Output stream.</returns>
public static OutputStream GetOutputStream(AdoDataConnection database, string whereClause)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
DataTable outputStreamTable = database.Connection.RetrieveData(database.AdapterType, "SELECT * FROM OutputStreamDetail " + whereClause);
if (outputStreamTable.Rows.Count == 0)
return null;
DataRow row = outputStreamTable.Rows[0];
int type = Convert.ToInt32(row.Field<object>("Type"));
OutputStream outputStream = new OutputStream
{
NodeID = database.Guid(row, "NodeID"),
ID = Convert.ToInt32(row.Field<object>("ID")),
Acronym = row.Field<string>("Acronym"),
Name = row.Field<string>("Name"),
Type = type,
ConnectionString = row.Field<string>("ConnectionString"),
IDCode = Convert.ToInt32(row.Field<object>("IDCode")),
CommandChannel = row.Field<string>("CommandChannel"),
DataChannel = row.Field<string>("DataChannel"),
AutoPublishConfigFrame = Convert.ToBoolean(row.Field<object>("AutoPublishConfigFrame")),
AutoStartDataChannel = Convert.ToBoolean(row.Field<object>("AutoStartDataChannel")),
NominalFrequency = Convert.ToInt32(row.Field<object>("NominalFrequency")),
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 = Convert.ToInt32(row.Field<object>("LoadOrder")),
Enabled = Convert.ToBoolean(row.Field<object>("Enabled")),
m_nodeName = row.Field<string>("NodeName"),
m_typeName = (type == 1) ? "IEEE C37.118" : (type == 2) ? "BPA" : "IEC 61850-90-5",
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"),
DataFormat = row.Field<string>("DataFormat"),
CoordinateFormat = row.Field<string>("CoordinateFormat"),
CurrentScalingValue = Convert.ToInt32(row.Field<object>("CurrentScalingValue")),
VoltageScalingValue = Convert.ToInt32(row.Field<object>("VoltageScalingValue")),
AnalogScalingValue = Convert.ToInt32(row.Field<object>("AnalogScalingValue")),
DigitalMaskValue = Convert.ToInt32(row.Field<object>("DigitalMaskValue")),
PerformTimestampReasonabilityCheck = Convert.ToBoolean(row.Field<object>("PerformTimeReasonabilityCheck"))
};
return outputStream;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Retrieves <see cref="ObservableCollection{T}"/> type list of <see cref="Device"/> information from the database based on query string filter.
/// </summary>
/// <param name="database"></param>
/// <param name="whereClause"></param>
/// <returns></returns>
public static ObservableCollection<Device> GetDevices(AdoDataConnection database, string whereClause)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
DataTable deviceTable = database.Connection.RetrieveData(database.AdapterType, "SELECT * FROM DeviceDetail " + whereClause);
if (deviceTable.Rows.Count == 0)
return null;
ObservableCollection<Device> deviceList = new ObservableCollection<Device>();
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 = Convert.ToBoolean(row.Field<object>("Enabled")),
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="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="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>
/// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="UserAccount"/> 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 user accounts defined in the database.</returns>
public static Dictionary<Guid, string> GetLookupList(AdoDataConnection database, bool isOptional = false)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary<Guid, string> userAccountList = new Dictionary<Guid, string>();
if (isOptional)
userAccountList.Add(Guid.Empty, "Select UserAccount");
DataTable userAccountTable = database.Connection.RetrieveData(database.AdapterType, "SELECT ID, Name FROM UserAccount ORDER BY Name");
foreach (DataRow row in userAccountTable.Rows)
userAccountList[database.Guid(row, "ID")] = UserInfo.SIDToAccountName(row.Field<string>("Name"));
return userAccountList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Loads <see cref="Device"/> 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="Device"/>.</returns>
public static ObservableCollection<Device> Load(AdoDataConnection database, IList<int> keys)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
string query;
string commaSeparatedKeys;
Device[] deviceList = null;
DataTable deviceTable;
int id;
if ((object)keys != null && keys.Count > 0)
{
commaSeparatedKeys = keys.Select(key => key.ToString()).Aggregate((str1, str2) => str1 + "," + str2);
query = string.Format("SELECT * FROM DeviceDetail WHERE ID IN ({0})", commaSeparatedKeys);
deviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout);
deviceList = new Device[deviceTable.Rows.Count];
foreach (DataRow row in deviceTable.Rows)
{
id = row.ConvertField<int>("ID");
deviceList[keys.IndexOf(id)] = new Device()
{
NodeID = database.Guid(row, "NodeID"),
ID = 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 = Convert.ToBoolean(row.Field<object>("Enabled")),
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 new ObservableCollection<Device>(deviceList ?? new Device[0]);
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Retrieves a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Measurement"/> assigned 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 filter data.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> containing SignalID and PointTag of <see cref="Measurement"/>s assigned to <see cref="MeasurementGroup"/>.</returns>
public static Dictionary<Guid, string> GetCurrentMeasurements(AdoDataConnection database, int measurementGroupId)
{
Dictionary<Guid, string> currentMeasurements;
DataTable currentMeasurementTable;
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
currentMeasurements = new Dictionary<Guid, string>();
query = database.ParameterizedQueryString("SELECT * FROM MeasurementGroupMeasDetail WHERE MeasurementGroupID = {0} ORDER BY PointID", "measurementGroupID");
currentMeasurementTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, measurementGroupId);
foreach (DataRow row in currentMeasurementTable.Rows)
currentMeasurements[database.Guid(row, "SignalID")] = row.Field<string>("PointTag");
return currentMeasurements;
}
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();
}
}
/// <summary>
/// Removes measurements from <see cref="Subscriber"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="subscriberID">ID of the <see cref="Subscriber"/> from which measurements to be removed.</param>
/// <param name="measurementsToBeRemoved">List of <see cref="Measurement"/> IDs to be removed.</param>
/// <returns>string, indicating success for UI display.</returns>
public static string RemoveMeasurements(AdoDataConnection database, Guid subscriberID, List<Guid> measurementsToBeRemoved)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
foreach (Guid id in measurementsToBeRemoved)
{
query = database.ParameterizedQueryString("DELETE FROM SubscriberMeasurement WHERE SubscriberID = {0} AND SignalID = {1}", "subscriberID", "signalID");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(subscriberID), database.Guid(id));
}
return "Selected measurements removed successfully";
}
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>
/// 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();
}
}
private static void PhasorDataSourceValidation(AdoDataConnection database, string nodeIDQueryString, ulong trackingVersion, string arguments, Action<string> statusMessage, Action<Exception> processException)
{
// Make sure setting exists to allow user to by-pass phasor data source validation at startup
ConfigurationFile configFile = ConfigurationFile.Current;
CategorizedSettingsElementCollection settings = configFile.Settings["systemSettings"];
settings.Add("ProcessPhasorDataSourceValidation", true, "Determines if the phasor data source validation should be processed at startup");
// See if this node should process phasor source validation
if (!settings["ProcessPhasorDataSourceValidation"].ValueAsBoolean())
return;
Dictionary<string, string> args = new Dictionary<string, string>();
bool skipOptimization = false, renameAllPointTags = false;
string arg, acronym;
if (!string.IsNullOrEmpty(arguments))
args = arguments.ParseKeyValuePairs();
if (args.TryGetValue("skipOptimization", out arg))
skipOptimization = arg.ParseBoolean();
if (args.TryGetValue("renameAllPointTags", out arg))
renameAllPointTags = arg.ParseBoolean();
CreateDefaultNode(database, nodeIDQueryString, statusMessage, processException);
LoadDefaultConfigurationEntity(database, statusMessage, processException);
LoadDefaultInterconnection(database, statusMessage, processException);
LoadDefaultProtocol(database, statusMessage, processException);
LoadDefaultSignalType(database, statusMessage, processException);
LoadDefaultStatistic(database, statusMessage, processException);
EstablishDefaultMeasurementKeyCache(database, statusMessage, processException);
statusMessage("Validating signal types...");
// Validate that the acronym for status flags is FLAG (it was STAT in prior versions)
if (database.Connection.ExecuteScalar("SELECT Acronym FROM SignalType WHERE Suffix='SF'").ToNonNullString().ToUpper() == "STAT")
database.Connection.ExecuteNonQuery("UPDATE SignalType SET Acronym='FLAG' WHERE Suffix='SF'");
// Validate that the calculation and statistic signal types are defined (they did not in initial release)
if (Convert.ToInt32(database.Connection.ExecuteScalar("SELECT COUNT(*) FROM SignalType WHERE Acronym='CALC'")) == 0)
database.Connection.ExecuteNonQuery("INSERT INTO SignalType(Name, Acronym, Suffix, Abbreviation, LongAcronym, Source, EngineeringUnits) VALUES('Calculated Value', 'CALC', 'CV', 'CV', 'Calculated', 'PMU', '')");
if (Convert.ToInt32(database.Connection.ExecuteScalar("SELECT COUNT(*) FROM SignalType WHERE Acronym='STAT'")) == 0)
database.Connection.ExecuteNonQuery("INSERT INTO SignalType(Name, Acronym, Suffix, Abbreviation, LongAcronym, Source, EngineeringUnits) VALUES('Statistic', 'STAT', 'ST', 'ST', 'Statistic', 'Any', '')");
if (Convert.ToInt32(database.Connection.ExecuteScalar("SELECT COUNT(*) FROM SignalType WHERE Acronym='QUAL'")) == 0)
database.Connection.ExecuteNonQuery("INSERT INTO SignalType(Name, Acronym, Suffix, Abbreviation, LongAcronym, Source, EngineeringUnits) VALUES('Quality Flags', 'QUAL', 'QF', 'QF', 'QualityFlags', 'Frame', '')");
// Make sure values are defined for long acronyms (did not exist in prior versions)
if (Convert.ToInt32(database.Connection.ExecuteScalar("SELECT COUNT(*) FROM SignalType WHERE LongAcronym='Undefined'")) > 0)
{
// Update abbreviations to better values for consistent custom point tag naming convention
if (database.Connection.ExecuteScalar("SELECT Abbreviation FROM SignalType WHERE Acronym='ALOG'").ToNonNullString().ToUpper() == "A")
database.Connection.ExecuteNonQuery("UPDATE SignalType SET Abbreviation='AV' WHERE Acronym='ALOG'");
if (database.Connection.ExecuteScalar("SELECT Abbreviation FROM SignalType WHERE Acronym='DIGI'").ToNonNullString().ToUpper() == "D")
database.Connection.ExecuteNonQuery("UPDATE SignalType SET Abbreviation='DV' WHERE Acronym='DIGI'");
if (database.Connection.ExecuteScalar("SELECT Abbreviation FROM SignalType WHERE Acronym='CALC'").ToNonNullString().ToUpper() == "C")
database.Connection.ExecuteNonQuery("UPDATE SignalType SET Abbreviation='CV' WHERE Acronym='CALC'");
if (database.Connection.ExecuteScalar("SELECT Abbreviation FROM SignalType WHERE Acronym='STAT'").ToNonNullString().ToUpper() == "P")
database.Connection.ExecuteNonQuery("UPDATE SignalType SET Abbreviation='ST' WHERE Acronym='STAT'");
if (database.Connection.ExecuteScalar("SELECT Abbreviation FROM SignalType WHERE Acronym='QUAL'").ToNonNullString().ToUpper() == "Q")
database.Connection.ExecuteNonQuery("UPDATE SignalType SET Abbreviation='QF' WHERE Acronym='QUAL'");
IEnumerable<DataRow> signalTypes = database.Connection.RetrieveData(database.AdapterType, "SELECT Name, Acronym FROM SignalType WHERE LongAcronym='Undefined'").AsEnumerable();
string longAcronym;
foreach (DataRow row in signalTypes)
{
acronym = row.Field<string>("Acronym").ToUpperInvariant().Trim();
switch (acronym)
{
case "IPHM":
longAcronym = "CurrentMagnitude";
break;
case "IPHA":
longAcronym = "CurrentAngle";
break;
case "VPHM":
longAcronym = "VoltageMagnitude";
break;
case "VPHA":
longAcronym = "VoltageAngle";
break;
case "FREQ":
longAcronym = "Frequency";
break;
case "DFDT":
longAcronym = "DfDt";
break;
case "ALOG":
longAcronym = "Analog";
break;
case "FLAG":
longAcronym = "StatusFlags";
break;
case "DIGI":
longAcronym = "Digital";
break;
case "CALC":
longAcronym = "Calculated";
break;
case "STAT":
longAcronym = "Statistic";
break;
case "ALRM":
longAcronym = "Alarm";
break;
case "QUAL":
longAcronym = "QualityFlags";
break;
default:
longAcronym = row.Field<string>("Name").Trim().RemoveWhiteSpace();
if (string.IsNullOrEmpty(longAcronym))
longAcronym = acronym.ToNonNullString("?");
break;
}
database.Connection.ExecuteNonQuery(string.Format("UPDATE SignalType SET LongAcronym='{0}' WHERE Acronym='{1}'", longAcronym, acronym));
}
}
statusMessage("Validating output stream device ID codes...");
// Validate all ID codes for output stream devices are not set their default value
database.Connection.ExecuteNonQuery("UPDATE OutputStreamDevice SET IDCode = ID WHERE IDCode = 0");
statusMessage("Verifying statistics archive exists...");
// Validate that the statistics historian exists
if (Convert.ToInt32(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM Historian WHERE Acronym='STAT' AND NodeID={0}", nodeIDQueryString))) == 0)
database.Connection.ExecuteNonQuery(string.Format("INSERT INTO Historian(NodeID, Acronym, Name, AssemblyName, TypeName, ConnectionString, IsLocal, Description, LoadOrder, Enabled) VALUES({0}, 'STAT', 'Statistics Archive', 'HistorianAdapters.dll', 'HistorianAdapters.LocalOutputAdapter', '', 1, 'Local historian used to archive system statistics', 9999, 1)", nodeIDQueryString));
// Make sure statistics path exists to hold historian files
string statisticsPath = FilePath.GetAbsolutePath(FilePath.AddPathSuffix("Statistics"));
if (!Directory.Exists(statisticsPath))
Directory.CreateDirectory(statisticsPath);
// Make sure needed statistic historian configuration settings are properly defined
settings = configFile.Settings["statMetadataFile"];
settings.Add("FileName", string.Format("Statistics{0}stat_dbase.dat", Path.DirectorySeparatorChar), "Name of the statistics meta-data file including its path.");
settings.Add("LoadOnOpen", true, "True if file records are to be loaded in memory when opened; otherwise False - this defaults to True for the statistics meta-data file.");
settings.Add("ReloadOnModify", false, "True if file records loaded in memory are to be re-loaded when file is modified on disk; otherwise False - this defaults to False for the statistics meta-data file.");
settings["LoadOnOpen"].Update(true);
settings["ReloadOnModify"].Update(false);
settings = configFile.Settings["statStateFile"];
settings.Add("FileName", string.Format("Statistics{0}stat_startup.dat", Path.DirectorySeparatorChar), "Name of the statistics state file including its path.");
settings.Add("AutoSaveInterval", 10000, "Interval in milliseconds at which the file records loaded in memory are to be saved automatically to disk. Use -1 to disable automatic saving - this defaults to 10,000 for the statistics state file.");
settings.Add("LoadOnOpen", true, "True if file records are to be loaded in memory when opened; otherwise False - this defaults to True for the statistics state file.");
settings.Add("SaveOnClose", true, "True if file records loaded in memory are to be saved to disk when file is closed; otherwise False - this defaults to True for the statistics state file.");
settings.Add("ReloadOnModify", false, "True if file records loaded in memory are to be re-loaded when file is modified on disk; otherwise False - this defaults to False for the statistics state file.");
settings["AutoSaveInterval"].Update(10000);
settings["LoadOnOpen"].Update(true);
settings["SaveOnClose"].Update(true);
settings["ReloadOnModify"].Update(false);
settings = configFile.Settings["statIntercomFile"];
settings.Add("FileName", string.Format("Statistics{0}scratch.dat", Path.DirectorySeparatorChar), "Name of the statistics intercom file including its path.");
settings.Add("AutoSaveInterval", 10000, "Interval in milliseconds at which the file records loaded in memory are to be saved automatically to disk. Use -1 to disable automatic saving - this defaults to 10,000 for the statistics intercom file.");
settings.Add("LoadOnOpen", true, "True if file records are to be loaded in memory when opened; otherwise False - this defaults to True for the statistics intercom file.");
settings.Add("SaveOnClose", true, "True if file records loaded in memory are to be saved to disk when file is closed; otherwise False - this defaults to True for the statistics intercom file.");
settings.Add("ReloadOnModify", false, "True if file records loaded in memory are to be re-loaded when file is modified on disk; otherwise False - this defaults to False for the statistics intercom file.");
settings["AutoSaveInterval"].Update(1000);
settings["LoadOnOpen"].Update(true);
settings["SaveOnClose"].Update(true);
settings["ReloadOnModify"].Update(false);
settings = configFile.Settings["statArchiveFile"];
settings.Add("FileName", string.Format("Statistics{0}stat_archive.d", Path.DirectorySeparatorChar), "Name of the statistics working archive file including its path.");
settings.Add("CacheWrites", true, "True if writes are to be cached for performance; otherwise False - this defaults to True for the statistics working archive file.");
settings.Add("ConserveMemory", false, "True if attempts are to be made to conserve memory; otherwise False - this defaults to False for the statistics working archive file.");
settings["CacheWrites"].Update(true);
settings["ConserveMemory"].Update(false);
settings = configFile.Settings["statMetadataService"];
settings.Add("Endpoints", "http.rest://localhost:6051/historian", "Semicolon delimited list of URIs where the web service can be accessed - this defaults to http.rest://localhost:6051/historian for the statistics meta-data service.");
settings = configFile.Settings["statTimeSeriesDataService"];
settings.Add("Endpoints", "http.rest://localhost:6052/historian", "Semicolon delimited list of URIs where the web service can be accessed - this defaults to http.rest://localhost:6052/historian for the statistics time-series data service.");
configFile.Save();
// Get the needed statistic related IDs
int statHistorianID = Convert.ToInt32(database.Connection.ExecuteScalar(string.Format("SELECT ID FROM Historian WHERE Acronym='STAT' AND NodeID={0}", nodeIDQueryString)));
// Load the defined system statistics
IEnumerable<DataRow> statistics = database.Connection.RetrieveData(database.AdapterType, "SELECT * FROM Statistic ORDER BY Source, SignalIndex").AsEnumerable();
// Filter statistics to device, input stream and output stream types
IEnumerable<DataRow> deviceStatistics = statistics.Where(row => string.Compare(row.Field<string>("Source"), "Device", true) == 0).ToList();
IEnumerable<DataRow> inputStreamStatistics = statistics.Where(row => string.Compare(row.Field<string>("Source"), "InputStream", true) == 0).ToList();
// Define kinds of output signal that will designate a location in an output stream protocol frame - other non-mappable measurements will be removed from output stream measurements
SignalKind[] validOutputSignalKinds = { SignalKind.Angle, SignalKind.Magnitude, SignalKind.Frequency, SignalKind.DfDt, SignalKind.Status, SignalKind.Analog, SignalKind.Digital, SignalKind.Quality };
HashSet<int> measurementIDsToDelete = new HashSet<int>();
SignalReference deviceSignalReference;
string query, signalReference, pointTag, company, description, protocolIDs;
int adapterID, deviceID, signalIndex;
bool firstStatisticExisted;
int? historianID;
string[] trackedTables;
ulong changes;
try
{
// Determine the tables for which changes are tracked
if (trackingVersion != ulong.MinValue)
{
trackedTables = database.Connection.RetrieveData(database.AdapterType, "SELECT Name FROM TrackedTable").Select()
.Select(row => row["Name"].ToNonNullString())
.ToArray();
}
else
{
trackedTables = new string[0];
}
}
catch
{
trackedTables = new string[0];
}
statusMessage("Validating device protocols...");
// Extract IDs for phasor protocols
StringBuilder protocolIDList = new StringBuilder();
DataTable protocols = database.Connection.RetrieveData(database.AdapterType, "SELECT * FROM Protocol");
if (protocols.Columns.Contains("Category"))
{
// Make sure new protocol types exist
if (Convert.ToInt32(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM Protocol WHERE Acronym='GatewayTransport'"))) == 0)
{
database.Connection.ExecuteNonQuery("INSERT INTO Protocol(Acronym, Name, Type, Category, AssemblyName, TypeName, LoadOrder) VALUES('GatewayTransport', 'Gateway Transport', 'Measurement', 'Gateway', 'GSF.TimeSeries.dll', 'GSF.TimeSeries.Transport.DataSubscriber', " + (protocols.Rows.Count + 1) + ")");
if (Convert.ToInt32(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM Protocol WHERE Acronym='WAV'"))) == 0)
database.Connection.ExecuteNonQuery("INSERT INTO Protocol(Acronym, Name, Type, Category, AssemblyName, TypeName, LoadOrder) VALUES('WAV', 'Wave Form Input Adapter', 'Frame', 'Audio', 'WavInputAdapter.dll', 'WavInputAdapter.WavInputAdapter', " + (protocols.Rows.Count + 2) + ")");
if (Convert.ToInt32(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM Protocol WHERE Acronym='IeeeC37_118V2'"))) == 0)
database.Connection.ExecuteNonQuery("INSERT INTO Protocol(Acronym, Name, Type, Category, AssemblyName, TypeName, LoadOrder) VALUES('IeeeC37_118V2', 'IEEE C37.118.2-2011', 'Frame', 'Phasor', 'PhasorProtocolAdapters.dll', 'PhasorProtocolAdapters.PhasorMeasurementMapper', 2)");
if (Convert.ToInt32(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM Protocol WHERE Acronym='VirtualInput'"))) == 0)
database.Connection.ExecuteNonQuery("INSERT INTO Protocol(Acronym, Name, Type, Category, AssemblyName, TypeName, LoadOrder) VALUES('VirtualInput', 'Virtual Device', 'Frame', 'Virtual', 'TestingAdapters.dll', 'TestingAdapters.VirtualInputAdapter', " + (protocols.Rows.Count + 4) + ")");
}
if (Convert.ToInt32(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM Protocol WHERE Acronym='Iec61850_90_5'"))) == 0)
database.Connection.ExecuteNonQuery("INSERT INTO Protocol(Acronym, Name, Type, Category, AssemblyName, TypeName, LoadOrder) VALUES('Iec61850_90_5', 'IEC 61850-90-5', 'Frame', 'Phasor', 'PhasorProtocolAdapters.dll', 'PhasorProtocolAdapters.PhasorMeasurementMapper', 12)");
foreach (DataRow protocol in protocols.Rows)
{
if (string.Compare(protocol.Field<string>("Category"), "Phasor", true) == 0)
{
if (protocolIDList.Length > 0)
protocolIDList.Append(", ");
protocolIDList.Append(protocol.ConvertField<int>("ID"));
}
}
}
else
{
// Older schemas do not include protocol categories and assembly info
foreach (DataRow protocol in protocols.Rows)
{
if (protocolIDList.Length > 0)
protocolIDList.Append(", ");
protocolIDList.Append(protocol.ConvertField<int>("ID"));
}
}
protocolIDs = protocolIDList.ToString();
try
{
// Determine how many changes were made to devices and measurements -
// if no changes were made, we can skip the next few steps
if (trackedTables.Contains("Device") && trackedTables.Contains("Measurement"))
changes = Convert.ToUInt64(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM TrackedChange WHERE (TableName = 'Device' OR TableName = 'Measurement') AND ID > {0}", trackingVersion)));
else
changes = ulong.MaxValue;
}
catch
{
changes = ulong.MaxValue;
}
if (skipOptimization || changes != 0L)
{
statusMessage("Validating device measurements...");
// Get protocol ID list for those protocols that support time quality flags
DataTable timeQualityProtocols = database.Connection.RetrieveData(database.AdapterType, "SELECT ID FROM Protocol WHERE Acronym = 'IeeeC37_118V1' OR Acronym = 'IeeeC37_118V2' OR Acronym = 'IeeeC37_118D6' OR Acronym = 'Iec61850_90_5'");
StringBuilder timeQualityProtocolIDList = new StringBuilder();
string timeQualityProtocolIDs;
foreach (DataRow timeQualityProtocol in timeQualityProtocols.Rows)
{
if (timeQualityProtocolIDList.Length > 0)
timeQualityProtocolIDList.Append(", ");
timeQualityProtocolIDList.Append(timeQualityProtocol.ConvertField<int>("ID"));
}
timeQualityProtocolIDs = timeQualityProtocolIDList.ToString();
int qualityFlagsSignalTypeID = Convert.ToInt32(database.Connection.ExecuteScalar("SELECT ID FROM SignalType WHERE Acronym='QUAL'"));
// Make sure one device quality flags measurement exists for each "connection" for devices that support time quality flags
foreach (DataRow device in database.Connection.RetrieveData(database.AdapterType, string.Format("SELECT * FROM Device WHERE ((IsConcentrator = 0 AND ParentID IS NULL) OR IsConcentrator = 1) AND NodeID = {0} AND ProtocolID IN ({1})", nodeIDQueryString, timeQualityProtocolIDs)).Rows)
{
deviceID = device.ConvertField<int>("ID");
acronym = device.Field<string>("Acronym");
signalReference = SignalReference.ToString(acronym, SignalKind.Quality);
// See if quality flags measurement exists for device
if (Convert.ToInt32(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM Measurement WHERE SignalReference = '{0}' AND DeviceID = {1}", signalReference, deviceID))) == 0)
{
historianID = device.ConvertNullableField<int>("HistorianID");
company = (string)database.Connection.ExecuteScalar(string.Format("SELECT MapAcronym FROM Company WHERE ID = {0}", device.ConvertNullableField<int>("CompanyID") ?? 0));
if (string.IsNullOrEmpty(company))
company = configFile.Settings["systemSettings"]["CompanyAcronym"].Value.TruncateRight(3);
pointTag = CreatePointTag(company, acronym, null, "QUAL");
description = string.Format("{0} Time Quality Flags", device.Field<string>("Name"));
query = database.ParameterizedQueryString("INSERT INTO Measurement(HistorianID, DeviceID, PointTag, SignalTypeID, PhasorSourceIndex, " +
"SignalReference, Description, Enabled) VALUES({0}, {1}, {2}, {3}, NULL, {4}, {5}, 1)", "historianID", "deviceID", "pointTag",
"signalTypeID", "signalReference", "description");
database.Connection.ExecuteNonQuery(query, DataExtensions.DefaultTimeoutDuration, historianID.HasValue ? (object)historianID.Value : (object)DBNull.Value, deviceID, pointTag, qualityFlagsSignalTypeID, signalReference, description);
}
}
// Make sure needed device statistic measurements exist, currently statistics are only associated with phasor devices so we filter based on protocol
foreach (DataRow device in database.Connection.RetrieveData(database.AdapterType, string.Format("SELECT * FROM Device WHERE IsConcentrator = 0 AND NodeID = {0} AND ProtocolID IN ({1})", nodeIDQueryString, protocolIDs)).Rows)
{
foreach (DataRow statistic in deviceStatistics)
{
string oldAcronym;
string oldSignalReference;
signalIndex = statistic.ConvertField<int>("SignalIndex");
oldAcronym = device.Field<string>("Acronym");
acronym = oldAcronym + "!PMU";
oldSignalReference = SignalReference.ToString(oldAcronym, SignalKind.Statistic, signalIndex);
signalReference = SignalReference.ToString(acronym, SignalKind.Statistic, signalIndex);
// If the original format for device statistics is found in the database, update to new format
if (Convert.ToInt32(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM Measurement WHERE SignalReference='{0}' AND HistorianID={1}", oldSignalReference, statHistorianID))) > 0)
database.Connection.ExecuteNonQuery(string.Format("UPDATE Measurement SET SignalReference='{0}' WHERE SignalReference='{1}' AND HistorianID={2}", signalReference, oldSignalReference, statHistorianID));
else if (!skipOptimization)
break;
}
}
statusMessage("Validating input stream measurements...");
// Make sure devices associated with a concentrator do not have any extraneous input stream statistic measurements - this can happen
// when a device was once a direct connect device but now is part of a concentrator...
foreach (DataRow inputStream in database.Connection.RetrieveData(database.AdapterType, string.Format("SELECT * FROM Device WHERE (IsConcentrator = 0 AND ParentID IS NOT NULL) AND NodeID = {0} AND ProtocolID IN ({1})", nodeIDQueryString, protocolIDs)).Rows)
{
firstStatisticExisted = false;
foreach (DataRow statistic in inputStreamStatistics)
{
acronym = inputStream.Field<string>("Acronym") + "!IS";
signalIndex = statistic.ConvertField<int>("SignalIndex");
signalReference = SignalReference.ToString(acronym, SignalKind.Statistic, signalIndex);
// To reduce time required to execute these steps, only first statistic is verified to exist
if (!skipOptimization && !firstStatisticExisted)
{
firstStatisticExisted = (Convert.ToInt32(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM Measurement WHERE SignalReference='{0}'", signalReference))) > 0);
// If the first extraneous input statistic doesn't exist, we assume no others do as well
if (!firstStatisticExisted)
break;
}
// Remove extraneous input statistics
database.Connection.ExecuteNonQuery(string.Format("DELETE FROM Measurement WHERE SignalReference = '{0}'", signalReference));
}
}
}
try
{
// Determine how many changes were made to output streams, devices, and measurements -
// if no changes were made, we can skip the next few steps
if (trackedTables.Contains("OutputStream") && trackedTables.Contains("OutputStreamDevice") && trackedTables.Contains("OutputStreamMeasurement") && trackedTables.Contains("Measurement"))
changes = Convert.ToUInt64(database.Connection.ExecuteScalar(string.Format("SELECT COUNT(*) FROM TrackedChange WHERE (TableName = 'OutputStream' OR TableName = 'OutputStreamDevice' OR TableName = 'OutputStreamMeasurement' OR TableName = 'Measurement') AND ID > {0}", trackingVersion)));
else
changes = ulong.MaxValue;
}
catch
{
changes = ulong.MaxValue;
}
if (skipOptimization || changes != 0L)
{
statusMessage("Validating output stream measurements...");
// Make sure needed output stream statistic measurements exist
foreach (DataRow outputStream in database.Connection.RetrieveData(database.AdapterType, string.Format("SELECT * FROM OutputStream WHERE NodeID = {0}", nodeIDQueryString)).Rows)
{
adapterID = outputStream.ConvertField<int>("ID");
// Load devices acronyms associated with this output stream
List<string> deviceAcronyms =
database.Connection.RetrieveData(database.AdapterType,
string.Format("SELECT Acronym FROM OutputStreamDevice WHERE AdapterID = {0} AND NodeID = {1}", adapterID, nodeIDQueryString))
.AsEnumerable()
.Select(row => row.Field<string>("Acronym"))
.ToList();
// Since measurements can be added to the output stream device itself (e.g., quality flags) - we add it as a valid mapping destination as well
deviceAcronyms.Add(outputStream.Field<string>("Acronym"));
// Sort list so binary search can be used to speed lookups
deviceAcronyms.Sort(StringComparer.OrdinalIgnoreCase);
// Validate measurements associated with this output stream
foreach (DataRow outputStreamMeasurement in database.Connection.RetrieveData(database.AdapterType, string.Format("SELECT * FROM OutputStreamMeasurement WHERE AdapterID = {0} AND NodeID = {1}", adapterID, nodeIDQueryString)).Rows)
{
// Parse output stream measurement signal reference
deviceSignalReference = new SignalReference(outputStreamMeasurement.Field<string>("SignalReference"));
// Validate that the signal reference is associated with one of the output stream's devices
if (deviceAcronyms.BinarySearch(deviceSignalReference.Acronym, StringComparer.OrdinalIgnoreCase) < 0)
{
// This measurement has a signal reference for a device that is not part of the associated output stream, so we mark it for deletion
measurementIDsToDelete.Add(outputStreamMeasurement.ConvertField<int>("ID"));
}
// Validate that the signal reference type is valid for an output stream
if (!validOutputSignalKinds.Any(validSignalKind => deviceSignalReference.Kind == validSignalKind))
{
// This measurement has a signal reference type that is not valid for an output stream, so we mark it for deletion
measurementIDsToDelete.Add(outputStreamMeasurement.ConvertField<int>("ID"));
}
}
}
}
if (measurementIDsToDelete.Count > 0)
{
statusMessage(string.Format("Removing {0} unused output stream device measurements...", measurementIDsToDelete.Count));
foreach (int measurementID in measurementIDsToDelete)
{
database.Connection.ExecuteNonQuery(string.Format("DELETE FROM OutputStreamMeasurement WHERE ID = {0} AND NodeID = {1}", measurementID, nodeIDQueryString));
}
}
if (renameAllPointTags)
{
statusMessage("Renaming all point tags...");
string device, vendor, signalAcronym;
char? phase;
int? vendorDeviceID;
SignalReference signal;
foreach (DataRow measurement in database.Connection.RetrieveData(database.AdapterType, "SELECT SignalID, CompanyAcronym, DeviceAcronym, VendorDeviceID, SignalReference, SignalAcronym, Phase FROM MeasurementDetail WHERE SignalAcronym <> 'STAT' AND Internal <> 0 AND Subscribed = 0").Rows)
{
company = measurement.ConvertField<string>("CompanyAcronym");
if (string.IsNullOrEmpty(company))
company = configFile.Settings["systemSettings"]["CompanyAcronym"].Value.TruncateRight(3);
device = measurement.ConvertField<string>("DeviceAcronym");
if ((object)device != null)
{
vendorDeviceID = measurement.ConvertNullableField<int>("VendorDeviceID");
if (vendorDeviceID.HasValue)
vendor = (string)database.Connection.ExecuteScalar("SELECT Acronym FROM Vendor WHERE ID = " + vendorDeviceID.Value);
else
vendor = null;
signalAcronym = measurement.ConvertField<string>("SignalAcronym");
try
{
signal = new SignalReference(measurement.ConvertField<string>("SignalReference"));
signalIndex = signal.Index;
if (signalIndex <= 0)
signalIndex = -1;
}
catch
{
signalIndex = -1;
}
phase = measurement.ConvertNullableField<char>("Phase");
database.Connection.ExecuteNonQuery(string.Format("UPDATE Measurement SET PointTag = '{0}' WHERE SignalID = '{1}'", CreatePointTag(company, device, vendor, signalAcronym, signalIndex, phase ?? '_'), database.Guid(measurement, "SignalID")));
}
}
}
if (skipOptimization || renameAllPointTags)
{
// If skipOptimization is set to true, automatically set it back to false
const string clearParametersQuery =
"UPDATE DataOperation SET Arguments = '' " +
"WHERE AssemblyName = 'PhasorProtocolAdapters.dll' " +
"AND TypeName = 'PhasorProtocolAdapters.CommonPhasorServices' " +
"AND MethodName = 'PhasorDataSourceValidation'";
database.Connection.ExecuteNonQuery(clearParametersQuery);
}
}
/// <summary>
/// Removed measurement groups from <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 measurement groups to be removed.</param>
/// <param name="measurementGroupsToBeRemoved">List of <see cref="MeasurementGroup"/> IDs to be removed.</param>
/// <returns>string, indicating success for UI display.</returns>
public static string RemoveMeasurementGroups(AdoDataConnection database, Guid subscriberID, List<int> measurementGroupsToBeRemoved)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
foreach (int id in measurementGroupsToBeRemoved)
{
query = database.ParameterizedQueryString("DELETE FROM SubscriberMeasurementGroup WHERE SubscriberID = {0} AND MeasurementGroupID = {1}", "subscriberID", "measurementGroupID");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(subscriberID), id);
}
return "Measurement groups removed from allowed measurement groups list for subscriber successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <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="keys">Keys of the measuremnets to be loaded from the database</param>
/// <returns>Collection of <see cref="OutputStream"/>.</returns>
public static ObservableCollection<OutputStream> Load(AdoDataConnection database, IList<int> keys)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
ObservableCollection<OutputStream> outputStreamList = new ObservableCollection<OutputStream>();
DataTable outputStreamTable;
string query;
string commaSeparatedKeys;
if ((object)keys != null && keys.Count > 0)
{
commaSeparatedKeys = keys.Select(key => "" + key.ToString() + "").Aggregate((str1, str2) => str1 + "," + str2);
query = string.Format("SELECT * FROM OutputStreamDetail WHERE ID IN ({0})", commaSeparatedKeys);
outputStreamTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout);
outputStreamList = new ObservableCollection<OutputStream>(from item in outputStreamTable.AsEnumerable()
let id = item.ConvertField<int>("ID")
let type = item.ConvertField<int>("Type") + 1
orderby keys.IndexOf(id)
select new OutputStream
{
NodeID = database.Guid(item, "NodeID"),
ID = id,
Acronym = item.Field<string>("Acronym"),
Name = item.Field<string>("Name"),
Type = type,
ConnectionString = item.Field<string>("ConnectionString"),
IDCode = item.ConvertField<int>("IDCode"),
CommandChannel = item.Field<string>("CommandChannel"),
DataChannel = item.Field<string>("DataChannel"),
AutoPublishConfigFrame = Convert.ToBoolean(item.Field<object>("AutoPublishConfigFrame")),
AutoStartDataChannel = Convert.ToBoolean(item.Field<object>("AutoStartDataChannel")),
NominalFrequency = item.ConvertField<int>("NominalFrequency"),
FramesPerSecond = item.ConvertNullableField<int>("FramesPerSecond") ?? 30,
LagTime = item.ConvertField<double>("LagTime"),
LeadTime = item.ConvertField<double>("LeadTime"),
UseLocalClockAsRealTime = Convert.ToBoolean(item.Field<object>("UseLocalClockAsRealTime")),
AllowSortsByArrival = Convert.ToBoolean(item.Field<object>("AllowSortsByArrival")),
LoadOrder = item.ConvertField<int>("LoadOrder"),
Enabled = Convert.ToBoolean(item.Field<object>("Enabled")),
m_nodeName = item.Field<string>("NodeName"),
m_typeName = (type == 1) ? "IEEE C37.118" : (type == 2) ? "BPA" : "IEC 61850-90-5",
IgnoreBadTimeStamps = Convert.ToBoolean(item.Field<object>("IgnoreBadTimeStamps")),
TimeResolution = item.ConvertField<int>("TimeResolution"),
AllowPreemptivePublishing = Convert.ToBoolean(item.Field<object>("AllowPreemptivePublishing")),
DownSamplingMethod = item.Field<string>("DownsamplingMethod"),
DataFormat = item.Field<string>("DataFormat"),
CoordinateFormat = item.Field<string>("CoordinateFormat"),
CurrentScalingValue = item.ConvertField<int>("CurrentScalingValue"),
VoltageScalingValue = item.ConvertField<int>("VoltageScalingValue"),
AnalogScalingValue = item.ConvertField<int>("AnalogScalingValue"),
DigitalMaskValue = item.ConvertField<int>("DigitalMaskValue"),
PerformTimestampReasonabilityCheck = Convert.ToBoolean(item.Field<object>("PerformTimeReasonabilityCheck")),
m_mirroringSourceDevice = GetMirroringSource(database, id)
});
return outputStreamList;
}
return outputStreamList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Handles meta-data synchronization to local system.
/// </summary>
/// <remarks>
/// This function should only be initiated from call to <see cref="SynchronizeMetadata(DataSet)"/> to make
/// sure only one meta-data synchronization happens at once. Users can override this method to customize
/// process of meta-data synchronization.
/// </remarks>
protected virtual void SynchronizeMetadata()
{
bool dataMonitoringEnabled = false;
// TODO: This function is complex and very closely tied to the current time-series data schema - perhaps it should be moved outside this class and referenced
// TODO: as a delegate that can be assigned and called to allow other schemas as well. DataPublisher is already very flexible in what data it can deliver.
try
{
DataSet metadata = m_receivedMetadata;
// Only perform database synchronization if meta-data has changed since last update
if (!SynchronizedMetadataChanged(metadata))
return;
if ((object)metadata == null)
{
OnStatusMessage("WARNING: Meta-data synchronization was not performed, deserialized dataset was empty.");
return;
}
// Reset data stream monitor while meta-data synchronization is in progress
if ((object)m_dataStreamMonitor != null && m_dataStreamMonitor.Enabled)
{
m_dataStreamMonitor.Enabled = false;
dataMonitoringEnabled = true;
}
// Track total meta-data synchronization process time
Ticks startTime = DateTime.UtcNow.Ticks;
DateTime updateTime;
DateTime latestUpdateTime = DateTime.MinValue;
// Open the configuration database using settings found in the config file
using (AdoDataConnection database = new AdoDataConnection("systemSettings"))
using (IDbCommand command = database.Connection.CreateCommand())
{
IDbTransaction transaction = null;
if (m_useTransactionForMetadata)
transaction = database.Connection.BeginTransaction(database.DefaultIsloationLevel);
try
{
if ((object)transaction != null)
command.Transaction = transaction;
// Query the actual record ID based on the known run-time ID for this subscriber device
int parentID = Convert.ToInt32(command.ExecuteScalar($"SELECT SourceID FROM Runtime WHERE ID = {ID} AND SourceTable='Device'", m_metadataSynchronizationTimeout));
// Validate that the subscriber device is marked as a concentrator (we are about to associate children devices with it)
if (!command.ExecuteScalar($"SELECT IsConcentrator FROM Device WHERE ID = {parentID}", m_metadataSynchronizationTimeout).ToString().ParseBoolean())
command.ExecuteNonQuery($"UPDATE Device SET IsConcentrator = 1 WHERE ID = {parentID}", m_metadataSynchronizationTimeout);
// Get any historian associated with the subscriber device
object historianID = command.ExecuteScalar($"SELECT HistorianID FROM Device WHERE ID = {parentID}", m_metadataSynchronizationTimeout);
// Determine the active node ID - we cache this since this value won't change for the lifetime of this class
if (m_nodeID == Guid.Empty)
m_nodeID = Guid.Parse(command.ExecuteScalar($"SELECT NodeID FROM IaonInputAdapter WHERE ID = {(int)ID}", m_metadataSynchronizationTimeout).ToString());
// Determine the protocol record auto-inc ID value for the gateway transport protocol (GEP) - this value is also cached since it shouldn't change for the lifetime of this class
if (m_gatewayProtocolID == 0)
m_gatewayProtocolID = int.Parse(command.ExecuteScalar("SELECT ID FROM Protocol WHERE Acronym='GatewayTransport'", m_metadataSynchronizationTimeout).ToString());
// Ascertain total number of actions required for all meta-data synchronization so some level feed back can be provided on progress
InitSyncProgress(metadata.Tables.Cast<DataTable>().Select(dataTable => (long)dataTable.Rows.Count).Sum() + 3);
// Prefix all children devices with the name of the parent since the same device names could appear in different connections (helps keep device names unique)
string sourcePrefix = Name + "!";
Dictionary<string, int> deviceIDs = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
string deviceAcronym, signalTypeAcronym;
decimal longitude, latitude;
decimal? location;
object originalSource;
int deviceID;
// Check to see if data for the "DeviceDetail" table was included in the meta-data
if (metadata.Tables.Contains("DeviceDetail"))
{
DataTable deviceDetail = metadata.Tables["DeviceDetail"];
List<Guid> uniqueIDs = new List<Guid>();
DataRow[] deviceRows;
// Define SQL statement to query if this device is already defined (this should always be based on the unique guid-based device ID)
string deviceExistsSql = database.ParameterizedQueryString("SELECT COUNT(*) FROM Device WHERE UniqueID = {0}", "uniqueID");
// Define SQL statement to insert new device record
string insertDeviceSql = database.ParameterizedQueryString("INSERT INTO Device(NodeID, ParentID, HistorianID, Acronym, Name, ProtocolID, FramesPerSecond, OriginalSource, AccessID, Longitude, Latitude, ContactList, IsConcentrator, Enabled) " +
"VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, 0, 1)", "nodeID", "parentID", "historianID", "acronym", "name", "protocolID", "framesPerSecond", "originalSource", "accessID", "longitude", "latitude", "contactList");
// Define SQL statement to update device's guid-based unique ID after insert
string updateDeviceUniqueIDSql = database.ParameterizedQueryString("UPDATE Device SET UniqueID = {0} WHERE Acronym = {1}", "uniqueID", "acronym");
// Define SQL statement to query if a device can be safely updated
string deviceIsUpdatableSql = database.ParameterizedQueryString("SELECT COUNT(*) FROM Device WHERE UniqueID = {0} AND (ParentID <> {1} OR ParentID IS NULL)", "uniqueID", "parentID");
// Define SQL statement to update existing device record
string updateDeviceSql = database.ParameterizedQueryString("UPDATE Device SET Acronym = {0}, Name = {1}, OriginalSource = {2}, ProtocolID = {3}, FramesPerSecond = {4}, HistorianID = {5}, AccessID = {6}, Longitude = {7}, Latitude = {8}, ContactList = {9} WHERE UniqueID = {10}",
"acronym", "name", "originalSource", "protocolID", "framesPerSecond", "historianID", "accessID", "longitude", "latitude", "contactList", "uniqueID");
// Define SQL statement to retrieve device's auto-inc ID based on its unique guid-based ID
string queryDeviceIDSql = database.ParameterizedQueryString("SELECT ID FROM Device WHERE UniqueID = {0}", "uniqueID");
// Define SQL statement to retrieve all unique device ID's for the current parent to check for mismatches
string queryUniqueDeviceIDsSql = database.ParameterizedQueryString("SELECT UniqueID FROM Device WHERE ParentID = {0}", "parentID");
// Define SQL statement to remove device records that no longer exist in the meta-data
string deleteDeviceSql = database.ParameterizedQueryString("DELETE FROM Device WHERE UniqueID = {0}", "uniqueID");
// Determine which device rows should be synchronized based on operational mode flags
if (ReceiveInternalMetadata && ReceiveExternalMetadata)
deviceRows = deviceDetail.Select();
else if (ReceiveInternalMetadata)
deviceRows = deviceDetail.Select("OriginalSource IS NULL");
else if (ReceiveExternalMetadata)
deviceRows = deviceDetail.Select("OriginalSource IS NOT NULL");
else
deviceRows = new DataRow[0];
// Check existence of optional meta-data fields
DataColumnCollection deviceDetailColumns = deviceDetail.Columns;
bool accessIDFieldExists = deviceDetailColumns.Contains("AccessID");
bool longitudeFieldExists = deviceDetailColumns.Contains("Longitude");
bool latitudeFieldExists = deviceDetailColumns.Contains("Latitude");
bool companyAcronymFieldExists = deviceDetailColumns.Contains("CompanyAcronym");
bool protocolNameFieldExists = deviceDetailColumns.Contains("ProtocolName");
bool vendorAcronymFieldExists = deviceDetailColumns.Contains("VendorAcronym");
bool vendorDeviceNameFieldExists = deviceDetailColumns.Contains("VendorDeviceName");
bool interconnectionNameFieldExists = deviceDetailColumns.Contains("InterconnectionName");
bool updatedOnFieldExists = deviceDetailColumns.Contains("UpdatedOn");
// Older versions of GEP did not include the AccessID field, so this is treated as optional
int accessID = 0;
foreach (DataRow row in deviceRows)
{
Guid uniqueID = Guid.Parse(row.Field<object>("UniqueID").ToString());
bool recordNeedsUpdating;
// Track unique device Guids in this meta-data session, we'll need to remove any old associated devices that no longer exist
uniqueIDs.Add(uniqueID);
// Determine if record has changed since last synchronization
if (updatedOnFieldExists)
{
try
{
updateTime = Convert.ToDateTime(row["UpdatedOn"]);
recordNeedsUpdating = updateTime > m_lastMetaDataRefreshTime;
if (updateTime > latestUpdateTime)
latestUpdateTime = updateTime;
}
catch
{
recordNeedsUpdating = true;
}
}
else
{
recordNeedsUpdating = true;
}
// We will synchronize meta-data only if the source owns this device and it's not defined as a concentrator (these should normally be filtered by publisher - but we check just in case).
if (!row["IsConcentrator"].ToNonNullString("0").ParseBoolean())
{
if (accessIDFieldExists)
accessID = row.ConvertField<int>("AccessID");
// Get longitude and latitude values if they are defined
longitude = 0M;
latitude = 0M;
if (longitudeFieldExists)
{
location = row.ConvertNullableField<decimal>("Longitude");
if (location.HasValue)
longitude = location.Value;
}
if (latitudeFieldExists)
{
location = row.ConvertNullableField<decimal>("Latitude");
if (location.HasValue)
latitude = location.Value;
}
// Save any reported extraneous values from device meta-data in connection string formatted contact list - all fields are considered optional
Dictionary<string, string> contactList = new Dictionary<string, string>();
if (companyAcronymFieldExists)
contactList["companyAcronym"] = row.Field<string>("CompanyAcronym") ?? string.Empty;
if (protocolNameFieldExists)
contactList["protocolName"] = row.Field<string>("ProtocolName") ?? string.Empty;
if (vendorAcronymFieldExists)
contactList["vendorAcronym"] = row.Field<string>("VendorAcronym") ?? string.Empty;
if (vendorDeviceNameFieldExists)
contactList["vendorDeviceName"] = row.Field<string>("VendorDeviceName") ?? string.Empty;
if (interconnectionNameFieldExists)
contactList["interconnectionName"] = row.Field<string>("InterconnectionName") ?? string.Empty;
// Determine if device record already exists
if (Convert.ToInt32(command.ExecuteScalar(deviceExistsSql, m_metadataSynchronizationTimeout, database.Guid(uniqueID))) == 0)
{
// Insert new device record
command.ExecuteNonQuery(insertDeviceSql, m_metadataSynchronizationTimeout, database.Guid(m_nodeID), parentID, historianID, sourcePrefix + row.Field<string>("Acronym"), row.Field<string>("Name"), m_gatewayProtocolID, row.ConvertField<int>("FramesPerSecond"),
m_internal ? (object)DBNull.Value : string.IsNullOrEmpty(row.Field<string>("ParentAcronym")) ? sourcePrefix + row.Field<string>("Acronym") : sourcePrefix + row.Field<string>("ParentAcronym"), accessID, longitude, latitude, contactList.JoinKeyValuePairs());
// Guids are normally auto-generated during insert - after insertion update the Guid so that it matches the source data. Most of the database
// scripts have triggers that support properly assigning the Guid during an insert, but this code ensures the Guid will always get assigned.
command.ExecuteNonQuery(updateDeviceUniqueIDSql, m_metadataSynchronizationTimeout, database.Guid(uniqueID), sourcePrefix + row.Field<string>("Acronym"));
}
else if (recordNeedsUpdating)
{
// Perform safety check to preserve device records which are not safe to overwrite
if (Convert.ToInt32(command.ExecuteScalar(deviceIsUpdatableSql, m_metadataSynchronizationTimeout, database.Guid(uniqueID), parentID)) > 0)
continue;
// Gateway is assuming ownership of the device records when the "internal" flag is true - this means the device's measurements can be forwarded to another party. From a device record perspective,
// ownership is inferred by setting 'OriginalSource' to null. When gateway doesn't own device records (i.e., the "internal" flag is false), this means the device's measurements can only be consumed
// locally - from a device record perspective this means the 'OriginalSource' field is set to the acronym of the PDC or PMU that generated the source measurements. This field allows a mirrored source
// restriction to be implemented later to ensure all devices in an output protocol came from the same original source connection, if desired.
originalSource = m_internal ? (object)DBNull.Value : string.IsNullOrEmpty(row.Field<string>("ParentAcronym")) ? sourcePrefix + row.Field<string>("Acronym") : sourcePrefix + row.Field<string>("ParentAcronym");
// Update existing device record
command.ExecuteNonQuery(updateDeviceSql, m_metadataSynchronizationTimeout, sourcePrefix + row.Field<string>("Acronym"), row.Field<string>("Name"), originalSource, m_gatewayProtocolID, row.ConvertField<int>("FramesPerSecond"), historianID, accessID, longitude, latitude, contactList.JoinKeyValuePairs(), database.Guid(uniqueID));
}
}
// Capture local device ID auto-inc value for measurement association
deviceIDs[row.Field<string>("Acronym")] = Convert.ToInt32(command.ExecuteScalar(queryDeviceIDSql, m_metadataSynchronizationTimeout, database.Guid(uniqueID)));
// Periodically notify user about synchronization progress
UpdateSyncProgress();
}
// Remove any device records associated with this subscriber that no longer exist in the meta-data
if (uniqueIDs.Count > 0)
{
// Sort unique ID list so that binary search can be used for quick lookups
uniqueIDs.Sort();
DataTable deviceUniqueIDs = command.RetrieveData(database.AdapterType, queryUniqueDeviceIDsSql, m_metadataSynchronizationTimeout, parentID);
Guid uniqueID;
foreach (DataRow deviceRow in deviceUniqueIDs.Rows)
{
uniqueID = database.Guid(deviceRow, "UniqueID");
// Remove any devices in the database that are associated with the parent device and do not exist in the meta-data
if (uniqueIDs.BinarySearch(uniqueID) < 0)
command.ExecuteNonQuery(deleteDeviceSql, m_metadataSynchronizationTimeout, database.Guid(uniqueID));
}
UpdateSyncProgress();
}
}
// Check to see if data for the "MeasurementDetail" table was included in the meta-data
if (metadata.Tables.Contains("MeasurementDetail"))
{
DataTable measurementDetail = metadata.Tables["MeasurementDetail"];
List<Guid> signalIDs = new List<Guid>();
DataRow[] measurementRows;
// Define SQL statement to query if this measurement is already defined (this should always be based on the unique signal ID Guid)
string measurementExistsSql = database.ParameterizedQueryString("SELECT COUNT(*) FROM Measurement WHERE SignalID = {0}", "signalID");
// Define SQL statement to insert new measurement record
string insertMeasurementSql = database.ParameterizedQueryString("INSERT INTO Measurement(DeviceID, HistorianID, PointTag, AlternateTag, SignalTypeID, PhasorSourceIndex, SignalReference, Description, Internal, Subscribed, Enabled) " +
"VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, 0, 1)", "deviceID", "historianID", "pointTag", "alternateTag", "signalTypeID", "phasorSourceIndex", "signalReference", "description", "internal");
// Define SQL statement to update measurement's signal ID after insert
string updateMeasurementSignalIDSql = database.ParameterizedQueryString("UPDATE Measurement SET SignalID = {0}, AlternateTag = NULL WHERE AlternateTag = {1}", "signalID", "alternateTag");
// Define SQL statement to update existing measurement record
string updateMeasurementSql = database.ParameterizedQueryString("UPDATE Measurement SET HistorianID = {0}, PointTag = {1}, SignalTypeID = {2}, PhasorSourceIndex = {3}, SignalReference = {4}, Description = {5}, Internal = {6} WHERE SignalID = {7}",
"historianID", "pointTag", "signalTypeID", "phasorSourceIndex", "signalReference", "description", "internal", "signalID");
// Define SQL statement to retrieve all measurement signal ID's for the current parent to check for mismatches - note that we use the ActiveMeasurements view
// since it associates measurements with their top-most parent runtime device ID, this allows us to easily query all measurements for the parent device
string queryMeasurementSignalIDsSql = database.ParameterizedQueryString("SELECT SignalID FROM ActiveMeasurement WHERE DeviceID = {0}", "deviceID");
// Define SQL statement to retrieve measurement's associated device ID, i.e., actual record ID, based on measurement's signal ID
string queryMeasurementDeviceIDSql = database.ParameterizedQueryString("SELECT DeviceID FROM Measurement WHERE SignalID = {0}", "signalID");
// Define SQL statement to remove device records that no longer exist in the meta-data
string deleteMeasurementSql = database.ParameterizedQueryString("DELETE FROM Measurement WHERE SignalID = {0}", "signalID");
// Load signal type ID's from local database associated with their acronym for proper signal type translation
Dictionary<string, int> signalTypeIDs = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
foreach (DataRow row in command.RetrieveData(database.AdapterType, "SELECT ID, Acronym FROM SignalType").Rows)
{
signalTypeAcronym = row.Field<string>("Acronym");
if (!string.IsNullOrWhiteSpace(signalTypeAcronym))
signalTypeIDs[signalTypeAcronym] = row.ConvertField<int>("ID");
}
// Determine which measurement rows should be synchronized based on operational mode flags
if (ReceiveInternalMetadata && ReceiveExternalMetadata)
measurementRows = measurementDetail.Select();
else if (ReceiveInternalMetadata)
measurementRows = measurementDetail.Select("Internal <> 0");
else if (ReceiveExternalMetadata)
measurementRows = measurementDetail.Select("Internal = 0");
else
measurementRows = new DataRow[0];
// Check existence of optional meta-data fields
DataColumnCollection measurementDetailColumns = measurementDetail.Columns;
bool phasorSourceIndexFieldExists = measurementDetailColumns.Contains("PhasorSourceIndex");
bool updatedOnFieldExists = measurementDetailColumns.Contains("UpdatedOn");
object phasorSourceIndex = DBNull.Value;
foreach (DataRow row in measurementRows)
{
bool recordNeedsUpdating;
// Determine if record has changed since last synchronization
if (updatedOnFieldExists)
{
try
{
updateTime = Convert.ToDateTime(row["UpdatedOn"]);
recordNeedsUpdating = updateTime > m_lastMetaDataRefreshTime;
if (updateTime > latestUpdateTime)
latestUpdateTime = updateTime;
}
catch
{
recordNeedsUpdating = true;
}
}
else
{
recordNeedsUpdating = true;
}
// Get device and signal type acronyms
deviceAcronym = row.Field<string>("DeviceAcronym") ?? string.Empty;
signalTypeAcronym = row.Field<string>("SignalAcronym") ?? string.Empty;
// Get phasor source index if field is defined
if (phasorSourceIndexFieldExists)
{
// Using ConvertNullableField extension since publisher could use SQLite database in which case
// all integers would arrive in data set as longs and need to be converted back to integers
int? index = row.ConvertNullableField<int>("PhasorSourceIndex");
phasorSourceIndex = index.HasValue ? (object)index.Value : (object)DBNull.Value;
}
// Make sure we have an associated device and signal type already defined for the measurement
if (!string.IsNullOrWhiteSpace(deviceAcronym) && deviceIDs.ContainsKey(deviceAcronym) && !string.IsNullOrWhiteSpace(signalTypeAcronym) && signalTypeIDs.ContainsKey(signalTypeAcronym))
{
Guid signalID = Guid.Parse(row.Field<object>("SignalID").ToString());
// Track unique measurement signal Guids in this meta-data session, we'll need to remove any old associated measurements that no longer exist
signalIDs.Add(signalID);
// Prefix the tag name with the "updated" device name
string pointTag = sourcePrefix + row.Field<string>("PointTag");
// Look up associated device ID (local DB auto-inc)
deviceID = deviceIDs[deviceAcronym];
// Determine if measurement record already exists
if (Convert.ToInt32(command.ExecuteScalar(measurementExistsSql, m_metadataSynchronizationTimeout, database.Guid(signalID))) == 0)
{
string alternateTag = Guid.NewGuid().ToString();
// Insert new measurement record
command.ExecuteNonQuery(insertMeasurementSql, m_metadataSynchronizationTimeout, deviceID, historianID, pointTag, alternateTag, signalTypeIDs[signalTypeAcronym], phasorSourceIndex, sourcePrefix + row.Field<string>("SignalReference"), row.Field<string>("Description") ?? string.Empty, database.Bool(m_internal));
// Guids are normally auto-generated during insert - after insertion update the Guid so that it matches the source data. Most of the database
// scripts have triggers that support properly assigning the Guid during an insert, but this code ensures the Guid will always get assigned.
command.ExecuteNonQuery(updateMeasurementSignalIDSql, m_metadataSynchronizationTimeout, database.Guid(signalID), alternateTag);
}
else if (recordNeedsUpdating)
{
// Update existing measurement record. Note that this update assumes that measurements will remain associated with a static source device.
command.ExecuteNonQuery(updateMeasurementSql, m_metadataSynchronizationTimeout, historianID, pointTag, signalTypeIDs[signalTypeAcronym], phasorSourceIndex, sourcePrefix + row.Field<string>("SignalReference"), row.Field<string>("Description") ?? string.Empty, database.Bool(m_internal), database.Guid(signalID));
}
}
// Periodically notify user about synchronization progress
UpdateSyncProgress();
}
// Remove any measurement records associated with existing devices in this session but no longer exist in the meta-data
if (signalIDs.Count > 0)
{
// Sort signal ID list so that binary search can be used for quick lookups
signalIDs.Sort();
// Query all the guid-based signal ID's for all measurement records associated with the parent device using run-time ID
DataTable measurementSignalIDs = command.RetrieveData(database.AdapterType, queryMeasurementSignalIDsSql, m_metadataSynchronizationTimeout, (int)ID);
Guid signalID;
// Walk through each database record and see if the measurement exists in the provided meta-data
foreach (DataRow measurementRow in measurementSignalIDs.Rows)
{
signalID = database.Guid(measurementRow, "SignalID");
// Remove any measurements in the database that are associated with received devices and do not exist in the meta-data
if (signalIDs.BinarySearch(signalID) < 0)
{
// Measurement was not in the meta-data, get the measurement's actual record based ID for its associated device
object measurementDeviceID = command.ExecuteScalar(queryMeasurementDeviceIDSql, m_metadataSynchronizationTimeout, database.Guid(signalID));
// If the unknown measurement is directly associated with a device that exists in the meta-data it is assumed that this measurement
// was removed from the publishing system and no longer exists therefore we remove it from the local measurement cache. If the user
// needs custom local measurements associated with a remote device, they should be associated with the parent device only.
if (measurementDeviceID != null && !(measurementDeviceID is DBNull) && deviceIDs.ContainsValue(Convert.ToInt32(measurementDeviceID)))
command.ExecuteNonQuery(deleteMeasurementSql, m_metadataSynchronizationTimeout, database.Guid(signalID));
}
}
UpdateSyncProgress();
}
}
// Check to see if data for the "PhasorDetail" table was included in the meta-data
if (metadata.Tables.Contains("PhasorDetail"))
{
Dictionary<int, int> maxSourceIndicies = new Dictionary<int, int>();
int sourceIndex;
// Phasor data is normally only needed so that the user can properly generate a mirrored IEEE C37.118 output stream from the source data.
// This is necessary since, in this protocol, the phasors are described (i.e., labeled) as a unit (i.e., as a complex number) instead of
// as two distinct angle and magnitude measurements.
// Define SQL statement to query if phasor record is already defined (no Guid is defined for these simple label records)
string phasorExistsSql = database.ParameterizedQueryString("SELECT COUNT(*) FROM Phasor WHERE DeviceID = {0} AND SourceIndex = {1}", "deviceID", "sourceIndex");
// Define SQL statement to insert new phasor record
string insertPhasorSql = database.ParameterizedQueryString("INSERT INTO Phasor(DeviceID, Label, Type, Phase, SourceIndex) VALUES ({0}, {1}, {2}, {3}, {4})", "deviceID", "label", "type", "phase", "sourceIndex");
// Define SQL statement to update existing phasor record
string updatePhasorSql = database.ParameterizedQueryString("UPDATE Phasor SET Label = {0}, Type = {1}, Phase = {2} WHERE DeviceID = {3} AND SourceIndex = {4}", "label", "type", "phase", "deviceID", "sourceIndex");
// Define SQL statement to delete a phasor record
string deletePhasorSql = database.ParameterizedQueryString("DELETE FROM Phasor WHERE DeviceID = {0} AND SourceIndex > {1}", "deviceID", "sourceIndex");
foreach (DataRow row in metadata.Tables["PhasorDetail"].Rows)
{
// Get device acronym
deviceAcronym = row.Field<string>("DeviceAcronym") ?? string.Empty;
// Make sure we have an associated device already defined for the phasor record
if (!string.IsNullOrWhiteSpace(deviceAcronym) && deviceIDs.ContainsKey(deviceAcronym))
{
bool recordNeedsUpdating;
// Determine if record has changed since last synchronization
try
{
updateTime = Convert.ToDateTime(row["UpdatedOn"]);
recordNeedsUpdating = updateTime > m_lastMetaDataRefreshTime;
if (updateTime > latestUpdateTime)
latestUpdateTime = updateTime;
}
catch
{
recordNeedsUpdating = true;
}
deviceID = deviceIDs[deviceAcronym];
// Determine if phasor record already exists
if (Convert.ToInt32(command.ExecuteScalar(phasorExistsSql, m_metadataSynchronizationTimeout, deviceID, row.ConvertField<int>("SourceIndex"))) == 0)
{
// Insert new phasor record
command.ExecuteNonQuery(insertPhasorSql, m_metadataSynchronizationTimeout, deviceID, row.Field<string>("Label") ?? "undefined", (row.Field<string>("Type") ?? "V").TruncateLeft(1), (row.Field<string>("Phase") ?? "+").TruncateLeft(1), row.ConvertField<int>("SourceIndex"));
}
else if (recordNeedsUpdating)
{
// Update existing phasor record
command.ExecuteNonQuery(updatePhasorSql, m_metadataSynchronizationTimeout, row.Field<string>("Label") ?? "undefined", (row.Field<string>("Type") ?? "V").TruncateLeft(1), (row.Field<string>("Phase") ?? "+").TruncateLeft(1), deviceID, row.ConvertField<int>("SourceIndex"));
}
// Track largest source index for each device
maxSourceIndicies.TryGetValue(deviceID, out sourceIndex);
if (row.ConvertField<int>("SourceIndex") > sourceIndex)
maxSourceIndicies[deviceID] = row.ConvertField<int>("SourceIndex");
}
// Periodically notify user about synchronization progress
UpdateSyncProgress();
}
// Remove any phasor records associated with existing devices in this session but no longer exist in the meta-data
if (maxSourceIndicies.Count > 0)
{
foreach (KeyValuePair<int, int> deviceIndexPair in maxSourceIndicies)
{
command.ExecuteNonQuery(deletePhasorSql, m_metadataSynchronizationTimeout, deviceIndexPair.Key, deviceIndexPair.Value);
}
}
}
if ((object)transaction != null)
transaction.Commit();
// Update local in-memory synchronized meta-data cache
m_synchronizedMetadata = metadata;
}
catch (Exception ex)
{
OnProcessException(new InvalidOperationException("Failed to synchronize meta-data to local cache: " + ex.Message, ex));
if ((object)transaction != null)
{
try
{
transaction.Rollback();
}
catch (Exception rollbackException)
{
OnProcessException(new InvalidOperationException("Failed to roll back database transaction due to exception: " + rollbackException.Message, rollbackException));
}
}
return;
}
finally
{
if ((object)transaction != null)
transaction.Dispose();
}
}
// New signals may have been defined, take original remote signal index cache and apply changes
if (m_remoteSignalIndexCache != null)
m_signalIndexCache = new SignalIndexCache(DataSource, m_remoteSignalIndexCache);
m_lastMetaDataRefreshTime = latestUpdateTime > DateTime.MinValue ? latestUpdateTime : DateTime.UtcNow;
OnStatusMessage("Meta-data synchronization completed successfully in {0}", (DateTime.UtcNow.Ticks - startTime).ToElapsedTimeString(2));
// Send notification that system configuration has changed
OnConfigurationChanged();
}
catch (Exception ex)
{
OnProcessException(new InvalidOperationException("Failed to synchronize meta-data to local cache: " + ex.Message, ex));
}
finally
{
// Restart data stream monitor after meta-data synchronization if it was originally enabled
if (dataMonitoringEnabled && (object)m_dataStreamMonitor != null)
m_dataStreamMonitor.Enabled = true;
}
}
/// <summary>
/// Loads <see cref="OutputStreamDevicePhasor"/> 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 measuremnets to be loaded from the database</param>
/// <returns>Collection of <see cref="OutputStreamDevicePhasor"/>.</returns>
public static ObservableCollection<OutputStreamDevicePhasor> Load(AdoDataConnection database, IList<int> keys)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
string query;
string commaSeparatedKeys;
OutputStreamDevicePhasor[] outputStreamDevicePhasorList = null;
DataTable outputStreamDevicePhasorTable;
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, OutputStreamDeviceID, ID, Label, Type, Phase, ScalingValue, LoadOrder " +
"FROM OutputStreamDevicePhasor WHERE ID IN ({0})", commaSeparatedKeys));
outputStreamDevicePhasorTable = database.Connection.RetrieveData(database.AdapterType, query);
outputStreamDevicePhasorList = new OutputStreamDevicePhasor[outputStreamDevicePhasorTable.Rows.Count];
foreach (DataRow row in outputStreamDevicePhasorTable.Rows)
{
id = row.ConvertField<int>("ID");
outputStreamDevicePhasorList[keys.IndexOf(id)] = new OutputStreamDevicePhasor()
{
NodeID = database.Guid(row, "NodeID"),
OutputStreamDeviceID = row.ConvertField<int>("OutputStreamDeviceID"),
ID = id,
Label = row.Field<string>("Label"),
Type = row.Field<string>("Type"),
Phase = row.Field<string>("Phase"),
ScalingValue = row.ConvertField<int>("ScalingValue"),
LoadOrder = row.ConvertField<int>("LoadOrder"),
m_phaseType = row.Field<string>("Phase") == "+" ? "Positive Sequence" : row.Field<string>("Phase") == "-" ? "Negative Sequence" :
row.Field<string>("Phase") == "0" ? "Zero Sequence" : row.Field<string>("Phase") == "A" ? "Phase A" :
row.Field<string>("Phase") == "B" ? "Phase B" : "Phase C",
m_phasorType = row.Field<string>("Type") == "V" ? "Voltage" : "Current"
};
}
}
return new ObservableCollection<OutputStreamDevicePhasor>(outputStreamDevicePhasorList ?? new OutputStreamDevicePhasor[0]);
}
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();
}
}
/// <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>
/// Deletes specified <see cref="UserAccount"/> record from database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="userAccountID">ID of the record to be deleted.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Delete(AdoDataConnection database, Guid userAccountID)
{
bool createdConnection = false;
string userName;
try
{
createdConnection = CreateConnection(ref database);
// Get the name of the user to be deleted
userName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM UserAccount WHERE ID = {0}", "userAccountID"), DefaultTimeout, database.Guid(userAccountID)).ToNonNullString();
// Setup current user context for any delete triggers
CommonFunctions.SetCurrentUserContext(database);
// Delete the user from the database
database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM UserAccount WHERE ID = {0}", "userAccountID"), DefaultTimeout, database.Guid(userAccountID));
// Write to the event log
CommonFunctions.LogEvent(string.Format("User \"{0}\" deleted successfully by user \"{1}\".", UserInfo.SIDToAccountName(userName), CommonFunctions.CurrentUser), 12);
return "User account deleted successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Retrieves <see cref="Dictionary{T1,T2}"/> type collection of <see cref="MeasurementGroup"/> neither allowed nor denied for <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 filter data.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> type collection of ID and Name of <see cref="MeasurementGroup"/>.</returns>
public static Dictionary<int, string> GetAvailableMeasurementGroups(AdoDataConnection database, Guid subscriberID)
{
Dictionary<int, string> availableMeasurementGroups;
DataTable availableMeasurementGroupTable;
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
availableMeasurementGroups = new Dictionary<int, string>();
query = database.ParameterizedQueryString("SELECT ID, Name FROM MeasurementGroup WHERE ID NOT IN (SELECT MeasurementGroupID FROM SubscriberMeasurementGroup WHERE SubscriberID = {0}) ORDER BY Name", "subscriberID");
availableMeasurementGroupTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Guid(subscriberID));
foreach (DataRow row in availableMeasurementGroupTable.Rows)
availableMeasurementGroups[row.ConvertField<int>("ID")] = row.Field<string>("Name");
return availableMeasurementGroups;
}
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>
/// Retrieves <see cref="Dictionary{T1,T2}"/> type collection of <see cref="MeasurementGroup"/> denied for <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 filter data.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> type collection of ID and Name of <see cref="MeasurementGroup"/>.</returns>
public static Dictionary<int, string> GetDeniedMeasurementGroups(AdoDataConnection database, Guid subscriberID)
{
Dictionary<int, string> deniedMeasurementGroups;
DataTable deniedMeasurementGroupTable;
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
deniedMeasurementGroups = new Dictionary<int, string>();
query = database.ParameterizedQueryString("SELECT MeasurementGroupID, MeasurementGroupName FROM SubscriberMeasGroupDetail WHERE SubscriberID = {0} AND Allowed = {1} ORDER BY MeasurementGroupName", "subscriberID", "allowed");
deniedMeasurementGroupTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Guid(subscriberID), database.Bool(false));
foreach (DataRow row in deniedMeasurementGroupTable.Rows)
deniedMeasurementGroups[row.ConvertField<int>("MeasurementGroupID")] = row.Field<string>("MeasurementGroupName");
return deniedMeasurementGroups;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Loads <see cref="PowerCalculation"/> 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="PowerCalculation"/>.</returns>
public static ObservableCollection<Phasor> Load(AdoDataConnection database, IList<int> keys)
{
var createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Phasor[] phasorList = null;
if (keys != null && keys.Count > 0)
{
var commaSeparatedKeys = keys.Select(key => key.ToString()).Aggregate((str1, str2) => str1 + "," + str2);
var query = string.Format("select p.id, p.deviceid, d.acronym as DeviceAcronym, p.label, p.type, p.phase, p.sourceindex, mags.signalid as mag_signalid, angles.signalid as angle_signalid " +
"from phasor p left join measurement mags " +
"on mags.deviceid = p.deviceid and mags.phasorsourceindex = p.sourceindex and mags.signaltypeid in (1,3) " +
"left join measurement angles " +
"on angles.deviceid = p.deviceid and angles.phasorsourceindex = p.sourceindex and angles.signaltypeid in (2,4) " +
"left join device d " +
"on p.deviceid = d.id " +
"where p.id in ({0})", commaSeparatedKeys);
var phasorTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout);
phasorList = new Phasor[phasorTable.Rows.Count];
foreach (DataRow row in phasorTable.Rows)
{
var id = row.ConvertField<int>("Id");
var deviceId = row.ConvertField<int>("DeviceId");
var deviceName = row.ConvertField<string>("DeviceAcronym");
var label = row.ConvertField<string>("Label");
var type = row.ConvertField<string>("Type");
var phase = row.ConvertField<string>("Phase");
var sourceIndex = row.ConvertField<int>("SourceIndex");
var magnitudeSignalId = database.Guid(row, "mag_signalId");
var angleSignalId = database.Guid(row, "angle_signalId");
var measurements = Measurement.LoadFromKeys(database, (new[] {magnitudeSignalId, angleSignalId}).ToList());
phasorList[keys.IndexOf(id)] = new Phasor
{
ID = id,
DeviceID = deviceId,
DeviceName = deviceName,
Label = label,
Type = type,
Phase = phase,
SourceIndex = sourceIndex,
MagnitudeMeasurement = measurements.FirstOrDefault(m => m.SignalID == magnitudeSignalId),
AngleMeasurement = measurements.FirstOrDefault(m => m.SignalID == angleSignalId)
};
}
}
return new ObservableCollection<Phasor>(phasorList ?? new Phasor[0]);
}
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 <see cref="Dictionary{T1,T2}"/> type collection of <see cref="Measurement"/> allowed for <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 filter data.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> type collection of SignalID and PointTag of <see cref="Measurement"/>.</returns>
public static Dictionary<Guid, string> GetAllowedMeasurements(AdoDataConnection database, Guid subscriberID)
{
Dictionary<Guid, string> allowedMeasurements;
DataTable allowedMeasurementTable;
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
allowedMeasurements = new Dictionary<Guid, string>();
query = database.ParameterizedQueryString("SELECT SignalID, PointTag FROM SubscriberMeasurementDetail WHERE SubscriberID = {0} AND Allowed = {1} ORDER BY PointTag", "subscriberID", "allowed");
allowedMeasurementTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Guid(subscriberID), database.Bool(true));
foreach (DataRow row in allowedMeasurementTable.Rows)
allowedMeasurements[database.Guid(row, "SignalID")] = row.Field<string>("PointTag");
return allowedMeasurements;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Deletes specified <see cref="Subscriber"/> record from database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="id">ID of the record to be deleted.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Delete(AdoDataConnection database, Guid id)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
// Setup current user context for any delete triggers
CommonFunctions.SetCurrentUserContext(database);
database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM Subscriber WHERE ID = {0}", "id"), DefaultTimeout, database.Guid(id));
try
{
CommonFunctions.SendCommandToService("ReloadConfig");
}
catch (Exception ex)
{
return "Subscriber deleted successfully. Failed to send ReloadConfig command to backend service." + Environment.NewLine + ex.Message;
}
return "Subscriber deleted successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Retrieves <see cref="MeasurementGroup"/> information for the group with the given ID.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="groupID">The ID of the measurement group to be retrieved.</param>
/// <returns>Measurement group with the given ID.</returns>
public static MeasurementGroup GetMeasurementGroup(AdoDataConnection database, int groupID)
{
DataTable measurementGroupTable;
bool createdConnection = false;
DataRow row;
try
{
createdConnection = CreateConnection(ref database);
measurementGroupTable = database.RetrieveData(DefaultTimeout, "SELECT * FROM MeasurementGroup WHERE ID = {0}", groupID);
if (measurementGroupTable.Rows.Count == 0)
return null;
row = measurementGroupTable.Rows[0];
MeasurementGroup measurementGroup = 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(),
};
return measurementGroup;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}