- // Prepare sql query part for left joins.
- $left_joins = null;
- if ($report['show_client'] || 'client' == $group_by_option)
- $left_joins .= " left join tt_clients c on (c.id = l.client_id)";
- if (($user->canManageTeam() || $user->isClient()) && $report['show_invoice'])
- $left_joins .= " left join tt_invoices i on (i.id = l.invoice_id and i.status = 1)";
- if ($user->canManageTeam() || $user->isClient() || in_array('ex', explode(',', $user->plugins)))
- $left_joins .= " left join tt_users u on (u.id = l.user_id)";
- if ($report['show_project'] || 'project' == $group_by_option)
- $left_joins .= " left join tt_projects p on (p.id = l.project_id)";
- if ($report['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::getFavWhere($report);
-
- // 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 ($report['show_cost'] && in_array('ex', explode(',', $user->plugins))) { // 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($user->canManageTeam() || $user->isClient())
- array_push($fields, 'u.name as user');
- // Add client name if it is selected.
- if ($report['show_client'] || 'client' == $group_by_option)
- array_push($fields, 'c.name as client');
- // Add project name if it is selected.
- if ($report['show_project'] || 'project' == $group_by_option)
- array_push($fields, 'p.name as project');
- if ($report['show_task'] || 'task' == $group_by_option)
- array_push($fields, 'null'); // null for task name. We need to match column count for union.
- if ($report['show_custom_field_1'] || 'cf_1' == $group_by_option)
- array_push($fields, 'null'); // null for cf_1.
- if ($report['show_start']) {
- array_push($fields, 'null'); // null for unformatted_start.
- array_push($fields, 'null'); // null for start.
- }
- if ($report['show_end'])
- array_push($fields, 'null'); // null for finish.
- if ($report['show_duration'])
- array_push($fields, 'null'); // null for duration.
- // Use the note field to print item name.
- if ($report['show_note'])
- array_push($fields, 'ei.name as note');
- array_push($fields, 'ei.cost as cost');
- array_push($fields, 'ei.cost as expense');
- // Add invoice name if it is selected.
- if (($user->canManageTeam() || $user->isClient()) && $report['show_invoice'])
- array_push($fields, 'i.name as invoice');
-
- // Prepare sql query part for left joins.
- $left_joins = null;
- if ($user->canManageTeam() || $user->isClient())
- $left_joins .= " left join tt_users u on (u.id = ei.user_id)";
- if ($report['show_client'] || 'client' == $group_by_option)
- $left_joins .= " left join tt_clients c on (c.id = ei.client_id)";
- if ($report['show_project'] || 'project' == $group_by_option)
- $left_joins .= " left join tt_projects p on (p.id = ei.project_id)";
- if (($user->canManageTeam() || $user->isClient()) && $report['show_invoice'])
- $left_joins .= " left join tt_invoices i on (i.id = ei.invoice_id and i.status = 1)";
-
- $where = ttReportHelper::getFavExpenseWhere($report);
-
- // 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 (($user->canManageTeam() || $user->isClient()) /*&& is_array($bean->getAttribute('users'))*/ && 'user' != $group_by_option)
- $sort_part .= ', user, type';
- if ($report['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;
- }