<?php

function get_sql_for_company_loans_inquiry($loan_id, $loan_name,
                                            $from_date, $to_date, $bank_id )                                  
{        
        $sql = "SELECT cl.id,
                    cl.name,                    
                    cl.description,
                    ltn.value_name loan_type_name, 
                    cf.name  credit_facility_name,
                    lv.value_name bank_name,
                    ba.bank_account_number,
                    cl.* 
        
                FROM ".TB_PREF."company_loans cl 
                        LEFT JOIN ".TB_PREF."credit_facilities cf ON 
                                cl.credit_facility_id = cf.id 
                        LEFT JOIN ".TB_PREF."list_values lv ON
                                lv.id = cl.bank_id 
                        LEFT JOIN ".TB_PREF."bank_accounts ba ON
                                cl.bank_account_id = ba.id 
                        LEFT JOIN ".TB_PREF."list_values ltn ON
                                cl.loan_type_id = ltn.id 

                WHERE cl.start_date >= ".db_escape(date2sql($from_date)). "AND 
                    cl.start_date <=".db_escape(date2sql($to_date));
                        
        
        if($loan_id > 0)
        {
            $sql .= " AND cl.id = ".db_escape($loan_id);            
        }
        
        
        if(strlen($loan_name) > 0)
        {
            $sql .= " AND cl.name LIKE ".db_escape('%'.$loan_name.'%');            
        }

        if($bank_id > 0)
        {
            $sql .= " AND cl.bank_id = ".db_escape($bank_id);           
        }        

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


function view_company_loan($loan_id)
{
    $sql = "SELECT cl.*,
                    cf.name credit_facility_name,
                    CONCAT(ba.bank_account_number, ' - ', ba.bank_account_name) bank_account_name  ,
                    CONCAT(la.account_code, ' - ', la.account_name) loan_account_name  ,
                    CONCAT(ia.account_code, ' - ', ia.account_name) interest_account_name  ,
                    CONCAT(lia.account_code, ' - ', lia.account_name) late_interest_account_name,
                    ltn.value_name loan_type_name 

            FROM ".TB_PREF."company_loans cl
                LEFT JOIN ".TB_PREF."credit_facilities cf ON
                    cf.id = cl.credit_facility_id
                
                LEFT JOIN ".TB_PREF."bank_accounts ba ON
                    ba.id = cl.bank_account_id
                    
                LEFT JOIN ".TB_PREF."chart_master la ON
                    cl.loan_account = la.account_code
                    
                LEFT JOIN ".TB_PREF."chart_master ia ON
                    cl.interest_account = ia.account_code
                    
                LEFT JOIN ".TB_PREF."chart_master lia ON
                    cl.late_interest_account = lia.account_code 
                
                LEFT JOIN ".TB_PREF."list_values ltn ON
                    cl.loan_type_id = ltn.id 
             
            WHERE 
                cl.id=".db_escape($loan_id);
    
    $result = db_query($sql);
    return db_fetch($result);
    
}

function view_company_loan_payments($loan_id)
{
    $sql = "SELECT p.*, 
                    CONCAT(ba.bank_account_number, ' - ', ba.bank_account_name) bank_account_name   

            FROM ".TB_PREF."company_loan_payments p
                    
                    LEFT JOIN ".TB_PREF."bank_accounts ba ON
                            p.bank_account_id = ba.id 
                            
            WHERE p.loan_id=".db_escape($loan_id);
    
    return db_query($sql, "Could not get loan payments");
}

?>
