Location: PHPKode > projects > phpBugTracker > query.php
<?php

// query.php - Query the bug database
// ------------------------------------------------------------------------
// Copyright (c) 2001 - 2004 The phpBugTracker Group
// ------------------------------------------------------------------------
// This file is part of phpBugTracker
//
// phpBugTracker is free software; you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation; either version 2 of the License, or
// (at your option) any later version.
//
// phpBugTracker is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with phpBugTracker; if not, write to the Free Software Foundation,
// Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
// ------------------------------------------------------------------------
// $Id: query.php,v 1.120 2009/05/30 07:25:27 brycen Exp $

include 'include.php';

function delete_saved_query($queryid) {
	global $db, $u, $me;

	$db->query("delete from ".TBL_SAVED_QUERY." where user_id = $u and saved_query_id = $queryid");
	if (!empty($_GET['form']) and $_GET['form'] == 'advanced') {
		header("Location: $me?op=query&form=advanced");
	} else {
		header("Location: $me?op=query");
	}
}

function show_query($edit = false) {
	global $db, $t, $u;

	if ($u != 'nobody') {
		// Grab the saved queries if there are any
		$t->assign('queries',
			$db->getAll("select * from ".TBL_SAVED_QUERY." where user_id = '$u'"));
	}

	if ($edit) {
		extract($_GET);
		if (isset($_SESSION['queryinfo']['queryparams'])) {
			extract($_SESSION['queryinfo']['queryparams']);
		}
		$t->assign('project', isset($projects) ? $projects : null);
		$t->assign('version', isset($versions) ? $versions : null);
		$t->assign('component', isset($components) ? $components : null);
		$t->assign('status', isset($status) ? $status : null);
		$t->assign('resolution', isset($resolution) ? $resolution : null);
		$t->assign('os', isset($os) ? $os : null);
		$t->assign('priority', isset($priority) ? $priority : null);
		$t->assign('severity', isset($severity) ? $severity : null);
		$t->assign('database', isset($database) ? $database : null);
		$t->assign('site', isset($site) ? $site : null);
		$t->assign('unassigned', isset($unassigned) ? $unassigned : null);
		$t->assign('emailsearch1', isset($emailsearch1) ? $emailsearch1 : null);
		$t->assign('closedinversion', isset($closedinversion) ? $closedinversion : null);
		$t->assign('tobeclosedinversion', isset($tobeclosedinversion) ? $tobeclosedinversion : null);
		$t->assign('order', isset($order) ? $order : null);
		$t->assign('sort', isset($sort) ? $sort : null);
		$t->assign('emailsearch1', isset($emailsearch1) ? $emailsearch1 : null);
		$t->assign('email1', isset($email1) ? $email1 : null);
		$t->assign('emailtype1', isset($emailtype1) ? $emailtype1 : null);
		$t->assign('emailfield1', isset($emailfield1) ? $emailfield1 : null);
		$t->assign('title', isset($title) ? $title : null);
		$t->assign('title_type', isset($title_type) ? $title_type : null);
		$t->assign('description', isset($description) ? $description : null);
		$t->assign('description_type', isset($description_type) ? $description_type : null);
		$t->assign('url', isset($url) ? $url : null);
		$t->assign('url_type', isset($url_type) ? $url_type : null);
		$t->assign('start_date', isset($start_date) ? $start_date : null);
		$t->assign('end_date', isset($end_date) ? $end_date : null);
		$t->assign('closed_start_date', isset($closed_start_date) ? $closed_start_date : null);
		$t->assign('closed_end_date', isset($closed_end_date) ? $closed_end_date : null);
	}

	// Show the advanced query form
	if (!empty($_GET['form']) and $_GET['form'] == 'advanced' or
		!empty($form) and $form == 'advanced') {
		$t->render('queryform.html', translate("Query Bugs"));
	} else { // or show the simple one
		$t->render('queryform-simple.html', translate("Query Bugs"));
	}

}

