function listCategories($id, $hide_hidden = true, $remove_root = true, $depth = true)
{
global $db;
// start with a empty arrays
$right = array();
$cats = array();
// retrieve the left and right value of the root node
$row = $db->x->getRow("SELECT lft, rgt\n FROM {list_category}\n WHERE category_name = 'root' AND lft = 1 AND list_id = ?", null, array($id));
$groupby = GetColumnNames('{list_category}', 'c.category_id', 'c');
// now, retrieve all descendants of the root node
$result = $db->x->getAll('SELECT c.category_id, c.category_name, c.*
FROM {list_category} c
WHERE list_id = ? AND lft BETWEEN ? AND ?
GROUP BY ' . $groupby . '
ORDER BY lft ASC', null, array($id, intval($row['lft']), intval($row['rgt'])));
foreach ($result as $row) {
if ($hide_hidden && !$row['show_in_list'] && $row['lft'] != 1) {
continue;
}
// check if we should remove a node from the stack
while (count($right) > 0 && $right[count($right) - 1] < $row['rgt']) {
array_pop($right);
}
$cats[] = $row + array('depth' => count($right) - 1);
// add this node to the stack
$right[] = $row['rgt'];
}
// Adjust output for select boxes
if ($depth) {
foreach ($cats as $key => $cat) {
if ($cat['depth'] > 0) {
$cats[$key]['category_name'] = str_repeat('...', $cat['depth']) . $cat['category_name'];
}
}
}
if ($remove_root) {
unset($cats[0]);
}
return $cats;
}
/**
* 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 listCategories($id, $hide_hidden = true, $remove_root = true, $depth = true)
{
global $db;
// start with a empty arrays
$right = array();
$cats = array();
// retrieve the left and right value of the root node
//debuglog("SELECT lft, rgt FROM {list_category} WHERE category_name='root' AND lft=1 AND list_id=".$id);
$rootsql = "SELECT lft, rgt FROM {list_category} WHERE category_name='root' AND lft=1 AND list_id=?";
$row = $db->x->getRow($rootsql, null, array($id));
// IF we FAILED :( create a new one, because something bad has happened.
if ($row == 0) {
debuglog("Something bad has happened to list number {$id}, and it's root entry has gone missing! Attempting to recover.");
$this->createRootCategory($id);
$row = $db->x->getRow($rootsql, null, array($id));
// try again!
if ($row == 0) {
trigger_error("class.project.php: listCategories: unable to find root category node! Unable to create a new one! BAD!");
}
}
$groupby = GetColumnNames('{list_category}', 'c.category_id', 'c');
//debuglog("SELECT c.category_id, c.category_name, c.* FROM {list_category} c WHERE list_id=$id AND lft BETWEEN $row[lft] AND $row[rgt] GROUP BY $groupby ORDER BY lft ASC");
// now, retrieve all descendants of the root node
$result = $db->x->getAll('SELECT c.category_id, c.category_name, c.*
FROM {list_category} c
WHERE list_id = ? AND lft BETWEEN ? AND ?
GROUP BY ' . $groupby . '
ORDER BY lft ASC', null, array($id, intval($row['lft']), intval($row['rgt'])));
foreach ($result as $row) {
if ($hide_hidden && !$row['show_in_list'] && $row['lft'] != 1) {
continue;
}
// check if we should remove a node from the stack
while (count($right) > 0 && $right[count($right) - 1] < $row['rgt']) {
array_pop($right);
}
$cats[] = $row + array('depth' => count($right) - 1);
// add this node to the stack
$right[] = $row['rgt'];
}
// Adjust output for select boxes
if ($depth) {
foreach ($cats as $key => $cat) {
if ($cat['depth'] > 0) {
$cats[$key]['category_name'] = str_repeat('...', $cat['depth']) . $cat['category_name'];
}
}
}
if ($remove_root) {
unset($cats[0]);
}
return $cats;
}
