<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Renewals_business_report  extends Action_controller{	
	public function __construct(){
		parent::__construct('renewals_business_report');
		$this->collect_base_info();
	}
	// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
	public function index(){
		$data['quick_link']     = $this->quick_link;
		$data['table_head']     = $this->table_head;
		$data['master_pick']    = $this->master_pick;
		$data['fliter_list']    = $this->fliter_list;
		$data['life_company']   = $this->get_life_company();
		$data['health_company'] = $this->get_health_company();
		$this->load->view("$this->control_name/manage",$data);
	}
	//FUNCTION FOR GET A RENEWAL BUSINESS DETAILS
	public function renewals_business_sts(){
		$start_date         = date('Y-m-d', strtotime($this->input->post('start_date')));
		$end_date           = date('Y-m-d', strtotime($this->input->post('end_date')));
		$l_company          = $this->input->post('life_company');
		$h_company          = $this->input->post('health_company');
		$month_count        = $this->input->post('month_count');
		$py_count           = $this->input->post('py_count');
		if((int)$l_company > 0){ // FOR LIFE COMPANY
			$l_company_qry  = ' AND cw_life_renewals.product_company_name = '.$l_company;
		}
		if((int)$h_company > 0){ // FOR HEALTH COMPANY
			$h_company_qry  = ' AND cw_health_renewals.company = '.$h_company;
		}	
		if((int)$month_count > 0){ // FOR HEALTH MONTH COUNT
			$h_company_qry .= ' AND month_count = '.$month_count;
		}
		if((int)$py_count > 0){ // FOR LIFE PY COUNT _ARN
			$l_company_qry .= ' AND cw_life_renewals.py_count = '.$py_count;
		}
		if($start_date && $end_date){
			//LIFE RENEWED PREMIUM GET QUERY FROM LIFE RENEWALS LOG QRY
			
			//RENEWED PREMIUM GET QUERY FROM LIFE RENEWALS LOG QRY 
			$life_renewed_premium_qry  	 =	'select ROUND(SUM(cw_life_renewals_log.renewal_premium_amount),2) AS renewed_premium from cw_life_renewals_log LEFT JOIN cw_life_renewals on cw_life_renewals_log.prime_life_renewals_id = cw_life_renewals.prime_life_renewals_id where cw_life_renewals_log.renewal_due_date BETWEEN "'.$start_date.'" AND "'.$end_date.'" and cw_life_renewals_log.trans_status = 1 and cw_life_renewals_log.from_portfolio != 1 and cw_life_renewals_log.trans_created_by != 1 '.$l_company_qry.'';
			$life_renewed_premium_info 	 = 	$this->db->query("CALL sp_a_run ('SELECT','$life_renewed_premium_qry')");
			$life_renewed_premium_rslt 	 = 	$life_renewed_premium_info->result();
			$life_renewed_premium_info->next_result();
			$life_renewed_premium     	 = 	$life_renewed_premium_rslt[0]->renewed_premium;

			//PENDING PREMIUM GET QUERY FROM LIFE RENEWALS QRY 
			$life_pending_premium_qry  	 =	'select ROUND(SUM(cw_life_renewals.net_premium),2) AS pending_premium from cw_life_renewals where cw_life_renewals.due_date BETWEEN "'.$start_date.'" AND "'.$end_date.'" AND cw_life_renewals.trans_status = 1 '.$l_company_qry.'';

			$life_pending_premium_info 	 = 	$this->db->query("CALL sp_a_run ('SELECT','$life_pending_premium_qry')");
			$life_pending_premium_rslt 	 = 	$life_pending_premium_info->result();
			$life_pending_premium_info->next_result();

			$life_pending_premium        =	$life_pending_premium_rslt[0]->pending_premium;
			$total_life_premium          = 	$life_renewed_premium + $life_pending_premium;
			$life_premium_per            = 	round(($total_life_premium - $life_pending_premium) * 100 / $total_life_premium) . "%";
			if($total_life_premium){
				$life_renewal_arr        = 	array(array("total_premium" => $total_life_premium, "renewed_premium" => $life_renewed_premium, "pending_premium" => $life_pending_premium, "premium_percentage" => $life_premium_per));
			}

			//HEALTH RENEWED PREMIUM GET QUERY FROM HEALTH RENEWALS LOG QRY
			$health_renewed_premium_qry  = 	'select ROUND(SUM(cw_health_renewals_log.renewal_net_premium),2) AS renewed_premium, count(*) as premium_count_policy from cw_health_renewals_log LEFT JOIN cw_health_renewals ON cw_health_renewals_log.prime_health_renewals_id = cw_health_renewals.prime_health_renewals_id where cw_health_renewals_log.trans_status = 1 and cw_health_renewals_log.renewal_from_date BETWEEN "'.$start_date.'" AND "'.$end_date.'" and cw_health_renewals_log.from_health != 1 and cw_health_renewals_log.trans_created_by != 1 '.$h_company_qry.'';

			$health_renewed_premium_info = 	$this->db->query("CALL sp_a_run ('SELECT','$health_renewed_premium_qry')");
			$health_renewed_premium_rslt = 	$health_renewed_premium_info->result();
			$health_renewed_premium_info->next_result();
			$health_renewed_premium      = round($health_renewed_premium_rslt[0]->renewed_premium,2);
			$premium_count_policy 		 =  $health_renewed_premium_rslt[0]->premium_count_policy;

			//health_pending_query
			$health_pending_premium_qry  = 'select ROUND(SUM(cw_health_renewals.net_premium),2) AS pending_premium,count(*) AS health_pending_policy from cw_health_renewals where cw_health_renewals.trans_status = 1 and cw_health_renewals.due_date BETWEEN "'.$start_date.'" AND "'.$end_date.'" '.$h_company_qry.'';
			$health_pending_premium_info = $this->db->query("CALL sp_a_run ('SELECT','$health_pending_premium_qry')");
			$health_pending_premium_rslt = $health_pending_premium_info->result();
			$health_pending_premium_info->next_result();

			$health_pending_premium      = $health_pending_premium_rslt[0]->pending_premium;
			$total_health_premium        = round($health_renewed_premium + $health_pending_premium,2);
			$health_premium_per          = round(($total_health_premium - $health_pending_premium) * 100 / $total_health_premium) . "%";
			$health_pend_perc   		 = round(($total_health_premium -$health_renewed_premium) * 100/ $total_health_premium) . "%";
			
			//Body data END
			if($total_health_premium){
				$health_renewal_arr      = array(array("total_premium" => $total_health_premium, "renewed_premium" => $health_renewed_premium, "pending_premium" => $health_pending_premium, "premium_percentage" => $health_premium_per));
			}
			echo json_encode(array('success' => TRUE,'life_renewal_report' => $life_renewal_arr,'health_renewal_report' => $health_renewal_arr));
		}
	}
	
	// RENEWED DETAILS REPORT
	public function renewed_details(){
		$start_date          = date('Y-m-d', strtotime($this->input->post('start_date')));
		$end_date            = date('Y-m-d', strtotime($this->input->post('end_date')));
		$l_company           = $this->input->post('life_company');
		$h_company           = $this->input->post('health_company');
		$month_count         = $this->input->post('month_count');
		$py_count            = $this->input->post('py_count');

		if((int)$l_company > 0){ // FOR LIFE COMPANY
			$l_company_qry   = ' AND cw_life_renewals.product_company_name = '.$l_company;
		}
		if((int)$h_company > 0){ // FOR HEALTH COMPANY
			$h_company_qry = ' AND cw_health_renewals.company = '.$h_company;
		}
		if((int)$month_count > 0){
			$h_company_qry .= ' AND month_count = '.$month_count;
		}
		if((int)$py_count > 0){ //GET PY_COUNT ADD WHERE IN LIFE _ARN
			$l_company_qry .= ' AND cw_life_renewals.py_count = '.$py_count;
		}
		if($start_date && $end_date){
			// LIFE RENEWED DATA
			$life_qry='SELECT cw_company_name.company_name,cw_life_renewals.product_company_name,COUNT(*) AS total_renewals,ROUND(SUM(cw_life_renewals_log.renewal_premium_amount), 2) AS renewed_premium FROM cw_life_renewals INNER JOIN cw_company_name ON cw_company_name.prime_company_name_id = cw_life_renewals.product_company_name INNER JOIN cw_life_renewals_log ON cw_life_renewals.prime_life_renewals_id = cw_life_renewals_log.prime_life_renewals_id WHERE cw_life_renewals_log.renewed_date BETWEEN "'.$start_date.'" AND "'.$end_date.'" and cw_life_renewals_log.trans_status = 1 and cw_life_renewals_log.from_portfolio != 1 and cw_life_renewals_log.trans_created_by != 1 AND cw_life_renewals.trans_status = 1 '.$l_company_qry.' GROUP BY cw_company_name.company_name';
	
			$life_qry_info=$this->db->query("CALL sp_a_run ('SELECT','$life_qry')");
			$life_renewed_premium_rslt = $life_qry_info->result_array();    
			$life_qry_info->next_result();
	
			// HEALTH RENEWED DATA DETAILS
			$health_qry='SELECT cw_health_company.company_name,cw_health_renewals.company,COUNT(*) AS total_renewals,ROUND(SUM(cw_health_renewals_log.renewal_net_premium), 2) AS renewed_premium FROM cw_health_renewals INNER JOIN cw_health_company ON cw_health_company.prime_health_company_id = cw_health_renewals.company INNER JOIN cw_health_renewals_log ON cw_health_renewals.prime_health_renewals_id = cw_health_renewals_log.prime_health_renewals_id WHERE cw_health_renewals_log.renewed_date BETWEEN "'.$start_date.'" AND "'.$end_date.'" And cw_health_renewals_log.trans_status = 1 and cw_health_renewals_log.from_health != 1 and cw_health_renewals_log.trans_created_by != 1  '.$h_company_qry.' GROUP BY cw_health_company.company_name';
			$health_qry_info             = $this->db->query("CALL sp_a_run ('SELECT','$health_qry')");
			$health_renewed_premium_rslt = $health_qry_info->result_array();    
			$health_qry_info->next_result();

			// HEALTH RENWED DATA DETAILS WILL BASED ON POLICY FROM DATE _ARN 18-10-2025
			$health__frm_qry='SELECT cw_health_company.company_name,cw_health_renewals.company,COUNT(*) AS total_renewals,ROUND(SUM(cw_health_renewals_log.renewal_net_premium), 2) AS renewed_premium FROM cw_health_renewals INNER JOIN cw_health_company ON cw_health_company.prime_health_company_id = cw_health_renewals.company INNER JOIN cw_health_renewals_log ON cw_health_renewals.prime_health_renewals_id = cw_health_renewals_log.prime_health_renewals_id WHERE cw_health_renewals_log.renewal_from_date BETWEEN "'.$start_date.'" AND "'.$end_date.'" And cw_health_renewals_log.trans_status = 1 and cw_health_renewals_log.from_health != 1 and cw_health_renewals_log.trans_created_by != 1  '.$h_company_qry.' GROUP BY cw_health_company.company_name';
			
			$health_frm_qry_info             = $this->db->query("CALL sp_a_run ('SELECT','$health__frm_qry')");
			$health_renewed_frm_premium_rslt = $health_frm_qry_info->result_array();    
			$health_frm_qry_info->next_result();
	
			$life_tr_line = "";
			$health_tr_line = "";
	
			foreach($life_renewed_premium_rslt as $value){
				$company_name = $value['company_name'];
				$total_renewals = $value['total_renewals'];
				$renewed_premium = $value['renewed_premium'];
				$company_code = $value['product_company_name'];
				$life_tr_line .= "<tr><td style='text-align:center'>$company_name</td><td style='text-align:center; color:blue;' id='mydata'   onclick=renewed_popup('".$start_date."','".$end_date."',$company_code,'life')>$renewed_premium</td><td style='text-align:center; color:blue;'    onclick=renewed_popup('".$start_date."','".$end_date."',$company_code,'life')>$total_renewals</td></tr>";
			}
	
			foreach($health_renewed_premium_rslt as $value){
				$company_name = $value['company_name'];
				$total_renewals = $value['total_renewals'];
				$renewed_premium = $value['renewed_premium'];
				$company_code = $value['company'];
				$health_tr_line .= "<tr><td style='text-align:center'>$company_name</td><td style='text-align:center; color:blue;' id='mydata'  onclick=renewed_popup('".$start_date."','".$end_date."',$company_code,'health')>$renewed_premium</td><td style='text-align:center; color:blue;'    onclick=renewed_popup('".$start_date."','".$end_date."',$company_code,'health')>$total_renewals</td></tr>";
			}

			// NEW RENEWED DATA DETAILS BASED ON _ARN 18-10-2025
			foreach($health_renewed_frm_premium_rslt as $value){
				$company_name      = $value['company_name'];
				$total_renewals    = $value['total_renewals'];
				$renewed_premium   = $value['renewed_premium'];
				$company_code      = $value['company'];
				$health_frm_tr_line    .= "<tr><td style='text-align:center'>$company_name</td><td style='text-align:center; color:blue;' id='mydata'  onclick=renewed_popup('".$start_date."','".$end_date."',$company_code,'health_frm')>$renewed_premium</td><td style='text-align:center; color:blue;'    onclick=renewed_popup('".$start_date."','".$end_date."',$company_code,'health_frm')>$total_renewals</td></tr>";
			}


			$thead="<thead>
						<tr >
							<th style='text-align:center'>Company Name</th>
							<th style='text-align:center'>Renewed Premium</th>
							<th style='text-align:center'>Total Renewals</th>
						</tr>
					</thead>";			
			if($life_tr_line){
				$life_table_content = "<h3>Life Renewed Report</h3>
				<table class='table table-striped table-bordered' id='life_table'>
					$thead
					<tbody>
						$life_tr_line
					</tbody>
				</table>";
			}else{
				$life_table_content = "<h3>Life Renewed Report</h3>
				<table class='table table-striped table-bordered' id='life_table'>
					$thead
					<tbody>
						<tr><td></td><td style='text-align:center'>No Data Available</td><td></td></tr>
					</tbody>
				</table>";
			}
			if($health_tr_line){ // THIS CONDITION FOR HEALTH RENEWED DATA BY [MS 22-01-2025]
				$health_table_content = "<h3>Health Renewed Report</h3>
				<h4>Renewed Date Based</h4>
				<table class='table table-striped table-bordered' id='health_table'>
					$thead
					<tbody>
						$health_tr_line
					</tbody>
				</table>
				<h4>From Date Based</h4>
				<table class='table table-striped table-bordered' id='health_frm_table'>
					$thead
					<tbody>
						$health_frm_tr_line
					</tbody>
				</table>";
			}else{
				$health_table_content = "<h3>Health Renewed Report</h3>
				<h4>Renewed Date Based</h4>
				<table class='table table-striped table-bordered' id='health_table'>
					$thead
					<tbody>
						<tr><td></td><td style='text-align:center'>No Data Available</td><td></td></tr>
					</tbody>
				</table>
				<h4>From Date Based</h4>
				<table class='table table-striped table-bordered' id='health_frm_table'>
					$thead
					<tbody>
						<tr><td></td><td style='text-align:center'>No Data Available</td><td></td></tr>
					</tbody>
				</table>
				";
			}
			echo json_encode(array('success' => TRUE, 'message' => "Data available", 'life_table_content' => $life_table_content, 'health_table_content' => $health_table_content));
		}
	}
	
	// POP UP RENEWED QUERY BOTH LIFE AND HEALTH
	public function show_popup_data(){	
		$status				 =	$this->input->post('status');
		$start_date     	 = 	$this->input->post('start_date');
		$end_date       	 = 	$this->input->post('end_date');
		$company_code 		 = 	$this->input->post('company_code');
		if($status === 'health'){
			$popup_qry	     = 'SELECT cw_health_renewals.proposer_name,cw_health_company.company_name,cw_health_product.product_name,cw_health_renewals_log.renewal_net_premium AS net_premium,cw_health_renewals.current_policy_no AS policy_number,cw_health_renewals_log.renewed_date,cw_health_renewals.contact_number,call_count FROM cw_health_renewals INNER JOIN cw_health_company ON cw_health_company.prime_health_company_id = cw_health_renewals.company INNER JOIN cw_health_renewals_log ON cw_health_renewals.prime_health_renewals_id = cw_health_renewals_log.prime_health_renewals_id INNER JOIN cw_health_product ON cw_health_product.prime_health_product_id = cw_health_renewals.product WHERE cw_health_renewals_log.renewed_date BETWEEN "'.$start_date.'" AND "'.$end_date.'" AND cw_health_renewals.company ="'.$company_code.'"And cw_health_renewals_log.trans_status = 1 and cw_health_renewals_log.from_health != 1 and cw_health_renewals_log.trans_created_by != 1';
		}else 
		if($status === 'health_frm'){
			$popup_qry	     = 'SELECT cw_health_renewals.proposer_name,cw_health_company.company_name,cw_health_product.product_name,cw_health_renewals_log.renewal_net_premium AS net_premium,cw_health_renewals.current_policy_no AS policy_number,cw_health_renewals_log.renewal_from_date,cw_health_renewals.contact_number,call_count FROM cw_health_renewals INNER JOIN cw_health_company ON cw_health_company.prime_health_company_id = cw_health_renewals.company INNER JOIN cw_health_renewals_log ON cw_health_renewals.prime_health_renewals_id = cw_health_renewals_log.prime_health_renewals_id INNER JOIN cw_health_product ON cw_health_product.prime_health_product_id = cw_health_renewals.product WHERE cw_health_renewals_log.renewal_from_date BETWEEN "'.$start_date.'" AND "'.$end_date.'" AND cw_health_renewals.company ="'.$company_code.'"And cw_health_renewals_log.trans_status = 1 and cw_health_renewals_log.from_health != 1 and cw_health_renewals_log.trans_created_by != 1';
		}

		if($status === 'life'){
			$popup_qry	     ='SELECT cw_life_renewals.client_name AS proposer_name,cw_company_name.company_name,cw_product_name.product_name,cw_life_renewals_log.renewal_premium_amount AS net_premium,cw_life_renewals. policy_number,cw_life_renewals_log.renewed_date FROM cw_life_renewals INNER JOIN cw_company_name ON cw_company_name.prime_company_name_id = cw_life_renewals.product_company_name INNER JOIN cw_life_renewals_log ON cw_life_renewals.prime_life_renewals_id = cw_life_renewals_log.prime_life_renewals_id INNER JOIN cw_product_name ON cw_product_name.prime_product_name_id = cw_life_renewals.product_name WHERE cw_life_renewals_log.renewed_date BETWEEN "'.$start_date.'" AND "'.$end_date.'" AND cw_life_renewals.product_company_name ="'.$company_code.'" AND cw_life_renewals.trans_status = 1 and cw_life_renewals_log.trans_status = 1 and cw_life_renewals_log.from_portfolio != 1 and cw_life_renewals_log.trans_created_by != 1';
		}
			// echo $popup_qry;die;
			//BOTH HEALTH AND LIFE DATAS CALLING TO DATABASE HERE
			$common_popup_info 	= 	$this->db->query("CALL sp_a_run ('SELECT','$popup_qry')");
			$common_popup_rslt 	= 	$common_popup_info->result_array();
			$common_popup_info->next_result();
			
			echo json_encode(array('success' => TRUE,'popup_data' => $common_popup_rslt));
	}
	//POP UP RENEWED QUERY BOTH LIFE AND HEALTH GROUP BY
	public function show_popup_data_gr(){	
		$status				 =	$this->input->post('status');
		$start_date     	 = 	$this->input->post('start_date');
		$end_date       	 = 	$this->input->post('end_date');
		$company_code 		 = 	$this->input->post('company_code');
		if($status === 'life'){
			$gr_qry = 'SELECT cw_life_renewals.client_name AS proposer_name,cw_company_name.company_name,cw_product_name.product_name,sum(cw_life_renewals_log.renewal_premium_amount) AS net_premium,cw_life_renewals. policy_number,cw_life_renewals_log.renewed_date FROM cw_life_renewals INNER JOIN cw_company_name ON cw_company_name.prime_company_name_id = cw_life_renewals.product_company_name INNER JOIN cw_life_renewals_log ON cw_life_renewals.prime_life_renewals_id = cw_life_renewals_log.prime_life_renewals_id INNER JOIN cw_product_name ON cw_product_name.prime_product_name_id = cw_life_renewals.product_name WHERE cw_life_renewals_log.renewed_date BETWEEN "'.$start_date.'" AND "'.$end_date.'" AND cw_life_renewals.product_company_name ="'.$company_code.'" AND cw_life_renewals.trans_status = 1 and cw_life_renewals_log.trans_status = 1 and cw_life_renewals_log.from_portfolio != 1 and cw_life_renewals_log.trans_created_by != 1 GROUP BY cw_product_name.product_name';
		}
		if($status === 'health'){
			$gr_qry = 'SELECT cw_health_renewals.proposer_name,cw_health_company.company_name,cw_health_product.product_name,sum(cw_health_renewals_log.renewal_net_premium) AS net_premium,cw_health_renewals.current_policy_no AS policy_number,cw_health_renewals_log.renewed_date,cw_health_renewals.contact_number,call_count FROM cw_health_renewals INNER JOIN cw_health_company ON cw_health_company.prime_health_company_id = cw_health_renewals.company INNER JOIN cw_health_renewals_log ON cw_health_renewals.prime_health_renewals_id = cw_health_renewals_log.prime_health_renewals_id INNER JOIN cw_health_product ON cw_health_product.prime_health_product_id = cw_health_renewals.product WHERE cw_health_renewals_log.renewed_date BETWEEN "'.$start_date.'" AND "'.$end_date.'" AND cw_health_renewals.company ="'.$company_code.'"And cw_health_renewals_log.trans_status = 1 and cw_health_renewals_log.from_health != 1 and cw_health_renewals_log.trans_created_by != 1 GROUP BY cw_health_product.product_name';
		}else 
		if($status === 'health_frm'){
			$gr_qry = 'SELECT cw_health_renewals.proposer_name,cw_health_company.company_name,cw_health_product.product_name,sum(cw_health_renewals_log.renewal_net_premium) AS net_premium,cw_health_renewals.current_policy_no AS policy_number,cw_health_renewals_log.renewal_from_date,cw_health_renewals.contact_number,call_count FROM cw_health_renewals INNER JOIN cw_health_company ON cw_health_company.prime_health_company_id = cw_health_renewals.company INNER JOIN cw_health_renewals_log ON cw_health_renewals.prime_health_renewals_id = cw_health_renewals_log.prime_health_renewals_id INNER JOIN cw_health_product ON cw_health_product.prime_health_product_id = cw_health_renewals.product WHERE cw_health_renewals_log.renewal_from_date BETWEEN "'.$start_date.'" AND "'.$end_date.'" AND cw_health_renewals.company ="'.$company_code.'"And cw_health_renewals_log.trans_status = 1 and cw_health_renewals_log.from_health != 1 and cw_health_renewals_log.trans_created_by != 1 GROUP BY cw_health_product.product_name';
		} 


		// echo $gr_qry; die;
		$gr_popup_info 	= 	$this->db->query("CALL sp_a_run ('SELECT','$gr_qry')");
		$gr_popup_rslt 	= 	$gr_popup_info->result_array();
		$gr_popup_info->next_result();
		echo json_encode(array('success' => TRUE,'gr_popup_data' => $gr_popup_rslt));
	}
	//TOTAL DETAILS POPUP
	public function show_total_details(){
		$name          = $this->input->post('name');
		$start_date    = date('Y-m-d', strtotime($this->input->post('start_date')));
		$end_date      = date('Y-m-d', strtotime($this->input->post('end_date')));
		$due_date      = 'BETWEEN "'.$start_date.'" AND "'.$end_date.'"';
		$select        = 'select cw_employees.login_code,cw_employees.employee_code,cw_employees.employee_name,cw_employees.level_1_reporting_person,cw_employees.level_2_reporting_person,';
		//Level one and Level two
		$level_qry                = 'SELECT cw_employees.employee_code, cw_employees.employee_name FROM cw_employees GROUP BY cw_employees.employee_code';
		$level_info               = $this->db->query($level_qry);
		$level_rslt               = $level_info->result_array();
		$level_info->next_result();
		foreach($level_rslt as $emp_code_arr){
			$level_arr[$emp_code_arr['employee_code']] = $emp_code_arr['employee_code']." - ".$emp_code_arr['employee_name'];
		}
		$l_company                = $this->input->post('life_company');
		$h_company                = $this->input->post('health_company');
		$month_count              = $this->input->post('month_count');
		$py_count                 = $this->input->post('py_count');

		if((int)$l_company > 0){ // FOR LIFE COMPANY
			$l_company_qry        = ' AND cw_life_renewals.product_company_name = '.$l_company;
		}
		if((int)$h_company > 0){ // FOR HEALTH COMPANY
			$h_company_qry        = ' AND cw_health_renewals.company = '.$h_company;
		}
		if((int)$month_count > 0){
			$h_company_qry       .= ' AND month_count = '.$month_count;
		}
		if((int)$py_count > 0){ //GET PY_COUNT ADD WHERE IN LIFE _ARN
			$l_company_qry       .= ' AND cw_life_renewals.py_count = '.$py_count;
		}

		if($name === "life"){
			//RENEWED PREMIUM GET QUERY FROM LIFE RENEWALS LOG QRY 
			$life_renewed_premium_qry  	= 	$select.'cw_life_renewals.client_name,cw_product_name.product_name,cw_life_renewals.due_date AS due_date,cw_company_name.company_name,ROUND(sum(cw_life_renewals_log.renewal_premium_amount),2) AS net_premium,cw_life_call_status.life_call_status as call_status,cw_life_renewal_status.renewal_status from cw_life_renewals_log inner join cw_employees on cw_employees.prime_employees_id = cw_life_renewals_log.trans_created_by inner join cw_life_renewals on cw_life_renewals.prime_life_renewals_id = cw_life_renewals_log.prime_life_renewals_id inner join cw_life_call_status on cw_life_call_status.prime_life_call_status_id = cw_life_renewals.call_status inner join cw_life_renewal_status on cw_life_renewal_status.prime_renewal_status_id = cw_life_renewals.renewal_status inner join cw_company_name on cw_company_name.prime_company_name_id = cw_life_renewals.product_company_name inner join cw_product_name on cw_product_name.prime_product_name_id = cw_life_renewals.product_name where cw_life_renewals_log.renewal_due_date '.$due_date.' and from_portfolio != 1 and cw_life_renewals_log.trans_created_by != 1 and cw_life_renewals_log.trans_status = 1 '.$l_company_qry.' GROUP BY cw_life_renewals_log.prime_life_renewals_id';
			$life_renewed_premium_info	=	$this->db->query($life_renewed_premium_qry);
			$life_renewed_premium_rslt 	= 	$life_renewed_premium_info->result_array();
			$life_renewed_premium_info->next_result();
			// echo $life_renewed_premium_qry;die;
			//pending
			$life_pending_premium_qry  = 'select cw_life_renewals.client_name,cw_company_name.company_name,cw_product_name.product_name,cw_life_mode.mode_value,cw_life_renewals.policy_number,DATE_FORMAT(STR_TO_DATE(cw_life_renewals.due_date,"%Y-%m-%d"),"%d-%m-%Y") as due_date,ROUND(cw_life_renewals.net_premium,2) AS net_premium,cw_life_call_status.life_call_status as call_status,cw_life_renewal_status.renewal_status,cw_employees.employee_code,cw_employees.employee_name,cw_employees.level_1_reporting_person,cw_employees.level_2_reporting_person from cw_life_renewals inner join cw_employees on cw_employees.prime_employees_id = cw_life_renewals.emp_id inner join cw_life_renewals_log on cw_life_renewals_log.prime_life_renewals_id = cw_life_renewals.prime_life_renewals_id inner join cw_company_name on cw_company_name.prime_company_name_id = cw_life_renewals.product_company_name inner join cw_life_mode on cw_life_mode.prime_life_mode_id = cw_life_renewals.mode inner join cw_product_name on cw_product_name.prime_product_name_id = cw_life_renewals.product_name inner join cw_life_call_status on cw_life_call_status.prime_life_call_status_id = cw_life_renewals.call_status inner join cw_life_renewal_status on cw_life_renewal_status.prime_renewal_status_id = cw_life_renewals.renewal_status where cw_life_renewals.due_date '.$due_date.' and cw_life_renewals.trans_status = 1 '.$l_company_qry.' GROUP BY cw_life_renewals.prime_life_renewals_id'; 
			$life_pending_premium_info = $this->db->query("CALL sp_a_run ('SELECT','$life_pending_premium_qry')");
			$life_pending_premium_rslt = $life_pending_premium_info->result_array();
			$life_pending_premium_info->next_result();	
			// echo $life_pending_premium_qry;die;
			$total_arr      = array_merge($life_pending_premium_rslt,$life_renewed_premium_rslt);
			$i = 0;
			foreach($total_arr as $val){
				$key_code_1 = $val['level_1_reporting_person'];
				$total_arr[$i]['level_1_reporting_person'] = $level_arr[$key_code_1];
				$key_code_2 = $val['level_2_reporting_person'];
				$total_arr[$i]['level_2_reporting_person'] = $level_arr[$key_code_2];
				$i = $i+1;
			}
		
			echo json_encode(array('success' => TRUE,'renewal_sts' => $total_arr));
		}else
		if($name === "health"){
			// health renewals Achieved report
			//achieved
			$health_renewed_premium_qry  = $select.'cw_health_renewals.proposer_name as client_name,cw_health_renewals.current_policy_no,cw_health_renewals.actual_from_date,cw_health_renewals.month_count,e.employee_name as log_upd_emp,cw_health_renewals.call_log_upd_date,cw_health_renewals.call_count,DATE_FORMAT(cw_health_renewals.due_date,"%d-%m-%Y") as due_date,cw_health_product.product_name,cw_health_company.company_name,ROUND(sum(cw_health_renewals_log.renewal_net_premium),2) AS net_premium,cw_health_call_status.health_call_status as call_status,cw_health_renewal_status.renewal_status,cw_health_renewals.ncd,cw_health_renewals.expected_date from cw_health_renewals_log  left join cw_health_renewals on cw_health_renewals.prime_health_renewals_id = cw_health_renewals_log.prime_health_renewals_id left join cw_employees on cw_employees.employee_code = cw_health_renewals.employee_code inner join cw_employees e on e.prime_employees_id  = cw_health_renewals.call_log_upd_by left join cw_health_call_status on cw_health_call_status.prime_health_call_status_id = cw_health_renewals.call_status left join cw_health_renewal_status on cw_health_renewal_status.prime_renewal_status_id = cw_health_renewals.renewal_status left join cw_health_product on cw_health_product.prime_health_product_id = cw_health_renewals.product left join cw_health_company on cw_health_company.prime_health_company_id = cw_health_renewals.company where cw_health_renewals_log.trans_status = 1 and cw_health_renewals_log.renewal_from_date '.$due_date.' and from_health != 1 AND cw_health_renewals_log.trans_created_by != 1 AND cw_health_renewals_log.trans_status = 1 '.$h_company_qry.' GROUP BY cw_health_renewals_log.prime_health_renewals_id';
			$health_renewed_premium_info    = $this->db->query($health_renewed_premium_qry);
			$health_renewed_premium_rslt    = $health_renewed_premium_info->result_array();
			$health_renewed_premium_info->next_result();
			// echo $health_renewed_premium_qry;die;

			//pending
			$health_pending_premium_qry  	= 'select cw_health_renewals.proposer_name as client_name,cw_health_renewals.current_policy_no,cw_health_renewals.actual_from_date,cw_health_renewals.month_count,cw_health_renewals.call_log_upd_date,cw_health_renewals.call_count,cw_health_company.company_name,cw_health_product.product_name,cw_payment_mode.payment_mode as mode_value,cw_health_renewals.current_policy_no as policy_number,DATE_FORMAT(STR_TO_DATE(cw_health_renewals.due_date,"%Y-%m-%d"),"%d-%m-%Y") as due_date,ROUND(cw_health_renewals.net_premium,2) AS net_premium,cw_health_call_status.health_call_status as call_status,cw_health_renewal_status.renewal_status,cw_employees.employee_code,cw_employees.employee_name,e.employee_name as log_upd_emp,cw_employees.level_1_reporting_person,cw_employees.level_2_reporting_person ,cw_health_renewals.ncd,cw_health_renewals.expected_date from cw_health_renewals inner join cw_employees on cw_employees.employee_code = cw_health_renewals.employee_code  inner join cw_employees e on e.prime_employees_id  = cw_health_renewals.call_log_upd_by inner join cw_health_renewals_log on cw_health_renewals_log.prime_health_renewals_id = cw_health_renewals.prime_health_renewals_id inner join cw_health_company on cw_health_company.prime_health_company_id = cw_health_renewals.company inner join cw_health_product on cw_health_product.prime_health_product_id = cw_health_renewals.product inner join cw_payment_mode on cw_payment_mode.prime_payment_mode_id = cw_health_renewals.payment_mode inner join cw_health_call_status on cw_health_call_status.prime_health_call_status_id = cw_health_renewals.call_status inner join cw_health_renewal_status on cw_health_renewal_status.prime_renewal_status_id = cw_health_renewals.renewal_status where cw_health_renewals.trans_status = 1 and cw_health_renewals.due_date '.$due_date.' '.$h_company_qry.' group by cw_health_renewals.prime_health_renewals_id ';
			$health_pending_premium_info 	= $this->db->query("CALL sp_a_run ('SELECT','$health_pending_premium_qry')");
			$health_pending_premium_rslt 	= $health_pending_premium_info->result_array();
			$health_pending_premium_info->next_result();
			// echo $health_pending_premium_qry;die;

			$total_arr = array_merge($health_pending_premium_rslt,$health_renewed_premium_rslt);
			$i = 0;
			foreach($total_arr as $val){
				$key_code_1 = $val['level_1_reporting_person'];
				$total_arr[$i]['level_1_reporting_person'] = $level_arr[$key_code_1];
				$key_code_2 = $val['level_2_reporting_person'];
				$total_arr[$i]['level_2_reporting_person'] = $level_arr[$key_code_2];
				$i = $i+1;
			}
			echo json_encode(array('success' => TRUE,'renewal_sts' => $total_arr));
		}
	}
	public function show_total_details_company_wise(){
		$name          = $this->input->post('name');
		$start_date    = date('Y-m-d', strtotime($this->input->post('start_date')));
		$end_date      = date('Y-m-d', strtotime($this->input->post('end_date')));
		$due_date      = 'BETWEEN "'.$start_date.'" AND "'.$end_date.'"';
		$select        = 'select cw_employees.login_code,cw_employees.employee_code,cw_employees.employee_name,';
		$l_company     = $this->input->post('life_company');
		$h_company     = $this->input->post('health_company');
		$month_count   = $this->input->post('month_count');
		$py_count      = $this->input->post('py_count');

		if((int)$l_company > 0){ // FOR LIFE COMPANY
			$l_company_qry   = ' AND cw_life_renewals.product_company_name = '.$l_company;
		}
		if((int)$h_company > 0){ // FOR HEALTH COMPANY
			$h_company_qry = ' AND cw_health_renewals.company = '.$h_company;
		}
		if((int)$month_count > 0){
			$h_company_qry .= ' AND month_count = '.$month_count;
		}
		if((int)$py_count > 0){//GET PY_COUNT ADD WHERE IN LIFE _ARN
			$l_company_qry .= ' AND cw_life_renewals.py_count = '.$py_count;
		}
		

		if($name === "life"){
			//RENEWED PREMIUM GET QUERY FROM LIFE RENEWALS LOG QRY 
			$life_renewed_premium_qry  	= 	$select.'cw_life_renewals.client_name,cw_product_name.product_name,cw_life_renewals.due_date AS due_date,cw_company_name.company_name,ROUND(sum(cw_life_renewals_log.renewal_premium_amount),2) AS net_premium from cw_life_renewals_log inner join cw_employees on cw_employees.prime_employees_id = cw_life_renewals_log.trans_created_by inner join cw_life_renewals on cw_life_renewals.prime_life_renewals_id = cw_life_renewals_log.prime_life_renewals_id inner join cw_company_name on cw_company_name.prime_company_name_id = cw_life_renewals.product_company_name inner join cw_product_name on cw_product_name.prime_product_name_id = cw_life_renewals.product_name where cw_life_renewals_log.renewal_due_date '.$due_date.' and from_portfolio != 1 and cw_life_renewals_log.trans_created_by != 1 and cw_life_renewals_log.trans_status = 1 '.$l_company_qry.' GROUP BY cw_life_renewals_log.trans_created_by,cw_life_renewals.product_company_name';
			$life_renewed_premium_info	=	$this->db->query($life_renewed_premium_qry);
			$life_renewed_premium_rslt 	= 	$life_renewed_premium_info->result_array();
			$life_renewed_premium_info->next_result();		

			//pending
			$life_pending_premium_qry  = 'select cw_life_renewals.client_name,cw_company_name.company_name,cw_product_name.product_name,cw_life_mode.mode_value,cw_life_renewals.policy_number,DATE_FORMAT(STR_TO_DATE(cw_life_renewals.due_date,"%Y-%m-%d"),"%d-%m-%Y") as due_date,ROUND(cw_life_renewals.net_premium,2) AS net_premium,cw_life_call_status.life_call_status as call_status,cw_life_renewal_status.renewal_status from cw_life_renewals inner join cw_company_name on cw_company_name.prime_company_name_id = cw_life_renewals.product_company_name inner join cw_life_mode on cw_life_mode.prime_life_mode_id = cw_life_renewals.mode inner join cw_product_name on cw_product_name.prime_product_name_id = cw_life_renewals.product_name inner join cw_life_call_status on cw_life_call_status.prime_life_call_status_id = cw_life_renewals.call_status inner join cw_life_renewal_status on cw_life_renewal_status.prime_renewal_status_id = cw_life_renewals.renewal_status where cw_life_renewals.due_date '.$due_date.' and cw_life_renewals.trans_status = 1 '.$l_company_qry; 
			$life_pending_premium_info = $this->db->query("CALL sp_a_run ('SELECT','$life_pending_premium_qry')");
			$life_pending_premium_rslt = $life_pending_premium_info->result_array();
			$life_pending_premium_info->next_result();	
			$total_arr = array_merge($life_renewed_premium_rslt,$life_pending_premium_rslt);
			foreach($total_arr as $data) {
				$comp_grp[$data['company_name']] += $data["net_premium"];
			}
			$final_arr = array();
			foreach($comp_grp as $key=>$value){
				$comp_arr['company_name'] = $key;
				$comp_arr['net_premium'] = $value;
				$final_arr[] = $comp_arr;
			}					
			echo json_encode(array('success' => TRUE,'renewal_sts' => $final_arr));
		}else
		if($name === "health"){
			// health renewals Achieved report
			$health_renewed_premium_qry  = $select.'cw_health_company.company_name,ROUND(sum(cw_health_renewals_log.renewal_net_premium),2) AS net_premium from cw_health_renewals_log inner join cw_employees on cw_employees.prime_employees_id = cw_health_renewals_log.trans_created_by inner join cw_health_renewals on cw_health_renewals.prime_health_renewals_id = cw_health_renewals_log.prime_health_renewals_id  inner join cw_health_company on cw_health_company.prime_health_company_id = cw_health_renewals.company where cw_health_renewals_log.trans_status = 1 and cw_health_renewals_log.renewal_from_date '.$due_date.' and from_health != 1 AND cw_health_renewals_log.trans_created_by != 1 AND cw_health_renewals_log.trans_status = 1 '.$h_company_qry.' GROUP BY cw_health_renewals.company';
			$health_renewed_premium_info = $this->db->query("CALL sp_a_run ('SELECT','$health_renewed_premium_qry')");
			$health_renewed_premium_rslt = $health_renewed_premium_info->result_array();
			$health_renewed_premium_info->next_result();
			// echo $health_renewed_premium_qry;die;

			//pending
			$health_pending_premium_qry  	= 'select cw_health_renewals.proposer_name as client_name,cw_health_company.company_name,cw_health_product.product_name,cw_payment_mode.payment_mode as mode_value,cw_health_renewals.current_policy_no as policy_number,DATE_FORMAT(STR_TO_DATE(cw_health_renewals.due_date,"%Y-%m-%d"),"%d-%m-%Y") as due_date,ROUND(sum(cw_health_renewals.net_premium),2) AS net_premium,cw_health_call_status.health_call_status as call_status,cw_health_renewal_status.renewal_status from cw_health_renewals inner join cw_health_company on cw_health_company.prime_health_company_id = cw_health_renewals.company inner join cw_health_product on cw_health_product.prime_health_product_id = cw_health_renewals.product inner join cw_payment_mode on cw_payment_mode.prime_payment_mode_id = cw_health_renewals.payment_mode inner join cw_health_call_status on cw_health_call_status.prime_health_call_status_id = cw_health_renewals.call_status inner join cw_health_renewal_status on cw_health_renewal_status.prime_renewal_status_id = cw_health_renewals.renewal_status where cw_health_renewals.trans_status = 1 and cw_health_renewals.due_date '.$due_date.' '.$h_company_qry.'  GROUP BY cw_health_company.company_name';
			$health_pending_premium_info 	= $this->db->query("CALL sp_a_run ('SELECT','$health_pending_premium_qry')");
			$health_pending_premium_rslt 	= $health_pending_premium_info->result_array();
			$health_pending_premium_info->next_result();

			$total_arr = array_merge($health_pending_premium_rslt,$health_renewed_premium_rslt);
			foreach($total_arr as $data) {
				$comp_grp[$data['company_name']] += $data["net_premium"];
			}
			$final_arr = array();
			foreach($comp_grp as $key=>$value){
				$comp_arr['company_name'] = $key;
				$comp_arr['net_premium'] = $value;
				$final_arr[] = $comp_arr;
			}		
			echo json_encode(array('success' => TRUE,'renewal_sts' => $final_arr));
		}
	}

	
	//RENEWALS DETAILS(ACHIEVE) POPUP FUNCTION
	public function show_renewal_details(){
		$name          = $this->input->post('name');
		$start_date    = date('Y-m-d', strtotime($this->input->post('start_date')));
		$end_date      = date('Y-m-d', strtotime($this->input->post('end_date')));

		$select   = 'select cw_employees.login_code,cw_employees.employee_code,cw_employees.employee_name,cw_employees.level_1_reporting_person,cw_employees.level_2_reporting_person,';
		$due_date = ' BETWEEN"'.$start_date.'" AND "'.$end_date.'"';
		//Level one and Level two
		$level_qry                = 'SELECT cw_employees.employee_code, cw_employees.employee_name FROM cw_employees GROUP BY cw_employees.employee_code';
		$level_info               = $this->db->query($level_qry);
		$level_rslt               = $level_info->result_array();
		$level_info->next_result();
		foreach($level_rslt as $emp_code_arr){
			$level_arr[$emp_code_arr['employee_code']] = $emp_code_arr['employee_code']." - ".$emp_code_arr['employee_name'];
		}
		$l_company		   = $this->input->post('life_company');
		$h_company		   = $this->input->post('health_company');
		$month_count	   = $this->input->post('month_count');
		$py_count	       = $this->input->post('py_count');

		if((int)$l_company > 0){ // FOR LIFE COMPANY
			$l_company_qry   = ' AND cw_life_renewals.product_company_name = '.$l_company;
		}
		if((int)$h_company > 0){ // FOR HEALTH COMPANY
			$h_company_qry = ' AND cw_health_renewals.company = '.$h_company;
		}
		if((int)$month_count > 0){
			$h_company_qry .= ' AND month_count = '.$month_count;
		}
		if((int)$py_count > 0){//GET PY_COUNT ADD WHERE IN LIFE _ARN
			$l_company_qry .= ' AND cw_life_renewals.py_count = '.$py_count;
		}

		if($name === "life"){//POLICY NUMBER AMBIGUOUS REASON LIFE_RENEWALS TABLE COLUMN SELECT _ARN
			//RENEWED PREMIUM GET QUERY FROM LIFE RENEWALS LOG QRY 
			$life_renewed_premium_qry  	= 	$select.'cw_life_renewals.client_name,cw_product_name.product_name,cw_life_renewals.due_date AS due_date,cw_company_name.company_name,ROUND(sum(cw_life_renewals_log.renewal_premium_amount),2) AS renewed_premium,cw_life_call_status.life_call_status as call_status,cw_life_renewal_status.renewal_status,cw_life_renewals.policy_number from cw_life_renewals_log inner join cw_employees on cw_employees.prime_employees_id = cw_life_renewals_log.trans_created_by inner join cw_life_renewals on cw_life_renewals.prime_life_renewals_id = cw_life_renewals_log.prime_life_renewals_id inner join cw_life_call_status on cw_life_call_status.prime_life_call_status_id = cw_life_renewals.call_status inner join cw_life_renewal_status on cw_life_renewal_status.prime_renewal_status_id = cw_life_renewals.renewal_status inner join cw_company_name on cw_company_name.prime_company_name_id = cw_life_renewals.product_company_name inner join cw_product_name on cw_product_name.prime_product_name_id = cw_life_renewals.product_name where cw_life_renewals_log.renewal_due_date '.$due_date.' and from_portfolio != 1 and cw_life_renewals_log.trans_created_by != 1 and cw_life_renewals_log.trans_status = 1 '.$l_company_qry.' GROUP BY cw_life_renewals_log.prime_life_renewals_id';
			$life_renewed_premium_info	=	$this->db->query($life_renewed_premium_qry);
			$life_renewed_premium_rslt 	= 	$life_renewed_premium_info->result_array();
			$life_renewed_premium_info->next_result();	

			if($life_renewed_premium_rslt){
				$i = 0;
				foreach($life_renewed_premium_rslt as $val){
					$key_code_1         = $val['level_1_reporting_person'];
					$life_renewed_premium_rslt[$i]['level_1_reporting_person'] = $level_arr[$key_code_1];
					$key_code_2         = $val['level_2_reporting_person'];
					$life_renewed_premium_rslt[$i]['level_2_reporting_person'] = $level_arr[$key_code_2];
					$i = $i+1;
				}
				echo json_encode(array('success' => TRUE,'renewal_sts' => $life_renewed_premium_rslt));
			}else{
				echo json_encode(array('success' => FALSE,'message' => "No Data Available"));
			}
		}else
		if($name === "health"){
			// health renewals report
			$health_renewed_premium_qry  = $select.'cw_health_company.company_name,ROUND(sum(cw_health_renewals_log.renewal_net_premium),2) AS renewed_premium,renewal_policy_no as policy_number,cw_health_renewals.ncd,cw_health_renewals.expected_date from cw_health_renewals_log inner join cw_employees on cw_employees.prime_employees_id = cw_health_renewals_log.trans_created_by inner join cw_health_renewals on cw_health_renewals.prime_health_renewals_id = cw_health_renewals_log.prime_health_renewals_id  inner join cw_health_company on cw_health_company.prime_health_company_id = cw_health_renewals.company where cw_health_renewals_log.trans_status = 1 and cw_health_renewals_log.renewal_from_date '.$due_date.' and from_health != 1 AND cw_health_renewals_log.trans_created_by != 1 AND cw_health_renewals_log.trans_status = 1 '.$h_company_qry.' GROUP BY cw_health_renewals_log.prime_health_renewals_id';
			$health_renewed_premium_info = $this->db->query($health_renewed_premium_qry);
			$health_renewed_premium_rslt = $health_renewed_premium_info->result_array();
			$health_renewed_premium_info->next_result();

			if($health_renewed_premium_rslt){
				$i = 0;
				foreach($health_renewed_premium_rslt as $val){
					$key_code_1         = $val['level_1_reporting_person'];
					$health_renewed_premium_rslt[$i]['level_1_reporting_person'] = $level_arr[$key_code_1];
					$key_code_2         = $val['level_2_reporting_person'];
					$health_renewed_premium_rslt[$i]['level_2_reporting_person'] = $level_arr[$key_code_2];
					$i = $i+1;
				}
				echo json_encode(array('success' => TRUE,'renewal_sts' => $health_renewed_premium_rslt));
			}else{
				echo json_encode(array('success' => FALSE,'message' => "No Data Available"));
			}
		}
	}		
	function show_renewal_details_company_wise(){
		$name          = $this->input->post('name');
		$start_date    = date('Y-m-d', strtotime($this->input->post('start_date')));
		$end_date      = date('Y-m-d', strtotime($this->input->post('end_date')));
		$select        = 'select cw_employees.login_code,cw_employees.employee_code,cw_employees.employee_name,';
		$due_date      = ' BETWEEN"'.$start_date.'" AND "'.$end_date.'"';
		$l_company     = $this->input->post('life_company');
		$h_company     = $this->input->post('health_company');
		$month_count   = $this->input->post('month_count');
		$py_count	   = $this->input->post('py_count');
		if((int)$l_company > 0){ // FOR LIFE COMPANY
			$l_company_qry   = ' AND cw_life_renewals.product_company_name = '.$l_company;
		}
		if((int)$h_company > 0){ // FOR HEALTH COMPANY
			$h_company_qry   = ' AND cw_health_renewals.company = '.$h_company;
		}
		if((int)$month_count > 0){
			$h_company_qry  .= ' AND month_count = '.$month_count;
		}
		if((int)$py_count > 0){//GET PY_COUNT ADD WHERE IN LIFE _ARN
			$l_company_qry .= ' AND cw_life_renewals.py_count = '.$py_count;
		}
		if($name === "life"){
			//RENEWED PREMIUM GET QUERY FROM LIFE RENEWALS LOG QRY 
			$life_renewed_premium_qry  	= 	$select.'cw_company_name.company_name,ROUND(sum(cw_life_renewals_log.renewal_premium_amount),2) AS renewed_premium from cw_life_renewals_log inner join cw_employees on cw_employees.prime_employees_id = cw_life_renewals_log.trans_created_by inner join cw_life_renewals on cw_life_renewals.prime_life_renewals_id = cw_life_renewals_log.prime_life_renewals_id inner join cw_company_name on cw_company_name.prime_company_name_id = cw_life_renewals.product_company_name where cw_life_renewals_log.renewal_due_date '.$due_date.' and from_portfolio != 1 and cw_life_renewals_log.trans_created_by != 1 and cw_life_renewals_log.trans_status = 1 '.$l_company_qry.' GROUP BY cw_company_name.company_name';
			$life_renewed_premium_info	=	$this->db->query("CALL sp_a_run ('SELECT','$life_renewed_premium_qry')");
			$life_renewed_premium_rslt_comp 	= 	$life_renewed_premium_info->result_array();
			$life_renewed_premium_info->next_result();	

			if($life_renewed_premium_rslt_comp){
				echo json_encode(array('success' => TRUE,'renewal_sts_comp' => $life_renewed_premium_rslt_comp));
			}else{
				echo json_encode(array('success' => FALSE,'message' => "No Data Available"));
			}
		}else
		if($name === "health"){
			// health renewals report
			$health_renewed_premium_qry  = $select.'cw_health_company.company_name,ROUND(sum(cw_health_renewals_log.renewal_net_premium),2) AS renewed_premium from cw_health_renewals_log inner join cw_employees on cw_employees.prime_employees_id = cw_health_renewals_log.trans_created_by inner join cw_health_renewals on cw_health_renewals.prime_health_renewals_id = cw_health_renewals_log.prime_health_renewals_id  inner join cw_health_company on cw_health_company.prime_health_company_id = cw_health_renewals.company where cw_health_renewals_log.trans_status = 1 and cw_health_renewals_log.renewal_from_date '.$due_date.' and from_health != 1 AND cw_health_renewals_log.trans_created_by != 1 AND cw_health_renewals_log.trans_status = 1 '.$h_company_qry.' GROUP BY cw_health_renewals.company';
			$health_renewed_premium_info = $this->db->query("CALL sp_a_run ('SELECT','$health_renewed_premium_qry')");
			$health_renewed_premium_rslt_comp = $health_renewed_premium_info->result_array();
			$health_renewed_premium_info->next_result();

			if($health_renewed_premium_rslt_comp){
				// echo $health_renewed_premium_qry;die;
				echo json_encode(array('success' => TRUE,'renewal_sts_comp' => $health_renewed_premium_rslt_comp));
			}else{
				echo json_encode(array('success' => FALSE,'message' => "No Data Available"));
			}
		}
	}
	//RENEWALS DETAILS(PENDING) POPUP FUNCTION
	public function show_pending_details(){ 
		$name          = $this->input->post('name'); 
		$start_date    = date('Y-m-d',strtotime($this->input->post('start_date'))); 
		$end_date      = date('Y-m-d',strtotime($this->input->post('end_date'))); 
		$due_date      = ' BETWEEN "'.$start_date.'" AND "'.$end_date.'"';
		//Level one and Level two
		$level_qry                = 'SELECT cw_employees.employee_code, cw_employees.employee_name FROM cw_employees GROUP BY cw_employees.employee_code';
		$level_info               = $this->db->query($level_qry);
		$level_rslt               = $level_info->result_array();
		$level_info->next_result();
		foreach($level_rslt as $emp_code_arr){
			$level_arr[$emp_code_arr['employee_code']] = $emp_code_arr['employee_code']." - ".$emp_code_arr['employee_name'];
		}
		$h_company	 = $this->input->post('health_company');
		$l_company	 = $this->input->post('life_company');
		$month_count = $this->input->post('month_count');
		$py_count	 = $this->input->post('py_count');
		if((int)$l_company > 0){ // FOR LIFE COMPANY
			$l_company_qry   = ' AND cw_life_renewals.product_company_name = '.$l_company;
		}
		if((int)$h_company > 0){ // FOR HEALTH COMPANY
			$h_company_qry = ' AND cw_health_renewals.company = '.$h_company;
		}
		if((int)$month_count > 0){
			$h_company_qry .= ' AND month_count = '.$month_count;
		}
		if((int)$py_count > 0){//GET PY_COUNT ADD WHERE IN LIFE _ARN
			$l_company_qry .= ' AND cw_life_renewals.py_count = '.$py_count;
		}
		if($name === "life"){
			//RENEWEDPREMIUM GET QUERY FROM LIFE RENEWALS LOG QRY  
			$life_pending_premium_qry  = 'select cw_life_renewals.client_name,cw_company_name.company_name,cw_product_name.product_name,cw_life_mode.mode_value,cw_life_renewals.policy_number,DATE_FORMAT(STR_TO_DATE(cw_life_renewals.due_date,"%Y-%m-%d"),"%d-%m-%Y") as due_date,ROUND(cw_life_renewals.net_premium,2) AS net_premium,cw_life_call_status.life_call_status as call_status,cw_life_renewal_status.renewal_status,cw_employees.employee_code,cw_employees.employee_name,cw_employees.level_1_reporting_person,cw_employees.level_2_reporting_person from cw_life_renewals inner join cw_employees on cw_employees.prime_employees_id = cw_life_renewals.emp_id inner join cw_life_renewals_log on cw_life_renewals_log.prime_life_renewals_id = cw_life_renewals.prime_life_renewals_id inner join cw_company_name on cw_company_name.prime_company_name_id = cw_life_renewals.product_company_name inner join cw_life_mode on cw_life_mode.prime_life_mode_id = cw_life_renewals.mode inner join cw_product_name on cw_product_name.prime_product_name_id = cw_life_renewals.product_name inner join cw_life_call_status on cw_life_call_status.prime_life_call_status_id = cw_life_renewals.call_status inner join cw_life_renewal_status on cw_life_renewal_status.prime_renewal_status_id = cw_life_renewals.renewal_status where cw_life_renewals.due_date '.$due_date.' and cw_life_renewals.trans_status = 1 '.$l_company_qry.' GROUP BY cw_life_renewals.prime_life_renewals_id'; 
			$life_pending_premium_info = $this->db->query("CALL sp_a_run ('SELECT','$life_pending_premium_qry')");
			$life_pending_premium_rslt = $life_pending_premium_info->result_array();
			$life_pending_premium_info->next_result();	
			if($life_pending_premium_rslt){
				$i = 0;
				foreach($life_pending_premium_rslt as $val){
					$key_code_1 = $val['level_1_reporting_person'];
					$life_pending_premium_rslt[$i]['level_1_reporting_person'] = $level_arr[$key_code_1];
					$key_code_2 = $val['level_2_reporting_person'];
					$life_pending_premium_rslt[$i]['level_2_reporting_person'] = $level_arr[$key_code_2];
					$i = $i+1;
				}
				echo json_encode(array('success' => TRUE,'renewal_sts' => $life_pending_premium_rslt));
			}else{
				echo json_encode(array('success' => FALSE,'message' => "No Data Available"));
			}
		}else
		if($name === "health"){
			// health renewals report
			$health_pending_premium_qry  	= 'select cw_health_renewals.proposer_name as client_name,cw_health_company.company_name,cw_health_product.product_name,cw_payment_mode.payment_mode as mode_value,cw_health_renewals.current_policy_no as policy_number,DATE_FORMAT(STR_TO_DATE(cw_health_renewals.due_date,"%Y-%m-%d"),"%d-%m-%Y") as due_date,ROUND(cw_health_renewals.net_premium,2) AS net_premium,cw_health_call_status.health_call_status as call_status,cw_health_renewal_status.renewal_status,cw_employees.employee_code,cw_employees.employee_name,cw_employees.level_1_reporting_person,cw_employees.level_2_reporting_person,cw_health_renewals.ncd,cw_health_renewals.expected_date from cw_health_renewals inner join cw_employees on cw_employees.employee_code = cw_health_renewals.employee_code inner join cw_health_renewals_log on cw_health_renewals_log.prime_health_renewals_id = cw_health_renewals.prime_health_renewals_id inner join cw_health_company on cw_health_company.prime_health_company_id = cw_health_renewals.company inner join cw_health_product on cw_health_product.prime_health_product_id = cw_health_renewals.product inner join cw_payment_mode on cw_payment_mode.prime_payment_mode_id = cw_health_renewals.payment_mode inner join cw_health_call_status on cw_health_call_status.prime_health_call_status_id = cw_health_renewals.call_status inner join cw_health_renewal_status on cw_health_renewal_status.prime_renewal_status_id = cw_health_renewals.renewal_status where cw_health_renewals.trans_status = 1 and cw_health_renewals.due_date '.$due_date.' '.$h_company_qry.' group by cw_health_renewals.prime_health_renewals_id ';
			// echo $health_pending_premium_qry;die;	
			$health_pending_premium_info 	= $this->db->query("CALL sp_a_run ('SELECT','$health_pending_premium_qry')");
			$health_pending_premium_rslt 	= $health_pending_premium_info->result_array();
			$health_pending_premium_info->next_result();

			if($health_pending_premium_rslt){
				$i = 0;
				foreach($health_pending_premium_rslt as $val){
					$key_code_1 = $val['level_1_reporting_person'];
					$health_pending_premium_rslt[$i]['level_1_reporting_person'] = $level_arr[$key_code_1];
					$key_code_2 = $val['level_2_reporting_person'];
					$health_pending_premium_rslt[$i]['level_2_reporting_person'] = $level_arr[$key_code_2];
					$i = $i+1;
				}
				echo json_encode(array('success' => TRUE,'renewal_sts' => $health_pending_premium_rslt));
			}else{
				echo json_encode(array('success' => FALSE,'message' => "No Data Available"));
			}
		}
	}
	function show_pending_details_company_wise(){
		$name          = $this->input->post('name'); 
		$start_date    = date('Y-m-d',strtotime($this->input->post('start_date'))); 
		$end_date      = date('Y-m-d',strtotime($this->input->post('end_date'))); 
		$due_date      = 'BETWEEN "'.$start_date.'" AND "'.$end_date.'"';
		$h_company	   = $this->input->post('health_company');
		$l_company	   = $this->input->post('life_company');
		$month_count   = $this->input->post('month_count');
		$py_count	   = $this->input->post('py_count');
		if((int)$l_company > 0){ // FOR LIFE COMPANY
			$l_company_qry   = ' AND cw_life_renewals.product_company_name = '.$l_company;
		}
		if((int)$h_company > 0){ // FOR HEALTH COMPANY
			$h_company_qry = ' AND cw_health_renewals.company = '.$h_company;
		}
		if((int)$month_count > 0){
			$h_company_qry .= ' AND month_count = '.$month_count;
		}
		if((int)$py_count > 0){//GET PY_COUNT ADD WHERE IN LIFE _ARN
			$l_company_qry .= ' AND cw_life_renewals.py_count = '.$py_count;
		}
		if($name === "life"){
			//RENEWEDPREMIUM GET QUERY FROM LIFE RENEWALS LOG QRY  PENDING 
			$life_pending_premium_qry  = 'select cw_life_renewals.client_name,cw_company_name.company_name,cw_product_name.product_name,cw_life_mode.mode_value,cw_life_renewals.policy_number,DATE_FORMAT(STR_TO_DATE(cw_life_renewals.due_date,"%Y-%m-%d"),"%d-%m-%Y") as due_date,ROUND(sum(cw_life_renewals.net_premium),2) AS net_premium,cw_life_call_status.life_call_status as call_status,cw_life_renewal_status.renewal_status from cw_life_renewals inner join cw_company_name on cw_company_name.prime_company_name_id = cw_life_renewals.product_company_name inner join cw_life_mode on cw_life_mode.prime_life_mode_id = cw_life_renewals.mode inner join cw_product_name on cw_product_name.prime_product_name_id = cw_life_renewals.product_name inner join cw_life_call_status on cw_life_call_status.prime_life_call_status_id = cw_life_renewals.call_status inner join cw_life_renewal_status on cw_life_renewal_status.prime_renewal_status_id = cw_life_renewals.renewal_status where cw_life_renewals.due_date '.$due_date.' and cw_life_renewals.trans_status = 1 '.$l_company_qry.' GROUP BY cw_company_name.company_name'; 
			$life_pending_premium_info = $this->db->query("CALL sp_a_run ('SELECT','$life_pending_premium_qry')");
			$life_pending_premium_rslt = $life_pending_premium_info->result_array();
			$life_pending_premium_info->next_result();	
			if($life_pending_premium_rslt){
				// echo $life_pending_premium_qry;die;
				echo json_encode(array('success' => TRUE,'renewal_sts' => $life_pending_premium_rslt));
			}else{
				echo json_encode(array('success' => FALSE,'message' => "No Data Available"));
			}
		}else
		if($name === "health"){
			// health renewals report pending 
			$health_pending_premium_qry  	= 'select cw_health_renewals.proposer_name as client_name,cw_health_company.company_name,cw_health_product.product_name,cw_payment_mode.payment_mode as mode_value,cw_health_renewals.current_policy_no as policy_number,DATE_FORMAT(STR_TO_DATE(cw_health_renewals.due_date,"%Y-%m-%d"),"%d-%m-%Y") as due_date,ROUND(sum(cw_health_renewals.net_premium),2) AS net_premium,cw_health_call_status.health_call_status as call_status,cw_health_renewal_status.renewal_status from cw_health_renewals inner join cw_health_company on cw_health_company.prime_health_company_id = cw_health_renewals.company inner join cw_health_product on cw_health_product.prime_health_product_id = cw_health_renewals.product inner join cw_payment_mode on cw_payment_mode.prime_payment_mode_id = cw_health_renewals.payment_mode inner join cw_health_call_status on cw_health_call_status.prime_health_call_status_id = cw_health_renewals.call_status inner join cw_health_renewal_status on cw_health_renewal_status.prime_renewal_status_id = cw_health_renewals.renewal_status where cw_health_renewals.trans_status = 1 and cw_health_renewals.due_date '.$due_date.' '.$h_company_qry.' GROUP BY cw_health_company.company_name';
			$health_pending_premium_info 	= $this->db->query("CALL sp_a_run ('SELECT','$health_pending_premium_qry')");
			$health_pending_premium_rslt 	= $health_pending_premium_info->result_array();
			$health_pending_premium_info->next_result();

			if($health_pending_premium_rslt){
				// echo $health_pending_premium_qry;die;
				echo json_encode(array('success' => TRUE,'renewal_sts' => $health_pending_premium_rslt));
			}else{
				echo json_encode(array('success' => FALSE,'message' => "No Data Available"));
			}
		}
	}
	//all pending tab functions start
	public function pending_details_tab(){
		$start_date           = date('Y-m-d', strtotime($this->input->post('start_date')));
		$end_date             = date('Y-m-d', strtotime($this->input->post('end_date')));
		$company              = $this->input->post('health_company');
		$month_count          = $this->input->post('month_count');
		if((int)$company > 0){
			$company_qry      = ' and cw_health_renewals.company = '.$company;
		}
		if((int)$month_count > 0){
			$company_qry     .= ' and cw_health_renewals.month_count = '.$month_count;
		}
		//health total achieved pending data query process start//
		$health_achieve_qry   = 'select ROUND(SUM(cw_health_renewals_log.renewal_net_premium),2) AS renewed_premium, count(*) as premium_count_policy from cw_health_renewals_log  LEFT JOIN cw_health_renewals ON cw_health_renewals_log.prime_health_renewals_id = cw_health_renewals.prime_health_renewals_id where cw_health_renewals_log.trans_status = 1 and cw_health_renewals_log.renewal_from_date BETWEEN "'.$start_date.'" AND "'.$end_date.'" and cw_health_renewals_log.from_health != 1 and cw_health_renewals_log.trans_created_by != 1 '.$company_qry;

		$health_achieve_info  = $this->db->query("CALL sp_a_run ('SELECT','$health_achieve_qry')");
		$health_ren_rslt 	  = $health_achieve_info->result();
		$health_achieve_info->next_result();

		$health_achieve_prem  = round($health_ren_rslt[0]->renewed_premium,2);
		$premium_count_policy = $health_ren_rslt[0]->premium_count_policy;

		//health_pending_query
		$health_pend_qry  	  = 'select ROUND(SUM(cw_health_renewals.net_premium),2) AS pending_premium,count(*) AS health_pending_policy from cw_health_renewals where cw_health_renewals.trans_status = 1 and cw_health_renewals.due_date BETWEEN "'.$start_date.'" AND "'.$end_date.'" '.$company_qry; 

		$health_pend_info 	  = $this->db->query("CALL sp_a_run ('SELECT','$health_pend_qry')");
		$health_pend_rslt 	  = $health_pend_info->result();
		$health_pend_info->next_result();

		$health_pend_prem     = (float)$health_pend_rslt[0]->pending_premium;
		$health_pend_policy   = $health_pend_rslt[0]->health_pending_policy;
		$health_tot_prem      = (float)$health_achieve_prem + $health_pend_prem;
		$health_prem_per      = $health_tot_prem > 0 ? round(($health_tot_prem - $health_pend_prem) * 100 / $health_tot_prem)  . "%" : "0%";
		$health_pend_perc  	  = $health_tot_prem > 0 ? round(($health_tot_prem - $health_achieve_prem) * 100 / $health_tot_prem) . "%" : "0%";
		$total_count  		  = $health_pend_policy + $premium_count_policy;
		$health_ach_cnt_per   = $total_count > 0 ? round(($total_count - $health_pend_policy) * 100 / $total_count ) . "%" : "0%";
		$health_pend_cnt_per  = $total_count > 0 ? round(($total_count - $premium_count_policy) * 100 / $total_count ) . "%" : "0%";


		//health business table
		$renewal_business_table = "<table class='table'><h3>Health renewal Business Report</h3><thead><tr><th colspan='3' class='col-md-4 center' style='border-right: 1px solid white;'>total</th><th colspan='4' class='col-md-4 center' style='border-right: 1px solid white;'>Achieved</th><th colspan='4' class='col-md-4 center' style='border-right: 1px solid white;'>Pending</th></tr><tr><th class='center'>premium</th><th class='center'>No. Of Policy</th><th class='center'>%</th><th class='center'>Premium</th><th class='center'>%</th><th class='center'>No. Of Policy</th><th class='center'>%</th><th class='center'>Premium</th><th>%</th><th class='center'>No. Of Policy</th><th class='center'>%</th></tr></thead><tbody id='health_business_data'><tr class='center'><td>".$health_tot_prem."</td><td>".$total_count."</td>  <td> 100% </td><td>".$health_achieve_prem."</td><td>".$health_prem_per."</td><td>".$premium_count_policy."</td><td>".$health_ach_cnt_per  ."</td><td>".$health_pend_prem."</td><td>".$health_pend_perc ."</td><td>".$health_pend_policy."</td><td>".$health_pend_cnt_per."</td></tr></tbody></table>";
		//health total achieved pending data query process end//

		//CALL AND RENEWAL BASED REPORT PROCESS START//
		$health_sts_pend_qry	=	'SELECT cw_health_renewals.call_status,cw_health_renewals.renewal_status,cw_health_call_status.health_call_status as health_call_status_name,cw_health_renewal_status.renewal_status as renewal_status_name,count(*) as policy_count,round(SUM(net_premium),2) as premium FROM `cw_health_renewals` inner join cw_health_call_status on cw_health_call_status.prime_health_call_status_id = cw_health_renewals.call_status inner join cw_health_renewal_status ON cw_health_renewal_status.prime_renewal_status_id=cw_health_renewals.renewal_status where cw_health_call_status.health_call_status !="renewed" AND `due_date` BETWEEN  "'.$start_date.'" and  "'.$end_date.'" and cw_health_renewals.trans_status = 1 '.$company_qry.' GROUP BY cw_health_renewals.renewal_status,cw_health_renewals.call_status';
		//  echo $health_sts_pend_qry;die;
		$health_sts_pend_info 	=	$this->db->query("CALL sp_a_run ('SELECT','$health_sts_pend_qry')");
		$health_sts_pend_rslt	=	$health_sts_pend_info->result();
		$health_sts_pend_info->next_result();

		//CALL AND RENEWAL TABLE DYNAMIC HEADER START
		foreach($health_sts_pend_rslt as $value){
			$count_arr[$value->renewal_status][$value->call_status] = array("policy_count"=>$value->policy_count,"premium"=>$value->premium,"renewal_status_name"=>$value->renewal_status_name,"call_status"=>$value->call_status);
			$ren_status_arr[$value->renewal_status] = $value->renewal_status_name;
			$header1[$value->call_status]           = $value->health_call_status_name;
		}

		$header_row1 = "<tr><td style='text-align:center'>status</td>";
		$header_row2 = "<tr><td>Renewed Status</td>";		
		foreach($header1 as $key => $value){
				$header_row1 .= "<th colspan=2  style='text-align:center; border-right: 1px solid white;'>".$value."</th>";
				$header_row2 .= "<th style='text-align:center;'>Count</th><th style='text-align:center;'>Premium</th>";
		}
		$header_rslt  = $header_row1."</tr>".$header_row2."</tr>";
		$tr_line      = "";
		foreach($count_arr as $ren_id => $call_data){
			$renewal_status  = $ren_status_arr[$ren_id];
			foreach($call_data as $key => $data){
				$policy_count= $data['policy_count'];
				$premium   	 = $data['premium'];
				$td_line     = 	"";
				foreach($header1 as $key1 => $value1){											
					if($key === $key1){		
						$call_id  = $key;			
						$td_line .= "<td style='text-align:center; color:blue;' id='mydata'   onclick=get_popup($ren_id,$call_id,'".$start_date."','".$end_date."','status','".$company."','".$month_count."')>".$policy_count."</td><td style='text-align:center;color:blue;'onclick=get_popup($ren_id,$call_id,'".$start_date."','".$end_date."','status','".$company."','".$month_count."')>".$premium."</td>";
					}else{
						$td_line .= "<td style='text-align:center;'>0</td><td style='text-align:center;'>0.00</td>";
					}	
				}	
				$tr_line .= "<tr><td style='text-align:center;'>".$renewal_status."</td >".$td_line."</tr>";
			}	
		}	
		if(!$tr_line){
			$tr_line = "<tr><td colspan=10 style='text-align:center;'>No Data Available</td></tr>";

		}
		$renewal_status_table = "<table class='col-md-12' id='health_business_renewal_table'><h3>Call and Renewal status Report</h3><thead id='table_head'>".$header_rslt."</thead><tbody id='table_body'>".$tr_line."</tbody></table>";
		//CALL AND RENEWAL BASED REPORT PROCESS END//
			
		//EMPLOYEE TABLE REPORT PROCESS START//
		$employee_based_qry	     = 'SELECT cw_health_renewals.company,cw_health_renewals.trans_updated_by,cw_health_company.company_name,cw_employees.employee_name,COUNT(*) AS policy_count,ROUND(SUM(cw_health_renewals.net_premium),2) AS premium FROM `cw_health_renewals` LEFT JOIN cw_employees ON cw_employees.prime_employees_id = cw_health_renewals.trans_updated_by INNER JOIN cw_health_company ON cw_health_company.prime_health_company_id = cw_health_renewals.company	INNER JOIN cw_health_call_status ON cw_health_call_status.prime_health_call_status_id = cw_health_renewals.call_status WHERE  `due_date` BETWEEN "'.$start_date.'" AND "'.$end_date.'" AND cw_health_renewals.trans_status = 1 '.$company_qry.' GROUP BY cw_health_renewals.trans_updated_by,cw_health_company.company_name';
		$employee_details_info	 = $this->db->query("CALL sp_a_run ('SELECT','$employee_based_qry')");
		$employee_details_result = $employee_details_info->result();
		$employee_details_info->next_result();

		//EMPLOYEE TABLE FOR(PERCENTAGE)//
		$emp_perc_qry		=	'SELECT cw_health_company.company_name,cw_health_renewals.company,SUM(cw_health_renewals.net_premium)AS total_premium FROM cw_health_renewals INNER JOIN cw_health_company ON cw_health_company.prime_health_company_id = cw_health_renewals.company WHERE cw_health_renewals.due_date BETWEEN "'.$start_date.'" AND "'.$end_date.'" AND cw_health_renewals.trans_status=1 '.$company_qry.'  GROUP BY cw_health_renewals.company';
		$emp_perc_info 		=	$this->db->query("CALL sp_a_run ('SELECT','$emp_perc_qry')");
		$emp_perc_rslt		=	$emp_perc_info->result();
		$emp_perc_info->next_result();

		$employee_header1	=	array();
		$employee_count_arr	=	array();
		$ren_status_arr		=	array();
		foreach($emp_perc_rslt as $tot){
			$emp_tot_arr[$tot->company]=$tot->total_premium;
		}
		foreach($employee_details_result as $com_value){
			// for all data array
			$employee_count_arr[$com_value->trans_updated_by][$com_value->company] = array("policy_count"=>$com_value->policy_count,"premium"=>$com_value->premium,"company"=>$com_value->company,'company_name'=>$com_value->company_name);
			//array for header
			$employee_header1[$com_value->company] = $com_value->company_name;
			//employee name
			$emp_name_arr[$com_value->trans_updated_by] = $com_value->employee_name;
		}	
		$emp_header_row 	 = "<tr><td style='text-align:center; border-right:1px solid white;'>Employee</td>";
		$emp_header_row1	 = "<tr><td style='text-align:center;'>Name</td>";
		foreach($employee_header1 as $key => $emp_value1){
			$emp_header_row .="<td colspan=3 style='text-align:center;'>".$emp_value1."</td>";
			$emp_header_row1.="<td>policy count</td><td>premium</td><td>percentage</td>";
		}
		$emp_header_row    	.= "</tr>";
		$emp_header_row1   	.= "</tr>";
		$emp_header_rslt     = $emp_header_row . $emp_header_row1;
		$emp_tr_line         = "";
		foreach($employee_count_arr as $emp_id => $emp_data){
			$employee_name   =  $emp_name_arr[$emp_id];						
			foreach($emp_data as $company => $data){
				foreach($emp_tot_arr as $com_id => $tot_amount){
					if($company === $com_id){
						$data['total_amount']=$tot_amount;
					}
				}
			$policy_count   = $data['policy_count'];
			$premium        = $data['premium'];
			$full_total     = $data['total_amount'];
			$percentage   	= round(($premium) * 100 / $full_total) . "%";
			$emp_td_line    = "";
			foreach($employee_header1 as $key1 => $value1){	
				if($company === $key1){				
					$emp_td_line .= "<td style='text-align:center; color:blue;' 	id='mydata' onclick=get_popup($emp_id,$company,'".$start_date."','".$end_date."','employee','".$company."','".$month_count."')>".$policy_count."</td><td style='text-align:center; color:blue;' onclick=get_popup($emp_id,$company,'".$start_date."','".$end_date."','employee','".$company."','".$month_count."')>".$premium."</td><td style='text-align:center;	color:blue;'  onclick=get_popup($emp_id,$company,'".$start_date."','".$end_date."','employee','".$company."','".$month_count."')>".$percentage."</td>";
				}else{
					$emp_td_line .= "<td style='text-align:center;'>0</td><td 	style='text-align:center;'>0.00</td><td style='text-align:center;	'>0</td>";
				}
			}
			$emp_tr_line .= "<tr><td style='text-align:center;'>".$employee_name."</td >".$emp_td_line."</tr>";	
			}		
		}
		if(!$emp_tr_line){
			$emp_tr_line = "<tr><td colspan=10 style='text-align:center;'>No Data Available</td></tr>";
		}
		$employee_tbl	  = "<table class='table table-hover' id='employee_table'><h3>Employee Report</h3><thead>".$emp_header_rslt."</thead><tbody>".$emp_tr_line."</tbody></table>";
		//EMPLOYEE TABLE REPORT PROCESS END//

		//COMPANY TABLE REPORT PROCESS START//
		$comp_pend_qry    = 'SELECT cw_health_renewals.company,cw_health_company.company_name,ROUND(SUM(cw_health_renewals.net_premium),2) AS pending_amount,COUNT(*) AS pending_count FROM cw_health_renewals LEFT JOIN cw_employees ON cw_employees.prime_employees_id = cw_health_renewals.trans_updated_by	INNER JOIN cw_health_company ON cw_health_company.prime_health_company_id = cw_health_renewals.company INNER JOIN cw_health_call_status ON cw_health_call_status.prime_health_call_status_id = cw_health_renewals.call_status WHERE due_date BETWEEN "'.$start_date.'" AND "'.$end_date.'" AND cw_health_renewals.trans_status = 1 '.$company_qry.' GROUP BY cw_health_company.company_name';
		$comp_pend_info   = $this->db->query("CALL sp_a_run ('SELECT','$comp_pend_qry')");
		$comp_pend_rslt   = $comp_pend_info->result_array();
		$comp_pend_info->next_result();
		// echo $comp_pend_qry;die;

		$comp_achieve_qry = 'SELECT ROUND(SUM(cw_health_renewals_log.renewal_net_premium),2) AS achieved_amount,cw_health_company.company_name, COUNT(*) AS achieved_count,cw_health_renewals.company FROM cw_health_renewals_log INNER JOIN cw_health_renewals ON cw_health_renewals.prime_health_renewals_id=cw_health_renewals_log.prime_health_renewals_id INNER JOIN cw_health_company ON cw_health_company.prime_health_company_id = cw_health_renewals.company	WHERE cw_health_renewals_log.trans_status = 1 AND cw_health_renewals_log.renewal_from_date BETWEEN "'.$start_date.'" AND "'.$end_date.'" AND cw_health_renewals_log.from_health != 1 AND cw_health_renewals_log.trans_created_by != 1 '.$company_qry.' GROUP BY cw_health_renewals.company';
		$comp_achiev_info = $this->db->query("CALL sp_a_run ('SELECT','$comp_achieve_qry')");
		$comp_achiev_rslt = $comp_achiev_info->result_array();
		$comp_achiev_info->next_result();
		// echo $comp_achieve_qry;die;
		$pend_comp_arr    = array();
		array_walk($comp_pend_rslt, function($v, $k) use(&$pend_comp_arr) {
			$pend_comp_arr[$v['company_name']] =$v;
		});
		$achieve_comp_arr = array();
		array_walk($comp_achiev_rslt, function($v, $k) use(&$achieve_comp_arr) {
			$achieve_comp_arr[$v['company_name']] =$v;
		});
		$all_comp_arr     = array_replace_recursive($pend_comp_arr,$achieve_comp_arr);
		//TABLE STRUCTURE CREATE 
		// $table_struct     = "<table id='company_table'><h3>Company Report</h3><thead><tr><th>Company Name</th><th>Pending Amount</th><th>Pending Count</th><th>Total Amount</th><th>Total Count</th><th>Achieved %</th><th>Pending %</th></tr></thead><tbody>";
		$table_struct     = '';
		foreach($all_comp_arr as $comp_key => $comp_val){
			$pend_count   = 0;	
			$pend_amt     = 0.00;	
			$achieve_count= 0;	
			$achieve_amt  = 0.00;
				//for pending details
			if(array_key_exists("pending_count",$comp_val)){
				$pend_count 	= $comp_val["pending_count"];	
				$pend_amt       = $comp_val["pending_amount"];	
			}
				//for achieve details
			if(array_key_exists("achieved_count",$comp_val)){
				$achieve_count  = (float)$comp_val["achieved_count"];	
				$achieve_amt    = (float)$comp_val["achieved_amount"];	
			}
				//for company key 
			if(array_key_exists("company",$comp_val)){
				$company  = (int)$comp_val["company"];		
			}
			$total_amount		= (float)$pend_amt + $achieve_amt;
			$tot_count		    = (float)$pend_count + $achieve_count;
			$pend_perc 			= round(($total_amount - $achieve_amt)* 100 /$total_amount).'%';
			$achieve_perc		= round(($total_amount - $pend_amt)* 100 /$total_amount).'%';
			//company table creation
			$table_struct  .="<tr><td style='text-align:center;'>".$comp_key."</td><td style='text-align:center; 	color:blue;' onclick=get_popup($emp_id,$company,'".$start_date."','".$end_date."','company','".$company."','".$month_count."')>".		$pend_amt."</td style='text-align:center'><td style='text-align:center'>".$pend_count."</td><td style='text-align:center;'>".$total_amount."</td><td style='text-align:center;'>".$tot_count."</td><td 		style='text-align:center'>$achieve_perc</td><td style='text-align:center'>".$pend_perc."</td></tr>";		
		}
		if(!$table_struct){
			$table_struct = "<tr><td colspan='7'>No Data Available</td></tr>";
			$comp_msg     = true;
		}
		$company_table    = "<table id='company_table' class='table table-hover'><h3>Company Report</h3><thead><tr><th>Company Name</th><th>Pending Amount</th><th>Pending Count</th><th>Total Amount</th><th>Total Count</th><th>Achieved %</th><th>Pending %</th></tr></thead><tbody>$table_struct</tbody></table>";
		//EMPLOYEE TABLE REPORT PROCESS END//	
		if($renewal_status_table){
			echo json_encode(array('success' => TRUE,'health_renewal_table' =>$renewal_business_table ,'health_header'=>$health_header,'employee_tbl'=>$employee_tbl,'company_table' => $company_table,'comp_msg' => $comp_msg,'renewal_sts_tbl'=>$renewal_status_table));
		}else{
			echo json_encode(array('success' => FALSE,'message' => "No Data Available"));
		}	
	}
	//status based report popup data function start
	public function show_business_popup_data(){
		$start_date     	 = 	$this->input->post('start_date');
		$end_date       	 = 	$this->input->post('end_date');
		$id1 				 = 	$this->input->post('id1');
		$id2       			 = 	$this->input->post('id2');
		$name 				 = 	$this->input->post('name');
		$month_count		 = 	$this->input->post('month_count');
		$company			 = 	$this->input->post('company');
		
		if((int)$company > 0){
			$company_qry   = ' AND cw_health_renewals.company = '.$company;
		}
		if((int)$month_count > 0){
			$company_qry .= ' AND cw_health_renewals.month_count = '.$month_count;
		}

		//due_date and trns_status
		$due_date_con        =  "cw_health_renewals.trans_status = 1 AND cw_health_renewals.due_date BETWEEN '$start_date' AND '$end_date'";

		// WHERE condition
		$where      =  'cw_health_renewals.company= '.$id2.'';

		//display
		$display    =  'cw_health_renewals.proposer_name,cw_health_renewals.contact_number,cw_health_renewals.due_date,cw_health_renewals.renewed_date,cw_health_renewals.call_log_upd_date,cw_health_renewals.current_policy_no AS policy_number,cw_health_company.company_name,cw_health_renewals.login_date,cw_health_renewals.net_premium as net_premium,cw_health_product.product_name,created.employee_name,cw_health_call_status.health_call_status,cw_sub_disposition.sub_disposition,call_count,CONCAT(owner.login_code," - ",owner.employee_name) as owner,CONCAT(level1.login_code," - ",level1.employee_name) as level1,concat(level2.login_code," - ",level2.employee_name) as level2,cw_health_renewals.ncd,cw_health_renewals.expected_date';

		//join 
		$joins   =  'cw_health_renewals LEFT JOIN cw_employees as created ON created.prime_employees_id = cw_health_renewals.call_log_upd_by INNER JOIN cw_health_company ON cw_health_company.prime_health_company_id=cw_health_renewals.company INNER JOIN cw_health_product ON cw_health_product.prime_health_product_id=cw_health_renewals.product inner join cw_health_call_status on cw_health_call_status.prime_health_call_status_id = cw_health_renewals.call_status inner join cw_sub_disposition on cw_sub_disposition.prime_sub_disposition_id=cw_health_renewals.sub_disposition inner join cw_employees as owner on owner.login_code = cw_health_renewals.login_code LEFT join cw_employees as level1 on level1.employee_code = owner.level_1_reporting_person LEFT join cw_employees as level2 on level2.employee_code = owner.level_2_reporting_person';

		if($name === 'status'){
			$popup_qry       = 'SELECT '.$display.' FROM '.$joins.' WHERE '.$due_date_con.'  AND cw_health_renewals.renewal_status='.$id1.' AND cw_health_renewals.call_status='.$id2.''.$company_qry;	
			// echo $popup_qry; die;			
		}
		if($name === 'employee'){
			$popup_qry		= 'SELECT '.$display.' FROM '.$joins.' WHERE  '.$due_date_con.'AND cw_health_renewals.trans_updated_by ='.$id1.' AND '.$where.''.$company_qry;
		}
		if($name === "company"){
			$popup_qry		= 'SELECT cw_health_renewals.proposer_name,cw_health_renewals.contact_number,cw_health_renewals.due_date,cw_health_renewals.renewed_date,cw_health_renewals.current_policy_no AS policy_number,cw_health_company.company_name,cw_health_renewals.login_date,Round((cw_health_renewals.net_premium),2) as net_premium,cw_health_product.product_name,created.employee_name,cw_health_call_status.health_call_status,cw_sub_disposition.sub_disposition,call_count,CONCAT(owner.login_code," - ",owner.employee_name) as owner,CONCAT(level1.login_code," - ",level1.employee_name) as level1,concat(level2.login_code," - ",level2.employee_name) as level2,cw_health_renewals.ncd,cw_health_renewals.expected_date ,cw_health_renewals.call_log_upd_date FROM cw_health_renewals INNER JOIN cw_health_company ON cw_health_company.prime_health_company_id=cw_health_renewals.company INNER JOIN cw_health_product ON cw_health_product.prime_health_product_id=cw_health_renewals.product inner join cw_health_call_status on cw_health_call_status.prime_health_call_status_id = cw_health_renewals.call_status inner join cw_sub_disposition on cw_sub_disposition.prime_sub_disposition_id=cw_health_renewals.sub_disposition inner join cw_employees as owner on owner.login_code = cw_health_renewals.login_code LEFT JOIN cw_employees as created ON created.prime_employees_id = cw_health_renewals.trans_updated_by LEFT join cw_employees as level1 on level1.employee_code = owner.level_1_reporting_person LEFT join cw_employees as level2 on level2.employee_code = owner.level_2_reporting_person WHERE cw_health_renewals.trans_status = 1 AND cw_health_renewals.due_date BETWEEN "'.$start_date.'" AND "'.$end_date.'" AND cw_health_renewals.company= "'.$id2.'"'.$company_qry;
		}
		// echo $popup_qry;die;
		$health_popup_info 	= 	$this->db->query($popup_qry);
		$health_popup_rslt 	= 	$health_popup_info->result_array();
		$health_popup_info->next_result();
		echo json_encode(array('success' => TRUE,'popup_data' => $health_popup_rslt));
	}
	function show_business_popup_data_product_wise(){
		$start_date     	 = 	$this->input->post('start_date');
		$end_date       	 = 	$this->input->post('end_date');
		$id1 				 = 	$this->input->post('id1');
		$id2       			 = 	$this->input->post('id2');
		$name 				 = 	$this->input->post('name');
		$month_count		 = 	$this->input->post('month_count');
		$company			 = 	$this->input->post('company');
		$company_qry         = '';
		if((int)$company > 0){
			$company_qry     = ' AND cw_health_renewals.company = '.$company;
		}
		if((int)$month_count > 0){
			$company_qry    .= ' AND cw_health_renewals.month_count = '.$month_count;
		}
		//due_date and trns_status
		$due_date_con        =  "cw_health_renewals.trans_status = 1 AND cw_health_renewals.due_date BETWEEN '$start_date' AND '$end_date'";

		// WHERE condition
		$where      =  'cw_health_renewals.company= '.$id2.'';
	
		//display
		$display    =  'cw_health_renewals.proposer_name,cw_health_renewals.contact_number,cw_health_renewals.due_date,cw_health_renewals.renewed_date,cw_health_renewals.current_policy_no AS policy_number,cw_health_company.company_name,cw_health_renewals.login_date,sum(cw_health_renewals.net_premium) as net_premium,cw_health_product.product_name,created.employee_name,cw_health_call_status.health_call_status,cw_sub_disposition.sub_disposition,call_count,CONCAT(owner.login_code," - ",owner.employee_name) as owner,CONCAT(level1.login_code," - ",level1.employee_name) as level1,concat(level2.login_code," - ",level2.employee_name) as level2';

		//join 
		$joins   =  'cw_health_renewals INNER JOIN cw_health_company ON cw_health_company.prime_health_company_id=cw_health_renewals.company INNER JOIN cw_health_product ON cw_health_product.prime_health_product_id=cw_health_renewals.product inner join cw_health_call_status on cw_health_call_status.prime_health_call_status_id = cw_health_renewals.call_status inner join cw_sub_disposition on cw_sub_disposition.prime_sub_disposition_id=cw_health_renewals.sub_disposition inner join cw_employees as owner on owner.login_code = cw_health_renewals.login_code LEFT JOIN cw_employees as created ON created.prime_employees_id = cw_health_renewals.trans_updated_by LEFT join cw_employees as level1 on level1.employee_code = owner.level_1_reporting_person LEFT join cw_employees as level2 on level2.employee_code = owner.level_2_reporting_person';

		$group_by = ' GROUP BY cw_health_product.product_name';

		if($name === 'status'){
			$popup_qry       = 'SELECT '.$display.' FROM '.$joins.' WHERE '.$due_date_con.'  AND cw_health_renewals.renewal_status='.$id1.'  '.$company_qry.' AND cw_health_renewals.call_status='.$id2.''.$group_by.'';				
		}
		if($name === 'employee'){
			$popup_qry	     = 'SELECT '.$display.' FROM '.$joins.' WHERE  '.$due_date_con.'AND cw_health_renewals.trans_updated_by ='.$id1.' '.$company_qry.' AND '.$where.''.$group_by.'';
		}
		if($name === "company"){
			$popup_qry		= $popup_qry		= 'SELECT cw_health_renewals.proposer_name,cw_health_renewals.contact_number,cw_health_renewals.due_date,cw_health_renewals.renewed_date,cw_health_renewals.current_policy_no AS policy_number,cw_health_company.company_name,cw_health_renewals.login_date,Round(sum(cw_health_renewals.net_premium),2) as net_premium,cw_health_product.product_name,created.employee_name,cw_health_call_status.health_call_status,cw_sub_disposition.sub_disposition,call_count,CONCAT(owner.login_code," - ",owner.employee_name) as owner,CONCAT(level1.login_code," - ",level1.employee_name) as level1,concat(level2.login_code," - ",level2.employee_name) as level2 FROM cw_health_renewals INNER JOIN cw_health_company ON cw_health_company.prime_health_company_id=cw_health_renewals.company INNER JOIN cw_health_product ON cw_health_product.prime_health_product_id=cw_health_renewals.product inner join cw_health_call_status on cw_health_call_status.prime_health_call_status_id = cw_health_renewals.call_status inner join cw_sub_disposition on cw_sub_disposition.prime_sub_disposition_id=cw_health_renewals.sub_disposition inner join cw_employees as owner on owner.login_code = cw_health_renewals.login_code LEFT JOIN cw_employees as created ON created.prime_employees_id = cw_health_renewals.trans_updated_by LEFT join cw_employees as level1 on level1.employee_code = owner.level_1_reporting_person LEFT join cw_employees as level2 on level2.employee_code = owner.level_2_reporting_person WHERE cw_health_renewals.trans_status = 1  '.$company_qry.' AND cw_health_renewals.due_date BETWEEN "'.$start_date.'" AND "'.$end_date.'" AND cw_health_renewals.company= 1'.$group_by.'';
		}
		// echo $popup_qry;die;
		$health_popup_info 	= 	$this->db->query($popup_qry);
		$health_popup_rslt 	= 	$health_popup_info->result_array();
		$health_popup_info->next_result();
		echo json_encode(array('success' => TRUE,'popup_data' => $health_popup_rslt));
	}

	public function get_life_company(){
		$comp_qry  = 'SELECT prime_company_name_id,company_name FROM cw_company_name WHERE trans_status = 1';
		$comp_info = $this->db->query("CALL sp_a_run ('SELECT','$comp_qry')");
		$comp_rslt = $comp_info->result_array();
		$comp_info->next_result();
		$option    = '';
		if(count($comp_rslt ?? []) > 0){
			foreach($comp_rslt as $key => $value){
				$option .= "<option value='".$value['prime_company_name_id']."'>".$value['company_name']."</option>";
			}
		}else{
			$option = "<option value=''>No Data Available</option>";
		}
		return $option;
	}

	public function get_health_company(){
		$comp_qry  = 'SELECT prime_health_company_id,company_name FROM cw_health_company WHERE trans_status = 1';
		$comp_info = $this->db->query("CALL sp_a_run ('SELECT','$comp_qry')");
		$comp_rslt = $comp_info->result_array();
		$comp_info->next_result();
		$option    = '';
		if(count($comp_rslt ?? []) > 0){
			foreach($comp_rslt as $key => $value){
				$option .= "<option value='".$value['prime_health_company_id']."'>".$value['company_name']."</option>";
			}
		}else{
			$option = "<option value=''>No Data Available</option>";
		}
		return $option;
	}
}
?>