dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #21946
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 10504: Improve Aggregate tabular report.
------------------------------------------------------------
revno: 10504
committer: Tran Chau <tran.hispvietnam@xxxxxxxxx>
branch nick: dhis2
timestamp: Tue 2013-04-09 15:28:13 +0700
message:
Improve 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-04-09 05:48:54 +0000
+++ dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/program/hibernate/HibernateProgramStageInstanceStore.java 2013-04-09 08:28:13 +0000
@@ -473,7 +473,6 @@
I18nFormat format, I18n i18n )
{
String sql = "";
- List<String> deValues = new ArrayList<String>();
String filterSQL = filterSQLStatement( deFilters );
Grid grid = new ListGrid();
@@ -654,10 +653,8 @@
else if ( (position == PatientAggregateReport.POSITION_ROW_ORGUNIT_COLUMN_DATA || position == PatientAggregateReport.POSITION_ROW_DATA_COLUMN_ORGUNIT)
&& deGroupBy != null )
{
- deValues = dataElementService.getDataElement( deGroupBy ).getOptionSet().getOptions();
-
- sql = getAggregateReportSQL7( programStage, orgunitIds, facilityLB, filterSQL, deGroupBy, deSum, deValues,
- periods.iterator().next(), aggregateType, limit, useCompletedEvents, format );
+ sql = getAggregateReportSQL7( programStage, orgunitIds, facilityLB, filterSQL, deGroupBy, deSum, periods
+ .iterator().next(), aggregateType, limit, useCompletedEvents, format );
}
// Type = 7 && NOT group-by
@@ -674,7 +671,7 @@
sql = getAggregateReportSQL8( programStage, orgunitIds, facilityLB, filterSQL, deGroupBy, periods
.iterator().next(), aggregateType, limit, useCompletedEvents, format );
}
-
+
if ( !sql.isEmpty() )
{
SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql );
@@ -1046,6 +1043,7 @@
/**
* Aggregate report Position Orgunit Rows - Period Columns - Data Filter
* Aggregate report Position Orgunit Columns - Period Rows - Data Filter
+ * This result is not included orgunits without any data
*
**/
private String getAggregateReportSQL12( ProgramStage programStage, Collection<Integer> roots, String facilityLB,
@@ -1057,13 +1055,13 @@
// orgunit
for ( Integer root : roots )
{
- Collection<Integer> orgunitIds = getOrganisationUnits( root, facilityLB );
-
sql += " (SELECT ";
sql += "( SELECT ou.name FROM organisationunit ou ";
sql += "WHERE ou.organisationunitid=" + root + " ) as orgunit, ";
+ Collection<Integer> allOrgunitIds = getOrganisationUnits( root, facilityLB );
+
// -- period
for ( Period period : periods )
{
@@ -1079,43 +1077,53 @@
periodName = startDate + " -> " + endDate;
}
- if ( aggregateType.equals( PatientAggregateReport.AGGREGATE_TYPE_COUNT ) )
+ Collection<Integer> orgunitIds = getServiceOrgunit( allOrgunitIds, period );
+
+ if ( orgunitIds.size() == 0 )
{
- sql += "(SELECT count(DISTINCT psi_1.programstageinstanceid) ";
+ sql += "(SELECT \'0\' ";
}
else
{
- sql += "(SELECT " + aggregateType + "( cast( value as DOUBLE PRECISION )) ";
- }
- sql += "FROM programstageinstance psi_1 ";
- sql += " JOIN patientdatavalue pdv_1 ";
- sql += " ON psi_1.programstageinstanceid=pdv_1.programstageinstanceid ";
- sql += "WHERE ";
- sql += " psi_1.organisationunitid in ( " + TextUtils.getCommaDelimitedString( orgunitIds )
- + " ) AND ";
- sql += " psi_1.executiondate >= '" + startDate + "' AND ";
- sql += " psi_1.executiondate <= '" + endDate + "' AND ";
- if ( deSum != null )
- {
- sql += " dataelementid=" + deSum + " AND ";
- }
- if ( useCompletedEvents != null )
- {
- sql += " AND psi_1.completed = " + useCompletedEvents + " AND ";
- }
- if ( deGroupBy != null )
- {
- sql += "(SELECT value from patientdatavalue ";
- sql += "WHERE programstageinstanceid=psi_1.programstageinstanceid AND ";
- sql += " dataelementid=" + deGroupBy + ") is not null AND ";
- }
- sql += " psi_1.programstageid=" + programStage.getId() + " ";
- sql += filterSQL + "LIMIT 1 ) as \"" + periodName + "\" ,";
+ 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 programstageinstance psi_1 ";
+ sql += " JOIN patientdatavalue pdv_1 ";
+ sql += " ON psi_1.programstageinstanceid=pdv_1.programstageinstanceid ";
+ sql += "WHERE ";
+ sql += " psi_1.organisationunitid in ( " + TextUtils.getCommaDelimitedString( orgunitIds )
+ + " ) AND ";
+ sql += " psi_1.executiondate >= '" + startDate + "' AND ";
+ sql += " psi_1.executiondate <= '" + endDate + "' AND ";
+ if ( deSum != null )
+ {
+ sql += " dataelementid=" + deSum + " AND ";
+ }
+ if ( useCompletedEvents != null )
+ {
+ sql += " AND psi_1.completed = " + useCompletedEvents + " AND ";
+ }
+ if ( deGroupBy != null )
+ {
+ sql += "(SELECT value from patientdatavalue ";
+ sql += "WHERE programstageinstanceid=psi_1.programstageinstanceid AND ";
+ sql += " dataelementid=" + deGroupBy + ") is not null AND ";
+ }
+ sql += " psi_1.programstageid=" + programStage.getId() + " ";
+ sql += filterSQL + "LIMIT 1 ) ";
+ }
+
+ sql += "as \"" + periodName + "\" ,";
}
- sql = sql.substring( 0, sql.length() - 1 ) + " ";
-
// -- end period
+ sql = sql.substring( 0, sql.length() - 1 ) + " ";
sql += " ) ";
sql += " UNION ";
}
@@ -1142,7 +1150,7 @@
for ( Integer root : roots )
{
- Collection<Integer> orgunitIds = getOrganisationUnits( root, facilityLB );
+ Collection<Integer> allOrgunitIds = getOrganisationUnits( root, facilityLB );
for ( Period period : periods )
{
@@ -1162,38 +1170,51 @@
sql += "( SELECT ou.name FROM organisationunit ou WHERE organisationunitid=" + root + " ) as orgunit, ";
sql += "'" + periodName + "' as period, ";
- if ( aggregateType.equals( PatientAggregateReport.AGGREGATE_TYPE_COUNT ) )
+ Collection<Integer> orgunitIds = getServiceOrgunit( allOrgunitIds, period );
+
+ if ( orgunitIds.size() == 0 )
{
- sql += "(SELECT count(DISTINCT psi_1.programstageinstanceid) ";
+ sql += "(SELECT \'0\' ";
}
else
{
- sql += "(SELECT " + aggregateType + "( cast( value as DOUBLE PRECISION )) ";
- }
- sql += "FROM ";
- sql += " patientdatavalue pdv_1 JOIN programstageinstance psi_1 ";
- sql += " ON psi_1.programstageinstanceid=pdv_1.programstageinstanceid ";
- sql += " JOIN organisationunit ou on (ou.organisationunitid=psi_1.organisationunitid ) ";
- sql += "WHERE ";
- sql += " ou.organisationunitid in ( " + TextUtils.getCommaDelimitedString( orgunitIds ) + " ) AND ";
- sql += " psi_1.programstageid=" + programStage.getId() + " AND ";
- if ( deSum != null )
- {
- sql += " dataelementid=" + deSum + " AND ";
- }
- if ( useCompletedEvents != null )
- {
- sql += " psi_1.completed = " + useCompletedEvents + " AND ";
- }
- if ( deGroupBy != null )
- {
- sql += "(SELECT value from patientdatavalue ";
- sql += "WHERE programstageinstanceid=psi_1.programstageinstanceid AND ";
- sql += " dataelementid=" + deGroupBy + ") is not null AND ";
- }
- sql += " psi_1.executiondate >= '" + startDate + "' AND ";
- sql += " psi_1.executiondate <= '" + endDate + "' ";
- sql += filterSQL + " LIMIT 1 ) as " + aggregateType + " ) ";
+ 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 ";
+ sql += " patientdatavalue pdv_1 JOIN programstageinstance psi_1 ";
+ sql += " ON psi_1.programstageinstanceid=pdv_1.programstageinstanceid ";
+ sql += " JOIN organisationunit ou on (ou.organisationunitid=psi_1.organisationunitid ) ";
+ sql += "WHERE ";
+ sql += " ou.organisationunitid in ( " + TextUtils.getCommaDelimitedString( orgunitIds )
+ + " ) AND ";
+ sql += " psi_1.programstageid=" + programStage.getId() + " AND ";
+ if ( deSum != null )
+ {
+ sql += " dataelementid=" + deSum + " AND ";
+ }
+ if ( useCompletedEvents != null )
+ {
+ sql += " psi_1.completed = " + useCompletedEvents + " AND ";
+ }
+ if ( deGroupBy != null )
+ {
+ sql += "(SELECT value from patientdatavalue ";
+ sql += "WHERE programstageinstanceid=psi_1.programstageinstanceid AND ";
+ sql += " dataelementid=" + deGroupBy + ") is not null AND ";
+ }
+ sql += " psi_1.executiondate >= '" + startDate + "' AND ";
+ sql += " psi_1.executiondate <= '" + endDate + "' ";
+ sql += filterSQL + " LIMIT 1 ) ";
+ }
+
+ sql += " as " + aggregateType;
+ sql += " ) ";
sql += " UNION ";
}
}
@@ -1220,7 +1241,7 @@
for ( Integer root : roots )
{
- Collection<Integer> orgunitIds = getOrganisationUnits( root, facilityLB );
+ Collection<Integer> allOrgunitIds = getOrganisationUnits( root, facilityLB );
for ( Period period : periods )
{
@@ -1239,39 +1260,52 @@
sql += "( SELECT ";
sql += "'" + periodName + "' as period, ";
- if ( aggregateType.equals( PatientAggregateReport.AGGREGATE_TYPE_COUNT ) )
+ Collection<Integer> orgunitIds = getServiceOrgunit( allOrgunitIds, period );
+ if ( orgunitIds.size() == 0 )
{
- sql += "(SELECT count(DISTINCT psi_1.programstageinstanceid) ";
+ sql += "(SELECT \'0\' ";
}
else
{
- sql += "(SELECT " + aggregateType + "( cast( value as DOUBLE PRECISION )) ";
- }
- sql += "FROM ";
- sql += " patientdatavalue pdv_1 JOIN programstageinstance psi_1 ";
- sql += " ON psi_1.programstageinstanceid=pdv_1.programstageinstanceid ";
- sql += " JOIN organisationunit ou on (ou.organisationunitid=psi_1.organisationunitid ) ";
- sql += "WHERE ";
- sql += " ou.organisationunitid in ( " + TextUtils.getCommaDelimitedString( orgunitIds ) + " ) AND ";
- sql += " psi_1.programstageid=" + programStage.getId() + " AND ";
- if ( deSum != null )
- {
- sql += " dataelementid=" + deSum + " AND ";
- }
- if ( useCompletedEvents != null )
- {
- sql += " psi_1.completed = " + useCompletedEvents + " AND ";
- }
- if ( deGroupBy != null )
- {
- sql += "(SELECT value from patientdatavalue ";
- sql += "WHERE programstageinstanceid=psi_1.programstageinstanceid AND ";
- sql += " dataelementid=" + deGroupBy + ") is not null AND ";
- }
- sql += " psi_1.executiondate >= '" + startDate + "' AND ";
- sql += " psi_1.executiondate <= '" + endDate + "' ";
- sql += filterSQL + " LIMIT 1 ) as " + aggregateType + ") ";
+ 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 ";
+ sql += " patientdatavalue pdv_1 JOIN programstageinstance psi_1 ";
+ sql += " ON psi_1.programstageinstanceid=pdv_1.programstageinstanceid ";
+ sql += " JOIN organisationunit ou on (ou.organisationunitid=psi_1.organisationunitid ) ";
+ sql += "WHERE ";
+ sql += " ou.organisationunitid in ( " + TextUtils.getCommaDelimitedString( orgunitIds )
+ + " ) AND ";
+ sql += " psi_1.programstageid=" + programStage.getId() + " AND ";
+ if ( deSum != null )
+ {
+ sql += " dataelementid=" + deSum + " AND ";
+ }
+ if ( useCompletedEvents != null )
+ {
+ sql += " psi_1.completed = " + useCompletedEvents + " AND ";
+ }
+ if ( deGroupBy != null )
+ {
+ sql += "(SELECT value from patientdatavalue ";
+ sql += "WHERE programstageinstanceid=psi_1.programstageinstanceid AND ";
+ sql += " dataelementid=" + deGroupBy + ") is not null AND ";
+ }
+ sql += " psi_1.executiondate >= '" + startDate + "' AND ";
+ sql += " psi_1.executiondate <= '" + endDate + "' ";
+ sql += filterSQL + " LIMIT 1 ) ";
+ }
+
+ sql += " as " + aggregateType;
+ sql += ") ";
sql += " UNION ALL ";
+
}
}
@@ -1296,47 +1330,57 @@
for ( Integer root : roots )
{
- Collection<Integer> orgunitIds = getOrganisationUnits( root, facilityLB );
+ Collection<Integer> allOrgunitIds = getOrganisationUnits( root, facilityLB );
sql += "( SELECT ";
sql += "( SELECT ou.name ";
sql += "FROM organisationunit ou ";
sql += "WHERE ou.organisationunitid=" + root + " ) as orgunit, ";
- if ( aggregateType.equals( PatientAggregateReport.AGGREGATE_TYPE_COUNT ) )
+ Collection<Integer> orgunitIds = getServiceOrgunit( allOrgunitIds, period );
+ if ( orgunitIds.size() == 0 )
{
- sql += "(SELECT count(DISTINCT psi_1.programstageinstanceid) ";
+ sql += "(SELECT \'0\' ";
}
else
{
- sql += "(SELECT " + aggregateType + "( cast( value as DOUBLE PRECISION )) ";
- }
- sql += "FROM ";
- sql += " patientdatavalue pdv_1 RIGHT JOIN programstageinstance psi_1 ";
- sql += " ON psi_1.programstageinstanceid=pdv_1.programstageinstanceid ";
- sql += "WHERE ";
- sql += " psi_1.organisationunitid in ( " + TextUtils.getCommaDelimitedString( orgunitIds ) + " ) AND ";
- sql += " psi_1.programstageid=" + programStage.getId() + " AND ";
- sql += " psi_1.executiondate >= '" + format.formatDate( period.getStartDate() ) + "' AND ";
- sql += " psi_1.executiondate <= '" + format.formatDate( period.getEndDate() ) + "' ";
- sql += filterSQL + " ";
- if ( deGroupBy != null )
- {
- sql += " AND (SELECT value from patientdatavalue ";
- sql += "WHERE programstageinstanceid=psi_1.programstageinstanceid AND ";
- sql += " dataelementid=" + deGroupBy + ") is not null ";
- }
- if ( useCompletedEvents != null )
- {
- sql += " AND psi_1.completed = " + useCompletedEvents + " ";
- }
- if ( deSum != null )
- {
- sql += " AND dataelementid=" + deSum + " ";
+ 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 ";
+ sql += " patientdatavalue pdv_1 RIGHT JOIN programstageinstance psi_1 ";
+ sql += " ON psi_1.programstageinstanceid=pdv_1.programstageinstanceid ";
+ sql += "WHERE ";
+ sql += " psi_1.organisationunitid in ( " + TextUtils.getCommaDelimitedString( orgunitIds )
+ + " ) AND ";
+ sql += " psi_1.programstageid=" + programStage.getId() + " AND ";
+ sql += " psi_1.executiondate >= '" + format.formatDate( period.getStartDate() ) + "' AND ";
+ sql += " psi_1.executiondate <= '" + format.formatDate( period.getEndDate() ) + "' ";
+ sql += filterSQL + " ";
+ if ( deGroupBy != null )
+ {
+ sql += " AND (SELECT value from patientdatavalue ";
+ sql += "WHERE programstageinstanceid=psi_1.programstageinstanceid AND ";
+ sql += " dataelementid=" + deGroupBy + ") is not null ";
+ }
+ if ( useCompletedEvents != null )
+ {
+ sql += " AND psi_1.completed = " + useCompletedEvents + " ";
+ }
+ if ( deSum != null )
+ {
+ sql += " AND dataelementid=" + deSum + " LIMIT 1 ";
+ }
}
- sql += " LIMIT 1 ) as " + aggregateType + " ) ";
+ sql += " ) as " + aggregateType + " ) ";
sql += " UNION ";
+
}
sql = sql.substring( 0, sql.length() - 6 ) + " ";
@@ -1358,13 +1402,13 @@
Integer limit, Boolean useCompletedEvents, I18nFormat format )
{
String sql = "";
- Collection<Integer> orgunitIds = getOrganisationUnits( root, facilityLB );
+ Collection<Integer> allOrgunitIds = 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( orgunitIds )
+ dataValueSql += " psi.organisationunitid in ( " + TextUtils.getCommaDelimitedString( allOrgunitIds )
+ " ) AND ";
dataValueSql += " psi.programstageid=" + programStage.getId() + " AND ( ";
for ( Period period : periods )
@@ -1409,29 +1453,126 @@
}
sql += "(SELECT DISTINCT '" + periodName + "' as period, ";
- 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 programstageinstance psi_1 JOIN patientdatavalue pdv_1 ";
- sql += " on psi_1.programstageinstanceid = pdv_1.programstageinstanceid ";
+
+ Collection<Integer> orgunitIds = getServiceOrgunit( allOrgunitIds, period );
+ if ( orgunitIds.size() == 0 )
+ {
+ for ( String deValue : deValues )
+ {
+ sql += "(SELECT \'0\' as \"" + deValue + "\",";
+ }
+ }
+ else
+ {
+ 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 programstageinstance psi_1 JOIN patientdatavalue pdv_1 ";
+ sql += " on psi_1.programstageinstanceid = pdv_1.programstageinstanceid ";
+ sql += "WHERE ";
+ sql += " psi_1.organisationunitid in ( " + TextUtils.getCommaDelimitedString( orgunitIds )
+ + " ) AND ";
+ sql += " psi_1.executiondate >= '" + startDate + "' AND ";
+ sql += " psi_1.executiondate <= '" + endDate + "' ";
+ sql += filterSQL + " AND ";
+ sql += " (SELECT value from patientdatavalue ";
+ sql += " WHERE programstageinstanceid=psi_1.programstageinstanceid AND ";
+ sql += " dataelementid=" + deGroupBy + ") = '" + deValue + "' ";
+ sql += " LIMIT 1 ) as \"" + deValue + "\",";
+ }
+ sql = sql.substring( 0, sql.length() - 1 ) + " ";
+
+ sql += "FROM programstageinstance psi JOIN patientdatavalue pdv ";
+ sql += " on psi.programstageinstanceid = pdv.programstageinstanceid ";
sql += "WHERE ";
- sql += " psi_1.organisationunitid in ( " + TextUtils.getCommaDelimitedString( orgunitIds )
- + " ) AND ";
- sql += " psi_1.executiondate >= '" + startDate + "' AND ";
- sql += " psi_1.executiondate <= '" + endDate + "' ";
- sql += filterSQL + " AND ";
- sql += " (SELECT value from patientdatavalue ";
- sql += " WHERE programstageinstanceid=psi_1.programstageinstanceid AND ";
- sql += " dataelementid=" + deGroupBy + ") = '" + deValue + "' ";
- sql += " LIMIT 1 ) as \"" + deValue + "\",";
- }
+ sql += " psi.programstageid=" + programStage.getId() + " ";
+ if ( deSum != null )
+ {
+ sql += " AND dataelementid=" + deSum + " ";
+ }
+ if ( useCompletedEvents != null )
+ {
+ sql += " AND psi.completed = " + useCompletedEvents + " ";
+ }
+ sql += "GROUP BY dataelementid ";
+ }
+
+ sql += ") UNION ALL ";
+
+ }
+
+ sql = sql.substring( 0, sql.length() - 10 );
+
+ if ( limit != null )
+ {
+ sql += " LIMIT " + limit;
+ }
+
+ }
+
+ return sql;
+ }
+
+ /**
+ * Aggregate report Position Orgunit Filter - Period Rows - Data Columns
+ * without group-by
+ **/
+ private String getAggregateReportSQL6WithoutGroup( ProgramStage programStage, Integer root, String facilityLB,
+ String filterSQL, Integer deSum, Collection<Period> periods, String aggregateType, Integer limit,
+ Boolean useCompletedEvents, I18nFormat format )
+ {
+ String sql = "";
+
+ Collection<Integer> allOrgunitIds = getOrganisationUnits( root, facilityLB );
+
+ for ( Period period : periods )
+ {
+ String periodName = "";
+ String startDate = format.formatDate( period.getStartDate() );
+ String endDate = format.formatDate( period.getEndDate() );
+ if ( period.getPeriodType() != null )
+ {
+ periodName = format.formatPeriod( period );
+ }
+ else
+ {
+ periodName = startDate + " -> " + endDate;
+ }
+
+ sql += "(SELECT DISTINCT '" + periodName + "' as period, ";
+
+ Collection<Integer> orgunitIds = getServiceOrgunit( allOrgunitIds, period );
+ if ( orgunitIds.size() == 0 )
+ {
+ sql += "(SELECT \'0\' ";
+ }
+ else
+ {
+ 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 programstageinstance psi_1 JOIN patientdatavalue pdv_1 ";
+ sql += " on psi_1.programstageinstanceid = pdv_1.programstageinstanceid ";
+ sql += "WHERE ";
+ sql += " psi_1.organisationunitid in ( " + TextUtils.getCommaDelimitedString( orgunitIds )
+ + " ) AND ";
+ sql += " psi_1.executiondate >= '" + startDate + "' AND ";
+ sql += " psi_1.executiondate <= '" + endDate + "' AND ";
+ sql += " psi_1.programstageid=" + programStage.getId() + " ";
+ sql += filterSQL + " LIMIT 1 ) as \"" + aggregateType + "\",";
+
sql = sql.substring( 0, sql.length() - 1 ) + " ";
sql += "FROM programstageinstance psi JOIN patientdatavalue pdv ";
@@ -1447,83 +1588,7 @@
sql += " AND psi.completed = " + useCompletedEvents + " ";
}
sql += "GROUP BY dataelementid ";
-
- sql += ") UNION ALL ";
-
- }
-
- sql = sql.substring( 0, sql.length() - 10 );
-
- if ( limit != null )
- {
- sql += " LIMIT " + limit;
- }
-
- }
-
- return sql;
- }
-
- /**
- * Aggregate report Position Orgunit Filter - Period Rows - Data Columns
- * without group-by
- **/
- private String getAggregateReportSQL6WithoutGroup( ProgramStage programStage, Integer root, String facilityLB,
- String filterSQL, Integer deSum, Collection<Period> periods, String aggregateType, Integer limit,
- Boolean useCompletedEvents, I18nFormat format )
- {
- String sql = "";
-
- Collection<Integer> orgunitIds = getOrganisationUnits( root, facilityLB );
-
- for ( Period period : periods )
- {
- String periodName = "";
- String startDate = format.formatDate( period.getStartDate() );
- String endDate = format.formatDate( period.getEndDate() );
- if ( period.getPeriodType() != null )
- {
- periodName = format.formatPeriod( period );
- }
- else
- {
- periodName = startDate + " -> " + endDate;
- }
-
- sql += "(SELECT DISTINCT '" + periodName + "' as period, ";
-
- 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 programstageinstance psi_1 JOIN patientdatavalue pdv_1 ";
- sql += " on psi_1.programstageinstanceid = pdv_1.programstageinstanceid ";
- sql += "WHERE ";
- sql += " psi_1.organisationunitid in ( " + TextUtils.getCommaDelimitedString( orgunitIds )
- + " ) AND ";
- sql += " psi_1.executiondate >= '" + startDate + "' AND ";
- sql += " psi_1.executiondate <= '" + endDate + "' ";
- sql += filterSQL + " LIMIT 1 ) as \"" + aggregateType + "\",";
-
- sql = sql.substring( 0, sql.length() - 1 ) + " ";
-
- sql += "FROM programstageinstance psi JOIN patientdatavalue pdv ";
- sql += " on psi.programstageinstanceid = pdv.programstageinstanceid ";
- sql += "WHERE ";
- sql += " psi.programstageid=" + programStage.getId() + " ";
- if ( deSum != null )
- {
- sql += " AND dataelementid=" + deSum + " ";
- }
- if ( useCompletedEvents != null )
- {
- sql += " AND psi.completed = " + useCompletedEvents + " ";
- }
- sql += "GROUP BY dataelementid ";
+ }
sql += ") UNION ALL ";
}
@@ -1542,60 +1607,103 @@
*
**/
private String getAggregateReportSQL7( ProgramStage programStage, Collection<Integer> roots, String facilityLB,
- String filterSQL, Integer deGroupBy, Integer deSum, List<String> deValues, Period period, String aggregateType,
- Integer limit, Boolean useCompletedEvents, I18nFormat format )
+ String filterSQL, Integer deGroupBy, Integer deSum, Period period, String aggregateType, Integer limit,
+ Boolean useCompletedEvents, I18nFormat format )
{
String sql = "";
+ Collection<Integer> allOrgunitIds = new HashSet<Integer>();
+
for ( Integer root : roots )
{
- Collection<Integer> orgunitIds = getOrganisationUnits( root, facilityLB );
-
- 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;
+ 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 );
+
+ 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;
@@ -1614,7 +1722,8 @@
for ( Integer root : roots )
{
- Collection<Integer> orgunitIds = getOrganisationUnits( root, facilityLB );
+ 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, ";
@@ -1669,7 +1778,8 @@
String sql = "";
for ( Integer root : roots )
{
- Collection<Integer> orgunitIds = getOrganisationUnits( root, facilityLB );
+ 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 ";
@@ -1712,13 +1822,13 @@
Integer limit, Boolean useCompletedEvents, I18nFormat format )
{
String sql = "";
- Collection<Integer> orgunitIds = getOrganisationUnits( root, facilityLB );
+ Collection<Integer> allOrgunitIds = 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( orgunitIds )
+ dataValueSql += " psi.organisationunitid in ( " + TextUtils.getCommaDelimitedString( allOrgunitIds )
+ " ) AND ";
dataValueSql += " psi.programstageid=" + programStage.getId() + " AND ( ";
for ( Period period : periods )
@@ -1751,7 +1861,6 @@
String groupByName = dataElementService.getDataElement( deGroupBy ).getDisplayName();
for ( String deValue : deValues )
{
-
sql += "(SELECT DISTINCT '" + deValue + "' as \"" + groupByName + "\", ";
for ( Period period : periods )
@@ -1773,35 +1882,43 @@
firstPeriodName = periodName;
}
- if ( aggregateType.equals( PatientAggregateReport.AGGREGATE_TYPE_COUNT ) )
+ Collection<Integer> orgunitIds = getServiceOrgunit( allOrgunitIds, period );
+ if ( orgunitIds.size() == 0 )
{
- sql += "(SELECT count(DISTINCT psi_1.programstageinstanceid) ";
+ sql += "(SELECT \'0\' ";
}
else
{
- sql += "(SELECT " + aggregateType + "( cast( value as DOUBLE PRECISION )) ";
- }
-
- sql += "FROM programstageinstance psi_1 JOIN patientdatavalue pdv_1 ";
- sql += " on psi_1.programstageinstanceid = pdv_1.programstageinstanceid ";
- sql += "WHERE ";
- sql += " psi_1.organisationunitid in ( " + TextUtils.getCommaDelimitedString( orgunitIds )
- + " ) AND ";
- sql += " psi_1.executiondate >= '" + startDate + "' AND ";
- sql += " psi_1.executiondate <= '" + endDate + "' ";
- sql += filterSQL + " AND ";
- sql += " (SELECT value from patientdatavalue ";
- sql += " WHERE programstageinstanceid=psi_1.programstageinstanceid AND ";
- sql += " dataelementid=" + deGroupBy + ") = '" + deValue + "' ";
- if ( deSum != null )
- {
- sql += " AND dataelementid=" + deSum + " ";
- }
- if ( useCompletedEvents != null )
- {
- sql += " AND psi_1.completed = " + useCompletedEvents + " ";
- }
-
+ 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 programstageinstance psi_1 JOIN patientdatavalue pdv_1 ";
+ sql += " on psi_1.programstageinstanceid = pdv_1.programstageinstanceid ";
+ sql += "WHERE ";
+ sql += " psi_1.organisationunitid in ( " + TextUtils.getCommaDelimitedString( orgunitIds )
+ + " ) AND ";
+ sql += " psi_1.executiondate >= '" + startDate + "' AND ";
+ sql += " psi_1.executiondate <= '" + endDate + "' ";
+ sql += filterSQL + " AND ";
+ sql += " (SELECT value from patientdatavalue ";
+ sql += " WHERE programstageinstanceid=psi_1.programstageinstanceid AND ";
+ sql += " dataelementid=" + deGroupBy + ") = '" + deValue + "' ";
+ if ( deSum != null )
+ {
+ sql += " AND dataelementid=" + deSum + " ";
+ }
+ if ( useCompletedEvents != null )
+ {
+ sql += " AND psi_1.completed = " + useCompletedEvents + " ";
+ }
+
+ }
sql += ") as \"" + periodName + "\",";
}
sql = sql.substring( 0, sql.length() - 1 );
@@ -1980,6 +2097,30 @@
return orgunitIds;
}
+ /**
+ * Return the Ids of organisation units which events happened.
+ *
+ */
+ private Collection<Integer> getServiceOrgunit( Collection<Integer> orgunitIds, Period period )
+ {
+ String sql = "select distinct organisationunitid from programstageinstance where executiondate>= '"
+ + DateUtils.getMediumDateString( period.getStartDate() ) + "' and executiondate<='"
+ + DateUtils.getMediumDateString( period.getEndDate() ) + "' and organisationunitid in ( "
+ + TextUtils.getCommaDelimitedString( orgunitIds ) + " )";
+
+ Collection<Integer> result = new HashSet<Integer>();
+ result = jdbcTemplate.query( sql, new RowMapper<Integer>()
+ {
+ public Integer mapRow( ResultSet rs, int rowNum )
+ throws SQLException
+ {
+ return rs.getInt( 1 );
+ }
+ } );
+
+ return result;
+ }
+
private void fillDataInGrid( Grid grid, SqlRowSet rs, Boolean displayTotals, I18n i18n, I18nFormat format )
{
int cols = rs.getMetaData().getColumnCount();
@@ -2074,7 +2215,7 @@
for ( int i = 2; i <= cols; i++ )
{
column.add( rowSet.getObject( i ) );
-
+
// Total value of the column
if ( rowSet.getMetaData().getColumnType( i ) != Types.VARCHAR )
{
@@ -2085,7 +2226,7 @@
// Add total value of the column
if ( displayTotals && cols > 2 )
{
- //grid.addValue( format.formatValue( total ) );
+ // grid.addValue( format.formatValue( total ) );
column.add( format.formatValue( total ) );
}
