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


function save_company_loan_payments($company_loan_id, $batch_ref,                               
                                    $tran_date, $comments, $is_return,
                                    $bank_account_id, $payment_amount, 
                                    $interest_account, $payment_interest_amount,
                                    $late_interest_account, $late_interest_amount)
{                
    global $Refs;
    
    $sclp_msg = array("success" => false, "message" => "Could not save company loan payment");
    
    if($bank_account_id > 0 && $payment_amount != 0){
        $reference = $batch_ref."-C";
        $sclp_msg = save_company_loan_payment($company_loan_id, $batch_ref, $reference,                                    
                                    $tran_date, $comments, $is_return,
                                    $bank_account_id, 
                                    $payment_amount, 0, 0);
        if($sclp_msg['success'] !== true){
            return $sclp_msg;
        }
        
        $payment_id = $sclp_msg['trans_no'];
    }
    
    if($interest_account > 0 && $payment_interest_amount != 0){
        $reference = $batch_ref."-I";
        $sclp_msg = save_company_loan_payment($company_loan_id, $batch_ref, $reference,                                
                                    $tran_date, $comments, $is_return,
                                    $interest_account, 
                                    0, $payment_interest_amount, 0);
        if($sclp_msg['success'] !== true){
            return $sclp_msg;
        }
        
        $payment_id = $sclp_msg['trans_no'];
    }
    
    if($late_interest_account > 0 && $late_interest_amount != 0){
        $reference = $batch_ref."-L";
        $sclp_msg = save_company_loan_payment($company_loan_id, $batch_ref, $reference,                                  
                                    $tran_date, $comments, $is_return,
                                    $late_interest_account, 
                                    0, 0, $late_interest_amount);
        if($sclp_msg['success'] !== true){
            return $sclp_msg;
        }
        
        $payment_id = $sclp_msg['trans_no'];
        
    }
    
    $uclips_msg = update_company_loan_payment_summary($company_loan_id, $tran_date);
    if($uclips_msg['success'] !== true){
        return $uclips_msg;
    }
    
    $Refs->save(ST_COMP_LOAN_REPAYMENT, $payment_id, $batch_ref);
    
    return $uclips_msg;
}

function create_company_loan_payment_gl_transactions($payment_id, $reference, $tran_date, $amount, $debit_account, $bank_account_id, $memo)
{
    $date_ = sql2date($tran_date);
//    add_gl_trans(ST_COMP_LOAN_REPAYMENT, $payment_id, $date_, $debit_account, 0, 0, $memo, 
//            $amount);

    $bank_gl_account = get_bank_gl_account($bank_account_id);
    
    add_bank_trans(ST_COMP_LOAN_REPAYMENT, $payment_id, $bank_account_id, $reference,
                $date_, -$amount, PT_MISC, $memo);
//    add_gl_trans(ST_COMP_LOAN_REPAYMENT, $payment_id, $date_, $bank_gl_account, 0, 0, $memo, 
//            -$amount);
}




function save_company_loan_payment($company_loan_id, $batch_ref, $reference,                                  
                                    $tran_date, $comments, $is_return,
                                    $bank_account_id, 
                                    $capital_amount, $interest_amount, $late_interest_amount)
{
    $payment_id = add_company_loan_payment($company_loan_id, $batch_ref, $reference, 
                                    $tran_date, $comments, $is_return,
                                    $bank_account_id, $capital_amount, $interest_amount, 
                                    $late_interest_amount);
    
//    $loan_installment = get_company_loan_installment($installment_id);
    $company_loan = get_company_loan($company_loan_id);
    if($capital_amount != 0){
        $debit_account = $company_loan['loan_account'];
        $amount = $capital_amount;
    }
    if($interest_amount != 0){
        $debit_account = $company_loan['interest_account'];
        $amount = $interest_amount;
    }
    if($late_interest_amount){
        $debit_account = $company_loan['late_interest_account'];
        $amount = $late_interest_amount;
    }
  
    $memo = $comments;
    create_company_loan_payment_gl_transactions($payment_id, $reference, $tran_date, $amount, $debit_account, $bank_account_id, $memo);

    $message = "Company Loan payment has been added.";                        
    return array("success" => true, "trans_no" => $payment_id, "message" =>$message);
    
}

