X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=WEB-INF%2Flib%2FttReportHelper.class.php;h=2fdaea4f1665910eb58f889f15fea7f1238ce5d5;hb=8e1e5862042f445b3fc7dbe9aed8fcef14af4239;hp=d44806ab577e8a97259e0cf813d9f68b71b5a05b;hpb=d76ad24052b5d1fd647938e1941628e295769b4b;p=timetracker.git diff --git a/WEB-INF/lib/ttReportHelper.class.php b/WEB-INF/lib/ttReportHelper.class.php index d44806ab..2fdaea4f 100644 --- a/WEB-INF/lib/ttReportHelper.class.php +++ b/WEB-INF/lib/ttReportHelper.class.php @@ -632,18 +632,18 @@ class ttReportHelper { // Without expenses, it's a simple select with group by. // With expenses, it becomes a select with group by from a combined set of records obtained with "union all". static function getSubtotals($bean) { - global $user; - + global $user; + $group_by_option = $bean->getAttribute('group_by'); if ('no_grouping' == $group_by_option) return null; - + $mdb2 = getConnection(); - + // Start with sql to obtain subtotals for time items. This simple sql will be used when we have no expenses. - + // Determine group by field and a required join. switch ($group_by_option) { - case 'date': + case 'date': $group_field = 'l.date'; $group_join = ''; break; @@ -656,54 +656,54 @@ class ttReportHelper { $group_join = 'left join tt_clients c on (l.client_id = c.id) '; break; case 'project': - $group_field = 'p.name'; + $group_field = 'p.name'; $group_join = 'left join tt_projects p on (l.project_id = p.id) '; - break; + break; case 'task': - $group_field = 't.name'; + $group_field = 't.name'; $group_join = 'left join tt_tasks t on (l.task_id = t.id) '; - break; + break; case 'cf_1': - $group_field = 'cfo.value'; - $custom_fields = new CustomFields($user->team_id); - if ($custom_fields->fields[0]['type'] == CustomFields::TYPE_TEXT) + $group_field = 'cfo.value'; + $custom_fields = new CustomFields($user->team_id); + if ($custom_fields->fields[0]['type'] == CustomFields::TYPE_TEXT) $group_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.value = cfo.id) '; elseif ($custom_fields->fields[0]['type'] == CustomFields::TYPE_DROPDOWN) $group_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) '; - break; + break; } $where = ttReportHelper::getWhere($bean); if ($bean->getAttribute('chcost')) { if (MODE_TIME == $user->tracking_mode) { - if ($group_by_option != 'user') - $left_join = 'left join tt_users u on (l.user_id = u.id)'; + 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, 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"; + $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, 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 + 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 + $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"; } // 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. if ($bean->getAttribute('chcost') && in_array('ex', explode(',', $user->plugins))) { // if ex(penses) plugin is enabled - + // Determine group by field and a required join. $group_join = null; $group_field = 'null'; switch ($group_by_option) { - case 'date': + case 'date': $group_field = 'ei.date'; $group_join = ''; break; @@ -716,9 +716,9 @@ class ttReportHelper { $group_join = 'left join tt_clients c on (ei.client_id = c.id) '; break; case 'project': - $group_field = 'p.name'; + $group_field = 'p.name'; $group_join = 'left join tt_projects p on (ei.project_id = p.id) '; - break; + break; } $where = ttReportHelper::getExpenseWhere($bean); @@ -726,53 +726,52 @@ class ttReportHelper { $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"; } - + // Execute query. $res = $mdb2->query($sql); - if (!is_a($res, 'PEAR_Error')) { - while ($val = $res->fetchRow()) { - if ('date' == $group_by_option) { - // This is needed to get the date in user date format. - $o_date = new DateAndTime(DB_DATEFORMAT, $val['group_field']); - $val['group_field'] = $o_date->toString($user->date_format); - unset($o_date); - } - $time = $val['time'] ? sec_to_time_fmt_hm($val['time']) : null; - if ($bean->getAttribute('chcost')) { - if ('.' != $user->decimal_mark) { - $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']); - } else - $subtotals[$val['group_field']] = array('name'=>$val['group_field'],'time'=>$time); + if (is_a($res, 'PEAR_Error')) die($res->getMessage()); + + while ($val = $res->fetchRow()) { + if ('date' == $group_by_option) { + // This is needed to get the date in user date format. + $o_date = new DateAndTime(DB_DATEFORMAT, $val['group_field']); + $val['group_field'] = $o_date->toString($user->date_format); + unset($o_date); } - } else - die($res->getMessage()); + $time = $val['time'] ? sec_to_time_fmt_hm($val['time']) : null; + if ($bean->getAttribute('chcost')) { + if ('.' != $user->decimal_mark) { + $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']); + } else + $subtotals[$val['group_field']] = array('name'=>$val['group_field'],'time'=>$time); + } return $subtotals; } - + // getFavSubtotals calculates report items subtotals when a favorite report is grouped by. // Without expenses, it's a simple select with group by. // With expenses, it becomes a select with group by from a combined set of records obtained with "union all". static function getFavSubtotals($report) { - global $user; - + global $user; + $group_by_option = $report['group_by']; if ('no_grouping' == $group_by_option) return null; - + $mdb2 = getConnection(); - + // Start with sql to obtain subtotals for time items. This simple sql will be used when we have no expenses. - + // Determine group by field and a required join. switch ($group_by_option) { - case 'date': + case 'date': $group_field = 'l.date'; $group_join = ''; break; @@ -785,32 +784,32 @@ class ttReportHelper { $group_join = 'left join tt_clients c on (l.client_id = c.id) '; break; case 'project': - $group_field = 'p.name'; + $group_field = 'p.name'; $group_join = 'left join tt_projects p on (l.project_id = p.id) '; - break; + break; case 'task': - $group_field = 't.name'; + $group_field = 't.name'; $group_join = 'left join tt_tasks t on (l.task_id = t.id) '; - break; + break; case 'cf_1': - $group_field = 'cfo.value'; - $custom_fields = new CustomFields($user->team_id); - if ($custom_fields->fields[0]['type'] == CustomFields::TYPE_TEXT) + $group_field = 'cfo.value'; + $custom_fields = new CustomFields($user->team_id); + if ($custom_fields->fields[0]['type'] == CustomFields::TYPE_TEXT) $group_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.value = cfo.id) '; elseif ($custom_fields->fields[0]['type'] == CustomFields::TYPE_DROPDOWN) $group_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) '; - break; + break; } $where = ttReportHelper::getFavWhere($report); if ($report['show_cost']) { if (MODE_TIME == $user->tracking_mode) { - if ($group_by_option != 'user') - $left_join = 'left join tt_users u on (l.user_id = u.id)'; + 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, 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"; + $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, @@ -820,19 +819,19 @@ class ttReportHelper { 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 + $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"; } // 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. if ($report['show_cost'] && in_array('ex', explode(',', $user->plugins))) { // if ex(penses) plugin is enabled - + // Determine group by field and a required join. $group_join = null; $group_field = 'null'; switch ($group_by_option) { - case 'date': + case 'date': $group_field = 'ei.date'; $group_join = ''; break; @@ -845,9 +844,9 @@ class ttReportHelper { $group_join = 'left join tt_clients c on (ei.client_id = c.id) '; break; case 'project': - $group_field = 'p.name'; + $group_field = 'p.name'; $group_join = 'left join tt_projects p on (ei.project_id = p.id) '; - break; + break; } $where = ttReportHelper::getFavExpenseWhere($report); @@ -855,37 +854,36 @@ class ttReportHelper { $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"; } - + // Execute query. $res = $mdb2->query($sql); - if (!is_a($res, 'PEAR_Error')) { - while ($val = $res->fetchRow()) { - if ('date' == $group_by_option) { - // This is needed to get the date in user date format. - $o_date = new DateAndTime(DB_DATEFORMAT, $val['group_field']); - $val['group_field'] = $o_date->toString($user->date_format); - unset($o_date); - } - $time = $val['time'] ? sec_to_time_fmt_hm($val['time']) : null; - if ($report['show_cost']) { - if ('.' != $user->decimal_mark) { - $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']); - } else - $subtotals[$val['group_field']] = array('name'=>$val['group_field'],'time'=>$time); + if (is_a($res, 'PEAR_Error')) die($res->getMessage()); + + while ($val = $res->fetchRow()) { + if ('date' == $group_by_option) { + // This is needed to get the date in user date format. + $o_date = new DateAndTime(DB_DATEFORMAT, $val['group_field']); + $val['group_field'] = $o_date->toString($user->date_format); + unset($o_date); } - } else - die($res->getMessage()); + $time = $val['time'] ? sec_to_time_fmt_hm($val['time']) : null; + if ($report['show_cost']) { + if ('.' != $user->decimal_mark) { + $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']); + } else + $subtotals[$val['group_field']] = array('name'=>$val['group_field'],'time'=>$time); + } return $subtotals; } - + // getTotals calculates total hours and cost for all report items. static function getTotals($bean) {