public async Task <LearnerLevelViewHBCPInfo> GetHBCPInfoAsync( int ukPrn, CancellationToken cancellationToken) { LearnerLevelViewHBCPInfo hbcpInfo = null; using (IDASPaymentsContext dasPaymentsContext = _dasPaymentsContextFactory()) { hbcpInfo = new LearnerLevelViewHBCPInfo() { UkPrn = ukPrn, HBCPModels = new List <LearnerLevelViewHBCPModel>() }; var hbcpRecords = await dasPaymentsContext.RequiredPaymentEvents .Where(x => x.Ukprn == ukPrn) .Select(x => new LearnerLevelViewHBCPModel { UkPrn = x.Ukprn, LearnerReferenceNumber = x.LearnerReferenceNumber, LearnerUln = x.LearnerUln, CollectionPeriod = x.CollectionPeriod, DeliveryPeriod = x.DeliveryPeriod, LearningAimReference = x.LearningAimReference, NonPaymentReason = x.NonPaymentReason, }) .Distinct() .ToListAsync(cancellationToken); hbcpInfo.HBCPModels.AddRange(hbcpRecords); } return(hbcpInfo); }
public IReadOnlyList <LearnerLevelViewModel> BuildLearnerLevelViewModelList( int ukprn, AppsMonthlyPaymentILRInfo appsMonthlyPaymentIlrInfo, AppsCoInvestmentILRInfo appsCoInvestmentIlrInfo, LearnerLevelViewDASDataLockInfo learnerLevelViewDASDataLockInfo, LearnerLevelViewHBCPInfo learnerLevelHBCPInfo, LearnerLevelViewFM36Info learnerLevelDAsInfo, IDictionary <LearnerLevelViewPaymentsKey, List <AppsMonthlyPaymentDasPaymentModel> > paymentsDictionary, IDictionary <string, List <AECApprenticeshipPriceEpisodePeriodisedValuesInfo> > aECPriceEpisodeDictionary, IDictionary <string, List <AECLearningDeliveryPeriodisedValuesInfo> > aECLearningDeliveryDictionary, IDictionary <long, string> employerNameDictionary, int returnPeriod) { // cache the passed in data for use in the private 'Get' methods _appsReturnPeriod = returnPeriod; // this variable is the final report and is the return value of this method. List <LearnerLevelViewModel> learnerLevelViewModelList = null; try { // Lookup for employer IDs to employer name IDictionary <int, string> employerNamesToIDs = new Dictionary <int, string>(); // Union the keys from the datasets being used to source the report var unionedKeys = UnionKeys(paymentsDictionary.Keys, aECPriceEpisodeDictionary.Keys, aECLearningDeliveryDictionary.Keys); // Populate the learner list using the ILR query first learnerLevelViewModelList = unionedKeys .OrderBy(o => o.LearnerReferenceNumber) .ThenBy(o => o.PaymentFundingLineType) .Select(record => { var reportRecord = new LearnerLevelViewModel() { Ukprn = ukprn, PaymentLearnerReferenceNumber = record.LearnerReferenceNumber, PaymentFundingLineType = record.PaymentFundingLineType }; // Extract ILR info var ilrRecord = appsMonthlyPaymentIlrInfo.Learners.FirstOrDefault(p => p.LearnRefNumber == reportRecord.PaymentLearnerReferenceNumber); if (ilrRecord != null) { reportRecord.FamilyName = ilrRecord.FamilyName; reportRecord.GivenNames = ilrRecord.GivenNames; reportRecord.PaymentUniqueLearnerNumber = ilrRecord.UniqueLearnerNumber; if ((ilrRecord.LearnerEmploymentStatus != null) && (ilrRecord.LearnerEmploymentStatus.Count > 0)) { reportRecord.LearnerEmploymentStatusEmployerId = ilrRecord.LearnerEmploymentStatus.Where(les => les?.Ukprn == ukprn && les.LearnRefNumber.CaseInsensitiveEquals(reportRecord.PaymentLearnerReferenceNumber) && les?.EmpStat == 10) .OrderByDescending(les => les?.DateEmpStatApp) .FirstOrDefault()?.EmpdId; } } if (paymentsDictionary.TryGetValue(new LearnerLevelViewPaymentsKey(reportRecord.PaymentLearnerReferenceNumber, reportRecord.PaymentFundingLineType), out List <AppsMonthlyPaymentDasPaymentModel> paymentValues)) { // Assign the amounts reportRecord.PlannedPaymentsToYouToDate = paymentValues.Where(p => PeriodESFAPlannedPaymentsFSTypePredicateToPeriod(p, _appsReturnPeriod) || PeriodESFAPlannedPaymentsTTTypePredicateToPeriod(p, _appsReturnPeriod) || PeriodESFAPlannedPaymentsNonZPTypePredicateToPeriod(p, _appsReturnPeriod)).Sum(c => c.Amount ?? 0m); reportRecord.ESFAPlannedPaymentsThisPeriod = paymentValues.Where(p => PeriodESFAPlannedPaymentsFSTypePredicate(p, _appsReturnPeriod) || PeriodESFAPlannedPaymentsTTTypePredicate(p, _appsReturnPeriod) || PeriodESFAPlannedPaymentsNonZPTypePredicate(p, _appsReturnPeriod)).Sum(c => c.Amount ?? 0m); reportRecord.CoInvestmentPaymentsToCollectThisPeriod = paymentValues.Where(p => PeriodCoInvestmentDueFromEmployerPaymentsTypePredicate(p, _appsReturnPeriod)).Sum(c => c.Amount ?? 0m); // NOTE: Additional earigns calc required for this field reportRecord.CoInvestmentOutstandingFromEmplToDate = paymentValues.Where(p => PeriodCoInvestmentDueFromEmployerPaymentsTypePredicateToPeriod(p, _appsReturnPeriod)).Sum(c => c.Amount ?? 0m); // Pull across the ULN if it's not already there (we can pick the first record as the set is matched on learner ref so all ULNs in it will be the same) reportRecord.PaymentUniqueLearnerNumber = paymentValues.FirstOrDefault()?.LearnerUln; // Extract company name string employerName; if ((employerNameDictionary != null) && employerNameDictionary.TryGetValue(paymentValues.FirstOrDefault().ApprenticeshipId ?? 0, out employerName)) { reportRecord.EmployerName = employerName; if ((reportRecord.LearnerEmploymentStatusEmployerId != null) && !employerNamesToIDs.ContainsKey(reportRecord.LearnerEmploymentStatusEmployerId ?? 0)) { employerNamesToIDs.Add(new KeyValuePair <int, string>(reportRecord.LearnerEmploymentStatusEmployerId ?? 0, reportRecord.EmployerName)); } } else { reportRecord.EmployerName = string.Empty; } } if (appsCoInvestmentIlrInfo != null) { var ilrInfo = appsCoInvestmentIlrInfo.Learners? .FirstOrDefault(l => l.LearnRefNumber.CaseInsensitiveEquals(reportRecord.PaymentLearnerReferenceNumber)); if (ilrInfo != null) { var learningDeliveries = ilrInfo.LearningDeliveries.Where(p => p.LearnRefNumber == reportRecord.PaymentLearnerReferenceNumber); if ((learningDeliveries != null) && (learningDeliveries.Count() > 0)) { foreach (var learningDelivery in learningDeliveries) { IReadOnlyCollection <AppFinRecordInfo> currentYearData = learningDelivery.AppFinRecords.Where(x => x.AFinDate >= Generics.BeginningOfYear && x.AFinDate <= Generics.EndOfYear && x.AFinType.CaseInsensitiveEquals("PMR")).ToList(); reportRecord.TotalCoInvestmentCollectedToDate = currentYearData.Where(x => x.AFinCode == 1 || x.AFinCode == 2).Sum(x => x.AFinAmount) - currentYearData.Where(x => x.AFinCode == 3).Sum(x => x.AFinAmount); } } } } // Work out total earnings aECLearningDeliveryDictionary.TryGetValue(reportRecord.PaymentLearnerReferenceNumber, out List <AECLearningDeliveryPeriodisedValuesInfo> ldLearner); aECPriceEpisodeDictionary.TryGetValue(reportRecord.PaymentLearnerReferenceNumber, out List <AECApprenticeshipPriceEpisodePeriodisedValuesInfo> peLearner); reportRecord.TotalEarningsToDate = CalculatePriceEpisodeEarningsToPeriod(ldLearner, peLearner, true, _appsReturnPeriod, reportRecord) + CalculateLearningDeliveryEarningsToPeriod(ldLearner, true, _appsReturnPeriod, reportRecord); reportRecord.TotalEarningsForPeriod = CalculatePriceEpisodeEarningsToPeriod(ldLearner, peLearner, false, _appsReturnPeriod, reportRecord) + CalculateLearningDeliveryEarningsToPeriod(ldLearner, false, _appsReturnPeriod, reportRecord); // Get any missing funding line types from earnings if (string.IsNullOrEmpty(reportRecord.PaymentFundingLineType) && learnerLevelDAsInfo != null && learnerLevelDAsInfo.AECPriceEpisodeFLTsInfo != null) { reportRecord.PaymentFundingLineType = learnerLevelDAsInfo.AECPriceEpisodeFLTsInfo.FirstOrDefault(p => p.LearnerReferenceNumber == reportRecord.PaymentLearnerReferenceNumber)?.PaymentFundingLineType; } // Default any null valued records reportRecord.ESFAPlannedPaymentsThisPeriod = reportRecord.ESFAPlannedPaymentsThisPeriod == null ? 0 : reportRecord.ESFAPlannedPaymentsThisPeriod; reportRecord.PlannedPaymentsToYouToDate = reportRecord.PlannedPaymentsToYouToDate == null ? 0 : reportRecord.PlannedPaymentsToYouToDate; reportRecord.CoInvestmentOutstandingFromEmplToDate = reportRecord.CoInvestmentOutstandingFromEmplToDate == null ? 0 : reportRecord.CoInvestmentOutstandingFromEmplToDate; reportRecord.CoInvestmentPaymentsToCollectThisPeriod = reportRecord.CoInvestmentPaymentsToCollectThisPeriod == null ? 0 : reportRecord.CoInvestmentPaymentsToCollectThisPeriod; reportRecord.TotalCoInvestmentCollectedToDate = reportRecord.TotalCoInvestmentCollectedToDate == null ? 0 : reportRecord.TotalCoInvestmentCollectedToDate; // Work out calculated fields // Issues amount - how much the gap is between what the provider earnt and the payments the ESFA/Employer were planning to give them // Following BR2 - if payments are => earnings, issues amount should be zero if ((reportRecord.ESFAPlannedPaymentsThisPeriod + reportRecord.CoInvestmentPaymentsToCollectThisPeriod) >= reportRecord.TotalEarningsForPeriod) { reportRecord.IssuesAmount = 0; } else { reportRecord.IssuesAmount = (reportRecord.TotalEarningsForPeriod - reportRecord.ESFAPlannedPaymentsThisPeriod - reportRecord.CoInvestmentPaymentsToCollectThisPeriod) * -1; } // Work out what is remaining from employer by subtracting what they a have paid so far from their calculated payments. reportRecord.CoInvestmentOutstandingFromEmplToDate = reportRecord.CoInvestmentOutstandingFromEmplToDate - reportRecord.TotalCoInvestmentCollectedToDate; // Issues for non-payment - worked out in order of priority. // Work out issues (Other) (NOTE: Do this first as lowest priority) if (reportRecord.TotalEarningsForPeriod > reportRecord.CoInvestmentPaymentsToCollectThisPeriod + reportRecord.ESFAPlannedPaymentsThisPeriod) { reportRecord.ReasonForIssues = Reports.LearnerLevelViewReport.ReasonForIssues_Other; } // Work out issues (HBCP) if ((learnerLevelHBCPInfo != null) && learnerLevelHBCPInfo.HBCPModels.Any(p => p.LearnerReferenceNumber == reportRecord.PaymentLearnerReferenceNumber && p.NonPaymentReason == 0 && p.DeliveryPeriod == _appsReturnPeriod)) { reportRecord.ReasonForIssues = Reports.LearnerLevelViewReport.ReasonForIssues_CompletionHoldbackPayment; } // Work out reason for issues (Clawback) if (reportRecord.ESFAPlannedPaymentsThisPeriod < 0) { reportRecord.ReasonForIssues = Reports.LearnerLevelViewReport.ReasonForIssues_Clawback; } // NOTE: As per WI93411, a level of rounding is required before the comparisions can be performed if ((reportRecord.TotalEarningsForPeriod > reportRecord.ESFAPlannedPaymentsThisPeriod + reportRecord.CoInvestmentPaymentsToCollectThisPeriod) && (decimal.Round(reportRecord.TotalEarningsToDate ?? 0, 2) == decimal.Round((reportRecord.PlannedPaymentsToYouToDate ?? 0) + (reportRecord.TotalCoInvestmentCollectedToDate ?? 0) + (reportRecord.CoInvestmentOutstandingFromEmplToDate ?? 0), 2))) { reportRecord.ReasonForIssues = Reports.LearnerLevelViewReport.ReasonForIssues_Clawback; } // If the reason for issue is datalock then we need to set the rule description if ((learnerLevelViewDASDataLockInfo != null) && (learnerLevelViewDASDataLockInfo.DASDataLocks != null)) { var datalock = learnerLevelViewDASDataLockInfo.DASDataLocks .FirstOrDefault(x => x.LearnerReferenceNumber == reportRecord.PaymentLearnerReferenceNumber && x.DeliveryPeriod == _appsReturnPeriod); // Check to see if any records returned if (datalock != null) { // Extract data lock info int datalock_rule_id = datalock.DataLockFailureId; // calculate the rule description string datalockValue = Generics.DLockErrorRuleNamePrefix + datalock_rule_id.ToString("00"); reportRecord.ReasonForIssues = datalockValue; reportRecord.RuleDescription = DataLockValidationMessages.Validations.FirstOrDefault(x => x.RuleId.CaseInsensitiveEquals(datalockValue))?.ErrorMessage; } } // Default any null calculated records reportRecord.IssuesAmount = reportRecord.IssuesAmount == null ? 0 : reportRecord.IssuesAmount; reportRecord.ReasonForIssues = reportRecord.ReasonForIssues == null ? string.Empty : reportRecord.ReasonForIssues; reportRecord.CoInvestmentOutstandingFromEmplToDate = reportRecord.CoInvestmentOutstandingFromEmplToDate == null ? 0 : reportRecord.CoInvestmentOutstandingFromEmplToDate; return(reportRecord); }).ToList(); // Remove the zeroed results learnerLevelViewModelList.RemoveAll(p => p.TotalEarningsToDate == 0 && p.PlannedPaymentsToYouToDate == 0 && p.TotalCoInvestmentCollectedToDate == 0 && p.CoInvestmentOutstandingFromEmplToDate == 0 && p.TotalEarningsForPeriod == 0 && p.ESFAPlannedPaymentsThisPeriod == 0 && p.CoInvestmentPaymentsToCollectThisPeriod == 0 && p.IssuesAmount == 0); // Set the missing employer names foreach (var llvr in learnerLevelViewModelList.Where(w => w.EmployerName == string.Empty || w.EmployerName == null)) { string employerName; if (employerNamesToIDs.TryGetValue(llvr.LearnerEmploymentStatusEmployerId ?? 0, out employerName)) { llvr.EmployerName = employerName; } } } catch (Exception ex) { _logger.LogError("Failed to Build Learner Level View", ex); throw ex; } return(learnerLevelViewModelList); }