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

function add_stock_reservation($sr_cart, $items, 
                                    $reference, $location, $reservation_location, 
                                    $sr_date, $comments,  
                                    $project_id, $movement_type, 
                                    $linked_field_type, $linked_field_id)
{
	global $loc_notification, $path_to_root, $Refs;
        
    $tran_type = ST_STOCK_RESERVATION;
    
    $created_by = $_SESSION['wa_current_user']->user;

	begin_transaction();
	$args = func_get_args();
	$args = (object)array_combine(array('sr_cart', 'items', 
                                    'reference', '$location', 'reservation_location',
                                    'sr_date', 'comments',  
                                    'project_id', 'movement_type', 
                                    'linked_field_type', 'linked_field_id'), $args);
        
	$args->trans_no = 0;
	hook_db_prewrite($args, $tran_type);

    if($reference == '' || $reference == null ){
        $sr_auto_ref = get_next_trans_no($tran_type);
        $reference = $sr_auto_ref;
    }
        
    $sr_no = add_stock_reservation_header($location, $reservation_location, $project_id, $reference, 
                                    $sr_date, $movement_type, $linked_field_type, $linked_field_id, 
                                    $comments, $created_by);

    if($sr_no > 0){
        foreach ($items as $line_item){
            add_stock_reservation_item($sr_no, $line_item->stock_id, $line_item->required_date, $line_item->standard_cost, 
                        $line_item->quantity, $line_item->tran_unit, $line_item->tran_qty, $line_item->convertion_factor, 
                        0,  $line_item->line_comment,$line_item->project_site_id, 0, 
                        $created_by);

            add_stock_transfer_item($sr_no, $line_item->stock_id, $location,
                        $reservation_location, $sr_date, $reference, $line_item->quantity, $line_item->tran_unit,
                        $line_item->tran_qty, $line_item->convertion_factor, $line_item->project_site_id, $tran_type);
                
//                $ailqdm_msg1 =  add_item_line_quantity_detail_movements(ST_LOCTRANSFER, $sr_no, $location_from, 
//                                                                            $sr_date, $line_item, false, '');
//                if($ailqdm_msg1['success'] !== true)
//                {
//                    cancel_transaction();
//                    return array("success" => $ailqdm_msg1['success'], "tran_no" => $sr_no, "message" => $ailqdm_msg1['message']);
//                }
//                else{
//                    $ailqdm_msg2 =  add_item_line_quantity_detail_movements(ST_LOCTRANSFER, $sr_no, $location_to, 
//                                                                            $sr_date, $line_item, true, '');
//                    if($ailqdm_msg2['success'] !== true)
//                    {
//                        cancel_transaction();
//                        return array("success" => $ailqdm_msg2['success'], "tran_no" => $sr_no, "message" => $ailqdm_msg2['message']);
//                    }
//                }
        }

        $Refs->save($tran_type, $sr_no, $reference);
        add_audit_trail($tran_type, $sr_no, $sr_date);

        $args->trans_no = $sr_no;
    }
    else{
        cancel_transaction();
        return array("success" => false, "tran_no" => $sr_no, "message" => "Could not create the Stock Reservation");
    }

	hook_db_postwrite($args, $tran_type);
	commit_transaction();
        
    $text = "Material Transfer Requisition $sr_no Created.";
    return array("success" => true, "tran_no" => $sr_no, "message" => $text);

}

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


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

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

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

	return $result;
}


function add_stock_reservation_header($location, $reservation_location, $project_id, $reference, 
                                    $tran_date, $movement_type, $linked_field_type, $linked_field_id, 
                                    $comments, $created_by)
{
   	$tran_date = date2sql($tran_date);
    $created_on = date('Y-m-d H:i:s');

	$sql = "INSERT INTO ".TB_PREF."stock_reservations( 
                        location, reservation_location,
                        project_id, reference, 
                        sr_date, movement_type, 
                        linked_field_type, linked_field_id, 
                        comments, 
                        created_by, created_on) 
            VALUES (".db_escape($location).", ".db_escape($reservation_location).", "
                        .db_escape($project_id).", ".db_escape($reference).", "
                        .db_escape($tran_date).", ".db_escape($movement_type).", "
                        .db_escape($linked_field_type).", ".db_escape($linked_field_id).", "
                        .db_escape($comments).", "
                        .db_escape($created_by).", ".db_escape($created_on).")";


		$error_msg = "Could not add_stock_reservation_header";

	db_query($sql, $error_msg);

	return db_insert_id();
}


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


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



