private async Task UpsertOptions(StandardCollation standard, bool removingStandard = false)
{
// retrieving both live and non-live, so that an existing non-live option can be re-activated
var existingOptions = await GetOptionsInternal(new List <int> {
standard.StandardId.Value
}, isLive : null);
var optionsToBeRemoved = removingStandard
? existingOptions
: existingOptions
.Where(existingOptionName => !standard.Options.Any(optionName => optionName.Equals(existingOptionName.OptionName, StringComparison.InvariantCulture)));
foreach (var option in optionsToBeRemoved)
{
await UpdateExistingOptionToRemoved(standard, option.OptionName);
}
// the OptionName is a natural key, the Id is created in assessor as the IFATE id of an option does not always exist
// therefore updates are to add previously removed options back for a standard
var optionsToBeUpdated = standard.Options
.Where(latestOptionName => existingOptions.Any(existingOptionName => existingOptionName.OptionName.Equals(latestOptionName, StringComparison.InvariantCulture)));
foreach (var optionName in optionsToBeUpdated)
{
await UpdateExistingOption(standard, optionName);
}
var optionsToBeInserted = standard.Options
.Where(latestOptionName => !existingOptions.Any(existingOptionName => existingOptionName.OptionName.Equals(latestOptionName, StringComparison.InvariantCulture)));
foreach (var optionName in optionsToBeInserted)
{
await InsertNewOption(standard, optionName);
}
}
public void Setup()
{
_standardService = new Mock <IStandardService>();
_cleanserService = new Mock <ISpecialCharacterCleanserService>();
_logger = new Mock <ILogger <SearchStandardsHandler> >();
_validator = new Mock <IEpaOrganisationValidator>();
_standardSummary1 = new StandardCollation {
StandardId = 1, Title = "name 100"
};
_standardSummary2 = new StandardCollation {
StandardId = 2, Title = "name 10"
};
errorResponse = BuildErrorResponse(errorMessage, ValidationStatusCode.BadRequest);
_validator.Setup(v => v.ValidatorSearchStandardsRequest(It.IsAny <SearchStandardsValidationRequest>())).Returns(new ValidationResponse());
_expectedStandards = new List <StandardCollation>
{
_standardSummary1,
_standardSummary2
};
_cleanserService.Setup(c => c.UnescapeAndRemoveNonAlphanumericCharacters(_standardSummary1.Title)).Returns(_standardSummary1.Title);
_cleanserService.Setup(c => c.UnescapeAndRemoveNonAlphanumericCharacters(_standardSummary2.Title)).Returns(_standardSummary2.Title);
_standardService.Setup(s => s.GetAllStandards()).Returns(Task.FromResult(_expectedStandards.AsEnumerable()));
_searchStandardsHandler = new SearchStandardsHandler(_standardService.Object, _logger.Object, _cleanserService.Object, _validator.Object);
}
private async Task InsertNewOption(StandardCollation standard, string optionName)
{
await _unitOfWork.Connection.ExecuteAsync(
"INSERT INTO [Options] ([StdCode] ,[OptionName]) " +
"VALUES (@stdCode, @optionName)",
param : new { StdCode = standard.StandardId, optionName },
transaction : _unitOfWork.Transaction
);
}
private async Task InsertNewStandard(StandardCollation standard, string standardData)
{
await _unitOfWork.Connection.ExecuteAsync(
"INSERT INTO [StandardCollation] ([StandardId],[ReferenceNumber] ,[Title],[StandardData]) " +
$@"VALUES (@standardId, @referenceNumber, @Title, @standardData)",
param : new { standard.StandardId, standard.ReferenceNumber, standard.Title, standardData },
transaction : _unitOfWork.Transaction
);
}
private async Task UpdateExistingStandardToRemoved(StandardCollation standard)
{
await _unitOfWork.Connection.ExecuteAsync(
"Update [StandardCollation] set IsLive=0, DateRemoved=getutcdate() " +
"where StandardId = @standardId",
param : new { standard.StandardId },
transaction : _unitOfWork.Transaction
);
}
private async Task UpdateExistingStandard(StandardCollation standard, string standardData)
{
// when new ReferenceNumber is null (IFA has not supplied one) retain the current ReferenceNumber
await _unitOfWork.Connection.ExecuteAsync(
"Update [StandardCollation] set ReferenceNumber = case when @referenceNumber is not null then @referenceNumber else ReferenceNumber end, Title = @Title, StandardData = @StandardData, DateUpdated=getutcdate(), DateRemoved=null, IsLive = 1 " +
"where StandardId = @standardId",
param : new { standard.StandardId, standard.ReferenceNumber, standard.Title, standardData },
transaction : _unitOfWork.Transaction
);
}
private async Task UpdateExistingOptionToRemoved(StandardCollation standard, string optionName)
{
await _unitOfWork.Connection.ExecuteAsync(
"UPDATE [Options] SET " +
"IsLive = 0, " +
"DateRemoved = GETUTCDATE() " +
"WHERE StdCode = @stdCode AND OptionName = @optionName COLLATE SQL_Latin1_General_CP1_CS_AS",
param : new { StdCode = standard.StandardId, optionName },
transaction : _unitOfWork.Transaction
);
}
public static string NormalizeCourseOption(StandardCollation standard, string courseOption)
{
if (standard.Options is null)
{
return(courseOption);
}
else
{
return(standard.Options.FirstOrDefault(g => g.Equals(courseOption, StringComparison.InvariantCultureIgnoreCase)) ?? courseOption);
}
}
private async Task UpdateExistingStandardToRemoved(StandardCollation standard)
{
await _unitOfWork.Connection.ExecuteAsync(
"UPDATE [StandardCollation] SET " +
"IsLive = 0, " +
"DateRemoved = GETUTCDATE() " +
"WHERE StandardId = @standardId",
param : new { standard.StandardId },
transaction : _unitOfWork.Transaction
);
await UpsertOptions(standard, true);
}
public async Task <StandardCollation> GetStandard(int standardId)
{
StandardCollation standardCollation = null;
try
{
standardCollation = await _standardRepository.GetStandardCollationByStandardId(standardId);
}
catch (Exception ex)
{
_logger.LogError(ex, $"STANDARD COLLATION: Failed to get for standard id: {standardId}");
}
return(standardCollation);
}
public async Task <StandardCollation> GetStandard(string referenceNumber)
{
StandardCollation standardCollation = null;
try
{
standardCollation = await _standardRepository.GetStandardCollationByReferenceNumber(referenceNumber);
}
catch (Exception ex)
{
_logger.LogError(ex, $"STANDARD COLLATION: Failed to get for standard reference: {referenceNumber}");
}
return(standardCollation);
}
private async Task UpdateExistingStandard(StandardCollation standard)
{
await _unitOfWork.Connection.ExecuteAsync(
"UPDATE [StandardCollation] SET " +
// when new ReferenceNumber is null (IFA has not supplied one) retain the current ReferenceNumber
"ReferenceNumber = CASE WHEN @referenceNumber IS NOT NULL THEN @referenceNumber ELSE ReferenceNumber END, " +
"Title = @title, " +
"StandardData = @StandardData, " +
"DateUpdated = GETUTCDATE(), " +
"DateRemoved = NULL, " +
"IsLive = 1 " +
"WHERE StandardId = @standardId",
param : new { standard.StandardId, standard.ReferenceNumber, standard.Title, standard.StandardData },
transaction : _unitOfWork.Transaction
);
await UpsertOptions(standard);
}
public void Arrange()
{
_mediator = new Mock <IMediator>();
_logger = new Mock <ILogger <RegisterQueryController> >();
_standard1 = new StandardCollation {
StandardId = 1, Title = "Test 9"
};
_standard2 = new StandardCollation {
StandardId = 1, Title = "Test 2"
};
_expectedStandards = new List <StandardCollation>
{
_standard1,
_standard2
};
_mediator.Setup(m =>
m.Send(It.IsAny <SearchStandardsRequest>(),
new CancellationToken())).ReturnsAsync(_expectedStandards);
_queryController = new RegisterQueryController(_mediator.Object, _logger.Object);
_result = _queryController.SearchStandards(_searchTerm).Result;
}
public async Task UpdateStandardCollationToInsertStandard(int standardId, string referenceNumber, string title, string standardData, string optionName)
{
var _lastestStandardCollations = Clone(_existingStandardCollations);
var newStandard = new StandardCollation
{
StandardId = standardId,
ReferenceNumber = referenceNumber,
Title = title,
StandardData = JsonConvert.DeserializeObject <StandardData>(standardData),
Options = new List <string>
{
optionName
}
};
_lastestStandardCollations.Add(newStandard);
var result = await _repository.UpsertApprovedStandards(_lastestStandardCollations);
var insertedStandard = await _repository.GetStandardCollationByStandardId(standardId);
Assert.AreEqual(newStandard.StandardId, insertedStandard.StandardId);
}
private async Task <Certificate> GetCertificate(GetBatchLearnerRequest request, StandardCollation standard)
{
Certificate certificate = null;
if (standard != null && request.IncludeCertificate)
{
var certificateRequest = new GetBatchCertificateRequest
{
Uln = request.Uln,
FamilyName = request.FamilyName,
StandardCode = standard.StandardId.GetValueOrDefault(),
StandardReference = standard.ReferenceNumber,
UkPrn = request.UkPrn
};
certificate = await _mediator.Send(certificateRequest);
}
return(certificate);
}
private async Task <LearnerDetailForExternalApi> GetLearnerDetails(GetBatchLearnerRequest request, StandardCollation standard)
{
LearnerDetailForExternalApi learnerDetail = null;
if (standard != null)
{
var learner = await _ilrRepository.Get(request.Uln, standard.StandardId.GetValueOrDefault());
if (learner != null)
{
var epao = await _organisationRepository.GetByUkPrn(learner.UkPrn);
learnerDetail = new LearnerDetailForExternalApi()
{
Uln = learner.Uln,
GivenNames = learner.GivenNames,
FamilyName = learner.FamilyName,
LearnerStartDate = learner.LearnStartDate,
LearnerReferenceNumber = learner.LearnRefNumber,
PlannedEndDate = learner.PlannedEndDate,
CompletionStatus = learner.CompletionStatus,
Standard = standard,
EndPointAssessorOrganisationId = epao?.EndPointAssessorOrganisationId ?? learner.EpaOrgId,
UkPrn = epao?.EndPointAssessorUkprn ?? learner.UkPrn,
OrganisationName = epao?.EndPointAssessorName ?? "Unknown"
};
}
else
{
_logger.LogError($"Could not find learner for ULN {request.Uln} and StandardCode {standard.StandardId.GetValueOrDefault()}");
}
}
return(learnerDetail);
}
private CertificateData CombineCertificateData(CertificateData data, Ilr ilr, StandardCollation standard, Provider provider)
{
var epaDetails = data.EpaDetails ?? new EpaDetails();
if (epaDetails.Epas is null)
{
epaDetails.Epas = new List <EpaRecord>();
}
var epaOutcome = data.OverallGrade == CertificateGrade.Fail ? EpaOutcome.Fail : EpaOutcome.Pass;
if (data.AchievementDate != null && !epaDetails.Epas.Any(rec => rec.EpaDate == data.AchievementDate.Value && rec.EpaOutcome == epaOutcome))
{
var record = new EpaRecord {
EpaDate = data.AchievementDate.Value, EpaOutcome = epaOutcome
};
epaDetails.Epas.Add(record);
var latestRecord = epaDetails.Epas.OrderByDescending(epa => epa.EpaDate).First();
epaDetails.LatestEpaDate = latestRecord.EpaDate;
epaDetails.LatestEpaOutcome = latestRecord.EpaOutcome;
}
return(new CertificateData()
{
LearnerGivenNames = ilr.GivenNames,
LearnerFamilyName = ilr.FamilyName,
LearningStartDate = ilr.LearnStartDate,
StandardReference = standard.ReferenceNumber,
StandardName = standard.Title,
StandardLevel = standard.StandardData.Level.GetValueOrDefault(),
StandardPublicationDate = standard.StandardData.EffectiveFrom,
FullName = $"{ilr.GivenNames} {ilr.FamilyName}",
ProviderName = provider.ProviderName,
ContactName = data.ContactName,
ContactOrganisation = data.ContactOrganisation,
Department = data.Department,
ContactAddLine1 = data.ContactAddLine1,
ContactAddLine2 = data.ContactAddLine2,
ContactAddLine3 = data.ContactAddLine3,
ContactAddLine4 = data.ContactAddLine4,
ContactPostCode = data.ContactPostCode,
Registration = data.Registration,
AchievementDate = data.AchievementDate,
CourseOption = CertificateHelpers.NormalizeCourseOption(standard, data.CourseOption),
OverallGrade = CertificateHelpers.NormalizeOverallGrade(data.OverallGrade),
EpaDetails = epaDetails
});
}
private CertificateData CombineCertificateData(CertificateData certData, CertificateData requestData, StandardCollation standard)
{
var epaDetails = certData.EpaDetails ?? new EpaDetails();
if (epaDetails.Epas is null)
{
epaDetails.Epas = new List <EpaRecord>();
}
var epaOutcome = certData.OverallGrade == CertificateGrade.Fail ? EpaOutcome.Fail : EpaOutcome.Pass;
if (requestData.AchievementDate != null && !epaDetails.Epas.Any(rec => rec.EpaDate == requestData.AchievementDate.Value && rec.EpaOutcome == epaOutcome))
{
var record = new EpaRecord {
EpaDate = requestData.AchievementDate.Value, EpaOutcome = epaOutcome
};
epaDetails.Epas.Add(record);
// sort pass outcomes before fail outcomes as pass is the final state even if earlier than the fail
var latestRecord = epaDetails.Epas
.OrderByDescending(epa => epa.EpaOutcome != EpaOutcome.Fail ? 1 : 0)
.ThenByDescending(epa => epa.EpaDate)
.First();
epaDetails.LatestEpaDate = latestRecord.EpaDate;
epaDetails.LatestEpaOutcome = latestRecord.EpaOutcome;
}
return(new CertificateData()
{
LearnerGivenNames = certData.LearnerGivenNames,
LearnerFamilyName = certData.LearnerFamilyName,
LearningStartDate = certData.LearningStartDate,
StandardReference = certData.StandardReference,
StandardName = certData.StandardName,
StandardLevel = certData.StandardLevel,
StandardPublicationDate = certData.StandardPublicationDate,
FullName = certData.FullName,
ProviderName = certData.ProviderName,
ContactName = requestData.ContactName,
ContactOrganisation = requestData.ContactOrganisation,
Department = requestData.Department,
ContactAddLine1 = requestData.ContactAddLine1,
ContactAddLine2 = requestData.ContactAddLine2,
ContactAddLine3 = requestData.ContactAddLine3,
ContactAddLine4 = requestData.ContactAddLine4,
ContactPostCode = requestData.ContactPostCode,
Registration = requestData.Registration,
AchievementDate = requestData.AchievementDate,
CourseOption = CertificateHelpers.NormalizeCourseOption(standard, requestData.CourseOption),
OverallGrade = CertificateHelpers.NormalizeOverallGrade(requestData.OverallGrade),
EpaDetails = epaDetails
});
}