summaryrefslogtreecommitdiff
path: root/includes/class.backend.php
diff options
context:
space:
mode:
Diffstat (limited to 'includes/class.backend.php')
-rw-r--r--includes/class.backend.php1869
1 files changed, 1869 insertions, 0 deletions
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
generated by cgit v1.2.3-70-g09d2 (git 2.47.0) at 2024-11-27 19:03:15 +0000