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);
}