Added an index on tt_custom_field_log for faster reporting, a few fixes.

diff --git a/WEB-INF/lib/ttReportHelper.class.php b/WEB-INF/lib/ttReportHelper.class.php
index 60cac07..85b2c8e 100644 (file)
--- 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 c92fd1a..3cf6d4a 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>
-          <td align="center">&nbsp;Anuko Time Tracker 1.18.04.4340 | 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.05.4341 | 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>

diff --git a/WEB-INF/templates/report.tpl b/WEB-INF/templates/report.tpl
index 5543842..0c98951 100644 (file)
--- 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}
       <tr class="rowReportSubtotal">
         <td class="cellLeftAlignedSubtotal">{$i18n.label.subtotal}
-        {if $user->can('view_reports') || $user->can('view_all_reports') || $user->isClient()}<td class="cellLeftAlignedSubtotal">{if $group_by == 'user'}{$subtotals[$prev_grouped_by]['name']|escape}</td>{/if}{/if}
-        {if $bean->getAttribute('chclient')}<td class="cellLeftAlignedSubtotal">{if $group_by == 'client'}{$subtotals[$prev_grouped_by]['name']|escape}</td>{/if}{/if}
-        {if $bean->getAttribute('chproject')}<td class="cellLeftAlignedSubtotal">{if $group_by == 'project'}{$subtotals[$prev_grouped_by]['name']|escape}</td>{/if}{/if}
-        {if $bean->getAttribute('chtask')}<td class="cellLeftAlignedSubtotal">{if $group_by == 'task'}{$subtotals[$prev_grouped_by]['name']|escape}</td>{/if}{/if}
-        {if $bean->getAttribute('chcf_1')}<td class="cellLeftAlignedSubtotal">{if $group_by == 'cf_1'}{$subtotals[$prev_grouped_by]['name']|escape}</td>{/if}{/if}
+        {if $user->can('view_reports') || $user->can('view_all_reports') || $user->isClient()}<td class="cellLeftAlignedSubtotal">{$subtotals[$prev_grouped_by]['user']|escape}</td>{/if}
+        {if $bean->getAttribute('chclient')}<td class="cellLeftAlignedSubtotal">{$subtotals[$prev_grouped_by]['client']|escape}</td>{/if}
+        {if $bean->getAttribute('chproject')}<td class="cellLeftAlignedSubtotal">{$subtotals[$prev_grouped_by]['project']|escape}</td>{/if}
+        {if $bean->getAttribute('chtask')}<td class="cellLeftAlignedSubtotal">{$subtotals[$prev_grouped_by]['task']|escape}</td>{/if}
+        {if $bean->getAttribute('chcf_1')}<td class="cellLeftAlignedSubtotal">{$subtotals[$prev_grouped_by]['cf_1']|escape}</td>{/if}
         {if $bean->getAttribute('chstart')}<td></td>{/if}
         {if $bean->getAttribute('chfinish')}<td></td>{/if}
         {if $bean->getAttribute('chduration')}<td class="cellRightAlignedSubtotal">{$subtotals[$prev_grouped_by]['time']}</td>{/if}
@@ -114,11 +114,11 @@
   {if $print_subtotals}
       <tr class="rowReportSubtotal">
         <td class="cellLeftAlignedSubtotal">{$i18n.label.subtotal}
-    {if $user->can('view_reports') || $user->can('view_all_reports') || $user->isClient()}<td class="cellLeftAlignedSubtotal">{if $group_by == 'user'}{$subtotals[$cur_grouped_by]['name']|escape}</td>{/if}{/if}
-    {if $bean->getAttribute('chclient')}<td class="cellLeftAlignedSubtotal">{if $group_by == 'client'}{$subtotals[$cur_grouped_by]['name']|escape}</td>{/if}{/if}
-    {if $bean->getAttribute('chproject')}<td class="cellLeftAlignedSubtotal">{if $group_by == 'project'}{$subtotals[$cur_grouped_by]['name']|escape}</td>{/if}{/if}
-    {if $bean->getAttribute('chtask')}<td class="cellLeftAlignedSubtotal">{if $group_by == 'task'}{$subtotals[$cur_grouped_by]['name']|escape}</td>{/if}{/if}
-    {if $bean->getAttribute('chcf_1')}<td class="cellLeftAlignedSubtotal">{if $group_by == 'cf_1'}{$subtotals[$cur_grouped_by]['name']|escape}</td>{/if}{/if}
+    {if $user->can('view_reports') || $user->can('view_all_reports') || $user->isClient()}<td class="cellLeftAlignedSubtotal">{$subtotals[$cur_grouped_by]['user']|escape}</td>{/if}
+    {if $bean->getAttribute('chclient')}<td class="cellLeftAlignedSubtotal">{$subtotals[$cur_grouped_by]['client']|escape}</td>{/if}
+    {if $bean->getAttribute('chproject')}<td class="cellLeftAlignedSubtotal">{$subtotals[$cur_grouped_by]['project']|escape}</td>{/if}
+    {if $bean->getAttribute('chtask')}<td class="cellLeftAlignedSubtotal">{$subtotals[$cur_grouped_by]['task']|escape}</td>{/if}
+    {if $bean->getAttribute('chcf_1')}<td class="cellLeftAlignedSubtotal">{$subtotals[$cur_grouped_by]['cf_1']|escape}</td>{/if}
     {if $bean->getAttribute('chstart')}<td></td>{/if}
     {if $bean->getAttribute('chfinish')}<td></td>{/if}
     {if $bean->getAttribute('chduration')}<td class="cellRightAlignedSubtotal">{$subtotals[$cur_grouped_by]['time']}</td>{/if}

diff --git a/dbinstall.php b/dbinstall.php
index 097d2ab..f6fd0c1 100644 (file)
--- a/dbinstall.php
+++ b/dbinstall.php
@@ -956,11 +956,13 @@ if ($_POST) {
     print "Updated $tt_expense_items_updated tt_expense_items records...<br>\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) {
 <h2>DB Install</h2>
 <table width="80%" border="1" cellpadding="10" cellspacing="0">
   <tr>
-    <td width="80%"><b>Create database structure (v1.18.00)</b>
+    <td width="80%"><b>Create database structure (v1.18.05)</b>
     <br>(applies only to new installations, do not execute when updating)</br></td><td><input type="submit" name="crstructure" value="Create"></td>
   </tr>
 </table>
@@ -1051,8 +1053,8 @@ if ($_POST) {
   </tr>
   </tr>
   <tr valign="top">
-    <td>Update database structure (v1.17.97 to v1.18.00)</td>
-    <td><input type="submit" name="convert11797to11800" value="Update"></td>
+    <td>Update database structure (v1.17.97 to v1.18.05)</td>
+    <td><input type="submit" name="convert11797to11805" value="Update"></td>
   </tr>
 </table>
 

diff --git a/mysql.sql b/mysql.sql
index 07a2323..955f1b2 100644 (file)
--- 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 f6abcc4..28e086c 100644 (file)
--- 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', '');