<?php
/**********************************************************************
    Copyright (C) FrontAccounting, LLC.
	Released under the terms of the GNU General Public License, GPL, 
	as published by the Free Software Foundation, either version 3 
	of the License, or (at your option) any later version.
    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  
    See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
***********************************************************************/
// ------------------------------------------------------------------------------

function get_supplier_details_to_order(&$order, $supplier_id)
{
	$sql = "SELECT curr_code, supp_name, tax_group_id, supp.tax_included, dimension_id, dimension2_id,
			supp.credit_limit - Sum((ov_amount + ov_gst + ov_discount)) as cur_credit,
				terms.terms, terms.days_before_due, terms.day_in_following_month
		FROM ".TB_PREF."suppliers supp
			 LEFT JOIN ".TB_PREF."supp_trans trans ON supp.supplier_id = trans.supplier_id
			 LEFT JOIN ".TB_PREF."payment_terms terms ON supp.payment_terms=terms.terms_indicator
		WHERE supp.supplier_id = ".db_escape($supplier_id)."
		GROUP BY
			  supp.supp_name";

	$result = db_query($sql, "The supplier details could not be retreived");
	$myrow = db_fetch($result);

	$order->credit = $myrow["cur_credit"];
	$order->terms = array( 
		'description' => $myrow['terms'],
		'days_before_due' => $myrow['days_before_due'], 
		'day_in_following_month' => $myrow['day_in_following_month'] );

	$_POST['supplier_id'] = $supplier_id;
	$_POST['supplier_name'] = $myrow["supp_name"];
	$_POST['curr_code'] = $myrow["curr_code"];
	$_POST['dimension'] = $myrow["dimension_id"];
	$_POST['dimension2'] = $myrow["dimension2_id"];

	$order->set_supplier($supplier_id, $myrow["supp_name"], $myrow["curr_code"], 
		(@$_POST["tax_group_id"]) ? @$_POST["tax_group_id"] : $myrow["tax_group_id"] , $myrow["tax_included"]);
}

//----------------------------------------------------------------------------------------

function delete_po($po)
{
	global $Refs;

	begin_transaction();
	hook_db_prevoid(ST_PURCHORDER, $po);
	$sql = "DELETE FROM ".TB_PREF."purch_orders WHERE order_no=".db_escape($po);
	db_query($sql, "The order header could not be deleted");

	$sql = "DELETE FROM ".TB_PREF."purch_order_details WHERE order_no =".db_escape($po);
	db_query($sql, "The order detail lines could not be deleted");

	$Refs->restore_last(ST_PURCHORDER, $po);
	commit_transaction();
}


//----------------------------------------------------------------------------------------

function add_po(&$po_obj)
{
	global $Refs;

	begin_transaction();
	hook_db_prewrite($po_obj, ST_PURCHORDER);

	if ($po_obj->delivery_date == "")
		$delivery_date = "0000-00-00";
	else
		$delivery_date = date2sql($po_obj->delivery_date);
     /*Insert to purchase order header record */
 $sql = "INSERT INTO ".TB_PREF."purch_orders (supplier_id, Comments, ord_date, reference, 
     	requisition_no, into_stock_location, delivery_address, total, tax_included, prep_amount,req_no,tax_group_id,dimension_id,dimension2_id,ex_rate,payment_terms,payterm,delivery_date_,po_account,port_of_discharge,port_of_loading,delivery_term,seller_contract_number,file_system,is_replacement, broker_id, brokery_amt,no_of_truck,brokery_type) VALUES(";
     $sql .= db_escape($po_obj->supplier_id) . "," .
         db_escape($po_obj->Comments) . ",'" .
         date2sql($po_obj->orig_order_date) . "', " .
		 db_escape($po_obj->reference) . ", " .
         db_escape($po_obj->supp_ref) . ", " .
         db_escape($po_obj->Location) . ", " .
         db_escape($po_obj->delivery_address) . ", " .
         db_escape($po_obj->get_trans_total()). ", " .
         db_escape($po_obj->tax_included). ", " .
         db_escape($po_obj->prep_amount). ", " .
         db_escape($po_obj->req_no). ", " .
         db_escape($po_obj->tax_group_id). ", " .
         db_escape($po_obj->dimension). ", " .
         db_escape($po_obj->dimension2). ", " .
         db_escape(($po_obj->ex_rate) ?  $po_obj->ex_rate : 1). ", " .
         db_escape($po_obj->payment_terms). ", " .
         db_escape($po_obj->payterm). ", 
         '$delivery_date', " .
         db_escape($po_obj->po_account). ", " .
         db_escape($po_obj->port_of_discharge). ", " .
         db_escape($po_obj->port_of_loading). ", " .
         db_escape($po_obj->delivery_term). ", " .
         db_escape($po_obj->seller_contract_number). ", " .
         db_escape($po_obj->file_system). ", " .
         db_escape($po_obj->is_replacement). ", " .
         db_escape($po_obj->broker_id). ", " .
         db_escape($po_obj->brokery_amt). ", " .
         db_escape($po_obj->no_of_truck ? $po_obj->no_of_truck : 0). ", " .
         db_escape($po_obj->brokery_type). ")";

	db_query($sql, "The purchase order header record could not be inserted");

     /*Get the auto increment value of the order number created from the sql above */
     $po_obj->order_no = db_insert_id();

     /*Insert the purchase order detail records */
     foreach ($po_obj->line_items as $line_no => $po_line)
     {
		$sql = "INSERT INTO ".TB_PREF."purch_order_details (order_no, item_code, description, delivery_date, unit_price, quantity_ordered, unit_id, no_of_qty, rate_type, rate) VALUES (";
		$sql .= $po_obj->order_no . ", " . db_escape($po_line->stock_id). "," .
		db_escape($po_line->item_description). ",'" .
		date2sql($po_line->req_del_date) . "'," .
		db_escape($po_line->price) . ", " .
		db_escape($po_line->quantity). ", " .
		db_escape($po_line->unit_id). ", " .
		db_escape($po_line->no_of_qty). ", " .
		db_escape($po_line->rate_type). ", " .
		db_escape($po_line->rate). ")";
		$req_line_id = $po_line->po_detail_rec;
		db_query($sql, "One of the purchase order detail records could not be inserted");
		$po_obj->line_items[$line_no]->po_detail_rec = db_insert_id();
			if (!is_inventory_item($po_line->stock_id))
			{

						$sql = "UPDATE ".TB_PREF."purch_order_details SET 
						in_ward_qty=quantity_ordered,qc_qty=quantity_ordered";
						$sql .= " WHERE  po_detail_item = ".db_escape($po_obj->line_items[$line_no]->po_detail_rec );
						db_query($sql, "could not update request for Purchase");
			}	
     	// if($po_obj->is_replacement == 0){
     	// 	$sql = "UPDATE ".TB_PREF."purch_order_details SET quantity_received=".db_escape($po_line->quantity)." WHERE po_detail_item = ".db_escape($po_obj->line_items[$line_no]->po_detail_rec);
		//    	db_query($sql,"The sales group could not be updated");
     	// }
		if($po_obj->req_no)
	   { 
	   	  $req_row =get_req_line_id($req_line_id);
	   	  if( ($po_line->quantity+$po_line->qty_inv) > $req_row['quantity_ordered'] )
	   	  	$update_qty=abs($req_row['quantity_ordered']-$po_line->qty_inv);
	   	  else
	   	  	$update_qty=$po_line->quantity;


	   	$sql = "UPDATE ".TB_PREF."req_order_details SET 
		  qty_delivered=qty_delivered+".$update_qty;
		$sql .= " WHERE  order_no = ".db_escape($po_obj->req_no)." AND item_code=".db_escape($po_line->stock_id);
		db_query($sql, "could not update request for Purchase");
		// display_error($sql);



	   }

     }

	$Refs->save(ST_PURCHORDER, $po_obj->order_no, $po_obj->reference);

	// if($po_obj->req_no && !is_complete_req($po_obj->req_no))
	// {
	// 	$sql = "UPDATE ".TB_PREF."req_orders SET 
	// 	is_purchase_request=2 , comments=".db_escape($po_obj->Comments);
	// 	$sql .= " WHERE order_no = ".db_escape($po_obj->req_no);
	// 	// display_error($sql);
	// 	db_query($sql, "could not update request for Purchase");
	// }	

	add_audit_trail(ST_PURCHORDER, $po_obj->order_no, $po_obj->orig_order_date);
	hook_db_postwrite($po_obj, ST_PURCHORDER);
	commit_transaction();

	return $po_obj->order_no;
}

