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 BulkUpsert(IList <CandidateWithHistory> candidatesWithHistory, IDictionary <Guid, CandidateSummary> candidateSummaries)
{
//Have to do these one at a time as need to get the id for the inserted person records
foreach (var candidateWithHistory in candidatesWithHistory.Where(c => c.CandidatePerson.Person.PersonId == 0))
{
//Insert any new person records to match with candidate records
var personId = (int)_targetDatabase.Insert(candidateWithHistory.CandidatePerson.Person);
candidateWithHistory.CandidatePerson.Candidate.PersonId = personId;
}
//Update any existing person records
_genericSyncRespository.BulkUpdate(_personTable, candidatesWithHistory.Where(c => c.CandidatePerson.Person.PersonId != 0).Select(c => _candidateMappers.MapPersonDictionary(c.CandidatePerson.Person)));
//Bulk insert any candidates with valid ids that are not already in the database
_genericSyncRespository.BulkInsert(_candidateTable, candidatesWithHistory.Where(c => c.CandidatePerson.Candidate.CandidateId != 0 && !candidateSummaries.ContainsKey(c.CandidatePerson.Candidate.CandidateGuid)).Select(c => _candidateMappers.MapCandidateDictionary(c.CandidatePerson.Candidate)));
//Now insert any remaining candidates one at a time
foreach (var candidateWithHistory in candidatesWithHistory.Where(c => c.CandidatePerson.Candidate.CandidateId == 0))
{
//Ensure school attended and candidate histories have the correct candidate id
var candidateId = (int)_targetDatabase.Insert(candidateWithHistory.CandidatePerson.Candidate);
if (candidateWithHistory.CandidatePerson.SchoolAttended != null)
{
candidateWithHistory.CandidatePerson.SchoolAttended.CandidateId = candidateId;
}
foreach (var candidateHistory in candidateWithHistory.CandidateHistory)
{
candidateHistory.CandidateId = candidateId;
}
}
//Finally, update existing candidates
_genericSyncRespository.BulkUpdate(_candidateTable, candidatesWithHistory.Where(c => c.CandidatePerson.Candidate.CandidateId != 0 && candidateSummaries.ContainsKey(c.CandidatePerson.Candidate.CandidateGuid)).Select(c => _candidateMappers.MapCandidateDictionary(c.CandidatePerson.Candidate)));
//Insert new schools attended
var newSchoolsAttended = candidatesWithHistory.Where(a => a.CandidatePerson.SchoolAttended != null && a.CandidatePerson.SchoolAttended.SchoolAttendedId == 0).Select(a => a.CandidatePerson.SchoolAttended);
_genericSyncRespository.BulkInsert(_schoolsAttendedTable, newSchoolsAttended.Select(sa => sa.MapSchoolAttendedDictionary()));
//Update existing schools attended
var existingSchoolsAttended = candidatesWithHistory.Where(a => a.CandidatePerson.SchoolAttended != null && a.CandidatePerson.SchoolAttended.SchoolAttendedId != 0).Select(a => a.CandidatePerson.SchoolAttended);
_genericSyncRespository.BulkUpdate(_schoolsAttendedTable, existingSchoolsAttended.Select(sa => sa.MapSchoolAttendedDictionary()));
//Insert new candidate history records
var newCandidateHistories = candidatesWithHistory.SelectMany(a => a.CandidateHistory).Where(a => a.CandidateHistoryId == 0);
_genericSyncRespository.BulkInsert(_candidateHistoryTable, newCandidateHistories.Select(ah => ah.MapCandidateHistoryDictionary()));
//Update existing candidate history records
var existingCandidateHistories = candidatesWithHistory.SelectMany(a => a.CandidateHistory).Where(a => a.CandidateHistoryId != 0);
_genericSyncRespository.BulkUpdate(_candidateHistoryTable, existingCandidateHistories.Select(ah => ah.MapCandidateHistoryDictionary()));
}
public Provider Create(Provider provider)
{
_logger.Info("Creating provider with Ukprn={0}", provider.Ukprn);
var dbProvider = MapProvider(provider);
_getOpenConnection.Insert(dbProvider);
return(GetByUkprn(provider.Ukprn));
}
private void Create(ApplicationWithHistory applicationWithHistory)
{
//Insert existing application
var applicationId = (int)_targetDatabase.Insert(applicationWithHistory.ApplicationWithSubVacancy.Application);
//Insert new application history records
foreach (var applicationHistory in applicationWithHistory.ApplicationHistory)
{
applicationHistory.ApplicationId = applicationId;
_targetDatabase.Insert(applicationHistory);
}
var schoolAttended = applicationWithHistory.ApplicationWithSubVacancy.SchoolAttended;
if (schoolAttended != null)
{
//Insert school attended
schoolAttended.ApplicationId = applicationId;
_targetDatabase.Insert(schoolAttended);
}
}
public ProviderSite Create(ProviderSite providerSite)
{
_logger.Info("Creating provider site with EDSURN={0}", providerSite.EdsUrn);
var dbProviderSite = MapProviderSite(providerSite);
_getOpenConnection.Insert(dbProviderSite);
var newProviderSite = GetByEdsUrn(providerSite.EdsUrn);
var providerSiteId = newProviderSite.ProviderSiteId;
foreach (var providerSiteRelationship in providerSite.ProviderSiteRelationships)
{
var dbProviderSiteRelationship = _mapper.Map <ProviderSiteRelationship, Entities.ProviderSiteRelationship>(providerSiteRelationship);
dbProviderSiteRelationship.ProviderSiteId = providerSiteId;
_getOpenConnection.Insert(dbProviderSiteRelationship);
}
return(GetById(providerSiteId));
}
private void Create(CandidateWithHistory candidateWithHistory)
{
//Insert new person record to match with candidate record
var personId = (int)_targetDatabase.Insert(candidateWithHistory.CandidatePerson.Person);
candidateWithHistory.CandidatePerson.Candidate.PersonId = personId;
//Insert candidate records
var candidateId = (int)_targetDatabase.Insert(candidateWithHistory.CandidatePerson.Candidate);
var schoolAttended = candidateWithHistory.CandidatePerson.SchoolAttended;
if (schoolAttended != null)
{
//Insert school attended
schoolAttended.CandidateId = candidateId;
_targetDatabase.Insert(schoolAttended);
}
//Insert new candidate history records
foreach (var candidateHistory in candidateWithHistory.CandidateHistory)
{
candidateHistory.CandidateId = candidateId;
_targetDatabase.Insert(candidateHistory);
}
}
public List <VacancyLocation> Create(List <VacancyLocation> locationAddresses)
{
foreach (var vacancyLocationAddress in locationAddresses)
{
var vacancyLocation = _mapper.Map <VacancyLocation, Entities.VacancyLocation>(vacancyLocationAddress);
PopulateLocalAuthorityId(vacancyLocationAddress, vacancyLocation);
PopulateCountyId(vacancyLocationAddress, vacancyLocation);
_getOpenConnection.Insert(vacancyLocation);
}
return(locationAddresses);
}
public ProviderUser Create(ProviderUser providerUser)
{
_logger.Debug("Creating provider user with ProviderUserGuid={0}", providerUser.ProviderUserGuid);
UpdateEntityTimestamps(providerUser);
var dbProviderUser = MapProviderUser(providerUser);
dbProviderUser.ProviderUserId = (int)_getOpenConnection.Insert(dbProviderUser);
_logger.Debug("Created provider user with ProviderUserGuid={0} and ProviderId={1}",
providerUser.ProviderUserGuid, dbProviderUser.ProviderUserId);
return(MapProviderUser(dbProviderUser));
}
public DomainEmployer Save(DomainEmployer employer)
{
var dbEmployer = _mapper.Map <DomainEmployer, Employer>(employer);
PopulateCountyId(employer, dbEmployer);
if (dbEmployer.EmployerId == 0)
{
dbEmployer.EmployerStatusTypeId = 1;
dbEmployer.EmployerId = (int)_getOpenConnection.Insert(dbEmployer);
}
else
{
const string sql = "SELECT * FROM dbo.Employer WHERE EmployerId = @EmployerId";
var sqlParams = new
{
dbEmployer.EmployerId
};
var existingEmployer = _getOpenConnection.Query <Employer>(sql, sqlParams).Single();
dbEmployer.CountyId = existingEmployer.CountyId;
dbEmployer.LocalAuthorityId = existingEmployer.LocalAuthorityId;
dbEmployer.GeocodeEasting = existingEmployer.GeocodeEasting;
dbEmployer.GeocodeNorthing = existingEmployer.GeocodeNorthing;
dbEmployer.PrimaryContact = existingEmployer.PrimaryContact;
dbEmployer.NumberofEmployeesAtSite = existingEmployer.NumberofEmployeesAtSite;
dbEmployer.NumberOfEmployeesInGroup = existingEmployer.NumberOfEmployeesInGroup;
dbEmployer.OwnerOrgnistaion = existingEmployer.OwnerOrgnistaion;
dbEmployer.CompanyRegistrationNumber = existingEmployer.CompanyRegistrationNumber;
dbEmployer.TotalVacanciesPosted = existingEmployer.TotalVacanciesPosted;
dbEmployer.BeingSupportedBy = existingEmployer.BeingSupportedBy;
dbEmployer.LockedForSupportUntil = existingEmployer.LockedForSupportUntil;
dbEmployer.DisableAllowed = existingEmployer.DisableAllowed;
dbEmployer.TrackingAllowed = existingEmployer.TrackingAllowed;
_getOpenConnection.UpdateSingle(dbEmployer);
}
return(GetById(dbEmployer.EmployerId));
}
public AgencyUser Save(AgencyUser entity)
{
_logger.Debug("Called SQL DB to save AgencyUser with AgencyUserId={0}", entity.AgencyUserId);
UpdateEntityTimestamps(entity);
var dbEntity = _mapper.Map <AgencyUser, Entities.AgencyUser>(entity);
if (dbEntity.AgencyUserGuid == Guid.Empty)
{
dbEntity.AgencyUserGuid = Guid.NewGuid();
}
// TODO: SQL: AG: consider generalising (and testing) SqlException handling below.
try
{
var result = (int)_getOpenConnection.Insert(dbEntity);
dbEntity.AgencyUserId = result;
}
catch (SqlException e)
when(e.Number == 2601)
{
if (!_getOpenConnection.UpdateSingle(dbEntity))
{
throw new Exception("Failed to update record after failed insert", e);
}
}
_logger.Debug("Saved AgencyUser with AgencyUserId={0}", entity.AgencyUserId);
var endResult = _mapper.Map <Entities.AgencyUser, AgencyUser>(dbEntity);
endResult.Role = entity.Role;
endResult.RegionalTeam = entity.RegionalTeam;
return(endResult);
}
public VacancyOwnerRelationship Save(VacancyOwnerRelationship vacancyOwnerRelationship)
{
var dbVacancyOwnerRelationship = _mapper.Map <VacancyOwnerRelationship, Entities.VacancyOwnerRelationship>(vacancyOwnerRelationship);
dbVacancyOwnerRelationship.StatusTypeId = (int)VacancyOwnerRelationshipStatusTypes.Live;
dbVacancyOwnerRelationship.EditedInRaa = true;
if (dbVacancyOwnerRelationship.VacancyOwnerRelationshipId == 0)
{
dbVacancyOwnerRelationship.VacancyOwnerRelationshipId = (int)_getOpenConnection.Insert(dbVacancyOwnerRelationship);
}
else
{
const string sql = @"
SELECT * FROM dbo.VacancyOwnerRelationship
WHERE VacancyOwnerRelationshipId = @VacancyOwnerRelationshipId
AND StatusTypeId = @StatusTypeId";
var sqlParams = new
{
dbVacancyOwnerRelationship.VacancyOwnerRelationshipId,
dbVacancyOwnerRelationship.StatusTypeId
};
var existingVacancyOwnerRelationship = _getOpenConnection.Query <Entities.VacancyOwnerRelationship>(sql, sqlParams).Single();
dbVacancyOwnerRelationship.ContractHolderIsEmployer = existingVacancyOwnerRelationship.ContractHolderIsEmployer;
dbVacancyOwnerRelationship.ManagerIsEmployer = existingVacancyOwnerRelationship.ManagerIsEmployer;
dbVacancyOwnerRelationship.Notes = existingVacancyOwnerRelationship.Notes;
dbVacancyOwnerRelationship.EmployerLogoAttachmentId = existingVacancyOwnerRelationship.EmployerLogoAttachmentId;
dbVacancyOwnerRelationship.NationWideAllowed = existingVacancyOwnerRelationship.NationWideAllowed;
_getOpenConnection.UpdateSingle(dbVacancyOwnerRelationship);
}
return(GetByIds(new[] { dbVacancyOwnerRelationship.VacancyOwnerRelationshipId }).Single());
}
public ApiUser Create(ApiUser apiUser)
{
var apiConfiguration = _configurationService.Get <ApiConfiguration>();
var externalSystem = GetExternalSystem(apiUser.CompanyId);
var externalSystemPermission = externalSystem.ExternalSystemPermission;
externalSystemPermission.Username = apiUser.ExternalSystemId == Guid.Empty ? Guid.NewGuid() : apiUser.ExternalSystemId;
externalSystemPermission.Password = new byte[64];
externalSystemPermission.UserParameters = string.Join(",", apiUser.AuthorisedApiEndpoints.Select(ae => ApiEndpointsCodeMap[ae]));
externalSystemPermission.Salt = apiConfiguration.Salt;
_getOpenConnection.Insert(externalSystemPermission);
var password = string.IsNullOrEmpty(apiUser.Password) ? GetApiPassword() : apiUser.Password;
_getOpenConnection.MutatingQuery <ExternalSystemPermission>(SetPasswordSql, new { password, externalSystemPermission.Username });
var createdApiUser = GetApiUser(externalSystemPermission.Username);
createdApiUser.Password = password;
return(createdApiUser);
}
public ApprenticeshipVacancy Save(ApprenticeshipVacancy entity)
{
_logger.Debug("Calling database to save apprenticeship vacancy with id={0}", entity.EntityId);
UpdateEntityTimestamps(entity);
// TODO: Map from ApprenticeshipVacancy to Apprenticeship ??
var dbVacancy = _mapper.Map <ApprenticeshipVacancy, Vacancy.Vacancy>(entity);
dbVacancy.VacancyLocationTypeCode = "S"; // TODO: Can't get this right unless / until added to ApprenticeshipVacancy or exclude from updates
// TODO: This should be the other way around (to avoid a race condition)
// and be in a single call to the database (to avoid a double latency hit)
// This should be done as a single method in _getOpenConnection
try
{
_getOpenConnection.Insert(dbVacancy);
}
catch (Exception ex)
{
// TODO: Detect key violation
if (!_getOpenConnection.UpdateSingle(dbVacancy))
{
throw new Exception("Failed to update record after failed insert", ex);
}
if (entity.LocationAddresses != null)
{
_getOpenConnection.MutatingQuery <int>(@"
-- TODO: Could be optimised. Locking may possibly be an issue
-- TODO: Should possibly split address into separate repo method
DELETE Address.PostalAddress
WHERE PostalAddressId IN (
SELECT PostalAddressId
FROM Vacancy.VacancyLocation
WHERE VacancyId = @VacancyId
)
DELETE Vacancy.VacancyLocation
FROM Vacancy.VacancyLocation
WHERE VacancyId = @VacancyId
");
}
}
if (entity.LocationAddresses != null) // TODO: Split into separate repository method
{
// TODO: Optimisation - insert several in one SQL round-trip
foreach (var location in entity.LocationAddresses)
{
var dbLocation = new Vacancy.VacancyLocation()
{
VacancyId = dbVacancy.VacancyId,
DirectApplicationUrl = "TODO",
NumberOfPositions = location.NumberOfPositions
};
var dbAddress = _mapper.Map <Domain.Entities.Locations.Address, Address.PostalAddress>(location.Address);
dbLocation.PostalAddressId = (int)_getOpenConnection.Insert(dbAddress);
_getOpenConnection.Insert(dbLocation);
}
}
_logger.Debug("Saved apprenticeship vacancy with to database with id={0}", entity.EntityId);
// TODO: Mongo used to map dbVacancy back to entity, not sure what the point in that is.
return(entity);
}
public void BulkUpsert(IList <ApplicationWithHistory> applicationsWithHistory, IDictionary <Guid, int> applicationIds)
{
//Bulk insert any applications with valid ids that are not already in the database
_genericSyncRespository.BulkInsert(_applicationTable, applicationsWithHistory.Where(a => a.ApplicationWithSubVacancy.Application.ApplicationId != 0 && !applicationIds.ContainsKey(a.ApplicationWithSubVacancy.Application.ApplicationGuid)).Select(a => _applicationMappers.MapApplicationDictionary(a.ApplicationWithSubVacancy.Application)));
//Now insert any remaining applications one at a time
foreach (var applicationWithHistory in applicationsWithHistory.Where(a => a.ApplicationWithSubVacancy.Application.ApplicationId == 0))
{
//Ensure schools attended and application histories have the correct application id
var applicationId = (int)_targetDatabase.Insert(applicationWithHistory.ApplicationWithSubVacancy.Application);
if (applicationWithHistory.ApplicationWithSubVacancy.SchoolAttended != null)
{
applicationWithHistory.ApplicationWithSubVacancy.SchoolAttended.ApplicationId = applicationId;
}
foreach (var applicationHistory in applicationWithHistory.ApplicationHistory)
{
applicationHistory.ApplicationId = applicationId;
}
}
//Finally, update existing applications
_genericSyncRespository.BulkUpdate(_applicationTable, applicationsWithHistory.Where(a => a.ApplicationWithSubVacancy.Application.ApplicationId != 0 && applicationIds.ContainsKey(a.ApplicationWithSubVacancy.Application.ApplicationGuid)).Select(a => _applicationMappers.MapApplicationDictionary(a.ApplicationWithSubVacancy.Application)));
//Insert new application history records
var newApplicationHistories = applicationsWithHistory.SelectMany(a => a.ApplicationHistory).Where(a => a.ApplicationHistoryId == 0);
_genericSyncRespository.BulkInsert(_applicationHistoryTable, newApplicationHistories.Select(ah => ah.MapApplicationHistoryDictionary()));
//Update existing application history records
var existingApplicationHistories = applicationsWithHistory.SelectMany(a => a.ApplicationHistory).Where(a => a.ApplicationHistoryId != 0);
_genericSyncRespository.BulkUpdate(_applicationHistoryTable, existingApplicationHistories.Select(ah => ah.MapApplicationHistoryDictionary()));
//Insert new schools attended
var newSchoolsAttended = applicationsWithHistory.Where(a => a.ApplicationWithSubVacancy.SchoolAttended != null && a.ApplicationWithSubVacancy.SchoolAttended.SchoolAttendedId == 0).Select(a => a.ApplicationWithSubVacancy.SchoolAttended);
_genericSyncRespository.BulkInsert(_schoolsAttendedTable, newSchoolsAttended.Select(sa => sa.MapSchoolAttendedDictionary()));
//Update existing schools attended
var existingSchoolsAttended = applicationsWithHistory.Where(a => a.ApplicationWithSubVacancy.SchoolAttended != null && a.ApplicationWithSubVacancy.SchoolAttended.SchoolAttendedId != 0).Select(a => a.ApplicationWithSubVacancy.SchoolAttended);
_genericSyncRespository.BulkUpdate(_schoolsAttendedTable, existingSchoolsAttended.Select(sa => sa.MapSchoolAttendedDictionary()));
var subVacancies = applicationsWithHistory.Where(a => a.ApplicationWithSubVacancy.SubVacancy != null).Select(a => a.ApplicationWithSubVacancy.SubVacancy).ToList();
var existingSubVacancies = _destinationSubVacancyRepository.GetApplicationSummariesByIds(subVacancies.Select(sv => sv.AllocatedApplicationId));
//Insert new sub vacancies
_genericSyncRespository.BulkInsert(_subVacanciesTable, subVacancies.Where(sv => !existingSubVacancies.ContainsKey(sv.AllocatedApplicationId)).Select(sv => _applicationMappers.MapSubVacancyDictionary(sv)));
//Update existing sub vacancies
_genericSyncRespository.BulkUpdate(_subVacanciesTable, subVacancies.Where(sv => existingSubVacancies.ContainsKey(sv.AllocatedApplicationId)).Select(sv => _applicationMappers.MapSubVacancyDictionary(sv)));
//Patch in any changes to the applications in Mongo based on what was discovered through processing
foreach (var applicationWithSubVacancy in applicationsWithHistory.Select(a => a.ApplicationWithSubVacancy))
{
if (applicationWithSubVacancy.UpdateNotes)
{
_updateVacancyApplicationsRepository.UpdateApplicationNotes(applicationWithSubVacancy.Application.ApplicationGuid, applicationWithSubVacancy.Application.AllocatedTo);
}
if (applicationWithSubVacancy.UpdateStatusTo.HasValue)
{
_updateVacancyApplicationsRepository.UpdateApplicationStatus(applicationWithSubVacancy.Application.ApplicationGuid, applicationWithSubVacancy.UpdateStatusTo.Value);
}
}
}
