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
q.[Id],
q.[QueueName],
q.[JobName],
q.[JobType],
q.[QueuedOn],
q.[TryNumber],
s.[Name] AS [ScheduleName]
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(
@"ORDER BY q.[QueuedOn] ASC, q.[JobName] 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<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;
}