=== modified file 'dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/dataarchive/jdbc/JdbcDataArchiveStore.java'
--- dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/dataarchive/jdbc/JdbcDataArchiveStore.java	2010-08-19 21:37:22 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/dataarchive/jdbc/JdbcDataArchiveStore.java	2010-09-30 06:06:17 +0000
@@ -32,6 +32,7 @@
 import org.apache.commons.logging.Log;
 import org.apache.commons.logging.LogFactory;
 import org.hisp.dhis.dataarchive.DataArchiveStore;
+import org.hisp.dhis.jdbc.StatementBuilder;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.jdbc.core.JdbcTemplate;
 
@@ -52,6 +53,9 @@
     @Autowired
     private JdbcTemplate jdbcTemplate;
     
+    @Autowired
+    private StatementBuilder statementBuilder;
+    
     // -------------------------------------------------------------------------
     // Dependencies
     // -------------------------------------------------------------------------
@@ -72,12 +76,7 @@
         
         // Delete data from datavalue
         
-        sql =
-            "DELETE FROM datavalue AS d " +
-            "USING period as p " +
-            "WHERE d.periodid=p.periodid " +
-            "AND p.startdate>='" + getMediumDateString( startDate ) + "' " +
-            "AND p.enddate<='" + getMediumDateString( endDate ) + "';";
+        sql = statementBuilder.archiveData( getMediumDateString( startDate ), getMediumDateString( endDate ) );
     
         log.info( sql );        
         jdbcTemplate.execute( sql ); 
@@ -99,12 +98,7 @@
         
         // Delete data from datavalue
         
-        sql =
-            "DELETE FROM datavaluearchive AS a " +
-            "USING period AS p " +
-            "WHERE a.periodid=p.periodid " +
-            "AND p.startdate>='" + getMediumDateString( startDate ) + "' " +
-            "AND p.enddate<='" + getMediumDateString( endDate ) + "';";
+        sql = statementBuilder.unArchiveData( getMediumDateString( startDate ), getMediumDateString( endDate ) );
 
         log.info( sql );        
         jdbcTemplate.execute( sql ); 
