Index: Application/Ringtoets/src/Application.Ringtoets.Migration.Core/EmbeddedResources/Migration_17.3_18.1.sql =================================================================== diff -u -ra6b42278177cd527e6632f93233011ea86ffd979 -rde6bbc678fed36fcc4cd4509635416e85e4c66ba --- Application/Ringtoets/src/Application.Ringtoets.Migration.Core/EmbeddedResources/Migration_17.3_18.1.sql (.../Migration_17.3_18.1.sql) (revision a6b42278177cd527e6632f93233011ea86ffd979) +++ Application/Ringtoets/src/Application.Ringtoets.Migration.Core/EmbeddedResources/Migration_17.3_18.1.sql (.../Migration_17.3_18.1.sql) (revision de6bbc678fed36fcc4cd4509635416e85e4c66ba) @@ -287,7 +287,7 @@ ORDER BY hleB.HydraulicLocationEntityId ); --- Perform the insertion +-- Perform the migration of the location entities INSERT INTO HydraulicLocationEntity ( [HydraulicLocationEntityId], [AssessmentSectionEntityId], @@ -324,6 +324,56 @@ FROM [SOURCEPROJECT].HydraulicLocationEntity AS sp JOIN TempHydraulicLocationCalculationEntity USING (HydraulicLocationEntityId); +-- Update the calculation inputs based on the norm +UPDATE HydraulicLocationCalculationEntity + SET [ShouldIllustrationPointsBeCalculated] = 1 + WHERE HydraulicLocationCalculationEntityId IN ( + SELECT + [HydraulicLocationCalculationEntityId] + FROM [SOURCEPROJECT].HydraulicLocationEntity hle + JOIN TempHydraulicLocationCalculationEntity mapping ON hle.HydraulicLocationEntityId = mapping.HydraulicLocationEntityId + JOIN HydraulicLocationCalculationEntity hlce ON mapping.Calculation2Id = hlce.HydraulicLocationCalculationEntityId + JOIN AssessmentSectionEntity USING (AssessmentSectionEntityId) + WHERE NormativeNormType = 2 AND hle.ShouldDesignWaterlevelIllustrationPointsBeCalculated = 1 + ); + +UPDATE HydraulicLocationCalculationEntity + SET [ShouldIllustrationPointsBeCalculated] = 1 + WHERE HydraulicLocationCalculationEntityId IN ( + SELECT + [HydraulicLocationCalculationEntityId] + FROM [SOURCEPROJECT].HydraulicLocationEntity hle + JOIN TempHydraulicLocationCalculationEntity mapping ON hle.HydraulicLocationEntityId = mapping.HydraulicLocationEntityId + JOIN HydraulicLocationCalculationEntity hlce ON mapping.Calculation3Id = hlce.HydraulicLocationCalculationEntityId + JOIN AssessmentSectionEntity USING (AssessmentSectionEntityId) + WHERE NormativeNormType = 1 AND hle.ShouldDesignWaterlevelIllustrationPointsBeCalculated = 1 + ); + +UPDATE HydraulicLocationCalculationEntity + SET [ShouldIllustrationPointsBeCalculated] = 1 + WHERE HydraulicLocationCalculationEntityId IN ( + SELECT + [HydraulicLocationCalculationEntityId] + FROM [SOURCEPROJECT].HydraulicLocationEntity hle + JOIN TempHydraulicLocationCalculationEntity mapping ON hle.HydraulicLocationEntityId = mapping.HydraulicLocationEntityId + JOIN HydraulicLocationCalculationEntity hlce ON mapping.Calculation6Id = hlce.HydraulicLocationCalculationEntityId + JOIN AssessmentSectionEntity USING (AssessmentSectionEntityId) + WHERE NormativeNormType = 2 AND hle.ShouldWaveHeightIllustrationPointsBeCalculated = 1 + ); + +UPDATE HydraulicLocationCalculationEntity + SET [ShouldIllustrationPointsBeCalculated] = 1 + WHERE HydraulicLocationCalculationEntityId IN ( + SELECT + [HydraulicLocationCalculationEntityId] + FROM [SOURCEPROJECT].HydraulicLocationEntity hle + JOIN TempHydraulicLocationCalculationEntity mapping ON hle.HydraulicLocationEntityId = mapping.HydraulicLocationEntityId + JOIN HydraulicLocationCalculationEntity hlce ON mapping.Calculation7Id = hlce.HydraulicLocationCalculationEntityId + JOIN AssessmentSectionEntity USING (AssessmentSectionEntityId) + WHERE NormativeNormType = 1 AND hle.ShouldWaveHeightIllustrationPointsBeCalculated = 1 + ); + + -- Cleanup DROP TABLE TempHydraulicLocationCalculationEntity; Index: Application/Ringtoets/test/Application.Ringtoets.Migration.Core.Test/test-data/FullTestProject173.rtd =================================================================== diff -u -r1c167e8adf4521008ed654b807a875df32e96244 -rde6bbc678fed36fcc4cd4509635416e85e4c66ba Binary files differ Index: Application/Ringtoets/test/Application.Ringtoets.Storage.Test/IntegrationTests/MigrationTo181IntegrationTest.cs =================================================================== diff -u -rb59831b545bb311d1a7cc09f207f3add659886c5 -rde6bbc678fed36fcc4cd4509635416e85e4c66ba --- Application/Ringtoets/test/Application.Ringtoets.Storage.Test/IntegrationTests/MigrationTo181IntegrationTest.cs (.../MigrationTo181IntegrationTest.cs) (revision b59831b545bb311d1a7cc09f207f3add659886c5) +++ Application/Ringtoets/test/Application.Ringtoets.Storage.Test/IntegrationTests/MigrationTo181IntegrationTest.cs (.../MigrationTo181IntegrationTest.cs) (revision de6bbc678fed36fcc4cd4509635416e85e4c66ba) @@ -257,8 +257,95 @@ "FROM HydraulicLocationCalculationEntity; " + "DETACH SOURCEPROJECT;"; reader.AssertReturnedDataIsValid(validateNrOfHydraulicBoundaryCalculationEntities); + + AssertMigratedHydraulicLocationCalculations(reader, sourceFilePath); } + private static void AssertMigratedHydraulicLocationCalculations(MigratedDatabaseReader reader, string sourceFilePath) + { + AssertNewHydraulicLocationCalculations(reader, sourceFilePath, 1); + string validateDesignWaterLevelCalculationsWithSignalingNorm = + $"ATTACH DATABASE \"{sourceFilePath}\" AS SOURCEPROJECT; " + + "SELECT COUNT() = " + + "( " + + "SELECT COUNT() " + + "FROM [SOURCEPROJECT].HydraulicLocationEntity " + + "JOIN AssessmentSectionEntity USING (AssessmentSectionEntityId) " + + "WHERE NormativeNormType = 2 " + + ") " + + "FROM HydraulicLocationEntity AS NEW " + + "JOIN [SOURCEPROJECT].HydraulicLocationEntity AS OLD ON OLD.HydraulicLocationEntityId = NEW.HydraulicLocationEntityId " + + "JOIN HydraulicLocationCalculationEntity hlce ON NEW.HydraulicLocationCalculationEntity2Id = hlce.HydraulicLocationCalculationEntityId " + + "JOIN AssessmentSectionEntity USING (AssessmentSectionEntityId) " + + "WHERE hlce.ShouldIllustrationPointsBeCalculated = OLD.ShouldDesignWaterLevelIllustrationPointsBeCalculated AND NormativeNormType = 2;" + + "DETACH DATABASE SOURCEPROJECT;"; + reader.AssertReturnedDataIsValid(validateDesignWaterLevelCalculationsWithSignalingNorm); + string validateDesignWaterLevelCalculationsWithLowerLimitNorm = + $"ATTACH DATABASE \"{sourceFilePath}\" AS SOURCEPROJECT; " + + "SELECT COUNT() = " + + "( " + + "SELECT COUNT() " + + "FROM [SOURCEPROJECT].HydraulicLocationEntity " + + "JOIN AssessmentSectionEntity USING (AssessmentSectionEntityId) " + + "WHERE NormativeNormType = 1 " + + ") " + + "FROM HydraulicLocationEntity AS NEW " + + "JOIN [SOURCEPROJECT].HydraulicLocationEntity AS OLD ON OLD.HydraulicLocationEntityId = NEW.HydraulicLocationEntityId " + + "JOIN HydraulicLocationCalculationEntity hlce ON NEW.HydraulicLocationCalculationEntity3Id = hlce.HydraulicLocationCalculationEntityId " + + "JOIN AssessmentSectionEntity USING (AssessmentSectionEntityId) " + + "WHERE hlce.ShouldIllustrationPointsBeCalculated = OLD.ShouldWaveHeightIllustrationPointsBeCalculated AND NormativeNormType = 1;" + + "DETACH DATABASE SOURCEPROJECT;"; + reader.AssertReturnedDataIsValid(validateDesignWaterLevelCalculationsWithLowerLimitNorm); + AssertNewHydraulicLocationCalculations(reader, sourceFilePath, 4); + + AssertNewHydraulicLocationCalculations(reader, sourceFilePath, 5); + string validateWaveHeightCalculationsWithSignalingNorm = + $"ATTACH DATABASE \"{sourceFilePath}\" AS SOURCEPROJECT; " + + "SELECT COUNT() = " + + "( " + + "SELECT COUNT() " + + "FROM [SOURCEPROJECT].HydraulicLocationEntity " + + "JOIN AssessmentSectionEntity USING (AssessmentSectionEntityId) " + + "WHERE NormativeNormType = 2 " + + ") " + + "FROM HydraulicLocationEntity AS NEW " + + "JOIN [SOURCEPROJECT].HydraulicLocationEntity AS OLD ON OLD.HydraulicLocationEntityId = NEW.HydraulicLocationEntityId " + + "JOIN HydraulicLocationCalculationEntity hlce ON NEW.HydraulicLocationCalculationEntity6Id = hlce.HydraulicLocationCalculationEntityId " + + "JOIN AssessmentSectionEntity USING (AssessmentSectionEntityId) " + + "WHERE hlce.ShouldIllustrationPointsBeCalculated = OLD.ShouldWaveHeightIllustrationPointsBeCalculated AND NormativeNormType = 2;" + + "DETACH DATABASE SOURCEPROJECT;"; + reader.AssertReturnedDataIsValid(validateWaveHeightCalculationsWithSignalingNorm); + string validateWaveHeightCalculationsWithLowerLimitNorm = + $"ATTACH DATABASE \"{sourceFilePath}\" AS SOURCEPROJECT; " + + "SELECT COUNT() = " + + "( " + + "SELECT COUNT() " + + "FROM [SOURCEPROJECT].HydraulicLocationEntity " + + "JOIN AssessmentSectionEntity USING (AssessmentSectionEntityId) " + + "WHERE NormativeNormType = 1 " + + ") " + + "FROM HydraulicLocationEntity AS NEW " + + "JOIN [SOURCEPROJECT].HydraulicLocationEntity AS OLD ON OLD.HydraulicLocationEntityId = NEW.HydraulicLocationEntityId " + + "JOIN HydraulicLocationCalculationEntity hlce ON NEW.HydraulicLocationCalculationEntity7Id = hlce.HydraulicLocationCalculationEntityId " + + "JOIN AssessmentSectionEntity USING (AssessmentSectionEntityId) " + + "WHERE hlce.ShouldIllustrationPointsBeCalculated = OLD.ShouldDesignWaterLevelIllustrationPointsBeCalculated AND NormativeNormType = 1;" + + "DETACH DATABASE SOURCEPROJECT;"; + reader.AssertReturnedDataIsValid(validateWaveHeightCalculationsWithLowerLimitNorm); + AssertNewHydraulicLocationCalculations(reader, sourceFilePath, 8); + } + + private static void AssertNewHydraulicLocationCalculations(MigratedDatabaseReader reader, string sourceFilePath, int entityCalculationNumber) + { + string validateNewHydraulicCalculations = + $"ATTACH DATABASE \"{sourceFilePath}\" AS SOURCEPROJECT; " + + "SELECT COUNT() = (SELECT COUNT() FROM [SOURCEPROJECT].HydraulicLocationEntity) " + + "FROM HydraulicLocationEntity AS NEW " + + $"INNER JOIN HydraulicLocationCalculationEntity hlce ON NEW.HydraulicLocationCalculationEntity{entityCalculationNumber}Id = hlce.HydraulicLocationCalculationEntityId " + + "WHERE hlce.ShouldIllustrationPointsBeCalculated = 0;" + + "DETACH DATABASE SOURCEPROJECT;"; + reader.AssertReturnedDataIsValid(validateNewHydraulicCalculations); + } + private static void AssertPipingSoilLayers(MigratedDatabaseReader reader) { const string validateBelowPhreaticLevel =