/// <summary> /// Saves measurement back to the configuration database /// </summary> /// <param name="database">Database connection for query. Will be created from config if this value is null.</param> /// <param name="measurement">Measurement to be inserted or updated</param> public void Save(AdoDataConnection database, PowerMeasurement measurement) { var createdConnection = false; try { createdConnection = CreateConnection(ref database); if (measurement.SignalID == Guid.Empty) { database.ExecuteNonQuery("INSERT INTO Measurement (DeviceID, PointTag, SignalTypeID, " + "SignalReference, Adder, Multiplier, Description, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES " + "({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11})", ToNotNull(measurement.DeviceID), measurement.PointTag, measurement.SignalTypeID, measurement.SignalReference, measurement.Adder, measurement.Multiplier, ToNotNull(measurement.Description), database.Bool(measurement.Enabled), Thread.CurrentPrincipal.Identity.Name, database.UtcNow, Thread.CurrentPrincipal.Identity.Name, database.UtcNow); measurement.SignalID = database.ExecuteScalar<Guid>("SELECT SignalID FROM Measurement WHERE PointTag={0}", measurement.PointTag); } else { database.ExecuteNonQuery("UPDATE Measurement SET DeviceID = {0}, PointTag = {1}, " + "SignalTypeID = {2}, SignalReference = {3}, Adder = {4}, Multiplier = {5}, Description = {6}, " + "Enabled = {7}, UpdatedBy = {8}, UpdatedOn = {9} WHERE SignalId = {10}", ToNotNull(measurement.DeviceID), measurement.PointTag, measurement.SignalTypeID, measurement.SignalReference, measurement.Adder, measurement.Multiplier, ToNotNull(measurement.Description), database.Bool(measurement.Enabled), Thread.CurrentPrincipal.Identity.Name, database.UtcNow, measurement.SignalID); } } finally { if (createdConnection) database?.Dispose(); } }
internal static void LoadConnectionParameters() { try { using (AdoDataConnection connection = new AdoDataConnection("systemSettings")) { TableOperations<IaonOutputAdapter> operations = new TableOperations<IaonOutputAdapter>(connection); IaonOutputAdapter record = operations.QueryRecords(limit: 1, restriction: new RecordRestriction { FilterExpression = "TypeName = 'openHistorian.Adapters.LocalOutputAdapter'" }) .FirstOrDefault(); if ((object)record == null) throw new NullReferenceException("Primary openHistorian adapter instance not found."); Dictionary<string, string> settings = record.ConnectionString.ParseKeyValuePairs(); string setting; if (!settings.TryGetValue("port", out setting) || !int.TryParse(setting, out s_portNumber)) s_portNumber = Connection.DefaultHistorianPort; if (!settings.TryGetValue("instanceName", out s_defaultInstanceName) || string.IsNullOrWhiteSpace(s_defaultInstanceName)) s_defaultInstanceName = record.AdapterName ?? "PPA"; } } catch { s_defaultInstanceName = "PPA"; s_portNumber = Connection.DefaultHistorianPort; } }
/// <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(); } }
public WebPageControllerResolver(WebServer webServer, string defaultWebPage, object model, Type modelType, AdoDataConnection database) { m_webServer = webServer; m_defaultWebPage = defaultWebPage; m_model = model; m_modelType = modelType; m_database = database; }
/// <summary> /// Validates that data operation and adapter instance exist within database. /// </summary> public static void ValidateDatabaseDefinitions() { using (AdoDataConnection database = new AdoDataConnection("systemSettings")) { if (!DataOperationExists(database)) CreateDataOperation(database); if (!AdapterInstanceExists(database)) CreateAdapterInstance(database); } }
/// <summary> /// Creates a new <see cref="RazorView"/>. /// </summary> /// <param name="razorEngine"><see cref="IRazorEngine"/> instance to use.</param> /// <param name="templateName">Name of template file, typically a .cshtml or .vbhtml file.</param> /// <param name="model">Reference to model to use when rendering template.</param> /// <param name="modelType">Type of <paramref name="model"/>.</param> /// <param name="pagedViewModelDataType">Type of data class for views based on paged view model, if any.</param> /// <param name="pagedViewModelHubType">Type of SignalR hub for views based on paged view model, if any.</param> /// <param name="database"><see cref="AdoDataConnection"/> to use, if any.</param> /// <param name="exceptionHandler">Delegate to handle exceptions, if any.</param> public RazorView(IRazorEngine razorEngine, string templateName, object model = null, Type modelType = null, Type pagedViewModelDataType = null, Type pagedViewModelHubType = null, AdoDataConnection database = null, Action<Exception> exceptionHandler = null) { m_razorEngine = razorEngine; TemplateName = templateName; Model = model; ModelType = modelType; PagedViewModelDataType = pagedViewModelDataType; PagedViewModelHubType = pagedViewModelHubType; Database = database; ExceptionHandler = exceptionHandler; }
/// <summary> /// Delegates control to the data operations that are to be performed at startup. /// </summary> private static void PerformTimeSeriesStartupOperations(AdoDataConnection database, string nodeIDQueryString, ulong trackingVersion, string arguments, Action<string> statusMessage, Action<Exception> processException) { // Set up messaging to the service s_statusMessage = statusMessage; s_processException = processException; // Run data operations ValidateDefaultNode(database, nodeIDQueryString); ValidateActiveMeasurements(database, nodeIDQueryString); ValidateAccountsAndGroups(database, nodeIDQueryString); ValidateDataPublishers(database, nodeIDQueryString, arguments); ValidateStatistics(database, nodeIDQueryString); ValidateAlarming(database, nodeIDQueryString); }
private static bool CreateConnection(ref AdoDataConnection database) { if ((object)database != null) return false; try { database = new AdoDataConnection("systemSettings"); return true; } catch { return false; } }
/// <summary> /// Creates a new <see cref="DataSubscriptionHubClient"/> instance. /// </summary> public DataSubscriptionHubClient() { m_statisticSubscriptionInfo = new UnsynchronizedSubscriptionInfo(false); m_dataSubscriptionInfo = new UnsynchronizedSubscriptionInfo(false); m_measurements = new List<MeasurementValue>(); m_statistics = new List<MeasurementValue>(); m_statusLights = new List<StatusLight>(); m_deviceDetails = new List<DeviceDetail>(); m_measurementDetails = new List<MeasurementDetail>(); m_phasorDetails = new List<PhasorDetail>(); m_schemaVersion = new List<SchemaVersion>(); m_measurementLock = new object(); using(AdoDataConnection conn = new AdoDataConnection("securityProvider")) { int index = conn.ExecuteScalar<int>("Select ID FROM ValueListGroup WHERE Name = 'ModbusSubscriptions'"); m_connectionString = conn.ExecuteScalar<string>("Select Text FROM ValueList WHERE GroupID = {0} AND IsDefault = 1", index); } }
/// <summary> /// Creates an instance of <see cref="App"/> class. /// </summary> public App() { AppDomain.CurrentDomain.SetPrincipalPolicy(PrincipalPolicy.WindowsPrincipal); m_errorLogger = new ErrorLogger(); m_defaultErrorText = m_errorLogger.ErrorTextMethod; m_errorLogger.ErrorTextMethod = ErrorText; m_errorLogger.ExitOnUnhandledException = false; m_errorLogger.HandleUnhandledException = true; m_errorLogger.LogToEmail = false; m_errorLogger.LogToEventLog = true; m_errorLogger.LogToFile = true; m_errorLogger.LogToScreenshot = true; m_errorLogger.LogToUI = true; m_errorLogger.Initialize(); m_title = AssemblyInfo.EntryAssembly.Title; // Setup default cache for measurement keys and associated Guid based signal ID's AdoDataConnection database = null; try { database = new AdoDataConnection(CommonFunctions.DefaultSettingsCategory); MeasurementKey.EstablishDefaultCache(database.Connection, database.AdapterType); } catch (Exception ex) { // First attempt to display a modal dialog will fail to block this // thread -- modal dialog displayed by the error logger will block now MessageBox.Show(ex.Message); // Log and display error, then exit application - manager must connect to database to continue m_errorLogger.Log(new InvalidOperationException(string.Format("{0} cannot connect to database: {1}", m_title, ex.Message), ex), true); } finally { if (database != null) database.Dispose(); } IsolatedStorageManager.WriteToIsolatedStorage("MirrorMode", false); }
/// <summary> /// Data operation to validate and ensure there is a node in the database. /// </summary> private static void ValidateDefaultNode(AdoDataConnection database, string nodeIDQueryString) { // Queries const string NodeCountFormat = "SELECT COUNT(*) FROM Node"; const string NodeInsertFormat = "INSERT INTO Node(Name, CompanyID, Description, Settings, MenuType, MenuData, Master, LoadOrder, Enabled) " + "VALUES('Default', NULL, 'Default node', 'RemoteStatusServerConnectionString={server=localhost:8500};datapublisherport=6165;RealTimeStatisticServiceUrl=http://localhost:6052/historian', " + "'File', 'Menu.xml', 1, 0, 1)"; const string NodeUpdateFormat = "UPDATE Node SET ID = {0}"; // Determine whether the node exists in the database and create it if it doesn't. int nodeCount = Convert.ToInt32(database.Connection.ExecuteScalar(NodeCountFormat)); if (nodeCount == 0) { database.Connection.ExecuteNonQuery(NodeInsertFormat); database.Connection.ExecuteNonQuery(string.Format(NodeUpdateFormat, nodeIDQueryString)); } }
/// <summary> /// Saves <see cref="InputWizardDevice"/> information in to database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="inputWizardDevice">Information about <see cref="InputWizardDevice"/>.</param> /// <returns>string, indicating success for UI display.</returns> public static string Save(AdoDataConnection database, InputWizardDevice inputWizardDevice) { return(""); }
private void LogStateChanges() { IList <StateChange> stateChanges; StringBuilder insertQuery; List <object> insertParameters; StringBuilder deleteQuery; List <object> deleteParameters; int count; Thread.Sleep(m_logProcessingDelay); stateChanges = m_stateChanges.Dequeue(); insertQuery = new StringBuilder(); insertParameters = new List <object>(); deleteQuery = new StringBuilder(); deleteParameters = new List <object>(); count = 0; using (AdoDataConnection connection = new AdoDataConnection("systemSettings")) { foreach (StateChange stateChange in stateChanges) { if (insertQuery.Length == 0) { insertQuery.Append("INSERT INTO AlarmLog(SignalID, PreviousState, NewState, Ticks, Timestamp, Value) "); insertQuery.Append("SELECT {0} AS SignalID, {1} AS PreviousState, {2} AS NewState, {3} AS Ticks, {4} AS Timestamp, {5} AS Value"); deleteQuery.Append("DELETE FROM AlarmLog WHERE "); deleteQuery.Append("(SignalID = {0} AND Ticks < {1})"); } else { insertQuery.Append(" UNION ALL "); insertQuery.AppendFormat("SELECT {{{0}}}, {{{1}}}, {{{2}}}, {{{3}}}, {{{4}}}, {{{5}}}", Enumerable.Range(count * 6, 6).Cast <object>().ToArray()); deleteQuery.Append(" OR "); deleteQuery.AppendFormat("(SignalID = {{{0}}} AND Ticks < {{{1}}})", Enumerable.Range(count * 2, 2).Cast <object>().ToArray()); } insertParameters.Add(stateChange.SignalID); insertParameters.Add(stateChange.OldStateID); insertParameters.Add(stateChange.NewStateID); insertParameters.Add(stateChange.Timestamp.Ticks); insertParameters.Add(stateChange.Timestamp); insertParameters.Add(stateChange.Value); deleteParameters.Add(stateChange.SignalID); deleteParameters.Add(stateChange.Timestamp.AddHours(-24.0D).Ticks); count++; if (count == m_bulkInsertLimit) { connection.ExecuteNonQuery(insertQuery.ToString(), insertParameters.ToArray()); connection.ExecuteNonQuery(deleteQuery.ToString(), deleteParameters.ToArray()); insertQuery.Clear(); insertParameters.Clear(); deleteQuery.Clear(); deleteParameters.Clear(); count = 0; } } if (count > 0) { connection.ExecuteNonQuery(insertQuery.ToString(), insertParameters.ToArray()); connection.ExecuteNonQuery(deleteQuery.ToString(), deleteParameters.ToArray()); } } }
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); } } } } } }
/// <summary> /// Returns true if a data operation exists to run this class. Returns false otherwise. /// </summary> /// <param name="database">Database connection to use for checking the data operation</param> /// <returns>True or false indicating whether the operation exists</returns> private static bool AdapterInstanceExists(AdoDataConnection database) { return(Convert.ToInt32(database.ExecuteScalar($"SELECT COUNT(*) FROM CustomActionAdapter WHERE TypeName='{typeof(PowerMultiCalculatorAdapter).FullName}'")) > 0); }
/// <summary> /// Validate accounts and groups to ensure that account names and group names are converted to SIDs. /// </summary> /// <param name="database">Data connection to use for database operations.</param> private static void ValidateAccountsAndGroups(AdoDataConnection database) { const string SelectUserAccountQuery = "SELECT ID, Name, UseADAuthentication FROM UserAccount"; const string SelectSecurityGroupQuery = "SELECT ID, Name FROM SecurityGroup"; const string UpdateUserAccountFormat = "UPDATE UserAccount SET Name = '{0}' WHERE ID = '{1}'"; const string UpdateSecurityGroupFormat = "UPDATE SecurityGroup SET Name = '{0}' WHERE ID = '{1}'"; string id; string sid; string accountName; Dictionary <string, string> updateMap; updateMap = new Dictionary <string, string>(); // Find user accounts that need to be updated using (IDataReader userAccountReader = database.Connection.ExecuteReader(SelectUserAccountQuery)) { while (userAccountReader.Read()) { id = userAccountReader["ID"].ToNonNullString(); accountName = userAccountReader["Name"].ToNonNullString(); if (userAccountReader["UseADAuthentication"].ToNonNullString().ParseBoolean()) { sid = UserInfo.UserNameToSID(accountName); if (!ReferenceEquals(accountName, sid) && UserInfo.IsUserSID(sid)) { updateMap.Add(id, sid); } } } } // Update user accounts foreach (KeyValuePair <string, string> pair in updateMap) { database.Connection.ExecuteNonQuery(string.Format(UpdateUserAccountFormat, pair.Value, pair.Key)); } updateMap.Clear(); // Find security groups that need to be updated using (IDataReader securityGroupReader = database.Connection.ExecuteReader(SelectSecurityGroupQuery)) { while (securityGroupReader.Read()) { id = securityGroupReader["ID"].ToNonNullString(); accountName = securityGroupReader["Name"].ToNonNullString(); if (accountName.Contains('\\')) { sid = UserInfo.GroupNameToSID(accountName); if (!ReferenceEquals(accountName, sid) && UserInfo.IsGroupSID(sid)) { updateMap.Add(id, sid); } } } } // Update security groups foreach (KeyValuePair <string, string> pair in updateMap) { database.Connection.ExecuteNonQuery(string.Format(UpdateSecurityGroupFormat, pair.Value, pair.Key)); } }
/// <summary> /// Gets a <see cref="Dictionary{T1,T2}"/> style list of <see cref="Subscriber"/> information. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="isOptional">Indicates if selection on UI is optional for this collection.</param> /// <returns><see cref="Dictionary{T1,T2}"/> containing ID and Name of subscribers defined in the database.</returns> public static Dictionary<Guid, string> GetLookupList(AdoDataConnection database, bool isOptional = false) { Dictionary<Guid, string> subscriberList; DataTable subscriberTable; bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); subscriberList = new Dictionary<Guid, string>(); if (isOptional) subscriberList.Add(Guid.Empty, "Select Subscriber"); query = database.ParameterizedQueryString("SELECT ID, Acronym FROM Subscriber WHERE Enabled = {0} AND NodeID = {1} ORDER BY Name", "enabled", "nodeID"); subscriberTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Bool(true), database.CurrentNodeID()); foreach (DataRow row in subscriberTable.Rows) { subscriberList[database.Guid(row, "ID")] = row.Field<string>("Acronym"); } return subscriberList; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Retrieves <see cref="Dictionary{T1,T2}"/> type collection of <see cref="MeasurementGroup"/> neither allowed nor denied for <see cref="Subscriber"/>. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="subscriberID">ID of the <see cref="Subscriber"/> to filter data.</param> /// <returns><see cref="Dictionary{T1,T2}"/> type collection of ID and Name of <see cref="MeasurementGroup"/>.</returns> public static Dictionary<int, string> GetAvailableMeasurementGroups(AdoDataConnection database, Guid subscriberID) { Dictionary<int, string> availableMeasurementGroups; DataTable availableMeasurementGroupTable; bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); availableMeasurementGroups = new Dictionary<int, string>(); query = database.ParameterizedQueryString("SELECT ID, Name FROM MeasurementGroup WHERE ID NOT IN (SELECT MeasurementGroupID FROM SubscriberMeasurementGroup WHERE SubscriberID = {0}) ORDER BY Name", "subscriberID"); availableMeasurementGroupTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Guid(subscriberID)); foreach (DataRow row in availableMeasurementGroupTable.Rows) availableMeasurementGroups[row.ConvertField<int>("ID")] = row.Field<string>("Name"); return availableMeasurementGroups; } finally { if (createdConnection && database != null) database.Dispose(); } }
// Static Methods /// <summary> /// Loads <see cref="RealTimeStream"/> information as an <see cref="ObservableCollection{T}"/> style list. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <returns>Collection of <see cref="RealTimeStream"/>.</returns> public static ObservableCollection <RealTimeStream> Load(AdoDataConnection database) { bool createdConnection = false; try { ObservableCollection <RealTimeStream> realTimeStreamList = null; createdConnection = CreateConnection(ref database); DataSet resultSet = new DataSet(); resultSet.EnforceConstraints = false; // Get PDCs list. resultSet.Tables.Add(database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, Acronym, Name, ConnectionString, CompanyName, Enabled FROM DeviceDetail " + "WHERE NodeID = {0} AND IsConcentrator = {1} AND Enabled = {2} ORDER BY Acronym", "nodeID", "isConcentrator", "enabled"), DefaultTimeout, database.CurrentNodeID(), database.Bool(true), database.Bool(true)).Copy()); resultSet.Tables[0].TableName = "PdcTable"; // Add a dummy device row in PDC table to associate PMUs which are not PDC and connected directly. DataRow row = resultSet.Tables["PdcTable"].NewRow(); row["ID"] = 0; row["Acronym"] = string.Empty; row["Name"] = "Devices Connected Directly"; row["CompanyName"] = string.Empty; row["Enabled"] = false; resultSet.Tables["PdcTable"].Rows.Add(row); // Get Non-PDC device list. resultSet.Tables.Add(database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, Acronym, Name, CompanyName, ProtocolName, VendorDeviceName, " + "ParentAcronym, Enabled FROM DeviceDetail WHERE NodeID = {0} AND IsConcentrator = {1} AND Enabled = {2} ORDER BY Acronym", "nodeID", "isConcentrator", "enabled"), DefaultTimeout, database.CurrentNodeID(), database.Bool(false), database.Bool(true)).Copy()); resultSet.Tables[1].TableName = "DeviceTable"; // Get non-statistical measurement list resultSet.Tables.Add(database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, DeviceID, SignalID, PointID, PointTag, SignalReference, " + "SignalAcronym, Description, SignalName, EngineeringUnits, HistorianAcronym, Subscribed, Internal FROM MeasurementDetail WHERE NodeID = {0} AND " + "SignalAcronym <> {1} ORDER BY SignalReference", "nodeID", "signalAcronym"), DefaultTimeout, database.CurrentNodeID(), "STAT").Copy()); resultSet.Tables[2].TableName = "MeasurementTable"; // Query for any non-statistical measurements that are subscribed via GEP, but are a part of another node in the same database // IMPORTANT: Make sure columns in this external node query exactly match those of the previous primary measurement query DataTable otherMeasurements = database.Connection.RetrieveData(database.AdapterType, database.ParameterizedQueryString("SELECT ID, 0 AS DeviceID, SignalID, PointID, PointTag, SignalReference, " + "SignalAcronym, Description, SignalName, EngineeringUnits, HistorianAcronym, Subscribed, Internal FROM MeasurementDetail WHERE NodeID <> {0} AND " + "SignalAcronym <> {1} AND Subscribed <> 0 ORDER BY SignalReference", "nodeID", "signalAcronym"), DefaultTimeout, database.CurrentNodeID(), "STAT"); realTimeStreamList = new ObservableCollection <RealTimeStream>( from pdc in resultSet.Tables["PdcTable"].AsEnumerable() let settings = pdc.Field <string>("ConnectionString").ToNonNullString().ParseKeyValuePairs() select new RealTimeStream { ID = pdc.ConvertField <int>("ID"), Acronym = string.IsNullOrEmpty(pdc.Field <string>("Acronym")) ? "DIRECT CONNECTED" : pdc.Field <string>("Acronym"), Name = pdc.Field <string>("Name"), CompanyName = pdc.Field <string>("CompanyName"), StatusColor = string.IsNullOrEmpty(pdc.Field <string>("Acronym")) ? "Transparent" : "Gray", Enabled = Convert.ToBoolean(pdc.Field <object>("Enabled")), Expanded = false, DeviceList = new ObservableCollection <RealTimeDevice>( from device in resultSet.Tables["DeviceTable"].AsEnumerable() where device.Field <string>("ParentAcronym").ToNonNullString() == pdc.Field <string>("Acronym") select new RealTimeDevice { ID = device.ConvertNullableField <int>("ID"), Acronym = device.Field <string>("Acronym"), Name = device.Field <string>("Name"), ProtocolName = device.Field <string>("ProtocolName"), VendorDeviceName = device.Field <string>("VendorDeviceName"), ParentAcronym = string.IsNullOrEmpty(device.Field <string>("ParentAcronym")) ? "DIRECT CONNECTED" : device.Field <string>("ParentAcronym"), Expanded = false, StatusColor = device.ConvertNullableField <int>("ID") == null ? "Transparent" : "Gray", Enabled = Convert.ToBoolean(device.Field <object>("Enabled")), MeasurementList = new ObservableCollection <RealTimeMeasurement>( from measurement in resultSet.Tables["MeasurementTable"].AsEnumerable() where measurement.ConvertNullableField <int>("DeviceID") == device.ConvertNullableField <int>("ID") && (measurement.ConvertField <bool>("Subscribed") || measurement.ConvertField <bool>("Internal") || (settings.ContainsKey("securityMode") && settings["securityMode"].Equals("None", StringComparison.OrdinalIgnoreCase))) //We will only display measurements which are internal or subscribed to avoid confusion. select new RealTimeMeasurement { ID = measurement.Field <string>("ID"), DeviceID = measurement.ConvertNullableField <int>("DeviceID"), SignalID = Guid.Parse(measurement.Field <object>("SignalID").ToString()), PointID = measurement.ConvertField <int>("PointID"), PointTag = measurement.Field <string>("PointTag"), SignalReference = measurement.Field <string>("SignalReference"), Description = measurement.Field <string>("description"), SignalName = measurement.Field <string>("SignalName"), SignalAcronym = measurement.Field <string>("SignalAcronym"), EngineeringUnit = measurement.Field <string>("SignalAcronym") == "FLAG" ? "Hex" : measurement.Field <string>("EngineeringUnits"), Expanded = false, Selected = false, Selectable = measurement.Field <string>("SignalAcronym") == "IPHM" || measurement.Field <string>("SignalAcronym") == "IPHA" || measurement.Field <string>("SignalAcronym") == "VPHM" || measurement.Field <string>("SignalAcronym") == "VPHA" || measurement.Field <string>("SignalAcronym") == "FREQ", LongTimeTag = "N/A", TimeTag = "N/A", Value = "--", Quality = "N/A", Foreground = new SolidColorBrush(Color.FromArgb(255, 0, 0, 0)) } ) } ) } ); if (otherMeasurements.Rows.Count > 0) { // Add subscribed measurements from other nodes realTimeStreamList.Add(new RealTimeStream { ID = 0, Acronym = "SUBSCRIBED", Name = "Subscribed Measurements", CompanyName = string.Empty, StatusColor = "Transparent", Enabled = false, Expanded = false, DeviceList = new ObservableCollection <RealTimeDevice>( otherMeasurements.Rows .Cast <DataRow>() .Where(measurement => measurement.ConvertNullableField <int>("DeviceID") == null) .Select(measurement => measurement.Field <string>("SignalReference")) .Select(GetSourceName) .Distinct() .Select(source => new RealTimeDevice { ID = 0, Acronym = source, Name = source, ProtocolName = string.Empty, VendorDeviceName = string.Empty, ParentAcronym = "SUBSCRIBED", Expanded = false, StatusColor = "Gray", Enabled = false, MeasurementList = new ObservableCollection <RealTimeMeasurement>( otherMeasurements.Rows .Cast <DataRow>() .Where(measurement => measurement.ConvertNullableField <int>("DeviceID") == null && measurement.Field <string>("SignalReference").StartsWith(source)) .Select(measurement => new RealTimeMeasurement { ID = measurement.Field <string>("ID"), DeviceID = measurement.ConvertNullableField <int>("DeviceID"), SignalID = Guid.Parse(measurement.Field <object>("SignalID").ToString()), PointID = measurement.ConvertField <int>("PointID"), PointTag = measurement.Field <string>("PointTag"), SignalReference = measurement.Field <string>("SignalReference"), Description = measurement.Field <string>("description"), SignalName = measurement.Field <string>("SignalName"), SignalAcronym = measurement.Field <string>("SignalAcronym"), EngineeringUnit = measurement.Field <string>("SignalAcronym") == "FLAG" ? "Hex" : measurement.Field <string>("EngineeringUnits"), Expanded = false, Selected = false, Selectable = measurement.Field <string>("SignalAcronym") == "IPHM" || measurement.Field <string>("SignalAcronym") == "IPHA" || measurement.Field <string>("SignalAcronym") == "VPHM" || measurement.Field <string>("SignalAcronym") == "VPHA" || measurement.Field <string>("SignalAcronym") == "FREQ", LongTimeTag = "N/A", TimeTag = "N/A", Value = "--", Quality = "N/A", Foreground = new SolidColorBrush(Color.FromArgb(255, 0, 0, 0)) }) ) }) ) }); } if (resultSet.Tables["MeasurementTable"].Select("DeviceID IS NULL").Length > 0) { // Add direct connected measurements with no associated device (DeviceID IS NULL) realTimeStreamList.Add(new RealTimeStream { ID = 0, Acronym = "CALCULATED", Name = "Calculated Measurements", CompanyName = string.Empty, StatusColor = "Transparent", Enabled = false, Expanded = false, DeviceList = new ObservableCollection <RealTimeDevice>( resultSet.Tables["MeasurementTable"].Rows .Cast <DataRow>() .Where(measurement => measurement.ConvertNullableField <int>("DeviceID") == null) .Select(measurement => measurement.Field <string>("SignalReference")) .Select(GetSourceName) .Distinct() .Select(source => new RealTimeDevice { ID = 0, Acronym = source, Name = source, ProtocolName = string.Empty, VendorDeviceName = string.Empty, ParentAcronym = "CALCULATED", Expanded = false, StatusColor = "Gray", Enabled = false, MeasurementList = new ObservableCollection <RealTimeMeasurement>( resultSet.Tables["MeasurementTable"].Rows .Cast <DataRow>() .Where(measurement => measurement.ConvertNullableField <int>("DeviceID") == null && measurement.Field <string>("SignalReference").StartsWith(source)) .Select(measurement => new RealTimeMeasurement { ID = measurement.Field <string>("ID"), DeviceID = measurement.ConvertNullableField <int>("DeviceID"), SignalID = Guid.Parse(measurement.Field <object>("SignalID").ToString()), PointID = measurement.ConvertField <int>("PointID"), PointTag = measurement.Field <string>("PointTag"), SignalReference = measurement.Field <string>("SignalReference"), Description = measurement.Field <string>("description"), SignalName = measurement.Field <string>("SignalName"), SignalAcronym = measurement.Field <string>("SignalAcronym"), EngineeringUnit = measurement.Field <string>("SignalAcronym") == "FLAG" ? "Hex" : measurement.Field <string>("EngineeringUnits"), Expanded = false, Selected = false, Selectable = measurement.Field <string>("SignalAcronym") == "IPHM" || measurement.Field <string>("SignalAcronym") == "IPHA" || measurement.Field <string>("SignalAcronym") == "VPHM" || measurement.Field <string>("SignalAcronym") == "VPHA" || measurement.Field <string>("SignalAcronym") == "FREQ", LongTimeTag = "N/A", TimeTag = "N/A", Value = "--", Quality = "N/A", Foreground = new SolidColorBrush(Color.FromArgb(255, 0, 0, 0)) }) ) }) ) }); } // Assign parent references for real-time measurements foreach (RealTimeStream stream in realTimeStreamList) { foreach (RealTimeDevice device in stream.DeviceList) { device.Parent = stream; foreach (RealTimeMeasurement measurement in device.MeasurementList) { measurement.Parent = device; } } } return(realTimeStreamList); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
static int Main(string[] args) { // System settings ConfigurationFile configFile = ConfigurationFile.Current; CategorizedSettingsElementCollection systemSettings = configFile.Settings["systemSettings"]; systemSettings.Add("NodeID", Guid.NewGuid().ToString(), "Unique Node ID"); Guid nodeID = systemSettings["NodeID"].ValueAs <Guid>(); string connectionString = systemSettings["ConnectionString"].Value; string nodeIDQueryString = null; string parameterizedQuery; int protocolID, signalTypeID; // Define guid with query string delimiters according to database needs Dictionary <string, string> settings = connectionString.ParseKeyValuePairs(); string setting; if (settings.TryGetValue("Provider", out setting)) { // Check if provider is for Access since it uses braces as Guid delimiters if (setting.StartsWith("Microsoft.Jet.OLEDB", StringComparison.OrdinalIgnoreCase)) { nodeIDQueryString = "{" + nodeID + "}"; } } if (string.IsNullOrWhiteSpace(nodeIDQueryString)) { nodeIDQueryString = "'" + nodeID + "'"; } using (AdoDataConnection database = new AdoDataConnection("systemSettings")) { IDbConnection connection = database.Connection; if (Convert.ToInt32(connection.ExecuteScalar("SELECT COUNT(*) FROM Protocol WHERE Acronym='WAV'")) == 0) { if (database.IsSQLServer || database.IsJetEngine) { connection.ExecuteNonQuery("INSERT INTO Protocol(Acronym, Name, [Type], Category, AssemblyName, TypeName) VALUES('WAV', 'Wave Form Input Adapter', 'Frame', 'Audio', 'WavInputAdapter.dll', 'WavInputAdapter.WavInputAdapter')"); } else { connection.ExecuteNonQuery("INSERT INTO Protocol(Acronym, Name, Type, Category, AssemblyName, TypeName) VALUES('WAV', 'Wave Form Input Adapter', 'Frame', 'Audio', 'WavInputAdapter.dll', 'WavInputAdapter.WavInputAdapter')"); } } protocolID = Convert.ToInt32(connection.ExecuteScalar("SELECT ID FROM Protocol WHERE Acronym='WAV'")); // Typically these values should be defined as analogs, however, we use a voltage magnitude signal type // since these types of values can be better graphed with auto-scaling in the visualization tools signalTypeID = Convert.ToInt32(connection.ExecuteScalar("SELECT ID FROM SignalType WHERE Acronym='VPHM'")); //signalTypeID = Convert.ToInt32(connection.ExecuteScalar("SELECT ID FROM SignalType WHERE Acronym='ALOG'")); string pathRoot = FilePath.GetDirectoryName((args.Length > 0) ? args[0] : systemSettings["MusicDirectory"].Value); string sourcePath = Path.Combine(pathRoot, "*" + Path.DirectorySeparatorChar + "*.wav"); foreach (string sourceFileName in FilePath.GetFileList(sourcePath)) { WaveFile sourceWave; string fileName = FilePath.GetFileName(sourceFileName); char[] invalidChars = new[] { '\'', '[', ']', '(', ')', ',', '-', '.' }; Console.WriteLine("Loading metadata for \"{0}\"...\r\n", fileName); sourceWave = WaveFile.Load(sourceFileName, false); fileName = FilePath.GetFileNameWithoutExtension(fileName).RemoveDuplicateWhiteSpace().RemoveCharacters(invalidChars.Contains).Trim(); string acronym = fileName.Replace(' ', '_').ToUpper() + "_" + (int)(sourceWave.SampleRate / SI.Kilo) + "KHZ"; string name = GenerateSongName(sourceWave, fileName); Console.WriteLine(" Acronym = {0}", acronym); Console.WriteLine(" Name = {0}", name); Console.WriteLine(""); // Check to see if device exists if (Convert.ToInt32(connection.ExecuteScalar(database.ParameterizedQueryString("SELECT COUNT(*) FROM Device WHERE Acronym = {0}", "acronym"), acronym)) == 0) { parameterizedQuery = database.ParameterizedQueryString("INSERT INTO Device(NodeID, Acronym, Name, ProtocolID, FramesPerSecond, " + "MeasurementReportingInterval, ConnectionString, Enabled) VALUES(" + nodeIDQueryString + ", {0}, {1}, {2}, {3}, {4}, {5}, {6})", "acronym", "name", "protocolID", "framesPerSecond", "measurementReportingInterval", "connectionString", "enabled"); // Insert new device record connection.ExecuteNonQuery(parameterizedQuery, acronym, name, protocolID, sourceWave.SampleRate, 1000000, string.Format("wavFileName={0}; connectOnDemand=true; outputSourceIDs={1}", FilePath.GetAbsolutePath(sourceFileName), acronym), database.Bool(true)); int deviceID = Convert.ToInt32(connection.ExecuteScalar(database.ParameterizedQueryString("SELECT ID FROM Device WHERE Acronym = {0}", "acronym"), acronym)); string pointTag; // Add a measurement for each defined wave channel for (int i = 0; i < sourceWave.Channels; i++) { int index = i + 1; pointTag = acronym + ":WAVA" + index; parameterizedQuery = database.ParameterizedQueryString("INSERT INTO Measurement(DeviceID, PointTag, SignalTypeID, SignalReference, Description, " + "Enabled) VALUES({0}, {1}, {2}, {3}, {4}, {5})", "deviceID", "pointTag", "signalTypeID", "signalReference", "description", "enabled"); // Insert new measurement record connection.ExecuteNonQuery(parameterizedQuery, (object)deviceID, pointTag, signalTypeID, acronym + "-AV" + index, name + " - channel " + index, database.Bool(true)); //index = Convert.ToInt32(connection.ExecuteScalar(database.ParameterizedQueryString("SELECT PointID FROM Measurement WHERE PointTag = {0}", "pointTag"), pointTag)); } // Disable all non analog measurements that may be associated with this device connection.ExecuteNonQuery(database.ParameterizedQueryString("UPDATE Measurement SET Enabled = {0} WHERE DeviceID = {1} AND SignalTypeID <> {2}", "enabled", "deviceID", "signalTypeID"), database.Bool(false), deviceID, signalTypeID); } } } return(0); }
/// <summary> /// Deletes specified <see cref="OutputStreamDevicePhasor"/> record and its associated measurements from database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="outputStreamDevicePhasorID">ID of the record to be deleted.</param> /// <returns>String, for display use, indicating success.</returns> public static string Delete(AdoDataConnection database, int outputStreamDevicePhasorID) { bool createdConnection = false; int adapterID; int outputStreamDeviceID; int deletedSignalReferenceIndex; int presentDevicePhasorCount; string angleSignalReference; string angleSignalReferenceBase; string magnitudeSignalReference; string magnitudeSignalReferenceBase; string previousAngleSignalReference; string previousMagnitudeSignalReference; string nextAngleSignalReference = string.Empty; string nextMagnitudeSignalReference = string.Empty; string lastAffectedMeasurementsMessage = string.Empty; try { createdConnection = CreateConnection(ref database); // Setup current user context for any delete triggers CommonFunctions.SetCurrentUserContext(database); GetDeleteMeasurementDetails(database, outputStreamDevicePhasorID, out angleSignalReference, out magnitudeSignalReference, out adapterID, out outputStreamDeviceID); // Delete angle/magnitude of measurements if they exist database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM OutputStreamMeasurement WHERE SignalReference = {0} AND AdapterID = {1}", "signalReference", "adapterID"), DefaultTimeout, angleSignalReference, adapterID); database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM OutputStreamMeasurement WHERE SignalReference = {0} AND AdapterID = {1}", "signalReference", "adapterID"), DefaultTimeout, magnitudeSignalReference, adapterID); presentDevicePhasorCount = Convert.ToInt32(database.Connection.ExecuteScalar(database.ParameterizedQueryString("SELECT COUNT(*) FROM OutputStreamDevicePhasor WHERE OutputStreamDeviceID = {0}", "outputStreamDeviceID"), DefaultTimeout, outputStreamDeviceID)); // Using signal reference angle/mag deleted build the next signal reference (increment by 1) int.TryParse(Regex.Match(magnitudeSignalReference, @"\d+$").Value, out deletedSignalReferenceIndex); angleSignalReferenceBase = Regex.Replace(angleSignalReference, @"\d+$", ""); magnitudeSignalReferenceBase = Regex.Replace(magnitudeSignalReference, @"\d+$", ""); for (int i = deletedSignalReferenceIndex; i < presentDevicePhasorCount; i++) { // We will be modifying the measurements with signal reference index i+1 to have signal refrence index i. previousAngleSignalReference = string.Format("{0}{1}", angleSignalReferenceBase, i); nextAngleSignalReference = string.Format("{0}{1}", angleSignalReferenceBase, i + 1); previousMagnitudeSignalReference = string.Format("{0}{1}", magnitudeSignalReferenceBase, i); nextMagnitudeSignalReference = string.Format("{0}{1}", magnitudeSignalReferenceBase, i + 1); // For angle... // Obtain details of measurements after the deleted measurements, then modify the signal reference (decrement by 1) and put it back OutputStreamMeasurement outputStreamMeasurement = GetOutputMeasurementDetails(database, nextAngleSignalReference, adapterID); outputStreamMeasurement.SignalReference = previousAngleSignalReference; OutputStreamMeasurement.Save(database, outputStreamMeasurement); // For magnitude... outputStreamMeasurement = GetOutputMeasurementDetails(database, nextMagnitudeSignalReference, adapterID); outputStreamMeasurement.SignalReference = previousMagnitudeSignalReference; OutputStreamMeasurement.Save(database, outputStreamMeasurement); } database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM OutputStreamDevicePhasor WHERE ID = {0}", "outputStreamDevicePhasorID"), DefaultTimeout, outputStreamDevicePhasorID); return("OutputStreamDevicePhasor deleted successfully"); } catch (Exception ex) { if (!string.IsNullOrEmpty(nextMagnitudeSignalReference)) { lastAffectedMeasurementsMessage = string.Format("{0}(Last affected measurements: {1} {2})", Environment.NewLine, nextMagnitudeSignalReference, nextAngleSignalReference); } CommonFunctions.LogException(database, "OutputStreamDevicePhasor.Delete", ex); MessageBoxResult dialogResult = MessageBox.Show(string.Format("Could not delete or modify measurements.{0}Do you still wish to delete this Phasor?{1}", Environment.NewLine, lastAffectedMeasurementsMessage), "", MessageBoxButton.YesNo); if (dialogResult == MessageBoxResult.Yes) { database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM OutputStreamDevicePhasor WHERE ID = {0}", "outputStreamDevicePhasorID"), DefaultTimeout, outputStreamDevicePhasorID); return("OutputStreamDevicePhasor deleted successfully but failed to modify all measurements "); } else { Exception exception = ex.InnerException ?? ex; return(string.Format("Delete OutputStreamDevicePhasor was unsuccessful: {0}", exception.Message)); } } finally { if (createdConnection && database != null) { database.Dispose(); } } }
private void HomeUserControl_Loaded(object sender, RoutedEventArgs e) { if (!CommonFunctions.CurrentPrincipal.IsInRole("Administrator,Editor")) { ButtonInputWizard.IsEnabled = false; } m_windowsServiceClient = CommonFunctions.GetWindowsServiceClient(); if (m_windowsServiceClient == null || m_windowsServiceClient.Helper.RemotingClient.CurrentState != ClientState.Connected) { // TODO: Disable any buttons here that will not function without service connectivity } else { m_windowsServiceClient.Helper.ReceivedServiceResponse += Helper_ReceivedServiceResponse; CommonFunctions.SendCommandToService("Health -actionable"); CommonFunctions.SendCommandToService("Version -actionable"); CommonFunctions.SendCommandToService("Status -actionable"); CommonFunctions.SendCommandToService("Time -actionable"); m_eventHandlerRegistered = true; } m_refreshTimer = new DispatcherTimer(); m_refreshTimer.Interval = TimeSpan.FromSeconds(5); m_refreshTimer.Tick += RefreshTimer_Tick; m_refreshTimer.Start(); if (IntPtr.Size == 8) { TextBlockInstance.Text = "64-bit"; } else { TextBlockInstance.Text = "32-bit"; } TextBlockLocalTime.Text = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"); Version appVersion = AssemblyInfo.EntryAssembly.Version; TextBlockManagerVersion.Text = appVersion.Major + "." + appVersion.Minor + "." + appVersion.Build + ".0"; try { using (AdoDataConnection database = new AdoDataConnection(CommonFunctions.DefaultSettingsCategory)) { TextBlockDatabaseType.Text = database.DatabaseType.ToString(); try { if (database.IsSqlite || database.IsJetEngine) { // Extract database file name from connection string for file centric databases TextBlockDatabaseName.Text = FilePath.GetFileName(database.Connection.ConnectionString.ParseKeyValuePairs()["Data Source"]); } else if (database.IsOracle) { // Extract user name from connection string for Oracle databases TextBlockDatabaseName.Text = database.Connection.ConnectionString.ParseKeyValuePairs()["User Id"]; } else { TextBlockDatabaseName.Text = database.Connection.Database; } } catch { // Fall back on database name if file anything fails TextBlockDatabaseName.Text = database.Connection.Database; } } } catch { TextBlockDatabaseName.Text = "Not Avaliable"; } try { using (UserInfo info = new UserInfo(CommonFunctions.CurrentUser)) { if (info.Exists) { TextBlockUser.Text = info.LoginID; } else { TextBlockUser.Text = CommonFunctions.CurrentUser; } } } catch { TextBlockUser.Text = CommonFunctions.CurrentUser; } ((HorizontalAxis)ChartPlotterDynamic.MainHorizontalAxis).LabelProvider.LabelStringFormat = ""; //Remove legend on the right. Panel legendParent = (Panel)ChartPlotterDynamic.Legend.ContentGrid.Parent; if (legendParent != null) { legendParent.Children.Remove(ChartPlotterDynamic.Legend.ContentGrid); } ChartPlotterDynamic.NewLegendVisible = false; m_xAxisDataCollection = new int[NumberOfPointsToPlot]; for (int i = 0; i < NumberOfPointsToPlot; i++) { m_xAxisDataCollection[i] = i; } m_xAxisBindingCollection = new EnumerableDataSource <int>(m_xAxisDataCollection); m_xAxisBindingCollection.SetXMapping(x => x); LoadComboBoxDeviceAsync(); }
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 Methods /// <summary> /// Gets a dependency resolver. /// </summary> /// <param name="webServer"><see cref="WebServer"/> instance to use for controller; uses default instance if not provided.</param> /// <param name="defaultWebPage">Default web page name to use for controller; uses "index.html" if not provided.</param> /// <param name="model">Reference to model to use when rendering Razor templates, if any.</param> /// <param name="modelType">Type of <paramref name="model"/>, if any.</param> /// <param name="connection"><see cref="AdoDataConnection"/> to use, if any.</param> /// <returns>Dependency resolver for the specified parameters.</returns> public static IDependencyResolver GetDependencyResolver(WebServer webServer, string defaultWebPage = null, object model = null, Type modelType = null, AdoDataConnection connection = null) { return(new WebPageControllerResolver(webServer, defaultWebPage, model, modelType, connection)); }
/// <summary> /// Loads <see cref="Node"/> information as an <see cref="ObservableCollection{T}"/> style list. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <returns>Collection of <see cref="Subscriber"/>.</returns> public static ObservableCollection<Subscriber> Load(AdoDataConnection database) { ObservableCollection<Subscriber> subscriberList; DataTable subscriberTable; bool createdConnection = false; string query; SslPolicyErrors validPolicyErrors; X509ChainStatusFlags validChainFlags; try { createdConnection = CreateConnection(ref database); subscriberList = new ObservableCollection<Subscriber>(); query = database.ParameterizedQueryString("SELECT ID, NodeID, Acronym, Name, SharedSecret, AuthKey, ValidIPAddresses, RemoteCertificateFile," + " ValidPolicyErrors, ValidChainFlags, AccessControlFilter, Enabled FROM Subscriber WHERE NodeID = {0} ORDER BY Name", "nodeID"); subscriberTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID()); foreach (DataRow row in subscriberTable.Rows) { subscriberList.Add(new Subscriber() { ID = database.Guid(row, "ID"), NodeID = database.Guid(row, "NodeID"), Acronym = row.Field<string>("Acronym"), Name = row.Field<string>("Name"), SharedSecret = row.Field<string>("SharedSecret"), AuthKey = row.Field<string>("AuthKey"), ValidIPAddresses = row.Field<string>("ValidIPAddresses"), RemoteCertificateFile = row.Field<string>("RemoteCertificateFile"), ValidPolicyErrors = Enum.TryParse(row.Field<string>("ValidPolicyErrors"), out validPolicyErrors) ? validPolicyErrors : (SslPolicyErrors?)null, ValidChainFlags = Enum.TryParse(row.Field<string>("ValidChainFlags"), out validChainFlags) ? validChainFlags : (X509ChainStatusFlags?)null, AccessControlFilter = row.Field<string>("AccessControlFilter"), Enabled = Convert.ToBoolean(row.Field<object>("Enabled")), AllowedMeasurementGroups = GetAllowedMeasurementGroups(database, database.Guid(row, "ID")), DeniedMeasurementGroups = GetDeniedMeasurementGroups(database, database.Guid(row, "ID")), AvailableMeasurementGroups = GetAvailableMeasurementGroups(database, database.Guid(row, "ID")), StatusColor = "gray", Version = "" }); } return subscriberList; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Retrieves <see cref="Dictionary{T1,T2}"/> type collection. /// </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}"/> type collection.</returns> /// <remarks>This is only a place holder method with no implementation.</remarks> public static Dictionary <int, string> GetLookupList(AdoDataConnection database, bool isOptional = false) { return(null); }
/// <summary> /// Adds measurement groups to <see cref="Subscriber"/>. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="subscriberID">ID of the <see cref="Subscriber"/> to which measurements to be added.</param> /// <param name="measurementGroupsToBeAdded">List of <see cref="MeasurementGroup"/> IDs to be added.</param> /// <param name="allowed">boolean flag to indicate if measurement groups are allowed or denied.</param> /// <returns>string, indicating success for UI display.</returns> public static int AddMeasurementGroups(AdoDataConnection database, Guid subscriberID, List<int> measurementGroupsToBeAdded, bool allowed) { const string QueryFormat = "INSERT INTO SubscriberMeasurementGroup (NodeID, SubscriberID, MeasurementGroupID, Allowed, UpdatedOn, UpdatedBy, CreatedOn, CreatedBy) " + "SELECT {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7} " + "WHERE (SELECT COUNT(*) FROM SubscriberMeasurementGroup WHERE SubscriberID = {1} AND MeasurementGroupID = {2}) = 0"; bool createdConnection = false; int rowsAffected = 0; try { createdConnection = CreateConnection(ref database); foreach (int id in measurementGroupsToBeAdded) { rowsAffected += database.ExecuteNonQuery(DefaultTimeout, QueryFormat, CommonFunctions.CurrentNodeID(), subscriberID, id, allowed, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser); } return rowsAffected; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Saves <see cref="RealTimeStream"/> information into the database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="stream">Information about <see cref="RealTimeStream"/></param> /// <returns>String, for display use, indicating success.</returns> /// <remarks>This is only a place holder method with no implementation.</remarks> public static string Save(AdoDataConnection database, RealTimeStream stream) { return(string.Empty); }
/// <summary> /// Deletes specified <see cref="Subscriber"/> record from database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="id">ID of the record to be deleted.</param> /// <returns>String, for display use, indicating success.</returns> public static string Delete(AdoDataConnection database, Guid id) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); // Setup current user context for any delete triggers CommonFunctions.SetCurrentUserContext(database); database.Connection.ExecuteNonQuery(database.ParameterizedQueryString("DELETE FROM Subscriber WHERE ID = {0}", "id"), DefaultTimeout, database.Guid(id)); try { CommonFunctions.SendCommandToService("ReloadConfig"); } catch (Exception ex) { return "Subscriber deleted successfully. Failed to send ReloadConfig command to backend service." + Environment.NewLine + ex.Message; } return "Subscriber deleted successfully"; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Deletes <see cref="RealTimeStream"/> record from the database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="streamID">ID of the record to be deleted.</param> /// <returns>String, for display use, indicating success.</returns> /// <remarks>This is only a place holder method with no implementation.</remarks> public static string Delete(AdoDataConnection database, int streamID) { return(string.Empty); }
/// <summary> /// Creates a data operation to run the validations in this class. /// </summary> /// <param name="database">Database connection to use for creating the data operation</param> private static void CreateDataOperation(AdoDataConnection database) { database.ExecuteNonQuery($"INSERT INTO DataOperation(Description, AssemblyName, TypeName, MethodName, Enabled) VALUES ('Power Calculation Validations', 'PowerCalculations.dll', '{typeof(PowerCalculationConfigurationValidation).FullName}', 'ValidatePowerCalculationConfigurations', 1)"); }
public void getContourAnimationFrame() { int animationID = Convert.ToInt32(HttpContext.Current.Request.QueryString["animation"]); int frameIndex = Convert.ToInt32(HttpContext.Current.Request.QueryString["frame"]); byte[] frameImage; using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter))) { frameImage = connection.ExecuteScalar<byte[]>("SELECT FrameImage FROM ContourAnimationFrame WHERE ContourAnimationID = {0} AND FrameIndex = {1}", animationID, frameIndex); } HttpContext.Current.Response.ContentType = "image/png"; HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("inline;filename=ContourFrame{0}x{1}.png", animationID, frameIndex)); HttpContext.Current.Response.BinaryWrite(frameImage); }
/// <summary> /// Creates a data operation to run the validations in this class. /// </summary> /// <param name="database">Database connection to use for creating the data operation</param> private static void CreateAdapterInstance(AdoDataConnection database) { database.ExecuteNonQuery($"INSERT INTO CustomActionAdapter(NodeID, AdapterName, AssemblyName, TypeName, ConnectionString, Enabled) VALUES ('{ConfigurationFile.Current.Settings["systemSettings"]["NodeID"].ValueAs<Guid>()}', 'PHASOR!POWERCALC', 'PowerCalculations.dll', '{typeof(PowerMultiCalculatorAdapter).FullName}', 'FramesPerSecond=30; LagTime=5.0; LeadTime=3.0', 1)"); }
private PiecewiseLinearFunction GetColorScale(ContourQuery contourQuery) { DataTable colorScale; using (AdoDataConnection conn = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter))) { string query = "SELECT " + " ContourColorScalePoint.Value, " + " ContourColorScalePoint.Color " + "FROM " + " ContourColorScale JOIN " + " ContourColorScalePoint ON ContourColorScalePoint.ContourColorScaleID = ContourColorScale.ID " + "WHERE ContourColorScale.Name = {0} " + "ORDER BY ContourColorScalePoint.OrderID"; colorScale = conn.RetrieveData(query, contourQuery.ColorScaleName); } double[] colorDomain = colorScale .Select() .Select(row => row.ConvertField<double>("Value")) .ToArray(); double[] colorRange = colorScale .Select() .Select(row => (double)(uint)row.ConvertField<int>("Color")) .ToArray(); return new PiecewiseLinearFunction() .SetDomain(colorDomain) .SetRange(colorRange); }
/// <summary> /// Loads configuration from the database and configures adapter to run with that configuration /// </summary> public override void Initialize() { base.Initialize(); HashSet <IMeasurement> outputMeasurements = new HashSet <IMeasurement>(); m_configuredCalculations = new List <PowerCalculation>(); m_adjustmentStrategies = new Dictionary <MeasurementKey, VoltageAdjustmentStrategy>(); m_averageCalculationsPerFrame = new RunningAverage(); m_averageCalculationTime = new RunningAverage(); m_averageTotalCalculationTime = new RunningAverage(); m_lastActivePowerCalculations = new ConcurrentQueue <IMeasurement>(); m_lastReactivePowerCalculations = new ConcurrentQueue <IMeasurement>(); m_lastApparentPowerCalculations = new ConcurrentQueue <IMeasurement>(); if (!Settings.TryGetValue(nameof(TableName), out string tableName)) { tableName = DefaultTableName; } string query = "SELECT " + // 0 1 2 3 4 5 "ID, CircuitDescription, VoltageAngleSignalID, VoltageMagSignalID, CurrentAngleSignalID, CurrentMagSignalID, " + // 6 7 8 "ActivePowerOutputSignalID, ReactivePowerOutputSignalID, ApparentPowerOutputSignalID FROM " + tableName + " " + "WHERE NodeId = {0} AND Enabled <> 0 "; using (AdoDataConnection database = new AdoDataConnection("systemSettings")) using (IDataReader rdr = database.ExecuteReader(query, ConfigurationFile.Current.Settings["systemSettings"]["NodeID"].ValueAs <Guid>())) { while (rdr.Read()) { m_configuredCalculations.Add(new PowerCalculation { PowerCalculationID = rdr.GetInt32(0), CircuitDescription = rdr.GetString(1), VoltageAngleMeasurementKey = MeasurementKey.LookUpBySignalID(Guid.Parse(rdr[2].ToString())), VoltageMagnitudeMeasurementKey = MeasurementKey.LookUpBySignalID(Guid.Parse(rdr[3].ToString())), CurrentAngleMeasurementKey = MeasurementKey.LookUpBySignalID(Guid.Parse(rdr[4].ToString())), CurrentMagnitudeMeasurementKey = MeasurementKey.LookUpBySignalID(Guid.Parse(rdr[5].ToString())), ActivePowerOutputMeasurement = AddOutputMeasurement(Guid.Parse(rdr[6].ToString()), outputMeasurements), ReactivePowerOutputMeasurement = AddOutputMeasurement(Guid.Parse(rdr[7].ToString()), outputMeasurements), ApparentPowerOutputMeasurement = AddOutputMeasurement(Guid.Parse(rdr[8].ToString()), outputMeasurements) }); } } if (m_configuredCalculations.Any()) { InputMeasurementKeys = m_configuredCalculations.SelectMany(pc => new[] { pc.CurrentAngleMeasurementKey, pc.CurrentMagnitudeMeasurementKey, pc.VoltageAngleMeasurementKey, pc.VoltageMagnitudeMeasurementKey }).ToArray(); } else { throw new InvalidOperationException("Skipped initialization of power calculator: no defined power calculations..."); } if (outputMeasurements.Any()) { OutputMeasurements = outputMeasurements.ToArray(); } Dictionary <string, string> settings = Settings; if (settings.TryGetValue(nameof(AlwaysProduceResult), out string setting)) { AlwaysProduceResult = setting.ParseBoolean(); } if (settings.TryGetValue(nameof(AdjustmentStrategy), out setting) && Enum.TryParse(setting, out VoltageAdjustmentStrategy adjustmentStrategy)) { AdjustmentStrategy = adjustmentStrategy; } if (settings.TryGetValue(nameof(EnableTemporalProcessing), out setting)) { EnableTemporalProcessing = setting.ParseBoolean(); } // Define per power calculation line adjustment strategies foreach (PowerCalculation powerCalculation in m_configuredCalculations) { if (powerCalculation.VoltageMagnitudeMeasurementKey is null || string.IsNullOrWhiteSpace(powerCalculation.CircuitDescription)) { continue; } try { Dictionary <string, string> circuitSettings = powerCalculation.CircuitDescription.ParseKeyValuePairs(); if (circuitSettings.TryGetValue(nameof(AdjustmentStrategy), out setting) && Enum.TryParse(setting, out adjustmentStrategy)) { m_adjustmentStrategies[powerCalculation.VoltageMagnitudeMeasurementKey] = adjustmentStrategy; } } catch (Exception ex) { OnStatusMessage(MessageLevel.Warning, $"Failed to parse settings from circuit description \"{powerCalculation.CircuitDescription}\": {ex.Message}"); } } }
public ContourAnimationInfo getContourAnimations(ContourQuery contourQuery) { List<List<TrendingDataLocation>> frames = GetFramesFromHistorian(contourQuery); PiecewiseLinearFunction colorScale = GetColorScale(contourQuery); Func<double, double> colorFunc = colorScale; // The actual startDate is the timestamp of the // first frame after contourQuery.GetStartDate() DateTime startDate = contourQuery.GetStartDate(); int stepSize = contourQuery.StepSize; int startTimeOffset = (int)Math.Ceiling((startDate - startDate.Date).TotalMinutes / stepSize); startDate = startDate.Date.AddMinutes(startTimeOffset * stepSize); double minLat = frames.Min(frame => frame.Min(location => location.Latitude)) - GetLatFromMiles(50.0D); double maxLat = frames.Min(frame => frame.Max(location => location.Latitude)) + GetLatFromMiles(50.0D); double minLng = frames.Min(frame => frame.Min(location => location.Longitude)) - GetLngFromMiles(50.0D, 0.0D); double maxLng = frames.Min(frame => frame.Max(location => location.Longitude)) + GetLngFromMiles(50.0D, 0.0D); GeoCoordinate topLeft = new GeoCoordinate(maxLat, minLng); GeoCoordinate bottomRight = new GeoCoordinate(minLat, maxLng); GSF.Drawing.Point topLeftPoint = s_crs.Translate(topLeft, contourQuery.Resolution); GSF.Drawing.Point bottomRightPoint = s_crs.Translate(bottomRight, contourQuery.Resolution); topLeftPoint = new GSF.Drawing.Point(Math.Floor(topLeftPoint.X), Math.Floor(topLeftPoint.Y)); bottomRightPoint = new GSF.Drawing.Point(Math.Ceiling(bottomRightPoint.X), Math.Ceiling(bottomRightPoint.Y)); topLeft = s_crs.Translate(topLeftPoint, contourQuery.Resolution); bottomRight = s_crs.Translate(bottomRightPoint, contourQuery.Resolution); int width = (int)(bottomRightPoint.X - topLeftPoint.X + 1); int height = (int)(bottomRightPoint.Y - topLeftPoint.Y + 1); int animationID; string timeZoneID = null; using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter))) { connection.ExecuteNonQuery("INSERT INTO ContourAnimation(ColorScaleName, StartTime, EndTime, StepSize) VALUES({0}, {1}, {2}, {3})", contourQuery.ColorScaleName, contourQuery.GetStartDate(), contourQuery.GetEndDate(), contourQuery.StepSize); animationID = connection.ExecuteScalar<int>("SELECT @@IDENTITY"); if (contourQuery.IncludeWeather) timeZoneID = connection.ExecuteScalar<string>("SELECT Value FROM Setting WHERE Name = 'XDATimeZone'"); } GSF.Threading.CancellationToken cancellationToken = new GSF.Threading.CancellationToken(); s_cancellationTokens[animationID] = cancellationToken; ProgressCounter progressCounter = new ProgressCounter(frames.Count); s_progressCounters[animationID] = progressCounter; Action<int> createFrame = i => { List<TrendingDataLocation> frame = frames[i]; IDWFunc idwFunction = GetIDWFunction(contourQuery, frame); uint[] pixelData; if (contourQuery.IncludeWeather) { TimeZoneInfo tzInfo = !string.IsNullOrEmpty(timeZoneID) ? TimeZoneInfo.FindSystemTimeZoneById(timeZoneID) : TimeZoneInfo.Local; // Weather data is only available in 5-minute increments DateTime frameTime = TimeZoneInfo.ConvertTimeToUtc(startDate.AddMinutes(stepSize * i), tzInfo); double minutes = (frameTime - frameTime.Date).TotalMinutes; int weatherMinutes = (int)Math.Ceiling(minutes / 5) * 5; NameValueCollection queryString = HttpUtility.ParseQueryString(string.Empty); queryString["service"] = "WMS"; queryString["request"] = "GetMap"; queryString["layers"] = "nexrad-n0r-wmst"; queryString["format"] = "image/png"; queryString["transparent"] = "true"; queryString["version"] = "1.1.1"; queryString["time"] = frameTime.Date.AddMinutes(weatherMinutes).ToString("o"); queryString["height"] = height.ToString(); queryString["width"] = width.ToString(); queryString["srs"] = "EPSG:3857"; GSF.Drawing.Point topLeftProjected = s_crs.Projection.Project(topLeft); GSF.Drawing.Point bottomRightProjected = s_crs.Projection.Project(bottomRight); queryString["bbox"] = string.Join(",", topLeftProjected.X, bottomRightProjected.Y, bottomRightProjected.X, topLeftProjected.Y); string weatherURL = "http://mesonet.agron.iastate.edu/cgi-bin/wms/nexrad/n0r-t.cgi?" + queryString.ToString(); using (WebClient client = new WebClient()) using (MemoryStream stream = new MemoryStream(client.DownloadData(weatherURL))) using (Bitmap bitmap = new Bitmap(stream)) { pixelData = bitmap.ToPixelData(); } } else { pixelData = new uint[width * height]; } if (cancellationToken.IsCancelled) return; for (int x = 0; x < width; x++) { if (cancellationToken.IsCancelled) return; for (int y = 0; y < height; y++) { if (cancellationToken.IsCancelled) return; if (pixelData[y * width + x] > 0) continue; GSF.Drawing.Point offsetPixel = new GSF.Drawing.Point(topLeftPoint.X + x, topLeftPoint.Y + y); GeoCoordinate pixelCoordinate = s_crs.Translate(offsetPixel, contourQuery.Resolution); double interpolatedValue = idwFunction(pixelCoordinate.Longitude, pixelCoordinate.Latitude); pixelData[y * width + x] = (uint)colorFunc(interpolatedValue); } } if (cancellationToken.IsCancelled) return; using (Bitmap bitmap = BitmapExtensions.FromPixelData(width, pixelData)) using (MemoryStream stream = new MemoryStream()) { bitmap.Save(stream, ImageFormat.Png); using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter))) { connection.ExecuteNonQuery("INSERT INTO ContourAnimationFrame VALUES({0}, {1}, {2})", animationID, i, stream.ToArray()); } } progressCounter.Increment(); }; Task.Run(() => { ICancellationToken token; ProgressCounter counter; Parallel.For(0, frames.Count, createFrame); s_cancellationTokens.TryRemove(animationID, out token); s_progressCounters.TryRemove(animationID, out counter); if (cancellationToken.IsCancelled) { using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter))) { connection.ExecuteNonQuery("DELETE FROM ContourAnimationFrame WHERE ContourAnimationID = {0}", animationID); connection.ExecuteNonQuery("DELETE FROM ContourAnimation WHERE ID = {0}", animationID); } } }); s_cleanUpAnimationOperation.TryRunOnceAsync(); return new ContourAnimationInfo() { AnimationID = animationID, ColorDomain = colorScale.Domain, ColorRange = colorScale.Range, MinLatitude = bottomRight.Latitude, MaxLatitude = topLeft.Latitude, MinLongitude = topLeft.Longitude, MaxLongitude = bottomRight.Longitude, Infos = frames.Select((frame, index) => new ContourInfo() { Locations = frame, URL = string.Format("./mapService.asmx/getContourAnimationFrame?animation={0}&frame={1}", animationID, index), Date = contourQuery.GetStartDate().AddMinutes(index * contourQuery.StepSize).ToString() }).ToList() }; }
/// <summary> /// Retrieves <see cref="ObservableCollection{T}"/> type collection of <see cref="InputWizardDevice"/>. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <returns>Collection of <see cref="InputWizardDevice"/>.</returns> public static ObservableCollection <InputWizardDevice> Load(AdoDataConnection database) { return(new ObservableCollection <InputWizardDevice>()); }
private InverseDistanceWeightingFunction GetIDWFunction(ContourQuery contourQuery, List<TrendingDataLocation> locations = null) { CoordinateReferenceSystem crs = new EPSG3857(); List<double> xList = new List<double>(); List<double> yList = new List<double>(); List<double> valueList = new List<double>(); if ((object)locations == null) locations = GetFrameFromDailySummary(contourQuery); locations .Select(location => { GeoCoordinate Coordinate = new GeoCoordinate(location.Latitude, location.Longitude); double? Value = (contourQuery.DataType == "Average") ? location.Average : (contourQuery.DataType == "Minimum") ? location.Minimum : (contourQuery.DataType == "Maximum") ? location.Maximum : null; return new { Coordinate, Value }; }) .Where(obj => (object)obj.Value != null) .ToList() .ForEach(obj => { xList.Add(obj.Coordinate.Longitude); yList.Add(obj.Coordinate.Latitude); valueList.Add(obj.Value.GetValueOrDefault()); }); if (valueList.Count == 0) { xList.Add(0.0D); yList.Add(0.0D); using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter))) { valueList.Add(connection.ExecuteScalar<double>("SELECT NominalValue FROM ContourColorScale WHERE Name = {0}", contourQuery.ColorScaleName)); } } return new InverseDistanceWeightingFunction() .SetXCoordinates(xList.ToArray()) .SetYCoordinates(yList.ToArray()) .SetValues(valueList.ToArray()) .SetDistanceFunction((x1, y1, x2, y2) => { GeoCoordinate coordinate1 = new GeoCoordinate(y1, x1); GeoCoordinate coordinate2 = new GeoCoordinate(y2, x2); return crs.Distance(coordinate1, coordinate2); }); }
public IHttpActionResult Post(DetailtsForSitesForm form) { try { using (AdoDataConnection XDAconnection = new AdoDataConnection("dbOpenXDA")) using (AdoDataConnection connection = new AdoDataConnection("systemSettings")) { DateTime date = DateTime.Parse(form.targetDate).ToUniversalTime(); DataTable table = XDAconnection.RetrieveData(Query, date, form.siteId, form.context, form.colorScale); IEnumerable <ValueList> chartSettings = new TableOperations <ValueList>(connection).QueryRecordsWhere("GroupID = ( SELECT ID FROM ValueListGroup WHERE Name = 'Chart." + Tab + "')"); int groupID = connection.ExecuteScalar <int>($"SELECT ID FROM ValueListGroup WHERE Name = 'Chart.{Tab}'"); List <string> skipColumns; if (Tab == "Events" || Tab == "Disturbances") { skipColumns = new List <string>() { "EventID", "MeterID", "Site" } } ; else { skipColumns = table.Columns.Cast <DataColumn>().Select(x => x.ColumnName).ToList(); } List <string> columnsToRemove = new List <string>(); foreach (DataColumn column in table.Columns) { if (skipColumns.Contains(column.ColumnName)) { continue; } //if (!chartSettings.Any(x => x.Text == column.ColumnName)) //{ // Random r = new Random(DateTime.UtcNow.Millisecond); // new TableOperations<ValueList>(connection).AddNewRecord(new ValueList // { // Key = 0, // GroupID = groupID, // Text = column.ColumnName, // Flag = false, // AltText1 = "#" + r.Next(256).ToString("X2") + r.Next(256).ToString("X2") + r.Next(256).ToString("X2"), // IsDefault = false, // Hidden = false, // Enabled = true // }); // chartSettings = new TableOperations<ValueList>(connection).QueryRecordsWhere("GroupID = ( SELECT ID FROM ValueListGroup WHERE Name = 'Chart." + Tab + "')"); //} ValueList setting = chartSettings.FirstOrDefault(x => x.Text == column.ColumnName); if (setting != null && setting.Enabled == false) { columnsToRemove.Add(column.ColumnName); } } foreach (string columnName in columnsToRemove) { table.Columns.Remove(columnName); } return(Ok(table)); } } catch (Exception ex) { return(InternalServerError(ex)); } }
public WebPageControllerResolver(WebServer webServer, string defaultWebPage, object model, Type modelType, AdoDataConnection database) { m_webServer = webServer; m_defaultWebPage = defaultWebPage; m_model = model; m_modelType = modelType; m_database = database; }
public ContourInfo getLocationsTrendingData(ContourQuery contourQuery) { List<TrendingDataLocation> locations = new List<TrendingDataLocation>(); DataTable colorScale; using (AdoDataConnection conn = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter))) using (IDbCommand cmd = conn.Connection.CreateCommand()) { cmd.Parameters.Add(new SqlParameter("@EventDateFrom", contourQuery.GetStartDate())); cmd.Parameters.Add(new SqlParameter("@EventDateTo", contourQuery.GetEndDate())); cmd.Parameters.Add(new SqlParameter("@colorScaleName", contourQuery.ColorScaleName)); cmd.Parameters.Add(new SqlParameter("@username", contourQuery.UserName)); cmd.CommandText = "dbo.selectMeterLocationsTrendingData"; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandTimeout = 300; using (IDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { TrendingDataLocation ourStatus = new TrendingDataLocation(); ourStatus.Latitude = (double)rdr["Latitude"]; ourStatus.Longitude = (double)rdr["Longitude"]; ourStatus.name = (string)rdr["Name"]; ourStatus.Average = (rdr.IsDBNull(rdr.GetOrdinal("Average")) ? (double?)null : (double)rdr["Average"]); ourStatus.Maximum = (rdr.IsDBNull(rdr.GetOrdinal("Maximum")) ? (double?)null : (double)rdr["Maximum"]); ourStatus.Minimum = (rdr.IsDBNull(rdr.GetOrdinal("Minimum")) ? (double?)null : (double)rdr["Minimum"]); ourStatus.id = (int)rdr["id"]; ourStatus.data.Add(ourStatus.Average); ourStatus.data.Add(ourStatus.Maximum); ourStatus.data.Add(ourStatus.Minimum); locations.Add(ourStatus); } } string query = "SELECT " + " ContourColorScalePoint.Value, " + " ContourColorScalePoint.Color " + "FROM " + " ContourColorScale JOIN " + " ContourColorScalePoint ON ContourColorScalePoint.ContourColorScaleID = ContourColorScale.ID " + "WHERE ContourColorScale.Name = {0} " + "ORDER BY ContourColorScalePoint.OrderID"; colorScale = conn.RetrieveData(query, contourQuery.ColorScaleName); } double[] colorDomain = colorScale .Select() .Select(row => row.ConvertField<double>("Value")) .ToArray(); double[] colorRange = colorScale .Select() .Select(row => (double)(uint)row.ConvertField<int>("Color")) .ToArray(); return new ContourInfo() { Locations = locations, ColorDomain = colorDomain, ColorRange = colorRange, }; }
/// <summary> /// Determines Color based on Channel Information for Full Data Channels /// </summary> /// <param name="channel">Channel that represents the signal</param> /// <returns>A color designation</returns> public static string GetColor(Channel channel) { if (channel == null) { return("random"); } using (AdoDataConnection connection = new AdoDataConnection("systemSettings")) { if (channel.MeasurementType.Name == "Voltage") { switch (channel.Phase.Name) { case ("AN"): return("Va"); case ("BN"): return("Vb"); case ("CN"): return("Vc"); case ("AB"): return("Vab"); case ("BC"): return("Vbc"); case ("CA"): return("Vca"); case ("NG"): return("Ires"); default: // Should be random return("random"); } } else if (channel.MeasurementType.Name == "Current") { switch (channel.Phase.Name) { case ("AN"): return("Ia"); case ("BN"): return("Ib"); case ("CN"): return("Ic"); case ("NG"): return("Ires"); case ("RES"): return("Ires"); default: // Should be random return("random"); } } } //Should be Random return("random"); }
private static void CleanUpAnimation() { using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter))) { DateTime yesterday = DateTime.UtcNow.AddDays(-1); connection.ExecuteNonQuery("DELETE FROM ContourAnimationFrame WHERE ContourAnimationID IN (SELECT ID FROM ContourAnimation WHERE AccessedOn < {0})", yesterday); connection.ExecuteNonQuery("DELETE FROM ContourAnimation WHERE AccessedOn < {0}", yesterday); } }
// from new EvendData Blob format private static byte[] ProcessLegacyBlob(int eventID, int requestedSeriesID, AdoDataConnection connection) { int eventDataID = connection.ExecuteScalar <int>("SELECT EventDataID FROM ChannelData WHERE SeriesID = {0} AND EventID = {1}", requestedSeriesID, eventID); byte[] timeDomainData = connection.ExecuteScalar <byte[]>("SELECT TimeDomainData FROM EventData WHERE ID = {0}", eventDataID); byte[] resultData = null; // If the blob contains the GZip header, // use the legacy deserialization algorithm if (timeDomainData[0] == 0x1F && timeDomainData[1] == 0x8B) { return(FromData_Legacy(timeDomainData, requestedSeriesID, eventID, connection)); } // Restore the GZip header before uncompressing timeDomainData[0] = 0x1F; timeDomainData[1] = 0x8B; byte[] uncompressedData = GZipStream.UncompressBuffer(timeDomainData); int offset = 0; int m_samples = LittleEndian.ToInt32(uncompressedData, offset); offset += sizeof(int); List <DateTime> times = new List <DateTime>(); while (times.Count < m_samples) { int timeValues = LittleEndian.ToInt32(uncompressedData, offset); offset += sizeof(int); long currentValue = LittleEndian.ToInt64(uncompressedData, offset); offset += sizeof(long); times.Add(new DateTime(currentValue)); for (int i = 1; i < timeValues; i++) { currentValue += LittleEndian.ToUInt16(uncompressedData, offset); offset += sizeof(ushort); times.Add(new DateTime(currentValue)); } } while (offset < uncompressedData.Length) { List <DataPoint> dataSeries = new List <DataPoint>(); int seriesID = LittleEndian.ToInt32(uncompressedData, offset); offset += sizeof(int); const ushort NaNValue = ushort.MaxValue; double decompressionOffset = LittleEndian.ToDouble(uncompressedData, offset); double decompressionScale = LittleEndian.ToDouble(uncompressedData, offset + sizeof(double)); offset += 2 * sizeof(double); for (int i = 0; i < m_samples; i++) { ushort compressedValue = LittleEndian.ToUInt16(uncompressedData, offset); offset += sizeof(ushort); double decompressedValue = decompressionScale * compressedValue + decompressionOffset; if (compressedValue == NaNValue) { decompressedValue = double.NaN; } dataSeries.Add(new DataPoint() { Time = times[i], Value = decompressedValue }); } if (seriesID == requestedSeriesID) { resultData = ToData(dataSeries, seriesID, m_samples); } // Insert into correct ChannelData..... connection.ExecuteNonQuery("UPDATE ChannelData SET TimeDomainData = {0} WHERE SeriesID = {1} AND EventID = {2}", ToData(dataSeries, seriesID, m_samples), seriesID, eventID); } connection.ExecuteNonQuery("DELETE FROM EventData WHERE ID = {0}", eventDataID); return(resultData); }
private List<TrendingDataLocation> GetFrameFromDailySummary(ContourQuery contourQuery) { List<TrendingDataLocation> locations = new List<TrendingDataLocation>(); using (AdoDataConnection conn = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter))) using (IDbCommand cmd = conn.Connection.CreateCommand()) { cmd.Parameters.Add(new SqlParameter("@EventDateFrom", contourQuery.GetStartDate())); cmd.Parameters.Add(new SqlParameter("@EventDateTo", contourQuery.GetEndDate())); cmd.Parameters.Add(new SqlParameter("@colorScaleName", contourQuery.ColorScaleName)); cmd.Parameters.Add(new SqlParameter("@username", contourQuery.UserName)); cmd.CommandText = "dbo.selectMeterLocationsTrendingData"; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandTimeout = 300; using (IDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { TrendingDataLocation ourStatus = new TrendingDataLocation(); ourStatus.Latitude = (double)rdr["Latitude"]; ourStatus.Longitude = (double)rdr["Longitude"]; ourStatus.name = (string)rdr["Name"]; ourStatus.Average = (rdr.IsDBNull(rdr.GetOrdinal("Average")) ? (double?)null : (double)rdr["Average"]); ourStatus.Maximum = (rdr.IsDBNull(rdr.GetOrdinal("Maximum")) ? (double?)null : (double)rdr["Maximum"]); ourStatus.Minimum = (rdr.IsDBNull(rdr.GetOrdinal("Minimum")) ? (double?)null : (double)rdr["Minimum"]); ourStatus.id = (int)rdr["id"]; locations.Add(ourStatus); } } } if (!string.IsNullOrEmpty(contourQuery.Meters)) { const int byteSize = 8; // Meter selections are stored as a base-64 string without padding, using '-' instead of '+' and '_' instead of '/' string padding = "A==".Remove(3 - (contourQuery.Meters.Length + 3) % 4); string base64 = contourQuery.Meters.Replace('-', '+').Replace('_', '/') + padding; byte[] meterSelections = Convert.FromBase64String(base64); // The resulting byte array is a simple set of bitflags ordered by meter ID and packed into the most significant bits. // In order to properly interpret the bytes, we must first order the data by meter ID to determine the location of // each meter's bitflag. Then we can filter out the unwanted data from the original list of meters locations = locations .OrderBy(location => location.id) .Where((location, index) => (meterSelections[index / byteSize] & (0x80 >> (index % byteSize))) > 0) .ToList(); } return locations; }
// From old EventDataBlob format private static byte[] FromData_Legacy(byte[] data, int requestedSeriesID, int eventID, AdoDataConnection connection) { byte[] resultData = null; byte[] uncompressedData; int offset; DateTime[] times; int seriesID; uncompressedData = GZipStream.UncompressBuffer(data); offset = 0; int m_samples = LittleEndian.ToInt32(uncompressedData, offset); offset += sizeof(int); times = new DateTime[m_samples]; for (int i = 0; i < m_samples; i++) { times[i] = new DateTime(LittleEndian.ToInt64(uncompressedData, offset)); offset += sizeof(long); } while (offset < uncompressedData.Length) { seriesID = LittleEndian.ToInt32(uncompressedData, offset); offset += sizeof(int); List <DataPoint> points = new List <DataPoint>(); for (int i = 0; i < m_samples; i++) { points.Add(new DataPoint() { Time = times[i], Value = LittleEndian.ToDouble(uncompressedData, offset) }); offset += sizeof(double); } if (seriesID == requestedSeriesID) { resultData = ToData(points, seriesID, m_samples); } // Insert into correct ChannelData..... connection.ExecuteNonQuery("UPDATE ChannelData SET TimeDomainData = {0} WHERE SeriesID = {1} AND EventID = {2}", ToData(points, seriesID, m_samples), seriesID, eventID); } //Remove EventData int eventDataID = connection.ExecuteScalar <int>("SELECT EventDataID FROM ChannelData WHERE SeriesID = {0} AND EventID = {1}", requestedSeriesID, eventID); connection.ExecuteNonQuery("DELETE FROM EventData WHERE ID = {0}", eventDataID); return(resultData); }
private List<List<TrendingDataLocation>> GetFramesFromHistorian(ContourQuery contourQuery) { DataTable idTable; string historianServer; string historianInstance; using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter))) { string query = "SELECT " + " Channel.ID AS ChannelID, " + " Meter.ID AS MeterID, " + " Meter.Name AS MeterName, " + " MeterLocation.Latitude, " + " MeterLocation.Longitude, " + " Channel.PerUnitValue " + "FROM " + " Meter JOIN " + " MeterLocation ON Meter.MeterLocationID = MeterLocation.ID LEFT OUTER JOIN " + " Channel ON " + " Channel.MeterID = Meter.ID AND " + " Channel.ID IN (SELECT ChannelID FROM ContourChannel WHERE ContourColorScaleName = {1}) " + "WHERE Meter.ID IN (SELECT * FROM authMeters({0}))"; idTable = connection.RetrieveData(query, contourQuery.UserName, contourQuery.ColorScaleName); historianServer = connection.ExecuteScalar<string>("SELECT Value FROM Setting WHERE Name = 'Historian.Server'") ?? "127.0.0.1"; historianInstance = connection.ExecuteScalar<string>("SELECT Value FROM Setting WHERE Name = 'Historian.Instance'") ?? "XDA"; } if (!string.IsNullOrEmpty(contourQuery.Meters)) { const int byteSize = 8; // Meter selections are stored as a base-64 string without padding, using '-' instead of '+' and '_' instead of '/' string padding = "A==".Remove(3 - (contourQuery.Meters.Length + 3) % 4); string base64 = contourQuery.Meters.Replace('-', '+').Replace('_', '/') + padding; byte[] meterSelections = Convert.FromBase64String(base64); // The resulting byte array is a simple set of bitflags ordered by meter ID and packed into the most significant bits. // In order to properly interpret the bytes, we must first group and order the data by meter ID to determine the location // of each meter's bitflag. Then we can filter out the unwanted data from the original table of IDs idTable.Select() .Select((Row, Index) => new { Row, Index }) .GroupBy(obj => obj.Row.ConvertField<int>("MeterID")) .OrderBy(grouping => grouping.Key) .Where((grouping, index) => (meterSelections[index / byteSize] & (0x80 >> (index % byteSize))) == 0) .SelectMany(grouping => grouping) .OrderByDescending(obj => obj.Index) .ToList() .ForEach(obj => idTable.Rows.RemoveAt(obj.Index)); } List<DataRow> meterRows = idTable .Select() .DistinctBy(row => row.ConvertField<int>("MeterID")) .ToList(); DateTime startDate = contourQuery.GetStartDate(); DateTime endDate = contourQuery.GetEndDate(); int stepSize = contourQuery.StepSize; // The frames to be included are those whose timestamps fall // within the range which is specified by startDate and // endDate. We start by aligning startDate and endDate with // the nearest frame timestamps which fall within that range int startTimeOffset = (int)Math.Ceiling((startDate - startDate.Date).TotalMinutes / stepSize); startDate = startDate.Date.AddMinutes(startTimeOffset * stepSize); int endTimeOffset = (int)Math.Floor((endDate - endDate.Date).TotalMinutes / stepSize); endDate = endDate.Date.AddMinutes(endTimeOffset * stepSize); // Since each frame includes data from all timestamps between // the previous frame's timestamp and its own timestamp, we // must include one additional frame of data before startDate startDate = startDate.AddMinutes(-stepSize); int frameCount = (int)((endDate - startDate).TotalMinutes / stepSize); List<Dictionary<int, TrendingDataLocation>> frames = Enumerable.Repeat(meterRows, frameCount) .Select(rows => rows.Select(row => new TrendingDataLocation() { id = row.ConvertField<int>("MeterID"), name = row.ConvertField<string>("MeterName"), Latitude = row.ConvertField<double>("Latitude"), Longitude = row.ConvertField<double>("Longitude") })) .Select(locations => locations.ToDictionary(location => location.id)) .ToList(); Dictionary<int, double?> nominalLookup = idTable .Select("ChannelID IS NOT NULL") .ToDictionary(row => row.ConvertField<int>("ChannelID"), row => row.ConvertField<double?>("PerUnitValue")); Dictionary<int, List<TrendingDataLocation>> lookup = idTable .Select("ChannelID IS NOT NULL") .Select(row => { int meterID = row.ConvertField<int>("MeterID"); return new { ChannelID = row.ConvertField<int>("ChannelID"), Frames = frames.Select(locationLookup => locationLookup[meterID]).ToList() }; }) .ToDictionary(obj => obj.ChannelID, obj => obj.Frames); using (Historian historian = new Historian(historianServer, historianInstance)) { foreach (TrendingDataPoint point in historian.Read(lookup.Keys, startDate, endDate)) { List<TrendingDataLocation> locations = lookup[point.ChannelID]; // Use ceiling to sort data into the next nearest frame. // Subtract 1 because startDate was shifted to include one additional frame of data int frameIndex = (int)Math.Ceiling((point.Timestamp - startDate).TotalMinutes / stepSize) - 1; if (frameIndex < 0 || frameIndex >= locations.Count) continue; TrendingDataLocation frame = locations[frameIndex]; double nominal = nominalLookup[point.ChannelID] ?? 1.0D; double value = point.Value / nominal; switch (point.SeriesID) { case SeriesID.Minimum: frame.Minimum = Math.Min(value, frame.Minimum ?? value); break; case SeriesID.Maximum: frame.Maximum = Math.Max(value, frame.Maximum ?? value); break; case SeriesID.Average: frame.Aggregate(value); frame.Average = frame.GetAverage(); break; } } } return frames .Select(frame => frame.Values.ToList()) .ToList(); }
// This is going through this function to migtrate all EventdataBlobs over to ChannelDataBlobs as they are read eventually removing the legacy table (eventData) public static List <byte[]> DataFromEvent(int eventID, AdoDataConnection connection) { List <byte[]> result = new List <byte[]>(); List <int> directChannelIDs = new List <int>(); //This Should start by getting multiple datasets string query = "SELECT SeriesID FROM ChannelData WHERE EventID = {0}"; DataTable table = connection.RetrieveData(query, eventID); result.AddRange(table.Rows.Cast <DataRow>().Select(row => { int seriesID = row.Field <int>("SeriesID"); byte[] singleSeriesData = connection.ExecuteScalar <byte[]>("SELECT TimeDomainData FROM ChannelData WHERE SeriesID = {0} AND EventID = {1}" , seriesID, eventID); if (singleSeriesData == null) { singleSeriesData = ProcessLegacyBlob(eventID, seriesID, connection); } directChannelIDs.Add(connection.ExecuteScalar <int>("SELECT ChannelID FROM Series WHERE ID = {0}", seriesID)); return(singleSeriesData); } )); //This Will get the extended Data (throught connections).... Asset asset = new TableOperations <Asset>(connection).QueryRecordWhere("ID = (SELECT AssetID FROM Event WHERE ID = {0})", eventID); asset.ConnectionFactory = () => { return(new AdoDataConnection(connection.Connection.ConnectionString, typeof(SqlConnection), typeof(SqlDataAdapter))); }; List <int> channelIDs = asset.ConnectedChannels.Select(item => item.ID).ToList(); foreach (int channelID in channelIDs) { if (directChannelIDs.Contains(channelID)) { continue; } //Find any Series where Event happens at the same time and ChannelID is ChannelID //-> note that this assumes any Channel is only associated with a single event at a time int channelDataID = connection.ExecuteScalar <int>("SELECT COUNT(ChannelData.ID) FROM ChannelData LEFT JOIN Event ON ChannelData.EventID = Event.ID " + "LEFT JOIN Series ON ChannelData.SeriesID = Series.ID " + "WHERE(Series.ChannelID = {0}) AND(Event.MeterID = (SELECT EV.MeterID FROM Event EV WHERE EV.ID = {1})) AND " + "(Event.StartTime <= (SELECT EV.EndTime FROM Event EV WHERE EV.ID = {1})) AND " + "(Event.EndTime >= (SELECT EV.StartTime FROM Event EV WHERE EV.ID = {1}))", channelID, eventID); if (channelDataID == 0) { continue; } channelDataID = connection.ExecuteScalar <int>("SELECT ChannelData.ID FROM ChannelData LEFT JOIN Event ON ChannelData.EventID = Event.ID " + "LEFT JOIN Series ON ChannelData.SeriesID = Series.ID " + "WHERE(Series.ChannelID = {0}) AND(Event.MeterID = (SELECT EV.MeterID FROM Event EV WHERE EV.ID = {1})) AND " + "(Event.StartTime <= (SELECT EV.EndTime FROM Event EV WHERE EV.ID = {1})) AND " + "(Event.EndTime >= (SELECT EV.StartTime FROM Event EV WHERE EV.ID = {1}))", channelID, eventID); byte[] singleSeriesData = connection.ExecuteScalar <byte[]>("SELECT TimeDomainData FROM ChannelData WHERE ID = {0}" , channelDataID); if (singleSeriesData == null) { ChannelData channelData = new TableOperations <ChannelData>(connection).QueryRecordWhere("ID = {0}", channelDataID); singleSeriesData = ProcessLegacyBlob(channelData.EventID, channelData.SeriesID, connection); } result.Add(singleSeriesData); } return(result); }
/// <summary> /// Handles loaded event for <see cref="SubscriberUserControl"/>. /// </summary> /// <param name="sender">Source of the event.</param> /// <param name="e">Event arguments.</param> private void SubscriberUserControl_Loaded(object sender, RoutedEventArgs e) { // Attach to load/before save event so class can load/add crypto keys from/to local keyIV cache m_dataContext.PropertyChanged += SubscriberUserControl_PropertyChanged; m_dataContext.BeforeSave += SubscriberUserControl_BeforeSave; LoadCurrentKeyIV(); try { using (AdoDataConnection database = new AdoDataConnection(CommonFunctions.DefaultSettingsCategory)) { Dictionary<string, string> settings; string server; IPAddress[] hostIPs = null; IEnumerable<IPAddress> localIPs; settings = database.DataPublisherConnectionString().ToNonNullString().ParseKeyValuePairs(); if (settings.TryGetValue("server", out server)) hostIPs = Dns.GetHostAddresses(server.Split(':')[0]); localIPs = Dns.GetHostAddresses("localhost").Concat(Dns.GetHostAddresses(Dns.GetHostName())); // Check to see if entered host name corresponds to a local IP address if ((object)hostIPs == null) MessageBox.Show("Failed to find service host address. If using Gateway security, secure key exchange may not succeed." + Environment.NewLine + "Please make sure to run manager application with administrative privileges on the server where service is hosted.", "Authorize Subcriber", MessageBoxButton.OK, MessageBoxImage.Warning); else if (!hostIPs.Any(ip => localIPs.Contains(ip))) MessageBox.Show("If using Gateway security, secure key exchange may not succeed." + Environment.NewLine + "Please make sure to run manager application with administrative privileges on the server where service is hosted.", "Authorize Subscriber", MessageBoxButton.OK, MessageBoxImage.Warning); } // If the user is not an Administrator, then the following properties for these controls are readonly and not enable bool isAdmin = CommonFunctions.CurrentPrincipal.IsInRole("Administrator,Editor"); AcronymField.IsReadOnly = !isAdmin; NameField.IsReadOnly = !isAdmin; ValidIpAddressesField.IsReadOnly = !isAdmin; EnablePGConnectionCheckBox.IsEnabled = isAdmin; ImportSRQButton.IsEnabled = isAdmin; ImportCERButton.IsEnabled = isAdmin; FooterControl.IsEnabled = isAdmin; } catch { MessageBox.Show("Please make sure to run manager application with administrative privileges on the server where service is hosted.", "Authorize Subscriber", MessageBoxButton.OK, MessageBoxImage.Warning); } }
/// <summary> /// Loads <see cref="Adapter"/> information as an <see cref="ObservableCollection{T}"/> style list. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="adapterType"><see cref="AdapterType"/> collection to be returned.</param> /// <param name="keys">Keys of the adapters to be loaded from the database.</param> /// <returns>Collection of <see cref="Adapter"/>.</returns> public static ObservableCollection <Adapter> Load(AdoDataConnection database, AdapterType adapterType, IList <int> keys) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); string viewName; if (adapterType == AdapterType.Action) { viewName = "CustomActionAdapterDetail"; } else if (adapterType == AdapterType.Filter) { viewName = "CustomFilterAdapterDetail"; } else if (adapterType == AdapterType.Input) { viewName = "CustomInputAdapterDetail"; } else { viewName = "CustomOutputAdapterDetail"; } string query; string commaSeparatedKeys; Adapter[] adapterList = null; DataTable adapterTable; int id; if ((object)keys != null && keys.Count > 0) { commaSeparatedKeys = keys.Select(key => "" + key.ToString() + "").Aggregate((str1, str2) => str1 + "," + str2); query = database.ParameterizedQueryString(string.Format("SELECT NodeID, ID, AdapterName, AssemblyName, TypeName, ConnectionString, " + "LoadOrder, Enabled, NodeName FROM {0} WHERE NodeID = {{0}} AND ID IN ({1})", viewName, commaSeparatedKeys), "nodeID"); adapterTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.CurrentNodeID()); adapterList = new Adapter[adapterTable.Rows.Count]; foreach (DataRow row in adapterTable.Rows) { id = row.ConvertField <int>("ID"); adapterList[keys.IndexOf(id)] = new Adapter() { NodeID = database.Guid(row, "NodeID"), ID = id, AdapterName = row.Field <string>("AdapterName"), AssemblyName = row.Field <string>("AssemblyName"), TypeName = row.Field <string>("TypeName"), ConnectionString = row.Field <string>("ConnectionString"), LoadOrder = row.ConvertField <int>("LoadOrder"), Enabled = Convert.ToBoolean(row.Field <object>("Enabled")), NodeName = row.Field <string>("NodeName"), Type = adapterType }; } } return(new ObservableCollection <Adapter>(adapterList ?? new Adapter[0])); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Retrieves <see cref="Dictionary{T1,T2}"/> type collection of <see cref="MeasurementGroup"/> denied for <see cref="Subscriber"/>. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="subscriberID">ID of the <see cref="Subscriber"/> to filter data.</param> /// <returns><see cref="Dictionary{T1,T2}"/> type collection of ID and Name of <see cref="MeasurementGroup"/>.</returns> public static Dictionary<int, string> GetDeniedMeasurementGroups(AdoDataConnection database, Guid subscriberID) { Dictionary<int, string> deniedMeasurementGroups; DataTable deniedMeasurementGroupTable; bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); deniedMeasurementGroups = new Dictionary<int, string>(); query = database.ParameterizedQueryString("SELECT MeasurementGroupID, MeasurementGroupName FROM SubscriberMeasGroupDetail WHERE SubscriberID = {0} AND Allowed = {1} ORDER BY MeasurementGroupName", "subscriberID", "allowed"); deniedMeasurementGroupTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout, database.Guid(subscriberID), database.Bool(false)); foreach (DataRow row in deniedMeasurementGroupTable.Rows) deniedMeasurementGroups[row.ConvertField<int>("MeasurementGroupID")] = row.Field<string>("MeasurementGroupName"); return deniedMeasurementGroups; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Loads <see cref="OutputStreamDevice"/> information as an <see cref="ObservableCollection{T}"/> style list. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="keys">Keys of the measurement to be loaded from the datbase</param> /// <returns>Collection of <see cref="OutputStreamDevice"/>.</returns> public static ObservableCollection <OutputStreamDevice> Load(AdoDataConnection database, IList <int> keys) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); string query; string commaSeparatedKeys; OutputStreamDevice[] outputStreamDeviceList = null; DataTable outputStreamDeviceTable; int id; if ((object)keys != null && keys.Count > 0) { commaSeparatedKeys = keys.Select(key => key.ToString()).Aggregate((str1, str2) => str1 + "," + str2); query = string.Format("SELECT NodeID, AdapterID, ID, IDCode, Acronym, BpaAcronym, Name, PhasorDataFormat, " + "FrequencyDataFormat, AnalogDataFormat, CoordinateFormat, LoadOrder, Enabled, Virtual " + "FROM OutputStreamDeviceDetail WHERE ID IN ({0})", commaSeparatedKeys); if (database.IsMySQL) { query = string.Format("SELECT NodeID, AdapterID, ID, IDCode, Acronym, BpaAcronym, Name, PhasorDataFormat, " + "FrequencyDataFormat, AnalogDataFormat, CoordinateFormat, LoadOrder, Enabled, `Virtual` " + "FROM OutputStreamDeviceDetail WHERE ID IN ({0})", commaSeparatedKeys); } outputStreamDeviceTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout); outputStreamDeviceList = new OutputStreamDevice[outputStreamDeviceTable.Rows.Count]; foreach (DataRow row in outputStreamDeviceTable.Rows) { id = row.ConvertField <int>("ID"); outputStreamDeviceList[keys.IndexOf(id)] = new OutputStreamDevice() { NodeID = database.Guid(row, "NodeID"), AdapterID = row.ConvertField <int>("AdapterID"), ID = id, IDCode = row.ConvertField <int>("IDCode"), Acronym = row.Field <string>("Acronym"), BpaAcronym = row.Field <string>("BpaAcronym"), Name = row.Field <string>("Name"), PhasorDataFormat = row.Field <string>("PhasorDataFormat"), FrequencyDataFormat = row.Field <string>("FrequencyDataFormat"), AnalogDataFormat = row.Field <string>("AnalogDataFormat"), CoordinateFormat = row.Field <string>("CoordinateFormat"), LoadOrder = row.ConvertField <int>("LoadOrder"), Enabled = Convert.ToBoolean(row.Field <object>("Enabled")), m_virtual = Convert.ToBoolean(row.Field <object>("Virtual")) }; } } return(new ObservableCollection <OutputStreamDevice>(outputStreamDeviceList ?? new OutputStreamDevice[0])); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Counts measurements assigned to <see cref="Subscriber"/>. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="subscriberID">ID of the <see cref="Subscriber"/> for which to count measurements.</param> /// <param name="allowed">boolean flag to indicate if measurements are allowed or denied.</param> /// <returns>string, indicating success for UI display.</returns> public static int GetGroupCount(AdoDataConnection database, Guid subscriberID, bool allowed) { const string QueryFormat = "SELECT COUNT(*) FROM SubscriberMeasurementGroup WHERE SubscriberID = {0} AND Allowed = {1}"; bool createdConnection = false; try { createdConnection = CreateConnection(ref database); return database.ExecuteNonQuery(DefaultTimeout, QueryFormat, subscriberID, allowed); } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Adds multiple devices to output steam. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="outputStreamID">ID of the output stream to which devices needs to be added.</param> /// <param name="devices">Collection of devices to be added.</param> /// <param name="addDigitals">Boolean flag indicating if analogs should be added.</param> /// <param name="addAnalogs">Boolean flag indicating if digirals should be added.</param> /// <returns>String, for display use, indicating success.</returns> public static string AddDevices(AdoDataConnection database, int outputStreamID, ObservableCollection <Device> devices, bool addDigitals, bool addAnalogs) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); foreach (Device device in devices) { OutputStreamDevice outputStreamDevice = new OutputStreamDevice(); outputStreamDevice.NodeID = device.NodeID; outputStreamDevice.AdapterID = outputStreamID; outputStreamDevice.Acronym = device.Acronym.Substring(device.Acronym.LastIndexOf("!", StringComparison.Ordinal) + 1); outputStreamDevice.BpaAcronym = string.Empty; outputStreamDevice.Name = device.Name; outputStreamDevice.LoadOrder = device.LoadOrder; outputStreamDevice.Enabled = true; outputStreamDevice.IDCode = device.AccessID; Save(database, outputStreamDevice); outputStreamDevice = GetOutputStreamDevice(database, "WHERE Acronym = '" + outputStreamDevice.Acronym + "' AND AdapterID = " + outputStreamID); if ((object)outputStreamDevice != null) { IList <int> keys = Phasor.LoadKeys(database, device.ID); ObservableCollection <Phasor> phasors = Phasor.Load(database, keys); foreach (Phasor phasor in phasors) { OutputStreamDevicePhasor outputStreamDevicePhasor = new OutputStreamDevicePhasor(); outputStreamDevicePhasor.NodeID = device.NodeID; outputStreamDevicePhasor.OutputStreamDeviceID = outputStreamDevice.ID; outputStreamDevicePhasor.Label = phasor.Label; outputStreamDevicePhasor.Type = phasor.Type; outputStreamDevicePhasor.Phase = phasor.Phase; outputStreamDevicePhasor.LoadOrder = phasor.SourceIndex; OutputStreamDevicePhasor.Save(database, outputStreamDevicePhasor); } ObservableCollection <Measurement> measurements = Measurement.Load(database, device.ID); int analogIndex = 0; foreach (Measurement measurement in measurements) { if (measurement.SignalAcronym != "STAT" && measurement.SignalAcronym != "QUAL") { measurement.SignalReference = measurement.SignalReference.Substring(measurement.SignalReference.LastIndexOf("!", StringComparison.Ordinal) + 1); if ((measurement.SignalAcronym != "ALOG" && measurement.SignalAcronym != "DIGI") || (measurement.SignalAcronym == "ALOG" && addAnalogs) || (measurement.SignalAcronym == "DIGI" && addDigitals)) { OutputStreamMeasurement outputStreamMeasurement = new OutputStreamMeasurement(); outputStreamMeasurement.NodeID = device.NodeID; outputStreamMeasurement.AdapterID = outputStreamID; outputStreamMeasurement.HistorianID = measurement.HistorianID; outputStreamMeasurement.PointID = measurement.PointID; outputStreamMeasurement.SignalReference = measurement.SignalReference; OutputStreamMeasurement.Save(database, outputStreamMeasurement); } if (addAnalogs && measurement.SignalAcronym == "ALOG") { OutputStreamDeviceAnalog outputStreamDeviceAnalog = new OutputStreamDeviceAnalog(); outputStreamDeviceAnalog.NodeID = device.NodeID; outputStreamDeviceAnalog.OutputStreamDeviceID = outputStreamDevice.ID; outputStreamDeviceAnalog.Label = string.IsNullOrEmpty(measurement.AlternateTag) ? device.Acronym.Length > 12 ? device.Acronym.Substring(0, 12) + ":A" + analogIndex : device.Acronym + ":A" + analogIndex : measurement.AlternateTag; // measurement.PointTag; int charIndex = measurement.SignalReference.LastIndexOf("-", StringComparison.Ordinal); int signalIndex; if (charIndex >= 0 && charIndex + 3 < measurement.SignalReference.Length && int.TryParse(measurement.SignalReference.Substring(charIndex + 3), out signalIndex)) { outputStreamDeviceAnalog.LoadOrder = signalIndex; } else { outputStreamDeviceAnalog.LoadOrder = 999; } OutputStreamDeviceAnalog.Save(database, outputStreamDeviceAnalog); analogIndex++; } else if (addDigitals && measurement.SignalAcronym == "DIGI") { OutputStreamDeviceDigital outputStreamDeviceDigital = new OutputStreamDeviceDigital(); outputStreamDeviceDigital.NodeID = device.NodeID; outputStreamDeviceDigital.OutputStreamDeviceID = outputStreamDevice.ID; outputStreamDeviceDigital.Label = string.IsNullOrEmpty(measurement.AlternateTag) ? DefaultDigitalLabel : measurement.AlternateTag; // measurement.PointTag; int charIndex = measurement.SignalReference.LastIndexOf("-", StringComparison.Ordinal); int signalIndex; if (charIndex >= 0 && charIndex + 3 < measurement.SignalReference.Length && int.TryParse(measurement.SignalReference.Substring(charIndex + 3), out signalIndex)) { outputStreamDeviceDigital.LoadOrder = signalIndex; } else { outputStreamDeviceDigital.LoadOrder = 999; } OutputStreamDeviceDigital.Save(database, outputStreamDeviceDigital); } } } } } return("Output Stream Device(s) added successfully!"); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Removed measurement groups from <see cref="Subscriber"/>. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="subscriberID">ID of the <see cref="Subscriber"/> to which measurement groups to be removed.</param> /// <param name="measurementGroupsToBeRemoved">List of <see cref="MeasurementGroup"/> IDs to be removed.</param> /// <returns>string, indicating success for UI display.</returns> public static int RemoveMeasurementGroups(AdoDataConnection database, Guid subscriberID, List<int> measurementGroupsToBeRemoved) { const string QueryFormat = "DELETE FROM SubscriberMeasurementGroup WHERE SubscriberID = {0} AND MeasurementGroupID = {1}"; bool createdConnection = false; int rowsAffected = 0; try { createdConnection = CreateConnection(ref database); foreach (int id in measurementGroupsToBeRemoved) rowsAffected += database.ExecuteNonQuery(DefaultTimeout, QueryFormat, subscriberID, id); return rowsAffected; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Gets collection of output stream devices. /// </summary> /// <param name="database">Source database connection.</param> /// <param name="whereClause">Where filter clause.</param> /// <returns>Collection of output stream devices.</returns> public static ObservableCollection <OutputStreamDevice> GetOutputStreamDevices(AdoDataConnection database, string whereClause) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); ObservableCollection <OutputStreamDevice> outputStreamDeviceList = new ObservableCollection <OutputStreamDevice>(); DataTable outputStreamDeviceTable = database.Connection.RetrieveData(database.AdapterType, "SELECT * FROM OutputStreamDeviceDetail " + whereClause); foreach (DataRow row in outputStreamDeviceTable.Rows) { outputStreamDeviceList.Add(new OutputStreamDevice { NodeID = database.Guid(row, "NodeID"), AdapterID = row.ConvertField <int>("AdapterID"), ID = row.ConvertField <int>("ID"), IDCode = row.ConvertField <int>("IDCode"), Acronym = row.Field <string>("Acronym"), BpaAcronym = row.Field <string>("BpaAcronym"), Name = row.Field <string>("Name"), PhasorDataFormat = row.Field <string>("PhasorDataFormat"), FrequencyDataFormat = row.Field <string>("FrequencyDataFormat"), AnalogDataFormat = row.Field <string>("AnalogDataFormat"), CoordinateFormat = row.Field <string>("CoordinateFormat"), LoadOrder = row.ConvertField <int>("LoadOrder"), Enabled = Convert.ToBoolean(row.Field <object>("Enabled")), m_virtual = Convert.ToBoolean(row.Field <object>("Virtual")) }); } return(outputStreamDeviceList); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
/// <summary> /// Saves <see cref="Subscriber"/> information to database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="subscriber">Information about <see cref="Subscriber"/>.</param> /// <returns>String, for display use, indicating success.</returns> public static string Save(AdoDataConnection database, Subscriber subscriber) { bool createdConnection = false; SslPolicyErrors validPolicyErrors; X509ChainStatusFlags validChainFlags; string query; try { createdConnection = CreateConnection(ref database); validPolicyErrors = (subscriber.ValidPolicyErrors ?? SslPolicyErrors.None) | (subscriber.RemoteCertificateIsSelfSigned ? SslPolicyErrors.RemoteCertificateChainErrors : SslPolicyErrors.None); validChainFlags = (subscriber.ValidChainFlags ?? X509ChainStatusFlags.NoError) | (subscriber.RemoteCertificateIsSelfSigned ? X509ChainStatusFlags.UntrustedRoot : X509ChainStatusFlags.NoError); if (subscriber.ID == Guid.Empty) { query = database.ParameterizedQueryString("INSERT INTO Subscriber (NodeID, Acronym, Name, SharedSecret, AuthKey, ValidIPAddresses, RemoteCertificateFile, ValidPolicyErrors, ValidChainFlags, " + "AccessControlFilter, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, " + "{13}, {14})", "nodeID", "acronym", "name", "sharedSecret", "authKey", "validIPAddresses", "remoteCertificateFile", "validPolicyErrors", "validChainFlags", "accessControlFilter", "enabled", "updatedBy", "updatedOn", "createdBy", "createdOn"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.CurrentNodeID(), subscriber.Acronym, subscriber.Name.ToNotNull(), subscriber.SharedSecret.ToNotNull(), subscriber.AuthKey.ToNotNull(), subscriber.ValidIPAddresses.ToNotNull(), subscriber.RemoteCertificateFile.ToNotNull(), validPolicyErrors.ToString(), validChainFlags.ToString(), subscriber.AccessControlFilter.ToNotNull(), database.Bool(subscriber.Enabled), CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow); } else { query = database.ParameterizedQueryString("UPDATE Subscriber SET NodeID = {0}, Acronym = {1}, Name = {2}, SharedSecret = {3}, AuthKey = {4}, ValidIPAddresses = {5}, RemoteCertificateFile = {6}, " + "ValidPolicyErrors = {7}, ValidChainFlags = {8}, AccessControlFilter = {9}, Enabled = {10}, UpdatedBy = {11}, UpdatedOn = {12} WHERE ID = {13}", "nodeID", "acronym", "name", "sharedSecret", "authKey", "validIPAddresses", "remoteCertificateFile", "validPolicyErrors", "validChainFlags", "accessControlFilter", "enabled", "updatedBy", "updatedOn", "id"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, database.Guid(subscriber.NodeID), subscriber.Acronym, subscriber.Name.ToNotNull(), subscriber.SharedSecret.ToNotNull(), subscriber.AuthKey.ToNotNull(), subscriber.ValidIPAddresses.ToNotNull(), subscriber.RemoteCertificateFile.ToNotNull(), validPolicyErrors.ToString(), validChainFlags.ToString(), subscriber.AccessControlFilter.ToNotNull(), database.Bool(subscriber.Enabled), CommonFunctions.CurrentUser, database.UtcNow, database.Guid(subscriber.ID)); } try { CommonFunctions.SendCommandToService("ReloadConfig"); } catch (Exception ex) { return "Subscriber information saved successfully. Failed to send ReloadConfig command to backend service." + Environment.NewLine + ex.Message; } return "Subscriber information saved successfully"; } finally { if (createdConnection && database != null) database.Dispose(); } }
private static void CreateOutputMeasurementsWhereNull(AdoDataConnection database, string nodeIDQueryString, Action <string> statusMessage) { statusMessage("Checking for calculations with null output measurements..."); string query = $"SELECT " + $" pc.ID, " + $" pc.CircuitDescription, " + $" pc.ActivePowerOutputSignalID, " + $" pc.ApparentPowerOutputSignalID, " + $" pc.ReactivePowerOutputSignalID, " + $" v.Acronym AS VendorAcronym, " + $" d.Acronym AS DeviceAcronym, " + $" c.Acronym AS CompanyAcronym, " + $" d.id AS DeviceID, " + $" vm.HistorianID AS HistorianID, " + $" p.Label AS CurrentLabel " + $"FROM " + $" PowerCalculation pc JOIN " + $" Measurement vm ON vm.SignalID = pc.VoltageAngleSignalID JOIN " + $" Measurement im ON im.SignalID = pc.CurrentAngleSignalID LEFT OUTER JOIN " + $" Phasor p ON im.DeviceID = p.DeviceID AND im.PhasorSourceIndex = p.SourceIndex LEFT OUTER JOIN " + $" Device d ON vm.DeviceID = d.ID LEFT OUTER JOIN " + $" VendorDevice vd ON vd.ID = d.VendorDeviceID LEFT OUTER JOIN " + $" Vendor v ON vd.VendorID = v.ID LEFT OUTER JOIN " + $" Company c ON d.CompanyID = c.ID " + $"WHERE " + $" pc.Enabled <> 0 AND " + $" pc.NodeID = {nodeIDQueryString} AND " + $" ( " + $" pc.ActivePowerOutputSignalID IS NULL OR " + $" pc.ReactivePowerOutputSignalID IS NULL OR " + $" pc.ApparentPowerOutputSignalID IS NULL " + $" )"; Dictionary <int, PowerMeasurement> activePowerUpdates = new Dictionary <int, PowerMeasurement>(); Dictionary <int, PowerMeasurement> reactivePowerUpdates = new Dictionary <int, PowerMeasurement>(); Dictionary <int, PowerMeasurement> apparentPowerUpdates = new Dictionary <int, PowerMeasurement>(); using (IDbCommand cmd = database.Connection.CreateCommand()) { cmd.CommandText = query; using (IDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { int powerCalculationID = rdr.GetInt32(0); string companyAcronym = rdr.IsDBNull(7) ? "" : rdr.GetString(7); string vendorAcronym = rdr.IsDBNull(5) ? "" : rdr.GetString(5); string signalTypeAcronym = "CALC"; string circuitDescription = rdr.IsDBNull(1) ? "" : rdr.GetString(1); int deviceID = rdr.GetInt32(8); int? historianID = rdr.IsDBNull(9) ? null : (int?)rdr.GetInt32(9); // Remove any settings defined in circuit description int semicolonIndex = circuitDescription.IndexOf(';'); if (semicolonIndex > -1) { circuitDescription = circuitDescription.Substring(0, semicolonIndex); } if (rdr.IsDBNull(2)) // Real - MW { // create active power output measurement PowerMeasurement measurement = CreateMeasurement(companyAcronym, circuitDescription + "-MW", vendorAcronym, signalTypeAcronym, circuitDescription, deviceID, historianID, "Active Power Calculation"); activePowerUpdates.Add(powerCalculationID, measurement); } if (rdr.IsDBNull(3)) // Apparent - MVA { // create apparent power output measurement PowerMeasurement measurement = CreateMeasurement(companyAcronym, circuitDescription + "-MVA", vendorAcronym, signalTypeAcronym, circuitDescription, deviceID, historianID, "Apparent Power Calculation"); apparentPowerUpdates.Add(powerCalculationID, measurement); } if (rdr.IsDBNull(4)) // Reactive - MVAR { //create reactive power output measurement PowerMeasurement measurement = CreateMeasurement(companyAcronym, circuitDescription + "-MVAR", vendorAcronym, signalTypeAcronym, circuitDescription, deviceID, historianID, "Reactive Power Calculation"); reactivePowerUpdates.Add(powerCalculationID, measurement); } } } } int newMeasurementsCount = activePowerUpdates.Count + reactivePowerUpdates.Count + apparentPowerUpdates.Count; if (newMeasurementsCount > 0) { MeasurementRepository repo = new MeasurementRepository(); statusMessage($"Creating {newMeasurementsCount} new output measurements for power calculation..."); foreach (KeyValuePair <int, PowerMeasurement> update in activePowerUpdates) { repo.Save(database, update.Value); UpdatePowerCalculation(database, update.Key, activePowerOutputSignalID: update.Value.SignalID); } statusMessage("Successfully created new active power calculations."); foreach (KeyValuePair <int, PowerMeasurement> update in reactivePowerUpdates) { repo.Save(database, update.Value); UpdatePowerCalculation(database, update.Key, reactivePowerOutputSignalID: update.Value.SignalID); } statusMessage("Successfully created new reactive power calculations."); foreach (KeyValuePair <int, PowerMeasurement> update in apparentPowerUpdates) { repo.Save(database, update.Value); UpdatePowerCalculation(database, update.Key, apparentPowerOutputSignalID: update.Value.SignalID); } statusMessage("Successfully created new apparent power calculations."); statusMessage("Completed creation of new measurements for null output measurements on power calculations."); } }
public TrendingDataSet getTrendsforChannelIDDate(string ChannelID, string targetDate) { //DateTime epoch = new DateTime(1970, 1, 1); //string theSproc = "dbo.selectTrendingDataByChannelIDDate2"; //DataSet dataSet = new DataSet(); //TrendingDataSet trendingDataSet = new TrendingDataSet(); //using (SqlConnection connection = new SqlConnection(connectionstring)) //using (SqlCommand command = connection.CreateCommand()) //using (SqlDataAdapter adapter = new SqlDataAdapter(command)) //{ // command.CommandText = theSproc; // command.CommandType = CommandType.StoredProcedure; // command.Parameters.AddWithValue("@EventDate", targetDate); // command.Parameters.AddWithValue("@ChannelID", ChannelID); // command.CommandTimeout = 300; // connection.Open(); // adapter.Fill(dataSet); // trendingDataSet.ChannelData = dataSet.Tables[0].Rows // .Cast<DataRow>() // .Select(row => new TrendingDataPoint() // { // Time = row.Field<DateTime>("thedate").Subtract(epoch).TotalMilliseconds, // Maximum = row.Field<double>("themaximum"), // Minimum = row.Field<double>("theminimum"), // Average = row.Field<double>("theaverage") // }) // .ToArray(); // trendingDataSet.AlarmLimits = dataSet.Tables[1].Rows // .Cast<DataRow>() // .Select(row => new TrendingAlarmLimit() // { // TimeStart = row.Field<DateTime>("thedatefrom").Subtract(epoch).TotalMilliseconds, // TimeEnd = row.Field<DateTime>("thedateto").Subtract(epoch).TotalMilliseconds, // High = row.Field<double?>("alarmlimithigh"), // Low = row.Field<double?>("alarmlimitlow") // }) // .ToArray(); // trendingDataSet.OffNormalLimits = dataSet.Tables[2].Rows // .Cast<DataRow>() // .Select(row => new TrendingAlarmLimit() // { // TimeStart = row.Field<DateTime>("thedatefrom").Subtract(epoch).TotalMilliseconds, // TimeEnd = row.Field<DateTime>("thedateto").Subtract(epoch).TotalMilliseconds, // High = row.Field<double?>("offlimithigh"), // Low = row.Field<double?>("offlimitlow") // }) // .ToArray(); //} string historianServer; string historianInstance; IEnumerable<int> channelIDs = new List<int>() { Convert.ToInt32(ChannelID) }; DateTime startDate = Convert.ToDateTime(targetDate); DateTime endDate = startDate.AddDays(1); TrendingDataSet trendingDataSet = new TrendingDataSet(); DateTime epoch = new DateTime(1970, 1, 1); using (AdoDataConnection connection = new AdoDataConnection(connectionstring, typeof(SqlConnection), typeof(SqlDataAdapter))) { historianServer = connection.ExecuteScalar<string>("SELECT Value FROM Setting WHERE Name = 'Historian.Server'") ?? "127.0.0.1"; historianInstance = connection.ExecuteScalar<string>("SELECT Value FROM Setting WHERE Name = 'Historian.Instance'") ?? "XDA"; using (Historian historian = new Historian(historianServer, historianInstance)) { foreach (openHistorian.XDALink.TrendingDataPoint point in historian.Read(channelIDs, startDate, endDate)) { if (!trendingDataSet.ChannelData.Exists(x => x.Time == point.Timestamp.Subtract(epoch).TotalMilliseconds)) { trendingDataSet.ChannelData.Add(new TrendingDataDatum()); trendingDataSet.ChannelData[trendingDataSet.ChannelData.Count - 1].Time = point.Timestamp.Subtract(epoch).TotalMilliseconds; } if (point.SeriesID.ToString() == "Average") trendingDataSet.ChannelData[trendingDataSet.ChannelData.IndexOf(x => x.Time == point.Timestamp.Subtract(epoch).TotalMilliseconds)].Average = point.Value; else if (point.SeriesID.ToString() == "Minimum") trendingDataSet.ChannelData[trendingDataSet.ChannelData.IndexOf(x => x.Time == point.Timestamp.Subtract(epoch).TotalMilliseconds)].Minimum = point.Value; else if (point.SeriesID.ToString() == "Maximum") trendingDataSet.ChannelData[trendingDataSet.ChannelData.IndexOf(x => x.Time == point.Timestamp.Subtract(epoch).TotalMilliseconds)].Maximum = point.Value; } } IEnumerable<DataRow> table = Enumerable.Empty<DataRow>(); table = connection.RetrieveData(" Select {0} AS thedatefrom, " + " DATEADD(DAY, 1, {0}) AS thedateto, " + " CASE WHEN AlarmRangeLimit.PerUnit <> 0 AND Channel.PerUnitValue IS NOT NULL THEN AlarmRangeLimit.High * PerUnitValue ELSE AlarmRangeLimit.High END AS alarmlimithigh," + " CASE WHEN AlarmRangeLimit.PerUnit <> 0 AND Channel.PerUnitValue IS NOT NULL THEN AlarmRangeLimit.Low * PerUnitValue ELSE AlarmRangeLimit.Low END AS alarmlimitlow " + " FROM AlarmRangeLimit JOIN " + " Channel ON AlarmRangeLimit.ChannelID = Channel.ID " + "WHERE AlarmRangeLimit.AlarmTypeID = (SELECT ID FROM AlarmType where Name = 'Alarm') AND " + " AlarmRangeLimit.ChannelID = {1}", startDate, Convert.ToInt32(ChannelID)).Select(); foreach (DataRow row in table) { trendingDataSet.AlarmLimits.Add(new TrendingAlarmLimit() { High = row.Field<double?>("alarmlimithigh"), Low = row.Field<double?>("alarmlimitlow"), TimeEnd = row.Field<DateTime>("thedateto").Subtract(epoch).TotalMilliseconds, TimeStart = row.Field<DateTime>("thedatefrom").Subtract(epoch).TotalMilliseconds }); } table = Enumerable.Empty<DataRow>(); table = connection.RetrieveData(" DECLARE @dayOfWeek INT = DATEPART(DW, {0}) - 1 " + " DECLARE @hourOfWeek INT = @dayOfWeek * 24 " + " ; WITH HourlyIndex AS" + " ( " + " SELECT @hourOfWeek AS HourOfWeek " + " UNION ALL " + " SELECT HourOfWeek + 1 " + " FROM HourlyIndex" + " WHERE (HourOfWeek + 1) < @hourOfWeek + 24" + " ) " + " SELECT " + " DATEADD(HOUR, HourlyIndex.HourOfWeek - @hourOfWeek, {0}) AS thedatefrom, " + " DATEADD(HOUR, HourlyIndex.HourOfWeek - @hourOfWeek + 1, {0}) AS thedateto, " + " HourOfWeekLimit.High AS offlimithigh, " + " HourOfWeekLimit.Low AS offlimitlow " + " FROM " + " HourlyIndex LEFT OUTER JOIN " + " HourOfWeekLimit ON HourOfWeekLimit.HourOfWeek = HourlyIndex.HourOfWeek " + " WHERE " + " HourOfWeekLimit.ChannelID IS NULL OR " + " HourOfWeekLimit.ChannelID = {1} ", startDate, Convert.ToInt32(ChannelID)).Select(); foreach (DataRow row in table) { trendingDataSet.OffNormalLimits.Add(new TrendingAlarmLimit() { High = row.Field<double?>("offlimithigh"), Low = row.Field<double?>("offlimitlow"), TimeEnd = row.Field<DateTime>("thedateto").Subtract(epoch).TotalMilliseconds, TimeStart = row.Field<DateTime>("thedatefrom").Subtract(epoch).TotalMilliseconds }); } } return trendingDataSet; }
/// <summary> /// Returns true if a data operation exists to run this class. Returns false otherwise. /// </summary> /// <param name="database">Database connection to use for checking the data operation</param> /// <returns>True or false indicating whether the operation exists</returns> private static bool DataOperationExists(AdoDataConnection database) { return(Convert.ToInt32(database.ExecuteScalar($"SELECT COUNT(*) FROM DataOperation WHERE TypeName='{typeof(PowerCalculationConfigurationValidation).FullName}' AND MethodName='ValidatePowerCalculationConfigurations'")) > 0); }