From 6d706063e2f1c0555844c381ee98bd5a4a457d4e Mon Sep 17 00:00:00 2001 From: Nik Okuntseff Date: Thu, 1 Nov 2018 18:13:07 +0000 Subject: [PATCH] Added an index on tt_custom_field_log for faster reporting, a few fixes. --- WEB-INF/lib/ttReportHelper.class.php | 82 +++++++++++++++++++++++----- WEB-INF/templates/footer.tpl | 2 +- WEB-INF/templates/report.tpl | 20 +++---- dbinstall.php | 10 ++-- mysql.sql | 4 +- report.php | 5 ++ 6 files changed, 92 insertions(+), 31 deletions(-) diff --git a/WEB-INF/lib/ttReportHelper.class.php b/WEB-INF/lib/ttReportHelper.class.php index 60cac079..85b2c8e9 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,18 +449,18 @@ 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"; +//die($sql_for_expenses); // Create a combined query. $combined = "select group_field, 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"; $sql = $combined; } - +//die($sql); // Execute query. $res = $mdb2->query($sql); if (is_a($res, 'PEAR_Error')) die($res->getMessage()); @@ -475,9 +472,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 +1193,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 +1210,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 +1235,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 +1260,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 +1298,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 +1325,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 +1352,32 @@ 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"; } // makeJoinPart builds a left join part for getSubtotals query (for time items). diff --git a/WEB-INF/templates/footer.tpl b/WEB-INF/templates/footer.tpl index c92fd1a9..3cf6d4ae 100644 --- a/WEB-INF/templates/footer.tpl +++ b/WEB-INF/templates/footer.tpl @@ -12,7 +12,7 @@
- {/if}{/if} - {if $bean->getAttribute('chclient')}{/if}{/if} - {if $bean->getAttribute('chproject')}{/if}{/if} - {if $bean->getAttribute('chtask')}{/if}{/if} - {if $bean->getAttribute('chcf_1')}{/if}{/if} + {if $user->can('view_reports') || $user->can('view_all_reports') || $user->isClient()}{/if} + {if $bean->getAttribute('chclient')}{/if} + {if $bean->getAttribute('chproject')}{/if} + {if $bean->getAttribute('chtask')}{/if} + {if $bean->getAttribute('chcf_1')}{/if} {if $bean->getAttribute('chstart')}{/if} {if $bean->getAttribute('chfinish')}{/if} {if $bean->getAttribute('chduration')}{/if} @@ -114,11 +114,11 @@ {if $print_subtotals} {/if}{/if} - {if $bean->getAttribute('chclient')}{/if}{/if} - {if $bean->getAttribute('chproject')}{/if}{/if} - {if $bean->getAttribute('chtask')}{/if}{/if} - {if $bean->getAttribute('chcf_1')}{/if}{/if} + {if $user->can('view_reports') || $user->can('view_all_reports') || $user->isClient()}{/if} + {if $bean->getAttribute('chclient')}{/if} + {if $bean->getAttribute('chproject')}{/if} + {if $bean->getAttribute('chtask')}{/if} + {if $bean->getAttribute('chcf_1')}{/if} {if $bean->getAttribute('chstart')}{/if} {if $bean->getAttribute('chfinish')}{/if} {if $bean->getAttribute('chduration')}{/if} diff --git a/dbinstall.php b/dbinstall.php index 097d2abe..f6fd0c1b 100644 --- a/dbinstall.php +++ b/dbinstall.php @@ -956,11 +956,13 @@ if ($_POST) { print "Updated $tt_expense_items_updated tt_expense_items records...
\n"; } - if ($_POST["convert11797to11800"]) { + if ($_POST["convert11797to11805"]) { setChange("ALTER TABLE `tt_fav_reports` CHANGE `group_by` `group_by1` varchar(20) default NULL"); setChange("ALTER TABLE `tt_fav_reports` ADD `group_by2` varchar(20) default NULL AFTER `group_by1`"); setChange("ALTER TABLE `tt_fav_reports` ADD `group_by3` varchar(20) default NULL AFTER `group_by2`"); setChange("UPDATE `tt_site_config` SET param_value = '1.18.00', modified = now() where param_name = 'version_db' and param_value = '1.17.97'"); + setChange("create index log_idx on tt_custom_field_log(log_id)"); + setChange("UPDATE `tt_site_config` SET param_value = '1.18.05', modified = now() where param_name = 'version_db' and param_value = '1.18.00'"); } if ($_POST["cleanup"]) { @@ -1006,7 +1008,7 @@ if ($_POST) {

DB Install

 Anuko Time Tracker 1.18.04.4340 | Copyright © Anuko | +  Anuko Time Tracker 1.18.05.4341 | Copyright © Anuko | {$i18n.footer.credits} | {$i18n.footer.license} | {$i18n.footer.improve} diff --git a/WEB-INF/templates/report.tpl b/WEB-INF/templates/report.tpl index 55438428..0c98951f 100644 --- a/WEB-INF/templates/report.tpl +++ b/WEB-INF/templates/report.tpl @@ -62,11 +62,11 @@ {if $cur_grouped_by != $prev_grouped_by && !$first_pass}
{$i18n.label.subtotal} - {if $user->can('view_reports') || $user->can('view_all_reports') || $user->isClient()}{if $group_by == 'user'}{$subtotals[$prev_grouped_by]['name']|escape}{if $group_by == 'client'}{$subtotals[$prev_grouped_by]['name']|escape}{if $group_by == 'project'}{$subtotals[$prev_grouped_by]['name']|escape}{if $group_by == 'task'}{$subtotals[$prev_grouped_by]['name']|escape}{if $group_by == 'cf_1'}{$subtotals[$prev_grouped_by]['name']|escape}{$subtotals[$prev_grouped_by]['user']|escape}{$subtotals[$prev_grouped_by]['client']|escape}{$subtotals[$prev_grouped_by]['project']|escape}{$subtotals[$prev_grouped_by]['task']|escape}{$subtotals[$prev_grouped_by]['cf_1']|escape}{$subtotals[$prev_grouped_by]['time']}
{$i18n.label.subtotal} - {if $user->can('view_reports') || $user->can('view_all_reports') || $user->isClient()}{if $group_by == 'user'}{$subtotals[$cur_grouped_by]['name']|escape}{if $group_by == 'client'}{$subtotals[$cur_grouped_by]['name']|escape}{if $group_by == 'project'}{$subtotals[$cur_grouped_by]['name']|escape}{if $group_by == 'task'}{$subtotals[$cur_grouped_by]['name']|escape}{if $group_by == 'cf_1'}{$subtotals[$cur_grouped_by]['name']|escape}{$subtotals[$cur_grouped_by]['user']|escape}{$subtotals[$cur_grouped_by]['client']|escape}{$subtotals[$cur_grouped_by]['project']|escape}{$subtotals[$cur_grouped_by]['task']|escape}{$subtotals[$cur_grouped_by]['cf_1']|escape}{$subtotals[$cur_grouped_by]['time']}
-
Create database structure (v1.18.00) + Create database structure (v1.18.05)
(applies only to new installations, do not execute when updating)
@@ -1051,8 +1053,8 @@ if ($_POST) { - Update database structure (v1.17.97 to v1.18.00) - + Update database structure (v1.17.97 to v1.18.05) + diff --git a/mysql.sql b/mysql.sql index 07a2323a..955f1b29 100644 --- a/mysql.sql +++ b/mysql.sql @@ -382,6 +382,8 @@ CREATE TABLE `tt_custom_field_log` ( PRIMARY KEY (`id`) ); +create index log_idx on tt_custom_field_log(log_id); + # # Structure for table tt_expense_items. @@ -456,4 +458,4 @@ CREATE TABLE `tt_site_config` ( PRIMARY KEY (`param_name`) ); -INSERT INTO `tt_site_config` (`param_name`, `param_value`, `created`) VALUES ('version_db', '1.18.00', now()); # TODO: change when structure changes. +INSERT INTO `tt_site_config` (`param_name`, `param_value`, `created`) VALUES ('version_db', '1.18.05', now()); # TODO: change when structure changes. diff --git a/report.php b/report.php index f6abcc4c..28e086c4 100644 --- a/report.php +++ b/report.php @@ -173,6 +173,8 @@ if ($request->isPost()) { $group_by = $bean->getAttribute('group_by1'); $options = ttReportHelper::getReportOptions($bean); + +$group_by_tag = ttReportHelper::makeGroupByXmlTag($options); $report_items = ttReportHelper::getItems($options); // Store record ids in session in case user wants to act on records such as marking them all paid. if ($request->isGet() && $user->isPluginEnabled('ps')) @@ -201,6 +203,9 @@ if ('no_grouping' != $group_by) { } */ $smarty->assign('group_by_header', ttReportHelper::makeGroupByHeader($options)); } + +$smarty->assign('group_by', $group_by_tag); + // Assign variables that are used to alternate color of rows for different dates. $smarty->assign('prev_date', ''); $smarty->assign('cur_date', ''); -- 2.20.1