private static async Task <IEnumerable <IMiragtionProviderItem> > GetProvidersFromCsv(
string filename,
IBlobStorageHelper blobStorageHelper,
StringBuilder logFile,
CloudBlobContainer containerProviderFiles)
{
var migrationProviderCsv = filename;
logFile.AppendLine($"Attempting to get content from Migration Provider CSV file: {migrationProviderCsv}");
var migrationProvidersCsvContent =
await blobStorageHelper.ReadFileAsync(containerProviderFiles, migrationProviderCsv);
logFile.AppendLine(
$"Got content from Migration Provider CSV file: {migrationProviderCsv} [content length: {migrationProvidersCsvContent.Length}]");
logFile.AppendLine(migrationProviderCsv);
logFile.AppendLine(migrationProvidersCsvContent);
logFile.AppendLine($"Attempting to deserialise content into a IEnumerable<{nameof(MiragtionProviderItem)}>");
var mpItems = MigrationProviderItemHelper.GetMiragtionProviderItems(migrationProvidersCsvContent, logFile);
logFile.AppendLine(
$"Start of deserialised content into: {mpItems.Count()} items in IEnumerable<{nameof(MiragtionProviderItem)}>");
logFile.Append(string.Join("," + Environment.NewLine, mpItems));
logFile.AppendLine($"{Environment.NewLine}End of deserialised content");
return(mpItems);
}
public XmlTransformHelper(AppSettings settings, IBlobStorageHelper helper)
{
this._settings = settings ?? throw new ArgumentNullException(nameof(settings));
this._helper = helper ?? throw new ArgumentNullException(nameof(helper));
this._xslt = new XslCompiledTransform(enableDebug: true);
this._arguments = new XsltArgumentList();
}
public DeviceGroupsWriter(
IBlobStorageConfig blobStorageConfig,
IBlobStorageHelper blobStorageHelper,
IFileWrapper fileWrapper,
ILogger logger)
{
this.blobStorageConfig = blobStorageConfig;
this.logger = logger;
this.blobStorageHelper = blobStorageHelper;
this.fileWrapper = fileWrapper;
}
public static async Task Run(
[TimerTrigger("%ApprenticeshipMigrationSchedule%")] TimerInfo myTimer,
ILogger log,
[Inject] IConfigurationRoot configuration,
[Inject] IBlobStorageHelper blobStorageHelper,
[Inject] IApprenticeshipServiceWrapper apprenticeshipServiceWrapper)
{
var logFile = new StringBuilder();
logFile.AppendLine($"Starting {nameof(ApprenticeshipDeltaExport)} at {DateTime.Now}");
var fileNames = new List <string>();
var last24HoursAgo = DateTime.Today.AddDays(-1);
var providersFileName = $"{DateTime.Today.ToString("yyyyMMdd")}\\Apprenticeships\\Apprenticeships_for_Providers_{DateTime.Now.ToString("yyyy-MM-ddTHH-mm-ss")}.json";
logFile.AppendLine($"24 Hours ago: {last24HoursAgo}");
logFile.AppendLine($"Apprenticeship Delta filename: {providersFileName}");
var containerNameExporter = configuration["ContainerNameExporter"];
var containerExporter = blobStorageHelper.GetBlobContainer(containerNameExporter);
try
{
logFile.AppendLine($"Attempting to call Apprenticeship API to gather apprenticeship delta updates");
var apprenticeshipDelta = apprenticeshipServiceWrapper.GetApprenticeshipDeltaUpdatesAsJson();
logFile.AppendLine($"Successful call to apprenticeship API");
if (apprenticeshipDelta != null)
{
logFile.AppendLine($"Apprenticeship Delta JSON returned");
var providersBlob = containerExporter.GetBlockBlobReference(providersFileName);
logFile.AppendLine($"Attempting to upload apprenticeship delta json");
await providersBlob.UploadTextAsync(apprenticeshipDelta);
logFile.AppendLine($"Upload successful");
}
else
{
logFile.AppendLine($"No updated apprenticeships between {last24HoursAgo} and {DateTime.Now} ");
}
}
catch (Exception e)
{
logFile.AppendLine(e.Message);
logFile.AppendLine(e.ToString());
}
finally
{
logFile.AppendLine($"Ending {nameof(ApprenticeshipDeltaExport)} at {DateTime.Now}");
var logFileName = $"{DateTime.Today.ToString("yyyyMMdd")}\\Apprenticeships\\Logs\\Log_{DateTime.Now.ToString("yyyy-MM-ddTHH-mm-ss")}.txt";
var logFileNameBolb = containerExporter.GetBlockBlobReference(logFileName);
await logFileNameBolb.UploadTextAsync(logFile.ToString());
}
}
public StatusService(
ILogger logger,
IHttpClient httpClient,
IBlobStorageHelper blobStorageHelper,
ICosmosDbSql cosmosDbSql,
IServicesConfig servicesConfig
)
{
this.log = logger;
this.httpClient = httpClient;
this.blobStorageHelper = blobStorageHelper;
this.cosmosDbSql = cosmosDbSql;
this.servicesConfig = servicesConfig;
this.timeout = this.servicesConfig.ConfigServiceTimeout;
}
public StatusService(
ILogger logger,
IStorageClient storageClient,
ITimeSeriesClient timeSeriesClient,
IHttpClient httpClient,
IServicesConfig servicesConfig,
IBlobStorageHelper blobStorageHelper)
{
this.log = logger;
this.storageClient = storageClient;
this.timeSeriesClient = timeSeriesClient;
this.httpClient = httpClient;
this.servicesConfig = servicesConfig;
this.blobStorageHelper = blobStorageHelper;
}
public static async Task Run(
[TimerTrigger("%MigrationReportSchedule%")] TimerInfo myTimer,
ILogger log,
[Inject] IConfigurationRoot configuration,
[Inject] ICosmosDbHelper cosmosDbHelper,
[Inject] IBlobStorageHelper blobHelper,
[Inject] IProviderCollectionService providerCollectionService,
[Inject] ICourseCollectionService courseCollectionService,
[Inject] IApprenticeshipCollectionService apprenticeshipCollectionService,
[Inject] IMigrationReportCollectionService migrationReportCollectionService)
{
await new MigrationReportGeneratorService().Run(
log, configuration, cosmosDbHelper, blobHelper, providerCollectionService, courseCollectionService,
apprenticeshipCollectionService, migrationReportCollectionService
);
}
private static async Task <IList <int> > GetProviderWhiteList(IBlobStorageHelper blobStorageHelper, CloudBlobContainer cloudBlobContainer)
{
var whiteList = await blobStorageHelper.ReadFileAsync(cloudBlobContainer, "ProviderWhiteList.txt");
if (string.IsNullOrEmpty(whiteList))
{
return(new List <int>());
}
var list = new List <int>();
foreach (var line in whiteList.Split(new[] { Environment.NewLine }, StringSplitOptions.None))
{
if (int.TryParse(line, out int id))
{
list.Add(id);
}
}
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] IUkrlpApiService ukrlpApiService
)
{
const string WHITE_LIST_FILE = "ProviderWhiteList.txt";
const string ProviderAppName = "Provider.Migrator";
var stopWatch = new Stopwatch();
// TODO : Change to correct collection below
var databaseId = configuration["CosmosDbSettings:DatabaseId"];
var providerCollectionId = configuration["CosmosDbCollectionSettings:ProvidersCollectionId"];
var connectionString = configuration.GetConnectionString("TribalRestore");
var venueExportFileName = $"ProviderExport-{DateTime.Now.ToString("dd-MM-yy HHmm")}";
var _cosmosClient = cosmosDbHelper.GetClient();
var blobContainer = blobhelper.GetBlobContainer(configuration["BlobStorageSettings:Container"]);
log.LogInformation($"WhitelistProviders : Start loading...");
var whiteListProviders = await GetProviderWhiteList();
log.LogInformation($"WhitelistProviders : Finished loading.");
// Get all changed data from UKRLP API
stopWatch.Reset();
log.LogInformation($"UKRLApiService: Start getting data..");
stopWatch.Start();
var ukrlpApiProviders = ukrlpApiService.GetAllProviders(whiteListProviders.Select(p => p.ToString()).ToList());
stopWatch.Stop();
log.LogInformation($"UKRLApiService: Finished getting datain {stopWatch.ElapsedMilliseconds / 1000}.");
int totalTribalCount = 0;
int totalAttemptedCount = 0;
int totalUpdatedCount = 0;
int totalInsertedCount = 0;
var result = new List <ProviderResultMessage>();
using (var sqlConnection = new SqlConnection(connectionString))
{
using (var command = sqlConnection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = @"SELECT
P.ProviderId,
P.Ukprn,
P.ProviderName,
RS.RecordStatusId,
RS.RecordStatusName,
P.RoATPFFlag,
P.RoATPProviderTypeId,
P.RoATPStartDate,
p.PassedOverallQAChecks,
P.MarketingInformation,
P.NationalApprenticeshipProvider,
P.TradingName,
P.UPIN,
Ar.AddressLine1,
Ar.AddressLine2,
Ar.Town,
Ar.County,
Ar.Postcode,
P.Email,
P.Website,
P.Telephone,
CASE
WHEN Count(C.CourseId) > 0 THEN 1
WHEN Count(C.CourseId) = 0 THEN 0
END As HasCourse,
CASE
WHEN Count(A.ApprenticeshipId) > 0 THEN 1
WHEN Count(A.ApprenticeshipId) = 0 THEN 0
END As HasApprenticeship
FROM [Provider] P
JOIN [RecordStatus] RS
ON P.RecordStatusId = RS.RecordStatusId
JOIN [Address] Ar
ON P.AddressId = Ar.AddressId
LEFT JOIN [Course] C
ON P.ProviderId = C.ProviderId
LEFT JOIN [Apprenticeship] A
ON P.ProviderId = A.ProviderId
WHERE P.RecordStatusId = 2
GROUP BY P.ProviderId,
P.Ukprn,
P.ProviderName,
RS.RecordStatusId,
RS.RecordStatusName,
P.RoATPFFlag,
P.RoATPProviderTypeId,
P.RoATPStartDate,
p.PassedOverallQAChecks,
P.MarketingInformation,
P.NationalApprenticeshipProvider,
P.TradingName,
P.UPIN,
Ar.AddressLine1,
Ar.AddressLine2,
Ar.Town,
Ar.County,
Ar.Postcode,
P.Email,
P.Website,
P.Telephone
";
try
{
//Open connection.
sqlConnection.Open();
stopWatch.Reset();
log.LogInformation($"Tribal Data: Start....");
stopWatch.Start();
using (SqlDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
// 1) Read provider data from Tribal
var item = ProviderSource.FromDataReader(dataReader);
totalTribalCount++;
try
{
// 2) Check if in Whitelist
if (!whiteListProviders.Any(x => x == item.UKPRN))
{
AddResultMessage(item.ProviderId, "SKIPPED-NotOnWhitelist", $"Provider {item.ProviderId} not on whitelist, ukprn {item.UKPRN}");
continue;
}
totalAttemptedCount++;
// 3) Check againts API ? If no match Add to Result Message, skip next
var ukrlpProviderItem = ukrlpApiProviders.FirstOrDefault(p => p.UnitedKingdomProviderReferenceNumber.Trim() == item.UKPRN.ToString());
if (ukrlpProviderItem == null)
{
AddResultMessage(item.ProviderId, "SKIPPED-NotInUkrlpApi", $"Provider {item.ProviderId} cannot be found in UKRLP Api, ukprn {item.UKPRN}");
continue;
}
// 4) Build Cosmos collection record
var providerToUpsert = BuildNewCosmosProviderItem(ukrlpProviderItem, item);
var cosmosProviderItem = await providerCollectionService.GetDocumentByUkprn(item.UKPRN);
if (cosmosProviderItem != null)
{
Uri collectionUri = UriFactory.CreateDocumentCollectionUri(databaseId, providerCollectionId);
await _cosmosClient.UpsertDocumentAsync(collectionUri, UpdateCosmosProviderItem(cosmosProviderItem, providerToUpsert));
totalUpdatedCount++;
AddResultMessage(item.ProviderId, "PROCESSED-Updated", $"Provider {item.ProviderId} updated in Cosmos Collection, ukprn {item.UKPRN}");
}
else
{
await cosmosDbHelper.CreateDocumentAsync(_cosmosClient, providerCollectionId, providerToUpsert);
totalInsertedCount++;
AddResultMessage(item.ProviderId, "PROCESSED-Inserted", $"Provider {item.ProviderId} inserted in Cosmos Collection, ukprn {item.UKPRN}");
}
}
catch (Exception ex)
{
string errorMessage = $"Error processing Provider {item.ProviderId} with Ukprn {item.UKPRN}. {ex.Message}";
AddResultMessage(item.ProviderId, "ERRORED", errorMessage);
log.LogInformation(errorMessage);
}
}
dataReader.Close();
}
stopWatch.Stop();
log.LogInformation($"Tribal Data: Processing completed in {stopWatch.ElapsedMilliseconds / 1000}");
AddResultMessage(0, "SUMMARY", $"Total Time : {stopWatch.ElapsedMilliseconds / 1000} seconds, Tribal : {totalTribalCount}, URLP : {ukrlpApiProviders.Count}, Processed : {totalAttemptedCount}, Updated : {totalUpdatedCount}, Inserted : {totalInsertedCount}");
}
catch (Exception ex)
{
log.LogError(ex.Message);
}
var resultsObjBytes = GetResultAsByteArray(result);
await WriteResultsToBlobStorage(resultsObjBytes);
}
}
void AddResultMessage(int providerId, string status, string message = "")
{
var validateResult = new ProviderResultMessage()
{
ProviderId = providerId, Status = status, Message = message
};
result.Add(validateResult);
}
Provider BuildNewCosmosProviderItem(ProviderRecordStructure ukrlpData, ProviderSource tribalData)
{
// Build contacts
List <Providercontact> providercontacts = new List <Providercontact>();
var ukrlpDataContacts = ukrlpData.ProviderContact
.Where(p => p.ContactType == "P")
.OrderByDescending(c => c.LastUpdated);
// Load UKRLP api contacts if available
if (ukrlpDataContacts.Any())
{
var ukrlpContact = ukrlpDataContacts.First();
// Build contact address
Contactaddress contactaddress = new Contactaddress()
{
SAON = new SAON()
{
Description = ukrlpContact.ContactAddress.SAON.Description
},
PAON = new PAON()
{
Description = ukrlpContact.ContactAddress.PAON.Description
},
StreetDescription = ukrlpContact.ContactAddress.StreetDescription,
UniqueStreetReferenceNumber = ukrlpContact.ContactAddress.UniqueStreetReferenceNumber,
UniquePropertyReferenceNumber = ukrlpContact.ContactAddress.UniquePropertyReferenceNumber,
Locality = ukrlpContact.ContactAddress.Locality,
Items = ukrlpContact.ContactAddress.Items,
ItemsElementName = ukrlpContact.ContactAddress.ItemsElementName?.Select(i => (int)i).ToArray(),
PostTown = ukrlpContact.ContactAddress.PostTown,
PostCode = ukrlpContact.ContactAddress.PostCode,
};
// Build contact personal details
Contactpersonaldetails contactpersonaldetails = new Contactpersonaldetails()
{
PersonNameTitle = ukrlpContact.ContactPersonalDetails.PersonNameTitle,
PersonGivenName = ukrlpContact.ContactPersonalDetails.PersonGivenName,
PersonFamilyName = ukrlpContact.ContactPersonalDetails.PersonFamilyName,
PersonNameSuffix = ukrlpContact.ContactPersonalDetails.PersonNameSuffix,
PersonRequestedName = ukrlpContact.ContactPersonalDetails.PersonRequestedName,
};
var providerContact = new Providercontact(contactaddress, contactpersonaldetails);
providerContact.ContactType = ukrlpContact.ContactType;
providerContact.ContactRole = ukrlpContact.ContactRole;
providerContact.ContactTelephone1 = ukrlpContact.ContactTelephone1;
providerContact.ContactTelephone2 = ukrlpContact.ContactTelephone2;
providerContact.ContactWebsiteAddress = ukrlpContact.ContactWebsiteAddress;
providerContact.ContactEmail = ukrlpContact.ContactEmail;
providerContact.LastUpdated = ukrlpContact.LastUpdated;
providercontacts.Add(providerContact);
}
else
{
// Check if valid Tribal Address exists
if (tribalData.IsValidAddress)
{
// Build contact address
Contactaddress tribalContactaddress = new Contactaddress()
{
StreetDescription = tribalData.AddressLine1,
Locality = tribalData.County,
PostTown = tribalData.Town,
PostCode = tribalData.PostCode,
};
var tribalContact = new Providercontact(tribalContactaddress, null);
tribalContact.ContactType = "P";
tribalContact.ContactTelephone1 = tribalData.Telephone;
tribalContact.ContactWebsiteAddress = tribalData.Website;
tribalContact.ContactEmail = tribalData.Email;
tribalContact.LastUpdated = DateTime.UtcNow;
providercontacts.Add(tribalContact);
}
else
{
// Cannot find address in UKRLP api or tribal so raise alert
AddResultMessage(tribalData.ProviderId, "WARNING", $"Cannot find contact address details in Api or Tribal data for Provider {tribalData.ProviderId}, ukprn {tribalData.UKPRN}.");
}
}
// Build provider aliases
List <Provideralias> provideraliases = new List <Provideralias>();
foreach (ProviderAliasesStructure ukrlpProviderAlias in ukrlpData.ProviderAliases)
{
provideraliases.Add(new Provideralias()
{
ProviderAlias = ukrlpProviderAlias.ProviderAlias,
LastUpdated = ukrlpProviderAlias.LastUpdated,
});
}
// Build provider Verificationdetail
List <Verificationdetail> providerVerificationdetails = new List <Verificationdetail>();
foreach (VerificationDetailsStructure providerVerificationDetail in ukrlpData.VerificationDetails)
{
providerVerificationdetails.Add(new Verificationdetail()
{
VerificationAuthority = providerVerificationDetail.VerificationAuthority,
VerificationID = providerVerificationDetail.VerificationID,
});
}
Provider providerToUpsert = new Provider(providercontacts.ToArray(), provideraliases.ToArray(), providerVerificationdetails.ToArray());
providerToUpsert.ProviderId = tribalData.ProviderId;
providerToUpsert.id = Guid.NewGuid();
providerToUpsert.UnitedKingdomProviderReferenceNumber = tribalData.UKPRN.ToString();
providerToUpsert.ProviderType = GetProviderType(tribalData.HasCourse, tribalData.HasApprenticeship);
providerToUpsert.ProviderName = ukrlpData.ProviderName;
providerToUpsert.ProviderStatus = ukrlpData.ProviderStatus;
providerToUpsert.ProviderVerificationDate = ukrlpData.ProviderVerificationDate;
providerToUpsert.ProviderVerificationDateSpecified = ukrlpData.ProviderVerificationDateSpecified;
providerToUpsert.ExpiryDateSpecified = ukrlpData.ExpiryDateSpecified;
providerToUpsert.ProviderAssociations = ukrlpData.ProviderAssociations;
providerToUpsert.Alias = ukrlpData.ProviderAliases?.FirstOrDefault()?.ProviderAlias;
providerToUpsert.Status = Status.Onboarded; // TODO : is this correct ?
providerToUpsert.PassedOverallQAChecks = tribalData.PassedOverallQAChecks;
providerToUpsert.RoATPFFlag = tribalData.RoATPFFlag;
providerToUpsert.RoATPProviderTypeId = tribalData.RoATPProviderTypeId;
providerToUpsert.RoATPStartDate = tribalData.RoATPStartDate;
providerToUpsert.MarketingInformation = tribalData.MarketingInformation;
providerToUpsert.NationalApprenticeshipProvider = tribalData.NationalApprenticeshipProvider;
providerToUpsert.TradingName = tribalData.TradingName;
providerToUpsert.UPIN = tribalData.UPIN;
providerToUpsert.LastUpdatedBy = ProviderAppName;
providerToUpsert.DateUpdated = DateTime.UtcNow;
return(providerToUpsert);
}
Provider UpdateCosmosProviderItem(Provider cosmosProviderItem, Provider providerToUpsert)
{
cosmosProviderItem.Alias = providerToUpsert.Alias;
cosmosProviderItem.ExpiryDateSpecified = providerToUpsert.ExpiryDateSpecified;
cosmosProviderItem.MarketingInformation = providerToUpsert.MarketingInformation;
cosmosProviderItem.NationalApprenticeshipProvider = providerToUpsert.NationalApprenticeshipProvider;
cosmosProviderItem.PassedOverallQAChecks = providerToUpsert.PassedOverallQAChecks;
cosmosProviderItem.ProviderAliases = providerToUpsert.ProviderAliases;
cosmosProviderItem.ProviderAssociations = providerToUpsert.ProviderAssociations;
cosmosProviderItem.ProviderContact = providerToUpsert.ProviderContact;
cosmosProviderItem.ProviderId = providerToUpsert.ProviderId;
cosmosProviderItem.ProviderName = providerToUpsert.ProviderName;
cosmosProviderItem.ProviderStatus = providerToUpsert.ProviderStatus;
cosmosProviderItem.ProviderType = providerToUpsert.ProviderType;
cosmosProviderItem.ProviderVerificationDate = providerToUpsert.ProviderVerificationDate;
cosmosProviderItem.ProviderVerificationDateSpecified = providerToUpsert.ProviderVerificationDateSpecified;
cosmosProviderItem.RoATPFFlag = providerToUpsert.RoATPFFlag;
cosmosProviderItem.RoATPProviderTypeId = providerToUpsert.RoATPProviderTypeId;
cosmosProviderItem.RoATPStartDate = providerToUpsert.RoATPStartDate;
cosmosProviderItem.Status = providerToUpsert.Status;
cosmosProviderItem.TradingName = providerToUpsert.TradingName;
cosmosProviderItem.UnitedKingdomProviderReferenceNumber = providerToUpsert.UnitedKingdomProviderReferenceNumber;
cosmosProviderItem.UPIN = providerToUpsert.UPIN;
cosmosProviderItem.VerificationDetails = providerToUpsert.VerificationDetails;
cosmosProviderItem.LastUpdatedBy = providerToUpsert.LastUpdatedBy;
cosmosProviderItem.DateUpdated = providerToUpsert.DateUpdated;
return(cosmosProviderItem);
}
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);
}
byte[] GetResultAsByteArray(IList <ProviderResultMessage> message)
{
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 <ProviderResultMessage>(message);
}
return(memoryStream.ToArray());
}
}
async Task WriteResultsToBlobStorage(byte[] data)
{
await blobhelper.UploadFile(blobContainer, venueExportFileName, data);
}
}
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] IBlobStorageHelper blobHelper,
[Inject] ILoggerFactory loggerFactory)
{
var whitelistFileName = "ProviderWhiteList.txt";
var blobContainer = configuration["BlobStorageSettings:Container"];
var databaseId = configuration["CosmosDbSettings:DatabaseId"];
var coursesCollectionId = "courses";
var documentClient = cosmosDbHelper.GetClient();
var coursesCollectionUri = UriFactory.CreateDocumentCollectionUri(databaseId, coursesCollectionId);
var logger = loggerFactory.CreateLogger(typeof(ArchiveCourses));
var whitelist = await GetProviderWhiteList();
foreach (var ukprn in whitelist)
{
var updated = 0;
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>();
foreach (var doc in queryResponse)
{
foreach (var run in doc.CourseRuns)
{
run.RecordStatus = CourseDirectory.Models.Enums.RecordStatus.Archived;
}
var documentLink = UriFactory.CreateDocumentUri(databaseId, coursesCollectionId, doc.id.ToString());
await documentClient.ReplaceDocumentAsync(documentLink, doc, new RequestOptions()
{
PartitionKey = new Microsoft.Azure.Documents.PartitionKey(ukprn)
});
updated++;
}
continuation = queryResponse.ResponseContinuation;
}while (continuation != null);
logger.LogInformation($"Archived {updated} courses for {ukprn}");
}
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);
}
}
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] IApprenticeshipCollectionService apprenticeshipCollectionService,
[Inject] ICosmosDbHelper cosmosDbHelper,
[Inject] IBlobStorageHelper blobhelper,
[Inject] IUkrlpApiService ukrlpApiService
)
{
const string apprenticeshipAppName = "Validate.Apprenticeship";
var apprenticeshipValidationFileName = $"{apprenticeshipAppName}-{DateTime.Now.ToString("dd-MM-yy HHmm")}";
var blobContainer = blobhelper.GetBlobContainer(configuration["BlobStorageSettings:Container"]);
var validator = new ApprenticeshipValidator();
List <ApprenticeshipValidationResult> validationEResult = new List <ApprenticeshipValidationResult>();
var stopWatch = new Stopwatch();
stopWatch.Start();
var resultData = await apprenticeshipCollectionService.GetAllApprenticeshipsAsync();
foreach (var item in resultData)
{
//item.ContactEmail = "testing";
//item.Url = "testing";
//item.ContactWebsite = "testing";
//item.ContactTelephone = "101101abc";
//item.ApprenticeshipTitle = item.ApprenticeshipTitle + " @";
var validationResult = validator.Validate(item);
if (!validationResult.IsValid)
{
foreach (var error in validationResult.Errors)
{
validationEResult.Add(new ApprenticeshipValidationResult()
{
ApprenticeshipId = item.ApprenticeshipId,
ProviderUkprn = item.ProviderUKPRN,
FieldName = error.PropertyName,
ErrorMessage = error.ErrorMessage
});
}
}
}
var resultsObjBytes = GetResultAsByteArray(validationEResult);
await WriteResultsToBlobStorage(resultsObjBytes);
stopWatch.Stop();
byte[] GetResultAsByteArray(IList <ApprenticeshipValidationResult> message)
{
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 <ApprenticeshipValidationResult>(message);
}
return(memoryStream.ToArray());
}
}
async Task WriteResultsToBlobStorage(byte[] data)
{
await blobhelper.UploadFile(blobContainer, apprenticeshipValidationFileName, data);
}
}
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
[Inject] IBlobStorageHelper blobhelper,
[Inject] ILoggerFactory loggerFactory,
[Inject] IConfiguration configuration,
[Inject] IFaocSearchServiceWrapper searchService
)
{
var logger = loggerFactory.CreateLogger(typeof(UpdateFaocSearch));
var blobContainername = configuration["BlobStorageSettings:Container"];
var blobContainer = blobhelper.GetBlobContainer(blobContainername);
var faocFileName = "Courses.csv";
var faocEntries = await GetFaocList();
var validator = new FaocValidator();
var validationErrors = new List <FaocValidationError>();
var validFaocEntries = new List <FaocEntry>();
foreach (var onlineCourse in faocEntries)
{
var isValid = validator.Validate(onlineCourse);
if (!isValid.IsValid)
{
validationErrors.Add(new FaocValidationError {
ID = onlineCourse.id, Error = isValid.Errors.Select(x => $"{x.ErrorMessage}{Environment.NewLine}").ToList()
});
continue;
}
else
{
validFaocEntries.Add(onlineCourse);
}
}
if (validFaocEntries.Count() > 0)
{
//upload coureses
var results = await searchService.UploadFaocBatch(validFaocEntries);
//delete old courses
await searchService.DeleteStaleDocuments(validFaocEntries);
logger.LogWarning($"{ validationErrors.Count } Courses Failed To Import");
if (validationErrors.Count() == 0)
{
await blobhelper.MoveFile(blobContainer, faocFileName, $"ProcessedCourses/courses-{DateTime.Now.ToString("yyyy-MM-ddTHH:mm:ss")}.csv");
}
else
{
await blobhelper.MoveFile(blobContainer, faocFileName, $"Errors/courses-{DateTime.Now.ToString("yyyy-MM-ddTHH:mm:ss")}.csv");
}
}
var totalImported = faocEntries.Count() - validationErrors.Count();
logger.LogWarning($"Finished Importing {totalImported} of {faocEntries.Count()} online courses.");
async Task <IList <FaocEntry> > GetFaocList()
{
var blob = blobhelper.GetBlobContainer(blobContainername).GetBlockBlobReference(faocFileName);
var ms = new MemoryStream();
await blob.DownloadToStreamAsync(ms);
ms.Seek(0L, SeekOrigin.Begin);
using (var reader = new StreamReader(ms))
{
using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
{
//incase header is lower case
csv.Configuration.PrepareHeaderForMatch = (string header, int index) => header.ToLower();
var records = csv.GetRecords <FaocEntry>();
return(records.ToList());
}
}
}
}
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] IBlobStorageHelper blobHelper,
[Inject] ILoggerFactory loggerFactory,
[Inject] IBlobStorageHelper blobhelper)
{
var whitelistFileName = "ProviderWhiteList.txt";
var pendingCoursesFileName = $"CoursesWithNoCostOrCostDecription-{DateTime.Now.ToString("dd-MM-yy HHmm")}";
var blobContainer = configuration["BlobStorageSettings:Container"];
var outputContainer = blobhelper.GetBlobContainer(configuration["BlobStorageSettings:Container"]);
var databaseId = configuration["CosmosDbSettings:DatabaseId"];
var coursesCollectionId = "courses";
var documentClient = cosmosDbHelper.GetClient();
var result = new List <MigrationPendingCourseRunResult>();
var coursesCollectionUri = UriFactory.CreateDocumentCollectionUri(databaseId, coursesCollectionId);
var logger = loggerFactory.CreateLogger(typeof(ArchiveCourses));
string continuation = null;
int count = 0;
var whitelist = await GetProviderWhiteList();
foreach (var ukprn in whitelist)
{
do
{
var feedOptions = new FeedOptions()
{
RequestContinuation = continuation,
EnableCrossPartitionQuery = true,
PartitionKey = new Microsoft.Azure.Documents.PartitionKey(ukprn)
};
//find courses that do not have a cost desciption or cost.
var queryResponse = await documentClient.CreateDocumentQuery <Course>(coursesCollectionUri, feedOptions)
.Where(p => p.CourseRuns.Any(x => x.Cost == null && (x.CostDescription == "" || x.CostDescription == null)) &&
(p.CourseStatus == RecordStatus.Live || p.CourseStatus == RecordStatus.MigrationPendingAndLive))
.AsDocumentQuery()
.ExecuteNextAsync <Course>();
//update course run to be migration pending.
foreach (var doc in queryResponse)
{
var currentStatus = doc.CourseStatus;
//Course instance id that caused the course to go to migration pending.
var message = string.Join("\n", doc.CourseRuns.Where(x => x.Cost == null && (x.CostDescription == "" || x.CostDescription == null))
.ToList()
.Select(x => $"Course Instance {x.CourseInstanceId} Invalid"));
doc.CourseRuns.Where(x => x.Cost == null && (x.CostDescription == "" || x.CostDescription == null))
.ToList()
.ForEach(x => x.RecordStatus = CourseDirectory.Models.Enums.RecordStatus.MigrationPending);
var documentLink = UriFactory.CreateDocumentUri(databaseId, coursesCollectionId, doc.id.ToString());
await documentClient.ReplaceDocumentAsync(documentLink, doc, new RequestOptions()
{
PartitionKey = new Microsoft.Azure.Documents.PartitionKey(ukprn)
});
result.Add(new MigrationPendingCourseRunResult {
CourseId = doc.CourseId, StatusId = doc.CourseStatus, Message = message
});
count++;
}
continuation = queryResponse.ResponseContinuation;
} while (continuation != null);
}
//Log Results to blob storage
var resultsObjBytes = GetResultAsByteArray(result);
await WriteResultsToBlobStorage(resultsObjBytes);
logger.LogInformation($"{count} courses Have been made pending");
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 WriteResultsToBlobStorage(byte[] data)
{
await blobhelper.UploadFile(outputContainer, pendingCoursesFileName, data);
}
byte[] GetResultAsByteArray(IList <MigrationPendingCourseRunResult> 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 <MigrationPendingCourseRunResult>(ob);
csvWriter.Flush();
}
return(memoryStream.ToArray());
}
}
}
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] IFEChoicesDataCollectionService feChoicesDataCollectionService,
[Inject] ICosmosDbHelper cosmosDbHelper,
[Inject] IBlobStorageHelper blobhelper
)
{
const string AppName = "FEChoicesData.Migrator";
var stopWatch = new Stopwatch();
var databaseId = configuration["CosmosDbSettings:DatabaseId"];
var feChoicesCollectionId = configuration["CosmosDbCollectionSettings:FEChoicesDataCollectionId"];
var connectionString = configuration.GetConnectionString("TribalRestore");
var fechoicesDataMigrationLogFile = $"FEChoicesDataMigrator-{DateTime.Now.ToString("dd-MM-yy HHmm")}";
List <Guid> feDataRecordsToDeleteByGuid = new List <Guid>();
List <int> feDataRecordsToDeleteByUkprn = new List <int>();
var blobContainer = blobhelper.GetBlobContainer(configuration["BlobStorageSettings:Container"]);
var _cosmosClient = cosmosDbHelper.GetClient();
// Get all changed data from UKRLP API
stopWatch.Reset();
log.LogInformation($"FEChoicesData Migrator: Start synching data..");
var result = new List <FeChoicesDataResultMessage>();
using (var sqlConnection = new SqlConnection(connectionString))
{
using (var command = sqlConnection.CreateCommand())
{
// Get non duplicate UKPRN data only
command.CommandType = CommandType.Text;
command.CommandText = @"SELECT P.Ukprn,
P.RecordStatusId,
D.UPIN,
D.LearnerSatisfaction,
D.LearnerDestination,
D.EmployerSatisfaction,
D.CreatedDateTimeUtc,
Count(p.UKprn)
FROM [Provider] P
JOIN [FEChoices] D
ON P.UPIN = D.UPIN
WHERE p.RecordStatusId = 2
GROUP BY
P.Ukprn,
P.RecordStatusId,
D.UPIN,
D.LearnerSatisfaction,
D.LearnerDestination,
D.EmployerSatisfaction,
D.CreatedDateTimeUtc
HAVING Count(P.Ukprn) = 1
ORDER BY D.CreatedDateTimeUtc DESC, p.Ukprn
";
try
{
//Open connection.
sqlConnection.Open();
stopWatch.Reset();
log.LogInformation($"Tribal Data: Start....");
stopWatch.Start();
List <FEChoicesSourceData> sourceData = new List <FEChoicesSourceData>();
using (SqlDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
// Read FE data from Tribal
var item = FEChoicesSourceData.FromDataReader(dataReader);
sourceData.Add(item);
}
dataReader.Close();
}
var destinationData = await feChoicesDataCollectionService.GetAllDocument();
var uniqueSourceUkprns = sourceData.Select(s => s.UKPRN).Distinct().ToList();
foreach (var ukprn in uniqueSourceUkprns)
{
// filter out duplicate form source
if (sourceData.Count(s => s.UKPRN == ukprn) > 1)
{
// mark for removal from destination if exists
feDataRecordsToDeleteByUkprn.Add(ukprn);
continue;
}
// pick the first as there might still be duplicates
var sourceItem = sourceData.First(s => s.UKPRN == ukprn);
try
{
// Check if item exists in both (could be duplicate in destination)
var itemsToUpdate = destinationData.Where(s => s.UKPRN == sourceItem.UKPRN);
// Update
if (itemsToUpdate != null && itemsToUpdate.Any())
{
var itemToUpdate = itemsToUpdate.First();
itemToUpdate.EmployerSatisfaction = sourceItem.EmployerSatisfaction;
itemToUpdate.LearnerSatisfaction = sourceItem.LearnerSatisfaction;
itemToUpdate.CreatedDateTimeUtc = sourceItem.CreatedDateTimeUtc;
itemToUpdate.LastUpdatedBy = AppName;
itemToUpdate.LastUpdatedOn = DateTime.UtcNow;
Uri collectionUri = UriFactory.CreateDocumentCollectionUri(databaseId, feChoicesCollectionId);
await _cosmosClient.UpsertDocumentAsync(collectionUri, itemToUpdate);
feDataRecordsToDeleteByGuid.AddRange(itemsToUpdate.Where(i => i.id != itemToUpdate.id).Select(i => i.id).ToList());
AddResultMessage(sourceItem.UKPRN, "PROCESSED-Updated", $"Provider {sourceItem.UKPRN} updated in Cosmos Collection");
}
// Insert new entry
else
{
var newRecord = new FEChoicesData()
{
id = Guid.NewGuid(),
UKPRN = sourceItem.UKPRN,
LearnerSatisfaction = sourceItem.LearnerSatisfaction,
EmployerSatisfaction = sourceItem.EmployerSatisfaction,
CreatedDateTimeUtc = sourceItem.CreatedDateTimeUtc,
CreatedBy = AppName,
CreatedOn = DateTime.UtcNow,
LastUpdatedBy = AppName,
LastUpdatedOn = DateTime.UtcNow,
};
await cosmosDbHelper.CreateDocumentAsync(_cosmosClient, feChoicesCollectionId, newRecord);
AddResultMessage(sourceItem.UKPRN, "PROCESSED-Created", $"Provider {sourceItem.UKPRN} updated in Cosmos Collection");
}
}
catch (Exception ex)
{
AddResultMessage(sourceItem.UKPRN, "ERRORED", $"Error while inserting/updating for provider {ex.Message}");
log.LogError($"Tribal Data: Error processing data.", ex);
}
}
// Remove data that is not in source
var howManyToDelete = destinationData.Where(d => !sourceData.Select(s => s.UKPRN).Contains(d.UKPRN));
foreach (var existingItem in howManyToDelete)
{
try
{
Uri docUri = UriFactory.CreateDocumentUri(databaseId, feChoicesCollectionId, existingItem.id.ToString());
var deleteResult = await _cosmosClient.DeleteDocumentAsync(docUri, new RequestOptions()
{
PartitionKey = new PartitionKey(existingItem.UKPRN)
});
AddResultMessage(existingItem.UKPRN, "DELETE", $"Record {existingItem.id} with UKPRN {existingItem.UKPRN} deleted as not in source.");
}
catch (Exception)
{
AddResultMessage(existingItem.UKPRN, "ERROR", $"Error deleting in destination record {existingItem.id} with UKPRN {existingItem.UKPRN}.");
}
}
// Remove data that is duplicate in destination using Id
var duplicatesToDeleteByGuid = destinationData.Where(d => feDataRecordsToDeleteByGuid.Contains(d.id));
foreach (var existingItem in duplicatesToDeleteByGuid)
{
try
{
Uri docUri = UriFactory.CreateDocumentUri(databaseId, feChoicesCollectionId, existingItem.id.ToString());
var deleteResult = await _cosmosClient.DeleteDocumentAsync(docUri, new RequestOptions()
{
PartitionKey = new PartitionKey(existingItem.UKPRN)
});
AddResultMessage(existingItem.UKPRN, "DELETE", $"Record {existingItem.id} with UKPRN {existingItem.UKPRN} deleted as duplicate in Cosmos.");
}
catch (Exception)
{
AddResultMessage(existingItem.UKPRN, "ERROR", $"Error deleting in destination record {existingItem.id} with UKPRN {existingItem.UKPRN}.");
}
}
// Remove data that is duplicate in source so needs to be removed from destination using UKPRN
var duplicatesToDeleteByUkprn = destinationData.Where(d => feDataRecordsToDeleteByUkprn.Contains(d.UKPRN));
foreach (var existingItem in duplicatesToDeleteByUkprn)
{
try
{
Uri docUri = UriFactory.CreateDocumentUri(databaseId, feChoicesCollectionId, existingItem.id.ToString());
var deleteResult = await _cosmosClient.DeleteDocumentAsync(docUri, new RequestOptions()
{
PartitionKey = new PartitionKey(existingItem.UKPRN)
});
AddResultMessage(existingItem.UKPRN, "DELETE", $"Record {existingItem.id} with UKPRN {existingItem.UKPRN} deleted as duplicate in Cosmos.");
}
catch (Exception)
{
AddResultMessage(existingItem.UKPRN, "ERROR", $"Error deleting in destination record {existingItem.id} with UKPRN {existingItem.UKPRN}.");
}
}
}
catch (Exception ex)
{
AddResultMessage(-1, "ERRORED-Unknown", $"{ex.Message}");
log.LogError($"Tribal Data: Error processing data.", ex);
}
stopWatch.Stop();
log.LogInformation($"Tribal Data: Processing completed in {stopWatch.ElapsedMilliseconds / 1000}");
var resultsObjBytes = GetResultAsByteArray(result);
await WriteResultsToBlobStorage(resultsObjBytes);
}
}
void AddResultMessage(int providerId, string status, string message = "")
{
var validateResult = new FeChoicesDataResultMessage()
{
ProviderId = providerId, Status = status, Message = message
};
result.Add(validateResult);
}
byte[] GetResultAsByteArray(IList <FeChoicesDataResultMessage> message)
{
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 <FeChoicesDataResultMessage>(message);
}
return(memoryStream.ToArray());
}
}
async Task WriteResultsToBlobStorage(byte[] data)
{
await blobhelper.UploadFile(blobContainer, fechoicesDataMigrationLogFile, data);
}
}
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] IBlobStorageHelper blobHelper,
[Inject] ILoggerFactory loggerFactory,
[Inject] IUkrlpApiService ukrlpApiService)
{
var blobContainer = configuration["BlobStorageSettings:Container"];
var databaseId = configuration["CosmosDbSettings:DatabaseId"];
var coursesCollectionId = "courses";
var providerCollectionId = "ukrlp";
var documentClient = cosmosDbHelper.GetClient();
var coursesCollectionUri = UriFactory.CreateDocumentCollectionUri(databaseId, coursesCollectionId);
var providerCollectionUri = UriFactory.CreateDocumentCollectionUri(databaseId, providerCollectionId);
var logger = loggerFactory.CreateLogger(typeof(ArchiveCourses));
var count = 0;
var logFileName = $"CoursesWithMissingLarsCodes";
string continuation = null;
using (var logStream = new MemoryStream())
using (var logStreamWriter = new StreamWriter(logStream))
using (var logCsvWriter = new CsvWriter(logStreamWriter, CultureInfo.InvariantCulture))
{
// Log CSV headers
logCsvWriter.WriteField("UKPRN");
logCsvWriter.WriteField("ProviderName");
logCsvWriter.WriteField("CourseId");
logCsvWriter.WriteField("Provider course ID");
logCsvWriter.WriteField("Course name");
logCsvWriter.WriteField("Start date");
logCsvWriter.WriteField("Cost");
logCsvWriter.WriteField("Cost description");
logCsvWriter.WriteField("Delivery mode");
logCsvWriter.WriteField("Attendance mode");
logCsvWriter.NextRecord();
do
{
try
{
var feedOptions = new FeedOptions()
{
RequestContinuation = continuation,
EnableCrossPartitionQuery = true
};
var queryResponse = await documentClient.CreateDocumentQuery <Course>(coursesCollectionUri, feedOptions)
.Where(p => (p.LearnAimRef == null || p.QualificationCourseTitle == null) && p.CourseStatus != CourseDirectory.Models.Enums.RecordStatus.Archived)
.AsDocumentQuery()
.ExecuteNextAsync <Course>();
foreach (var doc in queryResponse)
{
var providers = ukrlpApiService.GetAllProviders(new List <string> {
doc.ProviderUKPRN.ToString()
});
var provider = providers.FirstOrDefault();
foreach (var courserun in doc.CourseRuns)
{
logCsvWriter.WriteField(doc.ProviderUKPRN);
logCsvWriter.WriteField(provider?.ProviderName);
logCsvWriter.WriteField(courserun.CourseInstanceId);
logCsvWriter.WriteField(doc.CourseId);
logCsvWriter.WriteField(courserun.CourseName);
logCsvWriter.WriteField(courserun.StartDate);
logCsvWriter.WriteField(courserun.Cost);
logCsvWriter.WriteField(courserun.CostDescription);
logCsvWriter.WriteField(courserun.DeliveryMode);
logCsvWriter.WriteField(courserun.AttendancePattern);
logCsvWriter.NextRecord();
courserun.RecordStatus = CourseDirectory.Models.Enums.RecordStatus.Archived;
count++;
}
count++;
var documentLink = UriFactory.CreateDocumentUri(databaseId, coursesCollectionId, doc.id.ToString());
await documentClient.ReplaceDocumentAsync(documentLink, doc, new RequestOptions()
{
PartitionKey = new Microsoft.Azure.Documents.PartitionKey(doc.ProviderUKPRN)
});
}
continuation = queryResponse.ResponseContinuation;
}
catch (Exception e)
{
Console.WriteLine(e.Message);
logger.LogError(e.Message);
continuation = null;
}
}while (continuation != null);
// 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($"{count} courses have been archived");
}
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] IBlobStorageHelper blobHelper,
[Inject] ILoggerFactory loggerFactory,
[Inject] IBlobStorageHelper blobhelper)
{
var whitelistFileName = "ProviderWhiteList.txt";
var pendingCoursesFileName = $"CoursesWithNoCostOrCostDecription-{DateTime.Now.ToString("dd-MM-yy HHmm")}";
var blobContainer = configuration["BlobStorageSettings:Container"];
var outputContainer = blobhelper.GetBlobContainer(configuration["BlobStorageSettings:Container"]);
var databaseId = configuration["CosmosDbSettings:DatabaseId"];
var apprenticeshipCollectionId = "apprenticeship";
var documentClient = cosmosDbHelper.GetClient();
var apprenticeshipCollectionUri = UriFactory.CreateDocumentCollectionUri(databaseId, apprenticeshipCollectionId);
var logger = loggerFactory.CreateLogger(typeof(ArchiveCourses));
int count = 0;
var whitelist = await GetProviderWhiteList();
var updatedBy = "ArchiveApprenticeships";
foreach (var ukprn in whitelist)
{
string continuation = null;
do
{
var feedOptions = new FeedOptions()
{
RequestContinuation = continuation,
PartitionKey = new Microsoft.Azure.Documents.PartitionKey(ukprn)
};
//try/catch required as there are Apprenticeship records that are not valid (venueId is null in cosmos).
try
{
var queryResponse = await documentClient.CreateDocumentQuery <Apprenticeship>(apprenticeshipCollectionUri, feedOptions)
.Where(p => p.ProviderUKPRN == ukprn && p.CreatedBy != "ApprenticeshipMigrator" && p.RecordStatus == CourseDirectory.Models.Enums.RecordStatus.Live)
.AsDocumentQuery()
.ExecuteNextAsync <Apprenticeship>();
foreach (var doc in queryResponse)
{
//mark every location as arhived
foreach (var loc in doc.ApprenticeshipLocations)
{
loc.RecordStatus = CourseDirectory.Models.Enums.RecordStatus.Archived;
loc.UpdatedBy = updatedBy;
}
doc.UpdatedBy = updatedBy;
var documentLink = UriFactory.CreateDocumentUri(databaseId, apprenticeshipCollectionId, doc.id.ToString());
await documentClient.ReplaceDocumentAsync(documentLink, doc, new RequestOptions()
{
PartitionKey = new Microsoft.Azure.Documents.PartitionKey(ukprn)
});
count++;
}
continuation = queryResponse.ResponseContinuation;
}
catch (Exception)
{
continuation = null;
}
}while (continuation != null);
}
logger.LogInformation($"Archived {count} Apprenticeships");
Console.WriteLine($"Archived {count} Apprenticeships");
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);
}
}
public BlobAccessController(IBlobStorageHelper bobStorageHelper)
{
this.bobStorageHelper = bobStorageHelper;
}
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
[Inject] IConfigurationRoot configuration,
[Inject] ICosmosDbHelper cosmosDbHelper,
[Inject] IBlobStorageHelper blobHelper,
[Inject] ILoggerFactory loggerFactory)
{
var tribalConnectionString = configuration.GetConnectionString("TribalRestore");
var defaultConnectionString = configuration.GetConnectionString("DefaultConnection");
var qaStatuses = new List <ProviderQAStatus>();
var cosmosDbClient = cosmosDbHelper.GetClient();
var databaseId = configuration["CosmosDbSettings:DatabaseId"];
var ukrlp = "ukrlp";
var apprenticehipsUri = "apprenticeship";
var logFileName = $"QAStatusMigrator-{DateTime.Now.ToString("dd-MM-yy HHmm")}";
var blobContainer = configuration["BlobStorageSettings:Container"];
var whitelistFileName = "ProviderWhiteList.txt";
var whitelist = await GetProviderWhiteList();
using (var sqlConnection = new SqlConnection(tribalConnectionString))
{
var sql = @"SELECT DISTINCT PassedOverallQAChecks,Ukprn
FROM [Provider]";
qaStatuses = sqlConnection.Query <ProviderQAStatus>(sql).ToList();
}
try
{
using (var logStream = new MemoryStream())
using (var logStreamWriter = new StreamWriter(logStream))
using (var logCsvWriter = new CsvWriter(logStreamWriter, CultureInfo.InvariantCulture))
{
logCsvWriter.WriteField("UKPRN");
logCsvWriter.WriteField("CosmosID");
logCsvWriter.WriteField("PassedOverallQAChecks");
logCsvWriter.WriteField("Message");
logCsvWriter.NextRecord();
using (var sqlConnection = new SqlConnection(defaultConnectionString))
{
foreach (var s in qaStatuses)
{
var message = "";
if (!whitelist.Contains(s.UKPRN))
{
continue;
}
var provider = await GetExistingProvider(s.UKPRN.ToString(), cosmosDbClient);
if (s.PassedOverallQAChecks && provider != null)
{
var sql = @"IF NOT EXISTS (SELECT 1 FROM [Pttcd].[Providers] WHERE ProviderID = @ID)
INSERT INTO [Pttcd].[Providers] (ProviderId,ApprenticeshipQAStatus) SELECT @ID,@Status
ELSE
UPDATE [Pttcd].[Providers] SET ApprenticeshipQAStatus = @Status WHERE ProviderId = @ID";
var result = sqlConnection.Execute(sql, new
{
ID = provider.id,
Status = 16
});
}
else
{
var apprenticeships = await GetApprenticeships(s.UKPRN, cosmosDbClient);
message = $"Found {apprenticeships.Count()} Apprenticeship that is either Live or PendingMigration";
}
logCsvWriter.WriteField(s.UKPRN);
logCsvWriter.WriteField(provider?.id);
logCsvWriter.WriteField(s.PassedOverallQAChecks);
logCsvWriter.WriteField(message);
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);
}
}
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
throw;
}
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 <List <Apprenticeship> > GetApprenticeships(int ukprn, IDocumentClient documentClient)
{
var collectionLink = UriFactory.CreateDocumentCollectionUri(databaseId, apprenticehipsUri);
var apprenticeships = new List <Apprenticeship>();
string continuation = null;
do
{
var feedOptions = new FeedOptions()
{
RequestContinuation = continuation
};
var queryResponse = await documentClient.CreateDocumentQuery <Apprenticeship>(collectionLink, feedOptions)
.Where(p => p.ProviderUKPRN == ukprn && (p.RecordStatus == RecordStatus.Live || p.RecordStatus == RecordStatus.MigrationPending))
.AsDocumentQuery()
.ExecuteNextAsync <Apprenticeship>();
apprenticeships.AddRange(queryResponse.ToList());
continuation = queryResponse.ResponseContinuation;
}while (continuation != null);
return(apprenticeships);
}
async Task <Provider> GetExistingProvider(string ukprn, IDocumentClient documentClient)
{
var collectionLink = UriFactory.CreateDocumentCollectionUri(databaseId, ukrlp);
var query = documentClient
.CreateDocumentQuery <Provider>(collectionLink, new FeedOptions()
{
})
.Where(d => d.UnitedKingdomProviderReferenceNumber == ukprn)
.AsDocumentQuery();
return((await query.ExecuteNextAsync()).FirstOrDefault());
}
}
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());
}
}
public FileUploadController(IBlobStorageHelper blobStorageHelper)
{
_blobStorageHelper = blobStorageHelper;
}
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 void UploadFile(string pathToFile, IBlobStorageHelper blobStorageHelper)
{
blobStorageHelper.UploadTo(pathToFile, Settings.BlobSasUri);
UploadStatus = FileUploadStatus.Created;
}
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] IBlobStorageHelper blobHelper,
[Inject] ILoggerFactory loggerFactory)
{
var whitelistFileName = "ProviderWhiteList.txt";
var venuesCollectionId = "venues";
var coursesCollectionId = "courses";
var apprenticeshipCollectionId = "apprenticeship";
var blobContainer = configuration["BlobStorageSettings:Container"];
var databaseId = configuration["CosmosDbSettings:DatabaseId"];
var documentClient = cosmosDbHelper.GetClient();
var updatedBy = "ArchiveVenues";
var logger = loggerFactory.CreateLogger(typeof(ArchiveCourses));
var whitelist = await GetProviderWhiteList();
var venueCollectionUri = UriFactory.CreateDocumentCollectionUri(databaseId, venuesCollectionId);
var coursesCollectionUri = UriFactory.CreateDocumentCollectionUri(databaseId, coursesCollectionId);
var apprenticeshipCollectionUri = UriFactory.CreateDocumentCollectionUri(databaseId, apprenticeshipCollectionId);
var totalArchived = 0;
var totalCoursesReferencingOldVenue = 0;
var totalApprenticeshipReferenceoldVenue = 0;
using (var logStream = new MemoryStream())
using (var logStreamWriter = new StreamWriter(logStream))
using (var logCsvWriter = new CsvWriter(logStreamWriter, CultureInfo.InvariantCulture))
{
// archived venues
logCsvWriter.WriteField("UKPRN");
logCsvWriter.WriteField("Archived VenueId");
logCsvWriter.WriteField("Archived Venue Name");
logCsvWriter.WriteField("Archived Venue Address1");
logCsvWriter.WriteField("New VenueId");
logCsvWriter.WriteField("New Venue Name");
logCsvWriter.WriteField("New Venue Address1");
logCsvWriter.WriteField("Course Run Id");
logCsvWriter.WriteField("ApprenticeshipLocation Id");
logCsvWriter.WriteField("Message");
logCsvWriter.WriteField("Type");
logCsvWriter.NextRecord();
foreach (var ukprn in whitelist)
{
try
{
int totalArchivedForProvider = 0;
var allVenuesForProvider = await GetVenues(ukprn);
var allCoursesForProvider = await GetCourses(ukprn);
var allApprenticeshipsForProvider = await GetApprenticeships(ukprn);
//identify duplicates
var comp = new VenueEqualityComparer();
var uniqueGroups = allVenuesForProvider.GroupBy(x => x, comp);
//archive duplicate venues
foreach (var item in uniqueGroups)
{
//tribal venues & trival locations when venues were migrated, both locations and & venues from tribal
//were migrated as seperate records even though the address was the same. The below attempts to merge the two.
var migratedVenues = item.ToList().Where(x => x.CreatedBy == "VenueMigrator" && x.UpdatedBy != updatedBy); //expecting more than one here.
var tribalLocationVenue = migratedVenues.FirstOrDefault(x => x.LocationId != null); //Migrated Location
var tribalVenue = migratedVenues.FirstOrDefault(x => x.VenueID != 0); //Migrated Venue
var currentVenue = MergeVenue(tribalLocationVenue, tribalVenue, out string venueType);
//If there is no current venue, it means that either the venue was created by a previous migration
//e.g. CreatedBy != VenueMigrator and all Venues must be Archived
//OR
//the Archiver has already archived this group e.g. UpdatedBy == "ArchiveVenues" and therefore we skip changing
//this record to archived, as the duplicates have already been removed.
if (currentVenue == null)
{
var venuesNotMigratedByMigrationProcess = item.ToList();
foreach (var archivingVenue in venuesNotMigratedByMigrationProcess)
{
//only archive venues that haven't already been processed by archiveVenues function
if (archivingVenue.UpdatedBy != updatedBy)
{
await ArchiveVenue(archivingVenue, ukprn);
logCsvWriter.WriteField(ukprn);
logCsvWriter.WriteField(archivingVenue.ID);
logCsvWriter.WriteField(archivingVenue.VenueName);
logCsvWriter.WriteField($"{archivingVenue.Address1},{archivingVenue.Address2}, {archivingVenue.PostCode}");
logCsvWriter.WriteField("");
logCsvWriter.WriteField("");
logCsvWriter.WriteField("");
logCsvWriter.WriteField("");
logCsvWriter.WriteField(""); //ApprenticeshipLocationId
logCsvWriter.WriteField($"All old Venues archived, there were {venuesNotMigratedByMigrationProcess.Count()} duplicate Venues.");
logCsvWriter.WriteField("Venue");
logCsvWriter.NextRecord();
}
}
//continue to next ukprn as per the above logic
continue;
}
var nonCurrentVenues = item.ToList().Where(x => x.ID != currentVenue.ID).ToList(); // All venues that will be archived
//if there is a location venue & venue, add venue to list of non current venues
//and update the currentVenue to indicate it has been merged.
if (venueType == "Both")
{
nonCurrentVenues.Add(tribalVenue);
await ReplaceMergedREcord(currentVenue);
}
//courses that have course runs with old venue references.
var courseRunsOldVenue = allCoursesForProvider.Where(p => p.CourseRuns.Any(x => nonCurrentVenues.Where(y => Guid.Parse(y.ID) == x.VenueId).Count() > 0)).ToList();
totalCoursesReferencingOldVenue += courseRunsOldVenue.Count();
//apprenticeships that have locations with old venue refe
var apprenticeshipsOldVenue = allApprenticeshipsForProvider.Where(p => p.ApprenticeshipLocations.Any(x => nonCurrentVenues.Where(y => Guid.Parse(y.ID) == x.LocationGuidId).Count() > 0)).ToList();
totalApprenticeshipReferenceoldVenue += apprenticeshipsOldVenue.Count();
Console.WriteLine($"Archiving {nonCurrentVenues.Count()} - {ukprn} - {currentVenue.Address1}");
//handle archiving venue
foreach (var archivingVenue in nonCurrentVenues)
{
await ArchiveVenue(archivingVenue, ukprn);
logCsvWriter.WriteField(ukprn);
logCsvWriter.WriteField(archivingVenue.ID);
logCsvWriter.WriteField(archivingVenue.VenueName);
logCsvWriter.WriteField($"{archivingVenue.Address1},{archivingVenue.Address2}, {archivingVenue.PostCode}");
logCsvWriter.WriteField(currentVenue.ID);
logCsvWriter.WriteField(currentVenue.VenueName);
logCsvWriter.WriteField($"{currentVenue.Address1},{currentVenue.Address2}, {currentVenue.PostCode}");
logCsvWriter.WriteField("");
logCsvWriter.WriteField(""); //ApprenticeshipLocationId
logCsvWriter.WriteField($"There were {nonCurrentVenues.Count()} duplicate Venues");
logCsvWriter.WriteField("Venue");
logCsvWriter.NextRecord();
totalArchived++;
totalArchivedForProvider++;
//update courses that reference old venues
foreach (var course in courseRunsOldVenue)
{
//update venue to point at new venue.
course.CourseRuns.Where(p => nonCurrentVenues.Any(y => Guid.Parse(y.ID) == p.VenueId))
.ToList()
.ForEach(x =>
{
//update course instance
x.VenueId = Guid.Parse(currentVenue.ID);
x.UpdatedBy = updatedBy;
//log change
logCsvWriter.WriteField(ukprn);
logCsvWriter.WriteField(archivingVenue.ID);
logCsvWriter.WriteField(archivingVenue.VenueName);
logCsvWriter.WriteField($"{archivingVenue.Address1},{archivingVenue.Address2}, {archivingVenue.PostCode}");
logCsvWriter.WriteField(currentVenue.ID);
logCsvWriter.WriteField(currentVenue.VenueName);
logCsvWriter.WriteField($"{currentVenue.Address1},{currentVenue.Address2}, {currentVenue.PostCode}");
logCsvWriter.WriteField(x.CourseInstanceId);
logCsvWriter.WriteField(""); //ApprenticeshipLocationId
logCsvWriter.WriteField($"There were {nonCurrentVenues.Count()} duplicate Venues");
logCsvWriter.WriteField("Course");
logCsvWriter.NextRecord();
});
//update venue to reference currentVenue
var coursedocumentLink = UriFactory.CreateDocumentUri(databaseId, coursesCollectionId, course.id.ToString());
await documentClient.ReplaceDocumentAsync(coursedocumentLink, course, new RequestOptions()
{
PartitionKey = new Microsoft.Azure.Documents.PartitionKey(ukprn)
});
}
//update courses that reference old venues
foreach (var apprenticeship in apprenticeshipsOldVenue)
{
//update venue to point at new venue for locations
apprenticeship.ApprenticeshipLocations.Where(p => nonCurrentVenues.Any(y => Guid.Parse(y.ID) == p.LocationGuidId))
.ToList()
.ForEach(x =>
{
//update apprenticeship location
x.LocationGuidId = Guid.Parse(currentVenue.ID);
x.UpdatedBy = updatedBy;
x.LocationId = currentVenue.LocationId;
//log change
logCsvWriter.WriteField(ukprn);
logCsvWriter.WriteField(archivingVenue.ID);
logCsvWriter.WriteField(archivingVenue.VenueName);
logCsvWriter.WriteField($"{archivingVenue.Address1},{archivingVenue.Address2}, {archivingVenue.PostCode}");
logCsvWriter.WriteField(currentVenue.ID);
logCsvWriter.WriteField(currentVenue.VenueName);
logCsvWriter.WriteField($"{currentVenue.Address1},{currentVenue.Address2}, {currentVenue.PostCode}");
logCsvWriter.WriteField(""); //Course Instance
logCsvWriter.WriteField(x.Id);
logCsvWriter.WriteField($"There were {nonCurrentVenues.Count()} duplicate Venues");
logCsvWriter.WriteField("Apprenticeship");
logCsvWriter.NextRecord();
});
//update apprenticeship to reference currentvenue
var apprenticeshipDocumentLink = UriFactory.CreateDocumentUri(databaseId, apprenticeshipCollectionId, apprenticeship.id.ToString());
await documentClient.ReplaceDocumentAsync(apprenticeshipDocumentLink, apprenticeship, new RequestOptions());
}
}
}
Console.WriteLine($"Archived {totalArchivedForProvider} Venues for {ukprn}");
logger.LogInformation($"Archived {totalArchivedForProvider} Venues for {ukprn}");
}
catch (Exception e)
{
logger.LogError(e.Message);
}
}
// Upload log CSV to blob storage
{
logStreamWriter.Flush();
logStream.Seek(0L, SeekOrigin.Begin);
var blob = blobHelper.GetBlobContainer(blobContainer).GetBlockBlobReference("ArchivedVenues");
await blob.UploadFromStreamAsync(logStream);
}
}
Console.WriteLine($"Total Course runs that reference an old venue: {totalCoursesReferencingOldVenue}");
Console.WriteLine($"Total Apparenticeships that reference an old Venue {totalApprenticeshipReferenceoldVenue}");
Console.WriteLine($"Total Archived Venues {totalArchived}");
async Task ArchiveVenue(Venue archivingVenue, int ukprn)
{
//archive venue
archivingVenue.Status = VenueStatus.Archived;
archivingVenue.UpdatedBy = updatedBy;
archivingVenue.DateUpdated = DateTime.Now;
var documentLink = UriFactory.CreateDocumentUri(databaseId, venuesCollectionId, archivingVenue.ID.ToString());
await documentClient.ReplaceDocumentAsync(documentLink, archivingVenue, new RequestOptions());
}
async Task ReplaceMergedREcord(Venue mergedRecord)
{
//archive venue
mergedRecord.UpdatedBy = updatedBy;
mergedRecord.DateUpdated = DateTime.Now;
var documentLink = UriFactory.CreateDocumentUri(databaseId, venuesCollectionId, mergedRecord.ID.ToString());
await documentClient.ReplaceDocumentAsync(documentLink, mergedRecord, new RequestOptions());
}
Venue MergeVenue(Venue locationVenue, Venue venue, out string selectedVenue)
{
//default to first none null venue, location is chosen first.
var ven = locationVenue ?? venue;
if (locationVenue != null && venue != null)
{
selectedVenue = "Both";
}
else if (locationVenue == null && venue != null)
{
selectedVenue = "Venue";
}
else if (locationVenue != null && venue == null)
{
selectedVenue = "Location";
}
else
{
selectedVenue = "None";
}
//if there are two venues, one with a venue id & one with a location id.
//merge them.
if (locationVenue != null && venue != null)
{
ven.VenueID = venue.VenueID;
}
return(ven);
}
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 && p.Status == VenueStatus.Live)
.AsDocumentQuery()
.ExecuteNextAsync <Venue>();
venues.AddRange(queryResponse.ToList());
continuation = queryResponse.ResponseContinuation;
}while (continuation != null);
return(venues);
}
async Task <List <Apprenticeship> > GetApprenticeships(int ukprn)
{
var apprenticeships = new List <Apprenticeship>();
string continuation = null;
//get all apprenticeships for provider
do
{
var feedOptions = new FeedOptions()
{
RequestContinuation = continuation,
PartitionKey = new Microsoft.Azure.Documents.PartitionKey(ukprn)
};
//try/catch required as there are Apprenticeship records that are not valid (venueId is null in cosmos).
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(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);
}
}
public async Task Run(
ILogger log,
IConfigurationRoot configuration,
ICosmosDbHelper cosmosDbHelper,
IBlobStorageHelper blobHelper,
IProviderCollectionService providerCollectionService,
ICourseCollectionService courseCollectionService,
IApprenticeshipCollectionService apprenticeshipCollectionService,
IMigrationReportCollectionService migrationReportCollectionService)
{
log.LogInformation("Starting Migration Report generation");
var migrationLog = new StringBuilder();
migrationLog.AppendLine("-------------------------------------------------------");
migrationLog.AppendLine("Starting Migration Report generation");
migrationLog.AppendLine("-------------------------------------------------------");
var stopWatch = new Stopwatch();
stopWatch.Start();
IReadOnlyList <RecordStatus> migratedStatusList = new List <RecordStatus>
{
RecordStatus.Live,
RecordStatus.MigrationPending,
};
var blobContainer = blobHelper.GetBlobContainer(configuration["BlobStorageSettings:Container"]);
var whiteListedProviders = await GetProviderWhiteList(blobHelper, blobContainer);
var cosmosClient = cosmosDbHelper.GetClient();
log.LogDebug("Fetching migrated provider count...");
var migratedProvidersCount = (await providerCollectionService.GetAllMigratedProviders("Provider.Migrator")).Count;
log.LogDebug($"Migrated Provider count: {migratedProvidersCount}.");
log.LogDebug("Fetching providers...");
var providers = await providerCollectionService.GetDocumentsByUkprn(whiteListedProviders);
var providerTypeCounts = providers.GroupBy(t => t.ProviderType).Select(g => new { type = g.Key, qty = g.Count() });
log.LogDebug($"Provider counts: {string.Join("; ", providerTypeCounts.Select(c => $"{c.type}: {c.qty}"))}. Total: {providers.Count}");
int progress = 1;
int feCourseReportEntryCount = 0;
int apprenticeshipsReportEntryCount = 0;
foreach (var ukprn in whiteListedProviders)
{
try
{
var provider = providers.Single(p => int.Parse(p.UnitedKingdomProviderReferenceNumber) == ukprn);
var logStart = $"STARTED : Generating report for provider with UKPRN: {provider.UnitedKingdomProviderReferenceNumber}. Progress: {progress++}/{whiteListedProviders.Count}";
log.LogDebug(logStart);
migrationLog.AppendLine(logStart);
switch (provider.ProviderType)
{
case ProviderType.Both:
await GenerateApprenticeshipReport(
configuration, apprenticeshipCollectionService, migrationReportCollectionService, provider,
migratedStatusList, cosmosClient);
apprenticeshipsReportEntryCount++;
await GenerateFECourseReport(
configuration, courseCollectionService, migrationReportCollectionService, provider,
migratedStatusList, cosmosClient);
feCourseReportEntryCount++;
break;
case ProviderType.Apprenticeship:
await GenerateApprenticeshipReport(
configuration, apprenticeshipCollectionService, migrationReportCollectionService, provider,
migratedStatusList, cosmosClient);
apprenticeshipsReportEntryCount++;
break;
case ProviderType.FE:
await GenerateFECourseReport(
configuration, courseCollectionService, migrationReportCollectionService, provider,
migratedStatusList, cosmosClient);
feCourseReportEntryCount++;
break;
case ProviderType.Undefined:
break;
default:
throw new ArgumentOutOfRangeException();
}
migrationLog.AppendLine($"COMPLETED : Report for provider {provider.UnitedKingdomProviderReferenceNumber}");
}
catch (Exception ex)
{
migrationLog.AppendLine($"Error creating report for {ukprn}. {ex.GetBaseException().Message}");
log.LogError(ex, $"Error creating report for {ukprn}.");
}
}
stopWatch.Stop();
migrationLog.AppendLine("----------------------------------------------------------------");
migrationLog.AppendLine($"Completed Migration Report generation in {stopWatch.Elapsed.TotalMinutes} minutes.");
migrationLog.AppendLine($"Course Report Entries : {feCourseReportEntryCount} for {migratedProvidersCount} migrated providers.");
migrationLog.AppendLine($"Apps Report Entries : {apprenticeshipsReportEntryCount} for {migratedProvidersCount} migrated providers");
migrationLog.AppendLine("----------------------------------------------------------------");
log.LogDebug(migrationLog.ToString());
await blobHelper.UploadFile(
blobContainer,
$"MigrationReport_LogFile-{DateTime.Now:dd-MM-yy HHmm}.txt",
GetResultAsByteArray(migrationLog));
log.LogInformation($"Completed Migration Report generation. {feCourseReportEntryCount + apprenticeshipsReportEntryCount} records processed.");
}
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] IBlobStorageHelper blobHelper,
[Inject] ILoggerFactory loggerFactory)
{
var cosmosDbClient = cosmosDbHelper.GetClient();
var databaseId = configuration["CosmosDbSettings:DatabaseId"];
var apprenticeshipCollectionId = "apprenticeship";
var whitelistFileName = "ProviderWhiteList.txt";
var blobContainer = configuration["BlobStorageSettings:Container"];
var whitelist = await GetProviderWhiteList();
var logFileName = $"SetRadiusToTen-{DateTime.Now.ToString("dd-MM-yy HHmm")}";
var apprenticehipsUri = UriFactory.CreateDocumentCollectionUri(databaseId, apprenticeshipCollectionId);
var logger = loggerFactory.CreateLogger(typeof(SetApprenticeshipLocationRadiusToTen));
using (var logStream = new MemoryStream())
using (var logStreamWriter = new StreamWriter(logStream))
using (var logCsvWriter = new CsvWriter(logStreamWriter, CultureInfo.InvariantCulture))
{
logCsvWriter.WriteField("ApprenticeshoId");
logCsvWriter.WriteField("ApprenticeshipLocationId");
logCsvWriter.NextRecord();
try
{
foreach (var ukprn in whitelist)
{
string continuation = null;
do
{
var feedOptions = new FeedOptions()
{
RequestContinuation = continuation,
PartitionKey = new Microsoft.Azure.Documents.PartitionKey(ukprn)
};
var queryResponse = await cosmosDbClient.CreateDocumentQuery <Apprenticeship>(apprenticehipsUri, feedOptions)
.Where(p => p.ProviderUKPRN == ukprn)
.AsDocumentQuery()
.ExecuteNextAsync <Apprenticeship>();
foreach (var doc in queryResponse)
{
foreach (var loc in doc.ApprenticeshipLocations)
{
if (loc.Radius == 10)
{
loc.Radius = 30;
loc.UpdatedBy = nameof(SetApprenticeshipLocationRadiusToTen);
logCsvWriter.WriteField(doc.id);
logCsvWriter.WriteField(loc.Id);
logCsvWriter.NextRecord();
}
}
if (doc.ApprenticeshipLocations.Any(x => x.UpdatedBy == nameof(SetApprenticeshipLocationRadiusToTen)))
{
doc.UpdatedBy = nameof(SetApprenticeshipLocationRadiusToTen);
var documentLink = UriFactory.CreateDocumentUri(databaseId, apprenticeshipCollectionId, doc.id.ToString());
await cosmosDbClient.ReplaceDocumentAsync(documentLink, doc, new RequestOptions()
{
PartitionKey = new Microsoft.Azure.Documents.PartitionKey(ukprn)
});
}
}
continuation = queryResponse.ResponseContinuation;
} while (continuation != null);
}
} catch (Exception e)
{
logger.LogError(e.Message);
}
// 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);
}
}
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
[Inject] IConfigurationRoot configuration,
[Inject] ICosmosDbHelper cosmosDbHelper,
[Inject] IBlobStorageHelper blobHelper,
[Inject] ILoggerFactory loggerFactory,
[Inject] IBlobStorageHelper blobhelper)
{
var blobContainer = configuration["BlobStorageSettings:Container"];
var outputContainer = blobhelper.GetBlobContainer(configuration["BlobStorageSettings:Container"]);
var databaseId = configuration["CosmosDbSettings:DatabaseId"];
var apprenticeshipCollectionId = "apprenticeship";
var documentClient = cosmosDbHelper.GetClient();
var apprenticeshipCollectionUri = UriFactory.CreateDocumentCollectionUri(databaseId, apprenticeshipCollectionId);
var logger = loggerFactory.CreateLogger(typeof(ArchiveOldMigratedApprenticeships));
int count = 0;
var updatedBy = "ArchiveOldMigratedApprenticeships";
string continuation = null;
do
{
var feedOptions = new FeedOptions()
{
RequestContinuation = continuation,
EnableCrossPartitionQuery = true
};
//try/catch required as there are Apprenticeship records that are not valid (venueId is null in cosmos).
try
{
var queryResponse = await documentClient.CreateDocumentQuery <Apprenticeship>(apprenticeshipCollectionUri, feedOptions)
.Where(p => p.CreatedBy == "DFC – Apprenticeship Migration Tool")
.AsDocumentQuery()
.ExecuteNextAsync <Apprenticeship>();
foreach (var doc in queryResponse)
{
if (doc.RecordStatus.HasFlag(CourseDirectory.Models.Enums.RecordStatus.MigrationPending) || doc.RecordStatus == CourseDirectory.Models.Enums.RecordStatus.Live)
{
//mark every location as arhived
foreach (var loc in doc.ApprenticeshipLocations)
{
loc.RecordStatus = CourseDirectory.Models.Enums.RecordStatus.Archived;
loc.UpdatedBy = updatedBy;
}
doc.UpdatedBy = updatedBy;
var documentLink = UriFactory.CreateDocumentUri(databaseId, apprenticeshipCollectionId, doc.id.ToString());
await documentClient.ReplaceDocumentAsync(documentLink, doc, new RequestOptions());
count++;
}
}
continuation = queryResponse.ResponseContinuation;
}
catch (Exception)
{
continuation = null;
}
}while (continuation != null);
logger.LogInformation($"Archived {count} Apprenticeships");
Console.WriteLine($"Archived {count} Apprenticeships");
}
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);
}
}
}
