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

/**
* User profile class
* @package TenderSystem
* @subpackage overview
* @copyright Copyright &copy; 2002, ValueCard (Pty) Ltd
* @license TenderSystem Commercial 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 user_profile {
	var $tender;
	var $error;
	
	function user_profile() {
		// allow php5 to work
		$this->error = array();	
	}

	//get uncompleted RFQs for each a subscriber, or branch, or organisation
	function get_uncompleted_rfqs($params,$cfg,$conn) {
		$usersid = $params['usersid'];
		$branchid = $params['branchid'];
		$organisationid = $params['organisationid'];
		if(!$usersid) {
			// instantiate the error class and log
			$new_userid_error = new error;
			$error_text = "Error: Missing user id";
			$new_userid_error->log("User id","critical",$error_text,$cfg);
			// error occurred while querying into database 
			//$this->error[] = "1065";
			// response
			return FALSE;
		}
		$startdate = $params['startdate'];
		$enddate  = $params['enddate'];
		if (!$startdate) {
			$startdate = date("Y-m-d",ts_time($cfg) - 10800);		
		}
		if (!$enddate) {
			$enddate = date("Y-m-d");	
		}					
		// generate the sql query
		$sqlstr = "SELECT ~tender.id, SUM(~tender_quote.price) ";
		// 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 subscriber.id = ~tender.user_id ";
		$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
		$sqlstr.= "AND ~tender_item.classification = 1 ";
		$sqlstr.= "AND ~tender.status = 4 ";		
		$sqlstr.= "AND ~tender.created_date >= '".$startdate."' ";
		$sqlstr.= "AND ~tender.created_date <= '".$enddate."' ";
		if($usersid) {
			$sqlstr.= "AND ~tender.user_id = ".$usersid." ";
		}
		if($branchid) {
			$sqlstr.= "AND subscriber.branch = ".$branchid." ";
		}
		if($organisationid) {
			$sqlstr.= "AND subscriber.organisation = ".$organisationid." ";
		}
		$sqlstr.= "GROUP BY ~tender.id ";
		$sqlstr.= "ORDER BY ~tender.id ";
		$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;
		}
		return $this->tender;
	}

	//get total number of RFQs for an agent
	function get_rfqs_for_agent($params,$cfg,$conn) {	
		$usersid = $params['usersid'];
		$itemid = $params['itemid'];
		$startdate = $params['startdate'];
		$enddate  = $params['enddate'];
		if(!$usersid && !$itemid) {
			// instantiate the error class and log
			$new_userid_error = new error;
			$error_text = "Error: Missing user id";
			$new_userid_error->log("User id","critical",$error_text,$cfg);
			// error occurred while querying into database 
			//$this->error[] = "1065";
			// response
			return FALSE;
		}
		$sqlstr = "SELECT ~tender_request.id, ~tender_request.user_id "; 
		// FROM
		$sqlstr .= "FROM ~tender_request LEFT JOIN ~tender_quote "; 
		$sqlstr .= "					 ON ~tender_quote.tender_item = ~tender_request.tender_item "; 
		if($usersid) {
			$sqlstr.= "			  		 AND ~tender_quote.user_id = ".$usersid." ";
			$sqlstr .= "				 AND ~tender_quote.price != 0, ";
		} else {
			$sqlstr .= "				 AND ~tender_quote.price != 0, ";
		}
		$sqlstr .= "~tender_item ";
		// WHERE
		$sqlstr .= "WHERE ~tender_request.tender_item = ~tender_item.id "; 
		$sqlstr .= "AND ~tender_item.classification = 1 ";
		if($usersid) {
			$sqlstr.= "AND ~tender_request.user_id = ".$usersid." ";
		}		
		if($itemid) {
			$sqlstr.= "AND ~tender_request.tender_item = ".$itemid." ";
		}		
		$sqlstr .= "AND ~tender_request.created_date >= '".$startdate."' ";
		$sqlstr .= "AND ~tender_request.created_date <= '".$enddate."' ";
		$sqlstr .= "GROUP BY ~tender_request.id, ~tender_request.user_id "; 
		$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;
		}
		return $this->tender;
	}

	//get total number of RFQs for a subscriber, or branch, or organisation
	function get_total_rfqs($params,$cfg,$conn) {
		$usersid = $params['usersid'];
		$branchid = $params['branchid'];
		$organisationid = $params['organisationid'];		
		if(!$usersid) {
			// instantiate the error class and log
			$new_userid_error = new error;
			$error_text = "Error: Missing user id";
			$new_userid_error->log("User id","critical",$error_text,$cfg);
			// error occurred while querying into database 
			//$this->error[] = "1065";
			// response
			return FALSE;
		}
		$startdate = $params['startdate'];
		$enddate  = $params['enddate'];
		if (!$startdate) {
			$startdate = date("Y-m-d",ts_time($cfg) - 10800);		
		}
		if (!$enddate) {
			$enddate = date("Y-m-d");	
		}		
		$status = $params['status'];		
		$sqlstr = "SELECT ~organisation.id, ~tender.id ";
		// 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 ";
		if ($status) {
			if ($status == 'closed') {
				$sqlstr.= "AND ~tender_item.status > 3 ";
			} elseif ($status == 'open') {
				$sqlstr.= "AND ~tender_item.status = 2 ";
			}
		}
		$sqlstr.= "AND subscriber.organisation = ~organisation.id ";
		$sqlstr.= "AND ~tender.status != 0 ";
		$sqlstr.= "AND ~tender.created_date >= '".$startdate."' ";
		$sqlstr.= "AND ~tender.created_date <= '".$enddate."' ";
		if($usersid) {
			$sqlstr.= "AND ~tender.user_id = ".$usersid." ";
		}
		if($branchid) {
			$sqlstr.= "AND subscriber.branch = ".$branchid." ";
		}
		if($organisationid) {
			$sqlstr.= "AND subscriber.organisation = ".$organisationid." ";
		}
		$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;
		}
		return $this->tender;
	}

	// get total number and value of items for a subscriber, or branch, or organisation
	function get_total_items($params,$cfg,$conn) {
		$usersid = $params['usersid'];
		$branchid = $params['branchid'];
		$organisationid = $params['organisationid'];
		if(!$usersid) {
			// instantiate the error class and log
			$new_userid_error = new error;
			$error_text = "Error: Missing user id";
			$new_userid_error->log("User id","critical",$error_text,$cfg);
			// error occurred while querying into database 
			//$this->error[] = "1065";
			// response
			return FALSE;
		}
		$startdate = $params['startdate'];
		$enddate  = $params['enddate'];
		if (!$startdate) {
			$startdate = date("Y-m-d",ts_time($cfg) - 10800);		
		}
		if (!$enddate) {
			$enddate = date("Y-m-d");	
		}		
		$sqlstr = "SELECT ~tender_item.id, SUM(~tender_quote.price) ";
		// 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) LEFT JOIN ~tender_item_complete ";
		$sqlstr.= "             	 							   ON ~tender_item_complete.tender_item = ~tender_item.id, ";
		$sqlstr.= "~tender, ~users subscriber "; 
		// WHERE
		$sqlstr.= "WHERE subscriber.id = ~tender.user_id ";
		$sqlstr.= "AND (ts_tender_item_complete.status != 0 OR ts_tender_item_complete.status is NULL) ";
		$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
		$sqlstr.= "AND ~tender_item.classification = 1 ";
		$sqlstr.= "AND ~tender_item.status != 0 ";
		$sqlstr.= "AND ~tender.created_date >= '".$startdate."' ";
		$sqlstr.= "AND ~tender.created_date <= '".$enddate."' ";
		if($usersid) {
			$sqlstr.= "AND ~tender.user_id = ".$usersid." ";
		}
		if($branchid) {
			$sqlstr.= "AND subscriber.branch = ".$branchid." ";
		}
		if($organisationid) {
			$sqlstr.= "AND subscriber.organisation = ".$organisationid." ";
		}
		$sqlstr.= "GROUP BY ~tender_item.id ";
		//$sqlstr.= "ORDER BY ~tender_item.id ";
		$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;
		}
		return $this->tender;
	}

	// get total quotes for an agent
	function get_total_quotes($params,$cfg,$conn) {
		$usersid = $params['usersid'];
		$startdate = $params['startdate'];
		$enddate  = $params['enddate'];	
		if(!$usersid) {
			// instantiate the error class and log
			$new_userid_error = new error;
			$error_text = "Error: Missing user id";
			$new_userid_error->log("User id","critical",$error_text,$cfg);
			// error occurred while querying into database 
			//$this->error[] = "1065";
			// response
			return FALSE;
		}
		$sqlstr = "SELECT ~tender_quote.id, ~tender_quote.price ";
		// FROM
		$sqlstr.= "FROM ~tender_quote, ~tender_item ";
		// WHERE
		$sqlstr.= "WHERE ~tender_quote.tender_item = ~tender_item.id ";				
		$sqlstr.= "AND ~tender_item.classification = 1 ";
		$sqlstr.= "AND ~tender_quote.price != 0 ";
		$sqlstr.= "AND ~tender_quote.status != 0 ";
		$sqlstr.= "AND ~tender_quote.created_date >= '".$startdate."' ";
		$sqlstr.= "AND ~tender_quote.created_date <= '".$enddate."' ";
		if($usersid) {
			$sqlstr.= "AND ~tender_quote.user_id = ".$usersid." ";
		}
		$sqlstr.= "GROUP BY ~tender_quote.id, ~tender_quote.price ";
		$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;
		}
		return $this->tender;
	}

	//get total number and value of items awarded for an agent
	function get_awarded_for_agent($params,$cfg,$conn) {
		$usersid = $params['usersid'];
		$branchid = $params['branchid'];
		$organisationid = $params['organisationid'];
		if(!$usersid) {
			// instantiate the error class and log
			$new_userid_error = new error;
			$error_text = "Error: Missing user id";
			$new_userid_error->log("User id","critical",$error_text,$cfg);
			// error occurred while querying into database 
			//$this->error[] = "1065";
			// response
			return FALSE;
		}
		$startdate = $params['startdate'];
		$enddate  = $params['enddate'];
		if (!$startdate) {
			$startdate = date("Y-m-d",ts_time($cfg) - 10800);		
		}
		if (!$enddate) {
			$enddate = date("Y-m-d");	
		}		
		$sqlstr = "SELECT ~tender_quote.id, ~tender_quote.price ";
		// FROM
		$sqlstr.= "FROM ~tender_item_complete, ~tender_quote, ~tender_item ";
		// WHERE				
		$sqlstr.= "WHERE ~tender_quote.tender_item = ~tender_item_complete.tender_item ";
		$sqlstr.= "AND ~tender_item_complete.status != 0 ";
		$sqlstr.= "AND ~tender_quote.tender_item = ~tender_item.id ";
		$sqlstr.= "AND ~tender_item.classification = 1 ";
		if($usersid) {
			$sqlstr.= "AND ~tender_quote.user_id = ".$usersid." ";
		}		
		$sqlstr.= "AND ~tender_quote.id = ~tender_item_complete.tender_quote ";
		$sqlstr.= "AND ~tender_quote.price != 0 ";
		$sqlstr.= "AND ~tender_quote.created_date >= '".$startdate."' ";  
		$sqlstr.= "AND ~tender_quote.created_date <= '".$enddate."' "; 
		$sqlstr.= "GROUP BY ~tender_quote.id, ~tender_quote.price ";		
		$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;
		}
		return $this->tender;
	}
	
	//get total number and value of items awarded for an agent
	function get_uncompleted_for_agent($params,$cfg,$conn) {
		$usersid = $params['usersid'];
		$branchid = $params['branchid'];
		$organisationid = $params['organisationid'];
		if(!$usersid) {
			// instantiate the error class and log
			$new_userid_error = new error;
			$error_text = "Error: Missing user id";
			$new_userid_error->log("User id","critical",$error_text,$cfg);
			// error occurred while querying into database 
			//$this->error[] = "1065";
			// response
			return FALSE;
		}
		$startdate = $params['startdate'];
		$enddate  = $params['enddate'];
		if (!$startdate) {
			$startdate = date("Y-m-d",ts_time($cfg) - 10800);		
		}
		if (!$enddate) {
			$enddate = date("Y-m-d");	
		}		
		$sqlstr = "SELECT ~tender_request.id, ~tender_request.user_id "; 
		// FROM
		$sqlstr .= "FROM ~tender_request LEFT JOIN ~tender_quote "; 
		$sqlstr .= "					 ON ~tender_quote.tender_item = ~tender_request.tender_item "; 
		$sqlstr .= "					 AND ~tender_quote.price != 0 ";
		if($usersid) {
			$sqlstr.= "			  		 AND ~tender_quote.user_id = ".$usersid.", ";
		}
		$sqlstr .= "~tender_item ";
		// WHERE
		$sqlstr .= "WHERE ~tender_quote.id is NULL ";
		$sqlstr .= "AND ~tender_request.tender_item = ~tender_item.id "; 
		$sqlstr .= "AND ~tender_item.classification = 1 ";
		if($usersid) {
			$sqlstr.= "AND ~tender_request.user_id = ".$usersid." ";
		}		
		$sqlstr .= "AND ~tender_request.status != 0  ";
		$sqlstr .= "AND ~tender_request.created_date >= '".$startdate."' ";
		$sqlstr .= "AND ~tender_request.created_date <= '".$enddate."' ";
		$sqlstr .= "GROUP BY ~tender_request.id, ~tender_request.user_id "; 
		$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;
		}
		return $this->tender;
	}

	// get number of feedback requests for an agent
	function get_feedback_requests_for_agent($params,$cfg,$conn) {
		$usersid = $params['usersid'];
		$branchid = $params['branchid'];
		$organisationid = $params['organisationid'];
		if(!$usersid) {
			// instantiate the error class and log
			$new_userid_error = new error;
			$error_text = "Error: Missing user id";
			$new_userid_error->log("User id","critical",$error_text,$cfg);
			// error occurred while querying into database 
			//$this->error[] = "1065";
			// response
			return FALSE;
		}
		$startdate = $params['startdate'];
		$enddate  = $params['enddate'];
		if (!$startdate) {
			$startdate = date("Y-m-d",ts_time($cfg) - 10800);		
		}
		if (!$enddate) {
			$enddate = date("Y-m-d");	
		}		
		$sqlstr = "SELECT id, created_date, created_time "; 
		// FROM
		$sqlstr .= "FROM ~feedback_request "; 
		// WHERE
		$sqlstr .= "WHERE ~feedback_request.status = 1 ";
		if($usersid) {
			$sqlstr.= "AND ~feedback_request.user_id = ".$usersid." ";
		}
		$sqlstr .= "AND ~feedback_request.status = 1 ";
		$sqlstr .= "AND ~feedback_request.created_date >= '".$startdate."' "; 
		$sqlstr .= "AND ~feedback_request.created_date <= '".$enddate."' "; 
		$sqlstr .= "ORDER BY created_date ASC, created_time ASC ";
		$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;
		}
		return $this->tender;
	}
	
	// get number of feedback requests for an agent
	function get_feedback_response_for_agent($params,$cfg,$conn) {
		$usersid = $params['usersid'];
		$branchid = $params['branchid'];
		$organisationid = $params['organisationid'];
		if(!$usersid) {
			// instantiate the error class and log
			$new_userid_error = new error;
			$error_text = "Error: Missing user id";
			$new_userid_error->log("User id","critical",$error_text,$cfg);
			// error occurred while querying into database 
			//$this->error[] = "1065";
			// response
			return FALSE;
		}
		$startdate = $params['startdate'];
		$enddate  = $params['enddate'];
		if (!$startdate) {
			$startdate = date("Y-m-d",ts_time($cfg) - 10800);		
		}
		if (!$enddate) {
			$enddate = date("Y-m-d");	
		}		
		$sqlstr = "SELECT ~feedback_response.id, ~feedback_response.purchase_order "; 
		// FROM
		$sqlstr .= "FROM ~feedback_response, ~purchase_order, ~feedback_request ";
		// WHERE
		$sqlstr .= "WHERE ~feedback_response.purchase_order = ~purchase_order.id "; 
		$sqlstr .= "AND ~feedback_response.purchase_order = ~feedback_request.purchase_order ";
		if($usersid) {
			$sqlstr.= "AND ~feedback_request.user_id = ".$usersid." ";
		}
		$sqlstr .= "AND ~feedback_response.created_date >= '".$startdate."' ";
		$sqlstr .= "AND ~feedback_response.created_date <= '".$enddate."' "; 
		$sqlstr .= "GROUP BY ~feedback_response.id, ~feedback_response.purchase_order "; 

		$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;
		}
		return $this->tender;
	}


	//get total number and value of items awarded for a subscriber, or branch, or organisation
	function get_total_items_awarded($params,$cfg,$conn) {
		$usersid = $params['usersid'];
		$branchid = $params['branchid'];
		$organisationid = $params['organisationid'];
		if(!$usersid) {
			// instantiate the error class and log
			$new_userid_error = new error;
			$error_text = "Error: Missing user id";
			$new_userid_error->log("User id","critical",$error_text,$cfg);
			// error occurred while querying into database 
			//$this->error[] = "1065";
			// response
			return FALSE;
		}
		$startdate = $params['startdate'];
		$enddate  = $params['enddate'];
		if (!$startdate) {
			$startdate = date("Y-m-d",ts_time($cfg) - 10800);		
		}
		if (!$enddate) {
			$enddate = date("Y-m-d");	
		}		
		$sqlstr = "SELECT ~tender_item.id, SUM(~tender_quote.price) AS sum ";
		// 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, ~organisation, ~tender_item_complete ";
		// 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.id = ~tender_item_complete.tender_item ";
		$sqlstr.= "AND ~tender_item_complete.status = 1 ";
		$sqlstr.= "AND subscriber.organisation  = ~organisation.id ";
		$sqlstr.= "AND ~tender.created_date >= '".$startdate."' ";
		$sqlstr.= "AND ~tender.created_date <= '".$enddate."' ";
		if($usersid) {
			$sqlstr.= "AND ~tender.user_id = ".$usersid." ";
		}
		if($branchid) {
			$sqlstr.= "AND subscriber.branch = ".$branchid." ";
		}
		if($organisationid) {
			$sqlstr.= "AND subscriber.organisation = ".$organisationid." ";
		}
		$sqlstr.= "GROUP BY ~tender_item.id ";		
		$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;
		}
		return $this->tender;
	}
	
	//get total number of purchase orders for a subscriber, or branch, or organisation
	function get_total_purchase_orders($params,$cfg,$conn) {
		$usersid = $params['usersid'];
		$branchid = $params['branchid'];
		$organisationid = $params['organisationid'];
		if(!$usersid) {
			// instantiate the error class and log
			$new_userid_error = new error;
			$error_text = "Error: Missing user id";
			$new_userid_error->log("User id","critical",$error_text,$cfg);
			// error occurred while querying into database 
			//$this->error[] = "1065";
			// response
			return FALSE;
		}
		$startdate = $params['startdate'];
		$enddate  = $params['enddate'];
		if (!$startdate) {
			$startdate = date("Y-m-d",ts_time($cfg) - 10800);		
		}
		if (!$enddate) {
			$enddate = date("Y-m-d");	
		}		
		$status = $params['status'];
		if (!$status) {
			$status = "1";	
		}		
		$sqlstr = "SELECT ~organisation.id, ~purchase_order.id ";
		// FROM
		$sqlstr.= "FROM ~tender, ~purchase_order, ~tender_item, ";
		$sqlstr.= "~users subscriber, ~organisation, ";
		$sqlstr.= "~tender_item_complete ";
		// WHERE
		$sqlstr.= "WHERE ~purchase_order.tender = ~tender.id ";
		$sqlstr.= "AND ~tender.id = ~tender_item.tender ";
		$sqlstr.= "AND ~tender_item.id = ~tender_item_complete.tender_item ";
		$sqlstr.= "AND ~tender_item_complete.purchase_order = ~purchase_order.id ";
		$sqlstr.= "AND ~purchase_order.user_id = subscriber.id ";
		$sqlstr.= "AND ~purchase_order.status = ".$status." ";
		$sqlstr.= "AND subscriber.organisation  = ~organisation.id ";
		$sqlstr.= "AND ~purchase_order.created_date >= '".$startdate."' ";
		$sqlstr.= "AND ~purchase_order.created_date <= '".$enddate."' ";
		if($usersid) {
			$sqlstr.= "AND subscriber.id = ".$usersid." ";
		}
		if($branchid) {
			$sqlstr.= "AND subscriber.branch = ".$branchid." ";
		}
		if($organisationid) {
			$sqlstr.= "AND subscriber.organisation = ".$organisationid." ";
		}
		// GROUP BY
		$sqlstr.= "GROUP BY ~organisation.id, ~purchase_order.id ";		
		$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;
		}
		return $this->tender;
	}	
	
		
	//get total number of purchase orders for an agent
	function get_purchase_orders_for_agent($params,$cfg,$conn) {
		$usersid = $params['usersid'];
		$branchid = $params['branchid'];
		$organisationid = $params['organisationid'];
		if(!$usersid) {
			// instantiate the error class and log
			$new_userid_error = new error;
			$error_text = "Error: Missing user id";
			$new_userid_error->log("User id","critical",$error_text,$cfg);
			// error occurred while querying into database 
			//$this->error[] = "1065";
			// response
			return FALSE;
		}
		$startdate = $params['startdate'];
		$enddate  = $params['enddate'];
		if (!$startdate) {
			$startdate = date("Y-m-d",ts_time($cfg) - 10800);		
		}
		if (!$enddate) {
			$enddate = date("Y-m-d");	
		}		
		$status = $params['status'];
		if (!$status) {
			$status = "1";	
		}		
		$sqlstr = "SELECT ~purchase_order.id, ~purchase_order.agent "; 
		// FROM
		$sqlstr.= "FROM ~purchase_order ";
		// WHERE
		$sqlstr.= "WHERE ~purchase_order.status = ".$status." ";	
		if($usersid) {
			$sqlstr .= "AND ~purchase_order.agent = ".$usersid." ";
		}
		$sqlstr.= "AND ~purchase_order.created_date >= '".$startdate."' ";
		$sqlstr.= "AND ~purchase_order.created_date <= '".$enddate."' ";
		// GROUP BY
		$sqlstr.= "GROUP BY ~purchase_order.id, ~purchase_order.agent ";		
		$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;
		}
		return $this->tender;
	}		
	
	//get average RFQ value for a subscriber, or branch, or organisation
	function get_average_rfq($params,$cfg,$conn) {
		$usersid = $params['usersid'];
		$branchid = $params['branchid'];
		$organisationid = $params['organisationid'];
		if(!$usersid) {
			// instantiate the error class and log
			$new_userid_error = new error;
			$error_text = "Error: Missing user id";
			$new_userid_error->log("User id","critical",$error_text,$cfg);
			// error occurred while querying into database 
			//$this->error[] = "1065";
			// response
			return FALSE;
		}
		$startdate = $params['startdate'];
		$enddate  = $params['enddate'];
		if (!$startdate) {
			$startdate = date("Y-m-d",ts_time($cfg) - 10800);		
		}
		if (!$enddate) {
			$enddate = date("Y-m-d");	
		}		
		$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 ~tender_item.status != 0 ";
		$sqlstr.= "AND subscriber.organisation  = ~organisation.id ";
		$sqlstr.= "AND ~tender.created_date >= '".$startdate."' ";
		$sqlstr.= "AND ~tender.created_date <= '".$enddate."' ";
		if($usersid) {
			$sqlstr.= "AND ~tender.user_id = ".$usersid." ";
		}
		if($branchid) {
			$sqlstr.= "AND subscriber.branch = ".$branchid." ";
		}
		if($organisationid) {
			$sqlstr.= "AND subscriber.organisation = ".$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;
		if ($this->tender) {
			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']);
				}
			}
		}
		return $response;		
	}
	
	// get conversion rate for a subscriber, or branch, or organisation
	function get_conversion_rate($params,$cfg,$conn) {	
		$usersid = $params['usersid'];
		$branchid = $params['branchid'];
		$organisationid = $params['organisationid'];
		if(!$usersid) {
			// instantiate the error class and log
			$new_userid_error = new error;
			$error_text = "Error: Missing user id";
			$new_userid_error->log("User id","critical",$error_text,$cfg);
			// error occurred while querying into database 
			//$this->error[] = "1065";
			// response
			return FALSE;
		}
		$startdate = $params['startdate'];
		$enddate  = $params['enddate'];
		if (!$startdate) {
			$startdate = date("Y-m-d",ts_time($cfg) - 10800);	
		}
		if (!$enddate) {
			$enddate = date("Y-m-d");	
		}		
		//$sqlstr = "SELECT ~tender_item.id, ~tender_item.brand, ~tender_item.model, ~tender_item.status, ~tender_item.tender, ~tender.user_id ";
		$sqlstr = "SELECT ~organisation.id, COUNT(~tender_item.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 ~tender_item.status != 0 ";
		$sqlstr.= "AND subscriber.organisation  = ~organisation.id ";
		$sqlstr.= "AND ~tender.created_date >= '".$startdate."' ";
		$sqlstr.= "AND ~tender.created_date <= '".$enddate."' ";
		if($usersid) {
			$sqlstr.= "AND ~tender.user_id = ".$usersid." ";
		}
		if($branchid) {
			$sqlstr.= "AND subscriber.branch = ".$branchid." ";
		}
		if($organisationid) {
			$sqlstr.= "AND subscriber.organisation = ".$organisationid." ";
		}
		$sqlstr.= "GROUP BY ~organisation.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 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_item.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 >= '".$startdate."' ";
		$sqlstr.= "AND ~tender.created_date <= '".$enddate."' ";
		if($usersid) {
			$sqlstr.= "AND ~tender.user_id = ".$usersid." ";
		}
		if($branchid) {
			$sqlstr.= "AND subscriber.branch = ".$branchid." ";
		}
		if($organisationid) {
			$sqlstr.= "AND subscriber.organisation = ".$organisationid." ";
		}
		$sqlstr.= "GROUP BY ~organisation.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;
		}
		if ($response) {
			$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";
				}
			}
		}			
		return $response;
	}
	
	// get uncompleted tenders for display
	function get_uncompleted_tenders($params,$cfg,$conn) {
		$usersid = $params['usersid'];
		if(!$usersid) {
			// instantiate the error class and log
			$new_userid_error = new error;
			$error_text = "Error: Missing user id";
			$new_userid_error->log("User id","critical",$error_text,$cfg);
			// error occurred while querying into database 
			//$this->error[] = "1065";
			// response
			return FALSE;
		}
		$startdate = $params['startdate'];
		$enddate  = $params['enddate'];
		if (!$startdate) {
			$startdate = date("Y-m-d");		
		}
		if (!$enddate) {
			$enddate = date("Y-m-d",ts_time($cfg) - 259200);	
		}		
		$sqlstr = "SELECT ~tender.rfq_number, ~tender.rfq_reference, ~tender.transport, ";
		$sqlstr.= "~tender.id AS tenderid, ~tender.close_date, ~tender.close_time, ";
		$sqlstr.= "~tender.created_date, ~tender.created_time, ";
		$sqlstr.= "~users.name, ~users.surname, ~users.id AS usersid, ";
		$sqlstr.= "~area.name AS areaname, ";
		$sqlstr.= "~area_state.name AS statename ";
		// FROM
		$sqlstr.= "FROM ~tender, ~users, ~area, ~area_state ";
		// WHERE
		$sqlstr.= "WHERE  ~tender.user_id = ~users.id ";
		$sqlstr.= "AND ~tender.area = ~area.id ";
		$sqlstr.= "AND ~area.state = ~area_state.id ";
		$sqlstr.= "AND ~users.id = ".$usersid." ";
		$sqlstr.= "AND ~tender.status = 4 ";
		$sqlstr.= "AND ~tender.created_date >= '".$startdate."' ";
		$sqlstr.= "AND ~tender.created_date <= '".$enddate."' ";	
		$sqlstr.= "ORDER BY ~tender.close_date 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;
		}
		$new_database->convertTime($all,$cfg);
		return $all;
	}
}
?>