dhis2-devs team mailing list archive

[noreply@xxxxxxxxxxxxx]

------------------------------------------------------------
revno: 13132
committer: Lars Helge Øverland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Tue 2013-12-03 18:53:29 +0100
message:
  Event analytics, using double column types for numeric data elements and attributes in analytics tables. This in order to enabling sorting and aggregate operations.
modified:
  dhis-2/dhis-api/src/main/java/org/hisp/dhis/dataelement/DataElement.java
  dhis-2/dhis-api/src/main/java/org/hisp/dhis/patient/PatientAttribute.java
  dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcEventAnalyticsTableManager.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/dataelement/DataElement.java'
--- dhis-2/dhis-api/src/main/java/org/hisp/dhis/dataelement/DataElement.java	2013-10-08 17:16:47 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/dataelement/DataElement.java	2013-12-03 17:53:29 +0000
@@ -254,12 +254,20 @@
     }
 
     /**
+     * Indicates whether the value type of this data element is numeric.
+     */
+    public boolean isNumericType()
+    {
+        return VALUE_TYPE_INT.equals( type );
+    }
+    
+    /**
      * Returns the value type. If value type is int and the number type exists,
      * the number type is returned, otherwise the type is returned.
      */
     public String getDetailedNumberType()
     {
-        return (type != null && type.equals( VALUE_TYPE_INT ) && numberType != null) ? numberType : type;
+        return ( type != null && type.equals( VALUE_TYPE_INT ) && numberType != null) ? numberType : type;
     }
 
     /**
@@ -269,7 +277,7 @@
      */
     public String getDetailedTextType()
     {
-        return (type != null && type.equals( VALUE_TYPE_STRING ) && textType != null) ? textType : type;
+        return ( type != null && type.equals( VALUE_TYPE_STRING ) && textType != null) ? textType : type;
     }
     
     /** Returns whether aggregation should be skipped for this data element, based
@@ -416,7 +424,7 @@
     {
         return optionSet != null;
     }
-
+    
     // -------------------------------------------------------------------------
     // Getters and setters
     // -------------------------------------------------------------------------

=== modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/patient/PatientAttribute.java'
--- dhis-2/dhis-api/src/main/java/org/hisp/dhis/patient/PatientAttribute.java	2013-11-22 03:59:37 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/patient/PatientAttribute.java	2013-12-03 17:53:29 +0000
@@ -90,6 +90,18 @@
     }
 
     // -------------------------------------------------------------------------
+    // Logic
+    // -------------------------------------------------------------------------
+
+    /**
+     * Indicates whether the value type of this attribute is numeric.
+     */
+    public boolean isNumericType()
+    {
+        return TYPE_INT.equals( valueType );
+    }
+    
+    // -------------------------------------------------------------------------
     // Getters and setters
     // -------------------------------------------------------------------------
 

=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcEventAnalyticsTableManager.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcEventAnalyticsTableManager.java	2013-11-29 11:54:56 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcEventAnalyticsTableManager.java	2013-12-03 17:53:29 +0000
@@ -46,6 +46,7 @@
 import org.hisp.dhis.program.Program;
 import org.hisp.dhis.program.ProgramService;
 import org.hisp.dhis.system.util.DateUtils;
+import org.hisp.dhis.system.util.MathUtils;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.scheduling.annotation.Async;
 import org.springframework.transaction.annotation.Transactional;
@@ -181,6 +182,9 @@
     public List<String[]> getDimensionColumns( AnalyticsTable table )
     {
         final String dbl = statementBuilder.getDoubleColumnType();
+        final String text = "character varying(255)";
+        final String numericClause = " and value " + statementBuilder.getRegexpMatch() + " '" + MathUtils.NUMERIC_LENIENT_REGEXP + "'";
+        final String doubleSelect = "cast(value as " + dbl + ")";
         
         List<String[]> columns = new ArrayList<String[]>();
 
@@ -205,28 +209,36 @@
         
         for ( DataElement dataElement : table.getProgram().getAllDataElements() )
         {
-            String select = "(select value from patientdatavalue where programstageinstanceid=" +
-                "psi.programstageinstanceid and dataelementid=" + dataElement.getId() + ") as " + quote( dataElement.getUid() );
-            
-            String[] col = { quote( dataElement.getUid() ), "character varying(255)", select };
+            String dataType = dataElement.isNumericType() ? dbl : text;
+            String dataClause = dataElement.isNumericType() ? numericClause : "";
+            String select = dataElement.isNumericType() ? doubleSelect : "value";
+            
+            String sql = "(select " + select + " from patientdatavalue where programstageinstanceid=" +
+                "psi.programstageinstanceid and dataelementid=" + dataElement.getId() + dataClause + ") as " + quote( dataElement.getUid() );
+            
+            String[] col = { quote( dataElement.getUid() ), dataType, sql };
             columns.add( col );
         }
         
         for ( PatientAttribute attribute : table.getProgram().getPatientAttributes() )
         {
-            String select = "(select value from patientattributevalue where patientid=pi.patientid and " +
-                "patientattributeid=" + attribute.getId() + ") as " + quote( attribute.getUid() );
-            
-            String[] col = { quote( attribute.getUid() ), "character varying(255)", select };
+            String dataType = attribute.isNumericType() ? dbl : text;
+            String dataClause = attribute.isNumericType() ? numericClause : "";
+            String select = attribute.isNumericType() ? doubleSelect : "value";
+            
+            String sql = "(select " + select + " from patientattributevalue where patientid=pi.patientid and " +
+                "patientattributeid=" + attribute.getId() + dataClause + ") as " + quote( attribute.getUid() );
+            
+            String[] col = { quote( attribute.getUid() ), dataType, sql };
             columns.add( col );
         }
         
         for ( PatientIdentifierType identifierType : table.getProgram().getPatientIdentifierTypes() )
         {
-            String select = "(select identifier from patientidentifier where patientid=pi.patientid and " +
+            String sql = "(select identifier from patientidentifier where patientid=pi.patientid and " +
                 "patientidentifiertypeid=" + identifierType.getId() + ") as " + quote( identifierType.getUid() );
             
-            String[] col = { quote( identifierType.getUid() ), "character varying(31)", select };
+            String[] col = { quote( identifierType.getUid() ), "character varying(31)", sql };
             columns.add( col );
         }