<?php
class completed_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 = "pro.login_date"; 
      }
      $this->db->distinct('leads_id');
      $this->db->select('lead_type_info.lead_type_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.current_control,lead_type_info.created_date,category.cat_name,pro.login_date as log_date');
      $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('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->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);
      }

      //$this->db->where('lead_type_info.lead_status', 8)
      $this->db->group_start();
        $this->db->where('lead_type_info.current_control', "HC")->or_where('lead_type_info.current_control', "Completed");
      $this->db->group_end();
      $this->db->where("lead_type_info.current_control","Completed");
      $this->db->where('lead_type_info.status', 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->where('lead_type_info.current_control', "HC");
      //$this->db->where('lead_type_info.current_control', "Completed");      

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

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

  public function get_info($lead_id){
    $this->db->from('lead_type_info');   
   // $this->db->join('happy_calling', 'happy_calling.call_lead_id = lead_type_info.lead_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 exists($lead_id, $product_id){
    $this->db->from('happy_calling');
    $this->db->where('call_lead_id',$lead_id);
    $this->db->where('call_product_id',$product_id);
    return $this->db->get()->num_rows();
  }

  public function save_call($call_data, $lead_id, $product_id){
    $count = $this->exists($lead_id, $product_id);
    if($count === 1){
      $call_data['updated_by']   = $this->session->userdata('emp_id');
      $call_data['updated_date'] = date('Y-m-d h:i:s');
      $this->db->where('call_lead_id', $lead_id);
      $this->db->where('call_product_id', $product_id);
      return $this->db->update('happy_calling', $call_data);
    }else{
      $this->db->insert('happy_calling', $call_data);
      return $call_data['call_id'] = $this->db->insert_id();
    }
  }

  public function get_product_list($lead_id){
    $this->db->from('leads_product');
    $this->db->join('happy_calling', 'happy_calling.call_product_id = leads_product.customer_product_id');
    $this->db->join('category', 'category.cat_id = leads_product.product_category');
    $this->db->join('vendor', 'vendor.vendor_id = leads_product.company');
    $this->db->where('completed', 1);
    $this->db->where('leads_product.lead_info_id', $lead_id);
    return $this->db->get()->result();
  }

  public function get_fund_name_list(){
    $this->db->from('fund_name');
    $this->db->where('status', 0);
    return $this->db->get()->result();
  }

  public function get_amc($fund_id=FALSE){
    $this->db->from('amc');
    if($fund_id){
      $this->db->where('fund_id', $fund_id);
    }    
    $this->db->where('status', 0);
    return $this->db->get()->result();
  }
  public function get_product($call_lead_id,$call_product_id){
      $cat = $this->get_category($call_product_id);
      if($cat !== "4"){
        $this->db->select('*,vendor.vendorcompanyname as company,lead_type_info.created_date as created');
      }else{
        $this->db->select('*,lead_type_info.created_date as created');
      }
      $this->db->from('happy_calling');
      $this->db->join('leads_product', 'leads_product.customer_product_id = happy_calling.call_product_id');
      $this->db->join('lead_type_info', 'lead_type_info.lead_id = happy_calling.call_lead_id');
      $this->db->join('leads', 'leads.lead_id = happy_calling.call_lead_id');
      $this->db->join('customers', 'customers.cust_id = leads.cust_id');

      // if($cat !== "3  "){
      //     $this->db->join('employees', 'employees.id = lead_type_info.rm_name');
      //     $this->db->join('people', 'people.person_id = employees.person_id');
      // }      

      if($cat !== "4"){
        $this->db->join('vendor', 'vendor.vendor_id = leads_product.company');
        $this->db->join('items', 'items.product_id = leads_product.product');
      }
      $this->db->where('happy_calling.call_lead_id', $call_lead_id);
      $this->db->where('happy_calling.call_product_id', $call_product_id);
      return $this->db->get()->result();
  }

  public function get_category($product_id){
     $this->db->from('leads_product');
     $this->db->where('leads_product.customer_product_id', $product_id);
     return $this->db->get()->row()->product_category;
  }

  public function get_rm_name($person_id){
    $this->db->from('employees');
    $this->db->join('people', 'people.person_id = employees.person_id');
    $this->db->where('employees.id', $person_id);
    return $this->db->get()->row()->first_name;
  }

  public function can_update($product_id){
    //issuance_status,document_issue_status,confirmation
    $this->db->from('happy_calling');
    $this->db->where('call_product_id', $product_id);
    $result = $this->db->get()->row();
    $issuance_status       = $result->issuance_status;
    $document_issue_status = $result->document_issue_status;
    $confirmation          = $result->confirmation;
    $rm_comitted_status    = $result->rm_comitted_status;
    
    if(($issuance_status === "Issued") && ($document_issue_status === "Dispatched") && ($confirmation === "Received")  && ($rm_comitted_status === "1")){
      return true;
    }else{
      return false;
    }
  }

  public function update_completed($count,$product_id,$lead_info_id){

    $cc_data = array('current_control' => "Completed");
    $this->db->where('lead_type_id', $lead_info_id);
    $this->db->update('lead_type_info', $cc_data);

    $data = array('hc_status' => $count);
    $this->db->where('call_lead_id', $lead_info_id);
    $this->db->where('call_product_id', $product_id);    
    $result = $this->db->update('happy_calling', $data);
     
    if($this->renewal_exists($lead_info_id, $product_id) === 0){
      $product_info = $this->get_product_info($lead_info_id, $product_id);
      
      $issued_date = $product_info[0]['issued_date'];
      $mode        = $product_info[0]['mode'];

      if($mode === "1"){ //Yearly
        $renewal_date = date("d-m-Y", strtotime(date("Y-m-d", strtotime($issued_date)) . " + 1 year"));
      }else
      if($mode === "2"){ //Semi-Annual
        $renewal_date = date("d-m-Y", strtotime(date("Y-m-d", strtotime($issued_date)) . " + 6 months"));
      }else
      if($mode === "3"){ //Monthly
        $renewal_date = date("d-m-Y", strtotime(date("Y-m-d", strtotime($issued_date)) . " + 1 month"));
      }

      $renewal['lead_id']      = $lead_info_id;
      $renewal['product_id']   = $product_id;
      $renewal['renewal_date'] = date("Y-m-d", strtotime($renewal_date));
      $this->db->insert('renewal', $renewal);
    }
    return $result;
  }
   public function update_freelook($count,$product_id,$lead_info_id){
     $this->db->where_in('lead_type_id', $lead_info_id);
     $this->db->update('lead_type_info', array('status' => 0));

     $this->db->where_in('customer_product_id', $product_id);
     $this->db->update('leads_product', array('status' => 0));

     $this->db->where_in('call_lead_id', $lead_info_id);
     return $this->db->update('happy_calling', array('deleted' => 1));     
   }
   public function get_product_info($lead_id, $product_id){
    $this->db->from('happy_calling');
    $this->db->join('leads_product', 'leads_product.customer_product_id = happy_calling.call_product_id');
    $this->db->where('call_lead_id', $lead_id);
    $this->db->where('call_product_id', $product_id);
    return $this->db->get()->result_array();
   }

  public function renewal_exists($lead_id, $product_id){
    $this->db->from('renewal');
    $this->db->where('lead_id',$lead_id);
    $this->db->where('product_id',$product_id);
    return $this->db->get()->num_rows();
  }
}
?>