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');
34 require_once(dirname(__FILE__).'/../../plugins/CustomFields.class.php');
36 // Class ttReportHelper is used for help with reports.
37 class ttReportHelper {
39 // getWhere prepares a WHERE clause for a report query.
40 static function getWhere($bean) {
43 // Prepare dropdown parts.
45 if ($bean->getAttribute('client'))
46 $dropdown_parts .= ' and l.client_id = '.$bean->getAttribute('client');
47 elseif ($user->isClient() && $user->client_id)
48 $dropdown_parts .= ' and l.client_id = '.$user->client_id;
49 if ($bean->getAttribute('option')) $dropdown_parts .= ' and l.id in(select log_id from tt_custom_field_log where status = 1 and option_id = '.$bean->getAttribute('option').')';
50 if ($bean->getAttribute('project')) $dropdown_parts .= ' and l.project_id = '.$bean->getAttribute('project');
51 if ($bean->getAttribute('task')) $dropdown_parts .= ' and l.task_id = '.$bean->getAttribute('task');
52 if ($bean->getAttribute('include_records')=='1') $dropdown_parts .= ' and l.billable = 1';
53 if ($bean->getAttribute('include_records')=='2') $dropdown_parts .= ' and l.billable = 0';
54 if ($bean->getAttribute('invoice')=='1') $dropdown_parts .= ' and l.invoice_id is not NULL';
55 if ($bean->getAttribute('invoice')=='2') $dropdown_parts .= ' and l.invoice_id is NULL';
56 if ($bean->getAttribute('paid_status')=='1') $dropdown_parts .= ' and l.paid = 1';
57 if ($bean->getAttribute('paid_status')=='2') $dropdown_parts .= ' and l.paid = 0';
59 // Prepare user list part.
61 if (($user->can('view_reports') || $user->isClient()) && is_array($bean->getAttribute('users')))
62 $userlist = join(',', $bean->getAttribute('users'));
63 // Prepare sql query part for user list.
64 $user_list_part = null;
65 if ($user->can('view_reports') || $user->isClient())
66 $user_list_part = " and l.user_id in ($userlist)";
68 $user_list_part = " and l.user_id = ".$user->id;
70 // Prepare sql query part for where.
71 if ($bean->getAttribute('period'))
72 $period = new Period($bean->getAttribute('period'), new DateAndTime($user->date_format));
74 $period = new Period();
76 new DateAndTime($user->date_format, $bean->getAttribute('start_date')),
77 new DateAndTime($user->date_format, $bean->getAttribute('end_date')));
79 $where = " where l.status = 1 and l.date >= '".$period->getStartDate(DB_DATEFORMAT)."' and l.date <= '".$period->getEndDate(DB_DATEFORMAT)."'".
80 " $user_list_part $dropdown_parts";
84 // getFavWhere prepares a WHERE clause for a favorite report query.
85 static function getFavWhere($report) {
88 // Prepare dropdown parts.
90 if ($report['client_id'])
91 $dropdown_parts .= ' and l.client_id = '.$report['client_id'];
92 elseif ($user->isClient() && $user->client_id)
93 $dropdown_parts .= ' and l.client_id = '.$user->client_id;
94 if ($report['cf_1_option_id']) $dropdown_parts .= ' and l.id in(select log_id from tt_custom_field_log where status = 1 and option_id = '.$report['cf_1_option_id'].')';
95 if ($report['project_id']) $dropdown_parts .= ' and l.project_id = '.$report['project_id'];
96 if ($report['task_id']) $dropdown_parts .= ' and l.task_id = '.$report['task_id'];
97 if ($report['billable']=='1') $dropdown_parts .= ' and l.billable = 1';
98 if ($report['billable']=='2') $dropdown_parts .= ' and l.billable = 0';
99 if ($report['invoice']=='1') $dropdown_parts .= ' and l.invoice_id is not NULL';
100 if ($report['invoice']=='2') $dropdown_parts .= ' and l.invoice_id is NULL';
101 if ($report['paid_status']=='1') $dropdown_parts .= ' and l.paid = 1';
102 if ($report['paid_status']=='2') $dropdown_parts .= ' and l.paid = 0';
104 // Prepare user list part.
106 if (($user->can('view_reports') || $user->isClient())) {
107 if ($report['users'])
108 $userlist = $report['users'];
110 $active_users = ttTeamHelper::getActiveUsers();
111 foreach ($active_users as $single_user)
112 $users[] = $single_user['id'];
113 $userlist = join(',', $users);
116 // Prepare sql query part for user list.
117 $user_list_part = null;
118 if ($user->can('view_reports') || $user->isClient())
119 $user_list_part = " and l.user_id in ($userlist)";
121 $user_list_part = " and l.user_id = ".$user->id;
123 // Prepare sql query part for where.
124 if ($report['period'])
125 $period = new Period($report['period'], new DateAndTime($user->date_format));
127 $period = new Period();
129 new DateAndTime($user->date_format, $report['period_start']),
130 new DateAndTime($user->date_format, $report['period_end']));
132 $where = " where l.status = 1 and l.date >= '".$period->getStartDate(DB_DATEFORMAT)."' and l.date <= '".$period->getEndDate(DB_DATEFORMAT)."'".
133 " $user_list_part $dropdown_parts";
137 // getExpenseWhere prepares WHERE clause for expenses query in a report.
138 static function getExpenseWhere($bean) {
141 // Prepare dropdown parts.
142 $dropdown_parts = '';
143 if ($bean->getAttribute('client'))
144 $dropdown_parts .= ' and ei.client_id = '.$bean->getAttribute('client');
145 elseif ($user->isClient() && $user->client_id)
146 $dropdown_parts .= ' and ei.client_id = '.$user->client_id;
147 if ($bean->getAttribute('project')) $dropdown_parts .= ' and ei.project_id = '.$bean->getAttribute('project');
148 if ($bean->getAttribute('invoice')=='1') $dropdown_parts .= ' and ei.invoice_id is not NULL';
149 if ($bean->getAttribute('invoice')=='2') $dropdown_parts .= ' and ei.invoice_id is NULL';
150 if ($bean->getAttribute('paid_status')=='1') $dropdown_parts .= ' and ei.paid = 1';
151 if ($bean->getAttribute('paid_status')=='2') $dropdown_parts .= ' and ei.paid = 0';
153 // Prepare user list part.
155 if (($user->can('view_reports') || $user->isClient()) && is_array($bean->getAttribute('users')))
156 $userlist = join(',', $bean->getAttribute('users'));
157 // Prepare sql query part for user list.
158 $user_list_part = null;
159 if ($user->can('view_reports') || $user->isClient())
160 $user_list_part = " and ei.user_id in ($userlist)";
162 $user_list_part = " and ei.user_id = ".$user->id;
164 // Prepare sql query part for where.
165 if ($bean->getAttribute('period'))
166 $period = new Period($bean->getAttribute('period'), new DateAndTime($user->date_format));
168 $period = new Period();
170 new DateAndTime($user->date_format, $bean->getAttribute('start_date')),
171 new DateAndTime($user->date_format, $bean->getAttribute('end_date')));
173 $where = " where ei.status = 1 and ei.date >= '".$period->getStartDate(DB_DATEFORMAT)."' and ei.date <= '".$period->getEndDate(DB_DATEFORMAT)."'".
174 " $user_list_part $dropdown_parts";
178 // getFavExpenseWhere prepares a WHERE clause for expenses query in a favorite report.
179 static function getFavExpenseWhere($report) {
182 // Prepare dropdown parts.
183 $dropdown_parts = '';
184 if ($report['client_id'])
185 $dropdown_parts .= ' and ei.client_id = '.$report['client_id'];
186 elseif ($user->isClient() && $user->client_id)
187 $dropdown_parts .= ' and ei.client_id = '.$user->client_id;
188 if ($report['project_id']) $dropdown_parts .= ' and ei.project_id = '.$report['project_id'];
189 if ($report['invoice']=='1') $dropdown_parts .= ' and ei.invoice_id is not NULL';
190 if ($report['invoice']=='2') $dropdown_parts .= ' and ei.invoice_id is NULL';
191 if ($report['paid_status']=='1') $dropdown_parts .= ' and ei.paid = 1';
192 if ($report['paid_status']=='2') $dropdown_parts .= ' and ei.paid = 0';
194 // Prepare user list part.
196 if (($user->can('view_reports') || $user->isClient())) {
197 if ($report['users'])
198 $userlist = $report['users'];
200 $active_users = ttTeamHelper::getActiveUsers();
201 foreach ($active_users as $single_user)
202 $users[] = $single_user['id'];
203 $userlist = join(',', $users);
206 // Prepare sql query part for user list.
207 $user_list_part = null;
208 if ($user->can('view_reports') || $user->isClient())
209 $user_list_part = " and ei.user_id in ($userlist)";
211 $user_list_part = " and ei.user_id = ".$user->id;
213 // Prepare sql query part for where.
214 if ($report['period'])
215 $period = new Period($report['period'], new DateAndTime($user->date_format));
217 $period = new Period();
219 new DateAndTime($user->date_format, $report['period_start']),
220 new DateAndTime($user->date_format, $report['period_end']));
222 $where = " where ei.status = 1 and ei.date >= '".$period->getStartDate(DB_DATEFORMAT)."' and ei.date <= '".$period->getEndDate(DB_DATEFORMAT)."'".
223 " $user_list_part $dropdown_parts";
227 // getItems retrieves all items associated with a report.
228 // It combines tt_log and tt_expense_items in one array for presentation in one table using mysql union all.
229 // Expense items use the "note" field for item name.
230 static function getItems($bean) {
232 $mdb2 = getConnection();
234 // Determine these once as they are used in multiple places in this function.
235 $canViewReports = $user->can('view_reports');
236 $isClient = $user->isClient();
238 $group_by_option = $bean->getAttribute('group_by');
239 $convertTo12Hour = ('%I:%M %p' == $user->time_format) && ($bean->getAttribute('chstart') || $bean->getAttribute('chfinish'));
241 // Prepare a query for time items in tt_log table.
242 $fields = array(); // An array of fields for database query.
243 array_push($fields, 'l.id as id');
244 array_push($fields, '1 as type'); // Type 1 is for tt_log entries.
245 array_push($fields, 'l.date as date');
246 if($canViewReports || $isClient)
247 array_push($fields, 'u.name as user');
248 // Add client name if it is selected.
249 if ($bean->getAttribute('chclient') || 'client' == $group_by_option)
250 array_push($fields, 'c.name as client');
251 // Add project name if it is selected.
252 if ($bean->getAttribute('chproject') || 'project' == $group_by_option)
253 array_push($fields, 'p.name as project');
254 // Add task name if it is selected.
255 if ($bean->getAttribute('chtask') || 'task' == $group_by_option)
256 array_push($fields, 't.name as task');
258 $include_cf_1 = $bean->getAttribute('chcf_1') || 'cf_1' == $group_by_option;
260 $custom_fields = new CustomFields($user->group_id);
261 $cf_1_type = $custom_fields->fields[0]['type'];
262 if ($cf_1_type == CustomFields::TYPE_TEXT) {
263 array_push($fields, 'cfl.value as cf_1');
264 } elseif ($cf_1_type == CustomFields::TYPE_DROPDOWN) {
265 array_push($fields, 'cfo.value as cf_1');
269 if ($bean->getAttribute('chstart')) {
270 array_push($fields, "l.start as unformatted_start");
271 array_push($fields, "TIME_FORMAT(l.start, '%k:%i') as start");
274 if ($bean->getAttribute('chfinish'))
275 array_push($fields, "TIME_FORMAT(sec_to_time(time_to_sec(l.start) + time_to_sec(l.duration)), '%k:%i') as finish");
277 if ($bean->getAttribute('chduration'))
278 array_push($fields, "TIME_FORMAT(l.duration, '%k:%i') as duration");
280 if ($bean->getAttribute('chunits')) {
281 if ($user->unit_totals_only)
282 array_push($fields, "null as units");
284 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");
287 if ($bean->getAttribute('chnote'))
288 array_push($fields, 'l.comment as note');
290 $includeCost = $bean->getAttribute('chcost');
292 if (MODE_TIME == $user->tracking_mode)
293 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.
295 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.
296 array_push($fields, "null as expense");
299 if ($canViewReports && $bean->getAttribute('chpaid'))
300 array_push($fields, 'l.paid as paid');
302 if ($canViewReports && $bean->getAttribute('chip')) {
303 array_push($fields, 'l.created as created');
304 array_push($fields, 'l.created_ip as created_ip');
305 array_push($fields, 'l.modified as modified');
306 array_push($fields, 'l.modified_ip as modified_ip');
309 // Add invoice name if it is selected.
310 if (($canViewReports || $isClient) && $bean->getAttribute('chinvoice'))
311 array_push($fields, 'i.name as invoice');
313 // Prepare sql query part for left joins.
315 if ($bean->getAttribute('chclient') || 'client' == $group_by_option)
316 $left_joins .= " left join tt_clients c on (c.id = l.client_id)";
317 if (($canViewReports || $isClient) && $bean->getAttribute('chinvoice'))
318 $left_joins .= " left join tt_invoices i on (i.id = l.invoice_id and i.status = 1)";
319 if ($canViewReports || $isClient || $user->isPluginEnabled('ex'))
320 $left_joins .= " left join tt_users u on (u.id = l.user_id)";
321 if ($bean->getAttribute('chproject') || 'project' == $group_by_option)
322 $left_joins .= " left join tt_projects p on (p.id = l.project_id)";
323 if ($bean->getAttribute('chtask') || 'task' == $group_by_option)
324 $left_joins .= " left join tt_tasks t on (t.id = l.task_id)";
326 if ($cf_1_type == CustomFields::TYPE_TEXT)
327 $left_joins .= " left join tt_custom_field_log cfl on (l.id = cfl.log_id and cfl.status = 1)";
328 elseif ($cf_1_type == CustomFields::TYPE_DROPDOWN) {
329 $left_joins .= " left join tt_custom_field_log cfl on (l.id = cfl.log_id and cfl.status = 1)".
330 " left join tt_custom_field_options cfo on (cfl.option_id = cfo.id)";
333 if ($includeCost && MODE_TIME != $user->tracking_mode)
334 $left_joins .= " left join tt_user_project_binds upb on (l.user_id = upb.user_id and l.project_id = upb.project_id)";
336 $where = ttReportHelper::getWhere($bean);
338 // Construct sql query for tt_log items.
339 $sql = "select ".join(', ', $fields)." from tt_log l $left_joins $where";
340 // If we don't have expense items (such as when the Expenses plugin is desabled), the above is all sql we need,
341 // with an exception of sorting part, that is added in the end.
343 // However, when we have expenses, we need to do a union with a separate query for expense items from tt_expense_items table.
344 if ($bean->getAttribute('chcost') && $user->isPluginEnabled('ex')) { // if ex(penses) plugin is enabled
346 $fields = array(); // An array of fields for database query.
347 array_push($fields, 'ei.id');
348 array_push($fields, '2 as type'); // Type 2 is for tt_expense_items entries.
349 array_push($fields, 'ei.date');
350 if($canViewReports || $isClient)
351 array_push($fields, 'u.name as user');
352 // Add client name if it is selected.
353 if ($bean->getAttribute('chclient') || 'client' == $group_by_option)
354 array_push($fields, 'c.name as client');
355 // Add project name if it is selected.
356 if ($bean->getAttribute('chproject') || 'project' == $group_by_option)
357 array_push($fields, 'p.name as project');
358 if ($bean->getAttribute('chtask') || 'task' == $group_by_option)
359 array_push($fields, 'null'); // null for task name. We need to match column count for union.
360 if ($bean->getAttribute('chcf_1') || 'cf_1' == $group_by_option)
361 array_push($fields, 'null'); // null for cf_1.
362 if ($bean->getAttribute('chstart')) {
363 array_push($fields, 'null'); // null for unformatted_start.
364 array_push($fields, 'null'); // null for start.
366 if ($bean->getAttribute('chfinish'))
367 array_push($fields, 'null'); // null for finish.
368 if ($bean->getAttribute('chduration'))
369 array_push($fields, 'null'); // null for duration.
371 if ($bean->getAttribute('chunits'))
372 array_push($fields, 'null as units'); // null for work units.
373 // Use the note field to print item name.
374 if ($bean->getAttribute('chnote'))
375 array_push($fields, 'ei.name as note');
376 array_push($fields, 'ei.cost as cost');
377 array_push($fields, 'ei.cost as expense');
379 if ($canViewReports && $bean->getAttribute('chpaid'))
380 array_push($fields, 'ei.paid as paid');
382 if ($canViewReports && $bean->getAttribute('chip')) {
383 array_push($fields, 'ei.created as created');
384 array_push($fields, 'ei.created_ip as created_ip');
385 array_push($fields, 'ei.modified as modified');
386 array_push($fields, 'ei.modified_ip as modified_ip');
389 // Add invoice name if it is selected.
390 if (($canViewReports || $isClient) && $bean->getAttribute('chinvoice'))
391 array_push($fields, 'i.name as invoice');
393 // Prepare sql query part for left joins.
395 if ($canViewReports || $isClient)
396 $left_joins .= " left join tt_users u on (u.id = ei.user_id)";
397 if ($bean->getAttribute('chclient') || 'client' == $group_by_option)
398 $left_joins .= " left join tt_clients c on (c.id = ei.client_id)";
399 if ($bean->getAttribute('chproject') || 'project' == $group_by_option)
400 $left_joins .= " left join tt_projects p on (p.id = ei.project_id)";
401 if (($canViewReports || $isClient) && $bean->getAttribute('chinvoice'))
402 $left_joins .= " left join tt_invoices i on (i.id = ei.invoice_id and i.status = 1)";
404 $where = ttReportHelper::getExpenseWhere($bean);
406 // Construct sql query for expense items.
407 $sql_for_expense_items = "select ".join(', ', $fields)." from tt_expense_items ei $left_joins $where";
409 // Construct a union.
410 $sql = "($sql) union all ($sql_for_expense_items)";
413 // Determine sort part.
414 $sort_part = ' order by ';
415 if ('no_grouping' == $group_by_option || 'date' == $group_by_option)
416 $sort_part .= 'date';
418 $sort_part .= $group_by_option.', date';
419 if (($canViewReports || $isClient) && is_array($bean->getAttribute('users')) && 'user' != $group_by_option)
420 $sort_part .= ', user, type';
421 if ($bean->getAttribute('chstart'))
422 $sort_part .= ', unformatted_start';
423 $sort_part .= ', id';
426 // By now we are ready with sql.
428 // Obtain items for report.
429 $res = $mdb2->query($sql);
430 if (is_a($res, 'PEAR_Error')) die($res->getMessage());
432 while ($val = $res->fetchRow()) {
433 if ($convertTo12Hour) {
434 if($val['start'] != '')
435 $val['start'] = ttTimeHelper::to12HourFormat($val['start']);
436 if($val['finish'] != '')
437 $val['finish'] = ttTimeHelper::to12HourFormat($val['finish']);
439 if (isset($val['cost'])) {
440 if ('.' != $user->decimal_mark)
441 $val['cost'] = str_replace('.', $user->decimal_mark, $val['cost']);
443 if (isset($val['expense'])) {
444 if ('.' != $user->decimal_mark)
445 $val['expense'] = str_replace('.', $user->decimal_mark, $val['expense']);
447 if ('no_grouping' != $group_by_option) {
448 $val['grouped_by'] = $val[$group_by_option];
449 if ('date' == $group_by_option) {
450 // This is needed to get the date in user date format.
451 $o_date = new DateAndTime(DB_DATEFORMAT, $val['grouped_by']);
452 $val['grouped_by'] = $o_date->toString($user->date_format);
457 // This is needed to get the date in user date format.
458 $o_date = new DateAndTime(DB_DATEFORMAT, $val['date']);
459 $val['date'] = $o_date->toString($user->date_format);
463 $report_items[] = $row;
466 return $report_items;
469 // putInSession stores tt_log and tt_expense_items ids from a report in user session
470 // as 2 comma-separated lists.
471 static function putInSession($report_items) {
472 unset($_SESSION['report_item_ids']);
473 unset($_SESSION['report_item_expense_ids']);
475 // Iterate through records and build 2 comma-separated lists.
476 foreach($report_items as $item) {
477 if ($item['type'] == 1)
478 $report_item_ids .= ','.$item['id'];
479 else if ($item['type'] == 2)
480 $report_item_expense_ids .= ','.$item['id'];
482 $report_item_ids = trim($report_item_ids, ',');
483 $report_item_expense_ids = trim($report_item_expense_ids, ',');
485 // The lists are reqdy. Put them in session.
486 if ($report_item_ids) $_SESSION['report_item_ids'] = $report_item_ids;
487 if ($report_item_expense_ids) $_SESSION['report_item_expense_ids'] = $report_item_expense_ids;
490 // getFromSession obtains tt_log and tt_expense_items ids stored in user session.
491 static function getFromSession() {
493 $report_item_ids = $_SESSION['report_item_ids'];
494 if ($report_item_ids)
495 $items['report_item_ids'] = explode(',', $report_item_ids);
496 $report_item_expense_ids = $_SESSION['report_item_expense_ids'];
497 if ($report_item_expense_ids)
498 $items['report_item_expense_ids'] = explode(',', $report_item_expense_ids);
502 // getFavItems retrieves all items associated with a favorite report.
503 // It combines tt_log and tt_expense_items in one array for presentation in one table using mysql union all.
504 // Expense items use the "note" field for item name.
505 static function getFavItems($report) {
507 $mdb2 = getConnection();
509 // Determine these once as they are used in multiple places in this function.
510 $canViewReports = $user->can('view_reports');
511 $isClient = $user->isClient();
513 $group_by_option = $report['group_by'];
514 $convertTo12Hour = ('%I:%M %p' == $user->time_format) && ($report['show_start'] || $report['show_end']);
516 // Prepare a query for time items in tt_log table.
517 $fields = array(); // An array of fields for database query.
518 array_push($fields, 'l.id as id');
519 array_push($fields, '1 as type'); // Type 1 is for tt_log entries.
520 array_push($fields, 'l.date as date');
521 if($canViewReports || $isClient)
522 array_push($fields, 'u.name as user');
523 // Add client name if it is selected.
524 if ($report['show_client'] || 'client' == $group_by_option)
525 array_push($fields, 'c.name as client');
526 // Add project name if it is selected.
527 if ($report['show_project'] || 'project' == $group_by_option)
528 array_push($fields, 'p.name as project');
529 // Add task name if it is selected.
530 if ($report['show_task'] || 'task' == $group_by_option)
531 array_push($fields, 't.name as task');
533 $include_cf_1 = $report['show_custom_field_1'] || 'cf_1' == $group_by_option;
535 $custom_fields = new CustomFields($user->group_id);
536 $cf_1_type = $custom_fields->fields[0]['type'];
537 if ($cf_1_type == CustomFields::TYPE_TEXT) {
538 array_push($fields, 'cfl.value as cf_1');
539 } elseif ($cf_1_type == CustomFields::TYPE_DROPDOWN) {
540 array_push($fields, 'cfo.value as cf_1');
544 if ($report['show_start']) {
545 array_push($fields, "l.start as unformatted_start");
546 array_push($fields, "TIME_FORMAT(l.start, '%k:%i') as start");
549 if ($report['show_end'])
550 array_push($fields, "TIME_FORMAT(sec_to_time(time_to_sec(l.start) + time_to_sec(l.duration)), '%k:%i') as finish");
552 if ($report['show_duration'])
553 array_push($fields, "TIME_FORMAT(l.duration, '%k:%i') as duration");
555 if ($report['show_work_units']) {
556 if ($user->unit_totals_only)
557 array_push($fields, "null as units");
559 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");
563 if ($report['show_note'])
564 array_push($fields, 'l.comment as note');
566 $includeCost = $report['show_cost'];
568 if (MODE_TIME == $user->tracking_mode)
569 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.
571 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.
572 array_push($fields, "null as expense");
575 if ($canViewReports && $report['show_paid'])
576 array_push($fields, 'l.paid as paid');
578 if ($canViewReports && $report['show_ip']) {
579 array_push($fields, 'l.created as created');
580 array_push($fields, 'l.created_ip as created_ip');
581 array_push($fields, 'l.modified as modified');
582 array_push($fields, 'l.modified_ip as modified_ip');
584 // Add invoice name if it is selected.
585 if (($canViewReports || $isClient) && $report['show_invoice'])
586 array_push($fields, 'i.name as invoice');
588 // Prepare sql query part for left joins.
590 if ($report['show_client'] || 'client' == $group_by_option)
591 $left_joins .= " left join tt_clients c on (c.id = l.client_id)";
592 if (($canViewReports || $isClient) && $report['show_invoice'])
593 $left_joins .= " left join tt_invoices i on (i.id = l.invoice_id and i.status = 1)";
594 if ($canViewReports || $isClient || $user->isPluginEnabled('ex'))
595 $left_joins .= " left join tt_users u on (u.id = l.user_id)";
596 if ($report['show_project'] || 'project' == $group_by_option)
597 $left_joins .= " left join tt_projects p on (p.id = l.project_id)";
598 if ($report['show_task'] || 'task' == $group_by_option)
599 $left_joins .= " left join tt_tasks t on (t.id = l.task_id)";
601 if ($cf_1_type == CustomFields::TYPE_TEXT)
602 $left_joins .= " left join tt_custom_field_log cfl on (l.id = cfl.log_id and cfl.status = 1)";
603 elseif ($cf_1_type == CustomFields::TYPE_DROPDOWN) {
604 $left_joins .= " left join tt_custom_field_log cfl on (l.id = cfl.log_id and cfl.status = 1)".
605 " left join tt_custom_field_options cfo on (cfl.option_id = cfo.id)";
608 if ($includeCost && MODE_TIME != $user->tracking_mode)
609 $left_joins .= " left join tt_user_project_binds upb on (l.user_id = upb.user_id and l.project_id = upb.project_id)";
611 $where = ttReportHelper::getFavWhere($report);
613 // Construct sql query for tt_log items.
614 $sql = "select ".join(', ', $fields)." from tt_log l $left_joins $where";
615 // If we don't have expense items (such as when the Expenses plugin is desabled), the above is all sql we need,
616 // with an exception of sorting part, that is added in the end.
618 // However, when we have expenses, we need to do a union with a separate query for expense items from tt_expense_items table.
619 if ($report['show_cost'] && $user->isPluginEnabled('ex')) { // if ex(penses) plugin is enabled
621 $fields = array(); // An array of fields for database query.
622 array_push($fields, 'ei.id');
623 array_push($fields, '2 as type'); // Type 2 is for tt_expense_items entries.
624 array_push($fields, 'ei.date');
625 if($canViewReports || $isClient)
626 array_push($fields, 'u.name as user');
627 // Add client name if it is selected.
628 if ($report['show_client'] || 'client' == $group_by_option)
629 array_push($fields, 'c.name as client');
630 // Add project name if it is selected.
631 if ($report['show_project'] || 'project' == $group_by_option)
632 array_push($fields, 'p.name as project');
633 if ($report['show_task'] || 'task' == $group_by_option)
634 array_push($fields, 'null'); // null for task name. We need to match column count for union.
635 if ($report['show_custom_field_1'] || 'cf_1' == $group_by_option)
636 array_push($fields, 'null'); // null for cf_1.
637 if ($report['show_start']) {
638 array_push($fields, 'null'); // null for unformatted_start.
639 array_push($fields, 'null'); // null for start.
641 if ($report['show_end'])
642 array_push($fields, 'null'); // null for finish.
643 if ($report['show_duration'])
644 array_push($fields, 'null'); // null for duration.
645 if ($report['show_work_units'])
646 array_push($fields, 'null as units'); // null for work units.
647 // Use the note field to print item name.
648 if ($report['show_note'])
649 array_push($fields, 'ei.name as note');
650 array_push($fields, 'ei.cost as cost');
651 array_push($fields, 'ei.cost as expense');
653 if ($canViewReports && $report['show_paid'])
654 array_push($fields, 'ei.paid as paid');
656 if ($canViewReports && $report['show_ip']) {
657 array_push($fields, 'ei.created as created');
658 array_push($fields, 'ei.created_ip as created_ip');
659 array_push($fields, 'ei.modified as modified');
660 array_push($fields, 'ei.modified_ip as modified_ip');
662 // Add invoice name if it is selected.
663 if (($canViewReports || $isClient) && $report['show_invoice'])
664 array_push($fields, 'i.name as invoice');
666 // Prepare sql query part for left joins.
668 if ($canViewReports || $isClient)
669 $left_joins .= " left join tt_users u on (u.id = ei.user_id)";
670 if ($report['show_client'] || 'client' == $group_by_option)
671 $left_joins .= " left join tt_clients c on (c.id = ei.client_id)";
672 if ($report['show_project'] || 'project' == $group_by_option)
673 $left_joins .= " left join tt_projects p on (p.id = ei.project_id)";
674 if (($canViewReports || $isClient) && $report['show_invoice'])
675 $left_joins .= " left join tt_invoices i on (i.id = ei.invoice_id and i.status = 1)";
677 $where = ttReportHelper::getFavExpenseWhere($report);
679 // Construct sql query for expense items.
680 $sql_for_expense_items = "select ".join(', ', $fields)." from tt_expense_items ei $left_joins $where";
682 // Construct a union.
683 $sql = "($sql) union all ($sql_for_expense_items)";
686 // Determine sort part.
687 $sort_part = ' order by ';
688 if ($group_by_option == null || 'no_grouping' == $group_by_option || 'date' == $group_by_option) // TODO: fix DB for NULL values in group_by field.
689 $sort_part .= 'date';
691 $sort_part .= $group_by_option.', date';
692 if (($canViewReports || $isClient) /*&& is_array($bean->getAttribute('users'))*/ && 'user' != $group_by_option)
693 $sort_part .= ', user, type';
694 if ($report['show_start'])
695 $sort_part .= ', unformatted_start';
696 $sort_part .= ', id';
699 // By now we are ready with sql.
701 // Obtain items for report.
702 $res = $mdb2->query($sql);
703 if (is_a($res, 'PEAR_Error')) die($res->getMessage());
705 while ($val = $res->fetchRow()) {
706 if ($convertTo12Hour) {
707 if($val['start'] != '')
708 $val['start'] = ttTimeHelper::to12HourFormat($val['start']);
709 if($val['finish'] != '')
710 $val['finish'] = ttTimeHelper::to12HourFormat($val['finish']);
712 if (isset($val['cost'])) {
713 if ('.' != $user->decimal_mark)
714 $val['cost'] = str_replace('.', $user->decimal_mark, $val['cost']);
716 if (isset($val['expense'])) {
717 if ('.' != $user->decimal_mark)
718 $val['expense'] = str_replace('.', $user->decimal_mark, $val['expense']);
720 if ('no_grouping' != $group_by_option) {
721 $val['grouped_by'] = $val[$group_by_option];
722 if ('date' == $group_by_option) {
723 // This is needed to get the date in user date format.
724 $o_date = new DateAndTime(DB_DATEFORMAT, $val['grouped_by']);
725 $val['grouped_by'] = $o_date->toString($user->date_format);
730 // This is needed to get the date in user date format.
731 $o_date = new DateAndTime(DB_DATEFORMAT, $val['date']);
732 $val['date'] = $o_date->toString($user->date_format);
736 $report_items[] = $row;
739 return $report_items;
742 // getSubtotals calculates report items subtotals when a report is grouped by.
743 // Without expenses, it's a simple select with group by.
744 // With expenses, it becomes a select with group by from a combined set of records obtained with "union all".
745 static function getSubtotals($bean) {
748 $group_by_option = $bean->getAttribute('group_by');
749 if ('no_grouping' == $group_by_option) return null;
751 $mdb2 = getConnection();
753 // Start with sql to obtain subtotals for time items. This simple sql will be used when we have no expenses.
755 // Determine group by field and a required join.
756 switch ($group_by_option) {
758 $group_field = 'l.date';
762 $group_field = 'u.name';
763 $group_join = 'left join tt_users u on (l.user_id = u.id) ';
766 $group_field = 'c.name';
767 $group_join = 'left join tt_clients c on (l.client_id = c.id) ';
770 $group_field = 'p.name';
771 $group_join = 'left join tt_projects p on (l.project_id = p.id) ';
774 $group_field = 't.name';
775 $group_join = 'left join tt_tasks t on (l.task_id = t.id) ';
778 $group_field = 'cfo.value';
779 $custom_fields = new CustomFields($user->group_id);
780 if ($custom_fields->fields[0]['type'] == CustomFields::TYPE_TEXT)
781 $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) ';
782 elseif ($custom_fields->fields[0]['type'] == CustomFields::TYPE_DROPDOWN)
783 $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) ';
787 $where = ttReportHelper::getWhere($bean);
788 if ($bean->getAttribute('chcost')) {
789 if (MODE_TIME == $user->tracking_mode) {
790 if ($group_by_option != 'user')
791 $left_join = 'left join tt_users u on (l.user_id = u.id)';
792 $sql = "select $group_field as group_field, sum(time_to_sec(l.duration)) as time";
793 if ($bean->getAttribute('chunits')) {
794 if ($user->unit_totals_only)
795 $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";
797 $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";
799 $sql .= ", sum(cast(l.billable * coalesce(u.rate, 0) * time_to_sec(l.duration)/3600 as decimal(10, 2))) as cost,
800 null as expenses from tt_log l
801 $group_join $left_join $where group by $group_field";
803 // If we are including cost and tracking projects, our query (the same as above) needs to join the tt_user_project_binds table.
804 $sql = "select $group_field as group_field, sum(time_to_sec(l.duration)) as time";
805 if ($bean->getAttribute('chunits')) {
806 if ($user->unit_totals_only)
807 $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";
809 $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";
811 $sql .= ", sum(cast(l.billable * coalesce(upb.rate, 0) * time_to_sec(l.duration)/3600 as decimal(10,2))) as cost,
812 null as expenses from tt_log l
814 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";
817 $sql = "select $group_field as group_field, sum(time_to_sec(l.duration)) as time";
818 if ($bean->getAttribute('chunits')) {
819 if ($user->unit_totals_only)
820 $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";
822 $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";
824 $sql .= ", null as expenses from tt_log l
825 $group_join $where group by $group_field";
827 // By now we have sql for time items.
829 // However, when we have expenses, we need to do a union with a separate query for expense items from tt_expense_items table.
830 if ($bean->getAttribute('chcost') && $user->isPluginEnabled('ex')) { // if ex(penses) plugin is enabled
832 // Determine group by field and a required join.
834 $group_field = 'null';
835 switch ($group_by_option) {
837 $group_field = 'ei.date';
841 $group_field = 'u.name';
842 $group_join = 'left join tt_users u on (ei.user_id = u.id) ';
845 $group_field = 'c.name';
846 $group_join = 'left join tt_clients c on (ei.client_id = c.id) ';
849 $group_field = 'p.name';
850 $group_join = 'left join tt_projects p on (ei.project_id = p.id) ';
854 $where = ttReportHelper::getExpenseWhere($bean);
855 $sql_for_expenses = "select $group_field as group_field, null as time";
856 if ($bean->getAttribute('chunits')) $sql_for_expenses .= ", null as units";
857 $sql_for_expenses .= ", sum(ei.cost) as cost, sum(ei.cost) as expenses from tt_expense_items ei $group_join $where";
858 // Add a "group by" clause if we are grouping.
859 if ('null' != $group_field) $sql_for_expenses .= " group by $group_field";
861 // Create a combined query.
862 $combined = "select group_field, sum(time) as time";
863 if ($bean->getAttribute('chunits')) $combined .= ", sum(units) as units";
864 $combined .= ", sum(cost) as cost, sum(expenses) as expenses from (($sql) union all ($sql_for_expenses)) t group by group_field";
869 $res = $mdb2->query($sql);
870 if (is_a($res, 'PEAR_Error')) die($res->getMessage());
872 while ($val = $res->fetchRow()) {
873 if ('date' == $group_by_option) {
874 // This is needed to get the date in user date format.
875 $o_date = new DateAndTime(DB_DATEFORMAT, $val['group_field']);
876 $val['group_field'] = $o_date->toString($user->date_format);
879 $time = $val['time'] ? sec_to_time_fmt_hm($val['time']) : null;
880 if ($bean->getAttribute('chcost')) {
881 if ('.' != $user->decimal_mark) {
882 $val['cost'] = str_replace('.', $user->decimal_mark, $val['cost']);
883 $val['expenses'] = str_replace('.', $user->decimal_mark, $val['expenses']);
885 $subtotals[$val['group_field']] = array('name'=>$val['group_field'],'time'=>$time, 'units'=> $val['units'],'cost'=>$val['cost'],'expenses'=>$val['expenses']);
887 $subtotals[$val['group_field']] = array('name'=>$val['group_field'],'time'=>$time, 'units'=> $val['units']);
893 // getFavSubtotals calculates report items subtotals when a favorite report is grouped by.
894 // Without expenses, it's a simple select with group by.
895 // With expenses, it becomes a select with group by from a combined set of records obtained with "union all".
896 static function getFavSubtotals($report) {
899 $group_by_option = $report['group_by'];
900 if ('no_grouping' == $group_by_option) return null;
902 $mdb2 = getConnection();
904 // Start with sql to obtain subtotals for time items. This simple sql will be used when we have no expenses.
906 // Determine group by field and a required join.
907 switch ($group_by_option) {
909 $group_field = 'l.date';
913 $group_field = 'u.name';
914 $group_join = 'left join tt_users u on (l.user_id = u.id) ';
917 $group_field = 'c.name';
918 $group_join = 'left join tt_clients c on (l.client_id = c.id) ';
921 $group_field = 'p.name';
922 $group_join = 'left join tt_projects p on (l.project_id = p.id) ';
925 $group_field = 't.name';
926 $group_join = 'left join tt_tasks t on (l.task_id = t.id) ';
929 $group_field = 'cfo.value';
930 $custom_fields = new CustomFields($user->group_id);
931 if ($custom_fields->fields[0]['type'] == CustomFields::TYPE_TEXT)
932 $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) ';
933 elseif ($custom_fields->fields[0]['type'] == CustomFields::TYPE_DROPDOWN)
934 $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) ';
938 $where = ttReportHelper::getFavWhere($report);
939 if ($report['show_cost']) {
940 if (MODE_TIME == $user->tracking_mode) {
941 if ($group_by_option != 'user')
942 $left_join = 'left join tt_users u on (l.user_id = u.id)';
943 $sql = "select $group_field as group_field, sum(time_to_sec(l.duration)) as time";
944 if ($report['show_work_units']) {
945 if ($user->unit_totals_only)
946 $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";
948 $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";
950 $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";
951 $sql .= ", sum(cast(l.billable * coalesce(u.rate, 0) * time_to_sec(l.duration)/3600 as decimal(10, 2))) as cost,
952 null as expenses from tt_log l
953 $group_join $left_join $where group by $group_field";
955 // If we are including cost and tracking projects, our query (the same as above) needs to join the tt_user_project_binds table.
956 $sql = "select $group_field as group_field, sum(time_to_sec(l.duration)) as time";
957 if ($report['show_work_units']) {
958 if ($user->unit_totals_only)
959 $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";
961 $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";
963 $sql .= ", sum(cast(l.billable * coalesce(upb.rate, 0) * time_to_sec(l.duration)/3600 as decimal(10,2))) as cost,
964 null as expenses from tt_log l
966 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";
969 $sql = "select $group_field as group_field, sum(time_to_sec(l.duration)) as time";
970 if ($report['show_work_units']) {
971 if ($user->unit_totals_only)
972 $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";
974 $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";
976 $sql .= ", null as expenses from tt_log l
977 $group_join $where group by $group_field";
979 // By now we have sql for time items.
981 // However, when we have expenses, we need to do a union with a separate query for expense items from tt_expense_items table.
982 if ($report['show_cost'] && $user->isPluginEnabled('ex')) { // if ex(penses) plugin is enabled
984 // Determine group by field and a required join.
986 $group_field = 'null';
987 switch ($group_by_option) {
989 $group_field = 'ei.date';
993 $group_field = 'u.name';
994 $group_join = 'left join tt_users u on (ei.user_id = u.id) ';
997 $group_field = 'c.name';
998 $group_join = 'left join tt_clients c on (ei.client_id = c.id) ';
1001 $group_field = 'p.name';
1002 $group_join = 'left join tt_projects p on (ei.project_id = p.id) ';
1006 $where = ttReportHelper::getFavExpenseWhere($report);
1007 $sql_for_expenses = "select $group_field as group_field, null as time";
1008 if ($report['show_work_units']) $sql_for_expenses .= ", null as units";
1009 $sql_for_expenses .= ", sum(ei.cost) as cost, sum(ei.cost) as expenses from tt_expense_items ei $group_join $where";
1010 // Add a "group by" clause if we are grouping.
1011 if ('null' != $group_field) $sql_for_expenses .= " group by $group_field";
1013 // Create a combined query.
1014 $combined = "select group_field, sum(time) as time";
1015 if ($report['show_work_units']) $combined .= ", sum(units) as units";
1016 $combined .= ", sum(cost) as cost, sum(expenses) as expenses from (($sql) union all ($sql_for_expenses)) t group by group_field";
1021 $res = $mdb2->query($sql);
1022 if (is_a($res, 'PEAR_Error')) die($res->getMessage());
1024 while ($val = $res->fetchRow()) {
1025 if ('date' == $group_by_option) {
1026 // This is needed to get the date in user date format.
1027 $o_date = new DateAndTime(DB_DATEFORMAT, $val['group_field']);
1028 $val['group_field'] = $o_date->toString($user->date_format);
1031 $time = $val['time'] ? sec_to_time_fmt_hm($val['time']) : null;
1032 if ($report['show_cost']) {
1033 if ('.' != $user->decimal_mark) {
1034 $val['cost'] = str_replace('.', $user->decimal_mark, $val['cost']);
1035 $val['expenses'] = str_replace('.', $user->decimal_mark, $val['expenses']);
1037 $subtotals[$val['group_field']] = array('name'=>$val['group_field'],'time'=>$time, 'units'=> $val['units'], 'cost'=>$val['cost'],'expenses'=>$val['expenses']);
1039 $subtotals[$val['group_field']] = array('name'=>$val['group_field'],'time'=>$time, 'units'=> $val['units']);
1045 // getTotals calculates total hours and cost for all report items.
1046 static function getTotals($bean)
1050 $mdb2 = getConnection();
1052 $where = ttReportHelper::getWhere($bean);
1055 $time_part = "sum(time_to_sec(l.duration)) as time";
1056 if ($bean->getAttribute('chunits')) {
1057 $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";
1059 if ($bean->getAttribute('chcost')) {
1060 if (MODE_TIME == $user->tracking_mode)
1061 $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";
1063 $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";
1065 $cost_part = ", null as cost, null as expenses";
1067 if ($bean->getAttribute('chcost')) {
1068 if (MODE_TIME == $user->tracking_mode) {
1069 $left_joins = "left join tt_users u on (l.user_id = u.id)";
1071 $left_joins = "left join tt_user_project_binds upb on (l.user_id = upb.user_id and l.project_id = upb.project_id)";
1074 // Prepare a query for time items.
1075 $sql = "select $time_part $units_part $cost_part from tt_log l $left_joins $where";
1077 // If we have expenses, query becomes a bit more complex.
1078 if ($bean->getAttribute('chcost') && $user->isPluginEnabled('ex')) {
1079 $where = ttReportHelper::getExpenseWhere($bean);
1080 $sql_for_expenses = "select null as time";
1081 if ($bean->getAttribute('chunits')) $sql_for_expenses .= ", null as units";
1082 $sql_for_expenses .= ", sum(cost) as cost, sum(cost) as expenses from tt_expense_items ei $where";
1084 // Create a combined query.
1085 $combined = "select sum(time) as time";
1086 if ($bean->getAttribute('chunits')) $combined .= ", sum(units) as units";
1087 $combined .= ", sum(cost) as cost, sum(expenses) as expenses from (($sql) union all ($sql_for_expenses)) t";
1092 $res = $mdb2->query($sql);
1093 if (is_a($res, 'PEAR_Error')) die($res->getMessage());
1095 $val = $res->fetchRow();
1096 $total_time = $val['time'] ? sec_to_time_fmt_hm($val['time']) : null;
1097 if ($bean->getAttribute('chcost')) {
1098 $total_cost = $val['cost'];
1099 if (!$total_cost) $total_cost = '0.00';
1100 if ('.' != $user->decimal_mark)
1101 $total_cost = str_replace('.', $user->decimal_mark, $total_cost);
1102 $total_expenses = $val['expenses'];
1103 if (!$total_expenses) $total_expenses = '0.00';
1104 if ('.' != $user->decimal_mark)
1105 $total_expenses = str_replace('.', $user->decimal_mark, $total_expenses);
1108 if ($bean->getAttribute('period'))
1109 $period = new Period($bean->getAttribute('period'), new DateAndTime($user->date_format));
1111 $period = new Period();
1113 new DateAndTime($user->date_format, $bean->getAttribute('start_date')),
1114 new DateAndTime($user->date_format, $bean->getAttribute('end_date')));
1117 $totals['start_date'] = $period->getStartDate();
1118 $totals['end_date'] = $period->getEndDate();
1119 $totals['time'] = $total_time;
1120 $totals['units'] = $val['units'];
1121 $totals['cost'] = $total_cost;
1122 $totals['expenses'] = $total_expenses;
1127 // getFavTotals calculates total hours and cost for all favorite report items.
1128 static function getFavTotals($report)
1132 $mdb2 = getConnection();
1134 $where = ttReportHelper::getFavWhere($report);
1137 $time_part = "sum(time_to_sec(l.duration)) as time";
1138 if ($report['show_work_units']) {
1139 $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";
1141 if ($report['show_cost']) {
1142 if (MODE_TIME == $user->tracking_mode)
1143 $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";
1145 $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";
1147 $cost_part = ", null as cost, null as expenses";
1149 if ($report['show_cost']) {
1150 if (MODE_TIME == $user->tracking_mode) {
1151 $left_joins = "left join tt_users u on (l.user_id = u.id)";
1153 $left_joins = "left join tt_user_project_binds upb on (l.user_id = upb.user_id and l.project_id = upb.project_id)";
1156 // Prepare a query for time items.
1157 $sql = "select $time_part $units_part $cost_part from tt_log l $left_joins $where";
1159 // If we have expenses, query becomes a bit more complex.
1160 if ($report['show_cost'] && $user->isPluginEnabled('ex')) {
1161 $where = ttReportHelper::getFavExpenseWhere($report);
1162 $sql_for_expenses = "select null as time";
1163 if ($report['show_work_units']) $sql_for_expenses .= ", null as units";
1164 $sql_for_expenses .= ", sum(cost) as cost, sum(cost) as expenses from tt_expense_items ei $where";
1166 // Create a combined query.
1167 $combined = "select sum(time) as time";
1168 if ($report['show_work_units']) $combined .= ", sum(units) as units";
1169 $combined .= ", sum(cost) as cost, sum(expenses) as expenses from (($sql) union all ($sql_for_expenses)) t";
1174 $res = $mdb2->query($sql);
1175 if (is_a($res, 'PEAR_Error')) die($res->getMessage());
1177 $val = $res->fetchRow();
1178 $total_time = $val['time'] ? sec_to_time_fmt_hm($val['time']) : null;
1179 if ($report['show_cost']) {
1180 $total_cost = $val['cost'];
1181 if (!$total_cost) $total_cost = '0.00';
1182 if ('.' != $user->decimal_mark)
1183 $total_cost = str_replace('.', $user->decimal_mark, $total_cost);
1184 $total_expenses = $val['expenses'];
1185 if (!$total_expenses) $total_expenses = '0.00';
1186 if ('.' != $user->decimal_mark)
1187 $total_expenses = str_replace('.', $user->decimal_mark, $total_expenses);
1190 if ($report['period'])
1191 $period = new Period($report['period'], new DateAndTime($user->date_format));
1193 $period = new Period();
1195 new DateAndTime($user->date_format, $report['period_start']),
1196 new DateAndTime($user->date_format, $report['period_end']));
1199 $totals['start_date'] = $period->getStartDate();
1200 $totals['end_date'] = $period->getEndDate();
1201 $totals['time'] = $total_time;
1202 $totals['units'] = $val['units'];
1203 $totals['cost'] = $total_cost;
1204 $totals['expenses'] = $total_expenses;
1209 // The assignToInvoice assigns a set of records to a specific invoice.
1210 static function assignToInvoice($invoice_id, $time_log_ids, $expense_item_ids)
1212 $mdb2 = getConnection();
1213 if ($time_log_ids) {
1214 $sql = "update tt_log set invoice_id = ".$mdb2->quote($invoice_id).
1215 " where id in(".join(', ', $time_log_ids).")";
1216 $affected = $mdb2->exec($sql);
1217 if (is_a($affected, 'PEAR_Error')) die($affected->getMessage());
1219 if ($expense_item_ids) {
1220 $sql = "update tt_expense_items set invoice_id = ".$mdb2->quote($invoice_id).
1221 " where id in(".join(', ', $expense_item_ids).")";
1222 $affected = $mdb2->exec($sql);
1223 if (is_a($affected, 'PEAR_Error')) die($affected->getMessage());
1227 // The markPaid marks a set of records as either paid or unpaid.
1228 static function markPaid($time_log_ids, $expense_item_ids, $paid = true)
1230 $mdb2 = getConnection();
1231 $paid_val = (int) $paid;
1232 if ($time_log_ids) {
1233 $sql = "update tt_log set paid = $paid_val where id in(".join(', ', $time_log_ids).")";
1234 $affected = $mdb2->exec($sql);
1235 if (is_a($affected, 'PEAR_Error')) die($affected->getMessage());
1237 if ($expense_item_ids) {
1238 $sql = "update tt_expense_items set paid = $paid_val where id in(".join(', ', $expense_item_ids).")";
1239 $affected = $mdb2->exec($sql);
1240 if (is_a($affected, 'PEAR_Error')) die($affected->getMessage());
1244 // prepareReportBody - prepares an email body for report.
1245 static function prepareReportBody($bean, $comment)
1250 // Determine these once as they are used in multiple places in this function.
1251 $canViewReports = $user->can('view_reports');
1252 $isClient = $user->isClient();
1254 $items = ttReportHelper::getItems($bean);
1255 $group_by = $bean->getAttribute('group_by');
1256 if ($group_by && 'no_grouping' != $group_by)
1257 $subtotals = ttReportHelper::getSubtotals($bean);
1258 $totals = ttReportHelper::getTotals($bean);
1260 // Use custom fields plugin if it is enabled.
1261 if ($user->isPluginEnabled('cf'))
1262 $custom_fields = new CustomFields($user->group_id);
1264 // Define some styles to use in email.
1265 $style_title = 'text-align: center; font-size: 15pt; font-family: Arial, Helvetica, sans-serif;';
1266 $tableHeader = 'font-weight: bold; background-color: #a6ccf7; text-align: left;';
1267 $tableHeaderCentered = 'font-weight: bold; background-color: #a6ccf7; text-align: center;';
1268 $rowItem = 'background-color: #ffffff;';
1269 $rowItemAlt = 'background-color: #f5f5f5;';
1270 $rowSubtotal = 'background-color: #e0e0e0;';
1271 $cellLeftAligned = 'text-align: left; vertical-align: top;';
1272 $cellRightAligned = 'text-align: right; vertical-align: top;';
1273 $cellLeftAlignedSubtotal = 'font-weight: bold; text-align: left; vertical-align: top;';
1274 $cellRightAlignedSubtotal = 'font-weight: bold; text-align: right; vertical-align: top;';
1276 // Start creating email body.
1278 $body .= '<head><meta http-equiv="content-type" content="text/html; charset='.CHARSET.'"></head>';
1282 $body .= '<p style="'.$style_title.'">'.$i18n->get('form.mail.report_subject').': '.$totals['start_date'].' - '.$totals['end_date'].'</p>';
1285 if ($comment) $body .= '<p>'.htmlspecialchars($comment).'</p>';
1287 if ($bean->getAttribute('chtotalsonly')) {
1288 // Totals only report. Output subtotals.
1290 // Determine group_by header.
1291 if ('cf_1' == $group_by)
1292 $group_by_header = htmlspecialchars($custom_fields->fields[0]['label']);
1294 $key = 'label.'.$group_by;
1295 $group_by_header = $i18n->get($key);
1298 $body .= '<table border="0" cellpadding="4" cellspacing="0" width="100%">';
1300 $body .= '<td style="'.$tableHeader.'">'.$group_by_header.'</td>';
1301 if ($bean->getAttribute('chduration'))
1302 $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.duration').'</td>';
1303 if ($bean->getAttribute('chunits'))
1304 $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.work_units_short').'</td>';
1305 if ($bean->getAttribute('chcost'))
1306 $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.cost').'</td>';
1308 foreach($subtotals as $subtotal) {
1309 $body .= '<tr style="'.$rowSubtotal.'">';
1310 $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.($subtotal['name'] ? htmlspecialchars($subtotal['name']) : ' ').'</td>';
1311 if ($bean->getAttribute('chduration')) {
1312 $body .= '<td style="'.$cellRightAlignedSubtotal.'">';
1313 if ($subtotal['time'] <> '0:00') $body .= $subtotal['time'];
1316 if ($bean->getAttribute('chunits')) {
1317 $body .= '<td style="'.$cellRightAlignedSubtotal.'">';
1318 $body .= $subtotal['units'];
1321 if ($bean->getAttribute('chcost')) {
1322 $body .= '<td style="'.$cellRightAlignedSubtotal.'">';
1323 $body .= ($canViewReports || $isClient) ? $subtotal['cost'] : $subtotal['expenses'];
1330 $body .= '<tr><td> </td></tr>';
1331 $body .= '<tr style="'.$rowSubtotal.'">';
1332 $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.$i18n->get('label.total').'</td>';
1333 if ($bean->getAttribute('chduration')) {
1334 $body .= '<td style="'.$cellRightAlignedSubtotal.'">';
1335 if ($totals['time'] <> '0:00') $body .= $totals['time'];
1338 if ($bean->getAttribute('chunits')) {
1339 $body .= '<td style="'.$cellRightAlignedSubtotal.'">';
1340 $body .= $totals['units'];
1343 if ($bean->getAttribute('chcost')) {
1344 $body .= '<td nowrap style="'.$cellRightAlignedSubtotal.'">'.htmlspecialchars($user->currency).' ';
1345 $body .= ($canViewReports || $isClient) ? $totals['cost'] : $totals['expenses'];
1350 $body .= '</table>';
1354 // Print table header.
1355 $body .= '<table border="0" cellpadding="4" cellspacing="0" width="100%">';
1357 $body .= '<td style="'.$tableHeader.'">'.$i18n->get('label.date').'</td>';
1358 if ($canViewReports || $isClient)
1359 $body .= '<td style="'.$tableHeader.'">'.$i18n->get('label.user').'</td>';
1360 if ($bean->getAttribute('chclient'))
1361 $body .= '<td style="'.$tableHeader.'">'.$i18n->get('label.client').'</td>';
1362 if ($bean->getAttribute('chproject'))
1363 $body .= '<td style="'.$tableHeader.'">'.$i18n->get('label.project').'</td>';
1364 if ($bean->getAttribute('chtask'))
1365 $body .= '<td style="'.$tableHeader.'">'.$i18n->get('label.task').'</td>';
1366 if ($bean->getAttribute('chcf_1'))
1367 $body .= '<td style="'.$tableHeader.'">'.htmlspecialchars($custom_fields->fields[0]['label']).'</td>';
1368 if ($bean->getAttribute('chstart'))
1369 $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.start').'</td>';
1370 if ($bean->getAttribute('chfinish'))
1371 $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.finish').'</td>';
1372 if ($bean->getAttribute('chduration'))
1373 $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.duration').'</td>';
1374 if ($bean->getAttribute('chunits'))
1375 $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.work_units_short').'</td>';
1376 if ($bean->getAttribute('chnote'))
1377 $body .= '<td style="'.$tableHeader.'">'.$i18n->get('label.note').'</td>';
1378 if ($bean->getAttribute('chcost'))
1379 $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.cost').'</td>';
1380 if ($bean->getAttribute('chpaid'))
1381 $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.paid').'</td>';
1382 if ($bean->getAttribute('chip'))
1383 $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.ip').'</td>';
1384 if ($bean->getAttribute('chinvoice'))
1385 $body .= '<td style="'.$tableHeader.'">'.$i18n->get('label.invoice').'</td>';
1388 // Initialize variables to print subtotals.
1389 if ($items && 'no_grouping' != $group_by) {
1390 $print_subtotals = true;
1392 $prev_grouped_by = '';
1393 $cur_grouped_by = '';
1395 // Initialize variables to alternate color of rows for different dates.
1398 $row_style = $rowItem;
1400 // Print report items.
1401 if (is_array($items)) {
1402 foreach ($items as $record) {
1403 $cur_date = $record['date'];
1404 // Print a subtotal row after a block of grouped items.
1405 if ($print_subtotals) {
1406 $cur_grouped_by = $record['grouped_by'];
1407 if ($cur_grouped_by != $prev_grouped_by && !$first_pass) {
1408 $body .= '<tr style="'.$rowSubtotal.'">';
1409 $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.$i18n->get('label.subtotal').'</td>';
1410 $subtotal_name = htmlspecialchars($subtotals[$prev_grouped_by]['name']);
1411 if ($canViewReports || $isClient) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.($group_by == 'user' ? $subtotal_name : '').'</td>';
1412 if ($bean->getAttribute('chclient')) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.($group_by == 'client' ? $subtotal_name : '').'</td>';
1413 if ($bean->getAttribute('chproject')) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.($group_by == 'project' ? $subtotal_name : '').'</td>';
1414 if ($bean->getAttribute('chtask')) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.($group_by == 'task' ? $subtotal_name : '').'</td>';
1415 if ($bean->getAttribute('chcf_1')) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.($group_by == 'cf_1' ? $subtotal_name : '').'</td>';
1416 if ($bean->getAttribute('chstart')) $body .= '<td></td>';
1417 if ($bean->getAttribute('chfinish')) $body .= '<td></td>';
1418 if ($bean->getAttribute('chduration')) $body .= '<td style="'.$cellRightAlignedSubtotal.'">'.$subtotals[$prev_grouped_by]['time'].'</td>';
1419 if ($bean->getAttribute('chunits')) $body .= '<td style="'.$cellRightAlignedSubtotal.'">'.$subtotals[$prev_grouped_by]['units'].'</td>';
1420 if ($bean->getAttribute('chnote')) $body .= '<td></td>';
1421 if ($bean->getAttribute('chcost')) {
1422 $body .= '<td style="'.$cellRightAlignedSubtotal.'">';
1423 $body .= ($canViewReports || $isClient) ? $subtotals[$prev_grouped_by]['cost'] : $subtotals[$prev_grouped_by]['expenses'];
1426 if ($bean->getAttribute('chpaid')) $body .= '<td></td>';
1427 if ($bean->getAttribute('chip')) $body .= '<td></td>';
1428 if ($bean->getAttribute('chinvoice')) $body .= '<td></td>';
1430 $body .= '<tr><td> </td></tr>';
1432 $first_pass = false;
1435 // Print a regular row.
1436 if ($cur_date != $prev_date)
1437 $row_style = ($row_style == $rowItem) ? $rowItemAlt : $rowItem;
1438 $body .= '<tr style="'.$row_style.'">';
1439 $body .= '<td style="'.$cellLeftAligned.'">'.$record['date'].'</td>';
1440 if ($canViewReports || $isClient)
1441 $body .= '<td style="'.$cellLeftAligned.'">'.htmlspecialchars($record['user']).'</td>';
1442 if ($bean->getAttribute('chclient'))
1443 $body .= '<td style="'.$cellLeftAligned.'">'.htmlspecialchars($record['client']).'</td>';
1444 if ($bean->getAttribute('chproject'))
1445 $body .= '<td style="'.$cellLeftAligned.'">'.htmlspecialchars($record['project']).'</td>';
1446 if ($bean->getAttribute('chtask'))
1447 $body .= '<td style="'.$cellLeftAligned.'">'.htmlspecialchars($record['task']).'</td>';
1448 if ($bean->getAttribute('chcf_1'))
1449 $body .= '<td style="'.$cellLeftAligned.'">'.htmlspecialchars($record['cf_1']).'</td>';
1450 if ($bean->getAttribute('chstart'))
1451 $body .= '<td nowrap style="'.$cellRightAligned.'">'.$record['start'].'</td>';
1452 if ($bean->getAttribute('chfinish'))
1453 $body .= '<td nowrap style="'.$cellRightAligned.'">'.$record['finish'].'</td>';
1454 if ($bean->getAttribute('chduration'))
1455 $body .= '<td style="'.$cellRightAligned.'">'.$record['duration'].'</td>';
1456 if ($bean->getAttribute('chunits'))
1457 $body .= '<td style="'.$cellRightAligned.'">'.$record['units'].'</td>';
1458 if ($bean->getAttribute('chnote'))
1459 $body .= '<td style="'.$cellLeftAligned.'">'.htmlspecialchars($record['note']).'</td>';
1460 if ($bean->getAttribute('chcost'))
1461 $body .= '<td style="'.$cellRightAligned.'">'.$record['cost'].'</td>';
1462 if ($bean->getAttribute('chpaid')) {
1463 $body .= '<td style="'.$cellRightAligned.'">';
1464 $body .= $record['paid'] == 1 ? $i18n->get('label.yes') : $i18n->get('label.no');
1467 if ($bean->getAttribute('chip')) {
1468 $body .= '<td style="'.$cellRightAligned.'">';
1469 $body .= $record['modified'] ? $record['modified_ip'].' '.$record['modified'] : $record['created_ip'].' '.$record['created'];
1472 if ($bean->getAttribute('chinvoice'))
1473 $body .= '<td style="'.$cellRightAligned.'">'.htmlspecialchars($record['invoice']).'</td>';
1476 $prev_date = $record['date'];
1477 if ($print_subtotals)
1478 $prev_grouped_by = $record['grouped_by'];
1482 // Print a terminating subtotal.
1483 if ($print_subtotals) {
1484 $body .= '<tr style="'.$rowSubtotal.'">';
1485 $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.$i18n->get('label.subtotal').'</td>';
1486 $subtotal_name = htmlspecialchars($subtotals[$cur_grouped_by]['name']);
1487 if ($canViewReports || $isClient) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.($group_by == 'user' ? $subtotal_name : '').'</td>';
1488 if ($bean->getAttribute('chclient')) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.($group_by == 'client' ? $subtotal_name : '').'</td>';
1489 if ($bean->getAttribute('chproject')) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.($group_by == 'project' ? $subtotal_name : '').'</td>';
1490 if ($bean->getAttribute('chtask')) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.($group_by == 'task' ? $subtotal_name : '').'</td>';
1491 if ($bean->getAttribute('chcf_1')) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.($group_by == 'cf_1' ? $subtotal_name : '').'</td>';
1492 if ($bean->getAttribute('chstart')) $body .= '<td></td>';
1493 if ($bean->getAttribute('chfinish')) $body .= '<td></td>';
1494 if ($bean->getAttribute('chduration')) $body .= '<td style="'.$cellRightAlignedSubtotal.'">'.$subtotals[$cur_grouped_by]['time'].'</td>';
1495 if ($bean->getAttribute('chunits')) $body .= '<td style="'.$cellRightAlignedSubtotal.'">'.$subtotals[$cur_grouped_by]['units'].'</td>';
1496 if ($bean->getAttribute('chnote')) $body .= '<td></td>';
1497 if ($bean->getAttribute('chcost')) {
1498 $body .= '<td style="'.$cellRightAlignedSubtotal.'">';
1499 $body .= ($canViewReports || $isClient) ? $subtotals[$cur_grouped_by]['cost'] : $subtotals[$cur_grouped_by]['expenses'];
1502 if ($bean->getAttribute('chpaid')) $body .= '<td></td>';
1503 if ($bean->getAttribute('chip')) $body .= '<td></td>';
1504 if ($bean->getAttribute('chinvoice')) $body .= '<td></td>';
1509 $body .= '<tr><td> </td></tr>';
1510 $body .= '<tr style="'.$rowSubtotal.'">';
1511 $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.$i18n->get('label.total').'</td>';
1512 if ($canViewReports || $isClient) $body .= '<td></td>';
1513 if ($bean->getAttribute('chclient')) $body .= '<td></td>';
1514 if ($bean->getAttribute('chproject')) $body .= '<td></td>';
1515 if ($bean->getAttribute('chtask')) $body .= '<td></td>';
1516 if ($bean->getAttribute('chcf_1')) $body .= '<td></td>';
1517 if ($bean->getAttribute('chstart')) $body .= '<td></td>';
1518 if ($bean->getAttribute('chfinish')) $body .= '<td></td>';
1519 if ($bean->getAttribute('chduration')) $body .= '<td style="'.$cellRightAlignedSubtotal.'">'.$totals['time'].'</td>';
1520 if ($bean->getAttribute('chunits')) $body .= '<td style="'.$cellRightAlignedSubtotal.'">'.$totals['units'].'</td>';
1521 if ($bean->getAttribute('chnote')) $body .= '<td></td>';
1522 if ($bean->getAttribute('chcost')) {
1523 $body .= '<td nowrap style="'.$cellRightAlignedSubtotal.'">'.htmlspecialchars($user->currency).' ';
1524 $body .= ($canViewReports || $isClient) ? $totals['cost'] : $totals['expenses'];
1527 if ($bean->getAttribute('chpaid')) $body .= '<td></td>';
1528 if ($bean->getAttribute('chip')) $body .= '<td></td>';
1529 if ($bean->getAttribute('chinvoice')) $body .= '<td></td>';
1532 $body .= '</table>';
1536 if (!defined('REPORT_FOOTER') || !(REPORT_FOOTER == false))
1537 $body .= '<p style="text-align: center;">'.$i18n->get('form.mail.footer').'</p>';
1539 // Finish creating email body.
1540 $body .= '</body></html>';
1545 // checkFavReportCondition - checks whether it is okay to send fav report.
1546 static function checkFavReportCondition($report, $condition)
1548 $items = ttReportHelper::getFavItems($report);
1550 $condition = str_replace('count', '', $condition);
1551 $count_required = (int) trim(str_replace('>', '', $condition));
1553 if (count($items) > $count_required)
1554 return true; // Condition ok.
1559 // prepareFavReportBody - prepares an email body for a favorite report.
1560 static function prepareFavReportBody($report)
1565 // Determine these once as they are used in multiple places in this function.
1566 $canViewReports = $user->can('view_reports');
1567 $isClient = $user->isClient();
1569 $items = ttReportHelper::getFavItems($report);
1570 $group_by = $report['group_by'];
1571 if ($group_by && 'no_grouping' != $group_by)
1572 $subtotals = ttReportHelper::getFavSubtotals($report);
1573 $totals = ttReportHelper::getFavTotals($report);
1575 // Use custom fields plugin if it is enabled.
1576 if ($user->isPluginEnabled('cf'))
1577 $custom_fields = new CustomFields($user->group_id);
1579 // Define some styles to use in email.
1580 $style_title = 'text-align: center; font-size: 15pt; font-family: Arial, Helvetica, sans-serif;';
1581 $tableHeader = 'font-weight: bold; background-color: #a6ccf7; text-align: left;';
1582 $tableHeaderCentered = 'font-weight: bold; background-color: #a6ccf7; text-align: center;';
1583 $rowItem = 'background-color: #ffffff;';
1584 $rowItemAlt = 'background-color: #f5f5f5;';
1585 $rowSubtotal = 'background-color: #e0e0e0;';
1586 $cellLeftAligned = 'text-align: left; vertical-align: top;';
1587 $cellRightAligned = 'text-align: right; vertical-align: top;';
1588 $cellLeftAlignedSubtotal = 'font-weight: bold; text-align: left; vertical-align: top;';
1589 $cellRightAlignedSubtotal = 'font-weight: bold; text-align: right; vertical-align: top;';
1591 // Start creating email body.
1593 $body .= '<head><meta http-equiv="content-type" content="text/html; charset='.CHARSET.'"></head>';
1597 $body .= '<p style="'.$style_title.'">'.$i18n->get('form.mail.report_subject').': '.$totals['start_date'].' - '.$totals['end_date'].'</p>';
1600 // if ($comment) $body .= '<p>'.htmlspecialchars($comment).'</p>'; // No comment for fav. reports.
1602 if ($report['show_totals_only']) {
1603 // Totals only report. Output subtotals.
1605 // Determine group_by header.
1606 if ('cf_1' == $group_by)
1607 $group_by_header = htmlspecialchars($custom_fields->fields[0]['label']);
1609 $key = 'label.'.$group_by;
1610 $group_by_header = $i18n->get($key);
1613 $body .= '<table border="0" cellpadding="4" cellspacing="0" width="100%">';
1615 $body .= '<td style="'.$tableHeader.'">'.$group_by_header.'</td>';
1616 if ($report['show_duration'])
1617 $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.duration').'</td>';
1618 if ($report['show_work_units'])
1619 $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.work_units_short').'</td>';
1620 if ($report['show_cost'])
1621 $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.cost').'</td>';
1623 foreach($subtotals as $subtotal) {
1624 $body .= '<tr style="'.$rowSubtotal.'">';
1625 $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.($subtotal['name'] ? htmlspecialchars($subtotal['name']) : ' ').'</td>';
1626 if ($report['show_duration']) {
1627 $body .= '<td style="'.$cellRightAlignedSubtotal.'">';
1628 if ($subtotal['time'] <> '0:00') $body .= $subtotal['time'];
1631 if ($report['show_work_units']) {
1632 $body .= '<td style="'.$cellRightAlignedSubtotal.'">';
1633 $body .= $subtotal['units'];
1636 if ($report['show_cost']) {
1637 $body .= '<td style="'.$cellRightAlignedSubtotal.'">';
1638 $body .= ($canViewReports || $isClient) ? $subtotal['cost'] : $subtotal['expenses'];
1645 $body .= '<tr><td> </td></tr>';
1646 $body .= '<tr style="'.$rowSubtotal.'">';
1647 $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.$i18n->get('label.total').'</td>';
1648 if ($report['show_duration']) {
1649 $body .= '<td style="'.$cellRightAlignedSubtotal.'">';
1650 if ($totals['time'] <> '0:00') $body .= $totals['time'];
1653 if ($report['show_work_units']) {
1654 $body .= '<td style="'.$cellRightAlignedSubtotal.'">';
1655 $body .= $totals['units'];
1658 if ($report['show_cost']) {
1659 $body .= '<td nowrap style="'.$cellRightAlignedSubtotal.'">'.htmlspecialchars($user->currency).' ';
1660 $body .= ($canViewReports || $isClient) ? $totals['cost'] : $totals['expenses'];
1665 $body .= '</table>';
1669 // Print table header.
1670 $body .= '<table border="0" cellpadding="4" cellspacing="0" width="100%">';
1672 $body .= '<td style="'.$tableHeader.'">'.$i18n->get('label.date').'</td>';
1673 if ($canViewReports || $isClient)
1674 $body .= '<td style="'.$tableHeader.'">'.$i18n->get('label.user').'</td>';
1675 if ($report['show_client'])
1676 $body .= '<td style="'.$tableHeader.'">'.$i18n->get('label.client').'</td>';
1677 if ($report['show_project'])
1678 $body .= '<td style="'.$tableHeader.'">'.$i18n->get('label.project').'</td>';
1679 if ($report['show_task'])
1680 $body .= '<td style="'.$tableHeader.'">'.$i18n->get('label.task').'</td>';
1681 if ($report['show_custom_field_1'])
1682 $body .= '<td style="'.$tableHeader.'">'.htmlspecialchars($custom_fields->fields[0]['label']).'</td>';
1683 if ($report['show_start'])
1684 $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.start').'</td>';
1685 if ($report['show_end'])
1686 $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.finish').'</td>';
1687 if ($report['show_duration'])
1688 $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.duration').'</td>';
1689 if ($report['show_work_units'])
1690 $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.work_units_short').'</td>';
1691 if ($report['show_note'])
1692 $body .= '<td style="'.$tableHeader.'">'.$i18n->get('label.note').'</td>';
1693 if ($report['show_cost'])
1694 $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.cost').'</td>';
1695 if ($report['show_paid'])
1696 $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.paid').'</td>';
1697 if ($report['show_ip'])
1698 $body .= '<td style="'.$tableHeaderCentered.'" width="5%">'.$i18n->get('label.ip').'</td>';
1699 if ($report['show_invoice'])
1700 $body .= '<td style="'.$tableHeader.'">'.$i18n->get('label.invoice').'</td>';
1703 // Initialize variables to print subtotals.
1704 if ($items && 'no_grouping' != $group_by) {
1705 $print_subtotals = true;
1707 $prev_grouped_by = '';
1708 $cur_grouped_by = '';
1710 // Initialize variables to alternate color of rows for different dates.
1713 $row_style = $rowItem;
1715 // Print report items.
1716 if (is_array($items)) {
1717 foreach ($items as $record) {
1718 $cur_date = $record['date'];
1719 // Print a subtotal row after a block of grouped items.
1720 if ($print_subtotals) {
1721 $cur_grouped_by = $record['grouped_by'];
1722 if ($cur_grouped_by != $prev_grouped_by && !$first_pass) {
1723 $body .= '<tr style="'.$rowSubtotal.'">';
1724 $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.$i18n->get('label.subtotal').'</td>';
1725 $subtotal_name = htmlspecialchars($subtotals[$prev_grouped_by]['name']);
1726 if ($canViewReports || $isClient) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.($group_by == 'user' ? $subtotal_name : '').'</td>';
1727 if ($report['show_client']) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.($group_by == 'client' ? $subtotal_name : '').'</td>';
1728 if ($report['show_project']) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.($group_by == 'project' ? $subtotal_name : '').'</td>';
1729 if ($report['show_task']) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.($group_by == 'task' ? $subtotal_name : '').'</td>';
1730 if ($report['show_custom_field_1']) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.($group_by == 'cf_1' ? $subtotal_name : '').'</td>';
1731 if ($report['show_start']) $body .= '<td></td>';
1732 if ($report['show_end']) $body .= '<td></td>';
1733 if ($report['show_duration']) $body .= '<td style="'.$cellRightAlignedSubtotal.'">'.$subtotals[$prev_grouped_by]['time'].'</td>';
1734 if ($report['show_work_units']) $body .= '<td style="'.$cellRightAlignedSubtotal.'">'.$subtotals[$prev_grouped_by]['units'].'</td>';
1735 if ($report['show_note']) $body .= '<td></td>';
1736 if ($report['show_cost']) {
1737 $body .= '<td style="'.$cellRightAlignedSubtotal.'">';
1738 $body .= ($canViewReports || $isClient) ? $subtotals[$prev_grouped_by]['cost'] : $subtotals[$prev_grouped_by]['expenses'];
1741 if ($report['show_paid']) $body .= '<td></td>';
1742 if ($report['show_ip']) $body .= '<td></td>';
1743 if ($report['show_invoice']) $body .= '<td></td>';
1745 $body .= '<tr><td> </td></tr>';
1747 $first_pass = false;
1750 // Print a regular row.
1751 if ($cur_date != $prev_date)
1752 $row_style = ($row_style == $rowItem) ? $rowItemAlt : $rowItem;
1753 $body .= '<tr style="'.$row_style.'">';
1754 $body .= '<td style="'.$cellLeftAligned.'">'.$record['date'].'</td>';
1755 if ($canViewReports || $isClient)
1756 $body .= '<td style="'.$cellLeftAligned.'">'.htmlspecialchars($record['user']).'</td>';
1757 if ($report['show_client'])
1758 $body .= '<td style="'.$cellLeftAligned.'">'.htmlspecialchars($record['client']).'</td>';
1759 if ($report['show_project'])
1760 $body .= '<td style="'.$cellLeftAligned.'">'.htmlspecialchars($record['project']).'</td>';
1761 if ($report['show_task'])
1762 $body .= '<td style="'.$cellLeftAligned.'">'.htmlspecialchars($record['task']).'</td>';
1763 if ($report['show_custom_field_1'])
1764 $body .= '<td style="'.$cellLeftAligned.'">'.htmlspecialchars($record['cf_1']).'</td>';
1765 if ($report['show_start'])
1766 $body .= '<td nowrap style="'.$cellRightAligned.'">'.$record['start'].'</td>';
1767 if ($report['show_end'])
1768 $body .= '<td nowrap style="'.$cellRightAligned.'">'.$record['finish'].'</td>';
1769 if ($report['show_duration'])
1770 $body .= '<td style="'.$cellRightAligned.'">'.$record['duration'].'</td>';
1771 if ($report['show_work_units'])
1772 $body .= '<td style="'.$cellRightAligned.'">'.$record['units'].'</td>';
1773 if ($report['show_note'])
1774 $body .= '<td style="'.$cellLeftAligned.'">'.htmlspecialchars($record['note']).'</td>';
1775 if ($report['show_cost'])
1776 $body .= '<td style="'.$cellRightAligned.'">'.$record['cost'].'</td>';
1777 if ($report['show_paid']) {
1778 $body .= '<td style="'.$cellRightAligned.'">';
1779 $body .= $record['paid'] == 1 ? $i18n->get('label.yes') : $i18n->get('label.no');
1782 if ($report['show_ip']) {
1783 $body .= '<td style="'.$cellRightAligned.'">';
1784 $body .= $record['modified'] ? $record['modified_ip'].' '.$record['modified'] : $record['created_ip'].' '.$record['created'];
1787 if ($report['show_invoice'])
1788 $body .= '<td style="'.$cellRightAligned.'">'.htmlspecialchars($record['invoice']).'</td>';
1791 $prev_date = $record['date'];
1792 if ($print_subtotals)
1793 $prev_grouped_by = $record['grouped_by'];
1797 // Print a terminating subtotal.
1798 if ($print_subtotals) {
1799 $body .= '<tr style="'.$rowSubtotal.'">';
1800 $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.$i18n->get('label.subtotal').'</td>';
1801 $subtotal_name = htmlspecialchars($subtotals[$cur_grouped_by]['name']);
1802 if ($canViewReports || $isClient) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.($group_by == 'user' ? $subtotal_name : '').'</td>';
1803 if ($report['show_client']) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.($group_by == 'client' ? $subtotal_name : '').'</td>';
1804 if ($report['show_project']) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.($group_by == 'project' ? $subtotal_name : '').'</td>';
1805 if ($report['show_task']) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.($group_by == 'task' ? $subtotal_name : '').'</td>';
1806 if ($report['show_custom_field_1']) $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.($group_by == 'cf_1' ? $subtotal_name : '').'</td>';
1807 if ($report['show_start']) $body .= '<td></td>';
1808 if ($report['show_end']) $body .= '<td></td>';
1809 if ($report['show_duration']) $body .= '<td style="'.$cellRightAlignedSubtotal.'">'.$subtotals[$cur_grouped_by]['time'].'</td>';
1810 if ($report['show_work_units']) $body .= '<td style="'.$cellRightAlignedSubtotal.'">'.$subtotals[$cur_grouped_by]['units'].'</td>';
1811 if ($report['show_note']) $body .= '<td></td>';
1812 if ($report['show_cost']) {
1813 $body .= '<td style="'.$cellRightAlignedSubtotal.'">';
1814 $body .= ($canViewReports || $isClient) ? $subtotals[$cur_grouped_by]['cost'] : $subtotals[$cur_grouped_by]['expenses'];
1817 if ($report['show_paid']) $body .= '<td></td>';
1818 if ($report['show_ip']) $body .= '<td></td>';
1819 if ($report['show_invoice']) $body .= '<td></td>';
1824 $body .= '<tr><td> </td></tr>';
1825 $body .= '<tr style="'.$rowSubtotal.'">';
1826 $body .= '<td style="'.$cellLeftAlignedSubtotal.'">'.$i18n->get('label.total').'</td>';
1827 if ($canViewReports || $isClient) $body .= '<td></td>';
1828 if ($report['show_client']) $body .= '<td></td>';
1829 if ($report['show_project']) $body .= '<td></td>';
1830 if ($report['show_task']) $body .= '<td></td>';
1831 if ($report['show_custom_field_1']) $body .= '<td></td>';
1832 if ($report['show_start']) $body .= '<td></td>';
1833 if ($report['show_end']) $body .= '<td></td>';
1834 if ($report['show_duration']) $body .= '<td style="'.$cellRightAlignedSubtotal.'">'.$totals['time'].'</td>';
1835 if ($report['show_work_units']) $body .= '<td style="'.$cellRightAlignedSubtotal.'">'.$totals['units'].'</td>';
1836 if ($report['show_note']) $body .= '<td></td>';
1837 if ($report['show_cost']) {
1838 $body .= '<td nowrap style="'.$cellRightAlignedSubtotal.'">'.htmlspecialchars($user->currency).' ';
1839 $body .= ($canViewReports || $isClient) ? $totals['cost'] : $totals['expenses'];
1842 if ($report['show_paid']) $body .= '<td></td>';
1843 if ($report['show_ip']) $body .= '<td></td>';
1844 if ($report['show_invoice']) $body .= '<td></td>';
1847 $body .= '</table>';
1851 if (!defined('REPORT_FOOTER') || !(REPORT_FOOTER == false))
1852 $body .= '<p style="text-align: center;">'.$i18n->get('form.mail.footer').'</p>';
1854 // Finish creating email body.
1855 $body .= '</body></html>';
1860 // sendFavReport - sends a favorite report to a specified email, called from cron.php
1861 static function sendFavReport($report, $subject, $email, $cc) {
1862 // We are called from cron.php, we have no $bean in session.
1863 // cron.php sets global $user and $i18n objects to match our favorite report user.
1867 // Prepare report body.
1868 $body = ttReportHelper::prepareFavReportBody($report);
1870 import('mail.Mailer');
1871 $mailer = new Mailer();
1872 $mailer->setCharSet(CHARSET);
1873 $mailer->setContentType('text/html');
1874 $mailer->setSender(SENDER);
1876 $mailer->setReceiverCC($cc);
1877 if (!empty($user->bcc_email))
1878 $mailer->setReceiverBCC($user->bcc_email);
1879 $mailer->setReceiver($email);
1880 $mailer->setMailMode(MAIL_MODE);
1881 if (empty($subject)) $subject = $report['name'];
1882 if (!$mailer->send($subject, $body))