public static ApprenticeshipResult FromDataReader(SqlDataReader reader) { var result = new ApprenticeshipResult() { ApprenticeshipID = (int)reader["ApprenticeshipID"], FrameworkCode = reader["Frameworkcode"] as int?, UKPRN = (int)reader["UKPRN"], PathWayCode = reader["PathWayCode"] as int?, ProgType = reader["ProgType"] as int?, Version = reader["Version"] as int?, StandardCode = reader["StandardCode"] as int?, ProviderId = (int)reader["ProviderId"], MarketingInformation = reader["MarketingInformation"] as string, Url = reader["URL"] as string, ContactEmail = reader["ContactEmail"] as string, ContactTelephone = reader["ContactTelephone"] as string, ContactWebsite = reader["ContactWebsite"] as string, RecordStatusId = (int)reader["RecordStatusId"], CreatedByUserId = reader["CreatedByUserId"] as string, CreatedDateTimeUtc = (DateTime)reader["CreatedDateTimeUtc"], ApprenticeshipTitle = reader["ApprenticeshipTitle"] as string, NotionalEndLevel = reader["NotionalEndLevel"] as string }; return(result); }
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] IApprenticeReferenceDataService apprenticeReferenceDataService, [Inject] IApprenticeshipServiceWrapper apprenticeshipService, [Inject] IVenueCollectionService venueCollectionService, [Inject] IOnspdService onspdService ) { var apprenticeshipCollectionId = configuration["CosmosDbCollectionSettings:ApprenticeshipCollectionId"]; var connectionString = configuration.GetConnectionString("TribalRestore"); var blobContainer = blobhelper.GetBlobContainer(configuration["BlobStorageSettings:Container"]); var whiteListProviders = await GetProviderWhiteList(); var result = new List <ApprenticeshipResultMessage>(); var venueExportFileName = $"ApprenticeshipExport-{DateTime.Now.ToString("dd-MM-yy HHmm")}"; const string WHITE_LIST_FILE = "ProviderWhiteList-Apprenticeships.txt"; var ukprnCache = new List <int>(); var databaseId = configuration["CosmosDbSettings:DatabaseId"]; var apprenticeshipList = new List <ApprenticeshipResult>(); var createdBy = "ApprenticeshipMigrator"; var createdDate = DateTime.Now; SemaphoreSlim semaphore = new SemaphoreSlim(5); var client = cosmosDbHelper.GetClient(); var apprenticeshipSQL = @"SELECT a.ApprenticeshipId, p.ProviderId, a.FrameworkCode, a.ProgType, a.PathwayCode, a.StandardCode, a.[Version], a.MarketingInformation, a.[Url], a.ContactEmail, a.ContactTelephone, a.ContactWebsite, a.RecordStatusId, a.CreatedByUserId, a.CreatedDateTimeUtc, p.Ukprn, coalesce(s.StandardName,f.NasTitle) as [ApprenticeshipTitle], s.NotionalEndLevel FROM Apprenticeship a INNER JOIN Provider p on p.ProviderId = a.ProviderId LEFT JOIN [Standard] s on (s.StandardCode = a.StandardCode and s.Version = a.Version) LEFT jOIN [Framework] f on (f.FrameworkCode = a.FrameworkCode AND f.PathwayCode = a.PathwayCode AND f.ProgType = a.ProgType) WHERE a.recordStatusId=2 ORDER BY ProviderId "; var apprenticeshipLocationsSQL = @"SELECT al.ApprenticeshipId, al.ApprenticeshipLocationId, l.LocationId, a.AddressId, a.AddressLine1, a.AddressLine2, a.County, a.Postcode, a.Town, a.Longitude, a.Latitude, l.Website, l.Email, als.CSV as DeliveryModeStr, l.Telephone, l.LocationName, p.ProviderId, p.Ukprn, al.Radius FROM ApprenticeshipLocation al INNER JOIN Location l on l.LocationId = al.LocationId INNER JOIN Provider p on p.ProviderId = l.ProviderId INNER JOIN Address a ON a.AddressId = l.AddressId CROSS APPLY (SELECT STRING_AGG(DeliveryModeId,',') as CSV, aldm.ApprenticeshipLocationId FROM ApprenticeshipLocationDeliveryMode aldm WHERE ApprenticeshipLocationId = al.ApprenticeshipLocationId GROUP BY aldm.ApprenticeshipLocationId ) als WHERE al.RecordStatusId = 2 and al.ApprenticeshipId = @ApprenticeshipId ORDER BY ApprenticeshipId,ApprenticeshipLocationId"; try { using (var conn1 = new SqlConnection(connectionString)) using (var apprenticeshipscmd = conn1.CreateCommand()) { await conn1.OpenAsync(); apprenticeshipscmd.CommandText = apprenticeshipSQL; using (var apprenticeshipReader = apprenticeshipscmd.ExecuteReader()) { while (await apprenticeshipReader.ReadAsync()) { apprenticeshipList.Add(ApprenticeshipResult.FromDataReader(apprenticeshipReader)); } } } } catch (Exception e) { AddResultMessage(0, 0, "Failed", null, e.Message); log.LogError("Error occured Migrating Apprenticeships", e.Message); } await Task.WhenAll(apprenticeshipList.Select(async apprenticeship => { var apprenticeshipErrors = new List <string>(); //Errors Here cause apprenticeships to go into pending var apprenticeshipWarning = new List <string>(); //informational messages await semaphore.WaitAsync(); try { if (IsOnWhiteList(apprenticeship.UKPRN)) { apprenticeshipErrors = new List <string>(); //get relevant info var exisitingApprenticeship = await GetExistingApprenticeship(apprenticeship); var referenceDataFramework = await GetReferenceDataFramework(apprenticeship); var referenceDataStandard = await GetReferenceDataStandard(apprenticeship); var locations = await GetLocations(apprenticeship); var cosmosVenues = await GetCosmosVenues(locations); var cosmosProvider = await GetProvider(apprenticeship); //map objects for creating cosmos record var locs = MapLocations(locations, cosmosVenues); var id = exisitingApprenticeship?.id.ToString() ?? Guid.NewGuid().ToString(); var apprenticeType = MapApprenticeshipType(apprenticeship); //check to see if framework code/standard code is valid VerifiyIfStandardOrFramework(apprenticeship, referenceDataFramework, referenceDataStandard); var mappedStatus = MapApprenticeshipRecordStatus(locs); var mappedApprenticeship = MapApprenticeship(locs, id, apprenticeship, apprenticeType, mappedStatus, referenceDataFramework?.Id.ToString(), referenceDataStandard?.id.ToString(), cosmosProvider?.id.ToString()); //insert record into cosmos await CreateOrUpdateApprenticeshipRecord(mappedApprenticeship); //log message to output AddResultMessage(apprenticeship.ApprenticeshipID, apprenticeship.UKPRN, Enum.GetName(typeof(RecordStatus), mappedApprenticeship.RecordStatus), mappedApprenticeship.ApprenticeshipTitle, string.Join("\n", apprenticeshipErrors), string.Join("\n", apprenticeshipWarning)); } else { AddResultMessage(apprenticeship.ApprenticeshipID, apprenticeship.UKPRN, "Skipped", null, $"PRN {apprenticeship.UKPRN} not whitelisted"); } } catch (Exception e) { AddResultMessage(apprenticeship.ApprenticeshipID, apprenticeship.UKPRN, "Failed", null, $"Exception occured creating record - {e.Message}"); log.LogError("Error occurred creating or updating apprenticeship record!", e); } finally { semaphore.Release(); } void VerifiyIfStandardOrFramework(ApprenticeshipResult tribalRecord, ReferenceDataFramework refDataFramework, ReferenceDateStandard refDataStandard) { if (refDataFramework == null && refDataStandard == null) { apprenticeshipWarning.Add($"Standard/Framework code does not exist - framework code {tribalRecord.FrameworkCode}, pathway code: {tribalRecord.PathWayCode}, standard code: {tribalRecord.StandardCode}, version: {tribalRecord.Version}"); } } ApprenticeshipDTO MapApprenticeship(IList <ApprenticeshipLocationDTO> locs, string id, ApprenticeshipResult tribalRecord, ApprenticeshipType apprenticeshipTye, RecordStatus recordStatus, string frameworkId, string standardId, string providerId) { var cosmosApprenticeship = new ApprenticeshipDTO() { id = id, ApprenticeshipId = tribalRecord.ApprenticeshipID, ApprenticeshipTitle = tribalRecord.ApprenticeshipTitle, ProviderId = providerId, PathWayCode = tribalRecord.PathWayCode, ProgType = tribalRecord.ProgType, ProviderUKPRN = tribalRecord.UKPRN, FrameworkId = frameworkId, StandardId = standardId, FrameworkCode = tribalRecord.FrameworkCode, StandardCode = tribalRecord.StandardCode, Version = tribalRecord.Version, MarketingInformation = tribalRecord.MarketingInformation, Url = tribalRecord.Url, ContactTelephone = tribalRecord.ContactTelephone, ContactEmail = tribalRecord.ContactEmail, ContactWebsite = tribalRecord.ContactWebsite, CreatedBy = createdBy, CreatedDate = createdDate, NotionalNVQLevelv2 = tribalRecord.NotionalEndLevel, ApprenticeshipLocations = locs, ApprenticeshipType = apprenticeshipTye, RecordStatus = recordStatus }; return(cosmosApprenticeship); } async Task <IList <Dfc.CourseDirectory.Models.Models.Venues.Venue> > GetCosmosVenues(IList <ApprenticeshipLocationResult> locations) { IList <Dfc.CourseDirectory.Models.Models.Venues.Venue> lst = new List <Dfc.CourseDirectory.Models.Models.Venues.Venue>(); foreach (var s in locations) { var venue = await venueCollectionService.GetDocumentByLocationId(s.LocationId, s.UKPRN); if (venue != null) { lst.Add(venue); } } return(lst); } async Task <Provider> GetProvider(ApprenticeshipResult item) { return(await providerCollectionService.GetDocumentByUkprn(item.UKPRN)); } async Task <List <ApprenticeshipLocationResult> > GetLocations(ApprenticeshipResult item) { using (var sqlConnection = new SqlConnection(connectionString)) { var lst = await sqlConnection.QueryAsync <ApprenticeshipLocationResult>(apprenticeshipLocationsSQL, new { apprenticeshipId = item.ApprenticeshipID }, commandType: CommandType.Text); return(lst.ToList()); } } async Task <ReferenceDateStandard> GetReferenceDataStandard(ApprenticeshipResult item) { var app = await apprenticeReferenceDataService.GetStandardById(item.StandardCode ?? 0, item.Version ?? 0); return(app?.Value?.Value); } async Task <ReferenceDataFramework> GetReferenceDataFramework(ApprenticeshipResult item) { //checks for framework apprenticeship var app = await apprenticeReferenceDataService.GetFrameworkByCode(item.FrameworkCode ?? 0, item.ProgType ?? 0, item.PathWayCode ?? 0); return(app?.Value?.Value); } async Task <Apprenticeship> GetExistingApprenticeship(ApprenticeshipResult item) { //fetch existing apprenticeship row. return(await apprenticeshipService.GetApprenticeshipByApprenticeshipID(item.ApprenticeshipID)); } async Task CreateOrUpdateApprenticeshipRecord(ApprenticeshipDTO app) { var s = UriFactory.CreateDocumentUri(databaseId, apprenticeshipCollectionId, app.id); Uri collectionUri = UriFactory.CreateDocumentCollectionUri(databaseId, apprenticeshipCollectionId); var res = await client.UpsertDocumentAsync(collectionUri, app); } RecordStatus MapApprenticeshipRecordStatus(IList <ApprenticeshipLocationDTO> mappedLocation) { //if there are any errors with apprenticeshipREcord, set record to migration pending. if (apprenticeshipErrors.Any()) { return(RecordStatus.MigrationPending); } else { return(RecordStatus.Live); } } //Taken entirely from previous migration logic. ApprenticeshipLocationType GetApprenticeshipLocationType(ApprenticeshipLocationResult lo) { var deliveryModes = lo.DeliveryModes; if ((deliveryModes.Contains(1) && !deliveryModes.Contains(2) && deliveryModes.Contains(3)) || (deliveryModes.Contains(1) && deliveryModes.Contains(2) && !deliveryModes.Contains(3)) || (deliveryModes.Contains(1) && deliveryModes.Contains(2) && deliveryModes.Contains(3))) { return(ApprenticeshipLocationType.ClassroomBasedAndEmployerBased); } else if ((!deliveryModes.Contains(1) && !deliveryModes.Contains(2) && deliveryModes.Contains(3)) || (!deliveryModes.Contains(1) && deliveryModes.Contains(2) && !deliveryModes.Contains(3)) || (!deliveryModes.Contains(1) && deliveryModes.Contains(2) && deliveryModes.Contains(3))) { return(ApprenticeshipLocationType.ClassroomBased); } else if (deliveryModes.Contains(1) && !deliveryModes.Contains(2) && !deliveryModes.Contains(3)) { return(ApprenticeshipLocationType.EmployerBased); } else { return(ApprenticeshipLocationType.Undefined); } } ApprenticeshipType MapApprenticeshipType(ApprenticeshipResult tribalRecord) { if (tribalRecord.StandardCode.HasValue) { return(ApprenticeshipType.StandardCode); } else if (tribalRecord.FrameworkCode.HasValue) { return(ApprenticeshipType.FrameworkCode); } else { apprenticeshipWarning.Add($"ApprenticeshipId: {tribalRecord.ApprenticeshipID} has undefined apprenticeshipType"); return(ApprenticeshipType.Undefined); } } IList <ApprenticeshipLocationDTO> MapLocations(IList <ApprenticeshipLocationResult> locations, IList <Dfc.CourseDirectory.Models.Models.Venues.Venue> venues) { var locationBasedApprenticeshipLocation = new List <ApprenticeshipLocationDTO>(); var regionBasedApprenticeshipLocation = new List <ApprenticeshipLocationDTO>(); //no need to proceed if (locations == null) { return(null); } //employer based apprenticeships - group all locations into regions/subregions foreach (var location in locations) { var type = GetApprenticeshipLocationType(location); if (type == ApprenticeshipLocationType.EmployerBased) { var allRegionsWithSubRegions = new SelectRegionModel(); var onspdRegionSubregion = onspdService.GetOnspdData(new OnspdSearchCriteria(location.Postcode)); if (onspdRegionSubregion.IsFailure) { apprenticeshipWarning.Add($"LocationId: {location.LocationId} - Querying onspd failed - {onspdRegionSubregion.Error}"); continue; } else if (!onspdRegionSubregion.HasValue) { apprenticeshipWarning.Add($"Location:{location.LocationId} - Did not find a record for postcode: {location.Postcode}"); continue; } var selectedSubRegion = allRegionsWithSubRegions.RegionItems.SelectMany(sr => sr.SubRegion.Where(sb => sb.SubRegionName == onspdRegionSubregion.Value.Value.LocalAuthority || sb.SubRegionName == onspdRegionSubregion.Value.Value.County || onspdRegionSubregion.Value.Value.LocalAuthority.Contains(sb.SubRegionName) )).FirstOrDefault(); if (selectedSubRegion == null) { apprenticeshipWarning.Add($"Location:{location.LocationId} Unable to match region with ons data api, location skipped"); continue; } else { var appLocation = new ApprenticeshipLocationDTO() { Id = Guid.NewGuid().ToString(), VenueId = Guid.Empty.ToString(), TribalId = location.ApprenticeshipLocationId, DeliveryModes = location.DeliveryModes, LocationId = selectedSubRegion.ApiLocationId, Name = location.LocationName, ProviderId = location.ProviderId, ProviderUKPRN = location.UKPRN, Radius = location.Radius, ApprenticeshipLocationType = type, LocationType = LocationType.SubRegion, LocationGuidId = null, Regions = new List <string> { selectedSubRegion.Id }, RecordStatus = VenueStatus.Live, CreatedBy = createdBy, CreatedDate = createdDate, UpdatedBy = createdBy, UpdatedDate = createdDate }; //region based apprenticeships regionBasedApprenticeshipLocation.Add(appLocation); } } else if (type == ApprenticeshipLocationType.ClassroomBased || type == ApprenticeshipLocationType.ClassroomBasedAndEmployerBased) { //venue based (location based apprenticeships) var cosmosVenueItem = venues.FirstOrDefault(x => x.LocationId == location.LocationId); var status = default(VenueStatus); //set status be that of what the venue status is, otherwise if venue is not found //set status to pending. if (cosmosVenueItem != null) { status = cosmosVenueItem.Status; } else { apprenticeshipWarning.Add($"LocationId: {location.LocationId} did not find a venue in cosmos, record marked as pending"); continue; } var appLocation = new ApprenticeshipLocationDTO() { Id = Guid.NewGuid().ToString(), VenueId = Guid.Empty.ToString(), TribalId = location.ApprenticeshipLocationId, Address = new Dfc.CourseDirectory.Models.Models.Apprenticeships.Address() { Address1 = cosmosVenueItem?.Address1, Address2 = cosmosVenueItem?.Address2, County = cosmosVenueItem?.County, Email = cosmosVenueItem?.Email, Website = cosmosVenueItem?.Website, Longitude = cosmosVenueItem?.Longitude, Latitude = cosmosVenueItem?.Latitude, Postcode = cosmosVenueItem?.PostCode, Town = cosmosVenueItem?.Town, Phone = cosmosVenueItem?.Telephone }, DeliveryModes = location.DeliveryModes, LocationId = location.LocationId, Name = location.LocationName, ProviderId = location.ProviderId, ProviderUKPRN = location.UKPRN, Radius = location.Radius, ApprenticeshipLocationType = type, LocationType = LocationType.Venue, LocationGuidId = cosmosVenueItem?.ID, Regions = null, RecordStatus = status, CreatedBy = createdBy, CreatedDate = createdDate, UpdatedBy = createdBy, UpdatedDate = createdDate }; locationBasedApprenticeshipLocation.Add(appLocation); } else { apprenticeshipWarning.Add($"LocationId: {location.LocationId} skipped as type was unknown {type}"); continue; } } //add a new location with all distinct regions. if (regionBasedApprenticeshipLocation.Any(x => x.RecordStatus == VenueStatus.Live)) { var regionLocation = regionBasedApprenticeshipLocation.FirstOrDefault(x => x.RecordStatus == VenueStatus.Live); regionLocation.Regions = regionBasedApprenticeshipLocation.Where(x => x.Regions != null).SelectMany(x => x.Regions).Distinct().ToList(); locationBasedApprenticeshipLocation.Add(regionLocation); } return(locationBasedApprenticeshipLocation); } })); //Log Results to blob storage var resultsObjBytes = GetResultAsByteArray(result); await WriteResultsToBlobStorage(resultsObjBytes); //log completion log.LogInformation("Migrating Apprenticeships Complete"); 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); } async Task WriteResultsToBlobStorage(byte[] data) { await blobhelper.UploadFile(blobContainer, venueExportFileName, data); } void AddResultMessage(int apprenticeshipId, int ukprn, string status, string apprenticeshipTitle, string message = "", string warnings = "") { lock (result) { var validateResult = new ApprenticeshipResultMessage() { ApprenticeshipID = apprenticeshipId, Status = status, Message = message, UKPRN = ukprn, ApprenticeshipTitle = apprenticeshipTitle, Warnings = warnings }; result.Add(validateResult); } } byte[] GetResultAsByteArray(IList <ApprenticeshipResultMessage> 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 <ApprenticeshipResultMessage>(ob); csvWriter.Flush(); } return(memoryStream.ToArray()); } } bool IsOnWhiteList(int ukprn) { if (!whiteListProviders.Any(x => x == ukprn)) { return(false); } else { return(true); } } }