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

function add_material_requisition($mr_cart, $items, $reference, 
                                $issuing_location, $project_id, 
                                $date_, $memo_, $mr_complete, 
                                $release, $movement_type, $sample_items,
                                $linked_field_type, $linked_field_id)
{
	global $loc_notification, $path_to_root, $Refs;
        
        $created_by = $_SESSION['wa_current_user']->user;
        $next_approval_group_id = $mr_cart->next_approval_group_id;
        $current_user_id = $_SESSION['wa_current_user']->user;
        
	begin_transaction();
	$args = func_get_args();
	$args = (object)array_combine(array('mr_cart', 'items', 'reference', 
                                'issuing_location', 'project_id', 
                                'date_', 'memo_', 'mr_complete', 
                                'release', 'movement_type', 'sample_items',
                                'linked_field_type', 'linked_field_id'), $args);
	$args->trans_no = 0;
	hook_db_prewrite($args, ST_MATERIAL_REQ);

        if($reference == '' || $reference == null )
	{
            $mr_auto_ref = get_next_trans_no(ST_MATERIAL_REQ);
            $reference = $mr_auto_ref;
        }
        
        $mr_no = add_mr_header($date_, $reference, $issuing_location, 
                                $project_id, $mr_complete, 
                                $memo_, $created_by, $movement_type, 
                                $sample_items,
                                $linked_field_type, $linked_field_id);
         
        if(!$mr_no > 0){
            cancel_transaction();
            return array("success" => false, "tran_no" => $mr_no, "message" => get_message('ANOTHER_USER_HAS_UPDATED'));
        }
        else{
            foreach ($items as $line_item)
            {
                add_mr_item($mr_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->task_id,
                            $line_item->project_site_id );
            }

            $Refs->save(ST_MATERIAL_REQ, $mr_no, $reference);
            add_audit_trail(ST_MATERIAL_REQ, $mr_no, $date_);

            $args->trans_no = $mr_no;
        }
        
        
        
	hook_db_postwrite($args, ST_MATERIAL_REQ);

        
        if(!$release){
            	commit_transaction();
                return array("success" => true, "tran_no" => $mr_no, "message" => "Material requisition $mr_no has been created.");
        }
        else{        
            $text = "Material Requisition $mr_no Created.";
            //As it is a new release, below gives the full total of the document
            $tasks_summary = calculate_mr_task_remaining_totals($mr_no);      
          
    
            $info = approve_document(ST_MATERIAL_REQ, $mr_no, $project_id, $tasks_summary, 
                                        $next_approval_group_id, $current_user_id, 0);
            if($info['success'] !== true)
            {
                cancel_transaction();
                return array("success" => $info['success'],"tran_no" => $mr_no, "message" => $text." ".$info['message']);
            }

            if($info['approval_status'] == AS_APPROVED)
            {            
                //$mr_total = calculate_mr_remaining_total($mr_cart->line_items);
                $apc_msg = allocate_to_tasks(ST_MATERIAL_REQ, $mr_no, 
                                            $tasks_summary, $current_user_id);
                if($apc_msg['success'] !== true)
                {
                    cancel_transaction();
                    return array("success" => $apc_msg['success'], "tran_no" => $mr_no, "message" => $text." ".$info['message']);
                }
            }
            commit_transaction();
            return array("success" => true, "tran_no" => $mr_no, "message" => $text." ".$info['message']);
        }
        
        
//        return array("success" => true, "tran_no" => $mr_no, "message" => "Material Requisition created");
//	return $mr_no;
}

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

