+
+ $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) {
+ global $user;
+
+ $workUnits = $options['show_work_units'];
+ if ($workUnits) {
+ $unitTotalsOnly = $user->getConfigOption('unit_totals_only');
+ $firstUnitThreshold = $user->getConfigInt('1st_unit_threshold', 0);
+ $minutesInUnit = $user->getConfigInt('minutes_in_unit', 15);
+ if ($unitTotalsOnly)
+ $work_unit_part = ", if (sum(l.billable * time_to_sec(l.duration)/60) < $firstUnitThreshold, 0, ceil(sum(l.billable * time_to_sec(l.duration)/60/$minutesInUnit))) as units";
+ else
+ $work_unit_part = ", sum(if(l.billable = 0 or time_to_sec(l.duration)/60 < $firstUnitThreshold, 0, ceil(time_to_sec(l.duration)/60/$minutesInUnit))) as units";
+ }
+ return $work_unit_part;
+ }
+
+ // makeCostPart builds a cost part for time items.
+ static function makeCostPart($options) {
+ global $user;
+
+ if ($options['show_cost']) {
+ if (MODE_TIME == $user->getTrackingMode())
+ $cost_part = ", sum(cast(l.billable * coalesce(u.rate, 0) * time_to_sec(l.duration)/3600 as decimal(10, 2))) as cost";
+ else
+ $cost_part .= ", sum(cast(l.billable * coalesce(upb.rate, 0) * time_to_sec(l.duration)/3600 as decimal(10,2))) as cost";
+ }
+ return $cost_part;
+ }
+
+ // makeJoinExpensesPart builds a left join part for getSubtotals query for expense items.
+ static function makeJoinExpensesPart($options) {
+ global $user;
+
+ if (ttReportHelper::groupingBy('user', $options)) {
+ $join .= ' left join tt_users u on (ei.user_id = u.id)';
+ }
+ if (ttReportHelper::groupingBy('client', $options)) {
+ $join .= ' left join tt_clients c on (ei.client_id = c.id)';
+ }
+ if (ttReportHelper::groupingBy('project', $options)) {
+ $join .= ' left join tt_projects p on (ei.project_id = p.id)';
+ }
+ return $join;
+ }
+
+ // grouping determines if we are grouping the report by either group_by1,
+ // group_by2, or group_by3 values passed in $options.
+ static function grouping($options) {
+ $grouping = ($options['group_by1'] != null && $options['group_by1'] != 'no_grouping') ||
+ ($options['group_by2'] != null && $options['group_by2'] != 'no_grouping') ||
+ ($options['group_by3'] != null && $options['group_by3'] != 'no_grouping');
+ return $grouping;
+ }
+
+ // groupingBy determines if we are grouping a report by a value of $what
+ // ('date', 'user', 'project', etc.) by checking group_by1, group_by2,
+ // and group_by3 values passed in $options.
+ static function groupingBy($what, $options) {
+ $grouping = ($options['group_by1'] == $what) || ($options['group_by2'] == $what) || ($options['group_by3'] == $what);
+ return $grouping;
+ }
+
+ // makeGroupByHeader builds a column header for a totals-only report using group_by1,
+ // group_by2, and group_by3 values passed in $options.
+ static function makeGroupByHeader($options) {