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