From 03c03896828d818303c12dfe2e368f5bd9e7dbae Mon Sep 17 00:00:00 2001 From: Nik Okuntseff Date: Thu, 1 Nov 2018 19:15:48 +0000 Subject: [PATCH] Improved output of grouped subtotals on reports by including grouped values. --- WEB-INF/lib/ttReportHelper.class.php | 78 ++++++++++++++++++++++++++-- WEB-INF/templates/footer.tpl | 2 +- 2 files changed, 75 insertions(+), 5 deletions(-) diff --git a/WEB-INF/lib/ttReportHelper.class.php b/WEB-INF/lib/ttReportHelper.class.php index 85b2c8e9..11f8841f 100644 --- a/WEB-INF/lib/ttReportHelper.class.php +++ b/WEB-INF/lib/ttReportHelper.class.php @@ -453,14 +453,15 @@ class ttReportHelper { $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_expenses_part"; -//die($sql_for_expenses); + // 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; } -//die($sql); + // Execute query. $res = $mdb2->query($sql); if (is_a($res, 'PEAR_Error')) die($res->getMessage()); @@ -1380,6 +1381,75 @@ class ttReportHelper { 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). static function makeJoinPart($options) { global $custom_fields; // TODO: is it safe to assume the object is there when needed? diff --git a/WEB-INF/templates/footer.tpl b/WEB-INF/templates/footer.tpl index 3cf6d4ae..af9a278a 100644 --- a/WEB-INF/templates/footer.tpl +++ b/WEB-INF/templates/footer.tpl @@ -12,7 +12,7 @@
-
 Anuko Time Tracker 1.18.05.4341 | Copyright © Anuko | +  Anuko Time Tracker 1.18.05.4342 | Copyright © Anuko | {$i18n.footer.credits} | {$i18n.footer.license} | {$i18n.footer.improve} -- 2.20.1