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

function add_material_transfer_requisition($mtr_cart, $items, $reference, $requesting_location, $issuing_location, $mtr_date, $memo_, $mtr_complete, 
                                           $project_id, $release, $movement_type, $sample_items, $linked_field_type, $linked_field_id) {
    global $Refs;
        
        $created_by = $_SESSION['wa_current_user']->user;
        $next_approval_group_id = $mtr_cart->next_approval_group_id;
        $current_user_id = $_SESSION['wa_current_user']->user;

	begin_transaction();
	$args = func_get_args();
	$args = (object)array_combine(array('mtr_cart', 'items', 'reference', 'requesting_location', 'issuing_location', 
                                            'mtr_date', 'memo_', 'mtr_complete', 'project_id', 'release', 'movement_type', 
                                            'sample_item', 'linked_field_type', 'linked_field_id'), $args);
        
	$args->trans_no = 0;
	hook_db_prewrite($args, ST_TRANSFERREQ);

    if($reference == '' || $reference == null ){
        $mtr_auto_ref = get_next_trans_no(ST_TRANSFERREQ);
        $reference = $mtr_auto_ref;
    }
        
    $mtr_no = add_mtr_header($mtr_date, $reference, $requesting_location, $issuing_location, $mtr_complete, $memo_, $created_by, $project_id, 
                             $movement_type, $sample_items, $linked_field_type, $linked_field_id);

    if(!($mtr_no > 0)){
        cancel_transaction();
        return array("success" => false, "tran_no" => $mtr_no, "message" => "Could not create the Material Transfer Requisition");
    }
    
    foreach ($items as $line_item){
        add_mtr_item($mtr_no, $line_item->stock_id, $line_item->required_date, $line_item->standard_cost, 
                     $line_item->quantity, $line_item->line_comment, $created_by, $line_item->tran_unit, 
                     $line_item->tran_qty, $line_item->convertion_factor, $line_item->project_site_id, $line_item->task_id);
    }

    $Refs->save(ST_TRANSFERREQ, $mtr_no, $reference);
    add_audit_trail(ST_TRANSFERREQ, $mtr_no, $mtr_date);

    $args->trans_no = $mtr_no;
    

    hook_db_postwrite($args, ST_TRANSFERREQ);
	
        
    if(!$release){
        commit_transaction();
        $text = "Material Transfer Requisition $mtr_no Created.";
        return array("success" => true, "tran_no" => $mtr_no, "message" => $text);
    }
     
    $mtr_reference_total = calculate_mtr_remaining_total($mtr_cart->line_items, true);
    $info = approve_document(ST_TRANSFERREQ, $mtr_no, $project_id, $mtr_reference_total, $next_approval_group_id, $current_user_id, 0);

        if($info['success'] !== true){
            cancel_transaction();
            return array("success" => $info['success'],"tran_no" => $mtr_no, "message" => $info['message']);
        }
        
    
        if ($info['approval_status'] != AS_APPROVED) {    
            commit_transaction();
            return array("success" => true, "tran_no" => $mtr_no, "message" => $info['message']);
        }
    
//    $tasks_summary = calculate_mtr_task_remaining_totals($mtr_no);
//        $apc_msg = allocate_to_tasks(ST_TRANSFERREQ, $mtr_no, $tasks_summary, $current_user_id);
//        if ($apc_msg['success'] !== true) {
//            cancel_transaction();
//            return array("success" => $apc_msg['success'], "tran_no" => $mtr_no, "message" => $text." ".$info['message']);
//        }
    
    commit_transaction();
            
    return array("success" => true, "tran_no" => $mtr_no, "message" => $info['message']);        
}

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

