public async Task <PaymentsValues> GetPaymentsData(DateTime runStartDateTime, short academicYear,
byte collectionPeriod, bool populateEarnings, IList <long> ukprnList)
{
var sql = ScriptHelpers.GetSqlScriptText(PaymentsQuerySql);
sql = sql.Replace("@ukprnList", string.Join(",", ukprnList));
var paymentsMetrics = new PaymentsValues();
var results = new Dictionary <string, decimal?>(StringComparer.OrdinalIgnoreCase);
using (var connection = GetPaymentsConnectionString())
{
using (var cmd = connection.CreateCommand())
{
cmd.CommandTimeout = configuration.SqlCommandTimeout.Seconds;
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@monthendStartTime", runStartDateTime);
cmd.Parameters.AddWithValue("@startDate", runStartDateTime);
cmd.Parameters.AddWithValue("@collectionPeriod", collectionPeriod);
connection.Open();
using (var reader = await cmd.ExecuteReaderAsync())
{
await reader.ReadAsync();
PopulatePaymentsResults(results, reader);
await reader.NextResultAsync();
await reader.ReadAsync();
PopulatePaymentsResults(results, reader);
await reader.NextResultAsync();
await reader.ReadAsync();
PopulatePaymentsResults(results, reader);
}
}
}
SetMetricsFromResults(results, paymentsMetrics);
return(paymentsMetrics);
}
private static void SetMetricsFromResults(Dictionary <string, decimal?> results, PaymentsValues paymentsMetrics)
{
if (results.Any())
{
paymentsMetrics.RequiredPaymentsThisMonth = results["Required Payments made this month"] ?? 0m;
paymentsMetrics.PaymentsPriorToThisMonthYtd = results["Payments made before this month YTD"] ?? 0m;
paymentsMetrics.ExpectedPaymentsAfterPeriodEnd =
results["Expected Payments YTD after running Period End"] ?? 0m;
paymentsMetrics.TotalPaymentsThisMonth = results["Total payments this month"] ?? 0m;
paymentsMetrics.TotalAct1Ytd = results["Total ACT 1 payments YTD"] ?? 0m;
paymentsMetrics.TotalAct2Ytd = results["Total ACT 2 payments YTD"] ?? 0m;
paymentsMetrics.TotalPaymentsYtd = results["Total payments YTD"] ?? 0m;
paymentsMetrics.HeldBackCompletionThisMonth = results["Held Back Completion Payments this month"] ?? 0m;
paymentsMetrics.DasEarnings = results["DAS Earnings"] ?? 0m;
paymentsMetrics.DataLockedEarnings = results["Datalocked Earnings"] ?? 0m;
paymentsMetrics.DataLockedPayments = results["Datalocked Payments"] ?? 0m;
paymentsMetrics.AdjustedDataLocks = results["Adjusted Datalocks"] ?? 0m;
}
}
