/// <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 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, groupID);
foreach (DataRow row in possibleUsersTable.Rows)
possibleGroupUsers[database.Guid(row, "ID")] = row.Field<string>("Name");
return possibleGroupUsers;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Deletes specified <see cref="Historian"/> record from database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="historianID">ID of the record to be deleted.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Delete(AdoDataConnection database, int historianID)
{
bool createdConnection = false;
try
{
if (database == null)
{
database = new AdoDataConnection(CommonFunctions.DefaultSettingsCategory);
createdConnection = true;
}
CommonFunctions.SetCurrentUserContext(database);
database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM Historian WHERE ID = {0}", "historianID"), DefaultTimeout, historianID);
TimeSeriesFramework.UI.CommonFunctions.SendCommandToService("ReloadConfig");
return "Historian deleted successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Historian"/> information.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param>
/// <param name="includeStatHistorian">Indicates if statistical historian included in the collection.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> containing ID and Name of historians defined in the database.</returns>
public static Dictionary<int, string> GetLookupList(AdoDataConnection database, bool isOptional = false, bool includeStatHistorian = true)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary<int, string> historianList = new Dictionary<int, string>();
if (isOptional)
historianList.Add(0, "Select Historian");
string query = database.ParameterizedQueryString("SELECT ID, Acronym FROM Historian WHERE Enabled = {0} AND NodeID = {1} ORDER BY LoadOrder", "enabled", "nodeID");
DataTable historianTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Bool(true), database.CurrentNodeID());
foreach (DataRow row in historianTable.Rows)
{
if (!includeStatHistorian)
{
if (row.Field<string>("Acronym").ToUpper() != "STAT")
historianList[row.ConvertField<int>("ID")] = row.Field<string>("Acronym");
}
else
historianList[row.ConvertField<int>("ID")] = row.Field<string>("Acronym");
}
return historianList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
// Static Methods
/// <summary>
/// Loads <see cref="Historian"/> IDs as an <see cref="IList{T}"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="sortMember">The field to sort by.</param>
/// <param name="sortDirection"><c>ASC</c> or <c>DESC</c> for ascending or descending respectively.</param>
/// <returns>Collection of <see cref="Int32"/>.</returns>
public static IList<int> LoadKeys(AdoDataConnection database, string sortMember = "", string sortDirection = "")
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
IList<int> historianList = new List<int>();
string sortClause = string.Empty;
DataTable historianTable;
string query;
if (!string.IsNullOrEmpty(sortMember) || !string.IsNullOrEmpty(sortDirection))
sortClause = string.Format("ORDER BY {0} {1}", sortMember, sortDirection);
query = database.ParameterizedQueryString(string.Format("SELECT ID FROM HistorianDetail WHERE NodeID = {{0}} {0}", sortClause), "nodeID");
historianTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID());
foreach (DataRow row in historianTable.Rows)
{
historianList.Add(row.ConvertField<int>("ID"));
}
return historianList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
// Static Methods
/// <summary>
/// Loads <see cref="SignalType"/> information as an <see cref="ObservableCollection{T}"/> style list.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="source">Type if source to filter data.</param>
/// <param name="phasorType">Type of phasor type to filter data.</param>
/// <returns>Collection of <see cref="SignalType"/>.</returns>
public static ObservableCollection<SignalType> Load(AdoDataConnection database, string source = "", string phasorType = "")
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
ObservableCollection<SignalType> signalTypeList = new ObservableCollection<SignalType>();
string query = "SELECT ID, Acronym, Name, Suffix, Abbreviation, Source, EngineeringUnits FROM SignalType ORDER BY Name";
if (!string.IsNullOrEmpty(source) && source.ToUpper() == "PMU")
{
query = "SELECT ID, Acronym, Name, Suffix, Abbreviation, Source, EngineeringUnits FROM SignalType Where Source = 'PMU' AND SUFFIX IN ('FQ','DF','SF','AV','DV','CV') ORDER BY Name";
}
else if (!string.IsNullOrEmpty(source) && source.ToUpper() == "PHASOR" && !string.IsNullOrEmpty(phasorType))
{
if (phasorType.ToUpper() == "V")
query = "SELECT ID, Acronym, Name, Suffix, Abbreviation, Source, EngineeringUnits FROM SignalType Where Source = 'Phasor' " +
"AND Acronym IN ('VPHM', 'VPHA') ORDER BY Name";
else if (phasorType.ToUpper() == "I")
query = "SELECT ID, Acronym, Name, Suffix, Abbreviation, Source, EngineeringUnits FROM SignalType Where Source = 'Phasor' " +
"AND Acronym IN ('IPHM', 'IPHA') ORDER BY Name";
}
DataTable signalTypeTable = database.Connection.RetrieveData(database.AdapterType, query);
foreach (DataRow row in signalTypeTable.Rows)
{
signalTypeList.Add(new SignalType()
{
ID = row.ConvertField<int>("ID"),
Acronym = row.Field<string>("Acronym"),
Name = row.Field<string>("Name"),
Suffix = row.Field<string>("Suffix"),
Abbreviation = row.Field<string>("Abbreviation"),
Source = row.Field<string>("Source"),
EngineeringUnits = row["EngineeringUnits"].ToNonNullString()
});
}
return signalTypeList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Measurement"/> information.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param>
/// <param name="subscribedOnly">boolean flag to indicate if only subscribed measurements to be returned.</param>
/// <returns><see cref="Dictionary{T1,T2}"/> containing PointID and SignalID of measurements defined in the database.</returns>
public static Dictionary<Guid, string> GetLookupList(AdoDataConnection database, bool isOptional = false, bool subscribedOnly = false)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary<Guid, string> measurementList = new Dictionary<Guid, string>();
if (isOptional)
measurementList.Add(Guid.Empty, "Select Measurement");
DataTable measurementTable;
string query;
if (subscribedOnly)
{
// If subscribedOnly is set then return only those measurements which are not internal and subscribed flag is set to true.
query = database.ParameterizedQueryString("SELECT SignalID, PointTag FROM MeasurementDetail WHERE " +
"NodeID = {0} AND Internal = {1} AND Subscribed = {2} ORDER BY PointID", "nodeID", "internal", "subscribed");
measurementTable = database.Connection.RetrieveData(database.AdapterType, query,
DefaultTimeout, database.CurrentNodeID(), database.Bool(false), database.Bool(true));
}
else
{
query = database.ParameterizedQueryString("SELECT SignalID, PointTag FROM MeasurementDetail WHERE NodeID = {0} ORDER BY PointID", "nodeID");
measurementTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID());
}
foreach (DataRow row in measurementTable.Rows)
measurementList[database.Guid(row, "SignalID")] = row.Field<string>("PointTag");
return measurementList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Loads <see cref="Measurement"/> 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 measurements to be loaded from the database.</param>
/// <returns>Collection of <see cref="Measurement"/>.</returns>
public static ObservableCollection<Measurement> LoadFromKeys(AdoDataConnection database, List<Guid> keys)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
ObservableCollection<Measurement> measurementList = new ObservableCollection<Measurement>();
DataTable measurementTable;
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 MeasurementDetail WHERE SignalID IN ({0})", commaSeparatedKeys);
measurementTable = database.Connection.RetrieveData(database.AdapterType, query);
foreach (DataRow row in measurementTable.Rows)
{
measurementList.Add(new Measurement()
{
SignalID = database.Guid(row, "SignalID"),
HistorianID = row.ConvertNullableField<int>("HistorianID"),
PointID = row.ConvertField<int>("PointID"),
DeviceID = row.ConvertNullableField<int>("DeviceID"),
PointTag = row.Field<string>("PointTag"),
AlternateTag = row.Field<string>("AlternateTag"),
SignalTypeID = row.ConvertField<int>("SignalTypeID"),
PhasorSourceIndex = row.ConvertNullableField<int>("PhasorSourceIndex"),
SignalReference = row.Field<string>("SignalReference"),
Adder = row.ConvertField<double>("Adder"),
Multiplier = row.ConvertField<double>("Multiplier"),
Internal = Convert.ToBoolean(row.Field<object>("Internal")),
Subscribed = Convert.ToBoolean(row.Field<object>("Subscribed")),
Description = row.Field<string>("Description"),
Enabled = Convert.ToBoolean(row.Field<object>("Enabled")),
m_historianAcronym = row.Field<string>("HistorianAcronym"),
m_deviceAcronym = row.Field<object>("DeviceAcronym") == null ? string.Empty : row.Field<string>("DeviceAcronym"),
m_signalName = row.Field<string>("SignalName"),
m_signalAcronym = row.Field<string>("SignalAcronym"),
m_signalSuffix = row.Field<string>("SignalTypeSuffix"),
m_phasorLabel = row.Field<string>("PhasorLabel"),
m_framesPerSecond = Convert.ToInt32(row.Field<object>("FramesPerSecond") ?? 30),
m_id = row.Field<string>("ID"),
m_companyAcronym = row.Field<object>("CompanyAcronym") == null ? string.Empty : row.Field<string>("CompanyAcronym"),
m_companyName = row.Field<object>("CompanyName") == null ? string.Empty : row.Field<string>("CompanyName"),
Selected = false
});
}
}
return measurementList;
}
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();
}
}
/// <summary>
/// Retrieves a <see cref="Node"/> defined in the database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <returns><see cref="Node"/> information.</returns>
public static Node GetCurrentNode(AdoDataConnection database)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
DataTable nodeTable;
string query;
query = database.ParameterizedQueryString("Select ID, Name, CompanyID, Longitude, Latitude, Description, " +
"ImagePath, Settings, MenuData, MenuType, Master, LoadOrder, Enabled, CompanyName " +
"From NodeDetail WHERE ID = {0} ORDER BY LoadOrder", "id");
nodeTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID());
if (nodeTable.Rows.Count == 0)
return null;
DataRow row = nodeTable.Rows[0];
Node node = new Node()
{
ID = database.Guid(row, "ID"),
Name = row.Field<string>("Name"),
CompanyID = row.ConvertNullableField<int>("CompanyID"),
Longitude = row.ConvertNullableField<decimal>("Longitude"),
Latitude = row.ConvertNullableField<decimal>("Latitude"),
Description = row.Field<string>("Description"),
ImagePath = row.Field<string>("ImagePath"),
Settings = row.Field<string>("Settings"),
MenuType = row.Field<string>("MenuType"),
MenuData = row.Field<string>("MenuData"),
Master = Convert.ToBoolean(row.Field<object>("Master")),
LoadOrder = row.ConvertField<int>("LoadOrder"),
Enabled = Convert.ToBoolean(row.Field<object>("Enabled")),
m_companyName = row.Field<string>("CompanyName")
};
return node;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// 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);
ObservableCollection<Node> nodeList = new ObservableCollection<Node>();
DataTable nodeTable;
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 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);
foreach (DataRow row in nodeTable.Rows)
{
nodeList.Add(new Node()
{
ID = database.Guid(row, "ID"),
Name = row.Field<string>("Name"),
CompanyID = row.ConvertNullableField<int>("CompanyID"),
Longitude = row.ConvertNullableField<decimal>("Longitude"),
Latitude = row.ConvertNullableField<decimal>("Latitude"),
Description = row.Field<string>("Description"),
ImagePath = row.Field<string>("ImagePath"),
Settings = row.Field<string>("Settings"),
MenuType = row.Field<string>("MenuType"),
MenuData = row.Field<string>("MenuData"),
Master = Convert.ToBoolean(row.Field<object>("Master")),
LoadOrder = row.ConvertField<int>("LoadOrder"),
Enabled = Convert.ToBoolean(row.Field<object>("Enabled")),
//TimeSeriesDataServiceUrl = row.Field<string>("TimeSeriesDataServiceUrl"),
//RemoteStatusServiceUrl = row.Field<string>("RemoteStatusServiceUrl"),
//RealTimeStatisticServiceUrl = row.Field<string>("RealTimeStatisticServiceUrl"),
m_companyName = row.Field<string>("CompanyName")
});
}
}
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) || !string.IsNullOrEmpty(sortDirection))
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 specified <see cref="SecurityGroup"/> record from database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="securityGroupID">ID of the record to be deleted.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Delete(AdoDataConnection database, Guid securityGroupID)
{
bool createdConnection = false;
string securityGroupName;
try
{
createdConnection = CreateConnection(ref database);
// Get the name of the security group to be deleted
securityGroupName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM SecurityGroup WHERE ID = {0}", "userAccountID"), DefaultTimeout, database.Guid(securityGroupID)).ToNonNullString();
// Setup current user context for any delete triggers
CommonFunctions.SetCurrentUserContext(database);
// Delete the security group from the database
database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM SecurityGroup WHERE ID = {0}", "securityGroupID"), DefaultTimeout, database.Guid(securityGroupID));
// Write to the event log
CommonFunctions.LogEvent(string.Format("Security group \"{0}\" deleted successfully by user \"{1}\".", securityGroupName, CommonFunctions.CurrentUser), 13);
return "Security group deleted successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Saves <see cref="SecurityGroup"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="securityGroup">Information about <see cref="SecurityGroup"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, SecurityGroup securityGroup)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (securityGroup.ID == Guid.Empty)
{
query = database.ParameterizedQueryString("INSERT INTO SecurityGroup (Name, Description, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, " +
"{2}, {3}, {4}, {5})", "name", "description", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, securityGroup.Name, securityGroup.Description.ToNotNull(),
CommonFunctions.CurrentUser, database.UtcNow(), CommonFunctions.CurrentUser, database.UtcNow());
CommonFunctions.LogEvent(string.Format("Security group \"{0}\" created successfully by user \"{1}\".", securityGroup.Name, CommonFunctions.CurrentUser), 6);
}
else
{
query = database.ParameterizedQueryString("UPDATE SecurityGroup SET Name = {0}, Description = {1}, UpdatedBy = {2}, UpdatedOn = {3} " +
"WHERE ID = {4}", "name", "description", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, securityGroup.Name, securityGroup.Description.ToNotNull(),
CommonFunctions.CurrentUser, database.UtcNow(), database.Guid(securityGroup.ID));
CommonFunctions.LogEvent(string.Format("Information about security group \"{0}\" updated successfully by user \"{1}\".", securityGroup.Name, CommonFunctions.CurrentUser), 7);
}
return "Security group information saved successfully";
}
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"), id).ToNonNullString();
securityGroupName = database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT Name FROM SecurityGroup WHERE ID = {0}", "securityGroupID"), 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}\".", userName, securityGroupName, CommonFunctions.CurrentUser), 9);
}
return "User accounts deleted from group successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Retrieves statistic measurements for output stream.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="outputStreamAcronym">Acronym of the output stream to filter data.</param>
/// <returns><see cref="ObservableCollection{T}"/> type collection of Measurement.</returns>
public static ObservableCollection<Measurement> GetOutputStatisticMeasurements(AdoDataConnection database, string outputStreamAcronym)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
ObservableCollection<Measurement> measurementList = new ObservableCollection<Measurement>();
string query = database.ParameterizedQueryString("SELECT * FROM StatisticMeasurement WHERE NodeID = {0} AND DeviceID IS NULL ORDER BY SignalReference", "nodeID");
DataTable measurementTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID());
foreach (DataRow row in measurementTable.Rows)
{
if (row.Field<string>("SignalAcronym") == "STAT") // Just one more filter.
{
bool continueProcess = false;
if (!string.IsNullOrEmpty(outputStreamAcronym))
{
if (row.Field<string>("SignalReference").StartsWith(outputStreamAcronym + "!OS"))
continueProcess = true;
}
else
{
continueProcess = true;
}
if (continueProcess)
{
measurementList.Add(new Measurement()
{
SignalID = database.Guid(row, "SignalID"),
HistorianID = row.ConvertNullableField<int>("HistorianID"),
PointID = row.ConvertField<int>("PointID"),
DeviceID = row.ConvertNullableField<int>("DeviceID"),
PointTag = row.Field<string>("PointTag"),
AlternateTag = row.Field<string>("AlternateTag"),
SignalTypeID = row.ConvertField<int>("SignalTypeID"),
PhasorSourceIndex = row.ConvertNullableField<int>("PhasorSourceIndex"),
SignalReference = row.Field<string>("SignalReference"),
Adder = row.ConvertField<double>("Adder"),
Multiplier = row.ConvertField<double>("Multiplier"),
Internal = Convert.ToBoolean(row.Field<object>("Internal")),
Subscribed = Convert.ToBoolean(row.Field<object>("Subscribed")),
Description = row.Field<string>("Description"),
Enabled = row.ConvertField<bool>("Enabled"),
m_historianAcronym = row.Field<string>("HistorianAcronym"),
m_deviceAcronym = row.Field<object>("DeviceAcronym") == null ? string.Empty : row.Field<string>("DeviceAcronym"),
m_signalName = row.Field<string>("SignalName"),
m_signalAcronym = row.Field<string>("SignalAcronym"),
m_signalSuffix = row.Field<string>("SignalTypeSuffix"),
m_phasorLabel = row.Field<string>("PhasorLabel"),
m_framesPerSecond = Convert.ToInt32(row.Field<object>("FramesPerSecond") ?? 30),
m_id = row.Field<string>("ID"),
m_companyAcronym = row.Field<object>("CompanyAcronym") == null ? string.Empty : row.Field<string>("CompanyAcronym"),
m_companyName = row.Field<object>("CompanyName") == null ? string.Empty : row.Field<string>("CompanyName"),
Selected = false
});
}
}
}
return measurementList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Loads <see cref="Measurement"/> signal IDs as an <see cref="IList{T}"/>.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="filterExpression">SQL expression by which to filter the data coming from the database.</param>
/// <param name="searchText">The text to search by.</param>
/// <param name="sortMember">The field to sort by.</param>
/// <param name="sortDirection"><c>ASC</c> or <c>DESC</c> for ascending or descending respectively.</param>
/// <returns>Collection of <see cref="Guid"/>.</returns>
/// <remarks>
/// This method does not validate <paramref name="filterExpression"/> for SQL injection.
/// Developers should validate their inputs before entering a filter expression.
/// </remarks>
public static List<Guid> LoadSignalIDs(AdoDataConnection database, string filterExpression = "", string searchText = "", string sortMember = "", string sortDirection = "")
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
List<Guid> signalIDList = new List<Guid>();
DataTable measurementTable;
string query;
object[] parameters;
string searchParam = null;
string searchQuery = string.Empty;
string sortClause = string.Empty;
if (!string.IsNullOrEmpty(searchText))
{
searchParam = string.Format("%{0}%", searchText);
if (database.IsJetEngine)
{
// Access queries do not support UPPER but are case-insensitive anyway
searchQuery = string.Format("DESCRIPTION LIKE '{0}' OR DeviceID LIKE '{0}' OR SignalReference LIKE '{0}' " +
"OR SignalAcronym LIKE '{0}' OR SignalID LIKE '{0}' OR PointTag LIKE '{0}' OR CompanyName LIKE '{0}' " +
"OR CompanyAcronym LIKE '{0}'OR DeviceAcronym LIKE '{0}' OR SignalName LIKE '{0}' OR ID LIKE '{0}'", searchParam.Replace("'", "''"));
}
else
{
searchQuery = database.ParameterizedQueryString("UPPER(DESCRIPTION) LIKE UPPER({0}) OR DeviceID LIKE {0} OR UPPER(SignalReference) LIKE UPPER({0}) " +
"OR UPPER(SignalAcronym) LIKE UPPER({0}) OR UPPER(SignalID) LIKE UPPER({0}) OR UPPER(PointTag) LIKE UPPER({0}) OR UPPER(CompanyName) LIKE UPPER({0}) " +
"OR UPPER(CompanyAcronym) LIKE UPPER({0}) OR UPPER(DeviceAcronym) LIKE UPPER({0}) OR UPPER(SignalName) LIKE UPPER({0}) OR UPPER(ID) LIKE UPPER({0})", "searchParam");
}
}
if (!string.IsNullOrEmpty(sortMember) || !string.IsNullOrEmpty(sortDirection))
sortClause = string.Format("ORDER BY {0} {1}", sortMember, sortDirection);
if (!string.IsNullOrEmpty(filterExpression) && !string.IsNullOrEmpty(searchText))
query = string.Format("SELECT SignalID FROM MeasurementDetail WHERE ({0}) AND ({1}) {2}", filterExpression, searchQuery, sortClause);
else if (!string.IsNullOrEmpty(filterExpression))
query = string.Format("SELECT SignalID FROM MeasurementDetail WHERE ({0}) {1}", filterExpression, sortClause);
else if (!string.IsNullOrEmpty(searchText))
query = string.Format("SELECT SignalID FROM MeasurementDetail WHERE ({0}) {1}", searchQuery, sortClause);
else
query = string.Format("SELECT SignalID FROM MeasurementDetail {0}", sortClause);
parameters = !string.IsNullOrEmpty(searchText) && !database.IsJetEngine ? new object[] { searchParam } : new object[0];
measurementTable = database.Connection.RetrieveData(database.AdapterType, query, parameters);
foreach (DataRow row in measurementTable.Rows)
{
signalIDList.Add(database.Guid(row, "SignalID"));
}
return signalIDList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Saves <see cref="Node"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="node">Information about <see cref="Node"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, Node node)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (node.ID == null || node.ID == Guid.Empty)
{
query = database.ParameterizedQueryString("INSERT INTO Node (Name, CompanyID, Longitude, Latitude, Description, ImagePath, Settings, MenuType, MenuData, " +
"Master, LoadOrder, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, " +
"{13}, {14}, {15})", "name", "companyID", "longitude", "latitude", "description", "imagePath", "settings", "menuType", "menuData", "master",
"loadOrder", "enabled", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, node.Name, node.CompanyID.ToNotNull(), node.Longitude.ToNotNull(), node.Latitude.ToNotNull(),
node.Description.ToNotNull(), node.ImagePath.ToNotNull(), node.Settings.ToNotNull(), node.MenuType, node.MenuData, database.Bool(node.Master), node.LoadOrder,
database.Bool(node.Enabled), CommonFunctions.CurrentUser, database.UtcNow(), CommonFunctions.CurrentUser, database.UtcNow());
}
else
{
query = string.Format("SELECT Name FROM NodeDetail WHERE ID IN ('{0}')", node.ID);
DataTable nodeTable = database.Connection.RetrieveData(database.AdapterType, query);
query = string.Format("SELECT SignalIndex FROM Statistic WHERE Source = 'System'");
DataTable systemTable = database.Connection.RetrieveData(database.AdapterType, query);
query = database.ParameterizedQueryString("UPDATE Node SET Name = {0}, CompanyID = {1}, Longitude = {2}, Latitude = {3}, " +
"Description = {4}, ImagePath = {5}, Settings = {6}, MenuType = {7}, MenuData = {8}, Master = {9}, LoadOrder = {10}, Enabled = {11}, " +
"UpdatedBy = {12}, UpdatedOn = {13} WHERE ID = {14}", "name", "companyID", "longitude", "latitude", "description", "imagePath",
"Settings", "MenuType", "MenuData", "master", "loadOrder", "enabled", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, node.Name, node.CompanyID.ToNotNull(), node.Longitude.ToNotNull(), node.Latitude.ToNotNull(),
node.Description.ToNotNull(), node.ImagePath.ToNotNull(), node.Settings.ToNotNull(), node.MenuType, node.MenuData, database.Bool(node.Master), node.LoadOrder,
database.Bool(node.Enabled), CommonFunctions.CurrentUser, database.UtcNow(), database.Guid(node.ID));
if (nodeTable.Rows.Count > 0)
{
string newNodeName = node.Name
.RemoveCharacters(c => !char.IsLetterOrDigit(c))
.Replace(' ', '_')
.ToUpper();
string oldNodeName = nodeTable.Rows[0]["Name"].ToString()
.RemoveCharacters(c => !char.IsLetterOrDigit(c))
.Replace(' ', '_')
.ToUpper();
//SystemTable is read from the database.
for (int i = 0; i < systemTable.Rows.Count; i++)
{
string signalIndex = systemTable.Rows[i]["SignalIndex"].ToString();
string pointTag = string.Format("{0}!SYSTEM:ST{1}", newNodeName, signalIndex);
string newSignalReference = string.Format("{0}!SYSTEM-ST{1}", newNodeName, signalIndex);
string oldSignalReference = string.Format("{0}!SYSTEM-ST{1}", oldNodeName, signalIndex);
query = database.ParameterizedQueryString("UPDATE Measurement SET PointTag = {0}, SignalReference = {1} WHERE SignalReference = {2}", "name", "newSignalReference", "oldSignalReference");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, pointTag, newSignalReference, oldSignalReference);
}
}
}
return "Node information saved successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Loads information about <see cref="Measurement"/> assigned to Subscriber as <see cref="ObservableCollection{T}"/> style list.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="subscriberId">ID of the Subscriber to filter data.</param>
/// <returns>Collection of <see cref="Measurement"/>.</returns>
public static ObservableCollection<Measurement> GetMeasurementsBySubscriber(AdoDataConnection database, Guid subscriberId)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
if (subscriberId == null || subscriberId == Guid.Empty)
return Load(database);
ObservableCollection<Measurement> possibleMeasurements = new ObservableCollection<Measurement>();
string query = database.ParameterizedQueryString("SELECT * FROM MeasurementDetail WHERE SignalID NOT IN " +
"(SELECT SignalID FROM SubscriberMeasurement WHERE SubscriberID = {0}) ORDER BY PointTag", "subscriberID");
DataTable possibleMeasurementTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Guid(subscriberId));
foreach (DataRow row in possibleMeasurementTable.Rows)
{
possibleMeasurements.Add(new Measurement()
{
SignalID = database.Guid(row, "SignalID"),
HistorianID = row.ConvertNullableField<int>("HistorianID"),
PointID = row.ConvertField<int>("PointID"),
DeviceID = row.ConvertNullableField<int>("DeviceID"),
PointTag = row.Field<string>("PointTag"),
AlternateTag = row.Field<string>("AlternateTag"),
SignalTypeID = row.ConvertField<int>("SignalTypeID"),
PhasorSourceIndex = row.ConvertNullableField<int>("PhasorSourceIndex"),
SignalReference = row.Field<string>("SignalReference"),
Adder = row.ConvertField<double>("Adder"),
Multiplier = row.ConvertField<double>("Multiplier"),
Internal = Convert.ToBoolean(row.Field<object>("Internal")),
Subscribed = Convert.ToBoolean(row.Field<object>("Subscribed")),
Description = row.Field<string>("Description"),
Enabled = Convert.ToBoolean(row.Field<object>("Enabled")),
m_historianAcronym = row.Field<string>("HistorianAcronym"),
m_deviceAcronym = row.Field<object>("DeviceAcronym") == null ? string.Empty : row.Field<string>("DeviceAcronym"),
m_signalName = row.Field<string>("SignalName"),
m_signalAcronym = row.Field<string>("SignalAcronym"),
m_signalSuffix = row.Field<string>("SignalTypeSuffix"),
m_phasorLabel = row.Field<string>("PhasorLabel"),
m_id = row.Field<string>("ID"),
m_companyAcronym = row.Field<object>("CompanyAcronym") == null ? string.Empty : row.Field<string>("CompanyAcronym"),
m_companyName = row.Field<object>("CompanyName") == null ? string.Empty : row.Field<string>("CompanyName"),
Selected = false
});
}
return possibleMeasurements;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Deletes specified <see cref="Node"/> record from database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="nodeID">ID of the record to be deleted.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Delete(AdoDataConnection database, Guid nodeID)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
// Setup current user context for any delete triggers
CommonFunctions.SetCurrentUserContext(database);
// Before we delete node, make sure to delete all the devices associated with this node.
// All other node related items get deleted by cascade delete.
database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM Device WHERE NodeID = {0}", "nodeID"), DefaultTimeout, database.Guid(nodeID));
database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM Node WHERE ID = {0}", "nodeID"), DefaultTimeout, database.Guid(nodeID));
return "Node deleted successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Retrieves only subscribed <see cref="Measurement"/> collection.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <returns><see cref="ObservableCollection{T1}"/> type list of <see cref="Measurement"/>.</returns>
public static ObservableCollection<Measurement> GetSubscribedMeasurements(AdoDataConnection database)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
ObservableCollection<Measurement> measurementList = new ObservableCollection<Measurement>();
DataTable measurementTable;
string query;
query = database.ParameterizedQueryString("SELECT * FROM MeasurementDetail WHERE " +
"Subscribed = {0} ORDER BY PointTag", "subscribed");
measurementTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Bool(true));
foreach (DataRow row in measurementTable.Rows)
{
measurementList.Add(new Measurement()
{
SignalID = database.Guid(row, "SignalID"),
HistorianID = row.ConvertNullableField<int>("HistorianID"),
PointID = row.ConvertField<int>("PointID"),
DeviceID = row.ConvertNullableField<int>("DeviceID"),
PointTag = row.Field<string>("PointTag"),
AlternateTag = row.Field<string>("AlternateTag"),
SignalTypeID = row.ConvertField<int>("SignalTypeID"),
PhasorSourceIndex = row.ConvertNullableField<int>("PhasorSourceIndex"),
SignalReference = row.Field<string>("SignalReference"),
Adder = row.ConvertField<double>("Adder"),
Multiplier = row.ConvertField<double>("Multiplier"),
Internal = Convert.ToBoolean(row.Field<object>("Internal")),
Subscribed = Convert.ToBoolean(row.Field<object>("Subscribed")),
Description = row.Field<string>("Description"),
Enabled = Convert.ToBoolean(row.Field<object>("Enabled")),
m_historianAcronym = row.Field<string>("HistorianAcronym"),
m_deviceAcronym = row.Field<object>("DeviceAcronym") == null ? string.Empty : row.Field<string>("DeviceAcronym"),
m_signalName = row.Field<string>("SignalName"),
m_signalAcronym = row.Field<string>("SignalAcronym"),
m_signalSuffix = row.Field<string>("SignalTypeSuffix"),
m_phasorLabel = row.Field<string>("PhasorLabel"),
m_framesPerSecond = Convert.ToInt32(row.Field<object>("FramesPerSecond") ?? 30),
m_id = row.Field<string>("ID"),
m_companyAcronym = row.Field<object>("CompanyAcronym") == null ? string.Empty : row.Field<string>("CompanyAcronym"),
m_companyName = row.Field<object>("CompanyName") == null ? string.Empty : row.Field<string>("CompanyName"),
Selected = false
});
}
return measurementList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
// Input keys can use DataSource for filtering desired set of input or output measurements
// based on any table and fields in the data set by using a filter expression instead of
// a list of measurement ID's. The format is as follows:
// FILTER <TableName> WHERE <Expression> [ORDER BY <SortField>]
// Source tables are expected to have at least the following fields:
//
// ID NVARCHAR Measurement key formatted as: ArchiveSource:PointID
// SignalID GUID Unique identification for measurement
// PointTag NVARCHAR Point tag of measurement
// Adder FLOAT Adder to apply to value, if any (default to 0.0)
// Multiplier FLOAT Multipler to apply to value, if any (default to 1.0)
//
// Could have used standard SQL syntax here but didn't want to give user the impression
// that this a standard SQL expression when it isn't - so chose the word FILTER to make
// consumer was aware that this was not SQL, but SQL "like". The WHERE clause expression
// uses standard SQL syntax (it is simply the DataTable.Select filter expression).
/// <summary>
/// Parses input measurement keys from connection string setting.
/// </summary>
/// <param name="dataSource">The <see cref="DataSet"/> used to define input measurement keys.</param>
/// <param name="value">Value of setting used to define input measurement keys, typically "inputMeasurementKeys".</param>
/// <param name="measurementTable">Measurement table name used to load additional meta-data; this is not used when specifying a FILTER expression.</param>
/// <returns>User selected input measurement keys.</returns>
public static MeasurementKey[] ParseInputMeasurementKeys(DataSet dataSource, string value, string measurementTable = "ActiveMeasurements")
{
List<MeasurementKey> keys = new List<MeasurementKey>();
MeasurementKey key;
Guid id;
Match filterMatch = null;
bool dataSourceAvailable = ((object)dataSource != null);
value = value.Trim();
if (!string.IsNullOrWhiteSpace(value))
{
if (dataSourceAvailable)
{
lock (s_filterExpression)
{
filterMatch = s_filterExpression.Match(value);
}
}
if (dataSourceAvailable && filterMatch.Success)
{
string tableName = filterMatch.Result("${TableName}").Trim();
string expression = filterMatch.Result("${Expression}").Trim();
string sortField = filterMatch.Result("${SortField}").Trim();
string maxRows = filterMatch.Result("${MaxRows}").Trim();
int takeCount;
if (string.IsNullOrEmpty(maxRows) || !int.TryParse(maxRows, out takeCount))
takeCount = int.MaxValue;
foreach (DataRow row in dataSource.Tables[tableName].Select(expression, sortField).Take(takeCount))
{
if (MeasurementKey.TryParse(row["ID"].ToString(), row["SignalID"].ToNonNullString(Guid.Empty.ToString()).ConvertToType<Guid>(), out key))
keys.Add(key);
}
}
else if (value.StartsWith("SELECT ", StringComparison.InvariantCultureIgnoreCase))
{
try
{
using (AdoDataConnection database = new AdoDataConnection("systemSettings"))
{
DataTable results = database.Connection.RetrieveData(database.AdapterType, value);
foreach (DataRow row in results.Rows)
{
if (MeasurementKey.TryParse(row["ID"].ToString(), row["SignalID"].ToNonNullString(Guid.Empty.ToString()).ConvertToType<Guid>(), out key))
keys.Add(key);
}
}
}
catch (Exception ex)
{
throw new InvalidOperationException(string.Format("Could not parse input measurement definition from select statement \"{0}\": {1}", value, ex.Message), ex);
}
}
else
{
// Add manually defined measurement keys
foreach (string item in value.Split(';'))
{
if (!string.IsNullOrWhiteSpace(item))
{
if (MeasurementKey.TryParse(item, Guid.Empty, out key))
{
// Attempt to update empty signal ID if available
if (dataSourceAvailable && key.SignalID == Guid.Empty)
{
if (dataSource.Tables.Contains(measurementTable))
{
DataRow[] filteredRows = dataSource.Tables[measurementTable].Select(string.Format("ID = '{0}'", key.ToString()));
if (filteredRows.Length > 0)
key.SignalID = filteredRows[0]["SignalID"].ToNonNullString(Guid.Empty.ToString()).ConvertToType<Guid>();
}
}
keys.Add(key);
}
else if (Guid.TryParse(item, out id))
{
if (dataSourceAvailable && dataSource.Tables.Contains(measurementTable))
{
DataRow[] filteredRows = dataSource.Tables[measurementTable].Select(string.Format("SignalID = '{0}'", id));
if (filteredRows.Length > 0 && MeasurementKey.TryParse(filteredRows[0]["ID"].ToString(), id, out key))
keys.Add(key);
}
else
{
keys.Add(MeasurementKey.LookupBySignalID(id));
}
}
else
{
// Attempt to update empty signal ID if available
if (dataSourceAvailable && dataSource.Tables.Contains(measurementTable))
{
DataRow[] filteredRows = dataSource.Tables[measurementTable].Select(string.Format("PointTag = '{0}'", item));
if (filteredRows.Length > 0)
{
key = MeasurementKey.LookupBySignalID(filteredRows[0]["SignalID"].ToNonNullString(Guid.Empty.ToString()).ConvertToType<Guid>());
keys.Add(key);
}
}
if (key == default(MeasurementKey))
{
throw new InvalidOperationException(string.Format("Could not parse input measurement definition \"{0}\" as a filter expression, measurement key, point tag or Guid", item));
}
}
}
}
}
}
return keys.ToArray();
}
/// <summary>
/// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="SignalType"/> 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>
public static Dictionary<int, string> GetLookupList(AdoDataConnection database, bool isOptional = false)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
Dictionary<int, string> signalTypeList = new Dictionary<int, string>();
DataTable signalTypeTable;
if (isOptional)
signalTypeList.Add(0, "Select SignalType");
signalTypeTable = database.Connection.RetrieveData(database.AdapterType, "SELECT ID, Name FROM SignalType ORDER BY Name");
foreach (DataRow row in signalTypeTable.Rows)
signalTypeList[row.ConvertField<int>("ID")] = row.Field<string>("Name");
return signalTypeList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Parses output measurements from connection string setting.
/// </summary>
/// <param name="dataSource">The <see cref="DataSet"/> used to define output measurements.</param>
/// <param name="value">Value of setting used to define output measurements, typically "outputMeasurements".</param>
/// <param name="measurementTable">Measurement table name used to load additional meta-data; this is not used when specifying a FILTER expression.</param>
/// <returns>User selected output measurements.</returns>
public static IMeasurement[] ParseOutputMeasurements(DataSet dataSource, string value, string measurementTable = "ActiveMeasurements")
{
List<IMeasurement> measurements = new List<IMeasurement>();
Measurement measurement;
MeasurementKey key;
Match filterMatch = null;
Guid id;
bool dataSourceAvailable = ((object)dataSource != null);
value = value.Trim();
if (!string.IsNullOrWhiteSpace(value))
{
if (dataSourceAvailable)
{
lock (s_filterExpression)
{
filterMatch = s_filterExpression.Match(value);
}
}
if (dataSourceAvailable && filterMatch.Success)
{
string tableName = filterMatch.Result("${TableName}").Trim();
string expression = filterMatch.Result("${Expression}").Trim();
string sortField = filterMatch.Result("${SortField}").Trim();
string maxRows = filterMatch.Result("${MaxRows}").Trim();
int takeCount;
if (string.IsNullOrEmpty(maxRows) || !int.TryParse(maxRows, out takeCount))
takeCount = int.MaxValue;
foreach (DataRow row in dataSource.Tables[tableName].Select(expression, sortField).Take(takeCount))
{
id = row["SignalID"].ToNonNullString(Guid.Empty.ToString()).ConvertToType<Guid>();
measurement = new Measurement()
{
ID = id,
Key = MeasurementKey.Parse(row["ID"].ToString(), id),
TagName = row["PointTag"].ToNonNullString(),
Adder = double.Parse(row["Adder"].ToString()),
Multiplier = double.Parse(row["Multiplier"].ToString())
};
measurements.Add(measurement);
}
}
else if (value.StartsWith("SELECT ", StringComparison.InvariantCultureIgnoreCase))
{
try
{
using (AdoDataConnection database = new AdoDataConnection("systemSettings"))
{
DataTable results = database.Connection.RetrieveData(database.AdapterType, value);
foreach (DataRow row in results.Rows)
{
id = row["SignalID"].ToNonNullString(Guid.Empty.ToString()).ConvertToType<Guid>();
measurement = new Measurement
{
ID = id,
Key = MeasurementKey.Parse(row["ID"].ToString(), id),
TagName = row["PointTag"].ToNonNullString(),
Adder = double.Parse(row["Adder"].ToString()),
Multiplier = double.Parse(row["Multiplier"].ToString())
};
measurements.Add(measurement);
}
}
}
catch (Exception ex)
{
throw new InvalidOperationException(string.Format("Could not parse output measurement definition from select statement \"{0}\": {1}", value, ex.Message), ex);
}
}
else
{
string[] elem;
double adder, multipler;
foreach (string item in value.Split(';'))
{
if (!string.IsNullOrWhiteSpace(item))
{
elem = item.Trim().Split(',');
if (!MeasurementKey.TryParse(elem[0], Guid.Empty, out key))
{
if (Guid.TryParse(item, out id))
{
if (dataSourceAvailable && dataSource.Tables.Contains(measurementTable))
{
DataRow[] filteredRows = dataSource.Tables[measurementTable].Select(string.Format("SignalID = '{0}'", id));
if (filteredRows.Length > 0)
MeasurementKey.TryParse(filteredRows[0]["ID"].ToString(), id, out key);
}
else
{
key = MeasurementKey.LookupBySignalID(id);
}
}
else
{
// Attempt to update empty signal ID if available
if (dataSourceAvailable && dataSource.Tables.Contains(measurementTable))
{
DataRow[] filteredRows = dataSource.Tables[measurementTable].Select(string.Format("PointTag = '{0}'", item));
if (filteredRows.Length > 0)
{
key = MeasurementKey.LookupBySignalID(filteredRows[0]["SignalID"].ToNonNullString(Guid.Empty.ToString()).ConvertToType<Guid>());
}
}
if (key == default(MeasurementKey))
{
throw new InvalidOperationException(string.Format("Could not parse output measurement definition \"{0}\" as a filter expression, measurement key, point tag or Guid", item));
}
}
}
// Adder and multipler may be optionally specified
if (elem.Length > 1)
{
if (!double.TryParse(elem[1].Trim(), out adder))
adder = 0.0D;
}
else
{
adder = 0.0D;
}
if (elem.Length > 2)
{
if (!double.TryParse(elem[2].Trim(), out multipler))
multipler = 1.0D;
}
else
{
multipler = 1.0D;
}
// Create a new measurement for the provided field level information
measurement = new Measurement()
{
ID = key.SignalID,
Key = key,
Adder = adder,
Multiplier = multipler
};
// Attempt to lookup other associated measurement meta-data from default measurement table, if defined
try
{
if (dataSourceAvailable && dataSource.Tables.Contains(measurementTable))
{
DataRow[] filteredRows = dataSource.Tables[measurementTable].Select(string.Format("ID = '{0}'", key.ToString()));
if (filteredRows.Length > 0)
{
DataRow row = filteredRows[0];
measurement.ID = row["SignalID"].ToNonNullString(Guid.Empty.ToString()).ConvertToType<Guid>();
measurement.TagName = row["PointTag"].ToNonNullString();
// Attempt to update empty signal ID if available
if (measurement.Key.SignalID == Guid.Empty)
measurement.Key.UpdateSignalID(measurement.ID);
// Manually specified adder and multiplier take precedence, but if none were specified,
// then those defined in the meta-data are used instead
if (elem.Length < 3)
measurement.Multiplier = double.Parse(row["Multiplier"].ToString());
if (elem.Length < 2)
measurement.Adder = double.Parse(row["Adder"].ToString());
}
}
}
catch
{
// Errors here are not catastrophic, this simply limits the available meta-data
measurement.TagName = string.Empty;
}
measurements.Add(measurement);
}
}
}
}
return measurements.ToArray();
}
/// <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>
/// <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);
ObservableCollection<Historian> historianList = new ObservableCollection<Historian>();
DataTable historianTable;
string query;
string commaSeparatedKeys;
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());
foreach (DataRow row in historianTable.Rows)
{
historianList.Add(new Historian()
{
NodeID = database.Guid(row, "NodeID"),
ID = row.ConvertField<int>("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 historianList;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Saves <see cref="Measurement"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="measurement">Information about <see cref="Measurement"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, Measurement measurement)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (measurement.PointID == 0)
{
query = database.ParameterizedQueryString("INSERT INTO Measurement (HistorianID, DeviceID, PointTag, AlternateTag, SignalTypeID, PhasorSourceIndex, " +
"SignalReference, Adder, Multiplier, Subscribed, Internal, Description, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, " +
"{3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15}, {16})", "historianID", "deviceID", "pointTag", "alternateTag", "signalTypeID",
"phasorSourceIndex", "signalReference", "adder", "multiplier", "subscribed", "internal", "description", "enabled", "updatedBy", "updatedOn",
"createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, measurement.HistorianID.ToNotNull(), measurement.DeviceID.ToNotNull(), measurement.PointTag,
measurement.AlternateTag.ToNotNull(), measurement.SignalTypeID, measurement.PhasorSourceIndex ?? measurement.PhasorSourceIndex.ToNotNull(), measurement.SignalReference,
measurement.Adder, measurement.Multiplier, database.Bool(measurement.Subscribed), database.Bool(measurement.Internal), measurement.Description.ToNotNull(),
database.Bool(measurement.Enabled), CommonFunctions.CurrentUser, database.UtcNow(), CommonFunctions.CurrentUser, database.UtcNow());
}
else
{
query = database.ParameterizedQueryString("Update Measurement Set HistorianID = {0}, DeviceID = {1}, PointTag = {2}, AlternateTag = {3}, " +
"SignalTypeID = {4}, PhasorSourceIndex = {5}, SignalReference = {6}, Adder = {7}, Multiplier = {8}, Description = {9}, Subscribed = {10}, " +
"Internal = {11}, Enabled = {12}, UpdatedBy = {13}, UpdatedOn = {14} Where PointID = {15}", "historianID", "deviceID", "pointTag",
"alternateTag", "signalTypeID", "phasorSourceINdex", "signalReference", "adder", "multiplier", "description", "subscribed", "internal",
"enabled", "updatedBy", "updatedOn", "pointID");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, measurement.HistorianID.ToNotNull(), measurement.DeviceID.ToNotNull(), measurement.PointTag,
measurement.AlternateTag.ToNotNull(), measurement.SignalTypeID, measurement.PhasorSourceIndex ?? measurement.PhasorSourceIndex.ToNotNull(), measurement.SignalReference,
measurement.Adder, measurement.Multiplier, measurement.Description.ToNotNull(), database.Bool(measurement.Subscribed), database.Bool(measurement.Internal),
database.Bool(measurement.Enabled), CommonFunctions.CurrentUser, database.UtcNow(), measurement.PointID);
}
return "Measurement information saved successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Saves <see cref="Historian"/> information to database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="historian">Infomration about <see cref="Historian"/>.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Save(AdoDataConnection database, Historian historian)
{
bool createdConnection = false;
string query;
try
{
createdConnection = CreateConnection(ref database);
if (historian.ID == 0)
{
query = database.ParameterizedQueryString("INSERT INTO Historian (NodeID, Acronym, Name, AssemblyName, TypeName, ConnectionString, IsLocal, " +
"MeasurementReportingInterval, Description, LoadOrder, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, " +
"{5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14})", "nodeID", "acronym", "name", "assemblyName", "typeName", "connectionString", "isLocal",
"measurementReportingInterval", "description", "loadOrder", "enabled", "updatedBy", "updatedOn", "createdBy", "createdOn");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, (historian.NodeID != Guid.Empty) ? database.Guid(historian.NodeID) : database.CurrentNodeID(),
historian.Acronym.Replace(" ", "").ToUpper(), historian.Name.ToNotNull(), historian.AssemblyName.ToNotNull(), historian.TypeName.ToNotNull(),
historian.ConnectionString.ToNotNull(), database.Bool(historian.IsLocal), historian.MeasurementReportingInterval, historian.Description.ToNotNull(),
historian.LoadOrder, database.Bool(historian.Enabled), CommonFunctions.CurrentUser, database.UtcNow(),
CommonFunctions.CurrentUser, database.UtcNow());
}
else
{
query = database.ParameterizedQueryString("UPDATE Historian SET NodeID = {0}, Acronym = {1}, Name = {2}, AssemblyName = {3}, TypeName = {4}, " +
"ConnectionString = {5}, IsLocal = {6}, MeasurementReportingInterval = {7}, Description = {8}, LoadOrder = {9}, Enabled = {10}, " +
"UpdatedBy = {11}, UpdatedOn = {12} WHERE ID = {13}", "nodeID", "acronym", "name", "assemblyName", "typeName", "connectionString",
"isLocal", "measurementReportingInterval", "description", "loadOrder", "enabled", "updatedBy", "updatedOn", "id");
database.Connection.ExecuteNonQuery(query, DefaultTimeout, (historian.NodeID != Guid.Empty) ? database.Guid(historian.NodeID) : database.CurrentNodeID(),
historian.Acronym.Replace(" ", "").ToUpper(), historian.Name.ToNotNull(), historian.AssemblyName.ToNotNull(), historian.TypeName.ToNotNull(),
historian.ConnectionString.ToNotNull(), database.Bool(historian.IsLocal), historian.MeasurementReportingInterval, historian.Description.ToNotNull(),
historian.LoadOrder, database.Bool(historian.Enabled), CommonFunctions.CurrentUser, database.UtcNow(), historian.ID);
}
return "Historian information saved successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Deletes specified <see cref="Measurement"/> record from database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="signalID">ID of the record to be deleted.</param>
/// <returns>String, for display use, indicating success.</returns>
public static string Delete(AdoDataConnection database, Guid signalID)
{
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 Measurement WHERE SignalID = {0}", "signalID"), DefaultTimeout, database.Guid(signalID));
return "Measurement deleted successfully";
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Retrieves a <see cref="Historian"/> information from the database.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="whereClause">Filter expression to filter data.</param>
/// <returns><see cref="Historian"/> information.</returns>
public static Historian GetHistorian(AdoDataConnection database, string whereClause)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
DataTable historianTable = database.Connection.RetrieveData(database.AdapterType, "SELECT * FROM HistorianDetail " + whereClause);
if (historianTable.Rows.Count == 0)
return null;
DataRow row = historianTable.Rows[0];
Historian historian = new Historian()
{
NodeID = database.Guid(row, "NodeID"),
ID = row.ConvertField<int>("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 historian;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
/// <summary>
/// Retrieves a <see cref="Measurement"/> information from the database based on query string filter.
/// </summary>
/// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <param name="whereClause"><see cref="AdoDataConnection"/> to connection to database.</param>
/// <returns><see cref="Measurement"/> information.</returns>
public static Measurement GetMeasurement(AdoDataConnection database, string whereClause)
{
bool createdConnection = false;
try
{
createdConnection = CreateConnection(ref database);
DataTable measurementTable = database.Connection.RetrieveData(database.AdapterType, "SELECT * FROM MeasurementDetail " + whereClause);
if (measurementTable.Rows.Count == 0)
return null;
DataRow row = measurementTable.Rows[0];
Measurement measurement = new Measurement()
{
SignalID = database.Guid(row, "SignalID"),
HistorianID = row.ConvertNullableField<int>("HistorianID"),
PointID = row.ConvertField<int>("PointID"),
DeviceID = row.ConvertNullableField<int>("DeviceID"),
PointTag = row.Field<string>("PointTag"),
AlternateTag = row.Field<string>("AlternateTag"),
SignalTypeID = row.ConvertField<int>("SignalTypeID"),
PhasorSourceIndex = row.ConvertNullableField<int>("PhasorSourceIndex"),
SignalReference = row.Field<string>("SignalReference"),
Adder = row.ConvertField<double>("Adder"),
Multiplier = row.ConvertField<double>("Multiplier"),
Description = row.Field<string>("Description"),
Enabled = Convert.ToBoolean(row.Field<object>("Enabled")),
m_historianAcronym = row.Field<string>("HistorianAcronym"),
m_deviceAcronym = row.Field<object>("DeviceAcronym") == null ? string.Empty : row.Field<string>("DeviceAcronym"),
m_signalName = row.Field<string>("SignalName"),
m_signalAcronym = row.Field<string>("SignalAcronym"),
m_signalSuffix = row.Field<string>("SignalTypeSuffix"),
m_phasorLabel = row.Field<string>("PhasorLabel"),
m_framesPerSecond = Convert.ToInt32(row.Field<object>("FramesPerSecond") ?? 30),
m_id = row.Field<string>("ID"),
m_companyAcronym = row.Field<object>("CompanyAcronym") == null ? string.Empty : row.Field<string>("CompanyAcronym"),
m_companyName = row.Field<object>("CompanyName") == null ? string.Empty : row.Field<string>("CompanyName"),
Selected = false
};
return measurement;
}
finally
{
if (createdConnection && database != null)
database.Dispose();
}
}