function update_material_transfer_requisition($mtr_no, $mtr_cart, $reference, $requesting_location, $issuing_location , $mtr_date, $memo_, 
                                              $mtr_complete, $project_id, $release, $movement_type, $row_state, $linked_field_type, $linked_field_id) {
        $info = "";
        $items = $mtr_cart->line_items;
        $next_approval_group_id = $mtr_cart->next_approval_group_id;
        
        $current_user_id = $_SESSION['wa_current_user']->user;

	begin_transaction();
	$args = func_get_args();
	$args = (object)array_combine(array('mtr_no', 'mtr_cart', 
                'reference', 'requesting_location','issuing_location', 'mtr_date', 
                'memo_', 'mtr_complete', 'project_id', 'release', 
                'movement_type', 'row_state', 'linked_field_type', 'linked_field_id'), $args);
	$args->trans_no = 0;
    
	hook_db_prewrite($args, ST_TRANSFERREQ);

    $num_rows_affected = update_mtr_header($mtr_no, $reference, $requesting_location, $issuing_location, $mtr_date, $mtr_complete, $memo_, 
                                           $current_user_id, $project_id, $movement_type, $linked_field_type, $linked_field_id, $row_state);

    if($num_rows_affected !== 1){
        cancel_transaction();
        return array("success" => false, "tran_no" => $mtr_no, "message" => get_message('ANOTHER_USER_HAS_UPDATED'));
    }

    delete_mtr_items($mtr_no);

    foreach ($items as $line_item)
    {
        recreate_mtr_item($mtr_no, $line_item, $current_user_id);
    }

    add_audit_trail(ST_TRANSFERREQ, $mtr_no, $mtr_date);

    $args->trans_no = $mtr_no;
    hook_db_postwrite($args, ST_TRANSFERREQ);
    

    if(!$release){    
        commit_transaction();
        return array("success" => true, "message" => "Material Transfer Requisition $mtr_no has been updated");
    }
   
        $tasks_summary = 0;
//        $tasks_summary = calculate_mtr_task_remaining_totals($mtr_no);

        $info = approve_document(ST_TRANSFERREQ, $mtr_no, $project_id, $tasks_summary, $next_approval_group_id, $current_user_id, 
                                 db_new_row_state($row_state));
        if($info['success'] !== true){
            cancel_transaction();
            return array("success" => $info['success'], "message" => $info['message']);
        }
        
//        if ($info['approval_status'] == AS_APPROVED) {            
//            $apc_msg = allocate_to_tasks(ST_TRANSFERREQ, $mtr_no, $tasks_summary, $current_user_id);
//                if ($apc_msg['success'] !== true) {
//                    cancel_transaction();
//                    return array("success" => $apc_msg['success'], "tran_no" => $mtr_no, "message" => $apc_msg['message']." ".$info['message']);
//                }
//        }
            
        commit_transaction();    
//        cancel_transaction();    
            
        return array("success" => true, "message" => $info['message']);
    
}

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

function void_material_requisition($type_no)
{
	hook_db_prevoid(ST_TRANSFERREQ, $type_no);
	void_gl_trans(ST_TRANSFERREQ, $type_no);
	void_stock_move(ST_TRANSFERREQ, $type_no);
}

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

function get_material_requisition_items($trans_no)
{
	$result = get_stock_moves(ST_TRANSFERREQ, $trans_no);

	if (db_num_rows($result) == 0)
	{
		return null;
	}

	return $result;
}


function get_requestor_for_mtr($id)
{
    $sql = "SELECT real_name FROM ".TB_PREF."users WHERE id =". db_escape($id);
    $result = db_query($sql,"User could not be retreived");
    return db_fetch($result);
}

function calculate_mtr_remaining_total($line_items, $use_reference_costs = false)
{
        $remaining_total = 0;
        foreach ($line_items as $line_item)
        {
            if($use_reference_costs){
                if($line_item->high_value_item){
                    $item_cost = $line_item->reference_cost;
                }
                else{
                    $item_cost = $line_item->standard_cost;
                }
            }
            else{
                $item_cost = $line_item->standard_cost;
            }
            $line_remaining_total = ($line_item->requested_qty - $line_item->issued_qty)* $item_cost;
            $remaining_total += $line_remaining_total;
        }
        
        return $remaining_total;
}
//--------------------------------------------------------------------------------------------------

