/**
* 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;
}
