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