function build_query($assignedto, $reportedby, $open, $bookmarked) {
	global $db, $perm, $restricted_projects;

	$paramstr = '';
	foreach ($_GET as $k => $v) {
		$$k = $v;
		if ($k == 'order' or $k == 'sort') continue;
		if (is_array($v)) {
			foreach ($v as $value) {
				$paramstr .= "&{$k}[]=$value";
			}
		} else {
			$paramstr .= "&$k=$v";
		}
	}

	// Open bugs assigned to the user -- a hit list
	if ($assignedto || $reportedby || $bookmarked) {
		$query[] = 'b.status_id '.($open ? '' : 'not ').
			'in ('.OPEN_BUG_STATUSES.')';
		if ($assignedto) {
			$query[] = "assigned_to = {$_SESSION['uid']}";
		} else if ($bookmarked) {
			$query[] = "b.bug_id = bookmark.bug_id AND bookmark.user_id = {$_SESSION['uid']}";
		} else {
			$query[] = "b.created_by = {$_SESSION['uid']}";
		}
	} else {
		// Select boxes
		$flags = array();
		if ($open) {
            $flags[] = 'b.status_id '.($open ? '' : 'not ').  'in ('.OPEN_BUG_STATUSES.')';
		}
		// Need to check $array[0] for Opera --
		// it passes non-empty arrays for every multi-choice select box
		if (!empty($status) and $status[0]) {
			$flags[] = 'b.status_id in ('hide@address.com(',',$status).')';
		}
		// If $resolution[0] == 0 then 'None' was selected
		if (!empty($resolution) or isset($resolution[0])) {
			$flags[] = 'b.resolution_id in ('hide@address.com(',',$resolution).')';
		}
		if (!empty($os) and $os[0]) {
			$flags[] = 'b.os_id in ('hide@address.com(',',$os).')';
		}
		if (!empty($priority) and $priority[0]) {
			$flags[] = 'b.priority in ('hide@address.com(',',$priority).')';
		}
		if (!empty($severity) and $severity[0]) {
			$flags[] = 'b.severity_id in ('hide@address.com(',',$severity).')';
		}
		if (!empty($database) and isset($database[0])) {
			// $database[0] can be 0, which stands for no database reported
			$flags[] = 'b.database_id in ('hide@address.com(',',$database).')';
		}
		if (!empty($site) and $site[0]) {
			$flags[] = 'b.site_id in ('hide@address.com(',',$site).')';
		}
		if (!empty($flags)) {
			$query[] = '('hide@address.com(' and ',$flags).')';
		}
		if (!empty($start_date)) {
			$query[] = 'b.created_date > '.strtotime($start_date);
		}
		if (!empty($end_date)) {
			$query[] = 'b.created_date < '.strtotime($end_date);
		}
		if (!empty($closed_start_date)) {
			$query[] = 'b.close_date > '.strtotime($closed_start_date);
		}
		if (!empty($closed_end_date)) {
			$query[] = 'b.close_date < '.strtotime($closed_end_date);
		}
		if (!empty($unassigned)) {
			$query[] = 'b.assigned_to = 0';
		}

		// Email field(s)
		if (!empty($email1) && !empty($emailfield1)) {
			switch($emailtype1) {
				case 'like' : $econd = "like '%$email1%'"; break;
				case 'rlike' :
				case 'not rlike' :
				case '=' : $econd = "$emailtype1 '$email1'"; break;
			}
			foreach($emailfield1 as $field) $equery[] = "$field.$emailsearch1 $econd";
			$query[] = '('hide@address.com(' and ',$equery).')';
		}

		// Search for additional comments with 'description'
		// TODO: Change this to match the condition selected (see below for rlike, not rlike, etc.)
		$bugs_with_comment = array(0);
		if (!empty($description)) {
			foreach ($db->getAll('SELECT bug_id FROM '.TBL_COMMENT.' WHERE comment_text LIKE \'%'.$description.'%\'') as $row) {
				$bugs_with_comment[] = $row['bug_id'];
			}
		}
		
		// Text search field(s)
		foreach(array('title','url', 'description') as $searchfield) {
			if (!empty($$searchfield)) {
				switch (${$searchfield."_type"}) {
					case 'like' : $cond = "like '%".$$searchfield."%'"; break;
					case 'rlike' : $cond = "rlike '".$$searchfield."'"; break;
					case 'not rlike' : $cond = "not rlike '".$$searchfield."'"; break;
				}
				$fields[] = "b.$searchfield $cond".
					($searchfield == 'description' 
						? ' or b.bug_id in ('hide@address.com(', ', $bugs_with_comment).')'
						: '');
			}
		}
		if (!empty($fields)) $query[] = '('hide@address.com(' and ',$fields).')';

		// Project/Version/Component
		if (!empty($projects)) {
			$proj[] = "b.project_id = '$projects'";
			if (!empty($versions) and $versions != 'All') $proj[] = "b.version_id = '$versions'";
			if (isset($closedinversion) and $closedinversion != '' and $closedinversion != 'All') $proj[] = "b.closed_in_version_id = '$closedinversion'";
			if (isset($tobeclosedinversion) and $tobeclosedinversion != '' and $tobeclosedinversion != 'All') $proj[] = "b.to_be_closed_in_version_id = '$tobeclosedinversion'";
			if (!empty($components) and $components != 'All') $proj[] = "b.component_id = '$components'";
			$query[] = '('hide@address.com(' and ',$proj).')';
		} elseif (!$perm->have_perm('Admin')) { // Filter results from hidden projects
			$query[] = "b.project_id not in ($restricted_projects)";
		}
		// TODO: Something like this can be used for searching descriptions
		/* 
		select b.bug_id, b.title, b.description, c.comment_id, c.comment_text 
		from bug b left join comment c using (bug_id)
		where description like '%yet%' or comment_text like '%yet%'
		*/
	}


	if (!empty($query)) {
		return array(@join(' and ',$query), $paramstr);
	} else {
		return array('', '');
	}
}

