dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #17591
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 7052: WIP tabular report
------------------------------------------------------------
revno: 7052
committer: Lars Helge Overland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Sun 2012-05-27 00:54:37 +0200
message:
WIP tabular report
modified:
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-web/dhis-web-caseentry/src/main/java/org/hisp/dhis/caseentry/action/report/GenerateTabularReportAction.java
dhis-2/dhis-web/dhis-web-caseentry/src/main/webapp/dhis-web-caseentry/jsonTabularReportResult.vm
--
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/ProgramStageInstanceStore.java'
--- dhis-2/dhis-api/src/main/java/org/hisp/dhis/program/ProgramStageInstanceStore.java 2012-05-26 12:39:39 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/program/ProgramStageInstanceStore.java 2012-05-26 22:54:37 +0000
@@ -37,6 +37,7 @@
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;
import org.hisp.dhis.patient.PatientAttribute;
import org.hisp.dhis.patient.PatientIdentifierType;
@@ -76,8 +77,7 @@
List<ProgramStageInstance> get( Patient patient, Boolean completed);
List<ProgramStageInstance> get( ProgramStage programStage, OrganisationUnit orgunit, Date startDate, Date endDate, int min, int max );
-
- Grid getTabularReport( ProgramStage programStage, List<Boolean> hiddenCols,
+ Grid getTabularReport( ProgramStage programStage, 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,
=== 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-26 12:39:39 +0000
+++ dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/program/DefaultProgramStageInstanceService.java 2012-05-26 22:54:37 +0000
@@ -40,6 +40,7 @@
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.organisationunit.OrganisationUnitService;
import org.hisp.dhis.patient.Patient;
import org.hisp.dhis.patient.PatientAttribute;
@@ -204,7 +205,6 @@
Collection<Integer> bottomOrgunitIds, int level, Date startDate, Date endDate, boolean orderByOrgunitAsc,
boolean orderByExecutionDateByAsc, int min, int max, I18nFormat format, I18n i18n )
{
- /*
System.out.println("identifiers "+identifiers);
System.out.println("fixedAttributes "+fixedAttributes);
System.out.println("attributes "+attributes);
@@ -215,24 +215,12 @@
int maxLevel = organisationUnitService.getMaxOfOrganisationUnitLevels() - 1;
- return programStageInstanceStore.getTabularReport( programStage, hiddenCols, identifiers, fixedAttributes, attributes,
+ Map<Integer, OrganisationUnitLevel> orgUnitLevelMap = organisationUnitService.getOrganisationUnitLevelMap();
+
+ return programStageInstanceStore.getTabularReport( programStage, hiddenCols, orgUnitLevelMap, identifiers, fixedAttributes, attributes,
dataElements, identifierKeys, attributeKeys, dataElementKeys, upperOrgunitIds, level, maxLevel, startDate, endDate, !orderByOrgunitAsc, min, max, format, i18n );
- */
- List<String> keys = new ArrayList<String>();
-
- Map<String, String> valuesMap = programStageInstanceStore.get( programStage, keys, identifierKeys,
- fixedAttributes, attributeKeys, dataElementKeys, upperOrgunitIds, bottomOrgunitIds, startDate, endDate,
- orderByOrgunitAsc, orderByExecutionDateByAsc, min, max );
-
- if ( keys != null && keys.size() > 0 )
- {
- return createTabularGrid( level, hiddenCols, programStage, keys, valuesMap, identifiers, fixedAttributes,
- attributes, dataElements, startDate, endDate, format, i18n );
- }
-
- return new ListGrid();
}
-
+
public Grid getTabularReport( ProgramStage programStage, List<Boolean> hiddenCols,
List<PatientIdentifierType> identifiers, List<String> fixedAttributes, List<PatientAttribute> attributes,
List<DataElement> dataElements, Map<Integer, String> identifierKeys, Map<Integer, String> attributeKeys,
=== 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-26 12:39:39 +0000
+++ dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/program/hibernate/HibernateProgramStageInstanceStore.java 2012-05-26 22:54:37 +0000
@@ -39,16 +39,20 @@
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;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.hisp.dhis.common.Grid;
+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;
@@ -71,6 +75,8 @@
extends HibernateGenericStore<ProgramStageInstance>
implements ProgramStageInstanceStore
{
+ private static final Log log = LogFactory.getLog( HibernateProgramStageInstanceStore.class );
+
// -------------------------------------------------------------------------
// Dependency
// -------------------------------------------------------------------------
@@ -204,7 +210,7 @@
.setFirstResult( min ).setMaxResults( max ).list();
}
- public Grid getTabularReport( ProgramStage programStage, List<Boolean> hiddenCols,
+ public Grid getTabularReport( ProgramStage programStage, 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,
@@ -213,55 +219,48 @@
{
Grid grid = new ListGrid();
- String sql = getTabularReportSql( identifiers, fixedAttributes, attributes, dataElements, identifierKeys, attributeKeys, dataElementKeys,
- orgUnits, level, maxLevel, startDate, endDate, descOrder, min, max );
- System.out.println(sql);
- SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql );
- //TODO headers
- GridUtils.addRows( grid, rowSet );
-
- return grid;
- }
-
- private String getTabularReportSql( 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, int min, int max )
- {
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, ";
for ( int i = 0; i < maxLevel; i++ )
{
- sql += "(select name from organisationunit where organisationunitid=ous.idlevel1) as ou_level_" + i + ", ";
+ int l = i + 1;
+ String name = orgUnitLevelMap.containsKey( l ) ? orgUnitLevelMap.get( l ).getName() : "Level " + l;
+ grid.addHeader( new GridHeader( name, false, true ) );
+
+ sql += "(select name from organisationunit where organisationunitid=ous.idlevel1) as level_" + i + ", ";
}
- int count = 0;
-
for ( PatientIdentifierType type : identifiers )
{
- sql += "(select identifier from patientidentifier where patientid=p.patientid and patientidentifiertypeid=" + type.getId() + ") as identifier" + count++ + ", ";
+ 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 + ", ";
}
- count = 0;
-
for ( PatientAttribute attribute : attributes )
{
- sql += "(select value from patientattributevalue where patientid=p.patientid and patientattributeid=" + attribute.getId() + ") as attribute" + count++ + ", ";
+ 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() + ", ";
}
- count = 0;
-
for ( DataElement element : dataElements )
{
- sql += "(select value from patientdatavalue where programstageinstanceid=psi.programstageinstanceid and dataelementid=" + element.getId() + ") as element" + count++ + ", ";
+ 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() + ", ";
}
sql = sql.substring( 0, sql.length() - 2 ) + " "; // Remove last comma
@@ -279,16 +278,44 @@
sql += "order by ";
- for ( int i = 0; i < dataElements.size(); i++ )
+ for ( int i = 0; i < maxLevel; i++ )
{
- sql += "element" + i + ", ";
+ sql += "level_" + i + ", ";
}
- sql += "psi.executiondate) ";
+ sql += "psi.executiondate ";
sql += descOrder ? "desc " : "";
- sql += "as tabular offset 0 limit 50"; //TODO page and filter
-
- return sql;
+ sql += "offset 0 limit 50 ";
+ sql += ") as tabular "; //TODO page and filter
+
+ String operator = "where ";
+
+ for ( Integer key : identifierKeys.keySet() )
+ {
+ sql += operator + "identifier_" + key + identifierKeys.get( key ) + ", ";
+ operator = "and ";
+ }
+
+ for ( Integer key : attributeKeys.keySet() )
+ {
+ sql += operator + "attribute_" + key + attributeKeys.get( key ) + ", ";
+ operator = "and ";
+ }
+
+ for ( Integer key : dataElementKeys.keySet() )
+ {
+ sql += operator + "element_" + key + dataElementKeys.get( key ) + ", ";
+ }
+
+ sql = sql.substring( 0, sql.length() - 2 ) + " "; // Remove last comma
+
+ log.info(sql);
+
+ SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql );
+
+ GridUtils.addRows( grid, rowSet );
+
+ return grid;
}
public Map<String, String> get( ProgramStage programStage, List<String> keys,
=== 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-26 12:39:39 +0000
+++ dhis-2/dhis-web/dhis-web-caseentry/src/main/java/org/hisp/dhis/caseentry/action/report/GenerateTabularReportAction.java 2012-05-26 22:54:37 +0000
@@ -320,7 +320,7 @@
Collection<OrganisationUnit> orgUnits = organisationUnitService.getOrganisationUnitsAtLevel( maxLevel, selectedOrgunit );
bottomOrgunitIds = new HashSet<Integer>( ConversionUtils.getIdentifiers( OrganisationUnit.class, orgUnits ) );
- upperOrgunitIds.removeAll( bottomOrgunitIds ); //TODO fix
+ //upperOrgunitIds.removeAll( bottomOrgunitIds ); //TODO fix
}
// ---------------------------------------------------------------------
=== modified file 'dhis-2/dhis-web/dhis-web-caseentry/src/main/webapp/dhis-web-caseentry/jsonTabularReportResult.vm'
--- dhis-2/dhis-web/dhis-web-caseentry/src/main/webapp/dhis-web-caseentry/jsonTabularReportResult.vm 2012-05-26 17:38:10 +0000
+++ dhis-2/dhis-web/dhis-web-caseentry/src/main/webapp/dhis-web-caseentry/jsonTabularReportResult.vm 2012-05-26 22:54:37 +0000
@@ -1,34 +1,28 @@
-
#set( $noRows = $grid.getRows().size() )
{
#set( $noHeader = $grid.getHeaders().size() )
#set ( $metaData = $noHeader - $valueTypes.size() )
-"total": "$total",
+"total": "${total}",
"columns":[
- #set($index = 0)
- #foreach( $col in $grid.getHeaders() )
- {
- #set( $mapIndx = $index - $metaData )
- "valueType": #if( $index > $metaData && $mapSuggestedValues.get($mapIndx).size() > 0 )
- "combobox"
- #else
- "textfield"
- #end,
- "suggested": [#set( $suggestedValues = $mapSuggestedValues.get($mapIndx) )
- #foreach( $suggestedValue in $suggestedValues )
- ["$!encoder.jsonEncode( ${suggestedValue} )"]
- #if( $velocityCount < $suggestedValues.size() ),#end
- #end],
- "name": "$col.name",
- "hidden": "$col.hidden"
- }#if( $velocityCount < $noHeader ),#end
- #set($index = $index + 1)
- #end
+ #set($index = 0)
+ #foreach( $col in $grid.getHeaders() )
+ {
+ #set( $mapIndx = $index - $metaData )
+ "valueType": #if( $index > $metaData && $mapSuggestedValues.get($mapIndx).size() > 0 )"combobox"#else"textfield"#end,
+ "suggested": [#set( $suggestedValues = $mapSuggestedValues.get($mapIndx) )
+ #foreach( $suggestedValue in $suggestedValues )
+ ["$!encoder.jsonEncode( ${suggestedValue} )"]#if( $velocityCount < $suggestedValues.size() ),#end
+ #end],
+ "name": "${col.name}",
+ "hidden": "${col.hidden}"
+ }#if( $velocityCount < $noHeader ),#end
+ #set( $index = $index + 1 )
+ #end
],
"items":[
[
- "$i18n.getString( 'filter' )",
- #foreach( $col in $grid.getHeaders() )"",#end
+ "$i18n.getString( 'filter' )",
+ #foreach( $col in [1..${noHeader}] )""#if( $velocityCount < $noHeader ),#end#end
],
#foreach( $row in $grid.getRows() )
[
