/// <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(); } } }
/// <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(); } }
/// <summary> /// Creates a new measurement object for power calculation output measurements /// </summary> private static PowerMeasurement CreateMeasurement(string companyAcronym, string deviceAcronym, string vendorAcronym, string signalTypeAcronym, string circuitDescription, int deviceID, int?historianID, string descriptionSuffix) { PowerMeasurement measurement = new PowerMeasurement { PointTag = CommonPhasorServices.CreatePointTag(companyAcronym, deviceAcronym, vendorAcronym, signalTypeAcronym), Adder = 0, Multiplier = 1, Description = $"{circuitDescription} {descriptionSuffix}", DeviceID = deviceID, HistorianID = historianID, SignalTypeID = 10, Enabled = true, SignalID = Guid.Empty }; if (measurement.PointTag != null) { int beginIndex = measurement.PointTag.IndexOf('_'); measurement.SignalReference = measurement.PointTag.Substring(beginIndex + 1); } return(measurement); }
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."); } }
/// <summary> /// Creates a new measurement object for power calculation output measurements /// </summary> private static PowerMeasurement CreateMeasurement(string companyAcronym, string deviceAcronym, string vendorAcronym, string signalTypeAcronym, string circuitDescription, int deviceID, int? historianID, string descriptionSuffix) { PowerMeasurement measurement = new PowerMeasurement { PointTag = CommonPhasorServices.CreatePointTag(companyAcronym, deviceAcronym, vendorAcronym, signalTypeAcronym), Adder = 0, Multiplier = 1, Description = $"{circuitDescription} {descriptionSuffix}", DeviceID = deviceID, HistorianID = historianID, SignalTypeID = 10, Enabled = true, SignalID = Guid.Empty }; if (measurement.PointTag != null) { int beginIndex = measurement.PointTag.IndexOf('_'); measurement.SignalReference = measurement.PointTag.Substring(beginIndex + 1); } return measurement; }