// Formatting for spreadsheet
function format_spreadsheet_col($colvalue, $coltype) {
	global $select;
	
	switch($coltype) {
		case 'created_date' :
		case 'last_modified_date' :
		case 'close_date' :
			return ($colvalue ? date(DATE_FORMAT, $colvalue) : '');
		case 'lastmodifier' :
			return (!empty($colvalue) ? maskemail($colvalue) : '');
		case 'reporter' :
		case 'owner' :
		case 'lastmodifier' :
			return (!empty($colvalue) ? maskemail($colvalue) : '');
			break;
		default: return $colvalue;
	}
}

// Handle the formatting for various types of bug info in the bug list
function format_bug_col($colvalue, $coltype, $bugid, $pos) {
	global $select;

	switch ($coltype) {
		case 'url' :
			return "<a href=\"$colvalue\" target=\"_blank\">$colvalue</a>";
			break;
		case 'created_date' :
		case 'last_modified_date' :
		case 'close_date' :
			return '<div align="center">'.
				($colvalue ? date(DATE_FORMAT, $colvalue) : '&nbsp;').
				'</div>';
			break;
		case 'bug_id' :
		case 'title' :
			return "<a href=\"bug.php?op=show&bugid=$bugid&pos=$pos\">$colvalue</a>";
			break;
		case 'reporter' :
		case 'owner' :
		case 'lastmodifier' :
			return '<div align="center">'.
				(!empty($colvalue) ? maskemail($colvalue) : '').'</div>';
			break;
		default :
			return '<div align="center">'.
				(!empty($colvalue) ? $colvalue : '').'</div>';
			break;
	}
}

