private void InsertHeaderFooter(Workbook workbook, FundingClaim1619HeaderModel headerModel, FundingClaim1619FooterModel footerModel)
{
PageSetup pageSetup = workbook.Worksheets[0].PageSetup;
pageSetup.SetHeader(0, "&14&\"Bold\"16-19 Funding Claim Report");
pageSetup.SetHeader(2, "&10&\"Bold\"OFFICIAL-SENSITIVE");
var cells = workbook.Worksheets[0].Cells;
cells[ProviderCellName].PutValue(headerModel.ProviderName);
cells[UKPrnCellName].PutValue(headerModel.Ukprn);
cells[ILRFileNameCellName].PutValue(headerModel.IlrFile);
cells[YearCellName].PutValue(headerModel.Year);
pageSetup.SetFooter(0, "&8" + footerModel.ReportGeneratedAt);
pageSetup.SetFooter(2, "&8Page &P of &N");
cells[ComponentSetVersionCellName].PutValue(footerModel.ComponentSetVersion);
cells[ApplicationVersionCellName].PutValue(footerModel.ApplicationVersion);
cells[FilePreparationCellName].PutValue(footerModel.FilePreparationDate);
cells[LarsDataCellName].PutValue(footerModel.LarsData);
cells[OrganisationDataCellName].PutValue(footerModel.OrganisationData);
cells[PostcodeDataCellName].PutValue(footerModel.PostcodeData);
cells[LargeEmployerDataCellName].PutValue(footerModel.LargeEmployerData);
cells[CofRemovalDataCellName].PutValue(footerModel.CofRemovalData);
}
private async Task <FundingClaim1619HeaderModel> GetHeaderAsync(IReportServiceContext reportServiceContext, Task <IMessage> ilrFileTask, Task <ILRSourceFileInfo> lastSubmittedIlrFileTask, Task <string> providerNameTask, CancellationToken cancellationToken, bool isFis)
{
var ilrFileName = reportServiceContext.OriginalFilename ?? reportServiceContext.Filename;
FundingClaim1619HeaderModel fundingClaim1619HeaderModel = new FundingClaim1619HeaderModel
{
ProviderName = providerNameTask.Result ?? "Unknown",
Ukprn = reportServiceContext.Ukprn,
IlrFile = ilrFileName,
Year = Constants.Year
};
return(fundingClaim1619HeaderModel);
}
public async Task GenerateReport(IReportServiceContext reportServiceContext, ZipArchive archive, bool isFis, CancellationToken cancellationToken)
{
Task <IMessage> ilrFileTask = _ilrProviderService.GetIlrFile(reportServiceContext, cancellationToken);
Task <FM25Global> fm25DataTask = _fm25ProviderService.GetFM25Data(reportServiceContext, cancellationToken);
Task <string> providerNameTask = _orgProviderService.GetProviderName(reportServiceContext, cancellationToken);
Task <decimal?> cofRemovalTask = _orgProviderService.GetCofRemoval(reportServiceContext, cancellationToken);
Task <ILRSourceFileInfo> lastSubmittedIlrFileTask = _ilrProviderService.GetLastSubmittedIlrFile(reportServiceContext, cancellationToken);
await Task.WhenAll(ilrFileTask, fm25DataTask, providerNameTask, cofRemovalTask, lastSubmittedIlrFileTask);
FundingClaim1619HeaderModel fundingSummaryHeaderModel = await GetHeaderAsync(reportServiceContext, ilrFileTask, lastSubmittedIlrFileTask, providerNameTask, cancellationToken, isFis);
FundingClaim1619FooterModel fundingSummaryFooterModel = await GetFooterAsync(ilrFileTask, lastSubmittedIlrFileTask, cancellationToken);
string[] applicableFundingLineTypes = _applicableFundingLineTypes.Values.SelectMany(x => x).ToArray();
// Funding factors are at provider level but are output by the funding calculation against every learner record.
// All learners in one provider will have the same funding factor value so the report can pull the minimum value
// of each factor from the funding calc output at provider level to populate the factors table.
FundingClaim1619FundingFactorModel fundingClaim1619FundingFactorModel = null;
Dictionary <string, List <FundingClaim1619Model> > fundLineAndRateBandData = new Dictionary <string, List <FundingClaim1619Model> >();
foreach (ILearner learner in ilrFileTask.Result.Learners)
{
FM25Learner learnerFm25Data =
fm25DataTask.Result?.Learners?.SingleOrDefault(l => string.Equals(l.LearnRefNumber, learner.LearnRefNumber, StringComparison.OrdinalIgnoreCase));
if (learnerFm25Data == null)
{
continue;
}
if (!ApplicableLearner(learnerFm25Data, applicableFundingLineTypes))
{
continue;
}
if (fundingClaim1619FundingFactorModel == null)
{
fundingClaim1619FundingFactorModel = new FundingClaim1619FundingFactorModel
{
AreaCostFact1618Hist = learnerFm25Data.AreaCostFact1618Hist.GetValueOrDefault(0).ToString("N5"),
ProgWeightHist = learnerFm25Data.ProgWeightHist.GetValueOrDefault(0).ToString("N5"),
PrvDisadvPropnHist = learnerFm25Data.PrvDisadvPropnHist.GetValueOrDefault(0).ToString("N5"),
PrvHistLrgProgPropn = learnerFm25Data.PrvHistLrgProgPropn.GetValueOrDefault(0).ToString("N5"),
PrvRetentFactHist = learnerFm25Data.PrvRetentFactHist.GetValueOrDefault(0).ToString("N5")
};
}
if (learner.LearningDeliveries.Any(ApplicableLearningDelivery))
{
GetFundLineAndBandRateData(learnerFm25Data, ref fundLineAndRateBandData);
}
}
// probably don't need this as this will be calculated automatically through the template
//FundingClaim1619Model subTotal = TotalFundLineAndBandRateData(ref fundLineAndRateBandData);
// probably don't need this as this will be calculated automatically through the template
//FundingClaim1619Model total = new FundingClaim1619Model
//{
// StudentNumber = subTotal.StudentNumber,
// TotalFunding = 0 // Todo
//};
if (cancellationToken.IsCancellationRequested)
{
return;
}
long jobId = reportServiceContext.JobId;
string ukPrn = reportServiceContext.Ukprn.ToString();
var externalFileName = GetExternalFilename(ukPrn, jobId, reportServiceContext.SubmissionDateTimeUtc);
var fileName = GetFilename(ukPrn, jobId, reportServiceContext.SubmissionDateTimeUtc);
var assembly = Assembly.GetExecutingAssembly();
string resourceName = assembly.GetManifestResourceNames().Single(str => str.EndsWith("1619FundingClaimReportTemplate.xlsx"));
var manifestResourceStream = assembly.GetManifestResourceStream(resourceName);
Workbook workbook = new Workbook(manifestResourceStream);
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
InsertHeaderFooter(workbook, fundingSummaryHeaderModel, fundingSummaryFooterModel);
PopulateAllocationValues(cells, fundingClaim1619FundingFactorModel);
PopulateMainData(cells, fundLineAndRateBandData);
PopulateCofRemoval(cells, cofRemovalTask.Result);
workbook.CalculateFormula();
using (MemoryStream ms = new MemoryStream())
{
workbook.Save(ms, SaveFormat.Xlsx);
await _streamableKeyValuePersistenceService.SaveAsync($"{externalFileName}.xlsx", ms, cancellationToken);
await WriteZipEntry(archive, $"{fileName}.xlsx", ms, cancellationToken);
}
}
