public List <ApprenticeshipVacancy> Find(ApprenticeshipVacancyQuery query, out int totalResultsCount)
{
_logger.Debug("Calling database to find apprenticeship vacancies");
var liveStatus = _mapper.Map <ProviderVacancyStatuses, string>(ProviderVacancyStatuses.Live);
var coreQuery = @"
FROM Vacancy.Vacancy
WHERE Vacancy.VacancyStatusCode = 'LIV' -- TODO: Probably would want to parameterise from constant
" + (string.IsNullOrWhiteSpace(query.FrameworkCodeName) ? "" : "AND Vacancy.FrameworkCodeName = @FrameworkCodeName") + @"
" + (query.LiveDate.HasValue ? "AND Vacancy.PublishedDateTime >= @LiveDate" : "") + @"
" + (query.LatestClosingDate.HasValue ? "AND Vacancy.ClosingDate <= @LiveDate" : ""); // Vacancy.PublishedDateTime >= @LiveDate was Vacancy.DateSubmitted >= @LiveDate
// TODO: Vacancy.DateSubmitted should be DateLive (or DatePublished)???
var data = _getOpenConnection.QueryMultiple <int, Vacancy.Vacancy>(@"
SELECT COUNT(*)
" + coreQuery + @"
SELECT *
" + coreQuery + @"
ORDER BY Vacancy.VacancyReferenceNumber
OFFSET ((@CurrentPage - 1) * @PageSize) ROWS
FETCH NEXT @PageSize ROWS ONLY
", query);
totalResultsCount = data.Item1.Single();
var dbVacancies = data.Item2;
_logger.Debug("Found {0} apprenticeship vacanc(ies)", dbVacancies.Count);
return(new List <ApprenticeshipVacancy>());
}
public IList <VacancySummary> Find(ApprenticeshipVacancyQuery query, out int totalRecords)
{
var sqlParams = new
{
Skip = query.PageSize * (query.RequestedPage - 1),
Take = query.PageSize,
VacancyStatuses = query.DesiredStatuses.Select(s => (int)s),
query.FrameworkCodeName,
query.LatestClosingDate,
query.LiveDate
};
var sql = $@"{CoreQuery}
WHERE v.VacancyStatusId IN @VacancyStatuses
{(!string.IsNullOrEmpty(query.FrameworkCodeName) ? "AND af.CodeName = @FrameworkCodeName" : "")}
{(query.EditedInRaa ? "AND v.EditedInRaa = 1" : "")}
{(query.LiveDate.HasValue ? "AND dbo.GetLiveDate(v.VacancyId) >= @LiveDate" : "")}
{(query.LatestClosingDate.HasValue ? "AND v.ApplicationClosingDate <= @LatestClosingDate" : "")}
ORDER BY v.VacancyReferenceNumber
{(query.PageSize > 0 ? "OFFSET (@skip) ROWS FETCH NEXT (@take) ROWS ONLY" : "")}
";
var vacancies = _getOpenConnection.Query <DbVacancySummary>(sql, sqlParams);
// return the total record count as well
totalRecords = vacancies.Any() ? vacancies.First().TotalResultCount : 0;
var mapped = Mapper.Map <IList <DbVacancySummary>, List <VacancySummary> >(vacancies);
return(mapped);
}
public List <VacancySummary> Find(ApprenticeshipVacancyQuery query, out int totalResultsCount)
{
_logger.Debug("Calling repository to find apprenticeship vacancies");
var mongoQueryConditions = new List <IMongoQuery>()
{
Query <Vacancy> .EQ(vacancy => vacancy.Status, VacancyStatus.Live)
};
if (!string.IsNullOrWhiteSpace(query.FrameworkCodeName))
{
mongoQueryConditions.Add(Query <Vacancy>
.EQ(vacancy => vacancy.FrameworkCodeName, query.FrameworkCodeName));
}
if (query.LiveDate.HasValue)
{
// TODO: DateSubmitted should be DateLive (or DatePublished).
mongoQueryConditions.Add(Query <Vacancy>
.GTE(vacancy => vacancy.DateSubmitted, query.LiveDate));
}
if (query.LatestClosingDate.HasValue)
{
mongoQueryConditions.Add(Query <Vacancy>
.LTE(vacancy => vacancy.ClosingDate, query.LatestClosingDate));
}
if (query.DesiredStatuses.Any())
{
mongoQueryConditions.Add(Query <Vacancy>
.In(vacancy => vacancy.Status, query.DesiredStatuses));
}
var queryBuilder = new QueryBuilder <Vacancy>();
var vacancies = Collection.Find(queryBuilder.And(mongoQueryConditions))
.SetSortOrder(SortBy.Ascending("VacancyReferenceNumber"))
.SetSkip(query.PageSize * (query.RequestedPage - 1))
.SetLimit(query.PageSize)
.Select(vacancy => _mapper.Map <MongoVacancy, VacancySummary>(vacancy))
.ToList();
totalResultsCount = Convert.ToInt32(Collection.Count(queryBuilder.And(mongoQueryConditions)));
_logger.Debug("Found {0} apprenticeship vacanc(ies)", vacancies.Count);
return(vacancies);
}
public void Test()
{
var vacanySummaryRepository = new VacancySummaryRepository(_connection);
var query = new ApprenticeshipVacancyQuery()
{
RequestedPage = 1,
DesiredStatuses = new List <VacancyStatus>()
{
VacancyStatus.Live
},
LatestClosingDate = DateTime.Now,
EditedInRaa = false,
PageSize = 1000
};
int resultCount;
var eligibleVacancies = vacanySummaryRepository.Find(query, out resultCount);
Assert.GreaterOrEqual(1, eligibleVacancies.Count);
}
/// <summary>
/// This queues up to 1000 vacancies for
/// </summary>
/// <param name="deadline"></param>
public void QueueVacanciesForClosure(DateTime deadline)
{
var stopwatch = new Stopwatch();
stopwatch.Start();
var query = new ApprenticeshipVacancyQuery() {
RequestedPage = 1,
DesiredStatuses = new List<VacancyStatus>() { VacancyStatus.Live },
LatestClosingDate = deadline,
EditedInRaa = false,
PageSize = 1000 };
int resultCount;
var eligibleVacancies = _vacancySummaryService.Find(query, out resultCount);
var message = string.Format("Querying vacancies about to close took {0}", stopwatch.Elapsed);
var counter = 0;
foreach (var vacancy in eligibleVacancies)
{
var eligibleForClosure = new VacancyEligibleForClosure(vacancy.VacancyId);
_serviceBus.PublishMessage(eligibleForClosure);
counter ++;
}
stopwatch.Stop();
message += string.Format(". Queuing {0} vacancies for closure took {1}", counter, stopwatch.Elapsed);
if (stopwatch.ElapsedMilliseconds > 60000)
{
_logService.Warn(message);
}
else
{
_logService.Info(message);
}
}
public IList <VacancySummary> Find(ApprenticeshipVacancyQuery query, out int resultCount)
{
return(_vacancySummaryRepository.Find(query, out resultCount));
}
