public void ThenDataHistoryIsUpdatedWithStagingRecords()
{
InsertPendingLoadControl();
HmrcDataTestsRepository
.InsertIntoStaging(new DataStagingRecord {
Record_ID = 1, SchemePAYERef = "999/RD11111"
}).Wait();
HmrcDataTestsRepository
.InsertIntoStaging(new DataStagingRecord {
Record_ID = 2, SchemePAYERef = "999/RD11112"
}).Wait();
HmrcDataTestsRepository.InsertIntoHistory(new DataHistoryRecord {
Record_ID = 100, SourceFile_ID = 99, SchemePAYERef = "999/RD11112"
}).Wait();
HmrcDataTestsRepository.ExecuteLoadData().Wait();
var loadControl = HmrcDataTestsRepository.GetLoadControl().Result;
loadControl.Flag_LoadedSuccessfullyintoHistoryTable.Should().BeTrue();
}
private void InsertQualityTestRecord(bool stopLoadOnError)
{
HmrcDataTestsRepository.InsertIntoDataQualityTests(new DataQualityTestRecord
{
ColumnName = "SchemePAYERef",
ColumnNullable = true,
ColumnPatternMatching = "[0-9][0-9][0-9]/[A-Z]%",
RunColumnTests = true,
StopLoadIfTestPatternMatch = stopLoadOnError
}).Wait();
}
private void SetupDatabase()
{
HmrcDataTestsRepository = new HmrcDataTestsRepository(DataAcceptanceTests.Config.DatabaseConnectionString);
HmrcDataTestsRepository.DeleteLive().Wait();
HmrcDataTestsRepository.DeleteStaging().Wait();
HmrcDataTestsRepository.DeleteLoadControl().Wait();
HmrcDataTestsRepository.DeleteProcessLog().Wait();
HmrcDataTestsRepository.DeleteHistory().Wait();
HmrcDataTestsRepository.DeleteQualityTests().Wait();
HmrcDataTestsRepository.DeleteQualityLog().Wait();
}
public void ThenExistingLogFileDeletingRecordsIsNotCreated()
{
InsertPendingLoadControl();
HmrcDataTestsRepository.ExecuteLoadData().Wait();
var logs = HmrcDataTestsRepository.GetProcessLogs().Result;
logs.Any(l => l.ProcessEventName == "History Table Records Deleted for SourceFile_ID - 1")
.Should().BeFalse();
}
private void InsertQualityTestRecord(bool stopLoadOnError)
{
HmrcDataTestsRepository.InsertIntoDataQualityTests(new DataQualityTestRecord
{
ColumnName = "SchemePAYERef",
ColumnNullable = true,
ColumnType = "NVARCHAR",
ColumnLength = 5,
RunColumnTests = true,
StopLoadIfTestTextLength = stopLoadOnError
}).Wait();
}
private void InsertQualityTestRecord(bool stopLoadOnError)
{
HmrcDataTestsRepository.InsertIntoDataQualityTests(new DataQualityTestRecord
{
ColumnName = "EnglishFraction",
ColumnNullable = true,
ColumnType = "DECIMAL",
ColumnLength = 18,
ColumnPrecision = 5,
RunColumnTests = true,
StopLoadIfTestDecimalPlaces = stopLoadOnError
}).Wait();
}
private void InsertQualityTestRecord(bool stopLoadOnError)
{
HmrcDataTestsRepository.InsertIntoDataQualityTests(new DataQualityTestRecord
{
ColumnName = "TaxPeriodMonth",
ColumnNullable = true,
ColumnType = "INT",
ColumnMinValue = "1",
ColumnMaxValue = "12",
RunColumnTests = true,
StopLoadIfTestValueRange = stopLoadOnError
}).Wait();
}
public void AndValidationHasFlagToStopLoad_ThenValidationFailuresAreLoggedIntoConfigurationDataQualityTestsAndLoadDoesntTakePlace()
{
InsertPendingLoadControl();
HmrcDataTestsRepository.InsertIntoStaging(new DataStagingRecord {
EnglishFraction = "2123312.121212"
}).Wait();
InsertQualityTestRecord(stopLoadOnError: true);
HmrcDataTestsRepository.ExecuteLoadData().Wait();
AssertTestFailLogged("EnglishFraction", "Decimal places do not match specification. Actual: 2123312.121212 Expected Decimal Places: 5");
AssertLoadHalted();
}
public void AndValidationHasFlagToStopLoad_ThenValidationFailuresAreLoggedIntoConfigurationDataQualityTestsAndLoadDoesntTakePlace()
{
InsertPendingLoadControl();
HmrcDataTestsRepository.InsertIntoStaging(new DataStagingRecord {
SchemePAYERef = "DG123"
}).Wait();
InsertQualityTestRecord(true);
HmrcDataTestsRepository.ExecuteLoadData().Wait();
AssertTestFailLogged("SchemePAYERef", "Column pattern does not match specification. Actual: DG123 Expected Pattern: [0-9][0-9][0-9]/[A-Z]%");
AssertLoadHalted();
}
public void ThenValidationFailuresAreLoggedIntoConfigurationDataQualityTests()
{
InsertPendingLoadControl();
HmrcDataTestsRepository.InsertIntoStaging(new DataStagingRecord {
SchemePAYERef = "123456789"
}).Wait();
InsertQualityTestRecord(false);
HmrcDataTestsRepository.ExecuteLoadData().Wait();
AssertTestFailLogged("SchemePAYERef", "String length exceeds Specification. Actual: 9 Against spec size: 5");
AssertLoadCompleted();
}
public void ThenValidationFailuresAreLoggedIntoConfigurationDataQualityTests()
{
InsertPendingLoadControl();
HmrcDataTestsRepository.InsertIntoStaging(new DataStagingRecord {
TaxPeriodMonth = "14"
}).Wait();
InsertQualityTestRecord(false);
HmrcDataTestsRepository.ExecuteLoadData().Wait();
AssertTestFailLogged("TaxPeriodMonth", "Numeric column value outside acceptable values. Actual: 14. Range: 1 - 12");
AssertLoadCompleted();
}
public void AndValidationHasFlagToStopLoad_ThenValidationFailuresAreLoggedIntoConfigurationDataQualityTestsAndLoadDoesntTakePlace()
{
InsertPendingLoadControl();
HmrcDataTestsRepository.InsertIntoStaging(new DataStagingRecord {
TaxPeriodMonth = "14"
}).Wait();
InsertQualityTestRecord(true);
HmrcDataTestsRepository.ExecuteLoadData().Wait();
AssertTestFailLogged("TaxPeriodMonth", "Numeric column value outside acceptable values. Actual: 14. Range: 1 - 12");
AssertLoadHalted();
}
public void AndValidationHasFlagToStopLoad_ThenValidationFailuresAreLoggedIntoConfigurationDataQualityTestsAndLoadDoesntTakePlace()
{
InsertPendingLoadControl();
HmrcDataTestsRepository.InsertIntoStaging(new DataStagingRecord {
SchemePAYERef = "123456789"
}).Wait();
InsertQualityTestRecord(true);
HmrcDataTestsRepository.ExecuteLoadData().Wait();
AssertTestFailLogged("SchemePAYERef", "String length exceeds Specification. Actual: 9 Against spec size: 5");
AssertLoadHalted();
}
public void ThenValidationPassesAreNotLoggedIntoConfigurationDataQualityTests_AndLoadTakesPlace()
{
InsertPendingLoadControl();
HmrcDataTestsRepository.InsertIntoStaging(new DataStagingRecord {
TaxPeriodMonth = "12"
}).Wait();
InsertQualityTestRecord(true);
HmrcDataTestsRepository.ExecuteLoadData().Wait();
HmrcDataTestsRepository.GetQualityLogs().Result.Count().Should().Be(0, because: "there should be no quality test failures");
AssertLoadCompleted();
}
public void ThenValidationPassesAreNotLoggedIntoConfigurationDataQualityTests_AndLiveTableIsInserted()
{
InsertPendingLoadControl();
HmrcDataTestsRepository.InsertIntoStaging(new DataStagingRecord {
EnglishFraction = "678.34567"
}).Wait();
InsertQualityTestRecord(stopLoadOnError: true);
HmrcDataTestsRepository.ExecuteLoadData().Wait();
HmrcDataTestsRepository.GetQualityLogs().Result.Count().Should().Be(0, because: "there should be no quality test failures");
AssertLoadCompleted();
}
public void ThenValidationPassesAreNotLoggedIntoConfigurationDataQualityTests()
{
InsertPendingLoadControl();
HmrcDataTestsRepository.InsertIntoStaging(new DataStagingRecord {
SchemePAYERef = "12345"
}).Wait();
InsertQualityTestRecord(false);
HmrcDataTestsRepository.ExecuteLoadData().Wait();
HmrcDataTestsRepository.GetQualityLogs().Result.Count().Should().Be(0, because: "there should be no quality test failures");
AssertLoadCompleted();
}
public void ThenDataLiveShouldRemainEmpty()
{
HmrcDataTestsRepository.InsertIntoLoadControl(new LoadControlRecord
{
SourceFile_Name = "TestImport.psv",
SourceFile_Status = "Complete",
InsertDate = DateTime.Now
}).Wait();
HmrcDataTestsRepository.InsertIntoStaging(new DataStagingRecord()
{
Record_ID = 1, SchemePAYERef = "999/RD11111"
}).Wait();
HmrcDataTestsRepository.ExecuteLoadData().Wait();
HmrcDataTestsRepository.GetDataLiveCount().Result.Should().Be(0);
}
public void ThenAnErrorShouldBeAddedToProcess_LogTableWithTheCorrectValues()
{
HmrcDataTestsRepository.InsertIntoLoadControl(new LoadControlRecord
{
SourceFile_Name = "TestImport.psv",
SourceFile_Status = "Complete",
InsertDate = DateTime.Now
}).Wait();
HmrcDataTestsRepository.ExecuteLoadData().Wait();
var processLogContents = HmrcDataTestsRepository.GetProcessLogs().Result;
processLogContents.Any(l =>
l.ProcessEventName == "No Source File ID to load" &&
l.ProcessEventDescription == "No records loaded")
.Should().BeTrue();
}
public void ThenExistingSourceFileIdDataInHistoryDeleteLogIsCreated()
{
InsertPendingLoadControl();
HmrcDataTestsRepository.InsertIntoHistory(new DataHistoryRecord {
SourceFile_ID = 1, SchemePAYERef = ""
}).Wait();
HmrcDataTestsRepository.InsertIntoHistory(new DataHistoryRecord {
SourceFile_ID = 1, SchemePAYERef = ""
}).Wait();
HmrcDataTestsRepository.ExecuteLoadData().Wait();
var logs = HmrcDataTestsRepository.GetProcessLogs().Result;
logs.Any(l => l.ProcessEventName == "History Table Records Deleted for SourceFile_ID - 1")
.Should().BeTrue();
}
public void ThenTheNumberOfRecordsInDataLiveShouldMatchDataStaging()
{
InsertPendingLoadControl();
HmrcDataTestsRepository
.InsertIntoStaging(new DataStagingRecord {
Record_ID = 1, SchemePAYERef = "999/RD11111"
}).Wait();
HmrcDataTestsRepository
.InsertIntoStaging(new DataStagingRecord {
Record_ID = 2, SchemePAYERef = "999/RD11112"
}).Wait();
HmrcDataTestsRepository.ExecuteLoadData().Wait();
HmrcDataTestsRepository.GetDataLiveCount().Result.Should().Be(2);
}
public void ThenAMessageShouldBeAddedToProcess_LogTableConfirmingLevyDeclarationSnapshotHasBeenCreated()
{
InsertPendingLoadControl();
HmrcDataTestsRepository
.InsertIntoStaging(new DataStagingRecord {
Record_ID = 1, SchemePAYERef = "999/RD11111"
}).Wait();
HmrcDataTestsRepository
.InsertIntoStaging(new DataStagingRecord {
Record_ID = 2, SchemePAYERef = "999/RD11112"
}).Wait();
HmrcDataTestsRepository.ExecuteLoadData().Wait();
var processLogContents = HmrcDataTestsRepository.GetProcessLogs().Result;
processLogContents.Any(l => l.ProcessEventName == "Created Levy Snapshot").Should().BeTrue();
}
public void ThenDataStagingSourceFileIdIsUpdatedFromLoadControl()
{
InsertPendingLoadControl();
HmrcDataTestsRepository
.InsertIntoStaging(new DataStagingRecord {
Record_ID = 1, SchemePAYERef = "999/RD11111"
}).Wait();
HmrcDataTestsRepository
.InsertIntoStaging(new DataStagingRecord {
Record_ID = 2, SchemePAYERef = "999/RD11112"
}).Wait();
HmrcDataTestsRepository.ExecuteLoadData().Wait();
var stagingRecords = HmrcDataTestsRepository.GetStagingRecords().Result;
stagingRecords.All(r => r.SourceFile_ID == 1).Should().BeTrue();
}
public void ThenLoadControl_SourceFile_Status_IsSetToComplete()
{
InsertPendingLoadControl();
HmrcDataTestsRepository
.InsertIntoStaging(new DataStagingRecord {
Record_ID = 1, SchemePAYERef = "999/RD11111"
}).Wait();
HmrcDataTestsRepository
.InsertIntoStaging(new DataStagingRecord {
Record_ID = 2, SchemePAYERef = "999/RD11112"
}).Wait();
HmrcDataTestsRepository.ExecuteLoadData().Wait();
var loadControl = HmrcDataTestsRepository.GetLoadControl().Result;
loadControl.SourceFile_Status.Should().Be("Complete");
}
public void ThenLoadControl_Flag_Flag_LoadedSuccessfullyintoLiveTable_IsSetToTrue()
{
InsertPendingLoadControl();
HmrcDataTestsRepository
.InsertIntoStaging(new DataStagingRecord {
Record_ID = 1, SchemePAYERef = "999/RD11111"
}).Wait();
HmrcDataTestsRepository
.InsertIntoStaging(new DataStagingRecord {
Record_ID = 2, SchemePAYERef = "999/RD11112"
}).Wait();
HmrcDataTestsRepository.ExecuteLoadData().Wait();
var loadControl = HmrcDataTestsRepository.GetLoadControl().Result;
loadControl.Flag_LoadedSuccessfullyintoLiveTable.Should().BeTrue();
}
public void ThenOnlyNullCessationDatesAreReplacedWith31122999()
{
InsertPendingLoadControl();
HmrcDataTestsRepository
.InsertIntoStaging(new DataStagingRecord {
Record_ID = 1, SchemePAYERef = "999/RD11111", CessationDate = null
}).Wait();
HmrcDataTestsRepository
.InsertIntoStaging(new DataStagingRecord {
Record_ID = 2, SchemePAYERef = "999/RD11112", CessationDate = "20180125"
}).Wait();
HmrcDataTestsRepository.ExecuteLoadData().Wait();
var stagingRecords = HmrcDataTestsRepository.GetStagingRecords().Result;
stagingRecords.Single(r => r.Record_ID == 1).CessationDate.Should().Be("29991231");
stagingRecords.Single(r => r.Record_ID == 2).CessationDate.Should().NotBe("29991231");
}
public void AndValidationHasFlagToStopLoad_ThenValidationFailuresAreLoggedIntoConfigurationDataQualityTestsAndLoadDoesntTakePlace()
{
InsertPendingLoadControl();
HmrcDataTestsRepository.InsertIntoStaging(new DataStagingRecord {
TaxPeriodMonth = "n"
}).Wait();
HmrcDataTestsRepository.InsertIntoDataQualityTests(new DataQualityTestRecord
{
ColumnName = "TaxPeriodMonth",
ColumnNullable = true,
ColumnType = "INT",
RunColumnTests = true,
StopLoadIfTestIsNumeric = true
}).Wait();
HmrcDataTestsRepository.ExecuteLoadData().Wait();
AssertTestFailLogged("TaxPeriodMonth", "Numeric type field not Numeric. Actual: n");
AssertLoadHalted();
}
public void AndValidationHasFlagToStopLoad_ThenValidationPassesAreNotLoggedIntoConfigurationDataQualityTestsAndLoadTakesPlace()
{
InsertPendingLoadControl();
HmrcDataTestsRepository.InsertIntoStaging(new DataStagingRecord {
TaxPeriodMonth = "2"
}).Wait();
HmrcDataTestsRepository.InsertIntoDataQualityTests(new DataQualityTestRecord
{
ColumnName = "TaxPeriodMonth",
ColumnNullable = true,
ColumnType = "INT",
RunColumnTests = true,
StopLoadIfTestIsNumeric = true
}).Wait();
HmrcDataTestsRepository.ExecuteLoadData().Wait();
HmrcDataTestsRepository.GetQualityLogs().Result.Count().Should().Be(0, because: "there should be no quality test failures");
AssertLoadCompleted();
}
public void ThenALevyDeclarationSnapshotForTheCurrentDataShouldBeCreated()
{
var expectedTableName = $"DAS_LevyDeclarations_Snapshot_{DateTime.Today:yyyyMMdd}";
HmrcDataTestsRepository.RemoveLevyDeclarationSnapshotTable(expectedTableName).Wait();
InsertPendingLoadControl();
HmrcDataTestsRepository
.InsertIntoStaging(new DataStagingRecord {
Record_ID = 1, SchemePAYERef = "999/RD11111"
}).Wait();
HmrcDataTestsRepository
.InsertIntoStaging(new DataStagingRecord {
Record_ID = 2, SchemePAYERef = "999/RD11112"
}).Wait();
HmrcDataTestsRepository.ExecuteLoadData().Wait();
var tables = HmrcDataTestsRepository.GetLevyDeclarationSnapshotTableNames().Result;
tables.Any(l => l == expectedTableName).Should().BeTrue();
}
