<?php
// query.php - query manager
// ------------------------------------------------------------------------
// Copyright (c) 2001, 2002 The phpBugTracker Group
// Copyright (c) 2004, 2005 David Han Sze Chuen, Denis Guinnepain for Air Liquide Medical Systems (Taema)
// Copyright (c) 2005, 2006, 2015 Denis Guinnepain
 
// ------------------------------------------------------------------------
// This file is part of Diamentis

// Diamentis 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.

// Diamentis 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.
// ------------------------------------------------------------------------

require_once 'include.php';
require_once 'inc/func_query.inc.php';
require_once 'exportpdf.php';
@include_once 'diameter.php';

/*
Structure of the query info array in $_SESSION : 
Example : 
$_SESSION['queryinfo'] ['built_sql_query'] ['0'] = SELECT fi.* FROM mplus_file_index fi, mplus_file_prj_id_2 f WHERE fi.file_id = f.bug_id  
$_SESSION['queryinfo'] ['built_sql_query'] ['1'] = SELECT fi.* FROM mplus_file_index fi, mplus_file_prj_id_3 f WHERE fi.file_id = f.bug_id  
$_SESSION['queryinfo'] ['built_sql_query'] ['2'] = SELECT fi.* FROM mplus_file_index fi, mplus_file_prj_id_4 f WHERE fi.file_id = f.bug_id  
$_SESSION['queryinfo'] ['page'] = 1  
$_SESSION['queryinfo'] ['order'] = closed_date  
$_SESSION['queryinfo'] ['sort'] = asc  
$_SESSION['queryinfo'] ['selected_file_ids'] = all  

built_sql_query : array of sql queries to do to get the results of a search
page : page number
order : order the results by the field_name stored in "order". note : it's used only when processing the query results presentation. Not in SQL
sort : sort order : asc or desc
selected_file_ids : array of selected file ids, or 'all' or 'none'

*/

// //////////////////////////////////////////////////////////////////////////////
/**
 * delete_saved_query()
 * Delete a saved query
 * 
 * @param integer $queryid 
 * @return 
 */
