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