2 // +----------------------------------------------------------------------+
3 // | Anuko Time Tracker
4 // +----------------------------------------------------------------------+
5 // | Copyright (c) Anuko International Ltd. (https://www.anuko.com)
6 // +----------------------------------------------------------------------+
7 // | LIBERAL FREEWARE LICENSE: This source code document may be used
8 // | by anyone for any purpose, and freely redistributed alone or in
9 // | combination with other software, provided that the license is obeyed.
11 // | There are only two ways to violate the license:
13 // | 1. To redistribute this code in source form, with the copyright
14 // | notice or license removed or altered. (Distributing in compiled
15 // | forms without embedded copyright notices is permitted).
17 // | 2. To redistribute modified versions of this code in *any* form
18 // | that bears insufficient indications that the modifications are
19 // | not the work of the original author(s).
21 // | This license applies to this document only, not any other software
22 // | that it may be combined with.
24 // +----------------------------------------------------------------------+
26 // | https://www.anuko.com/time_tracker/credits.htm
27 // +----------------------------------------------------------------------+
29 import('ttClientHelper');
30 import('DateAndTime');
32 import('ttTimeHelper');
33 import('ttConfigHelper');
35 require_once(dirname(__FILE__).'/../../plugins/CustomFields.class.php');
37 // Definitions of types for timesheet dropdown.
38 define('TIMESHEET_ALL', 0); // Include all records.
39 define('TIMESHEET_NOT_ASSIGNED', 1); // Include records not assigned to timesheets.
40 define('TIMESHEET_ASSIGNED', 2); // Include records assigned to timesheets.
41 define('TIMESHEET_PENDING', 3); // Include records in submitted timesheets that are pending manager approval.
42 define('TIMESHEET_APPROVED', 4); // Include records in approved timesheets.
43 define('TIMESHEET_NOT_APPROVED', 5); // Include records in disapproved timesheets.
45 // Class ttReportHelper is used for help with reports.
46 class ttReportHelper {
48 // getWhere prepares a WHERE clause for a report query.
49 static function getWhere($options) {
52 $group_id = $user->getGroup();
53 $org_id = $user->org_id;
55 // A shortcut for timesheets.
56 if ($options['timesheet_id']) {
57 $where = " where l.timesheet_id = ".$options['timesheet_id']." and l.group_id = $group_id and l.org_id = $org_id";
61 // Prepare dropdown parts.
63 if ($options['client_id'])
64 $dropdown_parts .= ' and l.client_id = '.$options['client_id'];
65 elseif ($user->isClient() && $user->client_id)
66 $dropdown_parts .= ' and l.client_id = '.$user->client_id;
67 if ($options['cf_1_option_id']) $dropdown_parts .= ' and l.id in(select log_id from tt_custom_field_log where status = 1 and option_id = '.$options['cf_1_option_id'].')';
68 if ($options['project_id']) $dropdown_parts .= ' and l.project_id = '.$options['project_id'];
69 if ($options['task_id']) $dropdown_parts .= ' and l.task_id = '.$options['task_id'];
70 if ($options['billable']=='1') $dropdown_parts .= ' and l.billable = 1';
71 if ($options['billable']=='2') $dropdown_parts .= ' and l.billable = 0';
72 if ($options['invoice']=='1') $dropdown_parts .= ' and l.invoice_id is not null';
73 if ($options['invoice']=='2') $dropdown_parts .= ' and l.invoice_id is null';
74 if ($options['timesheet']==TIMESHEET_NOT_ASSIGNED) $dropdown_parts .= ' and l.timesheet_id is null';
75 if ($options['timesheet']==TIMESHEET_ASSIGNED) $dropdown_parts .= ' and l.timesheet_id is not null';
76 if ($options['approved']=='1') $dropdown_parts .= ' and l.approved = 1';
77 if ($options['approved']=='2') $dropdown_parts .= ' and l.approved = 0';
78 if ($options['paid_status']=='1') $dropdown_parts .= ' and l.paid = 1';
79 if ($options['paid_status']=='2') $dropdown_parts .= ' and l.paid = 0';
81 // Prepare sql query part for user list.
82 $userlist = $options['users'] ? $options['users'] : '-1';
83 if ($user->can('view_reports') || $user->can('view_all_reports') || $user->isClient())
84 $user_list_part = " and l.user_id in ($userlist)";
86 $user_list_part = " and l.user_id = ".$user->getUser();
87 $user_list_part .= " and l.group_id = $group_id and l.org_id = $org_id";
89 // Prepare sql query part for where.
90 $dateFormat = $user->getDateFormat();
91 if ($options['period'])
92 $period = new Period($options['period'], new DateAndTime($dateFormat));
94 $period = new Period();
96 new DateAndTime($dateFormat, $options['period_start']),
97 new DateAndTime($dateFormat, $options['period_end']));
99 $where = " where l.status = 1 and l.date >= '".$period->getStartDate(DB_DATEFORMAT)."' and l.date <= '".$period->getEndDate(DB_DATEFORMAT)."'".
100 " $user_list_part $dropdown_parts";
104 // getExpenseWhere prepares WHERE clause for expenses query in a report.
105 static function getExpenseWhere($options) {
108 $group_id = $user->getGroup();
109 $org_id = $user->org_id;
111 // Prepare dropdown parts.
112 $dropdown_parts = '';
113 if ($options['client_id'])
114 $dropdown_parts .= ' and ei.client_id = '.$options['client_id'];
115 elseif ($user->isClient() && $user->client_id)
116 $dropdown_parts .= ' and ei.client_id = '.$user->client_id;
117 if ($options['project_id']) $dropdown_parts .= ' and ei.project_id = '.$options['project_id'];
118 if ($options['invoice']=='1') $dropdown_parts .= ' and ei.invoice_id is not null';
119 if ($options['invoice']=='2') $dropdown_parts .= ' and ei.invoice_id is null';
120 if (isset($options['timesheet']) && ($options['timesheet']!=TIMESHEET_ALL && $options['timesheet']!=TIMESHEET_NOT_ASSIGNED)) {
121 $dropdown_parts .= ' and 0 = 1'; // Expense items do not have a timesheet_id.
123 if ($options['approved']=='1') $dropdown_parts .= ' and ei.approved = 1';
124 if ($options['approved']=='2') $dropdown_parts .= ' and ei.approved = 0';
125 if ($options['paid_status']=='1') $dropdown_parts .= ' and ei.paid = 1';
126 if ($options['paid_status']=='2') $dropdown_parts .= ' and ei.paid = 0';
128 // Prepare sql query part for user list.
129 $userlist = $options['users'] ? $options['users'] : '-1';
130 if ($user->can('view_reports') || $user->can('view_all_reports') || $user->isClient())
131 $user_list_part = " and ei.user_id in ($userlist)";
133 $user_list_part = " and ei.user_id = ".$user->getUser();
134 $user_list_part .= " and ei.group_id = $group_id and ei.org_id = $org_id";
136 // Prepare sql query part for where.
137 $dateFormat = $user->getDateFormat();
138 if ($options['period'])
139 $period = new Period($options['period'], new DateAndTime($dateFormat));
141 $period = new Period();
143 new DateAndTime($dateFormat, $options['period_start']),
144 new DateAndTime($dateFormat, $options['period_end']));
146 $where = " where ei.status = 1 and ei.date >= '".$period->getStartDate(DB_DATEFORMAT)."' and ei.date <= '".$period->getEndDate(DB_DATEFORMAT)."'".
147 " $user_list_part $dropdown_parts";
151 // getItems retrieves all items associated with a report.
152 // It combines tt_log and tt_expense_items in one array for presentation in one table using mysql union all.
153 // Expense items use the "note" field for item name.
154 static function getItems($options) {
156 $mdb2 = getConnection();
158 $group_id = $user->getGroup();
159 $org_id = $user->org_id;
161 // Determine these once as they are used in multiple places in this function.
162 $canViewReports = $user->can('view_reports') || $user->can('view_all_reports');
163 $isClient = $user->isClient();
165 $grouping = ttReportHelper::grouping($options);
167 $grouping_by_date = ttReportHelper::groupingBy('date', $options);
168 $grouping_by_client = ttReportHelper::groupingBy('client', $options);
169 $grouping_by_project = ttReportHelper::groupingBy('project', $options);
170 $grouping_by_task = ttReportHelper::groupingBy('task', $options);
171 $grouping_by_user = ttReportHelper::groupingBy('user', $options);
172 $grouping_by_cf_1 = ttReportHelper::groupingBy('cf_1', $options);
174 $convertTo12Hour = ('%I:%M %p' == $user->getTimeFormat()) && ($options['show_start'] || $options['show_end']);
175 $trackingMode = $user->getTrackingMode();
176 $decimalMark = $user->getDecimalMark();
178 // Prepare a query for time items in tt_log table.
179 $fields = array(); // An array of fields for database query.
180 array_push($fields, 'l.id');
181 array_push($fields, 'l.user_id');
182 array_push($fields, '1 as type'); // Type 1 is for tt_log entries.
183 array_push($fields, 'l.date');
184 if($canViewReports || $isClient)
185 array_push($fields, 'u.name as user');
186 // Add client name if it is selected.
187 if ($options['show_client'] || $grouping_by_client)
188 array_push($fields, 'c.name as client');
189 // Add project name if it is selected.
190 if ($options['show_project'] || $grouping_by_project)
191 array_push($fields, 'p.name as project');
192 // Add task name if it is selected.
193 if ($options['show_task'] || $grouping_by_task)
194 array_push($fields, 't.name as task');
196 $include_cf_1 = $options['show_custom_field_1'] || $grouping_by_cf_1;
198 $custom_fields = new CustomFields();
199 $cf_1_type = $custom_fields->fields[0]['type'];
200 if ($cf_1_type == CustomFields::TYPE_TEXT) {
201 array_push($fields, 'cfl.value as cf_1');
202 } elseif ($cf_1_type == CustomFields::TYPE_DROPDOWN) {
203 array_push($fields, 'cfo.value as cf_1');
207 if ($options['show_start']) {
208 array_push($fields, "l.start as unformatted_start");
209 array_push($fields, "TIME_FORMAT(l.start, '%k:%i') as start");
212 if ($options['show_end'])
213 array_push($fields, "TIME_FORMAT(sec_to_time(time_to_sec(l.start) + time_to_sec(l.duration)), '%k:%i') as finish");
215 if ($options['show_duration'])
216 array_push($fields, "TIME_FORMAT(l.duration, '%k:%i') as duration");
218 if ($options['show_work_units']) {
219 if ($user->getConfigOption('unit_totals_only'))
220 array_push($fields, "null as units");
222 $firstUnitThreshold = $user->getConfigInt('1st_unit_threshold', 0);
223 $minutesInUnit = $user->getConfigInt('minutes_in_unit', 15);
224 array_push($fields, "if(l.billable = 0 or time_to_sec(l.duration)/60 < $firstUnitThreshold, 0, ceil(time_to_sec(l.duration)/60/$minutesInUnit)) as units");
228 if ($options['show_note'])
229 array_push($fields, 'l.comment as note');
231 $includeCost = $options['show_cost'];
233 if (MODE_TIME == $trackingMode)
234 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.
236 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.
237 array_push($fields, "null as expense");
240 if ($options['show_approved'])
241 array_push($fields, 'l.approved');
243 if ($canViewReports && $options['show_paid'])
244 array_push($fields, 'l.paid');
246 if ($canViewReports && $options['show_ip']) {
247 array_push($fields, 'l.created');
248 array_push($fields, 'l.created_ip');
249 array_push($fields, 'l.modified');
250 array_push($fields, 'l.modified_ip');
252 // Add invoice name if it is selected.
253 if (($canViewReports || $isClient) && $options['show_invoice'])
254 array_push($fields, 'i.name as invoice');
255 // Add timesheet name if it is selected.
256 if ($options['show_timesheet'])
257 array_push($fields, 'ts.name as timesheet_name');
259 if ($options['show_files'])
260 array_push($fields, 'if(Sub1.entity_id is null, 0, 1) as has_files');
262 // Prepare sql query part for left joins.
264 if ($options['show_client'] || $grouping_by_client)
265 $left_joins .= " left join tt_clients c on (c.id = l.client_id)";
266 if (($canViewReports || $isClient) && $options['show_invoice'])
267 $left_joins .= " left join tt_invoices i on (i.id = l.invoice_id and i.status = 1)";
268 if ($canViewReports || $isClient || $user->isPluginEnabled('ex'))
269 $left_joins .= " left join tt_users u on (u.id = l.user_id)";
270 if ($options['show_project'] || $grouping_by_project)
271 $left_joins .= " left join tt_projects p on (p.id = l.project_id)";
272 if ($options['show_task'] || $grouping_by_task)
273 $left_joins .= " left join tt_tasks t on (t.id = l.task_id)";
275 if ($cf_1_type == CustomFields::TYPE_TEXT)
276 $left_joins .= " left join tt_custom_field_log cfl on (l.id = cfl.log_id and cfl.status = 1)";
277 elseif ($cf_1_type == CustomFields::TYPE_DROPDOWN) {
278 $left_joins .= " left join tt_custom_field_log cfl on (l.id = cfl.log_id and cfl.status = 1)".
279 " left join tt_custom_field_options cfo on (cfl.option_id = cfo.id)";
282 if ($includeCost && MODE_TIME != $trackingMode)
283 $left_joins .= " left join tt_user_project_binds upb on (l.user_id = upb.user_id and l.project_id = upb.project_id)";
284 if ($options['show_files']) {
285 $left_joins .= " left join (select distinct entity_id from tt_files".
286 " where entity_type = 'time' and group_id = $group_id and org_id = $org_id and status = 1) Sub1".
287 " on (l.id = Sub1.entity_id)";
290 // Prepare sql query part for inner joins.
292 if ($user->isPluginEnabled('ts')) {
293 $timesheet_option = $options['timesheet'];
294 if ($timesheet_option == TIMESHEET_PENDING)
295 $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.submit_status = 1 and ts.approve_status is null)";
296 else if ($timesheet_option == TIMESHEET_APPROVED)
297 $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approve_status = 1)";
298 else if ($timesheet_option == TIMESHEET_NOT_APPROVED)
299 $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approve_status = 0)";
300 else if ($options['show_timesheet'])
301 $inner_joins .= " left join tt_timesheets ts on (l.timesheet_id = ts.id)"; // Left join for timesheet nme.
304 $where = ttReportHelper::getWhere($options);
306 // Construct sql query for tt_log items.
307 $sql = "select ".join(', ', $fields)." from tt_log l $left_joins $inner_joins $where";
308 // If we don't have expense items (such as when the Expenses plugin is disabled), the above is all sql we need,
309 // with an exception of sorting part, that is added in the end.
311 // However, when we have expenses, we need to do a union with a separate query for expense items from tt_expense_items table.
312 if ($options['show_cost'] && $user->isPluginEnabled('ex')) { // if ex(penses) plugin is enabled
314 $fields = array(); // An array of fields for database query.
315 array_push($fields, 'ei.id');
316 array_push($fields, 'ei.user_id');
317 array_push($fields, '2 as type'); // Type 2 is for tt_expense_items entries.
318 array_push($fields, 'ei.date');
319 if($canViewReports || $isClient)
320 array_push($fields, 'u.name as user');
321 // Add client name if it is selected.
322 if ($options['show_client'] || $grouping_by_client)
323 array_push($fields, 'c.name as client');
324 // Add project name if it is selected.
325 if ($options['show_project'] || $grouping_by_project)
326 array_push($fields, 'p.name as project');
327 if ($options['show_task'] || $grouping_by_task)
328 array_push($fields, 'null'); // null for task name. We need to match column count for union.
329 if ($options['show_custom_field_1'] || $grouping_by_cf_1)
330 array_push($fields, 'null'); // null for cf_1.
331 if ($options['show_start']) {
332 array_push($fields, 'null'); // null for unformatted_start.
333 array_push($fields, 'null'); // null for start.
335 if ($options['show_end'])
336 array_push($fields, 'null'); // null for finish.
337 if ($options['show_duration'])
338 array_push($fields, 'null'); // null for duration.
339 if ($options['show_work_units'])
340 array_push($fields, 'null as units'); // null for work units.
341 // Use the note field to print item name.
342 if ($options['show_note'])
343 array_push($fields, 'ei.name as note');
344 array_push($fields, 'ei.cost as cost');
345 array_push($fields, 'ei.cost as expense');
347 if ($options['show_approved'])
348 array_push($fields, 'ei.approved');
350 if ($canViewReports && $options['show_paid'])
351 array_push($fields, 'ei.paid');
353 if ($canViewReports && $options['show_ip']) {
354 array_push($fields, 'ei.created');
355 array_push($fields, 'ei.created_ip');
356 array_push($fields, 'ei.modified');
357 array_push($fields, 'ei.modified_ip');
359 // Add invoice name if it is selected.
360 if (($canViewReports || $isClient) && $options['show_invoice'])
361 array_push($fields, 'i.name as invoice');
362 if ($options['show_timesheet'])
363 array_push($fields, 'null as timesheet_name');
365 if ($options['show_files'])
366 array_push($fields, 'if(Sub1.entity_id is null, 0, 1) as has_files');
368 // Prepare sql query part for left joins.
370 if ($canViewReports || $isClient)
371 $left_joins .= " left join tt_users u on (u.id = ei.user_id)";
372 if ($options['show_client'] || $grouping_by_client)
373 $left_joins .= " left join tt_clients c on (c.id = ei.client_id)";
374 if ($options['show_project'] || $grouping_by_project)
375 $left_joins .= " left join tt_projects p on (p.id = ei.project_id)";
376 if (($canViewReports || $isClient) && $options['show_invoice'])
377 $left_joins .= " left join tt_invoices i on (i.id = ei.invoice_id and i.status = 1)";
378 if ($options['show_files']) {
379 $left_joins .= " left join (select distinct entity_id from tt_files".
380 " where entity_type = 'expense' and group_id = $group_id and org_id = $org_id and status = 1) Sub1".
381 " on (ei.id = Sub1.entity_id)";
384 $where = ttReportHelper::getExpenseWhere($options);
386 // Construct sql query for expense items.
387 $sql_for_expense_items = "select ".join(', ', $fields)." from tt_expense_items ei $left_joins $where";
389 // Construct a union.
390 $sql = "($sql) union all ($sql_for_expense_items)";
393 // Determine sort part.
394 $sort_part = ' order by ';
396 $sort_part2 .= ($options['group_by1'] != null && $options['group_by1'] != 'no_grouping') ? ', '.$options['group_by1'] : '';
397 $sort_part2 .= ($options['group_by2'] != null && $options['group_by2'] != 'no_grouping') ? ', '.$options['group_by2'] : '';
398 $sort_part2 .= ($options['group_by3'] != null && $options['group_by3'] != 'no_grouping') ? ', '.$options['group_by3'] : '';
399 if (!$grouping_by_date) $sort_part2 .= ', date';
400 $sort_part .= ltrim($sort_part2, ', '); // Remove leading comma and space.
402 $sort_part .= 'date';
404 if (($canViewReports || $isClient) && $options['users'] && !$grouping_by_user)
405 $sort_part .= ', user, type';
406 if ($options['show_start'])
407 $sort_part .= ', unformatted_start';
408 $sort_part .= ', id';
411 // By now we are ready with sql.
413 // Obtain items for report.
414 $res = $mdb2->query($sql);
415 if (is_a($res, 'PEAR_Error')) die($res->getMessage());
417 while ($val = $res->fetchRow()) {
418 if ($convertTo12Hour) {
419 if($val['start'] != '')
420 $val['start'] = ttTimeHelper::to12HourFormat($val['start']);
421 if($val['finish'] != '')
422 $val['finish'] = ttTimeHelper::to12HourFormat($val['finish']);
424 if (isset($val['cost'])) {
425 if ('.' != $decimalMark)
426 $val['cost'] = str_replace('.', $decimalMark, $val['cost']);
428 if (isset($val['expense'])) {
429 if ('.' != $decimalMark)
430 $val['expense'] = str_replace('.', $decimalMark, $val['expense']);
433 if ($grouping) $val['grouped_by'] = ttReportHelper::makeGroupByKey($options, $val);
434 $val['date'] = ttDateToUserFormat($val['date']);
436 $report_items[] = $val;
439 return $report_items;
442 // putInSession stores tt_log and tt_expense_items ids from a report in user session
443 // as 2 comma-separated lists.
444 static function putInSession($report_items) {
445 unset($_SESSION['report_item_ids']);
446 unset($_SESSION['report_item_expense_ids']);
448 // Iterate through records and build 2 comma-separated lists.
449 foreach($report_items as $item) {
450 if ($item['type'] == 1)
451 $report_item_ids .= ','.$item['id'];
452 else if ($item['type'] == 2)
453 $report_item_expense_ids .= ','.$item['id'];
455 $report_item_ids = trim($report_item_ids, ',');
456 $report_item_expense_ids = trim($report_item_expense_ids, ',');
458 // The lists are reqdy. Put them in session.
459 if ($report_item_ids) $_SESSION['report_item_ids'] = $report_item_ids;
460 if ($report_item_expense_ids) $_SESSION['report_item_expense_ids'] = $report_item_expense_ids;
463 // getFromSession obtains tt_log and tt_expense_items ids stored in user session.
464 static function getFromSession() {
466 $report_item_ids = $_SESSION['report_item_ids'];
467 if ($report_item_ids)
468 $items['report_item_ids'] = explode(',', $report_item_ids);
469 $report_item_expense_ids = $_SESSION['report_item_expense_ids'];
470 if ($report_item_expense_ids)
471 $items['report_item_expense_ids'] = explode(',', $report_item_expense_ids);
475 // getSubtotals calculates report items subtotals when a report is grouped by.
476 // Without expenses, it's a simple select with group by.
477 // With expenses, it becomes a select with group by from a combined set of records obtained with "union all".
478 static function getSubtotals($options) {
480 $mdb2 = getConnection();
482 $concat_part = ttReportHelper::makeConcatPart($options);
483 $work_unit_part = ttReportHelper::makeWorkUnitPart($options);
484 $join_part = ttReportHelper::makeJoinPart($options);
485 $cost_part = ttReportHelper::makeCostPart($options);
486 $where = ttReportHelper::getWhere($options);
487 $group_by_part = ttReportHelper::makeGroupByPart($options);
489 $parts = "$concat_part, sum(time_to_sec(l.duration)) as time, null as expenses".$work_unit_part.$cost_part;
490 $sql = "select $parts from tt_log l $join_part $where $group_by_part";
491 // By now we have sql for time items.
493 // However, when we have expenses, we need to do a union with a separate query for expense items from tt_expense_items table.
494 if ($options['show_cost'] && $user->isPluginEnabled('ex')) { // if ex(penses) plugin is enabled
496 $concat_part = ttReportHelper::makeConcatExpensesPart($options);
497 $join_part = ttReportHelper::makeJoinExpensesPart($options);
498 $where = ttReportHelper::getExpenseWhere($options);
499 $group_by_expenses_part = ttReportHelper::makeGroupByExpensesPart($options);
500 $sql_for_expenses = "select $concat_part, null as time";
501 if ($options['show_work_units']) $sql_for_expenses .= ", null as units";
502 $sql_for_expenses .= ", sum(ei.cost) as cost, sum(ei.cost) as expenses from tt_expense_items ei $join_part $where $group_by_expenses_part";
504 // Create a combined query.
505 $fields = ttReportHelper::makeCombinedSelectPart($options);
506 $combined = "select $fields, sum(time) as time";
507 if ($options['show_work_units']) $combined .= ", sum(units) as units";
508 $combined .= ", sum(cost) as cost, sum(expenses) as expenses from (($sql) union all ($sql_for_expenses)) t group by $fields";
513 $res = $mdb2->query($sql);
514 if (is_a($res, 'PEAR_Error')) die($res->getMessage());
515 while ($val = $res->fetchRow()) {
516 $time = $val['time'] ? sec_to_time_fmt_hm($val['time']) : null;
517 $rowLabel = ttReportHelper::makeGroupByLabel($val['group_field'], $options);
518 if ($options['show_cost']) {
519 $decimalMark = $user->getDecimalMark();
520 if ('.' != $decimalMark) {
521 $val['cost'] = str_replace('.', $decimalMark, $val['cost']);
522 $val['expenses'] = str_replace('.', $decimalMark, $val['expenses']);
524 $subtotals[$val['group_field']] = array('name'=>$rowLabel,'user'=>$val['user'],'project'=>$val['project'],'task'=>$val['task'],'client'=>$val['client'],'cf_1'=>$val['cf_1'],'time'=>$time,'units'=> $val['units'],'cost'=>$val['cost'],'expenses'=>$val['expenses']);
526 $subtotals[$val['group_field']] = array('name'=>$rowLabel,'user'=>$val['user'],'project'=>$val['project'],'task'=>$val['task'],'client'=>$val['client'],'cf_1'=>$val['cf_1'],'time'=>$time, 'units'=> $val['units']);
532 // getTotals calculates total hours and cost for all report items.
533 static function getTotals($options)
536 $mdb2 = getConnection();
538 $trackingMode = $user->getTrackingMode();
539 $decimalMark = $user->getDecimalMark();
540 $where = ttReportHelper::getWhere($options);
543 $time_part = "sum(time_to_sec(l.duration)) as time";
544 if ($options['show_work_units']) {
545 $unitTotalsOnly = $user->getConfigOption('unit_totals_only');
546 $firstUnitThreshold = $user->getConfigInt('1st_unit_threshold', 0);
547 $minutesInUnit = $user->getConfigInt('minutes_in_unit', 15);
548 $units_part = $unitTotalsOnly ? ", null as units" : ", sum(if(l.billable = 0 or time_to_sec(l.duration)/60 < $firstUnitThreshold, 0, ceil(time_to_sec(l.duration)/60/$minutesInUnit))) as units";
550 if ($options['show_cost']) {
551 if (MODE_TIME == $trackingMode)
552 $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";
554 $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";
556 $cost_part = ", null as cost, null as expenses";
558 if ($options['show_cost']) {
559 if (MODE_TIME == $trackingMode) {
560 $left_joins = "left join tt_users u on (l.user_id = u.id)";
562 $left_joins = "left join tt_user_project_binds upb on (l.user_id = upb.user_id and l.project_id = upb.project_id)";
565 // Prepare sql query part for inner joins.
567 if ($user->isPluginEnabled('ts') && $options['timesheet']) {
568 $timesheet_option = $options['timesheet'];
569 if ($timesheet_option == TIMESHEET_PENDING)
570 $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.submit_status = 1 and ts.approve_status is null)";
571 else if ($timesheet_option == TIMESHEET_APPROVED)
572 $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approve_status = 1)";
573 else if ($timesheet_option == TIMESHEET_NOT_APPROVED)
574 $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approve_status = 0)";
576 // Prepare a query for time items.
577 $sql = "select $time_part $units_part $cost_part from tt_log l $left_joins $inner_joins $where";
579 // If we have expenses, query becomes a bit more complex.
580 if ($options['show_cost'] && $user->isPluginEnabled('ex')) {
581 $where = ttReportHelper::getExpenseWhere($options);
582 $sql_for_expenses = "select null as time";
583 if ($options['show_work_units']) $sql_for_expenses .= ", null as units";
584 $sql_for_expenses .= ", sum(cost) as cost, sum(cost) as expenses from tt_expense_items ei $where";
586 // Create a combined query.
587 $combined = "select sum(time) as time";
588 if ($options['show_work_units']) $combined .= ", sum(units) as units";
589 $combined .= ", sum(cost) as cost, sum(expenses) as expenses from (($sql) union all ($sql_for_expenses)) t";
594 $res = $mdb2->query($sql);
595 if (is_a($res, 'PEAR_Error')) die($res->getMessage());
597 $val = $res->fetchRow();
598 $total_time = $val['time'] ? sec_to_time_fmt_hm($val['time']) : null;
599 if ($options['show_cost']) {
600 $total_cost = $val['cost'];
601 if (!$total_cost) $total_cost = '0.00';
602 if ('.' != $decimalMark)
603 $total_cost = str_replace('.', $decimalMark, $total_cost);
604 $total_expenses = $val['expenses'];
605 if (!$total_expenses) $total_expenses = '0.00';
606 if ('.' != $decimalMark)
607 $total_expenses = str_replace('.', $decimalMark, $total_expenses);
610 $dateFormat = $user->getDateFormat();
611 if ($options['period'])
612 $period = new Period($options['period'], new DateAndTime($dateFormat));
614 $period = new Period();
616 new DateAndTime($dateFormat, $options['period_start']),
617 new DateAndTime($dateFormat, $options['period_end']));
620 $totals['start_date'] = $period->getStartDate();
621 $totals['end_date'] = $period->getEndDate();
622 $totals['time'] = $total_time;
623 $totals['units'] = $val['units'];
624 $totals['cost'] = $total_cost;
625 $totals['expenses'] = $total_expenses;
630 // The assignToInvoice assigns a set of records to a specific invoice.
631 static function assignToInvoice($invoice_id, $time_log_ids, $expense_item_ids) {
633 $mdb2 = getConnection();
635 $group_id = $user->getGroup();
636 $org_id = $user->org_id;
639 $sql = "update tt_log set invoice_id = ".$mdb2->quote($invoice_id).
640 " where id in(".join(', ', $time_log_ids).") and group_id = $group_id and org_id = $org_id";
641 $affected = $mdb2->exec($sql);
642 if (is_a($affected, 'PEAR_Error')) die($affected->getMessage());
644 if ($expense_item_ids) {
645 $sql = "update tt_expense_items set invoice_id = ".$mdb2->quote($invoice_id).
646 " where id in(".join(', ', $expense_item_ids).") and group_id = $group_id and org_id = $org_id";
647 $affected = $mdb2->exec($sql);
648 if (is_a($affected, 'PEAR_Error')) die($affected->getMessage());
652 // The assignToTimesheet assigns a set of tt_log records to a specific timesheet.
653 static function assignToTimesheet($timesheet_id, $time_log_ids) {
655 $mdb2 = getConnection();
657 $user_id = $user->getUser();
658 $group_id = $user->getGroup();
659 $org_id = $user->org_id;
662 // Use inner join as a protection mechanism not to do anything with "acted upon" timesheets.
663 // Allow oprations only with pending timesheets.
665 // Assigning a timesheet to records.
666 $inner_join = " inner join tt_timesheets ts on (ts.id = $timesheet_id".
667 " and ts.user_id = $user_id and ts.approve_status is null". // Timesheet to assign to is pending.
668 // Part below: existing timesheet either not exists or is also pending.
669 " and (l.timesheet_id is null or (l.timesheet_id = ts.id and ts.approve_status is null)))";
671 $inner_join = " inner join tt_timesheets ts on (ts.id = l.timesheet_id".
672 " and ts.user_id = $user_id and ts.approve_status is null)"; // Do not deassign from acted-upon timesheets.
675 $sql = "update tt_log l $inner_join".
676 " set l.timesheet_id = ".$mdb2->quote($timesheet_id).
677 " where l.id in(".join(', ', $time_log_ids).") and l.user_id = $user_id and l.group_id = $group_id and l.org_id = $org_id";
678 $affected = $mdb2->exec($sql);
679 if (is_a($affected, 'PEAR_Error')) die($affected->getMessage());
683 // The markApproved marks a set of records as either approved or unapproved.
684 static function markApproved($time_log_ids, $expense_item_ids, $approved = true) {
686 $mdb2 = getConnection();
688 $group_id = $user->getGroup();
689 $org_id = $user->org_id;
691 $approved_val = (int) $approved;
693 $sql = "update tt_log set approved = $approved_val".
694 " where id in(".join(', ', $time_log_ids).") and group_id = $group_id and org_id = $org_id";
695 $affected = $mdb2->exec($sql);
696 if (is_a($affected, 'PEAR_Error')) die($affected->getMessage());
698 if ($expense_item_ids) {
699 $sql = "update tt_expense_items set approved = $approved_val".
700 " where id in(".join(', ', $expense_item_ids).") and group_id = $group_id and org_id = $org_id";
701 $affected = $mdb2->exec($sql);
702 if (is_a($affected, 'PEAR_Error')) die($affected->getMessage());
706 // The markPaid marks a set of records as either paid or unpaid.
707 static function markPaid($time_log_ids, $expense_item_ids, $paid = true) {
709 $mdb2 = getConnection();
711 $group_id = $user->getGroup();
712 $org_id = $user->org_id;
714 $paid_val = (int) $paid;
716 $sql = "update tt_log set paid = $paid_val".
717 " where id in(".join(', ', $time_log_ids).") and group_id = $group_id and org_id = $org_id";
718 $affected = $mdb2->exec($sql);
719 if (is_a($affected, 'PEAR_Error')) die($affected->getMessage());
721 if ($expense_item_ids) {
722 $sql = "update tt_expense_items set paid = $paid_val".
723 " where id in(".join(', ', $expense_item_ids).") and group_id = $group_id and org_id = $org_id";
724 $affected = $mdb2->exec($sql);
725 if (is_a($affected, 'PEAR_Error')) die($affected->getMessage());
729 // prepareReportBody - prepares an email body for report.
730 static function prepareReportBody($options, $comment = null)
735 // Determine these once as they are used in multiple places in this function.
736 $canViewReports = $user->can('view_reports') || $user->can('view_all_reports');
737 $isClient = $user->isClient();
739 $config = new ttConfigHelper($user->getConfig());
740 $show_note_column = $options['show_note'] && !$config->getDefinedValue('report_note_on_separate_row');
741 $show_note_row = $options['show_note'] && $config->getDefinedValue('report_note_on_separate_row');
743 $items = ttReportHelper::getItems($options);
744 $grouping = ttReportHelper::grouping($options);
746 $subtotals = ttReportHelper::getSubtotals($options);
747 $totals = ttReportHelper::getTotals($options);
749 // Use custom fields plugin if it is enabled.
750 if ($user->isPluginEnabled('cf'))
751 $custom_fields = new CustomFields();
753 // Define some styles to use in email.
754 $style_title = 'text-align: center; font-size: 15pt; font-family: Arial, Helvetica, sans-serif;';
755 $tableHeader = 'font-weight: bold; background-color: #a6ccf7; text-align: left;';
756 $tableHeaderCentered = 'font-weight: bold; background-color: #a6ccf7; text-align: center;';
757 $rowItem = 'background-color: #ffffff;';
758 $rowItemAlt = 'background-color: #f5f5f5;';
759 $rowSubtotal = 'background-color: #e0e0e0;';
760 $cellLeftAligned = 'text-align: left; vertical-align: top;';
761 $cellRightAligned = 'text-align: right; vertical-align: top;';
762 $cellLeftAlignedSubtotal = 'font-weight: bold; text-align: left; vertical-align: top;';
763 $cellRightAlignedSubtotal = 'font-weight: bold; text-align: right; vertical-align: top;';
765 // Determine column span for note field.
767 if ($user->can('view_reports') || $user->can('view_all_reports') || $user->isClient()) $colspan++;
768 if ($options['show_client']) $colspan++;
769 if ($options['show_project']) $colspan++;
770 if ($options['show_task']) $colspan++;
771 if ($options['show_custom_field_1']) $colspan++;
772 if ($options['show_start']) $colspan++;
773 if ($options['show_end']) $colspan++;
774 if ($options['show_duration']) $colspan++;
775 if ($options['show_work_units']) $colspan++;
776 if ($options['show_cost']) $colspan++;
777 if ($options['show_approved']) $colspan++;
778 if ($options['show_paid']) $colspan++;
779 if ($options['show_ip']) $colspan++;
780 if ($options['show_invoice']) $colspan++;
781 if ($options['show_timesheet']) $colspan++;
783 // Start creating email body.
785 $body .= '<head><meta http-equiv="content-type" content="text/html; charset='.CHARSET.'"></head>';
789 $body .= '<p style="'.$style_title.'">'.$i18n->get('form.mail.report_subject').': '.$totals['start_date'].' - '.$totals['end_date'].'</p>';
792 if ($comment) $body .= '<p>'.htmlspecialchars($comment).'</p>';
794 if ($options['show_totals_only']) {
795 // Totals only report. Output subtotals.
796 $group_by_header = ttReportHelper::makeGroupByHeader($options);
798 $body .= '<table border="0" cellpadding="4" cellspacing="0" width="100%">';
800 $body .= '<td style="'.$tableHeader.'">'.$group_by_header.'</td>';
801 if ($options['show_duration'])
802 $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.duration').'</td>';
803 if ($options['show_work_units'])
804 $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.work_units_short').'</td>';
805 if ($options['show_cost'])
806 $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.cost').'</td>';
808 foreach($subtotals as $subtotal) {
809 $body .= '<tr style="'.$rowSubtotal.'">';
810 $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.($subtotal['name'] ? htmlspecialchars($subtotal['name']) : ' ').'</td>';
811 if ($options['show_duration']) {
812 $body .= '<td style="'.$cellRightAlignedSubtotal.'">';
813 if ($subtotal['time'] <> '0:00') $body .= $subtotal['time'];
816 if ($options['show_work_units']) {
817 $body .= '<td style="'.$cellRightAlignedSubtotal.'">';
818 $body .= $subtotal['units'];
821 if ($options['show_cost']) {
822 $body .= '<td style="'.$cellRightAlignedSubtotal.'">';
823 $body .= ($canViewReports || $isClient) ? $subtotal['cost'] : $subtotal['expenses'];
830 $body .= '<tr><td> </td></tr>';
831 $body .= '<tr style="'.$rowSubtotal.'">';
832 $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.$i18n->get('label.total').'</td>';
833 if ($options['show_duration']) {
834 $body .= '<td style="'.$cellRightAlignedSubtotal.'">';
835 if ($totals['time'] <> '0:00') $body .= $totals['time'];
838 if ($options['show_work_units']) {
839 $body .= '<td style="'.$cellRightAlignedSubtotal.'">';
840 $body .= $totals['units'];
843 if ($options['show_cost']) {
844 $body .= '<td nowrap style="'.$cellRightAlignedSubtotal.'">'.htmlspecialchars($user->currency).' ';
845 $body .= ($canViewReports || $isClient) ? $totals['cost'] : $totals['expenses'];
854 // Print table header.
855 $body .= '<table border="0" cellpadding="4" cellspacing="0" width="100%">';
857 $body .= '<td style="'.$tableHeader.'">'.$i18n->get('label.date').'</td>';
858 if ($canViewReports || $isClient)
859 $body .= '<td style="'.$tableHeader.'">'.$i18n->get('label.user').'</td>';
860 if ($options['show_client'])
861 $body .= '<td style="'.$tableHeader.'">'.$i18n->get('label.client').'</td>';
862 if ($options['show_project'])
863 $body .= '<td style="'.$tableHeader.'">'.$i18n->get('label.project').'</td>';
864 if ($options['show_task'])
865 $body .= '<td style="'.$tableHeader.'">'.$i18n->get('label.task').'</td>';
866 if ($options['show_custom_field_1'])
867 $body .= '<td style="'.$tableHeader.'">'.htmlspecialchars($custom_fields->fields[0]['label']).'</td>';
868 if ($options['show_start'])
869 $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.start').'</td>';
870 if ($options['show_end'])
871 $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.finish').'</td>';
872 if ($options['show_duration'])
873 $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.duration').'</td>';
874 if ($options['show_work_units'])
875 $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.work_units_short').'</td>';
876 if ($show_note_column)
877 $body .= '<td style="'.$tableHeader.'">'.$i18n->get('label.note').'</td>';
878 if ($options['show_cost'])
879 $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.cost').'</td>';
880 if ($options['show_approved'])
881 $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.approved').'</td>';
882 if ($options['show_paid'])
883 $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.paid').'</td>';
884 if ($options['show_ip'])
885 $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.ip').'</td>';
886 if ($options['show_invoice'])
887 $body .= '<td style="'.$tableHeader.'">'.$i18n->get('label.invoice').'</td>';
888 if ($options['show_timesheet'])
889 $body .= '<td style="'.$tableHeader.'">'.$i18n->get('label.timesheet').'</td>';
892 // Initialize variables to print subtotals.
893 if ($items && $grouping) {
894 $print_subtotals = true;
896 $prev_grouped_by = '';
897 $cur_grouped_by = '';
899 // Initialize variables to alternate color of rows for different dates.
902 $row_style = $rowItem;
904 // Print report items.
905 if (is_array($items)) {
906 foreach ($items as $record) {
907 $cur_date = $record['date'];
908 // Print a subtotal row after a block of grouped items.
909 if ($print_subtotals) {
910 $cur_grouped_by = $record['grouped_by'];
911 if ($cur_grouped_by != $prev_grouped_by && !$first_pass) {
912 $body .= '<tr style="'.$rowSubtotal.'">';
913 $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.$i18n->get('label.subtotal').'</td>';
914 $subtotal_name = htmlspecialchars($subtotals[$prev_grouped_by]['name']);
915 if ($canViewReports || $isClient) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.$subtotals[$prev_grouped_by]['user'].'</td>';
916 if ($options['show_client']) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.$subtotals[$prev_grouped_by]['client'].'</td>';
917 if ($options['show_project']) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.$subtotals[$prev_grouped_by]['project'].'</td>';
918 if ($options['show_task']) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.$subtotals[$prev_grouped_by]['task'].'</td>';
919 if ($options['show_custom_field_1']) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.$subtotals[$prev_grouped_by]['cf_1'].'</td>';
920 if ($options['show_start']) $body .= '<td></td>';
921 if ($options['show_end']) $body .= '<td></td>';
922 if ($options['show_duration']) $body .= '<td style="'.$cellRightAlignedSubtotal.'">'.$subtotals[$prev_grouped_by]['time'].'</td>';
923 if ($options['show_work_units']) $body .= '<td style="'.$cellRightAlignedSubtotal.'">'.$subtotals[$prev_grouped_by]['units'].'</td>';
924 if ($show_note_column) $body .= '<td></td>';
925 if ($options['show_cost']) {
926 $body .= '<td style="'.$cellRightAlignedSubtotal.'">';
927 $body .= ($canViewReports || $isClient) ? $subtotals[$prev_grouped_by]['cost'] : $subtotals[$prev_grouped_by]['expenses'];
930 if ($options['show_approved']) $body .= '<td></td>';
931 if ($options['show_paid']) $body .= '<td></td>';
932 if ($options['show_ip']) $body .= '<td></td>';
933 if ($options['show_invoice']) $body .= '<td></td>';
934 if ($options['show_timesheet']) $body .= '<td></td>';
936 $body .= '<tr><td> </td></tr>';
941 // Print a regular row.
942 if ($cur_date != $prev_date)
943 $row_style = ($row_style == $rowItem) ? $rowItemAlt : $rowItem;
944 $body .= '<tr style="'.$row_style.'">';
945 $body .= '<td style="'.$cellLeftAligned.'">'.$record['date'].'</td>';
946 if ($canViewReports || $isClient)
947 $body .= '<td style="'.$cellLeftAligned.'">'.htmlspecialchars($record['user']).'</td>';
948 if ($options['show_client'])
949 $body .= '<td style="'.$cellLeftAligned.'">'.htmlspecialchars($record['client']).'</td>';
950 if ($options['show_project'])
951 $body .= '<td style="'.$cellLeftAligned.'">'.htmlspecialchars($record['project']).'</td>';
952 if ($options['show_task'])
953 $body .= '<td style="'.$cellLeftAligned.'">'.htmlspecialchars($record['task']).'</td>';
954 if ($options['show_custom_field_1'])
955 $body .= '<td style="'.$cellLeftAligned.'">'.htmlspecialchars($record['cf_1']).'</td>';
956 if ($options['show_start'])
957 $body .= '<td nowrap style="'.$cellRightAligned.'">'.$record['start'].'</td>';
958 if ($options['show_end'])
959 $body .= '<td nowrap style="'.$cellRightAligned.'">'.$record['finish'].'</td>';
960 if ($options['show_duration'])
961 $body .= '<td style="'.$cellRightAligned.'">'.$record['duration'].'</td>';
962 if ($options['show_work_units'])
963 $body .= '<td style="'.$cellRightAligned.'">'.$record['units'].'</td>';
964 if ($show_note_column)
965 $body .= '<td style="'.$cellLeftAligned.'">'.htmlspecialchars($record['note']).'</td>';
966 if ($options['show_cost'])
967 $body .= '<td style="'.$cellRightAligned.'">'.$record['cost'].'</td>';
968 if ($options['show_approved']) {
969 $body .= '<td style="'.$cellRightAligned.'">';
970 $body .= $record['approved'] == 1 ? $i18n->get('label.yes') : $i18n->get('label.no');
973 if ($options['show_paid']) {
974 $body .= '<td style="'.$cellRightAligned.'">';
975 $body .= $record['paid'] == 1 ? $i18n->get('label.yes') : $i18n->get('label.no');
978 if ($options['show_ip']) {
979 $body .= '<td style="'.$cellRightAligned.'">';
980 $body .= $record['modified'] ? $record['modified_ip'].' '.$record['modified'] : $record['created_ip'].' '.$record['created'];
983 if ($options['show_invoice'])
984 $body .= '<td style="'.$cellRightAligned.'">'.htmlspecialchars($record['invoice']).'</td>';
985 if ($options['show_timesheet'])
986 $body .= '<td style="'.$cellRightAligned.'">'.htmlspecialchars($record['timesheet']).'</td>';
988 if ($show_note_row && $record['note']) {
989 $body .= '<tr style="'.$row_style.'">';
990 $body .= '<td style="'.$cellRightAligned.'">'.$i18n->get('label.note').':</td>';
991 $body .= '<td colspan="'.$colspan.'">'.$record['note'].'</td>';
994 $prev_date = $record['date'];
995 if ($print_subtotals)
996 $prev_grouped_by = $record['grouped_by'];
1000 // Print a terminating subtotal.
1001 if ($print_subtotals) {
1002 $body .= '<tr style="'.$rowSubtotal.'">';
1003 $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.$i18n->get('label.subtotal').'</td>';
1004 $subtotal_name = htmlspecialchars($subtotals[$cur_grouped_by]['name']);
1005 if ($canViewReports || $isClient) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.$subtotals[$prev_grouped_by]['user'].'</td>';
1006 if ($options['show_client']) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.$subtotals[$prev_grouped_by]['client'].'</td>';
1007 if ($options['show_project']) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.$subtotals[$prev_grouped_by]['project'].'</td>';
1008 if ($options['show_task']) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.$subtotals[$prev_grouped_by]['task'].'</td>';
1009 if ($options['show_custom_field_1']) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.$subtotals[$prev_grouped_by]['cf_1'].'</td>';
1010 if ($options['show_start']) $body .= '<td></td>';
1011 if ($options['show_end']) $body .= '<td></td>';
1012 if ($options['show_duration']) $body .= '<td style="'.$cellRightAlignedSubtotal.'">'.$subtotals[$cur_grouped_by]['time'].'</td>';
1013 if ($options['show_work_units']) $body .= '<td style="'.$cellRightAlignedSubtotal.'">'.$subtotals[$cur_grouped_by]['units'].'</td>';
1014 if ($show_note_column) $body .= '<td></td>';
1015 if ($options['show_cost']) {
1016 $body .= '<td style="'.$cellRightAlignedSubtotal.'">';
1017 $body .= ($canViewReports || $isClient) ? $subtotals[$cur_grouped_by]['cost'] : $subtotals[$cur_grouped_by]['expenses'];
1020 if ($options['show_approved']) $body .= '<td></td>';
1021 if ($options['show_paid']) $body .= '<td></td>';
1022 if ($options['show_ip']) $body .= '<td></td>';
1023 if ($options['show_invoice']) $body .= '<td></td>';
1024 if ($options['show_timesheet']) $body .= '<td></td>';
1029 $body .= '<tr><td> </td></tr>';
1030 $body .= '<tr style="'.$rowSubtotal.'">';
1031 $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.$i18n->get('label.total').'</td>';
1032 if ($canViewReports || $isClient) $body .= '<td></td>';
1033 if ($options['show_client']) $body .= '<td></td>';
1034 if ($options['show_project']) $body .= '<td></td>';
1035 if ($options['show_task']) $body .= '<td></td>';
1036 if ($options['show_custom_field_1']) $body .= '<td></td>';
1037 if ($options['show_start']) $body .= '<td></td>';
1038 if ($options['show_end']) $body .= '<td></td>';
1039 if ($options['show_duration']) $body .= '<td style="'.$cellRightAlignedSubtotal.'">'.$totals['time'].'</td>';
1040 if ($options['show_work_units']) $body .= '<td style="'.$cellRightAlignedSubtotal.'">'.$totals['units'].'</td>';
1041 if ($show_note_column) $body .= '<td></td>';
1042 if ($options['show_cost']) {
1043 $body .= '<td nowrap style="'.$cellRightAlignedSubtotal.'">'.htmlspecialchars($user->currency).' ';
1044 $body .= ($canViewReports || $isClient) ? $totals['cost'] : $totals['expenses'];
1047 if ($options['show_approved']) $body .= '<td></td>';
1048 if ($options['show_paid']) $body .= '<td></td>';
1049 if ($options['show_ip']) $body .= '<td></td>';
1050 if ($options['show_invoice']) $body .= '<td></td>';
1051 if ($options['show_timesheet']) $body .= '<td></td>';
1054 $body .= '</table>';
1058 if (!defined('REPORT_FOOTER') || !(REPORT_FOOTER == false))
1059 $body .= '<p style="text-align: center;">'.$i18n->get('form.mail.footer').'</p>';
1061 // Finish creating email body.
1062 $body .= '</body></html>';
1067 // checkFavReportCondition - checks whether it is okay to send fav report.
1068 static function checkFavReportCondition($options, $condition)
1070 $items = ttReportHelper::getItems($options);
1072 $condition = trim(str_replace('count', '', $condition));
1074 $greater_or_equal = ttStartsWith($condition, '>=');
1075 if ($greater_or_equal) $condition = trim(str_replace('>=', '', $condition));
1077 $less_or_equal = ttStartsWith($condition, '<=');
1078 if ($less_or_equal) $condition = trim(str_replace('<=', '', $condition));
1080 $not_equal = ttStartsWith($condition, '<>');
1081 if ($not_equal) $condition = trim(str_replace('<>', '', $condition));
1083 $greater = ttStartsWith($condition, '>');
1084 if ($greater) $condition = trim(str_replace('>', '', $condition));
1086 $less = ttStartsWith($condition, '<');
1087 if ($less) $condition = trim(str_replace('<', '', $condition));
1089 $equal = ttStartsWith($condition, '=');
1090 if ($equal) $condition = trim(str_replace('=', '', $condition));
1092 $count_required = (int) $condition;
1094 if ($greater && count($items) > $count_required) return true;
1095 if ($greater_or_equal && count($items) >= $count_required) return true;
1096 if ($less && count($items) < $count_required) return true;
1097 if ($less_or_equal && count($items) <= $count_required) return true;
1098 if ($equal && count($items) == $count_required) return true;
1099 if ($not_equal && count($items) <> $count_required) return true;
1104 // sendFavReport - sends a favorite report to a specified email, called from cron.php
1105 static function sendFavReport($options, $subject, $email, $cc) {
1106 // We are called from cron.php, we have no $bean in session.
1107 // cron.php sets global $user and $i18n objects to match our favorite report user.
1111 // Prepare report body.
1112 $body = ttReportHelper::prepareReportBody($options);
1114 import('mail.Mailer');
1115 $mailer = new Mailer();
1116 $mailer->setCharSet(CHARSET);
1117 $mailer->setContentType('text/html');
1118 $mailer->setSender(SENDER);
1120 $mailer->setReceiverCC($cc);
1121 if (!empty($user->bcc_email))
1122 $mailer->setReceiverBCC($user->bcc_email);
1123 $mailer->setReceiver($email);
1124 $mailer->setMailMode(MAIL_MODE);
1125 if (empty($subject)) $subject = $options['name'];
1126 if (!$mailer->send($subject, $body))
1132 // getReportOptions - returns an array of report options constructed from session bean.
1134 // Note: similarly to ttFavReportHelper::getReportOptions, this function is a part of
1135 // refactoring to simplify maintenance of report generating functions, as we currently
1136 // have 2 sets: normal reporting (from bean), and fav report emailing (from db fields).
1137 // Using options obtained from either db or bean shall allow us to use only one set of functions.
1138 static function getReportOptions($bean) {
1141 // Prepare an array of report options.
1144 // Construct one by one.
1145 $options['name'] = null; // No name required.
1146 $options['user_id'] = $user->id; // Not sure if we need user_id here. Fav reports use it to recycle $user object in cron.php.
1147 $options['client_id'] = $bean->getAttribute('client');
1148 $options['cf_1_option_id'] = $bean->getAttribute('option');
1149 $options['project_id'] = $bean->getAttribute('project');
1150 $options['task_id'] = $bean->getAttribute('task');
1151 $options['billable'] = $bean->getAttribute('include_records');
1152 $options['invoice'] = $bean->getAttribute('invoice');
1153 $options['paid_status'] = $bean->getAttribute('paid_status');
1154 $options['approved'] = $bean->getAttribute('approved');
1155 if ($user->isPluginEnabled('ap') && $user->isClient() && !$user->can('view_client_unapproved'))
1156 $options['approved'] = 1; // Restrict clients to approved records only.
1157 $options['timesheet'] = $bean->getAttribute('timesheet');
1159 $active_users_in_bean = $bean->getAttribute('users_active');
1160 if ($active_users_in_bean && is_array($active_users_in_bean)) {
1161 $users = join(',', $active_users_in_bean);
1163 $inactive_users_in_bean = $bean->getAttribute('users_inactive');
1164 if ($inactive_users_in_bean && is_array($inactive_users_in_bean)) {
1165 if ($users) $users .= ',';
1166 $users .= join(',', $inactive_users_in_bean);
1168 if ($users) $options['users'] = $users;
1170 $options['period'] = $bean->getAttribute('period');
1171 $options['period_start'] = $bean->getAttribute('start_date');
1172 $options['period_end'] = $bean->getAttribute('end_date');
1173 $options['show_client'] = $bean->getAttribute('chclient');
1174 $options['show_invoice'] = $bean->getAttribute('chinvoice');
1175 $options['show_approved'] = $bean->getAttribute('chapproved');
1176 $options['show_paid'] = $bean->getAttribute('chpaid');
1177 $options['show_ip'] = $bean->getAttribute('chip');
1178 $options['show_project'] = $bean->getAttribute('chproject');
1179 $options['show_start'] = $bean->getAttribute('chstart');
1180 $options['show_duration'] = $bean->getAttribute('chduration');
1181 $options['show_cost'] = $bean->getAttribute('chcost');
1182 $options['show_task'] = $bean->getAttribute('chtask');
1183 $options['show_end'] = $bean->getAttribute('chfinish');
1184 $options['show_note'] = $bean->getAttribute('chnote');
1185 $options['show_custom_field_1'] = $bean->getAttribute('chcf_1');
1186 $options['show_work_units'] = $bean->getAttribute('chunits');
1187 $options['show_timesheet'] = $bean->getAttribute('chtimesheet');
1188 $options['show_files'] = $bean->getAttribute('chfiles');
1189 $options['show_totals_only'] = $bean->getAttribute('chtotalsonly');
1190 $options['group_by1'] = $bean->getAttribute('group_by1');
1191 $options['group_by2'] = $bean->getAttribute('group_by2');
1192 $options['group_by3'] = $bean->getAttribute('group_by3');
1196 // verifyBean is a security function to make sure data in bean makes sense for a group.
1197 static function verifyBean($bean) {
1201 $active_users_in_bean = $bean->getAttribute('users_active');
1202 $inactive_users_in_bean = $bean->getAttribute('users_inactive');
1203 if (is_array($active_users_in_bean) || is_array($inactive_users_in_bean)) {
1204 $users_in_group = ttGroupHelper::getUsers();
1205 foreach ($users_in_group as $user_in_group) {
1206 $valid_ids[] = $user_in_group['id'];
1208 foreach ($active_users_in_bean as $user_in_bean) {
1209 if (!in_array($user_in_bean, $valid_ids)) {
1213 foreach ($inactive_users_in_bean as $user_in_bean) {
1214 if (!in_array($user_in_bean, $valid_ids)) {
1220 // TODO: add additional checks here. Perhaps do it before saving the bean for consistency.
1224 // makeGroupByKey builds a combined group by key from group_by1, group_by2 and group_by3 values
1225 // (passed in $options) and a row of data ($row obtained from a db query).
1226 static function makeGroupByKey($options, $row) {
1227 if ($options['group_by1'] != null && $options['group_by1'] != 'no_grouping') {
1228 // We have group_by1.
1229 $group_by1 = $options['group_by1'];
1230 $group_by1_value = $row[$group_by1];
1231 //if ($group_by1 == 'date') $group_by1_value = ttDateToUserFormat($group_by1_value);
1232 if (empty($group_by1_value)) $group_by1_value = 'Null'; // To match what comes out of makeConcatPart.
1233 $group_by_key .= ' - '.$group_by1_value;
1235 if ($options['group_by2'] != null && $options['group_by2'] != 'no_grouping') {
1236 // We have group_by2.
1237 $group_by2 = $options['group_by2'];
1238 $group_by2_value = $row[$group_by2];
1239 //if ($group_by2 == 'date') $group_by2_value = ttDateToUserFormat($group_by2_value);
1240 if (empty($group_by2_value)) $group_by2_value = 'Null'; // To match what comes out of makeConcatPart.
1241 $group_by_key .= ' - '.$group_by2_value;
1243 if ($options['group_by3'] != null && $options['group_by3'] != 'no_grouping') {
1244 // We have group_by3.
1245 $group_by3 = $options['group_by3'];
1246 $group_by3_value = $row[$group_by3];
1247 //if ($group_by3 == 'date') $group_by3_value = ttDateToUserFormat($group_by3_value);
1248 if (empty($group_by3_value)) $group_by3_value = 'Null'; // To match what comes out of makeConcatPart.
1249 $group_by_key .= ' - '.$group_by3_value;
1251 $group_by_key = trim($group_by_key, ' -');
1252 return $group_by_key;
1255 // makeGroupByPart builds a combined group by part for sql query for time items using group_by1,
1256 // group_by2, and group_by3 values passed in $options.
1257 static function makeGroupByPart($options) {
1258 if (!ttReportHelper::grouping($options)) return null;
1260 $group_by1 = $options['group_by1'];
1261 $group_by2 = $options['group_by2'];
1262 $group_by3 = $options['group_by3'];
1264 switch ($group_by1) {
1266 $group_by_parts .= ', l.date';
1269 $group_by_parts .= ', u.name';
1272 $group_by_parts .= ', c.name';
1275 $group_by_parts .= ', p.name';
1278 $group_by_parts .= ', t.name';
1281 $group_by_parts .= ', cfo.value';
1284 switch ($group_by2) {
1286 $group_by_parts .= ', l.date';
1289 $group_by_parts .= ', u.name';
1292 $group_by_parts .= ', c.name';
1295 $group_by_parts .= ', p.name';
1298 $group_by_parts .= ', t.name';
1301 $group_by_parts .= ', cfo.value';
1304 switch ($group_by3) {
1306 $group_by_parts .= ', l.date';
1309 $group_by_parts .= ', u.name';
1312 $group_by_parts .= ', c.name';
1315 $group_by_parts .= ', p.name';
1318 $group_by_parts .= ', t.name';
1321 $group_by_parts .= ', cfo.value';
1324 // Remove garbage from the beginning.
1325 $group_by_parts = ltrim($group_by_parts, ', ');
1326 $group_by_part = "group by $group_by_parts";
1327 return $group_by_part;
1330 // makeGroupByExpensesPart builds a combined group by part for sql query for expense items using
1331 // group_by1, group_by2, and group_by3 values passed in $options.
1332 static function makeGroupByExpensesPart($options) {
1333 $no_grouping = ($options['group_by1'] == null || $options['group_by1'] == 'no_grouping') &&
1334 ($options['group_by2'] == null || $options['group_by2'] == 'no_grouping') &&
1335 ($options['group_by3'] == null || $options['group_by3'] == 'no_grouping');
1336 if ($no_grouping) return null;
1338 $group_by1 = $options['group_by1'];
1339 $group_by2 = $options['group_by2'];
1340 $group_by3 = $options['group_by3'];
1342 switch ($group_by1) {
1344 $group_by_parts .= ', ei.date';
1347 $group_by_parts .= ', u.name';
1350 $group_by_parts .= ', c.name';
1353 $group_by_parts .= ', p.name';
1356 switch ($group_by2) {
1358 $group_by_parts .= ', ei.date';
1361 $group_by_parts .= ', u.name';
1364 $group_by_parts .= ', c.name';
1367 $group_by_parts .= ', p.name';
1370 switch ($group_by3) {
1372 $group_by_parts .= ', ei.date';
1375 $group_by_parts .= ', u.name';
1378 $group_by_parts .= ', c.name';
1381 $group_by_parts .= ', p.name';
1384 // Remove garbage from the beginning.
1385 $group_by_parts = ltrim($group_by_parts, ', ');
1386 if ($group_by_parts)
1387 $group_by_part = "group by $group_by_parts";
1388 return $group_by_part;
1391 // makeConcatPart builds a concatenation part for getSubtotals query (for time items).
1392 static function makeConcatPart($options) {
1393 $group_by1 = $options['group_by1'];
1394 $group_by2 = $options['group_by2'];
1395 $group_by3 = $options['group_by3'];
1397 switch ($group_by1) {
1399 $what_to_concat .= ", ' - ', l.date";
1402 $what_to_concat .= ", ' - ', u.name";
1403 $fields_part .= ', u.name as user';
1406 $what_to_concat .= ", ' - ', coalesce(c.name, 'Null')";
1407 $fields_part .= ', c.name as client';
1410 $what_to_concat .= ", ' - ', coalesce(p.name, 'Null')";
1411 $fields_part .= ', p.name as project';
1414 $what_to_concat .= ", ' - ', coalesce(t.name, 'Null')";
1415 $fields_part .= ', t.name as task';
1418 $what_to_concat .= ", ' - ', coalesce(cfo.value, 'Null')";
1419 $fields_part .= ', cfo.value as cf_1';
1422 switch ($group_by2) {
1424 $what_to_concat .= ", ' - ', l.date";
1427 $what_to_concat .= ", ' - ', u.name";
1428 $fields_part .= ', u.name as user';
1431 $what_to_concat .= ", ' - ', coalesce(c.name, 'Null')";
1432 $fields_part .= ', c.name as client';
1435 $what_to_concat .= ", ' - ', coalesce(p.name, 'Null')";
1436 $fields_part .= ', p.name as project';
1439 $what_to_concat .= ", ' - ', coalesce(t.name, 'Null')";
1440 $fields_part .= ', t.name as task';
1443 $what_to_concat .= ", ' - ', coalesce(cfo.value, 'Null')";
1444 $fields_part .= ', cfo.value as cf_1';
1447 switch ($group_by3) {
1449 $what_to_concat .= ", ' - ', l.date";
1452 $what_to_concat .= ", ' - ', u.name";
1453 $fields_part .= ', u.name as user';
1456 $what_to_concat .= ", ' - ', coalesce(c.name, 'Null')";
1457 $fields_part .= ', c.name as client';
1460 $what_to_concat .= ", ' - ', coalesce(p.name, 'Null')";
1461 $fields_part .= ', p.name as project';
1464 $what_to_concat .= ", ' - ', coalesce(t.name, 'Null')";
1465 $fields_part .= ', t.name as task';
1468 $what_to_concat .= ", ' - ', coalesce(cfo.value, 'Null')";
1469 $fields_part .= ', cfo.value as cf_1';
1472 // Remove garbage from both ends.
1473 $what_to_concat = trim($what_to_concat, "', -");
1474 $concat_part = "concat($what_to_concat) as group_field";
1475 $concat_part = trim($concat_part, ' -');
1476 return "$concat_part $fields_part";
1479 // makeConcatPart builds a concatenation part for getSubtotals query (for expense items).
1480 static function makeConcatExpensesPart($options) {
1481 $group_by1 = $options['group_by1'];
1482 $group_by2 = $options['group_by2'];
1483 $group_by3 = $options['group_by3'];
1485 switch ($group_by1) {
1487 $what_to_concat .= ", ' - ', ei.date";
1490 $what_to_concat .= ", ' - ', u.name";
1491 $fields_part .= ', u.name as user';
1494 $what_to_concat .= ", ' - ', coalesce(c.name, 'Null')";
1495 $fields_part .= ', c.name as client';
1498 $what_to_concat .= ", ' - ', coalesce(p.name, 'Null')";
1499 $fields_part .= ', p.name as project';
1503 $what_to_concat .= ", ' - ', 'Null'";
1504 $fields_part .= ', null as task';
1508 $what_to_concat .= ", ' - ', 'Null'";
1509 $fields_part .= ', null as cf_1';
1512 switch ($group_by2) {
1514 $what_to_concat .= ", ' - ', ei.date";
1517 $what_to_concat .= ", ' - ', u.name";
1518 $fields_part .= ', u.name as user';
1521 $what_to_concat .= ", ' - ', coalesce(c.name, 'Null')";
1522 $fields_part .= ', c.name as client';
1525 $what_to_concat .= ", ' - ', coalesce(p.name, 'Null')";
1526 $fields_part .= ', p.name as project';
1530 $what_to_concat .= ", ' - ', 'Null'";
1531 $fields_part .= ', null as task';
1535 $what_to_concat .= ", ' - ', 'Null'";
1536 $fields_part .= ', null as cf_1';
1539 switch ($group_by3) {
1541 $what_to_concat .= ", ' - ', ei.date";
1544 $what_to_concat .= ", ' - ', u.name";
1545 $fields_part .= ', u.name as user';
1548 $what_to_concat .= ", ' - ', coalesce(c.name, 'Null')";
1549 $fields_part .= ', c.name as client';
1552 $what_to_concat .= ", ' - ', coalesce(p.name, 'Null')";
1553 $fields_part .= ', p.name as project';
1557 $what_to_concat .= ", ' - ', 'Null'";
1558 $fields_part .= ', null as task';
1562 $what_to_concat .= ", ' - ', 'Null'";
1563 $fields_part .= ', null as cf_1';
1566 // Remove garbage from the beginning.
1567 if ($what_to_concat)
1568 $what_to_concat = substr($what_to_concat, 8);
1569 $concat_part = "concat($what_to_concat) as group_field";
1570 return "$concat_part $fields_part";
1573 // makeCombinedSelectPart builds a list of fields for a combined select on a union for getSubtotals.
1574 // This is used when we include expenses.
1575 static function makeCombinedSelectPart($options) {
1576 $group_by1 = $options['group_by1'];
1577 $group_by2 = $options['group_by2'];
1578 $group_by3 = $options['group_by3'];
1580 $fields = "group_field";
1582 switch ($group_by1) {
1584 $fields .= ', user';
1587 $fields_part .= ', client';
1590 $fields .= ', project';
1594 $fields .= ', task';
1598 $fields .= ', cf_1';
1601 switch ($group_by2) {
1603 $fields .= ', user';
1606 $fields_part .= ', client';
1609 $fields .= ', project';
1613 $fields .= ', task';
1617 $fields .= ', cf_1';
1620 switch ($group_by3) {
1622 $fields .= ', user';
1625 $fields_part .= ', client';
1628 $fields .= ', project';
1632 $fields .= ', task';
1636 $fields .= ', cf_1';
1642 // makeJoinPart builds a left join part for getSubtotals query (for time items).
1643 static function makeJoinPart($options) {
1646 $trackingMode = $user->getTrackingMode();
1647 if (ttReportHelper::groupingBy('user', $options) || MODE_TIME == $trackingMode) {
1648 $join .= ' left join tt_users u on (l.user_id = u.id)';
1650 if (ttReportHelper::groupingBy('client', $options)) {
1651 $join .= ' left join tt_clients c on (l.client_id = c.id)';
1653 if (ttReportHelper::groupingBy('project', $options)) {
1654 $join .= ' left join tt_projects p on (l.project_id = p.id)';
1656 if (ttReportHelper::groupingBy('task', $options)) {
1657 $join .= ' left join tt_tasks t on (l.task_id = t.id)';
1659 if (ttReportHelper::groupingBy('cf_1', $options)) {
1660 $custom_fields = new CustomFields();
1661 if ($custom_fields->fields[0]['type'] == CustomFields::TYPE_TEXT)
1662 $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)';
1663 elseif ($custom_fields->fields[0]['type'] == CustomFields::TYPE_DROPDOWN)
1664 $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)';
1666 if ($options['show_cost'] && $trackingMode != MODE_TIME) {
1667 $join .= ' left join tt_user_project_binds upb on (l.user_id = upb.user_id and l.project_id = upb.project_id)';
1669 // Prepare inner joins.
1670 $inner_joins = null;
1671 if ($user->isPluginEnabled('ts') && $options['timesheet']) {
1672 $timesheet_option = $options['timesheet'];
1673 if ($timesheet_option == TIMESHEET_PENDING)
1674 $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.submit_status = 1 and ts.approve_status is null)";
1675 else if ($timesheet_option == TIMESHEET_APPROVED)
1676 $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approve_status = 1)";
1677 else if ($timesheet_option == TIMESHEET_NOT_APPROVED)
1678 $inner_joins .= " inner join tt_timesheets ts on (l.timesheet_id = ts.id and ts.approve_status = 0)";
1680 $join .= $inner_joins;
1684 // makeWorkUnitPart builds an sql part for work units for time items.
1685 static function makeWorkUnitPart($options) {
1688 $workUnits = $options['show_work_units'];
1690 $unitTotalsOnly = $user->getConfigOption('unit_totals_only');
1691 $firstUnitThreshold = $user->getConfigInt('1st_unit_threshold', 0);
1692 $minutesInUnit = $user->getConfigInt('minutes_in_unit', 15);
1693 if ($unitTotalsOnly)
1694 $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";
1696 $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";
1698 return $work_unit_part;
1701 // makeCostPart builds a cost part for time items.
1702 static function makeCostPart($options) {
1705 if ($options['show_cost']) {
1706 if (MODE_TIME == $user->getTrackingMode())
1707 $cost_part = ", sum(cast(l.billable * coalesce(u.rate, 0) * time_to_sec(l.duration)/3600 as decimal(10, 2))) as cost";
1709 $cost_part .= ", sum(cast(l.billable * coalesce(upb.rate, 0) * time_to_sec(l.duration)/3600 as decimal(10,2))) as cost";
1714 // makeJoinExpensesPart builds a left join part for getSubtotals query for expense items.
1715 static function makeJoinExpensesPart($options) {
1718 if (ttReportHelper::groupingBy('user', $options)) {
1719 $join .= ' left join tt_users u on (ei.user_id = u.id)';
1721 if (ttReportHelper::groupingBy('client', $options)) {
1722 $join .= ' left join tt_clients c on (ei.client_id = c.id)';
1724 if (ttReportHelper::groupingBy('project', $options)) {
1725 $join .= ' left join tt_projects p on (ei.project_id = p.id)';
1730 // grouping determines if we are grouping the report by either group_by1,
1731 // group_by2, or group_by3 values passed in $options.
1732 static function grouping($options) {
1733 $grouping = ($options['group_by1'] != null && $options['group_by1'] != 'no_grouping') ||
1734 ($options['group_by2'] != null && $options['group_by2'] != 'no_grouping') ||
1735 ($options['group_by3'] != null && $options['group_by3'] != 'no_grouping');
1739 // groupingBy determines if we are grouping a report by a value of $what
1740 // ('date', 'user', 'project', etc.) by checking group_by1, group_by2,
1741 // and group_by3 values passed in $options.
1742 static function groupingBy($what, $options) {
1743 $grouping = ($options['group_by1'] == $what) || ($options['group_by2'] == $what) || ($options['group_by3'] == $what);
1747 // makeGroupByHeader builds a column header for a totals-only report using group_by1,
1748 // group_by2, and group_by3 values passed in $options.
1749 static function makeGroupByHeader($options) {
1751 global $custom_fields;
1753 $no_grouping = ($options['group_by1'] == null || $options['group_by1'] == 'no_grouping') &&
1754 ($options['group_by2'] == null || $options['group_by2'] == 'no_grouping') &&
1755 ($options['group_by3'] == null || $options['group_by3'] == 'no_grouping');
1756 if ($no_grouping) return null;
1758 if ($options['group_by1'] != null && $options['group_by1'] != 'no_grouping') {
1759 // We have group_by1.
1760 $group_by1 = $options['group_by1'];
1761 if ('cf_1' == $group_by1)
1762 $group_by_header .= ' - '.$custom_fields->fields[0]['label'];
1764 $key = 'label.'.$group_by1;
1765 $group_by_header .= ' - '.$i18n->get($key);
1768 if ($options['group_by2'] != null && $options['group_by2'] != 'no_grouping') {
1769 // We have group_by2.
1770 $group_by2 = $options['group_by2'];
1771 if ('cf_1' == $group_by2)
1772 $group_by_header .= ' - '.$custom_fields->fields[0]['label'];
1774 $key = 'label.'.$group_by2;
1775 $group_by_header .= ' - '.$i18n->get($key);
1778 if ($options['group_by3'] != null && $options['group_by3'] != 'no_grouping') {
1779 // We have group_by3.
1780 $group_by3 = $options['group_by3'];
1781 if ('cf_1' == $group_by3)
1782 $group_by_header .= ' - '.$custom_fields->fields[0]['label'];
1784 $key = 'label.'.$group_by3;
1785 $group_by_header .= ' - '.$i18n->get($key);
1788 $group_by_header = ltrim($group_by_header, ' -');
1789 return $group_by_header;
1792 // makeGroupByXmlTag creates an xml tag for a totals only report using group_by1,
1793 // group_by2, and group_by3 values passed in $options.
1794 static function makeGroupByXmlTag($options) {
1795 if ($options['group_by1'] != null && $options['group_by1'] != 'no_grouping') {
1796 // We have group_by1.
1797 $tag .= '_'.$options['group_by1'];
1799 if ($options['group_by2'] != null && $options['group_by2'] != 'no_grouping') {
1800 // We have group_by2.
1801 $tag .= '_'.$options['group_by2'];
1803 if ($options['group_by3'] != null && $options['group_by3'] != 'no_grouping') {
1804 // We have group_by3.
1805 $tag .= '_'.$options['group_by3'];
1807 $tag = ltrim($tag, '_');
1811 // makeGroupByLabel builds a label for one row in a "Totals only" report of grouped by items.
1812 // It does one thing: if we are grouping by date, the date format is converted for user.
1813 static function makeGroupByLabel($key, $options) {
1814 if (!ttReportHelper::groupingBy('date', $options))
1815 return $key; // No need to format.
1818 if ($user->getDateFormat() == DB_DATEFORMAT)
1819 return $key; // No need to format.
1822 if (preg_match('/\d\d\d\d-\d\d-\d\d/', $key, $matches)) {
1823 // Replace the first found match of a date in DB_DATEFORMAT.
1824 // This is not entirely clean but better than nothing for a label in a row.
1825 $userDate = ttDateToUserFormat($matches[0]);
1826 $label = str_replace($matches[0], $userDate, $key);