示例#1
0
        public void Creating_And_Assigning_Values_To_Provider()
        {
            Contactaddress         address = new Contactaddress();
            Contactpersonaldetails details = new Contactpersonaldetails();

            Provideralias[]      alias     = new Provideralias[1];
            Verificationdetail[] verDetail = new Verificationdetail[1];
            Providercontact[]    contact   = new Providercontact[1];
            Provider             prov      = new Provider(contact, alias, verDetail);

            // Assert.NotNull(prov);
        }
示例#2
0
        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);
            }
        }