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 teams.
36 // The getUserCount function returns number of people in team.
37 static function getUserCount($team_id) {
38 $mdb2 = getConnection();
40 $sql = "select count(id) as cnt from tt_users where team_id = $team_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 team 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 team.
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.team_id = $user->team_id and u.status = 1 order by upper(u.name)";
80 $sql = "select id, name from tt_users where team_id = $user->team_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->getKey('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 $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.team_id = $user->team_id and u.status = 1 and r.rank < $user->rank";
112 $res = $mdb2->query($sql);
113 if (is_a($res, 'PEAR_Error'))
115 $val = $res->fetchRow();
116 if (!$val['id'] || !$val['role_id'])
120 $sql = "update tt_users set role_id = $user->role_id where id = $user_id and team_id = $user->team_id";
121 $affected = $mdb2->exec($sql);
122 if (is_a($affected, 'PEAR_Error')) return false;
125 $role_id = $val['role_id'];
126 $sql = "update tt_users set role_id = $role_id where id = $user->id and team_id = $user->team_id";
127 $affected = $mdb2->exec($sql);
128 if (is_a($affected, 'PEAR_Error')) return false;
133 // The getUsersForSwap obtains all users a current user can swap roles with.
134 static function getUsersForSwap() {
136 $mdb2 = getConnection();
138 $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.team_id = $user->team_id and u.status = 1 and r.rank < $user->rank order by upper(u.name)";
139 $res = $mdb2->query($sql);
140 $user_list = array();
141 if (is_a($res, 'PEAR_Error'))
143 while ($val = $res->fetchRow()) {
144 $isClient = in_array('track_own_time', explode(',', $val['rights'])) ? 0 : 1; // Clients do not have data entry right.
146 continue; // Skip adding clients.
153 // The getUsers obtains all active and inactive (but not deleted) users in a given team.
154 static function getUsers() {
156 $mdb2 = getConnection();
157 $sql = "select id, name from tt_users where team_id = $user->team_id and (status = 1 or status = 0) order by upper(name)";
158 $res = $mdb2->query($sql);
159 $user_list = array();
160 if (is_a($res, 'PEAR_Error'))
162 while ($val = $res->fetchRow()) {
168 // The getInactiveUsers obtains all inactive users in a given team.
169 static function getInactiveUsers($team_id, $all_fields = false) {
170 $mdb2 = getConnection();
173 $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.team_id = $team_id and u.status = 0 order by upper(u.name)";
175 $sql = "select id, name from tt_users where team_id = $team_id and status = 0 order by upper(name)";
176 $res = $mdb2->query($sql);
178 if (!is_a($res, 'PEAR_Error')) {
179 while ($val = $res->fetchRow()) {
187 // The getAllUsers obtains all users in a given team.
188 static function getAllUsers($team_id, $all_fields = false) {
189 $mdb2 = getConnection();
191 $sql = "select * from tt_users where team_id = $team_id order by upper(name)";
193 $sql = "select id, name from tt_users where team_id = $team_id order by upper(name)";
194 $res = $mdb2->query($sql);
196 if (!is_a($res, 'PEAR_Error')) {
197 while ($val = $res->fetchRow()) {
205 // getActiveProjects - returns an array of active projects for team.
206 static function getActiveProjects($team_id)
209 $mdb2 = getConnection();
211 $sql = "select id, name, description, tasks from tt_projects
212 where team_id = $team_id and status = 1 order by upper(name)";
213 $res = $mdb2->query($sql);
215 if (!is_a($res, 'PEAR_Error')) {
216 while ($val = $res->fetchRow()) {
223 // getInactiveProjects - returns an array of inactive projects for team.
224 static function getInactiveProjects($team_id)
227 $mdb2 = getConnection();
229 $sql = "select id, name, description, tasks from tt_projects
230 where team_id = $team_id and status = 0 order by upper(name)";
231 $res = $mdb2->query($sql);
233 if (!is_a($res, 'PEAR_Error')) {
234 while ($val = $res->fetchRow()) {
241 // The getAllProjects obtains all projects in a given team.
242 static function getAllProjects($team_id, $all_fields = false) {
243 $mdb2 = getConnection();
246 $sql = "select * from tt_projects where team_id = $team_id order by status, upper(name)";
248 $sql = "select id, name from tt_projects where team_id = $team_id order by status, upper(name)";
249 $res = $mdb2->query($sql);
251 if (!is_a($res, 'PEAR_Error')) {
252 while ($val = $res->fetchRow()) {
260 // getActiveTasks - returns an array of active tasks for team.
261 static function getActiveTasks($team_id)
264 $mdb2 = getConnection();
266 $sql = "select id, name, description from tt_tasks where team_id = $team_id and status = 1 order by upper(name)";
267 $res = $mdb2->query($sql);
269 if (!is_a($res, 'PEAR_Error')) {
270 while ($val = $res->fetchRow()) {
277 // getInactiveTasks - returns an array of inactive tasks for team.
278 static function getInactiveTasks($team_id)
281 $mdb2 = getConnection();
283 $sql = "select id, name, description from tt_tasks
284 where team_id = $team_id and status = 0 order by upper(name)";
285 $res = $mdb2->query($sql);
287 if (!is_a($res, 'PEAR_Error')) {
288 while ($val = $res->fetchRow()) {
295 // The getAllTasks obtains all tasks in a given team.
296 static function getAllTasks($team_id, $all_fields = false) {
297 $mdb2 = getConnection();
300 $sql = "select * from tt_tasks where team_id = $team_id order by status, upper(name)";
302 $sql = "select id, name from tt_tasks where team_id = $team_id order by status, upper(name)";
303 $res = $mdb2->query($sql);
305 if (!is_a($res, 'PEAR_Error')) {
306 while ($val = $res->fetchRow()) {
314 // getActiveRolesForUser - returns an array of relevant active roles for user with rank less than self.
315 // "Relevant" means that client roles are filtered out if Client plugin is disabled.
316 static function getActiveRolesForUser()
320 $mdb2 = getConnection();
322 $sql = "select id, name, description, rank, rights from tt_roles where team_id = $user->team_id and rank < $user->rank and status = 1 order by rank";
323 $res = $mdb2->query($sql);
325 if (!is_a($res, 'PEAR_Error')) {
326 while ($val = $res->fetchRow()) {
327 $val['is_client'] = in_array('track_own_time', explode(',', $val['rights'])) ? 0 : 1; // Clients do not have data entry right.
328 if ($val['is_client'] && !$user->isPluginEnabled('cl'))
329 continue; // Skip adding a client role.
336 // getActiveRoles - returns an array of active roles for team.
337 static function getActiveRoles($team_id)
340 $mdb2 = getConnection();
342 $sql = "select id, name, description, rank, rights from tt_roles where team_id = $team_id and status = 1 order by rank";
343 $res = $mdb2->query($sql);
345 if (!is_a($res, 'PEAR_Error')) {
346 while ($val = $res->fetchRow()) {
347 $val['is_client'] = in_array('track_own_time', explode(',', $val['rights'])) ? 0 : 1; // Clients do not have data entry right.
354 // getInactiveRoles - returns an array of inactive roles for team.
355 static function getInactiveRoles($team_id)
358 $mdb2 = getConnection();
360 $sql = "select id, name, rank, description from tt_roles
361 where team_id = $team_id and status = 0 order by rank";
362 $res = $mdb2->query($sql);
364 if (!is_a($res, 'PEAR_Error')) {
365 while ($val = $res->fetchRow()) {
372 // getInactiveRolesForUser - returns an array of relevant active roles for user with rank less than self.
373 // "Relevant" means that client roles are filtered out if Client plugin is disabled.
374 static function getInactiveRolesForUser()
378 $mdb2 = getConnection();
380 $sql = "select id, name, description, rank, rights from tt_roles where team_id = $user->team_id and rank < $user->rank and status = 0 order by rank";
381 $res = $mdb2->query($sql);
383 if (!is_a($res, 'PEAR_Error')) {
384 while ($val = $res->fetchRow()) {
385 $val['is_client'] = in_array('track_own_time', explode(',', $val['rights'])) ? 0 : 1; // Clients do not have data entry right.
386 if ($val['is_client'] && !$user->isPluginEnabled('cl'))
387 continue; // Skip adding a client role.
394 // The getActiveClients returns an array of active clients for team.
395 static function getActiveClients($team_id, $all_fields = false)
398 $mdb2 = getConnection();
401 $sql = "select * from tt_clients where team_id = $team_id and status = 1 order by upper(name)";
403 $sql = "select id, name from tt_clients where team_id = $team_id and status = 1 order by upper(name)";
405 $res = $mdb2->query($sql);
407 if (!is_a($res, 'PEAR_Error')) {
408 while ($val = $res->fetchRow()) {
415 // The getInactiveClients returns an array of inactive clients for team.
416 static function getInactiveClients($team_id, $all_fields = false)
419 $mdb2 = getConnection();
422 $sql = "select * from tt_clients where team_id = $team_id and status = 0 order by upper(name)";
424 $sql = "select id, name from tt_clients where team_id = $team_id and status = 0 order by upper(name)";
426 $res = $mdb2->query($sql);
428 if (!is_a($res, 'PEAR_Error')) {
429 while ($val = $res->fetchRow()) {
436 // The getAllClients obtains all clients in a given team.
437 static function getAllClients($team_id, $all_fields = false) {
438 $mdb2 = getConnection();
441 $sql = "select * from tt_clients where team_id = $team_id order by status, upper(name)";
443 $sql = "select id, name from tt_clients where team_id = $team_id order by status, upper(name)";
445 $res = $mdb2->query($sql);
447 if (!is_a($res, 'PEAR_Error')) {
448 while ($val = $res->fetchRow()) {
456 // The getActiveInvoices returns an array of active invoices for team.
457 static function getActiveInvoices($localizeDates = true)
460 $addPaidStatus = $user->isPluginEnabled('ps');
463 $mdb2 = getConnection();
465 if ($user->isClient())
466 $client_part = " and i.client_id = $user->client_id";
468 $sql = "select i.id, i.name, i.date, i.client_id, i.status, c.name as client_name from tt_invoices i
469 left join tt_clients c on (c.id = i.client_id)
470 where i.status = 1 and i.team_id = $user->team_id $client_part order by i.name";
471 $res = $mdb2->query($sql);
473 if (!is_a($res, 'PEAR_Error')) {
474 $dt = new DateAndTime(DB_DATEFORMAT);
475 while ($val = $res->fetchRow()) {
476 if ($localizeDates) {
477 $dt->parseVal($val['date']);
478 $val['date'] = $dt->toString($user->date_format);
481 $val['paid'] = ttInvoiceHelper::isPaid($val['id']);
488 // The getAllInvoices returns an array of all invoices for team.
489 static function getAllInvoices()
494 $mdb2 = getConnection();
496 $sql = "select * from tt_invoices where team_id = $user->team_id";
497 $res = $mdb2->query($sql);
499 if (!is_a($res, 'PEAR_Error')) {
500 $dt = new DateAndTime(DB_DATEFORMAT);
501 while ($val = $res->fetchRow()) {
508 // The getRecentInvoices returns an array of recent invoices (max 3) for a client.
509 static function getRecentInvoices($team_id, $client_id)
514 $mdb2 = getConnection();
516 $sql = "select i.id, i.name from tt_invoices i
517 left join tt_clients c on (c.id = i.client_id)
518 where i.team_id = $team_id and i.status = 1 and c.id = $client_id
519 order by i.id desc limit 3";
520 $res = $mdb2->query($sql);
522 if (!is_a($res, 'PEAR_Error')) {
523 $dt = new DateAndTime(DB_DATEFORMAT);
524 while ($val = $res->fetchRow()) {
531 // getUserToProjectBinds - obtains all user to project binds for a team.
532 static function getUserToProjectBinds($team_id) {
533 $mdb2 = getConnection();
536 $sql = "select * from tt_user_project_binds where user_id in (select id from tt_users where team_id = $team_id) order by user_id, status, project_id";
537 $res = $mdb2->query($sql);
539 if (!is_a($res, 'PEAR_Error')) {
540 while ($val = $res->fetchRow()) {
548 // The getAllCustomFields obtains all custom fields in a given team.
549 static function getAllCustomFields($team_id) {
550 $mdb2 = getConnection();
552 $sql = "select * from tt_custom_fields where team_id = $team_id order by status";
554 $res = $mdb2->query($sql);
556 if (!is_a($res, 'PEAR_Error')) {
557 while ($val = $res->fetchRow()) {
565 // The getAllCustomFieldOptions obtains all custom field options in a given team.
566 static function getAllCustomFieldOptions($team_id) {
567 $mdb2 = getConnection();
569 $sql = "select * from tt_custom_field_options where field_id in (select id from tt_custom_fields where team_id = $team_id) order by id";
571 $res = $mdb2->query($sql);
573 if (!is_a($res, 'PEAR_Error')) {
574 while ($val = $res->fetchRow()) {
582 // The getCustomFieldLog obtains all custom field log entries for a given team.
583 static function getCustomFieldLog($team_id) {
584 $mdb2 = getConnection();
586 $sql = "select * from tt_custom_field_log where field_id in (select id from tt_custom_fields where team_id = $team_id) order by id";
588 $res = $mdb2->query($sql);
590 if (!is_a($res, 'PEAR_Error')) {
591 while ($val = $res->fetchRow()) {
599 // getFavReports - obtains all favorite reports for all users in team.
600 static function getFavReports($team_id) {
601 $mdb2 = getConnection();
604 $sql = "select * from tt_fav_reports where user_id in (select id from tt_users where team_id = $team_id)";
605 $res = $mdb2->query($sql);
607 if (!is_a($res, 'PEAR_Error')) {
608 while ($val = $res->fetchRow()) {
616 // getExpenseItems - obtains all expense items for all users in team.
617 static function getExpenseItems($team_id) {
618 $mdb2 = getConnection();
621 $sql = "select * from tt_expense_items where user_id in (select id from tt_users where team_id = $team_id)";
622 $res = $mdb2->query($sql);
624 if (!is_a($res, 'PEAR_Error')) {
625 while ($val = $res->fetchRow()) {
633 // getPredefinedExpenses - obtains predefined expenses for team.
634 static function getPredefinedExpenses($team_id) {
636 $replaceDecimalMark = ('.' != $user->decimal_mark);
638 $mdb2 = getConnection();
641 $sql = "select id, name, cost from tt_predefined_expenses where team_id = $team_id";
642 $res = $mdb2->query($sql);
644 if (!is_a($res, 'PEAR_Error')) {
645 while ($val = $res->fetchRow()) {
646 if ($replaceDecimalMark)
647 $val['cost'] = str_replace('.', $user->decimal_mark, $val['cost']);
655 // getNotifications - obtains notification descriptions for team.
656 static function getNotifications($team_id) {
657 $mdb2 = getConnection();
660 $sql = "select c.id, c.cron_spec, c.email, c.report_condition, fr.name from tt_cron c
661 left join tt_fav_reports fr on (fr.id = c.report_id)
662 where c.team_id = $team_id and c.status = 1 and fr.status = 1";
663 $res = $mdb2->query($sql);
665 if (!is_a($res, 'PEAR_Error')) {
666 while ($val = $res->fetchRow()) {
674 // getMonthlyQuotas - obtains monthly quotas for team.
675 static function getMonthlyQuotas($team_id) {
676 $mdb2 = getConnection();
679 $sql = "select year, month, minutes from tt_monthly_quotas where team_id = $team_id";
680 $res = $mdb2->query($sql);
682 if (!is_a($res, 'PEAR_Error')) {
683 while ($val = $res->fetchRow()) {
691 // The getTeams function returns an array of all active teams on the server.
692 static function getTeams() {
694 $mdb2 = getConnection();
696 $sql = "select id, name, lang, timestamp from tt_teams where status = 1 order by id desc";
697 $res = $mdb2->query($sql);
699 if (!is_a($res, 'PEAR_Error')) {
700 while ($val = $res->fetchRow()) {
701 $val['date'] = substr($val['timestamp'], 0, 10); // Strip the time.
709 // The markDeleted function marks the team and everything in it as deleted.
710 static function markDeleted($team_id) {
712 // Iterate through team users and mark them as deleted.
713 $users = ttTeamHelper::getAllUsers($team_id);
714 foreach ($users as $one_user) {
715 if (!ttUserHelper::markDeleted($one_user['id'])) return false;
718 // Mark tasks deleted.
719 if (!ttTeamHelper::markTasksDeleted($team_id)) return false;
721 $mdb2 = getConnection();
723 // Mark roles deleted.
724 $sql = "update tt_roles set status = NULL where team_id = $team_id";
725 $affected = $mdb2->exec($sql);
726 if (is_a($affected, 'PEAR_Error')) return false;
728 // Mark projects deleted.
729 $sql = "update tt_projects set status = NULL where team_id = $team_id";
730 $affected = $mdb2->exec($sql);
731 if (is_a($affected, 'PEAR_Error')) return false;
733 // Mark clients deleted.
734 $sql = "update tt_clients set status = NULL where team_id = $team_id";
735 $affected = $mdb2->exec($sql);
736 if (is_a($affected, 'PEAR_Error')) return false;
738 // Mark custom fields deleted.
739 $sql = "update tt_custom_fields set status = NULL where team_id = $team_id";
740 $affected = $mdb2->exec($sql);
741 if (is_a($affected, 'PEAR_Error')) return false;
743 // Mark team deleted.
744 $sql = "update tt_teams set status = NULL where id = $team_id";
745 $affected = $mdb2->exec($sql);
746 if (is_a($affected, 'PEAR_Error')) return false;
751 // The getTeamDetails function returns team details.
752 static function getTeamDetails($team_id) {
754 $mdb2 = getConnection();
756 $sql = "select t.name as team_name, u.id as manager_id, u.name as manager_name, u.login as manager_login, u.email as manager_email
758 inner join tt_users u on (u.team_id = t.id)
759 inner join tt_roles r on (r.id = u.role_id and r.rank = 512)
760 where t.id = $team_id";
762 $res = $mdb2->query($sql);
763 if (!is_a($res, 'PEAR_Error')) {
764 $val = $res->fetchRow();
771 // The insert function creates a new team.
772 static function insert($fields) {
774 $mdb2 = getConnection();
776 // Start with team name and currency.
777 $columns = 'name, currency';
778 $values = $mdb2->quote(trim($fields['name'])).', '.$mdb2->quote(trim($fields['currency']));
780 if ($fields['decimal_mark']) {
781 $columns .= ', decimal_mark';
782 $values .= ', '.$mdb2->quote($fields['decimal_mark']);
785 $lang = $fields['lang'];
790 $columns .= ', lang';
791 $values .= ', '.$mdb2->quote($lang);
793 if ($fields['date_format'] || defined('DATE_FORMAT_DEFAULT')) {
794 $date_format = $fields['date_format'] ? $fields['date_format'] : DATE_FORMAT_DEFAULT;
795 $columns .= ', date_format';
796 $values .= ', '.$mdb2->quote($date_format);
799 if ($fields['time_format'] || defined('TIME_FORMAT_DEFAULT')) {
800 $time_format = $fields['time_format'] ? $fields['time_format'] : TIME_FORMAT_DEFAULT;
801 $columns .= ', time_format';
802 $values .= ', '.$mdb2->quote($time_format);
805 if ($fields['week_start'] || defined('WEEK_START_DEFAULT')) {
806 $week_start = $fields['week_start'] ? $fields['week_start'] : WEEK_START_DEFAULT;
807 $columns .= ', week_start';
808 $values .= ', '.(int)$week_start;
811 if ($fields['tracking_mode']) {
812 $columns .= ', tracking_mode';
813 $values .= ', '.(int)$fields['tracking_mode'];
816 if ($fields['project_required']) {
817 $columns .= ', project_required';
818 $values .= ', '.(int)$fields['project_required'];
821 if ($fields['task_required']) {
822 $columns .= ', task_required';
823 $values .= ', '.(int)$fields['task_required'];
826 if ($fields['record_type']) {
827 $columns .= ', record_type';
828 $values .= ', '.(int)$fields['record_type'];
831 if ($fields['bcc_email']) {
832 $columns .= ', bcc_email';
833 $values .= ', '.$mdb2->quote($fields['bcc_email']);
836 if ($fields['plugins']) {
837 $columns .= ', plugins';
838 $values .= ', '.$mdb2->quote($fields['plugins']);
841 if ($fields['lock_spec']) {
842 $columns .= ', lock_spec';
843 $values .= ', '.$mdb2->quote($fields['lock_spec']);
846 if ($fields['workday_minutes']) {
847 $columns .= ', workday_minutes';
848 $values .= ', '.(int)$fields['workday_minutes'];
851 if ($fields['config']) {
852 $columns .= ', config';
853 $values .= ', '.$mdb2->quote($fields['config']);
856 $sql = "insert into tt_teams ($columns) values($values)";
857 $affected = $mdb2->exec($sql);
859 if (!is_a($affected, 'PEAR_Error')) {
860 $team_id = $mdb2->lastInsertID('tt_teams', 'id');
867 // The update function updates team information.
868 static function update($team_id, $fields)
871 $mdb2 = getConnection();
872 $name_part = 'name = '.$mdb2->quote($fields['name']);
875 $decimal_mark_part = '';
876 $date_format_part = '';
877 $time_format_part = '';
878 $week_start_part = '';
879 $tracking_mode_part = '';
880 $task_required_part = ' , task_required = '.(int) $fields['task_required'];
881 $record_type_part = '';
882 $bcc_email_part = '';
885 $lock_spec_part = '';
886 $workday_minutes_part = '';
888 if (isset($fields['currency'])) $currency_part = ', currency = '.$mdb2->quote($fields['currency']);
889 if (isset($fields['lang'])) $lang_part = ', lang = '.$mdb2->quote($fields['lang']);
890 if (isset($fields['decimal_mark'])) $decimal_mark_part = ', decimal_mark = '.$mdb2->quote($fields['decimal_mark']);
891 if (isset($fields['date_format'])) $date_format_part = ', date_format = '.$mdb2->quote($fields['date_format']);
892 if (isset($fields['time_format'])) $time_format_part = ', time_format = '.$mdb2->quote($fields['time_format']);
893 if (isset($fields['week_start'])) $week_start_part = ', week_start = '.(int) $fields['week_start'];
894 if (isset($fields['tracking_mode'])) $tracking_mode_part = ', tracking_mode = '.(int) $fields['tracking_mode'];
895 if (isset($fields['record_type'])) $record_type_part = ', record_type = '.(int) $fields['record_type'];
896 if (isset($fields['bcc_email'])) $bcc_email_part = ', bcc_email = '.$mdb2->quote($fields['bcc_email']);
897 if (isset($fields['plugins'])) $plugins_part = ', plugins = '.$mdb2->quote($fields['plugins']);
898 if (isset($fields['config'])) $config_part = ', config = '.$mdb2->quote($fields['config']);
899 if (isset($fields['lock_spec'])) $lock_spec_part = ', lock_spec = '.$mdb2->quote($fields['lock_spec']);
900 if (isset($fields['workday_minutes'])) $workday_minutes_part = ', workday_minutes = '.$mdb2->quote($fields['workday_minutes']);
901 $modified_part = ', modified = now(), modified_ip = '.$mdb2->quote($_SERVER['REMOTE_ADDR']).', modified_by = '.$mdb2->quote($user->id);
903 $sql = "update tt_teams set $name_part $currency_part $lang_part $decimal_mark_part
904 $date_format_part $time_format_part $week_start_part $tracking_mode_part $task_required_part $record_type_part
905 $bcc_email_part $plugins_part $config_part $lock_spec_part $workday_minutes_part $modified_part where id = $team_id";
906 $affected = $mdb2->exec($sql);
907 if (is_a($affected, 'PEAR_Error')) return false;
912 // The getInactiveTeams is a maintenance function that returns an array of inactive team ids (max 100).
913 static function getInactiveTeams() {
914 $inactive_teams = array();
915 $mdb2 = getConnection();
917 // Get all team ids for teams created or modified more than 6 months ago.
918 // $ts = date('Y-m-d', strtotime('-1 year'));
919 $ts = date('Y-m-d', strtotime('-6 month'));
920 $sql = "select id from tt_teams where timestamp < '$ts' order by id";
921 $res = $mdb2->query($sql);
924 if (!is_a($res, 'PEAR_Error')) {
925 while ($val = $res->fetchRow()) {
926 $team_id = $val['id'];
927 if (ttTeamHelper::isTeamActive($team_id) == false) {
929 $inactive_teams[] = $team_id;
930 // Limit the array size for perfomance by allowing this operation on small chunks only.
931 if ($count >= 100) break;
934 return $inactive_teams;
939 // The isTeamActive determines if a team is using Time Tracker or abandoned it.
940 static function isTeamActive($team_id) {
943 $mdb2 = getConnection();
944 $sql = "select id from tt_users where team_id = $team_id";
945 $res = $mdb2->query($sql);
946 if (is_a($res, 'PEAR_Error')) die($res->getMessage());
947 while ($val = $res->fetchRow()) {
948 $users[] = $val['id'];
950 $user_list = implode(',', $users); // This is a comma-separated list of user ids.
952 return false; // No users in team.
955 $ts = date('Y-m-d', strtotime('-2 years'));
956 $sql = "select count(*) as cnt from tt_log where user_id in ($user_list) and timestamp > '$ts'";
957 $res = $mdb2->query($sql);
958 if (!is_a($res, 'PEAR_Error')) {
959 if ($val = $res->fetchRow()) {
960 $count = $val['cnt'];
965 return false; // No time entries for the last 2 years.
968 // We will consider a team inactive if it has 5 or less time entries made more than 1 year ago.
969 $count_last_year = 0;
970 $ts = date('Y-m-d', strtotime('-1 year'));
971 $sql = "select count(*) as cnt from tt_log where user_id in ($user_list) and timestamp > '$ts'";
972 $res = $mdb2->query($sql);
973 if (!is_a($res, 'PEAR_Error')) {
974 if ($val = $res->fetchRow()) {
975 $count_last_year = $val['cnt'];
977 if ($count_last_year == 0)
978 return false; // No time entries for the last year and only a few entries before that.
984 // The delete function permanently deletes all data for a team.
985 static function delete($team_id) {
986 $mdb2 = getConnection();
989 $sql = "select id from tt_users where team_id = $team_id";
990 $res = $mdb2->query($sql);
991 if (is_a($res, 'PEAR_Error')) return false;
992 while ($val = $res->fetchRow()) {
993 $user_id = $val['id'];
994 if (!ttUserHelper::delete($user_id)) return false;
998 if (!ttTeamHelper::deleteTasks($team_id)) return false;
1000 // Delete client to project binds.
1001 $sql = "delete from tt_client_project_binds where client_id in (select id from tt_clients where team_id = $team_id)";
1002 $affected = $mdb2->exec($sql);
1003 if (is_a($affected, 'PEAR_Error')) return false;
1006 $sql = "delete from tt_projects where team_id = $team_id";
1007 $affected = $mdb2->exec($sql);
1008 if (is_a($affected, 'PEAR_Error')) return false;
1011 $sql = "delete from tt_clients where team_id = $team_id";
1012 $affected = $mdb2->exec($sql);
1013 if (is_a($affected, 'PEAR_Error')) return false;
1016 $sql = "delete from tt_invoices where team_id = $team_id";
1017 $affected = $mdb2->exec($sql);
1018 if (is_a($affected, 'PEAR_Error')) return false;
1020 // Delete custom fields.
1021 if (!ttTeamHelper::deleteCustomFields($team_id)) return false;
1024 $sql = "delete from tt_roles where team_id = $team_id";
1025 $affected = $mdb2->exec($sql);
1026 if (is_a($affected, 'PEAR_Error')) return false;
1029 $sql = "delete from tt_teams where id = $team_id";
1030 $affected = $mdb2->exec($sql);
1031 if (is_a($affected, 'PEAR_Error')) return false;
1036 // The markTasksDeleted deletes task binds and marks the tasks as deleted for a team.
1037 static function markTasksDeleted($team_id) {
1038 $mdb2 = getConnection();
1039 $sql = "select id from tt_tasks where team_id = $team_id";
1040 $res = $mdb2->query($sql);
1041 if (is_a($res, 'PEAR_Error')) return false;
1043 while ($val = $res->fetchRow()) {
1045 // Delete task binds.
1046 $task_id = $val['id'];
1047 $sql = "delete from tt_project_task_binds where task_id = $task_id";
1048 $affected = $mdb2->exec($sql);
1049 if (is_a($affected, 'PEAR_Error')) return false;
1051 // Mark task as deleted.
1052 $sql = "update tt_tasks set status = NULL where id = $task_id";
1053 $affected = $mdb2->exec($sql);
1054 if (is_a($affected, 'PEAR_Error')) return false;
1060 // The deleteTasks deletes all tasks and task binds for an inactive team.
1061 static function deleteTasks($team_id) {
1062 $mdb2 = getConnection();
1063 $sql = "select id from tt_tasks where team_id = $team_id";
1064 $res = $mdb2->query($sql);
1065 if (is_a($res, 'PEAR_Error')) return false;
1067 while ($val = $res->fetchRow()) {
1069 // Delete task binds.
1070 $task_id = $val['id'];
1071 $sql = "delete from tt_project_task_binds where task_id = $task_id";
1072 $affected = $mdb2->exec($sql);
1073 if (is_a($affected, 'PEAR_Error')) return false;
1076 $sql = "delete from tt_tasks where id = $task_id";
1077 $affected = $mdb2->exec($sql);
1078 if (is_a($affected, 'PEAR_Error')) return false;
1084 // The deleteCustomFields cleans up tt_custom_field_log, tt_custom_field_options and tt_custom_fields tables for an inactive team.
1085 static function deleteCustomFields($team_id) {
1086 $mdb2 = getConnection();
1087 $sql = "select id from tt_custom_fields where team_id = $team_id";
1088 $res = $mdb2->query($sql);
1089 if (is_a($res, 'PEAR_Error')) return false;
1091 while ($val = $res->fetchRow()) {
1092 $field_id = $val['id'];
1094 // Clean up tt_custom_field_log.
1095 $sql = "delete from tt_custom_field_log where field_id = $field_id";
1096 $affected = $mdb2->exec($sql);
1097 if (is_a($affected, 'PEAR_Error')) return false;
1099 // Clean up tt_custom_field_options.
1100 $sql = "delete from tt_custom_field_options where field_id = $field_id";
1101 $affected = $mdb2->exec($sql);
1102 if (is_a($affected, 'PEAR_Error')) return false;
1104 // Delete custom field.
1105 $sql = "delete from tt_custom_fields where id = $field_id";
1106 $affected = $mdb2->exec($sql);
1107 if (is_a($affected, 'PEAR_Error')) return false;
1113 // enablePlugin either enables or disables a specific plugin for team.
1114 static function enablePlugin($plugin, $enable = true)
1117 if (!$user->can('manage_features'))
1120 $plugin_array = explode(',', $user->plugins);
1121 if ($enable && !in_array($plugin, $plugin_array))
1122 $plugin_array[] = $plugin; // Add plugin to array.
1124 if (!$enable && in_array($plugin, $plugin_array)) {
1125 $key = array_search($plugin, $plugin_array);
1127 unset($plugin_array[$key]); // Remove plugin from array.
1130 $plugins = implode(',', $plugin_array);
1131 if ($plugins != $user->plugins) {
1132 if (!ttTeamHelper::update($user->team_id, array('name' => $user->team,'plugins' => $plugins)))
1134 $user->plugins = $plugins;