<?php
// +---------------------------------------------------------------------------------------+
// | TenderSystem                                                                          |
// +---------------------------------------------------------------------------------------+
// | The contents of this file are subject to the TenderSystem Public License Version 1.1  |
// | ("License"); you may not use this file except in compliance with the License. You may |
// | obtain a copy of the License at http://www.tendersystem.com/tpl/                      |
// |                                                                                       |
// | Software distributed under the License is distributed on an "AS IS" basis, WITHOUT    |
// | WARRANTY OF ANY KIND, either express or implied. See the License for the specific     |
// | language governing rights and limitations under the License.                          |
// |                                                                                       |
// | All copies of the Covered Code must include the "Powered by TenderSystem" logo and    |
// | ValueCard copyright notice on every user interface screen and in every outgoing       |
// | email in the same form as they appear in the distribution.                            |
// |                                                                                       |
// | The Original Code is: TenderSystem                                                    |
// | The Initial Developer of the Original Code is ValueCard (Pty) Limited.                |
// | Portions created by ValueCard are Copyright (C) 2002 ValueCard (Pty) Limited.         |
// | All Rights Reserved.                                                                  |
// | Contributor(s): __________________.                                                   |
// +---------------------------------------------------------------------------------------+

/**
* Tender item class
* @package TenderSystem
* @subpackage statistics
* @copyright Copyright &copy; 2002, ValueCard (Pty) Ltd
* @license http://www.tendersystem.com/tpl/ TenderSystem Public License
* @version 
*/

// Security - ensure that file never called directly
// not required if global variables are off or API is hosted on a non-public server
if (eregi(basename(__FILE__),$_SERVER['PHP_SELF'])) {
    die ("You can't access this file directly...");
}

