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 getUserDetails function returns user details.
35 static function getUserDetails($user_id) {
37 $mdb2 = getConnection();
39 $sql = "select * from tt_users where id = $user_id";
40 $res = $mdb2->query($sql);
42 if (!is_a($res, 'PEAR_Error')) {
43 $val = $res->fetchRow();
49 // The getUserName function returns user name.
50 static function getUserName($user_id) {
51 $mdb2 = getConnection();
53 $sql = "select name from tt_users where id = $user_id and (status = 1 or status = 0)";
54 $res = $mdb2->query($sql);
56 if (!is_a($res, 'PEAR_Error')) {
57 $val = $res->fetchRow();
63 // The getUserByLogin function obtains data for a user, who is identified by login.
64 static function getUserByLogin($login) {
65 $mdb2 = getConnection();
67 $sql = "select id, name from tt_users where login = ".$mdb2->quote($login)." and (status = 1 or status = 0)";
68 $res = $mdb2->query($sql);
69 if (!is_a($res, 'PEAR_Error')) {
70 if ($val = $res->fetchRow()) {
77 // The getUserByEmail function is a helper function that tries to obtain user details identified by email.
78 // This function works only when one such active user exists.
79 static function getUserByEmail($email) {
80 $mdb2 = getConnection();
82 $sql = "select login, count(*) as cnt from tt_users where email = ".$mdb2->quote($email)." and status = 1 group by email";
83 $res = $mdb2->query($sql);
85 if (is_a($res, 'PEAR_Error'))
88 $val = $res->fetchRow();
89 if (1 <> $val['cnt']) {
90 // We either have no users or multiple users with a given email.
96 // The getUserIdByTmpRef obtains user id from a temporary reference (used for password resets).
97 static function getUserIdByTmpRef($ref) {
98 $mdb2 = getConnection();
100 $sql = "select user_id from tt_tmp_refs where ref = ".$mdb2->quote($ref);
101 $res = $mdb2->query($sql);
103 if (!is_a($res, 'PEAR_Error')) {
104 $val = $res->fetchRow();
105 return $val['user_id'];
110 // insert - inserts a user into database.
111 static function insert($fields, $hash = true) {
112 $mdb2 = getConnection();
114 $password = $mdb2->quote($fields['password']);
116 $password = 'md5('.$password.')';
117 $email = isset($fields['email']) ? $fields['email'] : '';
118 $team_id = (int) $fields['team_id'];
119 $role = (int) $fields['role'];
120 $rate = str_replace(',', '.', isset($fields['rate']) ? $fields['rate'] : 0);
123 if (array_key_exists('status', $fields)) { // Key exists and may be NULL during migration of deleted acounts.
124 $status_f = ', status';
125 $status_v = ', '.$mdb2->quote($fields['status']);
128 $sql = "insert into tt_users (name, login, password, team_id, role, client_id, rate, email $status_f) values (".
129 $mdb2->quote($fields['name']).", ".$mdb2->quote($fields['login']).
130 ", $password, $team_id, $role, ".$mdb2->quote($fields['client_id']).", $rate, ".$mdb2->quote($email)." $status_v)";
131 $affected = $mdb2->exec($sql);
133 // Now deal with project assignment.
134 if (!is_a($affected, 'PEAR_Error')) {
135 $sql = "SELECT LAST_INSERT_ID() AS last_id";
136 $res = $mdb2->query($sql);
137 $val = $res->fetchRow();
138 $last_id = $val['last_id'];
140 $projects = isset($fields['projects']) ? $fields['projects'] : array();
141 if (count($projects) > 0) {
142 // We have at least one project assigned. Insert corresponding entries in tt_user_project_binds table.
143 foreach($projects as $p) {
144 if(!isset($p['rate']))
147 $p['rate'] = str_replace(',', '.', $p['rate']);
149 $sql = "insert into tt_user_project_binds (project_id, user_id, rate, status) values(".$p['id'].",".$last_id.",".$p['rate'].", 1)";
150 $affected = $mdb2->exec($sql);
158 // update - updates a user in database.
159 static function update($user_id, $fields) {
161 $mdb2 = getConnection();
164 if (!$user_id || !isset($fields['login']))
167 // Prepare query parts.
168 if (isset($fields['password']))
169 $pass_part = ', password = md5('.$mdb2->quote($fields['password']).')';
170 if (right_assign_roles & $user->rights) {
171 if (isset($fields['role'])) {
172 $role = (int) $fields['role'];
173 $role_part = ", role = $role";
175 if (array_key_exists('client_id', $fields)) // Could be NULL.
176 $client_part = ", client_id = ".$mdb2->quote($fields['client_id']);
179 if (array_key_exists('rate', $fields)) {
180 $rate = str_replace(',', '.', isset($fields['rate']) ? $fields['rate'] : 0);
181 if($rate == '') $rate = 0;
182 $rate_part = ", rate = ".$mdb2->quote($rate);
185 if (isset($fields['status'])) {
186 $status = (int) $fields['status'];
187 $status_part = ", status = $status";
190 $sql = "update tt_users set login = ".$mdb2->quote($fields['login']).
191 "$pass_part, name = ".$mdb2->quote($fields['name']).
192 "$role_part $client_part $rate_part $status_part, email = ".$mdb2->quote($fields['email']).
193 " where id = $user_id";
194 $affected = $mdb2->exec($sql);
195 if (is_a($affected, 'PEAR_Error')) return false;
197 if (array_key_exists('projects', $fields)) {
198 // Deal with project assignments.
199 // Note: we cannot simply delete old project binds and insert new ones because it screws up reporting
200 // (when looking for cost while entries for de-assigned projects exist).
201 // Therefore, we must iterate through all projects and only delete the binds when no time entries are present,
202 // otherwise de-activate the bind (set its status to inactive). This will keep the bind
203 // and its rate in database for reporting.
205 $all_projects = ttTeamHelper::getAllProjects($user->team_id);
206 $assigned_projects = isset($fields['projects']) ? $fields['projects'] : array();
208 foreach($all_projects as $p) {
209 // Determine if a project is assigned.
211 $project_id = $p['id'];
213 if (count($assigned_projects) > 0) {
214 foreach ($assigned_projects as $ap) {
215 if ($project_id == $ap['id']) {
219 $rate = str_replace(",",".",$rate);
227 ttUserHelper::deleteBind($user_id, $project_id);
229 // Here we need to either update or insert new tt_user_project_binds record.
230 // Determine if a record exists.
231 $sql = "select id from tt_user_project_binds where user_id = $user_id and project_id = $project_id";
232 $res = $mdb2->query($sql);
233 if (is_a($res, 'PEAR_Error')) die ($res->getMessage());
234 if ($val = $res->fetchRow()) {
235 // Record exists. Update it.
236 $sql = "update tt_user_project_binds set status = 1, rate = $rate where id = ".$val['id'];
237 $affected = $mdb2->exec($sql);
238 if (is_a($affected, 'PEAR_Error')) die ($affected->getMessage());
240 // Record does not exist. Insert it.
241 ttUserHelper::insertBind($user_id, $project_id, $rate, 1);
249 // markDeleted - marks user and its associated things as deleted.
250 static function markDeleted($user_id) {
251 $mdb2 = getConnection();
254 // Preliminary checks. Only managers, co-managers, and admin can do this.
255 if (!$user->canManageTeam() && !$user->isAdmin())
258 // Tho logic is different depending on who is doint the operation.
259 // Co-manage and admin - mark user deleted.
260 // Manager - mark user deleted. If manager is the only account in team, mark team items deleted.
263 if ($user->isAdmin()) {
264 // Mark user binds as deleted.
265 $sql = "update tt_user_project_binds set status = NULL where user_id = $user_id";
266 $affected = $mdb2->exec($sql);
267 if (is_a($affected, 'PEAR_Error'))
270 // Mark user as deleted.
271 $sql = "update tt_users set status = NULL where id = $user_id";
272 $affected = $mdb2->exec($sql);
273 if (is_a($affected, 'PEAR_Error'))
276 } else if ($user->isCoManager()) {
277 // Mark user binds as deleted.
278 $sql = "update tt_user_project_binds set status = NULL where user_id = $user_id";
279 $affected = $mdb2->exec($sql);
280 if (is_a($affected, 'PEAR_Error'))
283 // Mark user as deleted.
284 $sql = "update tt_users set status = NULL where id = $user_id and team_id = ".$user->team_id;
285 $affected = $mdb2->exec($sql);
286 if (is_a($affected, 'PEAR_Error'))
289 } else if ($user->isManager()) {
290 $user_count = ttTeamHelper::getUserCount($user->team_id);
292 // Marking deleted a manager with active users is not allowed.
293 if (($user_id == $user->id) && ($user_count > 1))
296 if (1 == $user_count) {
297 // Mark tasks deleted.
298 if (!ttTeamHelper::markTasksDeleted($user->team_id))
301 // Mark projects deleted.
302 $sql = "update tt_projects set status = NULL where team_id = $user->team_id";
303 $affected = $mdb2->exec($sql);
304 if (is_a($affected, 'PEAR_Error'))
307 // Mark clients deleted.
308 $sql = "update tt_clients set status = NULL where team_id = $user->team_id";
309 $affected = $mdb2->exec($sql);
310 if (is_a($affected, 'PEAR_Error'))
313 // Mark custom fields deleted.
314 $sql = "update tt_custom_fields set status = NULL where team_id = $user->team_id";
315 $affected = $mdb2->exec($sql);
316 if (is_a($affected, 'PEAR_Error'))
319 // Mark team deleted.
320 $sql = "update tt_teams set status = NULL where id = $user->team_id";
321 $affected = $mdb2->exec($sql);
322 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 user as deleted.
334 $sql = "update tt_users set status = NULL where id = $user_id and team_id = ".$user->team_id;
335 $affected = $mdb2->exec($sql);
336 if (is_a($affected, 'PEAR_Error'))
343 // The delete function permanently deletes a user and all associated data.
344 static function delete($user_id) {
345 $mdb2 = getConnection();
347 // Delete custom field log entries for user, if we have them.
348 $sql = "delete from tt_custom_field_log where log_id in
349 (select id from tt_log where user_id = $user_id)";
350 $affected = $mdb2->exec($sql);
351 if (is_a($affected, 'PEAR_Error'))
354 // Delete log entries for user.
355 $sql = "delete from tt_log where user_id = $user_id";
356 $affected = $mdb2->exec($sql);
357 if (is_a($affected, 'PEAR_Error'))
360 // Delete expense items for user.
361 $sql = "delete from tt_expense_items where user_id = $user_id";
362 $affected = $mdb2->exec($sql);
363 if (is_a($affected, 'PEAR_Error'))
366 // Delete user binds.
367 $sql = "delete from tt_user_project_binds where user_id = $user_id";
368 $affected = $mdb2->exec($sql);
369 if (is_a($affected, 'PEAR_Error'))
372 // Clean up tt_config table.
373 $sql = "delete from tt_config where user_id = $user_id";
374 $affected = $mdb2->exec($sql);
375 if (is_a($affected, 'PEAR_Error'))
378 // Clean up tt_fav_reports table.
379 $sql = "delete from tt_fav_reports where user_id = $user_id";
380 $affected = $mdb2->exec($sql);
381 if (is_a($affected, 'PEAR_Error'))
385 $sql = "delete from tt_users where id = $user_id";
386 $affected = $mdb2->exec($sql);
387 if (is_a($affected, 'PEAR_Error'))
393 // The saveTmpRef saves a temporary reference for user that is used to reset user password.
394 static function saveTmpRef($ref, $user_id) {
395 $mdb2 = getConnection();
397 $sql = "delete from tt_tmp_refs where timestamp + 86400 < now()";
398 $affected = $mdb2->exec($sql);
400 $sql = "insert into tt_tmp_refs (ref, user_id) values(".$mdb2->quote($ref).", $user_id)";
401 $affected = $mdb2->exec($sql);
404 // The setPassword function updates password for user.
405 static function setPassword($user_id, $password) {
406 $mdb2 = getConnection();
408 $sql = "update tt_users set password = md5(".$mdb2->quote($password).") where id = $user_id";
409 $affected = $mdb2->exec($sql);
411 return (!is_a($affected, 'PEAR_Error'));
414 // insertBind - inserts a user to project bind into tt_user_project_binds table.
415 static function insertBind($user_id, $project_id, $rate, $status) {
416 $mdb2 = getConnection();
418 $sql = "insert into tt_user_project_binds (user_id, project_id, rate, status)
419 values($user_id, $project_id, ".$mdb2->quote($rate).", $status)";
420 $affected = $mdb2->exec($sql);
421 return (!is_a($affected, 'PEAR_Error'));
424 // deleteBind - deactivates user to project bind when time entries exist,
425 // otherwise deletes it entirely.
426 static function deleteBind($user_id, $project_id) {
427 $mdb2 = getConnection();
429 $sql = "select count(*) as cnt from tt_log where
430 user_id = $user_id and project_id = $project_id and status = 1";
431 $res = $mdb2->query($sql);
432 if (is_a($res, 'PEAR_Error')) die ($res->getMessage());
435 $val = $res->fetchRow();
436 $count = $val['cnt'];
439 // Deactivate user bind.
440 $sql = "select id from tt_user_project_binds where user_id = $user_id and project_id = $project_id";
441 $res = $mdb2->query($sql);
442 if (is_a($res, 'PEAR_Error')) die ($res->getMessage());
443 if ($val = $res->fetchRow()) {
444 $sql = "update tt_user_project_binds set status = 0 where id = ".$val['id'];
445 $affected = $mdb2->exec($sql);
446 if (is_a($affected, 'PEAR_Error')) die ($res->getMessage());
450 $sql = "delete from tt_user_project_binds where user_id = $user_id and project_id = $project_id";
451 $affected = $mdb2->exec($sql);
452 if (is_a($affected, 'PEAR_Error')) die ($res->getMessage());