| index : flyspray | |
| Archlinux32 customized Flyspray installation | gitolite user |
| summaryrefslogtreecommitdiff |
| -rw-r--r-- | includes/class.backend.php | 1869 |
diff --git a/includes/class.backend.php b/includes/class.backend.php new file mode 100644 index 0000000..f440db9 --- /dev/null +++ b/includes/class.backend.php @@ -0,0 +1,1869 @@ +<?php +/** + * Flyspray + * + * Backend class + * + * This script contains reusable functions we use to modify + * various things in the Flyspray database tables. + * + * @license http://opensource.org/licenses/lgpl-license.php Lesser GNU Public License + * @package flyspray + * @author Tony Collins, Florian Schmitz + */ + +if (!defined('IN_FS')) { + die('Do not access this file directly.'); +} + +abstract class Backend +{ + /** + * Adds the user $user_id to the notifications list of $tasks + * @param integer $user_id + * @param array $tasks + * @param bool $do Force execution independent of user permissions + * @access public + * @return bool + * @version 1.0 + */ + public static function add_notification($user_id, $tasks, $do = false) + { + global $db, $user; + + settype($tasks, 'array'); + + $user_id = Flyspray::validUserId($user_id); + + if (!$user_id || !count($tasks)) { + return false; + } + + $sql = $db->query(' SELECT * + FROM {tasks} + WHERE ' . substr(str_repeat(' task_id = ? OR ', count($tasks)), 0, -3), + $tasks); + + while ($row = $db->fetchRow($sql)) { + // -> user adds himself + if ($user->id == $user_id) { + if (!$user->can_view_task($row) && !$do) { + continue; + } + // -> user is added by someone else + } else { + if (!$user->perms('manage_project', $row['project_id']) && !$do) { + continue; + } + } + + $notif = $db->query('SELECT notify_id + FROM {notifications} + WHERE task_id = ? and user_id = ?', + array($row['task_id'], $user_id)); + + if (!$db->countRows($notif)) { + $db->query('INSERT INTO {notifications} (task_id, user_id) + VALUES (?,?)', array($row['task_id'], $user_id)); + Flyspray::logEvent($row['task_id'], 9, $user_id); + } + } + + return (bool) $db->countRows($sql); + } + + + /** + * Removes a user $user_id from the notifications list of $tasks + * @param integer $user_id + * @param array $tasks + * @access public + * @return void + * @version 1.0 + */ + + public static function remove_notification($user_id, $tasks) + { + global $db, $user; + + settype($tasks, 'array'); + + if (!count($tasks)) { + return; + } + + $sql = $db->query(' SELECT * + FROM {tasks} + WHERE ' . substr(str_repeat(' task_id = ? OR ', count($tasks)), 0, -3), + $tasks); + + while ($row = $db->fetchRow($sql)) { + // -> user removes himself + if ($user->id == $user_id) { + if (!$user->can_view_task($row)) { + continue; + } + // -> user is removed by someone else + } else { + if (!$user->perms('manage_project', $row['project_id'])) { + continue; + } + } + + $db->query('DELETE FROM {notifications} + WHERE task_id = ? AND user_id = ?', + array($row['task_id'], $user_id)); + if ($db->affectedRows()) { + Flyspray::logEvent($row['task_id'], 10, $user_id); + } + } + } + + + /** + * Assigns one or more $tasks only to a user $user_id + * @param integer $user_id + * @param array $tasks + * @access public + * @return void + * @version 1.0 + */ + public static function assign_to_me($user_id, $tasks) + { + global $db, $notify; + + $user = $GLOBALS['user']; + if ($user_id != $user->id) { + $user = new User($user_id); + } + + settype($tasks, 'array'); + if (!count($tasks)) { + return; + } + + $sql = $db->query(' SELECT * + FROM {tasks} + WHERE ' . substr(str_repeat(' task_id = ? OR ', count($tasks)), 0, -3), + $tasks); + + while ($row = $db->fetchRow($sql)) { + if (!$user->can_take_ownership($row)) { + continue; + } + + $db->query('DELETE FROM {assigned} + WHERE task_id = ?', + array($row['task_id'])); + + $db->query('INSERT INTO {assigned} + (task_id, user_id) + VALUES (?,?)', + array($row['task_id'], $user->id)); + + if ($db->affectedRows()) { + $current_proj = new Project($row['project_id']); + Flyspray::logEvent($row['task_id'], 19, $user->id, implode(' ', Flyspray::getAssignees($row['task_id']))); + $notify->create(NOTIFY_OWNERSHIP, $row['task_id'], null, null, NOTIFY_BOTH, $current_proj->prefs['lang_code']); + } + + if ($row['item_status'] == STATUS_UNCONFIRMED || $row['item_status'] == STATUS_NEW) { + $db->query('UPDATE {tasks} SET item_status = 3 WHERE task_id = ?', array($row['task_id'])); + Flyspray::logEvent($row['task_id'], 3, 3, 1, 'item_status'); + } + } + } + + /** + * Adds a user $user_id to the assignees of one or more $tasks + * @param integer $user_id + * @param array $tasks + * @param bool $do Force execution independent of user permissions + * @access public + * @return void + * @version 1.0 + */ + public static function add_to_assignees($user_id, $tasks, $do = false) + { + global $db, $notify; + + settype($tasks, 'array'); + + $user = $GLOBALS['user']; + if ($user_id != $user->id) { + $user = new User($user_id); + } + + settype($tasks, 'array'); + if (!count($tasks)) { + return; + } + + $sql = $db->query(' SELECT * + FROM {tasks} + WHERE ' . substr(str_repeat(' task_id = ? OR ', count($tasks)), 0, -3), + $tasks); + + while ($row = $db->fetchRow($sql)) { + if (!$user->can_add_to_assignees($row) && !$do) { + continue; + } + + $db->replace('{assigned}', array('user_id'=> $user->id, 'task_id'=> $row['task_id']), array('user_id','task_id')); + + if ($db->affectedRows()) { + $current_proj = new Project($row['project_id']); + Flyspray::logEvent($row['task_id'], 29, $user->id, implode(' ', Flyspray::getAssignees($row['task_id']))); + $notify->create(NOTIFY_ADDED_ASSIGNEES, $row['task_id'], null, null, NOTIFY_BOTH, $current_proj->prefs['lang_code']); + } + + if ($row['item_status'] == STATUS_UNCONFIRMED || $row['item_status'] == STATUS_NEW) { + $db->query('UPDATE {tasks} SET item_status = 3 WHERE task_id = ?', array($row['task_id'])); + Flyspray::logEvent($row['task_id'], 3, 3, 1, 'item_status'); + } + } + } + + /** + * Adds a vote from $user_id to the task $task_id + * @param integer $user_id + * @param integer $task_id + * @access public + * @return bool + * @version 1.0 + */ + public static function add_vote($user_id, $task_id) + { + global $db; + + $user = $GLOBALS['user']; + if ($user_id != $user->id) { + $user = new User($user_id); + } + + $task = Flyspray::getTaskDetails($task_id); + + if (!$task) { + return false; + } + + if ($user->can_vote($task) > 0) { + + if($db->query("INSERT INTO {votes} (user_id, task_id, date_time) + VALUES (?,?,?)", array($user->id, $task_id, time()))) { + // TODO: Log event in a later version. + return true; + } + } + return false; + } + + /** + * Removes a vote from $user_id to the task $task_id + * @param integer $user_id + * @param integer $task_id + * @access public + * @return bool + * @version 1.0 + */ + public static function remove_vote($user_id, $task_id) + { + global $db; + + $user = $GLOBALS['user']; + if ($user_id != $user->id) { + $user = new User($user_id); + } + + $task = Flyspray::getTaskDetails($task_id); + + if (!$task) { + return false; + } + + if ($user->can_vote($task) == -2) { + + if($db->query("DELETE FROM {votes} WHERE user_id = ? and task_id = ?", + array($user->id, $task_id))) { + // TODO: Log event in a later version. + return true; + } + } + return false; + } + + /** + * Adds a comment to $task + * @param array $task + * @param string $comment_text + * @param integer $time for synchronisation with other functions + * @access public + * @return bool + * @version 1.0 + */ + public static function add_comment($task, $comment_text, $time = null) + { + global $conf, $db, $user, $notify, $proj; + + if (!($user->perms('add_comments', $task['project_id']) && (!$task['is_closed'] || $user->perms('comment_closed', $task['project_id'])))) { + return false; + } + + if($conf['general']['syntax_plugin'] != 'dokuwiki'){ + $purifierconfig = HTMLPurifier_Config::createDefault(); + $purifier = new HTMLPurifier($purifierconfig); + $comment_text = $purifier->purify($comment_text); + } + + if (!is_string($comment_text) || !strlen($comment_text)) { + return false; + } + + $time = !is_numeric($time) ? time() : $time ; + + $db->query('INSERT INTO {comments} + (task_id, date_added, last_edited_time, user_id, comment_text) + VALUES ( ?, ?, ?, ?, ? )', + array($task['task_id'], $time, $time, $user->id, $comment_text)); + $cid = $db->Insert_ID(); + Backend::upload_links($task['task_id'], $cid); + Flyspray::logEvent($task['task_id'], 4, $cid); + + if (Backend::upload_files($task['task_id'], $cid)) { + $notify->create(NOTIFY_COMMENT_ADDED, $task['task_id'], 'files', null, NOTIFY_BOTH, $proj->prefs['lang_code']); + } else { + $notify->create(NOTIFY_COMMENT_ADDED, $task['task_id'], null, null, NOTIFY_BOTH, $proj->prefs['lang_code']); + } + + + return true; + } + + /** + * Upload files for a comment or a task + * @param integer $task_id + * @param integer $comment_id if it is 0, the files will be attached to the task itself + * @param string $source name of the file input + * @access public + * @return bool + * @version 1.0 + */ + public static function upload_files($task_id, $comment_id = 0, $source = 'userfile') + { + global $db, $notify, $conf, $user; + + $task = Flyspray::getTaskDetails($task_id); + + if (!$user->perms('create_attachments', $task['project_id'])) { + return false; + } + + $res = false; + + if (!isset($_FILES[$source]['error'])) { + return false; + } + + foreach ($_FILES[$source]['error'] as $key => $error) { + if ($error != UPLOAD_ERR_OK) { + continue; + } + + + $fname = substr($task_id . '_' . md5(uniqid(mt_rand(), true)), 0, 30); + $path = BASEDIR .'/attachments/'. $fname ; + + $tmp_name = $_FILES[$source]['tmp_name'][$key]; + + // Then move the uploaded file and remove exe permissions + if(!@move_uploaded_file($tmp_name, $path)) { + //upload failed. continue + continue; + } + + @chmod($path, 0644); + $res = true; + + // Use a different MIME type + $fileparts = explode( '.', $_FILES[$source]['name'][$key]); + $extension = end($fileparts); + if (isset($conf['attachments'][$extension])) { + $_FILES[$source]['type'][$key] = $conf['attachments'][$extension]; + //actually, try really hard to get the real filetype, not what the browser reports. + } elseif($type = Flyspray::check_mime_type($path)) { + $_FILES[$source]['type'][$key] = $type; + }// we can try even more, however, far too much code is needed. + + $db->query("INSERT INTO {attachments} + ( task_id, comment_id, file_name, + file_type, file_size, orig_name, + added_by, date_added) + VALUES (?, ?, ?, ?, ?, ?, ?, ?)", + array($task_id, $comment_id, $fname, + $_FILES[$source]['type'][$key], + $_FILES[$source]['size'][$key], + $_FILES[$source]['name'][$key], + $user->id, time())); + $attid = $db->insert_ID(); + Flyspray::logEvent($task_id, 7, $attid, $_FILES[$source]['name'][$key]); + } + + return $res; + } + + public static function upload_links($task_id, $comment_id = 0, $source = 'userlink') + { + global $db, $user; + + $task = Flyspray::getTaskDetails($task_id); + + if (!$user->perms('create_attachments', $task['project_id'])) { + return false; + } + + if (!isset($_POST[$source])) { + return false; + } + + $res = false; + foreach($_POST[$source] as $text) { + $text = filter_var($text, FILTER_SANITIZE_URL); + + if( preg_match( '/^\s*(javascript:|data:)/', $text)){ + continue; + } + + if(empty($text)) { + continue; + } + + $res = true; + + // Insert into database + $db->query("INSERT INTO {links} (task_id, comment_id, url, added_by, date_added) VALUES (?, ?, ?, ?, ?)", + array($task_id, $comment_id, $text, $user->id, time())); + // TODO: Log event in a later version. + } + + return $res; + } + + /** + * Delete one or more attachments of a task or comment + * @param array $attachments + * @access public + * @return void + * @version 1.0 + */ + public static function delete_files($attachments) + { + global $db, $user; + + settype($attachments, 'array'); + if (!count($attachments)) { + return; + } + + $sql = $db->query(' SELECT t.*, a.* + FROM {attachments} a + LEFT JOIN {tasks} t ON t.task_id = a.task_id + WHERE ' . substr(str_repeat(' attachment_id = ? OR ', count($attachments)), 0, -3), + $attachments); + + while ($task = $db->fetchRow($sql)) { + if (!$user->perms('delete_attachments', $task['project_id'])) { + continue; + } + + $db->query('DELETE FROM {attachments} WHERE attachment_id = ?', + array($task['attachment_id'])); + @unlink(BASEDIR . '/attachments/' . $task['file_name']); + Flyspray::logEvent($task['task_id'], 8, $task['orig_name']); + } + } + + public static function delete_links($links) + { + global $db, $user; + + settype($links, 'array'); + + if(!count($links)) { + return; + } + + $sql = $db->query('SELECT t.*, l.* FROM {links} l LEFT JOIN {tasks} t ON t.task_id = l.task_id WHERE '.substr(str_repeat('link_id = ? OR ', count($links)), 0, -3), $links); + + //Delete from database + while($task = $db->fetchRow($sql)) { + if (!$user->perms('delete_attachments', $task['project_id'])) { + continue; + } + + $db->query('DELETE FROM {links} WHERE link_id = ?', array($task['link_id'])); + // TODO: Log event in a later version. + } + } + + /** + * Cleans a username (length, special chars, spaces) + * @param string $user_name + * @access public + * @return string + */ + public static function clean_username($user_name) + { + // Limit length + $user_name = substr(trim($user_name), 0, 32); + // Remove doubled up spaces and control chars + $user_name = preg_replace('![\x00-\x1f\s]+!u', ' ', $user_name); + // Strip special chars + return utf8_keepalphanum($user_name); + } + + public static function getAdminAddresses() { + global $db; + + $emails = array(); + $jabbers = array(); + $onlines = array(); + + $sql = $db->query('SELECT DISTINCT u.user_id, u.email_address, u.jabber_id, + u.notify_online, u.notify_type, u.notify_own, u.lang_code + FROM {users} u + JOIN {users_in_groups} ug ON u.user_id = ug.user_id + JOIN {groups} g ON g.group_id = ug.group_id + WHERE g.is_admin = 1 AND u.account_enabled = 1'); + + Notifications::assignRecipients($db->fetchAllArray($sql), $emails, $jabbers, $onlines); + + return array($emails, $jabbers, $onlines); + } + + public static function getProjectManagerAddresses($project_id) { + global $db; + + $emails = array(); + $jabbers = array(); + $onlines = array(); + + $sql = $db->query('SELECT DISTINCT u.user_id, u.email_address, u.jabber_id, + u.notify_online, u.notify_type, u.notify_own, u.lang_code + FROM {users} u + JOIN {users_in_groups} ug ON u.user_id = ug.user_id + JOIN {groups} g ON g.group_id = ug.group_id + WHERE g.manage_project = 1 AND g.project_id = ? AND u.account_enabled = 1', + array($project_id)); + + Notifications::assignRecipients($db->fetchAllArray($sql), $emails, $jabbers, $onlines); + + return array($emails, $jabbers, $onlines); + } + /** + * Creates a new user + * @param string $user_name + * @param string $password + * @param string $real_name + * @param string $jabber_id + * @param string $email + * @param integer $notify_type + * @param integer $time_zone + * @param integer $group_in + * @access public + * @return bool false if username is already taken + * @version 1.0 + * @notes This function does not have any permission checks (checked elsewhere) + */ + public static function create_user($user_name, $password, $real_name, $jabber_id, $email, $notify_type, $time_zone, $group_in, $enabled, $oauth_uid = '', $oauth_provider = '', $profile_image = '') + { + global $fs, $db, $notify, $baseurl; + + $user_name = Backend::clean_username($user_name); + + // TODO Handle this whole create_user better concerning return false. Why did it fail? + # 'notassigned' and '-1' are possible filtervalues for advanced task search + if( empty($user_name) || ctype_digit($user_name) || $user_name == '-1' || $user_name=='notassigned' ) { + return false; + } + + // Limit length + $real_name = substr(trim($real_name), 0, 100); + // Remove doubled up spaces and control chars + $real_name = preg_replace('![\x00-\x1f\s]+!u', ' ', $real_name); + + # 'notassigned' and '-1' are possible filtervalues for advanced task search, lets avoid them + if( ctype_digit($real_name) || $real_name == '-1' || $real_name=='notassigned' ) { + return false; + } + + // Check to see if the username is available + $sql = $db->query('SELECT COUNT(*) FROM {users} WHERE user_name = ?', array($user_name)); + + if ($db->fetchOne($sql)) { + return false; + } + + $auto = false; + // Autogenerate a password + if (!$password) { + $auto = true; + $password = substr(md5(uniqid(mt_rand(), true)), 0, mt_rand(8, 12)); + } + + // Check the emails before inserting anything to database. + $emailList = explode(';',$email); + foreach ($emailList as $mail) { //Still need to do: check email + $count = $db->query("SELECT COUNT(*) FROM {user_emails} WHERE email_address = ?",array($mail)); + $count = $db->fetchOne($count); + if ($count > 0) { + Flyspray::show_error("Email address has alredy been taken"); + return false; + } + } + + $db->query("INSERT INTO {users} + ( user_name, user_pass, real_name, jabber_id, profile_image, magic_url, + email_address, notify_type, account_enabled, + tasks_perpage, register_date, time_zone, dateformat, + dateformat_extended, oauth_uid, oauth_provider, lang_code) + VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, 25, ?, ?, ?, ?, ?, ?, ?)", + array($user_name, Flyspray::cryptPassword($password), $real_name, strtolower($jabber_id), + $profile_image, '', strtolower($email), $notify_type, $enabled, time(), $time_zone, '', '', $oauth_uid, $oauth_provider, $fs->prefs['lang_code'])); + + // Get this user's id for the record + $uid = Flyspray::userNameToId($user_name); + + foreach ($emailList as $mail) { + if ($mail != '') { + $db->query("INSERT INTO {user_emails}(id,email_address,oauth_uid,oauth_provider) VALUES (?,?,?,?)", + array($uid,strtolower($mail),$oauth_uid, $oauth_provider)); + } + } + + // Now, create a new record in the users_in_groups table + $db->query('INSERT INTO {users_in_groups} (user_id, group_id) + VALUES (?, ?)', array($uid, $group_in)); + + Flyspray::logEvent(0, 30, serialize(Flyspray::getUserDetails($uid))); + + $varnames = array('iwatch','atome','iopened'); + + $toserialize = array('string' => NULL, + 'type' => array (''), + 'sev' => array (''), + 'due' => array (''), + 'dev' => NULL, + 'cat' => array (''), + 'status' => array ('open'), + 'order' => NULL, + 'sort' => NULL, + 'percent' => array (''), + 'opened' => NULL, + 'search_in_comments' => NULL, + 'search_for_all' => NULL, + 'reported' => array (''), + 'only_primary' => NULL, + 'only_watched' => NULL); + + foreach($varnames as $tmpname) { + if($tmpname == 'iwatch') { + $tmparr = array('only_watched' => '1'); + } elseif ($tmpname == 'atome') { + $tmparr = array('dev'=> $uid); + } elseif($tmpname == 'iopened') { + $tmparr = array('opened'=> $uid); + } + $$tmpname = $tmparr + $toserialize; + } + + // Now give him his default searches + $db->query('INSERT INTO {searches} (user_id, name, search_string, time) + VALUES (?, ?, ?, ?)', + array($uid, L('taskswatched'), serialize($iwatch), time())); + $db->query('INSERT INTO {searches} (user_id, name, search_string, time) + VALUES (?, ?, ?, ?)', + array($uid, L('assignedtome'), serialize($atome), time())); + $db->query('INSERT INTO {searches} (user_id, name, search_string, time) + VALUES (?, ?, ?, ?)', + array($uid, L('tasksireported'), serialize($iopened), time())); + + if ($jabber_id) { + Notifications::jabberRequestAuth($jabber_id); + } + + // Send a user his details (his username might be altered, password auto-generated) + // dont send notifications if the user logged in using oauth + if (!$oauth_provider) { + $recipients = self::getAdminAddresses(); + $newuser = array(); + + // Add the right message here depending on $enabled. + if ($enabled === 0) { + $newuser[0][$email] = array('recipient' => $email, 'lang' => $fs->prefs['lang_code']); + + } else { + $newuser[0][$email] = array('recipient' => $email, 'lang' => $fs->prefs['lang_code']); + } + + // Notify the appropriate users + if ($fs->prefs['notify_registration']) { + $notify->create(NOTIFY_NEW_USER, null, + array($baseurl, $user_name, $real_name, $email, $jabber_id, $password, $auto), + $recipients, NOTIFY_EMAIL); + } + // And also the new user + $notify->create(NOTIFY_OWN_REGISTRATION, null, + array($baseurl, $user_name, $real_name, $email, $jabber_id, $password, $auto), + $newuser, NOTIFY_EMAIL); + } + + // If the account is created as not enabled, no matter what any + // preferences might say or how the registration was made in first + // place, it MUST be first approved by an admin. And a small + // work-around: there's no field for email, so we use reason_given + // for that purpose. + if ($enabled === 0) { + Flyspray::adminRequest(3, 0, 0, $uid, $email); + } + + return true; + } + + /** + * Deletes a user + * @param integer $uid + * @access public + * @return bool + * @version 1.0 + */ + public static function delete_user($uid) + { + global $db, $user; + + if (!$user->perms('is_admin')) { + return false; + } + + $userDetails = Flyspray::getUserDetails($uid); + + if (is_file(BASEDIR.'/avatars/'.$userDetails['profile_image'])) { + unlink(BASEDIR.'/avatars/'.$userDetails['profile_image']); + } + + $tables = array('users', 'users_in_groups', 'searches', 'notifications', 'assigned', 'votes', 'effort'); + # FIXME Deleting a users effort without asking when user is deleted may not be wanted in every situation. + # For example for billing a project and the deleted user worked for a project. + # The better solution is to just deactivate the user, but maybe there are cases a user MUSt be deleted from the database. + # Move that effort to an 'anonymous users' effort if the effort(s) was legal and should be measured for project(s)? + foreach ($tables as $table) { + if (!$db->query('DELETE FROM ' .'{' . $table .'}' . ' WHERE user_id = ?', array($uid))) { + return false; + } + } + + if (!empty($userDetails['profile_image']) && is_file(BASEDIR.'/avatars/'.$userDetails['profile_image'])) { + unlink(BASEDIR.'/avatars/'.$userDetails['profile_image']); + } + + $db->query('DELETE FROM {registrations} WHERE email_address = ?', + array($userDetails['email_address'])); + + $db->query('DELETE FROM {user_emails} WHERE id = ?', + array($uid)); + + $db->query('DELETE FROM {reminders} WHERE to_user_id = ? OR from_user_id = ?', + array($uid, $uid)); + + // for the unusual situuation that a user ID is re-used, make sure that the new user doesn't + // get permissions for a task automatically + $db->query('UPDATE {tasks} SET opened_by = 0 WHERE opened_by = ?', array($uid)); + + Flyspray::logEvent(0, 31, serialize($userDetails)); + + return true; + } + + + /** + * Deletes a project + * @param integer $pid + * @param integer $move_to to which project contents of the project are moved + * @access public + * @return bool + * @version 1.0 + */ + public static function delete_project($pid, $move_to = 0) + { + global $db, $user; + + if (!$user->perms('manage_project', $pid)) { + return false; + } + + // Delete all project's tasks related information + if (!$move_to) { + $task_ids = $db->query('SELECT task_id FROM {tasks} WHERE project_id = ' . intval($pid)); + $task_ids = $db->fetchCol($task_ids); + // What was supposed to be in tables field_values, notification_threads + // and redundant, they do not exist in database? + $tables = array('admin_requests', 'assigned', 'attachments', 'comments', + 'dependencies', 'related', 'history', + 'notifications', + 'reminders', 'votes'); + foreach ($tables as $table) { + if ($table == 'related') { + $stmt = $db->dblink->prepare('DELETE FROM ' . $db->dbprefix . $table . ' WHERE this_task = ? OR related_task = ? '); + } else { + $stmt = $db->dblink->prepare('DELETE FROM ' . $db->dbprefix . $table . ' WHERE task_id = ?'); + } + foreach ($task_ids as $id) { + $db->dblink->execute($stmt, ($table == 'related') ? array($id, $id) : array($id)); + } + } + } + + // unset category of tasks because we don't move categories + if ($move_to) { + $db->query('UPDATE {tasks} SET product_category = 0 WHERE project_id = ?', array($pid)); + } + + $tables = array('list_category', 'list_os', 'list_resolution', 'list_tasktype', + 'list_status', 'list_version', 'admin_requests', + 'cache', 'projects', 'tasks'); + + foreach ($tables as $table) { + if ($move_to && $table !== 'projects' && $table !== 'list_category') { + // Having a unique index in most list_* tables prevents + // doing just a simple update, if the list item already + // exists in target project, so we have to update existing + // tasks to use the one in target project. Something similar + // should be done when moving a single task to another project. + // Consider making this a separate function that can be used + // for that purpose too, if possible. + if (strpos($table, 'list_') === 0) { + list($type, $name) = explode('_', $table); + $sql = $db->query('SELECT ' . $name . '_id, ' . $name . '_name + FROM {' . $table . '} + WHERE project_id = ?', + array($pid)); + $rows = $db->fetchAllArray($sql); + foreach ($rows as $row) { + $sql = $db->query('SELECT ' . $name . '_id + FROM {' . $table . '} + WHERE project_id = ? AND '. $name . '_name = ?', + array($move_to, $row[$name .'_name'])); + $new_id = $db->fetchOne($sql); + if ($new_id) { + switch ($name) { + case 'os'; + $column = 'operating_system'; + break; + case 'resolution'; + $column = 'resolution_reason'; + break; + case 'tasktype'; + $column = 'task_type'; + break; + case 'status'; + $column = 'item_status'; + break; + case 'version'; + // Questionable what to do with this one. 1.0 could + // have been still future in the old project and + // already past in the new one... + $column = 'product_version'; + break; + } + if (isset($column)) { + $db->query('UPDATE {tasks} + SET ' . $column . ' = ? + WHERE ' . $column . ' = ?', + array($new_id, $row[$name . '_id'])); + $db->query('DELETE FROM {' . $table . '} + WHERE ' . $name . '_id = ?', + array($row[$name . '_id'])); + } + } + } + } + $base_sql = 'UPDATE {' . $table . '} SET project_id = ?'; + $sql_params = array($move_to, $pid); + } else { + $base_sql = 'DELETE FROM {' . $table . '}'; + $sql_params = array($pid); + } + + if (!$db->query($base_sql . ' WHERE project_id = ?', $sql_params)) { + return false; + } + } + + // groups are only deleted, not moved (it is likely + // that the destination project already has all kinds + // of groups which are also used by the old project) + $sql = $db->query('SELECT group_id FROM {groups} WHERE project_id = ?', array($pid)); + while ($row = $db->fetchRow($sql)) { + $db->query('DELETE FROM {users_in_groups} WHERE group_id = ?', array($row['group_id'])); + } + $sql = $db->query('DELETE FROM {groups} WHERE project_id = ?', array($pid)); + + //we have enough reasons .. the process is OK. + return true; + } + + /** + * Adds a reminder to a task + * @param integer $task_id + * @param string $message + * @param integer $how_often send a reminder every ~ seconds + * @param integer $start_time time when the reminder starts + * @param $user_id the user who is reminded. by default (null) all users assigned to the task are reminded. + * @access public + * @return bool + * @version 1.0 + */ + public static function add_reminder($task_id, $message, $how_often, $start_time, $user_id = null) + { + global $user, $db; + $task = Flyspray::getTaskDetails($task_id); + + if (!$user->perms('manage_project', $task['project_id'])) { + return false; + } + + if (is_null($user_id)) { + // Get all users assigned to a task + $user_id = Flyspray::getAssignees($task_id); + } else { + $user_id = array(Flyspray::validUserId($user_id)); + if (!reset($user_id)) { + return false; + } + } + + foreach ($user_id as $id) { + $sql = $db->replace('{reminders}', + array('task_id'=> $task_id, 'to_user_id'=> $id, + 'from_user_id' => $user->id, 'start_time' => $start_time, + 'how_often' => $how_often, 'reminder_message' => $message), + array('task_id', 'to_user_id', 'how_often', 'reminder_message')); + if(!$sql) { + // query has failed :( + return false; + } + } + // 2 = no record has found and was INSERT'ed correclty + if (isset($sql) && $sql == 2) { + Flyspray::logEvent($task_id, 17, $task_id); + } + return true; + } + + /** + * Adds a new task + * @param array $args array containing all task properties. unknown properties will be ignored + * @access public + * @return integer the task ID on success + * @version 1.0 + * @notes $args is POST data, bad..bad user.. + */ + public static function create_task($args) + { + global $conf, $db, $user, $proj; + + if (!isset($args)) return 0; + + // these are the POST variables that the user MUST send, if one of + // them is missing or if one of them is empty, then we have to abort + $requiredPostArgs = array('item_summary', 'project_id');//modify: made description not required + foreach ($requiredPostArgs as $required) { + if (empty($args[$required])) return 0; + } + + $notify = new Notifications(); + if ($proj->id != $args['project_id']) { + $proj = new Project($args['project_id']); + } + + if (!$user->can_open_task($proj)) { + return 0; + } + + // first populate map with default values + $sql_args = array( + 'project_id' => $proj->id, + 'date_opened' => time(), + 'last_edited_time' => time(), + 'opened_by' => intval($user->id), + 'percent_complete' => 0, + 'mark_private' => 0, + 'supertask_id' => 0, + 'closedby_version' => 0, + 'closure_comment' => '', + 'task_priority' => 2, + 'due_date' => 0, + 'anon_email' => '', + 'item_status'=> STATUS_UNCONFIRMED + ); + + // POST variables the user is ALLOWED to provide + $allowedPostArgs = array( + 'task_type', 'product_category', 'product_version', + 'operating_system', 'task_severity', 'estimated_effort', + 'supertask_id', 'item_summary', 'detailed_desc' + ); + // these POST variables the user is only ALLOWED to provide if he got the permissions + if ($user->perms('modify_all_tasks')) { + $allowedPostArgs[] = 'closedby_version'; + $allowedPostArgs[] = 'task_priority'; + $allowedPostArgs[] = 'due_date'; + $allowedPostArgs[] = 'item_status'; + } + if ($user->perms('manage_project')) { + $allowedPostArgs[] = 'mark_private'; + } + // now copy all over all POST variables the user is ALLOWED to provide + // (but only if they are not empty) + foreach ($allowedPostArgs as $allowed) { + if (!empty($args[$allowed])) { + $sql_args[$allowed] = $args[$allowed]; + } + } + + // Process the due_date + if ( isset($args['due_date']) && ($due_date = $args['due_date']) || ($due_date = 0) ) { + $due_date = Flyspray::strtotime($due_date); + } + + $sql_params[] = 'mark_private'; + $sql_values[] = intval($user->perms('manage_project') && isset($args['mark_private']) && $args['mark_private'] == '1'); + + $sql_params[] = 'due_date'; + $sql_values[] = $due_date; + + $sql_params[] = 'closure_comment'; + $sql_values[] = ''; + + // Process estimated effort + $estimated_effort = 0; + if ($proj->prefs['use_effort_tracking'] && isset($sql_args['estimated_effort'])) { + if (($estimated_effort = effort::editStringToSeconds($sql_args['estimated_effort'], $proj->prefs['hours_per_manday'], $proj->prefs['estimated_effort_format'])) === FALSE) { + Flyspray::show_error(L('invalideffort')); + $estimated_effort = 0; + } + $sql_args['estimated_effort'] = $estimated_effort; + } + + // Token for anonymous users + $token = ''; + if ($user->isAnon()) { + if (empty($args['anon_email'])) { + return 0; + } + $token = md5(function_exists('openssl_random_pseudo_bytes') ? + openssl_random_pseudo_bytes(32) : + uniqid(mt_rand(), true)); + $sql_args['task_token'] = $token; + $sql_args['anon_email'] = $args['anon_email']; + } + + // ensure all variables are in correct format + if (!empty($sql_args['due_date'])) { + $sql_args['due_date'] = Flyspray::strtotime($sql_args['due_date']); + } + if (isset($sql_args['mark_private'])) { + $sql_args['mark_private'] = intval($sql_args['mark_private'] == '1'); + } + + # dokuwiki syntax plugin filters on output + if($conf['general']['syntax_plugin'] != 'dokuwiki' && isset($sql_args['detailed_desc']) ){ + $purifierconfig = HTMLPurifier_Config::createDefault(); + $purifier = new HTMLPurifier($purifierconfig); + $sql_args['detailed_desc'] = $purifier->purify($sql_args['detailed_desc']); + } + + // split keys and values into two separate arrays + $sql_keys = array(); + $sql_values = array(); + foreach ($sql_args as $key => $value) { + $sql_keys[] = $key; + $sql_values[] = $value; + } + + /* + * TODO: At least with PostgreSQL, this has caused the sequence to be + * out of sync with reality. Must be fixed in upgrade process. Check + * what's the situation with MySQL. (It's fine, it updates the value even + * if the column was manually adjusted. Remove this whole block later.) + $result = $db->query('SELECT MAX(task_id)+1 + FROM {tasks}'); + $task_id = $db->fetchOne($result); + $task_id = $task_id ? $task_id : 1; + */ + //now, $task_id is always the first element of $sql_values + #array_unshift($sql_keys, 'task_id'); + #array_unshift($sql_values, $task_id); + + $sql_keys_string = join(', ', $sql_keys); + $sql_placeholder = $db->fill_placeholders($sql_values); + + $result = $db->query("INSERT INTO {tasks} + ($sql_keys_string) + VALUES ($sql_placeholder)", $sql_values); + $task_id=$db->insert_ID(); + + Backend::upload_links($task_id); + + // create tags + if (isset($args['tags'])) { + $tagList = explode(';', $args['tags']); + $tagList = array_map('strip_tags', $tagList); + $tagList = array_map('trim', $tagList); + $tagList = array_unique($tagList); # avoid duplicates for inputs like: "tag1;tag1" or "tag1; tag1<p></p>" + foreach ($tagList as $tag){ + if ($tag == ''){ + continue; + } + + # old tag feature + #$result2 = $db->query("INSERT INTO {tags} (task_id, tag) VALUES (?,?)",array($task_id,$tag)); + + # new tag feature. let's do it in 2 steps, it is getting too complicated to make it cross database compatible, drawback is possible (rare) race condition (use transaction?) + $res=$db->query("SELECT tag_id FROM {list_tag} WHERE (project_id=0 OR project_id=?) AND tag_name LIKE ? ORDER BY project_id", array($proj->id,$tag) ); + if($t=$db->fetchRow($res)){ + $tag_id=$t['tag_id']; + } else{ + if( $proj->prefs['freetagging']==1){ + # add to taglist of the project + $db->query("INSERT INTO {list_tag} (project_id,tag_name) VALUES (?,?)", array($proj->id,$tag)); + $tag_id=$db->insert_ID(); + } else{ + continue; + } + }; + $db->query("INSERT INTO {task_tag}(task_id,tag_id) VALUES(?,?)", array($task_id, $tag_id) ); + } + } + + // Log the assignments and send notifications to the assignees + if (isset($args['rassigned_to']) && is_array($args['rassigned_to'])) + { + // Convert assigned_to and store them in the 'assigned' table + foreach ($args['rassigned_to'] as $val) + { + $db->replace('{assigned}', array('user_id'=> $val, 'task_id'=> $task_id), array('user_id','task_id')); + } + // Log to task history + Flyspray::logEvent($task_id, 14, implode(' ', $args['rassigned_to'])); + + // Notify the new assignees what happened. This obviously won't happen if the task is now assigned to no-one. + $notify->create(NOTIFY_NEW_ASSIGNEE, $task_id, null, $notify->specificAddresses($args['rassigned_to']), NOTIFY_BOTH, $proj->prefs['lang_code']); + } + + // Log that the task was opened + Flyspray::logEvent($task_id, 1); + + $result = $db->query('SELECT * + FROM {list_category} + WHERE category_id = ?', + array($args['product_category'])); + $cat_details = $db->fetchRow($result); + + // We need to figure out who is the category owner for this task + if (!empty($cat_details['category_owner'])) { + $owner = $cat_details['category_owner']; + } + else { + // check parent categories + $result = $db->query('SELECT * + FROM {list_category} + WHERE lft < ? AND rgt > ? AND project_id = ? + ORDER BY lft DESC', + array($cat_details['lft'], $cat_details['rgt'], $cat_details['project_id'])); + while ($row = $db->fetchRow($result)) { + // If there's a parent category owner, send to them + if (!empty($row['category_owner'])) { + $owner = $row['category_owner']; + break; + } + } + } + + if (!isset($owner)) { + $owner = $proj->prefs['default_cat_owner']; + } + + if ($owner) { + if ($proj->prefs['auto_assign'] && ($args['item_status'] == STATUS_UNCONFIRMED || $args['item_status'] == STATUS_NEW)) { + Backend::add_to_assignees($owner, $task_id, true); + } + Backend::add_notification($owner, $task_id, true); + } + + // Reminder for due_date field + if (!empty($sql_args['due_date'])) { + Backend::add_reminder($task_id, L('defaultreminder') . "\n\n" . createURL('details', $task_id), 2*24*60*60, time()); + } + + // Create the Notification + if (Backend::upload_files($task_id)) { + $notify->create(NOTIFY_TASK_OPENED, $task_id, 'files', null, NOTIFY_BOTH, $proj->prefs['lang_code']); + } else { + $notify->create(NOTIFY_TASK_OPENED, $task_id, null, null, NOTIFY_BOTH, $proj->prefs['lang_code']); + } + + // If the reporter wanted to be added to the notification list + if (isset($args['notifyme']) && $args['notifyme'] == '1' && $user->id != $owner) { + Backend::add_notification($user->id, $task_id, true); + } + + if ($user->isAnon()) { + $anonuser = array(); + $anonuser[$email] = array('recipient' => $args['anon_email'], 'lang' => $fs->prefs['lang_code']); + $recipients = array($anonuser); + $notify->create(NOTIFY_ANON_TASK, $task_id, $token, + $recipients, NOTIFY_EMAIL, $proj->prefs['lang_code']); + } + + return array($task_id, $token); + } + + /** + * Closes a task + * @param integer $task_id + * @param integer $reason + * @param string $comment + * @param bool $mark100 + * @access public + * @return bool + * @version 1.0 + */ + public static function close_task($task_id, $reason, $comment, $mark100 = true) + { + global $db, $notify, $user, $proj; + $task = Flyspray::getTaskDetails($task_id); + + if (!$user->can_close_task($task)) { + return false; + } + + if ($task['is_closed']) { + return false; + } + + $db->query('UPDATE {tasks} + SET date_closed = ?, closed_by = ?, closure_comment = ?, + is_closed = 1, resolution_reason = ?, last_edited_time = ?, + last_edited_by = ? + WHERE task_id = ?', + array(time(), $user->id, $comment, $reason, time(), $user->id, $task_id)); + + if ($mark100) { + $db->query('UPDATE {tasks} SET percent_complete = 100 WHERE task_id = ?', + array($task_id)); + + Flyspray::logEvent($task_id, 3, 100, $task['percent_complete'], 'percent_complete'); + } + + $notify->create(NOTIFY_TASK_CLOSED, $task_id, null, null, NOTIFY_BOTH, $proj->prefs['lang_code']); + Flyspray::logEvent($task_id, 2, $reason, $comment); + + // If there's an admin request related to this, close it + $db->query('UPDATE {admin_requests} + SET resolved_by = ?, time_resolved = ? + WHERE task_id = ? AND request_type = ?', + array($user->id, time(), $task_id, 1)); + + // duplicate + if ($reason == RESOLUTION_DUPLICATE) { + preg_match("/\b(?:FS#|bug )(\d+)\b/", $comment, $dupe_of); + if (count($dupe_of) >= 2) { + $existing = $db->query('SELECT * FROM {related} WHERE this_task = ? AND related_task = ? AND is_duplicate = 1', + array($task_id, $dupe_of[1])); + + if ($existing && $db->countRows($existing) == 0) { + $db->query('INSERT INTO {related} (this_task, related_task, is_duplicate) VALUES(?, ?, 1)', + array($task_id, $dupe_of[1])); + } + Backend::add_vote($task['opened_by'], $dupe_of[1]); + } + } + + return true; + } + + /** + * Returns an array of tasks (respecting pagination) and an ID list (all tasks) + * @param array $args + * @param array $visible + * @param integer $offset + * @param integer $comment + * @param bool $perpage + * @access public + * @return array + * @version 1.0 + */ + public static function get_task_list($args, $visible, $offset = 0, $perpage = 20) { + global $fs, $proj, $db, $user, $conf; + /* build SQL statement {{{ */ + // Original SQL courtesy of Lance Conry http://www.rhinosw.com/ + $where = $sql_params = array(); + + // echo '<pre>' . print_r($visible, true) . '</pre>'; + // echo '<pre>' . print_r($args, true) . '</pre>'; + // PostgreSQL LIKE searches are by default case sensitive, + // so we use ILIKE instead. For other databases, in our case + // only MySQL/MariaDB, LIKE is good for our purposes. + $LIKEOP = 'LIKE'; + if ($db->dblink->dataProvider == 'postgres') { + $LIKEOP = 'ILIKE'; + } + + $select = ''; + $groupby = 't.task_id, '; + $cgroupbyarr = array(); + + // Joins absolutely needed for user viewing rights + $from = ' {tasks} t +-- All tasks have a project! +JOIN {projects} p ON t.project_id = p.project_id'; + + // Not needed for anonymous users + if (!$user->isAnon()) { +$from .= ' -- Global group always exists +JOIN ({groups} gpg + JOIN {users_in_groups} gpuig ON gpg.group_id = gpuig.group_id AND gpuig.user_id = ? +) ON gpg.project_id = 0 +-- Project group might exist or not. +LEFT JOIN ({groups} pg + JOIN {users_in_groups} puig ON pg.group_id = puig.group_id AND puig.user_id = ? +) ON pg.project_id = t.project_id'; + $sql_params[] = $user->id; + $sql_params[] = $user->id; + } + + // Keep this always, could also used for showing assigned users for a task. + // Keeps the overall logic somewhat simpler. + $from .= ' LEFT JOIN {assigned} ass ON t.task_id = ass.task_id'; + $from .= ' LEFT JOIN {task_tag} tt ON t.task_id = tt.task_id'; + $cfrom = $from; + + // Seems resution name really is needed... + $select .= 'lr.resolution_name, '; + $from .= ' LEFT JOIN {list_resolution} lr ON t.resolution_reason = lr.resolution_id '; + $groupby .= 'lr.resolution_name, '; + + // Otherwise, only join tables which are really necessary to speed up the db-query + if (array_get($args, 'type') || in_array('tasktype', $visible)) { + $select .= ' lt.tasktype_name, '; + $from .= ' +LEFT JOIN {list_tasktype} lt ON t.task_type = lt.tasktype_id '; + $groupby .= ' lt.tasktype_id, '; + } + + if (array_get($args, 'status') || in_array('status', $visible)) { + $select .= ' lst.status_name, '; + $from .= ' +LEFT JOIN {list_status} lst ON t.item_status = lst.status_id '; + $groupby .= ' lst.status_id, '; + } + + if (array_get($args, 'cat') || in_array('category', $visible)) { + $select .= ' lc.category_name AS category_name, '; + $from .= ' +LEFT JOIN {list_category} lc ON t.product_category = lc.category_id '; + $groupby .= 'lc.category_id, '; + } + + if (in_array('votes', $visible)) { + $select .= ' (SELECT COUNT(vot.vote_id) FROM {votes} vot WHERE vot.task_id = t.task_id) AS num_votes, '; + } + + $maxdatesql = ' GREATEST(COALESCE((SELECT max(c.date_added) FROM {comments} c WHERE c.task_id = t.task_id), 0), t.date_opened, t.date_closed, t.last_edited_time) '; + $search_for_changes = in_array('lastedit', $visible) || array_get($args, 'changedto') || array_get($args, 'changedfrom'); + if ($search_for_changes) { + $select .= ' GREATEST(COALESCE((SELECT max(c.date_added) FROM {comments} c WHERE c.task_id = t.task_id), 0), t.date_opened, t.date_closed, t.last_edited_time) AS max_date, '; + $cgroupbyarr[] = 't.task_id'; + } + + if (array_get($args, 'search_in_comments')) { + $from .= ' +LEFT JOIN {comments} c ON t.task_id = c.task_id '; + $cfrom .= ' +LEFT JOIN {comments} c ON t.task_id = c.task_id '; + $cgroupbyarr[] = 't.task_id'; + } + + if (in_array('comments', $visible)) { + $select .= ' (SELECT COUNT(cc.comment_id) FROM {comments} cc WHERE cc.task_id = t.task_id) AS num_comments, '; + } + + if (in_array('reportedin', $visible)) { + $select .= ' lv.version_name AS product_version_name, '; + $from .= ' +LEFT JOIN {list_version} lv ON t.product_version = lv.version_id '; + $groupby .= 'lv.version_id, '; + } + + if (array_get($args, 'opened') || in_array('openedby', $visible)) { + $select .= ' uo.real_name AS opened_by_name, '; + $from .= ' +LEFT JOIN {users} uo ON t.opened_by = uo.user_id '; + $groupby .= 'uo.user_id, '; + if (array_get($args, 'opened')) { + $cfrom .= ' +LEFT JOIN {users} uo ON t.opened_by = uo.user_id '; + } + } + + if (array_get($args, 'closed')) { + $select .= ' uc.real_name AS closed_by_name, '; + $from .= ' +LEFT JOIN {users} uc ON t.closed_by = uc.user_id '; + $groupby .= 'uc.user_id, '; + $cfrom .= ' +LEFT JOIN {users} uc ON t.closed_by = uc.user_id '; + } + + if (array_get($args, 'due') || in_array('dueversion', $visible)) { + $select .= ' lvc.version_name AS closedby_version_name, '; + $from .= ' +LEFT JOIN {list_version} lvc ON t.closedby_version = lvc.version_id '; + $groupby .= 'lvc.version_id, lvc.list_position, '; + } + + if (in_array('os', $visible)) { + $select .= ' los.os_name AS os_name, '; + $from .= ' +LEFT JOIN {list_os} los ON t.operating_system = los.os_id '; + $groupby .= 'los.os_id, '; + } + + if (in_array('attachments', $visible)) { + $select .= ' (SELECT COUNT(attc.attachment_id) FROM {attachments} attc WHERE attc.task_id = t.task_id) AS num_attachments, '; + } + + if (array_get($args, 'has_attachment')) { + $where[] = 'EXISTS (SELECT 1 FROM {attachments} att WHERE t.task_id = att.task_id)'; + } + # 20150213 currently without recursive subtasks! + if (in_array('effort', $visible)) { + $select .= ' (SELECT SUM(ef.effort) FROM {effort} ef WHERE t.task_id = ef.task_id) AS effort, '; + } + + if (array_get($args, 'dev') || in_array('assignedto', $visible)) { + # not every db system has this feature out of box + if($conf['database']['dbtype']=='mysqli' || $conf['database']['dbtype']=='mysql'){ + $select .= ' GROUP_CONCAT(DISTINCT u.user_name ORDER BY u.user_id) AS assigned_to_name, '; + $select .= ' GROUP_CONCAT(DISTINCT u.user_id ORDER BY u.user_id) AS assignedids, '; + $select .= ' GROUP_CONCAT(DISTINCT u.profile_image ORDER BY u.user_id) AS assigned_image, '; + } elseif( $conf['database']['dbtype']=='pgsql'){ + $select .= " array_to_string(array_agg(u.user_name ORDER BY u.user_id), ',') AS assigned_to_name, "; + $select .= " array_to_string(array_agg(CAST(u.user_id as text) ORDER BY u.user_id), ',') AS assignedids, "; + $select .= " array_to_string(array_agg(u.profile_image ORDER BY u.user_id), ',') AS assigned_image, "; + } else{ + $select .= ' MIN(u.user_name) AS assigned_to_name, '; + $select .= ' (SELECT COUNT(assc.user_id) FROM {assigned} assc WHERE assc.task_id = t.task_id) AS num_assigned, '; + } + // assigned table is now always included in join + $from .= ' +LEFT JOIN {users} u ON ass.user_id = u.user_id '; + $groupby .= 'ass.task_id, '; + if (array_get($args, 'dev')) { + $cfrom .= ' +LEFT JOIN {users} u ON ass.user_id = u.user_id '; + $cgroupbyarr[] = 't.task_id'; + $cgroupbyarr[] = 'ass.task_id'; + } + } + + # not every db system has this feature out of box, it is not standard sql + if($conf['database']['dbtype']=='mysqli' || $conf['database']['dbtype']=='mysql'){ + #$select .= ' GROUP_CONCAT(DISTINCT tg.tag_name ORDER BY tg.list_position) AS tags, '; + $select .= ' GROUP_CONCAT(DISTINCT tg.tag_id ORDER BY tg.list_position) AS tagids, '; + #$select .= ' GROUP_CONCAT(DISTINCT tg.class ORDER BY tg.list_position) AS tagclass, '; + } elseif($conf['database']['dbtype']=='pgsql'){ + #$select .= " array_to_string(array_agg(tg.tag_name ORDER BY tg.list_position), ',') AS tags, "; + $select .= " array_to_string(array_agg(CAST(tg.tag_id as text) ORDER BY tg.list_position), ',') AS tagids, "; + #$select .= " array_to_string(array_agg(tg.class ORDER BY tg.list_position), ',') AS tagclass, "; + } else{ + # unsupported groupconcat or we just do not know how write it for the other databasetypes in this section + #$select .= ' MIN(tg.tag_name) AS tags, '; + #$select .= ' (SELECT COUNT(tt.tag_id) FROM {task_tag} tt WHERE tt.task_id = t.task_id) AS tagnum, '; + $select .= ' MIN(tg.tag_id) AS tagids, '; + #$select .= " '' AS tagclass, "; + } + // task_tag join table is now always included in join + $from .= ' +LEFT JOIN {list_tag} tg ON tt.tag_id = tg.tag_id '; + $groupby .= 'tt.task_id, '; + $cfrom .= ' +LEFT JOIN {list_tag} tg ON tt.tag_id = tg.tag_id '; + $cgroupbyarr[] = 't.task_id'; + $cgroupbyarr[] = 'tt.task_id'; + + + # use preparsed task description cache for dokuwiki when possible + if($conf['general']['syntax_plugin']=='dokuwiki' && FLYSPRAY_USE_CACHE==true){ + $select.=' MIN(cache.content) desccache, '; + $from.=' +LEFT JOIN {cache} cache ON t.task_id=cache.topic AND cache.type=\'task\' '; + } else { + $select .= 'NULL AS desccache, '; + } + + if (array_get($args, 'only_primary')) { + $where[] = 'NOT EXISTS (SELECT 1 FROM {dependencies} dep WHERE dep.dep_task_id = t.task_id)'; + } + + # feature FS#1600 + if (array_get($args, 'only_blocker')) { + $where[] = 'EXISTS (SELECT 1 FROM {dependencies} dep WHERE dep.dep_task_id = t.task_id)'; + } + + if (array_get($args, 'only_blocked')) { + $where[] = 'EXISTS (SELECT 1 FROM {dependencies} dep WHERE dep.task_id = t.task_id)'; + } + + # feature FS#1599 + if (array_get($args, 'only_unblocked')) { + $where[] = 'NOT EXISTS (SELECT 1 FROM {dependencies} dep WHERE dep.task_id = t.task_id)'; + } + + if (array_get($args, 'hide_subtasks')) { + $where[] = 't.supertask_id = 0'; + } + + if (array_get($args, 'only_watched')) { + $where[] = 'EXISTS (SELECT 1 FROM {notifications} fsn WHERE t.task_id = fsn.task_id AND fsn.user_id = ?)'; + $sql_params[] = $user->id; + } + + if ($proj->id) { + $where[] = 't.project_id = ?'; + $sql_params[] = $proj->id; + } else { + if (!$user->isAnon()) { // Anon-case handled later. + $allowed = array(); + foreach($fs->projects as $p) { + $allowed[] = $p['project_id']; + } + if(count($allowed)>0){ + $where[] = 't.project_id IN (' . implode(',', $allowed). ')'; + }else{ + $where[] = '0 = 1'; # always empty result + } + } + } + + // process users viewing rights, if not anonymous + if (!$user->isAnon()) { + $where[] = ' +( -- Begin block where users viewing rights are checked. + -- Case everyone can see all project tasks anyway and task not private + (t.mark_private = 0 AND p.others_view = 1) + OR + -- Case admin or project manager, can see any task, even private + (gpg.is_admin = 1 OR gpg.manage_project = 1 OR pg.is_admin = 1 OR pg.manage_project = 1) + OR + -- Case allowed to see all tasks, but not private + ((gpg.view_tasks = 1 OR pg.view_tasks = 1) AND t.mark_private = 0) + OR + -- Case allowed to see own tasks (automatically covers private tasks also for this user!) + ((gpg.view_own_tasks = 1 OR pg.view_own_tasks = 1) AND (t.opened_by = ? OR ass.user_id = ?)) + OR + -- Case task is private, but user either opened it or is an assignee + (t.mark_private = 1 AND (t.opened_by = ? OR ass.user_id = ?)) + OR + -- Leave groups tasks as the last one to check. They are the only ones that actually need doing a subquery + -- for checking viewing rights. There\'s a chance that a previous check already matched and the subquery is + -- not executed at all. All this of course depending on how the database query optimizer actually chooses + -- to fetch the results and execute this query... At least it has been given the hint. + + -- Case allowed to see groups tasks, all projects (NOTE: both global and project specific groups accepted here) + -- Strange... do not use OR here with user_id in EXISTS clause, seems to prevent using index with both mysql and + -- postgresql, query times go up a lot. So it\'ll be 2 different EXISTS OR\'ed together. + (gpg.view_groups_tasks = 1 AND t.mark_private = 0 AND ( + EXISTS (SELECT 1 FROM {users_in_groups} WHERE (group_id = pg.group_id OR group_id = gpg.group_id) AND user_id = t.opened_by) + OR + EXISTS (SELECT 1 FROM {users_in_groups} WHERE (group_id = pg.group_id OR group_id = gpg.group_id) AND user_id = ass.user_id) + )) + OR + -- Case allowed to see groups tasks, current project. Only project group allowed here. + (pg.view_groups_tasks = 1 AND t.mark_private = 0 AND ( + EXISTS (SELECT 1 FROM {users_in_groups} WHERE group_id = pg.group_id AND user_id = t.opened_by) + OR + EXISTS (SELECT 1 FROM {users_in_groups} WHERE group_id = pg.group_id AND user_id = ass.user_id) + )) +) -- Rights have been checked +'; + $sql_params[] = $user->id; + $sql_params[] = $user->id; + $sql_params[] = $user->id; + $sql_params[] = $user->id; + } + /// process search-conditions {{{ + $submits = array('type' => 'task_type', 'sev' => 'task_severity', + 'due' => 'closedby_version', 'reported' => 'product_version', + 'cat' => 'product_category', 'status' => 'item_status', + 'percent' => 'percent_complete', 'pri' => 'task_priority', + 'dev' => array('ass.user_id', 'u.user_name', 'u.real_name'), + 'opened' => array('opened_by', 'uo.user_name', 'uo.real_name'), + 'closed' => array('closed_by', 'uc.user_name', 'uc.real_name')); + foreach ($submits as $key => $db_key) { + $type = array_get($args, $key, ($key == 'status') ? 'open' : ''); + settype($type, 'array'); + + if (in_array('', $type)) { + continue; + } + + $temp = ''; + $condition = ''; + foreach ($type as $val) { + // add conditions for the status selection + if ($key == 'status' && $val == 'closed' && !in_array('open', $type)) { + $temp .= ' is_closed = 1 AND'; + } elseif ($key == 'status' && !in_array('closed', $type)) { + $temp .= ' is_closed = 0 AND'; + } + if (is_numeric($val) && !is_array($db_key) && !($key == 'status' && $val == 'closed')) { + $temp .= ' ' . $db_key . ' = ? OR'; + $sql_params[] = $val; + } elseif (is_array($db_key)) { + if ($key == 'dev' && ($val == 'notassigned' || $val == '0' || $val == '-1')) { + $temp .= ' ass.user_id is NULL OR'; + } else { + foreach ($db_key as $singleDBKey) { + if(ctype_digit($val) && strpos($singleDBKey, '_name') === false) { + $temp .= ' ' . $singleDBKey . ' = ? OR'; + $sql_params[] = $val; + } elseif (!ctype_digit($val) && strpos($singleDBKey, '_name') !== false) { + $temp .= ' ' . $singleDBKey . " $LIKEOP ? OR"; + $sql_params[] = '%' . $val . '%'; + } + } + } + } + + // Add the subcategories to the query + if ($key == 'cat') { + $result = $db->query('SELECT * + FROM {list_category} + WHERE category_id = ?', array($val)); + $cat_details = $db->fetchRow($result); + + $result = $db->query('SELECT * + FROM {list_category} + WHERE lft > ? AND rgt < ? AND project_id = ?', array($cat_details['lft'], $cat_details['rgt'], $cat_details['project_id'])); + while ($row = $db->fetchRow($result)) { + $temp .= ' product_category = ? OR'; + $sql_params[] = $row['category_id']; + } + } + } + + if ($temp) { + $where[] = '(' . substr($temp, 0, -3) . ')'; # strip last ' OR' and 'AND' + } + } +/// }}} + + $order_keys = array( + 'id' => 't.task_id', + 'project' => 'project_title', + 'tasktype' => 'tasktype_name', + 'dateopened' => 'date_opened', + 'summary' => 'item_summary', + 'severity' => 'task_severity', + 'category' => 'lc.category_name', + 'status' => 'is_closed, item_status', + 'dueversion' => 'lvc.list_position', + 'duedate' => 'due_date', + 'progress' => 'percent_complete', + 'lastedit' => 'max_date', + 'priority' => 'task_priority', + 'openedby' => 'uo.real_name', + 'reportedin' => 't.product_version', + 'assignedto' => 'u.real_name', + 'dateclosed' => 't.date_closed', + 'os' => 'los.os_name', + 'votes' => 'num_votes', + 'attachments' => 'num_attachments', + 'comments' => 'num_comments', + 'private' => 'mark_private', + 'supertask' => 't.supertask_id', + ); + + // make sure that only columns can be sorted that are visible (and task severity, since it is always loaded) + $order_keys = array_intersect_key($order_keys, array_merge(array_flip($visible), array('severity' => 'task_severity'))); + + // Implementing setting "Default order by" + if (!array_key_exists('order', $args)) { + # now also for $proj->id=0 (allprojects) + $orderBy = $proj->prefs['sorting'][0]['field']; + $sort = $proj->prefs['sorting'][0]['dir']; + if (count($proj->prefs['sorting']) >1){ + $orderBy2 =$proj->prefs['sorting'][1]['field']; + $sort2= $proj->prefs['sorting'][1]['dir']; + } else{ + $orderBy2='severity'; + $sort2='DESC'; + } + } else { + $orderBy = $args['order']; + $sort = $args['sort']; + $orderBy2='severity'; + $sort2='desc'; + } + + // TODO: Fix this! If something is already ordered by task_id, there's + // absolutely no use to even try to order by something else also. + $order_column[0] = $order_keys[Filters::enum(array_get($args, 'order', $orderBy), array_keys($order_keys))]; + $order_column[1] = $order_keys[Filters::enum(array_get($args, 'order2', $orderBy2), array_keys($order_keys))]; + $sortorder = sprintf('%s %s, %s %s, t.task_id ASC', + $order_column[0], + Filters::enum(array_get($args, 'sort', $sort), array('asc', 'desc')), + $order_column[1], + Filters::enum(array_get($args, 'sort2', $sort2), array('asc', 'desc')) + ); + + $having = array(); + $dates = array('duedate' => 'due_date', 'changed' => $maxdatesql, + 'opened' => 'date_opened', 'closed' => 'date_closed'); + foreach ($dates as $post => $db_key) { + $var = ($post == 'changed') ? 'having' : 'where'; + if ($date = array_get($args, $post . 'from')) { + ${$var}[] = '(' . $db_key . ' >= ' . Flyspray::strtotime($date) . ')'; + } + if ($date = array_get($args, $post . 'to')) { + ${$var}[] = '(' . $db_key . ' <= ' . Flyspray::strtotime($date) . ' AND ' . $db_key . ' > 0)'; + } + } + + if (array_get($args, 'string')) { + $words = explode(' ', strtr(array_get($args, 'string'), '()', ' ')); + $comments = ''; + $where_temp = array(); + + if (array_get($args, 'search_in_comments')) { + $comments .= " OR c.comment_text $LIKEOP ?"; + } + if (array_get($args, 'search_in_details')) { + $comments .= " OR t.detailed_desc $LIKEOP ?"; + } + + foreach ($words as $word) { + $word=trim($word); + if($word==''){ + continue; + } + $likeWord = '%' . str_replace('+', ' ', $word) . '%'; + $where_temp[] = "(t.item_summary $LIKEOP ? OR t.task_id = ? $comments)"; + array_push($sql_params, $likeWord, intval($word)); + if (array_get($args, 'search_in_comments')) { + array_push($sql_params, $likeWord); + } + if (array_get($args, 'search_in_details')) { + array_push($sql_params, $likeWord); + } + } + + if(count($where_temp)>0){ + $where[] = '(' . implode((array_get($args, 'search_for_all') ? ' AND ' : ' OR '), $where_temp) . ')'; + } + } + + if ($user->isAnon()) { + $where[] = 't.mark_private = 0 AND p.others_view = 1'; + if(array_key_exists('status', $args)){ + if (in_array('closed', $args['status']) && !in_array('open', $args['status'])) { + $where[] = 't.is_closed = 1'; + } elseif (in_array('open', $args['status']) && !in_array('closed', $args['status'])) { + $where[] = 't.is_closed = 0'; + } + } + } + + $where = (count($where)) ? 'WHERE ' . join(' AND ', $where) : ''; + + // Get the column names of table tasks for the group by statement + if (!strcasecmp($conf['database']['dbtype'], 'pgsql')) { + $groupby .= "p.project_title, p.project_is_active, "; + // Remove this after checking old PostgreSQL docs. + // 1 column from task table should be enough, after + // already grouping by task_id, there's no possibility + // to have anything more in that table to group by. + $groupby .= $db->getColumnNames('{tasks}', 't.task_id', 't.'); + } else { + $groupby = 't.task_id'; + } + + $having = (count($having)) ? 'HAVING ' . join(' AND ', $having) : ''; + + // echo '<pre>' . print_r($args, true) . '</pre>'; + // echo '<pre>' . print_r($cgroupbyarr, true) . '</pre>'; + $cgroupby = count($cgroupbyarr) ? 'GROUP BY ' . implode(',', array_unique($cgroupbyarr)) : ''; + + $sqlcount = "SELECT COUNT(*) FROM (SELECT 1, t.task_id, t.date_opened, t.date_closed, t.last_edited_time + FROM $cfrom + $where + $cgroupby + $having) s"; + $sqltext = "SELECT t.*, $select +p.project_title, p.project_is_active +FROM $from +$where +GROUP BY $groupby +$having +ORDER BY $sortorder"; + + // Very effective alternative with a little bit more work + // and if row_number() can be emulated in mysql. Idea: + // Move every join and other operation not needed in + // the inner clause to select rows to the outer query, + // and do the rest when we already know which rows + // are in the window to show. Got it to run constantly + // under 6000 ms. + /* Leave this for next version, don't have enough time for testing. + $sqlexperiment = "SELECT * FROM ( +SELECT row_number() OVER(ORDER BY task_id) AS rownum, +t.*, $select p.project_title, p.project_is_active FROM $from +$where +GROUP BY $groupby +$having +ORDER BY $sortorder +) +t WHERE rownum BETWEEN $offset AND " . ($offset + $perpage); +*/ + +// echo '<pre>'.print_r($sql_params, true).'</pre>'; # for debugging +// echo '<pre>'.$sqlcount.'</pre>'; # for debugging +// echo '<pre>'.$sqltext.'</pre>'; # for debugging + $sql = $db->query($sqlcount, $sql_params); + $totalcount = $db->fetchOne($sql); + +# 20150313 peterdd: Do not override task_type with tasktype_name until we changed t.task_type to t.task_type_id! We need the id too. + + $sql = $db->query($sqltext, $sql_params, $perpage, $offset); + // $sql = $db->query($sqlexperiment, $sql_params); + $tasks = $db->fetchAllArray($sql); + $id_list = array(); + $limit = array_get($args, 'limit', -1); + $forbidden_tasks_count = 0; + foreach ($tasks as $key => $task) { + $id_list[] = $task['task_id']; + if (!$user->can_view_task($task)) { + unset($tasks[$key]); + $forbidden_tasks_count++; + } + } + +// Work on this is not finished until $forbidden_tasks_count is always zero. +// echo "<pre>$offset : $perpage : $totalcount : $forbidden_tasks_count</pre>"; + return array($tasks, $id_list, $totalcount, $forbidden_tasks_count); +// # end alternative + } + +# end get_task_list +} # end class |