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

function write_asset_transfer_request_header($atr_id, $reference, $from_project_id, $to_project_id, $from_location, $to_location, $comment, $date, $row_state){
    
    global $Refs;
        if($atr_id > 0){
            $msg = update_asset_transfer_request($atr_id, $reference, $from_project_id, $to_project_id, $from_location, $to_location, $comment, $date, $row_state);
            if($msg['success'] !== true){
                return $msg;
            }
        }else{
            $msg = insert_asset_transfer_request($reference, $from_project_id, $to_project_id, $from_location, $to_location, $comment, $date);
            if($msg['success'] !== true){
                return $msg;
            }
        }
        $Refs->save(ST_ASSET_TRANSFER_REQUEST, $msg['atr_id'], $reference);
        
    return array("success" => true, "message" => "", "atr_id" => $msg['atr_id'], "row_state" => $row_state);
}

function update_asset_transfer_request($atr_id, $reference, $from_project_id, $to_project_id, $from_location, $to_location, $comment, $date, $row_state){    
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
    
    $sql = "UPDATE ".TB_PREF."asset_transfer_requests 
                SET reference = ".db_escape($reference).",
                    from_project_id = ".db_escape($from_project_id).",
                    to_project_id = ".db_escape($to_project_id).",
                    from_asset_location = ".db_escape($from_location).",                          
                    to_asset_location = ".db_escape($to_location).",                          
                    tran_date = ".db_escape(date2sql($date)).",
                    comment = ".db_escape($comment).", 
                    updated_by = ".db_escape($updated_by).", 
                    updated_on = ".db_escape($updated_on).", 
                    row_state = ".db_escape(db_new_row_state($row_state))." 

            WHERE id = ".db_escape($atr_id)." 
                AND row_state = ".db_escape($row_state);
          
    db_query($sql, "could not update_asset_transfer_request"); 
    
    if(db_num_affected_rows() != 1){
        cancel_transaction();
        return array("success" => false, "message" => "Could not update Asset Transfer Request Header #$atr_id");
    }else{
        return array("success" => true, "message" => "", "atr_id" => $atr_id);
    }
}
         
function insert_asset_transfer_request($reference, $from_project_id, $to_project_id, $from_location, $to_location, $comment, $date){
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
    $sql = "INSERT INTO ".TB_PREF."asset_transfer_requests 
                  ( reference, from_project_id, to_project_id, 
                    from_asset_location, to_asset_location, 
                    comment, tran_date, 
                    created_by, created_on )         
            VALUES( ".db_escape($reference).", ".db_escape($from_project_id).",".db_escape($to_project_id).", 
                    ".db_escape($from_location).", ".db_escape($to_location).", 
                    ".db_escape($comment).", ".db_escape(date2sql($date)).",                   
                    ".db_escape($created_by).", ".db_escape($created_on).")";
     
    db_query($sql, "could not insert_asset_transfer_request");

    if(!(db_insert_id() > 0)){
        cancel_transaction();
        return array("success" => false, "message" => "Could not Insert a New Asset Transfer Request Header.", "atr_id" => null);
    }else{
        return array("success" => true, "message" => "", "atr_id" => db_insert_id());
    }
}
        
function write_asset_transfer_request_line($asset_tran_req_no, $atr_line_id, $asset_id, $quantity, $current_value, $from_site_id, $to_site_id, $line_comment){
    if($atr_line_id > 0){
        $msg = update_asset_transfer_request_line($atr_line_id, $asset_id, $quantity, $current_value, $from_site_id, $to_site_id, $line_comment);
        if($msg['success'] !== true){
            return $msg;
        }
    }else{
        $msg = insert_asset_transfer_request_line($asset_tran_req_no, $asset_id, $quantity, $current_value, $from_site_id, $to_site_id, $line_comment);
        if($msg['success'] !== true){
            return $msg;
        }
    }
    return array("success" => true, "message" => "", "atr_id" => $asset_tran_req_no);
}

