=== 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 22:33:33 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/program/ProgramStageInstanceService.java	2012-05-28 09:46:00 +0000
@@ -89,13 +89,13 @@
 
     List<ProgramStageInstance> getProgramStageInstances( Patient patient, Boolean completed );
     
-    Grid getTabularReport( List<Boolean> hiddenCols, 
+    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> organisationUnits,
         int level, Date startDate, Date endDate, boolean descOrder, Integer min, Integer max );
     
-    int getTabularReportCount( List<PatientIdentifierType> identifiers, List<String> fixedAttributes, List<PatientAttribute> attributes,
+    int getTabularReportCount( ProgramStage programStage, 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 );

=== 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 22:33:33 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/program/ProgramStageInstanceStore.java	2012-05-28 09:46:00 +0000
@@ -76,13 +76,13 @@
     
     List<ProgramStageInstance> get( ProgramStage programStage, OrganisationUnit orgunit, Date startDate, Date endDate, int min, int max );
     
-    Grid getTabularReport( List<Boolean> hiddenCols, Map<Integer, OrganisationUnitLevel> orgUnitLevelMap,
+    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,
         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,
+    int getTabularReportCount( ProgramStage programStage, 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/patient/startup/TableAlteror.java'
--- dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/patient/startup/TableAlteror.java	2012-05-24 03:10:00 +0000
+++ dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/patient/startup/TableAlteror.java	2012-05-28 09:46:00 +0000
@@ -95,6 +95,7 @@
         executeSql( "DROP TABLE programattribute" );
         
         executeSql( "ALTER TABLE patientattribute DROP COLUMN noChars" );
+        executeSql( "ALTER TABLE programstageinstance ALTER executiondate TYPE date" );
     }
 
     // -------------------------------------------------------------------------

=== 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 22:33:33 +0000
+++ dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/program/DefaultProgramStageInstanceService.java	2012-05-28 09:46:00 +0000
@@ -197,7 +197,7 @@
         return programStageInstanceStore.get( patient, completed );
     }
 
-    public Grid getTabularReport( List<Boolean> hiddenCols, 
+    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> organisationUnits,
@@ -207,18 +207,18 @@
         
         Map<Integer, OrganisationUnitLevel> orgUnitLevelMap = organisationUnitService.getOrganisationUnitLevelMap();
         
-        return programStageInstanceStore.getTabularReport( hiddenCols, orgUnitLevelMap, identifiers, fixedAttributes, attributes, 
+        return programStageInstanceStore.getTabularReport( programStage, hiddenCols, orgUnitLevelMap, identifiers, fixedAttributes, attributes, 
             dataElements, identifierKeys, attributeKeys, dataElementKeys, organisationUnits, level, maxLevel, startDate, endDate, descOrder, min, max );
     }
     
-    public int getTabularReportCount( List<PatientIdentifierType> identifiers, List<String> fixedAttributes, List<PatientAttribute> attributes,
+    public int getTabularReportCount( ProgramStage programStage, 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 )
     {
         int maxLevel = organisationUnitService.getMaxOfOrganisationUnitLevels();
         
-        return programStageInstanceStore.getTabularReportCount( identifiers, fixedAttributes, attributes, 
+        return programStageInstanceStore.getTabularReportCount( programStage, identifiers, fixedAttributes, attributes, 
             dataElements, identifierKeys, attributeKeys, dataElementKeys, organisationUnits, level, maxLevel, startDate, endDate );
     }
     

=== 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 22:33:33 +0000
+++ dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/program/hibernate/HibernateProgramStageInstanceStore.java	2012-05-28 09:46:00 +0000
@@ -185,7 +185,7 @@
             .setFirstResult( min ).setMaxResults( max ).list();
     }
 
-    public Grid getTabularReport( List<Boolean> hiddenCols, Map<Integer, OrganisationUnitLevel> orgUnitLevelMap,
+    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,
@@ -224,7 +224,7 @@
             grid.addHeader( new GridHeader( element.getDisplayName(), false, true ) );
         }
         
-        String sql = getTabularReportSql( false, identifiers, fixedAttributes, attributes, dataElements, 
+        String sql = getTabularReportSql( false, programStage, identifiers, fixedAttributes, attributes, dataElements, 
             identifierKeys, attributeKeys, dataElementKeys, orgUnits, level, maxLevel, startDate, endDate, descOrder, min, max );
         
         SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql );
@@ -234,18 +234,18 @@
         return grid;
     }
     
-    public int getTabularReportCount( List<PatientIdentifierType> identifiers, List<String> fixedAttributes, List<PatientAttribute> attributes,
+    public int getTabularReportCount( ProgramStage programStage, 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, 
+        String sql = getTabularReportSql( true, programStage, 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,
+    private String getTabularReportSql( boolean count, ProgramStage programStage, 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,
@@ -256,35 +256,35 @@
         
         String selector = count ? "count(*) " : "* ";
         
-        String sql = "select " + selector + "from ( select psi.executiondate, ";
+        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 + ", ";
+            sql += "(select name from organisationunit where organisationunitid=ous.idlevel" + l + ") as level_" + i + ",";
         }
 
         for ( PatientIdentifierType type : identifiers )
         {
-            sql += "(select identifier from patientidentifier where patientid=p.patientid and patientidentifiertypeid=" + type.getId() + ") as identifier_" + type.getId() + ", ";
+            sql += "(select identifier from patientidentifier where patientid=p.patientid and patientidentifiertypeid=" + type.getId() + ") as identifier_" + type.getId() + ",";
         }
         
         for ( String attribute : fixedAttributes )
         {
-            sql += "p." + attribute + ", ";
+            sql += "p." + attribute + ",";
         }
 
         for ( PatientAttribute attribute : attributes )
         {
-            sql += "(select value from patientattributevalue where patientid=p.patientid and patientattributeid=" + attribute.getId() + ") as attribute_" + attribute.getId() + ", ";
+            sql += "(select value from patientattributevalue where patientid=p.patientid and patientattributeid=" + attribute.getId() + ") as attribute_" + attribute.getId() + ",";
         }
 
         for ( DataElement element : dataElements )
         {
-            sql += "(select value from patientdatavalue where programstageinstanceid=psi.programstageinstanceid and dataelementid=" + element.getId() + ") as element_" + element.getId() + ", ";
+            sql += "(select value from patientdatavalue where programstageinstanceid=psi.programstageinstanceid and dataelementid=" + element.getId() + ") as element_" + element.getId() + ",";
         }
         
-        sql = sql.substring( 0, sql.length() - 2 ) + " "; // Removing last comma
+        sql = sql.substring( 0, sql.length() - 1 ) + " "; // Removing last comma
 
         sql += "from programstageinstance psi ";
         sql += "left join programinstance pi on (psi.programinstanceid=pi.programinstanceid) ";
@@ -292,7 +292,8 @@
         sql += "join organisationunit ou on (ou.organisationunitid=psi.organisationunitid) ";
         sql += "join _orgunitstructure ous on (psi.organisationunitid=ous.organisationunitid) ";
         
-        sql += "where psi.executiondate >= '" + sDate + "' ";
+        sql += "where psi.programstageid=" + programStage.getId() + " ";
+        sql += "and psi.executiondate >= '" + sDate + "' ";
         sql += "and psi.executiondate < '" + eDate + "' ";
 
         //TODO org unit criteria
@@ -301,7 +302,7 @@
         
         for ( int i = 0; i < maxLevel; i++ )
         {
-            sql += "level_" + i + ", ";
+            sql += "level_" + i + ",";
         }
         
         sql += "psi.executiondate ";
@@ -313,23 +314,23 @@
         
         for ( Integer key : identifierKeys.keySet() )
         {
-            sql += operator + "identifier_" + key + identifierKeys.get( key ) + ", ";
+            sql += operator + "identifier_" + key + identifierKeys.get( key ) + ",";
             operator = "and ";
         }
         
         for ( Integer key : attributeKeys.keySet() )
         {
-            sql += operator + "attribute_" + key + attributeKeys.get( key ) + ", ";
+            sql += operator + "attribute_" + key + attributeKeys.get( key ) + ",";
             operator = "and ";
         }
         
         for ( Integer key : dataElementKeys.keySet() )
         {
-            sql += operator + "element_" + key + dataElementKeys.get( key ) + ", ";
+            sql += operator + "element_" + key + dataElementKeys.get( key ) + ",";
             operator = "and ";
         }
 
-        sql = sql.substring( 0, sql.length() - 2 ) + " "; // Remove last comma
+        sql = sql.substring( 0, sql.length() - 1 ) + " "; // Remove last comma if exists
 
         log.info(sql);
         

=== 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 22:33:33 +0000
+++ dhis-2/dhis-web/dhis-web-caseentry/src/main/java/org/hisp/dhis/caseentry/action/report/GenerateTabularReportAction.java	2012-05-28 09:46:00 +0000
@@ -300,7 +300,7 @@
 
         ProgramStage programStage = programStageService.getProgramStage( programStageId );
 
-        //TODO include program stage in sql query
+        //TODO check sql traffic
         
         Date startValue = format.parseDate( startDate );
 
@@ -318,7 +318,7 @@
 
         if ( type == null ) // Tabular report
         {
-            int totalRecords = programStageInstanceService.getTabularReportCount( identifierTypes, fixedAttributes, patientAttributes, dataElements, 
+            int totalRecords = programStageInstanceService.getTabularReportCount( programStage, identifierTypes, fixedAttributes, patientAttributes, dataElements, 
                 searchingIdenKeys, searchingAttrKeys, searchingDEKeys, organisationUnits, level, startValue, endValue );
 
             total = getNumberOfPages( totalRecords );
@@ -326,14 +326,14 @@
             this.paging = createPaging( totalRecords );
             //total = paging.getTotal(); //TODO
             
-            grid = programStageInstanceService.getTabularReport( hiddenCols, identifierTypes,
+            grid = programStageInstanceService.getTabularReport( programStage, hiddenCols, identifierTypes,
                 fixedAttributes, patientAttributes, dataElements, searchingIdenKeys, searchingAttrKeys,
                 searchingDEKeys, organisationUnits, level, startValue, endValue, !orderByOrgunitAsc,
                 paging.getStartPos(), paging.getPageSize() );
         }
         else // Download as Excel
         {
-            grid = programStageInstanceService.getTabularReport( hiddenCols, identifierTypes,
+            grid = programStageInstanceService.getTabularReport( programStage, hiddenCols, identifierTypes,
                 fixedAttributes, patientAttributes, dataElements, searchingIdenKeys, searchingAttrKeys,
                 searchingDEKeys, organisationUnits, level, startValue, endValue, !orderByOrgunitAsc,
                 null, null );