@@ -130,13 +124,7 @@
     
     public void deleteRegularOverlappingData()
     {
-        String sql = 
-            "DELETE FROM datavalue AS d " +
-            "USING datavaluearchive AS a " +
-            "WHERE d.dataelementid=a.dataelementid " +
-            "AND d.periodid=a.periodid " +
-            "AND d.sourceid=a.sourceid " +
-            "AND d.categoryoptioncomboid=a.categoryoptioncomboid;";
+        String sql = statementBuilder.deleteRegularOverlappingData();
 
         log.info( sql );        
         jdbcTemplate.execute( sql );
@@ -144,13 +132,7 @@
     
     public void deleteArchivedOverlappingData()
     {
-        String sql = 
-            "DELETE FROM datavaluearchive AS a " +
-            "USING datavalue AS d " +
-            "WHERE a.dataelementid=d.dataelementid " +
-            "AND a.periodid=d.periodid " +
-            "AND a.sourceid=d.sourceid " +
-            "AND a.categoryoptioncomboid=d.categoryoptioncomboid;";
+        String sql = statementBuilder.deleteArchivedOverlappingData();
 
         log.info( sql );        
         jdbcTemplate.execute( sql );
@@ -160,28 +142,14 @@
     {
         // Delete overlaps from datavalue which are older than datavaluearchive
         
-        String sql = 
-            "DELETE FROM datavalue AS d " +
-            "USING datavaluearchive AS a " +
-            "WHERE d.dataelementid=a.dataelementid " +
-            "AND d.periodid=a.periodid " +
-            "AND d.sourceid=a.sourceid " +
-            "AND d.categoryoptioncomboid=a.categoryoptioncomboid " +
-            "AND d.lastupdated<a.lastupdated;";
+        String sql = statementBuilder.deleteOldestOverlappingDataValue();
 
         log.info( sql );        
         jdbcTemplate.execute( sql );
         
         // Delete overlaps from datavaluearchive which are older than datavalue
             
-        sql =            
-            "DELETE FROM datavaluearchive AS a " +
-            "USING datavalue AS d " +
-            "WHERE a.dataelementid=d.dataelementid " +
-            "AND a.periodid=d.periodid " +
-            "AND a.sourceid=d.sourceid " +
-            "AND a.categoryoptioncomboid=d.categoryoptioncomboid " +
-            "AND a.lastupdated<=d.lastupdated;";
+        sql =  statementBuilder.deleteOldestOverlappingArchiveData();          
 
         log.info( sql );        
         jdbcTemplate.execute( sql );

=== modified file 'dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/StatementBuilder.java'
--- dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/StatementBuilder.java	2010-09-23 06:55:57 +0000
+++ dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/StatementBuilder.java	2010-09-30 06:06:17 +0000
@@ -120,5 +120,15 @@
     String getDeflatedDataValues( int dataElementId, String dataElementName, int categoryOptionComboId,
     	String periodIds, int organisationUnitId, String organisationUnitName, int lowerBound, int upperBound );
     	
-    
+    String archiveData( String startDate, String endDate );
+    
+    String unArchiveData( String startDate, String endDate );
+    
+    String deleteRegularOverlappingData();
+    
+    String deleteArchivedOverlappingData();
+    
+    String deleteOldestOverlappingDataValue();
+    
+    String deleteOldestOverlappingArchiveData();
 }

=== modified file 'dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/DerbyStatementBuilder.java'
--- dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/DerbyStatementBuilder.java	2010-09-23 06:55:57 +0000
+++ dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/DerbyStatementBuilder.java	2010-09-30 06:06:17 +0000
@@ -220,4 +220,65 @@
             "AND ( CAST( dv.value AS " + getDoubleColumnType() + " ) < '" + lowerBound + "' " +
             "OR CAST( dv.value AS " + getDoubleColumnType() + " ) > '" + upperBound + "' )";
    }
+    
+   public String archiveData( String startDate, String endDate ){
+        
+        return  "DELETE FROM datavaluearchive AS a " +
+                "USING period AS p " +
+                "WHERE a.periodid=p.periodid " +
+                "AND p.startdate>='" + startDate + "' " +
+                "AND p.enddate<='" + endDate + "'";
+   }
+   
+   public String unArchiveData( String startDate, String endDate ){
+       
+       return  "DELETE FROM datavaluearchive AS a " +
+           "USING period AS p " +
+           "WHERE a.periodid=p.periodid " +
+           "AND p.startdate>='" +  startDate + "' " +
+           "AND p.enddate<='" +  endDate + "'";
+   }
+   
+   public String deleteRegularOverlappingData(){
+       
+       return "DELETE FROM datavalue AS d " +
+           "USING datavaluearchive AS a " +
+           "WHERE d.dataelementid=a.dataelementid " +
+           "AND d.periodid=a.periodid " +
+           "AND d.sourceid=a.sourceid " +
+           "AND d.categoryoptioncomboid=a.categoryoptioncomboid";
+
+   }
+
+   public String deleteArchivedOverlappingData(){
+
+       return "DELETE FROM datavaluearchive AS a " +
+           "USING datavalue AS d " +
+           "WHERE a.dataelementid=d.dataelementid " +
+           "AND a.periodid=d.periodid " +
+           "AND a.sourceid=d.sourceid " +
+           "AND a.categoryoptioncomboid=d.categoryoptioncomboid";
+   }
+
+   public String deleteOldestOverlappingDataValue(){
+       
+       return "DELETE FROM datavalue AS d " +
+           "USING datavaluearchive AS a " +
+           "WHERE d.dataelementid=a.dataelementid " +
+           "AND d.periodid=a.periodid " +
+           "AND d.sourceid=a.sourceid " +
+           "AND d.categoryoptioncomboid=a.categoryoptioncomboid " +
+           "AND d.lastupdated<a.lastupdated";
+   }
+   
+   public String deleteOldestOverlappingArchiveData(){
+       
+       return "DELETE FROM datavaluearchive AS a " +
+           "USING datavalue AS d " +
+           "WHERE a.dataelementid=d.dataelementid " +
+           "AND a.periodid=d.periodid " +
+           "AND a.sourceid=d.sourceid " +
+           "AND a.categoryoptioncomboid=d.categoryoptioncomboid " +
+           "AND a.lastupdated<=d.lastupdated";
+   }
 }

=== modified file 'dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/H2StatementBuilder.java'
--- dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/H2StatementBuilder.java	2010-09-23 06:55:57 +0000
+++ dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/H2StatementBuilder.java	2010-09-30 06:06:17 +0000
@@ -218,4 +218,65 @@
             "AND ( CAST( dv.value AS " + getDoubleColumnType() + " ) < '" + lowerBound + "' " +
             "OR CAST( dv.value AS " + getDoubleColumnType() + " ) > '" + upperBound + "' )";
    }
