public async Task <bool> IsSubmissionEventsCreated()
{
using (var connection = DatabaseConnection.Connection())
{
const string sql = "SELECT (CASE WHEN OBJECT_ID('[Submissions].[SubmissionEvents]', 'U') IS NULL THEN 0 ELSE 1 END)";
return(await connection.ExecuteScalarAsync <int>(sql) == 1);
}
}
public static async Task <bool> IsSubmissionEventsTablePopulated()
{
using (var conn = DatabaseConnection.Connection())
{
const string sql = "SELECT Count(1) FROM [Submissions].[SubmissionEvents]";
var result = await conn.ExecuteScalarAsync <int>(sql).ConfigureAwait(false);
return(result > 0);
}
}
public async Task <bool> AreTablesPopulated()
{
using (var conn = DatabaseConnection.Connection())
{
var sql = "SELECT Count(1) FROM [PaymentsDue].[RequiredPayments]";
var result = await conn.ExecuteScalarAsync <int>(sql).ConfigureAwait(false);
return(result > 10000);
}
}
public async Task <bool> IsCreated()
{
using (var connection = DatabaseConnection.Connection())
{
// doesn't work so well when initialisation script fails mid way through
// wrap creation script in transaction?
const string sql = "SELECT (CASE WHEN OBJECT_ID('[TransferPayments].[SendingAccountId]', 'U') IS NULL THEN 0 ELSE 1 END)";
return(await connection.ExecuteScalarAsync <int>(sql).ConfigureAwait(false) == 1);
}
}
public async Task BulkInsertTransfers(List <ItTransfer> transfers)
{
using (var conn = DatabaseConnection.Connection())
{
await conn.OpenAsync().ConfigureAwait(false);
using (var bcp = new SqlBulkCopy(conn))
using (var reader = ObjectReader.Create(transfers, "TransferId", "SendingAccountId", "ReceivingAccountId",
"RequiredPaymentId", "CommitmentId", "Amount", "TransferType", "CollectionPeriodName", "CollectionPeriodMonth", "CollectionPeriodYear"))
{
bcp.DestinationTableName = "[AccountTransfers].[TransferPayments]";
await bcp.WriteToServerAsync(reader).ConfigureAwait(false);
}
}
}
public async Task BulkInsertEarnings(List <ItEarning> earnings)
{
using (var conn = DatabaseConnection.Connection())
{
await conn.OpenAsync().ConfigureAwait(false);
using (var bcp = new SqlBulkCopy(conn))
using (var reader = ObjectReader.Create(earnings, "RequiredPaymentId", "StartDate",
"PlannedEndDate", "ActualEndDate", "CompletionStatus", "CompletionAmount",
"MonthlyInstallment", "TotalInstallments", "EndpointAssessorId"))
{
bcp.DestinationTableName = "[PaymentsDue].[Earnings]";
await bcp.WriteToServerAsync(reader).ConfigureAwait(false);
}
}
}
public async Task BulkInsertPayments(List <ItPayment> payments)
{
using (var conn = DatabaseConnection.Connection())
{
await conn.OpenAsync().ConfigureAwait(false);
using (var bcp = new SqlBulkCopy(conn))
using (var reader = ObjectReader.Create(payments, "PaymentId", "RequiredPaymentId", "DeliveryMonth",
"DeliveryYear", "CollectionPeriodName", "CollectionPeriodMonth", "CollectionPeriodYear",
"FundingSource", "TransactionType", "Amount"))
{
bcp.DestinationTableName = "[Payments].[Payments]";
await bcp.WriteToServerAsync(reader).ConfigureAwait(false);
}
}
}
public async Task BulkInsertRequiredPayments(List <ItRequiredPayment> requiredPayments)
{
using (var conn = DatabaseConnection.Connection())
{
await conn.OpenAsync().ConfigureAwait(false);
using (var bcp = new SqlBulkCopy(conn))
using (var reader = ObjectReader.Create(requiredPayments, "Id", "CommitmentId",
"CommitmentVersionId", "AccountId", "AccountVersionId", "Uln",
"LearnRefNumber", "AimSeqNumber", "Ukprn", "IlrSubmissionDateTime",
"PriceEpisodeIdentifier", "StandardCode", "ProgrammeType", "FrameworkCode",
"PathwayCode", "ApprenticeshipContractType", "DeliveryMonth", "DeliveryYear",
"CollectionPeriodName", "CollectionPeriodMonth", "CollectionPeriodYear",
"TransactionType", "AmountDue", "SfaContributionPercentage", "FundingLineType",
"UseLevyBalance", "LearnAimRef", "LearningStartDate"))
{
bcp.DestinationTableName = "[PaymentsDue].[RequiredPayments]";
await bcp.WriteToServerAsync(reader).ConfigureAwait(false);
}
}
}
public static async Task BulkInsertSubmissionEvents(List <ItSubmissionEvent> submissionEvents)
{
using (var conn = DatabaseConnection.Connection())
{
await conn.OpenAsync();
using (var bcp = new SqlBulkCopy(conn))
using (var reader = ObjectReader.Create(submissionEvents, "Id", "IlrFileName",
"FileDateTime", "SubmittedDateTime", "ComponentVersionNumber", "Ukprn",
"Uln", "LearnRefNumber", "AimSeqNumber", "PriceEpisodeIdentifier",
"StandardCode", "ProgrammeType", "FrameworkCode", "PathwayCode",
"ActualStartDate", "PlannedEndDate", "ActualEndDate",
"OnProgrammeTotalPrice",
"CompletionTotalPrice", "NiNumber",
"CommitmentId", "AcademicYear",
"EmployerReferenceNumber", "EPAOrgId", "GivenNames", "FamilyName", "CompStatus"))
{
bcp.DestinationTableName = "[Submissions].[SubmissionEvents]";
await bcp.WriteToServerAsync(reader);
}
}
}