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,
[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
ILogger log,
[Inject] IConfigurationRoot configuration,
[Inject] IApprenticeshipCollectionService apprenticeshipCollectionService,
[Inject] ICosmosDbHelper cosmosDbHelper,
[Inject] IBlobStorageHelper blobhelper,
[Inject] IUkrlpApiService ukrlpApiService
)
{
const string apprenticeshipAppName = "Validate.Apprenticeship";
var apprenticeshipValidationFileName = $"{apprenticeshipAppName}-{DateTime.Now.ToString("dd-MM-yy HHmm")}";
var blobContainer = blobhelper.GetBlobContainer(configuration["BlobStorageSettings:Container"]);
var validator = new ApprenticeshipValidator();
List <ApprenticeshipValidationResult> validationEResult = new List <ApprenticeshipValidationResult>();
var stopWatch = new Stopwatch();
stopWatch.Start();
var resultData = await apprenticeshipCollectionService.GetAllApprenticeshipsAsync();
foreach (var item in resultData)
{
//item.ContactEmail = "testing";
//item.Url = "testing";
//item.ContactWebsite = "testing";
//item.ContactTelephone = "101101abc";
//item.ApprenticeshipTitle = item.ApprenticeshipTitle + " @";
var validationResult = validator.Validate(item);
if (!validationResult.IsValid)
{
foreach (var error in validationResult.Errors)
{
validationEResult.Add(new ApprenticeshipValidationResult()
{
ApprenticeshipId = item.ApprenticeshipId,
ProviderUkprn = item.ProviderUKPRN,
FieldName = error.PropertyName,
ErrorMessage = error.ErrorMessage
});
}
}
}
var resultsObjBytes = GetResultAsByteArray(validationEResult);
await WriteResultsToBlobStorage(resultsObjBytes);
stopWatch.Stop();
byte[] GetResultAsByteArray(IList <ApprenticeshipValidationResult> message)
{
using (var memoryStream = new System.IO.MemoryStream())
{
using (var streamWriter = new System.IO.StreamWriter(memoryStream))
using (var csvWriter = new CsvWriter(streamWriter, CultureInfo.InvariantCulture))
{
csvWriter.WriteRecords <ApprenticeshipValidationResult>(message);
}
return(memoryStream.ToArray());
}
}
async Task WriteResultsToBlobStorage(byte[] data)
{
await blobhelper.UploadFile(blobContainer, apprenticeshipValidationFileName, data);
}
}
