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));
}
}
