public async Task <DcValues> GetDcEarningsData(short academicYear, byte collectionPeriod, List <long> ukprnList) { var sql = ScriptHelpers.GetSqlScriptText(DcEarnings); sql = sql.Replace(UkprnListToken, string.Join(",", ukprnList)); var results = new DcValues(); using (var connection = GetDataStoreConnectionString(academicYear)) { using (var cmd = connection.CreateCommand()) { cmd.CommandTimeout = configuration.SqlCommandTimeout.Seconds; cmd.CommandText = sql; cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@collectionPeriod", collectionPeriod); connection.Open(); using (var reader = await cmd.ExecuteReaderAsync()) { while (await reader.ReadAsync()) { var newValues = new DcContractTypeTotals(); Func <SqlDataReader, int, decimal> getValueAsNullableDecimal = (dataReader, i) => !dataReader.IsDBNull(i) ? (decimal)dataReader.GetValue(i) : (decimal)0; newValues.ContractType = (int)reader[0]; newValues.TT1 = getValueAsNullableDecimal(reader, 1); newValues.TT2 = getValueAsNullableDecimal(reader, 2); newValues.TT3 = getValueAsNullableDecimal(reader, 3); newValues.TT4 = getValueAsNullableDecimal(reader, 4); newValues.TT5 = getValueAsNullableDecimal(reader, 5); newValues.TT6 = getValueAsNullableDecimal(reader, 6); newValues.TT7 = getValueAsNullableDecimal(reader, 7); newValues.TT8 = getValueAsNullableDecimal(reader, 8); newValues.TT9 = getValueAsNullableDecimal(reader, 9); newValues.TT10 = getValueAsNullableDecimal(reader, 10); newValues.TT11 = getValueAsNullableDecimal(reader, 11); newValues.TT12 = getValueAsNullableDecimal(reader, 12); newValues.TT13 = getValueAsNullableDecimal(reader, 13); newValues.TT14 = getValueAsNullableDecimal(reader, 14); newValues.TT15 = getValueAsNullableDecimal(reader, 15); newValues.TT16 = getValueAsNullableDecimal(reader, 16); results.DcContractTypeTotals.Add(newValues); } } } } return(results); }
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); }