<?php
class all_leads_model extends CI_Model{
  /*
  Perform a search on items
  */
  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 === "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);
      }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);
      }

      $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")){
        $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)->or_where("lead_type_info.crm_name",$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 === "8"){
        $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 === "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)->or_where("lead_type_info.team_leader",$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();
      }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)->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();
      //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();
  }
  /*
  Get number of rows
  */
  public function get_found_rows($search, $filters)
  {
    return $this->search($search, $filters)->num_rows();
  }

  /*
  Gets information about a particular item
  */
  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();
      //Get all the fields from items table
      foreach($this->db->list_fields('lead_type_info') as $field)
      {
        $item_obj->$field = '';
      }
      return $item_obj;
    }
  }

  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->select('*, crm.first_name as crmName, rm.first_name as rmName');
    $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('prospect_level', 'prospect_level.prospect_id = lead_type_info.prospect_level');
    $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 crm', 'crm.person_id = employees.person_id');
    $this->db->join('people rm' ,'rm.person_id = lead_type_info.rm_name','left');
    $this->db->where('leads.cust_id', $cust_id);
    $this->db->where('lead_type_info.status', 1);
    return $this->db->get()->result();
  }
  //Get Met log
  public function get_met_log($lead_id){
    $this->db->select("met_log.log_lead_id,met_log.met_date,people.first_name,met_log.met_remarks");
    $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 Company
  public function get_company(){
    $this->db->from('vendor');
    $this->db->where('category_id',$this->session->userdata('emp_category'));
    $this->db->where('deleted', 0);
    return $this->db->get();
  }
  public function get_all_company(){
    $this->db->from('vendor');
    $this->db->where('deleted', 0);
    return $this->db->get();
  }
  public function get_product(){
    $this->db->from('items');
    $this->db->where('category_id',$this->session->userdata('emp_category'));
    $this->db->where('deleted', 0);
    return $this->db->get();
  }
  public function get_all_product(){
    $this->db->from('items');
    $this->db->where('deleted', 0);
    return $this->db->get();
  }

  //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();
	}
  //Get RE 
  public function get_re() {

     $this->db->from('employees');
     $this->db->join('people', 'people.person_id = employees.person_id');
     $this->db->where('role', '8');
     $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_leadstatus
  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('$category',category) and find_in_set('$role_id',role) and find_in_set('1',mode)");
    return $qry->result();
  }

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

  //get_lead_type
  public function get_lead_type(){
    $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_type where category in ($category)");
    return $qry->result();
     //echo $this->db->last_query();
  }
  /*
  Inserts or updates a customer
  */
  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){
    if($leads_id === -1){
		$role            = $this->session->userdata('emp_role');
		if($role === "3"){
			$lead_info_data['team_leader']  = $this->session->userdata('teamleader');
		}else
		if($role === "5"){
			$lead_info_data['team_leader']  = $this->session->userdata('rm_manager');
			$lead_info_data['rm_name']  = $this->session->userdata('emp_id');
		}
		
      $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_lead_id = $lead_info_data['lead_id']; 
        $met_log = array(
          'log_lead_id'     => $met_lead_id,
          'met_date'        => $lead_info_data['met_date'],
          'created_by'      => $this->session->userdata('emp_id'),
          'created_date'    => date('Y-m-d h:i:s'),
          'met_remarks'     => $lead_info_data['remarks'],
          'first_met'       => 1
        );
       
        // if($count === 1){
        //   $this->db->where('met_id', $log_id);
        //   return $this->db->update('met_log', $met_log);
        // }else{
          $this->db->insert('met_log', $met_log);
        //}
      }
      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_info = $this->is_met($leads_id); 
        $met_count    = $met_log_info->num_rows();
        $met_log_Rslt = $met_log_info->row();
        $first_met = 0;
        if($met_count === 0){
          $first_met = '1';
        }
        $met_log = array(
          'log_lead_id'     => $leads_id,
          'met_date'        => $lead_info_data['met_date'],
          'created_by'       => $this->session->userdata('emp_id'),
          'created_date'     => date('Y-m-d h:i:s'),
          'met_remarks'     => $lead_info_data['remarks'],
          'first_met'       => $first_met
        );

        $log_info = $this->is_met_exist($lead_info_data['met_date'],$leads_id);
        $count    = $log_info->num_rows();
        $log_Rslt = $log_info->row();
        $log_id   = $log_Rslt->met_id;
        //echo $count."::".$leads_id; die();
        if($count === 1){
         $met_log = array(
          'log_lead_id'     => $leads_id,          
          'created_by'       => $this->session->userdata('emp_id'),
          'created_date'     => date('Y-m-d h:i:s'),
          'met_remarks'     => $lead_info_data['remarks']         
        );

          $this->db->where('met_id', $log_id);
          return $this->db->update('met_log', $met_log);
        }else{
          $this->db->insert('met_log', $met_log);
        }
        
      }
    }
    return $leads_id;
  }
  
  public function is_met($leads_id){ 
    $this->db->from('met_log');
    $this->db->where('log_lead_id', $leads_id);
    $this->db->where('first_met', '1');
    return $this->db->get();
  }

  public function is_met_exist($date,$leads_id){
    $this->db->from('met_log');
    $this->db->where('log_lead_id', $leads_id);
    $this->db->where('met_date', $date);
     $this->db->where('first_met', '1');
    return $this->db->get();
  }
  /*
  Gets information about multiple Leads
  */
  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_cust_name($cus_lead_id){
    $cust_query = $this->db->query("SELECT * from ospos_leads where lead_id = '$cus_lead_id'");
    $cust_info  =  $cust_query->row();
    $cust_id = $cust_info->cust_id;
    $name_query = $this->db->query("SELECT * from ospos_customers where cust_id = '$cust_id'");
    $name_info  =  $name_query->row();
    $name = $name_info->cust_name;
    return $name;
  }
  public function delete_list($lead_ids){ 
    $this->db->where_in('lead_id', $lead_ids);
    return $this->db->update('lead_type_info', array('status' => 0,'deleted_by'=>$this->session->userdata('emp_id'),'deleted_date'=>date('Y-m-d h:i:s')));
  }
  public function view_login($cust_id){
    
    $login_query = $this->db->query("SELECT *,ospos_lead_type_info.created_by as owner,ospos_vendor.vendorcompanyname as companyname from ospos_customers left join ospos_leads on ospos_leads.cust_id = ospos_customers.cust_id inner join ospos_lead_type_info on ospos_lead_type_info.lead_id = ospos_leads.lead_id inner join ospos_leads_product on lead_info_id = lead_type_id inner join ospos_vendor on ospos_vendor.vendor_id = ospos_leads_product.company inner join ospos_items on ospos_items.product_id = ospos_leads_product.product inner join ospos_happy_calling on ospos_happy_calling.call_lead_id = ospos_leads_product.lead_info_id where ospos_customers.cust_id = '$cust_id' and ospos_leads_product.status = '1' and ospos_lead_type_info.status = '1' GROUP BY customer_product_id");
    return $login_query->result_array();
  }
  public function cust_info($cust_id){
    $cust_query = $this->db->query("SELECT * from ospos_customers where cust_id = '$cust_id'");
    return $cust_query->result_array();
  }
	public function get_view_family($cust_id){
		$cust_query = $this->db->query("SELECT * from ospos_family where cust_id = '$cust_id' and deleted = '0'");
	return $cust_query->result_array();
  }
}
?>