public AdultFundingClaimModel BuildAdultFundingClaimModel(
ILogger logger,
IReportServiceContext reportServiceContext,
FM35Global fm35Global,
List <EasSubmissionValues> easSubmissionValues,
ALBGlobal albGlobal,
string providerName,
ILRSourceFileInfo ilrSourceFileInfo,
IDateTimeProvider dateTimeProvider,
IIntUtilitiesService intUtilitiesService,
IMessage message,
IVersionInfo versionInfo,
string orgData,
string largeEmployersData,
string postcodeData,
string larsData)
{
var adultFundingClaimModel = new AdultFundingClaimModel();
try
{
var ilrfileName = reportServiceContext.OriginalFilename ?? reportServiceContext.Filename;
// FM35
decimal otherLearningProgramFunding6MonthsFm35 = 0;
decimal otherLearningProgramFunding12MonthsFm35 = 0;
decimal otherLearningSupport6MonthsFm35 = 0;
decimal otherLearningSupport12MonthsFm35 = 0;
decimal traineeShips1924ProgrammeFunding6MonthsFm35 = 0;
decimal traineeShips1924ProgrammeFunding12MonthsFm35 = 0;
decimal traineeShips1924LearningSupport6MonthsFm35 = 0;
decimal traineeShips1924LearningSupport12MonthsFm35 = 0;
// EAS
decimal otherLearningProgramFunding6MonthsEas = 0;
decimal otherLearningProgramFunding12MonthsEas = 0;
decimal otherLearningSupport6MonthsEas = 0;
decimal otherLearningSupport12MonthsEas = 0;
decimal traineeShips1924ProgrammeFunding6MonthsEas = 0;
decimal traineeShips1924ProgrammeFunding12MonthsEas = 0;
decimal traineeShips1924LearningSupport6MonthsEas = 0;
decimal traineeShips1924LearningSupport12MonthsEas = 0;
decimal traineeShips1924LearnerSupport6MonthsEas = 0;
decimal traineeShips1924LearnerSupport12MonthsEas = 0;
decimal loansAreasCosts6MonthsEas = 0;
decimal loansAreasCosts12MonthsEas = 0;
decimal loansExcessSupport6MonthsEas = 0;
decimal loansExcessSupport12MonthsEas = 0;
// ALB
decimal loansBursaryFunding6Months = 0;
decimal loansBursaryFunding12Months = 0;
decimal loansAreaCosts6Months = 0;
decimal loansAreaCosts12Months = 0;
var ukPrn = reportServiceContext.Ukprn;
// FM35
var fm35LearningDeliveryPeriodisedValues = GetFM35LearningDeliveryPeriodisedValues(fm35Global, ukPrn);
otherLearningProgramFunding6MonthsFm35 = Fm35DeliveryValues6Months(fm35LearningDeliveryPeriodisedValues, new[] { "OnProgPayment", "BalancePayment", "AchievePayment", "EmpOutcomePay" }, new[] { "AEB - Other Learning", "AEB - Other Learning (non-procured)" });
otherLearningProgramFunding12MonthsFm35 = Fm35DeliveryValues12Months(fm35LearningDeliveryPeriodisedValues, new[] { "OnProgPayment", "BalancePayment", "AchievePayment", "EmpOutcomePay" }, new[] { "AEB - Other Learning", "AEB - Other Learning (non-procured)" });
otherLearningSupport6MonthsFm35 = Fm35DeliveryValues6Months(fm35LearningDeliveryPeriodisedValues, new[] { "LearnSuppFundCash" }, new[] { "AEB - Other Learning", "AEB - Other Learning (non-procured)" });
otherLearningSupport12MonthsFm35 = Fm35DeliveryValues12Months(fm35LearningDeliveryPeriodisedValues, new[] { "LearnSuppFundCash" }, new[] { "AEB - Other Learning", "AEB - Other Learning (non-procured)" });
traineeShips1924ProgrammeFunding6MonthsFm35 = Fm35DeliveryValues6Months(fm35LearningDeliveryPeriodisedValues, new[] { "OnProgPayment", "BalancePayment", "AchievePayment", "EmpOutcomePay" }, new[] { "19-24 Traineeship’", "19-24 Traineeship (non-procured)" });
traineeShips1924ProgrammeFunding12MonthsFm35 = Fm35DeliveryValues12Months(fm35LearningDeliveryPeriodisedValues, new[] { "OnProgPayment", "BalancePayment", "AchievePayment", "EmpOutcomePay" }, new[] { "19-24 Traineeship’", "19-24 Traineeship (non-procured)" });
traineeShips1924LearningSupport6MonthsFm35 = Fm35DeliveryValues6Months(fm35LearningDeliveryPeriodisedValues, new[] { "LearnSuppFundCash" }, new[] { "19-24 Traineeship’", "19-24 Traineeship (non-procured)" });
traineeShips1924LearningSupport12MonthsFm35 = Fm35DeliveryValues12Months(fm35LearningDeliveryPeriodisedValues, new[] { "LearnSuppFundCash" }, new[] { "19-24 Traineeship’", "19-24 Traineeship (non-procured)" });
// ALB
var albLearningDeliveryPeriodisedValues = GetAlbLearningDeliveryPeriodisedValues(albGlobal, ukPrn);
loansBursaryFunding6Months = AlbDeliveryValues6Months(albLearningDeliveryPeriodisedValues, new[] { "ALBSupportPayment" }, new[] { "Advanced Learner Loans Bursary" });
loansBursaryFunding12Months = AlbDeliveryValues12Months(albLearningDeliveryPeriodisedValues, new[] { "ALBSupportPayment" }, new[] { "Advanced Learner Loans Bursary" });
loansAreaCosts6Months = AlbDeliveryValues6Months(albLearningDeliveryPeriodisedValues, new[] { "AreaUpliftBalPayment", "AreaUpliftOnProgPayment" }, new[] { "Advanced Learner Loans Bursary" });
loansAreaCosts12Months = AlbDeliveryValues12Months(albLearningDeliveryPeriodisedValues, new[] { "AreaUpliftBalPayment", "AreaUpliftOnProgPayment" }, new[] { "Advanced Learner Loans Bursary" });
// EAS
otherLearningProgramFunding6MonthsEas = easSubmissionValues.Where(x => new[] { "Authorised Claims: AEB-Other Learning", "Princes Trust: AEB-Other Learning" }.Contains(x.PaymentTypeName) &&
First6MonthsArray.Contains(x.CollectionPeriod)).Sum(y => y.PaymentValue);
otherLearningProgramFunding12MonthsEas = easSubmissionValues.Where(x => new[] { "Authorised Claims: AEB-Other Learning", "Princes Trust: AEB-Other Learning" }.Contains(x.PaymentTypeName) &&
First12MonthsArray.Contains(x.CollectionPeriod)).Sum(y => y.PaymentValue);
otherLearningSupport6MonthsEas = easSubmissionValues.Where(x => new[] { "Excess Learning Support: AEB-Other Learning" }.Contains(x.PaymentTypeName) &&
First6MonthsArray.Contains(x.CollectionPeriod)).Sum(y => y.PaymentValue);
otherLearningSupport12MonthsEas = easSubmissionValues.Where(x => new[] { "Excess Learning Support: AEB-Other Learning" }.Contains(x.PaymentTypeName) &&
First12MonthsArray.Contains(x.CollectionPeriod)).Sum(y => y.PaymentValue);
traineeShips1924ProgrammeFunding6MonthsEas = easSubmissionValues.Where(x => new[] { "Authorised Claims: 19-24 Traineeships" }.Contains(x.PaymentTypeName) &&
First6MonthsArray.Contains(x.CollectionPeriod)).Sum(y => y.PaymentValue);
traineeShips1924ProgrammeFunding12MonthsEas = easSubmissionValues.Where(x => new[] { "Authorised Claims: 19-24 Traineeships" }.Contains(x.PaymentTypeName) &&
First12MonthsArray.Contains(x.CollectionPeriod)).Sum(y => y.PaymentValue);
traineeShips1924LearningSupport6MonthsEas = easSubmissionValues.Where(x => new[] { "Excess Learning Support: 19-24 Traineeships" }.Contains(x.PaymentTypeName) &&
First6MonthsArray.Contains(x.CollectionPeriod)).Sum(y => y.PaymentValue);
traineeShips1924LearningSupport12MonthsEas = easSubmissionValues.Where(x => new[] { "Excess Learning Support: 19-24 Traineeships" }.Contains(x.PaymentTypeName) &&
First12MonthsArray.Contains(x.CollectionPeriod)).Sum(y => y.PaymentValue);
traineeShips1924LearnerSupport6MonthsEas = easSubmissionValues.Where(x => new[] { "Learner Support: 19-24 Traineeships" }.Contains(x.PaymentTypeName) &&
First6MonthsArray.Contains(x.CollectionPeriod)).Sum(y => y.PaymentValue);
traineeShips1924LearnerSupport12MonthsEas = easSubmissionValues.Where(x => new[] { "Learner Support: 19-24 Traineeships" }.Contains(x.PaymentTypeName) &&
First12MonthsArray.Contains(x.CollectionPeriod)).Sum(y => y.PaymentValue);
loansAreasCosts6MonthsEas = easSubmissionValues.Where(x => new[] { "Authorised Claims: Advanced Learner Loans Bursary" }.Contains(x.PaymentTypeName) &&
First6MonthsArray.Contains(x.CollectionPeriod)).Sum(y => y.PaymentValue);
loansAreasCosts12MonthsEas = easSubmissionValues.Where(x => new[] { "Authorised Claims: Advanced Learner Loans Bursary" }.Contains(x.PaymentTypeName) &&
First12MonthsArray.Contains(x.CollectionPeriod)).Sum(y => y.PaymentValue);
loansExcessSupport6MonthsEas = easSubmissionValues.Where(x => new[] { "Excess Support: Advanced Learner Loans Bursary" }.Contains(x.PaymentTypeName) &&
First6MonthsArray.Contains(x.CollectionPeriod)).Sum(y => y.PaymentValue);
loansExcessSupport12MonthsEas = easSubmissionValues.Where(x => new[] { "Excess Support: Advanced Learner Loans Bursary" }.Contains(x.PaymentTypeName) &&
First12MonthsArray.Contains(x.CollectionPeriod)).Sum(y => y.PaymentValue);
adultFundingClaimModel.OtherLearningProgrammeFunding6Months = otherLearningProgramFunding6MonthsFm35 + otherLearningProgramFunding6MonthsEas;
adultFundingClaimModel.OtherLearningProgrammeFunding12Months = otherLearningProgramFunding12MonthsFm35 + otherLearningProgramFunding12MonthsEas;
adultFundingClaimModel.OtherLearningLearningSupport6Months = otherLearningSupport6MonthsFm35 + otherLearningSupport6MonthsEas;
adultFundingClaimModel.OtherLearningLearningSupport12Months = otherLearningSupport12MonthsFm35 + otherLearningSupport12MonthsEas;
adultFundingClaimModel.Traineeships1924ProgrammeFunding6Months = traineeShips1924ProgrammeFunding6MonthsFm35 + traineeShips1924ProgrammeFunding6MonthsEas;
adultFundingClaimModel.Traineeships1924ProgrammeFunding12Months = traineeShips1924ProgrammeFunding12MonthsFm35 + traineeShips1924ProgrammeFunding12MonthsEas;
adultFundingClaimModel.Traineeships1924LearningSupport6Months = traineeShips1924LearningSupport6MonthsFm35 + traineeShips1924LearningSupport6MonthsEas;
adultFundingClaimModel.Traineeships1924LearningSupport12Months = traineeShips1924LearningSupport12MonthsFm35 + traineeShips1924LearningSupport12MonthsEas;
adultFundingClaimModel.Traineeships1924LearnerSupport6Months = traineeShips1924LearnerSupport6MonthsEas;
adultFundingClaimModel.Traineeships1924LearnerSupport12Months = traineeShips1924LearnerSupport12MonthsEas;
adultFundingClaimModel.LoansBursaryFunding6Months = loansBursaryFunding6Months;
adultFundingClaimModel.LoansBursaryFunding12Months = loansBursaryFunding12Months;
adultFundingClaimModel.LoansAreaCosts6Months = loansAreaCosts6Months + loansAreasCosts6MonthsEas;
adultFundingClaimModel.LoansAreaCosts12Months = loansAreaCosts12Months + loansAreasCosts12MonthsEas;
adultFundingClaimModel.LoansExcessSupport6Months = loansExcessSupport6MonthsEas;
adultFundingClaimModel.LoansExcessSupport12Months = loansExcessSupport12MonthsEas;
adultFundingClaimModel.ProviderName = providerName ?? "Unknown";
adultFundingClaimModel.Ukprn = ukPrn;
adultFundingClaimModel.ReportGeneratedAt = "Report generated at: " + dateTimeProvider.GetNowUtc().ToString("hh:mm:ss tt") + " on " +
dateTimeProvider.GetNowUtc().ToString("dd/MM/yyyy");
if (message == null) // NON - ILR Submission.
{
if (ilrSourceFileInfo.Filename == null)
{
adultFundingClaimModel.IlrFile = "NA";
adultFundingClaimModel.FilePreparationDate = "NA";
}
else
{
adultFundingClaimModel.IlrFile = ilrSourceFileInfo.Filename;
adultFundingClaimModel.FilePreparationDate = ilrSourceFileInfo.FilePreparationDate.GetValueOrDefault().ToString("dd/MM/yyyy");
}
}
else
{
adultFundingClaimModel.IlrFile = ilrfileName;
adultFundingClaimModel.FilePreparationDate =
message.HeaderEntity.CollectionDetailsEntity.FilePreparationDate.ToString("dd/MM/yyyy");
}
adultFundingClaimModel.ApplicationVersion = versionInfo.ServiceReleaseVersion;
adultFundingClaimModel.ComponentSetVersion = "NA";
adultFundingClaimModel.LargeEmployerData = largeEmployersData;
adultFundingClaimModel.OrganisationData = orgData;
adultFundingClaimModel.PostcodeData = postcodeData;
adultFundingClaimModel.LarsData = larsData;
}
catch (Exception ex)
{
logger.LogError($"Failed building Adult funding claim report, ex: {ex}");
throw;
}
return(adultFundingClaimModel);
}
private void PopulateWorkbook(Workbook workbook, AdultFundingClaimModel adultFundingClaimModel, bool isFis)
{
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
cells[ProviderNameCellName].PutValue(adultFundingClaimModel.ProviderName);
cells[UkprnCellName].PutValue(adultFundingClaimModel.Ukprn);
cells[IlrFileCellName].PutValue(adultFundingClaimModel.IlrFile);
if (!isFis)
{
cells.DeleteRow(8);
cells[OtherLearningProgrammeFunding6MonthsCellName].PutValue(adultFundingClaimModel.OtherLearningProgrammeFunding6Months);
cells[OtherLearningProgrammeFunding12MonthsCellName].PutValue(adultFundingClaimModel.OtherLearningProgrammeFunding12Months);
cells[OtherLearningLearningSupport6MonthsCellName].PutValue(adultFundingClaimModel.OtherLearningLearningSupport6Months);
cells[OtherLearningLearningSupport12MonthsCellName].PutValue(adultFundingClaimModel.OtherLearningLearningSupport12Months);
cells[Traineeships1924ProgrammeFunding6MonthsCellName].PutValue(adultFundingClaimModel.Traineeships1924ProgrammeFunding6Months);
cells[Traineeships1924ProgrammeFunding12MonthsCellName].PutValue(adultFundingClaimModel.Traineeships1924ProgrammeFunding12Months);
cells[Traineeships1924LearningSupport6MonthsCellName].PutValue(adultFundingClaimModel.Traineeships1924LearningSupport6Months);
cells[Traineeships1924LearningSupport12MonthsCellName].PutValue(adultFundingClaimModel.Traineeships1924LearningSupport12Months);
cells[Traineeships1924LearnerSupport6MonthsCellName].PutValue(adultFundingClaimModel.Traineeships1924LearnerSupport6Months);
cells[Traineeships1924LearnerSupport12MonthsCellName].PutValue(adultFundingClaimModel.Traineeships1924LearnerSupport12Months);
cells[LoansBursaryFunding6MonthsCellName].PutValue(adultFundingClaimModel.LoansBursaryFunding6Months);
cells[LoansBursaryFunding12MonthsCellName].PutValue(adultFundingClaimModel.LoansBursaryFunding12Months);
cells[LoansAreaCosts6MonthsCellName].PutValue(adultFundingClaimModel.LoansAreaCosts6Months);
cells[LoansAreaCosts12MonthsCellName].PutValue(adultFundingClaimModel.LoansAreaCosts12Months);
cells[LoansExcessSupport6MonthsCellName].PutValue(adultFundingClaimModel.LoansExcessSupport6Months);
cells[LoansExcessSupport12MonthsCellName].PutValue(adultFundingClaimModel.LoansExcessSupport12Months);
cells[ComponentSetVersionCellName].PutValue(adultFundingClaimModel.ComponentSetVersion);
cells[ApplicationVersionCellName].PutValue(adultFundingClaimModel.ApplicationVersion);
cells[FilePreparationDateCellName].PutValue(adultFundingClaimModel.FilePreparationDate);
cells[LarsDataCellName].PutValue(adultFundingClaimModel.LarsData);
cells[OrganisationDataCellName].PutValue(adultFundingClaimModel.OrganisationData);
cells[PostcodeDataCellName].PutValue(adultFundingClaimModel.PostcodeData);
cells[LargeEmployerDataCellName].PutValue(adultFundingClaimModel.LargeEmployerData);
cells[ReportGeneratedAtCellName].PutValue(adultFundingClaimModel.ReportGeneratedAt);
}
else
{
cells[OtherLearningProgrammeFunding6MonthsCellNameFis].PutValue(adultFundingClaimModel.OtherLearningProgrammeFunding6Months);
cells[OtherLearningProgrammeFunding12MonthsCellNameFis].PutValue(adultFundingClaimModel.OtherLearningProgrammeFunding12Months);
cells[OtherLearningLearningSupport6MonthsCellNameFis].PutValue(adultFundingClaimModel.OtherLearningLearningSupport6Months);
cells[OtherLearningLearningSupport12MonthsCellNameFis].PutValue(adultFundingClaimModel.OtherLearningLearningSupport12Months);
cells[Traineeships1924ProgrammeFunding6MonthsCellNameFis].PutValue(adultFundingClaimModel.Traineeships1924ProgrammeFunding6Months);
cells[Traineeships1924ProgrammeFunding12MonthsCellNameFis].PutValue(adultFundingClaimModel.Traineeships1924ProgrammeFunding12Months);
cells[Traineeships1924LearningSupport6MonthsCellNameFis].PutValue(adultFundingClaimModel.Traineeships1924LearningSupport6Months);
cells[Traineeships1924LearningSupport12MonthsCellNameFis].PutValue(adultFundingClaimModel.Traineeships1924LearningSupport12Months);
cells[Traineeships1924LearnerSupport6MonthsCellNameFis].PutValue(adultFundingClaimModel.Traineeships1924LearnerSupport6Months);
cells[Traineeships1924LearnerSupport12MonthsCellNameFis].PutValue(adultFundingClaimModel.Traineeships1924LearnerSupport12Months);
cells[LoansBursaryFunding6MonthsCellNameFis].PutValue(adultFundingClaimModel.LoansBursaryFunding6Months);
cells[LoansBursaryFunding12MonthsCellNameFis].PutValue(adultFundingClaimModel.LoansBursaryFunding12Months);
cells[LoansAreaCosts6MonthsCellNameFis].PutValue(adultFundingClaimModel.LoansAreaCosts6Months);
cells[LoansAreaCosts12MonthsCellNameFis].PutValue(adultFundingClaimModel.LoansAreaCosts12Months);
cells[LoansExcessSupport6MonthsCellNameFis].PutValue(adultFundingClaimModel.LoansExcessSupport6Months);
cells[LoansExcessSupport12MonthsCellNameFis].PutValue(adultFundingClaimModel.LoansExcessSupport12Months);
cells[ComponentSetVersionCellNameFis].PutValue(adultFundingClaimModel.ComponentSetVersion);
cells[ApplicationVersionCellNameFis].PutValue(adultFundingClaimModel.ApplicationVersion);
cells[FilePreparationDateCellNameFis].PutValue(adultFundingClaimModel.FilePreparationDate);
cells[LarsDataCellNameFis].PutValue(adultFundingClaimModel.LarsData);
cells[OrganisationDataCellNameFis].PutValue(adultFundingClaimModel.OrganisationData);
cells[PostcodeDataCellNameFis].PutValue(adultFundingClaimModel.PostcodeData);
cells[LargeEmployerDataCellNameFis].PutValue(adultFundingClaimModel.LargeEmployerData);
cells[ReportGeneratedAtCellNameFis].PutValue(adultFundingClaimModel.ReportGeneratedAt);
}
}