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

function delete_po($po)
{
	return;
        
        hook_db_prevoid($po, ST_PURCHORDER);
	$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");
}

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

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

    $id_user = $_SESSION['wa_current_user']->user;
    $datetime = date('Y-m-d H:i:s');

    $next_approval_group_id = 0;
    if (!isset($po_obj->project_id) && !isset($po_obj->task_id) && !isset($po_obj->approval_status)) {

        $po_obj->project_id = 0;
        $po_obj->task_id = 0;
        $po_obj->approval_status = 0;
    }

    $taxes_total = $staxes_total = 0;
    foreach ($po_obj->taxes as $po_tax_line){
        if($po_tax_line['Net'] > 0){ // $po_tax_line['Value'] can be zero for excempted taxes
            if($po_tax_line['suspended'] == 1){
                $staxes_total += $po_tax_line['Value'];
            }
            else{
                $taxes_total += $po_tax_line['Value'];
            }
        }
    }
    
    begin_transaction();
    hook_db_prewrite($po_obj, ST_PURCHORDER);

     /*Insert to purchase order header record */
     $sql = "INSERT INTO ".TB_PREF."purch_orders (
                supplier_id, 
                Comments, 
                ord_date, 
                reference, 
                terms_indicator,
                requisition_no, 
                into_stock_location, 
                delivery_address, 
                total, 
                tax_included,  
                tax_group_id,
                taxes,
                staxes,
                created_by, 
                created_on,
                project_id,
                generated_po,
                next_approval_group_id) 
            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->terms['terms_indicator']) . ", " .
                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->tax_group_id). ", " .
                db_escape($taxes_total). ", " .
                db_escape($staxes_total). ", " .
                db_escape($id_user). ", " .
                db_escape($datetime). ", ".
                db_escape($po_obj->project_id). ", ".
                db_escape($po_obj->generated_po). ", ".
                db_escape($next_approval_group_id). " )";
     
     //file_put_contents('C:\xampp\htdocs\accounts1\newfolder\log.txt', $sql);
 
 //exit;
	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 */
     
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
        
     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, 
                            original_order_qty, order_unit, 
                            order_unit_qty, order_unit_conv_factor, 
                            order_unit_price, order_unit_price_discount,
                            minimum_price, minimum_price_discount,
                            min_price_sup_id, project_site_id,
                            tax_included, tax_group_id,
                            line_comment,
                            created_by, created_on) 
                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->quantity). " , " .db_escape($po_line->tran_unit). " , " .
                db_escape($po_line->tran_qty). " , " .db_escape($po_line->convertion_factor). ", " .
                db_escape($po_line->order_unit_price). ", " .db_escape($po_line->order_unit_price_discount). ", " .
                db_escape($po_line->minimum_price). ", " .db_escape($po_line->minimum_price_discount). ", " .
                db_escape($po_line->min_price_sup_id). ",".db_escape($po_line->project_site_id). ",".
                db_escape($po_line->tax_included). ", " .db_escape($po_line->tax_group_id). ",".
                db_escape($po_line->line_comment). ", " .
                db_escape($created_by). ", " .db_escape($created_on). ")";
        
		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($po_line->pr_line_id > 0)
//                {
//                    $path_to_root = "..";
//                    include_once($path_to_root . "/requisitions/includes/db/purchase_requisitions_db.inc");
//                    
//                    complete_pr_line($po_line->pr_line_id, $po_line->po_detail_rec);
//                }
     }
     

    foreach ($po_obj->taxes as $po_tax_line){
            if($po_tax_line['Net'] > 0){ // $po_tax_line['Value'] can be zero for excempted taxes
                add_po_tax_details($po_obj->order_no, $po_tax_line['tax_type_id'], $po_tax_line['rate'], $po_obj->tax_included,
                                       $po_tax_line['Value'], $po_tax_line['Net'], 1, $po_obj->orig_order_date, 
                                        '', $po_tax_line['suspended']);
            }
    }
     
    $cspoi_msg = check_saved_purchase_order_integrity($po_obj->order_no);
    if($cspoi_msg['success'] !== true){
        cancel_transaction();
        return array("success" => false, "tran_no" => $po_obj->order_no, "message" => $cspoi_msg['message']);
    }
    
    $Refs->save(ST_PURCHORDER, $po_obj->order_no, $po_obj->reference);

    //add_comments(ST_PURCHORDER, $po_obj->order_no, $po_obj->orig_order_date, $po_obj->Comments);
        
