X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;ds=inline;f=WEB-INF%2Flib%2FttReportHelper.class.php;h=0cc2702cb502627dfb032879b5138338e32a4727;hb=3b3e56c2f4e2a9624f0e25b1d5b0892a421a00bd;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..0cc2702c 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; @@ -724,12 +835,16 @@ class ttReportHelper { $body .= '