private void Update(ApplicationWithHistory applicationWithHistory)
{
//update existing application
_targetDatabase.UpdateSingle(applicationWithHistory.ApplicationWithSubVacancy.Application);
//Insert new application history records
foreach (var applicationHistory in applicationWithHistory.ApplicationHistory.Where(a => a.ApplicationHistoryId == 0))
{
_targetDatabase.Insert(applicationHistory);
}
//Update existing application history records
foreach (var applicationHistory in applicationWithHistory.ApplicationHistory.Where(a => a.ApplicationHistoryId != 0))
{
_targetDatabase.UpdateSingle(applicationHistory);
}
var schoolAttended = applicationWithHistory.ApplicationWithSubVacancy.SchoolAttended;
if (schoolAttended != null)
{
if (schoolAttended.SchoolAttendedId == 0)
{
//Insert school attended if not already present
_targetDatabase.Insert(schoolAttended);
}
else
{
//Otherwise update
_targetDatabase.UpdateSingle(schoolAttended);
}
}
}
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));
}
public ApiUser Update(ApiUser apiUser)
{
var sql = "SELECT * FROM ExternalSystemPermission esp WHERE Username = @ExternalSystemId";
var externalSystemPermission = _getOpenConnection.Query <ExternalSystemPermission>(sql, new { apiUser.ExternalSystemId }).Single();
externalSystemPermission.UserParameters = string.Join(",", apiUser.AuthorisedApiEndpoints.Select(ae => ApiEndpointsCodeMap[ae]));
_getOpenConnection.UpdateSingle(externalSystemPermission);
return(GetApiUser(apiUser.ExternalSystemId));
}
public List <VacancyLocation> Update(List <VacancyLocation> locationAddresses)
{
foreach (var vacancyLocationAddress in locationAddresses)
{
var vacancyLocation = _mapper.Map <VacancyLocation, Entities.VacancyLocation>(vacancyLocationAddress);
PopulateLocalAuthorityId(vacancyLocationAddress, vacancyLocation);
PopulateCountyId(vacancyLocationAddress, vacancyLocation);
_getOpenConnection.UpdateSingle(vacancyLocation);
}
return(locationAddresses);
}
public ProviderSite Update(ProviderSite providerSite)
{
_logger.Debug("Saving provider site with ProviderSiteId={0}", providerSite.ProviderSiteId);
var dbProviderSite = MapProviderSite(providerSite);
if (!_getOpenConnection.UpdateSingle(dbProviderSite))
{
throw new Exception($"Failed to save provider site with ProviderSiteId={providerSite.ProviderSiteId}");
}
foreach (var providerSiteRelationship in providerSite.ProviderSiteRelationships)
{
var dbProviderSiteRelationship = _mapper.Map <ProviderSiteRelationship, Entities.ProviderSiteRelationship>(providerSiteRelationship);
if (!_getOpenConnection.UpdateSingle(dbProviderSiteRelationship))
{
throw new Exception($"Failed to save provider site relationship with ProviderSiteRelationshipId={providerSiteRelationship.ProviderSiteRelationshipId}");
}
}
return(GetById(providerSite.ProviderSiteId));
}
public ProviderUser Update(ProviderUser providerUser)
{
_logger.Debug("Updating provider user with ProviderUserId={0}", providerUser.ProviderUserId);
UpdateEntityTimestamps(providerUser);
var dbProviderUser = MapProviderUser(providerUser);
_getOpenConnection.UpdateSingle(dbProviderUser);
_logger.Debug("Updated provider user with ProviderUserId={0}", providerUser.ProviderUserId);
return(MapProviderUser(dbProviderUser));
}
private void Update(CandidateWithHistory candidateWithHistory)
{
//update existing person
_targetDatabase.UpdateSingle(candidateWithHistory.CandidatePerson.Person);
//update existing candidate
_targetDatabase.UpdateSingle(candidateWithHistory.CandidatePerson.Candidate);
//Insert new candidate history records
foreach (var candidateHistory in candidateWithHistory.CandidateHistory.Where(a => a.CandidateHistoryId == 0))
{
_targetDatabase.Insert(candidateHistory);
}
var schoolAttended = candidateWithHistory.CandidatePerson.SchoolAttended;
if (schoolAttended != null)
{
if (schoolAttended.SchoolAttendedId == 0)
{
//Insert school attended if not already present
_targetDatabase.Insert(schoolAttended);
}
else
{
//Otherwise update
_targetDatabase.UpdateSingle(schoolAttended);
}
}
//Update existing candidate history records
foreach (var candidateHistory in candidateWithHistory.CandidateHistory.Where(a => a.CandidateHistoryId != 0))
{
_targetDatabase.UpdateSingle(candidateHistory);
}
}
/// <summary>
/// UpdateEntityTimeStamps
/// Save to Collection
/// Return Saved object - this should be returned from the collection.save operation.
/// </summary>
/// <param name="provider"></param>
/// <returns></returns>
public Provider Update(Provider provider)
{
_logger.Debug("Saving provider with Ukprn={0}", provider.Ukprn);
var dbProvider = MapProvider(provider);
// TODO: SQL: AG: note that we are not attempting to insert a new Provider, we always update (temporary).
if (!_getOpenConnection.UpdateSingle(dbProvider))
{
throw new Exception($"Failed to save provider with Ukprn={provider.Ukprn}");
}
return(MapProvider(dbProvider));
}
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 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);
}