function list_items($assignedto = 0, $reportedby = 0, $open = 0, $bookmarked = 0) {
	global $me, $db, $t, $select, $u, $default_db_fields, $all_db_fields, $QUERY;

	$query_db_fields = array(
		'bug_id' => 'b.bug_id',
		'title' => 'title',
		'description' => 'b.description',
		'url' => 'url',
		'severity_name' => 'severity.severity_name',
		'priority_name' => 'priority.priority_name',
		'status_name' => 'status.status_name',
		'resolution_name' => 'resolution_name',
		'reporter' => 'reporter.login as reporter',
		'owner' => 'owner.login as owner',
		'created_date' => 'b.created_date',
		'lastmodifier' => 'lastmodifier.login as lastmodifier',
		'last_modified_date' => 'b.last_modified_date',
		'project_name' => 'project.project_name',
		'version_name' => 'version.version_name',
		'to_be_closed_in_version_name' => 'version2.version_name as to_be_closed_in_version_name',
		'closed_in_version_name' => 'version3.version_name as closed_in_version_name',
		'database_name' => TBL_DATABASE.'.database_name',
		'site_name' => 'site.site_name',
		'component_name' => 'component.component_name',
		'os_name' => 'os.os_name',
		'browser_string' => 'browser_string',
		'close_date' => 'close_date',
		'comments' => 'count(distinct comment.comment_id) as comments',
		'attachments' => 'count(distinct attachment.attachment_id) as attachments',
		'votes' => 'count(distinct vote.user_id) as votes'
	);

    // Bug: The aggregates Work for mysql but are missing required "group by" for postgres
	// New: only add expensive joins if the corresponding field is needed. Much faster.
	$join_db_fields = array(
		'attachments'	=> 'left join '.TBL_ATTACHMENT.' attachment on b.bug_id = attachment.bug_id',
		'comments' 	=> 'left join '.TBL_COMMENT.' comment on b.bug_id = comment.bug_id',
		'votes'		=> 'left join '.TBL_BUG_VOTE.' vote on b.bug_id = vote.bug_id',
	);

	$db_headers = array(
		'bug_id' => 'b.bug_id',
		'title' => 'title',
		'description' => 'b.description',
		'url' => 'url',
		'severity_name' => 'severity.sort_order',
		'priority_name' => 'priority.sort_order',
		'status_name' => 'status.sort_order',
		'owner' => 'owner',
		'reporter' => 'reporter.login',
		'lastmodifier' => 'lastmodifier.login',
		'created_date' => 'b.created_date',
		'last_modified_date' => 'b.last_modified_date',
		'project_name' => 'project_name',
		'component_name' => 'component_name',
		'version_name' => 'version_name',
		'os_name' => 'os_name',
		'to_be_closed_in_version_name' => 'version2.version_name',
		'closed_in_version_name' => 'version3.version_name',
		'database_name' => TBL_DATABASE.'.database_name',
		'site_name' => 'site.sort_order',
		'browser_string' => 'browser_string',
		'resolution_name' => 'resolution.sort_order',
		'close_date' => 'close_date',
		'comments' => 'comments',
		'attachments' => 'attachments',
		'votes' => 'votes'
	);

	extract($_GET);
	if (!isset($page)) {
		$page = 1;
	}
	// Save the query if requested
	if (!empty($savedqueryname)) {
		$savedquerystring = ereg_replace('&savedqueryname=.*(&?)', '\1', $_SERVER['QUERY_STRING']);
		$savedquerystring .= '&op=doquery';
		if ($savedqueryoverride) { // Updating an existing query
			$db->query("update ".TBL_SAVED_QUERY." set saved_query_string = ".$db->quote(stripslashes($savedquerystring))." where user_id = $u and saved_query_name = ".$db->quote(stripslashes($savedqueryname)));
		} else { // Adding a new saved query
			$nextid = $db->getOne("select max(saved_query_id)+1 from ".TBL_SAVED_QUERY." where user_id = $u");
			$nextid = $nextid ? $nextid : 1;
			$db->query("insert into ".TBL_SAVED_QUERY." (saved_query_id, user_id, saved_query_name, saved_query_string) values (".join(', ', array($nextid, $u, $db->quote(stripslashes($savedqueryname)), $db->quote(stripslashes($savedquerystring)))).")");
		}
	}
	if (!isset($order)) {
		if (isset($_SESSION['queryinfo']['order'])) {
			$order = $_SESSION['queryinfo']['order'];
			$sort = $_SESSION['queryinfo']['sort'];
		} else {
			$order = 'b.bug_id';
			$sort = 'asc';
		}
	}
	// Taint checking
	if (empty($db_headers[$order])) $order = 'bug_id';
	if (!in_array($sort, array('asc', 'desc'))) $sort = 'asc';
	
	if (empty($_SESSION['queryinfo'])) $_SESSION['queryinfo'] = array();
	$_SESSION['queryinfo']['order'] = $db_headers[$order];;
	$_SESSION['queryinfo']['sort'] = $sort;
	if (empty($_SESSION['queryinfo']['queryparams']) || !empty($_GET)) {
	      $_SESSION['queryinfo']['queryparams'] = $_GET;
	}

	if (empty($_SESSION['queryinfo']['query']) or isset($op)) {
		list($_SESSION['queryinfo']['query'], $paramstr) =
			build_query($assignedto, $reportedby, $open, $bookmarked);
	}
	
	$desired_fields = !empty($_SESSION['db_fields']) ?
		$_SESSION['db_fields'] : $default_db_fields;

	$in_use_query_fields = array('b.bug_id as bug_link_id', 
		'severity.severity_color', 'priority.priority_color');
    $in_use_join_fields = array();
	foreach ($desired_fields as $field) {
		$in_use_query_fields[]     = $query_db_fields[$field];
        if(isset($join_db_fields[$field])) {
            $in_use_join_fields[]  = $join_db_fields[$field];
            }
		$field_titles[]            = $all_db_fields[$field];
		$headers[]                 = $field;
	}

	if (empty($_GET['xl'])) { // HTML view
		$nr = $db->getOne($QUERY['query-list-bugs-count'].
			(!empty($_SESSION['queryinfo']['query'])
				? $QUERY['query-list-bugs-count-join'].
					$_SESSION['queryinfo']['query']
				: ''));
	
		$_SESSION['queryinfo']['numrows'] = $nr;
		list($selrange, $llimit) = multipages($nr, $page, "order=$order&sort=$sort");
	
		$t->assign(array(
			'db_fields' => $desired_fields,
			'field_titles' => $field_titles,
			'has_excel' => find_include('Spreadsheet/Excel/Writer.php')
			));

		$sql = sprintf(
                $QUERY['query-list-bugs'],
				join(', ', $in_use_query_fields),
				join(' ' , $in_use_join_fields),
                (!empty($_SESSION['queryinfo']['query']) ? "and {$_SESSION['queryinfo']['query']} " : ''),
                $db_headers[$order],
                $sort);
		// syslog(LOG_DEBUG,"query=$sql");
		$_SESSION['queryinfo']['full_query_sql'] = $sql;
		$t->assign('bugs', $db->getAll($db->modifyLimitQuery($sql, $llimit, $selrange)));
	
		sorting_headers($me, $headers, $order, $sort, "page=$page".
			(!empty($paramstr) ? $paramstr : ''));
		$t->render('buglist.html', translate("Bug List"));
	} else { // Spreasheet download
		$sql = sprintf(
                $QUERY['query-list-bugs'],
				join(', ', $in_use_query_fields),
				join(' ' , $in_use_join_fields),
                (!empty($_SESSION['queryinfo']['query']) ? "and {$_SESSION['queryinfo']['query']} " : ''),
                $db_headers[$order],
                $sort);
		//syslog(LOG_DEBUG,"query=$sql");
		dump_spreadsheet($desired_fields, $field_titles, $db->getAll($sql) );

        // $db->getAll(
		// sprintf($QUERY['query-list-bugs'], join(', ', $in_use_query_fields),
		// 		(!empty($_SESSION['queryinfo']['query'])
		//	? "and {$_SESSION['queryinfo']['query']} " : ''),
		// $db_headers[$order], $sort))
	}
}

