X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=WEB-INF%2Flib%2FttReportHelper.class.php;h=2fdaea4f1665910eb58f889f15fea7f1238ce5d5;hb=8e1e5862042f445b3fc7dbe9aed8fcef14af4239;hp=a70442241a4a9c13ff17398f5c121f1b36b7e376;hpb=abe3a7f279615f45b4bf0072a1b041ff651eeebc;p=timetracker.git

diff --git a/WEB-INF/lib/ttReportHelper.class.php b/WEB-INF/lib/ttReportHelper.class.php
index a7044224..2fdaea4f 100644
--- a/WEB-INF/lib/ttReportHelper.class.php
+++ b/WEB-INF/lib/ttReportHelper.class.php
@@ -428,10 +428,10 @@ class ttReportHelper {
   static function getFavItems($report) {
     global $user;
     $mdb2 = getConnection();
-    
+
     $group_by_option = $report['group_by'];
     $convertTo12Hour = ('%I:%M %p' == $user->time_format) && ($report['show_start'] || $report['show_end']);
-    
+
     // Prepare a query for time items in tt_log table.
     $fields = array(); // An array of fields for database query.
     array_push($fields, 'l.id as id');
@@ -495,9 +495,9 @@ class ttReportHelper {
     if ($user->canManageTeam() || $user->isClient() || in_array('ex', explode(',', $user->plugins)))
        $left_joins .= " left join tt_users u on (u.id = l.user_id)";
     if ($report['show_project'] || 'project' == $group_by_option)
-      $left_joins .= " left join tt_projects p on (p.id = l.project_id)";	
+      $left_joins .= " left join tt_projects p on (p.id = l.project_id)";
     if ($report['show_task'] || 'task' == $group_by_option)
-      $left_joins .= " left join tt_tasks t on (t.id = l.task_id)";		
+      $left_joins .= " left join tt_tasks t on (t.id = l.task_id)";
     if ($include_cf_1) {
       if ($cf_1_type == CustomFields::TYPE_TEXT)
         $left_joins .= " left join tt_custom_field_log cfl on (l.id = cfl.log_id and cfl.status = 1)";
@@ -508,17 +508,17 @@ class ttReportHelper {
     }
     if ($includeCost && MODE_TIME != $user->tracking_mode)
       $left_joins .= " left join tt_user_project_binds upb on (l.user_id = upb.user_id and l.project_id = upb.project_id)";
-    
+
     $where = ttReportHelper::getFavWhere($report);
-    
-    // Construct sql query for tt_log items.    
+
+    // Construct sql query for tt_log items.
     $sql = "select ".join(', ', $fields)." from tt_log l $left_joins $where";
     // If we don't have expense items (such as when the Expenses plugin is desabled), the above is all sql we need,
     // with an exception of sorting part, that is added in the end.
 
     // However, when we have expenses, we need to do a union with a separate query for expense items from tt_expense_items table.
     if ($report['show_cost'] && in_array('ex', explode(',', $user->plugins))) { // if ex(penses) plugin is enabled
-    
+
       $fields = array(); // An array of fields for database query.
       array_push($fields, 'ei.id');
       array_push($fields, '2 as type'); // Type 2 is for tt_expense_items entries.
@@ -551,7 +551,7 @@ class ttReportHelper {
       // Add invoice name if it is selected.
       if (($user->canManageTeam() || $user->isClient()) && $report['show_invoice'])
         array_push($fields, 'i.name as invoice');
-        
+
       // Prepare sql query part for left joins.
       $left_joins = null;
       if ($user->canManageTeam() || $user->isClient())
@@ -559,19 +559,19 @@ class ttReportHelper {
       if ($report['show_client'] || 'client' == $group_by_option)
         $left_joins .= " left join tt_clients c on (c.id = ei.client_id)";
       if ($report['show_project'] || 'project' == $group_by_option)
-        $left_joins .= " left join tt_projects p on (p.id = ei.project_id)";	      
+        $left_joins .= " left join tt_projects p on (p.id = ei.project_id)";
       if (($user->canManageTeam() || $user->isClient()) && $report['show_invoice'])
         $left_joins .= " left join tt_invoices i on (i.id = ei.invoice_id and i.status = 1)";
 
       $where = ttReportHelper::getFavExpenseWhere($report);
-      
+
       // Construct sql query for expense items.
       $sql_for_expense_items = "select ".join(', ', $fields)." from tt_expense_items ei $left_joins $where";
-      
+
       // Construct a union.
       $sql = "($sql) union all ($sql_for_expense_items)";
     }
-    
+
     // Determine sort part.
     $sort_part = ' order by ';
     if ($group_by_option == null || 'no_grouping' == $group_by_option || 'date' == $group_by_option) // TODO: fix DB for NULL values in group_by field.
@@ -583,68 +583,67 @@ class ttReportHelper {
     if ($report['show_start'])
       $sort_part .= ', unformatted_start';
     $sort_part .= ', id';
-    
+
     $sql .= $sort_part;
     // By now we are ready with sql.
 
     // Obtain items for report.
     $res = $mdb2->query($sql);
-    if (!is_a($res, 'PEAR_Error')) {
-      while ($val = $res->fetchRow()) {
-          if ($convertTo12Hour) {
-            if($val['start'] != '')
-              $val['start'] = ttTimeHelper::to12HourFormat($val['start']);
-            if($val['finish'] != '')
-              $val['finish'] = ttTimeHelper::to12HourFormat($val['finish']);
-          }
-  	      if (isset($val['cost'])) {
-            if ('.' != $user->decimal_mark)
-  	          $val['cost'] = str_replace('.', $user->decimal_mark, $val['cost']);
-  	      }
-          if (isset($val['expense'])) {
-            if ('.' != $user->decimal_mark)
-  	          $val['expense'] = str_replace('.', $user->decimal_mark, $val['expense']);
-  	      }
-  	      if ('no_grouping' != $group_by_option) {
-            $val['grouped_by'] = $val[$group_by_option];
-            if ('date' == $group_by_option) {
-              // This is needed to get the date in user date format.
-              $o_date = new DateAndTime(DB_DATEFORMAT, $val['grouped_by']);
-              $val['grouped_by'] = $o_date->toString($user->date_format);
-              unset($o_date);
-            }
-  	      }
-            
+    if (is_a($res, 'PEAR_Error')) die($res->getMessage());
+
+    while ($val = $res->fetchRow()) {
+      if ($convertTo12Hour) {
+        if($val['start'] != '')
+          $val['start'] = ttTimeHelper::to12HourFormat($val['start']);
+        if($val['finish'] != '')
+          $val['finish'] = ttTimeHelper::to12HourFormat($val['finish']);
+      }
+      if (isset($val['cost'])) {
+        if ('.' != $user->decimal_mark)
+          $val['cost'] = str_replace('.', $user->decimal_mark, $val['cost']);
+      }
+      if (isset($val['expense'])) {
+        if ('.' != $user->decimal_mark)
+          $val['expense'] = str_replace('.', $user->decimal_mark, $val['expense']);
+      }
+      if ('no_grouping' != $group_by_option) {
+        $val['grouped_by'] = $val[$group_by_option];
+        if ('date' == $group_by_option) {
           // This is needed to get the date in user date format.
-          $o_date = new DateAndTime(DB_DATEFORMAT, $val['date']);
-          $val['date'] = $o_date->toString($user->date_format);
+          $o_date = new DateAndTime(DB_DATEFORMAT, $val['grouped_by']);
+          $val['grouped_by'] = $o_date->toString($user->date_format);
           unset($o_date);
-
-          $row = $val;
-          $report_items[] = $row;
         }
-      } else
-  	    die($res->getMessage());
+      }
+
+      // This is needed to get the date in user date format.
+      $o_date = new DateAndTime(DB_DATEFORMAT, $val['date']);
+      $val['date'] = $o_date->toString($user->date_format);
+      unset($o_date);
+
+      $row = $val;
+      $report_items[] = $row;
+    }
 
     return $report_items;
   }
-  
+
   // getSubtotals calculates report items subtotals when a report is grouped by.
   // Without expenses, it's a simple select with group by.
   // With expenses, it becomes a select with group by from a combined set of records obtained with "union all".
   static function getSubtotals($bean) {
-  	global $user;
-  	
+    global $user;
+
     $group_by_option = $bean->getAttribute('group_by');
     if ('no_grouping' == $group_by_option) return null;
-    
+
     $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':
+      case 'date':
         $group_field = 'l.date';
         $group_join = '';
         break;
@@ -657,54 +656,54 @@ class ttReportHelper {
         $group_join = 'left join tt_clients c on (l.client_id = c.id) ';
         break;
       case 'project':
-  	    $group_field = 'p.name';
+        $group_field = 'p.name';
         $group_join = 'left join tt_projects p on (l.project_id = p.id) ';
-  	    break;
+        break;
       case 'task':
-  	    $group_field = 't.name';
+        $group_field = 't.name';
         $group_join = 'left join tt_tasks t on (l.task_id = t.id) ';
-  	    break;
+        break;
       case 'cf_1':
-  	    $group_field = 'cfo.value';
-  	    $custom_fields = new CustomFields($user->team_id);
-  	    if ($custom_fields->fields[0]['type'] == CustomFields::TYPE_TEXT)
+        $group_field = 'cfo.value';
+        $custom_fields = new CustomFields($user->team_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;
+        break;
     }
 
     $where = ttReportHelper::getWhere($bean);
     if ($bean->getAttribute('chcost')) {
       if (MODE_TIME == $user->tracking_mode) {
-      	if ($group_by_option != 'user')
-      	  $left_join = 'left join tt_users u on (l.user_id = u.id)';
+        if ($group_by_option != 'user')
+          $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, 
           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";      	
+          $group_join $left_join $where group by $group_field";
       } 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, 
           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 
+          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";
       }
     } else {
-  	   $sql = "select $group_field as group_field, sum(time_to_sec(l.duration)) as time, null as expenses from tt_log l 
+      $sql = "select $group_field as group_field, sum(time_to_sec(l.duration)) as time, null as expenses from tt_log l
          $group_join $where group by $group_field";
     }
     // 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 ($bean->getAttribute('chcost') && in_array('ex', explode(',', $user->plugins))) { // 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':
+        case 'date':
           $group_field = 'ei.date';
           $group_join = '';
           break;
@@ -717,9 +716,9 @@ class ttReportHelper {
           $group_join = 'left join tt_clients c on (ei.client_id = c.id) ';
           break;
         case 'project':
-  	      $group_field = 'p.name';
+          $group_field = 'p.name';
           $group_join = 'left join tt_projects p on (ei.project_id = p.id) ';
-  	      break;
+          break;
       }
 
       $where = ttReportHelper::getExpenseWhere($bean);
@@ -727,53 +726,52 @@ class ttReportHelper {
         $group_join $where";
       // Add a "group by" clause if we are grouping.
       if ('null' != $group_field) $sql_for_expenses .= " group by $group_field";
-         
+
       // Create a combined query.
       $sql = "select group_field, sum(time) as time, sum(cost) as cost, sum(expenses) as expenses from (($sql) union all ($sql_for_expenses)) t group by group_field";
     }
-    
+
     // Execute query.
     $res = $mdb2->query($sql);
-    if (!is_a($res, 'PEAR_Error')) {
-  	  while ($val = $res->fetchRow()) {
-  	  	if ('date' == $group_by_option) {
-          // This is needed to get the date in user date format.
-          $o_date = new DateAndTime(DB_DATEFORMAT, $val['group_field']);
-          $val['group_field'] = $o_date->toString($user->date_format);
-          unset($o_date);
-  	  	}
-  	  	$time = $val['time'] ? sec_to_time_fmt_hm($val['time']) : null;
-        if ($bean->getAttribute('chcost')) {
-  	      if ('.' != $user->decimal_mark) {
-  	        $val['cost'] = str_replace('.', $user->decimal_mark, $val['cost']);
-  	        $val['expenses'] = str_replace('.', $user->decimal_mark, $val['expenses']);
-  	      }
-      	  $subtotals[$val['group_field']] = array('name'=>$val['group_field'],'time'=>$time,'cost'=>$val['cost'],'expenses'=>$val['expenses']);
-        } else
-          $subtotals[$val['group_field']] = array('name'=>$val['group_field'],'time'=>$time);
+    if (is_a($res, 'PEAR_Error')) die($res->getMessage());
+
+    while ($val = $res->fetchRow()) {
+      if ('date' == $group_by_option) {
+        // This is needed to get the date in user date format.
+        $o_date = new DateAndTime(DB_DATEFORMAT, $val['group_field']);
+        $val['group_field'] = $o_date->toString($user->date_format);
+        unset($o_date);
       }
-    } else
-      die($res->getMessage());
+      $time = $val['time'] ? sec_to_time_fmt_hm($val['time']) : null;
+      if ($bean->getAttribute('chcost')) {
+        if ('.' != $user->decimal_mark) {
+          $val['cost'] = str_replace('.', $user->decimal_mark, $val['cost']);
+          $val['expenses'] = str_replace('.', $user->decimal_mark, $val['expenses']);
+        }
+        $subtotals[$val['group_field']] = array('name'=>$val['group_field'],'time'=>$time,'cost'=>$val['cost'],'expenses'=>$val['expenses']);
+      } else
+        $subtotals[$val['group_field']] = array('name'=>$val['group_field'],'time'=>$time);
+    }
 
     return $subtotals;
   }
-  
+
   // getFavSubtotals calculates report items subtotals when a favorite report is grouped by.
   // Without expenses, it's a simple select with group by.
   // With expenses, it becomes a select with group by from a combined set of records obtained with "union all".
   static function getFavSubtotals($report) {
-  	global $user;
-  	
+    global $user;
+
     $group_by_option = $report['group_by'];
     if ('no_grouping' == $group_by_option) return null;
-    
+
     $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':
+      case 'date':
         $group_field = 'l.date';
         $group_join = '';
         break;
@@ -786,32 +784,32 @@ class ttReportHelper {
         $group_join = 'left join tt_clients c on (l.client_id = c.id) ';
         break;
       case 'project':
-  	    $group_field = 'p.name';
+        $group_field = 'p.name';
         $group_join = 'left join tt_projects p on (l.project_id = p.id) ';
-  	    break;
+        break;
       case 'task':
-  	    $group_field = 't.name';
+        $group_field = 't.name';
         $group_join = 'left join tt_tasks t on (l.task_id = t.id) ';
-  	    break;
+        break;
       case 'cf_1':
-  	    $group_field = 'cfo.value';
-  	    $custom_fields = new CustomFields($user->team_id);
-  	    if ($custom_fields->fields[0]['type'] == CustomFields::TYPE_TEXT)
+        $group_field = 'cfo.value';
+        $custom_fields = new CustomFields($user->team_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;
+        break;
     }
 
     $where = ttReportHelper::getFavWhere($report);
     if ($report['show_cost']) {
       if (MODE_TIME == $user->tracking_mode) {
-      	if ($group_by_option != 'user')
-      	  $left_join = 'left join tt_users u on (l.user_id = u.id)';
+        if ($group_by_option != 'user')
+          $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, 
           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";      	
+          $group_join $left_join $where group by $group_field";
       } 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, 
@@ -821,19 +819,19 @@ class ttReportHelper {
           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";
       }
     } else {
-  	   $sql = "select $group_field as group_field, sum(time_to_sec(l.duration)) as time, null as expenses from tt_log l 
+      $sql = "select $group_field as group_field, sum(time_to_sec(l.duration)) as time, null as expenses from tt_log l 
          $group_join $where group by $group_field";
     }
     // 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 ($report['show_cost'] && in_array('ex', explode(',', $user->plugins))) { // 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':
+        case 'date':
           $group_field = 'ei.date';
           $group_join = '';
           break;
@@ -846,9 +844,9 @@ class ttReportHelper {
           $group_join = 'left join tt_clients c on (ei.client_id = c.id) ';
           break;
         case 'project':
-  	      $group_field = 'p.name';
+          $group_field = 'p.name';
           $group_join = 'left join tt_projects p on (ei.project_id = p.id) ';
-  	      break;
+          break;
       }
 
       $where = ttReportHelper::getFavExpenseWhere($report);
@@ -856,37 +854,36 @@ class ttReportHelper {
         $group_join $where";
       // Add a "group by" clause if we are grouping.
       if ('null' != $group_field) $sql_for_expenses .= " group by $group_field";
-         
+
       // Create a combined query.
       $sql = "select group_field, sum(time) as time, sum(cost) as cost, sum(expenses) as expenses from (($sql) union all ($sql_for_expenses)) t group by group_field";
     }
-    
+
     // Execute query.
     $res = $mdb2->query($sql);
-    if (!is_a($res, 'PEAR_Error')) {
-  	  while ($val = $res->fetchRow()) {
-  	  	if ('date' == $group_by_option) {
-          // This is needed to get the date in user date format.
-          $o_date = new DateAndTime(DB_DATEFORMAT, $val['group_field']);
-          $val['group_field'] = $o_date->toString($user->date_format);
-          unset($o_date);
-  	  	}
-  	  	$time = $val['time'] ? sec_to_time_fmt_hm($val['time']) : null;
-        if ($report['show_cost']) {
-  	      if ('.' != $user->decimal_mark) {
-  	        $val['cost'] = str_replace('.', $user->decimal_mark, $val['cost']);
-  	        $val['expenses'] = str_replace('.', $user->decimal_mark, $val['expenses']);
-  	      }
-      	  $subtotals[$val['group_field']] = array('name'=>$val['group_field'],'time'=>$time,'cost'=>$val['cost'],'expenses'=>$val['expenses']);
-        } else
-          $subtotals[$val['group_field']] = array('name'=>$val['group_field'],'time'=>$time);
+    if (is_a($res, 'PEAR_Error')) die($res->getMessage());
+
+    while ($val = $res->fetchRow()) {
+      if ('date' == $group_by_option) {
+        // This is needed to get the date in user date format.
+        $o_date = new DateAndTime(DB_DATEFORMAT, $val['group_field']);
+        $val['group_field'] = $o_date->toString($user->date_format);
+        unset($o_date);
       }
-    } else
-      die($res->getMessage());
+      $time = $val['time'] ? sec_to_time_fmt_hm($val['time']) : null;
+      if ($report['show_cost']) {
+        if ('.' != $user->decimal_mark) {
+          $val['cost'] = str_replace('.', $user->decimal_mark, $val['cost']);
+          $val['expenses'] = str_replace('.', $user->decimal_mark, $val['expenses']);
+        }
+        $subtotals[$val['group_field']] = array('name'=>$val['group_field'],'time'=>$time,'cost'=>$val['cost'],'expenses'=>$val['expenses']);
+      } else
+        $subtotals[$val['group_field']] = array('name'=>$val['group_field'],'time'=>$time);
+    }
 
     return $subtotals;
   }
-  
+
   // getTotals calculates total hours and cost for all report items.
   static function getTotals($bean)
   {