/** * Method to get a DatabaseQuery object for retrieving the data set from a database for ajax request. * * @return DatabaseQuery * * @since 1.0 */ protected function getAjaxListQuery() { $db = $this->getDb(); $query = $db->getQuery(true); $query->select("a.priority, a.issue_number, a.title, a.foreign_number, a.opened_date, a.closed_date, a.modified_date, a.labels, a.merge_state"); $query->from($db->quoteName('#__issues', 'a')); // Join over the status. $query->select('s.status AS status_title, s.closed AS closed_status'); $query->join('LEFT', '#__status AS s ON a.status = s.id'); $filter = $this->getProject()->project_id; if ($filter) { $query->where($db->quoteName('a.project_id') . ' = ' . (int) $filter); } $filter = $this->state->get('filter.search'); if ($filter) { $query = $this->processSearchFilter($query, $filter); } $filter = $this->state->get('filter.status'); if ($filter) { $query->where($db->quoteName('a.status') . ' = ' . (int) $filter); } $filter = $this->state->get('filter.state'); if (is_numeric($filter)) { $query->where($db->quoteName('s.closed') . ' = ' . (int) $filter); } $filter = $this->state->get('filter.priority'); if ($filter) { $query->where($db->quoteName('a.priority') . ' = ' . (int) $filter); } $filter = $this->state->get('filter.user'); if ($filter && is_numeric($filter)) { $username = $this->state->get('username'); switch ($filter) { case 1: $query->where($db->quoteName('a.opened_by') . ' = ' . $db->quote($username)); break; case 2: // Join over the activities. $query->join('LEFT', '#__activities AS ac ON a.issue_number = ac.issue_number'); $query->where($db->quoteName('ac.user') . ' = ' . $db->quote($username)); $query->where($db->quoteName('ac.project_id') . ' = ' . (int) $this->getProject()->project_id); $query->group('a.issue_number'); break; } } $filter = $this->state->get('filter.category'); if ($filter && is_numeric($filter)) { $categoryModel = new CategoryModel($db); $issues = $categoryModel->getIssueIdsByCategory($filter); if ($issues != null) { $issueId = array(); foreach ($issues as $issue) { $issueId[] = $issue->issue_id; } $issueId = implode(', ', $issueId); } else { $issueId = 0; } $query->where($db->quoteName('a.id') . ' IN (' . $issueId . ')'); } $filter = $this->state->get('filter.tests'); if ($filter && is_numeric($filter)) { // Common query elements $query->leftJoin($db->quoteName('#__issues_tests', 'it') . 'ON a.id = it.item_id')->where($db->quoteName('a.has_code') . ' = 1')->group('a.issue_number'); switch ($filter) { case 1: $query->where($db->quoteName('it.result') . ' = 1')->having('COUNT(it.item_id) = 1'); break; case 2: $query->where($db->quoteName('it.result') . ' = 1')->having('COUNT(it.item_id) > 1'); break; case 3: $query->having('COUNT(it.item_id) = 0'); break; } } $filter = $this->state->get('filter.easytest'); if ($filter && is_numeric($filter)) { $query->where($db->quoteName('a.easy') . ' = ' . (int) $filter); } $ordering = $db->escape($this->state->get('list.ordering', 'a.issue_number')); $direction = $db->escape($this->state->get('list.direction', 'DESC')); $query->order($ordering . ' ' . $direction); return $query; }
/** * Common function to process the filters for a query based on the model state * * @param DatabaseQuery $query DatabaseQuery object * * @return DatabaseQuery * * @since 1.0 */ private function processStateFilter(DatabaseQuery $query) { $db = $this->getDb(); $filter = $this->getProject()->project_id; if ($filter) { $query->where($db->quoteName('a.project_id') . ' = ' . (int) $filter); } $filter = $this->state->get('filter.search'); if ($filter) { $query = $this->processSearchFilter($query, $filter); } $filter = $this->state->get('filter.status'); if ($filter) { $query->where($db->quoteName('a.status') . ' = ' . (int) $filter); } $filter = $this->state->get('filter.state'); // State == 2 means "all". if (is_numeric($filter) && 2 != $filter) { $query->where($db->quoteName('s.closed') . ' = ' . (int) $filter); } $filter = $this->state->get('filter.priority'); if ($filter) { $query->where($db->quoteName('a.priority') . ' = ' . (int) $filter); } $filter = $this->state->get('filter.user'); if ($filter && is_numeric($filter)) { $username = $this->state->get('username'); switch ($filter) { case 1: $query->where($db->quoteName('a.opened_by') . ' = ' . $db->quote($username)); break; case 2: // Join over the activities. $query->join('LEFT', '#__activities AS ac ON a.issue_number = ac.issue_number'); $query->where($db->quoteName('ac.user') . ' = ' . $db->quote($username)); $query->where($db->quoteName('ac.project_id') . ' = ' . (int) $this->getProject()->project_id); $query->group('a.issue_number'); break; } } $filter = $this->state->get('filter.created_by'); if ($filter) { // Clean filter variable $filter = $db->quote('%' . $db->escape(StringHelper::strtolower($filter), true) . '%', false); $query->where($db->quoteName('a.opened_by') . ' LIKE ' . $filter); } $filter = $this->state->get('filter.category'); if ($filter && is_numeric($filter)) { $categoryModel = new CategoryModel($db); // If the category filter equals -1, that means we want issues without category. if ($filter == -1) { $issues = $categoryModel->getIssueIdsWithCategory(); } else { $issues = $categoryModel->getIssueIdsByCategory($filter); } if ($issues != null) { $issueId = array(); foreach ($issues as $issue) { $issueId[] = $issue->issue_id; } $issueId = implode(', ', $issueId); } else { $issueId = 0; } // Handle the no category filter if ($filter == -1) { $query->where($db->quoteName('a.id') . ' NOT IN (' . $issueId . ')'); } else { $query->where($db->quoteName('a.id') . ' IN (' . $issueId . ')'); } } $filter = $this->state->get('filter.label'); if ($filter && is_numeric($filter)) { $query->where('FIND_IN_SET(' . $filter . ', ' . $db->quoteName('a.labels') . ')'); } $filter = $this->state->get('filter.tests'); if ($filter && is_numeric($filter)) { // Common query elements $query->leftJoin($db->quoteName('#__issues_tests', 'it') . 'ON a.id = it.item_id')->where($db->quoteName('a.has_code') . ' = 1')->group('a.issue_number'); switch ($filter) { case 1: $query->where($db->quoteName('it.result') . ' = 1')->having('COUNT(it.item_id) = 1'); break; case 2: $query->where($db->quoteName('it.result') . ' = 1')->having('COUNT(it.item_id) > 1'); break; case 3: $query->having('COUNT(it.item_id) = 0'); break; } } $filter = $this->state->get('filter.easytest'); if ($filter && is_numeric($filter)) { $query->where($db->quoteName('a.easy') . ' = ' . (int) $filter); } return $query; }