Ejemplo n.º 1
0
 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")));
 }
Ejemplo n.º 2
0
 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)));
 }
Ejemplo n.º 3
0
 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")));
 }
Ejemplo n.º 4
0
        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")));
        }
Ejemplo n.º 5
0
        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());
                    }
                }
            }
        }
Ejemplo n.º 7
0
 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")));
 }