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('ttUserHelper');
30 import('DateAndTime');
31 import('ttInvoiceHelper');
33 // Class ttTeamHelper - contains helper functions that operate with groups.
36 // The getUserCount function returns number of active users in a group.
37 static function getUserCount($group_id) {
38 $mdb2 = getConnection();
40 $sql = "select count(id) as cnt from tt_users where group_id = $group_id and status = 1";
41 $res = $mdb2->query($sql);
43 if (!is_a($res, 'PEAR_Error')) {
44 $val = $res->fetchRow();
50 // The getUsersForClient obtains all active and inactive users in a group that are relevant to a client.
51 static function getUsersForClient() {
53 $mdb2 = getConnection();
55 $sql = "select u.id, u.name from tt_user_project_binds upb".
56 " inner join tt_client_project_binds cpb on (upb.project_id = cpb.project_id and cpb.client_id = $user->client_id)".
57 " inner join tt_users u on (u.id = upb.user_id)".
58 " where (u.status = 1 or u.status = 0)".
60 " order by upper(u.name)";
61 $res = $mdb2->query($sql);
63 if (is_a($res, 'PEAR_Error'))
65 while ($val = $res->fetchRow()) {
71 // The getActiveUsers obtains all active users in a given group.
72 static function getActiveUsers($options = null) {
75 $mdb2 = getConnection();
77 if (isset($options['getAllFields']))
78 $sql = "select u.*, r.name as role_name, r.rank from tt_users u left join tt_roles r on (u.role_id = r.id) where u.group_id = $user->group_id and u.status = 1 order by upper(u.name)";
80 $sql = "select id, name from tt_users where group_id = $user->group_id and status = 1 order by upper(name)";
81 $res = $mdb2->query($sql);
83 if (is_a($res, 'PEAR_Error'))
85 while ($val = $res->fetchRow()) {
86 // Localize top manager role name, as it is not localized in db.
87 if ($val['rank'] == 512)
88 $val['role_name'] = $i18n->get('role.top_manager.label');
92 if (isset($options['putSelfFirst'])) {
93 // Put own entry at the front.
94 $cnt = count($user_list);
95 for($i = 0; $i < $cnt; $i++) {
96 if ($user_list[$i]['id'] == $user->id) {
97 $self = $user_list[$i]; // Found self.
98 array_unshift($user_list, $self); // Put own entry at the front.
99 array_splice($user_list, $i+1, 1); // Remove duplicate.
106 // The swapRolesWith swaps existing user role with that of another user.
107 static function swapRolesWith($user_id) {
109 $mdb2 = getConnection();
111 // Obtain role id for the user we are swapping ourselves with.
112 $sql = "select u.id, u.role_id from tt_users u left join tt_roles r on (u.role_id = r.id) where u.id = $user_id and u.group_id = $user->group_id and u.status = 1 and r.rank < $user->rank";
113 $res = $mdb2->query($sql);
114 if (is_a($res, 'PEAR_Error'))
116 $val = $res->fetchRow();
117 if (!$val['id'] || !$val['role_id'])
120 $modified_part = ', modified = now(), modified_ip = '.$mdb2->quote($_SERVER['REMOTE_ADDR']).', modified_by = '.$mdb2->quote($user->id);
123 $sql = "update tt_users set role_id = $user->role_id".$modified_part." where id = $user_id and group_id = $user->group_id";
124 $affected = $mdb2->exec($sql);
125 if (is_a($affected, 'PEAR_Error')) return false;
128 $role_id = $val['role_id'];
129 $sql = "update tt_users set role_id = $role_id".$modified_part." where id = $user->id and group_id = $user->group_id";
130 $affected = $mdb2->exec($sql);
131 if (is_a($affected, 'PEAR_Error')) return false;
136 // The getUsersForSwap obtains all users a current user can swap roles with.
137 static function getUsersForSwap() {
139 $mdb2 = getConnection();
141 $sql = "select u.id, u.name, r.rank, r.rights from tt_users u left join tt_roles r on (u.role_id = r.id) where u.group_id = $user->group_id and u.status = 1 and r.rank < $user->rank order by upper(u.name)";
142 $res = $mdb2->query($sql);
143 $user_list = array();
144 if (is_a($res, 'PEAR_Error'))
146 while ($val = $res->fetchRow()) {
147 $isClient = in_array('track_own_time', explode(',', $val['rights'])) ? 0 : 1; // Clients do not have track_own_time right.
149 continue; // Skip adding clients.
156 // The getUsers obtains all active and inactive (but not deleted) users in a group.
157 static function getUsers() {
159 $mdb2 = getConnection();
160 $sql = "select id, name from tt_users where group_id = $user->group_id and (status = 1 or status = 0) order by upper(name)";
161 $res = $mdb2->query($sql);
162 $user_list = array();
163 if (is_a($res, 'PEAR_Error'))
165 while ($val = $res->fetchRow()) {
171 // The getInactiveUsers obtains all inactive users in a group.
172 static function getInactiveUsers($group_id, $all_fields = false) {
173 $mdb2 = getConnection();
176 $sql = "select u.*, r.name as role_name from tt_users u left join tt_roles r on (u.role_id = r.id) where u.group_id = $group_id and u.status = 0 order by upper(u.name)";
178 $sql = "select id, name from tt_users where group_id = $group_id and status = 0 order by upper(name)";
179 $res = $mdb2->query($sql);
181 if (!is_a($res, 'PEAR_Error')) {
182 while ($val = $res->fetchRow()) {
190 // getActiveProjects - returns an array of active projects for a group.
191 static function getActiveProjects($group_id)
194 $mdb2 = getConnection();
196 $sql = "select id, name, description, tasks from tt_projects
197 where group_id = $group_id and status = 1 order by upper(name)";
198 $res = $mdb2->query($sql);
200 if (!is_a($res, 'PEAR_Error')) {
201 while ($val = $res->fetchRow()) {
208 // getInactiveProjects - returns an array of inactive projects for a group.
209 static function getInactiveProjects($group_id)
212 $mdb2 = getConnection();
214 $sql = "select id, name, description, tasks from tt_projects
215 where group_id = $group_id and status = 0 order by upper(name)";
216 $res = $mdb2->query($sql);
218 if (!is_a($res, 'PEAR_Error')) {
219 while ($val = $res->fetchRow()) {
226 // The getAllProjects obtains all projects in a group.
227 static function getAllProjects($group_id, $all_fields = false) {
228 $mdb2 = getConnection();
231 $sql = "select * from tt_projects where group_id = $group_id order by status, upper(name)";
233 $sql = "select id, name from tt_projects where group_id = $group_id order by status, upper(name)";
234 $res = $mdb2->query($sql);
236 if (!is_a($res, 'PEAR_Error')) {
237 while ($val = $res->fetchRow()) {
245 // getActiveTasks - returns an array of active tasks for a group.
246 static function getActiveTasks($group_id)
249 $mdb2 = getConnection();
251 $sql = "select id, name, description from tt_tasks where group_id = $group_id and status = 1 order by upper(name)";
252 $res = $mdb2->query($sql);
254 if (!is_a($res, 'PEAR_Error')) {
255 while ($val = $res->fetchRow()) {
262 // getInactiveTasks - returns an array of inactive tasks for a group.
263 static function getInactiveTasks($group_id)
266 $mdb2 = getConnection();
268 $sql = "select id, name, description from tt_tasks
269 where group_id = $group_id and status = 0 order by upper(name)";
270 $res = $mdb2->query($sql);
272 if (!is_a($res, 'PEAR_Error')) {
273 while ($val = $res->fetchRow()) {
280 // The getAllTasks obtains all tasks in a group.
281 static function getAllTasks($group_id, $all_fields = false) {
282 $mdb2 = getConnection();
285 $sql = "select * from tt_tasks where group_id = $group_id order by status, upper(name)";
287 $sql = "select id, name from tt_tasks where group_id = $group_id order by status, upper(name)";
288 $res = $mdb2->query($sql);
290 if (!is_a($res, 'PEAR_Error')) {
291 while ($val = $res->fetchRow()) {
299 // getActiveRolesForUser - returns an array of relevant active roles for user with rank less than self.
300 // "Relevant" means that client roles are filtered out if Client plugin is disabled.
301 static function getActiveRolesForUser()
305 $mdb2 = getConnection();
307 $sql = "select id, name, description, rank, rights from tt_roles where group_id = $user->group_id and rank < $user->rank and status = 1 order by rank";
308 $res = $mdb2->query($sql);
310 if (!is_a($res, 'PEAR_Error')) {
311 while ($val = $res->fetchRow()) {
312 $val['is_client'] = in_array('track_own_time', explode(',', $val['rights'])) ? 0 : 1; // Clients do not have data entry right.
313 if ($val['is_client'] && !$user->isPluginEnabled('cl'))
314 continue; // Skip adding a client role.
321 // getActiveRoles - returns an array of active roles for a group.
322 static function getActiveRoles($group_id)
325 $mdb2 = getConnection();
327 $sql = "select id, name, description, rank, rights from tt_roles where group_id = $group_id and status = 1 order by rank";
328 $res = $mdb2->query($sql);
330 if (!is_a($res, 'PEAR_Error')) {
331 while ($val = $res->fetchRow()) {
332 $val['is_client'] = in_array('track_own_time', explode(',', $val['rights'])) ? 0 : 1; // Clients do not have track_own_time right.
339 // getInactiveRoles - returns an array of inactive roles for a group.
340 static function getInactiveRoles($group_id)
343 $mdb2 = getConnection();
345 $sql = "select id, name, rank, description from tt_roles
346 where group_id = $group_id and status = 0 order by rank";
347 $res = $mdb2->query($sql);
349 if (!is_a($res, 'PEAR_Error')) {
350 while ($val = $res->fetchRow()) {
357 // getInactiveRolesForUser - returns an array of relevant active roles for user with rank less than self.
358 // "Relevant" means that client roles are filtered out if Client plugin is disabled.
359 static function getInactiveRolesForUser()
363 $mdb2 = getConnection();
365 $sql = "select id, name, description, rank, rights from tt_roles where group_id = $user->group_id and rank < $user->rank and status = 0 order by rank";
366 $res = $mdb2->query($sql);
368 if (!is_a($res, 'PEAR_Error')) {
369 while ($val = $res->fetchRow()) {
370 $val['is_client'] = in_array('track_own_time', explode(',', $val['rights'])) ? 0 : 1; // Clients do not have data entry right.
371 if ($val['is_client'] && !$user->isPluginEnabled('cl'))
372 continue; // Skip adding a client role.
379 // The getActiveClients returns an array of active clients for a group.
380 static function getActiveClients($group_id, $all_fields = false)
383 $mdb2 = getConnection();
386 $sql = "select * from tt_clients where group_id = $group_id and status = 1 order by upper(name)";
388 $sql = "select id, name from tt_clients where group_id = $group_id and status = 1 order by upper(name)";
390 $res = $mdb2->query($sql);
392 if (!is_a($res, 'PEAR_Error')) {
393 while ($val = $res->fetchRow()) {
400 // The getInactiveClients returns an array of inactive clients for a group.
401 static function getInactiveClients($group_id, $all_fields = false)
404 $mdb2 = getConnection();
407 $sql = "select * from tt_clients where group_id = $group_id and status = 0 order by upper(name)";
409 $sql = "select id, name from tt_clients where group_id = $group_id and status = 0 order by upper(name)";
411 $res = $mdb2->query($sql);
413 if (!is_a($res, 'PEAR_Error')) {
414 while ($val = $res->fetchRow()) {
421 // The getAllClients obtains all clients in a group.
422 static function getAllClients($group_id, $all_fields = false) {
423 $mdb2 = getConnection();
426 $sql = "select * from tt_clients where group_id = $group_id order by status, upper(name)";
428 $sql = "select id, name from tt_clients where group_id = $group_id order by status, upper(name)";
430 $res = $mdb2->query($sql);
432 if (!is_a($res, 'PEAR_Error')) {
433 while ($val = $res->fetchRow()) {
441 // The getActiveInvoices returns an array of active invoices for a group.
442 static function getActiveInvoices($localizeDates = true)
445 $addPaidStatus = $user->isPluginEnabled('ps');
448 $mdb2 = getConnection();
450 if ($user->isClient())
451 $client_part = " and i.client_id = $user->client_id";
453 $sql = "select i.id, i.name, i.date, i.client_id, i.status, c.name as client_name from tt_invoices i
454 left join tt_clients c on (c.id = i.client_id)
455 where i.status = 1 and i.group_id = $user->group_id $client_part order by i.name";
456 $res = $mdb2->query($sql);
458 if (!is_a($res, 'PEAR_Error')) {
459 $dt = new DateAndTime(DB_DATEFORMAT);
460 while ($val = $res->fetchRow()) {
461 if ($localizeDates) {
462 $dt->parseVal($val['date']);
463 $val['date'] = $dt->toString($user->date_format);
466 $val['paid'] = ttInvoiceHelper::isPaid($val['id']);
473 // The getAllInvoices returns an array of all invoices for a group.
474 static function getAllInvoices()
479 $mdb2 = getConnection();
481 $sql = "select * from tt_invoices where group_id = $user->group_id";
482 $res = $mdb2->query($sql);
484 if (!is_a($res, 'PEAR_Error')) {
485 $dt = new DateAndTime(DB_DATEFORMAT);
486 while ($val = $res->fetchRow()) {
493 // The getRecentInvoices returns an array of recent invoices (max 3) for a client.
494 static function getRecentInvoices($group_id, $client_id)
499 $mdb2 = getConnection();
501 $sql = "select i.id, i.name from tt_invoices i
502 left join tt_clients c on (c.id = i.client_id)
503 where i.group_id = $group_id and i.status = 1 and c.id = $client_id
504 order by i.id desc limit 3";
505 $res = $mdb2->query($sql);
507 if (!is_a($res, 'PEAR_Error')) {
508 $dt = new DateAndTime(DB_DATEFORMAT);
509 while ($val = $res->fetchRow()) {
516 // getUserToProjectBinds - obtains all user to project binds for a group.
517 static function getUserToProjectBinds($group_id) {
518 $mdb2 = getConnection();
521 $sql = "select * from tt_user_project_binds where user_id in (select id from tt_users where group_id = $group_id) order by user_id, status, project_id";
522 $res = $mdb2->query($sql);
524 if (!is_a($res, 'PEAR_Error')) {
525 while ($val = $res->fetchRow()) {
533 // The getAllCustomFields obtains all custom fields in a group.
534 static function getAllCustomFields($group_id) {
535 $mdb2 = getConnection();
537 $sql = "select * from tt_custom_fields where group_id = $group_id order by status";
539 $res = $mdb2->query($sql);
541 if (!is_a($res, 'PEAR_Error')) {
542 while ($val = $res->fetchRow()) {
550 // The getAllCustomFieldOptions obtains all custom field options in a group.
551 static function getAllCustomFieldOptions($group_id) {
552 $mdb2 = getConnection();
554 $sql = "select * from tt_custom_field_options where field_id in (select id from tt_custom_fields where group_id = $group_id) order by id";
556 $res = $mdb2->query($sql);
558 if (!is_a($res, 'PEAR_Error')) {
559 while ($val = $res->fetchRow()) {
567 // The getCustomFieldLog obtains all custom field log entries for a group.
568 static function getCustomFieldLog($group_id) {
569 $mdb2 = getConnection();
571 $sql = "select * from tt_custom_field_log where field_id in (select id from tt_custom_fields where group_id = $group_id) order by id";
573 $res = $mdb2->query($sql);
575 if (!is_a($res, 'PEAR_Error')) {
576 while ($val = $res->fetchRow()) {
584 // getFavReports - obtains all favorite reports for all users in a group.
585 static function getFavReports($group_id) {
586 $mdb2 = getConnection();
589 $sql = "select * from tt_fav_reports where user_id in (select id from tt_users where group_id = $group_id)";
590 $res = $mdb2->query($sql);
592 if (!is_a($res, 'PEAR_Error')) {
593 while ($val = $res->fetchRow()) {
601 // getExpenseItems - obtains all expense items for all users in a group.
602 static function getExpenseItems($group_id) {
603 $mdb2 = getConnection();
606 $sql = "select * from tt_expense_items where user_id in (select id from tt_users where group_id = $group_id)";
607 $res = $mdb2->query($sql);
609 if (!is_a($res, 'PEAR_Error')) {
610 while ($val = $res->fetchRow()) {
618 // getPredefinedExpenses - obtains predefined expenses for a group.
619 static function getPredefinedExpenses($group_id) {
621 $replaceDecimalMark = ('.' != $user->decimal_mark);
623 $mdb2 = getConnection();
626 $sql = "select id, name, cost from tt_predefined_expenses where group_id = $group_id";
627 $res = $mdb2->query($sql);
629 if (!is_a($res, 'PEAR_Error')) {
630 while ($val = $res->fetchRow()) {
631 if ($replaceDecimalMark)
632 $val['cost'] = str_replace('.', $user->decimal_mark, $val['cost']);
640 // getNotifications - obtains notification descriptions for a group.
641 static function getNotifications($group_id) {
642 $mdb2 = getConnection();
645 $sql = "select c.id, c.cron_spec, c.email, c.report_condition, fr.name from tt_cron c
646 left join tt_fav_reports fr on (fr.id = c.report_id)
647 where c.group_id = $group_id and c.status = 1 and fr.status = 1";
648 $res = $mdb2->query($sql);
650 if (!is_a($res, 'PEAR_Error')) {
651 while ($val = $res->fetchRow()) {
659 // getMonthlyQuotas - obtains monthly quotas for a group.
660 static function getMonthlyQuotas($group_id) {
661 $mdb2 = getConnection();
664 $sql = "select year, month, minutes from tt_monthly_quotas where group_id = $group_id";
665 $res = $mdb2->query($sql);
667 if (!is_a($res, 'PEAR_Error')) {
668 while ($val = $res->fetchRow()) {
676 // The getInactiveGroups is a maintenance function that returns an array of inactive group ids (max 100).
677 static function getInactiveGroups() {
678 $inactive_groups = array();
679 $mdb2 = getConnection();
681 // Get all group ids for groups created or modified more than 8 months ago.
682 // $ts = date('Y-m-d', strtotime('-1 year'));
683 $ts = $mdb2->quote(date('Y-m-d', strtotime('-8 month')));
684 $sql = "select id from tt_groups where created < $ts and (modified is null or modified < $ts) order by id";
685 $res = $mdb2->query($sql);
688 if (!is_a($res, 'PEAR_Error')) {
689 while ($val = $res->fetchRow()) {
690 $group_id = $val['id'];
691 if (ttTeamHelper::isGroupActive($group_id) == false) {
693 $inactive_groups[] = $group_id;
694 // Limit the array size for perfomance by allowing this operation on small chunks only.
695 if ($count >= 100) break;
698 return $inactive_groups;
703 // The isGroupActive determines if a group is using Time Tracker or abandoned it.
704 static function isGroupActive($group_id) {
707 $mdb2 = getConnection();
708 $sql = "select id from tt_users where group_id = $group_id";
709 $res = $mdb2->query($sql);
710 if (is_a($res, 'PEAR_Error')) die($res->getMessage());
711 while ($val = $res->fetchRow()) {
712 $users[] = $val['id'];
714 $user_list = implode(',', $users); // This is a comma-separated list of user ids.
716 return false; // No users in group.
719 $ts = date('Y-m-d', strtotime('-2 years'));
720 $sql = "select count(*) as cnt from tt_log where user_id in ($user_list) and created > '$ts'";
721 $res = $mdb2->query($sql);
722 if (!is_a($res, 'PEAR_Error')) {
723 if ($val = $res->fetchRow()) {
724 $count = $val['cnt'];
729 return false; // No time entries for the last 2 years.
732 // We will consider a group inactive if it has 5 or less time entries made more than 1 year ago.
733 $count_last_year = 0;
734 $ts = date('Y-m-d', strtotime('-1 year'));
735 $sql = "select count(*) as cnt from tt_log where user_id in ($user_list) and created > '$ts'";
736 $res = $mdb2->query($sql);
737 if (!is_a($res, 'PEAR_Error')) {
738 if ($val = $res->fetchRow()) {
739 $count_last_year = $val['cnt'];
741 if ($count_last_year == 0)
742 return false; // No time entries for the last year and only a few entries before that.
748 // The delete function permanently deletes all data for a group.
749 static function delete($group_id) {
750 $mdb2 = getConnection();
753 $sql = "select id from tt_users where group_id = $group_id";
754 $res = $mdb2->query($sql);
755 if (is_a($res, 'PEAR_Error')) return false;
756 while ($val = $res->fetchRow()) {
757 $user_id = $val['id'];
758 if (!ttUserHelper::delete($user_id)) return false;
762 if (!ttTeamHelper::deleteTasks($group_id)) return false;
764 // Delete client to project binds.
765 $sql = "delete from tt_client_project_binds where client_id in (select id from tt_clients where group_id = $group_id)";
766 $affected = $mdb2->exec($sql);
767 if (is_a($affected, 'PEAR_Error')) return false;
770 $sql = "delete from tt_projects where group_id = $group_id";
771 $affected = $mdb2->exec($sql);
772 if (is_a($affected, 'PEAR_Error')) return false;
775 $sql = "delete from tt_clients where group_id = $group_id";
776 $affected = $mdb2->exec($sql);
777 if (is_a($affected, 'PEAR_Error')) return false;
780 $sql = "delete from tt_invoices where group_id = $group_id";
781 $affected = $mdb2->exec($sql);
782 if (is_a($affected, 'PEAR_Error')) return false;
784 // Delete custom fields.
785 if (!ttTeamHelper::deleteCustomFields($group_id)) return false;
788 $sql = "delete from tt_roles where group_id = $group_id";
789 $affected = $mdb2->exec($sql);
790 if (is_a($affected, 'PEAR_Error')) return false;
793 $sql = "delete from tt_groups where id = $group_id";
794 $affected = $mdb2->exec($sql);
795 if (is_a($affected, 'PEAR_Error')) return false;
800 // The deleteTasks deletes all tasks and task binds for an inactive group.
801 static function deleteTasks($group_id) {
802 $mdb2 = getConnection();
803 $sql = "select id from tt_tasks where group_id = $group_id";
804 $res = $mdb2->query($sql);
805 if (is_a($res, 'PEAR_Error')) return false;
807 while ($val = $res->fetchRow()) {
809 // Delete task binds.
810 $task_id = $val['id'];
811 $sql = "delete from tt_project_task_binds where task_id = $task_id";
812 $affected = $mdb2->exec($sql);
813 if (is_a($affected, 'PEAR_Error')) return false;
816 $sql = "delete from tt_tasks where id = $task_id";
817 $affected = $mdb2->exec($sql);
818 if (is_a($affected, 'PEAR_Error')) return false;
824 // The deleteCustomFields cleans up tt_custom_field_log, tt_custom_field_options and tt_custom_fields tables for an inactive group.
825 static function deleteCustomFields($group_id) {
826 $mdb2 = getConnection();
827 $sql = "select id from tt_custom_fields where group_id = $group_id";
828 $res = $mdb2->query($sql);
829 if (is_a($res, 'PEAR_Error')) return false;
831 while ($val = $res->fetchRow()) {
832 $field_id = $val['id'];
834 // Clean up tt_custom_field_log.
835 $sql = "delete from tt_custom_field_log where field_id = $field_id";
836 $affected = $mdb2->exec($sql);
837 if (is_a($affected, 'PEAR_Error')) return false;
839 // Clean up tt_custom_field_options.
840 $sql = "delete from tt_custom_field_options where field_id = $field_id";
841 $affected = $mdb2->exec($sql);
842 if (is_a($affected, 'PEAR_Error')) return false;
844 // Delete custom field.
845 $sql = "delete from tt_custom_fields where id = $field_id";
846 $affected = $mdb2->exec($sql);
847 if (is_a($affected, 'PEAR_Error')) return false;