------------------------------------------------------------ revno: 2152 committer: Hieu <hieu.hispviet...@gmail.com> branch nick: dhis2 timestamp: Tue 2010-11-23 17:55:32 +0700 message: Gap analysis - Improved using statementBuilder for setting up query to get the deflated data value gaps. 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-10-29 12:19:15 +0000 +++ dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/dataanalysis/jdbc/JdbcDataAnalysisStore.java 2010-11-23 10:55:32 +0000 @@ -73,36 +73,43 @@ // 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 ) @@ -114,50 +121,33 @@ 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 = - "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() + "' )"; - + + 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 ); + try - { + { final ResultSet resultSet = holder.getStatement().executeQuery( sql ); - + return mapper.getCollection( resultSet, new DeflatedDataValueNameMinMaxRowMapper() ); } catch ( SQLException ex ) @@ -173,26 +163,23 @@ 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 ) { @@ -202,5 +189,6 @@ { 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-10-30 11:54:24 +0000 +++ dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/StatementBuilder.java 2010-11-23 10:55:32 +0000 @@ -27,6 +27,9 @@ * 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; /** @@ -36,7 +39,7 @@ public interface StatementBuilder { final String QUOTE = "'"; - + /** * Encodes the provided SQL value. * @@ -44,65 +47,73 @@ * @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 ); @@ -110,37 +121,40 @@ 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 periodIds, int organisationUnitId, String organisationUnitName, int lowerBound, int upperBound ); + + String getDeflatedDataValueGaps( DataElement dataElement, DataElementCategoryOptionCombo categoryOptionCombo, + OrganisationUnit organisationUnit, String minValueSql, String maxValueSql, String periodIds ); + 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-10-30 11:54:24 +0000 +++ dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/DerbyStatementBuilder.java 2010-11-23 10:55:32 +0000 @@ -29,7 +29,10 @@ 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; /** @@ -193,7 +196,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 " + @@ -219,27 +222,50 @@ "AND dv.sourceid='" + organisationUnitId + "' " + "AND ( CAST( dv.value AS " + getDoubleColumnType() + " ) < '" + lowerBound + "' " + "OR CAST( dv.value AS " + getDoubleColumnType() + " ) > '" + upperBound + "' )"; - } - - public String archiveData( String startDate, String endDate ){ + } + + 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 " + "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 " + @@ -248,9 +274,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 " + @@ -258,9 +284,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 " + @@ -269,9 +295,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 " + @@ -280,30 +306,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 " + @@ -311,20 +337,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 " + @@ -332,10 +358,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 " + @@ -343,5 +369,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-02 08:28:21 +0000 +++ dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/H2StatementBuilder.java 2010-11-23 10:55:32 +0000 @@ -29,7 +29,10 @@ 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; /** @@ -157,31 +160,44 @@ 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 * 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 1 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 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 + ";"; + { +// 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 + "))"; } public String getStandardDeviation( int dataElementId, int categoryOptionComboId, int organisationUnitId ){ @@ -219,6 +235,29 @@ "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-10-30 11:54:24 +0000 +++ dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/MySQLStatementBuilder.java 2010-11-23 10:55:32 +0000 @@ -29,7 +29,10 @@ 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; /** @@ -219,8 +222,31 @@ "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-10-30 11:54:24 +0000 +++ dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/PostgreSQLStatementBuilder.java 2010-11-23 10:55:32 +0000 @@ -29,7 +29,10 @@ 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,6 +225,29 @@ "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 " +
_______________________________________________ Mailing list: https://launchpad.net/~dhis2-devs Post to : dhis2-devs@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp