/// <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; }