function delete_saved_query($queryid) {
	global $db, $u, $me, $_gv;

	$db->query("delete from " . TBL_SAVED_QUERY . " where user_id = $u
		and saved_query_id = $queryid");
	if (!empty($_gv['form']) and $_gv['form'] == 'advanced') {
		header("Location: $me?op=query&form=advanced");
	} else {
		header("Location: $me?op=query");
	} 
} 
// //////////////////////////////////////////////////////////////////////////////
/**
 * Show_simple_query_form()
 * Assign public fields' data in the template
 * 
 * @return void 
 */
 /*
function show_simple_query_form($project_id = PUBLIC_PROJECT_ID) {
	global $t, $system_hidden_field_names;

	$mpfile = &new MPFile($project_id); 
	// we must reorder the field conf by edit order
	$fc = $mpfile->project_field_properties; 
	// DEBUG
	// println("before sort");
	// foreach($fc as $fk=>$fv) {
	// println("$fk : $fv[edit_order]");
	// }
	// filter out the hidden fields
	foreach($fc as $k => $v) {
		if (in_array($k, $system_hidden_field_names)) {
			unset($fc[$k]);
		} 
	} 

	$fc = order_by_element_of_array('edit_order', 'asc', $fc); 
	// println("after sort");
	// foreach($fc as $fk=>$fv) {
	// println("$fk : $fv[edit_order]");
	// }
	$t->assign('fc', $fc); // field conf data
	$t->assign('mpfile', $mpfile); 
	// $t->assign('sp', $mpfile->sp_cache); // the subproperties
	// $t->assign('sb', $mpfile->sb_cache); // the selectbox data
	show_query('simple');
} 
*/
// //////////////////////////////////////////////////////////////////////////////
/**
 * show_advanced_query_form()
 * TODO : this function isn't finished
 * 
 * @return 
 */
 /*
function show_advanced_query_form($project_id) {
	global $t, $system_hidden_field_names;

	$mpfile = &new MPFile(PUBLIC_PROJECT_ID); 
	// we must reorder the field conf by edit order
	$fc = $mpfile->project_field_properties; 
	// filter out the hidden fields
	foreach($fc as $k => $v) {
		if (in_array($k, $system_hidden_field_names)) {
			unset($fc[$k]);
		} 
	} 

	$fc = order_by_element_of_array('edit_order', 'asc', $fc); 
	
	$t->assign('fc', $fc); // field conf data
	$t->assign('mpfile', $mpfile); 
	show_query('advanced');
} // end of $project_id
*/
// //////////////////////////////////////////////////////////////////////////////
/**
 * show_query()
 * Show a query form : simple or advanced
 * 
 * @return void 
 */
function show_query($project_id = PUBLIC_PROJECT_ID) {
	global $db, $t, $u, $system_hidden_field_names, $TITLE,$restricted_projects,$DiaMeter,$STRING;

	$project_id = intval($project_id);
	
	$mpfile = &new MPFile($project_id); 
	// we must reorder the field conf by edit order
	$fc = $mpfile->project_field_properties; 
	// DEBUG
	// println("before sort");
	// foreach($fc as $fk=>$fv) {
	// println("$fk : $fv[edit_order]");
	// }
	// filter out the hidden fields
	foreach($fc as $k => $v) {
		if (in_array($k, $system_hidden_field_names)) {
			unset($fc[$k]);
		}
		else
		{
		// subproperties
			$prop = & $mpfile->sp_cache[$v['field_name']];
			if ($prop->hidden==true)
			{
				unset($fc[$k]);
			};
			if ($prop->label==true)
			{
				unset($fc[$k]);
			};
		}
	} 
	if($project_id != PUBLIC_PROJECT_ID) {
		unset($fc['project_id']);
	}

	$list_of_project = array();
	foreach($db->getAll("SELECT * FROM " . TBL_PROJECT . " WHERE project_id != " . PUBLIC_PROJECT_ID) as $v) {
		$array_rest_pj=explode(',', $restricted_projects);
		if (!in_array($v['project_id'], $array_rest_pj))
		{
			$list_of_project[$v['project_id']] = htmlspecialchars($v['project_name']);
		}
	}
	if (count($list_of_project)==0)
	{
		death("",$STRING['noDomainAvailable']);
	}	
	$list_of_project[PUBLIC_PROJECT_ID] = $TITLE['simple_search'];
	//printarray($array_rest_pj);
	//printarray($list_of_project);
	$t->assign('list_of_project', $list_of_project);
	
	$fc = order_by_element_of_array('edit_order', 'asc', $fc); 
	// println("after sort");
	// foreach($fc as $fk=>$fv) {
	// println("$fk : $fv[edit_order]");
	// }
	$t->assign('fc', $fc); // field conf data
	$t->assign('mpfile', $mpfile); 
	
	// if user is logged
	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'"));
	} 

	$t->assign('internal_date_format_js', INTERNAL_DATE_FORMAT_JS);
	$t->assign('default_textarea_width', DEFAULT_TEXTAREA_WIDTH);
	$t->assign('default_textarea_height', DEFAULT_TEXTAREA_HEIGHT);
	$t->assign('no_comments', HIDE_COMMENTS);
	$t->assign('DiaMeter', $DiaMeter);
	$t->assign('domain_id', $project_id);
	

	//switch (strtolower($type_of_query)) {
	//	case 'simple' :
	$t->wrap('queryform-simple.html');
	//		break;
	//	case 'advanced' :
	//		$t->wrap('queryform.html');
	//		break;
	//} 
} // end of show_query($type_of_query)  


function save_query()
{
	global $db,$u;
	if (isset($_GET['savedqueryname'])) 
	{
		$savedqueryname = trim(stripslashes($_GET['savedqueryname']));
		if ($savedqueryname != '') 
		{
			// don't save the order to save the query in the query string
			$savedquerystring = ereg_replace("&savedqueryname=([^&])*(&.*)", '\\2', $_SERVER['QUERY_STRING']);
			//$savedquerystring .= '&op=doquery';
			$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($savedqueryname),
							$db->quote(stripslashes($savedquerystring)))) . ")");
		} 
	} 
}




// //////////////////////////////////////////////////////////////////////////////
/**
 * show_query_result()
 * Execute the search using build_query(), then prepare data for the template.
 * 
 * @param array $built_sql_query an array of sql queries to do
 * @param boolean $verbose 
 * @return void 
 */
