private void PopulateIds(DomainVacancy entity, Vacancy dbVacancy)
{
PopulateCountyId(entity, dbVacancy);
PopulateVacancyLocationTypeId(entity, dbVacancy);
PopulateApprenticeshipTypeId(entity, dbVacancy);
PopulateFrameworkId(entity, dbVacancy);
PopulateSectorId(entity, dbVacancy);
PopulateLocalAuthorityId(entity, dbVacancy);
}
private void PopulateCountyId(DomainVacancy entity, Vacancy dbVacancy)
{
if (!string.IsNullOrWhiteSpace(entity.Address?.County))
{
dbVacancy.CountyId = _getOpenConnection.QueryCached <int>(_cacheDuration, @"
SELECT CountyId
FROM dbo.County
WHERE FullName = @CountyFullName",
new
{
CountyFullName = entity.Address.County
}).SingleOrDefault();
}
}
private void PopulateVacancyLocationTypeId(DomainVacancy entity, Vacancy dbVacancy)
{
// A vacancy is multilocation if IsEmployerAddressMainAddress is set to false
var vacancyLocationTypeCodeName = entity.IsEmployerLocationMainApprenticeshipLocation.HasValue && entity.IsEmployerLocationMainApprenticeshipLocation.Value
? "STD"
: "MUL";
dbVacancy.VacancyLocationTypeId = _getOpenConnection.QueryCached <int>(_cacheDuration, @"
SELECT VacancyLocationTypeId
FROM dbo.VacancyLocationType
WHERE CodeName = @VacancyLocationTypeCodeName",
new
{
VacancyLocationTypeCodeName = vacancyLocationTypeCodeName
}).Single();
}
private void UpdateVacancyHistory(Vacancy previousVacancyState, Vacancy actualVacancyState)
{
if (StatusHasChanged(previousVacancyState, actualVacancyState))
{
CreateVacancyHistoryRow(actualVacancyState.VacancyId, _currentUserService.CurrentUserName,
VacancyHistoryEventType.StatusChange, actualVacancyState.VacancyStatusId, StatusChangeText);
}
if (ClosingDateHasBeenUpdated(previousVacancyState, actualVacancyState))
{
CreateVacancyHistoryRow(actualVacancyState.VacancyId, _currentUserService.CurrentUserName,
VacancyHistoryEventType.StatusChange, actualVacancyState.VacancyStatusId, StatusChangeText);
CreateVacancyHistoryRow(actualVacancyState.VacancyId, _currentUserService.CurrentUserName,
VacancyHistoryEventType.Note, actualVacancyState.VacancyStatusId, previousVacancyState.ApplicationClosingDate.Value.ToString("yyyy-MM-dd"));
}
}
private void PopulateSectorId(DomainVacancy entity, Vacancy dbVacancy)
{
if (!string.IsNullOrWhiteSpace(entity.SectorCodeName))
{
dbVacancy.SectorId = _getOpenConnection.QueryCached <int>(_cacheDuration, @"
SELECT ApprenticeshipOccupationId
FROM dbo.ApprenticeshipOccupation
WHERE CodeName = @SectorCodeName",
new
{
entity.SectorCodeName
}).Single();
}
else
{
dbVacancy.SectorId = null;
}
}
private void PopulateLocalAuthorityId(DomainVacancy entity, Vacancy dbVacancy)
{
if (!string.IsNullOrWhiteSpace(entity.LocalAuthorityCode))
{
dbVacancy.LocalAuthorityId = _getOpenConnection.QueryCached <int>(_cacheDuration, @"
SELECT LocalAuthorityId
FROM dbo.LocalAuthority
WHERE CodeName LIKE '%' + @LocalAuthorityCode",
new
{
entity.LocalAuthorityCode
}).Single();
}
else
{
dbVacancy.LocalAuthorityId = null;
}
}
private void PopulateApprenticeshipTypeId(DomainVacancy entity, Vacancy dbVacancy)
{
if (entity.VacancyType == VacancyType.Traineeship)
{
dbVacancy.ApprenticeshipType = _getOpenConnection.QueryCached <int>(_cacheDuration, @"
SELECT ApprenticeshipTypeId
FROM dbo.ApprenticeshipType
WHERE CodeName = 'TRA'").Single();
return;
}
dbVacancy.ApprenticeshipType = _getOpenConnection.QueryCached <int>(_cacheDuration, @"
SELECT ApprenticeshipTypeId
FROM dbo.ApprenticeshipType at JOIN Reference.EducationLevel el ON at.EducationLevelId = el.EducationLevelId
WHERE el.CodeName = @EducationLevel",
new
{
EducationLevel = (int)entity.ApprenticeshipLevel
}).Single(); // There's a better way to do this?
}
private void PopulateFrameworkId(DomainVacancy entity, Vacancy dbVacancy)
{
if (!string.IsNullOrWhiteSpace(entity.FrameworkCodeName))
{
dbVacancy.ApprenticeshipFrameworkId = _getOpenConnection.QueryCached <int>(_cacheDuration, @"
SELECT ApprenticeshipFrameworkId
FROM dbo.ApprenticeshipFramework
WHERE CodeName = @FrameworkCodeName",
new
{
entity.FrameworkCodeName
}).Single();
}
else
{
dbVacancy.ApprenticeshipFrameworkId = null;
}
if (entity.VacancyType == VacancyType.Traineeship)
{
dbVacancy.ApprenticeshipFrameworkId = TraineeshipFrameworkId;
}
}
private static bool ClosingDateHasBeenUpdated(Vacancy previousVacancy, Vacancy actualVacancy)
{
return(previousVacancy.VacancyStatusId == (int)VacancyStatus.Live &&
actualVacancy.VacancyStatusId == (int)VacancyStatus.Live &&
previousVacancy.ApplicationClosingDate != actualVacancy.ApplicationClosingDate);
}
private static bool StatusHasChanged(Vacancy previousVacancy, Vacancy actualVacancy)
{
return(previousVacancy.VacancyStatusId != actualVacancy.VacancyStatusId);
}