<?php
class Mutual_fund_model extends CI_Model{
	
	public function search($search, $filters, $rows = 0, $limit_from = 0, $sort = '', $order = 'asc'){
		$start_date = $filters['start_date'];
		$end_date   = $filters['end_date'];
		$emp_id     = $this->session->userdata('emp_id');
		$category   = $this->session->userdata('emp_category');
		$role_id    = $this->session->userdata('emp_role');

		if(!$sort){
			$sort = "mutual_fund.created_date"; 
		}
		$this->db->select("mutual_fund.mf_id,customers.cust_name,customers.cust_mobile,mutual_fund.mf_status,investment_type,entry_date,people.first_name as rm_name,fam_name,mf_login_value,mutual_fund_log.current_control as current_control,mf_investment.login_status,own.first_name as owner");
		$this->db->from('mutual_fund');
		$this->db->join('mutual_fund_log', 'mutual_fund_log.cust_id = mutual_fund.cust_id');
		$this->db->join('customers', 'customers.cust_id = mutual_fund_log.cust_id');
		$this->db->join('mf_investment', 'mf_investment.inves_mf_id = mutual_fund_log.mf_id','left');
		$this->db->join('employees','employees.id = mf_investment.rm_name','left');
		$this->db->join('people' ,'people.person_id = employees.person_id','left');
		$this->db->join('people own' ,'own.person_id = mutual_fund_log.created_by','left');
		$this->db->join('mf_family', 'mf_investment.investor = mf_family.mf_family_id','left');

		$this->db->group_start();
			$this->db->like('people.first_name', $search);
			$this->db->or_like('customers.cust_name', $search);
			$this->db->or_like('fam_name', $search);			
			$this->db->or_like('customers.cust_mobile', $search); 
			$this->db->or_like('investment_type', $search); 
			$this->db->or_like('mf_login_value', $search); 
			$this->db->or_like('entry_date', $search);
			$this->db->or_like('own.first_name', $search); 
			$this->db->or_like('mf_status', $search);
		$this->db->group_end();
		$this->db->group_start();
          $this->db->where("mutual_fund_log.created_by",$emp_id)->or_where("mutual_fund_log.current_control", $emp_id)->or_where("mutual_fund_log.current_control", 'MF');
	  $this->db->where("mutual_fund_log.status",1);
        $this->db->group_end();
		$this->db->group_by('mutual_fund_log.mf_id');
		$this->db->order_by($sort, $order);
		if($rows > 0){
			$this->db->limit($rows, $limit_from);
		}
		return $this->db->get();
	}
	
	public function get_found_rows($search, $filters){
		return $this->search($search, $filters)->num_rows();
	}
	
	public function get_info($mf_id){
		$this->db->from('mutual_fund');
		$this->db->join('customers', 'customers.cust_id = mutual_fund.cust_id');
		$this->db->where('mutual_fund.mf_id', $mf_id);
		return $this->db->get()->row();
	}
	
	public function get_family_list($mf_id){
		$this->db->from('mf_family');
		$this->db->where('mf_id',$mf_id);
		$this->db->where('deleted',0);
		return $this->db->get()->result();
	}
	
	public function get_bank_info(){
		$this->db->from('banks');
		$this->db->where('status', 1);
		return $this->db->get()->result();
	}
	
	public function get_rm_name(){
		$this->db->from('employees');
		$this->db->join('people', 'people.person_id = employees.person_id');
		$this->db->where('role', 5);
		return $this->db->get()->result();
	}
	
	public function get_distributer(){
		$this->db->from('vendor');
		$this->db->where('category_id', 4);
		return $this->db->get()->result();
	}
	
	public function get_distributer_code(){
		$this->db->from('code');
		$this->db->where('category_id', 4);
		return $this->db->get()->result();
	}
	
	public function get_amc(){
		$this->db->from('amc');
		$this->db->where('status', 1);
		return $this->db->get()->result();
	}
	
	public function get_scheme_info(){
		$this->db->from('fund_name');
		$this->db->where('status', 1);
		return $this->db->get()->result();
	}
	
	public function mf_log($mf_id){		
		$this->db->from('mutual_fund_log');
		$this->db->join('category', 'category.cat_id = mutual_fund_log.category');
		$this->db->join('vendor', 'vendor.vendor_id = mutual_fund_log.company');
		$this->db->join('employees', 'employees.person_id = mutual_fund_log.created_by');
		$this->db->join('people', 'people.person_id = employees.person_id');
		$this->db->where('mutual_fund_log.mf_id',$mf_id);
		$this->db->where('mutual_fund_log.status',1);
		return $this->db->get()->result();
	}
	
	public function lead_update_customer($customer_data, $cust_id){
		$this->db->where('cust_id', $cust_id);
		return $this->db->update('customers', $customer_data);
	}
	
