<?php

function get_sql_for_asset_transfer_inquiry1($trans_no, $company_code, $employee_code, $complete='', $trans_type=null) {
    $sql = "SELECT at.id,
                    at.atr_id req_id,
                    at.reference,
                    fl.location_name from_location_name,
                    intt.location_name intransit_location_name,
                    tl.location_name to_location_name,
                    at.company_code,
                    at.employee_code,
                    at.tran_date,
                    at.total,
                    at.comment,
                    at.approval_status,  
                    at.trans_no   
            FROM ".TB_PREF."asset_transfers at 
                JOIN ".TB_PREF."asset_transfer_requests atr 
                    ON at.atr_id = atr.id 
                JOIN ".TB_PREF."asset_locations fl  
                    ON atr.from_asset_location = fl.loc_code  
                JOIN ".TB_PREF."asset_locations tl  
                    ON atr.to_asset_location = tl.loc_code  
                JOIN ".TB_PREF."asset_locations intt   
                    ON at.to_asset_location = intt.loc_code  
            WHERE 1 = 1 ";
   
    if ($trans_no > 0) {
        $sql .= " AND at.trans_no = ".db_escape($trans_no);
    }
    if (strlen($company_code) > 0) {
        $sql .= " AND at.company_code = ".db_escape($company_code);
    }
    if (strlen($employee_code) > 0) {
        $sql .= " AND at.employee_code = ".db_escape($employee_code);
    }
    if ($complete != '') {
        if ($complete == 1) {
            $sql .= " AND at.approval_status = ".db_escape(AS_COMPLETED);
        } else {
            $sql .= " AND at.approval_status <> ".db_escape(AS_COMPLETED);
        }
    }
    if ($trans_type !== null) {
        $sql .= " AND at.type = ".db_escape($trans_type);
    }
        $sql .= " ORDER BY at.tran_date, at.trans_no";
    
    return $sql;
}


function get_sql_for_asset_transfer_inquiry($trans_no, $from_date, $to_date, $from_location, $to_location, $complete='', $trans_type=0) {
    $sql = "SELECT at.id, 
                    at.type,
                    at.trans_no,
                    at.reference,
                    fl.location_name from_location_name, 
                    tl.location_name to_location_name,
                    at.company_code,
                    at.employee_code,
                    at.tran_date,
                    at.total,
                    at.comment,
                    at.approval_status,  
                    at.atr_id,
                    req.type req_type, 
                    req.trans_no req_trans_no 
            FROM ".TB_PREF."asset_transfers at  
                JOIN ".TB_PREF."asset_locations fl  
                    ON at.from_asset_location = fl.loc_code  
                JOIN ".TB_PREF."asset_locations tl  
                    ON at.to_asset_location = tl.loc_code                   
                LEFT JOIN ".TB_PREF."asset_transfers req 
                    ON req.id = at.atr_id 
            WHERE at.tran_date >= ".db_escape($from_date)." 
                AND at.tran_date <= ".db_escape($to_date);
   
    if ($trans_no > 0) {
        $sql .= " AND at.trans_no = ".db_escape($trans_no);
    }
    if (strlen($from_location) > 0) {
        $sql .= " AND at.from_asset_location = ".db_escape($from_location);
    }
    if (strlen($to_location) > 0) {
        $sql .= " AND at.to_asset_location = ".db_escape($to_location);
    }
    if ($complete != '') {
        if ($complete == 1) {
            $sql .= " AND at.approval_status = ".db_escape(AS_COMPLETED);
        } else {
            $sql .= " AND at.approval_status <> ".db_escape(AS_COMPLETED);
        }
    }
    if ($trans_type > 0) {
        $sql .= " AND at.type = ".db_escape($trans_type);
    }
        $sql .= " ORDER BY at.tran_date, at.type, at.trans_no";
    
    return $sql;
}

