public async Task <IDictionary <string, IEnumerable <SupplementaryDataYearlyModel> > > GetSupplementaryDataAsync(int endYear, IEnumerable <SourceFileModel> sourceFiles, CancellationToken cancellationToken) { var suppData = await _supplementaryDataService.GetSupplementaryData(endYear, sourceFiles, cancellationToken); return(suppData); }
public async Task GenerateReport( SupplementaryDataWrapper supplementaryDataWrapper, SourceFileModel sourceFile, ZipArchive archive, CancellationToken cancellationToken) { var ukPrn = Convert.ToInt32(sourceFile.UKPRN); var sourceFiles = await _supplementaryDataService.GetImportFiles(sourceFile.UKPRN, cancellationToken); var supplementaryData = await _supplementaryDataService.GetSupplementaryData(sourceFiles, cancellationToken); var ilrYearlyFileData = await _ilrService.GetIlrFileDetails(ukPrn, cancellationToken); var fm70YearlyData = (await _ilrService.GetYearlyIlrData(ukPrn, cancellationToken)).ToList(); FundingSummaryHeaderModel fundingSummaryHeaderModel = PopulateReportHeader(sourceFile, ilrYearlyFileData, ukPrn, cancellationToken); var workbook = new Workbook(); workbook.Worksheets.Clear(); foreach (var file in sourceFiles) { var fundingYear = FileNameHelper.GetFundingYearFromFileName(file.FileName); var thisYearsFm70Data = fm70YearlyData.Where(d => d.FundingYear == fundingYear); var fundingSummaryModels = PopulateReportData(thisYearsFm70Data, supplementaryData[file.SourceFileId]).ToList(); ApplyFundingYearToEmptyFundingYears(fundingSummaryModels, fundingYear); FundingSummaryFooterModel fundingSummaryFooterModel = 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.Length); _reportWidth = valCount + rowOfData.Totals.Count + 2; foreach (FundingSummaryReportYearlyValueModel fundingSummaryReportYearlyValueModel in rowOfData.YearlyValues) { yearAndDataLengthModels.Add(new YearAndDataLengthModel( fundingSummaryReportYearlyValueModel.FundingYear, fundingSummaryReportYearlyValueModel.Values.Length)); } } _cachedHeaders = GetHeaderEntries(yearAndDataLengthModels); _cellStyles = _excelStyleProvider.GetFundingSummaryStyles(workbook); Worksheet sheet = workbook.Worksheets.Add(file.ConRefNumber); workbook = GetWorkbookReport(workbook, sheet, fundingSummaryHeaderModel, fundingSummaryModels, fundingSummaryFooterModel); ApplyAdditionalFormatting(workbook, rowOfData); } string externalFileName = GetExternalFilename(sourceFile.UKPRN, sourceFile.JobId ?? 0, sourceFile.SuppliedDate ?? DateTime.MinValue); string fileName = GetFilename(sourceFile.UKPRN, sourceFile.JobId ?? 0, sourceFile.SuppliedDate ?? DateTime.MinValue); using (var ms = new MemoryStream()) { workbook.Save(ms, SaveFormat.Xlsx); await _storage.SaveAsync($"{externalFileName}.xlsx", ms, cancellationToken); await WriteZipEntry(archive, $"{fileName}.xlsx", ms, cancellationToken); } }
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); }