public async Task <MigrationReportEntry> GetReportForApprenticeshipByUkprn(int ukprn)
{
var documents = new List <MigrationReportEntry>();
var uri = UriFactory.CreateDocumentCollectionUri(_cosmosDbSettings.DatabaseId, _cosmosDbCollectionSettings.MigrationReportApprenticeshipCollectionId);
var sql = $"SELECT * FROM a WHERE a.id = \"{ukprn}\"";
var options = new FeedOptions {
EnableCrossPartitionQuery = true, MaxItemCount = -1
};
var client = _cosmosDbHelper.GetClient();
using (var query = client.CreateDocumentQuery(uri, sql, options).AsDocumentQuery())
{
while (query.HasMoreResults)
{
foreach (MigrationReportEntry document in await query.ExecuteNextAsync <MigrationReportEntry>())
{
documents.Add(document);
}
}
}
return(documents.FirstOrDefault());
}
public async Task <List <Apprenticeship> > GetAllApprenticeshipsAsync()
{
var documents = new List <Apprenticeship>();
var uri = UriFactory.CreateDocumentCollectionUri(_cosmosDbSettings.DatabaseId, _cosmosDbCollectionSettings.ApprenticeshipCollectionId);
var sql = $"SELECT * FROM a";
var options = new FeedOptions {
EnableCrossPartitionQuery = true, MaxItemCount = -1
};
var client = _cosmosDbHelper.GetClient();
using (var query = client.CreateDocumentQuery(uri, sql, options).AsDocumentQuery())
{
while (query.HasMoreResults)
{
foreach (Apprenticeship document in await query.ExecuteNextAsync <Apprenticeship>())
{
documents.Add(document);
}
}
}
return(documents);
}
public async Task <CourseAudit> Audit(ILogger log, Document auditee)
{
Throw.IfNull(auditee, nameof(auditee));
Throw.IfNull(auditee.GetPropertyValue <Guid>("id"), "Document id");
CourseAudit persisted;
Guid id = auditee.GetPropertyValue <Guid>("id");
try
{
log.LogInformation($"Writing audit for course { id }");
using (var client = _cosmosDbHelper.GetClient())
{
await _cosmosDbHelper.CreateDocumentCollectionIfNotExistsAsync(client, _settings.AuditCollectionId);
Document doc = await _cosmosDbHelper.CreateDocumentAsync(client, _settings.AuditCollectionId,
new CourseAudit()
{
id = Guid.NewGuid(),
Collection = _settings.CoursesCollectionId,
DocumentId = id.ToString(),
UpdatedBy = auditee.GetPropertyValue <string>("UpdatedBy") ?? auditee.GetPropertyValue <string>("CreatedBy"),
UpdatedDate = auditee.GetPropertyValue <DateTime?>("UpdatedDate") ?? DateTime.Now,
Document = auditee
});
persisted = _cosmosDbHelper.DocumentTo <CourseAudit>(doc);
}
return(persisted);
}
catch (Exception ex)
{
throw ex;
}
}
public async Task Initialise()
{
using (var client = _cosmosDbHelper.GetClient())
{
await _cosmosDbHelper.CreateDocumentCollectionIfNotExistsAsync(client,
_settings.CoursesMigrationReportCollectionId);
}
}
public Task <IEnumerable <ApprenticeshipFramework> > GetAllAsync()
{
var uri = UriFactory.CreateDocumentCollectionUri(_cosmosDbSettings.DatabaseId, _cosmosDbCollectionSettings.FrameworksCollectionId);
var sql = $"SELECT * FROM c";
var options = new FeedOptions {
EnableCrossPartitionQuery = true, MaxItemCount = -1
};
var client = _cosmosDbHelper.GetClient();
var results = client.CreateDocumentQuery <ApprenticeshipFramework>(uri, sql, options).AsEnumerable();
return(Task.FromResult(results));
}
public async Task <ICourseText> GetCourseTextByLARS(string LARSRef)
{
Throw.IfNull(LARSRef, nameof(LARSRef));
CourseTextModel persisted = null;
using (var client = _cosmosDbHelper.GetClient())
{
var docs = _cosmosDbHelper.GetCourseTextByLARS(client, _settings.CourseTextCollectionId, LARSRef);
persisted = docs;
}
return(persisted);
}
public async Task <IEnumerable <DfcMigrationReport> > GetDfcReports()
{
try
{
using (var client = _cosmosDbHelper.GetClient())
{
var result = await _cosmosDbHelper.GetAllDfcMigrationReports(client,
_settings.DfcReportCollectionId);
return(result);
}
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
public async Task <IEnumerable <Document> > ArchiveAllCourses(ILogger log, IEnumerable <Document> documents)
{
Throw.IfNull(log, nameof(log));
Throw.IfNullOrEmpty(documents, nameof(documents));
List <Document> responseList = new List <Document>();
if (documents.Any())
{
using (var client = _cosmosDbHelper.GetClient())
{
Uri uri = UriFactory.CreateDocumentCollectionUri(_dbSettings.DatabaseId, _settings.CoursesCollectionId);
FeedOptions options = new FeedOptions {
EnableCrossPartitionQuery = true, MaxItemCount = -1
};
foreach (var doc in documents)
{
int UKPRN = doc.GetPropertyValue <int?>("UnitedKingdomProviderReferenceNumber") ?? 0;
if (UKPRN > 10000000)
{
log.LogInformation($"Processing document with UKPRN {UKPRN}");
IEnumerable <Course> courseDocs = client.CreateDocumentQuery <Course>(uri, options)
.Where(x => x.ProviderUKPRN == UKPRN);
foreach (Course courseDoc in courseDocs)
{
log.LogInformation($"Archiving course with id {courseDoc.id}");
Uri docUri = UriFactory.CreateDocumentUri(_dbSettings.DatabaseId, _settings.CoursesCollectionId, courseDoc.id.ToString());
//var result = await client.DeleteDocumentAsync(docUri, new RequestOptions() { PartitionKey = new PartitionKey(doc.ProviderUKPRN) });
Document d = client.ReadDocumentAsync(docUri, new RequestOptions()
{
PartitionKey = new PartitionKey(UKPRN)
})
?.Result
?.Resource;
if (d == null)
{
log.LogInformation($"** Course with id {courseDoc.id} and Title {courseDoc.QualificationCourseTitle} wasn't archived");
}
else
{
d.SetPropertyValue("CourseStatus", (int)RecordStatus.Archived);
//result = await client.UpsertDocumentAsync(docUri, result.Resource);
d = await _cosmosDbHelper.UpdateDocumentAsync(client, _settings.CoursesCollectionId, d);
responseList.Add(d);
log.LogInformation($" archived successfully");
}
}
}
}
}
}
return(responseList);
}
public async Task <Apprenticeship> GetApprenticeshipByApprenticeshipID(int apprenticeshipId)
{
var uri = UriFactory.CreateDocumentCollectionUri(_cosmosDbSettings.DatabaseId, _cosmosDbCollectionSettings.ApprenticeshipCollectionId);
var sql = $"SELECT* FROM c WHERE c.ApprenticeshipId = { apprenticeshipId }";
var options = new FeedOptions {
EnableCrossPartitionQuery = true, MaxItemCount = -1
};
var client = _cosmosDbHelper.GetClient();
var query = client.CreateDocumentQuery <Apprenticeship>(uri, sql, options).AsDocumentQuery();
return((await query.ExecuteNextAsync()).FirstOrDefault());
}
public async Task <IEnumerable <ICourse> > GetAllCourses(ILogger log)
{
try {
// Get all course documents in the collection
string token = null;
Task <FeedResponse <dynamic> > task = null;
List <dynamic> docs = new List <dynamic>();
log.LogInformation("Getting all courses from collection");
// Read documents in batches, using continuation token to make sure we get them all
using (DocumentClient client = _cosmosDbHelper.GetClient()) {
do
{
task = client.ReadDocumentFeedAsync(UriFactory.CreateDocumentCollectionUri("providerportal", _settings.CoursesCollectionId),
new FeedOptions {
MaxItemCount = -1, RequestContinuation = token
});
token = task.Result.ResponseContinuation;
log.LogInformation("Collating results");
docs.AddRange(task.Result.ToList());
} while (token != null);
}
// Cast the returned data by serializing to json and then deserialising into Course objects
log.LogInformation($"Serializing data for {docs.LongCount()} courses");
string json = JsonConvert.SerializeObject(docs);
return(JsonConvert.DeserializeObject <IEnumerable <Course> >(json));
} catch (Exception ex) {
throw ex;
}
}
public async Task <IEnumerable <FeChoice> > GetAllAsync()
{
var uri = UriFactory.CreateDocumentCollectionUri(_cosmosDbSettings.DatabaseId, _cosmosDbCollectionSettings.FeChoicesCollectionId);
var sql = $"SELECT * FROM c";
var options = new FeedOptions {
EnableCrossPartitionQuery = true, MaxItemCount = -1
};
var client = _cosmosDbHelper.GetClient();
var query = client.CreateDocumentQuery <FeChoice>(uri, sql, options).AsDocumentQuery();
var results = new List <FeChoice>();
while (query.HasMoreResults)
{
var response = await query.ExecuteNextAsync <FeChoice>();
results.AddRange(response);
}
return(results);
}
public async Task UpdateReport(int ukprn)
{
using (var client = _cosmosDbHelper.GetClient())
{
var courses = await _cosmosDbHelper.GetCourseCollectionDocumentsByUKPRN <Course>(client, _settings.CoursesCollectionId,
ukprn);
var migrationReport = (await _cosmosDbHelper.GetCourseCollectionDocumentsByUKPRN <CourseMigrationReport>(client,
_settings.CoursesMigrationReportCollectionId,
ukprn)).FirstOrDefault();
var provider = await _cosmosDbHelper.GetProviderByUKPRN(client, _settings.ProviderCollectionId,
ukprn);
if (provider == null || !HasValidReportOrCourses(courses, migrationReport))
{
throw new Exception($"Unable to generate report for Provider: {ukprn}");
}
var report = new CourseReportDocument
{
ProviderUKPRN = ukprn.ToString(),
MigrationPendingCount = courses.SelectMany(x => x.CourseRuns.Where(cr => cr.RecordStatus == RecordStatus.MigrationPending)).Count(),
MigrationReadyToGoLive = courses.SelectMany(x => x.CourseRuns.Where(cr => cr.RecordStatus == RecordStatus.MigrationReadyToGoLive)).Count(),
BulkUploadPendingcount = courses.SelectMany(x => x.CourseRuns.Where(cr => cr.RecordStatus == RecordStatus.BulkUloadPending)).Count(),
BulkUploadReadyToGoLiveCount = courses.SelectMany(x => x.CourseRuns.Where(cr => cr.RecordStatus == RecordStatus.BulkUploadReadyToGoLive)).Count(),
FailedMigrationCount = migrationReport?.LarslessCourses?.SelectMany(cr => cr.CourseRuns)?.Count(),
LiveCount = courses.SelectMany(c => c.CourseRuns.Where(cr => cr.RecordStatus == RecordStatus.Live)).Count(),
MigratedCount = migrationReport?.PreviousLiveCourseCount,
MigrationDate = migrationReport?.Timestamp,
MigrationRate = decimal.Round(MigrationRate(courses), 2, MidpointRounding.AwayFromZero),
ProviderName = provider.ProviderName,
ProviderType = provider.ProviderType
};
await _cosmosDbHelper.UpdateDocumentAsync(client, _settings.DfcReportCollectionId, report);
}
}
public async Task UpdateReport(int ukprn)
{
using (var client = _cosmosDbHelper.GetClient())
{
var apprenticeships = await _cosmosDbHelper.GetCourseCollectionDocumentsByUKPRN <Apprenticeship>(client, _settings.ApprenticeshipCollectionId,
ukprn);
var migrationReport = (await _cosmosDbHelper.GetCourseCollectionDocumentsByUKPRN <ApprenticeshipMigrationReport>(client,
_settings.ApprenticeshipMigrationReportCollectionId,
ukprn)).FirstOrDefault();
var provider = await _cosmosDbHelper.GetProviderByUKPRN(client, _settings.ProviderCollectionId,
ukprn);
if (provider == null || !HasValidReportOrApprenticeships(apprenticeships, migrationReport))
{
throw new Exception($"Unable to generate report for Provider: {ukprn}");
}
var report = new ApprenticeshipDfcReportDocument()
{
ProviderUKPRN = ukprn.ToString(),
MigrationPendingCount = apprenticeships.SelectMany(x => x.ApprenticeshipLocations.Where(cr => cr.RecordStatus == RecordStatus.MigrationPending)).Count(),
MigrationReadyToGoLive = apprenticeships.SelectMany(x => x.ApprenticeshipLocations.Where(cr => cr.RecordStatus == RecordStatus.MigrationReadyToGoLive)).Count(),
BulkUploadPendingcount = apprenticeships.SelectMany(x => x.ApprenticeshipLocations.Where(cr => cr.RecordStatus == RecordStatus.BulkUloadPending)).Count(),
BulkUploadReadyToGoLiveCount = apprenticeships.SelectMany(x => x.ApprenticeshipLocations.Where(cr => cr.RecordStatus == RecordStatus.BulkUploadReadyToGoLive)).Count(),
FailedMigrationCount = migrationReport?.NotTransferred,
LiveCount = apprenticeships.SelectMany(c => c.ApprenticeshipLocations.Where(cr => cr.RecordStatus == RecordStatus.Live)).Count(),
MigratedCount = migrationReport?.ApprenticeshipsMigrated,
MigrationDate = migrationReport?.MigrationDate,
MigrationRate = decimal.Round(MigrationRate(apprenticeships), 2, MidpointRounding.AwayFromZero),
ProviderName = provider.ProviderName,
ProviderType = provider.ProviderType
};
await _cosmosDbHelper.UpdateDocumentAsync(client, _settings.ApprenticeshipDfcReportCollection, report);
}
}
public ProviderCollectionService(
ICosmosDbHelper cosmosDbHelper,
IOptions <CosmosDbSettings> cosmosDbSettings,
IOptions <CosmosDbCollectionSettings> cosmosDbCollectionSettings)
{
Throw.IfNull(cosmosDbHelper, nameof(cosmosDbHelper));
Throw.IfNull(cosmosDbSettings, nameof(cosmosDbSettings));
Throw.IfNull(cosmosDbCollectionSettings, nameof(cosmosDbCollectionSettings));
_cosmosDbHelper = cosmosDbHelper;
_client = cosmosDbHelper.GetClient();
_cosmosDbSettings = cosmosDbSettings.Value;
_cosmosDbCollectionSettings = cosmosDbCollectionSettings.Value;
}
public VenueCollectionService(
ICosmosDbHelper cosmosDbHelper,
IOptions <CosmosDbSettings> cosmosDbSettings,
IOptions <CosmosDbCollectionSettings> cosmosDbCollectionSettings)
{
Throw.IfNull(cosmosDbHelper, nameof(cosmosDbHelper));
Throw.IfNull(cosmosDbSettings, nameof(cosmosDbSettings));
Throw.IfNull(cosmosDbCollectionSettings, nameof(cosmosDbCollectionSettings));
_cosmosDbHelper = cosmosDbHelper;
_cosmosDbSettings = cosmosDbSettings.Value;
_cosmosDbCollectionSettings = cosmosDbCollectionSettings.Value;
_documentClient = _cosmosDbHelper.GetClient();
}
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] ILoggerFactory loggerFactory
)
{
var logger = loggerFactory.CreateLogger(typeof(DeleteInvalidVenues));
var databaseId = configuration["CosmosDbSettings:DatabaseId"];
var venueCollectionId = "venues";
var documentClient = cosmosDbHelper.GetClient();
var venueCollectionUri = UriFactory.CreateDocumentCollectionUri(databaseId, venueCollectionId);
var deleteCount = 0;
string continuation = null;
do
{
var feedOptions = new FeedOptions()
{
RequestContinuation = continuation
};
var queryResponse = await documentClient.CreateDocumentQuery <Venue>(venueCollectionUri, feedOptions)
.Where(p => p.Latitude == null || p.Longitude == null)
.AsDocumentQuery()
.ExecuteNextAsync <Venue>();
foreach (var ven in queryResponse)
{
var item = UriFactory.CreateDocumentUri(databaseId, venueCollectionId, ven.ID);
await documentClient.DeleteDocumentAsync(item);
deleteCount++;
logger.LogInformation($"Deleted venue: {ven.VenueID}");
}
continuation = queryResponse.ResponseContinuation;
}while (continuation != null);
Console.WriteLine($"Deleted {deleteCount} venues with missing lat/long.");
logger.LogError($"Deleted {deleteCount} venues with missing lat/long.");
}
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] ILoggerFactory loggerFactory,
[Inject] IApprenticeshipCollectionService apprenticeshipCollectionService)
{
var databaseId = configuration["CosmosDbSettings:DatabaseId"];
var apprenticeshipCollectionId = "apprenticeship";
var documentClient = cosmosDbHelper.GetClient();
var logger = loggerFactory.CreateLogger(typeof(ArchivePendingApprenticeships));
int count = 0;
var updatedBy = "ArchivePendingApprenticeships";
var queryResponse = await apprenticeshipCollectionService.GetArchivedApprenticeshipsAsync();
foreach (var doc in queryResponse)
{
//mark every location as arhived regardless of their status
foreach (var loc in doc.ApprenticeshipLocations)
{
loc.RecordStatus = CourseDirectory.Models.Enums.RecordStatus.Archived;
loc.UpdatedBy = updatedBy;
loc.UpdatedDate = DateTime.UtcNow;
}
doc.UpdatedBy = updatedBy;
doc.UpdatedDate = DateTime.UtcNow;
var documentLink = UriFactory.CreateDocumentUri(databaseId, apprenticeshipCollectionId, doc.id.ToString());
await documentClient.ReplaceDocumentAsync(documentLink, doc);
count++;
}
logger.LogInformation($"Archived {count} Apprenticeships");
Console.WriteLine($"Archived {count} Apprenticeships");
}
public async Task CreateApprenticeshipReport(ApprenticeshipMigrationReport report)
{
var client = _cosmosDbHelper.GetClient();
await _cosmosDbHelper.CreateDatabaseIfNotExistsAsync(client);
await _cosmosDbHelper.CreateDocumentCollectionIfNotExistsAsync(client,
_settings.ApprenticeshipReportCollectionId);
var result = _cosmosDbHelper.GetDocumentsByUKPRN <ApprenticeshipMigrationReport>(client, _settings.ApprenticeshipReportCollectionId,
report.ProviderUKPRN);
if (result.Any())
{
report.Id = result.FirstOrDefault().Id;
await _cosmosDbHelper.UpdateDocumentAsync(client, _settings.ApprenticeshipReportCollectionId,
report);
}
else
{
var doc = await _cosmosDbHelper.CreateDocumentAsync(client, _settings.ApprenticeshipReportCollectionId, report);
}
}
public async Task <IApprenticeship> AddApprenticeship(IApprenticeship apprenticeship)
{
Throw.IfNull(apprenticeship, nameof(apprenticeship));
Apprenticeship persisted;
var client = _cosmosDbHelper.GetClient();
await _cosmosDbHelper.CreateDatabaseIfNotExistsAsync(client);
var doc = await _cosmosDbHelper.CreateDocumentAsync(client, _settings.ApprenticeshipCollectionId, apprenticeship);
persisted = _cosmosDbHelper.DocumentTo <Apprenticeship>(doc);
return(persisted);
}
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] 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 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(
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
[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
[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 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] IVenueCollectionService venueCollectionService,
[Inject] ILarsSearchService larsSearchService,
[Inject] IBlobStorageHelper blobHelper)
{
var databaseId = configuration["CosmosDbSettings:DatabaseId"];
var coursesCollectionId = "courses";
var logFileName = $"CourseMigrator-{DateTime.Now.ToString("dd-MM-yy HHmm")}";
var blobContainer = configuration["BlobStorageSettings:Container"];
var whitelistFileName = "ProviderWhiteList.txt";
var connectionString = configuration.GetConnectionString("TribalRestore");
var cosmosDbClient = cosmosDbHelper.GetClient();
using (var logStream = new MemoryStream())
using (var logStreamWriter = new StreamWriter(logStream))
using (var logCsvWriter = new CsvWriter(logStreamWriter, CultureInfo.InvariantCulture))
using (var conn1 = new SqlConnection(connectionString))
using (var conn2 = new SqlConnection(connectionString))
{
// Log CSV headers
logCsvWriter.WriteField("CourseId");
logCsvWriter.WriteField("UKPRN");
logCsvWriter.WriteField("Success");
logCsvWriter.WriteField("Status");
logCsvWriter.WriteField("Course instances");
logCsvWriter.WriteField("Error list");
logCsvWriter.NextRecord();
var whitelist = await GetProviderWhiteList();
await conn1.OpenAsync();
await conn2.OpenAsync();
using (var coursesCmd = conn1.CreateCommand())
using (var coursesInstancesCmd = conn2.CreateCommand())
{
coursesCmd.CommandTimeout = 60 * 60; // 1 hour
coursesInstancesCmd.CommandTimeout = 60 * 60; // 1 hour
coursesCmd.CommandText = @"
SELECT
c.CourseId,
c.CourseTitle,
c.CourseSummary,
c.LearningAimRefId,
c.QualificationLevelId,
c.EntryRequirements,
c.ProviderOwnCourseRef,
c.Url,
p.UKPRN,
c.EquipmentRequired,
c.AssessmentMethod,
p.Loans24Plus
FROM Course c
JOIN Provider p ON c.ProviderId = p.ProviderId
WHERE c.RecordStatusId = 2 --Live
--Last updated within 24 months of data freeze 28/02
AND (c.ModifiedDateTimeUtc >= '2018-02-28' OR EXISTS (
SELECT 1 FROM CourseInstance ci
WHERE ci.CourseId = c.CourseId
AND ci.RecordStatusId = 2
AND ci.ModifiedDateTimeUtc >= '2018-02-28'
))
ORDER BY c.CourseId, c.ProviderId";
coursesInstancesCmd.CommandText = @"
SELECT
ci.CourseInstanceId,
ci.CourseId,
ci.ProviderOwnCourseInstanceRef,
ci.StudyModeId,
ci.AttendanceTypeId,
ci.AttendancePatternId,
ci.DurationUnit,
ci.DurationUnitId,
ci.DurationAsText,
ci.StartDateDescription,
cisd.StartDate,
ci.Price,
ci.PriceAsText,
ci.Url,
civ.VenueId,
ci.VenueLocationId
FROM CourseInstance ci
LEFT JOIN CourseInstanceVenue civ ON ci.CourseInstanceId = civ.CourseInstanceId
LEFT JOIN CourseInstanceStartDate cisd ON ci.CourseInstanceId = cisd.CourseInstanceId
WHERE ci.RecordStatusId = 2 --Live
ORDER BY ci.CourseId, ci.OfferedByProviderId";
using (var coursesReader = coursesCmd.ExecuteReader())
using (var courseInstanceReader = coursesInstancesCmd.ExecuteReader())
{
var instanceReader = new CourseInstanceReader(courseInstanceReader);
var instanceEnumerator = instanceReader.ProcessReader().GetEnumerator();
var courseRowReader = coursesReader.GetRowParser <CourseResult>();
while (await coursesReader.ReadAsync())
{
var course = courseRowReader(coursesReader);
// If provider is not on whitelist - skip this course
if (!whitelist.Contains(course.UKPRN))
{
continue;
}
var instances = instanceReader.ConsumeReader(instanceEnumerator, course.CourseId);
var errors = new List <string>();
CourseMigrationResult result;
try
{
// Tribal don't have any Courses with zero CourseInstances...
if (instances.Count == 0)
{
errors.Add("Found zero CourseInstances.");
}
// Check LARS
var larsSearchResults = !string.IsNullOrEmpty(course.LearningAimRefId) ?
await QueryLars(course.LearningAimRefId) :
Array.Empty <LarsSearchResultItem>();
// Check the venues exist
Dictionary <int, Guid> venueIdMap = new Dictionary <int, Guid>();
foreach (var venueId in instances.Where(i => i.VenueId.HasValue).Select(i => i.VenueId.Value))
{
if (venueIdMap.ContainsKey(venueId))
{
continue;
}
var cosmosVenue = await venueCollectionService.GetDocumentByVenueId(venueId);
if (cosmosVenue == null)
{
errors.Add($"Missing venue {venueId}.");
}
else
{
venueIdMap.Add(venueId, Guid.Parse(cosmosVenue.ID));
}
}
if (errors.Count == 0)
{
// Got the course in Cosmos already?
var existingCourseRecord = await GetExistingCourse(course.CourseId, course.UKPRN, cosmosDbClient);
var mappedCourseRuns = instances
.Select(i =>
{
Guid?venueId = null;
if (i.VenueId.HasValue)
{
venueId = venueIdMap[i.VenueId.Value];
}
// Retain the existing Cosmos ID if there is one
// N.B. We can have more than one match on CourseInstanceId since we 'explode' on multiple start dates
var courseRunId =
existingCourseRecord?.CourseRuns.SingleOrDefault(r => r.CourseInstanceId == i.CourseInstanceId && r.StartDate == i.StartDate)?.id ??
Guid.NewGuid();
return(MapCourseInstance(course, i, courseRunId, venueId, errors));
})
.ToList();
var courseId = existingCourseRecord?.id ?? Guid.NewGuid();
var mappedCourse = MapCourse(course, mappedCourseRuns, larsSearchResults, courseId, errors);
var added = await UpsertCourse(mappedCourse, cosmosDbClient);
result = added ? CourseMigrationResult.Inserted : CourseMigrationResult.Updated;
}
else
{
result = CourseMigrationResult.SkippedDueToErrors;
}
}
catch (Exception ex)
{
errors.Add(ex.ToString().Replace("\n", " "));
result = CourseMigrationResult.Exception;
}
// Write to log
logCsvWriter.WriteField(course.CourseId);
logCsvWriter.WriteField(course.UKPRN);
logCsvWriter.WriteField(result == CourseMigrationResult.Inserted || result == CourseMigrationResult.Updated);
logCsvWriter.WriteField(result.ToString());
logCsvWriter.WriteField(instances.Count);
logCsvWriter.WriteField(string.Join(", ", errors));
logCsvWriter.NextRecord();
}
}
}
// Upload log CSV to blob storage
{
logStreamWriter.Flush();
logStream.Seek(0L, SeekOrigin.Begin);
var blob = blobHelper.GetBlobContainer(blobContainer).GetBlockBlobReference(logFileName);
await blob.UploadFromStreamAsync(logStream);
}
}
async Task <ISet <int> > GetProviderWhiteList()
{
var blob = blobHelper.GetBlobContainer(blobContainer).GetBlockBlobReference(whitelistFileName);
var ms = new MemoryStream();
await blob.DownloadToStreamAsync(ms);
ms.Seek(0L, SeekOrigin.Begin);
var results = new HashSet <int>();
string line;
using (var reader = new StreamReader(ms))
{
while ((line = reader.ReadLine()) != null)
{
if (string.IsNullOrEmpty(line))
{
continue;
}
var ukprn = int.Parse(line);
results.Add(ukprn);
}
}
return(results);
}
async Task <IReadOnlyCollection <LarsSearchResultItem> > QueryLars(string learningAimRef)
{
var result = await larsSearchService.SearchAsync(new LarsSearchCriteria(learningAimRef, top : 1, skip : 0));
if (result.IsFailure)
{
throw new Exception($"LARS search failed:\n{result.Error}");
}
return(result.Value.Value.ToList());
}
async Task <bool> UpsertCourse(Course course, IDocumentClient documentClient)
{
var collectionLink = UriFactory.CreateDocumentCollectionUri(databaseId, coursesCollectionId);
var result = await documentClient.UpsertDocumentAsync(collectionLink, course, new RequestOptions()
{
PartitionKey = new Microsoft.Azure.Documents.PartitionKey(course.ProviderUKPRN)
});
return(result.StatusCode == HttpStatusCode.Created);
}
async Task <Course> GetExistingCourse(int courseId, int ukprn, IDocumentClient documentClient)
{
var collectionLink = UriFactory.CreateDocumentCollectionUri(databaseId, coursesCollectionId);
var query = documentClient
.CreateDocumentQuery <Course>(collectionLink, new FeedOptions()
{
PartitionKey = new Microsoft.Azure.Documents.PartitionKey(ukprn)
})
.Where(d => d.CourseId == courseId)
.AsDocumentQuery();
return((await query.ExecuteNextAsync()).FirstOrDefault());
}
AttendancePattern MapAttendancePattern(DeliveryMode deliveryMode, int?attendancePatternId, out bool hasError)
{
if (deliveryMode != DeliveryMode.ClassroomBased)
{
hasError = false;
return(AttendancePattern.Undefined);
}
if (!attendancePatternId.HasValue)
{
hasError = true;
return(AttendancePattern.Undefined);
}
switch (attendancePatternId.Value)
{
case 1:
hasError = false;
return(AttendancePattern.Daytime);
case 2:
hasError = false;
return(AttendancePattern.DayOrBlockRelease);
case 3:
case 4:
hasError = false;
return(AttendancePattern.Evening);
case 5:
hasError = false;
return(AttendancePattern.Weekend);
case 6:
case 7:
case 8:
default:
hasError = true;
return(AttendancePattern.Undefined);
}
}
DeliveryMode MapDeliveryMode(int?attendanceTypeId, out bool hasError)
{
if (!attendanceTypeId.HasValue)
{
hasError = true;
return(DeliveryMode.Undefined);
}
switch (attendanceTypeId.Value)
{
case 1:
hasError = false;
return(DeliveryMode.ClassroomBased);
case 2:
case 3:
hasError = false;
return(DeliveryMode.WorkBased);
case 7:
case 8:
hasError = false;
return(DeliveryMode.Online);
case 4:
case 5:
case 6:
case 9:
default:
hasError = true;
return(DeliveryMode.Undefined);
}
}
StudyMode MapStudyMode(DeliveryMode deliveryMode, int?studyModeId, out bool hasError)
{
if (deliveryMode != DeliveryMode.ClassroomBased)
{
hasError = false;
return(StudyMode.Undefined);
}
if (!studyModeId.HasValue)
{
hasError = true;
return(StudyMode.Undefined);
}
switch (studyModeId.Value)
{
case 1:
hasError = false;
return(StudyMode.FullTime);
case 2:
hasError = false;
return(StudyMode.PartTime);
case 3:
hasError = true;
return(StudyMode.Undefined);
case 4:
hasError = false;
return(StudyMode.Flexible);
default:
hasError = true;
return(StudyMode.Undefined);
}
}
(DurationUnit, int?) MapDuration(int?durationUnit, int?durationValue, out bool hasError)
{
if (!durationUnit.HasValue)
{
hasError = true;
return(DurationUnit.Undefined, null);
}
switch (durationUnit.Value)
{
case 1:
hasError = false;
return(DurationUnit.Hours, durationValue);
case 2:
hasError = false;
return(DurationUnit.Days, durationValue);
case 3:
hasError = false;
return(DurationUnit.Weeks, durationValue);
case 4:
hasError = false;
return(DurationUnit.Months, durationValue);
case 5:
hasError = false;
return(DurationUnit.Months, 3);
case 7:
hasError = false;
return(DurationUnit.Years, durationValue);
case 6:
default:
hasError = true;
return(DurationUnit.Undefined, null);
}
}
CourseRun MapCourseInstance(
CourseResult course,
CourseInstanceResult courseInstance,
Guid id,
Guid?venueId,
List <string> errors)
{
var deliveryMode = MapDeliveryMode(courseInstance.AttendanceTypeId, out var deliveryModeError);
var attendancePattern = MapAttendancePattern(deliveryMode, courseInstance.AttendancePatternId, out var attendancePatternError);
var studyMode = MapStudyMode(deliveryMode, courseInstance.StudyModeId, out var studyModeError);
var(durationUnit, durationValue) = MapDuration(courseInstance.DurationUnitId, courseInstance.DurationUnit, out var durationError);
var hasErrors = false;
if (attendancePatternError)
{
errors.Add($"Invalid AttendancePattern");
hasErrors = true;
}
if (deliveryModeError)
{
errors.Add($"Invalid DeliveryMode");
hasErrors = true;
}
if (studyModeError)
{
errors.Add($"Invalid StudyMode");
hasErrors = true;
}
if (durationError)
{
errors.Add($"Invalid Duration");
hasErrors = true;
}
bool flexibleStartDate = default;
DateTime?startDate = default;
if (deliveryMode == DeliveryMode.Online)
{
flexibleStartDate = true;
}
else if (courseInstance.StartDate.HasValue)
{
flexibleStartDate = false;
startDate = courseInstance.StartDate;
}
else if (string.IsNullOrEmpty(courseInstance.StartDateDescription))
{
errors.Add($"Empty StartDateDescription");
hasErrors = true;
}
else if (DateTime.TryParseExact(courseInstance.StartDateDescription, "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out var sd))
{
flexibleStartDate = false;
startDate = sd;
}
else
{
flexibleStartDate = true;
}
if (deliveryMode == DeliveryMode.ClassroomBased && !venueId.HasValue)
{
errors.Add($"No venue");
hasErrors = true;
}
// Work-based should have regions(s) or be national
bool?national = null;
IEnumerable <string> regions = Array.Empty <string>();
IEnumerable <SubRegionItemModel> subRegions = Array.Empty <SubRegionItemModel>();
if (deliveryMode == DeliveryMode.WorkBased)
{
if (!courseInstance.VenueLocationId.HasValue)
{
errors.Add("No region found");
hasErrors = true;
}
else
{
if (RegionLookup.IsNational(courseInstance.VenueLocationId.Value))
{
national = true;
}
else
{
var lookupResult = RegionLookup.FindRegions(courseInstance.VenueLocationId.Value);
if (!lookupResult.HasValue)
{
errors.Add($"Cannot find sub-region(s) for VenueLocationId {courseInstance.VenueLocationId.Value}");
hasErrors = true;
}
else
{
regions = lookupResult.Value.regionIds;
subRegions = lookupResult.Value.subRegions;
national = false;
}
}
}
}
var recordStatus = hasErrors ? RecordStatus.MigrationPending : RecordStatus.Live;
return(new CourseRun()
{
AttendancePattern = attendancePattern,
Cost = courseInstance.Price,
CostDescription = courseInstance.PriceAsText,
CourseInstanceId = courseInstance.CourseInstanceId,
CourseName = course.CourseTitle,
CourseURL = courseInstance.Url,
CreatedBy = "CourseMigrator",
CreatedDate = DateTime.Now,
DeliveryMode = deliveryMode,
DurationUnit = durationUnit,
DurationValue = durationValue,
FlexibleStartDate = flexibleStartDate,
id = id,
ProviderCourseID = courseInstance.ProviderOwnCourseInstanceRef,
RecordStatus = recordStatus,
National = national,
Regions = regions,
StartDate = startDate,
StudyMode = studyMode,
SubRegions = subRegions,
//UpdatedBy
UpdatedDate = DateTime.Now,
VenueId = venueId
});
}
Course MapCourse(
CourseResult course,
IReadOnlyCollection <CourseRun> courseRuns,
IReadOnlyCollection <LarsSearchResultItem> larsSearchResults,
Guid id,
List <string> errors)
{
var isValid = courseRuns.All(r => r.RecordStatus.HasFlag(RecordStatus.Live));
LarlessReason?larlessReason = string.IsNullOrEmpty(course.LearningAimRefId) ?
LarlessReason.NoLars : larsSearchResults.Count == 0 ?
LarlessReason.UnknownLars : larsSearchResults.Count > 1 ?
LarlessReason.MultipleMatchingLars : // TODO Consider expired LARS
LarlessReason.Undefined;
var qualification = larsSearchResults.Count == 1 ? larsSearchResults.Single() : null;
if (qualification == null)
{
foreach (var cr in courseRuns)
{
cr.RecordStatus = RecordStatus.MigrationPending;
}
errors.Add("LARS lookup failed");
isValid = false;
}
return(new Course()
{
AdultEducationBudget = default,
public static async Task Run(
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)
{
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 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);
}
}