File: /home/cafsindia/crm_cafsindia_com/application/views/gmdashboard.php
<?php
/*
@ Author: GS
@ Start Date : 13 Aug 2018
@ Admin Inputs : Suresh Kumar
*/
// Collect IDs for Role
$crm_port = $this->db->query("SELECT count(id) as count,GROUP_CONCAT(id SEPARATOR ',') as ids FROM `ospos_employees` where category = '2' and role = '3' and deleted = '0'");
$crm_port_info = $crm_port->result_array();
$crm_port_ids = $crm_port_info[0]['ids'];
$crm_port_count = $crm_port_info[0]['count'];
//Health CRM Ids
$crm_hl = $this->db->query("SELECT count(id) as count,GROUP_CONCAT(id SEPARATOR ',') as ids FROM `ospos_employees` where category = '3' and role = '3' and deleted = '0'");
$crm_hl_info = $crm_hl->result_array();
$crm_hl_ids = $crm_hl_info[0]['ids'];
$crm_hl_count = $crm_hl_info[0]['count'];
//Health Manager Ids
$tl_hl = $this->db->query("SELECT count(id) as count,GROUP_CONCAT(id SEPARATOR ',') as ids FROM `ospos_employees` where category = '3' and role = '6' and deleted = '0'");
$tl_hl_info = $tl_hl->result_array();
$tl_hl_ids = $tl_hl_info[0]['ids'];
$tl_hl_count = $tl_hl_info[0]['count'];
//RM Ids
$rm = $this->db->query("SELECT count(id) as count,GROUP_CONCAT(id SEPARATOR ',') as ids FROM `ospos_employees` where category = '2' and role = '5' and grade != '18' and deleted = '0'");
$rm_info = $rm->result_array();
$rm_ids = $rm_info[0]['ids'];
$rm_count = $rm_info[0]['count'];
//RM Manager Role
$rmm = $this->db->query("SELECT count(id) as count,GROUP_CONCAT(id SEPARATOR ',') as ids FROM `ospos_employees` where role = '9' and deleted = '0'");
$rmm_info = $rmm->result_array();
$rmm_ids = $rmm_info[0]['ids'];
$rmm_count = $rmm_info[0]['count'];
// Collect IDs for Role End'
//Comman function start
$cur_month = date("Y-m");
$today = new DateTime();
$lastDay = new DateTime('last day of this month');
$DaysRemaining = $lastDay->diff($today)->format('%a');
$datestring= "$today first day of last month";
$dt=date_create($datestring);
$last_month = $dt->format('Y-m');
$april_month = date("Y-04");
$this_month_last= date("Y-m-d");
$april_month_first = date("Y-04").'-01';
$last_month_last=$dt->format('Y-m').'-31';
//Box 1 Plan - Achieved - RM Count - RRR
$company_plan = $this->db->query("SELECT IFNULL(sum(premium),0) as value FROM `ospos_target` where emp_id in ($rmm_ids) and category = '2' and from_date like '$cur_month%' and to_date like '$cur_month%' ");
$company_plan_array = $company_plan->result_array();
$company_plan_rm_target = $company_plan_array[0]['value'];
$company_plan_rm_target_convert= tlac($company_plan_rm_target);
// $company_plan_rm_target_convert=get_thousand($company_plan_rm_target);
if(!$company_plan_rm_target){
$company_plan_rm_target_convert = 0;
}
$company_plan_rm_achived = $this->db->query("SELECT IFNULL(sum(overall_net_premium),0) as value FROM `ospos_lead_type_info` inner join ospos_met_log on ospos_lead_type_info.lead_id=ospos_met_log.log_lead_id inner join ospos_leads_product on ospos_lead_type_info.lead_type_id=ospos_leads_product.lead_info_id where lead_status='9' and ospos_lead_type_info.status='1' and ospos_lead_type_info.status='1' and category = '2' and first_met='1' and login_date like '$cur_month%'");
$company_plan_rm_achived_array = $company_plan_rm_achived->result_array();
$company_plan_rm_achived = $company_plan_rm_achived_array[0]['value'];
$company_plan_rm_achived_value_convert= tlac($company_plan_rm_achived);
if(!$company_plan_rm_achived_value_convert){
$company_plan_rm_achived_value_convert = 0;
}
//ECHO $company_plan_rm_target.' - '.$company_plan_rm_achived.'-'.$DaysRemaining;
// RRR calculation
$rrr_rm= tlac(round(($company_plan_rm_target - $company_plan_rm_achived)/ ($DaysRemaining),0));
//Box 1 Plan - Achieved - RM Count - RRR End
//Box 2 Plan - Achieved - CRM Count - RRR
$company_plan_health = $this->db->query("SELECT IFNULL(sum(premium),0) as value FROM `ospos_target` where emp_id in ($tl_hl_ids) and category = '3' and from_date like '$cur_month%' and to_date like '$cur_month%' ");
$company_plan_health_array = $company_plan_health->result_array();
$company_plan_health_target = $company_plan_health_array[0]['value'];
$company_plan_health_target_convert= tlac($company_plan_health_target);
if(!$company_plan_health_target){
$company_plan_health_target_convert = 0;
}
$company_plan_health_achived = $this->db->query("SELECT IFNULL(sum(overall_net_premium),0) as value FROM `ospos_lead_type_info` inner join ospos_leads_product on ospos_lead_type_info.lead_type_id=ospos_leads_product.lead_info_id where lead_status='11' and prospect_level='4' and category = '3' and login_date like '$cur_month%'");
$company_plan_health_achived_array = $company_plan_health_achived->result_array();
$company_plan_health_achived_value = $company_plan_health_achived_array[0]['value'];
$company_plan_health_achived_value_convert= tlac($company_plan_health_achived_value);
if(!$company_plan_health_achived_value_convert){
$company_plan_health_achived_value_convert = 0;
}
//ECHO "$company_plan_health_target - $company_plan_health_achived_value_convert - $DaysRemaining";
// RRR calculation
$rrr_health= tlac(round(($company_plan_health_target - $company_plan_health_achived_value)/ ($DaysRemaining),0));
//Box 2 Plan - Achieved - CRM Count - RRR
//Box 3 SIP - Lumpsum - achived SIP %
$sip_target_qry = $this->db->query("SELECT IFNULL(sum(sip),0) as premium FROM `ospos_target` where emp_id in ($rmm_ids) and from_date like '$cur_month%' and to_date like '$cur_month%' ");
$sip_target_result = $sip_target_qry->result_array();
$sip_target_val= $sip_target_result[0]['premium'];
$sip_target = tlac($sip_target_val);
if(!$sip_target){
$sip_target = 0;
}
//SELECT IFNULL(sum(amount),0) as value FROM `ospos_mf_investment` where entry_date like '$cur_month%' and investment_type='SIP' and fund_class='Liquid'
$company_sip = $this->db->query("SELECT IFNULL(sum(ospos_mf_investment.amount),0) as value 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.status = '1'");
$company_sip_array = $company_sip->result_array();
$company_sip_achived = $company_sip_array[0]['value'];
$company_sip_achived_convert= tlac($company_sip_achived);
if(!$company_sip_achived_convert){
$company_sip_achived_convert = 0;
}
$company_lum = $this->db->query("SELECT IFNULL(sum(amount),0) as value FROM `ospos_mf_investment` where entry_date like '$cur_month%' and investment_type='LUMPSUM'");
$company_lum_array = $company_lum->result_array();
$company_lum_array_value = $company_lum_array[0]['value'];
$company_lum_array_value_convert= tlac($company_lum_array_value);
if(!$company_lum_array_value_convert){
$company_lum_array_value_convert = 0;
}
// SIP Avg calculation
//$avg_sip= round(($company_sip_achived)/$rm_count ,0);
//echo "GS:: $company_sip_achived :: $sip_target";
$avg_sip_per=round((($company_sip_achived)/$sip_target_val) * 100 ,0);
//Box 3 SIP - Lumpsum - Avg SIP End
//Box 4 Total - Company - Conversion
$company_tot_met = $this->db->query("SELECT count(*) as value 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 category = '2' and first_met='1' and ospos_met_log.met_date like '$cur_month%'");
$company_tot_met_array = $company_tot_met->result_array();
$company_tot_met_count = $company_tot_met_array[0]['value'];
if(!$company_tot_met_count){
$company_tot_met_count = 0;
}
$company_tot_met_crm = $this->db->query("SELECT count(*) as value 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 not in ($rm_ids) and category = '2' and first_met='1' and ospos_met_log.met_date like '$cur_month%'");
$company_tot_met_crm_array = $company_tot_met_crm->result_array();
$company_tot_met_crm_count = $company_tot_met_crm_array[0]['value'];
if(!$company_tot_met_crm_count){
$company_tot_met_crm_count = 0;
}
$company_tot_met_converted = $this->db->query("SELECT count(*) as value 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 prospect_level='4' and category = '2' and first_met='1' and ospos_met_log.met_date like '$cur_month%' ");
$company_tot_met_converted_array = $company_tot_met_converted->result_array();
$company_tot_met_converted_count = $company_tot_met_converted_array[0]['value'];
if(!$company_tot_met_converted_count){
$company_tot_met_converted_count = 0;
}
//Box 4 Total - Company - Conversion
//Box 5 Curr. rm Prod - LM rm Prod. - FY Prod month
//current month productivity
$company_crr_pro = $this->db->query("SELECT sum(overall_net_premium) as value FROM `ospos_lead_type_info` inner join ospos_leads_product on ospos_lead_type_info.lead_type_id=ospos_leads_product.lead_info_id where lead_status='9' and category = '2' and login_date like '$cur_month%'");
$company_crr_pro_array = $company_crr_pro->result_array();
$company_crr_pro_value = $company_crr_pro_array[0]['value'];
$company_crr_pro_value_avg=round(($company_crr_pro_value/$rm_count),0);
if(!$company_crr_pro_value_avg){
$company_crr_pro_value_avg = 0;
}
//last month productivity
$company_last_pro = $this->db->query("SELECT sum(overall_net_premium) as value FROM `ospos_lead_type_info` inner join ospos_leads_product on ospos_lead_type_info.lead_type_id=ospos_leads_product.lead_info_id where lead_status='9' and category = '2' and login_date like '$last_month%'");
$company_last_pro_array = $company_last_pro->result_array();
$company_last_pro_value = $company_last_pro_array[0]['value'];
$company_last_pro_value_avg= round(($company_last_pro_value/$rm_count),0);
if(!$company_last_pro_value_avg){
$company_last_pro_value_avg = 0;
}
$company_year_pro = $this->db->query("SELECT sum(overall_net_premium) as value FROM `ospos_lead_type_info`inner join ospos_leads_product on ospos_lead_type_info.lead_type_id=ospos_leads_product.lead_info_id where category = '2' and lead_status = '9' and prospect_level='4' and ospos_lead_type_info.status='1' and ospos_leads_product.status='1' and login_date BETWEEN '$april_month_first' and '$last_month_last'");
$company_year_pro_array = $company_year_pro->result_array();
$company_year_pro_value = $company_year_pro_array[0]['value'];
if(!$company_year_pro_value){
$company_year_pro_value = 0;
}
$month_deff=moth_diff($april_month_first,$this_month_last);
$company_year_pro_value_avg=round(($company_year_pro_value)/moth_diff($april_month_first,$this_month_last) ,0);
//Box 5 Curr. rm Prod - LM rm Prod. - FY Prod month Prod
//Box 6 Health Curr. Prod - LM Prod. - FY prod
//current month productivity
$company_crr_health_pro = $this->db->query("SELECT sum(overall_net_premium) as value FROM `ospos_lead_type_info` inner join ospos_leads_product on ospos_lead_type_info.lead_type_id=ospos_leads_product.lead_info_id where category = '3' and login_date like '$cur_month%'");
$company_crr_health_pro_array = $company_crr_health_pro->result_array();
$company_crr_health_pro_array_value = $company_crr_health_pro_array[0]['value'];
$company_crr_health_pro_value_avg=round(($company_crr_health_pro_array_value/$crm_hl_count),0);
if(!$company_crr_health_pro_value_avg){
$company_crr_health_pro_value_avg = 0;
}
//last month productivity
$company_last_health_pro = $this->db->query("SELECT sum(overall_net_premium) as value FROM `ospos_lead_type_info` inner join ospos_leads_product on ospos_lead_type_info.lead_type_id=ospos_leads_product.lead_info_id where category = '3' and login_date like '$last_month%'");
$company_last_health_pro_array = $company_last_health_pro->result_array();
$company_last_health_pro_value = $company_last_health_pro_array[0]['value'];
$company_last_health_pro_value_avg= round(($company_last_health_pro_value/$rm_count),0);
if(!$company_last_health_pro_value_avg){
$company_last_health_pro_value_avg = 0;
}
$company_year_health_pro = $this->db->query("SELECT sum(overall_net_premium) as value FROM `ospos_lead_type_info` inner join ospos_leads_product on ospos_lead_type_info.lead_type_id=ospos_leads_product.lead_info_id where category = '3' and ospos_lead_type_info.status='1' and ospos_leads_product.status='1' and login_date BETWEEN '$april_month_first' and '$last_month_last'");
$company_year_health_pro_array = $company_year_health_pro->result_array();
$company_year_health_pro_array_value = $company_year_health_pro_array[0]['value'];
if(!$company_year_health_pro_array_value){
$company_year_health_pro_array_value = 0;
}
$company_year_health_value_avg=round(($company_year_health_pro_array_value)/moth_diff($april_month_first,$this_month_last) ,0);
//Box 6 Curr. rm Prod - LM rm Prod. - FY Prod month Prod
//Box 1
$company_met = $this->db->query("SELECT count(*) as count from ospos_lead_type_info where lead_status = '9' and met_date like '$cur_month%'");
$company_met_result = $company_met->result_array();
$tot_company_met = $company_met_result[0]['count'];
$crm_met = $this->db->query("SELECT count(*) as count from ospos_lead_type_info where lead_status = '9' and created_by in ($crm_port_ids) and met_date like '$cur_month%'");
$tot_met_result = $crm_met->result_array();
$tot_met = $tot_met_result[0]['count'];
$company_log = $this->db->query("SELECT count(*) as count from ospos_lead_type_info left join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id where lead_status = '9' and prospect_level = '4' and login_date like '$cur_month%' and ospos_leads_product.status = '1'");
$tot_log_result = $company_log->result_array();
$tot_log = $tot_log_result[0]['count'];
$cr = round(($tot_log / $tot_company_met) * 100);
//Box 2
$login_value = $this->db->query("SELECT cat_name,IFNULL(sum(overall_net_premium),0) as value FROM `ospos_lead_type_info` inner join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id inner join ospos_category on ospos_category.cat_id = ospos_leads_product.product_category WHERE login_date like '$cur_month%' and ospos_leads_product.status = '1' GROUP BY cat_name");
$login_value_result = $login_value->result_array();
$life_val = 0;
$hl_val = 0;
foreach ($login_value_result as $key => $value) {
$category = $value['cat_name'];
$value = $value['value'];
if($category === "Portfolio"){
$life_val = $value;
}else
if($category === "Health Insurance"){
$hl_val = $value;
}
}
$sip_login = $this->db->query("SELECT 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) and ospos_lead_type_info.status = '1'");
$sip_login_rslt = $sip_login->result_array();
$sip_value = $sip_login_rslt[0]['amount'];
//Box 3
$target = $this->db->query("SELECT value from ospos_incentives where role = '$emp_role' and grade='$emp_grade' and from_date like '$cur_month%' and to_date like '$cur_month%'");
$target_result = $target->result_array();
$target_value = $target_result[0]['value'];
$rrr = round(($target_value - $life_val) / ($last_day - $date),1);
//Box 4
$login_qry = $this->db->query("SELECT sum(overall_net_premium) as value FROM `ospos_lead_type_info` INNER JOIN ospos_leads_product on lead_type_id = lead_info_id left join ospos_leads on ospos_leads.lead_id = ospos_lead_type_info.lead_id where prospect_level='4' and product_category = '3' and login_date like '$last_month%' and ospos_leads_product.status = '1'");
$login_result = $login_qry->result_array();
$last_login = $login_result[0]['value'];
$last_login_qry = $this->db->query("SELECT sum(overall_net_premium) as value FROM `ospos_lead_type_info` INNER JOIN ospos_leads_product on lead_type_id = lead_info_id inner join ospos_leads on ospos_leads.lead_id = ospos_lead_type_info.lead_id where prospect_level='4' and product_category = '3' and login_date BETWEEN '$fin_start_month' and '$last_month_endDate' and ospos_leads_product.status = '1'");
$last_login_result = $last_login_qry->result_array();
$last_login = $last_login_result[0]['value'];
$ytd_avg = round($hl_val / $lst_mth ,0);
//Box 6
$health_crm_avg = $hl_val /$crm_hl_count;
//Box 7
$rm_avg_met = round($tot_company_met /$rm_count);
//Box 8
$rm_avg_val = round($life_val /$rm_count);
/* Met Status */
$A_met_status = $this->db->query("SELECT prospect_name,count(*) as count,IFNULL(sum(expected_login),0) as amount,IFNULL(sum(overall_net_premium),0) as value FROM `ospos_lead_type_info` left join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id inner join ospos_prospect_level on prospect_id = prospect_level where lead_status = '9' and met_date like '$cur_month%' GROUP by prospect_level");
$A_met_lead_rslt = $A_met_status->result_array();
$A_met_hot = 0;
$A_met_hot_amt = 0;
$A_met_warm = 0;
$A_met_warm_amt =0;
$A_met_log = 0;
$A_met_log_amt = 0;
foreach ($A_met_lead_rslt as $key => $value) {
$prospect_name = $value['prospect_name'];
$count = $value['count'];
$amount = $value['amount'];
$value = $value['value'];
if($prospect_name === "Hot"){
$A_met_hot = $count;
$A_met_hot_amt = $amount;
}else
if($prospect_name === "Warm"){
$A_met_warm = $count;
$A_met_warm_amt = $amount;
}else
if($prospect_name === "Login"){
$A_met_log = $count;
$A_met_log_val = $value;
}
}
$A_ncd_met_status = $this->db->query("SELECT prospect_name,count(*) as count,IFNULL(sum(expected_login),0) as amount,IFNULL(sum(overall_net_premium),0) as value FROM `ospos_lead_type_info` left join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id inner join ospos_prospect_level on prospect_id = prospect_level where lead_status = '9' and ncd like '$cur_month%' and met_date not like '$cur_month%' GROUP by prospect_name");
$A_ncd_met_lead_rslt = $A_ncd_met_status->result_array();
$A_ncd_met_hot = 0;
$A_ncd_met_hot_amt = 0;
$A_ncd_met_warm = 0;
$A_ncd_met_warm_amt = 0;
$A_ncd_met_log = 0;
$A_ncd_met_log_val = 0;
foreach ($A_ncd_met_lead_rslt as $key => $value) {
$prospect_name = $value['prospect_name'];
$count = $value['count'];
$amount = $value['amount'];
$value = $value['value'];
if($prospect_name === "Hot"){
$A_ncd_met_hot = $count;
$A_ncd_met_hot_amt = $amount;
}else
if($prospect_name === "Warm"){
$A_ncd_met_warm = $count;
$A_ncd_met_warm_amt = $amount;
}else
if($prospect_name === "Login"){
$A_ncd_met_log = $count;
$A_ncd_met_log_val = $value;
}
}
$tot_hot = $A_met_hot + $A_ncd_met_hot;
$tot_hot_amt = $A_met_hot_amt + $A_ncd_met_hot_amt;
$tot_warm = $A_met_warm + $A_ncd_met_warm;
$tot_warm_amt = $A_met_warm_amt + $A_ncd_met_warm_amt;
$tot_login = $A_met_log + $A_ncd_met_log;
$tot_login_val= $A_met_log_val + $A_ncd_met_log_val;
/* Month Login */
$month_login_status = $this->db->query("SELECT ospos_lead_type.lead_type,sum(overall_net_premium) as value FROM `ospos_lead_type_info` left join ospos_lead_type on ospos_lead_type_info.lead_type = ospos_lead_type.lead_type_id inner join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id inner join ospos_lead_status on status_id = lead_status where login_date like '$cur_month%' GROUP by lead_type");
$month_login_rslt = $month_login_status->result_array();
$self = "0";
$upsell = "0";
$ref = "0";
foreach ($month_login_rslt as $key => $value) {
$lead_type = $value['lead_type'];
$val = $value['value'];
if($lead_type === "Self"){
$self = $val;
}else
if($lead_type === "Upsell"){
$upsell = $val;
}else
if($lead_type === "Reference"){
$ref = $val;
}
}
/* Month Login */
$month_crm_status = $this->db->query("SELECT ospos_lead_type.lead_type,IFNULL(sum(overall_net_premium),0) as value FROM `ospos_lead_type_info` left join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id left join ospos_lead_type on ospos_lead_type_info.lead_type = ospos_lead_type.lead_type_id inner join ospos_lead_status on status_id = lead_status where prospect_level = '4' and ospos_lead_type_info.lead_type != '8' and ospos_lead_type_info.lead_type != '9' and ospos_lead_type_info.lead_type != '10' and login_date like '$cur_month%' and ospos_leads_product.status = '1'");
$month_crm_rslt = $month_crm_status->result_array();
$lead_type = $month_crm_rslt[0]['lead_type'];
$crm_count = $month_crm_rslt[0]['value'];
/*Contribution */
$login = $this->db->query("SELECT IFNULL(sum(overall_net_premium),0) as value from ospos_lead_type_info left join ospos_leads_product on lead_type_id = lead_info_id where prospect_level = '4' and login_date like '$cur_month%' and ospos_leads_product.status = '1'");
$login_result = $login->result_array();
$total_login = $login_result[0]['value'];
$self_con = round(($self / $total_login)*100);
$upsell_con = round(($upsell / $total_login)*100);
$ref_con = round(($ref / $total_login)*100);
$crm_con = round(($crm_count / $total_login)*100);
/* Other Campaigns */
$month_oc_status = $this->db->query("SELECT sum(overall_net_premium) as value,cat_name FROM `ospos_lead_type_info` inner join ospos_category on ospos_category.cat_id = ospos_lead_type_info.category LEFT JOIN ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id where lead_mode = '2' AND login_date like '$cur_month%' and ospos_leads_product.status = '1' GROUP BY product_category");
$month_oc_rslt = $month_oc_status->result_array();
$term = "0";
$health = "0";
foreach ($month_oc_rslt as $key => $value){
$cat_name = $value['cat_name'];
$val = $value['value'];
if($cat_name === "Term Plan"){
$term = $val;
}else
if($cat_name === "Health Insurance"){
$health = $val;
}
}
/* Health Team Leader */
$ht_tl_info = $this->db->query("SELECT id,first_name,reporting FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id where role = '6' and deleted = '0' and category = '3' ORDER BY ospos_employees.reporting ASC");
$ht_tl_rslt = $ht_tl_info->result();
$ht_tl_list = "";
foreach ($ht_tl_rslt as $key => $value) {
$id = $value->id;
$first_name = $value->first_name;
$team_info = $this->db->query("SELECT GROUP_CONCAT(id) as team_ids FROM `ospos_employees` WHERE reporting = '$id'");
$team_rslt = $team_info->row();
$team_ids = $team_rslt->team_ids.",".$id;
$team_ids = ltrim($team_ids,",");
if($team_ids){
$lead_info = $this->db->query("SELECT count(*) as nop,ospos_leads_product.company,vendorcompanyname,ospos_leads_product.product_category as category,IFNULL(sum(overall_net_premium),0) as netamount FROM ospos_lead_type_info left join ospos_leads_product on lead_type_id = lead_info_id left join ospos_vendor on vendor_id = ospos_leads_product.company where login_date like '$cur_month%' and ospos_leads_product.status = '1' and ospos_lead_type_info.created_by in ($team_ids)");
$lead_rslt = $lead_info->result();
$star_amt = 0;
foreach ($lead_rslt as $key => $value){
$nop = $value->nop;
$netamount = $value->netamount;
$avg = round($netamount / $nop) ;
}
$team_zero = $this->db->query("SELECT login_date FROM `ospos_lead_type_info` inner join ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id where prospect_level='4' and ospos_leads_product.status = '1' and ospos_lead_type_info.created_by in ($team_ids) order by login_date desc limit 0,1");
$result = $team_zero->result_array();
$login_date = $result[0]['login_date'];
if($login_date === ""){
$login_date =date('Y-m-01');
}
$now = time();
$your_date = strtotime($login_date);
$datediff = $now - $your_date;
$Zero_days = floor($datediff / (60 * 60 * 24));
$lead_gen = $this->db->query("SELECT count(*) as gen FROM ospos_lead_type_info where category = '3' and created_date like '$cur_month%' and ospos_lead_type_info.created_by in ($team_ids)");
$lead_gen_rslt = $lead_gen->result();
$gen = 0;
foreach ($lead_gen_rslt as $key => $value){
$gen = $value->gen;
}
$ht_tl_list .= "<tr>
<td>$first_name</td>
<td class='cursor' onclick=get_mis_hllead($emp_role,$emp_cat,'$team_ids',3,'M','ALL');>$netamount</td>
<td>$nop</td>
<td>$avg</td>
<td>$Zero_days</td>
<td>$gen</td>
</tr>";
}
}
/* Other Campaigns */
$month_oc_mf_status = $this->db->query("SELECT sum(mf_amount) as value,mf_investment FROM `ospos_lead_type_info` inner join ospos_category on ospos_category.cat_id = ospos_lead_type_info.category LEFT JOIN ospos_leads_product on ospos_leads_product.lead_info_id = ospos_lead_type_info.lead_type_id where lead_mode = '2' AND product_category = '4' and login_date like '$cur_month%' and ospos_leads_product.status = '1' GROUP BY mf_investment");
$month_oc_mf_rslt = $month_oc_mf_status->result_array();
$sip = "0";
$lumpsum = "0";
foreach ($month_oc_mf_rslt as $key => $value){
$cat_name = $value['mf_investment'];
$val = $value['value'];
if($cat_name === "1"){
$sip = $val;
}else
if($cat_name === "2"){
$lumpsum = $val;
}
}
$mis_info = $this->db->query("SELECT count(DISTINCT(lead_id)) as count,IFNULL(sum(expected_login),0) as value,category FROM `ospos_lead_type_info` where current_control = 'MIS' and ncd like '$cur_month%' GROUP by category");
$mis_rslt = $mis_info->result();
$mis_life_count = 0;
$mis_life_amt = 0;
$mis_hl_count = 0;
$mis_hl_amt = 0;
foreach ($mis_rslt as $key => $value) {
$count = $value->count;
$category = $value->category;
$netamount = $value->value;
if($category === "2"){
$mis_life_count = $count;
$mis_life_amt = $netamount;
}
if($category === "3"){
$mis_hl_count = $count;
$mis_hl_amt = $netamount;
}
}
$hc_info = $this->db->query("SELECT count(DISTINCT(lead_id)) as count,ospos_leads_product.product_category as category,IFNULL(sum(overall_net_premium),0) as netamount FROM ospos_lead_type_info inner join ospos_leads_product on lead_type_id = lead_info_id where current_control = 'HC' and ospos_leads_product.status = '1' and ncd like '$cur_month%' GROUP by ospos_leads_product.product_category");
$hc_rslt = $hc_info->result();
$hc_life_count = 0;
$hc_life_amt = 0;
$hc_hl_count = 0;
$hc_hl_amt = 0;
foreach ($hc_rslt as $key => $value) {
$count = $value->count;
$category = $value->category;
$netamount = $value->netamount;
if($category === "2"){
$hc_life_count = $count;
$hc_life_amt = $netamount;
}
if($category === "3"){
$hc_hl_count = $count;
$hc_hl_amt = $netamount;
}
}
$completed_info = $this->db->query("SELECT count(DISTINCT(lead_id)) as count,ospos_leads_product.product_category as category,IFNULL(sum(overall_net_premium),0) as netamount FROM ospos_lead_type_info inner join ospos_leads_product on lead_type_id = lead_info_id where current_control = 'completed' and ospos_leads_product.status = '1' and ncd like '$cur_month%' GROUP by ospos_leads_product.product_category");
$completed_rslt = $completed_info->result();
$cp_life_count = 0;
$cp_life_amt = 0;
$cp_hl_count = 0;
$cp_hl_amt = 0;
foreach ($completed_rslt as $key => $value) {
$count = $value->count;
$category = $value->category;
$netamount = $value->netamount;
if($category === "2"){
$cp_life_count = $count;
$cp_life_amt = $netamount;
}
if($category === "3"){
$cp_hl_count = $count;
$cp_hl_amt = $netamount;
}
}
//Issuance Status
$issuance_info = $this->db->query("SELECT count(*) as count,issuance_status,cat_name FROM `ospos_happy_calling` left join ospos_leads_product on customer_product_id = call_product_id inner join ospos_category on cat_id = product_category where ospos_leads_product.login_date like '$cur_month%' and ospos_leads_product.status = '1' GROUP BY issuance_status,product_category");
$issuance_rslt = $issuance_info->result();
$h_issued = 0;
$h_issu_pen = 0;
$h_medi_pen = 0;
$h_doc_pen = 0;
$h_check_dis = 0;
$h_med_dec = 0;
$p_issued = 0;
$p_issu_pen = 0;
$p_medi_pen = 0;
$p_doc_pen = 0;
$p_check_dis = 0;
$p_med_dec = 0;
foreach($issuance_rslt as $key => $value) {
$count = $value->count;
$category = $value->cat_name;
$issuance_status = $value->issuance_status;
if($category === "Portfolio"){
if($issuance_status === "Issued"){
$p_issued = $count;
}else
if($issuance_status === "Pending For Issuance"){
$p_issu_pen = $count;
}else
if($issuance_status === "Medical Pending"){
$p_medi_pen = $count;
}else
if($issuance_status === "Docs Pending from Sales"){
$p_doc_pen = $count;
}else
if($issuance_status === "Cheque Dishonor"){
$p_check_dis = $count;
}else
if($issuance_status === "Medical Declined"){
$p_med_dec = $count;
}
}else
if($category === "Health Insurance"){
if($issuance_status === "Issued"){
$h_issued = $count;
}else
if($issuance_status === "Pending For Issuance"){
$h_issu_pen = $count;
}else
if($issuance_status === "Medical Pending"){
$h_medi_pen = $count;
}else
if($issuance_status === "Docs Pending from Sales"){
$h_doc_pen = $count;
}else
if($issuance_status === "Cheque Dishonor"){
$h_check_dis = $count;
}else
if($issuance_status === "Medical Declined"){
$h_med_dec = $count;
}
}
}
$action_info = $this->db->query("SELECT count(*) as count,issuance_status,ospos_happy_calling.login_date,cat_name FROM `ospos_happy_calling` left join ospos_leads_product on customer_product_id = call_product_id inner join ospos_category on cat_id = product_category WHERE issuance_status != 'Issued' and ospos_leads_product.status = '1' AND ospos_happy_calling.login_date <= DATE_ADD(CURDATE(),INTERVAL -7 DAY) GROUP BY issuance_status,cat_name");
$action_rslt = $action_info->result();
$h_act_issued = 0;
$h_act_issu_pen = 0;
$h_act_medi_pen = 0;
$h_act_doc_pen = 0;
$h_act_check_dis = 0;
$h_act_med_dec = 0;
$p_act_issued = 0;
$p_act_issu_pen = 0;
$p_act_medi_pen = 0;
$p_act_doc_pen = 0;
$p_act_check_dis = 0;
$p_act_med_dec = 0;
foreach($action_rslt as $key => $value) {
$count = $value->count;
$category = $value->cat_name;
$issuance_status = $value->issuance_status;
if($category === "Portfolio"){
if($issuance_status === "Pending For Issuance"){
$p_act_issu_pen = $count;
}else
if($issuance_status === "Medical Pending"){
$p_act_medi_pen = $count;
}else
if($issuance_status === "Docs Pending from Sales"){
$p_act_doc_pen = $count;
}else
if($issuance_status === "Cheque Dishonor"){
$p_act_check_dis = $count;
}else
if($issuance_status === "Medical Declined"){
$p_act_med_dec = $count;
}
}else
if($category === "Health Insurance"){
if($issuance_status === "Pending For Issuance"){
$h_act_issu_pen = $count;
}else
if($issuance_status === "Medical Pending"){
$h_act_medi_pen = $count;
}else
if($issuance_status === "Docs Pending from Sales"){
$h_act_doc_pen = $count;
}else
if($issuance_status === "Cheque Dishonor"){
$h_act_check_dis = $count;
}else
if($issuance_status === "Medical Declined"){
$h_act_med_dec = $count;
}
}
}
//Document Status
$document_info = $this->db->query("SELECT count(*) as count,document_issue_status,cat_name FROM `ospos_happy_calling` left join ospos_leads_product on customer_product_id = call_product_id inner join ospos_category on cat_id = product_category where issued_date like '$cur_month%' and ospos_leads_product.status = '1' GROUP BY document_issue_status,product_category");
$document_rslt = $document_info->result();
$h_dis = 0;
$h_not_dis = 0;
$h_return = 0;
$h_reprint = 0;
$h_reach = 0;
$h_cash = 0;
$p_dis = 0;
$p_not_dis = 0;
$p_return = 0;
$p_reprint = 0;
$p_reach = 0;
$p_cash = "N/A";
foreach($document_rslt as $key => $value){
$count = $value->count;
$category = $value->cat_name;
$document_issue_status = $value->document_issue_status;
if($category === "Portfolio"){
if($document_issue_status === "Dispatched"){
$p_dis = $count;
}else
if($document_issue_status === "NotDispatched"){
$p_not_dis = $count;
}else
if($document_issue_status === "ReturnedToHO"){
$p_return = $count;
}else
if($document_issue_status === "AskedForReprint"){
$p_reprint = $count;
}else
if($document_issue_status === "ReachedBranch"){
$p_reach = $count;
}else
if($document_issue_status === "CashlessCardPending"){
$p_cash = $count;
}
}else
if($category === "Health Insurance"){
if($document_issue_status === "Dispatched"){
$h_dis = $count;
}else
if($document_issue_status === "NotDispatched"){
$h_not_dis = $count;
}else
if($document_issue_status === "ReturnedToHO"){
$h_return = $count;
}else
if($document_issue_status === "AskedForReprint"){
$h_reprint = $count;
}else
if($document_issue_status === "ReachedBranch"){
$h_reach = $count;
}else
if($document_issue_status === "CashlessCardPending"){
$h_cash = $count;
}
}
}
$doc_action_info = $this->db->query("SELECT count(*) as count,document_issue_status,ospos_happy_calling.issued_date,cat_name FROM `ospos_happy_calling` left join ospos_leads_product on customer_product_id = call_product_id inner join ospos_category on cat_id = product_category WHERE document_issue_status != 'Dispatched' and ospos_leads_product.status = '1' AND ospos_happy_calling.issued_date <= DATE_ADD(CURDATE(),INTERVAL -7 DAY) GROUP BY document_issue_status,cat_name");
$doc_action_rslt = $doc_action_info->result();
$h_act_dis = 0;
$h_act_not_dis = 0;
$h_act_return = 0;
$h_act_reprint = 0;
$h_act_reach = 0;
$h_act_cash = 0;
$p_act_dis = 0;
$p_act_not_dis = 0;
$p_act_return = 0;
$p_act_reprint = 0;
$p_act_reach = 0;
$p_act_cash = "N/A";
foreach($doc_action_rslt as $key => $value) {
$count = $value->count;
$category = $value->cat_name;
$document_issue_status = $value->document_issue_status;
if($category === "Portfolio"){
if($document_issue_status === "NotDispatched"){
$p_act_not_dis = $count;
}else
if($document_issue_status === "ReturnedToHO"){
$p_act_return = $count;
}else
if($document_issue_status === "AskedForReprint"){
$p_act_reprint = $count;
}else
if($document_issue_status === "ReachedBranch"){
$p_act_reach = $count;
}else
if($document_issue_status === "CashlessCardPending"){
$p_act_cash = $count;
}
}else
if($category === "Health Insurance"){
if($document_issue_status === "NotDispatched"){
$h_act_not_dis = $count;
}else
if($document_issue_status === "ReturnedToHO"){
$h_act_return = $count;
}else
if($document_issue_status === "AskedForReprint"){
$h_act_reprint = $count;
}else
if($document_issue_status === "ReachedBranch"){
$h_act_reach = $count;
}else
if($document_issue_status === "CashlessCardPending"){
$h_act_cash = $count;
}
}
}
$rm_info = $this->db->query("SELECT id,first_name,reporting,IFNULL(sum(overall_net_premium),0) as netamount FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id left join ospos_lead_type_info on ospos_lead_type_info.created_by = ospos_employees.id left join ospos_leads_product on lead_type_id = lead_info_id where role = '5' and deleted = '0' and ospos_leads_product.status = '1' GROUP by reporting,id ORDER BY `netamount` ASC");
$rm_rslt = $rm_info->result();
$rm_list = "";
foreach ($rm_rslt as $key => $value) {
$id = $value->id;
$first_name = $value->first_name;
$reporting = $value->reporting;
$rm_manager_info = $this->db->query("SELECT id,first_name FROM `ospos_employees` INNER join ospos_people on ospos_people.person_id = ospos_employees.id where ospos_employees.id = '$reporting' ");
$rm_manager_rslt = $rm_manager_info->row();
$rm_manage = $rm_manager_rslt->first_name;
$lead_info = $this->db->query("SELECT ospos_leads_product.product_category as category,IFNULL(sum(overall_net_premium),0) as netamount,IFNULL(sum(mf_amount),0) as mf_amount,mf_investment FROM ospos_lead_type_info INNER join ospos_leads_product on lead_type_id = lead_info_id where login_date like '$cur_month%' and (ospos_lead_type_info.created_by = '$id' or ospos_lead_type_info.rm_name = '$id') and mf_investment != '2' and ospos_leads_product.status = '1' GROUP by ospos_leads_product.product_category");
$lead_rslt = $lead_info->result();
$lifeamount = 0;
$mfamount = 0;
$termamount = 0;
foreach($lead_rslt as $key => $value) {
$category = $value->category;
$netamount = $value->netamount;
$mf_amount = $value->mf_amount;
$type = $value->mf_investment;
if($category === "2"){
$lifeamount = $netamount;
}
if($category === "4"){
$mfamount = $mf_amount;
}
if($category === "5"){
$termamount = $netamount;
}
}
$rm_list .= "<tr>
<td>$rm_manage</td>
<td>$first_name</td>
<td class='cursor' onclick=get_mis_rmlead($emp_role,$emp_cat,$id,2,'M','');>$lifeamount</td>
<td class='cursor' onclick=get_mis_rmlead($emp_role,$emp_cat,$id,4,'M','SIP');>$mfamount</td>
<td class='cursor' onclick=get_mis_rmlead($emp_role,$emp_cat,$id,2,'M','');>$termamount</td>
</tr>";
}
//
function count_digit($number) {
return strlen($number);
}
function divider($number_of_digits) {
$tens="1";
if($number_of_digits>8)
return 10000000;
while(($number_of_digits-1)>0)
{
$tens.="0";
$number_of_digits--;
}
return $tens;
}
function tlac($num)
{
$number_of_digits = count_digit($num); //this is call :)
if($number_of_digits>3)
{
if($number_of_digits%2!=0)
$divider=divider($number_of_digits-1);
else
$divider=divider($number_of_digits);
}
else
$divider=1;
$fraction=$num/$divider;
$fraction=number_format($fraction,2);
if($number_of_digits==4 ||$number_of_digits==5)
$ext="k";
if($number_of_digits==6 ||$number_of_digits==7)
$ext="L";
if($number_of_digits==8 ||$number_of_digits==9)
$ext="Cr";
return $fraction." ".$ext;
}
function moth_diff($date1,$date2)
{
$ts1 = strtotime($date1);
$ts2 = strtotime($date2);
$year1 = date('Y', $ts1);
$year2 = date('Y', $ts2);
$month1 = date('m', $ts1);
$month2 = date('m', $ts2);
$diff = (($year2 - $year1) * 12) + ($month2 - $month1);
return $diff;
}
echo "
<!-- Top Boxs start -->
<div class='col-md-12 pd0 dash_header'>
<h3 class='text-center headtxt'>General Manager</h3>
<div class='row mg0 mgb15'>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Plan - Achieved - RM Count - RRR'>
<div class='dashbox orange'>
<table>
<tr>
<td class='pd10'><i class='fa fa-heartbeat fa-2x' aria-hidden='true'></i></td>
<td class='white'>
<h5>Life</h5>
<span class='bold'>$company_plan_rm_target_convert - $company_plan_rm_achived_value_convert </span> - <span class='bold' style='color: #080b0c;'>$rrr_rm
</span>
<div style='position: absolute; top: 5px; right: 25px; font-weight: bold;font-size: 11px;'>
$rm_count
</div>
</td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Plan - Achieved - CRM Count - RRR'>
<div class='dashbox red'>
<table>
<tr>
<td class='pd10'><i class='fa fa-user-md fa-2x' aria-hidden='true'></i></td>
<td class='white'>
<h5>Health</h5>
<span class='bold'> $company_plan_health_target_convert - $company_plan_health_achived_value_convert - </span><span class='bold' style='color: #080b0c;'>$rrr_health </span>
<div style='position: absolute; top: 5px; right: 25px; font-weight: bold;font-size: 11px;'>
$crm_hl_count
</div>
</td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Target - SIP - Lumpsum - achived 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 - $company_sip_achived_convert - $company_lum_array_value_convert</span>
<div style='position: absolute; top: 5px; right: 25px; font-weight: bold;font-size: 11px;'>
$avg_sip_per %
</div>
</td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Total - Company - Conversion'>
<div class='dashbox violet'>
<table>
<tr>
<td class='pd10'><i class='fa fa-history fa-2x' aria-hidden='true'></i></td>
<td class='white'>
<h5>Met</h5>
<span class='bold'>$company_tot_met_count - $company_tot_met_crm_count</span>
<div style='position: absolute; top: 5px; right: 25px; font-weight: bold;font-size: 11px;'>
$company_tot_met_converted_count
</div>
</td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Curr. RM Prod - LM RM Prod. - FY avg month '>
<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>Life Avg</h5>
<span class='bold'>$company_crr_pro_value_avg - $company_last_pro_value_avg </span>
<div style='position: absolute; top: 5px; right: 25px; font-weight: bold;font-size: 11px;'>
$company_year_pro_value_avg
</div>
</td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Curr. Prod - LM Prod. - FY prod'>
<div class='dashbox yellow'>
<table>
<tr>
<td class='pd10'><i class='fa fa-tachometer fa-2x' aria-hidden='true'></i></td>
<td class='white'>
<h5>Health Avg</h5>
<span class='bold'>$company_crr_health_pro_value_avg - $company_last_health_pro_value_avg </span>
<div style='position: absolute; top: 5px; right: 25px; font-weight: bold;font-size: 11px;'>
$company_year_health_value_avg
</div>
</td>
</tr>
</table>
</div>
</div>
</div>
</div>
<!-- Top Boxs END -->
<div class='col-md-12 pd0 divider'></div>
<div class='row mg0'>
<div class='col-md-12'>
<div class='form-group' style='margin-bottom:4px;'>
<input type='text' id='gm_start_date' value='$fin_start_date' class='datepicker form-control input-sm '/>
</div>
<div class='form-group' style='margin-bottom:4px;'>
<input type='text' id='gm_end_date' value='$fin_end_date' class='datepicker form-control input-sm '/>
</div>
<div class='form-group' style='margin-bottom:4px;'>
<button id='gm_search' class='btn btn-info btn-sm' onclick ='gm_search()'>search</button>
</div>
</div>
</div>
<div class='row mg0'>
<div class='col-md-12'>
<div class='row mg0'>
<div class='col-md-6 pdl0 col-style'>
<div id='gm_lead_portfolio_chart' style='height:315px;'></div>
</div>
<div class='col-md-6 pdl0 col-style'>
<div id='gm_lead_portfolio_rm_chart' style='height:315px;'></div>
</div>
</div>
</div>
<div class='col-md-12'>
<div class='row mg0'>
<div class='col-md-6 pdl0 col-style'>
<div id='gm_health_crm_chart' style='height:315px;'></div>
</div>
<div class='col-md-6 pdl0 col-style'>
<div id='gm_health_business_chart' style='height:315px;'></div>
</div>
</div>
</div>
<div class='col-md-12'>
<div class='col-md-6 col-style'>
<div id='gm_rm_avg_business_chart' style='height:315px;'></div>
</div>
<div class='col-md-6 col-style'>
<div id='gm_month_wise_chart' style='height:315px;'></div>
</div>
</div>
<div class='col-md-12'>
<div class='col-md-6 pdl0 col-style'>
<div id='gm_health_avg_business_chart' style='height:315px;'></div>
</div>
<div class='col-md-6 pdl0 col-style'>
<div id='gm_health_month_wise_chart' style='height:315px;'></div>
</div>
</div>
<div class='col-md-12'>
<div class='col-md-6 pdl0 col-style'>
<div id='gm_mf_chart' style='height:315px;'></div>
</div>
<div class='col-md-6 pdl0 col-style'>
<div id='gm_mf_month_wise_chart' style='height:315px;'></div>
</div>
</div>
</div>
";
?>