X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=WEB-INF%2Flib%2FttReportHelper.class.php;h=11f8841fe265fb30e93de4a88833e830a555d2b0;hb=03c03896828d818303c12dfe2e368f5bd9e7dbae;hp=7a4ba2664c2a9e29154bb71e53197ab51295778b;hpb=913eba1427598741fcfb8d1e53746923c1f9e0e4;p=timetracker.git

diff --git a/WEB-INF/lib/ttReportHelper.class.php b/WEB-INF/lib/ttReportHelper.class.php
index 7a4ba266..11f8841f 100644
--- 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;
     }
 
@@ -468,11 +466,6 @@ class ttReportHelper {
     $res = $mdb2->query($sql);
     if (is_a($res, 'PEAR_Error')) die($res->getMessage());
     while ($val = $res->fetchRow()) {
-// TODO: consider writing a function that properly formats a date part in a multi-part key.
-//
-//      if ('date' == $group_by_option) {
-//        $val['group_field'] = ttDateToUserFormat($val['group_field']);
-//      }
       $time = $val['time'] ? sec_to_time_fmt_hm($val['time']) : null;
       $rowLabel = ttReportHelper::makeGroupByLabel($val['group_field'], $options);
       if ($options['show_cost']) {
@@ -480,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;
@@ -1201,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;
   }
 
@@ -1217,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) {
@@ -1237,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) {
@@ -1257,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).
@@ -1290,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) {
@@ -1304,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) {
@@ -1318,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).
@@ -1478,6 +1595,25 @@ class ttReportHelper {
     return $group_by_header;
   }
 
+  // makeGroupByXmlTag creates an xml tag for a totals only report using group_by1,
+  // group_by2, and group_by3 values passed in $options.
+  static function makeGroupByXmlTag($options) {
+    if ($options['group_by1'] != null && $options['group_by1'] != 'no_grouping') {
+      // We have group_by1.
+      $tag .= '_'.$options['group_by1'];
+    }
+    if ($options['group_by2'] != null && $options['group_by2'] != 'no_grouping') {
+      // We have group_by2.
+      $tag .= '_'.$options['group_by2'];
+    }
+    if ($options['group_by3'] != null && $options['group_by3'] != 'no_grouping') {
+      // We have group_by3.
+      $tag .= '_'.$options['group_by3'];
+    }
+    $tag = ltrim($tag, '_');
+    return $tag;
+  }
+
   // makeGroupByLabel builds a label for one row in a "Totals only" report of grouped by items.
   // It does one thing: if we are grouping by date, the date format is converted for user.
   static function makeGroupByLabel($key, $options) {