+    
+   public String archiveData( String startDate, String endDate ){
+        
+        return  "DELETE FROM datavaluearchive AS a " +
+                "USING period AS p " +
+                "WHERE a.periodid=p.periodid " +
+                "AND p.startdate>='" + startDate + "' " +
+                "AND p.enddate<='" + endDate + "'";
+   }
+   
+   public String unArchiveData( String startDate, String endDate ){
+       
+       return  "DELETE FROM datavaluearchive AS a " +
+           "USING period AS p " +
+           "WHERE a.periodid=p.periodid " +
+           "AND p.startdate>='" +  startDate + "' " +
+           "AND p.enddate<='" +  endDate + "'";
+   }
+   
+   public String deleteRegularOverlappingData(){
+       
+       return "DELETE FROM datavalue AS d " +
+           "USING datavaluearchive AS a " +
+           "WHERE d.dataelementid=a.dataelementid " +
+           "AND d.periodid=a.periodid " +
+           "AND d.sourceid=a.sourceid " +
+           "AND d.categoryoptioncomboid=a.categoryoptioncomboid";
+
+   }
+
+   public String deleteArchivedOverlappingData(){
+
+       return "DELETE FROM datavaluearchive AS a " +
+           "USING datavalue AS d " +
+           "WHERE a.dataelementid=d.dataelementid " +
+           "AND a.periodid=d.periodid " +
+           "AND a.sourceid=d.sourceid " +
+           "AND a.categoryoptioncomboid=d.categoryoptioncomboid";
+   }
+
+   public String deleteOldestOverlappingDataValue(){
+       
+       return "DELETE FROM datavalue AS d " +
+           "USING datavaluearchive AS a " +
+           "WHERE d.dataelementid=a.dataelementid " +
+           "AND d.periodid=a.periodid " +
+           "AND d.sourceid=a.sourceid " +
+           "AND d.categoryoptioncomboid=a.categoryoptioncomboid " +
+           "AND d.lastupdated<a.lastupdated";
+   }
+   
+   public String deleteOldestOverlappingArchiveData(){
+       
+       return "DELETE FROM datavaluearchive AS a " +
+           "USING datavalue AS d " +
+           "WHERE a.dataelementid=d.dataelementid " +
+           "AND a.periodid=d.periodid " +
+           "AND a.sourceid=d.sourceid " +
+           "AND a.categoryoptioncomboid=d.categoryoptioncomboid " +
+           "AND a.lastupdated<=d.lastupdated";
+   }
 }

=== modified file 'dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/MySQLStatementBuilder.java'
--- dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/MySQLStatementBuilder.java	2010-09-23 09:32:16 +0000
+++ dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/MySQLStatementBuilder.java	2010-09-30 06:06:17 +0000
@@ -220,5 +220,65 @@
             "AND ( dv.value < '" + lowerBound + "' " +
             "OR  dv.value > '" + upperBound + "' )";
    }
