/// <summary>
        /// Gets a list of queue records.
        /// </summary>
        /// <param name="applicationName">The name of the application to get the queue list for.</param>
        /// <param name="search">The search query to filter the collection with.</param>
        /// <param name="limit">The paging limit to use.</param>
        /// <param name="offset">The paging offset to use.</param>
        /// <param name="transaction">The transaction to use, if applicable.</param>
        /// <returns>A collection of queue records.</returns>
        public RecordList<QueueListRecord> GetQueuedList(string applicationName, string search, int limit, int offset, IDbTransaction transaction)
        {
            StringBuilder cb = new StringBuilder(
            @"SELECT CAST(COUNT(q.[Id]) AS bigint)
            FROM [BlueCollarQueue] q
            LEFT OUTER JOIN [BlueCollarSchedule] s ON q.[ScheduleId] = s.[Id]
            WHERE
            q.[ApplicationName] = @ApplicationName");

            StringBuilder sb = new StringBuilder(
            @"SELECT *
            FROM
            (
            SELECT
            q.[Id],
            q.[QueueName],
            q.[JobName],
            q.[JobType],
            q.[QueuedOn],
            q.[TryNumber],
            s.[Name] AS [ScheduleName],
            ROW_NUMBER() OVER(ORDER BY q.[QueuedOn] ASC, q.[JobName] ASC) AS [RowNumber]
            FROM [BlueCollarQueue] q
            LEFT OUTER JOIN [BlueCollarSchedule] s ON q.[ScheduleId] = s.[Id]
            WHERE
            q.[ApplicationName] = @ApplicationName");

            if (!string.IsNullOrEmpty(search))
            {
                const string Search = @"
            AND
            (
            q.[QueueName] LIKE @Search
            OR q.[JobName] LIKE @Search
            OR q.[JobType] LIKE @Search
            OR s.[Name] LIKE @Search
            )";

                cb.Append(Search);
                sb.Append(Search);
            }

            cb.Append(";\n\n");

            sb.Append("\n");
            sb.Append(
            @") t
            WHERE t.[RowNumber] BETWEEN @Offset + 1 AND @Offset + @Limit;");

            sb.Append("\n");
            sb.Append(CountsSql);

            var p = new
            {
                ApplicationName = applicationName,
                Search = !string.IsNullOrEmpty(search) ? string.Concat("%", search, "%") : null,
                Limit = limit,
                Offset = offset
            };

            var list = new RecordList<QueueListRecord>();

            using (var multi = this.connection.QueryMultiple(cb.ToString() + sb.ToString(), p, transaction, null, null))
            {
                list.SetPaging(multi.Read<long>().First(), limit, offset);

                foreach (var record in multi.Read<QueueListRecord>())
                {
                    list.Records.Add(record);
                }

                list.Counts = CreateCounts(multi);
            }

            return list;
        }
Пример #2
0
        /// <summary>
        /// Gets a list of history records.
        /// </summary>
        /// <param name="applicationName">The name of the application to get the history list for.</param>
        /// <param name="search">The search query to filter the collection with.</param>
        /// <param name="limit">The paging limit to use.</param>
        /// <param name="offset">The paging offset to use.</param>
        /// <param name="transaction">The transaction to use, if applicable.</param>
        /// <returns>A list of history records.</returns>
        public RecordList<HistoryListRecord> GetHistoryList(string applicationName, string search, int limit, int offset, IDbTransaction transaction)
        {
            StringBuilder cb = new StringBuilder(
            @"SELECT CAST(COUNT(h.[Id]) AS bigint)
            FROM [BlueCollarHistory] h
            LEFT OUTER JOIN [BlueCollarSchedule] s ON h.[ScheduleId] = s.[Id]
            WHERE
            h.[ApplicationName] = @ApplicationName");

            StringBuilder sb = new StringBuilder(
            @"SELECT
            h.[Id],
            h.[QueueName],
            h.[JobName],
            h.[JobType],
            h.[QueuedOn],
            h.[TryNumber],
            h.[StartedOn],
            h.[Status],
            h.[FinishedOn],
            s.[Name] AS [ScheduleName]
            FROM [BlueCollarHistory] h
            LEFT OUTER JOIN [BlueCollarSchedule] s ON h.[ScheduleId] = s.[Id]
            WHERE
            h.[ApplicationName] = @ApplicationName");

            if (!string.IsNullOrEmpty(search))
            {
                const string Search = @"
            AND
            (
            h.[QueueName] LIKE @Search
            OR h.[JobName] LIKE @Search
            OR h.[JobType] LIKE @Search
            OR h.[Status] LIKE @Search
            OR s.[Name] LIKE @Search
            )";

                cb.Append(Search);
                sb.Append(Search);
            }

            cb.Append(";\n\n");

            sb.Append("\n");
            sb.Append(
            @"ORDER BY h.[QueuedOn] DESC, h.[JobName] ASC, h.[TryNumber] DESC
            LIMIT @Limit OFFSET @Offset;");

            sb.Append("\n");
            sb.Append(CountsSql);

            var p = new
            {
                ApplicationName = applicationName,
                Search = !string.IsNullOrEmpty(search) ? string.Concat("%", search, "%") : null,
                Limit = limit,
                Offset = offset
            };

            var list = new RecordList<HistoryListRecord>();

            using (var multi = this.connection.QueryMultiple(cb.ToString() + sb.ToString(), p, transaction, null, null))
            {
                list.SetPaging(multi.Read<long>().First(), limit, offset);

                foreach (var record in multi.Read<HistoryListRecord>())
                {
                    list.Records.Add(record);
                }

                list.Counts = CreateCounts(multi);
            }

            return list;
        }
        /// <summary>
        /// Gets a list of worker records.
        /// </summary>
        /// <param name="applicationName">The application name to get records for.</param>
        /// <param name="search">The search query to filter the collection with.</param>
        /// <param name="limit">The paging limit to use.</param>
        /// <param name="offset">The paging offset to use.</param>
        /// <param name="transaction">The transaction to use, if applicable.</param>
        /// <returns>A collection of worker records.</returns>
        public RecordList<WorkerRecord> GetWorkerList(string applicationName, string search, int limit, int offset, IDbTransaction transaction)
        {
            StringBuilder cb = new StringBuilder(
            @"SELECT CAST(COUNT([Id]) AS bigint)
            FROM [BlueCollarWorker]
            WHERE
            [ApplicationName] = @ApplicationName");

            StringBuilder sb = new StringBuilder(
            @"SELECT *
            FROM
            (
            SELECT
            *,
            ROW_NUMBER() OVER(ORDER BY [Name] ASC) AS [RowNumber]
            FROM [BlueCollarWorker]
            WHERE
            [ApplicationName] = @ApplicationName");

            if (!string.IsNullOrEmpty(search))
            {
                const string Search = @"
            AND
            (
            [Name] LIKE @Search
            OR [MachineName] LIKE @Search
            OR [MachineAddress] LIKE @Search
            OR [QueueNames] LIKE @Search
            OR [Status] LIKE @Search
            OR [Signal] LIKE @Search
            OR [Startup] LIKE @Search
            )";

                cb.Append(Search);
                sb.Append(Search);
            }

            cb.Append(";\n\n");

            sb.Append("\n");
            sb.Append(
            @") t
            WHERE t.[RowNumber] BETWEEN @Offset + 1 AND @Offset + @Limit;");

            sb.Append("\n");
            sb.Append(CountsSql);

            var p = new
            {
                ApplicationName = applicationName,
                Search = !string.IsNullOrEmpty(search) ? string.Concat("%", search, "%") : null,
                Limit = limit,
                Offset = offset
            };

            var list = new RecordList<WorkerRecord>();

            using (var multi = this.connection.QueryMultiple(cb.ToString() + sb.ToString(), p, transaction, null, null))
            {
                list.SetPaging(multi.Read<long>().First(), limit, offset);

                foreach (var record in multi.Read<WorkerRecord>())
                {
                    list.Records.Add(record);
                }

                list.Counts = CreateCounts(multi);
            }

            return list;
        }
Пример #4
0
        /// <summary>
        /// Gets a list of schedule records.
        /// </summary>
        /// <param name="applicationName">The name of the application to get the schedule list for.</param>
        /// <param name="search">The search query to filter the collection with.</param>
        /// <param name="limit">The paging limit to use.</param>
        /// <param name="offset">The paging offset to use.</param>
        /// <param name="transaction">The transaction to use, if applicable.</param>
        /// <returns>A collection of schedules.</returns>
        public RecordList<ScheduleListRecord> GetScheduleList(string applicationName, string search, int limit, int offset, IDbTransaction transaction)
        {
            StringBuilder cb = new StringBuilder(
            @"SELECT CAST(COUNT(DISTINCT s.[Id]) AS bigint)
            FROM [BlueCollarSchedule] s
            LEFT OUTER JOIN [BlueCollarScheduledJob] j ON s.[Id] = j.[ScheduleId]
            WHERE
            s.[ApplicationName] = @ApplicationName");

            StringBuilder sb = new StringBuilder(
            @"SELECT DISTINCT s.*,
            (
            SELECT CAST(COUNT(sj.[Id]) AS bigint)
            FROM [BlueCollarScheduledJob] sj
            WHERE
            sj.[ScheduleId] = s.[Id]
            ) AS [JobCount]
            FROM [BlueCollarSchedule] s
            LEFT OUTER JOIN [BlueCollarScheduledJob] j ON s.[Id] = j.[ScheduleId]
            WHERE
            s.[ApplicationName] = @ApplicationName");

            if (!string.IsNullOrEmpty(search))
            {
                const string Search = @"
            AND
            (
            s.[QueueName] LIKE @Search
            OR s.[Name] LIKE @Search
            OR s.[RepeatType] LIKE @Search
            OR j.[JobType] LIKE @Search
            )";

                cb.Append(Search);
                sb.Append(Search);
            }

            cb.Append(";\n\n");

            sb.Append("\n");
            sb.Append(
            @"ORDER BY s.[Name] ASC
            LIMIT @Limit OFFSET @Offset;");

            sb.Append("\n");
            sb.Append(CountsSql);

            var p = new
            {
                ApplicationName = applicationName,
                Search = !string.IsNullOrEmpty(search) ? string.Concat("%", search, "%") : null,
                Limit = limit,
                Offset = offset
            };

            var list = new RecordList<ScheduleListRecord>();

            using (var multi = this.connection.QueryMultiple(cb.ToString() + sb.ToString(), p, transaction, null, null))
            {
                list.SetPaging(multi.Read<long>().First(), limit, offset);

                foreach (var record in multi.Read<ScheduleListRecord>())
                {
                    list.Records.Add(record);
                }

                list.Counts = CreateCounts(multi);
            }

            return list;
        }