/// <summary> /// Gets measurement record, creating it if needed. /// </summary> /// <param name="instance">Target <see cref="IIndependentAdapterManager"/> instance.</param> /// <param name="currentDeviceID">Device ID associated with current adapter, or zero if none.</param> /// <param name="pointTag">Point tag of measurement.</param> /// <param name="alternateTag">Alternate tag of measurement.</param> /// <param name="signalReference">Signal reference of measurement.</param> /// <param name="description">Description of measurement.</param> /// <param name="signalType">Signal type of measurement.</param> /// <param name="targetHistorianAcronym">Acronym of target historian for measurement.</param> /// <returns>Measurement record.</returns> public static MeasurementRecord GetMeasurementRecord(this IIndependentAdapterManager instance, int currentDeviceID, string pointTag, string alternateTag, string signalReference, string description, SignalType signalType = SignalType.CALC, string targetHistorianAcronym = "PPA") { // Open database connection as defined in configuration file "systemSettings" category using (AdoDataConnection connection = instance.GetConfiguredConnection()) { TableOperations <DeviceRecord> deviceTable = new TableOperations <DeviceRecord>(connection); TableOperations <MeasurementRecord> measurementTable = new TableOperations <MeasurementRecord>(connection); TableOperations <HistorianRecord> historianTable = new TableOperations <HistorianRecord>(connection); TableOperations <SignalTypeRecord> signalTypeTable = new TableOperations <SignalTypeRecord>(connection); // Lookup target device ID int?deviceID = currentDeviceID > 0 ? currentDeviceID : deviceTable.QueryRecordWhere("Acronym = {0}", instance.Name)?.ID; // Lookup target historian ID int?historianID = historianTable.QueryRecordWhere("Acronym = {0}", targetHistorianAcronym)?.ID; // Lookup signal type ID int signalTypeID = signalTypeTable.QueryRecordWhere("Acronym = {0}", signalType.ToString())?.ID ?? 1; // Lookup measurement record by point tag, creating a new record if one does not exist MeasurementRecord measurement = measurementTable.QueryRecordWhere("SignalReference = {0}", signalReference) ?? measurementTable.NewRecord(); // Update record fields measurement.DeviceID = deviceID; measurement.HistorianID = historianID; measurement.PointTag = pointTag; measurement.AlternateTag = alternateTag; measurement.SignalReference = signalReference; measurement.SignalTypeID = signalTypeID; measurement.Description = description; // Save record updates measurementTable.AddNewOrUpdateRecord(measurement); // Re-query new records to get any database assigned information, e.g., unique Guid-based signal ID if (measurement.PointID == 0) { measurement = measurementTable.QueryRecordWhere("SignalReference = {0}", signalReference); } // Notify host system of configuration changes instance.OnConfigurationChanged(); return(measurement); } }
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 override void Execute(MeterDataSet meterDataSet) { FaultDataResource faultDataResource = meterDataSet.GetResource <FaultDataResource>(); string stationKey = meterDataSet.Meter.MeterLocation.AssetKey; foreach (var kvp in faultDataResource.FaultLookup) { DataGroup dataGroup = kvp.Key; DataAnalysis.FaultGroup faultGroup = kvp.Value; string lineKey = dataGroup.Line.AssetKey; for (int i = 0; i < faultGroup.Faults.Count; i++) { int faultNumber = i + 1; DataAnalysis.Fault fault = faultGroup.Faults[i]; if (fault.IsSuppressed) { continue; } string distance = fault.Summaries .Where(summary => summary.IsValid) .Where(summary => summary.IsSelectedAlgorithm) .Select(summary => summary.Distance.ToString("0.###")) .FirstOrDefault(); if (distance == null) { return; } string url = string.Format(Settings.URLFormat, stationKey, lineKey, distance); string structureInfo = GetStructureInfo(url); DataTable structureData = ToDataTable(structureInfo); if (structureData.Rows.Count == 0) { return; } Func <string, string> fieldMappingLookup = FieldMappingLookup; string assetKeyField = fieldMappingLookup("AssetKey"); string latitudeKeyField = fieldMappingLookup("Latitude"); string longitudeKeyField = fieldMappingLookup("Longitude"); if (!structureData.Columns.Contains(assetKeyField)) { return; } using (AdoDataConnection connection = meterDataSet.CreateDbConnection()) { TableOperations <Event> eventTable = new TableOperations <Event>(connection); Event evt = eventTable.GetEvent(meterDataSet.FileGroup, dataGroup); int faultSummaryID = connection.ExecuteScalar <int>(FaultSummaryQuery, evt.ID, faultNumber); TableOperations <Structure> structureTable = new TableOperations <Structure>(connection); foreach (DataRow row in structureData.Rows) { string assetKey = row.Field <string>(assetKeyField); string latitude = null; string longitude = null; if (structureData.Columns.Contains(latitudeKeyField)) { latitude = row.Field <string>(latitudeKeyField); } if (structureData.Columns.Contains(longitudeKeyField)) { longitude = row.Field <string>(longitudeKeyField); } Structure structure = structureTable.QueryRecordWhere("AssetKey = {0}", assetKey) ?? new Structure() { AssetKey = assetKey }; structure.LineID = dataGroup.Line.ID; if (double.TryParse(latitude, out double lat)) { structure.Latitude = lat; } if (double.TryParse(longitude, out double lon)) { structure.Longitude = lon; } structureTable.AddNewOrUpdateRecord(structure); if (structure.ID == 0) { structure.ID = connection.ExecuteScalar <int>("SELECT @@IDENTITY"); } if (faultSummaryID != 0) { connection.ExecuteNonQuery("INSERT INTO NearestStructure(FaultSummaryID, StructureID) VALUES({0}, {1})", faultSummaryID, structure.ID); } } } } } }
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); } }
public static void AddNewOrUpdateMeasurement(this TableOperations <Measurement> measurementTable, Measurement measurement) => measurementTable.AddNewOrUpdateRecord(measurement);
public IHttpActionResult UpdateMeterChannels([FromBody] JObject postData, int meterID, string filter) { if (PatchRoles == string.Empty || User.IsInRole(PatchRoles)) { try { using (AdoDataConnection connection = new AdoDataConnection(Connection)) { TableOperations <Channel> channelTable = new TableOperations <Channel>(connection); TableOperations <Series> seriesTable = new TableOperations <Series>(connection); JToken Channels = postData["Channels"]; IEnumerable <MeasurementType> measurementTypes = new TableOperations <MeasurementType>(connection).QueryRecords(); IEnumerable <MeasurementCharacteristic> measurementCharacteristics = new TableOperations <MeasurementCharacteristic>(connection).QueryRecords(); IEnumerable <Phase> phases = new TableOperations <Phase>(connection).QueryRecords(); IEnumerable <Asset> assets = new TableOperations <Asset>(connection).QueryRecordsWhere("ID IN (SELECT AssetID FROM MeterAsset WHERE MeterID = {0})", meterID); IEnumerable <SeriesType> seriesTypes = new TableOperations <SeriesType>(connection).QueryRecords(); List <int> channelIDs = new List <int>(); foreach (JToken channelToken in Channels) { Channel channel = new Channel(); channel.ID = channelToken["ID"].ToObject <int>(); channel.MeterID = meterID; channel.AssetID = assets.FirstOrDefault(asset => asset.AssetKey == channelToken["Asset"].ToString()).ID; channel.MeasurementTypeID = measurementTypes.First(mt => mt.Name == channelToken["MeasurementType"].ToString()).ID; channel.MeasurementCharacteristicID = measurementCharacteristics.First(mc => mc.Name == channelToken["MeasurementCharacteristic"].ToString()).ID; channel.PhaseID = phases.First(phase => phase.Name == channelToken["Phase"].ToString()).ID; channel.Name = channelToken["Name"].ToString(); channel.Description = channelToken["Description"].ToString() == string.Empty ? null : channelToken["Description"].ToString(); channel.Adder = channelToken["Adder"].ToObject <double>(); channel.Multiplier = channelToken["Multiplier"].ToObject <double>(); channel.SamplesPerHour = channelToken["SamplesPerHour"].ToObject <double>(); channel.PerUnitValue = channelToken["PerUnitValue"].ToObject <double?>(); channel.HarmonicGroup = channelToken["HarmonicGroup"].ToObject <int>(); channel.Enabled = channelToken["Enabled"].ToObject <bool>(); channel.ConnectionPriority = channelToken["ConnectionPriority"].ToObject <int>(); if (channel.AssetID == 0) { continue; } channelTable.AddNewOrUpdateRecord(channel); if (channel.ID == 0) { channel.ID = connection.ExecuteScalar <int>("SELECT @@IDENTITY"); } if (channelToken["Series"] is JArray array) { foreach (JToken seriesToken in array) { Series series = new Series(); series.ID = seriesToken.Value <int>("ID"); series.ChannelID = channel.ID; series.SeriesTypeID = seriesTypes.First(st => st.Name == seriesToken.Value <string>("SeriesType")).ID; series.SourceIndexes = seriesToken.Value <string>("SourceIndexes"); seriesTable.AddNewOrUpdateRecord(series); } } channelIDs.Add(channel.ID); } const string EventChannelFilter = "MeasurementCharacteristicID = (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'Instantaneous') AND " + "(SELECT COUNT(*) FROM Series WHERE ChannelID = Channel.ID) = 1 AND " + "EXISTS (SELECT * FROM Series WHERE SeriesTypeID IN (SELECT ID FROM SeriesType WHERE Name IN ('Values', 'Instantaneous')))"; string GetTypeFilter() { switch (filter.ToLower()) { case "event": return(EventChannelFilter); case "trend": return($"NOT ({EventChannelFilter})"); case "none": return("1 IS NULL"); default: return("1=1"); } } string idFilter = channelIDs.Any() ? $"ID NOT IN ({string.Join(",", channelIDs)})" : "1=1"; string deleteFilter = $"MeterID = {meterID} AND " + $"({GetTypeFilter()}) AND " + $"{idFilter}"; connection.ExecuteNonQuery("EXEC UniversalCascadeDelete 'Channel', {0}", deleteFilter); return(Ok("Completed without errors")); } } catch (Exception ex) { return(InternalServerError(ex)); } } else { return(Unauthorized()); } }
private void SetupRequiredAdapters() { try { const string PTPdProcessName = "PTPD!PROCESS"; const string StatisticServicesName = "STATISTIC!SERVICES"; const string DataPublisherName = "INTERNAL!DATAPUBLISHER"; // Access needed settings from specified categories in configuration file CategorizedSettingsElementCollection systemSettings = ConfigurationFile.Current.Settings["systemSettings"]; string newNodeID = Guid.NewGuid().ToString(); // Make sure needed settings exist systemSettings.Add("NodeID", newNodeID, "Unique Node ID"); // Get settings as currently defined in configuration file Guid nodeID = Guid.Parse(systemSettings["NodeID"].ValueAs(newNodeID)); // 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); // Make sure Grafana process adapter exists CustomActionAdapter actionAdapter = actionAdapterTable.QueryRecordWhere("AdapterName = {0}", PTPdProcessName) ?? actionAdapterTable.NewRecord(); // Update record fields actionAdapter.NodeID = nodeID; actionAdapter.AdapterName = PTPdProcessName; actionAdapter.AssemblyName = "FileAdapters.dll"; actionAdapter.TypeName = "FileAdapters.ProcessLauncher"; actionAdapter.Enabled = true; string interfaceGuid = null; // Scan network interfaces try { NetworkInterface[] networkInterfaces = NetworkInterface.GetAllNetworkInterfaces().Where(ni => ni.OperationalStatus == OperationalStatus.Up && ni.NetworkInterfaceType != NetworkInterfaceType.Loopback && ni.NetworkInterfaceType != NetworkInterfaceType.Tunnel).ToArray(); if (networkInterfaces.Length > 0) { interfaceGuid = networkInterfaces[0].Id; File.WriteAllText(FilePath.GetAbsolutePath("NetworkInterfaces.txt"), $"Local Network Interfaces:\r\n\r\n{string.Join("\r\n", networkInterfaces.Select(ni => $"{ni.Name} - {ni.Description}: {ni.Id}"))}"); } } catch { } // Define default adapter connection string if none is defined if (string.IsNullOrWhiteSpace(actionAdapter.ConnectionString)) { actionAdapter.ConnectionString = $"FileName=ptpd.exe; Arguments={{-b {interfaceGuid ?? "{InterfaceGuidHere}"} -V -g}}; ForceKillOnDispose=True"; } // Save record updates actionAdapterTable.AddNewOrUpdateRecord(actionAdapter); // Make sure statistic services adapter exists actionAdapter = actionAdapterTable.QueryRecordWhere("AdapterName = {0}", StatisticServicesName) ?? actionAdapterTable.NewRecord(); // Update record fields actionAdapter.NodeID = nodeID; actionAdapter.AdapterName = StatisticServicesName; actionAdapter.AssemblyName = "GSF.TimeSeries.dll"; actionAdapter.TypeName = "GSF.TimeSeries.Statistics.StatisticsEngine"; actionAdapter.Enabled = true; // Save record updates actionAdapterTable.AddNewOrUpdateRecord(actionAdapter); // Make sure internal data publisher adapter exists actionAdapter = actionAdapterTable.QueryRecordWhere("AdapterName = {0}", DataPublisherName) ?? actionAdapterTable.NewRecord(); // Update record fields actionAdapter.NodeID = nodeID; actionAdapter.AdapterName = DataPublisherName; actionAdapter.AssemblyName = "GSF.TimeSeries.dll"; actionAdapter.TypeName = "GSF.TimeSeries.Transport.DataPublisher"; actionAdapter.ConnectionString = "securityMode=None; allowSynchronizedSubscription=false; useBaseTimeOffsets=true; cacheMeasurementKeys={FILTER ActiveMeasurements WHERE SignalType = 'STAT'}"; actionAdapter.Enabled = true; // Save record updates actionAdapterTable.AddNewOrUpdateRecord(actionAdapter); } } catch (Exception ex) { LogPublisher log = Logger.CreatePublisher(typeof(ServiceHost), MessageClass.Application); log.Publish(MessageLevel.Error, "Error Message", "Failed to setup PTPd hosting adapter", null, ex); } }
private void UpdateConfigurationData(MeterDataSet meterDataSet) { bool updateLineLength = meterDataSet.Configuration.LineLength.HasValue; bool updateLineImpedance = meterDataSet.Configuration.R1.HasValue && meterDataSet.Configuration.X1.HasValue && meterDataSet.Configuration.R0.HasValue && meterDataSet.Configuration.X0.HasValue; if (!updateLineLength && !updateLineImpedance) { return; } using (AdoDataConnection connection = meterDataSet.CreateDbConnection()) { TableOperations <Line> lineTable = new TableOperations <Line>(connection); List <Line> lines = lineTable.QueryRecordsWhere("ID IN (SELECT LineID FROM MeterLocationLine WHERE MeterLocationID = {0})", meterDataSet.Meter.MeterLocationID).ToList(); if (lines.Count != 1) { return; } Line line = lines[0]; if (updateLineLength) { line.Length = meterDataSet.Configuration.LineLength.GetValueOrDefault(); lineTable.UpdateRecord(line); } if (updateLineImpedance) { TableOperations <LineImpedance> lineImpedanceTable = new TableOperations <LineImpedance>(connection); LineImpedance lineImpedance = lineImpedanceTable.QueryRecordWhere("LineID = {0}", line.ID); if ((object)lineImpedance == null) { lineImpedance = new LineImpedance() { ID = line.ID } } ; if (meterDataSet.Configuration.R1.HasValue) { lineImpedance.R1 = meterDataSet.Configuration.R1.GetValueOrDefault(); } if (meterDataSet.Configuration.X1.HasValue) { lineImpedance.X1 = meterDataSet.Configuration.X1.GetValueOrDefault(); } if (meterDataSet.Configuration.R0.HasValue) { lineImpedance.R0 = meterDataSet.Configuration.R0.GetValueOrDefault(); } if (meterDataSet.Configuration.X0.HasValue) { lineImpedance.X0 = meterDataSet.Configuration.X0.GetValueOrDefault(); } try { lineImpedanceTable.AddNewOrUpdateRecord(lineImpedance); } catch (Exception ex) { // Ignore errors regarding unique key constraints // which can occur as a result of a race condition bool isUniqueViolation = ExceptionHandler.IsUniqueViolation(ex); if (!isUniqueViolation) { throw; } } } } }