public void Render()
{
var workbook = new Workbook();
var currentPeriod = 12;
var fundingSummaryReport =
new FundingSummaryReportModel(
new Dictionary <string, string>(),
Enumerable.Range(1, 5)
.Select(l => (IFundingCategory) new FundingCategory("Funding Category Title", currentPeriod, Enumerable.Range(1, 10)
.Select(k => (IFundingSubCategory) new FundingSubCategory("Funding Sub Category Title", currentPeriod)
{
FundLineGroups = Enumerable.Range(1, 2)
.Select(i =>
{
return((IFundLineGroup) new FundLineGroup("FundLineGroup", currentPeriod, FundingDataSources.FM35, new string[] { }, null)
{
FundLines = Enumerable.Range(0, 5)
.Select(j => (IFundLine) new FundLine(currentPeriod, "Title", 1.1111m, 2.2222m, 3.3333m, 4.4444m, 5.5555m, 6.6666m, 7.7777m, 8.8888m, 9.9999m, 10.1010m, 11.1111m, 12.1212m))
.ToList()
});
}).ToList()
}).ToList()))
.ToList(),
new Dictionary <string, string>());
var worksheet = workbook.Worksheets[0];
NewService().Render(fundingSummaryReport, worksheet);
Directory.CreateDirectory("Output");
workbook.Save("Output/FundingSummaryReport.xlsx");
}
public async Task <FundingSummaryReportModel> BuildFundingSummaryReportModel(IReportServiceContext reportServiceContext, IPeriodisedValuesLookup periodisedValues, IDictionary <string, string> fcsContractAllocationFspCodeLookup, CancellationToken cancellationToken)
{
var noContract = "No Contract";
var carryInApprenticeshipBudget = fcsContractAllocationFspCodeLookup.GetValueOrDefault(FundingStreamPeriodCodeConstants.APPS1920, noContract);
var apprenticeshipEmployerOnApprenticeshipServiceLevy = fcsContractAllocationFspCodeLookup.GetValueOrDefault(FundingStreamPeriodCodeConstants.LEVY1799, noContract);
var apprenticeshipEmployersOnApprenticeshipServiceNonLevy = fcsContractAllocationFspCodeLookup.GetValueOrDefault(FundingStreamPeriodCodeConstants.NONLEVY2019, noContract);
var nonLevyContractedApprenticeships1618 = fcsContractAllocationFspCodeLookup.GetValueOrDefault(FundingStreamPeriodCodeConstants.C1618NLAP2018, noContract);
var nonLevyContractedApprenticeshipsAdult = fcsContractAllocationFspCodeLookup.GetValueOrDefault(FundingStreamPeriodCodeConstants.ANLAP2018, noContract);
var traineeships1618 = fcsContractAllocationFspCodeLookup.GetValueOrDefault(FundingStreamPeriodCodeConstants.C1618TRN1920, noContract);
var traineeships1924NonProcured = fcsContractAllocationFspCodeLookup.GetValueOrDefault(FundingStreamPeriodCodeConstants.AEBC19TRN1920, noContract);
var traineeships1924Procured = fcsContractAllocationFspCodeLookup.GetValueOrDefault(FundingStreamPeriodCodeConstants.AEB19TRN1920, noContract);
var adultEducationBudgetNonProcured = fcsContractAllocationFspCodeLookup.GetValueOrDefault(FundingStreamPeriodCodeConstants.AEBCASCL1920, noContract);
var adultEducationBudgetProcured = fcsContractAllocationFspCodeLookup.GetValueOrDefault(FundingStreamPeriodCodeConstants.AEBAS1920, noContract);
var advancedLoansBursary =
fcsContractAllocationFspCodeLookup.GetValueOrDefault(FundingStreamPeriodCodeConstants.ALLB1920)
?? fcsContractAllocationFspCodeLookup.GetValueOrDefault(FundingStreamPeriodCodeConstants.ALLBC1920)
?? noContract;
byte reportCurrentPeriod = (byte)reportServiceContext.ReturnPeriod > 12 ? (byte)12 : (byte)reportServiceContext.ReturnPeriod;
var headerData = await BuildHeaderData(reportServiceContext, CancellationToken.None);
var footerData = BuildFooterData(reportServiceContext);
var fundingSummaryReportModel = new FundingSummaryReportModel(
headerData,
new List <IFundingCategory>()
{
//----------------------------------------------------------------------------------------
// Carry-in Apprenticeships Budget (for starts before 1 May 2017 and non-procured delivery
//----------------------------------------------------------------------------------------
new FundingCategory(@"Carry-in Apprenticeships Budget (for starts before 1 May 2017 and non-procured delivery)", reportCurrentPeriod,
new List <IFundingSubCategory>()
{
new FundingSubCategory(@"16-18 Apprenticeship Frameworks for starts before 1 May 2017", reportCurrentPeriod)
.WithFundLineGroup(BuildIlrFm35FundLineGroup("16-18", "Apprenticeship Frameworks", reportCurrentPeriod, new[] { FundLineConstants.Apprenticeship1618 }, periodisedValues))
.WithFundLineGroup(BuildEasFm35FundLineGroup("16-18", "Apprenticeship Frameworks", reportCurrentPeriod, new[] { FundLineConstants.EasApprenticeship1618 }, periodisedValues)),
new FundingSubCategory(@"16-18 Trailblazer Apprenticeships for starts before 1 May 2017", reportCurrentPeriod)
.WithFundLineGroup(BuildIlrTrailblazerApprenticeshipsFundLineGroup("16-18", reportCurrentPeriod, new[] { FundLineConstants.TrailblazerApprenticeship1618 }, periodisedValues))
.WithFundLineGroup(BuildEasAuthorisedClaimsExcessLearningSupportFundLineGroup("16-18", "Trailblazer Apprenticeships", reportCurrentPeriod, new[] { FundLineConstants.EasTrailblazerApprenticeship1618 }, periodisedValues)),
new FundingSubCategory(@"16-18 Non-Levy Contracted Apprenticeships - Non-procured delivery", reportCurrentPeriod)
.WithFundLineGroup(BuildIlrNonLevyApprenticeshipsFundLineGroup("16-18", reportCurrentPeriod, new[] { FundLineConstants.NonLevyApprenticeship1618, FundLineConstants.NonLevyApprenticeship1618NonProcured }, periodisedValues))
.WithFundLineGroup(BuildEasNonLevyApprenticeshipsFundLineGroup("16-18", reportCurrentPeriod, new[] { FundLineConstants.NonLevyApprenticeship1618NonProcured }, periodisedValues)),
new FundingSubCategory(@"19-23 Apprenticeship Frameworks for starts before 1 May 2017", reportCurrentPeriod)
.WithFundLineGroup(BuildIlrFm35FundLineGroup("19-23", "Apprenticeship Frameworks", reportCurrentPeriod, new[] { FundLineConstants.Apprenticeship1923 }, periodisedValues))
.WithFundLineGroup(BuildEasFm35FundLineGroup("19-23", "Apprenticeship Frameworks", reportCurrentPeriod, new[] { FundLineConstants.EasApprenticeship1923 }, periodisedValues)),
new FundingSubCategory(@"19-23 Trailblazer Apprenticeships for starts before 1 May 2017", reportCurrentPeriod)
.WithFundLineGroup(BuildIlrTrailblazerApprenticeshipsFundLineGroup("19-23", reportCurrentPeriod, new[] { FundLineConstants.TrailblazerApprenticeship1923 }, periodisedValues))
.WithFundLineGroup(BuildEasAuthorisedClaimsExcessLearningSupportFundLineGroup("19-23", "Trailblazer Apprenticeships", reportCurrentPeriod, new[] { FundLineConstants.EasTrailblazerApprenticeship1923 }, periodisedValues)),
new FundingSubCategory(@"24+ Apprenticeship Frameworks for starts before 1 May 2017", reportCurrentPeriod)
.WithFundLineGroup(BuildIlrFm35FundLineGroup("24+", "Apprenticeship Frameworks", reportCurrentPeriod, new[] { FundLineConstants.Apprenticeship24Plus }, periodisedValues))
.WithFundLineGroup(BuildEasFm35FundLineGroup("24+", "Apprenticeship Frameworks", reportCurrentPeriod, new[] { FundLineConstants.EasApprenticeship24Plus }, periodisedValues)),
new FundingSubCategory(@"24+ Trailblazer Apprenticeships for starts before 1 May 2017", reportCurrentPeriod)
.WithFundLineGroup(BuildIlrTrailblazerApprenticeshipsFundLineGroup("24+", reportCurrentPeriod, new[] { FundLineConstants.TrailblazerApprenticeship24Plus }, periodisedValues))
.WithFundLineGroup(BuildEasAuthorisedClaimsExcessLearningSupportFundLineGroup("24+", "Trailblazer Apprenticeships", reportCurrentPeriod, new[] { FundLineConstants.EasTrailblazerApprenticeship24Plus }, periodisedValues)),
new FundingSubCategory(@"Adult Non-Levy Contracted Apprenticeships - Non-procured delivery", reportCurrentPeriod)
.WithFundLineGroup(BuildIlrNonLevyApprenticeshipsFundLineGroup("Adult", reportCurrentPeriod, new[] { FundLineConstants.NonLevyApprenticeship19Plus, FundLineConstants.NonLevyApprenticeship19PlusNonProcured }, periodisedValues))
.WithFundLineGroup(BuildEasNonLevyApprenticeshipsFundLineGroup("Adult", reportCurrentPeriod, new[] { FundLineConstants.NonLevyApprenticeship19PlusNonProcured }, periodisedValues))
}, carryInApprenticeshipBudget),
//-------------------------------------------------------------
// Apprenticeships – Employers on Apprenticeship Service - Levy
//-------------------------------------------------------------
new FundingCategory("Apprenticeships – Employers on Apprenticeship Service - Levy", reportCurrentPeriod,
new List <IFundingSubCategory>()
{
new FundingSubCategory("16-18 Apprenticeship (Employer on App Service) Levy", reportCurrentPeriod)
.WithFundLineGroup(BuildIlrApprenticeshipsFundLineGroup("16-18", "Apprenticeship (Employer on App Service) Levy", reportCurrentPeriod, new[] { FundLineConstants.LevyApprenticeship1618 }, periodisedValues))
.WithFundLineGroup(BuildEasLevyApprenticeshipsFundLineGroup("16-18", "Apprenticeship (Employer on App Service) Levy", reportCurrentPeriod, new[] { FundLineConstants.LevyApprenticeship1618 }, periodisedValues)),
new FundingSubCategory("Adult Apprenticeship (Employer on App Service) Levy", reportCurrentPeriod)
.WithFundLineGroup(BuildIlrApprenticeshipsFundLineGroup("Adult", "Apprenticeship (Employer on App Service) Levy", reportCurrentPeriod, new[] { FundLineConstants.LevyApprenticeship19Plus }, periodisedValues))
.WithFundLineGroup(BuildEasLevyApprenticeshipsFundLineGroup("Adult", "Apprenticeship (Employer on App Service) Levy", reportCurrentPeriod, new[] { FundLineConstants.LevyApprenticeship19Plus }, periodisedValues)),
}, apprenticeshipEmployerOnApprenticeshipServiceLevy),
//-----------------------------------------------------------------
// Apprenticeships – Employers on Apprenticeship Service - Non-Levy
//-----------------------------------------------------------------
new FundingCategory("Apprenticeships – Employers on Apprenticeship Service - Non-Levy", reportCurrentPeriod,
new List <IFundingSubCategory>()
{
new FundingSubCategory("16-18 Apprenticeship (Employer on App Service) Non-Levy", reportCurrentPeriod)
.WithFundLineGroup(BuildIlrApprenticeshipsFundLineGroup("16-18", "Apprenticeship (Employer on App Service) Non-Levy", reportCurrentPeriod, new[] { FundLineConstants.NonLevyApprenticeshipEmployerOnAppService1618 }, periodisedValues))
.WithFundLineGroup(BuildEasLevyApprenticeshipsFundLineGroup("16-18", "Apprenticeship (Employer on App Service) Non-Levy", reportCurrentPeriod, new[] { FundLineConstants.NonLevyApprenticeshipEmployerOnAppService1618 }, periodisedValues)),
new FundingSubCategory("Adult Apprenticeship (Employer on App Service) Non-Levy", reportCurrentPeriod)
.WithFundLineGroup(BuildIlrApprenticeshipsFundLineGroup("Adult", "Apprenticeship (Employer on App Service) Non-Levy", reportCurrentPeriod, new[] { FundLineConstants.NonLevyApprenticeshipEmployerOnAppService19Plus }, periodisedValues))
.WithFundLineGroup(BuildEasLevyApprenticeshipsFundLineGroup("Adult", "Apprenticeship (Employer on App Service) Non-Levy", reportCurrentPeriod, new[] { FundLineConstants.NonLevyApprenticeshipEmployerOnAppService19Plus }, periodisedValues)),
}, apprenticeshipEmployersOnApprenticeshipServiceNonLevy),
//---------------------------------------------------------------------
// 16-18 Non-Levy Contracted Apprenticeships Budget - Procured delivery
//---------------------------------------------------------------------
new FundingCategory("16-18 Non-Levy Contracted Apprenticeships Budget - Procured delivery", reportCurrentPeriod,
new List <IFundingSubCategory>()
{
new FundingSubCategory("16-18 Non-Levy Contracted Apprenticeships", reportCurrentPeriod)
.WithFundLineGroup(BuildIlrNonLevyApprenticeshipsProcuredFundLineGroup("16-18", reportCurrentPeriod, new[] { FundLineConstants.NonLevyApprenticeship1618Procured }, periodisedValues))
.WithFundLineGroup(BuildEasLevyApprenticeshipsFundLineGroup("16-18", "Non-Levy Contracted Apprenticeships", reportCurrentPeriod, new[] { FundLineConstants.NonLevyApprenticeship1618Procured }, periodisedValues)),
}, nonLevyContractedApprenticeships1618),
//---------------------------------------------------------------------
// Adult Non-Levy Contracted Apprenticeships Budget - Procured delivery
//---------------------------------------------------------------------
new FundingCategory("Adult Non-Levy Contracted Apprenticeships Budget - Procured delivery", reportCurrentPeriod,
new List <IFundingSubCategory>()
{
new FundingSubCategory("Adult Non-Levy Contracted Apprenticeships", reportCurrentPeriod)
.WithFundLineGroup(BuildIlrNonLevyApprenticeshipsProcuredFundLineGroup("Adult", reportCurrentPeriod, new[] { FundLineConstants.NonLevyApprenticeship19PlusProcured }, periodisedValues))
.WithFundLineGroup(BuildEasLevyApprenticeshipsFundLineGroup("Adult", "Non-Levy Contracted Apprenticeships", reportCurrentPeriod, new[] { FundLineConstants.NonLevyApprenticeship19PlusProcured }, periodisedValues)),
}, nonLevyContractedApprenticeshipsAdult),
//---------------------------------------------------------------------
// 16-18 Traineeships Budget
//---------------------------------------------------------------------
new FundingCategory("16-18 Traineeships Budget", reportCurrentPeriod,
new List <IFundingSubCategory>()
{
new FundingSubCategory("16-18 Traineeships", reportCurrentPeriod)
.WithFundLineGroup(BuildIlrFm25FundLineGroup(reportCurrentPeriod, periodisedValues))
.WithFundLineGroup(BuildEasFm25FundLineGroup(reportCurrentPeriod, periodisedValues))
}, traineeships1618),
//---------------------------------------------------------------------
// 19-24 Traineeships - Non-procured delivery
//---------------------------------------------------------------------
new FundingCategory("19-24 Traineeships - Non-procured delivery", reportCurrentPeriod,
new List <IFundingSubCategory>()
{
new FundingSubCategory("19-24 Traineeships", reportCurrentPeriod)
.WithFundLineGroup(BuildIlrFm35FundLineGroup("19-24", "Traineeships", reportCurrentPeriod, new[] { FundLineConstants.Traineeship1924, FundLineConstants.Traineeship1924NonProcured }, periodisedValues))
.WithFundLineGroup(BuildEasAuthorisedClaimsExcessLearningSupportFundLineGroup("19-24", "Traineeships", reportCurrentPeriod, new[] { FundLineConstants.EasTraineeships1924NonProcured }, periodisedValues)),
}, traineeships1924NonProcured),
//---------------------------------------------------------------------
// 19-24 Traineeships - Procured delivery from 1 Nov 2017
//---------------------------------------------------------------------
new FundingCategory("19-24 Traineeships - Procured delivery from 1 Nov 2017", reportCurrentPeriod,
new List <IFundingSubCategory>()
{
new FundingSubCategory("19-24 Traineeships", reportCurrentPeriod)
.WithFundLineGroup(BuildIlrFm35FundLineGroup("19-24", "Traineeships", reportCurrentPeriod, new[] { FundLineConstants.Traineeship1924ProcuredFromNov2017 }, periodisedValues))
.WithFundLineGroup(BuildEasAuthorisedClaimsExcessLearningSupportFundLineGroup("19-24", "Traineeships", reportCurrentPeriod, new[] { FundLineConstants.EasTraineeships1924ProcuredFromNov2017 }, periodisedValues)),
}, traineeships1924Procured),
//---------------------------------------------------------------------
// ESFA Adult Education Budget – Non-procured delivery
//---------------------------------------------------------------------
new FundingCategory("ESFA Adult Education Budget – Non-procured delivery", reportCurrentPeriod,
new List <IFundingSubCategory>()
{
new FundingSubCategory("ESFA AEB – Adult Skills (non-procured)", reportCurrentPeriod)
.WithFundLineGroup(BuildIlrFm35FundLineGroup("ESFA", "AEB - Adult Skills (non-procured)", reportCurrentPeriod, new[] { FundLineConstants.AebOtherLearningNonProcured }, periodisedValues))
.WithFundLineGroup(BuildEasAebFundLineGroup("ESFA", "AEB - Adult Skills (non-procured)", reportCurrentPeriod, new[] { FundLineConstants.EasAebAdultSkillsNonProcured }, periodisedValues))
}, adultEducationBudgetNonProcured, AdultEducationBudgetNote),
//---------------------------------------------------------------------
// ESFA Adult Education Budget – Procured delivery from 1 Nov 2017
//---------------------------------------------------------------------
new FundingCategory("ESFA Adult Education Budget – Procured delivery from 1 Nov 2017 ", reportCurrentPeriod,
new List <IFundingSubCategory>()
{
new FundingSubCategory("ESFA AEB – Adult Skills (procured from Nov 2017)", reportCurrentPeriod)
.WithFundLineGroup(BuildIlrFm35FundLineGroup("ESFA", "AEB - Adult Skills (procured from Nov 2017)", reportCurrentPeriod, new[] { FundLineConstants.AebOtherLearningProcuredFromNov2017 }, periodisedValues))
.WithFundLineGroup(BuildEasAebFundLineGroup("ESFA", "AEB - Adult Skills (procured from Nov 2017)", reportCurrentPeriod, new[] { FundLineConstants.EasAebAdultSkillsProcuredFromNov2017 }, periodisedValues))
}, adultEducationBudgetProcured, AdultEducationBudgetNote),
//---------------------------------------------------------------------
// Advanced Loans Bursary Budget
//---------------------------------------------------------------------
new FundingCategory("Advanced Loans Bursary Budget", reportCurrentPeriod,
new List <IFundingSubCategory>()
{
new FundingSubCategory("Advanced Loans Bursary", reportCurrentPeriod)
.WithFundLineGroup(BuildIlrFm99FundLineGroup(reportCurrentPeriod, periodisedValues))
.WithFundLineGroup(BuildEasFm99FundLineGroup(reportCurrentPeriod, periodisedValues))
}, advancedLoansBursary)
},
footerData);
return(fundingSummaryReportModel);
}
public IEnumerable <FundingSummaryReport> Map(IReportServiceContext reportServiceContext, FundingSummaryReportModel fundingSummaryReportModel, CancellationToken cancellationToken)
{
var persistModels = fundingSummaryReportModel.FundingCategories.SelectMany(fc => fc.FundingSubCategories.SelectMany(fsc =>
fsc.FundLineGroups.SelectMany(flg => flg.FundLines.Select(fl => new FundingSummaryReport
{
Ukprn = reportServiceContext.Ukprn,
ReturnPeriod = reportServiceContext.ReturnPeriod,
ContractNo = fc.ContractAllocationNumber,
FundingCategory = fc.FundingCategoryTitle,
FundingSubCategory = fsc.FundingSubCategoryTitle,
FundLine = fl.Title,
Aug20 = fl.Period1,
Sep20 = fl.Period2,
Oct20 = fl.Period3,
Nov20 = fl.Period4,
Dec20 = fl.Period5,
Jan21 = fl.Period6,
Feb21 = fl.Period7,
Mar21 = fl.Period8,
Apr21 = fl.Period9,
May21 = fl.Period10,
Jun21 = fl.Period11,
Jul21 = fl.Period12,
AugMar = fl.Period1To8,
AprJul = fl.Period9To12,
YearToDate = fl.YearToDate,
Total = fl.Total
})))).ToList();
return(persistModels);
}
