// column per progress tag, or row per tag?
// column +ve : tags can be different data type
// row +ve : unlimited tags, no schema change for extra tags unless require different data type
// -ve : tags all same data type, or muliple columns
// https://stackoverflow.com/questions/3967372/sql-server-how-to-constrain-a-table-to-contain-a-single-row
public async Task <long?> Get_AddEpaToApprenticeships_LastSubmissionEventId()
{
_logger.Debug("Getting last processed (by AddEpaToApprenticeships) SubmissionEventId");
return(await WithConnection(async connection => await connection.ExecuteScalarAsync <long?>(
"SELECT [AddEpa_LastSubmissionEventId] FROM [dbo].[JobProgress]",
commandType: CommandType.Text)));
}
public async Task <string> GetLatestEpaOrgId()
{
_logger.Debug("Getting latest EPAOrgId");
// this makes assumptions about the format of EPAOrgId (which are currently correct)
return(await WithConnection(async connection => await connection.QuerySingleOrDefaultAsync <string>(
"SELECT MAX([EPAOrgId]) FROM [dbo].[AssessmentOrganisation]",
commandType: CommandType.Text)));
}
public async Task <long> CreateApprenticeship(Apprenticeship apprenticeship)
{
_logger.Debug($"Creating apprenticeship - {apprenticeship.FirstName} {apprenticeship.LastName}", accountId: apprenticeship.EmployerAccountId, providerId: apprenticeship.ProviderId, commitmentId: apprenticeship.CommitmentId);
return(await WithTransaction(async (connection, trans) =>
{
var apprenticeshipId = await _apprenticeshipTransactions.CreateApprenticeship(connection, trans, apprenticeship);
return apprenticeshipId;
}));
}
public async Task UpdateApprenticeship(Apprenticeship apprenticeship, Caller caller)
{
_logger.Debug($"Updating apprenticeship {apprenticeship.Id}", accountId: apprenticeship.EmployerAccountId, providerId: apprenticeship.ProviderId, commitmentId: apprenticeship.CommitmentId, apprenticeshipId: apprenticeship.Id);
await WithTransaction(async (connection, trans) =>
{
var returnCode = await _apprenticeshipTransactions.UpdateApprenticeship(connection, trans, apprenticeship, caller);
return(returnCode);
});
}
public async Task <long> Create(Commitment commitment)
{
_logger.Debug($"Creating commitment with ref: {commitment.Reference}", accountId: commitment.EmployerAccountId, providerId: commitment.ProviderId);
return(await WithConnection(async connection =>
{
var parameters = new DynamicParameters();
parameters.Add("@reference", commitment.Reference, DbType.String);
parameters.Add("@transferSenderId", commitment.TransferSenderId, DbType.Int64);
parameters.Add("@transferSenderName", commitment.TransferSenderName, DbType.String);
parameters.Add("@legalEntityId", commitment.LegalEntityId, DbType.String);
parameters.Add("@legalEntityName", commitment.LegalEntityName, DbType.String);
parameters.Add("@LegalEntityAddress", commitment.LegalEntityAddress, DbType.String);
parameters.Add("@legalEntityOrganisationType", commitment.LegalEntityOrganisationType, DbType.Int16);
parameters.Add("@accountLegalEntityPublicHashedId", commitment.AccountLegalEntityPublicHashedId, DbType.String);
parameters.Add("@accountId", commitment.EmployerAccountId, DbType.Int64);
parameters.Add("@providerId", commitment.ProviderId, DbType.Int64);
parameters.Add("@providerName", commitment.ProviderName, DbType.String);
parameters.Add("@commitmentStatus", commitment.CommitmentStatus, DbType.Int16);
parameters.Add("@editStatus", commitment.EditStatus, DbType.Int16);
parameters.Add("@id", dbType: DbType.Int64, direction: ParameterDirection.Output);
parameters.Add("@createdOn", _currentDateTime.Now, DbType.DateTime);
parameters.Add("@lastAction", commitment.LastAction, DbType.Int16);
parameters.Add("@lastUpdateByEmployerName", commitment.LastUpdatedByEmployerName, DbType.String);
parameters.Add("@lastUpdateByEmployerEmail", commitment.LastUpdatedByEmployerEmail, DbType.String);
parameters.Add("@Originator", commitment.Originator, DbType.Byte);
using (var trans = connection.BeginTransaction())
{
var commitmentId = (await connection.QueryAsync <long>(
@"INSERT INTO [dbo].[Commitment](Reference, LegalEntityId, LegalEntityName, LegalEntityAddress, LegalEntityOrganisationType, AccountLegalEntityPublicHashedId,
EmployerAccountId, ProviderId, ProviderName, CommitmentStatus, EditStatus, CreatedOn, LastAction, LastUpdatedByEmployerName,
LastUpdatedByEmployerEmail, TransferSenderId, TransferSenderName, Originator)
VALUES (@reference, @legalEntityId, @legalEntityName, @legalEntityAddress, @legalEntityOrganisationType, @accountLegalEntityPublicHashedId,
@accountId, @providerId, @providerName, @commitmentStatus, @editStatus, @createdOn, @lastAction, @lastUpdateByEmployerName,
@lastUpdateByEmployerEmail, @TransferSenderId, @TransferSenderName, @Originator);
SELECT CAST(SCOPE_IDENTITY() as int);",
param: parameters,
commandType: CommandType.Text,
transaction: trans)).Single();
trans.Commit();
return commitmentId;
}
}));
}
private async Task ResolveAnyTriagedCourseDataLocks(long apprenticeshipId)
{
var dataLocks = (await _dataLockRepository.GetDataLocks(apprenticeshipId))
.Where(x => !x.IsResolved && x.TriageStatus == TriageStatus.Restart && IsCourseChangeError(x.ErrorCode)).ToList();
if (dataLocks.Any())
{
if (dataLocks.Count() > 1)
{
_logger.Debug($"More than one unresolved data lock with triage status of reset found when stopping apprenticeship. ApprenticeshipId: {apprenticeshipId}", apprenticeshipId);
}
foreach (var dataLock in dataLocks)
{
dataLock.IsResolved = true;
await _dataLockRepository.UpdateDataLockStatus(dataLock);
}
}
}