public async Task <List <ProviderTotal> > GetAlreadyPaidDataLockProviderTotals(short academicYear, byte collectionPeriod, CancellationToken cancellationToken)
{
var sql =
@"Select
dle.ukprn as Ukprn,
sum(p.Amount) as TotalAmount
from Payments2.dataLockEventNonPayablePeriod npp
join Payments2.dataLockEvent dle on npp.DataLockEventId = dle.EventId
join Payments2.payment p on dle.ukprn = p.ukprn
AND dle.LearningAimFrameworkCode = P.LearningAimFrameworkCode
AND dle.LearningAimPathwayCode = P.LearningAimPathwayCode
AND dle.LearningAimProgrammeType = P.LearningAimProgrammeType
AND dle.LearningAimReference = P.LearningAimReference
AND dle.LearningAimStandardCode = P.LearningAimStandardCode
and dle.learnerreferencenumber = p.learnerreferencenumber
and npp.deliveryperiod = p.deliveryperiod
AND npp.TransactionType = P.TransactionType
AND dle.AcademicYear = p.AcademicYear
where
dle.jobId in (select DcJobid from Payments2.LatestSuccessfulJobs Where AcademicYear = @academicYear AND CollectionPeriod = @collectionPeriod)
and npp.Amount <> 0
and dle.IsPayable = 0
and p.collectionperiod < dle.CollectionPeriod
and p.ContractType = 1
group by
dle.ukprn" ;
return(await AlreadyPaidDataLockProviderTotals.FromSql(sql, new SqlParameter("@academicYear", academicYear), new SqlParameter("@collectionPeriod", collectionPeriod))
.ToListAsync(cancellationToken));
}
public async Task <List <ProviderFundingLineTypeAmounts> > GetAlreadyPaidDataLockProviderTotals(short academicYear, byte collectionPeriod, CancellationToken cancellationToken)
{
var sql = @";WITH unGroupedAmounts AS (Select
dle.ukprn as Ukprn,
CASE WHEN p.LearningAimFundingLineType IN (
'16 - 18 Apprenticeship(From May 2017) Non - Levy Contract(non - procured)',
'16-18 Apprenticeship Non-Levy Contract (procured)',
'16-18 Apprenticeship (Employer on App Service)'
) THEN p.Amount ELSE 0 END AS FundingLineType16To18Amount,
CASE WHEN p.LearningAimFundingLineType IN (
'19+ Apprenticeship (From May 2017) Non-Levy Contract (non-procured)',
'19+ Apprenticeship Non-Levy Contract (procured)',
'19+ Apprenticeship (Employer on App Service)'
) THEN p.Amount ELSE 0 END AS FundingLineType19PlusAmount,
p.Amount AS Total
from Payments2.dataLockEventNonPayablePeriod npp
join Payments2.dataLockEvent dle on npp.DataLockEventId = dle.EventId
join Payments2.payment p on dle.ukprn = p.ukprn
AND dle.LearningAimFrameworkCode = P.LearningAimFrameworkCode
AND dle.LearningAimPathwayCode = P.LearningAimPathwayCode
AND dle.LearningAimProgrammeType = P.LearningAimProgrammeType
AND dle.LearningAimReference = P.LearningAimReference
AND dle.LearningAimStandardCode = P.LearningAimStandardCode
and dle.learnerreferencenumber = p.learnerreferencenumber
and npp.deliveryperiod = p.deliveryperiod
AND npp.TransactionType = P.TransactionType
AND dle.AcademicYear = p.AcademicYear
where
dle.jobId in (select DcJobid from Payments2.LatestSuccessfulJobs Where AcademicYear = @academicYear AND CollectionPeriod = @collectionPeriod)
and npp.Amount <> 0
and dle.IsPayable = 0
and p.collectionperiod < dle.CollectionPeriod
and p.ContractType = 1)
SELECT Ukprn,
SUM(unGroupedAmounts.FundingLineType16To18Amount) AS FundingLineType16To18Amount,
SUM(unGroupedAmounts.FundingLineType19PlusAmount) AS FundingLineType19PlusAmount,
SUM(unGroupedAmounts.Total) AS Total
FROM unGroupedAmounts
GROUP BY unGroupedAmounts.Ukprn" ;
return(await AlreadyPaidDataLockProviderTotals.FromSql(sql, new SqlParameter("@academicYear", academicYear), new SqlParameter("@collectionPeriod", collectionPeriod)).ToListAsync(cancellationToken));
}
