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 * from tt_users where team_id = $user->team_id and status = 1 order by upper(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 * from tt_users where team_id = $team_id and status = 0 order by upper(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 and u.role = $role_manager)
702 where t.id = $team_id";
704 $res = $mdb2->query($sql);
705 if (!is_a($res, 'PEAR_Error')) {
706 $val = $res->fetchRow();
713 // The insert function creates a new team.
714 static function insert($fields) {
716 $mdb2 = getConnection();
718 // Start with team name and currency.
719 $columns = 'name, currency';
720 $values = $mdb2->quote(trim($fields['name'])).', '.$mdb2->quote(trim($fields['currency']));
722 if ($fields['decimal_mark']) {
723 $columns .= ', decimal_mark';
724 $values .= ', '.$mdb2->quote($fields['decimal_mark']);
727 $lang = $fields['lang'];
732 $columns .= ', lang';
733 $values .= ', '.$mdb2->quote($lang);
735 if ($fields['date_format'] || defined('DATE_FORMAT_DEFAULT')) {
736 $date_format = $fields['date_format'] ? $fields['date_format'] : DATE_FORMAT_DEFAULT;
737 $columns .= ', date_format';
738 $values .= ', '.$mdb2->quote($date_format);
741 if ($fields['time_format'] || defined('TIME_FORMAT_DEFAULT')) {
742 $time_format = $fields['time_format'] ? $fields['time_format'] : TIME_FORMAT_DEFAULT;
743 $columns .= ', time_format';
744 $values .= ', '.$mdb2->quote($time_format);
747 if ($fields['week_start'] || defined('WEEK_START_DEFAULT')) {
748 $week_start = $fields['week_start'] ? $fields['week_start'] : WEEK_START_DEFAULT;
749 $columns .= ', week_start';
750 $values .= ', '.(int)$week_start;
753 if ($fields['tracking_mode']) {
754 $columns .= ', tracking_mode';
755 $values .= ', '.(int)$fields['tracking_mode'];
758 if ($fields['project_required']) {
759 $columns .= ', project_required';
760 $values .= ', '.(int)$fields['project_required'];
763 if ($fields['task_required']) {
764 $columns .= ', task_required';
765 $values .= ', '.(int)$fields['task_required'];
768 if ($fields['record_type']) {
769 $columns .= ', record_type';
770 $values .= ', '.(int)$fields['record_type'];
773 if ($fields['bcc_email']) {
774 $columns .= ', bcc_email';
775 $values .= ', '.$mdb2->quote($fields['bcc_email']);
778 if ($fields['plugins']) {
779 $columns .= ', plugins';
780 $values .= ', '.$mdb2->quote($fields['plugins']);
783 if ($fields['lock_spec']) {
784 $columns .= ', lock_spec';
785 $values .= ', '.$mdb2->quote($fields['lock_spec']);
788 if ($fields['workday_minutes']) {
789 $columns .= ', workday_minutes';
790 $values .= ', '.(int)$fields['workday_minutes'];
793 if ($fields['config']) {
794 $columns .= ', config';
795 $values .= ', '.$mdb2->quote($fields['config']);
798 $sql = "insert into tt_teams ($columns) values($values)";
799 $affected = $mdb2->exec($sql);
801 if (!is_a($affected, 'PEAR_Error')) {
802 $team_id = $mdb2->lastInsertID('tt_teams', 'id');
809 // The update function updates team information.
810 static function update($team_id, $fields)
812 $mdb2 = getConnection();
813 $name_part = 'name = '.$mdb2->quote($fields['name']);
816 $decimal_mark_part = '';
817 $date_format_part = '';
818 $time_format_part = '';
819 $week_start_part = '';
820 $tracking_mode_part = '';
821 $task_required_part = ' , task_required = '.(int) $fields['task_required'];
822 $record_type_part = '';
823 $bcc_email_part = '';
826 $lock_spec_part = '';
827 $workday_minutes_part = '';
829 if (isset($fields['currency'])) $currency_part = ', currency = '.$mdb2->quote($fields['currency']);
830 if (isset($fields['lang'])) $lang_part = ', lang = '.$mdb2->quote($fields['lang']);
831 if (isset($fields['decimal_mark'])) $decimal_mark_part = ', decimal_mark = '.$mdb2->quote($fields['decimal_mark']);
832 if (isset($fields['date_format'])) $date_format_part = ', date_format = '.$mdb2->quote($fields['date_format']);
833 if (isset($fields['time_format'])) $time_format_part = ', time_format = '.$mdb2->quote($fields['time_format']);
834 if (isset($fields['week_start'])) $week_start_part = ', week_start = '.(int) $fields['week_start'];
835 if (isset($fields['tracking_mode'])) $tracking_mode_part = ', tracking_mode = '.(int) $fields['tracking_mode'];
836 if (isset($fields['record_type'])) $record_type_part = ', record_type = '.(int) $fields['record_type'];
837 if (isset($fields['bcc_email'])) $bcc_email_part = ', bcc_email = '.$mdb2->quote($fields['bcc_email']);
838 if (isset($fields['plugins'])) $plugins_part = ', plugins = '.$mdb2->quote($fields['plugins']);
839 if (isset($fields['config'])) $config_part = ', config = '.$mdb2->quote($fields['config']);
840 if (isset($fields['lock_spec'])) $lock_spec_part = ', lock_spec = '.$mdb2->quote($fields['lock_spec']);
841 if (isset($fields['workday_minutes'])) $workday_minutes_part = ', workday_minutes = '.$mdb2->quote($fields['workday_minutes']);
843 $sql = "update tt_teams set $name_part $currency_part $lang_part $decimal_mark_part
844 $date_format_part $time_format_part $week_start_part $tracking_mode_part $task_required_part $record_type_part
845 $bcc_email_part $plugins_part $config_part $lock_spec_part $workday_minutes_part where id = $team_id";
846 $affected = $mdb2->exec($sql);
847 if (is_a($affected, 'PEAR_Error')) return false;
852 // The getInactiveTeams is a maintenance function that returns an array of inactive team ids (max 100).
853 static function getInactiveTeams() {
854 $inactive_teams = array();
855 $mdb2 = getConnection();
857 // Get all team ids for teams created or modified more than 6 months ago.
858 // $ts = date('Y-m-d', strtotime('-1 year'));
859 $ts = date('Y-m-d', strtotime('-6 month'));
860 $sql = "select id from tt_teams where timestamp < '$ts' order by id";
861 $res = $mdb2->query($sql);
864 if (!is_a($res, 'PEAR_Error')) {
865 while ($val = $res->fetchRow()) {
866 $team_id = $val['id'];
867 if (ttTeamHelper::isTeamActive($team_id) == false) {
869 $inactive_teams[] = $team_id;
870 // Limit the array size for perfomance by allowing this operation on small chunks only.
871 if ($count >= 100) break;
874 return $inactive_teams;
879 // The isTeamActive determines if a team is using Time Tracker or abandoned it.
880 static function isTeamActive($team_id) {
883 $mdb2 = getConnection();
884 $sql = "select id from tt_users where team_id = $team_id";
885 $res = $mdb2->query($sql);
886 if (is_a($res, 'PEAR_Error')) die($res->getMessage());
887 while ($val = $res->fetchRow()) {
888 $users[] = $val['id'];
890 $user_list = implode(',', $users); // This is a comma-separated list of user ids.
892 return false; // No users in team.
895 $ts = date('Y-m-d', strtotime('-2 years'));
896 $sql = "select count(*) as cnt from tt_log where user_id in ($user_list) and timestamp > '$ts'";
897 $res = $mdb2->query($sql);
898 if (!is_a($res, 'PEAR_Error')) {
899 if ($val = $res->fetchRow()) {
900 $count = $val['cnt'];
905 return false; // No time entries for the last 2 years.
908 // We will consider a team inactive if it has 5 or less time entries made more than 1 year ago.
909 $count_last_year = 0;
910 $ts = date('Y-m-d', strtotime('-1 year'));
911 $sql = "select count(*) as cnt from tt_log where user_id in ($user_list) and timestamp > '$ts'";
912 $res = $mdb2->query($sql);
913 if (!is_a($res, 'PEAR_Error')) {
914 if ($val = $res->fetchRow()) {
915 $count_last_year = $val['cnt'];
917 if ($count_last_year == 0)
918 return false; // No time entries for the last year and only a few entries before that.
924 // The delete function permanently deletes all data for a team.
925 static function delete($team_id) {
926 $mdb2 = getConnection();
929 $sql = "select id from tt_users where team_id = $team_id";
930 $res = $mdb2->query($sql);
931 if (is_a($res, 'PEAR_Error')) return false;
932 while ($val = $res->fetchRow()) {
933 $user_id = $val['id'];
934 if (!ttUserHelper::delete($user_id)) return false;
938 if (!ttTeamHelper::deleteTasks($team_id)) return false;
940 // Delete client to project binds.
941 $sql = "delete from tt_client_project_binds where client_id in (select id from tt_clients where team_id = $team_id)";
942 $affected = $mdb2->exec($sql);
943 if (is_a($affected, 'PEAR_Error')) return false;
946 $sql = "delete from tt_projects where team_id = $team_id";
947 $affected = $mdb2->exec($sql);
948 if (is_a($affected, 'PEAR_Error')) return false;
951 $sql = "delete from tt_clients where team_id = $team_id";
952 $affected = $mdb2->exec($sql);
953 if (is_a($affected, 'PEAR_Error')) return false;
956 $sql = "delete from tt_invoices where team_id = $team_id";
957 $affected = $mdb2->exec($sql);
958 if (is_a($affected, 'PEAR_Error')) return false;
960 // Delete custom fields.
961 if (!ttTeamHelper::deleteCustomFields($team_id)) return false;
964 $sql = "delete from tt_roles where team_id = $team_id";
965 $affected = $mdb2->exec($sql);
966 if (is_a($affected, 'PEAR_Error')) return false;
969 $sql = "delete from tt_teams where id = $team_id";
970 $affected = $mdb2->exec($sql);
971 if (is_a($affected, 'PEAR_Error')) return false;
976 // The markTasksDeleted deletes task binds and marks the tasks as deleted for a team.
977 static function markTasksDeleted($team_id) {
978 $mdb2 = getConnection();
979 $sql = "select id from tt_tasks where team_id = $team_id";
980 $res = $mdb2->query($sql);
981 if (is_a($res, 'PEAR_Error')) return false;
983 while ($val = $res->fetchRow()) {
985 // Delete task binds.
986 $task_id = $val['id'];
987 $sql = "delete from tt_project_task_binds where task_id = $task_id";
988 $affected = $mdb2->exec($sql);
989 if (is_a($affected, 'PEAR_Error')) return false;
991 // Mark task as deleted.
992 $sql = "update tt_tasks set status = NULL where id = $task_id";
993 $affected = $mdb2->exec($sql);
994 if (is_a($affected, 'PEAR_Error')) return false;
1000 // The deleteTasks deletes all tasks and task binds for an inactive team.
1001 static function deleteTasks($team_id) {
1002 $mdb2 = getConnection();
1003 $sql = "select id from tt_tasks where team_id = $team_id";
1004 $res = $mdb2->query($sql);
1005 if (is_a($res, 'PEAR_Error')) return false;
1007 while ($val = $res->fetchRow()) {
1009 // Delete task binds.
1010 $task_id = $val['id'];
1011 $sql = "delete from tt_project_task_binds where task_id = $task_id";
1012 $affected = $mdb2->exec($sql);
1013 if (is_a($affected, 'PEAR_Error')) return false;
1016 $sql = "delete from tt_tasks where id = $task_id";
1017 $affected = $mdb2->exec($sql);
1018 if (is_a($affected, 'PEAR_Error')) return false;
1024 // The deleteCustomFields cleans up tt_custom_field_log, tt_custom_field_options and tt_custom_fields tables for an inactive team.
1025 static function deleteCustomFields($team_id) {
1026 $mdb2 = getConnection();
1027 $sql = "select id from tt_custom_fields where team_id = $team_id";
1028 $res = $mdb2->query($sql);
1029 if (is_a($res, 'PEAR_Error')) return false;
1031 while ($val = $res->fetchRow()) {
1032 $field_id = $val['id'];
1034 // Clean up tt_custom_field_log.
1035 $sql = "delete from tt_custom_field_log where field_id = $field_id";
1036 $affected = $mdb2->exec($sql);
1037 if (is_a($affected, 'PEAR_Error')) return false;
1039 // Clean up tt_custom_field_options.
1040 $sql = "delete from tt_custom_field_options where field_id = $field_id";
1041 $affected = $mdb2->exec($sql);
1042 if (is_a($affected, 'PEAR_Error')) return false;
1044 // Delete custom field.
1045 $sql = "delete from tt_custom_fields where id = $field_id";
1046 $affected = $mdb2->exec($sql);
1047 if (is_a($affected, 'PEAR_Error')) return false;
1053 // enablePlugin either enables or disables a specific plugin for team.
1054 static function enablePlugin($plugin, $enable = true)
1057 if (!$user->canManageTeam())
1060 $plugin_array = explode(',', $user->plugins);
1061 if ($enable && !in_array($plugin, $plugin_array))
1062 $plugin_array[] = $plugin; // Add plugin to array.
1064 if (!$enable && in_array($plugin, $plugin_array)) {
1065 $key = array_search($plugin, $plugin_array);
1067 unset($plugin_array[$key]); // Remove plugin from array.
1070 $plugins = implode(',', $plugin_array);
1071 if ($plugins != $user->plugins) {
1072 if (!ttTeamHelper::update($user->team_id, array('name' => $user->team,'plugins' => $plugins)))
1074 $user->plugins = $plugins;