public static async Task Run(
[TimerTrigger("%ApprenticeshipMigrationSchedule%")] TimerInfo myTimer,
ILogger log,
[Inject] IConfigurationRoot configuration,
[Inject] IBlobStorageHelper blobStorageHelper,
[Inject] IApprenticeshipServiceWrapper apprenticeshipServiceWrapper)
{
var logFile = new StringBuilder();
logFile.AppendLine($"Starting {nameof(ApprenticeshipDeltaExport)} at {DateTime.Now}");
var fileNames = new List <string>();
var last24HoursAgo = DateTime.Today.AddDays(-1);
var providersFileName = $"{DateTime.Today.ToString("yyyyMMdd")}\\Apprenticeships\\Apprenticeships_for_Providers_{DateTime.Now.ToString("yyyy-MM-ddTHH-mm-ss")}.json";
logFile.AppendLine($"24 Hours ago: {last24HoursAgo}");
logFile.AppendLine($"Apprenticeship Delta filename: {providersFileName}");
var containerNameExporter = configuration["ContainerNameExporter"];
var containerExporter = blobStorageHelper.GetBlobContainer(containerNameExporter);
try
{
logFile.AppendLine($"Attempting to call Apprenticeship API to gather apprenticeship delta updates");
var apprenticeshipDelta = apprenticeshipServiceWrapper.GetApprenticeshipDeltaUpdatesAsJson();
logFile.AppendLine($"Successful call to apprenticeship API");
if (apprenticeshipDelta != null)
{
logFile.AppendLine($"Apprenticeship Delta JSON returned");
var providersBlob = containerExporter.GetBlockBlobReference(providersFileName);
logFile.AppendLine($"Attempting to upload apprenticeship delta json");
await providersBlob.UploadTextAsync(apprenticeshipDelta);
logFile.AppendLine($"Upload successful");
}
else
{
logFile.AppendLine($"No updated apprenticeships between {last24HoursAgo} and {DateTime.Now} ");
}
}
catch (Exception e)
{
logFile.AppendLine(e.Message);
logFile.AppendLine(e.ToString());
}
finally
{
logFile.AppendLine($"Ending {nameof(ApprenticeshipDeltaExport)} at {DateTime.Now}");
var logFileName = $"{DateTime.Today.ToString("yyyyMMdd")}\\Apprenticeships\\Logs\\Log_{DateTime.Now.ToString("yyyy-MM-ddTHH-mm-ss")}.txt";
var logFileNameBolb = containerExporter.GetBlockBlobReference(logFileName);
await logFileNameBolb.UploadTextAsync(logFile.ToString());
}
}
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);
}
}
}