function add_mtr_header($mtr_date, $reference, $requesting_location, 
                        $issuing_location, $mtr_complete, $memo_, 
                        $created_by, $project_id, $movement_type, 
                        $sample_items, $linked_field_type, $linked_field_id)
{
	$mtr_date = date2sql($mtr_date);
        $created_on = date('Y-m-d H:i:s');

	$sql = "INSERT INTO ".TB_PREF."material_transfer_requisitions( mtr_date, reference,"
                        . " requesting_location, issuing_location,"
                        . " mtr_complete, comments,"
                        . " project_id,  movement_type, "
                        . " sample_items, linked_field_type, linked_field_id, "
                        . " created_by, created_on) "
                . "VALUES (".db_escape($mtr_date).", ".db_escape($reference).", "
                        .db_escape($requesting_location).", ".db_escape($issuing_location).", "
                        .db_escape($mtr_complete).", ".db_escape($memo_).", "
                        .db_escape($project_id).", ".db_escape($movement_type).", "
                        .db_escape($sample_items).", ".db_escape($linked_field_type).", ".db_escape($linked_field_id).", "
                        .db_escape($created_by).", ".db_escape($created_on).")";


		$error_msg = "The material transfer requisition record cannot be inserted";

	db_query($sql, $error_msg);

	return db_insert_id();
}


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

function update_mtr_header($mtr_no, $reference, $requesting_location, $issuing_location,
                            $mtr_date, $mtr_complete, $memo_, $updated_by, 
                            $project_id, $movement_type, $linked_field_type, $linked_field_id,
                            $row_state)
{
	$mtr_date = date2sql($mtr_date);
        $updated_on = date('Y-m-d H:i:s');

	$sql = "UPDATE ".TB_PREF."material_transfer_requisitions 
                SET  
                    reference = ".db_escape($reference).", 
                    project_id = ".db_escape($project_id).", 
                    requesting_location = ".db_escape($requesting_location).", 
                    issuing_location = ".db_escape($issuing_location).", 
                    mtr_date = ".db_escape($mtr_date).", 
                    comments = ".db_escape($memo_).", 
                    mtr_complete = ".db_escape($mtr_complete).",                
                    movement_type = ".db_escape($movement_type).",
                    linked_field_type = ".db_escape($linked_field_type).", 
                    linked_field_id = ".db_escape($linked_field_id).",    
                    comments = ".db_escape($memo_).",                
                    updated_by = ".db_escape($updated_by).", 
                    updated_on = ".db_escape($updated_on).", 
                    row_state = ".db_new_row_state($row_state)." 
                WHERE mtr_no = ".$mtr_no." AND 
                    row_state = ". db_escape($row_state);

    $error_msg = "Could not update material transfer requisition record";

	db_query($sql, $error_msg);

	return db_num_affected_rows();
}

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



function add_mtr_item($mtr_no, $stock_id, $required_date, $price, $quantity, $line_comment, $created_by, $tran_unit, $tran_qty, $req_unit_conv_factor, 
                      $project_site_id, $task_id) {
    $mb_flag = get_mb_flag($stock_id);

        if (is_service($mb_flag))
        {
            display_db_error("Cannot request Service item : $stock_id", "");
        }

	$required_date = date2sql($required_date);
        $created_on = date('Y-m-d H:i:s');

	$sql = "INSERT INTO ".TB_PREF."material_transfer_requisition_details (mtr_no, "
                        . " stock_id, required_date, "
                        . " price, requested_qty, "
                        . " requested_unit, requested_unit_qty, "
                        . " req_unit_conv_factor, line_comment, "
                        . " project_site_id, task_id, "
                        . " created_by, created_on) "
                . " VALUES (".db_escape($mtr_no).", " 
                        .db_escape($stock_id).", ".db_escape($required_date).", "
                        .db_escape($price).", ".db_escape($quantity).", "
                        .db_escape($tran_unit).", ".db_escape($tran_qty).", "
                        .db_escape($req_unit_conv_factor).", ".db_escape($line_comment).", "
                        .db_escape($project_site_id).", ".db_escape($task_id).", "
                        .db_escape($created_by).", ".db_escape($created_on).")";

    $error_msg = "The material transfer requisition detail could not be inserted";

	db_query($sql, $error_msg);

	return db_insert_id();
}

