// Creates a measurement to define the average frequency signal. private void CreateOutputMeasurement() { Device virtualDevice = null; Measurement inputMeasurement; Measurement outputMeasurement; if (!string.IsNullOrEmpty(m_selectedVirtualDeviceName)) { virtualDevice = m_dataModel.Devices .Single(dev => dev.Acronym == m_selectedVirtualDeviceName); } inputMeasurement = m_dataModel.Measurements .Single(measurement => measurement.ID == m_selectedFrequencyMeasurement.Key); outputMeasurement = new Measurement { HistorianID = inputMeasurement.HistorianID, DeviceID = ((object)virtualDevice != null) ? virtualDevice.ID : (int?)null, PointTag = string.Format("{0}_{1}", m_selectedCalculatorName, inputMeasurement.PointTag), SignalTypeID = inputMeasurement.SignalTypeID, SignalReference = string.Format("{0}-FQ", m_selectedVirtualDeviceName ?? m_selectedCalculatorName), Description = string.Format("Average {0}", inputMeasurement.Description), Internal = true, Subscribed = false, Enabled = true }; Measurement.Save(null, outputMeasurement); }
public DataModel() { using (AdoDataConnection database = new AdoDataConnection(CommonFunctions.DefaultSettingsCategory)) { Adapters = Adapter.Load(database, AdapterType.Action, Adapter.LoadIDs(database, AdapterType.Action, "", "")).ToList(); Devices = Device.Load(database, Device.LoadKeys(database)).ToList(); Measurements = Measurement.GetMeasurements(database, "WHERE SignalAcronym = 'FREQ'").ToList(); } }
// Deletes the output measurement definition for the mapping that was removed. private void DeleteOutputMeasurement(string outKey) { Measurement measurement = m_dataModel.Measurements .SingleOrDefault(m => m.ID == outKey); if ((object)measurement != null) { Measurement.Delete(null, measurement.SignalID); } }
// Creates an IOMapping object between the given input and output measurement keys. private IOMapping MapKeys(string inKey, string outKey) { Measurement freq = m_dataModel.Measurements.SingleOrDefault(m => m.ID == inKey); string signalReference = ((object)freq != null) ? freq.SignalReference : null; return(new IOMapping { SignalReference = signalReference, InputKey = inKey, OutputKey = outKey }); }
// Updates the parent device of the output measurements // to the currently selected virtual device. private void UpdateVirtualDevice() { Device virtualDevice = m_dataModel.Devices.Single(device => device.Acronym == m_selectedVirtualDeviceName); IEnumerable <Measurement> outputMeasurements = m_dataModel.Measurements .Where(measurement => m_ioMappings.Any(mapping => measurement.ID == mapping.OutputKey)); using (AdoDataConnection database = new AdoDataConnection(CommonFunctions.DefaultSettingsCategory)) { foreach (Measurement outputMeasurement in outputMeasurements) { outputMeasurement.DeviceID = virtualDevice.ID; Measurement.Save(database, outputMeasurement); } } }
/// <summary> /// Saves <see cref="Phasor"/> information to database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="phasor">Information about <see cref="Phasor"/>.</param> /// <param name="oldSourceIndex">The old source index of the phasor.</param> /// <param name="skipMeasurementUpdate">Skips associated measurement update if this is already being handled.</param> /// <returns>String, for display use, indicating success.</returns> public static string SaveAndReorder(AdoDataConnection database, Phasor phasor, int oldSourceIndex, bool skipMeasurementUpdate = false) { bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); if (phasor.SourceIndex == 0) phasor.SourceIndex = database.ExecuteScalar<int>("SELECT MAX(SourceIndex) FROM Phasor WHERE DeviceID = {0}", phasor.DeviceID) + 1; // Since phasors could be reordered in the source device, this test could inadvertently throw an exception when it should not - so the validation has been removed //if (database.ExecuteScalar<int>("SELECT COUNT(*) FROM Phasor WHERE ID <> {0} AND DeviceID = {1} AND SourceIndex = {2}", phasor.ID, phasor.DeviceID, phasor.SourceIndex) > 0) // throw new InvalidOperationException("Phasor source index must be unique per device."); if (phasor.ID == 0) { query = database.ParameterizedQueryString("INSERT INTO Phasor (DeviceID, Label, Type, Phase, SourceIndex, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) " + "VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8})", "deviceID", "label", "type", "phase", "sourceIndex", "updatedBy", "updatedOn", "createdBy", "createdOn"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, phasor.DeviceID, phasor.Label, phasor.Type, phasor.Phase, phasor.SourceIndex, CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow); } else { query = database.ParameterizedQueryString("UPDATE Phasor SET DeviceID = {0}, Label = {1}, Type = {2}, Phase = {3}, SourceIndex = {4}, " + "UpdatedBy = {5}, UpdatedOn = {6} WHERE ID = {7}", "deviceID", "label", "type", "phase", "sourceIndex", "updatedBy", "updatedOn", "id"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, phasor.DeviceID, phasor.Label, phasor.Type, phasor.Phase, phasor.SourceIndex, CommonFunctions.CurrentUser, database.UtcNow, phasor.ID); } // Get reference to the device to which phasor is being added. Device device = Device.GetDevice(database, "WHERE ID = " + phasor.DeviceID); // Get Phasor signal types. ObservableCollection<SignalType> signals; if (phasor.Type == "V") signals = SignalType.GetVoltagePhasorSignalTypes(); else signals = SignalType.GetCurrentPhasorSignalTypes(); // Get reference to phasor which has just been added. Phasor addedPhasor = GetPhasor(database, "WHERE DeviceID = " + phasor.DeviceID + " AND SourceIndex = " + phasor.SourceIndex); foreach (SignalType signal in signals) { Measurement measurement = Measurement.GetMeasurement(database, "WHERE DeviceID = " + phasor.DeviceID + " AND SignalTypeSuffix = '" + signal.Suffix + "' AND PhasorSourceIndex = " + oldSourceIndex); if ((object)measurement == null) { measurement = new Measurement(); measurement.DeviceID = device.ID; measurement.HistorianID = device.HistorianID; measurement.PointTag = CommonPhasorServices.CreatePointTag(device.CompanyAcronym, device.Acronym, device.VendorAcronym, signal.Acronym, addedPhasor.SourceIndex, addedPhasor.Phase[0]); measurement.SignalReference = device.Acronym + "-" + signal.Suffix + addedPhasor.SourceIndex; measurement.SignalTypeID = signal.ID; measurement.Description = device.Name + " " + addedPhasor.Label + " " + device.VendorDeviceName + " " + addedPhasor.Phase + " " + signal.Name; measurement.PhasorSourceIndex = addedPhasor.SourceIndex; measurement.Enabled = true; Measurement.Save(database, measurement); } else if (!skipMeasurementUpdate || addedPhasor.SourceIndex != oldSourceIndex) // || measurement.SignalTypeID != signal.ID { // Update existing record when needed or when phasor source index has changed measurement.HistorianID = device.HistorianID; measurement.PointTag = CommonPhasorServices.CreatePointTag(device.CompanyAcronym, device.Acronym, device.VendorAcronym, signal.Acronym, addedPhasor.SourceIndex, addedPhasor.Phase[0]); measurement.SignalReference = device.Acronym + "-" + signal.Suffix + addedPhasor.SourceIndex; measurement.SignalTypeID = signal.ID; measurement.Description = device.Name + " " + addedPhasor.Label + " " + device.VendorDeviceName + " " + addedPhasor.Phase + " " + signal.Name; measurement.PhasorSourceIndex = addedPhasor.SourceIndex; Measurement.Save(database, measurement); } } return "Phasor information saved successfully"; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Saves <see cref="Phasor"/> information to database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="phasor">Information about <see cref="Phasor"/>.</param> /// <param name="oldSourceIndex">The old source index of the phasor.</param> /// <param name="skipMeasurementUpdate">Skips associated measurement update if this is already being handled.</param> /// <returns>String, for display use, indicating success.</returns> public static string SaveAndReorder(AdoDataConnection database, Phasor phasor, int oldSourceIndex, bool skipMeasurementUpdate = false) { bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); if (phasor.SourceIndex == 0) { phasor.SourceIndex = database.ExecuteScalar <int>("SELECT MAX(SourceIndex) FROM Phasor WHERE DeviceID = {0}", phasor.DeviceID) + 1; } // Since phasors could be reordered in the source device, this test could inadvertently throw an exception when it should not - so the validation has been removed //if (database.ExecuteScalar<int>("SELECT COUNT(*) FROM Phasor WHERE ID <> {0} AND DeviceID = {1} AND SourceIndex = {2}", phasor.ID, phasor.DeviceID, phasor.SourceIndex) > 0) // throw new InvalidOperationException("Phasor source index must be unique per device."); if (phasor.ID == 0) { query = database.ParameterizedQueryString("INSERT INTO Phasor (DeviceID, Label, Type, Phase, BaseKV, SourceIndex, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) " + "VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9})", "deviceID", "label", "type", "phase", "baseKV", "sourceIndex", "updatedBy", "updatedOn", "createdBy", "createdOn"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, phasor.DeviceID, phasor.Label, phasor.Type, phasor.Phase, phasor.BaseKV, phasor.SourceIndex, CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow); } else { query = database.ParameterizedQueryString("UPDATE Phasor SET DeviceID = {0}, Label = {1}, Type = {2}, Phase = {3}, BaseKV = {4}, SourceIndex = {5}, " + "UpdatedBy = {6}, UpdatedOn = {7} WHERE ID = {8}", "deviceID", "label", "type", "phase", "baseKV", "sourceIndex", "updatedBy", "updatedOn", "id"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, phasor.DeviceID, phasor.Label, phasor.Type, phasor.Phase, phasor.BaseKV, phasor.SourceIndex, CommonFunctions.CurrentUser, database.UtcNow, phasor.ID); } // Get reference to the device to which phasor is being added. Device device = Device.GetDevice(database, "WHERE ID = " + phasor.DeviceID); // Get Phasor signal types. ObservableCollection <SignalType> signals; if (phasor.Type == "V") { signals = SignalType.GetVoltagePhasorSignalTypes(); } else { signals = SignalType.GetCurrentPhasorSignalTypes(); } // Get reference to phasor which has just been added. Phasor addedPhasor = GetPhasor(database, "WHERE DeviceID = " + phasor.DeviceID + " AND SourceIndex = " + phasor.SourceIndex); foreach (SignalType signal in signals) { Measurement measurement = Measurement.GetMeasurement(database, "WHERE DeviceID = " + phasor.DeviceID + " AND SignalTypeSuffix = '" + signal.Suffix + "' AND PhasorSourceIndex = " + oldSourceIndex); if ((object)measurement == null) { measurement = new Measurement(); measurement.DeviceID = device.ID; measurement.HistorianID = device.HistorianID; measurement.PointTag = CommonPhasorServices.CreatePointTag(device.CompanyAcronym, device.Acronym, device.VendorAcronym, signal.Acronym, addedPhasor.Label, addedPhasor.SourceIndex, addedPhasor.Phase[0], addedPhasor.BaseKV); measurement.SignalReference = device.Acronym + "-" + signal.Suffix + addedPhasor.SourceIndex; measurement.SignalTypeID = signal.ID; measurement.Description = device.Name + " " + addedPhasor.Label + " " + device.VendorDeviceName + " " + addedPhasor.Phase + " " + signal.Name; measurement.PhasorSourceIndex = addedPhasor.SourceIndex; measurement.Enabled = true; Measurement.Save(database, measurement); } else if (!skipMeasurementUpdate || addedPhasor.SourceIndex != oldSourceIndex) // || measurement.SignalTypeID != signal.ID { // Update existing record when needed or when phasor source index has changed measurement.HistorianID = device.HistorianID; measurement.PointTag = CommonPhasorServices.CreatePointTag(device.CompanyAcronym, device.Acronym, device.VendorAcronym, signal.Acronym, addedPhasor.Label, addedPhasor.SourceIndex, addedPhasor.Phase[0], addedPhasor.BaseKV); measurement.SignalReference = device.Acronym + "-" + signal.Suffix + addedPhasor.SourceIndex; measurement.SignalTypeID = signal.ID; measurement.Description = device.Name + " " + addedPhasor.Label + " " + device.VendorDeviceName + " " + addedPhasor.Phase + " " + signal.Name; measurement.PhasorSourceIndex = addedPhasor.SourceIndex; Measurement.Save(database, measurement); } } return("Phasor information saved successfully"); } finally { if (createdConnection && database != null) { database.Dispose(); } } }
// Creates a measurement to define the average frequency signal. private void CreateOutputMeasurement() { Device virtualDevice = null; Measurement inputMeasurement; Measurement outputMeasurement; if (!string.IsNullOrEmpty(m_selectedVirtualDeviceName)) { virtualDevice = m_dataModel.Devices .Single(dev => dev.Acronym == m_selectedVirtualDeviceName); } inputMeasurement = m_dataModel.Measurements .Single(measurement => measurement.ID == m_selectedFrequencyMeasurement.Key); outputMeasurement = new Measurement { HistorianID = inputMeasurement.HistorianID, DeviceID = ((object)virtualDevice != null) ? virtualDevice.ID : (int?)null, PointTag = string.Format("{0}_{1}", m_selectedCalculatorName, inputMeasurement.PointTag), SignalTypeID = inputMeasurement.SignalTypeID, SignalReference = string.Format("{0}-FQ", m_selectedVirtualDeviceName ?? m_selectedCalculatorName), Description = string.Format("Average {0}", inputMeasurement.Description), Internal = true, Subscribed = false, Enabled = true }; Measurement.Save(null, outputMeasurement); }
// Creates a measurement associated with the given alarm and returns the new measurements signal ID. private static Guid? CreateAlarmMeasurement(AdoDataConnection database, Alarm alarm) { object nodeID; Historian historian; Measurement alarmMeasurement; int signalTypeId; try { nodeID = (alarm.NodeID != Guid.Empty) ? database.Guid(alarm.NodeID) : database.CurrentNodeID(); historian = Historian.GetHistorian(database, string.Format("WHERE Acronym = 'STAT' AND NodeID = '{0}'", nodeID)); signalTypeId = Convert.ToInt32(database.Connection.ExecuteScalar("SELECT ID FROM SignalType WHERE Acronym = 'ALRM'", DefaultTimeout)); alarmMeasurement = new Measurement() { HistorianID = historian.ID, PointTag = alarm.TagName, SignalTypeID = signalTypeId, SignalReference = "ALARM!SERVICES-AL" + alarm.ID, Description = "Measurement associated with alarm " + alarm.ID, Internal = true, Enabled = true }; Measurement.Save(database, alarmMeasurement); alarmMeasurement = Measurement.GetMeasurement(database, string.Format("WHERE PointTag = '{0}'", alarm.TagName)); return alarmMeasurement.SignalID; } catch { // Return null to indicate measurement // was not saved to database return null; } }
/// <summary> /// Loads <see cref="Measurement"/> information as an <see cref="ObservableCollection{T}"/> style list. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="keys">Keys of the measurements to be loaded from the database.</param> /// <returns>Collection of <see cref="Measurement"/>.</returns> public static ObservableCollection<Measurement> LoadFromKeys(AdoDataConnection database, List<Guid> keys) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); string query; string commaSeparatedKeys; Measurement[] measurementList = null; DataTable measurementTable; Guid signalID; if ((object)keys != null && keys.Count > 0) { commaSeparatedKeys = keys.Select(key => "'" + key.ToString() + "'").Aggregate((str1, str2) => str1 + "," + str2); query = string.Format("SELECT * FROM MeasurementDetail WHERE SignalID IN ({0})", commaSeparatedKeys); measurementTable = database.Connection.RetrieveData(database.AdapterType, query); measurementList = new Measurement[measurementTable.Rows.Count]; foreach (DataRow row in measurementTable.Rows) { signalID = database.Guid(row, "SignalID"); measurementList[keys.IndexOf(signalID)] = new Measurement(false) { SignalID = signalID, HistorianID = row.ConvertNullableField<int>("HistorianID"), PointID = row.ConvertField<int>("PointID"), DeviceID = row.ConvertNullableField<int>("DeviceID"), PointTag = row.Field<string>("PointTag"), AlternateTag = row.Field<string>("AlternateTag"), SignalTypeID = row.ConvertField<int>("SignalTypeID"), PhasorSourceIndex = row.ConvertNullableField<int>("PhasorSourceIndex"), SignalReference = row.Field<string>("SignalReference"), Adder = row.ConvertField<double>("Adder"), Multiplier = row.ConvertField<double>("Multiplier"), Internal = Convert.ToBoolean(row.Field<object>("Internal")), Subscribed = Convert.ToBoolean(row.Field<object>("Subscribed")), Description = row.Field<string>("Description"), Enabled = Convert.ToBoolean(row.Field<object>("Enabled")), m_historianAcronym = row.Field<string>("HistorianAcronym"), m_deviceAcronym = row.Field<object>("DeviceAcronym") == null ? string.Empty : row.Field<string>("DeviceAcronym"), m_signalName = row.Field<string>("SignalName"), m_signalAcronym = row.Field<string>("SignalAcronym"), m_signalSuffix = row.Field<string>("SignalTypeSuffix"), m_phasorLabel = row.Field<string>("PhasorLabel"), m_framesPerSecond = Convert.ToInt32(row.Field<object>("FramesPerSecond") ?? 30), m_id = row.Field<string>("ID"), m_companyAcronym = row.Field<object>("CompanyAcronym") == null ? string.Empty : row.Field<string>("CompanyAcronym"), m_companyName = row.Field<object>("CompanyName") == null ? string.Empty : row.Field<string>("CompanyName"), Selected = false }; } } return new ObservableCollection<Measurement>(measurementList ?? new Measurement[0]); } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Retrieves a <see cref="Measurement"/> information from the database based on query string filter. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="whereClause"><see cref="AdoDataConnection"/> to connection to database.</param> /// <returns><see cref="Measurement"/> information.</returns> public static Measurement GetMeasurement(AdoDataConnection database, string whereClause) { bool createdConnection = false; DataTable measurementTable; DataRow row; try { createdConnection = CreateConnection(ref database); measurementTable = database.Connection.RetrieveData(database.AdapterType, "SELECT * FROM MeasurementDetail " + whereClause); if (measurementTable.Rows.Count == 0) return null; row = measurementTable.Rows[0]; Measurement measurement = new Measurement { SignalID = database.Guid(row, "SignalID"), HistorianID = row.ConvertNullableField<int>("HistorianID"), PointID = row.ConvertField<int>("PointID"), DeviceID = row.ConvertNullableField<int>("DeviceID"), PointTag = row.Field<string>("PointTag"), AlternateTag = row.Field<string>("AlternateTag"), SignalTypeID = row.ConvertField<int>("SignalTypeID"), PhasorSourceIndex = row.ConvertNullableField<int>("PhasorSourceIndex"), SignalReference = row.Field<string>("SignalReference"), Adder = row.ConvertField<double>("Adder"), Multiplier = row.ConvertField<double>("Multiplier"), Description = row.Field<string>("Description"), Enabled = Convert.ToBoolean(row.Field<object>("Enabled")), m_historianAcronym = row.Field<string>("HistorianAcronym"), m_deviceAcronym = row.Field<object>("DeviceAcronym") == null ? string.Empty : row.Field<string>("DeviceAcronym"), m_signalName = row.Field<string>("SignalName"), m_signalAcronym = row.Field<string>("SignalAcronym"), m_signalSuffix = row.Field<string>("SignalTypeSuffix"), m_phasorLabel = row.Field<string>("PhasorLabel"), m_framesPerSecond = Convert.ToInt32(row.Field<object>("FramesPerSecond") ?? 30), m_id = row.Field<string>("ID"), m_companyAcronym = row.Field<object>("CompanyAcronym") == null ? string.Empty : row.Field<string>("CompanyAcronym"), m_companyName = row.Field<object>("CompanyName") == null ? string.Empty : row.Field<string>("CompanyName"), Selected = false }; return measurement; } finally { if (createdConnection && database != null) database.Dispose(); } }
/// <summary> /// Saves <see cref="Measurement"/> information to database. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="measurement">Information about <see cref="Measurement"/>.</param> /// <returns>String, for display use, indicating success.</returns> public static string Save(AdoDataConnection database, Measurement measurement) { bool createdConnection = false; string query; try { createdConnection = CreateConnection(ref database); if (measurement.PointID == 0) { query = database.ParameterizedQueryString("INSERT INTO Measurement (HistorianID, DeviceID, PointTag, AlternateTag, SignalTypeID, PhasorSourceIndex, " + "SignalReference, Adder, Multiplier, Subscribed, Internal, Description, Enabled, UpdatedBy, UpdatedOn, CreatedBy, CreatedOn) VALUES ({0}, {1}, {2}, " + "{3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15}, {16})", "historianID", "deviceID", "pointTag", "alternateTag", "signalTypeID", "phasorSourceIndex", "signalReference", "adder", "multiplier", "subscribed", "internal", "description", "enabled", "updatedBy", "updatedOn", "createdBy", "createdOn"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, measurement.HistorianID.ToNotNull(), measurement.DeviceID.ToNotNull(), measurement.PointTag, measurement.AlternateTag.ToNotNull(), measurement.SignalTypeID, measurement.PhasorSourceIndex ?? measurement.PhasorSourceIndex.ToNotNull(), measurement.SignalReference, measurement.Adder, measurement.Multiplier, database.Bool(measurement.Subscribed), database.Bool(measurement.Internal), measurement.Description.ToNotNull(), database.Bool(measurement.Enabled), CommonFunctions.CurrentUser, database.UtcNow, CommonFunctions.CurrentUser, database.UtcNow); } else { query = database.ParameterizedQueryString("UPDATE Measurement SET HistorianID = {0}, DeviceID = {1}, PointTag = {2}, AlternateTag = {3}, " + "SignalTypeID = {4}, PhasorSourceIndex = {5}, SignalReference = {6}, Adder = {7}, Multiplier = {8}, Description = {9}, Subscribed = {10}, " + "Internal = {11}, Enabled = {12}, UpdatedBy = {13}, UpdatedOn = {14} WHERE PointID = {15}", "historianID", "deviceID", "pointTag", "alternateTag", "signalTypeID", "phasorSourceINdex", "signalReference", "adder", "multiplier", "description", "subscribed", "internal", "enabled", "updatedBy", "updatedOn", "pointID"); database.Connection.ExecuteNonQuery(query, DefaultTimeout, measurement.HistorianID.ToNotNull(), measurement.DeviceID.ToNotNull(), measurement.PointTag, measurement.AlternateTag.ToNotNull(), measurement.SignalTypeID, measurement.PhasorSourceIndex ?? measurement.PhasorSourceIndex.ToNotNull(), measurement.SignalReference, measurement.Adder, measurement.Multiplier, measurement.Description.ToNotNull(), database.Bool(measurement.Subscribed), database.Bool(measurement.Internal), database.Bool(measurement.Enabled), CommonFunctions.CurrentUser, database.UtcNow, measurement.PointID); } return "Measurement information saved successfully"; } finally { if (createdConnection && database != null) database.Dispose(); } }