dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #08706
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 2152: Gap analysis - Improved using statementBuilder for setting up query to get the deflated data valu...
------------------------------------------------------------
revno: 2152
committer: Hieu <hieu.hispvietnam@xxxxxxxxx>
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 " +
