File: /home/cafsindia/crm_cafsindia_com/application/controllers/Home_bk02aug2018.php
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
require_once("Secure_Controller.php");
class Home extends Secure_Controller {
public function __construct(){
parent::__construct();
$this->load->model("Homemodel");
}
public function index(){
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
$data="";
$this->load->view('home',$data);
}
//MIS Charts Start
public function mis_life_chart(){
$result = $this->Homemodel->mis_life_chart();
$rows['type'] = 'pie';
$rows['name'] = 'Company';
foreach ($result as $key => $value) {
$rows['data'][] = array($value['vendorcompanyname'], $value['tot_count']);
}
$rslt = array();
array_push($rslt,$rows);
print json_encode($rslt, JSON_NUMERIC_CHECK);
}
public function mis_hl_chart(){
$result = $this->Homemodel->mis_hl_chart();
$rows['type'] = 'pie';
$rows['name'] = 'Company';
foreach ($result as $key => $value) {
$rows['data'][] = array($value['vendorcompanyname'], $value['tot_count']);
}
$rslt = array();
array_push($rslt,$rows);
print json_encode($rslt, JSON_NUMERIC_CHECK);
}
public function mis_rm_chart(){
$result = $this->Homemodel->mis_rm_chart();
print json_encode($result, JSON_NUMERIC_CHECK);
}
public function rmm_team_chart(){
$date = $_REQUEST['date'];
$emp_id = $_REQUEST['emp_id'];
$result = $this->Homemodel->rmm_team_chart($date,$emp_id);
print json_encode($result, JSON_NUMERIC_CHECK);
}
//MIS Charts End
//RM & RM Manager Charts Start
public function rm_ni_chart(){
$result = $this->Homemodel->rm_ni_chart();
$rows['type'] = 'pie';
$rows['name'] = 'Company';
foreach ($result as $key => $value) {
$rows['data'][] = array($value['renew_reason'], $value['notpaid']);
}
$rslt = array();
array_push($rslt,$rows);
print json_encode($rslt, JSON_NUMERIC_CHECK);
}
public function rmm_ni_chart(){
$result = $this->Homemodel->rmm_ni_chart();
$rows['type'] = 'pie';
$rows['name'] = 'Company';
foreach ($result as $key => $value) {
$rows['data'][] = array($value['renew_reason'], $value['notpaid']);
}
$rslt = array();
array_push($rslt,$rows);
print json_encode($rslt, JSON_NUMERIC_CHECK);
}
public function get_rm_val_chart(){
$type = $_REQUEST['type'];
$result = $this->Homemodel->get_rm_val_chart($type);
$drill_list = array();
foreach ($result as $key => $value) {
$lead_status = $value['lead_status'];
$statusname = $value['statusname'];
$lead_count = $value['lead_count'];
$rows['data'][] = array("name"=>$statusname,"y"=>$lead_count,"drilldown"=>$statusname);
$drill_result = $this->Homemodel->rm_chart_drill($type,$lead_status);
$drill = array();
$drill['name'] = $statusname;
$drill['id'] = $statusname;
foreach ($drill_result as $key => $value) {
$prospect_name = $value['prospect_name'];
$lead_count = $value['lead_count'];
$drill['data'][] = array($prospect_name,$lead_count);
array_push($drill_list,$drill);
}
}
$rslt = array();
array_push($rslt,$rows);
echo json_encode(array('series' => $rslt, 'drill_list' => $drill_list),JSON_NUMERIC_CHECK);
}
public function get_rm_chart(){
$id = $this->session->userdata('emp_id');
$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 = '$id' and deleted = '0'");
$rm_info = $rm_team_qry->result_array();
$rm_ids = $rm_info[0]['rm_ids'];
$type = $_REQUEST['type'];
$emp_id = $_REQUEST['emp_id'];
$emp_role = $_REQUEST['emp_role'];
$emp_cat = $_REQUEST['emp_cat'];
if($emp_id === "ALL"){
$emp_id = $rm_ids;
}
$result = $this->Homemodel->get_rm_chart($type,$emp_id,$emp_role,$emp_cat);
$drill_list = array();
foreach ($result as $key => $value) {
$lead_status = $value['lead_status'];
$statusname = $value['statusname'];
$lead_count = $value['lead_count'];
$rows['data'][] = array("name"=>$statusname,"y"=>$lead_count,"drilldown"=>$statusname);
$drill_result = $this->Homemodel->rmm_chart_drill($type,$lead_status,$emp_id,$emp_role,$emp_cat);
$drill = array();
$drill['name'] = $statusname;
$drill['id'] = $statusname;
foreach ($drill_result as $key => $value) {
$prospect_name = $value['prospect_name'];
$lead_count = $value['lead_count'];
$drill['data'][] = array($prospect_name,$lead_count);
array_push($drill_list,$drill);
}
}
$rslt = array();
array_push($rslt,$rows);
echo json_encode(array('series' => $rslt, 'drill_list' => $drill_list),JSON_NUMERIC_CHECK);
}
//RM & RM Manager Charts END
//CRM & AM Chart Start
public function crm_chart(){
$result = $this->Homemodel->crm_chart();
$drill_list = array();
//$rows['colorByPoint'] = false;
foreach ($result as $key => $value) {
$lead_status = $value['lead_status'];
$statusname = $value['statusname'];
$lead_count = $value['lead_count'];
$rows['data'][] = array("name"=>$statusname,"y"=>$lead_count,"drilldown"=>$statusname);
$drill_result = $this->Homemodel->crm_chart_drill($lead_status);
$drill = array();
$drill['name'] = $statusname;
$drill['id'] = $statusname;
foreach ($drill_result as $key => $value) {
$prospect_name = $value['prospect_name'];
$lead_count = $value['lead_count'];
$drill['data'][] = array($prospect_name,$lead_count);
array_push($drill_list,$drill);
}
}
$rslt = array();
array_push($rslt,$rows);
echo json_encode(array('series' => $rslt, 'drill_list' => $drill_list),JSON_NUMERIC_CHECK);
}
public function issuance_chart($date){
$result = $this->Homemodel->issuance_chart($date);
$drill_list = array();
foreach ($result as $key => $value) {
$issuance_status = $value['issuance_status'];
$premium = $value['premium'];
if($issuance_status === "Pending For Issuance"){
$issuance_status = "Pending";
}else
if($issuance_status === "Medical Pending"){
$issuance_status = "Medical";
}else
if($issuance_status === "Medical Declined"){
$issuance_status = "Declined";
}else
if($issuance_status === "Cheque Dishonor"){
$issuance_status = "Declined";
}
$rows['data'][] = array("name"=>$issuance_status,"y"=>$premium,"drilldown"=>$issuance_status);
$drill_result = $this->Homemodel->issuance_chart_drill($issuance_status,$date);
$drill = array();
$drill['name'] = $issuance_status;
$drill['id'] = $issuance_status;
foreach ($drill_result as $key => $value) {
$document_issue_status = $value['document_issue_status'];
$doc_premium = $value['doc_premium'];
$drill['data'][] = array($document_issue_status,$doc_premium);
array_push($drill_list,$drill);
}
}
$rslt = array();
array_push($rslt,$rows);
echo json_encode(array('series' => $rslt, 'drill_list' => $drill_list),JSON_NUMERIC_CHECK);
}
public function am_crm_chart($emp_id,$emp_cat,$emp_role){
$emp_id = $_REQUEST['emp_id'];
$emp_cat = $_REQUEST['emp_cat'];
$emp_role = $_REQUEST['emp_role'];
$result = $this->Homemodel->am_crm_chart($emp_id,$emp_cat,$emp_role);
$drill_list = array();
foreach ($result as $key => $value) {
//$emp_id = $emp_id;
//$emp_cat = $emp_cat;
//$emp_role = $emp_role;
$lead_status = $value['lead_status'];
$statusname = $value['statusname'];
$lead_count = $value['lead_count'];
$rows['data'][] = array("name"=>$statusname,"y"=>$lead_count,"drilldown"=>$statusname);
$drill_result = $this->Homemodel->am_crm_chart_drill($lead_status,$emp_id,$emp_cat,$emp_role);
$drill = array();
$drill['name'] = $statusname;
$drill['id'] = $statusname;
foreach ($drill_result as $key => $value) {
$prospect_name = $value['prospect_name'];
$lead_count = $value['lead_count'];
$drill['data'][] = array($prospect_name,$lead_count);
array_push($drill_list,$drill);
}
}
$rslt = array();
array_push($rslt,$rows);
echo json_encode(array('series' => $rslt, 'drill_list' => $drill_list),JSON_NUMERIC_CHECK);
}
//CRM & AM Chart End
public function logout(){
$this->Employee->logout();
}
public function get_map_crm_info(){
$emp_id = $this->input->post('emp_id');
$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'];
echo $map_crm;
}
//RM Manager Dashboard
public function get_rm_info(){
$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 = $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);
$emp_id = $this->input->post('emp_id');
$emp_role = $this->input->post('emp_role');
$emp_cat = $this->input->post('emp_cat');
$id = $this->session->userdata('emp_id');
$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 = '$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;
if($emp_id === "ALL"){
$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>";
//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 = '$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;
$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>";
}
$rmteamData = "<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>";
}
//RM
$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'];
//Box 3
$sip = $this->db->query("SELECT IFNULL(sum(mf_amount),0) as sip from ospos_lead_type_info INNER JOIN ospos_leads_product on lead_type_id = lead_info_id where mf_investment = '1' 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'");
$sip_result = $sip->result_array();
$sip_login = $sip_result[0]['sip'];
//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);
//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_status = $this->db->query("SELECT count(*) as count,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 inner 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_met_log.met_date like '$cur_month%' and ospos_lead_type_info.created_date like '$cur_month%'");
$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%' 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 count(*) as count,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 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 ospos_lead_type_info.status = '1' and ospos_lead_type_info.created_date not like '$cur_month%' and ospos_met_log.first_met='1' and ospos_met_log.met_date like '$cur_month%' GROUP BY ospos_lead_type_info.lead_id");
$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;
}
//else
// if($status_name === "Met"){
// $self_met = $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'];
//echo "SELECT statusname,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_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_leads_product.status = '1' and ospos_lead_type_info.ncd like '$cur_month%' GROUP by lead_status";
$ncd_self_status = $this->db->query("SELECT statusname,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_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_leads_product.status = '1' 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'];
$value = $value['value'];
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'>
<h3 class='text-center headtxt'>RM Dashboard</h3>
<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='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'>$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'>
<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'>Under Processing...</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 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>
<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','RMLEADNCD');>$ncd_lead_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'>
$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>
<th>Not Interested</th>
<th>Closed</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>";
}
//AM Dashboard
public function get_tl_info(){
/*
$am_emp_cat = $this->session->userdata('emp_category');
$am_emp_id = $this->session->userdata('emp_id');
$am_emp_role = $this->session->userdata('emp_role');
*/
$emp_role = $this->input->post('emp_role');
$emp_cat = $this->input->post('emp_cat');
$emp_id = $this->input->post('emp_id');
$emp_grade = $this->input->post('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 === "6"){
//echo "SELECT GROUP_CONCAT(teammembers SEPARATOR ',') as teammembers FROM `ospos_team` where teamleader in ('$emp_id') and deleted = '0'";
$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 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 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%' and ospos_met_log.first_met='1'");
$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 IFNULL(sum(met),0) as met,conversion_ratio FROM `ospos_goal_sheet` where emp_id in ($emp_id) AND role = '$emp_role' AND category = '$emp_cat' AND (YEAR(from_date) = '$cur_year' or YEAR(to_date) = '$cur_year') AND deleted = '0'");
$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);
//SELECT count(DISTINCT(lead_id)) 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.created_by in ($emp_id) and ncd like '$cur_year%' and ncd not like '$cur_month%'
//echo "SELECT count(DISTINCT(lead_id)) 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.created_by in ($team_ids) and ospos_met_log.met_date BETWEEN '$fin_start_month' and '$fin_end_month' and ospos_met_log.met_date not like '$cur_month%' and ospos_met_log.first_met='1'";
//Pending
$met_qry=$this->db->query("SELECT count(DISTINCT(lead_id)) 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.created_by in ($team_ids) and ospos_met_log.met_date BETWEEN '$fin_start_month' and '$fin_end_month' and ospos_met_log.met_date not like '$cur_month%' and ospos_met_log.first_met='1'");
$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 in($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;
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>Total Met</h5> <span class='bold'> $team_met</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>Achieved</th>
<th>Pending</th>
<th>Total Required</th>
</tr>
</thead>
<tbody>
<tr>
<td >Met</td>
<td>$actual_month</td>
<td>$met_count</td>
<td>$fnl_met_count</td>
<td>$req_met</td>
</tr>
<tr>
<td >Conversion Ratio</td>
<td>$conversion_ratio</td>
<td>-</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' style='overflow: auto; height: 237px;'>
<div class='form-group' style='margin-bottom:4px;float:right;'>
<select class='form-control input-sm' onchange=get_team_info(this.value,$emp_role,$emp_cat,'$map_crm',''); >
<option value='T'>Today</option>
<option value='Y'>Yeasterday</option>
<option value='TW'>Tomorrow</option>
<option value='M' selected>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);
//echo "SAT $team_value :: $avg_map_value";
//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 Name
$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' and deleted = '0'");
$crm_info_rslt = $crm_info->row();
$crm_name = $crm_info_rslt->first_name;
//TL Name
$report_info = $this->db->query("SELECT reporting from ospos_employees left join ospos_people on ospos_employees.person_id = ospos_people.person_id where ospos_employees.id = '$id'");
$tl_id = $report_info->row();
$tl = $tl_id->reporting;
$tl_info = $this->db->query("SELECT first_name from ospos_employees left join ospos_people on ospos_employees.person_id = ospos_people.person_id where ospos_employees.id = '$tl'");
$tl_name = $tl_info->row();
$manager_name = $tl_name->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>$manager_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 tbl'>
<thead>
<tr>
<th>CRM Name</th>
<th>Manager</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>";
}
}
}
}
?>