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); } } }