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

//-------------------------------------------------------------------------------------------------------------
include_once($path_to_root . "/purchasing/includes/db/purchase_requisitions_db.inc");

function add_pr_issue(&$items, $reference, $requesting_location, $issuing_location, $project_id, $memo_, $pri_date_,
                        $source_pr_no, $close_purch_req, $pr_row_state, $po_no, 
                        $movement_type, $linked_field_type, $linked_field_id, $group_company_code,
                        $employee_code, $employee_company_code) {
    
    global $loc_notification, $path_to_root, $Refs;
        
    $type = ST_PR_ISSUE;
//    $date = sql2date(date('Y/m/d'));
    $pri_date = date2sql($pri_date_);

    $pr_no = $source_pr_no;

    $created_by = $_SESSION['wa_current_user']->user;

    $total_cost = 0;
    $issue_total = 0;

    begin_transaction();
    $args = func_get_args();
    $args = (object)array_combine(array('items', 'reference', 'requesting_location', 'issuing_location', 'project_id', '$memo_', 'pri_date', 
                            'source_pr_no', 'pr_compplete', 'pr_row_state', 'po_no', 
                            'movement_type', 'linked_field_type', 'linked_field_id', 'group_company_code',
                            'employee_code', 'employee_company_code'), $args);
    $args->trans_no = 0;
    hook_db_prewrite($args, $type);

    if($reference == '' || $reference == null ){
        $reference = $Refs->get_next($type);
    }
        
    $uprs_msg = update_pr_status($source_pr_no, AS_ISSUED, $pr_row_state, $created_by);
    if($uprs_msg['success'] !== true)
    {
        cancel_transaction();
        return array("passed" => false, "tran_no" => $source_pr_no, "message" => $uprs_msg['message']);
    }

    $pri_no = add_pr_issues_header($reference, $requesting_location, $issuing_location, $project_id, $memo_, 
                                    $source_pr_no, $po_no, $created_by, $pri_date,
                                    $movement_type, $linked_field_type, $linked_field_id, $group_company_code,
                                    $employee_code, $employee_company_code);

    if(!($pri_no > 0))
    {
        cancel_transaction();
        return array("passed" => false, "message" => "Could not add purchase requisition transfer");
    }
        
    foreach ($items as &$line_item)
    {
        if($line_item->quantity > 0 && $line_item->item_selected){
            $item_info = get_stock_item_edit_info($line_item->stock_id);
            if($item_info['mb_flag'] != "D"){
                $inventory_item = true;
            }
            else{
                $inventory_item = false;
            }

            $pr_issue_detail_id = add_pr_issue_item($pri_no, $line_item->pr_line_id,
                        $line_item->stock_id, $line_item->item_description,
                        $line_item->quantity, $line_item->charging_cost, 
                        $line_item->tran_unit, $line_item->tran_qty,
                        $line_item->tran_unit_conv_factor, 
                        $line_item->po_line_id, $line_item->price,
                        $line_item->task_id, $line_item->project_site_id,
                        0, $line_item->cost_source);
            
            $line_item->target_line_id = $pr_issue_detail_id;
            
            if($inventory_item){
    //                add_stock_move(ST_PR_ISSUE, $line_item->stock_id, $pri_no, $issuing_location,
    //                        $date, $reference, -$line_item->quantity, $item_info['standard_cost'], null, null, null, null, null, 
    //                        $line_item->tran_unit,-$line_item->tran_qty, $line_item->tran_unit_conv_factor,
    //                        $line_item->task_id, $line_item->project_site_id);

                add_stock_adjustment_item($pri_no, $line_item->stock_id, $issuing_location, $pri_date_, 
                                $reference, -$line_item->quantity, $item_info['standard_cost'], $memo_, 
                                $line_item->tran_unit, -$line_item->tran_qty, $line_item->tran_unit_conv_factor, ST_PR_ISSUE, 
                                $line_item->task_id, $line_item->project_site_id, $pr_issue_detail_id, $project_id);

                $ailqdm_msg =  add_item_line_quantity_detail_movements(ST_PR_ISSUE, $pri_no, $issuing_location, 
                                                                        $pri_date_, $line_item, false, '');
                if($ailqdm_msg['success'] !== true){
                    cancel_transaction();
                    return array("success" => $ailqdm_msg['success'], "tran_no" => $source_pr_no, "message" => $ailqdm_msg['message']);
                }
            }else{
                add_non_stock_move(ST_PR_ISSUE, $line_item->stock_id, $pri_no, $issuing_location, $pri_date_, $reference, -$line_item->quantity, $line_item->charging_cost, 0, 1, 
                            0, 0, $line_item->tran_unit, -$line_item->tran_qty, $line_item->tran_unit_conv_factor, $line_item->task_id, $line_item->project_site_id, 
                            $pr_issue_detail_id, $line_item->item_description, $project_id);
            }

            $total_issued_qty = get_pr_item_total_issued_qty($line_item->pr_line_id);
            $allow_over_issue = $po_no > 0 ? true: false;
            $n = update_pr_item_issued_qty($line_item->pr_line_id, $total_issued_qty, $allow_over_issue);
            if($n !== 1){
                cancel_transaction();
                $message = "Line Id ".$line_item->pr_line_id." : Item ".$line_item->stock_id." - ".$line_item->item_description
                            ." : Entered quantity creates an over issue for the PR line #".$line_item->pr_line_id;
                return array("success" => false, "tran_no" => $source_pr_no, "message" => $message);
            }
            
        }
    }

        $pr_total_msg = update_pr_issue_header_total($pri_no);
        if($pr_total_msg['success'] !== true){
            cancel_transaction();
            return $pr_total_msg;
        }
        
	$Refs->save(ST_PR_ISSUE, $pri_no, $reference);
	add_audit_trail(ST_PR_ISSUE, $pri_no, $pri_date_);
	
//	if ($loc_notification == 1 && count($st_ids) > 0)
//		send_reorder_email($loc, $st_ids, $st_names, $st_num, $st_reorder);  
        
        
	$args->trans_no = $pri_no;
	hook_db_postwrite($args, $type);

    $pr_details = get_pr_details($pr_no);
    
    while ($myrow = db_fetch($pr_details))
    {
        $a['row_id'] = $myrow['id'];
        $a['stock_id'] = $myrow['item_code'];
        $a['task_id'] = $myrow['task_id'];
        $a['requested_qty'] = $myrow['requested_unit_qty'] * $myrow['req_unit_conv_factor'];
        $a['high_value_item'] = 0;
//        if($myrow['mb_flag'] == "D"){
//            //get first po line from pr_po_details
//            $po_lines_for_pr_line = get_pr_line_requisition_allocations($pr_details['id']);
//            $po_line_average_price = clculate_po_line_average_price($po_lines_for_pr_line);
//            $item_cost =  $po_line_average_price;
//        }
//        else{
//            $item_cost = $myrow['standard_cost'];
//        }
        
        $item_cost = $myrow['estimate_price'] / $myrow['req_unit_conv_factor'] ;
        $a['reference_cost'] = $item_cost;
        $a['standard_cost'] = $item_cost;
        $a['total_issued_qty'] = $myrow['issued_qty'] - $myrow['returned_qty'];
        $a['qty'] = 0;
        $a['charging_cost'] = 0;
        $pr_details_array[] = $a;
        
    }

//    foreach ($pr_details_array as &$pr_line){
//        $pr_line['qty'] = $pr_line['charging_cost'] = 0;
//        foreach ($items as $pr_issue_item){
//            if($pr_issue_item->row_id == $pr_line['row_id'] && $pr_issue_item->item_selected){
//                $pr_line['qty'] = $pr_issue_item->tran_qty * $pr_issue_item->tran_unit_conv_factor;
//                $pr_line['charging_cost'] = $pr_issue_item->charging_cost;
//                break;
//            }
//        }
//    }
    
    foreach ($items as $pri_item){
        $pri_item->row_id;
        $pri_item->pr_line_id;
        $pri_item->pr_po_line_id;
//        $charging_cost = get_tax_free_price_for_item($grn_item->stock_id, $grn_item->price, $grn->tax_group_id, $grn->tax_included);  
        $charging_cost = $pri_item->charging_cost;
        foreach ($pr_details_array as &$pr_line){
            if($pri_item->row_id == $pr_line['row_id'] && $pri_item->item_selected){
                $pr_line['qty'] = $pri_item->tran_qty * $pri_item->tran_unit_conv_factor;
                $pr_line['charging_cost'] += $charging_cost;
            }
        }                    
    }
    
    $tasks_summary = calculate_task_remaining_totals($pr_details_array);
    //$tasks_summary = calculate_mr_task_remaining_totals($items);
    
    $apc_msg = allocate_to_tasks(ST_PURCH_REQ, $pr_no, 
                                $tasks_summary, $created_by);
    if($apc_msg['success'] !== true)
    {
        cancel_transaction();
        return array("success" => $apc_msg['success'], "tran_no" => $pr_no, "message" => $apc_msg['message']);
    }
    
    //$im_total = calculate_document_total($items);
    
    $cct_msg = charge_cost_to_tasks(ST_PR_ISSUE, $pri_no, 
                                $tasks_summary, $created_by);
    
    if($cct_msg['success'] !== true)
    {
        cancel_transaction();
        return array("success" => $cct_msg['success'], "tran_no" => $pri_no, "message" => $cct_msg['message']);
    }
    
    commit_transaction();
    
    if(check_all_pr_lines_issued($source_pr_no)=== true){
            close_pr($source_pr_no, AS_COMPLETED);
    }
    else{
        if($close_purch_req){
            close_pr($source_pr_no, AS_CLOSED);
        }        
    }
        
    return array("success" => true, "tran_no" => $pri_no, "message" => "Purchase requisition issue $pri_no has been created");
    

}

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


