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);
        }
Beispiel #2
0
        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);
            }
        }