function update_material_requisition($mr_no, $mr_cart, 
                                    $issuing_location, $project_id, 
                                    $mr_date, $memo_, $mr_complete, 
                                    $mr_reference, $release, $movement_type, 
                                    $row_state,
                                    $linked_field_type, $linked_field_id)
{
	global $loc_notification, $path_to_root, $Refs;
        
        $info = "";
        $items = $mr_cart->line_items;
        $next_approval_group_id = $mr_cart->next_approval_group_id;
        
        $current_user_id = $_SESSION['wa_current_user']->user;

	begin_transaction();
	$args = func_get_args();
	$args = (object)array_combine(array('mr_no', 'mrt_cart',  
                                        'issuing_location','project_id', 
                                        'mr_date', 'memo_', 'mr_complete', 
                                        'mr_reference', 'release', 'movement_type', 
                                        'row_state',
                                        'linked_field_type', 'linked_field_id'), $args);
	$args->trans_no = 0;
	hook_db_prewrite($args, ST_MATERIAL_REQ);
        
    $num_rows_affected = update_mr_header($mr_no, $mr_date, $mr_complete, 
                                    $memo_, $current_user_id, $issuing_location, 
                                    $project_id, $mr_reference, 
                                    $movement_type, $row_state, 
                                    $linked_field_type, $linked_field_id);
    
    if($num_rows_affected !== 1){
        cancel_transaction();
        return array("success" => false, "tran_no" => $mr_no, "message" => get_message('ANOTHER_USER_HAS_UPDATED'));
    }


    delete_mr_items($mr_no);

    foreach ($items as $line_item)
    {
            recreate_mr_item($mr_no, $line_item, $current_user_id);
    }

    add_audit_trail(ST_MATERIAL_REQ, $mr_no, $mr_date);

    $args->trans_no = $mr_no;
    hook_db_postwrite($args, ST_MATERIAL_REQ);
	
    
    if(!$release){
        commit_transaction();
        return array("success" => true, "message" => "Material requisition $mr_no has been updated");
    }
    else{
        $tasks_summary = calculate_mr_task_remaining_totals($mr_no);
        $info = approve_document(ST_MATERIAL_REQ, $mr_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'],"tran_no" => $mr_no, "message" => $info['message']);
        }

        if($info['approval_status'] == AS_APPROVED){            
            $apc_msg = allocate_to_tasks(ST_MATERIAL_REQ, $mr_no, 
                                        $tasks_summary, $current_user_id);
            if($apc_msg['success'] !== true)
            {
                cancel_transaction();
                return array("success" => $apc_msg['success'], "tran_no" => $mr_no, "message" => $info['message']);
            }
        }
        
        commit_transaction();
        return array("success" => true, "tran_no" => $mr_no, "message" => $info['message']);
    }
}


function calculate_mr_task_remaining_totals($mr_no)
{
        $task_costs = array();
        
//        foreach ($line_items as $line_item)
//        {
//            //In MRs price field contains the item standard cost used to calculate the cost allocation.
//            //So use the same value when calculating the MR line values for allocation / reallocation
//            $standard_cost_at_allocation = $line_item->price;
//            if($line_item->high_value_item){
//                $item_reference_cost = $standard_cost_at_allocation;
//                $item_cost = $standard_cost_at_allocation;
//            }
//            else{
//                $item_reference_cost = $standard_cost_at_allocation;
//                $item_cost = $standard_cost_at_allocation;
//            }
//            
//            $line_remaining_total = (($line_item->tran_qty * $line_item->convertion_factor) - $line_item->issued_qty)* $item_cost;
//            $line_remaining_reference_total = (($line_item->tran_qty * $line_item->convertion_factor) - $line_item->issued_qty)* $item_reference_cost;
//            
//            add_to_task_cost($task_costs, $line_item->task_id, $line_remaining_total, $line_remaining_reference_total, 0);
//        }
//        
//        return $task_costs;
        
    $mr_details = get_mr_details($mr_no);

    while ($myrow = db_fetch($mr_details))
    {
        $a['row_id'] = $myrow['id'];
        $a['stock_id'] = $myrow['stock_id'];
        $a['task_id'] = $myrow['task_id'];
        $a['requested_qty'] = $myrow['requested_qty'];
        $a['high_value_item'] = $myrow['high_value_item'];
        $a['reference_cost'] = $myrow['price'];
        $a['standard_cost'] = $myrow['price'];
        $a['total_issued_qty'] = $myrow['issued_qty'];
        $a['qty'] = $myrow['requested_qty']; - $a['total_issued_qty'];

        $mr_details_array[] = $a;

    }
    
    $mr_tasks_summary = calculate_task_remaining_totals($mr_details_array);
    return $mr_tasks_summary;
    
}

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

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

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

	return $result;
}

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

