<?php

/* * ********************************************************************
 * ********************************************************************* */

//-------------------------------------------------------------------------------------------------------------
include_once($path_to_root . "/includes/common_functions.inc");

function add_material_requisition_transfer($items, $reference, $issuing_location, $receiving_location, $project_id, $memo_, $source_mtr_no, 
                                           $mtr_row_state, $tran_date) {
    
    global $loc_notification, $path_to_root, $Refs;
    $mtr_no = $_SESSION['mrt_items']->order_id;
    
    $type = ST_REQTRANSFER;
    $date = sql2date(date('Y/m/d'));

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

    begin_transaction();
    $args = func_get_args();
    $args = (object) array_combine(array('items', 'reference', 'issuing_location', 'receiving_location', 
                                        'project_id', '$memo_', 'source_mtr_no', '$mtr_row_state', 'tran_date'), $args);
    $args->trans_no = 0;
    hook_db_prewrite($args, $type);

    if ($reference == '' || $reference == null) {
        $reference = get_next_trans_no($type);
    }

    //$mrt_no = get_next_trans_no($type);
    $umrs_msg = update_mtr_status($mtr_no, AS_TRANSFERRED, $mtr_row_state, $created_by);
    if($umrs_msg['success'] !== true)
    {
        cancel_transaction();
        return array("passed" => false, "tran_no" => $mtr_no, "message" => $umrs_msg['message']);
    }
    
    
    $mrt_no = add_material_requisition_transfer_header($type, $reference, $issuing_location, $receiving_location, $project_id, $memo_, $source_mtr_no, 
                                                       $created_by, $tran_date);

    if (!$mrt_no > 0) {
        cancel_transaction();
        return array("passed" => false, "message" => "Could not add material requisition transfer");
    }

    if ($loc_notification == 1) {
        include_once($path_to_root . "/inventory/includes/inventory_db.inc");
        $st_ids = array();
        $st_names = array();
        $st_num = array();
        $st_reorder = array();
    }

    foreach ($items as $line_item) {
        if (isset($_POST[$line_item->row_id."_check"]) && $_POST[$line_item->row_id."_check"] == 1) {
        
            if ($loc_notification == 1) {
                $loc = calculate_reorder_level($location_from, $line_item, $st_ids, $st_names, $st_num, $st_reorder);
            }
            if ($line_item->tran_qty > 0) {
                
                if ($line_item->task_id == '') {
                    $line_item->task_id = 0;
                }
                
                $standard_cost = get_standard_cost($line_item->stock_id);
                
                $qty = $line_item->tran_qty * $line_item->convertion_factor;
                add_material_requisition_transfer_details(ST_REQTRANSFER, $line_item->stock_id, $mrt_no, $issuing_location, $date, $reference, $qty, 
                                                          $standard_cost, 0, 0, $line_item->tran_unit, $line_item->tran_qty, $line_item->convertion_factor, 
                                                          $line_item->task_id, $line_item->project_site_id, $line_item->row_id);
                update_mtr_item_issued_qty($line_item->row_id);
            }
        }
    }

    $Refs->save(ST_REQTRANSFER, $mrt_no, $reference);
    add_audit_trail(ST_REQTRANSFER, $mrt_no, $date);

    if ($loc_notification == 1 && count($st_ids) > 0) {
        send_reorder_email($loc, $st_ids, $st_names, $st_num, $st_reorder);
    }

    $args->trans_no = $mrt_no;
    hook_db_postwrite($args, $type);

        $urs_msg = update_mtr_row_state($mtr_no, $mtr_row_state);
        if($urs_msg['success'] !== true) {
            cancel_transaction();
            return array("passed" => false, "tran_no" => $mtr_no, "message" => $urs_msg['message']);
        }

    return array("success" => true, "tran_no" => $mrt_no, "message" => "");
}

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

function add_material_requisition_transfer_header($type, $reference, $issuing_location, $receiving_location, $project_id, $memo_, $source_mtr_no, 
                                                  $created_by, $tran_date_) {
    //$mr_date = date2sql($date_);
    $created_on = date('Y-m-d H:i:s');
    $tran_date = date2sql($tran_date_);

    $sql = "INSERT INTO " . TB_PREF . "material_requisition_transfers(type, reference,"
            . " issuing_location, receiving_location,"
            . " project_id, comments,"
            . " source_mtr_no, tran_date, "
            . " created_by, created_on) "
            . "VALUES (" . db_escape($type) . ", " . db_escape($reference) . ", "
            . db_escape($issuing_location) . ", " . db_escape($receiving_location) . ", "
            . db_escape($project_id) . ", " . db_escape($memo_) . ", "
            . db_escape($source_mtr_no) . ", ". db_escape($tran_date). ", "
            . db_escape($created_by) . ", " . db_escape($created_on) . ")";


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

    db_query($sql, $error_msg);

    return db_insert_id();
}

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

