private Workbook GetWorkbookReport( Workbook workbook, Worksheet sheet, FundingSummaryHeaderModel fundingSummaryHeaderModel, IEnumerable <FundingSummaryModel> fundingSummaryModels, FundingSummaryFooterModel fundingSummaryFooterModel) { WriteExcelRecords(sheet, new FundingSummaryHeaderMapper(), new List <FundingSummaryHeaderModel> { fundingSummaryHeaderModel }, _cellStyles[5], _cellStyles[5], true); foreach (var fundingSummaryModel in fundingSummaryModels) { if (string.IsNullOrEmpty(fundingSummaryModel.Title)) { WriteBlankRow(sheet); continue; } CellStyle excelHeaderStyle = _excelStyleProvider.GetCellStyle(_cellStyles, fundingSummaryModel.ExcelHeaderStyle); if (fundingSummaryModel.HeaderType == HeaderType.TitleOnly) { WriteTitleRecord(sheet, fundingSummaryModel.Title, excelHeaderStyle, _reportWidth); continue; } if (fundingSummaryModel.HeaderType == HeaderType.All) { _fundingSummaryMapper.MemberMaps.Single(x => x.Data.Index == 0).Name(fundingSummaryModel.Title); _cachedHeaders[0] = fundingSummaryModel.Title; WriteRecordsFromArray(sheet, _fundingSummaryMapper, _cachedHeaders, excelHeaderStyle); continue; } CellStyle excelRecordStyle = _excelStyleProvider.GetCellStyle(_cellStyles, fundingSummaryModel.ExcelRecordStyle); WriteExcelRecordsFromModelProperty(sheet, _fundingSummaryMapper, _cachedModelProperties, fundingSummaryModel, excelRecordStyle); } WriteExcelRecords(sheet, new FundingSummaryFooterMapper(), new List <FundingSummaryFooterModel> { fundingSummaryFooterModel }, _cellStyles[5], _cellStyles[5], true); return(workbook); }
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); }
private Workbook GetWorkbookReport( Workbook workbook, Worksheet sheet, FundingSummaryHeaderModel fundingSummaryHeaderModel, IEnumerable <FundingSummaryModel> fundingSummaryModels, FundingSummaryFooterModel fundingSummaryFooterModel) { { WriteExcelRecords(sheet, new FundingSummaryHeaderMapper(), new List <FundingSummaryHeaderModel> { fundingSummaryHeaderModel }, _cellStyles[7], _cellStyles[7], true); var firstFutureColumn = GetFirstFutureColumn(); int lastOperatedRow; // number of columns minus number of static columns (3) var endColumn = _cachedHeaders.Count() - 3; foreach (var fundingSummaryModel in fundingSummaryModels) { if (string.IsNullOrEmpty(fundingSummaryModel.Title)) { WriteBlankRow(sheet); continue; } CellStyle excelHeaderStyle = _excelStyleProvider.GetCellStyle(_cellStyles, fundingSummaryModel.ExcelHeaderStyle); if (fundingSummaryModel.HeaderType == HeaderType.TitleOnly) { WriteTitleRecord(sheet, fundingSummaryModel.Title, excelHeaderStyle, _reportWidth); continue; } if (fundingSummaryModel.HeaderType == HeaderType.All) { // Align data to the Right excelHeaderStyle.Style.HorizontalAlignment = TextAlignmentType.Right; excelHeaderStyle.StyleFlag.HorizontalAlignment = true; _fundingSummaryMapper.MemberMaps.Single(x => x.Data.Index == 0).Name(fundingSummaryModel.Title); _cachedHeaders[0] = fundingSummaryModel.Title; // this line is the month/year header WriteRecordsFromArray(sheet, _fundingSummaryMapper, _cachedHeaders, excelHeaderStyle); lastOperatedRow = GetCurrentRow(sheet) - 1; // ItaliciseFutureData(sheet, firstFutureColumn, lastOperatedRow, endColumn); continue; } CellStyle excelRecordStyle = _excelStyleProvider.GetCellStyle(_cellStyles, fundingSummaryModel.ExcelRecordStyle); // Align data to the Right excelRecordStyle.Style.HorizontalAlignment = TextAlignmentType.Right; excelRecordStyle.StyleFlag.HorizontalAlignment = true; // this line is subtotals below the month/ year header WriteExcelRecordsFromModelProperty(sheet, _fundingSummaryMapper, _cachedModelProperties, fundingSummaryModel, excelRecordStyle); lastOperatedRow = GetCurrentRow(sheet) - 1; // ItaliciseFutureData(sheet, firstFutureColumn, lastOperatedRow, endColumn); } for (int i = 0; i < workbook.Worksheets.Count; i++) { AlignWorkSheetColumnData(workbook.Worksheets[i], 0, TextAlignmentType.Left); } WriteExcelRecords(sheet, new FundingSummaryFooterMapper(), new List <FundingSummaryFooterModel> { fundingSummaryFooterModel }, _cellStyles[7], _cellStyles[7], true); return(workbook); } }