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

/**
* Request class
* @package TenderSystem
* @subpackage tender
* @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 tender_request extends procure_request {
	
	function tender_request() {
		// allow php5 to work
		$this->error = array();	
	}
	
	/**
	 * creates a new tender
	 * 
	 * @param array tender The Tender infromation
	 * @param array user The subscriber information
	 * @param array request The requestors information
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param boolean TRUE on success or FALSE on error  
	 */
	function initiate_tender($tender,$request,$cfg,$conn) {
		// validate paramaters
		$this->tender = $tender;
		$this->requester = $request;
		if (!$this->tender['transport']) {
			// set to default if not present
			$this->tender['transport'] = $cfg['tender']['transport']['default'];
		}
		$this->set_language("tender",$cfg);
		if(!$this->prepare_procure_request("tender",$cfg,$conn)) {
			return FALSE;
		}
		// check to add transport type
		if ($this->tender['transport'] == "d" || $this->tender['transport'] == "i") {
			// set db array
			$deliver['tender'] = $this->tender['id'];
			$deliver['category_sub'] = $this->tender['item'][0]['category_sub'];
			$deliver['model'] = $this->tender['statename'].":".$this->tender['areaname'];
			$deliver['quote']= "0.00";
			$deliver['budget'] = "0.00";
			$deliver['status'] = "1";
			if ($this->tender['transport'] == "d") {
				$deliver['classification'] = "3";
				$deliver['brand'] = $this->lang['deliver_brand'];
				$deliver['description'] = $this->lang['deliver_des'].$this->tender['areaname'].".";
				$deliver['description'].= $this->lang['can_be']." ".$cfg['local']['precurrency']."0.00".$cfg['local']['postcurrency'];
				$deliver['subcatname'] = $this->lang['deliver'];
			} elseif ($this->tender['transport'] == "i") {
				$deliver['classification'] = "4";
				$deliver['brand'] = $this->lang['install_brand'];
				$deliver['description'] = $this->lang['install_des'].$this->tender['areaname'].".";
				$deliver['description'].= $this->lang['can_be']." ".$cfg['local']['precurrency']."0.00".$cfg['local']['postcurrency'];
				$deliver['subcatname'] = $this->lang['install'];
			}
			// create delivery item
			$deliver['id'] = $this->create_tender_item($deliver,$cfg,$conn);
			// add to the end
			array_push($this->tender['item'],$deliver);
		}
		// response
		return TRUE;
	}
	
	/**
	 * set status of items and tender to error
	 *
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param Tboolean RUE on success or FALSE on error  
	 */
	function no_tender_suppliers($cfg,$conn) {
		// update items
		foreach ($this->tender['item'] as $value) {
			if ($value['status'] != "0") {
				// set that no applicable suppliers could be found
				$value['status'] = "0";
				$value['error'].= $this->lang['nosupplier'];
				// update the item
				if(!$this->update_item($value,$cfg,$conn)) {
					return FALSE;
				}
			}
		}
		// set tender status to error
		if(!$this->update_tender_status($this->tender['id'],"0",$this->lang['nosupplier'],$cfg,$conn)) {
			return FALSE;	
		}
		return TRUE;
	}
	
	/**
	 * checks if tender can be closed
	 *
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param boolean TRUE if yes or FALSE if no  
	 */
	function check_tender_closure($cfg,$conn) {
		// SELECT
		$sqlstr = "SELECT ~tender_item.id, ";
		$sqlstr.= "COUNT(DISTINCT supplier.id) AS request_count,  ";
		$sqlstr.= "COUNT(~tender_request.id) AS requests  ";
		// FROM
		$sqlstr.= "FROM ~tender_request, ~organisation supplier, ~users agent,  ";
		$sqlstr.= "~tender_item  ";
		// WHERE
		$sqlstr.= "WHERE ~tender_item.tender = '".$this->tender['id']."' ";
		$sqlstr.= "AND ~tender_request.tender_item = ~tender_item.id ";
		$sqlstr.= "AND ~tender_item.status = 2 ";
		$sqlstr.= "AND ~tender_request.status = 1 ";
		$sqlstr.= "AND ~tender_request.user_id = agent.id ";
		$sqlstr.= "AND (agent.status = '1' OR  agent.status = '2') ";
		$sqlstr.= "AND agent.organisation = supplier.id ";
		$sqlstr.= "AND supplier.status != 0 ";
		// GROUP BY
		$sqlstr.= "GROUP BY ~tender_item.id ";
		// ORDER BY
		$sqlstr.= "ORDER BY ~tender_item.id ";
		$new_database = new database;
		$new_database->parse_sql($sqlstr,$cfg);
		$request_assoc = $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()." SQL:".$sqlstr;
			$new_error->log("SQL","critical",$error_text,$cfg);
			// error occurred while querying into database 
			$this->error[] = "1065";
			// response
			return FALSE;
		}
		// SELECT
		$sqlstr = "SELECT ~tender_item.id, ";
		$sqlstr.= "COUNT(DISTINCT supplier.id) AS quote_count,  ";
		$sqlstr.= "COUNT(~tender_quote.id) AS quotes  ";
		// FROM
		$sqlstr.= "FROM ~tender_quote, ~organisation supplier, ~users agent,  ";
		$sqlstr.= "~tender_item  ";
		// WHERE
		$sqlstr.= "WHERE ~tender_item.tender = '".$this->tender['id']."' ";
		$sqlstr.= "AND ~tender_quote.tender_item = ~tender_item.id ";
		$sqlstr.= "AND ~tender_item.status = 2 ";
		$sqlstr.= "AND ~tender_quote.status = 2 ";
		$sqlstr.= "AND ~tender_quote.user_id = agent.id ";
		$sqlstr.= "AND (agent.status = '1' OR  agent.status = '2') ";
		$sqlstr.= "AND agent.organisation = supplier.id ";
		$sqlstr.= "AND supplier.status != 0 ";
		// GROUP BY
		$sqlstr.= "GROUP BY ~tender_item.id ";
		// ORDER BY
		$sqlstr.= "ORDER BY ~tender_item.id ";
		$new_database = new database;
		$new_database->parse_sql($sqlstr,$cfg);
		$quote_assoc = $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()." SQL:".$sqlstr;
			$new_error->log("SQL","critical",$error_text,$cfg);
			// error occurred while querying into database 
			$this->error[] = "1065";
			// response
			return FALSE;
		}
		if ($cfg['procure']['approve']['enable'] && !$cfg['approving_procure']) {
			// do not close as approval required
			return TRUE;
		}
		if (!$cfg['procure']['supplier']['required']) {
			if (count($request_assoc) == "0" || count($quote_assoc) == "0") {
				// don't close, no suppliers for quote
				return TRUE;
			}
		}
		// check if all item complete
		foreach($request_assoc as $key => $value) {
			if(!isset($quote_assoc[$key]['quote_count']) || 
			    $request_assoc[$key]['request_count'] != $quote_assoc[$key]['quote_count']) {
				// quoting not done
				return TRUE;	
			}
		}
		// set tender status
		$this->update_tender_status($this->tender['id'],"close","",$cfg,$conn);
		// convert closing to display format
		$time_stamp = ts_time($cfg);
		$this->response['closing'] = date($cfg['date']['format']." ".$cfg['time']['format'],$time_stamp);
		return TRUE;
	}
	
	/**
	 * gets all valid items
	 *
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param mixed array of items or FALSE on error  
	 */
	function valid_items($organisationid,$tender,$cfg,$conn) {
		$timestamp = ts_time($cfg);
		$expire_date = date("Y-m-d",$timestamp);
		// set the organisation
		$quote['organisation'] = $organisationid;
		// query db to find out if valid items are present
		$sqlstr = "SELECT ~tender_item.id, ";
		$sqlstr.= "~item.brand, ~item.valid_until, ~item.price, ";
		$sqlstr.= "~item.model, ~item.description, ~item.stock ";
		$sqlstr.= "FROM ~item, ~tender_item ";
		$sqlstr.= "WHERE ~item.supplier = '".$organisationid."' ";
		$sqlstr.= "AND ~item.status = '1' ";
		$sqlstr.= "AND ~item.valid_until > '".$expire_date."' ";
		$sqlstr.= "AND upper(~item.brand) = upper(~tender_item.brand) ";
		$sqlstr.= "AND upper(~item.model) = upper(~tender_item.model) ";
		$sqlstr.= "AND ~item.category_sub = ~tender_item.category_sub ";
		$sqlstr.= "AND ~tender_item.tender = '".$tender."' ";
		$sqlstr.= "AND ~tender_item.status = '2' ";		
		$sqlstr.= "ORDER BY ~item.id DESC ";
		$new_database = new database;
		$new_database->parse_sql($sqlstr,$cfg);
		// execute the query
 		$assoc = $conn->GetAssoc($sqlstr);
       	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);
			// set the response error
			$this->error[] = "1065";
			// response
			return FALSE;
       	}
		// return sub category name
		return $assoc;
	}
	
	/**
	 * gets all expired items
	 *
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param mixed array of items or FALSE on error  
	 */
	function expired_items($organisationid,$tender,$cfg,$conn) {
		// set the organisation
		$quote['organisation'] = $organisationid;
		// query db to find out if valid items are present
		$sqlstr = "SELECT ~tender_item.id, ";
		$sqlstr.= "~item.price ";
		$sqlstr.= "FROM ~item, ~tender_item ";
		$sqlstr.= "WHERE ~item.supplier = '".$organisationid."' ";
		$sqlstr.= "AND ~item.status = '1' ";
		$sqlstr.= "AND upper(~item.brand) = upper(~tender_item.brand) ";
		$sqlstr.= "AND upper(~item.model) = upper(~tender_item.model) ";
		$sqlstr.= "AND ~item.category_sub = ~tender_item.category_sub ";
		$sqlstr.= "AND ~tender_item.tender = '".$tender."' ";
		$sqlstr.= "AND ~tender_item.status = '2' ";		
		$sqlstr.= "ORDER BY ~item.id DESC ";	
		$new_database = new database;
		$new_database->parse_sql($sqlstr,$cfg);
		// execute the query
 		$assoc = $conn->GetAssoc($sqlstr);
       	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);
			// set the response error
			$this->error[] = "1065";
			// response
			return FALSE;
       	}
		// return sub category name
		return $assoc;
	}
	
	/**
	 * create a tender request
	 *
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param mixed array of items or FALSE on error  
	 */
	function do_request($cfg,$conn) {
		// set the status of the items to 2 and the tender
		$item_count = $this->ready_items($cfg,$conn);
		if($item_count === FALSE) {
			return FALSE;
		} elseif($item_count < 1) {
			$this->response['tender_deleted'] = "1";
			return TRUE;
		}
		// get agent info
		if(!$this->get_agent_info($cfg,$conn)) {
			return FALSE;
		}
		// send invitation
		if(!$this->send_invitations($cfg,$conn)) {
			return FALSE;
		}
       	return TRUE;
	}
	
	/**
	 * send tender invitations
	 *
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param mixed array of items or FALSE on error  
	 */
	function send_invitations($cfg,$conn) {
		foreach($this->agent as $key => $value) {
			$excluded = false;
			// determine if there are excluded suppliers
			if ($this->tender['excluded']) {
				if(in_array($value['supplierid'], $this->tender['excluded'])) {
					$excluded = true;
				}
			}
			// do not send to agents of supplier that are excluded			
			if (!$excluded) {		
				// find the tender items that the agent must tender for
				$items = $this->find_items($value['id'],$this->tender['id'],$cfg,$conn);
				if($items === FALSE) {
					// A SQL error
					return FALSE;	
				}
				// check if there are items to tender for
				if(count($items) == 0 || $items[0]['classification'] != "1") {
					// go on to next agent
					continue;
				}
				// assume no valid items
				$valid_items = array();
				// check if valid items should be loaded
				if ($cfg['tender']['validity']['status']) {
					// find the already loaded items that are a match the items that need to be tender for
					$valid_items = $this->valid_items($value['supplierid'],$this->tender['id'],$cfg,$conn);
					if($valid_items === FALSE) {
						// A SQL error
						return FALSE;	
					}
				}
				if($cfg['tender']['validity']['all']) {
					// check if have valid items for everything
					foreach($items as $key1 => $value1) {
						if(!$valid_items[$value1['id']]) {
							// remove all valid items
							$valid_items = array();
							break;
						}
					}
				}
				// the items that a rfq email must be sent for
				$tender_items = array();
				// go through each item to load valid items
				foreach($items as $key1 => $value1) {
					// check for a valid item, only load if preference to recieve tender request
					if($valid_items[$value1['id']] && ($value['preference'] == "m" || $value['preference'] == "n")) {
						// load the valid item
						$request['tender_item'] = $value1['id'];
						$request['user_id'] = $value['id'];
						$request['method'] = "Auto";
						$request['status'] = "1";
						// create request
						$create_request = $this->create_request($request,$cfg,$conn);
						if($create_request === FALSE) {
							// A SQL error
							return FALSE;	
						}
						// insert into tender response database
						$quote = $valid_items[$value1['id']];
						$quote['tender_item'] = $value1['id'];
						$quote['created_by'] = $quote['user_id'] = $value['id'];
						$quote['method'] = "Auto";
						$quote['status'] = "2";
						$create_quote = $this->create_tender_quote($quote,$cfg,$conn);
						if($create_quote === FALSE) {
							// A SQL error
							return FALSE;
						}
					} else {
						// send rfq for this item
						$tender_items[] = $value1;
						// populate the SMS
						if ($value1['classification'] == "1") {
							$sms_message[$value['id']].= $value1['brand']." ".$value1['model'].", ";
						}
					}
					// load item into response
					if($this->response['item'][$value1['id']]) {
						$this->response['item'][$value1['id']]['requested']++;
					} else {
						$this->response['item'][$value1['id']] = $value1;
						$this->response['item'][$value1['id']]['description'] = wordwrap($value1['description']);
						$this->response['item'][$value1['id']]['requested'] = 1;
					}
					// load supplier into response
					if(!$this->response['supplier'][$value['supplierid']]) {
						$new_organisation = new organisation;
						$this->response['supplier'][$value['supplierid']]['name'] = $new_organisation->get_name($value['supplierid'],$cfg,$conn);
						$this->response['supplier'][$value['supplierid']]['id'] = $value['supplierid'];
					}
				}
				// check for items that get rfq request
				if($tender_items) {
					// sending email request
					if($value['preference'] == "m" || $value['preference'] == "n") {
						// find prices of expired items to use as a default value
						if($cfg['tender']['expired']['default']) {
							$item_prices = $this->expired_items($value['supplierid'],$this->tender['id'],$cfg,$conn);
							if($item_prices === FALSE) {
								// 	A SQL error
								return FALSE;	
							}
							// go through each item a check if expired prices
							foreach($tender_items as $key1 => $value1) {
								$tender_items[$key1]['price'] = $item_prices[$value1['id']];							
							}
						}
						// send email
						$request['request_message'] = $this->send_email_request($value,$tender_items,$cfg,$conn);
						// error handling
						if(!$request['request_message']) {
							// instantiate the error class and log
							$new_error = new error;
							$error_text = "Error sending Tender Request to ".$value['name']." ".$value['surname']." Error:".$this->email_error;
							$new_error->log("Email","serious",$error_text,$cfg);
							$request['error'] = $this->email_error;
							$request['request_message'] = "NULL";
						}
						$request['method'] = "Email";
						$request['status'] = "1";					
						
					} else {
						// no email request
						$request['method'] = "None";
						$request['status'] = "1";
						$request['request_message'] = "NULL";
					}
					// create a request for each item
					foreach ($tender_items as $key1 => $value1) {
						$request['tender_item'] = $value1['id'];
						$request['user_id'] = $value['id'];
						$request['address'] = $value['email'];
						// create request
						$create_request = $this->create_request($request,$cfg,$conn);
						if($create_request === FALSE) {
							// A SQL error
							return FALSE;	
						}
					}
					// send sms notification
					if($value['preference'] == "n") {
						if(!$this->send_sms_notification($value,$tender_items,$sms_message[$value['id']],$cfg,$conn)) {
							// instantiate the error class and log
							$new_error = new error;
							$error_text = "Error sending Tender Request Notification to ".$value['name']." ".$value['surname']." Error:".$this->email_error;
							$new_error->log("Email","serious",$error_text,$cfg);
						}				
					}
				}
			}
		}
		// look for items not loaded into response
		$items = $this->find_items("",$this->tender['id'],$cfg,$conn);
		foreach($items as $key1 => $value1) {	
			// check if loaded into response
			if(!$this->response['item'][$value1['id']]) {
				$this->response['item'][$value1['id']] = $value1;
				$this->response['item'][$value1['id']]['description'] = wordwrap($value1['description']);
				$this->response['item'][$value1['id']]['requested'] = 0;
			}
		}
		// sort so that tranport item at bottom
		ksort($this->response['item']);
		// finnish
		return TRUE;
	}
	
	/**
	 * send email to an agent
	 *
	 * @param array agent agent information
	 * @param array items the tender items
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param mixed message id on success or FALSE on error  
	 */
	function send_email_request($agent,$items,$cfg,$conn) {
		// include email body template
		$subject = "";
		$html = "";
		$subject = "";
		require($cfg['file'].'modules/tender/templates/tender_request.php');
		// set message paramaters
		$message['reference'] = $this->tender['rfq_number'];
		$message['classification'] = "tender_return";
		$message['subject'] = $subject;
		$message['body'] = $html;
		$message['name'] = $agent['name']." ".$agent['surname'];
		$message['email'] = $agent['email'];
		// send the email
		$new_message = new message;
		$id = $new_message->send_email_message($message,$agent['id'],$this->requester['id'],$cfg,$conn);
		if(!$id) {
			$this->email_error = $new_message->error_text;
			return FALSE;
		}
		return $id;
	}

	/**
	 * find items an agent must quote on
	 *
	 * @param array agent agent's user ID
	 * @param array tender Tender ID
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param mixed message id on success or FALSE on error  
	 */
	function find_items($agent,$tender,$cfg,$conn) {
		$sqlstr = "SELECT DISTINCT ~tender_item.id, ~tender_item.brand, ~tender_item.model, ";
		$sqlstr.= "~tender_item.description, ~tender_item.classification, ~tender_item.quote,   ";
		$sqlstr.= "~tender_item.budget,   ";
		$sqlstr.= "~category_sub.name AS category_sub, ";
		$sqlstr.= "~category.name AS category ";
		$sqlstr.= "FROM ~users, ~tender_item, ~organisation,";
		$sqlstr.= "~organisation_branch, ~category_supplier, ~category, ~category_sub ";
		$sqlstr.= "WHERE ~users.status > 0 ";
		$sqlstr.= "AND ~users.classification = '1' ";
		if($agent) {
			$sqlstr.= "AND ~users.id = '".$agent."' ";
		}
		$sqlstr.= "AND ~users.organisation = ~organisation.id ";
		$sqlstr.= "AND ~organisation.status = '1' ";
		$sqlstr.= "AND ~users.branch = ~organisation_branch.id ";
		$sqlstr.= "AND ~organisation_branch.status = '1' ";
		$sqlstr.= "AND ~tender_item.status = '2' ";
		$sqlstr.= "AND ~tender_item.category_sub = ~category_sub.id ";
		$sqlstr.= "AND ~category_sub.category = ~category.id ";
		
		$sqlstr.= "AND (";
		$sqlstr.= "		(~organisation.id = ~category_supplier.supplier ";
		$sqlstr.= "		AND ~category_supplier.status = '1' ";
		$sqlstr.= "		AND ~tender_item.category_sub = ~category_supplier.category_sub) ";
		
		$sqlstr.= "	OR ~tender_item.classification > 2 )";
		
		$sqlstr.= "AND ~tender_item.tender = '".$tender."' ";
		$sqlstr.= "ORDER BY ~tender_item.classification ASC, ~category.name ASC, ~category_sub.name ASC ";
		$new_database = new database;
		$new_database->parse_sql($sqlstr,$cfg);
		// execute the query
 		$all = $conn->GetAll($sqlstr);
       	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);
			// set the response error
			$this->error[] = "1065";
			// response
			return FALSE;
       	}
		// return user info
		return $all;
	}
	
	/**
	 * find suppliers that provide an item
	 *
	 * @param integer organisation Organisation ID
	 * @param integer subcat the Sub Category ID
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param mixed message id on success or FALSE on error  
	 */
	function include_item($organisation,$subcat,$cfg,$conn) {
		$new_supplier = new supplier;
		return $new_supplier->provide_item($organisation,$subcat,$cfg,$conn);
	}
	
	
	/**
	 * calculates the difference between two dates
	 *
	 * @param string date1 First date
	 * @param string date2 Second date
	 *
	 * @param array difference in (days,hours,minutes,seconds)  
	 */
	function date_diff($date1,$date2) {
		$s = strtotime($date2)-strtotime($date1);
		$d = intval($s/86400); 
		$s -= $d*86400;
		$h = intval($s/3600);
		$s -= $h*3600;
		$m = intval($s/60); 
		$s -= $m*60;
		return array("d"=>$d,"h"=>$h,"m"=>$m,"s"=>$s);
	}
	
	/**
	 * calculates the closure of a tender
	 *
	 * @param mixed cfg The Config settings of tendersystem
	 *
	 * @param mixed TRUE on success or FALSE on error  
	 */
	function calculate_closure($cfg) {
		// if date provided determine if it is valid
		if ($cfg['tender']['date'] && $this->tender['closedate'] && $this->tender['closehour'] && $this->tender['closemin']) {
			// set the time
			$date_limit = date("YmdHi",ts_time($cfg) + $cfg['time']['default']*60);
			$date_array = explode("-",$this->tender['closedate']);
			$test_datetime = $date_array[0].$date_array[1].$date_array[2].$this->tender['closehour'].$this->tender['closemin'];
			// determine if future date
			if ($test_datetime >= $date_limit) {
				// closing date is correct
				$close_date = mktime($this->tender['closehour'],$this->tender['closemin'],"00",$date_array[1],$date_array[2],$date_array[0]);
				$close_date = $this->get_valid_time($close_date,$cfg['time']['within']*60,$cfg);
			}
		}
		if(!$close_date) {
			// increase tender closing time for every item above 5
			$item_over = count($this->tender['item']) - 5;
			$item_increment = max(0,$item_over) * ($cfg['time']['increment'] * 60);
			// calculate amount of open second
			$seconds = min($cfg['time']['max']*60 , $cfg['time']['default']*60 + $item_increment);
			// calculate closing time
			$time = ts_time($cfg) + $seconds;
			// check if valid closing time
			$close_date = $this->get_valid_time($time,$cfg['time']['within']*60,$cfg);
		}
		// error handling
		if (!$close_date) {
			// no closing date could be established
			$this->error[] = "289";
			// instantiate the error class and log
			$new_error = new error;
			$error_text = "Closing time cold not be calculated.";
			$new_error->log("Tender","critical",$error_text,$cfg);
		} else {
			// set the closing date and time
			$this->tender['closing']['time'] = date("H:i:s",$close_date);
			$this->tender['closing']['date'] = date("Y-m-d",$close_date);
			// convert closing to display format
			$this->response['closing'] = date($cfg['date']['format']." ".$cfg['time']['format'],$close_date)." ".$cfg['local']['display'];
			// return the closing time
			return $close_date;
		}
	}

	/**
	 * gets generic products
	 *
	 * @param integer organisation Organisation ID
	 * @param array user user information 
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 * @param string product product name
	 *
	 * @param mixed array of generic products on success or FALSE on error
	 */
	function get_generic_products($user,$cfg,$conn,$product="") {
		// set the complete file path
		$file_path = $cfg['file']."/modules/tender/language/".$user['language'];
		// open the directory
		$dir = opendir($file_path);
		// if folder accessible
		if ($dir) {
			// loop through all the files (modules)
			while(($file=readdir($dir)) !== FALSE) {
				// determine if hidden file not included
				if($file{0} != "." && $file != "CVS" 
					&& strpos($file,".xml") !== FALSE 
					&& strpos($file,"xml~") === FALSE
					&& $file != "commodity.xml") {
						// set the generic products into an array
						$products[] = str_replace(".xml","",$file);
				}
			}
			// close the directory
			closedir($dir);
		}
		// instantiate object
		$new_category = new category;
		// go through all the modules to determine if it has a generate report
		if(is_array($products)) {
			$index = $xml_array = $xml_params['product'] = array();
			foreach ($products as $value) {
				$xml_content = file_get_contents($cfg['file']."/modules/tender/language/".$user['language']."/".$value.".xml");
				// create parser
				$xml_parser = xml_parser_create();
				// parse xml into $xml_array
				$good_parse = xml_parse_into_struct($xml_parser, $xml_content, $xml_array, $index);
				// free parser
				xml_parser_free($xml_parser);
				if($good_parse) {
					if($xml_array[$index['CATEGORY_SUB'][0]]['type'] == "complete") {
						$sub_cat = $xml_array[$index['CATEGORY_SUB'][0]]['value'];
					} else {
						// find the options
						for($i = $index['CATEGORY_SUB'][0] + 1; $i < end($index['CATEGORY_SUB']) - 1; $i++) {
							if($xml_array[$i]['tag'] == "ID") {
								$sub_cat[] = $xml_array[$i]['value'];
							}
						}
					}
					$category_classification = $xml_array[$index['CLASSIFICATION'][0]]['value'];
					$button_name = $xml_array[$index['BUTTON'][0]]['value'];
					$product_available = $this->category_available($sub_cat,'',$category_classification,$cfg,$conn);
					if(!empty($product_available) || !$cfg['procure']['supplier']['required']) {
						$to_add['value'] = $button_name;
						$to_add['name'] = $value;
						$parent = $xml_array[$index['PRODUCT'][0]]['attributes']['PARENT'];
						if($parent) {
							if(strcasecmp($parent,$product) == "0") {
								array_unshift($xml_params['product'],$to_add);
							}
						} else {
							array_push($xml_params['product'],$to_add);
						}
					}
					unset($sub_cat);
				}
			}
		}
		return $xml_params['product'];
	}
	
	function category_available($subcat_id,$sub_area,$classification,$cfg,$conn) {
       // query database to find out if there are suppliers available for the sub category
       	$sqlstr = "SELECT DISTINCT ~category.id AS categoryid, ~category.name AS categoryname ";
		$sqlstr.= "FROM ~category, ~category_sub, ~category_supplier, ";
		$sqlstr.= "~organisation_supplier, ~area_supplier, ~organisation supplier ";
		$sqlstr.= "WHERE ~category_sub.status = '1' ";
		// category_sub id
		if ($subcat_id) {
			if(is_array($subcat_id)) {
				if(count($subcat_id) > 1) {
					$sqlstr.= "AND ( ~category_sub.id = ".$subcat_id[0]." ";
					for($i = 1; $i < count($subcat_id); $i++) {
						$sqlstr.= "OR ~category_sub.id = ".$subcat_id[$i]." ";
					}
					$sqlstr.= ") ";
				} elseif(count($subcat_id) == 1) {
					$sqlstr.= "AND ~category_sub.id = ".$subcat_id[0]." ";
				}
			} else {
				// include subcategory if reqired
				$sqlstr.= "AND ~category_sub.id = '".$subcat_id."' ";
			}
		}
		$sqlstr.= "AND ~category_sub.category = ~category.id ";
		$sqlstr.= "AND ~category.classification = '".$classification."' ";
		$sqlstr.= "AND ~category.status = '1' ";
		$sqlstr.= "AND ~category_sub.id = ~category_supplier.category_sub ";
		$sqlstr.= "AND ~category_supplier.status = '1' ";
		$sqlstr.= "AND ~category_supplier.supplier = supplier.id ";
		$sqlstr.= "AND supplier.status = '1' ";
		$sqlstr.= "AND supplier.id = ~area_supplier.supplier ";
		$sqlstr.= "AND ~area_supplier.status = '1' ";
		if($sub_area) {
			$sqlstr.= "AND ~area_supplier.area = '".$sub_area."' ";
		}
		$sqlstr.= "AND supplier.id = ~organisation_supplier.supplier ";
		$sqlstr.= "AND ~organisation_supplier.status = '1' ";
		$sqlstr.= "AND ~organisation_supplier.organisation = '".$this->subscriber['organisation']."' ";
		$sqlstr.= "ORDER BY ~category.name ASC, ~category.id ASC ";
		$new_database = new database;
		$new_database->parse_sql($sqlstr,$cfg);
		$all = $conn->GetAll($sqlstr);
        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);
			// set the response error
			$this->error[] = "1065";
			// response
			return FALSE;
        }
        // return response
        return $all;
	}
	
	function category_sub_available($subcat_id,$sub_area,$classification,$cfg,$conn) {
       // query database to find out if there are suppliers available for the sub category
       	$sqlstr = "SELECT DISTINCT ~category_sub.category AS categoryid, ~category_sub.name AS subcategoryname, ~category_sub.id AS subcategoryid ";
		$sqlstr.= "FROM ~category, ~category_sub, ~category_supplier, ";
		$sqlstr.= "~organisation_supplier, ~area_supplier, ~organisation supplier ";
		$sqlstr.= "WHERE ~category_sub.status = '1' ";
		// category_sub id
		if ($subcat_id) {
			if(is_array($subcat_id)) {
				if(count($subcat_id) > 1) {
					$sqlstr.= "AND ( ~category_sub.id = ".$subcat_id[0]." ";
					for($i = 1; $i < count($subcat_id); $i++) {
						$sqlstr.= "OR ~category_sub.id = ".$subcat_id[$i]." ";
					}
					$sqlstr.= ") ";
				} elseif(count($subcat_id) == 1) {
					$sqlstr.= "AND ~category_sub.id = ".$subcat_id[0]." ";
				}
			} else {
				// include subcategory if reqired
				$sqlstr.= "AND ~category_sub.id = '".$subcat_id."' ";
			}
		}
		$sqlstr.= "AND ~category_sub.category = ~category.id ";
		$sqlstr.= "AND ~category.classification = '".$classification."' ";
		$sqlstr.= "AND ~category.status = '1' ";
		$sqlstr.= "AND ~category_sub.id = ~category_supplier.category_sub ";
		$sqlstr.= "AND ~category_supplier.status = '1' ";
		$sqlstr.= "AND ~category_supplier.supplier = supplier.id ";
		$sqlstr.= "AND supplier.status = '1' ";
		$sqlstr.= "AND supplier.id = ~area_supplier.supplier ";
		$sqlstr.= "AND ~area_supplier.status = '1' ";
		if($sub_area) {
			$sqlstr.= "AND ~area_supplier.area = '".$sub_area."' ";
		}
		$sqlstr.= "AND supplier.id = ~organisation_supplier.supplier ";
		$sqlstr.= "AND ~organisation_supplier.status = '1' ";
		$sqlstr.= "AND ~organisation_supplier.organisation = '".$this->subscriber['organisation']."' ";
		$sqlstr.= "ORDER BY ~category_sub.name ASC ";
		$new_database = new database;
		$new_database->parse_sql($sqlstr,$cfg);
		$all = $conn->GetAll($sqlstr);
        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);
			// set the response error
			$this->error[] = "1065";
			// response
			return FALSE;
        }
        // return response
        return $all;
	}
	
	/**
	 * send approval email to administrator
	 *
	 * @param array admin administrator information
	 * @param array items the tender items
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param mixed message id on successor FALSE on error  
	 */
	function send_email_approve($admin,$items,$cfg,$conn) {
		// include email body template
		$subject = "";
		$html = "";
		require($cfg['file'].'modules/tender/templates/tender_approve.php');
		// set message paramaters
		$message['reference'] = $this->tender['rfq_number'];
		$message['classification'] = "Approval";
		$message['subject'] = $this->lang['approvesubject']." ".$this->tender['rfq_number']."(".$this->tender['rfq_reference'].")";
		$message['body'] = $html;
		$message['name'] = $admin['name']." ".$admin['surname'];
		$message['email'] = $admin['email'];
		// send the email
		$new_message = new message;
		$id = $new_message->send_email_message($message,$admin['usersid'],$this->requester['id'],$cfg,$conn);
		if(!$id) {
			$this->email_error = $new_message->error_text;
			return FALSE;
		}
		return $id;
	}
}
?>