File: /home/cafsindia/crm_cafsindia_com/application/models/Other_campaigns_model.php
<?php
class other_campaigns_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") || ($role_id === "10")){
$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_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_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_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();
if(($role_id === "3") || ($role_id === "5")|| ($role_id === "6")){
if($category !== "4"){
$this->db->where('lead_type_info.category !=',$category);
}
}
//Created by and current control
if(($role_id === "1") || ($role_id === "2")){
// Don't 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->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 === "5"){
$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 === "6"){
$this->db->group_start();
$this->db->where_in("lead_type_info.created_by",$team_rslt)->or_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 === "8"){
$this->db->where("lead_type_info.created_by",$emp_id);
}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);
$this->db->group_end();
}else
if($role_id === "10"){
$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();
}
$this->db->where('lead_type_info.status', 1);
if(($role_id === "1") || ($role_id === "2") || ($role_id === "4") || ($role_id === "9") || ($role_id === "10")) {
$this->db->where('lead_type_info.lead_mode', 2);
}
$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();
}
public function get_multiple_info($lead_ids){
$this->db->from('lead_type_info');
$this->db->where_in('lead_type_info.lead_id', $lead_ids);
return $this->db->get()->result_array();
}
public function get_existing_data($mobile){
$this->db->from('customers');
$this->db->group_start();
$this->db->where('cust_mobile', $mobile);
$this->db->group_end();
return $this->db->get();
//echo $this->db->last_query(); die;
}
public function get_leads_custid($cust_id){
$this->db->from('leads');
$this->db->join('lead_type_info', 'lead_type_info.lead_id = leads.lead_id');
$this->db->join('customers', 'customers.cust_id = leads.cust_id');
$this->db->join('lead_status', 'lead_status.status_id = lead_type_info.lead_status');
$this->db->join('category', 'category.cat_id = lead_type_info.category');
$this->db->join('employees','employees.id = lead_type_info.created_by');
$this->db->join('people', 'people.person_id = employees.person_id');
$this->db->where('leads.cust_id', $cust_id);
return $this->db->get()->result();
}
//Get Met log
public function get_met_log($lead_id){
$this->db->from('met_log');
$this->db->join('lead_type_info', 'lead_type_info.lead_id = met_log.log_lead_id');
$this->db->join('employees','employees.id = met_log.created_by');
$this->db->join('people', 'people.person_id = employees.person_id');
$this->db->where('met_log.log_lead_id', $lead_id);
return $this->db->get()->result();
}
//Get RM
public function get_rm() {
$this->db->from('employees');
$this->db->join('people', 'people.person_id = employees.person_id');
$this->db->where('role', '5');
$this->db->order_by('first_name', 'asc');
return $this->db->get();
}
public function get_tl() {
$emp_role = $this->session->userdata('emp_role');
if(($emp_role === "9") || ($emp_role === "10")){
$this->db->from('employees');
$this->db->join('people', 'people.person_id = employees.person_id');
$this->db->join('category', 'category.cat_id = employees.category');
$this->db->where('employees.role', '4');
$this->db->order_by('first_name', 'asc');
return $this->db->get();
}else{
$area_manager = $this->session->userdata('emp_id');
$this->db->from('employees');
$this->db->join('people', 'people.person_id = employees.person_id');
$this->db->join('category', 'category.cat_id = employees.category');
$this->db->where_in('employees.reporting', $area_manager);
$this->db->order_by('first_name', 'asc');
return $this->db->get();
}
}
public function get_crm() {
$teamleader = $this->session->userdata('teamleader');
$team_info = $this->get_team_info($teamleader);
$team_rslt = explode(",",$team_info->teammembers);
$this->db->from('employees');
$this->db->join('people', 'people.person_id = employees.person_id');
$this->db->where_in('employees.id', $team_rslt);
$this->db->order_by('first_name', 'asc');
return $this->db->get();
}
//get_prospect
public function get_prospect(){
$this->db->from('prospect_level');
$this->db->order_by('prospect_id', 'asc');
return $this->db->get();
}
public function get_lead_status_byid($id){
$this->db->select('statusname');
$this->db->from('lead_status');
$this->db->where('status_id', $id);
return $this->db->get();
}
public function get_status(){
$category = $this->session->userdata('emp_category');
$emp_id = $this->session->userdata('emp_id');
$role_id = $this->session->userdata('emp_role');
$qry = $this->db->query("SELECT * from ospos_lead_status where find_in_set('$role_id',role) and find_in_set('2',mode)");
//$qry = $this->db->query("SELECT * from ospos_lead_status");
return $qry->result();
}
public function get_company(){
$this->db->from('vendor');
$this->db->where('deleted', 0);
$this->db->order_by('category_id','ASC');
return $this->db->get()->result();
}
public function get_product($category){
$this->db->from('vendor');
$this->db->where('category_id', $category);
$this->db->where('deleted', 0);
$this->db->order_by('vendorcompanyname','ASC');
return $this->db->get()->result();
}
public function get_category(){
$role_id = $this->session->userdata('emp_role');
$category = $this->session->userdata('emp_category');
$this->db->from('category');
if(($role_id === "4") || ($role_id === "9")){
}else
if(($role_id === "5") && ($category === "4")){
$this->db->where('cat_id', 4);
}else{
$this->db->where('cat_id !=', $category);
}
return $this->db->get()->result();
}
public function get_info($lead_id){
$this->db->from('leads');
$this->db->join('lead_type_info', 'lead_type_info.lead_id = leads.lead_id');
$this->db->join('customers', 'customers.cust_id = leads.cust_id');
$this->db->where('lead_type_info.lead_id', $lead_id);
$query = $this->db->get();
if($query->num_rows() == 1){
return $query->row();
}else{
$item_obj = new stdClass();
foreach($this->db->list_fields('lead_type_info') as $field){
$item_obj->$field = '';
}
return $item_obj;
}
}
public function save_lead($cust_id,$leads_id){
if($leads_id === -1){
$lead_data = array(
'cust_id' => $cust_id,
'created_by' => $this->session->userdata('emp_id'),
'created_date' => date('Y-m-d h:i:s')
);
$this->db->insert('leads', $lead_data);
$insert_id = $this->db->insert_id();
$qry = $this->db->query("SELECT * from ospos_customers where cust_id = '$cust_id'");
$cust_info = $qry->row();
$name = $cust_info->cust_name;
$dob = $cust_info->dob;
$family_data = array(
'cust_id' => $cust_id,
'lead_id' => $insert_id,
'fam_name' => $name,
'fam_dob' => $dob,
'fam_relation' => "Self",
'fam_profile' => "Self",
'fam_sex' => "Self",
'created_by' => $this->session->userdata('emp_id'),
'created_date' => date('Y-m-d h:i:s'),
);
$this->db->insert('family', $family_data);
return $insert_id;
}else{
$lead_data = array(
'cust_id' => $cust_id,
'updated_by' => $this->session->userdata('emp_id'),
'updated_date' => date('Y-m-d h:i:s')
);
$this->db->where('lead_id', $leads_id);
$this->db->update('leads', $lead_data);
return $leads_id;
}
}
//Save or update lead_type_info
public function save_lead_info($lead_info_data,$leads_id){
$lead_status = $lead_info_data['lead_status'];
$category = $lead_info_data['category'];
if($leads_id === -1){
$lead_info_data['created_by'] = $this->session->userdata('emp_id');
$lead_info_data['created_date'] = date('Y-m-d h:i:s');
$this->db->insert('lead_type_info', $lead_info_data);
$lead_info_id = $this->db->insert_id();
$log_info = array(
'log_lead_id' => $lead_info_id,
'operation' => "INSERT",
'lead_status' => $lead_info_data['lead_status'],
'lead_remarks' => $lead_info_data['remarks'],
'created_by' => $this->session->userdata('emp_id'),
'created_date' => date('Y-m-d h:i:s')
);
$this->db->insert('lead_log', $log_info);
if($lead_info_data['lead_status'] === "9"){
$met_log = array(
'log_lead_id' => $lead_info_id,
'met_date' => date('Y-m-d'),
'created_by' => $this->session->userdata('emp_id'),
'created_date' => date('Y-m-d h:i:s')
);
$this->db->insert('met_log', $met_log);
}
if(($category === "4") && ($lead_status === "8")){
$this->save_mf($lead_info_data);
}
return $lead_info_id;
}else{
$lead_info_data['updated_by'] = $this->session->userdata('emp_id');
$lead_info_data['updated_date'] = date('Y-m-d h:i:s');
$this->db->where('lead_id', $leads_id);
$this->db->update('lead_type_info', $lead_info_data);
$log_info = array(
'log_lead_id' => $leads_id,
'operation' => "UPDATE",
'lead_status' => $lead_info_data['lead_status'],
'lead_remarks' => $lead_info_data['remarks'],
'created_by' => $this->session->userdata('emp_id'),
'created_date' => date('Y-m-d h:i:s')
);
$this->db->insert('lead_log', $log_info);
if($lead_info_data['lead_status'] === "9"){
$met_log = array(
'log_lead_id' => $leads_id,
'met_date' => date('Y-m-d'),
'created_by' => $this->session->userdata('emp_id'),
'created_date' => date('Y-m-d h:i:s')
);
$this->db->insert('met_log', $met_log);
}
if(($category === "4") && ($lead_status === "8")){
$this->save_mf($lead_info_data);
}
return $leads_id;
}
}
public function save_mf($lead_info_data){
$lead_id = $lead_info_data['lead_id'];
$cust_info = $this->get_cust_id($lead_id);
$cust_id = $cust_info->cust_id;
$mf_count = $this->is_mf_exist($cust_id);
$mf_info = array(
'cust_id' => $cust_id,
'mf_status' => "Pending",
);
$mf_log_info = array(
'cust_id' => $cust_id,
'lead_id' => $lead_info_data['lead_id'],
'category' => $lead_info_data['category'],
'company' => $lead_info_data['company'],
'mf_prospect' => $lead_info_data['mf_prospect'],
'mf_expected_value' => $lead_info_data['mf_expected_value'],
'mf_login_value' => $lead_info_data['mf_login_value'],
'current_control' => $lead_info_data['current_control'],
);
if((int)$mf_count->num_rows() === 0){
$mf_info['created_by'] = $this->session->userdata('emp_id');
$mf_info['created_date'] = date('Y-m-d h:i:s');
$this->db->insert('mutual_fund', $mf_info);
$mf_id = $this->db->insert_id();
$family_info = $this->get_family($cust_id);
$family_count = $family_info->num_rows();
if($family_count > 0){
$family_rlst = $family_info->result();
foreach($family_rlst as $family){
$family_data = array(
'mf_id' => $mf_id,
'cust_id' => $cust_id,
'fam_name' => $family->fam_name,
'fam_dob' => $family->fam_dob,
'fam_relation' => $family->fam_relation,
'fam_profile' => $family->fam_profile,
'fam_sex' => $family->fam_sex,
'deleted' => $family->fam_name,
'created_by' => $this->session->userdata('emp_id'),
'created_date' => date('Y-m-d h:i:s'),
);
$this->db->insert('mf_family', $family_data);
}
}
$mf_log_info['mf_id'] = $mf_id;
return $this->save_mf_log($mf_log_info);
}else{
$mf_info['updated_by'] = $this->session->userdata('emp_id');
$mf_info['updated_date'] = date('Y-m-d h:i:s');
$this->db->where('cust_id', $cust_id);
$this->db->update('mutual_fund', $mf_info);
$mf_rslt = $mf_count->row();
$mf_id = $mf_rslt->mf_id;
$mf_log_info['mf_id'] = $mf_id;
return $this->save_mf_log($mf_log_info);
}
}
public function get_cust_id($lead_id){
$this->db->from('leads');
$this->db->where('lead_id', $lead_id);
return $this->db->get()->row();
}
public function is_mf_exist($cust_id){
$this->db->from('mutual_fund');
$this->db->where('cust_id', $cust_id);
return $this->db->get();
}
public function get_family($cust_id){
$this->db->from('family');
$this->db->where('cust_id', $cust_id);
return $this->db->get();
}
public function is_mf_log_exist($cust_id,$lead_id){
$this->db->from('mutual_fund_log');
$this->db->where('cust_id', $cust_id);
$this->db->where('lead_id', $lead_id);
return $this->db->get()->num_rows();
}
public function save_mf_log($mf_log_info){
$cust_id = $mf_log_info['cust_id'];
$lead_id = $mf_log_info['lead_id'];
$log_count = $this->is_mf_log_exist($cust_id,$lead_id);
if((int)$log_count === 0){
$mf_log_info['created_by'] = $this->session->userdata('emp_id');
$mf_log_info['created_date'] = date('Y-m-d h:i:s');
return $this->db->insert('mutual_fund_log', $mf_log_info);
}else{
$mf_log_info['updated_by'] = $this->session->userdata('emp_id');
$mf_log_info['updated_date'] = date('Y-m-d h:i:s');
$this->db->where('cust_id', $cust_id);
$this->db->where('lead_id', $lead_id);
return $this->db->update('mutual_fund_log', $mf_log_info);
}
}
}
?>