function add_purchase_quotation(&$pq_obj)
{
	global $Refs;

	begin_transaction();
	hook_db_prewrite($pq_obj, ST_PURCHQUOTATION);

     /*Insert to purchase order header record */
     $sql = "INSERT INTO ".TB_PREF."purch_quotation (supplier_id, Comments, ord_date, reference, 
     	requisition_no, into_stock_location, delivery_address, total, tax_included, prep_amount,req_no,tax_group_id) VALUES(";
     $sql .= db_escape($pq_obj->supplier_id) . "," .
         db_escape($pq_obj->Comments) . ",'" .
         date2sql($pq_obj->orig_order_date) . "', " .
		 db_escape($pq_obj->reference) . ", " .
         db_escape($pq_obj->supp_ref) . ", " .
         db_escape($pq_obj->Location) . ", " .
         db_escape($pq_obj->delivery_address) . ", " .
         db_escape($pq_obj->get_trans_total()). ", " .
         db_escape($pq_obj->tax_included). ", " .
         db_escape($pq_obj->prep_amount). ", " .
         db_escape($pq_obj->req_no). ", " .
         db_escape($pq_obj->tax_group_id). ")";
        //  display_error($pq_obj->supplier_id);
		 // display_error($sql);
         // exit();

	db_query($sql, "The purchase quotation header record could not be inserted");

     /*Get the auto increment value of the order number created from the sql above */
     $pq_obj->order_no = db_insert_id();

//   display_error( $pq_obj->order_no." in side");
     /*Insert the purchase order detail records */
     foreach ($pq_obj->line_items as $line_no => $pq_line)
     {
		$sql = "INSERT INTO ".TB_PREF."purch_quotation_details (order_no, item_code, description, delivery_date,	unit_price,	quantity_ordered) VALUES (";
		$sql .= $pq_obj->order_no . ", " . db_escape($pq_line->stock_id). "," .
		db_escape($pq_line->item_description). ",'" .
		date2sql($pq_line->req_del_date) . "'," .
		db_escape($pq_line->price) . ", " .
		db_escape($pq_line->quantity). ")";
		db_query($sql, "One of the purchase quotation records could not be inserted");
		$pq_obj->line_items[$line_no]->po_detail_rec = db_insert_id();
		 // display_error($sql);
         // exit();
     }

	$Refs->save(ST_PURCHQUOTATION, $pq_obj->order_no, $pq_obj->reference);

	// if($pq_obj->req_no && !is_complete_req($pq_obj->req_no))
	// {
	// 	$sql = "UPDATE ".TB_PREF."req_orders SET 
	// 	is_purchase_request=2 , comments=".db_escape($pq_obj->Comments);
	// 	$sql .= " WHERE order_no = ".db_escape($pq_obj->req_no);
	// 	// display_error($sql);
	// 	db_query($sql, "could not update request for Purchase");
	// }	

	add_audit_trail(ST_PURCHQUOTATION, $pq_obj->order_no, $pq_obj->orig_order_date);
	hook_db_postwrite($pq_obj, ST_PURCHQUOTATION);
	commit_transaction();

	return $pq_obj->order_no;
}
//----------------------------------------------------------------------------------------
function add_requsition(&$po_obj)
{
	global $Refs;

	begin_transaction();
	hook_db_prewrite($po_obj, ST_REQUISITION);
	    if($po_obj->tax_included=='')
	$po_obj->tax_included= 0;

     /*Insert to purchase order header record */
     $sql = "INSERT INTO ".TB_PREF."req_orders (supplier_id, Comments, ord_date, reference, 
     	requisition_no, into_stock_location, delivery_address, total, tax_included, prep_amount,request_location, wo_order,is_purchase_request,is_purch_req_no) VALUES(";
     $sql .= db_escape($po_obj->supplier_id) . "," .
         db_escape($po_obj->Comments) . ",'" .
         date2sql($po_obj->orig_order_date) . "', " .
		 db_escape($po_obj->reference) . ", " .
         db_escape($po_obj->supp_ref) . ", " .
         db_escape($po_obj->Location) . ", " .
         db_escape($po_obj->delivery_address) . ", " .
         db_escape($po_obj->get_trans_total()). ", " .
         db_escape($po_obj->tax_included). ", " .
         db_escape($po_obj->prep_amount). ", " .
         db_escape($po_obj->receive_loc). ", " .
         db_escape($po_obj->wo_order). ", " .
         db_escape($po_obj->is_purchase_request). ", " .
         db_escape($po_obj->req_purch). ")";

// display_error($sql);
   	db_query($sql, "The purchase order header record could not be inserted");

     /*Get the auto increment value of the order number created from the sql above */
     $po_obj->order_no = db_insert_id();

     /*Insert the purchase order detail records */
     foreach ($po_obj->line_items as $line_no => $po_line)
     {
		$sql = "INSERT INTO ".TB_PREF."req_order_details (order_no, item_code, description, delivery_date,	unit_price,	quantity_ordered) VALUES (";
		$sql .= $po_obj->order_no . ", " . db_escape($po_line->stock_id). "," .
		db_escape($po_line->item_description). ",'" .
		date2sql($po_line->req_del_date) . "'," .
		db_escape($po_line->price) . ", " .
		db_escape($po_line->quantity). ")";
		db_query($sql, "One of the purchase order detail records could not be inserted");
		$po_obj->line_items[$line_no]->po_detail_rec = db_insert_id();
     }

	$Refs->save(ST_REQUISITION, $po_obj->order_no, $po_obj->reference);

	add_audit_trail(ST_REQUISITION, $po_obj->order_no, $po_obj->orig_order_date, _(" Request "));
	hook_db_postwrite($po_obj, ST_REQUISITION);
	commit_transaction();

	return $po_obj->order_no;
}