function add_pr_issue_from_grn($grn, $pr_header, $reference)
{
    global $loc_notification, $path_to_root, $Refs;
        
    $type = ST_PR_ISSUE;
    $date = sql2date(date('Y/m/d'));

    $pr_no = $grn->receiving_pr_no;
    $pr_row_state = $pr_header['row_state'];
//    $reference = $pr_header['reference'];
    $requesting_location = $pr_header['location'];
    $issuing_location = $grn->Location;
    $project_id = $pr_header['project_id'];
    $memo = "";
    $po_no = $grn->order_no;
    $pri_date = $grn->orig_order_date;
    
    $created_by = $_SESSION['wa_current_user']->user;

    $total_cost = 0;
    $issue_total = 0;

	begin_transaction();
	$args = func_get_args();
	$args = (object)array_combine(array('grn', 'pr_header', 'reference'), $args);
	$args->trans_no = 0;
	hook_db_prewrite($args, $type);

    if($reference == '' || $reference == null )
	{
        $reference = $Refs->get_next($type);
    }
        
    $uprs_msg = update_pr_status($pr_no, AS_ISSUED, $pr_row_state, $created_by);
    if($uprs_msg['success'] !== true)
    {
        cancel_transaction();
        return array("passed" => false, "tran_no" => $pr_no, "message" => $uprs_msg['message']);
    }

    $pri_no = add_pr_issues_header($reference, $requesting_location, $issuing_location, $project_id, $memo, 
                                    $pr_no, $po_no, $created_by, $pri_date);

    if(!($pri_no > 0))
    {
        cancel_transaction();
        return array("passed" => false, "message" => "Could not add purchase requisition transfer");
    }
      
        
//	foreach ($items as $line_item)
    foreach ($grn->line_items as $line_item){
        if($line_item->quantity > 0 && $line_item->item_selected && $line_item->pr_line_id > 0){
            $item_info = get_stock_item_edit_info($line_item->stock_id);
            if($item_info['mb_flag'] != "D"){
                $inventory_item = true;
            }
            else{
                $inventory_item = false;
            }
            
            $cost_source = "GRN line #".$line_item->grn_item_id;
            
            $already_issued_qty = get_pr_item_total_issued_qty($line_item->pr_line_id);
            $prd = get_pr_item($line_item->pr_line_id);
            if($prd['quantity'] - 0.000001 > $already_issued_qty + $line_item->receive_qty){
                $this_issue_base_unit_qty = $line_item->receive_qty;
                $this_issue_tran_unit_qty = $line_item->tran_qty;
            }
            else{
//                $this_issue_base_unit_qty = $prd['quantity'] - $already_issued_qty;
//                if($this_issue_base_unit_qty > 0){
//                    $this_issue_tran_unit_qty = $this_issue_base_unit_qty / $line_item->convertion_factor;
//                }
//                else{
//                    continue; // can not issue. Creates an over issue
//                }    
                
                //------------------------------------------
                // Over issues allowed as full grn qty does not get charged to the project. 2021-07-05
                //------------------------------------------
                $this_issue_base_unit_qty = $line_item->receive_qty;
                $this_issue_tran_unit_qty = $line_item->tran_qty;
            }
            
            add_pr_issue_item($pri_no, $line_item->pr_line_id,
                        $line_item->stock_id, $line_item->item_description,
                        $this_issue_base_unit_qty, $line_item->price, 
                        $line_item->tran_unit, $this_issue_tran_unit_qty,
                        $line_item->convertion_factor, 
                        $line_item->po_detail_rec, $line_item->price,
                        $line_item->task_id, $line_item->project_site_id,
                        $line_item->grn_item_id, $cost_source);
            
            if($inventory_item){
//                add_stock_move(ST_PR_ISSUE, $line_item->stock_id, $pri_no, $issuing_location,
//                        $date, $reference, -$line_item->quantity, $item_info['standard_cost'], null, null, null, null, null, 
//                        $line_item->tran_unit,-$line_item->tran_qty, $line_item->tran_unit_conv_factor,
//                        $line_item->task_id, $line_item->project_site_id);
                
                add_stock_adjustment_item($pri_no, $line_item->stock_id, $issuing_location, $date, 
                                $reference, -$line_item->receive_qty, $item_info['standard_cost'], $memo, 
                                $line_item->tran_unit, -$line_item->tran_qty, $line_item->convertion_factor, ST_PR_ISSUE, 
                                $line_item->task_id, $line_item->project_site_id, $line_item->pr_line_id);

                $ailqdm_msg =  add_item_line_quantity_detail_movements(ST_PR_ISSUE, $pri_no, $issuing_location, 
                                                                        $date, $line_item, false, '');
                if($ailqdm_msg['success'] !== true){
                    cancel_transaction();
                    return array("success" => $ailqdm_msg['success'], "tran_no" => $pri_no, "message" => $ailqdm_msg['message']);
                }
                
                
            }
//            $total_issued_qty = get_pr_item_total_issued_qty($line_item->pr_line_id);
            
            $total_issued_qty = $already_issued_qty + $this_issue_base_unit_qty;
            $n = update_pr_item_issued_qty($line_item->pr_line_id, $total_issued_qty, true);
            if($n !== 1){
                cancel_transaction();
                $message = "Line Id ".$line_item->po_detail_rec." : Item ".$line_item->stock_id." - ".$line_item->item_description
                        ." : Entered quantity creates an over issue for the PR line #".$line_item->pr_line_id;
                return array("success" => false, "tran_no" => $pr_no, "message" => $message);
            }
        }
    }
    
    $pr_total_msg = update_pr_issue_header_total($pri_no);
    if($pr_total_msg['success'] !== true){
        cancel_transaction();
        return $pr_total_msg;
    }

	$Refs->save(ST_PR_ISSUE, $pri_no, $reference);
	add_audit_trail(ST_PR_ISSUE, $pri_no, $date);
	
//	if ($loc_notification == 1 && count($st_ids) > 0)
//		send_reorder_email($loc, $st_ids, $st_names, $st_num, $st_reorder);  
        
        
	$args->trans_no = $pri_no;
	hook_db_postwrite($args, $type);

    $pr_details = get_pr_details($pr_no);
    
    while ($myrow = db_fetch($pr_details))
    {
        $a['row_id'] = $myrow['id'];
        $a['stock_id'] = $myrow['item_code'];
        $a['task_id'] = $myrow['task_id'];
        $a['requested_qty'] = $myrow['requested_unit_qty'] * $myrow['req_unit_conv_factor'];
        $a['high_value_item'] = 0;
//        if($myrow['mb_flag'] == "D"){
//            //get first po line from pr_po_details
//            $po_lines_for_pr_line = get_pr_line_requisition_allocations($pr_details['id']);
//            $po_line_average_price = clculate_po_line_average_price($po_lines_for_pr_line);
//            $item_cost =  $po_line_average_price;
//        }
//        else{
//            $item_cost = $myrow['standard_cost'];
//        }
        
//        $item_cost = $myrow['standard_cost'];
        $item_cost = $myrow['estimate_price'] / $myrow['req_unit_conv_factor'] ;
        $a['reference_cost'] = $item_cost;
        $a['standard_cost'] = $item_cost;
        $a['total_issued_qty'] = $myrow['issued_qty'];
        $a['qty'] = 0;
        $a['charging_cost'] = 0;
        $pr_details_array[] = $a;
        
    }

//    foreach ($pr_details_array as &$pr_line){
//        $pr_line['qty'] = $pr_line['charging_cost'] = 0;
//        foreach ($items as $pr_issue_item){
//            if($pr_issue_item->row_id == $pr_line['row_id'] && $pr_issue_item->item_selected){
//                $pr_line['qty'] = $pr_issue_item->tran_qty * $pr_issue_item->tran_unit_conv_factor;
//                $pr_line['charging_cost'] = $pr_issue_item->charging_cost;
//                break;
//            }
//        }
//    }
    
    foreach ($grn->line_items as $grn_item){
        $grn_item->row_id;
        $grn_item->pr_line_id;
        $grn_item->pr_po_line_id;
//        $charging_cost = get_tax_free_price_for_item($grn_item->stock_id, $grn_item->price, $grn->tax_group_id, $grn->tax_included);  
        $charging_cost = $grn_item->price;
        foreach ($pr_details_array as &$pr_line){
            if($grn_item->pr_line_id == $pr_line['row_id'] && $grn_item->item_selected){
                $pr_line['qty'] = $grn_item->tran_qty * $grn_item->convertion_factor;
                $pr_line['charging_cost'] += $charging_cost;
            }
        }                    
    }
    
    $tasks_summary = calculate_task_remaining_totals($pr_details_array);
    //$tasks_summary = calculate_mr_task_remaining_totals($items);
    
    $apc_msg = allocate_to_tasks(ST_PURCH_REQ, $pr_no, 
                                $tasks_summary, $created_by);
    if($apc_msg['success'] !== true)
    {
        cancel_transaction();
        return array("success" => $apc_msg['success'], "tran_no" => $pr_no, "message" => $apc_msg['message']);
    }
    
    //$im_total = calculate_document_total($items);
    
    $cct_msg = charge_cost_to_tasks(ST_PR_ISSUE, $pri_no, 
                                $tasks_summary, $created_by);
    
    if($cct_msg['success'] !== true)
    {
        cancel_transaction();
        return array("success" => $cct_msg['success'], "tran_no" => $pri_no, "message" => $cct_msg['message']);
    }
    
    commit_transaction();
    
    $source_pr_no = $grn->receiving_pr_no;
    $pr_complete = false;
    if($pr_complete){
        close_pr($source_pr_no);
    }
    else{
        if(check_all_pr_lines_issued($source_pr_no)=== true){
            close_pr($source_pr_no, AS_COMPLETED);
        }
    }
        
    return array("success" => true, "tran_no" => $pri_no, "message" => "Purchase Requisition Issue $pri_no has been created");
    

}

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


