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; } }