Initial attempt to refactor ttReportHelper::getSubtotals to use multiple group by.

diff --git a/WEB-INF/lib/ttReportHelper.class.php b/WEB-INF/lib/ttReportHelper.class.php
index e80e7d03c1a4271ff9a284800974090bbf572c2e..454b437dfeacb8591052e5eb337a773f6223ddba 100644 (file)
--- a/WEB-INF/lib/ttReportHelper.class.php
+++ b/WEB-INF/lib/ttReportHelper.class.php
@@ -397,51 +397,20 @@ class ttReportHelper {
   static function getSubtotals($options) {
     global $user;
 
   static function getSubtotals($options) {
     global $user;
 

-    $group_by_option = $options['group_by1'];
-    if ('no_grouping' == $group_by_option) return null;
+    $group_fields = ttReportHelper::makeGroupByFieldsPart($options);
+    if (!$group_fields) return null;

 
     $mdb2 = getConnection();
 
 
     $mdb2 = getConnection();
 

-    // Start with sql to obtain subtotals for time items. This simple sql will be used when we have no expenses.
-
-    // Determine group by field and a required join.
-    switch ($group_by_option) {
-      case 'date':
-        $group_field = 'l.date';
-        $group_join = '';
-        break;
-      case 'user':
-        $group_field = 'u.name';
-        $group_join = 'left join tt_users u on (l.user_id = u.id) ';
-        break;
-      case 'client':
-        $group_field = 'c.name';
-        $group_join = 'left join tt_clients c on (l.client_id = c.id) ';
-        break;
-      case 'project':
-        $group_field = 'p.name';
-        $group_join = 'left join tt_projects p on (l.project_id = p.id) ';
-        break;
-      case 'task':
-        $group_field = 't.name';
-        $group_join = 'left join tt_tasks t on (l.task_id = t.id) ';
-        break;
-      case 'cf_1':
-        $group_field = 'cfo.value';
-        $custom_fields = new CustomFields($user->group_id);
-        if ($custom_fields->fields[0]['type'] == CustomFields::TYPE_TEXT)
-          $group_join = 'left join tt_custom_field_log cfl on (l.id = cfl.log_id and cfl.status = 1) left join tt_custom_field_options cfo on (cfl.value = cfo.id) ';
-        elseif ($custom_fields->fields[0]['type'] == CustomFields::TYPE_DROPDOWN)
-          $group_join = 'left join tt_custom_field_log cfl on (l.id = cfl.log_id and cfl.status = 1) left join tt_custom_field_options cfo on (cfl.option_id = cfo.id) ';
-        break;
-    }
-
+    $concat_part = ttReportHelper::makeConcatPart($options);
+    $join_part = ttReportHelper::makeJoinPart($options);

     $where = ttReportHelper::getWhere($options);
     $where = ttReportHelper::getWhere($options);

+    $group_by_part = ttReportHelper::makeGroupByPart($options);

     if ($options['show_cost']) {
       if (MODE_TIME == $user->tracking_mode) {
     if ($options['show_cost']) {
       if (MODE_TIME == $user->tracking_mode) {

-        if ($group_by_option != 'user')
+        if (!ttReportHelper::groupingByUser($options))

           $left_join = 'left join tt_users u on (l.user_id = u.id)';
           $left_join = 'left join tt_users u on (l.user_id = u.id)';

-        $sql = "select $group_field as group_field, sum(time_to_sec(l.duration)) as time";
+        $sql = "select $concat_part, sum(time_to_sec(l.duration)) as time";

         if ($options['show_work_units']) {
           if ($user->unit_totals_only)
             $sql .= ", if (sum(l.billable * time_to_sec(l.duration)/60) < $user->first_unit_threshold, 0, ceil(sum(l.billable * time_to_sec(l.duration)/60/$user->minutes_in_unit))) as units";
         if ($options['show_work_units']) {
           if ($user->unit_totals_only)
             $sql .= ", if (sum(l.billable * time_to_sec(l.duration)/60) < $user->first_unit_threshold, 0, ceil(sum(l.billable * time_to_sec(l.duration)/60/$user->minutes_in_unit))) as units";


@@ -450,10 +419,10 @@ class ttReportHelper {
         }
         $sql .= ", sum(cast(l.billable * coalesce(u.rate, 0) * time_to_sec(l.duration)/3600 as decimal(10, 2))) as cost,
           null as expenses from tt_log l
         }
         $sql .= ", sum(cast(l.billable * coalesce(u.rate, 0) * time_to_sec(l.duration)/3600 as decimal(10, 2))) as cost,
           null as expenses from tt_log l

-          $group_join $left_join $where group by $group_field";
+          $join_part $left_join $where $group_by_part";

       } else {
         // If we are including cost and tracking projects, our query (the same as above) needs to join the tt_user_project_binds table.
       } else {
         // If we are including cost and tracking projects, our query (the same as above) needs to join the tt_user_project_binds table.

-        $sql = "select $group_field as group_field, sum(time_to_sec(l.duration)) as time";
+        $sql = "select $concat_part, sum(time_to_sec(l.duration)) as time";

         if ($options['show_work_units']) {
           if ($user->unit_totals_only)
             $sql .= ", if (sum(l.billable * time_to_sec(l.duration)/60) < $user->first_unit_threshold, 0, ceil(sum(l.billable * time_to_sec(l.duration)/60/$user->minutes_in_unit))) as units";
         if ($options['show_work_units']) {
           if ($user->unit_totals_only)
             $sql .= ", if (sum(l.billable * time_to_sec(l.duration)/60) < $user->first_unit_threshold, 0, ceil(sum(l.billable * time_to_sec(l.duration)/60/$user->minutes_in_unit))) as units";


@@ -462,11 +431,12 @@ class ttReportHelper {
         }
         $sql .= ", sum(cast(l.billable * coalesce(upb.rate, 0) * time_to_sec(l.duration)/3600 as decimal(10,2))) as cost,
           null as expenses from tt_log l 
         }
         $sql .= ", sum(cast(l.billable * coalesce(upb.rate, 0) * time_to_sec(l.duration)/3600 as decimal(10,2))) as cost,
           null as expenses from tt_log l 

-          $group_join
-          left join tt_user_project_binds upb on (l.user_id = upb.user_id and l.project_id = upb.project_id) $where group by $group_field";
+          $join_part
+          left join tt_user_project_binds upb on (l.user_id = upb.user_id and l.project_id = upb.project_id) $where $group_by_part";

       }
       }

-    } else {
-      $sql = "select $group_field as group_field, sum(time_to_sec(l.duration)) as time";
+    }  else {
+      // $sql = "select $group_field as group_field, sum(time_to_sec(l.duration)) as time";
+      $sql = "select $concat_part, sum(time_to_sec(l.duration)) as time";

       if ($options['show_work_units']) {
         if ($user->unit_totals_only)
           $sql .= ", if (sum(l.billable * time_to_sec(l.duration)/60) < $user->first_unit_threshold, 0, ceil(sum(l.billable * time_to_sec(l.duration)/60/$user->minutes_in_unit))) as units";
       if ($options['show_work_units']) {
         if ($user->unit_totals_only)
           $sql .= ", if (sum(l.billable * time_to_sec(l.duration)/60) < $user->first_unit_threshold, 0, ceil(sum(l.billable * time_to_sec(l.duration)/60/$user->minutes_in_unit))) as units";


@@ -474,41 +444,20 @@ class ttReportHelper {
           $sql .= ", sum(if(l.billable = 0 or time_to_sec(l.duration)/60 < $user->first_unit_threshold, 0, ceil(time_to_sec(l.duration)/60/$user->minutes_in_unit))) as units";
       }
       $sql .= ", null as expenses from tt_log l 
           $sql .= ", sum(if(l.billable = 0 or time_to_sec(l.duration)/60 < $user->first_unit_threshold, 0, ceil(time_to_sec(l.duration)/60/$user->minutes_in_unit))) as units";
       }
       $sql .= ", null as expenses from tt_log l 

-        $group_join $where group by $group_field";
+        $join_part $where $group_by_part";

     }
     // By now we have sql for time items.
 
     // However, when we have expenses, we need to do a union with a separate query for expense items from tt_expense_items table.
     if ($options['show_cost'] && $user->isPluginEnabled('ex')) { // if ex(penses) plugin is enabled
 
     }
     // By now we have sql for time items.
 
     // However, when we have expenses, we need to do a union with a separate query for expense items from tt_expense_items table.
     if ($options['show_cost'] && $user->isPluginEnabled('ex')) { // if ex(penses) plugin is enabled
 

-      // Determine group by field and a required join.
-      $group_join = null;
-      $group_field = 'null';
-      switch ($group_by_option) {
-        case 'date':
-          $group_field = 'ei.date';
-          $group_join = '';
-          break;
-        case 'user':
-          $group_field = 'u.name';
-          $group_join = 'left join tt_users u on (ei.user_id = u.id) ';
-          break;
-        case 'client':
-          $group_field = 'c.name';
-          $group_join = 'left join tt_clients c on (ei.client_id = c.id) ';
-          break;
-        case 'project':
-          $group_field = 'p.name';
-          $group_join = 'left join tt_projects p on (ei.project_id = p.id) ';
-          break;
-      }
-
+      $concat_part = ttReportHelper::makeConcatExpensesPart($options);
+      $join_part = ttReportHelper::makeJoinExpensesPart($options);

       $where = ttReportHelper::getExpenseWhere($options);
       $where = ttReportHelper::getExpenseWhere($options);

-      $sql_for_expenses = "select $group_field as group_field, null as time";
+      $group_by_part = ttReportHelper::makeGroupByExpensesPart($options);
+      $sql_for_expenses = "select $concat_part, null as time";

       if ($options['show_work_units']) $sql_for_expenses .= ", null as units";
       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 $group_join $where";
-      // Add a "group by" clause if we are grouping.
-      if ('null' != $group_field) $sql_for_expenses .= " group by $group_field";
+      $sql_for_expenses .= ", sum(ei.cost) as cost, sum(ei.cost) as expenses from tt_expense_items ei $join_part $where $group_by_part";

 
       // Create a combined query.
       $combined = "select group_field, sum(time) as time";
 
       // Create a combined query.
       $combined = "select group_field, sum(time) as time";


@@ -520,11 +469,12 @@ class ttReportHelper {
     // Execute query.
     $res = $mdb2->query($sql);
     if (is_a($res, 'PEAR_Error')) die($res->getMessage());
     // Execute query.
     $res = $mdb2->query($sql);
     if (is_a($res, 'PEAR_Error')) die($res->getMessage());

-

     while ($val = $res->fetchRow()) {
     while ($val = $res->fetchRow()) {

-      if ('date' == $group_by_option) {
-        $val['group_field'] = ttDateToUserFormat($val['group_field']);
-      }
+// 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;
       if ($options['show_cost']) {
         if ('.' != $user->decimal_mark) {
       $time = $val['time'] ? sec_to_time_fmt_hm($val['time']) : null;
       if ($options['show_cost']) {
         if ('.' != $user->decimal_mark) {


@@ -1090,31 +1040,426 @@ class ttReportHelper {
     return true;
   }
 
     return true;
   }
 

-  // makeGroupByKey - builds a combined group by key from group_by1, group_by2 and group_by3 values
+  // makeGroupByKey builds a combined group by key from group_by1, group_by2 and group_by3 values

   // (passed in $options) and a row of data ($row obtained from a db query).
   static function makeGroupByKey($options, $row) {
     if ($options['group_by1'] != null && $options['group_by1'] != 'no_grouping') {
       // We have group_by1.
       $group_by1 = $options['group_by1'];
       $group_by1_value = $row[$group_by1];
   // (passed in $options) and a row of data ($row obtained from a db query).
   static function makeGroupByKey($options, $row) {
     if ($options['group_by1'] != null && $options['group_by1'] != 'no_grouping') {
       // We have group_by1.
       $group_by1 = $options['group_by1'];
       $group_by1_value = $row[$group_by1];

-      if ($group_by1 == 'date') $group_by1_value = ttDateToUserFormat($group_by1_value);
+      //if ($group_by1 == 'date') $group_by1_value = ttDateToUserFormat($group_by1_value);
+      if (empty($group_by1_value)) $group_by1_value = 'Null'; // To match what comes out of makeConcatPart.

       $group_by_key .= ' - '.$group_by1_value;
     }
     if ($options['group_by2'] != null && $options['group_by2'] != 'no_grouping') {
       // We have group_by2.
       $group_by2 = $options['group_by2'];
       $group_by2_value = $row[$group_by2];
       $group_by_key .= ' - '.$group_by1_value;
     }
     if ($options['group_by2'] != null && $options['group_by2'] != 'no_grouping') {
       // We have group_by2.
       $group_by2 = $options['group_by2'];
       $group_by2_value = $row[$group_by2];

-      if ($group_by2 == 'date') $group_by2_value = ttDateToUserFormat($group_by2_value);
+      //if ($group_by2 == 'date') $group_by2_value = ttDateToUserFormat($group_by2_value);
+      if (empty($group_by2_value)) $group_by2_value = 'Null'; // To match what comes out of makeConcatPart.

       $group_by_key .= ' - '.$group_by2_value;
     }
     if ($options['group_by3'] != null && $options['group_by3'] != 'no_grouping') {
       // We have group_by3.
       $group_by3 = $options['group_by3'];
       $group_by3_value = $row[$group_by3];
       $group_by_key .= ' - '.$group_by2_value;
     }
     if ($options['group_by3'] != null && $options['group_by3'] != 'no_grouping') {
       // We have group_by3.
       $group_by3 = $options['group_by3'];
       $group_by3_value = $row[$group_by3];

-      if ($group_by3 == 'date') $group_by3_value = ttDateToUserFormat($group_by3_value);
+      //if ($group_by3 == 'date') $group_by3_value = ttDateToUserFormat($group_by3_value);
+      if (empty($group_by3_value)) $group_by3_value = 'Null'; // To match what comes out of makeConcatPart.

       $group_by_key .= ' - '.$group_by3_value;
     }
     $group_by_key = trim($group_by_key, ' -');
     return $group_by_key;
   }
       $group_by_key .= ' - '.$group_by3_value;
     }
     $group_by_key = trim($group_by_key, ' -');
     return $group_by_key;
   }

+
+  // makeGroupByPart builds a combined group by part for sql query for time items using group_by1,
+  // group_by2, and group_by3 values passed in $options.
+  static function makeGroupByPart($options) {
+    $no_grouping = ($options['group_by1'] == null || $options['group_by1'] == 'no_grouping') &&
+      ($options['group_by2'] == null || $options['group_by2'] == 'no_grouping') &&
+      ($options['group_by3'] == null || $options['group_by3'] == 'no_grouping');
+    if ($no_grouping) return null;
+
+    $group_by1 = $options['group_by1'];
+    $group_by2 = $options['group_by2'];
+    $group_by3 = $options['group_by3'];
+
+    switch ($group_by1) {
+      case 'date':
+        $group_by_parts .= ', l.date';
+        break;
+      case 'user':
+        $group_by_parts .= ', u.name';
+        break;
+      case 'client':
+        $group_by_parts .= ', c.name';
+        break;
+      case 'project':
+        $group_by_parts .= ', p.name';
+        break;
+      case 'task':
+        $group_by_parts .= ', t.name';
+        break;
+      case 'cf_1':
+        $group_by_parts .= ', cfo.value';
+        break;
+    }
+    switch ($group_by2) {
+      case 'date':
+        $group_by_parts .= ', l.date';
+        break;
+      case 'user':
+        $group_by_parts .= ', u.name';
+        break;
+      case 'client':
+        $group_by_parts .= ', c.name';
+        break;
+      case 'project':
+        $group_by_parts .= ', p.name';
+        break;
+      case 'task':
+        $group_by_parts .= ', t.name';
+        break;
+      case 'cf_1':
+        $group_by_parts .= ', cfo.value';
+        break;
+    }
+    switch ($group_by3) {
+      case 'date':
+        $group_by_parts .= ', l.date';
+        break;
+      case 'user':
+        $group_by_parts .= ', u.name';
+        break;
+      case 'client':
+        $group_by_parts .= ', c.name';
+        break;
+      case 'project':
+        $group_by_parts .= ', p.name';
+        break;
+      case 'task':
+        $group_by_parts .= ', t.name';
+        break;
+      case 'cf_1':
+        $group_by_parts .= ', cfo.value';
+        break;
+    }
+    // Remove garbage from the beginning.
+    $group_by_parts = ltrim($group_by_parts, ', ');
+    $group_by_part = "group by $group_by_parts";
+    return $group_by_part;
+  }
+
+  // makeGroupByExpensesPart builds a combined group by part for sql query for expense items using
+  // group_by1, group_by2, and group_by3 values passed in $options.
+  static function makeGroupByExpensesPart($options) {
+    $no_grouping = ($options['group_by1'] == null || $options['group_by1'] == 'no_grouping') &&
+      ($options['group_by2'] == null || $options['group_by2'] == 'no_grouping') &&
+      ($options['group_by3'] == null || $options['group_by3'] == 'no_grouping');
+    if ($no_grouping) return null;
+
+    $group_by1 = $options['group_by1'];
+    $group_by2 = $options['group_by2'];
+    $group_by3 = $options['group_by3'];
+
+    switch ($group_by1) {
+      case 'date':
+        $group_by_parts .= ', ei.date';
+        break;
+      case 'user':
+        $group_by_parts .= ', u.name';
+        break;
+      case 'client':
+        $group_by_parts .= ', c.name';
+        break;
+      case 'project':
+        $group_by_parts .= ', p.name';
+        break;
+    }
+    switch ($group_by2) {
+      case 'date':
+        $group_by_parts .= ', ei.date';
+        break;
+      case 'user':
+        $group_by_parts .= ', u.name';
+        break;
+      case 'client':
+        $group_by_parts .= ', c.name';
+        break;
+      case 'project':
+        $group_by_parts .= ', p.name';
+        break;
+    }
+    switch ($group_by3) {
+      case 'date':
+        $group_by_parts .= ', ei.date';
+        break;
+      case 'user':
+        $group_by_parts .= ', u.name';
+        break;
+      case 'client':
+        $group_by_parts .= ', c.name';
+        break;
+      case 'project':
+        $group_by_parts .= ', p.name';
+        break;
+    }
+    // Remove garbage from the beginning.
+    $group_by_parts = ltrim($group_by_parts, ', ');
+    $group_by_part = "group by $group_by_parts";
+    return $group_by_part;
+  }
+
+  // makeConcatPart builds a concatenation part for getSubtotals query (for time items).
+  static function makeConcatPart($options) {
+    $group_by1 = $options['group_by1'];
+    $group_by2 = $options['group_by2'];
+    $group_by3 = $options['group_by3'];
+
+    switch ($group_by1) {
+      case 'date':
+        $what_to_concat .= ", ' - ', l.date";
+        break;
+      case 'user':
+        $what_to_concat .= ", ' - ', u.name";
+        break;
+      case 'client':
+        $what_to_concat .= ", ' - ', coalesce(c.name, 'Null')";
+        break;
+      case 'project':
+        $what_to_concat .= ", ' - ', coalesce(p.name, 'Null')";
+        break;
+      case 'task':
+        $what_to_concat .= ", ' - ', coalesce(t.name, 'Null')";
+        break;
+      case 'cf_1':
+        $what_to_concat .= ", ' - ', coalesce(cfo.value, 'Null')";
+        break;
+    }
+    switch ($group_by2) {
+      case 'date':
+        $what_to_concat .= ", ' - ', l.date";
+        break;
+      case 'user':
+        $what_to_concat .= ", ' - ', u.name";
+        break;
+      case 'client':
+        $what_to_concat .= ", ' - ', coalesce(c.name, 'Null')";
+        break;
+      case 'project':
+        $what_to_concat .= ", ' - ', coalesce(p.name, 'Null')";
+        break;
+      case 'task':
+        $what_to_concat .= ", ' - ', coalesce(t.name, 'Null')";
+        break;
+      case 'cf_1':
+        $what_to_concat .= ", ' - ', coalesce(cfo.value, 'Null')";
+        break;
+    }
+    switch ($group_by3) {
+      case 'date':
+        $what_to_concat .= ", ' - ', l.date";
+        break;
+      case 'user':
+        $what_to_concat .= ", ' - ', u.name";
+        break;
+      case 'client':
+        $what_to_concat .= ", ' - ', coalesce(c.name, 'Null')";
+        break;
+      case 'project':
+        $what_to_concat .= ", ' - ', coalesce(p.name, 'Null')";
+        break;
+      case 'task':
+        $what_to_concat .= ", ' - ', coalesce(t.name, 'Null')";
+        break;
+      case 'cf_1':
+        $what_to_concat .= ", ' - ', coalesce(cfo.value, 'Null')";
+        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;
+  }
+
+  // makeConcatPart builds a concatenation part for getSubtotals query (for expense items).
+  static function makeConcatExpensesPart($options) {
+    $group_by1 = $options['group_by1'];
+    $group_by2 = $options['group_by2'];
+    $group_by3 = $options['group_by3'];
+
+    switch ($group_by1) {
+      case 'date':
+        $what_to_concat .= ", ' - ', ei.date";
+        break;
+      case 'user':
+        $what_to_concat .= ", ' - ', u.name";
+        break;
+      case 'client':
+        $what_to_concat .= ", ' - ', coalesce(c.name, 'Null')";
+        break;
+      case 'project':
+        $what_to_concat .= ", ' - ', coalesce(p.name, 'Null')";
+        break;
+    }
+    switch ($group_by2) {
+      case 'date':
+        $what_to_concat .= ", ' - ', ei.date";
+        break;
+      case 'user':
+        $what_to_concat .= ", ' - ', u.name";
+        break;
+      case 'client':
+        $what_to_concat .= ", ' - ', coalesce(c.name, 'Null')";
+        break;
+      case 'project':
+        $what_to_concat .= ", ' - ', coalesce(p.name, 'Null')";
+        break;
+    }
+    switch ($group_by3) {
+      case 'date':
+        $what_to_concat .= ", ' - ', ei.date";
+        break;
+      case 'user':
+        $what_to_concat .= ", ' - ', u.name";
+        break;
+      case 'client':
+        $what_to_concat .= ", ' - ', coalesce(c.name, 'Null')";
+        break;
+      case 'project':
+        $what_to_concat .= ", ' - ', coalesce(p.name, 'Null')";
+        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;
+  }
+
+  // makeJoinPart builds a left join part for getSubtotals query (for time items).
+  static function makeJoinPart($options) {
+    global $custom_fields; // TODO: is it safe to assume the object is there when needed?
+
+    $group_by_fields = ttReportHelper::makeGroupByFieldsPart($options); // TODO: refactor this, perhaps?
+    if (strpos($group_by_fields, 'user') !== false) {
+      // Grouping by user, add a join on tt_users table.
+      $join .= ' left join tt_users u on (l.user_id = u.id)';
+    }
+    if (strpos($group_by_fields, 'client') !== false) {
+      // Grouping by client, add a join on tt_clients table.
+      $join .= ' left join tt_clients c on (l.client_id = c.id)';
+    }
+    if (strpos($group_by_fields, 'project') !== false) {
+      // Grouping by project, add a join on tt_projects table.
+      $join .= ' left join tt_projects p on (l.project_id = p.id)';
+    }
+    if (strpos($group_by_fields, 'task') !== false) {
+      // Grouping by task, add a join on tt_tasks table.
+      $join .= ' left join tt_tasks t on (l.task_id = t.id)';
+    }
+    if (strpos($group_by_fields, 'cf_1') !== false) {
+      // Grouping by custom field 1, add a join for it.
+      // $custom_fields = new CustomFields($user->group_id);
+      if ($custom_fields->fields[0]['type'] == CustomFields::TYPE_TEXT)
+        $join .= ' left join tt_custom_field_log cfl on (l.id = cfl.log_id and cfl.status = 1) left join tt_custom_field_options cfo on (cfl.value = cfo.id)';
+      elseif ($custom_fields->fields[0]['type'] == CustomFields::TYPE_DROPDOWN)
+        $join .= ' left join tt_custom_field_log cfl on (l.id = cfl.log_id and cfl.status = 1) left join tt_custom_field_options cfo on (cfl.option_id = cfo.id)';
+    }
+    return $join;
+  }
+
+  // makeJoinExpensesPart builds a left join part for getSubtotals query for expense items.
+  static function makeJoinExpensesPart($options) {
+    $group_by_fields = ttReportHelper::makeGroupByFieldsPart($options); // TODO: refactor this, perhaps?
+    if (strpos($group_by_fields, 'user') !== false) {
+      // Grouping by user, add a join on tt_users table.
+      $join .= ' left join tt_users u on (ei.user_id = u.id)';
+    }
+    if (strpos($group_by_fields, 'client') !== false) {
+      // Grouping by client, add a join on tt_clients table.
+      $join .= ' left join tt_clients c on (ei.client_id = c.id)';
+    }
+    if (strpos($group_by_fields, 'project') !== false) {
+      // Grouping by project, add a join on tt_projects table.
+      $join .= ' left join tt_projects p on (ei.project_id = p.id)';
+    }
+    return $join;
+  }
+  
+  // makeGroupByFieldsPart builds a commma-separated list of fields for sql query using group_by1,
+  // group_by2, and group_by3 values passed in $options.
+  static function makeGroupByFieldsPart($options) {
+    $no_grouping = ($options['group_by1'] == null || $options['group_by1'] == 'no_grouping') &&
+      ($options['group_by2'] == null || $options['group_by2'] == 'no_grouping') &&
+      ($options['group_by3'] == null || $options['group_by3'] == 'no_grouping');
+    if ($no_grouping) return null;
+
+    if ($options['group_by1'] != null && $options['group_by1'] != 'no_grouping') {
+      // We have group_by1.
+      $group_by_fields .= ', '.$options['group_by1'];
+    }
+    if ($options['group_by2'] != null && $options['group_by2'] != 'no_grouping') {
+      // We have group_by2.
+      $group_by_fields .= ', '.$options['group_by2'];
+    }
+    if ($options['group_by3'] != null && $options['group_by3'] != 'no_grouping') {
+      // We have group_by3.
+      $group_by_fields .= ', '.$options['group_by3'];
+    }
+    $group_by_fields = ltrim($group_by_fields, ', ');
+    return $group_by_fields;
+  }
+
+  // groupingByUser determines if we are grouping a report by user.
+  static function groupingByUser($options) {
+    if ($options['group_by1'] == 'user' || $options['group_by2'] == 'user' || $options['group_by3'] == 'user') return true;
+
+    return false;
+  }
+
+  // makeGroupByHeader builds a column header for a totals-only report using group_by1,
+  // group_by2, and group_by3 values passed in $options.
+  static function makeGroupByHeader($options) {
+    global $i18n;
+    global $custom_fields;
+
+    $no_grouping = ($options['group_by1'] == null || $options['group_by1'] == 'no_grouping') &&
+      ($options['group_by2'] == null || $options['group_by2'] == 'no_grouping') &&
+      ($options['group_by3'] == null || $options['group_by3'] == 'no_grouping');
+    if ($no_grouping) return null;
+
+    if ($options['group_by1'] != null && $options['group_by1'] != 'no_grouping') {
+      // We have group_by1.
+      $group_by1 = $options['group_by1'];
+      if ('cf_1' == $group_by1)
+        $group_by_header .= ' - '.$custom_fields->fields[0]['label'];
+      else {
+        $key = 'label.'.$group_by1;
+        $group_by_header .= ' - '.$i18n->get($key);
+      }
+    }
+    if ($options['group_by2'] != null && $options['group_by2'] != 'no_grouping') {
+      // We have group_by2.
+      $group_by2 = $options['group_by2'];
+      if ('cf_1' == $group_by2)
+        $group_by_header .= ' - '.$custom_fields->fields[0]['label'];
+      else {
+        $key = 'label.'.$group_by2;
+        $group_by_header .= ' - '.$i18n->get($key);
+      }
+    }
+    if ($options['group_by3'] != null && $options['group_by3'] != 'no_grouping') {
+      // We have group_by3.
+      $group_by3 = $options['group_by3'];
+      if ('cf_1' == $group_by3)
+        $group_by_header .= ' - '.$custom_fields->fields[0]['label'];
+      else {
+        $key = 'label.'.$group_by3;
+        $group_by_header .= ' - '.$i18n->get($key);
+      }
+    }
+    $group_by_header = ltrim($group_by_header, ' -');
+    return $group_by_header;
+  }

 }
 }

diff --git a/WEB-INF/templates/footer.tpl b/WEB-INF/templates/footer.tpl
index d261f0815939650518b751586ef20efd366692b6..7b0a56e40d006bfadf401702f8c7c9e74d57e4e6 100644 (file)
--- a/WEB-INF/templates/footer.tpl
+++ b/WEB-INF/templates/footer.tpl
@@ -12,7 +12,7 @@
       <br>
       <table cellspacing="0" cellpadding="4" width="100%" border="0">
         <tr>
       <br>
       <table cellspacing="0" cellpadding="4" width="100%" border="0">
         <tr>

-          <td align="center">&nbsp;Anuko Time Tracker 1.18.01.4332 | Copyright &copy; <a href="https://www.anuko.com/lp/tt_3.htm" target="_blank">Anuko</a> |
+          <td align="center">&nbsp;Anuko Time Tracker 1.18.02.4333 | Copyright &copy; <a href="https://www.anuko.com/lp/tt_3.htm" target="_blank">Anuko</a> |

             <a href="https://www.anuko.com/lp/tt_4.htm" target="_blank">{$i18n.footer.credits}</a> |
             <a href="https://www.anuko.com/lp/tt_5.htm" target="_blank">{$i18n.footer.license}</a> |
             <a href="https://www.anuko.com/lp/tt_7.htm" target="_blank">{$i18n.footer.improve}</a>
             <a href="https://www.anuko.com/lp/tt_4.htm" target="_blank">{$i18n.footer.credits}</a> |
             <a href="https://www.anuko.com/lp/tt_5.htm" target="_blank">{$i18n.footer.license}</a> |
             <a href="https://www.anuko.com/lp/tt_7.htm" target="_blank">{$i18n.footer.improve}</a>

diff --git a/report.php b/report.php
index efbaccea8ea74136a23b14d4bbeeaf8612c71c81..5f7e687bf18013b55ef1e83665654472a7a7d98b 100644 (file)
--- a/report.php
+++ b/report.php
@@ -192,12 +192,14 @@ if ($report_items && 'no_grouping' != $group_by1) {
 }
 // Determine group by header.
 if ('no_grouping' != $group_by) {
 }
 // Determine group by header.
 if ('no_grouping' != $group_by) {

+  /*

   if ('cf_1' == $group_by)
     $smarty->assign('group_by_header', $custom_fields->fields[0]['label']);
   else {
     $key = 'label.'.$group_by;
     $smarty->assign('group_by_header', $i18n->get($key));
   if ('cf_1' == $group_by)
     $smarty->assign('group_by_header', $custom_fields->fields[0]['label']);
   else {
     $key = 'label.'.$group_by;
     $smarty->assign('group_by_header', $i18n->get($key));

-  }
+  } */
+  $smarty->assign('group_by_header', ttReportHelper::makeGroupByHeader($options));

 }
 // Assign variables that are used to alternate color of rows for different dates.
 $smarty->assign('prev_date', '');
 }
 // Assign variables that are used to alternate color of rows for different dates.
 $smarty->assign('prev_date', '');

diff --git a/tofile.php b/tofile.php
index 42b7343385fc8b50a4162ce6091450d1c6c27400..d996098b71da7dba01d6bfb7d3d9d65fd5e1977c 100644 (file)
--- a/tofile.php
+++ b/tofile.php
@@ -164,14 +164,7 @@ if ('csv' == $type) {
   $group_by1 = $bean->getAttribute('group_by1');
   if ($totals_only) {
     // Totals only report.
   $group_by1 = $bean->getAttribute('group_by1');
   if ($totals_only) {
     // Totals only report.

-
-    // Determine group_by header.
-    if ('cf_1' == $group_by1)
-      $group_by_header = $custom_fields->fields[0]['label'];
-    else {
-      $key = 'label.'.$group_by1;
-      $group_by_header = $i18n->get($key);
-    }
+    $group_by_header = ttReportHelper::makeGroupByHeader($options);

 
     // Print headers.
     print '"'.$group_by_header.'"';
 
     // Print headers.
     print '"'.$group_by_header.'"';

diff --git a/topdf.php b/topdf.php
index 7d3fa0096ea36bf3117ec72e09eae0d9c356324f..0040c7be926bb504c9d9a1d9393f9e14eacb55a3 100644 (file)
--- a/topdf.php
+++ b/topdf.php
@@ -62,17 +62,6 @@ $bean = new ActionForm('reportBean', new Form('reportForm'), $request);
 // is grouped by either date, user, client, project, task or cf_1 and user only needs to see subtotals by group.
 $totals_only = ($bean->getAttribute('chtotalsonly') == '1');
 
 // is grouped by either date, user, client, project, task or cf_1 and user only needs to see subtotals by group.
 $totals_only = ($bean->getAttribute('chtotalsonly') == '1');
 

-// Determine group by header.
-$group_by1 = $bean->getAttribute('group_by1');
-if ('no_grouping' != $group_by1) {
-  if ('cf_1' == $group_by1)
-    $group_by_header = $custom_fields->fields[0]['label'];
-  else {
-    $key = 'label.'.$group_by1;
-    $group_by_header = $i18n->get($key);
-  }
-}
-

 // Obtain items for report.
 $options = ttReportHelper::getReportOptions($bean);
 if (!$totals_only)
 // Obtain items for report.
 $options = ttReportHelper::getReportOptions($bean);
 if (!$totals_only)


@@ -104,6 +93,7 @@ $html .= '<table border="1" cellpadding="3" cellspacing="0" width="100%">';
 
 if ($totals_only) {
   // We are building a "totals only" report with only subtotals and total.
 
 if ($totals_only) {
   // We are building a "totals only" report with only subtotals and total.

+  $group_by_header = ttReportHelper::makeGroupByHeader($options);

   $colspan = 1; // Column span for an empty row.
   // Table header.
   $html .= '<thead>';
   $colspan = 1; // Column span for an empty row.
   // Table header.
   $html .= '<thead>';