function update_req(&$po_obj)
{
	begin_transaction();
	hook_db_prewrite($po_obj, ST_REQUISITION);

    /*Update the purchase order header with any changes */
    $sql = "UPDATE ".TB_PREF."req_orders SET Comments=" . db_escape($po_obj->Comments) . ",
		requisition_no= ". db_escape( $po_obj->supp_ref). ",
		into_stock_location=" . db_escape($po_obj->Location). ",
			request_location=" . db_escape($po_obj->receive_loc). ",
		ord_date='" . date2sql($po_obj->orig_order_date) . "',
		delivery_address=" . db_escape($po_obj->delivery_address).",
		total=". db_escape($po_obj->get_trans_total()).",
		prep_amount=". db_escape($po_obj->prep_amount).",
		tax_included=". db_escape($po_obj->tax_included);
    $sql .= " WHERE order_no = " . $po_obj->req_no;
	db_query($sql, "The purchase order could not be updated");

	$sql = "DELETE FROM ".TB_PREF."req_order_details WHERE order_no="
		.db_escape($po_obj->req_no);
	db_query($sql, "could not delete old purch order details");

    /*Now Update the purchase order detail records */
    foreach ($po_obj->line_items as $po_line)
    {
    		$sql = "INSERT INTO ".TB_PREF."req_order_details (order_no, item_code, description, delivery_date,	unit_price,	quantity_ordered) VALUES (";
		$sql .= $po_obj->req_no . ", " . db_escape($po_line->stock_id). "," .
		db_escape($po_line->item_description). ",'" .
		date2sql($po_line->req_del_date) . "'," .
		db_escape($po_line->price) . ", " .
		db_escape($po_line->quantity). ")";
		db_query($sql, "One of the purchase order detail records could not be updated");
    }


	add_audit_trail($po_obj->trans_type, $po_obj->req_no, Today(), _("Updated."));
	hook_db_postwrite($po_obj, ST_REQUISITION);
	commit_transaction();

	return $po_obj->req_no;
}

//----------------------------------------------------------------------------------------

function update_po(&$po_obj)
{
	begin_transaction();
	hook_db_prewrite($po_obj, ST_PURCHORDER);

    /*Update the purchase order header with any changes */
    $sql = "UPDATE ".TB_PREF."purch_orders SET Comments=" . db_escape($po_obj->Comments) . ",
		requisition_no= ". db_escape( $po_obj->supp_ref). ",
		into_stock_location=" . db_escape($po_obj->Location). ",
		ord_date='" . date2sql($po_obj->orig_order_date) . "',
		delivery_address=" . db_escape($po_obj->delivery_address).",
		total=". db_escape($po_obj->get_trans_total()).",
		prep_amount=". db_escape($po_obj->prep_amount).",
		payterm=". db_escape($po_obj->payterm).",
		dimension_id=". db_escape($po_obj->dimension).",
		dimension2_id=". db_escape($po_obj->dimension2).",
		payment_terms=". db_escape($po_obj->payment_terms).",
		ex_rate=". db_escape(($po_obj->ex_rate) ?  $po_obj->ex_rate : 1).",
		tax_group_id=". db_escape($po_obj->tax_group_id).",
		tax_included=". db_escape($po_obj->tax_included).",
		delivery_date_='". sql2date($po_obj->delivery_date)."',
		po_account=". db_escape($po_obj->po_account).",
		port_of_discharge=". db_escape($po_obj->port_of_discharge).",
		port_of_loading=". db_escape($po_obj->port_of_loading).",
		delivery_term=". db_escape($po_obj->delivery_term).",
		seller_contract_number=". db_escape($po_obj->seller_contract_number).",
		file_system=". db_escape($po_obj->file_system).",
		broker_id=". db_escape($po_obj->broker_id).",
		no_of_truck=". db_escape($po_obj->no_of_truck).",
		
		brokery_amt=". db_escape($po_obj->brokery_amt);
    $sql .= " WHERE order_no = " . $po_obj->order_no;
	db_query($sql, "The purchase order could not be updated");

	$sql = "DELETE FROM ".TB_PREF."purch_order_details WHERE order_no="
		.db_escape($po_obj->order_no);
	db_query($sql, "could not delete old purch order details");

    /*Now Update the purchase order detail records */
    foreach ($po_obj->line_items as $po_line)
    {
    	$sql = "INSERT INTO ".TB_PREF."purch_order_details (po_detail_item, order_no, item_code, 
    		description, delivery_date, unit_price, quantity_ordered, quantity_received,unit_id,no_of_qty) VALUES ("
			.db_escape($po_line->po_detail_rec ? $po_line->po_detail_rec : 0). ","
			.$po_obj->order_no . ","
			.db_escape($po_line->stock_id). ","
			.db_escape($po_line->item_description). ",'"
			.date2sql($po_line->req_del_date) . "',"
			.db_escape($po_line->price) . ", "
			.db_escape($po_line->quantity) . ", "
			.db_escape($po_line->qty_received) . ", "
			.db_escape($po_line->unit_id) . ", "
			.db_escape($po_line->no_of_qty) . ")";
		db_query($sql, "One of the purchase order detail records could not be updated");
		
		
		if (!is_inventory_item($po_line->stock_id))
			{

						$sql = "UPDATE ".TB_PREF."purch_order_details SET 
						in_ward_qty=quantity_ordered,qc_qty=quantity_ordered";
						$sql .= " WHERE  po_detail_item = ".db_escape($po_line->po_detail_rec ? $po_line->po_detail_rec : db_insert_id());
						db_query($sql, "could not update request for Purchase");
			}
    }

	reallocate_payments($po_obj->order_no, ST_PURCHORDER, $po_obj->orig_order_date, $po_obj->get_trans_total(), $po_obj->prepayments, $po_obj->supplier_id);

	add_audit_trail($po_obj->trans_type, $po_obj->order_no, Today(), _("Updated."));
	hook_db_postwrite($po_obj, ST_PURCHORDER);
	commit_transaction();

	return $po_obj->order_no;
}




function update_pq(&$pq_obj)
{
	begin_transaction();
	hook_db_prewrite($pq_obj, ST_PURCHQUOTATION);

    /*Update the purchase order header with any changes */
    $sql = "UPDATE ".TB_PREF."purch_quotation SET Comments=" . db_escape($pq_obj->Comments) . ",
		requisition_no= ". db_escape( $pq_obj->supp_ref). ",
		into_stock_location=" . db_escape($pq_obj->Location). ",
		ord_date='" . date2sql($pq_obj->orig_order_date) . "',
		delivery_address=" . db_escape($pq_obj->delivery_address).",
		total=". db_escape($pq_obj->get_trans_total()).",
		prep_amount=". db_escape($pq_obj->prep_amount).",
		tax_group_id=". db_escape($pq_obj->tax_group_id).",
		tax_included=". db_escape($pq_obj->tax_included);
    $sql .= " WHERE order_no = " . $pq_obj->order_no;
	db_query($sql, "The purchase order could not be updated");

	$sql = "DELETE FROM ".TB_PREF."purch_quotation_details WHERE order_no="
		.db_escape($pq_obj->order_no);
	db_query($sql, "could not delete old purch order details");

    /*Now Update the purchase order detail records */
    foreach ($pq_obj->line_items as $pq_line)
    {
    	$sql = "INSERT INTO ".TB_PREF."purch_quotation_details (po_detail_item, order_no, item_code, 
    		description, delivery_date, unit_price, quantity_ordered, quantity_received) VALUES ("
			.db_escape($pq_line->po_detail_rec ? $pq_line->po_detail_rec : 0). ","
			.$pq_obj->order_no . ","
			.db_escape($pq_line->stock_id). ","
			.db_escape($pq_line->item_description). ",'"
			.date2sql($pq_line->req_del_date) . "',"
			.db_escape($pq_line->price) . ", "
			.db_escape($pq_line->quantity) . ", "
			.db_escape($pq_line->qty_received) . ")";
		db_query($sql, "One of the purchase order detail records could not be updated");
    }

	reallocate_payments($pq_obj->order_no, ST_PURCHQUOTATION, $pq_obj->orig_order_date, $pq_obj->get_trans_total(), $pq_obj->prepayments, $pq_obj->supplier_id);

	add_audit_trail($pq_obj->trans_type, $pq_obj->order_no, Today(), _("Updated."));
	hook_db_postwrite($pq_obj, ST_PURCHQUOTATION);
	commit_transaction();

	return $pq_obj->order_no;
}


