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

function get_sql_for_subcontractor_credit_note_inquiry($selected_id, $reference, $project_id, 
                                                        $supplier_id, $sc_invoice_id, $from_date, $to_date )
{        
    $sql = "SELECT scn.*,
                    pm.	description project_description,
                    su.supp_name supplier_name,
                    sci.order_no invoice_order_no,
                    sci.reference invoice_referecne,
                    sci.ord_date invoice_order_date,
                    sci.total invoice_total                    
            
            FROM ".TB_PREF."subcontractor_credit_notes scn
                    LEFT JOIN ".TB_PREF."projects_master pm ON
                        scn.project_id = pm.project_id
                        
                    LEFT JOIN ".TB_PREF."suppliers su ON
                        scn.supplier_id = su.supplier_id
             
                    LEFT JOIN ".TB_PREF."subcontractor_invoices sci ON
                        scn.sc_invoice_id = sci.order_no 

            WHERE scn.ord_date >= ".db_escape(date2sql($from_date)). "AND 
                    scn.ord_date <=".db_escape(date2sql($to_date));
                        
    
            if($selected_id > 0){ 
                $sql .= " AND scn.order_no=".db_escape($selected_id);
            }
            
            if($reference != ''){ 
                $sql .= " AND scn.reference=".db_escape($reference);
            }
            
            if($project_id > 0){ 
                $sql .= " AND scn.project_id=".db_escape($project_id);
            }
                 
            
            if($supplier_id > 0){ 
                $sql .= " AND scn.supplier_id=".db_escape($supplier_id);
            }
            
            if($sc_invoice_id > 0){ 
                $sql .= " AND scn.sc_invoice_id=".db_escape($sc_invoice_id);
            }
                               
//    $sql .= " ORDER BY ord_date";
                                  
    return $sql; 
}

        
function get_sql_for_approve_subcontractor_credit_note_inquiry($current_user_id)
{        
    $sql = "SELECT scn.*,
                    pm.	description project_description,
                    su.supp_name supplier_name,
                    scn.order_no invoice_order_no,
                    scn.reference invoice_referecne,
                    scn.ord_date invoice_order_date,
                    scn.total invoice_total                    
            
            FROM ".TB_PREF."subcontractor_credit_notes scn
                    JOIN ".TB_PREF."projects_master pm ON
                        scn.project_id = pm.project_id                         
                    JOIN ".TB_PREF."suppliers su ON
                        scn.supplier_id = su.supplier_id         
                    JOIN ".TB_PREF."users c ON 
                        scn.created_by = c.id  
                    JOIN ".TB_PREF."approvers as apr ON 
                        scn.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 (scn.approval_status = ".AS_AWAITING_APPROVAL." OR scn.approval_status = ".AS_WITH_THE_APPROVER.") AND 
                    apr.user_id = ".$current_user_id."  
                        
            ORDER BY scn.order_no";    

            return $sql;
    
}


function view_subcontractor_credit_note_header($trans_no)
{
    $sql = "SELECT sccn.*,
                    pm.description project_description,
                    sup.supp_name supplier_name,
                    sci.order_no invoice_id,
                    sci.reference invoice_ref,
                    sci.ord_date invoice_ord_date,
                    sci.total invoice_total,
                    tg.name tax_group_name                   

            FROM ".TB_PREF."subcontractor_credit_notes sccn
                	                   
                LEFT JOIN ".TB_PREF."projects_master pm ON
                    sccn.project_id = pm.project_id
                    
                LEFT JOIN ".TB_PREF."suppliers sup ON
                    sccn.supplier_id = sup.supplier_id
                    
                LEFT JOIN ".TB_PREF."subcontractor_invoices sci ON
                    sccn.sc_invoice_id = sci.order_no
                    
                LEFT JOIN ".TB_PREF."tax_groups tg ON
                    sccn.tax_group_id = tg.id
                    

            WHERE sccn.order_no=".db_escape($trans_no);
                
    $result = db_query($sql);
    return db_fetch($result);
}
        
        
function view_subcontractor_credit_note_details($trans_no)
{
    $sql = "SELECT  sccnd.*,
                    lv.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,
                    boqi.description boq_item_description   
        
            FROM ".TB_PREF."subcontractor_credit_note_details sccnd
                    LEFT JOIN ".TB_PREF."list_values lv ON
                            sccnd.category_id = lv.id                     
                    LEFT JOIN ".TB_PREF."chart_master da ON
                            sccnd.debit_account = da.account_code                     
                    LEFT JOIN ".TB_PREF."chart_master ca ON
                            sccnd.credit_account = ca.account_code                     
                    LEFT JOIN ".TB_PREF."project_tasks pt ON 
                            sccnd.task_id = pt.task_id                  
                    LEFT JOIN ".TB_PREF."project_boq_items boqi ON 
                            sccnd.item_id = boqi.id  

            WHERE sccnd.order_no=".db_escape($trans_no);
                
    return db_query($sql);
    
}




?>