<?php
class open_leads_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 = "lead_type_info.ncd"; 
    }

    if($role_id === "6"){
      $team = $this->get_team_info($emp_id);
      $team_rslt = explode(",",$team->teammembers);
      $team_rslt[] = $emp_id;
    }else
    if($role_id === "4"){
      $area_info = $this->get_area_info($emp_id);
      $rslt = "";
      foreach ($area_info as $key => $value){
        $team = $this->get_team_info($value->id);
        if($rslt !== ""){
          $rslt .= ",$team->teammembers,$value->id";
        }else{
          $rslt .= "$team->teammembers,$value->id";
        }     
      }
      $team_rslt = explode(",",$rslt);
    }else
    if($role_id === "9"){
      $area_info = $this->get_area_info($emp_id);
      $rslt = "";
      foreach ($area_info as $key => $value){
        if($rslt !== ""){
          $rslt .= ",$value->id";
        }else{
          $rslt .= "$value->id";
        }     
      }
      $team_rslt = explode(",",$rslt);
    }

    $this->db->select('lead_type_info.lead_id as leads_id,customers.cust_name,customers.cust_mobile,customers.cust_address,lead_type.lead_type,lead_status.statusname,prospect_level.prospect_name,lead_type_info.ncd,lead_type_info.remarks,lead_type_info.created_by,lead_type_info.expected_login,lead_type_info.current_control,lead_type_info.created_date,category.cat_name');
    $this->db->from('lead_type_info');
    $this->db->join('leads', 'leads.lead_id = lead_type_info.lead_id');
    $this->db->join('customers', 'customers.cust_id = leads.cust_id');
    $this->db->join('lead_type', 'lead_type.lead_type_id = lead_type_info.lead_type');
    $this->db->join('lead_status', 'lead_status.status_id = lead_type_info.lead_status');
    $this->db->join('prospect_level', 'prospect_level.prospect_id = lead_type_info.prospect_level');
    $this->db->join('category', 'category.cat_id = lead_type_info.category');

    $this->db->group_start();
      $this->db->like('customers.cust_name', $search);
      $this->db->or_like('customers.cust_mobile', $search);      
      $this->db->or_like('lead_type.lead_type', $search);
      $this->db->or_like('lead_status.statusname', $search);
      $this->db->or_like('prospect_level.prospect_name', $search);
      $this->db->or_like('category.cat_name', $search);
      if(strpos($search, '-') !== false){
        $this->db->or_like('lead_type_info.ncd', date('Y-m-d',strtotime($search))); 
        $this->db->or_like('lead_type_info.created_date', date('Y-m-d',strtotime($search)));
      }
    $this->db->group_end();
    $cat = array();
      if($filters['General_Insurance'] != FALSE){
        $cat[] = 1;
      }
      if($filters['Portfolio'] != FALSE){
        $cat[] = 2;
      }
      if($filters['Health_Insurance'] != FALSE){
        $cat[] = 3;
      }
      if($filters['Mutual_Funds'] != FALSE){
        $cat[] = 4;
      }
      if($filters['Term_Plan'] != FALSE){        
        $cat[] = 5;
      }
      if(!empty($cat)) {
        $this->db->where_in('lead_type_info.category', $cat);
      }
    //Category
    if(($role_id === "3") || ($role_id === "5") || ($role_id === "6") || ($role_id === "8")){
      $this->db->where('lead_type_info.category',$category);
    }
    
    //Created by and current control
    if(($role_id === "1") || ($role_id === "2")){
      
      $this->db->where("lead_type_info.prospect_level !=",3);
      $this->db->where("lead_type_info.prospect_level !=",4);      

      $this->db->group_start();
      $this->db->where("lead_type_info.lead_status",1)->or_where("lead_type_info.lead_status",2)->or_where("lead_type_info.lead_status",3)->or_where("lead_type_info.lead_status",4)->or_where("lead_type_info.lead_status",5)->or_where("lead_type_info.lead_status",6)->or_where("lead_type_info.lead_status",7)->or_where("lead_type_info.lead_status",9)->or_where("lead_type_info.lead_status",10);
      $this->db->group_end();
    }else
    if($role_id === "3"){
      $this->db->group_start();
      $this->db->where("lead_type_info.lead_status",1)->or_where("lead_type_info.lead_status",2)->or_where("lead_type_info.lead_status",10)->or_where("lead_type_info.lead_status",7);
      $this->db->group_end();

      $this->db->group_start();
        $this->db->where("lead_type_info.created_by",$emp_id)->or_where("lead_type_info.current_control",$emp_id);
      $this->db->group_end();      
    }else
    if($role_id === "4"){ 
      $this->db->group_start();
      $this->db->where("lead_type_info.lead_status",1)->or_where("lead_type_info.lead_status",2)->or_where("lead_type_info.lead_status",4)->or_where("lead_type_info.lead_status",10);
      $this->db->group_end();

      $this->db->where_in('lead_type_info.created_by', $team_rslt);
    }else
    if($role_id === "5"){
      $this->db->where("lead_type_info.prospect_level !=",3);
      $this->db->where("lead_type_info.prospect_level !=",4); 

      $this->db->group_start();
      $this->db->where("lead_type_info.lead_status",1)->or_where("lead_type_info.lead_status",2)->or_where("lead_type_info.lead_status",6)->or_where("lead_type_info.lead_status",9);
      $this->db->group_end();
      

      $this->db->group_start();
        $this->db->where("lead_type_info.created_by",$emp_id)->or_where("lead_type_info.current_control",$emp_id)->or_where("lead_type_info.rm_name",$emp_id);
      $this->db->group_end();
    }else
    if($role_id === "6"){
      $this->db->group_start();
      $this->db->where("lead_type_info.lead_status",1)->or_where("lead_type_info.lead_status",2)->or_where("lead_type_info.lead_status",4)->or_where("lead_type_info.lead_status",10);
      $this->db->group_end();

      $this->db->group_start();
        $this->db->where_in("lead_type_info.created_by",$team_rslt)->or_where("lead_type_info.current_control",$emp_id);
      $this->db->group_end();
    }else
    if($role_id === "8"){
      $this->db->group_start();
        $this->db->where("lead_type_info.lead_status",1)->or_where("lead_type_info.lead_status",2)->or_where("lead_type_info.lead_status",10)->or_where("lead_type_info.lead_status",7);
      $this->db->group_end();

      $this->db->group_start();
        $this->db->where("lead_type_info.created_by",$emp_id)->or_where("lead_type_info.current_control",$emp_id);
      $this->db->group_end();      
    }else
    if($role_id === "9"){
      $this->db->group_start();
      $this->db->where("lead_type_info.lead_status",1)->or_where("lead_type_info.lead_status",2)->or_where("lead_type_info.lead_status",6);
      $this->db->group_end();

      $this->db->group_start();
        $this->db->where_in("lead_type_info.created_by",$team_rslt)->or_where("lead_type_info.current_control",$emp_id)->or_where_in("lead_type_info.rm_name",$team_rslt);
      $this->db->group_end();
    }
      $this->db->where('lead_type_info.status', 1);
      if(($role_id === "1") || ($role_id === "2") || ($role_id === "4") || ($role_id === "9")) {
         $this->db->group_start();
        $this->db->where('lead_type_info.lead_mode', 1)->or_where("lead_type_info.lead_mode",2);
         $this->db->group_end();
      }
      $this->db->where('DATE_FORMAT(ncd, "%Y-%m-%d") BETWEEN '. $this->db->escape($start_date).' AND '.$this->db->escape($end_date));

      $this->db->order_by($sort, $order);
      if($rows > 0){
        $this->db->limit($rows, $limit_from);
      }
      return $this->db->get();
      // echo $this->db->last_query();
  }

  public function get_team_info($id){
    $this->db->from('team');
    $this->db->where('teamleader', $id);
    $this->db->where('team.deleted',0);
    return $this->db->get()->row();
  }
  public function get_area_info($id){
    $this->db->from('employees');
    $this->db->where('reporting', $id);
    $this->db->where('employees.deleted',0);
    return $this->db->get()->result();
  }

  public function get_found_rows($search, $filters){
    return $this->search($search, $filters)->num_rows();
  }

}
?>