<?php
class Report_model extends CI_Model
{
	public function search($search, $filters, $rows = 0, $limit_from = 0, $sort = '', $order = 'asc'){
	$crm   = $filters['crm'];
	$crmto = $filters['crmto'];
    $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();

    //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")){
      
      $this->db->where("lead_type_info.prospect_level !=",3);
      $this->db->where("lead_type_info.prospect_level !=",4);      

      $this->db->group_start();
      $this->db->where("lead_type_info.lead_status",1)->or_where("lead_type_info.lead_status",2)->or_where("lead_type_info.lead_status",3)->or_where("lead_type_info.lead_status",4)->or_where("lead_type_info.lead_status",5)->or_where("lead_type_info.lead_status",6)->or_where("lead_type_info.lead_status",7)->or_where("lead_type_info.lead_status",9)->or_where("lead_type_info.lead_status",10);
      $this->db->group_end();
    }else
    if($role_id === "3"){
      $this->db->group_start();
      $this->db->where("lead_type_info.lead_status",1)->or_where("lead_type_info.lead_status",2)->or_where("lead_type_info.lead_status",10)->or_where("lead_type_info.lead_status",7);
      $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",1)->or_where("lead_type_info.lead_status",2)->or_where("lead_type_info.lead_status",4)->or_where("lead_type_info.lead_status",10);
      $this->db->group_end();

      $this->db->where_in('lead_type_info.created_by', $team_rslt);
    }else
    if($role_id === "5"){
      $this->db->where("lead_type_info.prospect_level !=",3);
      $this->db->where("lead_type_info.prospect_level !=",4); 

      $this->db->group_start();
      $this->db->where("lead_type_info.lead_status",1)->or_where("lead_type_info.lead_status",2)->or_where("lead_type_info.lead_status",6)->or_where("lead_type_info.lead_status",9);
      $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",1)->or_where("lead_type_info.lead_status",2)->or_where("lead_type_info.lead_status",4)->or_where("lead_type_info.lead_status",10);
      $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);
      $this->db->group_end();
    }else
    if($role_id === "9"){
      $this->db->group_start();
      $this->db->where("lead_type_info.lead_status",1)->or_where("lead_type_info.lead_status",2)->or_where("lead_type_info.lead_status",6);
      $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.created_by", $crm);
      $this->db->where("lead_type_info.current_control", $crm);
      $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->order_by($sort, $order);
      if($rows > 0){
        $this->db->limit($rows, $limit_from);
      }
      return $this->db->get();
  }

  
  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_crm($id) {
	$this->db->from('employees');
	$this->db->join('people', 'people.person_id = employees.person_id');
	$this->db->where('role', '3');
	$this->db->where('reporting',$id);
	$this->db->order_by('first_name', 'asc');

   return $this->db->get()->result_array();
}
public function get_role() {
	  $this->db->from('role');
    $this->db->order_by('role_name', 'asc');
    return $this->db->get();
}
public function get_category() {
     $this->db->from('category');
     $this->db->order_by('cat_name', 'asc');
     return $this->db->get();
}
public function role_wise($role,$cat,$start_date,$end_date,$status){

  $qry = $this->db->query("SELECT id,first_name from ospos_employees inner join ospos_people on ospos_people.person_id = ospos_employees.person_id where ospos_employees.deleted = '0' and role = '$role' and category = '$cat'");   
    $row = $qry->result_array(); 
    $count = 0;
    $axa_total = 0;
    $lic_total = 0;
    $Edel_total = 0;
    $NJ_total = 0;
    $star_total = 0;
    $axa  = 0;
    $lic  = 0;
    $Edel = 0;
    $NJ   = 0;
    $star = 0;
    $tot  = 0;

    $category['name'] = 'Name';
    if($cat === "2"){
      $series1['name'] = 'Value'; 
    }else
    if($cat === "3"){
      $series1['name'] = 'Value'; 
    }

    foreach ($row as $key => $value) {     
      $id = $value['id']; 
      $first_name = $value['first_name'];
      $role = $role;
      $status = $status;

      if($role === "3"){
        $role_qry = " and ospos_lead_type_info.created_by = '$id'";
      }else
      if($role === "5"){
        $role_qry = " and (ospos_lead_type_info.created_by = '$id' or ospos_lead_type_info.rm_name = '$id')";
      }else
      if($role === "6"){
        $role_qry = " and ospos_lead_type_info.created_by = '$id'";
      }else
      if($role === "8"){
        $role_qry = " and ospos_lead_type_info.re_name = '$id'";
      }

      if($status === "1"){
        $status_qry = " and ospos_happy_calling.issuance_status = 'Issued'";
      }else
      if($status === "2"){
        $status_qry = " and ospos_happy_calling.issuance_status != 'Issued'";
      }else
      if($status === "3"){
       $status_qry = "";
      }
      
      $val_qry = $this->db->query("SELECT IFNULL(sum(net_premium),0) as value,product_category,ospos_leads_product.company,vendorcompanyname FROM ospos_leads_product inner join ospos_lead_type_info on ospos_lead_type_info.lead_type_id = ospos_leads_product.lead_info_id INNER join ospos_vendor on vendor_id = ospos_leads_product.company inner join ospos_happy_calling on call_product_id = customer_product_id where ospos_leads_product.login_date between '$start_date' and '$end_date' $role_qry $status_qry and product_category = '$cat' group by vendorcompanyname ORDER BY value DESC");   
      $val_row = $val_qry->result_array();

      foreach ($val_row as $key => $val) {
        $company = $val['vendorcompanyname'];
        $value   = $val['value']; 
        
        if($company === "Bharti Axa"){
          $axa = $value;
          $axa_total += $value;      
        }else
        if($company === "LIC"){
          $lic = $value;
          $lic_total += $value;
        }else
        if($company === "Edelweiss Tokio"){
          $Edel = $value;
          $Edel_total += $value;
        }else
        if($company === "NJ"){
          $NJ = $value;
          $NJ_total += $value;
        }else
        if($company === "Star Health"){
          $star = $value;
          $star_total += $value;
        }
        if($cat === "2"){
          $tot = $axa + $lic + $Edel + $NJ;
          $tot_com = $axa_total + $lic_total + $Edel_total + $NJ_total;
        }else
        if($cat === "3"){
          $tot = $star + $NJ;
          $tot_com = $star_total + $NJ_total; 
        }
      }
      if($cat === "2"){
        $count ++;
        $category['data'][] = $first_name;
        $series1['data'][] = $tot;
      }else
      if($cat === "3"){
        $count ++;
        $category['data'][] = $first_name;
        $series1['data'][] = $tot;
      }
      $axa  = 0;
      $lic  = 0;
      $Edel = 0;
      $NJ   = 0;
      $star = 0;
      $tot  = 0;
    }
    $result = array();
    array_push($result,$category);
    array_push($result,$series1);
    return $result;
}
public function role_wise_report($role,$cat,$filters,$status){

   $start_date = $filters['start_date'];
   $end_date   = $filters['end_date']; 

  $qry = $this->db->query("SELECT id,first_name from ospos_employees inner join ospos_people on ospos_people.person_id = ospos_employees.person_id where ospos_employees.deleted = '0' and role = '$role' and category = '$cat'");   
    $row = $qry->result_array(); 
    $count = 0;
    $axa_total = 0;
    $lic_total = 0;
    $Edel_total = 0;
    $NJ_total = 0;
    $star_total = 0;
    $axa  = 0;
    $lic  = 0;
    $Edel = 0;
    $NJ   = 0;
    $star = 0;
    $tot  = 0;
    foreach ($row as $key => $value) {
     
      $id = $value['id']; 
      $first_name = $value['first_name'];
      $role = $role;
      $status = $status;

      if($role === "3"){
        $role_qry = " and ospos_lead_type_info.created_by = '$id'";
      }else
      if($role === "5"){
        $role_qry = " and (ospos_lead_type_info.created_by = '$id' or ospos_lead_type_info.rm_name = '$id')";
      }else
      if($role === "6"){
        $role_qry = " and ospos_lead_type_info.created_by = '$id'";
      }else
      if($role === "8"){
        $role_qry = " and ospos_lead_type_info.re_name = '$id'";
      }

      if($status === "1"){
        $status_qry = " and ospos_happy_calling.issuance_status = 'Issued'";
      }else
      if($status === "2"){
        $status_qry = " and ospos_happy_calling.issuance_status != 'Issued'";
      }else
      if($status === "3"){
       $status_qry = "";
      }
      
      $val_qry = $this->db->query("SELECT IFNULL(sum(net_premium),0) as value,product_category,ospos_leads_product.company,vendorcompanyname FROM ospos_leads_product inner join ospos_lead_type_info on ospos_lead_type_info.lead_type_id = ospos_leads_product.lead_info_id INNER join ospos_vendor on vendor_id = ospos_leads_product.company inner join ospos_happy_calling on call_product_id = customer_product_id where ospos_leads_product.login_date between '$start_date' and '$end_date' $role_qry $status_qry and product_category = '$cat' group by vendorcompanyname ORDER BY value DESC");   
      $val_row = $val_qry->result_array();
      foreach ($val_row as $key => $val) {
        $company = $val['vendorcompanyname'];
        $value   = $val['value']; 
        
        if($company === "Bharti Axa"){
          $axa = $value;
          $axa_total += $value;      
        }else
        if($company === "LIC"){
          $lic = $value;
          $lic_total += $value;
        }else
        if($company === "Edelweiss Tokio"){
          $Edel = $value;
          $Edel_total += $value;
        }else
        if($company === "NJ"){
          $NJ = $value;
          $NJ_total += $value;
        }else
        if($company === "Star Health"){
          $star = $value;
          $star_total += $value;
        }
        if($cat === "2"){
          $tot = $axa + $lic + $Edel + $NJ;
          $tot_com = $axa_total + $lic_total + $Edel_total + $NJ_total;
        }else
        if($cat === "3"){
          $tot = $star + $NJ;
          $tot_com = $star_total + $NJ_total; 
        }
      }
      if($cat === "2"){
        $count ++;
        $tr_line  .= "<tr style='text-align:center !important;'>
                  <td>$count</td>
                  <td style='text-align:left !important;'>$first_name</td>
                  <td>$axa</td>
                  <td>$lic</td>   
                  <td>$Edel</td>
                  <td style='font-weight:bold;color:#3369E6;'>$tot</td>
                </tr>"; 
      }else
      if($cat === "3"){
        $count ++;
        $tr_line  .= "<tr style='text-align:center !important;'>
                      <td>$count</td>
                      <td style='text-align:left !important;'>$first_name</td>                      
                      <td>$star</td>
                      <td>$NJ</td>
                      <td style='font-weight:bold;color:#3369E6;'>$tot</td>
                    </tr>"; 
      }
      $axa  = 0;
      $lic  = 0;
      $Edel = 0;
      $NJ   = 0;
      $star = 0;
      $tot  = 0;
    }    
    if($cat === "2"){
      $tr_line .="<tr style='font-weight:bold;text-align:center !important;background:#3369E6;'>
                  <td style ='color:#FFFFFF !important;'></td>
                  <td style ='color:#FFFFFF !important;'>Total</td>
                  <td style ='color:#FFFFFF !important;'>$axa_total</td>
                  <td style ='color:#FFFFFF !important;'>$lic_total</td>   
                  <td style ='color:#FFFFFF !important;'>$Edel_total</td>
                  <td style ='color:#FFFFFF !important;'>$tot_com</td>
                </tr>";
    }else
    if($cat === "3"){
      $tr_line .="<tr style ='font-weight:bold;text-align:center !important;background:#3369E6;'>
                  <td style ='color:#FFFFFF !important;'></td>
                  <td style ='color:#FFFFFF !important;'>Total</td>                    
                  <td style ='color:#FFFFFF !important;'>$star_total</td>
                  <td style ='color:#FFFFFF !important;'>$NJ_total</td>
                  <td style ='color:#FFFFFF !important;'>$tot_com</td>
                </tr>";
    }
    
    if($cat === "2"){
       echo "<table id='detail_list' class='table table-hover table-striped col-style'>
        <thead>
          <tr>
            <th style='text-align:center !important;padding:4px;'>Sl.No</th>
            <th style='text-align:center !important;padding:4px;'>Name</th>
            <th style='text-align:center !important;padding:4px;'>Bharti Axa</th> 
            <th style='text-align:center !important;padding:4px;'>LIC</th>
            <th style='text-align:center !important;padding:4px;'>Edelweiss Tokio</th> 
            <th style='font-weight:bold;text-align:center !important;padding:4px;'>Total</th>        
          </tr>
        </thead>
        <tbody> $tr_line</tbody>
      </table>";
    }else
    if($cat === "3"){
       echo "<table id='detail_list' class='table table-hover table-striped col-style'>
        <thead>
          <tr>
            <th style='text-align:center !important;padding:4px;'>Sl.No</th>
            <th style='text-align:center !important;padding:4px;'>Name</th>
            <th style='text-align:center !important;padding:4px;'>Star Health</th>
            <th style='text-align:center !important;padding:4px;'>NJ</th> 
            <th style='font-weight:bold;text-align:center !important;padding:4px;'>Total</th>        
          </tr>
        </thead>
        <tbody> $tr_line</tbody>
      </table>";
    }
}

  public function get_value($ids,$cat){

   $this->get_value($ids,$cat);
    $ids = $row[0]['ids']; 
    $this->get_value($ids);
  }

}
?>