//-------------------------------------------------------------------------------------------------------------
function delete_mtr_items($mtr_no, $error_msg = "")
{
    $sql = " DELETE FROM ".TB_PREF."material_transfer_requisition_details 
            WHERE mtr_no = ".$mtr_no;
    
    	if ($error_msg == "")
		$error_msg = "The material transfer requisition items could not be removed";

	db_query($sql, $error_msg);
}

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


function recreate_mtr_item($mtr_no, $li, $updated_by)
{
	$mb_flag = get_mb_flag($li->stock_id);

        if (is_service($mb_flag))
        {
            display_db_error("Cannot request Service item : $li->stock_id", "");
        }

	$required_date = date2sql($li->required_date);
    $updated_on = date('Y-m-d H:i:s');

    if($li->created_by){
        $created_by = $li->created_by;
    }
    else {
        $created_by = $updated_by;
    }

    if($li->created_on){
        $created_on = $li->created_on;
    }
    else {
        $created_on = $updated_on;
    }
    
    $standard_cost = get_standard_cost($li->stock_id);
        
	$sql = "INSERT INTO ".TB_PREF."material_transfer_requisition_details(
                        id, mtr_no, 
                        stock_id, 
                        required_date, price, standard_cost, 
                        requested_qty, requested_unit,                         
                        requested_unit_qty, req_unit_conv_factor,                        
                        issued_qty, line_comment, task_id, 
                        project_site_id, line_complete,
                        created_by, created_on, 
                        updated_by, updated_on) 
                VALUES (".db_escape($li->row_id).", ".db_escape($mtr_no).", "
                        .db_escape($li->stock_id).", "
                        .db_escape($required_date).", ".db_escape($li->price).", ".db_escape($standard_cost).", "
                        .db_escape($li->requested_qty).", ".db_escape($li->tran_unit).", "            
                        .db_escape($li->tran_qty).", ".db_escape($li->convertion_factor).", "            
                        .db_escape($li->issued_qty).", ".db_escape($li->line_comment).", "
                        .db_escape($li->task_id).", "
                        .db_escape($li->project_site_id).", ".db_escape($li->line_complete).", "
                        .db_escape($created_by).", ".db_escape($created_on).", "
                        .db_escape($updated_by).", ".db_escape($updated_on).")";
                            
	$error_msg = "The material transfer requisition record cannot be inserted";

	db_query($sql, $error_msg);

}


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


//function close_mtr($mtr_no)
//{
//    $mtr = get_mtr_edit_info($mtr_no);
//
//    $info['approval_status'] = AS_COMPLETED;
//    $info['tran_no'] = $mtr_no;
//    $info['row_state'] = $mtr['row_state'];
//    $info['current_user_id'] = $_SESSION['wa_current_user']->user;
//    $info['tran_type'] = ST_TRANSFERREQ;
//    $info['change_type'] = '';
//            
//    $num_recs = update_document_status($info);
//
//    if($num_recs == 1){
//        return array("success" => true, "message" => " Material Transfer Requisition $mtr_no has been closed.");
//    }
//    else{
//        return array("success" => false, "message" => "Material Transfer Requisition $mtr_no has been updated another user.");
//    }
//
//}

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

function get_mtr_edit_info($mtr_no)
{
        
    $sql = " SELECT * FROM ".TB_PREF."material_transfer_requisitions 
                WHERE mtr_no = ".$mtr_no ;
    
    $result = db_query($sql,"Material Transfer Requisition could not be retreived");

    return db_fetch($result);

}
//-------------------------------------------------------------------------------------------------------------

