Improved output of grouped subtotals on reports by including grouped values.

[timetracker.git] / WEB-INF / lib / ttReportHelper.class.php

diff --git a/WEB-INF/lib/ttReportHelper.class.php b/WEB-INF/lib/ttReportHelper.class.php
index 60cac07..11f8841 100644 (file)
--- a/WEB-INF/lib/ttReportHelper.class.php
+++ b/WEB-INF/lib/ttReportHelper.class.php
@@ -395,9 +395,6 @@ class ttReportHelper {
   static function getSubtotals($options) {
     global $user;
 
-    $group_fields = ttReportHelper::makeGroupByFieldsPart($options);
-    if (!$group_fields) return null;
-
     $mdb2 = getConnection();
 
     $concat_part = ttReportHelper::makeConcatPart($options);
@@ -452,15 +449,16 @@ class ttReportHelper {
       $concat_part = ttReportHelper::makeConcatExpensesPart($options);
       $join_part = ttReportHelper::makeJoinExpensesPart($options);
       $where = ttReportHelper::getExpenseWhere($options);
-      $group_by_part = ttReportHelper::makeGroupByExpensesPart($options);
+      $group_by_expenses_part = ttReportHelper::makeGroupByExpensesPart($options);
       $sql_for_expenses = "select $concat_part, null as time";
       if ($options['show_work_units']) $sql_for_expenses .= ", null as units";
-      $sql_for_expenses .= ", sum(ei.cost) as cost, sum(ei.cost) as expenses from tt_expense_items ei $join_part $where $group_by_part";
+      $sql_for_expenses .= ", sum(ei.cost) as cost, sum(ei.cost) as expenses from tt_expense_items ei $join_part $where $group_by_expenses_part";
 
       // Create a combined query.
-      $combined = "select group_field, sum(time) as time";
+      $fields = ttReportHelper::makeCombinedSelectPart($options);
+      $combined = "select $fields, sum(time) as time";
       if ($options['show_work_units']) $combined .= ", sum(units) as units";
-      $combined .= ", sum(cost) as cost, sum(expenses) as expenses from (($sql) union all ($sql_for_expenses)) t group by group_field";
+      $combined .= ", sum(cost) as cost, sum(expenses) as expenses from (($sql) union all ($sql_for_expenses)) t group by $fields";
       $sql = $combined;
     }
 
@@ -475,9 +473,9 @@ class ttReportHelper {
           $val['cost'] = str_replace('.', $user->decimal_mark, $val['cost']);
           $val['expenses'] = str_replace('.', $user->decimal_mark, $val['expenses']);
         }
-        $subtotals[$val['group_field']] = array('name'=>$rowLabel,'time'=>$time, 'units'=> $val['units'], 'cost'=>$val['cost'],'expenses'=>$val['expenses']);
+        $subtotals[$val['group_field']] = array('name'=>$rowLabel,'user'=>$val['user'],'project'=>$val['project'],'task'=>$val['task'],'client'=>$val['client'],'cf_1'=>$val['cf_1'],'time'=>$time,'units'=> $val['units'],'cost'=>$val['cost'],'expenses'=>$val['expenses']);
       } else
-        $subtotals[$val['group_field']] = array('name'=>$rowLabel,'time'=>$time, 'units'=> $val['units']);
+        $subtotals[$val['group_field']] = array('name'=>$rowLabel,'user'=>$val['user'],'project'=>$val['project'],'task'=>$val['task'],'client'=>$val['client'],'cf_1'=>$val['cf_1'],'time'=>$time, 'units'=> $val['units']);
     }
 
     return $subtotals;
@@ -1196,7 +1194,8 @@ class ttReportHelper {
     }
     // Remove garbage from the beginning.
     $group_by_parts = ltrim($group_by_parts, ', ');
-    $group_by_part = "group by $group_by_parts";
+    if ($group_by_parts)
+      $group_by_part = "group by $group_by_parts";
     return $group_by_part;
   }
 
