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

/**
* Update 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_update extends procure_update {
	// set paramaters
	var $error;
	
	function tender_update() {
		// allow php5 to work
		$this->error = array();	
	}
	
	function search_supplier_sub($supplierid,$class,$cfg,$conn) {
		// sqlstr
		$sqlstr = "SELECT ~category_sub.id AS subcategoryid, ~category_sub.name AS subcategoryname, ~category.id AS categoryid ";
		$sqlstr.= ",~category_supplier.id AS subcategorysupplierid ";
		$sqlstr.= "FROM ~category, ~category_sub, ~category_supplier ";
		$sqlstr.= "WHERE ~category_sub.status = '1' AND ~category.status = '1' ";
		$sqlstr.= "AND ~category_sub.id = ~category_supplier.category_sub ";
		if($supplierid) {
			$sqlstr.= "AND ~category_supplier.supplier = ".$supplierid."  ";
		}
		$sqlstr.= "AND ~category_supplier.status = '1' ";
		$sqlstr.= "AND ~category.status = '1' ";
		$sqlstr.= "AND ~category_sub.status = '1' ";
		$sqlstr.= "AND ~category.id = ~category_sub.category ";
		if($class) {
			$sqlstr.= "AND ~category.classification = ".$class." ";
		}
		$sqlstr.= "ORDER BY ~category_sub.name ASC, ~category_sub.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;
		}
		return $all;
	}
	
	function create_category_sub_supplier($category_sub,$supplier,$usersid,$cfg,$conn){
		if (!$category_sub){
			// Missing category id
			$this->error[] = "248";
		}
		if (!$supplier){
			// Missing organisation id
			$this->error[] = "78";
		} elseif(!$this->supplier_exists($supplier,$cfg,$conn)) {
			// Supplier id not in database
			$this->error[] = "356";
		}
		if(!empty($this->error)){
			// Error in category class
			$this->error[] = "247";
			return FALSE;
		}
		$new_database = new database;
		$table_name = "~category_supplier";
		// generate an id 
		$insert['id'] = $new_database->get_next_id($table_name,$cfg,$conn);
		$insert['category_sub'] = $category_sub;
		$insert['supplier'] = $supplier;
		$insert['created_by'] = $usersid;
		$insert['status'] = "1";
		$new_database->setTime("created",$insert,"",$cfg);
		$new_database->parse_sql($table_name,$cfg);
		// extcute sql insert
		$conn->AutoExecute($table_name,$insert,"INSERT");
		if ($conn->ErrorMsg()) {
			$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 inserting into Database
			$this->error[] = "1064";
			// response
			return FALSE;
		} 
		// resturn the id
		return $insert['id'];
	}
	
	function delete_category_sub_supplier($id,$usersid,$cfg,$conn) {
		$new_database = new database;
		$update['status'] = "0";
		$update['deleted_by'] = $usersid;
		$new_database->setTime("deleted",$update,"",$cfg);
		$where = "id = ".$id;
		if(!$new_database->update_table("~category_supplier",$update,$where,$cfg,$conn)) {
			if(!empty($new_database->error)) {
				$this->error = array_merge($this->error,$new_database->error);
			}
			// Error in Category Class
			$this->error[] = "247";
			return FALSE;
		} 
		// return that category_brand deleted
      	return TRUE;
	}
	
	/**
	 * search for tenders
	 * 
	 * @param integer value The Tender ID
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param mixed tender_information or FALSE on error  
	 */
	function get_open_tender_for_agent($class,$user,$cfg,$conn) {
		$sqlstr = "SELECT ~tender.id AS tenderid, ~tender.rfq_number, ~tender.status, ";
		$sqlstr.= "~tender.close_date, ~tender.close_time, ~tender.created_date, ";
		$sqlstr.= "~tender.created_time, ";
		$sqlstr.= "~users.id AS usersid,  ";
		$sqlstr.= "~area.name AS areaname, ";
		$sqlstr.= "COUNT(DISTINCT ~tender_item.id) AS itemcount, ";
		$sqlstr.= "COUNT(DISTINCT ~tender_quote.id) AS quotecount ";
		// FROM
		$sqlstr.= "FROM ~tender_request LEFT JOIN ~tender_quote ";
		$sqlstr.= "                     ON ~tender_quote.tender_item = ~tender_request.tender_item ";
		$sqlstr.= "                     AND ~tender_quote.user_id = ~tender_request.user_id ";
		$sqlstr.= "                     AND ~tender_quote.status != 0, ";
		$sqlstr.= "~tender, ~tender_item, ~users, ~area ";
		// WHERE
		$sqlstr.= "WHERE ~tender_request.status = '1' ";
		$sqlstr.= "AND ~tender_request.tender_item = ~tender_item.id ";
		$sqlstr.= "AND ~tender_item.tender = ~tender.id ";
		$sqlstr.= "AND ~tender.user_id = ~users.id ";
		$sqlstr.= "AND ~users.status > 0 ";
		$sqlstr.= "AND ~tender.area = ~area.id ";
		$sqlstr.= "AND ~tender_request.user_id = '".$user['id']."' ";
		$sqlstr.= "AND ~tender.status = 2 ";
		if($class) {
			$sqlstr.= "AND ~tender.classification = '".$class."' ";
		}
		// GROUP BY
		$sqlstr.= "GROUP BY ~tender.id, ~users.id, ~area.name, ";
		$sqlstr.= "~tender.status, ~tender.rfq_number, ~tender.close_date, ";
		$sqlstr.= "~tender.close_time, ~tender.created_date, ~tender.created_time ";
		// ORDER BY
		$sqlstr.= "ORDER BY quotecount ASC, ~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);
		// response
		$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;
	}
	
		/**
	 * Adds a valid Item for an supplier
	 * 
	 * @param array item The Item information
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param integer returns ID of iten Or FALSE on failure  
	 */
	function add_item($item,$cfg,$conn){
		$this->organisation = $item;
		if (!$this->organisation['supplier_id']){
			// Missing organisation id
			$this->error[] = "78";
		}
		if (!$this->organisation['brand']){
			// Missing brand
			$this->error[] = "250";
		}
		if (!$this->organisation['model']){
			// Missing model
			$this->error[] = "50";
		}
		if (!$this->organisation['valid_until']){
			// Missing until valid date
			$this->error[] = "252";
		}
		if (!$this->organisation['category_sub']){
			// Missing category_sub id
			$this->error[] = "249";
		}
		if(!$this->organisation['created_by']) {
			// missing created by
			$this->error[] = "239";
		}
		if(!empty($this->error)){
			// Error in Supplier class 
			$this->error[] = "312";
			return FALSE;
		}
		$new_database = new database;
		$table_name = "~item";
		// no id 
		if(!$this->organisation['id']) {
			// generate an id 
			$this->organisation['id'] = $new_database->get_next_id($table_name,$cfg,$conn);
		}
		// set created time
		$new_database->setTime("created",$this->organisation,"",$cfg);
		$new_database->parse_sql($table_name,$cfg);
		// create sql string 
		$conn->AutoExecute($table_name,$this->organisation,"INSERT");
      	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 inserting into Database
			$this->error[] = "1064";
			// response
			return FALSE;
		} 
		// resturn the id
		return $this->organisation['id'];
	}
	
	/**
	 * deletes a valid Item
	 * 
	 * @param integer ids The Item ID
	 * @param integer usersid User's ID
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param boolean TRUE on success Or FALSE on failure  
	 */
	function delete_item($id,$usersid,$cfg,$conn) {
		$new_database = new database;
		$update['deleted_by'] = $usersid;
		$update['status'] = "0";
		// set deleted time
		$new_database->setTime("deleted",$update,"",$cfg);
		$where = " id = ".$id." ";
		if(!$new_database->update_table("~item",$update,$where,$cfg,$conn)) {
			if(!empty($new_database->error)) {
				$this->error = array_merge($this->error,$new_database->error);
			}
			// Error in Supplier Class
			$this->error[] = "312";
			return FALSE;
		} 
      	return TRUE;
	}
	
	/**
	 * search for valid Items
	 * 
	 * @param array params The Search 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 failure  
	 */
	function search_item($params,$cfg,$conn) {
		$this->organisation = $params;
		// sqlstr
		$sqlstr = "SELECT ~item.id AS itemid, ~item.supplier AS supplierid, ";
		$sqlstr.= "~item.brand, ~item.model, ~item.valid_until, ~item.price, ";
		$sqlstr.= "~item.description , ~item.stock, ~item.status, ";
		$sqlstr.= "~organisation.status AS orgstatus, ~organisation.id AS organisationid , ~organisation.name AS organisationname, ";
		$sqlstr.= "~category_sub.status AS subcategorystatus, ~category_sub.id AS subcategoryid , ~category_sub.name AS subcategory,  ";
		$sqlstr.= "~category.status AS categorystatus, ~category.id AS categoryid , ~category.name AS categoryname  ";
		$sqlstr.= "FROM ~item, ~organisation, ~category_sub, ~category ";
		$sqlstr.= "WHERE ~item.status = '1' ";
		$sqlstr.= "AND ~organisation.status = '1' AND ~item.supplier = ~organisation.id ";
		$sqlstr.= "AND ~category_sub.status = '1' AND ~item.category_sub = ~category_sub.id ";
		$sqlstr.= "AND ~category.status = '1' AND ~category.id = ~category_sub.category ";
		$sqlstr.= "AND ~item.valid_until >= '".date("Y-m-d",ts_time($cfg))."' ";
		if($this->organisation['searchinfo']['organisationid']) {
			$sqlstr.= "AND ~item.supplier = '".$this->organisation['searchinfo']['organisationid']."' ";
		}
		$sqlstr.= "ORDER BY upper(~organisation.name) ASC, upper(~item.brand) ASC, upper(~category.name) ASC, ";
		$sqlstr.= "upper(~category_sub.name) ASC, ~organisation.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;
		}
		return $all;
	}
	
	/**
	 * Determine if supplier supplies a certain Sub Category
	 * 
	 * @param integer supplier The Supplier's Organisation ID
	 * @param integer subcat The Sub Category's ID
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param mixed Sub Category Name Or Empty String or FALSE on SQL failure  
	 */
	function provide_item($supplier,$subcat,$cfg,$conn) {
		// query db to find out which suppliers provide the selected items
		$sqlstr = "SELECT ~category_sub.name ";
		$sqlstr.= "FROM ~organisation, ~category_supplier, ~category_sub ";
		$sqlstr.= "WHERE ~organisation.id = '".$supplier."' ";
		$sqlstr.= "AND ~organisation.id = ~category_supplier.supplier ";
		$sqlstr.= "AND ~category_supplier.category_sub = '".$subcat."' ";
		$sqlstr.= "AND ~category_supplier.category_sub = ~category_sub.id ";
		$sqlstr.= "AND ~category_supplier.status = '1' ";
		$new_database = new database;
		$new_database->parse_sql($sqlstr,$cfg);
		// execute the query
 		$one = $conn->GetOne($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();
			$new_error->log("SQL","critical",$error_text,$cfg);
			// set the response error
			$this->error[] = "1065";
			// response
			return FALSE;
       	}
       	return $one;
	}
	
	/**
	 * Search for Tender Suppliers
	 * 
	 * @param array params Search Information
	 * @param mixed cfg The Config settings of tendersystem
	 * @param [ADOConnection] conn The connection to the database
	 *
	 * @param mixed A array with the organisations or FALSE on error  
	 */
	function supplier_search($params,$cfg,$conn) {
		$this->organisation = $params;
		// sqlstr
		$sqlstr = "SELECT DISTINCT ~organisation.id AS organisationid, ~organisation.name AS organisationname, ~organisation.status, ";
		$sqlstr.= "~organisation.classification, ~organisation.rep_user_id ";
		$sqlstr.= "FROM ~organisation, ~category_sub, ~category , ~category_supplier ";
		// include ~organisation_supplier information for supplier panel
		if($this->organisation['searchinfo']['organisationid']) {
			$sqlstr.= ", ~organisation_supplier ";
		}
		// state and ~area information
		if($this->organisation['searchinfo']['state'] || $this->organisation['searchinfo']['area']) {
			$sqlstr.= ", ~area, ~area_state state, ~area_supplier ";
		}
		$sqlstr.= "WHERE ~organisation.status = '1' ";
		$sqlstr.= "AND ~organisation.classification = '1' ";
		$sqlstr.= "AND ~organisation.id = ~category_supplier.supplier ";
		$sqlstr.= "AND ~category_supplier.category_sub = ~category_sub.id ";
		$sqlstr.= "AND ~category_sub.category = ~category.id ";
		$sqlstr.= "AND ~category_supplier.status = '1' ";
		$sqlstr.= "AND ~category.status = '1' ";
		$sqlstr.= "AND ~category_sub.status = '1' ";
		// include ~organisation_supplier information supplier panel
		if($this->organisation['searchinfo']['organisationid'] && $params['searchinfo']['panel'] == "panel") {
			$sqlstr.= "AND ~organisation.id = ~organisation_supplier.supplier ";
			$sqlstr.= "AND ~organisation_supplier.status = '1' ";
			$sqlstr.= "AND ~organisation_supplier.organisation = ".$this->organisation['searchinfo']['organisationid']."  ";
		}
		if($this->organisation['searchinfo']['state'] || $this->organisation['searchinfo']['area']) {
			$sqlstr.= "AND ~organisation.id = ~area_supplier.supplier ";
			$sqlstr.= "AND ~area_supplier.area = ~area.id ";
			$sqlstr.= "AND ~area.state = state.id ";
			$sqlstr.= "AND ~area_supplier.status = '1' ";
			$sqlstr.= "AND ~area.status = '1' ";
			$sqlstr.= "AND state.status = '1' ";
		}

		if($this->organisation['searchinfo']['state']) {
			$sqlstr.= "AND state.id = ".$this->organisation['searchinfo']['state']." ";
		}
		if($this->organisation['searchinfo']['area']) {
			$sqlstr.= "AND ~area.id = ".$this->organisation['searchinfo']['area']." ";
		}
		if($this->organisation['searchinfo']['category']) {
			$sqlstr.= "AND ~category.id = ".$this->organisation['searchinfo']['category']." ";
		}
		if($this->organisation['searchinfo']['category_sub']) {
			$sqlstr.= "AND ~category_sub.id = ".$this->organisation['searchinfo']['category_sub']." ";
		}
		if ($this->organisation['searchinfo']['searchvalue']) {
			$sqlstr.= "AND upper(~organisation.name) LIKE '%".strtoupper($this->organisation['searchinfo']['searchvalue'])."%' ";
		}
		if ($this->organisation['searchinfo']['organisationname']) {
			$sqlstr.= "AND ~organisation.name = '".$this->organisation['searchinfo']['organisationname']."' ";
		}
		if($this->organisation['searchinfo']['classification']) {
			$sqlstr.= "AND ~organisation.classification = ".$this->organisation['searchinfo']['classification']." ";
		}
		if($this->organisation['searchinfo']['organisation']) {
			$sqlstr.= "AND ~organisation.id = ".$this->organisation['searchinfo']['organisation']." ";
		}
		$sqlstr.= "ORDER BY ~organisation.name ASC, ~organisation.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;
		}
		// get rep info
		$new_users = new user;
		foreach($all as $key => $value) {
			$usersinfo = $new_users->get_info($value['rep_user_id'],$cfg,$conn);
			$all[$key]['repname'] = $usersinfo['name'];
			$all[$key]['repsurname'] = $usersinfo['surname'];
			$all[$key]['repemail'] = $usersinfo['email'];
			$all[$key]['repphone_prefix'] = $usersinfo['phone_prefix'];
			$all[$key]['repphone_work'] = $usersinfo['phone_work'];
			$all[$key]['repphone_mobile'] = $usersinfo['phone_mobile'];
		}
		return $all;
	}
}
?>