function show_query_result($built_sql_query, $verbose = false) 
{
	global $db, $STRING, $t, $u, $system_hidden_field_names, $me, $QUERY;
	$isMultiProject = null;
	$fileCount = 0; 
	// $ref_project_id = null;
	$result_ids = array(); 
	// store some query info in session
	if (!isset($_SESSION['queryinfo'])) 
	{
		$_SESSION['queryinfo'] = array();
		//death('ERROR', '00 show_query_result() : authentification session problem.');
	}
	
	// ////////////////////////////////
	// determine page size
	// ////////////////////////////////

	$pageSize = MAX_FILES_PER_PAGE;
	if ($_SESSION['pageSize'] !=0 )
	{
		$pageSize = $_SESSION['pageSize'];
	}
	//else
	//{
	//	death('ERROR', "pageSize vaut " . $_SESSION['pageSize']);
	//}
	
	// ////////////////////////////////
	// determine page ranking
	// ////////////////////////////////
	$page = null; 
	// if page isn't set in $_GET
	if (!isset($_GET['page'])) 
	{
		// is it in $_SESSION ?
			if (isset($_SESSION['queryinfo']['page'])) {
				// use value in SESSION
				$page = intval($_SESSION['queryinfo']['page']);
			} else { // default page value
				$page = 1;
			} 
		} else {
			$page = intval($_GET['page']);
		} 
		if (($page != -1) && ($page < 1)) // if page isn't 'last' => -1, or negative, set to 'first' => 1
			$page = 1; 
		// save page nb in session
		$_SESSION['queryinfo']['page'] = $page;
		$t->assign('page', $page); 
	// ////////////////////////////////////
	// determine sort and order parameters
	// ////////////////////////////////////
		$order = $sort = null;
		if (!isset($_GET['order'])) 
		{
			if (isset($_SESSION['queryinfo']['order'])) {
				$order = $_SESSION['queryinfo']['order'];
				$sort = $_SESSION['queryinfo']['sort'];
			} 
			else 
			{
				$order = 'bug_id';
				$sort = 'asc';
			} 
		}
		else
		{
			$order = stripslashes($_GET['order']);
			if (!isset($_GET['sort'])) 
			{
				$sort = 'asc';
			} 
			else 
			{
				$sort = ($_GET['sort'] == 'asc')? 'asc' : 'desc';
			} 
		}
		// store order and sort in session
		$_SESSION['queryinfo']['order'] = $order;
		$_SESSION['queryinfo']['sort'] = $sort;
		$t->assign('order', $order);
		$t->assign('sort', $sort); 
	// ////////////////////////////////
	// get list to display (short list)
	// ////////////////////////////////
	list($isMultiProject,$DBres) = executeQueryAndOrder($built_sql_query, $order, $sort, $page, $pageSize, $verbose);
	$fileCount=$DBres->numRows();
	$t->assign('fileCount', $fileCount);
	$t->assign('nb_columns', count($field_conf));
	// ////////////////////////////////
	// determine field to display
	// ////////////////////////////////
	// ////////////////////////////////
	// construct array to display
	// ////////////////////////////////
	$mpfiles = array();
	$files = array();
	$file_ids = array();
	$project_id=null;
//	$i=0;
	$project_loaded=array();
	$mpfile_per_file = array();
	if ($page>0)
	{
		$page_index=($page-1)* $pageSize;
		$page_index_fin=$page* $pageSize;
	}
	else
	{
		$page_index=0;
		$page_index_fin=$pageSize;
	}
	while ($file_info=$DBres->fetchRow(DB_FETCHMODE_DEFAULT,$page_index) AND $page_index<$page_index_fin ){
//		println("les nouveaux enregistrements valent");
//		printarray($new_file_info);
		$page_index++;
//	}
//	foreach($result_ids as $result_id => $file_info) {
//		println("les enregistrements valent");
//		printarray($file_info);
		$project_id=$file_info['project_id'];
		if ($project_loaded[$project_id]==true)
		{
		}
		else
		{
			$mpfiles[$project_id] = &new MPFile($project_id);
			$project_loaded[$project_id]=true;
		}
		$file_id=$file_info['file_id'];
		$file_ids[]=$file_id;
		$files[$file_id] = &$mpfiles[$project_id]->getField('*', $file_id, false); 
		$mpfile_per_file[$file_id] = &$mpfiles[$project_id]; // associate this file with the project mpfile
		
	}
	// check that the field name in $order exists
	foreach($mpfiles as $mpfile) 
	{
		if (!isset($mpfile->project_field_properties[$order])) 
		{
			death('ERROR', '01 show_query_result() : order value is incorrect.');
		} 
	} 

	$_SESSION['queryinfo']['result_list']=$file_ids;
	$t->assign('files', $files); // assign ordered files		
	$t->assign('mpfile_per_file', $mpfile_per_file);
//	$mpfile_per_file = array();
//	foreach($files as $k => $rf) 
//		{ // for each file
//		$project_id=$rf['project_id'];
//		$mpfile_per_file[$k] = &$mpfiles[$project_id]; // associate this file with the project mpfile
//		} 
	 

//	$t->assign('mpfile_per_file', $mpfile_per_file);

		// //////////////////////////
		// PREPARE TABLE HEADERS
		// //////////////////////////
		//$mpfile = null;
		//if ($isMultiProject) {
		//	$mpfile = &new MPFile(); // get the public project mpfile
		//} else { // get the current project mpfile
		//	reset($mpfiles);
		//	$mpfile = current($mpfiles); // get an existing mpfile object 
		//}
		//println("mpfiles");
		//printarray($mpfiles);
		//println("mpfile");
		//printarray($mpfile);
		if (empty($mpfile)) 
		{
			$mpfile = &new MPFile(); // get the public project mpfil
		}
		$field_conf = $mpfile->project_field_properties; 
		// some system field must be hidden
		$hidden_field_names=array();
		foreach ($field_conf as $FieldName)
		{
			// subproperties
			$prop = & $mpfile->sp_cache[$FieldName['field_name']];
			if ($prop->hidden==true)
			{
				$hidden_field_names[]=$FieldName['field_name'];
			}
			else
			{
				$field_to_select[$FieldName['field_conf_id']] = $FieldName['title'];
			}
		}
		$field_conf = _array_diff_key($field_conf, array_flip($system_hidden_field_names));
		$field_conf = _array_diff_key($field_conf, array_flip($hidden_field_names));
//		printarray($field_to_select);
//		die;
		$src_html=listbox("FieldToUpdate", $field_to_select, $cmdtype = 11);
		////////////////////////////////////////////
		// PRINT ONLY THE USER SELECTED FIELDS
		////////////////////////////////////////////
		// the user wants to see only some fields
		$db_fields = $_SESSION['db_fields'];
		if ($db_fields != null) { // if user has selected something, if not, all fields are printed
			$user_selected_project_ids = array_keys($db_fields);
			$user_selected_field_names = array();
			
			// if the search is multi-project, only public fields should be printed
			if ($isMultiProject) {
				$db_fields = _array_intersect_key($db_fields, array_flip(array(PUBLIC_PROJECT_ID)));
			}		
			// 'get_project_field' => "SELECT title FROM " . TBL_FIELD_CONF . " where project_id = %s order by list_order",
			foreach($db_fields as $proj_id => $v) {
				$sql = "SELECT * FROM " . TBL_FIELD_CONF . " where project_id = " . $db->quote($proj_id) . " order by list_order";
				
				// $sql = "SELECT * FROM " . TBL_FIELD_CONF . " where project_id IN (" . implode(', ', $user_selected_project_ids) . ") order by list_order";
				$res = $db->getAll($sql);
				foreach($v as $v2) {
					if (isset($res[$v2]) ){  //and !$prop->hidden) {
						$user_selected_field_names[] = $res[$v2]['field_name'];
					}
				}
			}
			if ($verbose) {
				println("show_query_result() : user field pref :");
				printarray($user_selected_field_names);
			}
			$field_conf = _array_intersect_key($field_conf, array_flip($user_selected_field_names));
		}		
		
		// order fields by 'list_order'
		$field_conf = order_by_element_of_array('list_order', 'asc', $field_conf);
		$t->assign('field_conf', $field_conf);


	// ////////////////////////////////
	// save query
	// ////////////////////////////////
	save_query();
	// ////////////////////////////////
	// determine number of page
	// ////////////////////////////////
	$offset = 0;
	$maxpages = ((int) ($fileCount / $pageSize)) + ((($fileCount % $pageSize) == 0)? 0 : 1); // max pages
	if (($page >= 1) && ($page <= $maxpages)) {
		$offset = ($page - 1) * $pageSize;
	} else { // go to last page
		$offset = ($maxpages - 1) * $pageSize;
		$page = $maxpages;
	} 
	// ////////////////////////////////
	// prepare header and footer
	// ////////////////////////////////
		$page_links = array();
		for($i = 1; $i <= $maxpages; $i++) {
			if ($i != $page) {
				$page_links[] = "<A href=\"{$me}?op=redoquery&sort=$sort&order=$order&page=$i\">$i</A>";
			} else {
				$page_links[] = "<B>$i</B>";
			} 
		} 

		if ($page != 1) {
			$t->assign('first_url', "<A href=\"{$me}?op=redoquery&sort=$sort&order=$order&page=1\">&lt;&lt;</A>");
		} 
		if ($page != $maxpages) {
			$t->assign('last_url', "<A href=\"{$me}?op=redoquery&sort=$sort&order=$order&page=-1\">&gt;&gt;</A>");
		} 
		$t->assign('page_links', implode(' | ', $page_links));
	if ($fileCount == 0) 
	{
		$no_result = true;
	} 
	else 
	{ // there are some results
		$no_result = false;
	} 
	$t->assign('no_result', $no_result);
	$t->assign('select_field',$src_html);
	// ////////////////////////////////
	// display 
	// ////////////////////////////////
	$t->wrap('buglist.html');
} // end of show_query_result