function clculate_po_line_average_price()
{
    
}


function add_pr_issues_header($reference, $requesting_location, $issuing_location, $project_id, $memo_, 
                                $source_pr_no, $po_no, $created_by, $pri_date, 
                                $movement_type=0, $linked_field_type=0, $linked_field_id=0, $group_company_code='',
                                $employee_code='', $employee_company_code='') {
	//$mr_date = date2sql($date_);
        $created_on = date('Y-m-d H:i:s');

	$sql = "INSERT INTO ".TB_PREF."purchase_requisition_issues(reference,"
                        . " requesting_location,"
                        . " issuing_location,"
                        . " pri_date,"
                        . " project_id,"
                        . " source_pr_no, "
                        . " comment, "
                        . " po_no, "
                        . " movement_type, linked_field_type, "
                        . " linked_field_id, group_company_code, "
                        . " employee_code, employee_company_code, "
                        . " created_by, created_on) "
                . "VALUES (".db_escape($reference).", "
                        .db_escape($requesting_location).", "
                        .db_escape($issuing_location).", "
                        .db_escape($pri_date).", "
                        .db_escape($project_id).", "
                        .db_escape($source_pr_no).", "
                        .db_escape($memo_).", "
                        .db_escape($po_no).", "
                        .db_escape($movement_type).", ".db_escape($linked_field_type).", "
                        .db_escape($linked_field_id).", ".db_escape($group_company_code).", "
                        .db_escape($employee_code).", ".db_escape($employee_company_code).", "
                        .db_escape($created_by).", ".db_escape($created_on).")";

	$error_msg = "The purchase requisition issue could not be inserted";

	db_query($sql, $error_msg);

	return db_insert_id();
}
//--------------------------------------------------------------------------------------------------


