File: /home/cafsindia/crm_cafsindia_com/application/models/Incentive_report_model.php
<?php
class incentive_report_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');
$emp_info = $this->get_employee_list($search, $filters, $rows, $limit_from, $sort, $order);
//print_r($emp_info);
$incentive = array();
foreach ($emp_info as $key => $value) {
$incentive_line = array();
$emp_id = $value->emp_id;
$username = $value->username;
$first_name = $value->first_name;
$role_name = $value->role_name;
$role_id = $value->role_id;
$category = $value->category;
$cat_name = $value->cat_name;
$grade_name = $value->grade_name;
$grade_id = $value->grade_id;
$nop = 0;
$netamount = 0;
$convertion = 0;
if(($role_id === "3") && ($category === "2")){
// CRM Portfolio
$this->db->from('incentives');
$this->db->where('grade',$grade_id);
$grade_info = $this->db->get()->result();
$met = $grade_info[0]->met;
$convertion_ratio = $grade_info[0]->convertion_ratio;
$credit = $grade_info[0]->credit;
$grade_value = $grade_info[0]->value;
$this->db->select('COUNT(DISTINCT ospos_lead_type_info.lead_id) as nop');
$this->db->from('lead_type_info');
$this->db->where('lead_type_info.created_by',$emp_id);
$this->db->where('lead_type_info.lead_status','9');
$this->db->where('DATE_FORMAT(ospos_lead_type_info.created_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($start_date).' AND '.$this->db->escape($end_date));
$tot_met_info = $this->db->get()->result();
$tot_met = $tot_met_info[0]->nop;
$this->db->select('COUNT(DISTINCT ospos_lead_type_info.lead_id) as nop,IFNULL(sum(net_premium),0) as netamount');
$this->db->from('lead_type_info');
$this->db->join('leads_product', 'leads_product.lead_info_id = lead_type_info.lead_id');
$this->db->where('lead_type_info.created_by',$emp_id);
$this->db->where('lead_type_info.lead_status','9');
$this->db->where('DATE_FORMAT(ospos_lead_type_info.created_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($start_date).' AND '.$this->db->escape($end_date));
$met_info = $this->db->get()->result();
$netamount = $met_info[0]->netamount;
$nop = $met_info[0]->nop;
$convertion_per = (int)$tot_met/ (int)$nop;
if((int)$convertion_per >= (int)$convertion_ratio){
$convertion = $grade_value;
}
}else
if(($role_id === "6") && ($category === "2")){
// CRM Portfolio Team leader
$this->db->from('incentives');
$this->db->where('grade',$grade_id);
$grade_info = $this->db->get()->result();
$met = $grade_info[0]->met;
$convertion_ratio = $grade_info[0]->convertion_ratio;
$credit = $grade_info[0]->credit;
$grade_value = $grade_info[0]->value;
$team = $this->get_team_info($emp_id);
$team_rslt = explode(",",$team->teammembers);
$team_rslt[] = $emp_id;
$this->db->select('COUNT(DISTINCT ospos_lead_type_info.lead_id) as nop');
$this->db->from('lead_type_info');
$this->db->where_in('lead_type_info.created_by',$team_rslt);
$this->db->where('lead_type_info.lead_status','9');
$this->db->where('DATE_FORMAT(ospos_lead_type_info.created_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($start_date).' AND '.$this->db->escape($end_date));
$tot_met_info = $this->db->get()->result();
$tot_met = $tot_met_info[0]->nop;
$this->db->select('COUNT(DISTINCT ospos_lead_type_info.lead_id) as nop,IFNULL(sum(net_premium),0) as netamount');
$this->db->from('lead_type_info');
$this->db->join('leads_product', 'leads_product.lead_info_id = lead_type_info.lead_id');
$this->db->where_in('lead_type_info.created_by',$team_rslt);
$this->db->where('lead_type_info.lead_status','9');
$this->db->where('DATE_FORMAT(ospos_lead_type_info.created_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($start_date).' AND '.$this->db->escape($end_date));
$met_info = $this->db->get()->result();
$netamount = $met_info[0]->netamount;
$nop = $met_info[0]->nop;
$convertion_per = (int)$tot_met/ (int)$nop;
if((int)$convertion_per >= (int)$convertion_ratio){
$convertion = $grade_value;
}
}else
if(($role_id === "3") && ($category === "3")){
// CRM Health
$this->db->select('COUNT(DISTINCT ospos_lead_type_info.lead_id) as nop,IFNULL(sum(net_premium),0) as netamount');
$this->db->from('lead_type_info');
$this->db->join('leads_product', 'leads_product.lead_info_id = lead_type_info.lead_id');
$this->db->where('lead_type_info.created_by',$emp_id);
$this->db->where('DATE_FORMAT(ospos_lead_type_info.created_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($start_date).' AND '.$this->db->escape($end_date));
$lead_info = $this->db->get()->result();
$netamount = $lead_info[0]->netamount;
$nop = $lead_info[0]->nop;
$this->db->from('incentives');
$this->db->where('grade',$grade_id);
$grade_info = $this->db->get()->result();
$cal_amt = 0;
$cal_avg = (int)$netamount / (int)$nop;
$canprocess = false;
foreach ($grade_info as $key => $value) {
$grade_value = $value->value;
$grade_nop = $value->nop;
$avg_premium = $value->avg_premium;
if((int)$nop >= (int)$grade_nop){
$cal_amt = $grade_value;
}
if((int)$cal_avg >= (int)$avg_premium){
$canprocess = true;
}
}
if($canprocess){
$convertion = $nop * $cal_amt;
}
}else
if(($role_id === "6") && ($category === "3")){
// CRM Health Team leader
$team = $this->get_team_info($emp_id);
$team_rslt = explode(",",$team->teammembers);
$team_rslt[] = $emp_id;
$this->db->select('COUNT(DISTINCT ospos_lead_type_info.lead_id) as nop,IFNULL(sum(net_premium),0) as netamount');
$this->db->from('lead_type_info');
$this->db->join('leads_product', 'leads_product.lead_info_id = lead_type_info.lead_id');
$this->db->where_in('lead_type_info.created_by',$emp_id);
$this->db->where('DATE_FORMAT(ospos_lead_type_info.created_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($start_date).' AND '.$this->db->escape($end_date));
$lead_info = $this->db->get()->result();
$netamount = $lead_info[0]->netamount;
$nop = $lead_info[0]->nop;
$this->db->from('incentives');
$this->db->where('grade',$grade_id);
$grade_info = $this->db->get()->result();
$cal_amt = 0;
$cal_avg = (int)$netamount / (int)$nop;
foreach ($grade_info as $key => $value) {
$productivity = $value->productivity;
$percentage = $value->percentage;
if((int)$cal_avg >= (int)$productivity){
$cal_amt = $percentage;
}
}
$convertion = ((int)$netamount * (int)$cal_amt) /100;
}else
if($role_id === "5"){
//RM
$this->db->select('COUNT(DISTINCT ospos_lead_type_info.lead_id) as nop,IFNULL(sum(net_premium),0) as netamount');
$this->db->from('lead_type_info');
$this->db->join('leads_product', 'leads_product.lead_info_id = lead_type_info.lead_id');
$this->db->where('lead_type_info.created_by',$emp_id);
$this->db->where('DATE_FORMAT(ospos_lead_type_info.created_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($start_date).' AND '.$this->db->escape($end_date));
$lead_info = $this->db->get()->result();
$netamount = $lead_info[0]->netamount;
$nop = $lead_info[0]->nop;
$this->db->from('incentives');
$this->db->where('grade',$grade_id);
$grade_info = $this->db->get()->result();
$slab_one_start = $grade_info[0]->slab_one_start;
$slab_one_end = $grade_info[0]->slab_one_end;
$slab_one_per = $grade_info[0]->slab_one_per;
$slab_two_start = $grade_info[0]->slab_two_start;
$slab_two_end = $grade_info[0]->slab_two_end;
$slab_two_per = $grade_info[0]->slab_two_per;
$slab_three_start = $grade_info[0]->slab_three_start;
$slab_three_end = $grade_info[0]->slab_three_end;
$slab_three_per = $grade_info[0]->slab_three_per;
$slab_four = $grade_info[0]->slab_four;
$slab_four_per = $grade_info[0]->slab_four_per;
if(((int)$netamount >= (int)$slab_one_start) && ((int)$netamount <= (int)$slab_one_end)){
$convertion = (int)$netamount / (int)$slab_one_per;
}else
if(((int)$netamount >= (int)$slab_two_start) && ((int)$netamount <= (int)$slab_two_end)){
$convertion = (int)$netamount / (int)$slab_two_per;
}else
if(((int)$netamount >= (int)$slab_three_start) && ((int)$netamount <= (int)$slab_three_end)){
$convertion = (int)$netamount / (int)$slab_three_per;
}else
if((int)$netamount >= (int)$slab_four){
$convertion = (int)$netamount / (int)$slab_four_per;
}
}else
if($role_id === "9"){
//RM Manger
$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('COUNT(DISTINCT ospos_lead_type_info.lead_id) as nop,IFNULL(sum(net_premium),0) as netamount');
$this->db->from('lead_type_info');
$this->db->join('leads_product', 'leads_product.lead_info_id = lead_type_info.lead_id');
$this->db->where_in('lead_type_info.created_by',$team_rslt);
$this->db->where('DATE_FORMAT(ospos_lead_type_info.created_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($start_date).' AND '.$this->db->escape($end_date));
$lead_info = $this->db->get()->result();
$netamount = $lead_info[0]->netamount;
$nop = $lead_info[0]->nop;
$this->db->from('incentives');
$this->db->where('grade',$grade_id);
$grade_info = $this->db->get()->result();
$slab_one_start = $grade_info[0]->slab_one_start;
$slab_one_end = $grade_info[0]->slab_one_end;
$slab_one_per = $grade_info[0]->slab_one_per;
$slab_two_start = $grade_info[0]->slab_two_start;
$slab_two_end = $grade_info[0]->slab_two_end;
$slab_two_per = $grade_info[0]->slab_two_per;
$slab_three_start = $grade_info[0]->slab_three_start;
$slab_three_end = $grade_info[0]->slab_three_end;
$slab_three_per = $grade_info[0]->slab_three_per;
$slab_four = $grade_info[0]->slab_four;
$slab_four_per = $grade_info[0]->slab_four_per;
if(((int)$netamount >= (int)$slab_one_start) && ((int)$netamount <= (int)$slab_one_end)){
$convertion = (int)$netamount / (int)$slab_one_per;
}else
if(((int)$netamount >= (int)$slab_two_start) && ((int)$netamount <= (int)$slab_two_end)){
$convertion = (int)$netamount / (int)$slab_two_per;
}else
if(((int)$netamount >= (int)$slab_three_start) && ((int)$netamount <= (int)$slab_three_end)){
$convertion = (int)$netamount / (int)$slab_three_per;
}else
if((int)$netamount >= (int)$slab_four){
$convertion = (int)$netamount / (int)$slab_four_per;
}
}
$incentive_line['emp_id'] = $emp_id;
$incentive_line['username'] = $username;
$incentive_line['first_name'] = $first_name;
$incentive_line['role_name'] = $role_name;
$incentive_line['role_id'] = $role_id;
$incentive_line['category'] = $category;
$incentive_line['cat_name'] = $cat_name;
$incentive_line['grade_name'] = $grade_name;
$incentive_line['netamount'] = $netamount;
$incentive_line['nop'] = $nop;
$incentive_line['convertion_ratio'] = round($convertion);
$incentive[] = $incentive_line;
}
return $incentive;
}
function get_employee_list($search, $filters, $rows, $limit_from, $sort, $order){
$this->db->select('employees.id as emp_id,employees.username as username,people.first_name as first_name,role.role_name as role_name,employees.role as role_id,employees.category as category, category.cat_name as cat_name,grade.grade as grade_name,employees.grade as grade_id');
$this->db->from('employees');
$this->db->join('people', 'people.person_id = employees.person_id');
$this->db->join('role', 'role.role_id = employees.role');
$this->db->join('category', 'category.cat_id = employees.category');
$this->db->join('grade', 'grade.grade_id = employees.grade');
$this->db->group_start();
$this->db->like('employees.username', $search);
$this->db->or_like('people.first_name', $search);
$this->db->or_like('role.role_name', $search);
$this->db->or_like('category.cat_name', $search);
$this->db->or_like('grade.grade', $search);
$this->db->group_end();
$this->db->where('employees.deleted',0);
return $this->db->get()->result();
//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();
}
public function get_found_rows($search, $filters){
$this->db->select('employees.username as username,people.first_name as first_name,role.role_name as role_name,category.cat_name as cat_name,grade.grade as grade_name,');
$this->db->from('employees');
$this->db->join('people', 'people.person_id = employees.person_id');
$this->db->join('role', 'role.role_id = employees.role');
$this->db->join('category', 'category.cat_id = employees.category');
$this->db->join('grade', 'grade.grade_id = employees.grade');
$this->db->where('employees.deleted',0);
$this->db->group_start();
$this->db->like('employees.username', $search);
$this->db->or_like('people.first_name', $search);
$this->db->or_like('role.role_name', $search);
$this->db->or_like('category.cat_name', $search);
$this->db->or_like('grade.grade', $search);
$this->db->group_end();
return $this->db->get()->num_rows();
}
public function get_multiple_info($lead_ids){
$this->db->from('employees');
$this->db->where('employees.deleted',0);
return $this->db->get()->result_array();
}
}
?>