<?php
/**********************************************************************
***********************************************************************/
include_once($path_to_root . "/purchasing/includes/supp_trans_class.inc");

include_once($path_to_root . "/includes/banking.inc");

include_once($path_to_root . "/includes/date_functions.inc");

include_once($path_to_root . "/includes/db/inventory_db.inc");

include_once($path_to_root . "/purchasing/includes/db/supp_trans_db.inc");
include_once($path_to_root . "/purchasing/includes/db/po_db.inc");
include_once($path_to_root . "/purchasing/includes/db/grn_db.inc");
include_once($path_to_root . "/purchasing/includes/db/invoice_db.inc");
include_once($path_to_root . "/purchasing/includes/db/suppalloc_db.inc");
include_once($path_to_root . "/purchasing/includes/db/supp_payment_db.inc");
include_once($path_to_root . "/purchasing/includes/db/suppliers_db.inc");

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

// add a supplier-related gl transaction
// $date_ is display date (non-sql)
// $amount is in SUPPLIERS'S currency

function add_gl_trans_supplier($type, $type_no, $date_, $account, $dimension, $dimension2,  
	$amount, $supplier_id, $err_msg="", $rate=0, $memo="")
{
	if ($err_msg == "")
		$err_msg = "The supplier GL transaction could not be inserted";	
		
	return add_gl_trans($type, $type_no, $date_, $account, $dimension, $dimension2, $memo, 
		$amount, get_supplier_currency($supplier_id), 
		PT_SUPPLIER, $supplier_id, $err_msg, $rate);
}

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

function get_purchase_price($supplier_id, $stock_id)
{
	$sql = "SELECT price, conversion_factor FROM ".TB_PREF."purch_data 
		WHERE supplier_id = ".db_escape($supplier_id) . " 
		AND stock_id = ".db_escape($stock_id);
	$result = db_query($sql, "The supplier pricing details for " . $stock_id . " could not be retrieved");    

	if (db_num_rows($result) == 1)
	{
		$myrow = db_fetch($result);
        if($myrow['conversion_factor'] == 0){
            return $myrow["price"];
        }
        else{
            return $myrow["price"] / $myrow['conversion_factor'];
        }
	} 
	else 
	{
		return 0;
	}	
}

function get_purchase_conversion_factor($supplier_id, $stock_id)
{
	$sql = "SELECT conversion_factor FROM ".TB_PREF."purch_data 
		WHERE supplier_id = ".db_escape($supplier_id)." 
		AND stock_id = ".db_escape($stock_id);
	$result = db_query($sql, "The supplier pricing details for " . $stock_id . " could not be retrieved");    

	if (db_num_rows($result) == 1)
	{
		$myrow = db_fetch($result);
        if($myrow['conversion_factor'] == 0){
            return 1;
        }
        else{
            return $myrow['conversion_factor'];
        }
	} 
	else 
	{
		return 1;
	}	
}
//----------------------------------------------------------------------------------------

function get_purchase_data($supplier_id, $stock_id)
{
	$sql = "SELECT * FROM ".TB_PREF."purch_data 
		WHERE supplier_id = ".db_escape($supplier_id) . "
		AND stock_id = ".db_escape($stock_id);
	$result = db_query($sql, "The supplier pricing details for " . $stock_id . " could not be retrieved");    

	return db_fetch($result);
}

function add_or_update_purchase_data($supplier_id, $stock_id, $price, $description="", $project_id=0, $task_id=0, $uom="")
{
//	$data = get_purchase_data($supplier_id, $stock_id);
//	if ($data === false)
//	{
//		$sql = "INSERT INTO ".TB_PREF."purch_data (supplier_id, stock_id, price, suppliers_uom,
//			conversion_factor, supplier_description) VALUES (".db_escape($supplier_id)
//			.", ".db_escape($stock_id).", ".db_escape($price).", "
//			.db_escape($uom).", 1, ".db_escape($description).")";
//		db_query($sql,"The supplier purchasing details could not be added");
//		return;
//	}	
//	$price = round($price * $data['conversion_factor'], user_price_dec());
//	$sql = "UPDATE ".TB_PREF."purch_data SET price=".db_escape($price);
//	if ($uom != "")
//		$sql .= ",suppliers_uom=".db_escape($uom);
//	if ($description != "")	
//		$sql .= ",supplier_description=".db_escape($description);
//	$sql .= " WHERE stock_id=".db_escape($stock_id)." AND supplier_id=".db_escape($supplier_id);
    
    
    $sql = "UPDATE ".TB_PREF."purch_data SET 
                        supplier_description=".db_escape($description)." 
            WHERE stock_id=".db_escape($stock_id)." AND supplier_id=".db_escape($supplier_id);
    
    
	db_query($sql,"The supplier purchasing details could not be updated");
	return true;
}

