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

function save_stock_asset_transfer_detail($type, $asset_id, $asset_quantity, $stock_id, $tran_unit, $stock_item_tran_qty, $stock_item_base_unit_qty, 
                                          $stock_item_base_unit_cost, $stock_project_id, $stock_task_id, $asset_value, $po_number, $reference, 
                                          $tran_date_, $comment, $return_id, $conversion_factor, $stock_location, $asset_location, $asset_project_id, 
                                          $asset_task_id, $site_id, $asset_department_id, $trans_no, $grn_line_no, $purchased_base_unit_cost, $mb_flag,
                                          $asset_new_current_value) {    
    
    $stock_project = get_project_record($stock_project_id);
    
    $stk_ast_tran_id = add_stock_asset_transfer_detail($asset_id, $asset_quantity, $stock_id, $tran_unit, $stock_item_tran_qty, $stock_item_base_unit_qty, 
                                    $stock_item_base_unit_cost, $stock_project_id, $stock_task_id, $asset_value, $po_number, $reference, $tran_date_, 
                                    $comment, $return_id, $conversion_factor, $stock_location, $asset_location, $asset_project_id, $asset_task_id, 
                                    $site_id, $asset_department_id, $stock_project['purchase_to_stock'], $trans_no, $grn_line_no, $purchased_base_unit_cost);
    
    if($mb_flag == "B" || $mb_flag == "M"){
//        $standard_cost = update_average_material_cost(null, $stock_id, $purchased_base_unit_cost, -abs($stock_item_base_unit_qty), $tran_date_);
        
        add_stock_adjustment_item($trans_no, $stock_id, $stock_location, $tran_date_, $reference, -abs($stock_item_base_unit_qty), $purchased_base_unit_cost, 
                                  $comment, $tran_unit, -abs($stock_item_tran_qty), $conversion_factor, ST_STOCK_ASSET_TRANSFER, $stock_task_id, $site_id,
                                  $stk_ast_tran_id, $stock_project_id);
    }

        add_asset_move($type, $trans_no, $trans_no, $reference, $tran_date_, $asset_id, $asset_location, $site_id, 
                       $asset_task_id, abs($asset_quantity), $asset_project_id);   
        
        
            update_asset_current_value($asset_id, $asset_new_current_value, 0);
            $tran_date = date2sql($tran_date_);
            
            $existing_asset_total = get_asset_total_qty($asset_id);

            add_asset_valuation($type, $trans_no, $tran_date, $asset_id, $asset_new_current_value, $existing_asset_total, $stk_ast_tran_id);
    
    return array("success" => true, "tran_no" => $trans_no, "message" => "");
}

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

function add_stock_asset_transfer_detail($asset_id, $asset_quantity, $stock_id, $tran_unit, $stock_item_tran_qty, $stock_item_quantity, $stock_item_cost,
                                         $stock_project_id, $stock_task_id, $asset_value, $po_number, $reference, $tran_date_, $comment, $return_id, 
                                         $conversion_factor, $stock_location, $asset_location, $asset_project_id, $asset_task_id, $site_id, 
                                         $asset_department_id, $stock_project_purchase_to_stock, $trans_no, $grn_line_no, $standard_cost) {
    
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');

    $sql = "INSERT INTO ".TB_PREF."stock_asset_transfers (
                                    asset_id, asset_quantity, 
                                    stock_id, tran_unit, 
                                    tran_qty, stock_item_quantity, stock_item_cost,
                                    stock_project_id, stock_task_id,
                                    value, po_number, reference,
                                    tran_date, comment,
                                    is_return, conversion_factor,
                                    stock_location, asset_location,
                                    asset_project_id, asset_task_id, site_id,
                                    stock_project_purchase_to_stock, asset_department_id,
                                    trans_no, grn_line_no, standard_cost, 
                                    created_by, created_on  )                                     
                                 
            VALUES (".db_escape($asset_id).",  ".db_escape($asset_quantity).", "
                    .db_escape($stock_id).",  ".db_escape($tran_unit).", "
                    .db_escape($stock_item_tran_qty).",  ".db_escape($stock_item_quantity).", ".db_escape($stock_item_cost).", "
                    .db_escape($stock_project_id).", ".db_escape($stock_task_id).", "
                    .db_escape($asset_value).", ".db_escape($po_number).", "
                    .db_escape($reference).", "
                    .db_escape(date2sql($tran_date_)).", ".db_escape($comment).", "
                    .db_escape($return_id).", ".db_escape($conversion_factor).", "
                    .db_escape($stock_location).",  ".db_escape($asset_location).",  "
                    .db_escape($asset_project_id).", ".db_escape($asset_task_id).", ".db_escape($site_id).", "
                    .db_escape($stock_project_purchase_to_stock).",  ".db_escape($asset_department_id).",  "
                    .db_escape($trans_no).", ".db_escape($grn_line_no).", ".db_escape($standard_cost).", "
                    .db_escape($created_by).", ".db_escape($created_on).")";

    db_query($sql, "add_stock_asset_transfer_detail");
    
    $id = db_insert_id();
    return $id;
}

