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."); } }
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, " + $" d.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 = 1 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); 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."); } }