private static async Task <string> GenerateVenuesExportForProvider(
ILogger log,
IVenueCollectionService venueCollectionService,
StringBuilder logFile,
IMiragtionProviderItem mpItem,
CloudBlobContainer containerExporter,
string containerNameExporter)
{
logFile.AppendLine($"\tAttempting to get venues' data for: {mpItem}");
var venues = await venueCollectionService.GetAllVenuesAsJsonForUkprnAsync(mpItem.Ukprn);
logFile.AppendLine($"\tGot venues' data for: {mpItem}");
if (venues != "[]")
{
logFile.AppendLine($"\t\tHas venues' data for: {mpItem}");
var venuesFileName =
$"{DateTime.Today.ToString("yyyyMMdd")}\\Generated\\Venues_for_Provider_{mpItem.Ukprn}_{DateTime.Now.ToString("yyyy-MM-ddTHH-mm-ss")}.json";
logFile.AppendLine($"\t\tGot reference to block blob containers for file: {venuesFileName}");
var venuesBlob = containerExporter.GetBlockBlobReference(venuesFileName);
logFile.AppendLine($"\t\tAttempting to upload file {venuesFileName} to blob container {containerNameExporter}");
await venuesBlob.UploadTextAsync(venues);
log.LogInformation($"uploaded {venuesFileName}");
logFile.AppendLine($"\t\tUploaded file {venuesFileName} to blob container {containerNameExporter}");
return(venuesFileName);
}
else
{
logFile.AppendLine($"\t\tHas no venues' data for: {mpItem}");
return(null);
}
}
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,
public static async Task Run(
[TimerTrigger("%schedule%")] TimerInfo myTimer,
ILogger log,
[Inject] IOptions <ExporterSettings> exporterSettings,
[Inject] IBlobStorageHelper blobStorageHelper,
[Inject] IProviderCollectionService providerCollectionService,
[Inject] ICourseCollectionService courseCollectionService,
[Inject] IVenueCollectionService venueCollectionService)
{
var configuration = exporterSettings.Value;
log.LogInformation("[Export] waiting for trigger...");
//TODO: add more logging after you get this working ...
var logFile = new StringBuilder();
logFile.AppendLine($"Starting {nameof(Export)} at {DateTime.Now}");
var fileNames = new List <string>();
var startDate = configuration.ExporterStartDate;
var providersFileName = $"{DateTime.Today.ToString("yyyyMMdd")}\\Generated\\Providers_{DateTime.Now.ToString("yyyy-MM-ddTHH-mm-ss")}.json";
logFile.AppendLine($"Start date: {startDate:dd/MM/yyyy hh:mm}");
logFile.AppendLine($"Provider filename: {providersFileName}");
var containerNameExporter = configuration.ContainerNameExporter;
var containerNameProviderFiles = configuration.ContainerNameProviderFiles;
var migrationProviderCsv = configuration.MigrationProviderCsv;
logFile.AppendLine($"Attempting to get reference to blob containers: {containerNameExporter}, {containerNameProviderFiles}");
var containerExporter = blobStorageHelper.GetBlobContainer(containerNameExporter);
var containerProviderFiles = blobStorageHelper.GetBlobContainer(containerNameProviderFiles);
logFile.AppendLine($"Got references to blob containers: {containerNameExporter}, {containerNameProviderFiles}");
try
{
log.LogInformation("[Export] grabbing providers");
var providersForExport = await GetProvidersFromCsv(migrationProviderCsv, blobStorageHelper, logFile, containerProviderFiles);
var providerFileName = await GenerateProvidersExport(providerCollectionService, providersForExport, logFile, providersFileName, containerExporter, containerNameExporter);
fileNames.Add(providerFileName);
var count = 0;
var total = providersForExport.Count();
// N.B. Deliberately not doing these in parallel to avoid creating too many DocumentClients...
foreach (var provider in providersForExport)
{
count++;
log.LogInformation($"[Export] checking {provider.Ukprn} [{count} of {total}]");
var export = await CheckForProviderUpdates(log, courseCollectionService,
venueCollectionService, logFile, provider, startDate, containerExporter,
containerNameExporter)
.ConfigureAwait(false);
fileNames.AddRange(export);
}
var fileNamesFileName = $"{DateTime.Today.ToString("yyyyMMdd")}\\Generated\\FileNames.json";
var fileNamesBlob = containerExporter.GetBlockBlobReference(fileNamesFileName);
await fileNamesBlob.UploadTextAsync(JsonConvert.SerializeObject(fileNames, Formatting.Indented));
}
catch (Exception e)
{
logFile.AppendLine(e.Message);
logFile.AppendLine(e.ToString());
throw;
}
finally
{
logFile.AppendLine($"Ending {nameof(Export)} at {DateTime.Now}");
var logFileName = $"{DateTime.Today.ToString("yyyyMMdd")}\\Generated\\Log_{DateTime.Now.ToString("yyyy-MM-ddTHH-mm-ss")}.txt";
var logFileNameBlob = containerExporter.GetBlockBlobReference(logFileName);
await logFileNameBlob.UploadTextAsync(logFile.ToString());
}
}
private static async Task <IEnumerable <string> > CheckForProviderUpdates(
ILogger log,
ICourseCollectionService courseCollectionService,
IVenueCollectionService venueCollectionService,
StringBuilder logFile,
IMiragtionProviderItem mpItem,
DateTime fromDate,
CloudBlobContainer containerExporter,
string containerNameExporter)
{
var fileNames = new List <string>();
logFile.AppendLine($"Attempting to get conditional data for: {mpItem}");
var hasTodaysDate = mpItem.DateMigrated.Date == DateTime.Today;
var dateMigratedIsInThePast = mpItem.DateMigrated.Date < DateTime.Today;
var hasCreatedCourses = await courseCollectionService.HasCoursesBeenCreatedSinceAsync(mpItem.Ukprn, fromDate);
var hasCreatedCourseRuns =
await courseCollectionService.HasCourseRunsBeenCreatedSinceAsync(mpItem.Ukprn, fromDate);
var hasUpdatedCourses = await courseCollectionService.HasCoursesBeenUpdatedSinceAsync(mpItem.Ukprn, fromDate);
var hasUpdatedCourseRuns =
await courseCollectionService.HasCourseRunsBeenUpdatedSinceAsync(mpItem.Ukprn, fromDate);
var hasDeletedCourses = await courseCollectionService.HasCoursesBeenDeletedSinceAsync(mpItem.Ukprn, fromDate);
var hasDeletedCourseRuns =
await courseCollectionService.HasCourseRunsBeenDeletedSinceAsync(mpItem.Ukprn, fromDate);
var hasUpdatedVenues = await venueCollectionService.HasBeenAnUpdatedSinceAsync(mpItem.Ukprn, fromDate);
logFile.AppendLine($"Got conditional data for: {mpItem}");
logFile.AppendLine($"\tHas today's date: {hasTodaysDate}");
logFile.AppendLine($"\tDate migrated is in the past: {dateMigratedIsInThePast}");
logFile.AppendLine($"\tHas created Courses: {hasCreatedCourses}");
logFile.AppendLine($"\tHas created CourseRuns: {hasCreatedCourseRuns}");
logFile.AppendLine($"\tHas updated Courses: {hasUpdatedCourses}");
logFile.AppendLine($"\tHas updated CourseRuns: {hasUpdatedCourseRuns}");
logFile.AppendLine($"\tHas deleted Courses: {hasDeletedCourses}");
logFile.AppendLine($"\tHas deleted CourseRuns: {hasDeletedCourseRuns}");
logFile.AppendLine($"\tHas updated Venues: {hasUpdatedVenues}");
logFile.AppendLine($"End of conditional data for: {mpItem}");
if (hasTodaysDate || (dateMigratedIsInThePast))
{
if (hasCreatedCourses || hasCreatedCourseRuns || hasUpdatedCourses || hasUpdatedCourseRuns ||
hasDeletedCourses || hasDeletedCourseRuns)
{
log.LogInformation($"updating courses for {mpItem.Ukprn}");
var courseFilename = await GenerateCoursesExportForProvider(log, courseCollectionService, logFile, mpItem, containerExporter,
containerNameExporter).ConfigureAwait(false);
fileNames.Add(courseFilename);
}
if (hasUpdatedVenues)
{
log.LogInformation($"updating venues for {mpItem.Ukprn}");
var venueFilename = await GenerateVenuesExportForProvider(log, venueCollectionService, logFile, mpItem, containerExporter,
containerNameExporter).ConfigureAwait(false);
fileNames.Add(venueFilename);
}
}
else
{
logFile.AppendLine($"Conditional logic for {mpItem} is False.");
}
return(fileNames);
}
public static async Task Run(
string input, // Work around https://github.com/Azure/azure-functions-vs-build-sdk/issues/168
ILogger log,
[Inject] IConfigurationRoot configuration,
[Inject] IVenueCollectionService venueCollectionService,
[Inject] IProviderCollectionService providerCollectionService,
[Inject] ICosmosDbHelper cosmosDbHelper,
[Inject] IBlobStorageHelper blobhelper
)
{
var venuesCollectionId = configuration["CosmosDbCollectionSettings:VenuesCollectionId"];
var connectionString = configuration.GetConnectionString("TribalRestore");
var blobContainer = blobhelper.GetBlobContainer(configuration["BlobStorageSettings:Container"]);
var whiteListProviders = await GetProviderWhiteList();
var result = new List <ResultMessage>();
var venueList = new List <Venue>();
var venueExportFileName = $"VenueExport-{DateTime.Now.ToString("dd-MM-yy HHmm")}";
const string WHITE_LIST_FILE = "ProviderWhiteList.txt";
var ukprnCache = new List <int>();
var databaseId = configuration["CosmosDbSettings:DatabaseId"];
//update or insert records
var _cosmosClient = cosmosDbHelper.GetClient();
using (var sqlConnection = new SqlConnection(connectionString))
{
using (var command = sqlConnection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = @"
DECLARE @Venues TABLE
(
VenueId INT NOT NULL,
ProviderId INT NOT NULL,
ProviderOwnVenueRef NVARCHAR(255) NULL,
VenueName NVARCHAR(255) NOT NULL,
Email NVARCHAR(255) NULL,
Website NVARCHAR(255) NULL,
Fax NVARCHAR(35) NULL,
Facilities NVARCHAR(2000),
RecordStatusId INT NOT NULL,
CreatedByUserId NVARCHAR(128) NOT NULL,
CreatedDateTimeUtc DATETIME NOT NULL,
ModifiedByUserId NVARCHAR(128) NULL,
ModifiedDateTimeUtc DATETIME NULL,
AddressId INT,
Telephone NVARCHAR(30) NULL,
BulkUploadVenueId NVARCHAR(255) NULL,
UKPRN INT NOT NULL,
AddressLine1 NVARCHAR(110) NULL,
AddressLine2 NVARCHAR(100) NULL,
County NVARCHAR(75) NULL,
Latitude Decimal(9,6) NULL,
Longitude Decimal(9,6) NULL,
Postcode NVARCHAR(30) NULL,
Town NVARCHAR(75) NULL,
source INT NOT NULL,
LocationID INT NULL
)
INSERT INTO @Venues
(
VenueId,
ProviderId,
ProviderOwnVenueRef,
VenueName,
Email,
Website,
Fax,
Facilities,
RecordStatusId,
CreatedByUserId,
CreatedDateTimeUtc,
ModifiedByUserId,
ModifiedDateTimeUtc,
AddressId,
Telephone,
BulkUploadVenueId,
UKPRN,
AddressLine1,
AddressLine2,
County,
Latitude,
Longitude,
Postcode,
Town,
source,
LocationID
)
SELECT distinct Ven.[VenueId],
Ven.[ProviderId],
Ven.[ProviderOwnVenueRef],
Ven.[VenueName],
Ven.[Email],
Ven.[Website],
Ven.[Fax],
Ven.[Facilities],
Ven.[RecordStatusId],
Ven.[CreatedByUserId],
Ven.[CreatedDateTimeUtc],
Ven.[ModifiedByUserId],
Ven.[ModifiedDateTimeUtc],
Ven.[AddressId],
Ven.[Telephone],
Ven.[BulkUploadVenueId],
pr.Ukprn,
Ad.AddressLine1,
ad.AddressLine2,
ad.County,
ad.[Latitude],
ad.[Longitude],
ad.Postcode,
ad.Town,
1 as [Source],
NULL as LocationId
FROM Venue Ven
INNER JOIN [Address] Ad on Ad.AddressId = Ven.AddressId
INNER JOIN [Provider] pr on pr.ProviderId = ven.ProviderId
WHERE Ven.RecordStatusID = 2
UNION ALL
SELECT DISTINCT 0,
L.[ProviderId],
l.ProviderOwnLocationRef,
L.[LocationName],
L.[Email],
L.[Website],
NULL,
NULL,
L.[RecordStatusId],
L.[CreatedByUserId],
L.[CreatedDateTimeUtc],
L.[ModifiedByUserId],
L.[ModifiedDateTimeUtc],
L.[AddressId],
L.[Telephone],
NULL,
pr.Ukprn,
Ad.AddressLine1,
ad.AddressLine2,
ad.County,
ad.[Latitude],
ad.[Longitude],
ad.Postcode,
ad.Town,
2 as [Source],
L.LocationId as LocationId
FROM Location l
INNER JOIN Address ad on ad.AddressId = l.AddressId
INNER JOIN Provider pr on pr.ProviderId = l.ProviderId
WHERE l.RecordStatusId = 2
SELECT * FROM @Venues
";
try
{
//Open connection.
sqlConnection.Open();
using (SqlDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
//Read venue
venueList.Add(Venue.FromDataReader(dataReader));
}
// Close the SqlDataReader.
dataReader.Close();
}
sqlConnection.Close();
}
catch (Exception ex)
{
log.LogError("An error occured migratiing Venues", ex);
}
}
}
foreach (var item in venueList)
{
try
{
if (Validate(item))
{
var cosmosVenue = await GetVenue(item.Source, item.VenueId, item.LocationID, item.UKPRN);
if (cosmosVenue != null)
{
//var s = UriFactory.CreateDocumentUri(databaseId, venuesCollectionId, cosmosVenue.ID.ToString());
if (cosmosVenue.UKPRN != item.UKPRN)
{
continue;
}
Uri collectionUri = UriFactory.CreateDocumentCollectionUri(databaseId, venuesCollectionId);
var editedVenue = new Dfc.CourseDirectory.Models.Models.Venues.Venue()
{
ID = cosmosVenue.ID,
UKPRN = item.UKPRN,
VenueName = item.VenueName,
Address1 = item.Address.Address1,
Address2 = item.Address.Address2,
Town = item.Address.Town,
County = item.Address.County,
PostCode = item.Address.Postcode,
Latitude = item.Address.Latitude,
Longitude = item.Address.Longitude,
Status = MapVenueStatus(item),
UpdatedBy = "VenueMigrator",
DateUpdated = DateTime.Now,
VenueID = item.VenueId,
ProviderID = item.ProviderId,
ProvVenueID = item.ProviderOwnVenueRef,
Email = item.Email,
Website = item.Website,
Telephone = item.Telephone,
CreatedBy = "VenueMigrator",
CreatedDate = DateTime.Now,
LocationId = item.LocationID,
TribalLocationId = item.LocationID
};
await _cosmosClient.UpsertDocumentAsync(collectionUri, editedVenue);
AddResultMessage(item.UKPRN, item.VenueId, item.LocationID, "Updated Record", $"Old cosmos record LocationId:{cosmosVenue.LocationId}, VenueId: {cosmosVenue.VenueID}");
}
else
{
var newVenue = new Dfc.CourseDirectory.Models.Models.Venues.Venue()
{
UKPRN = item.UKPRN,
VenueName = item.VenueName,
Address1 = item.Address.Address1,
Address2 = item.Address.Address2,
Town = item.Address.Town,
County = item.Address.County,
PostCode = item.Address.Postcode,
Latitude = item.Address.Latitude,
Longitude = item.Address.Longitude,
Status = MapVenueStatus(item),
UpdatedBy = item.CreatedByUserId,
DateUpdated = item.CreatedDateTimeUtc,
VenueID = item.VenueId,
ProviderID = item.ProviderId,
ProvVenueID = item.ProviderOwnVenueRef,
Email = item.Email,
Website = item.Website,
Telephone = item.Telephone,
CreatedDate = DateTime.Now,
CreatedBy = "VenueMigrator",
LocationId = item.LocationID,
TribalLocationId = item.LocationID
};
await cosmosDbHelper.CreateDocumentAsync(_cosmosClient, venuesCollectionId, newVenue);
//Log that successfully inserted venue
AddResultMessage(item.UKPRN, item.VenueId, item.LocationID, "Inserted Venue");
}
}
}
catch (Exception ex)
{
string errorMessage = $"An error occured while updating cosmos record for venue {item.VenueId}. {ex.Message}";
log.LogError(errorMessage, ex);
AddResultMessage(item.UKPRN, item.VenueId, item.LocationID, errorMessage);
}
}
var resultsObjBytes = GetResultAsByteArray(result);
await WriteResultsToBlobStorage(resultsObjBytes);
//log completion
log.LogInformation("Migrating Venues Complete");
async Task <Dfc.CourseDirectory.Models.Models.Venues.Venue> GetVenue(VenueSource source, int?venueId, int?locationId, int ukprn)
{
switch (source)
{
case VenueSource.Venue:
return(await venueCollectionService.GetDocumentByVenueId(venueId.Value));
case VenueSource.Location:
return(await venueCollectionService.GetDocumentByLocationId(locationId.Value, ukprn));
default: return(null);
}
}
CourseDirectory.Models.Models.Venues.VenueStatus MapVenueStatus(Venue venue)
{
//ignore record status for venues that do not have a postcode & migrate it over
//as pending.
if (string.IsNullOrEmpty(venue.Address?.Postcode))
{
return(CourseDirectory.Models.Models.Venues.VenueStatus.Pending);
}
switch (venue.RecordStatusId)
{
case TribalRecordStatus.Pending: return(CourseDirectory.Models.Models.Venues.VenueStatus.Pending);
case TribalRecordStatus.Live: return(CourseDirectory.Models.Models.Venues.VenueStatus.Live);
case TribalRecordStatus.Archived: return(CourseDirectory.Models.Models.Venues.VenueStatus.Archived);
case TribalRecordStatus.Deleted: return(CourseDirectory.Models.Models.Venues.VenueStatus.Deleted);
default: throw new Exception("$Unable to map recordStatus to VenueStatus");
}
}
byte[] GetResultAsByteArray(IList <ResultMessage> ob)
{
using (var memoryStream = new System.IO.MemoryStream())
{
using (var streamWriter = new System.IO.StreamWriter(memoryStream))
using (var csvWriter = new CsvWriter(streamWriter, CultureInfo.InvariantCulture))
{
csvWriter.WriteRecords <ResultMessage>(ob);
}
return(memoryStream.ToArray());
}
}
async Task WriteResultsToBlobStorage(byte[] data)
{
await blobhelper.UploadFile(blobContainer, venueExportFileName, data);
}
async Task <IList <int> > GetProviderWhiteList()
{
var list = new List <int>();
var whiteList = await blobhelper.ReadFileAsync(blobContainer, WHITE_LIST_FILE);
if (!string.IsNullOrEmpty(whiteList))
{
var lines = whiteList.Split(new[] { Environment.NewLine }, StringSplitOptions.None);
foreach (string line in lines)
{
if (int.TryParse(line, out int id))
{
list.Add(id);
}
}
}
return(list);
}
void AddResultMessage(int ukprn, int venueId, int?locationId, string status, string message = "")
{
var validateResult = new ResultMessage()
{
UKPRN = ukprn, VenueId = venueId, LocationId = locationId, Status = status, Message = message
};
result.Add(validateResult);
}
bool Validate(Venue item)
{
//are providers on list of whitelisted providers file
if (!whiteListProviders.Any(x => x == item.UKPRN))
{
AddResultMessage(item.UKPRN, item.VenueId, item.LocationID, "Failed", $"Provider {item.ProviderId} not on whitelist, ukprn {item.UKPRN}");
return(false);
}
if (!item.Address.Latitude.HasValue || !item.Address.Longitude.HasValue)
{
AddResultMessage(item.UKPRN, item.VenueId, item.LocationID, "Skiped", $"Skipped Location because Lat/Long are missing, {item.ProviderId} not on whitelist, ukprn {item.UKPRN}");
return(false);
}
////check to see if a record is already held for ukprn
//if (!ukprnCache.Contains(item.UKPRN))
//{
// var cosmosProvider = await providerCollectionService.ProviderExists(item.UKPRN);
// if (!cosmosProvider)
// {
// AddResultMessage(item.VenueId, item.LocationID, "Failed", "Unknown UKPRN");
// return false;
// }
// else
// {
// //provider exists - add to cache
// ukprnCache.Add(item.UKPRN);
// }
//}
return(true);
}
}
public static async Task Run(
string input, // Work around https://github.com/Azure/azure-functions-vs-build-sdk/issues/168
ILogger log,
[Inject] IConfigurationRoot configuration,
[Inject] IVenueCollectionService venueCollectionService,
[Inject] IProviderCollectionService providerCollectionService,
[Inject] ICosmosDbHelper cosmosDbHelper,
[Inject] IBlobStorageHelper blobhelper
)
{
var venuesCollectionId = configuration["CosmosDbCollectionSettings:VenuesCollectionId"];
var connectionString = configuration.GetConnectionString("TribalRestore");
var blobContainer = configuration["BlobStorageSettings:Container"];
var container = configuration["BlobStorageSettings:Container"];
var whiteListProviders = await GetProviderWhiteList();
var coursesCollectionId = "courses";
var apprenticeshipCollectionId = "apprenticeship";
var databaseId = configuration["CosmosDbSettings:DatabaseId"];
var documentClient = cosmosDbHelper.GetClient();
var venueCollectionUri = UriFactory.CreateDocumentCollectionUri(databaseId, venuesCollectionId);
var coursesCollectionUri = UriFactory.CreateDocumentCollectionUri(databaseId, coursesCollectionId);
var apprenticeshipCollectionUri = UriFactory.CreateDocumentCollectionUri(databaseId, apprenticeshipCollectionId);
var logFileName = $"ProvidersWithInvalidVenueReferences--{DateTime.Now.ToString("dd-MM-yy HHmm")}";
var result = new List <VenueReference>();
const string WHITE_LIST_FILE = "ProviderWhiteList.txt";
//counters
var ukprnsThatFailedToFetchVenues = 0;
var uniqueInvalidVenues = new HashSet <string>();
var replacedInvalidVenues = new HashSet <string>();
var allVenues = GetAllOldVenues();
//grand totals
var totalInvalidApprenticeshipLocationReferences = 0;
var totalInvalidCourseRunReferences = 0;
//provider scoped totals
var invalidCourseRunReferences = 0;
var invalidApprenticeshipLocationReferences = 0;
using (var logStream = new MemoryStream())
using (var logStreamWriter = new StreamWriter(logStream))
using (var logCsvWriter = new CsvWriter(logStreamWriter, CultureInfo.InvariantCulture))
{
//every provider
foreach (var ukprn in whiteListProviders)
{
try
{
//reset counters
invalidCourseRunReferences = 0;
invalidApprenticeshipLocationReferences = 0;
//fetch data for ukprn
var allCoursesForProvider = await GetCourses(ukprn);
var allApprenticeshipsForProvider = await GetApprenticeships(ukprn);
//courses
foreach (var course in allCoursesForProvider)
{
//course runs
foreach (var courserun in course.CourseRuns)
{
//only courses that references a venue (classroom based or both)
if (courserun.VenueId != null && courserun.VenueId != Guid.Empty)
{
//current venue & old venue (pre migration)
var currentVenue = await GetVenueById(courserun.VenueId?.ToString());
if (currentVenue != null)
{
if (course.ProviderUKPRN != currentVenue.UKPRN)
{
uniqueInvalidVenues.Add(courserun.VenueId.ToString());
invalidCourseRunReferences++;
}
result.Add(new VenueReference()
{
UKPRN = course.ProviderUKPRN,
VenueId = courserun.VenueId.ToString(),
VenueUKPRN = currentVenue.UKPRN,
Address1 = currentVenue.Address1,
Postcode = currentVenue.PostCode,
VenueName = currentVenue.VenueName,
UKPRNMatched = (course.ProviderUKPRN == currentVenue.UKPRN),
Message = (course.ProviderUKPRN == currentVenue.UKPRN) ? "Venue UKPRN Matches Course UKPRN" : "Venue UKPRN Does not match Course UKPRN",
Type = "Course",
CourseId = course.id,
CourseRunId = courserun.id
});
}
else
{
result.Add(new VenueReference()
{
UKPRN = course.ProviderUKPRN,
UKPRNMatched = false,
VenueUKPRN = -1,
VenueId = courserun.VenueId.ToString(),
Message = "VenueId does not exist in venues",
Type = "Course",
CourseId = course.id,
CourseRunId = courserun.id
});
}
}
}
//total for all providers
totalInvalidCourseRunReferences += invalidCourseRunReferences;
}
//apprenticeships
foreach (var apprenticeship in allApprenticeshipsForProvider)
{
//apprenticeship locations
foreach (var location in apprenticeship.ApprenticeshipLocations)
{
//only apprenticeshiplocations that references a venue (classroom based or both)
if (location.VenueId.HasValue && location.LocationGuidId.HasValue && location.LocationGuidId != Guid.Empty)
{
var currentVenue = await GetVenueById(location.LocationGuidId.ToString());
//venue exists in cosmos
if (currentVenue != null)
{
if (location.ProviderUKPRN != currentVenue.UKPRN)
{
uniqueInvalidVenues.Add(location.VenueId.ToString());
invalidApprenticeshipLocationReferences++;
}
//apprenticeshipId
result.Add(new VenueReference()
{
UKPRN = apprenticeship.ProviderUKPRN,
ApprenticeshipLocationUKPRN = location.ProviderUKPRN,
VenueId = location.LocationGuidId.ToString(),
VenueUKPRN = currentVenue.UKPRN,
Address1 = currentVenue.Address1,
Postcode = currentVenue.PostCode,
VenueName = currentVenue.VenueName,
UKPRNMatched = (apprenticeship.ProviderUKPRN == currentVenue.UKPRN),
Message = (apprenticeship.ProviderUKPRN == currentVenue.UKPRN) ? "Venue UKPRN Matches Apprenticeship UKPRN" : "Venue UKPRN Does not match Apprenticeship UKPRN",
Type = "Apprenticeship",
ApprenticeshipId = apprenticeship.id
});
}
else
{
result.Add(new VenueReference()
{
UKPRN = apprenticeship.ProviderUKPRN,
ApprenticeshipLocationUKPRN = location.ProviderUKPRN,
UKPRNMatched = false,
VenueUKPRN = -1,
VenueId = location.LocationGuidId.ToString(),
Type = "Apprenticeship",
Message = "VenueId does not exist in venues",
ApprenticeshipId = apprenticeship.id
});
}
}
}
totalInvalidApprenticeshipLocationReferences += invalidApprenticeshipLocationReferences;
}
//total for provider
Console.WriteLine($"{invalidCourseRunReferences} invalid venue references for {ukprn}");
Console.WriteLine($"{invalidApprenticeshipLocationReferences} invalid apprenticeship references for {ukprn}");
Console.WriteLine($"{uniqueInvalidVenues.Count()} unique venues");
}
catch (Exception e)
{
log.LogError(e.Message, e);
}
}
//block to try and fetch all venues for every provider
//to make sure that venues can be fetched without error.
foreach (var ukprn in whiteListProviders)
{
try
{
var venues = await GetVenues(ukprn);
}
catch (Exception e)
{
log.LogError(e.Message, e);
Console.WriteLine($"{ukprn} - failed to fetch venues");
ukprnsThatFailedToFetchVenues++;
}
}
//write venue reference documents
logCsvWriter.WriteHeader(typeof(VenueReference));
logCsvWriter.NextRecord();
foreach (var id in result)
{
logCsvWriter.WriteRecord(id);
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);
}
}
//
Console.WriteLine($"{totalInvalidCourseRunReferences} courserun invalid references in total");
Console.WriteLine($"{totalInvalidApprenticeshipLocationReferences} apprenticeship location invalid references in total");
Console.WriteLine($"{replacedInvalidVenues.Count()} venues have been reverted back to old venues");
Console.WriteLine($"{uniqueInvalidVenues.Count()} Venues were invalid");
Console.WriteLine($"{ukprnsThatFailedToFetchVenues} ukprns failed to fetch venues");
async Task <List <Course> > GetCourses(int ukprn)
{
var courses = new List <Course>();
string continuation = null;
do
{
var feedOptions = new FeedOptions()
{
RequestContinuation = continuation,
PartitionKey = new Microsoft.Azure.Documents.PartitionKey(ukprn)
};
var queryResponse = await documentClient.CreateDocumentQuery <Course>(coursesCollectionUri, feedOptions)
.Where(p => p.ProviderUKPRN == ukprn && p.CourseStatus != CourseDirectory.Models.Enums.RecordStatus.Archived)
.AsDocumentQuery()
.ExecuteNextAsync <Course>();
courses.AddRange(queryResponse.ToList());
continuation = queryResponse.ResponseContinuation;
}while (continuation != null);
return(courses);
}
async Task <List <Venue> > GetVenues(int ukprn)
{
var venues = new List <Venue>();
string continuation = null;
do
{
var feedOptions = new FeedOptions()
{
RequestContinuation = continuation
};
var queryResponse = await documentClient.CreateDocumentQuery <Venue>(venueCollectionUri, feedOptions)
.Where(p => p.UKPRN == ukprn)
.AsDocumentQuery()
.ExecuteNextAsync <Venue>();
venues.AddRange(queryResponse.ToList());
continuation = queryResponse.ResponseContinuation;
}while (continuation != null);
return(venues);
}
async Task <Venue> GetVenueById(string id)
{
var collectionLink = UriFactory.CreateDocumentCollectionUri(databaseId, venuesCollectionId);
var query = documentClient
.CreateDocumentQuery <Venue>(collectionLink, new FeedOptions()
{
EnableCrossPartitionQuery = true
})
.Where(d => d.ID == id)
.AsDocumentQuery();
return((await query.ExecuteNextAsync()).FirstOrDefault());
}
async Task <List <Apprenticeship> > GetApprenticeships(int ukprn)
{
var apprenticeships = new List <Apprenticeship>();
string continuation = null;
do
{
var feedOptions = new FeedOptions()
{
RequestContinuation = continuation,
PartitionKey = new Microsoft.Azure.Documents.PartitionKey(ukprn)
};
try
{
var queryResponse = await documentClient.CreateDocumentQuery <Apprenticeship>(apprenticeshipCollectionUri, feedOptions)
.Where(p => p.ProviderUKPRN == ukprn && p.RecordStatus != CourseDirectory.Models.Enums.RecordStatus.Archived)
.AsDocumentQuery()
.ExecuteNextAsync <Apprenticeship>();
apprenticeships.AddRange(queryResponse);
continuation = queryResponse.ResponseContinuation;
}
catch (Exception)
{
continuation = null;
}
}while (continuation != null);
return(apprenticeships);
}
async Task <ISet <int> > GetProviderWhiteList()
{
var blob = blobhelper.GetBlobContainer(blobContainer).GetBlockBlobReference(WHITE_LIST_FILE);
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);
}
IList <Venue> GetAllOldVenues()
{
var list = new List <Venue>();
var lookupFileResourceName = "Dfc.ProviderPortal.TribalExporter.2020-01-24_0325-venues-backup.json";
using (var stream = typeof(VenueReferenceChecker).Assembly.GetManifestResourceStream(lookupFileResourceName))
using (StreamReader file = new StreamReader(stream))
{
using (JsonTextReader reader = new JsonTextReader(file))
{
while (reader.Read())
{
JArray o2 = (JArray)JToken.ReadFrom(reader);
foreach (var item in o2)
{
Venue v = item.ToObject <Venue>();
list.Add(v);
}
}
}
}
return(list);
}
}
public static async Task Run(
string input, // Work around https://github.com/Azure/azure-functions-vs-build-sdk/issues/168
ILogger logger,
[Inject] IConfigurationRoot configuration,
[Inject] IVenueCollectionService venueCollectionService,
[Inject] IProviderCollectionService providerCollectionService,
[Inject] ICosmosDbHelper cosmosDbHelper,
[Inject] IBlobStorageHelper blobhelper
)
{
logger.LogDebug("VenueRestorer: Starting...");
var connectionString = configuration.GetConnectionString("TribalRestore");
var venuesCollectionId = configuration["CosmosDbCollectionSettings:VenuesCollectionId"];
var blobContainer = configuration["BlobStorageSettings:Container"];
var container = configuration["BlobStorageSettings:Container"];
var whiteListProviders = await GetProviderWhiteList();
logger.LogDebug($"VenueRestorer: {whiteListProviders.Count} white-listed providers to process");
var coursesCollectionId = "courses";
var apprenticeshipCollectionId = "apprenticeship";
var databaseId = configuration["CosmosDbSettings:DatabaseId"];
var documentClient = cosmosDbHelper.GetClient();
var venueCollectionUri = UriFactory.CreateDocumentCollectionUri(databaseId, venuesCollectionId);
var venueCollection_OldUri = UriFactory.CreateDocumentCollectionUri(databaseId, "venues_old");
var coursesCollectionUri = UriFactory.CreateDocumentCollectionUri(databaseId, coursesCollectionId);
var apprenticeshipCollectionUri = UriFactory.CreateDocumentCollectionUri(databaseId, apprenticeshipCollectionId);
var logFileName = $"VenueRestorer--{DateTime.Now.ToString("dd-MM-yy HHmm")}";
var allVenues = GetAllOldVenues();
var result = new List <ResultMessage>();
const string WHITE_LIST_FILE = "ProviderWhiteList.txt";
var ukprnCache = new List <int>();
var updatedBy = "VenueRestorer";
//grand totals
var invalidCourseRunReferences = 0;
var totalInvalidCourseRunReferences = 0;
var uniqueInvalidVenues = new HashSet <string>();
var replacedInvalidVenues = new HashSet <Venue>();
var references = new List <VenueRestorerReference>();
var rereferencedApprenticeshipLocations = 0;
//provider scoped totals
var totalInvalidApprenticeshipLocationReferences = 0;
var invalidApprenticeshipLocationReferences = 0;
int processedProviderCount = 0;
using (var logStream = new MemoryStream())
using (var logStreamWriter = new StreamWriter(logStream))
using (var logCsvWriter = new CsvWriter(logStreamWriter, CultureInfo.InvariantCulture))
{
foreach (var ukprn in whiteListProviders)
{
//reference for old venue so that courseruns & apprenticeship locations can be
//re-referenced
var venuesReplacedForProvider = new List <Tuple <Venue, Venue, Guid> >();
try
{
//reset counters
invalidCourseRunReferences = 0;
invalidApprenticeshipLocationReferences = 0;
//fetch data for ukprn
var allCoursesForProvider = await GetCourses(ukprn);
var allApprenticeshipsForProvider = await GetApprenticeships(ukprn);
var venues = await GetVenues(ukprn);
var old_venues = GetOldVenues(ukprn);
//courses
foreach (var course in allCoursesForProvider)
{
//course runs
foreach (var courserun in course.CourseRuns)
{
//only courses that references a venue (classroom based or both)
if (courserun.VenueId != null && courserun.VenueId != Guid.Empty)
{
//current venue & old venue (pre migration)
var invalidReferencedVenue = await GetVenueById(courserun.VenueId?.ToString());
var restoredVenue = old_venues.FirstOrDefault(x => new Guid(x.ID) == courserun.VenueId);
//if current venues provider is different to course then attempt to replace it with
//old venue from cosmos backup.
if (invalidReferencedVenue != null && invalidReferencedVenue.UKPRN != course.ProviderUKPRN)
{
//replace existing venue with old venue if a match is found
if (restoredVenue != null && restoredVenue.UKPRN == course.ProviderUKPRN)
{
await ReplaceVenue(invalidReferencedVenue.ID, restoredVenue, updatedBy);
//the venue that was referenced needs to be inserted again but with a new id.
var newId = Guid.NewGuid();
await ReplaceVenue(newId.ToString(), invalidReferencedVenue, updatedBy);
//reload venues as we have just replaced a venue
venues = await GetVenues(ukprn);
replacedInvalidVenues.Add(invalidReferencedVenue);
//store old venue so that apprenticeship locations can be re-referenced
venuesReplacedForProvider.Add(Tuple.Create(restoredVenue, invalidReferencedVenue, newId));
//log changes
references.Add(new VenueRestorerReference()
{
UKPRN = course.ProviderUKPRN,
VenueId = courserun.VenueId.ToString(),
CurrentVenueUKPRN = invalidReferencedVenue.UKPRN,
CurrentAddress1 = invalidReferencedVenue.Address1,
CurrentPostcode = invalidReferencedVenue.PostCode,
CurrentVenueName = invalidReferencedVenue.VenueName,
RestoredAddress1 = restoredVenue.Address1,
RestoredVenueName = restoredVenue.VenueName,
RestoredPostcode = restoredVenue.PostCode,
RestoredVenueUKPRN = restoredVenue.UKPRN,
UKPRNMatched = (course.ProviderUKPRN == invalidReferencedVenue.UKPRN),
Message = "Replaced Venue",
Type = "Course",
CourseId = course.id,
CourseRunId = courserun.id,
});
}
else
{
references.Add(new VenueRestorerReference()
{
UKPRN = course.ProviderUKPRN,
VenueId = courserun.VenueId.ToString(),
CurrentVenueUKPRN = invalidReferencedVenue.UKPRN,
CurrentAddress1 = invalidReferencedVenue.Address1,
CurrentPostcode = invalidReferencedVenue.PostCode,
CurrentVenueName = invalidReferencedVenue.VenueName,
UKPRNMatched = (course.ProviderUKPRN == invalidReferencedVenue.UKPRN),
Message = "Unable to replace Venue, as old venue was not found in backup",
Type = "Course",
CourseId = course.id,
CourseRunId = courserun.id,
});
}
//invalid references
uniqueInvalidVenues.Add(invalidReferencedVenue.ID);
invalidCourseRunReferences++;
}
}
}
//total for all providers
totalInvalidCourseRunReferences += invalidCourseRunReferences;
}
//apprenticeships
foreach (var apprenticeship in allApprenticeshipsForProvider)
{
//apprenticeship locations
foreach (var location in apprenticeship.ApprenticeshipLocations)
{
//only apprenticeshiplocations that references a venue (classroom based or both)
if (location.VenueId.HasValue && location.LocationGuidId.HasValue && location.LocationGuidId != Guid.Empty)
{
var invalidReferencedVenue = await GetVenueById(location.LocationGuidId?.ToString());
if (invalidReferencedVenue != null && invalidReferencedVenue.UKPRN != apprenticeship.ProviderUKPRN)
{
var restoredVenue = old_venues.FirstOrDefault(x => new Guid(x.ID) == location.LocationGuidId);
//replace existing venue with old venue if a match is found
if (restoredVenue != null && restoredVenue.UKPRN == apprenticeship.ProviderUKPRN)
{
logger.LogDebug($"VenueRestorer: Invalid Apprenticeship location, apprenticeship should reference {restoredVenue.VenueName}");
//old venue from json backup is the correct venue that should be referenced
//swap invalid venue, with restoredVenue
await ReplaceVenue(invalidReferencedVenue.ID, restoredVenue, updatedBy);
//the venue that was referenced needs to be inserted again but with a new id.
var newId = Guid.NewGuid();
await ReplaceVenue(newId.ToString(), invalidReferencedVenue, updatedBy);
//store old venue so that apprenticeship locations can be re-referenced
venuesReplacedForProvider.Add(Tuple.Create(restoredVenue, invalidReferencedVenue, newId));
//reload venues as we have just replaced a venue
venues = await GetVenues(ukprn);
//keep a track of the incorrect venue, these will be inserted with a new id.
replacedInvalidVenues.Add(invalidReferencedVenue);
references.Add(new VenueRestorerReference()
{
UKPRN = apprenticeship.ProviderUKPRN,
ApprenticeshipLocationUKPRN = location.ProviderUKPRN,
VenueId = location.LocationGuidId.ToString(),
CurrentVenueUKPRN = invalidReferencedVenue.UKPRN,
CurrentAddress1 = invalidReferencedVenue.Address1,
CurrentPostcode = invalidReferencedVenue.PostCode,
CurrentVenueName = invalidReferencedVenue.VenueName,
RestoredVenueUKPRN = restoredVenue.UKPRN,
RestoredAddress1 = restoredVenue?.Address1,
RestoredPostcode = restoredVenue?.PostCode,
RestoredVenueName = restoredVenue.VenueName,
UKPRNMatched = (apprenticeship.ProviderUKPRN == invalidReferencedVenue.UKPRN),
Message = "Replaced Venue",
Type = "Apprenticeship",
ApprenticeshipId = apprenticeship.id
});
}
else
{
references.Add(new VenueRestorerReference()
{
UKPRN = apprenticeship.ProviderUKPRN,
ApprenticeshipLocationUKPRN = location.ProviderUKPRN,
UKPRNMatched = false,
CurrentVenueUKPRN = -1,
VenueId = location.LocationGuidId.ToString(),
Type = "Apprenticeship",
Message = "Unable to replace Venue, as old venue was not found in backup",
ApprenticeshipId = apprenticeship.id
});
}
}
}
}
totalInvalidApprenticeshipLocationReferences += invalidApprenticeshipLocationReferences;
}
//rereference apprenticeship locations
//if there is a venue that has been replaced but is referenced by an apprenticeshiplocation then the apprenticeship
//record needs to be updated to point the the new venue record to save data loss.
foreach (var apprenticeship in allApprenticeshipsForProvider)
{
var updated = false;
foreach (var location in apprenticeship.ApprenticeshipLocations)
{
var replacedVenue = venuesReplacedForProvider.FirstOrDefault(x => new Guid(x.Item2.ID) == location.LocationGuidId);
if (replacedVenue != null)
{
updated = true;
location.LocationGuidId = replacedVenue.Item3;
}
}
if (updated)
{
var documentLink = UriFactory.CreateDocumentUri(databaseId, apprenticeshipCollectionId, apprenticeship.id.ToString());
await documentClient.ReplaceDocumentAsync(documentLink, apprenticeship, new RequestOptions()
{
PartitionKey = new Microsoft.Azure.Documents.PartitionKey(ukprn)
});
}
}
}
catch (Exception e)
{
logger.LogError($"VenueRestorer: error for ukprn {ukprn}: {e.Message}", e);
}
processedProviderCount++;
if (processedProviderCount % 100 == 0)
{
logger.LogInformation($"VenueRestorer: {processedProviderCount}/{whiteListProviders.Count} providers processed.");
}
logger.LogDebug(
$"VenueRestorer: completed for UKPRN {ukprn}, {processedProviderCount}/{whiteListProviders.Count}. " +
$"{invalidCourseRunReferences} invalid venue references, " +
$"{invalidApprenticeshipLocationReferences} invalid apprenticeship references, " +
$"{rereferencedApprenticeshipLocations} apprenticeship locations were re-referenced.");
}
//write csv file
logCsvWriter.WriteHeader(typeof(VenueRestorerReference));
logCsvWriter.NextRecord();
foreach (var reference in references)
{
logCsvWriter.WriteRecord(reference);
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);
logger.LogInformation($"VenueRestorer: log uploaded as {logFileName}");
}
logger.LogInformation(
$"VenueRestorer: completed. " +
$"{totalInvalidCourseRunReferences} invalid CourseRun references in total, " +
$"{totalInvalidApprenticeshipLocationReferences} Apprenticeship location invalid references in total, " +
$"{replacedInvalidVenues.Count()} Venues have been reverted back to old venues, " +
$"{uniqueInvalidVenues.Count()} Venues were invalid");
async Task <List <Course> > GetCourses(int ukprn)
{
var courses = new List <Course>();
//Get all courses
string continuation = null;
do
{
var feedOptions = new FeedOptions()
{
RequestContinuation = continuation,
PartitionKey = new Microsoft.Azure.Documents.PartitionKey(ukprn)
};
var queryResponse = await documentClient.CreateDocumentQuery <Course>(coursesCollectionUri, feedOptions)
.Where(p => p.ProviderUKPRN == ukprn && p.CourseStatus != CourseDirectory.Models.Enums.RecordStatus.Archived)
.AsDocumentQuery()
.ExecuteNextAsync <Course>();
courses.AddRange(queryResponse.ToList());
continuation = queryResponse.ResponseContinuation;
}while (continuation != null);
return(courses);
}
async Task <List <Venue> > GetVenues(int ukprn)
{
var venues = new List <Venue>();
string continuation = null;
do
{
var feedOptions = new FeedOptions()
{
RequestContinuation = continuation
};
var queryResponse = await documentClient.CreateDocumentQuery <Venue>(venueCollectionUri, feedOptions)
.Where(p => p.UKPRN == ukprn)
.AsDocumentQuery()
.ExecuteNextAsync <Venue>();
venues.AddRange(queryResponse.ToList());
continuation = queryResponse.ResponseContinuation;
}while (continuation != null);
return(venues);
}
List <Venue> GetOldVenues(int ukprn)
{
return(allVenues.Where(x => x.UKPRN == ukprn).ToList());
}
async Task <Venue> GetVenueById(string id)
{
var collectionLink = UriFactory.CreateDocumentCollectionUri(databaseId, venuesCollectionId);
var query = documentClient
.CreateDocumentQuery <Venue>(collectionLink, new FeedOptions()
{
EnableCrossPartitionQuery = true
})
.Where(d => d.ID == id)
.AsDocumentQuery();
return((await query.ExecuteNextAsync()).FirstOrDefault());
}
async Task <List <Apprenticeship> > GetApprenticeships(int ukprn)
{
var apprenticeships = new List <Apprenticeship>();
string continuation = null;
do
{
var feedOptions = new FeedOptions()
{
RequestContinuation = continuation,
PartitionKey = new Microsoft.Azure.Documents.PartitionKey(ukprn)
};
try
{
var queryResponse = await documentClient.CreateDocumentQuery <Apprenticeship>(apprenticeshipCollectionUri, feedOptions)
.Where(p => p.ProviderUKPRN == ukprn && p.RecordStatus != CourseDirectory.Models.Enums.RecordStatus.Archived)
.AsDocumentQuery()
.ExecuteNextAsync <Apprenticeship>();
apprenticeships.AddRange(queryResponse);
continuation = queryResponse.ResponseContinuation;
}
catch (Exception)
{
continuation = null;
}
}while (continuation != null);
return(apprenticeships);
}
async Task <ISet <int> > GetProviderWhiteList()
{
var blob = blobhelper.GetBlobContainer(blobContainer).GetBlockBlobReference(WHITE_LIST_FILE);
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 ReplaceVenue(string id, Venue matchedVenue, string updatedby)
{
Uri collectionUri = UriFactory.CreateDocumentCollectionUri(databaseId, venuesCollectionId);
var editedVenue = new Dfc.CourseDirectory.Models.Models.Venues.Venue()
{
ID = id,
UKPRN = matchedVenue.UKPRN,
VenueName = matchedVenue.VenueName,
Address1 = matchedVenue.Address1,
Address2 = matchedVenue.Address2,
Town = matchedVenue.Town,
County = matchedVenue.County,
PostCode = matchedVenue.PostCode,
Latitude = matchedVenue.Latitude,
Longitude = matchedVenue.Longitude,
Status = matchedVenue.Status,
UpdatedBy = updatedBy,
DateUpdated = matchedVenue.DateUpdated,
VenueID = matchedVenue.VenueID,
ProviderID = matchedVenue.ProviderID,
ProvVenueID = matchedVenue.ProvVenueID,
Email = matchedVenue.Email,
Website = matchedVenue.Website,
Telephone = matchedVenue.Telephone,
CreatedBy = matchedVenue.CreatedBy,
CreatedDate = DateTime.Now,
LocationId = matchedVenue.LocationId,
TribalLocationId = matchedVenue.TribalLocationId
};
await documentClient.UpsertDocumentAsync(collectionUri, editedVenue);
}
IList <Venue> GetAllOldVenues()
{
var list = new List <Venue>();
var lookupFileResourceName = "Dfc.ProviderPortal.TribalExporter.2020-01-24_0325-venues-backup.json";
using (var stream = typeof(VenueReferenceChecker).Assembly.GetManifestResourceStream(lookupFileResourceName))
using (StreamReader file = new StreamReader(stream))
{
using (JsonTextReader reader = new JsonTextReader(file))
{
while (reader.Read())
{
JArray o2 = (JArray)JToken.ReadFrom(reader);
foreach (var item in o2)
{
Venue v = item.ToObject <Venue>();
list.Add(v);
}
}
}
}
return(list);
}
}
public static async Task Run(
string input, // Work around https://github.com/Azure/azure-functions-vs-build-sdk/issues/168
ILogger log,
[Inject] IConfigurationRoot configuration,
[Inject] IProviderCollectionService providerCollectionService,
[Inject] ICosmosDbHelper cosmosDbHelper,
[Inject] IBlobStorageHelper blobhelper,
[Inject] IApprenticeReferenceDataService apprenticeReferenceDataService,
[Inject] IApprenticeshipServiceWrapper apprenticeshipService,
[Inject] IVenueCollectionService venueCollectionService,
[Inject] IOnspdService onspdService
)
{
var apprenticeshipCollectionId = configuration["CosmosDbCollectionSettings:ApprenticeshipCollectionId"];
var connectionString = configuration.GetConnectionString("TribalRestore");
var blobContainer = blobhelper.GetBlobContainer(configuration["BlobStorageSettings:Container"]);
var whiteListProviders = await GetProviderWhiteList();
var result = new List <ApprenticeshipResultMessage>();
var venueExportFileName = $"ApprenticeshipExport-{DateTime.Now.ToString("dd-MM-yy HHmm")}";
const string WHITE_LIST_FILE = "ProviderWhiteList-Apprenticeships.txt";
var ukprnCache = new List <int>();
var databaseId = configuration["CosmosDbSettings:DatabaseId"];
var apprenticeshipList = new List <ApprenticeshipResult>();
var createdBy = "ApprenticeshipMigrator";
var createdDate = DateTime.Now;
SemaphoreSlim semaphore = new SemaphoreSlim(5);
var client = cosmosDbHelper.GetClient();
var apprenticeshipSQL = @"SELECT a.ApprenticeshipId,
p.ProviderId,
a.FrameworkCode,
a.ProgType,
a.PathwayCode,
a.StandardCode,
a.[Version],
a.MarketingInformation,
a.[Url],
a.ContactEmail,
a.ContactTelephone,
a.ContactWebsite,
a.RecordStatusId,
a.CreatedByUserId,
a.CreatedDateTimeUtc,
p.Ukprn,
coalesce(s.StandardName,f.NasTitle) as [ApprenticeshipTitle],
s.NotionalEndLevel
FROM Apprenticeship a
INNER JOIN Provider p on p.ProviderId = a.ProviderId
LEFT JOIN [Standard] s on (s.StandardCode = a.StandardCode and s.Version = a.Version)
LEFT jOIN [Framework] f on (f.FrameworkCode = a.FrameworkCode AND f.PathwayCode = a.PathwayCode AND f.ProgType = a.ProgType)
WHERE a.recordStatusId=2
ORDER BY ProviderId
";
var apprenticeshipLocationsSQL = @"SELECT al.ApprenticeshipId,
al.ApprenticeshipLocationId,
l.LocationId,
a.AddressId,
a.AddressLine1,
a.AddressLine2,
a.County,
a.Postcode,
a.Town,
a.Longitude,
a.Latitude,
l.Website,
l.Email,
als.CSV as DeliveryModeStr,
l.Telephone,
l.LocationName,
p.ProviderId,
p.Ukprn,
al.Radius
FROM ApprenticeshipLocation al
INNER JOIN Location l on l.LocationId = al.LocationId
INNER JOIN Provider p on p.ProviderId = l.ProviderId
INNER JOIN Address a ON a.AddressId = l.AddressId
CROSS APPLY (SELECT STRING_AGG(DeliveryModeId,',') as CSV,
aldm.ApprenticeshipLocationId
FROM ApprenticeshipLocationDeliveryMode aldm
WHERE ApprenticeshipLocationId = al.ApprenticeshipLocationId
GROUP BY aldm.ApprenticeshipLocationId
) als
WHERE al.RecordStatusId = 2 and
al.ApprenticeshipId = @ApprenticeshipId
ORDER BY ApprenticeshipId,ApprenticeshipLocationId";
try
{
using (var conn1 = new SqlConnection(connectionString))
using (var apprenticeshipscmd = conn1.CreateCommand())
{
await conn1.OpenAsync();
apprenticeshipscmd.CommandText = apprenticeshipSQL;
using (var apprenticeshipReader = apprenticeshipscmd.ExecuteReader())
{
while (await apprenticeshipReader.ReadAsync())
{
apprenticeshipList.Add(ApprenticeshipResult.FromDataReader(apprenticeshipReader));
}
}
}
}
catch (Exception e)
{
AddResultMessage(0, 0, "Failed", null, e.Message);
log.LogError("Error occured Migrating Apprenticeships", e.Message);
}
await Task.WhenAll(apprenticeshipList.Select(async apprenticeship =>
{
var apprenticeshipErrors = new List <string>(); //Errors Here cause apprenticeships to go into pending
var apprenticeshipWarning = new List <string>(); //informational messages
await semaphore.WaitAsync();
try
{
if (IsOnWhiteList(apprenticeship.UKPRN))
{
apprenticeshipErrors = new List <string>();
//get relevant info
var exisitingApprenticeship = await GetExistingApprenticeship(apprenticeship);
var referenceDataFramework = await GetReferenceDataFramework(apprenticeship);
var referenceDataStandard = await GetReferenceDataStandard(apprenticeship);
var locations = await GetLocations(apprenticeship);
var cosmosVenues = await GetCosmosVenues(locations);
var cosmosProvider = await GetProvider(apprenticeship);
//map objects for creating cosmos record
var locs = MapLocations(locations, cosmosVenues);
var id = exisitingApprenticeship?.id.ToString() ?? Guid.NewGuid().ToString();
var apprenticeType = MapApprenticeshipType(apprenticeship);
//check to see if framework code/standard code is valid
VerifiyIfStandardOrFramework(apprenticeship, referenceDataFramework, referenceDataStandard);
var mappedStatus = MapApprenticeshipRecordStatus(locs);
var mappedApprenticeship = MapApprenticeship(locs,
id,
apprenticeship,
apprenticeType,
mappedStatus,
referenceDataFramework?.Id.ToString(),
referenceDataStandard?.id.ToString(),
cosmosProvider?.id.ToString());
//insert record into cosmos
await CreateOrUpdateApprenticeshipRecord(mappedApprenticeship);
//log message to output
AddResultMessage(apprenticeship.ApprenticeshipID,
apprenticeship.UKPRN,
Enum.GetName(typeof(RecordStatus),
mappedApprenticeship.RecordStatus),
mappedApprenticeship.ApprenticeshipTitle,
string.Join("\n", apprenticeshipErrors),
string.Join("\n", apprenticeshipWarning));
}
else
{
AddResultMessage(apprenticeship.ApprenticeshipID, apprenticeship.UKPRN, "Skipped", null, $"PRN {apprenticeship.UKPRN} not whitelisted");
}
}
catch (Exception e)
{
AddResultMessage(apprenticeship.ApprenticeshipID, apprenticeship.UKPRN, "Failed", null, $"Exception occured creating record - {e.Message}");
log.LogError("Error occurred creating or updating apprenticeship record!", e);
}
finally
{
semaphore.Release();
}
void VerifiyIfStandardOrFramework(ApprenticeshipResult tribalRecord, ReferenceDataFramework refDataFramework, ReferenceDateStandard refDataStandard)
{
if (refDataFramework == null && refDataStandard == null)
{
apprenticeshipWarning.Add($"Standard/Framework code does not exist - framework code {tribalRecord.FrameworkCode}, pathway code: {tribalRecord.PathWayCode}, standard code: {tribalRecord.StandardCode}, version: {tribalRecord.Version}");
}
}
ApprenticeshipDTO MapApprenticeship(IList <ApprenticeshipLocationDTO> locs, string id, ApprenticeshipResult tribalRecord, ApprenticeshipType apprenticeshipTye,
RecordStatus recordStatus, string frameworkId, string standardId, string providerId)
{
var cosmosApprenticeship = new ApprenticeshipDTO()
{
id = id,
ApprenticeshipId = tribalRecord.ApprenticeshipID,
ApprenticeshipTitle = tribalRecord.ApprenticeshipTitle,
ProviderId = providerId,
PathWayCode = tribalRecord.PathWayCode,
ProgType = tribalRecord.ProgType,
ProviderUKPRN = tribalRecord.UKPRN,
FrameworkId = frameworkId,
StandardId = standardId,
FrameworkCode = tribalRecord.FrameworkCode,
StandardCode = tribalRecord.StandardCode,
Version = tribalRecord.Version,
MarketingInformation = tribalRecord.MarketingInformation,
Url = tribalRecord.Url,
ContactTelephone = tribalRecord.ContactTelephone,
ContactEmail = tribalRecord.ContactEmail,
ContactWebsite = tribalRecord.ContactWebsite,
CreatedBy = createdBy,
CreatedDate = createdDate,
NotionalNVQLevelv2 = tribalRecord.NotionalEndLevel,
ApprenticeshipLocations = locs,
ApprenticeshipType = apprenticeshipTye,
RecordStatus = recordStatus
};
return(cosmosApprenticeship);
}
async Task <IList <Dfc.CourseDirectory.Models.Models.Venues.Venue> > GetCosmosVenues(IList <ApprenticeshipLocationResult> locations)
{
IList <Dfc.CourseDirectory.Models.Models.Venues.Venue> lst = new List <Dfc.CourseDirectory.Models.Models.Venues.Venue>();
foreach (var s in locations)
{
var venue = await venueCollectionService.GetDocumentByLocationId(s.LocationId, s.UKPRN);
if (venue != null)
{
lst.Add(venue);
}
}
return(lst);
}
async Task <Provider> GetProvider(ApprenticeshipResult item)
{
return(await providerCollectionService.GetDocumentByUkprn(item.UKPRN));
}
async Task <List <ApprenticeshipLocationResult> > GetLocations(ApprenticeshipResult item)
{
using (var sqlConnection = new SqlConnection(connectionString))
{
var lst = await sqlConnection.QueryAsync <ApprenticeshipLocationResult>(apprenticeshipLocationsSQL, new { apprenticeshipId = item.ApprenticeshipID }, commandType: CommandType.Text);
return(lst.ToList());
}
}
async Task <ReferenceDateStandard> GetReferenceDataStandard(ApprenticeshipResult item)
{
var app = await apprenticeReferenceDataService.GetStandardById(item.StandardCode ?? 0, item.Version ?? 0);
return(app?.Value?.Value);
}
async Task <ReferenceDataFramework> GetReferenceDataFramework(ApprenticeshipResult item)
{
//checks for framework apprenticeship
var app = await apprenticeReferenceDataService.GetFrameworkByCode(item.FrameworkCode ?? 0, item.ProgType ?? 0, item.PathWayCode ?? 0);
return(app?.Value?.Value);
}
async Task <Apprenticeship> GetExistingApprenticeship(ApprenticeshipResult item)
{
//fetch existing apprenticeship row.
return(await apprenticeshipService.GetApprenticeshipByApprenticeshipID(item.ApprenticeshipID));
}
async Task CreateOrUpdateApprenticeshipRecord(ApprenticeshipDTO app)
{
var s = UriFactory.CreateDocumentUri(databaseId, apprenticeshipCollectionId, app.id);
Uri collectionUri = UriFactory.CreateDocumentCollectionUri(databaseId, apprenticeshipCollectionId);
var res = await client.UpsertDocumentAsync(collectionUri, app);
}
RecordStatus MapApprenticeshipRecordStatus(IList <ApprenticeshipLocationDTO> mappedLocation)
{
//if there are any errors with apprenticeshipREcord, set record to migration pending.
if (apprenticeshipErrors.Any())
{
return(RecordStatus.MigrationPending);
}
else
{
return(RecordStatus.Live);
}
}
//Taken entirely from previous migration logic.
ApprenticeshipLocationType GetApprenticeshipLocationType(ApprenticeshipLocationResult lo)
{
var deliveryModes = lo.DeliveryModes;
if ((deliveryModes.Contains(1) && !deliveryModes.Contains(2) && deliveryModes.Contains(3)) ||
(deliveryModes.Contains(1) && deliveryModes.Contains(2) && !deliveryModes.Contains(3)) ||
(deliveryModes.Contains(1) && deliveryModes.Contains(2) && deliveryModes.Contains(3)))
{
return(ApprenticeshipLocationType.ClassroomBasedAndEmployerBased);
}
else if ((!deliveryModes.Contains(1) && !deliveryModes.Contains(2) && deliveryModes.Contains(3)) ||
(!deliveryModes.Contains(1) && deliveryModes.Contains(2) && !deliveryModes.Contains(3)) ||
(!deliveryModes.Contains(1) && deliveryModes.Contains(2) && deliveryModes.Contains(3)))
{
return(ApprenticeshipLocationType.ClassroomBased);
}
else if (deliveryModes.Contains(1) && !deliveryModes.Contains(2) && !deliveryModes.Contains(3))
{
return(ApprenticeshipLocationType.EmployerBased);
}
else
{
return(ApprenticeshipLocationType.Undefined);
}
}
ApprenticeshipType MapApprenticeshipType(ApprenticeshipResult tribalRecord)
{
if (tribalRecord.StandardCode.HasValue)
{
return(ApprenticeshipType.StandardCode);
}
else if (tribalRecord.FrameworkCode.HasValue)
{
return(ApprenticeshipType.FrameworkCode);
}
else
{
apprenticeshipWarning.Add($"ApprenticeshipId: {tribalRecord.ApprenticeshipID} has undefined apprenticeshipType");
return(ApprenticeshipType.Undefined);
}
}
IList <ApprenticeshipLocationDTO> MapLocations(IList <ApprenticeshipLocationResult> locations, IList <Dfc.CourseDirectory.Models.Models.Venues.Venue> venues)
{
var locationBasedApprenticeshipLocation = new List <ApprenticeshipLocationDTO>();
var regionBasedApprenticeshipLocation = new List <ApprenticeshipLocationDTO>();
//no need to proceed
if (locations == null)
{
return(null);
}
//employer based apprenticeships - group all locations into regions/subregions
foreach (var location in locations)
{
var type = GetApprenticeshipLocationType(location);
if (type == ApprenticeshipLocationType.EmployerBased)
{
var allRegionsWithSubRegions = new SelectRegionModel();
var onspdRegionSubregion = onspdService.GetOnspdData(new OnspdSearchCriteria(location.Postcode));
if (onspdRegionSubregion.IsFailure)
{
apprenticeshipWarning.Add($"LocationId: {location.LocationId} - Querying onspd failed - {onspdRegionSubregion.Error}");
continue;
}
else if (!onspdRegionSubregion.HasValue)
{
apprenticeshipWarning.Add($"Location:{location.LocationId} - Did not find a record for postcode: {location.Postcode}");
continue;
}
var selectedSubRegion = allRegionsWithSubRegions.RegionItems.SelectMany(sr => sr.SubRegion.Where(sb =>
sb.SubRegionName == onspdRegionSubregion.Value.Value.LocalAuthority ||
sb.SubRegionName == onspdRegionSubregion.Value.Value.County ||
onspdRegionSubregion.Value.Value.LocalAuthority.Contains(sb.SubRegionName)
)).FirstOrDefault();
if (selectedSubRegion == null)
{
apprenticeshipWarning.Add($"Location:{location.LocationId} Unable to match region with ons data api, location skipped");
continue;
}
else
{
var appLocation = new ApprenticeshipLocationDTO()
{
Id = Guid.NewGuid().ToString(),
VenueId = Guid.Empty.ToString(),
TribalId = location.ApprenticeshipLocationId,
DeliveryModes = location.DeliveryModes,
LocationId = selectedSubRegion.ApiLocationId,
Name = location.LocationName,
ProviderId = location.ProviderId,
ProviderUKPRN = location.UKPRN,
Radius = location.Radius,
ApprenticeshipLocationType = type,
LocationType = LocationType.SubRegion,
LocationGuidId = null,
Regions = new List <string> {
selectedSubRegion.Id
},
RecordStatus = VenueStatus.Live,
CreatedBy = createdBy,
CreatedDate = createdDate,
UpdatedBy = createdBy,
UpdatedDate = createdDate
};
//region based apprenticeships
regionBasedApprenticeshipLocation.Add(appLocation);
}
}
else if (type == ApprenticeshipLocationType.ClassroomBased || type == ApprenticeshipLocationType.ClassroomBasedAndEmployerBased)
{
//venue based (location based apprenticeships)
var cosmosVenueItem = venues.FirstOrDefault(x => x.LocationId == location.LocationId);
var status = default(VenueStatus);
//set status be that of what the venue status is, otherwise if venue is not found
//set status to pending.
if (cosmosVenueItem != null)
{
status = cosmosVenueItem.Status;
}
else
{
apprenticeshipWarning.Add($"LocationId: {location.LocationId} did not find a venue in cosmos, record marked as pending");
continue;
}
var appLocation = new ApprenticeshipLocationDTO()
{
Id = Guid.NewGuid().ToString(),
VenueId = Guid.Empty.ToString(),
TribalId = location.ApprenticeshipLocationId,
Address = new Dfc.CourseDirectory.Models.Models.Apprenticeships.Address()
{
Address1 = cosmosVenueItem?.Address1,
Address2 = cosmosVenueItem?.Address2,
County = cosmosVenueItem?.County,
Email = cosmosVenueItem?.Email,
Website = cosmosVenueItem?.Website,
Longitude = cosmosVenueItem?.Longitude,
Latitude = cosmosVenueItem?.Latitude,
Postcode = cosmosVenueItem?.PostCode,
Town = cosmosVenueItem?.Town,
Phone = cosmosVenueItem?.Telephone
},
DeliveryModes = location.DeliveryModes,
LocationId = location.LocationId,
Name = location.LocationName,
ProviderId = location.ProviderId,
ProviderUKPRN = location.UKPRN,
Radius = location.Radius,
ApprenticeshipLocationType = type,
LocationType = LocationType.Venue,
LocationGuidId = cosmosVenueItem?.ID,
Regions = null,
RecordStatus = status,
CreatedBy = createdBy,
CreatedDate = createdDate,
UpdatedBy = createdBy,
UpdatedDate = createdDate
};
locationBasedApprenticeshipLocation.Add(appLocation);
}
else
{
apprenticeshipWarning.Add($"LocationId: {location.LocationId} skipped as type was unknown {type}");
continue;
}
}
//add a new location with all distinct regions.
if (regionBasedApprenticeshipLocation.Any(x => x.RecordStatus == VenueStatus.Live))
{
var regionLocation = regionBasedApprenticeshipLocation.FirstOrDefault(x => x.RecordStatus == VenueStatus.Live);
regionLocation.Regions = regionBasedApprenticeshipLocation.Where(x => x.Regions != null).SelectMany(x => x.Regions).Distinct().ToList();
locationBasedApprenticeshipLocation.Add(regionLocation);
}
return(locationBasedApprenticeshipLocation);
}
}));
//Log Results to blob storage
var resultsObjBytes = GetResultAsByteArray(result);
await WriteResultsToBlobStorage(resultsObjBytes);
//log completion
log.LogInformation("Migrating Apprenticeships Complete");
async Task <IList <int> > GetProviderWhiteList()
{
var list = new List <int>();
var whiteList = await blobhelper.ReadFileAsync(blobContainer, WHITE_LIST_FILE);
if (!string.IsNullOrEmpty(whiteList))
{
var lines = whiteList.Split(new[] { Environment.NewLine }, StringSplitOptions.None);
foreach (string line in lines)
{
if (int.TryParse(line, out int id))
{
list.Add(id);
}
}
}
return(list);
}
async Task WriteResultsToBlobStorage(byte[] data)
{
await blobhelper.UploadFile(blobContainer, venueExportFileName, data);
}
void AddResultMessage(int apprenticeshipId, int ukprn, string status, string apprenticeshipTitle, string message = "", string warnings = "")
{
lock (result)
{
var validateResult = new ApprenticeshipResultMessage()
{
ApprenticeshipID = apprenticeshipId, Status = status, Message = message, UKPRN = ukprn, ApprenticeshipTitle = apprenticeshipTitle, Warnings = warnings
};
result.Add(validateResult);
}
}
byte[] GetResultAsByteArray(IList <ApprenticeshipResultMessage> ob)
{
using (var memoryStream = new System.IO.MemoryStream())
{
using (var streamWriter = new System.IO.StreamWriter(memoryStream))
using (var csvWriter = new CsvWriter(streamWriter, CultureInfo.InvariantCulture))
{
csvWriter.WriteRecords <ApprenticeshipResultMessage>(ob);
csvWriter.Flush();
}
return(memoryStream.ToArray());
}
}
bool IsOnWhiteList(int ukprn)
{
if (!whiteListProviders.Any(x => x == ukprn))
{
return(false);
}
else
{
return(true);
}
}
}