function update_pr_status($pr_no, $status, &$row_state, $current_user_id)
{
        $info['approval_status'] = $status;
        $info['tran_no'] = $pr_no;
        $info['row_state'] = $row_state;
        $info['current_user_id'] = $current_user_id;
        $info['tran_type'] = ST_PURCH_REQ;
        $info['change_type'] = '';

        update_document_status($info);
        $row_state = $info['row_state'];
        
        if($info['success'] === true ){
            return array("success" => true, "message" => " PR $pr_no has been updated.");
        }
        else{
            return array("success" => false, "message" => $info['message']);
        }
    
}

function add_pr_issue_item($pri_no, $pr_line_id,
                        $stock_id, $description,
                        $qty_issued, $unit_cost, 
                        $tran_unit, $tran_qty,
                        $convertion_factor, 
                        $po_line_id, $po_price,
                        $task_id, $project_site_id, 
                        $grn_item_id=0, $cost_source='')
{
    $sql = "INSERT INTO ".TB_PREF."purchase_requistion_issue_details (
                    pri_no, pr_line_id,
                    item_code, description,
                    qty_issued, unit_cost,
                    tran_unit, tran_qty,
                    conversion_factor, 
                    po_line_id, po_price,
                    task_id, project_site_id, 
                    grn_item_id, cost_source) 
            VALUES (".db_escape($pri_no).", ".db_escape($pr_line_id).", "
                    .db_escape($stock_id).", ".db_escape($description).", "
                    .db_escape($qty_issued).", ".db_escape($unit_cost).", "
                    .db_escape($tran_unit).", ".db_escape($tran_qty).", "
                    .db_escape($convertion_factor).", "
                    .db_escape($po_line_id).", ".db_escape($po_price).", "
                    .db_escape($task_id).", ".db_escape($project_site_id).", "
                    .db_escape($grn_item_id).", ".db_escape($cost_source).")";
    
    db_query($sql, "add_pr_issue_item");
    
    return db_insert_id();
}

