/// <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> /// Retrieves <see cref="Phasor"/> information based on query string filter. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="whereClause">query string to filter data.</param> /// <returns><see cref="Phasor"/> information.</returns> public static Phasor GetPhasor(AdoDataConnection database, string whereClause) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); DataTable phasorTable = database.Connection.RetrieveData(database.AdapterType, "SELECT * FROM PhasorDetail " + whereClause); if (phasorTable.Rows.Count == 0) return null; DataRow row = phasorTable.Rows[0]; Phasor phasor = new Phasor { ID = row.ConvertField<int>("ID"), DeviceID = row.ConvertField<int>("DeviceID"), Label = row.Field<string>("Label"), Type = row.Field<string>("Type"), Phase = row.Field<string>("Phase"), SourceIndex = row.ConvertField<int>("SourceIndex") }; return phasor; } 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> /// <returns>String, for display use, indicating success.</returns> public static string Save(AdoDataConnection database, Phasor phasor) { return SaveAndReorder(database, phasor, phasor.SourceIndex); }
/// <summary> /// Saves <see cref="Phasor"/> information to database and skips associated measurement update. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="phasor">Information about <see cref="Phasor"/>.</param> /// <returns>String, for display use, indicating success.</returns> public static string SaveWithoutMeasurementUpdate(AdoDataConnection database, Phasor phasor) { return SaveAndReorder(database, phasor, phasor.SourceIndex, true); }
/// <summary> /// Loads <see cref="Phasor"/> 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 phasors to be loaded from the database</param> /// <returns>Collection of <see cref="Phasor"/>.</returns> public static ObservableCollection<Phasor> Load(AdoDataConnection database, IList<int> keys) { bool createdConnection = false; try { createdConnection = CreateConnection(ref database); string query; string commaSeparatedKeys; Phasor[] phasorList = null; DataTable phasorTable; int id; if ((object)keys != null && keys.Count > 0) { commaSeparatedKeys = keys.Select(key => "" + key.ToString() + "").Aggregate((str1, str2) => str1 + "," + str2); query = string.Format("SELECT ID, DeviceID, Label, Type, Phase, DestinationPhasorID, SourceIndex, CreatedBy, CreatedOn, UpdatedBy, UpdatedOn FROM Phasor WHERE ID IN ({0})", commaSeparatedKeys); phasorTable = database.Connection.RetrieveData(database.AdapterType, query, DefaultTimeout); phasorList = new Phasor[phasorTable.Rows.Count]; foreach (DataRow row in phasorTable.Rows) { id = row.ConvertField<int>("ID"); phasorList[keys.IndexOf(id)] = new Phasor() { ID = id, DeviceID = row.ConvertField<int>("DeviceID"), Label = row.Field<string>("Label"), Type = row.Field<string>("Type"), Phase = row.Field<string>("Phase"), SourceIndex = row.ConvertField<int>("SourceIndex") }; } } return new ObservableCollection<Phasor>(phasorList ?? new Phasor[0]); } 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(); } } }
/// <summary> /// Saves <see cref="Phasor"/> information to database and skips associated measurement update. /// </summary> /// <param name="database"><see cref="AdoDataConnection"/> to connection to database.</param> /// <param name="phasor">Information about <see cref="Phasor"/>.</param> /// <returns>String, for display use, indicating success.</returns> public static string SaveWithoutMeasurementUpdate(AdoDataConnection database, Phasor phasor) { return(SaveAndReorder(database, phasor, phasor.SourceIndex, true)); }
/// <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> /// <returns>String, for display use, indicating success.</returns> public static string Save(AdoDataConnection database, Phasor phasor) { return(SaveAndReorder(database, phasor, phasor.SourceIndex)); }
/// <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="skipMeasurementUpdate">Skips associated measurement update if this is already being handled.</param> /// <returns>String, for display use, indicating success.</returns> public static string Save(AdoDataConnection database, Phasor phasor, bool skipMeasurementUpdate = false) { return(SaveAndReorder(database, phasor, phasor.SourceIndex, skipMeasurementUpdate)); }