function get_sql_from_po_document_search($doc_no)
{
    $sql = "SELECT DISTINCT pod.order_no,
                           pr_po.id pr_allo_id,
                           pid.indent_id,
                           prd.requisition_id,
                           wm.workorder_id work_order_id,
                           gi.grn_batch_id
            FROM ".TB_PREF."purch_order_details pod
            LEFT JOIN ".TB_PREF."pr_po_details pr_po ON
                pr_po.po_line_id = pod.po_detail_item
            LEFT JOIN ".TB_PREF."purchase_requisition_details prd ON
                prd.id = pr_po.pr_line_id                
            LEFT JOIN ".TB_PREF."purchase_indent_details pid ON
                prd.id = pid.pr_line_id  
            LEFT JOIN ".TB_PREF."wo_pr_details wopr ON
                prd.id = wopr.pr_line_id 
            LEFT JOIN ".TB_PREF."wo_material wm ON
                wopr.wo_material_id =wm.id 
            LEFT JOIN ".TB_PREF."grn_items gi ON
                gi.po_detail_item = pod.po_detail_item   
            WHERE pod.order_no=".db_escape($doc_no);
    
   return $result =db_query($sql,"The document link details could not be updated");
        
   
    
}

function get_sql_from_grn_document_search($doc_no)
{
    $sql = "SELECT DISTINCT pod.order_no,
                           pr_po.id pr_allo_id,
                           pid.indent_id,
                           prd.requisition_id,
                           wm.workorder_id work_order_id,
                           gi.grn_batch_id
            FROM ".TB_PREF."grn_items gi 
            LEFT JOIN ".TB_PREF."purch_order_details pod ON
                 gi.po_detail_item = pod.po_detail_item   
            LEFT JOIN ".TB_PREF."pr_po_details pr_po ON
                pr_po.po_line_id = pod.po_detail_item
            LEFT JOIN ".TB_PREF."purchase_requisition_details prd ON
                prd.id = pr_po.pr_line_id                
            LEFT JOIN ".TB_PREF."purchase_indent_details pid ON
                prd.id = pid.pr_line_id  
            LEFT JOIN ".TB_PREF."wo_pr_details wopr ON
                prd.id = wopr.pr_line_id 
            LEFT JOIN ".TB_PREF."wo_material wm ON
                wopr.wo_material_id =wm.id 
            WHERE gi.grn_batch_id=".db_escape($doc_no);
    
   return $result =db_query($sql,"The document link details could not be updated");
        
   
    
}

function get_sql_from_pr_allocation_document_search($doc_no)
{
     $sql = "SELECT DISTINCT pod.order_no,
                           pr_po.id pr_allo_id,
                           pid.indent_id,
                           prd.requisition_id,
                           wm.workorder_id work_order_id,
                           gi.grn_batch_id
            FROM ".TB_PREF."pr_po_details pr_po 
            LEFT JOIN ".TB_PREF."purchase_requisition_details prd ON
                prd.id = pr_po.pr_line_id      
            LEFT JOIN ".TB_PREF."purch_order_details pod ON
                pr_po.po_line_id = pod.po_detail_item
            LEFT JOIN ".TB_PREF."purchase_indent_details pid ON
                prd.id = pid.pr_line_id  
            LEFT JOIN ".TB_PREF."wo_pr_details wopr ON
                prd.id = wopr.pr_line_id 
            LEFT JOIN ".TB_PREF."wo_material wm ON
                wopr.wo_material_id =wm.id             
            LEFT JOIN ".TB_PREF."grn_items gi ON
                gi.po_detail_item = pod.po_detail_item   
            WHERE pr_po.id=".db_escape($doc_no);
    
   return $result =db_query($sql,"The document link details could not be updated");
}