-
+    
+   public String archiveData( String startDate, String endDate ){
+       
+       return "DELETE d FROM datavalue AS d " +
+               "INNER JOIN period as p " +
+               "WHERE d.periodid=p.periodid " +
+               "AND p.startdate>='" + startDate + "' " +
+               "AND p.enddate<='" +  endDate + "'";
+   }
+   
+   public String unArchiveData( String startDate, String endDate ){
+       
+       return  "DELETE a FROM datavaluearchive AS a " +
+           "INNER JOIN period AS p " +
+           "WHERE a.periodid=p.periodid " +
+           "AND p.startdate>='" +  startDate + "' " +
+           "AND p.enddate<='" +  endDate + "'";
+   }
+   
+   public String deleteRegularOverlappingData(){
+       
+       return "DELETE d FROM datavalue AS d " +
+           "INNER JOIN datavaluearchive AS a " +
+           "WHERE d.dataelementid=a.dataelementid " +
+           "AND d.periodid=a.periodid " +
+           "AND d.sourceid=a.sourceid " +
+           "AND d.categoryoptioncomboid=a.categoryoptioncomboid";
+
+   }
+   
+   public String deleteArchivedOverlappingData(){
+
+       return "DELETE a FROM datavaluearchive AS a " +
+           "INNER JOIN datavalue AS d " +
+           "WHERE a.dataelementid=d.dataelementid " +
+           "AND a.periodid=d.periodid " +
+           "AND a.sourceid=d.sourceid " +
+           "AND a.categoryoptioncomboid=d.categoryoptioncomboid";
+   }
+   
+   public String deleteOldestOverlappingDataValue(){
+       
+       return "DELETE d FROM datavalue AS d " +
+           "INNER JOIN datavaluearchive AS a " +
+           "WHERE d.dataelementid=a.dataelementid " +
+           "AND d.periodid=a.periodid " +
+           "AND d.sourceid=a.sourceid " +
+           "AND d.categoryoptioncomboid=a.categoryoptioncomboid " +
+           "AND d.lastupdated<a.lastupdated";
+   }
+   
+   public String deleteOldestOverlappingArchiveData(){
+       
+       return "DELETE a FROM datavaluearchive AS a " +
+           "INNER JOIN datavalue AS d " +
+           "WHERE a.dataelementid=d.dataelementid " +
+           "AND a.periodid=d.periodid " +
+           "AND a.sourceid=d.sourceid " +
+           "AND a.categoryoptioncomboid=d.categoryoptioncomboid " +
+           "AND a.lastupdated<=d.lastupdated";
+   }
 }

=== modified file 'dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/PostgreSQLStatementBuilder.java'
--- dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/PostgreSQLStatementBuilder.java	2010-09-23 06:55:57 +0000
+++ dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/PostgreSQLStatementBuilder.java	2010-09-30 06:06:17 +0000
@@ -220,4 +220,65 @@
            "AND ( CAST( dv.value AS " + getDoubleColumnType() + " ) < '" + lowerBound + "' " +
            "OR CAST( dv.value AS " + getDoubleColumnType() + " ) > '" + upperBound + "' )";
   }
+   
+  public String archiveData( String startDate, String endDate ){
+       
+       return  "DELETE FROM datavaluearchive AS a " +
+           "USING period AS p " +
+           "WHERE a.periodid=p.periodid " +
+           "AND p.startdate>='" + startDate + "' " +
+           "AND p.enddate<='" + endDate + "'";
+  }
+  
+  public String unArchiveData( String startDate, String endDate ){
+      
+      return  "DELETE FROM datavaluearchive AS a " +
+          "USING period AS p " +
+          "WHERE a.periodid=p.periodid " +
+          "AND p.startdate>='" +  startDate + "' " +
+          "AND p.enddate<='" +  endDate + "'";
+  }
+  
+  public String deleteRegularOverlappingData(){
+      
+      return "DELETE FROM datavalue AS d " +
+          "USING datavaluearchive AS a " +
+          "WHERE d.dataelementid=a.dataelementid " +
+          "AND d.periodid=a.periodid " +
+          "AND d.sourceid=a.sourceid " +
+          "AND d.categoryoptioncomboid=a.categoryoptioncomboid";
+
+  }
+  
+  public String deleteArchivedOverlappingData(){
+
+      return "DELETE FROM datavaluearchive AS a " +
+          "USING datavalue AS d " +
+          "WHERE a.dataelementid=d.dataelementid " +
+          "AND a.periodid=d.periodid " +
+          "AND a.sourceid=d.sourceid " +
+          "AND a.categoryoptioncomboid=d.categoryoptioncomboid";
+  }
+  
+  public String deleteOldestOverlappingDataValue(){
+      
+      return "DELETE FROM datavalue AS d " +
+          "USING datavaluearchive AS a " +
+          "WHERE d.dataelementid=a.dataelementid " +
+          "AND d.periodid=a.periodid " +
+          "AND d.sourceid=a.sourceid " +
+          "AND d.categoryoptioncomboid=a.categoryoptioncomboid " +
+          "AND d.lastupdated<a.lastupdated";
+  }
+  
+  public String deleteOldestOverlappingArchiveData(){
+      
+      return "DELETE FROM datavaluearchive AS a " +
+          "USING datavalue AS d " +
+          "WHERE a.dataelementid=d.dataelementid " +
+          "AND a.periodid=d.periodid " +
+          "AND a.sourceid=d.sourceid " +
+          "AND a.categoryoptioncomboid=d.categoryoptioncomboid " +
+          "AND a.lastupdated<=d.lastupdated";
+  }
 }