protected override AdhocCommandBase GetPieChartDataForSiteCommand(string site)
{
return(SQLiteAdhocCommand.UsingSmartConnection(myConfig.ConnectionString)
.WithSql(SQLiteStatement.Create(
"SELECT CheckId, COUNT(*) [Failures] FROM AgentData WHERE (Result = 0) AND (EventType = 'Result') AND (lower(SiteId)=")
.InsertParameter("@pSiteId", site.ToLower())
.Append(") GROUP BY CheckId")));
}
protected override AdhocCommandBase GetGeckoMeterDataForSiteCheckCommand(GeckometerArgs args)
{
return(SQLiteAdhocCommand.UsingSmartConnection(myConfig.ConnectionString)
.WithSql(SQLiteStatement.Create(
"SELECT MIN(ResultCount) [Min], MAX(ResultCount) [Max], AVG(ResultCount) [Avg] FROM AgentData WHERE (ResultCount IS NOT NULL) AND (EventType = 'Result') AND (lower(SiteId)=")
.InsertParameter("@pSiteId", args.Site.ToLower())
.Append(") AND (lower(CheckId)=")
.InsertParameter("@pCheckId", args.Check.ToLower()).Append(")")
.AppendIf(() => !string.IsNullOrEmpty(args.Tag), "AND (tag='{0}')", args.Tag)));
}
protected override AdhocCommandBase GetComparisonDataForSiteCheckCommand(ComparisonArgs args)
{
return(SQLiteAdhocCommand.UsingSmartConnection(myConfig.ConnectionString)
.WithSql(SQLiteStatement.Create(
"SELECT ResultCount FROM AgentData WHERE (ResultCount IS NOT NULL) AND (EventType = 'Result') AND (lower(SiteId)=")
.InsertParameter("@pSiteId", args.Site.ToLower())
.Append(") AND (lower(CheckId)=")
.InsertParameter("@pCheckId", args.Check.ToLower()).Append(")")
.AppendIf(() => !string.IsNullOrEmpty(args.Tag), "AND (tag='{0}')", args.Tag)
.OrderBy("GeneratedOnUtc")
.Append("LIMIT 2")));
}
protected override AdhocCommandBase GetGeckoboardPieChartForCheckCommand(PieChartArgs args)
{
var outcome = ConvertOutcome(args.Outcome);
var operation = ConvertOperation(args.DataOperation);
return(SQLiteAdhocCommand.UsingSmartConnection(myConfig.ConnectionString)
.WithSql(SQLiteStatement.Create(
"SELECT Tags [SegmentId], {0} [Count] FROM AgentData WHERE CheckId=", operation)
.InsertParameter("@pCheckId", args.Check)
.AppendIf(() => !string.IsNullOrEmpty(outcome), "AND {0}", outcome)
.AppendIf(() => !string.IsNullOrEmpty(args.Site), "AND SiteId=")
.InsertParameterIf(() => !string.IsNullOrEmpty(args.Site), "@pSiteId", args.Site)
.AppendIf(() => !string.IsNullOrEmpty(args.Agent), "AND AgentId=")
.InsertParameterIf(() => !string.IsNullOrEmpty(args.Agent), "@pAgentId", args.Agent)
.Append("GROUP BY Tags")));
}
protected override AdhocCommandBase GetLineChartDataForCheckRateCommand(LineChartArgs args)
{
var outcome = ConvertOutcome(args.Outcome);
var operation = ConvertOperation(args.DataOperation);
return(SQLiteAdhocCommand.UsingSmartConnection(myConfig.ConnectionString)
.WithSql(SQLiteStatement.Create(
"SELECT ({0}*{1}) [MinutesBucket], {2} [ResultCount] FROM AgentData WHERE CheckId=",
args.Bucket,
args.Multiplier,
operation)
.InsertParameter("@pCheckId", args.Check)
.AppendIf(() => !string.IsNullOrEmpty(outcome), "AND {0}", outcome)
.AppendIf(() => !string.IsNullOrEmpty(args.Site), "AND SiteId=")
.InsertParameterIf(() => !string.IsNullOrEmpty(args.Site), "@pSiteId", args.Site)
.AppendIf(() => !string.IsNullOrEmpty(args.Agent), "AND AgentId=")
.InsertParameterIf(() => !string.IsNullOrEmpty(args.Agent), "@pAgentId", args.Agent)
.AppendIf(() => !string.IsNullOrEmpty(args.Tag), "AND tag=")
.InsertParameterIf(() => !string.IsNullOrEmpty(args.Tag), "@pTag", args.Tag)
.Append("GROUP BY {0}", args.Bucket)
.OrderBy(args.Bucket)
.Append("LIMIT {0}", args.Limit)));
}
public void Execute()
{
var lastRun = Convert.ToInt64(myLastRun.ToString("yyyMMddHHmm"));
using (var query = SQLiteAdhocCommand.UsingSmartConnection(myConfig.ConnectionString)
.WithSql(SQLiteStatement.Create("select s.name, datetime(year || '-' || substr('0' || month, -2,2) || '-' || substr('0' || day, -2,2) || ' ' || substr('0' || hour, -2,2) || ':' || substr('0' || min, -2,2)) [recorded], ")
.Append("ch1_amps_min, ch1_amps_avg, ch1_amps_max, ch1_kw_min, CAST(ch1_kw_avg AS REAL) [ch1_kw_avg], ch1_kw_max")
.Append("from energy_history h join energy_sensor s on h.addr = s.addr")
.Append("where CAST((year || substr('0' || month, -2,2) || substr('0' || day, -2,2) || substr('0' || hour, -2,2) || substr('0' || min, -2,2)) AS INTEGER) > ")
.InsertParameter("@lastrun", lastRun)))
{
using (var reader = query.ExecuteReader())
{
while (reader.Read())
{
// assumes that results are in ascending order
myLastRun = Convert.ToDateTime(reader["recorded"]);
var sensor = reader["name"].ToString();
var data = HealthCheckData.For(Identity, sensor)
.AddTag(sensor);
// report average watts
data.ResultCount = Convert.ToDouble(reader["ch1_kw_avg"]);
data.GeneratedOnUtc = myLastRun.ToUniversalTime();
data.Properties = new ResultProperties();
Messenger.Publish(data);
Logger.Debug("[{0}] Sent reading {1}kw taken {2}",
Identity.Name,
data.ResultCount,
data.GeneratedOnUtc.ToLocalTime());
}
}
}
}
protected override AdhocCommandBase GetPieChartDataForAllSitesCommand()
{
return(SQLiteAdhocCommand.UsingSmartConnection(myConfig.ConnectionString)
.WithSql(SQLiteStatement.Create("SELECT SiteId, COUNT(*) [Failures] FROM AgentData WHERE Result = 0 AND EventType = 'Result' GROUP BY SiteId")));
}