function update_mtr_item_issued_qty($row_id) {
    $total_issued_qty = get_mrt_total_issued_qty_by_ref_line_id($row_id);
    
	$sql = "UPDATE ".TB_PREF."material_transfer_requisition_details 
                SET issued_qty = ".db_escape($total_issued_qty)."  
                WHERE id = ".db_escape($row_id);

	db_query($sql, 'update_mtr_item_issued_qty');
}

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


function get_sql_for_mtr_view($required_by_from = null, $required_by_to = null, 
            $created_on_from = null, $created_on_to = null,  
            $requested_location = "",  $issuing_location = "",
            $mtr_no = 0,  $mtr_reference = "", $mtr_complete = null, 
            $stock_item = null, $current_user=0)
{
        $filter = "";
        
        if($required_by_from != null)
        {
            $filter = " AND mtr.mtr_date > ".db_escape(date2sql($required_by_from));
        }
        
        if($required_by_to != null)
        {
                $filter .= " AND mtr.mtr_date < ".db_escape(date2sql($required_by_to));
        }
        
        if($created_on_from != null)
        {
                $filter .= " AND mtr.created_on > ".db_escape(date2sql($created_on_from));
        }
        
        if($created_on_to != null)
        {
                $filter .= " AND mtr.created_on < ".db_escape(date2sql($created_on_to));
        }
        
        if($requested_location != "")
        {
                $filter .= " AND mtr.requesting_location = ".db_escape($requested_location);  
        }
        
        if($issuing_location != "")
        {
                $filter .= " AND mtr.issuing_location = ".db_escape($issuing_location);            
        }
        
        if ($mtr_no != 0)
        {
                $filter .= " AND mtr.mtr_no = ".$mtr_no;
        }
        
        if ($mtr_reference != "")
        {
                $filter .= " AND mtr.reference = ".db_escape($mtr_reference); 
        }
        

        $filter .= " AND mtr.mtr_complete = ".db_escape($mtr_complete);         

        
        //$filter = "";
        
        if ($stock_item == null)
        {
            $sql = "SELECT 
                        mtr.mtr_no,
                        mtr.reference, 
                        mtr.requesting_location,
                        mtr.issuing_location,
                        mtr.mtr_date mtr_required_date,
                        mtr.comments, 
                        mtr.mtr_complete,
                        mtr.created_on
                    FROM 
                        ".TB_PREF."material_transfer_requisitions mtr,
                        ".TB_PREF."approvers a 
                    WHERE  
                        a.approval_group_id = mtr.next_approval_group_id AND 
                        mtr.approval_status NOT IN(30,40) AND 
                        a.user_id = ".db_escape($current_user)." "
                        .$filter." 
                    ORDER BY
                        mtr.mtr_no";
        }
        else
        {
            $filter .= " AND sm.stock_id = ".db_escape($stock_item);         
        
            $sql = "SELECT DISTINCT
                        mtr.mtr_no,
                        mtr.reference, 
                        mtr.requesting_location,
                        mtr.issuing_location,
                        mtr.mtr_date mtr_required_date,
                        mtr.comments, 
                        mtr.mtr_complete,
                        mtr.created_on
                    FROM 
                        ".TB_PREF."material_transfer_requisitions mtr,
                        ".TB_PREF."approvers a,
                        ".TB_PREF."material_transfer_requisition_details mtrd,
                        ".TB_PREF."stock_master sm
                    WHERE 
                        a.approval_group_id = mtr.next_approval_group_id AND 
                        mtr.approval_status NOT IN(30,40) AND 
                        a.user_id = ".db_escape($current_user)." AND
                        mtr.mtr_no = mtrd.mtr_no AND 
                        mtrd.stock_id = sm.stock_id "
                        .$filter."  
                    ORDER BY
                        mtr.mtr_no";
        }

	return $sql;
}

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


