File: /home/cafsindia/crm_cafsindia_com/application/views/mis_dashboard.php
<?php
//MIS
$issues_info = $this->db->query("SELECT ospos_leads_product.product_category as category, IFNULL(sum(overall_net_premium),0) as netamount FROM ospos_leads_product left join ospos_happy_calling on call_product_id = customer_product_id where ospos_leads_product.login_date like '$cur_month%' and ospos_leads_product.status = '1' and issuance_status = 'Issued' GROUP by ospos_leads_product.product_category");
$issues_rslt = $issues_info->result();
$life_issued = 0;
$health_issued = 0;
foreach ($issues_rslt as $key => $value) {
$category = $value->category;
$netamount = $value->netamount;
if($category === "2"){
$life_issued = number_format($netamount);
}
if($category === "3"){
$health_issued = number_format($netamount);
}
}
$log_info = $this->db->query("SELECT ospos_leads_product.product_category as category, IFNULL(sum(overall_net_premium),0) as netamount FROM ospos_lead_type_info left join ospos_leads_product on lead_type_id = lead_info_id where ospos_leads_product.login_date like '$cur_month%' and ospos_lead_type_info.status = '1' and ospos_leads_product.status = '1' GROUP by ospos_leads_product.product_category");
$log_rslt = $log_info->result();
$life_log = 0;
$health_log = 0;
$term_log = 0;
foreach ($log_rslt as $key => $value) {
$category = $value->category;
$netamount = $value->netamount;
if($category === "2"){
$life_log = $netamount;
}
if($category === "3"){
$health_log = $netamount;
}
if($category === "5"){
$term_log = $netamount;
}
}
$mf_info = $this->db->query("SELECT IFNULL(sum(ospos_mf_investment.amount),0) as value,investment_type from ospos_mf_investment 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 ospos_lead_type_info.status = '1' GROUP by investment_type");
$mf_rslt = $mf_info->result();
$sip_amt = 0;
$lumpsum_amt = 0;
foreach ($mf_rslt as $key => $value) {
$type = $value->investment_type;
$netamount = $value->value;
if($type === "SIP"){
$sip_amt = $netamount;
}
if($type === "LUMPSUM"){
$lumpsum_amt = $netamount;
}
}
//Renewal
$collectable_info = $this->db->query("SELECT ospos_leads_product.product_category as category, sum(ren_overall_total_premium) as netamount FROM ospos_renewal inner join ospos_leads_product on ospos_renewal.product_id = ospos_leads_product.customer_product_id where ospos_renewal.due_date like '$cur_month%' and (renew_status != 'Terminated' or renew_status != 'Surrendered') and (renewal_date != due_date) and ospos_renewal.status = '1' GROUP by ospos_leads_product.product_category");
$collectable_rslt = $collectable_info->result();
$life_collectable = 0;
$health_collectable = 0;
foreach ($collectable_rslt as $key => $value) {
$category = $value->category;
$netamount = $value->netamount;
if($category === "2"){
$life_collectable = $netamount;
}
if($category === "3"){
$health_collectable = $netamount;
}
}
$collected_info = $this->db->query("SELECT ospos_leads_product.product_category as category, sum(log_overall_total_premium) as netamount FROM ospos_renewal_log inner join ospos_renewal on ospos_renewal_log.renewal_id = ospos_renewal.renewal_id inner join ospos_leads_product on ospos_renewal.product_id = ospos_leads_product.customer_product_id where ospos_renewal_log.log_renewal_date like '$cur_month%' and (log_renew_status = 'Paid' or log_renew_status = 'Renewed') and ospos_renewal.status = '1' and (ospos_renewal.issued_date IS NOT NULL or ospos_renewal_log.log_renewal_date != ospos_renewal.issued_date) GROUP by ospos_leads_product.product_category");
$collected_rslt = $collected_info->result();
$life_collected = 0;
$health_collected = 0;
foreach ($collected_rslt as $key => $value){
$category = $value->category;
$netamount = $value->netamount;
if($category === "2"){
$life_collected = $netamount;
}
if($category === "3"){
$health_collected = $netamount;
}
}
$life_m_collected_per = round(($life_collected/ $life_collectable) * 100);
$health_m_collected_per = round(($health_collected/ $health_collectable) * 100);
//YTD Collectable % ]
$today = date("Y-m-d");
$collectable_ytd = $this->db->query("SELECT distinct(ospos_renewal_log.renewal_id),ospos_leads_product.product_category as category, sum(log_overall_total_premium) as netamount FROM ospos_renewal_log inner join ospos_renewal on ospos_renewal_log.renewal_id = ospos_renewal.renewal_id inner join ospos_leads_product on ospos_renewal.product_id = ospos_leads_product.customer_product_id where (log_renew_status != 'Terminated' or log_renew_status != 'Surrendered') and ospos_renewal_log.log_due_date between '$fin_start_month' and '$last_month_endDate' and ospos_renewal.status = '1' GROUP by ospos_leads_product.product_category");
$collectable_ytd_rslt = $collectable_ytd->result();
$life_collectable_ytd = 0;
$health_collectable_ytd = 0;
foreach ($collectable_ytd_rslt as $key => $value){
$category = $value->category;
$netamount = $value->netamount;
if($category === "2"){
$life_collectable_ytd = $netamount;
}
if($category === "3"){
$health_collectable_ytd = $netamount;
}
}
//YTD Collected %
$collected_ytd = $this->db->query("SELECT ospos_leads_product.product_category as category, sum(log_overall_total_premium) as netamount FROM ospos_renewal_log INNER JOIN ospos_renewal on ospos_renewal.renewal_id = ospos_renewal_log.renewal_id inner join ospos_leads_product on ospos_renewal.product_id = ospos_leads_product.customer_product_id where (log_renew_status = 'Paid' or log_renew_status = 'Renewed') and ospos_renewal_log.log_renewal_date between '$fin_start_month' and '$last_month_endDate' and ospos_renewal.status = '1' and (ospos_renewal.issued_date IS NOT NULL or ospos_renewal_log.log_renewal_date != ospos_renewal.issued_date) GROUP by ospos_leads_product.product_category");
$collected_ytd_rslt = $collected_ytd->result();
$life_ytd = 0;
$health_ytd = 0;
foreach ($collected_ytd_rslt as $key => $value){
$category = $value->category;
$netamount = $value->netamount;
if($category === "2"){
$life_ytd = $netamount;
}
if($category === "3"){
$health_ytd = $netamount;
}
}
$life_collected_per = round(($life_ytd/ $life_collectable_ytd) * 100);
$health_collected_per = round(($health_ytd/ $health_collectable_ytd) * 100);
//Dropped
$dropped_qry = $this->db->query("SELECT ospos_leads_product.product_category as category, sum(log_overall_total_premium) as netamount FROM ospos_renewal_log inner join ospos_renewal on ospos_renewal_log.renewal_id = ospos_renewal.renewal_id inner join ospos_leads_product on ospos_renewal.product_id = ospos_leads_product.customer_product_id where (log_renew_status = 'Terminated' or log_renew_status = 'Surrendered') and ospos_renewal_log.log_due_date like '$cur_month%' and ospos_renewal.status = '1' GROUP by ospos_leads_product.product_category");
$dropped_rslt = $dropped_qry->result();
$life_drop = 0;
$health_drop = 0;
foreach ($dropped_rslt as $key => $value) {
$category = $value->category;
$netamount = $value->netamount;
if($category === "2"){
$life_drop = number_format($netamount);
}
if($category === "3"){
$health_drop = number_format($netamount);
}
}
//FY Dropped
$fy_dropped_qry = $this->db->query("SELECT ospos_leads_product.product_category as category, sum(log_overall_total_premium) as netamount FROM ospos_renewal_log inner join ospos_renewal on ospos_renewal_log.renewal_id = ospos_renewal.renewal_id inner join ospos_leads_product on ospos_renewal.product_id = ospos_leads_product.customer_product_id where (log_renew_status = 'Terminated' or log_renew_status = 'Surrendered') and ospos_renewal_log.log_due_date between '$fin_start_month' and '$last_month_endDate' and ospos_renewal.status = '1' GROUP by ospos_leads_product.product_category");
$fy_dropped_rslt = $fy_dropped_qry->result();
$fy_life_drop = 0;
$fy_health_drop = 0;
foreach ($fy_dropped_rslt as $key => $value) {
$category = $value->category;
$netamount = $value->netamount;
if($category === "2"){
$fy_life_drop = number_format($netamount);
}
if($category === "3"){
$fy_health_drop = number_format($netamount);
}
}
$life_drop_per = round(($life_drop / $life_collectable) * 100);
$health_drop_per = round(($health_drop / $health_collectable) * 100);
$fy_life_drop_per = round(($fy_life_drop / $life_collectable_ytd) * 100);
$fy_health_drop_per = round(($fy_health_drop / $health_collectable_ytd) * 100);
echo "<div class='col-md-12 pd0 dash_header'>
<h3 class='text-center headtxt'>MIS Dashboard</h3>
<div class='row mg0 mgb15'>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Total Issued /Life WFP Logged'>
<div class='dashbox orange'>
<table>
<tr>
<td class='pd10'><i class='fa fa-heartbeat fa-2x' aria-hidden='true'></i></td>
<td class='white'>
<h5>Life Logged</h5>
<span class='bold'>$life_log</span>
<div style='position: absolute; top: 5px; right: 25px; font-weight: bold;font-size: 11px;'>$life_issued</div>
</td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Total Issued /Life WFP Logged'>
<div class='dashbox red'>
<table>
<tr>
<td class='pd10'><i class='fa fa-user-md fa-2x' aria-hidden='true'></i></td>
<td class='white'>
<h5>Health Logged</h5>
<span class='bold'>$health_log</span>
<div style='position: absolute; top: 5px; right: 25px; font-weight: bold;font-size: 11px;'>$health_issued</div>
</td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Total SIP This Month /Lumpsum'>
<div class='dashbox blue'>
<table>
<tr>
<td class='pd10'><i class='fa fa-tags fa-2x' aria-hidden='true'></i></td>
<td class='white'>
<h5>SIP - Lumpsum</h5>
<span class='bold'> $sip_amt - $lumpsum_amt</span>
</td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Total Issued /Life WFP Logged'>
<div class='dashbox violet'>
<table>
<tr>
<td class='pd10'><i class='fa fa-repeat fa-2x' aria-hidden='true'></i></td>
<td class='white'>
<h5>Term Plan</h5>
<span class='bold'> $term_log</span>
</td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Avg Met'>
<div class='dashbox blue_gray'>
<table>
<tr>
<td class='pd10'><i class='fa fa-thermometer-half fa-2x' aria-hidden='true'></i></td>
<td class='white'>
<h5>Under Processing...</h5>
<span class='bold'> $rm_avg_met </span>
</td>
</tr>
</table>
</div>
</div>
<div class='col-md-2 pdl0' data-toggle='tooltip' data-placement='right' title='Days to go'>
<div class='dashbox teal'>
<table>
<tr>
<td class='pd10'><i class='fa fa-hourglass-half fa-2x' aria-hidden='true'></i></td>
<td class='white'>
<h5>Days</h5>
<span class='bold'> $DaysRemaining</span>
</td>
</tr>
</table>
</div>
</div>
</div>
</div>
<div class='col-md-12 pd0 divider' style='margin-bottom: -12px;'></div>
<div class='col-md-12 pd10' style='margin-bottom: -8px;'>
<div class='form-group' style='margin-bottom:4px;'>
<select id='mis_category' class='form-control input-sm' onchange='get_mis_category()'>
<option value=''>--- Select Category ---</option>
<option value='2' selected>Portfolio</option>
<option value='3'>Health</option>
<option value='4'>Term Plan</option>
</select>
</div>
<div class='form-group' style='margin-bottom:4px;'>
<select id='mis_date' class='form-control input-sm' onchange='get_mis_data()'>
<option value=''>--- Select Type ---</option>
<option value='M' selected>Month</option>
<option value='FY'>FY</option>
<option value='LTD'>LTD</option>
</select>
</div>
</div>
<div class='col-md-4 pd0 col-style' style='margin-left: 12px; margin-right: -18px; padding-right: 5px !important;'>
<div id='mis_life_chart' style='height:220px;'> </div>
</div>
<div class='col-md-4 pd0 col-style' style='padding-left: 5px !important; margin-left: 21px;'>
<div id='issuance_info_table' style ='padding-top: 7px;'></div>
<div id='document_info_table' style ='padding-top: 6px;'></div>
<div id='app_info_table' style ='padding-top: 6px;'></div>
</div>
<div class='col-md-4 pd0 col-style' style=' margin-left: 3px;width: 411px;'>
<div id='issuance_chart' style='height:220px;' ></div>
</div>
<div class='col-md-12 pd10' style='margin-bottom: -8px;'>
<div class='form-group' style='margin-bottom:4px;'>
<select id='mis_rm_category' class='form-control input-sm'>
<option value=''>--- Select Category ---</option>
<option value='2' selected>Portfolio</option>
<option value='3'>Health</option>
</select>
</div>
<div class='form-group' style='margin-bottom:4px;'>
<input type='text' id='start_date' value='$first' class='datepicker'/>
</div>
<div class='form-group' style='margin-bottom:4px;'>
<input type='text' id='end_date' value='$last' class='datepicker'/>
</div>
<div class='form-group' style='margin-bottom:4px;'>
<button type='submit' id='search' style = ''>search</button>
</div>
</div>
<div class='col-md-6 pd10 col-style' id='business_info_table' style='margin-bottom: -8px;height: 304px;overflow: auto;'>
</div>
<div class='col-md-6 pd10 col-style' style='margin-bottom: -8px;'>
<div id='mis_rm_chart' style='height:284px;'>
</div>
</div>
<div class='col-md-12 dash_header' style='margin-top: 10px;'>
<h3 class='text-center headtxt'>Renewal Dashboard</h3>
<div class='row mg0 mgb15'>
<div class='col-md-3 pdl0' data-toggle='tooltip' data-placement='right' title='This Month Life Collectable - Collected - Collected %'>
<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'>$life_collectable - $life_collected - $life_m_collected_per %</span>
<div style='position: absolute; top: 5px; right: 25px; font-weight: bold;font-size: 11px;'>FY - $life_collected_per %</div>
</td>
</tr>
</table>
</div>
</div>
<div class='col-md-3 pdl0' data-toggle='tooltip' data-placement='right' title='This Month Health Collectable - Collected - Collected %'>
<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'>$health_collectable - $health_collected - $health_m_collected_per %</span>
<div style='position: absolute; top: 5px; right: 25px; font-weight: bold;font-size: 11px;'>FY - $health_collected_per %</div>
</td>
</tr>
</table>
</div>
</div>
<div class='col-md-3 pdl0' data-toggle='tooltip' data-placement='right' title='This Month Life Collectable - Dropped - Dropped %'>
<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>Life Dropped</h5>
<span class='bold'>$life_collectable - $life_drop - $life_drop_per %</span>
<div style='position: absolute; top: 5px; right: 25px; font-weight: bold;font-size: 11px;'>FY - $fy_life_drop_per %</div>
</td>
</tr>
</table>
</div>
</div>
<div class='col-md-3 pdl0' data-toggle='tooltip' data-placement='right' title='Health Collectable - Dropped - Dropped %'>
<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>Health Dropped</h5>
<span class='bold'>$health_collectable - $health_drop - $health_drop_per %</span>
<div style='position: absolute; top: 5px; right: 25px; font-weight: bold;font-size: 11px;'>FY - $fy_health_drop_per %</div>
</td>
</tr>
</table>
</div>
</div>
</div>
</div>
<div class='col-md-12 pd10' style='margin-bottom: -8px;'>
<div class='form-group' style='margin-bottom:4px;'>
<select id='renew_category' class='form-control input-sm'>
<option value=''>--- Select Category ---</option>
<option value='2' selected>Portfolio</option>
<option value='3'>Health</option>
</select>
</div>
<div class='form-group' style='margin-bottom:4px;'>
<input type='text' id='renew_start_date' value='$first' class='datepicker'/>
</div>
<div class='form-group' style='margin-bottom:4px;'>
<input type='text' id='renew_end_date' value='$last' class='datepicker'/>
</div>
<div class='form-group' style='margin-bottom:4px;'>
<button type='submit' id='renew_search' style = ''>search</button>
</div>
</div>
<div class='col-md-12' style='margin-top: 10px;'>
<div class='col-md-6 pd10 col-style' style='margin-bottom: -8px;height: 304px;overflow: auto;'>
<div id='renew_info_table'></div>
<div id='client_status_table'></div>
</div>
<div class='col-md-6 pd10 col-style' style='margin-bottom: -8px;'>
<div id='renew_chart' style='height:284px;'>
</div>
</div>
</div>";
?>