Author: fdesbois Date: 2010-04-19 23:19:47 +0000 (Mon, 19 Apr 2010) New Revision: 461 Log: Update calcul on realTidesValue, seems to have some erroneous data. Modified: trunk/wao-business/src/main/java/fr/ifremer/wao/PostgresMigrationCallback.java Modified: trunk/wao-business/src/main/java/fr/ifremer/wao/PostgresMigrationCallback.java =================================================================== --- trunk/wao-business/src/main/java/fr/ifremer/wao/PostgresMigrationCallback.java 2010-04-19 22:29:26 UTC (rev 460) +++ trunk/wao-business/src/main/java/fr/ifremer/wao/PostgresMigrationCallback.java 2010-04-19 23:19:47 UTC (rev 461) @@ -85,11 +85,11 @@ @Override protected void createSampleMonthEstimatedTidesColumn_1_2b(List<String> queries) { queries.add("ALTER TABLE SampleMonth ADD estimatedTidesValue INTEGER;"); + // Calculate estimatedTidesValue for all existing contacts // estimatedTidesValue = nbContacts with state BOARDING_DONE // sampleRows must match + tideBeginDate must match with periodDate - queries.add("UPDATE SampleMonth S SET " + - "estimatedTidesValue = (" + + String subquery = " SELECT COUNT(*) FROM Contact C" + " WHERE C.sampleRow = S.sampleRow" + " AND C.state = " + ContactState.BOARDING_DONE.ordinal() + @@ -98,8 +98,13 @@ " AND date_part('year', C.tideBeginDate) = " + "date_part('year', S.periodDate)" + " AND (C.validationProgram != FALSE OR " + - " C.validationProgram IS NULL)" + - "), " + + " C.validationProgram IS NULL)"; + + queries.add("UPDATE SampleMonth S SET " + + "estimatedTidesValue = (" + subquery + "), " + + // Recalcul realTides (may have erroneous data) + "realTidesValue = (" + subquery + + " AND C.validationCompany = TRUE" + "), " + "topiaVersion = S.topiaVersion + 1;"); }