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 AddMigrationReport(CourseMigrationReport courseReport)
{
Throw.IfNull(courseReport, nameof(courseReport));
try
{
using (var client = _cosmosDbHelper.GetClient())
{
var result = await _cosmosDbHelper.GetDocumentsByUKPRN<CourseMigrationReport>(client, _settings.CoursesMigrationReportCollectionId,
courseReport.ProviderUKPRN);
if (result.Any())
{
courseReport.Id = result.FirstOrDefault().Id;
await _cosmosDbHelper.UpdateDocumentAsync(client, _settings.CoursesMigrationReportCollectionId,
courseReport);
}
else
{
var doc = await _cosmosDbHelper.CreateDocumentAsync(client, _settings.CoursesMigrationReportCollectionId, courseReport);
}
}
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
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 async Task <ICourse> AddCourse(ICourse course)
{
Throw.IfNull(course, nameof(course));
Course persisted;
using (var client = _cosmosDbHelper.GetClient())
{
await _cosmosDbHelper.CreateDatabaseIfNotExistsAsync(client);
await _cosmosDbHelper.CreateDocumentCollectionIfNotExistsAsync(client, _settings.CoursesCollectionId);
var doc = await _cosmosDbHelper.CreateDocumentAsync(client, _settings.CoursesCollectionId, course);
persisted = _cosmosDbHelper.DocumentTo <Course>(doc);
}
return(persisted);
}
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 static async Task Run(
string input, // Work around https://github.com/Azure/azure-functions-vs-build-sdk/issues/168
ILogger log,
[Inject] IConfigurationRoot configuration,
[Inject] IVenueCollectionService venueCollectionService,
[Inject] IProviderCollectionService providerCollectionService,
[Inject] ICosmosDbHelper cosmosDbHelper,
[Inject] IBlobStorageHelper blobhelper
)
{
var venuesCollectionId = configuration["CosmosDbCollectionSettings:VenuesCollectionId"];
var connectionString = configuration.GetConnectionString("TribalRestore");
var blobContainer = blobhelper.GetBlobContainer(configuration["BlobStorageSettings:Container"]);
var whiteListProviders = await GetProviderWhiteList();
var result = new List <ResultMessage>();
var venueList = new List <Venue>();
var venueExportFileName = $"VenueExport-{DateTime.Now.ToString("dd-MM-yy HHmm")}";
const string WHITE_LIST_FILE = "ProviderWhiteList.txt";
var ukprnCache = new List <int>();
var databaseId = configuration["CosmosDbSettings:DatabaseId"];
//update or insert records
var _cosmosClient = cosmosDbHelper.GetClient();
using (var sqlConnection = new SqlConnection(connectionString))
{
using (var command = sqlConnection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = @"
DECLARE @Venues TABLE
(
VenueId INT NOT NULL,
ProviderId INT NOT NULL,
ProviderOwnVenueRef NVARCHAR(255) NULL,
VenueName NVARCHAR(255) NOT NULL,
Email NVARCHAR(255) NULL,
Website NVARCHAR(255) NULL,
Fax NVARCHAR(35) NULL,
Facilities NVARCHAR(2000),
RecordStatusId INT NOT NULL,
CreatedByUserId NVARCHAR(128) NOT NULL,
CreatedDateTimeUtc DATETIME NOT NULL,
ModifiedByUserId NVARCHAR(128) NULL,
ModifiedDateTimeUtc DATETIME NULL,
AddressId INT,
Telephone NVARCHAR(30) NULL,
BulkUploadVenueId NVARCHAR(255) NULL,
UKPRN INT NOT NULL,
AddressLine1 NVARCHAR(110) NULL,
AddressLine2 NVARCHAR(100) NULL,
County NVARCHAR(75) NULL,
Latitude Decimal(9,6) NULL,
Longitude Decimal(9,6) NULL,
Postcode NVARCHAR(30) NULL,
Town NVARCHAR(75) NULL,
source INT NOT NULL,
LocationID INT NULL
)
INSERT INTO @Venues
(
VenueId,
ProviderId,
ProviderOwnVenueRef,
VenueName,
Email,
Website,
Fax,
Facilities,
RecordStatusId,
CreatedByUserId,
CreatedDateTimeUtc,
ModifiedByUserId,
ModifiedDateTimeUtc,
AddressId,
Telephone,
BulkUploadVenueId,
UKPRN,
AddressLine1,
AddressLine2,
County,
Latitude,
Longitude,
Postcode,
Town,
source,
LocationID
)
SELECT distinct Ven.[VenueId],
Ven.[ProviderId],
Ven.[ProviderOwnVenueRef],
Ven.[VenueName],
Ven.[Email],
Ven.[Website],
Ven.[Fax],
Ven.[Facilities],
Ven.[RecordStatusId],
Ven.[CreatedByUserId],
Ven.[CreatedDateTimeUtc],
Ven.[ModifiedByUserId],
Ven.[ModifiedDateTimeUtc],
Ven.[AddressId],
Ven.[Telephone],
Ven.[BulkUploadVenueId],
pr.Ukprn,
Ad.AddressLine1,
ad.AddressLine2,
ad.County,
ad.[Latitude],
ad.[Longitude],
ad.Postcode,
ad.Town,
1 as [Source],
NULL as LocationId
FROM Venue Ven
INNER JOIN [Address] Ad on Ad.AddressId = Ven.AddressId
INNER JOIN [Provider] pr on pr.ProviderId = ven.ProviderId
WHERE Ven.RecordStatusID = 2
UNION ALL
SELECT DISTINCT 0,
L.[ProviderId],
l.ProviderOwnLocationRef,
L.[LocationName],
L.[Email],
L.[Website],
NULL,
NULL,
L.[RecordStatusId],
L.[CreatedByUserId],
L.[CreatedDateTimeUtc],
L.[ModifiedByUserId],
L.[ModifiedDateTimeUtc],
L.[AddressId],
L.[Telephone],
NULL,
pr.Ukprn,
Ad.AddressLine1,
ad.AddressLine2,
ad.County,
ad.[Latitude],
ad.[Longitude],
ad.Postcode,
ad.Town,
2 as [Source],
L.LocationId as LocationId
FROM Location l
INNER JOIN Address ad on ad.AddressId = l.AddressId
INNER JOIN Provider pr on pr.ProviderId = l.ProviderId
WHERE l.RecordStatusId = 2
SELECT * FROM @Venues
";
try
{
//Open connection.
sqlConnection.Open();
using (SqlDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
//Read venue
venueList.Add(Venue.FromDataReader(dataReader));
}
// Close the SqlDataReader.
dataReader.Close();
}
sqlConnection.Close();
}
catch (Exception ex)
{
log.LogError("An error occured migratiing Venues", ex);
}
}
}
foreach (var item in venueList)
{
try
{
if (Validate(item))
{
var cosmosVenue = await GetVenue(item.Source, item.VenueId, item.LocationID, item.UKPRN);
if (cosmosVenue != null)
{
//var s = UriFactory.CreateDocumentUri(databaseId, venuesCollectionId, cosmosVenue.ID.ToString());
if (cosmosVenue.UKPRN != item.UKPRN)
{
continue;
}
Uri collectionUri = UriFactory.CreateDocumentCollectionUri(databaseId, venuesCollectionId);
var editedVenue = new Dfc.CourseDirectory.Models.Models.Venues.Venue()
{
ID = cosmosVenue.ID,
UKPRN = item.UKPRN,
VenueName = item.VenueName,
Address1 = item.Address.Address1,
Address2 = item.Address.Address2,
Town = item.Address.Town,
County = item.Address.County,
PostCode = item.Address.Postcode,
Latitude = item.Address.Latitude,
Longitude = item.Address.Longitude,
Status = MapVenueStatus(item),
UpdatedBy = "VenueMigrator",
DateUpdated = DateTime.Now,
VenueID = item.VenueId,
ProviderID = item.ProviderId,
ProvVenueID = item.ProviderOwnVenueRef,
Email = item.Email,
Website = item.Website,
Telephone = item.Telephone,
CreatedBy = "VenueMigrator",
CreatedDate = DateTime.Now,
LocationId = item.LocationID,
TribalLocationId = item.LocationID
};
await _cosmosClient.UpsertDocumentAsync(collectionUri, editedVenue);
AddResultMessage(item.UKPRN, item.VenueId, item.LocationID, "Updated Record", $"Old cosmos record LocationId:{cosmosVenue.LocationId}, VenueId: {cosmosVenue.VenueID}");
}
else
{
var newVenue = new Dfc.CourseDirectory.Models.Models.Venues.Venue()
{
UKPRN = item.UKPRN,
VenueName = item.VenueName,
Address1 = item.Address.Address1,
Address2 = item.Address.Address2,
Town = item.Address.Town,
County = item.Address.County,
PostCode = item.Address.Postcode,
Latitude = item.Address.Latitude,
Longitude = item.Address.Longitude,
Status = MapVenueStatus(item),
UpdatedBy = item.CreatedByUserId,
DateUpdated = item.CreatedDateTimeUtc,
VenueID = item.VenueId,
ProviderID = item.ProviderId,
ProvVenueID = item.ProviderOwnVenueRef,
Email = item.Email,
Website = item.Website,
Telephone = item.Telephone,
CreatedDate = DateTime.Now,
CreatedBy = "VenueMigrator",
LocationId = item.LocationID,
TribalLocationId = item.LocationID
};
await cosmosDbHelper.CreateDocumentAsync(_cosmosClient, venuesCollectionId, newVenue);
//Log that successfully inserted venue
AddResultMessage(item.UKPRN, item.VenueId, item.LocationID, "Inserted Venue");
}
}
}
catch (Exception ex)
{
string errorMessage = $"An error occured while updating cosmos record for venue {item.VenueId}. {ex.Message}";
log.LogError(errorMessage, ex);
AddResultMessage(item.UKPRN, item.VenueId, item.LocationID, errorMessage);
}
}
var resultsObjBytes = GetResultAsByteArray(result);
await WriteResultsToBlobStorage(resultsObjBytes);
//log completion
log.LogInformation("Migrating Venues Complete");
async Task <Dfc.CourseDirectory.Models.Models.Venues.Venue> GetVenue(VenueSource source, int?venueId, int?locationId, int ukprn)
{
switch (source)
{
case VenueSource.Venue:
return(await venueCollectionService.GetDocumentByVenueId(venueId.Value));
case VenueSource.Location:
return(await venueCollectionService.GetDocumentByLocationId(locationId.Value, ukprn));
default: return(null);
}
}
CourseDirectory.Models.Models.Venues.VenueStatus MapVenueStatus(Venue venue)
{
//ignore record status for venues that do not have a postcode & migrate it over
//as pending.
if (string.IsNullOrEmpty(venue.Address?.Postcode))
{
return(CourseDirectory.Models.Models.Venues.VenueStatus.Pending);
}
switch (venue.RecordStatusId)
{
case TribalRecordStatus.Pending: return(CourseDirectory.Models.Models.Venues.VenueStatus.Pending);
case TribalRecordStatus.Live: return(CourseDirectory.Models.Models.Venues.VenueStatus.Live);
case TribalRecordStatus.Archived: return(CourseDirectory.Models.Models.Venues.VenueStatus.Archived);
case TribalRecordStatus.Deleted: return(CourseDirectory.Models.Models.Venues.VenueStatus.Deleted);
default: throw new Exception("$Unable to map recordStatus to VenueStatus");
}
}
byte[] GetResultAsByteArray(IList <ResultMessage> ob)
{
using (var memoryStream = new System.IO.MemoryStream())
{
using (var streamWriter = new System.IO.StreamWriter(memoryStream))
using (var csvWriter = new CsvWriter(streamWriter, CultureInfo.InvariantCulture))
{
csvWriter.WriteRecords <ResultMessage>(ob);
}
return(memoryStream.ToArray());
}
}
async Task WriteResultsToBlobStorage(byte[] data)
{
await blobhelper.UploadFile(blobContainer, venueExportFileName, data);
}
async Task <IList <int> > GetProviderWhiteList()
{
var list = new List <int>();
var whiteList = await blobhelper.ReadFileAsync(blobContainer, WHITE_LIST_FILE);
if (!string.IsNullOrEmpty(whiteList))
{
var lines = whiteList.Split(new[] { Environment.NewLine }, StringSplitOptions.None);
foreach (string line in lines)
{
if (int.TryParse(line, out int id))
{
list.Add(id);
}
}
}
return(list);
}
void AddResultMessage(int ukprn, int venueId, int?locationId, string status, string message = "")
{
var validateResult = new ResultMessage()
{
UKPRN = ukprn, VenueId = venueId, LocationId = locationId, Status = status, Message = message
};
result.Add(validateResult);
}
bool Validate(Venue item)
{
//are providers on list of whitelisted providers file
if (!whiteListProviders.Any(x => x == item.UKPRN))
{
AddResultMessage(item.UKPRN, item.VenueId, item.LocationID, "Failed", $"Provider {item.ProviderId} not on whitelist, ukprn {item.UKPRN}");
return(false);
}
if (!item.Address.Latitude.HasValue || !item.Address.Longitude.HasValue)
{
AddResultMessage(item.UKPRN, item.VenueId, item.LocationID, "Skiped", $"Skipped Location because Lat/Long are missing, {item.ProviderId} not on whitelist, ukprn {item.UKPRN}");
return(false);
}
////check to see if a record is already held for ukprn
//if (!ukprnCache.Contains(item.UKPRN))
//{
// var cosmosProvider = await providerCollectionService.ProviderExists(item.UKPRN);
// if (!cosmosProvider)
// {
// AddResultMessage(item.VenueId, item.LocationID, "Failed", "Unknown UKPRN");
// return false;
// }
// else
// {
// //provider exists - add to cache
// ukprnCache.Add(item.UKPRN);
// }
//}
return(true);
}
}
public static async Task Run(
string input, // Work around https://github.com/Azure/azure-functions-vs-build-sdk/issues/168
ILogger log,
[Inject] IConfigurationRoot configuration,
[Inject] 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
ILogger log,
[Inject] IConfigurationRoot configuration,
[Inject] IFEChoicesDataCollectionService feChoicesDataCollectionService,
[Inject] ICosmosDbHelper cosmosDbHelper,
[Inject] IBlobStorageHelper blobhelper
)
{
const string AppName = "FEChoicesData.Migrator";
var stopWatch = new Stopwatch();
var databaseId = configuration["CosmosDbSettings:DatabaseId"];
var feChoicesCollectionId = configuration["CosmosDbCollectionSettings:FEChoicesDataCollectionId"];
var connectionString = configuration.GetConnectionString("TribalRestore");
var fechoicesDataMigrationLogFile = $"FEChoicesDataMigrator-{DateTime.Now.ToString("dd-MM-yy HHmm")}";
List <Guid> feDataRecordsToDeleteByGuid = new List <Guid>();
List <int> feDataRecordsToDeleteByUkprn = new List <int>();
var blobContainer = blobhelper.GetBlobContainer(configuration["BlobStorageSettings:Container"]);
var _cosmosClient = cosmosDbHelper.GetClient();
// Get all changed data from UKRLP API
stopWatch.Reset();
log.LogInformation($"FEChoicesData Migrator: Start synching data..");
var result = new List <FeChoicesDataResultMessage>();
using (var sqlConnection = new SqlConnection(connectionString))
{
using (var command = sqlConnection.CreateCommand())
{
// Get non duplicate UKPRN data only
command.CommandType = CommandType.Text;
command.CommandText = @"SELECT P.Ukprn,
P.RecordStatusId,
D.UPIN,
D.LearnerSatisfaction,
D.LearnerDestination,
D.EmployerSatisfaction,
D.CreatedDateTimeUtc,
Count(p.UKprn)
FROM [Provider] P
JOIN [FEChoices] D
ON P.UPIN = D.UPIN
WHERE p.RecordStatusId = 2
GROUP BY
P.Ukprn,
P.RecordStatusId,
D.UPIN,
D.LearnerSatisfaction,
D.LearnerDestination,
D.EmployerSatisfaction,
D.CreatedDateTimeUtc
HAVING Count(P.Ukprn) = 1
ORDER BY D.CreatedDateTimeUtc DESC, p.Ukprn
";
try
{
//Open connection.
sqlConnection.Open();
stopWatch.Reset();
log.LogInformation($"Tribal Data: Start....");
stopWatch.Start();
List <FEChoicesSourceData> sourceData = new List <FEChoicesSourceData>();
using (SqlDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
// Read FE data from Tribal
var item = FEChoicesSourceData.FromDataReader(dataReader);
sourceData.Add(item);
}
dataReader.Close();
}
var destinationData = await feChoicesDataCollectionService.GetAllDocument();
var uniqueSourceUkprns = sourceData.Select(s => s.UKPRN).Distinct().ToList();
foreach (var ukprn in uniqueSourceUkprns)
{
// filter out duplicate form source
if (sourceData.Count(s => s.UKPRN == ukprn) > 1)
{
// mark for removal from destination if exists
feDataRecordsToDeleteByUkprn.Add(ukprn);
continue;
}
// pick the first as there might still be duplicates
var sourceItem = sourceData.First(s => s.UKPRN == ukprn);
try
{
// Check if item exists in both (could be duplicate in destination)
var itemsToUpdate = destinationData.Where(s => s.UKPRN == sourceItem.UKPRN);
// Update
if (itemsToUpdate != null && itemsToUpdate.Any())
{
var itemToUpdate = itemsToUpdate.First();
itemToUpdate.EmployerSatisfaction = sourceItem.EmployerSatisfaction;
itemToUpdate.LearnerSatisfaction = sourceItem.LearnerSatisfaction;
itemToUpdate.CreatedDateTimeUtc = sourceItem.CreatedDateTimeUtc;
itemToUpdate.LastUpdatedBy = AppName;
itemToUpdate.LastUpdatedOn = DateTime.UtcNow;
Uri collectionUri = UriFactory.CreateDocumentCollectionUri(databaseId, feChoicesCollectionId);
await _cosmosClient.UpsertDocumentAsync(collectionUri, itemToUpdate);
feDataRecordsToDeleteByGuid.AddRange(itemsToUpdate.Where(i => i.id != itemToUpdate.id).Select(i => i.id).ToList());
AddResultMessage(sourceItem.UKPRN, "PROCESSED-Updated", $"Provider {sourceItem.UKPRN} updated in Cosmos Collection");
}
// Insert new entry
else
{
var newRecord = new FEChoicesData()
{
id = Guid.NewGuid(),
UKPRN = sourceItem.UKPRN,
LearnerSatisfaction = sourceItem.LearnerSatisfaction,
EmployerSatisfaction = sourceItem.EmployerSatisfaction,
CreatedDateTimeUtc = sourceItem.CreatedDateTimeUtc,
CreatedBy = AppName,
CreatedOn = DateTime.UtcNow,
LastUpdatedBy = AppName,
LastUpdatedOn = DateTime.UtcNow,
};
await cosmosDbHelper.CreateDocumentAsync(_cosmosClient, feChoicesCollectionId, newRecord);
AddResultMessage(sourceItem.UKPRN, "PROCESSED-Created", $"Provider {sourceItem.UKPRN} updated in Cosmos Collection");
}
}
catch (Exception ex)
{
AddResultMessage(sourceItem.UKPRN, "ERRORED", $"Error while inserting/updating for provider {ex.Message}");
log.LogError($"Tribal Data: Error processing data.", ex);
}
}
// Remove data that is not in source
var howManyToDelete = destinationData.Where(d => !sourceData.Select(s => s.UKPRN).Contains(d.UKPRN));
foreach (var existingItem in howManyToDelete)
{
try
{
Uri docUri = UriFactory.CreateDocumentUri(databaseId, feChoicesCollectionId, existingItem.id.ToString());
var deleteResult = await _cosmosClient.DeleteDocumentAsync(docUri, new RequestOptions()
{
PartitionKey = new PartitionKey(existingItem.UKPRN)
});
AddResultMessage(existingItem.UKPRN, "DELETE", $"Record {existingItem.id} with UKPRN {existingItem.UKPRN} deleted as not in source.");
}
catch (Exception)
{
AddResultMessage(existingItem.UKPRN, "ERROR", $"Error deleting in destination record {existingItem.id} with UKPRN {existingItem.UKPRN}.");
}
}
// Remove data that is duplicate in destination using Id
var duplicatesToDeleteByGuid = destinationData.Where(d => feDataRecordsToDeleteByGuid.Contains(d.id));
foreach (var existingItem in duplicatesToDeleteByGuid)
{
try
{
Uri docUri = UriFactory.CreateDocumentUri(databaseId, feChoicesCollectionId, existingItem.id.ToString());
var deleteResult = await _cosmosClient.DeleteDocumentAsync(docUri, new RequestOptions()
{
PartitionKey = new PartitionKey(existingItem.UKPRN)
});
AddResultMessage(existingItem.UKPRN, "DELETE", $"Record {existingItem.id} with UKPRN {existingItem.UKPRN} deleted as duplicate in Cosmos.");
}
catch (Exception)
{
AddResultMessage(existingItem.UKPRN, "ERROR", $"Error deleting in destination record {existingItem.id} with UKPRN {existingItem.UKPRN}.");
}
}
// Remove data that is duplicate in source so needs to be removed from destination using UKPRN
var duplicatesToDeleteByUkprn = destinationData.Where(d => feDataRecordsToDeleteByUkprn.Contains(d.UKPRN));
foreach (var existingItem in duplicatesToDeleteByUkprn)
{
try
{
Uri docUri = UriFactory.CreateDocumentUri(databaseId, feChoicesCollectionId, existingItem.id.ToString());
var deleteResult = await _cosmosClient.DeleteDocumentAsync(docUri, new RequestOptions()
{
PartitionKey = new PartitionKey(existingItem.UKPRN)
});
AddResultMessage(existingItem.UKPRN, "DELETE", $"Record {existingItem.id} with UKPRN {existingItem.UKPRN} deleted as duplicate in Cosmos.");
}
catch (Exception)
{
AddResultMessage(existingItem.UKPRN, "ERROR", $"Error deleting in destination record {existingItem.id} with UKPRN {existingItem.UKPRN}.");
}
}
}
catch (Exception ex)
{
AddResultMessage(-1, "ERRORED-Unknown", $"{ex.Message}");
log.LogError($"Tribal Data: Error processing data.", ex);
}
stopWatch.Stop();
log.LogInformation($"Tribal Data: Processing completed in {stopWatch.ElapsedMilliseconds / 1000}");
var resultsObjBytes = GetResultAsByteArray(result);
await WriteResultsToBlobStorage(resultsObjBytes);
}
}
void AddResultMessage(int providerId, string status, string message = "")
{
var validateResult = new FeChoicesDataResultMessage()
{
ProviderId = providerId, Status = status, Message = message
};
result.Add(validateResult);
}
byte[] GetResultAsByteArray(IList <FeChoicesDataResultMessage> message)
{
using (var memoryStream = new System.IO.MemoryStream())
{
using (var streamWriter = new System.IO.StreamWriter(memoryStream))
using (var csvWriter = new CsvWriter(streamWriter, CultureInfo.InvariantCulture))
{
csvWriter.WriteRecords <FeChoicesDataResultMessage>(message);
}
return(memoryStream.ToArray());
}
}
async Task WriteResultsToBlobStorage(byte[] data)
{
await blobhelper.UploadFile(blobContainer, fechoicesDataMigrationLogFile, data);
}
}