@@ -1212,18 +1211,23 @@ class ttReportHelper {
         break;
       case 'user':
         $what_to_concat .= ", ' - ', u.name";
+        $fields_part .= ', u.name as user';
         break;
       case 'client':
         $what_to_concat .= ", ' - ', coalesce(c.name, 'Null')";
+        $fields_part .= ', c.name as client';
         break;
       case 'project':
         $what_to_concat .= ", ' - ', coalesce(p.name, 'Null')";
+        $fields_part .= ', p.name as project';
         break;
       case 'task':
         $what_to_concat .= ", ' - ', coalesce(t.name, 'Null')";
+        $fields_part .= ', t.name as task';
         break;
       case 'cf_1':
         $what_to_concat .= ", ' - ', coalesce(cfo.value, 'Null')";
+        $fields_part .= ', cfo.value as cf_1';
         break;
     }
     switch ($group_by2) {
@@ -1232,18 +1236,23 @@ class ttReportHelper {
         break;
       case 'user':
         $what_to_concat .= ", ' - ', u.name";
+        $fields_part .= ', u.name as user';
         break;
       case 'client':
         $what_to_concat .= ", ' - ', coalesce(c.name, 'Null')";
+        $fields_part .= ', c.name as client';
         break;
       case 'project':
         $what_to_concat .= ", ' - ', coalesce(p.name, 'Null')";
+        $fields_part .= ', p.name as project';
         break;
       case 'task':
         $what_to_concat .= ", ' - ', coalesce(t.name, 'Null')";
+        $fields_part .= ', t.name as task';
         break;
       case 'cf_1':
         $what_to_concat .= ", ' - ', coalesce(cfo.value, 'Null')";
+        $fields_part .= ', cfo.value as cf_1';
         break;
     }
     switch ($group_by3) {
@@ -1252,25 +1261,30 @@ class ttReportHelper {
         break;
       case 'user':
         $what_to_concat .= ", ' - ', u.name";
+        $fields_part .= ', u.name as user';
         break;
       case 'client':
         $what_to_concat .= ", ' - ', coalesce(c.name, 'Null')";
+        $fields_part .= ', c.name as client';
         break;
       case 'project':
         $what_to_concat .= ", ' - ', coalesce(p.name, 'Null')";
+        $fields_part .= ', p.name as project';
         break;
       case 'task':
         $what_to_concat .= ", ' - ', coalesce(t.name, 'Null')";
+        $fields_part .= ', t.name as task';
         break;
       case 'cf_1':
         $what_to_concat .= ", ' - ', coalesce(cfo.value, 'Null')";
+        $fields_part .= ', cfo.value as cf_1';
         break;
     }
     // Remove garbage from both ends.
     $what_to_concat = trim($what_to_concat, "', -");
     $concat_part = "concat($what_to_concat) as group_field";
     $concat_part = trim($concat_part, ' -');
-    return $concat_part;
+    return "$concat_part $fields_part";
   }
 
   // makeConcatPart builds a concatenation part for getSubtotals query (for expense items).
@@ -1285,12 +1299,25 @@ class ttReportHelper {
         break;
       case 'user':
         $what_to_concat .= ", ' - ', u.name";
+        $fields_part .= ', u.name as user';
         break;
       case 'client':
         $what_to_concat .= ", ' - ', coalesce(c.name, 'Null')";
+        $fields_part .= ', c.name as client';
         break;
       case 'project':
         $what_to_concat .= ", ' - ', coalesce(p.name, 'Null')";
+        $fields_part .= ', p.name as project';
+        break;
+
+      case 'task':
+        $what_to_concat .= ", ' - ', 'Null'";
+        $fields_part .= ', null as task';
+        break;
+
+      case 'cf_1':
+        $what_to_concat .= ", ' - ', 'Null'";
+        $fields_part .= ', null as cf_1';
         break;
     }
     switch ($group_by2) {
@@ -1299,12 +1326,25 @@ class ttReportHelper {
         break;
       case 'user':
         $what_to_concat .= ", ' - ', u.name";
+        $fields_part .= ', u.name as user';
         break;
       case 'client':
         $what_to_concat .= ", ' - ', coalesce(c.name, 'Null')";
+        $fields_part .= ', c.name as client';
         break;
       case 'project':
         $what_to_concat .= ", ' - ', coalesce(p.name, 'Null')";
+        $fields_part .= ', p.name as project';
+        break;
+
+      case 'task':
+        $what_to_concat .= ", ' - ', 'Null'";
+        $fields_part .= ', null as task';
+        break;
+
+      case 'cf_1':
+        $what_to_concat .= ", ' - ', 'Null'";
+        $fields_part .= ', null as cf_1';
         break;
     }
     switch ($group_by3) {
@@ -1313,19 +1353,101 @@ class ttReportHelper {
         break;
       case 'user':
         $what_to_concat .= ", ' - ', u.name";
+        $fields_part .= ', u.name as user';
         break;
       case 'client':
         $what_to_concat .= ", ' - ', coalesce(c.name, 'Null')";
+        $fields_part .= ', c.name as client';
         break;
       case 'project':
         $what_to_concat .= ", ' - ', coalesce(p.name, 'Null')";
+        $fields_part .= ', p.name as project';
+        break;
+
+      case 'task':
+        $what_to_concat .= ", ' - ', 'Null'";
+        $fields_part .= ', null as task';
+        break;
+
+      case 'cf_1':
+        $what_to_concat .= ", ' - ', 'Null'";
+        $fields_part .= ', null as cf_1';
         break;
     }
-    // Remove garbage from both ends.
-    $what_to_concat = trim($what_to_concat, "', -");
+    // Remove garbage from the beginning.
+    if ($what_to_concat)
+        $what_to_concat = substr($what_to_concat, 8);
     $concat_part = "concat($what_to_concat) as group_field";
-    $concat_part = trim($concat_part, ' -');
-    return $concat_part;
+    return "$concat_part $fields_part";
+  }
+
+  // makeCombinedSelectPart builds a list of fields for a combined select on a union for getSubtotals.
+  // This is used when we include expenses.
+  static function makeCombinedSelectPart($options) {
+    $group_by1 = $options['group_by1'];
+    $group_by2 = $options['group_by2'];
+    $group_by3 = $options['group_by3'];
+
+    $fields = "group_field";
+
+    switch ($group_by1) {
+      case 'user':
+        $fields .= ', user';
+        break;
+      case 'client':
+        $fields_part .= ', client';
+        break;
+      case 'project':
+        $fields .= ', project';
+        break;
+
+      case 'task':
+        $fields .= ', task';
+        break;
+
+      case 'cf_1':
+        $fields .= ', cf_1';
+        break;
+    }
+    switch ($group_by2) {
+      case 'user':
+        $fields .= ', user';
+        break;
+      case 'client':
+        $fields_part .= ', client';
+        break;
+      case 'project':
+        $fields .= ', project';
+        break;
+
+      case 'task':
+        $fields .= ', task';
+        break;
+
+      case 'cf_1':
+        $fields .= ', cf_1';
+        break;
+    }
+    switch ($group_by3) {
+      case 'user':
+        $fields .= ', user';
+        break;
+      case 'client':
+        $fields_part .= ', client';
+        break;
+      case 'project':
+        $fields .= ', project';
+        break;
+
+      case 'task':
+        $fields .= ', task';
+        break;
+
+      case 'cf_1':
+        $fields .= ', cf_1';
+        break;
+    }
+    return $fields;
   }
 
   // makeJoinPart builds a left join part for getSubtotals query (for time items).