<?php
class Loan_Payment_Received_Model extends CI_Model
{
    private $tableName = 'loan';
    function __construct()
    {
        /* Call the Model constructor */
        parent::__construct();
        $this->load->database();
        $this->load->helper('url');
        $this->load->library(array(
            'email'
        ));
    }
    function update_loan_issue_status($loan_id, $data)
    {
        $this->db->where('loan_id', $loan_id);
        return $this->db->update('loan', $data);
    }
    function get_loan_paid_total_by_loan_id($loan_id)
    {
        $this->db->select_sum('pymnt_amount');
        $this->db->from('loan_payment');
        $this->db->where('loan_payment.pymnt_returned', 0);
        $this->db->where('loan_payment.loan_id', $loan_id);
        $query = $this->db->get();
        return $query->row_array();
    }
    function save_loan_payment($data)
    {
        if ($data) {
            return $this->db->insert('loan_payment', $data);
        } else {
            return false;
        }
    }
    function getLoan_approved_issued($warehouse_id = '')
    {
        $this->db->select('b.*,c.cus_name');
        $this->db->select('mpt.payment_term_name');
        $this->db->from('loan b');
        $this->db->join('warehouses w', 'w.id = b.warehouse_id', 'left');
        $this->db->join('customer c', 'c.cus_id = b.cus_id', 'left');
        $this->db->join('mstr_payment_terms mpt', 'mpt.id = b.loan_payment_term', 'left');
        $this->db->where("b.approval_status", 1);
        $this->db->where("b.loan_issue_status", 1);
        if ($warehouse_id) {
            $this->db->where("b.warehouse_id", $warehouse_id); //
        }
        $this->db->order_by("b.loan_id", "desc");
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }
    function get_last_panalty_by_loan_id($loan_id)
    {
        $this->db->select('loan_panalty.payment_date,loan_panalty.panalty_amount');
        $this->db->from('loan_panalty');
        $this->db->where("loan_panalty.loan_id", $loan_id);
        $this->db->where("loan_panalty.is_removed", 0);
        $this->db->order_by("loan_panalty.id", "desc");
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->row_array();
        } else {
            return false;
        }
    }
    function getLoan_paid_list($loan_id)
    {
        $this->db->select('user.user_first_name');
        $this->db->select('loan_payment.*');
        $this->db->from('loan_payment');
        $this->db->join('user', 'user.user_id=loan_payment.user_id', 'left');
        $this->db->where('loan_payment.loan_id', $loan_id);
        $query = $this->db->get();
        return $query->result();
    }
    function get_total_realize_cheque_for_today_by_loan_id($loan_id, $today)
    {
        $this->db->select_sum('loan_received');
        $this->db->from('loan_payment');
        $this->db->where('loan_payment.loan_id', $loan_id);
        $this->db->where('loan_payment.pymnt_collected', 1);
        $this->db->where('loan_payment.pymnt_deposited', 1);
        //$this->db->where('loan_payment.pymnt_realize',1);
        $this->db->where('loan_payment.pymnt_paying_by', "Cheque");
        $this->db->where('loan_payment.pymnt_returned', 0);
        $this->db->where("loan_payment.pymnt_cheque_date <=", $today);
        $query = $this->db->get();
        return $query->row_array();
    }
    function get_total_cheque_for_today_by_loan_id($loan_id, $today)
    {
        $this->db->select_sum('loan_received');
        $this->db->from('loan_payment');
        $this->db->where('loan_payment.loan_id', $loan_id);
        $this->db->where('loan_payment.pymnt_collected', 1);
        $this->db->where('loan_payment.is_initial_cheque', 1);
        $this->db->where('loan_payment.pymnt_paying_by', "Cheque");
        $this->db->where("loan_payment.pymnt_cheque_date <=", $today);
        $query = $this->db->get();
        return $query->row_array();
    }
    function get_total_panalty_by_loan_id($loan_id)
    {
        $this->db->select_sum('panalty_amount');
        $this->db->from('loan_panalty');
        $this->db->where('loan_panalty.loan_id', $loan_id);
        $this->db->where('loan_panalty.is_removed', 0);
        $query = $this->db->get();
        return $query->row_array();
    }
    function get_total_due_by_loan_id($loan_id)
    {
        $this->db->select_sum('due_amount');
        $this->db->from('loan_due_amount');
        $this->db->where('loan_due_amount.loan_id', $loan_id);
        $this->db->where('loan_due_amount.is_removed', 0);
        $query = $this->db->get();
        return $query->row_array();
    }
    function get_total_paid_panalty_by_loan_id($loan_id)
    {
        $this->db->select_sum('panalty_received');
        $this->db->from('loan_payment');
        $this->db->where('loan_payment.loan_id', $loan_id);
        $this->db->where('loan_payment.pymnt_collected', 1);
        $this->db->where('loan_payment.pymnt_returned', 0);
        $query = $this->db->get();
        return $query->row_array();
    }
    function get_total_paid_due_by_loan_id($loan_id)
    {
        $this->db->select_sum('due_received');
        $this->db->from('loan_payment');
        $this->db->where('loan_payment.loan_id', $loan_id);
        $this->db->where('loan_payment.pymnt_collected', 1);
        $this->db->where('loan_payment.pymnt_returned', 0);
        $query = $this->db->get();
        return $query->row_array();
    }
    function get_total_paid_loan_by_loan_id($loan_id)
    {
        $this->db->select_sum('loan_received');
        $this->db->from('loan_payment');
        $this->db->where('loan_payment.loan_id', $loan_id);
        $this->db->where('loan_payment.pymnt_collected', 1);
        $this->db->where('loan_payment.pymnt_returned', 0);
        $query = $this->db->get();
        return $query->row_array();
    }
    function get_total_paid_capital_by_loan_id($loan_id)
    {
        $this->db->select_sum('capital_received');
        $this->db->from('loan_payment');
        $this->db->where('loan_payment.loan_id', $loan_id);
        $this->db->where('loan_payment.pymnt_collected', 1);
        $this->db->where('loan_payment.pymnt_returned', 0);
        $query = $this->db->get();
        return $query->row_array();
    }
    function get_total_paid_interest_by_loan_id($loan_id)
    {
        $this->db->select_sum('loan_interest_recived');
        $this->db->from('loan_payment');
        $this->db->where('loan_payment.loan_id', $loan_id);
        $this->db->where('loan_payment.pymnt_collected', 1);
        $this->db->where('loan_payment.pymnt_returned', 0);
        $query = $this->db->get();
        return $query->row_array();
    }
    function update_loan_close_status($loan_id, $data)
    {
        $this->db->where('loan_id', $loan_id);
        return $this->db->update('loan', $data);
    }
    function getLoan_approved_issued_cash_collect($warehouse_id = '', $route, $payment_t)
    {
        $this->db->select('b.*,c.cus_name,c.cus_mobile,c.cus_nic,c.location_latitude,c.location_long,c.location_status,c.cus_address');
        $this->db->select('mpt.payment_term_name');
        $this->db->from('loan b');
        $this->db->join('warehouses w', 'w.id = b.warehouse_id', 'left');
        $this->db->join('customer c', 'c.cus_id = b.cus_id', 'left');
        $this->db->join('mstr_payment_terms mpt', 'mpt.id = b.loan_payment_term', 'left');
        $this->db->where("b.approval_status", 1);
        $this->db->where("b.loan_issue_status", 1);
        $this->db->where("b.is_closed", 0);
        if ($warehouse_id) {
            $this->db->where("b.warehouse_id", $warehouse_id); //
        }
        if ($route) {
            $this->db->where("b.route_id", $route); //
        }
        if ($payment_t) {
            $this->db->where("b.loan_payment_term", $payment_t); //
        }
        $this->db->order_by("b.loan_id", "desc");
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }
    //----------------------------------------------------------------------------------
    //loan report 
    function get_loan_payment_total($loan_id)
    {
        $this->db->select_sum('pymnt_amount');
        $this->db->from('loan_payment');
        $this->db->where('loan_payment.loan_id', $loan_id);
        $this->db->where('loan_payment.pymnt_collected', 1);
        $this->db->where('loan_payment.pymnt_deposited', 1);
        $this->db->where('loan_payment.pymnt_returned', 0);
        $query = $this->db->get();
        return $query->row_array();
    }
    //-------------------------------------------------------------------------------
	
	function get_last_paid_loan_by_loan_id($loan_id)
    {
        $this->db->select('pymnt_amount');
        $this->db->from('loan_payment');
        $this->db->where('loan_payment.loan_id', $loan_id);
        $this->db->where('loan_payment.pymnt_collected', 1);
        $this->db->where('loan_payment.pymnt_deposited', 1);
        $this->db->where('loan_payment.pymnt_returned', 0);
		$this->db->limit(1);
		$this->db->order_by("loan_payment.pymnt_date_time", "desc");
		$this->db->order_by("loan_payment.paymnt_id", "desc");
		
        $query = $this->db->get();
		
		//print_r($query);
        return $query->row_array();
    }
	
	
	
}