//----------------------------------------------------------------------------------------

function get_po($order_no) {
    $sql = "SELECT * FROM ".TB_PREF."purch_orders WHERE order_no = ".db_escape($order_no);
    $result = db_query($sql);

    return db_fetch($result);
}

//----------------------------------------------------------------------------------------

function read_po_header($order_no, &$order)
{
    	$sql = "SELECT po.*, supplier.*, loc.location_name , po.dimension_id as dim_id ,po.dimension2_id as dim2_id, po.tax_group_id as tax_group_id_ord ,po.payment_terms as pay_term
		FROM ".TB_PREF."purch_orders po,"
			.TB_PREF."suppliers supplier,"
			.TB_PREF."locations loc 
		WHERE po.supplier_id = supplier.supplier_id
		AND loc.loc_code = into_stock_location
		AND po.order_no = ".db_escape($order_no);

   	$result = db_query($sql, "The order cannot be retrieved");

	if (db_num_rows($result) == 1)
	{

      	$myrow = db_fetch($result);

      	$order->trans_type = ST_PURCHORDER;
      	$order->order_no = $order_no;

      	$order->set_supplier($myrow["supplier_id"], $myrow["supp_name"], $myrow["curr_code"],
      		$myrow['tax_group_id_ord'], $myrow["tax_included"]);

		$order->credit = get_current_supp_credit($order->supplier_id);

      	$order->orig_order_date = sql2date($myrow["ord_date"]);
      	$order->Comments = $myrow["comments"];
      	$order->Location = $myrow["into_stock_location"];
      	$order->supp_ref = $myrow["requisition_no"];
		$order->reference = $myrow["reference"];
		$order->dimension = $myrow["dim_id"];
		$order->dimension2 = $myrow["dim2_id"];
      	$order->delivery_address = $myrow["delivery_address"];
      	$order->alloc = $myrow["alloc"];
      	$order->prep_amount = $myrow["prep_amount"];
      	$order->payment_terms = $myrow["pay_term"];
      	// display_error($order->payment_terms);
      	$order->req_no = $myrow["req_no"];
      	$order->ex_rate = $myrow["ex_rate"];
  		$order->qc_req_date = sql2date($myrow["qc_req_date"]);
  		$order->delivery_date = sql2date($myrow["delivery_date_"]);
  		$order->po_account = $myrow["po_account"];
  		$order->port_of_discharge = $myrow["port_of_discharge"];
  		$order->port_of_loading = $myrow["port_of_loading"];
  		$order->delivery_term = $myrow["delivery_term"];
  		$order->seller_contract_number = $myrow["seller_contract_number"];
  		$order->file_system = $myrow["file_system"];
      	$order->prepayments = get_payments_for($order_no, ST_PURCHORDER, $myrow["supplier_id"]);
  		$order->is_replacement = $myrow["is_replacement"];
  		$order->broker_id = $myrow["broker_id"];
  		$order->brokery_amt = $myrow["brokery_amt"];
  		$order->no_of_truck = $myrow["no_of_truck"];
  				$order->brokery_type = $myrow["brokery_type"];
  		
      	
      	return true;
	}

	display_db_error("FATAL : duplicate purchase order found", "", true);
	return false;
}




function read_pq_header($order_no, &$order)
{
    	$sql = "SELECT pq.*, supplier.*, loc.location_name , pq.tax_group_id as tax_group_id_ord 
		FROM ".TB_PREF."purch_quotation pq,"
			.TB_PREF."suppliers supplier,"
			.TB_PREF."locations loc 
		WHERE pq.supplier_id = supplier.supplier_id
		AND loc.loc_code = into_stock_location
		AND pq.order_no = ".db_escape($order_no);

   	$result = db_query($sql, "The order cannot be retrieved");

	if (db_num_rows($result) == 1)
	{

      	$myrow = db_fetch($result);

      	$order->trans_type = ST_PURCHQUOTATION;
      	$order->order_no = $order_no;

      	$order->set_supplier($myrow["supplier_id"], $myrow["supp_name"], $myrow["curr_code"],
      		$myrow['tax_group_id_ord'], $myrow["tax_included"]);

		$order->credit = get_current_supp_credit($order->supplier_id);

      	$order->orig_order_date = sql2date($myrow["ord_date"]);
      	$order->Comments = $myrow["comments"];
      	$order->Location = $myrow["into_stock_location"];
      	$order->supp_ref = $myrow["requisition_no"];
      	$order->reference = $myrow["reference"];

      	$order->delivery_address = $myrow["delivery_address"];
      	$order->alloc = $myrow["alloc"];
      	$order->prep_amount = $myrow["prep_amount"];
      	$order->req_no = $myrow["req_no"];

      	$order->prepayments = get_payments_for($order_no, ST_PURCHQUOTATION, $myrow["supplier_id"]);

      	return true;
	}

	display_db_error("FATAL : duplicate purchase quotation found", "", true);
	return false;
}

//----------------------------------------------------------------------------------------


