/// <summary>
/// Gets a schedule and its related scheduled jobs, filtered by the given list parameters.
/// </summary>
/// <param name="applicationName">The name of the application to get the scheduled job list for.</param>
/// <param name="id">The ID of the schedule to get.</param>
/// <param name="search">The search query to filter the related job 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 schedule, or null if none was found.</returns>
public ScheduledJobRecordList GetScheduledJobList(string applicationName, long id, string search, int limit, int offset, IDbTransaction transaction)
{
StringBuilder cb = new StringBuilder(
@"SELECT CAST(COUNT(j.[Id]) AS bigint)
FROM [BlueCollarScheduledJob] j
WHERE
j.[ScheduleId] = @Id");
StringBuilder sb = new StringBuilder(
@"SELECT TOP 1 s.[Id], s.[Name]
FROM [BlueCollarSchedule] s
WHERE
s.[Id] = @Id;
SELECT *
FROM
(
SELECT
*,
ROW_NUMBER() OVER(ORDER BY j.[Number] ASC) AS [RowNumber]
FROM [BlueCollarScheduledJob] j
WHERE
j.[ScheduleId] = @Id");
if (!string.IsNullOrEmpty(search))
{
cb.Append("\n AND j.[JobType] LIKE @Search");
sb.Append("\n AND j.[JobType] LIKE @Search");
}
cb.Append(";\n\n");
sb.Append(
@") t
WHERE t.[RowNumber] BETWEEN @Offset + 1 AND @Limit + 1;");
sb.Append("\n");
sb.Append(CountsSql);
var p = new
{
ApplicationName = applicationName,
Id = id,
Search = !string.IsNullOrEmpty(search) ? string.Concat("%", search, "%") : null,
Limit = limit,
Offset = offset
};
ScheduledJobRecordList list = new ScheduledJobRecordList();
using (var multi = this.connection.QueryMultiple(cb.ToString() + sb.ToString(), p, transaction, null, null))
{
list.SetPaging(multi.Read<long>().First(), limit, offset);
var schedule = multi.Read<ScheduleRecord>().FirstOrDefault();
if (schedule != null)
{
list.Id = schedule.Id.Value;
list.Name = schedule.Name;
}
foreach (var record in multi.Read<ScheduledJobRecord>())
{
list.Records.Add(record);
}
list.Counts = CreateCounts(multi);
}
return list;
}
/// <summary>
/// Gets a schedule and its related scheduled jobs, filtered by the given list parameters.
/// </summary>
/// <param name="applicationName">The name of the application to get the scheduled job list for.</param>
/// <param name="id">The ID of the schedule to get.</param>
/// <param name="search">The search query to filter the related job 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 schedule, or null if none was found.</returns>
public ScheduledJobRecordList GetScheduledJobList(string applicationName, long id, string search, int limit, int offset, IDbTransaction transaction)
{
StringBuilder cb = new StringBuilder(
@"SELECT CAST(COUNT(j.[Id]) AS bigint)
FROM [BlueCollarScheduledJob] j
WHERE
j.[ScheduleId] = @Id");
StringBuilder sb = new StringBuilder(
@"SELECT s.[Id], s.[Name], j.*
FROM [BlueCollarSchedule] s
LEFT OUTER JOIN [BlueCollarScheduledJob] j ON s.[Id] = j.[ScheduleId]");
if (!string.IsNullOrEmpty(search))
{
cb.Append("\n AND j.[JobType] LIKE @Search");
sb.Append(" AND j.[JobType] LIKE @Search");
}
cb.Append(";\n\n");
sb.Append(@"
WHERE
s.[Id] = @Id
ORDER BY s.[Name], j.[Number] ASC
LIMIT @Limit OFFSET @Offset;");
sb.Append("\n");
sb.Append(CountsSql);
var p = new
{
ApplicationName = applicationName,
Id = id,
Search = !string.IsNullOrEmpty(search) ? string.Concat("%", search, "%") : null,
Limit = limit,
Offset = offset
};
ScheduledJobRecordList list = new ScheduledJobRecordList();
using (var multi = this.connection.QueryMultiple(cb.ToString() + sb.ToString(), p, transaction, null, null))
{
bool readSchedule = false;
list.SetPaging(multi.Read<long>().First(), limit, offset);
var records = multi.Read<ScheduleRecord, ScheduledJobRecord, ScheduledJobRecord>(
(s, sj) =>
{
if (!readSchedule)
{
list.Id = s.Id.Value;
list.Name = s.Name;
}
return sj;
},
"Id").Where(r => r != null);
foreach (var record in records)
{
list.Records.Add(record);
}
list.Counts = CreateCounts(multi);
}
return list;
}