dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #08715
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 2159: Reverted commit r 2152
------------------------------------------------------------
revno: 2159
committer: Lars Helge Overland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Wed 2010-11-24 10:49:54 +0100
message:
Reverted commit r 2152
modified:
dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/dataanalysis/jdbc/JdbcDataAnalysisStore.java
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/DerbyStatementBuilder.java
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/MySQLStatementBuilder.java
dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/PostgreSQLStatementBuilder.java
--
lp:dhis2
https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk
Your team DHIS 2 developers is subscribed to branch lp:dhis2.
To unsubscribe from this branch go to https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk/+edit-subscription
=== modified file 'dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/dataanalysis/jdbc/JdbcDataAnalysisStore.java'
--- dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/dataanalysis/jdbc/JdbcDataAnalysisStore.java 2010-11-23 10:55:32 +0000
+++ dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/dataanalysis/jdbc/JdbcDataAnalysisStore.java 2010-11-24 09:49:54 +0000
@@ -73,43 +73,36 @@
// OutlierAnalysisStore implementation
// -------------------------------------------------------------------------
- public Double getStandardDeviation( DataElement dataElement, DataElementCategoryOptionCombo categoryOptionCombo,
- OrganisationUnit organisationUnit )
- {
- final String sql = statementBuilder.getStandardDeviation( dataElement.getId(), categoryOptionCombo.getId(),
- organisationUnit.getId() );
-
- return statementManager.getHolder().queryForDouble( sql );
- }
-
- public Double getAverage( DataElement dataElement, DataElementCategoryOptionCombo categoryOptionCombo,
- OrganisationUnit organisationUnit )
- {
- final String sql = statementBuilder.getStandardDeviation( dataElement.getId(), categoryOptionCombo.getId(),
- organisationUnit.getId() );
-
- return statementManager.getHolder().queryForDouble( sql );
- }
-
- public Collection<DeflatedDataValue> getDeflatedDataValues( DataElement dataElement,
- DataElementCategoryOptionCombo categoryOptionCombo, Collection<Period> periods,
- OrganisationUnit organisationUnit, int lowerBound, int upperBound )
+ public Double getStandardDeviation( DataElement dataElement, DataElementCategoryOptionCombo categoryOptionCombo, OrganisationUnit organisationUnit )
+ {
+ final String sql = statementBuilder.getStandardDeviation(dataElement.getId(), categoryOptionCombo.getId(), organisationUnit.getId() );
+
+ return statementManager.getHolder().queryForDouble( sql );
+ }
+
+ public Double getAverage( DataElement dataElement, DataElementCategoryOptionCombo categoryOptionCombo, OrganisationUnit organisationUnit )
+ {
+ final String sql = statementBuilder.getStandardDeviation(dataElement.getId(), categoryOptionCombo.getId(), organisationUnit.getId() );
+
+ return statementManager.getHolder().queryForDouble( sql );
+ }
+
+ public Collection<DeflatedDataValue> getDeflatedDataValues( DataElement dataElement, DataElementCategoryOptionCombo categoryOptionCombo,
+ Collection<Period> periods, OrganisationUnit organisationUnit, int lowerBound, int upperBound )
{
final StatementHolder holder = statementManager.getHolder();
-
+
final ObjectMapper<DeflatedDataValue> mapper = new ObjectMapper<DeflatedDataValue>();
-
- final String periodIds = TextUtils.getCommaDelimitedString( ConversionUtils.getIdentifiers( Period.class,
- periods ) );
-
- final String sql = statementBuilder.getDeflatedDataValues( dataElement.getId(), dataElement.getName(),
- categoryOptionCombo.getId(), periodIds, organisationUnit.getId(), organisationUnit.getName(), lowerBound,
- upperBound );
-
+
+ final String periodIds = TextUtils.getCommaDelimitedString( ConversionUtils.getIdentifiers( Period.class, periods ) );
+
+ final String sql = statementBuilder.getDeflatedDataValues( dataElement.getId(), dataElement.getName(), categoryOptionCombo.getId(),
+ periodIds, organisationUnit.getId(), organisationUnit.getName(), lowerBound, upperBound );
+
try
- {
+ {
final ResultSet resultSet = holder.getStatement().executeQuery( sql );
-
+
return mapper.getCollection( resultSet, new DeflatedDataValueNameMinMaxRowMapper() );
}
catch ( SQLException ex )
@@ -121,33 +114,50 @@
holder.close();
}
}
-
- public Collection<DeflatedDataValue> getDeflatedDataValueGaps( DataElement dataElement,
- DataElementCategoryOptionCombo categoryOptionCombo, Collection<Period> periods,
- OrganisationUnit organisationUnit )
+
+ public Collection<DeflatedDataValue> getDeflatedDataValueGaps( DataElement dataElement, DataElementCategoryOptionCombo categoryOptionCombo,
+ Collection<Period> periods, OrganisationUnit organisationUnit )
{
final StatementHolder holder = statementManager.getHolder();
final ObjectMapper<DeflatedDataValue> mapper = new ObjectMapper<DeflatedDataValue>();
-
- final String periodIds = TextUtils.getCommaDelimitedString( ConversionUtils.getIdentifiers( Period.class,
- periods ) );
-
- final String minValueSql = "SELECT minvalue FROM minmaxdataelement " + "WHERE sourceid=' "
- + organisationUnit.getId() + "' " + "AND dataelementid='" + dataElement.getId() + "' "
- + "AND categoryoptioncomboid='" + categoryOptionCombo.getId() + "'";
-
- final String maxValueSql = "SELECT maxvalue FROM minmaxdataelement " + "WHERE sourceid=' "
- + organisationUnit.getId() + "' " + "AND dataelementid='" + dataElement.getId() + "' "
- + "AND categoryoptioncomboid='" + categoryOptionCombo.getId() + "'";
-
- final String sql = statementBuilder.getDeflatedDataValueGaps( dataElement, categoryOptionCombo,
- organisationUnit, minValueSql, maxValueSql, periodIds );
-
+
+ final String periodIds = TextUtils.getCommaDelimitedString( ConversionUtils.getIdentifiers( Period.class, periods ) );
+
+ final String minValueSql =
+ "SELECT minvalue FROM minmaxdataelement " +
+ "WHERE sourceid=' " + organisationUnit.getId() + "' " +
+ "AND dataelementid='" + dataElement.getId() + "' " +
+ "AND categoryoptioncomboid='" + categoryOptionCombo.getId() + "'";
+
+ final String maxValueSql =
+ "SELECT maxvalue FROM minmaxdataelement " +
+ "WHERE sourceid=' " + organisationUnit.getId() + "' " +
+ "AND dataelementid='" + dataElement.getId() + "' " +
+ "AND categoryoptioncomboid='" + categoryOptionCombo.getId() + "'";
+
+ final String sql =
+ "SELECT '" + dataElement.getId() + "' AS dataelementid, pe.periodid, " +
+ "'" + organisationUnit.getId() + "' AS sourceid, '" + categoryOptionCombo.getId() + "' AS categoryoptioncomboid, " +
+ "'' AS value, '' AS storedby, '1900-01-01' AS lastupdated, '' AS comment, false AS followup, " +
+ "( " + minValueSql + " ) AS minvalue, ( " + maxValueSql + " ) AS maxvalue, " +
+ statementBuilder.encode( dataElement.getName() ) + " AS dataelementname, pt.name AS periodtypename, pe.startdate, pe.enddate, " +
+ statementBuilder.encode( organisationUnit.getName() ) + " AS sourcename, " +
+ statementBuilder.encode( categoryOptionCombo.getName() ) + " AS categoryoptioncomboname " + //TODO join?
+ "FROM period AS pe " +
+ "JOIN periodtype AS pt USING (periodtypeid) " +
+ "WHERE periodid IN (" + periodIds + ") " +
+ "AND periodtypeid='" + dataElement.getPeriodType().getId() + "' " +
+ "AND periodid NOT IN ( " +
+ "SELECT periodid FROM datavalue " +
+ "WHERE dataelementid='" + dataElement.getId() + "' " +
+ "AND categoryoptioncomboid='" + categoryOptionCombo.getId() + "' " +
+ "AND sourceid='" + organisationUnit.getId() + "' )";
+
try
- {
+ {
final ResultSet resultSet = holder.getStatement().executeQuery( sql );
-
+
return mapper.getCollection( resultSet, new DeflatedDataValueNameMinMaxRowMapper() );
}
catch ( SQLException ex )
@@ -163,23 +173,26 @@
public Collection<DeflatedDataValue> getDataValuesMarkedForFollowup()
{
final StatementHolder holder = statementManager.getHolder();
-
- final String sql = "SELECT dv.dataelementid, dv.periodid, dv.sourceid, dv.categoryoptioncomboid, dv.value, "
- + "dv.storedby, dv.lastupdated, dv.comment, dv.followup, mm.minvalue, mm.maxvalue, de.name AS dataelementname, "
- + "pe.startdate, pe.enddate, pt.name AS periodtypename, ou.name AS sourcename, cc.categoryoptioncomboname "
- + "FROM datavalue AS dv "
- + "LEFT JOIN minmaxdataelement AS mm using (sourceid, dataelementid, categoryoptioncomboid) "
- + "JOIN dataelement AS de using (dataelementid) " + "JOIN period AS pe using (periodid) "
- + "JOIN periodtype AS pt using (periodtypeid) " + "JOIN source AS sr using (sourceid) "
- + "LEFT JOIN organisationunit AS ou on ou.organisationunitid=sr.sourceid "
- + "LEFT JOIN _categoryoptioncomboname AS cc using (categoryoptioncomboid) " + "WHERE dv.followup=true";
-
+
+ final String sql =
+ "SELECT dv.dataelementid, dv.periodid, dv.sourceid, dv.categoryoptioncomboid, dv.value, " +
+ "dv.storedby, dv.lastupdated, dv.comment, dv.followup, mm.minvalue, mm.maxvalue, de.name AS dataelementname, " +
+ "pe.startdate, pe.enddate, pt.name AS periodtypename, ou.name AS sourcename, cc.categoryoptioncomboname " +
+ "FROM datavalue AS dv " +
+ "LEFT JOIN minmaxdataelement AS mm using (sourceid, dataelementid, categoryoptioncomboid) " +
+ "JOIN dataelement AS de using (dataelementid) " +
+ "JOIN period AS pe using (periodid) " +
+ "JOIN periodtype AS pt using (periodtypeid) " +
+ "JOIN source AS sr using (sourceid) " +
+ "LEFT JOIN organisationunit AS ou on ou.organisationunitid=sr.sourceid " +
+ "LEFT JOIN _categoryoptioncomboname AS cc using (categoryoptioncomboid) " +
+ "WHERE dv.followup=true";
+
try
{
final ResultSet resultSet = holder.getStatement().executeQuery( sql );
-
- return new ObjectMapper<DeflatedDataValue>().getCollection( resultSet,
- new DeflatedDataValueNameMinMaxRowMapper() );
+
+ return new ObjectMapper<DeflatedDataValue>().getCollection( resultSet, new DeflatedDataValueNameMinMaxRowMapper() );
}
catch ( SQLException ex )
{
@@ -189,6 +202,5 @@
{
holder.close();
}
- }
-
+ }
}
=== 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-11-23 10:55:32 +0000
+++ dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/StatementBuilder.java 2010-11-24 09:49:54 +0000
@@ -27,9 +27,6 @@
* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
-import org.hisp.dhis.dataelement.DataElement;
-import org.hisp.dhis.dataelement.DataElementCategoryOptionCombo;
-import org.hisp.dhis.organisationunit.OrganisationUnit;
import org.hisp.dhis.period.Period;
/**
@@ -39,7 +36,7 @@
public interface StatementBuilder
{
final String QUOTE = "'";
-
+
/**
* Encodes the provided SQL value.
*
@@ -47,73 +44,65 @@
* @return the SQL encoded value.
*/
String encode( String value );
-
+
/**
* Returns the name of a double column type.
- *
* @return the name of a double column type.
*/
String getDoubleColumnType();
-
+
/**
* Creates a SELECT statement returning the identifier of the given Period.
*
- * @param period the Period to use in the statement.
+ * @param period the Period to use in the statement.
* @return a SELECT statement returning the identifier of the given Period.
*/
String getPeriodIdentifierStatement( Period period );
-
+
/**
* Creates a create table statement fot the aggregated datavalue table.
- *
* @return a create table statement fot the aggregated datavalue table.
*/
String getCreateAggregatedDataValueTable();
-
+
/**
* Creates a create table statement for the aggregated indicatorvalue table.
- *
* @return a create table statement for the aggregated indicatorvalue table.
- */
+ */
String getCreateAggregatedIndicatorTable();
/**
- * Creates a create table statement for the aggregated datasetcompleteness
- * table.
- *
- * @return a create table statement for the aggregated datasetcompleteness
- * table.
+ * Creates a create table statement for the aggregated datasetcompleteness table.
+ * @return a create table statement for the aggregated datasetcompleteness table.
*/
String getCreateDataSetCompletenessTable();
-
+
/**
* Creates a create index statement for the datavalue table.
- *
* @return a create index statement for the datavalue table.
*/
String getCreateDataValueIndex();
-
+
/**
* Creates a delete datavalue statement.
- *
* @return a delete datavalue statement.
*/
String getDeleteZeroDataValues();
-
+
/**
* Returns the maximum number of columns in a table.
*
* @return the maximum number of columns in a table.
*/
int getMaximumNumberOfColumns();
-
+
/**
- * Drop Dataset foreign key for DataEntryForm table
- *
+ * Drop Dataset foreign key for DataEntryForm table
+ *
* @return
*/
String getDropDatasetForeignKeyForDataEntryFormTable();
-
+
String getMoveDataValueToDestination( int sourceId, int destinationId );
String getSummarizeDestinationAndSourceWhereMatching( int sourceId, int destinationId );
@@ -121,40 +110,37 @@
String getMoveFromSourceToDestination( int destDataElementId, int destCategoryOptionComboId,
int sourceDataElementId, int sourceCategoryOptionComboId );
- String getUpdateDestination( int destDataElementId, int destCategoryOptionComboId, int sourceDataElementId,
- int sourceCategoryOptionComboId );
-
+ String getUpdateDestination( int destDataElementId, int destCategoryOptionComboId,
+ int sourceDataElementId, int sourceCategoryOptionComboId );
+
String getStandardDeviation( int dataElementId, int categoryOptionComboId, int organisationUnitId );
-
+
String getAverage( int dataElementId, int categoryOptionComboId, int organisationUnitId );
-
+
String getDeflatedDataValues( int dataElementId, String dataElementName, int categoryOptionComboId,
- String periodIds, int organisationUnitId, String organisationUnitName, int lowerBound, int upperBound );
-
- String getDeflatedDataValueGaps( DataElement dataElement, DataElementCategoryOptionCombo categoryOptionCombo,
- OrganisationUnit organisationUnit, String minValueSql, String maxValueSql, String periodIds );
-
+ 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();
-
- String archivePatientData( String startDate, String endDate );
-
- String unArchivePatientData( String startDate, String endDate );
-
+
+ String archivePatientData ( String startDate, String endDate );
+
+ String unArchivePatientData ( String startDate, String endDate );
+
String deleteRegularOverlappingPatientData();
-
+
String deleteArchivedOverlappingPatientData();
-
+
String deleteOldestOverlappingPatientDataValue();
-
+
String deleteOldestOverlappingPatientArchiveData();
}
=== 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-11-23 10:55:32 +0000
+++ dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/DerbyStatementBuilder.java 2010-11-24 09:49:54 +0000
@@ -29,10 +29,7 @@
import static org.hisp.dhis.system.util.DateUtils.getSqlDateString;
-import org.hisp.dhis.dataelement.DataElement;
-import org.hisp.dhis.dataelement.DataElementCategoryOptionCombo;
import org.hisp.dhis.jdbc.StatementBuilder;
-import org.hisp.dhis.organisationunit.OrganisationUnit;
import org.hisp.dhis.period.Period;
/**
@@ -196,7 +193,7 @@
"AND categoryoptioncomboid='" + categoryOptionComboId + "' " +
"AND sourceid='" + organisationUnitId + "'";
- }
+ }
public String getAverage( int dataElementId, int categoryOptionComboId, int organisationUnitId ){
return "SELECT AVG( CAST( value AS " + getDoubleColumnType() + " ) ) FROM datavalue " +
@@ -222,50 +219,27 @@
"AND dv.sourceid='" + organisationUnitId + "' " +
"AND ( CAST( dv.value AS " + getDoubleColumnType() + " ) < '" + lowerBound + "' " +
"OR CAST( dv.value AS " + getDoubleColumnType() + " ) > '" + upperBound + "' )";
- }
-
- public String getDeflatedDataValueGaps( DataElement dataElement, DataElementCategoryOptionCombo categoryOptionCombo,
- OrganisationUnit organisationUnit, String minValueSql, String maxValueSql, String periodIds )
- {
- return "SELECT '" + dataElement.getId() + "' AS dataelementid, pe.periodid, " + "'"
- + organisationUnit.getId() + "' AS sourceid, '" + categoryOptionCombo.getId()
- + "' AS categoryoptioncomboid, "
- + "'' AS value, '' AS storedby, '1900-01-01' AS lastupdated, '' AS comment, false AS followup, "
- + "( " + minValueSql + " ) AS minvalue, ( " + maxValueSql + " ) AS maxvalue, "
- + encode( dataElement.getName() ) + " AS dataelementname, pt.name AS periodtypename, pe.startdate, pe.enddate, "
- + encode( organisationUnit.getName() ) + " AS sourcename, "
- + encode( categoryOptionCombo.getName() ) + " AS categoryoptioncomboname "
- + // TODO join?
- "FROM period AS pe "
- + "JOIN periodtype AS pt USING (periodtypeid) "
- + "WHERE pe.periodid IN (" + periodIds + ") "
- + "AND pe.periodtypeid='" + dataElement.getPeriodType().getId() + "' "
- + "AND pe.periodid NOT IN ( "
- + "SELECT DISTINCT periodid FROM datavalue "
- + "WHERE dataelementid='" + dataElement.getId() + "' "
- + "AND categoryoptioncomboid='" + categoryOptionCombo.getId() + "' "
- + "AND sourceid='" + organisationUnit.getId() + "' )";
- }
-
- public String archiveData( String startDate, String endDate ){
+ }
+
+ 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 ){
+ 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(){
+ public String deleteRegularOverlappingData(){
return "DELETE FROM datavalue AS d " +
"USING datavaluearchive AS a " +
@@ -274,9 +248,9 @@
"AND d.sourceid=a.sourceid " +
"AND d.categoryoptioncomboid=a.categoryoptioncomboid";
- }
+ }
- public String deleteArchivedOverlappingData(){
+ public String deleteArchivedOverlappingData(){
return "DELETE FROM datavaluearchive AS a " +
"USING datavalue AS d " +
@@ -284,9 +258,9 @@
"AND a.periodid=d.periodid " +
"AND a.sourceid=d.sourceid " +
"AND a.categoryoptioncomboid=d.categoryoptioncomboid";
- }
+ }
- public String deleteOldestOverlappingDataValue(){
+ public String deleteOldestOverlappingDataValue(){
return "DELETE FROM datavalue AS d " +
"USING datavaluearchive AS a " +
@@ -295,9 +269,9 @@
"AND d.sourceid=a.sourceid " +
"AND d.categoryoptioncomboid=a.categoryoptioncomboid " +
"AND d.lastupdated<a.lastupdated";
- }
+ }
- public String deleteOldestOverlappingArchiveData(){
+ public String deleteOldestOverlappingArchiveData(){
return "DELETE FROM datavaluearchive AS a " +
"USING datavalue AS d " +
@@ -306,30 +280,30 @@
"AND a.sourceid=d.sourceid " +
"AND a.categoryoptioncomboid=d.categoryoptioncomboid " +
"AND a.lastupdated<=d.lastupdated";
- }
+ }
- public String archivePatientData ( String startDate, String endDate )
- {
+ public String archivePatientData ( String startDate, String endDate )
+ {
return "DELETE FROM patientdatavalue AS pdv "
+ "USING programstageinstance AS psi , programinstance AS pi "
+ "WHERE pdv.programstageinstanceid = psi.programstageinstanceid "
+ "AND pi.programinstanceid = psi.programinstanceid "
+ "WHERE pi.enddate >= '" + startDate + "' "
+ "AND pi.enddate <= '" + endDate + "';";
- }
+ }
- public String unArchivePatientData ( String startDate, String endDate )
- {
+ public String unArchivePatientData ( String startDate, String endDate )
+ {
return "DELETE FROM patientdatavaluearchive AS pdv "
+ "USING programstageinstance AS psi , programinstance AS pi "
+ "WHERE pdv.programstageinstanceid = psi.programstageinstanceid "
+ "AND pi.programinstanceid = psi.programinstanceid "
+ "WHERE pi.enddate >= '" + startDate + "' "
+ "AND pi.enddate <= '" + endDate + "';";
- }
+ }
- public String deleteRegularOverlappingPatientData()
- {
+ public String deleteRegularOverlappingPatientData()
+ {
return "DELETE FROM patientdatavalue AS d " +
"USING patientdatavaluearchive AS a " +
"WHERE d.programstageinstanceid=a.programstageinstanceid " +
@@ -337,20 +311,20 @@
"AND d.organisationunitid=a.organisationunitid " +
"AND d.categoryoptioncomboid=a.categoryoptioncomboid " +
"AND d.timestamp<a.timestamp;";
- }
+ }
- public String deleteArchivedOverlappingPatientData()
- {
+ public String deleteArchivedOverlappingPatientData()
+ {
return "DELETE FROM patientdatavaluearchive AS a " +
"USING patientdatavalue AS d " +
"WHERE d.programstageinstanceid=a.programstageinstanceid " +
"AND d.dataelementid=a.dataelementid " +
"AND d.organisationunitid=a.organisationunitid " +
"AND d.categoryoptioncomboid=a.categoryoptioncomboid ";
- }
+ }
- public String deleteOldestOverlappingPatientDataValue()
- {
+ public String deleteOldestOverlappingPatientDataValue()
+ {
return "DELETE FROM patientdatavalue AS d " +
"USING patientdatavaluearchive AS a " +
"WHERE d.programstageinstanceid=a.programstageinstanceid " +
@@ -358,10 +332,10 @@
"AND d.organisationunitid=a.organisationunitid " +
"AND d.categoryoptioncomboid=a.categoryoptioncomboid " +
"AND d.timestamp<a.timestamp;";
- }
+ }
- public String deleteOldestOverlappingPatientArchiveData()
- {
+ public String deleteOldestOverlappingPatientArchiveData()
+ {
return "DELETE FROM patientdatavalue AS d " +
"USING patientdatavaluearchive AS a " +
"WHERE d.programstageinstanceid=a.programstageinstanceid " +
@@ -369,5 +343,5 @@
"AND d.organisationunitid=a.organisationunitid " +
"AND d.categoryoptioncomboid=a.categoryoptioncomboid " +
"AND a.timestamp<=d.timestamp;";
- }
+ }
}
=== 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-11-23 10:55:32 +0000
+++ dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/H2StatementBuilder.java 2010-11-24 09:49:54 +0000
@@ -29,10 +29,7 @@
import static org.hisp.dhis.system.util.DateUtils.getSqlDateString;
-import org.hisp.dhis.dataelement.DataElement;
-import org.hisp.dhis.dataelement.DataElementCategoryOptionCombo;
import org.hisp.dhis.jdbc.StatementBuilder;
-import org.hisp.dhis.organisationunit.OrganisationUnit;
import org.hisp.dhis.period.Period;
/**
@@ -160,44 +157,31 @@
public String getUpdateDestination( int destDataElementId, int destCategoryOptionComboId,
int sourceDataElementId, int sourceCategoryOptionComboId )
{
- return "UPDATE datavalue AS d1 "
- + "SET dataelementid=" + destDataElementId + ", categoryoptioncomboid=" + destCategoryOptionComboId + " "
- + "WHERE dataelementid=" + sourceDataElementId + " "
- + "AND categoryoptioncomboid=" + sourceCategoryOptionComboId + " "
- + "AND NOT EXISTS ( "
- + "SELECT 1 FROM datavalue AS d2 "
- + "WHERE d2.dataelementid=" + destDataElementId + " "
- + "AND d2.categoryoptioncomboid=" + destCategoryOptionComboId + " "
- + "AND d1.periodid=d2.periodid "
- + "AND d1.sourceid=d2.sourceid );";
+ return "UPDATE datavalue AS d1 SET dataelementid=" + destDataElementId + ", categoryoptioncomboid="
+ + destCategoryOptionComboId + " " + "WHERE dataelementid=" + sourceDataElementId
+ + " and categoryoptioncomboid=" + sourceCategoryOptionComboId + " " + "AND NOT EXISTS ( "
+ + "SELECT * FROM datavalue AS d2 " + "WHERE d2.dataelementid=" + destDataElementId + " "
+ + "AND d2.categoryoptioncomboid=" + destCategoryOptionComboId + " " + "AND d1.periodid=d2.periodid "
+ + "AND d1.sourceid=d2.sourceid );";
}
@Override
public String getMoveFromSourceToDestination( int destDataElementId, int destCategoryOptionComboId,
int sourceDataElementId, int sourceCategoryOptionComboId )
- {
-// return "UPDATE datavalue AS d1 "
-// + "SET (value,storedby,lastupdated,comment,followup) IN "
-// + "(SELECT d2.value,d2.storedby,d2.lastupdated,d2.comment,d2.followup "
-// + "FROM datavalue AS d2 "
-// + "WHERE (d1.periodid=d2.periodid) "
-// + "AND (d1.sourceid=d2.sourceid) "
-// + "AND (d1.lastupdated<d2.lastupdated) "
-// + "AND (d1.dataelementid=" + destDataElementId + ") "
-// + "AND (d1.categoryoptioncomboid=" + destCategoryOptionComboId + ") "
-// + "AND (d2.dataelementid=" + sourceDataElementId + ") "
-// + "AND (d2.categoryoptioncomboid=" + sourceCategoryOptionComboId + "))";
- System.out.println("hey");
- return "UPDATE datavalue AS d1 "
- + "SET value=(SELECT d2.value FROM datavalue AS d2 "
- + "WHERE (d1.periodid=d2.periodid) "
- + "AND (d1.sourceid=d2.sourceid) "
- + "AND (d1.lastupdated<d2.lastupdated) "
- + "AND (d1.dataelementid=" + destDataElementId + ") "
- + "AND (d1.categoryoptioncomboid=" + destCategoryOptionComboId + ") "
- + "AND (d2.dataelementid=" + sourceDataElementId + ") "
- + "AND (d2.categoryoptioncomboid=" + sourceCategoryOptionComboId + "))";
+ {
+ return "UPDATE datavalue SET value=d2.value,storedby=d2.storedby,lastupdated=d2.lastupdated,comment=d2.comment,followup=d2.followup "
+ + "FROM datavalue AS d2 "
+ + "WHERE datavalue.periodid=d2.periodid "
+ + "AND datavalue.sourceid=d2.sourceid "
+ + "AND datavalue.lastupdated<d2.lastupdated "
+ + "AND datavalue.dataelementid="
+ + destDataElementId
+ + " AND datavalue.categoryoptioncomboid="
+ + destCategoryOptionComboId
+ + " "
+ + "AND d2.dataelementid="
+ + sourceDataElementId + " AND d2.categoryoptioncomboid=" + sourceCategoryOptionComboId + ";";
}
public String getStandardDeviation( int dataElementId, int categoryOptionComboId, int organisationUnitId ){
@@ -235,29 +219,6 @@
"OR CAST( dv.value AS " + getDoubleColumnType() + " ) > '" + upperBound + "' )";
}
- public String getDeflatedDataValueGaps( DataElement dataElement, DataElementCategoryOptionCombo categoryOptionCombo,
- OrganisationUnit organisationUnit, String minValueSql, String maxValueSql, String periodIds )
- {
- return "SELECT '" + dataElement.getId() + "' AS dataelementid, pe.periodid, " + "'"
- + organisationUnit.getId() + "' AS sourceid, '" + categoryOptionCombo.getId()
- + "' AS categoryoptioncomboid, "
- + "'' AS value, '' AS storedby, '1900-01-01' AS lastupdated, '' AS comment, false AS followup, "
- + "( " + minValueSql + " ) AS minvalue, ( " + maxValueSql + " ) AS maxvalue, "
- + encode( dataElement.getName() ) + " AS dataelementname, pt.name AS periodtypename, pe.startdate, pe.enddate, "
- + encode( organisationUnit.getName() ) + " AS sourcename, "
- + encode( categoryOptionCombo.getName() ) + " AS categoryoptioncomboname "
- + // TODO join?
- "FROM period AS pe "
- + "JOIN periodtype AS pt ON (pe.periodtypeid = pt.periodtypeid) "
- + "WHERE pe.periodid IN (" + periodIds + ") "
- + "AND pe.periodtypeid='" + dataElement.getPeriodType().getId() + "' "
- + "AND pe.periodid NOT IN ( "
- + "SELECT DISTINCT periodid FROM datavalue "
- + "WHERE dataelementid='" + dataElement.getId() + "' "
- + "AND categoryoptioncomboid='" + categoryOptionCombo.getId() + "' "
- + "AND sourceid='" + organisationUnit.getId() + "' )";
- }
-
public String archiveData( String startDate, String endDate )
{
return "DELETE FROM datavalue AS a " +
=== 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-11-23 10:55:32 +0000
+++ dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/MySQLStatementBuilder.java 2010-11-24 09:49:54 +0000
@@ -29,10 +29,7 @@
import static org.hisp.dhis.system.util.DateUtils.getSqlDateString;
-import org.hisp.dhis.dataelement.DataElement;
-import org.hisp.dhis.dataelement.DataElementCategoryOptionCombo;
import org.hisp.dhis.jdbc.StatementBuilder;
-import org.hisp.dhis.organisationunit.OrganisationUnit;
import org.hisp.dhis.period.Period;
/**
@@ -222,31 +219,8 @@
"AND dv.sourceid='" + organisationUnitId + "' " +
"AND ( dv.value < '" + lowerBound + "' " +
"OR dv.value > '" + upperBound + "' )";
- }
+ }
- public String getDeflatedDataValueGaps( DataElement dataElement, DataElementCategoryOptionCombo categoryOptionCombo,
- OrganisationUnit organisationUnit, String minValueSql, String maxValueSql, String periodIds )
- {
- return "SELECT '" + dataElement.getId() + "' AS dataelementid, pe.periodid, " + "'"
- + organisationUnit.getId() + "' AS sourceid, '" + categoryOptionCombo.getId()
- + "' AS categoryoptioncomboid, "
- + "'' AS value, '' AS storedby, '1900-01-01' AS lastupdated, '' AS comment, false AS followup, "
- + "( " + minValueSql + " ) AS minvalue, ( " + maxValueSql + " ) AS maxvalue, "
- + encode( dataElement.getName() ) + " AS dataelementname, pt.name AS periodtypename, pe.startdate, pe.enddate, "
- + encode( organisationUnit.getName() ) + " AS sourcename, "
- + encode( categoryOptionCombo.getName() ) + " AS categoryoptioncomboname "
- + // TODO join?
- "FROM period AS pe "
- + "JOIN periodtype AS pt USING (periodtypeid) "
- + "WHERE pe.periodid IN (" + periodIds + ") "
- + "AND pe.periodtypeid='" + dataElement.getPeriodType().getId() + "' "
- + "AND pe.periodid NOT IN ( "
- + "SELECT DISTINCT periodid FROM datavalue "
- + "WHERE dataelementid='" + dataElement.getId() + "' "
- + "AND categoryoptioncomboid='" + categoryOptionCombo.getId() + "' "
- + "AND sourceid='" + organisationUnit.getId() + "' )";
- }
-
public String archiveData( String startDate, String endDate )
{
return "DELETE d FROM datavalue AS d " +
=== 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-11-23 10:55:32 +0000
+++ dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/PostgreSQLStatementBuilder.java 2010-11-24 09:49:54 +0000
@@ -29,10 +29,7 @@
import static org.hisp.dhis.system.util.DateUtils.getSqlDateString;
-import org.hisp.dhis.dataelement.DataElement;
-import org.hisp.dhis.dataelement.DataElementCategoryOptionCombo;
import org.hisp.dhis.jdbc.StatementBuilder;
-import org.hisp.dhis.organisationunit.OrganisationUnit;
import org.hisp.dhis.period.Period;
/**
@@ -225,29 +222,6 @@
"OR CAST( dv.value AS " + getDoubleColumnType() + " ) > '" + upperBound + "' )";
}
- public String getDeflatedDataValueGaps( DataElement dataElement, DataElementCategoryOptionCombo categoryOptionCombo,
- OrganisationUnit organisationUnit, String minValueSql, String maxValueSql, String periodIds )
- {
- return "SELECT '" + dataElement.getId() + "' AS dataelementid, pe.periodid, " + "'"
- + organisationUnit.getId() + "' AS sourceid, '" + categoryOptionCombo.getId()
- + "' AS categoryoptioncomboid, "
- + "'' AS value, '' AS storedby, '1900-01-01' AS lastupdated, '' AS comment, false AS followup, "
- + "( " + minValueSql + " ) AS minvalue, ( " + maxValueSql + " ) AS maxvalue, "
- + encode( dataElement.getName() ) + " AS dataelementname, pt.name AS periodtypename, pe.startdate, pe.enddate, "
- + encode( organisationUnit.getName() ) + " AS sourcename, "
- + encode( categoryOptionCombo.getName() ) + " AS categoryoptioncomboname "
- + // TODO join?
- "FROM period AS pe "
- + "JOIN periodtype AS pt USING (periodtypeid) "
- + "WHERE pe.periodid IN (" + periodIds + ") "
- + "AND pe.periodtypeid='" + dataElement.getPeriodType().getId() + "' "
- + "AND pe.periodid NOT IN ( "
- + "SELECT DISTINCT periodid FROM datavalue "
- + "WHERE dataelementid='" + dataElement.getId() + "' "
- + "AND categoryoptioncomboid='" + categoryOptionCombo.getId() + "' "
- + "AND sourceid='" + organisationUnit.getId() + "' )";
- }
-
public String archiveData( String startDate, String endDate )
{
return "DELETE FROM datavaluearchive AS a " +
