public VacancySummaries GetVacancySummaries(int pageNumber)
{
//Page number coming in increments from 1 rather than 0, the repo expects pages to start at 0 so take one from the passed in value
var query = new VacancySummaryByStatusQuery()
{
PageSize = PageSize,
RequestedPage = pageNumber,
DesiredStatuses = _desiredStatuses
};
int totalRecords;
var vacancies = _vacancySummaryService.GetWithStatus(query, out totalRecords);
var vacancyParties = _providerService.GetVacancyOwnerRelationships(vacancies.Select(v => v.VacancyOwnerRelationshipId).Distinct(), false);
var employers = _employerService.GetEmployers(vacancyParties.Values.Select(v => v.EmployerId).Distinct()).ToDictionary(e => e.EmployerId, e => e);
var providers = _providerService.GetProviders(vacancies.Select(v => v.ContractOwnerId).Distinct()).ToDictionary(p => p.ProviderId, p => p);
var categories = _referenceDataProvider.GetCategories(CategoryStatus.Active, CategoryStatus.PendingClosure).ToList();
//TODO: workaround to have the indexing partially working. Should be done properly
var apprenticeshipSummaries =
vacancies.Where(v => v.VacancyType == VacancyType.Apprenticeship).Select(
v =>
{
try
{
return(ApprenticeshipSummaryMapper.GetApprenticeshipSummary(v,
employers[vacancyParties[v.VacancyOwnerRelationshipId].EmployerId],
providers[v.ContractOwnerId],
categories, _logService));
}
catch (Exception ex)
{
_logService.Error($"Error indexing the apprenticeship vacancy with ID={v.VacancyId}", ex);
return(null);
}
});
var traineeshipSummaries =
vacancies.Where(v => v.VacancyType == VacancyType.Traineeship).Select(
v =>
{
try
{
return(TraineeshipSummaryMapper.GetTraineeshipSummary(v,
employers[vacancyParties[v.VacancyOwnerRelationshipId].EmployerId],
providers[v.ContractOwnerId],
categories, _logService));
}
catch (Exception ex)
{
_logService.Error($"Error indexing the traineeship vacancy with ID={v.VacancyId}", ex);
return(null);
}
});
return(new VacancySummaries(apprenticeshipSummaries.Where(s => s != null), traineeshipSummaries.Where(s => s != null)));
}
public IList <RegionalTeamMetrics> GetRegionalTeamMetrics(VacancySummaryByStatusQuery query)
{
return(_vacancySummaryRepository.GetRegionalTeamMetrics(query));
}
public IList <VacancySummary> GetWithStatus(VacancySummaryByStatusQuery query, out int totalRecords)
{
return(_vacancySummaryRepository.GetByStatus(query, out totalRecords));
}
public IList <VacancySummary> GetWithStatus(VacancySummaryByStatusQuery query, out int totalRecords)
{
return(_getVacancySummaryStrategies.GetWithStatus(query, out totalRecords));
}
public IList <RegionalTeamMetrics> GetRegionalTeamsMetrics(VacancySummaryByStatusQuery query)
{
return(_getVacancySummaryStrategies.GetRegionalTeamMetrics(query));
}
public IList <RegionalTeamMetrics> GetRegionalTeamMetrics(VacancySummaryByStatusQuery query)
{
var sqlParams = new
{
VacancyStatuses = query.DesiredStatuses.Select(s => (int)s),
query = query.SearchString,
QueryMode = query.SearchMode,
};
var sql = $@"SELECT
rt.TeamName AS RegionalTeam,
COUNT(*) AS TotalCount,
COUNT(CASE {GetFilterSql(VacanciesSummaryFilterTypes.SubmittedToday, "WHEN")} THEN 1 END) AS SubmittedTodayCount,
COUNT(CASE {GetFilterSql(VacanciesSummaryFilterTypes.SubmittedYesterday, "WHEN")} THEN 1 END) AS SubmittedYesterdayCount,
COUNT(CASE {GetFilterSql(VacanciesSummaryFilterTypes.SubmittedMoreThan48Hours, "WHEN")} THEN 1 END) AS SubmittedMoreThan48HoursCount,
COUNT(CASE {GetFilterSql(VacanciesSummaryFilterTypes.Resubmitted, "WHEN")} THEN 1 END) AS ResubmittedCount
FROM Vacancy v
JOIN VacancyOwnerRelationship o
ON o.VacancyOwnerRelationshipId = v.VacancyOwnerRelationshipId
JOIN Employer e
ON o.EmployerId = e.EmployerId
JOIN Provider p
ON p.ProviderID = v.ContractOwnerID
JOIN ProviderSite s
ON s.ProviderSiteId = o.ProviderSiteId
JOIN RegionalTeamMappings t
ON s.PostCode LIKE t.PostcodeStart + '[0-9]%'
JOIN RegionalTeams rt
ON rt.Id = t.RegionalTeam_Id
WHERE v.VacancyStatusId IN @VacancyStatuses
AND (((@query IS NULL OR @query = ''))
OR (p.TradingName LIKE '%' + @query + '%' AND (@QueryMode = '{(int)ManageVacancySearchMode.All}' OR @QueryMode = '{(int)ManageVacancySearchMode.Provider}'))
OR (REPLACE(v.PostCode, ' ', '') LIKE REPLACE(@query, ' ', '') + '%' AND (@QueryMode = '{(int)ManageVacancySearchMode.All}' OR @QueryMode = '{(int)ManageVacancySearchMode.VacancyPostcode}'))
)
GROUP BY rt.TeamName, rt.Id
ORDER BY rt.Id ASC
";
var counts = _getOpenConnection.Query <RegionalTeamMetrics>(sql, sqlParams).ToList();
// fill in any blanks as the query won't return any all-zero regions
var expectedRegions = new[]
{
RegionalTeam.EastMidlands,
RegionalTeam.North,
RegionalTeam.NorthWest,
RegionalTeam.SouthEast,
RegionalTeam.SouthWest,
RegionalTeam.WestMidlands,
RegionalTeam.YorkshireAndHumberside,
RegionalTeam.EastAnglia
};
var fillItems = expectedRegions
.Where(w => counts.All(a => a.RegionalTeam != w))
.Select(s => new RegionalTeamMetrics()
{
RegionalTeam = s
});
counts.AddRange(fillItems);
return(counts.OrderBy(o => o.RegionalTeam).ToList());
}
public IList <VacancySummary> GetByStatus(VacancySummaryByStatusQuery query, out int totalRecords)
{
var sqlParams = new
{
Skip = query.PageSize * (query.RequestedPage - 1),
Take = query.PageSize,
Query = query.SearchString,
QueryMode = query.SearchMode,
VacancyStatuses = query.DesiredStatuses.Select(s => (int)s)
};
string orderByField = "";
switch (query.OrderByField)
{
case VacancySummaryOrderByColumn.Title:
orderByField = "v.Title";
break;
case VacancySummaryOrderByColumn.Provider:
orderByField = "p.TradingName";
break;
case VacancySummaryOrderByColumn.DateSubmitted:
orderByField = "dbo.GetSubmittedDate(v.VacancyID)";
break;
case VacancySummaryOrderByColumn.ClosingDate:
orderByField = "v.ApplicationClosingDate";
break;
case VacancySummaryOrderByColumn.SubmissionCount:
orderByField = "v.SubmissionCount";
break;
case VacancySummaryOrderByColumn.VacancyLocation:
orderByField = "v.Town";
break;
}
if (query.OrderByField == VacancySummaryOrderByColumn.OrderByFilter)
{
orderByField = "dbo.GetSubmittedDate(v.VacancyID)";
}
var filterSql = GetFilterSql(query.Filter);
var sql = $@"{CoreQuery}
--Text search
WHERE (((@query IS NULL OR @query = '') {(query.RegionalTeamName != RegionalTeam.Other ? $"AND rt.TeamName = '{query.RegionalTeamName}'" : "")})
OR (p.TradingName LIKE '%' + @query + '%' AND (@QueryMode = '{(int)ManageVacancySearchMode.All}' OR @QueryMode = '{(int)ManageVacancySearchMode.Provider}'))
OR (REPLACE(v.PostCode, ' ', '') LIKE REPLACE(@query, ' ', '') + '%' AND (@QueryMode = '{(int)ManageVacancySearchMode.All}' OR @QueryMode = '{(int)ManageVacancySearchMode.VacancyPostcode}'))
)
AND v.VacancyStatusId IN @VacancyStatuses
{filterSql}
{(!string.IsNullOrEmpty(orderByField) ? ("ORDER BY " + orderByField + (query.Order == Order.Descending ? " DESC" : "")) : "")}
{(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>, IList <VacancySummary> >(vacancies);
return(mapped);
}