function add_mr_header($date_, $reference, $issuing_location, 
                        $project_id, $mr_complete, 
                        $memo_, $created_by, $movement_type, 
                        $sample_items, 
                        $linked_field_type, $linked_field_id)
{
	$mr_date = date2sql($date_);
        $created_on = date('Y-m-d H:i:s');

	$sql = "INSERT INTO ".TB_PREF."material_requisitions( mr_date, reference, "
                        . " issuing_location, project_id,"
                        . " movement_type, sample_items,"
                        . "linked_field_type, linked_field_id, "
                        . " mr_complete, comments,"
                        . " created_by, created_on) "
                . "VALUES (".db_escape($mr_date).", ".db_escape($reference).", "
                        .db_escape($issuing_location).", ".db_escape($project_id).", "
                        .db_escape($movement_type).", ".db_escape($sample_items).", "
                        .db_escape($linked_field_type).", ".db_escape($linked_field_id).", "
                        .db_escape($mr_complete).", ".db_escape($memo_).", "
                        .db_escape($created_by).", ".db_escape($created_on).")";

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

	db_query($sql, $error_msg);

	return db_insert_id();
}


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

function update_mr_header($mr_no, $mr_date, $mr_complete, 
                        $memo_, $updated_by, $issuing_location, 
                        $project_id, $mr_reference, 
                        $movement_type, $row_state, 
                        $linked_field_type, $linked_field_id)
{
	$mr_date = date2sql($mr_date);
    $updated_on = date('Y-m-d H:i:s');

	$sql = "UPDATE ".TB_PREF."material_requisitions 
                SET  
                        mr_date = ".db_escape($mr_date).", 
                        comments = ".db_escape($memo_).", 
                        mr_complete = ".db_escape($mr_complete).",  
                        updated_by = ".db_escape($updated_by).", 
                        updated_on = ".db_escape($updated_on).",
                        issuing_location = ".db_escape($issuing_location).",
                        project_id = ".db_escape($project_id).", 
                        movement_type = ".db_escape($movement_type).",
                        reference = ".db_escape($mr_reference).", 
                        linked_field_type = ".db_escape($linked_field_type).",
                        linked_field_id = ".db_escape($linked_field_id).",
                        row_state = ".db_new_row_state($row_state)." 
                WHERE mr_no = ".$mr_no." AND row_state = ".$row_state;

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

	db_query($sql, $error_msg);
    
    return db_num_affected_rows();

}

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


