//******************************************************************************************************//  PowerCalculationConfigurationValidation.cs - Gbtc////  Copyright © 2012, Grid Protection Alliance.  All Rights Reserved.////  Licensed to the Grid Protection Alliance (GPA) under one or more contributor license agreements. See//  the NOTICE file distributed with this work for additional information regarding copyright ownership.//  The GPA licenses this file to you under the MIT License (MIT), the "License"; you may//  not use this file except in compliance with the License. You may obtain a copy of the License at:////      http://www.opensource.org/licenses/MIT////  Unless agreed to in writing, the subject software distributed under the License is distributed on an//  "AS-IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. Refer to the//  License for the specific language governing permissions and limitations.////  Code Modification History://  ----------------------------------------------------------------------------------------------------//  11/2/2015 - Ryan McCoy//       Generated original version of source code.////****************************************************************************************************** using System;using System.Collections.Generic;using System.Data;using System.Diagnostics.CodeAnalysis;using GSF.Configuration;using GSF.Data;using PhasorProtocolAdapters; namespace PowerCalculations.PowerMultiCalculator{    #region [ Static ]     /// <summary>    /// Implements validations for power calculations in the power calculation adapter    /// </summary>    public static class PowerCalculationConfigurationValidation    {        /// <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>        /// 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;        }         /// <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)");        }         /// <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>        /// 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)");        }         /// <summary>        /// Data operation to validate power calculation configuration. This method checks that input measurements and non-null output measurements exist, are enabled, and have the correct signal type.        /// If null output measurements are found in the configuration, this method will create the output measurements and update the configuration.        /// </summary>        /// <param name="database">Database connection for configuration information</param>        /// <param name="nodeIDQueryString">Node ID for database queries</param>        /// <param name="trackingVersion">Not used</param>        /// <param name="arguments">Not used</param>        /// <param name="statusMessage">Delegate for method to communicate status updates</param>        /// <param name="processException">Exception handling delegate</param>        public static void ValidatePowerCalculationConfigurations(AdoDataConnection database, string nodeIDQueryString, ulong trackingVersion, string arguments, Action<string> statusMessage, Action<Exception> processException)        {            try            {                CheckInputMeasurementsExist(database, nodeIDQueryString, statusMessage);                CheckInputMeasurementsAreEnabled(database, nodeIDQueryString, statusMessage);                CheckInputMeasurementsSignalType(database, nodeIDQueryString, statusMessage);                CheckOutputMeasurementsExist(database, nodeIDQueryString, statusMessage);                CheckOutputMeasurementsAreEnabled(database, nodeIDQueryString, statusMessage);                CheckOutputMeasurementsSignalType(database, nodeIDQueryString, statusMessage);                CreateOutputMeasurementsWhereNull(database, nodeIDQueryString, statusMessage);            }            catch (Exception e)            {                processException(e);            }        }           /// <summary>        /// Checks power calculation configuration for null output measurements. When found, a new measurement is created, and the configuration is updated to use the new measurement.        /// </summary>        /// <param name="database">Configuration database connection</param>        /// <param name="nodeIDQueryString">Node ID formatted for query</param>        /// <param name="statusMessage">Delegate for communicating status updates</param>        [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "Code has been evaluated, SQL text used by function is not based on user input.")]        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;        }         /// <summary>        /// Updated power calculation to use new Signal IDs for output measurement        /// </summary>        /// <param name="database">Configuration database connection</param>        /// <param name="powerCalculationID">ID of the power calculation to be updated</param>        /// <param name="activePowerOutputSignalID">Active power output signal ID, if needed for update</param>        /// <param name="reactivePowerOutputSignalID">Reactive power output signal ID, if needed for update</param>        /// <param name="apparentPowerOutputSignalID">Apparent power output signal ID, if needed for update</param>        [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "Code has been evaluated, SQL generated by function is based non-text based numeric input.")]        private static void UpdatePowerCalculation(AdoDataConnection database, int powerCalculationID, Guid? activePowerOutputSignalID = null, Guid? reactivePowerOutputSignalID = null, Guid? apparentPowerOutputSignalID = null)        {            if (activePowerOutputSignalID == null && reactivePowerOutputSignalID == null && apparentPowerOutputSignalID == null)                return;             List<string> updates = new List<string>();            if (activePowerOutputSignalID != null)                updates.Add($"ActivePowerOutputSignalID='{activePowerOutputSignalID.Value}'");            if (reactivePowerOutputSignalID != null)                updates.Add($"ReactivePowerOutputSignalID='{reactivePowerOutputSignalID.Value}'");            if (apparentPowerOutputSignalID != null)                updates.Add($"ApparentPowerOutputSignalID='{apparentPowerOutputSignalID.Value}'");             string query = "UPDATE PowerCalculation SET ";            query += string.Join(", ", updates) + " ";            query += $"WHERE ID={powerCalculationID} ";             using (IDbCommand cmd = database.Connection.CreateCommand())            {                cmd.CommandText = query;                cmd.ExecuteNonQuery();            }        }         /// <summary>        /// Verifies that output measurements are set to calculated signal type. Calculations are disabled if output measurements are not configured correctly.        /// </summary>        /// <param name="database">Configuration database connection</param>        /// <param name="nodeIDQueryString">Node ID formatted for query</param>        /// <param name="statusMessage">Delegate to communicate status updates</param>        private static void CheckOutputMeasurementsSignalType(AdoDataConnection database, string nodeIDQueryString, Action<string> statusMessage)        {            statusMessage("Validating if non-null output measurements have correct signal type...");             string query =                $"UPDATE PowerCalculation " +                $"SET Enabled = 0 " +                $"WHERE " +                $"    Enabled = 1 AND " +                $"    NodeID = {nodeIDQueryString} AND " +                $"    ( " +                $"        (SELECT Acronym FROM Measurement JOIN SignalType ON Measurement.SignalTypeID = SignalType.ID WHERE SignalID = ActivePowerOutputSignalID) <> 'CALC' OR " +                $"        (SELECT Acronym FROM Measurement JOIN SignalType ON Measurement.SignalTypeID = SignalType.ID WHERE SignalID = ActivePowerOutputSignalID) <> 'CALC' OR " +                $"        (SELECT Acronym FROM Measurement JOIN SignalType ON Measurement.SignalTypeID = SignalType.ID WHERE SignalID = ActivePowerOutputSignalID) <> 'CALC' " +                $"    )";             const string failureMessage = "One or more power calculations are defined with non-null output measurements having the wrong signal type. Power calculations will be disabled.";             RunDatabaseValidation(database, statusMessage, query, failureMessage);        }         /// <summary>        /// Verifies that output measurements are enabled. Power calculations will be disabled where outputs are disabled.        /// </summary>        /// <param name="database"></param>        /// <param name="nodeIDQueryString"></param>        /// <param name="statusMessage"></param>        private static void CheckOutputMeasurementsAreEnabled(AdoDataConnection database, string nodeIDQueryString, Action<string> statusMessage)        {            statusMessage("Validating if non-null output measurements are enabled...");             string query =                $"UPDATE PowerCalculation " +                $"SET Enabled = 0 " +                $"WHERE " +                $"    Enabled = 1 AND " +                $"    NodeID = {nodeIDQueryString} AND " +                $"    (SELECT COUNT(*) FROM Measurement WHERE Enabled = 0 AND SignalID IN (ActivePowerOutputSignalID, ReactivePowerOutputSignalID, ApparentPowerOutputSignalID)) > 0";             const string failureMessage = "One or more power calculations are defined with output measurements that are disabled. Power calculations will be disabled.";             RunDatabaseValidation(database, statusMessage, query, failureMessage);        }         /// <summary>        /// Verifies that output measurements exist in the measurement table. Power calculations are disabled where the configured outputs do not exist.        /// </summary>        /// <param name="database">Configuration database connection</param>        /// <param name="nodeIDQueryString">Node ID formatted for query</param>        /// <param name="statusMessage">Delegate to communicate status updates</param>        private static void CheckOutputMeasurementsExist(AdoDataConnection database, string nodeIDQueryString, Action<string> statusMessage)        {            statusMessage("Validating if non-null output measurements exist...");             string query =                 $"UPDATE PowerCalculation SET Enabled=0 WHERE Enabled=1 AND NodeID={nodeIDQueryString} " +                 "AND ((ActivePowerOutputSignalID IS NOT NULL AND ActivePowerOutputSignalID NOT IN (SELECT SignalID from Measurement)) " +                 "OR (ReactivePowerOutputSignalID IS NOT NULL AND ReactivePowerOutputSignalID NOT IN (SELECT SignalID from Measurement)) " +                 "OR (ApparentPowerOutputSignalID IS NOT NULL AND ApparentPowerOutputSignalID NOT IN (SELECT SignalID from Measurement)))";             const string failureMessage = "One or more power calculations are defined with non-null output measurements that do not exist. Power calculations will be disabled.";             RunDatabaseValidation(database, statusMessage, query, failureMessage);        }         /// <summary>        /// Validates that input measurements have the correct signal type. Power calculations are disabled where inputs have the wrong signal types.        /// </summary>        /// <param name="database">Configuration database connection</param>        /// <param name="nodeIDQueryString">Node ID formatted for database query</param>        /// <param name="statusMessage">Delegate for status updates</param>        private static void CheckInputMeasurementsSignalType(AdoDataConnection database, string nodeIDQueryString, Action<string> statusMessage)        {            statusMessage("Validating if input measurements have correct signal type...");             string query =                $"UPDATE PowerCalculation " +                $"SET Enabled = 0 " +                $"WHERE " +                $"    Enabled = 1 AND " +                $"    NodeID = {nodeIDQueryString} AND " +                $"    ( " +                $"        (SELECT Acronym FROM Measurement JOIN SignalType ON Measurement.SignalTypeID = SignalType.ID WHERE SignalID = VoltageAngleSignalID) <> 'VPHA' OR " +                $"        (SELECT Acronym FROM Measurement JOIN SignalType ON Measurement.SignalTypeID = SignalType.ID WHERE SignalID = VoltageMagSignalID) <> 'VPHM' OR " +                $"        (SELECT Acronym FROM Measurement JOIN SignalType ON Measurement.SignalTypeID = SignalType.ID WHERE SignalID = CurrentAngleSignalID) <> 'IPHA' OR " +                $"        (SELECT Acronym FROM Measurement JOIN SignalType ON Measurement.SignalTypeID = SignalType.ID WHERE SignalID = CurrentMagSignalID) <> 'IPHM' " +                $"    )";             const string failureMessage = "One or more power calculations are defined with input measurements having the wrong signal type. Power calculations will be disabled.";             RunDatabaseValidation(database, statusMessage, query, failureMessage);        }         /// <summary>        /// Verifies that input measurements are enabled. Power calculations are disabled where input measurements are disabled.        /// </summary>        /// <param name="database">Configuration database connection</param>        /// <param name="nodeIDQueryString">Node ID formatted for query</param>        /// <param name="statusMessage">Delegate for communicating status updates</param>        private static void CheckInputMeasurementsAreEnabled(AdoDataConnection database, string nodeIDQueryString, Action<string> statusMessage)        {            statusMessage("Validating if input measurements are enabled...");             string query =                $"UPDATE PowerCalculation " +                $"SET Enabled = 0 " +                $"WHERE " +                $"    Enabled = 1 AND " +                $"    NodeID = {nodeIDQueryString} AND " +                $"    (SELECT COUNT(*) FROM Measurement WHERE Enabled = 0 AND SignalID IN (VoltageAngleSignalID, VoltageMagSignalID, CurrentAngleSignalID, CurrentMagSignalID)) > 0";             const string failureMessage = "One or more power calculations are defined with input measurements that are disabled. Power calculations will be disabled.";             RunDatabaseValidation(database, statusMessage, query, failureMessage);        }         /// <summary>        /// Verifies that input measurements exist. Power calculations are disabled where input measurements do not exist.        /// </summary>        /// <param name="database">Configuration database connection</param>        /// <param name="nodeIDQueryString">Node ID formatted for query</param>        /// <param name="statusMessage">Delegate for communicating status updates</param>        private static void CheckInputMeasurementsExist(AdoDataConnection database, string nodeIDQueryString, Action<string> statusMessage)        {            statusMessage("Validating if input measurements exist...");             string query =                 $"UPDATE PowerCalculation SET Enabled=0 WHERE Enabled=1 AND NodeID={nodeIDQueryString}" +                 "AND (VoltageMagSignalID NOT IN (SELECT SignalID from Measurement) " +                 "OR VoltageAngleSignalID NOT IN (SELECT SignalID from Measurement) " +                 "OR CurrentMagSignalID NOT IN (SELECT SignalID from Measurement) " +                 "OR CurrentAngleSignalID NOT IN (SELECT SignalID from Measurement))";             const string failureMessage = "One or more power calculations are defined with input measurements that do not exist. Power calculations will be disabled.";             RunDatabaseValidation(database, statusMessage, query, failureMessage);        }         [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "Code has been evaluated, functions calling this method create SQL text that is not based on user input.")]        private static void RunDatabaseValidation(AdoDataConnection database, Action<string> statusMessage, string query, string failureMessage)        {            using (IDbCommand cmd = database.Connection.CreateCommand())            {                cmd.CommandText = query;                 int affectedRows = cmd.ExecuteNonQuery();                 if (affectedRows > 0)                    statusMessage(failureMessage);            }        }         #endregion    }}