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); } }