function get_sql_for_mtr_inquiry($required_by_from = null, $required_by_to = null, $requested_location = "",  $issuing_location = "", $mtr_no = 0,  
                                 $mtr_reference = "", $current_user=0, $filtering_status=-1) {
        $filter = "";
        
        if($required_by_from != null)
        {
            $filter = " AND mtr.mtr_date >= ".db_escape(date2sql($required_by_from));
        }
        
        if($required_by_to != null)
        {
                $filter .= " AND mtr.mtr_date <= ".db_escape(date2sql($required_by_to));
        }
        
        if($requested_location != "")
        {
                $filter .= " AND mtr.requesting_location = ".db_escape($requested_location);  
        }
        
        if($issuing_location != "")
        {
                $filter .= " AND mtr.issuing_location = ".db_escape($issuing_location);            
        }
        
        if ($mtr_no > 0)
        {
                $filter .= " AND mtr.mtr_no = ".$mtr_no;
        }
        
        if ($mtr_reference != "")
        {
                $filter .= " AND mtr.reference = ".db_escape($mtr_reference); 
        }
        
        if ($filtering_status > -1)
        {
                $filter .= " AND mtr.approval_status = ".db_escape($filtering_status); 
        }
        

            $sql = "SELECT 
                        mtr.mtr_no,
                        mtr.reference, 
                        rl.location_name requesting_location,
                        il.location_name issuing_location,
                        p.description,
                        mtr.mtr_date mtr_required_date,
                        u.real_name originator, 
                        mtr.approval_status,
                        mtr.created_on
                    FROM 
                        ".TB_PREF."material_transfer_requisitions mtr,
                        ".TB_PREF."projects_master as p, 
                        ".TB_PREF."locations il,
                        ".TB_PREF."locations rl,
                        ".TB_PREF."users u 
                    WHERE  mtr.project_id = p.project_id AND 
                        mtr.issuing_location = il.loc_code AND 
                        mtr.requesting_location = rl.loc_code AND 
                        mtr.created_by = u.id "
                        .$filter." 
                    ORDER BY
                        mtr.mtr_no";

	return $sql;
}


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


function get_sql_for_aprove_mtr_inquiry($current_user_id)
{
//        $filter = "";
//        
//        if($required_by_from != null)
//        {
//            $filter = " AND mtr.mtr_date > ".db_escape(date2sql($required_by_from));
//        }
//        
//        if($required_by_to != null)
//        {
//                $filter .= " AND mtr.mtr_date < ".db_escape(date2sql($required_by_to));
//        }
//        
//        if($requested_location != "")
//        {
//                $filter .= " AND mtr.requesting_location = ".db_escape($requested_location);  
//        }
//        
//        if($issuing_location != "")
//        {
//                $filter .= " AND mtr.issuing_location = ".db_escape($issuing_location);            
//        }
//        
//        if ($mtr_no != 0)
//        {
//                $filter .= " AND mtr.mtr_no = ".$mtr_no;
//        }
//        
//        if ($mtr_reference != "")
//        {
//                $filter .= " AND mtr.reference = ".db_escape($mtr_reference); 
//        }
//        
//
//        $filter .= " AND mtr.mtr_complete = ".db_escape($mtr_complete);         

        
        //$filter = "";
        

            $sql = "SELECT 
                        mtr.mtr_no,
                        mtr.reference, 
                        rl.location_name req_location_name,
                        il.location_name iss_location_name,
                        p.description,
                        mtr.mtr_date mtr_required_date,
                        u.real_name,
                        mtr.created_on,
                        mtr.approval_status 
                    FROM 
                        ".TB_PREF."material_transfer_requisitions mtr,
                        ".TB_PREF."projects_master as p, 
                        ".TB_PREF."locations il,
                        ".TB_PREF."locations rl,
                        ".TB_PREF."approvers as apr,  
                        ".TB_PREF."user_data_access_groups as udag,
                        ".TB_PREF."users u     
                    WHERE  mtr.project_id = p.project_id AND 
                        mtr.issuing_location = il.loc_code AND 
                        mtr.requesting_location = rl.loc_code AND 
                        (mtr.approval_status = ".AS_AWAITING_APPROVAL." OR mtr.approval_status = ".AS_WITH_THE_APPROVER.") AND 
                        mtr.next_approval_group_id = apr.approval_group_id AND 
                        p.dag_id = udag.dag_id AND 
                        udag.user_id = apr.user_id AND 
                        apr.user_id = u.id AND 
                        apr.user_id = ".$current_user_id." 
                    ORDER BY
                        mtr.mtr_no";
        

	return $sql;
}


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