//        update_pr_history($po_obj->order_no, $po_obj->reference, $po_obj->approval_hierarchy, 
//                    $id_user, $datetime, $po_obj->pr_status, $po_obj->Comments);

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

    return array("success" => true, "tran_no" => $po_obj->order_no, "message" => "");
}

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

function update_po(&$po_obj)
{
	begin_transaction();
	hook_db_prewrite($po_obj, ST_PURCHORDER);
        
    $num_rows = update_po_header($po_obj);
    if($num_rows != 1){
        cancel_transaction();
        return array("success" => false, "tran_no" => $po_obj->order_no, "message" => get_message(MC_ANOTHER_USER_HAS_UPDATED));
    }

    delete_po_lines($po_obj);

    /*Now Update the purchase order detail records */

    recreate_po_lines($po_obj->order_no, $po_obj);    
        
    delete_po_tax_details($po_obj->order_no);
    foreach ($po_obj->taxes as $po_tax_line){
        if($po_tax_line['Net'] > 0){ // $po_tax_line['Value'] can be zero for excempted taxes
            add_po_tax_details($po_obj->order_no, $po_tax_line['tax_type_id'], $po_tax_line['rate'], $po_obj->tax_included,
                                   $po_tax_line['Value'], $po_tax_line['Net'], 1, $po_obj->orig_order_date, 
                                        '', $po_tax_line['suspended']);
        }
    }
    
    $cspoi_msg = check_saved_purchase_order_integrity($po_obj->order_no);
    if($cspoi_msg['success'] !== true){
        cancel_transaction();
        return array("success" => false, "tran_no" => $po_obj->order_no, "message" => $cspoi_msg['message']);
    }
    
    add_audit_trail($po_obj->trans_type, $po_obj->order_no, Today(), _("Updated."));
    hook_db_postwrite($po_obj, ST_PURCHORDER);
    commit_transaction();

    return array("success" => true, "tran_no" => $po_obj->order_no, "message" => "PO $po_obj->order_no has been updated.");
}

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

function recreate_po_lines($order_no, $po_obj)
{
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
    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, 
                        project_site_id, unit_price, 
                        quantity_ordered, quantity_received, 
                        original_order_qty, order_unit, 
                        order_unit_qty, order_unit_conv_factor, 
                        order_unit_price, order_unit_price_discount, 
                        minimum_price, minimum_price_discount, 
                        min_price_sup_id,                        
                        tax_included, tax_group_id,
                        line_comment,
                        updated_by, updated_on) 
                VALUES ("
                        .db_escape($po_line->po_detail_rec ? $po_line->po_detail_rec : 0). ","
                        .$order_no . ",".db_escape($po_line->stock_id). ","
                        .db_escape($po_line->item_description). ",'".date2sql($po_line->req_del_date) . "',"
                        .db_escape($po_line->project_site_id) . ", ".db_escape($po_line->price) . ", "
                        .db_escape($po_line->quantity) . ", ".db_escape($po_line->qty_received) . ", "
                        .db_escape($po_line->quantity) . ", ".db_escape($po_line->tran_unit). " , " 
                        .db_escape($po_line->tran_qty). " , ".db_escape($po_line->convertion_factor).", "
                        .db_escape($po_line->order_unit_price). ", ".db_escape($po_line->order_unit_price_discount). ", " 
                        .db_escape($po_line->minimum_price). ", ".db_escape($po_line->minimum_price_discount). ", "
                        .db_escape($po_line->min_price_sup_id). ", "
                        .db_escape($po_line->tax_included). ", " .db_escape($po_line->tax_group_id). ","
                        .db_escape($po_line->line_comment).", "
                        .db_escape($updated_by). ", ".db_escape($updated_on). ")";
		
		db_query($sql, "One of the purchase order detail records could not be updated");
    }
}

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


