<?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');
	    $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(); 		
	}
	
	//-------------------------------------------------------------------------------
	
   
   
   
}