File: /home/cafsindia/crm_cafsindia_com/application/views/home_bk02aug2018.php
<?php $this->load->view("partial/header"); ?>
<script type="text/javascript" src="dist/datatables.js"></script>
<script type="text/javascript" src="dist/highcharts.js"></script>
<script type="text/javascript" src="dist/highcharts-3d.js"></script>
<script type="text/javascript" src="dist/data.js"></script>
<script type="text/javascript" src="dist/drilldown.js"></script>
<script type="text/javascript" src="dist/dashboard.js"></script>
<style type="text/css">
.col-style{background-color:#FFFFFF;box-shadow:0 2px 2px 0 rgba(0, 0, 0, 0.14), 0 1px 5px 0 rgba(0, 0, 0, 0.12), 0 3px 1px -2px rgba(0, 0, 0, 0.2); border-radius: 3px; padding: 8px;margin-bottom: 10px;border:0px !important;}
.mg0{margin:0px !important;}
.pd0{padding:0px !important;}
.pd10{padding:10px !important;}
.pdl0{padding-left:0px !important;}
.pd4{padding: 4px !important;}
.pd04{padding: 0px 4px !important;}
th{background-color:#007ac3; color:#FFFFFF; text-align: center;white-space: nowrap;text-overflow: ellipsis; overflow: hidden;vertical-align: inherit !important;padding:6px 8px !important;border: 1px solid #c5cacc !important;font-weight: 500;background: linear-gradient(45deg, #00AEFF -182%, #3369E6 100%) !important;border-right: 0px !important;border-left: 0px !important;}
.tx-center{text-align: center !important;}
body{color:#000000;text-shadow: none !important;background-color:#f5f2f0;font-family: verdana;font-weight: 500;}
table{margin:0px;text-align: center !important;font-size: inherit;text-transform:capitalize;}
h5{white-space: nowrap; margin:0px;padding:5px;font-weight: bold;text-overflow: ellipsis; overflow: hidden;}
.dataspan{color: #c7254e; font-size: 15px; font-weight: bold;font-size: inherit;font-size: inherit;}
.mgleft10{margin-left: 10px;}
.bold{font-weight: bold !important;font-size: 11px !important;}
td:first-child{text-align:left !important;border-left:0px !important;white-space: nowrap !important;}
td:last-child{border-right:0px !important;}
th:first-child{border-left:0px !important;}
th:last-child{border-right:0px !important;}
.cursor{cursor: pointer;}
td{padding:4px 8px!important;border-right: 0px !important;border-left: 0px !important;}
.thheadpd{padding:3px !important;}
.dash_header{padding: 8px 16px!important; background-color: #FFFFFF;}
.headtxt{margin: 8px; font-size: 18px; font-weight: bold; margin-bottom: 15px;color:#044ef9 !important;}
.mgb15{margin-bottom:15px !important;}
.ulalign{display: inline-block;}
.lipd{padding: 15px 15px;}
.dashbox{border-radius: 3px; color: #FFFFFF; margin-bottom: 10px;text-overflow: ellipsis; overflow: hidden;}
.violet{background: linear-gradient(45deg, #9C27B0 0%, #BA68C8 100%); box-shadow: 0 6px 20px 0 #E1BEE7;}
.orange{background: linear-gradient(45deg, #ff6f00 0%, #ffca28 100%);box-shadow: 0 6px 20px 0 rgba(255, 111, 0, 0.3);}
.red{background: linear-gradient(45deg, #FF5252 0%, #f48fb1 100%); box-shadow: 0 6px 20px 0 rgba(244, 143, 177, 0.5);}
.blue{background: linear-gradient(45deg, #0288d1 0%, #26c6da 100%);box-shadow: 0 6px 20px 0 rgba(38, 198, 218, 0.5);}
.teal{background: linear-gradient(45deg, #009688 0%, #4DB6AC 100%);box-shadow: 0 6px 20px 0 #B2DFDB;}
.yellow{background: linear-gradient(45deg, #FFC107 0%, #FFD54F 100%);box-shadow: 0 6px 20px 0 #FFECB3;}
.redish{background: linear-gradient(45deg, #f10000 0%, #b9383e 100%);box-shadow: 0 6px 20px 0 #FFECB3;}
.dorange{background: linear-gradient(45deg, #FF5722 0%, #FF8A65 100%); box-shadow: 0 6px 20px 0 #FFCCBC;}
.brown{background: linear-gradient(45deg, #795548 0%, #A1887F 100%); box-shadow: 0 6px 20px 0 #D7CCC8;}
.blue_gray{background: linear-gradient(45deg, #607D8B 0%, #78909C 100%); box-shadow: 0 6px 20px 0 #CFD8DC;}
.lime{background: linear-gradient(45deg, #CDDC39 0%, #C0CA33 100%);
box-shadow: 0 6px 20px 0 #E6EE9C;}
.divider{background: url(images/divider.png) -5px 0 repeat-x;height: 12px;background-color:#f5f2f0;margin-bottom:15px;}
.white{white-space: nowrap !important;text-overflow: ellipsis; overflow: hidden;}
h5, .h5 {
font-size: inherit;
}
select.form-control.input-sm {
padding: 1px !important;
height: 22px !important;
}
.dataTables_filter {
float: right !important;
}
</style>
<?php
//Session Info
$emp_role = $this->session->userdata('emp_role');
$emp_id = $this->session->userdata('emp_id');
$emp_cat = $this->session->userdata('emp_category');
$emp_name = $this->session->userdata('emp_name');
$teamleader = $this->session->userdata('teamleader');
$emp_grade = $this->session->userdata('emp_grade');
$today = new DateTime();
$lastDay = new DateTime('last day of this month');
$DaysRemaining = $lastDay->diff($today)->format('%a days');
$first_day = date("Y-m-01");
$last_day = date("t");
$today = date("Y-m-d");
$yesterday = date("Y-m-d",strtotime("-1 days"));
$tmrw = new DateTime('tomorrow');
$tmrw = $tmrw->format('Y-m-d');
$cur_month = date("Y-m");
$cur_year = date("Y");
$datestring= "$today first day of last month";
$dt=date_create($datestring);
$last_month = $dt->format('Y-m');
$april_month = date("Y-04");
//Get Financial Year
$year = date('Y');
$year1 = date('Y')-1;
$year2 = date('Y')+1;
if (date('m') > 03){
$fin_start_month = $year."-"."04"."-"."01";
$fin_end_month = $year2."-"."03"."-"."31";
$m = date("m");
$mth = $m - 1 ;
$lst_mth = $mth - 3;
}else{
$fin_start_month = $year1."-"."04"."-"."01";
$fin_end_month = $year."-"."03"."-"."31";
$m = date("m");
$lst_mth = $m + 9 ;
}
//Last 30 days
$thirty_days = date('Y-m-d', strtotime('today - 30 days'));
//Last Month End Date
$dd = $endLastMonth = mktime(0, 0, 0, date("m"), 0, date("Y"));
$last_month_endDate = date("Y-m-d", $dd);
if($emp_role === "4"){
//Portfolio Team Leaders
$tl_port = $this->db->query("SELECT id,first_name FROM `ospos_employees` inner join ospos_people on ospos_people.person_id = ospos_employees.person_id where category = '2' and role = '6' and deleted = '0'");
$tl_port_info = $tl_port->result_array();
//Health Team Leaders
$tl_health = $this->db->query("SELECT id,first_name FROM `ospos_employees` inner join ospos_people on ospos_people.person_id = ospos_employees.person_id where category = '3' and role = '6' and deleted = '0'");
$tl_health_info = $tl_health->result_array();
$pf_tl_info_qry = $this->db->query("SELECT GROUP_CONCAT(id SEPARATOR ',') as tl_list FROM `ospos_employees` inner join ospos_people on ospos_people.person_id = ospos_employees.person_id where category = '2' and role = '6' and deleted = '0'");
$pf_tl_info = $pf_tl_info_qry->result_array();
$pf_tl = $pf_tl_info[0]['tl_list'];
$hl_tl_info_qry = $this->db->query("SELECT GROUP_CONCAT(id SEPARATOR ',') as tl_list FROM `ospos_employees` inner join ospos_people on ospos_people.person_id = ospos_employees.person_id where category = '3' and role = '6' and deleted = '0'");
$hl_tl_info = $hl_tl_info_qry->result_array();
$hl_tl = $hl_tl_info[0]['tl_list'];
?>
<div class='col-md-12 pd0 dash_header'>
<h3 class='text-center headtxt'>Area Manager Dashboard</h3>
<div class='col-md-12'>
<div class='row mg0'>
<div class='col-md-6 pdl0'>
<div class='form-group' style='margin-bottom:4px;'>
<label style="color:#ff6f00;">Portfolio</label>
<select class='form-control input-sm'); style="text-transform: capitalize;" onchange=get_tl_info(this.value,'2','6','<?php echo $pf_tl; ?>');>
<option value=''>-- Select Portfolio --</option>
<option value='ALL'>ALL</option>
<?php foreach ($tl_port_info as $key => $value) {
$id = $value['id'];
$first_name = $value['first_name'];
$grade = $value['grade'];
?>
<option value=<?php echo $id; ?>><?php echo $first_name; ?></option>
<?php } ?>
</select>
</div>
<div class='form-group' style='margin-bottom:4px;text-transform: capitalize;'>
<label style="color:#9C27B0;">Health</label>
<select class='form-control input-sm'); onchange=get_tl_info(this.value,'3','6','<?php echo $hl_tl; ?>');>
<option value=''>-- Select Health --</option>
<option value='ALL'>ALL</option>
<?php foreach ($tl_health_info as $key => $value) {
$id = $value['id'];
$first_name = $value['first_name'];
?>
<option value=<?php echo $id; ?>><?php echo $first_name; ?></option>
<?php } ?>
</select>
</div>
</div>
</div>
</div>
<div id = "tl_data" class='col-md-12'>
</div>
</div>
<?php
}
?>
<?php
if($emp_role === "1"){
//ADMIN
//Portfolio CRM Ids
$crm_port = $this->db->query("SELECT count(id) as count,GROUP_CONCAT(id SEPARATOR ',') as ids FROM `ospos_employees` where category = '2' and role = '3' and deleted = '0'");
$crm_port_info = $crm_port->result_array();
$crm_port_ids = $crm_port_info[0]['ids'];
$crm_port_count = $crm_port_info[0]['count'];
//Health CRM Ids
$crm_hl = $this->db->query("SELECT count(id) as count,GROUP_CONCAT(id SEPARATOR ',') as ids FROM `ospos_employees` where category = '3' and role = '3' and deleted = '0'");
$crm_hl_info = $crm_hl->result_array();
$crm_hl_ids = $crm_hl_info[0]['ids'];
$crm_hl_count = $crm_hl_info[0]['count'];
//RM Ids
$rm = $this->db->query("SELECT count(id) as count,GROUP_CONCAT(id SEPARATOR ',') as ids FROM `ospos_employees` where role = '5' and deleted = '0'");
$rm_info = $rm->result_array();
$rm_ids = $rm_info[0]['ids'];
$rm_count = $rm_info[0]['count'];
//Box 1
$company_met = $this->db->query("SELECT count(*) as count from ospos_lead_type_info where lead_status = '9' and met_date like '$cur_month%'");
$company_met_result = $company_met->result_array();
$tot_company_met = $company_met_result[0]['count'];
$crm_met = $this->db->query("SELECT count(*) as count from ospos_lead_type_info where lead_status = '9' and created_by in ($crm_port_ids) and met_date like '$cur_month%'");
$tot_met_result = $crm_met->result_array();
$tot_met = $tot_met_result[0]['count'];
$company_log = $this->db->query("SELECT count(*) as count from ospos_lead_type_info left join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id where lead_status = '9' and prospect_level = '4' and login_date like '$cur_month%' and ospos_leads_product.status = '1'");
$tot_log_result = $company_log->result_array();
$tot_log = $tot_log_result[0]['count'];
$cr = round(($tot_log / $tot_company_met) * 100);
//Box 2
$login_value = $this->db->query("SELECT cat_name,IFNULL(sum(overall_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 inner join ospos_category on ospos_category.cat_id = ospos_leads_product.product_category WHERE login_date like '$cur_month%' and ospos_leads_product.status = '1' GROUP BY cat_name");
$login_value_result = $login_value->result_array();
$life_val = 0;
$hl_val = 0;
foreach ($login_value_result as $key => $value) {
$category = $value['cat_name'];
$value = $value['value'];
if($category === "Portfolio"){
$life_val = $value;
}else
if($category === "Health Insurance"){
$hl_val = $value;
}
}
$sip_login = $this->db->query("SELECT sum(mf_amount) as value FROM `ospos_lead_type_info` inner join ospos_category on ospos_category.cat_id = ospos_lead_type_info.category LEFT JOIN ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id where mf_investment = '2' AND product_category = '4' and login_date like '$cur_month%' and ospos_leads_product.status = '1'");
$sip_login_rslt = $sip_login->result_array();
$sip_value = $sip_login_rslt[0]['value'];
//Box 3
$target = $this->db->query("SELECT value from ospos_incentives where role = '$emp_role' and grade='$emp_grade' and from_date like '$cur_month%' and to_date like '$cur_month%'");
$target_result = $target->result_array();
$target_value = $target_result[0]['value'];
$rrr = round(($target_value - $life_val) / ($last_day - $date),1);
//Box 4
$login_qry = $this->db->query("SELECT sum(overall_net_premium) as value FROM `ospos_lead_type_info` INNER JOIN ospos_leads_product on lead_type_id = lead_info_id left join ospos_leads on ospos_leads.lead_id = ospos_lead_type_info.lead_id where prospect_level='4' and product_category = '3' and login_date like '$last_month%' and ospos_leads_product.status = '1'");
$login_result = $login_qry->result_array();
$last_login = $login_result[0]['value'];
$last_login_qry = $this->db->query("SELECT sum(overall_net_premium) as value FROM `ospos_lead_type_info` INNER JOIN ospos_leads_product on lead_type_id = lead_info_id inner join ospos_leads on ospos_leads.lead_id = ospos_lead_type_info.lead_id where prospect_level='4' and product_category = '3' and login_date BETWEEN '$fin_start_month' and '$last_month_endDate' and ospos_leads_product.status = '1'");
$last_login_result = $last_login_qry->result_array();
$last_login = $last_login_result[0]['value'];
$ytd_avg = round($hl_val / $lst_mth ,0);
//Box 6
$health_crm_avg = $hl_val /$crm_hl_count;
//Box 7
$rm_avg_met = round($tot_company_met /$rm_count);
//Box 8
$rm_avg_val = round($life_val /$rm_count);
/* Met Status */
$A_met_status = $this->db->query("SELECT prospect_name,count(*) as count,IFNULL(sum(expected_login),0) as amount,IFNULL(sum(overall_net_premium),0) as value FROM `ospos_lead_type_info` left join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id inner join ospos_prospect_level on prospect_id = prospect_level where lead_status = '9' and met_date like '$cur_month%' GROUP by prospect_level");
$A_met_lead_rslt = $A_met_status->result_array();
$A_met_hot = 0;
$A_met_hot_amt = 0;
$A_met_warm = 0;
$A_met_warm_amt =0;
$A_met_log = 0;
$A_met_log_amt = 0;
foreach ($A_met_lead_rslt as $key => $value) {
$prospect_name = $value['prospect_name'];
$count = $value['count'];
$amount = $value['amount'];
$value = $value['value'];
if($prospect_name === "Hot"){
$A_met_hot = $count;
$A_met_hot_amt = $amount;
}else
if($prospect_name === "Warm"){
$A_met_warm = $count;
$A_met_warm_amt = $amount;
}else
if($prospect_name === "Login"){
$A_met_log = $count;
$A_met_log_val = $value;
}
}
$A_ncd_met_status = $this->db->query("SELECT prospect_name,count(*) as count,IFNULL(sum(expected_login),0) as amount,IFNULL(sum(overall_net_premium),0) as value FROM `ospos_lead_type_info` left join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id inner join ospos_prospect_level on prospect_id = prospect_level where lead_status = '9' and ncd like '$cur_month%' and met_date not like '$cur_month%' GROUP by prospect_name");
$A_ncd_met_lead_rslt = $A_ncd_met_status->result_array();
$A_ncd_met_hot = 0;
$A_ncd_met_hot_amt = 0;
$A_ncd_met_warm = 0;
$A_ncd_met_warm_amt = 0;
$A_ncd_met_log = 0;
$A_ncd_met_log_val = 0;
foreach ($A_ncd_met_lead_rslt as $key => $value) {
$prospect_name = $value['prospect_name'];
$count = $value['count'];
$amount = $value['amount'];
$value = $value['value'];
if($prospect_name === "Hot"){
$A_ncd_met_hot = $count;
$A_ncd_met_hot_amt = $amount;
}else
if($prospect_name === "Warm"){
$A_ncd_met_warm = $count;
$A_ncd_met_warm_amt = $amount;
}else
if($prospect_name === "Login"){
$A_ncd_met_log = $count;
$A_ncd_met_log_val = $value;
}
}
$tot_hot = $A_met_hot + $A_ncd_met_hot;
$tot_hot_amt = $A_met_hot_amt + $A_ncd_met_hot_amt;
$tot_warm = $A_met_warm + $A_ncd_met_warm;
$tot_warm_amt = $A_met_warm_amt + $A_ncd_met_warm_amt;
$tot_login = $A_met_log + $A_ncd_met_log;
$tot_login_val= $A_met_log_val + $A_ncd_met_log_val;
/* Month Login */
$month_login_status = $this->db->query("SELECT ospos_lead_type.lead_type,sum(overall_net_premium) as value FROM `ospos_lead_type_info` left join ospos_lead_type on ospos_lead_type_info.lead_type = ospos_lead_type.lead_type_id inner join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id inner join ospos_lead_status on status_id = lead_status where login_date like '$cur_month%' GROUP by lead_type");
$month_login_rslt = $month_login_status->result_array();
$self = "0";
$upsell = "0";
$ref = "0";
foreach ($month_login_rslt as $key => $value) {
$lead_type = $value['lead_type'];
$val = $value['value'];
if($lead_type === "Self"){
$self = $val;
}else
if($lead_type === "Upsell"){
$upsell = $val;
}else
if($lead_type === "Reference"){
$ref = $val;
}
}
/* Month Login */
$month_crm_status = $this->db->query("SELECT ospos_lead_type.lead_type,IFNULL(sum(overall_net_premium),0) as value FROM `ospos_lead_type_info` left join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id left join ospos_lead_type on ospos_lead_type_info.lead_type = ospos_lead_type.lead_type_id inner join ospos_lead_status on status_id = lead_status where prospect_level = '4' and ospos_lead_type_info.lead_type != '8' and ospos_lead_type_info.lead_type != '9' and ospos_lead_type_info.lead_type != '10' and login_date like '$cur_month%' and ospos_leads_product.status = '1'");
$month_crm_rslt = $month_crm_status->result_array();
$lead_type = $month_crm_rslt[0]['lead_type'];
$crm_count = $month_crm_rslt[0]['value'];
/*Contribution */
$login = $this->db->query("SELECT IFNULL(sum(overall_net_premium),0) as value from ospos_lead_type_info left join ospos_leads_product on lead_type_id = lead_info_id where prospect_level = '4' and login_date like '$cur_month%' and ospos_leads_product.status = '1'");
$login_result = $login->result_array();
$total_login = $login_result[0]['value'];
$self_con = round(($self / $total_login)*100);
$upsell_con = round(($upsell / $total_login)*100);
$ref_con = round(($ref / $total_login)*100);
$crm_con = round(($crm_count / $total_login)*100);
/* Other Campaigns */
$month_oc_status = $this->db->query("SELECT sum(overall_net_premium) as value,cat_name FROM `ospos_lead_type_info` inner join ospos_category on ospos_category.cat_id = ospos_lead_type_info.category LEFT JOIN ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id where lead_mode = '2' AND login_date like '$cur_month%' and ospos_leads_product.status = '1' GROUP BY product_category");
$month_oc_rslt = $month_oc_status->result_array();
$term = "0";
$health = "0";
foreach ($month_oc_rslt as $key => $value){
$cat_name = $value['cat_name'];
$val = $value['value'];
if($cat_name === "Term Plan"){
$term = $val;
}else
if($cat_name === "Health Insurance"){
$health = $val;
}
}
/* Health Team Leader */
$ht_tl_info = $this->db->query("SELECT id,first_name,reporting FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id where role = '6' and deleted = '0' and category = '3' ORDER BY ospos_employees.reporting ASC");
$ht_tl_rslt = $ht_tl_info->result();
$ht_tl_list = "";
foreach ($ht_tl_rslt as $key => $value) {
$id = $value->id;
$first_name = $value->first_name;
$team_info = $this->db->query("SELECT GROUP_CONCAT(id) as team_ids FROM `ospos_employees` WHERE reporting = '$id'");
$team_rslt = $team_info->row();
$team_ids = $team_rslt->team_ids.",".$id;
$lead_info = $this->db->query("SELECT count(*) as nop,ospos_leads_product.company,vendorcompanyname,ospos_leads_product.product_category as category,IFNULL(sum(overall_net_premium),0) as netamount FROM ospos_lead_type_info left join ospos_leads_product on lead_type_id = lead_info_id left join ospos_vendor on vendor_id = ospos_leads_product.company where login_date like '$cur_month%' and ospos_leads_product.status = '1' and ospos_lead_type_info.created_by in ($team_ids)");
$lead_rslt = $lead_info->result();
$star_amt = 0;
foreach ($lead_rslt as $key => $value){
$nop = $value->nop;
$netamount = $value->netamount;
$avg = round($netamount / $nop) ;
}
$team_zero = $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 in ($team_ids) order by login_date desc limit 0,1");
$result = $team_zero->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));
$lead_gen = $this->db->query("SELECT count(*) as gen FROM ospos_lead_type_info where category = '3' and created_date like '$cur_month%' and ospos_lead_type_info.created_by in ($team_ids)");
$lead_gen_rslt = $lead_gen->result();
$gen = 0;
foreach ($lead_gen_rslt as $key => $value){
$gen = $value->gen;
}
$ht_tl_list .= "<tr>
<td>$first_name</td>
<td class='cursor' onclick=get_mis_hllead($emp_role,$emp_cat,'$team_ids',3,'M','ALL');>$netamount</td>
<td>$nop</td>
<td>$avg</td>
<td>$Zero_days</td>
<td>$gen</td>
</tr>";
}
/* Other Campaigns */
$month_oc_mf_status = $this->db->query("SELECT sum(mf_amount) as value,mf_investment FROM `ospos_lead_type_info` inner join ospos_category on ospos_category.cat_id = ospos_lead_type_info.category LEFT JOIN ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id where lead_mode = '2' AND product_category = '4' and login_date like '$cur_month%' and ospos_leads_product.status = '1' GROUP BY mf_investment");
$month_oc_mf_rslt = $month_oc_mf_status->result_array();
$sip = "0";
$lumpsum = "0";
foreach ($month_oc_mf_rslt as $key => $value){
$cat_name = $value['mf_investment'];
$val = $value['value'];
if($cat_name === "1"){
$sip = $val;
}else
if($cat_name === "2"){
$lumpsum = $val;
}
}
$mis_info = $this->db->query("SELECT count(DISTINCT(lead_id)) as count,IFNULL(sum(expected_login),0) as value,category FROM `ospos_lead_type_info` where current_control = 'MIS' and ncd like '$cur_month%' GROUP by category");
$mis_rslt = $mis_info->result();
$mis_life_count = 0;
$mis_life_amt = 0;
$mis_hl_count = 0;
$mis_hl_amt = 0;
foreach ($mis_rslt as $key => $value) {
$count = $value->count;
$category = $value->category;
$netamount = $value->value;
if($category === "2"){
$mis_life_count = $count;
$mis_life_amt = $netamount;
}
if($category === "3"){
$mis_hl_count = $count;
$mis_hl_amt = $netamount;
}
}
$hc_info = $this->db->query("SELECT count(DISTINCT(lead_id)) as count,ospos_leads_product.product_category as category,IFNULL(sum(overall_net_premium),0) as netamount FROM ospos_lead_type_info inner join ospos_leads_product on lead_type_id = lead_info_id where current_control = 'HC' and ospos_leads_product.status = '1' and ncd like '$cur_month%' GROUP by ospos_leads_product.product_category");
$hc_rslt = $hc_info->result();
$hc_life_count = 0;
$hc_life_amt = 0;
$hc_hl_count = 0;
$hc_hl_amt = 0;
foreach ($hc_rslt as $key => $value) {
$count = $value->count;
$category = $value->category;
$netamount = $value->netamount;
if($category === "2"){
$hc_life_count = $count;
$hc_life_amt = $netamount;
}
if($category === "3"){
$hc_hl_count = $count;
$hc_hl_amt = $netamount;
}
}
$completed_info = $this->db->query("SELECT count(DISTINCT(lead_id)) as count,ospos_leads_product.product_category as category,IFNULL(sum(overall_net_premium),0) as netamount FROM ospos_lead_type_info inner join ospos_leads_product on lead_type_id = lead_info_id where current_control = 'completed' and ospos_leads_product.status = '1' and ncd like '$cur_month%' GROUP by ospos_leads_product.product_category");
$completed_rslt = $completed_info->result();
$cp_life_count = 0;
$cp_life_amt = 0;
$cp_hl_count = 0;
$cp_hl_amt = 0;
foreach ($completed_rslt as $key => $value) {
$count = $value->count;
$category = $value->category;
$netamount = $value->netamount;
if($category === "2"){
$cp_life_count = $count;
$cp_life_amt = $netamount;
}
if($category === "3"){
$cp_hl_count = $count;
$cp_hl_amt = $netamount;
}
}
//Issuance Status
$issuance_info = $this->db->query("SELECT count(*) as count,issuance_status,cat_name FROM `ospos_happy_calling` left join ospos_leads_product on customer_product_id = call_product_id inner join ospos_category on cat_id = product_category where ospos_leads_product.login_date like '$cur_month%' and ospos_leads_product.status = '1' GROUP BY issuance_status,product_category");
$issuance_rslt = $issuance_info->result();
$h_issued = 0;
$h_issu_pen = 0;
$h_medi_pen = 0;
$h_doc_pen = 0;
$h_check_dis = 0;
$h_med_dec = 0;
$p_issued = 0;
$p_issu_pen = 0;
$p_medi_pen = 0;
$p_doc_pen = 0;
$p_check_dis = 0;
$p_med_dec = 0;
foreach($issuance_rslt as $key => $value) {
$count = $value->count;
$category = $value->cat_name;
$issuance_status = $value->issuance_status;
if($category === "Portfolio"){
if($issuance_status === "Issued"){
$p_issued = $count;
}else
if($issuance_status === "Pending For Issuance"){
$p_issu_pen = $count;
}else
if($issuance_status === "Medical Pending"){
$p_medi_pen = $count;
}else
if($issuance_status === "Docs Pending from Sales"){
$p_doc_pen = $count;
}else
if($issuance_status === "Cheque Dishonor"){
$p_check_dis = $count;
}else
if($issuance_status === "Medical Declined"){
$p_med_dec = $count;
}
}else
if($category === "Health Insurance"){
if($issuance_status === "Issued"){
$h_issued = $count;
}else
if($issuance_status === "Pending For Issuance"){
$h_issu_pen = $count;
}else
if($issuance_status === "Medical Pending"){
$h_medi_pen = $count;
}else
if($issuance_status === "Docs Pending from Sales"){
$h_doc_pen = $count;
}else
if($issuance_status === "Cheque Dishonor"){
$h_check_dis = $count;
}else
if($issuance_status === "Medical Declined"){
$h_med_dec = $count;
}
}
}
$action_info = $this->db->query("SELECT count(*) as count,issuance_status,ospos_happy_calling.login_date,cat_name FROM `ospos_happy_calling` left join ospos_leads_product on customer_product_id = call_product_id inner join ospos_category on cat_id = product_category WHERE issuance_status != 'Issued' and ospos_leads_product.status = '1' AND ospos_happy_calling.login_date <= DATE_ADD(CURDATE(),INTERVAL -7 DAY) GROUP BY issuance_status,cat_name");
$action_rslt = $action_info->result();
$h_act_issued = 0;
$h_act_issu_pen = 0;
$h_act_medi_pen = 0;
$h_act_doc_pen = 0;
$h_act_check_dis = 0;
$h_act_med_dec = 0;
$p_act_issued = 0;
$p_act_issu_pen = 0;
$p_act_medi_pen = 0;
$p_act_doc_pen = 0;
$p_act_check_dis = 0;
$p_act_med_dec = 0;
foreach($action_rslt as $key => $value) {
$count = $value->count;
$category = $value->cat_name;
$issuance_status = $value->issuance_status;
if($category === "Portfolio"){
if($issuance_status === "Pending For Issuance"){
$p_act_issu_pen = $count;
}else
if($issuance_status === "Medical Pending"){
$p_act_medi_pen = $count;
}else
if($issuance_status === "Docs Pending from Sales"){
$p_act_doc_pen = $count;
}else
if($issuance_status === "Cheque Dishonor"){
$p_act_check_dis = $count;
}else
if($issuance_status === "Medical Declined"){
$p_act_med_dec = $count;
}
}else
if($category === "Health Insurance"){
if($issuance_status === "Pending For Issuance"){
$h_act_issu_pen = $count;
}else
if($issuance_status === "Medical Pending"){
$h_act_medi_pen = $count;
}else
if($issuance_status === "Docs Pending from Sales"){
$h_act_doc_pen = $count;
}else
if($issuance_status === "Cheque Dishonor"){
$h_act_check_dis = $count;
}else
if($issuance_status === "Medical Declined"){
$h_act_med_dec = $count;
}
}
}
//Document Status
$document_info = $this->db->query("SELECT count(*) as count,document_issue_status,cat_name FROM `ospos_happy_calling` left join ospos_leads_product on customer_product_id = call_product_id inner join ospos_category on cat_id = product_category where issued_date like '$cur_month%' and ospos_leads_product.status = '1' GROUP BY document_issue_status,product_category");
$document_rslt = $document_info->result();
$h_dis = 0;
$h_not_dis = 0;
$h_return = 0;
$h_reprint = 0;
$h_reach = 0;
$h_cash = 0;
$p_dis = 0;
$p_not_dis = 0;
$p_return = 0;
$p_reprint = 0;
$p_reach = 0;
$p_cash = "N/A";
foreach($document_rslt as $key => $value){
$count = $value->count;
$category = $value->cat_name;
$document_issue_status = $value->document_issue_status;
if($category === "Portfolio"){
if($document_issue_status === "Dispatched"){
$p_dis = $count;
}else
if($document_issue_status === "NotDispatched"){
$p_not_dis = $count;
}else
if($document_issue_status === "ReturnedToHO"){
$p_return = $count;
}else
if($document_issue_status === "AskedForReprint"){
$p_reprint = $count;
}else
if($document_issue_status === "ReachedBranch"){
$p_reach = $count;
}else
if($document_issue_status === "CashlessCardPending"){
$p_cash = $count;
}
}else
if($category === "Health Insurance"){
if($document_issue_status === "Dispatched"){
$h_dis = $count;
}else
if($document_issue_status === "NotDispatched"){
$h_not_dis = $count;
}else
if($document_issue_status === "ReturnedToHO"){
$h_return = $count;
}else
if($document_issue_status === "AskedForReprint"){
$h_reprint = $count;
}else
if($document_issue_status === "ReachedBranch"){
$h_reach = $count;
}else
if($document_issue_status === "CashlessCardPending"){
$h_cash = $count;
}
}
}
$doc_action_info = $this->db->query("SELECT count(*) as count,document_issue_status,ospos_happy_calling.issued_date,cat_name FROM `ospos_happy_calling` left join ospos_leads_product on customer_product_id = call_product_id inner join ospos_category on cat_id = product_category WHERE document_issue_status != 'Dispatched' and ospos_leads_product.status = '1' AND ospos_happy_calling.issued_date <= DATE_ADD(CURDATE(),INTERVAL -7 DAY) GROUP BY document_issue_status,cat_name");
$doc_action_rslt = $doc_action_info->result();
$h_act_dis = 0;
$h_act_not_dis = 0;
$h_act_return = 0;
$h_act_reprint = 0;
$h_act_reach = 0;
$h_act_cash = 0;
$p_act_dis = 0;
$p_act_not_dis = 0;
$p_act_return = 0;
$p_act_reprint = 0;
$p_act_reach = 0;
$p_act_cash = "N/A";
foreach($doc_action_rslt as $key => $value) {
$count = $value->count;
$category = $value->cat_name;
$document_issue_status = $value->document_issue_status;
if($category === "Portfolio"){
if($document_issue_status === "NotDispatched"){
$p_act_not_dis = $count;
}else
if($document_issue_status === "ReturnedToHO"){
$p_act_return = $count;
}else
if($document_issue_status === "AskedForReprint"){
$p_act_reprint = $count;
}else
if($document_issue_status === "ReachedBranch"){
$p_act_reach = $count;
}else
if($document_issue_status === "CashlessCardPending"){
$p_act_cash = $count;
}
}else
if($category === "Health Insurance"){
if($document_issue_status === "NotDispatched"){
$h_act_not_dis = $count;
}else
if($document_issue_status === "ReturnedToHO"){
$h_act_return = $count;
}else
if($document_issue_status === "AskedForReprint"){
$h_act_reprint = $count;
}else
if($document_issue_status === "ReachedBranch"){
$h_act_reach = $count;
}else
if($document_issue_status === "CashlessCardPending"){
$h_act_cash = $count;
}
}
}
$rm_info = $this->db->query("SELECT id,first_name,reporting,IFNULL(sum(overall_net_premium),0) as netamount FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id left join ospos_lead_type_info on ospos_lead_type_info.created_by = ospos_employees.id left join ospos_leads_product on lead_type_id = lead_info_id where role = '5' and deleted = '0' and ospos_leads_product.status = '1' GROUP by reporting,id ORDER BY `netamount` ASC");
$rm_rslt = $rm_info->result();
$rm_list = "";
foreach ($rm_rslt as $key => $value) {
$id = $value->id;
$first_name = $value->first_name;
$reporting = $value->reporting;
$rm_manager_info = $this->db->query("SELECT id,first_name FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id where ospos_employees.id = '$reporting' ");
$rm_manager_rslt = $rm_manager_info->row();
$rm_manage = $rm_manager_rslt->first_name;
$lead_info = $this->db->query("SELECT ospos_leads_product.product_category as category,IFNULL(sum(overall_net_premium),0) as netamount,IFNULL(sum(mf_amount),0) as mf_amount,mf_investment FROM ospos_lead_type_info INNER join ospos_leads_product on lead_type_id = lead_info_id where login_date like '$cur_month%' and (ospos_lead_type_info.created_by = '$id' or ospos_lead_type_info.rm_name = '$id') and mf_investment != '2' and ospos_leads_product.status = '1' GROUP by ospos_leads_product.product_category");
$lead_rslt = $lead_info->result();
$lifeamount = 0;
$mfamount = 0;
$termamount = 0;
foreach($lead_rslt as $key => $value) {
$category = $value->category;
$netamount = $value->netamount;
$mf_amount = $value->mf_amount;
$type = $value->mf_investment;
if($category === "2"){
$lifeamount = $netamount;
}
if($category === "4"){
$mfamount = $mf_amount;
}
if($category === "5"){
$termamount = $netamount;
}
}
$rm_list .= "<tr>
<td>$rm_manage</td>
<td>$first_name</td>
<td class='cursor' onclick=get_mis_rmlead($emp_role,$emp_cat,$id,2,'M','');>$lifeamount</td>
<td class='cursor' onclick=get_mis_rmlead($emp_role,$emp_cat,$id,4,'M','SIP');>$mfamount</td>
<td class='cursor' onclick=get_mis_rmlead($emp_role,$emp_cat,$id,2,'M','');>$termamount</td>
</tr>";
}
echo "<div class='col-md-12 pd0 dash_header'>
<h3 class='text-center headtxt'>ADMIN Dashboard</h3>
<div class='row mg0 mgb15'>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Company Met / CRM Met ,Conv %'>
<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>Met</h5> <span class='bold'>$tot_company_met / $tot_met , $cr%</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='LIFE Value - From MIS,HEALTH - Value, SIP VALUE'>
<div class='dashbox red'>
<table>
<tr>
<td class='pd10'><i class='fa fa-user-md fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>Login</h5> <span class='bold'> $life_val , $hl_val , $sip_value </span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Target & Required Run Rate'>
<div class='dashbox blue'>
<table>
<tr>
<td class='pd10'><i class='fa fa-bookmark-o fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>Target</h5> <span class='bold'>$target_value & $rrr</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Last Month Login / YTD Avg'>
<div class='dashbox violet'>
<table>
<tr>
<td class='pd10'><i class='fa fa-history fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>Last Month</h5> <span class='bold'>$last_login / $ytd_avg</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='sr. RM- AVG,RM AVG - Like Role wise Avg'>
<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>RM Avg</h5> <span class='bold'> $rm_avg_val </span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Role CRM Wise Avg - Required'>
<div class='dashbox yellow'>
<table>
<tr>
<td class='pd10'><i class='fa fa-tachometer fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>CRM Avg</h5> <span class='bold'>$health_crm_avg</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Avg Met'>
<div class='dashbox blue_gray'>
<table>
<tr>
<td class='pd10'><i class='fa fa-thermometer-half fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>Avg Met</h5> <span class='bold'> $rm_avg_met </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='row mg0'>
<div class='col-md-6 pdl0'>
<table class='table table-bordered table-hover col-style'>
<thead>
<colgroup span='2'></colgroup>
<colgroup span='2'></colgroup>
<tr>
<th rowspan='2' >Met Status</th>
<th colspan='2' scope='colgroup' class='thheadpd'>Hot</th>
<th colspan='2' scope='colgroup' class='thheadpd'>Warm</th>
<th colspan='2' scope='colgroup' class='thheadpd'>Login</th>
</tr>
<tr>
<th scope='col' style='font-size:11px;padding:4px !important;'>Count</th>
<th scope='col' style='font-size:11px;padding:4px !important;'>Expected</th>
<th scope='col' style='font-size:11px;padding:4px !important;'>Count</th>
<th scope='col' style='font-size:11px;padding:4px !important;'>Expected</th>
<th scope='col' style='font-size:11px;padding:4px !important;'>Count</th>
<th scope='col' style='font-size:11px;padding:4px !important;'>Login</th>
</tr>
</thead>
<tbody>
<tr>
<td >FTM</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,'1','M','ADMIN');>$A_met_hot</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,'1','M','ADMIN');>$A_met_hot_amt</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,'2','M','ADMIN');>$A_met_warm</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,'2','M','ADMIN');>$A_met_warm_amt</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,'4','M','ADMIN');>$A_met_log</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,'4','M','ADMIN');>$A_met_log_val</td>
</tr>
<tr>
<td >NCD</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,'1','M','ADMINNCD');>$A_ncd_met_hot</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,'1','M','ADMINNCD');>$A_ncd_met_hot_amt</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,'2','M','ADMINNCD');>$A_ncd_met_warm</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,'2','M','ADMINNCD');>$A_ncd_met_warm_amt</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,'4','M','ADMINNCD');>$A_ncd_met_log</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,'4','M','ADMINNCD');>$A_ncd_met_log_val</td>
</tr>
<tr>
<td >Total</td>
<td class='cursor'>$tot_hot</td>
<td class='cursor'>$tot_hot_amt</td>
<td class='cursor'>$tot_warm</td>
<td class='cursor'>$tot_warm_amt</td>
<td class='cursor'>$tot_login</td>
<td class='cursor'>$tot_login_val</td>
</tr>
</tbody>
</table>
</div>
<div class='col-md-6 pdl0'>
<table class='table table-bordered table-hover col-style'>
<thead>
<tr>
<th>Login Status</th>
<th>Self</th>
<th>Reference</th>
<th>Upsell</th>
<th>CRM</th>
</tr>
</thead>
<tbody>
<tr>
<td style='padding-top:3%;'>Month Login</td>
<td class='cursor' onclick=get_lead_info_type($emp_role,$emp_cat,$emp_id,'8','M','ADMIN');>$self</td>
<td class='cursor' onclick=get_lead_info_type($emp_role,$emp_cat,$emp_id,'10','M','ADMIN');>$ref</td>
<td class='cursor' onclick=get_lead_info_type($emp_role,$emp_cat,$emp_id,'9','M','ADMIN');>$upsell</td>
<td class='cursor' onclick=get_lead_info_type($emp_role,$emp_cat,$emp_id,'0','M','ADMIN');>$crm_count</td>
</tr>
<tr>
<td>Contritubution</td>
<td>$self_con %</td>
<td>$ref_con %</td>
<td>$upsell_con %</td>
<td>$crm_con %</td>
</tr>
</tbody>
</table>
</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>Health TL</th>
<th>Value</th>
<th>Nop</th>
<th>Avg.Premium</th>
<th>Zero Days Count</th>
<th>Generated</th>
</tr>
</thead>
<tbody>
$ht_tl_list
</tbody>
</table>
</div>
<div class='col-md-6 pdl0'>
<table class='table table-bordered table-hover col-style'>
<thead>
<tr>
<th>Other Campaign</th>
<th>Lumpsum</th>
<th>SIP</th>
<th>Term</th>
<th>Health</th>
</tr>
</thead>
<tbody>
<tr>
<td >Month</td>
<td class='cursor' onclick=get_admin_ocl($emp_role,$emp_cat,'$rm_team_ids','4','M','Lumpsum');>$lumpsum</td>
<td class='cursor' onclick=get_admin_ocl($emp_role,$emp_cat,'$rm_team_ids','4','M','SIP');>$sip</td>
<td class='cursor' onclick=get_admin_ocl($emp_role,$emp_cat,'$rm_team_ids','5','M','');>$term</td>
<td class='cursor' onclick=get_admin_ocl($emp_role,$emp_cat,'$rm_team_ids','3','M','SIP');>$health</td>
</tr>
</tbody>
</table>
</div>
</div>
</div>
<div class='col-md-12'>
<div class='col-md-6 pdl0'>
<div class='form-group' style='margin-bottom:4px;'>
<select class='form-control input-sm' onchange=get_renew_life_info(this.value); >
<option value='M'>Month</option>
<option value='FY'>FY</option>
<option value='LTD'>LTD</option>
</select>
</div>
<table class='table table-bordered table-hover col-style' id='renew_life_info_table'>
<thead>
<tr>
<th>Renewal Life</th>
<th>Collectable</th>
<th>Paid</th>
<th>Not Paid</th>
<th>Not Interested</th>
<th>Closed</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
<div class='col-md-6 pdl0'>
<div class='form-group' style='margin-bottom:4px;'>
<select class='form-control input-sm' onchange=get_renew_hl_info(this.value); >
<option value='M'>Month</option>
<option value='FY'>FY</option>
<option value='LTD'>LTD</option>
</select>
</div>
<table class='table table-bordered table-hover col-style' id='renew_hl_info_table'>
<thead>
<tr>
<th>Renewal Health</th>
<th>Collectable</th>
<th>Paid</th>
<th>Not Paid</th>
<th>Not Interested</th>
<th>Terminated</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</div>
<div class='col-md-12 pd0'>
<div class='col-md-6 pdl0'>
<table class='table table-bordered table-hover col-style'>
<thead>
<colgroup span='2'></colgroup>
<colgroup span='2'></colgroup>
<tr>
<th rowspan='2'>Lead Status</th>
<th colspan='2' scope='colgroup' class='thheadpd'>Life</th>
<th colspan='2' scope='colgroup' class='thheadpd'>Health</th>
</tr>
<tr>
<th scope='col' style='font-size:11px;padding:4px !important;'>Count</th>
<th scope='col' style='font-size:11px;padding:4px !important;'>Value</th>
<th scope='col' style='font-size:11px;padding:4px !important;'>Count</th>
<th scope='col' style='font-size:11px;padding:4px !important;'>Value</th>
</tr>
</thead>
<tbody>
<tr>
<td>MIS</td>
<td class='cursor' onclick=+($emp_role,$emp_cat,'MIS',2,'M','MIS');>$mis_life_count</td>
<td class='cursor' onclick=get_mis_lead_info($emp_role,$emp_cat,'MIS',2,'M','MIS');>$mis_life_amt</td>
<td class='cursor' onclick=get_mis_lead_info($emp_role,$emp_cat,'MIS',3,'M','MIS');>$mis_hl_count</td>
<td class='cursor' onclick=get_mis_lead_info($emp_role,$emp_cat,'MIS',3,'M','MIS');>$mis_hl_amt</td>
</tr>
<tr>
<td>Happy Calling</td>
<td class='cursor' onclick=get_mis_lead_info($emp_role,$emp_cat,'HC',2,'M','');>$hc_life_count</td>
<td class='cursor' onclick=get_mis_lead_info($emp_role,$emp_cat,'HC',2,'M','');>$hc_life_amt</td>
<td class='cursor' onclick=get_mis_lead_info($emp_role,$emp_cat,'HC',3,'M','');>$hc_hl_count</td>
<td class='cursor' onclick=get_mis_lead_info($emp_role,$emp_cat,'HC',3,'M','');>$hc_hl_amt</td>
</tr>
<tr>
<td>Completed</td>
<td class='cursor' onclick=get_mis_lead_info($emp_role,$emp_cat,'completed',2,'M','');>$cp_life_count</td>
<td class='cursor' onclick=get_mis_lead_info($emp_role,$emp_cat,'completed',2,'M','');>$cp_life_amt</td>
<td class='cursor' onclick=get_mis_lead_info($emp_role,$emp_cat,'completed',3,'M','');>$cp_hl_count</td>
<td class='cursor' onclick=get_mis_lead_info($emp_role,$emp_cat,'completed',3,'M','');>$cp_hl_amt</td>
</tr>
</tbody>
</table>
</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>
<colgroup span='2'></colgroup>
<colgroup span='2'></colgroup>
<tr>
<th rowspan='2' >Issuance Status</th>
<th colspan='2' scope='colgroup' class='thheadpd'>Life</th>
<th colspan='2' scope='colgroup' class='thheadpd'>Health</th>
</tr>
<tr>
<th scope='col' style='font-size:11px;padding:4px !important;'>Count</th>
<th scope='col' style='font-size:11px;padding:4px !important;'>Need Action</th>
<th scope='col' style='font-size:11px;padding:4px !important;'>Count</th>
<th scope='col' style='font-size:11px;padding:4px !important;'>Need Action</th>
</tr>
</thead>
<tbody>
<tr>
<td>Issued</td>
<td class='cursor' onclick=get_issued_info('ALL','2','Issued');>$p_issued</td>
<td class='cursor' onclick=get_issued_info('PEND','2','Issued');>$p_act_issued</td>
<td class='cursor' onclick=get_issued_info('ALL','3','Issued');>$h_issued</td>
<td class='cursor' onclick=get_issued_info('PEND','3','Issued');>$h_act_issued</td>
</tr>
<tr>
<td>Pending For Issuance</td>
<td class='cursor' onclick=get_issued_info('ALL','2','Pending_For_Issuance');>$p_issu_pen</td>
<td class='cursor' onclick=get_issued_info('PEND','2','Pending_For_Issuance');>$p_act_issu_pen</td>
<td class='cursor' onclick=get_issued_info('ALL','3','Pending_For_Issuance');>$h_issu_pen</td>
<td class='cursor' onclick=get_issued_info('PEND','3','Pending_For_Issuance');>$h_act_issu_pen</td>
</tr>
<tr>
<td>Medical Pending</td>
<td class='cursor' onclick=get_issued_info('ALL','2','Medical_Pending');>$p_medi_pen</td>
<td class='cursor' onclick=get_issued_info('PEND','2','Medical_Pending');>$p_act_medi_pen</td>
<td class='cursor' onclick=get_issued_info('ALL','3','Medical_Pending');>$h_medi_pen</td>
<td class='cursor' onclick=get_issued_info('PEND','3','Medical_Pending');>$h_act_medi_pen </td>
</tr>
<tr>
<td>Docs Pending from Sales</td>
<td class='cursor' onclick=get_issued_info('ALL','2','Document_Pending');>$p_doc_pen</td>
<td class='cursor' onclick=get_issued_info('PEND','2','Document_Pending');>$p_act_doc_pen</td>
<td class='cursor' onclick=get_issued_info('ALL','3','Document_Pending');>$h_doc_pen</td>
<td class='cursor' onclick=get_issued_info('PEND','3','Document_Pending');>$h_act_doc_pen</td>
</tr>
<tr>
<td>Cheque Dishonor</td>
<td class='cursor' onclick=get_issued_info('ALL','2','Cheque_Dishonor');>$p_check_dis</td>
<td class='cursor' onclick=get_issued_info('PEND','2','Cheque_Dishonor');>$p_act_check_dis</td>
<td class='cursor' onclick=get_issued_info('ALL','3','Cheque_Dishonor');>$h_check_dis</td>
<td class='cursor' onclick=get_issued_info('PEND','3','Cheque_Dishonor');>$h_act_check_dis</td>
</tr>
<tr>
<td>Medical Decline</td>
<td class='cursor' onclick=get_issued_info('ALL','2','Medical_Decline');>$p_med_dec</td>
<td class='cursor' onclick=get_issued_info('PEND','2','Medical_Decline');>$p_act_med_dec</td>
<td class='cursor' onclick=get_issued_info('ALL','3','Medical_Decline');>$h_med_dec</td>
<td class='cursor' onclick=get_issued_info('PEND','3','Medical_Decline');>$h_act_med_dec</td>
</tr>
</tbody>
</table>
</div>
<div class='col-md-6 pd0'>
<table class='table table-bordered table-hover col-style'>
<thead>
<colgroup span='2'></colgroup>
<colgroup span='2'></colgroup>
<tr>
<th rowspan='2' >Document Status</th>
<th colspan='2' scope='colgroup' class='thheadpd'>Life</th>
<th colspan='2' scope='colgroup' class='thheadpd'>Health</th>
</tr>
<tr>
<th scope='col' style='font-size:11px;padding:4px !important;'>Count</th>
<th scope='col' style='font-size:11px;padding:4px !important;'>Need Action</th>
<th scope='col' style='font-size:11px;padding:4px !important;'>Count</th>
<th scope='col' style='font-size:11px;padding:4px !important;'>Need Action</th>
</tr>
</thead>
<tbody>
<tr>
<td>Dispatched</td>
<td class='cursor' onclick=get_document_info('ALL','2','Dispatched');>$p_dis</td>
<td class='cursor' onclick=get_document_info('PEND','2','Dispatched');>$p_act_dis</td>
<td class='cursor' onclick=get_document_info('ALL','3','Dispatched');>$h_dis</td>
<td class='cursor' onclick=get_document_info('PEND','3','Dispatched');>$h_act_dis</td>
</tr>
<tr>
<td >Not Dispatched</td>
<td class='cursor' onclick=get_document_info('ALL','2','Not_Dispatched');>$p_not_dis</td>
<td class='cursor' onclick=get_document_info('PEND','2','Not_Dispatched');>$p_act_not_dis</td>
<td class='cursor' onclick=get_document_info('ALL','3','Not_Dispatched');>$h_not_dis</td>
<td class='cursor' onclick=get_document_info('PEND','3','Not_Dispatched');>$h_act_not_dis</td>
</tr>
<tr>
<td >Returned To HO</td>
<td class='cursor' onclick=get_document_info('ALL','2','Returned_To_HO');>$p_return</td>
<td class='cursor' onclick=get_document_info('PEND','2','Returned_To_HO');>$p_act_return</td>
<td class='cursor' onclick=get_document_info('ALL','3','Returned_To_HO');>$h_return</td>
<td class='cursor' onclick=get_document_info('PEND','3','Returned_To_HO');>$h_act_return </td>
</tr>
<tr>
<td >Asked For Reprint</td>
<td class='cursor' onclick=get_document_info('ALL','2','Asked_For_Reprint');>$p_reprint</td>
<td class='cursor' onclick=get_document_info('PEND','2','Asked_For_Reprint');>$p_act_reprint</td>
<td class='cursor' onclick=get_document_info('ALL','3','Asked_For_Reprint');>$h_reprint</td>
<td class='cursor' onclick=get_document_info('PEND','3','Asked_For_Reprint');>$h_act_reprint</td>
</tr>
<tr>
<td >Reached Branch</td>
<td class='cursor' onclick=get_document_info('ALL','2','Reached_Branch');>$p_reach</td>
<td class='cursor' onclick=get_document_info('PEND','2','Reached_Branch');>$p_act_reach</td>
<td class='cursor' onclick=get_document_info('ALL','3','Reached_Branch');>$h_reach</td>
<td class='cursor' onclick=get_document_info('PEND','3','Reached_Branch');>$h_act_reach</td>
</tr>
<tr>
<td >Cashless Card Pending</td>
<td class='cursor' onclick=get_document_info('ALL','2','Cashless_Card_Pending');>$p_cash</td>
<td class='cursor' onclick=get_document_info('PEND','2','Cashless_Card_Pending');>$p_act_cash</td>
<td class='cursor' onclick=get_document_info('ALL','3','Cashless_Card_Pending');>$h_cash</td>
<td class='cursor' onclick=get_document_info('PEND','3','Cashless_Card_Pending');>$h_act_cash</td>
</tr>
</tbody>
</table>
</div>
</div>
</div>
<div class='col-md-12'>
<div class='row mg0'>
<div class='col-md-6 pd0'>
<table class='table table-bordered table-hover col-style'>
<thead>
<tr>
<th>RM Manager</th>
<th>RM Name</th>
<th>Life</th>
<th>SIP</th>
<th>Term Plan</th>
</tr>
</thead>
<tbody>
$rm_list
</tbody>
</table>
</div>
</div>
</div>
</div>
</div>";
}else
if($emp_role === "2"){
//Managerd
}else
if($emp_role === "3"){
// CRM Portfolio
if($emp_cat === "2"){
/* Head box - START */
$zeroqry = $this->db->query("SELECT count(met_id) as count,ospos_met_log.met_date FROM ospos_met_log INNER JOIN ospos_lead_type_info ON ospos_met_log.log_lead_id = ospos_lead_type_info.lead_id WHERE lead_status = '9' AND ospos_lead_type_info.created_by = '$emp_id' and first_met='1' group by log_lead_id order by ospos_met_log.met_date desc limit 0,1");
$zeroqry_result = $zeroqry->result_array();
$met_date = $zeroqry_result[0]['met_date'];
if($met_date === ""){
$met_date=date('Y-m-01');
}
$now = time();
$your_date = strtotime($met_date);
$datediff = $now - $your_date;
$Zero_days = floor($datediff / (60 * 60 * 24));
//SELECT count(DISTINCT(met_date)) as met_date FROM `ospos_lead_type_info` where lead_status='9' and ospos_lead_type_info.created_by = '$emp_id' and met_date BETWEEN '$first_day' and '$yesterday'
//Zero Days in a month- crmbox - 1
$zero_in_month = $this->db->query("SELECT count(met_id) as count FROM ospos_met_log INNER JOIN ospos_lead_type_info ON ospos_met_log.log_lead_id = ospos_lead_type_info.lead_id WHERE lead_status = '9' AND ospos_lead_type_info.created_by = '$emp_id' and first_met='1' AND ospos_met_log.met_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]['met_date'];
$days = explode("-", $yesterday);
$days = (int)$days[2];
$month = $days - $month;
$zeroresult = $Zero_days. "/".$month;
//Self Met crmbox - 2
// $self_qry=$this->db->query("SELECT count(*) as count FROM `ospos_lead_type_info` where lead_status='9' and ospos_lead_type_info.created_by = '$emp_id' and met_date like '$cur_month%'");
// echo "SELECT count(distinct ospos_met_log.met_date) as count, MIN( ospos_met_log.met_date ) FROM ospos_met_log INNER JOIN ospos_lead_type_info ON ospos_met_log.log_lead_id = ospos_lead_type_info.lead_id WHERE lead_status = '9' AND ospos_lead_type_info.created_by = '$emp_id' AND ospos_met_log.met_date like '$cur_month%'";
// $self_qry=$this->db->query("SELECT count(distinct ospos_met_log.met_date) as count, MIN( ospos_met_log.met_date ) FROM ospos_met_log INNER JOIN ospos_lead_type_info ON ospos_met_log.log_lead_id = ospos_lead_type_info.lead_id WHERE lead_status = '9' AND ospos_lead_type_info.created_by = '$emp_id' AND ospos_met_log.met_date like '$cur_month%'");
$self_qry=$this->db->query("SELECT count(met_id) as count FROM ospos_met_log INNER JOIN ospos_lead_type_info ON ospos_met_log.log_lead_id = ospos_lead_type_info.lead_id WHERE lead_status = '9' AND ospos_lead_type_info.created_by = '$emp_id' and first_met='1' AND ospos_met_log.met_date like '$cur_month%'");
$self_result = $self_qry->result_array();
$self_count = $self_result[0]['count'];
//echo "SELECT IFNULL(met,0) as met FROM `ospos_incentives` where role = '$emp_role' and category = '$emp_cat' and from_date like '$cur_month%' and to_date like '$cur_month%' ";
$crm_target_qry = $this->db->query("SELECT IFNULL(met,0) as met 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%' ");
$crm_target_result = $crm_target_qry->result_array();
$crm_target = $crm_target_result[0]['met'];
if(!$crm_target){
$crm_target = 0;
}
//Con/Nop & value crmbox - 4
$crm_log_qry = $this->db->query("SELECT count(*) as count FROM `ospos_lead_type_info` left 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 ='$emp_id' and ospos_leads_product.status = '1' and login_date like '$cur_month%'");
$crm_log_result = $crm_log_qry->result_array();
$crm_log = $crm_log_result[0]['count'];
$crm_con_ratio = round(((int)$crm_log / (int)$self_count) * 100);
$crm_nop_qry = $this->db->query("SELECT count(*) as count,IFNULL(sum(overall_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 = '$emp_id' and ospos_leads_product.status = '1' and ncd like '$cur_month%'");
$crm_nop_result = $crm_nop_qry->result_array();
$crm_nop = $crm_nop_result[0]['count'];
$crm_value = $crm_nop_result[0]['value'];
/* Head box - END */
/*Leads - START */
// Today Count
$today_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.created_by = '$emp_id' and ospos_lead_type_info.lead_mode='1' and ncd like '$today%' GROUP by lead_status");
$today_lead_rslt = $today_lead->result_array();
$today_app = 0;
$today_follw = 0;
$today_drop = 0;
foreach ($today_lead_rslt as $key => $value) {
$count = $value['count'];
$statusname = $value['statusname'];
if($statusname === "Appointment"){
$today_app = $count;
}else
if($statusname === "Follow Up"){
$today_follw = $count;
}else
if($statusname === "Dropped"){
$today_drop = $count;
}
}
$today_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.created_by = '$emp_id' and ospos_lead_type_info.lead_mode='1' and created_date like '$today%' GROUP by lead_status");
$today_lead_rslt_cr = $today_lead_cr->result_array();
$today_app_cr = 0;
$today_follw_cr = 0;
$today_drop_cr = 0;
foreach ($today_lead_rslt_cr as $key => $value) {
$count = $value['count'];
$statusname = $value['statusname'];
if($statusname === "Appointment"){
$today_app_cr = $count;
}else
if($statusname === "Follow Up"){
$today_follw_cr = $count;
}else
if($statusname === "Dropped"){
$today_drop_cr = $count;
}
}
// Tomorrow count
$tmrw_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.created_by = '$emp_id' and ospos_lead_type_info.lead_mode='1' and ncd like '$tmrw%' GROUP by lead_status");
$tmrw_lead_rslt = $tmrw_lead->result_array();
$tmrw_app = 0;
$tmrw_follw = 0;
$tmrw_drop = 0;
foreach ($tmrw_lead_rslt as $key => $value) {
$count = $value['count'];
$statusname = $value['statusname'];
if($statusname === "Appointment"){
$tmrw_app = $count;
}else
if($statusname === "Follow Up"){
$tmrw_follw = $count;
}
}
// Month count
$month_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.created_by = '$emp_id' and ospos_lead_type_info.lead_mode='1' and ncd like '$cur_month%' GROUP by lead_status");
$month_lead_rslt = $month_lead->result_array();
$month_app = 0;
$month_follw = 0;
$month_drop = 0;
foreach ($month_lead_rslt as $key => $value) {
$count = $value['count'];
$statusname = $value['statusname'];
if($statusname === "Appointment"){
$month_app = $count;
}else
if($statusname === "Follow Up"){
$month_follw = $count;
}else
if($statusname === "Dropped"){
$month_drop = $count;
}
}
$month_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.created_by = '$emp_id' and created_date like '$cur_month%' GROUP by lead_status");
$month_lead_rslt_cr = $month_lead_cr->result_array();
$month_app_cr = 0;
$month_follw_cr = 0;
$month_drop_cr = 0;
foreach ($month_lead_rslt_cr as $key => $value) {
$count = $value['count'];
$statusname = $value['statusname'];
if($statusname === "Appointment"){
$month_app_cr = $count;
}else
if($statusname === "Follow Up"){
$month_follw_cr = $count;
}else
if($statusname === "Dropped"){
$month_drop_cr = $count;
}
}
/* Lead - END*/
/*RM Status - START */
// Created date
//$rm_crdate = $this->db->query("SELECT IFNULL(count(*),0) as count,prospect_name FROM `ospos_lead_type_info` left JOIN ospos_prospect_level on prospect_id = prospect_level where ospos_lead_type_info.created_by = '$emp_id' and lead_status = '9' and met_date like '$cur_month%' GROUP by prospect_name");
$rm_crdate = $this->db->query("SELECT IFNULL(count(met_id),0) as count,prospect_name FROM ospos_met_log INNER JOIN ospos_lead_type_info ON ospos_met_log.log_lead_id = ospos_lead_type_info.lead_id inner JOIN ospos_prospect_level on ospos_lead_type_info.prospect_level = ospos_prospect_level.prospect_id WHERE lead_status = '9' AND ospos_lead_type_info.created_by = '$emp_id' AND first_met='1' AND ospos_met_log.met_date like '$cur_month%' and ospos_lead_type_info.status='1' GROUP by prospect_name");
$rm_crdate_rslt = $rm_crdate->result_array();
$crdate_hot = 0;
$crdate_warm = 0;
$crdate_drop = 0;
$crdate_log = 0;
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;
}else
if($prospect_name === "Login"){
$crdate_log = $count;
}
}
// NCD
$rm_ncd = $this->db->query("SELECT IFNULL(count(*),0) as count,prospect_level,prospect_name FROM `ospos_lead_type_info` inner JOIN ospos_prospect_level on prospect_id = prospect_level where ospos_lead_type_info.created_by = '$emp_id' and lead_status = '9' and ncd like '$cur_month%' and ospos_lead_type_info.met_date not like '$cur_month%' and ospos_lead_type_info.status='1' GROUP by prospect_level");
$rm_ncd_rslt = $rm_ncd->result_array();
$ncd_hot = 0;
$ncd_warm = 0;
$ncd_drop = 0;
$ncd_log = 0;
foreach ($rm_ncd_rslt as $key => $value) {
$count = $value['count'];
$prospect_name = $value['prospect_name'];
if($prospect_name === "Hot"){
$ncd_hot = $count;
}else
if($prospect_name === "Warm"){
$ncd_warm = $count;
}else
if($prospect_name === "Dropped"){
$ncd_drop = $count;
}else
if($prospect_name === "Login"){
$ncd_log = $count;
}
}
// C-Ratio
$rm_crdate_nop = $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 = '$emp_id' and lead_status = '9' and ospos_lead_type_info.status='1' and ospos_met_log.met_date like '$cur_month%' and ospos_met_log.first_met='1'");
$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 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 = '$emp_id' and ospos_leads_product.status = '1' and ospos_met_log.met_date like '$cur_month%' and ospos_met_log.first_met='1' and ospos_lead_type_info.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 = '$emp_id' and lead_status = '9' and ncd like '$cur_month%' and met_date not 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 = '$emp_id' and ospos_lead_type_info.ncd like '$cur_month%' and met_date not like '$cur_month%' and ospos_leads_product.status = '1' ");
$rm_ncd_log_rslt = $rm_ncd_log->result_array();
$rm_ncd_log = $rm_ncd_log_rslt[0]['count'];
$ncd_cratio = round(((int)$rm_ncd_log / (int)$ncd_nop) * 100);
//
/*RM Status -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) = '$fin_start_month' or YEAR(to_date) = '$fin_end_month')");
$goal_sheet_rslt = $goal_sheet->result_array();
$req_met = $goal_sheet_rslt[0]['met'];
$conversion_ratio = $goal_sheet_rslt[0]['conversion_ratio'];
$actual_month = $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 BETWEEN '$fin_start_month' and '$fin_end_month') 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_met = $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 BETWEEN '$fin_start_month' and '$fin_end_month')");
$login_result = $login_qry->result_array();
$login_count = $login_result[0]['count'];
//Total login value
$login_value_qry = $this->db->query("SELECT IFNULL(sum(overall_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 = '$emp_id' and ospos_leads_product.status = '1'");
$login_value_result = $login_value_qry->row();
$tot_log_val = $login_value_result->net_premium;
/*Goal Sheet - END */
echo "<div class='col-md-12 pd0 dash_header'>
<h3 class='text-center headtxt'>CRM Portfolio</h3>
<div class='row mg0 mgb15'>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Zero Days / Zero in a Month'>
<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>Zero</h5> <span class='bold'>$zeroresult</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Self Met / Target Met'>
<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'> $self_count/$crm_target</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-toggle='tooltip' data-placement='right' title='Last Month Earned Incentive'>
<div class='dashbox blue'>
<table>
<tr>
<td class='pd10'><i class='fa fa-ticket fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>Incentive</h5> <span class='bold'> -</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' 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'> $crm_con_ratio% / $crm_nop & $crm_value</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-6'>
<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,$emp_id,1,'T','');>$today_app</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,2,'T','');>$today_follw</td>
</tr>
<tr>
<td >Tomorrow</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,1,'TW','');>$tmrw_app</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,2,'TW','');>$tmrw_follw</td>
</tr>
<tr>
<td >Month</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,1,'M','');>$month_app</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,2,'M','');>$month_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,$emp_id,1,'T','created_date');>$today_app_cr</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,2,'T','created_date');>$today_follw_cr</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,3,'M','created_date');>$today_drop_cr</td>
</tr>
<tr>
<td >Month</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,1,'M','created_date');>$month_app_cr</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,2,'M','created_date');>$month_follw_cr</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,3,'M','created_date');>$month_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>$required_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 pdl0'>
<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 class='col-md-12'>
<div class='row mg0'>
<div class='col-md-12'>
<table class='table table-bordered table-hover col-style'>
<thead>
<tr>
<th>RM Status</th>
<th>MET</th>
<th>Login</th>
<th>Hot</th>
<th>Warm</th>
<th>Dropped</th>
<th>C- Ratio</th>
</tr>
</thead>
<tbody>
<tr>
<td >Current Month</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,9,'M','PMET');>$crdate_nop</td>
<td class='cursor' onclick=get_rm_lead_info($emp_role,$emp_cat,$emp_id,4,'CRMMET','');>$crdate_log</td>
<td class='cursor' onclick=get_rm_lead_info($emp_role,$emp_cat,$emp_id,1,'CRMMET','');>$crdate_hot</td>
<td class='cursor' onclick=get_rm_lead_info($emp_role,$emp_cat,$emp_id,2,'CRMMET','');>$crdate_warm</td>
<td class='cursor' onclick=get_rm_lead_info($emp_role,$emp_cat,$emp_id,3,'CRMMET','');>$crdate_drop</td>
<td>$crdate_cratio%</td>
</tr>
<tr>
<td >NCD</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,9,'M','METNCD');>$ncd_nop</td>
<td class='cursor' onclick=get_rm_lead_info($emp_role,$emp_cat,$emp_id,4,'NCD','');>$ncd_log</td>
<td class='cursor' onclick=get_rm_lead_info($emp_role,$emp_cat,$emp_id,1,'NCD','');>$ncd_hot</td>
<td class='cursor' onclick=get_rm_lead_info($emp_role,$emp_cat,$emp_id,2,'NCD','');>$ncd_warm</td>
<td class='cursor' onclick=get_rm_lead_info($emp_role,$emp_cat,$emp_id,3,'NCD','');>$ncd_drop</td>
<td>$ncd_cratio%</td>
</tr>
</tbody>
</table>
</div>
</div>
</div>
</div>";
}
// CRM Health
if($emp_cat === "3"){
/* Head box - START */
$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_lead_type_info.category = '3' and ospos_leads_product.status = '1' and ospos_lead_type_info.created_by = '$emp_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));
//Zero Days in a month- crmbox - 1
$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_lead_type_info.category = '3' and ospos_leads_product.status = '1' and ospos_lead_type_info.created_by = '$emp_id' 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;
$zeroresult = $Zero_days. "/".$month;
//Self Met crmbox - 2
$self_qry=$this->db->query("SELECT IFNULL(sum(overall_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_lead_type_info.category = '3' and ospos_leads_product.status = '1' and ospos_lead_type_info.created_by = '$emp_id' and login_date like '$cur_month%'");
$self_result = $self_qry->result_array();
$self_value = round($self_result[0]['value']);
$crm_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%' ");
$crm_target_result = $crm_target_qry->result_array();
$crm_target = $crm_target_result[0]['value'];
if(!$crm_target){
$crm_target = 0;
}
//Con/Nop & value crmbox - 4
//echo "SELECT count(DISTINCT (lead_id)) 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 ospos_lead_type_info.created_by = '$emp_id' and ospos_lead_type_info.prospect_level = '4' and ospos_lead_type_info.category='3' and login_date like '$cur_month%'";
$crm_nop_qry = $this->db->query("SELECT count(DISTINCT (lead_id)) 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 ospos_lead_type_info.created_by = '$emp_id' and ospos_lead_type_info.prospect_level = '4' and ospos_lead_type_info.category='3' and login_date like '$cur_month%' and ospos_leads_product.status = '1' ");
$crm_nop_result = $crm_nop_qry->result_array();
$crm_nop = $crm_nop_result[0]['count'];
$avg_value = round($self_value / $crm_nop);
if(!$avg_value){
$avg_value = 0;
}
//Avg per month
$crm_avgmonth_qry = $this->db->query("SELECT count(*) as count,IFNULL(sum(overall_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 = '$emp_id' and login_date BETWEEN '$fin_start_month' and ospos_leads_product.status = '1' and '$fin_end_month%' ");
$crm_avgmonth_result = $crm_avgmonth_qry->result_array();
$crm_avgmonth = round($crm_avgmonth_result[0]['value']);
$crm_nop_qry = $this->db->query("SELECT count(*) as count FROM `ospos_lead_type_info` where ospos_lead_type_info.created_by = '$emp_id' and ospos_lead_type_info.prospect_level = '4' and ncd BETWEEN '$fin_start_month' and '$fin_end_month%'");
$crm_nop_result = $crm_nop_qry->result_array();
$fy_crm_nop = $crm_nop_result[0]['count'];
$cr_lst_mth = $lst_mth -1;
$health_avg = round($crm_avgmonth / $cr_lst_mth);
/* Head box - END */
/*Leads - START */
// 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 = '$emp_id' 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 = '$emp_id' 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 = '$emp_id' 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 = '$emp_id' 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 = '$emp_id' 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 ($emp_id) and ospos_lead_type_info.ncd like '$cur_month%' and ospos_lead_type_info.prospect_level = '4' and ospos_leads_product.status = '1' 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;
}
else {
$not_issued = $netamount;
}
}
/*Issuance Status - END*/
/*Goal Sheet - START */
$goal_sheet = $this->db->query("SELECT premium,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_value = $goal_sheet_rslt[0]['premium'];
$conversion_ratio = $goal_sheet_rslt[0]['conversion_ratio'];
$actual_month = round($req_value / 12);
//Pending
$collected_qry=$this->db->query("SELECT IFNULL(sum(overall_net_premium),0) as value FROM `ospos_lead_type_info` left join ospos_leads_product on lead_info_id = lead_type_id where lead_status='11' and ospos_lead_type_info.created_by = '$emp_id' and ncd between '$fin_start_month' and '$fin_end_month' and ncd not like '$cur_month%' and ospos_leads_product.status = '1'");
$collected_result = $collected_qry->result_array();
$collected_value = $collected_result[0]['value'];
$fnl_collected_value = $req_value - $collected_value;
$req_gola_amt = $actual_month + $fnl_collected_value;
//Total login value
$login_value_qry = $this->db->query("SELECT IFNULL(sum(overall_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 = '$emp_id' and ospos_leads_product.status = '1'");
$login_value_result = $login_value_qry->row();
$tot_log_val = $login_value_result->net_premium;
/*Goal Sheet - END */
echo "<div class='col-md-12 pd0 dash_header'>
<h3 class='text-center headtxt'>CRM Health</h3>
<div class='row mg0 mgb15'>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Zero Days / Zero in a Month'>
<div class='dashbox orange'>
<table>
<tr>
<td class='pd10'><i class='fa fa-user-md fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>Zero</h5> <span class='bold'>$zeroresult</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Self Login Value'>
<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'> $self_value / $crm_target</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Nop & Avg Premium' >
<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'> $crm_nop / $avg_value</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Avg Per month current FY. Excluding current Month'>
<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>Avg Per</h5> <span class='bold'> $health_avg</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-toggle='tooltip' data-placement='right' title='Last Month Earned Incentive'>
<div class='dashbox yellow'>
<table>
<tr>
<td class='pd10'><i class='fa fa-ticket fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>Incentive</h5> <span class='bold'> -</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 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,$emp_id,1,'T','HLPROSPECT');>$today_hot</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,2,'T','HLPROSPECT');>$today_warm</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,4,'T','HLPROSPECT');>$today_log</td>
</tr>
<tr>
<td >Tomorrow</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,1,'TW','HLPROSPECT');>$tmrw_hot</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,2,'TW','HLPROSPECT');>$tmrw_warm</td>
<td > - </td>
</tr>
<tr>
<td >Month</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,1,'M','HLPROSPECT');>$month_hot</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,2,'M','HLPROSPECT');>$month_warm</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,4,'M','HLPROSPECT');>$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,$emp_id,1,'T','PROSPECT_CR');>$today_hot_cr</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,2,'T','PROSPECT_CR');>$today_warm_cr</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,4,'T','PROSPECT_CR');>$today_log_cr</td>
</tr>
<tr>
<td >Month</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,1,'M','PROSPECT_CR');>$month_hot_cr</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,2,'M','PROSPECT_CR');>$month_warm_cr</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,$emp_id,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>Goal Sheet</th>
<th>This Month Actual</th>
<th>Pending</th>
<th>Total Required</th>
</tr>
</thead>
<tbody>
<tr>
<td >Collected</td>
<td>$actual_month</td>
<td>$fnl_collected_value</td>
<td>$req_gola_amt</td>
</tr>
<tr>
<td >NOP</td>
<td>-</td>
<td>-</td>
<td>-</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,$emp_id,'issued','M','');>$issued</td>
<td class='cursor' onclick=get_lead_info_issued($emp_role,$emp_cat,$emp_id,'not_issued','M','');>$not_issued</td>
<td class='cursor' onclick=get_lead_info_issued($emp_role,$emp_cat,$emp_id,'med_pend','M','');>$med_pend</td>
</tr>
</tbody>
</table>
</div>
</div>
</div>
<div class='col-md-6' style='padding-right: 0px !important;'>
<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>";
}
}else
if($emp_role === "5"){
include('rmdashboard.php');
}else
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;
*/
$team = $this->db->query("SELECT GROUP_CONCAT(teammembers SEPARATOR ',') as teammembers FROM `ospos_team` where teamleader in ($emp_id) and deleted = '0'");
$team_info = $team->result_array();
$map_crm = $team_info[0]['teammembers'];
$team_ids = explode(",",$map_crm);
$map_count = substr_count($team_ids, ",") +1;
$team_ids[] = $emp_id;
$team_ids = implode(",",$team_ids);
$team_count = substr_count($team_ids, ",") +1;
//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(overall_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 like '$cur_month%' 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 like '$cur_month%' 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)$crdate_log / (int)$tm_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(overall_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'>Yesterday</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>Manager</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(overall_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(overall_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(overall_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(overall_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(overall_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(overall_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(overall_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 id='tbl' 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>";
}
}else
if($emp_role === "7"){
//MIS
$issues_info = $this->db->query("SELECT ospos_leads_product.product_category as category, IFNULL(sum(overall_net_premium),0) as netamount FROM ospos_leads_product left join ospos_happy_calling on call_product_id = customer_product_id where ospos_leads_product.login_date like '$cur_month%' and ospos_leads_product.status = '1' and issuance_status = 'Issued' GROUP by ospos_leads_product.product_category");
$issues_rslt = $issues_info->result();
$life_issued = 0;
$health_issued = 0;
foreach ($issues_rslt as $key => $value) {
$category = $value->category;
$netamount = $value->netamount;
if($category === "2"){
$life_issued = number_format($netamount);
}
if($category === "3"){
$health_issued = number_format($netamount);
}
}
$log_info = $this->db->query("SELECT ospos_leads_product.product_category as category, IFNULL(sum(overall_net_premium),0) as netamount FROM ospos_lead_type_info left join ospos_leads_product on lead_type_id = lead_info_id inner join ospos_happy_calling on ospos_happy_calling.call_product_id = ospos_leads_product.customer_product_id where ospos_leads_product.login_date like '$cur_month%' and ospos_lead_type_info.status = '1' and ospos_leads_product.status = '1' GROUP by ospos_leads_product.product_category");
$log_rslt = $log_info->result();
$life_log = 0;
$health_log = 0;
$term_log = 0;
foreach ($log_rslt as $key => $value) {
$category = $value->category;
$netamount = $value->netamount;
if($category === "2"){
$life_log = $netamount;
}
if($category === "3"){
$health_log = $netamount;
}
if($category === "5"){
$term_log = $netamount;
}
}
$mf_info = $this->db->query("SELECT mf_investment,IFNULL(sum(mf_amount),0) as netamount FROM `ospos_leads_product` where ospos_leads_product.login_date like '$cur_month%' and ospos_leads_product.status = '1' GROUP by mf_investment");
$mf_rslt = $mf_info->result();
$sip_amt = 0;
$lumpsum_amt = 0;
foreach ($mf_rslt as $key => $value) {
$type = $value->mf_investment;
$netamount = $value->netamount;
if($type === "1"){
$sip_amt = $netamount;
}
if($type === "2"){
$lumpsum_amt = $netamount;
}
}
/*
$rm_info = $this->db->query("SELECT id,first_name,reporting,IFNULL(sum(overall_net_premium),0) as netamount FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id left join ospos_lead_type_info on ospos_lead_type_info.created_by = ospos_employees.id left join ospos_leads_product on lead_type_id = lead_info_id where role = '5' and deleted = '0' and ospos_leads_product.status = '1' GROUP by reporting,id ORDER BY `netamount` ASC");
$rm_rslt = $rm_info->result();
$rm_list = "";
foreach ($rm_rslt as $key => $value) {
$id = $value->id;
$first_name = $value->first_name;
$reporting = $value->reporting;
$rm_manager_info = $this->db->query("SELECT id,first_name FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id where ospos_employees.id = '$reporting' ");
$rm_manager_rslt = $rm_manager_info->row();
$rm_manage = $rm_manager_rslt->first_name;
$lead_info = $this->db->query("SELECT ospos_leads_product.product_category as category,IFNULL(sum(overall_net_premium),0) as netamount,IFNULL(sum(mf_amount),0) as mf_amount,mf_investment FROM ospos_lead_type_info INNER join ospos_leads_product on lead_type_id = lead_info_id where login_date like '$cur_month%' and (ospos_lead_type_info.created_by = '$id' or ospos_lead_type_info.rm_name = '$id') and mf_investment != '2' and ospos_leads_product.status = '1' GROUP by ospos_leads_product.product_category");
$lead_rslt = $lead_info->result();
$lifeamount = 0;
$mfamount = 0;
$termamount = 0;
foreach($lead_rslt as $key => $value) {
$category = $value->category;
$netamount = $value->netamount;
$mf_amount = $value->mf_amount;
$type = $value->mf_investment;
if($category === "2"){
$lifeamount = $netamount;
}
if($category === "4"){
$mfamount = $mf_amount;
}
if($category === "5"){
$termamount = $netamount;
}
}
$rm_list .= "<tr>
<td>$rm_manage</td>
<td>$first_name</td>
<td class='cursor' onclick=get_mis_rmlead($emp_role,$emp_cat,$id,2,'M','');>$lifeamount</td>
<td class='cursor' onclick=get_mis_rmlead($emp_role,$emp_cat,$id,4,'M','SIP');>$mfamount</td>
<td class='cursor' onclick=get_mis_rmlead($emp_role,$emp_cat,$id,2,'M','');>$termamount</td>
</tr>";
}
$ht_tl_info = $this->db->query("SELECT id,first_name,reporting FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id where role = '6' and deleted = '0' and category = '3' ORDER BY ospos_employees.reporting ASC");
$ht_tl_rslt = $ht_tl_info->result();
$ht_tl_list = "";
foreach ($ht_tl_rslt as $key => $value) {
$id = $value->id;
$first_name = $value->first_name;
$team_info = $this->db->query("SELECT GROUP_CONCAT(id) as team_ids FROM `ospos_employees` WHERE reporting = '$id'");
$team_rslt = $team_info->row();
$team_ids = $team_rslt->team_ids.",".$id;
$lead_info = $this->db->query("SELECT ospos_leads_product.company,vendorcompanyname,ospos_leads_product.product_category as category,IFNULL(sum(overall_net_premium),0) as netamount FROM ospos_lead_type_info inner join ospos_leads_product on lead_type_id = lead_info_id inner join ospos_happy_calling on ospos_happy_calling.call_product_id = ospos_leads_product.customer_product_id left join ospos_vendor on vendor_id = ospos_leads_product.company where ospos_leads_product.login_date like '$cur_month%' and ospos_lead_type_info.created_by in ($team_ids) and ospos_lead_type_info.status = '1' and ospos_leads_product.status = '1' GROUP by ospos_leads_product.company");
$lead_rslt = $lead_info->result();
$star_amt = 0;
$nj_amt = 0;
$other_amt = 0;
foreach ($lead_rslt as $key => $value){
$company = $value->vendorcompanyname;
$netamount = $value->netamount;
if($company === "Star Health"){
$star_amt += $netamount;
}else
if($company === "NJ"){
$nj_amt += $netamount;
}else{
$other_amt += $netamount;
}
}
$net = $star_amt + $nj_amt + $other_amt;
$ht_tl_list .= "<tr>
<td>$first_name</td>
<td class='cursor' onclick=get_mis_hllead($emp_role,$emp_cat,'$team_ids',3,'M','STAR');>$star_amt</td>
<td class='cursor' onclick=get_mis_hllead($emp_role,$emp_cat,'$team_ids',3,'M','NJ');>$nj_amt</td>
<td class='cursor' onclick=get_mis_hllead($emp_role,$emp_cat,'$team_ids',3,'M','OTHERS');>$other_amt</td>
<td>$net</td>
</tr>";
}
$mis_info = $this->db->query("SELECT count(DISTINCT(lead_id)) as count,IFNULL(sum(overall_net_premium),0) as value,category FROM `ospos_lead_type_info` left JOIN ospos_leads_product on lead_info_id = lead_type_id where current_control = 'MIS' and ospos_lead_type_info.status = '1' and ospos_leads_product.status = '1' and login_date like '$cur_month%' GROUP by category");
$mis_rslt = $mis_info->result();
$mis_life_count = 0;
$mis_life_amt = 0;
$mis_hl_count = 0;
$mis_hl_amt = 0;
foreach ($mis_rslt as $key => $value) {
$count = $value->count;
$category = $value->category;
$netamount = $value->value;
if($category === "2"){
$mis_life_count = $count;
$mis_life_amt = $netamount;
}else
if($category === "3"){
$mis_hl_count = $count;
$mis_hl_amt = $netamount;
}
}
$hc_info = $this->db->query("SELECT count(DISTINCT(customer_product_id)) as count,ospos_leads_product.product_category as category,IFNULL(sum(overall_net_premium),0) as netamount FROM ospos_lead_type_info inner join ospos_leads_product on lead_type_id = lead_info_id where current_control = 'HC' and ospos_lead_type_info.status = '1' and ospos_leads_product.status = '1' and login_date like '$cur_month%' GROUP by ospos_leads_product.product_category");
$hc_rslt = $hc_info->result();
$hc_life_count = 0;
$hc_life_amt = 0;
$hc_hl_count = 0;
$hc_hl_amt = 0;
foreach ($hc_rslt as $key => $value) {
$count = $value->count;
$category = $value->category;
$netamount = $value->netamount;
if($category === "2"){
$hc_life_count = $count;
$hc_life_amt = $netamount;
}
if($category === "3"){
$hc_hl_count = $count;
$hc_hl_amt = $netamount;
}
}
$completed_info = $this->db->query("SELECT count(DISTINCT(customer_product_id)) as count,ospos_leads_product.product_category as category,IFNULL(sum(overall_net_premium),0) as netamount FROM ospos_lead_type_info inner join ospos_leads_product on lead_type_id = lead_info_id where current_control = 'completed' and ospos_leads_product.status = '1' and ncd like '$cur_month%' GROUP by ospos_leads_product.product_category");
$completed_rslt = $completed_info->result();
$cp_life_count = 0;
$cp_life_amt = 0;
$cp_hl_count = 0;
$cp_hl_amt = 0;
foreach ($completed_rslt as $key => $value) {
$count = $value->count;
$category = $value->category;
$netamount = $value->netamount;
if($category === "2"){
$cp_life_count = $count;
$cp_life_amt = $netamount;
}
if($category === "3"){
$cp_hl_count = $count;
$cp_hl_amt = $netamount;
}
}
$to_year = (date('m') > 3) ? date('Y') +1 : date('Y');
$frm_year = $to_year - 1;
$from = $frm_year."-04-01";
$to = $to_year."-03-31";
$fy_info = $this->db->query("SELECT count(DISTINCT(lead_id)) as count,ospos_leads_product.product_category as category,IFNULL(sum(overall_net_premium),0) as netamount FROM ospos_lead_type_info inner join ospos_leads_product on lead_type_id = lead_info_id where (ospos_lead_type_info.created_date BETWEEN '$from' and '$to') and ospos_leads_product.status = '1' GROUP by ospos_leads_product.product_category");
$fy_rslt = $fy_info->result();
$fy_life_count = 0;
$fy_life_amt = 0;
$fy_life_avg = 0;
$fy_hl_count = 0;
$fy_hl_amt = 0;
$fy_hl_avg = 0;
foreach($fy_rslt as $key => $value) {
$count = $value->count;
$category = $value->category;
$netamount = $value->netamount;
if($category === "2"){
$fy_life_count = $count;
$fy_life_amt = $netamount;
$fy_life_avg = round((int)$fy_life_amt / (int)$fy_life_count);
}
if($category === "3"){
$fy_hl_count = $count;
$fy_hl_amt = $netamount;
$fy_hl_avg = round((int)$fy_hl_amt / (int)$fy_hl_count);
}
}
//Issuance Status
$issuance_info = $this->db->query("SELECT count(*) as count,issuance_status,cat_name FROM `ospos_happy_calling` left join ospos_leads_product on customer_product_id = call_product_id left join ospos_lead_type_info on lead_type_id = lead_info_id left join ospos_category on cat_id = product_category where ospos_leads_product.login_date like '$cur_month%' and ospos_leads_product.status = '1' and ospos_lead_type_info.status = '1' GROUP BY issuance_status,product_category");
$issuance_rslt = $issuance_info->result();
// print_r($issuance_rslt);
$h_issued = 0;
$h_issu_pen = 0;
$h_medi_pen = 0;
$h_doc_pen = 0;
$h_check_dis = 0;
$h_med_dec = 0;
$h_iss_null = 0;
$p_issued = 0;
$p_issu_pen = 0;
$p_medi_pen = 0;
$p_doc_pen = 0;
$p_check_dis = 0;
$p_med_dec = 0;
$p_iss_null = 0;
foreach($issuance_rslt as $key => $value) {
$count = $value->count;
$category = $value->cat_name;
$issuance_status = $value->issuance_status;
if($category === "Portfolio"){
if($issuance_status === "Issued"){
$p_issued = $count;
}else
if($issuance_status === "Pending For Issuance"){
$p_issu_pen = $count;
}else
if($issuance_status === "Medical Pending"){
$p_medi_pen = $count;
}else
if($issuance_status === "Docs Pending from Sales"){
$p_doc_pen = $count;
}else
if($issuance_status === "Cheque Dishonor"){
$p_check_dis = $count;
}else
if($issuance_status === "Medical Declined"){
$p_med_dec = $count;
}else
if(!$issuance_status){
$p_iss_null = $count;
}
}else
if($category === "Health Insurance"){
if($issuance_status === "Issued"){
$h_issued = $count;
}else
if($issuance_status === "Pending For Issuance"){
$h_issu_pen = $count;
}else
if($issuance_status === "Medical Pending"){
$h_medi_pen = $count;
}else
if($issuance_status === "Docs Pending from Sales"){
$h_doc_pen = $count;
}else
if($issuance_status === "Cheque Dishonor"){
$h_check_dis = $count;
}else
if($issuance_status === "Medical Declined"){
$h_med_dec = $count;
}else
if(!$issuance_status){
$h_iss_null = $count;
}
}
}
$p_issu_pen = $p_issu_pen + $p_iss_null;
$h_issu_pen = $h_issu_pen + $h_iss_null;
$action_info = $this->db->query("SELECT count(*) as count,issuance_status,cat_name FROM `ospos_happy_calling` left join ospos_leads_product on customer_product_id = call_product_id inner join ospos_lead_type_info on lead_type_id = lead_info_id inner join ospos_category on cat_id = product_category WHERE ospos_leads_product.status = '1' and ospos_lead_type_info.status = '1' AND ospos_leads_product.login_date <= DATE_ADD(CURDATE(),INTERVAL -7 DAY) GROUP BY issuance_status,cat_name");
$action_rslt = $action_info->result();
$h_act_issued = 0;
$h_act_issu_pen = 0;
$h_act_medi_pen = 0;
$h_act_doc_pen = 0;
$h_act_check_dis = 0;
$h_act_med_dec = 0;
$h_act_null = 0;
$p_act_issued = 0;
$p_act_issu_pen = 0;
$p_act_medi_pen = 0;
$p_act_doc_pen = 0;
$p_act_check_dis = 0;
$p_act_med_dec = 0;
$p_act_null = 0;
foreach($action_rslt as $key => $value) {
$count = $value->count;
$category = $value->cat_name;
$issuance_status = $value->issuance_status;
if($category === "Portfolio"){
if($issuance_status === "Pending For Issuance"){
$p_act_issu_pen = $count;
}else
if($issuance_status === "Medical Pending"){
$p_act_medi_pen = $count;
}else
if($issuance_status === "Docs Pending from Sales"){
$p_act_doc_pen = $count;
}else
if($issuance_status === "Cheque Dishonor"){
$p_act_check_dis = $count;
}else
if($issuance_status === "Medical Declined"){
$p_act_med_dec = $count;
}else
if(!$issuance_status){
$p_act_null = $count;
}
}else
if($category === "Health Insurance"){
if($issuance_status === "Pending For Issuance"){
$h_act_issu_pen = $count;
}else
if($issuance_status === "Medical Pending"){
$h_act_medi_pen = $count;
}else
if($issuance_status === "Docs Pending from Sales"){
$h_act_doc_pen = $count;
}else
if($issuance_status === "Cheque Dishonor"){
$h_act_check_dis = $count;
}else
if($issuance_status === "Medical Declined"){
$h_act_med_dec = $count;
}else
if(!$issuance_status){
$h_act_null = $count;
}
}
}
$p_act_issu_pen = $p_act_issu_pen + $p_act_null;
$h_act_issu_pen = $h_act_issu_pen + $h_act_null;
//Document Status
$document_info = $this->db->query("SELECT count(*) as count,document_issue_status,cat_name FROM `ospos_happy_calling` left join ospos_leads_product on customer_product_id = call_product_id inner join ospos_lead_type_info on lead_type_id = lead_info_id inner join ospos_category on cat_id = product_category where issued_date like '$cur_month%' and ospos_leads_product.status = '1' and ospos_lead_type_info.status = '1' GROUP BY document_issue_status,product_category");
$document_rslt = $document_info->result();
$h_dis = 0;
$h_not_dis = 0;
$h_return = 0;
$h_reprint = 0;
$h_reach = 0;
$h_cash = 0;
$h_null = 0;
$p_dis = 0;
$p_not_dis = 0;
$p_return = 0;
$p_reprint = 0;
$p_reach = 0;
$p_cash = "N/A";
$p_null = 0;
foreach($document_rslt as $key => $value){
$count = $value->count;
$category = $value->cat_name;
$document_issue_status = $value->document_issue_status;
if($category === "Portfolio"){
if($document_issue_status === "Dispatched"){
$p_dis = $count;
}else
if($document_issue_status === "NotDispatched"){
$p_not_dis = $count;
}else
if($document_issue_status === "ReturnedToHO"){
$p_return = $count;
}else
if($document_issue_status === "AskedForReprint"){
$p_reprint = $count;
}else
if($document_issue_status === "ReachedBranch"){
$p_reach = $count;
}else
if($document_issue_status === "CashlessCardPending"){
$p_cash = $count;
}else
if(!$document_issue_status){
$p_null = $count;
}
}else
if($category === "Health Insurance"){
if($document_issue_status === "Dispatched"){
$h_dis = $count;
}else
if($document_issue_status === "NotDispatched"){
$h_not_dis = $count;
}else
if($document_issue_status === "ReturnedToHO"){
$h_return = $count;
}else
if($document_issue_status === "AskedForReprint"){
$h_reprint = $count;
}else
if($document_issue_status === "ReachedBranch"){
$h_reach = $count;
}else
if($document_issue_status === "CashlessCardPending"){
$h_cash = $count;
}else
if(!$document_issue_status){
$h_null = $count;
}
}
}
$p_not_dis = $p_null + $p_not_dis;
$h_not_dis = $h_not_dis + $h_null;
$doc_action_info = $this->db->query("SELECT count(*) as count,document_issue_status,ospos_happy_calling.issued_date,cat_name FROM `ospos_happy_calling` left join ospos_leads_product on customer_product_id = call_product_id inner join ospos_lead_type_info on lead_type_id = lead_info_id inner join ospos_category on cat_id = product_category WHERE issuance_status = 'Issued' and ospos_happy_calling.issued_date <= DATE_ADD(CURDATE(),INTERVAL -7 DAY) and ospos_leads_product.status = '1' and ospos_lead_type_info.status = '1' GROUP BY document_issue_status,cat_name");
$doc_action_rslt = $doc_action_info->result();
$h_act_dis = 0;
$h_act_not_dis = 0;
$h_act_return = 0;
$h_act_reprint = 0;
$h_act_reach = 0;
$h_act_cash = 0;
$h_act_null = 0;
$p_act_dis = 0;
$p_act_not_dis = 0;
$p_act_return = 0;
$p_act_reprint = 0;
$p_act_reach = 0;
$p_act_cash = "N/A";
$p_act_null = 0;
foreach($doc_action_rslt as $key => $value) {
$count = $value->count;
$category = $value->cat_name;
$document_issue_status = $value->document_issue_status;
if($category === "Portfolio"){
if($document_issue_status === "NotDispatched"){
$p_act_not_dis = $count;
}else
if($document_issue_status === "ReturnedToHO"){
$p_act_return = $count;
}else
if($document_issue_status === "AskedForReprint"){
$p_act_reprint = $count;
}else
if($document_issue_status === "ReachedBranch"){
$p_act_reach = $count;
}else
if($document_issue_status === "CashlessCardPending"){
$p_act_cash = $count;
}else
if((!$document_issue_status) or $document_issue_status === ""){
$p_act_null += $count;
}
}else
if($category === "Health Insurance"){
if($document_issue_status === "NotDispatched"){
$h_act_not_dis = $count;
}else
if($document_issue_status === "ReturnedToHO"){
$h_act_return = $count;
}else
if($document_issue_status === "AskedForReprint"){
$h_act_reprint = $count;
}else
if($document_issue_status === "ReachedBranch"){
$h_act_reach = $count;
}else
if($document_issue_status === "CashlessCardPending"){
$h_act_cash = $count;
}else
if(!$document_issue_status){
$h_act_null += $count;
}
}
}
$p_act_not_dis = $p_act_not_dis + $p_act_null;
$h_act_not_dis = $h_act_not_dis + $h_act_null;
*/
echo "<div class='col-md-12 pd0 dash_header'>
<h3 class='text-center headtxt'>MIS Dashboard</h3>
<div class='row mg0 mgb15'>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Total Issued /Life WFP Logged'>
<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>Life Logged</h5> <span class='bold'> $life_issued / $life_log</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Total Issued /Life WFP Logged'>
<div class='dashbox red'>
<table>
<tr>
<td class='pd10'><i class='fa fa-user-md fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>Health Logged</h5> <span class='bold'> $health_issued / $health_log</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Total Issued /Life WFP Logged'>
<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>SIP / Lumpsum</h5> <span class='bold'> $sip_amt / $lumpsum_amt</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Total Issued /Life WFP Logged'>
<div class='dashbox violet'>
<table>
<tr>
<td class='pd10'><i class='fa fa-repeat fa-2x' aria-hidden='true'></i></td>
<td class='white'><h5>Term Plan</h5> <span class='bold'> $term_log</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='row mg0'>
<div class='col-md-6 pdl0'>
<div class='row mg0'>
<div class='col-md-12 pd0'>
<div class='form-group' style='margin-bottom:4px;'>
<select id='mis_category' class='form-control input-sm' onchange=get_cat_issuance_info(this.value,'M');>
<option value=''>--- Select Category ---</option>
<option value='2'>Portfolio</option>
<option value='3'>Health</option>
<option value='4'>Term Plan</option>
</select>
</div>
<div class='form-group' id='mis_date' style='margin-bottom:4px;'>
<select class='form-control input-sm' onchange=get_issuance_info(this.value,'2'); >
<option value=''>--- Select Type ---</option>
<option value='M'>Month</option>
<option value='FY'>FY</option>
<option value='LTD'>LTD</option>
</select>
</div>
<div id='issuance_info_table'></div>
<div id='document_info_table'></div>
<div id='app_info_table'></div>
</div>
</div>
</div>
<div class='col-md-6 pdl0'>
<div class='row mg0'>
<div class='col-md-12 pd0 col-style'>
<div id='issuance_chart' style='height:284px;'> </div>
</div>
<div class='col-md-12 pd0'>
<table class='table table-bordered table-hover col-style'>
<thead>
<tr>
<th>RM Manager</th>
<th>RM Name</th>
<th>Life</th>
<th>SIP</th>
<th>Term Plan</th>
</tr>
</thead>
<tbody>
$rm_list
</tbody>
</table>
</div>
<div class='col-md-12 pd0 col-style'>
<div id='mis_life_chart' style='height:284px;'> </div>
</div>
<div class='col-md-12 pd0 col-style'>
<div id='mis_hl_chart' style='height:284px;'>
</div>
</div>
<div class='col-md-12 pd0 col-style'>
<div id='mis_rm_chart' style='height:284px;'>
</div>
</div>
</div>
</div>
</div>
</div>
</div>";
}else
if($emp_role === "8"){
//Executive
}else
if($emp_role === "9"){
//RM Manager
//Rm's
$rm_tm_info = $this->db->query("SELECT GROUP_CONCAT(id SEPARATOR ',') as ids FROM `ospos_employees` inner join ospos_people on ospos_people.person_id = ospos_employees.person_id where reporting = '$emp_id' and deleted = '0'");
$rm_tm_ids = $rm_tm_info->result_array();
$rmteamids = $rm_tm_ids[0]['ids'];
$rm_info = $this->db->query("SELECT id,first_name FROM `ospos_employees` inner join ospos_people on ospos_people.person_id = ospos_employees.person_id where reporting = '$emp_id' and deleted = '0'");
$rmteamid = $rm_info->result_array();
?>
<div class='col-md-12 pd0 dash_header'>
<h3 class='text-center headtxt'>Manager Dashboard</h3>
<div class='col-md-12'>
<div class='row mg0'>
<div class='col-md-6 pdl0'>
<div class='form-group' style='margin-bottom:4px;'>
<select class='form-control input-sm'); style="text-transform: capitalize;color: #044ef9; border-color: #0852fd;" onchange=get_rm_info(this.value,2,5,'$rmteamids');>
<option value=''>-- Select RM --</option>
<option value='ALL'>ALL</option>
<?php foreach ($rmteamid as $key => $value) {
$id = $value['id'];
$first_name = $value['first_name'];
?>
<option value=<?php echo $id; ?>><?php echo $first_name; ?></option>
<?php } ?>
</select>
</div>
</div>
</div>
</div>
<div id = "rm_data" class='col-md-12'>
</div>
</div>
<?php
}else
if($emp_role === "10"){
//RE Manager
}
?>
<div class="modal fade" id="detail_list_model" tabindex="-1" role="dialog" aria-labelledby="myModalLabel">
<div class="modal-dialog modal-lg" style="width: 90%;" role="document">
<div class="modal-content">
<div class="modal-header" style='padding: 5px 15px;'>
<button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button>
<h4 class="modal-title" id="myModalLabel">Detailed List</h4>
</div>
<div class="modal-body" id="lead_model_body" style="height:260px;">
</div>
</div>
</div>
</div>
<script type="text/javascript">
$(document).ready(function(){
$.fn.dataTableExt.sErrMode = 'throw';
var emp_id = '<?php echo $emp_id ?>';
var emp_role = '<?php echo $emp_role ?>';
var emp_cat = '<?php echo $emp_cat ?>';
var ids = '<?php echo $rm_team_ids ?>';
var rm_ids = '<?php echo $rmteamids ?>';
var manager = '<?php echo $manager ?>';
$('.tbl').DataTable({
"paging": false,
"dom": '<"top"fi>rt<"bottom"flp><"clear">'
});
if(emp_role === "1"){
get_renew_life_info("M");
get_renew_hl_info("M");
}else
if((emp_role === "3") && (emp_cat === "2")){
crm_chart();
}else
if((emp_role === "3") && (emp_cat === "3")){
crm_chart();
}else
if((emp_role === "6") && (emp_cat === "2")){
var map_crm = '<?php echo $map_crm ?>';
get_team_info("T",emp_role,emp_cat,map_crm,'');
crm_chart();
}else
if((emp_role === "6") && (emp_cat === "3")){
crm_chart();
}else
if(emp_role === "5"){
get_rm_val_chart("LTD");
//rm_ni_chart();
rmm_team_chart("M",manager);
get_renew_rm_info("M",emp_id);
}else
if(emp_role === "7"){
get_issuance_info("M");
issuance_chart("M");
get_renewal_info("M");
//get_productivity_info("M");
mis_life_chart();
mis_hl_chart();
mis_rm_chart();
}else
if(emp_role === "9"){
//rmm_ni_chart();
get_rm_info("ALL",2,5,rm_ids);
get_renew_rm_info("M",emp_id);
}
});
//Area Manager Function - START
function get_tl_info(emp_id,emp_cat,emp_role,tl){
if(emp_id === "ALL"){
emp_id = tl;
}
$.ajax({
type: "POST",
url: "index.php/Home/get_tl_info",
data: {emp_id:emp_id,emp_cat:emp_cat,emp_role:emp_role},
success: function(data) {
$('#tl_data').html(data);
$('.tbl').DataTable({
"paging": false,
"dom": '<"top"fi>rt<"bottom"flp><"clear">'
});
get_map_crm_info(emp_id,emp_role,emp_cat);
},
});
}
function get_map_crm_info(emp_id,emp_role,emp_cat){
if((emp_role === "6") && (emp_cat === "2")){
$.ajax({
type: "POST",
url: "index.php/Home/get_map_crm_info",
data: {emp_id:emp_id},
success: function(map_crm) {
get_team_info("M",emp_role,emp_cat,map_crm,'');
},
});
am_crm_chart(emp_id,emp_cat,emp_role);
/*
$('#team_info_table').DataTable({
"paging" : false,
"filter" : false,
"ordering": true,
});
*/
}else
if((emp_role === "6") && (emp_cat === "3")){
am_crm_chart(emp_id,emp_cat,emp_role);
}
}
//Area Manager Function - END
//RM Manager Function - START
function get_rm_info(emp_id,emp_cat,emp_role,rm_ids){
$.ajax({
type: "POST",
url: "index.php/Home/get_rm_info",
data: {emp_id:emp_id,emp_cat:emp_cat,emp_role:emp_role},
success: function(data) {
$('#rm_data').html(data);
manager_id = '<?php echo $this->session->userdata('emp_id') ?>';
get_rm_chart("LTD",emp_id,emp_role,emp_cat);
rmm_team_chart("M",manager_id);
if(emp_id === "ALL"){
rmm_team_chart("M",manager_id);
}
//get_map_crm_info(emp_id,emp_role,emp_cat);
},
});
}
//RM Manager Function - END
//RM Chart
$('#get_rm_val_chart').on("change", function(e){
e.preventDefault();
var type = $('#get_rm_val_chart').val();
get_rm_val_chart(type);
});
</script>
<?php $this->load->view('partial/footer'); ?>