//function get_material_issue_header($pri_no)
//{
//    $sql = "SELECT mrt.*, 
//                    rl.location_name receiving_location_name,
//                    il.location_name requesting_location_name,
//                    u.real_name created_user_name 
//            FROM ".TB_PREF."material_requisition_transfers mrt,
//                    ".TB_PREF."locations rl,
//                    ".TB_PREF."locations il,
//                    ".TB_PREF."users u 
//            WHERE mrt.requesting_location = il.loc_code AND 
//                    mrt.receiving_location = rl.loc_code AND 
//                    mrt.created_by = u.id AND  
//                    mrt_no = '".$mrt_no."'";
//    
//    $result = db_query($sql);
//    return db_fetch($result);
//    
//}
//
////--------------------------------------------------------------------------------------------------
//
//function get_material_issue_details($pri_no, $type)
//{
//    $sql = "SELECT  smoves.stock_id ,
//                    smoves.qty ,
//                    smaster.description  
//            FROM    ".TB_PREF."stock_moves smoves,
//                    ".TB_PREF."stock_master smaster  
//            WHERE   smoves.stock_id = smaster.stock_id AND 
//                    smoves.trans_no = '".$pri_no."' AND type = '".$type."'";
//    
//    $result = db_query($sql);
//    
//    return $result;
//    
//}

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

