/// <summary>
/// Gets the available timestamps.
/// </summary>
public override List <DateTime> GetTimestamps(TimeRange timeRange)
{
try
{
stopwatch.Restart();
conn.Open();
List <DateTime> timestamps = new List <DateTime>();
string endOper = timeRange.EndInclusive ? "<=" : "<";
string sql =
$"SELECT DISTINCT time_stamp FROM {queryBuilder.HistoricalTable} " +
$"WHERE @startTime <= time_stamp AND time_stamp {endOper} @endTime " +
$"ORDER BY time_stamp";
NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);
cmd.Parameters.Add("startTime", NpgsqlDbType.TimestampTz).Value = timeRange.StartTime;
cmd.Parameters.Add("endTime", NpgsqlDbType.TimestampTz).Value = timeRange.EndTime;
NpgsqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
timestamps.Add(reader.GetDateTimeUtc(0));
}
stopwatch.Stop();
arcLog?.WriteAction(ServerPhrases.ReadingTimestampsCompleted,
timestamps.Count, stopwatch.ElapsedMilliseconds);
return(timestamps);
}
finally
{
conn.Close();
}
}
/// <summary>
/// Gets the trend of the specified channel.
/// </summary>
public override Trend GetTrend(TimeRange timeRange, int cnlNum)
{
try
{
stopwatch.Restart();
conn.Open();
Trend trend = new Trend(cnlNum, 0);
NpgsqlCommand cmd = CreateTrendCommand(timeRange, cnlNum);
NpgsqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
trend.Points.Add(new TrendPoint(
reader.GetDateTimeUtc(0),
reader.GetDouble(1),
reader.GetInt32(2)));
}
stopwatch.Stop();
arcLog?.WriteAction(ServerPhrases.ReadingTrendCompleted,
trend.Points.Count, stopwatch.ElapsedMilliseconds);
return(trend);
}
finally
{
conn.Close();
}
}
/// <summary>
/// Gets a trend bundle containing the trend of the first channel.
/// </summary>
private TrendBundle GetFirstTrend(TimeRange timeRange, int[] cnlNums)
{
try
{
stopwatch.Restart();
conn.Open();
TrendBundle trendBundle = new TrendBundle(cnlNums, 0);
TrendBundle.CnlDataList trend = trendBundle.Trends[0];
NpgsqlCommand cmd = CreateTrendCommand(timeRange, cnlNums[0]);
NpgsqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
trendBundle.Timestamps.Add(reader.GetDateTimeUtc(0));
trend.Add(new CnlData(
reader.GetDouble(1),
reader.GetInt32(2)));
}
stopwatch.Stop();
arcLog?.WriteAction(ServerPhrases.ReadingTrendCompleted,
trendBundle.Timestamps.Count, stopwatch.ElapsedMilliseconds);
return(trendBundle);
}
finally
{
conn.Close();
}
}
/// <summary>
/// Reads an event.
/// </summary>
private Event ReadEvent(NpgsqlDataReader reader)
{
return(new Event
{
EventID = reader.GetInt64(0),
Timestamp = reader.GetDateTimeUtc(1),
Hidden = reader.GetBoolean(2),
CnlNum = reader.GetInt32(3),
ObjNum = reader.GetInt32(4),
DeviceNum = reader.GetInt32(5),
PrevCnlVal = reader.GetDouble(6),
PrevCnlStat = reader.GetInt32(7),
CnlVal = reader.GetDouble(8),
CnlStat = reader.GetInt32(9),
Severity = reader.GetInt32(10),
AckRequired = reader.GetBoolean(11),
Ack = reader.GetBoolean(12),
AckTimestamp = reader.GetDateTimeUtc(13),
AckUserID = reader.GetInt32(14),
TextFormat = (EventTextFormat)reader.GetInt32(15),
Text = reader.IsDBNull(16) ? "" : reader.GetString(16),
Data = reader.IsDBNull(17) ? null : (byte[])reader[17]
});
}
/// <summary>
/// Reads the current data.
/// </summary>
public override void ReadData(ICurrentData curData, out bool completed)
{
NpgsqlTransaction trans = null;
try
{
stopwatch.Restart();
conn.Open();
trans = conn.BeginTransaction();
string sql = "SELECT cnl_num, time_stamp, val, stat FROM " + queryBuilder.CurrentTable;
NpgsqlCommand cmd = new NpgsqlCommand(sql, conn, trans);
List <int> cnlsToDelete = new List <int>();
int pointCnt = 0;
using (NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int cnlNum = reader.GetInt32(0);
int cnlIndex = curData.GetCnlIndex(cnlNum);
if (cnlIndex >= 0)
{
curData.Timestamps[cnlIndex] = reader.GetDateTimeUtc(1);
curData.CnlData[cnlIndex] = new CnlData
{
Val = reader.GetDouble(2),
Stat = reader.GetInt32(3)
};
pointCnt++;
}
else
{
cnlsToDelete.Add(cnlNum);
}
}
}
// delete data of unused channels
if (cnlsToDelete.Count > 0)
{
sql = $"DELETE FROM {queryBuilder.CurrentTable} WHERE cnl_num = @cnlNum";
cmd = new NpgsqlCommand(sql, conn, trans);
NpgsqlParameter cnlNumParam = cmd.Parameters.Add("cnlNum", NpgsqlDbType.Integer);
foreach (int cnlNum in cnlsToDelete)
{
cnlNumParam.Value = cnlNum;
cmd.ExecuteNonQuery();
}
}
trans.Commit();
completed = true;
hasError = false;
stopwatch.Stop();
arcLog?.WriteAction(ServerPhrases.ReadingPointsCompleted, pointCnt, stopwatch.ElapsedMilliseconds);
}
catch
{
trans?.Rollback();
completed = false;
hasError = true;
}
finally
{
conn.Close();
}
}
