<?php
class Loan_Model extends CI_Model
{
    private $tableName = 'user';
    function __construct()
    {
        /* Call the Model constructor */
        parent::__construct();
        $this->load->database();
        $this->load->helper('url');
        $this->load->library(array(
            'email'
        ));
    }
    function change_collected_status(&$data, $id = false)
    {
        if (!$id) {
            return $this->db->insert('loan_payment', $data);
        } else {
            $this->db->where('paymnt_id', $id);
            return $this->db->update('loan_payment', $data);
        }
    }
    public function delete_loan_assign_item($loan_id, $type = '')
    {
        $this->db->where('loan_id', $loan_id);
        $this->db->delete('loan_payment');
    }
    //get service suggestions
    function get_menu_items_suggestions($term)
    {
        $this->db->select('menu_item' . '.*');
        $this->db->order_by("item_name", "asc");
        $this->db->where("item_extra", '');
        $this->db->where("(item_name LIKE '%$term%' OR item_code LIKE '%$term%') ");
        $this->db->limit(10, 0);
        $query = $this->db->get('menu_item');
        return $query->result_array();
    }
    function get_menu_extra_items_suggestions($term)
    {
        $this->db->select('menu_item.*');
        $this->db->order_by("item_name", "asc");
        $this->db->where("item_extra", 'Extra');
        $this->db->where("(item_name LIKE '%$term%' OR item_code LIKE '%$term%') ");
        $this->db->limit(10, 0);
        $query = $this->db->get('menu_item');
        return $query->result_array();
    }
    function save_menu_assign_item($arr, $id = false)
    {
        if (!$id) {
            $return_value = $this->db->insert('loan_payment', $arr);
            return $return_value;
        }
    }
    function get_loan_detais_and_selected_all_menu_items($loan_id)
    {
        $this->db->select('w.*,b.*,bi.*,i.*,c.*');
        $this->db->from('loan_menu_items bi');
        $this->db->join('menu_item i', 'i.item_id = bi.item_id', 'left');
        $this->db->join('loan b', 'b.loan_id = bi.loan_id', 'left');
        $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->order_by("i.menu_id", "asc");
        $this->db->where("bi.loan_id", $loan_id); //("id !=",$id);
        $query = $this->db->get();
        return $query->result();
    }
    function get_loan_selected_all_menu_items($loan_id, $val = '', $extra_item = '')
    {
        $this->db->select('bi.*,i.*');
        $this->db->from('loan_menu_items bi');
        $this->db->join('menu_item i', 'i.item_id = bi.item_id', 'left');
        $this->db->order_by("bi.bk_itm_id", "asc");
        if ($extra_item == 'extra_item') {
            $this->db->where("bi.bkng_extra_item", 'Extra'); //("id !=",$id);
        } else {
            $this->db->where("bi.bkng_extra_item", '');
        }
        $this->db->where("bi.loan_id", $loan_id); //("id !=",$id);
        $query = $this->db->get();
        return $query->result();
    }
    //Sales save
    function save_loan_header(&$arr, $id = false)
    {
        if (!$id) {
            $this->db->insert('loan', $arr);
        } else {
            $this->db->where('loan_id', $id);
            return $this->db->update('loan', $arr);
        }
    }
    function checkAvalabilityDate($loan_id = '', $bkng_date = '', $warehouse_id = '')
    {
        $this->db->select('b.loan_id');

        $this->db->from('loan b');
        $error = false;
        $this->db->where("b.bkng_status", 'Confirmed');
        if ($loan_id) {
            $this->db->where("b.loan_id !=", $loan_id); //
        }
        if ($warehouse_id) {
            $this->db->where("b.warehouse_id", $warehouse_id); //
        }
        if ($bkng_date) {
            $this->db->where("b.bkng_date", $bkng_date); //
        }
        $this->db->order_by("b.bkng_time", "desc");
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return true;
        } else {
            return false;
        }
    }
    function checkAvalabilityTime($loan_id = '', $bkng_date = '', $bkng_time = '', $warehouse_id = '')
    {
        $this->db->select('b.*');
        $this->db->from('loan b');
        $error = false;
        $this->db->where("b.bkng_status", 'Confirmed'); //
        if ($loan_id) {
            $this->db->where("b.loan_id !=", $loan_id); //
        }
        if ($warehouse_id) {
            $this->db->where("b.warehouse_id", $warehouse_id); //
        }
        if ($bkng_date) {
            $this->db->where("b.bkng_date", $bkng_date); //
        }
        if ($bkng_time) {
            $this->db->where("b.bkng_time", $bkng_time); //
        }
        $this->db->order_by("b.bkng_time", "desc");
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return true;
        } else {
            return false;
        }
    }
    function getPaymentsForPrint($srh_warehouse_id = '', $srh_to_date = '', $srh_from_date = '',$user_id='',$route_id='')
    {
        $this->db->select('p.*,c.cus_name,b.*,u.user_first_name');
        $this->db->from('loan_payment p');
        $this->db->join('loan b', 'b.loan_id = p.loan_id', 'left');
        $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('user u', 'u.user_id = p.user_id', 'left');
        if ($srh_warehouse_id) {
            $this->db->where("b.warehouse_id", $srh_warehouse_id); //
        }
        if ($srh_to_date) {
            $this->db->where("p.pymnt_date_time <=", $srh_to_date); //("id !=",$id);
        }
        if ($srh_from_date) {
            $this->db->where("p.pymnt_date_time >=", $srh_from_date); //("id !=",$id);
        }
		
		if ($user_id) {
            $this->db->where("p.user_id",$user_id); //("id !=",$id);
        }
		
		if ($route_id) {
            $this->db->where("p.route_id",$route_id); //("id !=",$id);
        }
		
		
		
        $this->db->order_by("p.paymnt_id", "desc");
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }
	
	    function getPaymentsForPrint_summary($srh_warehouse_id = '', $srh_to_date = '', $srh_from_date = '',$user_id='',$route_id='')
    {
        $this->db->select_sum('p.pymnt_amount');
		$this->db->select_sum('p.loan_received');
		$this->db->select_sum('p.capital_received');
		$this->db->select_sum('p.loan_interest_recived');
		$this->db->select_sum('p.panalty_received');
		$this->db->select_sum('p.due_received');
        $this->db->from('loan_payment p');
        if ($srh_warehouse_id) {
            $this->db->where("p.warehouse_id", $srh_warehouse_id); //
        }
        if ($srh_to_date) {
            $this->db->where("p.pymnt_date_time <=", $srh_to_date); //("id !=",$id);
        }
        if ($srh_from_date) {
            $this->db->where("p.pymnt_date_time >=", $srh_from_date); //("id !=",$id);
        }		
		if ($user_id) {
            $this->db->where("p.user_id",$user_id); //("id !=",$id);
        }
		
		if ($route_id) {
            $this->db->where("p.route_id",$route_id); //("id !=",$id);
        }		
		$this->db->where("p.pymnt_paying_by","Cash");
		$this->db->where("p.pymnt_collected",1);
		$this->db->where("p.pymnt_deposited",1);
		$this->db->where("p.pymnt_returned",0);       
        $query = $this->db->get();
       	return $query->row_array();
    }
	
	
	
    function getLoanForPrint($srh_warehouse_id = '', $srh_to_date = '', $srh_from_date = '')
    {
        $this->db->select('b.*,c.cus_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');
        if ($srh_warehouse_id) {
            $this->db->where("b.warehouse_id", $srh_warehouse_id); //
        }
        if ($srh_to_date) {
            $this->db->where("b.bkng_date <=", $srh_to_date); //("id !=",$id);
        }
        if ($srh_from_date) {
            $this->db->where("b.bkng_date >=", $srh_from_date); //("id !=",$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 getLoan($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", 0);
        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 getLoan_by_customer($cus_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');
        if ($cus_id) {
            $this->db->where("b.cus_id", $cus_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 getLoan_approved($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);
        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_loan_payments_by_loan_id($loan_id)
    {
        $this->db->select('bp.*,u.user_first_name,up.user_group_name,b.bank_name,b.bank_code');
        $this->db->from('loan_payment bp');
        $this->db->join('user u', 'bp.user_id = u.user_id', 'left');
        $this->db->join('user_group up', 'u.group_id = up.user_group_id', 'left');
        $this->db->join('mstr_bank b', 'b.bank_id = bp.bank_id', 'left');
        $this->db->order_by("bp.pymnt_date_time", "desc");
        $this->db->where("bp.loan_id", $loan_id); //("id !=",$id);
        $query = $this->db->get();
        return $query->result();
    }
    //Sales get toatal paid 
    function get_total_paid_by_loan_id($loan_id)
    {
        $this->db->select('lp.*');
        $this->db->from('loan_payment lp');
        $this->db->where('lp.loan_id', $loan_id);
        $query    = $this->db->get();
        $des      = '';
        $des      = $query->result();
        //print_r($des);
        $tot_paid = 0;
        foreach ($des as $row) {
            if ($row->pymnt_paying_by == 'Cheque') {
                if ($row->pymnt_deposited == 1 && $row->pymnt_returned == 0) {
                    $tot_paid += $row->pymnt_amount;
                }
            }
            if ($row->pymnt_paying_by == 'Cash') {
                if ($row->pymnt_collected == 1) {
                    $tot_paid += $row->pymnt_amount;
                }
            }
        }
        return $tot_paid;
    }
    function save_loan_payments(&$data, $id = false)
    {
        if (!$id) {
            return $this->db->insert('loan_payment', $data);
        } else {
            return false;
        }
    }
    //get loan details by id
    function get_loan_details_by_id($loan_id)
    {
        $this->db->select('l.*, u.user_first_name,c.cus_name');
		 $this->db->select('gc.cus_name as garanter_name');
		 $this->db->select('mpt.payment_term_name');
        $this->db->from('loan l');
        $this->db->join('user u', 'u.user_id = l.user_id', 'left');
        $this->db->join('customer c', 'l.cus_id=c.cus_id', 'left');
		$this->db->join('customer gc', 'l.g_cus_id=gc.cus_id', 'left');
		$this->db->join('mstr_payment_terms mpt', 'l.loan_payment_term=mpt.id', 'left');
        $this->db->where('l.loan_id', $loan_id);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            return false;
        }
    }
    public function gen_ref_number_loan($column_name, $table_name, $type_code, $whecol = '', $wheval = '')
    {
        $this->db->select_max($table_name . '.war_b_id');
        if ($whecol) {
            $this->db->where($whecol, $wheval);
        }
        $query = $this->db->get($table_name);
        if ($query->num_rows() > 0) {
            $g = $query->result();
            $u = $this->set_ref_no($g[0]->$column_name, $type_code);
            return $u;
        } else {
            return false;
        }
    }
    function set_ref_no($f, $t)
    {
        $w = '';
        $d = date('Y/m/');
        if ($t) {
            $w = $t;
        }
        $w = $w . sprintf("%03d", $f + 1);
        return $w;
    }
    function get_loan_payments_term()
    {
        $this->db->select('mstr_payment_terms.*');
        $this->db->from('mstr_payment_terms');
        $this->db->order_by("mstr_payment_terms.id", "asc");
        $query = $this->db->get();
        return $query->result_array();
    }
    function get_loan_payments_interest()
    {
        $this->db->select('mstr_interest_rate.*');
        $this->db->from('mstr_interest_rate');
        $this->db->where('mstr_interest_rate.status', 1);
        $this->db->order_by("mstr_interest_rate.interest_rate_value", "asc");
        $query = $this->db->get();
        return $query->result_array();
    }
    function get_loan_payments_interest_by_p_term_id($id)
    {
        $this->db->select('mstr_interest_rate.*');
        $this->db->from('mstr_interest_rate');
        $this->db->where('mstr_interest_rate.status', 1);
        $this->db->where('mstr_interest_rate.preferd_paymet_type', $id);
        $this->db->order_by("mstr_interest_rate.interest_rate_value", "asc");
        $query = $this->db->get();
        $data  = array();
        //$data[0] = 'SELECT'; 
        foreach ($query->result() as $row) {
            $data[$row->interest_rate_value] = $row->interest_rate_value;
        }
        return ($data);
    }
    function get_loan_payments_duration()
    {
        $this->db->select('mstr_duration.*');
        $this->db->from('mstr_duration');
        $this->db->where('mstr_duration.status', 1);
        $this->db->order_by("mstr_duration.duration_value", "asc");
        $query = $this->db->get();
        return $query->result_array();
    }
    function get_loan_details_by_loan_id($loan_id)
    {
        $this->db->select('loan.*');
        $this->db->select('c.cus_name');
        $this->db->from('loan');
        $this->db->join('customer c', 'c.cus_id = loan.cus_id', 'left');
        $this->db->where('loan.loan_id', $loan_id);
        $query = $this->db->get();
        return $query->row_array();
    }
    function get_total_issued_by_loan_id($loan_id)
    {
        $this->db->select_sum('issue_amount');
        $this->db->from('loan_issue');
        $this->db->where('loan_id', $loan_id);
        $this->db->where('is_return', 0);
        $query = $this->db->get();
        return $query->row_array();
    }
	
	//new update in 2019-01-17---------------------------------------------------------------------------------
	function loan_payment_summary_by_loan_id($loan_id)
    {		
		$this->db->select_sum('pymnt_amount');
        $this->db->select_sum('capital_received');
		$this->db->select_sum('loan_interest_recived');
		$this->db->select_sum('loan_received');
		$this->db->select_sum('due_received');
		$this->db->select_sum('panalty_received');
		$this->db->select_sum('excess_received');
        $this->db->from('loan_payment');
        $this->db->where('loan_id', $loan_id);
        $this->db->where('pymnt_returned', 0);
		$this->db->where('pymnt_deposited', 1);		
        $query = $this->db->get();
        return $query->row_array();
    }
	
	   function getLoan_approved_active($warehouse_id,$route,$payment_t,$start,$length,$search_key_val)
   {
	   $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');	   
	 
	 	$approval_status_con="b.approval_status =1";
		$is_closed_con=" AND b.is_closed =0";
		
		$loan_payment_term_con="";	
		$route_id_con="";
		$warehouse_id_con="";	
	 	
		if($warehouse_id){
		$warehouse_id_con=" AND b.warehouse_id =".$warehouse_id." ";			
		}		
		if($route){
		$route_id_con=" AND b.route_id =".$route." ";	
		}		
		if($payment_t){
		$loan_payment_term_con=" AND b.loan_payment_term =".$payment_t." ";	
		}		
		$where_condition=$approval_status_con.$is_closed_con.$warehouse_id_con.$route_id_con.$loan_payment_term_con;
		
		if($search_key_val)
		{
			$this->db->where($where_condition.' AND c.cus_code LIKE \'%'.$search_key_val.'%\'');
            $this->db->or_where($where_condition.' AND c.cus_name LIKE \'%'.$search_key_val.'%\'');
			$this->db->or_where($where_condition.' AND c.cus_nic LIKE \'%'.$search_key_val.'%\'');
			$this->db->or_where($where_condition.' AND c.cus_mobile LIKE \'%'.$search_key_val.'%\'');
			$this->db->or_where($where_condition.' AND b.loan_ref LIKE \'%'.$search_key_val.'%\'');				
		}
		else
		{
			$this->db->where($where_condition);
		}
		$this->db->order_by("b.loan_id", "desc");
       if($start!='' && $length!='')
	   {
        $this->db->limit($length,$start);
       	$query = $this->db->get(); 
      	return $query->result();     		
	   }
		else
		{
			$query = $this->db->get(); 
			return  $query->num_rows();			
		}
		
     
   }
   
   function getLoan_closed($wareh_id,$start,$length,$search_key_val)
   {
	   $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');		 
	  
	    if($search_key_val)
		{			
			if($wareh_id){
			$this->db->where('b.is_closed =1 AND b.warehouse_id ='.$wareh_id.' AND c.cus_code LIKE \'%'.$search_key_val.'%\'');
            $this->db->or_where('b.is_closed =1 AND b.warehouse_id ='.$wareh_id.' AND c.cus_name LIKE \'%'.$search_key_val.'%\'');
			$this->db->or_where('b.is_closed =1 AND b.warehouse_id ='.$wareh_id.' AND c.cus_nic LIKE \'%'.$search_key_val.'%\'');
			$this->db->or_where('b.is_closed =1 AND b.warehouse_id ='.$wareh_id.' AND c.cus_mobile LIKE \'%'.$search_key_val.'%\'');
			$this->db->or_where('b.is_closed =1 AND b.warehouse_id ='.$wareh_id.' AND b.loan_ref LIKE \'%'.$search_key_val.'%\'');				
			}
			else{
			$this->db->where('b.is_closed =1 AND c.cus_code LIKE \'%'.$search_key_val.'%\'');
            $this->db->or_where('b.is_closed =1 AND c.cus_name LIKE \'%'.$search_key_val.'%\'');
			$this->db->or_where('b.is_closed =1 AND c.cus_nic LIKE \'%'.$search_key_val.'%\'');
			$this->db->or_where('b.is_closed =1 AND c.cus_mobile LIKE \'%'.$search_key_val.'%\'');
			$this->db->or_where('b.is_closed =1 AND b.loan_ref LIKE \'%'.$search_key_val.'%\'');
			}
		}
		else
		{
			$this->db->where("b.is_closed",1);
	  		if($wareh_id)
			{	   
	   		$this->db->where("b.warehouse_id",$wareh_id);//
	   		}		
		}
	   
       $this->db->order_by("b.system_close_date", "desc");
       if($start!='' && $length!='')
	   {
        $this->db->limit($length,$start);
       	$query = $this->db->get(); 
      	return $query->result();     		
	   }
		else
		{
			$query = $this->db->get(); 
			return  $query->num_rows();			
		}
	  
   }
	
}