<?php
class Reports_Loan_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);
		
	}
	
	//------------------------------------------------------------------------------------------------new 
	
	 function get_loan_installment_list_cash($wareh_id,$route_id,$payment_term,$srh_from_date,$srh_to_date)
   {
	   $this->db->select('c.cus_name');
	   $this->db->select('l.loan_ref');
	   $this->db->select('b.*');
	   $this->db->select('u.user_first_name');
	   $this->db->select('pt.payment_term_name');	   
	    $this->db->select('r.route_name');
       $this->db->from('loan_payment 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('loan l', 'l.loan_id = b.loan_id', 'left');
	   $this->db->join('routes r', 'r.route_id = b.route_id', 'left');
	   $this->db->join('user u', 'u.user_id = b.user_id', 'left');
	   $this->db->join('mstr_payment_terms pt', 'pt.id = l.loan_payment_term', 'left');
	   
	   
	   $this->db->where("b.pymnt_paying_by","Cash");
	   $this->db->where("b.pymnt_collected",1);
	   $this->db->where("b.pymnt_returned",0);	   
	   
	   if($wareh_id){	   
	   $this->db->where("b.warehouse_id",$wareh_id);//
	   }
	    if($route_id){	   
	   $this->db->where("b.route_id",$route_id);//
	   }
	    if($payment_term){	   
	   $this->db->where("l.loan_payment_term",$payment_term);//
	   }
	    if($srh_from_date){	   
	    $this->db->where("b.pymnt_date_time >=",$srh_from_date);
	   }
	   if($srh_to_date){	   
	    $this->db->where("b.pymnt_date_time <=",$srh_to_date);
	   }
	    $this->db->order_by("b.pymnt_date_time", "desc");
	   $query = $this->db->get();	  
     if($query->num_rows() >0)
     {
       return $query->result();
     }
     else
     {
       return false;
     }
   }
   
   
   
   
   
   //---------------------------------------------------------------------new
   
    function get_loan_installment_summary_cash($wareh_id,$route_id,$loan_payment_term,$srh_from_date,$srh_to_date)
   {
	   $this->db->select_sum('b.loan_received');
	   $this->db->select_sum('b.capital_received');
	    $this->db->select_sum('b.loan_interest_recived');
		$this->db->select_sum('b.panalty_received');
		$this->db->select_sum('b.due_received');
		$this->db->select_sum('b.excess_received');
		$this->db->select_sum('b.pymnt_amount');
       $this->db->from('loan_payment b');
	   $this->db->join('loan l','b.loan_id=l.loan_id','left');
	   $this->db->where("b.pymnt_paying_by","Cash");
	   $this->db->where("b.pymnt_collected",1);
	   $this->db->where("b.pymnt_returned",0);   
	  
	    if($route_id){	   
	   $this->db->where("b.route_id",$route_id);//
	   }
	    if($loan_payment_term){	   
	   $this->db->where("l.loan_payment_term",$loan_payment_term);//
	   }
	    if($srh_from_date){	   
	    $this->db->where("b.pymnt_date_time >=",$srh_from_date);
	   }
	   if($srh_to_date){	   
	    $this->db->where("b.pymnt_date_time <=",$srh_to_date);
	   }
	   
	  
	   $query = $this->db->get();	  
     if($query->num_rows() >0)
     {
       return $query->row_array();
     }
     else
     {
       return false;
     }
   }
   
   
   //--------------------------------------------------------
   
   function get_close_loan_list($wareh_id,$route_id,$loan_payment_term,$srh_from_date,$srh_to_date)
   {
	   $this->db->select('b.*,c.cus_name');
	   $this->db->select('mpt.payment_term_name');
	   $this->db->select('r.route_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->join('routes r', 'r.route_id = b.route_id', 'left');
	   $this->db->where("b.approval_status",1);
	   $this->db->where("b.is_closed",1);
	    
		if($srh_from_date){	   
	    $this->db->where("b.system_close_date >=",$srh_from_date);
	   }
	   if($srh_to_date){	   
	    $this->db->where("b.system_close_date <=",$srh_to_date);
	   }
	   if($wareh_id){	   
	   $this->db->where("b.warehouse_id",$wareh_id);//
	   }	   
	    if($route_id){	   
	   $this->db->where("b.route_id",$route_id);//
	   }	   
	     if($loan_payment_term){	   
	   $this->db->where("b.loan_payment_term",$loan_payment_term);//
	   }  
	   
	   $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_close_loan_summary($wareh_id,$route_id,$loan_payment_term,$srh_from_date,$srh_to_date)
   {
	   $this->db->select_sum('b.total_interest');
	   $this->db->select_sum('b.loan_tot_amount');
	   $this->db->select_sum('b.loan_payable_total_amount');
       $this->db->from('loan b');
	   $this->db->where("b.approval_status",1);

	   $this->db->where("b.is_closed",1);
	   if($srh_from_date){	   
	    $this->db->where("b.system_close_date >=",$srh_from_date);
	   }
	   if($srh_to_date){	   
	    $this->db->where("b.system_close_date <=",$srh_to_date);
	   }
	   if($wareh_id){	   
	   $this->db->where("b.warehouse_id",$wareh_id);//
	   }	   
	    if($route_id){	   
	   $this->db->where("b.route_id",$route_id);//
	   }	   
	     if($loan_payment_term){	   
	   $this->db->where("b.loan_payment_term",$loan_payment_term);//
	   }  
	   
	   $query = $this->db->get();
     	if($query->num_rows() >0)
    	 {
       return $query->row_array();
	   }
    	else
		{
       return false;
     	}	   
   }
   
   
    function get_close_loan_summary_panalty($wareh_id,$route_id,$loan_payment_term,$srh_from_date,$srh_to_date)
   {
	   $this->db->select_sum('lp.panalty_amount');
       $this->db->from('loan_panalty lp');
	   $this->db->join('loan b', 'lp.loan_id = b.loan_id', 'inner');
	   $this->db->where("lp.is_removed",0);
	   $this->db->where("b.is_closed",1);
	   
	    if($srh_from_date){	   
	    $this->db->where("b.system_close_date >=",$srh_from_date);
	   }
	   if($srh_to_date){	   
	    $this->db->where("b.system_close_date <=",$srh_to_date);
	   }
	   if($wareh_id){	   
	   $this->db->where("b.warehouse_id",$wareh_id);//
	   }	   
	    if($route_id){	   
	   $this->db->where("b.route_id",$route_id);//
	   }	   
	     if($loan_payment_term){	   
	   $this->db->where("b.loan_payment_term",$loan_payment_term);//
	   }  
	     
	   $query = $this->db->get();
     	if($query->num_rows() >0)
    	 {
       return $query->row_array();
	   }
    	else
		{
       return false;
     	}	   
   }
   
    function get_close_loan_summary_due($wareh_id,$route_id,$loan_payment_term,$srh_from_date,$srh_to_date)
   {
	   $this->db->select_sum('lp.due_amount');
       $this->db->from('loan_due_amount lp');
	   $this->db->join('loan b', 'lp.loan_id = b.loan_id', 'left');
	   $this->db->where("lp.is_removed",0);
	   $this->db->where("b.is_closed",1);
	   
	    if($srh_from_date){	   
	    $this->db->where("b.system_close_date >=",$srh_from_date);
	   }
	   if($srh_to_date){	   
	    $this->db->where("b.system_close_date <=",$srh_to_date);
	   }
	   if($wareh_id){	   
	   $this->db->where("b.warehouse_id",$wareh_id);//
	   }	   
	    if($route_id){	   
	   $this->db->where("b.route_id",$route_id);//
	   }	   
	     if($loan_payment_term){	   
	   $this->db->where("b.loan_payment_term",$loan_payment_term);//
	   }  
	     
	   $query = $this->db->get();
     	if($query->num_rows() >0)
    	 {
       return $query->row_array();
	   }
    	else
		{
       return false;
     	}	   
   }
   
   
   
   
   //---------------------------------------------------------------------new
   
    function get_close_loan_paid_summary($wareh_id,$route_id,$loan_payment_term,$srh_from_date,$srh_to_date)
   {
	   $this->db->select_sum('lp.loan_received');
	   $this->db->select_sum('lp.capital_received');
	    $this->db->select_sum('lp.loan_interest_recived');
		$this->db->select_sum('lp.panalty_received');
		$this->db->select_sum('lp.due_received');
		$this->db->select_sum('lp.excess_received');
		$this->db->select_sum('lp.pymnt_amount');
       $this->db->from('loan_payment lp');
	   $this->db->join('loan b', 'lp.loan_id = b.loan_id', 'inner');
	   $this->db->where("lp.pymnt_collected",1);
	   $this->db->where("lp.pymnt_returned",0); 
	   
	   if($srh_from_date){	   
	    $this->db->where("b.system_close_date >=",$srh_from_date);
	   }
	   if($srh_to_date){	   
	    $this->db->where("b.system_close_date <=",$srh_to_date);
	   }
	   if($wareh_id){	   
	   $this->db->where("b.warehouse_id",$wareh_id);//
	   }	   
	    if($route_id){	   
	   $this->db->where("b.route_id",$route_id);//
	   }	   
	     if($loan_payment_term){	   
	   $this->db->where("b.loan_payment_term",$loan_payment_term);//
	   }    
	  
	   
	   
	   
	   $query = $this->db->get();	  
     if($query->num_rows() >0)
     {
       return $query->row_array();
     }
     else
     {
       return false;
     }
   }
   
   
   //--------------------------------------------------------
   
   
   
   
   
   
}