public void Delete(Guid applicationGuid)
{
var destinationApplicationIds = _destinationApplicationRepository.GetApplicationIdsByGuid(new[] { applicationGuid });
if (destinationApplicationIds.ContainsKey(applicationGuid))
{
var applicationId = destinationApplicationIds[applicationGuid].ApplicationId;
_targetDatabase.MutatingQuery <object>("DELETE FROM SubVacancy WHERE AllocatedApplicationId = @applicationId", new { applicationId });
_targetDatabase.MutatingQuery <object>("DELETE FROM SchoolAttended WHERE ApplicationId = @applicationId", new { applicationId });
_targetDatabase.MutatingQuery <object>("DELETE FROM ApplicationHistory WHERE ApplicationId = @applicationId", new { applicationId });
_targetDatabase.MutatingQuery <object>("DELETE FROM Application WHERE ApplicationId = @applicationId", new { applicationId });
}
}
public void Delete(int vacancyId)
{
_getOpenConnection.MutatingQuery <int>(@"
UPDATE dbo.Vacancy
SET VacancyStatusId = @VacancyStatus
WHERE VacancyId = @VacancyId",
new
{
VacancyId = vacancyId,
VacancyStatus = VacancyStatus.Deleted
});
}
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 void ResurrectVacancyOwnerRelationship(int providerSiteId, int employerId)
{
const string sql = @"
UPDATE dbo.VacancyOwnerRelationship SET StatusTypeId = @StatusTypeId
WHERE ProviderSiteID = @ProviderSiteId
AND EmployerId = @EmployerId";
var sqlParams = new
{
ProviderSiteId = providerSiteId,
EmployerId = employerId,
StatusTypeId = VacancyOwnerRelationshipStatusTypes.Live
};
_getOpenConnection.MutatingQuery <object>(sql, sqlParams);
}
public void DeleteProviderSiteRelationship(int providerSiteRelationshipId)
{
_getOpenConnection.MutatingQuery <object>("DELETE FROM ProviderSiteFramework WHERE ProviderSiteRelationshipID = @providerSiteRelationshipId", new { providerSiteRelationshipId });
_getOpenConnection.MutatingQuery <object>("DELETE FROM ProviderSiteLocalAuthority WHERE ProviderSiteRelationshipID = @providerSiteRelationshipId", new { providerSiteRelationshipId });
_getOpenConnection.MutatingQuery <object>("DELETE FROM ProviderSiteRelationship WHERE ProviderSiteRelationshipID = @providerSiteRelationshipId", new { providerSiteRelationshipId });
}
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);
}
protected void UpdateLastSyncVersion()
{
_targetDatabase.MutatingQuery <int>($"UPDATE Sync.SyncParams SET LastSyncVersion = @lastSyncVersion", new { lastSyncVersion = _nextSyncVersion });
}