public void ParsesRequestObject()
{
var request = new TimeSeriesAnalyticsRequest()
{
Window = Windows.Minutes,
WindowSize = 1,
};
var serializerSettings = new JsonSerializerSettings();
serializerSettings.ContractResolver = new CamelCasePropertyNamesContractResolver();
request.Fields.Add(new TimeSeriesAnalyticsRequestField()
{
Name = "int1", Operation = Operations.Average
});
var json = JsonConvert.SerializeObject(request, serializerSettings);
Console.WriteLine(json);
}
public async Task DataStream_Postgres_Stream_Request_Test()
{
var stream = GetValidStream();
var connector = new PostgresqlConnector(_logger);
AssertSuccessful(await connector.InitAsync(stream));
var deviceId = "DEV001";
var recordCount = 20;
for (var idx = 0; idx < 20; ++idx)
{
var dateStamp = DateTime.Now.AddSeconds((idx - recordCount) * 20);
AssertSuccessful(await AddRecord(connector, stream, deviceId, idx + 300, idx + 200, dateStamp.ToJSONString()));
}
var request = new TimeSeriesAnalyticsRequest()
{
Window = Windows.Minutes,
WindowSize = 1,
};
request.Fields.Add(new TimeSeriesAnalyticsRequestField()
{
Name = "int1", Operation = Operations.Average
});
var results = await connector.GetTimeSeriesAnalyticsAsync(request, new Core.Models.UIMetaData.ListRequest()
{
PageSize = 50
});
foreach (var result in results.Model)
{
Console.WriteLine(result);
}
}
public Task <ListResponse <DataStreamResult> > GetTimeSeriesAnalyticsAsync(TimeSeriesAnalyticsRequest request, ListRequest listRequest)
{
throw new NotImplementedException("AWS S3 does not support stream");
}
public async Task <ListResponse <DataStreamResult> > GetDataAsync(string datastreamid, [FromBody] TimeSeriesAnalyticsRequest request)
{
var dataStream = await _dataStreamManager.LoadFullDataStreamConfigurationAsync(datastreamid, OrgEntityHeader, UserEntityHeader);
var connectorResult = DataStreamServices.GetConnector(dataStream.Result.StreamType.Value, _adminlogger);
var connector = connectorResult.Result;
if (request.ListRequest == null)
{
request.ListRequest = new ListRequest()
{
PageSize = 50,
PageIndex = 1
};
}
return(await connector.GetTimeSeriesAnalyticsAsync(request, request.ListRequest));
}
public async Task <ListResponse <DataStreamResult> > GetTimeSeriesAnalyticsAsync(TimeSeriesAnalyticsRequest request, ListRequest listRequest)
{
var fields = new List <string>();
var windowType = "";
switch (request.Window)
{
case Windows.Seconds: windowType = "seconds"; break;
case Windows.Minutes: windowType = "minutes"; break;
case Windows.Hours: windowType = "hours"; break;
case Windows.Days: windowType = "days"; break;
case Windows.Months: windowType = "months"; break;
case Windows.Years: windowType = "years"; break;
}
var sql = new StringBuilder($"select time_bucket('{request.WindowSize} {windowType}', {_stream.TimestampFieldName}) as period");
fields.Add("period");
sql.Append($", {_stream.DeviceIdFieldName}");
fields.Add(_stream.DeviceIdFieldName);
foreach (var method in request.Fields)
{
var operationType = "";
switch (method.Operation)
{
case Operations.Average: operationType = "avg"; break;
case Operations.Minimum: operationType = "min"; break;
case Operations.Maximum: operationType = "max"; break;
case Operations.Count: operationType = "count"; break;
case Operations.Sum: operationType = "sum"; break;
case Operations.Interpolate: operationType = "interpolate"; break;
}
if (String.IsNullOrEmpty(operationType))
{
sql.Append($", {method.Name}");
fields.Add(method.Name);
}
else
{
var fieldName = $"{operationType}_{method.Name}";
sql.Append($", {operationType}({method.Name}) as {fieldName}");
fields.Add(fieldName);
}
}
sql.AppendLine();
sql.AppendLine($" from {_stream.DbSchema}.{_stream.DbTableName}");
sql.AppendLine($" where 1 = 1"); /* just used to establish a where clause we can use by appending "and x = y" */
foreach (var filterItem in request.Filter)
{
sql.AppendLine($" and {filterItem.Key} = @parm{filterItem.Key}");
}
sql.AppendLine($" group by period, {_stream.DeviceIdFieldName}");
sql.AppendLine($" order by period desc");
sql.AppendLine($" LIMIT {listRequest.PageSize} OFFSET {listRequest.PageSize * Math.Max(listRequest.PageIndex - 1, 0)} ");
var responseItems = new List <DataStreamResult>();
using (var cn = OpenConnection(_stream.DbName))
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = cn;
cmd.CommandText = sql.ToString();
Console.WriteLine(cmd.CommandText);
if (!String.IsNullOrEmpty(listRequest.NextRowKey))
{
cmd.Parameters.AddWithValue($"@lastDateStamp", listRequest.NextRowKey.ToDateTime());
}
if (!String.IsNullOrEmpty(listRequest.StartDate))
{
cmd.Parameters.AddWithValue($"@startDateStamp", listRequest.StartDate.ToDateTime());
}
if (!String.IsNullOrEmpty(listRequest.EndDate))
{
cmd.Parameters.AddWithValue($"@endDateStamp", listRequest.EndDate.ToDateTime());
}
foreach (var filterItem in request.Filter)
{
cmd.Parameters.AddWithValue($"@parm{filterItem.Key}", filterItem.Value);
}
cmd.CommandType = System.Data.CommandType.Text;
using (var rdr = await cmd.ExecuteReaderAsync())
{
while (rdr.Read())
{
var resultItem = new DataStreamResult();
for (var idx = 0; idx < rdr.FieldCount; ++idx)
{
resultItem.Add(fields[idx], rdr[idx]);
}
responseItems.Add(resultItem);
}
}
}
var response = new Core.Models.UIMetaData.ListResponse <DataStreamResult>();
response.Model = responseItems;
response.PageSize = responseItems.Count;
response.PageIndex = listRequest.PageIndex;
response.HasMoreRecords = responseItems.Count == listRequest.PageSize && listRequest.PageSize > 0;
if (response.HasMoreRecords)
{
response.NextRowKey = responseItems.Last().Timestamp;
}
return(response);
}