function add_mr_item($mr_no, $stock_id, 
                    $required_date, $price, $quantity, 
                    $line_comment, $created_by, $tran_unit, 
                    $tran_qty, $convertion_factor, $task_id, 
                    $project_site_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_requisition_details (mr_no, "
                        . " stock_id, "
                        . " required_date, "
                        . " price, requested_qty, "
                        . " line_comment, requested_unit, "
                        . " requested_unit_qty, req_unit_conv_factor, "
                        . " task_id, project_site_id, "
                        . " created_by, created_on) "
                . " VALUES (".db_escape($mr_no).", " 
                        .db_escape($stock_id).", "
                        .db_escape($required_date).", "
                        .db_escape($price).", ".db_escape($quantity).", "
                        .db_escape($line_comment).", ".db_escape($tran_unit).", "
                        .db_escape($tran_qty).", ".db_escape($convertion_factor).", "
                        .db_escape($task_id).", ".db_escape($project_site_id).", "
                        .db_escape($created_by).", ".db_escape($created_on).")";

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

	db_query($sql, $error_msg);

	return db_insert_id();
}

//-------------------------------------------------------------------------------------------------------------
function delete_mr_items($mr_no, $error_msg = "")
{
    $sql = " DELETE FROM ".TB_PREF."material_requisition_details 
            WHERE mr_no = ".$mr_no;
    
    	if ($error_msg == "")
		$error_msg = "The material requisition items could not be removed";

	db_query($sql, $error_msg);
}

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


function recreate_mr_item($mr_no, $line_item, $updated_by)
{
	$mb_flag = get_mb_flag($line_item->stock_id);

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

	$required_date = date2sql($line_item->required_date);
        $updated_on = date('Y-m-d H:i:s');
        
    if($line_item->created_by > 0){
        $created_by = $line_item->created_by;
        $created_on = $line_item->created_on;
    }
    else {
        $created_by = $updated_by;
        $created_on = $updated_on;
    }
        
        //$$created_on = date2sql($created_on);
        
    if($line_item->row_id > 0){
	$sql = "INSERT INTO ".TB_PREF."material_requisition_details(
                        id, mr_no, stock_id, 
                        required_date, price, requested_qty, 
                        issued_qty, line_comment, line_complete, 
                        requested_unit, requested_unit_qty, req_unit_conv_factor,
                        task_id, project_site_id,
                        created_by, created_on, 
                        updated_by, updated_on) 
                VALUES (".db_escape($line_item->row_id).", ".db_escape($mr_no).", ".db_escape($line_item->stock_id).", "
                        .db_escape($required_date).", ".db_escape($line_item->standard_cost).", ".db_escape($line_item->quantity).", "
                        .db_escape($line_item->issued_qty).", ".db_escape($line_item->line_comment).", ".db_escape($line_item->line_complete).", "
                        .db_escape($line_item->tran_unit).", ".db_escape($line_item->tran_qty).", ".db_escape($line_item->convertion_factor).", "
                        .db_escape($line_item->task_id).", ".db_escape($line_item->project_site_id).", "
                        .db_escape($created_by).", ".db_escape($created_on).", "
                        .db_escape($updated_by).", ".db_escape($updated_on).")";
    }else{
        $sql = "INSERT INTO ".TB_PREF."material_requisition_details(
                        mr_no, stock_id, 
                        required_date, price, requested_qty, 
                        issued_qty, line_comment, line_complete, 
                        requested_unit, requested_unit_qty, req_unit_conv_factor,
                        task_id, project_site_id,
                        created_by, created_on, 
                        updated_by, updated_on) 
                VALUES (".db_escape($mr_no).", ".db_escape($line_item->stock_id).", "
                        .db_escape($required_date).", ".db_escape($line_item->standard_cost).", ".db_escape($line_item->quantity).", "
                        .db_escape($line_item->issued_qty).", ".db_escape($line_item->line_comment).", ".db_escape($line_item->line_complete).", "
                        .db_escape($line_item->tran_unit).", ".db_escape($line_item->tran_qty).", ".db_escape($line_item->convertion_factor).", "
                        .db_escape($line_item->task_id).", ".db_escape($line_item->project_site_id).", "
                        .db_escape($created_by).", ".db_escape($created_on).", "
                        .db_escape($updated_by).", ".db_escape($updated_on).")";
    }
                
		$error_msg = "The material requisition could not be inserted";

	db_query($sql, $error_msg);

}

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


function close_mr($mr_no, $closing_status = AS_COMPLETED)
{
    $can_close_mr = false;
    
    $current_user_id = $_SESSION['wa_current_user']->user;
    $mrh = get_material_requisition_header_record($mr_no);
    begin_transaction();
    if($mrh['approval_status'] == AS_APPROVED || $mrh['approval_status'] == AS_ISSUED){
        $can_close_mr = false;
        $tasks_summary = calculate_mr_task_remaining_totals($mr_no);
//        get_task_cost_details($tasks_summary);
        $rta_msg = remove_task_allocations(ST_MATERIAL_REQ, $mr_no, 
                                $tasks_summary, $current_user_id);
        if($rta_msg['success'] !== true){
            cancel_transaction();
            return $rta_msg;
        }
        else{
            $can_close_mr = true;
        }
    }
    else{
        $can_close_mr = false;
        cancel_transaction();
        return array("success" => false, "message" => "Material Requisition $mr_no status does not allow closing it.");
    }
    
    
    if($can_close_mr){
//        $info['approval_status'] = $closing_status;
//        $info['tran_no'] = $mr_no;
//        $info['row_state'] = $mrh['row_state'];
//        $info['current_user_id'] = $_SESSION['wa_current_user']->user;
//        $info['tran_type'] = ST_MATERIAL_REQ;
//        $info['change_type'] = '';
//
//        if($manual_close){
        
        $mr_close_msg = direct_update_document_status(ST_MATERIAL_REQ, $mr_no, $closing_status, $mrh['row_state']);     

        if($mr_close_msg['success'] === true ){
            close_mr_for_prs($mr_no);
            commit_transaction();
            return array("success" => true, "message" => " MR $mr_no has been closed.");
        }
        else{
            cancel_transaction();
            return array("success" => false, "message" => $mr_close_msg['message']);
        }
    }
    
    cancel_transaction();
    return array("success" => false, "message" => "Could not close MR $mr_no.");
}

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

function update_mr_item_issued_qty($row_id, $this_issue_qty)
{
//	$issued_qty = get_mr_item($row_id);
//    $new_issued_qty = $issued_qty['issued_qty'] + $this_issued_qty;

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

	$sql = "UPDATE ".TB_PREF."material_requisition_details 
                SET     issued_qty = issued_qty + ".$this_issue_qty." 
                WHERE   id = ".$row_id ;

    $error_msg = "Could not update Material Requisition Detail record ";

	db_query($sql, $error_msg);

	return ;
}
//-------------------------------------------------------------------------------------------------------------


function update_mr_item_returned_qty($row_id) {
    $total_qty_returned = get_return_material_details_qty_returned($row_id);
    
    $sql = "UPDATE ".TB_PREF."material_requisition_details 
            SET returned_qty = ".db_escape($total_qty_returned)." 
            WHERE id = ".db_escape($row_id);

    $error_msg = "Could not update_mr_item_returned_qty.";

	db_query($sql, $error_msg);

	return ;
}
//-------------------------------------------------------------------------------------------------------------



function get_sql_for_approve_material_requisitions($current_user_id)
{

    $sql = "SELECT 
                mr.mr_no,
                mr.reference, 
                il.location_name issuing_location ,
                p.description,
                mr.mr_date mr_required_date,
                c.real_name,
                mr.created_on,
                mr.approval_status  
            FROM 
                ".TB_PREF."material_requisitions mr,
                ".TB_PREF."projects_master as p, 
                ".TB_PREF."locations il,
                ".TB_PREF."users c,
                ".TB_PREF."approvers as apr,
                ".TB_PREF."user_data_access_groups as udag,
                ".TB_PREF."users u   
            WHERE  mr.project_id = p.project_id AND 
                mr.issuing_location = il.loc_code AND 
                (mr.approval_status = ".AS_AWAITING_APPROVAL." OR mr.approval_status = ".AS_WITH_THE_APPROVER.") AND 
                mr.next_approval_group_id = apr.approval_group_id AND 
                mr.created_by = c.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
                mr.mr_no";            

	return $sql;
}

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

function get_material_requisition($mr_no)
{
    $sql = "SELECT 
                mr.mr_no,
                mr.reference, 
                mr.issuing_location,
                il.location_name issuing_location_name,              
                mr.mr_date mr_required_date,
                mr.comments, 
                mr.approval_status, 
                mr.mr_complete,
                mr.project_id,
                pm.description project_description,
                ap.approval_path_name approval_path_description,
                mr.next_approval_group_id,
                u.real_name requestor_name,
                mr.created_by,
                mr.created_on, 
                mr.movement_type, 
                mt.name movement_type_name, 
                mr.linked_field_type,
                mr.sample_items,
                mr.linked_field_id,
                mr.row_state,
                mr.pr_no 
            FROM ".TB_PREF."material_requisitions mr 
                INNER JOIN ".TB_PREF."projects_master pm ON 
                    mr.project_id = pm.project_id 
                INNER JOIN ".TB_PREF."locations il ON 
                    mr.issuing_location = il.loc_code                 
                INNER JOIN ".TB_PREF."users u ON 
                     mr.created_by = u.id 
                LEFT JOIN ".TB_PREF."movement_types mt ON 
                    mr.movement_type = mt.id                  
                LEFT JOIN ".TB_PREF."approval_path ap ON 
                    pm.approval_path_id = ap.approval_path_id 
                
            WHERE  
                mr.mr_no = ".  db_escape($mr_no);
    
    $result = db_query($sql,"Material requisition could not be retreived");

    return db_fetch($result);

}

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



function get_material_requisition_header_record($mr_no)
{
    $sql = "SELECT 
                mr.* 
            FROM ".TB_PREF."material_requisitions mr                 
            WHERE  
                mr.mr_no = ".  db_escape($mr_no);
    
    $result = db_query($sql,"Material requisition header could not be retreived");

    return db_fetch($result);

}

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

function get_mr_details($mr_no)
{
    $sql = "SELECT 
                mrd.id,
                mrd.mr_no,
                mrd.stock_id,
                sm.description,
                sm.units,
                mrd.required_date,
                mrd.price,
                sm.material_cost + sm.labour_cost + sm.overhead_cost AS standard_cost,
                mrd.requested_qty,
                mrd.issued_qty,
                mrd.returned_qty,
                mrd.ordered_qty,
                mrd.line_comment,
                mrd.line_complete, 
                mrd.created_by, 
                mrd.created_on,
                mrd.requested_unit,
                mrd.requested_unit_qty, 
                mrd.req_unit_conv_factor,
                sm.high_value_item,
                sm.sample_item,
                sm.reference_cost, 
                sm.stock_control_method, 
                mrd.pr_line_id,
                mrd.task_id,
                pt.description task_description,
                mrd.project_site_id,
                ps.description project_site_description 
            FROM 
                ".TB_PREF."material_requisition_details mrd 
                INNER JOIN ".TB_PREF."stock_master sm ON 
                    mrd.stock_id = sm.stock_id   
                LEFT JOIN ".TB_PREF."project_tasks pt ON
                    mrd.task_id = pt.task_id 
                LEFT JOIN ".TB_PREF."project_sites ps ON
                    mrd.project_site_id = ps.id 
            WHERE 
                mrd.mr_no = ".db_escape($mr_no)." 
            ORDER BY
                mrd.id";
    
    $result = db_query($sql,"Material requisition details could not be retreived");

    return $result;

}

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

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

    return db_fetch($result);

}


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

