<?php


function save_credit_facility_sub_limit($sublimit_id, $sublimit_type_id, $sublimit_value)
{
    $sublimit = get_credit_facility_sub_limit($sublimit_id);
    if($sublimit){
        update_credit_facility_sub_limit($sublimit_id, $sublimit_type_id, $sublimit_value);
    }
    else{
        add_credit_facility_sub_limit($sublimit_type_id, $sublimit_value);
    }
    
}



function add_credit_facility($name, $description, $bank_id,
                               $branch_name, $start_date, $end_date,
                               $amount, $comment)       
{
        $id_user = $_SESSION['wa_current_user']->user;
        $datetime = date('Y-m-d H:i:s');        

        $sql = "INSERT INTO ".TB_PREF."credit_facilities
                                    (name, description, bank_id, branch_id, 
                                     start_date, end_date, amount, comment)	                                                                                                        
                                               
                VALUES (".db_escape($name).",
                        ".db_escape($description).
                        ",".db_escape($bank_id).
                        ",".db_escape($branch_name).
                        ",".db_escape(date2sql($start_date)).
                        ",".db_escape(date2sql($end_date)).                       
                        ",".db_escape($amount).  
                        ",".db_escape($comment).")";

	 db_query($sql, "Could not add Credit Facility.");
         
     return db_insert_id();
}


function update_credit_facility($id, $name, $description,  
                                  $start_date, $end_date, $amount, $comment )
{
        $id_user = $_SESSION['wa_current_user']->user;
        $datetime = date('Y-m-d H:i:s');
	$sql = "UPDATE ".TB_PREF."credit_facilities 
                             SET name=".db_escape($name).",
                                description=".db_escape($description).",      
                                start_date=".db_escape(date2sql($start_date)).",     
                                end_date=".db_escape(date2sql($end_date)).",                                    
                                amount=".db_escape($amount).",  
                                comment=".db_escape($comment).",
                                updated_by=".db_escape($id_user).", 
                                updated_on=".db_escape($datetime)."                 
                                    
			WHERE id=".db_escape($id);

	db_query($sql, "Could not update Credit Facility");
}


function get_credit_facility($credit_facility_id)
{
	$sql="SELECT cf.*,
                    bnk.value_name bank_name,
                    br.branch_name,
                    ba.bank_account_name 
            FROM ".TB_PREF."credit_facilities cf 
                    INNER JOIN ".TB_PREF."list_values AS bnk ON
                            cf.bank_id = bnk.id
                    INNER JOIN ".TB_PREF."bank_branches AS br ON
                            cf.branch_id = br.id   
                    LEFT JOIN ".TB_PREF."bank_accounts ba ON 
                            cf.bank_account_id = ba.id 
            WHERE cf.id=".db_escape($credit_facility_id);

	$result = db_query($sql, "Could not get_credit_facility");
        return db_fetch($result);
}



function delete_credit_facilities($selected_id)
{
	$sql="DELETE FROM ".TB_PREF."credit_facilities WHERE id=".db_escape($selected_id);

	db_query($sql, "Could not delete Credit Facility");
}


function get_credit_facilities()
{
    $sql = "SELECT cf.id,
                   cf.name,
                   cf.description,
                   lv.value_name,
                   br.branch_name,
                   cf.start_date,
                   cf.end_date,
                   cf.amount,
                   cf.comment
                   
            FROM ".TB_PREF."credit_facilities cf
                LEFT JOIN ".TB_PREF."bank_branches AS br ON
                      cf.branch_id = br.id
                LEFT JOIN ".TB_PREF."list_values AS lv ON
                      cf.bank_id = lv.id";                   

    return db_query($sql, "Could not get credit facility");      
}


function get_company_loan_total($selected_id)
{
    $sql = "SELECT SUM(loan_amount) total_loan_amount
            FROM ".TB_PREF."company_loans               
            WHERE id=".db_escape($selected_id);              
    
    $result = db_query($sql);
    return db_fetch($result);    
}


function get_company_loan_installments_for_calculate_balance($selected_id)
{
     $sql = "SELECT SUM(cl.loan_amount) total_loans ,
                    SUM(cli.installment_amount) total_installments 
        
            FROM ".TB_PREF."credit_facilities cf
                   LEFT JOIN ".TB_PREF."company_loans cl ON
                        cl.credit_facility_id = cf.id
                    LEFT JOIN ".TB_PREF."company_loan_installments cli ON 
                        cl.id = cli.loan_id
                        
            WHERE  cf.id=".db_escape($selected_id);                
    
    $result = db_query($sql);
    return db_fetch($result);    
}


function get_bank_name($credit_facility_id)
{
     $sql = "SELECT lv.value_name,
                    bb.branch_name,                   
                    ba.bank_account_name,
                    cf.bank_account_id bank_account_id,
                    cf.bank_id, 
                    cf.branch_id bank_branch_id
         
            FROM ".TB_PREF."credit_facilities cf,
                    ".TB_PREF."list_values lv,
                    ".TB_PREF."bank_branches bb,
                    ".TB_PREF."bank_accounts ba
                 
            WHERE lv.id = cf.bank_id AND
                    cf.branch_id  = bb.id AND
                    cf.bank_account_id = ba.id AND
                    cf.id=".db_escape($credit_facility_id);
     
     $result = db_query($sql);
     return db_fetch($result);
            
}


function get_total_values_of_bonds_lc_and_company_loans($credit_facility_id)
{
    //company loans
    //bonds
    //LCs
    //Loan installments
    $sql = "SELECT SUM(loan_amount) loans_total FROM ".TB_PREF."company_loans WHERE credit_facility_id=".db_escape($credit_facility_id);
    $result = db_query($sql);
    $loans_sum = db_fetch($result);
    $loans_total = $loans_sum['loans_total'];
    
    $sql = "SELECT SUM(value) bonds_total FROM ".TB_PREF."bonds WHERE credit_facility_id=".db_escape($credit_facility_id);
    $result = db_query($sql);
    $bonds_sum = db_fetch($result);
    $bonds_total = $bonds_sum['bonds_total'];
    
    $sql = "SELECT SUM(value) lc_total FROM ".TB_PREF."letters_of_credit WHERE credit_facility_id=".db_escape($credit_facility_id); 
    $result = db_query($sql);
    $lc_sum = db_fetch($result);
    $lc_total = $lc_sum['lc_total'];
    
    $full_total = $loans_total + $bonds_total +  $lc_total;
    
    
    $sql = "SELECT SUM(cli.installment_amount) installments_total 
        
            FROM ".TB_PREF."company_loan_installments cli, 
                    ".TB_PREF."company_loans cl
                
            WHERE cli.loan_id = cl.id AND
                    cl.credit_facility_id=".db_escape($credit_facility_id); 
    
    
    $result = db_query($sql);
    $loan_installment_sum = db_fetch($result);
    $loan_installment_total = $loan_installment_sum['installments_total'];
    
    $balance = ($full_total - $loan_installment_total);
//    $balance = $loan_installment_total;
    return $balance;    
    
}

function get_credit_facility_amount_total($credit_facility_id)
{
    $sql = "SELECT SUM(amount) amount_total
            FROM ".TB_PREF."credit_facilities
            WHERE id=".db_escape($credit_facility_id);

    $result = db_query($sql);
    return db_fetch($result);
    
}

function get_credit_facility_sublimits($credit_facility_id)
{
    $sql = "SELECT sl.*, 
                    ba.bank_account_number,
                    ba.bank_account_name,
                    ba.account_code 
            FROM ".TB_PREF."credit_facility_sublimits sl 
                    LEFT JOIN ".TB_PREF."bank_accounts ba  ON 
                            sl.bank_account_id = ba.id 
            WHERE credit_facility_id=".db_escape($credit_facility_id);

    return db_query($sql);

}


function get_credit_facility_sub_limit($id)
{
	$sql="SELECT sl.*, ba.bank_account_name, bn.value_name bank_name  
            FROM ".TB_PREF."credit_facility_sublimits sl 
                    INNER JOIN ".TB_PREF."bank_accounts ba ON 
                            sl.bank_account_id = ba.id 
                    INNER JOIN ".TB_PREF."list_values bn ON 
                            ba.bank_id = bn.id 
            WHERE sl.id=".db_escape($id);

	$result = db_query($sql, "Could not get_credit_facility_sub_limit");
    return db_fetch($result);
}

function get_credit_facility_sub_limit_record($id)
{
	$sql="SELECT sl.* 
            FROM ".TB_PREF."credit_facility_sublimits sl 
            WHERE sl.id=".db_escape($id);

	$result = db_query($sql, "Could not get_credit_facility_sub_limit_record");
    return db_fetch($result);
}


function get_credit_facility_sub_limit_record_by_bank_account($credit_facility_id, $bank_account_id)
{
	$sql="SELECT sl.* 
            FROM ".TB_PREF."credit_facility_sublimits sl 
            WHERE sl.credit_facility_id = ".db_escape($credit_facility_id)
                ." AND sl.bank_account_id=".db_escape($bank_account_id);

	$result = db_query($sql, "Could not get_credit_facility_sub_limit_record_by_banck_account");
    return db_fetch($result);
}

function update_credit_facility_sublimit($selected_id, $name, $amount, $bank_account_id, $comment)
{
    $id_user = $_SESSION['wa_current_user']->user;
    $datetime = date('Y-m-d H:i:s');
    
	$sql = "UPDATE ".TB_PREF."credit_facility_sublimits  
            SET name=".db_escape($name).",                                    
                amount=".db_escape($amount).", 
                bank_account_id=".db_escape($bank_account_id).", 
                comment=".db_escape($comment).",
                updated_by=".db_escape($id_user).", 
                updated_on=".db_escape($datetime)."                 
                                    
			WHERE id=".db_escape($selected_id);

	db_query($sql, "Could not update_credit_facility_sublimit");
}

function add_credit_facility_sublimit($credit_facility_id, $name, $amount, $bank_account_id, $comment)
{
    $id_user = $_SESSION['wa_current_user']->user;
    $datetime = date('Y-m-d H:i:s');     

    $sql = "INSERT INTO ".TB_PREF."credit_facility_sublimits
                                (credit_facility_id, name, 
                                 amount, bank_account_id, comment, 
                                 created_by, created_on)	                                                                                                        

            VALUES (".db_escape($credit_facility_id).","
                    .db_escape($name).","
                    .db_escape($amount).","
                    .db_escape($bank_account_id).","
                    .db_escape($comment).","
                    .db_escape($id_user).","
                    .db_escape($datetime).")";

	db_query($sql, "Could not add_credit_facility_sublimit");
}


function check_bank_account_already_selected_for_credit_facility($credit_facility_id, $bank_account_id)
{
        $sql="SELECT id  
                FROM ".TB_PREF."credit_facility_sublimits sl 
                WHERE sl.credit_facility_id = ".db_escape($credit_facility_id)
                        ." AND sl.bank_account_id=".db_escape($bank_account_id);
        
        $result = db_query($sql,"get_bank_account_balance");
        
        $row = db_fetch($result);
        If(db_num_rows($result) > 0){
            return true;
        }
        else{
            return false;
        }
}
?>