function update_company_loan_payment_summary($company_loan_id, $tran_date)
{
    
    $installment_details = get_company_loan_paid_total_values($company_loan_id);
    
    $total_interest_paid = $installment_details['total_interest_paid'];
    $total_late_interest_paid = $installment_details['total_late_interest_paid'];
    $total_capital_paid = $installment_details['total_amount_paid'];
    
//    $n = update_company_loan_installment_paid_amounts($installment_id, $total_amount_paid, 
//                                                        $total_interest_paid, $total_late_interest_paid );
//    
//    if($n !== 1){
//        $message = "Could not update loan installment.";
//        return array("success" => false, "message" =>$message);
//    }
//    
//    
//    $l_installment_details = get_company_loan_installments_total_amounts($company_loan_id);
//    $loan_id = $l_installment_details['loan_id'];
//    $total_paid_amount = $l_installment_details['total_paid_amount'];
//    $total_interest = $l_installment_details['total_interest'];
//    $total_late_interest = $l_installment_details['total_late_interest'];

    $n2 = update_company_loan_paid_amounts($company_loan_id, $total_capital_paid, $total_interest_paid, $total_late_interest_paid, $tran_date);
    
    if($n2 !== 1){
        $message = "Could not update loan paid amounts.";
        return array("success" => false, "message" =>$message);
    }
    
    return array("success" => true, "message" =>"");
}
        
    
function get_company_loan_paid_total_values($loan_id)
{
    $sql = "SELECT SUM(payment_interest_amount) total_interest_paid,
                    SUM(late_interest_amount) total_late_interest_paid,
                    SUM(payment_amount) total_amount_paid  
                    
            FROM ".TB_PREF."company_loan_payments 
                
            WHERE loan_id=".db_escape($loan_id);
    
    $result = db_query($sql);
    return db_fetch($result);
}

function update_company_loan_installment_paid_amounts($installment_id, $total_amount_paid, 
                                                        $total_interest_paid, $total_late_interest_paid )
{    
    $loan_installment = get_company_loan_installment($installment_id);
    $balance_amount = $loan_installment['installment_amount'] - $loan_installment['paid_amount'];
    
    if($loan_installment['installment_amount'] < $total_amount_paid){
        return 0;
    }
    
    if($loan_installment['installment_amount'] > $total_amount_paid){    
        $paid = 0;
    }
    else{
        $paid = 1;
    }
    
    $sql = "UPDATE ".TB_PREF."company_loan_installments
            SET paid_amount=".db_escape($total_amount_paid).",
                total_payment_interest=".db_escape($total_interest_paid).",
                total_late_interest=".db_escape($total_late_interest_paid).",
                paid = ".db_escape($paid)." 
            WHERE id=".db_escape($installment_id)." AND paid <> 1";
    
    db_query($sql, "Could not update paid amount.");
    return db_num_affected_rows();
    
}


function add_company_loan_payment($company_loan_id, $batch_ref, $reference, 
                                    $tran_date, $comments, $is_return,
                                    $bank_account_id, $capital_amount, $interest_amount, 
                                    $late_interest_amount)
{
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
    
    $sql = "INSERT INTO ".TB_PREF."company_loan_payments
        
        (loan_id, tran_date, 
            batch_ref, reference, 
            comments, is_return,
            bank_account_id, 
            payment_amount, payment_interest_amount, late_interest_amount,             
            created_by, created_on)
        
    VALUES 
    
    (".db_escape($company_loan_id).", ".db_escape($tran_date).", 
        ".db_escape($batch_ref).", ".db_escape($reference).", 
        ".db_escape($comments).", ".db_escape($is_return).", 
        ".db_escape($bank_account_id).", 
        ".db_escape($capital_amount).", ".db_escape($interest_amount).", ".db_escape($late_interest_amount).", 
        ".db_escape($created_by).", ".db_escape($created_on).")";
    
    db_query($sql, "Could not add loan payments."); 
    
    return db_insert_id();
           
}


function save_company_loan_installment($installment_id, $tran_date,                                        
                                        $company_loan_id, $installment_amount, $interest_amount,
                                        $comments, $capital_paid,
                                        $interest_paid, $late_interest_paid, $paid)
                                  