function update_asset_transfer_request_line($atr_line_id, $asset_id, $quantity, $current_value, $from_site_id, $to_site_id, $line_comment){
    $sql = "UPDATE ".TB_PREF."asset_transfer_request_details 
                SET quantity = ".db_escape($quantity).", 
                    asset_id = ".db_escape($asset_id).",
                    asset_value = ".db_escape($current_value).",
                    from_site_id = ".db_escape($from_site_id).",
                    to_site_id = ".db_escape($to_site_id).",
                    line_comment = ".db_escape($line_comment).", 
                    row_state = row_state + 1 
            WHERE id = ".db_escape($atr_line_id);
    
    db_query($sql, "Could not update_asset_transfer_request_line");
    
    if(db_num_affected_rows() != 1){
        cancel_transaction();
        return array("success" => false, "message" => "Could not update Asset Transfer Request Details Line #$atr_line_id");
    }else{
        return array("success" => true, "message" => "", "atr_line_id" => $atr_line_id);
    }
}        

function insert_asset_transfer_request_line($asset_tran_req_no, $asset_id, $quantity, $current_value, $from_site_id, $to_site_id, $line_comment){
    $sql = "INSERT INTO ".TB_PREF."asset_transfer_request_details 
                        ( atr_id, asset_id, 
                          quantity, asset_value, 
                          from_site_id, to_site_id, line_comment )        
            VALUE       ( ".db_escape($asset_tran_req_no).", ".db_escape($asset_id).",
                          ".db_escape($quantity).", ".db_escape($current_value).",  
                          ".db_escape($from_site_id).", ".db_escape($to_site_id).", ".db_escape($line_comment).")";      
    db_query($sql, "could not insert_asset_transfer_request_line");
    
    if(!(db_insert_id() > 0)){
        cancel_transaction();
        return array("success" => false, "message" => "Could not Insert a New Asset Transfer Request Details Line.");
    }else{
        return array("success" => true, "message" => "", "atr_line_id" => db_insert_id());
    }
}

function get_asset_transfer_request_header($asset_tran_req_no){
    $sql = "SELECT atr.*,
                    pm.description from_project_description,  
                    pm2.description to_project_description,
                    falo.location_name from_location_name,
                    talo.location_name to_location_name 
                    
            FROM ".TB_PREF."asset_transfer_requests atr
                LEFT JOIN ".TB_PREF."projects_master pm 
                    ON atr.from_project_id = pm.project_id
                LEFT JOIN ".TB_PREF."projects_master pm2 
                    ON atr.to_project_id = pm2.project_id
                LEFT JOIN ".TB_PREF."asset_locations falo  
                    ON atr.from_asset_location = falo.loc_code  
                LEFT JOIN ".TB_PREF."asset_locations talo  
                    ON atr.to_asset_location = talo.loc_code  
            WHERE atr.id = ".db_escape($asset_tran_req_no);
    
    $result = db_query($sql, "Could not get_asset_transfer_request_header");
    return db_fetch($result);
}
 
function get_asset_transfer_request_detail_line($selected_id){
    $sql = "SELECT atrd.*         
            FROM ".TB_PREF."asset_transfer_request_details atrd
            WHERE atrd.id = ".db_escape($selected_id);
    
    $result = db_query($sql, "could not get_asset_transfer_request_detail_line");
    return db_fetch($result);
}

function delete_asset_transfer_request_detail($selected_id){
    $sql = "DELETE FROM ".TB_PREF."asset_transfer_request_details WHERE id=".db_escape($selected_id);
    db_query($sql, "could not delete_asset_transfer_request_detail");
    return db_num_affected_rows();
} 

