public void CreateFileList(ISourceFileModel <ILineOfCode> src)
{
src.ListOfCode = new ObservableCollection <ILineOfCode>();
string temp = src.SourceFile;
string[] lines = temp.Split(
new[] { Environment.NewLine },
StringSplitOptions.None
);
for (int i = 0; i < lines.Length; i++)
{
Match m = Regex.Match(lines[i], pattern);
var LoC = new LineOfCode(lines[i]);
if (m.Success == true)
{
//ins array rein :D
string com = m.Value;
string adress = m.Value;
com = com.Remove(0, 5); //erste 4 Zeichen (sogenannter Index looool) inklusive Leerzeichen
adress = adress.Remove(4); // Ab Index 4 sind die Zeichen unbrauchbar
short comAsInt = short.Parse(com, NumberStyles.HexNumber);
short adressAsInt = short.Parse(adress, NumberStyles.HexNumber);
LoC.ProgramCode = comAsInt;
LoC.CommandIndex = adressAsInt;
}
src.ListOfCode.Add(LoC);
}
}
public async Task <string> GenerateReport(
IEsfJobContext esfJobContext,
ISourceFileModel sourceFile,
SupplementaryDataWrapper wrapper,
CancellationToken cancellationToken)
{
var fundingSummaryReportModels = await _modelBuilder.Build(esfJobContext, cancellationToken);
string fileName = GetExternalFilename(esfJobContext.UkPrn, ReportName, esfJobContext.JobId, esfJobContext.SubmissionDateTimeUtc, _excelExtension);
using (var workbook = _excelFileService.NewWorkbook())
{
workbook.Worksheets.Clear();
foreach (var tab in fundingSummaryReportModels)
{
var worksheet = _excelFileService.GetWorksheetFromWorkbook(workbook, tab.TabName);
await _renderService.RenderAsync(esfJobContext, tab, worksheet);
}
await _excelFileService.SaveWorkbookAsync(workbook, fileName, esfJobContext.BlobContainerName, cancellationToken);
}
return(fileName);
}
public async Task <SupplementaryDataWrapper> GetFile(
IEsfJobContext esfJobContext,
ISourceFileModel sourceFileModel,
CancellationToken cancellationToken)
{
SupplementaryDataWrapper wrapper = new SupplementaryDataWrapper();
ICollection <SupplementaryDataLooseModel> esfRecords = new List <SupplementaryDataLooseModel>();
IList <ValidationErrorModel> errors = new List <ValidationErrorModel>();
try
{
esfRecords = await _eSFProviderService.GetESFRecordsFromFile(esfJobContext, cancellationToken);
if (esfRecords == null || !esfRecords.Any())
{
_logger.LogInfo("No ESF records to process");
}
}
catch (ValidationException ex)
{
_logger.LogError($"The file format is incorrect, key: {sourceFileModel.FileName}", ex);
errors.Add(new ValidationErrorModel
{
RuleName = "Fileformat_01",
ErrorMessage = "The file format is incorrect. Please check the field headers are as per the Guidance document.",
IsWarning = false
});
}
wrapper.SupplementaryDataLooseModels = esfRecords;
wrapper.ValidErrorModels = errors;
return(wrapper);
}
public async Task <SupplementaryDataWrapper> RunFileValidators(
ISourceFileModel sourceFileModel,
SupplementaryDataWrapper wrapper)
{
foreach (var model in wrapper.SupplementaryDataLooseModels)
{
foreach (var validator in _validators)
{
if (await validator.IsValid(sourceFileModel, model))
{
continue;
}
wrapper.ValidErrorModels.Add(new ValidationErrorModel
{
RuleName = validator.ErrorName,
ErrorMessage = validator.ErrorMessage,
IsWarning = false
});
return(wrapper);
}
}
return(wrapper);
}
public async Task ProduceReports(
IEsfJobContext esfJobContext,
SupplementaryDataWrapper wrapper,
ISourceFileModel sourceFile,
CancellationToken cancellationToken)
{
await ProduceReports(esfJobContext, wrapper, sourceFile, cancellationToken, true);
}
public async Task Execute(
IEsfJobContext esfJobContext,
ISourceFileModel sourceFile,
SupplementaryDataWrapper wrapper,
CancellationToken cancellationToken)
{
await _controller.ValidateData(wrapper, sourceFile, cancellationToken);
}
public ApplicationService(IMemoryService memory, ISourceFileModel <ILineOfCode> srcModel, OperationHelpers operationHelpers, BitOperations bitOperations, ByteOperations byteOperations, LiteralControlOperations literalControlOperations)
{
this._memory = memory;
this._srcModel = srcModel;
_bitOperations = bitOperations;
_byteOperations = byteOperations;
_literalControlOperations = literalControlOperations;
_operationHelpers = operationHelpers;
}
public async Task <bool> StoreData(ISourceFileModel sourceFile, SupplementaryDataWrapper wrapper, CancellationToken cancellationToken)
{
bool successfullyCommitted = false;
using (SqlConnection connection = new SqlConnection(_dbConfiguration.ESFR2ConnectionString))
{
SqlTransaction transaction = null;
try
{
await connection.OpenAsync(cancellationToken);
cancellationToken.ThrowIfCancellationRequested();
transaction = connection.BeginTransaction();
var ukPrn = Convert.ToInt32(sourceFile.UKPRN);
await _storeClear.ClearAsync(ukPrn, sourceFile.ConRefNumber, connection, transaction, cancellationToken);
int fileId = await _storeFileDetails.StoreAsync(connection, transaction, sourceFile, cancellationToken);
await _storeValidation.StoreAsync(connection, transaction, fileId, wrapper.ValidErrorModels, cancellationToken);
_logger.LogDebug($"{wrapper.SupplementaryDataModels.Count} suppData rows to save.");
await _store.StoreAsync(connection, transaction, fileId, wrapper.SupplementaryDataModels, cancellationToken);
await _storeEsfUnitCost.StoreAsync(connection, transaction, wrapper.SupplementaryDataModels, cancellationToken);
transaction.Commit();
successfullyCommitted = true;
}
catch (Exception ex)
{
_logger.LogError("Failed to persist to DEDs", ex);
throw;
}
finally
{
if (!successfullyCommitted)
{
try
{
transaction?.Rollback();
}
catch (Exception ex2)
{
_logger.LogError("Failed to rollback DEDs persist transaction", ex2);
throw;
}
}
}
}
return(successfullyCommitted);
}
void IFileService.Reset(ISourceFileModel <ILineOfCode> src)
{
if (src.ListOfCode != null)
{
for (int i = 0; i < src.ListOfCode.Count; i++)
{
src.ListOfCode[i].IsDebug = false;
src.ListOfCode[i].IsExecuted = false;
}
}
}
public async Task Execute(
IEsfJobContext esfJobContext,
ISourceFileModel sourceFile,
SupplementaryDataWrapper supplementaryDataWrapper,
CancellationToken cancellationToken)
{
var success = await _storageController.StoreData(sourceFile, supplementaryDataWrapper, cancellationToken);
if (!success)
{
_logger.LogError("Failed to save data to the data store.");
}
}
public async Task <bool> StoreValidationOnly(
ISourceFileModel sourceFile,
SupplementaryDataWrapper wrapper,
CancellationToken cancellationToken)
{
bool successfullyCommitted = false;
using (SqlConnection connection =
new SqlConnection(_dbConfiguration.ESFR2ConnectionString))
{
SqlTransaction transaction = null;
try
{
await connection.OpenAsync(cancellationToken);
cancellationToken.ThrowIfCancellationRequested();
transaction = connection.BeginTransaction();
int fileId = await _storeFileDetails.StoreAsync(connection, transaction, sourceFile, cancellationToken);
await _storeValidation.StoreAsync(connection, transaction, fileId, wrapper.ValidErrorModels, cancellationToken);
transaction.Commit();
successfullyCommitted = true;
}
catch (Exception ex)
{
_logger.LogError("Failed to persist to DEDs", ex);
throw;
}
finally
{
if (!successfullyCommitted)
{
try
{
transaction?.Rollback();
}
catch (Exception ex2)
{
_logger.LogError("Failed to rollback DEDs persist transaction", ex2);
throw;
}
}
}
}
return(successfullyCommitted);
}
public async Task <string> GenerateReport(
IEsfJobContext esfJobContext,
ISourceFileModel sourceFile,
SupplementaryDataWrapper wrapper,
CancellationToken cancellationToken)
{
var report = GetValidationReport(wrapper.SupplementaryDataModels, wrapper.ValidErrorModels);
var externalFilename = GetExternalFilename(sourceFile.UKPRN, sourceFile.JobId ?? 0, sourceFile.SuppliedDate ?? DateTime.MinValue, ReportExtension);
await SaveJson(esfJobContext, externalFilename, report, cancellationToken);
return(externalFilename);
}
public async Task <string> GenerateReport(
IEsfJobContext esfJobContext,
ISourceFileModel sourceFile,
SupplementaryDataWrapper wrapper,
CancellationToken cancellationToken)
{
ReportFileName = $"{sourceFile.ConRefNumber} " + ReportFileName;
string externalFileName = GetExternalFilename(sourceFile.UKPRN, sourceFile.JobId ?? 0, sourceFile.SuppliedDate ?? DateTime.MinValue, _reportExtension);
await WriteCsv(esfJobContext, externalFileName, wrapper.ValidErrorModels.OrderBy(s => s.IsWarning).ThenBy(s => s.RuleName), cancellationToken);
return(externalFileName);
}
public async Task ExecuteTasks(
IEsfJobContext esfJobContext,
ISourceFileModel sourceFileModel,
SupplementaryDataWrapper supplementaryDataWrapper,
CancellationToken cancellationToken)
{
var tasks = esfJobContext.Tasks;
foreach (var task in tasks)
{
cancellationToken.ThrowIfCancellationRequested();
await HandleTask(esfJobContext, supplementaryDataWrapper, task, sourceFileModel, cancellationToken);
}
}
public async Task <string> GenerateReport(
IEsfJobContext esfJobContext,
ISourceFileModel sourceFile,
SupplementaryDataWrapper wrapper,
CancellationToken cancellationToken)
{
var reportModels = BuildModels(wrapper);
ReportFileName = $"{sourceFile.ConRefNumber} " + ReportFileName;
string externalFileName = GetExternalFilename(esfJobContext.UkPrn, esfJobContext.JobId, sourceFile.SuppliedDate ?? DateTime.MinValue, _reportExtension);
await WriteCsv(esfJobContext, externalFileName, reportModels, cancellationToken);
return(externalFileName);
}
public async Task <bool> IsValid(ISourceFileModel sourceFileModel, SupplementaryDataLooseModel model)
{
if (string.IsNullOrWhiteSpace(model.ConRefNumber))
{
return(true);
}
var numericString = model.ConRefNumber.Replace(ESFConstants.ConRefNumberPrefix, string.Empty);
if (!int.TryParse(numericString, out var contractNumber))
{
return(true);
}
return(contractNumber >= ESFConstants.ESFRound2StartConRefNumber);
}
public async Task <string> GenerateReport(
IEsfJobContext esfJobContext,
ISourceFileModel sourceFile,
SupplementaryDataWrapper wrapper,
CancellationToken cancellationToken)
{
var externalFileName = GetExternalFilename(esfJobContext.UkPrn, esfJobContext.JobId, sourceFile?.SuppliedDate ?? DateTime.MinValue, ReportExtension);
cancellationToken.ThrowIfCancellationRequested();
var ukPrn = esfJobContext.UkPrn;
var reportModels = await GetModels(ukPrn, esfJobContext.CollectionYear, cancellationToken);
await WriteCsv(esfJobContext, externalFileName, reportModels, cancellationToken);
return(externalFileName);
}
public async Task <string> GenerateReport(IEsfJobContext esfJobContext, ISourceFileModel sourceFile, SupplementaryDataWrapper wrapper, CancellationToken cancellationToken)
{
var externalFileName = GetExternalFilename(esfJobContext.UkPrn, esfJobContext.JobId, sourceFile?.SuppliedDate ?? DateTime.MinValue, ReportNameConstants.CsvExtension);
var learningDeliveries = _aimAndDeliverableDataProvider.GetLearningDeliveriesAsync(esfJobContext.UkPrn, cancellationToken);
var dpOutcomes = _aimAndDeliverableDataProvider.GetDpOutcomesAsync(esfJobContext.UkPrn, cancellationToken);
var deliverablePeriods = _aimAndDeliverableDataProvider.GetDeliverablePeriodsAsync(esfJobContext.UkPrn, cancellationToken);
var esfDpOutcomes = _aimAndDeliverableDataProvider.GetEsfDpOutcomesAsync(esfJobContext.UkPrn, cancellationToken);
var fcsDeliverableCodeMappings = _aimAndDeliverableDataProvider.GetFcsDeliverableCodeMappingsAsync(cancellationToken);
await Task.WhenAll(learningDeliveries, dpOutcomes, deliverablePeriods, esfDpOutcomes, fcsDeliverableCodeMappings);
var learnAimRefs = new HashSet <string>(learningDeliveries.Result.Select(l => l.LearnAimRef), StringComparer.OrdinalIgnoreCase);
var larsLearningDeliveries = await _aimAndDeliverableDataProvider.GetLarsLearningDeliveriesAsync(learnAimRefs, cancellationToken);
var reportModels = _aimAndDeliverableModelBuilder.Build(esfJobContext, learningDeliveries.Result, dpOutcomes.Result, deliverablePeriods.Result, esfDpOutcomes.Result, larsLearningDeliveries, fcsDeliverableCodeMappings.Result);
await WriteCsv(esfJobContext, externalFileName, reportModels, cancellationToken, _aimAndDeliverableMapper);
return(externalFileName);
}
private async Task HandleTask(
IEsfJobContext esfJobContext,
SupplementaryDataWrapper wrapper,
string task,
ISourceFileModel sourceFile,
CancellationToken cancellationToken)
{
var orderedHandlers = _taskHandlers.OrderBy(t => t.Order);
foreach (var handler in orderedHandlers)
{
if (!handler.IsMatch(task))
{
continue;
}
await handler.Execute(esfJobContext, sourceFile, wrapper, cancellationToken);
break;
}
}
private async Task ProduceReports(
IEsfJobContext esfJobContext,
SupplementaryDataWrapper wrapper,
ISourceFileModel sourceFile,
CancellationToken cancellationToken,
bool passedFileValidation)
{
_logger.LogInfo("ESF Reporting service called");
var reportNames = new List <string>();
try
{
if (!string.IsNullOrWhiteSpace(sourceFile?.FileName))
{
foreach (var validationReport in _validationReports)
{
reportNames.Add(await validationReport.GenerateReport(esfJobContext, sourceFile, wrapper, cancellationToken));
}
}
if (passedFileValidation)
{
var reportsToRun = _esfReports.Where(r => esfJobContext.Tasks.Contains(r.TaskName, StringComparer.OrdinalIgnoreCase));
foreach (var report in reportsToRun)
{
reportNames.Add(await report.GenerateReport(esfJobContext, sourceFile, wrapper, cancellationToken));
}
}
}
catch (Exception ex)
{
_logger.LogError(ex.Message, ex);
throw;
}
var zipFileName = $"{esfJobContext.UkPrn}/{esfJobContext.JobId}{ReportNameConstants.ZipName}";
await _zipService.CreateZipAsync(zipFileName, reportNames, esfJobContext.BlobContainerName, cancellationToken);
}
private async Task PrePopulateReferenceDataCache(
SupplementaryDataWrapper wrapper,
ISourceFileModel sourceFile,
CancellationToken cancellationToken)
{
var allUlns = wrapper.SupplementaryDataModels.Select(m => m.ULN).ToList();
_populationService.PrePopulateUlnCache(allUlns, cancellationToken);
var ukPrn = Convert.ToInt32(sourceFile.UKPRN);
_populationService.PrePopulateContractAllocations(ukPrn, wrapper.SupplementaryDataModels, cancellationToken);
await _populationService.PrePopulateContractDeliverableUnitCosts(ukPrn, cancellationToken);
var deliverableCodes = wrapper.SupplementaryDataModels.Select(m => m.DeliverableCode).ToList();
_populationService.PrePopulateContractDeliverableCodeMappings(deliverableCodes, cancellationToken);
var learnAimRefs = wrapper.SupplementaryDataModels.Select(m => m.LearnAimRef).ToList();
await _populationService.PrePopulateLarsLearningDeliveries(learnAimRefs, cancellationToken);
await _populationService.PrePopulateValidationErrorMessages(cancellationToken);
}
public async Task <int> StoreAsync(SqlConnection sqlConnection, SqlTransaction sqlTransaction, ISourceFileModel sourceFile, CancellationToken cancellationToken)
{
string insertFileDetails =
"INSERT INTO [dbo].[SourceFile] ([ConRefNumber], [UKPRN], [Filename], [DateTime], [FilePreparationDate]) " +
"VALUES (@ConRefNumber, @UKPRN, @FileName, @SuppliedDate, @PreparationDate); SELECT CAST(SCOPE_IDENTITY() as int)";
cancellationToken.ThrowIfCancellationRequested();
var parameters = new DynamicParameters();
parameters.Add("@ConRefNumber", sourceFile.ConRefNumber);
parameters.Add("@UKPRN", sourceFile.UKPRN);
parameters.Add("@FileName", sourceFile.FileName);
parameters.Add("@SuppliedDate", sourceFile.SuppliedDate?.ToString("yyyy-MM-dd HH:mm:ss"));
parameters.Add("@PreparationDate", sourceFile.PreparationDate.ToString("yyyy-MM-dd HH:mm:ss"));
return(await _dataStoreQueryExecutionService.ExecuteSqlWithParameterAsync <int>(sqlConnection, sqlTransaction, parameters, insertFileDetails, cancellationToken));
}
public async Task <bool> IsValid(ISourceFileModel sourceFileModel, SupplementaryDataLooseModel model)
{
string[] filenameParts = sourceFileModel.FileName.SplitFileName(_filenameExtension);
return(filenameParts[2] == model.ConRefNumber);
}
public async Task ValidateData(
SupplementaryDataWrapper wrapper,
ISourceFileModel sourceFile,
CancellationToken cancellationToken)
{
try
{
foreach (var looseModel in wrapper.SupplementaryDataLooseModels)
{
if (_looseValidatorCommand.Execute(looseModel))
{
continue;
}
foreach (var error in _looseValidatorCommand.Errors)
{
wrapper.ValidErrorModels.Add(error);
}
if (!_looseValidatorCommand.RejectFile)
{
continue;
}
return;
}
wrapper.SupplementaryDataLooseModels = FilterOutInvalidLooseRows(wrapper);
wrapper.SupplementaryDataModels = wrapper.SupplementaryDataLooseModels
.Select(m => _mapper.GetSupplementaryDataModelFromLooseModel(m)).ToList();
await PrePopulateReferenceDataCache(wrapper, sourceFile, cancellationToken);
foreach (var command in _validatorCommands)
{
if (command is ICrossRecordCommand)
{
((ICrossRecordCommand)command).AllRecords = wrapper.SupplementaryDataModels;
}
foreach (var model in wrapper.SupplementaryDataModels)
{
if (command.IsValid(model))
{
continue;
}
foreach (var error in command.Errors)
{
wrapper.ValidErrorModels.Add(error);
}
if (!command.RejectFile)
{
continue;
}
RejectFile = true;
return;
}
}
wrapper.SupplementaryDataModels = FilterOutInvalidRows(wrapper);
}
catch (Exception ex)
{
_logger.LogError(ex.Message, ex);
throw;
}
}
public async Task <string> GenerateReport(
IEsfJobContext esfJobContext,
ISourceFileModel sourceFile,
SupplementaryDataWrapper wrapper,
CancellationToken cancellationToken)
{
var ukPrn = esfJobContext.UkPrn;
var conRefNumbers = await _referenceDataService.GetContractAllocationsForUkprn(ukPrn, cancellationToken);
if (!conRefNumbers.Any())
{
conRefNumbers = new List <string> {
NotApplicable
};
}
var collectionYear = Convert.ToInt32($"20{esfJobContext.CollectionYear.ToString().Substring(0, 2)}");
var sourceFiles = await _supplementaryDataService.GetImportFiles(esfJobContext.UkPrn.ToString(), cancellationToken);
_logger.LogDebug($"{sourceFiles.Count} esf files found for ukprn {ukPrn} and collection year 20{esfJobContext.CollectionYear.ToString().Substring(0, 2)}.");
var supplementaryData = await _supplementaryDataService.GetSupplementaryData(collectionYear, sourceFiles, cancellationToken);
var ilrYearlyFileData = (await _ilrService.GetIlrFileDetails(ukPrn, collectionYear, cancellationToken)).ToList();
var fm70YearlyData = (await _ilrService.GetYearlyIlrData(ukPrn, esfJobContext.CollectionName, collectionYear, esfJobContext.ReturnPeriod, cancellationToken)).ToList();
var workbook = new Workbook();
workbook.Worksheets.Clear();
foreach (var conRefNumber in conRefNumbers)
{
var file = sourceFiles.FirstOrDefault(sf => sf.ConRefNumber.CaseInsensitiveEquals(conRefNumber));
FundingSummaryHeaderModel fundingSummaryHeaderModel =
PopulateReportHeader(file, ilrYearlyFileData, ukPrn, conRefNumber, cancellationToken);
var fm70YearlyDataForConRef = new List <FM70PeriodisedValuesYearly>();
var supplementaryDataYearlyModels = new List <SupplementaryDataYearlyModel>();
supplementaryData.TryGetValue(conRefNumber, out var suppData);
foreach (var fm70Data in fm70YearlyData)
{
var periodisedValuesPerConRef = fm70Data.Fm70PeriodisedValues.Where(x => conRefNumber.CaseInsensitiveEquals(x.ConRefNumber)).ToList();
fm70YearlyDataForConRef.Add(new FM70PeriodisedValuesYearly()
{
Fm70PeriodisedValues = periodisedValuesPerConRef,
FundingYear = fm70Data.FundingYear
});
supplementaryDataYearlyModels.Add(new SupplementaryDataYearlyModel
{
FundingYear = fm70Data.FundingYear,
SupplementaryData = suppData?.FirstOrDefault(x => x.FundingYear == fm70Data.FundingYear)?.SupplementaryData ?? new List <SupplementaryDataModel>()
});
}
var fundingSummaryModels = PopulateReportData(collectionYear, fm70YearlyDataForConRef, supplementaryDataYearlyModels).ToList();
ReplaceConRefNumInTitle(fundingSummaryModels, conRefNumber);
FundingSummaryFooterModel fundingSummaryFooterModel = await PopulateReportFooter(cancellationToken);
FundingSummaryModel rowOfData = fundingSummaryModels.FirstOrDefault(x => x.DeliverableCode == "ST01" && x.YearlyValues.Any());
var yearAndDataLengthModels = new List <YearAndDataLengthModel>();
if (rowOfData != null)
{
int valCount = rowOfData.YearlyValues.Sum(x => x.Values.Count);
_reportWidth = valCount + rowOfData.Totals.Count + 2;
foreach (FundingSummaryReportYearlyValueModel fundingSummaryReportYearlyValueModel in
rowOfData.YearlyValues)
{
yearAndDataLengthModels.Add(new YearAndDataLengthModel(
fundingSummaryReportYearlyValueModel.FundingYear,
fundingSummaryReportYearlyValueModel.Values.Count));
}
}
_cachedHeaders = GetHeaderEntries(collectionYear, yearAndDataLengthModels);
_cellStyles = _excelStyleProvider.GetFundingSummaryStyles(workbook);
Worksheet sheet = workbook.Worksheets.Add(conRefNumber);
sheet.Cells.StandardWidth = 19;
sheet.Cells.Columns[0].Width = 63.93;
sheet.IsGridlinesVisible = false;
AddImageToReport(sheet);
workbook = GetWorkbookReport(workbook, sheet, fundingSummaryHeaderModel, fundingSummaryModels, fundingSummaryFooterModel);
}
string externalFileName = GetExternalFilename(ukPrn.ToString(), esfJobContext.JobId, sourceFile?.SuppliedDate ?? DateTime.MinValue, _excelExtension);
await WriteExcelFile(esfJobContext, externalFileName, workbook, cancellationToken);
return(externalFileName);
}
public async Task <bool> IsValid(ISourceFileModel sourceFileModel, SupplementaryDataLooseModel model)
{
var previousFiles = await _esfRepository.AllPreviousFilesForValidation(sourceFileModel.UKPRN, sourceFileModel.ConRefNumber, CancellationToken.None);
return(previousFiles?.All(f => f.FilePreparationDate <= sourceFileModel.PreparationDate) ?? true);
}