//[Fact]
public void ClearBulkUploadStatus_Should_Succeed()
{
// Arrange
//int ukPRN = 10003954; // Liverpool City Council
int ukPRN = 10000712; // University College Birmingham
ILarsSearchService larsSearchService = LarsSearchServiceTestFactory.GetService();
ICourseService courseService = CourseServiceTestFactory.GetService();
IVenueService venueService = VenueServiceTestFactory.GetService();
IProviderService providerService = ProviderServiceTestFactory.GetService();
IProviderFileImporter importer = new ProviderCsvFileImporter(larsSearchService, courseService, venueService, providerService);
var beforeProvider = providerService.GetProviderByPRNAsync(new ProviderSearchCriteria(ukPRN.ToString())).Result.Value.Value.First();
var logger = Microsoft.Extensions.Logging.Abstractions.NullLogger.Instance;
// Act
var result = Task.Run(async() => await importer.ClearBulkUploadStatus(logger, beforeProvider)).Result;
// Assert
result.Should().BeTrue();
var afterProvider = providerService.GetProviderByPRNAsync(new ProviderSearchCriteria(ukPRN.ToString())).Result.Value.Value.First();
afterProvider.BulkUploadStatus.Should().NotBeNull();
afterProvider.BulkUploadStatus.InProgress.Should().BeFalse();
}
public void When_File_Is_ValidCsv_Then_File_Should_Import()
{
// Arrange
ILarsSearchService larsSearchService = LarsSearchServiceMockFactory.GetLarsSearchService();
ICourseService courseService = CourseServiceMockFactory.GetCourseService();
IVenueService venueService = VenueServiceMockFactory.GetVenueService();
IProviderService providerService = ProviderServiceMockFactory.GetProviderService();
IProviderFileImporter importer = new ProviderCsvFileImporter(larsSearchService, courseService, venueService, providerService);
Stream fileStream = CsvStreams.BulkUpload_ValidMultiple();
ILogger log = Microsoft.Extensions.Logging.Abstractions.NullLogger.Instance;
int ukPRN = 0;
// Act
List <string> errors;
var courses = importer.ParseCsvFile(log, @"10000020\Bulk Upload\Files\190627-082122 Provider Name Ltd.csv", fileStream, ukPRN, out errors);
fileStream.Close();
// Assert
errors.Should().NotBeNull();
errors.Should().BeEmpty();
}
public HomeController(
ILogger <HomeController> logger,
ILarsSearchService larsSearchService,
IHttpContextAccessor contextAccessor)
{
_logger = logger;
_larsSearchService = larsSearchService;
_contextAccessor = contextAccessor;
//Set this todisplay the Search Provider fork of the ProviderSearchResult ViewComponent
_session.SetInt32("ProviderSearch", 1);
}
public LarsSearchController(
ILogger <LarsSearchController> logger,
IOptions <LarsSearchSettings> larsSearchSettings,
ILarsSearchService larsSearchService,
ILarsSearchHelper larsSearchHelper,
IPaginationHelper paginationHelper)
{
Throw.IfNull(logger, nameof(logger));
Throw.IfNull(larsSearchSettings, nameof(larsSearchSettings));
Throw.IfNull(larsSearchService, nameof(larsSearchService));
Throw.IfNull(larsSearchHelper, nameof(larsSearchHelper));
Throw.IfNull(paginationHelper, nameof(paginationHelper));
_logger = logger;
_larsSearchSettings = larsSearchSettings.Value;
_larsSearchService = larsSearchService;
_larsSearchHelper = larsSearchHelper;
_paginationHelper = paginationHelper;
}
//[Fact]
public void DeleteCoursesForProviderTest()
{
// Arrange
//int ukPRN = 10003954; // Liverpool City Council
int ukPRN = 10000712; // University College Birmingham
ILarsSearchService larsSearchService = LarsSearchServiceTestFactory.GetService();
ICourseService courseService = CourseServiceTestFactory.GetService();
IVenueService venueService = VenueServiceTestFactory.GetService();
IProviderService providerService = ProviderServiceTestFactory.GetService();
IProviderFileImporter importer = new ProviderCsvFileImporter(larsSearchService, courseService, venueService, providerService);
var beforeProvider = providerService.GetProviderByPRNAsync(new ProviderSearchCriteria(ukPRN.ToString())).Result.Value.Value.First();
var logger = Microsoft.Extensions.Logging.Abstractions.NullLogger.Instance;
// Act
var result1 = Task.Run(async() => await importer.DeleteBulkUploadCourses(logger, ukPRN)).Result;
var result2 = Task.Run(async() => await importer.ArchiveCourses(logger, ukPRN)).Result;
// Assert
}
public static async Task Run(
string input, // Work around https://github.com/Azure/azure-functions-vs-build-sdk/issues/168
[Inject] IConfigurationRoot configuration,
[Inject] ICosmosDbHelper cosmosDbHelper,
[Inject] IVenueCollectionService venueCollectionService,
[Inject] ILarsSearchService larsSearchService,
[Inject] IBlobStorageHelper blobHelper)
{
var databaseId = configuration["CosmosDbSettings:DatabaseId"];
var coursesCollectionId = "courses";
var logFileName = $"CourseMigrator-{DateTime.Now.ToString("dd-MM-yy HHmm")}";
var blobContainer = configuration["BlobStorageSettings:Container"];
var whitelistFileName = "ProviderWhiteList.txt";
var connectionString = configuration.GetConnectionString("TribalRestore");
var cosmosDbClient = cosmosDbHelper.GetClient();
using (var logStream = new MemoryStream())
using (var logStreamWriter = new StreamWriter(logStream))
using (var logCsvWriter = new CsvWriter(logStreamWriter, CultureInfo.InvariantCulture))
using (var conn1 = new SqlConnection(connectionString))
using (var conn2 = new SqlConnection(connectionString))
{
// Log CSV headers
logCsvWriter.WriteField("CourseId");
logCsvWriter.WriteField("UKPRN");
logCsvWriter.WriteField("Success");
logCsvWriter.WriteField("Status");
logCsvWriter.WriteField("Course instances");
logCsvWriter.WriteField("Error list");
logCsvWriter.NextRecord();
var whitelist = await GetProviderWhiteList();
await conn1.OpenAsync();
await conn2.OpenAsync();
using (var coursesCmd = conn1.CreateCommand())
using (var coursesInstancesCmd = conn2.CreateCommand())
{
coursesCmd.CommandTimeout = 60 * 60; // 1 hour
coursesInstancesCmd.CommandTimeout = 60 * 60; // 1 hour
coursesCmd.CommandText = @"
SELECT
c.CourseId,
c.CourseTitle,
c.CourseSummary,
c.LearningAimRefId,
c.QualificationLevelId,
c.EntryRequirements,
c.ProviderOwnCourseRef,
c.Url,
p.UKPRN,
c.EquipmentRequired,
c.AssessmentMethod,
p.Loans24Plus
FROM Course c
JOIN Provider p ON c.ProviderId = p.ProviderId
WHERE c.RecordStatusId = 2 --Live
--Last updated within 24 months of data freeze 28/02
AND (c.ModifiedDateTimeUtc >= '2018-02-28' OR EXISTS (
SELECT 1 FROM CourseInstance ci
WHERE ci.CourseId = c.CourseId
AND ci.RecordStatusId = 2
AND ci.ModifiedDateTimeUtc >= '2018-02-28'
))
ORDER BY c.CourseId, c.ProviderId";
coursesInstancesCmd.CommandText = @"
SELECT
ci.CourseInstanceId,
ci.CourseId,
ci.ProviderOwnCourseInstanceRef,
ci.StudyModeId,
ci.AttendanceTypeId,
ci.AttendancePatternId,
ci.DurationUnit,
ci.DurationUnitId,
ci.DurationAsText,
ci.StartDateDescription,
cisd.StartDate,
ci.Price,
ci.PriceAsText,
ci.Url,
civ.VenueId,
ci.VenueLocationId
FROM CourseInstance ci
LEFT JOIN CourseInstanceVenue civ ON ci.CourseInstanceId = civ.CourseInstanceId
LEFT JOIN CourseInstanceStartDate cisd ON ci.CourseInstanceId = cisd.CourseInstanceId
WHERE ci.RecordStatusId = 2 --Live
ORDER BY ci.CourseId, ci.OfferedByProviderId";
using (var coursesReader = coursesCmd.ExecuteReader())
using (var courseInstanceReader = coursesInstancesCmd.ExecuteReader())
{
var instanceReader = new CourseInstanceReader(courseInstanceReader);
var instanceEnumerator = instanceReader.ProcessReader().GetEnumerator();
var courseRowReader = coursesReader.GetRowParser <CourseResult>();
while (await coursesReader.ReadAsync())
{
var course = courseRowReader(coursesReader);
// If provider is not on whitelist - skip this course
if (!whitelist.Contains(course.UKPRN))
{
continue;
}
var instances = instanceReader.ConsumeReader(instanceEnumerator, course.CourseId);
var errors = new List <string>();
CourseMigrationResult result;
try
{
// Tribal don't have any Courses with zero CourseInstances...
if (instances.Count == 0)
{
errors.Add("Found zero CourseInstances.");
}
// Check LARS
var larsSearchResults = !string.IsNullOrEmpty(course.LearningAimRefId) ?
await QueryLars(course.LearningAimRefId) :
Array.Empty <LarsSearchResultItem>();
// Check the venues exist
Dictionary <int, Guid> venueIdMap = new Dictionary <int, Guid>();
foreach (var venueId in instances.Where(i => i.VenueId.HasValue).Select(i => i.VenueId.Value))
{
if (venueIdMap.ContainsKey(venueId))
{
continue;
}
var cosmosVenue = await venueCollectionService.GetDocumentByVenueId(venueId);
if (cosmosVenue == null)
{
errors.Add($"Missing venue {venueId}.");
}
else
{
venueIdMap.Add(venueId, Guid.Parse(cosmosVenue.ID));
}
}
if (errors.Count == 0)
{
// Got the course in Cosmos already?
var existingCourseRecord = await GetExistingCourse(course.CourseId, course.UKPRN, cosmosDbClient);
var mappedCourseRuns = instances
.Select(i =>
{
Guid?venueId = null;
if (i.VenueId.HasValue)
{
venueId = venueIdMap[i.VenueId.Value];
}
// Retain the existing Cosmos ID if there is one
// N.B. We can have more than one match on CourseInstanceId since we 'explode' on multiple start dates
var courseRunId =
existingCourseRecord?.CourseRuns.SingleOrDefault(r => r.CourseInstanceId == i.CourseInstanceId && r.StartDate == i.StartDate)?.id ??
Guid.NewGuid();
return(MapCourseInstance(course, i, courseRunId, venueId, errors));
})
.ToList();
var courseId = existingCourseRecord?.id ?? Guid.NewGuid();
var mappedCourse = MapCourse(course, mappedCourseRuns, larsSearchResults, courseId, errors);
var added = await UpsertCourse(mappedCourse, cosmosDbClient);
result = added ? CourseMigrationResult.Inserted : CourseMigrationResult.Updated;
}
else
{
result = CourseMigrationResult.SkippedDueToErrors;
}
}
catch (Exception ex)
{
errors.Add(ex.ToString().Replace("\n", " "));
result = CourseMigrationResult.Exception;
}
// Write to log
logCsvWriter.WriteField(course.CourseId);
logCsvWriter.WriteField(course.UKPRN);
logCsvWriter.WriteField(result == CourseMigrationResult.Inserted || result == CourseMigrationResult.Updated);
logCsvWriter.WriteField(result.ToString());
logCsvWriter.WriteField(instances.Count);
logCsvWriter.WriteField(string.Join(", ", errors));
logCsvWriter.NextRecord();
}
}
}
// Upload log CSV to blob storage
{
logStreamWriter.Flush();
logStream.Seek(0L, SeekOrigin.Begin);
var blob = blobHelper.GetBlobContainer(blobContainer).GetBlockBlobReference(logFileName);
await blob.UploadFromStreamAsync(logStream);
}
}
async Task <ISet <int> > GetProviderWhiteList()
{
var blob = blobHelper.GetBlobContainer(blobContainer).GetBlockBlobReference(whitelistFileName);
var ms = new MemoryStream();
await blob.DownloadToStreamAsync(ms);
ms.Seek(0L, SeekOrigin.Begin);
var results = new HashSet <int>();
string line;
using (var reader = new StreamReader(ms))
{
while ((line = reader.ReadLine()) != null)
{
if (string.IsNullOrEmpty(line))
{
continue;
}
var ukprn = int.Parse(line);
results.Add(ukprn);
}
}
return(results);
}
async Task <IReadOnlyCollection <LarsSearchResultItem> > QueryLars(string learningAimRef)
{
var result = await larsSearchService.SearchAsync(new LarsSearchCriteria(learningAimRef, top : 1, skip : 0));
if (result.IsFailure)
{
throw new Exception($"LARS search failed:\n{result.Error}");
}
return(result.Value.Value.ToList());
}
async Task <bool> UpsertCourse(Course course, IDocumentClient documentClient)
{
var collectionLink = UriFactory.CreateDocumentCollectionUri(databaseId, coursesCollectionId);
var result = await documentClient.UpsertDocumentAsync(collectionLink, course, new RequestOptions()
{
PartitionKey = new Microsoft.Azure.Documents.PartitionKey(course.ProviderUKPRN)
});
return(result.StatusCode == HttpStatusCode.Created);
}
async Task <Course> GetExistingCourse(int courseId, int ukprn, IDocumentClient documentClient)
{
var collectionLink = UriFactory.CreateDocumentCollectionUri(databaseId, coursesCollectionId);
var query = documentClient
.CreateDocumentQuery <Course>(collectionLink, new FeedOptions()
{
PartitionKey = new Microsoft.Azure.Documents.PartitionKey(ukprn)
})
.Where(d => d.CourseId == courseId)
.AsDocumentQuery();
return((await query.ExecuteNextAsync()).FirstOrDefault());
}
AttendancePattern MapAttendancePattern(DeliveryMode deliveryMode, int?attendancePatternId, out bool hasError)
{
if (deliveryMode != DeliveryMode.ClassroomBased)
{
hasError = false;
return(AttendancePattern.Undefined);
}
if (!attendancePatternId.HasValue)
{
hasError = true;
return(AttendancePattern.Undefined);
}
switch (attendancePatternId.Value)
{
case 1:
hasError = false;
return(AttendancePattern.Daytime);
case 2:
hasError = false;
return(AttendancePattern.DayOrBlockRelease);
case 3:
case 4:
hasError = false;
return(AttendancePattern.Evening);
case 5:
hasError = false;
return(AttendancePattern.Weekend);
case 6:
case 7:
case 8:
default:
hasError = true;
return(AttendancePattern.Undefined);
}
}
DeliveryMode MapDeliveryMode(int?attendanceTypeId, out bool hasError)
{
if (!attendanceTypeId.HasValue)
{
hasError = true;
return(DeliveryMode.Undefined);
}
switch (attendanceTypeId.Value)
{
case 1:
hasError = false;
return(DeliveryMode.ClassroomBased);
case 2:
case 3:
hasError = false;
return(DeliveryMode.WorkBased);
case 7:
case 8:
hasError = false;
return(DeliveryMode.Online);
case 4:
case 5:
case 6:
case 9:
default:
hasError = true;
return(DeliveryMode.Undefined);
}
}
StudyMode MapStudyMode(DeliveryMode deliveryMode, int?studyModeId, out bool hasError)
{
if (deliveryMode != DeliveryMode.ClassroomBased)
{
hasError = false;
return(StudyMode.Undefined);
}
if (!studyModeId.HasValue)
{
hasError = true;
return(StudyMode.Undefined);
}
switch (studyModeId.Value)
{
case 1:
hasError = false;
return(StudyMode.FullTime);
case 2:
hasError = false;
return(StudyMode.PartTime);
case 3:
hasError = true;
return(StudyMode.Undefined);
case 4:
hasError = false;
return(StudyMode.Flexible);
default:
hasError = true;
return(StudyMode.Undefined);
}
}
(DurationUnit, int?) MapDuration(int?durationUnit, int?durationValue, out bool hasError)
{
if (!durationUnit.HasValue)
{
hasError = true;
return(DurationUnit.Undefined, null);
}
switch (durationUnit.Value)
{
case 1:
hasError = false;
return(DurationUnit.Hours, durationValue);
case 2:
hasError = false;
return(DurationUnit.Days, durationValue);
case 3:
hasError = false;
return(DurationUnit.Weeks, durationValue);
case 4:
hasError = false;
return(DurationUnit.Months, durationValue);
case 5:
hasError = false;
return(DurationUnit.Months, 3);
case 7:
hasError = false;
return(DurationUnit.Years, durationValue);
case 6:
default:
hasError = true;
return(DurationUnit.Undefined, null);
}
}
CourseRun MapCourseInstance(
CourseResult course,
CourseInstanceResult courseInstance,
Guid id,
Guid?venueId,
List <string> errors)
{
var deliveryMode = MapDeliveryMode(courseInstance.AttendanceTypeId, out var deliveryModeError);
var attendancePattern = MapAttendancePattern(deliveryMode, courseInstance.AttendancePatternId, out var attendancePatternError);
var studyMode = MapStudyMode(deliveryMode, courseInstance.StudyModeId, out var studyModeError);
var(durationUnit, durationValue) = MapDuration(courseInstance.DurationUnitId, courseInstance.DurationUnit, out var durationError);
var hasErrors = false;
if (attendancePatternError)
{
errors.Add($"Invalid AttendancePattern");
hasErrors = true;
}
if (deliveryModeError)
{
errors.Add($"Invalid DeliveryMode");
hasErrors = true;
}
if (studyModeError)
{
errors.Add($"Invalid StudyMode");
hasErrors = true;
}
if (durationError)
{
errors.Add($"Invalid Duration");
hasErrors = true;
}
bool flexibleStartDate = default;
DateTime?startDate = default;
if (deliveryMode == DeliveryMode.Online)
{
flexibleStartDate = true;
}
else if (courseInstance.StartDate.HasValue)
{
flexibleStartDate = false;
startDate = courseInstance.StartDate;
}
else if (string.IsNullOrEmpty(courseInstance.StartDateDescription))
{
errors.Add($"Empty StartDateDescription");
hasErrors = true;
}
else if (DateTime.TryParseExact(courseInstance.StartDateDescription, "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out var sd))
{
flexibleStartDate = false;
startDate = sd;
}
else
{
flexibleStartDate = true;
}
if (deliveryMode == DeliveryMode.ClassroomBased && !venueId.HasValue)
{
errors.Add($"No venue");
hasErrors = true;
}
// Work-based should have regions(s) or be national
bool?national = null;
IEnumerable <string> regions = Array.Empty <string>();
IEnumerable <SubRegionItemModel> subRegions = Array.Empty <SubRegionItemModel>();
if (deliveryMode == DeliveryMode.WorkBased)
{
if (!courseInstance.VenueLocationId.HasValue)
{
errors.Add("No region found");
hasErrors = true;
}
else
{
if (RegionLookup.IsNational(courseInstance.VenueLocationId.Value))
{
national = true;
}
else
{
var lookupResult = RegionLookup.FindRegions(courseInstance.VenueLocationId.Value);
if (!lookupResult.HasValue)
{
errors.Add($"Cannot find sub-region(s) for VenueLocationId {courseInstance.VenueLocationId.Value}");
hasErrors = true;
}
else
{
regions = lookupResult.Value.regionIds;
subRegions = lookupResult.Value.subRegions;
national = false;
}
}
}
}
var recordStatus = hasErrors ? RecordStatus.MigrationPending : RecordStatus.Live;
return(new CourseRun()
{
AttendancePattern = attendancePattern,
Cost = courseInstance.Price,
CostDescription = courseInstance.PriceAsText,
CourseInstanceId = courseInstance.CourseInstanceId,
CourseName = course.CourseTitle,
CourseURL = courseInstance.Url,
CreatedBy = "CourseMigrator",
CreatedDate = DateTime.Now,
DeliveryMode = deliveryMode,
DurationUnit = durationUnit,
DurationValue = durationValue,
FlexibleStartDate = flexibleStartDate,
id = id,
ProviderCourseID = courseInstance.ProviderOwnCourseInstanceRef,
RecordStatus = recordStatus,
National = national,
Regions = regions,
StartDate = startDate,
StudyMode = studyMode,
SubRegions = subRegions,
//UpdatedBy
UpdatedDate = DateTime.Now,
VenueId = venueId
});
}
Course MapCourse(
CourseResult course,
IReadOnlyCollection <CourseRun> courseRuns,
IReadOnlyCollection <LarsSearchResultItem> larsSearchResults,
Guid id,
List <string> errors)
{
var isValid = courseRuns.All(r => r.RecordStatus.HasFlag(RecordStatus.Live));
LarlessReason?larlessReason = string.IsNullOrEmpty(course.LearningAimRefId) ?
LarlessReason.NoLars : larsSearchResults.Count == 0 ?
LarlessReason.UnknownLars : larsSearchResults.Count > 1 ?
LarlessReason.MultipleMatchingLars : // TODO Consider expired LARS
LarlessReason.Undefined;
var qualification = larsSearchResults.Count == 1 ? larsSearchResults.Single() : null;
if (qualification == null)
{
foreach (var cr in courseRuns)
{
cr.RecordStatus = RecordStatus.MigrationPending;
}
errors.Add("LARS lookup failed");
isValid = false;
}
return(new Course()
{
AdultEducationBudget = default,