function get_material_transfers_for_requisition($mtr_no)
{
    $sql = "SELECT 
                mrt.*,
                u.real_name issuer_name   
            FROM 
                ".TB_PREF."material_requisition_transfers mrt,
                ".TB_PREF."users u 
            WHERE  
                mrt.created_by = u.id AND 
                mrt.source_mtr_no = ".  db_escape($mtr_no);
    
    return db_query($sql,"Material requisition transfers could not be retreived");


}

function get_requestor($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 update_mr_header_with_pr_no($mr_no, $mr_row_state, $pr_no)
{
    $sql = "UPDATE ".TB_PREF."material_requisitions 
            SET     pr_no = ".$pr_no." , 
                    row_state = ". db_new_row_state($mr_row_state)." 
            WHERE   mr_no = ".$mr_no." AND 
                    row_state = ". db_escape($mr_row_state)." AND 
                    pr_no = 0" ;

	db_query($sql, "Could not update Material Requisition with PR number");
    
    $num_rows_affected = db_num_affected_rows();
    
    if($num_rows_affected !== 1){
        cancel_transaction();
        return array("success" => false, "tran_no" => $mr_no, "message" => get_message('ANOTHER_USER_HAS_UPDATED'));
    }
    else{
        return array("success" => true, "tran_no" => $mr_no, "message" => "");
    }
}


function update_mr_detail_line($mr_line_id, $pr_line_id)
{
    $sql = "UPDATE ".TB_PREF."material_requisition_details 
                SET     pr_line_id = ".$pr_line_id." 
                WHERE   id = ".$mr_line_id." AND 
                        pr_line_id = 0" ;

	db_query($sql, "Could not update Material Requisition Detail record ");
    
    return db_num_affected_rows();
}

function get_mr_edit_info($mr_no)
{
        
    $sql = " SELECT * FROM ".TB_PREF."material_requisitions 
                WHERE mr_no = ".$mr_no ;
    
    $result = db_query($sql,"Material requisition could not be retreived");

    return db_fetch($result);

}

function calculate_mr_remaining_total($line_items)
{
        $remaining_total = 0;
        foreach ($line_items as $line_item)
        {

            $item_cost = $line_item->standard_cost;
            
            $line_remaining_total = (($line_item->tran_qty * $line_item->convertion_factor) - $line_item->issued_qty)* $item_cost;
            $remaining_total += $line_remaining_total;
        }
        
        return $remaining_total;
}

function update_mr_status($mr_no, $status, &$row_state, $current_user_id)
{
        $info['approval_status'] = $status;
        $info['tran_no'] = $mr_no;
        $info['row_state'] = $row_state;
        $info['current_user_id'] = $current_user_id;
        $info['tran_type'] = ST_MATERIAL_REQ;
        $info['change_type'] = '';

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

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

function check_all_mr_lines_issued($source_mr_no)
{
    $sql = "SELECT SUM(IF(requested_qty - issued_qty > 0, 1, 0)) outstanding_line_count  
            FROM ".TB_PREF."material_requisition_details 
            WHERE mr_no = ".db_escape($source_mr_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 add_mr_pr_detail($mr_line_id, $order_quantity, 
                    $order_price, $order_unit, 
                    $order_unit_qty, $order_unit_conversion_factor, 
                    $required_date, $pr_line_id)
{
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
        
        
	$sql = "INSERT INTO ".TB_PREF."material_requisition_pr_details( 
                    mr_line_id, order_quantity, 
                    order_price, order_unit, 
                    order_unit_qty, order_unit_conversion_factor, 
                    required_date, pr_line_id, 
                    created_by, created_on) 
            VALUES (".db_escape($mr_line_id).", ".db_escape($order_quantity) . ","
                    .db_escape($order_price) . ", ".db_escape($order_unit) . ", "
                    .db_escape($order_unit_qty) . ", ".db_escape($order_unit_conversion_factor) . ", "
                    .db_escape(date2sql($required_date)) . ", ".db_escape($pr_line_id) . ", "
                    .db_escape($created_by) . ", ".db_escape($created_on).")";

	db_query($sql,"Could not add_mr_pr_detail");
}

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

function update_mr_item_ordered_qty($row_id, $this_pr_qty)
{
//	$issued_qty = get_mr_item($row_id);
//    $new_issued_qty = $issued_qty['issued_qty'] + $this_issued_qty;

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

	$sql = "UPDATE ".TB_PREF."material_requisition_details 
                SET     ordered_qty = ordered_qty + ".$this_pr_qty." 
                WHERE   id = ".$row_id ;

    $error_msg = "Could not update_mr_item_ordered_qty";

	db_query($sql, $error_msg);

	return db_num_affected_rows();
}
//-------------------------------------------------------------------------------------------------------------

function close_mr_for_prs($mr_no)
{
    $sql = "UPDATE ".TB_PREF."material_requisitions SET 
                    closed_for_prs= 1     
            WHERE mr_no = ".db_escape($mr_no);

    db_query($sql, "Could not close_mr_for_prs");
    
}

function get_mr_details_by_from_to_dates($from_date, $to_date, $approval_status=AS_APPROVED) {
    $sql = "SELECT mr.mr_no, mr.issuing_location, mrd.*, 
                IF (sk.editable = 1, CONCAT('[', di.stock_id, '] ', di.item_description), CONCAT('[', di.stock_id, '] ', sk.description)) description, 
                sk.material_cost + sk.labour_cost + sk.overhead_cost AS standard_cost,
                un.name unit_name, 
                pt.description task_description,
                ps.description project_site_description 
                
            FROM ".TB_PREF."material_requisitions mr  
                JOIN ".TB_PREF."material_requisition_details mrd 
                    ON mr.mr_no = mrd.mr_no 
                JOIN ".TB_PREF."stock_master sk 
                    ON mrd.stock_id = sk.stock_id   
                LEFT JOIN ".TB_PREF."project_tasks pt 
                    ON mrd.task_id = pt.task_id 
                LEFT JOIN ".TB_PREF."project_sites ps 
                    ON mrd.project_site_id = ps.id 
                LEFT JOIN ".TB_PREF."item_units un  
                    ON ard.tran_unit = un.abbr  
            WHERE mr.mr_date >= ".db_escape($from_date)." 
                AND mr.mr_date <= ".db_escape($to_date)." 
                AND mr.approvel_status = ".db_escape($approval_status)." 
            ORDER BY mrd.id";
    
    return db_query($sql, "get_mr_details_by_from_to_dates");
}

?>