public async Task <List <DynoCard> > GetPreviousCards(DynoCardAnomalyResult anomalyCard)
{
DateTime start = anomalyCard.Timestamp.Subtract(TimeSpan.FromMinutes(30));
DateTime end = anomalyCard.Timestamp;
int startEpoch = (int)(start.ToUniversalTime().Subtract(new DateTime(1970, 1, 1))).TotalSeconds;
int endEpoch = (int)(end.ToUniversalTime().Subtract(new DateTime(1970, 1, 1))).TotalSeconds;
System.Console.WriteLine($"Start: {startEpoch}");
System.Console.WriteLine($"End: {endEpoch}");
var sql = new StringBuilder()
.Append("WITH cteEndCardID (cardID) ")
.Append("AS ( ")
.Append("SELECT TOP 1 h.DC_ID ")
.Append("FROM ACTIVE.CARD_HEADER h ")
.Append($"WHERE h.CH_EPOC_DATE >= {endEpoch} ")
.Append("ORDER BY h.CH_EPOC_DATE ), ")
.Append("cteStartCardID(cardID) ")
.Append("AS ( ")
.Append("SELECT TOP 1 h.DC_ID ")
.Append("FROM ACTIVE.CARD_HEADER h ")
.Append($"WHERE h.CH_EPOC_DATE <= {startEpoch} ")
.Append("OR h.CH_EPOC_DATE = (SELECT MIN(CH_EPOC_DATE) FROM ACTIVE.CARD_HEADER) ")
.Append("ORDER BY h.DC_ID DESC) ")
.Append("SELECT h.CH_CARD_TYPE, ")
.Append("dc.DC_ID, ")
.Append("h.CH_ID, ")
.Append("h.CH_SCALED_MAX_LOAD, ")
.Append("h.CH_SCALED_MIN_LOAD, ")
.Append("h.CH_STROKE_LENGTH, ")
.Append("h.CH_STROKE_PERIOD, ")
.Append("h.CH_GROSS_STROKE, ")
.Append("h.CH_NET_STROKE, ")
.Append("h.CH_PUMP_FILLAGE, ")
.Append("h.CH_FLUID_LOAD, ")
.Append("d.CD_ID, ")
.Append("d.CD_POSITION, ")
.Append("d.CD_LOAD, ")
.Append("h.CH_EPOC_DATE, ")
.Append("dc.PU_ID, ")
.Append("h.CH_NUMBER_OF_POINTS ")
.Append("FROM [ACTIVE].[DYNO_CARD] dc ")
.Append("JOIN [ACTIVE].[CARD_HEADER] h ON dc.DC_ID = h.DC_ID ")
.Append("JOIN [ACTIVE].[CARD_DETAIL] d ON h.CH_ID = d.CH_ID ")
.Append("JOIN cteStartCardID sc ON h.DC_ID >= sc.cardID ")
.Append("JOIN cteEndCardID ec ON h.DC_ID <= ec.cardID ")
.Append("ORDER BY h.CH_ID DESC");
Dictionary <int, DynoCard> cardList = new Dictionary <int, DynoCard>();
// //Store the data in SQL db
using (Sql.SqlConnection conn = new Sql.SqlConnection(ConnectionString))
{
conn.Open();
using (Sql.SqlCommand cardHistorySelect = new Sql.SqlCommand(sql.ToString(), conn))
{
var results = await cardHistorySelect.ExecuteReaderAsync();
if (results.HasRows)
{
PumpCard pumpCard = null;
SurfaceCard surfaceCard = null;
int previousCardID = 0;
DateTime epoch = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);
while (await results.ReadAsync())
{
string cardTypeValue = results.GetString(0);
int dynoCardID = results.GetInt32(1);
CardType cardType = "P".Equals(cardTypeValue) ? CardType.Pump : CardType.Surface;
if (previousCardID != dynoCardID)
{
previousCardID = dynoCardID;
pumpCard = null;
surfaceCard = null;
}
if (cardType == CardType.Surface)
{
if (surfaceCard == null)
{
surfaceCard = new SurfaceCard()
{
Id = results.GetInt32(2),
Timestamp = epoch.AddSeconds(results.GetInt32(14)),
ScaledMaxLoad = (int)results.GetFloat(3),
ScaledMinLoad = (int)results.GetFloat(4),
StrokeLength = (int)results.GetFloat(5),
StrokePeriod = (int)results.GetFloat(6),
NumberOfPoints = results.GetInt32(16),
CardType = cardType,
CardCoordinates = new List <CardCoordinate>()
};
surfaceCard.CardCoordinates.Add(new CardCoordinate()
{
Order = results.GetInt32(11),
Position = (int)results.GetFloat(12),
Load = (int)results.GetFloat(13)
});
DynoCard dynoCard = null;
if (cardList.ContainsKey(dynoCardID))
{
dynoCard = cardList[dynoCardID];
}
else
{
dynoCard = new DynoCard();
dynoCard.Id = dynoCardID;
dynoCard.Timestamp = surfaceCard.Timestamp;
}
dynoCard.SurfaceCard = surfaceCard;
cardList[dynoCardID] = dynoCard;
}
else
{
cardList[dynoCardID].SurfaceCard.CardCoordinates.Add(new CardCoordinate()
{
Position = (int)results.GetFloat(12),
Load = (int)results.GetFloat(13)
});
}
}
else if (cardType == CardType.Pump)
{
if (pumpCard == null)
{
pumpCard = new PumpCard()
{
Id = results.GetInt32(2),
Timestamp = epoch.AddSeconds(results.GetInt32(14)),
ScaledMaxLoad = (int)results.GetFloat(3),
ScaledMinLoad = (int)results.GetFloat(4),
GrossStroke = (int)results.GetFloat(7),
NetStroke = (int)results.GetFloat(8),
PumpFillage = (int)results.GetFloat(9),
FluidLoad = (int)results.GetFloat(10),
NumberOfPoints = results.GetInt32(16),
CardType = cardType,
CardCoordinates = new List <CardCoordinate>()
};
pumpCard.CardCoordinates.Add(new CardCoordinate()
{
Order = results.GetInt32(11),
Position = (int)results.GetFloat(12),
Load = (int)results.GetFloat(13)
});
DynoCard dynoCard = null;
if (cardList.ContainsKey(dynoCardID))
{
dynoCard = cardList[dynoCardID];
}
else
{
dynoCard = new DynoCard();
dynoCard.Id = dynoCardID;
dynoCard.Timestamp = pumpCard.Timestamp;
}
dynoCard.PumpCard = pumpCard;
cardList[dynoCardID] = dynoCard;
}
else
{
cardList[dynoCardID].PumpCard.CardCoordinates.Add(new CardCoordinate()
{
Position = (int)results.GetFloat(12),
Load = (int)results.GetFloat(13)
});
}
}
}
}
}
}
var cards = cardList?.Values?.OrderBy(c => c.Timestamp).ToList();
if (cards != null && cards.Count > 0)
{
cards.Last().TriggeredEvents = true;
}
return(await Task.FromResult(cards));
}
private static async Task <MessageResponse> GeneratedTelemetryInput(Message message, object userContext)
{
Console.WriteLine("Processing generated telemetry input");
var counterValue = Interlocked.Increment(ref counter);
try
{
ModuleClient deviceClient = (ModuleClient)userContext;
var messageBytes = message.GetBytes();
var messageString = Encoding.UTF8.GetString(messageBytes);
if (!string.IsNullOrEmpty(messageString))
{
DynoCard card = JsonConvert.DeserializeObject <DynoCard>(messageString);
int cardID = await(new DataHelper()).PersistDynoCard(card);
if (cardID > 0)
{
card.Id = cardID;
var dynoCardMessage = card.ToDeviceMessage();
await deviceClient.SendEventAsync("output1", dynoCardMessage);
}
// Simulating ML alerts - generate a random number 10 percent of the time
int random = (new Random()).Next(1, 10);
System.Console.WriteLine( );
if (random == 1)
{
var fakeAnomaly = new DynoCardAnomalyResult()
{
Id = cardID.ToString(),
Timestamp = card.Timestamp,
Anomaly = "true"
};
var anomalydBytes = JsonConvert.SerializeObject(fakeAnomaly);
var anomalyByteString = Encoding.UTF8.GetBytes(anomalydBytes);
var anomalydMessage = new Message(anomalyByteString);
await deviceClient.SendEventAsync("telemetryOutput", anomalydMessage);
}
}
// Indicate that the message treatment is completed
return(MessageResponse.Completed);
}
catch (AggregateException ex)
{
foreach (Exception exception in ex.InnerExceptions)
{
Console.WriteLine();
Console.WriteLine("Error in generated telemetry input: {0}", exception);
}
return(MessageResponse.Completed);
}
catch (Exception ex)
{
Console.WriteLine();
Console.WriteLine("Error in generated telemetry input: {0}", ex.Message);
return(MessageResponse.Completed);
}
}