function get_pr_item_total_issued_qty($pr_line_id)
{
    $sql = "SELECT SUM(qty_issued) total_issued_qty  
            FROM ".TB_PREF."purchase_requistion_issue_details 
            WHERE pr_line_id = ".db_escape($pr_line_id);
    
    $result = db_query($sql, "get_pr_item_total_issued_qty");
    return db_result_value($result, "total_issued_qty");
}

function get_latest_purchase_requistion_issue_item_unit_cost($item_code){
    $sql = "SELECT id, unit_cost, conversion_factor 
            FROM ".TB_PREF."purchase_requistion_issue_details 
            WHERE item_code = ".db_escape($item_code)." 
            ORDER BY pri_no, id DESC
            LIMIT 5";
    
    return db_query($sql, "could not get_latest_purchase_requistion_issue_item_unit_cost");
}




function update_pr_issue_header_total($pri_no){
    $lines_total = get_pr_issue_header_details_lines_total($pri_no);
    $sql = "UPDATE ".TB_PREF."purchase_requisition_issues   
                SET total = ".db_escape($lines_total)." 
            WHERE pri_no = ".db_escape($pri_no);
    
    db_query($sql, "could not update_pr_issue_header_total");
    if(!(db_num_affected_rows() > 0)){
        return array("success" => false, "message" => "Could not Update PR Issues Header #$pri_no Total.");
    }else{
        return array("success" => true, "message" => "");
    }
}
function get_pr_issue_header_details_lines_total($pri_no)
{
    
//    $sql = "SELECT SUM(FORMAT(unit_cost * conversion_factor,2) * tran_qty) lines_total 
//            FROM ".TB_PREF."purchase_requistion_issue_details   
//            WHERE pri_no = ".  db_escape($pri_no);
    
    $sql = "SELECT (unit_cost * conversion_factor) tran_unit_cost , tran_qty 
            FROM ".TB_PREF."purchase_requistion_issue_details   
            WHERE pri_no = ".  db_escape($pri_no);
    
    $result = db_query($sql, "could not get_pr_issue_header_details_lines_total");
    
    $total = 0;
    while ($row = db_fetch($result)) {
        $nn = round($row['tran_unit_cost'],2);
        $tran_qty = doubleval($row['tran_qty']);
        $tt = doubleval($nn * $tran_qty);
        $total += $tt;
    }
    return $total;
}


