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