<?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 appeasubscriberr 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): __________________.                                                   |
// +---------------------------------------------------------------------------------------+

/**
* Procure report class
* @package TenderSystem
* @subpackage procure
* @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 procure_report {
	// set paramaters
	var $user;
	var $tender;
	var $requester;
	var $deliver;
	var $supplier;
	var $agent;
	var $response;
	var $error;
	
	/**
	 * Returns information for group by reports
	 * 
	 * @param array params search information
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param mixed array of tenders or FALSE on error  
	 */
	function search_tender($params,$cfg,$conn) {
		$this->tender = $params;
		$sqlstr = "SELECT DISTINCT ~tender.id AS tenderid, ~tender.rfq_number, ~tender.rfq_reference, ";
		$sqlstr.= "~tender.created_date, ~tender.created_time, ~tender.status, ";
		$sqlstr.= "~tender.close_date, ~tender.close_time, ";
		$sqlstr.= "~users.id AS usersid, ";
		$sqlstr.= " ~area.name AS areaname ";
		// FROM
		$sqlstr.= "FROM ~tender, ~users, ~organisation_branch branch, ";
		$sqlstr.= "~organisation, ~area, ~category_sub ";
		// WHERE
		$sqlstr.= "WHERE ~tender.rfq_number like '%".$this->tender['searchinfo']['rfqnumber']."%' ";
		$sqlstr.= "AND ~tender.rfq_reference like '%".$this->tender['searchinfo']['rfqreference']."%' ";
		$sqlstr.= "AND ~tender.user_id = ~users.id ";
		$sqlstr.= "AND ~users.status > 0 ";
		$sqlstr.= "AND ~users.branch = branch.id ";
		$sqlstr.= "AND branch.status = '1' ";
		$sqlstr.= "AND ~users.organisation = ~organisation.id ";
		$sqlstr.= "AND ~organisation.status = '1' ";
		$sqlstr.= "AND ~tender.area = ~area.id ";
		$sqlstr.= "AND ~area.status = '1' ";
		$sqlstr.= "AND ~category_sub.status = '1' ";
		if($this->tender['searchinfo']['tenderstatus'] || $this->tender['searchinfo']['tenderstatus'] === "0") {
			$sqlstr.= "AND ~tender.status = ".$this->tender['searchinfo']['tenderstatus']." ";
		}
		if($this->tender['searchinfo']['tenderid']) {
			$sqlstr.= "AND ~tender.id = ".$this->tender['searchinfo']['tenderid']." ";
		}
		if($this->tender['searchinfo']['tenderclassification']) {
			$sqlstr.= "AND ~tender.classification = '".$this->tender['searchinfo']['tenderclassification']."' ";
		}
		if($this->tender['searchinfo']['usersid']) {
			$sqlstr.= "AND ~users.id = ".$this->tender['searchinfo']['usersid']." ";
		}
		if($this->tender['searchinfo']['branchid']) {
			$sqlstr.= "AND ~users.branch = ".$this->tender['searchinfo']['branchid']." ";
		}
		if($this->tender['searchinfo']['organisationid']) {
			$sqlstr.= "AND ~users.organisation = ".$this->tender['searchinfo']['organisationid']." ";
		}
		if($this->tender['searchinfo']['areaid']) {
			$sqlstr.= "AND ~area.id = ".$this->tender['searchinfo']['areaid']." ";
		}
		if($this->tender['searchinfo']['stateid']) {
			$sqlstr.= "AND ~area.state = ".$this->tender['searchinfo']['stateid']." ";
		}
		if($this->tender['searchinfo']['startdate']) {
			$sqlstr.= "AND ~tender.close_date >= '".$this->tender['searchinfo']['startdate']."' ";
		}
		if($this->tender['searchinfo']['enddate']) {
			$sqlstr.= "AND ~tender.close_date <= '".$this->tender['searchinfo']['enddate']."' ";
		}
		// ORDER BY
		$sqlstr.= " ORDER BY ~tender.close_date DESC, ~tender.close_time DESC, ~tender.id DESC ";
		$new_database = new database;
		$new_database->parse_sql($sqlstr,$cfg);
		$all = $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;
		}
		// change time to proper format
		$new_database->convertTime($all,$cfg);
		// get user info
		$new_users = new user;
		foreach($all as $key => $value) {
			$users_info = $new_users->get_info($value['usersid'],$cfg,$conn);
			$all[$key]['name'] = $users_info['name'];
			$all[$key]['surname'] = $users_info['surname'];
			$all[$key]['phone_prefix'] = $users_info['phone_prefix'];
			$all[$key]['email'] = $users_info['email'];
			$all[$key]['phone_work'] = $users_info['phone_work'];
		}
		return $all;
	}

	/**
	 * Returns information for group by quote report
	 * 
	 * @param array params search information
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param mixed array of tenders or FALSE on error  
	 */
	function search_agent($params,$cfg,$conn) {
		$this->tender = $params;
		$sqlstr = "SELECT ~tender_quote.user_id AS usersid,  ";
		$sqlstr.= "~tender.id AS tenderid,  ";
 		$sqlstr.= "SUM(~tender_quote.price) AS total ";
		// FROM
		$sqlstr.= "FROM ~tender_quote, ~tender_item, ~tender, ~users, ~organisation_branch, ~organisation ";
		// WHERE
		$sqlstr.= "WHERE ~tender_quote.tender_item = ~tender_item.id ";
		$sqlstr.= "AND ~tender_item.status > 3 ";
		$sqlstr.= "AND ~tender.id = ~tender_item.tender ";
		$sqlstr.= "AND ~tender_quote.status > 3 ";
		$sqlstr.= "AND ~users.id = ~tender_quote.user_id ";
		$sqlstr.= "AND ~users.status > 0 ";
		$sqlstr.= "AND ~users.branch = ~organisation_branch.id ";
		$sqlstr.= "AND ~organisation_branch.status = '1' ";
		$sqlstr.= "AND ~users.organisation = ~organisation.id ";
		$sqlstr.= "AND ~organisation.status = '1' ";		
		if($this->tender['searchinfo']['tenderstatus']) {
			$sqlstr.= "AND ~tender_item.status = '".$this->tender['searchinfo']['tenderstatus']."' ";
		}
		if($this->tender['searchinfo']['tenderid']) {
			$sqlstr.= "AND ~tender.id = ".$this->tender['searchinfo']['tenderid']." ";
		}
		if(is_array($this->tender['searchinfo']['tenderids'])) {
			if(count($this->tender['searchinfo']['tenderids']) > 1) {
				$sqlstr.= "AND ( ~tender.id = ".$this->tender['searchinfo']['tenderids'][0]." ";
				for($i = 1; $i < count($this->tender['searchinfo']['tenderids']); $i++) {
					$sqlstr.= "OR ~tender.id = ".$this->tender['searchinfo']['tenderids'][$i]." ";
				}
				$sqlstr.= ") ";
			} elseif(count($this->tender['searchinfo']['tenderids']) == 1) {
				$sqlstr.= "AND ~tender.id = ".$this->tender['searchinfo']['tenderids'][0]." ";
			}
		}
		if($this->tender['searchinfo']['tenderclassification']) {
			$sqlstr.= "AND ~tender.classification = '".$this->tender['searchinfo']['tenderclassification']."' ";
		}
		if($this->tender['searchinfo']['tenderid']) {
			$sqlstr.= "AND ~tender_item.tender = '".$this->tender['searchinfo']['tenderid']."' ";
		}
		if($this->tender['searchinfo']['tenderitemid']) {
			$sqlstr.= "AND ~tender_item.id = '".$this->tender['searchinfo']['tenderitemid']."' ";
		}
		if($this->tender['searchinfo']['agentid']) {
			$sqlstr.= "AND ~tender_quote.user_id = '".$this->tender['searchinfo']['agentid']."' ";
		}
		if($this->tender['searchinfo']['usersid']) {
			$sqlstr.= "AND ~tender.user_id = '".$this->tender['searchinfo']['usersid']."' ";
		}
		// GROUP BY
		$sqlstr.= "GROUP BY ~tender_quote.user_id, ~tender.id ";
		// ORDER BY
		$sqlstr.= "ORDER BY ~tender.id ";
		$new_database = new database;
		$new_database->parse_sql($sqlstr,$cfg);
		$all = $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()." SQL:".$sqlstr;
			$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($all as $key => $value) {
			$users_info = $new_users->get_profile($value['usersid'],$cfg,$conn);
			$all[$key]['name'] = $users_info['name'];
			$all[$key]['surname'] = $users_info['surname'];
			$all[$key]['phone_prefix'] = $users_info['phone_prefix'];
			$all[$key]['email'] = $users_info['email'];
			$all[$key]['phone_work'] = $users_info['phone_work'];
			$all[$key]['phone_mobile'] = $users_info['phone_mobile'];
			$all[$key]['organisationname'] = $users_info['organisationname'];
			$all[$key]['organisationid'] = $users_info['organisationid'];
			$all[$key]['branchname'] = $users_info['branchname'];
			$all[$key]['branchid'] = $users_info['branchid'];
		}
		return $all;
	}
	
	/**
	 * Returns information for group by item report
	 * 
	 * @param array params search information
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param mixed array of tenders or FALSE on error  
	 */
	function search_tender_item($params,$cfg,$conn) {
		$this->tender = $params;
		$sqlstr = "SELECT ~tender_item.id AS tenderitemid, ~tender_item.tender, ";
		$sqlstr.= "~tender_item.brand, ~tender_item.model, ~tender_item.description, ";
		$sqlstr.= "~tender_item.status AS tenderitemstatus, ";
		$sqlstr.= "~tender_item.classification, ";
		$sqlstr.= "~organisation.id AS organisationid, ";
		$sqlstr.= "~tender.classification AS tenderclassification ";
		// FROM
		$sqlstr.= "FROM ~tender_item, ~users, ~organisation_branch branch, ~organisation, ~tender ";
		// WHERE
		$sqlstr.= "WHERE (~tender_item.status = 4 OR ~tender_item.status = 6) ";
		$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
		$sqlstr.= "AND ~tender_item.classification = '1' ";
		$sqlstr.= "AND ~tender.user_id = ~users.id ";
		$sqlstr.= "AND (~users.status = '1' OR  ~users.status = '2') ";
		$sqlstr.= "AND ~users.branch = branch.id ";
		$sqlstr.= "AND branch.status = '1' ";
		$sqlstr.= "AND ~users.organisation = ~organisation.id ";
		$sqlstr.= "AND ~organisation.status = '1' ";
		if($this->tender['searchinfo']['tenderstatus']) {
			$sqlstr.= "AND ~tender_item.status = '".$this->tender['searchinfo']['tenderstatus']."' ";
		}
		if($this->tender['searchinfo']['tenderitemid']) {
			$sqlstr.= "AND ~tender_item.id = '".$this->tender['searchinfo']['tenderitemid']."' ";
		}
		if($this->tender['searchinfo']['tenderid']) {
			$sqlstr.= "AND ~tender_item.tender = '".$this->tender['searchinfo']['tenderid']."' ";
		}
		if(is_array($this->tender['searchinfo']['tenderids'])) {
			if(count($this->tender['searchinfo']['tenderids']) > 1) {
				$sqlstr.= "AND ( ~tender_item.tender = ".$this->tender['searchinfo']['tenderids'][0]." ";
				for($i = 1; $i < count($this->tender['searchinfo']['tenderids']); $i++) {
					$sqlstr.= "OR ~tender_item.tender = ".$this->tender['searchinfo']['tenderids'][$i]." ";
				}
				$sqlstr.= ") ";
			} elseif(count($this->tender['searchinfo']['tenderids']) == 1) {
				$sqlstr.= "AND ~tender_item.tender = ".$this->tender['searchinfo']['tenderids'][0]." ";
			}
		}		
		if(is_array($this->tender['searchinfo']['classificatons'])) {
			if(count($this->tender['searchinfo']['classificaton']) > 1) {
				$sqlstr.= "AND ( ~tender_item.classificaton = ".$this->tender['searchinfo']['classificatons'][0]." ";
				for($i = 1; $i < count($this->tender['searchinfo']['tenderids']); $i++) {
					$sqlstr.= "OR ~tender_item.classificaton = ".$this->tender['searchinfo']['classificatons'][$i]." ";
				}
				$sqlstr.= ") ";
			} elseif(count($this->tender['searchinfo']['classificatons']) == 1) {
				$sqlstr.= "AND ~tender_item.classification = ".current($this->tender['searchinfo']['classificatons'])." ";
			}
		}
		// ORDER BY
		$sqlstr.= "ORDER BY ~tender_item.id, ~tender_item.tender, ";
		$sqlstr.= "~tender_item.brand, ~tender_item.model, ~tender_item.description, ";
		$sqlstr.= "~tender_item.status, ~tender_item.classification, ";
		$sqlstr.= "~organisation.id, ~tender.classification ";
		$new_database = new database;
		$new_database->parse_sql($sqlstr,$cfg);
		$all = $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;
		}
		
		return $all;
	}


	/**
	 * Returns information for group by price report
	 * 
	 * @param array params search information
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param mixed array of tenders or FALSE on error  
	 */
	function search_tender_quote_position($params,$cfg,$conn) {
		$this->tender = $params;
		$sqlstr = "SELECT DISTINCT ~tender_quote.quote_position, ~tender.id AS tenderid ";
		// FROM
		$sqlstr.= "FROM ~tender_quote, ~tender_item, ~tender, ~users, ~organisation_branch, ~organisation ";
		// WHERE
		$sqlstr.= "WHERE ~tender_quote.tender_item = ~tender_item.id ";
		$sqlstr.= "AND ~tender_item.status > 3 ";
		$sqlstr.= "AND ~tender.id = ~tender_item.tender ";
		$sqlstr.= "AND ~tender_quote.status > 3 ";
		$sqlstr.= "AND ~users.id = ~tender_quote.user_id ";
		$sqlstr.= "AND ~users.status > 0 ";
		$sqlstr.= "AND ~users.branch = ~organisation_branch.id ";
		$sqlstr.= "AND ~organisation_branch.status = '1' ";
		$sqlstr.= "AND ~users.organisation = ~organisation.id ";
		$sqlstr.= "AND ~organisation.status = '1' ";
		if($this->tender['searchinfo']['tenderstatus']) {
			$sqlstr.= "AND ~tender_item.status = '".$this->tender['searchinfo']['tenderstatus']."' ";
		}
		if($this->tender['searchinfo']['tenderitemid']) {
			$sqlstr.= "AND ~tender_item.id = '".$this->tender['searchinfo']['tenderitemid']."' ";
		}
		if($this->tender['searchinfo']['tenderid']) {
			$sqlstr.= "AND ~tender_item.tender = '".$this->tender['searchinfo']['tenderid']."' ";
		}
		if(is_array($this->tender['searchinfo']['tenderids'])) {
			if(count($this->tender['searchinfo']['tenderids']) > 1) {
				$sqlstr.= "AND ( ~tender_item.tender = ".$this->tender['searchinfo']['tenderids'][0]." ";
				for($i = 1; $i < count($this->tender['searchinfo']['tenderids']); $i++) {
					$sqlstr.= "OR ~tender_item.tender = ".$this->tender['searchinfo']['tenderids'][$i]." ";
				}
				$sqlstr.= ") ";
			} elseif(count($this->tender['searchinfo']['tenderids']) == 1) {
				$sqlstr.= "AND ~tender_item.tender = ".$this->tender['searchinfo']['tenderids'][0]." ";
			}
		}		
		if(is_array($this->tender['searchinfo']['classificatons'])) {
			if(count($this->tender['searchinfo']['classificaton']) > 1) {
				$sqlstr.= "AND ( ~tender_item.classificaton = ".$this->tender['searchinfo']['classificatons'][0]." ";
				for($i = 1; $i < count($this->tender['searchinfo']['tenderids']); $i++) {
					$sqlstr.= "OR ~tender_item.classificaton = ".$this->tender['searchinfo']['classificatons'][$i]." ";
				}
				$sqlstr.= ") ";
			} elseif(count($this->tender['searchinfo']['classificatons']) == 1) {
				$sqlstr.= "AND ~tender_item.classification = ".current($this->tender['searchinfo']['classificatons'])." ";
			}
		}
		// GROUP BY
		$sqlstr.= "GROUP BY ~tender_quote.quote_position, ~tender.id, ~tender_quote.user_id ";
		// ORDER BY
		$sqlstr.= "ORDER BY ~tender_quote.quote_position, ~tender.id ";
		$new_database = new database;
		$new_database->parse_sql($sqlstr,$cfg);
		$all = $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;
		}
		
		return $all;
	}


	/**
	 * Returns information for group by quote report
	 * 
	 * @param array params search information
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param mixed array of tenders or FALSE on error  
	 */
	function search_quoted($params,$cfg,$conn) {
		$this->tender = $params;
		$sqlstr = "SELECT ~tender_quote.id, ~tender_quote.user_id, ~tender_quote.tender_item, ";
		$sqlstr.= "~tender_quote.brand, ~tender_quote.model, ~tender_quote.description, ";
		$sqlstr.= "~tender_quote.stock, ~tender_quote.price, ~tender_quote.status AS quotestatus, ";
		$sqlstr.= "~tender_quote.quote_position, ~tender_quote.valid_until,  ~tender_quote.classification AS quoteclass, ";
		$sqlstr.= "~users.id AS usersid, ";
		$sqlstr.= "~organisation.id AS organisationid, ";
		$sqlstr.= "~tender_item.tender, ~tender_item.status, ~tender_item.classification, ";
		$sqlstr.= "~tender.classification AS tenderclassification ";
		// FROM
		$sqlstr.= "FROM ~tender_item, ~tender_quote, ~users, ~organisation_branch branch, ~organisation, ~tender ";
		// WHERE
		$sqlstr.= "WHERE (~tender_quote.status = 4 OR ~tender_quote.status = 6) ";
		$sqlstr.= "AND ~tender_quote.tender_item = ~tender_item.id ";
		$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
		$sqlstr.= "AND ~tender_quote.user_id = ~users.id ";
		$sqlstr.= "AND (~users.status = '1' OR  ~users.status = '2') ";
		$sqlstr.= "AND ~users.branch = branch.id ";
		$sqlstr.= "AND branch.status = '1' ";
		$sqlstr.= "AND ~users.organisation = ~organisation.id ";
		$sqlstr.= "AND ~organisation.status = '1' ";
		if($this->tender['searchinfo']['agentid']) {
			$sqlstr.= "AND ~tender_quote.user_id = '".$this->tender['searchinfo']['agentid']."' ";
		}
		if($this->tender['searchinfo']['tenderstatus']) {
			$sqlstr.= "AND ~tender_item.status = '".$this->tender['searchinfo']['tenderstatus']."' ";
		}
		if($this->tender['searchinfo']['tender_quoteid']) {
			$sqlstr.= "AND ~tender_quote.id = '".$this->tender['searchinfo']['tender_quoteid']."' ";
		}
		if($this->tender['searchinfo']['tenderitemid']) {
			$sqlstr.= "AND ~tender_quote.tender_item = '".$this->tender['searchinfo']['tenderitemid']."' ";
		}
		if($this->tender['searchinfo']['tenderid']) {
			$sqlstr.= "AND ~tender_item.tender = '".$this->tender['searchinfo']['tenderid']."' ";
		}
		if(is_array($this->tender['searchinfo']['tenderids'])) {
			if(count($this->tender['searchinfo']['tenderids']) > 1) {
				$sqlstr.= "AND ( ~tender_item.tender = ".$this->tender['searchinfo']['tenderids'][0]." ";
				for($i = 1; $i < count($this->tender['searchinfo']['tenderids']); $i++) {
					$sqlstr.= "OR ~tender_item.tender = ".$this->tender['searchinfo']['tenderids'][$i]." ";
				}
				$sqlstr.= ") ";
			} elseif(count($this->tender['searchinfo']['tenderids']) == 1) {
				$sqlstr.= "AND ~tender_item.tender = ".$this->tender['searchinfo']['tenderids'][0]." ";
			}
		}		
		if(is_array($this->tender['searchinfo']['tender_quoteids'])) {
			if(count($this->tender['searchinfo']['tender_quoteids']) > 1) {
				$sqlstr.= "AND ( ~tender_quote.id = ".$this->tender['searchinfo']['tender_quoteids'][0]." ";
				for($i = 1; $i < count($this->tender['searchinfo']['tender_quoteids']); $i++) {
					$sqlstr.= "OR ~tender_quote.id = ".$this->tender['searchinfo']['tender_quoteids'][$i]." ";
				}
				$sqlstr.= ") ";
			} elseif(count($this->tender['searchinfo']['tender_quoteids']) == 1) {
				$sqlstr.= "AND ~tender_quote.id = ".$this->tender['searchinfo']['tender_quoteids'][0]." ";
			}
		}
		if(is_array($this->tender['searchinfo']['classificatons'])) {
			if(count($this->tender['searchinfo']['classificaton']) > 1) {
				$sqlstr.= "AND ( ~tender_item.classificaton = ".$this->tender['searchinfo']['classificatons'][0]." ";
				for($i = 1; $i < count($this->tender['searchinfo']['tenderids']); $i++) {
					$sqlstr.= "OR ~tender_item.classificaton = ".$this->tender['searchinfo']['classificatons'][$i]." ";
				}
				$sqlstr.= ") ";
			} elseif(count($this->tender['searchinfo']['classificatons']) == 1) {
				$sqlstr.= "AND ~tender_item.classification = ".current($this->tender['searchinfo']['classificatons'])." ";
			}
		}
		// ORDER BY
		$sqlstr.= "ORDER BY ~tender_quote.status ASC, ~tender_item.classification ASC, ~tender_item.id ASC, ~tender_quote.price ASC, ";
		$sqlstr.= "~tender_quote.quote_position ASC, ~tender_quote.id ASC ";
		$new_database = new database;
		$new_database->parse_sql($sqlstr,$cfg);
		$all = $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;
		}
		$time = strtotime(date("Y-m-d",ts_time($cfg)));
		// change time to proper format
		$new_database->convertTime($all,$cfg);
		foreach($all as $key => $value) {
			if(strtotime($value['valid_until']) < $time) {
				$all[$key]['expire'] = "1";
			}
		}
		return $all;
	}
	
	/**
	 * Returns information for group by item report
	 * 
	 * @param array params search information
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param mixed array of tenders or FALSE on error  
	 */
	function search_supplier_quoted($params,$cfg,$conn) {
		$this->tender = $params;
		$sqlstr = "SELECT ~tender_quote.id, ~tender_quote.tender_item,  ";
		$sqlstr.= "~tender_quote.brand, ~tender_quote.model, ~tender_quote.description, ";
		$sqlstr.= "~tender_quote.stock, ~tender_quote.price, ~tender_quote.status AS quotestatus, ";
		$sqlstr.= "~tender_quote.quote_position, ~tender_quote.valid_until,  ~tender_quote.classification AS quoteclass, ";
		$sqlstr.= "~users.id AS usersid, ";
		$sqlstr.= "~organisation.id AS organisationid, ";
		$sqlstr.= "~tender_item.tender, ~tender_item.status, ~tender_item.classification, ";
		$sqlstr.= "~tender.classification AS tenderclassification ";
		// FROM
		$sqlstr.= "FROM ~tender_item, ~tender_quote, ~users, ~organisation_branch branch, ~organisation, ~tender ";
		// WHERE
		$sqlstr.= "WHERE (~tender_quote.status = 4 OR ~tender_quote.status = 6) ";
		$sqlstr.= "AND ~tender_quote.tender_item = ~tender_item.id ";
		$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
		$sqlstr.= "AND ~tender_quote.user_id = ~users.id ";
		$sqlstr.= "AND (~users.status = '1' OR  ~users.status = '2') ";
		$sqlstr.= "AND ~users.branch = branch.id ";
		$sqlstr.= "AND branch.status = '1' ";
		$sqlstr.= "AND ~users.organisation = ~organisation.id ";
		$sqlstr.= "AND ~organisation.status = '1' ";
		if($this->tender['searchinfo']['agentid']) {
			$sqlstr.= "AND ~tender_quote.user_id = '".$this->tender['searchinfo']['agentid']."' ";
		}
		if($this->tender['searchinfo']['tenderstatus']) {
			$sqlstr.= "AND ~tender_item.status = '".$this->tender['searchinfo']['tenderstatus']."' ";
		}
		if($this->tender['searchinfo']['tender_quoteid']) {
			$sqlstr.= "AND ~tender_quote.id = '".$this->tender['searchinfo']['tender_quoteid']."' ";
		}
		if($this->tender['searchinfo']['tenderitemid']) {
			$sqlstr.= "AND ~tender_quote.tender_item = '".$this->tender['searchinfo']['tenderitemid']."' ";
		}
		if($this->tender['searchinfo']['tenderid']) {
			$sqlstr.= "AND ~tender_item.tender = '".$this->tender['searchinfo']['tenderid']."' ";
		}
		if(is_array($this->tender['searchinfo']['tenderids'])) {
			if(count($this->tender['searchinfo']['tenderids']) > 1) {
				$sqlstr.= "AND ( ~tender_item.tender = ".$this->tender['searchinfo']['tenderids'][0]." ";
				for($i = 1; $i < count($this->tender['searchinfo']['tenderids']); $i++) {
					$sqlstr.= "OR ~tender_item.tender = ".$this->tender['searchinfo']['tenderids'][$i]." ";
				}
				$sqlstr.= ") ";
			} elseif(count($this->tender['searchinfo']['tenderids']) == 1) {
				$sqlstr.= "AND ~tender_item.tender = ".$this->tender['searchinfo']['tenderids'][0]." ";
			}
		}		
		if(is_array($this->tender['searchinfo']['tender_quoteids'])) {
			if(count($this->tender['searchinfo']['tender_quoteids']) > 1) {
				$sqlstr.= "AND ( ~tender_quote.id = ".$this->tender['searchinfo']['tender_quoteids'][0]." ";
				for($i = 1; $i < count($this->tender['searchinfo']['tender_quoteids']); $i++) {
					$sqlstr.= "OR ~tender_quote.id = ".$this->tender['searchinfo']['tender_quoteids'][$i]." ";
				}
				$sqlstr.= ") ";
			} elseif(count($this->tender['searchinfo']['tender_quoteids']) == 1) {
				$sqlstr.= "AND ~tender_quote.id = ".$this->tender['searchinfo']['tender_quoteids'][0]." ";
			}
		}
		if(is_array($this->tender['searchinfo']['classificatons'])) {
			if(count($this->tender['searchinfo']['classificaton']) > 1) {
				$sqlstr.= "AND ( ~tender_item.classificaton = ".$this->tender['searchinfo']['classificatons'][0]." ";
				for($i = 1; $i < count($this->tender['searchinfo']['tenderids']); $i++) {
					$sqlstr.= "OR ~tender_item.classificaton = ".$this->tender['searchinfo']['classificatons'][$i]." ";
				}
				$sqlstr.= ") ";
			} elseif(count($this->tender['searchinfo']['classificatons']) == 1) {
				$sqlstr.= "AND ~tender_item.classification = ".current($this->tender['searchinfo']['classificatons'])." ";
			}
		}
		// ORDER BY
		$sqlstr.= "ORDER BY ~tender_quote.status ASC, ~tender_item.classification ASC, ~tender_item.id ASC, ~tender_quote.price ASC, ";
		$sqlstr.= "~tender_quote.quote_position ASC, ~tender_quote.id ASC ";
		$new_database = new database;
		$new_database->parse_sql($sqlstr,$cfg);
		$all = $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;
		}
		$time = strtotime(date("Y-m-d",ts_time($cfg)));
		// change time to proper format
		$new_database->convertTime($all,$cfg);
		foreach($all as $key => $value) {
			if(strtotime($value['valid_until']) < $time) {
				$all[$key]['expire'] = "1";
			}
		}
		// get user info
		$new_users = new user;
		foreach($all as $key => $value) {
			$users_info = $new_users->get_profile($value['usersid'],$cfg,$conn);
			$all[$key]['name'] = $users_info['name'];
			$all[$key]['surname'] = $users_info['surname'];
			$all[$key]['phone_prefix'] = $users_info['phone_prefix'];
			$all[$key]['email'] = $users_info['email'];
			$all[$key]['phone_work'] = $users_info['phone_work'];
			$all[$key]['phone_mobile'] = $users_info['phone_mobile'];
			$all[$key]['organisationname'] = $users_info['organisationname'];
			$all[$key]['organisationid'] = $users_info['organisationid'];
			$all[$key]['branchname'] = $users_info['branchname'];
			$all[$key]['branchid'] = $users_info['branchid'];
		}
		return $all;
	}
}
?>