function get_sql_for_asset_transfer_header_inquiry($trans_no, $company_code, $employee_code, $ap_status='') {
    $sql = "SELECT at.id,
                    at.reference,
                    pm.description from_project_description,
                    pm2.description to_project_description,
                    fl.location_name from_location_name,
                    tl.location_name to_location_name,
                    at.company_code,
                    at.employee_code,
                    at.tran_date,
                    at.approval_status,
                    at.total,
                    at.approval_status,
                    at.type,
                    at.trans_no 
            FROM ".TB_PREF."asset_transfers at 
                LEFT JOIN ".TB_PREF."projects_master pm 
                    ON at.from_project_id = pm.project_id 
                LEFT JOIN ".TB_PREF."projects_master pm2 
                    ON at.to_project_id = pm2.project_id 
                JOIN ".TB_PREF."asset_locations fl  
                    ON at.from_asset_location = fl.loc_code  
                JOIN ".TB_PREF."asset_locations tl  
                    ON at.to_asset_location = tl.loc_code  
            WHERE at.type = ".db_escape(ST_ASSET_TRANSFER);
   
    if ($trans_no > 0) {
        $sql .= " AND at.trans_no = ".db_escape($trans_no);
    }
    if (strlen($company_code) > 0) {
        $sql .= " AND at.company_code = ".db_escape($company_code);
    }
    if (strlen($employee_code) > 0) {
        $sql .= " AND at.employee_code = ".db_escape($employee_code);
    }
    if ($ap_status != -1) {
        $sql .= " AND at.approval_status = ".db_escape($ap_status);
    }
        $sql .= " ORDER BY at.tran_date, at.trans_no";
    
    return $sql;
}
        
function view_asset_transfer_header($asset_transfer_request_id){
    $sql = "SELECT *                     
            FROM ".TB_PREF."asset_transfers                  
            WHERE id = ".db_escape($asset_transfer_request_id);
    
    $result = db_query($sql, "could not view_asset_transfer");
    return db_fetch($result);
}
        
function view_asset_transfer_header_by_type_trans_no($type, $trans_no) {
    $sql = "SELECT art.*,
                pm.description from_project_description,
                pm2.description to_project_description,
                flo.location_name from_location_name,
                tlo.location_name to_location_name 
            FROM ".TB_PREF."asset_transfers art
                LEFT JOIN ".TB_PREF."projects_master pm 
                    ON art.from_project_id = pm.project_id
                LEFT JOIN ".TB_PREF."projects_master pm2  
                    ON art.to_project_id = pm2.project_id
                LEFT JOIN ".TB_PREF."asset_locations flo 
                    ON art.from_asset_location = flo.loc_code 
                LEFT JOIN ".TB_PREF."asset_locations tlo  
                    ON art.to_asset_location = tlo.loc_code 
            WHERE art.type = ".db_escape($type)." 
                AND art.trans_no = ".db_escape($trans_no);
    
    $result = db_query($sql, "view_asset_transfer_header_by_type_trans_no");
    return db_fetch($result);
}

function view_asset_transfer_details($asset_transfer_id){
    $sql = "SELECT atd.*,
                    am.description asset_description,
                    am.asset_code,
                    am.asset_id 
        
            FROM ".TB_PREF."asset_transfer_details atd
                    LEFT JOIN ".TB_PREF."asset_master am 
                        ON atd.asset_id = am.asset_id                 
            WHERE atd.at_id = ".db_escape($asset_transfer_id);
    
    return db_query($sql, "could not view_asset_transfer_details"); 
}

function view_asset_requisition_transfer_details_with_AT_RCPT_counts($asset_requisition_transfer_id) {
    $sql = "SELECT artd.*,
                am.asset_code,
                am.description asset_description,
                am.asset_id,
                artd.quantity in_transit_qty,
                artd.transferred_qty received_qty  
            FROM ".TB_PREF."asset_transfer_details artd 
                JOIN ".TB_PREF."asset_master am 
                    ON artd.asset_id = am.asset_id 
            WHERE artd.at_id = ".db_escape($asset_requisition_transfer_id)." 
                AND artd.type = ".db_escape(ST_ASSET_REQUISITION_TRANSFER);

    return db_query($sql, "view_asset_requisition_transfer_details_with_AT_RCPT_counts");  
}

function view_asset_transfer_receipts_belong_to_asset_requisition_transfer($asset_requisition_transfer_ids_array) {
    $sql = "SELECT *                     
            FROM ".TB_PREF."asset_transfers                  
            WHERE atr_id IN (".implode(',', $asset_requisition_transfer_ids_array).")  
                AND type = ".db_escape(ST_ASSET_TRANSFER_RECEIPT);
    
    return db_query($sql, "view_asset_transfer_receipts_belong_to_asset_requisition_transfer"); 
}

?>