public async Task <bool> WriteAsync(IDataStoreContext dataStoreContext, IDataStoreCache cache, CancellationToken cancellationToken)
{
using (var connection = new OleDbConnection(string.Format(MdbConstants.MdbConnectionStringTemplate, dataStoreContext.ExportOutputLocation)))
{
await connection.OpenAsync(cancellationToken);
var exports = _exports
.Where(e => dataStoreContext.Tasks.Contains(e.TaskKey, StringComparer.OrdinalIgnoreCase))
.OrderBy(e => e.TaskOrder);
_logger.LogInfo("Starting Export");
foreach (var export in exports)
{
await export.ExportAsync(cache, connection, dataStoreContext.ExportOutputLocation,
cancellationToken);
}
cancellationToken.ThrowIfCancellationRequested();
_logger.LogInfo("Finished Export");
}
return(true);
}
private void PopulateDataStoreCache(IDataStoreCache cache, IEnumerable <FM36Learner> learners, FM36Global fm36Global, int ukprn)
{
cache.AddRange(BuildGlobals(fm36Global, ukprn));
learners.NullSafeForEach(learner =>
{
var learnRefNumber = learner.LearnRefNumber;
cache.Add(BuildLearner(learner, ukprn, learnRefNumber));
learner.LearningDeliveries.NullSafeForEach(learningDelivery => cache.Add(BuildLearningDelivery(learningDelivery, ukprn, learnRefNumber)));
learner.PriceEpisodes.NullSafeForEach(priceEpisode => cache.Add(BuildPriceEpisode(priceEpisode, ukprn, learnRefNumber)));
});
var learningDeliveryPeriodisedValues = learners.SelectMany(l => l.LearningDeliveries.Select(ld =>
new FundModel36LearningDeliveryPeriodisedValue(fm36Global.UKPRN, l.LearnRefNumber, ld.AimSeqNumber, ld.LearningDeliveryPeriodisedValues, ld.LearningDeliveryPeriodisedTextValues)));
cache.AddRange(BuildLearningDeliveryPeriods(learningDeliveryPeriodisedValues));
learningDeliveryPeriodisedValues.NullSafeForEach(ldpv =>
{
ldpv.LearningDeliveryPeriodisedValue.NullSafeForEach(learningDeliveryPeriodisedValue => cache.Add(BuildLearningDeliveryPeriodisedValues(learningDeliveryPeriodisedValue, ukprn, ldpv.AimSeqNumber, ldpv.LearnRefNumber)));
ldpv.LearningDeliveryPeriodisedTextValue.NullSafeForEach(learningDeliveryPeriodisedTextValue => cache.Add(BuildLearningDeliveryPeriodisedTextValues(learningDeliveryPeriodisedTextValue, ukprn, ldpv.AimSeqNumber, ldpv.LearnRefNumber)));
});
var priceEpisodePeriodisedValues = learners.SelectMany(l => l.PriceEpisodes.Select(pe =>
new FundModelPriceEpisodePeriodisedValue <List <PriceEpisodePeriodisedValues> >(fm36Global.UKPRN, l.LearnRefNumber, pe.PriceEpisodeIdentifier, pe.PriceEpisodePeriodisedValues)));
cache.AddRange(BuildPriceEpisodePeriods(priceEpisodePeriodisedValues));
priceEpisodePeriodisedValues.NullSafeForEach(pepv => pepv.PriceEpisodePeriodisedValue.NullSafeForEach(priceEpisodePeriodisedValue => cache.Add(BuildPriceEpisodePeriodisedValue(priceEpisodePeriodisedValue, ukprn, pepv.LearnRefNumber, pepv.PriceEpisodeIdentifier))));
}
public async Task WriteAsync(IDataStoreCache cache, SqlConnection connection, SqlTransaction transaction, CancellationToken cancellationToken)
{
await _persistenceService.PersistInvalidLearnerDataAsync(cache, connection, transaction, cancellationToken);
_logger.LogDebug("WriteToDEDS - ILR Invalid Learner Data Stored");
cancellationToken.ThrowIfCancellationRequested();
}
private void PopulateDataStoreCache(IDataStoreCache cache, IEnumerable <ALBLearner> learners, ALBGlobal albGlobal, int ukprn)
{
cache.AddRange(BuildGlobals(albGlobal, ukprn));
learners.NullSafeForEach(learner =>
{
var learnRefNumber = learner.LearnRefNumber;
cache.Add(BuildLearner(learnRefNumber, ukprn));
learner.LearningDeliveries.NullSafeForEach(ld => cache.Add(BuildLearningDelivery(ld, ukprn, learnRefNumber)));
});
var periodisedValues = learners.Select(l => new FundModelLearnerPeriodisedValue <List <LearnerPeriodisedValue> >(ukprn, l.LearnRefNumber, l.LearnerPeriodisedValues));
cache.AddRange(BuildLearnerPeriods(periodisedValues, ukprn));
periodisedValues.NullSafeForEach(pv => pv.LearnerPeriodisedValue.NullSafeForEach(lpv => cache.Add(BuildLearnerPeriodisedValue(lpv, ukprn, pv.LearnRefNumber))));
var learningDeliveryPeriodisedValues = learners
.SelectMany(l => l.LearningDeliveries.Select(ld =>
new FundModelLearningDeliveryPeriodisedValue <List <LearningDeliveryPeriodisedValue> >(ukprn, l.LearnRefNumber, ld.AimSeqNumber, ld.LearningDeliveryPeriodisedValues)));
cache.AddRange(BuildLearningDeliveryPeriods(learningDeliveryPeriodisedValues, ukprn));
learningDeliveryPeriodisedValues.NullSafeForEach(ldpv => ldpv.LearningDeliveryPeriodisedValue.NullSafeForEach(learningDeliveryPeriodisedValue => cache.Add(BuildLearningDeliveryPeriodisedValues(learningDeliveryPeriodisedValue, ldpv.AimSeqNumber, ukprn, ldpv.LearnRefNumber))));
}
public async Task ExportAsync(IDataStoreCache dataStoreCache, OleDbConnection connection, string exportPath,
CancellationToken cancellationToken)
{
await ExportTableAsync <FM25_global, FM25globalClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <FM25_Learner, FM25LearnerClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
}
private void PopulateDataStoreCache(IDataStoreCache cache, IEnumerable <FM70Learner> learners, FM70Global fm70Global, int ukprn)
{
cache.AddRange(BuildGlobals(fm70Global, ukprn));
learners.NullSafeForEach(learner =>
{
var learnRefNumber = learner.LearnRefNumber;
cache.Add(BuildLearner(ukprn, learnRefNumber));
learner.LearnerDPOutcomes.NullSafeForEach(learnerDp => cache.Add(BuildDPOutcome(learnerDp, ukprn, learnRefNumber)));
learner.LearningDeliveries.NullSafeForEach(learningDelivery =>
{
var aimSeqNumber = learningDelivery.AimSeqNumber.Value;
cache.Add(BuildLearningDelivery(learningDelivery, ukprn, learnRefNumber));
learningDelivery.LearningDeliveryDeliverableValues.NullSafeForEach(ldd => cache.Add(BuildLearningDeliveryDeliverable(ldd, ukprn, learnRefNumber, aimSeqNumber)));
});
});
var learningDeliveryPeriodisedValues = learners.SelectMany(l => l.LearningDeliveries.SelectMany(ld => ld.LearningDeliveryDeliverableValues.Select(ldd =>
new FundModelESFLearningDeliveryPeriodisedValue <List <LearningDeliveryDeliverablePeriodisedValue> >(ukprn, l.LearnRefNumber, ld.AimSeqNumber.Value, ldd.DeliverableCode, ldd.LearningDeliveryDeliverablePeriodisedValues))));
cache.AddRange(BuildLearningDeliveryDeliverablePeriods(learningDeliveryPeriodisedValues));
learningDeliveryPeriodisedValues.NullSafeForEach(ldpv => ldpv.LearningDeliveryPeriodisedValue.NullSafeForEach(learningDeliveryPeriodisedValue => cache.Add(BuildLearningDeliveryDeliverablePeriodisedValue(learningDeliveryPeriodisedValue, ukprn, ldpv.AimSeqNumber, ldpv.LearnRefNumber, ldpv.EsfDeliverableCode))));
}
private void PopulateDataStoreCache(IDataStoreCache cache, IDataStoreContext dataStoreContext, IMessage message, IEnumerable <FM70Learner> learners, int ukprn)
{
var academicYear = $"20{dataStoreContext.CollectionYear.Substring(0, 2)}/{dataStoreContext.CollectionYear.Substring(2, 2)}";
var collectionReturnCode = dataStoreContext.CollectionPeriod;
var collectionType = $"ILR{dataStoreContext.CollectionYear}";
cache.Add(BuildLatestProviderSubmission(ukprn, collectionReturnCode, collectionType));
if (learners == null)
{
return;
}
var conRefNumberDictionary = BuildConRefNumberDictionary(message);
var learningDeliveryPeriodisedValues =
learners
.SelectMany(l => l.LearningDeliveries
.SelectMany(ld => ld.LearningDeliveryDeliverableValues
.Select(ldd => new FundModelESFLearningDeliveryPeriodisedValue <List <LearningDeliveryDeliverablePeriodisedValue> >(ukprn, l.LearnRefNumber, ld.AimSeqNumber.Value, ldd.DeliverableCode, ldd.LearningDeliveryDeliverablePeriodisedValues))));
learningDeliveryPeriodisedValues
.NullSafeForEach(ldpv => ldpv.LearningDeliveryPeriodisedValue
.NullSafeForEach(learningDeliveryPeriodisedValue => cache.Add(BuildFundingData(dataStoreContext, learningDeliveryPeriodisedValue, conRefNumberDictionary, ukprn, ldpv.AimSeqNumber, ldpv.LearnRefNumber, ldpv.EsfDeliverableCode, academicYear, collectionReturnCode, collectionType))));
}
public void MapData(IDataStoreCache cache, IDataStoreContext dataStoreContext, IMessage message, FM70Global fm70Global)
{
var learners = fm70Global?.Learners;
var ukprn = fm70Global.UKPRN;
PopulateDataStoreCache(cache, dataStoreContext, message, learners, ukprn);
}
public async Task ExportAsync(IDataStoreCache dataStoreCache, OleDbConnection connection, string exportPath,
CancellationToken cancellationToken)
{
await ExportTableAsync <DV_global, DVglobalClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <DV_Learner, DVLearnerClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <DV_LearningDelivery, DVLearningDeliveryClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
}
private void PopulateValidLearners(IDataStoreCache cache, int ukprn, IHeader header, IReadOnlyCollection <ISourceFile> sourceFileCollection, IEnumerable <ILearner> learners, IEnumerable <ILearnerDestinationAndProgression> destinationAndProgressions)
{
var source = header.SourceEntity;
int lLDDandHealthProblemID = 1;
int learningDeliveryFamId = 1;
int appFinRecordId = 1;
cache.AddRange(BuildCollectionDetails(ukprn, header));
cache.AddRange(BuildLearningProviders(ukprn));
cache.AddRange(BuildSources(ukprn, source));
cache.AddRange(BuildSourceFiles(ukprn, sourceFileCollection));
learners.NullSafeForEach(learner =>
{
cache.Add(BuildValidLearner(ukprn, learner));
learner.ContactPreferences.NullSafeForEach(contactPreference => cache.Add(BuildContactPreference(ukprn, learner, contactPreference)));
learner.LearningDeliveries.NullSafeForEach(learningDelivery =>
{
cache.Add(BuildLearningDelivery(ukprn, learner, learningDelivery));
if (learningDelivery.LearningDeliveryHEEntity != null)
{
cache.Add(BuildLearningDeliveryHERecord(ukprn, learner, learningDelivery));
}
learningDelivery.AppFinRecords.NullSafeForEach(appFinRecord => cache.Add(BuildLearningDeliveryAppFinRecord(ukprn, learner, learningDelivery, appFinRecord, appFinRecordId++)));
learningDelivery.LearningDeliveryFAMs.NullSafeForEach(famRecord => cache.Add(BuildLearningDeliveryFAMRecord(ukprn, learner, learningDelivery, famRecord, learningDeliveryFamId++)));
learningDelivery.LearningDeliveryWorkPlacements.NullSafeForEach(workPlacement => cache.Add(BuildLearningDeliveryWorkPlacement(ukprn, learner, learningDelivery, workPlacement)));
learningDelivery.ProviderSpecDeliveryMonitorings.NullSafeForEach(monitoring => cache.Add(BuildProviderSpecDeliveryMonitoring(ukprn, learner, learningDelivery, monitoring)));
});
learner.LearnerEmploymentStatuses.NullSafeForEach(employmentStatus =>
{
cache.Add(BuildLearnerEmploymentStatus(ukprn, learner, employmentStatus));
employmentStatus.EmploymentStatusMonitorings.NullSafeForEach(monitoring => cache.Add(BuildEmploymentStatusMonitoring(ukprn, learner, employmentStatus, monitoring)));
});
learner.LearnerFAMs.NullSafeForEach(fam => cache.Add(BuildLearnerFAM(ukprn, learner, fam)));
if (learner.LearnerHEEntity != null)
{
cache.Add(BuildLearnerHE(ukprn, learner));
learner.LearnerHEEntity.LearnerHEFinancialSupports.NullSafeForEach(support => cache.Add(BuildLearnerHEFinancialSupport(ukprn, learner, support)));
}
learner.LLDDAndHealthProblems.NullSafeForEach(problem => cache.Add(BuildLLDDAndHealthProblem(ukprn, learner, problem, lLDDandHealthProblemID++)));
learner.ProviderSpecLearnerMonitorings.NullSafeForEach(monitoring => cache.Add(BuildProviderSpecLearnerMonitoring(ukprn, learner, monitoring)));
});
destinationAndProgressions.NullSafeForEach(destinationAndProgression =>
{
cache.Add(BuildLearnerDestinationAndProgression(ukprn, destinationAndProgression));
destinationAndProgression.DPOutcomes.NullSafeForEach(dpOutcome => cache.Add(BuildDPOutcome(ukprn, destinationAndProgression, dpOutcome)));
});
}
public async Task ExportAsync(IDataStoreCache dataStoreCache, OleDbConnection connection, string exportPath,
CancellationToken cancellationToken)
{
await ExportTableAsync <FM25_FM35_global, FM25FM35globalClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <FM25_FM35_Learner_Period, FM25FM35LearnerPeriodClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <FM25_FM35_Learner_PeriodisedValue, FM25FM35LearnerPeriodisedValuesClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
}
public void MapLearnerData(IDataStoreCache cache, IMessage ilr, IEnumerable <string> learnersValid)
{
var ukprn = ilr.LearningProviderEntity.UKPRN;
var header = ilr.HeaderEntity;
var sourceFileCollection = ilr.SourceFilesCollection;
var learners = ilr.Learners?.Where(l => learnersValid.Contains(l.LearnRefNumber, StringComparer.OrdinalIgnoreCase));
var destinationAndProgressions = ilr.LearnerDestinationAndProgressions?.Where(ldp => learnersValid.Contains(ldp.LearnRefNumber, StringComparer.OrdinalIgnoreCase));
PopulateValidLearners(cache, ukprn, header, sourceFileCollection, learners, destinationAndProgressions);
}
public void MapInvalidLearnerData(IDataStoreCache cache, ILooseMessage ilr, IEnumerable <string> learnersValid)
{
var ukprn = ilr.LearningProviderEntity.UKPRN;
var header = ilr.HeaderEntity;
var sourceFileCollection = ilr.SourceFilesCollection;
var learners = ilr.Learners?.Where(l => !learnersValid.Contains(!string.IsNullOrWhiteSpace(l.LearnRefNumber) ? l.LearnRefNumber.Trim() : string.Empty, StringComparer.OrdinalIgnoreCase));
var learnerDestinationAndProgressions = ilr.LearnerDestinationAndProgressions?.Where(ldp => !learnersValid.Contains(!string.IsNullOrWhiteSpace(ldp.LearnRefNumber) ? ldp.LearnRefNumber.Trim() : string.Empty, StringComparer.OrdinalIgnoreCase));
PopulateInvalidLearners(cache, ukprn, header, sourceFileCollection, learners, learnerDestinationAndProgressions);
}
public async Task PersistFM81DataAsync(IDataStoreCache dataStoreCache, SqlConnection sqlConnection, SqlTransaction sqlTransaction, CancellationToken cancellationToken)
{
await _bulkInsert.Insert(TableNameConstants.FM81_global, dataStoreCache.Get <TBL_global>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.FM81_Learner, dataStoreCache.Get <TBL_Learner>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.FM81_LearningDelivery, dataStoreCache.Get <TBL_LearningDelivery>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.FM81_LearningDelivery_Period, dataStoreCache.Get <TBL_LearningDelivery_Period>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.FM81_LearningDelivery_PeriodisedValues, dataStoreCache.Get <TBL_LearningDelivery_PeriodisedValue>(), sqlConnection, sqlTransaction, cancellationToken);
}
public async Task ExportAsync(IDataStoreCache dataStoreCache, OleDbConnection connection, string exportPath, CancellationToken cancellationToken)
{
await ExportTableAsync <TBL_global, TBLglobalClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <TBL_Learner, TBLLearnerClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <TBL_LearningDelivery, TBLLearningDeliveryClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <TBL_LearningDelivery_Period, TBLLearningDeliveryPeriodClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <TBL_LearningDelivery_PeriodisedValue, TBLLearningDeliveryPeriodsedValuesClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
}
public async Task WriteILRDataAsync(IDataStoreContext dataStoreContext, IDataStoreCache cache, CancellationToken cancellationToken)
{
var transactions = _ilrTransactions
.Where(t => dataStoreContext.Tasks.Contains(t.TaskKey, StringComparer.OrdinalIgnoreCase))
.OrderBy(t => t.TaskOrder);
using (SqlConnection ilrConnection = new SqlConnection(dataStoreContext.IlrDatabaseConnectionString))
{
await ilrConnection.OpenAsync(cancellationToken);
cancellationToken.ThrowIfCancellationRequested();
_logger.LogDebug("Starting ILR Transaction");
using (SqlTransaction ilrTransaction = ilrConnection.BeginTransaction())
{
try
{
await _dataStorePersistenceService.ClearIlrDataAsync(dataStoreContext, ilrConnection, ilrTransaction, cancellationToken);
_logger.LogDebug("WriteToDEDS - ILR Data cleared");
cancellationToken.ThrowIfCancellationRequested();
await _persistenceService.PersistProcessingInformationDataAsync(cache, ilrConnection, ilrTransaction, cancellationToken);
_logger.LogDebug("WriteToDEDS - ILR File Details Stored");
cancellationToken.ThrowIfCancellationRequested();
foreach (var transaction in transactions)
{
await transaction.WriteAsync(cache, ilrConnection, ilrTransaction, cancellationToken);
}
cancellationToken.ThrowIfCancellationRequested();
ilrTransaction.Commit();
}
catch (Exception ex)
{
_logger.LogError($"ILR Transaction failed attempting to rollback - {ex.Message}", ex);
ilrTransaction.Rollback();
_logger.LogDebug("ILR Transaction successfully rolled back");
throw;
}
_logger.LogDebug("ILR Transaction complete");
}
}
}
public void MapALBData(IDataStoreCache cache, ALBGlobal albGlobal)
{
var learners = albGlobal?.Learners;
if (learners == null)
{
return;
}
var ukprn = albGlobal.UKPRN;
PopulateDataStoreCache(cache, learners, albGlobal, ukprn);
}
public void MapData(IDataStoreCache cache, FM36Global fm36Global)
{
var learners = fm36Global?.Learners;
if (learners == null)
{
return;
}
var ukprn = fm36Global.UKPRN;
PopulateDataStoreCache(cache, learners, fm36Global, ukprn);
}
private void PopulateDataStoreCache(IDataStoreCache cache, IEnumerable <FM25Learner> learners, FM25Global fm25Global, int ukprn)
{
cache.AddRange(BuildFM25Global(fm25Global, ukprn));
cache.AddRange(BuildFM25_35_Global(fm25Global, ukprn));
learners.NullSafeForEach(learner =>
{
var learnRefNumber = learner.LearnRefNumber;
cache.Add(BuildFM25Learner(ukprn, learner));
learner.LearnerPeriods.NullSafeForEach(learnerPeriod => cache.Add(BuildFM25_35_LearnerPeriod(learnerPeriod, ukprn, learnRefNumber)));
learner.LearnerPeriodisedValues.NullSafeForEach(learnerPV => cache.Add(BuildFM25_35_LearnerPeriodisedValues(learnerPV, ukprn, learnRefNumber)));
});
}
public async Task ExportAsync(IDataStoreCache dataStoreCache, OleDbConnection connection, string exportPath,
CancellationToken cancellationToken)
{
await ExportTableAsync <CollectionDetail, DefaultTableClassMap <CollectionDetail> >(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <Learner, ValidLearnerClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <LearningProvider, DefaultTableClassMap <LearningProvider> >(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <Source, DefaultTableClassMap <Source> >(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <SourceFile, DefaultTableClassMap <SourceFile> >(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <ContactPreference, DefaultTableClassMap <ContactPreference> >(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <EmploymentStatusMonitoring, DefaultTableClassMap <EmploymentStatusMonitoring> >(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <LearnerEmploymentStatus, ValidLearnerEmploymentStatusClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <LearnerFAM, ValidLearnerFAMClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <LearnerHE, ValidLearnerHEClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <LearnerHEFinancialSupport, ValidLearnerHEFinancialSupportClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <LearningDelivery, ValidLearningDeliveryClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <LearningDeliveryFAM, ValidLearningDeliveryFAMClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <LearningDeliveryHE, ValidLearningDeliveryHEClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <AppFinRecord, ValidAppFinRecordClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <LearningDeliveryWorkPlacement, ValidLearningDeliveryWorkPlacementClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <LLDDandHealthProblem, ValidLLDDandHealthProblemClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <ProviderSpecDeliveryMonitoring, ValidProviderSpecDeliveryMonitoringClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <ProviderSpecLearnerMonitoring, ValidProviderSpecLearnerMonitoringClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <LearnerDestinationandProgression, ValidLearnerDestinationandProgressionClassMap>(exportPath, dataStoreCache, connection,
cancellationToken);
await ExportTableAsync <DPOutcome, ValidDPOutcomeClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
}
public async Task <bool> WriteAsync(IDataStoreContext dataStoreContext, IDataStoreCache cache, CancellationToken cancellationToken)
{
try
{
await _ilrTransaction.WriteILRDataAsync(dataStoreContext, cache, cancellationToken);
await _fm36HistoryTransaction.WriteFM36HistoryAsync(dataStoreContext, cache, cancellationToken);
await _esfFundingTransaction.WriteESFFundingAsync(dataStoreContext, cache, cancellationToken);
}
catch (Exception ex)
{
_logger.LogDebug($"Transaction Controller failed - {ex.Message}");
return(false);
}
return(true);
}
public async Task PersistInvalidLearnerDataAsync(IDataStoreCache dataStoreCache, SqlConnection sqlConnection, SqlTransaction sqlTransaction, CancellationToken cancellationToken)
{
await _bulkInsert.Insert(TableNameConstants.InvalidCollectionDetails, dataStoreCache.Get <ILR2021.DataStore.EF.Invalid.CollectionDetail>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.InvalidLearningProvider, dataStoreCache.Get <ILR2021.DataStore.EF.Invalid.LearningProvider>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.InvalidSource, dataStoreCache.Get <ILR2021.DataStore.EF.Invalid.Source>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.InvalidSourceFile, dataStoreCache.Get <ILR2021.DataStore.EF.Invalid.SourceFile>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.InvalidAppFinRecord, dataStoreCache.Get <ILR2021.DataStore.EF.Invalid.AppFinRecord>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.InvalidContactPreference, dataStoreCache.Get <ILR2021.DataStore.EF.Invalid.ContactPreference>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.InvalidEmploymentStatusMonitoring, dataStoreCache.Get <ILR2021.DataStore.EF.Invalid.EmploymentStatusMonitoring>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.InvalidLearner, dataStoreCache.Get <ILR2021.DataStore.EF.Invalid.Learner>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.InvalidLearnerEmploymentStatus, dataStoreCache.Get <ILR2021.DataStore.EF.Invalid.LearnerEmploymentStatus>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.InvalidLearnerFAM, dataStoreCache.Get <ILR2021.DataStore.EF.Invalid.LearnerFAM>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.InvalidLearnerHE, dataStoreCache.Get <ILR2021.DataStore.EF.Invalid.LearnerHE>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.InvalidLearnerHEFinancialSupport, dataStoreCache.Get <ILR2021.DataStore.EF.Invalid.LearnerHEFinancialSupport>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.InvalidLearningDelivery, dataStoreCache.Get <ILR2021.DataStore.EF.Invalid.LearningDelivery>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.InvalidLearningDeliveryFAM, dataStoreCache.Get <ILR2021.DataStore.EF.Invalid.LearningDeliveryFAM>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.InvalidLearningDeliveryHE, dataStoreCache.Get <ILR2021.DataStore.EF.Invalid.LearningDeliveryHE>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.InvalidLearningDeliveryWorkPlacement, dataStoreCache.Get <ILR2021.DataStore.EF.Invalid.LearningDeliveryWorkPlacement>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.InvalidLLDDandHealthProblem, dataStoreCache.Get <ILR2021.DataStore.EF.Invalid.LLDDandHealthProblem>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.InvalidProviderSpecDeliveryMonitoring, dataStoreCache.Get <ILR2021.DataStore.EF.Invalid.ProviderSpecDeliveryMonitoring>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.InvalidProviderSpecLearnerMonitoring, dataStoreCache.Get <ILR2021.DataStore.EF.Invalid.ProviderSpecLearnerMonitoring>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.InvalidDPOutcome, dataStoreCache.Get <ILR2021.DataStore.EF.Invalid.DPOutcome>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.InvalidLearnerDestinationandProgression, dataStoreCache.Get <ILR2021.DataStore.EF.Invalid.LearnerDestinationandProgression>(), sqlConnection, sqlTransaction, cancellationToken);
}
protected async Task ExportTableAsync <T, TClassMap>(string exportPath, IDataStoreCache cache, OleDbConnection connection, CancellationToken cancellationToken) where TClassMap : ClassMap <T>
{
using (var transaction = connection.BeginTransaction())
{
try
{
await _export.ExportAsync <T, TClassMap>(_context.GetTableName <T>(), cache.Get <T>(), exportPath, connection, transaction, cancellationToken);
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
_logger.LogError(ex.Message);
throw;
}
}
}
public async Task WriteESFFundingAsync(IDataStoreContext dataStoreContext, IDataStoreCache cache, CancellationToken cancellationToken)
{
using (SqlConnection esfFundingConnection = new SqlConnection(dataStoreContext.EsfFundingDatabaseConnectionString))
{
await esfFundingConnection.OpenAsync(cancellationToken);
cancellationToken.ThrowIfCancellationRequested();
_logger.LogDebug("Starting ESF Funding Transaction");
using (SqlTransaction esfFundingTransaction = esfFundingConnection.BeginTransaction())
{
try
{
await _dataStorePersistenceService.ClearEsfSummarisationDataAsync(dataStoreContext, esfFundingConnection, esfFundingTransaction, cancellationToken);
_logger.LogDebug("ESF clean up successful");
await _persistenceService.PersistESFSummarisationDataAsync(cache, esfFundingConnection, esfFundingTransaction, cancellationToken);
_logger.LogDebug("ESF Funding persistence complete");
await _persistenceService.PersistESFLatestProviderSubmissionAsync(cache, esfFundingConnection, esfFundingTransaction, cancellationToken);
_logger.LogDebug("ESF Latest Provider Submission persistence complete");
esfFundingTransaction.Commit();
}
catch (Exception ex)
{
_logger.LogDebug($"ESF Funding Transaction failed attempting to rollback - {ex.Message}");
esfFundingTransaction.Rollback();
_logger.LogDebug("ESF Funding Transaction successfully rolled back");
throw;
}
_logger.LogDebug("ESF Funding Transaction complete");
}
}
}
private void PopulateDataStoreCache(IDataStoreCache cache, IEnumerable <FM81Learner> learners, FM81Global fm81Global, int ukprn)
{
cache.AddRange(BuildGlobals(fm81Global, ukprn));
learners.NullSafeForEach(learner =>
{
var learnRefNumber = learner.LearnRefNumber;
cache.Add(BuildLearner(ukprn, learnRefNumber));
learner.LearningDeliveries.NullSafeForEach(learningDelivery => cache.Add(BuildLearningDelivery(learningDelivery, ukprn, learnRefNumber)));
});
var learningDeliveryPeriodisedValues = learners.SelectMany(l => l.LearningDeliveries.Select(ld =>
new FundModelLearningDeliveryPeriodisedValue <List <LearningDeliveryPeriodisedValue> >(fm81Global.UKPRN, l.LearnRefNumber, ld.AimSeqNumber.Value, ld.LearningDeliveryPeriodisedValues)));
cache.AddRange(BuildLearningDeliveryPeriods(learningDeliveryPeriodisedValues));
learningDeliveryPeriodisedValues.NullSafeForEach(ldpv => ldpv.LearningDeliveryPeriodisedValue.NullSafeForEach(learningDeliveryPeriodisedValue => cache.Add(BuildLearningDeliveryPeriodisedValue(learningDeliveryPeriodisedValue, ukprn, ldpv.AimSeqNumber, ldpv.LearnRefNumber))));
}
public async Task ExportAsync(IDataStoreCache dataStoreCache, OleDbConnection connection, string exportPath,
CancellationToken cancellationToken)
{
await ExportTableAsync <ESF_global, ESFglobalClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <ESF_Learner, ESFLearnerClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <ESF_LearningDelivery, ESFLearningDeliveryClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <ESF_LearningDelivery, ESFLearningDeliveryClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <ESF_LearningDeliveryDeliverable, ESFLearningDeliveryDeliverableClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <ESF_LearningDeliveryDeliverable_Period, ESFLearningDeliveryDeliverablePeriodClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <ESF_LearningDeliveryDeliverable_PeriodisedValue, ESFLearningDeliveryDeliverablePeriodisedValuesClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <ESF_DPOutcome, DefaultTableClassMap <ESF_DPOutcome> >(exportPath, dataStoreCache, connection, cancellationToken);
}
public async Task PersistFM36DataAsync(IDataStoreCache dataStoreCache, SqlConnection sqlConnection, SqlTransaction sqlTransaction, CancellationToken cancellationToken)
{
await _bulkInsert.Insert(TableNameConstants.FM36_global, dataStoreCache.Get <AEC_global>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.FM36_Learner, dataStoreCache.Get <AEC_Learner>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.FM36_LearningDelivery, dataStoreCache.Get <AEC_LearningDelivery>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.FM36_LearningDelivery_Period, dataStoreCache.Get <AEC_LearningDelivery_Period>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.FM36_LearningDelivery_PeriodisedValues, dataStoreCache.Get <AEC_LearningDelivery_PeriodisedValue>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.FM36_LearningDelivery_PeriodisedTextValues, dataStoreCache.Get <AEC_LearningDelivery_PeriodisedTextValue>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.FM36_PriceEpisodes, dataStoreCache.Get <AEC_ApprenticeshipPriceEpisode>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.FM36_PriceEpisode_Period, dataStoreCache.Get <AEC_ApprenticeshipPriceEpisode_Period>(), sqlConnection, sqlTransaction, cancellationToken);
await _bulkInsert.Insert(TableNameConstants.FM36_PriceEpisode_PeriodisedValues, dataStoreCache.Get <AEC_ApprenticeshipPriceEpisode_PeriodisedValue>(), sqlConnection, sqlTransaction, cancellationToken);
}
public async Task WriteFM36HistoryAsync(IDataStoreContext dataStoreContext, IDataStoreCache cache, CancellationToken cancellationToken)
{
using (SqlConnection fm36HistoryConnection = new SqlConnection(dataStoreContext.AppEarnHistoryDatabaseConnectionString))
{
await fm36HistoryConnection.OpenAsync(cancellationToken);
cancellationToken.ThrowIfCancellationRequested();
_logger.LogDebug("Starting FM36 History Transaction");
using (SqlTransaction fm36HistoryTransaction = fm36HistoryConnection.BeginTransaction())
{
try
{
await _dataStorePersistenceService.ClearFm36HistoryDataAsync(dataStoreContext, fm36HistoryConnection, fm36HistoryTransaction, cancellationToken);
_logger.LogDebug("FM36 History Clean Up successful");
await _persistenceService.PersistFM36HistoryDataAsync(cache, fm36HistoryConnection, fm36HistoryTransaction, cancellationToken);
_logger.LogDebug("FM36 History persistence complete");
fm36HistoryTransaction.Commit();
}
catch (Exception ex)
{
_logger.LogDebug($"FM36 Transaction failed attempting to rollback - {ex.Message}");
fm36HistoryTransaction.Rollback();
_logger.LogDebug("FM36 Transaction successfully rolled back");
throw;
}
_logger.LogDebug("FM36 Transaction complete");
}
}
}
public async Task ExportAsync(IDataStoreCache dataStoreCache, OleDbConnection connection, string exportPath,
CancellationToken cancellationToken)
{
await ExportTableAsync <AEC_global, AECglobalClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <AEC_Learner, AECLearnerClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <AEC_LearningDelivery, AECLearningDeliveryClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <AEC_LearningDelivery_Period, AECLearningDeliveryPeriodClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <AEC_LearningDelivery_PeriodisedTextValue, AECLearningDeliveryPeriodisedTextValuesClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <AEC_LearningDelivery_PeriodisedValue, AECLearningDeliveryPeriodisedValuesClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <AEC_ApprenticeshipPriceEpisode, AECApprenticeshipPriceEpisodeClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <AEC_ApprenticeshipPriceEpisode_Period, AECApprenticeshipPriceEpisodePeriodClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <AEC_ApprenticeshipPriceEpisode_PeriodisedValue, AECApprenticeshipPriceEpisodePeriodisedValueClassMap>(exportPath, dataStoreCache, connection, cancellationToken);
await ExportTableAsync <AEC_HistoricEarningOutput, DefaultTableClassMap <AEC_HistoricEarningOutput> >(exportPath, dataStoreCache, connection, cancellationToken);
}
public async Task PersistProcessingInformationDataAsync(IDataStoreCache dataStoreCache, SqlConnection sqlConnection, SqlTransaction sqlTransaction, CancellationToken cancellationToken)
{
long fileDetailsId;
var fileDetails = dataStoreCache.Get <FileDetail>().Single();
SendTelemetry(fileDetails);
using (SqlCommand sqlCommand = new SqlCommand(BuildInsertFileDetailsSql(), sqlConnection, sqlTransaction))
{
sqlCommand.Parameters.AddWithNullableValue("@UKPRN", fileDetails.UKPRN);
sqlCommand.Parameters.AddWithNullableValue("@FileName", fileDetails.Filename);
sqlCommand.Parameters.AddWithNullableValue("@FileSizeKb", fileDetails.FileSizeKb);
sqlCommand.Parameters.AddWithNullableValue("@TotalLearnersSubmitted", fileDetails.TotalLearnersSubmitted);
sqlCommand.Parameters.AddWithNullableValue("@TotalValidLearnersSubmitted", fileDetails.TotalValidLearnersSubmitted);
sqlCommand.Parameters.AddWithNullableValue("@TotalInvalidLearnersSubmitted", fileDetails.TotalInvalidLearnersSubmitted);
sqlCommand.Parameters.AddWithNullableValue("@TotalErrorCount", fileDetails.TotalErrorCount);
sqlCommand.Parameters.AddWithNullableValue("@TotalWarningCount", fileDetails.TotalWarningCount);
sqlCommand.Parameters.AddWithNullableValue("@SubmittedTime", fileDetails.SubmittedTime);
sqlCommand.Parameters.AddWithNullableValue("@Success", fileDetails.Success);
sqlCommand.Parameters.AddWithNullableValue("@OrgName", fileDetails.OrgName);
sqlCommand.Parameters.AddWithNullableValue("@OrgVersion", fileDetails.OrgVersion);
sqlCommand.Parameters.AddWithNullableValue("@LarsVersion", fileDetails.LarsVersion);
sqlCommand.Parameters.AddWithNullableValue("@EmployersVersion", fileDetails.EmployersVersion);
sqlCommand.Parameters.AddWithNullableValue("@PostcodesVersion", fileDetails.PostcodesVersion);
sqlCommand.Parameters.AddWithNullableValue("@CampusIdentifierVersion", fileDetails.CampusIdentifierVersion);
sqlCommand.Parameters.AddWithNullableValue("@EasUploadDateTime", fileDetails.EasUploadDateTime);
fileDetailsId = (long)await sqlCommand.ExecuteScalarAsync(cancellationToken);
}
using (SqlCommand sqlCommand = new SqlCommand(BuildInsertProcessingDataSql(fileDetailsId, dataStoreCache.Get <ProcessingData>().Single()), sqlConnection, sqlTransaction))
{
await sqlCommand.ExecuteNonQueryAsync(cancellationToken);
}
}