function show_stat_result($verbose)
{
	global $db, $STRING, $t, $u, $_GET;
	
	$res=show_stat($_SESSION['queryinfo']['built_sql_query'],$_GET['stat_field'],$_GET['weight_field'], $verbose);
	save_query();
	$name_sql = "SELECT title FROM " . TBL_FIELD_CONF . " fc WHERE fc.field_name=\"" . $_GET['stat_field'] . "\"" ;
	$stat_name =  $db->getOne($name_sql);
	$name_sql = "SELECT title FROM " . TBL_FIELD_CONF . " fc WHERE fc.field_name=\"" . $_GET['weight_field'] . "\"" ;
	$weight_name =  $db->getOne($name_sql);
	$_SESSION['queryinfo']['res']=$res;
	$_SESSION['queryinfo']['title_stat']=$STRING['title_stat'] . $stat_name;
	$_SESSION['queryinfo']['title_weight']=$STRING['title_stat'] . $stat_name ." \n". $STRING['weighted'] . $weight_name;
	$_SESSION['queryinfo']['ChartSelect']=$_GET['ChartSelect'];
	$t->assign('stats',$res);
	$t->assign('field',$stat_name);
	if ((isset($_GET['weight'])? true : false))
	{
		$t->assign('weight_field',$weight_name);
	}
	else
	{
		$t->assign('weight_field',"");
	}
	$t->wrap('statform.html');

} // end of show_stat_result






