/// <summary>
/// This is for the overall AVERAGES part: (MIN), (MAX), (AVG)
/// </summary>
/// <param name="domain"></param>
/// <param name="address"></param>
/// <param name="parameterName"></param>
/// <param name="aggregator"></param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <returns></returns>
public List <StatisticsEntry> GetHourlyStats(
string domain,
string address,
string parameterName,
string aggregator,
DateTime startDate, DateTime endDate
)
{
var values = new List <StatisticsEntry>();
//lock (dbLock)
{
var dbCommand = dbConnection.CreateCommand();
string filter = "";
if (!string.IsNullOrEmpty(domain) && !string.IsNullOrEmpty(address))
{
filter = " Domain=@domain AND Address=@address and ";
dbCommand.Parameters.Add(new SQLiteParameter("@domain", domain));
dbCommand.Parameters.Add(new SQLiteParameter("@address", address));
}
string query = "select TimeStart,TimeEnd,Domain,Address," + aggregator + "(AverageValue) as Value from ValuesHist where " + filter + " Parameter = @parameterName AND " + GetParameterizedDateRangeFilter(ref dbCommand, startDate, endDate) + " group by Domain, Address, strftime('%H', TimeStart) order by TimeStart asc;";
dbCommand.Parameters.Add(new SQLiteParameter("@parameterName", parameterName));
//if (domain != "" && address != "") filter = "Domain ='" + domain + "' and Address = '" + address + "' and ";
//string query = "select TimeStart,TimeEnd,Domain,Address," + aggregator + "(AverageValue) as Value from ValuesHist where " + filter + "Parameter = '" + parameterName + "' AND " + GetDateRangeFilter(startDate, endDate) + " group by Domain, Address, strftime('%H', TimeStart) order by TimeStart asc;";
dbCommand.CommandText = query;
var reader = dbCommand.ExecuteReader();
//
while (reader.Read())
{
var entry = new StatisticsEntry();
entry.TimeStart = DateTime.Parse(reader.GetString(0));
entry.TimeEnd = DateTime.Parse(reader.GetString(1));
entry.Domain = reader.GetString(2);
entry.Address = reader.GetString(3);
entry.Value = 0;
try
{
entry.Value = (double)reader.GetFloat(4);
}
catch
{
var value = reader.GetValue(4);
if (value != DBNull.Value && value != null)
{
double.TryParse(
reader.GetString(4),
out entry.Value
);
}
}
//
values.Add(entry);
}
//
reader.Close();
}
return(values);
}
public List <StatisticsEntry> GetTodayDetail(
string domain,
string address,
string parameterName,
string aggregator = "Avg"
)
{
var values = new List <StatisticsEntry>();
//lock (dbLock)
{
var dbCommand = dbConnection.CreateCommand();
string filter = "";
var start = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00.000000");
if (domain != "" && address != "")
{
filter = "Domain ='" + domain + "' and Address = '" + address + "' and ";
}
// aggregated averages by hour
string q = "select TimeStart,TimeEnd,Domain,Address," + aggregator + "(AverageValue) as Value from ValuesHist where " + filter + "Parameter = '" + parameterName + "' and TimeStart >= '" + start.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss.ffffff") + "' group by TimeStart order by TimeStart asc;";
// detailed module stats
if (domain != "" && address != "")
{
q = "select TimeStart,TimeEnd,Domain,Address,AverageValue as Value from ValuesHist where " + filter + "Parameter = '" + parameterName + "' and TimeStart >= '" + start.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss.ffffff") + "' order by TimeStart asc;";
}
dbCommand.CommandText = q;
var reader = dbCommand.ExecuteReader();
//
while (reader.Read())
{
var entry = new StatisticsEntry();
entry.TimeStart = DateTime.Parse(reader.GetString(0));
entry.TimeEnd = DateTime.Parse(reader.GetString(1));
entry.Domain = reader.GetString(2);
entry.Address = reader.GetString(3);
entry.Value = 0;
try
{
entry.Value = (double)reader.GetFloat(4);
}
catch
{
var value = reader.GetValue(4);
if (value != DBNull.Value && value != null)
{
double.TryParse(
reader.GetString(4),
out entry.Value
);
}
}
//
values.Add(entry);
}
//
reader.Close();
}
return(values);
}
/// <summary>
/// This is for the current day's AVERAGES part: (TODAY_AVG)
/// </summary>
/// <param name="domain"></param>
/// <param name="address"></param>
/// <param name="parameterName"></param>
/// <param name="aggregator"></param>
/// <returns></returns>
public List <StatisticsEntry> GetHourlyStatsToday(
string domain,
string address,
string parameterName,
string aggregator
)
{
var values = new List <StatisticsEntry>();
//lock (dbLock)
{
var dbCommand = dbConnection.CreateCommand();
string filter = "";
var start = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00.000000");
dbCommand.Parameters.Add(new SQLiteParameter("@start", start.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss.ffffff")));
//if (domain != "" && address != "") filter = "Domain ='" + domain + "' and Address = '" + address + "' and ";
if (!string.IsNullOrEmpty(domain) && !string.IsNullOrEmpty(address))
{
filter = " Domain=@domain AND Address=@address and ";
dbCommand.Parameters.Add(new SQLiteParameter("@domain", domain));
dbCommand.Parameters.Add(new SQLiteParameter("@address", address));
}
dbCommand.Parameters.Add(new SQLiteParameter("@parameterName", parameterName));
// aggregated averages by hour
string query = "select TimeStart,TimeEnd,Domain,Address," + aggregator + "(AverageValue) as Value from ValuesHist where " + filter + "Parameter = @parameterName and TimeStart >= @start group by Domain, Address, strftime('%H', TimeStart) order by TimeStart asc;";
dbCommand.CommandText = query;
SQLiteDataReader reader = dbCommand.ExecuteReader();
//
while (reader.Read())
{
var entry = new StatisticsEntry();
entry.TimeStart = DateTime.Parse(reader.GetString(0));
entry.TimeEnd = DateTime.Parse(reader.GetString(1));
entry.Domain = reader.GetString(2);
entry.Address = reader.GetString(3);
entry.Value = 0;
try
{
entry.Value = (double)reader.GetFloat(4);
}
catch
{
var value = reader.GetValue(4);
if (value != DBNull.Value && value != null)
{
double.TryParse(
reader.GetString(4),
out entry.Value
);
}
}
//
values.Add(entry);
}
//
reader.Close();
}
return(values);
}
public List <StatisticsEntry> GetHourlyCounter(
string domain,
string address,
string parameterName,
double timescaleseconds,
DateTime startDate, DateTime endDate
)
{
var values = new List <StatisticsEntry>();
//lock (dbLock)
{
var dbCommand = dbConnection.CreateCommand();
string filter = "";
if (!string.IsNullOrEmpty(domain) && !string.IsNullOrEmpty(address))
{
filter = " Domain=@domain AND Address=@address and ";
dbCommand.Parameters.Add(new SQLiteParameter("@domain", domain));
dbCommand.Parameters.Add(new SQLiteParameter("@address", address));
}
string query = "select TimeStart,TimeEnd,Domain,Address,Sum(AverageValue*( ((julianday(TimeEnd) - 2440587.5) * 86400.0) -((julianday(TimeStart) - 2440587.5) * 86400.0) )/" + timescaleseconds.ToString(CultureInfo.InvariantCulture) + ") as CounterValue from ValuesHist where " + filter + " Parameter = @parameterName AND " + GetParameterizedDateRangeFilter(ref dbCommand, startDate, endDate) + " group by Domain, Address, strftime('%H', TimeStart) order by TimeStart desc;";
dbCommand.Parameters.Add(new SQLiteParameter("@parameterName", parameterName));
dbCommand.CommandText = query;
var reader = dbCommand.ExecuteReader();
//
while (reader.Read())
{
var entry = new StatisticsEntry();
entry.TimeStart = DateTime.Parse(reader.GetString(0));
entry.TimeEnd = DateTime.Parse(reader.GetString(1));
entry.Domain = reader.GetString(2);
entry.Address = reader.GetString(3);
entry.Value = 0;
try
{
entry.Value = (double)reader.GetFloat(4);
}
catch
{
var value = reader.GetValue(4);
if (value != DBNull.Value && value != null)
{
double.TryParse(
reader.GetString(4),
out entry.Value
);
}
}
//
values.Add(entry);
}
//
reader.Close();
}
return(values);
}
public List <StatisticsEntry> GetHourlyStats(
string domain,
string address,
string parameterName,
string aggregator
)
{
var values = new List <StatisticsEntry>();
//lock (dbLock)
{
var dbCommand = dbConnection.CreateCommand();
string filter = "";
if (domain != "" && address != "")
{
filter = "Domain ='" + domain + "' and Address = '" + address + "' and ";
}
string query = "select TimeStart,TimeEnd,Domain,Address," + aggregator + "(AverageValue) as Value from ValuesHist where " + filter + "Parameter = '" + parameterName + "' group by Domain, Address, strftime('%H', TimeStart) order by TimeStart asc;";
dbCommand.CommandText = query;
var reader = dbCommand.ExecuteReader();
//
while (reader.Read())
{
var entry = new StatisticsEntry();
entry.TimeStart = DateTime.Parse(reader.GetString(0));
entry.TimeEnd = DateTime.Parse(reader.GetString(1));
entry.Domain = reader.GetString(2);
entry.Address = reader.GetString(3);
entry.Value = 0;
try
{
entry.Value = (double)reader.GetFloat(4);
}
catch
{
var value = reader.GetValue(4);
if (value != DBNull.Value && value != null)
{
double.TryParse(
reader.GetString(4),
out entry.Value
);
}
}
//
values.Add(entry);
}
//
reader.Close();
}
return(values);
}
public List <StatisticsEntry> GetHourlyCounter(
string domain,
string address,
string parameterName,
double timescaleseconds
)
{
var values = new List <StatisticsEntry>();
//lock (dbLock)
{
var dbCommand = dbConnection.CreateCommand();
string filter = "";
if (domain != "" && address != "")
{
filter = "Domain ='" + domain + "' and Address = '" + address + "' and ";
}
string query = "select TimeStart,TimeEnd,Domain,Address,Sum(AverageValue*( ((julianday(TimeEnd) - 2440587.5) * 86400.0) -((julianday(TimeStart) - 2440587.5) * 86400.0) )/" + timescaleseconds.ToString(System.Globalization.CultureInfo.InvariantCulture) + ") as CounterValue from ValuesHist where " + filter + "Parameter = '" + parameterName + "' group by Domain, Address, strftime('%H', TimeStart) order by TimeStart desc;";
dbCommand.CommandText = query;
var reader = dbCommand.ExecuteReader();
//
while (reader.Read())
{
var entry = new StatisticsEntry();
entry.TimeStart = DateTime.Parse(reader.GetString(0));
entry.TimeEnd = DateTime.Parse(reader.GetString(1));
entry.Domain = reader.GetString(2);
entry.Address = reader.GetString(3);
entry.Value = 0;
try
{
entry.Value = (double)reader.GetFloat(4);
}
catch
{
var value = reader.GetValue(4);
if (value != DBNull.Value && value != null)
{
double.TryParse(
reader.GetString(4),
out entry.Value
);
}
}
//
values.Add(entry);
}
//
reader.Close();
}
return(values);
}
public List <StatisticsEntry> GetHourlyStats24(string domain, string address, string param, string aggregator)
{
List <StatisticsEntry> values = new List <StatisticsEntry>();
lock (_dblock)
{
SQLiteCommand dbcmd = _dbconnection.CreateCommand();
string filter = "";
DateTime startdate = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00.000000");
if (domain != "" && address != "")
{
filter = "Domain ='" + domain + "' and Address = '" + address + "' and ";
}
// aggregated averages by hour
string q = "select TimeStart,TimeEnd,Domain,Address," + aggregator + "(AverageValue) as Value from ValuesHist where " + filter + "Parameter = '" + param + "' and TimeStart >= '" + startdate.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss.ffffff") + "' group by Domain, Address, strftime('%H', TimeStart) order by TimeStart asc;";
dbcmd.CommandText = q;
SQLiteDataReader reader = dbcmd.ExecuteReader();
//
while (reader.Read())
{
StatisticsEntry se = new StatisticsEntry();
se.TimeStart = DateTime.Parse(reader.GetString(0));
se.TimeEnd = DateTime.Parse(reader.GetString(1));
se.Domain = reader.GetString(2);
se.Address = reader.GetString(3);
se.Value = 0;
try
{
se.Value = (double)reader.GetFloat(4);
}
catch
{
var val = reader.GetValue(4);
if (val != DBNull.Value && val != null)
{
double.TryParse(reader.GetString(4), out se.Value);
}
}
//
values.Add(se);
}
//
reader.Close();
}
return(values);
}
/// <summary>
/// Gets the today detail.
/// </summary>
/// <returns>The today detail.</returns>
/// <param name="domain">Domain.</param>
/// <param name="address">Address.</param>
/// <param name="parameterName">Parameter name.</param>
/// <param name="aggregator">Aggregator.</param>
public List <StatisticsEntry> GetTodayDetail(
string domain,
string address,
string parameterName,
string aggregator = "Avg"
)
{
var values = new List <StatisticsEntry>();
var dbCommand = dbConnection.CreateCommand();
string filter = "";
string groupBy = "";
var start = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00.000000");
dbCommand.Parameters.Add(new SQLiteParameter("@start", start.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss.ffffff")));
if (!string.IsNullOrEmpty(domain) && !string.IsNullOrEmpty(address))
{
// detailed module stats. We set our own aggregator. (Detailed red line in chart)
filter = " Domain=@domain AND Address=@address and ";
dbCommand.Parameters.Add(new SQLiteParameter("@domain", domain));
dbCommand.Parameters.Add(new SQLiteParameter("@address", address));
aggregator = "AverageValue";
}
else
{
// aggregated averages by hour
if (!string.IsNullOrEmpty(aggregator))
{
aggregator = aggregator + "(AverageValue)";
}
groupBy = "group by TimeStart";
}
string query = "select TimeStart,TimeEnd,Domain,Address," + aggregator + " as Value from ValuesHist where " + filter + " Parameter = @parameterName AND TimeStart >= @start " + groupBy + " order by TimeStart asc;";
dbCommand.Parameters.Add(new SQLiteParameter("@parameterName", parameterName));
dbCommand.CommandText = query;
var reader = dbCommand.ExecuteReader();
while (reader.Read())
{
// If nothing is found in filter during aggregate, we get a row of all DBNulls. Skip the entry.
// NOTE: We got an exception before this check if HG sends a request for a param that has no results
// for the Parameter/TimeStart filter. We got single row of all DBNulls.
if (reader.IsDBNull(0))
{
continue;
}
var entry = new StatisticsEntry();
entry.TimeStart = DateTime.Parse(reader.GetString(0));
entry.TimeEnd = DateTime.Parse(reader.GetString(1));
entry.Domain = reader.GetString(2);
entry.Address = reader.GetString(3);
entry.Value = 0;
try
{
entry.Value = (double)reader.GetFloat(4);
}
catch
{
var value = reader.GetValue(4);
if (value != DBNull.Value && value != null)
{
double.TryParse(
reader.GetString(4),
out entry.Value
);
}
}
values.Add(entry);
}
reader.Close();
return(values);
}
/// <summary>
/// Gets the today detail.
/// </summary>
/// <returns>The today detail.</returns>
/// <param name="domain">Domain.</param>
/// <param name="address">Address.</param>
/// <param name="parameterName">Parameter name.</param>
/// <param name="aggregator">Aggregator.</param>
public List<StatisticsEntry> GetTodayDetail(
string domain,
string address,
string parameterName,
string aggregator = "Avg"
)
{
var values = new List<StatisticsEntry>();
//lock (dbLock)
{
var dbCommand = dbConnection.CreateCommand();
string filter = "";
string groupBy = "";
var start = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00.000000");
dbCommand.Parameters.Add(new SQLiteParameter("@start", start.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss.ffffff")));
if (!string.IsNullOrEmpty(domain) && !string.IsNullOrEmpty(address))
{
// detailed module stats. We set our own aggregator. (Detailed red line in chart)
filter = " Domain=@domain AND Address=@address and ";
dbCommand.Parameters.Add(new SQLiteParameter("@domain", domain));
dbCommand.Parameters.Add(new SQLiteParameter("@address", address));
aggregator = "AverageValue";
}
else
{
// aggregated averages by hour
if (!string.IsNullOrEmpty(aggregator))
{
aggregator = aggregator + "(AverageValue)";
}
groupBy = "group by TimeStart";
}
string query = "select TimeStart,TimeEnd,Domain,Address," + aggregator + " as Value from ValuesHist where " + filter + " Parameter = @parameterName AND TimeStart >= @start " + groupBy + " order by TimeStart asc;";
dbCommand.Parameters.Add(new SQLiteParameter("@parameterName", parameterName));
dbCommand.CommandText = query;
var reader = dbCommand.ExecuteReader();
//
while (reader.Read())
{
// If nothing is found in filter during aggregate, we get a row of all DBNulls. Skip the entry.
// NOTE: We got an exception before this check if HG sends a request for a param that has no results
// for the Parameter/TimeStart filter. We got single row of all DBNulls.
if (reader.IsDBNull(0))
{
continue;
}
var entry = new StatisticsEntry();
entry.TimeStart = DateTime.Parse(reader.GetString(0));
entry.TimeEnd = DateTime.Parse(reader.GetString(1));
entry.Domain = reader.GetString(2);
entry.Address = reader.GetString(3);
entry.Value = 0;
try
{
entry.Value = (double)reader.GetFloat(4);
}
catch
{
var value = reader.GetValue(4);
if (value != DBNull.Value && value != null) double.TryParse(
reader.GetString(4),
out entry.Value
);
}
//
values.Add(entry);
}
//
reader.Close();
}
return values;
}
/// <summary>
/// This is for the current day's AVERAGES part: (TODAY_AVG)
/// </summary>
/// <param name="domain"></param>
/// <param name="address"></param>
/// <param name="parameterName"></param>
/// <param name="aggregator"></param>
/// <returns></returns>
public List<StatisticsEntry> GetHourlyStatsToday(
string domain,
string address,
string parameterName,
string aggregator
)
{
var values = new List<StatisticsEntry>();
//lock (dbLock)
{
var dbCommand = dbConnection.CreateCommand();
string filter = "";
var start = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00.000000");
dbCommand.Parameters.Add(new SQLiteParameter("@start", start.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss.ffffff") ));
//if (domain != "" && address != "") filter = "Domain ='" + domain + "' and Address = '" + address + "' and ";
if (!string.IsNullOrEmpty(domain) && !string.IsNullOrEmpty(address))
{
filter = " Domain=@domain AND Address=@address and ";
dbCommand.Parameters.Add(new SQLiteParameter("@domain", domain));
dbCommand.Parameters.Add(new SQLiteParameter("@address", address));
}
dbCommand.Parameters.Add(new SQLiteParameter("@parameterName", parameterName));
// aggregated averages by hour
string query = "select TimeStart,TimeEnd,Domain,Address," + aggregator + "(AverageValue) as Value from ValuesHist where " + filter + "Parameter = @parameterName and TimeStart >= @start group by Domain, Address, strftime('%H', TimeStart) order by TimeStart asc;";
dbCommand.CommandText = query;
SQLiteDataReader reader = dbCommand.ExecuteReader();
//
while (reader.Read())
{
var entry = new StatisticsEntry();
entry.TimeStart = DateTime.Parse(reader.GetString(0));
entry.TimeEnd = DateTime.Parse(reader.GetString(1));
entry.Domain = reader.GetString(2);
entry.Address = reader.GetString(3);
entry.Value = 0;
try
{
entry.Value = (double)reader.GetFloat(4);
}
catch
{
var value = reader.GetValue(4);
if (value != DBNull.Value && value != null) double.TryParse(
reader.GetString(4),
out entry.Value
);
}
//
values.Add(entry);
}
//
reader.Close();
}
return values;
}
/// <summary>
/// This is for the overall AVERAGES part: (MIN), (MAX), (AVG)
/// </summary>
/// <param name="domain"></param>
/// <param name="address"></param>
/// <param name="parameterName"></param>
/// <param name="aggregator"></param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <returns></returns>
public List<StatisticsEntry> GetHourlyStats(
string domain,
string address,
string parameterName,
string aggregator,
DateTime startDate, DateTime endDate
)
{
var values = new List<StatisticsEntry>();
//lock (dbLock)
{
var dbCommand = dbConnection.CreateCommand();
string filter = "";
if (!string.IsNullOrEmpty(domain) && !string.IsNullOrEmpty(address))
{
filter = " Domain=@domain AND Address=@address and ";
dbCommand.Parameters.Add(new SQLiteParameter("@domain", domain));
dbCommand.Parameters.Add(new SQLiteParameter("@address", address));
}
string query = "";
if( aggregator != "" )
query = "select TimeStart,TimeEnd,Domain,Address," + aggregator + "(AverageValue) as Value from ValuesHist where " + filter + " Parameter = @parameterName AND " + GetParameterizedDateRangeFilter(ref dbCommand, startDate, endDate) + " group by Domain, Address, strftime('%H', TimeStart) order by TimeStart asc;";
else
query = "select TimeStart,TimeEnd,Domain,Address,AverageValue from ValuesHist where " + filter + " Parameter = @parameterName AND " + GetParameterizedDateRangeFilter(ref dbCommand, startDate, endDate) + " order by TimeStart asc;";
dbCommand.Parameters.Add(new SQLiteParameter("@parameterName", parameterName));
//if (domain != "" && address != "") filter = "Domain ='" + domain + "' and Address = '" + address + "' and ";
//string query = "select TimeStart,TimeEnd,Domain,Address," + aggregator + "(AverageValue) as Value from ValuesHist where " + filter + "Parameter = '" + parameterName + "' AND " + GetDateRangeFilter(startDate, endDate) + " group by Domain, Address, strftime('%H', TimeStart) order by TimeStart asc;";
dbCommand.CommandText = query;
var reader = dbCommand.ExecuteReader();
//
while (reader.Read())
{
var entry = new StatisticsEntry();
entry.TimeStart = DateTime.Parse(reader.GetString(0));
entry.TimeEnd = DateTime.Parse(reader.GetString(1));
entry.Domain = reader.GetString(2);
entry.Address = reader.GetString(3);
entry.Value = 0;
try
{
entry.Value = (double)reader.GetFloat(4);
}
catch
{
var value = reader.GetValue(4);
if (value != DBNull.Value && value != null) double.TryParse(
reader.GetString(4),
out entry.Value
);
}
//
values.Add(entry);
}
//
reader.Close();
}
return values;
}
/// <summary>
/// Gets the hourly counter.
/// </summary>
/// <returns>The hourly counter.</returns>
/// <param name="domain">Domain.</param>
/// <param name="address">Address.</param>
/// <param name="parameterName">Parameter name.</param>
/// <param name="timescaleseconds">Timescaleseconds.</param>
/// <param name="startDate">Start date.</param>
/// <param name="endDate">End date.</param>
public List<StatisticsEntry> GetHourlyCounter(
string domain,
string address,
string parameterName,
double timescaleseconds,
DateTime startDate, DateTime endDate
)
{
var values = new List<StatisticsEntry>();
//lock (dbLock)
{
var dbCommand = dbConnection.CreateCommand();
string filter = "";
if (!string.IsNullOrEmpty(domain) && !string.IsNullOrEmpty(address))
{
filter= " Domain=@domain AND Address=@address and ";
dbCommand.Parameters.Add(new SQLiteParameter("@domain", domain));
dbCommand.Parameters.Add(new SQLiteParameter("@address", address));
}
string query = "select TimeStart,TimeEnd,Domain,Address,Sum(AverageValue*( ((julianday(TimeEnd) - 2440587.5) * 86400.0) -((julianday(TimeStart) - 2440587.5) * 86400.0) )/" + timescaleseconds.ToString(CultureInfo.InvariantCulture) + ") as CounterValue from ValuesHist where " + filter + " Parameter = @parameterName AND " + GetParameterizedDateRangeFilter(ref dbCommand, startDate, endDate) + " group by Domain, Address, strftime('%H', TimeStart) order by TimeStart desc;";
dbCommand.Parameters.Add(new SQLiteParameter("@parameterName", parameterName));
dbCommand.CommandText = query;
var reader = dbCommand.ExecuteReader();
//
while (reader.Read())
{
var entry = new StatisticsEntry();
entry.TimeStart = DateTime.Parse(reader.GetString(0));
entry.TimeEnd = DateTime.Parse(reader.GetString(1));
entry.Domain = reader.GetString(2);
entry.Address = reader.GetString(3);
entry.Value = 0;
try
{
entry.Value = (double)reader.GetFloat(4);
}
catch
{
var value = reader.GetValue(4);
if (value != DBNull.Value && value != null) double.TryParse(
reader.GetString(4),
out entry.Value
);
}
//
values.Add(entry);
}
//
reader.Close();
}
return values;
}
public List<StatisticsEntry> GetHourlyCounter(string domain, string address, string parameterName, double timescaleseconds)
{
var values = new List<StatisticsEntry>();
//lock (dbLock)
{
var dbCommand = dbConnection.CreateCommand();
string filter = "";
if (domain != "" && address != "") filter = "Domain ='" + domain + "' and Address = '" + address + "' and ";
string query = "select TimeStart,TimeEnd,Domain,Address,Sum(AverageValue*( ((julianday(TimeEnd) - 2440587.5) * 86400.0) -((julianday(TimeStart) - 2440587.5) * 86400.0) )/" + timescaleseconds.ToString(System.Globalization.CultureInfo.InvariantCulture) + ") as CounterValue from ValuesHist where " + filter + "Parameter = '" + parameterName + "' group by Domain, Address, strftime('%H', TimeStart) order by TimeStart desc;";
dbCommand.CommandText = query;
var reader = dbCommand.ExecuteReader();
//
while (reader.Read())
{
var entry = new StatisticsEntry();
entry.TimeStart = DateTime.Parse(reader.GetString(0));
entry.TimeEnd = DateTime.Parse(reader.GetString(1));
entry.Domain = reader.GetString(2);
entry.Address = reader.GetString(3);
entry.Value = 0;
try
{
entry.Value = (double)reader.GetFloat(4);
}
catch
{
var value = reader.GetValue(4);
if (value != DBNull.Value && value != null) double.TryParse(reader.GetString(4), out entry.Value);
}
//
values.Add(entry);
}
//
reader.Close();
}
return values;
}
public List<StatisticsEntry> GetTodayDetail(string domain, string address, string parameterName, string aggregator = "Avg")
{
var values = new List<StatisticsEntry>();
//lock (dbLock)
{
var dbCommand = dbConnection.CreateCommand();
string filter = "";
var start = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00.000000");
if (domain != "" && address != "") filter = "Domain ='" + domain + "' and Address = '" + address + "' and ";
// aggregated averages by hour
string q = "select TimeStart,TimeEnd,Domain,Address," + aggregator + "(AverageValue) as Value from ValuesHist where " + filter + "Parameter = '" + parameterName + "' and TimeStart >= '" + start.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss.ffffff") + "' group by TimeStart order by TimeStart asc;";
// detailed module stats
if (domain != "" && address != "")
{
q = "select TimeStart,TimeEnd,Domain,Address,AverageValue as Value from ValuesHist where " + filter + "Parameter = '" + parameterName + "' and TimeStart >= '" + start.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss.ffffff") + "' order by TimeStart asc;";
}
dbCommand.CommandText = q;
var reader = dbCommand.ExecuteReader();
//
while (reader.Read())
{
var entry = new StatisticsEntry();
entry.TimeStart = DateTime.Parse(reader.GetString(0));
entry.TimeEnd = DateTime.Parse(reader.GetString(1));
entry.Domain = reader.GetString(2);
entry.Address = reader.GetString(3);
entry.Value = 0;
try
{
entry.Value = (double)reader.GetFloat(4);
}
catch
{
var value = reader.GetValue(4);
if (value != DBNull.Value && value != null) double.TryParse(reader.GetString(4), out entry.Value);
}
//
values.Add(entry);
}
//
reader.Close();
}
return values;
}
public List<StatisticsEntry> GetHourlyStats(string domain, string address, string parameterName, string aggregator)
{
var values = new List<StatisticsEntry>();
//lock (dbLock)
{
var dbCommand = dbConnection.CreateCommand();
string filter = "";
if (domain != "" && address != "") filter = "Domain ='" + domain + "' and Address = '" + address + "' and ";
string query = "select TimeStart,TimeEnd,Domain,Address," + aggregator + "(AverageValue) as Value from ValuesHist where " + filter + "Parameter = '" + parameterName + "' group by Domain, Address, strftime('%H', TimeStart) order by TimeStart asc;";
dbCommand.CommandText = query;
var reader = dbCommand.ExecuteReader();
//
while (reader.Read())
{
var entry = new StatisticsEntry();
entry.TimeStart = DateTime.Parse(reader.GetString(0));
entry.TimeEnd = DateTime.Parse(reader.GetString(1));
entry.Domain = reader.GetString(2);
entry.Address = reader.GetString(3);
entry.Value = 0;
try
{
entry.Value = (double)reader.GetFloat(4);
}
catch
{
var value = reader.GetValue(4);
if (value != DBNull.Value && value != null) double.TryParse(reader.GetString(4), out entry.Value);
}
//
values.Add(entry);
}
//
reader.Close();
}
return values;
}
