dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #22670
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 10968: Fixed bug when to run Patient Aggregate Tabular report.
------------------------------------------------------------
revno: 10968
committer: Tran Chau <tran.hispvietnam@xxxxxxxxx>
branch nick: dhis2
timestamp: Fri 2013-05-24 12:22:57 +0700
message:
Fixed bug when to run Patient Aggregate Tabular report.
modified:
dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/program/hibernate/HibernateProgramStageInstanceStore.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-patient/src/main/java/org/hisp/dhis/program/hibernate/HibernateProgramStageInstanceStore.java'
--- dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/program/hibernate/HibernateProgramStageInstanceStore.java 2013-05-24 04:19:07 +0000
+++ dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/program/hibernate/HibernateProgramStageInstanceStore.java 2013-05-24 05:22:57 +0000
@@ -1161,7 +1161,7 @@
if ( orgunitIds.size() == 0 )
{
- sql += "(SELECT ( cast( \'0\' as " + statementBuilder.getDoubleColumnType() + " )) ";
+ sql += "(SELECT 0 ";
}
else
{
@@ -1254,7 +1254,7 @@
if ( orgunitIds.size() == 0 )
{
- sql += "(SELECT \'0\' ";
+ sql += "(SELECT 0 ";
}
else
{
@@ -1300,7 +1300,7 @@
}
sql = sql.substring( 0, sql.length() - 6 ) + " ";
- sql += " ORDER BY orgunit asc ";
+ sql += " ) ORDER BY orgunit asc ";
if ( limit != null )
{
sql += "LIMIT " + limit;
@@ -1343,7 +1343,7 @@
Collection<Integer> orgunitIds = getServiceOrgunit( allOrgunitIds, period );
if ( orgunitIds.size() == 0 )
{
- sql += "(SELECT \'0\' ";
+ sql += "(SELECT 0 ";
}
else
{
@@ -1389,7 +1389,7 @@
}
}
- sql = sql.substring( 0, sql.length() - 10 );
+ sql = sql.substring( 0, sql.length() - 10 ) + " ) ";
if ( limit != null )
{
sql += " LIMIT " + limit;
@@ -1420,7 +1420,7 @@
Collection<Integer> orgunitIds = getServiceOrgunit( allOrgunitIds, period );
if ( orgunitIds.size() == 0 )
{
- sql += "(SELECT \'0\' ";
+ sql += "(SELECT 0 ";
}
else
{
@@ -1539,7 +1539,7 @@
{
for ( String deValue : deValues )
{
- sql += "(SELECT \'0\' as \"" + deValue + "\",";
+ sql += "(SELECT 0 as \"" + deValue + "\",";
}
}
else
@@ -1631,7 +1631,7 @@
Collection<Integer> orgunitIds = getServiceOrgunit( allOrgunitIds, period );
if ( orgunitIds.size() == 0 )
{
- sql += "(SELECT \'0\' ";
+ sql += "(SELECT 0 ";
}
else
{
@@ -1699,86 +1699,156 @@
allOrgunitIds.addAll( getOrganisationUnits( root, facilityLB ) );
}
- String dataValueSql = "SELECT DISTINCT(pdv.value) ";
- dataValueSql += "FROM patientdatavalue pdv JOIN programstageinstance psi";
- dataValueSql += " ON pdv.programstageinstanceid=psi.programstageinstanceid ";
- dataValueSql += "WHERE pdv.dataelementid=" + deGroupBy + " AND ";
- dataValueSql += " psi.organisationunitid in ( " + TextUtils.getCommaDelimitedString( allOrgunitIds )
- + " ) AND ";
- dataValueSql += " psi.programstageid=" + programStage.getId() + " AND ( ";
-
- dataValueSql += " ( psi.executiondate >= '" + format.formatDate( period.getStartDate() ) + "' AND ";
- dataValueSql += " psi.executiondate <= '" + format.formatDate( period.getEndDate() ) + "') OR ";
-
- dataValueSql = dataValueSql.substring( 0, dataValueSql.length() - 3 );
- dataValueSql += ") ORDER BY value asc";
-
- Collection<String> deValues = new HashSet<String>();
- try
- {
- deValues = jdbcTemplate.query( dataValueSql, new RowMapper<String>()
- {
- public String mapRow( ResultSet rs, int rowNum )
- throws SQLException
- {
- return rs.getString( 1 );
- }
- } );
- }
- catch ( Exception ex )
- {
- ex.printStackTrace();
- }
-
- if ( deValues.size() > 0 )
- {
- for ( Integer root : roots )
- {
- allOrgunitIds = getOrganisationUnits( root, facilityLB );
- Collection<Integer> orgunitIds = getServiceOrgunit( allOrgunitIds, period );
-
+ if ( allOrgunitIds.size() > 0 )
+ {
+ String dataValueSql = "SELECT DISTINCT(pdv.value) ";
+ dataValueSql += "FROM patientdatavalue pdv JOIN programstageinstance psi";
+ dataValueSql += " ON pdv.programstageinstanceid=psi.programstageinstanceid ";
+ dataValueSql += "WHERE pdv.dataelementid=" + deGroupBy + " AND ";
+ dataValueSql += " psi.organisationunitid in ( " + TextUtils.getCommaDelimitedString( allOrgunitIds )
+ + " ) AND ";
+ dataValueSql += " psi.programstageid=" + programStage.getId() + " AND ( ";
+
+ dataValueSql += " ( psi.executiondate >= '" + format.formatDate( period.getStartDate() ) + "' AND ";
+ dataValueSql += " psi.executiondate <= '" + format.formatDate( period.getEndDate() ) + "') OR ";
+
+ dataValueSql = dataValueSql.substring( 0, dataValueSql.length() - 3 );
+ dataValueSql += ") ORDER BY value asc";
+
+ Collection<String> deValues = new HashSet<String>();
+ try
+ {
+ deValues = jdbcTemplate.query( dataValueSql, new RowMapper<String>()
+ {
+ public String mapRow( ResultSet rs, int rowNum )
+ throws SQLException
+ {
+ return rs.getString( 1 );
+ }
+ } );
+ }
+ catch ( Exception ex )
+ {
+ ex.printStackTrace();
+ }
+
+ if ( deValues.size() > 0 )
+ {
+ for ( Integer root : roots )
+ {
+ allOrgunitIds = getOrganisationUnits( root, facilityLB );
+ Collection<Integer> orgunitIds = getServiceOrgunit( allOrgunitIds, period );
+
+ sql += "(SELECT ";
+ sql += "( SELECT ou.name FROM organisationunit ou WHERE ou.organisationunitid=" + root
+ + " ) as orgunit, ";
+ for ( String deValue : deValues )
+ {
+ if ( aggregateType.equals( PatientAggregateReport.AGGREGATE_TYPE_COUNT ) )
+ {
+ sql += "(SELECT count(DISTINCT psi_1.programstageinstanceid) ";
+ }
+ else
+ {
+ sql += "(SELECT " + aggregateType + "( cast( value as DOUBLE PRECISION )) ";
+ }
+ sql += "FROM patientdatavalue pdv_1 ";
+ sql += " inner join programstageinstance psi_1 ";
+ sql += " on psi_1.programstageinstanceid = pdv_1.programstageinstanceid ";
+ sql += "WHERE ";
+ sql += " psi_1.executiondate >= '" + format.formatDate( period.getStartDate() )
+ + "' AND ";
+ sql += " psi_1.executiondate <= '" + format.formatDate( period.getEndDate() ) + "' AND ";
+ sql += " psi_1.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgunitIds )
+ + ") AND ";
+ if ( deSum != null )
+ {
+ sql += " dataelementid=" + deSum + " AND ";
+ }
+ if ( useCompletedEvents != null )
+ {
+ sql += " psi_1.completed = " + useCompletedEvents + " AND ";
+ }
+ sql += " psi_1.programstageid=" + programStage.getId() + " ";
+ sql += filterSQL + " AND ";
+ sql += " (SELECT value FROM patientdatavalue ";
+ sql += " WHERE programstageinstanceid=psi_1.programstageinstanceid AND ";
+ sql += " dataelementid= pdv_1.dataelementid AND ";
+ sql += " dataelementid=" + deGroupBy + " ) = '" + deValue + "' ";
+ sql += " LIMIT 1 ) as \"" + deValue + "\",";
+ }
+
+ sql = sql.substring( 0, sql.length() - 1 ) + " ) ";
+ sql += " UNION ";
+ }
+
+ sql = sql.substring( 0, sql.length() - 6 );
+ if ( limit != null )
+ {
+ sql += " LIMIT " + limit;
+ }
+ }
+ }
+
+ return sql;
+ }
+
+ /**
+ * Aggregate report Position Orgunit Rows - Period Filter - Data Columns
+ *
+ **/
+ private String getAggregateReportSQL7WithoutGroup( ProgramStage programStage, Collection<Integer> roots,
+ String facilityLB, String filterSQL, Integer deSum, Period period, String aggregateType, Integer limit,
+ Boolean useCompletedEvents, I18nFormat format )
+ {
+
+ String sql = "";
+
+ for ( Integer root : roots )
+ {
+ Collection<Integer> allOrgunitIds = getOrganisationUnits( root, facilityLB );
+ Collection<Integer> orgunitIds = getServiceOrgunit( allOrgunitIds, period );
+
+ if ( orgunitIds.size() > 0 )
+ {
sql += "(SELECT ";
sql += "( SELECT ou.name FROM organisationunit ou WHERE ou.organisationunitid=" + root
+ " ) as orgunit, ";
- for ( String deValue : deValues )
- {
- if ( aggregateType.equals( PatientAggregateReport.AGGREGATE_TYPE_COUNT ) )
- {
- sql += "(SELECT count(DISTINCT psi_1.programstageinstanceid) ";
- }
- else
- {
- sql += "(SELECT " + aggregateType + "( cast( value as DOUBLE PRECISION )) ";
- }
- sql += "FROM patientdatavalue pdv_1 ";
- sql += " inner join programstageinstance psi_1 ";
- sql += " on psi_1.programstageinstanceid = pdv_1.programstageinstanceid ";
- sql += "WHERE ";
- sql += " psi_1.executiondate >= '" + format.formatDate( period.getStartDate() ) + "' AND ";
- sql += " psi_1.executiondate <= '" + format.formatDate( period.getEndDate() ) + "' AND ";
- sql += " psi_1.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgunitIds )
- + ") AND ";
- if ( deSum != null )
- {
- sql += " dataelementid=" + deSum + " AND ";
- }
- if ( useCompletedEvents != null )
- {
- sql += " psi_1.completed = " + useCompletedEvents + " AND ";
- }
- sql += " psi_1.programstageid=" + programStage.getId() + " ";
- sql += filterSQL + " AND ";
- sql += " (SELECT value FROM patientdatavalue ";
- sql += " WHERE programstageinstanceid=psi_1.programstageinstanceid AND ";
- sql += " dataelementid= pdv_1.dataelementid AND ";
- sql += " dataelementid=" + deGroupBy + " ) = '" + deValue + "' ";
- sql += " LIMIT 1 ) as \"" + deValue + "\",";
- }
-
- sql = sql.substring( 0, sql.length() - 1 ) + " ) ";
+
+ if ( aggregateType.equals( PatientAggregateReport.AGGREGATE_TYPE_COUNT ) )
+ {
+ sql += "(SELECT count(DISTINCT psi_1.programstageinstanceid) ";
+ }
+ else
+ {
+ sql += "(SELECT " + aggregateType + "( cast( value as DOUBLE PRECISION )) ";
+ }
+
+ sql += "FROM patientdatavalue pdv_1 ";
+ sql += " inner join programstageinstance psi_1 ";
+ sql += " on psi_1.programstageinstanceid = pdv_1.programstageinstanceid ";
+ sql += "WHERE ";
+ sql += " psi_1.executiondate >= '" + format.formatDate( period.getStartDate() ) + "' AND ";
+ sql += " psi_1.executiondate <= '" + format.formatDate( period.getEndDate() ) + "' AND ";
+ if ( deSum != null )
+ {
+ sql += " dataelementid=" + deSum + " AND ";
+ }
+ if ( useCompletedEvents != null )
+ {
+ sql += " psi_1.completed = " + useCompletedEvents + " AND ";
+ }
+ sql += " psi_1.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgunitIds )
+ + ") AND ";
+ sql += " psi_1.programstageid=" + programStage.getId() + " ";
+ sql += filterSQL + " LIMIT 1 ) as \"" + aggregateType + "\" ) ";
+
sql += " UNION ";
}
+ }
+ if ( !sql.isEmpty() )
+ {
sql = sql.substring( 0, sql.length() - 6 );
if ( limit != null )
{
@@ -1790,64 +1860,6 @@
}
/**
- * Aggregate report Position Orgunit Rows - Period Filter - Data Columns
- *
- **/
- private String getAggregateReportSQL7WithoutGroup( ProgramStage programStage, Collection<Integer> roots,
- String facilityLB, String filterSQL, Integer deSum, Period period, String aggregateType, Integer limit,
- Boolean useCompletedEvents, I18nFormat format )
- {
-
- String sql = "";
-
- for ( Integer root : roots )
- {
- Collection<Integer> allOrgunitIds = getOrganisationUnits( root, facilityLB );
- Collection<Integer> orgunitIds = getServiceOrgunit( allOrgunitIds, period );
-
- sql += "(SELECT ";
- sql += "( SELECT ou.name FROM organisationunit ou WHERE ou.organisationunitid=" + root + " ) as orgunit, ";
-
- if ( aggregateType.equals( PatientAggregateReport.AGGREGATE_TYPE_COUNT ) )
- {
- sql += "(SELECT count(DISTINCT psi_1.programstageinstanceid) ";
- }
- else
- {
- sql += "(SELECT " + aggregateType + "( cast( value as DOUBLE PRECISION )) ";
- }
-
- sql += "FROM patientdatavalue pdv_1 ";
- sql += " inner join programstageinstance psi_1 ";
- sql += " on psi_1.programstageinstanceid = pdv_1.programstageinstanceid ";
- sql += "WHERE ";
- sql += " psi_1.executiondate >= '" + format.formatDate( period.getStartDate() ) + "' AND ";
- sql += " psi_1.executiondate <= '" + format.formatDate( period.getEndDate() ) + "' AND ";
- if ( deSum != null )
- {
- sql += " dataelementid=" + deSum + " AND ";
- }
- if ( useCompletedEvents != null )
- {
- sql += " psi_1.completed = " + useCompletedEvents + " AND ";
- }
- sql += " psi_1.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgunitIds ) + ") AND ";
- sql += " psi_1.programstageid=" + programStage.getId() + " ";
- sql += filterSQL + " LIMIT 1 ) as \"" + aggregateType + "\" ) ";
-
- sql += " UNION ";
- }
-
- sql = sql.substring( 0, sql.length() - 6 );
- if ( limit != null )
- {
- sql += " LIMIT " + limit;
- }
-
- return sql;
- }
-
- /**
* Aggregate report Position Data Rows
*
**/
@@ -1861,33 +1873,39 @@
Collection<Integer> allOrgunitIds = getOrganisationUnits( root, facilityLB );
Collection<Integer> orgunitIds = getServiceOrgunit( allOrgunitIds, period );
- sql += "(SELECT pdv_1.value, " + aggregateType + "(pdv_1.value) as \"" + aggregateType + "\" ";
- sql += "FROM patientdatavalue pdv_1 ";
- sql += " JOIN programstageinstance psi_1 ";
- sql += " ON psi_1.programstageinstanceid = pdv_1.programstageinstanceid ";
- sql += "WHERE ";
- sql += " psi_1.programstageid=" + programStage.getId() + " AND ";
- if ( useCompletedEvents != null )
- {
- sql += " psi_1.completed = " + useCompletedEvents + " AND ";
- }
- sql += " psi_1.executiondate >= '" + format.formatDate( period.getStartDate() ) + "' AND ";
- sql += " psi_1.executiondate <= '" + format.formatDate( period.getEndDate() ) + "' AND ";
- sql += " psi_1.organisationunitid in( " + TextUtils.getCommaDelimitedString( orgunitIds ) + " ) ";
- if ( deGroupBy != null )
- {
- sql += " AND pdv_1.dataelementid=" + deGroupBy + " ";
- }
- sql += filterSQL + " ";
- sql += "GROUP BY pdv_1.value )";
- sql += " UNION ";
+ if ( orgunitIds.size() > 0 )
+ {
+ sql += "(SELECT pdv_1.value, " + aggregateType + "(pdv_1.value) as \"" + aggregateType + "\" ";
+ sql += "FROM patientdatavalue pdv_1 ";
+ sql += " JOIN programstageinstance psi_1 ";
+ sql += " ON psi_1.programstageinstanceid = pdv_1.programstageinstanceid ";
+ sql += "WHERE ";
+ sql += " psi_1.programstageid=" + programStage.getId() + " AND ";
+ if ( useCompletedEvents != null )
+ {
+ sql += " psi_1.completed = " + useCompletedEvents + " AND ";
+ }
+ sql += " psi_1.executiondate >= '" + format.formatDate( period.getStartDate() ) + "' AND ";
+ sql += " psi_1.executiondate <= '" + format.formatDate( period.getEndDate() ) + "' AND ";
+ sql += " psi_1.organisationunitid in( " + TextUtils.getCommaDelimitedString( orgunitIds ) + " ) ";
+ if ( deGroupBy != null )
+ {
+ sql += " AND pdv_1.dataelementid=" + deGroupBy + " ";
+ }
+ sql += filterSQL + " ";
+ sql += "GROUP BY pdv_1.value )";
+ sql += " UNION ";
+ }
}
- sql = sql.substring( 0, sql.length() - 6 ) + " ";
- sql += "ORDER BY \"" + aggregateType + "\" desc ";
- if ( limit != null )
+ if ( !sql.isEmpty() )
{
- sql += " LIMIT " + limit;
+ sql = sql.substring( 0, sql.length() - 6 ) + " ";
+ sql += "ORDER BY \"" + aggregateType + "\" desc ";
+ if ( limit != null )
+ {
+ sql += " LIMIT " + limit;
+ }
}
return sql;
