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


function get_sql_for_stock_items_adjustment_inquiry($from_date, $to_date, $receiving_location, $reference, $project_id, $id)                                                     
{
    $sql = "SELECT  sa.id,
                    sa.reference,
                    l.location_name,
                    sa.ai_date,
                    pm.description project_description,
                    sa.type,
                    IF(sa.increase = 1 , 'Yes', 'No') increase,  
                    sa.comments,
                    mt.name movement_type,
                    IF(sa.sample_items = 1 , 'Yes', 'No') sample_items,
                    sa.approval_status, 
                    sa.type adjustment_type  
                    
            FROM ".TB_PREF."stock_adjustment_requests sa  
                    LEFT JOIN ".TB_PREF."projects_master pm 
                        ON sa.project_id = pm.project_id 
                    LEFT JOIN ".TB_PREF."locations l 
                        ON sa.location = l.loc_code        
                    LEFT JOIN ".TB_PREF."movement_types mt 
                        ON mt.id = sa.movement_type        
                               
            WHERE 1=1 ";
    
    if($id>0 || strlen($reference)>0){
        if($id>0){
            $sql .= " AND sa.id = ".db_escape($id);
        }
        if(strlen($reference)>0){
            $sql .= " AND sa.reference = ".db_escape($reference);
        }
    }
    else{
        $sql .= " AND sa.ai_date >= ".db_escape(date2sql($from_date))." AND sa.ai_date <= ".db_escape(date2sql($to_date)); 
            
    
        if($receiving_location != ''){        
             $sql .= " AND sa.location = ".db_escape($receiving_location);
         }

         if($reference > 0){
             $sql .= " AND sa.reference = ".db_escape($reference);
         }

         if($project_id > 0){
             $sql .= " AND sa.project_id=".db_escape($project_id);
         }
    }
    
    $sql .= " ORDER BY sa.id";
    return $sql;
    
}



//function get_sql_for_approve_stock_adjustment_request_inquiry($from_date, $to_date, $receiving_location, $reference, $project_id, $id)                                                     
function get_sql_for_approve_stock_adjustment_request_inquiry($current_user_id)                                                     
{
    $sql = "SELECT  sa.id,
                    sa.reference,
                    l.location_name,
                    sa.ai_date,
                    pm.description project_description,
                    sa.type,
                    IF(sa.increase = 1 , 'Yes', 'No') increase,  
                    sa.comments,
                    mt.name movement_type,
                    IF(sa.sample_items = 1 , 'Yes', 'No') sample_items,
                    sa.approval_status, 
                    sa.type adjustment_type  
                    
            FROM ".TB_PREF."stock_adjustment_requests sa  
                    JOIN ".TB_PREF."projects_master pm 
                        ON sa.project_id = pm.project_id 
                    JOIN ".TB_PREF."locations l 
                        ON sa.location = l.loc_code        
                    JOIN ".TB_PREF."movement_types mt 
                        ON mt.id = sa.movement_type      
                    
                    JOIN ".TB_PREF."approvers apr ON
                        sa.next_approval_group_id = apr.approval_group_id  
                        
                    JOIN ".TB_PREF."users c ON
                        sa.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 (sa.approval_status = ".AS_AWAITING_APPROVAL." OR sa.approval_status = ".AS_WITH_THE_APPROVER.") AND                 
                    udag.user_id = apr.user_id AND                   
                    apr.user_id =".db_escape($current_user_id);  
    
//    if($id>0 || strlen($reference)>0){
//        if($id>0){
//            $sql .= " AND sa.id = ".db_escape($id);
//        }
//        if(strlen($reference)>0){
//            $sql .= " AND sa.reference = ".db_escape($reference);
//        }
//    }
//    else{
//        $sql .= " AND sa.ai_date >= ".db_escape(date2sql($from_date))." AND sa.ai_date <= ".db_escape(date2sql($to_date)); 
//            
//    
//        if($receiving_location != ''){        
//             $sql .= " AND sa.location = ".db_escape($receiving_location);
//         }
//
//         if($reference > 0){
//             $sql .= " AND sa.reference = ".db_escape($reference);
//         }
//
//         if($project_id > 0){
//             $sql .= " AND sa.project_id=".db_escape($project_id);
//         }
//    }
    
    $sql .= " ORDER BY sa.id";
    return $sql;
    
}


function get_stock_adjustment_request_items($trans_type, $trans_no)
{
    $sql = "SELECT loc.location_name, sar.reference, sar.ai_date,
                   sm.description, sar.comments,
                   pm.description project_name,
                   mt.name movement_type,
                   IF(sar.sample_items = 1, 'Yes', 'No') sample_items,
                   sar.approval_status,
                   sar.id,
                   IF(sa.id > 0, 'Yes', 'No') process,
                   sa.id sa_id,
                   sad.*,
                   sar.linked_field_type,
                   sar.linked_field_id,
                   pt.description task_description 
                   
            FROM ".TB_PREF."stock_adjustment_requests sar 
                LEFT JOIN ".TB_PREF."stock_adjustment_request_details sad  
                    ON sar.id = sad.trans_no 
                LEFT JOIN ".TB_PREF."locations loc   
                    ON sar.location = loc.loc_code  
                LEFT JOIN ".TB_PREF."stock_master sm    
                    ON sm.stock_id = sad.stock_id  
                LEFT JOIN ".TB_PREF."projects_master pm     
                    ON pm.project_id = sar.project_id  
                LEFT JOIN ".TB_PREF."movement_types mt      
                    ON sar.movement_type = mt.id   
                LEFT JOIN ".TB_PREF."stock_adjustments sa       
                    ON sar.id = sa.sar_id   
                LEFT JOIN ".TB_PREF."project_tasks pt          
                    ON pt.task_id = sad.task_id 
            WHERE sar.type = ".db_escape($trans_type)."
                AND sar.id = ".db_escape($trans_no)."
            ORDER BY sar.ai_date";
    
    
    return db_query($sql,"get_stock_adjustment_request_items");
}
       

function view_stock_adjustment_items($trans_type, $trans_no)
{
    $sql = "SELECT loc.location_name, 
                    sar.reference, sar.ai_date, sar.comments, sar_id,
                   sm.description, 
                   pm.description project_name,
                   mt.name movement_type,
                   IF(sar.sample_items = 1, 'Yes', 'No') sample_items,
                   sad.*,
                   sar.linked_field_type,
                   sar.linked_field_id,
                   jb.description job,
                   pt.description task_description                   
                   
            FROM ".TB_PREF."stock_adjustments sar 
                JOIN ".TB_PREF."stock_moves sad  
                    ON sar.id = sad.trans_no 
                    AND sad.type = sar.type                     
                JOIN ".TB_PREF."locations loc   
                    ON sar.location = loc.loc_code  
                JOIN ".TB_PREF."stock_master sm    
                    ON sm.stock_id = sad.stock_id  
                JOIN ".TB_PREF."projects_master pm     
                    ON pm.project_id = sar.project_id  
                LEFT JOIN ".TB_PREF."movement_types mt      
                    ON sar.movement_type = mt.id   
                LEFT JOIN ".TB_PREF."project_sites jb       
                    ON sad.project_site_id = jb.id                    
                LEFT JOIN ".TB_PREF."project_tasks pt          
                    ON pt.task_id = sad.task_id 
            WHERE sar.type = ".db_escape($trans_type)."
                AND sar.id = ".db_escape($trans_no)."
            ORDER BY sar.ai_date";
    
    
    return db_query($sql,"get_stock_adjustment_request_items");
}


?>