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('ttTeamHelper');
31 // Class ttUserHelper contains helper functions for operations with users.
34 // The getUserName function returns user name.
35 static function getUserName($user_id) {
36 $mdb2 = getConnection();
38 $sql = "select name from tt_users where id = $user_id and (status = 1 or status = 0)";
39 $res = $mdb2->query($sql);
41 if (!is_a($res, 'PEAR_Error')) {
42 $val = $res->fetchRow();
48 // The getUserByLogin function obtains data for a user, who is identified by login.
49 static function getUserByLogin($login) {
50 $mdb2 = getConnection();
52 $sql = "select id, name from tt_users where login = ".$mdb2->quote($login)." and (status = 1 or status = 0)";
53 $res = $mdb2->query($sql);
54 if (!is_a($res, 'PEAR_Error')) {
55 if ($val = $res->fetchRow()) {
62 // The getUserByEmail function is a helper function that tries to obtain user details identified by email.
63 // This function works only when one such active user exists.
64 static function getUserByEmail($email) {
65 $mdb2 = getConnection();
67 $sql = "select login, count(*) as cnt from tt_users where email = ".$mdb2->quote($email)." and status = 1 group by email";
68 $res = $mdb2->query($sql);
70 if (is_a($res, 'PEAR_Error'))
73 $val = $res->fetchRow();
74 if (1 <> $val['cnt']) {
75 // We either have no users or multiple users with a given email.
81 // The getUserIdByTmpRef obtains user id from a temporary reference (used for password resets).
82 static function getUserIdByTmpRef($ref) {
83 $mdb2 = getConnection();
85 $sql = "select user_id from tt_tmp_refs where ref = ".$mdb2->quote($ref);
86 $res = $mdb2->query($sql);
88 if (!is_a($res, 'PEAR_Error')) {
89 $val = $res->fetchRow();
90 return $val['user_id'];
95 // insert - inserts a user into database.
96 static function insert($fields, $hash = true) {
98 $mdb2 = getConnection();
100 $password = $mdb2->quote($fields['password']);
102 $password = 'md5('.$password.')';
103 $email = isset($fields['email']) ? $fields['email'] : '';
104 $team_id = (int) $fields['team_id'];
105 $rate = str_replace(',', '.', isset($fields['rate']) ? $fields['rate'] : 0);
108 if (array_key_exists('status', $fields)) { // Key exists and may be NULL during migration of deleted acounts.
109 $status_f = ', status';
110 $status_v = ', '.$mdb2->quote($fields['status']);
112 $created_ip_v = ', '.$mdb2->quote($_SERVER['REMOTE_ADDR']);
113 $created_by_v = ', '.$mdb2->quote($user->id);
115 $sql = "insert into tt_users (name, login, password, team_id, role_id, client_id, rate, email, created, created_ip, created_by $status_f) values (".
116 $mdb2->quote($fields['name']).", ".$mdb2->quote($fields['login']).
117 ", $password, $team_id, ".$mdb2->quote($fields['role_id']).", ".$mdb2->quote($fields['client_id']).", $rate, ".$mdb2->quote($email).", now() $created_ip_v $created_by_v $status_v)";
118 $affected = $mdb2->exec($sql);
120 // Now deal with project assignment.
121 if (!is_a($affected, 'PEAR_Error')) {
122 $sql = "SELECT LAST_INSERT_ID() AS last_id";
123 $res = $mdb2->query($sql);
124 $val = $res->fetchRow();
125 $last_id = $val['last_id'];
127 $projects = isset($fields['projects']) ? $fields['projects'] : array();
128 if (count($projects) > 0) {
129 // We have at least one project assigned. Insert corresponding entries in tt_user_project_binds table.
130 foreach($projects as $p) {
131 if(!isset($p['rate']))
134 $p['rate'] = str_replace(',', '.', $p['rate']);
136 $sql = "insert into tt_user_project_binds (project_id, user_id, rate, status) values(".$p['id'].",".$last_id.",".$p['rate'].", 1)";
137 $affected = $mdb2->exec($sql);
145 // update - updates a user in database.
146 static function update($user_id, $fields) {
148 $mdb2 = getConnection();
151 if (!$user_id || !isset($fields['login']))
154 // Prepare query parts.
155 if (isset($fields['password']))
156 $pass_part = ', password = md5('.$mdb2->quote($fields['password']).')';
157 if (in_array('manage_users', $user->rights)) {
158 if (isset($fields['role_id'])) {
159 $role_id = (int) $fields['role_id'];
160 $role_id_part = ", role_id = $role_id";
162 if (array_key_exists('client_id', $fields)) // Could be NULL.
163 $client_part = ", client_id = ".$mdb2->quote($fields['client_id']);
166 if (array_key_exists('rate', $fields)) {
167 $rate = str_replace(',', '.', isset($fields['rate']) ? $fields['rate'] : 0);
168 if($rate == '') $rate = 0;
169 $rate_part = ", rate = ".$mdb2->quote($rate);
172 if (isset($fields['status'])) {
173 $status = (int) $fields['status'];
174 $status_part = ", status = $status";
177 $modified_part = ', modified = now(), modified_ip = '.$mdb2->quote($_SERVER['REMOTE_ADDR']).', modified_by = '.$mdb2->quote($user->id);
179 $sql = "update tt_users set login = ".$mdb2->quote($fields['login']).
180 "$pass_part, name = ".$mdb2->quote($fields['name']).
181 "$role_id_part $client_part $rate_part $modified_part $status_part, email = ".$mdb2->quote($fields['email']).
182 " where id = $user_id";
183 $affected = $mdb2->exec($sql);
184 if (is_a($affected, 'PEAR_Error')) return false;
186 if (array_key_exists('projects', $fields)) {
187 // Deal with project assignments.
188 // Note: we cannot simply delete old project binds and insert new ones because it screws up reporting
189 // (when looking for cost while entries for de-assigned projects exist).
190 // Therefore, we must iterate through all projects and only delete the binds when no time entries are present,
191 // otherwise de-activate the bind (set its status to inactive). This will keep the bind
192 // and its rate in database for reporting.
194 $all_projects = ttTeamHelper::getAllProjects($user->team_id);
195 $assigned_projects = isset($fields['projects']) ? $fields['projects'] : array();
197 foreach($all_projects as $p) {
198 // Determine if a project is assigned.
200 $project_id = $p['id'];
202 if (count($assigned_projects) > 0) {
203 foreach ($assigned_projects as $ap) {
204 if ($project_id == $ap['id']) {
208 $rate = str_replace(",",".",$rate);
216 ttUserHelper::deleteBind($user_id, $project_id);
218 // Here we need to either update or insert new tt_user_project_binds record.
219 // Determine if a record exists.
220 $sql = "select id from tt_user_project_binds where user_id = $user_id and project_id = $project_id";
221 $res = $mdb2->query($sql);
222 if (is_a($res, 'PEAR_Error')) die ($res->getMessage());
223 if ($val = $res->fetchRow()) {
224 // Record exists. Update it.
225 $sql = "update tt_user_project_binds set status = 1, rate = $rate where id = ".$val['id'];
226 $affected = $mdb2->exec($sql);
227 if (is_a($affected, 'PEAR_Error')) die ($affected->getMessage());
229 // Record does not exist. Insert it.
230 ttUserHelper::insertBind($user_id, $project_id, $rate, 1);
238 // markDeleted - marks user and its associated things as deleted.
239 static function markDeleted($user_id) {
240 $mdb2 = getConnection();
243 // Preliminary checks. Only managers, co-managers, and admin can do this.
244 if (!$user->canManageTeam() && !$user->isAdmin())
247 // Tho logic is different depending on who is doing the operation.
248 // Co-manager and admin - mark user deleted.
249 // Manager - mark user deleted. If manager is the only account in team, mark team items deleted.
252 if ($user->isAdmin()) {
253 // Mark user binds as deleted.
254 $sql = "update tt_user_project_binds set status = NULL where user_id = $user_id";
255 $affected = $mdb2->exec($sql);
256 if (is_a($affected, 'PEAR_Error'))
259 // Mark favorite reports as deleted.
260 $sql = "update tt_fav_reports set status = NULL where user_id = $user_id";
261 $affected = $mdb2->exec($sql);
262 if (is_a($affected, 'PEAR_Error'))
265 // Mark user as deleted.
266 $sql = "update tt_users set status = NULL where id = $user_id";
267 $affected = $mdb2->exec($sql);
268 if (is_a($affected, 'PEAR_Error'))
271 } elseif ($user->isCoManager()) {
272 // Mark user binds as deleted.
273 $sql = "update tt_user_project_binds set status = NULL where user_id = $user_id";
274 $affected = $mdb2->exec($sql);
275 if (is_a($affected, 'PEAR_Error'))
278 // Mark favorite reports as deleted.
279 $sql = "update tt_fav_reports set status = NULL where user_id = $user_id";
280 $affected = $mdb2->exec($sql);
281 if (is_a($affected, 'PEAR_Error'))
284 // Mark user as deleted.
285 $sql = "update tt_users set status = NULL where id = $user_id and team_id = ".$user->team_id;
286 $affected = $mdb2->exec($sql);
287 if (is_a($affected, 'PEAR_Error'))
290 } elseif ($user->isManager()) {
291 $user_count = ttTeamHelper::getUserCount($user->team_id);
293 // Marking deleted a manager with active users is not allowed.
294 if (($user_id == $user->id) && ($user_count > 1))
297 if (1 == $user_count) {
298 // Mark tasks deleted.
299 if (!ttTeamHelper::markTasksDeleted($user->team_id))
302 // Mark projects deleted.
303 $sql = "update tt_projects set status = NULL where team_id = $user->team_id";
304 $affected = $mdb2->exec($sql);
305 if (is_a($affected, 'PEAR_Error'))
308 // Mark clients deleted.
309 $sql = "update tt_clients set status = NULL where team_id = $user->team_id";
310 $affected = $mdb2->exec($sql);
311 if (is_a($affected, 'PEAR_Error'))
314 // Mark custom fields deleted.
315 $sql = "update tt_custom_fields set status = NULL where team_id = $user->team_id";
316 $affected = $mdb2->exec($sql);
317 if (is_a($affected, 'PEAR_Error'))
320 // Mark team deleted.
321 $sql = "update tt_teams set status = NULL where id = $user->team_id";
322 $affected = $mdb2->exec($sql);
323 if (is_a($affected, 'PEAR_Error'))
327 // Mark user binds as deleted.
328 $sql = "update tt_user_project_binds set status = NULL where user_id = $user_id";
329 $affected = $mdb2->exec($sql);
330 if (is_a($affected, 'PEAR_Error'))
333 // Mark favorite reports as deleted.
334 $sql = "update tt_fav_reports set status = NULL where user_id = $user_id";
335 $affected = $mdb2->exec($sql);
336 if (is_a($affected, 'PEAR_Error'))
339 // Mark user as deleted.
340 $sql = "update tt_users set status = NULL where id = $user_id and team_id = ".$user->team_id;
341 $affected = $mdb2->exec($sql);
342 if (is_a($affected, 'PEAR_Error'))
349 // The delete function permanently deletes a user and all associated data.
350 static function delete($user_id) {
351 $mdb2 = getConnection();
353 // Delete custom field log entries for user, if we have them.
354 $sql = "delete from tt_custom_field_log where log_id in
355 (select id from tt_log where user_id = $user_id)";
356 $affected = $mdb2->exec($sql);
357 if (is_a($affected, 'PEAR_Error'))
360 // Delete log entries for user.
361 $sql = "delete from tt_log where user_id = $user_id";
362 $affected = $mdb2->exec($sql);
363 if (is_a($affected, 'PEAR_Error'))
366 // Delete expense items for user.
367 $sql = "delete from tt_expense_items where user_id = $user_id";
368 $affected = $mdb2->exec($sql);
369 if (is_a($affected, 'PEAR_Error'))
372 // Delete user binds.
373 $sql = "delete from tt_user_project_binds where user_id = $user_id";
374 $affected = $mdb2->exec($sql);
375 if (is_a($affected, 'PEAR_Error'))
378 // Clean up tt_config table.
379 $sql = "delete from tt_config where user_id = $user_id";
380 $affected = $mdb2->exec($sql);
381 if (is_a($affected, 'PEAR_Error'))
384 // Clean up tt_fav_reports table.
385 $sql = "delete from tt_fav_reports where user_id = $user_id";
386 $affected = $mdb2->exec($sql);
387 if (is_a($affected, 'PEAR_Error'))
391 $sql = "delete from tt_users where id = $user_id";
392 $affected = $mdb2->exec($sql);
393 if (is_a($affected, 'PEAR_Error'))
399 // The saveTmpRef saves a temporary reference for user that is used to reset user password.
400 static function saveTmpRef($ref, $user_id) {
401 $mdb2 = getConnection();
403 $sql = "delete from tt_tmp_refs where created < now() - interval 1 hour";
404 $affected = $mdb2->exec($sql);
406 $sql = "insert into tt_tmp_refs (created, ref, user_id) values(now(), ".$mdb2->quote($ref).", $user_id)";
407 $affected = $mdb2->exec($sql);
410 // The setPassword function updates password for user.
411 static function setPassword($user_id, $password) {
412 $mdb2 = getConnection();
414 $sql = "update tt_users set password = md5(".$mdb2->quote($password).") where id = $user_id";
415 $affected = $mdb2->exec($sql);
417 return (!is_a($affected, 'PEAR_Error'));
420 // insertBind - inserts a user to project bind into tt_user_project_binds table.
421 static function insertBind($user_id, $project_id, $rate, $status) {
422 $mdb2 = getConnection();
424 $sql = "insert into tt_user_project_binds (user_id, project_id, rate, status)
425 values($user_id, $project_id, ".$mdb2->quote($rate).", $status)";
426 $affected = $mdb2->exec($sql);
427 return (!is_a($affected, 'PEAR_Error'));
430 // deleteBind - deactivates user to project bind when time entries exist,
431 // otherwise deletes it entirely.
432 static function deleteBind($user_id, $project_id) {
433 $mdb2 = getConnection();
435 $sql = "select count(*) as cnt from tt_log where
436 user_id = $user_id and project_id = $project_id and status = 1";
437 $res = $mdb2->query($sql);
438 if (is_a($res, 'PEAR_Error')) die ($res->getMessage());
441 $val = $res->fetchRow();
442 $count = $val['cnt'];
445 // Deactivate user bind.
446 $sql = "select id from tt_user_project_binds where user_id = $user_id and project_id = $project_id";
447 $res = $mdb2->query($sql);
448 if (is_a($res, 'PEAR_Error')) die ($res->getMessage());
449 if ($val = $res->fetchRow()) {
450 $sql = "update tt_user_project_binds set status = 0 where id = ".$val['id'];
451 $affected = $mdb2->exec($sql);
452 if (is_a($affected, 'PEAR_Error')) die ($res->getMessage());
456 $sql = "delete from tt_user_project_binds where user_id = $user_id and project_id = $project_id";
457 $affected = $mdb2->exec($sql);
458 if (is_a($affected, 'PEAR_Error')) die ($res->getMessage());
463 // updateLastAccess - updates last access info for user in db.
464 static function updateLastAccess() {
466 $mdb2 = getConnection();
467 $accessed_ip = $mdb2->quote($_SERVER['REMOTE_ADDR']);
468 $sql = "update tt_users set accessed = now(), accessed_ip = $accessed_ip where id = $user->id";