Exemple #1
0
 function show()
 {
     global $page, $db, $user, $fs, $proj;
     $page->setTitle($fs->prefs['page_title'] . L('reports'));
     $events = array(1 => L('taskopened'), 13 => L('taskreopened'), 2 => L('taskclosed'), 3 => L('taskedited'), 14 => L('assignmentchanged'), 29 => L('events.useraddedtoassignees'), 4 => L('commentadded'), 5 => L('commentedited'), 6 => L('commentdeleted'), 7 => L('attachmentadded'), 8 => L('attachmentdeleted'), 11 => L('relatedadded'), 12 => L('relateddeleted'), 9 => L('notificationadded'), 10 => L('notificationdeleted'), 17 => L('reminderadded'), 18 => L('reminderdeleted'));
     $user_events = array(30 => L('created'), 31 => L('deleted'));
     $page->assign('events', $events);
     $page->assign('user_events', $user_events);
     $sort = strtoupper(Get::enum('sort', array('desc', 'asc')));
     $where = array();
     $params = array();
     $orderby = '';
     switch (Get::val('order')) {
         case 'type':
             $orderby = "h.event_type {$sort}, h.event_date {$sort}";
             break;
         case 'user':
             $orderby = "user_id {$sort}, h.event_date {$sort}";
             break;
         case 'date':
         default:
             $orderby = "h.event_date {$sort}, h.event_type {$sort}";
     }
     foreach (Get::val('events', array()) as $eventtype) {
         $where[] = 'h.event_type = ?';
         $params[] = $eventtype;
     }
     $where = '(' . implode(' OR ', $where) . ')';
     if ($proj->id) {
         $where = $where . 'AND (t.project_id = ?  OR h.event_type > 29) ';
         $params[] = $proj->id;
     }
     if (($fromdate = Req::val('fromdate')) || Req::val('todate')) {
         $where .= ' AND ';
         $todate = Req::val('todate');
         if ($fromdate) {
             $where .= ' h.event_date > ?';
             $params[] = Flyspray::strtotime($fromdate) + 0;
         }
         if ($todate && $fromdate) {
             $where .= ' AND h.event_date < ?';
             $params[] = Flyspray::strtotime($todate) + 86400;
         } else {
             if ($todate) {
                 $where .= ' h.event_date < ?';
                 $params[] = Flyspray::strtotime($todate) + 86400;
             }
         }
     }
     $histories = array();
     if (count(Get::val('events'))) {
         if (Get::num('event_number') > 0) {
             $db->setLimit(Get::num('event_number'));
         }
         $histories = $db->x->getAll("SELECT h.*, t.*, p.project_prefix\n                                             FROM {history} h\n                                        LEFT JOIN {tasks} t ON h.task_id = t.task_id\n                                        LEFT JOIN {projects} p ON t.project_id = p.project_id\n                                            WHERE {$where}\n                                         ORDER BY {$orderby}", null, $params);
     }
     $page->assign('histories', $histories);
     $page->assign('sort', $sort);
     $page->pushTpl('reports.tpl');
 }
    /**
     * 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((SELECT max(c.date_added) FROM {comments} c WHERE c.task_id = t.task_id), 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((SELECT max(c.date_added) FROM {comments} c WHERE c.task_id = t.task_id), 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 ('mysql' == $db->dblink->dataProvider) {
                #$select .= ' GROUP_CONCAT(u.real_name) AS assigned_to_name, ';
                # without distinct i see multiple times each assignee
                # maybe performance penalty due distinct?, solve by better groupby construction?
                $select .= ' GROUP_CONCAT(DISTINCT u.real_name) AS assigned_to_name, ';
                # maybe later for building links to users
                #$select .= ' GROUP_CONCAT(DISTINCT u.real_name ORDER BY u.user_id) AS assigned_to_name, ';
                #$select .= ' GROUP_CONCAT(DISTINCT u.user_id ORDER BY u.user_id) AS assignedids, ';
            } else {
                $select .= ' MIN(u.real_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
        if ('mysql' == $db->dblink->dataProvider) {
            # without distinct i see multiple times each tag (when task has several assignees too)
            $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, ';
        } else {
            # FIXME: GROUP_CONCAT() for postgresql?
            $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 .= ' 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 (strpos($singleDBKey, '_name') !== false) {
                                $temp .= ' ' . $singleDBKey . " {$LIKEOP} ? OR";
                                $sql_params[] = '%' . $val . '%';
                            } elseif (is_numeric($val)) {
                                $temp .= ' ' . $singleDBKey . ' = ? 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) . ')';
            }
        }
        /// }}}
        $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) {
                $likeWord = '%' . str_replace('+', ' ', trim($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);
                }
            }
            $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\n                           FROM     {$cfrom}\n                           {$where}\n                           {$cgroupby}\n                           {$having}) s";
        $sqltext = "SELECT t.*, {$select}\np.project_title, p.project_is_active\nFROM {$from}\n{$where}\nGROUP BY {$groupby}\n{$having}\nORDER 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
    }
Exemple #3
0
     array_push($values, Post::val('bulk_reportedver'));
 }
 if (!Post::val('bulk_due_version') == 0) {
     array_push($columns, 'closedby_version');
     array_push($values, Post::val('bulk_due_version'));
 }
 # TODO Does the user has similiar rights in current and target projects?
 # TODO Does a task has subtasks? What happens to them? What if they are open/closed?
 # But: Allowing task dependencies between tasks in different projects is a feature!
 if (!Post::val('bulk_projects') == 0) {
     array_push($columns, 'project_id');
     array_push($values, Post::val('bulk_projects'));
 }
 if (!is_null(Post::val('bulk_due_date'))) {
     array_push($columns, 'due_date');
     array_push($values, Flyspray::strtotime(Post::val('bulk_due_date')));
 }
 //only process if one of the task fields has been updated.
 if (!array_count_values($columns) == 0 && Post::val('ids')) {
     //add the selected task id's to the query string
     $task_ids = Post::val('ids');
     $valuesAndTasks = array_merge_recursive($values, $task_ids);
     //execute the database update on all selected queries
     $update = $db->Query("UPDATE  {tasks}\n                                     SET  " . join('=?, ', $columns) . "=?\n                                   WHERE" . substr(str_repeat(' task_id = ? OR ', count(Post::val('ids'))), 0, -3), $valuesAndTasks);
 }
 //Set the assignments
 if (Post::val('bulk_assignment')) {
     // Delete the current assignees for the selected tasks
     $db->Query("DELETE FROM {assigned} WHERE" . substr(str_repeat(' task_id = ? OR ', count(Post::val('ids'))), 0, -3), Post::val('ids'));
     // Convert assigned_to and store them in the 'assigned' table
     foreach ((array) Post::val('ids') as $id) {
Exemple #4
0
 /**
  * Returns an array of tasks (respecting pagination) and an ID list (all tasks)
  * @param array $args call by reference because we have to modifiy $_GET if we use default values from a user profile
  * @param array $visible
  * @param integer $offset
  * @param integer $comment
  * @param bool $perpage
  * @access public
  * @return array
  * @version 1.0
  */
 function get_task_list(&$args, $visible, $offset = 0, $perpage = null)
 {
     global $proj, $db, $user, $conf, $fs;
     /* build SQL statement {{{ */
     // Original SQL courtesy of Lance Conry http://www.rhinosw.com/
     $where = $sql_params = array();
     $select = '';
     $groupby = 't.task_id, ';
     $from = '             {tasks}         t
                  LEFT JOIN  {projects}      p   ON t.project_id = p.project_id
                  LEFT JOIN  {list_items} lr ON t.resolution_reason = lr.list_item_id
                  LEFT JOIN  {redundant} r ON t.task_id = r.task_id ';
     // Only join tables which are really necessary to speed up the db-query
     $from .= ' LEFT JOIN  {assigned} ass      ON t.task_id = ass.task_id ';
     $from .= ' LEFT JOIN  {users} u           ON ass.user_id = u.user_id ';
     if (array_get($args, 'dev') || in_array('assignedto', $visible)) {
         $select .= ' MIN(u.real_name)               AS assigned_to_name, ';
         $select .= ' COUNT(ass.user_id)    AS num_assigned, ';
     }
     if (array_get($args, 'only_primary')) {
         $from .= ' LEFT JOIN  {dependencies} dep  ON dep.dep_task_id = t.task_id ';
         $where[] = 'dep.depend_id IS null';
     }
     if (array_get($args, 'has_attachment')) {
         $where[] = 'attachment_count > 0';
     }
     // sortable default fields
     $order_keys = array('id' => 't.task_id %s', 'project' => 'project_title %s', 'dateopened' => 'date_opened %s', 'summary' => 'item_summary %s', 'progress' => 'percent_complete %s', 'lastedit' => 'last_changed_time %s', 'openedby' => 'r.opened_by_real_name %s', 'closedby' => 'r.closed_by_real_name %s', 'changedby' => 'r.last_changed_by_real_name %s', 'assignedto' => 'u.real_name %s', 'dateclosed' => 't.date_closed %s', 'votes' => 'vote_count %s', 'attachments' => 'attachment_count %s', 'comments' => 'comment_count %s', 'state' => 'closed_by %1$s, is_closed %1$s', 'projectlevelid' => 'prefix_id %s', 'private' => 'mark_private %s');
     // custom sortable fields
     foreach ($proj->fields as $field) {
         if ($field->prefs['list_type'] == LIST_CATEGORY) {
             // consider hierarchical structure of categories
             $order_keys['field' . $field->id] = 'lcfield' . $field->id . '.lft %1$s, field' . $field->id . ' %1$s';
         } else {
             $order_keys['field' . $field->id] = 'field' . $field->id . ' %s';
         }
     }
     // Default user sort column and order
     if (!$user->isAnon()) {
         if (!isset($args['sort'])) {
             $args['sort'] = $user->infos['defaultorder'];
         }
         if (!isset($args['order'])) {
             $usercolumns = explode(' ', $user->infos['defaultsortcolumn']);
             foreach ($usercolumns as $column) {
                 if (isset($order_keys[$column])) {
                     $args['order'] = $column;
                     break;
                 }
             }
         }
     }
     // make sure that only columns can be sorted that are visible
     $order_keys = array_intersect_key($order_keys, array_flip($visible));
     $order_column[0] = $order_keys[Filters::enum(array_get($args, 'order', 'id'), array_keys($order_keys))];
     $order_column[1] = $order_keys[Filters::enum(array_get($args, 'order2', 'project'), array_keys($order_keys))];
     $order_column[0] = sprintf($order_column[0], strtoupper(Filters::enum(array_get($args, 'sort', 'desc'), array('asc', 'desc'))));
     $order_column[1] = sprintf($order_column[1], strtoupper(Filters::enum(array_get($args, 'sort2', 'desc'), array('asc', 'desc'))));
     $sortorder = sprintf('%s, %s, t.task_id ASC', $order_column[0], $order_column[1]);
     // search custom fields
     $custom_fields_joined = array();
     foreach ($proj->fields as $field) {
         $ref = 'field' . $field->id;
         if ($field->prefs['field_type'] == FIELD_DATE) {
             if (!array_get($args, 'field' . $field->id . 'from') && !array_get($args, 'field' . $field->id . 'to')) {
                 continue;
             }
             $from .= " LEFT JOIN {field_values} {$ref} ON t.task_id = {$ref}.task_id AND {$ref}.field_id = {$field->id} ";
             $custom_fields_joined[] = $field->id;
             if ($date = array_get($args, 'field' . $field->id . 'from')) {
                 $where[] = "({$ref}.field_value >= ?)";
                 $sql_params[] = Flyspray::strtotime($date);
             }
             if ($date = array_get($args, 'field' . $field->id . 'to')) {
                 $where[] = "({$ref}.field_value <= ? AND {$ref}.field_value > 0)";
                 $sql_params[] = Flyspray::strtotime($date);
             }
         } elseif ($field->prefs['field_type'] == FIELD_LIST) {
             if (in_array('', (array) array_get($args, 'field' . $field->id, array('')))) {
                 continue;
             }
             $from .= " LEFT JOIN {field_values} {$ref} ON t.task_id = {$ref}.task_id AND {$ref}.field_id = {$field->id} ";
             $custom_fields_joined[] = $field->id;
             $fwhere = array();
             foreach ($args['field' . $field->id] as $val) {
                 $fwhere[] = " {$ref}.field_value = ? ";
                 $sql_params[] = $val;
             }
             if (count($fwhere)) {
                 $where[] = ' (' . implode(' OR ', $fwhere) . ') ';
             }
         } else {
             if (!($val = array_get($args, 'field' . $field->id))) {
                 continue;
             }
             $from .= " LEFT JOIN {field_values} {$ref} ON t.task_id = {$ref}.task_id AND {$ref}.field_id = {$field->id} ";
             $custom_fields_joined[] = $field->id;
             $where[] = "({$ref}.field_value LIKE ?)";
             // try to determine a valid user ID if necessary
             if ($field->prefs['field_type'] == FIELD_USER) {
                 $val = Flyspray::UserNameOrId($val);
             }
             $sql_params[] = $val;
         }
     }
     // now join custom fields used in columns
     foreach ($proj->columns as $col => $name) {
         if (preg_match('/^field(\\d+)$/', $col, $match) && (in_array($col, $visible) || $match[1] == $fs->prefs['color_field'])) {
             if (!in_array($match[1], $custom_fields_joined)) {
                 $from .= " LEFT JOIN {field_values} {$col} ON t.task_id = {$col}.task_id AND {$col}.field_id = " . intval($match[1]);
             }
             $from .= " LEFT JOIN {fields} f{$col} ON f{$col}.field_id = {$col}.field_id ";
             // join special tables for certain fields
             if ($proj->fields['field' . $match[1]]->prefs['field_type'] == FIELD_LIST) {
                 $from .= "LEFT JOIN {list_items} li{$col} ON (f{$col}.list_id = li{$col}.list_id AND {$col}.field_value = li{$col}.list_item_id)\n                              LEFT JOIN {list_category} lc{$col} ON (f{$col}.list_id = lc{$col}.list_id AND {$col}.field_value = lc{$col}.category_id) ";
                 if ($proj->fields['field' . $match[1]]->prefs['list_type'] != LIST_CATEGORY) {
                     $select .= " li{$col}.item_name AS {$col}_name, ";
                 } else {
                     $select .= " lc{$col}.category_name AS {$col}_name, ";
                 }
             } else {
                 if ($proj->fields['field' . $match[1]]->prefs['field_type'] == FIELD_USER) {
                     $from .= " LEFT JOIN {users} u{$col} ON {$col}.field_value = u{$col}.user_id ";
                     $select .= " u{$col}.user_name AS {$col}_name, ";
                 }
             }
             $select .= "{$col}.field_value AS {$col}, ";
             // adding data to queries not nice, but otherwise sql_params and joins are not in sync
         }
     }
     // open / closed (never thought that I'd use XOR some time)
     if (in_array('open', array_get($args, 'status', array('open'))) xor in_array('closed', array_get($args, 'status', array()))) {
         $where[] = ' is_closed = ? ';
         $sql_params[] = (int) in_array('closed', array_get($args, 'status', array()));
     }
     /// process search-conditions {{{
     $submits = array('percent' => 'percent_complete', 'dev' => array('a.user_id', 'us.user_name'), 'opened' => array('opened_by', 'r.opened_by_user_name'), 'closed' => array('closed_by', 'r.closed_by_user_name'));
     // add custom user fields
     foreach ($submits as $key => $db_key) {
         $type = array_get($args, $key, '');
         settype($type, 'array');
         if (in_array('', $type)) {
             continue;
         }
         if ($key == 'dev') {
             $from .= 'LEFT JOIN {assigned} a  ON t.task_id = a.task_id ';
             $from .= 'LEFT JOIN {users} us  ON a.user_id = us.user_id ';
         }
         $temp = '';
         $condition = '';
         foreach ($type as $val) {
             if (is_numeric($val) && !is_array($db_key)) {
                 $temp .= ' ' . $db_key . ' = ?  OR';
                 $sql_params[] = $val;
             } elseif (is_array($db_key)) {
                 if ($key == 'dev' && ($val == 'notassigned' || $val == '0' || $val == '-1')) {
                     $temp .= ' a.user_id IS NULL  OR';
                 } else {
                     if (is_numeric($val)) {
                         $condition = ' = ? OR';
                     } else {
                         $val = '%' . $val . '%';
                         $condition = ' LIKE ? OR';
                     }
                     foreach ($db_key as $value) {
                         $temp .= ' ' . $value . $condition;
                         $sql_params[] = $val;
                     }
                 }
             }
         }
         if ($temp) {
             $where[] = '(' . substr($temp, 0, -3) . ')';
         }
     }
     /// }}}
     $having = array();
     $dates = array('due_date', 'changed' => 'r.last_changed_time', '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')) {
             $from .= 'LEFT JOIN {comments} c  ON t.task_id = c.task_id ';
             $comments .= ' OR c.comment_text LIKE ? ';
         }
         if (array_get($args, 'search_in_details')) {
             $comments .= 'OR t.detailed_desc LIKE ? ';
         }
         foreach ($words as $word) {
             $word = '%' . str_replace('+', ' ', trim($word)) . '%';
             $where_temp[] = "(t.item_summary LIKE ? OR t.task_id LIKE ? {$comments})";
             array_push($sql_params, $word, $word);
             if (array_get($args, 'search_in_comments')) {
                 array_push($sql_params, $word);
             }
             if (array_get($args, 'search_in_details')) {
                 array_push($sql_params, $word);
             }
         }
         $where[] = '(' . implode(array_get($args, 'search_for_all') ? ' AND ' : ' OR ', $where_temp) . ')';
     }
     if (array_get($args, 'only_watched')) {
         //join the notification table to get watched tasks
         $from .= ' LEFT JOIN {notifications} fsn ON t.task_id = fsn.task_id';
         $where[] = 'fsn.user_id = ?';
         $sql_params[] = $user->id;
     }
     if ($proj->id) {
         $where[] = 't.project_id = ?';
         $sql_params[] = $proj->id;
     } else {
         $tmpwhere = array();
         foreach (array_get($args, 'search_project', array()) as $id) {
             if ($id) {
                 $tmpwhere[] = 't.project_id = ?';
                 $sql_params[] = $id;
             }
         }
         if (count($tmpwhere)) {
             $where[] = '(' . implode(' OR ', $tmpwhere) . ')';
         }
     }
     $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')) {
         $order_column[0] = substr($order_column[0], 0, -4);
         $order_column[1] = substr($order_column[1], 0, -4);
         $groupby .= "p.project_title, p.project_prefix, {$order_column[0]},{$order_column[1]}, lr.item_name, ";
         $groupby .= GetColumnNames('{tasks}', 't.task_id', 't');
     } else {
         $groupby = 't.task_id';
     }
     $having = count($having) ? 'HAVING ' . join(' AND ', $having) : '';
     $tasks = $db->x->getAll("\n                          SELECT   t.*, r.*, {$select}\n                                   p.project_title, p.project_prefix,\n                                   lr.item_name AS resolution_name\n                          FROM     {$from}\n                          {$where}\n                          GROUP BY {$groupby}\n                          {$having}\n                          ORDER BY {$sortorder}", null, $sql_params);
     $id_list = array();
     $limit = array_get($args, 'limit', -1);
     $task_count = 0;
     foreach ($tasks as $key => $task) {
         $id_list[] = $task['task_id'];
         if (!$user->can_view_task($task)) {
             unset($tasks[$key]);
             array_pop($id_list);
             --$task_count;
         } elseif ($perpage && ($task_count < $offset || $task_count > $offset - 1 + $perpage || $limit > 0 && $task_count >= $limit)) {
             unset($tasks[$key]);
         }
         ++$task_count;
     }
     return array($tasks, $id_list);
 }
