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


function get_sql_for_tool_request_inquiry($tool_request_id = 0, $from_date, $to_date, $location = null,
                                        $project_id = 0, $company_code = null, $employee_code = null)
{
    $sql = "SELECT ar.id,
                    ar.reference,
                    ar.date,
                    ar.comment,
                    ar.approval_status,
                    pm.description project_description,
                    em.initials,
                    em.employee_code,
                    em.last_name,
                    lo.location_name,
                    ar.company_code,
                    ar.employee_code 

            FROM ".TB_PREF."tool_requests ar 
                    LEFT JOIN ".TB_PREF."projects_master pm ON
                        ar.project_id = pm.project_id 

                    LEFT JOIN ".TB_PREF."asset_locations lo ON
                        ar.location = lo.loc_code 
                    
                    LEFT JOIN ".TB_PREF."employee_master em ON
                        ar.employee_code = em.employee_code
                 
            WHERE 1=1 ";
   
    if($tool_request_id > 0){
        $sql .= "AND ar.id =".db_escape($tool_request_id);
    }
    else{
        $sql .= "AND ar.date >=".db_escape(date2sql($from_date))." AND  ar.date <=".db_escape(date2sql($to_date));
    
        if($location != null){
            $sql .= "AND ar.location=".db_escape($location);
        } 

        if($project_id > 0){
            $sql .= "AND ar.project_id=".db_escape($project_id);
        }

        if(strlen($company_code) > 0){
            $sql .= "AND ar.company_code=".db_escape($company_code);
        }

        if(strlen($employee_code) > 2){
            $sql .= "AND ar.employee_code=".db_escape($employee_code);
        }

        if($location != ''){
            $sql .= "AND ar.location=".db_escape($location);
        }   
    }
   
    return $sql;
    
}


function get_sql_for_outstanding_tool_request_inquiry($tool_request_id = 0, $from_date, $to_date, $location = null,
                                        $project_id = 0, $company_code = null, $employee_code = null)
{
    $sql = "SELECT ar.id,
                    ar.reference,
                    ar.date,
                    ar.comment,
                    ar.approval_status,
                    pm.description project_description,
                    em.initials,
                    em.employee_code,
                    em.last_name,
                    lo.location_name,
                    ar.company_code,
                    ar.employee_code 

            FROM ".TB_PREF."tool_requests ar 
                    LEFT JOIN ".TB_PREF."projects_master pm ON
                        ar.project_id = pm.project_id 

                    LEFT JOIN ".TB_PREF."asset_locations lo ON
                        ar.location = lo.loc_code 
                    
                    LEFT JOIN ".TB_PREF."employee_master em ON
                        ar.employee_code = em.employee_code
                 
            WHERE (ar.approval_status=".AS_APPROVED." OR ar.approval_status=".AS_ISSUED.") " ;
   
    if($tool_request_id > 0){
        $sql .= "AND ar.id =".db_escape($tool_request_id);
    }
    else{
        $sql .= "AND ar.date >=".db_escape(date2sql($from_date))." AND  ar.date <=".db_escape(date2sql($to_date));
    
        if($location != null){
            $sql .= "AND ar.location=".db_escape($location);
        } 

        if($project_id > 0){
            $sql .= "AND ar.project_id=".db_escape($project_id);
        }

        if($company_code != ''){
            $sql .= "AND ar.company_code=".db_escape($company_code);
        }

        if($employee_code > 0){
            $sql .= "AND ar.employee_code=".db_escape($employee_code);
        }

        if($location != ''){
            $sql .= "AND ar.location=".db_escape($location);
        }   
    }
   
    return $sql;
    
}

        

function view_tool_request($tool_request_id)
{
    $sql = "SELECT ar.*,
                    pm.description project_description,
                    em.last_name employee_last_name,
                    em.employee_code,
                    em.initials,
                    lo.location_name
                            
            FROM ".TB_PREF."tool_requests ar
                    LEFT JOIN ".TB_PREF."projects_master pm ON
                        ar.project_id = pm.project_id
                        
                    LEFT JOIN ".TB_PREF."employee_master em ON
                        ar.employee_code = em.employee_code
                        
                    LEFT JOIN ".TB_PREF."asset_locations lo ON
                        ar.location = lo.loc_code
                
            WHERE ar.id =".db_escape($tool_request_id);
    
    $result = db_query($sql);
    return db_fetch($result);
    
}


function view_tool_request_details($tool_request_id)
{
    $sql = "SELECT ard.*,
                    am.description asset_description,
                    am.asset_code,
                    am.asset_id, 
                    ar.description rate_description,
                    pt.task_code,
                    pt.description task_description,                    
                    ps.description site_name
        
            FROM ".TB_PREF."tool_request_details ard
                    LEFT JOIN ".TB_PREF."asset_master am ON
                        ard.asset_id = am.asset_id
                        
                    LEFT JOIN ".TB_PREF."tool_rates ar ON
                        ard.rate_category_id = ar.id
                        
                    LEFT JOIN ".TB_PREF."project_tasks pt ON
                        ard.task_id = pt.task_id
                        
                    LEFT JOIN ".TB_PREF."project_sites ps ON
                        ard.site_id = ps.id
                
            WHERE ard.ar_id=".db_escape($tool_request_id);
     
    
    return db_query($sql);
      
}

  

function get_sql_for_approve_tool_request_inquiry($current_user_id)
{        
    $sql = "SELECT ar.*,
                    pm.description project_description, 
                    lo.location_name   
            
            FROM ".TB_PREF."tool_requests ar 
                JOIN ".TB_PREF."projects_master pm ON
                    ar.project_id = pm.project_id                    
                JOIN ".TB_PREF."asset_locations lo ON
                    ar.location = lo.loc_code                                                       
                JOIN ".TB_PREF."users c ON
                    ar.created_by = c.id 
                JOIN ".TB_PREF."user_data_access_groups udag ON
                    pm.dag_id = udag.dag_id                                
                JOIN ".TB_PREF."approvers apr ON
                    udag.user_id = apr.user_id   
                    
            WHERE (ar.approval_status = ".AS_AWAITING_APPROVAL." OR ar.approval_status = ".AS_WITH_THE_APPROVER.") AND 
                    ar.next_approval_group_id = apr.approval_group_id AND 
                    ar.created_by = c.id AND 
                  
                    udag.user_id = apr.user_id AND  
                 
                    apr.user_id =".db_escape($current_user_id);                    
                             
    return $sql; 
}

function view_tool_issues_for_tool_request($tool_request_id)
{
    $sql = "SELECT ai.*,
                    lo.location_name 
            FROM ".TB_PREF."tool_issues ai 
                LEFT JOIN ".TB_PREF."asset_locations lo ON
                        ai.location = lo.loc_code 
            WHERE ai.ar_id=".db_escape($tool_request_id);
     
    
    return db_query($sql, "view_tool_issues_for_tool_request");
}
        
?>