function read_req_header($order_no, &$order, $internal_view=false)
{
    	$sql = "SELECT po.*, supplier.*, loc.location_name 
		FROM ".TB_PREF."req_orders po,"
			.TB_PREF."suppliers supplier,"
			.TB_PREF."locations loc
		WHERE po.supplier_id = supplier.supplier_id
		AND loc.loc_code = into_stock_location
		AND po.order_no = ".db_escape($order_no);

   	$result = db_query($sql, "The order cannot be retrieved");

	if (db_num_rows($result) == 1)
	{

      	$myrow = db_fetch($result);

       if(!$order->req_no)
       {
      	$order->trans_type = ST_REQUISITION;
      	$order->order_no = $order_no;
       }

      	$order->set_supplier($myrow["supplier_id"], $myrow["supp_name"], $myrow["curr_code"],
      		$myrow['tax_group_id'], $myrow["tax_included"]);

		$order->credit = get_current_supp_credit($order->supplier_id);

      	$order->orig_order_date = sql2date($myrow["ord_date"]);
      	$order->Comments = $myrow["comments"];
      	$order->Location = $myrow["into_stock_location"];

      	$order->receive_loc = $myrow["request_location"];
      	$order->supp_ref = $myrow["requisition_no"];
      	if($order->req_no!="" && $internal_view==true)
      	$order->reference = $myrow["reference"];
         if(!$_GET['req_purch'])
          $order->req_purch = $myrow["is_purchase_request"];
      
        	// display_error(	$order->reference);
      $order->wo_order = $myrow["wo_order"];
            $order->gl_entry_no = $myrow["gl_entry_no"];

      	$order->delivery_address = $myrow["delivery_address"];
      	$order->alloc = $myrow["alloc"];
      	$order->prep_amount = $myrow["prep_amount"];
      	$order->prepayments = get_payments_for($order_no, ST_REQUISITION, $myrow["supplier_id"]);

      	return true;
	}

	display_db_error("FATAL : duplicate purchase order found", "", true);
	return false;
}
//----------------------------------------------------------------------------------------
function read_po_items($order_no, &$order, $open_items_only=false)
{
	/*now populate the line po array with the purchase order details records */

	$sql = "SELECT poline.*, units
		FROM ".TB_PREF."purch_order_details poline
			LEFT JOIN ".TB_PREF."stock_master item	ON poline.item_code=item.stock_id
		WHERE order_no =".db_escape($order_no);

    if ($open_items_only)
 		$sql .= " AND (poline.quantity_ordered > poline.quantity_received) ";

	$sql .= " ORDER BY po_detail_item";

	$result = db_query($sql, "The lines on the purchase order cannot be retrieved");

    if (db_num_rows($result) > 0)
    {
		while ($myrow = db_fetch($result))
        {
        	$data = get_purchase_data($order->supplier_id, $myrow['item_code']);
        	if ($data !== false)
        	{
        		if ($data['supplier_description'] != "")
        			$myrow['description'] = $data['supplier_description'];
        	}		
            if (is_null($myrow["units"]))
            {
		       	$units = "";
            }
            else
            {
                $units = $myrow["units"];
            }

            if ($order->add_to_order($order->lines_on_order, $myrow["item_code"],
            	$myrow["quantity_ordered"],$myrow["description"],
            	$myrow["unit_price"],$units, sql2date($myrow["delivery_date"]),
            	$myrow["qty_invoiced"], $myrow["quantity_received"], null, null, $myrow["in_ward_qty"], $myrow["out_ward_qty"], $myrow["qc_qty"], 0,null,0,$myrow["unit_id"],$myrow["no_of_qty"], $myrow["rate_type"], $myrow["rate"])) {
				$order->line_items[$order->lines_on_order-1]->po_detail_rec = $myrow["po_detail_item"];
			}
        } /* line po from purchase order details */
    } //end of checks on returned data set
}

//----------------------------------------------------------------------------------------

function read_req_items($order_no, &$order, $open_items_only=false, $internal_view = false)
{
	/*now populate the line po array with the purchase order details records */

	$sql = "SELECT poline.*, units
		FROM ".TB_PREF."req_order_details poline
			LEFT JOIN ".TB_PREF."stock_master item	ON poline.item_code=item.stock_id
		WHERE order_no =".db_escape($order_no);

    if ($open_items_only)
 		$sql .= " AND (poline.quantity_ordered > poline.quantity_received) ";

	$sql .= " ORDER BY po_detail_item";


	$result = db_query($sql, "The lines on the purchase order cannot be retrieved");

    if (db_num_rows($result) > 0)
    {
		while ($myrow = db_fetch($result))
        {
        	$data = get_purchase_data($order->supplier_id, $myrow['item_code']);
        	if ($data !== false)
        	{
        		if ($data['supplier_description'] != "")
        			$myrow['description'] = $data['supplier_description'];
        	}		
            if (is_null($myrow["units"]))
            {
		       	$units = "";
            }
            else
            {
                $units = $myrow["units"];
            }      


              if($internal_view)
              $qty = $myrow['quantity_ordered'];
              	else
             $qty= ($order->req_no) ?  ($myrow["quantity_ordered"]-$myrow["quantity_received"]-$myrow["qty_delivered"] ) : $myrow["quantity_ordered"];
             
             // $myrow["quantity_received"]=$myrow["quantity_received"]
            if ($order->add_to_order($order->lines_on_order, $myrow["item_code"],$qty
            	,$myrow["description"],
            	$myrow["unit_price"],$units, sql2date($myrow["delivery_date"]),
            	$myrow["qty_delivered"], $myrow["quantity_received"])) {
				$order->line_items[$order->lines_on_order-1]->po_detail_rec = $myrow["po_detail_item"];
			}
        } /* line po from purchase order details */
    } //end of checks on returned data set
}


function read_pq_items($order_no, &$order, $open_items_only=false)
{
	/*now populate the line po array with the purchase order details records */

	$sql = "SELECT pqline.*, units
		FROM ".TB_PREF."purch_quotation_details pqline
			LEFT JOIN ".TB_PREF."stock_master item	ON pqline.item_code=item.stock_id
		WHERE order_no =".db_escape($order_no);

    if ($open_items_only)
 		$sql .= " AND (pqline.quantity_ordered > pqline.quantity_received) ";

	$sql .= " ORDER BY po_detail_item";

	$result = db_query($sql, "The lines on the purchase order cannot be retrieved");

    if (db_num_rows($result) > 0)
    {
		while ($myrow = db_fetch($result))
        {
        	$data = get_purchase_data($order->supplier_id, $myrow['item_code']);
        	if ($data !== false)
        	{
        		if ($data['supplier_description'] != "")
        			$myrow['description'] = $data['supplier_description'];
        	}		
            if (is_null($myrow["units"]))
            {
		       	$units = "";
            }
            else
            {
                $units = $myrow["units"];
            }

            if ($order->add_to_order($order->lines_on_order, $myrow["item_code"],
            	$myrow["quantity_ordered"],$myrow["description"],
            	$myrow["unit_price"],$units, sql2date($myrow["delivery_date"]),
            	$myrow["qty_invoiced"], $myrow["quantity_received"])) {
				$order->line_items[$order->lines_on_order-1]->po_detail_rec = $myrow["po_detail_item"];
			}
        } /* line po from purchase order details */
    } //end of checks on returned data set
}

//----------------------------------------------------------------------------------------

function read_pq($order_no, &$order, $open_items_only=false)
{
	$result = read_pq_header($order_no, $order);

	if ($result)
		read_pq_items($order_no, $order, $open_items_only);
}

function read_po($order_no, &$order, $open_items_only=false)
{
	$result = read_po_header($order_no, $order);

	if ($result)
		read_po_items($order_no, $order, $open_items_only);
}


function read_req($order_no, &$order, $open_items_only=false ,$internal_view=false)
{
	$result = read_req_header($order_no, $order,$internal_view);

	if ($result)
		read_req_items($order_no, $order, $open_items_only,$internal_view);
}

//----------------------------------------------------------------------------------------

function get_po_items($order_no)
{
	$sql = "SELECT item_code, quantity_ordered, quantity_received, qty_invoiced, unit_price
		FROM ".TB_PREF."purch_order_details
		WHERE order_no=".db_escape($order_no)
		." ORDER BY po_detail_item";

	$result = db_query($sql, "could not query purch order details");
    check_db_error("Could not check that the details of the purchase order had not been changed by another user ", $sql);
    return $result;
}
//----------------------------------------------------------------------------------------

function get_short_info($stock_id)
{
	$sql = "SELECT description, units, mb_flag
		FROM ".TB_PREF."stock_master WHERE stock_id = ".db_escape($stock_id);

	return db_query($sql,"The stock details for " . $stock_id . " could not be retrieved");
}

