File: /home/cafsindia/crm_cafsindia_com/dashboard/am_call_back.php
<?php
include('db_connect.php');
error_reporting(0);
$frm = $_POST['frm'];
if($frm === "get_tl_info"){
$emp_role = $_POST['emp_role'];
$emp_cat = $_POST['emp_cat'];
$emp_id = $_POST['emp_id'];
echo "SAT $emp_role :: $emp_cat :: $emp_id";
if($emp_role === "6"){
$team = $this->db->query("SELECT teammembers,(LENGTH(teammembers) - LENGTH(REPLACE(teammembers, ',', '')) + 1) as count FROM `ospos_team` where teamleader = '$emp_id' and deleted = '0'");
$team_info = $team->result_array();
$map_crm = $team_info[0]['teammembers'];
$team_ids = explode(",",$map_crm);
$team_ids[] = $emp_id;
$team_ids = implode(",",$team_ids);
$map_count =$team_info[0]['count'];
$team_count = $map_count;
//Portfolio Team Leader
if($emp_cat === "2"){
/* Head box - START */
//Box 1
// $team_map_qry = $this->db->query("SELECT count(*) as count FROM `ospos_lead_type_info` where lead_status='9' and ospos_lead_type_info.created_by in ($map_crm) and met_date like '$cur_month%'");
$team_map_qry = $this->db->query( "SELECT IFNULL(count(*),0) as count FROM `ospos_lead_type_info` inner join ospos_met_log on ospos_lead_type_info.lead_id=ospos_met_log.log_lead_id where lead_status = '9' and ospos_lead_type_info.status='1' and ospos_lead_type_info.created_by in ($map_crm) and ospos_met_log.met_date like '$cur_month%' and ospos_met_log.first_met='1'");
$map_met_result = $team_map_qry->result_array();
$map_team_met = $map_met_result[0]['count'];
$avg_map_met = round($map_team_met/$team_count);
//Box 2
// $team_met_qry = $this->db->query("SELECT count(*) as count FROM `ospos_lead_type_info` where lead_status='9' and ospos_lead_type_info.created_by in ($team_ids) and met_date like '$cur_month%'");
$team_met_qry = $this->db->query( "SELECT IFNULL(count(*),0) as count FROM `ospos_lead_type_info` inner join ospos_met_log on ospos_lead_type_info.lead_id=ospos_met_log.log_lead_id where lead_status = '9' and ospos_lead_type_info.status='1' and ospos_lead_type_info.created_by in ($team_ids) and ospos_met_log.met_date like '$cur_month%' and ospos_met_log.first_met='1'");
$team_met_result = $team_met_qry->result_array();
$team_met = $team_met_result[0]['count'];
$team_target_qry = $this->db->query("SELECT IFNULL(met,0) as met FROM `ospos_incentives` where role = '$emp_role' and category = '$emp_cat' and grade='$emp_grade' and from_date like '$cur_month%' and to_date like '$cur_month%' ");
$team_target_result = $team_target_qry->result_array();
$team_target = $team_target_result[0]['met'];
//Box 3
$date = date("d");
//Required Avg
$team_avg_trgt = ($team_target / $team_count) - ($team_met / $team_count) ;
$team_ra = round($team_avg_trgt / ($last_day - $date),1);
//Required Run Rate
$team_rrr = round(($team_target - $team_met) / ($last_day - $date),1);
//Box 4
$team_log_qry = $this->db->query("SELECT count(*) as count FROM `ospos_lead_type_info` inner join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id where prospect_level='4' and ospos_lead_type_info.created_by in ($team_ids) and login_date like '$cur_month%' and ospos_leads_product.status = '1'");
$team_log_result = $team_log_qry->result_array();
$team_log = $team_log_result[0]['count'];
$team_con_ratio = round(((int)$team_log / (int)$team_met) * 100);
$team_nop_qry = $this->db->query("SELECT count(*) as count,IFNULL(sum(net_premium),0) as value FROM `ospos_lead_type_info` inner join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id where ospos_lead_type_info.created_by in ($team_ids) and ncd like '$cur_month%' and ospos_leads_product.status = '1'");
$team_nop_result = $team_nop_qry->result_array();
$team_nop = $team_nop_result[0]['count'];
$team_value = $team_nop_result[0]['value'];
//Box 5
$team_app_qry = $this->db->query("SELECT count(*) as count FROM `ospos_lead_type_info` where lead_status='1' and ospos_lead_type_info.created_by in ($team_ids) and ncd like '$cur_month%'");
$team_app_result = $team_app_qry->result_array();
$team_app = $team_app_result[0]['count'];
//Box 6
$team_lastmet_qry = $this->db->query("SELECT IFNULL(count(*),0) as count FROM `ospos_lead_type_info` inner join ospos_met_log on ospos_lead_type_info.lead_id=ospos_met_log.log_lead_id where lead_status = '9' and ospos_lead_type_info.status='1' and ospos_lead_type_info.created_by in ($team_ids) and ospos_met_log.met_date like '$last_month%'");
$team_lastmet_result = $team_lastmet_qry->result_array();
$team_lastmet = $team_lastmet_result[0]['count'];
//Box 8
$team_oc_qry = $this->db->query("SELECT COUNT(*) as count FROM `ospos_lead_type_info`where lead_mode = '2' and created_by in ($team_ids) and created_date LIKE '$cur_month%'");
$team_oc_result = $team_oc_qry->result_array();
$team_oc = $team_oc_result[0]['count'];
/* Head box - END */
/*Leads - START */
// Today Count
$today_team_lead = $this->db->query("SELECT count(*) as count,lead_status,statusname FROM `ospos_lead_type_info` left join ospos_lead_status on status_id = lead_status where ospos_lead_type_info.lead_mode='1' and ospos_lead_type_info.created_by in ($team_ids) and ncd like '$today%' GROUP by lead_status");
$today_team_lead_rslt = $today_team_lead->result_array();
$today_team_app = 0;
$today_team_follw = 0;
$today_team_drop = 0;
foreach ($today_team_lead_rslt as $key => $value) {
$count = $value['count'];
$statusname = $value['statusname'];
if($statusname === "Appointment"){
$today_team_app = $count;
}else
if($statusname === "Follow Up"){
$today_team_follw = $count;
}else
if($statusname === "Dropped"){
$today_team_drop = $count;
}
}
$today_team_lead_cr = $this->db->query("SELECT count(*) as count,lead_status,statusname FROM `ospos_lead_type_info` left join ospos_lead_status on status_id = lead_status where ospos_lead_type_info.lead_mode='1' and ospos_lead_type_info.created_by in ($team_ids) and created_date like '$today%' GROUP by lead_status");
$today_team_lead_rslt_cr = $today_team_lead_cr->result_array();
$today_team_app_cr = 0;
$today_team_follw_cr = 0;
$today_team_drop_cr = 0;
foreach ($today_team_lead_rslt_cr as $key => $value) {
$count = $value['count'];
$statusname = $value['statusname'];
if($statusname === "Appointment"){
$today_team_app_cr = $count;
}else
if($statusname === "Follow Up"){
$today_team_follw_cr = $count;
}else
if($statusname === "Dropped"){
$today_team_drop_cr = $count;
}
}
// Tomorrow count
$tmrw_team_lead = $this->db->query("SELECT count(*) as count,lead_status,statusname FROM `ospos_lead_type_info` left join ospos_lead_status on status_id = lead_status where ospos_lead_type_info.lead_mode='1' and ospos_lead_type_info.created_by in ($team_ids) and ncd like '$tmrw%' GROUP by lead_status");
$tmrw_team_lead_rslt = $tmrw_team_lead->result_array();
$tmrw_team_app = 0;
$tmrw_team_follw = 0;
$tmrw_team_drop = 0;
foreach ($tmrw_team_lead_rslt as $key => $value) {
$count = $value['count'];
$statusname = $value['statusname'];
if($statusname === "Appointment"){
$tmrw_team_app = $count;
}else
if($statusname === "Follow Up"){
$tmrw_team_follw = $count;
}
}
// Month count
$month_team_lead = $this->db->query("SELECT count(*) as count,lead_status,statusname FROM `ospos_lead_type_info` left join ospos_lead_status on status_id = lead_status where ospos_lead_type_info.lead_mode='1' and ospos_lead_type_info.created_by in ($team_ids) and ncd like '$cur_month%' GROUP by lead_status");
$month_team_lead_rslt = $month_team_lead->result_array();
$month_team_app = 0;
$month_team_follw = 0;
$month_team_drop = 0;
foreach ($month_team_lead_rslt as $key => $value) {
$count = $value['count'];
$statusname = $value['statusname'];
if($statusname === "Appointment"){
$month_team_app = $count;
}else
if($statusname === "Follow Up"){
$month_team_follw = $count;
}else
if($statusname === "Dropped"){
$month_team_drop = $count;
}
}
$month_team_lead_cr = $this->db->query("SELECT count(*) as count,lead_status,statusname FROM `ospos_lead_type_info` left join ospos_lead_status on status_id = lead_status where ospos_lead_type_info.lead_mode='1' and ospos_lead_type_info.created_by in ($team_ids) and created_date like '$cur_month%' GROUP by lead_status");
$month_team_lead_rslt_cr = $month_team_lead_cr->result_array();
$month_team_app_cr = 0;
$month_team_follw_cr = 0;
$month_team_drop_cr = 0;
foreach ($month_team_lead_rslt_cr as $key => $value) {
$count = $value['count'];
$statusname = $value['statusname'];
if($statusname === "Appointment"){
$month_team_app_cr = $count;
}else
if($statusname === "Follow Up"){
$month_team_follw_cr = $count;
}else
if($statusname === "Dropped"){
$month_team_drop_cr = $count;
}
}
/* Lead - END*/
/*Goal Sheet - START */
$goal_sheet = $this->db->query("SELECT met,conversion_ratio FROM `ospos_goal_sheet` where emp_id = '$emp_id' AND role = '$emp_role' AND category = '$emp_cat' AND (YEAR(from_date) = '$cur_year' or YEAR(to_date) = '$cur_year')");
$goal_sheet_rslt = $goal_sheet->result_array();
$req_met = $goal_sheet_rslt[0]['met'];
$conversion_ratio = $goal_sheet_rslt[0]['conversion_ratio'];
$actual_month = round($req_met / 12);
//Pending
$met_qry=$this->db->query("SELECT count(DISTINCT(lead_id)) as count FROM `ospos_lead_type_info` where lead_status='9' and ospos_lead_type_info.created_by = '$emp_id' and ncd like '$cur_year%' and ncd not like '$cur_month%'");
$met_result = $met_qry->result_array();
$met_count = $met_result[0]['count'];
$fnl_met_count = $req_met - $met_count;
//$Required = $actual_month + $fnl_met_count;
//No of LOP
$login_qry=$this->db->query("SELECT count(DISTINCT(lead_id)) as count FROM `ospos_lead_type_info` where prospect_level='4' and ospos_lead_type_info.created_by = '$emp_id' and ncd like '$cur_year%'");
$login_result = $login_qry->result_array();
$login_count = $login_result[0]['count'];
/*Goal Sheet - END */
/*RM Status - START */
// Created date
$rm_list = $this->db->query("SELECT rm_name,first_name from ospos_lead_type_info left join ospos_employees on ospos_lead_type_info.rm_name = ospos_employees.id left join ospos_people on ospos_employees.person_id = ospos_people.person_id inner join ospos_met_log on ospos_lead_type_info.lead_id=ospos_met_log.log_lead_id where ospos_lead_type_info.created_by in ($team_ids) and lead_status = '9' and ospos_met_log.met_date like '$cur_month%' and ospos_met_log.first_met='1' GROUP by rm_name");
$rm_list_rslt = $rm_list->result_array();
$rm_line = "";
foreach ($rm_list_rslt as $key => $value) {
$crdate_hot = 0;
$crdate_warm = 0;
$crdate_drop = 0;
$crdate_log = 0;
$rm_name = $value['rm_name'];
$first_name = $value['first_name'];
$rm_met = $this->db->query("SELECT IFNULL(count(*),0) as count FROM `ospos_lead_type_info` inner join ospos_met_log on ospos_lead_type_info.lead_id=ospos_met_log.log_lead_id where ospos_lead_type_info.created_by in ($team_ids) and lead_status = '9' and ospos_met_log.met_date >= DATE(NOW()) + INTERVAL -30 DAY and ospos_met_log.first_met='1' and ospos_lead_type_info.rm_name = '$rm_name' group by prospect_level");
$rm_met_rslt = $rm_met->row();
$tot_met_count = $rm_met_rslt->count;
$tm_rm_met = $this->db->query("SELECT IFNULL(count(*),0) as count FROM `ospos_lead_type_info` inner join ospos_met_log on ospos_lead_type_info.lead_id=ospos_met_log.log_lead_id where ospos_lead_type_info.created_by in ($team_ids) and lead_status = '9' and ospos_met_log.met_date like '$cur_month%' and ospos_met_log.first_met='1' and ospos_lead_type_info.rm_name = '$rm_name' ");
$tm_rm_met_rslt = $tm_rm_met->row();
$tm_tot_met_count = $tm_rm_met_rslt->count;
$rm_crdate = $this->db->query("SELECT IFNULL(count(*),0) as count,prospect_level,prospect_name FROM `ospos_lead_type_info` left JOIN ospos_prospect_level on prospect_id = prospect_level inner join ospos_met_log on ospos_lead_type_info.lead_id=ospos_met_log.log_lead_id where ospos_lead_type_info.created_by in ($team_ids) and lead_status = '9' and ospos_met_log.met_date like '$cur_month%' and ospos_met_log.first_met='1' and ospos_lead_type_info.rm_name = '$rm_name' GROUP by prospect_level");
$rm_crdate_rslt = $rm_crdate->result_array();
foreach ($rm_crdate_rslt as $key => $value) {
$count = $value['count'];
$prospect_name = $value['prospect_name'];
if($prospect_name === "Hot"){
$crdate_hot = $count;
}else
if($prospect_name === "Warm"){
$crdate_warm = $count;
}else
if($prospect_name === "Dropped"){
$crdate_drop = $count;
}
if($prospect_name === "Login"){
$crdate_log = $count;
}
}
$rm_log_count = $this->db->query("SELECT IFNULL(count(*),0) as count,prospect_level,prospect_name FROM `ospos_lead_type_info` left JOIN ospos_prospect_level on prospect_id = prospect_level inner join ospos_met_log on ospos_lead_type_info.lead_id=ospos_met_log.log_lead_id where ospos_lead_type_info.created_by in ($team_ids) and lead_status = '9' and ospos_met_log.met_date >= DATE(NOW()) + INTERVAL -30 DAY and ospos_lead_type_info.rm_name = '$rm_name' and prospect_level = '4' and ospos_met_log.first_met='1' ");
$rm_log_countrslt = $rm_log_count->result();
$conv_log = $rm_log_countrslt[0]->count;
$rm_cratio = round(((int)$conv_log / (int)$tot_met_count)*100);
$rm_line .= "<tr>
<td>$first_name</td>
<td class='cursor' onclick=get_rm_lead_info($emp_role,$emp_cat,'$team_ids',9,'MET',$rm_name);>$tm_tot_met_count</td>
<td class='cursor' onclick=get_rm_lead_info($emp_role,$emp_cat,'$team_ids',4,'TEAM',$rm_name);>$crdate_log</td>
<td class='cursor' onclick=get_rm_lead_info($emp_role,$emp_cat,'$team_ids',1,'TEAM',$rm_name);>$crdate_hot</td>
<td class='cursor' onclick=get_rm_lead_info($emp_role,$emp_cat,'$team_ids',2,'TEAM',$rm_name);>$crdate_warm</td>
<td class='cursor' onclick=get_rm_lead_info($emp_role,$emp_cat,'$team_ids',3,'TEAM',$rm_name);>$crdate_drop</td>
<td>$rm_cratio%</td>
</tr>";
}
// C-Ratio
$rm_crdate_nop = $this->db->query("SELECT IFNULL(count(*),0) as count FROM `ospos_lead_type_info` where ospos_lead_type_info.created_by in ($team_ids) and created_date like '$cur_month%'");
$rm_crdate_nop_rslt = $rm_crdate_nop->result_array();
$crdate_nop = $rm_crdate_nop_rslt[0]['count'];
$rm_crdate_log = $this->db->query("SELECT IFNULL(count(*),0) as count FROM `ospos_lead_type_info` INNER join ospos_leads_product on lead_type_id = lead_info_id where ospos_lead_type_info.created_by in ($team_ids) and ospos_lead_type_info.created_date like '$cur_month%' and ospos_leads_product.status = '1'");
$rm_crdate_log_rslt = $rm_crdate_log->result_array();
$rm_crdate_log = $rm_crdate_log_rslt[0]['count'];
$crdate_cratio = round(((int)$rm_crdate_log / (int)$crdate_nop) * 100);
$rm_ncd_nop = $this->db->query("SELECT IFNULL(count(*),0) as count FROM `ospos_lead_type_info` where ospos_lead_type_info.created_by in ($team_ids) and ncd like '$cur_month%'");
$rm_ncd_nop_rslt = $rm_ncd_nop->result_array();
$ncd_nop = $rm_ncd_nop_rslt[0]['count'];
$rm_ncd_log = $this->db->query("SELECT IFNULL(count(*),0) as count FROM `ospos_lead_type_info` INNER join ospos_leads_product on lead_type_id = lead_info_id where ospos_lead_type_info.created_by in ($team_ids) and ospos_lead_type_info.ncd like '$cur_month%' and ospos_leads_product.status = '1'");
$rm_ncd_log_rslt = $rm_ncd_log->result_array();
$ncd_log = $rm_ncd_log_rslt[0]['count'];
$ncd_cratio = round(((int)$ncd_log / (int)$ncd_nop) * 100);
//Total login value
$login_value_qry = $this->db->query("SELECT IFNULL(sum(net_premium),0) as net_premium FROM `ospos_lead_type_info` INNER JOIN ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id where ospos_lead_type_info.created_by in ($team_ids)");
$login_value_result = $login_value_qry->row();
$tot_log_val = $login_value_result->net_premium;
//
/*RM Status -END */
/*
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Total Appointments in TEAM (Depends on NCD Current Month)'>
<div class='dashbox dorange'>
<table>
<tr>
<td class='pd10'><i class='fa fa-tags fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>Appointments</h5> <span class='bold'> $team_app</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Other Campaing Leads'>
<div class='dashbox brown'>
<table>
<tr>
<td class='pd10'><i class='fa fa-random fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>OCL</h5> <span class='bold'> $team_oc</span></td>
</tr>
</table>
</div>
</div>
*/
echo "<div class='col-md-12 pd0 dash_header'>
<h3 class='text-center headtxt'>CRM Portfolio Team Leader</h3>
<div class='row mg0 mgb15'>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Current Avg Met / Mapped CRM Total Met'>
<div class='dashbox orange'>
<table>
<tr>
<td class='pd10'><i class='fa fa-heartbeat fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>Avg Met</h5> <span class='bold'>$avg_map_met/$map_team_met</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Team Met / Target'>
<div class='dashbox red'>
<table>
<tr>
<td class='pd10'><i class='fa fa-users fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>Met</h5> <span class='bold'> $team_met/$team_target</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Required Avg / Required Run Rate'>
<div class='dashbox blue'>
<table>
<tr>
<td class='pd10'><i class='fa fa-tachometer fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>Required</h5> <span class='bold'> $team_ra/$team_rrr</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Conversion / NOP & Value'>
<div class='dashbox violet'>
<table>
<tr>
<td class='pd10'><i class='fa fa-thermometer-half fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>Con/NOP & Value</h5> <span class='bold'> $team_con_ratio / $team_nop & $team_value</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Last Month Total Met / Avg. Met Count'>
<div class='dashbox yellow'>
<table>
<tr>
<td class='pd10'><i class='fa fa-history fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>Last Month Met</h5> <span class='bold'> $team_lastmet</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Days to go'>
<div class='dashbox teal'>
<table>
<tr>
<td class='pd10'><i class='fa fa-hourglass-half fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>Days</h5> <span class='bold'> $DaysRemaining</span></td>
</tr>
</table>
</div>
</div>
</div>
</div>
<div class='col-md-12 pd0 divider'></div>
<div class='row mg0'>
<div class='col-md-12'>
<div class='col-md-6 pd0'>
<div class='row mg0'>
<div class='col-md-12 pdl0'>
<table class='table table-bordered table-hover col-style'>
<thead>
<tr>
<th>Leads</th>
<th>Appointment</th>
<th>Follow up</th>
</tr>
</thead>
<tbody>
<tr>
<td >Today</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$team_ids',1,'T','');>$today_team_app</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$team_ids',2,'T','');>$today_team_follw</td>
</tr>
<tr>
<td >Tomorrow</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$team_ids',1,'TW','');>$tmrw_team_app</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$team_ids',2,'TW','');>$tmrw_team_follw</td>
</tr>
<tr>
<td >Month</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$team_ids',1,'M','');>$month_team_app</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$team_ids',2,'M','');>$month_team_follw</td>
</tr>
</tbody>
</table>
</div>
<div class='col-md-12 pdl0'>
<table class='table table-bordered table-hover col-style'>
<thead>
<tr>
<th>Generated</th>
<th>Appointment</th>
<th>Follow up</th>
<th>Dropped</th>
</tr>
</thead>
<tbody>
<tr>
<td >Today</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$team_ids',1,'T','created_date');>$today_team_app_cr</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$team_ids',2,'T','created_date');>$today_team_follw_cr</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$team_ids',3,'T','created_date');>$today_team_drop_cr</td>
</tr>
<tr>
<td >Month</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$team_ids',1,'M','created_date');>$month_team_app_cr</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$team_ids',2,'M','created_date');>$month_team_follw_cr</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$team_ids',3,'M','created_date');>$month_team_drop_cr</td>
</tr>
</tbody>
</table>
</div>
<div class='col-md-12 pdl0'>
<table class='table table-bordered table-hover col-style'>
<thead>
<tr>
<th>Goal Sheet</th>
<th>This Month Actual</th>
<th>Pending</th>
<th>Total Required</th>
</tr>
</thead>
<tbody>
<tr>
<td >Met</td>
<td>$actual_month</td>
<td>$fnl_met_count</td>
<td>$req_met</td>
</tr>
<tr>
<td >Conversion Ratio</td>
<td>$conversion_ratio</td>
<td>-</td>
<td>-</td>
</tr>
<tr>
<td >NOP</td>
<td colspan='3'>$login_count</td>
</tr>
</tbody>
</table>
</div>
</div>
</div>
<div class='col-md-6 pd0'>
<div class='col-md-12 col-style'>
<div id='ribbon'>Login $tot_log_val</div>
<div id='crm_chart' style='height:315px;'></div>
</div>
</div>
</div>
<div class='col-md-12'>
<div class='row mg0'>
<div class='col-md-6 pdl0'>
<table class='table table-bordered table-hover col-style' >
<thead>
<tr>
<th>RM Name</th>
<th>Met</th>
<th>Login</th>
<th>Hot</th>
<th>Warm</th>
<th>Dropped</th>
<th>C- Ratio</th>
</tr>
</thead>
<tbody>
$rm_line
</tbody>
</table>
</div>
<div class='col-md-6 pdl0'>
<div class='form-group' style='margin-bottom:4px;float:right;'>
<select class='form-control input-sm' onchange=get_team_info(this.value,$emp_role,$emp_cat,'$map_crm',''); >
<option value='T'>Today</option>
<option value='Y'>Yeasterday</option>
<option value='TW'>Tomorrow</option>
<option value='M'>Month</option>
</select>
</div>
<table class='table table-bordered table-hover col-style' id='team_info_table'>
<thead>
<tr>
<th>Team CRM</th>
<th>Zero Days</th>
<th>Met</th>
<th>Appointment</th>
<th>Follow up</th>
<th>C- Ratio</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</div>
</div>
</div>";
}
//Health Team Leader
if($emp_cat === "3"){
/* Head box - START */
//Box 1
$team_qry = $this->db->query("SELECT IFNULL(sum(net_premium),0) as value FROM `ospos_lead_type_info` inner join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id where prospect_level='4' and lead_mode='1' and ospos_lead_type_info.created_by in ($team_ids) and ospos_leads_product.status = '1' and login_date like '$cur_month%'");
$value_result = $team_qry->result_array();
$team_value = $value_result[0]['value'];
$avg_map_value = round($team_value/$team_count);
//Box 2
$team_value_qry = $this->db->query("SELECT IFNULL(sum(net_premium),0) as value FROM `ospos_lead_type_info` inner join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id where prospect_level='4' and ospos_leads_product.status = '1' and lead_mode='1' and ospos_lead_type_info.created_by in ($team_ids) and login_date like '$cur_month%'");
$team_value_result = $team_value_qry->result_array();
$team_value = $team_value_result[0]['value'];
$team_target_qry = $this->db->query("SELECT IFNULL(value,0) as value FROM `ospos_incentives` where role = '$emp_role' and grade='$emp_grade' and category = '$emp_cat' and from_date like '$cur_month%' and to_date like '$cur_month%' ");
$team_target_result = $team_target_qry->result_array();
$team_target = $team_target_result[0]['value'];
if(!$team_target){
$team_target = 0;
}
$team_ach_per = round(($team_value / $team_target) * 100,1);
//Box 3
$team_map_qry = $this->db->query("SELECT count(distinct(lead_id)) as count,IFNULL(sum(net_premium),0) as value FROM `ospos_lead_type_info` inner join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id where prospect_level='4' and lead_mode='1' and ospos_leads_product.status = '1' and ospos_lead_type_info.created_by in ($map_crm) and login_date like '$cur_month%'");
$map_value_result = $team_map_qry->result_array();
$map_map_nop = $map_value_result[0]['count'];
$map_team_value = $map_value_result[0]['value'];
$map_avg_value = round($map_team_value / $map_map_nop );
//Box 6
$team_oc_qry = $this->db->query("SELECT COUNT(*) as count FROM `ospos_lead_type_info`where lead_mode = '2' and created_by in ($team_ids) and created_date LIKE '$cur_month%'");
$team_oc_result = $team_oc_qry->result_array();
$team_oc = $team_oc_result[0]['count'];
//Box 7
$zero_in_month = $this->db->query("SELECT count(DISTINCT(login_date)) as login_date FROM `ospos_lead_type_info` inner join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id where prospect_level = '4' and ospos_leads_product.status = '1' and ospos_lead_type_info.created_by in ($team_ids) and login_date BETWEEN '$first_day' and '$yesterday'"); //$first_day , $yesterday
$zero_in_month_result = $zero_in_month->result_array();
$month = (int)$zero_in_month_result[0]['login_date'];
$days = explode("-", $yesterday);
$days = (int)$days[2];
$month = $days - $month;
// Today Count
$today_lead = $this->db->query("SELECT count(*) as count,lead_status,prospect_name,IFNULL(sum(expected_value),0) as amount FROM `ospos_lead_type_info` left join ospos_prospect_level on prospect_id = prospect_level where ospos_lead_type_info.lead_mode='1' and ospos_lead_type_info.created_by in ($team_ids) and ncd like '$today%' GROUP by prospect_level");
$today_lead_rslt = $today_lead->result_array();
$today_hot = "0 / 0";
$today_warm = "0 / 0";
$today_log = "0 / 0";
foreach ($today_lead_rslt as $key => $value) {
$count = $value['count'];
$amount = $value['amount'];
$statusname = $value['prospect_name'];
if($statusname === "Hot"){
$today_hot = "$count / $amount";
}else
if($statusname === "Warm"){
$today_warm = "$count / $amount";
}else
if($statusname === "Login"){
$today_log = "$count / $amount";
}
}
$today_lead_cr = $this->db->query("SELECT count(*) as count,lead_status,prospect_name,IFNULL(sum(expected_value),0) as amount FROM `ospos_lead_type_info` left join ospos_prospect_level on prospect_id = prospect_level where ospos_lead_type_info.lead_mode='1' and ospos_lead_type_info.created_by in ($team_ids) and created_date like '$today%' GROUP by prospect_level");
$today_lead_rslt_cr = $today_lead_cr->result_array();
$today_hot_cr = "0 / 0";
$today_warm_cr = "0 / 0";
$today_log_cr = "0 / 0";
foreach ($today_lead_rslt_cr as $key => $value) {
$count = $value['count'];
$amount = $value['amount'];
$statusname = $value['prospect_name'];
if($statusname === "Hot"){
$today_hot_cr = "$count / $amount";
}else
if($statusname === "Warm"){
$today_warm_cr = "$count / $amount";
}else
if($statusname === "Login"){
$today_log_cr = "$count / $amount";
}
}
// Tomorrow count
$tmrw_lead = $this->db->query("SELECT count(*) as count,lead_status,prospect_name,IFNULL(sum(expected_value),0) as amount FROM `ospos_lead_type_info` left join ospos_prospect_level on prospect_id = prospect_level where ospos_lead_type_info.lead_mode='1' and ospos_lead_type_info.created_by in ($team_ids) and ncd like '$tmrw%' GROUP by prospect_level");
$tmrw_lead_rslt = $tmrw_lead->result_array();
$tmrw_hot = "0 / 0";
$tmrw_warm = "0 / 0";
foreach ($tmrw_lead_rslt as $key => $value) {
$count = $value['count'];
$amount = $value['amount'];
$statusname = $value['prospect_name'];
if($statusname === "Hot"){
$tmrw_hot = "$count / $amount";
}else
if($statusname === "Warm"){
$tmrw_warm = "$count / $amount";
}
}
// Month count
$month_lead = $this->db->query("SELECT count(*) as count,lead_status,prospect_name,IFNULL(sum(expected_value),0) as amount FROM `ospos_lead_type_info` left join ospos_prospect_level on prospect_id = prospect_level where ospos_lead_type_info.lead_mode='1' and ospos_lead_type_info.created_by in ($team_ids) and ncd like '$cur_month%' GROUP by prospect_level");
$month_lead_rslt = $month_lead->result_array();
$month_hot = "0 / 0";
$month_warm = "0 / 0";
$month_log = "0 / 0";
foreach ($month_lead_rslt as $key => $value) {
$count = $value['count'];
$amount = $value['amount'];
$statusname = $value['prospect_name'];
if($statusname === "Hot"){
$month_hot = "$count / $amount";
}else
if($statusname === "Warm"){
$month_warm = "$count / $amount";
}else
if($statusname === "Login"){
$month_log = "$count / $amount";
}
}
$month_lead_cr = $this->db->query("SELECT count(*) as count,lead_status,prospect_name,IFNULL(sum(expected_value),0) as amount FROM `ospos_lead_type_info` left join ospos_prospect_level on prospect_id = prospect_level where ospos_lead_type_info.lead_mode='1' and ospos_lead_type_info.created_by in ($team_ids) and created_date like '$cur_month%' GROUP by prospect_level");
$month_lead_rslt_cr = $month_lead_cr->result_array();
$month_hot_cr = "0 / 0";
$month_warm_cr = "0 / 0";
$month_log_cr = "0 / 0";
foreach ($month_lead_rslt_cr as $key => $value) {
$count = $value['count'];
$amount = $value['amount'];
$statusname = $value['prospect_name'];
if($statusname === "Hot"){
$month_hot_cr = "$count / $amount";
}else
if($statusname === "Warm"){
$month_warm_cr = "$count / $amount";
}else
if($statusname === "Login"){
$month_log_cr = "$count / $amount";
}
}
/*Leads - END */
/*Issuance Status - START*/
$issuance = $this->db->query("SELECT count(DISTINCT(lead_id)) as count,IFNULL(issuance_status,'Not Issued') as status,IFNULL(sum(net_premium ),0) as netamount FROM `ospos_lead_type_info` left join ospos_leads_product on lead_info_id = lead_type_id left join ospos_happy_calling on call_product_id = customer_product_id where ospos_lead_type_info.created_by in ($team_ids) and ospos_lead_type_info.ncd like '$cur_month%' and ospos_leads_product.status = '1' and ospos_lead_type_info.prospect_level = '4' GROUP by issuance_status");
$issuance_rslt = $issuance->result_array();
$issued = 0;
$not_issued = 0;
$med_pend = 0;
foreach ($issuance_rslt as $key => $value) {
$count = $value['count'];
$netamount = $value['netamount'];
$issued_sts = $value['status'];
if($issued_sts === "Issued"){
$issued = $netamount;
}
if($issued_sts === "Issued"){
$issued = $netamount;
}else
if($issued_sts === "Not Issued"){
$not_issued = $netamount;
}else
if($issued_sts === "Medical Pending"){
$med_pend = $netamount;
}
}
/*Issuance Status - END*/
/* Team Result - START*/
$hl_crm_line = "";
$hl_crm_hot = 0;
$hl_crm_warm = 0;
$hl_crm_ids = explode(",",$map_crm);
foreach($hl_crm_ids as $id){
$crm_info = $this->db->query("SELECT first_name FROM `ospos_employees` left join ospos_people on ospos_people.person_id = ospos_employees.id where ospos_employees.id = '$id'");
$crm_info_rslt = $crm_info->row();
$crm_name = $crm_info_rslt->first_name;
$log_info = $this->db->query("SELECT count(DISTINCT(lead_id)) as count,IFNULL(sum(net_premium),0) as netamount FROM ospos_lead_type_info INNER join ospos_leads_product on lead_info_id = lead_type_id where ospos_lead_type_info.lead_mode='1' and ospos_lead_type_info.created_by = '$id' and ospos_leads_product.status = '1' and ospos_lead_type_info.prospect_level = '4' and ospos_lead_type_info.ncd like '$cur_month%'");
$log_info_rslt = $log_info->row();
$log_val = $log_info_rslt->netamount;
$log_info = $this->db->query("SELECT count(DISTINCT(lead_id)) as nop,IFNULL(sum(net_premium),0) as netamount FROM ospos_lead_type_info inner join ospos_leads_product on lead_info_id = lead_type_id where ospos_lead_type_info.lead_mode='1' and ospos_lead_type_info.created_by = '$id' and ospos_lead_type_info.prospect_level = '4' and ospos_leads_product.status = '1' and ospos_lead_type_info.ncd like '$cur_month%'");
$log_info_rslt = $log_info->row();
$nop = $log_info_rslt->nop;
$log_val = $log_info_rslt->netamount;
$avg_premium = round((int)$log_val / (int)$nop);
$hl_crm_lead = $this->db->query("SELECT count(*) as count,prospect_name,IFNULL(sum(expected_value),0) as amount FROM `ospos_lead_type_info` left join ospos_prospect_level on prospect_id = prospect_level where ospos_lead_type_info.lead_mode='1' and ospos_lead_type_info.created_by in ($id) and ncd like '$cur_month%' GROUP by prospect_level");
$hl_crm_lead_rslt = $hl_crm_lead->result_array();
$hl_crm_hot = 0;
$hl_crm_warm = 0;
foreach ($hl_crm_lead_rslt as $key => $value) {
$count = $value['count'];
$prospect_name = $value['prospect_name'];
if($prospect_name === "Hot"){
$hl_crm_hot = $count;
}else
if($prospect_name === "Warm"){
$hl_crm_warm = $count;
}
}
$qry = $this->db->query("SELECT login_date FROM `ospos_lead_type_info` inner join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id where prospect_level='4' and ospos_leads_product.status = '1' and ospos_lead_type_info.created_by = '$id' order by login_date desc limit 0,1");
$result = $qry->result_array();
$login_date = $result[0]['login_date'];
if($login_date === ""){
$login_date =date('Y-m-01');
}
$now = time();
$your_date = strtotime($login_date);
$datediff = $now - $your_date;
$Zero_days = floor($datediff / (60 * 60 * 24));
$clr = "";
if((int)$Zero_days >= 5){
$clr = "style='color:#FF5252';";
}
$hl_crm_line .= "<tr $clr>
<td>$crm_name</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$id',4,'M','PROSPECT');>$log_val</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$id',4,'M','PROSPECT');>$nop</td>
<td>$avg_premium</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$id',1,'M','PROSPECT_NCD');>$hl_crm_hot</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$id',2,'M','PROSPECT_NCD');>$hl_crm_warm</td>
<td>$Zero_days</td>
</tr>";
}
//Total login value
$login_value_qry = $this->db->query("SELECT IFNULL(sum(net_premium),0) as net_premium FROM `ospos_lead_type_info` INNER JOIN ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id where ospos_leads_product.status = '1' and ospos_lead_type_info.created_by in ($team_ids)");
$login_value_result = $login_value_qry->row();
$tot_log_val = $login_value_result->net_premium;
/* Team Result - END */
/*
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Active / Total no. People Mapped'>
<div class='dashbox violet'>
<table>
<tr>
<td class='pd10'><i class='fa fa-sitemap fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>Mapped</h5> <span class='bold'> $map_count</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Team Met / Logged Value'>
<div class='dashbox dorange'>
<table>
<tr>
<td class='pd10'><i class='fa fa-users fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>Team Met</h5> <span class='bold'> -</span></td>
</tr>
</table>
</div>
</div>
*/
echo "<div class='col-md-12 pd0 dash_header'>
<h3 class='text-center headtxt'>CRM Health Team Leader</h3>
<div class='row mg0 mgb15'>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Team Avg Productivity'>
<div class='dashbox orange'>
<table>
<tr>
<td class='pd10'><i class='fa fa-tachometer fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>Productivity</h5> <span class='bold'>$avg_map_value</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Team Login Value / Target Value , Achie.%'>
<div class='dashbox red'>
<table>
<tr>
<td class='pd10'><i class='fa fa-sign-in fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>Login</h5> <span class='bold'> $team_value / $team_target , $team_ach_per</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Nop & AVG Premium (Mapped CRM)'>
<div class='dashbox blue'>
<table>
<tr>
<td class='pd10'><i class='fa fa-tags fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>NOP</h5> <span class='bold'> $map_map_nop & $map_avg_value</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Team Zero days in a month'>
<div class='dashbox yellow'>
<table>
<tr>
<td class='pd10'><i class='fa fa-user-md fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>Zero days</h5> <span class='bold'> $month</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Other Campaing Leads'>
<div class='dashbox brown'>
<table>
<tr>
<td class='pd10'><i class='fa fa-random fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>OCL</h5> <span class='bold'> $team_oc</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Days to go'>
<div class='dashbox teal'>
<table>
<tr>
<td class='pd10'><i class='fa fa-hourglass-half fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>Days</h5> <span class='bold'> $DaysRemaining</span></td>
</tr>
</table>
</div>
</div>
</div>
</div>
<div class='col-md-12 pd0 divider'></div>
<div class='row mg0'>
<div class='col-md-12'>
<div class='col-md-6 pdl0'>
<div class='row mg0'>
<div class='col-md-12 pd0'>
<table class='table table-bordered table-hover col-style'>
<thead>
<tr>
<th>Leads</th>
<th>Hot</th>
<th>Warm</th>
<th>Colleted</th>
</tr>
</thead>
<tbody>
<tr>
<td >Today</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$team_ids',1,'T','PROSPECT_NCD');>$today_hot</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$team_ids',2,'T','PROSPECT_NCD');>$today_warm</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$team_ids',4,'T','PROSPECT_NCD');>$today_log</td>
</tr>
<tr>
<td >Tomorrow</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$team_ids',1,'TW','PROSPECT_NCD');>$tmrw_hot</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$team_ids',2,'TW','PROSPECT_NCD');>$tmrw_warm</td>
<td> - </td>
</tr>
<tr>
<td >Month</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$team_ids',1,'M','PROSPECT_NCD');>$month_hot</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$team_ids',2,'M','PROSPECT_NCD');>$month_warm</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$team_ids',4,'M','PROSPECT_NCD');>$month_log</td>
</tr>
</tbody>
</table>
</div>
<div class='col-md-12 pd0'>
<table class='table table-bordered table-hover col-style'>
<thead>
<tr>
<th>Generated</th>
<th>Hot</th>
<th>Warm</th>
<th>Collected</th>
</tr>
</thead>
<tbody>
<tr>
<td >Today</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$team_ids',1,'T','PROSPECT_CR');>$today_hot_cr</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$team_ids',2,'T','PROSPECT_CR');>$today_warm_cr</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$team_ids',4,'T','PROSPECT_CR');>$today_log_cr</td>
</tr>
<tr>
<td >Month</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$team_ids',1,'M','PROSPECT_CR');>$month_hot_cr</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$team_ids',2,'M','PROSPECT_CR');>$month_warm_cr</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$team_ids',4,'M','PROSPECT_CR');>$month_log_cr</td>
</tr>
</tbody>
</table>
</div>
<div class='col-md-12 pd0'>
<table class='table table-bordered table-hover col-style'>
<thead>
<tr>
<th>Issuance Status</th>
<th>Issued</th>
<th>Not Issued</th>
<th>Medical Pending</th>
</tr>
</thead>
<tbody>
<tr>
<td >Current Month</td>
<td class='cursor' onclick=get_lead_info_issued($emp_role,$emp_cat,'$team_ids','issued','M','');>$issued</td>
<td class='cursor' onclick=get_lead_info_issued($emp_role,$emp_cat,'$team_ids','not_issued','M','');>$not_issued</td>
<td class='cursor' onclick=get_lead_info_issued($emp_role,$emp_cat,'$team_ids','med_pend','M','');>$med_pend</td>
</tr>
</tbody>
</table>
</div>
</div>
</div>
<div class='col-md-6 pdl0'>
<div class='row mg0'>
<div class='col-md-12 pd0'>
<div class='col-md-12 col-style'>
<div id='ribbon'>Login $tot_log_val</div>
<div id='crm_chart' style='height:315px;'></div>
</div>
</div>
</div>
</div>
</div>
<div class='col-md-12'>
<table class='table table-bordered table-hover col-style'>
<thead>
<tr>
<th>CRM Name</th>
<th>Value</th>
<th>NOP</th>
<th>Avg. Premium</th>
<th>Hot</th>
<th>Warm</th>
<th>Zero Days</th>
</tr>
</thead>
<tbody>
$hl_crm_line
</tbody>
</table>
</div>
</div>";
}
}
}
?>