public async Task <List <ProviderTotal> > GetDataLockedEarningsTotals(short academicYear, byte collectionPeriod, CancellationToken cancellationToken) { var latestSuccessfulJobIds = LatestSuccessfulJobs.AsNoTracking() .Where(j => j.AcademicYear == academicYear && j.CollectionPeriod == collectionPeriod) .Select(x => x.DcJobId); return(await DataLockEventNonPayablePeriods .Where(period => period.Amount != 0 && latestSuccessfulJobIds.Contains(period.DataLockEvent.JobId)) .GroupBy(x => x.DataLockEvent.Ukprn) .Select(x => new ProviderTotal() { Ukprn = x.Key, TotalAmount = x.Sum(period => period.Amount) }) .ToListAsync(cancellationToken)); }
public async Task <List <ProviderContractTypeAmounts> > GetHeldBackCompletionPaymentTotals(short academicYear, byte collectionPeriod, CancellationToken cancellationToken) { var latestSuccessfulJobIds = LatestSuccessfulJobs.AsNoTracking() .Where(j => j.AcademicYear == academicYear && j.CollectionPeriod == collectionPeriod) .Select(x => x.DcJobId); var providerMetrics = await RequiredPaymentEvents .AsNoTracking() .Where(rp => latestSuccessfulJobIds.Contains(rp.JobId) && rp.NonPaymentReason != null && rp.NonPaymentReason == NonPaymentReason.InsufficientEmployerContribution) .GroupBy(rp => new { rp.Ukprn, rp.ContractType }) .Select(group => new { group.Key.Ukprn, group.Key.ContractType, Amount = group.Sum(requiredPaymentInGroup => requiredPaymentInGroup.Amount) }) .ToListAsync(cancellationToken) .ConfigureAwait(false); var uniqueUkprns = providerMetrics.Select(x => x.Ukprn).Distinct(); var results = new List <ProviderContractTypeAmounts>(); foreach (var ukprn in uniqueUkprns) { results.Add(new ProviderContractTypeAmounts { Ukprn = ukprn, ContractType1 = providerMetrics.FirstOrDefault(providerMetric => providerMetric.ContractType == ContractType.Act1 && providerMetric.Ukprn == ukprn)?.Amount ?? 0, ContractType2 = providerMetrics.FirstOrDefault(providerMetric => providerMetric.ContractType == ContractType.Act2 && providerMetric.Ukprn == ukprn)?.Amount ?? 0, }); } return(results); }