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); } }