X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;ds=sidebyside;f=WEB-INF%2Flib%2FttReportHelper.class.php;h=ac3582e68b378437d0c8352757ea142706f96714;hb=a6f6687b468a81a0f96c108faefbfc69dd607acf;hp=6b84f4c7183dffb5c6355bfbb05cc42988792fe1;hpb=4453de30f591a32c3de8fd71f5b5ad9558d3878e;p=timetracker.git

diff --git a/WEB-INF/lib/ttReportHelper.class.php b/WEB-INF/lib/ttReportHelper.class.php
index 6b84f4c7..ac3582e6 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 {
 
@@ -60,8 +68,10 @@ 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['paid_status']=='1') $dropdown_parts .= ' and l.paid = 1';
     if ($options['paid_status']=='2') $dropdown_parts .= ' and l.paid = 0';
 
@@ -108,8 +118,10 @@ 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 ($options['timesheet']==TIMESHEET_NOT_ASSIGNED) $dropdown_parts .= ' and ei.timesheet_id is null';
+    if ($options['timesheet']==TIMESHEET_ASSIGNED) $dropdown_parts .= ' and ei.timesheet_id is not null';
     if ($options['paid_status']=='1') $dropdown_parts .= ' and ei.paid = 1';
     if ($options['paid_status']=='2') $dropdown_parts .= ' and ei.paid = 0';
 
@@ -260,13 +272,25 @@ 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)";
-    if ($options['show_timesheet'])
-      $left_joins .= " left join tt_timesheets ts on (l.timesheet_id = ts.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.approval_status is null)";
+      else if ($timesheet_option == TIMESHEET_APPROVED)
+        $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approval_status = 1)";
+      else if ($timesheet_option == TIMESHEET_NOT_APPROVED)
+        $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approval_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.
 
@@ -331,13 +355,25 @@ class ttReportHelper {
         $left_joins .= " left join tt_projects p on (p.id = ei.project_id)";
       if (($canViewReports || $isClient) && $options['show_invoice'])
         $left_joins .= " left join tt_invoices i on (i.id = ei.invoice_id and i.status = 1)";
-      if ($options['show_timesheet'])
-        $left_joins .= " left join tt_timesheets ts on (ei.timesheet_id = ts.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 (ei.timesheet_id = ts.id and ts.submit_status = 1 and ts.approval_status is null)";
+        else if ($timesheet_option == TIMESHEET_APPROVED)
+          $inner_joins .= " inner join tt_timesheets ts on (ei.timesheet_id = ts.id and ts.approval_status = 1)";
+        else if ($timesheet_option == TIMESHEET_NOT_APPROVED)
+          $inner_joins .= " inner join tt_timesheets ts on (ei.timesheet_id = ts.id and ts.approval_status = 0)";
+        else if ($options['show_timesheet'])
+          $inner_joins .= " left join tt_timesheets ts on (ei.timesheet_id = ts.id)"; // Left join for timesheet name.
+      }
 
       $where = ttReportHelper::getExpenseWhere($options);
 
       // Construct sql query for expense items.
-      $sql_for_expense_items = "select ".join(', ', $fields)." from tt_expense_items ei $left_joins $where";
+      $sql_for_expense_items = "select ".join(', ', $fields)." from tt_expense_items ei $left_joins $inner_joins $where";
 
       // Construct a union.
       $sql = "($sql) union all ($sql_for_expense_items)";
@@ -515,15 +551,37 @@ 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.approval_status is null)";
+      else if ($timesheet_option == TIMESHEET_APPROVED)
+        $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approval_status = 1)";
+      else if ($timesheet_option == TIMESHEET_NOT_APPROVED)
+        $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approval_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')) {
+      // 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 (ei.timesheet_id = ts.id and ts.submit_status = 1 and ts.approval_status is null)";
+        else if ($timesheet_option == TIMESHEET_APPROVED)
+          $inner_joins .= " inner join tt_timesheets ts on (ei.timesheet_id = ts.id and ts.approval_status = 1)";
+        else if ($timesheet_option == TIMESHEET_NOT_APPROVED)
+          $inner_joins .= " inner join tt_timesheets ts on (ei.timesheet_id = ts.id and ts.approval_status = 0)";
+      }
       $where = ttReportHelper::getExpenseWhere($options);
       $sql_for_expenses = "select null as time";
       if ($options['show_work_units']) $sql_for_expenses .= ", null as units";
-      $sql_for_expenses .= ", sum(cost) as cost, sum(cost) as expenses from tt_expense_items ei $where";
+      $sql_for_expenses .= ", sum(cost) as cost, sum(cost) as expenses from tt_expense_items ei $inner_joins $where";
 
       // Create a combined query.
       $combined = "select sum(time) as time";
@@ -995,6 +1053,9 @@ class ttReportHelper {
     $options['billable'] = $bean->getAttribute('include_records');
     $options['invoice'] = $bean->getAttribute('invoice');
     $options['paid_status'] = $bean->getAttribute('paid_status');
+    $options['timesheet'] = $bean->getAttribute('timesheet');
+    if ($user->isPluginEnabled('ts') && $user->isClient() && !$user->can('view_client_unapproved'))
+      $options['timesheet'] = TIMESHEET_APPROVED; // Restrict clients to approved timesheet records only.
     if (is_array($bean->getAttribute('users'))) $options['users'] = join(',', $bean->getAttribute('users'));
     $options['period'] = $bean->getAttribute('period');
     $options['period_start'] = $bean->getAttribute('start_date');
@@ -1487,6 +1548,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.approval_status is null)";
+      else if ($timesheet_option == TIMESHEET_APPROVED)
+        $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approval_status = 1)";
+      else if ($timesheet_option == TIMESHEET_NOT_APPROVED)
+        $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approval_status = 0)";
+    }
+    $join .= $inner_joins;
     return $join;
   }
 
@@ -1522,6 +1595,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)';
     }
@@ -1531,6 +1606,18 @@ class ttReportHelper {
     if (ttReportHelper::groupingBy('project', $options)) {
       $join .= ' left join tt_projects p on (ei.project_id = p.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 (ei.timesheet_id = ts.id and ts.submit_status = 1 and ts.approval_status is null)";
+      else if ($timesheet_option == TIMESHEET_APPROVED)
+        $inner_joins .= " inner join tt_timesheets ts on (ei.timesheet_id = ts.id and ts.approval_status = 1)";
+      else if ($timesheet_option == TIMESHEET_NOT_APPROVED)
+        $inner_joins .= " inner join tt_timesheets ts on (ei.timesheet_id = ts.id and ts.approval_status = 0)";
+    }
+    $join .= $inner_joins;
     return $join;
   }