	public function lead_save_family($family_data,$mf_id,$mf_family_id){
		if($mf_family_id === "-1"){
			$this->db->insert('mf_family', $family_data);
			$mf_family_id = $this->db->insert_id();
		}else{
			$this->db->where('mf_family_id', $mf_family_id);
			$this->db->update('mf_family', $family_data);
		}     
		return $this->get_family_list($mf_id);
	}
	public function get_family_person($mf_family_id){
		$this->db->from('mf_family');
		$this->db->where('mf_family_id',$mf_family_id);
		$this->db->where('deleted',0);
		return $this->db->get()->result();
	}
	public function get_company_code($distributer){
		$this->db->from('code');
		$this->db->where('vendorcompanyname',$distributer);
		$this->db->where('status',1);
		return $this->db->get()->result();
	}
	public function remove_family_person($mf_family_id,$family_data){    
		$this->db->where('mf_family_id',$mf_family_id);
		return $this->db->update('mf_family', $family_data);
	}
	public function is_bank_exist($bk_mf_id,$bank_name,$bank_acc_no){
		$this->db->from('mf_bank');
		$this->db->where('bk_mf_id',$bk_mf_id);
		$this->db->where('bank_name',$bank_name);
		$this->db->where('bank_acc_no',$bank_acc_no);
		$this->db->where('status',1);
		return $this->db->get();
	}
	public function save_bank($bank_data){
		$bk_mf_id    = $bank_data['bk_mf_id'];
		$bank_name   = $bank_data['bank_name'];
		$bank_acc_no = $bank_data['bank_acc_no'];
		$bank_info   = $this->is_bank_exist($bk_mf_id,$bank_name,$bank_acc_no);
		if((int)$bank_info->num_rows() === 0){
			$bank_data['created_by']   = $this->session->userdata('emp_id');
			$bank_data['created_date'] = date('Y-m-d h:i:s');	
			return $this->db->insert('mf_bank', $bank_data);
		}else{
			$bank_rslt  = $bank_info->row();
			$mf_bank_id = $bank_rslt->mf_bank_id;
			
			$bank_data['updated_by']   = $this->session->userdata('emp_id');
			$bank_data['updated_date'] = date('Y-m-d h:i:s');
			$this->db->where('mf_bank_id', $mf_bank_id);
			return $this->db->update('mf_bank', $bank_data);
		}
	}
	public function get_bank_list($bk_mf_id){
		$this->db->select("ospos_mf_bank.mf_bank_id,ospos_mf_bank.bank_name as bank_id,ospos_banks.bank_name,ospos_mf_bank.bank_branch,ospos_mf_bank.bank_acc_no,ospos_mf_bank.bank_ifsc,ospos_mf_bank.bk_mf_id");
		$this->db->from('mf_bank');
		$this->db->join('banks', 'banks.bank_id = mf_bank.bank_name');
		$this->db->where('bk_mf_id',$bk_mf_id);
		$this->db->where('mf_bank.status',1);
		return $this->db->get()->result();
	}
	public function get_bank_by_id($mf_bank_id){
		$this->db->from('mf_bank');
		$this->db->where('mf_bank.status',1);
		$this->db->where('mf_bank_id',$mf_bank_id);
		return $this->db->get()->result();
	}
	public function delete_bank($bank_data,$mf_bank_id){    
		$this->db->where('mf_bank_id',$mf_bank_id);
		return $this->db->update('mf_bank', $bank_data);
	}
	public function get_fund_by_id($amc){
		$this->db->from('fund_name');		
		$this->db->where('fund_name.amc',$amc);
		$this->db->where('status',1);
		return $this->db->get()->result();
	}
	public function save_investment($investment_data,$investment_id){
		if($investment_id === "-1"){
			$investment_data['created_by']   = $this->session->userdata('emp_id');
			$investment_data['created_date'] = date('Y-m-d h:i:s');	
			return $this->db->insert('mf_investment', $investment_data);
		}else{
			$investment_data['updated_by']   = $this->session->userdata('emp_id');
			$investment_data['updated_date'] = date('Y-m-d h:i:s');
			$this->db->where('investment_id', $investment_id);
			return $this->db->update('mf_investment', $investment_data);
		}
	}
	public function get_investment($inves_mf_id){
		$this->db->select("investment_id,inves_mf_id,entry_date,people.first_name as rm_name,vendor.vendorcompanyname,code.company_code,amc.amc_name,fund_name.fund_name,fund_class,fund_type,investment_type,sip_day,amount,purpose,policy_no,inves.fam_name as investor_name ,nomi.fam_name as nominee_name,guard.fam_name as guardian_name,mf_bank.bank_acc_no,cheque_no,login_status,folio_no,sip_status,lead_id");
		$this->db->from('mf_investment');
		$this->db->join('employees','employees.id = mf_investment.rm_name');
		$this->db->join('people', 'people.person_id = employees.person_id');
		$this->db->join('vendor', 'vendor.vendor_id = mf_investment.distributer');
		$this->db->join('code', 'code.code_id = mf_investment.distributer_code');
		$this->db->join('amc', 'amc.amc_id = mf_investment.amc');
		$this->db->join('fund_name', 'fund_name.fund_name_id = mf_investment.scheme_name');
		$this->db->join('mf_family inves' ,'inves.mf_family_id = mf_investment.investor','left');
		$this->db->join('mf_family nomi' ,'nomi.mf_family_id = mf_investment.nominee','left');
		$this->db->join('mf_family guard' ,'guard.mf_family_id = mf_investment.guardian','left');
		$this->db->join('mf_bank', 'mf_bank.mf_bank_id = mf_investment.investor_bank');
		$this->db->where('inves_mf_id',$inves_mf_id);
		$this->db->where('mf_investment.status',1);
		return $this->db->get()->result();
	}
	public function get_investmen_by_id($investment_id){
		$this->db->from('mf_investment');
		$this->db->where('investment_id',$investment_id);
		$this->db->where('mf_investment.status',1);
		return $this->db->get()->result();
	}
	public function delete_investment($investment_data,$investment_id){    
		$this->db->where('investment_id',$investment_id);
		return $this->db->update('mf_investment', $investment_data);
	}
	public function is_ewealth_exist($inves_mf_id,$ew_investor){
		$this->db->from('mf_ewealth');
		$this->db->where('inves_mf_id',$inves_mf_id);
		$this->db->where('ew_investor',$ew_investor);
		$this->db->where('mf_ewealth.status',1);
		return $this->db->get();
	}
	public function save_ewealth($inves_mf_id,$ew_investor){
		$ewealth_data = array(
			'inves_mf_id'  => $inves_mf_id,
			'ew_investor'  => $ew_investor,
			'created_by'   => $this->session->userdata('emp_id'),
			'created_date' => date('Y-m-d h:i:s'),
		);
		$ewealth_info =  $this->is_ewealth_exist($inves_mf_id,$ew_investor);
		if((int)$ewealth_info->num_rows() === 0){
			return $this->db->insert('mf_ewealth', $ewealth_data);
		}
		return true;
	}
	public function update_ewealth($ewealth_data){
		$inves_mf_id = $ewealth_data['inves_mf_id'];
		$ew_investor = $ewealth_data['ew_investor'];	
		
		$this->update_ewealth_log($ewealth_data);
		
		$ewealth_data['updated_by']   = $this->session->userdata('emp_id');
		$ewealth_data['updated_date'] = date('Y-m-d h:i:s');
		$this->db->where('inves_mf_id', $inves_mf_id);
		$this->db->where('ew_investor', $ew_investor);
		return $this->db->update('mf_ewealth', $ewealth_data);
		
	}
	public function update_ewealth_log($ewealth_data){
		$ewealth_data['created_by']   = $this->session->userdata('emp_id');
		$ewealth_data['created_date'] = date('Y-m-d h:i:s');	
		return $this->db->insert('mf_ewealth_log', $ewealth_data);
	}
	public function get_ewealth($inves_mf_id){
		$this->db->from('mf_ewealth');
		$this->db->join('mf_family' ,'mf_family.mf_family_id = mf_ewealth.ew_investor','left');
		$this->db->where('inves_mf_id',$inves_mf_id);
		$this->db->where('mf_ewealth.status',1);
		return $this->db->get()->result();
	}
	public function get_ewealth_log($inves_mf_id){
		$this->db->select("*,mf_ewealth_log.created_date as created_on");
		$this->db->from('mf_ewealth_log');
		$this->db->join('mf_family' ,'mf_family.mf_family_id = mf_ewealth_log.ew_investor','left');
		$this->db->where('inves_mf_id',$inves_mf_id);
		$this->db->where('mf_ewealth_log.status',1);
		return $this->db->get()->result();
	}
	public function get_ewealth_by_id($ewealth_id){
		$this->db->from('mf_ewealth');
		$this->db->where('ewealth_id',$ewealth_id);
		$this->db->where('mf_ewealth.status',1);
		return $this->db->get()->result();
	}
	public function upd_completed($completed_data,$lead_id){
		$sts = $completed_data['mf_lead_status'];
		$lead_sts = "MF MIS";
		if($sts === "1"){
			$lead_sts = "Completed";
		}
		$this->db->where('lead_id', $lead_id);
		$this->db->update('lead_type_info', array("current_control"=>$lead_sts));
		
		$this->db->where('lead_id', $lead_id);
		return $this->db->update('mutual_fund_log', $completed_data);
	}
	public function update_lead_status($lead_id,$mf_id){
		$this->db->select("IFNULL(count(*),0) as pending_count");
		$this->db->from('mutual_fund_log');
		$this->db->where('mf_id',$mf_id);
		$this->db->where('mf_lead_status',0);
		$lead_info = $this->db->get()->result();		
		if((int)$lead_info[0]->pending_count === 0){
			$this->db->where('mf_id', $mf_id);
			return $this->db->update('mutual_fund', array('mf_status'=>"Completed"));
		}else{
			$this->db->where('mf_id', $mf_id);
			return $this->db->update('mutual_fund', array('mf_status'=>"Pending"));
		}
		
	}
}
?>