public DomainVacancy Update(DomainVacancy entity)
{
_logger.Debug("Calling database to update apprenticeship vacancy with id={0}", entity.VacancyId);
_logger.Info(
$"[{entity.VacancyGuid}] Calling database to update the following vacancy: {JsonConvert.SerializeObject(entity, Formatting.Indented, new JsonSerializerSettings { ContractResolver = new ExcludeLiveClosingDateResolver() })}");
UpdateEntityTimestamps(entity); // Do we need this?
var dbVacancy = _mapper.Map <DomainVacancy, Vacancy>(entity);
PopulateIds(entity, dbVacancy);
var previousVacancyState = GetBy(entity.VacancyId);
_logger.Info(
$"[{entity.VacancyGuid}] Calling database to update the following vacancy: {JsonConvert.SerializeObject(dbVacancy, Formatting.Indented, new JsonSerializerSettings { ContractResolver = new ExcludeLiveClosingDateResolver() })}");
_getOpenConnection.UpdateSingle(dbVacancy);
SaveTextFieldsFor(dbVacancy.VacancyId, entity);
SaveAdditionalQuestionsFor(dbVacancy.VacancyId, entity);
SaveCommentsFor(dbVacancy.VacancyId, entity);
UpdateVacancyHistory(previousVacancyState, dbVacancy);
_logger.Debug("Updated apprenticeship vacancy with to database with id={0}", entity.VacancyId);
return(GetByMapped(entity.VacancyId));
}
private void SaveCommentsFor(int vacancyId, DomainVacancy entity)
{
SaveComment(vacancyId, ReferralCommentCodeName.TitleComment, entity.TitleComment);
SaveComment(vacancyId, ReferralCommentCodeName.ApprenticeshipLevelComment, entity.ApprenticeshipLevelComment);
SaveComment(vacancyId, ReferralCommentCodeName.ClosingDateComment, entity.ClosingDateComment);
SaveComment(vacancyId, ReferralCommentCodeName.ContactDetailsComment, entity.ContactDetailsComment); // Maybe CFS
SaveComment(vacancyId, ReferralCommentCodeName.DesiredQualificationsComment, entity.DesiredQualificationsComment);
SaveComment(vacancyId, ReferralCommentCodeName.DesiredSkillsComment, entity.DesiredSkillsComment);
SaveComment(vacancyId, ReferralCommentCodeName.DurationComment, entity.DurationComment);
SaveComment(vacancyId, ReferralCommentCodeName.EmployerDescriptionComment, entity.EmployerDescriptionComment);
SaveComment(vacancyId, ReferralCommentCodeName.EmployerWebsiteUrlComment, entity.EmployerWebsiteUrlComment);
SaveComment(vacancyId, ReferralCommentCodeName.FirstQuestionComment, entity.FirstQuestionComment);
SaveComment(vacancyId, ReferralCommentCodeName.SecondQuestionComment, entity.SecondQuestionComment);
SaveComment(vacancyId, ReferralCommentCodeName.FrameworkCodeNameComment, entity.FrameworkCodeNameComment);
SaveComment(vacancyId, ReferralCommentCodeName.FutureProspectsComment, entity.FutureProspectsComment);
SaveComment(vacancyId, ReferralCommentCodeName.LongDescriptionComment, entity.LongDescriptionComment);
SaveComment(vacancyId, ReferralCommentCodeName.NumberOfPositionsComment, entity.NumberOfPositionsComment);
SaveComment(vacancyId, ReferralCommentCodeName.OfflineApplicationInstructionsComment, entity.OfflineApplicationInstructionsComment);
SaveComment(vacancyId, ReferralCommentCodeName.OfflineApplicationUrlComment, entity.OfflineApplicationUrlComment);
SaveComment(vacancyId, ReferralCommentCodeName.PersonalQualitiesComment, entity.PersonalQualitiesComment);
SaveComment(vacancyId, ReferralCommentCodeName.PossibleStartDateComment, entity.PossibleStartDateComment);
SaveComment(vacancyId, ReferralCommentCodeName.SectorCodeNameComment, entity.SectorCodeNameComment); // Or needs a new one?
SaveComment(vacancyId, ReferralCommentCodeName.ShortDescriptionComment, entity.ShortDescriptionComment);
SaveComment(vacancyId, ReferralCommentCodeName.StandardIdComment, entity.StandardIdComment);
SaveComment(vacancyId, ReferralCommentCodeName.ThingsToConsiderComment, entity.ThingsToConsiderComment);
SaveComment(vacancyId, ReferralCommentCodeName.TrainingProvidedComment, entity.TrainingProvidedComment);
SaveComment(vacancyId, ReferralCommentCodeName.WageComment, entity.WageComment);
SaveComment(vacancyId, ReferralCommentCodeName.WorkingWeekComment, entity.WorkingWeekComment);
SaveComment(vacancyId, ReferralCommentCodeName.LocationAddressesComment, entity.LocationAddressesComment);
SaveComment(vacancyId, ReferralCommentCodeName.AdditionalLocationInformationComment, entity.AdditionalLocationInformationComment);
SaveComment(vacancyId, ReferralCommentCodeName.AnonymousEmployerReasonComment, entity.AnonymousEmployerReasonComment);
SaveComment(vacancyId, ReferralCommentCodeName.AnonymousAboutTheEmployerComment, entity.AnonymousAboutTheEmployerComment);
SaveComment(vacancyId, ReferralCommentCodeName.AnonymousEmployerDescriptionComment, entity.AnonymousEmployerDescriptionComment);
}
public DomainVacancy Create(DomainVacancy entity)
{
_logger.Debug("Calling database to save apprenticeship vacancy with id={0}", entity.VacancyId);
_logger.Info(
$"[{entity.VacancyGuid}] Calling database to create the following domain vacancy: {JsonConvert.SerializeObject(entity, Formatting.Indented, new JsonSerializerSettings { ContractResolver = new ExcludeLiveClosingDateResolver() })}");
UpdateEntityTimestamps(entity);
var dbVacancy = _mapper.Map <DomainVacancy, Vacancy>(entity);
PopulateIds(entity, dbVacancy);
_logger.Info(
$"[{entity.VacancyGuid}] Calling database to create the following database vacancy: {JsonConvert.SerializeObject(dbVacancy, Formatting.Indented, new JsonSerializerSettings { ContractResolver = new ExcludeLiveClosingDateResolver() })}");
dbVacancy.VacancyId = (int)_getOpenConnection.Insert(dbVacancy);
SaveTextFieldsFor(dbVacancy.VacancyId, entity);
SaveAdditionalQuestionsFor(dbVacancy.VacancyId, entity);
CreateVacancyHistoryRow(dbVacancy.VacancyId, _currentUserService.CurrentUserName, VacancyHistoryEventType.StatusChange,
(int)entity.Status, StatusChangeText);
_logger.Debug("Saved apprenticeship vacancy to database with id={0}", entity.VacancyId);
return(_mapper.Map <Vacancy, DomainVacancy>(dbVacancy));
}
private void SaveTextFieldsFor(int vacancyId, DomainVacancy entity)
{
SaveTextField(vacancyId, TextFieldCodeName.TrainingProvided, entity.TrainingProvided);
SaveTextField(vacancyId, TextFieldCodeName.DesiredQualifications, entity.DesiredQualifications);
SaveTextField(vacancyId, TextFieldCodeName.DesiredSkills, entity.DesiredSkills);
SaveTextField(vacancyId, TextFieldCodeName.PersonalQualities, entity.PersonalQualities);
SaveTextField(vacancyId, TextFieldCodeName.ThingsToConsider, entity.ThingsToConsider);
SaveTextField(vacancyId, TextFieldCodeName.FutureProspects, entity.FutureProspects);
}
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 UpdateEntityTimestamps(DomainVacancy entity)
{
// determine whether this is a "new" entity being saved for the first time
if (entity.CreatedDateTime == _dateTimeService.MinValue)
{
entity.CreatedDateTime = _dateTimeService.UtcNow;
entity.UpdatedDateTime = null;
}
else
{
entity.UpdatedDateTime = _dateTimeService.UtcNow;
}
}
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 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 PatchTrainingType(DomainVacancy result)
{
if (result?.TrainingType != TrainingType.Unknown)
{
return;
}
if (!string.IsNullOrWhiteSpace(result.SectorCodeName))
{
result.TrainingType = TrainingType.Sectors;
}
else if (result?.StandardId != null)
{
result.TrainingType = TrainingType.Standards;
}
else if (!string.IsNullOrWhiteSpace(result.FrameworkCodeName))
{
result.TrainingType = TrainingType.Frameworks;
}
}
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 string MapExpectedDuration(DomainVacancy vacancy)
{
return(!vacancy.Duration.HasValue
? vacancy.ExpectedDuration
: new Duration(vacancy.DurationType, vacancy.Duration).GetDisplayText());
}
private void SaveAdditionalQuestionsFor(int vacancyId, DomainVacancy entity)
{
UpsertAdditionalQuestion(vacancyId, 1, entity.FirstQuestion);
UpsertAdditionalQuestion(vacancyId, 2, entity.SecondQuestion);
}
