dhis2-devs team mailing list archive

[noreply@xxxxxxxxxxxxx]

------------------------------------------------------------
revno: 18595
committer: jimgrace@xxxxxxxxx
branch nick: dhis2
timestamp: Thu 2015-03-12 14:00:07 -0400
message:
  Rewrite approval SQL query without derived table.
modified:
  dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/dataapproval/hibernate/HibernateDataApprovalStore.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-services/dhis-service-core/src/main/java/org/hisp/dhis/dataapproval/hibernate/HibernateDataApprovalStore.java'
--- dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/dataapproval/hibernate/HibernateDataApprovalStore.java	2015-02-26 16:45:09 +0000
+++ dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/dataapproval/hibernate/HibernateDataApprovalStore.java	2015-03-12 18:00:07 +0000
@@ -328,8 +328,8 @@
                     "left join dataapproval da on da.organisationunitid = ous.organisationunitid " +
                     "and da.dataapprovallevelid = " + dal.getId() + " and da.periodid in (" + periodIds + ") " +
                     "and da.datasetid in (" + dataSetIds + ") " +
-                    "and da.attributeoptioncomboid = a.categoryoptioncomboid " +
-                    "where ous.idlevel" + orgUnitLevel + " = a.organisationunitid " +
+                    "and da.attributeoptioncomboid = cocco.categoryoptioncomboid " +
+                    "where ous.idlevel" + orgUnitLevel + " = o.organisationunitid " +
                     "and ous.level = " + dal.getOrgUnitLevel() + " " +
                     "and ( da.dataapprovalid is null " + ( acceptanceRequiredForApproval ? "or not da.accepted " : "" ) + ") )";
                 break;
@@ -342,29 +342,27 @@
         {
             approvedAboveSubquery = "exists(select 1 from dataapproval da " +
                 "join dataapprovallevel dal on dal.dataapprovallevelid = da.dataapprovallevelid " +
-                "join _orgunitstructure ou on ou.organisationunitid = a.organisationunitid and ou.idlevel" + orgUnitLevelAbove + " = da.organisationunitid " +
-                "where da.periodid in (" + periodIds + ") and da.datasetid in (" + dataSetIds + ") and da.attributeoptioncomboid = a.categoryoptioncomboid) ";
+                "join _orgunitstructure ou on ou.organisationunitid = o.organisationunitid and ou.idlevel" + orgUnitLevelAbove + " = da.organisationunitid " +
+                "where da.periodid in (" + periodIds + ") and da.datasetid in (" + dataSetIds + ") and da.attributeoptioncomboid = cocco.categoryoptioncomboid) ";
         }
 
         final String sql =
-            "select a.categoryoptioncomboid, a.organisationunitid, " +
+            "select cocco.categoryoptioncomboid, o.organisationunitid, " +
             "(select min(coalesce(dal.level, 0)) from period p " +
             "left join dataapproval da on da.datasetid in (" + dataSetIds + ") and da.periodid = p.periodid " +
-                    "and da.attributeoptioncomboid = a.categoryoptioncomboid and da.organisationunitid = a.organisationunitid " +
+                    "and da.attributeoptioncomboid = cocco.categoryoptioncomboid and da.organisationunitid = o.organisationunitid " +
                 "left join dataapprovallevel dal on dal.dataapprovallevelid = da.dataapprovallevelid " +
                 "where p.periodid in (" + periodIds + ") " +
             ") as highest_approved_level, " +
             "(select substring(min(concat(100000 + coalesce(dal.level, 0), coalesce(da.accepted, FALSE))) from 7) from period p " +
                 "left join dataapproval da on da.datasetid in (" + dataSetIds + ") and da.periodid = p.periodid " +
-                    "and da.attributeoptioncomboid = a.categoryoptioncomboid and da.organisationunitid = a.organisationunitid " +
+                    "and da.attributeoptioncomboid = cocco.categoryoptioncomboid and da.organisationunitid = o.organisationunitid " +
                 "left join dataapprovallevel dal on dal.dataapprovallevelid = da.dataapprovallevelid " +
                 "where p.periodid in (" + periodIds + ") " +
             ") as accepted_at_highest_level, " +
             readyBelowSubquery + " as ready_below, " +
             approvedAboveSubquery + " as approved_above " +
-            "from ( " + // subquery to get combinations of organisation unit and category option combo
-                "select distinct cocco.categoryoptioncomboid, ccoc.categorycomboid, coalesce(coo.organisationunitid, o.organisationunitid) as organisationunitid " +
-                "from categoryoptioncombos_categoryoptions cocco " +
+            "from categoryoptioncombos_categoryoptions cocco " +
                 "join categorycombos_optioncombos ccoc on ccoc.categoryoptioncomboid = cocco.categoryoptioncomboid and ccoc.categorycomboid in (" + categoryComboIds + ") " +
                 "join dataelementcategoryoption co on co.categoryoptionid = cocco.categoryoptionid " +
                     "and (co.startdate is null or co.startdate <= '" + maxDate + "') and (co.enddate is null or co.enddate >= '" + minDate + "') " +
@@ -372,14 +370,13 @@
                 "left join categoryoption_organisationunits coo on coo.categoryoptionid = co.categoryoptionid " +
                 "left join _orgunitstructure ous on ous.idlevel" + orgUnitLevel + " = o.organisationunitid and ous.organisationunitid = coo.organisationunitid " +
                 joinAncestors +
-                "left join dataelementcategoryoptionusergroupaccesses couga on couga.categoryoptionid = cocco.categoryoptionid " +
-                "left join usergroupaccess uga on uga.usergroupaccessid = couga.usergroupaccessid " +
-                "left join usergroupmembers ugm on ugm.usergroupid = uga.usergroupid " +
-                "where ( coo.categoryoptionid is null or ous.organisationunitid is not null " + testAncestors + ") " +
-                 ( isSuperUser || user == null ? "" : "and ( ugm.userid = " + user.getId() + " or co.userid = " + user.getId() + " " +
-                         "or co.publicaccess is null or left(co.publicaccess, 1) = 'r' ) " ) +
-                 ( attributeOptionCombo == null ? "" : "and cocco.categoryoptioncomboid = " + attributeOptionCombo.getId() + " " ) +
-            ") as a";
+                "where ( coo.categoryoptionid is null or ous.organisationunitid is not null " + testAncestors + ")" +
+                 ( isSuperUser || user == null ? "" :
+                         " and ( co.publicaccess is null or left(co.publicaccess, 1) = 'r' or co.userid = " + user.getId() + " or exists ( " +
+                                 "select 1 from dataelementcategoryoptionusergroupaccesses couga " +
+                                 "left join usergroupaccess uga on uga.usergroupaccessid = couga.usergroupaccessid " +
+                                 "left join usergroupmembers ugm on ugm.usergroupid = uga.usergroupid " +
+                                 "where couga.categoryoptionid = cocco.categoryoptionid and ugm.userid = " + user.getId() + ") )" );
 
         log.debug( "Get approval SQL: " + sql );