public async Task <IList <SourceFileModel> > GetImportFilesAsync(int ukPrn, CancellationToken cancellationToken)
        {
            var importFiles = await _supplementaryDataService.GetImportFiles(ukPrn.ToString(), cancellationToken);

            return(importFiles);
        }
        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);
            }
        }
示例#3
0
        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);
        }