function get_grn_and_po_headers_details_by_grn_no($grn_no) {
    $sql = "SELECT grn.*, 
                gsup.supp_name,  
                gsup.curr_code, 
                gloc.location_name, 
                pm.description project_description,
                po.delivery_address,  
                po.project_id,   
                po.comments     
            FROM ".TB_PREF."grn_batch grn 
                JOIN ".TB_PREF."purch_orders po 
                    ON grn.purch_order_no = po.order_no 
                LEFT JOIN ".TB_PREF."suppliers gsup   
                    ON grn.supplier_id = gsup.supplier_id  
                LEFT JOIN ".TB_PREF."locations gloc 
                    ON grn.loc_code = gloc.loc_code   
                LEFT JOIN ".TB_PREF."projects_master pm 
                    ON po.project_id = pm.project_id    
            WHERE grn.id = ".db_escape($grn_no);
    
        $result = db_query($sql, 'get_grn_and_po_headers_details_by_grn_no');
        return db_fetch($result);
}

function get_grn_and_po_lines_details_by_grn_no($grn_no) {
    $sql = "SELECT grn.*, 
                    git.*, 
                    git.id grn_line_no, 
                    pod.po_detail_item po_line_no,
                    pod.unit_price,
                    pod.act_price,
                    pod.quantity_ordered,
                    pod.std_cost_unit, 
                    pod.order_unit po_order_unit,
                    pod.order_unit_qty po_order_unit_qty,
                    pod.order_unit_conv_factor po_order_unit_conv_factor,
                    pod.order_unit_price po_order_unit_price,
                    pod.order_unit_price_discount po_order_unit_price_discount,
                    sm.units,
                    po.project_id, 
                    po.task_id,
                    iu.name tran_unit_name,
                    pm.description po_project_name,
                    git.description grn_item_description, sm.description sm_item_description, sm.editable,
                    (sm.material_cost + sm.labour_cost + sm.overhead_cost) standard_cost, sm.mb_flag  
            FROM ".TB_PREF."grn_batch grn 
                JOIN ".TB_PREF."grn_items git 
                    ON grn.id = git.grn_batch_id 
                JOIN ".TB_PREF."purch_order_details pod 
                    ON git.po_detail_item = pod.po_detail_item  
                JOIN ".TB_PREF."purch_orders po  
                    ON pod.order_no = po.order_no   
                JOIN ".TB_PREF."stock_master sm 
                    ON git.item_code = sm.stock_id   
                JOIN ".TB_PREF."item_units iu  
                    ON git.tran_unit = iu.abbr 
                JOIN ".TB_PREF."projects_master pm   
                    ON po.project_id = pm.project_id   
    
            WHERE grn.id = ".db_escape($grn_no)." 
            ORDER BY git.id";
    
        return db_query($sql, 'get_grn_and_po_lines_details_by_grn_no');
}

function get_pr_line_details_for_po_line($po_line_no) {
    $sql = "SELECT prd.quantity pr_qty,
                prd.issued_qty pr_issued_qty,
                prd.returned_qty pr_returned_qty,
                prd.quantity,
                prd.task_id pr_task_id,
                pt.description task_name,
                prd.id pr_line_no,
                prd.requisition_id pr_no  
            FROM ".TB_PREF."purch_order_details pod 
                JOIN ".TB_PREF."pr_po_details pr_po 
                    ON pod.po_detail_item = pr_po.po_line_id 
                JOIN ".TB_PREF."purchase_requisition_details prd 
                    ON pr_po.pr_line_id = prd.id 
                JOIN ".TB_PREF."project_tasks pt    
                    ON prd.task_id = pt.task_id  
            WHERE pod.po_detail_item = ".db_escape($po_line_no);
    
        $result = db_query($sql, 'get_pr_line_details_for_po_line');
        return db_fetch($result);
}

