dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #17595
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 7056: TR, improve method for getting total records
------------------------------------------------------------
revno: 7056
committer: Lars Helge Overland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Mon 2012-05-28 00:33:33 +0200
message:
TR, improve method for getting total records
modified:
dhis-2/dhis-api/src/main/java/org/hisp/dhis/program/ProgramStageInstanceService.java
dhis-2/dhis-api/src/main/java/org/hisp/dhis/program/ProgramStageInstanceStore.java
dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/program/DefaultProgramStageInstanceService.java
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/resources/META-INF/dhis/beans.xml
dhis-2/dhis-web/dhis-web-caseentry/src/main/java/org/hisp/dhis/caseentry/action/report/GenerateTabularReportAction.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-api/src/main/java/org/hisp/dhis/program/ProgramStageInstanceService.java'
--- dhis-2/dhis-api/src/main/java/org/hisp/dhis/program/ProgramStageInstanceService.java 2012-05-27 21:57:33 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/program/ProgramStageInstanceService.java 2012-05-27 22:33:33 +0000
@@ -89,14 +89,16 @@
List<ProgramStageInstance> getProgramStageInstances( Patient patient, Boolean completed );
- Grid getTabularReport( ProgramStage programStage, List<Boolean> hiddenCols,
+ Grid getTabularReport( List<Boolean> hiddenCols,
List<PatientIdentifierType> identifiers, List<String> fixedAttributes, List<PatientAttribute> attributes,
List<DataElement> dataElements, Map<Integer, String> identifierKeys, Map<Integer, String> attributeKeys,
Map<Integer, String> dataElementKeys, Collection<Integer> organisationUnits,
- int level, Date startDate, Date endDate, boolean descOrder,
- Integer min, Integer max, I18nFormat format, I18n i18n );
+ int level, Date startDate, Date endDate, boolean descOrder, Integer min, Integer max );
- int countProgramStageInstances( ProgramStage programStage, Map<Integer,String> searchingIdenKeys, Map<Integer,String> searchingAttrKeys, Map<Integer,String> searchingDEKeys, Collection<Integer> orgunitIds, Date startDate, Date endDate );
+ int getTabularReportCount( List<PatientIdentifierType> identifiers, List<String> fixedAttributes, List<PatientAttribute> attributes,
+ List<DataElement> dataElements, Map<Integer, String> identifierKeys, Map<Integer, String> attributeKeys,
+ Map<Integer, String> dataElementKeys, Collection<Integer> organisationUnits,
+ int level, Date startDate, Date endDate );
List<Grid> getProgramStageInstancesReport( ProgramInstance programInstance, I18nFormat format, I18n i18n );
=== modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/program/ProgramStageInstanceStore.java'
--- dhis-2/dhis-api/src/main/java/org/hisp/dhis/program/ProgramStageInstanceStore.java 2012-05-27 21:57:33 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/program/ProgramStageInstanceStore.java 2012-05-27 22:33:33 +0000
@@ -34,8 +34,6 @@
import org.hisp.dhis.common.GenericStore;
import org.hisp.dhis.common.Grid;
import org.hisp.dhis.dataelement.DataElement;
-import org.hisp.dhis.i18n.I18n;
-import org.hisp.dhis.i18n.I18nFormat;
import org.hisp.dhis.organisationunit.OrganisationUnit;
import org.hisp.dhis.organisationunit.OrganisationUnitLevel;
import org.hisp.dhis.patient.Patient;
@@ -78,21 +76,14 @@
List<ProgramStageInstance> get( ProgramStage programStage, OrganisationUnit orgunit, Date startDate, Date endDate, int min, int max );
- Grid getTabularReport( ProgramStage programStage, List<Boolean> hiddenCols, Map<Integer, OrganisationUnitLevel> orgUnitLevelMap,
+ Grid getTabularReport( List<Boolean> hiddenCols, Map<Integer, OrganisationUnitLevel> orgUnitLevelMap,
List<PatientIdentifierType> identifiers, List<String> fixedAttributes, List<PatientAttribute> attributes,
List<DataElement> dataElements, Map<Integer, String> identifierKeys, Map<Integer, String> attributeKeys,
Map<Integer, String> dataElementKeys, Collection<Integer> orgUnits,
- int level, int maxLevel, Date startDate, Date endDate, boolean descOrder,
- Integer min, Integer max, I18nFormat format, I18n i18n );
-
- /** Get all values and put it on the map.
- * @return key: key-word_object-id
- * value: value
- **/
- Map<String, String> get( ProgramStage programStage, List<String> keys, Map<Integer,String> searchingIdenKeys, List<String> fixedAttributes, Map<Integer,String> searchingAttrKeys, Map<Integer,String> searchingDEKeys, Collection<Integer> upperOrgunitIds, Collection<Integer> bottomOrgunitIds, Date startDate, Date endDate, boolean orderByOrgunitAsc, boolean orderByExecutionDateByAsc, int min, int max );
-
- Map<String, String> get( ProgramStage programStage, List<String> keys, Map<Integer,String> searchingIdenKeys, List<String> fixedAttributes, Map<Integer,String> searchingAttrKeys, Map<Integer,String> searchingDEKeys, Collection<Integer> upperOrgunitIds, Collection<Integer> bottomOrgunitIds, Date startDate, Date endDate, boolean orderByOrgunitAsc, boolean orderByExecutionDateByAsc );
-
- int count( ProgramStage programStage, Map<Integer,String> searchingIdenKeys, Map<Integer,String> searchingAttrKeys, Map<Integer,String> searchingKeys, Collection<Integer> orgunitIds, Date startDate, Date endDate );
-
+ int level, int maxLevel, Date startDate, Date endDate, boolean descOrder, Integer min, Integer max );
+
+ int getTabularReportCount( List<PatientIdentifierType> identifiers, List<String> fixedAttributes, List<PatientAttribute> attributes,
+ List<DataElement> dataElements, Map<Integer, String> identifierKeys, Map<Integer, String> attributeKeys,
+ Map<Integer, String> dataElementKeys, Collection<Integer> orgUnits,
+ int level, int maxLevel, Date startDate, Date endDate );
}
=== modified file 'dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/program/DefaultProgramStageInstanceService.java'
--- dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/program/DefaultProgramStageInstanceService.java 2012-05-27 21:57:33 +0000
+++ dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/program/DefaultProgramStageInstanceService.java 2012-05-27 22:33:33 +0000
@@ -197,38 +197,31 @@
return programStageInstanceStore.get( patient, completed );
}
- public Grid getTabularReport( ProgramStage programStage, List<Boolean> hiddenCols,
+ public Grid getTabularReport( List<Boolean> hiddenCols,
List<PatientIdentifierType> identifiers, List<String> fixedAttributes, List<PatientAttribute> attributes,
List<DataElement> dataElements, Map<Integer, String> identifierKeys, Map<Integer, String> attributeKeys,
Map<Integer, String> dataElementKeys, Collection<Integer> organisationUnits,
- int level, Date startDate, Date endDate, boolean descOrder,
- Integer min, Integer max, I18nFormat format, I18n i18n )
+ int level, Date startDate, Date endDate, boolean descOrder, Integer min, Integer max )
{
- System.out.println("identifiers "+identifiers);
- System.out.println("fixedAttributes "+fixedAttributes);
- System.out.println("attributes "+attributes);
- System.out.println("dataElements "+dataElements);
- System.out.println("identifierKeys "+identifierKeys);
- System.out.println("attributeKeys "+attributeKeys);
- System.out.println("dataElementKeys "+dataElementKeys);
-
int maxLevel = organisationUnitService.getMaxOfOrganisationUnitLevels();
Map<Integer, OrganisationUnitLevel> orgUnitLevelMap = organisationUnitService.getOrganisationUnitLevelMap();
- return programStageInstanceStore.getTabularReport( programStage, hiddenCols, orgUnitLevelMap, identifiers, fixedAttributes, attributes,
- dataElements, identifierKeys, attributeKeys, dataElementKeys, organisationUnits, level, maxLevel, startDate, endDate, descOrder, min, max, format, i18n );
+ return programStageInstanceStore.getTabularReport( hiddenCols, orgUnitLevelMap, identifiers, fixedAttributes, attributes,
+ dataElements, identifierKeys, attributeKeys, dataElementKeys, organisationUnits, level, maxLevel, startDate, endDate, descOrder, min, max );
}
- @Override
- public int countProgramStageInstances( ProgramStage programStage, Map<Integer, String> searchingIdenKeys,
- Map<Integer, String> searchingAttrKeys, Map<Integer, String> searchingDEKeys, Collection<Integer> orgunitIds,
- Date startDate, Date endDate )
+ public int getTabularReportCount( List<PatientIdentifierType> identifiers, List<String> fixedAttributes, List<PatientAttribute> attributes,
+ List<DataElement> dataElements, Map<Integer, String> identifierKeys, Map<Integer, String> attributeKeys,
+ Map<Integer, String> dataElementKeys, Collection<Integer> organisationUnits,
+ int level, Date startDate, Date endDate )
{
- return programStageInstanceStore.count( programStage, searchingIdenKeys, searchingAttrKeys, searchingDEKeys,
- orgunitIds, startDate, endDate );
+ int maxLevel = organisationUnitService.getMaxOfOrganisationUnitLevels();
+
+ return programStageInstanceStore.getTabularReportCount( identifiers, fixedAttributes, attributes,
+ dataElements, identifierKeys, attributeKeys, dataElementKeys, organisationUnits, level, maxLevel, startDate, endDate );
}
-
+
public List<Grid> getProgramStageInstancesReport( ProgramInstance programInstance, I18nFormat format, I18n i18n )
{
List<Grid> grids = new ArrayList<Grid>();
=== 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 2012-05-27 21:57:33 +0000
+++ dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/program/hibernate/HibernateProgramStageInstanceStore.java 2012-05-27 22:33:33 +0000
@@ -26,19 +26,12 @@
*/
package org.hisp.dhis.program.hibernate;
-import java.sql.ResultSet;
-import java.sql.ResultSetMetaData;
-import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
-import java.util.HashMap;
-import java.util.Iterator;
import java.util.List;
import java.util.Map;
-import org.amplecode.quick.StatementHolder;
-import org.amplecode.quick.StatementManager;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Query;
@@ -48,15 +41,11 @@
import org.hisp.dhis.common.GridHeader;
import org.hisp.dhis.dataelement.DataElement;
import org.hisp.dhis.hibernate.HibernateGenericStore;
-import org.hisp.dhis.i18n.I18n;
-import org.hisp.dhis.i18n.I18nFormat;
-import org.hisp.dhis.jdbc.StatementBuilder;
import org.hisp.dhis.organisationunit.OrganisationUnit;
import org.hisp.dhis.organisationunit.OrganisationUnitLevel;
import org.hisp.dhis.patient.Patient;
import org.hisp.dhis.patient.PatientAttribute;
import org.hisp.dhis.patient.PatientIdentifierType;
-import org.hisp.dhis.patientreport.PatientTabularReport;
import org.hisp.dhis.program.ProgramInstance;
import org.hisp.dhis.program.ProgramStage;
import org.hisp.dhis.program.ProgramStageInstance;
@@ -80,20 +69,6 @@
// -------------------------------------------------------------------------
// Dependency
// -------------------------------------------------------------------------
-
- private StatementManager statementManager;
-
- public void setStatementManager( StatementManager statementManager )
- {
- this.statementManager = statementManager;
- }
-
- private StatementBuilder statementBuilder;
-
- public void setStatementBuilder( StatementBuilder statementBuilder )
- {
- this.statementBuilder = statementBuilder;
- }
private JdbcTemplate jdbcTemplate;
@@ -210,56 +185,102 @@
.setFirstResult( min ).setMaxResults( max ).list();
}
- public Grid getTabularReport( ProgramStage programStage, List<Boolean> hiddenCols, Map<Integer, OrganisationUnitLevel> orgUnitLevelMap,
+ public Grid getTabularReport( List<Boolean> hiddenCols, Map<Integer, OrganisationUnitLevel> orgUnitLevelMap,
List<PatientIdentifierType> identifiers, List<String> fixedAttributes, List<PatientAttribute> attributes,
List<DataElement> dataElements, Map<Integer, String> identifierKeys, Map<Integer, String> attributeKeys,
Map<Integer, String> dataElementKeys, Collection<Integer> orgUnits,
- int level, int maxLevel, Date startDate, Date endDate, boolean descOrder,
- Integer min, Integer max, I18nFormat format, I18n i18n )
+ int level, int maxLevel, Date startDate, Date endDate, boolean descOrder, Integer min, Integer max )
{
Grid grid = new ListGrid();
- String sDate = DateUtils.getMediumDateString( startDate );
- String eDate = DateUtils.getMediumDateString( endDate );
-
grid.addHeader( new GridHeader( "Report date", false, true ) );
- String sql = "select * from ( select psi.executiondate, ";
+ //TODO hidden cols
for ( int i = 0; i < maxLevel; i++ )
{
int l = i + 1;
String name = orgUnitLevelMap.containsKey( l ) ? orgUnitLevelMap.get( l ).getName() : "Level " + l;
grid.addHeader( new GridHeader( name, false, true ) );
-
+ }
+
+ for ( PatientIdentifierType type : identifiers )
+ {
+ grid.addHeader( new GridHeader( type.getName(), false, true ) );
+ }
+
+ for ( String attribute : fixedAttributes )
+ {
+ grid.addHeader( new GridHeader( attribute, false, true ) );
+ }
+
+ for ( PatientAttribute attribute : attributes )
+ {
+ grid.addHeader( new GridHeader( attribute.getName(), false, true ) );
+ }
+
+ for ( DataElement element : dataElements )
+ {
+ grid.addHeader( new GridHeader( element.getDisplayName(), false, true ) );
+ }
+
+ String sql = getTabularReportSql( false, identifiers, fixedAttributes, attributes, dataElements,
+ identifierKeys, attributeKeys, dataElementKeys, orgUnits, level, maxLevel, startDate, endDate, descOrder, min, max );
+
+ SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql );
+
+ GridUtils.addRows( grid, rowSet );
+
+ return grid;
+ }
+
+ public int getTabularReportCount( List<PatientIdentifierType> identifiers, List<String> fixedAttributes, List<PatientAttribute> attributes,
+ List<DataElement> dataElements, Map<Integer, String> identifierKeys, Map<Integer, String> attributeKeys,
+ Map<Integer, String> dataElementKeys, Collection<Integer> orgUnits,
+ int level, int maxLevel, Date startDate, Date endDate )
+ {
+ String sql = getTabularReportSql( true, identifiers, fixedAttributes, attributes, dataElements, identifierKeys, attributeKeys,
+ dataElementKeys, orgUnits, level, maxLevel, startDate, endDate, false, null, null );
+
+ return jdbcTemplate.queryForInt( sql );
+ }
+
+ private String getTabularReportSql( boolean count, List<PatientIdentifierType> identifiers, List<String> fixedAttributes, List<PatientAttribute> attributes,
+ List<DataElement> dataElements, Map<Integer, String> identifierKeys, Map<Integer, String> attributeKeys,
+ Map<Integer, String> dataElementKeys, Collection<Integer> orgUnits,
+ int level, int maxLevel, Date startDate, Date endDate, boolean descOrder,
+ Integer min, Integer max )
+ {
+ String sDate = DateUtils.getMediumDateString( startDate );
+ String eDate = DateUtils.getMediumDateString( endDate );
+
+ String selector = count ? "count(*) " : "* ";
+
+ String sql = "select " + selector + "from ( select psi.executiondate, ";
+
+ for ( int i = 0; i < maxLevel; i++ )
+ {
+ int l = i + 1;
sql += "(select name from organisationunit where organisationunitid=ous.idlevel" + l + ") as level_" + i + ", ";
}
for ( PatientIdentifierType type : identifiers )
{
- grid.addHeader( new GridHeader( type.getName(), false, true ) );
-
sql += "(select identifier from patientidentifier where patientid=p.patientid and patientidentifiertypeid=" + type.getId() + ") as identifier_" + type.getId() + ", ";
}
for ( String attribute : fixedAttributes )
{
- grid.addHeader( new GridHeader( attribute, false, true ) );
-
sql += "p." + attribute + ", ";
}
for ( PatientAttribute attribute : attributes )
{
- grid.addHeader( new GridHeader( attribute.getName(), false, true ) );
-
sql += "(select value from patientattributevalue where patientid=p.patientid and patientattributeid=" + attribute.getId() + ") as attribute_" + attribute.getId() + ", ";
}
for ( DataElement element : dataElements )
{
- grid.addHeader( new GridHeader( element.getDisplayName(), false, true ) );
-
sql += "(select value from patientdatavalue where programstageinstanceid=psi.programstageinstanceid and dataelementid=" + element.getId() + ") as element_" + element.getId() + ", ";
}
@@ -312,530 +333,6 @@
log.info(sql);
- SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql );
-
- GridUtils.addRows( grid, rowSet );
-
- return grid;
- }
-
- public Map<String, String> get( ProgramStage programStage, List<String> keys,
- Map<Integer, String> searchingIdenKeys, List<String> fixedAttributes, Map<Integer, String> searchingAttrKeys,
- Map<Integer, String> searchingDEKeys, Collection<Integer> upperOrgunitIds,
- Collection<Integer> bottomOrgunitIds, Date startDate, Date endDate, boolean orderByOrgunitAsc,
- boolean orderByExecutionDateByAsc, int min, int max )
- {
- String sql = "";
- if ( upperOrgunitIds != null && upperOrgunitIds.size() > 0 )
- {
- sql = getTabularReportStatement( 1, programStage, searchingIdenKeys, fixedAttributes, searchingAttrKeys,
- searchingDEKeys, upperOrgunitIds, startDate, endDate, orderByOrgunitAsc, orderByExecutionDateByAsc );
- }
-
- String sqlBottom = "";
-
- if ( bottomOrgunitIds != null && bottomOrgunitIds.size() > 0 )
- {
- sqlBottom = getTabularReportStatement( 1, programStage, searchingIdenKeys, fixedAttributes,
- searchingAttrKeys, searchingDEKeys, bottomOrgunitIds, startDate, endDate, orderByOrgunitAsc,
- orderByExecutionDateByAsc );
-
- if ( sql.isEmpty() )
- {
- sql = sqlBottom;
- }
- else
- {
- sql = "( " + sqlBottom + ") union all ( " + sql + " ) ";
- }
- }
-
- sql += statementBuilder.limitRecord( min, max );
- List<Integer> ids = executeSQL( sql );
- if ( ids != null && ids.size() > 0 )
- {
- sql = getTabularReportStatement( ids, searchingIdenKeys, fixedAttributes, searchingAttrKeys,
- orderByOrgunitAsc, orderByExecutionDateByAsc );
- return executeSQL( sql, keys, fixedAttributes );
- }
-
- return null;
- }
-
- public Map<String, String> get( ProgramStage programStage, List<String> keys,
- Map<Integer, String> searchingIdenKeys, List<String> fixedAttributes, Map<Integer, String> searchingAttrKeys,
- Map<Integer, String> searchingDEKeys, Collection<Integer> upperOrgunitIds,
- Collection<Integer> bottomOrgunitIds, Date startDate, Date endDate, boolean orderByOrgunitAsc,
- boolean orderByExecutionDateByAsc )
- {
- String sql = "";
-
- if ( upperOrgunitIds != null && upperOrgunitIds.size() > 0 )
- {
- sql = getTabularReportStatement( 1, programStage, searchingIdenKeys, fixedAttributes, searchingAttrKeys,
- searchingDEKeys, upperOrgunitIds, startDate, endDate, orderByOrgunitAsc, orderByExecutionDateByAsc );
- }
- String sqlBottom = "";
-
- if ( bottomOrgunitIds.size() > 0 )
- {
- sqlBottom = getTabularReportStatement( 1, programStage, searchingIdenKeys, fixedAttributes,
- searchingAttrKeys, searchingDEKeys, bottomOrgunitIds, startDate, endDate, orderByOrgunitAsc,
- orderByExecutionDateByAsc );
- if ( sql.isEmpty() )
- {
- sql = sqlBottom;
- }
- else
- {
- sql = "( " + sqlBottom + ") union all ( " + sql + " ) ";
- }
- }
-
- List<Integer> ids = executeSQL( sql );
- if ( ids != null && ids.size() > 0 )
- {
- sql = getTabularReportStatement( ids, searchingIdenKeys, fixedAttributes, searchingAttrKeys,
- orderByOrgunitAsc, orderByExecutionDateByAsc );
-
- return executeSQL( sql, keys, fixedAttributes );
- }
-
- return null;
- }
-
- public int count( ProgramStage programStage, Map<Integer, String> searchingIdenKeys,
- Map<Integer, String> searchingAttrKeys, Map<Integer, String> searchingDEKeys, Collection<Integer> orgunitIds,
- Date startDate, Date endDate )
- {
- String sql = getTabularReportStatement( 0, programStage, searchingIdenKeys, null, searchingAttrKeys,
- searchingDEKeys, orgunitIds, startDate, endDate, true, true );
-
- return executeCountSQL( sql );
- }
-
- // -------------------------------------------------------------------------
- // Supportive methods
- // -------------------------------------------------------------------------
-
- private String getData( int mark, Map<Integer, String> searchingIdenKeys, List<String> fixedAttributes,
- Map<Integer, String> searchingAttrKeys )
- {
- String select = "";
- switch ( mark )
- {
- // count result
- case 0:
- select = "select count( distinct psi.programstageinstanceid ) ";
- break;
- // Get data with limit
- case 1:
- select = "SELECT distinct psi.programstageinstanceid, ogu.name , psi.executiondate ";
- break;
- case 2:
- select = "SELECT psi.programstageinstanceid AS psiid, ogu.organisationunitid AS orgunitid, ogu.name AS orgunitname, psi.executiondate AS "
- + PatientTabularReport.PREFIX_EXECUTION_DATE;
-
- if ( fixedAttributes != null )
- {
- for ( String fixedAttribute : fixedAttributes )
- {
- select += ",p." + fixedAttribute + " AS " + PatientTabularReport.PREFIX_FIXED_ATTRIBUTE + "_"
- + fixedAttribute;
- }
- }
- if ( searchingIdenKeys.size() > 0 )
- {
- select += ",pid.patientidentifiertypeid AS " + PatientTabularReport.PREFIX_IDENTIFIER_TYPE
- + "_id, pid.identifier AS " + PatientTabularReport.PREFIX_IDENTIFIER_TYPE + "_value";
- }
- if ( searchingAttrKeys.size() > 0 )
- {
- select += ",pav.patientattributeid AS " + PatientTabularReport.PREFIX_PATIENT_ATTRIBUTE
- + "_id, pav.value AS " + PatientTabularReport.PREFIX_PATIENT_ATTRIBUTE + "_value ";
- }
- break;
- default:
- return "";
- }
-
- if ( mark == 2 )
- {
- select += ",pdv.dataelementid AS " + PatientTabularReport.PREFIX_DATA_ELEMENT + "_id, pdv.value AS "
- + PatientTabularReport.PREFIX_DATA_ELEMENT + "_value FROM programstageinstance AS psi "
- + " INNER JOIN patientdatavalue AS pdv ON pdv.programstageinstanceid=psi.programstageinstanceid "
- + " INNER JOIN programinstance pi ON pi.programinstanceid=psi.programinstanceid "
- + " INNER JOIN organisationunit ogu ON ogu.organisationunitid=psi.organisationunitid ";
-
- if ( (fixedAttributes != null && fixedAttributes.size() > 0) || searchingIdenKeys.size() > 0
- || searchingAttrKeys.size() > 0 )
- {
- select += " INNER JOIN patient AS p on p.patientid = pi.patientid ";
- }
-
- if ( searchingIdenKeys.size() > 0 )
- {
- select += " INNER JOIN patientidentifier AS pid ON pid.patientid = p.patientid ";
- }
- if ( searchingAttrKeys.size() > 0 )
- {
- select += " INNER JOIN patientattributevalue AS pav ON pav.patientid = p.patientid ";
- }
- }
- else
- {
- select += " FROM programstageinstance AS psi "
- + " INNER JOIN patientdatavalue AS pdv ON pdv.programstageinstanceid=psi.programstageinstanceid "
- + " INNER JOIN programinstance pi ON pi.programinstanceid=psi.programinstanceid "
- + " INNER JOIN organisationunit ogu ON ogu.organisationunitid=psi.organisationunitid ";
- }
-
- return select;
- }
-
- private String getTabularReportStatement( int mark, ProgramStage programStage,
- Map<Integer, String> searchingIdenKeys, List<String> fixedAttributes, Map<Integer, String> searchingAttrKeys,
- Map<Integer, String> searchingDEKeys, Collection<Integer> orgunitIds, Date startDate, Date endDate,
- boolean orderByOrgunitAsc, boolean orderByExecutionDateByAsc )
- {
- String select = getData( mark, searchingIdenKeys, fixedAttributes, searchingAttrKeys );
-
- String sqlID = " select distinct psi.programstageinstanceid from patientdatavalue pdv "
- + "inner join programstageinstance psi on pdv.programstageinstanceid=psi.programstageinstanceid "
- + "INNER JOIN patientidentifier as pid ON pid.patientid = p.patientid "
- + "INNER JOIN patientidentifiertype as pit ON pid.patientidentifiertypeid = pit.patientidentifiertypeid ";
-
- String sqlATTR = " SELECT distinct psi.programstageinstanceid " + "FROM programstageinstance AS psi "
- + "INNER JOIN patientdatavalue AS pdv ON pdv.programstageinstanceid=psi.programstageinstanceid "
- + "INNER JOIN programinstance pi ON pi.programinstanceid=psi.programinstanceid "
- + "INNER JOIN organisationunit ogu ON ogu.organisationunitid=psi.organisationunitid "
- + "INNER JOIN patient AS p on p.patientid = pi.patientid "
- + "INNER JOIN patientattributevalue AS pav ON pav.patientid = p.patientid ";
-
- String sqlDE = " select distinct psi.programstageinstanceid from patientdatavalue pdv "
- + "inner join programstageinstance psi on pdv.programstageinstanceid=psi.programstageinstanceid ";
-
- String condition = " WHERE psi.executiondate >= '" + DateUtils.getMediumDateString( startDate )
- + "' AND psi.executiondate <= '" + DateUtils.getMediumDateString( endDate ) + "' "
- + " AND psi.organisationunitid in " + splitListHelper( orgunitIds ) + " AND psi.programstageid = "
- + programStage.getId() + " ";
-
- // ---------------------------------------------------------------------
- // Searching program-stage-instances by patient-identifiers
- // ---------------------------------------------------------------------
-
- Iterator<Integer> idenKeys = searchingIdenKeys.keySet().iterator();
- boolean index = false;
- while ( idenKeys.hasNext() )
- {
- Integer attributeId = idenKeys.next();
-
- if ( index )
- {
- condition += " AND psi.programstageinstanceid in ( " + sqlID + " WHERE psi.programstageid = "
- + programStage.getId() + " ";
- }
-
- condition += " AND pid.patientidentifierTypeid=" + attributeId + " AND lower(pid.identifier) ";
-
- String compareValue = searchingIdenKeys.get( attributeId ).toLowerCase();
-
- if ( compareValue.contains( "%" ) )
- {
- compareValue = compareValue.replace( "=", "like " );
- }
-
- condition += compareValue;
-
- if ( index )
- {
- condition += ") ";
- }
-
- index = true;
- }
-
- // ---------------------------------------------------------------------
- // Searching program-stage-instances by patient-attributes
- // ---------------------------------------------------------------------
-
- Iterator<Integer> attrKeys = searchingAttrKeys.keySet().iterator();
- index = false;
- while ( attrKeys.hasNext() )
- {
- Integer attributeId = attrKeys.next();
-
- if ( index )
- {
- condition += " AND psi.programstageinstanceid in ( " + sqlATTR + " WHERE 1=1 ";
- }
-
- condition += " AND pav.patientattributeid=" + attributeId + " AND lower(pav.value) ";
-
- String compareValue = searchingAttrKeys.get( attributeId ).toLowerCase();
-
- if ( compareValue.contains( "%" ) )
- {
- compareValue = compareValue.replace( "=", "like " );
- }
-
- condition += compareValue;
-
- if ( index )
- {
- condition += ") ";
- }
-
- index = true;
- }
-
- // ---------------------------------------------------------------------
- // Searching program-stage-instances by dataelements
- // ---------------------------------------------------------------------
-
- Iterator<Integer> deKeys = searchingDEKeys.keySet().iterator();
-
- index = false;
- while ( deKeys.hasNext() )
- {
- Integer dataElementId = deKeys.next();
-
- condition += " AND psi.programstageinstanceid in ( " + sqlDE + " WHERE 1=1 ";
-
- condition += " AND pdv.dataElementid=" + dataElementId + " AND lower(pdv.value) ";
-
- String compareValue = searchingDEKeys.get( dataElementId ).toLowerCase();
-
- if ( compareValue.contains( "%" ) )
- {
- compareValue = compareValue.replace( "=", "like " );
- }
-
- condition += compareValue;
-
- condition += ") ";
- }
-
- if ( mark == 0 )
- {
- return select + condition;
- }
-
- condition += " ORDER BY ogu.name ";
- condition += orderByOrgunitAsc ? "asc" : "desc";
- condition += ", psi.executiondate ";
- condition += orderByExecutionDateByAsc ? "asc" : "desc";
-
- return select + condition;
- }
-
- private String getTabularReportStatement( List<Integer> ids, Map<Integer, String> searchingIdenKeys,
- List<String> fixedAttributes, Map<Integer, String> searchingAttrKeys, boolean orderByOrgunitAsc,
- boolean orderByExecutionDateByAsc )
- {
- String sql = getData( 2, searchingIdenKeys, fixedAttributes, searchingAttrKeys );
-
- sql += " WHERE psi.programstageinstanceid in " + splitListHelper( ids ) + " ";
- sql += " ORDER BY ogu.name ";
- sql += orderByOrgunitAsc ? "asc" : "desc";
- sql += ", psi.executiondate ";
- sql += orderByExecutionDateByAsc ? "asc" : "desc";
-
return sql;
}
-
- private Map<String, String> executeSQL( String sql, List<String> keys, List<String> fixedAttributes )
- {
- StatementHolder holder = statementManager.getHolder();
-
- try
- {
- Statement statement = holder.getStatement();
-
- ResultSet resultSet = statement.executeQuery( sql );
-
- return gridMapping( resultSet, fixedAttributes, keys );
- }
- catch ( Exception ex )
- {
- return null;
- }
- finally
- {
- holder.close();
- }
- }
-
- private List<Integer> executeSQL( String sql )
- {
- List<Integer> result = new ArrayList<Integer>();
-
- StatementHolder holder = statementManager.getHolder();
-
- try
- {
- Statement statement = holder.getStatement();
-
- ResultSet resultSet = statement.executeQuery( sql );
-
- while ( resultSet.next() )
- {
- result.add( resultSet.getInt( 1 ) );
- }
-
- return result;
- }
- catch ( Exception ex )
- {
- return null;
- }
- finally
- {
- holder.close();
- }
- }
-
- private int executeCountSQL( String sql )
- {
- StatementHolder holder = statementManager.getHolder();
-
- try
- {
- Statement statement = holder.getStatement();
-
- ResultSet resultSet = statement.executeQuery( sql );
-
- return (resultSet != null && resultSet.next()) ? resultSet.getInt( 1 ) : 0;
- }
- catch ( Exception ex )
- {
- return 0;
- }
- finally
- {
- holder.close();
- }
- }
-
- /**
- * Splits a list of integers by comma. Use this method if you have a list
- * that will be used in f.ins. a WHERE xxx IN (list) clause in SQL.
- *
- * @param Collection <Integer> list of Integers
- * @return the list as a string splitted by a comma.
- */
- private String splitListHelper( Collection<Integer> list )
- {
- StringBuffer sb = new StringBuffer();
- int count = 0;
-
- sb.append( "(" );
- for ( Integer i : list )
- {
- sb.append( i );
-
- count++;
-
- if ( count < list.size() )
- {
- sb.append( "," );
- }
- }
- sb.append( ")" );
-
- return sb.toString();
- }
-
- private Map<String, String> gridMapping( ResultSet resultSet, List<String> fixedAttributes, List<String> keys )
- {
- Map<String, String> valuesMap = new HashMap<String, String>();
-
- try
- {
- ResultSetMetaData meta = resultSet.getMetaData();
-
- while ( resultSet.next() )
- {
- String key = resultSet.getString( "psiid" );
-
- // Get execution-date
- if ( !keys.contains( key ) )
- {
- keys.add( key );
- }
-
- valuesMap.put( key + "_" + PatientTabularReport.PREFIX_EXECUTION_DATE, resultSet
- .getString( PatientTabularReport.PREFIX_EXECUTION_DATE ) );
-
- // Get orgunit-id
- valuesMap.put( key + "_" + PatientTabularReport.PREFIX_ORGUNIT, resultSet.getString( "orgunitid" ) );
-
- for ( String fixedAttr : fixedAttributes )
- {
- // Get fixed-attributes
- key = resultSet.getInt( "psiid" ) + "_" + PatientTabularReport.PREFIX_FIXED_ATTRIBUTE + "_"
- + fixedAttr;
- valuesMap.put( key, resultSet.getString( fixedAttr ) );
- }
-
- // Get idens
- String colname = PatientTabularReport.PREFIX_IDENTIFIER_TYPE + "_id";
- if ( existedCol( colname, meta ) )
- {
- key = resultSet.getInt( "psiid" ) + "_" + PatientTabularReport.PREFIX_IDENTIFIER_TYPE + "_"
- + resultSet.getString( colname );
- valuesMap.put( key, resultSet.getString( PatientTabularReport.PREFIX_IDENTIFIER_TYPE + "_value" ) );
- }
-
- // Get dynmic-attributes
- colname = PatientTabularReport.PREFIX_PATIENT_ATTRIBUTE + "_id";
- if ( existedCol( colname, meta ) )
- {
- key = resultSet.getInt( "psiid" ) + "_" + PatientTabularReport.PREFIX_PATIENT_ATTRIBUTE + "_"
- + resultSet.getString( colname );
- valuesMap
- .put( key, resultSet.getString( PatientTabularReport.PREFIX_PATIENT_ATTRIBUTE + "_value" ) );
- }
-
- // Get data-elements
- colname = PatientTabularReport.PREFIX_DATA_ELEMENT + "_id";
- if ( existedCol( colname, meta ) )
- {
- key = resultSet.getInt( "psiid" ) + "_" + PatientTabularReport.PREFIX_DATA_ELEMENT + "_"
- + resultSet.getString( colname );
- valuesMap.put( key, resultSet.getString( PatientTabularReport.PREFIX_DATA_ELEMENT + "_value" ) );
- }
- }
- }
- catch ( Exception e )
- {
- e.printStackTrace();
- }
-
- return valuesMap;
- }
-
- private boolean existedCol( String colname, ResultSetMetaData meta )
- {
- int numCol = 0;
- try
- {
- numCol = meta.getColumnCount();
-
- for ( int i = 1; i < numCol + 1; i++ )
- {
- if ( meta.getColumnName( i ).equals( colname ) )
- {
- return true;
- }
-
- }
- }
- catch ( Exception e )
- {
- return false;
- }
-
- return false;
- }
}
=== modified file 'dhis-2/dhis-services/dhis-service-patient/src/main/resources/META-INF/dhis/beans.xml'
--- dhis-2/dhis-services/dhis-service-patient/src/main/resources/META-INF/dhis/beans.xml 2012-05-26 12:39:39 +0000
+++ dhis-2/dhis-services/dhis-service-patient/src/main/resources/META-INF/dhis/beans.xml 2012-05-27 22:33:33 +0000
@@ -37,8 +37,6 @@
class="org.hisp.dhis.program.hibernate.HibernateProgramStageInstanceStore">
<property name="clazz" value="org.hisp.dhis.program.ProgramStageInstance" />
<property name="sessionFactory" ref="sessionFactory" />
- <property name="statementManager" ref="statementManager" />
- <property name="statementBuilder" ref="statementBuilder" />
<property name="jdbcTemplate" ref="jdbcTemplate" />
</bean>
=== modified file 'dhis-2/dhis-web/dhis-web-caseentry/src/main/java/org/hisp/dhis/caseentry/action/report/GenerateTabularReportAction.java'
--- dhis-2/dhis-web/dhis-web-caseentry/src/main/java/org/hisp/dhis/caseentry/action/report/GenerateTabularReportAction.java 2012-05-27 21:57:33 +0000
+++ dhis-2/dhis-web/dhis-web-caseentry/src/main/java/org/hisp/dhis/caseentry/action/report/GenerateTabularReportAction.java 2012-05-27 22:33:33 +0000
@@ -34,7 +34,6 @@
import static org.hisp.dhis.patientreport.PatientTabularReport.VALUE_TYPE_OPTION_SET;
import java.util.ArrayList;
-import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
@@ -48,7 +47,6 @@
import org.hisp.dhis.i18n.I18n;
import org.hisp.dhis.i18n.I18nFormat;
import org.hisp.dhis.organisationunit.OrganisationUnit;
-import org.hisp.dhis.organisationunit.OrganisationUnitHierarchy;
import org.hisp.dhis.organisationunit.OrganisationUnitService;
import org.hisp.dhis.paging.ActionPagingSupport;
import org.hisp.dhis.patient.PatientAttribute;
@@ -169,13 +167,6 @@
this.orderByOrgunitAsc = orderByOrgunitAsc;
}
- private boolean orderByExecutionDateByAsc;
-
- public void setOrderByExecutionDateByAsc( boolean orderByExecutionDateByAsc )
- {
- this.orderByExecutionDateByAsc = orderByExecutionDateByAsc;
- }
-
private Integer level;
public void setLevel( Integer level )
@@ -309,6 +300,8 @@
ProgramStage programStage = programStageService.getProgramStage( programStageId );
+ //TODO include program stage in sql query
+
Date startValue = format.parseDate( startDate );
Date endValue = format.parseDate( endDate );
@@ -325,25 +318,25 @@
if ( type == null ) // Tabular report
{
- int totalRecords = programStageInstanceService.countProgramStageInstances( programStage, searchingIdenKeys,
- searchingAttrKeys, searchingDEKeys, organisationUnits, startValue, endValue );
+ int totalRecords = programStageInstanceService.getTabularReportCount( identifierTypes, fixedAttributes, patientAttributes, dataElements,
+ searchingIdenKeys, searchingAttrKeys, searchingDEKeys, organisationUnits, level, startValue, endValue );
total = getNumberOfPages( totalRecords );
this.paging = createPaging( totalRecords );
//total = paging.getTotal(); //TODO
- grid = programStageInstanceService.getTabularReport( programStage, hiddenCols, identifierTypes,
+ grid = programStageInstanceService.getTabularReport( hiddenCols, identifierTypes,
fixedAttributes, patientAttributes, dataElements, searchingIdenKeys, searchingAttrKeys,
searchingDEKeys, organisationUnits, level, startValue, endValue, !orderByOrgunitAsc,
- paging.getStartPos(), paging.getPageSize(), format, i18n );
+ paging.getStartPos(), paging.getPageSize() );
}
else // Download as Excel
{
- grid = programStageInstanceService.getTabularReport( programStage, hiddenCols, identifierTypes,
+ grid = programStageInstanceService.getTabularReport( hiddenCols, identifierTypes,
fixedAttributes, patientAttributes, dataElements, searchingIdenKeys, searchingAttrKeys,
searchingDEKeys, organisationUnits, level, startValue, endValue, !orderByOrgunitAsc,
- null, null, format, i18n );
+ null, null );
}
System.out.println();
