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