/// <summary>
        /// Gets a set of system statistics for the given application name and date ranges.
        /// </summary>
        /// <param name="applicationName">The name of the application to get system statistics for.</param>
        /// <param name="recentBeginDate">The begin date of the recent period to get statistics for.</param>
        /// <param name="distantBeginDate">The begin date of the distant period to get statistics for.</param>
        /// <param name="endDate">The end date of the distant period to get statistics for.</param>
        /// <param name="transaction">The transaction to use, if applicable.</param>
        /// <returns>A set of system statistics.</returns>
        public StatisticsRecord GetStatistics(string applicationName, DateTime recentBeginDate, DateTime distantBeginDate, DateTime endDate, IDbTransaction transaction)
        {
            const string HistoryStatusSql =
            @"SELECT CAST(COUNT([Id]) AS bigint)
            FROM [BlueCollarHistory]
            WHERE
            [ApplicationName] = @ApplicationName
            AND [FinishedOn] > {0}
            AND [FinishedOn] <= @End;

            SELECT CAST(COUNT([Id]) AS bigint)
            FROM [BlueCollarHistory]
            WHERE
            [ApplicationName] = @ApplicationName
            AND [FinishedOn] > {0}
            AND [FinishedOn] <= @End
            AND [Status] = @Succeeded;

            SELECT CAST(COUNT([Id]) AS bigint)
            FROM [BlueCollarHistory]
            WHERE
            [ApplicationName] = @ApplicationName
            AND [FinishedOn] > {0}
            AND [FinishedOn] <= @End
            AND [Status] = @Failed;

            SELECT CAST(COUNT([Id]) AS bigint)
            FROM [BlueCollarHistory]
            WHERE
            [ApplicationName] = @ApplicationName
            AND [FinishedOn] > {0}
            AND [FinishedOn] <= @End
            AND [Status] = @Canceled;

            SELECT CAST(COUNT([Id]) AS bigint)
            FROM [BlueCollarHistory]
            WHERE
            [ApplicationName] = @ApplicationName
            AND [FinishedOn] > {0}
            AND [FinishedOn] <= @End
            AND [Status] = @TimedOut;

            SELECT CAST(COUNT([Id]) AS bigint)
            FROM [BlueCollarHistory]
            WHERE
            [ApplicationName] = @ApplicationName
            AND [FinishedOn] > {0}
            AND [FinishedOn] <= @End
            AND [Status] = @Interrupted;";

            const string JobsPerHourSql =
            @"SELECT
            [Day],
            [QueueName],
            CAST(AVG([Count]) AS bigint) AS [JobsPerHour]
            FROM
            (
            SELECT
            CAST(DATEPART(yy, [Hour]) AS varchar(4))
            + '-' + CAST(DATEPART(m, [Hour]) AS varchar(2))
            + '-' + CAST(DATEPART(d, [Hour]) AS varchar(2))
            + ' ' + '00:00:00' AS [Day],
            [QueueName],
            [Count]
            FROM
            (
            SELECT
            [Hour],
            [QueueName],
            COUNT([Id]) AS [Count]
            FROM
            (
            SELECT
                [Id],
                [QueueName],
                CAST(DATEPART(yy, [FinishedOn]) AS varchar(4))
                    + '-' + CAST(DATEPART(m, [FinishedOn]) AS varchar(2))
                    + '-' + CAST(DATEPART(d, [FinishedOn]) AS varchar(2))
                    + ' ' + CAST(DATEPART(hh, [FinishedOn]) AS varchar(2))
                    + ':00:00' AS [Hour]
            FROM [BlueCollarHistory]
            WHERE
                [ApplicationName] = @ApplicationName
                AND [FinishedOn] > @DistantBegin
                AND [FinishedOn] <= @End
            ) t1
            GROUP BY [Hour], [QueueName]
            ) t2
            ) t3
            GROUP BY [Day], [QueueName]";

            const string JobsPerWorkerSql =
            @"SELECT
            w.[Name],
            w.[MachineName],
            w.[MachineAddress],
            CAST(COUNT(h.[Id]) AS bigint) AS [Count]
            FROM [BlueCollarHistory] h
            LEFT OUTER JOIN [BlueCollarWorker] w ON h.[WorkerId] = w.[Id]
            WHERE
            h.[ApplicationName] = @ApplicationName
            AND h.[FinishedOn] > @DistantBegin
            AND h.[FinishedOn] <= @End
            GROUP BY w.[Name], w.[MachineName], w.[MachineAddress];";

            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(CultureInfo.InvariantCulture, HistoryStatusSql, "@DistantBegin");
            sb.Append("\n\n");
            sb.AppendFormat(CultureInfo.InvariantCulture, HistoryStatusSql, "@RecentBegin");
            sb.Append("\n\n");
            sb.Append(JobsPerHourSql);
            sb.Append("\n\n");
            sb.Append(JobsPerWorkerSql);
            sb.Append("\n\n");
            sb.Append(CountsSql);

            var p = new
            {
                ApplicationName = applicationName,
                DistantBegin = distantBeginDate,
                RecentBegin = recentBeginDate,
                End = endDate,
                Succeeded = HistoryStatus.Succeeded.ToString(),
                Failed = HistoryStatus.Failed.ToString(),
                Canceled = HistoryStatus.Canceled.ToString(),
                TimedOut = HistoryStatus.TimedOut.ToString(),
                Interrupted = HistoryStatus.Interrupted.ToString()
            };

            StatisticsRecord stats = new StatisticsRecord();

            using (var multi = this.connection.QueryMultiple(sb.ToString(), p, transaction, null, null))
            {
                stats.HistoryStatusDistant = CreateHistoryStatusCounts(multi);
                stats.HistoryStatusRecent = CreateHistoryStatusCounts(multi);

                foreach (var record in multi.Read<JobsPerHourByDayRecord>().OrderBy(r => r.Date))
                {
                    stats.JobsPerHourByDay.Add(record);
                }

                foreach (var record in multi.Read<JobsPerWorkerRecord>())
                {
                    stats.JobsPerWorker.Add(record);
                }

                stats.Counts = CreateCounts(multi);
            }

            return stats;
        }