function dump_spreadsheet($fields, $titles, &$data) {
	include_once('Spreadsheet/Excel/Writer.php');
	$workbook = new Spreadsheet_Excel_Writer();
	$workbook->send('buglist.xls');
	error_reporting(0);
	$boldformat =& $workbook->addformat(array('bold' => 1));
	$worksheet =& $workbook->addworksheet('buglist');
	$row = 0;
	for ($i = 0, $colcount = count($fields); $i < $colcount; $i++) {
		$worksheet->write($row, $i, $titles[$i], $boldformat);
	}
	$row++;
	for ($i = 0, $bugcount = count($data); $i < $bugcount; $i++) {
		for ($j = 0; $j < $colcount; $j++) {
			$worksheet->write($row, $j, format_spreadsheet_col($data[$i][$fields[$j]], $fields[$j]));
		}
		$row++;
	}
	$worksheet->freezepanes(array(1, 0));
	$workbook->close();
}

$reportedby = !empty($_GET['reportedby']) ? $_GET['reportedby'] : 0;
$assignedto = !empty($_GET['assignedto']) ? $_GET['assignedto'] : 0;
$open = !empty($_GET['open']) ? $_GET['open'] : 0;
$bookmarked = !empty($_GET['bookmarked']) ? $_GET['bookmarked'] : 0;

// Make sure the page variable is numeric, if it's populated
if (!empty($_GET['page'])) $_GET['page'] =  preg_replace('/[^0-9]/', '', $_GET['page']);

// Make sure the user has permission to list bugs
if (!empty($_GET['projects']) && isset($restricted_projects) && 
	in_array($_GET['projects'], explode(',', $restricted_projects))) {
		show_text(translate("You do not have the rights to view this project.", true));
		exit;
}

if (isset($_GET['op'])) switch($_GET['op']) {
	case 'query' : show_query(); break;
	case 'doquery' : $_SESSION['queryinfo'] = array(); list_items(0,0,$open,0); break;
	case 'delquery' : 
		if ($auth->is_authenticated()) delete_saved_query(check_id($_GET['queryid']));  
		else show_query(); 
		break;
	case 'mybugs' : 
		if ($auth->is_authenticated()) list_items($assignedto, $reportedby, $open, $bookmarked); 
		else show_query(); 
		break;
	case 'edit' : show_query(true); break;
	default : show_query(); break;
}
else list_items($assignedto, $reportedby, $open, $bookmarked);

?>
Return current item: phpBugTracker