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