//function get_material_issue_header($mrt_no) {
//    $sql = "SELECT mrt.*, 
//                    rl.location_name receiving_location_name,
//                    il.location_name issuing_location_name,
//                    u.real_name created_user_name 
//            FROM " . TB_PREF . "material_requisition_transfers mrt,
//                    " . TB_PREF . "locations rl,
//                    " . TB_PREF . "locations il,
//                    " . TB_PREF . "users u 
//            WHERE mrt.issuing_location = il.loc_code AND 
//                    mrt.receiving_location = rl.loc_code AND 
//                    mrt.created_by = u.id AND  
//                    mrt_no = '" . $mrt_no . "'";
//
//    $result = db_query($sql);
//    return db_fetch($result);
//}

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

//function get_material_issue_details($mrt_no, $type) {
//    $sql = "SELECT  smoves.stock_id ,
//                    smoves.qty ,
//                    smaster.description , 
//                    smoves.tran_unit , 
//                    smoves.standard_cost 
//            FROM    " . TB_PREF . "stock_moves smoves,
//                    " . TB_PREF . "stock_master smaster  
//            WHERE   smoves.stock_id = smaster.stock_id AND 
//                    smoves.trans_no = '" . $mrt_no . "' AND type = '" . $type . "'";
//
//    $result = db_query($sql);
//
//    return $result;
//}

function get_locations($loc_code) {
    $sql = "SELECT  location_name  
            FROM    " . TB_PREF . "locations 
            WHERE   loc_code = '" . $loc_code . "'";

    $result = db_query($sql);
    return db_fetch($result);
}

function get_mrt_total_issued_qty_by_ref_line_id($ref_line_id) {
    $sql = "SELECT SUM(qty) total_issued_qty 
            FROM ".TB_PREF."material_requisition_transfer_details  
            WHERE ref_line_id = ".db_escape($ref_line_id);

    $result = db_query($sql, 'get_mrt_total_issued_qty_by_ref_line_id');
    return db_result_value($result, 'total_issued_qty');
}

function add_material_requisition_transfer_details($type, $stock_id, $trans_no, $location, $date_, $reference, $quantity, $std_cost, $person_id=0, 
                                                   $price=0, $tran_unit="", $tran_qty=0, $convertion_factor=1, $task_id=0, $project_site_id=0, 
                                                   $reference_line_id=0) {
    $date = date2sql($date_);

	$sql = "INSERT INTO ".TB_PREF."material_requisition_transfer_details (stock_id, trans_no, 
                    type, loc_code,
                    tran_date, person_id, 
                    reference, qty, 
                    standard_cost, price,  
                    tran_unit, tran_qty, 
                    convertion_factor, task_id,
                    project_site_id, ref_line_id) 
            VALUES (".db_escape($stock_id).", ".db_escape($trans_no).", "
                    .db_escape($type).", ".db_escape($location).", "
                    .db_escape($date).", ".db_escape($person_id).", "
                    .db_escape($reference).", ".db_escape($quantity).", "
                    .db_escape($std_cost).", ".db_escape($price).", "
                    .db_escape($tran_unit).", ".db_escape($tran_qty).","
                    .db_escape($convertion_factor).", ".db_escape($task_id).", "
                    .db_escape($project_site_id).", ".db_escape($reference_line_id).")";

	db_query($sql, 'add_material_requisition_transfer_details');
	return db_insert_id();
}

function get_sql_for_mrt_inquiry($type=0, $from_date=null, $to_date=null, $receiving_location="", $issuing_location="", $mrt_no=0,  
                                 $mrt_reference="", $filtering_status=-1) {
    $filter = "";
        
        if ($type != 0) {
            $filter .= " AND mrt.type = ".db_escape($type);
        }
        
        if ($from_date != null) {
            $filter .= " AND mrt.created_on >= ".db_escape(date2sql($from_date));
        }
        
        if ($to_date != null) {
            $filter .= " AND mrt.created_on <= ".db_escape(date2sql($to_date).' 23:59:59');
        }
        
        if ($receiving_location != "") {
            $filter .= " AND mrt.receiving_location = ".db_escape($receiving_location);  
        }
        
        if ($issuing_location != "") {
            $filter .= " AND mrt.issuing_location = ".db_escape($issuing_location);            
        }
        
        if ($mrt_no > 0) {
            $filter .= " AND mrt.mrt_no = ".$mrt_no;
        }
        
        if ($mrt_reference != "") {
            $filter .= " AND mrt.reference = ".db_escape($mrt_reference); 
        }
        
        if ($filtering_status > -1)
        {
                $filter .= " AND mrt.approval_status = ".db_escape($filtering_status); 
        }
        
        
            $sql = "SELECT mrt.mrt_no,
                        mrt.reference, 
                        rl.location_name receiving_location,
                        pm.description issuing_project_name,   
                        il.location_name issuing_location,
                        mrt.created_on,
                        mrt.comments, 
                        mrt.type,
                        mrt.approval_status 
                    FROM ".TB_PREF."material_requisition_transfers mrt 
                        LEFT JOIN ".TB_PREF."projects_master pm 
                            ON mrt.project_id = pm.project_id 
                        JOIN ".TB_PREF."locations il 
                            ON mrt.issuing_location = il.loc_code 
                        JOIN ".TB_PREF."locations rl 
                            ON mrt.receiving_location = rl.loc_code 
                    WHERE 1 = 1 $filter   
                    ORDER BY mrt.mrt_no";

	return $sql;
}