Exemple #5
0
# check field for update against allowed dbfields for quickedit.
# maybe FUTURE: add (dynamic read from database) allowed CUSTOM FIELDS checks for the project and user
# (if there is urgent request for implementing custom fields into Flyspray and using of tag-feature isn't enough to accomplish - like numbers/dates/timestamps as custom fields)
$allowedFields = array('due_date', 'item_status', 'percent_complete', 'task_type', 'product_category', 'task_severity', 'task_priority', 'product_version', 'closedby_version');
if ($proj->prefs['use_effort_tracking'] && $user->perms('track_effort')) {
    $allowedFields[] = 'estimated_effort';
}
if (!in_array(Post::val('name'), $allowedFields)) {
    header(':', true, 403);
    die(L('invalidfield'));
}
$value = Post::val('value');
# check if user is not sending manipulated invalid values
switch (Post::val('name')) {
    case 'due_date':
        $value = Flyspray::strtotime(Post::val('value'));
        $value = intval($value);
        break;
    case 'estimated_effort':
        $value = effort::EditStringToSeconds(Post::val('value'), $proj->prefs['hours_per_manday'], $proj->prefs['estimated_effort_format']);
        $value = intval($value);
        break;
    case 'task_priority':
    case 'task_severity':
        if (!preg_match("/^[1-5]\$/", $value)) {
            header(':', true, 403);
            die(L('invalidvalue'));
        }
        break;
    case 'percent_complete':
        if (!is_numeric($value) || $value < 0 || $value > 100) {
     while ($attachment = $db->FetchRow($check_attachments)) {
         $db->Query("DELETE from {attachments} WHERE attachment_id = ?", array($attachment['attachment_id']));
         @unlink(BASEDIR . '/attachments/' . $attachment['file_name']);
         Flyspray::logEvent($attachment['task_id'], 8, $attachment['orig_name']);
     }
     $_SESSION['SUCCESS'] = L('commentdeletedmsg');
     break;
     // ##################
     // adding a reminder
     // ##################
 // ##################
 // adding a reminder
 // ##################
 case 'details.addreminder':
     $how_often = Post::val('timeamount1', 1) * Post::val('timetype1');
     $start_time = Flyspray::strtotime(Post::val('timeamount2', 0));
     $userId = Flyspray::UsernameToId(Post::val('to_user_id'));
     if (!Backend::add_reminder($task['task_id'], Post::val('reminder_message'), $how_often, $start_time, $userId)) {
         Flyspray::show_error(L('usernotexist'));
         break;
     }
     $_SESSION['SUCCESS'] = L('reminderaddedmsg');
     break;
     // ##################
     // removing a reminder
     // ##################
 // ##################
 // removing a reminder
 // ##################
 case 'deletereminder':
     if (!$user->perms('manage_project') || !is_array(Post::val('reminder_id'))) {
Exemple #7
0
    $where[] = 'h.event_type = ?';
    $params[] = $eventtype;
}
$where = '(' . implode(' OR ', $where) . ')';
if ($proj->id) {
    $where = $where . 'AND (t.project_id = ?  OR h.event_type > 29) ';
    $params[] = $proj->id;
}
if (($fromdate = Req::val('fromdate')) || Req::val('todate')) {
    $where .= ' AND ';
    $todate = Req::val('todate');
    if ($fromdate) {
        $where .= ' h.event_date > ?';
        $params[] = Flyspray::strtotime($fromdate) + 0;
    }
    if ($todate && $fromdate) {
        $where .= ' AND h.event_date < ?';
        $params[] = Flyspray::strtotime($todate) + 86400;
    } else {
        if ($todate) {
            $where .= ' h.event_date < ?';
            $params[] = Flyspray::strtotime($todate) + 86400;
        }
    }
}
if (count(Req::val('events'))) {
    $histories = $db->Query("SELECT h.*\n                        FROM  {history} h\n                   LEFT JOIN {tasks} t ON h.task_id = t.task_id\n                        WHERE {$where}\n                     ORDER BY {$orderby}", $params, Req::num('event_number', -1));
    $histories = $db->FetchAllArray($histories);
}
$page->uses('histories', 'sort');
$page->pushTpl('reports.tpl');
Exemple #8
0
 function action_update_fields()
 {
     global $fs, $db, $proj, $user;
     $types = Post::val('field_type');
     $names = Post::val('field_name');
     $lists = Post::val('list_id');
     $tense = Post::val('version_tense');
     $delete = Post::val('delete');
     $force = Post::val('force_default');
     $required = Post::val('value_required');
     foreach (Post::val('id', array()) as $id) {
         if (isset($delete[$id])) {
             $num = $db->x->execParam('DELETE FROM {fields} WHERE field_id = ? AND project_id = ?', array($id, $proj->id));
             // sort of permission check (the query below does not check project_id)
             if ($num) {
                 $db->x->execParam('DELETE FROM {field_values} WHERE field_id = ?', $id);
             }
             continue;
         }
         $default[$id] = Post::val('field' . $id, 0);
         if ($types[$id]['field_type'] == FIELD_DATE && $default[$id]) {
             $default[$id] = Flyspray::strtotime($default[$id]);
         }
         $db->x->execParam('UPDATE {fields} SET field_name = ?, field_type = ?, list_id = ?, value_required = ?,
                                         version_tense = ?, default_value = ?, force_default = ?
                      WHERE field_id = ? AND project_id = ?', array($names[$id], $types[$id], array_get($lists, $id, null), array_get($required, $id, 0), array_get($tense, $id, 0), $default[$id], array_get($force, $id, 0), $id, $proj->id));
     }
     $proj = new Project($proj->id);
     return array(SUBMIT_OK, L('fieldsupdated'));
 }
Exemple #9
0
 /**
  * Returns a correct value for the field based on user input
  * @access public
  * @param string $input
  * @return string
  */
 function read($input)
 {
     global $user, $db;
     switch ($this->prefs['field_type']) {
         case FIELD_DATE:
             $value = $input ? Flyspray::strtotime($input) : '';
             // this would be a unix timestamp
             if (is_numeric($input)) {
                 $value = $input;
             }
             break;
         case FIELD_TEXT:
             $value = (string) $input;
             break;
         case FIELD_LIST:
             if ($this->prefs['list_type'] == LIST_CATEGORY) {
                 $check = $db->x->GetOne('SELECT count(*)
                                         FROM {list_category}
                                        WHERE list_id = ? AND category_id = ?', null, array($this->prefs['list_id'], $input));
             } else {
                 $check = $db->x->GetOne('SELECT count(*)
                                         FROM {list_items}
                                        WHERE list_id = ? AND list_item_id = ?', null, array($this->prefs['list_id'], $input));
             }
             $value = $check ? $input : 0;
             break;
         case FIELD_USER:
             // try to determine a valid user ID if necessary
             $value = Flyspray::UserNameOrId($input);
             break;
     }
     if (!$value || $this->prefs['force_default'] && !$user->perms('modify_all_tasks')) {
         $value = $this->prefs['default_value'];
     }
     return $value;
 }
 /**
  * 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 $proj, $db, $user, $conf;
     /* build SQL statement {{{ */
     // Original SQL courtesy of Lance Conry http://www.rhinosw.com/
     $where = $sql_params = array();
     $select = '';
     $groupby = 't.task_id, ';
     $from = '             {tasks}         t
                  LEFT JOIN  {projects}      p   ON t.project_id = p.project_id
                  LEFT JOIN  {list_tasktype} lt  ON t.task_type = lt.tasktype_id
                  LEFT JOIN  {list_status}   lst ON t.item_status = lst.status_id
                  LEFT JOIN  {list_resolution} lr ON t.resolution_reason = lr.resolution_id ';
     // Only join tables which are really necessary to speed up the db-query
     if (array_get($args, 'cat') || in_array('category', $visible)) {
         $from .= ' LEFT JOIN  {list_category} lc  ON t.product_category = lc.category_id ';
         $select .= ' lc.category_name               AS category_name, ';
         $groupby .= 'lc.category_name, ';
     }
     if (in_array('votes', $visible)) {
         $from .= ' LEFT JOIN  {votes} vot         ON t.task_id = vot.task_id ';
         $select .= ' COUNT(DISTINCT vot.vote_id)    AS num_votes, ';
     }
     $maxdatesql = ' GREATEST((SELECT max(c.date_added) FROM {comments} c WHERE c.task_id = t.task_id), 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((SELECT max(c.date_added) FROM {comments} c WHERE c.task_id = t.task_id), t.date_opened, t.date_closed, t.last_edited_time) AS max_date, ';
     }
     if (array_get($args, 'search_in_comments')) {
         $from .= ' LEFT JOIN  {comments} c          ON t.task_id = c.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)) {
         $from .= ' LEFT JOIN  {list_version} lv   ON t.product_version = lv.version_id ';
         $select .= ' lv.version_name                AS product_version, ';
         $groupby .= 'lv.version_name, ';
     }
     if (array_get($args, 'opened') || in_array('openedby', $visible)) {
         $from .= ' LEFT JOIN  {users} uo          ON t.opened_by = uo.user_id ';
         $select .= ' uo.real_name                   AS opened_by_name, ';
         $groupby .= 'uo.real_name, ';
     }
     if (array_get($args, 'closed')) {
         $from .= ' LEFT JOIN  {users} uc          ON t.closed_by = uc.user_id ';
         $select .= ' uc.real_name                   AS closed_by_name, ';
         $groupby .= 'uc.real_name, ';
     }
     if (array_get($args, 'due') || in_array('dueversion', $visible)) {
         $from .= ' LEFT JOIN  {list_version} lvc  ON t.closedby_version = lvc.version_id ';
         $select .= ' lvc.version_name               AS closedby_version, ';
         $groupby .= 'lvc.version_name, ';
     }
     if (in_array('os', $visible)) {
         $from .= ' LEFT JOIN  {list_os} los       ON t.operating_system = los.os_id ';
         $select .= ' los.os_name                    AS os_name, ';
         $groupby .= 'los.os_name, ';
     }
     if (in_array('attachments', $visible) || array_get($args, 'has_attachment')) {
         $from .= ' LEFT JOIN  {attachments} att   ON t.task_id = att.task_id ';
         $select .= ' COUNT(DISTINCT att.attachment_id) AS num_attachments, ';
     }
     $from .= ' LEFT JOIN  {assigned} ass      ON t.task_id = ass.task_id ';
     $from .= ' LEFT JOIN  {users} u           ON ass.user_id = u.user_id ';
     if (array_get($args, 'dev') || in_array('assignedto', $visible)) {
         $select .= ' MIN(u.real_name)               AS assigned_to_name, ';
         $select .= ' COUNT(DISTINCT ass.user_id)    AS num_assigned, ';
     }
     if (array_get($args, 'only_primary')) {
         $from .= ' LEFT JOIN  {dependencies} dep  ON dep.dep_task_id = t.task_id ';
         $where[] = 'dep.depend_id IS NULL';
     }
     if (array_get($args, 'has_attachment')) {
         $where[] = 'att.attachment_id IS NOT NULL';
     }
     if ($proj->id) {
         $where[] = 't.project_id = ?';
         $sql_params[] = $proj->id;
     }
     $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');
     // 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')));
     $order_column[0] = $order_keys[Filters::enum(array_get($args, 'order', 'severity'), array_keys($order_keys))];
     $order_column[1] = $order_keys[Filters::enum(array_get($args, 'order2', 'priority'), array_keys($order_keys))];
     $sortorder = sprintf('%s %s, %s %s, t.task_id ASC', $order_column[0], Filters::enum(array_get($args, 'sort', 'desc'), array('asc', 'desc')), $order_column[1], Filters::enum(array_get($args, 'sort2', 'desc'), array('asc', 'desc')));
     /// 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('a.user_id', 'us.user_name', 'us.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;
         }
         if ($key == 'dev') {
             setcookie('tasklist_type', 'assignedtome');
             $from .= 'LEFT JOIN {assigned} a  ON t.task_id = a.task_id ';
             $from .= 'LEFT JOIN {users} us  ON a.user_id = us.user_id ';
         } else {
             setcookie('tasklist_type', 'project');
         }
         $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 <> '1' 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 .= ' a.user_id is NULL  OR';
                 } else {
                     foreach ($db_key as $singleDBKey) {
                         if (strpos($singleDBKey, '_name') !== false) {
                             $temp .= ' ' . $singleDBKey . ' LIKE ? OR ';
                             $sql_params[] = '%' . $val . '%';
                         } elseif (is_numeric($val)) {
                             $temp .= ' ' . $singleDBKey . ' = ? 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) . ')';
         }
     }
     /// }}}
     $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 LIKE ?';
         }
         if (array_get($args, 'search_in_details')) {
             $comments .= 'OR t.detailed_desc LIKE ?';
         }
         foreach ($words as $word) {
             $likeWord = '%' . str_replace('+', ' ', trim($word)) . '%';
             $where_temp[] = "(t.item_summary LIKE ? 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);
             }
         }
         $where[] = '(' . implode(array_get($args, 'search_for_all') ? ' AND ' : ' OR ', $where_temp) . ')';
     }
     if (array_get($args, 'only_watched')) {
         //join the notification table to get watched tasks
         $from .= ' LEFT JOIN {notifications} fsn ON t.task_id = fsn.task_id';
         $where[] = 'fsn.user_id = ?';
         $sql_params[] = $user->id;
     }
     $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, lst.status_name, lt.tasktype_name,{$order_column[0]},{$order_column[1]}, lr.resolution_name, ";
         $groupby .= $db->GetColumnNames('{tasks}', 't.task_id', 't.');
     } else {
         $groupby = 't.task_id';
     }
     $having = count($having) ? 'HAVING ' . join(' AND ', $having) : '';
     $sql = $db->Query("\n                          SELECT   t.*, {$select}\n                                   p.project_title, p.project_is_active,\n                                   lst.status_name AS status_name,\n                                   lt.tasktype_name AS task_type,\n                                   lr.resolution_name\n                          FROM     {$from}\n                          {$where}\n                          GROUP BY {$groupby}\n                          {$having}\n                          ORDER BY {$sortorder}", $sql_params);
     $tasks = $db->fetchAllArray($sql);
     $id_list = array();
     $limit = array_get($args, 'limit', -1);
     $task_count = 0;
     foreach ($tasks as $key => $task) {
         $id_list[] = $task['task_id'];
         if (!$user->can_view_task($task)) {
             unset($tasks[$key]);
             array_pop($id_list);
             --$task_count;
         } elseif (!is_null($perpage) && ($task_count < $offset || $task_count > $offset - 1 + $perpage || $limit > 0 && $task_count >= $limit)) {
             unset($tasks[$key]);
         }
         ++$task_count;
     }
     return array($tasks, $id_list);
 }
Exemple #11
0
 function action_addreminder($task)
 {
     global $user, $db, $fs, $proj;
     $how_often = Post::val('timeamount1', 1) * Post::val('timetype1');
     $start_time = Flyspray::strtotime(Post::val('timeamount2', 0));
     $userId = Flyspray::UsernameToId(Post::val('to_user_id'));
     if (!Backend::add_reminder($task['task_id'], Post::val('reminder_message'), $how_often, $start_time, $userId)) {
         return array(ERROR_RECOVER, L('usernotexist'));
     }
     return array(SUBMIT_OK, L('reminderaddedmsg'));
 }