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


function get_sql_for_subcontractor_invoice_inquiry($selected_id, $reference, $project_id, $supplier_id, $from_date, $to_date)
{
    $sql = "SELECT sci.order_no,
                    sci.reference,
                    pm.description project_description,
                    sp.supp_name supplier_name,
                    sci.supp_inv_ref,
                    sci.ord_date,
                    sci.due_date,
                    c.real_name,
                    sci.approval_status,
                    sci.total,
                    sci.taxes 
                    

            FROM ".TB_PREF."subcontractor_invoices sci
                    LEFT JOIN ".TB_PREF."projects_master pm ON
                        sci.project_id = pm.project_id                         
                        
                    LEFT JOIN ".TB_PREF."suppliers sp ON
                        sp.supplier_id = sci.supplier_id 
                         
                    LEFT JOIN ".TB_PREF."users c ON
                        sci.created_by = c.id 
                        
            WHERE sci.ord_date >".db_escape(date2sql($from_date))."AND  sci.ord_date <".db_escape(date2sql($to_date));                        
                      
    if($selected_id > 0){
        $sql .= "AND sci.order_no=".db_escape($selected_id);
    }
    
    if($reference != ''){
        $sql .= "AND sci.reference=".db_escape($reference);
    }
    
    if($project_id > 0){
        $sql .= "AND sci.project_id=".db_escape($project_id);
    }
    
    if($supplier_id > 0){
        $sql .= "AND sci.supplier_id=".db_escape($supplier_id);
    }
    
    
    return $sql;
    
}
        


function get_sql_for_approve_subcontractor_invoice_inquiry($current_user_id)
{
    $sql = "SELECT sci.order_no,
                    sci.reference,
                    pm.description project_description,
                    sp.supp_name supplier_name,
                    sci.supp_inv_ref,
                    sci.ord_date,
                    sci.due_date,
                    c.real_name                
                    

            FROM ".TB_PREF."subcontractor_invoices sci,
                    ".TB_PREF."projects_master pm, 
                    ".TB_PREF."suppliers sp,
                    ".TB_PREF."users c,
                    ".TB_PREF."approvers as apr,
                    ".TB_PREF."user_data_access_groups as udag,
                    ".TB_PREF."users u  
                        
            WHERE sci.project_id = pm.project_id AND 
                    sp.supplier_id = sci.supplier_id AND 
                    (sci.approval_status = ".AS_AWAITING_APPROVAL." OR sci.approval_status = ".AS_WITH_THE_APPROVER.") AND 
                    sci.next_approval_group_id = apr.approval_group_id AND 
                    sci.created_by = c.id AND 
                    pm.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
                        sci.order_no";    
    
    return $sql;
    
}
        

function view_subcontractor_invoice_header($trans_no)
{
    $sql = "SELECT sci.*,
                    pm.description project_description,                   
                    pt.terms payment_terms,      
                    tg.name tax_group_name,
                    sp.supp_name supplier_name,
                    u.real_name 

            FROM ".TB_PREF."subcontractor_invoices sci 
                    INNER JOIN ".TB_PREF."users u ON
                        sci.created_by = u.id  
                    
                    LEFT JOIN ".TB_PREF."projects_master pm ON
                        sci.project_id = pm.project_id

                    LEFT JOIN ".TB_PREF."payment_terms pt ON
                        pt.terms_indicator = sci.payment_terms

                    LEFT JOIN ".TB_PREF."suppliers sp ON
                        sp.supplier_id = sci.supplier_id

                    LEFT JOIN ".TB_PREF."tax_groups tg ON
                        tg.id = sci.tax_group_id

            WHERE   sci.order_no=".db_escape($trans_no);   
                         
    
    $result = db_query($sql);
    return db_fetch($result);

}


function view_subcontractor_invoice_details($order_no)
{
    $sql = "SELECT pid.*, 
                    u.name unit_name,
                    cn.value_name category_name,
                    da.account_name debit_account_name,
                    ca.account_name credit_account_name,
                    pt.task_code, 
                    pt.description task_description, 
                    boqi.code boq_item_code 
            FROM ".TB_PREF."subcontractor_invoice_details pid 
                    LEFT JOIN ".TB_PREF."item_units u ON 
                            pid.unit = u.abbr 
                    LEFT JOIN ".TB_PREF."list_values cn ON 
                            pid.category_id = cn.id 
                    LEFT JOIN ".TB_PREF."chart_master da ON 
                            pid.debit_account = da.account_code  
                    LEFT JOIN ".TB_PREF."chart_master ca ON 
                            pid.credit_account = ca.account_code  
                    LEFT JOIN ".TB_PREF."project_tasks pt ON 
                            pid.task_id = pt.task_id  
                    LEFT JOIN ".TB_PREF."project_boq_items boqi ON 
                            pid.item_id = boqi.id  
                    
            WHERE order_no=".db_escape($order_no)." 
            ORDER BY sequence";
    
    return db_query($sql, "Could not get manual project invoices");
    
}

        
        
?>