function add_stock_reservation_item($sr_no, $stock_id, 
                                $releasing_date, $price, 
                                $reserved_qty, $reserved_unit, 
                                $reserved_unit_qty, $reserved_unit_conv_factor, 
                                $released_qty, $line_comment, 
                                $project_site_id, $line_complete, 
                                $created_by)
{


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

	$sql = "INSERT INTO ".TB_PREF."stock_reservation_details(
                        sr_no, stock_id, 
                        releasing_date, price, 
                        reserved_qty, reserved_unit, 
                        reserved_unit_qty, reserved_unit_conv_factor, 
                        released_qty, line_comment, 
                        project_site_id, line_complete, 
                        created_by, created_on) 
            VALUES (".db_escape($sr_no).", ".db_escape($stock_id).", " 
                        .db_escape($releasing_date).", ".db_escape($price).", "
                        .db_escape($reserved_qty).", ".db_escape($reserved_unit).", "
                        .db_escape($reserved_unit_qty).", ".db_escape($reserved_unit_conv_factor).", "
                        .db_escape($released_qty).", ".db_escape($line_comment).", "
                        .db_escape($project_site_id).", ".db_escape($line_complete).", "
                        .db_escape($created_by).", ".db_escape($created_on).")";

    $error_msg = "Could not add_stock_reservation_item";

	db_query($sql, $error_msg);

	return db_insert_id();
}



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

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