function get_material_requisition_transfer($mrt_no) {
    $sql = "SELECT  
                mrt.mrt_no,
                mrt.reference, 
                mrt.issuing_location,
                mrt.receiving_location,
                mrt.project_id, 
                pm.description project_description,
                mrt.comments, 
                u.real_name requestor_name,
                mrt.created_by,
                mrt.created_on, 
                mrt.tran_date, 
                mrt.row_state,
                mrt.source_mtr_no,
                rl.location_name receiving_location_name,
                il.location_name issuing_location_name,  
                mrt.approval_status, 
                mrt.source_mtr_no  
                
            FROM ".TB_PREF."material_requisition_transfers mrt 
                LEFT JOIN ".TB_PREF."projects_master pm 
                    ON mrt.project_id = pm.project_id 
                INNER JOIN ".TB_PREF."users u 
                    ON mrt.created_by = u.id 
                INNER JOIN ".TB_PREF."locations il 
                    ON mrt.issuing_location = il.loc_code 
                INNER JOIN ".TB_PREF."locations rl 
                    ON mrt.receiving_location = rl.loc_code 
            WHERE mrt.mrt_no = ".db_escape($mrt_no);
    
    $result = db_query($sql, "get_material_requisition_transfer");
    return db_fetch($result);
}

function get_mrt_details($mrt_no) {
    $sql = "SELECT mrtd.trans_id id,
                mrtd.trans_no mrt_no,
                mrtd.stock_id,
                mrtd.tran_date,
                mrtd.tran_unit,
                mrtd.tran_qty,
                mrtd.convertion_factor,
                mrtd.qty,
                mrtd.issued_qty,
                mrtd.task_id,
                mrtd.project_site_id,
                mrtd.ref_line_id,
                mrtd.price,
                sm.description,
                sm.units,
                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,
                pt.description task_description,
                ps.description project_site_description 
                
            FROM ".TB_PREF."material_requisition_transfer_details mrtd 
                INNER JOIN ".TB_PREF."stock_master sm 
                    ON mrtd.stock_id = sm.stock_id 
                LEFT JOIN ".TB_PREF."project_sites ps 
                    ON mrtd.project_site_id = ps.id 
                LEFT JOIN ".TB_PREF."project_tasks pt 
                    ON mrtd.task_id = pt.task_id 
            WHERE mrtd.trans_no = ".db_escape($mrt_no)." 
            ORDER BY mrtd.trans_id";
    
        return db_query($sql, "get_mrt_details");
}

function update_mrt_item_issued_qty($row_id) {
    $total_issued_qty = get_mt_receipt_total_received_qty_by_ref_line_id($row_id);
    
	$sql = "UPDATE ".TB_PREF."material_requisition_transfer_details  
                SET issued_qty = ".db_escape($total_issued_qty)."  
                WHERE trans_id = ".db_escape($row_id);

	db_query($sql, 'update_mrt_item_issued_qty');
}

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

function get_mrt_header_details($mrt_no) {
    $sql = "SELECT mrt.*, 
                    rl.location_name receiving_location_name,
                    il.location_name issuing_location_name,
                    u.real_name created_user_name 
            FROM " . TB_PREF . "material_requisition_transfers mrt,
                    " . TB_PREF . "locations rl,
                    " . TB_PREF . "locations il,
                    " . TB_PREF . "users u 
            WHERE mrt.issuing_location = il.loc_code AND 
                    mrt.receiving_location = rl.loc_code AND 
                    mrt.created_by = u.id AND  
                    mrt_no = '" . $mrt_no . "'";

    $result = db_query($sql, 'get_mrt_header_details');
    return db_fetch($result);
}

function get_mrt_header_record($mrt_no) {
    $sql = "SELECT * 
            FROM ".TB_PREF."material_requisition_transfers  
            WHERE mrt_no = ".db_escape($mrt_no);

    $result = db_query($sql, 'get_mrt_header_record');
    return db_fetch($result);
}

//function calculate_mrt_task_remaining_totals($mrt_no) {
//    $mrt_details = get_mrt_details($mrt_no);
//        while ($myrow = db_fetch($mrt_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'];
//
//            $mrt_details_array[] = $a;
//        }
//    
//    $mrt_tasks_summary = calculate_task_remaining_totals($mrt_details_array);
//    
//    return $mrt_tasks_summary;
//}

?>