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

function get_sql_for_asset_transfer_request_inquiry($asset_transfer_request_id = 0, $from_location=null, $to_location=null, $from_project_id=-1, 
                                                    $to_project_id=-1, $approval_status=null) {
    $sql = "SELECT atr.id,
                    atr.reference,
                    pm.description from_project_description,
                    pm2.description to_project_description,
                    flo.location_name from_location_name,
                    tlo.location_name to_location_name,
                    atr.total,
                    atr.comment,
                    atr.approval_status 
                    
            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 flo 
                        ON atr.from_asset_location = flo.loc_code 
                    LEFT JOIN ".TB_PREF."asset_locations tlo  
                        ON atr.to_asset_location = tlo.loc_code 
                 
            WHERE 1 = 1 ";
   
    if($asset_transfer_request_id > 0){
        $sql .= "AND atr.id =".db_escape($asset_transfer_request_id);
    }
    if(strlen($from_location) > 0){
        $sql .= "AND atr.from_asset_location = ".db_escape($from_location);
    } 
    if(strlen($to_location) > 0){
        $sql .= "AND atr.to_asset_location = ".db_escape($to_location);
    } 
    if($from_project_id > 0){
        $sql .= "AND atr.from_project_id = ".db_escape($from_project_id);
    }
    if($to_project_id > 0){
        $sql .= "AND atr.to_project_id = ".db_escape($to_project_id);
    }
    if ($approval_status !== null) {
        $sql .= "AND atr.approval_status = ".db_escape($approval_status);
    }
    
    return $sql;
}
        
function view_asset_transfer_request($asset_transfer_request_id){
    $sql = "SELECT atr.*,
                    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_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 flo 
                    ON atr.from_asset_location = flo.loc_code 
                LEFT JOIN ".TB_PREF."asset_locations tlo  
                    ON atr.to_asset_location = tlo.loc_code 
                
            WHERE atr.id =".db_escape($asset_transfer_request_id);
    
    $result = db_query($sql, "could not view_asset_transfer_request");
    return db_fetch($result);
}

function view_asset_transfer_request_details($asset_transfer_request_id){
    
    $sql = "SELECT atrd.*,
                    am.asset_code,
                    am.description asset_description,
                    am.asset_id,
                    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($asset_transfer_request_id);

    return db_query($sql, "could not view_asset_transfer_request_details");  
}

function view_asset_transfer_request_details_with_ART_and_AT_RCPT($asset_transfer_request_id) {
    $sql = "SELECT atrd.*,
                    am.asset_code,
                    am.description asset_description,
                    am.asset_id,
                    ps.description from_site_description,
                    ps2.description to_site_description,
                    SUM(artd.transferred_qty) received_qty 

            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 
                    
                LEFT JOIN ".TB_PREF."asset_transfer_details artd 
                    ON atrd.id = artd.atr_line_id  
                        AND artd.type = ".db_escape(ST_ASSET_REQUISITION_TRANSFER)." 
            WHERE atrd.atr_id = ".db_escape($asset_transfer_request_id)." 
            GROUP BY atrd.id";

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

function view_asset_requistion_transfers_belong_to_asset_transfer_request($asset_transfer_request_id, $trans_type) {
    $sql = "SELECT *                     
            FROM ".TB_PREF."asset_transfers                  
            WHERE atr_id = ".db_escape($asset_transfer_request_id)." 
                AND type = ".db_escape($trans_type);
    
    return db_query($sql, "could not view_asset_requistion_transfers_belong_to_asset_transfer_request"); 
}

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"); 
}

function get_sql_for_approve_asset_transfer_request_inquiry($current_user_id){ 
    
    /*
    $sql = "SELECT atr.*,
                    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_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 flo 
                    ON atr.from_asset_location = flo.loc_code 
                LEFT JOIN ".TB_PREF."asset_locations tlo  
                    ON atr.to_asset_location = tlo.loc_code 
                                                       
                JOIN ".TB_PREF."approvers apr 
                    ON atr.approved_by = apr.user_id                 
                JOIN ".TB_PREF."users c 
                    ON atr.created_by = c.id
                JOIN ".TB_PREF."user_data_access_groups udag 
                    ON pm.dag_id = udag.dag_id
                JOIN ".TB_PREF."users u 
                    ON apr.user_id = u.id                                 
                                 
            WHERE ( atr.approval_status = ".AS_AWAITING_APPROVAL." OR atr.approval_status = ".AS_WITH_THE_APPROVER." ) AND 
                    atr.next_approval_group_id = apr.approval_group_id AND atr.created_by = c.id AND 
                    udag.user_id = apr.user_id AND apr.user_id = ".db_escape($current_user_id);             
     * 
     */
    
    
    
    $sql = " SELECT atr.*  ,
                    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_transfer_requests atr 
                JOIN ".TB_PREF."projects_master pm 
                    ON atr.from_project_id = pm.project_id             
                JOIN ".TB_PREF."projects_master pm2 
                    ON atr.to_project_id = pm2.project_id 
                JOIN ".TB_PREF."asset_locations flo 
                    ON atr.from_asset_location = flo.loc_code 
                JOIN ".TB_PREF."asset_locations tlo  
                    ON atr.to_asset_location = tlo.loc_code 
                JOIN ".TB_PREF."users c 
                    ON atr.created_by = c.id  
                JOIN ".TB_PREF."approvers as apr 
                    ON atr.next_approval_group_id = apr.approval_group_id  
                JOIN ".TB_PREF."user_data_access_groups as udag ON 
                    pm.dag_id = udag.dag_id AND 
                    udag.user_id = apr.user_id  
                JOIN ".TB_PREF."users u ON 
                    apr.user_id = u.id 
            
            WHERE (atr.approval_status = ".AS_AWAITING_APPROVAL." OR atr.approval_status = ".AS_WITH_THE_APPROVER." ) 
                    AND apr.user_id = ".db_escape($current_user_id);  
                             
    return $sql; 
}

function get_asset_transfer_receipts_total_by_AT_REQ_line_id($req_line_id) {
    $sql = "SELECT SUM(rcp.quantity) total_qty 
            FROM ".TB_PREF."asset_transfer_details tns   
                JOIN ".TB_PREF."asset_transfer_details rcp 
                    ON tns.id = rcp.atr_line_id  
            WHERE tns.atr_line_id = ".db_escape($req_line_id)." 
                AND tns.type = ".db_escape(ST_ASSET_REQUISITION_TRANSFER)." 
                AND rcp.type = ".db_escape(ST_ASSET_TRANSFER_RECEIPT);
    
    $result = db_query($sql, "get_asset_transfer_receipts_total_by_AT_REQ_line_id");
    return db_result_value($result, 'total_qty');
}
        
        
?>