BlueCollar.SQLiteRepository.GetStatistics C# (CSharp) Метод

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