Exemplo n.º 2
0
        /// <summary>
        /// Gets a set of system statistics for the given application name and date ranges.
        /// </summary>
        /// <param name="applicationName">The name of the application to get system statistics for.</param>
        /// <param name="recentBeginDate">The begin date of the recent period to get statistics for.</param>
        /// <param name="distantBeginDate">The begin date of the distant period to get statistics for.</param>
        /// <param name="endDate">The end date of the distant period to get statistics for.</param>
        /// <param name="transaction">The transaction to use, if applicable.</param>
        /// <returns>A set of system statistics.</returns>
        public StatisticsRecord GetStatistics(string applicationName, DateTime recentBeginDate, DateTime distantBeginDate, DateTime endDate, IDbTransaction transaction)
        {
            const string HistoryStatusSql =
            @"SELECT CAST(COUNT([Id]) AS bigint)
            FROM [BlueCollarHistory]
            WHERE
            [ApplicationName] = @ApplicationName
            AND [FinishedOn] > {0}
            AND [FinishedOn] <= @End;

            SELECT CAST(COUNT([Id]) AS bigint)
            FROM [BlueCollarHistory]
            WHERE
            [ApplicationName] = @ApplicationName
            AND [FinishedOn] > {0}
            AND [FinishedOn] <= @End
            AND [Status] = @Succeeded;

            SELECT CAST(COUNT([Id]) AS bigint)
            FROM [BlueCollarHistory]
            WHERE
            [ApplicationName] = @ApplicationName
            AND [FinishedOn] > {0}
            AND [FinishedOn] <= @End
            AND [Status] = @Failed;

            SELECT CAST(COUNT([Id]) AS bigint)
            FROM [BlueCollarHistory]
            WHERE
            [ApplicationName] = @ApplicationName
            AND [FinishedOn] > {0}
            AND [FinishedOn] <= @End
            AND [Status] = @Canceled;

            SELECT CAST(COUNT([Id]) AS bigint)
            FROM [BlueCollarHistory]
            WHERE
            [ApplicationName] = @ApplicationName
            AND [FinishedOn] > {0}
            AND [FinishedOn] <= @End
            AND [Status] = @TimedOut;

            SELECT CAST(COUNT([Id]) AS bigint)
            FROM [BlueCollarHistory]
            WHERE
            [ApplicationName] = @ApplicationName
            AND [FinishedOn] > {0}
            AND [FinishedOn] <= @End
            AND [Status] = @Interrupted;";

            // QueueName needs "AS QueueName" for some reason; the projection is naming the column
            // key as literally "[QueueName]" (meaning, with brackets) otherwise.
            const string JobsPerHourSql =
            @"SELECT
            datetime([Day]) AS [Day],
            [QueueName] AS [QueueName],
            CAST(AVG([Count]) AS bigint) AS [JobsPerHour]
            FROM
            (
            SELECT
            date([Hour], 'start of day') AS [Day],
            [QueueName],
            [Count]
            FROM
            (
            SELECT
            [Hour],
            [QueueName],
            COUNT([Id]) AS [Count]
            FROM
            (
            SELECT
                [Id],
                [QueueName],
                strftime('%Y-%m-%d %H:00:00', [FinishedOn]) AS [Hour]
            FROM [BlueCollarHistory]
            WHERE
                [ApplicationName] = @ApplicationName
                AND [FinishedOn] > @DistantBegin
                AND [FinishedOn] <= @End
            ) t1
            GROUP BY [Hour], [QueueName]
            ) t2
            ) t3
            GROUP BY [Day], [QueueName];";

            const string JobsPerWorkerSql =
            @"SELECT
            w.[Name],
            w.[MachineName],
            w.[MachineAddress],
            CAST(COUNT(h.[Id]) AS bigint) AS [Count]
            FROM [BlueCollarHistory] h
            LEFT OUTER JOIN [BlueCollarWorker] w ON h.[WorkerId] = w.[Id]
            WHERE
            h.[ApplicationName] = @ApplicationName
            AND h.[FinishedOn] > @DistantBegin
            AND h.[FinishedOn] <= @End
            GROUP BY w.[Name], w.[MachineName], w.[MachineAddress];";

            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(CultureInfo.InvariantCulture, HistoryStatusSql, "@DistantBegin");
            sb.Append("\n\n");
            sb.AppendFormat(CultureInfo.InvariantCulture, HistoryStatusSql, "@RecentBegin");
            sb.Append("\n\n");
            sb.Append(JobsPerHourSql);
            sb.Append("\n\n");
            sb.Append(JobsPerWorkerSql);
            sb.Append("\n\n");
            sb.Append(CountsSql);

            var p = new
            {
                ApplicationName = applicationName,
                DistantBegin = distantBeginDate,
                RecentBegin = recentBeginDate,
                End = endDate,
                Succeeded = HistoryStatus.Succeeded.ToString(),
                Failed = HistoryStatus.Failed.ToString(),
                Canceled = HistoryStatus.Canceled.ToString(),
                TimedOut = HistoryStatus.TimedOut.ToString(),
                Interrupted = HistoryStatus.Interrupted.ToString()
            };

            StatisticsRecord stats = new StatisticsRecord();

            using (var multi = this.connection.QueryMultiple(sb.ToString(), p, transaction, null, null))
            {
                stats.HistoryStatusDistant = CreateHistoryStatusCounts(multi);
                stats.HistoryStatusRecent = CreateHistoryStatusCounts(multi);

                foreach (var record in multi.Read<JobsPerHourByDayRecord>().OrderBy(r => r.Date))
                {
                    stats.JobsPerHourByDay.Add(record);
                }

                foreach (var record in multi.Read<JobsPerWorkerRecord>())
                {
                    stats.JobsPerWorker.Add(record);
                }

                stats.Counts = CreateCounts(multi);
            }

            return stats;
        }