private decimal?GetYearToDate(FundingSummaryModel fundingSummaryModel, int period)
{
decimal total = 0;
for (int i = 0; i < period; i++)
{
switch (i)
{
case 0:
total += fundingSummaryModel.Period1 ?? 0;
break;
case 1:
total += fundingSummaryModel.Period2 ?? 0;
break;
case 2:
total += fundingSummaryModel.Period3 ?? 0;
break;
case 3:
total += fundingSummaryModel.Period4 ?? 0;
break;
case 4:
total += fundingSummaryModel.Period5 ?? 0;
break;
case 5:
total += fundingSummaryModel.Period6 ?? 0;
break;
case 6:
total += fundingSummaryModel.Period7 ?? 0;
break;
case 7:
total += fundingSummaryModel.Period8 ?? 0;
break;
case 8:
total += fundingSummaryModel.Period9 ?? 0;
break;
case 9:
total += fundingSummaryModel.Period10 ?? 0;
break;
case 10:
total += fundingSummaryModel.Period11 ?? 0;
break;
case 11:
total += fundingSummaryModel.Period12 ?? 0;
break;
}
}
return(total);
}
public FundingSummaryModel BuildWithEasSubValueLine(string title, List <EasSubmissionValues> easSubmissionValues, string paymentTypeName, int period)
{
FundingSummaryModel fundingSummaryModel = new FundingSummaryModel(title);
List <EasSubmissionValues> paymentWiseSubmissionValues = easSubmissionValues
.Where(sv => string.Equals(sv.PaymentTypeName, paymentTypeName, StringComparison.OrdinalIgnoreCase)).ToList();
fundingSummaryModel.Period1 = paymentWiseSubmissionValues.Where(x => x.CollectionPeriod == 1).Sum(x => x.PaymentValue);
fundingSummaryModel.Period2 = paymentWiseSubmissionValues.Where(x => x.CollectionPeriod == 2).Sum(x => x.PaymentValue);
fundingSummaryModel.Period3 = paymentWiseSubmissionValues.Where(x => x.CollectionPeriod == 3).Sum(x => x.PaymentValue);
fundingSummaryModel.Period4 = paymentWiseSubmissionValues.Where(x => x.CollectionPeriod == 4).Sum(x => x.PaymentValue);
fundingSummaryModel.Period5 = paymentWiseSubmissionValues.Where(x => x.CollectionPeriod == 5).Sum(x => x.PaymentValue);
fundingSummaryModel.Period6 = paymentWiseSubmissionValues.Where(x => x.CollectionPeriod == 6).Sum(x => x.PaymentValue);
fundingSummaryModel.Period7 = paymentWiseSubmissionValues.Where(x => x.CollectionPeriod == 7).Sum(x => x.PaymentValue);
fundingSummaryModel.Period8 = paymentWiseSubmissionValues.Where(x => x.CollectionPeriod == 8).Sum(x => x.PaymentValue);
fundingSummaryModel.Period9 = paymentWiseSubmissionValues.Where(x => x.CollectionPeriod == 9).Sum(x => x.PaymentValue);
fundingSummaryModel.Period10 = paymentWiseSubmissionValues.Where(x => x.CollectionPeriod == 10).Sum(x => x.PaymentValue);
fundingSummaryModel.Period11 = paymentWiseSubmissionValues.Where(x => x.CollectionPeriod == 11).Sum(x => x.PaymentValue);
fundingSummaryModel.Period12 = paymentWiseSubmissionValues.Where(x => x.CollectionPeriod == 12).Sum(x => x.PaymentValue);
fundingSummaryModel.Period1_8 =
fundingSummaryModel.Period1 + fundingSummaryModel.Period2 + fundingSummaryModel.Period3 +
fundingSummaryModel.Period4 + fundingSummaryModel.Period5 + fundingSummaryModel.Period6 +
fundingSummaryModel.Period7 + fundingSummaryModel.Period8;
fundingSummaryModel.Period9_12 =
fundingSummaryModel.Period9 + fundingSummaryModel.Period10 + fundingSummaryModel.Period11 +
fundingSummaryModel.Period12;
fundingSummaryModel.YearToDate = GetYearToDate(fundingSummaryModel, period - 1);
fundingSummaryModel.Total = GetYearToDate(fundingSummaryModel, 12);
return(fundingSummaryModel);
}
public FundingSummaryModel TotalRecords(string title, params FundingSummaryModel[] fundingSummaryModels)
{
var fundingSummaryModel = new FundingSummaryModel(title);
foreach (FundingSummaryModel summaryModel in fundingSummaryModels)
{
fundingSummaryModel.Period1 = Total(fundingSummaryModel.Period1, summaryModel.Period1);
fundingSummaryModel.Period2 = Total(fundingSummaryModel.Period2, summaryModel.Period2);
fundingSummaryModel.Period3 = Total(fundingSummaryModel.Period3, summaryModel.Period3);
fundingSummaryModel.Period4 = Total(fundingSummaryModel.Period4, summaryModel.Period4);
fundingSummaryModel.Period5 = Total(fundingSummaryModel.Period5, summaryModel.Period5);
fundingSummaryModel.Period6 = Total(fundingSummaryModel.Period6, summaryModel.Period6);
fundingSummaryModel.Period7 = Total(fundingSummaryModel.Period7, summaryModel.Period7);
fundingSummaryModel.Period8 = Total(fundingSummaryModel.Period8, summaryModel.Period8);
fundingSummaryModel.Period9 = Total(fundingSummaryModel.Period9, summaryModel.Period9);
fundingSummaryModel.Period10 = Total(fundingSummaryModel.Period10, summaryModel.Period10);
fundingSummaryModel.Period11 = Total(fundingSummaryModel.Period11, summaryModel.Period11);
fundingSummaryModel.Period12 = Total(fundingSummaryModel.Period12, summaryModel.Period12);
fundingSummaryModel.Period1_8 = Total(fundingSummaryModel.Period1_8, summaryModel.Period1_8);
fundingSummaryModel.Period9_12 = Total(fundingSummaryModel.Period9_12, summaryModel.Period9_12);
fundingSummaryModel.YearToDate = Total(fundingSummaryModel.YearToDate, summaryModel.YearToDate);
fundingSummaryModel.Total = Total(fundingSummaryModel.Total, summaryModel.Total);
}
return(fundingSummaryModel);
}
public void Execute(
IList <FundingSummaryModel> reportOutput,
FundingReportRow row,
IEnumerable <SupplementaryDataYearlyModel> esfDataModels,
IEnumerable <FM70PeriodisedValuesYearlyModel> ilrData)
{
List <string> deliverableCodes = row.DeliverableCode?.Split(',').Select(x => x.Trim())
.Where(x => !string.IsNullOrWhiteSpace(x)).ToList();
List <FundingSummaryModel> reportRowsToTotal = deliverableCodes == null?
reportOutput.Where(r => r.ExcelRecordStyle == 2).ToList() :
reportOutput.Where(r => deliverableCodes.Contains(r.DeliverableCode) && r.ExcelRecordStyle == 4).ToList();
if (!reportRowsToTotal.Any())
{
return;
}
var rowModel = new FundingSummaryModel(row.Title, HeaderType.None, 2)
{
DeliverableCode = row.DeliverableCode,
ExcelRecordStyle = 2
};
if (row.RowType == RowType.FinalTotal)
{
rowModel.ExcelHeaderStyle = 0;
rowModel.ExcelRecordStyle = 0;
}
var yearlyValueTotals = new List <FundingSummaryReportYearlyValueModel>();
foreach (var yearlyValue in reportRowsToTotal.First().YearlyValues)
{
var yearlyModel = new FundingSummaryReportYearlyValueModel
{
FundingYear = yearlyValue.FundingYear
};
List <FundingSummaryReportYearlyValueModel> periodValues = reportRowsToTotal.SelectMany(r => r.YearlyValues).ToList();
for (var i = 0; i < (periodValues.FirstOrDefault()?.Values.Length ?? 0); i++)
{
yearlyModel.Values[i] = GetPeriodTotals(periodValues, i);
}
yearlyValueTotals.Add(yearlyModel);
}
rowModel.YearlyValues = yearlyValueTotals;
yearlyValueTotals.ForEach(v =>
{
rowModel.Totals.Add(v.Values.Sum());
});
reportOutput.Add(rowModel);
}
private void TotalAlbAreaCosts(
List <LearningDeliveryPeriodisedValue> periodisedValues,
int period,
FundingSummaryModel fundingSummaryModelAlbAreaCosts)
{
if (periodisedValues == null)
{
return;
}
var progPaymentPeriodisedValue = periodisedValues.Where(x => string.Equals(x.AttributeName, AlbAreaUpliftOnProgPayment, StringComparison.OrdinalIgnoreCase)).ToArray();
if (progPaymentPeriodisedValue.Length > 0)
{
fundingSummaryModelAlbAreaCosts.Period1 += progPaymentPeriodisedValue.Sum(x => x.Period1 ?? 0);
fundingSummaryModelAlbAreaCosts.Period2 += progPaymentPeriodisedValue.Sum(x => x.Period2 ?? 0);
fundingSummaryModelAlbAreaCosts.Period3 += progPaymentPeriodisedValue.Sum(x => x.Period3 ?? 0);
fundingSummaryModelAlbAreaCosts.Period4 += progPaymentPeriodisedValue.Sum(x => x.Period4 ?? 0);
fundingSummaryModelAlbAreaCosts.Period5 += progPaymentPeriodisedValue.Sum(x => x.Period5 ?? 0);
fundingSummaryModelAlbAreaCosts.Period6 += progPaymentPeriodisedValue.Sum(x => x.Period6 ?? 0);
fundingSummaryModelAlbAreaCosts.Period7 += progPaymentPeriodisedValue.Sum(x => x.Period7 ?? 0);
fundingSummaryModelAlbAreaCosts.Period8 += progPaymentPeriodisedValue.Sum(x => x.Period8 ?? 0);
fundingSummaryModelAlbAreaCosts.Period9 += progPaymentPeriodisedValue.Sum(x => x.Period9 ?? 0);
fundingSummaryModelAlbAreaCosts.Period10 += progPaymentPeriodisedValue.Sum(x => x.Period10 ?? 0);
fundingSummaryModelAlbAreaCosts.Period11 += progPaymentPeriodisedValue.Sum(x => x.Period11 ?? 0);
fundingSummaryModelAlbAreaCosts.Period12 += progPaymentPeriodisedValue.Sum(x => x.Period12 ?? 0);
}
var areaUpliftPeriodisedValue = periodisedValues.Where(x => string.Equals(x.AttributeName, AlbAreaUpliftBalPayment, StringComparison.OrdinalIgnoreCase)).ToArray();
if (areaUpliftPeriodisedValue.Length > 0)
{
fundingSummaryModelAlbAreaCosts.Period1 += areaUpliftPeriodisedValue.Sum(x => x.Period1 ?? 0);
fundingSummaryModelAlbAreaCosts.Period1 += areaUpliftPeriodisedValue.Sum(x => x.Period2 ?? 0);
fundingSummaryModelAlbAreaCosts.Period1 += areaUpliftPeriodisedValue.Sum(x => x.Period3 ?? 0);
fundingSummaryModelAlbAreaCosts.Period1 += areaUpliftPeriodisedValue.Sum(x => x.Period4 ?? 0);
fundingSummaryModelAlbAreaCosts.Period1 += areaUpliftPeriodisedValue.Sum(x => x.Period5 ?? 0);
fundingSummaryModelAlbAreaCosts.Period1 += areaUpliftPeriodisedValue.Sum(x => x.Period6 ?? 0);
fundingSummaryModelAlbAreaCosts.Period1 += areaUpliftPeriodisedValue.Sum(x => x.Period7 ?? 0);
fundingSummaryModelAlbAreaCosts.Period1 += areaUpliftPeriodisedValue.Sum(x => x.Period8 ?? 0);
fundingSummaryModelAlbAreaCosts.Period1 += areaUpliftPeriodisedValue.Sum(x => x.Period9 ?? 0);
fundingSummaryModelAlbAreaCosts.Period1 += areaUpliftPeriodisedValue.Sum(x => x.Period10 ?? 0);
fundingSummaryModelAlbAreaCosts.Period1 += areaUpliftPeriodisedValue.Sum(x => x.Period11 ?? 0);
fundingSummaryModelAlbAreaCosts.Period1 += areaUpliftPeriodisedValue.Sum(x => x.Period12 ?? 0);
}
fundingSummaryModelAlbAreaCosts.Period1_8 =
fundingSummaryModelAlbAreaCosts.Period1 + fundingSummaryModelAlbAreaCosts.Period2 + fundingSummaryModelAlbAreaCosts.Period3 +
fundingSummaryModelAlbAreaCosts.Period4 + fundingSummaryModelAlbAreaCosts.Period5 + fundingSummaryModelAlbAreaCosts.Period6 +
fundingSummaryModelAlbAreaCosts.Period7 + fundingSummaryModelAlbAreaCosts.Period8;
fundingSummaryModelAlbAreaCosts.Period9_12 =
fundingSummaryModelAlbAreaCosts.Period9 + fundingSummaryModelAlbAreaCosts.Period10 + fundingSummaryModelAlbAreaCosts.Period11 +
fundingSummaryModelAlbAreaCosts.Period12;
fundingSummaryModelAlbAreaCosts.YearToDate = GetYearToDateTotal(fundingSummaryModelAlbAreaCosts, period);
fundingSummaryModelAlbAreaCosts.Total = GetYearToDateTotal(fundingSummaryModelAlbAreaCosts, 12);
}
public void Execute(
int endYear,
IList <FundingSummaryModel> reportOutput,
FundingReportRow row,
IEnumerable <SupplementaryDataYearlyModel> esfDataModels,
IEnumerable <FM70PeriodisedValuesYearly> ilrData)
{
FundingSummaryModel rowModel = new FundingSummaryModel(row.Title, HeaderType.None, 3);
FundingSummaryModel grandTotalRow = reportOutput.FirstOrDefault(r => r.Title == "<ESF-1> Total (£)");
if (row.RowType == RowType.FinalCumulative)
{
rowModel.ExcelHeaderStyle = 6;
rowModel.ExcelRecordStyle = 6;
}
if (grandTotalRow == null)
{
reportOutput.Add(rowModel);
return;
}
var yearlyValues = InitialiseFundingYears(endYear, esfDataModels);
var cumulativeTotal = 0M;
foreach (var year in grandTotalRow.YearlyValues)
{
var yearValues = yearlyValues.FirstOrDefault(yv => yv.FundingYear == year.FundingYear);
if (yearValues == null)
{
continue;
}
for (var i = 0; i < year.Values.Count; i++)
{
cumulativeTotal += year.Values[i];
yearValues.Values.Add(cumulativeTotal);
}
}
decimal?yearEndCumulative = 0M;
for (var index = 0; index < grandTotalRow.Totals.Count - 1; index++)
{
var total = grandTotalRow.Totals[index];
yearEndCumulative += total ?? 0M;
rowModel.Totals.Add(yearEndCumulative);
}
rowModel.Totals.Add(yearEndCumulative);
rowModel.YearlyValues = yearlyValues;
reportOutput.Add(rowModel);
}
public FundingSummaryModel BuildWithFundLine(string title, FM81Global fm81Global, List <string> validLearners, string fundLine, string[] attributes, int period)
{
FundingSummaryModel fundingSummaryModel = new FundingSummaryModel(title);
if (fm81Global?.Learners == null || validLearners == null)
{
return(fundingSummaryModel);
}
LearningDelivery[] learningDeliveries = _cacheProviderService.Get(fundLine);
if (learningDeliveries == null)
{
FM81Learner[] learners =
fm81Global.Learners.Where(x => validLearners.Contains(x.LearnRefNumber)).ToArray();
learningDeliveries = learners.SelectMany(x => x.LearningDeliveries)
.Where(x => string.Equals(fundLine, x.LearningDeliveryValues.FundLine, StringComparison.OrdinalIgnoreCase)).ToArray();
_cacheProviderService.Set(fundLine, learningDeliveries);
}
LearningDeliveryPeriodisedValue[] periodisedValues = learningDeliveries.SelectMany(x => x.LearningDeliveryPeriodisedValues)
.Where(x => attributes.Contains(x.AttributeName)).ToArray();
fundingSummaryModel.Period1 = periodisedValues.Sum(x => x.Period1 ?? 0);
fundingSummaryModel.Period2 = periodisedValues.Sum(x => x.Period2 ?? 0);
fundingSummaryModel.Period3 = periodisedValues.Sum(x => x.Period3 ?? 0);
fundingSummaryModel.Period4 = periodisedValues.Sum(x => x.Period4 ?? 0);
fundingSummaryModel.Period5 = periodisedValues.Sum(x => x.Period5 ?? 0);
fundingSummaryModel.Period6 = periodisedValues.Sum(x => x.Period6 ?? 0);
fundingSummaryModel.Period7 = periodisedValues.Sum(x => x.Period7 ?? 0);
fundingSummaryModel.Period8 = periodisedValues.Sum(x => x.Period8 ?? 0);
fundingSummaryModel.Period9 = periodisedValues.Sum(x => x.Period9 ?? 0);
fundingSummaryModel.Period10 = periodisedValues.Sum(x => x.Period10 ?? 0);
fundingSummaryModel.Period11 = periodisedValues.Sum(x => x.Period11 ?? 0);
fundingSummaryModel.Period12 = periodisedValues.Sum(x => x.Period12 ?? 0);
fundingSummaryModel.Period1_8 =
fundingSummaryModel.Period1 + fundingSummaryModel.Period2 + fundingSummaryModel.Period3 +
fundingSummaryModel.Period4 + fundingSummaryModel.Period5 + fundingSummaryModel.Period6 +
fundingSummaryModel.Period7 + fundingSummaryModel.Period8;
fundingSummaryModel.Period9_12 =
fundingSummaryModel.Period9 + fundingSummaryModel.Period10 + fundingSummaryModel.Period11 +
fundingSummaryModel.Period12;
fundingSummaryModel.YearToDate = GetYearToDate(fundingSummaryModel, period - 1);
fundingSummaryModel.Total = GetYearToDate(fundingSummaryModel, 12);
return(fundingSummaryModel);
}
public void Execute(
IList <FundingSummaryModel> reportOutput,
FundingReportRow row,
IEnumerable <SupplementaryDataYearlyModel> esfDataModels,
IEnumerable <FM70PeriodisedValuesYearlyModel> ilrData)
{
FundingSummaryModel rowModel = new FundingSummaryModel(row.Title, HeaderType.None, 3);
FundingSummaryModel grandTotalRow = reportOutput.FirstOrDefault(r => r.Title == "<ESF-1> Total (£)");
if (row.RowType == RowType.FinalCumulative)
{
rowModel.ExcelHeaderStyle = 0;
rowModel.ExcelRecordStyle = 0;
}
if (grandTotalRow == null)
{
reportOutput.Add(rowModel);
return;
}
var yearlyValues = new List <FundingSummaryReportYearlyValueModel>();
var cumulativeTotal = 0M;
foreach (var year in grandTotalRow.YearlyValues)
{
var yearValues = new FundingSummaryReportYearlyValueModel();
for (var i = 0; i < 12; i++)
{
cumulativeTotal += year.Values[i];
yearValues.Values[i] = cumulativeTotal;
}
yearlyValues.Add(yearValues);
}
rowModel.YearlyValues = yearlyValues;
var yearEndCumulative = 0M;
for (var index = 0; index < grandTotalRow.Totals.Count - 1; index++)
{
var total = grandTotalRow.Totals[index];
yearEndCumulative += total;
rowModel.Totals.Add(yearEndCumulative);
}
reportOutput.Add(rowModel);
}
public FundingSummaryModel BuildWithFundLine(string title, FM36Global fm36Global, List <string> validLearners, string[] fundLines, string[] attributes, int period)
{
FundingSummaryModel fundingSummaryModel = new FundingSummaryModel(title);
if (fm36Global?.Learners == null || validLearners == null)
{
return(fundingSummaryModel);
}
LearningDelivery[] learningDeliveries = _cacheProviderService.Get(fundLines.GetHashCode());
if (learningDeliveries == null)
{
FM36Learner[] learners = fm36Global.Learners.Where(x => validLearners.Contains(x.LearnRefNumber)).ToArray();
learningDeliveries = learners.SelectMany(x => x.LearningDeliveries).Where(x => fundLines.Contains(x.LearningDeliveryValues.LearnDelInitialFundLineType)).ToArray();
_cacheProviderService.Set(fundLines.GetHashCode(), learningDeliveries);
}
var periodisedValues = learningDeliveries.SelectMany(x => x.LearningDeliveryPeriodisedValues)
.Where(x => attributes.Contains(x.AttributeName)).ToArray();
fundingSummaryModel.Period1 = periodisedValues.Sum(x => x.Period1 ?? 0);
fundingSummaryModel.Period2 = periodisedValues.Sum(x => x.Period2 ?? 0);
fundingSummaryModel.Period3 = periodisedValues.Sum(x => x.Period3 ?? 0);
fundingSummaryModel.Period4 = periodisedValues.Sum(x => x.Period4 ?? 0);
fundingSummaryModel.Period5 = periodisedValues.Sum(x => x.Period5 ?? 0);
fundingSummaryModel.Period6 = periodisedValues.Sum(x => x.Period6 ?? 0);
fundingSummaryModel.Period7 = periodisedValues.Sum(x => x.Period7 ?? 0);
fundingSummaryModel.Period8 = periodisedValues.Sum(x => x.Period8 ?? 0);
fundingSummaryModel.Period9 = periodisedValues.Sum(x => x.Period9 ?? 0);
fundingSummaryModel.Period10 = periodisedValues.Sum(x => x.Period10 ?? 0);
fundingSummaryModel.Period11 = periodisedValues.Sum(x => x.Period11 ?? 0);
fundingSummaryModel.Period12 = periodisedValues.Sum(x => x.Period12 ?? 0);
fundingSummaryModel.Period1_8 =
fundingSummaryModel.Period1 + fundingSummaryModel.Period2 + fundingSummaryModel.Period3 +
fundingSummaryModel.Period4 + fundingSummaryModel.Period5 + fundingSummaryModel.Period6 +
fundingSummaryModel.Period7 + fundingSummaryModel.Period8;
fundingSummaryModel.Period9_12 =
fundingSummaryModel.Period9 + fundingSummaryModel.Period10 + fundingSummaryModel.Period11 +
fundingSummaryModel.Period12;
fundingSummaryModel.YearToDate = GetYearToDate(fundingSummaryModel, period - 1);
fundingSummaryModel.Total = GetYearToDate(fundingSummaryModel, 12);
return(fundingSummaryModel);
}
public FundingSummaryModel BuildWithFundLine(
string title,
FM25Global fm25Global,
List<string> validLearners,
string fundLine,
int period)
{
FundingSummaryModel fundingSummaryModel = new FundingSummaryModel(title);
if (fm25Global?.Learners == null || validLearners == null)
{
return fundingSummaryModel;
}
FM25Learner[] fundLineObject = fm25Global.Learners.Where(x =>
string.Equals(x.FundLine, fundLine, StringComparison.OrdinalIgnoreCase) &&
validLearners.Contains(x.LearnRefNumber)).ToArray();
LearnerPeriodisedValues[] periodisedValues = fundLineObject.SelectMany(x => x.LearnerPeriodisedValues)
.Where(x => string.Equals(x.AttributeName, "LnrOnProgPay")).ToArray();
fundingSummaryModel.Period1 = periodisedValues.Sum(x => x.Period1 ?? 0);
fundingSummaryModel.Period2 = periodisedValues.Sum(x => x.Period2 ?? 0);
fundingSummaryModel.Period3 = periodisedValues.Sum(x => x.Period3 ?? 0);
fundingSummaryModel.Period4 = periodisedValues.Sum(x => x.Period4 ?? 0);
fundingSummaryModel.Period5 = periodisedValues.Sum(x => x.Period5 ?? 0);
fundingSummaryModel.Period6 = periodisedValues.Sum(x => x.Period6 ?? 0);
fundingSummaryModel.Period7 = periodisedValues.Sum(x => x.Period7 ?? 0);
fundingSummaryModel.Period8 = periodisedValues.Sum(x => x.Period8 ?? 0);
fundingSummaryModel.Period9 = periodisedValues.Sum(x => x.Period9 ?? 0);
fundingSummaryModel.Period10 = periodisedValues.Sum(x => x.Period10 ?? 0);
fundingSummaryModel.Period11 = periodisedValues.Sum(x => x.Period11 ?? 0);
fundingSummaryModel.Period12 = periodisedValues.Sum(x => x.Period12 ?? 0);
fundingSummaryModel.Period1_8 =
fundingSummaryModel.Period1 + fundingSummaryModel.Period2 + fundingSummaryModel.Period3 +
fundingSummaryModel.Period4 + fundingSummaryModel.Period5 + fundingSummaryModel.Period6 +
fundingSummaryModel.Period7 + fundingSummaryModel.Period8;
fundingSummaryModel.Period9_12 =
fundingSummaryModel.Period9 + fundingSummaryModel.Period10 + fundingSummaryModel.Period11 +
fundingSummaryModel.Period12;
fundingSummaryModel.YearToDate = GetYearToDate(fundingSummaryModel, period - 1);
fundingSummaryModel.Total = GetYearToDate(fundingSummaryModel, 12);
return fundingSummaryModel;
}
private void ApplyAdditionalFormatting(Workbook workbook, FundingSummaryModel rowOfData)
{
if (rowOfData == null)
{
return;
}
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells.CreateRange(1, 5, 4, 1).ApplyStyle(_cellStyles[6].Style, _cellStyles[6].StyleFlag); // Header
int valCount = rowOfData.YearlyValues.Sum(x => x.Values.Length);
int nonYearCount = rowOfData.YearlyValues.Sum(x => x.FundingYear != 2018 ? x.Values.Length : 0);
int yearCount = rowOfData.YearlyValues.Sum(x => x.FundingYear == 2018 ? x.Values.Length : 0);
worksheet.Cells.CreateRange(9, nonYearCount + 1, 110, yearCount).ApplyStyle(_cellStyles[6].Style, _cellStyles[6].StyleFlag); // Current Year
worksheet.Cells.CreateRange(9, valCount + rowOfData.Totals.Count, 110, 1).ApplyStyle(_cellStyles[6].Style, _cellStyles[6].StyleFlag); // Current Year Subtotal
}
private void TotalAlbFunding(
List <LearningDeliveryPeriodisedValue> periodisedValues,
int period,
string attributeName,
FundingSummaryModel fundingSummaryModelAlbFunding)
{
if (periodisedValues == null)
{
return;
}
LearningDeliveryPeriodisedValue[] periodisedValue = periodisedValues.Where(x => string.Equals(x.AttributeName, attributeName, StringComparison.OrdinalIgnoreCase)).ToArray();
if (periodisedValue.Length == 0)
{
return;
}
fundingSummaryModelAlbFunding.Period1 += periodisedValue.Sum(x => x.Period1 ?? 0);
fundingSummaryModelAlbFunding.Period2 += periodisedValue.Sum(x => x.Period2 ?? 0);
fundingSummaryModelAlbFunding.Period3 += periodisedValue.Sum(x => x.Period3 ?? 0);
fundingSummaryModelAlbFunding.Period4 += periodisedValue.Sum(x => x.Period4 ?? 0);
fundingSummaryModelAlbFunding.Period5 += periodisedValue.Sum(x => x.Period5 ?? 0);
fundingSummaryModelAlbFunding.Period6 += periodisedValue.Sum(x => x.Period6 ?? 0);
fundingSummaryModelAlbFunding.Period7 += periodisedValue.Sum(x => x.Period7 ?? 0);
fundingSummaryModelAlbFunding.Period8 += periodisedValue.Sum(x => x.Period8 ?? 0);
fundingSummaryModelAlbFunding.Period9 += periodisedValue.Sum(x => x.Period9 ?? 0);
fundingSummaryModelAlbFunding.Period10 += periodisedValue.Sum(x => x.Period10 ?? 0);
fundingSummaryModelAlbFunding.Period11 += periodisedValue.Sum(x => x.Period11 ?? 0);
fundingSummaryModelAlbFunding.Period12 += periodisedValue.Sum(x => x.Period12 ?? 0);
fundingSummaryModelAlbFunding.Period1_8 =
fundingSummaryModelAlbFunding.Period1 + fundingSummaryModelAlbFunding.Period2 + fundingSummaryModelAlbFunding.Period3 +
fundingSummaryModelAlbFunding.Period4 + fundingSummaryModelAlbFunding.Period5 + fundingSummaryModelAlbFunding.Period6 +
fundingSummaryModelAlbFunding.Period7 + fundingSummaryModelAlbFunding.Period8;
fundingSummaryModelAlbFunding.Period9_12 =
fundingSummaryModelAlbFunding.Period9 + fundingSummaryModelAlbFunding.Period10 + fundingSummaryModelAlbFunding.Period11 +
fundingSummaryModelAlbFunding.Period12;
fundingSummaryModelAlbFunding.YearToDate = GetYearToDateTotal(fundingSummaryModelAlbFunding, period - 1);
fundingSummaryModelAlbFunding.Total = GetYearToDateTotal(fundingSummaryModelAlbFunding, 12);
}
public FundingSummaryModel TotalRecordsCumulative(string title, FundingSummaryModel sourceFundingSummaryModel)
{
FundingSummaryModel fundingSummaryModel = new FundingSummaryModel(title);
fundingSummaryModel.Period1 = sourceFundingSummaryModel.Period1;
fundingSummaryModel.Period2 = Total(fundingSummaryModel.Period1, sourceFundingSummaryModel.Period2);
fundingSummaryModel.Period3 = Total(fundingSummaryModel.Period2, sourceFundingSummaryModel.Period3);
fundingSummaryModel.Period4 = Total(fundingSummaryModel.Period3, sourceFundingSummaryModel.Period4);
fundingSummaryModel.Period5 = Total(fundingSummaryModel.Period4, sourceFundingSummaryModel.Period5);
fundingSummaryModel.Period6 = Total(fundingSummaryModel.Period5, sourceFundingSummaryModel.Period6);
fundingSummaryModel.Period7 = Total(fundingSummaryModel.Period6, sourceFundingSummaryModel.Period7);
fundingSummaryModel.Period8 = Total(fundingSummaryModel.Period7, sourceFundingSummaryModel.Period8);
fundingSummaryModel.Period9 = Total(fundingSummaryModel.Period8, sourceFundingSummaryModel.Period9);
fundingSummaryModel.Period10 = Total(fundingSummaryModel.Period9, sourceFundingSummaryModel.Period10);
fundingSummaryModel.Period11 = Total(fundingSummaryModel.Period10, sourceFundingSummaryModel.Period11);
fundingSummaryModel.Period12 = Total(fundingSummaryModel.Period11, sourceFundingSummaryModel.Period12);
fundingSummaryModel.Period1_8 = null;
fundingSummaryModel.Period9_12 = null;
fundingSummaryModel.YearToDate = null;
fundingSummaryModel.Total = null;
return(fundingSummaryModel);
}
public async Task <List <FundingSummaryModel> > BuildAsync(IReportServiceContext reportServiceContext, CancellationToken cancellationToken)
{
FundingSummaryModel fundingSummaryModelAlbFunding = new FundingSummaryModel()
{
Title = "ILR Advanced Loans Bursary Funding (£)"
};
FundingSummaryModel fundingSummaryModelAlbAreaCosts = new FundingSummaryModel()
{
Title = "ILR Advanced Loans Bursary Area Costs (£)"
};
List <FundingSummaryModel> fundingSummaryModels = new List <FundingSummaryModel>()
{
fundingSummaryModelAlbFunding,
fundingSummaryModelAlbAreaCosts
};
Task <IMessage> ilrFile = _ilrProviderService.GetIlrFile(reportServiceContext, cancellationToken);
Task <List <string> > validLearners = _validLearnersService.GetLearnersAsync(reportServiceContext, cancellationToken);
Task <ALBGlobal> albData = _allbProviderService.GetAllbData(reportServiceContext, cancellationToken);
await Task.WhenAll(ilrFile, validLearners, albData);
List <string> ilrError = new List <string>();
List <string> albLearnerError = new List <string>();
try
{
ILearner[] learners = ilrFile.Result?.Learners?.Where(x => validLearners.Result.Contains(x.LearnRefNumber)).ToArray();
foreach (ILearner learner in learners ?? Enumerable.Empty <ILearner>())
{
ALBLearner albLearner = albData.Result?.Learners?.SingleOrDefault(x => string.Equals(x.LearnRefNumber, learner.LearnRefNumber, StringComparison.OrdinalIgnoreCase));
if (albLearner == null)
{
albLearnerError.Add(learner.LearnRefNumber);
continue;
}
foreach (ILearningDelivery learningDelivery in learner.LearningDeliveries ?? Enumerable.Empty <ILearningDelivery>())
{
LearningDelivery albLearningDeliveryAreaCosts = albLearner.LearningDeliveries?.Where(x => x.LearningDeliveryValue.FundLine == "Advanced Learner Loans Bursary").SingleOrDefault(x => x.AimSeqNumber == learningDelivery.AimSeqNumber);
if (learningDelivery.LearningDeliveryFAMs != null && learningDelivery.LearningDeliveryFAMs.Any(x =>
!(string.Equals(x.LearnDelFAMType, Constants.LearningDeliveryFAMCodeLDM, StringComparison.OrdinalIgnoreCase) && string.Equals(x.LearnDelFAMCode, "359", StringComparison.OrdinalIgnoreCase))))
{
TotalAlbFunding(albLearningDeliveryAreaCosts?.LearningDeliveryPeriodisedValues, reportServiceContext.ReturnPeriod, AlbSupportPayment, fundingSummaryModelAlbFunding);
}
TotalAlbAreaCosts(albLearningDeliveryAreaCosts?.LearningDeliveryPeriodisedValues, reportServiceContext.ReturnPeriod, fundingSummaryModelAlbAreaCosts);
}
}
if (ilrError.Any())
{
_logger.LogWarning($"Failed to get one or more ILR learners while {nameof(AllbBuilder)}.{nameof(BuildAsync)}: {_stringUtilitiesService.JoinWithMaxLength(ilrError)}");
}
if (albLearnerError.Any())
{
_logger.LogWarning($"Failed to get one or more ALB learners while {nameof(AllbBuilder)}.{nameof(BuildAsync)}: {_stringUtilitiesService.JoinWithMaxLength(albLearnerError)}");
}
}
catch (Exception ex)
{
_logger.LogError("AlbBuilder BuildAsync failed with Exception: ", ex);
}
return(fundingSummaryModels);
}
public void Execute(
int endYear,
IList <FundingSummaryModel> reportOutput,
FundingReportRow row,
IEnumerable <SupplementaryDataYearlyModel> esfDataModels,
IEnumerable <FM70PeriodisedValuesYearly> ilrData)
{
var reportRow = new FundingSummaryModel
{
Title = row.Title,
DeliverableCode = row.DeliverableCode,
CodeBase = row.CodeBase
};
var supplementaryDataYearlyModels = esfDataModels.ToList();
var reportRowYearlyValues = InitialiseFundingYears(endYear, supplementaryDataYearlyModels);
var codeBase = row.CodeBase;
if (codeBase == EsfCodeBase)
{
foreach (var strategy in _esfStrategies)
{
if (row.CostType != null)
{
if (!strategy.IsMatch(row.DeliverableCode, row.CostType))
{
continue;
}
}
if (!strategy.IsMatch(row.DeliverableCode))
{
continue;
}
strategy.Execute(supplementaryDataYearlyModels, reportRowYearlyValues);
break;
}
}
else
{
foreach (var strategy in _ilrStrategies)
{
if (row.AttributeNames != null)
{
if (!strategy.IsMatch(row.DeliverableCode, row.AttributeNames))
{
continue;
}
}
if (!strategy.IsMatch(row.DeliverableCode))
{
continue;
}
strategy.Execute(ilrData, reportRowYearlyValues);
break;
}
}
reportRowYearlyValues.ForEach(v =>
{
reportRow.Totals.Add(v.Values.Sum());
});
reportRow.YearlyValues = reportRowYearlyValues;
reportRow.GrandTotal = reportRow.Totals.Sum() ?? 0M;
reportRow.IsDataRow = true;
reportOutput.Add(reportRow);
}
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);
}
public void Execute(
int endYear,
IList <FundingSummaryModel> reportOutput,
FundingReportRow row,
IEnumerable <SupplementaryDataYearlyModel> esfDataModels,
IEnumerable <FM70PeriodisedValuesYearly> ilrData)
{
List <string> deliverableCodes = row.DeliverableCode?.Split(',').Select(x => x.Trim())
.Where(x => !string.IsNullOrWhiteSpace(x)).ToList();
List <FundingSummaryModel> reportRowsToTotal = deliverableCodes == null?
reportOutput.Where(r => r.IsDataRow).ToList() :
reportOutput.Where(r => deliverableCodes.Contains(r.DeliverableCode) && r.IsDataRow &&
(string.IsNullOrEmpty(row.CodeBase) || r.CodeBase == row.CodeBase)).ToList();
if (!reportRowsToTotal.Any())
{
return;
}
var rowModel = new FundingSummaryModel(row.Title, HeaderType.None, 3)
{
DeliverableCode = row.DeliverableCode,
ExcelRecordStyle = 3,
ExcelHeaderStyle = 3
};
if (string.IsNullOrEmpty(row.CodeBase))
{
rowModel.ExcelHeaderStyle = 2;
rowModel.ExcelRecordStyle = 2;
}
if (row.RowType == RowType.FinalTotal)
{
rowModel.ExcelHeaderStyle = 6;
rowModel.ExcelRecordStyle = 6;
}
var yearlyValueTotals = InitialiseFundingYears(endYear, esfDataModels);
foreach (var year in reportRowsToTotal.First().YearlyValues)
{
var yearValues = yearlyValueTotals.FirstOrDefault(yv => yv.FundingYear == year.FundingYear);
if (yearValues == null)
{
continue;
}
List <FundingSummaryReportYearlyValueModel> periodValues = reportRowsToTotal.SelectMany(r => r.YearlyValues)
.Where(r => r.FundingYear == year.FundingYear).ToList();
if (!periodValues.Any())
{
continue;
}
var periodValueCount = periodValues.First().Values.Count;
for (var i = 0; i < periodValueCount; i++)
{
yearValues.Values.Add(periodValues.Sum(model => model.Values.ElementAtOrDefault(i)));
}
}
yearlyValueTotals.ForEach(v =>
{
rowModel.Totals.Add(v.Values.Sum());
});
rowModel.YearlyValues = yearlyValueTotals;
rowModel.GrandTotal = rowModel.Totals.Sum() ?? 0M;
reportOutput.Add(rowModel);
}
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 void Execute(
IList <FundingSummaryModel> reportOutput,
FundingReportRow row,
IEnumerable <SupplementaryDataYearlyModel> esfDataModels,
IEnumerable <FM70PeriodisedValuesYearlyModel> ilrData)
{
var reportRow = new FundingSummaryModel
{
Title = row.Title,
DeliverableCode = row.DeliverableCode
};
var reportRowYearlyValues = new List <FundingSummaryReportYearlyValueModel>();
var codeBase = row.CodeBase;
if (codeBase == EsfCodeBase)
{
foreach (var strategy in _esfStrategies)
{
if (row.ReferenceType != null)
{
if (!strategy.IsMatch(row.DeliverableCode, row.ReferenceType))
{
continue;
}
}
if (!strategy.IsMatch(row.DeliverableCode))
{
continue;
}
strategy.Execute(esfDataModels, reportRowYearlyValues);
break;
}
}
else
{
foreach (var strategy in _ilrStrategies)
{
if (row.AttributeNames != null)
{
if (!strategy.IsMatch(row.DeliverableCode, row.AttributeNames))
{
continue;
}
}
if (!strategy.IsMatch(row.DeliverableCode))
{
continue;
}
strategy.Execute(ilrData, reportRowYearlyValues);
break;
}
}
reportRow.YearlyValues = reportRowYearlyValues;
reportRowYearlyValues.ForEach(v =>
{
reportRow.Totals.Add(v.Values.Sum());
});
reportOutput.Add(reportRow);
}
