File: /home/cafsindia/login_cafsindia_com/application/controllers/Claim_health_report.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Claim_health_report extends Action_controller{
public function __construct(){
parent::__construct('claim_health_report');
$this->collect_base_info();
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$this->load->view("$this->control_name/manage",$data);
}
public function get_view_data(){
$start_date = $this->input->post('start_date');
$end_date = $this->input->post('end_date');
$overall_table = $this->get_overall_table($start_date,$end_date);
$company_table = $this->get_company_table($start_date,$end_date);
$status_table = $this->get_status_table($start_date,$end_date);
echo json_encode(array('success' => TRUE,'overall_table' => $overall_table , 'company_table' => $company_table , 'status_table' => $status_table));
}
# OVERALL TABLE
public function get_overall_table($start_date,$end_date){
# TOTAL CLAIM
$total_qry = 'SELECT COUNT(*) AS total_claim_count FROM cw_health_claim WHERE policy_from BETWEEN "'.$start_date.'" AND "'.$end_date.'" AND trans_status = 1 ';
$total_info = $this->db->query("CALL sp_a_run ('SELECT','$total_qry')");
$total_rslt = $total_info->result();
$total_info->next_result();
$tot_claim_count = $total_rslt[0]->total_claim_count;
# PAID CLAIM
$paid_qry = 'SELECT COUNT(*) AS paid_count,SUM(paid_amount) AS paid_amount FROM cw_health_claim WHERE policy_from BETWEEN "'.$start_date.'" AND "'.$end_date.'" AND paid_status = 1 AND trans_status = 1';
$paid_info = $this->db->query("CALL sp_a_run ('SELECT','$paid_qry')");
$paid_rslt = $paid_info->result();
$paid_info->next_result();
$tot_paid_count = $paid_rslt[0]->paid_count;
$paid_amount = $paid_rslt[0]->paid_amount;
$paid_per = $this->get_percentage($tot_paid_count,$tot_claim_count);
# PENDING
$pending_qry = 'SELECT COUNT(*) AS pending_count FROM cw_health_claim WHERE policy_from BETWEEN "'.$start_date.'" AND "'.$end_date.'" AND paid_status NOT IN (1,2,5) AND trans_status = 1';
$pending_info = $this->db->query("CALL sp_a_run ('SELECT','$pending_qry')");
$pending_rslt = $pending_info->result();
$pending_info->next_result();
$pending_count = $pending_rslt[0]->pending_count;
$pending_per = $this->get_percentage($pending_count,$tot_claim_count);
# DROP
$drop_qry = 'SELECT COUNT(*) AS dropped_count FROM cw_health_claim WHERE policy_from BETWEEN "'.$start_date.'" AND "'.$end_date.'" AND paid_status IN (2,5) AND trans_status = 1';
$drop_info = $this->db->query("CALL sp_a_run ('SELECT','$drop_qry')");
$drop_rslt = $drop_info->result();
$drop_info->next_result();
$dropped_count = $drop_rslt[0]->dropped_count;
$dropped_per = $this->get_percentage($dropped_count,$tot_claim_count);
$overall_head = "<thead><tr><th colspan=2 style='border-right: 1px solid white;'>Total Claim</th><th colspan=3 style='border-right: 1px solid white;'>Paid</th><th colspan=2 style='border-right: 1px solid white;'>Pending</th><th colspan=2 style='border-right: 1px solid white;'>Dropped</th></tr><tr><th>Count</th><th style='border-right: 1px solid white;'>Percentage</th><th>Count</th><th>Amount</th><th style='border-right: 1px solid white;'>Percentage</th><th>Count</th><th style='border-right: 1px solid white;'>Percentage</th><th>Count</th><th>Percentage</th></tr></thead>";
$overall_body = "<tbody><tr><td>$tot_claim_count</td><td>100%</td><td>$tot_paid_count</td><td>$paid_amount</td><td>$paid_per</td><td>$pending_count</td><td>$pending_per</td><td>$dropped_count</td><td>$dropped_per</td></tr></tbody>";
if($tot_claim_count <= 0){
$overall_body = "<tr><td colspan=9>No Data Available</td></tr>";
}
return $overall_head.$overall_body;
}
# COMPANY TABLE
public function get_company_table($start_date,$end_date){
# TOTAL CLAIM COMPANY WISE
$total_qry = 'SELECT cw_health_company.company_name,COUNT(*) AS total_claim_count FROM cw_health_claim INNER JOIN cw_health_company ON cw_health_claim.company_name = cw_health_company.prime_health_company_id WHERE policy_from BETWEEN "'.$start_date.'" AND "'.$end_date.'" AND cw_health_claim.trans_status = 1 GROUP BY cw_health_claim.company_name';
$total_info = $this->db->query("CALL sp_a_run ('SELECT','$total_qry')");
$total_rslt = $total_info->result();
$total_info->next_result();
$company_table = '';
# PAID CLAIM COMPANY WISE
$paid_qry = 'SELECT cw_health_company.company_name as paid_company,COUNT(*) AS paid_count,sum(paid_amount) as paid_amount FROM cw_health_claim INNER JOIN cw_health_company ON cw_health_claim.company_name = cw_health_company.prime_health_company_id WHERE policy_from BETWEEN "'.$start_date.'" AND "'.$end_date.'" AND cw_health_claim.trans_status = 1 AND paid_status = 1 GROUP BY cw_health_claim.company_name';
$paid_info = $this->db->query("CALL sp_a_run ('SELECT','$paid_qry')");
$paid_rslt = $paid_info->result();
$paid_info->next_result();
# PENDING CLAIM COMPANY WISE
$pending_qry = 'SELECT cw_health_company.company_name as pend_company,COUNT(*) AS pending_count FROM cw_health_claim INNER JOIN cw_health_company ON cw_health_claim.company_name = cw_health_company.prime_health_company_id WHERE policy_from BETWEEN "'.$start_date.'" AND "'.$end_date.'" AND cw_health_claim.trans_status = 1 AND paid_status NOT IN (1,2,5) AND cw_health_claim.trans_status = 1 GROUP BY cw_health_claim.company_name';
$pending_info = $this->db->query("CALL sp_a_run ('SELECT','$pending_qry')");
$pending_rslt = $pending_info->result();
$pending_info->next_result();
# DROP
$drop_qry = 'SELECT cw_health_company.company_name as drop_company,COUNT(*) AS drop_count FROM cw_health_claim INNER JOIN cw_health_company ON cw_health_claim.company_name = cw_health_company.prime_health_company_id WHERE policy_from BETWEEN "'.$start_date.'" AND "'.$end_date.'" AND cw_health_claim.trans_status = 1 AND paid_status IN (2,5) AND cw_health_claim.trans_status = 1 GROUP BY cw_health_claim.company_name';
$drop_info = $this->db->query("CALL sp_a_run ('SELECT','$drop_qry')");
$drop_rslt = $drop_info->result();
$drop_info->next_result();
$table_arr = array('total' => $total_rslt, 'paid' => $paid_rslt, 'pending' => $pending_rslt, 'drop' => $drop_rslt);
$create_table = $this->create_company_table($table_arr);
$company_table = "<table>$create_table</table>";
return $company_table;
}
# COMPANY WISE TABLE CREATION
public function create_company_table($table_arr){
$thead = "<thead><tr><th rowspan=2>Company</th><th colspan=2>Total Claim</th><th colspan=3>Paid</th><th colspan=2>Pending</th><th colspan=2>Dropped</th></tr><tr><th>Count</th><th>Percentage</th><th>Count</th><th>Amount</th><th>Percentage</th><th>Count</th><th>Percentage</th><th>Count</th><th>Percentage</th></tr></thead>";
$tbody = '<tbody>';
for($i = 0 ; $i < count($table_arr['total']) ; $i++){
$company_name = $table_arr['total'][$i]->company_name ?? 0;
$total_claim_count = $table_arr['total'][$i]->total_claim_count ?? 0;
$paid_count = $table_arr['paid'][$i]->paid_count ?? 0;
$paid_amount = $table_arr['paid'][$i]->paid_amount ?? 0;
$pending_count = $table_arr['pending'][$i]->pending_count ?? 0;
$drop_count = $table_arr['drop'][$i]->drop_count ?? 0;
$tbody .= '<tr>';
$tbody .= '<td>'.$company_name.'</td>';
$tbody .= '<td>'.$total_claim_count.'</td>';
$tbody .= '<td>'."100%".'</td>';
$tbody .= '<td>'.$paid_count.'</td>';
$tbody .= '<td>'.$paid_amount.'</td>';
$tbody .= '<td>'.$this->get_percentage($paid_count,$total_claim_count).'</td>';
$tbody .= '<td>'.$pending_count.'</td>';
$tbody .= '<td>'.$this->get_percentage($pending_count,$total_claim_count).'</td>';
$tbody .= '<td>'.$drop_count.'</td>';
$tbody .= '<td>'.$this->get_percentage($drop_count,$total_claim_count).'</td>';
$tbody .= '</tr>';
}
$tbody .= "</tbody>";
if(!count($table_arr['total'])){
$tbody = "<tr><td colspan=10>No Data Available</td></tr>";
}
return $thead.$tbody;
}
# STATUS WISE TABLE
public function get_status_table($start_date,$end_date){
# GET COMPANY
$comp_qry = 'SELECT company_name FROM cw_health_company WHERE trans_status = 1 GROUP BY prime_health_company_id';
$comp_info = $this->db->query("CALL sp_a_run ('SELECT','$comp_qry')");
$comp_rslt = $comp_info->result();
$comp_info->next_result();
# GET PAID STATUS
$paid_status_qry = 'SELECT paid_status FROM cw_paid_status WHERE trans_status = 1';
$paid_status_info = $this->db->query("CALL sp_a_run ('SELECT','$paid_status_qry')");
$paid_status_rslt = $paid_status_info->result();
$paid_status_info->next_result();
# GET DATA
$get_data_qry = 'SELECT cw_paid_status.paid_status,COUNT(*) as count,cw_health_company.company_name,SUM(COUNT(*)) OVER(PARTITION BY paid_status ) AS total_count FROM cw_health_claim INNER JOIN cw_paid_status ON cw_paid_status.prime_paid_status_id = cw_health_claim.paid_status INNER JOIN cw_health_company ON cw_health_claim.company_name = cw_health_company.prime_health_company_id WHERE policy_from BETWEEN "'.$start_date.'" AND "'.$end_date.'" AND cw_health_claim.trans_status = 1 GROUP BY cw_health_claim.paid_status,cw_health_claim.company_name';
$get_data_info = $this->db->query("CALL sp_a_run ('SELECT','$get_data_qry')");
$get_data_rslt = $get_data_info->result();
$get_data_info->next_result();
$trail_body = "<tbody>";
$thead = "<thead><tr><th>Company</th>";
$thead2 = "<tr><th>Claim Status</th>";
$head_flag = "create";
foreach($paid_status_rslt as $key => $val){
$trail_body .= "<tr><td>$val->paid_status</td>";
$td_count = 0;
foreach($comp_rslt as $comp => $comp_name){
if($head_flag === 'create'){
$thead .= "<th colspan = 2>$comp_name->company_name</th>";
$thead2 .= "<th>Count</th><th>Percentage</th>";
}
foreach($get_data_rslt as $k => $v){
if($v->paid_status === $val->paid_status && $v->company_name === $comp_name->company_name){
$per = $this->get_percentage($v->count,$v->total_count);
$trail_body .= "<td>$v->count</td><td>$per</td>";
$td_count++;
}
}
}
for($i = $td_count ; $i < count($comp_rslt) ; $i++){
$trail_body .= "<td>0</td><td>0%</td>";
}
$head_flag = "don't";
$trail_body .= "</tr>";
}
if(count($get_data_rslt) <= 0){
$colspan_count = (count($comp_rslt) * 2 ) + 1;
$trail_body = "<tr><td colspan= ".$colspan_count.">No Data Available</td></tr>";
}
$trail_body .= "</tbody>";
$thead .= "</tr>$thead2</thead>";
return "$thead $trail_body";
}
# GET PERCENTAGE
public function get_percentage($value,$total_value){
$per = (int)$value /(int)$total_value * (int)100 ;
if((int)$per >= 0){
return round($per) .'%';
}else{
return 0;
}
}
}
?>