<?php
class dropped_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();
      
      $this->db->where('lead_type_info.status', 1);
      $this->db->where('lead_type_info.lead_mode', 1);
      $this->db->where('DATE_FORMAT(ncd, "%Y-%m-%d") BETWEEN '. $this->db->escape($start_date).' AND '.$this->db->escape($end_date));

      //Category
      if(($role_id === "3") || ($role_id === "5") || ($role_id === "6")){
        $this->db->where('lead_type_info.category',$category);
      }

      //Lead Status
      $this->db->group_start();
      $this->db->where("lead_type_info.lead_status",3)->or_where("lead_type_info.prospect_level",3);
      $this->db->group_end();

      //Created by and current control
      if(($role_id === "1") || ($role_id === "2")){
        // Dont Remove this if
      }else
      if($role_id === "3"){
        $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->where_in('lead_type_info.created_by', $team_rslt);
      }else
      if($role_id === "5"){
        $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_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 === "9"){        
        $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->where('lead_type_info.lead_mode', 1);
      }
      $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();
  }

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