From a97edb0f5cfcf62419cd98eb3cc0fc415eaab319 Mon Sep 17 00:00:00 2001 From: Nik Okuntseff Date: Sat, 15 Dec 2018 15:28:07 +0000 Subject: [PATCH] Simplified conditional logic in ttReportHelper::getSubtotals. --- WEB-INF/lib/ttReportHelper.class.php | 76 +++++++++++++++------------- WEB-INF/templates/footer.tpl | 2 +- 2 files changed, 41 insertions(+), 37 deletions(-) diff --git a/WEB-INF/lib/ttReportHelper.class.php b/WEB-INF/lib/ttReportHelper.class.php index 068ae9db..2265f0da 100644 --- a/WEB-INF/lib/ttReportHelper.class.php +++ b/WEB-INF/lib/ttReportHelper.class.php @@ -410,44 +410,14 @@ class ttReportHelper { $mdb2 = getConnection(); $concat_part = ttReportHelper::makeConcatPart($options); + $work_unit_part = ttReportHelper::makeWorkUnitPart($options); $join_part = ttReportHelper::makeJoinPart($options); - - // TODO: Consider moving this block out into a separate function. - $workUnits = $options['show_work_units']; - if ($workUnits) { - $unitTotalsOnly = $user->getConfigOption('unit_totals_only'); - $firstUnitThreshold = $user->getConfigInt('1st_unit_threshold'); - $minutesInUnit = $user->getConfigInt('minutes_in_unit', 15); - if ($unitTotalsOnly) - $work_unit_part = ", if (sum(l.billable * time_to_sec(l.duration)/60) < $firstUnitThreshold, 0, ceil(sum(l.billable * time_to_sec(l.duration)/60/$minutesInUnit))) as units"; - else - $work_unit_part = ", sum(if(l.billable = 0 or time_to_sec(l.duration)/60 < $firstUnitThreshold, 0, ceil(time_to_sec(l.duration)/60/$minutesInUnit))) as units"; - } - // End of TODO. - + $cost_part = ttReportHelper::makeCostPart($options); $where = ttReportHelper::getWhere($options); $group_by_part = ttReportHelper::makeGroupByPart($options); - if ($options['show_cost']) { - if (MODE_TIME == $user->getTrackingMode()) { - if (!ttReportHelper::groupingBy('user', $options)) - $left_join = 'left join tt_users u on (l.user_id = u.id)'; - $sql = "select $concat_part, sum(time_to_sec(l.duration)) as time".$work_unit_part; - $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 - $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. - $sql = "select $concat_part, sum(time_to_sec(l.duration)) as time".$work_unit_part; - $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 - $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 $concat_part, sum(time_to_sec(l.duration)) as time".$work_unit_part; - $sql .= ", null as expenses from tt_log l - $join_part $where $group_by_part"; - } + + $parts = "$concat_part, sum(time_to_sec(l.duration)) as time, null as expenses".$work_unit_part.$cost_part; + $sql = "select $parts from tt_log l $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. @@ -1465,7 +1435,8 @@ class ttReportHelper { static function makeJoinPart($options) { global $user; - if (ttReportHelper::groupingBy('user', $options)) { + $trackingMode = $user->getTrackingMode(); + if (ttReportHelper::groupingBy('user', $options) || MODE_TIME == $trackingMode) { $join .= ' left join tt_users u on (l.user_id = u.id)'; } if (ttReportHelper::groupingBy('client', $options)) { @@ -1484,9 +1455,42 @@ class ttReportHelper { 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)'; } + if ($options['show_cost'] && $trackingMode != MODE_TIME) { + $join .= ' left join tt_user_project_binds upb on (l.user_id = upb.user_id and l.project_id = upb.project_id)'; + } return $join; } + // makeWorkUnitPart builds an sql part for work units for time items. + static function makeWorkUnitPart($options) { + global $user; + + $workUnits = $options['show_work_units']; + if ($workUnits) { + $unitTotalsOnly = $user->getConfigOption('unit_totals_only'); + $firstUnitThreshold = $user->getConfigInt('1st_unit_threshold'); + $minutesInUnit = $user->getConfigInt('minutes_in_unit', 15); + if ($unitTotalsOnly) + $work_unit_part = ", if (sum(l.billable * time_to_sec(l.duration)/60) < $firstUnitThreshold, 0, ceil(sum(l.billable * time_to_sec(l.duration)/60/$minutesInUnit))) as units"; + else + $work_unit_part = ", sum(if(l.billable = 0 or time_to_sec(l.duration)/60 < $firstUnitThreshold, 0, ceil(time_to_sec(l.duration)/60/$minutesInUnit))) as units"; + } + return $work_unit_part; + } + + // makeCostPart builds a cost part for time items. + static function makeCostPart($options) { + global $user; + + if ($options['show_cost']) { + if (MODE_TIME == $user->getTrackingMode()) + $cost_part = ", sum(cast(l.billable * coalesce(u.rate, 0) * time_to_sec(l.duration)/3600 as decimal(10, 2))) as cost"; + else + $cost_part .= ", sum(cast(l.billable * coalesce(upb.rate, 0) * time_to_sec(l.duration)/3600 as decimal(10,2))) as cost"; + } + return $cost_part; + } + // makeJoinExpensesPart builds a left join part for getSubtotals query for expense items. static function makeJoinExpensesPart($options) { if (ttReportHelper::groupingBy('user', $options)) { diff --git a/WEB-INF/templates/footer.tpl b/WEB-INF/templates/footer.tpl index ae8c6333..84526cfb 100644 --- a/WEB-INF/templates/footer.tpl +++ b/WEB-INF/templates/footer.tpl @@ -12,7 +12,7 @@
-
 Anuko Time Tracker 1.18.31.4655 | Copyright © Anuko | +  Anuko Time Tracker 1.18.31.4656 | Copyright © Anuko | {$i18n.footer.credits} | {$i18n.footer.license} | {$i18n.footer.improve} -- 2.20.1