function insert_stock_asset_transfer_header($GRN_No, $type, $reference, $tran_date, $comments) {
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
        $sql = "INSERT INTO ".TB_PREF."stock_asset_transfer_header 
                      ( grn_batch_no, type, reference, tran_date, 
                        comment, created_by, created_on )         
                VALUES( ".db_escape($GRN_No).", ".db_escape($type).", ".db_escape($reference).", ".db_escape($tran_date).", 
                        ".db_escape($comments).", ".db_escape($created_by).", ".db_escape($created_on).")";

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

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

function get_stock_asset_transfer_details($trans_no) {
    $sql = "SELECT ami.*,
                    am.asset_id, 
                    am.asset_code,
                    am.description asset_description,
                    apm.description asset_project_description,
                    apt.description asset_task_description,
                    sm.description stock_description,
                    spm.description stock_project_description,
                    spt.description stock_task_description,
                    au.name tran_unit_name,
                    sklo.location_name stock_location_name,
                    alo.location_name asset_location_name 
            FROM ".TB_PREF."stock_asset_transfers ami
                JOIN ".TB_PREF."asset_master am ON
                    ami.asset_id = am.asset_id
                LEFT JOIN ".TB_PREF."projects_master apm ON
                    ami.asset_project_id = apm.project_id
                LEFT JOIN ".TB_PREF."project_tasks apt ON
                    ami.asset_task_id = apt.task_id
                LEFT JOIN ".TB_PREF."stock_master sm ON 
                    ami.stock_id = sm.stock_id
                LEFT JOIN ".TB_PREF."projects_master spm ON
                    ami.stock_project_id = spm.project_id
                LEFT JOIN ".TB_PREF."project_tasks spt ON
                    ami.stock_task_id = spt.task_id
                LEFT JOIN ".TB_PREF."item_units au ON
                    ami.tran_unit = au.abbr
                LEFT JOIN ".TB_PREF."asset_locations alo 
                    ON ami.asset_location = alo.loc_code 
                LEFT JOIN ".TB_PREF."locations sklo 
                    ON ami.stock_location = sklo.loc_code 
            WHERE ami.trans_no = ".db_escape($trans_no);
    
    return db_query($sql, 'get_stock_asset_transfer_details');
}

function get_grn_and_stock_asset_transfer_lines_details_by_stock_asset_trans($stock_asset_no) {
    $sql = "SELECT ami.*, am.asset_id, am.asset_code, am.description asset_description, apm.description asset_project_description,
                apt.description asset_task_description, spm.description stock_project_description, spt.description stock_task_description, 
                au.name tran_unit_name, sklo.location_name stock_location_name, alo.location_name asset_location_name, git.tran_qty grn_tran_qty, 
                pod.po_detail_item, pod.order_unit_price po_order_unit_price, pod.order_unit_price_discount po_order_unit_price_discount,
                git.description grn_item_description, sm.description sm_item_description, sm.editable   
            FROM ".TB_PREF."stock_asset_transfers ami
                JOIN ".TB_PREF."asset_master am 
                    ON ami.asset_id = am.asset_id
                JOIN ".TB_PREF."projects_master apm 
                    ON ami.asset_project_id = apm.project_id
                JOIN ".TB_PREF."project_tasks apt 
                    ON ami.asset_task_id = apt.task_id
                JOIN ".TB_PREF."projects_master spm 
                    ON ami.stock_project_id = spm.project_id
                JOIN ".TB_PREF."item_units au 
                    ON ami.tran_unit = au.abbr
                JOIN ".TB_PREF."asset_locations alo 
                    ON ami.asset_location = alo.loc_code 
                JOIN ".TB_PREF."locations sklo 
                    ON ami.stock_location = sklo.loc_code    
                JOIN ".TB_PREF."grn_items git 
                    ON ami.grn_line_no = git.id  
                JOIN ".TB_PREF."purch_order_details pod 
                    ON git.po_detail_item = pod.po_detail_item  
                JOIN ".TB_PREF."stock_master sm 
                    ON git.item_code = sm.stock_id   
                LEFT JOIN ".TB_PREF."project_tasks spt 
                    ON ami.stock_task_id = spt.task_id
            WHERE ami.trans_no = ".db_escape($stock_asset_no)." 
            ORDER BY ami.grn_line_no";
    
        return db_query($sql, 'get_grn_and_stock_asset_transfer_lines_details_by_stock_asset_trans');
}

function get_stock_asset_transfer_grn_line_transferred_total_qty($grn_line_no) {
    $sql = "SELECT SUM(tran_qty) total_qty 
            FROM ".TB_PREF."stock_asset_transfers  
            WHERE grn_line_no = ".db_escape($grn_line_no);
    
        $result = db_query($sql, 'get_stock_asset_transfer_grn_line_transferred_total_qty');
        return db_result_value($result, 'total_qty');
}

function get_stock_asset_transfer_records($trans_no) {
    $sql = "SELECT * 
            FROM ".TB_PREF."stock_asset_transfers  
            WHERE trans_no = ".db_escape($trans_no);
    
        return db_query($sql, 'get_stock_asset_transfer_records');
}
 
?>