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