public VacancyCounts GetLotteryCounts(VacancySummaryQuery query)
{
var sqlParams = new
{
query.ProviderId,
query.ProviderSiteId,
QueryMode = query.SearchMode,
Query = query.SearchString
};
var sql = $@"SELECT
COUNT(CASE {GetFilterSql(VacanciesSummaryFilterTypes.Live, "WHEN")} THEN 1 END) AS LiveCount,
COUNT(CASE {GetFilterSql(VacanciesSummaryFilterTypes.Submitted, "WHEN")} THEN 1 END) AS SubmittedCount,
COUNT(CASE {GetFilterSql(VacanciesSummaryFilterTypes.Rejected, "WHEN")} THEN 1 END) AS RejectedCount,
COUNT(CASE {GetFilterSql(VacanciesSummaryFilterTypes.ClosingSoon, "WHEN")} THEN 1 END) AS ClosingSoonCount,
COUNT(CASE {GetFilterSql(VacanciesSummaryFilterTypes.Closed, "WHEN")} THEN 1 END) AS ClosedCount,
COUNT(CASE {GetFilterSql(VacanciesSummaryFilterTypes.Draft, "WHEN")} THEN 1 END) AS DraftCount,
SUM(dbo.GetNewApplicantCount(v.VacancyId)) AS NewApplicationsCount,
COUNT(CASE {GetFilterSql(VacanciesSummaryFilterTypes.Completed, "WHEN")} THEN 1 END) AS CompletedCount
FROM Vacancy v
JOIN VacancyOwnerRelationship o
ON o.VacancyOwnerRelationshipId = v.VacancyOwnerRelationshipId
JOIN Employer e
ON o.EmployerId = e.EmployerId
JOIN ProviderSiteRelationship r
ON r.ProviderSiteId = o.ProviderSiteId
WHERE o.ProviderSiteID = @ProviderSiteId
AND (v.VacancyManagerId = @ProviderSiteId
OR v.DeliveryOrganisationId = @ProviderSiteId)
AND (v.VacancyTypeId = {(int)query.VacancyType} OR v.VacancyTypeId = {(int)VacancyType.Unknown})
--Text search
AND ((@query IS NULL OR @query = '')
OR ((CAST(v.VacancyReferenceNumber AS VARCHAR(255)) = @query AND (@QueryMode = '{(int)VacancySearchMode.All}' OR @QueryMode = '{(int)VacancySearchMode.ReferenceNumber}'))
OR (v.Title LIKE '%' + @query + '%' AND (@QueryMode = '{(int)VacancySearchMode.All}' OR @QueryMode = '{(int)VacancySearchMode.VacancyTitle}'))
OR (e.FullName LIKE '%' + @query + '%' AND (@QueryMode = '{(int)VacancySearchMode.All}' OR @QueryMode = '{(int)VacancySearchMode.EmployerName}'))
OR (REPLACE(v.PostCode, ' ', '') LIKE REPLACE(@query, ' ', '') + '%' AND (@QueryMode = '{(int)VacancySearchMode.All}' OR @QueryMode = '{(int)VacancySearchMode.Postcode}')))
)
AND r.ProviderId = @providerId
AND r.ProviderSiteRelationshipTypeId = 1
";
var counts = _getOpenConnection.Query <VacancyCounts>(sql, sqlParams);
// only one item returns from this query
return(counts.FirstOrDefault());
}
/// <summary>
/// Search and filter vacancies for the calling provider
/// </summary>
/// <param name="filter">Filter by vacancy status</param>
/// <param name="providerSiteId">The site Id of the provider</param>
/// <param name="providerId">The providers database id</param>
/// <param name="type">The type of vacancy; Apprenticeship or Traineeeship</param>
/// <returns>A list of vacancies</returns>
public IEnumerable <VacancySummary> Get(VacancyType type, VacanciesSummaryFilterTypes filter, int providerId, int providerSiteId)
{
var query = new VacancySummaryQuery()
{
PageSize = 100,
RequestedPage = 1,
VacancyType = type,
Filter = filter,
OrderByField = VacancySummaryOrderByColumn.OrderByFilter,
Order = Order.Ascending,
ProviderId = providerId,
ProviderSiteId = providerSiteId
};
int totalRecords;
return(_VacancySummaryService.GetSummariesForProvider(query, out totalRecords));
}
public VacancyCounts GetLotteryCounts(VacancySummaryQuery query)
{
return(_vacancySummaryRepository.GetLotteryCounts(query));
}
public IList <VacancySummary> GetSummariesForProvider(VacancySummaryQuery query, out int totalRecords)
{
return(_vacancySummaryRepository.GetSummariesForProvider(query, out totalRecords));
}
public IList <VacancySummary> GetSummariesForProvider(VacancySummaryQuery query, out int totalRecords)
{
var sqlParams = new
{
Skip = query.PageSize * (query.RequestedPage - 1),
Take = query.PageSize,
QueryMode = query.SearchMode,
Query = query.SearchString,
query.ProviderId,
query.ProviderSiteId
};
string orderByField = "";
switch (query.OrderByField)
{
case VacancySummaryOrderByColumn.Title:
orderByField = "v.Title";
break;
case VacancySummaryOrderByColumn.Applications:
orderByField = @"(CASE v.ApplyOutsideNAVMS
WHEN 1 THEN COALESCE(v.NoOfOfflineApplicants, 0)
ELSE dbo.GetApplicantCount(v.VacancyId)
END)" ;
break;
case VacancySummaryOrderByColumn.Employer:
orderByField = "e.FullName";
break;
case VacancySummaryOrderByColumn.Location:
orderByField = $"v.Town {(query.Order == Order.Descending ? "DESC" : "")}, v.PostCode";
break;
}
if (query.OrderByField == VacancySummaryOrderByColumn.OrderByFilter)
{
switch (query.Filter)
{
case VacanciesSummaryFilterTypes.ClosingSoon:
case VacanciesSummaryFilterTypes.NewApplications:
orderByField = @"v.ApplicationClosingDate, CreatedDate"; //created date
query.Order = Order.Descending;
break;
case VacanciesSummaryFilterTypes.Closed:
case VacanciesSummaryFilterTypes.Live:
case VacanciesSummaryFilterTypes.Completed:
orderByField = "e.FullName, v.Title";
break;
case VacanciesSummaryFilterTypes.All:
case VacanciesSummaryFilterTypes.Submitted:
case VacanciesSummaryFilterTypes.Rejected:
case VacanciesSummaryFilterTypes.Draft:
// Requirement is "most recently created first" (Faizal 30/6/2016).
// Previously there was no ordering in the code and it was coming out in natural database order
orderByField = "CreatedDate";
query.Order = Order.Descending;
break;
default:
throw new ArgumentException($"{query.Filter}");
}
}
var filterSql = GetFilterSql(query.Filter);
var sql = $@"{CoreQuery}
JOIN ProviderSiteRelationship r
ON r.ProviderSiteId = o.ProviderSiteId
WHERE o.ProviderSiteID = @ProviderSiteId
AND (v.VacancyManagerId = @ProviderSiteId
OR v.DeliveryOrganisationId = @ProviderSiteId)
AND r.ProviderId = @providerId
AND r.ProviderSiteRelationshipTypeId = 1
AND (v.VacancyTypeId = {(int)query.VacancyType} OR v.VacancyTypeId = {(int)VacancyType.Unknown})
--Text search
AND ((@query IS NULL OR @query = '')
OR ((CAST(v.VacancyReferenceNumber AS VARCHAR(255)) = @query AND (@QueryMode = '{(int)VacancySearchMode.All}' OR @QueryMode = '{(int)VacancySearchMode.ReferenceNumber}'))
OR (v.Title LIKE '%' + @query + '%' AND (@QueryMode = '{(int)VacancySearchMode.All}' OR @QueryMode = '{(int)VacancySearchMode.VacancyTitle}'))
OR (e.FullName LIKE '%' + @query + '%' AND (@QueryMode = '{(int)VacancySearchMode.All}' OR @QueryMode = '{(int)VacancySearchMode.EmployerName}'))
OR (REPLACE(v.PostCode, ' ', '') LIKE REPLACE(@query, ' ', '') + '%' AND (@QueryMode = '{(int)VacancySearchMode.All}' OR @QueryMode = '{(int)VacancySearchMode.Postcode}')))
)
AND v.VacancyStatusId != 4
{filterSql}
ORDER BY {orderByField} {(query.Order == Order.Descending ? "DESC" : "")}
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);
}
