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) {
74 $mdb2 = getConnection();
76 if (isset($options['getAllFields']))
77 $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)";
79 $sql = "select id, name from tt_users where team_id = $user->team_id and status = 1 order by upper(name)";
80 $res = $mdb2->query($sql);
82 if (is_a($res, 'PEAR_Error'))
84 while ($val = $res->fetchRow()) {
88 if (isset($options['putSelfFirst'])) {
89 // Put own entry at the front.
90 $cnt = count($user_list);
91 for($i = 0; $i < $cnt; $i++) {
92 if ($user_list[$i]['id'] == $user->id) {
93 $self = $user_list[$i]; // Found self.
94 array_unshift($user_list, $self); // Put own entry at the front.
95 array_splice($user_list, $i+1, 1); // Remove duplicate.
102 // The getUsers obtains all active and inactive (but not deleted) users in a given team.
103 static function getUsers() {
105 $mdb2 = getConnection();
107 $sql = "select id, name from tt_users where team_id = $user->team_id and (status = 1 or status = 0) order by upper(name)";
108 $res = $mdb2->query($sql);
109 $user_list = array();
110 if (is_a($res, 'PEAR_Error'))
112 while ($val = $res->fetchRow()) {
119 // The getInactiveUsers obtains all inactive users in a given team.
120 static function getInactiveUsers($team_id, $all_fields = false) {
121 $mdb2 = getConnection();
124 $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)";
126 $sql = "select id, name from tt_users where team_id = $team_id and status = 0 order by upper(name)";
127 $res = $mdb2->query($sql);
129 if (!is_a($res, 'PEAR_Error')) {
130 while ($val = $res->fetchRow()) {
138 // The getAllUsers obtains all users in a given team.
139 static function getAllUsers($team_id, $all_fields = false) {
140 $mdb2 = getConnection();
143 $sql = "select * from tt_users where team_id = $team_id order by upper(name)";
145 $sql = "select id, name from tt_users where team_id = $team_id order by upper(name)";
146 $res = $mdb2->query($sql);
148 if (!is_a($res, 'PEAR_Error')) {
149 while ($val = $res->fetchRow()) {
157 // getActiveProjects - returns an array of active projects for team.
158 static function getActiveProjects($team_id)
161 $mdb2 = getConnection();
163 $sql = "select id, name, description, tasks from tt_projects
164 where team_id = $team_id and status = 1 order by upper(name)";
165 $res = $mdb2->query($sql);
167 if (!is_a($res, 'PEAR_Error')) {
168 while ($val = $res->fetchRow()) {
175 // getInactiveProjects - returns an array of inactive projects for team.
176 static function getInactiveProjects($team_id)
179 $mdb2 = getConnection();
181 $sql = "select id, name, description, tasks from tt_projects
182 where team_id = $team_id and status = 0 order by upper(name)";
183 $res = $mdb2->query($sql);
185 if (!is_a($res, 'PEAR_Error')) {
186 while ($val = $res->fetchRow()) {
193 // The getAllProjects obtains all projects in a given team.
194 static function getAllProjects($team_id, $all_fields = false) {
195 $mdb2 = getConnection();
198 $sql = "select * from tt_projects where team_id = $team_id order by status, upper(name)";
200 $sql = "select id, name from tt_projects where team_id = $team_id order by status, upper(name)";
201 $res = $mdb2->query($sql);
203 if (!is_a($res, 'PEAR_Error')) {
204 while ($val = $res->fetchRow()) {
212 // getActiveTasks - returns an array of active tasks for team.
213 static function getActiveTasks($team_id)
216 $mdb2 = getConnection();
218 $sql = "select id, name, description from tt_tasks where team_id = $team_id and status = 1 order by upper(name)";
219 $res = $mdb2->query($sql);
221 if (!is_a($res, 'PEAR_Error')) {
222 while ($val = $res->fetchRow()) {
229 // getInactiveTasks - returns an array of inactive tasks for team.
230 static function getInactiveTasks($team_id)
233 $mdb2 = getConnection();
235 $sql = "select id, name, description from tt_tasks
236 where team_id = $team_id and status = 0 order by upper(name)";
237 $res = $mdb2->query($sql);
239 if (!is_a($res, 'PEAR_Error')) {
240 while ($val = $res->fetchRow()) {
247 // The getAllTasks obtains all tasks in a given team.
248 static function getAllTasks($team_id, $all_fields = false) {
249 $mdb2 = getConnection();
252 $sql = "select * from tt_tasks where team_id = $team_id order by status, upper(name)";
254 $sql = "select id, name from tt_tasks where team_id = $team_id order by status, upper(name)";
255 $res = $mdb2->query($sql);
257 if (!is_a($res, 'PEAR_Error')) {
258 while ($val = $res->fetchRow()) {
266 // getActiveRolesForUser - returns an array of relevant active roles for user with rank less than self.
267 // "Relevant" means that client roles are filtered out if Client plugin is disabled.
268 static function getActiveRolesForUser()
272 $mdb2 = getConnection();
274 $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";
275 $res = $mdb2->query($sql);
277 if (!is_a($res, 'PEAR_Error')) {
278 while ($val = $res->fetchRow()) {
279 $val['is_client'] = in_array('track_own_time', explode(',', $val['rights'])) ? 0 : 1; // Clients do not have data entry right.
280 if ($val['is_client'] && !$user->isPluginEnabled('cl'))
281 continue; // Skip adding a client role.
288 // getActiveRoles - returns an array of active roles for team.
289 static function getActiveRoles($team_id)
292 $mdb2 = getConnection();
294 $sql = "select id, name, description, rank, rights from tt_roles where team_id = $team_id and status = 1 order by rank";
295 $res = $mdb2->query($sql);
297 if (!is_a($res, 'PEAR_Error')) {
298 while ($val = $res->fetchRow()) {
299 $val['is_client'] = in_array('track_own_time', explode(',', $val['rights'])) ? 0 : 1; // Clients do not have data entry right.
306 // getInactiveRoles - returns an array of inactive roles for team.
307 static function getInactiveRoles($team_id)
310 $mdb2 = getConnection();
312 $sql = "select id, name, rank, description from tt_roles
313 where team_id = $team_id and status = 0 order by rank";
314 $res = $mdb2->query($sql);
316 if (!is_a($res, 'PEAR_Error')) {
317 while ($val = $res->fetchRow()) {
324 // getAllRoles - obtains all roles defined for team.
325 static function getAllRoles($team_id) {
326 $mdb2 = getConnection();
329 $sql = "select * from tt_roles where team_id = $team_id";
330 $res = $mdb2->query($sql);
332 if (!is_a($res, 'PEAR_Error')) {
333 while ($val = $res->fetchRow()) {
341 // The getActiveClients returns an array of active clients for team.
342 static function getActiveClients($team_id, $all_fields = false)
345 $mdb2 = getConnection();
348 $sql = "select * from tt_clients where team_id = $team_id and status = 1 order by upper(name)";
350 $sql = "select id, name from tt_clients where team_id = $team_id and status = 1 order by upper(name)";
352 $res = $mdb2->query($sql);
354 if (!is_a($res, 'PEAR_Error')) {
355 while ($val = $res->fetchRow()) {
362 // The getInactiveClients returns an array of inactive clients for team.
363 static function getInactiveClients($team_id, $all_fields = false)
366 $mdb2 = getConnection();
369 $sql = "select * from tt_clients where team_id = $team_id and status = 0 order by upper(name)";
371 $sql = "select id, name from tt_clients where team_id = $team_id and status = 0 order by upper(name)";
373 $res = $mdb2->query($sql);
375 if (!is_a($res, 'PEAR_Error')) {
376 while ($val = $res->fetchRow()) {
383 // The getAllClients obtains all clients in a given team.
384 static function getAllClients($team_id, $all_fields = false) {
385 $mdb2 = getConnection();
388 $sql = "select * from tt_clients where team_id = $team_id order by status, upper(name)";
390 $sql = "select id, name from tt_clients where team_id = $team_id order by status, upper(name)";
392 $res = $mdb2->query($sql);
394 if (!is_a($res, 'PEAR_Error')) {
395 while ($val = $res->fetchRow()) {
403 // The getActiveInvoices returns an array of active invoices for team.
404 static function getActiveInvoices($localizeDates = true)
407 $addPaidStatus = $user->isPluginEnabled('ps');
410 $mdb2 = getConnection();
412 if ($user->isClient())
413 $client_part = " and i.client_id = $user->client_id";
415 $sql = "select i.id, i.name, i.date, i.client_id, i.status, c.name as client_name from tt_invoices i
416 left join tt_clients c on (c.id = i.client_id)
417 where i.status = 1 and i.team_id = $user->team_id $client_part order by i.name";
418 $res = $mdb2->query($sql);
420 if (!is_a($res, 'PEAR_Error')) {
421 $dt = new DateAndTime(DB_DATEFORMAT);
422 while ($val = $res->fetchRow()) {
423 if ($localizeDates) {
424 $dt->parseVal($val['date']);
425 $val['date'] = $dt->toString($user->date_format);
428 $val['paid'] = ttInvoiceHelper::isPaid($val['id']);
435 // The getAllInvoices returns an array of all invoices for team.
436 static function getAllInvoices()
441 $mdb2 = getConnection();
443 $sql = "select * from tt_invoices where team_id = $user->team_id";
444 $res = $mdb2->query($sql);
446 if (!is_a($res, 'PEAR_Error')) {
447 $dt = new DateAndTime(DB_DATEFORMAT);
448 while ($val = $res->fetchRow()) {
455 // The getRecentInvoices returns an array of recent invoices (max 3) for a client.
456 static function getRecentInvoices($team_id, $client_id)
461 $mdb2 = getConnection();
463 $sql = "select i.id, i.name from tt_invoices i
464 left join tt_clients c on (c.id = i.client_id)
465 where i.team_id = $team_id and i.status = 1 and c.id = $client_id
466 order by i.id desc limit 3";
467 $res = $mdb2->query($sql);
469 if (!is_a($res, 'PEAR_Error')) {
470 $dt = new DateAndTime(DB_DATEFORMAT);
471 while ($val = $res->fetchRow()) {
478 // getUserToProjectBinds - obtains all user to project binds for a team.
479 static function getUserToProjectBinds($team_id) {
480 $mdb2 = getConnection();
483 $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";
484 $res = $mdb2->query($sql);
486 if (!is_a($res, 'PEAR_Error')) {
487 while ($val = $res->fetchRow()) {
495 // The getAllCustomFields obtains all custom fields in a given team.
496 static function getAllCustomFields($team_id) {
497 $mdb2 = getConnection();
499 $sql = "select * from tt_custom_fields where team_id = $team_id order by status";
501 $res = $mdb2->query($sql);
503 if (!is_a($res, 'PEAR_Error')) {
504 while ($val = $res->fetchRow()) {
512 // The getAllCustomFieldOptions obtains all custom field options in a given team.
513 static function getAllCustomFieldOptions($team_id) {
514 $mdb2 = getConnection();
516 $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";
518 $res = $mdb2->query($sql);
520 if (!is_a($res, 'PEAR_Error')) {
521 while ($val = $res->fetchRow()) {
529 // The getCustomFieldLog obtains all custom field log entries for a given team.
530 static function getCustomFieldLog($team_id) {
531 $mdb2 = getConnection();
533 $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";
535 $res = $mdb2->query($sql);
537 if (!is_a($res, 'PEAR_Error')) {
538 while ($val = $res->fetchRow()) {
546 // getFavReports - obtains all favorite reports for all users in team.
547 static function getFavReports($team_id) {
548 $mdb2 = getConnection();
551 $sql = "select * from tt_fav_reports where user_id in (select id from tt_users where team_id = $team_id)";
552 $res = $mdb2->query($sql);
554 if (!is_a($res, 'PEAR_Error')) {
555 while ($val = $res->fetchRow()) {
563 // getExpenseItems - obtains all expense items for all users in team.
564 static function getExpenseItems($team_id) {
565 $mdb2 = getConnection();
568 $sql = "select * from tt_expense_items where user_id in (select id from tt_users where team_id = $team_id)";
569 $res = $mdb2->query($sql);
571 if (!is_a($res, 'PEAR_Error')) {
572 while ($val = $res->fetchRow()) {
580 // getPredefinedExpenses - obtains predefined expenses for team.
581 static function getPredefinedExpenses($team_id) {
583 $replaceDecimalMark = ('.' != $user->decimal_mark);
585 $mdb2 = getConnection();
588 $sql = "select id, name, cost from tt_predefined_expenses where team_id = $team_id";
589 $res = $mdb2->query($sql);
591 if (!is_a($res, 'PEAR_Error')) {
592 while ($val = $res->fetchRow()) {
593 if ($replaceDecimalMark)
594 $val['cost'] = str_replace('.', $user->decimal_mark, $val['cost']);
602 // getNotifications - obtains notification descriptions for team.
603 static function getNotifications($team_id) {
604 $mdb2 = getConnection();
607 $sql = "select c.id, c.cron_spec, c.email, c.report_condition, fr.name from tt_cron c
608 left join tt_fav_reports fr on (fr.id = c.report_id)
609 where c.team_id = $team_id and c.status = 1 and fr.status = 1";
610 $res = $mdb2->query($sql);
612 if (!is_a($res, 'PEAR_Error')) {
613 while ($val = $res->fetchRow()) {
621 // getMonthlyQuotas - obtains monthly quotas for team.
622 static function getMonthlyQuotas($team_id) {
623 $mdb2 = getConnection();
626 $sql = "select year, month, minutes from tt_monthly_quotas where team_id = $team_id";
627 $res = $mdb2->query($sql);
629 if (!is_a($res, 'PEAR_Error')) {
630 while ($val = $res->fetchRow()) {
638 // The getTeams function returns an array of all active teams on the server.
639 static function getTeams() {
641 $mdb2 = getConnection();
643 $sql = "select id, name, lang, timestamp from tt_teams where status = 1 order by id desc";
644 $res = $mdb2->query($sql);
646 if (!is_a($res, 'PEAR_Error')) {
647 while ($val = $res->fetchRow()) {
648 $val['date'] = substr($val['timestamp'], 0, 10); // Strip the time.
656 // The markDeleted function marks the team and everything in it as deleted.
657 static function markDeleted($team_id) {
659 // Iterate through team users and mark them as deleted.
660 $users = ttTeamHelper::getAllUsers($team_id);
661 foreach ($users as $one_user) {
662 if (!ttUserHelper::markDeleted($one_user['id'])) return false;
665 // Mark tasks deleted.
666 if (!ttTeamHelper::markTasksDeleted($team_id)) return false;
668 $mdb2 = getConnection();
670 // Mark projects deleted.
671 $sql = "update tt_projects set status = NULL where team_id = $team_id";
672 $affected = $mdb2->exec($sql);
673 if (is_a($affected, 'PEAR_Error')) return false;
675 // Mark clients deleted.
676 $sql = "update tt_clients set status = NULL where team_id = $team_id";
677 $affected = $mdb2->exec($sql);
678 if (is_a($affected, 'PEAR_Error')) return false;
680 // Mark custom fields deleted.
681 $sql = "update tt_custom_fields set status = NULL where team_id = $team_id";
682 $affected = $mdb2->exec($sql);
683 if (is_a($affected, 'PEAR_Error')) return false;
685 // Mark team deleted.
686 $sql = "update tt_teams set status = NULL where id = $team_id";
687 $affected = $mdb2->exec($sql);
688 if (is_a($affected, 'PEAR_Error')) return false;
693 // The getTeamDetails function returns team details.
694 static function getTeamDetails($team_id) {
696 $mdb2 = getConnection();
698 $role_manager = ROLE_MANAGER;
699 $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
701 inner join tt_users u on (u.team_id = t.id)
702 inner join tt_roles r on (r.id = u.role_id and r.rank = 512)
703 where t.id = $team_id";
705 $res = $mdb2->query($sql);
706 if (!is_a($res, 'PEAR_Error')) {
707 $val = $res->fetchRow();
714 // The insert function creates a new team.
715 static function insert($fields) {
717 $mdb2 = getConnection();
719 // Start with team name and currency.
720 $columns = 'name, currency';
721 $values = $mdb2->quote(trim($fields['name'])).', '.$mdb2->quote(trim($fields['currency']));
723 if ($fields['decimal_mark']) {
724 $columns .= ', decimal_mark';
725 $values .= ', '.$mdb2->quote($fields['decimal_mark']);
728 $lang = $fields['lang'];
733 $columns .= ', lang';
734 $values .= ', '.$mdb2->quote($lang);
736 if ($fields['date_format'] || defined('DATE_FORMAT_DEFAULT')) {
737 $date_format = $fields['date_format'] ? $fields['date_format'] : DATE_FORMAT_DEFAULT;
738 $columns .= ', date_format';
739 $values .= ', '.$mdb2->quote($date_format);
742 if ($fields['time_format'] || defined('TIME_FORMAT_DEFAULT')) {
743 $time_format = $fields['time_format'] ? $fields['time_format'] : TIME_FORMAT_DEFAULT;
744 $columns .= ', time_format';
745 $values .= ', '.$mdb2->quote($time_format);
748 if ($fields['week_start'] || defined('WEEK_START_DEFAULT')) {
749 $week_start = $fields['week_start'] ? $fields['week_start'] : WEEK_START_DEFAULT;
750 $columns .= ', week_start';
751 $values .= ', '.(int)$week_start;
754 if ($fields['tracking_mode']) {
755 $columns .= ', tracking_mode';
756 $values .= ', '.(int)$fields['tracking_mode'];
759 if ($fields['project_required']) {
760 $columns .= ', project_required';
761 $values .= ', '.(int)$fields['project_required'];
764 if ($fields['task_required']) {
765 $columns .= ', task_required';
766 $values .= ', '.(int)$fields['task_required'];
769 if ($fields['record_type']) {
770 $columns .= ', record_type';
771 $values .= ', '.(int)$fields['record_type'];
774 if ($fields['bcc_email']) {
775 $columns .= ', bcc_email';
776 $values .= ', '.$mdb2->quote($fields['bcc_email']);
779 if ($fields['plugins']) {
780 $columns .= ', plugins';
781 $values .= ', '.$mdb2->quote($fields['plugins']);
784 if ($fields['lock_spec']) {
785 $columns .= ', lock_spec';
786 $values .= ', '.$mdb2->quote($fields['lock_spec']);
789 if ($fields['workday_minutes']) {
790 $columns .= ', workday_minutes';
791 $values .= ', '.(int)$fields['workday_minutes'];
794 if ($fields['config']) {
795 $columns .= ', config';
796 $values .= ', '.$mdb2->quote($fields['config']);
799 $sql = "insert into tt_teams ($columns) values($values)";
800 $affected = $mdb2->exec($sql);
802 if (!is_a($affected, 'PEAR_Error')) {
803 $team_id = $mdb2->lastInsertID('tt_teams', 'id');
810 // The update function updates team information.
811 static function update($team_id, $fields)
813 $mdb2 = getConnection();
814 $name_part = 'name = '.$mdb2->quote($fields['name']);
817 $decimal_mark_part = '';
818 $date_format_part = '';
819 $time_format_part = '';
820 $week_start_part = '';
821 $tracking_mode_part = '';
822 $task_required_part = ' , task_required = '.(int) $fields['task_required'];
823 $record_type_part = '';
824 $bcc_email_part = '';
827 $lock_spec_part = '';
828 $workday_minutes_part = '';
830 if (isset($fields['currency'])) $currency_part = ', currency = '.$mdb2->quote($fields['currency']);
831 if (isset($fields['lang'])) $lang_part = ', lang = '.$mdb2->quote($fields['lang']);
832 if (isset($fields['decimal_mark'])) $decimal_mark_part = ', decimal_mark = '.$mdb2->quote($fields['decimal_mark']);
833 if (isset($fields['date_format'])) $date_format_part = ', date_format = '.$mdb2->quote($fields['date_format']);
834 if (isset($fields['time_format'])) $time_format_part = ', time_format = '.$mdb2->quote($fields['time_format']);
835 if (isset($fields['week_start'])) $week_start_part = ', week_start = '.(int) $fields['week_start'];
836 if (isset($fields['tracking_mode'])) $tracking_mode_part = ', tracking_mode = '.(int) $fields['tracking_mode'];
837 if (isset($fields['record_type'])) $record_type_part = ', record_type = '.(int) $fields['record_type'];
838 if (isset($fields['bcc_email'])) $bcc_email_part = ', bcc_email = '.$mdb2->quote($fields['bcc_email']);
839 if (isset($fields['plugins'])) $plugins_part = ', plugins = '.$mdb2->quote($fields['plugins']);
840 if (isset($fields['config'])) $config_part = ', config = '.$mdb2->quote($fields['config']);
841 if (isset($fields['lock_spec'])) $lock_spec_part = ', lock_spec = '.$mdb2->quote($fields['lock_spec']);
842 if (isset($fields['workday_minutes'])) $workday_minutes_part = ', workday_minutes = '.$mdb2->quote($fields['workday_minutes']);
844 $sql = "update tt_teams set $name_part $currency_part $lang_part $decimal_mark_part
845 $date_format_part $time_format_part $week_start_part $tracking_mode_part $task_required_part $record_type_part
846 $bcc_email_part $plugins_part $config_part $lock_spec_part $workday_minutes_part where id = $team_id";
847 $affected = $mdb2->exec($sql);
848 if (is_a($affected, 'PEAR_Error')) return false;
853 // The getInactiveTeams is a maintenance function that returns an array of inactive team ids (max 100).
854 static function getInactiveTeams() {
855 $inactive_teams = array();
856 $mdb2 = getConnection();
858 // Get all team ids for teams created or modified more than 6 months ago.
859 // $ts = date('Y-m-d', strtotime('-1 year'));
860 $ts = date('Y-m-d', strtotime('-6 month'));
861 $sql = "select id from tt_teams where timestamp < '$ts' order by id";
862 $res = $mdb2->query($sql);
865 if (!is_a($res, 'PEAR_Error')) {
866 while ($val = $res->fetchRow()) {
867 $team_id = $val['id'];
868 if (ttTeamHelper::isTeamActive($team_id) == false) {
870 $inactive_teams[] = $team_id;
871 // Limit the array size for perfomance by allowing this operation on small chunks only.
872 if ($count >= 100) break;
875 return $inactive_teams;
880 // The isTeamActive determines if a team is using Time Tracker or abandoned it.
881 static function isTeamActive($team_id) {
884 $mdb2 = getConnection();
885 $sql = "select id from tt_users where team_id = $team_id";
886 $res = $mdb2->query($sql);
887 if (is_a($res, 'PEAR_Error')) die($res->getMessage());
888 while ($val = $res->fetchRow()) {
889 $users[] = $val['id'];
891 $user_list = implode(',', $users); // This is a comma-separated list of user ids.
893 return false; // No users in team.
896 $ts = date('Y-m-d', strtotime('-2 years'));
897 $sql = "select count(*) as cnt from tt_log where user_id in ($user_list) and timestamp > '$ts'";
898 $res = $mdb2->query($sql);
899 if (!is_a($res, 'PEAR_Error')) {
900 if ($val = $res->fetchRow()) {
901 $count = $val['cnt'];
906 return false; // No time entries for the last 2 years.
909 // We will consider a team inactive if it has 5 or less time entries made more than 1 year ago.
910 $count_last_year = 0;
911 $ts = date('Y-m-d', strtotime('-1 year'));
912 $sql = "select count(*) as cnt from tt_log where user_id in ($user_list) and timestamp > '$ts'";
913 $res = $mdb2->query($sql);
914 if (!is_a($res, 'PEAR_Error')) {
915 if ($val = $res->fetchRow()) {
916 $count_last_year = $val['cnt'];
918 if ($count_last_year == 0)
919 return false; // No time entries for the last year and only a few entries before that.
925 // The delete function permanently deletes all data for a team.
926 static function delete($team_id) {
927 $mdb2 = getConnection();
930 $sql = "select id from tt_users where team_id = $team_id";
931 $res = $mdb2->query($sql);
932 if (is_a($res, 'PEAR_Error')) return false;
933 while ($val = $res->fetchRow()) {
934 $user_id = $val['id'];
935 if (!ttUserHelper::delete($user_id)) return false;
939 if (!ttTeamHelper::deleteTasks($team_id)) return false;
941 // Delete client to project binds.
942 $sql = "delete from tt_client_project_binds where client_id in (select id from tt_clients where team_id = $team_id)";
943 $affected = $mdb2->exec($sql);
944 if (is_a($affected, 'PEAR_Error')) return false;
947 $sql = "delete from tt_projects where team_id = $team_id";
948 $affected = $mdb2->exec($sql);
949 if (is_a($affected, 'PEAR_Error')) return false;
952 $sql = "delete from tt_clients where team_id = $team_id";
953 $affected = $mdb2->exec($sql);
954 if (is_a($affected, 'PEAR_Error')) return false;
957 $sql = "delete from tt_invoices where team_id = $team_id";
958 $affected = $mdb2->exec($sql);
959 if (is_a($affected, 'PEAR_Error')) return false;
961 // Delete custom fields.
962 if (!ttTeamHelper::deleteCustomFields($team_id)) return false;
965 $sql = "delete from tt_roles where team_id = $team_id";
966 $affected = $mdb2->exec($sql);
967 if (is_a($affected, 'PEAR_Error')) return false;
970 $sql = "delete from tt_teams where id = $team_id";
971 $affected = $mdb2->exec($sql);
972 if (is_a($affected, 'PEAR_Error')) return false;
977 // The markTasksDeleted deletes task binds and marks the tasks as deleted for a team.
978 static function markTasksDeleted($team_id) {
979 $mdb2 = getConnection();
980 $sql = "select id from tt_tasks where team_id = $team_id";
981 $res = $mdb2->query($sql);
982 if (is_a($res, 'PEAR_Error')) return false;
984 while ($val = $res->fetchRow()) {
986 // Delete task binds.
987 $task_id = $val['id'];
988 $sql = "delete from tt_project_task_binds where task_id = $task_id";
989 $affected = $mdb2->exec($sql);
990 if (is_a($affected, 'PEAR_Error')) return false;
992 // Mark task as deleted.
993 $sql = "update tt_tasks set status = NULL where id = $task_id";
994 $affected = $mdb2->exec($sql);
995 if (is_a($affected, 'PEAR_Error')) return false;
1001 // The deleteTasks deletes all tasks and task binds for an inactive team.
1002 static function deleteTasks($team_id) {
1003 $mdb2 = getConnection();
1004 $sql = "select id from tt_tasks where team_id = $team_id";
1005 $res = $mdb2->query($sql);
1006 if (is_a($res, 'PEAR_Error')) return false;
1008 while ($val = $res->fetchRow()) {
1010 // Delete task binds.
1011 $task_id = $val['id'];
1012 $sql = "delete from tt_project_task_binds where task_id = $task_id";
1013 $affected = $mdb2->exec($sql);
1014 if (is_a($affected, 'PEAR_Error')) return false;
1017 $sql = "delete from tt_tasks where id = $task_id";
1018 $affected = $mdb2->exec($sql);
1019 if (is_a($affected, 'PEAR_Error')) return false;
1025 // The deleteCustomFields cleans up tt_custom_field_log, tt_custom_field_options and tt_custom_fields tables for an inactive team.
1026 static function deleteCustomFields($team_id) {
1027 $mdb2 = getConnection();
1028 $sql = "select id from tt_custom_fields where team_id = $team_id";
1029 $res = $mdb2->query($sql);
1030 if (is_a($res, 'PEAR_Error')) return false;
1032 while ($val = $res->fetchRow()) {
1033 $field_id = $val['id'];
1035 // Clean up tt_custom_field_log.
1036 $sql = "delete from tt_custom_field_log where field_id = $field_id";
1037 $affected = $mdb2->exec($sql);
1038 if (is_a($affected, 'PEAR_Error')) return false;
1040 // Clean up tt_custom_field_options.
1041 $sql = "delete from tt_custom_field_options where field_id = $field_id";
1042 $affected = $mdb2->exec($sql);
1043 if (is_a($affected, 'PEAR_Error')) return false;
1045 // Delete custom field.
1046 $sql = "delete from tt_custom_fields where id = $field_id";
1047 $affected = $mdb2->exec($sql);
1048 if (is_a($affected, 'PEAR_Error')) return false;
1054 // enablePlugin either enables or disables a specific plugin for team.
1055 static function enablePlugin($plugin, $enable = true)
1058 if (!$user->can('manage_features'))
1061 $plugin_array = explode(',', $user->plugins);
1062 if ($enable && !in_array($plugin, $plugin_array))
1063 $plugin_array[] = $plugin; // Add plugin to array.
1065 if (!$enable && in_array($plugin, $plugin_array)) {
1066 $key = array_search($plugin, $plugin_array);
1068 unset($plugin_array[$key]); // Remove plugin from array.
1071 $plugins = implode(',', $plugin_array);
1072 if ($plugins != $user->plugins) {
1073 if (!ttTeamHelper::update($user->team_id, array('name' => $user->team,'plugins' => $plugins)))
1075 $user->plugins = $plugins;