public async Task <ListResponse <DataStreamResult> > GetTimeSeriesAnalyticsAsync(string query, Dictionary <string, object> filter, ListRequest request)
{
var sql = new StringBuilder(query);
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" */
if (!String.IsNullOrEmpty(request.NextRowKey))
{
sql.AppendLine($" and {_stream.TimestampFieldName} < @lastDateStamp");
}
if (!String.IsNullOrEmpty(request.StartDate))
{
sql.AppendLine($" and {_stream.TimestampFieldName} >= @startDateStamp");
}
if (!String.IsNullOrEmpty(request.EndDate))
{
sql.AppendLine($" and {_stream.TimestampFieldName} <= @endDateStamp");
}
foreach (var filterItem in filter)
{
sql.AppendLine($" and {filterItem.Key} = @parm{filterItem.Key}");
}
sql.AppendLine($" group by period");
sql.AppendLine($" order by period desc");
sql.AppendLine($" LIMIT {request.PageSize} OFFSET {request.PageSize * Math.Max(request.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(request.NextRowKey))
{
cmd.Parameters.AddWithValue($"@lastDateStamp", request.NextRowKey.ToDateTime());
}
if (!String.IsNullOrEmpty(request.StartDate))
{
cmd.Parameters.AddWithValue($"@startDateStamp", request.StartDate.ToDateTime());
}
if (!String.IsNullOrEmpty(request.EndDate))
{
cmd.Parameters.AddWithValue($"@endDateStamp", request.EndDate.ToDateTime());
}
foreach (var filterItem in filter)
{
cmd.Parameters.AddWithValue($"@parm{filterItem.Key}", filterItem.Value);
_logger.AddCustomEvent(LogLevel.Message, "ProcessStreamAnalyticsAsync", $"{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($"col{idx}", rdr[idx]);
}
responseItems.Add(resultItem);
}
}
}
var response = new Core.Models.UIMetaData.ListResponse <DataStreamResult>();
response.Model = responseItems;
response.PageSize = responseItems.Count;
response.PageIndex = request.PageIndex;
response.HasMoreRecords = responseItems.Count == request.PageSize && request.PageSize > 0;
if (response.HasMoreRecords)
{
response.NextRowKey = responseItems.Last().Timestamp;
}
return(response);
}
public async Task <ListResponse <DataStreamResult> > GetItemsAsync(Dictionary <string, object> filter, ListRequest request)
{
var sql = new StringBuilder("select ");
if (request.PageSize == 0)
{
request.PageSize = 50;
}
sql.Append($"{_stream.TimestampFieldName}");
sql.Append($", {_stream.DeviceIdFieldName}");
foreach (var fld in _stream.Fields)
{
switch (fld.FieldType.Value)
{
case DeviceAdmin.Models.ParameterTypes.GeoLocation:
sql.Append($", ST_AsText({fld.FieldName}) out_{fld.FieldName}");
break;
default:
sql.Append($", {fld.FieldName}");
break;
}
}
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" */
_logger.AddCustomEvent(LogLevel.Message, "GetItemsAsync", sql.ToString());
var responseItems = new List <DataStreamResult>();
using (var cn = OpenConnection(_stream.DbName))
using (var cmd = new NpgsqlCommand())
{
Console.WriteLine(cmd.CommandText);
if (!String.IsNullOrEmpty(request.NextRowKey))
{
sql.AppendLine($" and {_stream.TimestampFieldName} < @lastDateStamp");
cmd.Parameters.AddWithValue($"@lastDateStamp", request.NextRowKey.ToDateTime());
}
if (!String.IsNullOrEmpty(request.StartDate))
{
sql.AppendLine($" and {_stream.TimestampFieldName} >= @startDateStamp");
cmd.Parameters.AddWithValue($"@startDateStamp", request.StartDate.ToDateTime());
}
if (!String.IsNullOrEmpty(request.EndDate))
{
sql.AppendLine($" and {_stream.TimestampFieldName} <= @endDateStamp");
cmd.Parameters.AddWithValue($"@endDateStamp", request.EndDate.ToDateTime());
}
if (!String.IsNullOrEmpty(request.GroupBy))
{
sql.AppendLine($" and period = @groupBy");
cmd.Parameters.AddWithValue($"@groupBy", request.GroupBy);
}
foreach (var filterItem in filter)
{
sql.AppendLine($" and {filterItem.Key} = @parm{filterItem.Key}");
cmd.Parameters.AddWithValue($"@parm{filterItem.Key}", filterItem.Value);
_logger.AddCustomEvent(LogLevel.Message, "GetItemsAsync", $"{filterItem.Key} - {filterItem.Value}");
}
sql.AppendLine($" order by {_stream.TimestampFieldName} desc");
sql.AppendLine($" LIMIT {request.PageSize} OFFSET {request.PageSize * Math.Max(request.PageIndex - 1, 0)} ");
cmd.Connection = cn;
cmd.CommandText = sql.ToString();
cmd.CommandType = System.Data.CommandType.Text;
using (var rdr = await cmd.ExecuteReaderAsync())
{
while (rdr.Read())
{
var resultItem = new DataStreamResult();
var timeStamp = Convert.ToDateTime(rdr[_stream.TimestampFieldName]);
timeStamp = DateTime.SpecifyKind(timeStamp, DateTimeKind.Utc);
resultItem.Timestamp = timeStamp.ToJSONString();
resultItem.Add(_stream.TimestampFieldName, resultItem.Timestamp);
resultItem.Add(_stream.DeviceIdFieldName, rdr[_stream.DeviceIdFieldName]);
foreach (var fld in _stream.Fields)
{
switch (fld.FieldType.Value)
{
case DeviceAdmin.Models.ParameterTypes.GeoLocation:
var result = rdr[$"out_{fld.FieldName}"] as String;
if (!String.IsNullOrEmpty(result))
{
var reg = new Regex(@"^POINT\((?'lat'[\d\.\-]{2,14}) (?'lon'[\d\.\-]{2,14})\)$");
var regMatch = reg.Match(result);
if (regMatch.Success && regMatch.Groups.Count == 3)
{
var strLat = regMatch.Groups[1];
var strLon = regMatch.Groups[2];
if (double.TryParse(strLat.Value, out double lat) &&
double.TryParse(strLat.Value, out double lon))
{
resultItem.Add(fld.FieldName, $"{lat:0.0000000},{lon:0.0000000}");
}
}
}
if (!resultItem.Keys.Contains(fld.FieldName))
{
resultItem.Add(fld.FieldName, null);
}
break;
case DeviceAdmin.Models.ParameterTypes.DateTime:
{
var dtValue = rdr[fld.FieldName] as DateTime?;
if (dtValue.HasValue)
{
resultItem.Add(fld.FieldName, dtValue.Value.ToJSONString());
}
}
break;
default:
resultItem.Add(fld.FieldName, rdr[fld.FieldName]);
break;
}
}
responseItems.Add(resultItem);
}
}
}
var response = new Core.Models.UIMetaData.ListResponse <DataStreamResult>();
response.Model = responseItems;
response.PageSize = responseItems.Count;
response.PageIndex = request.PageIndex;
response.HasMoreRecords = responseItems.Count == request.PageSize;
if (response.HasMoreRecords)
{
response.NextRowKey = responseItems.Last().Timestamp;
}
return(response);
}
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);
}
public async Task <LagoVista.Core.Models.UIMetaData.ListResponse <DataStreamResult> > GetItemsAsync(string deviceId, LagoVista.Core.Models.UIMetaData.ListRequest request)
{
var sql = new StringBuilder("select ");
if (request.PageSize == 0)
{
request.PageSize = 50;
}
sql.Append($"[{_stream.TimeStampFieldName}]");
foreach (var fld in _stream.Fields)
{
sql.Append($", [{fld.FieldName}]");
}
sql.AppendLine();
sql.AppendLine($" from [{_stream.DBTableName}]");
sql.AppendLine($" where [{_stream.DeviceIdFieldName}] = @deviceId");
if (!String.IsNullOrEmpty(request.NextRowKey))
{
sql.AppendLine($" and {_stream.TimeStampFieldName} < @lastDateStamp");
}
if (!String.IsNullOrEmpty(request.StartDate))
{
sql.AppendLine($" and {_stream.TimeStampFieldName} >= @startDateStamp");
}
if (!String.IsNullOrEmpty(request.EndDate))
{
sql.AppendLine($" and {_stream.TimeStampFieldName} <= @endDateStamp");
}
sql.AppendLine($" order by [{_stream.TimeStampFieldName}] desc");
sql.AppendLine(" OFFSET @PageSize * @PageIndex ROWS");
sql.AppendLine(" FETCH NEXT @PageSize ROWS ONLY ");
Console.WriteLine(sql.ToString());
var responseItems = new List <DataStreamResult>();
using (var cn = new System.Data.SqlClient.SqlConnection(_connectionString))
using (var cmd = new System.Data.SqlClient.SqlCommand(sql.ToString(), cn))
{
cmd.Parameters.AddWithValue("@deviceId", deviceId);
cmd.Parameters.AddWithValue("@PageSize", request.PageSize);
cmd.Parameters.AddWithValue("@PageIndex", request.PageIndex);
if (!String.IsNullOrEmpty(request.NextRowKey))
{
cmd.Parameters.AddWithValue($"@lastDateStamp", request.NextRowKey.ToDateTime());
}
if (!String.IsNullOrEmpty(request.StartDate))
{
cmd.Parameters.AddWithValue($"@startDateStamp", request.StartDate.ToDateTime());
}
if (!String.IsNullOrEmpty(request.EndDate))
{
cmd.Parameters.AddWithValue($"@endDateStamp", request.EndDate.ToDateTime());
}
cmd.CommandType = System.Data.CommandType.Text;
await cmd.Connection.OpenAsync();
using (var rdr = await cmd.ExecuteReaderAsync())
{
while (rdr.Read())
{
var resultItem = new DataStreamResult();
resultItem.Timestamp = Convert.ToDateTime(rdr[_stream.TimeStampFieldName]).ToJSONString();
foreach (var fld in _stream.Fields)
{
resultItem.Fields.Add(fld.FieldName, rdr[fld.FieldName]);
}
responseItems.Add(resultItem);
}
}
}
var response = new Core.Models.UIMetaData.ListResponse <DataStreamResult>();
response.Model = responseItems;
response.PageSize = responseItems.Count;
response.PageIndex = request.PageIndex;
response.HasMoreRecords = responseItems.Count == request.PageSize;
if (response.HasMoreRecords)
{
response.NextRowKey = responseItems.Last().Timestamp;
}
return(response);
}
