function Project($id) { global $db, $fs; // Get custom fields $fields = $db->x->getAll('SELECT f.*, l.list_type FROM {fields} f LEFT JOIN {lists} l ON f.list_id = l.list_id WHERE f.project_id IN (0, ?) ORDER BY field_name', null, array($id)); foreach ($fields as $field) { $f = new Field($field); if ($f->id == $fs->prefs['color_field']) { $f->values = $this->get_list($f->prefs, $f->id); } $this->fields['field' . $field['field_id']] = $f; } $this->columns = array_combine($this->columns, array_map('L', $this->columns)); foreach ($this->fields as $field) { $this->columns['field' . $field->id] = $field->prefs['field_name']; } if (is_numeric($id) && $id > 0) { $this->prefs = $db->x->getRow("SELECT p.*, c.content AS pm_instructions, c.last_updated AS cache_update\n FROM {projects} p\n LEFT JOIN {cache} c ON c.topic = p.project_id AND c.type = 'msg'\n WHERE p.project_id = ?", null, array($id)); if (is_array($this->prefs)) { $this->id = (int) $this->prefs['project_id']; $this->prefs['visible_columns'] = implode(' ', array_intersect(explode(' ', $this->prefs['visible_columns']), array_keys($this->columns))); $this->prefs['theme_style'] = Filters::enum($this->prefs['theme_style'], Flyspray::listThemes()); return; } } $this->id = 0; $this->prefs = array(); $this->prefs['project_title'] = L('allprojects'); $this->prefs['feed_description'] = L('feedforall'); $this->prefs['theme_style'] = $fs->prefs['global_theme']; $this->prefs['theme_style'] = Filters::enum($this->prefs['theme_style'], Flyspray::listThemes()); $this->prefs['lang_code'] = $fs->prefs['lang_code']; $this->prefs['others_view'] = 1; $this->prefs['intro_message'] = ''; $this->prefs['anon_open'] = $this->prefs['override_user_lang'] = 0; $this->prefs['feed_img_url'] = ''; $this->prefs['default_entry'] = $fs->prefs['default_entry']; $this->prefs['notify_reply'] = ''; $fs->prefs['visible_columns'] = implode(' ', array_intersect(explode(' ', $fs->prefs['visible_columns']), array_keys($this->columns))); return; }
/** * 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 }
/** * 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); }
function get_events($task_id, $where = '', $sort = 'ASC') { global $db; $sort = Filters::enum($sort, array('ASC', 'DESC')); return $db->x->getAll("SELECT h.*,\n p1.project_title AS project_id1,\n p2.project_title AS project_id2,\n lr.item_name AS resolution_name,\n c.date_added AS c_date_added,\n c.user_id AS c_user_id,\n att.orig_name,\n lfn.item_name AS new_value_l,\n lfo.item_name AS old_value_l,\n lcfn.category_name AS new_value_c,\n lcfn.category_name AS old_value_c,\n f.*, li.list_type\n\n FROM {history} h\n\n LEFT JOIN {list_items} lr ON lr.list_item_id = h.new_value AND h.event_type = 2\n LEFT JOIN {list_items} lfn ON lfn.list_item_id = h.new_value AND h.event_type = 3\n LEFT JOIN {list_items} lfo ON lfo.list_item_id = h.old_value AND h.event_type = 3\n LEFT JOIN {list_category} lcfn ON lcfn.category_id = h.new_value AND h.event_type = 3\n LEFT JOIN {list_category} lcfo ON lcfo.category_id = h.old_value AND h.event_type = 3\n LEFT JOIN {fields} f ON f.field_id = h.field_changed AND h.event_type = 3\n LEFT JOIN {lists} li ON f.list_id = li.list_id\n\n LEFT JOIN {projects} p1 ON p1.project_id = h.old_value AND h.field_changed='project_id'\n LEFT JOIN {projects} p2 ON p2.project_id = h.new_value AND h.field_changed='project_id'\n\n LEFT JOIN {comments} c ON c.comment_id = h.field_changed AND h.event_type = 5\n\n LEFT JOIN {attachments} att ON att.attachment_id = h.new_value AND h.event_type = 7\n\n WHERE h.task_id = ? {$where}\n ORDER BY event_date {$sort}, event_type ASC", null, $task_id); }
if (is_readable(BASEDIR . '/setup/index.php') && strpos($fs->version, 'dev') === false) { die('Please empty the folder "' . BASEDIR . DIRECTORY_SEPARATOR . "setup\" before you start using Flyspray.\n" . "If you are upgrading, please go to the setup directory and launch upgrade.php"); } // Get available do-modes and include the classes $modes = str_replace('.php', '', array_map('basename', glob_compat(BASEDIR . "/scripts/*.php"))); // yes, we need all of them for now foreach ($modes as $mode) { require_once BASEDIR . '/scripts/' . $mode . '.php'; } $do = Req::val('do'); // Any "do" mode that accepts a task_id or id field should be added here. if (Req::num('task_id')) { $project_id = $db->x->GetOne('SELECT project_id FROM {tasks} WHERE task_id = ?', null, Req::num('task_id')); $do = Filters::enum($do, array('details', 'depends', 'editcomment')); } else { if ($do == 'admin' && Get::has('switch') && Get::val('project') != '0') { $do = 'pm'; } elseif ($do == 'pm' && Get::has('switch') && Get::val('project') == '0') { $do = 'admin'; } elseif (Get::has('switch') && $do == 'details') { $do = 'index'; } if ($do && class_exists('FlysprayDo' . ucfirst($do)) && !call_user_func(array('FlysprayDo' . ucfirst($do), 'is_projectlevel'))) { $project_id = 0; } } if (!isset($project_id)) { // Determine which project we want to see if (($project_id = Cookie::val('flyspray_project')) == '') {
/** * area_users * * @access public * @return void */ function area_users() { global $fs, $db, $proj, $user, $page; // Prepare the sorting $order_keys = array('username' => 'user_name', 'realname' => 'real_name', 'email' => 'email_address', 'jabber' => 'jabber_id', 'regdate' => 'register_date', 'status' => 'account_enabled'); $order_column[0] = $order_keys[Filters::enum(Get::val('order', 'username'), array_keys($order_keys))]; $order_column[1] = $order_keys[Filters::enum(Get::val('order2', 'username'), array_keys($order_keys))]; $sortorder = sprintf('%s %s, %s %s, u.user_id ASC', $order_column[0], Filters::enum(Get::val('sort', 'desc'), array('asc', 'desc')), $order_column[1], Filters::enum(Get::val('sort2', 'desc'), array('asc', 'desc'))); // Search options $search_keys = array('user_name', 'real_name', 'email_address', 'jabber_id'); $where = 'WHERE 1=1 '; $args = array(); foreach ($search_keys as $key) { if (Get::val($key) != '') { $where .= sprintf(' AND %s LIKE ? ', $key); $args[] = '%' . Get::val($key) . '%'; } } // Search for users in a specific group $groups = Get::val('group_id'); if (is_array($groups) && count($groups) && !in_array(0, $groups)) { $where = ' LEFT JOIN {users_in_groups} uig ON u.user_id = uig.user_id ' . $where; $where .= ' AND (' . substr(str_repeat(' uig.group_id = ? OR ', count($groups)), 0, -3) . ' ) '; $args = array_merge($args, $groups); } $sql = $db->x->getAll('SELECT u.user_id, u.user_name, u.real_name, u.register_date, u.jabber_id, u.email_address, u.account_enabled FROM {users} u ' . $where . 'ORDER BY ' . $sortorder, null, $args); $users = GroupBy($sql, 'user_id'); $page->assign('user_count', count($users)); // Offset and limit $user_list = array(); $offset = max(Get::num('pagenum') - 1, 0) * 50; for ($i = $offset; $i < $offset + 50 && $i < count($users); $i++) { $user_list[] = $users[$i]; } // Get the user groups in a separate query because groups may be hidden // because of search options which are disregarded here if (count($user_list)) { $in = implode(',', array_map(create_function('$x', 'return reset($x);'), $user_list)); $sql = $db->x->getAll('SELECT user_id, g.group_id, g.group_name, g.project_id FROM {groups} g LEFT JOIN {users_in_groups} uig ON uig.group_id = g.group_id WHERE user_id IN (' . $in . ')'); $user_groups = GroupBy($sql, 'user_id', array('group_id', 'group_name', 'project_id'), !REINDEX); $page->assign('user_groups', $user_groups); } $page->assign('all_groups', Flyspray::listallGroups()); $page->assign('user_list', $user_list); }
/** * 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); }
function show() { global $db, $page, $fs, $proj, $do; $page = new FSTpl(); $page->setTheme($proj->prefs['theme_style']); $page->assign('do', $do); $page->pushTpl('baseheader.tpl'); $assignees = ''; if (Get::val('onlyassignees')) { $assignees = 'AND (g.show_as_assignees = 1 OR g.is_admin = 1)'; } $query = 'SELECT g.group_id, g.group_name, g.group_desc, g.group_open, count(u.user_id) AS num_users FROM {groups} g LEFT JOIN {users_in_groups} uig ON uig.group_id = g.group_id LEFT JOIN {users} u ON (uig.user_id = u.user_id ' . $assignees . ') WHERE g.project_id = ? GROUP BY g.group_id'; $page->assign('groups', $db->x->getAll($query, null, $proj->id)); $page->assign('globalgroups', $db->x->getAll($query, null, 0)); // Search conditions $where = array(); $params = array(); foreach (array('user_name', 'real_name') as $key) { if (Post::val($key)) { $where[] = ' ' . $key . ' LIKE ? '; $params[] = '%' . Post::val($key) . '%'; } } $where = count($where) ? implode(' OR ', $where) : '1=1'; // fill the table with users if (Get::val('group_id', -1) > 0) { $order_keys = array('username' => 'user_name', 'realname' => 'real_name'); $order_column = $order_keys[Filters::enum(Get::val('order', 'username'), array_keys($order_keys))]; $sortorder = sprintf('ORDER BY %s %s, u.user_id ASC', $order_column, Filters::enum(Get::val('sort', 'desc'), array('asc', 'desc'))); $users = $db->x->getAll('SELECT u.user_id, user_name, real_name, email_address FROM {users} u LEFT JOIN {users_in_groups} uig ON uig.user_id = u.user_id LEFT JOIN {groups} g ON uig.group_id = g.group_id WHERE uig.group_id = ? ' . $assignees . ' AND ( ' . $where . ' )' . $sortorder, null, array_merge(array(Get::val('group_id')), $params)); // Offset and limit $user_list = array(); $offset = max(Get::num('pagenum') - 1, 0) * 20; for ($i = $offset; $i < $offset + 20 && $i < count($users); $i++) { $user_list[] = $users[$i]; } $page->assign('users', $user_list); } else { // be tricky ^^: show most assigned users $db->setLimit(20); $users = $db->x->getAll('SELECT a.user_id, u.user_name, u.real_name, email_address, count(a.user_id) AS a_count, CASE WHEN t.project_id = ? THEN 1 ELSE 0 END AS my_project FROM {assigned} a LEFT JOIN {users} u ON a.user_id = u.user_id LEFT JOIN {tasks} t ON a.task_id = t.task_id WHERE ( ' . $where . ' )' . ' AND u.account_enabled = 1 GROUP BY a.user_id ORDER BY my_project DESC, a_count DESC', null, array_merge(array($proj->id), $params)); $page->assign('users', $users); } $page->assign('usercount', count($users)); $page->setTitle($fs->prefs['page_title'] . L('userselect')); $page->pushTpl('userselect.tpl'); $page->finish(); }
function enum($key, $options, $default = null) { return Filters::enum(Get::val($key, $default), $options); }
/** * show * * @access public * @return void */ function show() { global $user, $page, $fs, $conf, $db, $proj, $baseurl; $path_to_dot = array_get($conf['general'], 'dot_path', ''); //php 4 on windows does not have is_executable.. $func = function_exists('is_executable') ? 'is_executable' : 'is_file'; $path_to_dot = $func($path_to_dot) ? $path_to_dot : ''; $useLocal = !Flyspray::function_disabled('shell_exec') && $path_to_dot; $fmt = Filters::enum(array_get($conf['general'], 'dot_format', 'png'), array('png', 'svg')); $id = $this->task['task_id']; $page->assign('task_id', $id); $prunemode = Get::num('prune', 0); $selfurl = CreateURL(array('depends', 'task' . $id)); $pmodes = array(L('none'), L('pruneclosedlinks'), L('pruneclosedtasks')); foreach ($pmodes as $mode => $desc) { if ($mode == $prunemode) { $strlist[] = $desc; } else { $strlist[] = "<a href='" . Filters::noXSS($selfurl) . ($mode != 0 ? "&prune={$mode}" : "") . "'>{$desc}</a>\n"; } } $page->assign('strlist', $strlist); $starttime = microtime(); $sql = 'SELECT t1.task_id AS id1, t1.prefix_id AS pxid1, p1.project_prefix AS ppx1, t1.item_summary AS sum1, t1.percent_complete AS pct1, t1.is_closed AS clsd1, t1.closure_comment AS com1, u1c.real_name AS clsdby1, r1.item_name as res1, t2.task_id AS id2, t2.prefix_id AS pxid2, p2.project_prefix AS ppx2, t2.item_summary AS sum2, t2.percent_complete AS pct2, t2.is_closed AS clsd2, t2.closure_comment AS com2, u2c.real_name AS clsdby2, r2.item_name as res2 FROM {dependencies} AS d JOIN {tasks} AS t1 ON d.task_id=t1.task_id LEFT JOIN {users} AS u1c ON t1.closed_by=u1c.user_id LEFT JOIN {projects} AS p1 ON t1.project_id = p1.project_id LEFT JOIN {list_items} AS r1 ON t1.resolution_reason=r1.list_item_id JOIN {tasks} AS t2 ON d.dep_task_id=t2.task_id LEFT JOIN {users} AS u2c ON t2.closed_by=u2c.user_id LEFT JOIN {projects} AS p2 ON t2.project_id = p2.project_id LEFT JOIN {list_items} AS r2 ON t2.resolution_reason=r2.list_item_id WHERE t1.project_id= ? ORDER BY d.task_id, d.dep_task_id'; $edges = $db->x->getAll($sql, null, $proj->id); $edge_list = array(); $rvrs_list = array(); $node_list = array(); foreach ($edges as $row) { extract($row, EXTR_REFS); $edge_list[$id1][] = $id2; $rvrs_list[$id2][] = $id1; if (!isset($node_list[$id1])) { $node_list[$id1] = array('id' => $id1, 'sum' => $sum1, 'pct' => $pct1, 'clsd' => $clsd1, 'ppx' => $ppx1, 'pxid' => $pxid1, 'com' => $com1, 'clsdby' => $clsdby1, 'res' => $res1); } if (!isset($node_list[$id2])) { $node_list[$id2] = array('id' => $id2, 'sum' => $sum2, 'pct' => $pct2, 'clsd' => $clsd2, 'ppx' => $ppx2, 'pxid' => $pxid2, 'com' => $com2, 'clsdby' => $clsdby2, 'res' => $res2); } } // Now we have our lists of nodes and edges, along with a helper // list of reverse edges. Time to do the graph coloring, so we know // which ones are in our particular connected graph. We'll set up a // list and fill it up as we visit nodes that are connected to our // main task. $connected = array(); $levelsdown = 0; $levelsup = 0; function ConnectsTo($id, $down, $up, &$connected, &$edge_list, &$rvrs_list, &$levelsdown, &$levelsup, &$prunemode, &$node_list) { if (!isset($connected[$id])) { $connected[$id] = 1; } if ($down > $levelsdown) { $levelsdown = $down; } if ($up > $levelsup) { $levelsup = $up; } $selfclosed = $node_list[$id]['clsd']; if (isset($edge_list[$id])) { foreach ($edge_list[$id] as $neighbor) { $neighborclosed = $node_list[$neighbor]['clsd']; if (!isset($connected[$neighbor]) && !($prunemode == 1 && $selfclosed && $neighborclosed) && !($prunemode == 2 && $neighborclosed)) { ConnectsTo($neighbor, $down, $up + 1, $connected, $edge_list, $rvrs_list, $levelsdown, $levelsup, $prunemode, $node_list); } } } if (isset($rvrs_list[$id])) { foreach ($rvrs_list[$id] as $neighbor) { $neighborclosed = $node_list[$neighbor]['clsd']; if (!isset($connected[$neighbor]) && !($prunemode == 1 && $selfclosed && $neighborclosed) && !($prunemode == 2 && $neighborclosed)) { ConnectsTo($neighbor, $down + 1, $up, $connected, $edge_list, $rvrs_list, $levelsdown, $levelsup, $prunemode, $node_list); } } } } ConnectsTo($id, 0, 0, $connected, $edge_list, $rvrs_list, $levelsdown, $levelsup, $prunemode, $node_list); $connected_nodes = array_keys($connected); sort($connected_nodes); // Now lets get rid of the extra junk in our arrays. // In prunemode 0, we know we're only going to have to get rid of // whole lists, and not elements in the lists, because if they were // in the list, they'd be connected, so we wouldn't be removing them. // In prunemode 1 or 2, we may have to remove stuff from the list, because // you can have an edge to a node that didn't end up connected. foreach (array("edge_list", "rvrs_list", "node_list") as $l) { foreach (${$l} as $n => $list) { if (!isset($connected[$n])) { unset(${$l}[$n]); } if ($prunemode != 0 && $l != "node_list" && isset(${$l}[$n])) { // Only keep entries that appear in the $connected_nodes list ${$l}[$n] = array_intersect(${$l}[$n], $connected_nodes); } } } // Now we've got everything we need... let's draw the pretty pictures //Open the graph, and print global options $lj = 'n'; // label justification - l, r, or n (for center) $graphname = "task_{$id}_dependencies"; $dotgraph = "digraph {$graphname} {\n" . "node [width=1.1, shape=ellipse, border=10, color=\"#00E11E\", style=\"filled\", " . "fontsize=10.0, pencolor=black, margin=\"0.1, 0.0\"];\n"; // define the nodes foreach ($node_list as $n => $r) { $col = ""; if ($r['clsd'] && $n != $id) { $r['pct'] = 120; } // color code: shades of gray for % done $x = dechex(255 - ($r['pct'] + 10)); $col = "#{$x}{$x}{$x}"; // Make sure label terminates in \n! $label = $r['ppx'] . '#' . $r['pxid'] . " \n" . ($useLocal ? addslashes(utf8_substr($r['sum'], 0, 15)) . "\n" : '') . ($r['clsd'] ? L('closed') : "{$r['pct']}% " . L('complete')); $tooltip = $r['clsd'] ? L('closed') . ": {$r['res']}" . (!empty($r['clsdby']) ? " ({$r['clsdby']})" : '') . ($r['com'] != '' ? ' - ' . str_replace(array("\r", "\n"), '', $r['com']) : '') : $r['pct']; $dotgraph .= "FS{$n} [label=\"" . str_replace("\n", "\\{$lj}", $label) . "\", " . ($r['clsd'] ? 'color=black,' : '') . ($r['clsd'] ? 'fillcolor=white,' : "fillcolor=\"{$col}\",") . ($n == $id ? 'shape=box,' : '') . "href=\"javascript:top.window.location.href='" . CreateURL(array("details", 'task' . $n)) . "'\", target=\"_top\" " . "tooltip=\"{$tooltip}\"];\n"; } // Add edges foreach ($edge_list as $src => $dstlist) { foreach ($dstlist as $dst) { $dotgraph .= "FS{$src} -> FS{$dst};\n"; } } // all done $dotgraph .= "}\n"; // All done with the graph. Save it to a temp file (new name if the data has changed) $dotfilename = sprintf('cache/fs_depends_dot_%d_%s.dot', $id, md5($dotgraph)); $imgfilename = sprintf('%s/%s.%s', BASEDIR, $dotfilename, $fmt); $mapfilename = sprintf('%s/%s.%s', BASEDIR, $dotfilename, 'map'); //cannot use tempnam( ) as file has to end with $ftm extension if (!$useLocal) { //cannot use tempnam() as file has to end with $ftm extension $tname = $dotfilename; } else { // we are operating on the command line, avoid races. $tname = tempnam(Flyspray::get_tmp_dir(), md5(uniqid(mt_rand(), true))); } //get our dot done.. file_put_contents($tname, $dotgraph, LOCK_EX); // Now run dot on it, if target file does not already exist if (!is_file($imgfilename)) { if (!$useLocal) { require_once 'Zend/Rest/Client.php'; $client = new Zend_Rest_Client('http://webdot.flyspray.org/'); $data = base64_decode($client->getGraph(base64_encode($dotgraph), $fmt)->post()); file_put_contents($imgfilename, $data, LOCK_EX); $data = base64_decode($client->getGraph(base64_encode($dotgraph), 'cmapx')->post()); file_put_contents($mapfilename, $data, LOCK_EX); } else { $tfn = escapeshellarg($tname); shell_exec(sprintf('%s -T %s -o %s %s', $path_to_dot, escapeshellarg($fmt), escapeshellarg($imgfilename), $tfn)); $data['map'] = shell_exec(sprintf('%s -T cmapx %s', $path_to_dot, $tfn)); file_put_contents($mapfilename, $data['map'], LOCK_EX); // Remove files so that they are not exposed to the public unlink($tname); } } $page->assign('map', file_get_contents($mapfilename)); $page->assign('image', sprintf('%s%s.%s', $baseurl, $dotfilename, $fmt)); // we have to find out the image size if it is SVG if ($fmt == 'svg') { if (!$remote) { $data = file_get_contents(BASEDIR . '/' . $file_name . '.' . $fmt); } preg_match('/<svg width="([0-9.]+)([a-zA-Z]+)" height="([0-9.]+)([a-zA-Z]+)"/', $data, $matches); $page->assign('width', round($matches[1] * ($matches[2] == 'pt' ? 1.4 : ($matches[2] == 'in' ? 1.33 * 72.27 : 1)), 0)); $page->assign('height', round($matches[3] * ($matches[4] == 'pt' ? 1.4 : ($matches[4] == 'in' ? 1.35 * 72.27 : 1)), 0)); } /* [TC] We cannot have this stuff outputting here, so I put it in a quick template */ $page->assign('taskid', $id); $page->assign('fmt', $fmt); $page->assign('graphname', $graphname); $endtime = microtime(); list($startusec, $startsec) = explode(' ', $starttime); list($endusec, $endsec) = explode(' ', $endtime); $diff = $endsec - $startsec + ($endusec - $startusec); $page->assign('time', round($diff, 2)); $page->setTitle($this->task['project_prefix'] . '#' . $this->task['prefix_id'] . ': ' . L('dependencygraph')); $page->pushTpl('depends.tpl'); }