function get_material_transfer_requisition($mtr_no)
{
    $sql = "SELECT 
                mtr.mtr_no,
                mtr.reference, 
                mtr.requesting_location,
                mtr.project_id, 
                pm.description project_description,
                mtr.issuing_location,
                mtr.mtr_date mtr_required_date,
                mtr.comments, 
                mtr.mtr_complete,
                u.real_name requestor_name,
                ap.approval_path_name approval_path_description,
                mtr.next_approval_group_id,
                mtr.approval_status,
                mtr.created_by,
                mtr.created_on, 
                mtr.movement_type, 
                mt.name movement_type_name, 
                mtr.linked_field_type,
                mtr.linked_field_id,
                mtr.sample_items, 
                mtr.row_state,
                rl.location_name requesting_location_name,
                il.location_name issuing_location_name
            FROM ".TB_PREF."material_transfer_requisitions mtr 
                INNER JOIN ".TB_PREF."projects_master pm ON 
                        mtr.project_id = pm.project_id 
                INNER JOIN ".TB_PREF."users u ON   
                        mtr.created_by = u.id 
                INNER JOIN ".TB_PREF."locations il ON 
                        mtr.issuing_location = il.loc_code 
                INNER JOIN ".TB_PREF."locations rl on 
                        mtr.requesting_location = rl.loc_code 
                LEFT JOIN ".TB_PREF."movement_types mt ON 
                        mtr.movement_type = mt.id 
                LEFT JOIN ".TB_PREF."project_approval_paths pap ON 
                        pap.project_id = mtr.project_id AND 
                        pap.approval_path_type = 'MR' 
                LEFT JOIN ".TB_PREF."approval_path ap ON 
                        pap.approval_path_id = ap.approval_path_id 
            WHERE  
                mtr.mtr_no = ".  db_escape($mtr_no);
    
    $result = db_query($sql,"Material transfer requisition could not be retreived");

    return db_fetch($result);

}

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

function get_mtr_details($mtr_no)
{
    $sql = "SELECT 
                mtrd.*,
                sm.description,
                sm.units,
                pt.description task_description,
                sm.high_value_item,
                sm.sample_item,
                sm.reference_cost, 
                sm.material_cost + sm.labour_cost + sm.overhead_cost AS standard_cost, 
                sm.stock_control_method,
                mtrd.project_site_id,
                ps.description project_site_description 
            FROM 
                ".TB_PREF."material_transfer_requisition_details mtrd 
                INNER JOIN ".TB_PREF."stock_master sm ON 
                        mtrd.stock_id = sm.stock_id 
                LEFT JOIN ".TB_PREF."project_sites ps ON
                        mtrd.project_site_id = ps.id 
                LEFT JOIN ".TB_PREF."project_tasks pt ON
                    mtrd.task_id = pt.task_id 
            WHERE 
                
                mtrd.mtr_no = ".$mtr_no." 
            ORDER BY
                mtrd.id";
    
    $result = db_query($sql,"Material transfer requisition details could not be retreived");

    return $result;

}

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

function get_mtr_item($row_id)
{
        
    $sql = "SELECT issued_qty 
            FROM ".TB_PREF."material_transfer_requisition_details 
            WHERE id = ".$row_id ;
    
    $result = db_query($sql,"Material transfer_requisition item detail could not be retreived");

    return db_fetch($result);

}
//-------------------------------------------------------------------------------------------------------------


function get_total_qty_issued($mtr_no)
{
        
    $sql = " SELECT SUM(issued_qty) total_qty_issued FROM ".TB_PREF."material_transfer_requisition_details 
                WHERE mtr_no = ".$mtr_no ;
    
    $result = db_query($sql,"Material transfer requisition issued quantity total could not be retreived");

    return db_fetch($result);

}

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