function get_pr_issue_details_for_return($grn_line_id)
{
    $sql = "SELECT prid.*,
                pri.issuing_location,
                pri.project_id,
                grni.grn_batch_id grn_no, 
                grn.loc_code location,
                pri.source_pr_no pr_no,
                prd.issued_qty,
                prd.returned_qty 
            FROM ".TB_PREF."purchase_requistion_issue_details prid   
                JOIN ".TB_PREF."purchase_requisition_issues pri 
                    ON pri.pri_no = prid.pri_no 
                JOIN ".TB_PREF."purchase_requisition_details prd 
                    ON prd.id = prid.pr_line_id 
                JOIN ".TB_PREF."grn_items grni  
                    ON grni.id = prid.grn_item_id  
                JOIN ".TB_PREF."grn_batch grn   
                    ON grn.id = grni.grn_batch_id  
            WHERE prid.grn_item_id = ".  db_escape($grn_line_id);
    
    $result = db_query($sql, "get_pr_issue_detail_from_grn_line_id");
    return db_fetch($result);
}


function get_pr_issue_pri_no($pri_no) {
    $sql = "SELECT pri.*, 
                    lo.location_name,
                    pm.description project_description,
                    us.real_name originator_name        

            FROM ".TB_PREF."purchase_requisition_issues pri,
                    ".TB_PREF."locations lo,
                    ".TB_PREF."projects_master pm,
                    ".TB_PREF."users us
             
            WHERE lo.loc_code = pri.issuing_location AND
                pm.project_id = pri.project_id AND
                us.id = pri.created_by AND
                pri.pri_no=".db_escape($pri_no);
    
    $result = db_query($sql, "get_pr_issue_pri_no");     
    return db_fetch($result);
}



