<?php

function get_sql_for_supplier_transport_invoices_inquiry($from = null, $to = null, 
                                                        $sti_id =null, $supplier_id = null)
{
        
        $sql = "SELECT 
                        sti.id,
                        sti.reference,
                        p.description project_description,
                        sti.supp_inv_ref,
                        supp.supp_name supplier_name,
                        sti.tran_date,
                        sti.total + sti.taxes sti_total,  
                        c.real_name,
                        sti.approval_status 
                    FROM ".TB_PREF."supplier_transport_invoices sti  
                            JOIN ".TB_PREF."suppliers supp ON 
                                    sti.supplier_id = supp.supplier_id 
                            JOIN ".TB_PREF."projects_master as p ON  
                                    sti.project_id = p.project_id  
                            JOIN ".TB_PREF."users c ON 
                                    sti.created_by = c.id  
                            WHERE  1 ";
      
        if($from != null){
            $sql .= " AND sti.tran_date > ".db_escape(date2sql($from));
        }
        
        if($to != null){
                $sql .= " AND sti.tran_date < ".db_escape(date2sql($to));
        }

        if($supplier_id > 0){
                $sql .= " AND sti.supplier_id = ".db_escape($supplier_id);            
        }
        
        if ($sti_id > 0){
                $sql .= " AND sti.id = ".$sti_id;
        }
        

        $sql .= " ORDER BY sti.id ";   
        
	return $sql;
}

function get_sql_for_approve_supplier_transport_invoices_inquiry($current_user_id)
{
        
        $sql = "SELECT 
                        sti.id,
                        sti.reference,
                        p.description project_description,
                        sti.supp_inv_ref,
                        supp.supp_name supplier_name,
                        sti.tran_date,
                        sti.total + sti.taxes sti_total,                        
                        c.real_name,
                        sti.approval_status 
                    FROM ".TB_PREF."supplier_transport_invoices sti, 
                            ".TB_PREF."suppliers supp,
                            ".TB_PREF."projects_master as p, 
                            ".TB_PREF."users c,
                            ".TB_PREF."approvers as apr,
                            ".TB_PREF."user_data_access_groups as udag,
                            ".TB_PREF."users u  
                                    
                    WHERE  sti.supplier_id=supp.supplier_id AND 
                            sti.project_id = p.project_id AND 
                            (sti.approval_status = ".AS_AWAITING_APPROVAL." OR sti.approval_status = ".AS_WITH_THE_APPROVER.") AND 
                            sti.next_approval_group_id = apr.approval_group_id AND 
                            sti.created_by = c.id AND 
                            p.dag_id = udag.dag_id AND 
                            udag.user_id = apr.user_id AND  
                            apr.user_id = u.id AND 
                            apr.user_id = ".$current_user_id." 
                    ORDER BY
                        sti.id";    
        
	return $sql;
}


function view_supplier_transport_invoice_header($vrc_id)
{
        
        $sql = "SELECT sti.*,
                        supp.supp_name, 
                        pt.terms, 
                        pm.description project_description,
                        t.description task_description
                FROM ".TB_PREF."supplier_transport_invoices sti 
                        INNER JOIN ".TB_PREF."suppliers supp ON 
                                sti.supplier_id = supp.supplier_id 
                        INNER JOIN ".TB_PREF."payment_terms pt ON 
                                sti.payment_terms = pt.terms_indicator 
                        LEFT JOIN ".TB_PREF."projects_master pm ON 
                                sti.project_id = pm.project_id
                        LEFT JOIN ".TB_PREF."project_tasks t ON 
                                sti.task_id = t.task_id  

                WHERE sti.id = ".$vrc_id;
       
        
         $result =db_query($sql,"Could not view_supplier_transport_invoice");
         return db_fetch($result);
}

function view_supplier_transport_invoice_details($sti_id)
{
        
        $sql = "SELECT 
                        vrc.*,
                        p.description project_description,
                        pt.description task_description,
                        vp.description package_description,
                        vh.description vehicle_description,
                        sup.supp_name,
                        dr.name 
                       
                    FROM ".TB_PREF."vehicle_running_chart vrc
                            LEFT JOIN ".TB_PREF."projects_master p ON
                                    p.project_id=vrc.project_id
                            LEFT JOIN ".TB_PREF."project_tasks pt ON
                                    pt.task_id=vrc.task_id 
                            LEFT JOIN ".TB_PREF."vehicles vh ON
                                    vh.id=vrc.vehicle_id
                            LEFT JOIN ".TB_PREF."suppliers sup ON
                                    vrc.supplier_id=sup.supplier_id
                            LEFT JOIN ".TB_PREF."vehicle_packages vp ON
                                    vp.id=vrc.package_id 
                            LEFT JOIN ".TB_PREF."drivers dr ON
                                    dr.id=vrc.driver_id 
                    WHERE vrc.inv_no = ".$sti_id;
       
        
    return db_query($sql,"Could not view_supplier_transport_invoice_details");

}


function view_transport_inv_vehicle_allocations($sti_id)
{

    $sql = "SELECT va.*, 
                    pm.description project_description,
                    vp.description package_description,
                    vh.plate_number,
                    vh.hired,
                    vp.cost,
                    s.supp_name 
            FROM  ".TB_PREF."vehicle_allocations AS va 
                    LEFT JOIN ".TB_PREF."projects_master AS pm ON
                            va.project_id = pm.project_id 
                    LEFT JOIN ".TB_PREF."vehicle_packages AS vp ON 
                            va.package_id = vp.id
                    LEFT JOIN ".TB_PREF."vehicles AS vh ON 
                           va.vehicle_id = vh.id 
                    LEFT JOIN ".TB_PREF."suppliers s ON 
                           va.supplier_id = s.supplier_id 
            WHERE va.transport_inv_no = ".db_escape($sti_id);          
    

               
    return db_query($sql, "Could not get vehicle allocation");
}



?>
