X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=WEB-INF%2Flib%2FttReportHelper.class.php;h=0900f7f9d0e440717d5bbcfc8886b689e3373503;hb=8ec1dac1bcf71ce282ab116413d335f582b5e8f3;hp=170dc2a9f30f7ff7fea808cfdfae354a97195dac;hpb=c5c7ed2be4f41e34e63ee90158f499a6f76a76c1;p=timetracker.git diff --git a/WEB-INF/lib/ttReportHelper.class.php b/WEB-INF/lib/ttReportHelper.class.php index 170dc2a9..0900f7f9 100644 --- a/WEB-INF/lib/ttReportHelper.class.php +++ b/WEB-INF/lib/ttReportHelper.class.php @@ -276,6 +276,9 @@ class ttReportHelper { // Add duration. if ($bean->getAttribute('chduration')) array_push($fields, "TIME_FORMAT(l.duration, '%k:%i') as duration"); + // Add work units. + if ($bean->getAttribute('chunits')) + array_push($fields, "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"); // Add note. if ($bean->getAttribute('chnote')) array_push($fields, 'l.comment as note'); @@ -303,10 +306,6 @@ class ttReportHelper { if (($canViewReports || $isClient) && $bean->getAttribute('chinvoice')) array_push($fields, 'i.name as invoice'); - // Add work units. - if ($bean->getAttribute('chunits')) - array_push($fields, "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"); - // Prepare sql query part for left joins. $left_joins = null; if ($bean->getAttribute('chclient') || 'client' == $group_by_option) @@ -549,6 +548,10 @@ class ttReportHelper { // Add duration. if ($report['show_duration']) array_push($fields, "TIME_FORMAT(l.duration, '%k:%i') as duration"); + // Add work units. + if ($report['show_work_units']) + array_push($fields, "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"); + // Add note. if ($report['show_note']) array_push($fields, 'l.comment as note'); @@ -632,6 +635,8 @@ class ttReportHelper { array_push($fields, 'null'); // null for finish. if ($report['show_duration']) array_push($fields, 'null'); // null for duration. + if ($report['show_work_units']) + array_push($fields, 'null'); // null for work units. // Use the note field to print item name. if ($report['show_note']) array_push($fields, 'ei.name as note'); @@ -909,21 +914,25 @@ class ttReportHelper { if (MODE_TIME == $user->tracking_mode) { 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, + $sql = "select $group_field as group_field, sum(time_to_sec(l.duration)) as time, + 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, 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"; } 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, + $sql = "select $group_field as group_field, sum(time_to_sec(l.duration)) as time, + 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, 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 $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 - $group_join $where group by $group_field"; + $sql = "select $group_field as group_field, sum(time_to_sec(l.duration)) as time, + 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, + null as expenses from tt_log l + $group_join $where group by $group_field"; } // By now we have sql for time items. @@ -953,13 +962,13 @@ class ttReportHelper { } $where = ttReportHelper::getFavExpenseWhere($report); - $sql_for_expenses = "select $group_field as group_field, null as time, sum(ei.cost) as cost, sum(ei.cost) as expenses from tt_expense_items ei + $sql_for_expenses = "select $group_field as group_field, null as time, null as units, sum(ei.cost) as cost, sum(ei.cost) as expenses from tt_expense_items ei $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"; + $sql = "select group_field, sum(time) as time, sum(units) as units, sum(cost) as cost, sum(expenses) as expenses from (($sql) union all ($sql_for_expenses)) t group by group_field"; } // Execute query. @@ -979,9 +988,9 @@ class ttReportHelper { $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']); + $subtotals[$val['group_field']] = array('name'=>$val['group_field'],'time'=>$time, 'units'=> $val['units'], 'cost'=>$val['cost'],'expenses'=>$val['expenses']); } else - $subtotals[$val['group_field']] = array('name'=>$val['group_field'],'time'=>$time); + $subtotals[$val['group_field']] = array('name'=>$val['group_field'],'time'=>$time, 'units'=> $val['units']); } return $subtotals; @@ -1078,26 +1087,30 @@ class ttReportHelper { if ($report['show_cost']) { if (MODE_TIME == $user->tracking_mode) { $sql = "select sum(time_to_sec(l.duration)) as time, + 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, 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 left join tt_users u on (l.user_id = u.id) $where"; } else { $sql = "select sum(time_to_sec(l.duration)) as time, + 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, 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 left join tt_user_project_binds upb on (l.user_id = upb.user_id and l.project_id = upb.project_id) $where"; } } else - $sql = "select sum(time_to_sec(l.duration)) as time, null as cost, null as expenses from tt_log l $where"; + $sql = "select sum(time_to_sec(l.duration)) as time, + 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, + null as cost, null as expenses from tt_log l $where"; // If we have expenses, query becomes a bit more complex. if ($report['show_cost'] && $user->isPluginEnabled('ex')) { $where = ttReportHelper::getFavExpenseWhere($report); - $sql_for_expenses = "select null as time, sum(cost) as cost, sum(cost) as expenses from tt_expense_items ei $where"; + $sql_for_expenses = "select null as time, null as units, sum(cost) as cost, sum(cost) as expenses from tt_expense_items ei $where"; // Create a combined query. - $sql = "select sum(time) as time, sum(cost) as cost, sum(expenses) as expenses from (($sql) union all ($sql_for_expenses)) t"; + $sql = "select sum(time) as time, sum(units) as units, sum(cost) as cost, sum(expenses) as expenses from (($sql) union all ($sql_for_expenses)) t"; } // Execute query. @@ -1129,6 +1142,7 @@ class ttReportHelper { $totals['start_date'] = $period->getStartDate(); $totals['end_date'] = $period->getEndDate(); $totals['time'] = $total_time; + $totals['units'] = $val['units']; $totals['cost'] = $total_cost; $totals['expenses'] = $total_expenses; @@ -1229,6 +1243,8 @@ class ttReportHelper { $body .= '