function get_material_transfers_for_requisition($mtr_no) {
    $sql = "SELECT 
                mrt.*,
                u.real_name issuer_name,
                rl.location_name receiving_location_name,
                il.location_name issuing_location_name 
            FROM ".TB_PREF."material_requisition_transfers mrt,
                ".TB_PREF."users u,
                ".TB_PREF."locations il,
                ".TB_PREF."locations rl
            WHERE mrt.created_by = u.id AND 
                mrt.source_mtr_no = ".db_escape($mtr_no)." 
                AND mrt.type = ".db_escape(ST_REQTRANSFER)." 
                AND mrt.issuing_location = il.loc_code  
                AND mrt.receiving_location = rl.loc_code";
    
    return db_query($sql, "get_material_transfers_for_requisition");
}

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

function check_all_mtr_lines_transferred($source_mtr_no)
{
    $sql = "SELECT SUM(IF(requested_qty - issued_qty > 0, 1, 0)) outstanding_line_count  
            FROM ".TB_PREF."material_transfer_requisition_details 
            WHERE mtr_no = ".db_escape($source_mtr_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_mtr_status($mtr_no, $status, &$row_state, $current_user_id)
{
        $info['approval_status'] = $status;
        $info['tran_no'] = $mtr_no;
        $info['row_state'] = $row_state;
        $info['current_user_id'] = $current_user_id;
        $info['tran_type'] = ST_TRANSFERREQ;
        $info['change_type'] = '';

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

function update_mtr_item_received_qty($mrt_line_id) {
    $row = get_mrt_deatails_record_by_id($mrt_line_id);
    
    $mtr_line_id = $row['ref_line_id'];
    $total_received_qty = get_mt_receipt_total_received_qty_by_mtr_line_id($mtr_line_id);
    
	$sql = "UPDATE ".TB_PREF."material_transfer_requisition_details   
                SET received_qty = ".db_escape($total_received_qty)."  
                WHERE id = ".db_escape($mtr_line_id);

	db_query($sql, 'update_mtr_item_received_qty');
}

function update_mtr_row_state($mtr_no, $mtr_row_state) {
    $sql = "UPDATE ".TB_PREF."material_transfer_requisitions    
            SET row_state = row_state + 1   
            WHERE mtr_no = ".db_escape($mtr_no)." 
                AND row_state = ".db_escape($mtr_row_state);

	db_query($sql, 'update_mtr_row_state');
        $num_af_r = db_num_affected_rows();
        
        if (! ($num_af_r  == 1)) {
            return array('success' => false, "Another user has updated MTR header record #$mtr_no.");
        } else {
            return array('success' => true, "");
        }
}

function get_mtr_deatails_record_by_id($id) {
    $sql = "SELECT * 
            FROM ".TB_PREF."material_transfer_requisition_details    
            WHERE id = ".db_escape($id);
    
        $result = db_query($sql, 'get_mtr_deatails_record_by_id');
        return db_fetch($result);
}

function get_mtr_header_record_by_id($mtr_no) {
    $sql = "SELECT * 
            FROM ".TB_PREF."material_transfer_requisitions     
            WHERE mtr_no = ".db_escape($mtr_no);
    
        $result = db_query($sql, 'get_mtr_header_record_by_id');
        return db_fetch($result);
}

function check_all_mtr_lines_issued($mt_rqt_no) {
    $sql = "SELECT SUM(IF(requested_qty - issued_qty > 0, 1, 0)) outstanding_line_count  
            FROM ".TB_PREF."material_transfer_requisition_details   
            WHERE mtr_no = ".db_escape($mt_rqt_no);
    
    $result = db_query($sql, 'check_all_mtr_lines_issued');
    $line_count = db_result_value($result, 'outstanding_line_count');
    
        if ($line_count > 0) {
            return false;
        } else {
            return true;
        }
}

?>