public IHttpActionResult PatchArray([FromBody] IEnumerable <ApplicationRoleUserAccount> records) { try { if (PatchRoles == string.Empty || User.IsInRole(PatchRoles)) { using (AdoDataConnection connection = new AdoDataConnection(Connection)) { IEnumerable <ApplicationRoleUserAccount> applicationRoles = new TableOperations <ApplicationRoleUserAccount>(connection).QueryRecordsWhere("UserAccountID = {0}", records.First().UserAccountID); foreach (ApplicationRoleUserAccount applicationRole in applicationRoles) { if (records.FirstOrDefault(r => r.ApplicationRoleID == applicationRole.ApplicationRoleID) == null) { new TableOperations <ApplicationRoleUserAccount>(connection).DeleteRecord(applicationRole); } } foreach (ApplicationRoleUserAccount record in records) { if (applicationRoles.FirstOrDefault(r => r.ApplicationRoleID == record.ApplicationRoleID) == null) { new TableOperations <ApplicationRoleUserAccount>(connection).AddNewRecord(record); } } return(Ok("Updated Roles without error.")); } } else { return(Unauthorized()); } } catch (Exception ex) { return(InternalServerError(ex)); } }
public IHttpActionResult Post(DetailtsForSitesForm form) { try { using (AdoDataConnection XDAconnection = new AdoDataConnection("dbOpenXDA")) using (AdoDataConnection connection = new AdoDataConnection("systemSettings")) { DateTime date = DateTime.Parse(form.targetDate).ToUniversalTime(); DataTable table = XDAconnection.RetrieveData(Query, date, form.siteId, form.context, form.colorScale); IEnumerable <ValueList> chartSettings = new TableOperations <ValueList>(connection).QueryRecordsWhere("GroupID = ( SELECT ID FROM ValueListGroup WHERE Name = 'Chart." + Tab + "')"); int groupID = connection.ExecuteScalar <int>($"SELECT ID FROM ValueListGroup WHERE Name = 'Chart.{Tab}'"); List <string> skipColumns; if (Tab == "Events" || Tab == "Disturbances") { skipColumns = new List <string>() { "EventID", "MeterID", "Site" } } ; else { skipColumns = table.Columns.Cast <DataColumn>().Select(x => x.ColumnName).ToList(); } List <string> columnsToRemove = new List <string>(); foreach (DataColumn column in table.Columns) { if (skipColumns.Contains(column.ColumnName)) { continue; } //if (!chartSettings.Any(x => x.Text == column.ColumnName)) //{ // Random r = new Random(DateTime.UtcNow.Millisecond); // new TableOperations<ValueList>(connection).AddNewRecord(new ValueList // { // Key = 0, // GroupID = groupID, // Text = column.ColumnName, // Flag = false, // AltText1 = "#" + r.Next(256).ToString("X2") + r.Next(256).ToString("X2") + r.Next(256).ToString("X2"), // IsDefault = false, // Hidden = false, // Enabled = true // }); // chartSettings = new TableOperations<ValueList>(connection).QueryRecordsWhere("GroupID = ( SELECT ID FROM ValueListGroup WHERE Name = 'Chart." + Tab + "')"); //} ValueList setting = chartSettings.FirstOrDefault(x => x.Text == column.ColumnName); if (setting != null && setting.Enabled == false) { columnsToRemove.Add(column.ColumnName); } } foreach (string columnName in columnsToRemove) { table.Columns.Remove(columnName); } return(Ok(table)); } } catch (Exception ex) { return(InternalServerError(ex)); } }
public IHttpActionResult UpdateMeterChannels([FromBody] JObject postData, int meterID, string filter) { if (PatchRoles == string.Empty || User.IsInRole(PatchRoles)) { try { using (AdoDataConnection connection = new AdoDataConnection(Connection)) { TableOperations <Channel> channelTable = new TableOperations <Channel>(connection); TableOperations <Series> seriesTable = new TableOperations <Series>(connection); JToken Channels = postData["Channels"]; IEnumerable <MeasurementType> measurementTypes = new TableOperations <MeasurementType>(connection).QueryRecords(); IEnumerable <MeasurementCharacteristic> measurementCharacteristics = new TableOperations <MeasurementCharacteristic>(connection).QueryRecords(); IEnumerable <Phase> phases = new TableOperations <Phase>(connection).QueryRecords(); IEnumerable <Asset> assets = new TableOperations <Asset>(connection).QueryRecordsWhere("ID IN (SELECT AssetID FROM MeterAsset WHERE MeterID = {0})", meterID); IEnumerable <SeriesType> seriesTypes = new TableOperations <SeriesType>(connection).QueryRecords(); List <int> channelIDs = new List <int>(); foreach (JToken channelToken in Channels) { Channel channel = new Channel(); channel.ID = channelToken["ID"].ToObject <int>(); channel.MeterID = meterID; channel.AssetID = assets.FirstOrDefault(asset => asset.AssetKey == channelToken["Asset"].ToString()).ID; channel.MeasurementTypeID = measurementTypes.First(mt => mt.Name == channelToken["MeasurementType"].ToString()).ID; channel.MeasurementCharacteristicID = measurementCharacteristics.First(mc => mc.Name == channelToken["MeasurementCharacteristic"].ToString()).ID; channel.PhaseID = phases.First(phase => phase.Name == channelToken["Phase"].ToString()).ID; channel.Name = channelToken["Name"].ToString(); channel.Description = channelToken["Description"].ToString() == string.Empty ? null : channelToken["Description"].ToString(); channel.Adder = channelToken["Adder"].ToObject <double>(); channel.Multiplier = channelToken["Multiplier"].ToObject <double>(); channel.SamplesPerHour = channelToken["SamplesPerHour"].ToObject <double>(); channel.PerUnitValue = channelToken["PerUnitValue"].ToObject <double?>(); channel.HarmonicGroup = channelToken["HarmonicGroup"].ToObject <int>(); channel.Enabled = channelToken["Enabled"].ToObject <bool>(); channel.ConnectionPriority = channelToken["ConnectionPriority"].ToObject <int>(); if (channel.AssetID == 0) { continue; } channelTable.AddNewOrUpdateRecord(channel); if (channel.ID == 0) { channel.ID = connection.ExecuteScalar <int>("SELECT @@IDENTITY"); } if (channelToken["Series"] is JArray array) { foreach (JToken seriesToken in array) { Series series = new Series(); series.ID = seriesToken.Value <int>("ID"); series.ChannelID = channel.ID; series.SeriesTypeID = seriesTypes.First(st => st.Name == seriesToken.Value <string>("SeriesType")).ID; series.SourceIndexes = seriesToken.Value <string>("SourceIndexes"); seriesTable.AddNewOrUpdateRecord(series); } } channelIDs.Add(channel.ID); } const string EventChannelFilter = "MeasurementCharacteristicID = (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'Instantaneous') AND " + "(SELECT COUNT(*) FROM Series WHERE ChannelID = Channel.ID) = 1 AND " + "EXISTS (SELECT * FROM Series WHERE SeriesTypeID IN (SELECT ID FROM SeriesType WHERE Name IN ('Values', 'Instantaneous')))"; string GetTypeFilter() { switch (filter.ToLower()) { case "event": return(EventChannelFilter); case "trend": return($"NOT ({EventChannelFilter})"); case "none": return("1 IS NULL"); default: return("1=1"); } } string idFilter = channelIDs.Any() ? $"ID NOT IN ({string.Join(",", channelIDs)})" : "1=1"; string deleteFilter = $"MeterID = {meterID} AND " + $"({GetTypeFilter()}) AND " + $"{idFilter}"; connection.ExecuteNonQuery("EXEC UniversalCascadeDelete 'Channel', {0}", deleteFilter); return(Ok("Completed without errors")); } } catch (Exception ex) { return(InternalServerError(ex)); } } else { return(Unauthorized()); } }
public IHttpActionResult Post(LocationsForm form) { try { Locations meters = new Locations(); using (AdoDataConnection XDAconnection = new AdoDataConnection("dbOpenXDA")) using (AdoDataConnection connection = new AdoDataConnection("systemSettings")) { DateTime startDate; DateTime endDate; if (form.context == "day") { startDate = DateTime.Parse(form.targetDateFrom).ToUniversalTime(); endDate = startDate.AddDays(1).AddSeconds(-1); } else if (form.context == "hour") { startDate = DateTime.Parse(form.targetDateFrom).ToUniversalTime(); endDate = startDate.AddHours(1).AddSeconds(-1); } else if (form.context == "minute") { startDate = DateTime.Parse(form.targetDateFrom).ToUniversalTime(); endDate = startDate.AddMinutes(1).AddSeconds(-1); } else if (form.context == "second") { startDate = DateTime.Parse(form.targetDateFrom).ToUniversalTime(); endDate = startDate.AddSeconds(1).AddMilliseconds(-1); } else { startDate = DateTime.Parse(form.targetDateFrom).ToUniversalTime(); endDate = DateTime.Parse(form.targetDateTo).ToUniversalTime(); } DataTable table = XDAconnection.RetrieveData(Query, startDate, endDate, form.meterIds, form.context); IEnumerable <ValueList> chartSettings = new TableOperations <ValueList>(connection).QueryRecordsWhere("GroupID = ( SELECT ID FROM ValueListGroup WHERE Name = 'Chart." + Tab + "')"); int groupID = connection.ExecuteScalar <int>($"SELECT ID FROM ValueListGroup WHERE Name = 'Chart.{Tab}'"); List <string> skipColumns = new List <string>() { "ID", "Name", "Longitude", "Latitude", "Count", "ExpectedPoints", "GoodPoints", "LatchedPoints", "UnreasonablePoints", "NoncongruentPoints", "DuplicatePoints" }; List <string> columnsToRemove = new List <string>(); foreach (DataColumn column in table.Columns) { if (skipColumns.Contains(column.ColumnName)) { continue; } //if (!chartSettings.Any(x => x.Text == column.ColumnName)) //{ // Random r = new Random(DateTime.UtcNow.Millisecond); // new TableOperations<ValueList>(connection).AddNewRecord(new ValueList // { // Key = 0, // GroupID = groupID, // Text = column.ColumnName, // Flag = false, // AltText1 = "#" + r.Next(256).ToString("X2") + r.Next(256).ToString("X2") + r.Next(256).ToString("X2"), // IsDefault = false, // Hidden = false, // Enabled = true // }); // chartSettings = new TableOperations<ValueList>(connection).QueryRecordsWhere("GroupID = ( SELECT ID FROM ValueListGroup WHERE Name = 'Chart." + Tab + "')"); //} ValueList setting = chartSettings.FirstOrDefault(x => x.Text == column.ColumnName); if (setting != null && setting.Enabled == false) { columnsToRemove.Add(column.ColumnName); } } foreach (string columnName in columnsToRemove) { table.Columns.Remove(columnName); } meters.Colors = chartSettings.ToDictionary(x => x.Text, x => x.AltText1); meters.Data = table; return(Ok(meters)); } } catch (Exception ex) { return(InternalServerError(ex)); } }