function get_sql_from_pr_req_indent_document_search($doc_no)
{
    
    $sql = "SELECT DISTINCT pod.order_no,
                           pr_po.id pr_allo_id,
                           pid.indent_id,
                           prd.requisition_id,
                           wm.workorder_id work_order_id,
                           gi.grn_batch_id
            FROM ".TB_PREF."purchase_requisition_details prd 
            LEFT JOIN ".TB_PREF."pr_po_details pr_po ON
                prd.id = pr_po.pr_line_id  
            LEFT JOIN ".TB_PREF."purchase_indent_details pid ON
                prd.id = pid.pr_line_id  
            LEFT JOIN ".TB_PREF."wo_pr_details wopr ON
                prd.id = wopr.pr_line_id 
            LEFT JOIN ".TB_PREF."wo_material wm ON
                wopr.wo_material_id =wm.id                
            LEFT JOIN ".TB_PREF."purch_order_details pod ON
                pr_po.po_line_id = pod.po_detail_item
            LEFT JOIN ".TB_PREF."grn_items gi ON
                gi.po_detail_item = pod.po_detail_item   
            WHERE prd.requisition_id=".db_escape($doc_no);
    
   return $result =db_query($sql,"The document link details could not be updated");
    
    
}

function get_sql_from_pr_indent_document_search($doc_no)
{
    $sql = "SELECT DISTINCT pod.order_no,
                           pr_po.id pr_allo_id,
                           pid.indent_id,
                           prd.requisition_id,
                           wm.workorder_id work_order_id,
                           gi.grn_batch_id
            FROM ".TB_PREF."purchase_indent_details pid ON
                prd.id = pid.pr_line_id  
            LEFT JOIN ".TB_PREF."wo_pr_details wopr ON
                prd.id = wopr.pr_line_id 
            LEFT JOIN ".TB_PREF."wo_material wm ON
                wopr.wo_material_id =wm.id 
            LEFT JOIN ".TB_PREF."purch_order_details pod ON
                pr_po.po_line_id = pod.po_detail_item
            LEFT JOIN ".TB_PREF."grn_items gi ON
                gi.po_detail_item = pod.po_detail_item   
            WHERE pid.indent_id=".db_escape($doc_no);
    
   return $result =db_query($sql,"The document link details could not be updated");
    
}

function get_sql_from_wo_document_search($doc_no)
{
    $sql = "SELECT DISTINCT pod.order_no,
                           pr_po.id pr_allo_id,
                           pid.indent_id,
                           prd.requisition_id,
                           wm.workorder_id work_order_id,
                           gi.grn_batch_id
            FROM ".TB_PREF."wo_material wm 
            LEFT JOIN ".TB_PREF."wo_pr_details wopr ON
                wopr.wo_material_id =wm.id 
            LEFT JOIN ".TB_PREF."pr_po_details pr_po ON
                wopr.pr_line_id = pr_po.pr_line_id 
            LEFT JOIN ".TB_PREF."purchase_requisition_details prd ON
                prd.id = pr_po.pr_line_id     
            LEFT JOIN ".TB_PREF."purchase_indent_details pid ON
                prd.id = pid.pr_line_id              
            LEFT JOIN ".TB_PREF."purch_order_details pod ON
                pr_po.po_line_id = pod.po_detail_item
            LEFT JOIN ".TB_PREF."grn_items gi ON
                gi.po_detail_item = pod.po_detail_item   
            WHERE wm.workorder_id=".db_escape($doc_no);
    
   return $result =db_query($sql,"The document link details could not be updated");
    
}

function get_pr_allocation_ids_for_po_line($row_id)
{
        
    $sql = "SELECT id allocation_id
            FROM ".TB_PREF."pr_po_details pr_po 
            WHERE 
                po_line_id = ".$row_id ;
    
    $result = db_query($sql,"Could not get_pr_allocation_ids_for_po_line");

    return $result;

}

function get_pr_line_ids_for_po_line($row_id)
{
        
    $sql = "SELECT DISTINCT prd.id, prd.requisition_id 
            FROM ".TB_PREF."pr_po_details pr_po 
                JOIN ".TB_PREF."purchase_requisition_details prd 
                    ON prd.id = pr_po.pr_line_id 
            WHERE pr_po.po_line_id = ".$row_id ;
    
    $result = db_query($sql,"get_pr_line_ids_for_po_line");

    return $result;

}

?>