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 $group_id = (int) $fields['group_id'];
105 $org_id = (int) $fields['org_id'];
106 $rate = str_replace(',', '.', isset($fields['rate']) ? $fields['rate'] : 0);
109 if (array_key_exists('status', $fields)) { // Key exists and may be NULL during migration of deleted acounts.
110 $status_f = ', status';
111 $status_v = ', '.$mdb2->quote($fields['status']);
113 $created_ip_v = ', '.$mdb2->quote($_SERVER['REMOTE_ADDR']);
114 $created_by_v = ', '.$mdb2->quote($user->id);
116 $sql = "insert into tt_users (name, login, password, group_id, org_id, role_id, client_id, rate, email, created, created_ip, created_by $status_f) values (".
117 $mdb2->quote($fields['name']).", ".$mdb2->quote($fields['login']).
118 ", $password, $group_id, $org_id, ".$mdb2->quote($fields['role_id']).", ".$mdb2->quote($fields['client_id']).", $rate, ".$mdb2->quote($email).", now() $created_ip_v $created_by_v $status_v)";
119 $affected = $mdb2->exec($sql);
121 // Now deal with project assignment.
122 if (!is_a($affected, 'PEAR_Error')) {
123 $sql = "SELECT LAST_INSERT_ID() AS last_id";
124 $res = $mdb2->query($sql);
125 $val = $res->fetchRow();
126 $last_id = $val['last_id'];
128 $projects = isset($fields['projects']) ? $fields['projects'] : array();
129 if (count($projects) > 0) {
130 // We have at least one project assigned. Insert corresponding entries in tt_user_project_binds table.
131 foreach($projects as $p) {
132 if(!isset($p['rate']))
135 $p['rate'] = str_replace(',', '.', $p['rate']);
137 $sql = "insert into tt_user_project_binds (project_id, user_id, group_id, org_id, rate, status)".
138 " values(".$p['id'].", $last_id, $group_id, $org_id, ".$p['rate'].", 1)";
139 $affected = $mdb2->exec($sql);
147 // update - updates a user in database.
148 static function update($user_id, $fields) {
150 $mdb2 = getConnection();
153 if (!$user_id || !isset($fields['login']))
156 // Prepare query parts.
157 if (isset($fields['password']))
158 $pass_part = ', password = md5('.$mdb2->quote($fields['password']).')';
159 if (in_array('manage_users', $user->rights)) {
160 if (isset($fields['role_id'])) {
161 $role_id = (int) $fields['role_id'];
162 $role_id_part = ", role_id = $role_id";
164 if (array_key_exists('client_id', $fields)) // Could be NULL.
165 $client_part = ", client_id = ".$mdb2->quote($fields['client_id']);
168 if (array_key_exists('rate', $fields)) {
169 $rate = str_replace(',', '.', isset($fields['rate']) ? $fields['rate'] : 0);
170 if($rate == '') $rate = 0;
171 $rate_part = ", rate = ".$mdb2->quote($rate);
174 if (isset($fields['status'])) {
175 $status = (int) $fields['status'];
176 $status_part = ", status = $status";
179 $modified_part = ', modified = now(), modified_ip = '.$mdb2->quote($_SERVER['REMOTE_ADDR']).', modified_by = '.$mdb2->quote($user->id);
181 $sql = "update tt_users set login = ".$mdb2->quote($fields['login']).
182 "$pass_part, name = ".$mdb2->quote($fields['name']).
183 "$role_id_part $client_part $rate_part $modified_part $status_part, email = ".$mdb2->quote($fields['email']).
184 " where id = $user_id";
185 $affected = $mdb2->exec($sql);
186 if (is_a($affected, 'PEAR_Error')) return false;
188 if (array_key_exists('projects', $fields)) {
189 // Deal with project assignments.
190 // Note: we cannot simply delete old project binds and insert new ones because it screws up reporting
191 // (when looking for cost while entries for de-assigned projects exist).
192 // Therefore, we must iterate through all projects and only delete the binds when no time entries are present,
193 // otherwise de-activate the bind (set its status to inactive). This will keep the bind
194 // and its rate in database for reporting.
196 $all_projects = ttTeamHelper::getAllProjects($user->group_id);
197 $assigned_projects = isset($fields['projects']) ? $fields['projects'] : array();
199 foreach($all_projects as $p) {
200 // Determine if a project is assigned.
202 $project_id = $p['id'];
204 if (count($assigned_projects) > 0) {
205 foreach ($assigned_projects as $ap) {
206 if ($project_id == $ap['id']) {
210 $rate = str_replace(",",".",$rate);
218 ttUserHelper::deleteBind($user_id, $project_id);
220 // Here we need to either update or insert new tt_user_project_binds record.
221 // Determine if a record exists.
222 $sql = "select id from tt_user_project_binds where user_id = $user_id and project_id = $project_id";
223 $res = $mdb2->query($sql);
224 if (is_a($res, 'PEAR_Error')) die ($res->getMessage());
225 if ($val = $res->fetchRow()) {
226 // Record exists. Update it.
227 $sql = "update tt_user_project_binds set status = 1, rate = $rate where id = ".$val['id'];
228 $affected = $mdb2->exec($sql);
229 if (is_a($affected, 'PEAR_Error')) die ($affected->getMessage());
231 // Record does not exist. Insert it.
232 ttUserHelper::insertBind(array(
233 'user_id' => $user_id,
234 'project_id' => $project_id,
235 'group_id' => $user->getActiveGroup(),
236 'org_id' => $user->org_id,
238 'status' => ACTIVE));
246 // The delete function permanently deletes a user and all associated data.
247 static function delete($user_id) {
248 $mdb2 = getConnection();
250 // Delete custom field log entries for user, if we have them.
251 $sql = "delete from tt_custom_field_log where log_id in
252 (select id from tt_log where user_id = $user_id)";
253 $affected = $mdb2->exec($sql);
254 if (is_a($affected, 'PEAR_Error'))
257 // Delete log entries for user.
258 $sql = "delete from tt_log where user_id = $user_id";
259 $affected = $mdb2->exec($sql);
260 if (is_a($affected, 'PEAR_Error'))
263 // Delete expense items for user.
264 $sql = "delete from tt_expense_items where user_id = $user_id";
265 $affected = $mdb2->exec($sql);
266 if (is_a($affected, 'PEAR_Error'))
269 // Delete user binds.
270 $sql = "delete from tt_user_project_binds where user_id = $user_id";
271 $affected = $mdb2->exec($sql);
272 if (is_a($affected, 'PEAR_Error'))
275 // Clean up tt_config table.
276 $sql = "delete from tt_config where user_id = $user_id";
277 $affected = $mdb2->exec($sql);
278 if (is_a($affected, 'PEAR_Error'))
281 // Clean up tt_fav_reports table.
282 $sql = "delete from tt_fav_reports where user_id = $user_id";
283 $affected = $mdb2->exec($sql);
284 if (is_a($affected, 'PEAR_Error'))
288 $sql = "delete from tt_users where id = $user_id";
289 $affected = $mdb2->exec($sql);
290 if (is_a($affected, 'PEAR_Error'))
296 // The saveTmpRef saves a temporary reference for user that is used to reset user password.
297 static function saveTmpRef($ref, $user_id) {
298 $mdb2 = getConnection();
300 $sql = "delete from tt_tmp_refs where created < now() - interval 1 hour";
301 $affected = $mdb2->exec($sql);
303 $sql = "insert into tt_tmp_refs (created, ref, user_id) values(now(), ".$mdb2->quote($ref).", $user_id)";
304 $affected = $mdb2->exec($sql);
307 // The setPassword function updates password for user.
308 static function setPassword($user_id, $password) {
309 $mdb2 = getConnection();
311 $sql = "update tt_users set password = md5(".$mdb2->quote($password).") where id = $user_id";
312 $affected = $mdb2->exec($sql);
314 return (!is_a($affected, 'PEAR_Error'));
317 // insertBind - inserts a user to project bind into tt_user_project_binds table.
318 static function insertBind($fields) {
320 $mdb2 = getConnection();
322 // This may be used during import. Use the following until we have import refactored.
323 $group_id = $fields['group_id'] ? (int) $fields['group_id'] : $user->getActiveGroup();
324 $org_id = $fields['org_id'] ? (int) $fields['org_id'] : $user->org_id;
326 $user_id = (int) $fields['user_id'];
327 $project_id = (int) $fields['project_id'];
328 $rate = $mdb2->quote($fields['rate']);
329 $status = $mdb2->quote($fields['status']);
331 $sql = "insert into tt_user_project_binds (user_id, project_id, group_id, org_id, rate, status)".
332 " values($user_id, $project_id, $group_id, $org_id, $rate, $status)";
333 $affected = $mdb2->exec($sql);
334 return (!is_a($affected, 'PEAR_Error'));
337 // deleteBind - deactivates user to project bind when time entries exist,
338 // otherwise deletes it entirely.
339 static function deleteBind($user_id, $project_id) {
340 $mdb2 = getConnection();
342 $sql = "select count(*) as cnt from tt_log where
343 user_id = $user_id and project_id = $project_id and status = 1";
344 $res = $mdb2->query($sql);
345 if (is_a($res, 'PEAR_Error')) die ($res->getMessage());
348 $val = $res->fetchRow();
349 $count = $val['cnt'];
352 // Deactivate user bind.
353 $sql = "select id from tt_user_project_binds where user_id = $user_id and project_id = $project_id";
354 $res = $mdb2->query($sql);
355 if (is_a($res, 'PEAR_Error')) die ($res->getMessage());
356 if ($val = $res->fetchRow()) {
357 $sql = "update tt_user_project_binds set status = 0 where id = ".$val['id'];
358 $affected = $mdb2->exec($sql);
359 if (is_a($affected, 'PEAR_Error')) die ($res->getMessage());
363 $sql = "delete from tt_user_project_binds where user_id = $user_id and project_id = $project_id";
364 $affected = $mdb2->exec($sql);
365 if (is_a($affected, 'PEAR_Error')) die ($res->getMessage());
370 // updateLastAccess - updates last access info for user in db.
371 static function updateLastAccess() {
373 $mdb2 = getConnection();
374 $accessed_ip = $mdb2->quote($_SERVER['REMOTE_ADDR']);
375 $sql = "update tt_users set accessed = now(), accessed_ip = $accessed_ip where id = $user->id";