dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #17587
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 7048: WIP tabular report
------------------------------------------------------------
revno: 7048
committer: Lars Helge Overland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Sat 2012-05-26 14:39:39 +0200
message:
WIP tabular report
modified:
dhis-2/dhis-api/src/main/java/org/hisp/dhis/common/Grid.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-support/dhis-support-system/pom.xml
dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/grid/GridUtils.java
dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/grid/ListGrid.java
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/common/Grid.java'
--- dhis-2/dhis-api/src/main/java/org/hisp/dhis/common/Grid.java 2012-04-03 08:51:42 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/common/Grid.java 2012-05-26 12:39:39 +0000
@@ -193,7 +193,7 @@
* Adds a regression column to the grid. Column must hold numeric data.
*
* @param columnIndex the index of the base column.
- * @oaram addHeader indicates whether to add a grid header for the regression column.
+ * @param addHeader indicates whether to add a grid header for the regression column.
*/
Grid addRegressionColumn( int columnIndex, boolean addHeader );
@@ -210,7 +210,7 @@
*
* @param rs the result set.
*/
- void addHeaders( ResultSet rs );
+ Grid addHeaders( ResultSet rs );
/**
* Moves the cursor the next row and adds values for each column of the given
@@ -218,5 +218,5 @@
*
* @param rs the result set.
*/
- void addRow( ResultSet rs );
+ Grid addRow( ResultSet rs );
}
=== 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-15 02:36:43 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/program/ProgramStageInstanceStore.java 2012-05-26 12:39:39 +0000
@@ -32,8 +32,14 @@
import java.util.Map;
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.patient.Patient;
+import org.hisp.dhis.patient.PatientAttribute;
+import org.hisp.dhis.patient.PatientIdentifierType;
/**
* @author Abyot Asalefew
@@ -71,6 +77,13 @@
List<ProgramStageInstance> get( ProgramStage programStage, OrganisationUnit orgunit, Date startDate, Date endDate, int min, int max );
+ 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,
+ Map<Integer, String> dataElementKeys, Collection<Integer> orgUnits,
+ int level, int maxLevel, Date startDate, Date endDate, boolean descOrder,
+ int min, int max, I18nFormat format, I18n i18n );
+
/** Get all values and put it on the map.
* @return key: key-word_object-id
* value: value
=== 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 10:46:29 +0000
+++ dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/program/DefaultProgramStageInstanceService.java 2012-05-26 12:39:39 +0000
@@ -204,6 +204,20 @@
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);
+ System.out.println("dataElements "+dataElements);
+ System.out.println("identifierKeys "+identifierKeys);
+ System.out.println("attributeKeys "+attributeKeys);
+ System.out.println("dataElementKeys "+dataElementKeys);
+
+ int maxLevel = organisationUnitService.getMaxOfOrganisationUnitLevels() - 1;
+
+ return programStageInstanceStore.getTabularReport( programStage, hiddenCols, 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,
=== 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-24 16:31:06 +0000
+++ dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/program/hibernate/HibernateProgramStageInstanceStore.java 2012-05-26 12:39:39 +0000
@@ -42,16 +42,26 @@
import org.hibernate.Query;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
+import org.hisp.dhis.common.Grid;
+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.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;
import org.hisp.dhis.program.ProgramStageInstanceStore;
+import org.hisp.dhis.system.grid.GridUtils;
+import org.hisp.dhis.system.grid.ListGrid;
import org.hisp.dhis.system.util.DateUtils;
+import org.springframework.jdbc.core.JdbcTemplate;
+import org.springframework.jdbc.support.rowset.SqlRowSet;
/**
* @author Abyot Asalefew
@@ -78,6 +88,13 @@
{
this.statementBuilder = statementBuilder;
}
+
+ private JdbcTemplate jdbcTemplate;
+
+ public void setJdbcTemplate( JdbcTemplate jdbcTemplate )
+ {
+ this.jdbcTemplate = jdbcTemplate;
+ }
// -------------------------------------------------------------------------
// Implemented methods
@@ -187,6 +204,93 @@
.setFirstResult( min ).setMaxResults( max ).list();
}
+ 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,
+ Map<Integer, String> dataElementKeys, Collection<Integer> orgUnits,
+ int level, int maxLevel, Date startDate, Date endDate, boolean descOrder,
+ int min, int max, I18nFormat format, I18n i18n )
+ {
+ 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 );
+
+ 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 count = 0;
+
+ for ( PatientIdentifierType type : identifiers )
+ {
+ sql += "(select identifier from patientidentifier where patientid=p.patientid and patientidentifiertypeid=" + type.getId() + ") as identifier" + count++ + ", ";
+ }
+
+ for ( String attribute : fixedAttributes )
+ {
+ 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++ + ", ";
+ }
+
+ count = 0;
+
+ for ( DataElement element : dataElements )
+ {
+ sql += "(select value from patientdatavalue where programstageinstanceid=psi.programstageinstanceid and dataelementid=" + element.getId() + ") as element" + count++ + ", ";
+ }
+
+ sql = sql.substring( 0, sql.length() - 2 ) + " "; // Remove last comma
+
+ sql += "from programstageinstance psi ";
+ sql += "left join programinstance pi on (psi.programinstanceid=pi.programinstanceid) ";
+ sql += "left join patient p on (pi.patientid=p.patientid) ";
+ sql += "join organisationunit ou on (ou.organisationunitid=psi.organisationunitid) ";
+ sql += "join _orgunitstructure ous on (psi.organisationunitid=ous.organisationunitid) ";
+
+ sql += "where psi.executiondate >= '" + sDate + "' ";
+ sql += "and psi.executiondate < '" + eDate + "' ";
+
+ //TODO org unit criteria
+
+ sql += "order by ";
+
+ for ( int i = 0; i < dataElements.size(); i++ )
+ {
+ sql += "element" + i + ", ";
+ }
+
+ sql += "psi.executiondate) ";
+ sql += descOrder ? "desc " : "";
+ sql += "as tabular offset 0 limit 50"; //TODO page and filter
+
+ return sql;
+ }
+
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,
=== 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-23 19:48:53 +0000
+++ dhis-2/dhis-services/dhis-service-patient/src/main/resources/META-INF/dhis/beans.xml 2012-05-26 12:39:39 +0000
@@ -39,6 +39,7 @@
<property name="sessionFactory" ref="sessionFactory" />
<property name="statementManager" ref="statementManager" />
<property name="statementBuilder" ref="statementBuilder" />
+ <property name="jdbcTemplate" ref="jdbcTemplate" />
</bean>
<bean id="org.hisp.dhis.program.ProgramInstanceStore"
=== modified file 'dhis-2/dhis-support/dhis-support-system/pom.xml'
--- dhis-2/dhis-support/dhis-support-system/pom.xml 2012-05-11 19:04:25 +0000
+++ dhis-2/dhis-support/dhis-support-system/pom.xml 2012-05-26 12:39:39 +0000
@@ -72,6 +72,10 @@
<artifactId>spring-web</artifactId>
</dependency>
<dependency>
+ <groupId>org.springframework</groupId>
+ <artifactId>spring-jdbc</artifactId>
+ </dependency>
+ <dependency>
<groupId>com.lowagie</groupId>
<artifactId>itext</artifactId>
</dependency>
=== modified file 'dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/grid/GridUtils.java'
--- dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/grid/GridUtils.java 2012-05-11 19:04:25 +0000
+++ dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/grid/GridUtils.java 2012-05-26 12:39:39 +0000
@@ -71,6 +71,7 @@
import org.hisp.dhis.system.util.MathUtils;
import org.hisp.dhis.system.util.StreamUtils;
import org.hisp.dhis.system.velocity.VelocityManager;
+import org.springframework.jdbc.support.rowset.SqlRowSet;
import com.lowagie.text.Document;
import com.lowagie.text.pdf.PdfPTable;
@@ -409,6 +410,24 @@
writer.closeDocument();
}
+ /**
+ * Writes all rows in the SqlRowSet to the given Grid.
+ */
+ public static void addRows( Grid grid, SqlRowSet rs )
+ {
+ int cols = rs.getMetaData().getColumnCount();
+
+ while ( rs.next() )
+ {
+ grid.addRow();
+
+ for ( int i = 1; i <= cols; i++ )
+ {
+ grid.addValue( rs.getObject( i ) );
+ }
+ }
+ }
+
// -------------------------------------------------------------------------
// Supportive methods
// -------------------------------------------------------------------------
=== modified file 'dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/grid/ListGrid.java'
--- dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/grid/ListGrid.java 2012-05-11 19:04:25 +0000
+++ dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/grid/ListGrid.java 2012-05-26 12:39:39 +0000
@@ -510,7 +510,7 @@
// SQL utility methods
// -------------------------------------------------------------------------
- public void addHeaders( ResultSet rs )
+ public Grid addHeaders( ResultSet rs )
{
try
{
@@ -527,19 +527,21 @@
{
throw new RuntimeException( ex );
}
+
+ return this;
}
- public void addRow( ResultSet rs )
+ public Grid addRow( ResultSet rs )
{
try
{
- int columnNo = rs.getMetaData().getColumnCount();
+ int cols = rs.getMetaData().getColumnCount();
while ( rs.next() )
{
addRow();
- for ( int i = 1; i <= columnNo; i++ )
+ for ( int i = 1; i <= cols; i++ )
{
addValue( rs.getObject( i ) );
}
@@ -549,8 +551,10 @@
{
throw new RuntimeException( ex );
}
+
+ return this;
}
-
+
// -------------------------------------------------------------------------
// Supportive methods
// -------------------------------------------------------------------------
=== 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 09:18:15 +0000
+++ 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
@@ -292,6 +292,8 @@
Set<Integer> bottomOrgunitIds = new HashSet<Integer>();
+ int maxLevel = organisationUnitService.getMaxOfOrganisationUnitLevels() - 1;
+
if ( facilityLB.equals( "selected" ) )
{
upperOrgunitIds.add( orgunitId );
@@ -315,11 +317,10 @@
// Get bottom org unit
- int maxLevel = organisationUnitService.getMaxOfOrganisationUnitLevels() - 1;
Collection<OrganisationUnit> orgUnits = organisationUnitService.getOrganisationUnitsAtLevel( maxLevel, selectedOrgunit );
bottomOrgunitIds = new HashSet<Integer>( ConversionUtils.getIdentifiers( OrganisationUnit.class, orgUnits ) );
- upperOrgunitIds.removeAll( bottomOrgunitIds );
+ upperOrgunitIds.removeAll( bottomOrgunitIds ); //TODO fix
}
// ---------------------------------------------------------------------
@@ -369,6 +370,8 @@
orderByExecutionDateByAsc, format, i18n );
}
+ System.out.println();
+ System.out.println(grid);
return type == null ? SUCCESS : type;
}
