<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
ob_start();
class Health_claim_report extends Action_controller{	
	public function __construct(){
		parent::__construct('health_claim_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;
		}
	}
}
ob_end_flush();
?>