Class tenderitem {
	// set paramaters
	var $user;
	var $tender;
	var $error;
	
	/**
	 * Get monthly statistics
	 * 
	 * @param array params search information
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param mixed tender_information or FALSE on error  
	 */
	function get_monthly_statistics($params,$cfg,$conn) {
		// set default values an array incorrect order
		$this->user = $params;
		if ($params['yaxis'] == 'average_rfq') {
			$sqlstr = "SELECT EXTRACT(MONTH FROM ~tender_item.created_date), SUM(~tender_quote.price) / COUNT(*) ";
			// FROM
			$sqlstr.= "FROM ~tender_item LEFT JOIN ~tender_quote ";
			$sqlstr.= "					 ON	~tender_quote.tender_item = ~tender_item.id ";
			$sqlstr.= "					 AND ~tender_quote.quote_position = 1 ";
			$sqlstr.= "					 AND ~tender_quote.price != 0, ";
			$sqlstr.= "~tender, ~users subscriber ";
			// WHERE
			$sqlstr.= "WHERE ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.classification = 1 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY EXTRACT(MONTH FROM ~tender_item.created_date) ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",FALSE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			require($cfg['file'].'modules/statistics/language/'.$this->user['language'].'/statistics.php');
			for($i = 1; $i < 13; $i++) {
				if($this->tender[$i]) {
					$response[$i-1] = array($lang['months'][$i], $this->tender[$i]);
				} else {
					$response[$i-1] = array($lang['months'][$i],"0");
				}
			}			
		} elseif ($params['yaxis'] == 'conversion_rate') { 
			$sqlstr = "SELECT EXTRACT(MONTH FROM ~tender_item.created_date) AS value, COUNT(*) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~users subscriber ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.classification = 1 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY EXTRACT(MONTH FROM ~tender_item.created_date) ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",FALSE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			require($cfg['file'].'modules/statistics/language/'.$this->user['language'].'/statistics.php');
			for($i = 1; $i < 13; $i++) {
				if($this->tender[$i]) {
					//$response[$i-1] = array($lang['months'][$i],$this->tender[$i] / $response[$i-1][1]);					 
					$response[$i-1] = array($lang['months'][$i], $this->tender[$i]);
				} else {
					$response[$i-1] = array($lang['months'][$i],"0");
				}
			}	
			$sqlstr = "SELECT EXTRACT(MONTH FROM ~tender_item_complete.created_date) AS value, COUNT(*) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~users subscriber, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item.classification = 1 ";
			$sqlstr.= "AND ~tender_item_complete.status = 1 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY EXTRACT(MONTH FROM ~tender_item_complete.created_date) ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",FALSE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			for($i = 1; $i < 13; $i++) {
				if($this->tender[$i]) {										 
					$response[$i-1][1] = ($this->tender[$i] / $response[$i-1][1]) * 100;
				} else {
					$response[$i-1][1] = "0";
				}				
				/*if($this->tender[$i]) {
					//$response[$i-1] = array($lang['months'][$i],$this->tender[$i] / $response[$i-1][1]);					 
					$response[$i-1] = array($lang['months'][$i], $this->tender[$i]);
				} else {
					$response[$i-1] = array($lang['months'][$i],"0");
				}*/				
			}	
				
		} else {			
			$sqlstr = "SELECT EXTRACT(MONTH FROM ~tender_item.created_date) AS value, COUNT(*) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~users subscriber ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.status = 4 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY EXTRACT(MONTH FROM ~tender_item.created_date) ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",FALSE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			require($cfg['file'].'modules/statistics/language/'.$this->user['language'].'/statistics.php');
			for($i = 1; $i < 13; $i++) {
				if($this->tender[$i]) {
					$response[] = array($lang['months'][$i],$this->tender[$i]);
				} else {
					$response[] = array($lang['months'][$i],"0");
				}
			}
			
			$sqlstr = "SELECT EXTRACT(MONTH FROM ~tender_item_complete.created_date) AS value, COUNT(*) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item_complete, ~users subscriber, ~tender_item ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 1 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY EXTRACT(MONTH FROM ~tender_item_complete.created_date) ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",FALSE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			for($i = 1; $i < 13; $i++) {
				if($this->tender[$i]) {
					$response[$i-1][] = $this->tender[$i];
				} else {
					$response[$i-1][] = "0";
				}
			}
			$sqlstr = "SELECT EXTRACT(MONTH FROM ~tender_item_complete.created_date) AS value, COUNT(*) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~users subscriber, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 2 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY EXTRACT(MONTH FROM ~tender_item_complete.created_date) ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",FALSE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			for($i = 1; $i < 13; $i++) {
				if($this->tender[$i]) {
					$response[$i-1][] = $this->tender[$i];
				} else {
					$response[$i-1][] = "0";
				}
			}
			$sqlstr = "SELECT EXTRACT(MONTH FROM ~tender_item_complete.created_date) AS value, COUNT(*) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~users subscriber, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 3 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY EXTRACT(MONTH FROM ~tender_item_complete.created_date) ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",FALSE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			for($i = 1; $i < 13; $i++) {
				if($this->tender[$i]) {
					$response[$i-1][] = $this->tender[$i];
				} else {
					$response[$i-1][] = "0";
				}
			}		
			$sqlstr = "SELECT EXTRACT(MONTH FROM ~tender_item_complete.created_date) AS value, COUNT(*) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~users subscriber, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 4 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY EXTRACT(MONTH FROM ~tender_item_complete.created_date) ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",FALSE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			for($i = 1; $i < 13; $i++) {
				if($this->tender[$i]) {
					$response[$i-1][] = $this->tender[$i];
				} else {
					$response[$i-1][] = "0";
				}
			}
		}
		return $response;
	}
	
	/**
	 * Get daily statistics
	 * 
	 * @param array params search information
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param mixed tender_information or FALSE on error  
	 */
	function get_daily_statistics($params,$cfg,$conn) {
		// set default values an array incorrect order
		$this->user = $params;
		if ($params['yaxis'] == 'average_rfq') {
			$sqlstr = "SELECT EXTRACT(DAY FROM ~tender_item.created_date), SUM(~tender_quote.price) / COUNT(*) ";
			// FROM
			$sqlstr.= "FROM ~tender_item LEFT JOIN ~tender_quote ";
			$sqlstr.= "					 ON	~tender_quote.tender_item = ~tender_item.id ";
			$sqlstr.= "					 AND ~tender_quote.quote_position = 1, ";
			$sqlstr.= "~tender, ~users subscriber ";
			// WHERE
			$sqlstr.= "WHERE ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.classification = 1 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY EXTRACT(DAY FROM ~tender_item.created_date) ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",FALSE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			require($cfg['file'].'modules/statistics/language/'.$this->user['language'].'/statistics.php');
			for($i = 1; $i <= 31; $i++) {
				if($this->tender[$i]) {
					$response[$i][] = $i;
					$response[$i][] = $this->tender[$i];
				} else {
					$response[$i][] = $i;
					$response[$i][] = "0";
				}
			}
		} else {
			$sqlstr = "SELECT EXTRACT(DAY FROM ~tender_item.created_date) AS value, COUNT(*) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~users subscriber ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.status = 4 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY EXTRACT(DAY FROM ~tender_item.created_date) ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",FALSE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			require($cfg['file'].'modules/statistics/language/'.$this->user['language'].'/statistics.php');
			for($i = 1; $i <= 31; $i++) {
				if($this->tender[$i]) {
					$response[$i][] = $i;
					$response[$i][] = $this->tender[$i];
				} else {
					$response[$i][] = $i;
					$response[$i][] = "0";
				}
			}
			$sqlstr = "SELECT EXTRACT(DAY FROM ~tender_item_complete.created_date) AS value, COUNT(*) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~users subscriber, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 1 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY EXTRACT(DAY FROM ~tender_item_complete.created_date) ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",FALSE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			for($i = 1; $i <= 31; $i++) {
				if($this->tender[$i]) {
					$response[$i][] = $this->tender[$i];
				} else {
					$response[$i][] = "0";
				}
			}
			$sqlstr = "SELECT EXTRACT(DAY FROM ~tender_item_complete.created_date) AS value, COUNT(*) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~users subscriber, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 2 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY EXTRACT(DAY FROM ~tender_item_complete.created_date) ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",FALSE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			for($i = 1; $i <= 31; $i++) {
				if($this->tender[$i]) {
					$response[$i][] = $this->tender[$i];
				} else {
					$response[$i][] = "0";
				}
			}
			$sqlstr = "SELECT EXTRACT(DAY FROM ~tender_item_complete.created_date) AS value, COUNT(*) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~users subscriber, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 3 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY EXTRACT(DAY FROM ~tender_item_complete.created_date) ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",FALSE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			for($i = 1; $i <= 31; $i++) {
				if($this->tender[$i]) {
					$response[$i][] = $this->tender[$i];
				} else {
					$response[$i][] = "0";
				}
			}
			$sqlstr = "SELECT EXTRACT(DAY FROM ~tender_item_complete.created_date) AS value, COUNT(*) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~users subscriber, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status =4 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY EXTRACT(DAY FROM ~tender_item_complete.created_date) ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",FALSE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			for($i = 1; $i <= 31; $i++) {
				if($this->tender[$i]) {
					$response[$i][] = $this->tender[$i];
				} else {
					$response[$i][] = "0";
				}
			}
		}
		return $response;
	}
	
	/**
	 * Get hourly statistics
	 * 
	 * @param array params search information
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param mixed tender_information or FALSE on error  
	 */
	function get_hourly_statistics($params,$cfg,$conn) {
		// set default values an array incorrect order
		$this->user = $params;
		if ($params['yaxis'] == 'average_rfq') {
			$sqlstr = "SELECT EXTRACT(HOUR FROM ~tender_item.created_time) AS value, SUM(~tender_quote.price) / COUNT(*) AS count ";
			// FROM			
			$sqlstr.= "FROM ~tender_item LEFT JOIN ~tender_quote ";
			$sqlstr.= "					 ON	~tender_quote.tender_item = ~tender_item.id ";
			$sqlstr.= "					 AND ~tender_quote.quote_position = 1, ";
			$sqlstr.= "~tender, ~users subscriber ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.classification = 1 ";			
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY EXTRACT(HOUR FROM ~tender_item.created_time) ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",FALSE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			for($i = 0; $i < 24; $i++) {
				if($this->tender[$i]) {
					$response[] = array($i.":00",$this->tender[$i]);
				} else {
					$response[] = array($i.":00","0");
				}
			}	
		} else {
			$sqlstr = "SELECT EXTRACT(HOUR FROM ~tender_item.created_time) AS value, COUNT(*) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~users subscriber ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.status = 4 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY EXTRACT(HOUR FROM ~tender_item.created_time) ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",FALSE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			for($i = 0; $i < 24; $i++) {
				if($this->tender[$i]) {
					$response[] = array($i.":00",$this->tender[$i]);
				} else {
					$response[] = array($i.":00","0");
				}
			}
			$sqlstr = "SELECT EXTRACT(HOUR FROM ~tender_item_complete.created_time) AS value, COUNT(*) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~users subscriber, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 1 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY EXTRACT(HOUR FROM ~tender_item_complete.created_time) ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",FALSE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			for($i = 0; $i < 24; $i++) {
				if($this->tender[$i]) {
					$response[$i][] =$this->tender[$i];
				} else {
					$response[$i][] = "0";
				}
			}
			$sqlstr = "SELECT EXTRACT(HOUR FROM ~tender_item_complete.created_time) AS value, COUNT(*) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~users subscriber, ~tender_item_complete  ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 2 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY EXTRACT(HOUR FROM ~tender_item_complete.created_time) ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",FALSE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			for($i = 0; $i < 24; $i++) {
				if($this->tender[$i]) {
					$response[$i][] =$this->tender[$i];
				} else {
					$response[$i][] = "0";
				}
			}
			$sqlstr = "SELECT EXTRACT(HOUR FROM ~tender_item_complete.created_time) AS value, COUNT(*) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~users subscriber, ~tender_item_complete  ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 3 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY EXTRACT(HOUR FROM ~tender_item_complete.created_time) ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",FALSE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			for($i = 0; $i < 24; $i++) {
				if($this->tender[$i]) {
					$response[$i][] =$this->tender[$i];
				} else {
					$response[$i][] = "0";
				}
			}
			$sqlstr = "SELECT EXTRACT(HOUR FROM ~tender_item_complete.created_time) AS value, COUNT(*) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~users subscriber, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 4 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY EXTRACT(HOUR FROM ~tender_item_complete.created_time) ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",FALSE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			for($i = 0; $i < 24; $i++) {
				if($this->tender[$i]) {
					$response[$i][] =$this->tender[$i];
				} else {
					$response[$i][] = "0";
				}
			}
		}
		return $response;
	}
	
	/**
	 * Get subscriber statistics
	 * 
	 * @param array params search information
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param mixed tender_information or FALSE on error  
	 */
	function get_subscriber_statistics($params,$cfg,$conn) {
		// set default values an array incorrect order
		$this->user = $params;
		if ($params['yaxis'] == 'average_rfq') {
			$sqlstr = "SELECT subscriber.id, SUM(~tender_quote.price) / COUNT(~tender_item.id) AS avg ";
			// FROM
			$sqlstr.= "FROM ~tender_item LEFT JOIN ~tender_quote ";
			$sqlstr.= "					 ON	~tender_quote.tender_item = ~tender_item.id ";
			$sqlstr.= "					 AND ~tender_quote.quote_position = 1 ";
			$sqlstr.= "					 AND ~tender_quote.price != 0, ";
			$sqlstr.= "~tender, ~users subscriber ";
			// WHERE
			$sqlstr.= "WHERE ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.classification = 1 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY subscriber.id ";
			$sqlstr.= "ORDER BY (SUM(~tender_quote.price) / COUNT(~tender_item.id)) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			// get user info			
			//require($cfg['file'].'modules/user/class/user.class.php');
			$new_users = new user;
			foreach($this->tender as $key => $value) {
				if ($value['avg'] != 0) {
					$users_info = $new_users->get_info($key,$cfg,$conn);
					$fullname = $users_info['name']." ".$users_info['surname'];
					$response[$key] = array($fullname, $value['avg']);
				}
			}			
		} elseif ($params['yaxis'] == 'conversion_rate') {
			$sqlstr = "SELECT subscriber.id, COUNT(~tender_item.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~users subscriber ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.classification = 1 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY subscriber.id ";
			$sqlstr.= "ORDER BY COUNT(~tender_item.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			// get user info
			$new_users = new user;
			foreach($this->tender as $key => $value) {
				$users_info = $new_users->get_info($key,$cfg,$conn);
				$fullname = $users_info['name']." ".$users_info['surname'];
				$response[$key] = array($fullname,$value['count']);
			}
			$sqlstr = "SELECT subscriber.id, COUNT(~tender_item.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~users subscriber, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item.classification = 1 ";
			$sqlstr.= "AND ~tender_item_complete.status = 1 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY subscriber.id ";
			$sqlstr.= "ORDER BY COUNT(~tender_item.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			$keys = array_keys($response);
			for($i = 0; $i < sizeof($response); $i++) {
				$value_returned = false;
				foreach($this->tender as $key => $value) {	
					if ($keys[$i] == $key) {
						$response[$keys[$i]][1] = ($value['count'] / $response[$keys[$i]][1]) * 100;
						$value_returned = true;
					}					
				}
				if (!$value_returned) {
					$response[$keys[$i]][1] = "0";
				}
			}					
		} else {
			$sqlstr = "SELECT subscriber.id, COUNT(~tender_item.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~users subscriber ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.status = 4 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY subscriber.id ";
			$sqlstr.= "ORDER BY COUNT(~tender_item.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			// get user info
			$new_users = new user;
			foreach($this->tender as $key => $value) {
				$users_info = $new_users->get_info($key,$cfg,$conn);
				$fullname = $users_info['name']." ".$users_info['surname'];
				$response[$key] = array($fullname,$value['count'],"0","0","0","0");
			}
			$sqlstr = "SELECT subscriber.id, COUNT(~tender_item.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~users subscriber, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 1 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY subscriber.id ";
			$sqlstr.= "ORDER BY COUNT(~tender_item.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][2] = $value['count'];
				} else {
					$users_info = $new_users->get_info($key,$cfg,$conn);
					$fullname = $users_info['name']." ".$users_info['surname'];
					$response[$key] = array($fullname,"0",$value['count'],"0","0","0");
				}
	
			}
			$sqlstr = "SELECT subscriber.id, COUNT(~tender_item.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~users subscriber, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 2 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY subscriber.id ";
			$sqlstr.= "ORDER BY COUNT(~tender_item.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][3] = $value['count'];
				} else {
					$users_info = $new_users->get_info($key,$cfg,$conn);
					$fullname = $users_info['name']." ".$users_info['surname'];
					$response[$key] = array($fullname,"0","0",$value['count'],"0","0");
				}
	
			}
			$sqlstr = "SELECT subscriber.id, COUNT(~tender_item.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~users subscriber, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 3 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY subscriber.id ";
			$sqlstr.= "ORDER BY COUNT(~tender_item.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][4] = $value['count'];
				} else {
					$users_info = $new_users->get_info($key,$cfg,$conn);
					$fullname = $users_info['name']." ".$users_info['surname'];
					$response[$key] = array($fullname,"0","0","0",$value['count'],"0");
				}
	
			}
			$sqlstr = "SELECT subscriber.id, COUNT(~tender_item.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~users subscriber, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 4 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY subscriber.id ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][5] = $value['count'];
				} else {
					$users_info = $new_users->get_info($key,$cfg,$conn);
					$fullname = $users_info['name']." ".$users_info['surname'];
					$response[$key] = array($fullname,"0","0","0","0",$value['count']);
				}
	
			}
		}
		return $response;
	}
	
	/**
	 * Get branch statistics
	 * 
	 * @param array params search information
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param mixed tender_information or FALSE on error  
	 */
	function get_branch_statistics($params,$cfg,$conn) {
		// set default values an array incorrect order
		$this->user = $params;
		if ($params['yaxis'] == 'average_rfq') {
			$sqlstr = "SELECT branch.id, SUM(~tender_quote.price) / COUNT(~tender_item.id) AS avg ";
			$sqlstr.= "FROM ~tender_item LEFT JOIN ~tender_quote ";
			$sqlstr.= "					 ON	~tender_quote.tender_item = ~tender_item.id ";
			$sqlstr.= "					 AND ~tender_quote.quote_position = 1, ";
			$sqlstr.= "~tender, ~users subscriber, ~organisation_branch branch ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND subscriber.branch = branch.id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.classification = 1 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY branch.id ";
			$sqlstr.= "ORDER BY SUM(~tender_quote.price) / COUNT(~tender_item.id) DESC ";			
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			// get branch info
			$new_branch = new branch;
			foreach($this->tender as $key => $value) {
				$branch_info = $new_branch->get_info($key,$cfg,$conn);
				$response[$key] = array($branch_info['name'],$value['avg']);
			}
		} elseif ($params['yaxis'] == 'conversion_rate') {  
			$sqlstr = "SELECT branch.id, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~organisation_branch branch, ~tender_item ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND subscriber.branch = branch.id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.classification = 1 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY branch.id ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			// get branch info
			$new_branch = new branch;
			foreach($this->tender as $key => $value) {
				$branch_info = $new_branch->get_info($key,$cfg,$conn);
				$response[$key] = array($branch_info['name'],$value['count']);
			}
			$sqlstr = "SELECT branch.id, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~organisation_branch branch, ~tender_item, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND subscriber.branch = branch.id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item.classification = 1 ";			
			$sqlstr.= "AND ~tender_item_complete.status = 1 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY branch.id ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			$keys = array_keys($response);
			for($i = 0; $i < sizeof($response); $i++) {
				$value_returned = false;
				foreach($this->tender as $key => $value) {	
					if ($keys[$i] == $key) {
						$response[$keys[$i]][1] = ($value['count'] / $response[$keys[$i]][1]) * 100;
						$value_returned = true;
					}					
				}
				if (!$value_returned) {
					$response[$keys[$i]][1] = "0";
				}
			}								
		} else {
			$sqlstr = "SELECT branch.id, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~organisation_branch branch, ~tender_item ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND subscriber.branch = branch.id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.status = 4 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY branch.id ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			// get branch info
			$new_branch = new branch;
			foreach($this->tender as $key => $value) {
				$branch_info = $new_branch->get_info($key,$cfg,$conn);
				if (strlen($branch_info['name']) > 15) {
					$branch_info['name'] = substr($branch_info['name'], 0, 15)."..";
				}				
				$response[$key] = array($branch_info['name'],$value['count'],"0","0","0","0");
			}
			$sqlstr = "SELECT branch.id, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~organisation_branch branch, ~tender_item, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND subscriber.branch = branch.id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 1 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY branch.id ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][2] = $value['count'];
				} else {
					$branch_info = $new_branch->get_info($key,$cfg,$conn);
					if (strlen($branch_info['name']) > 15) {
						$branch_info['name'] = substr($branch_info['name'], 0, 15)."..";
					}					
					$response[$key] = array($branch_info['name'],"0",$value['count'],"0","0","0");
				}
			}			
			$sqlstr = "SELECT branch.id, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~organisation_branch branch, ~tender_item, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND subscriber.branch = branch.id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 2 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY branch.id ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][3] = $value['count'];
				} else {
					$branch_info = $new_branch->get_info($key,$cfg,$conn);
					if (strlen($branch_info['name']) > 15) {
						$branch_info['name'] = substr($branch_info['name'], 0, 15)."..";
					}					
					$response[$key] = array($branch_info['name'],"0","0",$value['count'],"0","0");
				}
			}
			$sqlstr = "SELECT branch.id, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~organisation_branch branch, ~tender_item, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND subscriber.branch = branch.id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 3 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY branch.id, branch.name ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][4] = $value['count'];
				} else {
					$branch_info = $new_branch->get_info($key,$cfg,$conn);
					if (strlen($branch_info['name']) > 15) {
						$branch_info['name'] = substr($branch_info['name'], 0, 15)."..";
					}
					$response[$key] = array($branch_info['name'],"0","0","0",$value['count'],"0");
				}
	
			}
			$sqlstr = "SELECT branch.id, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~organisation_branch branch, ~tender_item, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND subscriber.branch = branch.id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 1 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY branch.id ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][5] = $value['count'];
				} else {
					$branch_info = $new_branch->get_info($key,$cfg,$conn);
					if (strlen($branch_info['name']) > 15) {
						$branch_info['name'] = substr($branch_info['name'], 0, 15)."..";
					}
					$response[$key] = array($branch_info['name'],"0","0","0","0",$value['count']);
				}
	
			}
		}
		return $response;
	}
	
	/**
	 * Get organisation statistics
	 * 
	 * @param array params search information
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param mixed tender_information or FALSE on error  
	 */
	function get_organisation_statistics($params,$cfg,$conn) {
		// set default values an array incorrect order
		$this->user = $params;
		if ($params['yaxis'] == 'average_rfq') {
			$sqlstr = "SELECT ~organisation.id, SUM(~tender_quote.price) / COUNT(~tender_item.id) AS avg ";
			// FROM
			$sqlstr.= "FROM ~tender_item LEFT JOIN ~tender_quote ";
			$sqlstr.= "					 ON	~tender_quote.tender_item = ~tender_item.id ";
			$sqlstr.= "					 AND ~tender_quote.quote_position = 1, ";
			$sqlstr.= "~tender, ~users subscriber, ~organisation ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.classification = 1 ";
			$sqlstr.= "AND subscriber.organisation  = ~organisation.id ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~organisation.id ";
			$sqlstr.= "ORDER BY SUM(~tender_quote.price) / COUNT(~tender_item.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			// get organisation info
			$new_organisation = new organisation;
			foreach($this->tender as $key => $value) {
				if ($value['avg'] != 0) {
					$organisation_info = $new_organisation->get_info($key,$cfg,$conn);
					$response[$key] = array($organisation_info['name'],$value['avg']);
				}
			}
		} elseif ($params['yaxis'] == 'conversion_rate') { 
			$sqlstr = "SELECT ~organisation.id, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~organisation, ~tender_item ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.classification = 1 ";			
			$sqlstr.= "AND subscriber.organisation  = ~organisation.id ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~organisation.id ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			// get organisation info
			$new_organisation = new organisation;
			foreach($this->tender as $key => $value) {
				$organisation_info = $new_organisation->get_info($key,$cfg,$conn);
				$response[$key] = array($organisation_info['name'],$value['count']);
			}
			$sqlstr = "SELECT ~organisation.id, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~organisation, ~tender_item, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item.classification = 1 ";
			$sqlstr.= "AND ~tender_item_complete.status = 1 ";
			$sqlstr.= "AND subscriber.organisation  = ~organisation.id ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~organisation.id ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			$keys = array_keys($response);
			for($i = 0; $i < sizeof($response); $i++) {
				$value_returned = false;
				foreach($this->tender as $key => $value) {	
					if ($keys[$i] == $key) {
						$response[$keys[$i]][1] = ($value['count'] / $response[$keys[$i]][1]) * 100;
						$value_returned = true;
					}					
				}
				if (!$value_returned) {
					$response[$keys[$i]][1] = "0";
				}
			}
		} else {
			$sqlstr = "SELECT ~organisation.id, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~organisation, ~tender_item ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.status = 4 ";
			$sqlstr.= "AND subscriber.organisation  = ~organisation.id ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~organisation.id ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			// get organisation info
			$new_organisation = new organisation;
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][1] = $value['count'];
				} else {
					$organisation_info = $new_organisation->get_info($key,$cfg,$conn);
					$response[$key] = array($organisation_info['name'],$value['count'],"0","0","0","0");
				}
	
			}
			$sqlstr = "SELECT ~organisation.id, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~organisation, ~tender_item, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 1 ";
			$sqlstr.= "AND subscriber.organisation  = ~organisation.id ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~organisation.id ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][2] = $value['count'];
				} else {
					$organisation_info = $new_organisation->get_info($key,$cfg,$conn);
					$response[$key] = array($organisation_info['name'],"0",$value['count'],"0","0","0");
				}
	
			}
			$sqlstr = "SELECT ~organisation.id, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~organisation, ~tender_item, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 2 ";
			$sqlstr.= "AND subscriber.organisation  = ~organisation.id ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~organisation.id ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][3] = $value['count'];
				} else {
					$organisation_info = $new_organisation->get_info($key,$cfg,$conn);
					$response[$key] = array($organisation_info['name'],"0","0",$value['count'],"0","0");
				}
	
			}
			$sqlstr = "SELECT ~organisation.id, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~organisation, ~tender_item, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 3 ";
			$sqlstr.= "AND subscriber.organisation  = ~organisation.id ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~organisation.id ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][4] = $value['count'];
				} else {
					$organisation_info = $new_organisation->get_info($key,$cfg,$conn);
					$response[$key] = array($organisation_info['name'],"0","0","0",$value['count'],"0");
				}
	
			}
			$sqlstr = "SELECT ~organisation.id, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~organisation, ~tender_item, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 4 ";
			$sqlstr.= "AND subscriber.organisation  = ~organisation.id ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~organisation.id ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][5] = $value['count'];
				} else {
					$organisation_info = $new_organisation->get_info($key,$cfg,$conn);
					$response[$key] = array($organisation_info['name'],"0","0","0","0",$value['count']);
				}
	
			}
		}
		return $response;
	}
	
	/**
	 * Get state statistics
	 * 
	 * @param array params search information
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param mixed tender_information or FALSE on error  
	 */
	function get_state_statistics($params,$cfg,$conn) {
		// set default values an array incorrect order
		$this->user = $params;
		if ($params['yaxis'] == 'average_rfq') {
			$sqlstr = "SELECT state.id, state.name, SUM(~tender_quote.price) / COUNT(~tender_item.id) AS avg ";
			// FROM
			$sqlstr.= "FROM ~tender_item LEFT JOIN ~tender_quote ";
			$sqlstr.= "					 ON	~tender_quote.tender_item = ~tender_item.id ";
			$sqlstr.= "					 AND ~tender_quote.quote_position = 1, ";
			$sqlstr.= "~tender, ~users subscriber,  ~area_state state, ~area ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender.area  = ~area.id ";
			$sqlstr.= "AND ~area.state  = state.id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.classification = 1 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY state.id, state.name ";
			$sqlstr.= "ORDER BY SUM(~tender_quote.price) / COUNT(~tender_item.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if ($value['avg'] != 0) {
					$response[$key] = array($value['name'],$value['avg']);
				}
			}
		} elseif ($params['yaxis'] == 'conversion_rate') {
			$sqlstr = "SELECT state.id, state.name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~tender_item, ~area_state state, ~area ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender.area  = ~area.id ";
			$sqlstr.= "AND ~area.state  = state.id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.classification = 1 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY state.id, state.name ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $value) {
				$response[] = array($value['name'],$value['count']);
			}
			$sqlstr = "SELECT state.id, state.name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~tender_item, ~area_state state, ~area, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender.area  = ~area.id ";
			$sqlstr.= "AND ~area.state  = state.id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item.classification = 1 ";
			$sqlstr.= "AND ~tender_item_complete.status = 1 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY state.id, state.name ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			/*foreach($this->tender as $value) {
				$response[] = array($value['name'],$value['count']);
			}*/
			
			for($i = 0; $i < sizeof($response); $i++) {
				$value_returned = false;
				foreach($this->tender as $value) {	
					if ($response[$i][0] == $value['name']) {
						$response[$i][1] = ($value['count'] / $response[$i][1]) * 100;
						$value_returned = true;
					}					
				}
				if (!$value_returned) {
					$response[$i][1] = "0";
				}
			}		
		} else {			
			$sqlstr = "SELECT state.id, state.name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~tender_item, ~area_state state, ~area ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender.area  = ~area.id ";
			$sqlstr.= "AND ~area.state  = state.id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY state.id, state.name ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][1] = $value['count'];
				} else {
					$response[$key] = array($value['name'],$value['count'],"0","0","0","0");
				}
			}
			$sqlstr = "SELECT state.id, state.name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~tender_item, ~area_state state, ~area, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender.area  = ~area.id ";
			$sqlstr.= "AND ~area.state  = state.id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 1 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY state.id, state.name ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][2] = $value['count'];
				} else {
					$response[$key] = array($value['name'],"0",$value['count'],"0","0","0");
				}
	
			}
			$sqlstr = "SELECT state.id, state.name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~tender_item, ~area_state state, ~area, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender.area  = ~area.id ";
			$sqlstr.= "AND ~area.state  = state.id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 2 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY state.id, state.name ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][3] = $value['count'];
				} else {
					$response[$key] = array($value['name'],"0","0",$value['count'],"0","0");
				}
	
			}
			$sqlstr = "SELECT state.id, state.name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~tender_item, ~area_state state, ~area, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender.area  = ~area.id ";
			$sqlstr.= "AND ~area.state  = state.id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 3 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY state.id, state.name ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][4] = $value['count'];
				} else {
					$response[$key] = array($value['name'],"0","0","0",$value['count'],"0");
				}
	
			}
			$sqlstr = "SELECT state.id, state.name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~tender_item, ~area_state state, ~area, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender.area  = ~area.id ";
			$sqlstr.= "AND ~area.state  = state.id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 4 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY state.id, state.name ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][5] = $value['count'];
				} else {
					$response[$key] = array($value['name'],"0","0","0","0",$value['count']);
				}
	
			}
		}
		return $response;
	}

	
	/**
	 * Get area statistics
	 * 
	 * @param array params search information
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param mixed tender_information or FALSE on error  
	 */
	function get_area_statistics($params,$cfg,$conn) {
		// set default values an array incorrect order
		$this->user = $params;
		if ($params['yaxis'] == 'average_rfq') {
			$sqlstr = "SELECT ~area.id, ~area.name, SUM(~tender_quote.price) / COUNT(~tender_item.id) AS avg ";
			// FROM
			$sqlstr.= "FROM ~tender_item LEFT JOIN ~tender_quote ";
			$sqlstr.= "					 ON	~tender_quote.tender_item = ~tender_item.id ";
			$sqlstr.= "					 AND ~tender_quote.quote_position = 1, ";
			$sqlstr.= "~tender, ~users subscriber, ~area ";	
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender.area  = ~area.id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.classification = 1 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~area.id, ~area.name ";
			$sqlstr.= "ORDER BY SUM(~tender_quote.price) / COUNT(~tender_item.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if ($value['avg'] != 0) {					
					if (strlen($value['name']) > 15) {
						$value['name'] = substr($value['name'], 0, 15)."..";
					}
					$response[$key] = array($value['name'],$value['avg']);
				}
			}
		} elseif ($params['yaxis'] == 'conversion_rate') { 
			$sqlstr = "SELECT ~area.id, ~area.name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~tender_item, ~area ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender.area  = ~area.id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.classification = 1 ";			
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~area.id, ~area.name ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $value) {
				if (strlen($value['name']) > 15) {
					$value['name'] = substr($value['name'], 0, 15)."..";
				}
				$response[] = array($value['name'],$value['count']);
			}
			$sqlstr = "SELECT ~area.id, ~area.name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~tender_item, ~area, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender.area  = ~area.id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item.classification = 1 ";			
			$sqlstr.= "AND ~tender_item_complete.status = 1 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~area.id, ~area.name ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			for($i = 0; $i < sizeof($response); $i++) {
				$value_returned = false;
				foreach($this->tender as $value) {	
					if (strlen($value['name']) > 15) {
						$value['name'] = substr($value['name'], 0, 15)."..";
					}				
					if ($response[$i][0] == $value['name']) {
						$response[$i][1] = ($value['count'] / $response[$i][1]) * 100;
						$value_returned = true;
					}					
				}
				if (!$value_returned) {
					$response[$i][1] = "0";
				}
			}						
		} else {
			$sqlstr = "SELECT ~area.id, ~area.name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~tender_item, ~area ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender.area  = ~area.id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.status = 4 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~area.id, ~area.name ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if (strlen($value['name']) > 15) {
					$value['name'] = substr($value['name'], 0, 15)."..";
				}
				if($response[$key]) {
					$response[$key][1] = $value['count'];
				} else {
					$response[$key] = array($value['name'],$value['count'],"0","0","0","0");
				}
	
			}
			$sqlstr = "SELECT ~area.id, ~area.name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~tender_item, ~area, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender.area  = ~area.id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 1 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~area.id, ~area.name ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if (strlen($value['name']) > 15) {
					$value['name'] = substr($value['name'], 0, 15)."..";
				}
				if($response[$key]) {
					$response[$key][2] = $value['count'];
				} else {
					$response[$key] = array($value['name'],"0",$value['count'],"0","0","0");
				}
	
			}
			$sqlstr = "SELECT ~area.id, ~area.name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~tender_item, ~area, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender.area  = ~area.id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 2 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~area.id, ~area.name ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if (strlen($value['name']) > 15) {
					$value['name'] = substr($value['name'], 0, 15)."..";
				}
				if($response[$key]) {
					$response[$key][3] = $value['count'];
				} else {
					$response[$key] = array($value['name'],"0","0",$value['count'],"0","0");
				}
	
			}
			$sqlstr = "SELECT ~area.id, ~area.name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~tender_item, ~area, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender.area  = ~area.id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 3 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~area.id, ~area.name ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if (strlen($value['name']) > 15) {
					$value['name'] = substr($value['name'], 0, 15)."..";
				}				
				if($response[$key]) {
					$response[$key][4] = $value['count'];
				} else {
					$response[$key] = array($value['name'],"0","0","0",$value['count'],"0");
				}
	
			}
			$sqlstr = "SELECT ~area.id, ~area.name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~tender_item, ~area, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender.area  = ~area.id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 4 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~area.id, ~area.name ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if (strlen($value['name']) > 15) {
					$value['name'] = substr($value['name'], 0, 15)."..";
				}				
				if($response[$key]) {
					$response[$key][5] = $value['count'];
				} else {
					$response[$key] = array($value['name'],"0","0","0","0",$value['count']);
				}
	
			}
		}
		return $response;
	}
	
	/**
	 * Get category statistics
	 * 
	 * @param array params search information
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param mixed tender_information or FALSE on error  
	 */
	function get_category_statistics($params,$cfg,$conn) {
		// set default values an array incorrect order
		$this->user = $params;
		if ($params['yaxis'] == 'average_rfq') {
			$sqlstr = "SELECT ~category.id, ~category.name, SUM(~tender_quote.price) / COUNT(~tender_item.id) AS avg ";
			// FROM
			$sqlstr.= "FROM ~tender_item LEFT JOIN ~tender_quote ";
			$sqlstr.= "					 ON	~tender_quote.tender_item = ~tender_item.id ";
			$sqlstr.= "					 AND ~tender_quote.quote_position = 1, ";
			$sqlstr.= "~tender, ~users subscriber, ~category, ~category_sub ";	
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";			
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.category_sub = ~category_sub.id ";
			$sqlstr.= "AND ~category_sub.category = ~category.id ";			
			$sqlstr.= "AND ~tender_item.classification = 1 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~category.id, ~category.name ";
			$sqlstr.= "ORDER BY SUM(~tender_quote.price) / COUNT(~tender_item.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if ($value['avg'] != 0) {
					$response[$key] = array($value['name'],$value['avg']);
				}
			} 
		} elseif ($params['yaxis'] == 'conversion_rate') {
			$sqlstr = "SELECT ~category.id, ~category.name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~tender_item, ~category, ~category_sub ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.classification = 1 ";			
			$sqlstr.= "AND ~tender_item.category_sub = ~category_sub.id ";
			$sqlstr.= "AND ~category_sub.category = ~category.id ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~category.id, ~category.name ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $value) {
				$response[] = array($value['name'],$value['count']);
			}
			$sqlstr = "SELECT ~category.id, ~category.name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~tender_item, ~category, ~category_sub, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item_complete.tender_item = ~tender_item.id ";
			$sqlstr.= "AND ~tender_item.category_sub = ~category_sub.id ";
			$sqlstr.= "AND ~category_sub.category = ~category.id ";
			$sqlstr.= "AND ~tender_item.classification = 1 ";			
			$sqlstr.= "AND ~tender_item_complete.status = 1 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~category.id, ~category.name ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			for($i = 0; $i < sizeof($response); $i++) {
				$value_returned = false;
				foreach($this->tender as $value) {	
					if ($response[$i][0] == $value['name']) {
						$response[$i][1] = ($value['count'] / $response[$i][1]) * 100;
						$value_returned = true;
					}					
				}
				if (!$value_returned) {
					$response[$i][1] = "0";
				}
			}					
		} else { 
			$sqlstr = "SELECT ~category.id, ~category.name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~tender_item, ~category, ~category_sub ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.category_sub = ~category_sub.id ";
			$sqlstr.= "AND ~category_sub.category = ~category.id ";
			$sqlstr.= "AND ~tender_item.status = 4 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~category.id, ~category.name ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][1] = $value['count'];
				} else {
					$response[$key] = array($value['name'],$value['count'],"0","0","0","0");
				}
	
			}
			$sqlstr = "SELECT ~category.id, ~category.name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~tender_item, ~category, ~category_sub, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item_complete.tender_item = ~tender_item.id ";
			$sqlstr.= "AND ~tender_item.category_sub = ~category_sub.id ";
			$sqlstr.= "AND ~category_sub.category = ~category.id ";
			$sqlstr.= "AND ~tender_item_complete.status = 1 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~category.id, ~category.name ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][2] = $value['count'];
				} else {
					$response[$key] = array($value['name'],"0",$value['count'],"0","0","0");
				}
	
			}
			$sqlstr = "SELECT ~category.id, ~category.name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~tender_item, ~category, ~category_sub, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item_complete.tender_item = ~tender_item.id ";
			$sqlstr.= "AND ~tender_item.category_sub = ~category_sub.id ";
			$sqlstr.= "AND ~category_sub.category = ~category.id ";
			$sqlstr.= "AND ~tender_item_complete.status = 2 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~category.id, ~category.name ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][3] = $value['count'];
				} else {
					$response[$key] = array($value['name'],"0","0",$value['count'],"0","0");
				}
	
			}
			$sqlstr = "SELECT ~category.id, ~category.name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~tender_item, ~category, ~category_sub, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item_complete.tender_item = ~tender_item.id ";
			$sqlstr.= "AND ~tender_item.category_sub = ~category_sub.id ";
			$sqlstr.= "AND ~category_sub.category = ~category.id ";
			$sqlstr.= "AND ~tender_item_complete.status = 3 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~category.id, ~category.name ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][4] = $value['count'];
				} else {
					$response[$key] = array($value['name'],"0","0","0",$value['count'],"0");
				}
	
			}
			$sqlstr = "SELECT ~category.id, ~category.name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~tender_item, ~category, ~category_sub, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item_complete.tender_item = ~tender_item.id ";
			$sqlstr.= "AND ~tender_item.category_sub = ~category_sub.id ";
			$sqlstr.= "AND ~category_sub.category = ~category.id ";
			$sqlstr.= "AND ~tender_item_complete.status = 4 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~category.id, ~category.name ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][5] = $value['count'];
				} else {
					$response[$key] = array($value['name'],"0","0","0",$value['count'],"0");
				}
	
			}
		}
		return $response;
	}
	
	/**
	 * Get sub category statistics
	 * 
	 * @param array params search information
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param mixed tender_information or FALSE on error  
	 */
	function get_category_sub_statistics($params,$cfg,$conn) {
		// set default values an array incorrect order
		$this->user = $params;
		if ($params['yaxis'] == 'average_rfq') {
			$sqlstr = "SELECT ~category_sub.id, ~category_sub.name AS name, SUM(~tender_quote.price) / COUNT(~tender_item.id) AS avg ";
			// FROM
			$sqlstr.= "FROM ~tender_item LEFT JOIN ~tender_quote ";
			$sqlstr.= "					 ON	~tender_quote.tender_item = ~tender_item.id ";
			$sqlstr.= "					 AND ~tender_quote.quote_position = 1, ";
			$sqlstr.= "~tender, ~users subscriber, ~category_sub ";	
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";			
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.category_sub = ~category_sub.id ";
			$sqlstr.= "AND ~tender_item.classification = 1 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~category_sub.id, ~category_sub.name ";
			$sqlstr.= "ORDER BY SUM(~tender_quote.price) / COUNT(~tender_item.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if ($value['avg'] != 0) {
					$response[$key] = array($value['name'],$value['avg']);
				}
			}
		} elseif ($params['yaxis'] == 'conversion_rate') {
			$sqlstr = "SELECT ~category_sub.id, ~category_sub.name AS name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~tender_item, ~category_sub ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.classification = 1 ";			
			$sqlstr.= "AND ~tender_item.category_sub = ~category_sub.id ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~category_sub.id, ~category_sub.name ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $value) {			     		
				$response[] = array($value['name'],$value['count']);
			}
			$sqlstr = "SELECT ~category_sub.id, ~category_sub.name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~tender_item, ~category_sub, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item.category_sub = ~category_sub.id ";
			$sqlstr.= "AND ~tender_item.classification = 1 ";			
			$sqlstr.= "AND ~tender_item_complete.status = 1 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~category_sub.id, ~category_sub.name ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}			 
			for($i = 0; $i < sizeof($response); $i++) {
				$value_returned = false;
				foreach($this->tender as $value) {	
					if ($response[$i][0] == $value['name']) {
						$response[$i][1] = ($value['count'] / $response[$i][1]) * 100;
						$value_returned = true;
					}					
				}
				if (!$value_returned) {
					$response[$i][1] = "0";
				}
			}						
		} else {		
			$sqlstr = "SELECT ~category_sub.id, ~category_sub.name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~tender_item, ~category_sub ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.category_sub = ~category_sub.id ";
			$sqlstr.= "AND ~tender_item.status = 4 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~category_sub.id, ~category_sub.name ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][1] = $value['count'];
				} else {
					$response[$key] = array($value['name'],$value['count'],"0","0","0","0");
				}
	
			}
			$sqlstr = "SELECT ~category_sub.id, ~category_sub.name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~tender_item, ~category_sub, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item.category_sub = ~category_sub.id ";
			$sqlstr.= "AND ~tender_item_complete.status = 1 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~category_sub.id, ~category_sub.name ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][2] = $value['count'];
				} else {
					$response[$key] = array($value['name'],"0",$value['count'],"0","0","0");
				}
	
			}
			$sqlstr = "SELECT ~category_sub.id, ~category_sub.name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~tender_item, ~category_sub, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item.category_sub = ~category_sub.id ";
			$sqlstr.= "AND ~tender_item_complete.status = 2 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~category_sub.id, ~category_sub.name ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][3] = $value['count'];
				} else {
					$response[$key] = array($value['name'],"0","0",$value['count'],"0","0");
				}
	
			}
			$sqlstr = "SELECT ~category_sub.id, ~category_sub.name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~tender_item, ~category_sub, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item.category_sub = ~category_sub.id ";
			$sqlstr.= "AND ~tender_item_complete.status = 3 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~category_sub.id, ~category_sub.name ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][4] = $value['count'];
				} else {
					$response[$key] = array($value['name'],"0","0","0",$value['count'],"0");
				}
	
			}
			$sqlstr = "SELECT ~category_sub.id, ~category_sub.name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~users subscriber, ~tender_item, ~category_sub, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item.category_sub = ~category_sub.id ";
			$sqlstr.= "AND ~tender_item_complete.status = 4 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~category_sub.id, ~category_sub.name ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][5] = $value['count'];
				} else {
					$response[$key] = array($value['name'],"0","0","0","0",$value['count']);
				}
			}
		}
		return $response;
	}
	
	/**
	 * Get brand statistics
	 * 
	 * @param array params search information
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param mixed tender_information or FALSE on error  
	 */
	function get_brand_statistics($params,$cfg,$conn) {
		// set default values an array incorrect order
		$this->user = $params;
		if ($params['yaxis'] == 'average_rfq') {
			$sqlstr = "SELECT ~tender_item.brand , ~tender_item.brand AS name, SUM(~tender_quote.price) / COUNT(~tender_item.id) AS avg ";
			// FROM
			$sqlstr.= "FROM ~tender_item LEFT JOIN ~tender_quote ";
			$sqlstr.= "					 ON	~tender_quote.tender_item = ~tender_item.id ";
			$sqlstr.= "					 AND ~tender_quote.quote_position = 1, ";
			$sqlstr.= "~tender, ~users subscriber ";	
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";			
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.classification = 1 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~tender_item.brand ";			
			$sqlstr.= "ORDER BY SUM(~tender_quote.price) / COUNT(~tender_item.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if ($value['avg'] != 0) {
					$response[$key] = array($value['name'],$value['avg']);
				}
			}
		} elseif ($params['yaxis'] == 'conversion_rate') {
			$sqlstr = "SELECT ~tender_item.brand , ~tender_item.brand AS name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~users subscriber ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.classification = 1 ";			
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~tender_item.brand ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $value) {
				$response[] = array($value['name'],$value['count']);
			}	
			$sqlstr = "SELECT ~tender_item.brand , ~tender_item.brand AS name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~users subscriber, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item.classification = 1 ";			
			$sqlstr.= "AND ~tender_item_complete.status = 1 ";			
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~tender_item.brand ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			for($i = 0; $i < sizeof($response); $i++) {
				$value_returned = false;
				foreach($this->tender as $value) {	
					if ($response[$i][0] == $value['name']) {
						$response[$i][1] = ($value['count'] / $response[$i][1]) * 100;
						$value_returned = true;
					}					
				}
				if (!$value_returned) {
					$response[$i][1] = "0";
				}
			}						
		} else {
			$sqlstr = "SELECT ~tender_item.brand , ~tender_item.brand AS name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~users subscriber ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.status = 4 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~tender_item.brand ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][1] = $value['count'];
				} else {
					$response[$key] = array($value['name'],$value['count'],"0","0","0","0");
				}
	
			}
			$sqlstr = "SELECT ~tender_item.brand , ~tender_item.brand AS name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~users subscriber, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 1 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~tender_item.brand ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][2] = $value['count'];
				} else {
					$response[$key] = array($value['name'],"0",$value['count'],"0","0","0");
				}
	
			}
			$sqlstr = "SELECT ~tender_item.brand , ~tender_item.brand AS name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~users subscriber, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 2 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~tender_item.brand ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][3] = $value['count'];
				} else {
					$response[$key] = array($value['name'],"0","0",$value['count'],"0","0");
				}
	
			}
			$sqlstr = "SELECT ~tender_item.brand , ~tender_item.brand AS name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~users subscriber, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 3 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~tender_item.brand ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][4] = $value['count'];
				} else {
					$response[$key] = array($value['name'],"0","0","0",$value['count'],"0");
				}
	
			}
			$sqlstr = "SELECT ~tender_item.brand , ~tender_item.brand AS name, COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~users subscriber, ~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.status = 4 ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY ~tender_item.brand ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAssoc($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			foreach($this->tender as $key => $value) {
				if($response[$key]) {
					$response[$key][5] = $value['count'];
				} else {
					$response[$key] = array($value['name'],"0","0","0","0",$value['count']);
				}
			}
		}
		return $response;
	}
	
	/**
	 * Get supplier statistics
	 * 
	 * @param array params search information
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param mixed tender_information or FALSE on error  
	 */
	function get_supplier_statistics($params,$cfg,$conn) {
		// set default values an array incorrect order
		$this->user = $params;
		if ($params['yaxis'] == 'conversion_rate') {
			$sqlstr = "SELECT supplier.id , COUNT(~tender_item.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~tender_quote, ";
			$sqlstr.= "~users subscriber, ~users agent, ~organisation supplier ";			
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_quote.tender_item ";
			$sqlstr.= "AND ~tender_item.classification = 1 ";			
			$sqlstr.= "AND ~tender_quote.user_id = agent.id ";
			$sqlstr.= "AND agent.organisation = supplier.id ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY supplier.id ";
			$sqlstr.= "ORDER BY COUNT(~tender_item.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAll($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}			
			// get organisation info
			$new_organisation = new organisation;
			foreach($this->tender as $key => $value) {
				$organisation_info = $new_organisation->get_info($value['id'],$cfg,$conn);
				$response[$value['id']] = array($organisation_info['name'],$value['count']);
			}
			$sqlstr = "SELECT supplier.id, COUNT(~tender_item.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~tender_quote, ";
			$sqlstr.= "~users subscriber, ~users agent, ~organisation supplier, ";
			$sqlstr.= "~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.tender_quote = ~tender_quote.id ";
			$sqlstr.= "AND ~tender_item.classification = 1 ";
			$sqlstr.= "AND ~tender_item_complete.status = 1 ";
			$sqlstr.= "AND ~tender_quote.user_id = agent.id ";
			$sqlstr.= "AND agent.organisation = supplier.id ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY supplier.id ";
			$sqlstr.= "ORDER BY COUNT(~tender_item.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAll($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			$keys = array_keys($response);
			for($i = 0; $i < sizeof($response); $i++) {
				$value_returned = false;
				foreach($this->tender as $key => $value) {	
					if ($keys[$i] == $value['id']) {
						$response[$keys[$i]][1] = ($value['count'] / $response[$keys[$i]][1]) * 100;
						$value_returned = true;
					}					
				}
				if (!$value_returned) {
					$response[$keys[$i]][1] = "0";
				}
			}
		} else {
			$sqlstr = "SELECT supplier.id , COUNT(~tender.id) AS count ";
			// FROM
			$sqlstr.= "FROM ~tender, ~tender_item, ~tender_quote, ";
			$sqlstr.= "~users subscriber, ~users agent, ~organisation supplier, ";
			$sqlstr.= "~tender_item_complete ";
			// WHERE
			$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
			$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
			$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
			$sqlstr.= "AND ~tender_item_complete.tender_quote = ~tender_quote.id ";
			$sqlstr.= "AND ~tender_quote.user_id = agent.id ";
			$sqlstr.= "AND agent.organisation = supplier.id ";
			$sqlstr.= "AND ~tender.created_date >= '".$this->user['startdate']."' ";
			$sqlstr.= "AND ~tender.created_date <= '".$this->user['enddate']."' ";
			if($this->user['usersid']) {
				$sqlstr.= "AND ~tender.user_id = ".$this->user['usersid']." ";
			}
			if($this->user['branchid']) {
				$sqlstr.= "AND subscriber.branch = ".$this->user['branchid']." ";
			}
			if($this->user['organisationid']) {
				$sqlstr.= "AND subscriber.organisation = ".$this->user['organisationid']." ";
			}
			$sqlstr.= "GROUP BY supplier.id ";
			$sqlstr.= "ORDER BY COUNT(~tender.id) DESC ";
			$new_database = new database;
			$new_database->parse_sql($sqlstr,$cfg);
			$this->tender = $conn->GetAll($sqlstr,"",TRUE);
			if($conn->ErrorMsg()) {
				// instantiate the error class and log
				$new_error = new error;
				$error_text = "SQL error in file ".__file__." on line ".__line__." Error:".$conn->ErrorMsg();
				$new_error->log("SQL","critical",$error_text,$cfg);
				// error occurred while querying into database 
				$this->error[] = "1065";
				// response
				return FALSE;
			}
			// get organisation info
			$new_organisation = new organisation;
			foreach($this->tender as $value) {
				$organisation_info = $new_organisation->get_info($value['id'],$cfg,$conn);
				$response[] = array($organisation_info['name'],$value['count']);
			}
		}	
		return $response;
	}
}
?>