File: /home/cafsindia/crm_cafsindia_com/application/models/Closed_leads_model.php
<?php
class closed_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 = "log_date";
}
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->distinct('leads_id');
$this->db->select('lead_type_info.lead_id as leads_id,customers.cust_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.created_date,lead_type_info.current_control,category.cat_name,pro.login_date as log_date,sum(pro.overall_net_premium) as premium,lead_type_info.met_date,met_log.met_date as first_met');
$this->db->from('lead_type_info');
$this->db->join('leads', 'leads.lead_id = lead_type_info.lead_id');
$this->db->join('leads_product pro', 'pro.lead_info_id = lead_type_info.lead_type_id','left');
$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('met_log', 'met_log.log_lead_id = lead_type_info.lead_id','left');
$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->or_like('pro.login_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")){
$this->db->where('lead_type_info.category',$category);
}
//Created by and current control
if(($role_id === "1") || ($role_id === "2")){
// Dont Remove this if
$this->db->group_start();
$this->db->where("lead_type_info.lead_status",8)->or_where("lead_type_info.lead_status",11)->or_where("lead_type_info.prospect_level",4);
$this->db->group_end();
}else
if($role_id === "3"){
$this->db->group_start();
$this->db->where("lead_type_info.lead_status",8)->or_where("lead_type_info.lead_status",9)->or_where("lead_type_info.lead_status",11);
$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",8)->or_where("lead_type_info.lead_status",11)->or_where("lead_type_info.prospect_level",4);
$this->db->group_end();
$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.lead_status",8)->or_where("lead_type_info.lead_status",11)->or_where("lead_type_info.prospect_level",4);
$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",8)->or_where("lead_type_info.lead_status",9)->or_where("lead_type_info.lead_status",11);
$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("lead_type_info.team_leader",$emp_id);
$this->db->group_end();
}else
if($role_id === "8"){
$this->db->group_start();
$this->db->where("lead_type_info.lead_status",8)->or_where("lead_type_info.lead_status",9)->or_where("lead_type_info.lead_status",11);
$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.re_name",$emp_id);
$this->db->group_end();
}else
if($role_id === "9"){
$this->db->group_start();
$this->db->where("lead_type_info.lead_status",8)->or_where("lead_type_info.lead_status",11)->or_where("lead_type_info.prospect_level",4);
$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);
$this->db->where('pro.status', 1);
if($category=== "2")
{
if($role_id!=="4")
$this->db->where('met_log.first_met', 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->group_start();
$this->db->where('DATE_FORMAT(pro.login_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($start_date).' AND '.$this->db->escape($end_date))->or_where('pro.login_date', NULL,true);
$this->db->group_end();
$this->db->group_by('lead_type_info.lead_id');
$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();
}
}
?>