function get_sql_for_po_search_completed($from, $to, $supplier_id=ALL_TEXT, $location=ALL_TEXT,
	$order_number = '', $stock_id = '', $also_closed=false, $order_ref_number='')
{
	$sql = "SELECT 
		porder.order_no, 
		porder.reference, 
		supplier.supp_name, 
		location.location_name,
		porder.requisition_no, 
		porder.ord_date, 
		supplier.curr_code, 
		Sum(line.unit_price*line.quantity_ordered) AS OrderValue,
		Sum(line.delivery_date < '". date2sql(Today()) ."'
		AND (line.quantity_ordered > line.quantity_received)) As OverDue,
		porder.into_stock_location,line.quantity_ordered,line.quantity_received,
		chk.isopen,porder.is_close
		FROM ".TB_PREF."purch_orders as porder
				LEFT JOIN (
					SELECT order_no, SUM(quantity_ordered-quantity_received + quantity_ordered-qty_invoiced) isopen
					FROM ".TB_PREF."purch_order_details
					GROUP BY order_no
				) chk ON chk.order_no=porder.order_no,"
			.TB_PREF."purch_order_details as line, "
			.TB_PREF."suppliers as supplier, "
			.TB_PREF."locations as location
		WHERE porder.order_no = line.order_no
		AND porder.supplier_id = supplier.supplier_id
		AND location.loc_code = porder.into_stock_location ";

	if ($supplier_id != ALL_TEXT)
		$sql .= "AND supplier.supplier_id=".$supplier_id." ";
	if ($order_number != "")
	{
		$sql .= "AND porder.order_no =".db_escape($order_number);
	}elseif ($order_ref_number != "") {
				$sql .= "AND porder.reference LIKE ".db_escape('%'. $order_ref_number . '%');
	}
	else
	{

		$data_after = date2sql($from);
		$date_before = date2sql($to);

		$sql .= " AND porder.ord_date >= '$data_after'";
		$sql .= " AND porder.ord_date <= '$date_before'";

		if ($location != ALL_TEXT)
		{
			$sql .= " AND porder.into_stock_location = ".db_escape($location);
		}
		if ($stock_id !== '')
		{
			$sql .= " AND line.item_code=".db_escape($stock_id);
		}
		if ($supplier_id != ALL_TEXT)
			$sql .= " AND supplier.supplier_id=".db_escape($supplier_id);

	}

	if (!$also_closed)
		$sql .= " AND isopen";
	$sql .= " GROUP BY porder.order_no";
	return $sql;
}


function get_sql_for_pq_search_completed($from, $to, $supplier_id=ALL_TEXT, $location=ALL_TEXT,
	$order_number = '', $stock_id = '', $also_closed=false)
{
	$sql = "SELECT 
		pqrder.order_no, 
		pqrder.reference, 
		supplier.supp_name, 
		location.location_name,
		pqrder.requisition_no, 
		pqrder.ord_date, 
		supplier.curr_code, 
		Sum(line.unit_price*line.quantity_ordered) AS OrderValue,
		Sum(line.delivery_date < '". date2sql(Today()) ."'
		AND (line.quantity_ordered > line.quantity_received)) As OverDue,
		pqrder.into_stock_location,
		chk.isopen,pqrder.is_close
		FROM ".TB_PREF."purch_quotation as pqrder
				LEFT JOIN (
					SELECT order_no, SUM(quantity_ordered-quantity_received + quantity_ordered-qty_invoiced) isopen
					FROM ".TB_PREF."purch_quotation_details
					GROUP BY order_no
				) chk ON chk.order_no=pqrder.order_no,"
			.TB_PREF."purch_quotation_details as line, "
			.TB_PREF."suppliers as supplier, "
			.TB_PREF."locations as location
		WHERE pqrder.order_no = line.order_no
		AND pqrder.supplier_id = supplier.supplier_id
		AND location.loc_code = pqrder.into_stock_location ";

	if ($supplier_id != ALL_TEXT)
		$sql .= "AND supplier.supplier_id=".$supplier_id." ";
	if ($order_number != "")
	{
		$sql .= "AND pqrder.reference LIKE ".db_escape('%'. $order_number . '%');
	}
	else
	{

		$data_after = date2sql($from);
		$date_before = date2sql($to);

		$sql .= " AND pqrder.ord_date >= '$data_after'";
		$sql .= " AND pqrder.ord_date <= '$date_before'";

		if ($location != ALL_TEXT)
		{
			$sql .= " AND pqrder.into_stock_location = ".db_escape($location);
		}
		if ($stock_id !== '')
		{
			$sql .= " AND line.item_code=".db_escape($stock_id);
		}
		if ($supplier_id != ALL_TEXT)
			$sql .= " AND supplier.supplier_id=".db_escape($supplier_id);

	}

	if (!$also_closed)
		$sql .= " AND isopen";
	$sql .= " GROUP BY pqrder.order_no";
	return $sql;
}


function get_sql_for_req_search_completed($from, $to, $supplier_id=ALL_TEXT, $location=ALL_TEXT,
	$order_number = '', $stock_id = '', $also_closed=false,$show_purch_req=null)
{
	$sql = "SELECT 
		porder.order_no, 
		porder.reference, 
		supplier.supp_name, 
		location.location_name,
		porder.requisition_no, 
		porder.ord_date, 
		supplier.curr_code, 
		Sum(line.unit_price*line.quantity_ordered) AS OrderValue,
		Sum(line.delivery_date < '". date2sql(Today()) ."'
		AND (line.quantity_ordered > line.quantity_received)) As OverDue,
		porder.into_stock_location,
		chk.isopen,porder.is_complete,porder.is_purchase_request,chk.issue
		FROM ".TB_PREF."req_orders as porder
				LEFT JOIN (
					SELECT order_no,SUM(quantity_received+qty_delivered) as issue, round(SUM(quantity_ordered-quantity_received-qty_delivered),4) isopen
					FROM ".TB_PREF."req_order_details
					GROUP BY order_no
				) chk ON chk.order_no=porder.order_no,"
			.TB_PREF."req_order_details as line, "
			.TB_PREF."suppliers as supplier, "
			.TB_PREF."locations as location
		WHERE porder.order_no = line.order_no
		AND porder.supplier_id = supplier.supplier_id
		AND location.loc_code = porder.into_stock_location ";

	if ($supplier_id != ALL_TEXT)
		$sql .= "AND supplier.supplier_id=".$supplier_id." ";
	if ($order_number != "")
	{
		$sql .= "AND porder.reference LIKE ".db_escape('%'. $order_number . '%');
	}
	else
	{

		$data_after = date2sql($from);
		$date_before = date2sql($to);

		$sql .= " AND porder.ord_date >= '$data_after'";
		$sql .= " AND porder.ord_date <= '$date_before'";

		if ($location != ALL_TEXT)
		{
			$sql .= " AND porder.into_stock_location = ".db_escape($location);
		}
		if ($stock_id !== '')
		{
			$sql .= " AND line.item_code=".db_escape($stock_id);
		}
		if ($supplier_id != ALL_TEXT)
			$sql .= " AND supplier.supplier_id=".db_escape($supplier_id);

	}

	if ($_SESSION["wa_current_user"]->to_location!='' && !$show_purch_req)
		 $sql.=" AND ( porder.request_location IN (".$_SESSION["wa_current_user"]->location.") OR porder.into_stock_location IN (".$_SESSION["wa_current_user"]->location.") ) ";


// display_error($show_purch_req);
if($show_purch_req!=null)
{
	if(!$show_purch_req)
	$sql .= " AND porder.is_purchase_request = ".db_escape($show_purch_req);
	else
	$sql .= " AND porder.is_purchase_request >0 AND is_closed=0";

}


	if (!$also_closed)
		$sql .= " AND chk.isopen>0 AND is_closed=0";
	$sql .= " GROUP BY porder.order_no";

	// display_error($sql);
	return $sql;
}