{
    if($installment_id > 0){
        update_company_loan_installment($installment_id, $tran_date,                                        
                                        $installment_amount, $interest_amount,
                                        $comments, $capital_paid,
                                        $interest_paid, $late_interest_paid, $paid);
        return;
    }
    else{
        add_company_loan_installment($tran_date, $company_loan_id,                                       
                                        $installment_amount, $interest_amount,
                                        $comments, $capital_paid,
                                        $interest_paid, $late_interest_paid, $paid);
       
    }
    
//    if($pay == 0){
//        return;
//    }
//    
//    $paid_amounts = get_company_loan_total_paid_amounts($loan_id);
//    
//    if($is_return > 0 ){
//        $last_payment_date = null;
//    }
//    else{
//        $last_payment_date = $tran_date;
//    }
//    
//    update_company_loan_paid_amounts($loan_id, $paid_amounts['capital_paid'], 
//                                         $paid_amounts['interest_paid'], $last_payment_date);
//    
//    $loan = get_company_loan($loan_id);
//    if($loan['loan_amount'] <= $paid_amounts['capital_paid']){
//        update_company_loan_completed($loan_id);
//    }  
    
}


function add_company_loan_installment($tran_date, $company_loan_id,                                       
                                        $installment_amount, $interest_amount,
                                        $comments, $capital_paid,
                                        $interest_paid, $late_interest_paid, $paid)
{
    $created_by = $_SESSION['wa_current_user']->user;
    $created_on = date('Y-m-d H:i:s');
        
    $sql = "INSERT INTO ".TB_PREF."company_loan_installments 
                    ( loan_id, tran_date, 
                        installment_amount, interest_amount, 
                        comments, paid_amount, 
                        total_payment_interest, total_late_interest,
                        paid ,                    
                        created_by, created_on )                                                             

            VALUES (".db_escape($company_loan_id).", "
                    .db_escape(date2sql($tran_date)).","
                    .db_escape($installment_amount).", "
                    .db_escape($interest_amount).",  "
                    .db_escape($comments).","
                    .db_escape($capital_paid).", "
                    .db_escape($interest_paid).", " 
                    .db_escape($late_interest_paid).", "
                    .db_escape($paid).", "
                    .db_escape($created_by).", ".db_escape($created_on).")";              

	db_query($sql,"Company Loan installment could not be added");
                      
}

function update_company_loan_installment($installment_id, $tran_date,                                        
                                        $installment_amount, $interest_amount,
                                        $comments, $capital_paid,
                                        $interest_paid, $late_interest_paid, $paid)
{
    $updated_by = $_SESSION['wa_current_user']->user;
    $updated_on = date('Y-m-d H:i:s');
        
    $sql = "UPDATE ".TB_PREF."company_loan_installments  
        
            SET installment_amount =".db_escape($installment_amount).", 
                interest_amount =".db_escape($interest_amount).", 
                tran_date=".db_escape(date2sql($tran_date)).", 
                comments=".db_escape($comments).", 
                paid_amount=".db_escape($capital_paid).", 
                total_payment_interest=".db_escape($interest_paid).", 
                total_late_interest=".db_escape($late_interest_paid).", 
                paid=".db_escape($paid).", 
                updated_by=".db_escape($updated_by).", 
                updated_on=".db_escape($updated_on)." 
                        
            WHERE id =".db_escape($installment_id)." AND paid = 0";          
	
	db_query($sql,"Could not update_company_loan_installment");	
}


//function update_company_loan_paid_amounts($loan_id, $capital_paid, 
//                                         $interest_paid, $total_late_interest_paid, $last_payment_date = null)
//{
//    $sql = "UPDATE ".TB_PREF."company_loans
//            SET total_captal_paid=".db_escape($capital_paid).", 
//                total_interest_paid=".db_escape($interest_paid).", 
//                total_late_interest_paid=".db_escape($total_late_interest_paid);
//    
//    
//    if($last_payment_date){
//        $sql .= ", last_payment_date=".db_escape($last_payment_date);
//    }
//                    
//    $sql .= " WHERE id=".db_escape($loan_id);    
//        
//    db_query($sql);    
//}


function get_company_loan_installments($loan_id, $active_only = 0)
{
    $sql = "SELECT * 
            FROM ".TB_PREF."company_loan_installments                     
            WHERE  loan_id = ".db_escape($loan_id);
        if($active_only){
            $sql .= " AND paid = 0 ";
        }
        
        $sql .= " ORDER BY tran_date";                   
        
	//$sql .= " ORDER BY id";
        
    $result =  db_query($sql, "Company Loan installments could not be retrieved");    
    return $result;
}


function get_schedulled_installments_total($from_date, $to_date)
{
    $sql = "SELECT SUM(installment_amount) capital_total, SUM(interest_amount) interest_total  
            FROM ".TB_PREF."company_loan_installments                     
            WHERE  tran_date >= ".db_escape($from_date)." 
                AND tran_date <= ".db_escape($to_date);
                 
    $result =  db_query($sql, "get_schedulled_installments_total");    
    return db_fetch($result);
}


function get_company_loan_installment($id)
{
    $sql = "SELECT * 
            FROM ".TB_PREF."company_loan_installments                     
            WHERE id = ".db_escape($id);                   
        
	//$sql .= " ORDER BY id";
        
    $result =  db_query($sql, "Could not get_company_loan_payment");    
    return db_fetch($result);
}


function get_company_loan_total_paid_amounts($loan_id)
{
    $sql = "SELECT SUM(installment_amount) capital_paid, SUM(interest_amount) interest_paid  
            FROM  ".TB_PREF."company_loan_installments                 
            WHERE loan_id = ".db_escape($loan_id)." AND paid = 1";       
	
        
    $result =  db_query($sql, "Could not get_company_loan_total_paid_amounts");    
    return db_fetch($result);
}


//function get_credit_facilities_for_company_loan_installments($company_loan_id)
//{
//    $sql = "SELECT *
//                   
//            FROM ".TB_PREF."credit_facilities 
//                
//            WHERE id=".db_escape($company_loan_id);
//                 
//    
//    $result = db_query($sql);
//    return db_fetch($result);
//}


function get_company_loan_payments($loan_id)
{
    $sql = "SELECT * 
            FROM ".TB_PREF."loan_payments 
            WHERE installment_id=".db_escape($loan_id);
    
    return db_query($sql, "Could not get loan payments");
}



function delete_company_loan_installment($selected_id)
{
    $sql = "DELETE FROM ".TB_PREF."company_loan_installments 
            WHERE id=".db_escape($selected_id)." AND 
                paid_amount = 0";
        
    db_query($sql,"Could not delete_company_loan_installment");
}


function get_total_paid_and_total_interest_for_company_loan_installments($company_loan_id)
{
    $sql = "SELECT SUM(installment_amount) total_paid, SUM(interest_amount) total_interest
            FROM ".TB_PREF."company_loan_installments                
            WHERE loan_id=".db_escape($company_loan_id);
    
    $result = db_query($sql);
    return db_fetch($result); 
    
}

function view_company_loan_installments($loan_id)
{
    $sql = "SELECT * 
            FROM ".TB_PREF."company_loan_installments                     
            WHERE  loan_id = ".db_escape($loan_id)."  
            ORDER BY tran_date";                   
        
	//$sql .= " ORDER BY id";
        
    $result =  db_query($sql, "Company Loan installments could not be retrieved");    
    return $result;
}

function get_credit_facility_bank_account_record($credit_facility_id)
{
    $sql = "SELECT ba.bank_account_name,
                    ba.id bank_account_id 

            FROM ".TB_PREF."credit_facility_sublimits cfs
                INNER JOIN ".TB_PREF."bank_accounts ba ON
                    cfs.bank_account_id = ba.id
                
            WHERE cfs.credit_facility_id=".db_escape($credit_facility_id);
    
    $result = db_query($sql);
    return db_fetch($result);
    
}


function get_company_loan_installments_total_amounts($company_loan_id)
{
    $sql = "SELECT SUM(paid_amount) total_paid_amount,
                    SUM(total_payment_interest) total_interest,
                    SUM(total_late_interest) total_late_interest,
                    loan_id
        
            FROM ".TB_PREF."company_loan_installments
                
            WHERE loan_id=".db_escape($company_loan_id);
    
    $result = db_query($sql);
    return db_fetch($result);

}



function get_company_loan_payment_record($payment_id)
{
    $sql = "SELECT * 
            FROM ".TB_PREF."company_loan_payments 
            WHERE id=".db_escape($payment_id);
    
    $result = db_query($sql, "get_company_loan_payment_record");
    return db_fetch($result);
}


function get_company_loan_payments_by_batch_ref($batch_ref)
{
    $sql = "SELECT * 
            FROM ".TB_PREF."company_loan_payments 
            WHERE batch_ref=".db_escape($batch_ref);
    
    return db_query($sql, "get_company_loan_payments_by_batch_ref");

}

?>