// getItems retrieves all items associated with a report.
// It combines tt_log and tt_expense_items in one array for presentation in one table using mysql union all.
// Expense items use the "note" field for item name.
- static function getItems($bean, $options) {
+ static function getItems($options) {
global $user;
$mdb2 = getConnection();
// Determine these once as they are used in multiple places in this function.
- $canViewReports = $user->can('view_reports');
+ $canViewReports = $user->can('view_reports') || $user->can('view_all_reports');
$isClient = $user->isClient();
$group_by_option = $options['group_by'];
// If we don't have expense items (such as when the Expenses plugin is desabled), the above is all sql we need,
// with an exception of sorting part, that is added in the end.
-// TODO: refactoring in progress down from here... The above is identical to getFavItems and is ready to merge.
-
// 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') && $user->isPluginEnabled('ex')) { // if ex(penses) plugin is enabled
+ if ($options['show_cost'] && $user->isPluginEnabled('ex')) { // if ex(penses) plugin is enabled
$fields = array(); // An array of fields for database query.
array_push($fields, 'ei.id');
if($canViewReports || $isClient)
array_push($fields, 'u.name as user');
// Add client name if it is selected.
- if ($bean->getAttribute('chclient') || 'client' == $group_by_option)
+ if ($options['show_client'] || 'client' == $group_by_option)
array_push($fields, 'c.name as client');
// Add project name if it is selected.
- if ($bean->getAttribute('chproject') || 'project' == $group_by_option)
+ if ($options['show_project'] || 'project' == $group_by_option)
array_push($fields, 'p.name as project');
- if ($bean->getAttribute('chtask') || 'task' == $group_by_option)
+ if ($options['show_task'] || 'task' == $group_by_option)
array_push($fields, 'null'); // null for task name. We need to match column count for union.
- if ($bean->getAttribute('chcf_1') || 'cf_1' == $group_by_option)
+ if ($options['show_custom_field_1'] || 'cf_1' == $group_by_option)
array_push($fields, 'null'); // null for cf_1.
- if ($bean->getAttribute('chstart')) {
+ if ($options['show_start']) {
array_push($fields, 'null'); // null for unformatted_start.
array_push($fields, 'null'); // null for start.
}
- if ($bean->getAttribute('chfinish'))
+ if ($options['show_end'])
array_push($fields, 'null'); // null for finish.
- if ($bean->getAttribute('chduration'))
+ if ($options['show_duration'])
array_push($fields, 'null'); // null for duration.
- // Add work units.
- if ($bean->getAttribute('chunits'))
+ if ($options['show_work_units'])
array_push($fields, 'null as units'); // null for work units.
// Use the note field to print item name.
- if ($bean->getAttribute('chnote'))
+ if ($options['show_note'])
array_push($fields, 'ei.name as note');
array_push($fields, 'ei.cost as cost');
array_push($fields, 'ei.cost as expense');
// Add paid status.
- if ($canViewReports && $bean->getAttribute('chpaid'))
+ if ($canViewReports && $options['show_paid'])
array_push($fields, 'ei.paid as paid');
// Add IP address.
- if ($canViewReports && $bean->getAttribute('chip')) {
+ 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');
}
-
// Add invoice name if it is selected.
- if (($canViewReports || $isClient) && $bean->getAttribute('chinvoice'))
+ if (($canViewReports || $isClient) && $options['show_invoice'])
array_push($fields, 'i.name as invoice');
// Prepare sql query part for left joins.
$left_joins = null;
if ($canViewReports || $isClient)
$left_joins .= " left join tt_users u on (u.id = ei.user_id)";
- if ($bean->getAttribute('chclient') || 'client' == $group_by_option)
+ if ($options['show_client'] || 'client' == $group_by_option)
$left_joins .= " left join tt_clients c on (c.id = ei.client_id)";
- if ($bean->getAttribute('chproject') || 'project' == $group_by_option)
+ if ($options['show_project'] || 'project' == $group_by_option)
$left_joins .= " left join tt_projects p on (p.id = ei.project_id)";
- if (($canViewReports || $isClient) && $bean->getAttribute('chinvoice'))
+ if (($canViewReports || $isClient) && $options['show_invoice'])
$left_joins .= " left join tt_invoices i on (i.id = ei.invoice_id and i.status = 1)";
$where = ttReportHelper::getExpenseWhere($options);
// Determine sort part.
$sort_part = ' order by ';
- if ('no_grouping' == $group_by_option || 'date' == $group_by_option)
+ if ($group_by_option == null || 'no_grouping' == $group_by_option || 'date' == $group_by_option)
$sort_part .= 'date';
else
$sort_part .= $group_by_option.', date';
- if (($canViewReports || $isClient) && is_array($bean->getAttribute('users')) && 'user' != $group_by_option)
+ if (($canViewReports || $isClient) && $options['users'] && 'user' != $group_by_option)
$sort_part .= ', user, type';
- if ($bean->getAttribute('chstart'))
+ if ($options['show_start'])
$sort_part .= ', unformatted_start';
$sort_part .= ', id';
return $items;
}
- // getFavItems retrieves all items associated with a favorite report.
- // It combines tt_log and tt_expense_items in one array for presentation in one table using mysql union all.
- // Expense items use the "note" field for item name.
- static function getFavItems($options) {
- global $user;
- $mdb2 = getConnection();
-
- // Determine these once as they are used in multiple places in this function.
- $canViewReports = $user->can('view_reports');
- $isClient = $user->isClient();
-
- $group_by_option = $options['group_by'];
- $convertTo12Hour = ('%I:%M %p' == $user->time_format) && ($options['show_start'] || $options['show_end']);
-
- // 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, '1 as type'); // Type 1 is for tt_log entries.
- array_push($fields, 'l.date as date');
- if($canViewReports || $isClient)
- array_push($fields, 'u.name as user');
- // Add client name if it is selected.
- if ($options['show_client'] || 'client' == $group_by_option)
- array_push($fields, 'c.name as client');
- // Add project name if it is selected.
- if ($options['show_project'] || 'project' == $group_by_option)
- array_push($fields, 'p.name as project');
- // Add task name if it is selected.
- if ($options['show_task'] || 'task' == $group_by_option)
- array_push($fields, 't.name as task');
- // Add custom field.
- $include_cf_1 = $options['show_custom_field_1'] || 'cf_1' == $group_by_option;
- if ($include_cf_1) {
- $custom_fields = new CustomFields($user->group_id);
- $cf_1_type = $custom_fields->fields[0]['type'];
- if ($cf_1_type == CustomFields::TYPE_TEXT) {
- array_push($fields, 'cfl.value as cf_1');
- } elseif ($cf_1_type == CustomFields::TYPE_DROPDOWN) {
- array_push($fields, 'cfo.value as cf_1');
- }
- }
- // Add start time.
- if ($options['show_start']) {
- array_push($fields, "l.start as unformatted_start");
- array_push($fields, "TIME_FORMAT(l.start, '%k:%i') as start");
- }
- // Add finish time.
- if ($options['show_end'])
- array_push($fields, "TIME_FORMAT(sec_to_time(time_to_sec(l.start) + time_to_sec(l.duration)), '%k:%i') as finish");
- // Add duration.
- if ($options['show_duration'])
- array_push($fields, "TIME_FORMAT(l.duration, '%k:%i') as duration");
- // Add work units.
- if ($options['show_work_units']) {
- if ($user->unit_totals_only)
- array_push($fields, "null as units");
- else
- 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 ($options['show_note'])
- array_push($fields, 'l.comment as note');
- // Handle cost.
- $includeCost = $options['show_cost'];
- if ($includeCost) {
- if (MODE_TIME == $user->tracking_mode)
- array_push($fields, "cast(l.billable * coalesce(u.rate, 0) * time_to_sec(l.duration)/3600 as decimal(10,2)) as cost"); // Use default user rate.
- else
- 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 paid status.
- if ($canViewReports && $options['show_paid'])
- array_push($fields, 'l.paid as 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');
- }
- // Add invoice name if it is selected.
- if (($canViewReports || $isClient) && $options['show_invoice'])
- array_push($fields, 'i.name as invoice');
-
- // Prepare sql query part for left joins.
- $left_joins = null;
- if ($options['show_client'] || 'client' == $group_by_option)
- $left_joins .= " left join tt_clients c on (c.id = l.client_id)";
- if (($canViewReports || $isClient) && $options['show_invoice'])
- $left_joins .= " left join tt_invoices i on (i.id = l.invoice_id and i.status = 1)";
- if ($canViewReports || $isClient || $user->isPluginEnabled('ex'))
- $left_joins .= " left join tt_users u on (u.id = l.user_id)";
- if ($options['show_project'] || 'project' == $group_by_option)
- $left_joins .= " left join tt_projects p on (p.id = l.project_id)";
- if ($options['show_task'] || 'task' == $group_by_option)
- $left_joins .= " left join tt_tasks t on (t.id = l.task_id)";
- if ($include_cf_1) {
- if ($cf_1_type == CustomFields::TYPE_TEXT)
- $left_joins .= " left join tt_custom_field_log cfl on (l.id = cfl.log_id and cfl.status = 1)";
- elseif ($cf_1_type == CustomFields::TYPE_DROPDOWN) {
- $left_joins .= " 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 ($includeCost && MODE_TIME != $user->tracking_mode)
- $left_joins .= " left join tt_user_project_binds upb on (l.user_id = upb.user_id and l.project_id = upb.project_id)";
-
- $where = ttReportHelper::getWhere($options);
-
- // Construct sql query for tt_log items.
- $sql = "select ".join(', ', $fields)." from tt_log l $left_joins $where";
- // If we don't have expense items (such as when the Expenses plugin is desabled), the above is all sql we need,
- // with an exception of sorting part, that is added in the end.
-
- // However, when we have expenses, we need to do a union with a separate query for expense items from tt_expense_items table.
- if ($options['show_cost'] && $user->isPluginEnabled('ex')) { // if ex(penses) plugin is enabled
-
- $fields = array(); // An array of fields for database query.
- array_push($fields, 'ei.id');
- array_push($fields, '2 as type'); // Type 2 is for tt_expense_items entries.
- array_push($fields, 'ei.date');
- if($canViewReports || $isClient)
- array_push($fields, 'u.name as user');
- // Add client name if it is selected.
- if ($options['show_client'] || 'client' == $group_by_option)
- array_push($fields, 'c.name as client');
- // Add project name if it is selected.
- if ($options['show_project'] || 'project' == $group_by_option)
- array_push($fields, 'p.name as project');
- if ($options['show_task'] || 'task' == $group_by_option)
- array_push($fields, 'null'); // null for task name. We need to match column count for union.
- if ($options['show_custom_field_1'] || 'cf_1' == $group_by_option)
- array_push($fields, 'null'); // null for cf_1.
- if ($options['show_start']) {
- array_push($fields, 'null'); // null for unformatted_start.
- array_push($fields, 'null'); // null for start.
- }
- if ($options['show_end'])
- array_push($fields, 'null'); // null for finish.
- if ($options['show_duration'])
- array_push($fields, 'null'); // null for duration.
- if ($options['show_work_units'])
- array_push($fields, 'null as units'); // null for work units.
- // Use the note field to print item name.
- if ($options['show_note'])
- array_push($fields, 'ei.name as note');
- array_push($fields, 'ei.cost as cost');
- array_push($fields, 'ei.cost as expense');
- // Add paid status.
- if ($canViewReports && $options['show_paid'])
- array_push($fields, 'ei.paid as 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');
- }
- // Add invoice name if it is selected.
- if (($canViewReports || $isClient) && $options['show_invoice'])
- array_push($fields, 'i.name as invoice');
-
- // Prepare sql query part for left joins.
- $left_joins = null;
- if ($canViewReports || $isClient)
- $left_joins .= " left join tt_users u on (u.id = ei.user_id)";
- if ($options['show_client'] || 'client' == $group_by_option)
- $left_joins .= " left join tt_clients c on (c.id = ei.client_id)";
- if ($options['show_project'] || 'project' == $group_by_option)
- $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)";
-
- $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";
-
- // Construct a union.
- $sql = "($sql) union all ($sql_for_expense_items)";
- }
-
- // Determine sort part.
- $sort_part = ' order by ';
- if ($group_by_option == null || 'no_grouping' == $group_by_option || 'date' == $group_by_option) // TODO: fix DB for NULL values in group_by field.
- $sort_part .= 'date';
- else
- $sort_part .= $group_by_option.', date';
- if (($canViewReports || $isClient) /*&& is_array($bean->getAttribute('users'))*/ && 'user' != $group_by_option)
- $sort_part .= ', user, type';
- if ($options['show_start'])
- $sort_part .= ', unformatted_start';
- $sort_part .= ', id';
-
- $sql .= $sort_part;
- // By now we are ready with sql.
-
- // Obtain items for report.
- $res = $mdb2->query($sql);
- if (is_a($res, 'PEAR_Error')) die($res->getMessage());
-
- while ($val = $res->fetchRow()) {
- if ($convertTo12Hour) {
- if($val['start'] != '')
- $val['start'] = ttTimeHelper::to12HourFormat($val['start']);
- if($val['finish'] != '')
- $val['finish'] = ttTimeHelper::to12HourFormat($val['finish']);
- }
- if (isset($val['cost'])) {
- if ('.' != $user->decimal_mark)
- $val['cost'] = str_replace('.', $user->decimal_mark, $val['cost']);
- }
- if (isset($val['expense'])) {
- if ('.' != $user->decimal_mark)
- $val['expense'] = str_replace('.', $user->decimal_mark, $val['expense']);
- }
- if ('no_grouping' != $group_by_option) {
- $val['grouped_by'] = $val[$group_by_option];
- if ('date' == $group_by_option) {
- // This is needed to get the date in user date format.
- $o_date = new DateAndTime(DB_DATEFORMAT, $val['grouped_by']);
- $val['grouped_by'] = $o_date->toString($user->date_format);
- unset($o_date);
- }
- }
-
- // This is needed to get the date in user date format.
- $o_date = new DateAndTime(DB_DATEFORMAT, $val['date']);
- $val['date'] = $o_date->toString($user->date_format);
- unset($o_date);
-
- $row = $val;
- $report_items[] = $row;
- }
-
- return $report_items;
- }
-
// getSubtotals calculates report items subtotals when a 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 getSubtotals($bean, $options) {
+ static function getSubtotals($options) {
global $user;
- $group_by_option = $bean->getAttribute('group_by');
+ $group_by_option = $options['group_by'];
if ('no_grouping' == $group_by_option) return null;
$mdb2 = getConnection();
}
$where = ttReportHelper::getWhere($options);
- if ($bean->getAttribute('chcost')) {
+ if ($options['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)';
$sql = "select $group_field as group_field, sum(time_to_sec(l.duration)) as time";
- if ($bean->getAttribute('chunits')) {
+ if ($options['show_work_units']) {
if ($user->unit_totals_only)
$sql .= ", if (sum(l.billable * time_to_sec(l.duration)/60) < $user->first_unit_threshold, 0, ceil(sum(l.billable * time_to_sec(l.duration)/60/$user->minutes_in_unit))) as units";
else
$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
$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";
- if ($bean->getAttribute('chunits')) {
- if ($user->unit_totals_only)
- $sql .= ", if (sum(l.billable * time_to_sec(l.duration)/60) < $user->first_unit_threshold, 0, ceil(sum(l.billable * time_to_sec(l.duration)/60/$user->minutes_in_unit))) as units";
- else
- $sql .= ", 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";
- }
- $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
- $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";
- if ($bean->getAttribute('chunits')) {
- if ($user->unit_totals_only)
- $sql .= ", if (sum(l.billable * time_to_sec(l.duration)/60) < $user->first_unit_threshold, 0, ceil(sum(l.billable * time_to_sec(l.duration)/60/$user->minutes_in_unit))) as units";
- else
- $sql .= ", 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";
- }
- $sql .= ", 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') && $user->isPluginEnabled('ex')) { // 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':
- $group_field = 'ei.date';
- $group_join = '';
- break;
- case 'user':
- $group_field = 'u.name';
- $group_join = 'left join tt_users u on (ei.user_id = u.id) ';
- break;
- case 'client':
- $group_field = 'c.name';
- $group_join = 'left join tt_clients c on (ei.client_id = c.id) ';
- break;
- case 'project':
- $group_field = 'p.name';
- $group_join = 'left join tt_projects p on (ei.project_id = p.id) ';
- break;
- }
-
- $where = ttReportHelper::getExpenseWhere($options);
- $sql_for_expenses = "select $group_field as group_field, null as time";
- if ($bean->getAttribute('chunits')) $sql_for_expenses .= ", null as units";
- $sql_for_expenses .= ", 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.
- $combined = "select group_field, sum(time) as time";
- if ($bean->getAttribute('chunits')) $combined .= ", sum(units) as units";
- $combined .= ", sum(cost) as cost, sum(expenses) as expenses from (($sql) union all ($sql_for_expenses)) t group by group_field";
- $sql = $combined;
- }
-
- // Execute query.
- $res = $mdb2->query($sql);
- 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);
- }
- $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, 'units'=> $val['units'],'cost'=>$val['cost'],'expenses'=>$val['expenses']);
- } else
- $subtotals[$val['group_field']] = array('name'=>$val['group_field'],'time'=>$time, 'units'=> $val['units']);
- }
-
- 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($options) {
- global $user;
-
- $group_by_option = $options['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':
- $group_field = 'l.date';
- $group_join = '';
- break;
- case 'user':
- $group_field = 'u.name';
- $group_join = 'left join tt_users u on (l.user_id = u.id) ';
- break;
- case 'client':
- $group_field = 'c.name';
- $group_join = 'left join tt_clients c on (l.client_id = c.id) ';
- break;
- case 'project':
- $group_field = 'p.name';
- $group_join = 'left join tt_projects p on (l.project_id = p.id) ';
- break;
- case 'task':
- $group_field = 't.name';
- $group_join = 'left join tt_tasks t on (l.task_id = t.id) ';
- break;
- case 'cf_1':
- $group_field = 'cfo.value';
- $custom_fields = new CustomFields($user->group_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;
- }
-
- $where = ttReportHelper::getWhere($options);
- if ($options['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)';
- $sql = "select $group_field as group_field, sum(time_to_sec(l.duration)) as time";
- if ($options['show_work_units']) {
- if ($user->unit_totals_only)
- $sql .= ", if (sum(l.billable * time_to_sec(l.duration)/60) < $user->first_unit_threshold, 0, ceil(sum(l.billable * time_to_sec(l.duration)/60/$user->minutes_in_unit))) as units";
- else
- $sql .= ", 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";
- }
- $sql .= ", 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";
- $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
- $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";
}
// getTotals calculates total hours and cost for all report items.
- static function getTotals($bean, $options)
- {
- global $user;
-
- $mdb2 = getConnection();
-
- $where = ttReportHelper::getWhere($options);
-
- // Prepare parts.
- $time_part = "sum(time_to_sec(l.duration)) as time";
- if ($bean->getAttribute('chunits')) {
- $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";
- }
- if ($bean->getAttribute('chcost')) {
- if (MODE_TIME == $user->tracking_mode)
- $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";
- } else {
- $cost_part = ", null as cost, null as expenses";
- }
- if ($bean->getAttribute('chcost')) {
- if (MODE_TIME == $user->tracking_mode) {
- $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 a query for time items.
- $sql = "select $time_part $units_part $cost_part from tt_log l $left_joins $where";
-
- // If we have expenses, query becomes a bit more complex.
- if ($bean->getAttribute('chcost') && $user->isPluginEnabled('ex')) {
- $where = ttReportHelper::getExpenseWhere($options);
- $sql_for_expenses = "select null as time";
- if ($bean->getAttribute('chunits')) $sql_for_expenses .= ", null as units";
- $sql_for_expenses .= ", sum(cost) as cost, sum(cost) as expenses from tt_expense_items ei $where";
-
- // Create a combined query.
- $combined = "select sum(time) as time";
- if ($bean->getAttribute('chunits')) $combined .= ", sum(units) as units";
- $combined .= ", sum(cost) as cost, sum(expenses) as expenses from (($sql) union all ($sql_for_expenses)) t";
- $sql = $combined;
- }
-
- // Execute query.
- $res = $mdb2->query($sql);
- if (is_a($res, 'PEAR_Error')) die($res->getMessage());
-
- $val = $res->fetchRow();
- $total_time = $val['time'] ? sec_to_time_fmt_hm($val['time']) : null;
- if ($bean->getAttribute('chcost')) {
- $total_cost = $val['cost'];
- if (!$total_cost) $total_cost = '0.00';
- if ('.' != $user->decimal_mark)
- $total_cost = str_replace('.', $user->decimal_mark, $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 ($bean->getAttribute('period'))
- $period = new Period($bean->getAttribute('period'), new DateAndTime($user->date_format));
- else {
- $period = new Period();
- $period->setPeriod(
- new DateAndTime($user->date_format, $bean->getAttribute('start_date')),
- new DateAndTime($user->date_format, $bean->getAttribute('end_date')));
- }
-
- $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;
-
- return $totals;
- }
-
- // getFavTotals calculates total hours and cost for all favorite report items.
- static function getFavTotals($options)
+ static function getTotals($options)
{
global $user;
}
// prepareReportBody - prepares an email body for report.
- static function prepareReportBody($bean, $comment)
+ static function prepareReportBody($bean, $options, $comment)
{
global $user;
global $i18n;
// Determine these once as they are used in multiple places in this function.
- $canViewReports = $user->can('view_reports');
+ $canViewReports = $user->can('view_reports') || $user->can('view_all_reports');
$isClient = $user->isClient();
- $options = ttReportHelper::getReportOptions($bean);
- $items = ttReportHelper::getItems($bean, $options);
- $group_by = $bean->getAttribute('group_by');
+ $items = ttReportHelper::getItems($options);
+ $group_by = $options['group_by'];
if ($group_by && 'no_grouping' != $group_by)
- $subtotals = ttReportHelper::getSubtotals($bean, $options);
- $totals = ttReportHelper::getTotals($bean, $options);
+ $subtotals = ttReportHelper::getSubtotals($options);
+ $totals = ttReportHelper::getTotals($options);
// Use custom fields plugin if it is enabled.
if ($user->isPluginEnabled('cf'))
// Output comment.
if ($comment) $body .= '<p>'.htmlspecialchars($comment).'</p>';
- if ($bean->getAttribute('chtotalsonly')) {
+ if ($options['show_totals_only']) {
// Totals only report. Output subtotals.
// Determine group_by header.
$body .= '<table border="0" cellpadding="4" cellspacing="0" width="100%">';
$body .= '<tr>';
$body .= '<td style="'.$tableHeader.'">'.$group_by_header.'</td>';
- if ($bean->getAttribute('chduration'))
+ if ($options['show_duration'])
$body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.duration').'</td>';
- if ($bean->getAttribute('chunits'))
+ if ($options['show_work_units'])
$body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.work_units_short').'</td>';
- if ($bean->getAttribute('chcost'))
+ if ($options['show_cost'])
$body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.cost').'</td>';
$body .= '</tr>';
foreach($subtotals as $subtotal) {
$body .= '<tr style="'.$rowSubtotal.'">';
$body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.($subtotal['name'] ? htmlspecialchars($subtotal['name']) : ' ').'</td>';
- if ($bean->getAttribute('chduration')) {
+ if ($options['show_duration']) {
$body .= '<td style="'.$cellRightAlignedSubtotal.'">';
if ($subtotal['time'] <> '0:00') $body .= $subtotal['time'];
$body .= '</td>';
}
- if ($bean->getAttribute('chunits')) {
+ if ($options['show_work_units']) {
$body .= '<td style="'.$cellRightAlignedSubtotal.'">';
$body .= $subtotal['units'];
$body .= '</td>';
}
- if ($bean->getAttribute('chcost')) {
+ if ($options['show_cost']) {
$body .= '<td style="'.$cellRightAlignedSubtotal.'">';
$body .= ($canViewReports || $isClient) ? $subtotal['cost'] : $subtotal['expenses'];
$body .= '</td>';
$body .= '</tr>';
}
+// TODO: refactoring ongoing down from here...
+
+
+
+
// Print totals.
$body .= '<tr><td> </td></tr>';
$body .= '<tr style="'.$rowSubtotal.'">';
// checkFavReportCondition - checks whether it is okay to send fav report.
static function checkFavReportCondition($options, $condition)
{
- $items = ttReportHelper::getFavItems($options);
+ $items = ttReportHelper::getItems($options);
$condition = str_replace('count', '', $condition);
$count_required = (int) trim(str_replace('>', '', $condition));
}
// prepareFavReportBody - prepares an email body for a favorite report.
- static function prepareFavReportBody($options)
+ static function prepareFavReportBody($options, $comment = null)
{
global $user;
global $i18n;
// Determine these once as they are used in multiple places in this function.
- $canViewReports = $user->can('view_reports');
+ $canViewReports = $user->can('view_reports') || $user->can('view_all_reports');
$isClient = $user->isClient();
- $items = ttReportHelper::getFavItems($options);
+ $items = ttReportHelper::getItems($options);
$group_by = $options['group_by'];
if ($group_by && 'no_grouping' != $group_by)
- $subtotals = ttReportHelper::getFavSubtotals($options);
- $totals = ttReportHelper::getFavTotals($options);
+ $subtotals = ttReportHelper::getSubtotals($options);
+ $totals = ttReportHelper::getTotals($options);
// Use custom fields plugin if it is enabled.
if ($user->isPluginEnabled('cf'))
$body .= '<p style="'.$style_title.'">'.$i18n->get('form.mail.report_subject').': '.$totals['start_date'].' - '.$totals['end_date'].'</p>';
// Output comment.
- // if ($comment) $body .= '<p>'.htmlspecialchars($comment).'</p>'; // No comment for fav. reports.
+ if ($comment) $body .= '<p>'.htmlspecialchars($comment).'</p>';
if ($options['show_totals_only']) {
// Totals only report. Output subtotals.
$options['show_note'] = $bean->getAttribute('chnote');
$options['show_custom_field_1'] = $bean->getAttribute('chcf_1');
$options['show_work_units'] = $bean->getAttribute('chunits');
-/*
- `show_totals_only` tinyint(4) NOT NULL default 0, # whether to show totals only
-*/
+ $options['show_totals_only'] = $bean->getAttribute('chtotalsonly');
$options['group_by'] = $bean->getAttribute('group_by');
-/*
- * TODO: remaining fields to fill in...
- `status` tinyint(4) default 1, # favorite report status
- PRIMARY KEY (`id`)
-);
-*/
return $options;
}