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->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 $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 markDeleted function marks the team and everything in it as deleted.
692 static function markDeleted($team_id) {
694 // Iterate through team users and mark them as deleted.
695 $users = ttTeamHelper::getAllUsers($team_id);
696 foreach ($users as $one_user) {
697 if (!ttUserHelper::markDeleted($one_user['id'])) return false;
700 // Mark tasks deleted.
701 if (!ttTeamHelper::markTasksDeleted($team_id)) return false;
703 $mdb2 = getConnection();
705 // Mark roles deleted.
706 $sql = "update tt_roles set status = NULL where team_id = $team_id";
707 $affected = $mdb2->exec($sql);
708 if (is_a($affected, 'PEAR_Error')) return false;
710 // Mark projects deleted.
711 $sql = "update tt_projects set status = NULL where team_id = $team_id";
712 $affected = $mdb2->exec($sql);
713 if (is_a($affected, 'PEAR_Error')) return false;
715 // Mark clients deleted.
716 $sql = "update tt_clients set status = NULL where team_id = $team_id";
717 $affected = $mdb2->exec($sql);
718 if (is_a($affected, 'PEAR_Error')) return false;
720 // Mark custom fields deleted.
721 $sql = "update tt_custom_fields set status = NULL where team_id = $team_id";
722 $affected = $mdb2->exec($sql);
723 if (is_a($affected, 'PEAR_Error')) return false;
725 // Mark team deleted.
726 $sql = "update tt_teams set status = NULL where id = $team_id";
727 $affected = $mdb2->exec($sql);
728 if (is_a($affected, 'PEAR_Error')) return false;
733 // The getTeamDetails function returns team details.
734 static function getTeamDetails($team_id) {
736 $mdb2 = getConnection();
738 $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
740 inner join tt_users u on (u.team_id = t.id)
741 inner join tt_roles r on (r.id = u.role_id and r.rank = 512)
742 where t.id = $team_id";
744 $res = $mdb2->query($sql);
745 if (!is_a($res, 'PEAR_Error')) {
746 $val = $res->fetchRow();
753 // The insert function creates a new team.
754 static function insert($fields) {
756 $mdb2 = getConnection();
758 // Start with team name and currency.
759 $columns = 'name, currency';
760 $values = $mdb2->quote(trim($fields['name'])).', '.$mdb2->quote(trim($fields['currency']));
762 if ($fields['decimal_mark']) {
763 $columns .= ', decimal_mark';
764 $values .= ', '.$mdb2->quote($fields['decimal_mark']);
767 $lang = $fields['lang'];
772 $columns .= ', lang';
773 $values .= ', '.$mdb2->quote($lang);
775 if ($fields['date_format'] || defined('DATE_FORMAT_DEFAULT')) {
776 $date_format = $fields['date_format'] ? $fields['date_format'] : DATE_FORMAT_DEFAULT;
777 $columns .= ', date_format';
778 $values .= ', '.$mdb2->quote($date_format);
781 if ($fields['time_format'] || defined('TIME_FORMAT_DEFAULT')) {
782 $time_format = $fields['time_format'] ? $fields['time_format'] : TIME_FORMAT_DEFAULT;
783 $columns .= ', time_format';
784 $values .= ', '.$mdb2->quote($time_format);
787 if ($fields['week_start'] || defined('WEEK_START_DEFAULT')) {
788 $week_start = $fields['week_start'] ? $fields['week_start'] : WEEK_START_DEFAULT;
789 $columns .= ', week_start';
790 $values .= ', '.(int)$week_start;
793 if ($fields['tracking_mode']) {
794 $columns .= ', tracking_mode';
795 $values .= ', '.(int)$fields['tracking_mode'];
798 if ($fields['project_required']) {
799 $columns .= ', project_required';
800 $values .= ', '.(int)$fields['project_required'];
803 if ($fields['task_required']) {
804 $columns .= ', task_required';
805 $values .= ', '.(int)$fields['task_required'];
808 if ($fields['record_type']) {
809 $columns .= ', record_type';
810 $values .= ', '.(int)$fields['record_type'];
813 if ($fields['bcc_email']) {
814 $columns .= ', bcc_email';
815 $values .= ', '.$mdb2->quote($fields['bcc_email']);
818 if ($fields['plugins']) {
819 $columns .= ', plugins';
820 $values .= ', '.$mdb2->quote($fields['plugins']);
823 if ($fields['lock_spec']) {
824 $columns .= ', lock_spec';
825 $values .= ', '.$mdb2->quote($fields['lock_spec']);
828 if ($fields['workday_minutes']) {
829 $columns .= ', workday_minutes';
830 $values .= ', '.(int)$fields['workday_minutes'];
833 if ($fields['config']) {
834 $columns .= ', config';
835 $values .= ', '.$mdb2->quote($fields['config']);
838 $sql = "insert into tt_teams ($columns) values($values)";
839 $affected = $mdb2->exec($sql);
841 if (!is_a($affected, 'PEAR_Error')) {
842 $team_id = $mdb2->lastInsertID('tt_teams', 'id');
849 // The update function updates team information.
850 static function update($team_id, $fields)
853 $mdb2 = getConnection();
854 $name_part = 'name = '.$mdb2->quote($fields['name']);
857 $decimal_mark_part = '';
858 $date_format_part = '';
859 $time_format_part = '';
860 $week_start_part = '';
861 $tracking_mode_part = '';
862 $task_required_part = ' , task_required = '.(int) $fields['task_required'];
863 $record_type_part = '';
864 $bcc_email_part = '';
867 $lock_spec_part = '';
868 $workday_minutes_part = '';
870 if (isset($fields['currency'])) $currency_part = ', currency = '.$mdb2->quote($fields['currency']);
871 if (isset($fields['lang'])) $lang_part = ', lang = '.$mdb2->quote($fields['lang']);
872 if (isset($fields['decimal_mark'])) $decimal_mark_part = ', decimal_mark = '.$mdb2->quote($fields['decimal_mark']);
873 if (isset($fields['date_format'])) $date_format_part = ', date_format = '.$mdb2->quote($fields['date_format']);
874 if (isset($fields['time_format'])) $time_format_part = ', time_format = '.$mdb2->quote($fields['time_format']);
875 if (isset($fields['week_start'])) $week_start_part = ', week_start = '.(int) $fields['week_start'];
876 if (isset($fields['tracking_mode'])) $tracking_mode_part = ', tracking_mode = '.(int) $fields['tracking_mode'];
877 if (isset($fields['record_type'])) $record_type_part = ', record_type = '.(int) $fields['record_type'];
878 if (isset($fields['bcc_email'])) $bcc_email_part = ', bcc_email = '.$mdb2->quote($fields['bcc_email']);
879 if (isset($fields['plugins'])) $plugins_part = ', plugins = '.$mdb2->quote($fields['plugins']);
880 if (isset($fields['config'])) $config_part = ', config = '.$mdb2->quote($fields['config']);
881 if (isset($fields['lock_spec'])) $lock_spec_part = ', lock_spec = '.$mdb2->quote($fields['lock_spec']);
882 if (isset($fields['workday_minutes'])) $workday_minutes_part = ', workday_minutes = '.$mdb2->quote($fields['workday_minutes']);
883 $modified_part = ', modified = now(), modified_ip = '.$mdb2->quote($_SERVER['REMOTE_ADDR']).', modified_by = '.$mdb2->quote($user->id);
885 $sql = "update tt_teams set $name_part $currency_part $lang_part $decimal_mark_part
886 $date_format_part $time_format_part $week_start_part $tracking_mode_part $task_required_part $record_type_part
887 $bcc_email_part $plugins_part $config_part $lock_spec_part $workday_minutes_part $modified_part where id = $team_id";
888 $affected = $mdb2->exec($sql);
889 if (is_a($affected, 'PEAR_Error')) return false;
894 // The getInactiveTeams is a maintenance function that returns an array of inactive team ids (max 100).
895 static function getInactiveTeams() {
896 $inactive_teams = array();
897 $mdb2 = getConnection();
899 // Get all team ids for teams created or modified more than 8 months ago.
900 // $ts = date('Y-m-d', strtotime('-1 year'));
901 $ts = $mdb2->quote(date('Y-m-d', strtotime('-8 month')));
902 $sql = "select id from tt_teams where created < $ts and (modified is null or modified < $ts) order by id";
903 $res = $mdb2->query($sql);
906 if (!is_a($res, 'PEAR_Error')) {
907 while ($val = $res->fetchRow()) {
908 $team_id = $val['id'];
909 if (ttTeamHelper::isTeamActive($team_id) == false) {
911 $inactive_teams[] = $team_id;
912 // Limit the array size for perfomance by allowing this operation on small chunks only.
913 if ($count >= 100) break;
916 return $inactive_teams;
921 // The isTeamActive determines if a team is using Time Tracker or abandoned it.
922 static function isTeamActive($team_id) {
925 $mdb2 = getConnection();
926 $sql = "select id from tt_users where team_id = $team_id";
927 $res = $mdb2->query($sql);
928 if (is_a($res, 'PEAR_Error')) die($res->getMessage());
929 while ($val = $res->fetchRow()) {
930 $users[] = $val['id'];
932 $user_list = implode(',', $users); // This is a comma-separated list of user ids.
934 return false; // No users in team.
937 $ts = date('Y-m-d', strtotime('-2 years'));
938 $sql = "select count(*) as cnt from tt_log where user_id in ($user_list) and created > '$ts'";
939 $res = $mdb2->query($sql);
940 if (!is_a($res, 'PEAR_Error')) {
941 if ($val = $res->fetchRow()) {
942 $count = $val['cnt'];
947 return false; // No time entries for the last 2 years.
950 // We will consider a team inactive if it has 5 or less time entries made more than 1 year ago.
951 $count_last_year = 0;
952 $ts = date('Y-m-d', strtotime('-1 year'));
953 $sql = "select count(*) as cnt from tt_log where user_id in ($user_list) and created > '$ts'";
954 $res = $mdb2->query($sql);
955 if (!is_a($res, 'PEAR_Error')) {
956 if ($val = $res->fetchRow()) {
957 $count_last_year = $val['cnt'];
959 if ($count_last_year == 0)
960 return false; // No time entries for the last year and only a few entries before that.
966 // The delete function permanently deletes all data for a team.
967 static function delete($team_id) {
968 $mdb2 = getConnection();
971 $sql = "select id from tt_users where team_id = $team_id";
972 $res = $mdb2->query($sql);
973 if (is_a($res, 'PEAR_Error')) return false;
974 while ($val = $res->fetchRow()) {
975 $user_id = $val['id'];
976 if (!ttUserHelper::delete($user_id)) return false;
980 if (!ttTeamHelper::deleteTasks($team_id)) return false;
982 // Delete client to project binds.
983 $sql = "delete from tt_client_project_binds where client_id in (select id from tt_clients where team_id = $team_id)";
984 $affected = $mdb2->exec($sql);
985 if (is_a($affected, 'PEAR_Error')) return false;
988 $sql = "delete from tt_projects where team_id = $team_id";
989 $affected = $mdb2->exec($sql);
990 if (is_a($affected, 'PEAR_Error')) return false;
993 $sql = "delete from tt_clients where team_id = $team_id";
994 $affected = $mdb2->exec($sql);
995 if (is_a($affected, 'PEAR_Error')) return false;
998 $sql = "delete from tt_invoices where team_id = $team_id";
999 $affected = $mdb2->exec($sql);
1000 if (is_a($affected, 'PEAR_Error')) return false;
1002 // Delete custom fields.
1003 if (!ttTeamHelper::deleteCustomFields($team_id)) return false;
1006 $sql = "delete from tt_roles where team_id = $team_id";
1007 $affected = $mdb2->exec($sql);
1008 if (is_a($affected, 'PEAR_Error')) return false;
1011 $sql = "delete from tt_teams where id = $team_id";
1012 $affected = $mdb2->exec($sql);
1013 if (is_a($affected, 'PEAR_Error')) return false;
1018 // The markTasksDeleted deletes task binds and marks the tasks as deleted for a team.
1019 static function markTasksDeleted($team_id) {
1020 $mdb2 = getConnection();
1021 $sql = "select id from tt_tasks where team_id = $team_id";
1022 $res = $mdb2->query($sql);
1023 if (is_a($res, 'PEAR_Error')) return false;
1025 while ($val = $res->fetchRow()) {
1027 // Delete task binds.
1028 $task_id = $val['id'];
1029 $sql = "delete from tt_project_task_binds where task_id = $task_id";
1030 $affected = $mdb2->exec($sql);
1031 if (is_a($affected, 'PEAR_Error')) return false;
1033 // Mark task as deleted.
1034 $sql = "update tt_tasks set status = NULL where id = $task_id";
1035 $affected = $mdb2->exec($sql);
1036 if (is_a($affected, 'PEAR_Error')) return false;
1042 // The deleteTasks deletes all tasks and task binds for an inactive team.
1043 static function deleteTasks($team_id) {
1044 $mdb2 = getConnection();
1045 $sql = "select id from tt_tasks where team_id = $team_id";
1046 $res = $mdb2->query($sql);
1047 if (is_a($res, 'PEAR_Error')) return false;
1049 while ($val = $res->fetchRow()) {
1051 // Delete task binds.
1052 $task_id = $val['id'];
1053 $sql = "delete from tt_project_task_binds where task_id = $task_id";
1054 $affected = $mdb2->exec($sql);
1055 if (is_a($affected, 'PEAR_Error')) return false;
1058 $sql = "delete from tt_tasks where id = $task_id";
1059 $affected = $mdb2->exec($sql);
1060 if (is_a($affected, 'PEAR_Error')) return false;
1066 // The deleteCustomFields cleans up tt_custom_field_log, tt_custom_field_options and tt_custom_fields tables for an inactive team.
1067 static function deleteCustomFields($team_id) {
1068 $mdb2 = getConnection();
1069 $sql = "select id from tt_custom_fields where team_id = $team_id";
1070 $res = $mdb2->query($sql);
1071 if (is_a($res, 'PEAR_Error')) return false;
1073 while ($val = $res->fetchRow()) {
1074 $field_id = $val['id'];
1076 // Clean up tt_custom_field_log.
1077 $sql = "delete from tt_custom_field_log where field_id = $field_id";
1078 $affected = $mdb2->exec($sql);
1079 if (is_a($affected, 'PEAR_Error')) return false;
1081 // Clean up tt_custom_field_options.
1082 $sql = "delete from tt_custom_field_options where field_id = $field_id";
1083 $affected = $mdb2->exec($sql);
1084 if (is_a($affected, 'PEAR_Error')) return false;
1086 // Delete custom field.
1087 $sql = "delete from tt_custom_fields where id = $field_id";
1088 $affected = $mdb2->exec($sql);
1089 if (is_a($affected, 'PEAR_Error')) return false;
1095 // enablePlugin either enables or disables a specific plugin for team.
1096 static function enablePlugin($plugin, $enable = true)
1099 if (!$user->can('manage_features'))
1102 $plugin_array = explode(',', $user->plugins);
1103 if ($enable && !in_array($plugin, $plugin_array))
1104 $plugin_array[] = $plugin; // Add plugin to array.
1106 if (!$enable && in_array($plugin, $plugin_array)) {
1107 $key = array_search($plugin, $plugin_array);
1109 unset($plugin_array[$key]); // Remove plugin from array.
1112 $plugins = implode(',', $plugin_array);
1113 if ($plugins != $user->plugins) {
1114 if (!ttTeamHelper::update($user->team_id, array('name' => $user->team,'plugins' => $plugins)))
1116 $user->plugins = $plugins;