<?php
/**********************************************************************
***********************************************************************/


function get_sql_for_outstanding_po_search($order_number, $order_reference, $orders_after_date, $orders_to_date, $stock_location,
                                            $supplier_id, $select_stock_from_list)
{
	//global $all_items, $order_number, $selected_stock_item;
    global $SysPrefs;
    
    $over_receive_factor = 1+ ($SysPrefs->over_receive_allowance() / 100);
	
	$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,
                    porder.approval_status,
                    Sum(line.delivery_date < '". date2sql(Today()) ."'
                    AND (line.quantity_ordered > line.quantity_received)) As OverDue,
                    porder.supplier_id 
            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  * ".$over_receive_factor." > line.quantity_received) 
                        AND porder.approval_status = ".AS_APPROVED." ";               
                               

            if(isset($order_number) && $order_number > 0)
            {
                $sql .= " AND porder.order_no = ".db_escape($order_number);
            }
            elseif(isset($order_reference) && strlen($order_reference) > 0){
                $sql .= " AND porder.reference LIKE ".db_escape('%'. $order_reference . '%');
            }
            else
            {
                $sql .= "  AND porder.ord_date >= ".db_escape(date2sql($orders_after_date));
                $sql .= "  AND porder.ord_date <= ".db_escape(date2sql($orders_to_date));
            
                if ($stock_location != '')
                {
                        $sql .= " AND porder.into_stock_location = ".db_escape($_POST['StockLocation']);
                }

        
                if (($select_stock_from_list))
                {
                        $sql .= " AND line.item_code=".db_escape($select_stock_from_list);
                }                


                if (($supplier_id) > 0)
                {
                        $sql .= " AND porder.supplier_id =".db_escape($supplier_id);
                }
            
            } //end not order number selected

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

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


function get_po_tax_details($po_no)
{
    $sql = "SELECT amount 
            FROM ".TB_PREF."project_invoice_taxes 
            WHERE order_no=".db_escape($pi_no)." AND 
                tax_type_id = ".db_escape($tax_type_id);
    
    $result = db_query($sql);
    $amt =  db_fetch($result);   
    $amount = 0;
    if(isset($amt['amount']) && $amt['amount'] > 0){
        $amount = $amt['amount'];
    }
    
    return $amount;
}

?>