private static CustomActionAdapter GetOrAddActionAdapter(TableOperations <CustomActionAdapter> actionAdapterTable, string tielineID, out bool newAdd)
{
string adapterName = $"IMPEDANCE_{tielineID}_CALC";
CustomActionAdapter actionAdapter = actionAdapterTable.QueryRecordWhere("NodeID = {0} AND AdapterName = {1}", nodeID, adapterName);
if ((object)actionAdapter == null)
{
actionAdapter = actionAdapterTable.NewRecord();
actionAdapter.NodeID = nodeID;
actionAdapter.AdapterName = adapterName;
actionAdapter.AssemblyName = AssemblyName;
actionAdapter.TypeName = TypeName;
actionAdapterTable.AddNewRecord(actionAdapter);
// Re-query newly added record to get auto-increment ID
actionAdapter = actionAdapterTable.QueryRecordWhere("NodeID = {0} AND AdapterName = {1}", nodeID, adapterName);
newAdd = true;
if ((object)actionAdapter == null)
{
throw new InvalidOperationException($"Failed to lookup CustomActionAdapter record with AdapterName of \"{adapterName}\".");
}
}
else
{
newAdd = false;
}
return(actionAdapter);
}
public void ValidateCalculatorConfigurations(int?historianID, string systemName)
{
const int Avg = 0, Max = 1, Min = 2;
PowerCalculationConfigurationValidation.ValidateDatabaseDefinitions();
TableOperations <Measurement> measurementTable = DataContext.Table <Measurement>();
string frequencyDeviceName = string.Format(SystemFrequencyDeviceName, systemName);
// Look for existing frequency average
if (measurementTable.QueryRecordCountWhere($"SignalReference = '{SignalReference.ToString(frequencyDeviceName, SignalKind.Frequency)}'") > 0)
{
return;
}
TableOperations <CustomActionAdapter> customActionAdapterTable = DataContext.Table <CustomActionAdapter>();
CustomActionAdapter avgFreqAdapter = customActionAdapterTable.QueryRecordWhere("TypeName = {0}", typeof(PowerCalculations.AverageFrequency).FullName) ?? NewCustomActionAdapter();
Measurement[] measurements = GetCalculatedFrequencyMeasurements(historianID, systemName, frequencyDeviceName);
double lagTime = DefaultCalculationLagTime;
// Reduce lag-time since dynamic calculations can depend on average frequency
lagTime -= lagTime > 1.0 ? 1.0 : 0.5;
if (lagTime < 0.1)
{
lagTime = 0.1;
}
avgFreqAdapter.AdapterName = "PHASOR!AVERAGEFREQ";
avgFreqAdapter.AssemblyName = "PowerCalculations.dll";
avgFreqAdapter.TypeName = typeof(PowerCalculations.AverageFrequency).FullName;
avgFreqAdapter.ConnectionString = $"InputMeasurementKeys={{FILTER ActiveMeasurements WHERE SignalType = 'FREQ' AND SignalReference NOT LIKE '{frequencyDeviceName}%'}}; OutputMeasurements={{{measurements[Avg].SignalID};{measurements[Max].SignalID};{measurements[Min].SignalID}}}; LagTime={lagTime}; LeadTime={DefaultCalculationLeadTime}; FramesPerSecond={DefaultCalculationFramesPerSecond}";
avgFreqAdapter.Enabled = true;
customActionAdapterTable.AddNewOrUpdateRecord(avgFreqAdapter);
}
public void AddNewOrUpdateCustomActionAdapter(CustomActionAdapter customActionAdapter)
{
TableOperations <CustomActionAdapter> customActionAdapterTable = DataContext.Table <CustomActionAdapter>();
if (customActionAdapterTable.QueryRecordCountWhere("AdapterName = {0}", customActionAdapter.AdapterName) == 0)
{
AddNewCustomActionAdapter(customActionAdapter);
}
else
{
CustomActionAdapter existingActionAdapter = customActionAdapterTable.QueryRecordWhere("AdapterName = {0}", customActionAdapter.AdapterName);
existingActionAdapter.AssemblyName = customActionAdapter.AssemblyName;
existingActionAdapter.TypeName = customActionAdapter.TypeName;
existingActionAdapter.ConnectionString = customActionAdapter.ConnectionString;
existingActionAdapter.LoadOrder = customActionAdapter.LoadOrder;
existingActionAdapter.Enabled = customActionAdapter.Enabled;
existingActionAdapter.UpdatedBy = customActionAdapter.UpdatedBy;
existingActionAdapter.UpdatedOn = customActionAdapter.UpdatedOn;
UpdateCustomActionAdapter(existingActionAdapter);
}
}
public void UpdateCustomActionAdapter(CustomActionAdapter customActionAdapter)
{
DataContext.Table <CustomActionAdapter>().UpdateRecord(customActionAdapter);
}
public void AddNewCustomActionAdapter(CustomActionAdapter customActionAdapter)
{
DataContext.Table <CustomActionAdapter>().AddNewRecord(customActionAdapter);
}
private void SetupGrafanaHostingAdapter()
{
try
{
const string GrafanaProcessAdapterName = "GRAFANA!PROCESS";
const string DefaultGrafanaServerPath = GrafanaAuthProxyController.DefaultServerPath;
const string GrafanaAdminRoleName = GrafanaAuthProxyController.GrafanaAdminRoleName;
const string GrafanaAdminRoleDescription = "Grafana Administrator Role";
// Access needed settings from specified categories in configuration file
CategorizedSettingsElementCollection systemSettings = ConfigurationFile.Current.Settings["systemSettings"];
CategorizedSettingsElementCollection grafanaHosting = ConfigurationFile.Current.Settings["grafanaHosting"];
string newNodeID = Guid.NewGuid().ToString();
// Make sure needed settings exist
systemSettings.Add("NodeID", newNodeID, "Unique Node ID");
grafanaHosting.Add("ServerPath", DefaultGrafanaServerPath, "Defines the path to the Grafana server to host - set to empty string to disable hosting.");
// Get settings as currently defined in configuration file
Guid nodeID = Guid.Parse(systemSettings["NodeID"].ValueAs(newNodeID));
string grafanaServerPath = grafanaHosting["ServerPath"].ValueAs(DefaultGrafanaServerPath);
// Only enable adapter if file path to configured Grafana server executable is accessible
bool enabled = File.Exists(FilePath.GetAbsolutePath(grafanaServerPath));
// Open database connection as defined in configuration file "systemSettings" category
using (AdoDataConnection connection = new AdoDataConnection("systemSettings"))
{
// Make sure Grafana process adapter exists
TableOperations <CustomActionAdapter> actionAdapterTable = new TableOperations <CustomActionAdapter>(connection);
CustomActionAdapter actionAdapter = actionAdapterTable.QueryRecordWhere("AdapterName = {0}", GrafanaProcessAdapterName) ?? actionAdapterTable.NewRecord();
// Update record fields
actionAdapter.NodeID = nodeID;
actionAdapter.AdapterName = GrafanaProcessAdapterName;
actionAdapter.AssemblyName = "FileAdapters.dll";
actionAdapter.TypeName = "FileAdapters.ProcessLauncher";
actionAdapter.Enabled = enabled;
// Define default adapter connection string if none is defined
if (string.IsNullOrWhiteSpace(actionAdapter.ConnectionString))
{
actionAdapter.ConnectionString =
$"FileName={DefaultGrafanaServerPath}; " +
$"WorkingDirectory={FilePath.GetAbsolutePath("Grafana")}; " +
"ForceKillOnDispose=True; " +
"ProcessOutputAsLogMessages=True; " +
"LogMessageTextExpression={(?<=.*msg\\s*\\=\\s*\\\")[^\\\"]*(?=\\\")|(?<=.*file\\s*\\=\\s*\\\")[^\\\"]*(?=\\\")|(?<=.*file\\s*\\=\\s*)[^\\s]*(?=s|$)|(?<=.*path\\s*\\=\\s*\\\")[^\\\"]*(?=\\\")|(?<=.*path\\s*\\=\\s*)[^\\s]*(?=s|$)|(?<=.*error\\s*\\=\\s*\\\")[^\\\"]*(?=\\\")|(?<=.*reason\\s*\\=\\s*\\\")[^\\\"]*(?=\\\")|(?<=.*id\\s*\\=\\s*\\\")[^\\\"]*(?=\\\")|(?<=.*version\\s*\\=\\s*)[^\\s]*(?=\\s|$)|(?<=.*dbtype\\s*\\=\\s*)[^\\s]*(?=\\s|$)|(?<=.*)commit\\s*\\=\\s*[^\\s]*(?=\\s|$)|(?<=.*)compiled\\s*\\=\\s*[^\\s]*(?=\\s|$)|(?<=.*)address\\s*\\=\\s*[^\\s]*(?=\\s|$)|(?<=.*)protocol\\s*\\=\\s*[^\\s]*(?=\\s|$)|(?<=.*)subUrl\\s*\\=\\s*[^\\s]*(?=\\s|$)|(?<=.*)code\\s*\\=\\s*[^\\s]*(?=\\s|$)|(?<=.*name\\s*\\=\\s*)[^\\s]*(?=\\s|$)}; " +
"LogMessageLevelExpression={(?<=.*lvl\\s*\\=\\s*)[^\\s]*(?=\\s|$)}; " +
"LogMessageLevelMappings={info=Info; warn=Waning; error=Error; critical=Critical; debug=Debug}";
}
// Preserve connection string on existing records except for Grafana server executable path that comes from configuration file
Dictionary <string, string> settings = actionAdapter.ConnectionString.ParseKeyValuePairs();
settings["FileName"] = grafanaServerPath;
actionAdapter.ConnectionString = settings.JoinKeyValuePairs();
// Save record updates
actionAdapterTable.AddNewOrUpdateRecord(actionAdapter);
// Make sure Grafana admin role exists
TableOperations <ApplicationRole> applicationRoleTable = new TableOperations <ApplicationRole>(connection);
ApplicationRole applicationRole = applicationRoleTable.QueryRecordWhere("Name = {0} AND NodeID = {1}", GrafanaAdminRoleName, nodeID);
if ((object)applicationRole == null)
{
applicationRole = applicationRoleTable.NewRecord();
applicationRole.NodeID = nodeID;
applicationRole.Name = GrafanaAdminRoleName;
applicationRole.Description = GrafanaAdminRoleDescription;
applicationRoleTable.AddNewRecord(applicationRole);
}
}
}
catch (Exception ex)
{
LogPublisher log = Logger.CreatePublisher(typeof(ServiceHost), MessageClass.Application);
log.Publish(MessageLevel.Error, "Error Message", "Failed to setup Grafana hosting adapter", null, ex);
}
}
static int Main()
{
int row = 0;
try
{
Arguments args = new Arguments(Environment.CommandLine, true);
string sourceApp, sourceDevice, setting;
double lagTime, leadTime;
int framesPerSecond, successes = 0, failures = 0, simpleFormattedRecords = 0, explicitFormattedRecords = 0;
bool skipFirstRow;
if (args.Count != RequiredArgumentCount)
{
throw new ArgumentException($"Expected {RequiredArgumentCount} argument, received {args.Count}.");
}
if (!args.TryGetValue("sourceApp", out sourceApp))
{
sourceApp = DefaultSourceApp;
}
if (!args.TryGetValue("sourceDevice", out sourceDevice))
{
sourceDevice = DefaultSourceDevice;
}
if (!args.TryGetValue("lagTime", out setting) || !double.TryParse(setting, out lagTime))
{
lagTime = DefaultLagTime;
}
if (!args.TryGetValue("leadTime", out setting) || !double.TryParse(setting, out leadTime))
{
leadTime = DefaultLeadTime;
}
if (!args.TryGetValue("framesPerSecond", out setting) || !int.TryParse(setting, out framesPerSecond))
{
framesPerSecond = DefaultFramesPerSecond;
}
if (args.TryGetValue("skipFirstRow", out setting))
{
skipFirstRow = setting.ParseBoolean();
}
else
{
skipFirstRow = true;
}
string sourceFileName = FilePath.GetAbsolutePath(args["OrderedArg1"]);
string configFile = FilePath.GetAbsolutePath($"{sourceApp}.exe.config");
if (!File.Exists(configFile))
{
throw new FileNotFoundException($"Config file for {sourceApp} application \"{configFile}\" was not found.");
}
XDocument serviceConfig = XDocument.Load(configFile);
nodeID = Guid.Parse(serviceConfig
.Descendants("systemSettings")
.SelectMany(systemSettings => systemSettings.Elements("add"))
.Where(element => "NodeID".Equals((string)element.Attribute("name"), StringComparison.OrdinalIgnoreCase))
.Select(element => (string)element.Attribute("value"))
.FirstOrDefault());
string connectionString = serviceConfig
.Descendants("systemSettings")
.SelectMany(systemSettings => systemSettings.Elements("add"))
.Where(element => "ConnectionString".Equals((string)element.Attribute("name"), StringComparison.OrdinalIgnoreCase))
.Select(element => (string)element.Attribute("value"))
.FirstOrDefault();
string dataProviderString = serviceConfig
.Descendants("systemSettings")
.SelectMany(systemSettings => systemSettings.Elements("add"))
.Where(element => "DataProviderString".Equals((string)element.Attribute("name"), StringComparison.OrdinalIgnoreCase))
.Select(element => (string)element.Attribute("value"))
.FirstOrDefault();
using (AdoDataConnection connection = new AdoDataConnection(connectionString, dataProviderString))
using (StreamReader reader = File.OpenText(sourceFileName))
{
TableOperations <Device> deviceTable = new TableOperations <Device>(connection);
TableOperations <Measurement> measurementTable = new TableOperations <Measurement>(connection);
TableOperations <Phasor> phasorTable = new TableOperations <Phasor>(connection);
TableOperations <CustomActionAdapter> actionAdapterTable = new TableOperations <CustomActionAdapter>(connection);
string line;
while ((object)(line = reader.ReadLine()) != null)
{
row++;
if (skipFirstRow)
{
skipFirstRow = false;
continue;
}
// Simple mode: -- simple mode only works when devices on both end of the line are measuring a single voltage and current
// 0 1 2
// TieLine, SendingDevice, ReceivingDevice
// Explicit mode:
// 0 1 2 3 4 5 6 7 8 9 10
// TieLine, SendingSubstation, ReceivingSubstation, SV1Angle, SV1Magnitude, SI1Angle, SI1Magnitude, RV1Angle, RV1Magnitude, RI1Angle, RI1Magnitude
string[] columns = line.Split(',');
if (columns.Length < 3)
{
Console.WriteLine($"Not enough columns in CSV file at row {row} - expected 3 for simple mode, encountered {columns.Length}, skipped row.");
continue;
}
if (columns.Length > 3 && columns.Length < 11)
{
Console.WriteLine($"Not enough columns in CSV file at row {row} - expected 11 for explicit mode, encountered {columns.Length}, skipped row.");
continue;
}
string tieLineID = columns[0].ToUpperInvariant().Trim();
string sendDevice = columns[1].ToUpperInvariant().Trim();
string receiveDevice = columns[2].ToUpperInvariant().Trim();
string sv1Angle = null, sv1Magnitude = null, si1Angle = null, si1Magnitude = null, rv1Angle = null, rv1Magnitude = null, ri1Angle = null, ri1Magnitude = null;
string[] measurements;
bool simpleMode = columns.Length == 3;
List <string> inputMeasurements = new List <string>();
Dictionary <string, string> adapterConnectionString = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase);
if (simpleMode)
{
simpleFormattedRecords++;
}
else
{
explicitFormattedRecords++;
sv1Angle = columns[3].ToUpperInvariant().Trim();
sv1Magnitude = columns[4].ToUpperInvariant().Trim();
si1Angle = columns[5].ToUpperInvariant().Trim();
si1Magnitude = columns[6].ToUpperInvariant().Trim();
rv1Angle = columns[7].ToUpperInvariant().Trim();
rv1Magnitude = columns[8].ToUpperInvariant().Trim();
ri1Angle = columns[9].ToUpperInvariant().Trim();
ri1Magnitude = columns[10].ToUpperInvariant().Trim();
}
// Add initial connection string settings
adapterConnectionString["FramesPerSecond"] = framesPerSecond.ToString();
adapterConnectionString["LagTime"] = lagTime.ToString(CultureInfo.InvariantCulture);
adapterConnectionString["LeadTime"] = leadTime.ToString(CultureInfo.InvariantCulture);
// Get or add virtual device to associate new output measurements with
Device device = GetOrAddDevice(deviceTable, sourceDevice);
if (simpleMode)
{
// Get input measurement IDs for sending device
measurements = GetInputMeasurements(deviceTable, measurementTable, phasorTable, sendDevice);
if ((object)measurements == null)
{
failures++;
continue;
}
inputMeasurements.AddRange(measurements);
// Get input measurement IDs for receiving device
measurements = GetInputMeasurements(deviceTable, measurementTable, phasorTable, receiveDevice);
if ((object)measurements == null)
{
failures++;
continue;
}
inputMeasurements.AddRange(measurements);
}
else
{
if (GetInputMeasurement(measurementTable, sv1Angle, inputMeasurements) ||
GetInputMeasurement(measurementTable, sv1Magnitude, inputMeasurements) ||
GetInputMeasurement(measurementTable, si1Angle, inputMeasurements) ||
GetInputMeasurement(measurementTable, si1Magnitude, inputMeasurements) ||
GetInputMeasurement(measurementTable, rv1Angle, inputMeasurements) ||
GetInputMeasurement(measurementTable, rv1Magnitude, inputMeasurements) ||
GetInputMeasurement(measurementTable, ri1Angle, inputMeasurements) ||
GetInputMeasurement(measurementTable, ri1Magnitude, inputMeasurements))
{
failures++;
continue;
}
}
// Define input measurement keys connection string parameter
adapterConnectionString["InputMeasurementKeys"] = string.Join("; ", inputMeasurements);
bool newAdd;
CustomActionAdapter actionAdapter = GetOrAddActionAdapter(actionAdapterTable, tieLineID, out newAdd);
Console.WriteLine($"{(newAdd ? "Adding" : "Augmenting")} impedance calculation for \"{tieLineID}\"...");
// Get output measurement IDs for calculator adapter, creating them if needed
OrderedDictionary outputTypes = new OrderedDictionary(StringComparer.OrdinalIgnoreCase)
{
["RESISTANCE"] = "Resistance",
["REACTANCE"] = "Reactance",
["CONDUCTANCE"] = "Conductance",
["SUSCEPTANCE"] = "Susceptance",
["LINEIMPEDANCE"] = "Line Impedance",
["LINEIMPEDANCEANGLE"] = "Line Impedance Angle",
["LINEADMITTANCE"] = "Line Admittance",
["LINEADMITTANCEANGLE"] = "Line Admittance Angle"
};
measurements = GetOutputMeasurements(measurementTable, device.ID, tieLineID, sendDevice, receiveDevice, outputTypes);
// Define input measurement keys connection string parameter
adapterConnectionString["OutputMeasurements"] = string.Join("; ", measurements);
// Save updates to action adapter
actionAdapter.Enabled = true;
actionAdapter.ConnectionString = adapterConnectionString.JoinKeyValuePairs();
actionAdapterTable.UpdateRecord(actionAdapter);
successes++;
}
}
Console.WriteLine();
Console.WriteLine($"Found {simpleFormattedRecords:N0} simple formatted records.");
Console.WriteLine($"Found {explicitFormattedRecords:N0} explicit formatted records.");
Console.WriteLine();
Console.WriteLine($"{successes:N0} successful imports.");
Console.WriteLine($"{failures:N0} failed imports.");
#if DEBUG
Console.ReadKey();
#endif
return(0);
}
catch (Exception ex)
{
Console.WriteLine();
Console.WriteLine($"Import halted at row {row}!");
Console.Error.WriteLine($"Load Exception: {ex.Message}");
return(1);
}
}