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('ttGroupHelper');
32 // Class ttProjectHelper is used to help with project related tasks.
33 class ttProjectHelper {
35 // getAssignedProjects - returns an array of assigned projects.
36 static function getAssignedProjects($user_id) {
38 $mdb2 = getConnection();
40 $group_id = $user->getGroup();
41 $org_id = $user->org_id;
44 // Do a query with inner join to get assigned projects.
45 $sql = "select p.id, p.name, p.tasks, upb.rate from tt_projects p".
46 " inner join tt_user_project_binds upb on (upb.user_id = $user_id and upb.project_id = p.id and upb.status = 1)".
47 " where p.group_id = $group_id and p.org_id = $org_id and p.status = 1 order by p.name";
48 $res = $mdb2->query($sql);
49 if (!is_a($res, 'PEAR_Error')) {
50 while ($val = $res->fetchRow()) {
57 // getRates - returns an array of project rates for user, including deassigned and deactivated projects.
58 static function getRates($user_id) {
60 $mdb2 = getConnection();
62 $group_id = $user->getGroup();
63 $org_id = $user->org_id;
66 $sql = "select p.id, upb.rate from tt_projects p".
67 " inner join tt_user_project_binds upb on (upb.user_id = $user_id and upb.project_id = p.id)".
68 " where p.group_id = $group_id and p.org_id = $org_id";
69 $res = $mdb2->query($sql);
70 if (!is_a($res, 'PEAR_Error')) {
71 while ($val = $res->fetchRow()) {
72 $val['rate'] = str_replace('.', $user->getDecimalMark(), $val['rate']);
79 // getProjects - returns an array of active and inactive projects in group.
80 static function getProjects() {
82 $mdb2 = getConnection();
84 $group_id = $user->getGroup();
85 $org_id = $user->org_id;
88 $sql = "select id, name, tasks from tt_projects".
89 " where group_id = $group_id and org_id = $org_id and (status = 0 or status = 1) order by name";
90 $res = $mdb2->query($sql);
91 if (!is_a($res, 'PEAR_Error')) {
92 while ($val = $res->fetchRow()) {
99 // getProjectsForClient - returns an array of active and inactive projects in a group for a client.
100 static function getProjectsForClient() {
102 $mdb2 = getConnection();
104 $group_id = $user->getGroup();
105 $org_id = $user->org_id;
108 $sql = "select p.id, p.name, p.tasks from tt_projects p".
109 " inner join tt_client_project_binds cpb on (cpb.client_id = $user->client_id and cpb.project_id = p.id)".
110 " where p.group_id = $group_id and p.org_id = $org_id and (p.status = 0 or p.status = 1)".
113 $res = $mdb2->query($sql);
114 if (!is_a($res, 'PEAR_Error')) {
115 while ($val = $res->fetchRow()) {
122 // get - gets details of the project identified by its id.
123 static function get($id) {
125 $mdb2 = getConnection();
127 $group_id = $user->getGroup();
128 $org_id = $user->org_id;
130 $sql = "select id, name, description, status, tasks from tt_projects".
131 " where id = $id and group_id = $group_id and org_id = $org_id and (status = 0 or status = 1)";
132 $res = $mdb2->query($sql);
133 if (!is_a($res, 'PEAR_Error')) {
134 $val = $res->fetchRow();
135 if ($val && $val['id'])
141 // The getProjectByName looks up a project by name.
142 static function getProjectByName($name) {
144 $mdb2 = getConnection();
146 $group_id = $user->getGroup();
147 $org_id = $user->org_id;
149 $sql = "select id from tt_projects".
150 " where group_id = $group_id and org_id = $org_id and name = ".$mdb2->quote($name).
151 " and (status = 1 or status = 0)";
152 $res = $mdb2->query($sql);
153 if (!is_a($res, 'PEAR_Error')) {
154 $val = $res->fetchRow();
155 if ($val && $val['id'])
162 // delete - deletes things associated with a project and marks the project as deleted.
163 static function delete($id) {
165 $mdb2 = getConnection();
167 $group_id = $user->getGroup();
168 $org_id = $user->org_id;
170 // Start with project itself. Reason: if the passed in project_id is bogus,
171 // we'll fail right here and don't damage any other data.
173 // Mark project as deleted and remove associated tasks.
174 $sql = "update tt_projects set status = NULL, tasks = NULL where id = $id and group_id = $group_id and org_id = $org_id";
175 $affected = $mdb2->exec($sql);
176 if (is_a($affected, 'PEAR_Error') || 0 == $affected)
177 return false; // An error ocurred, or 0 rows updated.
179 // Delete user binds to this project.
180 $sql = "delete from tt_user_project_binds where project_id = $id and group_id = $group_id and org_id = $org_id";
181 $affected = $mdb2->exec($sql);
182 if (is_a($affected, 'PEAR_Error'))
185 // Delete task binds to this project.
186 $sql = "delete from tt_project_task_binds where project_id = $id and group_id = $group_id and org_id = $org_id";
187 $affected = $mdb2->exec($sql);
188 if (is_a($affected, 'PEAR_Error'))
191 // Delete client binds to this project.
192 $sql = "delete from tt_client_project_binds where project_id = $id and group_id = $group_id and org_id = $org_id";
193 $affected = $mdb2->exec($sql);
194 if (is_a($affected, 'PEAR_Error'))
200 // insert function inserts a new project into database.
201 static function insert($fields)
204 $mdb2 = getConnection();
206 $group_id = $user->getGroup();
207 $org_id = $user->org_id;
209 $name = $fields['name'];
210 $description = $fields['description'];
211 $users = $fields['users'];
212 $tasks = $fields['tasks'];
213 $comma_separated = implode(',', $tasks); // This is a comma-separated list of associated task ids.
214 $status = $fields['status'];
216 $sql = "insert into tt_projects (group_id, org_id, name, description, tasks, status)".
217 " values ($group_id, $org_id, ".$mdb2->quote($name).", ".$mdb2->quote($description).", ".$mdb2->quote($comma_separated).", ".$mdb2->quote($status).")";
218 $affected = $mdb2->exec($sql);
219 if (is_a($affected, 'PEAR_Error'))
222 $last_id = $mdb2->lastInsertID('tt_projects', 'id');
224 // Bind the project to users.
225 $active_users = ttGroupHelper::getActiveUsers(array('getAllFields'=>true));
226 foreach ($active_users as $u) {
227 if(in_array($u['id'], $users)) {
228 $sql = "insert into tt_user_project_binds (project_id, user_id, group_id, org_id, status, rate) values(
229 $last_id, ".$u['id'].", $group_id, $org_id, 1, ".$u['rate'].")";
230 $affected = $mdb2->exec($sql);
231 if (is_a($affected, 'PEAR_Error'))
236 // Bind the project to tasks in tt_project_task_binds table.
237 $active_tasks = ttGroupHelper::getActiveTasks();
238 foreach ($active_tasks as $task) {
239 if(in_array($task['id'], $tasks)) {
240 $sql = "insert into tt_project_task_binds (project_id, task_id, group_id, org_id)".
241 " values($last_id, ".$task['id'].", $group_id, $org_id)";
242 $affected = $mdb2->exec($sql);
243 if (is_a($affected, 'PEAR_Error'))
251 // update function - updates the project in database.
252 static function update($fields) {
254 $mdb2 = getConnection();
256 $group_id = $user->getGroup();
257 $org_id = $user->org_id;
259 $project_id = $fields['id']; // Project we are updating.
260 $name = $fields['name']; // Project name.
261 $description = $fields['description']; // Project description.
262 $users_to_bind = $fields['users']; // Users to bind with project.
263 $tasks_to_bind = $fields['tasks']; // Tasks to bind with project.
264 $status = $fields['status']; // Project status.
266 // Update records in tt_user_project_binds table.
267 // This logic is complicated because these binds have rates associated with them.
268 // We try to recover old rates if a bind existed before and is now reactivated.
269 $sql = "select user_id, status from tt_user_project_binds".
270 " where project_id = $project_id and group_id = $group_id and org_id = $org_id";
271 $all_users = array();
272 $users_to_update = array();
273 $res2 = $mdb2->query($sql);
274 while ($row = $res2->fetchRow()) {
275 if(!in_array($row['user_id'], $users_to_bind)) {
276 // Delete tt_user_project_binds record (safely).
277 ttUserHelper::deleteBind($row['user_id'], $project_id);
278 } elseif (!$row['status']) {
279 // If we are here, status of the bind is not active. Memorize such users to activate their bind status.
280 $users_to_update[] = $row['user_id']; // Users we need to update in tt_user_project_binds.
282 $all_users[] = $row['user_id']; // All users from tt_user_project_binds for project.
285 $users_to_add = array_diff($users_to_bind, $all_users); // Users missing from tt_user_project_binds, that we need to insert.
286 if(count($users_to_add) > 0) {
287 $sql = "select id, rate from tt_users".
288 " where id in (".join(', ', $users_to_add).") and group_id = $group_id and org_id = $org_id";
289 $res = $mdb2->query($sql);
290 while ($row = $res->fetchRow()) {
291 $user_rate[$row['id']] = $row['rate'];
293 foreach ($users_to_add as $id) {
294 $sql = "insert into tt_user_project_binds (user_id, project_id, group_id, org_id, rate, status)".
295 " values($id, $project_id, $group_id, $org_id, ".$user_rate[$id].", 1)";
296 $affected = $mdb2->exec($sql);
297 if (is_a($affected, 'PEAR_Error'))
301 // Update status (to active) in existing tt_user_project_binds records.
302 if ($users_to_update) {
303 $sql = "update tt_user_project_binds set status = 1".
304 " where project_id = $project_id and user_id in (".join(', ', $users_to_update).") and group_id = $group_id and org_id = $org_id";
305 $affected = $mdb2->exec($sql);
306 if (is_a($affected, 'PEAR_Error'))
309 // End of updating tt_user_project_binds table.
311 // Update records in tt_project_task_binds table by deleting and inserting.
312 $sql = "delete from tt_project_task_binds".
313 " where project_id = $project_id and group_id = $group_id and org_id = $org_id";
314 $affected = $mdb2->exec($sql);
315 if (is_a($affected, 'PEAR_Error'))
318 foreach ($tasks_to_bind as $task_id) {
319 $sql = "insert into tt_project_task_binds (project_id, task_id, group_id, org_id)".
320 " values($project_id, $task_id, $group_id, $org_id)";
321 $affected = $mdb2->exec($sql);
322 if (is_a($affected, 'PEAR_Error'))
325 // End of updating tt_project_task_binds table.
327 // Update project name, description, tasks and status in tt_projects table.
328 $comma_separated = implode(",", $tasks_to_bind); // This is a comma-separated list of associated task ids.
329 $sql = "update tt_projects set name = ".$mdb2->quote($name).", description = ".$mdb2->quote($description).
330 ", tasks = ".$mdb2->quote($comma_separated).", status = ".$mdb2->quote($status).
331 " where id = $project_id and group_id = $group_id and org_id = $org_id";
332 $affected = $mdb2->exec($sql);
333 return (!is_a($affected, 'PEAR_Error'));
336 // getAssignedUsers - returns an array of user ids assigned to a project.
337 static function getAssignedUsers($project_id) {
339 $mdb2 = getConnection();
341 $group_id = $user->getGroup();
342 $org_id = $user->org_id;
345 $sql = "select user_id from tt_user_project_binds".
346 " where project_id = $project_id and group_id = $group_id and org_id = $org_id and status = 1";
347 $res = $mdb2->query($sql);
348 if (is_a($res, 'PEAR_Error')) return false;
349 while ($row = $res->fetchRow()) {
350 $result[] = $row['user_id'];