<?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