// //////////////////////////////////////////////////////////////////////////////
// analyse some GET variables
// //////////////////////////////////////////////////////////////////////////////
// should we search the files reported by the user ?
$reportedby = !empty($_GET['reportedby']) ? $_GET['reportedby'] : 0; 
// should we search the files assigned to the user ?
$assignedto = !empty($_GET['assignedto']) ? $_GET['assignedto'] : 0; 
// have these files to be open or close ?
$open = !empty($_GET['open']) ? $_GET['open'] : 0; 
// //////////////////////////////////////////////////////////////////////////////
// analyse requested action
// //////////////////////////////////////////////////////////////////////////////
if (isset($_GET['op'])) {
	switch ($_GET['op']) {
		case 'query' :
			/*
			if (isset($_GET['form']) && $_GET['form'] == 'advanced')
				show_advanced_query_form();
			else
				show_simple_query_form();
			*/
			show_query((isset($_GET['id'])? intval($_GET['id']) : PUBLIC_PROJECT_ID));
			break;
		case 'doquery' :
			// build_query();
			$_SESSION['queryinfo']['built_sql_query'] = build_query(
				$_GET[$primary_input_array], 
				$_GET[$cmptype_input_array], 
				(isset($_GET[$secondary_input_array])? ($_GET[$secondary_input_array]) : null), 
				$_GET['primary_input_comment'], 
				$_GET['cmptype_comment'], 
				(isset($_GET['secondary_input_comment'])? ($_GET['secondary_input_comment']) : null), 
				(isset($_GET['project_id'])? ($_GET['project_id']) : null), 
				(isset($_GET['meet_all_conditions'])? true : false), 
				null,
				isset($_GET['stat'])? false : true,
				$verbose
			);
//			printarray($_GET);
//			printarray($_SESSION['queryinfo']);
			$_SESSION['queryinfo'] ['page'] = 1;
			$_SESSION['queryinfo'] ['domain_id'] = $_GET['domain'];
		case 'redoquery' :
			$t->assign('include_js', array("inc/func_cookie.js")); // include_js is used in wrap.html
			// $t->assign('onUnload', "javascript:treatSelectedFiles();"); // onUnload is used in wrap.html
			$t->assign('action_list', $action_list);
			if ($DiaMeter=="fee" && (isset($_GET['stat'])? true : false))
			{
				show_stat_result($verbose);
			}
			else
			{
				show_query_result($_SESSION['queryinfo']['built_sql_query'], $verbose);
			}
			break;
		case 'delquery' : delete_saved_query($_GET['queryid']);
			break;
		case 'export' : 
			print_multi_files($_SESSION['queryinfo']['result_list']);		
		
			break;
		case 'print' :
			print_list($_SESSION['queryinfo']['built_sql_query']);
			break;
		default : 
			// unknown operation, show the simple query form
			show_query();
			break;
	} 
} else {
	// list_items($assignedto, $reportedby, $open);
	// unknown operation, show the simple query form
	show_query();
} 

?>