function delete_po_lines($po_obj)
{
    	$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");
}


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


function update_po_header(&$po_obj)
{
    $id_user = $_SESSION['wa_current_user']->user;
	$datetime = date('Y-m-d H:i:s');
        
    $taxes_total = $staxes_total = 0;
    
    foreach ($po_obj->taxes as $po_tax_line){
        if($po_tax_line['Net'] > 0){ // $po_tax_line['Value'] can be zero for excempted taxes
            if($po_tax_line['suspended'] == 1){
                $staxes_total += $po_tax_line['Value'];
            }
            else{
                $taxes_total += $po_tax_line['Value'];
            }
        }
    }

    /*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). ",
                terms_indicator= ". db_escape( $po_obj->terms['terms_indicator']). ",
                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()).",
                project_id=". db_escape($po_obj->project_id).",
                tax_included=". db_escape($po_obj->tax_included).", 
                tax_group_id=". db_escape($po_obj->tax_group_id).", 
                taxes=". db_escape($taxes_total).", 
                staxes=". db_escape($staxes_total).", 
                updated_by =". db_escape($id_user).",
                updated_on =". db_escape($datetime).",
                row_state = ". db_new_row_state($po_obj->row_state);
        
    $sql .= " WHERE order_no = " . $po_obj->order_no. " AND 
                    row_state = ".$po_obj->row_state;

	db_query($sql, "The purchase order could not be updated");
    
    return db_num_affected_rows();
}


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, $pr_no=0)
{
   	$sql = "SELECT po.*, 
                    supp.supp_name,  
                    supp.curr_code, 
                    loc.location_name, 
                    pm.description project_description 
            FROM ".TB_PREF."purch_orders po, 
                    ".TB_PREF."suppliers supp, 
                    ".TB_PREF."locations loc, 
                    ".TB_PREF."projects_master pm 
            WHERE po.supplier_id = supp.supplier_id
            AND loc.loc_code = into_stock_location
            AND pm.project_id = po.project_id    
            AND po.order_no = ".db_escape($order_no);

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

    $num_rows = db_num_rows($result);
	if ($num_rows == 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'], $myrow["tax_included"]);

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

      	$order->orig_order_date = sql2date($myrow["ord_date"]);
        
        $ss = str_replace('<br>', "\r\n", $myrow["comments"]);        
      	$order->Comments = $ss;
        
      	$order->Location = $myrow["into_stock_location"];
      	$order->supp_ref = $myrow["requisition_no"];
      	$order->reference = $myrow["reference"];
        $order->terms['terms_indicator'] = $myrow["terms_indicator"];
      	$order->delivery_address = $myrow["delivery_address"];
        $order->approval_status = $myrow["approval_status"];
        $order->project_id = $myrow["project_id"];
        $order->project_description = $myrow["project_description"];        
        $order->created_by = $myrow["created_by"];
        $order->next_approval_group_id = $myrow["next_approval_group_id"];
        $order->job_reference = $myrow["project_description"];
        $order->generated_po = $myrow["generated_po"];
        $order->row_state = $myrow["row_state"];
        $order->tax_group_id = $myrow["tax_group_id"];
        
        $order->receiving_pr_no = $pr_no;
        
        if($pr_no > 0){
            $prh = get_pr_edit_info($pr_no);
            $order->pr_row_state = $prh["row_state"];
            $order->pr_complete = $prh["pr_complete"];
        }
        else{
            $order->pr_row_state = 0;
            $order->pr_complete = 1;
        }

      	return true;
	}
    elseif($num_rows == 0){
        display_db_error("No purchase order found", "", true);
        false;
    }
    else{
        display_db_error("FATAL : duplicate purchase order found", "", true);
        false;
    }
}

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


function get_po_edit_info($po_no)
{
        
    $sql = " SELECT * FROM ".TB_PREF."purch_orders  
                WHERE order_no = ".db_escape($po_no);
    
    $result = db_query($sql,"Purchase order could not be retreived");

    return db_fetch($result);

}


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

    if($pr_no > 0){
        $sql = "SELECT pod.*, 
                    sm.units, sm.stock_control_method,
                    u.name tran_unit_name,
                    ps.description project_site_name,
                    prd.quantity pr_qty,
                    prd.issued_qty pr_issued_qty,
                    prd.id pr_line_id,
                    prd.requisition_id,
                    pr_po.id pr_po_line_id,
                    prd.task_id 
                FROM ".TB_PREF."purch_order_details pod 
                    LEFT JOIN ".TB_PREF."pr_po_details pr_po ON 
                        pod.po_detail_item = pr_po.po_line_id 
                    LEFT JOIN ".TB_PREF."purchase_requisition_details prd ON 
                        pr_po.pr_line_id = prd.id 
                    LEFT JOIN ".TB_PREF."stock_master sm ON 
                        pod.item_code= sm.stock_id 
                    LEFT JOIN ".TB_PREF."item_units u ON 
                        pod.order_unit= u.abbr   
                    LEFT JOIN ".TB_PREF."project_sites ps ON 
                        pod.project_site_id = ps.id   
                WHERE pod.order_no =".db_escape($order_no)." AND 
                        (prd.requisition_id = ".db_escape($pr_no)." OR prd.requisition_id IS NULL)";
    }
    else{
        $sql = "SELECT pod.*, 
                    sm.units, sm.stock_control_method,
                    u.name tran_unit_name,
                    ps.description project_site_name,
                    0 pr_qty,
                    0 pr_issued_qty,
                    0 pr_line_id,
                    0 requisition_id,
                    0 pr_po_line_id,
                    0 task_id  
                FROM ".TB_PREF."purch_order_details pod 
                    LEFT JOIN ".TB_PREF."stock_master sm ON 
                        pod.item_code= sm.stock_id 
                    LEFT JOIN ".TB_PREF."item_units u ON 
                        pod.order_unit= u.abbr   
                    LEFT JOIN ".TB_PREF."project_sites ps ON 
                        pod.project_site_id = ps.id   
                WHERE pod.order_no =".db_escape($order_no);
    }
    
    if ($open_items_only){
 		$sql .= " AND (pod.quantity_ordered > pod.quantity_received) ";
    }

	$sql .= " ORDER BY pod.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 ($data['suppliers_uom'] != "")
                    //	$myrow['units'] = $data['suppliers_uom'];
            }		
            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"], 0, 0, $myrow["original_order_qty"], 
                        $myrow["order_unit"], $myrow["order_unit_qty"], $myrow["order_unit_conv_factor"],
                        $myrow["minimum_price"],$myrow["min_price_sup_id"],$myrow["order_unit_price"])) 
                {
                    $newline = &$order->line_items[$order->lines_on_order-1];
                                $newline->po_detail_rec = $myrow["po_detail_item"];
                                $newline->standard_cost = $myrow["std_cost_unit"];  /*Needed for receiving goods and GL interface */
                            // set for later GRN edition
    //	   	    	$newline->receive_qty = $newline->quantity - $newline->qty_dispatched;
    //                   $po_line_no = $order->line_items[$order->lines_on_order-1]; 
                    $po_line_no =  (count($order->line_items)-1);

                    $order->line_items[$po_line_no]->alternate_unit_name = $myrow['tran_unit_name'];
                    $order->line_items[$po_line_no]->order_unit_conv_factor = $myrow["order_unit_conv_factor"];
                    $order->line_items[$po_line_no]->row_id = $myrow["po_detail_item"];
                    $order->line_items[$po_line_no]->order_unit_qty = $myrow["order_unit_qty"];
                    $order->line_items[$po_line_no]->stock_control_method = $myrow["stock_control_method"];
                    $order->line_items[$po_line_no]->line_ref = $newline->po_detail_rec;
                    $order->line_items[$po_line_no]->project_site_id = $myrow["project_site_id"];
                    $order->line_items[$po_line_no]->project_site_name = $myrow["project_site_name"];
                    $order->line_items[$po_line_no]->order_unit_price = $myrow["order_unit_price"];
                    $order->line_items[$po_line_no]->order_unit_price_discount = $myrow["order_unit_price_discount"];
                    $order->line_items[$po_line_no]->minimum_price_discount = $myrow["minimum_price_discount"];
                    
                    $order->line_items[$po_line_no]->pr_line_id = $myrow["pr_line_id"];
                    $order->line_items[$po_line_no]->pr_qty = $myrow["pr_qty"];
                    if($pr_no > 0){
                        $total_issued_qty = get_pr_item_total_issued_qty($myrow["pr_line_id"]);
                        $order->line_items[$po_line_no]->pr_issued_qty = $total_issued_qty;
                    }
                    else{
                        $order->line_items[$po_line_no]->pr_issued_qty = $myrow["pr_issued_qty"];
                    }
                    
                    $order->line_items[$po_line_no]->requisition_id = $myrow["requisition_id"];
                    $order->line_items[$po_line_no]->pr_po_line_id = $myrow["pr_po_line_id"];
                    $order->line_items[$po_line_no]->task_id = $myrow["task_id"];
                    $order->line_items[$po_line_no]->tax_group_id = $order->tax_group_id;
                    $order->line_items[$po_line_no]->tax_included = $myrow["tax_included"];
                    $order->line_items[$po_line_no]->line_comment = $myrow["line_comment"];
                    
                }
        } /* line po from purchase order details */
    } //end of checks on returned data set
}

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

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

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

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

