X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=WEB-INF%2Flib%2FttReportHelper.class.php;h=4de5a48399b3d10c2a967eb4594090c6ff1da8a4;hb=1e825770a4defc5eb8d2032c35dc90e8bc8684da;hp=c3656ad6feb126ad6865cc7a2e8bb9326d32509e;hpb=0ae87b2b2eeb7710e1d169f63708c6c5dc2e2452;p=timetracker.git

diff --git a/WEB-INF/lib/ttReportHelper.class.php b/WEB-INF/lib/ttReportHelper.class.php
index c3656ad6..4de5a483 100644
--- a/WEB-INF/lib/ttReportHelper.class.php
+++ b/WEB-INF/lib/ttReportHelper.class.php
@@ -33,6 +33,14 @@ import('ttTimeHelper');
 
 require_once(dirname(__FILE__).'/../../plugins/CustomFields.class.php');
 
+// Definitions of types for timesheet dropdown.
+define('TIMESHEET_ALL', 0); // Include all records.
+define('TIMESHEET_NOT_ASSIGNED', 1); // Include records not assigned to timesheets.
+define('TIMESHEET_ASSIGNED', 2); // Include records assigned to timesheets.
+define('TIMESHEET_PENDING', 3); // Include records in submitted timesheets that are pending manager approval.
+define('TIMESHEET_APPROVED', 4); // Include records in approved timesheets.
+define('TIMESHEET_NOT_APPROVED', 5); // Include records in disapproved timesheets.
+
 // Class ttReportHelper is used for help with reports.
 class ttReportHelper {
 
@@ -43,6 +51,12 @@ class ttReportHelper {
     $group_id = $user->getGroup();
     $org_id = $user->org_id;
 
+    // A shortcut for timesheets.
+    if ($options['timesheet_id']) {
+      $where = " where l.timesheet_id = ".$options['timesheet_id']." and l.group_id = $group_id and l.org_id = $org_id";
+      return $where;
+    }
+
     // Prepare dropdown parts.
     $dropdown_parts = '';
     if ($options['client_id'])
@@ -54,8 +68,12 @@ class ttReportHelper {
     if ($options['task_id']) $dropdown_parts .= ' and l.task_id = '.$options['task_id'];
     if ($options['billable']=='1') $dropdown_parts .= ' and l.billable = 1';
     if ($options['billable']=='2') $dropdown_parts .= ' and l.billable = 0';
-    if ($options['invoice']=='1') $dropdown_parts .= ' and l.invoice_id is not NULL';
-    if ($options['invoice']=='2') $dropdown_parts .= ' and l.invoice_id is NULL';
+    if ($options['invoice']=='1') $dropdown_parts .= ' and l.invoice_id is not null';
+    if ($options['invoice']=='2') $dropdown_parts .= ' and l.invoice_id is null';
+    if ($options['timesheet']==TIMESHEET_NOT_ASSIGNED) $dropdown_parts .= ' and l.timesheet_id is null';
+    if ($options['timesheet']==TIMESHEET_ASSIGNED) $dropdown_parts .= ' and l.timesheet_id is not null';
+    if ($options['approved']=='1') $dropdown_parts .= ' and l.approved = 1';
+    if ($options['approved']=='2') $dropdown_parts .= ' and l.approved = 0';
     if ($options['paid_status']=='1') $dropdown_parts .= ' and l.paid = 1';
     if ($options['paid_status']=='2') $dropdown_parts .= ' and l.paid = 0';
 
@@ -96,8 +114,13 @@ class ttReportHelper {
     elseif ($user->isClient() && $user->client_id)
       $dropdown_parts .= ' and ei.client_id = '.$user->client_id;
     if ($options['project_id']) $dropdown_parts .= ' and ei.project_id = '.$options['project_id'];
-    if ($options['invoice']=='1') $dropdown_parts .= ' and ei.invoice_id is not NULL';
-    if ($options['invoice']=='2') $dropdown_parts .= ' and ei.invoice_id is NULL';
+    if ($options['invoice']=='1') $dropdown_parts .= ' and ei.invoice_id is not null';
+    if ($options['invoice']=='2') $dropdown_parts .= ' and ei.invoice_id is null';
+    if (isset($options['timesheet']) && ($options['timesheet']!=TIMESHEET_ALL && $options['timesheet']!=TIMESHEET_NOT_ASSIGNED)) {
+        $dropdown_parts .= ' and 0 = 1'; // Expense items do not have a timesheet_id.
+    }
+    if ($options['approved']=='1') $dropdown_parts .= ' and ei.approved = 1';
+    if ($options['approved']=='2') $dropdown_parts .= ' and ei.approved = 0';
     if ($options['paid_status']=='1') $dropdown_parts .= ' and ei.paid = 1';
     if ($options['paid_status']=='2') $dropdown_parts .= ' and ei.paid = 0';
 
@@ -154,7 +177,6 @@ class ttReportHelper {
     array_push($fields, 'l.user_id');
     array_push($fields, '1 as type'); // Type 1 is for tt_log entries.
     array_push($fields, 'l.date');
-    array_push($fields, 'l.timesheet_id');
     if($canViewReports || $isClient)
       array_push($fields, 'u.name as user');
     // Add client name if it is selected.
@@ -210,6 +232,9 @@ class ttReportHelper {
         array_push($fields, "cast(l.billable * coalesce(upb.rate, 0) * time_to_sec(l.duration)/3600 as decimal(10,2)) as cost"); // Use project rate for user.
       array_push($fields, "null as expense"); 
     }
+    // Add approved.
+    if ($options['show_approved'])
+      array_push($fields, 'l.approved');
     // Add paid status.
     if ($canViewReports && $options['show_paid'])
       array_push($fields, 'l.paid');
@@ -223,6 +248,9 @@ class ttReportHelper {
     // Add invoice name if it is selected.
     if (($canViewReports || $isClient) && $options['show_invoice'])
       array_push($fields, 'i.name as invoice');
+    // Add timesheet name if it is selected.
+    if ($options['show_timesheet'])
+      array_push($fields, 'ts.name as timesheet_name');
 
     // Prepare sql query part for left joins.
     $left_joins = null;
@@ -247,10 +275,24 @@ class ttReportHelper {
     if ($includeCost && MODE_TIME != $trackingMode)
       $left_joins .= " left join tt_user_project_binds upb on (l.user_id = upb.user_id and l.project_id = upb.project_id)";
 
+    // Prepare sql query part for inner joins.
+    $inner_joins = null;
+    if ($user->isPluginEnabled('ts')) {
+      $timesheet_option = $options['timesheet'];
+      if ($timesheet_option == TIMESHEET_PENDING)
+        $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.submit_status = 1 and ts.approve_status is null)";
+      else if ($timesheet_option == TIMESHEET_APPROVED)
+        $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approve_status = 1)";
+      else if ($timesheet_option == TIMESHEET_NOT_APPROVED)
+        $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approve_status = 0)";
+      else if ($options['show_timesheet'])
+        $inner_joins .= " left join tt_timesheets ts on (l.timesheet_id = ts.id)"; // Left join for timesheet nme.
+    }
+
     $where = ttReportHelper::getWhere($options);
 
     // Construct sql query for tt_log items.
-    $sql = "select ".join(', ', $fields)." from tt_log l $left_joins $where";
+    $sql = "select ".join(', ', $fields)." from tt_log l $left_joins $inner_joins $where";
     // If we don't have expense items (such as when the Expenses plugin is disabled), the above is all sql we need,
     // with an exception of sorting part, that is added in the end.
 
@@ -262,7 +304,6 @@ class ttReportHelper {
       array_push($fields, 'ei.user_id');
       array_push($fields, '2 as type'); // Type 2 is for tt_expense_items entries.
       array_push($fields, 'ei.date');
-      array_push($fields, 'ei.timesheet_id');
       if($canViewReports || $isClient)
         array_push($fields, 'u.name as user');
       // Add client name if it is selected.
@@ -290,6 +331,9 @@ class ttReportHelper {
         array_push($fields, 'ei.name as note');
       array_push($fields, 'ei.cost as cost');
       array_push($fields, 'ei.cost as expense');
+      // Add approved.
+      if ($options['show_approved'])
+        array_push($fields, 'ei.approved');
       // Add paid status.
       if ($canViewReports && $options['show_paid'])
         array_push($fields, 'ei.paid');
@@ -303,6 +347,8 @@ class ttReportHelper {
       // Add invoice name if it is selected.
       if (($canViewReports || $isClient) && $options['show_invoice'])
         array_push($fields, 'i.name as invoice');
+      if ($options['show_timesheet'])
+        array_push($fields, 'null as timesheet_name');
 
       // Prepare sql query part for left joins.
       $left_joins = null;
@@ -496,8 +542,19 @@ class ttReportHelper {
         $left_joins = "left join tt_user_project_binds upb on (l.user_id = upb.user_id and l.project_id = upb.project_id)";
       }
     }
+    // Prepare sql query part for inner joins.
+    $inner_joins = null;
+    if ($user->isPluginEnabled('ts') && $options['timesheet']) {
+      $timesheet_option = $options['timesheet'];
+      if ($timesheet_option == TIMESHEET_PENDING)
+        $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.submit_status = 1 and ts.approve_status is null)";
+      else if ($timesheet_option == TIMESHEET_APPROVED)
+        $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approve_status = 1)";
+      else if ($timesheet_option == TIMESHEET_NOT_APPROVED)
+        $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approve_status = 0)";
+    }
     // Prepare a query for time items.
-    $sql = "select $time_part $units_part $cost_part from tt_log l $left_joins $where";
+    $sql = "select $time_part $units_part $cost_part from tt_log l $left_joins $inner_joins $where";
 
     // If we have expenses, query becomes a bit more complex.
     if ($options['show_cost'] && $user->isPluginEnabled('ex')) {
@@ -572,6 +629,60 @@ class ttReportHelper {
     }
   }
 
+  // The assignToTimesheet assigns a set of tt_log records to a specific timesheet.
+  static function assignToTimesheet($timesheet_id, $time_log_ids) {
+    global $user;
+    $mdb2 = getConnection();
+
+    $user_id = $user->getUser();
+    $group_id = $user->getGroup();
+    $org_id = $user->org_id;
+
+    if ($time_log_ids) {
+      // Use inner join as a protection mechanism not to do anything with "acted upon" timesheets.
+      // Allow oprations only with pending timesheets.
+      if ($timesheet_id) {
+        // Assigning a timesheet to records.
+        $inner_join = " inner join tt_timesheets ts on (ts.id = $timesheet_id".
+          " and ts.user_id = $user_id and ts.approve_status is null". // Timesheet to assign to is pending.
+          // Part below: existing timesheet either not exists or is also pending.
+          " and (l.timesheet_id is null or (l.timesheet_id = ts.id and ts.approve_status is null)))";
+      } else {
+        $inner_join = " inner join tt_timesheets ts on (ts.id = l.timesheet_id".
+          " and ts.user_id = $user_id and ts.approve_status is null)"; // Do not deassign from acted-upon timesheets.
+      }
+
+      $sql = "update tt_log l $inner_join".
+        " set l.timesheet_id = ".$mdb2->quote($timesheet_id).
+        " where l.id in(".join(', ', $time_log_ids).") and l.user_id = $user_id and l.group_id = $group_id and l.org_id = $org_id";
+      $affected = $mdb2->exec($sql);
+      if (is_a($affected, 'PEAR_Error')) die($affected->getMessage());
+    }
+  }
+
+  // The markApproved marks a set of records as either approved or unapproved.
+  static function markApproved($time_log_ids, $expense_item_ids, $approved = true) {
+    global $user;
+    $mdb2 = getConnection();
+
+    $group_id = $user->getGroup();
+    $org_id = $user->org_id;
+
+    $approved_val = (int) $approved;
+    if ($time_log_ids) {
+      $sql = "update tt_log set approved = $approved_val".
+        " where id in(".join(', ', $time_log_ids).") and group_id = $group_id and org_id = $org_id";
+      $affected = $mdb2->exec($sql);
+      if (is_a($affected, 'PEAR_Error')) die($affected->getMessage());
+    }
+    if ($expense_item_ids) {
+      $sql = "update tt_expense_items set approved = $approved_val".
+        " where id in(".join(', ', $expense_item_ids).") and group_id = $group_id and org_id = $org_id";
+      $affected = $mdb2->exec($sql);
+      if (is_a($affected, 'PEAR_Error')) die($affected->getMessage());
+    }
+  }
+
   // The markPaid marks a set of records as either paid or unpaid.
   static function markPaid($time_log_ids, $expense_item_ids, $paid = true) {
     global $user;
@@ -627,6 +738,24 @@ class ttReportHelper {
     $cellLeftAlignedSubtotal = 'font-weight: bold; text-align: left; vertical-align: top;';
     $cellRightAlignedSubtotal = 'font-weight: bold; text-align: right; vertical-align: top;';
 
+    // Determine column span for note field.
+    $colspan = 1;
+    if ($user->can('view_reports') || $user->can('view_all_reports') || $user->isClient()) $colspan++;
+    if ($options['show_client']) $colspan++;
+    if ($options['show_project']) $colspan++;
+    if ($options['show_task']) $colspan++;
+    if ($options['show_custom_field_1']) $colspan++;
+    if ($options['show_start']) $colspan++;
+    if ($options['show_end']) $colspan++;
+    if ($options['show_duration']) $colspan++;
+    if ($options['show_work_units']) $colspan++;
+    if ($options['show_cost']) $colspan++;
+    if ($options['show_approved']) $colspan++;
+    if ($options['show_paid']) $colspan++;
+    if ($options['show_ip']) $colspan++;
+    if ($options['show_invoice']) $colspan++;
+    if ($options['show_timesheet']) $colspan++;
+
     // Start creating email body.
     $body = '<html>';
     $body .= '<head><meta http-equiv="content-type" content="text/html; charset='.CHARSET.'"></head>';
@@ -724,12 +853,16 @@ class ttReportHelper {
         $body .= '<td style="'.$tableHeader.'">'.$i18n->get('label.note').'</td>';
       if ($options['show_cost'])
         $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.cost').'</td>';
+      if ($options['show_approved'])
+        $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.approved').'</td>';
       if ($options['show_paid'])
         $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.paid').'</td>';
       if ($options['show_ip'])
         $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.ip').'</td>';
       if ($options['show_invoice'])
         $body .= '<td style="'.$tableHeader.'">'.$i18n->get('label.invoice').'</td>';
+      if ($options['show_timesheet'])
+        $body .= '<td style="'.$tableHeader.'">'.$i18n->get('label.timesheet').'</td>';
       $body .= '</tr>';
 
       // Initialize variables to print subtotals.
@@ -770,9 +903,11 @@ class ttReportHelper {
                 $body .= ($canViewReports || $isClient) ? $subtotals[$prev_grouped_by]['cost'] : $subtotals[$prev_grouped_by]['expenses'];
                 $body .= '</td>';
               }
+              if ($options['show_approved']) $body .= '<td></td>';
               if ($options['show_paid']) $body .= '<td></td>';
               if ($options['show_ip']) $body .= '<td></td>';
               if ($options['show_invoice']) $body .= '<td></td>';
+              if ($options['show_timesheet']) $body .= '<td></td>';
               $body .= '</tr>';
               $body .= '<tr><td>&nbsp;</td></tr>';
             }
@@ -806,6 +941,11 @@ class ttReportHelper {
             $body .= '<td style="'.$cellLeftAligned.'">'.htmlspecialchars($record['note']).'</td>';
           if ($options['show_cost'])
             $body .= '<td style="'.$cellRightAligned.'">'.$record['cost'].'</td>';
+          if ($options['show_approved']) {
+            $body .= '<td style="'.$cellRightAligned.'">';
+            $body .= $record['approved'] == 1 ? $i18n->get('label.yes') : $i18n->get('label.no');
+            $body .= '</td>';
+          }
           if ($options['show_paid']) {
             $body .= '<td style="'.$cellRightAligned.'">';
             $body .= $record['paid'] == 1 ? $i18n->get('label.yes') : $i18n->get('label.no');
@@ -818,6 +958,8 @@ class ttReportHelper {
           }
           if ($options['show_invoice'])
             $body .= '<td style="'.$cellRightAligned.'">'.htmlspecialchars($record['invoice']).'</td>';
+          if ($options['show_timesheet'])
+            $body .= '<td style="'.$cellRightAligned.'">'.htmlspecialchars($record['timesheet']).'</td>';
           $body .= '</tr>';
 
           $prev_date = $record['date'];
@@ -846,9 +988,11 @@ class ttReportHelper {
           $body .= ($canViewReports || $isClient) ? $subtotals[$cur_grouped_by]['cost'] : $subtotals[$cur_grouped_by]['expenses'];
           $body .= '</td>';
         }
+        if ($options['show_approved']) $body .= '<td></td>';
         if ($options['show_paid']) $body .= '<td></td>';
         if ($options['show_ip']) $body .= '<td></td>';
         if ($options['show_invoice']) $body .= '<td></td>';
+        if ($options['show_timesheet']) $body .= '<td></td>';
         $body .= '</tr>';
       }
 
@@ -871,9 +1015,11 @@ class ttReportHelper {
         $body .= ($canViewReports || $isClient) ? $totals['cost'] : $totals['expenses'];
         $body .= '</td>';
       }
+      if ($options['show_approved']) $body .= '<td></td>';
       if ($options['show_paid']) $body .= '<td></td>';
       if ($options['show_ip']) $body .= '<td></td>';
       if ($options['show_invoice']) $body .= '<td></td>';
+      if ($options['show_timesheet']) $body .= '<td></td>';
       $body .= '</tr>';
 
       $body .= '</table>';
@@ -976,12 +1122,17 @@ class ttReportHelper {
     $options['billable'] = $bean->getAttribute('include_records');
     $options['invoice'] = $bean->getAttribute('invoice');
     $options['paid_status'] = $bean->getAttribute('paid_status');
+    $options['approved'] = $bean->getAttribute('approved');
+    if ($user->isPluginEnabled('ap') && $user->isClient() && !$user->can('view_client_unapproved'))
+      $options['approved'] = 1; // Restrict clients to approved records only.
+    $options['timesheet'] = $bean->getAttribute('timesheet');
     if (is_array($bean->getAttribute('users'))) $options['users'] = join(',', $bean->getAttribute('users'));
     $options['period'] = $bean->getAttribute('period');
     $options['period_start'] = $bean->getAttribute('start_date');
     $options['period_end'] = $bean->getAttribute('end_date');
     $options['show_client'] = $bean->getAttribute('chclient');
     $options['show_invoice'] = $bean->getAttribute('chinvoice');
+    $options['show_approved'] = $bean->getAttribute('chapproved');
     $options['show_paid'] = $bean->getAttribute('chpaid');
     $options['show_ip'] = $bean->getAttribute('chip');
     $options['show_project'] = $bean->getAttribute('chproject');
@@ -993,6 +1144,7 @@ class ttReportHelper {
     $options['show_note'] = $bean->getAttribute('chnote');
     $options['show_custom_field_1'] = $bean->getAttribute('chcf_1');
     $options['show_work_units'] = $bean->getAttribute('chunits');
+    $options['show_timesheet'] = $bean->getAttribute('chtimesheet');
     $options['show_totals_only'] = $bean->getAttribute('chtotalsonly');
     $options['group_by1'] = $bean->getAttribute('group_by1');
     $options['group_by2'] = $bean->getAttribute('group_by2');
@@ -1467,6 +1619,18 @@ class ttReportHelper {
     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)';
     }
+    // Prepare inner joins.
+    $inner_joins = null;
+    if ($user->isPluginEnabled('ts') && $options['timesheet']) {
+      $timesheet_option = $options['timesheet'];
+      if ($timesheet_option == TIMESHEET_PENDING)
+        $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.submit_status = 1 and ts.approve_status is null)";
+      else if ($timesheet_option == TIMESHEET_APPROVED)
+        $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approve_status = 1)";
+      else if ($timesheet_option == TIMESHEET_NOT_APPROVED)
+        $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approve_status = 0)";
+    }
+    $join .= $inner_joins;
     return $join;
   }
 
@@ -1502,6 +1666,8 @@ class ttReportHelper {
 
   // makeJoinExpensesPart builds a left join part for getSubtotals query for expense items.
   static function makeJoinExpensesPart($options) {
+    global $user;
+
     if (ttReportHelper::groupingBy('user', $options)) {
       $join .= ' left join tt_users u on (ei.user_id = u.id)';
     }