function get_pr_issue_details($pri_no) {
    $sql = "SELECT prid.*,
                    ps.description site_name,
                    pt.description task_description,
                    pt.task_code,
                    mov.qty,
                    mov.standard_cost,
                    sm.mb_flag 

            FROM ".TB_PREF."purchase_requistion_issue_details prid 
                JOIN ".TB_PREF."project_tasks pt ON 
                    prid.task_id = pt.task_id 
                JOIN ".TB_PREF."stock_master sm ON 
                    prid.item_code = sm.stock_id 
                LEFT JOIN ".TB_PREF."stock_moves mov ON
                    mov.type = ".db_escape(ST_PR_ISSUE)." AND 
                    mov.stock_id = prid.item_code AND 
                    mov.trans_no = prid.pri_no AND 
                    mov.ref_line_id = prid.pr_line_id  
                LEFT JOIN ".TB_PREF."project_sites ps ON
                    prid.project_site_id = ps.id 
                
                
            WHERE prid.pri_no=".db_escape($pri_no);
    
      return db_query($sql, 'get_pr_issue_details');    
    
}


function get_pri_deatails_record_by_id($id) {
    $sql = "SELECT * 
            FROM ".TB_PREF."purchase_requistion_issue_details 
            WHERE id = ".db_escape($id);
    
        $result = db_query($sql, "get_pr_item_total_issued_qty");
        return db_fetch($result);
}


function get_pri_record_by_id($pri_no) {
    $sql = "SELECT * 
            FROM ".TB_PREF."purchase_requisition_issues   
            WHERE pri_no = ".db_escape($pri_no);
    
        $result = db_query($sql, "get_pri_record_by_id");
        return db_fetch($result);
}



function check_all_pr_issue_lines_returned($source_pri_no)
{
    $sql = "SELECT SUM(IF(qty_issued - returned_qty > 0, 1, 0)) outstanding_line_count  
            FROM ".TB_PREF."purchase_requistion_issue_details  
            WHERE pri_no = ".db_escape($source_pri_no);
    
    $result = db_query($sql);
    $line_count = db_fetch($result);
    if(!$line_count){
        return true;
    }
    else{
        if($line_count['outstanding_line_count'] == 0){
            return true;
        }
        else{
            return false;
        }
    }
    
}



function update_pr_issue_fully_returned($pri_no){

    $sql = "UPDATE ".TB_PREF."purchase_requisition_issues   
                SET status = ".db_escape(AS_COMPLETED)." 
            WHERE pri_no = ".db_escape($pri_no);
    
    db_query($sql, "update_pr_issue_fully_returned");
    
}