function get_po_items($order_no)
{
	$sql = "SELECT po_detail_item, item_code, quantity_ordered, quantity_received, qty_invoiced
		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_po_details($order_no)
{
	$sql = "SELECT *
		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_po_header($po_no)
{
    $sql = "SELECT 
		porder.*,  
		supplier.supp_name, 
		location.location_name,
		supplier.curr_code 
            FROM ".TB_PREF."purch_orders as porder, "
                    .TB_PREF."suppliers as supplier, "
                    .TB_PREF."locations as location
            WHERE porder.order_no = ". db_escape($po_no)."
                    AND porder.supplier_id = supplier.supplier_id
                    AND location.loc_code = porder.into_stock_location ";

   $result = db_query($sql, "PO header could not be retrieved");
   
   return db_fetch($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()
{

	$sql = "SELECT 
                porder.order_no, 
                porder.reference, 
                supplier.supp_name, 
                projects.description project_description,
                location.location_name,
                porder.requisition_no, 
                porder.ord_date, 
                supplier.curr_code, 
                porder.total AS OrderValue,
                porder.approval_status,
                porder.total,
                porder.tax_included,
                porder.taxes,
                porder.staxes 
		FROM ".TB_PREF."purch_orders as porder, "
                .TB_PREF."suppliers as supplier, "
                .TB_PREF."locations as location, "
                .TB_PREF."projects_master as projects 
		WHERE porder.supplier_id = supplier.supplier_id
                AND location.loc_code = porder.into_stock_location 
                AND porder.project_id = projects.project_id ";

        if((isset($_POST['order_number']) && $_POST['order_number'] > 0) || (isset($_POST['reference']) && strlen($_POST['reference']) > 0)){
            if (isset($_POST['order_number']) && $_POST['order_number'] > 0){
                $sql .= " AND porder.order_no=". db_escape($_POST['order_number'])." ";
            }

            if (isset($_POST['reference']) && $_POST['reference'] > 0){
                $sql .= " AND porder.reference LIKE ".db_escape('%'. $_POST['reference'] . '%');
            }
        }
        else{
        
            $data_after = date2sql($_POST['OrdersAfterDate']);
            $date_before = date2sql($_POST['OrdersToDate']);

            $sql .= " AND porder.ord_date >= ".db_escape($data_after)." ";
            $sql .= " AND porder.ord_date <= ".db_escape($date_before)." ";
        
            if (isset($_POST['supplier_id']) && $_POST['supplier_id'] > 0){
                $sql .= "AND porder.supplier_id=". db_escape($_POST['supplier_id'])." ";
            }
    
            if (isset($_POST['StockLocation']) && $_POST['StockLocation'] != ALL_TEXT){
                    $sql .= " AND porder.into_stock_location = ".db_escape($_POST['StockLocation']);
            }
        
            if (isset($_POST['project_id']) && $_POST['project_id'] > 0){
                $sql .= "AND porder.project_id=". db_escape($_POST['project_id'])." ";
            }
        }

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

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

function get_sql_for_pr_search()
{
	global $all_items, $order_number, $selected_stock_item;;
	
	$sql = "SELECT 
		porder.order_no, 
		porder.reference, 
                -- porder.pr_status,
                lvalues.value_name,
                users.real_name,
                porder.approved_by, 
                lists.list_name AS approval_hierarchy,
		-- 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
		FROM "
			.TB_PREF."vw_purch_orders as porder, "
			.TB_PREF."purch_order_details as line, "
			.TB_PREF."suppliers as supplier, "
			.TB_PREF."locations as location, "
			.TB_PREF."lists as lists, "
                        .TB_PREF."list_values as lvalues, "
			.TB_PREF."users as users
		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 porder.created_by = users.id
                AND porder.pr_status = lvalues.id
                AND lists.list_code = porder.approval_hierarchy " ;

	if (isset($order_number) && $order_number != "")
	{
		$sql .= "AND porder.reference LIKE ".db_escape('%'. $order_number . '%');
	}
	else
	{
		$data_after = date2sql($_POST['OrdersAfterDate']);
		$data_before = date2sql($_POST['OrdersToDate']);

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

		if (isset($_POST['StockLocation']) && $_POST['StockLocation'] != $all_items)
		{
			$sql .= " AND porder.into_stock_location = ".db_escape($_POST['StockLocation']);
		}

		if (isset($selected_stock_item))
		{
			$sql .= " AND line.item_code=".db_escape($selected_stock_item);
		}
	} //end not order number selected

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

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

function get_sql_for_po_search()
{
	global $all_items, $order_number, $selected_stock_item;
	
	$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
		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) ";

	if (isset($order_number) && $order_number != "")
	{
		$sql .= "AND porder.order_no LIKE ".db_escape('%'. $order_number . '%');
	}
	else
	{
		$data_after = date2sql($_POST['OrdersAfterDate']);
		$data_before = date2sql($_POST['OrdersToDate']);

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

		if (isset($_POST['StockLocation']) && $_POST['StockLocation'] != $all_items)
		{
			$sql .= " AND porder.into_stock_location = ".db_escape($_POST['StockLocation']);
		}

		if (isset($selected_stock_item))
		{
			$sql .= " AND line.item_code=".db_escape($selected_stock_item);
		}
	} //end not order number selected

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

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

function get_initial_pr_status_id($approval_hierarchy)
{
	$sql = "SELECT id FROM ".TB_PREF."list_values 
                WHERE 
                    list_code= ".db_escape($approval_hierarchy)."
                ORDER BY value_code LIMIT 1";
        
     //file_put_contents('C:\xampp\htdocs\accounts1\newfolder\log.txt', $sql);
     
	return db_query($sql,"The pr status id could not be retrieved");
}

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

function calculate_po_remaining_total($line_items)
{
        $remaining_total = 0;
        foreach ($line_items as $line_item)
        {
            $line_remaining_total = ($line_item->quantity - $line_item->qty_received)* $line_item->price;
            $remaining_total += $line_remaining_total;
        }
        
        return $remaining_total;
}


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

function get_po_record($po_no)
{
    $sql = "SELECT * FROM ".TB_PREF."purch_orders WHERE order_no = ".db_escape($po_no);
    
    $result = db_query($sql, "PO record could not be retrieved");
    
    return db_fetch($result);
}
//----------------------------------------------------------------------------------------

function get_po_trans($po_no)
{
    $sql = "SELECT po.*, sup.curr_code sup_curr_code  
            FROM ".TB_PREF."purch_orders po 
                JOIN ".TB_PREF."suppliers sup  
                    ON sup.supplier_id = po.supplier_id 
                WHERE po.order_no = ".db_escape($po_no);
    
    $result = db_query($sql, "get_po_trans");
    
    return db_fetch($result);
}
//----------------------------------------------------------------------------------------


  function get_po_supplier($po_no)
{
    $sql = "SELECT 
                    supplier.supplier_id, supplier.tax_group_id, supplier.tax_included
            FROM ".TB_PREF."purch_orders as porder, "
                    .TB_PREF."suppliers as supplier 
            WHERE porder.order_no = ". db_escape($po_no)."
                    AND porder.supplier_id = supplier.supplier_id";

   $result = db_query($sql, "Could not get_po_supplier");
   
   return db_fetch($result);
    
  
}


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

function update_close_po($po_no, $row_state)
{
        $sql = "UPDATE ".TB_PREF."purch_orders 
                SET approval_status = ".AS_CLOSED." 
                WHERE order_no = ".db_escape($po_no)." AND 
                    row_state = ".db_escape($row_state)." AND 
                    approval_status = ".db_escape(AS_APPROVED);
        
	db_query($sql, "The purchase order could not be updated");
    
    return db_num_affected_rows();
}

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


function add_po_tax_details($po_no, $tax_id, $rate, $included,
                            $amount, $net_amount, $ex_rate, $tran_date, $memo,
                            $suspended=0)
{

	$sql = "INSERT INTO ".TB_PREF."purch_order_tax_details 
                        (order_no, 
                        tran_date, tax_type_id, 
                        rate, ex_rate,
                        included_in_price, net_amount, 
                        amount, memo,
                        suspended)
            VALUES (".db_escape($po_no).",'"
                        .date2sql($tran_date)."',".db_escape($tax_id).","
                        .db_escape($rate).",".db_escape($ex_rate).","
                        .($included ? 1:0).",".db_escape($net_amount).","
                        .db_escape($amount).",".db_escape($memo).","
                        .db_escape($suspended).")";

	db_query($sql, "Could not add_po_tax_details");

}


function delete_po_tax_details($po_no)
{

	$sql = "DELETE FROM ".TB_PREF."purch_order_tax_details WHERE order_no=".db_escape($po_no);
	db_query($sql, "Could not delete_po_tax_details");

}


function get_po_line_details($po_line_id)
{
	$sql = "SELECT pod.*,
                        sm.description item_master_description,
                        sm.editable 
		FROM ".TB_PREF."purch_order_details pod 
                    JOIN ".TB_PREF."stock_master sm 
                        ON pod.item_code = sm.stock_id 
		WHERE po_detail_item =".db_escape($po_line_id);

	$result = db_query($sql, "get_po_line_record");
        return db_fetch($result);

}

function get_po_line_record($po_line_id)
{
	$sql = "SELECT * 
		FROM ".TB_PREF."purch_order_details
		WHERE po_detail_item =".db_escape($po_line_id);

	$result = db_query($sql, "get_po_line_record");
        return db_fetch($result);

}

function get_miss_matching_purchase_order_lines($order_no){
    $sql = "SELECT po.tax_included po_tax_included,
                    pod.tax_included pod_tax_included,
                    po.tax_group_id po_tax_group_id,
                    pod.tax_group_id pod_tax_group_id,
                    pod.po_detail_item pod_id,
                    po.order_no  
                    
            FROM ".TB_PREF."purch_orders po   
                JOIN ".TB_PREF."purch_order_details pod 
                    ON po.order_no = pod.order_no  
            WHERE po.order_no = ".db_escape($order_no)." 
                AND pod.order_no = po.order_no 
                AND (pod.tax_included <> po.tax_included OR pod.tax_group_id <> po.tax_group_id)";
                
    return db_query($sql, "get_miss_matching_purchase_order_lines");
}

function update_po_header_row_state($po_no, $row_state)
{
       $sql = "UPDATE ".TB_PREF."purch_orders 
                SET row_state = ". db_new_row_state($row_state)." 
                WHERE order_no = " . db_escape($po_no). " 
                    AND row_state = ". db_escape($row_state);

	db_query($sql, "update_po_header_row_state");
    
        return db_num_affected_rows();
}