function get_sql_for_store_search_completed($from, $to, $supplier_id=ALL_TEXT, $location=ALL_TEXT,
	$order_number = '', $stock_id = '', $also_closed=false)
{
	$sql = "SELECT 
		porder.order_no, 
		porder.reference, 
		supplier.supp_name, 
		porder.ord_date, 
		location.location_name,
	    porder.request_location, 
		-- porder.requisition_no, 
		porder.into_stock_location,
		chk.isopen,porder.is_complete,porder.is_purchase_request,chk.issue,porder.is_closed
		FROM ".TB_PREF."req_orders as porder
				LEFT JOIN (
					SELECT order_no,SUM(quantity_received+qty_delivered) as issue, SUM(ROUND(quantity_ordered,5)-quantity_received-qty_delivered) isopen
					FROM ".TB_PREF."req_order_details
					GROUP BY order_no
				) chk ON chk.order_no=porder.order_no,"
			.TB_PREF."req_order_details as line, "
			.TB_PREF."suppliers as supplier, "
			.TB_PREF."locations as location
		WHERE porder.order_no = line.order_no
		AND porder.supplier_id = supplier.supplier_id
		AND location.loc_code = porder.into_stock_location  AND  ( porder.request_location IN (".$_SESSION["wa_current_user"]->location.") OR porder.into_stock_location IN (".$_SESSION["wa_current_user"]->location.") ) ";

	if ($supplier_id != ALL_TEXT)
		$sql .= "AND supplier.supplier_id=".$supplier_id." ";
	if ($order_number != "")
	{
		$sql .= "AND porder.reference LIKE ".db_escape('%'. $order_number . '%');
	}
	else
	{

		$data_after = date2sql($from);
		$date_before = date2sql($to);

		$sql .= " AND porder.ord_date >= '$data_after'";
		$sql .= " AND porder.ord_date <= '$date_before'";

		if ($location != ALL_TEXT)
		{
			$sql .= " AND porder.into_stock_location = ".db_escape($location);
		}
		if ($stock_id !== '')
		{
			$sql .= " AND line.item_code=".db_escape($stock_id);
		}
		if ($supplier_id != ALL_TEXT)
			$sql .= " AND supplier.supplier_id=".db_escape($supplier_id);

	}
	 	// $sql .= " AND is_purchase_request=0 ";
  
    // display_error( $also_closed);
	if (!$also_closed)
		$sql .= " AND isopen>0  AND is_complete=0   AND is_closed=0";
// 	else
// 		$sql .= "  ";	
     

	$sql .= " GROUP BY porder.order_no";

	// display_error($sql);
	return $sql;
}
function get_sql_for_po_search($from, $to, $supplier_id=ALL_TEXT, $location=ALL_TEXT, $order_number='', $stock_id='')
{
	$sql = "SELECT 
		porder.order_no, 
		porder.reference,
		supplier.supp_name, 
		location.location_name,
		porder.requisition_no, 
		porder.ord_date,
		supplier.curr_code,
		Sum(line.unit_price*line.quantity_ordered) AS OrderValue,
		Sum(line.delivery_date < '". date2sql(Today()) ."'
		AND (line.quantity_ordered > line.quantity_received)) As OverDue
		, porder.is_qc_complete , porder.is_close,
		SUM(line.in_ward_qty) as in_ward_qty,
			SUM(line.out_ward_qty) as out_ward_qty,
		SUM(line.qc_qty) as qc_qty,
		SUM(line.quantity_ordered) as quantity_ordered,
		SUM(line.quantity_received) as quantity_received
		 FROM ".TB_PREF."purch_orders as porder,"
			.TB_PREF."purch_order_details as line, "
			.TB_PREF."suppliers as supplier, "
			.TB_PREF."locations as location
		WHERE porder.order_no = line.order_no
		AND porder.supplier_id = supplier.supplier_id
		AND location.loc_code = porder.into_stock_location
		AND (line.quantity_ordered > line.quantity_received) AND is_close=0 AND  ( porder.into_stock_location IN (".$_SESSION["wa_current_user"]->qc_location.") OR porder.into_stock_location IN (".$_SESSION["wa_current_user"]->qc_location.") ) ";

	if ($order_number != "")
	{
		$sql .= "AND porder.reference LIKE ".db_escape('%'. $order_number . '%');
	}
	else
	{
		$data_after = date2sql($from);
		$data_before = date2sql($to);

		$sql .= "  AND porder.ord_date >= '$data_after'";
		$sql .= "  AND porder.ord_date <= '$data_before'";

		if ($location != ALL_TEXT)
		{
			$sql .= " AND porder.into_stock_location = ".db_escape($location);
		}

		if ($stock_id != '')
		{
			$sql .= " AND line.item_code=".db_escape($stock_id);
		}
		if ($supplier_id != ALL_TEXT)
			$sql .= " AND supplier.supplier_id=".db_escape($supplier_id);
	} //end not order number selected

	$sql .= " GROUP BY porder.order_no";
	return $sql;
}

function add_qc_field($category_id,$field_id,$fileld_name,$min='',$max='',$dropdown_value='')
{
	if($min=='') $min=0;
	if($max=='') $max=0;
	     $sql = "INSERT INTO ".TB_PREF."qc_setup (type_id, name, qc_type, min, 
     	max, options) VALUES(";
     $sql .= db_escape($category_id) . "," .
         db_escape($fileld_name) . "," .
         db_escape($field_id) . ", " .
		 db_escape($min) . ", " .
         db_escape($max) . ", " .
         db_escape($dropdown_value) . ")";

// display_error($sql);
   	db_query($sql, "The purchase order header record could not be inserted");

   /*Get the auto increment value of the order number created from the sql above */
     $id = db_insert_id();
$date = date('Y-m-d');
	add_audit_trail(ST_REQUISITION, $id, sql2date($date),_("QC setup Form"));


}

function get_po_qc_category_detalis($order_no, $type ){
	if($type==ST_WORKORDER)
		   $sql="SELECT sc.* , sm.description as item_description FROM 0_workorders wo , 0_stock_master sm , 0_stock_category sc WHERE wo.id=".db_escape($order_no)." AND wo.stock_id=sm.stock_id AND sm.category_id=sc.category_id";
		else
		  $sql="SELECT sc.*, pod.description as item_description FROM 0_purch_order_details pod , 0_stock_master sm , 0_stock_category sc WHERE pod.order_no=".db_escape($order_no)." AND pod.item_code=sm.stock_id AND sm.category_id=sc.category_id";
		return $sql;

}
function get_qc_details($category_id){
    $sql = "SELECT * FROM ".TB_PREF."qc_setup
		WHERE type_id=".db_escape($category_id)." ORDER BY id";
	$result = db_query($sql, "not get qc details");
   return $result;
}


