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

function add_company_loan($name, $description, $loan_type_id,
                            $credit_facility_id, $bank_account_id, 
                            $loan_amount, $interest, $installment_amount,
                            $installment_interest,  $bank_charge, $start_date, $status, 
                            $inactive, $completed, $original_loan_id,
                            $loan_account, $interest_account, $late_interest_account)
{
        $created_by = $_SESSION['wa_current_user']->user;
        $created_on = date('Y-m-d H:i:s');        
        
	$sql = "INSERT INTO ".TB_PREF."company_loans
                        (name, description, loan_type_id,
                        credit_facility_id, bank_account_id, 
                        loan_amount, interest, installment_amount, 
                        installment_interest, bank_charge, start_date, status, 
                        inactive, completed, original_loan_id,
                        loan_account, interest_account, late_interest_account,
                        created_by, created_on) 
                VALUES (".db_escape($name).", ".db_escape($description).",".db_escape($loan_type_id).","
                        .db_escape($credit_facility_id).", ".db_escape($bank_account_id).","
                        .db_escape($loan_amount). ", ".db_escape($interest).", ".db_escape($installment_amount). ", "
                        .db_escape($installment_interest). ", ".db_escape($bank_charge).", ".db_escape(date2sql($start_date)).", "
                        .db_escape($status).", ".db_escape($inactive).", ".db_escape($completed).", "                        
                        .db_escape($original_loan_id).", ".db_escape($loan_account).","
                        .db_escape($interest_account).", ".db_escape($late_interest_account).","
                        .db_escape($created_by).", ".db_escape($created_on).")";

	db_query($sql,"New Company Loan could not be added");
        return db_insert_id();     
        
}



   
function update_company_loan($selected_id, $name, $description, $loan_type_id, $credit_facility_id, 
                                $loan_amount, $interest, $installment_amount,
                                $installment_interest, $bank_charge, $start_date, $status, 
                                $inactive, $completed,
                                $loan_account, $interest_account, $late_interest_account,
                                $bank_account_id)
{
        $updated_by = $_SESSION['wa_current_user']->user;
        $updated_on = date('Y-m-d H:i:s');
    
        $sql = "UPDATE ".TB_PREF."company_loans 
            
                SET name=".db_escape($name).",
                    description=".db_escape($description).",
                    loan_type_id=".db_escape($loan_type_id).",
                    credit_facility_id=".db_escape($credit_facility_id).", 
                    loan_amount=".db_escape($loan_amount).",                     
                    interest=".db_escape($interest).",
                    installment_amount=".db_escape($installment_amount).",
                    installment_interest=".db_escape($installment_interest).",
                    bank_charge=".db_escape($bank_charge).",   
                    start_date =".db_escape($start_date).",
                    status =".db_escape($status).",
                    inactive=".db_escape($inactive).",
                    completed=".db_escape($completed).",
                    loan_account=".db_escape($loan_account).",
                    interest_account=".db_escape($interest_account).",
                    late_interest_account=".db_escape($late_interest_account).",
                    bank_account_id=".db_escape($bank_account_id).", 
                    updated_by=".db_escape($updated_by).",
                    updated_on=".db_escape($updated_on)." 
                        
		WHERE id=".db_escape($selected_id);                     
        
	db_query($sql,"New Company Loan could not be updated");
}

function update_company_loan_paid_amounts($loan_id, $total_paid_amount, $total_interest, $total_late_interest, $last_payment_date)
{
    $sql = "UPDATE ".TB_PREF."company_loans
            SET   total_captal_paid =".db_escape($total_paid_amount).", 
                    total_interest_paid =".db_escape($total_interest).",
                    total_late_interest_paid =".db_escape($total_late_interest).",
                    last_payment_date=".db_escape($last_payment_date)." 
            WHERE id=".db_escape($loan_id)." 
                    AND loan_amount >=  ".$total_paid_amount;
    
    db_query($sql);
    return db_num_affected_rows();
}
  
  
function update_company_loan_completed($loan_id)
{
        $sql = "UPDATE ".TB_PREF."company_loans             
                SET completed= 1   
                WHERE id=".db_escape($loan_id);                     
        
	db_query($sql,"New Could not update_company_loan_completed");
}


function delete_company_loan($selected_id)
{
	$sql = "DELETE FROM ".TB_PREF."company_loans WHERE id=".db_escape($selected_id);
        
	db_query($sql,"could not delete selected company loan");
}

function get_company_loans()
{
    $sql = "SELECT cl.* , 
                    cf.name  credit_facility_name,
                    lv.value_name bank_name 
        
            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 ";
    
    return db_query($sql, "Company Loans could not be retrieved");
}


function get_company_loan($selected_id)
{
    $sql = "SELECT cl.*,
                    bnk.value_name bank_name,
                    ba.bank_account_number  
            FROM ".TB_PREF."company_loans cl
                    LEFT JOIN ".TB_PREF."list_values AS bnk ON 
                            cl.bank_id = bnk.id
                    LEFT JOIN ".TB_PREF."bank_accounts ba ON
                            cl.bank_account_id = ba.id 
           WHERE cl.id = ".db_escape($selected_id); 

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

function get_company_loan_header($company_loan_id)
{
    $sql = "SELECT cl.*,
                    cf.name credit_facility_name
        
            FROM ".TB_PREF."company_loans cl 
                    ".TB_PREF."credit_facilities cf


            WHERE   cf.id = cl.credit_facility_id AND 
                    cl.id=".db_escape($company_loan_id);

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

function get_total_paid_from_company_loan_installments($loan_id)
{
    $sql = "SELECT SUM(installment_amount) total_installment_amount
            FROM ".TB_PREF."company_loan_installments
            WHERE loan_id=".db_escape($loan_id);
    
    $result = db_query($sql);
    return db_fetch($result);
}


function get_total_interest_from_company_loan_installments($loan_id)
{
    $sql = "SELECT SUM(interest_amount) total_interest_amount
            FROM ".TB_PREF."company_loan_installments
            WHERE loan_id=".db_escape($loan_id);
    
    $result = db_query($sql);
    return db_fetch($result);
}



function get_total_loan_for_selected_credit_facility($credit_facility_id, $selected_id)
{
    $sql = "SELECT SUM(loan_amount) total_loan_amount
            FROM ".TB_PREF."company_loans
            WHERE credit_facility_id=".db_escape($credit_facility_id)." AND
                    id<>".db_escape($selected_id);
    
    $result = db_query($sql);
    return db_fetch($result);
}



function get_loan_record($selected_id)
{
    $sql = "SELECT cl.* 
            FROM ".TB_PREF."company_loans cl 
            WHERE cl.id=".db_escape($selected_id);
            
    $result = db_query($sql, 'Could not get_loan_record');
    return db_fetch($result);
    
}
 

function complete_loan($selected_id)
{
    $id_user = $_SESSION['wa_current_user']->user;
    $datetime = date('Y-m-d H:i:s');
    
	$sql = "UPDATE ".TB_PREF."company_loans 
                SET completed= 1, 
                updated_by=".db_escape($id_user).", 
                updated_on=".db_escape($datetime)."                
                                    
		WHERE id=".db_escape($selected_id)." AND completed = 0";
                             
	db_query($sql, "Could not complete loan");
    
    $n = db_num_affected_rows();
    if(!($n == 1)){
        return array("success" => false, "message" => "Could not complete this loan.");
    }
    
        return array("success" => true, "trans_no" => $selected_id, "message" => "Loan $selected_id has been completed.");
}


function extend_loan($selected_id, $extend_loan_reference, $extend_loan_description, 
                        $extend_loan_amount, $extend_interest, $extend_installment_amount, 
                        $extend_installment_interest, $extend_bank_charge, $extend_start_date)
{
    $msg = complete_loan($selected_id);
    if($msg['success'] !== true){
        return $msg;
    }

    $old_loan = get_loan_record($selected_id);
    
    $status = 'N';
    add_company_loan($extend_loan_reference, $extend_loan_description, $old_loan['loan_type_id'], $old_loan['credit_facility_id'],$old_loan['bank_account_id'],
                        $extend_loan_amount, $extend_interest, $extend_installment_amount, $extend_installment_interest, $extend_bank_charge, $extend_start_date,                     
                        $status, 0, 0, $selected_id, 
                        $old_loan['loan_account'], $old_loan['interest_account'], $old_loan['late_interest_account']);                      
    
    $new_loan_id  = db_insert_id();
    if(!($new_loan_id > 0)){
        return array("success" => false, "message" => "Could not create a new loan.");
    }
    
    return array("success" => true, "trans_no" => $new_loan_id, "message" => "Loan $selected_id has been completed and new Loan $new_loan_id created.");
    
}


function get_extended_id($selected_id)
{
    $sql = "SELECT *
            FROM ".TB_PREF."company_loans
            WHERE original_loan_id = ".db_escape($selected_id);
    
   $result = db_query($sql);
   return db_fetch($result);
    
}


?>