File: /home/cafsindia/crm_cafsindia_com/application/views/rmdashboard.php
<?php
if($emp_onchange === "1")
{
$emp_role = $emp_role;
$emp_cat = $emp_cat;
if($emp_role==="5")
$emp_id = $emp_id;
if($emp_role==="9")
$emp_check = $emp_check;
$emp_id = $emp_id;
$today = new DateTime();
$lastDay = new DateTime('last day of this month');
$DaysRemaining = $lastDay->diff($today)->format('%a days');
$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');
$first_day = date("Y-m-01");
$last_day = date("t");
$today = date("Y-m-d");
$yesterday = date("Y-m-d",strtotime("-1 days"));
//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_count = $mth - 3;
}else{
$fin_start_month = $year1."-"."04"."-"."01";
$fin_end_month = $year."-"."03"."-"."31";
$m = date("m");
$lst_mth_count = $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_check === "ALL"){
//RM Team Data
$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' and ospos_employees.reporting in ($emp_id) GROUP BY 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;
$met_info = $this->db->query("SELECT count(*) as count FROM ospos_lead_type_info INNER join ospos_met_log ON ospos_met_log.log_lead_id = ospos_lead_type_info.lead_id where ospos_met_log.met_date like '$cur_month%' and (ospos_lead_type_info.created_by = '$id' or ospos_lead_type_info.rm_name = '$id') and first_met='1' and ospos_lead_type_info.status = '1'");
$met_rslt = $met_info->result_array();
$rmmet = $met_rslt[0]['count'];
$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_lead_info($emp_role,$emp_cat,$id,'9','M','RMMLEAD');>$rmmet</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,5,'M','');>$termamount</td>
</tr>";
}
$rmteamData = "<div class='col-md-12 pd0'>
<table class='table table-bordered table-hover col-style tbl'>
<thead>
<tr>
<th>RM Manager</th>
<th>RM Name</th>
<th>Met</th>
<th>Life</th>
<th>SIP</th>
<th>Term Plan</th>
</tr>
</thead>
<tbody>
$rm_list
</tbody>
</table>
</div>";
//RM
$rm_team_qry = $this->db->query("SELECT GROUP_CONCAT(id SEPARATOR ',') as rm_ids FROM `ospos_employees` inner join ospos_people on ospos_people.person_id = ospos_employees.person_id where category = '2' and reporting in($emp_id) and deleted = '0'");
$rm_info = $rm_team_qry->result_array();
$rm_ids = $rm_info[0]['rm_ids'];
$team_count = substr_count($rm_ids, ",") +1;
// All RMs are assigned
$emp_id = $rm_ids;
$tot_met = $this->db->query("SELECT count(*) 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 in ($emp_id) or rm_name in ($emp_id)) and first_met='1' and ospos_lead_type_info.status = '1' AND ospos_met_log.met_date like '$cur_month%'");
$tot_met_result = $tot_met->result_array();
$tot_met = $tot_met_result[0]['count'];
$avg_tot_met = round($tot_met / $team_count);
//Box 2
$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 (ospos_lead_type_info.created_by in ($emp_id) or rm_name in ($emp_id)) and login_date like '$cur_month%' and ospos_leads_product.status = '1'");
$login_result = $login->result_array();
$tot_login = $login_result[0]['value'];
$avg_tot_login = round($tot_login / $team_count);
$rmm_data = "<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Team Avg Login & Met'>
<div class='dashbox dorange'>
<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_tot_login</span><div style='position: absolute; top: 5px; right: 25px; font-weight: bold;font-size: 11px;'>$avg_tot_met</div></td>
</tr>
</table>
</div>
</div>";
}
$manager_info = $this->db->query("SELECT reporting FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id where ospos_employees.id = '$emp_id' and deleted = '0' ");
$manager_id = $manager_info->result_array();
$manager = $manager_id[0]['reporting'];
//Box 1
$company_met = $this->db->query("SELECT count(*) 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 first_met='1' and rm_name in ($emp_id) and ospos_lead_type_info.status = '1' and ospos_met_log.met_date like '$cur_month%' and ospos_lead_type_info.created_by not in ($emp_id)");
$company_met_result = $company_met->result_array();
$tot_company_met = $company_met_result[0]['count'];
$tot_met = $this->db->query("SELECT count(*) 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 in ($emp_id) or rm_name in ($emp_id)) and first_met='1' and ospos_lead_type_info.status = '1' AND ospos_met_log.met_date like '$cur_month%'");
$tot_met_result = $tot_met->result_array();
$tot_met = $tot_met_result[0]['count'];
//Box 2
$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 lead_status = '9' and prospect_level = '4' and (ospos_lead_type_info.created_by in ($emp_id) or rm_name in ($emp_id)) and login_date like '$cur_month%' and ospos_lead_type_info.status = '1' and ospos_leads_product.status = '1'");
$login_result = $login->result_array();
$tot_login = $login_result[0]['value'];
$unique_client_qry = $this->db->query("SELECT count(cust_id) as count,COUNT(ospos_lead_type_info.lead_id)as lead_id from ospos_lead_type_info inner join ospos_leads on ospos_leads.lead_id = ospos_lead_type_info.lead_id 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 ($emp_id) or rm_name in ($emp_id)) and login_date like '$cur_month%' and ospos_leads_product.status = '1'");
$unique_client_result = $unique_client_qry->result_array();
$tot_unique_client = $unique_client_result[0]['count'];
$rm_target_qry = $this->db->query("SELECT IFNULL(sum(premium),0) as premium FROM `ospos_target` where emp_id in ($emp_id) and category = '$emp_cat' and from_date like '$cur_month%' and to_date like '$cur_month%' ");
$rm_target_result = $rm_target_qry->result_array();
$rm_target = $rm_target_result[0]['premium'];
if(!$rm_target){
$rm_target = 0;
}
//Box 3
$sip_target_qry = $this->db->query("SELECT IFNULL(sum(sip),0) as sip_target FROM `ospos_target` where emp_id in ($emp_id) and from_date like '$cur_month%' and to_date like '$cur_month%' ");
$sip_target_result = $sip_target_qry->result_array();
$sip_target = $sip_target_result[0]['sip_target'];
if(!$sip_target){
$sip_target = 0;
}
$sip = $this->db->query("SELECT IFNULL(sum(ospos_mf_investment.amount),0) as amount from ospos_mutual_fund inner join ospos_mf_investment on ospos_mf_investment.inves_mf_id = ospos_mutual_fund.mf_id INNER JOIN ospos_lead_type_info ON ospos_mf_investment.lead_id = ospos_lead_type_info.lead_id where entry_date like '$cur_month%' and investment_type = 'SIP' and fund_class = 'Equity' and lead_status = '8' AND (ospos_lead_type_info.created_by in ($emp_id) or ospos_lead_type_info.rm_name in ($emp_id)) and ospos_lead_type_info.status = '1'");
$sip_result = $sip->result_array();
$sip_login = $sip_result[0]['amount'];
//Box 4
/*
$rm_met = $this->db->query("SELECT count(*) as count 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 lead_status = '9' and (ospos_lead_type_info.created_by in ($emp_id) or rm_name in ($emp_id)) and met_date BETWEEN '$thirty_days' and '$today' and ospos_leads_product.status = '1'");
$rm_met_result = $rm_met->result_array();
$rm_met_count = $rm_met_result[0]['count'];
$cust_id = $rm_met_result[0]['cust_id'];
$rm_logged = $this->db->query("SELECT count(*) as cust_id 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 (ospos_lead_type_info.created_by in ($emp_id) or rm_name in ($emp_id)) and login_date BETWEEN '$thirty_days' and '$today' and ospos_leads_product.status = '1'");
$rm_logged_result = $rm_logged->result_array();
$rm_logged_count = $rm_logged_result[0]['count'];
$cust_id = $rm_logged_result[0]['cust_id'];
$rm_cr = round((( $rm_met_count / $cust_id ) * 100));
//Box 5
$rm_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%'");
$rm_target_result = $rm_target->result_array();
$rm_target_value = $rm_target_result[0]['value'];
$rm_rrr = round(($rm_target_value - $tot_login) / ($last_day - $date),1);
*/
//Box 6
$rm_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 (ospos_lead_type_info.created_by in ($emp_id) or rm_name in ($emp_id)) and login_date like '$last_month%' and ospos_leads_product.status = '1'");
$rm_login_result = $rm_login_qry->result_array();
$rm_login = $rm_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 (ospos_lead_type_info.created_by in ($emp_id) or rm_name in ($emp_id)) 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($last_login / $lst_mth_count);
//Box 7
$zeroqry = $this->db->query("SELECT count(*) 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 in ($emp_id) or rm_name in ($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;
$rm_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(DISTINCT(ospos_met_log.met_date)) 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 in ($emp_id) or rm_name in ($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'];
$day = (int)$zero_in_month_result[0]['count'];
$days = explode("-", $yesterday);
$days = (int)$days[2];
$rm_month = $days - $day;
/* Met Status */
$met_status = $this->db->query("SELECT prospect_name,count(*) as count,IFNULL(sum(expected_value),0) as amount 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 prospect_id = prospect_level where lead_status = '9' and (ospos_lead_type_info.created_by in ($emp_id) or rm_name in ($emp_id)) AND first_met='1' and ospos_lead_type_info.status = '1' and ospos_met_log.met_date like '$cur_month%' GROUP by prospect_name");
$met_lead_rslt = $met_status->result_array();
$met_hot = "0 - 0";
$met_warm = "0 - 0";
$met_drop = "0 - 0";
foreach ($met_lead_rslt as $key => $value) {
$prospect_name = $value['prospect_name'];
$count = $value['count'];
$amount = $value['amount'];
$premium = $value['premium'];
//echo "$prospect_name :: $count :: $amount :: $premium";
if($prospect_name === "Hot"){
$met_hot = "$count - $amount";
}else
if($prospect_name === "Warm"){
$met_warm = "$count - $amount";
}else
if($prospect_name === "Dropped"){
$met_drop = "$count - $amount";
}
}
$met_log_status = $this->db->query("SELECT sum(overall_net_premium) as amount FROM ospos_met_log INNER JOIN ospos_lead_type_info ON ospos_met_log.log_lead_id = ospos_lead_type_info.lead_id 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 (ospos_lead_type_info.created_by in ($emp_id) or rm_name in ($emp_id)) and ospos_lead_type_info.status = '1' and ospos_leads_product.status = '1' AND first_met='1' and ospos_leads_product.login_date like '%$cur_month%' and ospos_met_log.met_date like '$cur_month%' group by lead_info_id ");
$met_log_rslt = $met_log_status->result_array();
$met_log_rslt_rows = $met_log_status->num_rows();
$metlog = "0 - 0";
$amount=0;
foreach ($met_log_rslt as $key => $value){
$count = $met_log_rslt_rows;
$amount = $amount+$value['amount'];
$metlog = "$count - $amount";
}
$ncd_met_status = $this->db->query("SELECT prospect_name,count(*) as count,IFNULL(sum(expected_value),0) as amount 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 prospect_id = prospect_level where lead_status = '9' and (ospos_lead_type_info.created_by in ($emp_id) or rm_name in ($emp_id)) and ospos_lead_type_info.status = '1' and ospos_met_log.first_met='1' and ospos_met_log.met_date not like '$cur_month%' and ncd like '$cur_month%' GROUP by prospect_level");
$ncd_met_lead_rslt = $ncd_met_status->result_array();
$ncd_met_hot = "0 - 0";
$ncd_met_warm = "0 - 0";
$ncd_met_log = "0 - 0";
$ncd_met_drop = "0 - 0";
foreach ($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"){
$ncd_met_hot = "$count - $amount";
}else
if($prospect_name === "Warm"){
$ncd_met_warm = "$count - $amount";
}else
if($prospect_name === "Dropped"){
$ncd_met_drop = "$count - $amount";
}
}
$ncd_met_log_status = $this->db->query("SELECT IFNULL(sum(overall_net_premium),0) as amount FROM ospos_met_log INNER JOIN ospos_lead_type_info ON ospos_met_log.log_lead_id = ospos_lead_type_info.lead_id 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 (ospos_lead_type_info.created_by in ($emp_id) or rm_name in ($emp_id)) and ospos_lead_type_info.status = '1' and ospos_met_log.first_met='1' and ospos_met_log.met_date not like '$cur_month%' and ospos_leads_product.login_date like '%$cur_month%' group by lead_info_id ");
$ncd_met_log_rslt = $ncd_met_log_status->result_array();
$ncd_met_log_rslt_rows = $ncd_met_log_status->num_rows();
$ncd_met_log = "0 - 0";
$amount=0;
foreach ($ncd_met_log_rslt as $key => $value) {
$count = $ncd_met_log_rslt_rows;
$amount = $amount+$value['amount'];
$ncd_met_log = "$count - $amount";
}
/* Self Lead */
$self_met_status = $this->db->query("SELECT statusname,count(*) as count FROM `ospos_lead_type_info` inner join ospos_lead_status on status_id = lead_status where ospos_lead_type_info.created_by in ($emp_id) and ospos_lead_type_info.created_date like '$cur_month%' GROUP by statusname");
$self_met_lead_rslt = $self_met_status->result_array();
$self_app = "0";
$self_flw = "0";
$self_drop = "0";
$self_met = "0";
foreach ($self_met_lead_rslt as $key => $value) {
$status_name = $value['statusname'];
$count = $value['count'];
if($status_name === "Appointment"){
$self_app = $count;
}else
if($status_name === "Follow Up"){
$self_flw = $count;
}else
if($status_name === "Dropped"){
$self_drop = $count;
}
}
$last_self_met_status = $this->db->query("SELECT statusname,count(*) as count FROM `ospos_lead_type_info` inner join ospos_lead_status on status_id = lead_status where ospos_lead_type_info.status='1' and ospos_lead_type_info.lead_mode='1' and ospos_lead_type_info.created_by in ($emp_id) and ospos_lead_type_info.created_date like '$last_month%' GROUP by statusname");
$last_self_met_lead_rslt = $last_self_met_status->result_array();
$last_self_app = "0";
$last_self_flw = "0";
$last_self_drop = "0";
$last_self_met = "0";
foreach ($last_self_met_lead_rslt as $key => $value) {
$status_name = $value['statusname'];
$count = $value['count'];
if($status_name === "Appointment"){
$last_self_app = $count;
}else
if($status_name === "Follow Up"){
$last_self_flw = $count;
}else
if($status_name === "Dropped"){
$last_self_drop = $count;
}
}
// Fist met lead count this month 30 july 2018 @GS
$self_met_status = $this->db->query("SELECT count(*) as count FROM `ospos_lead_type_info` inner join ospos_lead_status on status_id = lead_status inner join ospos_met_log on ospos_met_log.log_lead_id = ospos_lead_type_info.lead_id where lead_status = '9' AND first_met='1' and ospos_lead_type_info.created_by in ($emp_id) and ospos_met_log.met_date like '$cur_month%'");
$self_met_lead_count = $self_met_status->result_array();
$self_met = $self_met_lead_count[0]['count'];
$last_self_met_status = $this->db->query("SELECT count(*) as count FROM `ospos_lead_type_info` inner join ospos_lead_status on status_id = lead_status inner join ospos_met_log on ospos_met_log.log_lead_id = ospos_lead_type_info.lead_id where ospos_lead_type_info.status='1' and ospos_lead_type_info.lead_mode='1' and lead_status = '9' AND first_met='1' and ospos_lead_type_info.created_by in ($emp_id) and ospos_met_log.met_date like '$last_month%'");
$last_self_met_lead_count = $last_self_met_status->result_array();
$last_self_met = $last_self_met_lead_count[0]['count'];
$ncd_self_status = $this->db->query("SELECT statusname,count(*) as count,IFNULL(sum(expected_login),0) as amount FROM `ospos_lead_type_info` inner join ospos_lead_status on status_id = lead_status where ospos_lead_type_info.created_by in ($emp_id) and ospos_lead_type_info.created_date not like '$cur_month%' and ospos_lead_type_info.ncd like '$cur_month%' GROUP by lead_status");
$ncd_lead_rslt = $ncd_self_status->result_array();
$ncd_lead_app = "0";
$ncd_lead_flw = "0";
$ncd_lead_drp = "0";
$ncd_lead_met = "0";
foreach ($ncd_lead_rslt as $key => $value) {
$status_name = $value['statusname'];
$count = $value['count'];
$amount = $value['amount'];
if($status_name === "Appointment"){
$ncd_lead_app = $count;
}else
if($status_name === "Follow Up"){
$ncd_lead_flw = $count;
}else
if($status_name === "Dropped"){
$ncd_lead_drp = $count;
}else
if($status_name === "Met"){
$ncd_lead_met = $count;
}
}
/* 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 lead_status = '9' and prospect_level = '4' and ospos_lead_type_info.created_by in ($emp_id) and ospos_lead_type_info.status = '1' and ospos_leads_product.status = '1' and 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` inner join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id inner 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 lead_status = '9' and prospect_level = '4' and ospos_lead_type_info.rm_name in ($emp_id) and ospos_lead_type_info.created_by not in ($emp_id) and ospos_lead_type_info.status = '1' and ospos_leads_product.status = '1' and login_date like '$cur_month%'");
$month_crm_rslt = $month_crm_status->result_array();
$lead_type = $month_crm_rslt[0]['lead_type'];
$crm_count = $month_crm_rslt[0]['value'];
/*Contribution */
$self_con = round(($self / $tot_login)*100);
$upsell_con = round(($upsell / $tot_login)*100);
$ref_con = round(($ref / $tot_login)*100);
$crm_con = round(($crm_count / $tot_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 ospos_lead_type_info.created_by in ($emp_id) 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;
}
}
/* 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 ospos_lead_type_info.created_by in ($emp_id) 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;
}
}
//Total login value - LTD
$rm_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 ($emp_id) or rm_name in ($emp_id)) and ospos_lead_type_info.status = '1' and ospos_leads_product.status = '1'");
$rm_login_value_result = $rm_login_value_qry->row();
$rm_tot_log_val = $rm_login_value_result->net_premium;
//Total login value - Month
$rm_login_month_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 login_date like '$cur_month%' and (ospos_lead_type_info.created_by in ($emp_id) or rm_name in ($emp_id)) and ospos_lead_type_info.status = '1' and ospos_leads_product.status = '1'");
$rm_login_month_result = $rm_login_month_qry->row();
$rm_tot_month_val = $rm_login_month_result->net_premium;
echo "<div class='col-md-12 pd0 dash_header'>
<div class='row mg0 mgb15'>$rmm_data
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Total Met & Company 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>Total Met</h5> <span class='bold'>$tot_met</span> <div style='position: absolute; top: 5px; right: 25px; font-weight: bold;font-size: 11px;'>$tot_company_met</div></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Target - Login Value & Nop unique client'>
<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'>$rm_target - $tot_login</span><div style='position: absolute; top: 5px; right: 25px; font-weight: bold;font-size: 11px;'>$tot_unique_client</div></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='SIP Target - SIP'>
<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>SIP</h5> <span class='bold'>$sip_target - $sip_login</span></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Last Month Login & YTD Avg Logged'>
<div class='dashbox brown'>
<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'> $rm_login </span><div style='position: absolute; top: 5px; right: 25px; font-weight: bold;font-size: 11px;'>$ytd_avg</div></td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Zero Days in a month & Zero Days From last Met Date'>
<div class='dashbox redish'>
<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'> $rm_month</span><div style='position: absolute; top: 5px; right: 25px; font-weight: bold;font-size: 11px;'>$rm_Zero_days</div></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>
<tr>
<th>Self Lead</th>
<th>Appointment</th>
<th>Follow up</th>
<th>Dropped</th>
<th>Met</th>
</tr>
</thead>
<tbody>
<tr>
<td >FTM</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$emp_id','1','M','RMLEAD');>$self_app</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$emp_id','2','M','RMLEAD');>$self_flw</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$emp_id','3','M','RMLEAD');>$self_drop</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$emp_id','9','M','METRMLEAD');>$self_met</td>
</tr>
<tr>
<td >TLM</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$emp_id','1','M','RMLEADNCD');>$ncd_lead_app</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$emp_id','2','M','RMLEADNCD');>$ncd_lead_flw</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$emp_id','3','M','RMLEADNCD');>$ncd_lead_drp</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$emp_id','9','M','METRMLEADNCD');>$ncd_lead_met</td>
</tr>
<tr>
<td >Last Month</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$emp_id','1','LM','RMLEAD');>$last_self_app</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$emp_id','2','LM','RMLEAD');>$last_self_flw</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$emp_id','3','LM','RMLEAD');>$last_self_drop</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$emp_id','9','LM','METRMLEAD');>$last_self_met</td>
</tr>
</tbody>
</table>
<table class='table table-bordered table-hover col-style'>
<thead>
<tr>
<th>Met Status</th>
<th>Hot</th>
<th>Warm</th>
<th>Dropped</th>
<th>Login</th>
</tr>
</thead>
<tbody>
<tr>
<td >FTM</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$emp_id','1','M','RMMETTM');>$met_hot</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$emp_id','2','M','RMMETTM');>$met_warm</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$emp_id','3','M','RMMETTM');>$met_drop</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$emp_id','4','M','RMMETTMLOG');>$metlog</td>
</tr>
<tr>
<td >TLM</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$emp_id','1','M','RMMETNCD');>$ncd_met_hot</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$emp_id','2','M','RMMETNCD');>$ncd_met_warm</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$emp_id','3','M','RMMETNCD');>$ncd_met_drop</td>
<td class='cursor' onclick=get_lead_info($emp_role,$emp_cat,'$emp_id','4','M','RMMETNCDLOG');>$ncd_met_log</td>
</tr>
</tbody>
</table>
<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>Company</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','');>$self</td>
<td class='cursor' onclick=get_lead_info_type($emp_role,$emp_cat,'$emp_id','10','M','');>$ref</td>
<td class='cursor' onclick=get_lead_info_type($emp_role,$emp_cat,'$emp_id','9','M','');>$upsell</td>
<td class='cursor' onclick=get_lead_info_type($emp_role,$emp_cat,'$emp_id','8','M','rm_name');>$crm_count</td>
</tr>
<tr>
<td ></td>
<td>$self_con%</td>
<td>$ref_con%</td>
<td>$upsell_con%</td>
<td>$crm_con%</td>
</tr>
</tbody>
</table>
<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_rm_ocl($emp_role,$emp_cat,'$emp_id','4','M','Lumpsum');>$lumpsum</td>
<td class='cursor' onclick=get_rm_ocl($emp_role,$emp_cat,'$emp_id','4','M','SIP');>$sip</td>
<td class='cursor' onclick=get_rm_ocl($emp_role,$emp_cat,'$emp_id','5','M','');>$term</td>
<td class='cursor' onclick=get_rm_ocl($emp_role,$emp_cat,'$emp_id','3','M','SIP');>$health</td>
</tr>
</tbody>
</table>
</div>
<div class='col-md-6 pdl0'>
<div class='col-md-12 col-style'>
<div id='ribbon'>LTD $rm_tot_log_val</div>
<div id='rm_val_chart' style='height:315px;'></div>
</div>
</div>
</div>
</div>
<div class='col-md-12'>
<div class='row mg0'>
<div class='col-md-6 pdl0' style='height: 300px; overflow: auto;'>
$rmteamData
<div class='form-group' style='margin-bottom:4px;'>
<select class='form-control input-sm' onchange=get_renew_rm_info(this.value,'$emp_id'); >
<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_rm_info_table'>
<thead>
<tr>
<th>Renewal Life</th>
<th>Collectable</th>
<th>Paid</th>
<th>Not Paid</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
<div class='col-md-6 col-style'>
<div id='ribbon'>Month $rm_tot_month_val</div>
<div id='rmm_team_chart' style='height:284px;'> </div>
</div>
</div>
</div>
</div>";
?>