function is_complete_req($order_no){
    $sql = "SELECT * FROM ".TB_PREF."req_order_details
		WHERE order_no=".db_escape($order_no)." AND quantity_ordered!=(quantity_received+qty_delivered) ORDER BY order_no";
	$result = db_query($sql, "not get is_complete_req");
   $row = db_fetch($result);
   if($row)
   	return 1;
   else
   	return 0;

}



function get_qc_details_id($id){
    $sql = "SELECT * FROM ".TB_PREF."qc_setup
		WHERE id=".db_escape($id)." ORDER BY id";
	$result = db_query($sql, "not get qc details");
   return db_fetch($result);
}


function update_qc_field($id,$category_id,$field_id,$fileld_name,$min='',$max='',$dropdown_value='')
{
	if($min=='') $min=0;
	if($max=='') $max=0;

	$sql = "UPDATE ".TB_PREF."qc_setup SET 
	type_id=".db_escape($category_id).",
	name=".db_escape($fileld_name).",
	qc_type=".db_escape($field_id).",
	min=".db_escape($min).",
	max=".db_escape($max).",
	options=".db_escape($dropdown_value)." WHERE id=".db_escape($id);

   // display_error($sql);
   	db_query($sql, "The purchase order header record could not be inserted");
   /*Get the auto increment value of the order number created from the sql above */
	$date = date('Y-m-d');
	add_audit_trail(ST_REQUISITION, $id, sql2date($date),_(" Update QC seetup Form"));


}

function delete_qc_setup_item($selected_id)
{
	$sql = "DELETE FROM ".TB_PREF."qc_setup WHERE id=".db_escape($selected_id);
	db_query($sql,"Could not delete this qc_setup components");
}

function get_qc_trans($qc_no, $type)
{
 $sql = "SELECT * FROM ".TB_PREF."qc_trans qct , ".TB_PREF."qc_setup qcs
		WHERE qct.field_id= qcs.id AND qct.trans_no=".db_escape($qc_no)." AND qct.type=".db_escape($type)." ORDER BY qct.id";
	//display_error($sql);	
	$result = db_query($sql, " not get qc trans");
   return $result;
}

function get_qc_trans_group_by($order_no, $type){


	 $sql = "SELECT * FROM ".TB_PREF."qc_trans qct , ".TB_PREF."qc_setup qcs
		WHERE qct.field_id= qcs.id AND qct.po_order_no=".db_escape($order_no)." AND qct.type=".db_escape($type)." GROUP BY trans_no ORDER BY qct.id";
	// display_error($sql);	
	$result = db_query($sql, " not get qc trans");
	
   return 	$result;


}

function get_req_trans($req_no){

	$sql = "SELECT po.*, supplier.*, loc.location_name 
		FROM ".TB_PREF."req_orders po,"
			.TB_PREF."suppliers supplier,"
			.TB_PREF."locations loc
		WHERE po.supplier_id = supplier.supplier_id
		AND loc.loc_code = into_stock_location
		AND po.order_no = ".db_escape($req_no);

		$result = db_query($sql, " not get requsition trans");
   return $result;
}

function get_req_trans_details($req_no,$item_code='')
{


	$sql = "SELECT poline.*, units
		FROM ".TB_PREF."req_order_details poline
			LEFT JOIN ".TB_PREF."stock_master item	ON poline.item_code=item.stock_id
		WHERE order_no =".db_escape($req_no);

    if ($open_items_only)
 		$sql .= " AND (poline.quantity_ordered > poline.quantity_received) ";
 	if($item_code)
 	$sql .= " AND poline.item_code=".db_escape($item_code);	

	$sql .= " ORDER BY po_detail_item";


// display_error($sql);
	return $result = db_query($sql, "The lines on the purchase order cannot be retrieved");

}

function purchase_order_close($id, $status)
{
  	$sql = "UPDATE ".TB_PREF."purch_orders SET is_close = ".db_escape($status)." WHERE order_no=".db_escape($id);

  	db_query($sql, "Can't change PO close ");
  		add_audit_trail(ST_PURCHORDER, $id, Today(), ($status) ? _("GT Order Approved") : _("PO colse") );
  	// $status
}


function update_for_partial_qc($order_no)
{
 $sql = "SELECT * FROM ".TB_PREF."purch_orders po , ".TB_PREF."purch_order_details pod
		WHERE  po.order_no=pod.order_no AND   (pod.quantity_ordered > pod.quantity_received)  AND po.order_no=".db_escape($order_no)." ORDER BY po.order_no";
 $result = db_query($sql, " check po partial grn for order");
  // $row=db_fetch($result);
		// display_error($sql."  == ".db_num_rows($result));sss
  if(db_num_rows($result)>0) {
  	$sql = "UPDATE ".TB_PREF."purch_orders SET is_qc_complete = 0 WHERE order_no=".db_escape($order_no);
  	db_query($sql, "Can't change PO update for again qc ");
  }
 
}

function get_req_line_id($po_line_id){

			$sql = "SELECT * FROM  ".TB_PREF."req_order_details rqd
			WHERE  rqd.po_detail_item=".db_escape($po_line_id);
			$result = db_query($sql, " check po partial grn for order");
          // display_error($sql);
			return db_fetch($result);
}

function get_next_qc_trans_no($type){

	$sql="SELECT COUNT(DISTINCT po_order_no,type , trans_no ) +1 as trans_no  FROM `0_qc_trans` WHERE type=".db_escape($type)."  ORDER By id";
	// display_error($sql);
	$result = db_query($sql, " check po partial grn for order");
	$row = db_fetch($result);
	return $row['trans_no'];
}

function order_total_po($from, $to, $supplier_id=ALL_TEXT, $location=ALL_TEXT, 
	$order_number='', $stock_id='')
{
	// SUM(po.total) AS total
	$sql = "SELECT po.*, pod.* FROM ".TB_PREF."purch_orders as po INNER JOIN ".TB_PREF."purch_order_details as pod ON po.order_no=pod.order_no WHERE po.order_no=pod.order_no";


	if ($order_number != "")
	{
		$sql .= "AND po.reference LIKE ".db_escape('%'. $order_number . '%');
	}
	else
	{
		$data_after = date2sql($from);
		$data_before = date2sql($to);

		$sql .= "  AND po.ord_date >= '$data_after'";
		$sql .= "  AND po.ord_date <= '$data_before'";

		if ($location != ALL_TEXT)
		{
			$sql .= " AND po.into_stock_location = ".db_escape($location);
		}

		if ($stock_id != '')
		{
			$sql .= " AND pod.item_code=".db_escape($stock_id);
		}
		if ($supplier_id != ALL_TEXT)
			$sql .= " AND po.supplier_id=".db_escape($supplier_id);
	} //end not order number selected

	// $sql .= " GROUP BY po.order_no";

	// if ($stock_id) {

	$sql .= " GROUP BY po.order_no";
	// display_error($sql);
	$result = db_query($sql);
	
	while ($myrow = db_fetch($result)) {
		$total += $myrow['total'];
	}

	return $total;

	// }
	// $result = db_query($sql);
	// $data = db_fetch($result);
	// return $data['total'];		


}

function update_quantity_received($order_no, $quantity_received)
{
	$sql = "UPDATE ".TB_PREF."purch_order_details SET quantity_received=".db_escape($quantity_received)." WHERE order_no = ".db_escape($order_no);
	display_error($sql);
   	db_query($sql,"The sales group could not be updated");
}