+ // makeConcatPart builds a concatenation part for getSubtotals query (for time items).
+ static function makeConcatPart($options) {
+ $group_by1 = $options['group_by1'];
+ $group_by2 = $options['group_by2'];
+ $group_by3 = $options['group_by3'];
+
+ switch ($group_by1) {
+ case 'date':
+ $what_to_concat .= ", ' - ', l.date";
+ break;
+ case 'user':
+ $what_to_concat .= ", ' - ', u.name";
+ $fields_part .= ', u.name as user';
+ break;
+ case 'client':
+ $what_to_concat .= ", ' - ', coalesce(c.name, 'Null')";
+ $fields_part .= ', c.name as client';
+ break;
+ case 'project':
+ $what_to_concat .= ", ' - ', coalesce(p.name, 'Null')";
+ $fields_part .= ', p.name as project';
+ break;
+ case 'task':
+ $what_to_concat .= ", ' - ', coalesce(t.name, 'Null')";
+ $fields_part .= ', t.name as task';
+ break;
+ case 'cf_1':
+ $what_to_concat .= ", ' - ', coalesce(cfo.value, 'Null')";
+ $fields_part .= ', cfo.value as cf_1';
+ break;
+ }
+ switch ($group_by2) {
+ case 'date':
+ $what_to_concat .= ", ' - ', l.date";
+ break;
+ case 'user':
+ $what_to_concat .= ", ' - ', u.name";
+ $fields_part .= ', u.name as user';
+ break;
+ case 'client':
+ $what_to_concat .= ", ' - ', coalesce(c.name, 'Null')";
+ $fields_part .= ', c.name as client';
+ break;
+ case 'project':
+ $what_to_concat .= ", ' - ', coalesce(p.name, 'Null')";
+ $fields_part .= ', p.name as project';
+ break;
+ case 'task':
+ $what_to_concat .= ", ' - ', coalesce(t.name, 'Null')";
+ $fields_part .= ', t.name as task';
+ break;
+ case 'cf_1':
+ $what_to_concat .= ", ' - ', coalesce(cfo.value, 'Null')";
+ $fields_part .= ', cfo.value as cf_1';
+ break;
+ }
+ switch ($group_by3) {
+ case 'date':
+ $what_to_concat .= ", ' - ', l.date";
+ break;
+ case 'user':
+ $what_to_concat .= ", ' - ', u.name";
+ $fields_part .= ', u.name as user';
+ break;
+ case 'client':
+ $what_to_concat .= ", ' - ', coalesce(c.name, 'Null')";
+ $fields_part .= ', c.name as client';
+ break;
+ case 'project':
+ $what_to_concat .= ", ' - ', coalesce(p.name, 'Null')";
+ $fields_part .= ', p.name as project';
+ break;
+ case 'task':
+ $what_to_concat .= ", ' - ', coalesce(t.name, 'Null')";
+ $fields_part .= ', t.name as task';
+ break;
+ case 'cf_1':
+ $what_to_concat .= ", ' - ', coalesce(cfo.value, 'Null')";
+ $fields_part .= ', cfo.value as cf_1';
+ break;
+ }
+ // Remove garbage from both ends.
+ $what_to_concat = trim($what_to_concat, "', -");
+ $concat_part = "concat($what_to_concat) as group_field";
+ $concat_part = trim($concat_part, ' -');
+ return "$concat_part $fields_part";
+ }
+
+ // makeConcatPart builds a concatenation part for getSubtotals query (for expense items).
+ static function makeConcatExpensesPart($options) {
+ $group_by1 = $options['group_by1'];
+ $group_by2 = $options['group_by2'];
+ $group_by3 = $options['group_by3'];
+
+ switch ($group_by1) {
+ case 'date':
+ $what_to_concat .= ", ' - ', ei.date";
+ break;
+ case 'user':
+ $what_to_concat .= ", ' - ', u.name";
+ $fields_part .= ', u.name as user';
+ break;
+ case 'client':
+ $what_to_concat .= ", ' - ', coalesce(c.name, 'Null')";
+ $fields_part .= ', c.name as client';
+ break;
+ case 'project':
+ $what_to_concat .= ", ' - ', coalesce(p.name, 'Null')";
+ $fields_part .= ', p.name as project';
+ break;
+
+ case 'task':
+ $what_to_concat .= ", ' - ', 'Null'";
+ $fields_part .= ', null as task';
+ break;
+
+ case 'cf_1':
+ $what_to_concat .= ", ' - ', 'Null'";
+ $fields_part .= ', null as cf_1';
+ break;
+ }
+ switch ($group_by2) {
+ case 'date':
+ $what_to_concat .= ", ' - ', ei.date";
+ break;
+ case 'user':
+ $what_to_concat .= ", ' - ', u.name";
+ $fields_part .= ', u.name as user';
+ break;
+ case 'client':
+ $what_to_concat .= ", ' - ', coalesce(c.name, 'Null')";
+ $fields_part .= ', c.name as client';
+ break;
+ case 'project':
+ $what_to_concat .= ", ' - ', coalesce(p.name, 'Null')";
+ $fields_part .= ', p.name as project';
+ break;
+
+ case 'task':
+ $what_to_concat .= ", ' - ', 'Null'";
+ $fields_part .= ', null as task';
+ break;
+
+ case 'cf_1':
+ $what_to_concat .= ", ' - ', 'Null'";
+ $fields_part .= ', null as cf_1';
+ break;
+ }
+ switch ($group_by3) {
+ case 'date':
+ $what_to_concat .= ", ' - ', ei.date";
+ break;
+ case 'user':
+ $what_to_concat .= ", ' - ', u.name";
+ $fields_part .= ', u.name as user';
+ break;
+ case 'client':
+ $what_to_concat .= ", ' - ', coalesce(c.name, 'Null')";
+ $fields_part .= ', c.name as client';
+ break;
+ case 'project':
+ $what_to_concat .= ", ' - ', coalesce(p.name, 'Null')";
+ $fields_part .= ', p.name as project';
+ break;
+
+ case 'task':
+ $what_to_concat .= ", ' - ', 'Null'";
+ $fields_part .= ', null as task';
+ break;
+
+ case 'cf_1':
+ $what_to_concat .= ", ' - ', 'Null'";
+ $fields_part .= ', null as cf_1';
+ break;
+ }
+ // Remove garbage from the beginning.
+ if ($what_to_concat)
+ $what_to_concat = substr($what_to_concat, 8);
+ $concat_part = "concat($what_to_concat) as group_field";
+ return "$concat_part $fields_part";
+ }
+
+ // makeCombinedSelectPart builds a list of fields for a combined select on a union for getSubtotals.
+ // This is used when we include expenses.
+ static function makeCombinedSelectPart($options) {
+ $group_by1 = $options['group_by1'];
+ $group_by2 = $options['group_by2'];
+ $group_by3 = $options['group_by3'];
+
+ $fields = "group_field";
+
+ switch ($group_by1) {
+ case 'user':
+ $fields .= ', user';
+ break;
+ case 'client':
+ $fields_part .= ', client';
+ break;
+ case 'project':
+ $fields .= ', project';
+ break;
+
+ case 'task':
+ $fields .= ', task';
+ break;
+
+ case 'cf_1':
+ $fields .= ', cf_1';
+ break;
+ }
+ switch ($group_by2) {
+ case 'user':
+ $fields .= ', user';
+ break;
+ case 'client':
+ $fields_part .= ', client';
+ break;
+ case 'project':
+ $fields .= ', project';
+ break;
+
+ case 'task':
+ $fields .= ', task';
+ break;
+
+ case 'cf_1':
+ $fields .= ', cf_1';
+ break;
+ }
+ switch ($group_by3) {
+ case 'user':
+ $fields .= ', user';
+ break;
+ case 'client':
+ $fields_part .= ', client';
+ break;
+ case 'project':
+ $fields .= ', project';
+ break;
+
+ case 'task':
+ $fields .= ', task';
+ break;
+
+ case 'cf_1':
+ $fields .= ', cf_1';
+ break;
+ }
+ return $fields;
+ }
+
+ // makeJoinPart builds a left join part for getSubtotals query (for time items).
+ static function makeJoinPart($options) {
+ global $user;
+
+ $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)) {
+ $join .= ' left join tt_clients c on (l.client_id = c.id)';
+ }
+ if (ttReportHelper::groupingBy('project', $options)) {
+ $join .= ' left join tt_projects p on (l.project_id = p.id)';
+ }
+ if (ttReportHelper::groupingBy('task', $options)) {
+ $join .= ' left join tt_tasks t on (l.task_id = t.id)';
+ }
+ if (ttReportHelper::groupingBy('cf_1', $options)) {
+ $custom_fields = new CustomFields();
+ if ($custom_fields->fields[0]['type'] == CustomFields::TYPE_TEXT)
+ $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)
+ $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.approve_status is null)";
+ else if ($timesheet_option == TIMESHEET_APPROVED)
+ $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approve_status = 1)";
+ else if ($timesheet_option == TIMESHEET_NOT_APPROVED)
+ $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approve_status = 0)";
+ }
+ $join .= $inner_joins;
+ return $join;
+ }
+
+ // makeWorkUnitPart builds an sql part for work units for time items.
+ static function makeWorkUnitPart($options) {