function get_asset_transfer_request_details($atr_id){
    $sql = "SELECT atrd.*,
                    am.asset_code,
                    am.description asset_description,
                    am.asset_id,
                    am.current_value,
                    am.multiple_items,
                    ps.description from_site_description,
                    ps2.description to_site_description 

            FROM ".TB_PREF."asset_transfer_request_details atrd 
                LEFT JOIN ".TB_PREF."asset_master am 
                    ON atrd.asset_id = am.asset_id 
                LEFT JOIN ".TB_PREF."project_sites ps 
                    ON atrd.from_site_id = ps.id 
                LEFT JOIN ".TB_PREF."project_sites ps2 
                    ON atrd.to_site_id = ps2.id 
            WHERE atrd.atr_id = ".db_escape($atr_id);
    
    return db_query($sql, "Could not get_asset_transfer_request_details");
}

function get_asset_transfer_request_details_line_count($asset_tran_req_no){
    $sql = "SELECT COUNT(id) line_count 
            FROM ".TB_PREF."asset_transfer_request_details  
            WHERE atr_id = ".db_escape($asset_tran_req_no);
    
    $result = db_query($sql, "could not get_asset_transfer_request_details_line_count");
    $count =  db_fetch($result);   
    if(isset($count['line_count']) && $count['line_count'] > 0){
        return $count['line_count'];
    }    
    return 0;
}
   
function release_asset_transfer_request_for_approval($asset_tran_req_no){
    $current_user_id = $_SESSION['wa_current_user']->user;
    $atrh = get_asset_transfer_request_header($asset_tran_req_no);
    
    $info = approve_document(ST_ASSET_TRANSFER_REQUEST, $asset_tran_req_no, $atrh['from_project_id'], $atrh['total'], $atrh['next_approval_group_id'], 
                             $current_user_id, $atrh['row_state']);

    if($info['success'] !== true){
        cancel_transaction();
        return array("success" => $info['success'],"tran_no" => $asset_tran_req_no, "message" => $info['message']);
    }

    return array("success" => true, "atr_id" => $asset_tran_req_no, "message" => $info['message']);  
}

function update_asset_transfer_request_header_total($asset_tran_req_no){
    
    $total = get_asset_transfer_request_lines_total($asset_tran_req_no);
    $row_state = get_asset_transfer_request_header($asset_tran_req_no);
    
    $sql = "UPDATE ".TB_PREF."asset_transfer_requests  
            SET row_state = ".db_escape(db_new_row_state($row_state['row_state'])).", 
                total = ".db_escape($total)." 
            WHERE id = ".db_escape($asset_tran_req_no)." 
                AND row_state = ".db_escape($row_state['row_state']);
    
    db_query($sql, "could not update_asset_transfer_request_header_total");
    
    $n = db_num_affected_rows();
    if($n != 1){
        cancel_transaction();
        return array("success" => false, "message" => "Could not update Asset Transfer Request Header Total #$asset_tran_req_no");
    }else{
        return array("success" => true, "message" => "", "atr_line_id" => $asset_tran_req_no);
    }
}

function get_asset_transfer_request_lines_total($asset_tran_req_no){
    $sql = "SELECT SUM(asset_value * quantity) total 
            FROM ".TB_PREF."asset_transfer_request_details 
            WHERE atr_id = ".db_escape($asset_tran_req_no);    
    $result = db_query($sql, "could not get_asset_transfer_request_header_total");
    return db_result_value($result, "total");
}

function check_all_asset_transfer_request_lines_fully_transferred($asset_issue_id){
    $sql = "SELECT id num_recs  
            FROM ".TB_PREF."asset_transfer_request_details   
            WHERE quantity > transferred_qty    
                AND atr_id = ".db_escape($asset_issue_id);
    
    $result = db_query($sql, "could not check_all_asset_transfer_request_lines_fully_transferred");
    $num_recs = db_num_rows($result);
    
    if($num_recs > 0){
        return false;
    }else{
        return true;
    }
}

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

function update_asset_transfer_request_row_status($id, $row_state) {
    $sql = "UPDATE ".TB_PREF."asset_transfer_requests 
                SET row_state = ".db_escape(db_new_row_state($row_state))." 
            WHERE id = ".db_escape($id)." 
                AND row_state = ".db_escape($row_state);
                
    db_query($sql, "update_asset_transfer_request_row_status");
    return db_num_affected_rows();
}