X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=WEB-INF%2Flib%2FttReportHelper.class.php;h=ac3582e68b378437d0c8352757ea142706f96714;hb=a6f6687b468a81a0f96c108faefbfc69dd607acf;hp=8e8b312e4307efa8543bf628be31fd88932d10dc;hpb=d0c39073f009db09baa980ef6e1c2f1e71500a58;p=timetracker.git diff --git a/WEB-INF/lib/ttReportHelper.class.php b/WEB-INF/lib/ttReportHelper.class.php index 8e8b312e..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 { @@ -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,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'; @@ -89,6 +105,12 @@ class ttReportHelper { $group_id = $user->getGroup(); $org_id = $user->org_id; + // A shortcut for timesheets. + if ($options['timesheet_id']) { + $where = " where ei.timesheet_id = ".$options['timesheet_id']." and ei.group_id = $group_id and ei.org_id = $org_id"; + return $where; + } + // Prepare dropdown parts. $dropdown_parts = ''; if ($options['client_id']) @@ -96,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'; @@ -150,9 +174,10 @@ class ttReportHelper { // 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'); + array_push($fields, 'l.id'); + array_push($fields, 'l.user_id'); array_push($fields, '1 as type'); // Type 1 is for tt_log entries. - array_push($fields, 'l.date as date'); + array_push($fields, 'l.date'); if($canViewReports || $isClient) array_push($fields, 'u.name as user'); // Add client name if it is selected. @@ -191,8 +216,8 @@ class ttReportHelper { if ($user->getConfigOption('unit_totals_only')) array_push($fields, "null as units"); else { - $firstUnitThreshold = $user->getConfigInt('1st_unit_threshold'); - $minutesInUnit = $user->getConfigInt('minutes_in_unit'); + $firstUnitThreshold = $user->getConfigInt('1st_unit_threshold', 0); + $minutesInUnit = $user->getConfigInt('minutes_in_unit', 15); array_push($fields, "if(l.billable = 0 or time_to_sec(l.duration)/60 < $firstUnitThreshold, 0, ceil(time_to_sec(l.duration)/60/$minutesInUnit)) as units"); } } @@ -210,17 +235,20 @@ class ttReportHelper { } // Add paid status. if ($canViewReports && $options['show_paid']) - array_push($fields, 'l.paid as paid'); + array_push($fields, 'l.paid'); // Add IP address. if ($canViewReports && $options['show_ip']) { - array_push($fields, 'l.created as created'); - array_push($fields, 'l.created_ip as created_ip'); - array_push($fields, 'l.modified as modified'); - array_push($fields, 'l.modified_ip as modified_ip'); + array_push($fields, 'l.created'); + array_push($fields, 'l.created_ip'); + array_push($fields, 'l.modified'); + array_push($fields, 'l.modified_ip'); } // 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; @@ -245,10 +273,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.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. @@ -257,6 +299,7 @@ class ttReportHelper { $fields = array(); // An array of fields for database query. array_push($fields, 'ei.id'); + 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'); if($canViewReports || $isClient) @@ -288,17 +331,19 @@ class ttReportHelper { array_push($fields, 'ei.cost as expense'); // Add paid status. if ($canViewReports && $options['show_paid']) - array_push($fields, 'ei.paid as paid'); + array_push($fields, 'ei.paid'); // Add IP address. if ($canViewReports && $options['show_ip']) { - array_push($fields, 'ei.created as created'); - array_push($fields, 'ei.created_ip as created_ip'); - array_push($fields, 'ei.modified as modified'); - array_push($fields, 'ei.modified_ip as modified_ip'); + array_push($fields, 'ei.created'); + array_push($fields, 'ei.created_ip'); + array_push($fields, 'ei.modified'); + array_push($fields, 'ei.modified_ip'); } // 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, 'ts.name as timesheet_name'); // Prepare sql query part for left joins. $left_joins = null; @@ -311,10 +356,24 @@ class ttReportHelper { if (($canViewReports || $isClient) && $options['show_invoice']) $left_joins .= " left join tt_invoices i on (i.id = ei.invoice_id and i.status = 1)"; + // 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)"; @@ -410,57 +469,14 @@ class ttReportHelper { $mdb2 = getConnection(); $concat_part = ttReportHelper::makeConcatPart($options); + $work_unit_part = ttReportHelper::makeWorkUnitPart($options); $join_part = ttReportHelper::makeJoinPart($options); + $cost_part = ttReportHelper::makeCostPart($options); $where = ttReportHelper::getWhere($options); $group_by_part = ttReportHelper::makeGroupByPart($options); - if ($options['show_cost']) { - $workUnits = $options['show_work_units']; - if ($workUnits) { - $unitTotalsOnly = $user->getConfigOption('unit_totals_only'); - $firstUnitThreshold = $user->getConfigInt('1st_unit_threshold'); - $minutesInUnit = $user->getConfigInt('minutes_in_unit', 15); - } - - if (MODE_TIME == $user->getTrackingMode()) { - if (!ttReportHelper::groupingBy('user', $options)) - $left_join = 'left join tt_users u on (l.user_id = u.id)'; - $sql = "select $concat_part, sum(time_to_sec(l.duration)) as time"; - if ($workUnits) { - if ($unitTotalsOnly) - $sql .= ", if (sum(l.billable * time_to_sec(l.duration)/60) < $firstUnitThreshold, 0, ceil(sum(l.billable * time_to_sec(l.duration)/60/$minutesInUnit))) as units"; - else - $sql .= ", sum(if(l.billable = 0 or time_to_sec(l.duration)/60 < $firstUnitThreshold, 0, ceil(time_to_sec(l.duration)/60/$minutesInUnit))) as units"; - } - $sql .= ", 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 - $join_part $left_join $where $group_by_part"; - } 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 $concat_part, sum(time_to_sec(l.duration)) as time"; - if ($workUnits) { - if ($unitTotalsOnly) - $sql .= ", if (sum(l.billable * time_to_sec(l.duration)/60) < $firstUnitThreshold, 0, ceil(sum(l.billable * time_to_sec(l.duration)/60/$minutesInUnit))) as units"; - else - $sql .= ", sum(if(l.billable = 0 or time_to_sec(l.duration)/60 < $firstUnitThreshold, 0, ceil(time_to_sec(l.duration)/60/$minutesInUnit))) as units"; - } - $sql .= ", 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 - $join_part - left join tt_user_project_binds upb on (l.user_id = upb.user_id and l.project_id = upb.project_id) $where $group_by_part"; - } - } else { - // $sql = "select $group_field as group_field, sum(time_to_sec(l.duration)) as time"; - $sql = "select $concat_part, sum(time_to_sec(l.duration)) as time"; - if ($workUnits) { - if ($unitTotalsOnly) - $sql .= ", if (sum(l.billable * time_to_sec(l.duration)/60) < $firstUnitThreshold, 0, ceil(sum(l.billable * time_to_sec(l.duration)/60/$minutesInUnit))) as units"; - else - $sql .= ", sum(if(l.billable = 0 or time_to_sec(l.duration)/60 < $firstUnitThreshold, 0, ceil(time_to_sec(l.duration)/60/$minutesInUnit))) as units"; - } - $sql .= ", null as expenses from tt_log l - $join_part $where $group_by_part"; - } + $parts = "$concat_part, sum(time_to_sec(l.duration)) as time, null as expenses".$work_unit_part.$cost_part; + $sql = "select $parts from tt_log l $join_part $where $group_by_part"; // 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. @@ -506,18 +522,22 @@ class ttReportHelper { static function getTotals($options) { global $user; - $mdb2 = getConnection(); + $trackingMode = $user->getTrackingMode(); + $decimalMark = $user->getDecimalMark(); $where = ttReportHelper::getWhere($options); // Prepare parts. $time_part = "sum(time_to_sec(l.duration)) as time"; if ($options['show_work_units']) { - $units_part = $user->unit_totals_only ? ", null as units" : ", sum(if(l.billable = 0 or time_to_sec(l.duration)/60 < $user->first_unit_threshold, 0, ceil(time_to_sec(l.duration)/60/$user->minutes_in_unit))) as units"; + $unitTotalsOnly = $user->getConfigOption('unit_totals_only'); + $firstUnitThreshold = $user->getConfigInt('1st_unit_threshold', 0); + $minutesInUnit = $user->getConfigInt('minutes_in_unit', 15); + $units_part = $unitTotalsOnly ? ", null as units" : ", sum(if(l.billable = 0 or time_to_sec(l.duration)/60 < $firstUnitThreshold, 0, ceil(time_to_sec(l.duration)/60/$minutesInUnit))) as units"; } if ($options['show_cost']) { - if (MODE_TIME == $user->tracking_mode) + if (MODE_TIME == $trackingMode) $cost_part = ", sum(cast(l.billable * coalesce(u.rate, 0) * time_to_sec(l.duration)/3600 as decimal(10,2))) as cost, null as expenses"; else $cost_part = ", sum(cast(l.billable * coalesce(upb.rate, 0) * time_to_sec(l.duration)/3600 as decimal(10,2))) as cost, null as expenses"; @@ -525,21 +545,43 @@ class ttReportHelper { $cost_part = ", null as cost, null as expenses"; } if ($options['show_cost']) { - if (MODE_TIME == $user->tracking_mode) { + if (MODE_TIME == $trackingMode) { $left_joins = "left join tt_users u on (l.user_id = u.id)"; } else { $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"; @@ -557,21 +599,22 @@ class ttReportHelper { if ($options['show_cost']) { $total_cost = $val['cost']; if (!$total_cost) $total_cost = '0.00'; - if ('.' != $user->decimal_mark) - $total_cost = str_replace('.', $user->decimal_mark, $total_cost); + if ('.' != $decimalMark) + $total_cost = str_replace('.', $decimalMark, $total_cost); $total_expenses = $val['expenses']; if (!$total_expenses) $total_expenses = '0.00'; - if ('.' != $user->decimal_mark) - $total_expenses = str_replace('.', $user->decimal_mark, $total_expenses); + if ('.' != $decimalMark) + $total_expenses = str_replace('.', $decimalMark, $total_expenses); } + $dateFormat = $user->getDateFormat(); if ($options['period']) - $period = new Period($options['period'], new DateAndTime($user->date_format)); + $period = new Period($options['period'], new DateAndTime($dateFormat)); else { $period = new Period(); $period->setPeriod( - new DateAndTime($user->date_format, $options['period_start']), - new DateAndTime($user->date_format, $options['period_end'])); + new DateAndTime($dateFormat, $options['period_start']), + new DateAndTime($dateFormat, $options['period_end'])); } $totals['start_date'] = $period->getStartDate(); @@ -1010,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'); @@ -1027,6 +1073,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'); @@ -1478,7 +1525,8 @@ class ttReportHelper { static function makeJoinPart($options) { global $user; - if (ttReportHelper::groupingBy('user', $options)) { + $trackingMode = $user->getTrackingMode(); + if (ttReportHelper::groupingBy('user', $options) || MODE_TIME == $trackingMode) { $join .= ' left join tt_users u on (l.user_id = u.id)'; } if (ttReportHelper::groupingBy('client', $options)) { @@ -1497,11 +1545,58 @@ class ttReportHelper { elseif ($custom_fields->fields[0]['type'] == CustomFields::TYPE_DROPDOWN) $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)'; } + 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; } + // makeWorkUnitPart builds an sql part for work units for time items. + static function makeWorkUnitPart($options) { + global $user; + + $workUnits = $options['show_work_units']; + if ($workUnits) { + $unitTotalsOnly = $user->getConfigOption('unit_totals_only'); + $firstUnitThreshold = $user->getConfigInt('1st_unit_threshold', 0); + $minutesInUnit = $user->getConfigInt('minutes_in_unit', 15); + if ($unitTotalsOnly) + $work_unit_part = ", if (sum(l.billable * time_to_sec(l.duration)/60) < $firstUnitThreshold, 0, ceil(sum(l.billable * time_to_sec(l.duration)/60/$minutesInUnit))) as units"; + else + $work_unit_part = ", sum(if(l.billable = 0 or time_to_sec(l.duration)/60 < $firstUnitThreshold, 0, ceil(time_to_sec(l.duration)/60/$minutesInUnit))) as units"; + } + return $work_unit_part; + } + + // makeCostPart builds a cost part for time items. + static function makeCostPart($options) { + global $user; + + if ($options['show_cost']) { + if (MODE_TIME == $user->getTrackingMode()) + $cost_part = ", sum(cast(l.billable * coalesce(u.rate, 0) * time_to_sec(l.duration)/3600 as decimal(10, 2))) as cost"; + else + $cost_part .= ", sum(cast(l.billable * coalesce(upb.rate, 0) * time_to_sec(l.duration)/3600 as decimal(10,2))) as cost"; + } + return $cost_part; + } + // 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)'; } @@ -1511,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; } @@ -1602,7 +1709,7 @@ class ttReportHelper { return $key; // No need to format. global $user; - if ($user->date_format == DB_DATEFORMAT) + if ($user->getDateFormat() == DB_DATEFORMAT) return $key; // No need to format. $label = $key;