function close_stock_reservation($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_stock_reservation_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_stock_reservation_item($row_id, $this_release_qty)
{
	$sql = "UPDATE ".TB_PREF."stock_reservation_details  
                SET  released_qty = released_qty + ".  db_escape($this_release_qty)."  
                WHERE id = ".$row_id ;

	$error_msg = "Could not update_stock_reservation_item. ";

	db_query($sql, $error_msg);

	return ;
}

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


function get_sql_for_stock_reservation_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=null)
{
        $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_stock_reservation_inquiry($sr_from = null, $sr_to = null, 
            $created_on_from = null, $created_on_to = null,  
            $location = "", 
            $sr_no = 0,  $sr_reference = "", $sr_complete = null, 
            $stock_item = null)
{
        $filter = "";
        
        if($sr_from != null)
        {
            $filter = " AND sr.sr_date > ".db_escape(date2sql($sr_from));
        }
        
        if($sr_to != null)
        {
                $filter .= " AND sr.sr_date < ".db_escape(date2sql($sr_to));
        }
        
        if($created_on_from != null)
        {
                $filter .= " AND sr.created_on > ".db_escape(date2sql($created_on_from));
        }
        
        if($created_on_to != null)
        {
                $filter .= " AND sr.created_on < ".db_escape(date2sql($created_on_to));
        }
        
        if($location != "")
        {
                $filter .= " AND sr.location = ".db_escape($location);  
        }
        
        if ($sr_no > 0)
        {
                $filter .= " AND sr.id = ".db_escape($sr_no);  
        }
        
        if ($sr_reference != "")
        {
                $filter .= " AND sr.reference = ".db_escape($sr_reference); 
        }
        

        $filter .= " AND sr.sr_complete = ".db_escape($sr_complete);         

        
        //$filter = "";
        
        if ($stock_item == null)
        {
            $sql = "SELECT 
                        sr.id, 
                        sr.reference, 
                        l.location_name, 
                        p.description, 
                        sr.sr_date, 
                        u.real_name originator, 
                        sr.created_on,
                        sr.sr_complete 
                    FROM 
                        ".TB_PREF."stock_reservations sr,
                        ".TB_PREF."projects_master as p, 
                        ".TB_PREF."locations l,
                        ".TB_PREF."users u 
                    WHERE  sr.project_id = p.project_id AND 
                        sr.location = l.loc_code AND 
                        sr.created_by = u.id "
                        .$filter." 
                    ORDER BY
                        sr.id";
        }
        else
        {
            $filter .= " AND srd.stock_id = ".db_escape($stock_item);         
        
            $sql = "SELECT DISTINCT
                        sr.id, 
                        sr.reference, 
                        l.location_name, 
                        p.description, 
                        sr.sr_date, 
                        u.real_name originator, 
                        sr.created_on,
                        sr.sr_complete 
                    FROM 
                        ".TB_PREF."stock_reservations sr,
                        ".TB_PREF."projects_master as p, 
                        ".TB_PREF."locations l,
                        ".TB_PREF."users u,
                        ".TB_PREF."stock_reservation_details srd 
                    WHERE 
                        sr.project_id = p.project_id AND 
                        sr.location = l.loc_code AND 
                        sr.created_by = u.id AND 
                        sr.id = srd.sr_no "
                        .$filter."   
                    ORDER BY
                        sr.id";
        }

	return $sql;
}


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


function get_sql_for_aprove_stock_reservation_inquiry($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_id=null)
{
        $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,
                        p.description,
                        mtr.mtr_date mtr_required_date,
                        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."approvers as apr  
                    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 
                        apr.user_id = ".$current_user_id." "
                        .$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,
                        approval_status 
                    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_stock_reservation($sr_no)
{
    $sql = "SELECT 
                sr.id,
                sr.reference, 
                sr.location,
                sr.project_id, 
                pm.description project_description,
                sr.reservation_location,
                sr.sr_date,
                sr.comments, 
                sr.sr_complete,
                u.real_name requestor_name,
                sr.created_by,
                sr.created_on, 
                sr.movement_type, 
                mt.name movement_type_name, 
                sr.linked_field_type,
                sr.linked_field_id,
                sr.row_state,
                l.location_name 
            FROM ".TB_PREF."stock_reservations sr 
                INNER JOIN ".TB_PREF."projects_master pm ON 
                        sr.project_id = pm.project_id 
                INNER JOIN ".TB_PREF."users u ON   
                        sr.created_by = u.id 
                INNER JOIN ".TB_PREF."locations l ON 
                        sr.location = l.loc_code 
                LEFT JOIN ".TB_PREF."movement_types mt ON 
                        sr.movement_type = mt.id 
            WHERE  
                sr.id = ".  db_escape($sr_no);
    
    $result = db_query($sql,"Stock reservation could not be retreived");

    return db_fetch($result);

}

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

function get_stock_reservation_details($sr_no)
{
    $sql = "SELECT 
                srd.id,
                srd.sr_no,
                srd.stock_id,
                sm.description,
                sm.units,
                srd.releasing_date,
                srd.reserved_qty,
                srd.released_qty,
                srd.line_comment,
                srd.line_complete, 
                srd.created_by, 
                srd.created_on,
                srd.reserved_unit,
                srd.reserved_unit_qty, 
                srd.reserved_unit_conv_factor,
                sm.stock_control_method,
                srd.project_site_id,
                ps.description project_site_description 
            FROM 
                ".TB_PREF."stock_reservation_details srd 
                        INNER JOIN ".TB_PREF."stock_master sm ON 
                                srd.stock_id = sm.stock_id 
                        LEFT JOIN ".TB_PREF."project_sites ps ON
                                srd.project_site_id = ps.id 
            WHERE 
                
                srd.sr_no = ".$sr_no." 
            ORDER BY
                srd.id";
    
    $result = db_query($sql,"Stock reservation details could not be retreived");

    return $result;

}

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

function get_stock_reservation_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_released($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_stock_releases_for_stock_reservation($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 check_all_sr_lines_released($source_sr_no)
{
    $sql = "SELECT SUM(IF(reserved_qty - released_qty > 0, 1, 0)) outstanding_line_count  
            FROM ".TB_PREF."stock_reservation_details 
            WHERE sr_no = ".db_escape($source_sr_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_stock_reservation_status($source_sr_no, $sr_row_state, $close = 0)
{
        $sql = "UPDATE ".TB_PREF."stock_reservations 
                SET  sr_complete = ".db_escape($close).",
                    row_state = ". db_new_row_state($sr_row_state)."  
                WHERE id = ".$source_sr_no ;
        
        db_query($sql,"Could not update_stock_reservation_status");
        
        $num_rows = db_num_affected_rows();
        if($num_rows == 1){
            return array("success" => true, "message" => " SR $source_sr_no has been updated.");
        }
        else{
            return array("success" => false, "message" => "Another user has updated SR $source_sr_no.");
        }
    
}
//-------------------------------------------------------------------------------------------------------------

?>